--删除旧记录
--delete from Report_Objects where Code='LIS.U.000005' --删除脚本内容
IF not EXISTS(SELECT * FROM Report_Objects WHERE code='LIS.U.000005')
begin --插入
Declare @Report_Id_830 int
Exec Core_NewId 'Report_Objects', 'id', @Report_Id_830 out
Declare @Report_Id_830_CateID int
Set @Report_Id_830_CateID = (select id from Report_Categories where code='LIS.5')
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_830, 'LIS.U.000005', '标本登记查询病人信息', Null, Null, @Report_Id_830_CateID, 1, 3, 200, 0, Null, '2013-03-20 11:42:31', '2013-03-22 10:49:53', Null, '
CE74F396F42C6F481554FB419AC5186CDECLARE @BeginDate varchar(30)
, @EndDate varchar(30)
, @ACF01 int/*医疗类别*/
, @BCE01C int/*核收人*/
, @VAA02 varchar(20)/*病历号*/
, @LAA08 varchar(20)/*条码号*/
, @VAA05 varchar(64)/*病人*/
, @BCK01A int/*申请病区ID*/
, @BCK01B int/*申请科室ID*/
, @BCK01C int/*核收科室ID*/
, @LAA32 int/*状态*/
, @PRNCP int/*打印*/
, @VCB04 varchar(20) /*会员卡*/
, @LAA42 int/*作废*/
select @BeginDate=:BeginDate
, @EndDate=:EndDate
, @ACF01 = :ACF01
, @BCE01C=:BCE01C
, @VAA02=:VAA02
, @LAA08=:LAA08
, @VAA05=:VAA05
, @BCK01A=:BCK01A
, @BCK01B=:BCK01B
, @BCK01C=:BCK01C
, @LAA32=:LAA32
, @PRNCP=:PRNCP
, @VCB04=:VCB04
, @LAA42=:LAA42
declare @sql varchar(8000)
--select @BeginDate = convert(varchar(10), @BeginDate, 120), @EndDate = convert(varchar(10), @EndDate, 120) + '' 23:59:59.999''
set @sql = ''
SELECT (case when ISNULL(PRNCP, 0) > 0 then 0 else 1 end) AS Checked, A.LAA01, A.LAA02, A.VAA01, A.VAA07, A.VAP01, A.ACF01, A.VAA02, A.LAA08, A.LAA09
, (case when A.VAP01 > 0 then (select P1.VAP05 from VAP1 P1 where P1.VAP01 = A.VAP01) else A.VAA05 end) VAA05
, A.ABW01, A.LAA12, A.LAA13
, A.AAU01, A.BES02, A.LAA16, A.BEL01, A.LAA18, A.LAA19, A.LAA20, A.ABB02, A.BCK01A, A.BCK01B, A.BCQ04, A.BCE03A, A.LAA26
, A.BCE03B, A.LAA28, A.BCK01C, A.BCE01C, A.BCE03C, A.LAA31, A.LAA32, A.LAA33, A.PRNCP, A.LAA35, A.VAF01, A.LAA37, A.LAA38
, BCE01D, BCE03D, LAA48, BCE01E, BCE03E, LAA51, isnull(LAA52, 0) LAA52, LAA53, LAA54
, F.ACF03, W.ABW02, U.AAU02, K1.BCK03 AS BCK03B, A.BAQ03, A.LAA45, L.BEL12, L.BEL08, A1.VBU01, (case when A.LAA08 = A.LAA09 then 0 else 1 end) as IsWG
, '''''''' as Field1, '''''''' as Field2, '''''''' as Field3, '''''''' as Field4, '''''''' as Field5
, '''''''' as Field6, '''''''' as Field7, '''''''' as Field8, '''''''' as Field9, '''''''' as Field10
FROM LAA1 A LEFT JOIN ACF1 F ON F.ACF01 = A.ACF01
LEFT JOIN ABW1 W ON W.ABW01 = A.ABW01
LEFT JOIN AAU1 U ON U.AAU01 = A.AAU01
LEFT JOIN BCK1 K1 ON K1.BCK01 = A.BCK01B
LEFT JOIN VAA1 A1 ON A1.VAA01 = A.VAA01
LEFT JOIN BEL1 L ON L.BEL01 = A.BEL01
WHERE A.LAA28 between '''''' + @BeginDate + '''''' and '''''' + @EndDate + ''''''''
if @BCE01C>0 set @sql = @sql+'' and A.BCE01C=''''''+convert(varchar, @BCE01C)+''''''''
if @VAA02>'''' set @sql = @sql+'' and (A.VAA02 = '''''' + @VAA02 + '''''' or A1.VAA03=''''''+@VAA02+'''''' or A1.VAA04=''''''+@VAA02+''''''
or ((select top 1 F1.SCF04 from SCF1 F1 join SCA1 C1 on C1.SCA01 = F1.SCA01 and C1.VAA01 = A.VAA01) = ''''''+@VAA02+'''''') )''
if @LAA08>'''' set @sql = @sql+'' and A.LAA08=''''''+@LAA08+''''''''
if @VAA05>'''' set @sql = @sql+'' and A.VAA05=''''''+@VAA05+''''''''
if @VCB04>'''' set @sql = @sql+'' and A1.VAA02=''''''+@VCB04+''''''''
if @BCK01A>0 set @sql = @sql+'' and A.BCK01A='' + convert(varchar, @BCK01A)
if @BCK01B>0 set @sql = @sql+'' and A.BCK01B='' + convert(varchar, @BCK01B)
if @BCK01C>0 set @sql = @sql+'' and A.BCK01C='' + convert(varchar, @BCK01C)
if @LAA32>-1 set @sql = @sql+'' and A.LAA32='' + convert(varchar, @LAA32)
if @PRNCP=0 set @sql = @sql+'' and A.PRNCP=0''
else if @PRNCP>=1 set @sql = @sql+'' and A.PRNCP>=1''
if @ACF01 > 0 set @sql = @sql + '' and A.ACF01 = '' + CONVERT(VARCHAR, @ACF01)
if @LAA42 = 0 set @sql = @sql + '' and A.LAA42 = 0''
else if @LAA42 = 1 set @sql = @sql + '' and A.LAA42 >= 1''
exec (@sql)0=全部
1=门诊
2=住院
4=体检<?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid Head="1" AWS="1" QDB="1"><Sql>SELECT DISTINCT a.BCK01,a.BCK02,a.BCK03,ABBRP,ABBRW
FROM BCK1 a
JOIN BAZ1 b ON a.BCK01=b.BCK01
and b.BAU01=''02''</Sql><HD><Ie FD="BCK02" CA="编码" WD="100"/><Ie FD="BCK03" CA="名称" WD="100"/></HD><QF><Ie FD="BCK02" WE="7"/><Ie FD="BCK03" WE="7"/><Ie FD="ABBRP" WE="7"/><Ie FD="ABBRW" WE="7"/></QF></Grid></Configs><?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid Head="1" AWS="1" QDB="1"><Sql>select BCK01, BCK02, BCK03, ABBRP, ABBRW from BCK1</Sql><HD><Ie FD="BCK02" CA="编码" WD="100"/><Ie FD="BCK03" CA="名称" WD="100"/></HD><QF><Ie FD="BCK02" WE="7"/><Ie FD="BCK03" WE="7"/><Ie FD="ABBRP" WE="7"/><Ie FD="ABBRW" WE="7"/></QF></Grid></Configs><?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid Head="1" AWS="1" QDB="1"><Sql>select BCK01, BCK02, BCK03, ABBRP, ABBRW from BCK1</Sql><HD><Ie FD="BCK02" CA="编码" WD="100"/><Ie FD="BCK03" CA="名称" WD="100"/></HD><QF><Ie FD="BCK02" WE="7"/><Ie FD="BCK03" WE="7"/><Ie FD="ABBRP" WE="7"/><Ie FD="ABBRW" WE="7"/></QF></Grid></Configs>-1=全部
0=登记
1=核收
3=新开样本
4=完成
5=拒收
6=作废-1=全部
0=未打印
1=已打印<?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid Head="1" AWS="1" QDB="1"><Sql>select Distinct st.BCE01, st.BCE02, st.BCE03, st.BCE04, st.BCE05, st.BCE06, st.BCK01, st.ABBRP, st.ABBRW From BCE1 st
Join BCK1 te On st.BCK01 = te.BCK01
Join BAZ1 td On te.BCK01 = td.BCK01 Where td.BAU01=''31'' And st.BCE41 = 1</Sql><HD><Ie FD="BCE02" CA="编码" WD="100"/><Ie FD="BCE03" CA="名称" WD="100"/></HD><QF><Ie FD="BCE02" WE="7"/><Ie FD="BCE03" WE="7"/><Ie FD="ABBRP" WE="7"/><Ie FD="ABBRW" WE="7"/></QF></Grid></Configs>0=未作废
1=已作废
', Null, 1, 1, Null, 1, Null)
end
else --更新
begin
update Report_Objects set Config='
CE74F396F42C6F481554FB419AC5186CDECLARE @BeginDate varchar(30)
, @EndDate varchar(30)
, @ACF01 int/*医疗类别*/
, @BCE01C int/*核收人*/
, @VAA02 varchar(20)/*病历号*/
, @LAA08 varchar(20)/*条码号*/
, @VAA05 varchar(64)/*病人*/
, @BCK01A int/*申请病区ID*/
, @BCK01B int/*申请科室ID*/
, @BCK01C int/*核收科室ID*/
, @LAA32 int/*状态*/
, @PRNCP int/*打印*/
, @VCB04 varchar(20) /*会员卡*/
, @LAA42 int/*作废*/
select @BeginDate=:BeginDate
, @EndDate=:EndDate
, @ACF01 = :ACF01
, @BCE01C=:BCE01C
, @VAA02=:VAA02
, @LAA08=:LAA08
, @VAA05=:VAA05
, @BCK01A=:BCK01A
, @BCK01B=:BCK01B
, @BCK01C=:BCK01C
, @LAA32=:LAA32
, @PRNCP=:PRNCP
, @VCB04=:VCB04
, @LAA42=:LAA42
declare @sql varchar(8000)
--select @BeginDate = convert(varchar(10), @BeginDate, 120), @EndDate = convert(varchar(10), @EndDate, 120) + '' 23:59:59.999''
set @sql = ''
SELECT (case when ISNULL(PRNCP, 0) > 0 then 0 else 1 end) AS Checked, A.LAA01, A.LAA02, A.VAA01, A.VAA07, A.VAP01, A.ACF01, A.VAA02, A.LAA08, A.LAA09
, (case when A.VAP01 > 0 then (select P1.VAP05 from VAP1 P1 where P1.VAP01 = A.VAP01) else A.VAA05 end) VAA05
, A.ABW01, A.LAA12, A.LAA13
, A.AAU01, A.BES02, A.LAA16, A.BEL01, A.LAA18, A.LAA19, A.LAA20, A.ABB02, A.BCK01A, A.BCK01B, A.BCQ04, A.BCE03A, A.LAA26
, A.BCE03B, A.LAA28, A.BCK01C, A.BCE01C, A.BCE03C, A.LAA31, A.LAA32, A.LAA33, A.PRNCP, A.LAA35, A.VAF01, A.LAA37, A.LAA38
, BCE01D, BCE03D, LAA48, BCE01E, BCE03E, LAA51, isnull(LAA52, 0) LAA52, LAA53, LAA54
, F.ACF03, W.ABW02, U.AAU02, K1.BCK03 AS BCK03B, A.BAQ03, A.LAA45, L.BEL12, L.BEL08, A1.VBU01, (case when A.LAA08 = A.LAA09 then 0 else 1 end) as IsWG
, '''''''' as Field1, '''''''' as Field2, '''''''' as Field3, '''''''' as Field4, '''''''' as Field5
, '''''''' as Field6, '''''''' as Field7, '''''''' as Field8, '''''''' as Field9, '''''''' as Field10
FROM LAA1 A LEFT JOIN ACF1 F ON F.ACF01 = A.ACF01
LEFT JOIN ABW1 W ON W.ABW01 = A.ABW01
LEFT JOIN AAU1 U ON U.AAU01 = A.AAU01
LEFT JOIN BCK1 K1 ON K1.BCK01 = A.BCK01B
LEFT JOIN VAA1 A1 ON A1.VAA01 = A.VAA01
LEFT JOIN BEL1 L ON L.BEL01 = A.BEL01
WHERE A.LAA28 between '''''' + @BeginDate + '''''' and '''''' + @EndDate + ''''''''
if @BCE01C>0 set @sql = @sql+'' and A.BCE01C=''''''+convert(varchar, @BCE01C)+''''''''
if @VAA02>'''' set @sql = @sql+'' and (A.VAA02 = '''''' + @VAA02 + '''''' or A1.VAA03=''''''+@VAA02+'''''' or A1.VAA04=''''''+@VAA02+''''''
or ((select top 1 F1.SCF04 from SCF1 F1 join SCA1 C1 on C1.SCA01 = F1.SCA01 and C1.VAA01 = A.VAA01) = ''''''+@VAA02+'''''') )''
if @LAA08>'''' set @sql = @sql+'' and A.LAA08=''''''+@LAA08+''''''''
if @VAA05>'''' set @sql = @sql+'' and A.VAA05=''''''+@VAA05+''''''''
if @VCB04>'''' set @sql = @sql+'' and A1.VAA02=''''''+@VCB04+''''''''
if @BCK01A>0 set @sql = @sql+'' and A.BCK01A='' + convert(varchar, @BCK01A)
if @BCK01B>0 set @sql = @sql+'' and A.BCK01B='' + convert(varchar, @BCK01B)
if @BCK01C>0 set @sql = @sql+'' and A.BCK01C='' + convert(varchar, @BCK01C)
if @LAA32>-1 set @sql = @sql+'' and A.LAA32='' + convert(varchar, @LAA32)
if @PRNCP=0 set @sql = @sql+'' and A.PRNCP=0''
else if @PRNCP>=1 set @sql = @sql+'' and A.PRNCP>=1''
if @ACF01 > 0 set @sql = @sql + '' and A.ACF01 = '' + CONVERT(VARCHAR, @ACF01)
if @LAA42 = 0 set @sql = @sql + '' and A.LAA42 = 0''
else if @LAA42 = 1 set @sql = @sql + '' and A.LAA42 >= 1''
exec (@sql)0=全部
1=门诊
2=住院
4=体检<?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid Head="1" AWS="1" QDB="1"><Sql>SELECT DISTINCT a.BCK01,a.BCK02,a.BCK03,ABBRP,ABBRW
FROM BCK1 a
JOIN BAZ1 b ON a.BCK01=b.BCK01
and b.BAU01=''02''</Sql><HD><Ie FD="BCK02" CA="编码" WD="100"/><Ie FD="BCK03" CA="名称" WD="100"/></HD><QF><Ie FD="BCK02" WE="7"/><Ie FD="BCK03" WE="7"/><Ie FD="ABBRP" WE="7"/><Ie FD="ABBRW" WE="7"/></QF></Grid></Configs><?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid Head="1" AWS="1" QDB="1"><Sql>select BCK01, BCK02, BCK03, ABBRP, ABBRW from BCK1</Sql><HD><Ie FD="BCK02" CA="编码" WD="100"/><Ie FD="BCK03" CA="名称" WD="100"/></HD><QF><Ie FD="BCK02" WE="7"/><Ie FD="BCK03" WE="7"/><Ie FD="ABBRP" WE="7"/><Ie FD="ABBRW" WE="7"/></QF></Grid></Configs><?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid Head="1" AWS="1" QDB="1"><Sql>select BCK01, BCK02, BCK03, ABBRP, ABBRW from BCK1</Sql><HD><Ie FD="BCK02" CA="编码" WD="100"/><Ie FD="BCK03" CA="名称" WD="100"/></HD><QF><Ie FD="BCK02" WE="7"/><Ie FD="BCK03" WE="7"/><Ie FD="ABBRP" WE="7"/><Ie FD="ABBRW" WE="7"/></QF></Grid></Configs>-1=全部
0=登记
1=核收
3=新开样本
4=完成
5=拒收
6=作废-1=全部
0=未打印
1=已打印<?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid Head="1" AWS="1" QDB="1"><Sql>select Distinct st.BCE01, st.BCE02, st.BCE03, st.BCE04, st.BCE05, st.BCE06, st.BCK01, st.ABBRP, st.ABBRW From BCE1 st
Join BCK1 te On st.BCK01 = te.BCK01
Join BAZ1 td On te.BCK01 = td.BCK01 Where td.BAU01=''31'' And st.BCE41 = 1</Sql><HD><Ie FD="BCE02" CA="编码" WD="100"/><Ie FD="BCE03" CA="名称" WD="100"/></HD><QF><Ie FD="BCE02" WE="7"/><Ie FD="BCE03" WE="7"/><Ie FD="ABBRP" WE="7"/><Ie FD="ABBRW" WE="7"/></QF></Grid></Configs>0=未作废
1=已作废
' where Code='LIS.U.000005'
end
GO
--删除旧记录
--delete from Report_Objects where Code='LIS.U.000006' --删除脚本内容
IF not EXISTS(SELECT * FROM Report_Objects WHERE code='LIS.U.000006')
begin --插入
Declare @Report_Id_831 int
Exec Core_NewId 'Report_Objects', 'id', @Report_Id_831 out
Declare @Report_Id_831_CateID int
Set @Report_Id_831_CateID = (select id from Report_Categories where code='LIS.5')
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_831, 'LIS.U.000006', '标本登记查询项目信息', Null, Null, @Report_Id_831_CateID, 1, 3, 200, 0, Null, '2013-03-22 10:49:56', '2013-03-22 10:49:56', Null, '
60172F4C10A81688EC54F42E160FAA1Ddeclare @LAA01 varchar(1000)
set @LAA01 = :LAA01
if OBJECT_ID(''tempdb..#LAK1'') is not null drop table #LAK1
SELECT Distinct 1 AS Checked, K.LAK01, K.LAA01, K.VAF01, K.BBX01A, K.BBX01, K.LAK09
, X1.BBX05 AS BBX05A, X2.BBX05 AS BBX05, C.BFC03, A1.ACF01, 1 VBI01
, ISNULL(L.BEL01, 0) AS BEL01, L.BEL02, L.ABBRP, L.ABBRW, L.BEL05, L.BEL06, L.BEL07
, L.BEL08, L.BEL09, L.BBY01, L.BEL11, convert(varchar(2), '''') as Number
INTO #LAK1
FROM LAK1 K LEFT JOIN BBX1 X1 ON X1.BBX01 = K.BBX01A
LEFT JOIN BBX1 X2 ON X2.BBX01 = K.BBX01
LEFT JOIN BFC1 C ON C.BBX01 = K.BBX01
LEFT JOIN BEL1 L ON L.BEL01 = X2.BBX19
left join LAA1 A1 on A1.LAA01 = K.LAA01
WHERE K.LAA01 in (@LAA01)
ORDER BY K.BBX01A, K.BBX01
SELECT * FROM #LAK1
DROP TABLE #LAK1
', Null, 1, 1, Null, 1, Null)
end
else --更新
begin
update Report_Objects set Config='
60172F4C10A81688EC54F42E160FAA1Ddeclare @LAA01 varchar(1000)
set @LAA01 = :LAA01
if OBJECT_ID(''tempdb..#LAK1'') is not null drop table #LAK1
SELECT Distinct 1 AS Checked, K.LAK01, K.LAA01, K.VAF01, K.BBX01A, K.BBX01, K.LAK09
, X1.BBX05 AS BBX05A, X2.BBX05 AS BBX05, C.BFC03, A1.ACF01, 1 VBI01
, ISNULL(L.BEL01, 0) AS BEL01, L.BEL02, L.ABBRP, L.ABBRW, L.BEL05, L.BEL06, L.BEL07
, L.BEL08, L.BEL09, L.BBY01, L.BEL11, convert(varchar(2), '''') as Number
INTO #LAK1
FROM LAK1 K LEFT JOIN BBX1 X1 ON X1.BBX01 = K.BBX01A
LEFT JOIN BBX1 X2 ON X2.BBX01 = K.BBX01
LEFT JOIN BFC1 C ON C.BBX01 = K.BBX01
LEFT JOIN BEL1 L ON L.BEL01 = X2.BBX19
left join LAA1 A1 on A1.LAA01 = K.LAA01
WHERE K.LAA01 in (@LAA01)
ORDER BY K.BBX01A, K.BBX01
SELECT * FROM #LAK1
DROP TABLE #LAK1
' where Code='LIS.U.000006'
end
GO
--删除旧记录
--delete from Report_Objects where Code='LIS.U.000007' --删除脚本内容
IF not EXISTS(SELECT * FROM Report_Objects WHERE code='LIS.U.000007')
begin --插入
Declare @Report_Id_835 int
Exec Core_NewId 'Report_Objects', 'id', @Report_Id_835 out
Declare @Report_Id_835_CateID int
Set @Report_Id_835_CateID = (select id from Report_Categories where code='LIS.5')
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_835, 'LIS.U.000007', '样本查询(不弹出窗口)', Null, Null, @Report_Id_835_CateID, 1, 3, 200, 0, Null, '2013-03-28 14:52:04', '2013-03-28 15:07:59', Null, '
8486DC90F1B845D700688BC00EC093CEdeclare @UserID int
, @BeginDate varchar(20)
, @EndDate varchar(20)
, @Group int
, @BEY01 int
, @ACF01 varchar(4)
, @BCK01C varchar(200)
, @ABB02 varchar(30)
, @BCE03 varchar(30)
, @BCK01D varchar(200)
, @LAB32 int
, @LAB36 int
, @BEY09 varchar(50)
, @LAB42 varchar(20)
, @LAB09 varchar(20)
, @VAA05 varchar(64)
, @BCE03B varchar(10)
, @BCE03D varchar(10)
, @OpenSecret int
select @UserID = :UserID
, @BeginDate = :BeginDate
, @EndDate = :EndDate
, @Group = :Group
, @BEY01 = :BEY01
, @ACF01 = :ACF01
, @BCK01C = :BCK01C
, @ABB02 = :ABB02
, @BCE03 = :BCE03
, @BCK01D = :BCK01D
, @LAB32 = :LAB32
, @LAB36 = :LAB36
, @BEY09 = :BEY09
, @LAB42 = :LAB42
, @LAB09 = :LAB09
, @VAA05 = :VAA05
, @BCE03B = :BCE03B
, @BCE03D = :BCE03D
, @OpenSecret = :OpenSecret
DECLARE @sql VARCHAR(8000)
set @sql = ''
if OBJECT_ID(''''tempdb..#LAB1'''') is not null drop table #LAB1
select Checked=0, B1.BEZ01, Z1.BEZ03, A.LAB01, A.LAB02, A.VAA01, A.VAA07, A.VAP01, A.BCK01A, A.ABBRP, A.ABBRW, A.LAB09
, (case when A.VAP01 > 0 then (select P1.VAP05 from VAP1 P1 where P1.VAP01 = A.VAP01) else A.VAA05 end) VAA05, ISNULL(A.ABW01, 0) ABW01 ,ISNULL(x.ABW02, 0) ABW02
, A.LAB12, ISNULL(A.AAU01, ''''Y'''') AAU01, A.LAB14, A.BDP02, A.LAB16, A.BES02, A.LAB18, ISNULL(A.ACF01, 1) ACF01, A.LAB20, ISNULL(A.LAB21, 1) LAB21, A.LAB22
, A.LAB23, A.ABB02, A.BEY01A, A.BEY01B, A.BEW02, A.BCK01B, Y.BEY07, A.LAB30, A.LAB31, ISNULL(A.LAB32, 0) LAB32, A.LAB33
, A.LAB34, A.LAB35, A.LAB36, A.LAB37, A.BCE03, A.BCK01C, ISNULL(A.LAB40, GETDATE()) LAB40, A.VAF01, A.LAB42, A.LAB43, A.BCE03A
, A.BCE03C, A.LAB46, A.BCE01D, A.BCE03D, A.BCE03E, A.LAB50, A.BCE01B, A.BCE03B, A.LAB53, A.LAB54
, A.LAB55, A.LAB56, A.BET02, A.LAB58, A.LAB59, A.LAB60, A.LAB61, A.LAB62, A.LAB63, A.LAB64, A.BEX02
, A.BCK01D, A.BAQ03, isnull(A.LAB68, 0) LAB68, A.LAB69, ISNULL(A.LAB70, 0) LAB70, ISNULL(A.LAB71, 0) LAB71
, A.BCE01F, A.BCE03F, A.LAB74, A.VAA01_64, A.VAA07_64, A.VAP01_64, A.LAB87
, K1.BCK03 AS BCK03A, K2.BCK03 AS BCK03B, K3.BCK03 AS BCK03C, K4.BCK03 AS BCK03D
, U1.AAU02, Y.BEY03 BEY03A, Y.BEY02 BEY02A
, (CASE A.ACF01 WHEN 1 THEN ''''门诊'''' WHEN 2 THEN ''''住院'''' WHEN 4 THEN ''''体检'''' ELSE ''''其他'''' END) ACF03
, ISNULL(H1.BFU01, 0) BFU01, ISNULL(U.BFU03, '''''''') BFU03, H1.LAH08, convert(varchar(2000), '''''''') as TESTAREA, convert(varchar(100), '''''''') as Postfix
, A.LAB77, A.LAB78, A.LAB79, A.LAB80, A.BCE01G, A.BCE03G, A.LAB81, A.BCE01, A.BCE02
, CASE WHEN ISNULL(A.BCE03F, '''''''') > '''''''' THEN 1 ELSE 0 END IsCheck
into #LAB1
From LAB1 A LEFT JOIN BCK1 K1 ON K1.BCK01 = A.BCK01A
LEFT JOIN ABW1 x ON A.ABW01 = x.ABW01
LEFT JOIN BCK1 K2 ON K2.BCK01 = A.BCK01B
LEFT JOIN BCK1 K3 ON K3.BCK01 = A.BCK01C
LEFT JOIN BCK1 K4 ON K4.BCK01 = A.BCK01D
LEFT JOIN BEY1 Y ON Y.BEY01 = A.BEY01A AND ISNULL(Y.BEY52, 0) = 0
LEFT JOIN AAU1 U1 ON U1.AAU01 = A.AAU01
LEFT JOIN LAH1 H1 ON H1.LAB01 = A.LAB01
LEFT JOIN BFU1 U ON U.BFU01 = H1.BFU01
JOIN BFB1 B1 ON B1.BEY01 = A.BEY01A
JOIN BEZ1 Z1 ON Z1.BEZ01 = B1.BEZ01
''
set @sql = @sql + '' Where A.LAB20 Between '''''' + @BeginDate + '''''' and '''''' + @EndDate + ''''''''
select @Group = isnull(@Group, 0), @BEY01 = isnull(@BEY01, 0)
if @Group > -1
set @sql = @sql + '' and B1.BEZ01 = '' + CONVERT(VARCHAR, @Group)
if @BEY01 > -1
set @sql = @sql + '' And A.BEY01A = '' + CONVERT(VARCHAR, @BEY01)
if @ACF01 > ''0''
set @sql = @sql + '' and A.ACF01 = '' + @ACF01
if @BCK01C > ''-1''
set @sql = @sql + '' And A.BCK01C IN ('' + @BCK01C + '')''
if @ABB02 > ''''
set @sql = @sql + '' And A.ABB02 = '''''' + @ABB02 + ''''''''
if @BCE03 > ''''
set @sql = @sql + '' And A.BCE03 = '''''' + @BCE03 + ''''''''
if @BCK01D > ''-1''
set @sql = @sql + '' And A.BCK01D = '' + @BCK01D
if @LAB32 = 1
set @sql = @sql + '' And (A.LAB32 = 3)''
if @LAB32 = 2
set @sql = @sql + '' And (A.LAB32 <= 2 or A.LAB32 is null)''
if @LAB36 = 1
set @sql = @sql + '' And (A.LAB36 >= 1)''
if @LAB36 = 2
set @sql = @sql + '' And (A.LAB36 = 0 or A.LAB36 is null)''
if @BEY09 > ''''
set @sql = @sql + '' and Y.BEY09 = '''''' + @BEY09 + ''''''''
if @LAB42 > ''''
begin
set @LAB42 = ''%%'' + @LAB42 + ''%%''
set @sql = @sql + '' And A.LAB42 like '''''' + @LAB42 + ''''''''
end
if @LAB09 > ''''
begin
set @LAB09 = ''%%'' + @LAB09 + ''%%''
set @sql = @sql + '' And A.LAB09 like '''''' + @LAB09 + ''''''''
end
if @VAA05 > ''''
begin
set @VAA05 = ''%%'' + @VAA05 + ''%%''
set @sql = @sql + '' And (A.VAA05 like '''''' + @VAA05
+ '''''' or A.ABBRP like '''''' + @VAA05
+ '''''' or A.ABBRW like '''''' + @VAA05 + '''''')''
end
if @BCE03B > ''''
set @sql = @sql + '' and A.BCE03B = '''''' + @BCE03B + ''''''''
if @BCE03D > ''''
set @sql = @sql + '' and A.BCE03D = '''''' + @BCE03D + ''''''''
if @OpenSecret = 0
set @sql = @sql + '' and (isnull(A.LAB70, 0) = 0 or A.BCE01D = '' + convert(varchar, @UserID) + '')''
--set @sql = @sql + '' Order BY A.BEY01A, Convert(varchar(10), A.LAB20, 120), LAB23''
--SET @SQL = @SQL + ''order by A.BEY01A, A.LAB23''
SET @sql = @sql + '' SELECT * FROM #LAB1 Order BY BEY01A, Convert(varchar(10),LAB20, 120), LAB23
DROP TABLE #LAB1''
EXEC(@sql)<?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid Head="1" AWS="1"><Sql>DECLARE @BCE01 int
SELECT @BCE01 = <UserID>
Select A.BEZ01, A.BEZ02, A.BEZ03
From BEZ1 A Join BFA1 B On A.BEZ01 = B.BEZ01
Where B.BCE01 = @BCE01</Sql><HD><Ie FD="BEZ02" CA="编码" WD="100"/><Ie FD="BEZ03" CA="名称" WD="100"/></HD></Grid></Configs><?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid Head="1" AWS="1"><Sql>DECLARE @BEZ01 int
SELECT @BEZ01 = <Group>
select -1 BEY01, '''' BEY02, ''所有仪器'' BEY03, 0 ISDEF
union all
select A.BEY01,A.BEY02,A.BEY03, B.ISDEF
from BEY1 A
Join BFB1 B On A.BEY01=B.BEY01
Join BEZ1 C On C.BEZ01=B.BEZ01
where C.BEZ01 = @BEZ01</Sql><HD><Ie FD="BEY02" CA="编码" WD="100"/><Ie FD="BEY03" CA="名称" WD="100"/></HD></Grid></Configs>0=
1=门诊
2=住院
3=体检
4=其他<?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid Head="1" AWS="1"><Sql>SELECT DISTINCT A.BCK01,A.BCK02,A.BCK03,ABBRP,ABBRW
FROM BCK1 A JOIN BAZ1 B ON A.BCK01=B.BCK01 And B.BAU01=''02''</Sql><HD><Ie FD="BCK02" CA="编码" WD="100"/><Ie FD="BCK03" CA="名称" WD="100"/></HD></Grid></Configs><?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid><Sql>SELECT DISTINCT A.BCK01,A.BCK02,A.BCK03,ABBRP,ABBRW
FROM BCK1 A JOIN BAZ1 B ON A.BCK01=B.BCK01 And B.BAU01=''01''</Sql><HD><Ie FD="BCK03" WD="100"/></HD><QF><Ie FD="BCK03" WE="7"/><Ie FD="BCK02" WE="7"/><Ie FD="abbrp" WE="7"/></QF></Grid></Configs>1=已审核
2=未审核1=已打印
2=未打印
', Null, 1, 1, Null, 1, Null)
end
else --更新
begin
update Report_Objects set Config='
8486DC90F1B845D700688BC00EC093CEdeclare @UserID int
, @BeginDate varchar(20)
, @EndDate varchar(20)
, @Group int
, @BEY01 int
, @ACF01 varchar(4)
, @BCK01C varchar(200)
, @ABB02 varchar(30)
, @BCE03 varchar(30)
, @BCK01D varchar(200)
, @LAB32 int
, @LAB36 int
, @BEY09 varchar(50)
, @LAB42 varchar(20)
, @LAB09 varchar(20)
, @VAA05 varchar(64)
, @BCE03B varchar(10)
, @BCE03D varchar(10)
, @OpenSecret int
select @UserID = :UserID
, @BeginDate = :BeginDate
, @EndDate = :EndDate
, @Group = :Group
, @BEY01 = :BEY01
, @ACF01 = :ACF01
, @BCK01C = :BCK01C
, @ABB02 = :ABB02
, @BCE03 = :BCE03
, @BCK01D = :BCK01D
, @LAB32 = :LAB32
, @LAB36 = :LAB36
, @BEY09 = :BEY09
, @LAB42 = :LAB42
, @LAB09 = :LAB09
, @VAA05 = :VAA05
, @BCE03B = :BCE03B
, @BCE03D = :BCE03D
, @OpenSecret = :OpenSecret
DECLARE @sql VARCHAR(8000)
set @sql = ''
if OBJECT_ID(''''tempdb..#LAB1'''') is not null drop table #LAB1
select Checked=0, B1.BEZ01, Z1.BEZ03, A.LAB01, A.LAB02, A.VAA01, A.VAA07, A.VAP01, A.BCK01A, A.ABBRP, A.ABBRW, A.LAB09
, (case when A.VAP01 > 0 then (select P1.VAP05 from VAP1 P1 where P1.VAP01 = A.VAP01) else A.VAA05 end) VAA05, ISNULL(A.ABW01, 0) ABW01 ,ISNULL(x.ABW02, 0) ABW02
, A.LAB12, ISNULL(A.AAU01, ''''Y'''') AAU01, A.LAB14, A.BDP02, A.LAB16, A.BES02, A.LAB18, ISNULL(A.ACF01, 1) ACF01, A.LAB20, ISNULL(A.LAB21, 1) LAB21, A.LAB22
, A.LAB23, A.ABB02, A.BEY01A, A.BEY01B, A.BEW02, A.BCK01B, Y.BEY07, A.LAB30, A.LAB31, ISNULL(A.LAB32, 0) LAB32, A.LAB33
, A.LAB34, A.LAB35, A.LAB36, A.LAB37, A.BCE03, A.BCK01C, ISNULL(A.LAB40, GETDATE()) LAB40, A.VAF01, A.LAB42, A.LAB43, A.BCE03A
, A.BCE03C, A.LAB46, A.BCE01D, A.BCE03D, A.BCE03E, A.LAB50, A.BCE01B, A.BCE03B, A.LAB53, A.LAB54
, A.LAB55, A.LAB56, A.BET02, A.LAB58, A.LAB59, A.LAB60, A.LAB61, A.LAB62, A.LAB63, A.LAB64, A.BEX02
, A.BCK01D, A.BAQ03, isnull(A.LAB68, 0) LAB68, A.LAB69, ISNULL(A.LAB70, 0) LAB70, ISNULL(A.LAB71, 0) LAB71
, A.BCE01F, A.BCE03F, A.LAB74, A.VAA01_64, A.VAA07_64, A.VAP01_64, A.LAB87
, K1.BCK03 AS BCK03A, K2.BCK03 AS BCK03B, K3.BCK03 AS BCK03C, K4.BCK03 AS BCK03D
, U1.AAU02, Y.BEY03 BEY03A, Y.BEY02 BEY02A
, (CASE A.ACF01 WHEN 1 THEN ''''门诊'''' WHEN 2 THEN ''''住院'''' WHEN 4 THEN ''''体检'''' ELSE ''''其他'''' END) ACF03
, ISNULL(H1.BFU01, 0) BFU01, ISNULL(U.BFU03, '''''''') BFU03, H1.LAH08, convert(varchar(2000), '''''''') as TESTAREA, convert(varchar(100), '''''''') as Postfix
, A.LAB77, A.LAB78, A.LAB79, A.LAB80, A.BCE01G, A.BCE03G, A.LAB81, A.BCE01, A.BCE02
, CASE WHEN ISNULL(A.BCE03F, '''''''') > '''''''' THEN 1 ELSE 0 END IsCheck
into #LAB1
From LAB1 A LEFT JOIN BCK1 K1 ON K1.BCK01 = A.BCK01A
LEFT JOIN ABW1 x ON A.ABW01 = x.ABW01
LEFT JOIN BCK1 K2 ON K2.BCK01 = A.BCK01B
LEFT JOIN BCK1 K3 ON K3.BCK01 = A.BCK01C
LEFT JOIN BCK1 K4 ON K4.BCK01 = A.BCK01D
LEFT JOIN BEY1 Y ON Y.BEY01 = A.BEY01A AND ISNULL(Y.BEY52, 0) = 0
LEFT JOIN AAU1 U1 ON U1.AAU01 = A.AAU01
LEFT JOIN LAH1 H1 ON H1.LAB01 = A.LAB01
LEFT JOIN BFU1 U ON U.BFU01 = H1.BFU01
JOIN BFB1 B1 ON B1.BEY01 = A.BEY01A
JOIN BEZ1 Z1 ON Z1.BEZ01 = B1.BEZ01
''
set @sql = @sql + '' Where A.LAB20 Between '''''' + @BeginDate + '''''' and '''''' + @EndDate + ''''''''
select @Group = isnull(@Group, 0), @BEY01 = isnull(@BEY01, 0)
if @Group > -1
set @sql = @sql + '' and B1.BEZ01 = '' + CONVERT(VARCHAR, @Group)
if @BEY01 > -1
set @sql = @sql + '' And A.BEY01A = '' + CONVERT(VARCHAR, @BEY01)
if @ACF01 > ''0''
set @sql = @sql + '' and A.ACF01 = '' + @ACF01
if @BCK01C > ''-1''
set @sql = @sql + '' And A.BCK01C IN ('' + @BCK01C + '')''
if @ABB02 > ''''
set @sql = @sql + '' And A.ABB02 = '''''' + @ABB02 + ''''''''
if @BCE03 > ''''
set @sql = @sql + '' And A.BCE03 = '''''' + @BCE03 + ''''''''
if @BCK01D > ''-1''
set @sql = @sql + '' And A.BCK01D = '' + @BCK01D
if @LAB32 = 1
set @sql = @sql + '' And (A.LAB32 = 3)''
if @LAB32 = 2
set @sql = @sql + '' And (A.LAB32 <= 2 or A.LAB32 is null)''
if @LAB36 = 1
set @sql = @sql + '' And (A.LAB36 >= 1)''
if @LAB36 = 2
set @sql = @sql + '' And (A.LAB36 = 0 or A.LAB36 is null)''
if @BEY09 > ''''
set @sql = @sql + '' and Y.BEY09 = '''''' + @BEY09 + ''''''''
if @LAB42 > ''''
begin
set @LAB42 = ''%%'' + @LAB42 + ''%%''
set @sql = @sql + '' And A.LAB42 like '''''' + @LAB42 + ''''''''
end
if @LAB09 > ''''
begin
set @LAB09 = ''%%'' + @LAB09 + ''%%''
set @sql = @sql + '' And A.LAB09 like '''''' + @LAB09 + ''''''''
end
if @VAA05 > ''''
begin
set @VAA05 = ''%%'' + @VAA05 + ''%%''
set @sql = @sql + '' And (A.VAA05 like '''''' + @VAA05
+ '''''' or A.ABBRP like '''''' + @VAA05
+ '''''' or A.ABBRW like '''''' + @VAA05 + '''''')''
end
if @BCE03B > ''''
set @sql = @sql + '' and A.BCE03B = '''''' + @BCE03B + ''''''''
if @BCE03D > ''''
set @sql = @sql + '' and A.BCE03D = '''''' + @BCE03D + ''''''''
if @OpenSecret = 0
set @sql = @sql + '' and (isnull(A.LAB70, 0) = 0 or A.BCE01D = '' + convert(varchar, @UserID) + '')''
--set @sql = @sql + '' Order BY A.BEY01A, Convert(varchar(10), A.LAB20, 120), LAB23''
--SET @SQL = @SQL + ''order by A.BEY01A, A.LAB23''
SET @sql = @sql + '' SELECT * FROM #LAB1 Order BY BEY01A, Convert(varchar(10),LAB20, 120), LAB23
DROP TABLE #LAB1''
EXEC(@sql)<?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid Head="1" AWS="1"><Sql>DECLARE @BCE01 int
SELECT @BCE01 = <UserID>
Select A.BEZ01, A.BEZ02, A.BEZ03
From BEZ1 A Join BFA1 B On A.BEZ01 = B.BEZ01
Where B.BCE01 = @BCE01</Sql><HD><Ie FD="BEZ02" CA="编码" WD="100"/><Ie FD="BEZ03" CA="名称" WD="100"/></HD></Grid></Configs><?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid Head="1" AWS="1"><Sql>DECLARE @BEZ01 int
SELECT @BEZ01 = <Group>
select -1 BEY01, '''' BEY02, ''所有仪器'' BEY03, 0 ISDEF
union all
select A.BEY01,A.BEY02,A.BEY03, B.ISDEF
from BEY1 A
Join BFB1 B On A.BEY01=B.BEY01
Join BEZ1 C On C.BEZ01=B.BEZ01
where C.BEZ01 = @BEZ01</Sql><HD><Ie FD="BEY02" CA="编码" WD="100"/><Ie FD="BEY03" CA="名称" WD="100"/></HD></Grid></Configs>0=
1=门诊
2=住院
3=体检
4=其他<?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid Head="1" AWS="1"><Sql>SELECT DISTINCT A.BCK01,A.BCK02,A.BCK03,ABBRP,ABBRW
FROM BCK1 A JOIN BAZ1 B ON A.BCK01=B.BCK01 And B.BAU01=''02''</Sql><HD><Ie FD="BCK02" CA="编码" WD="100"/><Ie FD="BCK03" CA="名称" WD="100"/></HD></Grid></Configs><?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid><Sql>SELECT DISTINCT A.BCK01,A.BCK02,A.BCK03,ABBRP,ABBRW
FROM BCK1 A JOIN BAZ1 B ON A.BCK01=B.BCK01 And B.BAU01=''01''</Sql><HD><Ie FD="BCK03" WD="100"/></HD><QF><Ie FD="BCK03" WE="7"/><Ie FD="BCK02" WE="7"/><Ie FD="abbrp" WE="7"/></QF></Grid></Configs>1=已审核
2=未审核1=已打印
2=未打印
' where Code='LIS.U.000007'
end
GO
--删除旧记录
--delete from Report_Objects where Code='LIS.U.000009' --删除脚本内容
IF not EXISTS(SELECT * FROM Report_Objects WHERE code='LIS.U.000009')
begin --插入
Declare @Report_Id_1022 int
Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1022 out
Declare @Report_Id_1022_CateID int
Set @Report_Id_1022_CateID = (select id from Report_Categories where code='LIS.5')
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_1022, 'LIS.U.000009', '条码查询', Null, Null, @Report_Id_1022_CateID, 1, 3, 200, 0, Null, '2014-01-16 15:11:19', '2014-01-16 15:11:19', Null, '
6764A2AB097C0A3F35FF8C59511492DDDeclare @BeginDate datetime
, @EndDate Datetime
select @BeginDate = :BeginDate
, @EndDate = :EndDate
select A.LAA08 AS 条码号, A.VAA05 病人姓名, (case when A.ACF01=1 then ''门诊'' when A.ACF01=2 then ''住院'' else ''体检'' end) 医疗类型
, B.ABW02 性别, A.ABB02 样本类型, A.LAA28 采样时间, ''1'' as ss
from LAA1 A left join ABW1 B on B.ABW01 = A.ABW01
where A.LAA28 between @BeginDate and @EndDate
and not exists(select * from LAB1 B where B.LAB42 = A.LAA08)
', Null, 1, 1, Null, 1, Null)
end
else --更新
begin
update Report_Objects set Config='
6764A2AB097C0A3F35FF8C59511492DDDeclare @BeginDate datetime
, @EndDate Datetime
select @BeginDate = :BeginDate
, @EndDate = :EndDate
select A.LAA08 AS 条码号, A.VAA05 病人姓名, (case when A.ACF01=1 then ''门诊'' when A.ACF01=2 then ''住院'' else ''体检'' end) 医疗类型
, B.ABW02 性别, A.ABB02 样本类型, A.LAA28 采样时间, ''1'' as ss
from LAA1 A left join ABW1 B on B.ABW01 = A.ABW01
where A.LAA28 between @BeginDate and @EndDate
and not exists(select * from LAB1 B where B.LAB42 = A.LAA08)
' where Code='LIS.U.000009'
end
GO
--删除旧记录
--delete from Report_Objects where Code='LIS.U.000011' --删除脚本内容
IF not EXISTS(SELECT * FROM Report_Objects WHERE code='LIS.U.000011')
begin --插入
Declare @Report_Id_1155 int
Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1155 out
Declare @Report_Id_1155_CateID int
Set @Report_Id_1155_CateID = (select id from Report_Categories where code='LIS.5')
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_1155, 'LIS.U.000011', '标本登记查询病人信息（单病人检索）', Null, Null, @Report_Id_1155_CateID, 1, 3, 200, 0, Null, '2015-09-25 16:59:27', '2015-09-25 17:00:08', Null, '
D84595F6A48173BB925D8D5F52EC7292DECLARE @BDate datetime
, @EDate datetime
, @LAA08 varchar(20)
, @VAA02 varchar(64)
, @VCB04 varchar(64)
, @VAA15 varchar(20)
, @strLAA01 varchar(1000)
SELECT @BDate = :BDate
, @EDate = :EDate
, @LAA08 = :LAA08
, @VAA02 = :VAA02
, @VCB04 = :VCB04
, @VAA15 = :VAA15
, @strLAA01 = :strLAA01
if @strLAA01 > ''''
SELECT 1 AS Checked, A.LAA01, A.LAA02, A.VAA01, A.VAA07, A.VAP01, A.ACF01, A.VAA02, A.LAA08, A.LAA09, A.VAA05, A.ABW01, A.LAA12, A.LAA13
, A.AAU01, A.BES02, A.LAA16, A.BEL01, A.LAA18, A.LAA19, A.LAA20, A.ABB02, A.BCK01A, A.BCK01B, A.BCQ04, A.BCE03A, A.LAA26
, A.BCE03B, A.LAA28, A.BCK01C, A.BCE01C, A.BCE03C, A.LAA31, A.LAA32, A.LAA33, A.PRNCP, A.LAA35, A.VAF01, A.LAA37, A.LAA38
, BCE01D, BCE03D, LAA48, BCE01E, BCE03E, LAA51, LAA52, LAA53, LAA54
, F.ACF03, W.ABW02, U.AAU02, K1.BCK03 AS BCK03B, A.BAQ03, A.LAA45, L.BEL12, L.BEL08, A1.VBU01, (case when A.LAA08 = A.LAA09 then 0 else 1 end) as IsWG
, '''' as Field1, '''' as Field2, '''' as Field3, '''' as Field4, '''' as Field5
, '''' as Field6, '''' as Field7, '''' as Field8, '''' as Field9, '''' as Field10
FROM LAA1 A LEFT JOIN ACF1 F ON F.ACF01 = A.ACF01
LEFT JOIN ABW1 W ON W.ABW01 = A.ABW01
LEFT JOIN AAU1 U ON U.AAU01 = A.AAU01
LEFT JOIN BCK1 K1 ON K1.BCK01 = A.BCK01B
LEFT JOIN VAA1 A1 ON A1.VAA01 = A.VAA01
LEFT JOIN BEL1 L ON L.BEL01 = A.BEL01
WHERE A.LAA01 in (select Value from dbo.Split(@strLAA01, '',''))
else
SELECT 1 AS Checked, A.LAA01, A.LAA02, A.VAA01, A.VAA07, A.VAP01, A.ACF01, A.VAA02, A.LAA08, A.LAA09, A.VAA05, A.ABW01, A.LAA12, A.LAA13
, A.AAU01, A.BES02, A.LAA16, A.BEL01, A.LAA18, A.LAA19, A.LAA20, A.ABB02, A.BCK01A, A.BCK01B, A.BCQ04, A.BCE03A, A.LAA26
, A.BCE03B, A.LAA28, A.BCK01C, A.BCE01C, A.BCE03C, A.LAA31, A.LAA32, A.LAA33, A.PRNCP, A.LAA35, A.VAF01, A.LAA37, A.LAA38
, BCE01D, BCE03D, LAA48, BCE01E, BCE03E, LAA51, LAA52, LAA53, LAA54
, F.ACF03, W.ABW02, U.AAU02, K1.BCK03 AS BCK03B, A.BAQ03, A.LAA45, L.BEL12, L.BEL08, A1.VBU01, (case when A.LAA08 = A.LAA09 then 0 else 1 end) as IsWG
, '''' as Field1, '''' as Field2, '''' as Field3, '''' as Field4, '''' as Field5
, '''' as Field6, '''' as Field7, '''' as Field8, '''' as Field9, '''' as Field10
FROM LAA1 A LEFT JOIN ACF1 F ON F.ACF01 = A.ACF01
LEFT JOIN ABW1 W ON W.ABW01 = A.ABW01
LEFT JOIN AAU1 U ON U.AAU01 = A.AAU01
LEFT JOIN BCK1 K1 ON K1.BCK01 = A.BCK01B
LEFT JOIN VAA1 A1 ON A1.VAA01 = A.VAA01
LEFT JOIN BEL1 L ON L.BEL01 = A.BEL01
WHERE (A.LAA28 >= @BDate and A.LAA28 <= @EDate) and
(
(@LAA08 > '''' AND A.LAA08 = @LAA08)
OR (@VAA02 > '''' AND (A.VAA02 = @VAA02 or A1.VAA03=@VAA02 or A1.VAA04=@VAA02
--or ((select top 1 F1.SCF04 from SCF1 F1 join SCA1 C1 on C1.SCA01 = F1.SCA01 and C1.VAA01 = A.VAA01) = @VAA02)
) )
or (@VCB04 > '''' AND (A1.VAA01 = isnull((select top 1 VAA01 from VBU1 where VBU01 = (select TOP 1 VBU01 from VCB1 where VCB04 = @VCB04)), 0) )
or (@VAA15 > '''' AND (A1.VAA15 = @VAA15))
))
', Null, 0, 1, Null, 1, Null)
end
else --更新
begin
update Report_Objects set Config='
D84595F6A48173BB925D8D5F52EC7292DECLARE @BDate datetime
, @EDate datetime
, @LAA08 varchar(20)
, @VAA02 varchar(64)
, @VCB04 varchar(64)
, @VAA15 varchar(20)
, @strLAA01 varchar(1000)
SELECT @BDate = :BDate
, @EDate = :EDate
, @LAA08 = :LAA08
, @VAA02 = :VAA02
, @VCB04 = :VCB04
, @VAA15 = :VAA15
, @strLAA01 = :strLAA01
if @strLAA01 > ''''
SELECT 1 AS Checked, A.LAA01, A.LAA02, A.VAA01, A.VAA07, A.VAP01, A.ACF01, A.VAA02, A.LAA08, A.LAA09, A.VAA05, A.ABW01, A.LAA12, A.LAA13
, A.AAU01, A.BES02, A.LAA16, A.BEL01, A.LAA18, A.LAA19, A.LAA20, A.ABB02, A.BCK01A, A.BCK01B, A.BCQ04, A.BCE03A, A.LAA26
, A.BCE03B, A.LAA28, A.BCK01C, A.BCE01C, A.BCE03C, A.LAA31, A.LAA32, A.LAA33, A.PRNCP, A.LAA35, A.VAF01, A.LAA37, A.LAA38
, BCE01D, BCE03D, LAA48, BCE01E, BCE03E, LAA51, LAA52, LAA53, LAA54
, F.ACF03, W.ABW02, U.AAU02, K1.BCK03 AS BCK03B, A.BAQ03, A.LAA45, L.BEL12, L.BEL08, A1.VBU01, (case when A.LAA08 = A.LAA09 then 0 else 1 end) as IsWG
, '''' as Field1, '''' as Field2, '''' as Field3, '''' as Field4, '''' as Field5
, '''' as Field6, '''' as Field7, '''' as Field8, '''' as Field9, '''' as Field10
FROM LAA1 A LEFT JOIN ACF1 F ON F.ACF01 = A.ACF01
LEFT JOIN ABW1 W ON W.ABW01 = A.ABW01
LEFT JOIN AAU1 U ON U.AAU01 = A.AAU01
LEFT JOIN BCK1 K1 ON K1.BCK01 = A.BCK01B
LEFT JOIN VAA1 A1 ON A1.VAA01 = A.VAA01
LEFT JOIN BEL1 L ON L.BEL01 = A.BEL01
WHERE A.LAA01 in (select Value from dbo.Split(@strLAA01, '',''))
else
SELECT 1 AS Checked, A.LAA01, A.LAA02, A.VAA01, A.VAA07, A.VAP01, A.ACF01, A.VAA02, A.LAA08, A.LAA09, A.VAA05, A.ABW01, A.LAA12, A.LAA13
, A.AAU01, A.BES02, A.LAA16, A.BEL01, A.LAA18, A.LAA19, A.LAA20, A.ABB02, A.BCK01A, A.BCK01B, A.BCQ04, A.BCE03A, A.LAA26
, A.BCE03B, A.LAA28, A.BCK01C, A.BCE01C, A.BCE03C, A.LAA31, A.LAA32, A.LAA33, A.PRNCP, A.LAA35, A.VAF01, A.LAA37, A.LAA38
, BCE01D, BCE03D, LAA48, BCE01E, BCE03E, LAA51, LAA52, LAA53, LAA54
, F.ACF03, W.ABW02, U.AAU02, K1.BCK03 AS BCK03B, A.BAQ03, A.LAA45, L.BEL12, L.BEL08, A1.VBU01, (case when A.LAA08 = A.LAA09 then 0 else 1 end) as IsWG
, '''' as Field1, '''' as Field2, '''' as Field3, '''' as Field4, '''' as Field5
, '''' as Field6, '''' as Field7, '''' as Field8, '''' as Field9, '''' as Field10
FROM LAA1 A LEFT JOIN ACF1 F ON F.ACF01 = A.ACF01
LEFT JOIN ABW1 W ON W.ABW01 = A.ABW01
LEFT JOIN AAU1 U ON U.AAU01 = A.AAU01
LEFT JOIN BCK1 K1 ON K1.BCK01 = A.BCK01B
LEFT JOIN VAA1 A1 ON A1.VAA01 = A.VAA01
LEFT JOIN BEL1 L ON L.BEL01 = A.BEL01
WHERE (A.LAA28 >= @BDate and A.LAA28 <= @EDate) and
(
(@LAA08 > '''' AND A.LAA08 = @LAA08)
OR (@VAA02 > '''' AND (A.VAA02 = @VAA02 or A1.VAA03=@VAA02 or A1.VAA04=@VAA02
--or ((select top 1 F1.SCF04 from SCF1 F1 join SCA1 C1 on C1.SCA01 = F1.SCA01 and C1.VAA01 = A.VAA01) = @VAA02)
) )
or (@VCB04 > '''' AND (A1.VAA01 = isnull((select top 1 VAA01 from VBU1 where VBU01 = (select TOP 1 VBU01 from VCB1 where VCB04 = @VCB04)), 0) )
or (@VAA15 > '''' AND (A1.VAA15 = @VAA15))
))
' where Code='LIS.U.000011'
end
GO
--删除旧记录
--delete from Report_Objects where Code='LIS.U.000023' --删除脚本内容
IF not EXISTS(SELECT * FROM Report_Objects WHERE code='LIS.U.000023')
begin --插入
Declare @Report_Id_1710 int
Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1710 out
Declare @Report_Id_1710_CateID int
Set @Report_Id_1710_CateID = (select id from Report_Categories where code='LIS.5')
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_1710, 'LIS.U.000023', '样本检验(快速查询)', Null, Null, @Report_Id_1710_CateID, 1, 3, 200, 0, Null, '2018-11-08 09:57:02', '2018-11-08 09:57:28', Null, '
BD0DF3C2F50B551CD308DA7374CF20A6declare @UserID int
, @Group int
, @BEY01 int
, @aDate int
, @Keyword varchar(200)
select @UserID = :UserID
, @Group = :Group
, @BEY01 = :BEY01
, @aDate = :aDate
, @Keyword = :Keyword
DECLARE @sql varchar(8000), @sqlOption varchar(8000), @BeginDate varchar(30), @EndDate varchar(30), @LAB42 varchar(20), @LAB09 varchar(20), @VAA05 varchar(64), @BCE03B varchar(10), @BCE03D varchar(10), @LAB22 varchar(30)
select @LAB09 = @Keyword, @LAB42 = @Keyword, @VAA05 = @Keyword, @BCE03B = '''', @LAB22 = @Keyword, @BCE03D = ''''
select @sqlOption = '''', @BeginDate = convert(varchar(10), getdate()-@aDate, 121)+'' 00:00:00'', @EndDate = convert(varchar(10), getdate(), 121) + '' 23:59:59''
set @sql = ''
if OBJECT_ID(''''tempdb..#LAB1'''') is not null drop table #LAB1
select Checked=0, B1.BEZ01, Z1.BEZ03, A.LAB01, A.LAB02, A.VAA01, A.VAA07, A.VAP01, A.BCK01A, A.ABBRP, A.ABBRW, A.LAB09, A.VAA05, ISNULL(A.ABW01, 0) ABW01
, A.LAB12, ISNULL(A.AAU01, ''''Y'''') AAU01, A.LAB14, A.BDP02, A.LAB16, A.BES02, A.LAB18, ISNULL(A.ACF01, 1) ACF01, A.LAB20, ISNULL(A.LAB21, 1) LAB21, A.LAB22
, A.LAB23, A.ABB02, A.BEY01A, A.BEY01B, A.BEW02, A.BCK01B, Y.BEY07, A.LAB30, A.LAB31, ISNULL(A.LAB32, 0) LAB32, A.LAB33
, A.LAB34, A.LAB35, A.LAB36, A.LAB37, A.BCE03, A.BCK01C, ISNULL(A.LAB40, GETDATE()) LAB40, A.VAF01, A.LAB42, A.LAB43, A.BCE03A
, A.BCE03C, A.LAB46, A.BCE01D, A.BCE03D, A.BCE03E, A.LAB50, A.BCE01B, A.BCE03B, A.LAB53, A.LAB54
, A.LAB55, A.LAB56, A.BET02, A.LAB58, A.LAB59, A.LAB60, A.LAB61, A.LAB62, A.LAB63, A.LAB64, A.BEX02
, A.BCK01D, A.BAQ03, isnull(A.LAB68, 0) LAB68, A.LAB69, ISNULL(A.LAB70, 0) LAB70, ISNULL(A.LAB71, 0) LAB71
, A.BCE01F, A.BCE03F, A.LAB74, A.VAA01_64, A.VAA07_64, A.LAB87
, K1.BCK03 AS BCK03A, K2.BCK03 AS BCK03B, K3.BCK03 AS BCK03C, K4.BCK03 AS BCK03D
, U1.AAU02, Y.BEY03 BEY03A, Y.BEY02 BEY02A
, (CASE A.ACF01 WHEN 1 THEN ''''门诊'''' WHEN 2 THEN ''''住院'''' WHEN 4 THEN ''''体检'''' ELSE ''''其他'''' END) ACF03
, ISNULL(H1.BFU01, 0) BFU01, ISNULL(U.BFU03, '''''''') BFU03, H1.LAH08, convert(varchar(2000), '''''''') as TESTAREA, convert(varchar(100), '''''''') as Postfix
, A.LAB77, A.LAB78, A.LAB79, A.LAB80, A.BCE01G, A.BCE03G, A.LAB81, A.BCE01, A.BCE02
into #LAB1
From LAB1 A LEFT JOIN BCK1 K1 ON K1.BCK01 = A.BCK01A
LEFT JOIN BCK1 K2 ON K2.BCK01 = A.BCK01B
LEFT JOIN BCK1 K3 ON K3.BCK01 = A.BCK01C
LEFT JOIN BCK1 K4 ON K4.BCK01 = A.BCK01D
LEFT JOIN BEY1 Y ON Y.BEY01 = A.BEY01A AND ISNULL(Y.BEY52, 0) = 0
LEFT JOIN AAU1 U1 ON U1.AAU01 = A.AAU01
LEFT JOIN LAH1 H1 ON H1.LAB01 = A.LAB01
LEFT JOIN BFU1 U ON U.BFU01 = H1.BFU01
JOIN BFB1 B1 ON B1.BEY01 = A.BEY01A
JOIN BEZ1 Z1 ON Z1.BEZ01 = B1.BEZ01
Where A.LAB20 >= ''''''+@BeginDate+'''''' and A.LAB20 <= ''''''+@EndDate+''''''
''
select @Group = isnull(@Group, 0), @BEY01 = isnull(@BEY01, 0)
if @Group > -1
set @sql = @sql + '' and B1.BEZ01 = '' + CONVERT(VARCHAR, @Group)
if @BEY01 > -1
set @sql = @sql + '' And A.BEY01A = '' + CONVERT(VARCHAR, @BEY01)
if @LAB42 > '''' --条码
begin
set @LAB42 = ''%%'' + @LAB42 + ''%%''
if @sqlOption <> '''' set @sqlOption = @sqlOption + '' or''
set @sqlOption = @sqlOption + '' A.LAB42 like '''''' + @LAB42 + ''''''''
end
if @LAB09 > '''' --病历号
begin
set @LAB09 = ''%%'' + @LAB09 + ''%%''
if @sqlOption <> '''' set @sqlOption = @sqlOption + '' or''
set @sqlOption = @sqlOption + '' A.LAB09 like '''''' + @LAB09 + ''''''''
end
if @LAB22 > '''' --样本号
begin
if @sqlOption <> '''' set @sqlOption = @sqlOption + '' or''
set @sqlOption = @sqlOption + '' A.LAB22 = '''''' + @LAB22 + ''''''''
end
if @VAA05 > '''' --病人
begin
set @VAA05 = ''%%'' + @VAA05 + ''%%''
if @sqlOption <> '''' set @sqlOption = @sqlOption + '' or''
set @sqlOption = @sqlOption + '' (A.VAA05 like '''''' + @VAA05
+ '''''' or A.ABBRP like '''''' + @VAA05
+ '''''' or A.ABBRW like '''''' + @VAA05 + '''''')''
end
if @BCE03B > '''' --审核人
begin
if @sqlOption <> '''' set @sqlOption = @sqlOption + '' or''
set @sqlOption = @sqlOption + '' A.BCE03B = '''''' + @BCE03B + ''''''''
end
if @BCE03D > '''' --检验人
begin
if @sqlOption <> '''' set @sqlOption = @sqlOption + '' or''
set @sqlOption = @sqlOption + '' A.BCE03D = '''''' + @BCE03D + ''''''''
end
if @sqlOption > ''''
set @sql = @sql + '' and ('' + @sqlOption + '')''
SET @sql = @sql + '' SELECT * FROM #LAB1 Order BY BEY01A, Convert(varchar(10),LAB20, 120), LAB23 DROP TABLE #LAB1''
EXEC(@sql)<?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid Head="1" AWS="1"><Sql>DECLARE @BCE01 int
SELECT @BCE01 = <UserID>
Select A.BEZ01, A.BEZ02, A.BEZ03
From BEZ1 A Join BFA1 B On A.BEZ01 = B.BEZ01
Where B.BCE01 = @BCE01</Sql><HD><Ie FD="BEZ02" CA="编码" WD="100"/><Ie FD="BEZ03" CA="名称" WD="100"/></HD></Grid></Configs><?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid Head="1" AWS="1"><Sql>DECLARE @BEZ01 int
SELECT @BEZ01 = <Group>
select -1 BEY01, '''' BEY02, ''所有仪器'' BEY03, 0 ISDEF
union all
select A.BEY01,A.BEY02,A.BEY03, B.ISDEF
from BEY1 A
Join BFB1 B On A.BEY01=B.BEY01
Join BEZ1 C On C.BEZ01=B.BEZ01
where C.BEZ01 = @BEZ01</Sql><HD><Ie FD="BEY02" CA="编码" WD="100"/><Ie FD="BEY03" CA="名称" WD="100"/></HD></Grid></Configs>
', Null, 0, 1, Null, 1, Null)
end
else --更新
begin
update Report_Objects set Config='
BD0DF3C2F50B551CD308DA7374CF20A6declare @UserID int
, @Group int
, @BEY01 int
, @aDate int
, @Keyword varchar(200)
select @UserID = :UserID
, @Group = :Group
, @BEY01 = :BEY01
, @aDate = :aDate
, @Keyword = :Keyword
DECLARE @sql varchar(8000), @sqlOption varchar(8000), @BeginDate varchar(30), @EndDate varchar(30), @LAB42 varchar(20), @LAB09 varchar(20), @VAA05 varchar(64), @BCE03B varchar(10), @BCE03D varchar(10), @LAB22 varchar(30)
select @LAB09 = @Keyword, @LAB42 = @Keyword, @VAA05 = @Keyword, @BCE03B = '''', @LAB22 = @Keyword, @BCE03D = ''''
select @sqlOption = '''', @BeginDate = convert(varchar(10), getdate()-@aDate, 121)+'' 00:00:00'', @EndDate = convert(varchar(10), getdate(), 121) + '' 23:59:59''
set @sql = ''
if OBJECT_ID(''''tempdb..#LAB1'''') is not null drop table #LAB1
select Checked=0, B1.BEZ01, Z1.BEZ03, A.LAB01, A.LAB02, A.VAA01, A.VAA07, A.VAP01, A.BCK01A, A.ABBRP, A.ABBRW, A.LAB09, A.VAA05, ISNULL(A.ABW01, 0) ABW01
, A.LAB12, ISNULL(A.AAU01, ''''Y'''') AAU01, A.LAB14, A.BDP02, A.LAB16, A.BES02, A.LAB18, ISNULL(A.ACF01, 1) ACF01, A.LAB20, ISNULL(A.LAB21, 1) LAB21, A.LAB22
, A.LAB23, A.ABB02, A.BEY01A, A.BEY01B, A.BEW02, A.BCK01B, Y.BEY07, A.LAB30, A.LAB31, ISNULL(A.LAB32, 0) LAB32, A.LAB33
, A.LAB34, A.LAB35, A.LAB36, A.LAB37, A.BCE03, A.BCK01C, ISNULL(A.LAB40, GETDATE()) LAB40, A.VAF01, A.LAB42, A.LAB43, A.BCE03A
, A.BCE03C, A.LAB46, A.BCE01D, A.BCE03D, A.BCE03E, A.LAB50, A.BCE01B, A.BCE03B, A.LAB53, A.LAB54
, A.LAB55, A.LAB56, A.BET02, A.LAB58, A.LAB59, A.LAB60, A.LAB61, A.LAB62, A.LAB63, A.LAB64, A.BEX02
, A.BCK01D, A.BAQ03, isnull(A.LAB68, 0) LAB68, A.LAB69, ISNULL(A.LAB70, 0) LAB70, ISNULL(A.LAB71, 0) LAB71
, A.BCE01F, A.BCE03F, A.LAB74, A.VAA01_64, A.VAA07_64, A.LAB87
, K1.BCK03 AS BCK03A, K2.BCK03 AS BCK03B, K3.BCK03 AS BCK03C, K4.BCK03 AS BCK03D
, U1.AAU02, Y.BEY03 BEY03A, Y.BEY02 BEY02A
, (CASE A.ACF01 WHEN 1 THEN ''''门诊'''' WHEN 2 THEN ''''住院'''' WHEN 4 THEN ''''体检'''' ELSE ''''其他'''' END) ACF03
, ISNULL(H1.BFU01, 0) BFU01, ISNULL(U.BFU03, '''''''') BFU03, H1.LAH08, convert(varchar(2000), '''''''') as TESTAREA, convert(varchar(100), '''''''') as Postfix
, A.LAB77, A.LAB78, A.LAB79, A.LAB80, A.BCE01G, A.BCE03G, A.LAB81, A.BCE01, A.BCE02
into #LAB1
From LAB1 A LEFT JOIN BCK1 K1 ON K1.BCK01 = A.BCK01A
LEFT JOIN BCK1 K2 ON K2.BCK01 = A.BCK01B
LEFT JOIN BCK1 K3 ON K3.BCK01 = A.BCK01C
LEFT JOIN BCK1 K4 ON K4.BCK01 = A.BCK01D
LEFT JOIN BEY1 Y ON Y.BEY01 = A.BEY01A AND ISNULL(Y.BEY52, 0) = 0
LEFT JOIN AAU1 U1 ON U1.AAU01 = A.AAU01
LEFT JOIN LAH1 H1 ON H1.LAB01 = A.LAB01
LEFT JOIN BFU1 U ON U.BFU01 = H1.BFU01
JOIN BFB1 B1 ON B1.BEY01 = A.BEY01A
JOIN BEZ1 Z1 ON Z1.BEZ01 = B1.BEZ01
Where A.LAB20 >= ''''''+@BeginDate+'''''' and A.LAB20 <= ''''''+@EndDate+''''''
''
select @Group = isnull(@Group, 0), @BEY01 = isnull(@BEY01, 0)
if @Group > -1
set @sql = @sql + '' and B1.BEZ01 = '' + CONVERT(VARCHAR, @Group)
if @BEY01 > -1
set @sql = @sql + '' And A.BEY01A = '' + CONVERT(VARCHAR, @BEY01)
if @LAB42 > '''' --条码
begin
set @LAB42 = ''%%'' + @LAB42 + ''%%''
if @sqlOption <> '''' set @sqlOption = @sqlOption + '' or''
set @sqlOption = @sqlOption + '' A.LAB42 like '''''' + @LAB42 + ''''''''
end
if @LAB09 > '''' --病历号
begin
set @LAB09 = ''%%'' + @LAB09 + ''%%''
if @sqlOption <> '''' set @sqlOption = @sqlOption + '' or''
set @sqlOption = @sqlOption + '' A.LAB09 like '''''' + @LAB09 + ''''''''
end
if @LAB22 > '''' --样本号
begin
if @sqlOption <> '''' set @sqlOption = @sqlOption + '' or''
set @sqlOption = @sqlOption + '' A.LAB22 = '''''' + @LAB22 + ''''''''
end
if @VAA05 > '''' --病人
begin
set @VAA05 = ''%%'' + @VAA05 + ''%%''
if @sqlOption <> '''' set @sqlOption = @sqlOption + '' or''
set @sqlOption = @sqlOption + '' (A.VAA05 like '''''' + @VAA05
+ '''''' or A.ABBRP like '''''' + @VAA05
+ '''''' or A.ABBRW like '''''' + @VAA05 + '''''')''
end
if @BCE03B > '''' --审核人
begin
if @sqlOption <> '''' set @sqlOption = @sqlOption + '' or''
set @sqlOption = @sqlOption + '' A.BCE03B = '''''' + @BCE03B + ''''''''
end
if @BCE03D > '''' --检验人
begin
if @sqlOption <> '''' set @sqlOption = @sqlOption + '' or''
set @sqlOption = @sqlOption + '' A.BCE03D = '''''' + @BCE03D + ''''''''
end
if @sqlOption > ''''
set @sql = @sql + '' and ('' + @sqlOption + '')''
SET @sql = @sql + '' SELECT * FROM #LAB1 Order BY BEY01A, Convert(varchar(10),LAB20, 120), LAB23 DROP TABLE #LAB1''
EXEC(@sql)<?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid Head="1" AWS="1"><Sql>DECLARE @BCE01 int
SELECT @BCE01 = <UserID>
Select A.BEZ01, A.BEZ02, A.BEZ03
From BEZ1 A Join BFA1 B On A.BEZ01 = B.BEZ01
Where B.BCE01 = @BCE01</Sql><HD><Ie FD="BEZ02" CA="编码" WD="100"/><Ie FD="BEZ03" CA="名称" WD="100"/></HD></Grid></Configs><?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid Head="1" AWS="1"><Sql>DECLARE @BEZ01 int
SELECT @BEZ01 = <Group>
select -1 BEY01, '''' BEY02, ''所有仪器'' BEY03, 0 ISDEF
union all
select A.BEY01,A.BEY02,A.BEY03, B.ISDEF
from BEY1 A
Join BFB1 B On A.BEY01=B.BEY01
Join BEZ1 C On C.BEZ01=B.BEZ01
where C.BEZ01 = @BEZ01</Sql><HD><Ie FD="BEY02" CA="编码" WD="100"/><Ie FD="BEY03" CA="名称" WD="100"/></HD></Grid></Configs>
' where Code='LIS.U.000023'
end
GO
--删除旧记录
--delete from Report_Objects where Code='LIS.U.000024' --删除脚本内容
IF not EXISTS(SELECT * FROM Report_Objects WHERE code='LIS.U.000024')
begin --插入
Declare @Report_Id_1712 int
Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1712 out
Declare @Report_Id_1712_CateID int
Set @Report_Id_1712_CateID = (select id from Report_Categories where code='LIS.5')
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_1712, 'LIS.U.000024', '查询项目明细', Null, Null, @Report_Id_1712_CateID, 1, 3, 200, 0, Null, '2018-11-09 14:00:55', '2018-11-09 14:00:55', Null, '
3DA74C04D4D99EE2EE0DD04205BC4103declare @strID int
set @strID = :strID
--declare @strID int select @strID = 12705
if object_id(''tempdb..#tmpVBQ1'') is not null drop table #tmpVBQ1
CREATE TABLE #tmpVBQ1(
VBQ01 int, VAF01 int, CBM01 int, VAIFlag varchar(10), BBX01A int, BBX01B int, BBX05A varchar(128), BBX05B varchar(128)
)
declare @ACF01 int
SET @ACF01 = (
SELECT ACF01 FROM VBQ1 WHERE VBQ01 = @strID
UNION
SELECT ACF01 FROM VBQ2 where VBQ01 = @strID
)
if @ACF01 = 4
insert into #tmpVBQ1(VBQ01, VAF01, CBM01, VAIFlag, BBX01A, BBX01B, BBX05A, BBX05B)
select @strID, A.PEJ01, 0, '''', A.BBX01, B.BBX01B, A.PEJ07, C.BBX05
from PEJ1 A with(nolock) join PEN1 B with(nolock) on A.PEJ01 = B.PEJ01 and isnull(A.DFLAG,0) = 0
join BBX1 C on C.BBX01 = B.BBX01B and BDA01 = ''L''
where A.VBQ01 = @strID
if @ACF01 = 1 -----------------------门诊条码 取项目
insert into #tmpVBQ1(VBQ01, VAF01, CBM01, VAIFlag, BBX01A, BBX01B, BBX05A, BBX05B)
--插入非组合项目
SELECT @strID, F.VAF01, F.CBM01, '''', 0 BBX01A, F.BBX01, '''', X.BBX05
FROM VAF1 F with(nolock)
LEFT JOIN BBX1 X ON X.BBX01 = F.BBX01
LEFT JOIN BFC1 C ON C.BBX01 = F.BBX01
WHERE F.VAF51 = @strID AND F.BDA01 = ''L''
AND F.VAF10 >= 8
--AND ((@FiltrateNoCostItem = 0) or ((@FiltrateNoCostItem = 1) and ((F.VAF04 = 1 AND EXISTS(SELECT * FROM VAI1 I1 with(nolock) WHERE I1.CBM01 = F.CBM01 AND I1.VAI18 in (3, 4) AND I1.VAI16 in (1, 2))) OR F.VAF04 = 2) ) )
and ISNULL(X.BBX12, 0) = 0
UNION
--插入组合项目
SELECT DISTINCT @strID, F.VAF01, F.CBM01, '''', D.BBX01A, D.BBX01B, X.BBX05, X1.BBX05
FROM VAF1 F with(nolock)
JOIN BFD1 D ON D.BBX01A = F.BBX01
JOIN BBX1 X ON X.BBX01 = D.BBX01A
JOIN BBX1 X1 on X1.BBX01 = D.BBX01B
WHERE F.VAF51 = @strID AND F.BDA01 = ''L''
AND F.VAF10 >= 8 AND X.BBX12 = 1
--AND ((@FiltrateNoCostItem = 0) or ((@FiltrateNoCostItem = 1) and ((F.VAF04 = 1 AND EXISTS(SELECT * FROM VAI1 I1 with(nolock) WHERE I1.CBM01 = F.CBM01 AND I1.VAI18 in (3, 4) AND I1.VAI16 in (1, 2))) OR F.VAF04 = 2) ) )
if @ACF01 = 2 -------------------------住院条码 取项目
insert into #tmpVBQ1(VBQ01, VAF01, CBM01, VAIFlag, BBX01A, BBX01B, BBX05A, BBX05B)
--插入非组合项目
SELECT @strID, F.VAF01, F.CBM01, '''', 0 BBX01A, F.BBX01, '''', X.BBX05
FROM VAF2 F with(nolock)
LEFT JOIN BBX1 X ON X.BBX01 = F.BBX01
LEFT JOIN BFC1 C ON C.BBX01 = F.BBX01
WHERE F.VAF51 = @strID and F.BDA01 = ''L''
AND F.VAF10 >= 8
--AND ((F.VAF04 = 1 AND EXISTS(SELECT * FROM VAI2 I1 WHERE I1.CBM01 = F.CBM01 AND I1.VAI18 in (3, 4) AND I1.VAI16 in (1, 2))) OR F.VAF04 = 2)
and ISNULL(X.BBX12, 0) = 0
UNION
--插入组合项目
SELECT DISTINCT @strID, F.VAF01, F.CBM01, '''', D.BBX01A, D.BBX01B, X.BBX05, X1.BBX05
FROM VAF2 F with(nolock)
JOIN BFD1 D ON D.BBX01A = F.BBX01
JOIN BBX1 X ON X.BBX01 = D.BBX01A
JOIN BBX1 X1 on X1.BBX01 = D.BBX01B
WHERE F.VAF51 = @strID and F.BDA01 = ''L''
AND F.VAF10 >= 8 AND X.BBX12 = 1
--AND ((F.VAF04 = 1 AND EXISTS(SELECT * FROM VAI2 I1 WHERE I1.CBM01 = F.CBM01 AND I1.VAI18 in (3, 4) AND I1.VAI16 in (1, 2))) OR F.VAF04 = 2)
if @ACF01 = 1
begin
UPDATE M SET M.VAIFlag = ''已结账''
FROM #tmpVBQ1 M
where exists(SELECT * FROM VAI1 I1 with(nolock) WHERE I1.CBM01 = M.CBM01 AND I1.VAI18 in (3, 4) AND I1.VAI16 in (1, 2))
update #tmpVBQ1 set VAIFlag = ''未结账'' where VAIFlag = ''''
end
select VBQ01 申请表ID, VAF01 医嘱ID, BBX05A 组合名称, BBX05B 明细名称, VAIFlag 结账 from #tmpVBQ1
drop table #tmpVBQ1
', Null, 0, 1, Null, 1, Null)
end
else --更新
begin
update Report_Objects set Config='
3DA74C04D4D99EE2EE0DD04205BC4103declare @strID int
set @strID = :strID
--declare @strID int select @strID = 12705
if object_id(''tempdb..#tmpVBQ1'') is not null drop table #tmpVBQ1
CREATE TABLE #tmpVBQ1(
VBQ01 int, VAF01 int, CBM01 int, VAIFlag varchar(10), BBX01A int, BBX01B int, BBX05A varchar(128), BBX05B varchar(128)
)
declare @ACF01 int
SET @ACF01 = (
SELECT ACF01 FROM VBQ1 WHERE VBQ01 = @strID
UNION
SELECT ACF01 FROM VBQ2 where VBQ01 = @strID
)
if @ACF01 = 4
insert into #tmpVBQ1(VBQ01, VAF01, CBM01, VAIFlag, BBX01A, BBX01B, BBX05A, BBX05B)
select @strID, A.PEJ01, 0, '''', A.BBX01, B.BBX01B, A.PEJ07, C.BBX05
from PEJ1 A with(nolock) join PEN1 B with(nolock) on A.PEJ01 = B.PEJ01 and isnull(A.DFLAG,0) = 0
join BBX1 C on C.BBX01 = B.BBX01B and BDA01 = ''L''
where A.VBQ01 = @strID
if @ACF01 = 1 -----------------------门诊条码 取项目
insert into #tmpVBQ1(VBQ01, VAF01, CBM01, VAIFlag, BBX01A, BBX01B, BBX05A, BBX05B)
--插入非组合项目
SELECT @strID, F.VAF01, F.CBM01, '''', 0 BBX01A, F.BBX01, '''', X.BBX05
FROM VAF1 F with(nolock)
LEFT JOIN BBX1 X ON X.BBX01 = F.BBX01
LEFT JOIN BFC1 C ON C.BBX01 = F.BBX01
WHERE F.VAF51 = @strID AND F.BDA01 = ''L''
AND F.VAF10 >= 8
--AND ((@FiltrateNoCostItem = 0) or ((@FiltrateNoCostItem = 1) and ((F.VAF04 = 1 AND EXISTS(SELECT * FROM VAI1 I1 with(nolock) WHERE I1.CBM01 = F.CBM01 AND I1.VAI18 in (3, 4) AND I1.VAI16 in (1, 2))) OR F.VAF04 = 2) ) )
and ISNULL(X.BBX12, 0) = 0
UNION
--插入组合项目
SELECT DISTINCT @strID, F.VAF01, F.CBM01, '''', D.BBX01A, D.BBX01B, X.BBX05, X1.BBX05
FROM VAF1 F with(nolock)
JOIN BFD1 D ON D.BBX01A = F.BBX01
JOIN BBX1 X ON X.BBX01 = D.BBX01A
JOIN BBX1 X1 on X1.BBX01 = D.BBX01B
WHERE F.VAF51 = @strID AND F.BDA01 = ''L''
AND F.VAF10 >= 8 AND X.BBX12 = 1
--AND ((@FiltrateNoCostItem = 0) or ((@FiltrateNoCostItem = 1) and ((F.VAF04 = 1 AND EXISTS(SELECT * FROM VAI1 I1 with(nolock) WHERE I1.CBM01 = F.CBM01 AND I1.VAI18 in (3, 4) AND I1.VAI16 in (1, 2))) OR F.VAF04 = 2) ) )
if @ACF01 = 2 -------------------------住院条码 取项目
insert into #tmpVBQ1(VBQ01, VAF01, CBM01, VAIFlag, BBX01A, BBX01B, BBX05A, BBX05B)
--插入非组合项目
SELECT @strID, F.VAF01, F.CBM01, '''', 0 BBX01A, F.BBX01, '''', X.BBX05
FROM VAF2 F with(nolock)
LEFT JOIN BBX1 X ON X.BBX01 = F.BBX01
LEFT JOIN BFC1 C ON C.BBX01 = F.BBX01
WHERE F.VAF51 = @strID and F.BDA01 = ''L''
AND F.VAF10 >= 8
--AND ((F.VAF04 = 1 AND EXISTS(SELECT * FROM VAI2 I1 WHERE I1.CBM01 = F.CBM01 AND I1.VAI18 in (3, 4) AND I1.VAI16 in (1, 2))) OR F.VAF04 = 2)
and ISNULL(X.BBX12, 0) = 0
UNION
--插入组合项目
SELECT DISTINCT @strID, F.VAF01, F.CBM01, '''', D.BBX01A, D.BBX01B, X.BBX05, X1.BBX05
FROM VAF2 F with(nolock)
JOIN BFD1 D ON D.BBX01A = F.BBX01
JOIN BBX1 X ON X.BBX01 = D.BBX01A
JOIN BBX1 X1 on X1.BBX01 = D.BBX01B
WHERE F.VAF51 = @strID and F.BDA01 = ''L''
AND F.VAF10 >= 8 AND X.BBX12 = 1
--AND ((F.VAF04 = 1 AND EXISTS(SELECT * FROM VAI2 I1 WHERE I1.CBM01 = F.CBM01 AND I1.VAI18 in (3, 4) AND I1.VAI16 in (1, 2))) OR F.VAF04 = 2)
if @ACF01 = 1
begin
UPDATE M SET M.VAIFlag = ''已结账''
FROM #tmpVBQ1 M
where exists(SELECT * FROM VAI1 I1 with(nolock) WHERE I1.CBM01 = M.CBM01 AND I1.VAI18 in (3, 4) AND I1.VAI16 in (1, 2))
update #tmpVBQ1 set VAIFlag = ''未结账'' where VAIFlag = ''''
end
select VBQ01 申请表ID, VAF01 医嘱ID, BBX05A 组合名称, BBX05B 明细名称, VAIFlag 结账 from #tmpVBQ1
drop table #tmpVBQ1
' where Code='LIS.U.000024'
end
GO
--删除旧记录
--delete from Report_Objects where Code='LIS.U.000029' --删除脚本内容
IF not EXISTS(SELECT * FROM Report_Objects WHERE code='LIS.U.000029')
begin --插入
Declare @Report_Id_1815 int
Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1815 out
Declare @Report_Id_1815_CateID int
Set @Report_Id_1815_CateID = (select id from Report_Categories where code='LIS.5')
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_1815, 'LIS.U.000029', '标本登记待检信息查询', Null, Null, @Report_Id_1815_CateID, 1, 3, 200, 0, Null, '2019-05-08 09:21:24', '2019-05-08 09:21:24', Null, '
E473DDFA4EDEC7B02B8C5A4969192B2BDECLARE @BeginDate Datetime,
@EndDate Datetime,
@VAA05 varchar(30)
select @BeginDate = :BeginDate,
@EndDate = :EndDate,
@VAA05 = :VAA05
if object_id(''tempdb..#tmpVBQ1'') is not null drop table #tmpVBQ1
select A.VBQ01, A.ACF01, C.ACF03
, (case when A.VBQ07=0 then ''普通'' else ''紧急'' end) VBQ07
, A.VAA01, B.VAA05, A.BCQ04, A.BCE03A, A.VBQ12, A.VBQ15, A.ABB02, A.VBQ31
into #tmpVBQ1
from VBQ1 A join VAA1 B on A.VAA01 = B.VAA01
join ACF1 C on C.ACF01 = A.ACF01
where A.VBQ12 >= @BeginDate and A.VBQ12 <= @EndDate and (B.VAA05 LIKE ''%''+@VAA05+''%'' or B.ABBRP like ''%''+@VAA05+''%'' or B.ABBRW like ''%''+@VAA05+''%'')
and A.VBQ19 = 0 and A.BDA01 = ''L''
insert into #tmpVBQ1
select A.VBQ01, A.ACF01, C.ACF03
, (case when A.VBQ07=0 then ''普通'' else ''紧急'' end) VBQ07
, A.VAA01, B.VAA05, A.BCQ04, A.BCE03A, A.VBQ12, A.VBQ15, A.ABB02, A.VBQ31
from VBQ2 A join VAA1 B on A.VAA01 = B.VAA01
join ACF1 C on C.ACF01 = A.ACF01
where A.VBQ12 >= @BeginDate and A.VBQ12 <= @EndDate and (B.VAA05 LIKE ''%''+@VAA05+''%'' or B.ABBRP like ''%''+@VAA05+''%'' or B.ABBRW like ''%''+@VAA05+''%'')
and A.VBQ19 = 0 and A.BDA01 = ''L''
select VBQ15, VAA05, ACF03, VBQ01, ACF01, VBQ07 as ''紧急'' --本行不要删减
, case when VBQ07=''普通'' then 0 else 255 end as LineColor --本行颜色，不要改字段名称 0黑色 255红色 16711680蓝色 65535黄色 32768绿色
, VAA01, BCQ04 as ''床位'', BCE03A as ''申请人'', VBQ12 as ''申请时间'', ABB02 as ''样本类型'', VBQ31 as ''项目名称''
from #tmpVBQ1 order by VAA01
drop table #tmpVBQ1
', Null, 1, 1, Null, 1, Null)
end
else --更新
begin
update Report_Objects set Config='
E473DDFA4EDEC7B02B8C5A4969192B2BDECLARE @BeginDate Datetime,
@EndDate Datetime,
@VAA05 varchar(30)
select @BeginDate = :BeginDate,
@EndDate = :EndDate,
@VAA05 = :VAA05
if object_id(''tempdb..#tmpVBQ1'') is not null drop table #tmpVBQ1
select A.VBQ01, A.ACF01, C.ACF03
, (case when A.VBQ07=0 then ''普通'' else ''紧急'' end) VBQ07
, A.VAA01, B.VAA05, A.BCQ04, A.BCE03A, A.VBQ12, A.VBQ15, A.ABB02, A.VBQ31
into #tmpVBQ1
from VBQ1 A join VAA1 B on A.VAA01 = B.VAA01
join ACF1 C on C.ACF01 = A.ACF01
where A.VBQ12 >= @BeginDate and A.VBQ12 <= @EndDate and (B.VAA05 LIKE ''%''+@VAA05+''%'' or B.ABBRP like ''%''+@VAA05+''%'' or B.ABBRW like ''%''+@VAA05+''%'')
and A.VBQ19 = 0 and A.BDA01 = ''L''
insert into #tmpVBQ1
select A.VBQ01, A.ACF01, C.ACF03
, (case when A.VBQ07=0 then ''普通'' else ''紧急'' end) VBQ07
, A.VAA01, B.VAA05, A.BCQ04, A.BCE03A, A.VBQ12, A.VBQ15, A.ABB02, A.VBQ31
from VBQ2 A join VAA1 B on A.VAA01 = B.VAA01
join ACF1 C on C.ACF01 = A.ACF01
where A.VBQ12 >= @BeginDate and A.VBQ12 <= @EndDate and (B.VAA05 LIKE ''%''+@VAA05+''%'' or B.ABBRP like ''%''+@VAA05+''%'' or B.ABBRW like ''%''+@VAA05+''%'')
and A.VBQ19 = 0 and A.BDA01 = ''L''
select VBQ15, VAA05, ACF03, VBQ01, ACF01, VBQ07 as ''紧急'' --本行不要删减
, case when VBQ07=''普通'' then 0 else 255 end as LineColor --本行颜色，不要改字段名称 0黑色 255红色 16711680蓝色 65535黄色 32768绿色
, VAA01, BCQ04 as ''床位'', BCE03A as ''申请人'', VBQ12 as ''申请时间'', ABB02 as ''样本类型'', VBQ31 as ''项目名称''
from #tmpVBQ1 order by VAA01
drop table #tmpVBQ1
' where Code='LIS.U.000029'
end
GO