初始化单项

初始化单项流程

1.1初始化单项InitSynExamItem，

sfjy00：0单项启用
sfjy00：1单项禁用

同步单项

select * from TBL_ITEM_V
转指定耗材
select KEYNAME from tbl_handlelog where BUSCODE='CONITEMTOMATERIAL' and status=0

1.2初始化科室和单项的关系InitDepartmentExamItemRelation

select BMBH00 as hisDepartmentId,concat(BMMC00,BMBH00) as departmentName, (select WM_CONCAT(SQXMID) as myhisItemIds from HM_ZD_TJSQXM where SFJY00=0 
 and SFJCXM=1 and SFKSID=BMBH00) as myhisItemIds,0 as operateType from PT_BMBM00 where YXBZ00=1

1.3初始化分类和单项的关系 InitSpeciesExamItemRelation

select KDFLID as hisSpeciesId,FLMC00 as speciesName,(select WM_CONCAT(SQXMID) as myhisItemIds from HM_ZD_TJSQXM where SFJY00=0 
 and SFJCXM=1  and KDFLID=HM_ZD_KDFL00.KDFLID) as myhisItemIds,0 as operateType from HM_ZD_KDFL00 where YXBZ00=1

1.4初始化单项关系InitExamItemRelation

select sqxmid as hisItemId,(select WM_CONCAT(a.dcxmid) as myrelationHisItemIds from HM_ZD_SQXMDC  a inner join HM_ZD_TJSQXM b on a.dcxmid=b.sqxmid and b.SFJY00=0 and b. SFJCXM=1 
 where a.sqxmid=HM_ZD_TJSQXM.SQXMID) as myrelationHisItemIds,3 as relationType from HM_ZD_TJSQXM where SFJY00=0 and SFJCXM=1 and SQXMID in(select SQXMID from HM_ZD_SQXMDC where DCXMID
 in (select sqxmid from HM_ZD_TJSQXM where SFJY00=0 and SFJCXM=1)) 
 union all
select sqxmid as hisItemId,(select WM_CONCAT(a.CTXMID) as myrelationHisItemIds from HM_ZD_KDXMCT  a inner join HM_ZD_TJSQXM b on a.CTXMID=b.sqxmid and b.SFJY00=0 and b. SFJCXM=1
 where a.sqxmid=HM_ZD_TJSQXM.SQXMID) as myrelationHisItemIds,2 as relationType from HM_ZD_TJSQXM where SFJY00=0 and SFJCXM=1 and SQXMID in(select SQXMID from HM_ZD_KDXMCT where CTXMID
 in (select sqxmid from HM_ZD_TJSQXM where SFJY00=0 and SFJCXM=1))
 union all
 select sqxmid as hisItemId,(select WM_CONCAT(a.ZXMID0) as myrelationHisItemIds from HM_ZD_SQDXGL  a inner join HM_ZD_TJSQXM b on a.ZXMID0=b.sqxmid and b.SFJY00=0 and b. SFJCXM=1
 where a.ZHXMID=HM_ZD_TJSQXM.SQXMID) as myrelationHisItemIds,4 as relationType from HM_ZD_TJSQXM where SFJY00=0 and SFJCXM=1 and SQXMID in(select ZHXMID from HM_ZD_SQDXGL where ZXMID0
 in (select sqxmid from HM_ZD_TJSQXM where SFJY00=0 and SFJCXM=1))

1.6初始化耗材的分组InitMaterialGroup

select TXMQZ0 as hisMaterialGroupId,TXMMC0 as groupName,1 as operationType from HM_ZD_TXMLB0 where YXBZ00=1

1.7初始化耗材和分组的关系InitMaterialAndGroupRelation

select hisMaterialGroupId,myhisItemMaterialIds from (
select TXMQZ0 as hisMaterialGroupId,(select WM_CONCAT(SQXMID) from HM_ZD_TJSQXM where TMQZ00=HM_ZD_TXMLB0.TXMQZ0 and SFJY00=0 and SFJCXM=0) as myhisItemMaterialIds  
 from HM_ZD_TXMLB0 where YXBZ00=1 ) where myhisItemMaterialIds is not null

根据数据库版本，如果不支持会切换成下面语句

 select hisMaterialGroupId,myhisItemMaterialIds from (
  select TXMQZ0 as hisMaterialGroupId,(select (listagg(SQXMID,',') within group(order by SQXMID)) from HM_ZD_TJSQXM where TMQZ00=HM_ZD_TXMLB0.TXMQZ0 and SFJY00=0 and SFJCXM=0) as myhisItemMaterialIds   from HM_ZD_TXMLB0 where YXBZ00=1 ) where myhisItemMaterialIds is not null

1.8初始化单项与耗材关系数据InitMaterialAndExamItemRelation

select a.sqxmid as hisItemId,a.dcxmid as hisItemMaterialId,b.tmqz00 as hisMaterialGroupId 
from HM_ZD_SQXMDC a inner join HM_ZD_TJSQXM b on a.dcxmid=b.sqxmid and b.SFJY00 =0 and b.SFJCXM=0

单项关联耗材，耗材关联分组，只有在同一个分组的耗材，在添加套餐时，该分组下的耗材仅会出现一次