From c292931f4f2575549af03861a144cac811ce76d1 Mon Sep 17 00:00:00 2001 From: zhaowenxuan <chacca165@163.com> Date: 星期三, 30 七月 2025 09:27:45 +0800 Subject: [PATCH] 同步his项目存储过程 --- ltkj-admin/src/main/resources/sql/陕健医.sql | 209 +++++++++++++++++++++++++++++++++++++++++++++++++++- 1 files changed, 204 insertions(+), 5 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 0a6732e..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" @@ -1,17 +1,216 @@ # lis銆乸acs瑙嗗浘 -鍦板潃锛�10.100.100.205 -鏁版嵁搴撳悕锛歭tkjview -鐢ㄦ埛锛歭tkjview -瀵嗙爜锛歭tkj@view -瑙嗗浘 +# 鍦板潃锛�10.100.100.205 +# 鏁版嵁搴撳悕锛歭tkjview +# 鐢ㄦ埛锛歭tkjview +# 瀵嗙爜锛歭tkj@view +# 瑙嗗浘 +/* ltkjview.lis_sqd | his_bodycheck ltkjview.lis_xm | hjlis_ylsf ltkjview.pacs_sqd | v_examhisorderdata ltkjview.pacs_xm | v_examitem +*/ # 杩畨澶栭�佸鎺� # 閲戝爢鍩� CREATE USER 'ltkjwsdian'@'%' IDENTIFIED BY 'ltkj@ws20250320'; GRANT INSERT, UPDATE, DELETE,SELECT ON ltkjpeis10_jdczgzyy.ws_import_result_group TO 'ltkjwsdian'@'%'; +GRANT INSERT, UPDATE, DELETE,SELECT ON ltkjpeis10_jdczgzyy.ws_import_pdf_group TO 'ltkjwsdian'@'%'; GRANT SELECT ON ltkjpeis10_jdczgzyy.ws_export_sample_group TO 'ltkjwsdian'@'%'; +GRANT SELECT ON ltkjpeis10_jdczgzyy.ws_import_pdf_group TO 'ltkjwsdian'@'%'; FLUSH PRIVILEGES; + +# 澶栭�丳DF鎶ュ憡琛� +CREATE TABLE ws_import_pdf_group( + id bigint AUTO_INCREMENT COMMENT '搴忓彿', + sample_no varchar(100) NULL COMMENT '鎸囨牱鏈湪LIS鐨勫敮涓�ID鍙枫�傚鏋滄病鏈夛紝灏卞～鏍锋湰鏉＄爜鍙枫��', + hos_code varchar(100) NULL COMMENT '璐村湪璇曠涓婄殑鏍锋湰鍖婚櫌鏉＄爜鍙枫��', + patient_no varchar(100) NULL COMMENT '鎮ｈ�呭敮涓�ID锛屾垨鑰呭敮涓�灏辫瘖鍗″彿銆佺ぞ淇濆崱鍙�', + out_patient_no varchar(100) NULL COMMENT '闂ㄨ瘖鍙�', + pdf_file varchar(255) NULL COMMENT '鎶ュ憡鏂囦欢璺緞', + report_date datetime NULL COMMENT '鎶ュ憡鏃ユ湡', + is_read varchar(30) NULL COMMENT '鏄惁涓嬭浇', + create_time datetime NULL COMMENT '鍒涘缓鏃ユ湡', + da_barcode varchar(100) NULL COMMENT '杩畨鏉＄爜', + 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