From 027c1328165a0d37a778ec7f58760bb0fe1ffebb Mon Sep 17 00:00:00 2001 From: zjh <1084500556@qq.com> Date: 星期三, 30 七月 2025 17:00:01 +0800 Subject: [PATCH] zjh20250730-1 --- ltkj-admin/src/main/resources/sql/陕健医.sql | 146 ++++++++++++++++++++++++++++++++++++++++++++++++ 1 files changed, 146 insertions(+), 0 deletions(-) diff --git "a/ltkj-admin/src/main/resources/sql/\351\231\225\345\201\245\345\214\273.sql" "b/ltkj-admin/src/main/resources/sql/\351\231\225\345\201\245\345\214\273.sql" index 762ea92..4086118 100644 --- "a/ltkj-admin/src/main/resources/sql/\351\231\225\345\201\245\345\214\273.sql" +++ "b/ltkj-admin/src/main/resources/sql/\351\231\225\345\201\245\345\214\273.sql" @@ -66,5 +66,151 @@ TRUNCATE TABLE tj_ask_historys; TRUNCATE TABLE tj_ask_medical_history; TRUNCATE TABLE tj_ask_work_log; +TRUNCATE TABLE dict_user_info; + +# 鏌ヨ鍓嶇紑鍊� +SELECT * FROM ( + SELECT config_value,'sqyy' as hosp FROM ltkjpeis10_sqyy.`sys_config` WHERE config_key = 'make_lis_tmh_prefix' union all + SELECT config_value,'bjfhyy' as hosp FROM ltkjpeis10_bjfhyy.`sys_config` WHERE config_key = 'make_lis_tmh_prefix' union all + SELECT config_value,'bjxjyy' as hosp FROM ltkjpeis10_bjxjyy.`sys_config` WHERE config_key = 'make_lis_tmh_prefix' union all + SELECT config_value,'chkwyy' as hosp FROM ltkjpeis10_chkwyy.`sys_config` WHERE config_key = 'make_lis_tmh_prefix' union all + SELECT config_value,'jdczgzyy' as hosp FROM ltkjpeis10_jdczgzyy.`sys_config` WHERE config_key = 'make_lis_tmh_prefix' union all + SELECT config_value,'pbkwyy' as hosp FROM ltkjpeis10_pbkwyy.`sys_config` WHERE config_key = 'make_lis_tmh_prefix' union all + SELECT config_value,'ssyjyy' as hosp FROM ltkjpeis10_ssyjyy.`sys_config` WHERE config_key = 'make_lis_tmh_prefix' union all + SELECT config_value,'wbzxyy' as hosp FROM ltkjpeis10_wbzxyy.`sys_config` WHERE config_key = 'make_lis_tmh_prefix' union all + SELECT config_value,'xamjyy' as hosp FROM ltkjpeis10_xamjyy.`sys_config` WHERE config_key = 'make_lis_tmh_prefix' union all + SELECT config_value,'xatlgcyy' as hosp FROM ltkjpeis10_xatlgcyy.`sys_config` WHERE config_key = 'make_lis_tmh_prefix' + ) a ORDER BY a.config_value DESC; + +# 鍚屾浠锋牸瀛樺偍杩囩▼ +CREATE TABLE tb_proprice_his +( + pro_id BIGINT NULL , + old_price DECIMAL(10,2) NULL, + new_price DECIMAL(10,2) NULL, + pro_parent_id BIGINT NULL, + time DATETIME DEFAULT CURRENT_TIMESTAMP +); + +CREATE DEFINER=`root`@`%` PROCEDURE `pro_tb_his_project`() +BEGIN + # 鏄惁寮�鍚悓姝is椤圭洰 + DECLARE isOpenTbHisProject VARCHAR(10); + # 鏄惁寮�鍚悓姝ュ叕鍏卞椁� + DECLARE isOpenCommonTaoCan VARCHAR(10); + # 鏄惁寮�鍚悓姝ュ崟浣嶅椁� + DECLARE isOpenDeptTaoCan VARCHAR(10); + + main_block: BEGIN + # 鍑屼簯浠锋牸鍚屾瀛樺偍杩囩▼锛宧is鍋滅敤鐨勯」鐩笉瑕佷粠濂楅 鍒嗙粍涓垹闄わ紝鍙慨鏀逛环鏍� + # 瀛樺偍杩囩▼璋冪敤鍐欏埌涓棿灞傦紝鍔犲弬鏁伴厤缃粯璁や笉鍚屾 + SELECT config_value INTO isOpenTbHisProject FROM `sys_config` WHERE config_key = 'isOpenTbHisProject' LIMIT 1; + SELECT config_value INTO isOpenCommonTaoCan FROM `sys_config` WHERE config_key = 'isOpenCommonTaoCan' LIMIT 1; + SELECT config_value INTO isOpenDeptTaoCan FROM `sys_config` WHERE config_key = 'isOpenDeptTaoCan' LIMIT 1; + + IF isOpenTbHisProject IS NULL OR isOpenTbHisProject != 'Y' THEN + LEAVE main_block; + END IF; + + DELETE FROM tb_proprice_his; + DROP TEMPORARY TABLE IF EXISTS his_tb_temp_prods; + CREATE TEMPORARY TABLE his_tb_temp_prods(id BIGINT); + INSERT INTO his_tb_temp_prods(id,pro_parent_id) + # 宸插仠鐢ㄩ」鐩甶ds + SELECT b.pro_id,b.pro_parent_id FROM ( + SELECT + a.pro_id,a.his_xmbm,a.pro_parent_id + FROM + tj_project a + LEFT JOIN tj_project b ON a.pro_parent_id = b.pro_id + WHERE a.pro_parent_id <> 0 AND a.his_xmbm <> '' AND a.pro_status =0 AND a.deleted = 0 AND b.pro_status = 0 AND b.deleted = 0 + ) b + LEFT JOIN ltkj_shanxiqinjdczgzyy_getshoufeixmzd c ON b.his_xmbm = c.shoufeixmid + WHERE c.shoufeixmid IS NULL OR c.zuofeibz = 1; + + INSERT INTO tb_proprice_his(pro_id) SELECT id FROM his_tb_temp_prods; + + UPDATE tj_project a + JOIN his_tb_temp_prods b ON a.pro_id = b.pro_id + SET a.pro_status = 1; + + INSERT INTO tb_proprice_his (pro_id,old_price,new_price,pro_parent_id) + # 浠锋牸鍙樺姩鐨勬暟鎹� + SELECT c.pro_id,c.pro_price,d.danjia,c.pro_parent_id FROM + ( + SELECT + a.pro_id,a.his_xmbm,a.pro_price,a.pro_parent_id + FROM + tj_project a + LEFT JOIN tj_project b ON a.pro_parent_id = b.pro_id + WHERE a.pro_parent_id <> 0 AND a.his_xmbm <> '' AND a.pro_status =0 AND a.deleted = 0 AND b.pro_status = 0 AND b.deleted = 0 + ) c + LEFT JOIN ltkj_shanxiqinjdczgzyy_getshoufeixmzd d ON c.his_xmbm = d.shoufeixmid + WHERE d.zuofeibz = 0 AND c.pro_price <> d.danjia AND c.pro_id NOT IN (SELECT id FROM his_tb_temp_prods); + + # 淇敼灏忛」浠锋牸 + UPDATE tj_project a + JOIN tb_proprice_his b ON a.pro_id = b.pro_id + SET a.pro_price = b.new_price; + + # 淇敼澶ч」浠锋牸 + UPDATE tj_project a + JOIN (SELECT pro_parent_id FROM tb_proprice_his GROUP BY(pro_parent_id)) b ON a.pro_id = b.pro_parent_id + JOIN ( + SELECT pro_parent_id, SUM(pro_price * sl) AS parentPrice + FROM tj_project + WHERE pro_parent_id != 0 and deleted=0 and pro_status=0 + GROUP BY pro_parent_id + ) d ON a.pro_id = d.pro_parent_id + SET a.pro_price = d.parentPrice; + + # 濡傛灉his鍋滅敤浜嗭紝椤圭洰涔熸敼鍋滅敤鐘舵�侊紝濂楅 鍒嗙粍涓殑椤圭洰涓嶈繘琛屽垹闄ゅ彉鍔� 鍙慨鏀瑰師浠凤紝涓�鍏变笁涓厤缃� 鏄惁寮�鍚悓姝ャ�佹槸鍚﹀悓姝ュ叕鍏卞椁愩�佹槸鍚﹀悓姝ュ崟浣嶅椁� + IF isOpenCommonTaoCan = 'Y' THEN + + # 鍋滅敤椤圭洰 + UPDATE tj_package_project SET price_ord = 0 + WHERE pro_id IN (SELECT pro_id FROM tb_proprice_his WHERE new_price IS NULL); + # 鏀逛环椤圭洰 + UPDATE tj_package_project a + JOIN tb_proprice_his b ON a.pro_id = b.pro_id + SET a.price_ord = b.new_price + WHERE b.new_price IS NOT NULL; + + # 淇敼濂楅鎬讳环 + UPDATE tj_package a + JOIN ( + SELECT b.pac_id,SUM(b.price_ord) AS order_price + FROM tj_package_project b + JOIN tb_proprice_his c ON b.pro_id = c.pro_id + GROUP BY b.pac_id + ) d ON a.pac_id = d.pac_id + SET a.price = d.order_price; + + END IF; + IF isOpenDeptTaoCan = 'Y' THEN + + # 鍋滅敤椤圭洰 + UPDATE tj_grouping_pro a SET pro_price = 0 + WHERE pro_id IN (SELECT pro_id FROM tb_proprice_his WHERE new_price IS NULL); + # 鏀逛环椤圭洰 + UPDATE tj_grouping_pro a + JOIN tb_proprice_his b ON a.pro_id = b.pro_id + SET a.pro_price = b.new_price + WHERE b.new_price IS NOT NULL; + + # 淇敼濂楅鎬讳环 + UPDATE tj_dw_grouping a + JOIN ( + SELECT b.grouping_id,SUM(b.pro_price) AS order_price + FROM tj_grouping_pro b + JOIN tb_proprice_his c ON b.pro_id = c.pro_id + GROUP BY b.grouping_id + ) d ON a.id = d.grouping_id + SET a.price = d.order_price; + + END IF; + + END main_block; +END -- Gitblit v1.8.0