Skip to content

J
jiedao-api-boot-master
Switch branch/tag
Find file
Normal viewHistoryPermalink
20240830.sql 12.6 KB
Newer Older
yanghao's avatar
chore: 1、添加字典 2、修改角色更新
fecfe8e5
 
yanghao committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 
-- 添加字典

INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES ('客户角色', 'customer_role', 0, NULL, '1', now(), '1', now(), b'0');

INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (0, '0', 'C0', 'CO', 'customer_role', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, '1', '工程商', 'Project contractor', 'customer_role', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (2, '2', '代理', 'Agent', 'customer_role', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (3, '3', '同行', 'Consolidated shipping', 'customer_role', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (6, '6', '集运', 'Competitors', 'customer_role', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');



INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES ('客户创建入口', 'customer_from', 0, NULL, '1', now(), '1', now(), b'0');

INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, '1', '导入', 'Import', 'customer_from', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (2, '2', '后台用户创建', 'Backend', 'customer_from', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (3, '3', 'APP注册', 'APP', 'customer_from', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (4, '4', '发货人创建', 'Shipper', 'customer_from', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');


INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES ('客户获取方式', 'customer_get_method', 0, NULL, '1', now(), '1', now(), b'0');

INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, '1', '自主创建', 'Independent creation', 'customer_get_method', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (2, '2', '分配/移交接收', 'Allot/Transfer', 'customer_get_method', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (3, '3', '公海捞取', 'Open Sea pick up', 'customer_get_method', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
yanghao's avatar
sql: 更新跟进纪录相关字典
27376485
 
yanghao committed
45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 
INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES ('客户跟进状态', 'customer_followup_status', 0, NULL, '1', now(), '1', now(), b'0');

INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (0, '0', '未提交', 'UnCommited', 'customer_followup_status', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, '1', '已提交', 'Commited', 'customer_followup_status', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');



INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES ('客户跟进类型', 'customer_followup_type', 0, NULL, '1', now(), '1', now(), b'0');

INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, '1', '维系拜访', 'Follow-up Visit', 'customer_followup_type', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (2, '2', '商机跟进', 'Business opportunities', 'customer_followup_type', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (3, '3', '联合拜访', 'Joint visit', 'customer_followup_type', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');


INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES ('跟进方式', 'customer_followup_method', 0, NULL, '1', now(), '1', now(), b'0');

INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, '1', '电话', 'Phone', 'customer_followup_method', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (2, '2', '面谈', 'Face-to-face', 'customer_followup_method', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (3, '3', '网络聊天', 'Online Chat', 'customer_followup_method', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');


INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES ('跟进结果', 'customer_followup_result_type', 0, NULL, '1', now(), '1', now(), b'0');

INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, '1', '等待消息', 'Waiting for message', 'customer_followup_result_type', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
yanghao's avatar
chore: 跟进纪录数据刷新
d9e2e600
 
yanghao committed
85 86 87 88 89 90 91 92 93 94 



-- ----------------------------
-- Table structure for ecw_customer_followup
-- ----------------------------
DROP TABLE IF EXISTS `ecw_customer_followup`;
CREATE TABLE `ecw_customer_followup`  (
                                          `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
                                          `number` varchar(64)  NULL DEFAULT NULL COMMENT '编号',
yanghao's avatar
chore: 客户跟进bug修复
f17caac1
 
yanghao committed
95 
                                          `parent_id` bigint NULL DEFAULT NULL COMMENT '上一级跟进单ID',
yanghao's avatar
chore: 跟进纪录数据刷新
d9e2e600
 
yanghao committed
96 97 98 99 100 101 
                                          `parent_number` varchar(64)  NULL DEFAULT NULL COMMENT '上一级跟进单号',
                                          `status` int NULL DEFAULT NULL COMMENT '状态 字典customer_followup_status',
                                          `customer_id` bigint NULL DEFAULT NULL COMMENT '客户编号',
                                          `offer_id` bigint NULL DEFAULT NULL COMMENT '报价单',
                                          `follow_type` tinyint NULL DEFAULT NULL COMMENT '跟进类型 字典customer_followup_type',
                                          `follow_time` datetime NULL DEFAULT NULL COMMENT '跟进时间',
yanghao's avatar
chore: 客户跟进联系人由名称修改为id，并在列表中返回联系人电话.
578e3634
 
yanghao committed
102 
                                          `contact_id` bigint NULL DEFAULT NULL COMMENT '联系人id',
yanghao's avatar
chore: 跟进纪录数据刷新
d9e2e600
 
yanghao committed
103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 
                                          `contact_name` varchar(63)  NULL DEFAULT NULL COMMENT '联系人',
                                          `follow_method` tinyint NULL DEFAULT NULL COMMENT '跟进方式 字典customer_followup_method',
                                          `follow_user_id` bigint NULL DEFAULT NULL COMMENT '客户经理/跟进业务员id',
                                          `purpose` varchar(512)  NULL DEFAULT NULL COMMENT '目的',
                                          `result_type` tinyint NULL DEFAULT NULL COMMENT '跟进结果 字典customer_followup_result_type',
                                          `feedback` varchar(2048)  NULL DEFAULT NULL COMMENT '客户反馈',
                                          `attatchment` varchar(2048)  NULL DEFAULT NULL COMMENT '附件 多个以逗号分隔',
                                          `next_time` datetime NULL DEFAULT NULL COMMENT '下次跟进时间',
                                          `next_plan` varchar(2048)  NULL DEFAULT NULL COMMENT '下次跟进计划',
                                          `creator` varchar(63)  NOT NULL DEFAULT '' COMMENT '创建者',
                                          `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                          `updater` varchar(63)  NULL DEFAULT '' COMMENT '更新者',
                                          `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                          `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
                                          PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 1  COMMENT = '客户跟进表';




-- 下面为刷新数据的sql
yanghao's avatar
chore: 1、添加字典 2、修改角色更新
fecfe8e5
 
yanghao committed
125 126 127 128 129 130 
-- 更新客户角色
update ecw_customer set roles = '2' where FIND_IN_SET(2, type) != 0;
update ecw_customer set roles = '3' where FIND_IN_SET(3, type) != 0;
update ecw_customer set roles = '2,3' where FIND_IN_SET(2, type) != 0 and FIND_IN_SET(3, type) != 0;
yanghao's avatar
chore: 根据客户日志中的创建/接收/捞取的最后行为时间 来更新归属时间
295b2149
 
yanghao committed
131 132 133 134 135 136 137 138 
-- 根据客户日志中的创建/接收/捞取的最后行为时间 来更新归属时间
update  ecw_customer a
    inner join (
        select customer_id, max(create_time) as maxtime from ecw_customer_operate_log where (operate_type = 5 or operate_type = 1 or operate_type = 7) group by customer_id
    ) b on a.id = b.customer_id
set a.customer_service_confirmed_time = b.maxtime
where a.deleted = 0;
yanghao's avatar
chore: 跟进纪录数据刷新
d9e2e600
 
yanghao committed
139 140 141 142 143 144 145 146 147 148 149 150 

-- 刷新客户跟进数据
insert into ecw_customer_followup(id, number, parent_number, `status`, customer_id, offer_id, follow_type, follow_time, contact_name,
                                  follow_method, follow_user_id, purpose, result_type, feedback, attatchment,
                                  next_time, next_plan, creator, create_time, updater, update_time, deleted)
select null, null, null, 1, customer_id, null, 1, follow_time, contact_name, follow_method, follow_user_id, null, 1,
       CONCAT('【客户反馈：】\r\n', feedback, '\r\n\r\n【处理结果：】\r\n', result),
       null, null, null, creator, create_time, updater, update_time, deleted
from ecw_customer_follow;
-- 更新客户跟进编号
update ecw_customer_followup set number = CONCAT('GJ', 2024000000 + id);