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();
|
