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
| -- CRM 经营统计补充：保留原采购、销售、售后配置；按名称幂等新增，不覆盖已有自定义图表。
|
| SET @crm_dashboard_id = (SELECT id FROM bi_dashboard WHERE code='purchase_sales' AND deleted=0 LIMIT 1);
|
| INSERT INTO bi_chart_config (dashboard_id,name,chart_type,data_source_id,query_sql,position,chart_options,refresh_interval,sort,status,creator,create_time,updater,update_time,deleted)
| SELECT @crm_dashboard_id,'本月新增客户','number',NULL,'SELECT COUNT(*) AS value FROM crm_customer WHERE deleted=0 AND create_time >= CAST(DATE_FORMAT(CURDATE(), ''%Y-%m-01'') AS DATE) AND create_time < DATE_ADD(CAST(DATE_FORMAT(CURDATE(), ''%Y-%m-01'') AS DATE), INTERVAL 1 MONTH)','{"x":0,"y":101,"w":12,"h":4}','{"dashboardSection":"crm"}',60,101,1,'1',NOW(),'1',NOW(),b'0'
| WHERE @crm_dashboard_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM bi_chart_config WHERE dashboard_id=@crm_dashboard_id AND name='本月新增客户' AND deleted=0);
|
| INSERT INTO bi_chart_config (dashboard_id,name,chart_type,data_source_id,query_sql,position,chart_options,refresh_interval,sort,status,creator,create_time,updater,update_time,deleted)
| SELECT @crm_dashboard_id,'跟进中商机金额（元）','number',NULL,'SELECT COALESCE(SUM(total_price),0) AS value FROM crm_business WHERE deleted=0 AND (end_status IS NULL OR end_status=0)','{"x":0,"y":102,"w":12,"h":4}','{"dashboardSection":"crm"}',60,102,1,'1',NOW(),'1',NOW(),b'0'
| WHERE @crm_dashboard_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM bi_chart_config WHERE dashboard_id=@crm_dashboard_id AND name='跟进中商机金额（元）' AND deleted=0);
|
| INSERT INTO bi_chart_config (dashboard_id,name,chart_type,data_source_id,query_sql,position,chart_options,refresh_interval,sort,status,creator,create_time,updater,update_time,deleted)
| SELECT @crm_dashboard_id,'本月有效合同金额（元）','number',NULL,'SELECT COALESCE(SUM(total_price),0) AS value FROM crm_contract WHERE deleted=0 AND audit_status=20 AND order_date >= CAST(DATE_FORMAT(CURDATE(), ''%Y-%m-01'') AS DATE) AND order_date < DATE_ADD(CAST(DATE_FORMAT(CURDATE(), ''%Y-%m-01'') AS DATE), INTERVAL 1 MONTH)','{"x":0,"y":103,"w":12,"h":4}','{"dashboardSection":"crm"}',60,103,1,'1',NOW(),'1',NOW(),b'0'
| WHERE @crm_dashboard_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM bi_chart_config WHERE dashboard_id=@crm_dashboard_id AND name='本月有效合同金额（元）' AND deleted=0);
|
| INSERT INTO bi_chart_config (dashboard_id,name,chart_type,data_source_id,query_sql,position,chart_options,refresh_interval,sort,status,creator,create_time,updater,update_time,deleted)
| SELECT @crm_dashboard_id,'本月已审核回款（元）','number',NULL,'SELECT COALESCE(SUM(price),0) AS value FROM crm_receivable WHERE deleted=0 AND audit_status=20 AND return_time >= CAST(DATE_FORMAT(CURDATE(), ''%Y-%m-01'') AS DATE) AND return_time < DATE_ADD(CAST(DATE_FORMAT(CURDATE(), ''%Y-%m-01'') AS DATE), INTERVAL 1 MONTH)','{"x":0,"y":104,"w":12,"h":4}','{"dashboardSection":"crm"}',60,104,1,'1',NOW(),'1',NOW(),b'0'
| WHERE @crm_dashboard_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM bi_chart_config WHERE dashboard_id=@crm_dashboard_id AND name='本月已审核回款（元）' AND deleted=0);
|
| INSERT INTO bi_chart_config (dashboard_id,name,chart_type,data_source_id,query_sql,position,chart_options,refresh_interval,sort,status,creator,create_time,updater,update_time,deleted)
| SELECT @crm_dashboard_id,'有效合同待回款（元）','number',NULL,'SELECT COALESCE(SUM(GREATEST(COALESCE(c.total_price,0)-COALESCE(r.paid,0),0)),0) AS value FROM crm_contract c LEFT JOIN (SELECT contract_id, SUM(price) AS paid FROM crm_receivable WHERE deleted=0 AND audit_status=20 GROUP BY contract_id) r ON r.contract_id=c.id WHERE c.deleted=0 AND c.audit_status=20','{"x":0,"y":105,"w":12,"h":4}','{"dashboardSection":"crm"}',60,105,1,'1',NOW(),'1',NOW(),b'0'
| WHERE @crm_dashboard_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM bi_chart_config WHERE dashboard_id=@crm_dashboard_id AND name='有效合同待回款（元）' AND deleted=0);
|
| INSERT INTO bi_chart_config (dashboard_id,name,chart_type,data_source_id,query_sql,position,chart_options,refresh_interval,sort,status,creator,create_time,updater,update_time,deleted)
| SELECT @crm_dashboard_id,'本月报价单数','number',NULL,'SELECT COUNT(*) AS value FROM crm_sale_quotation WHERE deleted=0 AND quotation_time >= CAST(DATE_FORMAT(CURDATE(), ''%Y-%m-01'') AS DATE) AND quotation_time < DATE_ADD(CAST(DATE_FORMAT(CURDATE(), ''%Y-%m-01'') AS DATE), INTERVAL 1 MONTH)','{"x":0,"y":106,"w":12,"h":4}','{"dashboardSection":"crm"}',60,106,1,'1',NOW(),'1',NOW(),b'0'
| WHERE @crm_dashboard_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM bi_chart_config WHERE dashboard_id=@crm_dashboard_id AND name='本月报价单数' AND deleted=0);
|
| INSERT INTO bi_chart_config (dashboard_id,name,chart_type,data_source_id,query_sql,position,chart_options,refresh_interval,sort,status,creator,create_time,updater,update_time,deleted)
| SELECT @crm_dashboard_id,'近12个月新增客户（家）','line',NULL,'SELECT DATE_FORMAT(m.month_start, ''%Y-%m'') AS name, COALESCE(v.value,0) AS value FROM (SELECT DATE_SUB(CAST(DATE_FORMAT(CURDATE(), ''%Y-%m-01'') AS DATE), INTERVAL n MONTH) AS month_start FROM (SELECT 0 AS n UNION ALL SELECT 1 AS n UNION ALL SELECT 2 AS n UNION ALL SELECT 3 AS n UNION ALL SELECT 4 AS n UNION ALL SELECT 5 AS n UNION ALL SELECT 6 AS n UNION ALL SELECT 7 AS n UNION ALL SELECT 8 AS n UNION ALL SELECT 9 AS n UNION ALL SELECT 10 AS n UNION ALL SELECT 11 AS n) offsets) m LEFT JOIN (SELECT DATE_FORMAT(create_time, ''%Y-%m'') AS month, COUNT(*) AS value FROM crm_customer WHERE deleted=0 AND create_time >= DATE_SUB(CAST(DATE_FORMAT(CURDATE(), ''%Y-%m-01'') AS DATE), INTERVAL 11 MONTH) AND create_time < DATE_ADD(CAST(DATE_FORMAT(CURDATE(), ''%Y-%m-01'') AS DATE), INTERVAL 1 MONTH) GROUP BY DATE_FORMAT(create_time, ''%Y-%m'')) v ON v.month=DATE_FORMAT(m.month_start,''%Y-%m'') ORDER BY m.month_start','{"x":0,"y":107,"w":12,"h":8}','{"dashboardSection":"crm"}',60,107,1,'1',NOW(),'1',NOW(),b'0'
| WHERE @crm_dashboard_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM bi_chart_config WHERE dashboard_id=@crm_dashboard_id AND name='近12个月新增客户（家）' AND deleted=0);
|
| INSERT INTO bi_chart_config (dashboard_id,name,chart_type,data_source_id,query_sql,position,chart_options,refresh_interval,sort,status,creator,create_time,updater,update_time,deleted)
| SELECT @crm_dashboard_id,'近12个月合同与回款（元）','line',NULL,'SELECT DATE_FORMAT(m.month_start,''%Y-%m'') AS 月份, COALESCE(c.value,0) AS 有效合同金额, COALESCE(r.value,0) AS 已审核回款 FROM (SELECT DATE_SUB(CAST(DATE_FORMAT(CURDATE(), ''%Y-%m-01'') AS DATE), INTERVAL n MONTH) AS month_start FROM (SELECT 0 AS n UNION ALL SELECT 1 AS n UNION ALL SELECT 2 AS n UNION ALL SELECT 3 AS n UNION ALL SELECT 4 AS n UNION ALL SELECT 5 AS n UNION ALL SELECT 6 AS n UNION ALL SELECT 7 AS n UNION ALL SELECT 8 AS n UNION ALL SELECT 9 AS n UNION ALL SELECT 10 AS n UNION ALL SELECT 11 AS n) offsets) m LEFT JOIN (SELECT DATE_FORMAT(order_date, ''%Y-%m'') AS month, SUM(total_price) AS value FROM crm_contract WHERE deleted=0 AND audit_status=20 AND order_date >= DATE_SUB(CAST(DATE_FORMAT(CURDATE(), ''%Y-%m-01'') AS DATE), INTERVAL 11 MONTH) AND order_date < DATE_ADD(CAST(DATE_FORMAT(CURDATE(), ''%Y-%m-01'') AS DATE), INTERVAL 1 MONTH) GROUP BY DATE_FORMAT(order_date, ''%Y-%m'')) c ON c.month=DATE_FORMAT(m.month_start,''%Y-%m'') LEFT JOIN (SELECT DATE_FORMAT(return_time, ''%Y-%m'') AS month, SUM(price) AS value FROM crm_receivable WHERE deleted=0 AND audit_status=20 AND return_time >= DATE_SUB(CAST(DATE_FORMAT(CURDATE(), ''%Y-%m-01'') AS DATE), INTERVAL 11 MONTH) AND return_time < DATE_ADD(CAST(DATE_FORMAT(CURDATE(), ''%Y-%m-01'') AS DATE), INTERVAL 1 MONTH) GROUP BY DATE_FORMAT(return_time, ''%Y-%m'')) r ON r.month=DATE_FORMAT(m.month_start,''%Y-%m'') ORDER BY m.month_start','{"x":0,"y":108,"w":12,"h":8}','{"dashboardSection":"crm"}',60,108,1,'1',NOW(),'1',NOW(),b'0'
| WHERE @crm_dashboard_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM bi_chart_config WHERE dashboard_id=@crm_dashboard_id AND name='近12个月合同与回款（元）' AND deleted=0);
|
| INSERT INTO bi_chart_config (dashboard_id,name,chart_type,data_source_id,query_sql,position,chart_options,refresh_interval,sort,status,creator,create_time,updater,update_time,deleted)
| SELECT @crm_dashboard_id,'商机阶段分布（个）','bar',NULL,'SELECT CASE b.end_status WHEN 1 THEN ''赢单'' WHEN 2 THEN ''输单'' WHEN 3 THEN ''无效'' ELSE COALESCE(s.name,''未配置阶段'') END AS name, COUNT(*) AS value FROM crm_business b LEFT JOIN crm_business_status s ON s.id=b.status_id AND s.deleted=0 WHERE b.deleted=0 GROUP BY name ORDER BY value DESC,name','{"x":0,"y":109,"w":12,"h":8}','{"dashboardSection":"crm"}',60,109,1,'1',NOW(),'1',NOW(),b'0'
| WHERE @crm_dashboard_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM bi_chart_config WHERE dashboard_id=@crm_dashboard_id AND name='商机阶段分布（个）' AND deleted=0);
|
| INSERT INTO bi_chart_config (dashboard_id,name,chart_type,data_source_id,query_sql,position,chart_options,refresh_interval,sort,status,creator,create_time,updater,update_time,deleted)
| SELECT @crm_dashboard_id,'客户成交状态（家）','pie',NULL,'SELECT CASE deal_status WHEN 1 THEN ''已成交'' ELSE ''未成交'' END AS name, COUNT(*) AS value FROM crm_customer WHERE deleted=0 GROUP BY name','{"x":0,"y":110,"w":12,"h":8}','{"dashboardSection":"crm"}',60,110,1,'1',NOW(),'1',NOW(),b'0'
| WHERE @crm_dashboard_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM bi_chart_config WHERE dashboard_id=@crm_dashboard_id AND name='客户成交状态（家）' AND deleted=0);
|
| INSERT INTO bi_chart_config (dashboard_id,name,chart_type,data_source_id,query_sql,position,chart_options,refresh_interval,sort,status,creator,create_time,updater,update_time,deleted)
| SELECT @crm_dashboard_id,'重点客户合同金额 TOP10（元）','bar',NULL,'SELECT COALESCE(c.name,CONCAT(''客户编号 '',ct.customer_id)) AS name,SUM(ct.total_price) AS value FROM crm_contract ct LEFT JOIN crm_customer c ON c.id=ct.customer_id AND c.deleted=0 WHERE ct.deleted=0 AND ct.audit_status=20 GROUP BY ct.customer_id,c.name ORDER BY value DESC,ct.customer_id LIMIT 10','{"x":0,"y":111,"w":12,"h":8}','{"dashboardSection":"crm","horizontal":true}',60,111,1,'1',NOW(),'1',NOW(),b'0'
| WHERE @crm_dashboard_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM bi_chart_config WHERE dashboard_id=@crm_dashboard_id AND name='重点客户合同金额 TOP10（元）' AND deleted=0);
|
| INSERT INTO bi_chart_config (dashboard_id,name,chart_type,data_source_id,query_sql,position,chart_options,refresh_interval,sort,status,creator,create_time,updater,update_time,deleted)
| SELECT @crm_dashboard_id,'近12个月报价数量（单）','line',NULL,'SELECT DATE_FORMAT(m.month_start, ''%Y-%m'') AS name, COALESCE(v.value,0) AS value FROM (SELECT DATE_SUB(CAST(DATE_FORMAT(CURDATE(), ''%Y-%m-01'') AS DATE), INTERVAL n MONTH) AS month_start FROM (SELECT 0 AS n UNION ALL SELECT 1 AS n UNION ALL SELECT 2 AS n UNION ALL SELECT 3 AS n UNION ALL SELECT 4 AS n UNION ALL SELECT 5 AS n UNION ALL SELECT 6 AS n UNION ALL SELECT 7 AS n UNION ALL SELECT 8 AS n UNION ALL SELECT 9 AS n UNION ALL SELECT 10 AS n UNION ALL SELECT 11 AS n) offsets) m LEFT JOIN (SELECT DATE_FORMAT(quotation_time, ''%Y-%m'') AS month, COUNT(*) AS value FROM crm_sale_quotation WHERE deleted=0 AND quotation_time >= DATE_SUB(CAST(DATE_FORMAT(CURDATE(), ''%Y-%m-01'') AS DATE), INTERVAL 11 MONTH) AND quotation_time < DATE_ADD(CAST(DATE_FORMAT(CURDATE(), ''%Y-%m-01'') AS DATE), INTERVAL 1 MONTH) GROUP BY DATE_FORMAT(quotation_time, ''%Y-%m'')) v ON v.month=DATE_FORMAT(m.month_start,''%Y-%m'') ORDER BY m.month_start','{"x":0,"y":112,"w":12,"h":8}','{"dashboardSection":"crm"}',60,112,1,'1',NOW(),'1',NOW(),b'0'
| WHERE @crm_dashboard_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM bi_chart_config WHERE dashboard_id=@crm_dashboard_id AND name='近12个月报价数量（单）' AND deleted=0);
|
| INSERT INTO bi_chart_config (dashboard_id,name,chart_type,data_source_id,query_sql,position,chart_options,refresh_interval,sort,status,creator,create_time,updater,update_time,deleted)
| SELECT @crm_dashboard_id,'最近有效合同与回款','table',NULL,'SELECT c.no AS 合同编号,c.name AS 合同名称,COALESCE(cu.name,''客户已删除'') AS 客户,c.total_price AS 合同金额,COALESCE(r.paid,0) AS 已回款,GREATEST(COALESCE(c.total_price,0)-COALESCE(r.paid,0),0) AS 待回款,DATE_FORMAT(c.order_date,''%Y-%m-%d'') AS 签单日期 FROM crm_contract c LEFT JOIN crm_customer cu ON cu.id=c.customer_id AND cu.deleted=0 LEFT JOIN (SELECT contract_id, SUM(price) AS paid FROM crm_receivable WHERE deleted=0 AND audit_status=20 GROUP BY contract_id) r ON r.contract_id=c.id WHERE c.deleted=0 AND c.audit_status=20 ORDER BY c.order_date DESC,c.id DESC LIMIT 10','{"x":0,"y":113,"w":24,"h":8}','{"dashboardSection":"crm"}',60,113,1,'1',NOW(),'1',NOW(),b'0'
| WHERE @crm_dashboard_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM bi_chart_config WHERE dashboard_id=@crm_dashboard_id AND name='最近有效合同与回款' AND deleted=0);
|
| INSERT INTO bi_chart_config (dashboard_id,name,chart_type,data_source_id,query_sql,position,chart_options,refresh_interval,sort,status,creator,create_time,updater,update_time,deleted)
| SELECT @crm_dashboard_id,'逾期未跟进客户 TOP10','table',NULL,'SELECT c.name AS 客户,COALESCE(u.nickname,''负责人已删除'') AS 负责人,DATE_FORMAT(c.contact_next_time,''%Y-%m-%d'') AS 计划联系日期,DATEDIFF(CURDATE(),c.contact_next_time) AS 逾期天数,DATE_FORMAT(c.contact_last_time,''%Y-%m-%d'') AS 最近联系日期 FROM crm_customer c LEFT JOIN system_users u ON u.id=c.owner_user_id AND u.deleted=0 WHERE c.deleted=0 AND c.owner_user_id IS NOT NULL AND c.contact_next_time < CURDATE() AND (c.contact_last_time IS NULL OR c.contact_last_time < c.contact_next_time) ORDER BY c.contact_next_time,c.id LIMIT 10','{"x":0,"y":114,"w":24,"h":8}','{"dashboardSection":"crm"}',60,114,1,'1',NOW(),'1',NOW(),b'0'
| WHERE @crm_dashboard_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM bi_chart_config WHERE dashboard_id=@crm_dashboard_id AND name='逾期未跟进客户 TOP10' AND deleted=0);
|
| UPDATE bi_chart_config SET chart_options=JSON_SET(COALESCE(chart_options,JSON_OBJECT()),'$.horizontal',JSON_EXTRACT('true','$')) WHERE dashboard_id=@crm_dashboard_id AND name='重点客户合同金额 TOP10（元）' AND deleted=0 AND JSON_UNQUOTE(JSON_EXTRACT(chart_options,'$.dashboardSection'))='crm';
|
|
