--增加分类
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=1669 --删除脚本内容
GO


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=1669)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode,Custom)  Values(1669, 47, 'SQL_检索待手术病人', '检索待手术病人', '检索待手术病人--未完成病人,包括门诊病人与住院病人', 1, 0, Null, 1, 'Declare @lBCK01 int, @lMember tinyint, @aDate datetime, @aDay int
Set @lBCK01 = %d
set @lMember = %d

set @aDay = cast(dbo.GetSysParamValue(100,106004,1) as int)
if @aDay = 0
  set @aDate = Convert(varchar(10),DateAdd(Day,-60,GetDate()),21)
else
  Set @aDate = Convert(varchar(10),DateAdd(Day,-@aDay,GetDate()),21)

--说明：上面两个参数及名称是固定的不能修改，其他条件可以随意加，包括下面的字段也不能改变只能增加不能减少 

IF object_id(''tempdb..#tmp_VAT'') IS NOT NULL DROP TABLE #tmp_VAT
IF object_id(''tempdb..#tmp_VAF1'') IS NOT NULL DROP TABLE #tmp_VAF1
IF object_id(''tempdb..#tmp_VAF2'') IS NOT NULL DROP TABLE #tmp_VAF2
SELECT a.VAT01 , a.VAT04 , a.BEE01 , a.BEE03 ,a.VAT33 , a.BCK01 , a.BBX01 
  , a.VAA01 , a.VAA07 , a.ACF01 , a.VAT08,a.VAT09,a.VAF01,a.VCY01
  , ( select Case when MAX(Feeflag)>0 then 1 else 0 end as  Feeflag from 
            (select top 1 1 as Feeflag from VAI1 T2  where t2.VAA01=a.VAA01 and T2.VAI16<2  and T2.CBM01=a.vat01 and T2.VAI18>=5
             union all
             select top 1 1 as Feeflag from VAI2 T2  where t2.VAA01=a.VAA01  and T2.VAI16<2  and T2.CBM01=a.vat01 and T2.VAI18>=5 ) t3
    ) as Feeflag
   into #tmp_VAT
FROM VAT1 a  
where a.VAT04 <= 4  and a.VAT08 >= @aDate

select v.bda01,v.vaf35,v.VAF22,v.BCE03A,v.VAF01,v.CBM01,v.BBX01,v.BCK01A,v.BCK01B,V.BCK01D,v.VAF01A,v.VAA01,v.VAF06,v.VAF47,v.VAF23,v.BCE01A into #tmp_VAF1
from VAF1 v
where v.VAF47 >= @aDate and v.VAF10 >=8 and v.VAF10 <= 9 and (v.bck01b=@lBCK01 or v.bck01d=@lBCK01)

select v.bda01,v.vaf35,v.VAF22,v.BCE03A,v.VAF01,v.CBM01,v.BBX01,v.BCK01A,v.BCK01B,V.BCK01D,v.VAF01A,v.VAA01,v.VAF06,v.VAF47,v.VAF23,v.BCE01A into #tmp_VAF2
from VAF2 v
where v.VAF47 >= @aDate and v.VAF10 >=8 and v.VAF10 <= 9 and (v.bck01b=@lBCK01 or v.bck01d=@lBCK01)

  
SELECT a.VAT01 , a.VAT04 ,b.VAA03, b.VAA04, b.VAA05 , c.ABW02 , b.VAA10 , a.BEE01 , a.BEE03 ,b.ABBRP,B.ABBRW
  ,a.VAT33 ,d.BCQ04B ,f.BAK05 ,((case when v.VAF35 = 1 then ''(紧急)'' else '''' end)+isnull(g.BBX05,v.VAF22)) BBX05 
  , m.BBX05 MBBX05 , n.BCK03 , a.BCK01 , a.BBX01 
  , a.VAA01 , a.VAA07 , a.ACF01 , d.VAE44,a.VAT08,v.BCE03A,v.VAF01,v.CBM01,a.VAT09,isnull(b.VBU01,0) VBU01 
  ,v.BCE01A,k.BCE01 BCE01C,a.FeeFlag  
FROM #tmp_VAT a  
  JOIN VAA1 b with(nolock) ON a.VAA01 = b.VAA01
  JOIN VAE1 d with(nolock) ON a.VAA07 = d.VAE01
  JOIN #tmp_VAF2 v   ON a.VAF01 = v.VAF01 
  left JOIN BBX1 g with(nolock) ON v.BBX01 = g.BBX01
  LEFT JOIN BCK1 n with(nolock) ON v.BCK01A = n.BCK01
  LEFT JOIN BBX1 m with(nolock) ON a.BBX01 = m.BBX01 
  LEFT JOIN VAO2 e with(nolock) ON a.VAF01 = e.VAF01 AND e.VAO11 = 8 
  LEFT JOIN BAK1 f with(nolock) ON e.BAK01A = f.BAK01 
  LEFT JOIN ABW1 c with(nolock) ON b.ABW01 = c.ABW01
  LEFT JOIN BCE1 k with(nolock) on k.BCE02=d.BCE02C
where a.VAT04 <= 4 and d.VAE44 >= 1 and d.VAE44<= 5 and a.VAT08 >= @aDate
  and v.BCK01B = @lBCK01
union all
SELECT a.VAT01 , a.VAT04 ,b.VAA03, b.VAA04, b.VAA05 , c.ABW02 , b.VAA10 , a.BEE01 , a.BEE03 ,b.ABBRP,B.ABBRW
  ,a.VAT33 ,d.BCQ04B ,f.BAK05 ,((case when v.VAF35 = 1 then ''(紧急)'' else '''' end)+isnull(g.BBX05,v.VAF22)) BBX05 
  , m.BBX05 MBBX05 , n.BCK03 , a.BCK01 , a.BBX01 
  , a.VAA01 , a.VAA07 , a.ACF01 , d.VAE44,a.VAT08,v.BCE03A,v.VAF01,v.CBM01,a.VAT09,isnull(b.VBU01,0) VBU01
    ,v.BCE01A,k.BCE01 BCE01C,a.FeeFlag   
FROM #tmp_VAT a    
  JOIN VAA1 b  with(nolock)  ON a.VAA01 = b.VAA01
  JOIN VAE1 d  with(nolock)  ON a.VAA07 = d.VAE01
  JOIN #tmp_VAF2 v    ON a.VAF01 = v.VAF01 
  left JOIN BBX1 g with(nolock)  ON v.BBX01 = g.BBX01
  LEFT JOIN BCK1 n  with(nolock) ON v.BCK01A = n.BCK01
  LEFT JOIN BBX1 m  with(nolock) ON a.BBX01 = m.BBX01 
  LEFT JOIN VAO2 e  with(nolock) ON a.VAF01 = e.VAF01 AND e.VAO11 = 8 
  LEFT JOIN BAK1 f with(nolock)  ON e.BAK01A = f.BAK01 
  LEFT JOIN ABW1 c with(nolock)  ON b.ABW01 = c.ABW01
  LEFT JOIN BCE1 k with(nolock) on k.BCE02=d.BCE02C
where a.VAT04 <= 4 and d.VAE44 >= 1 and d.VAE44<= 5 and a.VAT08 >= @aDate
  and v.BCK01B <> @lBCK01 and exists(select vaf01 from #tmp_VAF2 v1 where v1.VAF01A = v.VAF01 and v1.BCK01B = @lBCK01)
Union All
SELECT a.VAT01 , a.VAT04 ,b.VAA03, b.VAA03 VAA04, b.VAA05 , c.ABW02 , b.VAA10 , a.BEE01 , a.BEE03 ,b.ABBRP,B.ABBRW
  ,a.VAT33 ,BCQ04B='''' ,f.BAK05 , g.BBX05 , m.BBX05 MBBX05 , n.BCK03 , a.BCK01 , a.BBX01 
  , a.VAA01 , a.VAA07 , a.ACF01 , VAE44 = 0  ,a.VAT08,v.BCE03A,v.VAF01,v.CBM01,a.VAT09 ,isnull(b.VBU01,0) VBU01
    ,v.BCE01A,v.BCE01A as  BCE01C,a.FeeFlag   
FROM #tmp_VAT a  
  JOIN VAA1 b   with(nolock) ON a.VAA01 = b.VAA01
  JOIN VAC1 d  with(nolock)  ON a.VAA07 = d.VAC01
  JOIN #tmp_VAF1 v   ON a.VAF01 = v.VAF01 
  JOIN BBX1 g  with(nolock) ON v.BBX01 = g.BBX01
  LEFT JOIN BCK1 n  with(nolock) ON v.BCK01A = n.BCK01
  LEFT JOIN BBX1 m  with(nolock) ON a.BBX01 = m.BBX01 
  LEFT JOIN VAO1 e  with(nolock) ON a.VAF01 = e.VAF01 AND e.VAO11 = 8 
  LEFT JOIN BAK1 f  with(nolock) ON e.BAK01A = f.BAK01 
  LEFT JOIN ABW1 c  with(nolock) ON b.ABW01 = c.ABW01
  
where a.VAT04 <= 4 and a.VAT08 >= @aDate 
  and (v.BCK01B = @lBCK01 or exists(select vaf01 from #tmp_VAF1 v1 where v1.VAF01A = v.VAF01 and v1.BCK01B = @lBCK01))
  and exists(select * from VAI1 s where v.CBM01 = s.CBM01 and ((s.VAI18 = 4) or (@lMember >= 1 and s.VAI18 <= 3) ))
UNION ALL
SELECT a.VAT01 , a.VAT04 ,b.VAA03, b.VAA03 VAA04, b.VAA05 , c.ABW02 , b.VAA10 , a.BEE01 , a.BEE03 ,b.ABBRP,B.ABBRW
	, a.VAT33 ,BCQ04B='''' ,f.BAK05 , '''' , m.BBX05 MBBX05 , n.BCK03 , a.BCK01 , a.BBX01
	, a.VAA01 , a.VAA07 , a.ACF01 , VAE44 = 0  ,a.VAT08,g.BCE03,0,g.CBM01,a.VAT09,isnull(b.VBU01,0) VBU01
	  ,g.BCE01,g.BCE01 as  BCE01C,a.FeeFlag   
FROM #tmp_VAT a
	JOIN CBM1 g  with(nolock)  ON a.VAT01 = g.CBM01
	JOIN VAA1 b   with(nolock) ON a.VAA01 = b.VAA01
	LEFT JOIN BCK1 n  with(nolock)  ON g.BCK01B = n.BCK01
	LEFT JOIN BBX1 m  with(nolock)  ON a.BBX01 = m.BBX01
	LEFT JOIN VAO1 e  with(nolock)  ON a.VAF01 = e.VAF01 AND e.VAO11 = 8
	LEFT JOIN BAK1 f  with(nolock)  ON e.BAK01A = f.BAK01
	LEFT JOIN ABW1 c  with(nolock)  ON b.ABW01 = c.ABW01
 
WHERE a.VAT04 <= 4 AND a.VAT08 >= @aDate AND a.BCK01 = @lBCK01
	AND a.VCY01 > 0 AND a.VAA07 > 0

IF object_id(''tempdb..#tmp_VAT'') IS NOT NULL DROP TABLE #tmp_VAT
IF object_id(''tempdb..#tmp_VAF1'') IS NOT NULL DROP TABLE #tmp_VAF1
IF object_id(''tempdb..#tmp_VAF2'') IS NOT NULL DROP TABLE #tmp_VAF2', '2018-02-02 18:32:26', '(8088)王海涛', 0, 0)
else
print 'SYS_Scripts.id=1669 已经存在.'

GO


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