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
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
| select dept_id as 部门, username as 用户名,real_name as 姓名,gender as 性别,mobile as 手机号,email as 邮箱,status as 状态（0禁用，1正常）,level as 等级,customer_limit as 客户容量,rong_cloud_status as 融云状态（0关闭，1开启）,identity as 身份（1综合产品销售，2商务，3操作，4供应链销售，5管理员） from sys_user where is_valid = 1;
select * from sys_user_identity where user_id = 215;
select * from sys_dept;
select COUNT(*) from sys_user;
select * from sys_user;
SELECT
sys_dept.name AS 部门,
sys_user.username AS 用户名,
sys_user.real_name AS 姓名,
sys_user.gender AS 性别,
sys_user.mobile AS 手机号,
sys_user.email AS 邮箱,
sys_user.status AS 状态（0禁用，1正常）,
sys_user.level AS 等级,
sys_user.customer_limit AS 客户容量,
sys_user.rong_cloud_status AS 融云状态（0关闭，1开启）,
sys_user.identity AS 身份（1物流销售，2商务，3操作，4供应链销售，5管理员）
FROM
sys_user
JOIN
sys_dept
ON
sys_user.dept_id = sys_dept.id
WHERE
sys_user.is_valid = 1;
SELECT
sys_dept.name AS 部门,
sys_user.username AS 用户名,
sys_user.real_name AS 姓名,
sys_user.gender AS 性别,
sys_user.mobile AS 手机号,
sys_user.email AS 邮箱,
sys_user.status AS 状态（0禁用，1正常）,
sys_user.level AS 等级,
sys_user.customer_limit AS 客户容量,
sys_user.rong_cloud_status AS 融云状态（0关闭，1开启）,
sys_user_identity.identity AS 身份（1物流销售，2商务，3操作，4供应链销售，5管理员）
FROM
sys_user
LEFT JOIN
sys_dept ON sys_user.dept_id = sys_dept.id
LEFT JOIN
sys_user_identity ON sys_user.user_id = sys_user_identity.user_id
WHERE
sys_user.is_valid = 1
ORDER BY
sys_user_identity.identity;
SELECT
sys_dept.name AS 部门,
sys_user.username AS 用户名,
sys_user.real_name AS 姓名,
sys_user.gender AS 性别（1男，2女，3保密）,
sys_user.mobile AS 手机号,
sys_user.email AS 邮箱,
sys_user.status AS 状态（0禁用，1正常）,
sys_user.level AS 等级,
sys_user.customer_limit AS 客户容量,
sys_user.rong_cloud_status AS 融云状态（0关闭，1开启）,
GROUP_CONCAT(sys_user_identity.identity SEPARATOR ', ') AS 身份（1物流销售，2商务，3操作，4供应链销售，5管理员）
FROM
sys_user
LEFT JOIN
sys_dept ON sys_user.dept_id = sys_dept.id
LEFT JOIN
(SELECT user_id, identity FROM sys_user_identity GROUP BY user_id, identity) AS sys_user_identity ON sys_user.user_id = sys_user_identity.user_id
WHERE
sys_user.is_valid = 1
GROUP BY
sys_user.user_id, sys_dept.name, sys_user.username, sys_user.real_name, sys_user.gender, sys_user.mobile, sys_user.email, sys_user.status, sys_user.level, sys_user.customer_limit, sys_user.rong_cloud_status
ORDER BY
身份（1物流销售，2商务，3操作，4供应链销售，5管理员）;
select
company_name as '公司名称',
customer_type as '客户类别: 1 普通客户 2 公海客户',
grade as '客户等级',
status as '状态',
deal_status as '成交状态：1物流产品成交，2综合产品成交，3都成交',
source as '客户来源',
has_agreement as '是否签署协议',
address as '公司地址',
label_id as '标签ID',
transfer_time as '移入公海时间',
transfer_user_id as '移入人',
allot_time as '分配时间',
follow_user_id as '客户跟进人ID',
source as '客户来源',
latest_follow as '最新跟进',
latest_follow_time as '最近联系时间',
latest_follow_action as '最新跟进类型',
settlement_type as '结算类型',
settlement_date as '结算日',
payment_days as '账期',
sales_area_id as '销售区域部门id',
last_shipping_time as '上次出货日期',
one_touch_sale as '一拍档销售姓名',
one_touch_customer_service as '一拍档客服姓名',
administrative_division as '行政区域',
offline_export_strength as '线下出口实力',
major_trading_countries as '主要贸易国',
major_logistics_destination_countries as '主要物流目的国',
used_logistics_mode as '用过的物流方式',
remark as '备注',
create_user_id as '创建人',
create_time as '创建时间',
last_update_user_id as '最后更新人',
last_update_time as '最后更新时间',
master_customer_id as '关联的主客户id'
from crm_customer where is_valid = 1;
select
c.company_name as '公司名称',
c.customer_type as '客户类别: 1 普通客户 2 公海客户',
u3.dict_label as '客户来源',
c.latest_follow as '最新跟进',
c.latest_follow_time as '最近联系时间',
c.latest_follow_action as '最新跟进类型',
c.address as '公司地址',
c.latest_follow as '最新跟进',
d.name as '销售区域',
u.real_name as '销售名称（物流）',
u2.real_name as '供应链销售名称',
cu2.is_partner as '是否合作（1为合作）',
cu2.transfer_time as '移入公海时间',
u.real_name as '移入人',
c.allot_time as '分配时间',
c.create_time as '创建时间',
c.remark as '备注'
from crm_customer c
left join sys_dept d on d.id = c.sales_area_id
left join crm_customer_user cu on cu.customer_id = c.id and cu.type = 1 and cu.current =1
left join sys_user u on u.user_id = cu.user_id
left join crm_customer_user cu2 on cu2.customer_id = c.id and cu2.type = 3 and cu2.current =1
left join sys_user u2 on u2.user_id = cu2.user_id
left join sys_dict_data u3 on u3.dict_value = c.source
where c.is_valid =1 and u3.dict_type_id = 2;
select
c.company_name as '公司名称',
c.customer_type as '客户类别: 1 普通客户 2 公海客户',
u3.dict_label as '客户来源',
c.latest_follow as '最新跟进',
c.latest_follow_time as '最近联系时间',
c.latest_follow_action as '最新跟进类型',
c.address as '公司地址',
c.latest_follow as '最新跟进',
d.name as '销售区域',
u.real_name as '销售名称（物流）',
u2.real_name as '供应链销售名称',
cu2.is_partner as '是否合作（1为合作）',
cu2.transfer_time as '移入公海时间',
u.real_name as '移入人',
c.allot_time as '分配时间',
c.create_time as '创建时间',
c.remark as '备注'
from crm_customer c
left join sys_dept d on d.id = c.sales_area_id
left join crm_customer_user cu on cu.customer_id = c.id and cu.current =1
left join sys_user u on u.user_id = cu.user_id
left join crm_customer_user cu2 on cu2.customer_id = c.id and cu2.current =1
left join sys_user u2 on u2.user_id = cu2.user_id
left join sys_dict_data u3 on u3.dict_value = c.source
where c.is_valid =1 and u3.dict_type_id = 2;
select * from crm_customer_user;
select * from sys_dept;
select * from sys_user;
select * from crm_customer;
select * from sys_dict_type;
select * from sys_dict_data;
select COUNT(*) from crm_customer where is_valid = 1;
|
