update SYS_Scripts set xtext = '
DECLARE @lBCK01 int, @lType int, @lDt1 varchar(30),@lDt2 varchar(30), @lInput tinyint,@para396 varchar(10),@param406 varchar(10)
SET @lBCK01 = %d
SET @lType = %d
Set @lDt1 = Convert(varchar(10),Cast(%s as datetime),21)+'' 00:00:00.000''
Set @lDt2 = Convert(varchar(10),Cast(%s as datetime),21)+'' 23:59:59.999''
set @lInput = %d
set @param406 = dbo.GetSysParamValue(100,105003,406)
declare @kbmBCV table(BCK01A int,BCK01B int)
declare @kbmVAP table(VAE01 int,fCount int)
if OBJECT_ID(''tempdb..#kbmvbo'') is not null drop table #kbmvbo
if OBJECT_ID(''tempdb..#kbmvae'') is not null drop table #kbmvae
IF Object_id(''tempdb..#kbmVAO'') IS NOT NULL DROP TABLE #kbmVAO
select a.VAA07,min(a.VAO01) VAOid
into #kbmVAO
from VAO2 a with(nolock)
join VAE1 b with(nolock) on b.VAE01 = a.VAA07
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  

set @para396 = dbo.GetSysParamValue(100,105001,396)
insert into @kbmBCV(BCK01A,BCK01B)
select BCK01A,BCK01B from BCV1 where BCK01A = @lBCK01

select v.VAA07,v.BCK01B  into #kbmvbo 
from VBO1 v with(nolock) 
where @lType = 2 and v.VBO04 IS NULL AND v.VBO06 = 3 AND v.VBO19 IS NULL
  and EXISTS(SELECT m.* FROM @kbmBCV m WHERE m.BCK01A = @lBCK01 AND v.BCK01B = m.BCK01B)
  
select a.*,b.VAA03,a.VAE94 VAA04,a.VAE95 VAA05,a.VAE98 VAA15,a.VAE96 ABW01
  ,b.VAA02,0 BDP05,d.VAM27,d.VAM31,b.VBU01,e1.VAO15,b.VAA82
  ,Case when a.VAE96=''1'' then ''男'' when a.VAE96=''2'' then ''女'' else ''未知'' end ABW02 
into #kbmvae 
from VAE1 a with(nolock)
join VAA1 b with(nolock) on b.VAA01 = a.VAA01
left join VAM1 d with(nolock) on d.VAA07 = a.VAE01
left join #kbmvbo v on a.VAE44 = 3 and v.VAA07 = a.VAE01  
left join #kbmVAO e on e.VAA07 = a.VAE01
left join VAO2 e1 with(nolock) on e1.VAO01 = e.VAOid
WHERE (@lType=1 and ((a.VAE44 in (2,4) and a.BCK01D = @lBCK01)
          or (@lInput = 1 and a.VAE44 = 1 and a.BCK01D = @lBCK01)
          or (a.VAE44 = 5 and a.BCK01D = @lBCK01 and a.VAE26 >= @lDt1 And a.VAE26 <= @lDt2 )))
  OR (@lType=2 and ((a.VAE44=1 and EXISTS(SELECT m.* FROM @kbmBCV m WHERE m.BCK01A = @lBCK01 AND a.BCK01B = m.BCK01B))
	    OR (a.VAE44 in (2,3,4) and a.BCK01C = @lBCK01)
	    OR (a.VAE44 = 3 and EXISTS(SELECT m.* FROM @kbmBCV m WHERE m.BCK01A = @lBCK01 AND v.BCK01B = m.BCK01B))
	    OR (a.VAE44 = 5 and a.BCK01C = @lBCK01 and a.VAE26 >= @lDt1 And a.VAE26 <= @lDt2 )
	    )
  )  
 update a set a.BDP05=isnull(b.BDP05,0) from #kbmvae a join BDP1 b with(nolock) on b.BDP02=a.BDP02
insert into @kbmVAP(VAE01,fCount)
select a.VAE01,COUNT(1) fCount
from #kbmvae a
join VAP1 b with(nolock) on b.VAA07 = a.VAE01
where a.VAE44 >= 2 and a.VAE44 <= 4
  and ((@ltype = 1 and a.BCK01D = @lBCK01)or(@ltype = 2 and a.BCK01C = @lBCK01))
group by a.VAE01  

SELECT a.VAA01,a.VAA02,a.VAA03,a.VAA04,a.VAA05
  ,a.VAA15,a.ABW02,a.VAE01,a.VAE02,a.ABK02
  ,Agep = CASE WHEN isnull(a.VAE46,0)=0 THEN NULL else (ltrim(str(a.VAE46))+c.AAU02) END  
  ,case when isnull(v2.BCQ01,0) > 0 and a.BCK01C <> v2.BCK01A then
     (case when a.ABV01 = ''99'' then ''(暂离)'' else '''' end) + a.BCQ04B+''(借: ''+v3.BCK03+'')''
        else (case when a.ABV01 = ''99'' then ''(暂离)'' else '''' end) + a.BCQ04B end as BCQ04B, v2.BCQ03
  ,a.AAG01,e.AAG02,a.BDP02,a.ABC02,f.ABJ02, a.VAE11,a.VAE26,a.BCE03C,a.BCE02C ,a.BCE03B
  ,FStatus = CASE WHEN a.VAE44=2 THEN ''2-在院病人''
       when a.VAE44=1 then ''0-入院病人'' 
       WHEN a.VAE44=3 and a.BCK01C <> @lBCK01 THEN ''1-转入病人: ''+g.BCK03
       when a.VAE44 = 3 and (a.BCK01C = @lBCK01)and not exists(select * from @kbmBCV s join @kbmBCV s1 on s.BCK01A = s1.BCK01A where s.BCK01B = a.BCK01D and s1.BCK01B = v.BCK01B )
          then ''3-转出病人: ''+v1.BCK03
       when a.VAE44 = 3 and (a.BCK01C = @lBCK01)and exists(select * from @kbmBCV s join @kbmBCV s1 on s.BCK01A = s1.BCK01A where s.BCK01B = a.BCK01D and s1.BCK01B = v.BCK01B )
          then ''1-转出转入：'' + v1.BCK03 
       when a.VAE44 = 4 then ''4-预出院病人''
	   WHEN a.VAE44 = 5 THEN ''5-出院病人'' END 
  ,FVAE04 = case when a.VAE04=1 then ''门诊留观病人''
                 WHEN a.VAE04 = 2 THEN ''住院留观病人'' else ''住院病人'' end     
  ,a.VAE04,a.VAE44,a.VAE80, BCK03 = CASE WHEN a.VAE44 = 3 THEN v1.BCK03 ELSE g.BCK03 END
  ,e.AAG03,a.BCK01D,a.BCK01C,v1.BCK01 AS BCK01V,a.BCK01A,a.BCK01B
  ,case when a.VAE44 < 2 then g1.BCK03 else g.BCK03 end as FBCK03
  ,case when exists(select * from @kbmBCV s join @kbmBCV s1 on s.BCK01A = s1.BCK01A where s.BCK01B = a.BCK01D and s1.BCK01B = v.BCK01B ) then 1 else 0 end FSign
  ,case when ISNULL(s.fCount,0) = 0 then '''' else CONVERT(varchar(10),s.fCount)+''个小孩'' end FVAP,h.VBM08,h.VBM09,h.VBM12
  ,h.VBM04,h.VBM05,isnull(h.VBM04,0)+isnull(h1.VBU14,0)-isnull(h.VBM05,0)+isnull(h.VBM08,0)+isnull(h.VBM12,0) as balance
  ,n.ABO02,ISNULL(c5.VCN13,9) VCN13,isnull(c5.VCN19,0) VCN19,c5.VCN08,c6.VCS07,a.BDP05,a.VAM27,a.VAM31,a.VAE88 
  ,h.VBM13,h.BEP07,a.VAO15,a.VAA82  
FROM #kbmvae a
LEFT JOIN AAU1 c with(nolock) ON c.AAU01 = a.AAU01
LEFT JOIN BBY1 e1 with(nolock) ON a.AAG01 = e1.BBY01
LEFT JOIN AAG1 e with(nolock) ON e1.BCF01 = e.AAG01
LEFT JOIN ABJ1 f with(nolock) ON a.ABJ01 = f.ABJ01
LEFT JOIN BCK1 g with(nolock) ON (a.BCK01c = g.BCK01 and @lType=1) or (@lType=2 and a.BCK01d = g.BCK01)
left join BCK1 g1 with(nolock) on a.BCK01B = g1.BCK01
LEFT JOIN #kbmvbo v ON a.VAE44 = 3 AND a.VAE01=v.VAA07 
LEFT JOIN BCK1 v1 with(nolock) ON v.BCK01B = v1.BCK01
LEFT JOIN BCQ1 v2 with(nolock) ON a.VAA01 = v2.VAA01 and (v2.BCQ13 = 1 or v2.BCQ13 = 3) and a.BCQ04B = v2.BCQ04
left join BCK1 v3 with(nolock) on v2.BCK01A = v3.BCK01
left join @kbmVAP s on s.VAE01 = a.VAE01
left join VBM2 h with(nolock) on h.VAA07 = a.VAE01 and h.ACF01 = 2
left join VBU1 h1 with(nolock) on h1.VBU01 = a.VBU01 and h1.VBU21 = 1 and @param406 <> ''1''
left join ABO1 n with(nolock) on n.ABO01 = a.VAE22
left join VCN1 c5 with(nolock) on c5.VAA07 = a.VAE01 and c5.VCN33=1
left join VCS1 c6 with(nolock) on c6.VCS04=c5.VCN01
WHERE (@lType=1 and ((a.VAE44 in (2,4) and a.BCK01D = @lBCK01)
          or (@lInput = 1 and a.VAE44 = 1 and a.BCK01D = @lBCK01)
          or (a.VAE44 = 5 and a.BCK01D = @lBCK01 and a.VAE26 >= @lDt1 And a.VAE26 <= @lDt2 )))
  OR (@lType=2 and ((a.VAE44=1 and ((@para396<>''1'' and EXISTS(SELECT m.* FROM @kbmBCV m WHERE m.BCK01A = @lBCK01 AND a.BCK01B = m.BCK01B) )
                                    or(@para396=''1'' and (a.BCK01A = @lBCK01 or (ISNULL(a.BCK01A,0)=0
                                     and EXISTS(SELECT m.* FROM @kbmBCV m WHERE m.BCK01A = @lBCK01 AND a.BCK01B = m.BCK01B)) ) )) )
	    OR (a.VAE44 in (2,3,4) and a.BCK01C = @lBCK01)
	    OR (a.VAE44 = 3 and EXISTS(SELECT m.* FROM @kbmBCV m WHERE m.BCK01A = @lBCK01 AND v.BCK01B = m.BCK01B))
	    OR (a.VAE44 = 5 and a.BCK01C = @lBCK01 and a.VAE26 >= @lDt1 And a.VAE26 <= @lDt2 )
	    )
  )
ORDER BY v2.Rownr, a.BCQ04B  

if OBJECT_ID(''tempdb..#kbmvbo'') is not null drop table #kbmvbo
if OBJECT_ID(''tempdb..#kbmvae'') is not null drop table #kbmvae
IF Object_id(''tempdb..#kbmVAO'') IS NOT NULL DROP TABLE #kbmVAO
' where id = 878;