--删除旧记录
--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, '
D46E0CB45411ABBDFE030855B6FC3FCFdeclare @BCK01 int,
@lMember tinyint,
@aDate datetime
set @BCK01 = :BCK01
set @lMember = :Member
set @aDate = :dt1
--说明：上面两个参数及名称是固定的不能修改，其他条件可以随意加，包括下面的字段也不能改变只能增加不能减少
SELECT a.VAT01 , a.VAT04 ,b.VAA03, b.VAA04, b.VAA05 , c.ABW02 , b.VAA10 , a.BEE01
, a.BEE03 ,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 VAT1 a
JOIN VAA1 b ON a.VAA01 = b.VAA01
JOIN VAE1 d ON a.VAA07 = d.VAE01
JOIN V_VAF_FULL v ON a.VAF01 = v.VAF01 and v.VAF10 >=8 and v.VAF10 <= 9
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 V_VAO_FULL 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.ACF01 = 2 and d.VAE44 >= 1 and d.VAE44<= 5 and a.VAT08 >= @aDate
and (v.BCK01B = @BCK01 or exists(select vaf01 from V_VAF_FULL v1 where v1.VAF01A = v.VAF01 and v1.BCK01B = @BCK01))
Union All
SELECT a.VAT01 , a.VAT04 ,b.VAA03, b.VAA03 VAA04, b.VAA05 , c.ABW02 , b.VAA10 , a.BEE01
, a.BEE03 ,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 VAT1 a
JOIN VAA1 b ON a.VAA01 = b.VAA01
JOIN VAC1 d ON a.VAA07 = d.VAC01
JOIN V_VAF_FULL v ON a.VAF01 = v.VAF01 and v.VAF10 >=8 and v.VAF10 <= 9
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 V_VAO_FULL 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.ACF01 = 1 and a.VAT08 >= @aDate and (v.BCK01B = @BCK01 or exists(select vaf01 from V_VAF_FULL v1 where v1.VAF01A = v.VAF01 and v1.BCK01B = @BCK01))
and exists(select * from V_VAI_FULL 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 , 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 VAT1 a
JOIN V_CBM_FULL 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 V_VAO_FULL 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.BCK01 = @BCK01 and a.VAT08 >= @aDate AND a.VCY01 > 0 AND a.VAA07 > 0<?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid UseW="1"><Sql>select * from BCK1 where exists(select * from BAZ1 where BCK01=BCK1.BCK01 and BAU01=''03'')</Sql><HD><Ie FD="BCK03" WD="100"/></HD><QF><Ie FD="BCK01" WE="7"/><Ie FD="BCK02" WE="7"/><Ie FD="BCK03" WE="7"/><Ie FD="ABBRP" WE="7"/></QF></Grid></Configs>0=否
1=是
', Null, 0, 1, Null, 0, Null)
end
else --更新
begin
update Report_Objects set Config='
D46E0CB45411ABBDFE030855B6FC3FCFdeclare @BCK01 int,
@lMember tinyint,
@aDate datetime
set @BCK01 = :BCK01
set @lMember = :Member
set @aDate = :dt1
--说明：上面两个参数及名称是固定的不能修改，其他条件可以随意加，包括下面的字段也不能改变只能增加不能减少
SELECT a.VAT01 , a.VAT04 ,b.VAA03, b.VAA04, b.VAA05 , c.ABW02 , b.VAA10 , a.BEE01
, a.BEE03 ,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 VAT1 a
JOIN VAA1 b ON a.VAA01 = b.VAA01
JOIN VAE1 d ON a.VAA07 = d.VAE01
JOIN V_VAF_FULL v ON a.VAF01 = v.VAF01 and v.VAF10 >=8 and v.VAF10 <= 9
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 V_VAO_FULL 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.ACF01 = 2 and d.VAE44 >= 1 and d.VAE44<= 5 and a.VAT08 >= @aDate
and (v.BCK01B = @BCK01 or exists(select vaf01 from V_VAF_FULL v1 where v1.VAF01A = v.VAF01 and v1.BCK01B = @BCK01))
Union All
SELECT a.VAT01 , a.VAT04 ,b.VAA03, b.VAA03 VAA04, b.VAA05 , c.ABW02 , b.VAA10 , a.BEE01
, a.BEE03 ,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 VAT1 a
JOIN VAA1 b ON a.VAA01 = b.VAA01
JOIN VAC1 d ON a.VAA07 = d.VAC01
JOIN V_VAF_FULL v ON a.VAF01 = v.VAF01 and v.VAF10 >=8 and v.VAF10 <= 9
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 V_VAO_FULL 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.ACF01 = 1 and a.VAT08 >= @aDate and (v.BCK01B = @BCK01 or exists(select vaf01 from V_VAF_FULL v1 where v1.VAF01A = v.VAF01 and v1.BCK01B = @BCK01))
and exists(select * from V_VAI_FULL 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 , 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 VAT1 a
JOIN V_CBM_FULL 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 V_VAO_FULL 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.BCK01 = @BCK01 and a.VAT08 >= @aDate AND a.VCY01 > 0 AND a.VAA07 > 0<?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid UseW="1"><Sql>select * from BCK1 where exists(select * from BAZ1 where BCK01=BCK1.BCK01 and BAU01=''03'')</Sql><HD><Ie FD="BCK03" WD="100"/></HD><QF><Ie FD="BCK01" WE="7"/><Ie FD="BCK02" WE="7"/><Ie FD="BCK03" WE="7"/><Ie FD="ABBRP" WE="7"/></QF></Grid></Configs>0=否
1=是
' where Code='HO.ops.000003'
end
GO