# 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