单项同步

1.同步单项

select * from TBL_ITEM_V

2.同步科室和单项关系

select id, KEYID as hisDepartmentId,KEYNAME as departmentName,'' as myhisItemIds,(case when 
(HANDLETYPE=1 or HANDLETYPE=2) then 0 else 2 end) as operateType from 
tbl_handlelog where BUSCODE='DEPTCHANGE' and status=0
union all
select id, RELATEDID as hisDepartmentId,RELATEDNAME as departmentName,KEYID as myhisItemIds, (case when 
(HANDLETYPE=1 or HANDLETYPE=2) then 0  when HANDLETYPE=3 then 1 else 2 end) as operateType
from tbl_handlelog where BUSCODE='ITEMBINDDEPT' and status=0

同步分类和单项关系

select id, KEYID as hisSpeciesId,KEYNAME as speciesName,'' as myhisItemIds,(case when 
(HANDLETYPE=1 or HANDLETYPE=2) then 0 else 2 end) as operateType from 
tbl_handlelog where BUSCODE='CATEGORYCHANGE' and status=0
union all
select id, RELATEDID as hisSpeciesId,RELATEDNAME as speciesName,KEYID as myhisItemIds, (case when 
(HANDLETYPE=1 or HANDLETYPE=2) then 0 else 2 end) as operateType
from tbl_handlelog where BUSCODE='ITEMBINDCATEGORY' and status=0

同步单项项目关系

select id,keyid as hisItemId,(select WM_CONCAT(CTXMID)  from HM_ZD_KDXMCT where sqxmid=keyid and sqxmfs =2  group by  CTXMID) as myrelationHisItemIds,2 as relationType
 from tbl_handlelog where BUSCODE='ITEMRELATIONMUTEX' and status=0
union all
select id,keyid as hisItemId,(select WM_CONCAT(DCXMID)  from HM_ZD_SQXMDC where sqxmid=keyid  and DCXMID in 
(select sqxmid from HM_ZD_TJSQXM where SFJCXM=1 and SFJY00=0)  group by  DCXMID) as myrelationHisItemIds,3 as relationType
 from tbl_handlelog where BUSCODE='ITEMRELATIONRELYON' and status=0
 union all
select id,keyid as hisItemId,(select WM_CONCAT(ZXMID0) from HM_ZD_SQDXGL where ZHXMID=keyid   group by  ZXMID0) as myrelationHisItemIds,4 as relationType
 from tbl_handlelog where BUSCODE='ITEMRELATIONFATHERSON' and status=0

同步耗材的分组

select id, KEYID as hisMaterialGroupId,KEYNAME as groupName,(case when HANDLETYPE=1 then 1 when  HANDLETYPE=2 then 3 else 2 end)
as operationType from 
tbl_handlelog where BUSCODE='GROUPCHANGE' and status=0

同步耗材分组和耗材的关系

select id,RELATEDID as hisMaterialGroupId,(select WM_CONCAT(SQXMID) from HM_ZD_TJSQXM where TMQZ00=RELATEDID  and SFJCXM=0 group by SQXMID) as myhisItemMaterialIds
 from tbl_handlelog where BUSCODE='MATERIALBINDGROUP' and status=0

同步单项和耗材的关系

select id,keyid as hisItemId,(select WM_CONCAT(DCXMID)   from HM_ZD_SQXMDC where sqxmid=keyid  and DCXMID in 
            (select sqxmid from HM_ZD_TJSQXM where SFJCXM=0 and SFJY00=0) group by DCXMID )  as myrelationHisItemIds
             from tbl_handlelog where BUSCODE='ITEMMATERIALRELATIONRELYON' and status=0