From 6efa4693cb921bf4c582d48d504e02c73f7f336b Mon Sep 17 00:00:00 2001
From: zjh <1084500556@qq.com>
Date: 星期一, 04 八月 2025 17:35:11 +0800
Subject: [PATCH] zjh202500804
---
ltkj-admin/src/main/resources/sql/陕健医.sql | 179 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 179 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 181b431..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"
@@ -35,3 +35,182 @@
pdf_base64 text NULL COMMENT 'pdf鎶ュ憡鍗昩ase64涓�',
jpg_base64 text NULL COMMENT 'jpg鎶ュ憡鍗昩ase64涓�'
) COMMENT '澶栭�丳DF鍥炰紶';
+
+# 娓呭簱
+TRUNCATE TABLE tb_transition;
+TRUNCATE TABLE tj_order;
+TRUNCATE TABLE tj_order_ycxm;
+TRUNCATE TABLE tj_order_detail;
+TRUNCATE TABLE tj_order_remark;
+TRUNCATE TABLE tj_order_detail_rules;
+TRUNCATE TABLE ltkj_hysqd;
+TRUNCATE TABLE ltkj_hybgd;
+TRUNCATE TABLE ltkj_exam_jcsqd;
+TRUNCATE TABLE ltkj_exam_jcbgd;
+TRUNCATE TABLE tj_jcycxm;
+TRUNCATE TABLE tj_sampling;
+TRUNCATE TABLE tj_report;
+TRUNCATE TABLE tj_reservation;
+TRUNCATE TABLE tj_wsxm;
+TRUNCATE TABLE tj_zhxm;
+TRUNCATE TABLE tj_xd_picture;
+TRUNCATE TABLE tj_xdtgmdjg;
+TRUNCATE TABLE tj_report_print;
+TRUNCATE TABLE tj_flowing_water;
+TRUNCATE TABLE tj_flowing_water_his;
+TRUNCATE TABLE tj_dw_grouping;
+TRUNCATE TABLE tj_grouping_pro;
+TRUNCATE TABLE tj_customer;
+TRUNCATE TABLE tj_dw_dept;
+TRUNCATE TABLE tj_customer_black;
+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