--删除旧记录 --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, ' 737C021CEE2152C43F007B1F0EDF0E86DECLARE @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 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 , 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=' 737C021CEE2152C43F007B1F0EDF0E86DECLARE @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 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 , 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, ' 6AC5A3BBB8C5DAB03E496E14AB2C732Ddeclare @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) , @BCE01B varchar(10) , @BCE01D 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 , @BCE01B = :BCE01B , @BCE01D = :BCE01D , @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, 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 , 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 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 '' 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 @BCE01B > ''0'' set @sql = @sql + '' and A.BCE01B = '' + @BCE01B if @BCE01D > ''0'' set @sql = @sql + '' and A.BCE01D = '' + @BCE01D 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 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 = &lt;UserID&gt; 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 = &lt;Group&gt; 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=' 6AC5A3BBB8C5DAB03E496E14AB2C732Ddeclare @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) , @BCE01B varchar(10) , @BCE01D 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 , @BCE01B = :BCE01B , @BCE01D = :BCE01D , @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, 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 , 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 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 '' 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 @BCE01B > ''0'' set @sql = @sql + '' and A.BCE01B = '' + @BCE01B if @BCE01D > ''0'' set @sql = @sql + '' and A.BCE01D = '' + @BCE01D 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 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 = &lt;UserID&gt; 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 = &lt;Group&gt; 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.000008' --删除脚本内容 IF not EXISTS(SELECT * FROM Report_Objects WHERE code='LIS.U.000008') begin --插入 Declare @Report_Id_1013 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1013 out Declare @Report_Id_1013_CateID int Set @Report_Id_1013_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_1013, 'LIS.U.000008', '危急值报告', Null, Null, @Report_Id_1013_CateID, 1, 3, 200, 0, Null, '2014-01-02 10:35:42', '2014-01-02 10:35:42', Null, ' 8F2F949C200F020594643C9BDBEA512Edeclare @BCK01 int select @BCK01 =:BCK01 declare @sqlstr varchar(8000) set @sqlstr = ''select * from LTI1 where ISNULL(LTI13, '''''''') = '''''''' '' if isnull(@BCK01, 0) > 0 set @sqlstr = @sqlstr + '' and LTI04 = '' + convert(varchar, @BCK01) set @sqlstr = @sqlstr + '' order by LTI13, LTI11'' EXEC(@sqlstr)<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid Head="1"><Sql>select BCK01, BCK02, BCK03 from BCK1</Sql><HD><Ie FD="BCK02" CA="编码" WD="100"/><Ie FD="BCK03" CA="名称" WD="100"/></HD></Grid></Configs> ', Null, 0, 1, Null, 1, Null) end else --更新 begin update Report_Objects set Config=' 8F2F949C200F020594643C9BDBEA512Edeclare @BCK01 int select @BCK01 =:BCK01 declare @sqlstr varchar(8000) set @sqlstr = ''select * from LTI1 where ISNULL(LTI13, '''''''') = '''''''' '' if isnull(@BCK01, 0) > 0 set @sqlstr = @sqlstr + '' and LTI04 = '' + convert(varchar, @BCK01) set @sqlstr = @sqlstr + '' order by LTI13, LTI11'' EXEC(@sqlstr)<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid Head="1"><Sql>select BCK01, BCK02, BCK03 from BCK1</Sql><HD><Ie FD="BCK02" CA="编码" WD="100"/><Ie FD="BCK03" CA="名称" WD="100"/></HD></Grid></Configs> ' where Code='LIS.U.000008' 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, ' 8CF5D3D04B98F087600125CFAAE935EADeclare @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 采样时间 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=' 8CF5D3D04B98F087600125CFAAE935EADeclare @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 采样时间 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.000010' --删除脚本内容 IF not EXISTS(SELECT * FROM Report_Objects WHERE code='LIS.U.000010') begin --插入 Declare @Report_Id_1057 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1057 out Declare @Report_Id_1057_CateID int Set @Report_Id_1057_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_1057, 'LIS.U.000010', '样本合并打印', '请输入目的样本的样本号', Null, @Report_Id_1057_CateID, 1, 3, 200, 0, Null, '2014-05-22 11:52:06', '2014-05-22 11:52:06', Null, ' 30DE57550E0241AB5578A0CAEFD3DC2Cdeclare @LAB22 varchar(20) , @Date datetime , @BEY01 int select @LAB22=:LAB22 , @Date=:Date , @BEY01=:BEY01 declare @BeginDate datetime, @EndDate datetime, @LAB01 int select @BeginDate = CONVERT(datetime, CONVERT(varchar(10), @Date, 121)+'' 00:00:00'', 121) , @EndDate = CONVERT(datetime, CONVERT(varchar(10), @Date, 121)+'' 23:59:59'', 121) select @LAB01 = (case when LAB01A > 0 then LAB01A else LAB01 end) from LAB1 where (LAB20 between @BeginDate and @EndDate) and BEY01A = @BEY01 and LAB22 = @LAB22 select A.LAB01 ID, A.LAB02 检验单号, A.VAA01 病人ID, A.VAA07 就诊ID, A.VAP01 婴儿ID, A.BCK01A 病人科室ID, KA.BCK03 病人科室 , A.ABBRP 拼音码, A.ABBRW 五笔码, A.LAB09 病历号, A.VAA05 姓名, W.ABW02 性别, A.LAB12 年龄, U.AAU02 年龄单位 , A.LAB14 出生日期, A.BDP02 病人类别, A.LAB16 床号, A.BES02 生理体征, A.LAB18 诊断, A.ACF01 医疗类别, A.LAB20 检验时间 , A.LAB21 检验类型, A.LAB22 标本号, A.LAB23 标本序号, A.ABB02 样本类型, A.BEY01A 仪器ID, A.BEY01B 接收仪器ID , A.BEW02 标本形态, A.BCK01B 执行科室ID, A.BEY07 普通, A.LAB30 合并报告ID, A.LAB31 报告性质, A.LAB32 检验状态 , A.LAB33 报告状态, A.LAB34 报告时间, A.LAB35 打印时间, A.LAB36 打印次数, A.LAB37 申请类型, A.BCE03 送检医生 , A.BCK01C 送检科室ID, A.LAB40 送检时间, A.VAF01 医嘱ID, A.LAB42 样本条码, A.LAB43 采样时间, A.BCE03A 采样人, A.BCE03C 核收人 , A.LAB46 核收时间, A.BCE01D 检验人ID, A.BCE03D 检验人, A.BCE03E 反审人, A.LAB50 培养时间, A.BCE01B 审核人ID, 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 工作单位, A.LAB01A 合并ID, A.LAB69 项目编码 from LAB1 A left join BCK1 KA on KA.BCK01 = A.BCK01A left join ABW1 W on W.ABW01 = A.ABW01 left join AAU1 U on U.AAU01 = A.AAU01 where A.LAB01 = @LAB01declare @LAB22 varchar(20) , @Date datetime , @BEY01 int select @LAB22=:LAB22 , @Date=:Date , @BEY01=:BEY01 declare @LAB01 int/*目的样本*/, @BeginDate datetime, @EndDate datetime select @BeginDate = CONVERT(datetime, CONVERT(varchar(10), @Date, 121)+'' 00:00:00'', 121) , @EndDate = CONVERT(datetime, CONVERT(varchar(10), @Date, 121)+'' 23:59:59'', 121) select @LAB01 = (case when LAB01A > 0 then LAB01A else LAB01 end) from LAB1 where (LAB20 between @BeginDate and @EndDate) and BEY01A = @BEY01 and LAB22 = @LAB22 if OBJECT_ID(''tempdb..#LAB1'') is not null drop table #LAB1 select * into #LAB1 from LAB1 where LAB01 = @LAB01 or LAB01A = @LAB01 select A.LAC01 ID , A.LAB01 检验标本ID , A.BBX01A 诊疗组合项目ID , X.BBX05 组合项目 , A.BBX01 诊疗项目ID , X1.BBX05 明细项目 , A.LAJ01 酶标测试ID , A.BFO01 细菌ID , A.BEY01 仪器ID , A.LAC08 原始定量 , A.LAC09 原始定性 , A.LAC10 报告结果 , A.LAC11 结果标志 , A.LAC12 危急标志 , A.LAC13 参考低值 , A.LAC14 参考高值 , A.LAC15 参考范围 , A.ROWNR 次序 , A.LAC29 异常低值 , A.LAC30 异常高值 , A.VAF01 医嘱ID from LAC1 A left join BBX1 X on X.BBX01 = A.BBX01A left join BBX1 X1 on X1.BBX01 = A.BBX01 where exists(select * from #LAB1 B where B.LAB01 = A.LAB01) drop table #LAB1<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid Head="1" AWS="1"><Sql>select BEY01, BEY03 from BEY1</Sql><HD><Ie FD="BEY01" CA="仪器ID" WD="100"/><Ie FD="BEY03" CA="仪器名称" WD="100"/></HD></Grid></Configs> ', Null, 1, 1, Null, 1, Null) end else --更新 begin update Report_Objects set Config=' 30DE57550E0241AB5578A0CAEFD3DC2Cdeclare @LAB22 varchar(20) , @Date datetime , @BEY01 int select @LAB22=:LAB22 , @Date=:Date , @BEY01=:BEY01 declare @BeginDate datetime, @EndDate datetime, @LAB01 int select @BeginDate = CONVERT(datetime, CONVERT(varchar(10), @Date, 121)+'' 00:00:00'', 121) , @EndDate = CONVERT(datetime, CONVERT(varchar(10), @Date, 121)+'' 23:59:59'', 121) select @LAB01 = (case when LAB01A > 0 then LAB01A else LAB01 end) from LAB1 where (LAB20 between @BeginDate and @EndDate) and BEY01A = @BEY01 and LAB22 = @LAB22 select A.LAB01 ID, A.LAB02 检验单号, A.VAA01 病人ID, A.VAA07 就诊ID, A.VAP01 婴儿ID, A.BCK01A 病人科室ID, KA.BCK03 病人科室 , A.ABBRP 拼音码, A.ABBRW 五笔码, A.LAB09 病历号, A.VAA05 姓名, W.ABW02 性别, A.LAB12 年龄, U.AAU02 年龄单位 , A.LAB14 出生日期, A.BDP02 病人类别, A.LAB16 床号, A.BES02 生理体征, A.LAB18 诊断, A.ACF01 医疗类别, A.LAB20 检验时间 , A.LAB21 检验类型, A.LAB22 标本号, A.LAB23 标本序号, A.ABB02 样本类型, A.BEY01A 仪器ID, A.BEY01B 接收仪器ID , A.BEW02 标本形态, A.BCK01B 执行科室ID, A.BEY07 普通, A.LAB30 合并报告ID, A.LAB31 报告性质, A.LAB32 检验状态 , A.LAB33 报告状态, A.LAB34 报告时间, A.LAB35 打印时间, A.LAB36 打印次数, A.LAB37 申请类型, A.BCE03 送检医生 , A.BCK01C 送检科室ID, A.LAB40 送检时间, A.VAF01 医嘱ID, A.LAB42 样本条码, A.LAB43 采样时间, A.BCE03A 采样人, A.BCE03C 核收人 , A.LAB46 核收时间, A.BCE01D 检验人ID, A.BCE03D 检验人, A.BCE03E 反审人, A.LAB50 培养时间, A.BCE01B 审核人ID, 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 工作单位, A.LAB01A 合并ID, A.LAB69 项目编码 from LAB1 A left join BCK1 KA on KA.BCK01 = A.BCK01A left join ABW1 W on W.ABW01 = A.ABW01 left join AAU1 U on U.AAU01 = A.AAU01 where A.LAB01 = @LAB01declare @LAB22 varchar(20) , @Date datetime , @BEY01 int select @LAB22=:LAB22 , @Date=:Date , @BEY01=:BEY01 declare @LAB01 int/*目的样本*/, @BeginDate datetime, @EndDate datetime select @BeginDate = CONVERT(datetime, CONVERT(varchar(10), @Date, 121)+'' 00:00:00'', 121) , @EndDate = CONVERT(datetime, CONVERT(varchar(10), @Date, 121)+'' 23:59:59'', 121) select @LAB01 = (case when LAB01A > 0 then LAB01A else LAB01 end) from LAB1 where (LAB20 between @BeginDate and @EndDate) and BEY01A = @BEY01 and LAB22 = @LAB22 if OBJECT_ID(''tempdb..#LAB1'') is not null drop table #LAB1 select * into #LAB1 from LAB1 where LAB01 = @LAB01 or LAB01A = @LAB01 select A.LAC01 ID , A.LAB01 检验标本ID , A.BBX01A 诊疗组合项目ID , X.BBX05 组合项目 , A.BBX01 诊疗项目ID , X1.BBX05 明细项目 , A.LAJ01 酶标测试ID , A.BFO01 细菌ID , A.BEY01 仪器ID , A.LAC08 原始定量 , A.LAC09 原始定性 , A.LAC10 报告结果 , A.LAC11 结果标志 , A.LAC12 危急标志 , A.LAC13 参考低值 , A.LAC14 参考高值 , A.LAC15 参考范围 , A.ROWNR 次序 , A.LAC29 异常低值 , A.LAC30 异常高值 , A.VAF01 医嘱ID from LAC1 A left join BBX1 X on X.BBX01 = A.BBX01A left join BBX1 X1 on X1.BBX01 = A.BBX01 where exists(select * from #LAB1 B where B.LAB01 = A.LAB01) drop table #LAB1<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid Head="1" AWS="1"><Sql>select BEY01, BEY03 from BEY1</Sql><HD><Ie FD="BEY01" CA="仪器ID" WD="100"/><Ie FD="BEY03" CA="仪器名称" WD="100"/></HD></Grid></Configs> ' where Code='LIS.U.000010' 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, ' F2DAE677CDF01DB67164B80676397EA2DECLARE @LAA08 varchar(20) , @VAA02 varchar(20) , @VCB04 varchar(20) SELECT @LAA08 = :LAA08 , @VAA02 = :VAA02 , @VCB04 = :VCB04 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 , 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 (@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.VAA02 = @VCB04) ', Null, 0, 1, Null, 1, Null) end else --更新 begin update Report_Objects set Config=' F2DAE677CDF01DB67164B80676397EA2DECLARE @LAA08 varchar(20) , @VAA02 varchar(20) , @VCB04 varchar(20) SELECT @LAA08 = :LAA08 , @VAA02 = :VAA02 , @VCB04 = :VCB04 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 , 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 (@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.VAA02 = @VCB04) ' where Code='LIS.U.000011' end GO