From 9787289d767a21e44d63c0d8a5f13a6b54e12a4b Mon Sep 17 00:00:00 2001
From: 云 <2163098428@qq.com>
Date: 星期三, 14 十月 2026 09:54:11 +0800
Subject: [PATCH] feat(bi): 增加CRM数据展示功能并优化图表组件

---
 sql/mysql/bi_purchase_sales_crm_patch.sql |   61 ++++++++++++++++++++++++++++++
 1 files changed, 61 insertions(+), 0 deletions(-)

diff --git a/sql/mysql/bi_purchase_sales_crm_patch.sql b/sql/mysql/bi_purchase_sales_crm_patch.sql
new file mode 100644
index 0000000..804a351
--- /dev/null
+++ b/sql/mysql/bi_purchase_sales_crm_patch.sql
@@ -0,0 +1,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';

--
Gitblit v1.9.3