caimei365-cloud-order/backup.sql

-- =========== 订单微服务模块

-- 废弃 sku表
-- 修改 cm_cart 机构购物车表
ALTER TABLE `cm_cart`
MODIFY COLUMN `skuID` VARCHAR(20) DEFAULT NULL COMMENT '【V2021已废弃】',
MODIFY COLUMN `isOutOfTime` CHAR(1) DEFAULT '0' COMMENT '【V2021已废弃】',
ADD COLUMN `shopID` INT(11) DEFAULT NULL COMMENT '商品供应商Id' AFTER `productID`;
-- 更新 cm_cart 数据
UPDATE `cm_cart` c LEFT JOIN `product` p ON c.`productID`=p.`productID` SET c.`shopID`=p.`shopID`;

-- 修改 bp_order_product_cart 协销购物车表
ALTER TABLE `bp_order_product_cart`
MODIFY COLUMN `serviceProviderId` BIGINT(11) DEFAULT NULL COMMENT '协销ID' AFTER `id`,
MODIFY COLUMN `clubId` BIGINT(11) DEFAULT NULL COMMENT '机构ID' AFTER `serviceProviderId`,
MODIFY COLUMN `shopId` bigint(11) DEFAULT NULL COMMENT '供应商ID' AFTER `clubId`,
MODIFY COLUMN `productId` bigint(11) DEFAULT NULL COMMENT '商品ID' AFTER `shopId`,
MODIFY COLUMN `num` int(11) DEFAULT NULL COMMENT '购买数量' AFTER `productId`,
ADD COLUMN `addDate` datetime DEFAULT NULL COMMENT '添加时间' AFTER `num`,
MODIFY COLUMN `presentNum` int(11) DEFAULT NULL COMMENT '【V2021已废弃】赠送数量',
MODIFY COLUMN `name` varchar(300) DEFAULT NULL COMMENT '【V2021已废弃】商品名称',
MODIFY COLUMN `orderId` bigint(11) DEFAULT NULL COMMENT '【V2021已废弃】订单ID',
MODIFY COLUMN `contractProductId` bigint(11) DEFAULT NULL COMMENT '【V2021已废弃】合同商品ID',
MODIFY COLUMN `productAmount` decimal(20,6) DEFAULT NULL COMMENT '【V2021已废弃】price*num',
MODIFY COLUMN `totalFee` decimal(20,6) DEFAULT NULL COMMENT '【V2021已废弃】总价',
MODIFY COLUMN `image` text COMMENT '【V2021已废弃】商品图片',
MODIFY COLUMN `productNo` varchar(50) DEFAULT NULL COMMENT '【V2021已废弃】商品编号',
MODIFY COLUMN `price` decimal(20,2) DEFAULT NULL COMMENT '【V2021已废弃】商品价格',
MODIFY COLUMN `contractId` bigint(11) DEFAULT NULL COMMENT '【V2021已废弃】合同id',
MODIFY COLUMN `shopName` varchar(50) DEFAULT NULL COMMENT '【V2021已废弃】供应商名称',
MODIFY COLUMN `discount` decimal(20,6) DEFAULT NULL COMMENT '【V2021已废弃】折扣',
MODIFY COLUMN `discountPrice` decimal(20,6) DEFAULT NULL COMMENT '【V2021已废弃】折后单价',
MODIFY COLUMN `taxRate` decimal(20,0) DEFAULT NULL COMMENT '【V2021已废弃】税率',
MODIFY COLUMN `addedValueTax` decimal(20,6) DEFAULT NULL COMMENT '【V2021已废弃】增值税',
MODIFY COLUMN `totalAddedValueTax` decimal(20,6) DEFAULT NULL COMMENT '【V2021已废弃】总增值税',
MODIFY COLUMN `stipulateFlag` char(1) DEFAULT '0' COMMENT '【V2021已废弃】是否是规定商品 0否 1是',
MODIFY COLUMN `shopFee` decimal(20,6) DEFAULT NULL COMMENT '【V2021已废弃】应付供应商金额',
MODIFY COLUMN `otherFee` decimal(20,6) DEFAULT NULL COMMENT '【V2021已废弃】应付第三方金额',
MODIFY COLUMN `cmFee` decimal(20,6) DEFAULT NULL COMMENT '【V2021已废弃】应付采美金额',
MODIFY COLUMN `shopFee1` decimal(20,6) DEFAULT NULL COMMENT '【V2021已废弃】单个应付供应商金额',
MODIFY COLUMN `otherFee1` decimal(20,6) DEFAULT NULL COMMENT '【V2021已废弃】单个应付第三方金额',
MODIFY COLUMN `cmFee1` decimal(20,6) DEFAULT NULL COMMENT '【V2021已废弃】单个应付采美金额',
MODIFY COLUMN `shouldPayFee` decimal(20,6) DEFAULT NULL COMMENT '【V2021已废弃】应付金额',
MODIFY COLUMN `payStatus` char(1) DEFAULT '0' COMMENT '【V2021已废弃】支付状态 0 未付款 1 已付款',
MODIFY COLUMN `payDate` datetime DEFAULT NULL COMMENT '【V2021已废弃】支付时间',
MODIFY COLUMN `stagesFlag` char(1) DEFAULT NULL COMMENT '【V2021已废弃】分期订单标志 0否 1是',
MODIFY COLUMN `normalUserID` bigint(11) DEFAULT NULL COMMENT '【V2021已废弃】个人用户ID';

-- 修改 repeat_purchase_price 复购价格库表
ALTER TABLE `repeat_purchase_price`
ADD COLUMN `updateTime` varchar(50) DEFAULT NULL COMMENT '更新时间' AFTER `createTime`;


-- =========== 超级会员S_VIP
-- 修改 cm_order_pay_link 支付链接
ALTER TABLE `cm_order_pay_link`
    ADD COLUMN `vipRecordId` bigint(11) DEFAULT NULL COMMENT '会员购买记录Id' AFTER `orderId`,
    ADD COLUMN `vipMonth` bigint(11) DEFAULT NULL COMMENT '会员购买记录Id' AFTER `vipRecordId`;

-- 修改分账详情表
ALTER TABLE `cm_split_account` ADD COLUMN `vipRecordId` bigint(11) NULL COMMENT '超级会员购买记录Id' AFTER `shopId`;

-- 修改 cm_discern_receipt,cm_receipt_order_relation 收款表
ALTER TABLE `cm_discern_receipt`
    MODIFY COLUMN `receiptType` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '0' COMMENT '收款款项类型：1订单款，2非订单款，3返佣款，5供应商退款，6超级会员款';
ALTER TABLE `cm_receipt_order_relation`
    ADD COLUMN `vipRecordId` bigint(11) DEFAULT NULL COMMENT '超级会员购买记录Id' AFTER `orderId`,
    MODIFY COLUMN `relationType` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '0' COMMENT '关系类型：1返佣订单（返佣款或供应商退款）、2非返佣订单（订单款或者非订单款）、3超级会员款';

-- 修改 订单
ALTER TABLE `caimei`.`cm_order`
    ADD COLUMN `svipFullReduction` DECIMAL(15,2) NULL COMMENT '订单超级会员优惠' DEFAULT 0.00 AFTER `couponAmount`;
ALTER TABLE `caimei`.`cm_shop_order`
    ADD COLUMN `svipShopReduction` DECIMAL(15,2) NULL COMMENT '店铺超级会员优惠' DEFAULT 0.00 AFTER `differencePrice`;
ALTER TABLE `caimei`.`cm_order_product`
    ADD COLUMN `svipPriceFlag` INT NULL DEFAULT 0 COMMENT '超级会员优惠价格标识：0不是，1是' AFTER `heUserId`,
    ADD COLUMN `svipPriceType` INT NULL DEFAULT NULL COMMENT '超级会员优惠价类型:1折扣价，2直接优惠价' AFTER `svipPriceFlag`,
    ADD COLUMN `svipDiscount` DECIMAL(20,2) NULL DEFAULT '0.00' COMMENT '超级会员折扣' AFTER `svipPriceFlag`,
    ADD COLUMN `svipReduction` DECIMAL(20,2) NULL DEFAULT 0.00 COMMENT '超级会员优惠' AFTER `svipDiscount`;

-- ===========================CRM收款工具重构================================
-- 收款用户表
DROP TABLE IF EXISTS `cm_receipt_user`;
CREATE TABLE `cm_receipt_user` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '收款用户ID',
  `mobile` VARCHAR(11) NULL COMMENT '收款用户手机号',
  `password` VARCHAR(255) NULL COMMENT '收款用户密码',
  `openid` varchar(32) NULL COMMENT '收款用户小程序openid',
  `unionId` varchar(32) NULL COMMENT '收款用户unionId',
  `user_type` char(2) NULL COMMENT '收款用户类型：1协销人员，2客服，3财务，4超级管理员',
  `name` VARCHAR(100) NOT NULL COMMENT '姓名',
  `create_time` DATETIME DEFAULT NULL COMMENT '创建时间',
  `update_time` DATETIME DEFAULT NULL COMMENT '更新时间',
  `del_flag` CHAR(1) NOT NULL DEFAULT '0' COMMENT '删除标记：0未删，其他删除',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COMMENT='采美收款用户表';
 -- 收款权限表
DROP TABLE IF EXISTS `cm_receipt_permission`;
CREATE TABLE `cm_receipt_permission` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '收款权限ID',
  `permission` VARCHAR(100) DEFAULT NULL COMMENT '收款权限描述',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COMMENT='采美收款权限表';
 -- 采美收款用户权限关联表
DROP TABLE IF EXISTS `cm_receipt_user_permission`;
CREATE TABLE `cm_receipt_user_permission` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT DEFAULT NULL COMMENT '收款用户ID',
  `permission_id` BIGINT DEFAULT NULL COMMENT '收款权限ID',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COMMENT='采美收款用户权限关联表';
 -- 收款类型表
DROP TABLE IF EXISTS `cm_receipt_type`;
CREATE TABLE `cm_receipt_type` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '收款类型ID',
  `type` VARCHAR(100) NOT NULL COMMENT '收款类型',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='采美收款类型表';
-- 初始数据
INSERT INTO `cm_receipt_permission`(`id`,`permission`) VALUES
(1,'录入收款'),
(2,'收款列表操作'),
(3,'关联订单款'),
(4,'关联返佣款'),
(5,'口头返佣'),
(6,'关联供应商退款'),
(7,'审核订单款'),
(8,'收款详情分享'),
(9,'订单款/非订单款详情查看'),
(10,'返佣款详情查看'),
(11,'供应商退款详情查看');
INSERT INTO `cm_receipt_type`(`id`,`type`) VALUES (1,'建设银行-7297'),(2,'广发银行-0115'),(3,'中信银行-7172'),(4,'中信银行-0897'),(5,'中信银行-0897-财付通'),(6,'中信银行-0897-支付宝'),(7,'线上-支付宝'),(8,'线上-微信支付'),(9,'线上-快钱支付'),(10,'口头返佣'),(11,'广发银行-5461'),(12,'企业网银'),(13,'微信支付'),(14,'支付宝'),(15,'微信支付'),(16,'余额抵扣'),(18,'建设银行-1854'),(19,'建设银行-6256');

 -- 公众号微信用户列表
DROP TABLE IF EXISTS `wechat_user`;
CREATE TABLE `wechat_user` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `openid` varchar(32) NULL COMMENT '公众号openid',
  `unionId` varchar(32) NULL COMMENT '用户unionId',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='公众号微信用户列表';

-- 老收款用户数据迁移
INSERT INTO `cm_receipt_user` (`id`, `name`, `mobile`, `password`, `user_type`, `del_flag`) VALUES
('1','超','15814011616','C4644008C86EA3AAA6520B3895722FC4','4','0'),
('11','珣哥','15917362709','C4644008C86EA3AAA6520B3895722FC4','4','0'),
('17','张姐','13537588152','C4644008C86EA3AAA6520B3895722FC4','4','0'),
('21','陈莹','15899794221','C4644008C86EA3AAA6520B3895722FC4','4','0'),
('32','周总','13817700327','C4644008C86EA3AAA6520B3895722FC4','4','0'),
('44','周泽男','13543340328','C4644008C86EA3AAA6520B3895722FC4','4','0'),
('45','陈丽够','15889600343','C4644008C86EA3AAA6520B3895722FC4','4','0'),
('47','詹媛婵 ','13590155856','C4644008C86EA3AAA6520B3895722FC4','4','0'),
('84','李昕','18823462860','C4644008C86EA3AAA6520B3895722FC4','4','0'),
('18','张顺星','15112512592','C4644008C86EA3AAA6520B3895722FC4','1''0'),
('19','高林林','15817465281','C4644008C86EA3AAA6520B3895722FC4','1''0'),
('20','刘泽英','13538472670','C4644008C86EA3AAA6520B3895722FC4','1''0'),
('42','陈玉何','13760294722','C4644008C86EA3AAA6520B3895722FC4','1''0'),
('43','伍湘粤','13267215166','C4644008C86EA3AAA6520B3895722FC4','1''0'),
('57','王雅朦','15602931021','C4644008C86EA3AAA6520B3895722FC4','1''0'),
('59','黄豪','15738705659','C4644008C86EA3AAA6520B3895722FC4','1''0'),
('68','黄清强','13425197829','C4644008C86EA3AAA6520B3895722FC4','1''0'),
('69','詹丹妮','15816574186','C4644008C86EA3AAA6520B3895722FC4','1''0'),
('78','林哲源','18475590094','C4644008C86EA3AAA6520B3895722FC4','1''0'),
('119','王尚飞','16625199790','C4644008C86EA3AAA6520B3895722FC4','1''0');

update cm_discern_receipt set receiptUserPermissionID = 11 where receiptUserPermissionID in (11,22,23,24,88);
update cm_discern_receipt set receiptUserPermissionID = 21 where receiptUserPermissionID in (21,37,40,90);
update cm_discern_receipt set receiptUserPermissionID = 44 where receiptUserPermissionID in (44,51,93);
update cm_discern_receipt set receiptUserPermissionID = 45 where receiptUserPermissionID in (45,46,94);
update cm_discern_receipt set receiptUserPermissionID = 47 where receiptUserPermissionID in (47,48,49,95);
update cm_discern_receipt set receiptUserPermissionID = 18 where receiptUserPermissionID in (18,85);
update cm_discern_receipt set receiptUserPermissionID = 19 where receiptUserPermissionID in (19,86);
update cm_discern_receipt set receiptUserPermissionID = 20 where receiptUserPermissionID in (20,87);
update cm_discern_receipt set receiptUserPermissionID = 42 where receiptUserPermissionID in (42,91);
update cm_discern_receipt set receiptUserPermissionID = 43 where receiptUserPermissionID in (43,92);
update cm_discern_receipt set receiptUserPermissionID = 57 where receiptUserPermissionID in (57,98);
update cm_discern_receipt set receiptUserPermissionID = 59 where receiptUserPermissionID in (59,99);
update cm_discern_receipt set receiptUserPermissionID = 68 where receiptUserPermissionID in (68,101);
update cm_discern_receipt set receiptUserPermissionID = 69 where receiptUserPermissionID in (69,102);
update cm_discern_receipt set receiptUserPermissionID = 78 where receiptUserPermissionID in (78,105);
update cm_discern_receipt set receiptUserPermissionID = 119 where receiptUserPermissionID in (119,120);

update cm_discern_receipt set confirmUserPermissionID = 11 where confirmUserPermissionID in (11,22,23,24,88);
update cm_discern_receipt set confirmUserPermissionID = 21 where confirmUserPermissionID in (21,37,40,90);
update cm_discern_receipt set confirmUserPermissionID = 44 where confirmUserPermissionID in (44,51,93);
update cm_discern_receipt set confirmUserPermissionID = 45 where confirmUserPermissionID in (45,46,94);
update cm_discern_receipt set confirmUserPermissionID = 47 where confirmUserPermissionID in (47,48,49,95);
update cm_discern_receipt set confirmUserPermissionID = 18 where confirmUserPermissionID in (18,85);
update cm_discern_receipt set confirmUserPermissionID = 19 where confirmUserPermissionID in (19,86);
update cm_discern_receipt set confirmUserPermissionID = 20 where confirmUserPermissionID in (20,87);
update cm_discern_receipt set confirmUserPermissionID = 42 where confirmUserPermissionID in (42,91);
update cm_discern_receipt set confirmUserPermissionID = 43 where confirmUserPermissionID in (43,92);
update cm_discern_receipt set confirmUserPermissionID = 57 where confirmUserPermissionID in (57,98);
update cm_discern_receipt set confirmUserPermissionID = 59 where confirmUserPermissionID in (59,99);
update cm_discern_receipt set confirmUserPermissionID = 68 where confirmUserPermissionID in (68,101);
update cm_discern_receipt set confirmUserPermissionID = 69 where confirmUserPermissionID in (69,102);
update cm_discern_receipt set confirmUserPermissionID = 78 where confirmUserPermissionID in (78,105);
update cm_discern_receipt set confirmUserPermissionID = 119 where confirmUserPermissionID in (119,120);

update cm_discern_receipt set reviewUserPermissionID = 11 where reviewUserPermissionID in (11,22,23,24,88);
update cm_discern_receipt set reviewUserPermissionID = 21 where reviewUserPermissionID in (21,37,40,90);
update cm_discern_receipt set reviewUserPermissionID = 44 where reviewUserPermissionID in (44,51,93);
update cm_discern_receipt set reviewUserPermissionID = 45 where reviewUserPermissionID in (45,46,94);
update cm_discern_receipt set reviewUserPermissionID = 47 where reviewUserPermissionID in (47,48,49,95);
update cm_discern_receipt set reviewUserPermissionID = 18 where reviewUserPermissionID in (18,85);
update cm_discern_receipt set reviewUserPermissionID = 19 where reviewUserPermissionID in (19,86);
update cm_discern_receipt set reviewUserPermissionID = 20 where reviewUserPermissionID in (20,87);
update cm_discern_receipt set reviewUserPermissionID = 42 where reviewUserPermissionID in (42,91);
update cm_discern_receipt set reviewUserPermissionID = 43 where reviewUserPermissionID in (43,92);
update cm_discern_receipt set reviewUserPermissionID = 57 where reviewUserPermissionID in (57,98);
update cm_discern_receipt set reviewUserPermissionID = 59 where reviewUserPermissionID in (59,99);
update cm_discern_receipt set reviewUserPermissionID = 68 where reviewUserPermissionID in (68,101);
update cm_discern_receipt set reviewUserPermissionID = 69 where reviewUserPermissionID in (69,102);
update cm_discern_receipt set reviewUserPermissionID = 78 where reviewUserPermissionID in (78,105);
update cm_discern_receipt set reviewUserPermissionID = 119 where reviewUserPermissionID in (119,120);

update cm_discern_receipt set cancelUserPermissionID = 11 where cancelUserPermissionID in (11,22,23,24,88);
update cm_discern_receipt set cancelUserPermissionID = 21 where cancelUserPermissionID in (21,37,40,90);
update cm_discern_receipt set cancelUserPermissionID = 44 where cancelUserPermissionID in (44,51,93);
update cm_discern_receipt set cancelUserPermissionID = 45 where cancelUserPermissionID in (45,46,94);
update cm_discern_receipt set cancelUserPermissionID = 47 where cancelUserPermissionID in (47,48,49,95);
update cm_discern_receipt set cancelUserPermissionID = 18 where cancelUserPermissionID in (18,85);
update cm_discern_receipt set cancelUserPermissionID = 19 where cancelUserPermissionID in (19,86);
update cm_discern_receipt set cancelUserPermissionID = 20 where cancelUserPermissionID in (20,87);
update cm_discern_receipt set cancelUserPermissionID = 42 where cancelUserPermissionID in (42,91);
update cm_discern_receipt set cancelUserPermissionID = 43 where cancelUserPermissionID in (43,92);
update cm_discern_receipt set cancelUserPermissionID = 57 where cancelUserPermissionID in (57,98);
update cm_discern_receipt set cancelUserPermissionID = 59 where cancelUserPermissionID in (59,99);
update cm_discern_receipt set cancelUserPermissionID = 68 where cancelUserPermissionID in (68,101);
update cm_discern_receipt set cancelUserPermissionID = 69 where cancelUserPermissionID in (69,102);
update cm_discern_receipt set cancelUserPermissionID = 78 where cancelUserPermissionID in (78,105);
update cm_discern_receipt set cancelUserPermissionID = 119 where cancelUserPermissionID in (119,120);