/*
SQLyog Ultimate v11.24 (32 bit)
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


USE `portal`;

/*Table structure for table `crm_account_set` */

CREATE TABLE `crm_account_set` (
  `ACT_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '账套ID',
  `ACT_CODE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '账套代号',
  `ACT_NAME` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '账套名称',
  `PTF_ID` bigint(20) DEFAULT NULL COMMENT '主管平台ID',
  `ENABLE` tinyint(1) DEFAULT '1' COMMENT '是否可用',
  `ACT_DESC` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '账套描述',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  `ACT_TYPE` bigint(20) DEFAULT '1',
  PRIMARY KEY (`ACT_ID`),
  UNIQUE KEY `ACT_CODE` (`ACT_CODE`),
	KEY `PTF_ID`(`PTF_ID`),
	KEY `REC_USER`(`REC_USER`)
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='CRM账套';

/*Data for the table `crm_account_set` */

/*Table structure for table `crm_wxgzh` */

CREATE TABLE `crm_wxgzh` (
  `GZH_ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '公众号ID',
  `GZH_TYPE` tinyint(4) NOT NULL COMMENT '公众号类型：<br>0代表订阅号，<br>1代表由历史老帐号升级后的订阅号，<br>2代表服务号',
  `VERIFY` tinyint(4) NOT NULL DEFAULT '0' COMMENT '认证类型:<br>-1代表未认证，<br>1代表新浪微博认证，<br>2代表腾讯微博认证，<br>3代表已资质认证通过但还未通过名称认证，<br>4代表已资质认证通过、还未通过名称认证，但通过了新浪微博认证，<br>5代表已资质认证通过、还未通过名称认证，但通过了腾讯微博认证',
  `USER_NAME` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '原始ID',
  `NICK_NAME` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '昵称',
  `HEAD_IMG` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '头像',
  `QRCODE` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '二维码',
  `ICON` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '小图标',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`GZH_ID`),
	KEY `GZH_TYPE`(`GZH_TYPE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='微信公众号';

/*Data for the table `crm_wxgzh` */

/*Table structure for table `crm_account_set_brand` */

CREATE TABLE `crm_account_set_brand` (
  `ACT_ID` bigint(20) DEFAULT NULL COMMENT '账套ID',
  `BND_ID` bigint(20) DEFAULT NULL COMMENT '品牌ID',
  UNIQUE KEY `ACT_ID` (`ACT_ID`,`BND_ID`),
  CONSTRAINT `crm_account_set_brand_ibfk_1` FOREIGN KEY (`ACT_ID`) REFERENCES `crm_account_set` (`ACT_ID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='CRM账套的适用经营品牌（空为不限品牌）';

/*Data for the table `crm_account_set_brand` */

/*Table structure for table `crm_account_set_channel` */

CREATE TABLE `crm_account_set_channel` (
  `ACT_ID` bigint(20) NOT NULL COMMENT '账套ID',
  `SCENE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '场景',
  `CHL_ID` bigint(20) NOT NULL COMMENT '通道ID',
  UNIQUE KEY `ACT_ID` (`ACT_ID`,`SCENE`,`CHL_ID`),
	KEY `CHL_ID`(`CHL_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_account_set_channel` */

/*Table structure for table `crm_account_set_rights` */

CREATE TABLE `crm_account_set_rights` (
  `ACT_ID` bigint(20) NOT NULL COMMENT '账套ID',
  `RIGHTS` text COLLATE utf8_bin NOT NULL COMMENT '权益说明',
	`REC_TIME` timestamp  NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间', 
	`REC_USER` bigint(20) NULL COMMENT '记录人',
	UNIQUE KEY `ACT_ID` (`ACT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='账套顾客权益';

/*Data for the table `crm_account_set_rights` */

/*Table structure for table `crm_account_set_site` */

CREATE TABLE `crm_account_set_site` (
  `ACT_ID` bigint(20) NOT NULL COMMENT '账套ID',
	`GRP_ID` int(11) NOT NULL COMMENT '网点组ID',
  `SIT_ID` int(11) NOT NULL COMMENT '网点ID',
  `BND_ID` int(11) NOT NULL COMMENT '品牌ID',
  UNIQUE KEY `SIT_ID` (`SIT_ID`,`BND_ID`),
  KEY `ACT_ID` (`ACT_ID`),
	KEY `GRP_ID`(`GRP_ID`),
  CONSTRAINT `crm_account_set_site_ibfk_1` FOREIGN KEY (`ACT_ID`) REFERENCES `crm_account_set` (`ACT_ID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='CRM账套适用网点';

/*Data for the table `crm_account_set_site` */

/*Table structure for table `crm_account_set_wxgzh` */

CREATE TABLE `crm_account_set_wxgzh` (
  `ACT_ID` bigint(20) NOT NULL COMMENT '账套ID',
  `GZH_ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '公众号ID',
  UNIQUE KEY `ACT_ID` (`ACT_ID`,`GZH_ID`),
  KEY `GZH_ID` (`GZH_ID`),
  CONSTRAINT `crm_account_set_wxgzh_ibfk_1` FOREIGN KEY (`ACT_ID`) REFERENCES `crm_account_set` (`ACT_ID`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `crm_account_set_wxgzh_ibfk_2` FOREIGN KEY (`GZH_ID`) REFERENCES `crm_wxgzh` (`GZH_ID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='CRM账套绑定微信公众号';

/*Data for the table `crm_account_set_wxgzh` */

/*Table structure for table `crm_api_account` */

CREATE TABLE `crm_api_account` (
  `API_ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '用户名',
  `API_TYPE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '类型',
  `API_SEC` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '密码',
  `SEC_OLD` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '旧密码',
  `SEC_TIME` bigint(20) DEFAULT NULL COMMENT '密码时间',
  `ENABLE` tinyint(1) DEFAULT '1' COMMENT '是否可用',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`API_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='crm webservice api用户';

/*Data for the table `crm_api_account` */

/*Table structure for table `crm_api_account_auth` */

CREATE TABLE `crm_api_account_auth` (
  `API_ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT 'API用户',
  `RES_ID` int(11) NOT NULL COMMENT 'API资源ID',
  UNIQUE KEY `API_ID` (`API_ID`,`RES_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_api_account_auth` */

/*Table structure for table `crm_api_account_site` */

CREATE TABLE `crm_api_account_site` (
  `API_ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT 'API用户名',
  `SIT_ID` bigint(20) NOT NULL COMMENT '网点ID',
  UNIQUE KEY `API_ID` (`API_ID`,`SIT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_api_account_site` */

/*Table structure for table `crm_api_account_wxgzh` */

CREATE TABLE `crm_api_account_wxgzh` (
  `API_ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT 'API用户名',
  `GZH_ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '公众号',
  UNIQUE KEY `API_ID` (`API_ID`,`GZH_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_api_account_wxgzh` */

/*Table structure for table `crm_api_resource` */

CREATE TABLE `crm_api_resource` (
  `RES_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'API资源ID',
  `RES_NAME` varchar(64) COLLATE utf8_bin NOT NULL COMMENT 'API资源名称',
  `RES_DESC` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT 'API资源描述',
  `RES_URL` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT 'API资源url',
  `RES_PID` int(11) DEFAULT NULL COMMENT '父ID',
  `RES_LEVEL` int(11) NOT NULL DEFAULT '0' COMMENT '层级',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`RES_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='API资源';

/*Data for the table `crm_api_resource` */

/*Table structure for table `crm_appeal` */

CREATE TABLE `crm_appeal` (
  `APL_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '申述ID',
  `CST_ID` bigint(20) NOT NULL COMMENT '顾客ID',
  `GZH_ID` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '公众号ID',
  `APL_REASON` varchar(200) COLLATE utf8_bin NOT NULL COMMENT '申述原因',
  `APL_PHONE` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '申述手机号',
  `APL_CARD` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '申述卡号',
  `APL_IP` varchar(36) COLLATE utf8_bin DEFAULT NULL COMMENT '申述IP',
  `APL_STAT` tinyint(4) DEFAULT '0' COMMENT '申述状态',
  `APL_TIME` datetime DEFAULT NULL COMMENT '申述时间',
  `IDCARD_FRONT` varchar(200) COLLATE utf8_bin NOT NULL COMMENT '身份证正面',
  `IDCARD_BACK` varchar(200) COLLATE utf8_bin NOT NULL COMMENT '身份证反面',
  `CHK_USER` bigint(20) DEFAULT NULL COMMENT '审核人',
  `CHK_TIME` datetime DEFAULT NULL COMMENT '审核时间',
  `CHK_MEMO` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '审核备注',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`APL_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_appeal` */

CREATE TABLE `crm_approval_process`(
	`ID` bigint(20) NOT NULL  auto_increment COMMENT 'chId' , 
	`TYPE` int(11) NULL  COMMENT '类型(0优惠券，1营销活动)' , 
	`TYPEID` varchar(32) COLLATE utf8_bin NULL  COMMENT '对应优惠券/营销Id' , 
	`DESC` varchar(255) COLLATE utf8_bin NULL  COMMENT '备注' , 
	`STATUS` int(11) NULL  COMMENT '审批状态(1待审批2通过3退回)' , 
	`REC_TIME` timestamp NULL  DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '审批时间' , 
	`REC_USER` bigint(20) NULL  COMMENT '审批人' , 
	PRIMARY KEY (`ID`),
	KEY `TYPE` (`TYPE`,`TYPEID`),
	KEY `REC_USER`(`REC_USER`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Table structure for table `crm_appeal_result` */

CREATE TABLE `crm_appeal_result` (
  `APL_ID` bigint(20) NOT NULL COMMENT '申述ID',
  `CST_ID` bigint(20) NOT NULL COMMENT '顾客ID',
  UNIQUE KEY `APL_ID` (`APL_ID`,`CST_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_appeal_result` */

CREATE TABLE `crm_condition_field` (
  `FLD_SCOPE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '字段作用域',
  `FLD_NAME` varchar(128) COLLATE utf8_bin NOT NULL COMMENT '字段名',
  `FLD_SQL` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '字段sql名',
  `FLD_OPERATOR` text COLLATE utf8_bin COMMENT '字段操作符',
  `VAL_TYPE` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '字段类型(_char,_num,_date,_select)',
  `VAL_OPTIONS` text COLLATE utf8_bin COMMENT '字段选项',
  `VAL_VALID` text COLLATE utf8_bin COMMENT '字段校验',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  UNIQUE KEY `FLD_SCOPE` (`FLD_SCOPE`,`FLD_SQL`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;




/*Table structure for table `crm_coupon_channel` */

CREATE TABLE `crm_coupon_channel` (
  `CPL_ID` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '券型ID',
  `CHL_ID` bigint(20) NOT NULL COMMENT '渠道ID',
  UNIQUE KEY `CPL_ID` (`CPL_ID`,`CHL_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='券的发行渠道';

/*Data for the table `crm_coupon_channel` */

/*Table structure for table `crm_coupon_gifts` */

CREATE TABLE `crm_coupon_gifts` (
  `CPL_ID` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '券型ID',
  `GDS_ID` bigint(20) NOT NULL COMMENT '商品ID',
  `AMOUNT` int(11) DEFAULT '0' COMMENT '数量',
  UNIQUE KEY `CPL_ID` (`CPL_ID`,`GDS_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='礼品券的使用礼品';

/*Data for the table `crm_coupon_gifts` */

/*Table structure for table `crm_coupon_rule` */

CREATE TABLE `crm_coupon_rule` (
  `CPL_ID` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '券型ID',
  `CPN_SRC` varchar(16) COLLATE utf8_bin NOT NULL DEFAULT 'INNER' COMMENT '来源',
  `CPL_NAME` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '券型名称',
  `CPL_DESC` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '券型描述',
  `CPN_TYPE` tinyint(4) NOT NULL COMMENT '券类型',
  `GENERATE` tinyint(4) DEFAULT NULL COMMENT '产生方式',
  `CPL_ORG` bigint(20) NOT NULL COMMENT '券发行单位',
  `BLC_ACT` bigint(20) NOT NULL COMMENT '发券主体',
  `SHARED` tinyint(1) DEFAULT '0' COMMENT '共享性',
  `MULTIPLE` tinyint(1) DEFAULT NULL COMMENT '单笔消费允许使用多张券',
  `PRICE` decimal(10,2) DEFAULT '0.00' COMMENT '价格',
  `MONEY` decimal(10,2) DEFAULT NULL COMMENT '金额',
  `VERIFY` tinyint(4) DEFAULT NULL COMMENT '核销方式',
  `CPL_STAT` tinyint(4) NOT NULL COMMENT '状态',
  `ENABLE` tinyint(1) DEFAULT '0' COMMENT '是否可用',
  `ISU_S_TIME` datetime DEFAULT NULL COMMENT '发放开始',
  `ISU_E_TIME` datetime DEFAULT NULL COMMENT '发放结束',
  `USE_S_TIME` datetime DEFAULT NULL COMMENT '使用开始时间',
  `USE_E_TIME` datetime DEFAULT NULL COMMENT '使用结束时间',
  `USE_AFTER` int(11) DEFAULT '-1' COMMENT '发券后*天有效',
  `ICON` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '图标',
  `URL` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '详情页URL地址',
  `CONTENT` text COLLATE utf8_bin COMMENT '详情内容',
  `EXCLUSIVE` tinyint(1) DEFAULT '0' COMMENT '跟其它促销优惠活动并用',
  `DISCOUNT` double(4,2) DEFAULT NULL COMMENT '折扣',
  `GOODS_LMT` tinyint(1) DEFAULT '0' COMMENT '商品限制',
  `SITE_LMT` tinyint(1) DEFAULT '0' COMMENT '网点限制',
  `MONEY_LMT` decimal(10,2) DEFAULT NULL COMMENT '金额限制',
  `DISCOUNT_LMT` double(4,2) DEFAULT NULL COMMENT '折扣限制',
  `AMOUNT_LIMIT` int(11) DEFAULT '-1' COMMENT '购买数量限制',
  `CST_LMT` tinyint(4) DEFAULT '2' COMMENT '发券对象限制',
	`BENIFIT_LMT` int(11) DEFAULT '1' COMMENT '优惠限制，用bit位表示（如：0代表都不允许，1代表只允许优惠商品，2代表只允许优惠运费，3代表都允许,目前大于3代表不合法）金额券默认1，包邮券默认2',
  `BENIFIT_MAX` decimal(10,2) DEFAULT '-1.00' COMMENT '最高优惠金额(-1或者空代表不限)，仅使用于折扣券，包邮券',
  `WEIGHT_LMT` double DEFAULT '-1' COMMENT '重量限制（-1或者空代表不限）',
  `REGION_LMT` tinyint(1) DEFAULT '0' COMMENT '区域是否限制，如果是true，另外的表存区域',
  `MAX_NUM` bigint(20) DEFAULT '0' COMMENT '最大数量',
  `NOW_NUM` bigint(20) DEFAULT '0' COMMENT '当前数量',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`CPL_ID`),
	KEY `BLC_ACT`(`BLC_ACT`),
	KEY `REC_USER`(`REC_USER`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_coupon_rule` */

/*Table structure for table `crm_coupon_site_receive` */

CREATE TABLE `crm_coupon_site_receive` (
  `CPL_ID` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '券型ID',
  `SIT_ID` bigint(20) NOT NULL COMMENT '店铺ID',
  UNIQUE KEY `CPL_ID` (`CPL_ID`,`SIT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_coupon_site_receive` */

/*Table structure for table `crm_coupons` */

CREATE TABLE `crm_coupons` (
  `CPN_ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '券ID',
  `CPL_ID` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '券规则ID',
  `CPN_STAT` tinyint(4) NOT NULL DEFAULT '0' COMMENT '券状态',
  `CPN_FROM` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '发券来源',
  `CPN_FROM_INST` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '发券来源实例',
  `S_TIME` datetime DEFAULT NULL COMMENT '开始时间',
  `E_TIME` datetime DEFAULT NULL COMMENT '结束时间',
  `CRT_TIME` datetime DEFAULT NULL COMMENT '券生成时间',
  `LOK_TIME` datetime DEFAULT NULL COMMENT '券锁定时间',
  `CPN_USE` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '券核销方式',
  `CPN_USE_INST` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '券核销实例',
  `USE_TIME` datetime DEFAULT NULL COMMENT '核销时间',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`CPN_ID`),
  KEY `CPL_ID` (`CPL_ID`),
  KEY `CPN_FROM` (`CPN_FROM`,`CPN_FROM_INST`),
  KEY `CPN_USE` (`CPN_USE`,`CPN_USE_INST`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_coupons` */

/*Table structure for table `crm_coupons_customer` */

CREATE TABLE `crm_coupons_customer` (
  `CST_ID` bigint(20) NOT NULL COMMENT '持有券顾客ID',
  `CPN_ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '券ID',
  `CST_FROM` bigint(20) DEFAULT NULL COMMENT '转赠顾客ID',
  `RCV_TIME` datetime DEFAULT NULL COMMENT '接收券时间',
  `ORDERS` int(11) DEFAULT '0' COMMENT '顺序',
  `ENABLE` tinyint(1) DEFAULT '1' COMMENT '是否有效',
	KEY `CST_ID1` (`CST_ID`),
	KEY `CST_ID2` (`CST_FROM`),
	KEY `CPN_ID` (`CPN_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_coupons_customer` */

CREATE TABLE `crm_coupons_grant` (
  `GRT_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '发放ID',
  `DOC_CODE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '单据编号',
  `CPL_ID` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '券型号',
  `GRT_TARGET` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '发放对象（SHOPPER,SITE）',
  `PER_NUM` int(11) DEFAULT NULL COMMENT '每人数量',
  `TOTAL_NUM` bigint(20) DEFAULT NULL COMMENT '总数量',
  `ENABLE` tinyint(1) DEFAULT NULL COMMENT '是否可用',
  `GRT_STAT` tinyint(4) DEFAULT NULL COMMENT '审批状态',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  `REMARK` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`GRT_ID`),
  UNIQUE KEY `DOC_CODE` (`DOC_CODE`),
  KEY `CPL_ID` (`CPL_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `crm_coupons_grant_shopper`(
	`ID` bigint(20) NOT NULL  auto_increment COMMENT '主键ID' , 
	`GRT_ID` bigint(20) NOT NULL  COMMENT '发放ID' , 
	`SHP_ID` bigint(20) NOT NULL  COMMENT '店员ID' , 
	`THIS_NUM` int(11) NOT NULL  COMMENT '数量' , 
	PRIMARY KEY (`ID`) , 
	KEY `GRT_ID`(`GRT_ID`) , 
	KEY `SHP_ID`(`SHP_ID`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Table structure for table `crm_coupons_outer` */

CREATE TABLE `crm_coupons_outer` (
  `CPN_ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '券ID',
  `CPN_OUTER` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '外部券ID',
  `CPN_SRC` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '券来源',
  PRIMARY KEY (`CPN_ID`),
  UNIQUE KEY `CPN_OUTER` (`CPN_OUTER`,`CPN_SRC`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_coupons_outer` */

CREATE TABLE `crm_coupons_receipt` (
  `ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '回执ID',
  `CHANNEL` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '渠道',
  `USR` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '代理对象',
  `TYPE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '类型（ISSUE,CONSUME）',
  `BIZ_TYPE` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '业务类型',
  `BIZ_NO` varchar(36) COLLATE utf8_bin NOT NULL COMMENT '业务ID',
  `CST_ID` bigint(20) DEFAULT NULL COMMENT '顾客ID',
  `CPN_ID` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '券ID',
  `CPL_ID` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '券规则ID',
  `AMOUNT` int(11) DEFAULT NULL COMMENT '数量',
  `REMARK` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '描述',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`ID`),
  KEY `CHANNEL` (`CHANNEL`,`TYPE`,`BIZ_NO`,`BIZ_TYPE`),
	KEY `BIZ_NO`(`BIZ_NO`,`BIZ_TYPE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


/*Table structure for table `crm_coupons_shopper` */

CREATE TABLE `crm_coupons_shopper` (
  `SHP_ID` bigint(20) NOT NULL COMMENT '导购ID',
  `CPL_ID` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '券型ID',
  `TOTAL_NUM` int(11) DEFAULT '0' COMMENT '总数',
  `REMAIN_NUM` int(11) DEFAULT '0' COMMENT '剩余数',
  UNIQUE KEY `SHP_ID` (`SHP_ID`,`CPL_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_coupons_shopper` */

/*Table structure for table `crm_coupons_site` */

CREATE TABLE `crm_coupons_site` (
  `SIT_ID` bigint(20) NOT NULL COMMENT '网点ID',
  `CPL_ID` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '券型ID',
  `TOTAL_NUM` int(11) DEFAULT NULL COMMENT '总数',
  `REMAIN_NUM` int(11) DEFAULT NULL COMMENT '剩余数量',
  UNIQUE KEY `SIT_ID` (`SIT_ID`,`CPL_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_coupons_site` */

/*Table structure for table `crm_customer` */

CREATE TABLE `crm_customer` (
  `CST_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '顾客ID',
  `CST_TYPE` tinyint(4) NOT NULL COMMENT '顾客类型',
  `CST_CHL` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '顾客来源渠道',
  `CST_SRC` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '顾客来源',
  `SRC_VAL` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '来源实例',
  `REFERRER` bigint(20) DEFAULT NULL COMMENT '推荐人',
  `CST_SITE` bigint(20) DEFAULT NULL COMMENT '入会网点',
  `REG_TIME` datetime DEFAULT NULL COMMENT '入会时间',
  `CST_NAME` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '会员名',
  `FIRST_NAME` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '名',
  `LAST_NAME` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '姓',
  `SEX` tinyint(2) NULL COMMENT '性别',
  `PHONE` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '手机',
  `EMAIL` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '邮箱',
  `CRED_TYPE` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '证件类型',
  `CRED_NUM` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '证件号',
  `BIRTHDAY` date DEFAULT NULL COMMENT '出生日期',
  `BIRTH_TYPE` tinyint(2) DEFAULT '0' COMMENT '生日类型',
  `VERIFY` tinyint(4) DEFAULT '0' COMMENT '认证',
  `MARRIAGE` tinyint(2) DEFAULT '2' COMMENT '婚否',
  `JOB` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '工作',
  `DEGREE` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '学历',
  `ACTIVATE` tinyint(1) DEFAULT '1' COMMENT '是否激活',
  `FREEZE` tinyint(1) DEFAULT '0' COMMENT '是否冻结',
  `MERGED` tinyint(1) DEFAULT '0' COMMENT '是否已经归并',
  `FRZ_ID` bigint(20) DEFAULT NULL COMMENT '冻结ID',
  `LAST_LOGIN` datetime DEFAULT NULL COMMENT '最近一次登录',
  `PREPAID` tinyint(1) DEFAULT '0' COMMENT '是否启用储值账户',
  `RCV_MSG` tinyint(1) DEFAULT '1' COMMENT '是否接收消息',
  `PAY_PASS` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '支付密码',
  `TOTAL_PROFIT` double(12,2) DEFAULT '0.00' COMMENT '累计收益',
  `TOTAL_AMOUNT` double(12,2) DEFAULT '0.00' COMMENT '累计消费',
  `FILE_TIME` datetime DEFAULT NULL COMMENT '留档时间',
	`PROVINCE` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '省',
  `CITY` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '市',
  `COUNTY` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '县',
  `ADDRESS` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '地址',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '创建人',
  `M_ATTR1` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '自定义字段1',
  `M_ATTR2` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR3` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR4` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR5` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR6` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR7` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR8` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR9` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR10` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR11` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR12` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR13` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR14` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR15` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR16` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR17` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR18` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR19` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR20` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR21` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR22` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR23` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR24` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR25` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR26` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR27` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR28` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR29` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `M_ATTR30` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`CST_ID`),
  UNIQUE KEY `PHONE` (`PHONE`),
  KEY `CST_SITE` (`CST_SITE`),
	KEY `CST_SRC`(`CST_SRC`,`SRC_VAL`),
	KEY `REFERRER`(`REFERRER`)
) ENGINE=InnoDB AUTO_INCREMENT=200013572468 DEFAULT CHARSET=utf8mb4 COMMENT='CRM顾客';

/*Data for the table `crm_customer` */

/*Table structure for table `crm_customer_account` */

CREATE TABLE `crm_customer_account` (
  `CST_ID` bigint(20) NOT NULL COMMENT '顾客ID',
  `ACT_ID` bigint(20) NOT NULL COMMENT '账套ID',
  `CST_CHL` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '顾客来源渠道',
  `CST_SRC` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '顾客来源',
  `SRC_VAL` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '来源实例',
  `REFERRER` bigint(20) DEFAULT NULL COMMENT '推荐人',
  `CST_RANK` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '会员等级',
  `RANK_TIME` datetime DEFAULT NULL COMMENT '上次定级时间',
  `NEXT_RANK_TIME` datetime DEFAULT NULL COMMENT '下次定级时间',
  `FAV_SITE` bigint(20) DEFAULT NULL COMMENT '首选网点',
  `FAV_SERVICE` bigint(20) DEFAULT NULL COMMENT '首选客服',
  `LAST_LOGIN` datetime DEFAULT NULL COMMENT '最近登录',
  `TOTAL_PROFIT` decimal(12,2) DEFAULT '0.00' COMMENT '累计收益',
  `TOTAL_AMOUNT` decimal(12,2) DEFAULT '0.00' COMMENT '累计消费',
  `TOTAL_TIMES` int(11) DEFAULT '0' COMMENT '累计次数',
  PRIMARY KEY (`CST_ID`,`ACT_ID`),
	KEY `CST_SRC`(`CST_SRC`,`SRC_VAL`),
  KEY `ACT_RANK` (`ACT_ID`,`CST_RANK`,`NEXT_RANK_TIME`),
  KEY `REFERRER` (`REFERRER`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_customer_account` */

/*Table structure for table `crm_customer_activity` */

CREATE TABLE `crm_customer_activity` (
  `CST_ID` bigint(20) NOT NULL COMMENT '顾客ID',
  `MKT_ID` bigint(20) NOT NULL COMMENT '营销活动ID',
  KEY `CST_ID` (`CST_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_customer_activity` */

/*Table structure for table `crm_customer_address` */

CREATE TABLE `crm_customer_address` (
  `ADDR_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '地址ID',
  `CST_ID` bigint(20) NOT NULL COMMENT '顾客ID',
  `COUNTRY_CODE` int(11) DEFAULT NULL COMMENT '国家',
  `PROVINCE_CODE` int(11) DEFAULT NULL COMMENT '省',
  `CITY_CODE` int(11) DEFAULT NULL COMMENT '市',
  `COUNTY_CODE` int(11) DEFAULT NULL COMMENT '县',
  `DETAIL` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '详细地址',
  `ZIP_CODE` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '邮编',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`ADDR_ID`),
  KEY `CST_ID` (`CST_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_customer_address` */

CREATE TABLE `crm_customer_auto` (
  `CST_ID` bigint(20) NOT NULL COMMENT '顾客ID',
  `AUTO_TYPE` bigint(20) NOT NULL COMMENT '车型',
  `PLATE_NUM` varchar(10) COLLATE utf8_bin DEFAULT NULL COMMENT '车牌',
  `PROD_DATE` date DEFAULT NULL COMMENT '出厂日期',
  `REC_TIME` timestamp NULL DEFAULT NULL COMMENT '记录时间',
  KEY `CST_ID` (`CST_ID`),
  KEY `AUTO_TYPE` (`AUTO_TYPE`),
  KEY `PLATE_NUM` (`PLATE_NUM`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


/*Table structure for table `crm_customer_change` */

CREATE TABLE `crm_customer_change` (
  `CST_ID` bigint(20) NOT NULL COMMENT '顾客ID',
  `ACT_ID` bigint(20) DEFAULT NULL COMMENT '账套ID',
  `CST_TYPE` tinyint(4) DEFAULT NULL COMMENT '顾客类型',
  `CST_RANK` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '会员等级',
  `S_TIME` datetime NOT NULL COMMENT '开始时间',
  `E_TIME` datetime DEFAULT NULL COMMENT '结束时间',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  UNIQUE KEY `CST_ID` (`CST_ID`,`ACT_ID`,`CST_RANK`,`S_TIME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='顾客等级或者类型变更';

/*Data for the table `crm_customer_change` */

/*Table structure for table `crm_customer_chat_channel` */

CREATE TABLE `crm_customer_chat_channel` (
  `CST_ID` bigint(20) NOT NULL COMMENT '顾客ID',
  `CHANNEL` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '交流通道',
  `IDENTITY` varchar(128) COLLATE utf8_bin NOT NULL COMMENT '通道标识（手机号，邮箱地址，QQ号）',
  `S_TIME` datetime NOT NULL COMMENT '开始使用时间',
  `E_TIME` datetime DEFAULT NULL COMMENT '终止使用时间',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REMARK` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '备注',
  KEY `channel` (`CHANNEL`,`IDENTITY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='顾客交流通道';

/*Data for the table `crm_customer_chat_channel` */

/*Table structure for table `crm_customer_chat_service` */

CREATE TABLE `crm_customer_chat_service` (
  `CST_ID` bigint(20) NOT NULL COMMENT '顾客ID',
  `SERV_ID` bigint(20) NOT NULL COMMENT '客服ID',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_customer_chat_service` */

/*Table structure for table `crm_customer_city` */

CREATE TABLE `crm_customer_city` (
  `CST_ID` bigint(20) NOT NULL COMMENT '顾客ID',
  `CITY_ID` bigint(20) NOT NULL COMMENT '城市ID',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
   KEY `CST_ID` (`CST_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_customer_city` */

/*Table structure for table `crm_customer_freeze` */

CREATE TABLE `crm_customer_freeze` (
  `FRZ_ID` bigint(20) NOT NULL COMMENT '冻结ID',
  `CST_ID` bigint(20) NOT NULL COMMENT '顾客ID',
  `FRZ_CAUSE` varchar(128) COLLATE utf8_bin NOT NULL COMMENT '冻结原因',
  `FRZ_USER` bigint(20) DEFAULT NULL COMMENT '冻结操作人',
  `S_TIME` datetime NOT NULL COMMENT '开始时间',
  `E_TIME` datetime NOT NULL COMMENT '结束时间',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REMARK` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`FRZ_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_customer_freeze` */

/*Table structure for table `crm_customer_group` */

CREATE TABLE `crm_customer_group` (
  `GRP_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '社群ID',
  `GRP_NAME` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '社群名称',
  `GRP_DESC` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '社群介绍',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`GRP_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_customer_group` */

/*Table structure for table `crm_customer_guide` */

CREATE TABLE `crm_customer_guide` (
  `GUI_ID` bigint(20) DEFAULT NULL COMMENT 'ID',
  `GUI_PID` bigint(20) DEFAULT NULL COMMENT '父ID',
  `ACT_ID` bigint(20) DEFAULT NULL COMMENT '账套ID，0为整个联盟',
  `IS_SYSTEM` tinyint(1) DEFAULT NULL COMMENT '是否系统',
  `GUI_TYPE` tinyint(4) DEFAULT NULL COMMENT '类型(0：分组；1：项)',
  `GUI_TITLE` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '标题',
  `GUI_CONTENT` text COLLATE utf8_bin COMMENT '内容',
  `USEFULL` bigint(20) DEFAULT NULL COMMENT '有用数量',
  `USELESS` bigint(20) DEFAULT NULL COMMENT '无用数量',
  `TOTAL` bigint(20) DEFAULT NULL COMMENT '浏览总数',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='会员宝典';

/*Data for the table `crm_customer_guide` */

/*Table structure for table `crm_customer_merge` */

CREATE TABLE `crm_customer_merge` (
  `CST_TARGET` bigint(20) NOT NULL COMMENT '目标顾客',
  `CST_SOURCE` bigint(20) NOT NULL COMMENT '源顾客',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
	KEY `CST_SOURCE` (`CST_SOURCE`),
	KEY `CST_TARGET`(`CST_TARGET`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='顾客归并';

/*Data for the table `crm_customer_merge` */

CREATE TABLE `crm_customer_need_rated` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `CST_ID` bigint(20) NOT NULL COMMENT '顾客ID',
  `ACT_ID` bigint(20) NOT NULL COMMENT '账套ID',
  `UP_DATE` date NOT NULL COMMENT '时间',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ACT_ID` (`CST_ID`,`ACT_ID`,`UP_DATE`),
  KEY `UP_DATE` (`UP_DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Table structure for table `crm_customer_old` */

CREATE TABLE `crm_customer_old` (
  `CST_ID` bigint(20) NOT NULL COMMENT '顾客ID',
  `ACT_ID` bigint(20) NOT NULL COMMENT '账套ID',
  `CARD_ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '老卡号',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  UNIQUE KEY `CARD_ID` (`CARD_ID`,`ACT_ID`),
	UNIQUE KEY `CST_ID` (`CST_ID`,`ACT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_customer_old` */

/*Table structure for table `crm_customer_protocol` */

CREATE TABLE `crm_customer_protocol` (
  `PTL_NAME` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '会员协议名称',
  `PTL_TYPE` tinyint(4) NOT NULL COMMENT '协议内容类型',
  `PTL_CONTENT` text COLLATE utf8_bin NOT NULL COMMENT '会员协议内容',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`PTL_NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_customer_protocol` */

insert into `crm_customer_protocol` (`PTL_NAME`, `PTL_TYPE`, `PTL_CONTENT`, `REC_TIME`, `REC_USER`) values('CUSTOMER_PROTOCOL','1','',CURRENT_TIMESTAMP,NULL);

/*Table structure for table `crm_customer_rank` */

CREATE TABLE `crm_customer_rank` (
  `ACT_ID` bigint(20) NOT NULL COMMENT '账套ID',
  `RAK_ID` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '等级ID',
  `RAK_NAME` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '等级名称',
  `RAK_DESC` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '等级描述',
  `RAK_INDEX` int(11) DEFAULT '0' COMMENT '等级级别',
  `RAK_DELAY` int(11) DEFAULT '-1' COMMENT '等级升级延迟',
  `RAK_EXPIRY` int(11) DEFAULT '-1' COMMENT '等级有效期',
  `IS_FANS` tinyint(1) DEFAULT '0' COMMENT '粉丝是否适用',
  `IS_MEMBER` tinyint(1) DEFAULT '1' COMMENT '会员是否适用',
  `IS_SYSTEM` tinyint(1) DEFAULT '0' COMMENT '是否系统',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`ACT_ID`,`RAK_ID`),
  UNIQUE KEY `ACT_ID` (`ACT_ID`,`RAK_INDEX`),
  CONSTRAINT `crm_customer_rank_ibfk_1` FOREIGN KEY (`ACT_ID`) REFERENCES `crm_account_set` (`ACT_ID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='顾客等级';

/*Data for the table `crm_customer_rank` */

CREATE TABLE `crm_customer_rating` (
  `ACT_ID` bigint(20) NOT NULL COMMENT '账套ID',
  `RAT_NAME` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '评级名称',
  `RAT_DELAY` int(11) DEFAULT NULL COMMENT '延迟',
  `RAT_DAYS` int(11) DEFAULT NULL COMMENT '时间段',
  `REC_TIME` timestamp NULL DEFAULT NULL COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`ACT_ID`),
  UNIQUE KEY `ACT_ID` (`ACT_ID`),
  CONSTRAINT `crm_customer_rating_ibfk_1` FOREIGN KEY (`ACT_ID`) REFERENCES `crm_account_set` (`ACT_ID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


/*Table structure for table `crm_customer_tags` */

CREATE TABLE `crm_customer_tags` (
  `CST_ID` bigint(20) NOT NULL COMMENT '顾客ID',
  `TAG_KEY` bigint(20) NOT NULL COMMENT '标签名',
  `TAG_VAL` varchar(20) COLLATE utf8_bin NOT NULL COMMENT '标签值ID',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `OP_USER` bigint(20) DEFAULT '0' COMMENT '打标签的人',
	`VAL_NAME` varchar(30) COLLATE utf8_bin DEFAULT NULL COMMENT '名称',
  UNIQUE KEY `CST_ID` (`CST_ID`,`TAG_KEY`,`TAG_VAL`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_customer_tags` */

/*Table structure for table `crm_customer_verify` */

CREATE TABLE `crm_customer_verify` (
  `CST_ID` bigint(20) NOT NULL COMMENT '顾客ID',
  `VRFY_TYPE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '认证类型',
  `VRFY_USER` bigint(20) DEFAULT NULL COMMENT '认证操作人',
  `S_TIME` datetime DEFAULT NULL COMMENT '开始时间',
  `E_TIME` datetime DEFAULT NULL COMMENT '结束时间',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REMARK` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '备注'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_customer_verify` */

/*Table structure for table `crm_customer_visit_site` */

CREATE TABLE `crm_customer_visit_site` (
  `CST_ID` bigint(20) DEFAULT NULL COMMENT '顾客ID',
  `SIT_ID` bigint(20) DEFAULT NULL COMMENT '网点ID',
  `ACTION` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '行为（购物,体验,路过...）',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REMARK` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '备注'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_customer_visit_site` */

/*Table structure for table `crm_customer_weixin` */

CREATE TABLE `crm_customer_weixin` (
  `CST_ID` bigint(20) NOT NULL COMMENT '顾客ID',
  `GZH_ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '公众号ID',
  `OPEN_ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '微信ID',
  `UNION_ID` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '开发者一致性ID',
  `SUBSCRIBE` tinyint(1) DEFAULT '1' COMMENT '是否关注',
  `NICK_NAME` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '昵称',
  `SEX` tinyint(2) DEFAULT '2' COMMENT '性别',
  `CITY` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '城市',
  `PROVINCE` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '省',
  `COUNTRY` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '国家',
  `HEAD_IMG` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '头像',
  `SUBS_TIME` datetime DEFAULT NULL COMMENT '关注时间',
  `LAST_ACTION` bigint(20) DEFAULT NULL COMMENT '最近交互时间',
	`LAST_SUBS` datetime DEFAULT NULL COMMENT '上次关注',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`GZH_ID`,`OPEN_ID`),
	KEY `CST_ID` (`CST_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_customer_weixin` */

/*Table structure for table `crm_customer_wxfrom` */

CREATE TABLE `crm_customer_wxfrom` (
  `GZH_ID` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '公众号ID',
  `OPEN_ID` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '微信ID',
	`ACTION` tinyint(4) DEFAULT NULL COMMENT '操作类型： 1：微信关注 2：微信取消 3：扫码进入',
  `SCENE` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '场景',
  `FROM_ID` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '场景值',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
	KEY `OPEN_ID` (`GZH_ID`,`OPEN_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_customer_wxfrom` */

/*Table structure for table `crm_discount_item` */

CREATE TABLE `crm_discount_item` (
  `DIST_ID` bigint(20) NOT NULL COMMENT '折扣ID',
  `S_DISCOUNT` double NOT NULL COMMENT '开始折扣',
  `E_DISCOUNT` double NOT NULL COMMENT '结束折扣'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_discount_item` */

/*Table structure for table `crm_discount_section` */

CREATE TABLE `crm_discount_section` (
  `DIST_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '折扣ID',
  `DIST_NAME` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '折扣名称',
  `DIST_DESC` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '折扣描述',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`DIST_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='折扣段表';

/*Data for the table `crm_discount_section` */

/*Table structure for table `crm_district` */

CREATE TABLE `crm_district` (
  `CBD_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '商圈ID',
  `CBD_NAME` varchar(60) COLLATE utf8_bin NOT NULL COMMENT '商圈名称',
  `PROVINCE` bigint(20) DEFAULT NULL COMMENT '省',
  `CITY` bigint(20) DEFAULT NULL COMMENT '市',
  `COUNTY` bigint(20) DEFAULT NULL COMMENT '县',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`CBD_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_district` */

CREATE TABLE `crm_event`(
	`EVT_ID` int(11) NOT NULL  auto_increment COMMENT '事件ID' , 
	`EVT_CODE` varchar(32) COLLATE utf8_bin NULL  COMMENT '事件代号' , 
	`EVT_NAME` varchar(64) COLLATE utf8_bin NULL  COMMENT '事件名称' , 
	`EVT_DESC` varchar(200) COLLATE utf8_bin NULL  COMMENT '事件描述' , 
	`GLOBAL` tinyint(1) NULL  DEFAULT 0 COMMENT '是否全局事件' , 
	`INTERNAL` tinyint(1) NULL  DEFAULT 0 COMMENT '是否内部事件' , 
	`REC_TIME` timestamp NULL  DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '记录时间' , 
	`REC_USER` bigint(20) NULL  COMMENT '记录人' , 
	PRIMARY KEY (`EVT_ID`) , 
	UNIQUE KEY `EVT_CODE`(`EVT_CODE`) , 
	KEY `REC_USER`(`REC_USER`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


CREATE TABLE `crm_event_action`(
	`EVT_ID` int(11) NOT NULL  COMMENT '事件ID' , 
	`ACTION` int(11) NOT NULL  COMMENT '动作' , 
	`SCOPE` varchar(32) COLLATE utf8_bin NOT NULL  COMMENT '作用域（POINTS:积分，COUPON:券,MIX:复合,MSG:消息）' , 
	KEY `EVT_ID`(`EVT_ID`) , 
	KEY `ACTION`(`ACTION`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;




CREATE TABLE `crm_event_action_process`(
	`UNION_ID` int(11) NOT NULL  COMMENT '主键' , 
	`ACTION` int(11) NOT NULL  COMMENT '动作' , 
	`TEMPLATE` int(11) NOT NULL  COMMENT '模板' , 
	`EXE_TYPE` tinyint(4) NOT NULL  COMMENT '执行器类型' , 
	`EXE_NAME` varchar(200) COLLATE utf8_bin NULL  COMMENT '执行器名称' , 
	`EXECUTER` blob NULL  COMMENT '执行器' , 
	`PSR_TYPE` tinyint(4) NULL  COMMENT '解释器类型' , 
	`PSR_NAME` varchar(200) COLLATE utf8_bin NULL  COMMENT '解析器名称' , 
	`PARSER` blob NULL  COMMENT '解析器' , 
	PRIMARY KEY (`UNION_ID`) , 
	UNIQUE KEY `ACTION`(`ACTION`,`TEMPLATE`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;



CREATE TABLE `crm_event_group`(
	`GRP_ID` int(11) NOT NULL  auto_increment COMMENT '分组ID' , 
	`S_TIME` datetime NOT NULL  COMMENT '开始时间' , 
	`E_TIME` datetime NOT NULL  COMMENT '结束时间' , 
	`STRATEGY` varchar(64) COLLATE utf8_bin NOT NULL  COMMENT '执行策略' , 
	`REC_TIME` timestamp NULL  DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '记录时间' , 
	PRIMARY KEY (`GRP_ID`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `crm_event_listener`(
	`GRP_ID` int(11) NOT NULL  COMMENT '分组ID' , 
	`ACT_ID` int(11) NULL  COMMENT '账套ID' , 
	`EVT_ID` int(11) NOT NULL  COMMENT '事件ID' , 
	`UNION_ID` int(11) NOT NULL  COMMENT '事件处理ID' , 
	`FRONT_DATA` text COLLATE utf8_bin NULL  COMMENT '事件数据' , 
	`INNER_DATA` text COLLATE utf8_bin NULL  COMMENT '内部数据' , 
	`INDEX` int(11) NULL  DEFAULT 0 COMMENT '组内排序' , 
	KEY `UNION_ID`(`UNION_ID`) , 
	KEY `ACT_ID`(`ACT_ID`) , 
	KEY `GRP_ID`(`GRP_ID`) , 
	KEY `EVT_ID`(`EVT_ID`) , 
	CONSTRAINT `crm_event_listener_ibfk_1` 
	FOREIGN KEY (`UNION_ID`) REFERENCES `crm_event_action_process` (`UNION_ID`) ON DELETE CASCADE ON UPDATE NO ACTION , 
	CONSTRAINT `crm_event_listener_ibfk_2` 
	FOREIGN KEY (`GRP_ID`) REFERENCES `crm_event_group` (`GRP_ID`) ON DELETE CASCADE ON UPDATE NO ACTION 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


CREATE TABLE `crm_event_template`(
	`TPL_ID` int(11) NOT NULL  COMMENT '模板ID' , 
	`TPL_NAME` varchar(64) COLLATE utf8_bin NULL  COMMENT '模板名称' , 
	`TPL_DESC` varchar(200) COLLATE utf8_bin NULL  COMMENT '模板描述' , 
	PRIMARY KEY (`TPL_ID`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


/*Table structure for table `crm_field` */

CREATE TABLE `crm_field` (
  `FLD_SCOPE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '字段作用域（MEMBER：会员',
  `FLD_NAME` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '字段名',
  `FLD_SQL` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '字段sql名',
  `FLD_TYPE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '字段类型(_char,_num,_date,_passwd,_image,_radio,_check,_select)',
  `FLD_DESC` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '字段描述',
  `USE_NEW` tinyint(1) DEFAULT '1' COMMENT '新建时是否展示',
  `USE_MOD` tinyint(1) DEFAULT '1' COMMENT '编辑时是否展示',
  `REQ_NEW` tinyint(1) DEFAULT '0' COMMENT '新建时是否必填',
  `REQ_MOD` tinyint(1) DEFAULT '0' COMMENT '编辑时是否必填',
  `IS_SYSTEM` tinyint(1) DEFAULT '0' COMMENT '是否系统保留',
  `FLD_INDEX` int(11) DEFAULT '1' COMMENT '字段序号',
  `FLD_OPTIONS` text COLLATE utf8_bin COMMENT '字段选项',
  `FLD_VALID` text COLLATE utf8_bin COMMENT '字段校验',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`FLD_SQL`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_field` */


/*Table structure for table `crm_market` */

CREATE TABLE `crm_market` (
  `MKT_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '营销活动ID',
  `MKT_CODE` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '活动代号',
  `MKT_TYPE` tinyint(4) NOT NULL COMMENT '活动类型（主动营销）',
  `MKT_NAME` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '活动名称',
  `MKT_DESC` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '活动描述',
  `MKT_PROJECT` int(20) DEFAULT NULL COMMENT '活动项目',
  `MKT_ORG` int(11) DEFAULT NULL COMMENT '活动发起组织',
  `MKT_STAT` tinyint(4) NOT NULL COMMENT '活动状态',
  `ENABLE` tinyint(1) DEFAULT '0' COMMENT '是否可用',
  `P_TYPE` tinyint(4) NOT NULL COMMENT '推送时间类型',
  `S_TIME` datetime DEFAULT NULL COMMENT '开始日期',
  `E_TIME` datetime DEFAULT NULL COMMENT '结束日期',
  `P_TIME` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '推送时间',
  `P_CRON` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT 'CRON表达式',
  `P_LIMIT` int(11) DEFAULT NULL COMMENT '推送限制，*天内不重复推送',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  `REC_TIME` timestamp NULL DEFAULT NULL COMMENT '记录时间',
  PRIMARY KEY (`MKT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*Data for the table `crm_market` */

CREATE TABLE `crm_market_channel` (
  `MKT_ID` int(11) NOT NULL COMMENT '活动ID',
  `CHL_ID` int(11) NOT NULL COMMENT '渠道ID',
  UNIQUE KEY `MKT_ID` (`MKT_ID`,`CHL_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `crm_market_coupon` (
  `ITM_ID` int(11) NOT NULL COMMENT '推送ID',
  `TOTAL_NUM` int(11) NULL COMMENT '总数量',
  `CPN_ORG` int(11) NOT NULL COMMENT '组织机构',
  `BLC_ACT` bigint(20) NOT NULL COMMENT '结算账套',
  UNIQUE KEY `ITM_ID` (`ITM_ID`),
  KEY `CPN_ORG` (`CPN_ORG`),
  KEY `BLC_ACT` (`BLC_ACT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `crm_market_coupon_item` (
  `ITM_ID` int(11) NOT NULL COMMENT '推送ID',
  `CPL_ID` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '券型ID',
  `CPN_NUM` int(11) NOT NULL COMMENT '每人数量',
  UNIQUE KEY `ITM_ID` (`ITM_ID`,`CPL_ID`),
  KEY `CPL_ID` (`CPL_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


/*Table structure for table `crm_market_item` */

CREATE TABLE `crm_market_item` (
  `ITM_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '推送组ID',
  `MKT_ID` int(11) NOT NULL COMMENT '活动ID',
  `ITM_INDEX` int(11) NOT NULL COMMENT '组排序',
  `CST_RNG_ID` int(11) DEFAULT NULL COMMENT '顾客范围ID',
  `CST_EXPS` text COLLATE utf8_bin COMMENT '顾客条件',
  `CMP_TYPE` tinyint(4) DEFAULT NULL COMMENT '对比类型.1:百分比;2:人数',
  `CMP_NUMBER` double DEFAULT '0' COMMENT '数值',
  `MSG_CHL` int(11) NOT NULL COMMENT '消息通道',
  `MSG_TYPE` tinyint(4) NOT NULL COMMENT '消息通道,0:微信,1:短信,2:彩信3:邮箱.此处配置和通道配置不一致,需要胡炳城修改界面',
  `MSG_ID` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '消息ID',
  `MSG_SUBJECT` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '消息主题',
  `MSG_CONTENT` text COLLATE utf8_bin COMMENT '消息内容',
  `REC_TIME` timestamp NULL DEFAULT NULL COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`ITM_ID`),
  UNIQUE KEY `MKT_ID` (`MKT_ID`,`ITM_INDEX`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_market_item` */

CREATE TABLE `crm_market_points` (
  `ITM_ID` int(11) NOT NULL COMMENT '推送组ID',
  `PNT_SET` bigint(20) NOT NULL COMMENT '积点账套',
  `PER_VAL` double(12,2) NOT NULL COMMENT '每人积分值',
  `EXPIRY` int(11) NOT NULL COMMENT '有效期',
  `TOTAL_NUM` int(11) NULL COMMENT '最高积分份数',
  `BLC_ACT` bigint(20) NOT NULL COMMENT '结算账套',
  `PNT_ORG` int(11) NOT NULL COMMENT '积分组织',
  `REC_TIME` timestamp NULL DEFAULT NULL COMMENT '记录时间',
  UNIQUE KEY `ITM_ID` (`ITM_ID`),
  KEY `PNT_SET` (`PNT_SET`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Table structure for table `crm_order_account` */

CREATE TABLE `crm_market_activity_customer` (
  `ITM_ID` int(11) NOT NULL COMMENT '推送组ID',
  `CST_ID` bigint(20) NOT NULL COMMENT '顾客ID',
  `BATCH_ID` bigint(20) NOT NULL COMMENT '批次ID',
  `STAT` int(11) NOT NULL DEFAULT '0' COMMENT '人员状态,0:默认状态,1:积分或券成功;2:失败;3:对照客户;4:设置积分不足不发客户;5:设置券不足不发客户;6:实际积分发送失败;7:实际券发送失败;8:通道信息发送失败;9:推送限制,如近期已发过,10:短信发送成功',
  UNIQUE KEY `UNIQUE_ITEM` (`CST_ID`,`BATCH_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='主动营销客户表';


CREATE TABLE `crm_market_activity_customer_his` (
  `ITM_ID` int(11) NOT NULL COMMENT '推送组ID',
  `CST_ID` bigint(20) NOT NULL COMMENT '顾客ID',
  `BATCH_ID` bigint(20) NOT NULL COMMENT '批次ID',
  `STAT` int(11) NOT NULL DEFAULT '0' COMMENT '人员状态,0:默认状态,1:积分或券成功;2:失败;3:对照客户;4:设置积分不足不发客户;5:设置券不足不发客户;6:实际积分发送失败;7:实际券发送失败;8:通道信息发送失败;9:推送限制,如近期已发过,10:短信发送成功',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  UNIQUE KEY `UNIQUE_ITEM` (`CST_ID`,`BATCH_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='主动营销客户表,历史记录';

	
CREATE TABLE `crm_order_account` (
  `SLD_ID` bigint(20) NOT NULL COMMENT '订单ID',
  `ACT_ID` bigint(20) NOT NULL COMMENT '账套ID',
  UNIQUE KEY `SLD_ID` (`SLD_ID`,`ACT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_order_account` */

/*Table structure for table `crm_order_address` */

CREATE TABLE `crm_order_address` (
  `ADDR_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '地址ID',
  `SLD_ID` bigint(20) NOT NULL COMMENT '订单ID',
  `COUNTRY` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '国家',
  `PROVINCE` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '省',
  `CITY` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '市',
  `COUNTY` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '县',
  `DETAIL` varchar(128) COLLATE utf8_bin NOT NULL COMMENT '详细地址',
  `ZIP_CODE` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '邮编',
  `RCV_NAME` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '收件人姓名',
  `TEL` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '电话',
  `MOBILE` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '手机',
  PRIMARY KEY (`ADDR_ID`),
	KEY `SLD_ID`(`SLD_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='销售订单中地址信息';

/*Data for the table `crm_order_address` */

/*Table structure for table `crm_order_coupons` */

CREATE TABLE `crm_order_coupons` (
  `RCPT_ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '回执ID',
  `CPN_ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '券ID',
  `CPL_ID` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '券型ID',
  KEY `CPN_ID` (`CPN_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='销售订单中用到的券';

/*Data for the table `crm_order_coupons` */

/*Table structure for table `crm_order_delivery` */

CREATE TABLE `crm_order_delivery` (
  `DLV_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '配送ID',
  `SLD_ID` bigint(20) NOT NULL COMMENT '订单ID',
  `DLV_TYPE` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '配送方式',
  `DLV_STAT` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '发货状态',
  `SIT_ID` bigint(20) DEFAULT NULL COMMENT '自提店铺',
  `ALLOCATE_TIME` datetime DEFAULT NULL COMMENT '配货时间',
  `OUTPUT_TIME` datetime DEFAULT NULL COMMENT '出库时间',
  `EMS_COMPANY` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '快递公司',
  `EMS_NUM` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '快递单号',
  `APNT_TIME` datetime DEFAULT NULL COMMENT '预约时间',
  `RCV_TIME` datetime DEFAULT NULL COMMENT '收货时间',
  PRIMARY KEY (`DLV_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='销售订单的配送信息';

/*Data for the table `crm_order_delivery` */

/*Table structure for table `crm_order_evaluate` */

CREATE TABLE `crm_order_evaluate` (
  `SLD_ID` bigint(20) NOT NULL COMMENT '订单ID',
  `EVLT_RANK` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '评价等级',
  `EVLT_CONTENT` text COLLATE utf8_bin NOT NULL COMMENT '评价内容',
  `EVLT_TIME` datetime DEFAULT NULL COMMENT '评价时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='订单的评论信息';

/*Data for the table `crm_order_evaluate` */

/*Table structure for table `crm_order_invoice` */

CREATE TABLE `crm_order_invoice` (
  `INV_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '发票ID',
  `SLD_ID` bigint(20) NOT NULL COMMENT '订单ID',
  `INV_TYPE` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '发票类型',
  `INV_TITLE` varchar(128) COLLATE utf8_bin NOT NULL COMMENT '抬头',
  `INV_CONTENT` varchar(256) COLLATE utf8_bin NOT NULL COMMENT '内容',
  `INV_TIME` datetime DEFAULT NULL COMMENT '时间',
  PRIMARY KEY (`INV_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='订单的发票信息';

/*Data for the table `crm_order_invoice` */

/*Table structure for table `crm_order_item` */

CREATE TABLE `crm_order_item` (
  `ITM_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '订单项ID',
  `SLD_ID` bigint(20) NOT NULL COMMENT '订单ID',
  `ART_NUM` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '货号',
  `SKU` varchar(32) COLLATE utf8_bin NOT NULL COMMENT 'SKU',
  `ART_NAME` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '名称',
  `SPEC` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '规格',
  `QUANTITY` int(11) DEFAULT '0' COMMENT '数量',
  `DISCOUNT` double DEFAULT '1' COMMENT '折扣',
  `PRICE` decimal(10,2) DEFAULT '0.00' COMMENT '价格',
  `END_PRICE` decimal(10,2) DEFAULT '0.00' COMMENT '结算价',
	`FACT_QUANTITY` int(11)  NULL DEFAULT '0' COMMENT '实际数量',
	`FACT_PRM_PRICE` decimal(10,2) NULL DEFAULT '0.00' COMMENT '实际牌价价格',
	`FACT_END_PRICE` decimal(10,2) NULL DEFAULT '0.00' COMMENT '实际结算价格',
  `POINTS` double DEFAULT '0' COMMENT '积分',
  `REFERRER` bigint(20) DEFAULT NULL COMMENT '推荐人',
  `GUIDE` bigint(20) DEFAULT NULL COMMENT '导购',
  `GUIDER` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '导购编号',
  PRIMARY KEY (`ITM_ID`),
	KEY `SLD_ID` (`SLD_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='订单项';

/*Data for the table `crm_order_item` */

/*Table structure for table `crm_order_item_promotion` */

CREATE TABLE `crm_order_item_promotion` (
  `ITM_ID` bigint(20) NOT NULL COMMENT '订单项ID',
  `ACT_CODE` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '促销活动',
  `REMARK` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '备注',
	KEY `ITM_ID` (`ITM_ID`),
	KEY `ACT_CODE` (`ACT_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_order_item_promotion` */

/*Table structure for table `crm_order_payment` */

CREATE TABLE `crm_order_payment` (
  `SLD_ID` bigint(20) NOT NULL COMMENT '订单ID',
  `PAY_ID` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '支付方式',
  `SERIAL_NUM` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '流水号',
  `AMOUNT` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '金额',
	KEY `SLD_ID` (`SLD_ID`),
	KEY `PAY_ID` (`PAY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_order_payment` */

/*Table structure for table `crm_order_points` */

CREATE TABLE `crm_order_points` (
  `RCPT_ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '回执ID',
  `BLC_ACT` bigint(20) NOT NULL COMMENT '结束账套',
  `ACT_ID` bigint(20) NOT NULL COMMENT '账套ID',
  `PNT_TYPE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '积分类型',
  `PNT_VAL` double NOT NULL DEFAULT '0' COMMENT '积分值',
  KEY `RCPT_ID` (`RCPT_ID`),
  CONSTRAINT `crm_order_points_ibfk_1` FOREIGN KEY (`RCPT_ID`) REFERENCES `crm_order_receipt` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='订单管连的积分信息';

/*Data for the table `crm_order_points` */

/*Table structure for table `crm_order_promotion` */

CREATE TABLE `crm_order_promotion` (
  `SLD_ID` bigint(20) NOT NULL COMMENT '销售订单ID',
  `ACT_CODE` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '促销活动ID',
  `REMARK` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '备注'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='销售订单参与的促销活动';

/*Data for the table `crm_order_promotion` */

/*Table structure for table `crm_order_receipt` */

CREATE TABLE `crm_order_receipt` (
  `ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '回执ID',
  `TYPE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '类型',
  `SERVICE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '业务',
  `ORDER_ID` varchar(36) COLLATE utf8_bin NOT NULL COMMENT '订单号',
  `ORDER_SRC` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '订单渠道',
  `CST_ID` bigint(20) DEFAULT NULL COMMENT '顾客ID',
  `SIT_ID` bigint(20) DEFAULT NULL COMMENT '网点ID',
  `ORDER_AMOUNT` decimal(10,2) DEFAULT NULL COMMENT '订单金额',
  `ORDER_DESC` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '订单摘要',
  `CONFIRM` tinyint(1) DEFAULT '0' COMMENT '是否确认',
  `USR_CONFIRM` tinyint(4) DEFAULT '0' COMMENT '用户确认状态',
  `ENABLE` tinyint(1) DEFAULT '1' COMMENT '是否有效',
  `CONFIRM_TYPE` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '验证类型',
  `REMARK` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '备注',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ORDER_ID` (`ORDER_ID`,`ORDER_SRC`,`SERVICE`,`TYPE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='订单操作回执';

/*Data for the table `crm_order_receipt` */

/*Table structure for table `crm_payment` */

CREATE TABLE `crm_payment` (
  `PAY_ID` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '支付ID',
  `PAY_NAME` varchar(60) COLLATE utf8_bin NOT NULL COMMENT '支付名称',
  `PAY_ICON` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '支付图标',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  `REMARK` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`PAY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_payment` */

/*Table structure for table `crm_point_adjust` */

CREATE TABLE `crm_point_adjust` (
  `ADJ_ID` varchar(16) NOT NULL COMMENT '主键ID,单据编号',
  `ACT_ID` bigint(20) NOT NULL COMMENT '帐套id',
  `PNT_TYPE` varchar(16) NOT NULL COMMENT '积分类型',
  `BLC_ACT` bigint(11) NOT NULL COMMENT '结算主体',
  `DEF_DAYS` int(11) DEFAULT NULL COMMENT '有效天数',
  `APRO_STAT` tinyint(4) DEFAULT '0' COMMENT '审核状态（0：创建中，1：待审批，2：已核准，3：已驳回，4：已不准）',
  `OPT_STAT` tinyint(4) DEFAULT '0' COMMENT '操作状态（0：未执行，1：正在执行，2：执行成功）',
  `TOTAL_NUM` bigint(20) DEFAULT '0' COMMENT '总数量',
  `FAILED_NUM` bigint(20) DEFAULT '0' COMMENT '失败的数量',
  `ENABLE` tinyint(1) DEFAULT '1' COMMENT '是否可用',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(11) DEFAULT NULL COMMENT '记录人',
  `REMARK` varchar(200) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`ADJ_ID`),
  KEY `ACT_ID` (`ACT_ID`),
  KEY `BLC_ACT` (`BLC_ACT`),
  KEY `REC_USER` (`REC_USER`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='积分调整主表';

/*Data for the table `crm_point_adjust` */

/*Table structure for table `crm_point_adjust_detail` */

CREATE TABLE `crm_point_adjust_detail` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `ADJ_ID` varchar(16) NOT NULL COMMENT '调整主表编号',
  `CST_ID` bigint(20) NOT NULL COMMENT '顾客id',
  `POINTS` double NOT NULL COMMENT '调整积分',
  `ORDER_ID` varchar(32) DEFAULT NULL COMMENT '订单ID',
  `ORDER_SRC` varchar(16) DEFAULT NULL COMMENT '订单渠道',
  `S_TIME` datetime DEFAULT NULL COMMENT '开始时间',
  `E_TIME` datetime DEFAULT NULL COMMENT '结束时间',
  `STATUS` tinyint(4) DEFAULT '0' COMMENT '状态（0：未调整，1：已调整，-1：调整失败）',
  `RESULT_MEMO` varchar(64) DEFAULT NULL COMMENT '结果备注',
  `REMARK` varchar(200) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ADJ_ID` (`ADJ_ID`,`CST_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='积分调整明细表';

/*Data for the table `crm_point_adjust_detail` */

/*Table structure for table `crm_points_account` */

CREATE TABLE `crm_points_account` (
  `PNT_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '积分账户ID',
  `ACT_ID` bigint(20) NOT NULL COMMENT 'CRM账套ID',
  `PNT_TYPE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '积分类型',
  `CST_ID` bigint(20) NOT NULL COMMENT '顾客ID',
  `PNT_VAL` double(12,2) DEFAULT '0.00' COMMENT '积分值',
  `TOTAL_ALL` double(12,2) DEFAULT '0.00' COMMENT '历史累计',
  `TOTAL_THIS` double(12,2) DEFAULT '0.00' COMMENT '本账期累计',
  `S_TIME` datetime DEFAULT NULL COMMENT '开始时间',
  `E_TIME` datetime DEFAULT NULL COMMENT '结束时间',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`PNT_ID`),
  UNIQUE KEY `CST_ID` (`CST_ID`,`ACT_ID`,`PNT_TYPE`),
  KEY `ACT_ID` (`ACT_ID`,`PNT_TYPE`),
  CONSTRAINT `crm_points_account_ibfk_1` FOREIGN KEY (`ACT_ID`, `PNT_TYPE`) REFERENCES `crm_points_set` (`ACT_ID`, `PNT_TYPE`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_points_account` */

/*Table structure for table `crm_points_change` */

CREATE TABLE `crm_points_change` (
  `PNT_ID` bigint(20) NOT NULL COMMENT '积分账户ID',
  `TYPE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '积分变更类型',
  `PNT_VAL` double NOT NULL COMMENT '积分值（正负）',
  `FACT_PNT` double DEFAULT NULL COMMENT '实际积分值',
  `CST_ID` bigint(20) NOT NULL COMMENT '顾客ID',
  `BLC_IN` bigint(20) DEFAULT NULL COMMENT '产生积分结算账套ID',
  `BLC_OUT` bigint(20) DEFAULT NULL COMMENT '消费积分结算账套ID',
  `S_TIME` datetime DEFAULT NULL COMMENT '积分的开始时间',
  `E_TIME` datetime DEFAULT NULL COMMENT '积分的结束时间',
  `CHL_ID` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '积分变更途径',
  `CHL_INST` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '积分变更途径值',
  `LOCKED` tinyint(1) DEFAULT NULL COMMENT '是否锁定',
  `U_V` decimal(12,6) DEFAULT NULL COMMENT '积分单位价值',
  `REC_TIME` datetime NOT NULL COMMENT '记录时间',
  KEY `PNT_ID` (`PNT_ID`),
  KEY `CST_ID` (`CST_ID`),
  KEY `CHL_ID` (`CHL_ID`,`CHL_INST`,`CST_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_points_change` */


CREATE TABLE `crm_points_event`(
	`PNT_ID` bigint(20) NOT NULL  COMMENT '积点账套ID' , 
	`EVT_ID` int(11) NOT NULL  COMMENT '事件ID' , 
	`GRP_ID` int(11) NOT NULL  COMMENT '事件处理分组ID' , 
	`REC_TIME` timestamp NULL  DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '记录时间' , 
	`REC_USER` bigint(20) NULL  COMMENT '记录人' , 
	UNIQUE KEY `PNT_ID`(`PNT_ID`,`EVT_ID`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Table structure for table `crm_points_item` */

CREATE TABLE `crm_points_item` (
  `ITM_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '积分项ID',
  `PNT_ID` bigint(20) NOT NULL COMMENT '积分账户ID',
  `PNT_VAL` double(12,2) NOT NULL DEFAULT '0.00' COMMENT '积分值',
  `BLC_ACT` bigint(20) NOT NULL COMMENT '结算账套',
  `S_TIME` datetime DEFAULT NULL COMMENT '有效期开始',
  `E_TIME` datetime DEFAULT NULL COMMENT '有效期结束',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`ITM_ID`),
  UNIQUE KEY `PNT_ID` (`PNT_ID`,`E_TIME`,`S_TIME`,`BLC_ACT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_points_item` */

/*Table structure for table `crm_points_payment` */

CREATE TABLE `crm_points_payment` (
  `ACT_ID` bigint(20) NOT NULL COMMENT '账套ID',
  `PAY_ID` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '支付方式ID',
  UNIQUE KEY `ACT_ID` (`ACT_ID`,`PAY_ID`),
  CONSTRAINT `crm_points_payment_ibfk_1` FOREIGN KEY (`ACT_ID`) REFERENCES `crm_account_set` (`ACT_ID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='ＣＲＭ账套允许积分的收款方式';

/*Data for the table `crm_points_payment` */

CREATE TABLE `crm_points_receipt` (
  `ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '回执ID',
  `CHANNEL` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '积分变更渠道',
  `USR` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '代理对象',
  `TYPE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '积分变更类型',
  `MODE` tinyint(4) NOT NULL DEFAULT '0' COMMENT '方式',
  `BIZ_TYPE` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '业务类型',
  `BIZ_NO` varchar(36) COLLATE utf8_bin NOT NULL COMMENT '业务ID',
  `CST_ID` bigint(20) NOT NULL COMMENT '顾客ID',
  `ACT_ID` bigint(20) DEFAULT NULL COMMENT '账套ID',
  `BLC_ID` bigint(20) DEFAULT NULL COMMENT '结算账套ID',
  `PNT_TYPE` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '积点类型',
  `PNT_VAL` double(12,2) DEFAULT NULL COMMENT '积点值',
  `FACT_PRICE` decimal(10,2) DEFAULT NULL COMMENT '实际金额',
  `LOCKED` tinyint(1) DEFAULT '0' COMMENT '是否冻结',
  `CONFIRM` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否确认',
  `CONFIRM_TYPE` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '确认类型',
  `USR_CONFIRM` tinyint(4) DEFAULT '0' COMMENT '用户是否确认',
  `REMARK` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT '描述',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `BIZ_ID` (`CHANNEL`,`TYPE`,`BIZ_NO`,`BIZ_TYPE`),
	KEY `CHANNEL`(`USR`,`CHANNEL`) , 
  KEY `CST_ID`(`CST_ID`,`CONFIRM`,`CONFIRM_TYPE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Table structure for table `crm_points_set` */

CREATE TABLE `crm_points_set` (
  `PNT_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '积分账套ID',
  `ACT_ID` bigint(20) NOT NULL COMMENT 'CRM账套ID',
  `PNT_TYPE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '积分类型',
  `PNT_SET_NAME` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '积分账套名称',
  `PNT_UNIT` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '积分单位，默认''分''',
  `PNT_U_V` double(12,6) NOT NULL DEFAULT '1.000000' COMMENT '积分单位价值',
  `EXCHANGE` tinyint(1) DEFAULT '1' COMMENT '是否允许通兑',
  `IS_FANS` tinyint(1) DEFAULT '0' COMMENT '粉丝是否适用',
  `ENABLE` tinyint(1) DEFAULT '1' COMMENT '是否可用',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`PNT_ID`),
  UNIQUE KEY `actId` (`ACT_ID`,`PNT_TYPE`),
  KEY `PNT_TYPE` (`PNT_TYPE`),
  CONSTRAINT `crm_points_set_ibfk_1` FOREIGN KEY (`ACT_ID`) REFERENCES `crm_account_set` (`ACT_ID`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `crm_points_set_ibfk_2` FOREIGN KEY (`PNT_TYPE`) REFERENCES `crm_points_type` (`PNT_TYPE`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='CRM积点账套';

/*Data for the table `crm_points_set` */

/*Table structure for table `crm_points_set_cash` */

CREATE TABLE `crm_points_set_cash` (
  `PNT_ID` bigint(20) NOT NULL COMMENT '积分账套ID',
  `PLC_ID` bigint(20) DEFAULT NULL COMMENT '策略ID',
  PRIMARY KEY (`PNT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_points_set_cash` */

/*Table structure for table `crm_points_set_expiry` */

CREATE TABLE `crm_points_set_expiry` (
  `PNT_ID` bigint(20) NOT NULL COMMENT '积分账套ID',
  `PLC_ID` bigint(20) NOT NULL COMMENT '策略ID',
  KEY `PLC_ID` (`PLC_ID`),
  KEY `PNT_ID` (`PNT_ID`),
  CONSTRAINT `crm_points_set_expiry_ibfk_1` FOREIGN KEY (`PLC_ID`) REFERENCES `crm_policy` (`PLC_ID`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `crm_points_set_expiry_ibfk_2` FOREIGN KEY (`PNT_ID`) REFERENCES `crm_points_set` (`PNT_ID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_points_set_expiry` */

/*Table structure for table `crm_points_type` */

CREATE TABLE `crm_points_type` (
  `PNT_TYPE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '积分类型代号',
  `PNT_NAME` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '积分名称',
  `PNT_DESC` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '积分描述',
  `IS_SYSTEM` tinyint(1) DEFAULT '0' COMMENT '是否系统保留',
  `IS_DEFAULT` tinyint(1) DEFAULT '0' COMMENT '是否是积分变更的默认积分类型',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`PNT_TYPE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='CRM积点类型';

/*Data for the table `crm_points_type` */



/*Table structure for table `crm_policy` */

CREATE TABLE `crm_policy` (
  `PLC_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '策略ID',
  `TPL_ID` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '模板ID',
  `PLC_DESC` text COLLATE utf8_bin COMMENT '策略描述',
  `PLC_FRONT_DATA` text COLLATE utf8_bin COMMENT '策略数据',
  `PLC_INNER_DATA` text COLLATE utf8_bin COMMENT '策略内部数据',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`PLC_ID`),
  KEY `TPL_ID` (`TPL_ID`),
  CONSTRAINT `crm_policy_ibfk_1` FOREIGN KEY (`TPL_ID`) REFERENCES `crm_template` (`TPL_ID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_policy` */

/*Table structure for table `crm_policy_discount_item` */

CREATE TABLE `crm_policy_discount_item` (
  `PLCY_ID` bigint(20) NOT NULL COMMENT '策略ID',
  `PLCY_TYPE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '策略类型',
  `S_DISCOUNT` double NOT NULL COMMENT '开始折扣',
  `E_DISCOUNT` double NOT NULL COMMENT '结束折扣',
  `values` double NOT NULL COMMENT '值'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_policy_discount_item` */

/*Table structure for table `crm_policy_goods` */

CREATE TABLE `crm_policy_goods` (
  `PLCY_ID` bigint(20) NOT NULL COMMENT '策略ID',
  `PLCY_TYPE` tinyint(4) NOT NULL COMMENT '策略类型',
  `GDS_ID` bigint(20) NOT NULL COMMENT '商品ID',
  UNIQUE KEY `PLCY_ID` (`PLCY_ID`,`PLCY_TYPE`,`GDS_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_policy_goods` */

/*Table structure for table `crm_policy_goods_group` */

CREATE TABLE `crm_policy_goods_group` (
  `PLCY_ID` bigint(20) NOT NULL COMMENT '策略ID',
  `PLCY_TYPE` tinyint(4) NOT NULL COMMENT '策略类型',
  `GRP_ID` bigint(20) NOT NULL COMMENT '商品分类ID',
  UNIQUE KEY `PLCY_ID` (`PLCY_ID`,`PLCY_TYPE`,`GRP_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_policy_goods_group` */

CREATE TABLE `crm_range` (
  `RNG_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `RNG_OBJECT` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '选择的对象',
  PRIMARY KEY (`RNG_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `crm_range_goods` (
  `RNG_ID` int(11) NOT NULL COMMENT '策略ID',
  `GDS_ID` bigint(20) NOT NULL COMMENT '商品ID',
  UNIQUE KEY `RNG_ID` (`RNG_ID`,`GDS_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `crm_range_goods_brand` (
  `RNG_ID` int(11) NOT NULL COMMENT '策略ID',
  `BND_ID` int(11) NOT NULL COMMENT '品牌ID',
  UNIQUE KEY `RNG_ID` (`RNG_ID`,`BND_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `crm_range_goods_group` (
  `RNG_ID` int(11) NOT NULL COMMENT '策略ID',
  `GRP_ID` int(11) NOT NULL COMMENT '商品分类ID',
  UNIQUE KEY `RNG_ID` (`RNG_ID`,`GRP_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `crm_range_site` (
  `RNG_ID` int(11) NOT NULL COMMENT '策略ID',
  `SIT_ID` int(11) NOT NULL COMMENT '网点ID',
  UNIQUE KEY `RNG_ID` (`RNG_ID`,`SIT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Table structure for table `crm_rank_policy_change` */

CREATE TABLE `crm_rank_policy_change` (
  `ACT_ID` bigint(20) NOT NULL COMMENT '账套ID',
  `RANK_FROM` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '开始等级',
  `PLC_ID` bigint(20) NOT NULL COMMENT '策略类型',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  UNIQUE KEY `ACT_ID` (`ACT_ID`,`RANK_FROM`),
  CONSTRAINT `crm_rank_policy_change_ibfk_1` FOREIGN KEY (`ACT_ID`) REFERENCES `crm_account_set` (`ACT_ID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='顾客升降级策略';

/*Data for the table `crm_rank_policy_change` */

/*Table structure for table `crm_rank_policy_discount` */

CREATE TABLE `crm_rank_policy_discount` (
  `PLCY_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '策略ID',
  `ACT_ID` bigint(20) NOT NULL COMMENT '账套ID',
  `RAK_ID` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '等级ID',
  `MAX_DISCOUNT` double NOT NULL COMMENT '最大综合折扣',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`PLCY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_rank_policy_discount` */

/*Table structure for table `crm_rank_policy_points` */

CREATE TABLE `crm_rank_policy_points` (
  `PLCY_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '策略ID',
  `ACT_ID` bigint(20) NOT NULL COMMENT '账套ID',
  `RAK_ID` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '等级ID',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`PLCY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_rank_policy_points` */

/*Table structure for table `crm_region` */

CREATE TABLE `crm_region` (
  `ID` bigint(20) NOT NULL COMMENT '行政区域ID',
  `TYPE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '类型',
  `NAME` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '名称',
  `PID` bigint(20) NOT NULL COMMENT '父ID',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '创建人',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_region` */

/*Table structure for table `crm_sales_order` */

CREATE TABLE `crm_sales_order` (
  `SLD_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '销售单ID',
  `ORDER_ID` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '订单ID',
  `ORDER_FORMER` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '原单ID',
  `ORDER_ORIGI` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '原始单号',
  `SLD_CHANNEL` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '渠道',
  `SLD_TYPE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '类型',
  `SLD_STAT` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '订单状态',
  `CST_ID` bigint(20) DEFAULT NULL COMMENT '顾客ID',
  `USR_NAME` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '线上顾客ID',
  `NICK_NAME` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '线上顾客昵称',
  `SIT_ID` bigint(20) DEFAULT NULL COMMENT '销售网点',
  `SIT_CODE` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '网点代号',
  `SLD_TIME` datetime DEFAULT NULL COMMENT '订单时间',
  `PAY_TIME` datetime DEFAULT NULL COMMENT '支付时间',
  `CONFIRM_TIME` datetime DEFAULT NULL COMMENT '确认时间',
  `PRE_SALE_TIME` datetime DEFAULT NULL COMMENT '预售时间',
  `QUANTITY` int(11) DEFAULT '0' COMMENT '数量',
  `DISCOUNT` double DEFAULT '1' COMMENT '折扣',
  `POINTS` double DEFAULT '0' COMMENT '积分值',
  `PRICE` decimal(10,2) DEFAULT '0.00' COMMENT '现价',
  `END_PRICE` decimal(10,2) DEFAULT '0.00' COMMENT '结算价',
  `FREIGHT` decimal(10,2) DEFAULT '0.00' COMMENT '运费',
  `POUNDAGE` decimal(10,2) DEFAULT '0.00' COMMENT '手续费',
  `FACT_PRICE` decimal(10,2) DEFAULT '0.00' COMMENT '实际金额',
  `CST_MESSAGE` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '顾客留言',
  `SLR_MESSAGE` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '商家留言',
  `PAYABLE` decimal(10,2) DEFAULT '0.00' COMMENT '应付',
  `PAID` decimal(10,2) DEFAULT '0.00' COMMENT '实付',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REMARK` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '订单备注',
  PRIMARY KEY (`SLD_ID`),
  KEY `ORDER_ID` (`ORDER_ID`,`SLD_CHANNEL`),
  KEY `CST_ID` (`CST_ID`),
  KEY `SIT_ID` (`SIT_ID`),
  KEY `SLD_TIME` (`SLD_TIME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='销售订单';

/*Data for the table `crm_sales_order` */

/*Table structure for table `crm_sales_order_abnormal` */

CREATE TABLE `crm_sales_order_abnormal` (
  `SLD_FORMER` varchar(36) COLLATE utf8_bin NOT NULL COMMENT '原单号',
  `SLD_CHNL` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '渠道',
  `END_PRICE` decimal(10,2) DEFAULT '0.00' COMMENT '金额',
  `RESULT` tinyint(1) DEFAULT '0' COMMENT '结果',
  KEY `SLD_FORMER` (`SLD_FORMER`,`SLD_CHNL`),
  KEY `RESULT` (`RESULT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_sales_order_abnormal` */

/*Table structure for table `crm_sales_type` */

CREATE TABLE `crm_sales_type` (
  `CHL_ID` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '渠道ID',
  `SLT_TYPE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '销售类型',
  `SLT_NAME` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '销售类型名称',
  `SLT_DESC` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '销售类型描述',
  `SAL_TYPE_ID` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '系统类型ID',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`CHL_ID`,`SLT_TYPE`),
  KEY `SAL_TYPE_ID` (`SAL_TYPE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='销售类型';

/*Data for the table `crm_sales_type` */

CREATE TABLE `crm_sequence` (
  `SEQ_NAME` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '序列名称',
  `CUR_VAL` int(11) DEFAULT NULL COMMENT '当前值',
  `INCRE` int(11) DEFAULT NULL COMMENT '自增值',
  PRIMARY KEY (`SEQ_NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;



/*Table structure for table `crm_site_group` */

CREATE TABLE `crm_site_group` (
  `GRP_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '网点组ID',
  `ACT_ID` bigint(20) NOT NULL COMMENT '账套ID',
  `GRP_NAME` varchar(64) NOT NULL COMMENT '网点组名称',
  `GRP_DESC` varchar(200) DEFAULT NULL COMMENT '网点组描述',
  `GRP_INDEX` int(11) DEFAULT '0' COMMENT '排序',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`GRP_ID`),
  KEY `ACT_ID` (`ACT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Table structure for table `crm_site_group_site` */

/*Table structure for table `crm_site_group_wxgzh` */

CREATE TABLE `crm_site_group_wxgzh` (
  `GRP_ID` int(11) NOT NULL COMMENT '分组ID',
  `GZH_ID` varchar(32) NOT NULL COMMENT '公众号ID',
  UNIQUE KEY `GRP_ID` (`GRP_ID`,`GZH_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Table structure for table `crm_system_setting` */

CREATE TABLE `crm_statements` (
  `ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT 'group ID',
  `INDEX` int(11) NOT NULL COMMENT '顺序',
  `STMT` text COLLATE utf8_bin NOT NULL COMMENT '语句',
  UNIQUE KEY `ID` (`ID`,`INDEX`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE /*!50032 IF NOT EXISTS */ `crm_system_setting` (
  `SYS_ATTR` varchar(32) NOT NULL COMMENT '系统属性',
  `SET_TYPE` varchar(16) DEFAULT NULL COMMENT '类型',
  `SYS_VAL` varchar(1024) NOT NULL COMMENT '值',
  PRIMARY KEY (`SYS_ATTR`),
  UNIQUE KEY `sysAttr` (`SYS_ATTR`,`SET_TYPE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `crm_system_setting` */

/*Table structure for table `crm_tag_key` */

CREATE TABLE `crm_tag_key` (
  `TAG_KEY` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '标签名ID',
  `TAG_NAME` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '标签名',
  `TAG_TYPE` tinyint(4) NOT NULL COMMENT '标签类型(手动，自动，模板)',
  `TAG_DESC` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '标签描述',
  `MULTIPLE` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否多个',
  `ENABLE` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否可用',
  `TAG_ACT` bigint(20) DEFAULT NULL COMMENT '所属账套',
  `PTF_CREATE` int(11) DEFAULT NULL COMMENT '创建平台',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
	`PLC_ID` bigint(20) DEFAULT NULL COMMENT '策略id',
  PRIMARY KEY (`TAG_KEY`),
  KEY `PTF_CREATE` (`PTF_CREATE`),
  KEY `REC_USER` (`REC_USER`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_tag_key` */

/*Table structure for table `crm_tag_val` */

CREATE TABLE `crm_tag_val` (
  `VAL_ID` varchar(20) COLLATE utf8_bin NOT NULL COMMENT '标签值ID',
  `TAG_KEY` bigint(20) NOT NULL COMMENT '标签名',
  `TAG_VAL` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '标签值',
  `VAL_DESC` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '描述',
  `EDITABLE` tinyint(1) DEFAULT '1' COMMENT '是否可编辑',
	`PLC_ID` bigint(20) DEFAULT NULL COMMENT '策略ID',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  UNIQUE KEY `TAG_KEY_ID` (`TAG_KEY`,`TAG_VAL`),
  UNIQUE KEY `TAG_KEY` (`TAG_KEY`,`VAL_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_tag_val` */


CREATE TABLE `crm_tag_platform`(
	`TAG_KEY` bigint(20) NOT NULL  COMMENT '标签' , 
	`PTF_TYPE` int(11) NOT NULL  COMMENT '平台类型' , 
	UNIQUE KEY `TAG_KEY`(`TAG_KEY`,`PTF_TYPE`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


CREATE TABLE `crm_tag_template_popular_argument` (
	`TAG_KEY` bigint(20) NOT NULL COMMENT '标签ID',
  `S_TIME_FLAG` tinyint(4) DEFAULT NULL COMMENT '开始时间是否固定值,0:固定,1: 参数',
  `S_TIME` date DEFAULT NULL COMMENT '开始时间 格式 2015-06-01',
  `S_TIME_TYPE` varchar(128) DEFAULT NULL COMMENT 'S_日期选项,格式:currentTime:当前时间,REG_TIME:入会日期,VERIFY_TIME:认证日期',
  `S_RANGE` varchar(8) DEFAULT NULL COMMENT 'S_前后,格式:0,1;0:前1:后',
  `S_DIF` int(11) DEFAULT NULL COMMENT 'S_前后数值',
  `S_UNIT` varchar(32) DEFAULT NULL COMMENT 'S_日期单位;DAY,WEEK,MONTH,YEAR',
  `E_TIME_FLAG` tinyint(4) DEFAULT NULL COMMENT '结束时间是否固定值',
  `E_TIME` date DEFAULT NULL COMMENT '结束时间',
  `E_TIME_TYPE` varchar(128) DEFAULT NULL COMMENT 'E_日期选项',
  `E_RANGE` varchar(8) DEFAULT NULL COMMENT 'E_前后',
  `E_DIF` int(11) DEFAULT NULL COMMENT 'E_前后数值',
  `E_UNIT` varchar(32) DEFAULT NULL COMMENT 'E_日期单位',
  `SITE_GROUP` int(11) DEFAULT NULL COMMENT '网点组 ,来源crm_site_group',
  `PRO_GROUP` int(11) DEFAULT NULL COMMENT '商品组 ,来源未定 ',
  `CST_BEHAVIOR` varchar(32) DEFAULT NULL COMMENT '行为,BUY,BROWSE,COLLECT',
  `PRO_CATE` varchar(32) DEFAULT NULL COMMENT '类型,BRAND',
  `TOP_N` int(11) DEFAULT NULL COMMENT 'top_n,数字',
  `LOGIC` varchar(32) DEFAULT NULL COMMENT 'and or 保留字段',
  UNIQUE KEY `ID` (`TAG_KEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='顾客喜好模板标签_参数结构表';


CREATE TABLE `crm_tag_template_rfm_argument` (
  `TAG_KEY` bigint(20) NOT NULL COMMENT '标签ID',
  `S_TIME_FLAG` tinyint(4) DEFAULT NULL COMMENT '开始时间是否固定值,0:固定,1: 参数',
  `S_TIME` date DEFAULT NULL COMMENT '开始时间 格式 2015-06-01',
  `S_TIME_TYPE` varchar(128) DEFAULT NULL COMMENT 'S_日期选项,格式:currentTime:当前时间,REG_TIME:入会日期,VERIFY_TIME:认证日期',
  `S_RANGE` varchar(8) DEFAULT NULL COMMENT 'S_前后,格式:0,1;0:前1:后',
  `S_DIF` int(11) DEFAULT NULL COMMENT 'S_前后数值',
  `S_UNIT` varchar(32) DEFAULT NULL COMMENT 'S_日期单位DAY,WEEK,MONTH,YEAR',
  `E_TIME_FLAG` tinyint(4) DEFAULT NULL COMMENT '结束时间是否固定值',
  `E_TIME` date DEFAULT NULL COMMENT '结束时间',
  `E_TIME_TYPE` varchar(128) DEFAULT NULL COMMENT 'E_日期选项',
  `E_RANGE` varchar(8) DEFAULT NULL COMMENT 'E_前后',
  `E_DIF` int(11) DEFAULT NULL COMMENT 'E_前后数值',
  `E_UNIT` varchar(32) DEFAULT NULL COMMENT 'E_日期单位',
  `ACT_ID` bigint(20) DEFAULT NULL COMMENT '账套ID',
  `MERGE` tinyint(4) DEFAULT NULL COMMENT '是否合并为一次销售;格式:0,1;0:不合并,1:合并',
  `SITE_GROUP` int(11) DEFAULT NULL COMMENT '网点组 ,来源crm_site_group',
  `PRO_GROUP` int(11) DEFAULT NULL COMMENT '商品组 ,来源未定 ',
  `RECENCY` varchar(128) DEFAULT NULL COMMENT 'R,未用,直接去crm_tag_val获取',
  `FREQUENCY` varchar(128) DEFAULT NULL COMMENT 'F,未用,直接去crm_tag_val获取',
  `MONETARY` varchar(128) DEFAULT NULL COMMENT 'M,未用,直接去crm_tag_val获取',
  `LOGIC` varchar(32) DEFAULT NULL COMMENT 'and or 保留字段',
  UNIQUE KEY `ID`(`TAG_KEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='RFM模板标签_参数结构表';

/*Table structure for table `crm_task_result` */

CREATE TABLE `crm_task_result` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `JOB_ID` varchar(36) COLLATE utf8_bin NOT NULL COMMENT 'jobID',
  `BIZ` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '业务',
  `OP` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '操作',
  `DESC` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '描述',
  `RESULT` tinyint(4) NOT NULL COMMENT '结果',
  `FRI_TIME` datetime DEFAULT NULL COMMENT '触发时间',
  `SCD_TIME` datetime DEFAULT NULL COMMENT '实际执行时间',
  `EXE_TIME` bigint(20) DEFAULT NULL COMMENT '持续时间',
  `REMARK` text COLLATE utf8_bin COMMENT '备注',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_task_result` */

/*Table structure for table `crm_template` */

CREATE TABLE `crm_template` (
  `TPL_ID` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '模板ID',
  `SCOPE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '模板类型',
  `TPL_DESC` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '模板描述',
  `EXE_TYPE` tinyint(4) NOT NULL DEFAULT '0' COMMENT '执行器类型',
  `EXE_NAME` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '执行器名称',
  `EXECUTER` blob COMMENT '执行',
  `PSR_TYPE` tinyint(4) NOT NULL DEFAULT '0' COMMENT '解释器类型',
  `PSR_NAME` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '解析器名称',
  `PARSER` blob COMMENT '解析',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`TPL_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_template` */



/*Table structure for table `crm_variable` */

CREATE TABLE `crm_variable` (
  `VAR_SCOPE` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '变量域',
  `VAR_CODE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '变量code',
  `VAR_NAME` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '变量名称',
  `VAR_DESC` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '变量描述',
  `VAR_SYSTEM` tinyint(1) DEFAULT '0' COMMENT '是否系统',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`VAR_SCOPE`,`VAR_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


/*Data for the table `crm_variable` */


CREATE TABLE `crm_variable_type` (
  `VAR_SCOPE` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '变量域',
  `VAR_TYPE` int(1) DEFAULT NULL COMMENT '变量类型',
  `VAR_NAME` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '变量名称'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;


/*Table structure for table `crm_wxgzh_brand` */

CREATE TABLE `crm_wxgzh_brand` (
  `GZH_ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '公众号ID',
  `BND_ID` bigint(20) NOT NULL COMMENT '品牌ID',
  UNIQUE KEY `GZH_ID` (`GZH_ID`,`BND_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `crm_wxgzh_brand` */

CREATE TABLE `crm_wxgzh_site` (
  `GZH_ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '公众号ID',
  `SIT_CODE` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '网点代号',
  `STAT` tinyint(4) NOT NULL COMMENT '状态',
  `QRCODE` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '二维码',
  UNIQUE KEY `GZH_ID` (`GZH_ID`,`SIT_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


CREATE TABLE `excel_column` (
  `ROW_ID` bigint(32) NOT NULL COMMENT '行号: 系统保留字段，标识一条数据记录。',
  `CODE` varchar(16) NOT NULL COMMENT '列code',
  `NAME` varchar(128) DEFAULT NULL COMMENT '列名称',
  `SHEET_CODE` varchar(128) NOT NULL COMMENT 'sheet页code',
  `SHEET_NAME` varchar(128) DEFAULT NULL COMMENT 'sheet页名称',
  `SORT_CODE` int(11) DEFAULT NULL COMMENT '排序',
  `MIN_LENGTH` int(11) DEFAULT NULL COMMENT '列最小长度',
  `MAX_LENGTH` int(11) DEFAULT NULL COMMENT '列最大长度',
  `CODE_LIST` varchar(16) DEFAULT NULL COMMENT 'combobox型数据,对应combo code',
  `DATA_TYPE` varchar(128) DEFAULT NULL COMMENT '列数据类型',
  `PATTERN` varchar(256) DEFAULT NULL COMMENT '当前设置为填写正则表达式',
  `RULES` varchar(256) DEFAULT NULL COMMENT '列的规则定义组,规则之间以逗号分隔',
  `STATUS` char(1) NOT NULL DEFAULT '1' COMMENT '状态: 0 停用，1 有效。',
  `CREATED_USER` bigint(20) DEFAULT NULL COMMENT '创建人: 系统保留字段，记录插入时字段赋值为当前操作用户ID。',
  `CREATED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间: 系统保留字段，插入记录时字段赋值为当前系统时间戳。',
  `MODIFICATION_NUM` int(11) NOT NULL DEFAULT '0' COMMENT '更改次数: 记录被更改的次数。',
  `DELETED_FLAG` varchar(1) NOT NULL DEFAULT '0' COMMENT '记录删除标志: [0]-未删除;[1]-逻辑删除',
  `NULLABLE` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`ROW_ID`),
  UNIQUE KEY `CODE` (`CODE`,`SHEET_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='EXCEL导入功能，对列属性进行定义';

CREATE TABLE `excel_column_rule` (
  `ROW_ID` bigint(32) NOT NULL COMMENT '行号: 系统保留字段，标识一条数据记录。',
  `CODE` varchar(16) NOT NULL COMMENT '规则code',
  `NAME` varchar(128) NOT NULL COMMENT '规则名称',
  `MESSAGE` varchar(1024) DEFAULT NULL COMMENT '不符合规则的报错信息',
  `CREATED_USER` bigint(20) DEFAULT NULL COMMENT '创建人: 系统保留字段，记录插入时字段赋值为当前操作用户ID。',
  `CREATED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间: 系统保留字段，插入记录时字段赋值为当前系统时间戳。',
  `MODIFICATION_NUM` int(11) NOT NULL DEFAULT '0' COMMENT '更改次数: 记录被更改的次数。',
  `DELETED_FLAG` varchar(1) NOT NULL DEFAULT '0' COMMENT '记录删除标志: [0]-未删除;[1]-逻辑删除',
  PRIMARY KEY (`ROW_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='EXCEL导入功能，对列的规则进行定义';

CREATE TABLE `excel_error_procedure` (
  `ROW_ID` bigint(32) NOT NULL AUTO_INCREMENT COMMENT '行号: 系统保留字段，标识一条数据记录。',
  `ROWNUM` int(10) unsigned DEFAULT NULL COMMENT 'EXCEL中的行号',
  `COL_CODE` varchar(16) DEFAULT NULL COMMENT '列code',
  `MESSAGE` varchar(1024) DEFAULT NULL COMMENT '不符合规则的报错信息',
  `GROUP_ID` varchar(32) NOT NULL COMMENT '组id,多用户操作的时的区分字段',
  `CREATED_USER` bigint(20) DEFAULT NULL COMMENT '创建人: 系统保留字段，记录插入时字段赋值为当前操作用户ID。',
  `CREATED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间: 系统保留字段，插入记录时字段赋值为当前系统时间戳。',
  `MODIFICATION_NUM` int(11) NOT NULL DEFAULT '0' COMMENT '更改次数: 记录被更改的次数。',
  `DELETED_FLAG` varchar(1) NOT NULL DEFAULT '0' COMMENT '记录删除标志: [0]-未删除;[1]-逻辑删除',
  PRIMARY KEY (`ROW_ID`),
  KEY `groupdesc` (`GROUP_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='EXCEL导入功能，调用存储过程后插入的错误信息,,定时清除';

/*Table structure for table `qrtz_calendars` */

CREATE TABLE `qrtz_calendars` (
  `SCHED_NAME` varchar(120) NOT NULL,
  `CALENDAR_NAME` varchar(200) NOT NULL,
  `CALENDAR` blob NOT NULL,
  PRIMARY KEY (`SCHED_NAME`,`CALENDAR_NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `qrtz_calendars` */

/*Table structure for table `qrtz_job_details` */

CREATE TABLE `qrtz_job_details` (
  `SCHED_NAME` varchar(120) NOT NULL,
  `JOB_NAME` varchar(200) NOT NULL,
  `JOB_GROUP` varchar(200) NOT NULL,
  `DESCRIPTION` varchar(250) DEFAULT NULL,
  `JOB_CLASS_NAME` varchar(250) NOT NULL,
  `IS_DURABLE` varchar(1) NOT NULL,
  `IS_NONCONCURRENT` varchar(1) NOT NULL,
  `IS_UPDATE_DATA` varchar(1) NOT NULL,
  `REQUESTS_RECOVERY` varchar(1) NOT NULL,
  `JOB_DATA` blob,
  PRIMARY KEY (`SCHED_NAME`,`JOB_NAME`,`JOB_GROUP`),
  KEY `IDX_QRTZ_J_REQ_RECOVERY` (`SCHED_NAME`,`REQUESTS_RECOVERY`),
  KEY `IDX_QRTZ_J_GRP` (`SCHED_NAME`,`JOB_GROUP`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `qrtz_job_details` */

/*Table structure for table `qrtz_triggers` */

CREATE TABLE `qrtz_triggers` (
  `SCHED_NAME` varchar(120) NOT NULL,
  `TRIGGER_NAME` varchar(200) NOT NULL,
  `TRIGGER_GROUP` varchar(200) NOT NULL,
  `JOB_NAME` varchar(200) NOT NULL,
  `JOB_GROUP` varchar(200) NOT NULL,
  `DESCRIPTION` varchar(250) DEFAULT NULL,
  `NEXT_FIRE_TIME` bigint(13) DEFAULT NULL,
  `PREV_FIRE_TIME` bigint(13) DEFAULT NULL,
  `PRIORITY` int(11) DEFAULT NULL,
  `TRIGGER_STATE` varchar(16) NOT NULL,
  `TRIGGER_TYPE` varchar(8) NOT NULL,
  `START_TIME` bigint(13) NOT NULL,
  `END_TIME` bigint(13) DEFAULT NULL,
  `CALENDAR_NAME` varchar(200) DEFAULT NULL,
  `MISFIRE_INSTR` smallint(2) DEFAULT NULL,
  `JOB_DATA` blob,
  PRIMARY KEY (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
  KEY `SCHED_NAME` (`SCHED_NAME`,`JOB_NAME`,`JOB_GROUP`),
  KEY `IDX_QRTZ_T_J` (`SCHED_NAME`,`JOB_NAME`,`JOB_GROUP`),
  KEY `IDX_QRTZ_T_JG` (`SCHED_NAME`,`JOB_GROUP`),
  KEY `IDX_QRTZ_T_C` (`SCHED_NAME`,`CALENDAR_NAME`),
  KEY `IDX_QRTZ_T_G` (`SCHED_NAME`,`TRIGGER_GROUP`),
  KEY `IDX_QRTZ_T_STATE` (`SCHED_NAME`,`TRIGGER_STATE`),
  KEY `IDX_QRTZ_T_N_STATE` (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`,`TRIGGER_STATE`),
  KEY `IDX_QRTZ_T_N_G_STATE` (`SCHED_NAME`,`TRIGGER_GROUP`,`TRIGGER_STATE`),
  KEY `IDX_QRTZ_T_NEXT_FIRE_TIME` (`SCHED_NAME`,`NEXT_FIRE_TIME`),
  KEY `IDX_QRTZ_T_NFT_ST` (`SCHED_NAME`,`TRIGGER_STATE`,`NEXT_FIRE_TIME`),
  KEY `IDX_QRTZ_T_NFT_MISFIRE` (`SCHED_NAME`,`MISFIRE_INSTR`,`NEXT_FIRE_TIME`),
  KEY `IDX_QRTZ_T_NFT_ST_MISFIRE` (`SCHED_NAME`,`MISFIRE_INSTR`,`NEXT_FIRE_TIME`,`TRIGGER_STATE`),
  KEY `IDX_QRTZ_T_NFT_ST_MISFIRE_GRP` (`SCHED_NAME`,`MISFIRE_INSTR`,`NEXT_FIRE_TIME`,`TRIGGER_GROUP`,`TRIGGER_STATE`),
  CONSTRAINT `qrtz_triggers_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `JOB_NAME`, `JOB_GROUP`) REFERENCES `qrtz_job_details` (`SCHED_NAME`, `JOB_NAME`, `JOB_GROUP`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `qrtz_triggers` */

/*Table structure for table `qrtz_blob_triggers` */

CREATE TABLE `qrtz_blob_triggers` (
  `SCHED_NAME` varchar(120) NOT NULL,
  `TRIGGER_NAME` varchar(200) NOT NULL,
  `TRIGGER_GROUP` varchar(200) NOT NULL,
  `BLOB_DATA` blob,
  PRIMARY KEY (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
  KEY `SCHED_NAME` (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
  CONSTRAINT `qrtz_blob_triggers_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `qrtz_triggers` (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `qrtz_blob_triggers` */

/*Table structure for table `qrtz_cron_triggers` */

CREATE TABLE `qrtz_cron_triggers` (
  `SCHED_NAME` varchar(120) NOT NULL,
  `TRIGGER_NAME` varchar(200) NOT NULL,
  `TRIGGER_GROUP` varchar(200) NOT NULL,
  `CRON_EXPRESSION` varchar(120) NOT NULL,
  `TIME_ZONE_ID` varchar(80) DEFAULT NULL,
  PRIMARY KEY (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
  KEY `SCHED_NAME` (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
  CONSTRAINT `qrtz_cron_triggers_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `qrtz_triggers` (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `qrtz_cron_triggers` */

/*Table structure for table `qrtz_fired_triggers` */

CREATE TABLE `qrtz_fired_triggers` (
  `SCHED_NAME` varchar(120) NOT NULL,
  `ENTRY_ID` varchar(95) NOT NULL,
  `TRIGGER_NAME` varchar(200) NOT NULL,
  `TRIGGER_GROUP` varchar(200) NOT NULL,
  `INSTANCE_NAME` varchar(200) NOT NULL,
  `FIRED_TIME` bigint(13) NOT NULL,
  `PRIORITY` int(11) NOT NULL,
  `STATE` varchar(16) NOT NULL,
  `JOB_NAME` varchar(200) DEFAULT NULL,
  `JOB_GROUP` varchar(200) DEFAULT NULL,
  `IS_NONCONCURRENT` varchar(1) DEFAULT NULL,
  `REQUESTS_RECOVERY` varchar(1) DEFAULT NULL,
  `SCHED_TIME` bigint(13) NOT NULL,
  PRIMARY KEY (`SCHED_NAME`,`ENTRY_ID`),
  KEY `IDX_QRTZ_FT_TRIG_INST_NAME` (`SCHED_NAME`,`INSTANCE_NAME`),
  KEY `IDX_QRTZ_FT_INST_JOB_REQ_RCVRY` (`SCHED_NAME`,`INSTANCE_NAME`,`REQUESTS_RECOVERY`),
  KEY `IDX_QRTZ_FT_J_G` (`SCHED_NAME`,`JOB_NAME`,`JOB_GROUP`),
  KEY `IDX_QRTZ_FT_JG` (`SCHED_NAME`,`JOB_GROUP`),
  KEY `IDX_QRTZ_FT_T_G` (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
  KEY `IDX_QRTZ_FT_TG` (`SCHED_NAME`,`TRIGGER_GROUP`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `qrtz_fired_triggers` */


/*Table structure for table `qrtz_locks` */

CREATE TABLE `qrtz_locks` (
  `SCHED_NAME` varchar(120) NOT NULL,
  `LOCK_NAME` varchar(40) NOT NULL,
  PRIMARY KEY (`SCHED_NAME`,`LOCK_NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `qrtz_locks` */

/*Table structure for table `qrtz_paused_trigger_grps` */

CREATE TABLE `qrtz_paused_trigger_grps` (
  `SCHED_NAME` varchar(120) NOT NULL,
  `TRIGGER_GROUP` varchar(200) NOT NULL,
  PRIMARY KEY (`SCHED_NAME`,`TRIGGER_GROUP`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `qrtz_paused_trigger_grps` */

/*Table structure for table `qrtz_scheduler_state` */

CREATE TABLE `qrtz_scheduler_state` (
  `SCHED_NAME` varchar(120) NOT NULL,
  `INSTANCE_NAME` varchar(200) NOT NULL,
  `LAST_CHECKIN_TIME` bigint(13) NOT NULL,
  `CHECKIN_INTERVAL` bigint(13) NOT NULL,
  PRIMARY KEY (`SCHED_NAME`,`INSTANCE_NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `qrtz_scheduler_state` */

/*Table structure for table `qrtz_simple_triggers` */

CREATE TABLE `qrtz_simple_triggers` (
  `SCHED_NAME` varchar(120) NOT NULL,
  `TRIGGER_NAME` varchar(200) NOT NULL,
  `TRIGGER_GROUP` varchar(200) NOT NULL,
  `REPEAT_COUNT` bigint(7) NOT NULL,
  `REPEAT_INTERVAL` bigint(12) NOT NULL,
  `TIMES_TRIGGERED` bigint(10) NOT NULL,
  PRIMARY KEY (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
  KEY `SCHED_NAME` (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
  CONSTRAINT `qrtz_simple_triggers_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `qrtz_triggers` (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `qrtz_simple_triggers` */

/*Table structure for table `qrtz_simprop_triggers` */

CREATE TABLE `qrtz_simprop_triggers` (
  `SCHED_NAME` varchar(120) NOT NULL,
  `TRIGGER_NAME` varchar(200) NOT NULL,
  `TRIGGER_GROUP` varchar(200) NOT NULL,
  `STR_PROP_1` varchar(512) DEFAULT NULL,
  `STR_PROP_2` varchar(512) DEFAULT NULL,
  `STR_PROP_3` varchar(512) DEFAULT NULL,
  `INT_PROP_1` int(11) DEFAULT NULL,
  `INT_PROP_2` int(11) DEFAULT NULL,
  `LONG_PROP_1` bigint(20) DEFAULT NULL,
  `LONG_PROP_2` bigint(20) DEFAULT NULL,
  `DEC_PROP_1` decimal(13,4) DEFAULT NULL,
  `DEC_PROP_2` decimal(13,4) DEFAULT NULL,
  `BOOL_PROP_1` varchar(1) DEFAULT NULL,
  `BOOL_PROP_2` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
  CONSTRAINT `qrtz_simprop_triggers_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `qrtz_triggers` (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `qrtz_simprop_triggers` */


CREATE TABLE `duiba_points_order` (
  `uid` varchar(255) NOT NULL,
  `credits` int(11) DEFAULT NULL,
  `appkey` varchar(255) DEFAULT NULL,
  `timestamp` varchar(20) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `ordernum` varchar(255) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  `faceprice` int(11) DEFAULT NULL,
  `actualprice` int(11) DEFAULT NULL,
  `ip` varchar(255) DEFAULT NULL,
  `waitaudit` int(11) DEFAULT NULL,
  `params` varchar(255) DEFAULT NULL,
  `sign` varchar(255) DEFAULT NULL,
  `res_status` varchar(30) DEFAULT NULL,
  `res_bizid` varchar(255) DEFAULT NULL,
  `res_errormessage` varchar(255) DEFAULT NULL,
  `res_balance` int(11) DEFAULT NULL,
  `gxrq` datetime DEFAULT NULL,
  KEY `duiba_points_order_ordernum` (`ordernum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `duiba_points_order_result` (
  `appkey` varchar(255) DEFAULT NULL,
  `timestamp` varchar(20) DEFAULT NULL,
  `errormessage` varchar(255) DEFAULT NULL,
  `ordernum` varchar(255) DEFAULT NULL,
  `success` int(11) DEFAULT NULL,
  `bizid` varchar(255) DEFAULT NULL,
  `sign` varchar(255) DEFAULT NULL,
  `res_status` varchar(30) DEFAULT NULL,
  `gxrq` datetime DEFAULT NULL,
  `res_message` varchar(255) DEFAULT NULL,
  KEY `duiba_points_order_result_ordernum` (`ordernum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE `mt_product` (
  `P_ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '产品ID',
  `P_TYPE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '类型',
  `P_BRAND` int(11) NOT NULL COMMENT '品牌',
  `P_NAME` varchar(200) COLLATE utf8_bin NOT NULL COMMENT '产品名称',
  `P_TITLE` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT '产品标题',
  `PC_RETAIL` decimal(15,6) NOT NULL COMMENT '零售价',
  `PC_SOURCE` decimal(15,6) NOT NULL COMMENT '采购价',
  `PC_COST` decimal(15,6) NOT NULL COMMENT '成本价',
  `ENABLE` tinyint(1) DEFAULT '1' COMMENT '是否可用',
  `P_UNIT` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '单位',
  `PC_MAX` decimal(15,6) DEFAULT NULL COMMENT '最高价',
  `PC_MIN` decimal(15,6) DEFAULT NULL COMMENT '最低价',
  `P_IMAGE` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '产品主图',
	`SIZE_KIND` varchar(32) COLLATE utf8_bin DEFAULT NULL,
	`QTY_FLAG` tinyint(1) DEFAULT '1' COMMENT '是否需要计算数量',
  `P_ATTR1` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性1',
  `P_ATTR2` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性2',
  `P_ATTR3` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性3',
  `P_ATTR4` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性4',
  `P_ATTR5` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性5',
  `P_ATTR6` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性6',
  `P_ATTR7` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性7',
  `P_ATTR8` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性8',
  `P_ATTR9` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性9',
  `P_ATTR10` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性10',
  `P_ATTR11` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性11',
  `P_ATTR12` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性12',
  `P_ATTR13` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性13',
  `P_ATTR14` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性14',
  `P_ATTR15` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性15',
  `P_ATTR16` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性16',
  `P_ATTR17` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性17',
  `P_ATTR18` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性18',
  `P_ATTR19` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性19',
  `P_ATTR20` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性20',
  `P_ATTR21` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性21',
  `P_ATTR22` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性22',
  `P_ATTR23` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性23',
  `P_ATTR24` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性24',
  `P_ATTR25` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性25',
  `P_ATTR26` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性26',
  `P_ATTR27` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性27',
  `P_ATTR28` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性28',
  `P_ATTR29` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性29',
  `P_ATTR30` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性30',
  `P_CLAS1` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类1',
  `P_CLAS2` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类2',
  `P_CLAS3` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类3',
  `P_CLAS4` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类4',
  `P_CLAS5` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类5',
  `P_CLAS6` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类6',
  `P_CLAS7` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类7',
  `P_CLAS8` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类8',
  `P_CLAS9` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类9',
  `P_CLAS10` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类10',
  `P_CLAS11` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类11',
  `P_CLAS12` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类12',
  `P_CLAS13` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类13',
  `P_CLAS14` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类14',
  `P_CLAS15` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类15',
  `P_CLAS16` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类16',
  `P_CLAS17` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类17',
  `P_CLAS18` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类18',
  `P_CLAS19` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类19',
  `P_CLAS20` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类20',
  `P_CLAS21` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类21',
  `P_CLAS22` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类22',
  `P_CLAS23` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类23',
  `P_CLAS24` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类24',
  `P_CLAS25` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类25',
  `P_CLAS26` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类26',
  `P_CLAS27` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类27',
  `P_CLAS28` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类28',
  `P_CLAS29` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类29',
  `P_CLAS30` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类30',
  `P_CLAS31` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类31',
  `P_CLAS32` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类32',
  `P_CLAS33` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类33',
  `P_CLAS34` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类34',
  `P_CLAS35` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类35',
  `P_CLAS36` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类36',
  `P_CLAS37` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类37',
  `P_CLAS38` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类38',
  `P_CLAS39` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类39',
  `P_CLAS40` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类40',
  `P_CLAS41` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类41',
  `P_CLAS42` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类42',
  `P_CLAS43` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类43',
  `P_CLAS44` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类44',
  `P_CLAS45` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类45',
  `P_CLAS46` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类46',
  `P_CLAS47` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类47',
  `P_CLAS48` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类48',
  `P_CLAS49` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类49',
  `P_CLAS50` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类50',
  `REC_TIME` timestamp NULL DEFAULT NULL COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  `REMARK` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`P_ID`),
  KEY `P_TYPE` (`P_TYPE`),
  KEY `P_BRAND` (`P_BRAND`),
  KEY `REC_USER` (`REC_USER`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='产品档案';

/*Table structure for table `mt_product_attr` */

CREATE TABLE `mt_product_attr` (
  `PA_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `ATTR_ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '属性ID',
  `P_TYPE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '产品类型',
  `ATTR_SCOPE` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0：产品属性，1：sku属性',
  `ATTR_FIELD` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '属性字段',
  `ATTR_NAME` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '属性名称',
  `ATTR_DESC` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '属性描述',
  `ATTR_INDEX` int(11) DEFAULT NULL COMMENT '排序',
  `ATTR_TYPE` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '字段类型(_char,_num,_date,_select)',
  `ATTR_OPTIONS` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT '字段选项',
  `ATTR_VALID` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT '字段校验',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`PA_ID`),
  UNIQUE KEY `ATTR_ID` (`ATTR_ID`),
  UNIQUE KEY `P_TYPE` (`P_TYPE`,`ATTR_FIELD`),
  KEY `REC_USER` (`REC_USER`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='产品属性';

/*Table structure for table `mt_product_class` */

CREATE TABLE `mt_product_class` (
  `CLZ_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `P_CLZ` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '分类代号（全表唯一）',
  `GRP_ID` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '分类方法ID',
  `CLZ_PID` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '父分类ID（第一级为null）',
  `CLZ_NAME` varchar(200) COLLATE utf8_bin NOT NULL COMMENT '分类名称',
  `CLZ_DESC` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '描述',
  `CLZ_SHORT` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '简称',
  `CLZ_INDEX` int(11) DEFAULT NULL COMMENT '排序',
  `CLZ_LEVEL` tinyint(4) NOT NULL COMMENT '层级',
  `CLZ_FIELD` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '字段名',
  `ENABLE` tinyint(1) NOT NULL COMMENT '是否可用',
  `IS_LEAF` tinyint(1) NOT NULL COMMENT '是否末节点',
  `IS_PTYPE` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否是产品类型',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`CLZ_ID`),
  UNIQUE KEY `P_CLZ` (`P_CLZ`),
  KEY `CLZ_PID` (`CLZ_PID`),
  KEY `CLZ_FIELD` (`CLZ_FIELD`),
  KEY `REC_USER` (`REC_USER`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='产品分类';

/*Table structure for table `mt_product_class_channel` */

CREATE TABLE `mt_product_class_channel` (
  `GRP_ID` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '分类方法ID',
  `CHL_ID` int(11) NOT NULL COMMENT '渠道ID',
  UNIQUE KEY `GRP_ID` (`GRP_ID`,`CHL_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='分类适用渠道';

/*Table structure for table `mt_product_class_group` */

CREATE TABLE `mt_product_class_group` (
  `GRP_ID` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '分类方法ID',
  `GRP_NAME` varchar(200) COLLATE utf8_bin NOT NULL COMMENT '分类方法名称',
  `GRP_DESC` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '分类方法描述',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `CRT_PTF` int(11) DEFAULT NULL COMMENT '创建平台',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`GRP_ID`),
  KEY `CRT_PTF` (`CRT_PTF`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='产品分类方法';

/*Table structure for table `mt_product_class_platform` */

CREATE TABLE `mt_product_class_platform` (
  `GRP_ID` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '分类方法ID',
  `PTF_ID` int(11) NOT NULL COMMENT '平台ID',
  UNIQUE KEY `GRP_ID` (`GRP_ID`,`PTF_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='分类适用的平台';

/*Table structure for table `mt_product_sku` */

CREATE TABLE `mt_product_sku` (
  `SKU` varchar(32) COLLATE utf8_bin NOT NULL COMMENT 'sku',
  `P_ID` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '货号',
  `S_SPEC1` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '规格1',
  `S_SPEC2` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '规格2',
  `S_SPEC3` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '规格3',
  `S_SPEC4` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '规格4',
  `S_SPEC5` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '规格5',
  `S_RETAIL` decimal(15,6) NOT NULL COMMENT '零售价',
  `S_SOURCE` decimal(15,6) NOT NULL COMMENT '进价',
  `S_COST` decimal(15,6) NOT NULL COMMENT '成本价',
  `S_VOLUME` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '体积',
  `S_WEIGHT` double DEFAULT NULL COMMENT '重量',
  `S_IMAGE` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '图片',
  `S_ATTR1` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性1',
  `S_ATTR2` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性2',
  `S_ATTR3` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性3',
  `S_ATTR4` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性4',
  `S_ATTR5` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性5',
  `S_ATTR6` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性6',
  `S_ATTR7` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性7',
  `S_ATTR8` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性8',
  `S_ATTR9` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性9',
  `S_ATTR10` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性10',
  `S_ATTR11` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性11',
  `S_ATTR12` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性12',
  `S_ATTR13` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性13',
  `S_ATTR14` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性14',
  `S_ATTR15` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性15',
  `S_ATTR16` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性16',
  `S_ATTR17` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性17',
  `S_ATTR18` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性18',
  `S_ATTR19` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性19',
  `S_ATTR20` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '属性20',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`SKU`),
  KEY `P_ID` (`P_ID`),
  KEY `S_SPEC1` (`S_SPEC1`),
  KEY `S_SPEC2` (`S_SPEC2`),
  KEY `S_SPEC3` (`S_SPEC3`),
  KEY `S_SPEC4` (`S_SPEC4`),
  KEY `S_SPEC5` (`S_SPEC5`),
  KEY `REC_USER` (`REC_USER`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='sku';

/*Table structure for table `mt_product_spec` */

CREATE TABLE `mt_product_spec` (
  `SPEC_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `P_SPEC` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '规格代号(全表唯一)',
  `P_TYPE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '产品类型',
  `SPEC_FIELD` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '规格字段',
  `SPEC_NAME` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '规格名称',
  `SPEC_SHORT` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '规格简称',
  `SPEC_DESC` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '规格描述',
  `SPEC_INDEX` int(11) DEFAULT NULL COMMENT '排序',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`SPEC_ID`),
  UNIQUE KEY `P_SPEC` (`P_SPEC`),
  UNIQUE KEY `P_TYPE` (`P_TYPE`,`SPEC_FIELD`),
  UNIQUE KEY `P_TYPE_2` (`P_TYPE`,`SPEC_NAME`),
  UNIQUE KEY `P_TYPE_3` (`P_TYPE`,`SPEC_SHORT`),
  KEY `REC_USER` (`REC_USER`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='产品规格项';

/*Table structure for table `mt_product_spec_group` */

CREATE TABLE `mt_product_spec_group` (
  `GRP_ID` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '规格组ID',
  `GRP_NAME` varchar(200) COLLATE utf8_bin NOT NULL COMMENT '规格组名称',
  `GRP_DESC` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '规格组描述',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`GRP_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='规格组';

/*Table structure for table `mt_product_spec_val` */

CREATE TABLE `mt_product_spec_val` (
  `VAL_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `SPEC_VAL` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '规格值ID（全表唯一）',
  `P_TYPE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '产品类型(暂时冗余)',
  `P_SPEC` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '产品规格',
  `GRP_ID` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '规格组ID',
  `VAL_NAME` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '规格值内容',
  `VAL_INDEX` int(11) DEFAULT NULL COMMENT '排序',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`VAL_ID`),
  UNIQUE KEY `SPEC_VAL` (`SPEC_VAL`),
  UNIQUE KEY `P_SPEC` (`P_SPEC`,`VAL_NAME`,`GRP_ID`),
  KEY `REC_USER` (`REC_USER`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='产品规格值';

/*Table structure for table `mt_product_type` */

CREATE TABLE `mt_product_type` (
  `PDC_TYPE` varchar(16) COLLATE utf8_bin NOT NULL COMMENT '产品类型',
  `TYPE_NAME` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '类型名称',
  `TYPE_DESC` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '产品类型描述',
  `PDC_INDEX` int(11) DEFAULT NULL COMMENT '排序',
  `PDC_PID` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '父ID',
  `PDC_LEVEL` tinyint(4) NOT NULL DEFAULT '0' COMMENT '层级',
  `ENABLE` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否可用',
  `VISIBLE` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否可见',
  `IS_LEAF` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否是末节点',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  `REC_USER` bigint(20) DEFAULT NULL COMMENT '记录人',
  PRIMARY KEY (`PDC_TYPE`),
  KEY `REC_USER` (`REC_USER`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='产品分类';

/* Trigger structure for table `crm_sales_order` */




CREATE TABLE `crm_customer_behave` (
  `appCustId` varchar(50) NOT NULL COMMENT '顾客ID（APP）',
  `actTime` varchar(50) DEFAULT NULL COMMENT '行为时间(格式:yyyyMMddTHHmmss)',
  `actType` varchar(4) DEFAULT NULL COMMENT '行为类型（1-登录，2-浏览，3-租车请求，4-取消订单，5-订单被拒）',
  `extra` text COMMENT '额外参数，也是json对象',
  `orderId` varchar(50) DEFAULT NULL COMMENT '订单ID，放在extra对象中，可以不存在，即extra对象为空'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='行为记录表';

CREATE TABLE `crm_customer_event_item` (
  `CST_ID` bigint(20) NOT NULL,
  `ACT_ID` bigint(11) DEFAULT NULL,
  `EVT_ID` varchar(16) COLLATE utf8_bin NOT NULL,
  `ACT_SCOPE` varchar(16) COLLATE utf8_bin NOT NULL,
  `EVT_TIME` datetime DEFAULT NULL,
  KEY `CST_ID` (`CST_ID`,`EVT_ID`,`ACT_SCOPE`,`ACT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `crm_customer_site` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '代理主键',
  `CST_ID` bigint(20) NOT NULL COMMENT '顾客ID',
  `SIT_ID` int(11) NOT NULL COMMENT '网点ID',
  `REC_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `CST_ID` (`CST_ID`,`SIT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `crm_customer_suggest` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `FROM` bigint(20) NOT NULL COMMENT '发消息者',
  `TO` bigint(20) NOT NULL COMMENT '接受消息者',
  `SOURCE` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '投诉来源',
  `SOURCE_ID` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '来源编号',
  `CONTENT` text COLLATE utf8_bin NOT NULL COMMENT '投诉内容',
  `REC_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='顾客意见';




DELIMITER $$
DROP TRIGGER /*!50032 IF EXISTS */ `crm_tgrb_order_input`$$
/*!50003 CREATE */ /*!50003 TRIGGER `crm_tgrb_order_input` BEFORE INSERT ON `crm_sales_order` FOR EACH ROW BEGIN
	-- 如果是正常单据
	if ISNULL(new.`ORDER_FORMER`)
	then
		-- 设置实际金额为单据金额
	    set new.`FACT_PRICE` = new.`END_PRICE`;	          
	else
		-- 实际金额为0
	    set new.`FACT_PRICE` = 0;
	    -- 插入到异常单据表中
	    INSERT INTO `crm_sales_order_abnormal`(`SLD_FORMER`,`SLD_CHNL`,`END_PRICE`) VALUES(new.`ORDER_FORMER`,new.`SLD_CHANNEL`,new.`END_PRICE`);	    
	end if;
    END */$$


DELIMITER ;

/*!50106 set global event_scheduler = 1*/;

/* Event structure for event `crm_evt_order_process` */

DELIMITER $$
DROP EVENT /*!50032 IF EXISTS */ `crm_evt_order_process`$$
/*!50106 CREATE EVENT `crm_evt_order_process` ON SCHEDULE EVERY 2 HOUR STARTS CURRENT_TIMESTAMP ON COMPLETION PRESERVE ENABLE DO BEGIN
	    call crm_pdu_order_process();
	END */$$
DELIMITER ;

/* Event structure for event `crm_evt_points_expiry` */

DELIMITER $$
DROP EVENT /*!50032 IF EXISTS */ `crm_evt_points_expiry`$$
/*!50106 CREATE EVENT `crm_evt_points_expiry` ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(CURDATE(),INTERVAL 0 SECOND) ON COMPLETION PRESERVE ENABLE DO BEGIN
    call crm_pdu_points_expiry();
END */$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE /*!50032 IF EXISTS */ `crm_pdu_common_exe`$$
/*!50003 CREATE PROCEDURE `crm_pdu_common_exe`(IN uid VARCHAR(32))
BEGIN
    DECLARE i INT DEFAULT 0 ;
    DECLARE tt INT DEFAULT 0 ;
    -- start transaction;
    SELECT MAX(`INDEX`) INTO tt FROM crm_statements WHERE `ID` = uid;
    WHILE i <= tt 
    DO
		SET @st = NULL;
		SELECT @st:=`STMT` FROM crm_statements WHERE `ID` = uid AND `INDEX` = i;
		IF (@st IS NOT NULL) AND (LENGTH(@st) > 0)
		THEN
			PREPARE stmts FROM @st;
			EXECUTE stmts;
			DEALLOCATE PREPARE stmts;
		END IF;
    SET i = i+1;
    END WHILE;
END */$$
DELIMITER ;

/* Procedure structure for procedure `crm_pdu_order_process` */

DELIMITER $$
DROP PROCEDURE /*!50032 IF EXISTS */ `crm_pdu_order_process`$$
/*!50003 CREATE PROCEDURE `crm_pdu_order_process`()
    COMMENT '处理非正常订单，如退货，换货..'
BEGIN
	-- 更新sales_order的实际金额，成功后abnormal表result为true
	UPDATE crm_sales_order o 
	INNER JOIN crm_sales_order_abnormal a 
	ON (o.`ORDER_ID`=a.`SLD_FORMER` AND o.`SLD_CHANNEL` = a.`SLD_CHNL`) 
	SET o.`FACT_PRICE` =o.`FACT_PRICE`+a.`END_PRICE`,a.`RESULT`=TRUE;
	-- 删除处理成功的记录
	delete from crm_sales_order_abnormal where RESULT = true;
    END */$$
DELIMITER ;

/* Procedure structure for procedure `crm_pdu_points_expiry` */

DELIMITER $$
DROP PROCEDURE /*!50032 IF EXISTS */ `crm_pdu_points_expiry`$$
/*!50003 CREATE PROCEDURE `crm_pdu_points_expiry`()
    COMMENT '清理过期的积分'
BEGIN
	-- 当前时间
	declare nw datetime default now();
	-- 创建临时表
	create temporary table if not exists tmp_points_item(
	itm bigint primary key,
	pnt bigint,
	cst bigint,
	val double,
	blc bigint,
	st datetime,
	et datetime,
	key pntid (pnt)
	) DEFAULT CHARSET=utf8;
	-- 清除表数据
	TRUNCATE TABLE tmp_points_item;
	-- 插入需要清除的item
	insert into tmp_points_item 
	SELECT i.`ITM_ID`,i.`PNT_ID`,a.`CST_ID`,i.`PNT_VAL`,i.`BLC_ACT`,i.`S_TIME`,i.`E_TIME` 
	FROM crm_points_item i 
	INNER JOIN crm_points_account a 
	ON i.`PNT_ID` = a.`PNT_ID` 
	WHERE i.`E_TIME`<nw;
	-- 插入变更记录
	INSERT INTO crm_points_change
	(`PNT_ID`,`TYPE`,`PNT_VAL`,`FACT_PNT`,`CST_ID`,`BLC_IN`,`BLC_OUT`,`S_TIME`,`E_TIME`,`CHL_ID`,`REC_TIME`) 
	SELECT pnt,'EXPIRY',-val,0,cst,blc,blc,st,et,'CLEAR',nw FROM tmp_points_item;
	-- 更新账户积分值
	UPDATE crm_points_account a 
	INNER JOIN (SELECT SUM(val) AS vl,pnt FROM tmp_points_item GROUP BY pnt) tmp 
	ON a.`PNT_ID` = tmp.`pnt` 
	SET a.`PNT_VAL` = a.`PNT_VAL`- tmp.`vl`;
	-- 清除过期的item
	DELETE i FROM crm_points_item i INNER JOIN tmp_points_item t ON i.`ITM_ID` = t.`itm`;
	-- 临时表数据清空
	TRUNCATE TABLE tmp_points_item;
    END */$$
DELIMITER ;

/* Procedure structure for procedure `crm_pdu_points_total` */

DELIMITER $$
DROP PROCEDURE /*!50032 IF EXISTS */ `crm_pdu_points_total`$$
/*!50003 CREATE PROCEDURE `crm_pdu_points_total`(in pntId bigint,in nw datetime)
    COMMENT '查询顾客的账户积分'
BEGIN
    declare len int default 0;
    -- 最小限度的锁表
    SELECT COUNT(*) into len FROM crm_points_item WHERE PNT_ID = pntId FOR UPDATE;
    -- 查积分
    SELECT SUM(PNT_VAL) FROM crm_points_item WHERE PNT_ID = pntId AND `E_TIME` > nw AND `S_TIME` < nw;
    END */$$
DELIMITER ;

/*!50106 set global log_bin_trust_function_creators = 1*/;

DELIMITER $$
DROP FUNCTION /*!50032 IF EXISTS */ `crm_seq_next_val`$$
/*!50003 CREATE FUNCTION `crm_seq_next_val`(seq VARCHAR(32),inc INT) RETURNS INT(11)
BEGIN
	SET @nxt = 0;
	UPDATE crm_sequence SET CUR_VAL = (SELECT @nxt := CUR_VAL + IF(inc IS NULL OR inc <=0,INCRE,inc)) WHERE SEQ_NAME = seq;    
	RETURN @nxt;	
  END */$$
DELIMITER ;

DELIMITER $$

DROP PROCEDURE /*!50032 IF EXISTS */ `crm_tmp_pdu_cust_rated`$$
/*!50003 CREATE PROCEDURE `crm_tmp_pdu_cust_rated`()
    COMMENT '顾客评级'
BEGIN
    DECLARE now_date DATE ;
    DECLARE now_time DATETIME;
    DECLARE next_time DATETIME;
    DECLARE from_time DATETIME;
    DECLARE to_time DATETIME;
    SET now_date = CURRENT_DATE();
    SET now_time = CURRENT_TIMESTAMP();
    SET next_time = ADDDATE(now_time, 365);
    SET to_time = SUBDATE(now_date,30);
    SET from_time = SUBDATE(to_time,365);
    SET to_time = ADDDATE(to_time,INTERVAL '23:59:59' HOUR_SECOND);
	-- 创建临时表
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_cst_rated_37(
    `ID` BIGINT PRIMARY KEY,
    `STAT` TINYINT(1) DEFAULT '0',
    `CST_ID` BIGINT NOT NULL,
    `CST_RAK` VARCHAR(16) NOT NULL,
    `RAK_TIME` DATETIME NOT NULL,
    `TOTAL_AMOUNT` DECIMAL(10,2) DEFAULT '0.00',
    KEY `KEY_CST_ID` (`CST_ID`)
    ) DEFAULT CHARSET=utf8; 
    
    -- 清空表数据
    TRUNCATE TABLE tmp_cst_rated_37;
    
    -- 插入升级数据（排除最高级）
    INSERT INTO tmp_cst_rated_37(`ID`,`CST_ID`,`CST_RAK`,`RAK_TIME`)
    SELECT n.`ID`,n.`CST_ID`,a.`CST_RANK`,a.`RANK_TIME` 
    FROM crm_customer_need_rated n 
    INNER JOIN crm_customer_account a 
    ON (n.`CST_ID` = a.`CST_ID` AND n.`ACT_ID` =37)
    WHERE n.`UP_DATE` = now_date AND a.`CST_RANK` IN ('A','B');
    
    -- 更新变量（累计消费金额）
    UPDATE tmp_cst_rated_37 t 
    SET t.`TOTAL_AMOUNT`  = (SELECT SUM(s.`FACT_PRICE`) FROM crm_sales_order s,crm_order_account a WHERE s.`SLD_ID` = a.`SLD_ID` AND a.`ACT_ID` = 37 AND t.`CST_ID` = s.`CST_ID` AND s.`FACT_PRICE`>0 AND s.`SLD_TIME` BETWEEN from_time AND to_time); 
        
    -- 执行表达式（判断是否满足等级C）
    UPDATE tmp_cst_rated_37 SET `STAT`=(`TOTAL_AMOUNT` >= 20000);
    
    -- 等级变更1
    INSERT INTO `crm_customer_change`(`CST_ID`,`ACT_ID`,`CST_RANK`,`S_TIME`, `E_TIME`) SELECT `CST_ID`,37,`CST_RAK`,`RAK_TIME`,now_time FROM tmp_cst_rated_37 WHERE `STAT` = 1 ON DUPLICATE KEY UPDATE `E_TIME` = VALUES(`E_TIME`);
    
    -- 等级变更2
    INSERT INTO `crm_customer_change`(`CST_ID`,`ACT_ID`,`CST_RANK`,`S_TIME`) SELECT `CST_ID`,37,'C',now_time FROM tmp_cst_rated_37 WHERE `STAT` = 1;
    
    -- 更新等级
    UPDATE crm_customer_account a INNER JOIN tmp_cst_rated_37 t ON (a.`CST_ID` = t.`CST_ID` AND a.`ACT_ID` = 37) SET a.`CST_RANK` = 'C',a.`RANK_TIME` = now_time,a.`NEXT_RANK_TIME` = next_time WHERE t.`STAT` = 1;
    
    -- 删除成功数据
    DELETE FROM tmp_cst_rated_37 WHERE `STAT` = 1 OR `CST_RAK` = 'B';
    
    -- 执行表达式（判断是否满足等级B）
    UPDATE tmp_cst_rated_37 SET `STAT`=(`TOTAL_AMOUNT` >= 10000);
    
    -- 等级变更1
    INSERT INTO `crm_customer_change`(`CST_ID`,`ACT_ID`,`CST_RANK`,`S_TIME`, `E_TIME`) SELECT `CST_ID`,37,`CST_RAK`,`RAK_TIME`,now_time FROM tmp_cst_rated_37 WHERE `STAT` = 1 ON DUPLICATE KEY UPDATE `E_TIME` = VALUES(`E_TIME`);
    
    -- 等级变更2
    INSERT INTO `crm_customer_change`(`CST_ID`,`ACT_ID`,`CST_RANK`,`S_TIME`) SELECT `CST_ID`,37,'B',now_time FROM tmp_cst_rated_37 WHERE `STAT` = 1;
    
    -- 更新等级
    UPDATE crm_customer_account a INNER JOIN tmp_cst_rated_37 t ON (a.`CST_ID` = t.`CST_ID` AND a.`ACT_ID` = 37) SET a.`CST_RANK` = 'B',a.`RANK_TIME` = now_time,a.`NEXT_RANK_TIME` = next_time WHERE t.`STAT` = 1;
    
    -- 清空表数据
    TRUNCATE TABLE tmp_cst_rated_37;
    DELETE FROM crm_customer_need_rated WHERE UP_DATE = now_date;
        
    -- 插入需要降级数据
    INSERT IGNORE INTO crm_customer_need_rated(`CST_ID`,`ACT_ID`,`UP_DATE`) 
    SELECT CST_ID,ACT_ID,now_date 
    FROM crm_customer_account 
    WHERE ACT_ID = 37 
    AND CST_RANK IS NOT NULL 
    AND NEXT_RANK_TIME < now_date;
    
    -- 插入数据到临时表
    INSERT INTO tmp_cst_rated_37(`ID`,`CST_ID`,`CST_RAK`,`RAK_TIME`)
    SELECT n.`ID`,n.`CST_ID`,a.`CST_RANK`,a.`RANK_TIME` 
    FROM crm_customer_need_rated n 
    INNER JOIN crm_customer_account a 
    ON (n.`CST_ID` = a.`CST_ID` AND n.`ACT_ID` =37)
    WHERE n.`UP_DATE` = now_date;
    
    -- 更新变量（累计消费金额）
    UPDATE tmp_cst_rated_37 t 
    SET t.`TOTAL_AMOUNT`  = (SELECT SUM(s.`FACT_PRICE`) FROM crm_sales_order s,crm_order_account a WHERE s.`SLD_ID` = a.`SLD_ID` AND a.`ACT_ID` = 37 AND t.`CST_ID` = s.`CST_ID` AND s.`FACT_PRICE`>0 AND s.`SLD_TIME` BETWEEN from_time AND to_time); 
    
    -- 执行表达式（判断是否满足等级C）
    UPDATE tmp_cst_rated_37 SET `STAT`=(`TOTAL_AMOUNT` >= 20000);
    
     -- 等级变更1
    INSERT INTO `crm_customer_change`(`CST_ID`,`ACT_ID`,`CST_RANK`,`S_TIME`, `E_TIME`) SELECT `CST_ID`,37,`CST_RAK`,`RAK_TIME`,now_time FROM tmp_cst_rated_37 WHERE `STAT` = 1 ON DUPLICATE KEY UPDATE `E_TIME` = VALUES(`E_TIME`);
    
    -- 等级变更2
    INSERT INTO `crm_customer_change`(`CST_ID`,`ACT_ID`,`CST_RANK`,`S_TIME`) SELECT `CST_ID`,37,'C',now_time FROM tmp_cst_rated_37 WHERE `STAT` = 1;
    
    -- 更新等级
    UPDATE crm_customer_account a INNER JOIN tmp_cst_rated_37 t ON (a.`CST_ID` = t.`CST_ID` AND a.`ACT_ID` = 37) SET a.`CST_RANK` = 'C',a.`RANK_TIME` = now_time,a.`NEXT_RANK_TIME` = next_time WHERE t.`STAT` = 1;
    
    -- 删除数据
    DELETE FROM tmp_cst_rated_37 WHERE `STAT` = 1;
    
    -- 执行表达式（判断是否满足等级B）
    UPDATE tmp_cst_rated_37 SET `STAT`=(`TOTAL_AMOUNT` >= 10000);
    
     -- 等级变更1
    INSERT INTO `crm_customer_change`(`CST_ID`,`ACT_ID`,`CST_RANK`,`S_TIME`, `E_TIME`) SELECT `CST_ID`,37,`CST_RAK`,`RAK_TIME`,now_time FROM tmp_cst_rated_37 WHERE `STAT` = 1 ON DUPLICATE KEY UPDATE `E_TIME` = VALUES(`E_TIME`);
    
    -- 等级变更2
    INSERT INTO `crm_customer_change`(`CST_ID`,`ACT_ID`,`CST_RANK`,`S_TIME`) SELECT `CST_ID`,37,'B',now_time FROM tmp_cst_rated_37 WHERE `STAT` = 1;
    
    -- 更新等级
    UPDATE crm_customer_account a INNER JOIN tmp_cst_rated_37 t ON (a.`CST_ID` = t.`CST_ID` AND a.`ACT_ID` = 37) SET a.`CST_RANK` = 'B',a.`RANK_TIME` = now_time,a.`NEXT_RANK_TIME` = next_time WHERE t.`STAT` = 1;
    
    -- 删除数据
    DELETE FROM tmp_cst_rated_37 WHERE `STAT` = 1;
    
    -- 执行表达式(剩下的设为A等级)
    UPDATE tmp_cst_rated_37 SET `STAT`=1;
    
     -- 完善上次变更
    INSERT INTO `crm_customer_change`(`CST_ID`,`ACT_ID`,`CST_RANK`,`S_TIME`, `E_TIME`) SELECT `CST_ID`,37,`CST_RAK`,`RAK_TIME`,now_time FROM tmp_cst_rated_37 WHERE `STAT` = 1 ON DUPLICATE KEY UPDATE `E_TIME` = VALUES(`E_TIME`);
    
    -- 插入本次变更
    INSERT INTO `crm_customer_change`(`CST_ID`,`ACT_ID`,`CST_RANK`,`S_TIME`) SELECT `CST_ID`,37,'A',now_time FROM tmp_cst_rated_37 WHERE `STAT` = 1;
    
    -- 更新等级
    UPDATE crm_customer_account a INNER JOIN tmp_cst_rated_37 t ON (a.`CST_ID` = t.`CST_ID` AND a.`ACT_ID` = 37) SET a.`CST_RANK` = 'A',a.`RANK_TIME` = now_time,a.`NEXT_RANK_TIME` = next_time WHERE t.`STAT` = 1;
    
    -- 删除数据
    DELETE FROM crm_customer_need_rated WHERE UP_DATE = now_date;
    DROP TABLE tmp_cst_rated_37;
    
    END */$$

DELIMITER ;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;