CREATE TABLE `tb_proprice_his` (
   `pro_id` bigint DEFAULT NULL COMMENT '小项id',
   `old_price` varchar(255) DEFAULT NULL,
   `new_price` varchar(255) DEFAULT NULL,
   `time` datetime DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE `tb_project_price_history` (
    `pro_id` bigint DEFAULT NULL COMMENT '小项id',
    `old_price` varchar(255) DEFAULT NULL,
    `new_price` varchar(255) DEFAULT NULL,
    `time` datetime DEFAULT CURRENT_TIMESTAMP
) COMMENT='his同步价格历史记录表';

DROP PROCEDURE IF EXISTS `tb_check_hisproject`;
CREATE DEFINER=`root`@`%` PROCEDURE `tb_check_hisproject`()
BEGIN
    # 子项临时表
    DROP TEMPORARY TABLE IF EXISTS temp_tb_check_hisproject_proid;
# 大项临时表
    DROP TEMPORARY TABLE IF EXISTS temp_tb_check_hisproject_parent_proid;
# 被停用的大项临时表
    DROP TEMPORARY TABLE IF EXISTS temp_tb_check_hisproject_parent_updated;

    CREATE TEMPORARY TABLE temp_tb_check_hisproject_proid(id BIGINT,p_id BIGINT);
    CREATE TEMPORARY TABLE temp_tb_check_hisproject_parent_proid(id BIGINT);
    CREATE TEMPORARY TABLE temp_tb_check_hisproject_parent_updated (id BIGINT);

# 提取项目表hisxmdm在his表不存在的小项
    INSERT INTO temp_tb_check_hisproject_proid (id,p_id)
    SELECT tp.pro_id,tp.pro_parent_id
    FROM tj_project tp
    WHERE tp.pro_status = 0
      AND tp.pro_parent_id <> 0
      AND tp.deleted = 0
      AND tp.his_xmbm <> ''
      AND tp.pro_parent_id = 1633660948860522504
      AND NOT EXISTS (
        SELECT 1
        FROM ltkj_mxjfxmzd lm
        WHERE lm.xmdm = tp.his_xmbm
    );

# 根据提取的小项拿大项
    INSERT INTO temp_tb_check_hisproject_parent_proid
    SELECT p.p_id FROM temp_tb_check_hisproject_proid p GROUP BY p.p_id;

# 修改子项状态为停用
    UPDATE tj_project t1 JOIN temp_tb_check_hisproject_proid t2 ON t1.pro_id = t2.id SET t1.pro_status = 1;
# 修改父项状态为停用
    INSERT INTO temp_tb_check_hisproject_parent_updated (id)
    SELECT p.pro_id
    FROM tj_project p
             JOIN temp_tb_check_hisproject_parent_proid t ON p.pro_id = t.id
    WHERE NOT EXISTS (
        SELECT 1
        FROM tj_project AS child
        WHERE child.pro_parent_id = p.pro_id
          AND child.pro_status = 0
          AND child.deleted = 0
          AND child.his_xmbm <> ''
    );
    UPDATE tj_project p JOIN temp_tb_check_hisproject_parent_updated t ON p.pro_id = t.id SET p.pro_status = 1;

# 套餐、组合中移除未启用项目
    DELETE FROM tj_package_project WHERE pro_id IN (SELECT id FROM temp_tb_check_hisproject_parent_updated);
    DELETE FROM tj_grouping_pro WHERE pro_id IN (SELECT id FROM temp_tb_check_hisproject_parent_updated);
    DELETE FROM tj_zhxmglpro WHERE pro_id IN (SELECT id FROM temp_tb_check_hisproject_parent_updated);

# 调用同步价格存储过程
    CALL tb_hisprodj();

    # 修改项目价格
--  UPDATE tj_project a
--     JOIN temp_tb_check_hisproject_parent_proid b ON a.pro_id = b.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;
--
--   -- 4 同步套餐关联项目表价格
--
--      UPDATE tj_package_project a  JOIN tj_project b ON a.pro_id=b.pro_id
--
--      SET a.price_now = b.pro_price * (a.limits/10),
--      a.price_ord = b.pro_price
--      WHERE a.pro_id= b.pro_id AND b.pro_id IN (select id from temp_tb_check_hisproject_parent_proid);
--
--
-- 	-- 5 同步套餐价格
--        UPDATE tj_package aa
--        JOIN (
--        	SELECT
--        		a.pac_id,
--        		SUM( a.price_now ) pr,
--           sum(a.price_ord) olpr
--        	FROM
--        		tj_package_project a
--        	GROUP BY
--        		a.pac_id
--        	) bb
--        	SET aa.price = bb.olpr ,
--         aa.new_price = bb.pr
--        WHERE
--        	aa.pac_id = bb.pac_id;
--
-- -- 单位套餐明细项目
--      update tj_grouping_pro a
--      join  tj_project b ON a.pro_id=b.pro_id
--      SET a.ys_price = b.pro_price * (a.limits/10),
--      a.pro_price = b.pro_price
--      WHERE a.pro_id= b.pro_id;
--   -- 单位套餐总价
--      UPDATE tj_dw_grouping aa
--        JOIN (
--        	SELECT
--        		a.grouping_id,
--        		SUM( a.ys_price ) pr,
--           sum(a.pro_price) olpr
--        	FROM
--        		tj_grouping_pro a
--        	GROUP BY
--        		a.grouping_id
--        	) bb
--        	SET aa.price = bb.olpr ,
--         aa.ys_price = bb.pr
--        WHERE
--        	aa.id = bb.grouping_id;
--
-- DROP TEMPORARY TABLE IF EXISTS temp_tb_check_hisproject_proid;
-- DROP TEMPORARY TABLE IF EXISTS temp_tb_check_hisproject_parent_proid;

# 子项临时表
    DROP TEMPORARY TABLE IF EXISTS temp_tb_check_hisproject_proid;
# 大项临时表
    DROP TEMPORARY TABLE IF EXISTS temp_tb_check_hisproject_parent_proid;
# 被停用的大项临时表
    DROP TEMPORARY TABLE IF EXISTS temp_tb_check_hisproject_parent_updated;
END


DROP PROCEDURE IF EXISTS `tb_hisprodj`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `tb_hisprodj`()
BEGIN

-- 1 同步父项目对应子项的数量(项目数量和his中数量不一致的项目)


--   UPDATE tj_project a
-- 	   LEFT JOIN tj_project b ON a.pro_id = b.pro_parent_id
-- 	   LEFT JOIN ltkj_jfzhglmxjfxmzd c ON a.his_xmbm = c.zhxmdm AND c.xmdm=b.his_xmbm
-- 	   LEFT JOIN ltkj_mxjfxmzd d ON c.xmdm = d.xmdm
-- 	   SET b.sl=c.sl
-- 	   WHERE
-- 	   a.deleted = 0
-- 	   AND b.deleted = 0
-- 	   AND b.sl != c.sl
-- 	   AND b.pro_name NOT LIKE '%费%' AND b.pro_name NOT LIKE '静脉采血';

-- 2 同步明细项目以及明细项目对应的父项目

    DROP TEMPORARY TABLE IF EXISTS temp_tb_hisdj_parent_ids;

    CREATE TEMPORARY TABLE temp_tb_hisdj_parent_ids (id BIGINT);
-- 查询his和体检单项价格不对应的所有大项id
    INSERT INTO temp_tb_hisdj_parent_ids (id)
    SELECT DISTINCT a.pro_parent_id
    FROM tj_project a
    JOIN ltkj_mxjfxmzd b ON a.his_xmbm = b.xmdm
    WHERE a.deleted = 0 and a.pro_status=0 AND a.pro_parent_id != 0 AND a.pro_price <> b.xmdj ;

    delete from  tb_proprice_his;
    -- 查询 所有子项目的价格及项目id
    INSERT INTO tb_proprice_his(pro_id,old_price,new_price)
    SELECT a.pro_id,a.pro_price,b.xmdj  FROM tj_project a
    JOIN ltkj_mxjfxmzd b ON a.his_xmbm = b.xmdm
    join temp_tb_hisdj_parent_ids d on d.id = a.pro_parent_id
    WHERE a.deleted = 0 and a.pro_status=0 AND a.pro_parent_id != 0 AND a.pro_price <> b.xmdj  AND a.his_xmbm IS NOT NULL AND a.his_xmbm <> '';

    UPDATE tj_project a
    JOIN tb_proprice_his b ON a.pro_id = b.pro_id
    SET a.pro_price = b.new_price,
        a.hisdj = b.new_price
    WHERE a.deleted = 0  and a.pro_status=0;

    UPDATE tj_project a
    JOIN temp_tb_hisdj_parent_ids b ON a.pro_id = b.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;



  -- 4 同步套餐关联项目表价格

     UPDATE tj_package_project a  JOIN tj_project b ON a.pro_id=b.pro_id

     SET a.price_now = b.pro_price * (a.limits/10),
     a.price_ord = b.pro_price
     WHERE a.pro_id= b.pro_id;



	-- 5 同步套餐价格
       UPDATE tj_package aa
       JOIN (
       	SELECT
       		a.pac_id,
       		SUM( a.price_now ) pr,
          sum(a.price_ord) olpr
       	FROM
       		tj_package_project a
       	GROUP BY
       		a.pac_id
       	) bb
       	SET aa.price = bb.olpr ,
        aa.new_price = bb.pr
       WHERE
       	aa.pac_id = bb.pac_id;

-- 单位套餐明细项目
     update tj_grouping_pro a
     join  tj_project b ON a.pro_id=b.pro_id
     SET a.ys_price = b.pro_price * (a.limits/10),
     a.pro_price = b.pro_price
     WHERE a.pro_id= b.pro_id;
  -- 单位套餐总价
     UPDATE tj_dw_grouping aa
       JOIN (
       	SELECT
       		a.grouping_id,
       		SUM( a.ys_price ) pr,
          sum(a.pro_price) olpr
       	FROM
       		tj_grouping_pro a
       	GROUP BY
       		a.grouping_id
       	) bb
       	SET aa.price = bb.olpr ,
        aa.ys_price = bb.pr
       WHERE
       	aa.id = bb.grouping_id;

    DROP TEMPORARY TABLE IF EXISTS temp_tb_hisdj_parent_ids;

   insert into tb_project_price_history
   select * from tb_proprice_his;


END


# 将套餐 组合 单位套餐中 所有胸腹连透项目替换为胸部正位片
UPDATE tj_package_project SET pro_id = 1740177341251154261,price_now = 40,limits = 10,price_ord = 40 WHERE pro_id = 1633660948860522504;
UPDATE tj_grouping_pro a
    JOIN (
        SELECT id FROM tj_grouping_pro WHERE pro_id = 1633660948860522504
    ) b ON a.id = b.id
SET a.pro_id = 1740177341251154261,
    a.pro_name = '胸部正位片',
    a.pro_py_name = 'xbzwp',
    a.pro_price = 40,
    a.limits = 10,
    a.ys_price = 40;
call tb_check_hisproject();