--增加分类
GO
IF not EXISTS(SELECT * FROM SYS_ScriptCategories WHERE id=1)
insert into SYS_ScriptCategories(ID,Code,Name,ComponentID,xRowNum,ParentID,xLevel,Description)  Values(1, 'SC1', 'HOMaster 基础管理', 1, 0, 0, 0, Null)
else
print 'SYS_ScriptCategories.id=1 已经存在.'

GO


If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 1)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=1
GO


--删除旧记录
delete from SYS_Scripts where id=882 --删除脚本内容
GO


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=882)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode,Custom)  Values(882, 33, 'Main_Patient_Item', Null, '住院工作站首页 病人信息', 1, 1, Null, 1, 'DECLARE @lType int ,@lBCK01 int,@adate date
SET @lType = %d
SET @lBCK01 = %d
set @adate = GETDATE()
IF Object_id(''tempdb..#kbmVAO'') IS NOT NULL DROP TABLE #kbmVAO
IF Object_id(''tempdb..#kbmVAT'') IS NOT NULL DROP TABLE #kbmVAT
IF Object_id(''tempdb..#kbmday'') IS NOT NULL DROP TABLE #kbmday
select a.VAA07,min(a.VAO01) VAOid
into #kbmVAO
from VAO2 a with(nolock)
join VAE1 b with(nolock) on b.VAE01 = a.VAA07 and a.ACF01 = 2
where a.ACF01 = 2 and b.VAE44 >= 2 and b.VAE44 <= 3 and a.VAF01 = 0 and a.VAO11 = 2
  and ((@lType = 1 and b.BCK01D = @lBCK01)or(@lType = 2 and b.BCK01C = @lBCK01))
group by a.VAA07  

select a.VAA07,max(a.VAT08) VAT08,CAST('''' as varchar(512)) shday 
into #kbmVAT
from VAT1 a with(nolock)
join VAE1 b with(nolock) on b.VAE01 = a.VAA07 and a.ACF01 = 2
where a.ACF01 = 2 and b.VAE44 >= 2 and b.VAE44 <= 3 and a.VAT04<=4
  and ((@lType = 1 and b.BCK01D = @lBCK01)or(@lType = 2 and b.BCK01C = @lBCK01))
group by a.VAA07 

select b.VAE01,a.VAF36,''术后''+cast(DATEDIFF(DAY,a.VAF36,@adate) as varchar)+''天'' shday
into #kbmday
from VAF2 a with(nolock)
join BBX1 c with(nolock) on c.BBX01=a.BBX01
join VAE1 b with(nolock) on b.VAE01 = a.VAF06
where b.VAE44 >= 2 and b.VAE44 <= 3 and a.BDA01=''Z'' and c.BBX13=''6''
  and ((@lType = 1 and b.BCK01D = @lBCK01)or(@lType = 2 and b.BCK01C = @lBCK01))
order by b.VAE01,a.VAF36  

update s set s.shday=n.shday
from #kbmVAT s
join (SELECT A.VAE01,[SHDAY]=STUFF((SELECT ''/''+[SHDAY] FROM #kbmday B 
       WHERE B.VAE01=A.VAE01 FOR XML PATH('''')),1,1,'''')
   FROM #kbmday A
   GROUP BY A.VAE01 ) n on n.VAE01=s.VAA07  
IF Object_id(''tempdb..#kbmday'') IS NOT NULL DROP TABLE #kbmday
SELECT CASE WHEN a.BCQ13=0 THEN (isnull(a.BCQ03,'''')+''-''+a.BCQ04+'' 空床'')
         WHEN a.BCQ13=1 AND c.VAE01 >0 THEN (isnull(a.BCQ03,'''')+''-''+a.BCQ04+'' 占用'')
         WHEN a.BCQ13=1 AND isnull(c.VAE01,0)=0 THEN (isnull(a.BCQ03,'''')+''-''+a.BCQ04+'' ''+isnull(v1.BCK03,'''')+''借床'')
         WHEN a.BCQ13=2 AND c.VAE01 >0 THEN (isnull(a.BCQ03,'''')+''-''+a.BCQ04+'' 包床'')
         WHEN a.BCQ13=2 AND isnull(c.VAE01,0)=0 THEN (isnull(a.BCQ03,'''')+''-''+a.BCQ04+'' ''+isnull(v1.BCK03,'''')+''借床'')
         WHEN a.BCQ13=3 AND c.VAE01 >0 THEN (isnull(a.BCQ03,'''')+''-''+a.BCQ04+'' 包房'')
         WHEN a.BCQ13=3 AND isnull(c.VAE01,0)=0 THEN (isnull(a.BCQ03,'''')+''-''+a.BCQ04+'' ''+isnull(v1.BCK03,'''')+''借床'')
         WHEN a.BCQ13=4 THEN (isnull(a.BCQ03,'''')+''-''+a.BCQ04+'' 维修'')
         WHEN a.BCQ13=5 THEN (isnull(a.BCQ03,'''')+''-''+a.BCQ04+'' 撤编'') END AS BedNo
   ,Case WHEN a.BCQ13=1 AND isnull(c.VAE01,0)=0 then v.VAE94+'' ''+Isnull(v.BDP02,'''') 
         else c.VAE94+''  ''+isnull(c.BDP02,'''') END AS IPTNo
   ,Case WHEN a.BCQ13=1 AND isnull(c.VAE01,0)=0 then v.VAE95+'' ''+isnull(v3.ABW02,'''')
      else (CASE WHEN isnull(c.VAE46,0)=0 THEN c.VAE95+'' ''+isnull(f.ABW02,'''')+'' ''+isnull(g.ABL02,'''')
            else c.VAE95+'' ''+isnull(f.ABW02,'''')+'' ''+(ltrim(str(c.VAE46))+isnull(e.AAU02,''''))+'' ''+isnull(g.ABL02,'''') END)
      end  AS PName
   ,CASE a.BCQ13 WHEN 2 THEN NULL ELSE m.ABO02 END AS ABO02 
   ,CASE a.BCQ13 WHEN 2 THEN NULL 
       ELSE (Case WHEN a.BCQ13=1 AND isnull(c.VAE01,0)=0 then v.VAE11 else c.VAE11 End) END AS VAE11
   ,CASE WHEN a.BCQ13 = 2 THEN NULL 
         when a.BCQ13 = 1 and isnull(c.VAE01,0)=0 then v6.VAO15
       ELSE c2.VAO15 END AS VAE42
   ,CASE a.BCQ13 WHEN 2 THEN NULL ELSE h.AAG03 END AS AAG03,c.BCK01D
   ,CASE a.BCQ13 WHEN 2 THEN NULL ELSE c.BCE03B END AS BCE03B
   ,CASE a.BCQ13 WHEN 2 THEN NULL ELSE c.BCE03C END AS BCE03C
   ,CASE a.BCQ13 WHEN 2 THEN NULL ELSE k.BCK03 END AS BCK03D
   ,b.BBY05 ,CASE a.BCQ13 WHEN 2 THEN NULL ELSE d.VAA62 END AS VAA62
   ,a.BCK01A,a.BCQ03,a.BCQ04,a.BCK01B,a.BCQ01,a.BCQ13,a.BCQ09
   ,c.VAE01,c.VAE02,c.VAA01,c.BCK01C,c.BCK01D,c.VAE44
   ,CASE WHEN (a.BCQ09 = 0)and(@lType=1) THEN a1.BCK03
         WHEN (a.BCQ09 = 0)and(@lType=2) THEN a2.BCK03
         WHEN (a.BCQ09 = 1)and(@lType=1) THEN a1.BCK03+'' 共用''   
         ELSE ''共用'' END AS BCK03N,a.Rownr
  ,Case When c1.BDP05 is not null And v5.BDP05 is null then isnull(c1.BDP05,0)
        When v5.BDP05 is not null And c1.BDP05 is null then isnull(v5.BDP05,0) else 0 End As FBDP05
  ,ISNULL(c5.VCN13,9) VCN13,c.VAE22,isnull(c5.VCN19,0) VCN19,ISNULL(c1.BDP07,0) fbdp07
  , case when DATEDIFF(DAY,c.VAE11,@adate)=0 then 1 else 0 end fnew ,d.VAA62
  ,case when c.VAE01>0 then CAST(dbo.GetVAE27(c.VAE11,isnull(c.VAE26,Getdate())) as varchar)+''天'' else '''' end fday
  ,convert(varchar(10),d1.VAT08,120) vat08,d1.shday,c.VAE83,c.VAE71
FROM BCQ1 a
  LEFT JOIN BBY1 b with(nolock) ON case when a.BCQ13=2 then isnull(a.BBY01B,a.BBY01) else a.BBY01 end=b.BBY01 
  LEFT JOIN VAE1 c with(nolock) ON a.VAA01 = c.VAA01 and c.VAE44 >= 2 and c.VAE44 < 5 and a.BCK01A=c.BCK01C
  LEFT JOIN VAA1 d with(nolock) ON c.VAA01 = d.VAA01
  LEFT JOIN BDP1 c1 ON c.BDP02 = c1.BDP02
  left join #kbmVAO c3 on c3.VAA07 = c.VAE01  
  left join VAO2 c2 with(nolock) on c2.VAO01 = c3.VAOid
  LEFT join #kbmVAT d1 on d1.VAA07=c.VAE01
  LEFT JOIN AAU1 e with(nolock) ON e.AAU01 = c.AAU01
  LEFT JOIN ABW1 f with(nolock) ON d.ABW01 = f.ABW01
  LEFT JOIN ABL1 g with(nolock) ON d.ABL01 = g.ABL01
  LEFT JOIN BBY1 h1 with(nolock) ON c.AAG01 = h1.BBY01
  LEFT JOIN AAG1 h with(nolock) ON h1.BCF01 = h.AAG01
  LEFT JOIN BCK1 k with(nolock) ON c.BCK01D = k.BCK01
  LEFT JOIN ABO1 m with(nolock) ON c.VAE22 = m.ABO01
  LEFT JOIN BCK1 a1 with(nolock) ON a.BCK01A = a1.BCK01
  LEFT JOIN BCK1 a2 with(nolock) ON a.BCK01B = a2.BCK01
  left join VAE1 v with(nolock) on a.VAA01=v.VAA01 and a.BCQ13>0 and v.VAE44 >= 2 and v.VAE44 < 5 and a.BCK01A<>v.BCK01C
  left join VAA1 v2 with(nolock) on v.VAA01 = v2.VAA01
  LEFT JOIN BDP1 v5 with(nolock) ON v.BDP02 = v5.BDP02
  left join BCK1 v1 with(nolock) on v.BCK01C=v1.BCK01  
  left join ABW1 v3 with(nolock) on v2.ABW01 = v3.ABW01
  left join #kbmVAO v7 on v7.VAA07 = v.VAE01
  left join VAO2 v6 with(nolock) on v6.VAO01 = v7.VAOid
  left join VCN1 c5 with(nolock) on c5.VAA07 = c.VAE01 and c5.VCN33=1
WHERE a.BCQ13 >= 0 and a.BCQ13 <= 3
  and ((@lType = 1 AND ((a.BCK01B = @lBCK01 AND a.BCQ09=0)OR(a.BCQ09=1 AND ((a.VAA01 =0 ) OR (a.VAA01 > 0 AND c.BCK01D=@lBCK01))
 			    AND EXISTS(SELECT s.* FROM BCV1 s WHERE s.BCK01B=@lBCK01 AND a.BCK01A=s.BCK01A))))
  OR(@lType = 2 AND a.BCK01A = @lBCK01)) 
UNION ALL
SELECT CASE a.BCQ13 WHEN 1 THEN (isnull(a.BCQ03,'''')+''-''+a.BCQ04+'' 借床''+isnull(a1.BCK03,''''))
         WHEN 2 THEN (isnull(a.BCQ03,'''')+''-''+a.BCQ04+'' 包床''+isnull(a1.BCK03,''''))
         WHEN 3 THEN (isnull(a.BCQ03,'''')+''-''+a.BCQ04+'' 包房''+isnull(a1.BCK03,'''')) END AS BedNo
   ,CASE WHEN isnull(c.BDP02,''0'')=''0'' THEN c.VAE94 ELSE (c.VAE94+''  ''+c.BDP02) END AS IPTNo
   ,CASE WHEN isnull(c.VAE46,0)=0 THEN c.VAE95+'' ''+isnull(f.ABW02,'''')+'' ''+isnull(g.ABL02,'''')
      else c.VAE95+'' ''+isnull(f.ABW02,'''')+'' ''+(ltrim(str(c.VAE46))+isnull(e.AAU02,''''))+'' ''+isnull(g.ABL02,'''') END AS PName
   ,CASE a.BCQ13 WHEN 2 THEN NULL ELSE m.ABO02 END AS ABO02 
   ,CASE a.BCQ13 WHEN 2 THEN NULL ELSE c.VAE11 END AS VAE11
   ,CASE a.BCQ13 WHEN 2 THEN NULL ELSE c2.VAO15 END AS VAE42
   ,CASE a.BCQ13 WHEN 2 THEN NULL ELSE h.AAG03 END AS AAG03,c.BCK01D
   ,CASE a.BCQ13 WHEN 2 THEN NULL ELSE c.BCE03B END AS BCE03B 
   ,CASE a.BCQ13 WHEN 2 THEN NULL ELSE c.BCE03C END AS BCE03C
   ,CASE a.BCQ13 WHEN 2 THEN NULL ELSE k.BCK03 END AS BCK03D
   ,b.BBY05 ,CASE a.BCQ13 WHEN 2 THEN NULL ELSE d.VAA62 END AS VAA62
   ,a.BCK01A,a.BCQ03,a.BCQ04,a.BCK01B,a.BCQ01,a.BCQ13,a.BCQ09
   ,c.VAE01,c.VAE02,c.VAA01,c.BCK01C,c.BCK01D,c.VAE44
   ,CASE WHEN (a.BCQ09 = 0)and(@lType=1) THEN a1.BCK03
         WHEN (a.BCQ09 = 0)and(@lType=2) THEN a2.BCK03
         WHEN (a.BCQ09 = 1)and(@lType=1) THEN a1.BCK03+'' 共用''   
         ELSE ''共用'' END AS BCK03N,a.Rownr, 0 FBDP05 
   ,ISNULL(c5.VCN13,9) VCN13,c.VAE22,isnull(c5.VCN19,0) VCN19 ,0 bdp07,0 fnew,d.VAA62 
   ,cast(dbo.GetVAE27(c.VAE11,isnull(c.VAE26,Getdate())) as varchar)+''天'' fday     
   ,convert(varchar(10),d1.VAT08,120) vat08,d1.shday,c.VAE83,c.VAE71    
FROM BCQ1 a
  JOIN VAE1 c with(nolock) ON a.VAA01 = c.VAA01 AND c.VAE44>=2 AND c.VAE44<4 and a.BCK01A<>c.BCK01C
  JOIN VAA1 d with(nolock) ON c.VAA01 = d.VAA01
  left join #kbmVAO c3 on c3.VAA07 = c.VAE01
  left join VAO2 c2 with(nolock) on c2.VAO01 = c3.VAOid
  LEFT join #kbmVAT d1 on d1.VAA07=c.VAE01
  LEFT JOIN BBY1 b with(nolock) ON case when a.BCQ13=2 then isnull(a.BBY01B,a.BBY01) else a.BBY01 end=b.BBY01 
  LEFT JOIN AAU1 e with(nolock) ON e.AAU01 = c.AAU01
  LEFT JOIN ABW1 f with(nolock) ON d.ABW01 = f.ABW01
  LEFT JOIN ABL1 g with(nolock) ON d.ABL01 = g.ABL01
  LEFT JOIN BBY1 h1 with(nolock) ON c.AAG01 = h1.BBY01
  LEFT JOIN AAG1 h with(nolock) ON h1.BCF01 = h.AAG01
  LEFT JOIN BCK1 k with(nolock) ON c.BCK01D = k.BCK01
  LEFT JOIN ABO1 m with(nolock) ON c.VAE22 = m.ABO01
  LEFT JOIN BCK1 a1 with(nolock) ON a.BCK01A = a1.BCK01
  LEFT JOIN BCK1 a2 with(nolock) ON a.BCK01B = a2.BCK01
  left join VCN1 c5 with(nolock) on c5.VAA07 = c.VAE01 and c5.VCN33=1
WHERE a.VAA01>0 and a.BCQ13>0 and a.BCQ13<= 3 
 and ((@lType = 1 and c.BCK01D = @lBCK01
       AND EXISTS(SELECT s.* FROM BCV1 s with(nolock) WHERE s.BCK01B=@lBCK01 AND c.BCK01C=s.BCK01A))
  OR(@lType = 2 AND a.BCK01A <> @lBCK01 and c.BCK01C=@lBCK01))   
order by a.Rownr,a.BCQ04

IF Object_id(''tempdb..#kbmVAO'') IS NOT NULL DROP TABLE #kbmVAO
IF Object_id(''tempdb..#kbmVAT'') IS NOT NULL DROP TABLE #kbmVAT', '2018-09-03 17:35:40', '(8058)高瑜', 0, 0)
else
print 'SYS_Scripts.id=882 已经存在.'

GO


If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 882)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=882
GO