# lis、pacs视图
|
# 地址：10.100.100.205
|
# 数据库名：ltkjview
|
# 用户：ltkjview
|
# 密码：ltkj@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;
|
|
# 外送PDF报告表
|
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报告单base64串',
|
jpg_base64 text NULL COMMENT 'jpg报告单base64串'
|
) COMMENT '外送PDF回传';
|
|
# 清库
|
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
|
# 是否开启同步his项目
|
DECLARE isOpenTbHisProject VARCHAR(10);
|
# 是否开启同步公共套餐
|
DECLARE isOpenCommonTaoCan VARCHAR(10);
|
# 是否开启同步单位套餐
|
DECLARE isOpenDeptTaoCan VARCHAR(10);
|
|
main_block: BEGIN
|
# 凌云价格同步存储过程，his停用的项目不要从套餐 分组中删除，只修改价格
|
# 存储过程调用写到中间层，加参数配置默认不同步
|
|
|
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)
|
# 已停用项目ids
|
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
|
