etrade_shop_data_149.sql
5.2 KB
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
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
-- 注意：因为电子结算是新的数据库服务器，所以从客户数据生成店铺数据需要经过以下几步
-- 1、在原149数据库服务器创建shop表，并执行数据初始化脚本，得到店铺数据表
-- 2、将店铺数据表导入电子结算的新的数据库服务器
-- 3、依据生成的店铺数据，再生成shop_extra_info和shop_operate这两张表的初始化数据。（脚本为：etrade_shop_data.sql）
-- 4、数据初始化完成后，删除中间表`dili-customer`.`shop`
USE dili-customer;
CREATE TABLE `dili-customer`.`shop`
(
`id` bigint NOT NULL AUTO_INCREMENT,
`market_id` bigint NOT NULL COMMENT '市场id',
`name` varchar(50) NOT NULL COMMENT '店铺名称',
`customer_name` varchar(50) NOT NULL COMMENT '开店客户名称',
`customer_id` bigint NOT NULL COMMENT '开店客户id',
`main_products` varchar(100) DEFAULT NULL COMMENT '主营商品',
`location` varchar(100) DEFAULT NULL COMMENT '店铺位置',
`contact_name` varchar(50) DEFAULT NULL COMMENT '联系人',
`contact_phone` varchar(30) DEFAULT NULL COMMENT '联系电话',
`state` tinyint NOT NULL DEFAULT 1 COMMENT '启用禁用状态（1=启用 0=禁用）',
`recommend` tinyint NOT NULL DEFAULT 0 COMMENT '是否推荐（1=推荐 0=不推荐）',
`recommend_id` bigint DEFAULT NULL COMMENT '推荐类型id',
`recommend_time` datetime DEFAULT NULL COMMENT '推荐设置时间',
`open_time` datetime NOT NULL COMMENT '开店时间',
`shop_icon_url` varchar(200) NOT NULL COMMENT '店铺图标url',
`source` tinyint NOT NULL COMMENT '店铺来源类型（1=客户系统生成）',
`remark` varchar(100) DEFAULT NULL COMMENT '备注',
`card_no` varchar(30) DEFAULT NULL COMMENT '店铺收款主卡卡号',
`account_id` bigint DEFAULT NULL COMMENT '卡账户id',
`fund_account_id` bigint DEFAULT NULL COMMENT '资金账号',
`version` int NOT NULL DEFAULT 1 COMMENT '乐观锁，版本号',
`created_time` datetime NOT NULL DEFAULT current_timestamp COMMENT '创建时间',
`creater` varchar(50) DEFAULT NULL COMMENT '创建人名字',
`creater_id` bigint DEFAULT NULL COMMENT '创建人ID',
`modified_time` datetime NOT NULL DEFAULT current_timestamp on update current_timestamp COMMENT '更新时间',
`modifier` varchar(50) DEFAULT NULL COMMENT '修改人名字',
`modifier_id` bigint DEFAULT NULL COMMENT '修改人ID',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `unq_market_customer` (`market_id`,`customer_id`,`name`) USING BTREE COMMENT '同一市场同一客户只有一个店铺',
KEY `idx_customer_id` (`customer_id`) USING BTREE COMMENT '开店客户id索引'
) ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='店铺信息表';
INSERT INTO `dili-customer`.`shop` ( market_id,name,customer_name,customer_id,main_products,location,contact_name,contact_phone,state,recommend,open_time,shop_icon_url,source,remark,card_no,account_id,fund_account_id,version,creater,creater_id,modifier,modifier_id)
SELECT m.market_id,CONCAT(c.name,'的店'), c.name,c.id,goods.main_products,b.business_address,c.name,c.contacts_phone,1,0,NOW(),'02ad3bd6bfef4e959c26a0743001af5c',1,'存量客户导入',uc.card_no,uc.account_id,uua.account_id,1,'系统',0,'系统',0
FROM `dili-customer`.`customer` c
LEFT JOIN `dili-customer`.`customer_market` m ON m.customer_id = c.id
LEFT JOIN `dili-customer`.`character_type` t ON t.customer_id = c.id AND m.market_id = t.market_id
LEFT JOIN `dili-customer`.`customer_base_extension_info` b ON b.customer_id = c.id
LEFT JOIN `dili_account`.`account_user_account` ua ON ua.customer_id = c.id AND m.market_id = ua.firm_id
LEFT JOIN `dili_account`.`account_user_card` uc ON ua.account_id = uc.account_id AND m.market_id = uc.firm_id AND uc.type =10 AND uc.state = 1
LEFT JOIN `dili_upay`.`upay_user_account` uua ON uua.account_id = uc.account_id AND uua.customer_id = m.customer_id AND uua.state = 1
LEFT JOIN ( SELECT market_id, customer_id,
group_concat(
CASE customer_goods_category_id
WHEN 1 THEN '蔬菜'
WHEN 2 THEN '水果'
WHEN 3 THEN '水产'
WHEN 4 THEN '肉类'
WHEN 5 THEN '禽蛋'
WHEN 6 THEN '粮油'
WHEN 7 THEN '干调'
WHEN 8 THEN '其他'
ELSE ''
END
) AS main_products
FROM `dili-customer`.`customer_goods_category`
GROUP BY market_id, customer_id) AS goods ON goods.customer_id = c.id AND m.market_id = goods.market_id
WHERE t.character_type = 'business_user_character_type'
AND m.state = 1 AND m.approval_status = 2
AND c.certificate_number IS NOT NULL
GROUP BY m.market_id, c.id