--删除旧记录
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)  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 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 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 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 
FROM #tmp_VAT a
  JOIN VAA1 b ON a.VAA01 = b.VAA01
  JOIN VAE1 d ON a.VAA07 = d.VAE01
  JOIN #tmp_VAF2 v ON a.VAF01 = v.VAF01 
  left JOIN BBX1 g ON v.BBX01 = g.BBX01
  LEFT JOIN BCK1 n ON v.BCK01A = n.BCK01
  LEFT JOIN BBX1 m ON a.BBX01 = m.BBX01 
  LEFT JOIN VAO2 e ON a.VAF01 = e.VAF01 AND e.VAO11 = 8 
  LEFT JOIN BAK1 f ON e.BAK01A = f.BAK01 
  LEFT JOIN ABW1 c ON b.ABW01 = c.ABW01
where a.VAT04 <= 4 and a.ACF01 = 2 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 
FROM #tmp_VAT a
  JOIN VAA1 b ON a.VAA01 = b.VAA01
  JOIN VAE1 d ON a.VAA07 = d.VAE01
  JOIN #tmp_VAF2 v ON a.VAF01 = v.VAF01 
  left JOIN BBX1 g ON v.BBX01 = g.BBX01
  LEFT JOIN BCK1 n ON v.BCK01A = n.BCK01
  LEFT JOIN BBX1 m ON a.BBX01 = m.BBX01 
  LEFT JOIN VAO2 e ON a.VAF01 = e.VAF01 AND e.VAO11 = 8 
  LEFT JOIN BAK1 f ON e.BAK01A = f.BAK01 
  LEFT JOIN ABW1 c ON b.ABW01 = c.ABW01
where a.VAT04 <= 4 and a.ACF01 = 2 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 v.CBM01 , -1 ,b.VAA03, b.VAA04, b.VAA05 , c.ABW02 , b.VAA10 , null , '''' ,b.ABBRP,B.ABBRW 
  ,v.VAF23 ,d.BCQ04B ,'''' ,(case when v.VAF35 = 1 then ''(紧急)'' else '''' end) + v.VAF22 , '''' MBBX05 , n.BCK03 , 0 , 0 
  , b.VAA01 , d.VAE01 , 2 , d.VAE44,v.VAF47,v.BCE03A,v.VAF01,v.CBM01,v.VAF47,isnull(b.VBU01,0) VBU01
FROM #tmp_VAF2 v 
  JOIN VAA1 b ON b.VAA01 = v.VAA01
  JOIN VAE1 d ON d.VAE01 = v.VAF06
  LEFT JOIN BCK1 n ON v.BCK01A = n.BCK01    
  LEFT JOIN ABW1 c ON b.ABW01 = c.ABW01
where @lBCK01 >= 1 and d.VAE44 >= 1 and d.VAE44<= 5 and v.BDA01 in (''S'',''T'',''A'')
  and v.BCK01B <> @lBCK01 and v.BCK01D = @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
FROM #tmp_VAT a
  JOIN VAA1 b ON a.VAA01 = b.VAA01
  JOIN VAC1 d ON a.VAA07 = d.VAC01
  JOIN #tmp_VAF1 v ON a.VAF01 = v.VAF01 
  JOIN BBX1 g ON v.BBX01 = g.BBX01
  LEFT JOIN BCK1 n ON v.BCK01A = n.BCK01
  LEFT JOIN BBX1 m ON a.BBX01 = m.BBX01 
  LEFT JOIN VAO1 e ON a.VAF01 = e.VAF01 AND e.VAO11 = 8 
  LEFT JOIN BAK1 f ON e.BAK01A = f.BAK01 
  LEFT JOIN ABW1 c ON b.ABW01 = c.ABW01
where a.VAT04 <= 4 and a.ACF01 = 1 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 v.CBM01 , -1 ,b.VAA03, b.VAA04, b.VAA05 , c.ABW02 , b.VAA10 , null , ''''  ,b.ABBRP,B.ABBRW
  ,v.VAF23 ,'''' ,'''' , v.VAF22 , '''' MBBX05 , n.BCK03 , 0 , 0 
  , v.VAA01 , v.VAF06 , 1 , 2,v.VAF47,v.BCE03A,v.VAF01,v.CBM01,v.vAF47,isnull(b.VBU01,0) VBU01
FROM #tmp_VAF1 v 
  JOIN VAA1 b ON b.VAA01 = v.VAA01
  JOIN VAC1 d ON d.VAC01 = v.VAF06
  LEFT JOIN BCK1 n ON v.BCK01A = n.BCK01    
  LEFT JOIN ABW1 c ON b.ABW01 = c.ABW01
where  @lBCK01 >= 1 and v.BCK01B <> @lBCK01 and v.BCK01D = @lBCK01 and v.BDA01 in (''S'',''T'',''A'')
  and exists(select * from VAI1 s where v.CBM01 = s.CBM01 and s.VAI18 >= 3 and s.VAI18 <= 4)
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
FROM #tmp_VAT a
	JOIN CBM1 g ON a.VAT01 = g.CBM01
	JOIN VAA1 b ON a.VAA01 = b.VAA01
	LEFT JOIN BCK1 n ON g.BCK01B = n.BCK01
	LEFT JOIN BBX1 m ON a.BBX01 = m.BBX01
	LEFT JOIN VAO1 e ON a.VAF01 = e.VAF01 AND e.VAO11 = 8
	LEFT JOIN BAK1 f ON e.BAK01A = f.BAK01
	LEFT JOIN ABW1 c 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', '2015-01-26 16:54:10', '(8088)王海涛', 0)
else
print 'SYS_Scripts.id=1669 已经存在.'

GO


--删除旧记录
--delete from Report_Objects where Code='HO.ops.000003' --删除脚本内容


IF not EXISTS(SELECT * FROM Report_Objects WHERE code='HO.ops.000003')
begin --插入
  Declare @Report_Id_893 int
  Exec Core_NewId 'Report_Objects', 'id', @Report_Id_893 out
  Declare @Report_Id_893_CateID int
  Set @Report_Id_893_CateID = (select id from Report_Categories where code='HIS.18')
  insert into Report_Objects(ID,Code,Name,Description,Password,CategoryID,xType,Classification,ProductID,ProgramID,ElementID,CreateDate,UpdateDate,ISSUANCEDATE,Config,SetupPath,IsMenu,Enabled,GroupName,Authorized,RefreshInterval)  Values(@Report_Id_893, 'HO.ops.000003', '手术工作站过滤查询', Null, Null, @Report_Id_893_CateID, 1, 4, 100, 0, Null, '2013-07-19 11:12:21', '2013-07-19 11:12:21', Null, '<?xml version="1.0"?>
<Root Ver="1.0" YIBAO="" NoDialog="" Preview="1111111111110001"><Config TimeOut="15" BrowMode="0"><DBLink Mode="1"></DBLink><Chart HasChart="0" ViewFirst="0"/><BrowUrl User="" Pass=""></BrowUrl><CTI Enabled="0" Tel1="" Tel2="" Tel3=""/><Print Edit="0" ExecSql="" Select="0" SelectDefault="1" Mode="0"/><OtherReport><File></File><Fun></Fun></OtherReport><MD5>F7DB1C50C252EE2A8DAFE09E6D2BC33B</MD5></Config><DTS><Script>declare @lBCK01 int,
 @lMember tinyint,
 @aDate datetime ,
 @VAA01 int 
set @lBCK01 = :BCK01
set @lMember = :Member
set @aDate = :dt1
set @VAA01=:VAA01</Script><Ie Name="新建数据源" Fixed="0" Visible="1" OrderBy="" OrderType="0" Bands="0" UseBCD="0"><Descrption></Descrption><Sql>declare @lBCK01 int,
 @lMember tinyint,
 @aDate datetime ,
 @VAA01 int 
set @lBCK01 = :BCK01
set @lMember = :Member
set @aDate = :dt1
set @VAA01=:VAA01
--说明：上面两个参数及名称是固定的不能修改，其他条件可以随意加，包括下面的字段也不能改变只能增加不能减少 

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 into #tmp_VAT
FROM VAT1 a
where a.VAT04 &lt;= 4  and a.VAT08 &gt;= @aDate and (a.VAA01=@VAA01 or @VAA01=0)

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 into #tmp_VAF1
from VAF1 v
where v.VAF47 &gt;= @aDate and v.VAF10 &gt;=8 and v.VAF10 &lt;= 9 and (v.bck01b=@lBCK01 or v.bck01d=@lBCK01)  and (v.VAA01=@VAA01 or @VAA01=0)

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 into #tmp_VAF2
from VAF2 v
where v.VAF47 &gt;= @aDate and v.VAF10 &gt;=8 and v.VAF10 &lt;= 9 and (v.bck01b=@lBCK01 or v.bck01d=@lBCK01)  and (v.VAA01=@VAA01 or @VAA01=0)

  
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 
FROM #tmp_VAT a
  JOIN VAA1 b ON a.VAA01 = b.VAA01
  JOIN VAE1 d ON a.VAA07 = d.VAE01
  JOIN #tmp_VAF2 v ON a.VAF01 = v.VAF01 
  left JOIN BBX1 g ON v.BBX01 = g.BBX01
  LEFT JOIN BCK1 n ON v.BCK01A = n.BCK01
  LEFT JOIN BBX1 m ON a.BBX01 = m.BBX01 
  LEFT JOIN VAO2 e ON a.VAF01 = e.VAF01 AND e.VAO11 = 8 
  LEFT JOIN BAK1 f ON e.BAK01A = f.BAK01 
  LEFT JOIN ABW1 c ON b.ABW01 = c.ABW01
where a.VAT04 &lt;= 4 and a.ACF01 = 2 and d.VAE44 &gt;= 1 and d.VAE44&lt;= 5 and a.VAT08 &gt;= @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 
FROM #tmp_VAT a
  JOIN VAA1 b ON a.VAA01 = b.VAA01
  JOIN VAE1 d ON a.VAA07 = d.VAE01
  JOIN #tmp_VAF2 v ON a.VAF01 = v.VAF01 
  left JOIN BBX1 g ON v.BBX01 = g.BBX01
  LEFT JOIN BCK1 n ON v.BCK01A = n.BCK01
  LEFT JOIN BBX1 m ON a.BBX01 = m.BBX01 
  LEFT JOIN VAO2 e ON a.VAF01 = e.VAF01 AND e.VAO11 = 8 
  LEFT JOIN BAK1 f ON e.BAK01A = f.BAK01 
  LEFT JOIN ABW1 c ON b.ABW01 = c.ABW01
where a.VAT04 &lt;= 4 and a.ACF01 = 2 and d.VAE44 &gt;= 1 and d.VAE44&lt;= 5 and a.VAT08 &gt;= @aDate
  and v.BCK01B &lt;&gt; @lBCK01 and exists(select vaf01 from #tmp_VAF2 v1 where v1.VAF01A = v.VAF01 and v1.BCK01B = @lBCK01)
union all
SELECT v.CBM01 , -1 ,b.VAA03, b.VAA04, b.VAA05 , c.ABW02 , b.VAA10 , null , '''' ,b.ABBRP,B.ABBRW 
  ,v.VAF23 ,d.BCQ04B ,'''' ,(case when v.VAF35 = 1 then ''(紧急)'' else '''' end) + v.VAF22 , '''' MBBX05 , n.BCK03 , 0 , 0 
  , b.VAA01 , d.VAE01 , 2 , d.VAE44,v.VAF47,v.BCE03A,v.VAF01,v.CBM01,v.VAF47,isnull(b.VBU01,0) VBU01
FROM #tmp_VAF2 v 
  JOIN VAA1 b ON b.VAA01 = v.VAA01
  JOIN VAE1 d ON d.VAE01 = v.VAF06
  LEFT JOIN BCK1 n ON v.BCK01A = n.BCK01    
  LEFT JOIN ABW1 c ON b.ABW01 = c.ABW01
where @lBCK01 &gt;= 1 and d.VAE44 &gt;= 1 and d.VAE44&lt;= 5 and v.BDA01 in (''S'',''T'',''A'')
  and v.BCK01B &lt;&gt; @lBCK01 and v.BCK01D = @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
FROM #tmp_VAT a
  JOIN VAA1 b ON a.VAA01 = b.VAA01
  JOIN VAC1 d ON a.VAA07 = d.VAC01
  JOIN #tmp_VAF1 v ON a.VAF01 = v.VAF01 
  JOIN BBX1 g ON v.BBX01 = g.BBX01
  LEFT JOIN BCK1 n ON v.BCK01A = n.BCK01
  LEFT JOIN BBX1 m ON a.BBX01 = m.BBX01 
  LEFT JOIN VAO1 e ON a.VAF01 = e.VAF01 AND e.VAO11 = 8 
  LEFT JOIN BAK1 f ON e.BAK01A = f.BAK01 
  LEFT JOIN ABW1 c ON b.ABW01 = c.ABW01
where a.VAT04 &lt;= 4 and a.ACF01 = 1 and a.VAT08 &gt;= @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 &gt;= 1 and s.VAI18 &lt;= 3) ))
Union All
SELECT v.CBM01 , -1 ,b.VAA03, b.VAA04, b.VAA05 , c.ABW02 , b.VAA10 , null , ''''  ,b.ABBRP,B.ABBRW
  ,v.VAF23 ,'''' ,'''' , v.VAF22 , '''' MBBX05 , n.BCK03 , 0 , 0 
  , v.VAA01 , v.VAF06 , 1 , 2,v.VAF47,v.BCE03A,v.VAF01,v.CBM01,v.vAF47,isnull(b.VBU01,0) VBU01
FROM #tmp_VAF1 v 
  JOIN VAA1 b ON b.VAA01 = v.VAA01
  JOIN VAC1 d ON d.VAC01 = v.VAF06
  LEFT JOIN BCK1 n ON v.BCK01A = n.BCK01    
  LEFT JOIN ABW1 c ON b.ABW01 = c.ABW01
where  @lBCK01 &gt;= 1 and v.BCK01B &lt;&gt; @lBCK01 and v.BCK01D = @lBCK01 and v.BDA01 in (''S'',''T'',''A'')
  and exists(select * from VAI1 s where v.CBM01 = s.CBM01 and s.VAI18 &gt;= 3 and s.VAI18 &lt;= 4)
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
FROM #tmp_VAT a
	JOIN CBM1 g ON a.VAT01 = g.CBM01
	JOIN VAA1 b ON a.VAA01 = b.VAA01
	LEFT JOIN BCK1 n ON g.BCK01B = n.BCK01
	LEFT JOIN BBX1 m ON a.BBX01 = m.BBX01
	LEFT JOIN VAO1 e ON a.VAF01 = e.VAF01 AND e.VAO11 = 8
	LEFT JOIN BAK1 f ON e.BAK01A = f.BAK01
	LEFT JOIN ABW1 c ON b.ABW01 = c.ABW01
WHERE a.VAT04 &lt;= 4 AND a.VAT08 &gt;= @aDate AND a.BCK01 = @lBCK01
	AND a.VCY01 &gt; 0 AND a.VAA07 &gt; 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</Sql><Links/></Ie></DTS><Params><Ie Name="dt1" Alias="起始时间" GroupName="" DefaultValue="1" Style="4" NotNull="1" Privilege="0"><Config KeyField="" DispField="" Flag="0"></Config></Ie><Ie Name="BCK01" Alias="执行科室" GroupName="" DefaultValue="0" Style="1" NotNull="1" Privilege="0"><Config KeyField="BCK01" DispField="BCK03" Flag="1">&lt;?xml version="1.0"?&gt;
&lt;Configs Ver="1"&gt;&lt;Win SZ="1"/&gt;&lt;Grid UseW="1"&gt;&lt;Sql&gt;select * from BCK1 where exists(select * from BAZ1 where BCK01=BCK1.BCK01 and BAU01=''03'')&lt;/Sql&gt;&lt;HD&gt;&lt;Ie FD="BCK03" WD="100"/&gt;&lt;/HD&gt;&lt;QF&gt;&lt;Ie FD="BCK01" WE="7"/&gt;&lt;Ie FD="BCK02" WE="7"/&gt;&lt;Ie FD="BCK03" WE="7"/&gt;&lt;Ie FD="ABBRP" WE="7"/&gt;&lt;/QF&gt;&lt;/Grid&gt;&lt;/Configs&gt;</Config></Ie><Ie Name="Member" Alias="门诊显示未收费手术" GroupName="" DefaultValue="0" Style="3" NotNull="0" Privilege="0"><Config KeyField="" DispField="" Flag="0">0=否
1=是</Config></Ie><Ie Name="VAA01" Alias="病人" GroupName="" DefaultValue="0" Style="1" NotNull="0" Privilege="0"><Config KeyField="VAA01" DispField="VAA05" Flag="1">&lt;?xml version="1.0"?&gt;
&lt;Configs Ver="1"&gt;&lt;Win SZ="1"/&gt;&lt;Grid Head="1" UseW="1"&gt;&lt;Sql&gt;select VAA01,VAA03,VAA04,VAA05,ABBRP from VAA1 a where exists(select * from VAT1 where VAA01=a.VAA01)&lt;/Sql&gt;&lt;HD&gt;&lt;Ie FD="VAA03" CA="门诊号" WD="100"/&gt;&lt;Ie FD="VAA04" CA="住院号" WD="100"/&gt;&lt;Ie FD="VAA05" CA="姓名" WD="100"/&gt;&lt;Ie FD="abbrp" CA="拼音码" WD="100"/&gt;&lt;/HD&gt;&lt;QF&gt;&lt;Ie FD="VAA03" WE="7"/&gt;&lt;Ie FD="VAA04" WE="7"/&gt;&lt;Ie FD="VAA05" WE="7"/&gt;&lt;Ie FD="ABBRP" WE="7"/&gt;&lt;/QF&gt;&lt;/Grid&gt;&lt;/Configs&gt;</Config></Ie></Params></Root>
', Null, 0, 1, Null, 0, Null)
end
else --更新
begin
  update Report_Objects set Config='<?xml version="1.0"?>
<Root Ver="1.0" YIBAO="" NoDialog="" Preview="1111111111110001"><Config TimeOut="15" BrowMode="0"><DBLink Mode="1"></DBLink><Chart HasChart="0" ViewFirst="0"/><BrowUrl User="" Pass=""></BrowUrl><CTI Enabled="0" Tel1="" Tel2="" Tel3=""/><Print Edit="0" ExecSql="" Select="0" SelectDefault="1" Mode="0"/><OtherReport><File></File><Fun></Fun></OtherReport><MD5>F7DB1C50C252EE2A8DAFE09E6D2BC33B</MD5></Config><DTS><Script>declare @lBCK01 int,
 @lMember tinyint,
 @aDate datetime ,
 @VAA01 int 
set @lBCK01 = :BCK01
set @lMember = :Member
set @aDate = :dt1
set @VAA01=:VAA01</Script><Ie Name="新建数据源" Fixed="0" Visible="1" OrderBy="" OrderType="0" Bands="0" UseBCD="0"><Descrption></Descrption><Sql>declare @lBCK01 int,
 @lMember tinyint,
 @aDate datetime ,
 @VAA01 int 
set @lBCK01 = :BCK01
set @lMember = :Member
set @aDate = :dt1
set @VAA01=:VAA01
--说明：上面两个参数及名称是固定的不能修改，其他条件可以随意加，包括下面的字段也不能改变只能增加不能减少 

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 into #tmp_VAT
FROM VAT1 a
where a.VAT04 &lt;= 4  and a.VAT08 &gt;= @aDate and (a.VAA01=@VAA01 or @VAA01=0)

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 into #tmp_VAF1
from VAF1 v
where v.VAF47 &gt;= @aDate and v.VAF10 &gt;=8 and v.VAF10 &lt;= 9 and (v.bck01b=@lBCK01 or v.bck01d=@lBCK01)  and (v.VAA01=@VAA01 or @VAA01=0)

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 into #tmp_VAF2
from VAF2 v
where v.VAF47 &gt;= @aDate and v.VAF10 &gt;=8 and v.VAF10 &lt;= 9 and (v.bck01b=@lBCK01 or v.bck01d=@lBCK01)  and (v.VAA01=@VAA01 or @VAA01=0)

  
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 
FROM #tmp_VAT a
  JOIN VAA1 b ON a.VAA01 = b.VAA01
  JOIN VAE1 d ON a.VAA07 = d.VAE01
  JOIN #tmp_VAF2 v ON a.VAF01 = v.VAF01 
  left JOIN BBX1 g ON v.BBX01 = g.BBX01
  LEFT JOIN BCK1 n ON v.BCK01A = n.BCK01
  LEFT JOIN BBX1 m ON a.BBX01 = m.BBX01 
  LEFT JOIN VAO2 e ON a.VAF01 = e.VAF01 AND e.VAO11 = 8 
  LEFT JOIN BAK1 f ON e.BAK01A = f.BAK01 
  LEFT JOIN ABW1 c ON b.ABW01 = c.ABW01
where a.VAT04 &lt;= 4 and a.ACF01 = 2 and d.VAE44 &gt;= 1 and d.VAE44&lt;= 5 and a.VAT08 &gt;= @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 
FROM #tmp_VAT a
  JOIN VAA1 b ON a.VAA01 = b.VAA01
  JOIN VAE1 d ON a.VAA07 = d.VAE01
  JOIN #tmp_VAF2 v ON a.VAF01 = v.VAF01 
  left JOIN BBX1 g ON v.BBX01 = g.BBX01
  LEFT JOIN BCK1 n ON v.BCK01A = n.BCK01
  LEFT JOIN BBX1 m ON a.BBX01 = m.BBX01 
  LEFT JOIN VAO2 e ON a.VAF01 = e.VAF01 AND e.VAO11 = 8 
  LEFT JOIN BAK1 f ON e.BAK01A = f.BAK01 
  LEFT JOIN ABW1 c ON b.ABW01 = c.ABW01
where a.VAT04 &lt;= 4 and a.ACF01 = 2 and d.VAE44 &gt;= 1 and d.VAE44&lt;= 5 and a.VAT08 &gt;= @aDate
  and v.BCK01B &lt;&gt; @lBCK01 and exists(select vaf01 from #tmp_VAF2 v1 where v1.VAF01A = v.VAF01 and v1.BCK01B = @lBCK01)
union all
SELECT v.CBM01 , -1 ,b.VAA03, b.VAA04, b.VAA05 , c.ABW02 , b.VAA10 , null , '''' ,b.ABBRP,B.ABBRW 
  ,v.VAF23 ,d.BCQ04B ,'''' ,(case when v.VAF35 = 1 then ''(紧急)'' else '''' end) + v.VAF22 , '''' MBBX05 , n.BCK03 , 0 , 0 
  , b.VAA01 , d.VAE01 , 2 , d.VAE44,v.VAF47,v.BCE03A,v.VAF01,v.CBM01,v.VAF47,isnull(b.VBU01,0) VBU01
FROM #tmp_VAF2 v 
  JOIN VAA1 b ON b.VAA01 = v.VAA01
  JOIN VAE1 d ON d.VAE01 = v.VAF06
  LEFT JOIN BCK1 n ON v.BCK01A = n.BCK01    
  LEFT JOIN ABW1 c ON b.ABW01 = c.ABW01
where @lBCK01 &gt;= 1 and d.VAE44 &gt;= 1 and d.VAE44&lt;= 5 and v.BDA01 in (''S'',''T'',''A'')
  and v.BCK01B &lt;&gt; @lBCK01 and v.BCK01D = @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
FROM #tmp_VAT a
  JOIN VAA1 b ON a.VAA01 = b.VAA01
  JOIN VAC1 d ON a.VAA07 = d.VAC01
  JOIN #tmp_VAF1 v ON a.VAF01 = v.VAF01 
  JOIN BBX1 g ON v.BBX01 = g.BBX01
  LEFT JOIN BCK1 n ON v.BCK01A = n.BCK01
  LEFT JOIN BBX1 m ON a.BBX01 = m.BBX01 
  LEFT JOIN VAO1 e ON a.VAF01 = e.VAF01 AND e.VAO11 = 8 
  LEFT JOIN BAK1 f ON e.BAK01A = f.BAK01 
  LEFT JOIN ABW1 c ON b.ABW01 = c.ABW01
where a.VAT04 &lt;= 4 and a.ACF01 = 1 and a.VAT08 &gt;= @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 &gt;= 1 and s.VAI18 &lt;= 3) ))
Union All
SELECT v.CBM01 , -1 ,b.VAA03, b.VAA04, b.VAA05 , c.ABW02 , b.VAA10 , null , ''''  ,b.ABBRP,B.ABBRW
  ,v.VAF23 ,'''' ,'''' , v.VAF22 , '''' MBBX05 , n.BCK03 , 0 , 0 
  , v.VAA01 , v.VAF06 , 1 , 2,v.VAF47,v.BCE03A,v.VAF01,v.CBM01,v.vAF47,isnull(b.VBU01,0) VBU01
FROM #tmp_VAF1 v 
  JOIN VAA1 b ON b.VAA01 = v.VAA01
  JOIN VAC1 d ON d.VAC01 = v.VAF06
  LEFT JOIN BCK1 n ON v.BCK01A = n.BCK01    
  LEFT JOIN ABW1 c ON b.ABW01 = c.ABW01
where  @lBCK01 &gt;= 1 and v.BCK01B &lt;&gt; @lBCK01 and v.BCK01D = @lBCK01 and v.BDA01 in (''S'',''T'',''A'')
  and exists(select * from VAI1 s where v.CBM01 = s.CBM01 and s.VAI18 &gt;= 3 and s.VAI18 &lt;= 4)
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
FROM #tmp_VAT a
	JOIN CBM1 g ON a.VAT01 = g.CBM01
	JOIN VAA1 b ON a.VAA01 = b.VAA01
	LEFT JOIN BCK1 n ON g.BCK01B = n.BCK01
	LEFT JOIN BBX1 m ON a.BBX01 = m.BBX01
	LEFT JOIN VAO1 e ON a.VAF01 = e.VAF01 AND e.VAO11 = 8
	LEFT JOIN BAK1 f ON e.BAK01A = f.BAK01
	LEFT JOIN ABW1 c ON b.ABW01 = c.ABW01
WHERE a.VAT04 &lt;= 4 AND a.VAT08 &gt;= @aDate AND a.BCK01 = @lBCK01
	AND a.VCY01 &gt; 0 AND a.VAA07 &gt; 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</Sql><Links/></Ie></DTS><Params><Ie Name="dt1" Alias="起始时间" GroupName="" DefaultValue="1" Style="4" NotNull="1" Privilege="0"><Config KeyField="" DispField="" Flag="0"></Config></Ie><Ie Name="BCK01" Alias="执行科室" GroupName="" DefaultValue="0" Style="1" NotNull="1" Privilege="0"><Config KeyField="BCK01" DispField="BCK03" Flag="1">&lt;?xml version="1.0"?&gt;
&lt;Configs Ver="1"&gt;&lt;Win SZ="1"/&gt;&lt;Grid UseW="1"&gt;&lt;Sql&gt;select * from BCK1 where exists(select * from BAZ1 where BCK01=BCK1.BCK01 and BAU01=''03'')&lt;/Sql&gt;&lt;HD&gt;&lt;Ie FD="BCK03" WD="100"/&gt;&lt;/HD&gt;&lt;QF&gt;&lt;Ie FD="BCK01" WE="7"/&gt;&lt;Ie FD="BCK02" WE="7"/&gt;&lt;Ie FD="BCK03" WE="7"/&gt;&lt;Ie FD="ABBRP" WE="7"/&gt;&lt;/QF&gt;&lt;/Grid&gt;&lt;/Configs&gt;</Config></Ie><Ie Name="Member" Alias="门诊显示未收费手术" GroupName="" DefaultValue="0" Style="3" NotNull="0" Privilege="0"><Config KeyField="" DispField="" Flag="0">0=否
1=是</Config></Ie><Ie Name="VAA01" Alias="病人" GroupName="" DefaultValue="0" Style="1" NotNull="0" Privilege="0"><Config KeyField="VAA01" DispField="VAA05" Flag="1">&lt;?xml version="1.0"?&gt;
&lt;Configs Ver="1"&gt;&lt;Win SZ="1"/&gt;&lt;Grid Head="1" UseW="1"&gt;&lt;Sql&gt;select VAA01,VAA03,VAA04,VAA05,ABBRP from VAA1 a where exists(select * from VAT1 where VAA01=a.VAA01)&lt;/Sql&gt;&lt;HD&gt;&lt;Ie FD="VAA03" CA="门诊号" WD="100"/&gt;&lt;Ie FD="VAA04" CA="住院号" WD="100"/&gt;&lt;Ie FD="VAA05" CA="姓名" WD="100"/&gt;&lt;Ie FD="abbrp" CA="拼音码" WD="100"/&gt;&lt;/HD&gt;&lt;QF&gt;&lt;Ie FD="VAA03" WE="7"/&gt;&lt;Ie FD="VAA04" WE="7"/&gt;&lt;Ie FD="VAA05" WE="7"/&gt;&lt;Ie FD="ABBRP" WE="7"/&gt;&lt;/QF&gt;&lt;/Grid&gt;&lt;/Configs&gt;</Config></Ie></Params></Root>
' where Code='HO.ops.000003'
end

GO