--delete from Report_Objects where CategoryID=43 /*EMR*/ IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.QS001') begin --插入 Declare @Report_Id_906 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_906 out Declare @Report_Id_906_CateID int Set @Report_Id_906_CateID = (select id from Report_Categories where code='EMR.ROOT') 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_906, 'EMR.QS001', '病历完成情况抽查', '针对单个病人', Null, @Report_Id_906_CateID, 1, 3, 600, 0, Null, '2013-08-19 09:50:30', '2013-08-19 09:50:30', Null, '', Null, 1, 1, Null, 1, Null) end else --更新 begin update Report_Objects set Config='' where Code='EMR.QS001' end GO --delete from Report_Objects where CategoryID=71 /*病人病历编辑(内置程序)*/ IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.EMB.000001') begin --插入 Declare @Report_Id_738 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_738 out Declare @Report_Id_738_CateID int Set @Report_Id_738_CateID = (select id from Report_Categories where code='EMR.WIN') 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_738, 'EMR.EMB.000001', '病历浏览窗口代码', '医生站，护士长', Null, @Report_Id_738_CateID, 1, 5, 600, 0, Null, '2012-09-19 10:24:39', '2012-11-01 15:39:51', Null, '{ 注释： VAQ1: 病历记录表 VAA1: 病人信息表 CAF1: 病历时限表 Word.GetVar(表名，字段名, 默认值) } //病历 删除 function ShanChu(): Boolean; begin Result := True; end; //病历 修改 function XiuGai: Boolean; begin if HOUserInfo.AAH01 = 0 then Raise ''修改病历时，聘任级别不能设置为[不限]。''; if HOUserInfo.AAH01 = 8 then Raise ''修改病历时，待聘人员不能操作。''; if not (HOUserInfo.AAH01 in [0..5,9]) then Raise ''无效的《聘任职务》代码:''+HOUserInfo.Other(''AAH01'')+''，如有疑问请与管理员联系。''; case Word.GetVar(''VAQ1'', ''VAQ20'', 0) of 2: raise ''病历文件[''+Word.GetVar(''VAQ1'', ''CAD04'', '''')+'']已经归档.''; 3: raise ''连续病历文件[''+Word.GetVar(''VAQ1'', ''CAD04'', '''')+'']仅用于打印浏览.''; 4: raise ''病历文件[''+Word.GetVar(''VAQ1'', ''CAD04'', '''')+'']已经作废.''; end; Result := True; //返回真，允许修改 end; //病历 审阅 function ShenYue(ival:string): Boolean; begin if HOUserInfo.AAH01 = 0 then Raise ''审阅病历时，聘任级别不能设置为[不限]。''; if HOUserInfo.AAH01 = 8 then Raise ''审阅病历时，待聘人员不能操作。''; if Word.GetVar(''VAQ1'', ''BCE01'', 0) = HOUserInfo.EmpId then begin raise ''病历不能由本人审阅。''; end; if ival =''1'' then begin if (word.getVar(''VAA1'',''VAM27'',0)<>HOUserInfo.EmpName ) and (word.getVar(''VAA1'',''VAM31'',0)<>HouserINfo.EmpName) then raise ''该病历只能由病人的主治医师或主任医师审阅。''; end else begin if Word.GetVar(''VAQ1'', ''VAQ17'', 0) <= HOUserInfo.AAH01 then begin raise ''该病历只能由上级医师审阅。''; end; end; if Word.GetVar(''VAQ1'', ''VAQ20'', 0) = 0 then begin raise ''该病历[''+Word.GetVar(''VAQ1'', ''CAD04'', '''')+'']还未完成，需要[''+Word.GetVar(''VAQ1'', ''BCE03A'', '''')+'']签名后才可以审阅。''; end; Result := True; //返回真，允许审阅 end; //病历归档 function GuiDang(): Boolean; begin Result := True; end; //评分 function PingFen(): Boolean; begin Result := True; end; ', Null, 0, 1, Null, 0, Null) end else --更新 begin update Report_Objects set Config='{ 注释： VAQ1: 病历记录表 VAA1: 病人信息表 CAF1: 病历时限表 Word.GetVar(表名，字段名, 默认值) } //病历 删除 function ShanChu(): Boolean; begin Result := True; end; //病历 修改 function XiuGai: Boolean; begin if HOUserInfo.AAH01 = 0 then Raise ''修改病历时，聘任级别不能设置为[不限]。''; if HOUserInfo.AAH01 = 8 then Raise ''修改病历时，待聘人员不能操作。''; if not (HOUserInfo.AAH01 in [0..5,9]) then Raise ''无效的《聘任职务》代码:''+HOUserInfo.Other(''AAH01'')+''，如有疑问请与管理员联系。''; case Word.GetVar(''VAQ1'', ''VAQ20'', 0) of 2: raise ''病历文件[''+Word.GetVar(''VAQ1'', ''CAD04'', '''')+'']已经归档.''; 3: raise ''连续病历文件[''+Word.GetVar(''VAQ1'', ''CAD04'', '''')+'']仅用于打印浏览.''; 4: raise ''病历文件[''+Word.GetVar(''VAQ1'', ''CAD04'', '''')+'']已经作废.''; end; Result := True; //返回真，允许修改 end; //病历 审阅 function ShenYue(ival:string): Boolean; begin if HOUserInfo.AAH01 = 0 then Raise ''审阅病历时，聘任级别不能设置为[不限]。''; if HOUserInfo.AAH01 = 8 then Raise ''审阅病历时，待聘人员不能操作。''; if Word.GetVar(''VAQ1'', ''BCE01'', 0) = HOUserInfo.EmpId then begin raise ''病历不能由本人审阅。''; end; if ival =''1'' then begin if (word.getVar(''VAA1'',''VAM27'',0)<>HOUserInfo.EmpName ) and (word.getVar(''VAA1'',''VAM31'',0)<>HouserINfo.EmpName) then raise ''该病历只能由病人的主治医师或主任医师审阅。''; end else begin if Word.GetVar(''VAQ1'', ''VAQ17'', 0) <= HOUserInfo.AAH01 then begin raise ''该病历只能由上级医师审阅。''; end; end; if Word.GetVar(''VAQ1'', ''VAQ20'', 0) = 0 then begin raise ''该病历[''+Word.GetVar(''VAQ1'', ''CAD04'', '''')+'']还未完成，需要[''+Word.GetVar(''VAQ1'', ''BCE03A'', '''')+'']签名后才可以审阅。''; end; Result := True; //返回真，允许审阅 end; //病历归档 function GuiDang(): Boolean; begin Result := True; end; //评分 function PingFen(): Boolean; begin Result := True; end; ' where Code='EMR.EMB.000001' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.EMB.000002') begin --插入 Declare @Report_Id_757 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_757 out Declare @Report_Id_757_CateID int Set @Report_Id_757_CateID = (select id from Report_Categories where code='EMR.WIN') 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_757, 'EMR.EMB.000002', '病历审阅窗口代码', 'ViewVCV1 显示审阅日志', Null, @Report_Id_757_CateID, 1, 5, 600, 0, Null, '2012-11-01 15:14:11', '2012-11-01 16:55:12', Null, 'function IsViewVCV1: Boolean; begin //是否显示审阅日志列表 Result := true; end; function IsViewVBT1: Boolean; begin //是否显示修改日志 Result := true; end; function ViewVCV1Filter: String; begin //设置过滤条件 Result := ''VCV07=4''; end;', Null, 0, Null, Null, 0, Null) end else --更新 begin update Report_Objects set Config='function IsViewVCV1: Boolean; begin //是否显示审阅日志列表 Result := true; end; function IsViewVBT1: Boolean; begin //是否显示修改日志 Result := true; end; function ViewVCV1Filter: String; begin //设置过滤条件 Result := ''VCV07=4''; end;' where Code='EMR.EMB.000002' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.PatientInfo.000001') begin --插入 Declare @Report_Id_774 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_774 out Declare @Report_Id_774_CateID int Set @Report_Id_774_CateID = (select id from Report_Categories where code='EMR.WIN') 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_774, 'EMR.PatientInfo.000001', '病人信息（病历自动引用项目)', Null, Null, @Report_Id_774_CateID, 1, 4, 600, 0, Null, '2012-11-28 09:54:17', '2014-06-17 14:26:05', Null, ' F17610F57C0E495ECBEB9DDFC0D51218DECLARE @VAA01 INT DECLARE @VAA07 INT DECLARE @VAP01 INT DECLARE @ACF01 INT SET @VAA01=:VAA01 SET @VAA07=:VAA07 SET @VAP01=:VAP01 SET @ACF01=:ACF01 IF @ACF01=1 --门诊病人信息 BEGIN SELECT /*病人基本信息*/ X.VAA05 AS [姓名], X.VAA06 AS [姓], B.ABW02 AS [性别], case when DATEDIFF(YEAR,VAA12,GETDATE())<=10 then dbo.GetPatAge(dbo.GetAgeDetailFromAge(0,''Y'',VAA12),3,0,0) else CAST(A.VAA10 AS VARCHAR)+C.AAU02 end AS [年龄], X.ABQ02 AS [民族], D.ACK02 AS [婚姻], D.ACK02 AS [婚姻状况], A.VAC16 AS [职业], X.ACC02 AS [国籍], E.ABL02 AS [血型], X.VAA02 as 会员卡号, /*病人附加信息*/ X.VAA12 AS [出生日期], X.VAA20 AS [出生地点], X.VAA14 AS [身份证件], X.VAA15 AS [身份证号码], X.VAA38 AS [学历], A.VAC19 AS [家庭地址], A.VAC20 AS [家庭电话], A.VAC28 AS [单位电话], A.VAC27 AS [工作单位], A.VAC21 AS [联系人姓名], A.AAZ02 AS [联系人关系], A.VAC23 AS [联系人地址], A.VAC24 AS [联系人电话], A.BDP02 AS [病人类别], A.ABC02 AS [病人费别], x.VAA35 AS [移动电话], X.IAK05 AS [社会保障号], F.IAA03 AS [保险机构], X.BDX02 AS [了解途径], X.VAA66 AS [病案号], X.VAA29 AS [住址邮编], X.VAA50 AS [单位邮编], isnull(X.vaa28,'''') AS [户口地址], isnull(X.vaa29,'''') AS [户籍邮编], /*门诊信息*/ A.VAC02 AS [就诊号], A.VAC10 AS [是否急诊], A.VAC35 AS [就诊时间], G.BCK03 AS [就诊科室], X.VAA03 AS [门诊号], A.BCE03B AS [操作员], A.BCE03A AS [接诊医师] FROM VAC1 A with(nolock) JOIN VAA1 X with(nolock) ON A.VAA01=X.VAA01 --病人信息表 LEFT JOIN ABW1 B ON X.ABW01=B.ABW01 --性别 LEFT JOIN AAU1 C ON A.AAU01=C.AAU01 --年龄单位 LEFT JOIN ACK1 D ON A.ACK01=D.ACK01 --婚姻 LEFT JOIN ABL1 E ON X.ABL01=E.ABL01 --血型 LEFT JOIN IAA1 F ON A.IAA01=F.IAA01 --保险机构 LEFT JOIN BCK1 G ON A.BCK01A=G.BCK01 WHERE A.VAC01=@VAA07 AND A.VAA01=@VAA01 END ELSE BEGIN --住院病人信息 SELECT /*病人基本信息*/ X.VAA05 AS [姓名], X.VAA06 AS [姓], B.ABW02 AS [性别], case when DATEDIFF(YEAR,VAA12,GETDATE())<=10 and VAE87>'''' then dbo.GetPatAge(VAE87,3,0,0) else CAST(A.VAE46 AS VARCHAR)+C.AAU02 end AS [年龄], X.ABQ02 AS [民族], D.ACK02 AS [婚姻], D.ACK02 AS [婚姻状况], A.AAT02 AS [职业], A.ACC02 AS [国籍], E.ABL02 AS [血型], X.VAA02 as 会员卡号, /*病人附加信息*/ X.VAA12 AS [出生日期], isnull(VCT1.VCT32, '''') + isnull(VCT1.VCT33, '''') + isnull(VCT1.VCT34, '''') AS [出生地点], X.VAA14 AS [身份证件], X.VAA15 AS [身份证号码], A.AAY02 AS [学历], isnull(A.VAE60, '''') + isnull(A.VAE61, '''') + isnull(VCT1.VCT41, '''') + isnull(A.VAE62, '''') AS [家庭地址], A.VAE63 AS [家庭电话], A.VAE55 AS [单位电话], A.BAQ03 AS [工作单位], A.VAE68 AS [联系人姓名], A.AAZ02 AS [联系人关系], A.VAE70 AS [联系人地址], A.VAE71 AS [联系人电话], A.BDP02 AS [病人类别], A.ABC02 AS [病人费别], A.VAE64 AS [移动电话], X.IAK05 AS [社会保障号], F.IAA03 AS [保险机构], X.BDX02 AS [了解途径], X.VAA66 AS [病案号], VCT1.VCT42 AS [住址邮编], X.VAA50 AS [单位邮编], isnull(VCT1.VCT37,'' '')+isnull(VCT1.VCT38,'' '')+isnull(VCT1.VCT39,'' '')+isnull(VCT1.VCT40,'' '') AS [户口地址], isnull(X.vaa29,'' '') AS [户籍邮编], /*住院信息*/ X.VAA04 AS [住院号], A.VAE19 AS [住院次数], A.VAE26 AS [出院日期], A.ABK02 AS [住院目的], A.VAE11 AS [入院日期], G1.BCK03 AS [入院病区], G2.BCK03 AS [入院科室], H.ABO02 AS [入院情况], P.VBP06 AS [入科时间], A.BCQ04B AS [床位号], G6.BCQ03 AS [病室号], A.BCQ04B AS [住院床位], G3.BCK03 AS [住院病区], G4.BCK03 AS [住院科室], A.BCE03C as [住院医师], O.VAO15 as [入院诊断], O2.VAO15 as [出院诊断], O5.VAO15 as [门诊诊断(病案)], O4.VAO15 as [入院诊断(病案)], o3.VAO15 as [出院诊断(病案)], I.ABV02 AS [出院情况], A.VAE27 AS [住院天数], A.BCE03E AS [编码员], G5.VAP05 AS [婴儿姓名], G5.ABW01 AS [婴儿性别], G5.VAP07 AS [分娩日期] FROM VAE1 A with(nolock) JOIN VAA1 X with(nolock) ON A.VAA01=X.VAA01 --病人信息表 LEFT JOIN ABW1 B ON X.ABW01=B.ABW01 --性别 LEFT JOIN AAU1 C ON A.AAU01=C.AAU01 --年龄单位 LEFT JOIN ACK1 D ON A.ACK01=D.ACK01 --婚姻 LEFT JOIN ABL1 E ON X.ABL01=E.ABL01 --血型 LEFT JOIN IAA1 F ON A.IAA01=F.IAA01 --保险机构 LEFT JOIN BCK1 G1 ON A.BCK01A=G1.BCK01 LEFT JOIN BCK1 G2 ON A.BCK01B=G2.BCK01 LEFT JOIN BCK1 G3 ON A.BCK01C=G3.BCK01 LEFT JOIN BCK1 G4 ON A.BCK01D=G4.BCK01 LEFT JOIN ABO1 H ON A.ABO01=H.ABO01 LEFT JOIN ABV1 I ON A.ABV01=I.ABV01 LEFT JOIN VAO2 O with(nolock) on A.vae01=O.vaa07 and o.acf01=2 and o.vao11=2 and o.vao06=1 LEFT JOIN VAO2 O2 with(nolock) on A.vae01=O2.vaa07 and o2.acf01=2 and o2.vao11=3 and o2.vao06=1 LEFT JOIN VAO2 O3 with(nolock) on A.vae01=O3.vaa07 and O3.acf01=3 and O3.vao11=3 and o3.VAO25 =1 and O3.vao06=1 LEFT JOIN VAO2 O4 with(nolock) on A.vae01=O4.vaa07 and O4.acf01=3 and O4.vao11=2 LEFT JOIN VAO2 O5 with(nolock) on A.vae01=O5.vaa07 and O5.acf01=3 and O5.vao11=1 LEFT JOIN (SELECT VBP06,VAA07 FROM VBP1 WHERE VBP05=3 AND VAA01=@VAA01 AND VAA07=@VAA07) P ON A.VAE01=P.VAA07 LEFT JOIN VCT1 with(nolock) on a.VAE01=VCT1.VAA07 LEFT JOIN VAP1 G5 ON A.VAE01=G5.VAA07 AND G5.VAP01=@VAP01 LEFT JOIN BCQ1 G6 ON A.BCQ04B=G6.BCQ04 AND A.BCK01C=G6.BCK01A WHERE A.VAA01=@VAA01 AND A.VAE01=@VAA07 END1=门诊 2=住院 ', Null, 0, 0, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' F17610F57C0E495ECBEB9DDFC0D51218DECLARE @VAA01 INT DECLARE @VAA07 INT DECLARE @VAP01 INT DECLARE @ACF01 INT SET @VAA01=:VAA01 SET @VAA07=:VAA07 SET @VAP01=:VAP01 SET @ACF01=:ACF01 IF @ACF01=1 --门诊病人信息 BEGIN SELECT /*病人基本信息*/ X.VAA05 AS [姓名], X.VAA06 AS [姓], B.ABW02 AS [性别], case when DATEDIFF(YEAR,VAA12,GETDATE())<=10 then dbo.GetPatAge(dbo.GetAgeDetailFromAge(0,''Y'',VAA12),3,0,0) else CAST(A.VAA10 AS VARCHAR)+C.AAU02 end AS [年龄], X.ABQ02 AS [民族], D.ACK02 AS [婚姻], D.ACK02 AS [婚姻状况], A.VAC16 AS [职业], X.ACC02 AS [国籍], E.ABL02 AS [血型], X.VAA02 as 会员卡号, /*病人附加信息*/ X.VAA12 AS [出生日期], X.VAA20 AS [出生地点], X.VAA14 AS [身份证件], X.VAA15 AS [身份证号码], X.VAA38 AS [学历], A.VAC19 AS [家庭地址], A.VAC20 AS [家庭电话], A.VAC28 AS [单位电话], A.VAC27 AS [工作单位], A.VAC21 AS [联系人姓名], A.AAZ02 AS [联系人关系], A.VAC23 AS [联系人地址], A.VAC24 AS [联系人电话], A.BDP02 AS [病人类别], A.ABC02 AS [病人费别], x.VAA35 AS [移动电话], X.IAK05 AS [社会保障号], F.IAA03 AS [保险机构], X.BDX02 AS [了解途径], X.VAA66 AS [病案号], X.VAA29 AS [住址邮编], X.VAA50 AS [单位邮编], isnull(X.vaa28,'''') AS [户口地址], isnull(X.vaa29,'''') AS [户籍邮编], /*门诊信息*/ A.VAC02 AS [就诊号], A.VAC10 AS [是否急诊], A.VAC35 AS [就诊时间], G.BCK03 AS [就诊科室], X.VAA03 AS [门诊号], A.BCE03B AS [操作员], A.BCE03A AS [接诊医师] FROM VAC1 A with(nolock) JOIN VAA1 X with(nolock) ON A.VAA01=X.VAA01 --病人信息表 LEFT JOIN ABW1 B ON X.ABW01=B.ABW01 --性别 LEFT JOIN AAU1 C ON A.AAU01=C.AAU01 --年龄单位 LEFT JOIN ACK1 D ON A.ACK01=D.ACK01 --婚姻 LEFT JOIN ABL1 E ON X.ABL01=E.ABL01 --血型 LEFT JOIN IAA1 F ON A.IAA01=F.IAA01 --保险机构 LEFT JOIN BCK1 G ON A.BCK01A=G.BCK01 WHERE A.VAC01=@VAA07 AND A.VAA01=@VAA01 END ELSE BEGIN --住院病人信息 SELECT /*病人基本信息*/ X.VAA05 AS [姓名], X.VAA06 AS [姓], B.ABW02 AS [性别], case when DATEDIFF(YEAR,VAA12,GETDATE())<=10 and VAE87>'''' then dbo.GetPatAge(VAE87,3,0,0) else CAST(A.VAE46 AS VARCHAR)+C.AAU02 end AS [年龄], X.ABQ02 AS [民族], D.ACK02 AS [婚姻], D.ACK02 AS [婚姻状况], A.AAT02 AS [职业], A.ACC02 AS [国籍], E.ABL02 AS [血型], X.VAA02 as 会员卡号, /*病人附加信息*/ X.VAA12 AS [出生日期], isnull(VCT1.VCT32, '''') + isnull(VCT1.VCT33, '''') + isnull(VCT1.VCT34, '''') AS [出生地点], X.VAA14 AS [身份证件], X.VAA15 AS [身份证号码], A.AAY02 AS [学历], isnull(A.VAE60, '''') + isnull(A.VAE61, '''') + isnull(VCT1.VCT41, '''') + isnull(A.VAE62, '''') AS [家庭地址], A.VAE63 AS [家庭电话], A.VAE55 AS [单位电话], A.BAQ03 AS [工作单位], A.VAE68 AS [联系人姓名], A.AAZ02 AS [联系人关系], A.VAE70 AS [联系人地址], A.VAE71 AS [联系人电话], A.BDP02 AS [病人类别], A.ABC02 AS [病人费别], A.VAE64 AS [移动电话], X.IAK05 AS [社会保障号], F.IAA03 AS [保险机构], X.BDX02 AS [了解途径], X.VAA66 AS [病案号], VCT1.VCT42 AS [住址邮编], X.VAA50 AS [单位邮编], isnull(VCT1.VCT37,'' '')+isnull(VCT1.VCT38,'' '')+isnull(VCT1.VCT39,'' '')+isnull(VCT1.VCT40,'' '') AS [户口地址], isnull(X.vaa29,'' '') AS [户籍邮编], /*住院信息*/ X.VAA04 AS [住院号], A.VAE19 AS [住院次数], A.VAE26 AS [出院日期], A.ABK02 AS [住院目的], A.VAE11 AS [入院日期], G1.BCK03 AS [入院病区], G2.BCK03 AS [入院科室], H.ABO02 AS [入院情况], P.VBP06 AS [入科时间], A.BCQ04B AS [床位号], G6.BCQ03 AS [病室号], A.BCQ04B AS [住院床位], G3.BCK03 AS [住院病区], G4.BCK03 AS [住院科室], A.BCE03C as [住院医师], O.VAO15 as [入院诊断], O2.VAO15 as [出院诊断], O5.VAO15 as [门诊诊断(病案)], O4.VAO15 as [入院诊断(病案)], o3.VAO15 as [出院诊断(病案)], I.ABV02 AS [出院情况], A.VAE27 AS [住院天数], A.BCE03E AS [编码员], G5.VAP05 AS [婴儿姓名], G5.ABW01 AS [婴儿性别], G5.VAP07 AS [分娩日期] FROM VAE1 A with(nolock) JOIN VAA1 X with(nolock) ON A.VAA01=X.VAA01 --病人信息表 LEFT JOIN ABW1 B ON X.ABW01=B.ABW01 --性别 LEFT JOIN AAU1 C ON A.AAU01=C.AAU01 --年龄单位 LEFT JOIN ACK1 D ON A.ACK01=D.ACK01 --婚姻 LEFT JOIN ABL1 E ON X.ABL01=E.ABL01 --血型 LEFT JOIN IAA1 F ON A.IAA01=F.IAA01 --保险机构 LEFT JOIN BCK1 G1 ON A.BCK01A=G1.BCK01 LEFT JOIN BCK1 G2 ON A.BCK01B=G2.BCK01 LEFT JOIN BCK1 G3 ON A.BCK01C=G3.BCK01 LEFT JOIN BCK1 G4 ON A.BCK01D=G4.BCK01 LEFT JOIN ABO1 H ON A.ABO01=H.ABO01 LEFT JOIN ABV1 I ON A.ABV01=I.ABV01 LEFT JOIN VAO2 O with(nolock) on A.vae01=O.vaa07 and o.acf01=2 and o.vao11=2 and o.vao06=1 LEFT JOIN VAO2 O2 with(nolock) on A.vae01=O2.vaa07 and o2.acf01=2 and o2.vao11=3 and o2.vao06=1 LEFT JOIN VAO2 O3 with(nolock) on A.vae01=O3.vaa07 and O3.acf01=3 and O3.vao11=3 and o3.VAO25 =1 and O3.vao06=1 LEFT JOIN VAO2 O4 with(nolock) on A.vae01=O4.vaa07 and O4.acf01=3 and O4.vao11=2 LEFT JOIN VAO2 O5 with(nolock) on A.vae01=O5.vaa07 and O5.acf01=3 and O5.vao11=1 LEFT JOIN (SELECT VBP06,VAA07 FROM VBP1 WHERE VBP05=3 AND VAA01=@VAA01 AND VAA07=@VAA07) P ON A.VAE01=P.VAA07 LEFT JOIN VCT1 with(nolock) on a.VAE01=VCT1.VAA07 LEFT JOIN VAP1 G5 ON A.VAE01=G5.VAA07 AND G5.VAP01=@VAP01 LEFT JOIN BCQ1 G6 ON A.BCQ04B=G6.BCQ04 AND A.BCK01C=G6.BCK01A WHERE A.VAA01=@VAA01 AND A.VAE01=@VAA07 END1=门诊 2=住院 ' where Code='EMR.PatientInfo.000001' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.EMB.000003') begin --插入 Declare @Report_Id_775 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_775 out Declare @Report_Id_775_CateID int Set @Report_Id_775_CateID = (select id from Report_Categories where code='EMR.WIN') 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_775, 'EMR.EMB.000003', '护理记录窗口代码', Null, Null, @Report_Id_775_CateID, 1, 5, 600, 0, Null, '2012-12-04 22:43:50', '2012-12-04 22:44:16', Null, '//护理记录 新增 AGD=归档 ADataSet 当前护理记录表 function XinZeng(AGD: Boolean; ADataSet: TDataSet): Boolean; begin if AGD then begin Raise ''护理记录已经归档，请取消归档后新增。''; end; Result := true; end; //护理记录 修改 function XiuGai(AGD: Boolean; ADataSet: TDataSet): Boolean; begin if AGD then begin Raise ''护理记录已经归档，请取消归档后修改。''; end; if ActiveConfig.DataSet.Eof then begin Raise ''护理记录为空，请选择要修改的护理记录。''; end; Result := true; end; //护理记录 删除 function ShanChu(AGD: Boolean; ADataSet: TDataSet): Boolean; begin if AGD then begin Raise ''护理记录已经归档，请取消归档后修改。''; end; if ActiveConfig.DataSet.Eof then begin Raise ''护理记录为空，请选择要修改的护理记录。''; end; if ADataSet.FieldByName(''VCF18'').AsInteger = 3 then begin Raise ''当前选择的护理记录已经归档，不能删除。''; end; Result := true; end; ', Null, 0, 1, Null, 0, Null) end else --更新 begin update Report_Objects set Config='//护理记录 新增 AGD=归档 ADataSet 当前护理记录表 function XinZeng(AGD: Boolean; ADataSet: TDataSet): Boolean; begin if AGD then begin Raise ''护理记录已经归档，请取消归档后新增。''; end; Result := true; end; //护理记录 修改 function XiuGai(AGD: Boolean; ADataSet: TDataSet): Boolean; begin if AGD then begin Raise ''护理记录已经归档，请取消归档后修改。''; end; if ActiveConfig.DataSet.Eof then begin Raise ''护理记录为空，请选择要修改的护理记录。''; end; Result := true; end; //护理记录 删除 function ShanChu(AGD: Boolean; ADataSet: TDataSet): Boolean; begin if AGD then begin Raise ''护理记录已经归档，请取消归档后修改。''; end; if ActiveConfig.DataSet.Eof then begin Raise ''护理记录为空，请选择要修改的护理记录。''; end; if ADataSet.FieldByName(''VCF18'').AsInteger = 3 then begin Raise ''当前选择的护理记录已经归档，不能删除。''; end; Result := true; end; ' where Code='EMR.EMB.000003' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.PatientInfo.000002') begin --插入 Declare @Report_Id_776 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_776 out Declare @Report_Id_776_CateID int Set @Report_Id_776_CateID = (select id from Report_Categories where code='EMR.WIN') 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_776, 'EMR.PatientInfo.000002', '病人诊断或报告', Null, Null, @Report_Id_776_CateID, 1, 4, 600, 0, Null, '2012-12-13 10:20:44', '2012-12-13 10:20:44', Null, '', Null, 0, Null, Null, 0, Null) end else --更新 begin update Report_Objects set Config='' where Code='EMR.PatientInfo.000002' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.PatientInfo.000003') begin --插入 Declare @Report_Id_1070 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1070 out Declare @Report_Id_1070_CateID int Set @Report_Id_1070_CateID = (select id from Report_Categories where code='EMR.WIN') 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_1070, 'EMR.PatientInfo.000003', '医嘱数字签名', '医嘱数字签名,查询脚本', Null, @Report_Id_1070_CateID, 1, 4, 600, 0, Null, '2014-07-15 10:41:01', '2014-07-15 10:42:34', Null, ' 75B7D690F739269B304E0376327E213C Declare @lVAE01 int, @lVAF07 int, @lVAF11 tinyint ,@ACF01 int Declare @lImageSign tinyint, @lImageSignNo tinyint, @OneRow varchar(10) set @ACF01=:ACF01 Set @lVAE01 = :VAA07 Set @lVAF07 = 0 Set @lVAF11 = 1 Set @lImageSign = 1 Set @lImageSignNo = 1 Set @OneRow = Isnull((select value from sys_parameters where productid = 9999 and programid = 9999 and paramno = 13),''0'') IF Object_id(''tempdb..#TmpAdvBill'') IS NOT NULL DROP TABLE #TmpAdvBill IF Object_id(''tempdb..#kbmVAF'') IS NOT NULL DROP TABLE #kbmVAF IF Object_id(''tempdb..#kbmCBM'') IS NOT NULL DROP TABLE #kbmCBM IF Object_id(''tempdb..#kbmBAC'') IS NOT NULL DROP TABLE #kbmBAC select a.* into #kbmVAF from VAF1 a where 1 = @ACF01 and a.VAF06 = @lVAE01 union all select a.* from VAF2 a where 2 = @ACF01 and a.VAF06 = @lVAE01 SELECT a.VAF06 登记ID,a.VAF36 开始日期 ,a.VAF22 医嘱内容及规格,d.BBY05 医嘱内容,d.BBY06 规格, a.VAF19 剂量,b.VAF22 用法,b.VAF23 用法说明 ,a.VAF26 频次,a.VAF23 嘱托, a.BCE03A 医师, a.BCE03B 护士, a.BCE03C 校对护士, a.BCE03E 停嘱护士 ,a.BCE03F 停嘱校对护士 , A.BCE03D 停嘱医师,a.VAF45 校对时间,a.VAF47 执行时间,a.VAF47 停嘱时间 ,a.VAF01 医嘱ID,a.CBM01 医嘱单号,a.VAF59 组号,a.ROWNR 成员序号 ,(CONVERT(varchar(20),a.CBM01)+CONVERT(varchar(10),a.VAF59)) as 分组号 ,a.VAF60 滴速 Into #TmpAdvBill FROM #kbmVAF a JOIN #kbmVAF b ON a.VAF01A = b.VAF01 Join BBY1 d on a.BBY01 = d.BBY01 join BAG1 d1 on d.BBY01 = d1.BBY01 join BBX1 d2 on d1.BBX01 = d2.BBX01 join BCK1 a1 on a.BCK01A = a1.BCK01 WHERE a.BDA01 >=''1'' and a.BDA01 <= ''2'' Select m.*,m.医嘱ID as VAF01 from ( SELECT a.登记ID,a.开始日期,a.医嘱内容及规格,a.医嘱内容,a.规格, a.剂量,a.用法,a.用法说明 ,a.频次,a.嘱托, a.医师, a.护士, a.校对护士, a.停嘱护士 ,a.停嘱校对护士 , A.停嘱医师,a.校对时间,a.执行时间,a.停嘱时间 ,a.医嘱ID,a.医嘱单号,a.组号,a.成员序号,a.分组号 ,a.滴速 FROM #TmpAdvBill a join (select 医嘱单号,组号,Max(成员序号) 组成员数 from #TmpAdvBill group by 医嘱单号,组号) b on a.医嘱单号 = b.医嘱单号 and a.组号 = b.组号 Union All SELECT a.VAF06 登记ID,a.VAF36 开始日期 ,a.VAF22 医嘱内容及规格,a.VAF22 医嘱内容,'''' 规格, '''' 剂量 , a.VAF22 用法,a.VAF23 用法说明, a.VAF26 频次,a.VAF23 嘱托, a.BCE03A 医师, a.BCE03B 护士, a.BCE03C 校对护士 , a.BCE03E 停嘱护士,a.BCE03F 停嘱校对护士 , A.BCE03D 停嘱医师,a.VAF45 校对时间,a.VAF47 执行时间,a.VAF47 停嘱时间 ,a.VAF01 医嘱ID,a.CBM01 医嘱单号,a.VAF59 组号,(c.组成员数+1) 序号 ,Cast(a.VAF59 as varchar) 分组号, a.VAF60 滴速 FROM #kbmVAF a join BBX1 b on a.BBX01 = b.BBX01 join (select 医嘱单号,组号,Max(成员序号) 组成员数 from #TmpAdvBill group by 医嘱单号,组号) c on a.CBM01 = c.医嘱单号 and a.VAF59 = c.组号 and c.组成员数 >= 2 join BCK1 a1 on a.BCK01A = a1.BCK01 WHERE @OneRow = ''0'' And a.BDA01 = ''T'' AND a.VAF01A = 0 AND a.VAF32 = 1 UNION all SELECT a.VAF06 登记ID,a.VAF36 开始日期 ,a.VAF22 医嘱内容及规格,a.VAF22 医嘱内容,'''' 规格, a.VAF19 剂量 , b.BBX05 用法,'''' 用法说明, a.VAF26 频次,a.VAF23 嘱托, a.BCE03A 医师, a.BCE03B 护士, a.BCE03C 校对护士 , a.BCE03E 停嘱护士,a.BCE03F 停嘱校对护士 , A.BCE03D 停嘱医师,a.VAF45 校对时间,a.VAF47 执行时间,a.VAF47 停嘱时间 ,a.VAF01 医嘱ID,a.CBM01 医嘱单号,a.VAF59 组号,a.ROWNR 成员序号 ,Cast(a.VAF59 as varchar) 分组号, ''0'' 滴速 FROM #kbmVAF a JOIN BBX1 b ON a.BBX01 = b.BBX01 join BCK1 a1 on a.BCK01A = a1.BCK01 WHERE a.BDA01=''8'' UNION all SELECT a.VAF06 登记ID,a.VAF36 开始日期 ,a.VAF22 医嘱内容及规格,a.VAF22 医嘱内容,'''' 规格, '''' 剂量 , '''' 用法,'''' 用法说明, a.VAF26 频次,a.VAF23 嘱托, a.BCE03A 医师, a.BCE03B 护士, a.BCE03C 校对护士 , a.BCE03E 停嘱护士,a.BCE03F 停嘱校对护士 , A.BCE03D 停嘱医师,a.VAF45 校对时间,a.VAF47 执行时间,a.VAF47 停嘱时间 ,a.VAF01 医嘱ID,a.CBM01 医嘱单号,a.VAF59 组号,a.ROWNR 成员序号 ,Cast(a.VAF59 as varchar) 分组号, ''0'' 滴速 FROM #kbmVAF a join BBX1 b on a.BBX01 = b.BBX01 join BCK1 a1 on a.BCK01A = a1.BCK01 WHERE (a.BDA01=''S'' or a.BDA01=''E'') AND a.VAF01A = 0 UNION ALL SELECT a.VAF06 登记ID,a.VAF36 开始日期 ,a.VAF22 医嘱内容及规格,a.VAF22 医嘱内容,'''' 规格, '''' 剂量 , '''' 用法,'''' 用法说明, a.VAF26 频次,a.VAF23 嘱托, a.BCE03A 医师, a.BCE03B 护士, a.BCE03C 校对护士 , a.BCE03E 停嘱护士,a.BCE03F 停嘱校对护士 , A.BCE03D 停嘱医师,a.VAF45 校对时间,a.VAF47 执行时间,a.VAF47 停嘱时间 ,a.VAF01 医嘱ID,a.CBM01 医嘱单号,a.VAF59 组号,a.ROWNR 序号 ,Cast(a.VAF59 as varchar) 分组号, ''0'' 滴速 FROM #kbmVAF a join BBX1 b on a.BBX01 = b.BBX01 join BCK1 a1 on a.BCK01A = a1.BCK01 WHERE a.BDA01 NOT IN (''1'',''2'',''3'',''8'',''S'',''E'',''0'') AND a.VAF01A = 0 AND a.VAF32 = 0 UNION ALL SELECT a.VAF06 登记ID,a.VAF36 开始日期 ,a.VAF22 医嘱内容及规格,a.VAF22 医嘱内容,'''' 规格, '''' 剂量 , '''' 用法,'''' 用法说明, a.VAF26 频次,a.VAF23 嘱托, a.BCE03A 医师, a.BCE03B 护士, a.BCE03C 校对护士 , a.BCE03E 停嘱护士,a.BCE03F 停嘱校对护士 , A.BCE03D 停嘱医师,a.VAF45 校对时间,a.VAF47 执行时间,a.VAF47 停嘱时间 ,a.VAF01 医嘱ID,a.CBM01 医嘱单号,a.VAF59 组号,a.ROWNR 成员序号 ,Cast(a.VAF59 as varchar) 分组号, ''0'' 滴速 FROM #kbmVAF a join BCK1 a1 on a.BCK01A = a1.BCK01 left join BAC1 s on @lImageSign = @lImageSignNo and a.BCE01A = s.BCE01 WHERE a.BDA01 = ''0'' ) m ORDER BY m.开始日期,m.医嘱单号,m.组号,m.成员序号 IF Object_id(''tempdb..#kbmVAF'') IS NOT NULL DROP TABLE #kbmVAF IF Object_id(''tempdb..#TmpAdvBill'') IS NOT NULL DROP TABLE #TmpAdvBill IF Object_id(''tempdb..#kbmCBM'') IS NOT NULL DROP TABLE #kbmCBM1=门诊 2=住院<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid Head="1"><Sql>declare @acf01 int set @acf01=&lt;ACF01&gt; select * from ( select VAC01 VAA07,VAA03 病历号,VAA05 姓名,VAC35 就诊时间,VAA03,VAA04,VAA05,ABBRP from VAA1 a join VAC1 b on a.VAA01=b.VAA01 where @acf01=1 union all select VAE01 VAA07,VAA04 病历号,VAA05 姓名,VAE11 就诊时间,VAA03,VAA04,VAA05,ABBRP from VAA1 a join VAE1 b on a.VAA01=b.VAA01 where @acf01=2 ) as t</Sql><HD><Ie FD="VAA07" CA="就诊ID" WD="100"/><Ie FD="病历号" CA="病历号" WD="100"/><Ie FD="姓名" CA="姓名" WD="100"/><Ie FD="就诊时间" CA="就诊时间" WD="100"/></HD><QF><Ie FD="VAA03" WE="7"/><Ie FD="VAA04" WE="7"/><Ie FD="VAA05" WE="7"/><Ie FD="abbrp" WE="7"/></QF></Grid></Configs> ', Null, 0, Null, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' 75B7D690F739269B304E0376327E213C Declare @lVAE01 int, @lVAF07 int, @lVAF11 tinyint ,@ACF01 int Declare @lImageSign tinyint, @lImageSignNo tinyint, @OneRow varchar(10) set @ACF01=:ACF01 Set @lVAE01 = :VAA07 Set @lVAF07 = 0 Set @lVAF11 = 1 Set @lImageSign = 1 Set @lImageSignNo = 1 Set @OneRow = Isnull((select value from sys_parameters where productid = 9999 and programid = 9999 and paramno = 13),''0'') IF Object_id(''tempdb..#TmpAdvBill'') IS NOT NULL DROP TABLE #TmpAdvBill IF Object_id(''tempdb..#kbmVAF'') IS NOT NULL DROP TABLE #kbmVAF IF Object_id(''tempdb..#kbmCBM'') IS NOT NULL DROP TABLE #kbmCBM IF Object_id(''tempdb..#kbmBAC'') IS NOT NULL DROP TABLE #kbmBAC select a.* into #kbmVAF from VAF1 a where 1 = @ACF01 and a.VAF06 = @lVAE01 union all select a.* from VAF2 a where 2 = @ACF01 and a.VAF06 = @lVAE01 SELECT a.VAF06 登记ID,a.VAF36 开始日期 ,a.VAF22 医嘱内容及规格,d.BBY05 医嘱内容,d.BBY06 规格, a.VAF19 剂量,b.VAF22 用法,b.VAF23 用法说明 ,a.VAF26 频次,a.VAF23 嘱托, a.BCE03A 医师, a.BCE03B 护士, a.BCE03C 校对护士, a.BCE03E 停嘱护士 ,a.BCE03F 停嘱校对护士 , A.BCE03D 停嘱医师,a.VAF45 校对时间,a.VAF47 执行时间,a.VAF47 停嘱时间 ,a.VAF01 医嘱ID,a.CBM01 医嘱单号,a.VAF59 组号,a.ROWNR 成员序号 ,(CONVERT(varchar(20),a.CBM01)+CONVERT(varchar(10),a.VAF59)) as 分组号 ,a.VAF60 滴速 Into #TmpAdvBill FROM #kbmVAF a JOIN #kbmVAF b ON a.VAF01A = b.VAF01 Join BBY1 d on a.BBY01 = d.BBY01 join BAG1 d1 on d.BBY01 = d1.BBY01 join BBX1 d2 on d1.BBX01 = d2.BBX01 join BCK1 a1 on a.BCK01A = a1.BCK01 WHERE a.BDA01 >=''1'' and a.BDA01 <= ''2'' Select m.*,m.医嘱ID as VAF01 from ( SELECT a.登记ID,a.开始日期,a.医嘱内容及规格,a.医嘱内容,a.规格, a.剂量,a.用法,a.用法说明 ,a.频次,a.嘱托, a.医师, a.护士, a.校对护士, a.停嘱护士 ,a.停嘱校对护士 , A.停嘱医师,a.校对时间,a.执行时间,a.停嘱时间 ,a.医嘱ID,a.医嘱单号,a.组号,a.成员序号,a.分组号 ,a.滴速 FROM #TmpAdvBill a join (select 医嘱单号,组号,Max(成员序号) 组成员数 from #TmpAdvBill group by 医嘱单号,组号) b on a.医嘱单号 = b.医嘱单号 and a.组号 = b.组号 Union All SELECT a.VAF06 登记ID,a.VAF36 开始日期 ,a.VAF22 医嘱内容及规格,a.VAF22 医嘱内容,'''' 规格, '''' 剂量 , a.VAF22 用法,a.VAF23 用法说明, a.VAF26 频次,a.VAF23 嘱托, a.BCE03A 医师, a.BCE03B 护士, a.BCE03C 校对护士 , a.BCE03E 停嘱护士,a.BCE03F 停嘱校对护士 , A.BCE03D 停嘱医师,a.VAF45 校对时间,a.VAF47 执行时间,a.VAF47 停嘱时间 ,a.VAF01 医嘱ID,a.CBM01 医嘱单号,a.VAF59 组号,(c.组成员数+1) 序号 ,Cast(a.VAF59 as varchar) 分组号, a.VAF60 滴速 FROM #kbmVAF a join BBX1 b on a.BBX01 = b.BBX01 join (select 医嘱单号,组号,Max(成员序号) 组成员数 from #TmpAdvBill group by 医嘱单号,组号) c on a.CBM01 = c.医嘱单号 and a.VAF59 = c.组号 and c.组成员数 >= 2 join BCK1 a1 on a.BCK01A = a1.BCK01 WHERE @OneRow = ''0'' And a.BDA01 = ''T'' AND a.VAF01A = 0 AND a.VAF32 = 1 UNION all SELECT a.VAF06 登记ID,a.VAF36 开始日期 ,a.VAF22 医嘱内容及规格,a.VAF22 医嘱内容,'''' 规格, a.VAF19 剂量 , b.BBX05 用法,'''' 用法说明, a.VAF26 频次,a.VAF23 嘱托, a.BCE03A 医师, a.BCE03B 护士, a.BCE03C 校对护士 , a.BCE03E 停嘱护士,a.BCE03F 停嘱校对护士 , A.BCE03D 停嘱医师,a.VAF45 校对时间,a.VAF47 执行时间,a.VAF47 停嘱时间 ,a.VAF01 医嘱ID,a.CBM01 医嘱单号,a.VAF59 组号,a.ROWNR 成员序号 ,Cast(a.VAF59 as varchar) 分组号, ''0'' 滴速 FROM #kbmVAF a JOIN BBX1 b ON a.BBX01 = b.BBX01 join BCK1 a1 on a.BCK01A = a1.BCK01 WHERE a.BDA01=''8'' UNION all SELECT a.VAF06 登记ID,a.VAF36 开始日期 ,a.VAF22 医嘱内容及规格,a.VAF22 医嘱内容,'''' 规格, '''' 剂量 , '''' 用法,'''' 用法说明, a.VAF26 频次,a.VAF23 嘱托, a.BCE03A 医师, a.BCE03B 护士, a.BCE03C 校对护士 , a.BCE03E 停嘱护士,a.BCE03F 停嘱校对护士 , A.BCE03D 停嘱医师,a.VAF45 校对时间,a.VAF47 执行时间,a.VAF47 停嘱时间 ,a.VAF01 医嘱ID,a.CBM01 医嘱单号,a.VAF59 组号,a.ROWNR 成员序号 ,Cast(a.VAF59 as varchar) 分组号, ''0'' 滴速 FROM #kbmVAF a join BBX1 b on a.BBX01 = b.BBX01 join BCK1 a1 on a.BCK01A = a1.BCK01 WHERE (a.BDA01=''S'' or a.BDA01=''E'') AND a.VAF01A = 0 UNION ALL SELECT a.VAF06 登记ID,a.VAF36 开始日期 ,a.VAF22 医嘱内容及规格,a.VAF22 医嘱内容,'''' 规格, '''' 剂量 , '''' 用法,'''' 用法说明, a.VAF26 频次,a.VAF23 嘱托, a.BCE03A 医师, a.BCE03B 护士, a.BCE03C 校对护士 , a.BCE03E 停嘱护士,a.BCE03F 停嘱校对护士 , A.BCE03D 停嘱医师,a.VAF45 校对时间,a.VAF47 执行时间,a.VAF47 停嘱时间 ,a.VAF01 医嘱ID,a.CBM01 医嘱单号,a.VAF59 组号,a.ROWNR 序号 ,Cast(a.VAF59 as varchar) 分组号, ''0'' 滴速 FROM #kbmVAF a join BBX1 b on a.BBX01 = b.BBX01 join BCK1 a1 on a.BCK01A = a1.BCK01 WHERE a.BDA01 NOT IN (''1'',''2'',''3'',''8'',''S'',''E'',''0'') AND a.VAF01A = 0 AND a.VAF32 = 0 UNION ALL SELECT a.VAF06 登记ID,a.VAF36 开始日期 ,a.VAF22 医嘱内容及规格,a.VAF22 医嘱内容,'''' 规格, '''' 剂量 , '''' 用法,'''' 用法说明, a.VAF26 频次,a.VAF23 嘱托, a.BCE03A 医师, a.BCE03B 护士, a.BCE03C 校对护士 , a.BCE03E 停嘱护士,a.BCE03F 停嘱校对护士 , A.BCE03D 停嘱医师,a.VAF45 校对时间,a.VAF47 执行时间,a.VAF47 停嘱时间 ,a.VAF01 医嘱ID,a.CBM01 医嘱单号,a.VAF59 组号,a.ROWNR 成员序号 ,Cast(a.VAF59 as varchar) 分组号, ''0'' 滴速 FROM #kbmVAF a join BCK1 a1 on a.BCK01A = a1.BCK01 left join BAC1 s on @lImageSign = @lImageSignNo and a.BCE01A = s.BCE01 WHERE a.BDA01 = ''0'' ) m ORDER BY m.开始日期,m.医嘱单号,m.组号,m.成员序号 IF Object_id(''tempdb..#kbmVAF'') IS NOT NULL DROP TABLE #kbmVAF IF Object_id(''tempdb..#TmpAdvBill'') IS NOT NULL DROP TABLE #TmpAdvBill IF Object_id(''tempdb..#kbmCBM'') IS NOT NULL DROP TABLE #kbmCBM1=门诊 2=住院<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid Head="1"><Sql>declare @acf01 int set @acf01=&lt;ACF01&gt; select * from ( select VAC01 VAA07,VAA03 病历号,VAA05 姓名,VAC35 就诊时间,VAA03,VAA04,VAA05,ABBRP from VAA1 a join VAC1 b on a.VAA01=b.VAA01 where @acf01=1 union all select VAE01 VAA07,VAA04 病历号,VAA05 姓名,VAE11 就诊时间,VAA03,VAA04,VAA05,ABBRP from VAA1 a join VAE1 b on a.VAA01=b.VAA01 where @acf01=2 ) as t</Sql><HD><Ie FD="VAA07" CA="就诊ID" WD="100"/><Ie FD="病历号" CA="病历号" WD="100"/><Ie FD="姓名" CA="姓名" WD="100"/><Ie FD="就诊时间" CA="就诊时间" WD="100"/></HD><QF><Ie FD="VAA03" WE="7"/><Ie FD="VAA04" WE="7"/><Ie FD="VAA05" WE="7"/><Ie FD="abbrp" WE="7"/></QF></Grid></Configs> ' where Code='EMR.PatientInfo.000003' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.PatientInfo.000004') begin --插入 Declare @Report_Id_1088 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1088 out Declare @Report_Id_1088_CateID int Set @Report_Id_1088_CateID = (select id from Report_Categories where code='EMR.WIN') 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_1088, 'EMR.PatientInfo.000004', '数字签名医嘱查询', '查询起止时间内指定医生的数字签名医嘱', Null, @Report_Id_1088_CateID, 1, 4, 600, 0, Null, '2014-10-16 14:19:37', '2014-10-16 17:15:35', Null, ' 8522AC8CE2D0E41DAF620B9CFDA90774 Declare @lVAE01 int, @lVAF07 int, @lVAF11 tinyint declare @VAF36 datetime, @VAF36A datetime, @BCE01 int Declare @lImageSign tinyint, @lImageSignNo tinyint, @OneRow varchar(10) set @VAF36=:VAF36 set @VAF36A=:VAF36A set @BCE01=:BCE01 Set @lVAF07 = 0 Set @lVAF11 = 1 Set @lImageSign = 1 Set @lImageSignNo = 1 Set @OneRow = Isnull((select value from sys_parameters where productid = 9999 and programid = 9999 and paramno = 13),''0'') IF Object_id(''tempdb..#TmpAdvBill'') IS NOT NULL DROP TABLE #TmpAdvBill IF Object_id(''tempdb..#kbmVAF'') IS NOT NULL DROP TABLE #kbmVAF IF Object_id(''tempdb..#kbmCBM'') IS NOT NULL DROP TABLE #kbmCBM IF Object_id(''tempdb..#kbmBAC'') IS NOT NULL DROP TABLE #kbmBAC select a.* into #kbmVAF from VAF1 a where a.VAF36 between @VAF36 and @VAF36A and a.BCE01A=@BCE01 union all select a.* from VAF2 a where a.VAF36 between @VAF36 and @VAF36A and a.BCE01A=@BCE01 SELECT a.VAF06 登记ID,a.VAF36 开始日期 ,a.VAF22 医嘱内容及规格,d.BBY05 医嘱内容,d.BBY06 规格, a.VAF19 剂量,b.VAF22 用法,b.VAF23 用法说明 ,a.VAF26 频次,a.VAF23 嘱托, a.BCE03A 医师, a.BCE03B 护士, a.BCE03C 校对护士, a.BCE03E 停嘱护士 ,a.BCE03F 停嘱校对护士 , A.BCE03D 停嘱医师,a.VAF45 校对时间,a.VAF47 执行时间,a.VAF47 停嘱时间 ,a.VAF01 医嘱ID,a.CBM01 医嘱单号,a.VAF59 组号,a.ROWNR 成员序号 ,(CONVERT(varchar(20),a.CBM01)+CONVERT(varchar(10),a.VAF59)) as 分组号 ,a.VAF60 滴速 ,a.VAF04 Into #TmpAdvBill FROM #kbmVAF a JOIN #kbmVAF b ON a.VAF01A = b.VAF01 Join BBY1 d on a.BBY01 = d.BBY01 join BAG1 d1 on d.BBY01 = d1.BBY01 join BBX1 d2 on d1.BBX01 = d2.BBX01 join BCK1 a1 on a.BCK01A = a1.BCK01 WHERE a.BDA01 >=''1'' and a.BDA01 <= ''2'' Select m.*,m.医嘱ID as VAF01 ,CASE WHEN ISNULL(ca_vaf1.ca_id,0)=0 AND ISNULL(ca_vaf2.CA_id,0)=0 THEN '''' ELSE ''√'' end AS 是否签名 from ( SELECT a.登记ID,a.开始日期,a.医嘱内容及规格,a.医嘱内容,a.规格, a.剂量,a.用法,a.用法说明 ,a.频次,a.嘱托, a.医师, a.护士, a.校对护士, a.停嘱护士 ,a.停嘱校对护士 , A.停嘱医师,a.校对时间,a.执行时间,a.停嘱时间 ,a.医嘱ID,a.医嘱单号,a.组号,a.成员序号,a.分组号 ,a.滴速,a.VAF04 FROM #TmpAdvBill a join (select 医嘱单号,组号,Max(成员序号) 组成员数 from #TmpAdvBill group by 医嘱单号,组号) b on a.医嘱单号 = b.医嘱单号 and a.组号 = b.组号 Union All SELECT a.VAF06 登记ID,a.VAF36 开始日期 ,a.VAF22 医嘱内容及规格,a.VAF22 医嘱内容,'''' 规格, '''' 剂量 , a.VAF22 用法,a.VAF23 用法说明, a.VAF26 频次,a.VAF23 嘱托, a.BCE03A 医师, a.BCE03B 护士, a.BCE03C 校对护士 , a.BCE03E 停嘱护士,a.BCE03F 停嘱校对护士 , A.BCE03D 停嘱医师,a.VAF45 校对时间,a.VAF47 执行时间,a.VAF47 停嘱时间 ,a.VAF01 医嘱ID,a.CBM01 医嘱单号,a.VAF59 组号,(c.组成员数+1) 序号 ,Cast(a.VAF59 as varchar) 分组号, a.VAF60 滴速,a.VAF04 FROM #kbmVAF a join BBX1 b on a.BBX01 = b.BBX01 join (select 医嘱单号,组号,Max(成员序号) 组成员数 from #TmpAdvBill group by 医嘱单号,组号) c on a.CBM01 = c.医嘱单号 and a.VAF59 = c.组号 and c.组成员数 >= 2 join BCK1 a1 on a.BCK01A = a1.BCK01 WHERE @OneRow = ''0'' And a.BDA01 = ''T'' AND a.VAF01A = 0 AND a.VAF32 = 1 UNION all SELECT a.VAF06 登记ID,a.VAF36 开始日期 ,a.VAF22 医嘱内容及规格,a.VAF22 医嘱内容,'''' 规格, a.VAF19 剂量 , b.BBX05 用法,'''' 用法说明, a.VAF26 频次,a.VAF23 嘱托, a.BCE03A 医师, a.BCE03B 护士, a.BCE03C 校对护士 , a.BCE03E 停嘱护士,a.BCE03F 停嘱校对护士 , A.BCE03D 停嘱医师,a.VAF45 校对时间,a.VAF47 执行时间,a.VAF47 停嘱时间 ,a.VAF01 医嘱ID,a.CBM01 医嘱单号,a.VAF59 组号,a.ROWNR 成员序号 ,Cast(a.VAF59 as varchar) 分组号, ''0'' 滴速,a.VAF04 FROM #kbmVAF a JOIN BBX1 b ON a.BBX01 = b.BBX01 join BCK1 a1 on a.BCK01A = a1.BCK01 WHERE a.BDA01=''8'' UNION all SELECT a.VAF06 登记ID,a.VAF36 开始日期 ,a.VAF22 医嘱内容及规格,a.VAF22 医嘱内容,'''' 规格, '''' 剂量 , '''' 用法,'''' 用法说明, a.VAF26 频次,a.VAF23 嘱托, a.BCE03A 医师, a.BCE03B 护士, a.BCE03C 校对护士 , a.BCE03E 停嘱护士,a.BCE03F 停嘱校对护士 , A.BCE03D 停嘱医师,a.VAF45 校对时间,a.VAF47 执行时间,a.VAF47 停嘱时间 ,a.VAF01 医嘱ID,a.CBM01 医嘱单号,a.VAF59 组号,a.ROWNR 成员序号 ,Cast(a.VAF59 as varchar) 分组号, ''0'' 滴速,a.VAF04 FROM #kbmVAF a join BBX1 b on a.BBX01 = b.BBX01 join BCK1 a1 on a.BCK01A = a1.BCK01 WHERE (a.BDA01=''S'' or a.BDA01=''E'') AND a.VAF01A = 0 UNION ALL SELECT a.VAF06 登记ID,a.VAF36 开始日期 ,a.VAF22 医嘱内容及规格,a.VAF22 医嘱内容,'''' 规格, '''' 剂量 , '''' 用法,'''' 用法说明, a.VAF26 频次,a.VAF23 嘱托, a.BCE03A 医师, a.BCE03B 护士, a.BCE03C 校对护士 , a.BCE03E 停嘱护士,a.BCE03F 停嘱校对护士 , A.BCE03D 停嘱医师,a.VAF45 校对时间,a.VAF47 执行时间,a.VAF47 停嘱时间 ,a.VAF01 医嘱ID,a.CBM01 医嘱单号,a.VAF59 组号,a.ROWNR 序号 ,Cast(a.VAF59 as varchar) 分组号, ''0'' 滴速,a.VAF04 FROM #kbmVAF a join BBX1 b on a.BBX01 = b.BBX01 join BCK1 a1 on a.BCK01A = a1.BCK01 WHERE a.BDA01 NOT IN (''1'',''2'',''3'',''8'',''S'',''E'',''0'') AND a.VAF01A = 0 AND a.VAF32 = 0 UNION ALL SELECT a.VAF06 登记ID,a.VAF36 开始日期 ,a.VAF22 医嘱内容及规格,a.VAF22 医嘱内容,'''' 规格, '''' 剂量 , '''' 用法,'''' 用法说明, a.VAF26 频次,a.VAF23 嘱托, a.BCE03A 医师, a.BCE03B 护士, a.BCE03C 校对护士 , a.BCE03E 停嘱护士,a.BCE03F 停嘱校对护士 , A.BCE03D 停嘱医师,a.VAF45 校对时间,a.VAF47 执行时间,a.VAF47 停嘱时间 ,a.VAF01 医嘱ID,a.CBM01 医嘱单号,a.VAF59 组号,a.ROWNR 成员序号 ,Cast(a.VAF59 as varchar) 分组号, ''0'' 滴速,a.VAF04 FROM #kbmVAF a join BCK1 a1 on a.BCK01A = a1.BCK01 left join BAC1 s on @lImageSign = @lImageSignNo and a.BCE01A = s.BCE01 WHERE a.BDA01 = ''0'' ) m LEFT JOIN ca_VAF1 ON ca_VAF1.VAF01=m.医嘱ID LEFT JOIN ca_VAF2 ON ca_VAF2.VAF01=m.医嘱ID ORDER BY m.开始日期,m.医嘱单号,m.组号,m.成员序号 IF Object_id(''tempdb..#kbmVAF'') IS NOT NULL DROP TABLE #kbmVAF IF Object_id(''tempdb..#TmpAdvBill'') IS NOT NULL DROP TABLE #TmpAdvBill IF Object_id(''tempdb..#kbmCBM'') IS NOT NULL DROP TABLE #kbmCBM <?xml version="1.0"?> <Configs Ver="1"><Win SZ="1" NL="1"/><Grid UseW="1" FFUse="1"><Sql>select BCE01,BCE03,abbrp,abbrw from bce1 WHERE BCE41=1</Sql><QF><Ie FD="abbrp" WE="7" CT="2"/><Ie FD="abbrw" WE="7" CT="1"/><Ie FD="bce03" WE="7"/></QF></Grid></Configs> ', Null, 0, Null, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' 8522AC8CE2D0E41DAF620B9CFDA90774 Declare @lVAE01 int, @lVAF07 int, @lVAF11 tinyint declare @VAF36 datetime, @VAF36A datetime, @BCE01 int Declare @lImageSign tinyint, @lImageSignNo tinyint, @OneRow varchar(10) set @VAF36=:VAF36 set @VAF36A=:VAF36A set @BCE01=:BCE01 Set @lVAF07 = 0 Set @lVAF11 = 1 Set @lImageSign = 1 Set @lImageSignNo = 1 Set @OneRow = Isnull((select value from sys_parameters where productid = 9999 and programid = 9999 and paramno = 13),''0'') IF Object_id(''tempdb..#TmpAdvBill'') IS NOT NULL DROP TABLE #TmpAdvBill IF Object_id(''tempdb..#kbmVAF'') IS NOT NULL DROP TABLE #kbmVAF IF Object_id(''tempdb..#kbmCBM'') IS NOT NULL DROP TABLE #kbmCBM IF Object_id(''tempdb..#kbmBAC'') IS NOT NULL DROP TABLE #kbmBAC select a.* into #kbmVAF from VAF1 a where a.VAF36 between @VAF36 and @VAF36A and a.BCE01A=@BCE01 union all select a.* from VAF2 a where a.VAF36 between @VAF36 and @VAF36A and a.BCE01A=@BCE01 SELECT a.VAF06 登记ID,a.VAF36 开始日期 ,a.VAF22 医嘱内容及规格,d.BBY05 医嘱内容,d.BBY06 规格, a.VAF19 剂量,b.VAF22 用法,b.VAF23 用法说明 ,a.VAF26 频次,a.VAF23 嘱托, a.BCE03A 医师, a.BCE03B 护士, a.BCE03C 校对护士, a.BCE03E 停嘱护士 ,a.BCE03F 停嘱校对护士 , A.BCE03D 停嘱医师,a.VAF45 校对时间,a.VAF47 执行时间,a.VAF47 停嘱时间 ,a.VAF01 医嘱ID,a.CBM01 医嘱单号,a.VAF59 组号,a.ROWNR 成员序号 ,(CONVERT(varchar(20),a.CBM01)+CONVERT(varchar(10),a.VAF59)) as 分组号 ,a.VAF60 滴速 ,a.VAF04 Into #TmpAdvBill FROM #kbmVAF a JOIN #kbmVAF b ON a.VAF01A = b.VAF01 Join BBY1 d on a.BBY01 = d.BBY01 join BAG1 d1 on d.BBY01 = d1.BBY01 join BBX1 d2 on d1.BBX01 = d2.BBX01 join BCK1 a1 on a.BCK01A = a1.BCK01 WHERE a.BDA01 >=''1'' and a.BDA01 <= ''2'' Select m.*,m.医嘱ID as VAF01 ,CASE WHEN ISNULL(ca_vaf1.ca_id,0)=0 AND ISNULL(ca_vaf2.CA_id,0)=0 THEN '''' ELSE ''√'' end AS 是否签名 from ( SELECT a.登记ID,a.开始日期,a.医嘱内容及规格,a.医嘱内容,a.规格, a.剂量,a.用法,a.用法说明 ,a.频次,a.嘱托, a.医师, a.护士, a.校对护士, a.停嘱护士 ,a.停嘱校对护士 , A.停嘱医师,a.校对时间,a.执行时间,a.停嘱时间 ,a.医嘱ID,a.医嘱单号,a.组号,a.成员序号,a.分组号 ,a.滴速,a.VAF04 FROM #TmpAdvBill a join (select 医嘱单号,组号,Max(成员序号) 组成员数 from #TmpAdvBill group by 医嘱单号,组号) b on a.医嘱单号 = b.医嘱单号 and a.组号 = b.组号 Union All SELECT a.VAF06 登记ID,a.VAF36 开始日期 ,a.VAF22 医嘱内容及规格,a.VAF22 医嘱内容,'''' 规格, '''' 剂量 , a.VAF22 用法,a.VAF23 用法说明, a.VAF26 频次,a.VAF23 嘱托, a.BCE03A 医师, a.BCE03B 护士, a.BCE03C 校对护士 , a.BCE03E 停嘱护士,a.BCE03F 停嘱校对护士 , A.BCE03D 停嘱医师,a.VAF45 校对时间,a.VAF47 执行时间,a.VAF47 停嘱时间 ,a.VAF01 医嘱ID,a.CBM01 医嘱单号,a.VAF59 组号,(c.组成员数+1) 序号 ,Cast(a.VAF59 as varchar) 分组号, a.VAF60 滴速,a.VAF04 FROM #kbmVAF a join BBX1 b on a.BBX01 = b.BBX01 join (select 医嘱单号,组号,Max(成员序号) 组成员数 from #TmpAdvBill group by 医嘱单号,组号) c on a.CBM01 = c.医嘱单号 and a.VAF59 = c.组号 and c.组成员数 >= 2 join BCK1 a1 on a.BCK01A = a1.BCK01 WHERE @OneRow = ''0'' And a.BDA01 = ''T'' AND a.VAF01A = 0 AND a.VAF32 = 1 UNION all SELECT a.VAF06 登记ID,a.VAF36 开始日期 ,a.VAF22 医嘱内容及规格,a.VAF22 医嘱内容,'''' 规格, a.VAF19 剂量 , b.BBX05 用法,'''' 用法说明, a.VAF26 频次,a.VAF23 嘱托, a.BCE03A 医师, a.BCE03B 护士, a.BCE03C 校对护士 , a.BCE03E 停嘱护士,a.BCE03F 停嘱校对护士 , A.BCE03D 停嘱医师,a.VAF45 校对时间,a.VAF47 执行时间,a.VAF47 停嘱时间 ,a.VAF01 医嘱ID,a.CBM01 医嘱单号,a.VAF59 组号,a.ROWNR 成员序号 ,Cast(a.VAF59 as varchar) 分组号, ''0'' 滴速,a.VAF04 FROM #kbmVAF a JOIN BBX1 b ON a.BBX01 = b.BBX01 join BCK1 a1 on a.BCK01A = a1.BCK01 WHERE a.BDA01=''8'' UNION all SELECT a.VAF06 登记ID,a.VAF36 开始日期 ,a.VAF22 医嘱内容及规格,a.VAF22 医嘱内容,'''' 规格, '''' 剂量 , '''' 用法,'''' 用法说明, a.VAF26 频次,a.VAF23 嘱托, a.BCE03A 医师, a.BCE03B 护士, a.BCE03C 校对护士 , a.BCE03E 停嘱护士,a.BCE03F 停嘱校对护士 , A.BCE03D 停嘱医师,a.VAF45 校对时间,a.VAF47 执行时间,a.VAF47 停嘱时间 ,a.VAF01 医嘱ID,a.CBM01 医嘱单号,a.VAF59 组号,a.ROWNR 成员序号 ,Cast(a.VAF59 as varchar) 分组号, ''0'' 滴速,a.VAF04 FROM #kbmVAF a join BBX1 b on a.BBX01 = b.BBX01 join BCK1 a1 on a.BCK01A = a1.BCK01 WHERE (a.BDA01=''S'' or a.BDA01=''E'') AND a.VAF01A = 0 UNION ALL SELECT a.VAF06 登记ID,a.VAF36 开始日期 ,a.VAF22 医嘱内容及规格,a.VAF22 医嘱内容,'''' 规格, '''' 剂量 , '''' 用法,'''' 用法说明, a.VAF26 频次,a.VAF23 嘱托, a.BCE03A 医师, a.BCE03B 护士, a.BCE03C 校对护士 , a.BCE03E 停嘱护士,a.BCE03F 停嘱校对护士 , A.BCE03D 停嘱医师,a.VAF45 校对时间,a.VAF47 执行时间,a.VAF47 停嘱时间 ,a.VAF01 医嘱ID,a.CBM01 医嘱单号,a.VAF59 组号,a.ROWNR 序号 ,Cast(a.VAF59 as varchar) 分组号, ''0'' 滴速,a.VAF04 FROM #kbmVAF a join BBX1 b on a.BBX01 = b.BBX01 join BCK1 a1 on a.BCK01A = a1.BCK01 WHERE a.BDA01 NOT IN (''1'',''2'',''3'',''8'',''S'',''E'',''0'') AND a.VAF01A = 0 AND a.VAF32 = 0 UNION ALL SELECT a.VAF06 登记ID,a.VAF36 开始日期 ,a.VAF22 医嘱内容及规格,a.VAF22 医嘱内容,'''' 规格, '''' 剂量 , '''' 用法,'''' 用法说明, a.VAF26 频次,a.VAF23 嘱托, a.BCE03A 医师, a.BCE03B 护士, a.BCE03C 校对护士 , a.BCE03E 停嘱护士,a.BCE03F 停嘱校对护士 , A.BCE03D 停嘱医师,a.VAF45 校对时间,a.VAF47 执行时间,a.VAF47 停嘱时间 ,a.VAF01 医嘱ID,a.CBM01 医嘱单号,a.VAF59 组号,a.ROWNR 成员序号 ,Cast(a.VAF59 as varchar) 分组号, ''0'' 滴速,a.VAF04 FROM #kbmVAF a join BCK1 a1 on a.BCK01A = a1.BCK01 left join BAC1 s on @lImageSign = @lImageSignNo and a.BCE01A = s.BCE01 WHERE a.BDA01 = ''0'' ) m LEFT JOIN ca_VAF1 ON ca_VAF1.VAF01=m.医嘱ID LEFT JOIN ca_VAF2 ON ca_VAF2.VAF01=m.医嘱ID ORDER BY m.开始日期,m.医嘱单号,m.组号,m.成员序号 IF Object_id(''tempdb..#kbmVAF'') IS NOT NULL DROP TABLE #kbmVAF IF Object_id(''tempdb..#TmpAdvBill'') IS NOT NULL DROP TABLE #TmpAdvBill IF Object_id(''tempdb..#kbmCBM'') IS NOT NULL DROP TABLE #kbmCBM <?xml version="1.0"?> <Configs Ver="1"><Win SZ="1" NL="1"/><Grid UseW="1" FFUse="1"><Sql>select BCE01,BCE03,abbrp,abbrw from bce1 WHERE BCE41=1</Sql><QF><Ie FD="abbrp" WE="7" CT="2"/><Ie FD="abbrw" WE="7" CT="1"/><Ie FD="bce03" WE="7"/></QF></Grid></Configs> ' where Code='EMR.PatientInfo.000004' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.PatientInfo.000005') begin --插入 Declare @Report_Id_1090 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1090 out Declare @Report_Id_1090_CateID int Set @Report_Id_1090_CateID = (select id from Report_Categories where code='EMR.WIN') 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_1090, 'EMR.PatientInfo.000005', '样本检验数字签名', Null, Null, @Report_Id_1090_CateID, 1, 4, 600, 0, Null, '2014-10-28 16:46:00', '2014-10-28 16:46:44', Null, ' 1FAAF63EC4EB1B08D1F6804198240021Declare @BeginDate Datetime , @EndDate Datetime select @BeginDate=:BeginDate , @EndDate=:EndDate select A.LAB01 , ''检验人:'' + A.BCE03D + '', 检验时间:'' + CONVERT(varchar(30), A.LAB20, 120) + '', 样本号:'' + A.LAB22 as Date1 , convert(varchar(8000), ''送检医生:'' + A.BCE03 + '' 病人:'' + A.VAA05) as Date2 , Case when isnull(B.CA_ID, 0) = 0 then '''' else ''√'' end 是否签名 from V_LAB_FULL A left join CA_LAB1 B on A.LAB01 = B.LAB01 where A.LAB32 = 3 and A.LAB53 >= @BeginDate and A.LAB53 <= @EndDate ', Null, 0, Null, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' 1FAAF63EC4EB1B08D1F6804198240021Declare @BeginDate Datetime , @EndDate Datetime select @BeginDate=:BeginDate , @EndDate=:EndDate select A.LAB01 , ''检验人:'' + A.BCE03D + '', 检验时间:'' + CONVERT(varchar(30), A.LAB20, 120) + '', 样本号:'' + A.LAB22 as Date1 , convert(varchar(8000), ''送检医生:'' + A.BCE03 + '' 病人:'' + A.VAA05) as Date2 , Case when isnull(B.CA_ID, 0) = 0 then '''' else ''√'' end 是否签名 from V_LAB_FULL A left join CA_LAB1 B on A.LAB01 = B.LAB01 where A.LAB32 = 3 and A.LAB53 >= @BeginDate and A.LAB53 <= @EndDate ' where Code='EMR.PatientInfo.000005' end GO --delete from Report_Objects where CategoryID=74 /*病人病历浏览*/ IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.Brow.000003') begin --插入 Declare @Report_Id_770 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_770 out Declare @Report_Id_770_CateID int Set @Report_Id_770_CateID = (select id from Report_Categories where code='EMR.Brow') 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_770, 'EMR.Brow.000003', '病历导出工具', Null, Null, @Report_Id_770_CateID, 1, 4, 600, 0, Null, '2012-12-27 20:39:31', '2012-12-27 20:39:31', Null, ' E5965B881FAA9F063BB429D22DC317CFDECLARE @VAE01 INT, @VAA01 INT SET @VAA01=:VAA01 SET @VAE01=:VAE01 SELECT 1 Checked, VAQ01, VAA01, VAA07, VAP01, VAQ01A, CAD01, CAD04, BCE03A, BCE03B, BCE03C, PNRCP, VAQ16, 1 PRINTCount, ACF01, VAQ30, VAQ31 FROM VAQ1 WHERE VAA01=@VAA01 AND (VAA07=@VAE01 or @VAE01=0) AND (VAQ21=0 or VAQ21=2) AND VAQ20<>4<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid RecNo="1" Head="1"><Sql>SELECT VAA01, VAA02, VAA03, VAA04, VAA05 FROM VAA1</Sql><HD><Ie FD="VAA03" CA="门诊号" WD="100"/><Ie FD="VAA04" CA="住院号" WD="100"/><Ie FD="VAA05" CA="姓名" WD="100"/></HD><QF><Ie FD="VAA04" WE="7"/><Ie FD="VAA05" WE="7"/><Ie FD="ABBRP" WE="7" CT="2"/><Ie FD="ABBRW" WE="7" CT="1"/><Ie FD="VAA03" WE="7"/></QF></Grid></Configs> <?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid RecNo="1" Head="1"><Sql>SELECT VAE01, VAE02, VAE11, VAE19 FROM VAE1 WHERE VAA01=&lt;VAA01&gt;</Sql><HD><Ie FD="VAE02" CA="登记号" WD="100"/><Ie FD="VAE11" CA="入院日期" WD="100"/><Ie FD="VAE19" CA="住院次数" WD="100"/></HD></Grid></Configs> ', Null, 0, 1, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' E5965B881FAA9F063BB429D22DC317CFDECLARE @VAE01 INT, @VAA01 INT SET @VAA01=:VAA01 SET @VAE01=:VAE01 SELECT 1 Checked, VAQ01, VAA01, VAA07, VAP01, VAQ01A, CAD01, CAD04, BCE03A, BCE03B, BCE03C, PNRCP, VAQ16, 1 PRINTCount, ACF01, VAQ30, VAQ31 FROM VAQ1 WHERE VAA01=@VAA01 AND (VAA07=@VAE01 or @VAE01=0) AND (VAQ21=0 or VAQ21=2) AND VAQ20<>4<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid RecNo="1" Head="1"><Sql>SELECT VAA01, VAA02, VAA03, VAA04, VAA05 FROM VAA1</Sql><HD><Ie FD="VAA03" CA="门诊号" WD="100"/><Ie FD="VAA04" CA="住院号" WD="100"/><Ie FD="VAA05" CA="姓名" WD="100"/></HD><QF><Ie FD="VAA04" WE="7"/><Ie FD="VAA05" WE="7"/><Ie FD="ABBRP" WE="7" CT="2"/><Ie FD="ABBRW" WE="7" CT="1"/><Ie FD="VAA03" WE="7"/></QF></Grid></Configs> <?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid RecNo="1" Head="1"><Sql>SELECT VAE01, VAE02, VAE11, VAE19 FROM VAE1 WHERE VAA01=&lt;VAA01&gt;</Sql><HD><Ie FD="VAE02" CA="登记号" WD="100"/><Ie FD="VAE11" CA="入院日期" WD="100"/><Ie FD="VAE19" CA="住院次数" WD="100"/></HD></Grid></Configs> ' where Code='EMR.Brow.000003' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.Brow.000001') begin --插入 Declare @Report_Id_777 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_777 out Declare @Report_Id_777_CateID int Set @Report_Id_777_CateID = (select id from Report_Categories where code='EMR.Brow') 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_777, 'EMR.Brow.000001', '在院病人', '在院病人查询，可以通过住院信息 或 病人信息查询。', Null, @Report_Id_777_CateID, 1, 4, 600, 0, Null, '2012-11-08 09:18:37', '2012-11-08 11:02:27', Null, ' 6179BE5E4B95ECB63B697C5DDC50630CDECLARE @BCQ04 varchar(24) DECLARE @VAA04 varchar(24) DECLARE @VAA01 int DECLARE @Cate int set @BCQ04 = :BCQ04 set @VAA04 = :VAA04 set @VAA01 = :VAA01 set @Cate = :Cate DECLARE @SQL VARCHAR(1024) SET @SQL='' SELECT A.VAA01, A.VAE01 AS VAA07, ISNULL(B.VAA04, ''''无'''')+''''/''''+ISNULL(B.VAA04, ''''无'''') as NO, B.VAA05, C.BCK03 AS CateName FROM VAE1 A JOIN VAA1 B ON A.VAA01=B.VAA01'' IF @Cate=0 BEGIN SET @SQL = @SQL + CHAR(13)+''JOIN BCK1 C ON A.BCK01D=C.BCK01'' END ELSE BEGIN SET @SQL = @SQL + CHAR(13)+''JOIN BCK1 C ON A.BCK01C=C.BCK01'' END SET @SQL = @SQL + CHAR(13)+''WHERE A.VAA01=''+Cast(@VAA01 as VARCHAR) EXEC(@SQL)<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid><Sql>SELECT VAA01, VAA05 FROM VAA1</Sql><HD><Ie FD="VAA05" CA="姓名" WD="100" SZ="1"/></HD><QF><Ie FD="VAA05" WE="7"/><Ie FD="ABBRP" WE="7" CT="2"/><Ie FD="ABBRW" WE="7" CT="1"/></QF></Grid></Configs>0=根据科室分类 1=根据病区分类 ', Null, 0, 1, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' 6179BE5E4B95ECB63B697C5DDC50630CDECLARE @BCQ04 varchar(24) DECLARE @VAA04 varchar(24) DECLARE @VAA01 int DECLARE @Cate int set @BCQ04 = :BCQ04 set @VAA04 = :VAA04 set @VAA01 = :VAA01 set @Cate = :Cate DECLARE @SQL VARCHAR(1024) SET @SQL='' SELECT A.VAA01, A.VAE01 AS VAA07, ISNULL(B.VAA04, ''''无'''')+''''/''''+ISNULL(B.VAA04, ''''无'''') as NO, B.VAA05, C.BCK03 AS CateName FROM VAE1 A JOIN VAA1 B ON A.VAA01=B.VAA01'' IF @Cate=0 BEGIN SET @SQL = @SQL + CHAR(13)+''JOIN BCK1 C ON A.BCK01D=C.BCK01'' END ELSE BEGIN SET @SQL = @SQL + CHAR(13)+''JOIN BCK1 C ON A.BCK01C=C.BCK01'' END SET @SQL = @SQL + CHAR(13)+''WHERE A.VAA01=''+Cast(@VAA01 as VARCHAR) EXEC(@SQL)<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid><Sql>SELECT VAA01, VAA05 FROM VAA1</Sql><HD><Ie FD="VAA05" CA="姓名" WD="100" SZ="1"/></HD><QF><Ie FD="VAA05" WE="7"/><Ie FD="ABBRP" WE="7" CT="2"/><Ie FD="ABBRW" WE="7" CT="1"/></QF></Grid></Configs>0=根据科室分类 1=根据病区分类 ' where Code='EMR.Brow.000001' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.Brow.000002') begin --插入 Declare @Report_Id_778 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_778 out Declare @Report_Id_778_CateID int Set @Report_Id_778_CateID = (select id from Report_Categories where code='EMR.Brow') 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_778, 'EMR.Brow.000002', '出院病人', '出院病人查询', Null, @Report_Id_778_CateID, 1, 4, 600, 0, Null, '2012-11-08 10:45:45', '2012-11-08 10:45:45', Null, '', Null, 0, 1, Null, 0, Null) end else --更新 begin update Report_Objects set Config='' where Code='EMR.Brow.000002' end GO --delete from Report_Objects where CategoryID=75 /*病历管理报表*/ IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.Report.000001') begin --插入 Declare @Report_Id_779 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_779 out Declare @Report_Id_779_CateID int Set @Report_Id_779_CateID = (select id from Report_Categories where code='EMR.Reports') 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_779, 'EMR.Report.000001', '病人病历修改日志', '病人病历修改日志查询', Null, @Report_Id_779_CateID, 1, 3, 600, 0, Null, '2012-12-16 18:45:06', '2012-12-16 18:45:06', Null, '', Null, 0, 1, Null, 0, Null) end else --更新 begin update Report_Objects set Config='' where Code='EMR.Report.000001' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.Report.000002') begin --插入 Declare @Report_Id_780 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_780 out Declare @Report_Id_780_CateID int Set @Report_Id_780_CateID = (select id from Report_Categories where code='EMR.Reports') 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_780, 'EMR.Report.000002', '病人病历审阅日志', '病人病历审阅日志', Null, @Report_Id_780_CateID, 1, 3, 600, 0, Null, '2012-12-16 18:45:54', '2012-12-16 18:45:54', Null, '', Null, 0, 1, Null, 0, Null) end else --更新 begin update Report_Objects set Config='' where Code='EMR.Report.000002' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.Report.000003') begin --插入 Declare @Report_Id_1129 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1129 out Declare @Report_Id_1129_CateID int Set @Report_Id_1129_CateID = (select id from Report_Categories where code='EMR.Reports') 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_1129, 'EMR.Report.000003', '获取可用诊断', '获取可用诊断、医嘱、检查、护理', Null, @Report_Id_1129_CateID, 1, 3, 600, 0, Null, '2015-06-03 11:39:09', '2015-06-03 11:39:09', Null, ' B4C84933E5864E2D35784744C1C6784D DECLARE @Type int, @vaa01 int, @VAA07 INT, @ACF01 INT, @VAP01 INT, @AAG01 VARCHAR(2), @BCK01 INT, @EmpId INT, @DeptId INT, @DocumentId int SET @Type=%d SET @VAA01=%d SET @VAA07=%d SET @ACF01=%d SET @VAP01=%d SET @AAG01=%s SET @BCK01=%d SET @EmpId=%d SET @DeptId=%d SET @DocumentId=%d declare @vaa04 varchar(20) if @acf01=1 begin select @vaa04=vaa03 from vaa1 with(nolock) where VAA01=@VAA01 end else begin select @vaa04=vaa04 from vaa1 with(nolock) where VAA01=@VAA01 end SELECT distinct ''1过敏记录'' as CateName,BBX05 as Caption,1 ROWNR,CAST('''' AS VARCHAR(32)) scbm01,0 vaf59,0 sorder FROM VAN1 with(nolock) WHERE ACF01 = @ACF01 AND VAA07 = @VAA07 and VAN08=1 UNION ALL SELECT distinct ''2门诊诊断'' as FType,a.VAO15,1 ROWNR,'''',0,0 FROM VAO1 a with(nolock) WHERE a.ACF01 = @ACF01 AND a.VAA07 = @VAA07 AND VAO11=1 UNION ALL SELECT distinct ''3入院诊断'' as FType,a.VAO15,1 ROWNR,'''',0,0 FROM VAO2 a with(nolock) WHERE a.ACF01 = @ACF01 AND a.VAA07 = @VAA07 AND VAO11=2 UNION ALL SELECT distinct ''4出院诊断'' as FType,a.VAO15,1 ROWNR,'''',0,0 FROM VAO2 a with(nolock) WHERE a.ACF01 in (@ACF01,3) AND a.VAA07 = @VAA07 AND (VAO11=3 and vao06=1) UNION ALL SELECT distinct ''5其他诊断记录'' as FType,a.VAO15,1 ROWNR,'''',0,0 FROM VAO2 a with(nolock) WHERE a.ACF01 in (@ACF01,3) AND a.VAA07 = @VAA07 and (VAO11=3 and VAO06>1) UNION ALL SELECT FType = case a.vaf11 when 1 then ''6长期医嘱'' when 2 then ''6临时医嘱-''+CONVERT(varchar(10),vaf42,120) else ''6医嘱'' end ,a.VAF22N,0 ,CAST(a.cbm01 AS varchar)+CAST(a.vaf59 AS varchar) scbm01,a.VAF59,1 sorder FROM (SELECT a.cbm01,a.vaf59,a.rownr,a.vaf11, a.vaf42, (a.VAF22+'' ,每次''+isnull(a.VAF19,'''')+'' ''+a.VAF26+'' /''+CONVERT(varchar(3),a.VAF17)+''天''+'' ''+b.VAF22+'' ''+a.VAF23)AS VAF22N FROM VAF1 a with(nolock) JOIN VAF1 b with(nolock) ON a.VAF01A = b.VAF01 WHERE (a.BDA01=''1'' OR a.BDA01 = ''2'') AND a.VAF06 = @VAA07 and a.VAF04 = @ACF01 UNION all SELECT a.cbm01,a.vaf59,a.rownr,a.vaf11, a.vaf42, a.VAF22 AS VAF22N FROM VAF1 a with(nolock) WHERE a.BDA01=''8'' AND a.VAF06 = @VAA07 and a.VAF04 = @ACF01 UNION all SELECT a.cbm01,a.vaf59,a.rownr,a.vaf11, a.vaf42, a.VAF22 AS VAF22N FROM VAF1 a with(nolock) WHERE a.BDA01 in (''S'',''E'') AND IsNull(a.VAF01A,0)=0 AND a.VAF06 = @VAA07 and a.VAF04 = @ACF01 UNION ALL SELECT a.cbm01,a.vaf59,a.rownr,a.vaf11, a.VAF42, a.VAF22 AS VAF22N FROM VAF1 a with(nolock) WHERE a.BDA01 NOT IN (''1'',''2'',''3'',''8'',''S'',''E'',''0'') AND IsNull(a.VAF01A,0)=0 AND NOT EXISTS(SELECT b.VAF01A FROM VAF1 b WHERE a.VAF01=b.VAF01A) AND a.VAF06 = @VAA07 and a.VAF04 = @ACF01 UNION ALL SELECT a.cbm01,a.vaf59,a.rownr,a.vaf11, a.vaf42, a.VAF22 AS VAF22N FROM VAF1 a with(nolock) WHERE a.BDA01 = ''0'' AND a.VAF06 = @VAA07 and a.VAF04 = @ACF01) a UNION ALL SELECT FType = case a.vaf11 when 1 then ''6长期医嘱'' when 2 then ''6临时医嘱-''+CONVERT(varchar(10),vaf42,120) else ''6医嘱'' end ,a.VAF22N,0 ,CAST(a.cbm01 AS varchar)+CAST(a.vaf59 AS varchar) scbm01,a.VAF59,1 sorder FROM (SELECT a.cbm01,a.vaf59,a.rownr,a.vaf11, a.vaf42, (a.VAF22+'' ,每次''+isnull(a.VAF19,'''')+'' ''+a.VAF26+'' /''+CONVERT(varchar(3),a.VAF17)+''天''+'' ''+b.VAF22+'' ''+a.VAF23)AS VAF22N FROM VAF2 a with(nolock) JOIN VAF2 b ON a.VAF01A = b.VAF01 WHERE (a.BDA01=''1'' OR a.BDA01 = ''2'') AND a.VAF06 = @VAA07 and a.VAF04 = @ACF01 UNION all SELECT a.cbm01,a.vaf59,a.rownr,a.vaf11, a.vaf42, a.VAF22 AS VAF22N FROM VAF2 a with(nolock) WHERE a.BDA01=''8'' AND a.VAF06 = @VAA07 and a.VAF04 = @ACF01 UNION all SELECT a.cbm01,a.vaf59,a.rownr,a.vaf11, a.vaf42, a.VAF22 AS VAF22N FROM VAF2 a with(nolock) WHERE a.BDA01 in (''S'',''E'') AND IsNull(a.VAF01A,0)=0 AND a.VAF06 = @VAA07 and a.VAF04 = @ACF01 UNION ALL SELECT a.cbm01,a.vaf59,a.rownr,a.vaf11, a.VAF42, a.VAF22 AS VAF22N FROM VAF2 a with(nolock) WHERE a.BDA01 NOT IN (''1'',''2'',''3'',''8'',''S'',''E'',''0'') AND IsNull(a.VAF01A,0)=0 AND NOT EXISTS(SELECT b.VAF01A FROM VAF2 b WHERE a.VAF01=b.VAF01A) AND a.VAF06 = @VAA07 and a.VAF04 = @ACF01 UNION ALL SELECT a.cbm01,a.vaf59,a.rownr,a.vaf11, a.vaf42, a.VAF22 AS VAF22N FROM VAF2 a with(nolock) WHERE a.BDA01 = ''0'' AND a.VAF06 = @VAA07 and a.VAF04 = @ACF01) a UNION ALL SELECT ''7检验结果-''+CONVERT(varchar(16),isnull(C.LAB20,''''),120)+''-''+isnull(D.BEY20,'''') CateName, B.BBX05+'' ''+a.lac10 + case when ascii(E.BDG02) between 48 and 57 then ''×'' else '''' end + isnull(E.BDG02, '''') + isnull(a.lac11, '''') caption,convert(varchar(20),a.BBX01A)+convert(varchar(10),isnull(g.ROWNR,E.ROWNR)),'''',0,0 FROM LAC1 A with(nolock) JOIN BBX1 B ON A.BBX01=B.BBX01 join LAB1 C with(nolock) on C.LAB01=A.LAB01 join BEY1 D on D.BEY01=c.BEY01A join BFC1 E on E.BBX01=B.BBX01 left join BFD1 g on A.BBX01A = g.BBX01A and a.BBX01 = g.BBX01B WHERE C.LAB32=3 and C.LAB09=@vaa04 UNION ALL select ''8影像检查-''+CONVERT(varchar(16),isnull(b.PAB17,''''),120)+''-''+isnull(a.BBC01,'''') CateName, ISNULL(PAA43,'''')+''(''+ISNULL(PAA44,'''')+'') ''+char(13)+''影像所见：''+ISNULL(b.PAB10,'''')+char(13)+''影像提示：''+ISNULL(b.PAB11,'''')+char(13)+''诊断建议：''+ISNULL(b.PAB12,'''') caption, b.PAB01,'''',0,0 from PAA1 a with(nolock) join PAB1 b with(nolock) on a.PAA01=b.PAA01 where a.vaa01=@vaa01 and a.vaa07=@VAA07 and a.acf01=@acf01 and a.PAA35 in(3,4) UNION ALL select ''9护理记录'' CateName, CONVERT(varchar(16),isnull(记录时间,''''),120)+'' 体温:''+体温+''℃''+'' 心率:''+心率+''次/分''+'' 脉搏:''+脉搏+''次/分''+'' 呼吸:''+呼吸+''次/分''+'' 体重:''+体重+''Kg''+'' 血压:''+收缩压+''/''+舒张压+''mmHg'' caption, VCF01,'''',0,0 from (Select VCF01,isnull(VCF24,'''') AS 体温,isnull(VCF28,'''') AS 心率,isnull(VCF30,'''') as 脉搏,isnull(VCF33,'''') as 呼吸,isnull(VCF37,'''') as 体重,isnull(VCF41,'''') as 收缩压,isnull(VCF42,'''') as 舒张压,VCF09 as 记录时间 From VCF1 with(nolock) Where vaa01=@vaa01 and vaa07=@VAA07 and vap01=@VAP01 and acf01=@acf01 ) as T ORDER BY CateName,scbm01,ROWNR ', Null, 0, 1, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' B4C84933E5864E2D35784744C1C6784D DECLARE @Type int, @vaa01 int, @VAA07 INT, @ACF01 INT, @VAP01 INT, @AAG01 VARCHAR(2), @BCK01 INT, @EmpId INT, @DeptId INT, @DocumentId int SET @Type=%d SET @VAA01=%d SET @VAA07=%d SET @ACF01=%d SET @VAP01=%d SET @AAG01=%s SET @BCK01=%d SET @EmpId=%d SET @DeptId=%d SET @DocumentId=%d declare @vaa04 varchar(20) if @acf01=1 begin select @vaa04=vaa03 from vaa1 with(nolock) where VAA01=@VAA01 end else begin select @vaa04=vaa04 from vaa1 with(nolock) where VAA01=@VAA01 end SELECT distinct ''1过敏记录'' as CateName,BBX05 as Caption,1 ROWNR,CAST('''' AS VARCHAR(32)) scbm01,0 vaf59,0 sorder FROM VAN1 with(nolock) WHERE ACF01 = @ACF01 AND VAA07 = @VAA07 and VAN08=1 UNION ALL SELECT distinct ''2门诊诊断'' as FType,a.VAO15,1 ROWNR,'''',0,0 FROM VAO1 a with(nolock) WHERE a.ACF01 = @ACF01 AND a.VAA07 = @VAA07 AND VAO11=1 UNION ALL SELECT distinct ''3入院诊断'' as FType,a.VAO15,1 ROWNR,'''',0,0 FROM VAO2 a with(nolock) WHERE a.ACF01 = @ACF01 AND a.VAA07 = @VAA07 AND VAO11=2 UNION ALL SELECT distinct ''4出院诊断'' as FType,a.VAO15,1 ROWNR,'''',0,0 FROM VAO2 a with(nolock) WHERE a.ACF01 in (@ACF01,3) AND a.VAA07 = @VAA07 AND (VAO11=3 and vao06=1) UNION ALL SELECT distinct ''5其他诊断记录'' as FType,a.VAO15,1 ROWNR,'''',0,0 FROM VAO2 a with(nolock) WHERE a.ACF01 in (@ACF01,3) AND a.VAA07 = @VAA07 and (VAO11=3 and VAO06>1) UNION ALL SELECT FType = case a.vaf11 when 1 then ''6长期医嘱'' when 2 then ''6临时医嘱-''+CONVERT(varchar(10),vaf42,120) else ''6医嘱'' end ,a.VAF22N,0 ,CAST(a.cbm01 AS varchar)+CAST(a.vaf59 AS varchar) scbm01,a.VAF59,1 sorder FROM (SELECT a.cbm01,a.vaf59,a.rownr,a.vaf11, a.vaf42, (a.VAF22+'' ,每次''+isnull(a.VAF19,'''')+'' ''+a.VAF26+'' /''+CONVERT(varchar(3),a.VAF17)+''天''+'' ''+b.VAF22+'' ''+a.VAF23)AS VAF22N FROM VAF1 a with(nolock) JOIN VAF1 b with(nolock) ON a.VAF01A = b.VAF01 WHERE (a.BDA01=''1'' OR a.BDA01 = ''2'') AND a.VAF06 = @VAA07 and a.VAF04 = @ACF01 UNION all SELECT a.cbm01,a.vaf59,a.rownr,a.vaf11, a.vaf42, a.VAF22 AS VAF22N FROM VAF1 a with(nolock) WHERE a.BDA01=''8'' AND a.VAF06 = @VAA07 and a.VAF04 = @ACF01 UNION all SELECT a.cbm01,a.vaf59,a.rownr,a.vaf11, a.vaf42, a.VAF22 AS VAF22N FROM VAF1 a with(nolock) WHERE a.BDA01 in (''S'',''E'') AND IsNull(a.VAF01A,0)=0 AND a.VAF06 = @VAA07 and a.VAF04 = @ACF01 UNION ALL SELECT a.cbm01,a.vaf59,a.rownr,a.vaf11, a.VAF42, a.VAF22 AS VAF22N FROM VAF1 a with(nolock) WHERE a.BDA01 NOT IN (''1'',''2'',''3'',''8'',''S'',''E'',''0'') AND IsNull(a.VAF01A,0)=0 AND NOT EXISTS(SELECT b.VAF01A FROM VAF1 b WHERE a.VAF01=b.VAF01A) AND a.VAF06 = @VAA07 and a.VAF04 = @ACF01 UNION ALL SELECT a.cbm01,a.vaf59,a.rownr,a.vaf11, a.vaf42, a.VAF22 AS VAF22N FROM VAF1 a with(nolock) WHERE a.BDA01 = ''0'' AND a.VAF06 = @VAA07 and a.VAF04 = @ACF01) a UNION ALL SELECT FType = case a.vaf11 when 1 then ''6长期医嘱'' when 2 then ''6临时医嘱-''+CONVERT(varchar(10),vaf42,120) else ''6医嘱'' end ,a.VAF22N,0 ,CAST(a.cbm01 AS varchar)+CAST(a.vaf59 AS varchar) scbm01,a.VAF59,1 sorder FROM (SELECT a.cbm01,a.vaf59,a.rownr,a.vaf11, a.vaf42, (a.VAF22+'' ,每次''+isnull(a.VAF19,'''')+'' ''+a.VAF26+'' /''+CONVERT(varchar(3),a.VAF17)+''天''+'' ''+b.VAF22+'' ''+a.VAF23)AS VAF22N FROM VAF2 a with(nolock) JOIN VAF2 b ON a.VAF01A = b.VAF01 WHERE (a.BDA01=''1'' OR a.BDA01 = ''2'') AND a.VAF06 = @VAA07 and a.VAF04 = @ACF01 UNION all SELECT a.cbm01,a.vaf59,a.rownr,a.vaf11, a.vaf42, a.VAF22 AS VAF22N FROM VAF2 a with(nolock) WHERE a.BDA01=''8'' AND a.VAF06 = @VAA07 and a.VAF04 = @ACF01 UNION all SELECT a.cbm01,a.vaf59,a.rownr,a.vaf11, a.vaf42, a.VAF22 AS VAF22N FROM VAF2 a with(nolock) WHERE a.BDA01 in (''S'',''E'') AND IsNull(a.VAF01A,0)=0 AND a.VAF06 = @VAA07 and a.VAF04 = @ACF01 UNION ALL SELECT a.cbm01,a.vaf59,a.rownr,a.vaf11, a.VAF42, a.VAF22 AS VAF22N FROM VAF2 a with(nolock) WHERE a.BDA01 NOT IN (''1'',''2'',''3'',''8'',''S'',''E'',''0'') AND IsNull(a.VAF01A,0)=0 AND NOT EXISTS(SELECT b.VAF01A FROM VAF2 b WHERE a.VAF01=b.VAF01A) AND a.VAF06 = @VAA07 and a.VAF04 = @ACF01 UNION ALL SELECT a.cbm01,a.vaf59,a.rownr,a.vaf11, a.vaf42, a.VAF22 AS VAF22N FROM VAF2 a with(nolock) WHERE a.BDA01 = ''0'' AND a.VAF06 = @VAA07 and a.VAF04 = @ACF01) a UNION ALL SELECT ''7检验结果-''+CONVERT(varchar(16),isnull(C.LAB20,''''),120)+''-''+isnull(D.BEY20,'''') CateName, B.BBX05+'' ''+a.lac10 + case when ascii(E.BDG02) between 48 and 57 then ''×'' else '''' end + isnull(E.BDG02, '''') + isnull(a.lac11, '''') caption,convert(varchar(20),a.BBX01A)+convert(varchar(10),isnull(g.ROWNR,E.ROWNR)),'''',0,0 FROM LAC1 A with(nolock) JOIN BBX1 B ON A.BBX01=B.BBX01 join LAB1 C with(nolock) on C.LAB01=A.LAB01 join BEY1 D on D.BEY01=c.BEY01A join BFC1 E on E.BBX01=B.BBX01 left join BFD1 g on A.BBX01A = g.BBX01A and a.BBX01 = g.BBX01B WHERE C.LAB32=3 and C.LAB09=@vaa04 UNION ALL select ''8影像检查-''+CONVERT(varchar(16),isnull(b.PAB17,''''),120)+''-''+isnull(a.BBC01,'''') CateName, ISNULL(PAA43,'''')+''(''+ISNULL(PAA44,'''')+'') ''+char(13)+''影像所见：''+ISNULL(b.PAB10,'''')+char(13)+''影像提示：''+ISNULL(b.PAB11,'''')+char(13)+''诊断建议：''+ISNULL(b.PAB12,'''') caption, b.PAB01,'''',0,0 from PAA1 a with(nolock) join PAB1 b with(nolock) on a.PAA01=b.PAA01 where a.vaa01=@vaa01 and a.vaa07=@VAA07 and a.acf01=@acf01 and a.PAA35 in(3,4) UNION ALL select ''9护理记录'' CateName, CONVERT(varchar(16),isnull(记录时间,''''),120)+'' 体温:''+体温+''℃''+'' 心率:''+心率+''次/分''+'' 脉搏:''+脉搏+''次/分''+'' 呼吸:''+呼吸+''次/分''+'' 体重:''+体重+''Kg''+'' 血压:''+收缩压+''/''+舒张压+''mmHg'' caption, VCF01,'''',0,0 from (Select VCF01,isnull(VCF24,'''') AS 体温,isnull(VCF28,'''') AS 心率,isnull(VCF30,'''') as 脉搏,isnull(VCF33,'''') as 呼吸,isnull(VCF37,'''') as 体重,isnull(VCF41,'''') as 收缩压,isnull(VCF42,'''') as 舒张压,VCF09 as 记录时间 From VCF1 with(nolock) Where vaa01=@vaa01 and vaa07=@VAA07 and vap01=@VAP01 and acf01=@acf01 ) as T ORDER BY CateName,scbm01,ROWNR ' where Code='EMR.Report.000003' end GO --delete from Report_Objects where CategoryID=76 /*转移文本附件*/ IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.Convert.000001') begin --插入 Declare @Report_Id_781 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_781 out Declare @Report_Id_781_CateID int Set @Report_Id_781_CateID = (select id from Report_Categories where code='EMR.ConvertText') 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_781, 'EMR.Convert.000001', '病历转移文本附件', '病历转移文本附件', Null, @Report_Id_781_CateID, 1, 4, 600, 0, Null, '2012-12-29 10:37:53', '2012-12-29 11:21:55', Null, ' A2488F28C5278193CAE87CFDEFECACE5DECLARE @VAE01 INT, @VAA01 INT SET @VAA01=:VAA01 SET @VAE01=:VAE01 select A.VAQ01 ,A.VAQ31 from VAQ1 A where A.VAA01=@VAA01 AND A.ACF01=2 AND A.VAA07=@VAE01 AND EXISTS(SELECT * FROM CAD1 WHERE A.CAD01=CAD01 AND CAD15=1)<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid RecNo="1" Head="1"><Sql>SELECT VAA01, VAA02, VAA03, VAA04, VAA05 FROM VAA1</Sql><HD><Ie FD="VAA04" CA="住院号" WD="100"/><Ie FD="VAA05" CA="姓名" WD="100"/></HD><QF><Ie FD="VAA04" WE="7"/><Ie FD="VAA05" WE="7"/><Ie FD="ABBRP" WE="7" CT="2"/><Ie FD="ABBRW" WE="7" CT="1"/></QF></Grid></Configs><?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid RecNo="1" Head="1"><Sql>SELECT VAE01, VAE02, VAE11, VAE19 FROM VAE1 WHERE VAA01=&lt;VAA01&gt;</Sql><HD><Ie FD="VAE02" CA="登记号" WD="100"/><Ie FD="VAE11" CA="入院日期" WD="100"/><Ie FD="VAE19" CA="住院次数" WD="100"/></HD></Grid></Configs> ', Null, 0, 1, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' A2488F28C5278193CAE87CFDEFECACE5DECLARE @VAE01 INT, @VAA01 INT SET @VAA01=:VAA01 SET @VAE01=:VAE01 select A.VAQ01 ,A.VAQ31 from VAQ1 A where A.VAA01=@VAA01 AND A.ACF01=2 AND A.VAA07=@VAE01 AND EXISTS(SELECT * FROM CAD1 WHERE A.CAD01=CAD01 AND CAD15=1)<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid RecNo="1" Head="1"><Sql>SELECT VAA01, VAA02, VAA03, VAA04, VAA05 FROM VAA1</Sql><HD><Ie FD="VAA04" CA="住院号" WD="100"/><Ie FD="VAA05" CA="姓名" WD="100"/></HD><QF><Ie FD="VAA04" WE="7"/><Ie FD="VAA05" WE="7"/><Ie FD="ABBRP" WE="7" CT="2"/><Ie FD="ABBRW" WE="7" CT="1"/></QF></Grid></Configs><?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid RecNo="1" Head="1"><Sql>SELECT VAE01, VAE02, VAE11, VAE19 FROM VAE1 WHERE VAA01=&lt;VAA01&gt;</Sql><HD><Ie FD="VAE02" CA="登记号" WD="100"/><Ie FD="VAE11" CA="入院日期" WD="100"/><Ie FD="VAE19" CA="住院次数" WD="100"/></HD></Grid></Configs> ' where Code='EMR.Convert.000001' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.Convert.000002') begin --插入 Declare @Report_Id_782 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_782 out Declare @Report_Id_782_CateID int Set @Report_Id_782_CateID = (select id from Report_Categories where code='EMR.ConvertText') 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_782, 'EMR.Convert.000002', '住院病历转换到文本', '住院病历转换到文本', Null, @Report_Id_782_CateID, 1, 4, 600, 0, Null, '2012-12-29 11:21:19', '2015-06-11 14:33:32', Null, ' 1EE9E539EBE3B435CCD3B8A0196F33E4DECLARE @BCK01 INT, @VAE01 INT SET @BCK01=:BCK01 SET @VAE01=:VAE01 SET @BCK01 = ISNULL(@BCK01, 0) select A.VAQ01 ,A.VAQ31 from VAQ1 A where (@BCK01=0 or A.BCK01=@BCK01) AND A.ACF01=2 AND (@VAE01=0 or A.VAA07=@VAE01) AND EXISTS(SELECT * FROM CAD1 WHERE A.CAD01=CAD01 AND CAD15=1)<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid RecNo="1" Head="1" UseW="1"><Sql>SELECT a.BCK01, A.BCK02, a.BCK03 FROM BCK1 a Where EXISTS(SELECT b.* FROM BAZ1 b WHERE a.BCK01=b.BCK01 AND b.BAU01 IN (''01'') AND b.ACF01 IN(2,3)) </Sql><Order>ORDER BY A.BCK03</Order><HD><Ie FD="BCK02" CA="编码" WD="100"/><Ie FD="BCK03" CA="名称" WD="100"/></HD><QF><Ie FD="a.abbrp" WE="7"/><Ie FD="a.bck03" WE="7"/></QF></Grid></Configs><?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid UseW="1"><Sql>select * from VAA1 a join VAE1 b on a.vaa01=b.vaa01 where b.BCK01D=&lt;BCK01&gt;</Sql><Order>order by VAE01 desc</Order><HD><Ie FD="VAA04" WD="100"/><Ie FD="VAA05" WD="100"/><Ie FD="VAE11" WD="100"/></HD><QF><Ie FD="a.abbrp" WE="7"/><Ie FD="a.vaa05" WE="7"/><Ie FD="a.vaa04" WE="7"/></QF></Grid></Configs> ', Null, 0, 1, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' 1EE9E539EBE3B435CCD3B8A0196F33E4DECLARE @BCK01 INT, @VAE01 INT SET @BCK01=:BCK01 SET @VAE01=:VAE01 SET @BCK01 = ISNULL(@BCK01, 0) select A.VAQ01 ,A.VAQ31 from VAQ1 A where (@BCK01=0 or A.BCK01=@BCK01) AND A.ACF01=2 AND (@VAE01=0 or A.VAA07=@VAE01) AND EXISTS(SELECT * FROM CAD1 WHERE A.CAD01=CAD01 AND CAD15=1)<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid RecNo="1" Head="1" UseW="1"><Sql>SELECT a.BCK01, A.BCK02, a.BCK03 FROM BCK1 a Where EXISTS(SELECT b.* FROM BAZ1 b WHERE a.BCK01=b.BCK01 AND b.BAU01 IN (''01'') AND b.ACF01 IN(2,3)) </Sql><Order>ORDER BY A.BCK03</Order><HD><Ie FD="BCK02" CA="编码" WD="100"/><Ie FD="BCK03" CA="名称" WD="100"/></HD><QF><Ie FD="a.abbrp" WE="7"/><Ie FD="a.bck03" WE="7"/></QF></Grid></Configs><?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid UseW="1"><Sql>select * from VAA1 a join VAE1 b on a.vaa01=b.vaa01 where b.BCK01D=&lt;BCK01&gt;</Sql><Order>order by VAE01 desc</Order><HD><Ie FD="VAA04" WD="100"/><Ie FD="VAA05" WD="100"/><Ie FD="VAE11" WD="100"/></HD><QF><Ie FD="a.abbrp" WE="7"/><Ie FD="a.vaa05" WE="7"/><Ie FD="a.vaa04" WE="7"/></QF></Grid></Configs> ' where Code='EMR.Convert.000002' end GO --delete from Report_Objects where CategoryID=77 /*病历评分管理*/ IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.REVIEW.000001') begin --插入 Declare @Report_Id_783 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_783 out Declare @Report_Id_783_CateID int Set @Report_Id_783_CateID = (select id from Report_Categories where code='EMR.REVIEW') 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_783, 'EMR.REVIEW.000001', '我的工作列表', '我的工作列表', Null, @Report_Id_783_CateID, 1, 4, 600, 0, Null, '2012-12-29 14:17:55', '2014-04-25 15:30:58', Null, ' 76B91F82FF3DA815EA30F2D813079271 select A.RAS01 ,A.ADB01 ,A.ACF01 ,A.VAA01 ,A.VAA07 ,A.RAS06 ,A.RAS07 ,A.RAS08 ,A.RAS09 ,A.RAS10 ,A.RAS11 ,A.RAS12 ,A.BCE01A ,A.BCE03A ,A.RAS15 ,A.BCE01B ,A.BCE03B ,A.RAS18 ,C.VAE02 ,B.VAA04 ,B.VAA05 ,C.VAE19 ,D.BCK03 AS BCK03D from RAS1 A JOIN VAA1 B ON A.VAA01=B.VAA01 JOIN VAE1 C ON A.VAA07=C.VAE01 LEFT JOIN BCK1 D ON C.BCK01D=D.BCK01 where A.BCE01A=@$EmpId ', Null, 0, 1, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' 76B91F82FF3DA815EA30F2D813079271 select A.RAS01 ,A.ADB01 ,A.ACF01 ,A.VAA01 ,A.VAA07 ,A.RAS06 ,A.RAS07 ,A.RAS08 ,A.RAS09 ,A.RAS10 ,A.RAS11 ,A.RAS12 ,A.BCE01A ,A.BCE03A ,A.RAS15 ,A.BCE01B ,A.BCE03B ,A.RAS18 ,C.VAE02 ,B.VAA04 ,B.VAA05 ,C.VAE19 ,D.BCK03 AS BCK03D from RAS1 A JOIN VAA1 B ON A.VAA01=B.VAA01 JOIN VAE1 C ON A.VAA07=C.VAE01 LEFT JOIN BCK1 D ON C.BCK01D=D.BCK01 where A.BCE01A=@$EmpId ' where Code='EMR.REVIEW.000001' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.REVIEW.000003') begin --插入 Declare @Report_Id_792 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_792 out Declare @Report_Id_792_CateID int Set @Report_Id_792_CateID = (select id from Report_Categories where code='EMR.REVIEW') 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_792, 'EMR.REVIEW.000003', '病历评分报表', Null, Null, @Report_Id_792_CateID, 1, 3, 600, 0, Null, '2013-01-09 15:06:04', '2013-01-09 15:06:04', Null, ' 3AF0067AD83F7E0ECE4E982404C4F232 select A.RAS01 ,A.ADB01 ,A.ACF01 ,A.VAA01 ,A.VAA07 ,A.RAS06 ,A.RAS07 ,A.RAS08 ,A.RAS09 ,A.RAS10 ,A.RAS11 ,A.RAS12 ,A.BCE01A ,A.BCE03A ,A.RAS15 ,A.BCE01B ,A.BCE03B ,A.RAS18 ,C.VAE02 ,B.VAA04 ,B.VAA05 ,C.VAE19 ,D.BCK03 AS BCK03D from RAS1 A JOIN VAA1 B ON A.VAA01=B.VAA01 JOIN VAE1 C ON A.VAA07=C.VAE01 LEFT JOIN BCK1 D ON C.BCK01D=D.BCK01 where RAS01=:RAS01 DECLARE @ADB01 INT , @RAS01 INT = :RAS01 select @ADB01=ADB01 from RAS1 where RAS01=@RAS01 select A.ADC01, A.ADC01A, A.ADB01, A.ADC04, A.ADC05, A.ADC06, A.ADC07, A.ADC08, A.ADC09, ADC09B = CASE A.ADC08 WHEN 1 THEN Cast(convert(numeric(6,1), A.ADC09, 0) AS VARCHAR)+'' ''+ISNULL(A.ADC10, '''') WHEN 2 THEN ''等级'' END, A.ADC10, A.ADC11, A.ADC12, A.ROWNR, A.ROWNRA, A.ROWNRB, A.ADC16, B.ADC05 AS ADC05A, B.ADC09 AS ADC09A, C.RAT01, C.RAT04, RAT04B = CASE A.ADC08 WHEN 1 THEN C.RAT04 WHEN 2 THEN C.ADC11 END, C.ADC11 AS RAT05, C.RAT07 from ADC1 A join ADC1 B ON A.ADC01A=B.ADC01 left join RAT1 C ON C.ADC01=A.ADC01 AND C.RAS01=@RAS01 where A.ADC01A>0 AND A.ADB01=@ADB01 order by A.ADC01A, A.ADC01 ', Null, 0, 1, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' 3AF0067AD83F7E0ECE4E982404C4F232 select A.RAS01 ,A.ADB01 ,A.ACF01 ,A.VAA01 ,A.VAA07 ,A.RAS06 ,A.RAS07 ,A.RAS08 ,A.RAS09 ,A.RAS10 ,A.RAS11 ,A.RAS12 ,A.BCE01A ,A.BCE03A ,A.RAS15 ,A.BCE01B ,A.BCE03B ,A.RAS18 ,C.VAE02 ,B.VAA04 ,B.VAA05 ,C.VAE19 ,D.BCK03 AS BCK03D from RAS1 A JOIN VAA1 B ON A.VAA01=B.VAA01 JOIN VAE1 C ON A.VAA07=C.VAE01 LEFT JOIN BCK1 D ON C.BCK01D=D.BCK01 where RAS01=:RAS01 DECLARE @ADB01 INT , @RAS01 INT = :RAS01 select @ADB01=ADB01 from RAS1 where RAS01=@RAS01 select A.ADC01, A.ADC01A, A.ADB01, A.ADC04, A.ADC05, A.ADC06, A.ADC07, A.ADC08, A.ADC09, ADC09B = CASE A.ADC08 WHEN 1 THEN Cast(convert(numeric(6,1), A.ADC09, 0) AS VARCHAR)+'' ''+ISNULL(A.ADC10, '''') WHEN 2 THEN ''等级'' END, A.ADC10, A.ADC11, A.ADC12, A.ROWNR, A.ROWNRA, A.ROWNRB, A.ADC16, B.ADC05 AS ADC05A, B.ADC09 AS ADC09A, C.RAT01, C.RAT04, RAT04B = CASE A.ADC08 WHEN 1 THEN C.RAT04 WHEN 2 THEN C.ADC11 END, C.ADC11 AS RAT05, C.RAT07 from ADC1 A join ADC1 B ON A.ADC01A=B.ADC01 left join RAT1 C ON C.ADC01=A.ADC01 AND C.RAS01=@RAS01 where A.ADC01A>0 AND A.ADB01=@ADB01 order by A.ADC01A, A.ADC01 ' where Code='EMR.REVIEW.000003' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.REVIEW.000010') begin --插入 Declare @Report_Id_800 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_800 out Declare @Report_Id_800_CateID int Set @Report_Id_800_CateID = (select id from Report_Categories where code='EMR.REVIEW') 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_800, 'EMR.REVIEW.000010', '显示在院病人（已完成病历）', '显示在院病人（已完成病历）', Null, @Report_Id_800_CateID, 1, 4, 600, 0, Null, '2013-01-21 16:07:15', '2014-04-25 15:29:16', Null, ' 0EE727A813D42255179345F9151B5603 select A.RAS01 ,A.ADB01 ,ISNULL(A.ACF01, 2) ACF01 ,ISNULL(A.VAA01, B.VAA01) VAA01 ,ISNULL(A.VAA07, C.VAE01) VAA07 ,A.RAS06 ,A.RAS07 ,A.RAS08 ,isnull(A.RAS09, 0) RAS09 ,isnull(A.RAS10, 10) RAS10 ,A.RAS11 ,A.RAS12 ,A.BCE01A ,A.BCE03A ,A.RAS15 ,A.BCE01B ,A.BCE03B ,A.RAS18 ,C.VAE02 ,B.VAA04 ,B.VAA05 ,C.VAE19 ,D.BCK03 AS BCK03D from VAE1 c JOIN VAA1 B ON C.VAA01=B.VAA01 LEFT JOIN RAS1 A ON A.VAA07=C.VAE01 LEFT JOIN BCK1 D ON C.BCK01D=D.BCK01 where C.VAE44<5 ', Null, 0, 1, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' 0EE727A813D42255179345F9151B5603 select A.RAS01 ,A.ADB01 ,ISNULL(A.ACF01, 2) ACF01 ,ISNULL(A.VAA01, B.VAA01) VAA01 ,ISNULL(A.VAA07, C.VAE01) VAA07 ,A.RAS06 ,A.RAS07 ,A.RAS08 ,isnull(A.RAS09, 0) RAS09 ,isnull(A.RAS10, 10) RAS10 ,A.RAS11 ,A.RAS12 ,A.BCE01A ,A.BCE03A ,A.RAS15 ,A.BCE01B ,A.BCE03B ,A.RAS18 ,C.VAE02 ,B.VAA04 ,B.VAA05 ,C.VAE19 ,D.BCK03 AS BCK03D from VAE1 c JOIN VAA1 B ON C.VAA01=B.VAA01 LEFT JOIN RAS1 A ON A.VAA07=C.VAE01 LEFT JOIN BCK1 D ON C.BCK01D=D.BCK01 where C.VAE44<5 ' where Code='EMR.REVIEW.000010' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.REVIEW.000012') begin --插入 Declare @Report_Id_801 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_801 out Declare @Report_Id_801_CateID int Set @Report_Id_801_CateID = (select id from Report_Categories where code='EMR.REVIEW') 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_801, 'EMR.REVIEW.000012', '查询科室病人列表', '查询科室病人列表', Null, @Report_Id_801_CateID, 1, 4, 600, 0, Null, '2013-01-21 16:08:09', '2014-04-25 15:29:30', Null, ' DB56466D6A61F8A1E70CA4D26124426FDECLARE @BCK01 INT, @VAE44 INT SET @BCK01=:BCK01 SET @VAE44=:VAE44 if isnull(@VAE44, 1)=1 begin select A.RAS01 ,A.ADB01 ,ISNULL(A.ACF01, 2) ACF01 ,ISNULL(A.VAA01, B.VAA01) VAA01 ,ISNULL(A.VAA07, C.VAE01) VAA07 ,A.RAS06 ,A.RAS07 ,A.RAS08 ,isnull(A.RAS09, 0) RAS09 ,isnull(A.RAS10, 10) RAS10 ,A.RAS11 ,A.RAS12 ,A.BCE01A ,A.BCE03A ,A.RAS15 ,A.BCE01B ,A.BCE03B ,A.RAS18 ,C.VAE02 ,B.VAA04 ,B.VAA05 ,C.VAE19 ,D.BCK03 AS BCK03D from VAE1 c JOIN VAA1 B ON C.VAA01=B.VAA01 LEFT JOIN RAS1 A ON A.VAA07=C.VAE01 LEFT JOIN BCK1 D ON C.BCK01D=D.BCK01 where C.BCK01D = @BCK01 and c.vae44<5 end ELSE BEGIN select A.RAS01 ,A.ADB01 ,ISNULL(A.ACF01, 2) ACF01 ,ISNULL(A.VAA01, B.VAA01) VAA01 ,ISNULL(A.VAA07, C.VAE01) VAA07 ,A.RAS06 ,A.RAS07 ,A.RAS08 ,isnull(A.RAS09, 0) RAS09 ,isnull(A.RAS10, 10) RAS10 ,A.RAS11 ,A.RAS12 ,A.BCE01A ,A.BCE03A ,A.RAS15 ,A.BCE01B ,A.BCE03B ,A.RAS18 ,C.VAE02 ,B.VAA04 ,B.VAA05 ,C.VAE19 ,D.BCK03 AS BCK03D from VAE1 c JOIN VAA1 B ON C.VAA01=B.VAA01 LEFT JOIN RAS1 A ON A.VAA07=C.VAE01 LEFT JOIN BCK1 D ON C.BCK01D=D.BCK01 where C.BCK01D = @BCK01 and c.vae44=5 END<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid RecNo="1" Head="1"><Sql>SELECT BCK01, BCK02, BCK03 FROM BCK1</Sql><HD><Ie FD="BCK02" CA="编码" WD="100"/><Ie FD="BCK03" CA="名称" WD="200" SZ="1"/></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=在院 2=出院 ', Null, 0, 1, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' DB56466D6A61F8A1E70CA4D26124426FDECLARE @BCK01 INT, @VAE44 INT SET @BCK01=:BCK01 SET @VAE44=:VAE44 if isnull(@VAE44, 1)=1 begin select A.RAS01 ,A.ADB01 ,ISNULL(A.ACF01, 2) ACF01 ,ISNULL(A.VAA01, B.VAA01) VAA01 ,ISNULL(A.VAA07, C.VAE01) VAA07 ,A.RAS06 ,A.RAS07 ,A.RAS08 ,isnull(A.RAS09, 0) RAS09 ,isnull(A.RAS10, 10) RAS10 ,A.RAS11 ,A.RAS12 ,A.BCE01A ,A.BCE03A ,A.RAS15 ,A.BCE01B ,A.BCE03B ,A.RAS18 ,C.VAE02 ,B.VAA04 ,B.VAA05 ,C.VAE19 ,D.BCK03 AS BCK03D from VAE1 c JOIN VAA1 B ON C.VAA01=B.VAA01 LEFT JOIN RAS1 A ON A.VAA07=C.VAE01 LEFT JOIN BCK1 D ON C.BCK01D=D.BCK01 where C.BCK01D = @BCK01 and c.vae44<5 end ELSE BEGIN select A.RAS01 ,A.ADB01 ,ISNULL(A.ACF01, 2) ACF01 ,ISNULL(A.VAA01, B.VAA01) VAA01 ,ISNULL(A.VAA07, C.VAE01) VAA07 ,A.RAS06 ,A.RAS07 ,A.RAS08 ,isnull(A.RAS09, 0) RAS09 ,isnull(A.RAS10, 10) RAS10 ,A.RAS11 ,A.RAS12 ,A.BCE01A ,A.BCE03A ,A.RAS15 ,A.BCE01B ,A.BCE03B ,A.RAS18 ,C.VAE02 ,B.VAA04 ,B.VAA05 ,C.VAE19 ,D.BCK03 AS BCK03D from VAE1 c JOIN VAA1 B ON C.VAA01=B.VAA01 LEFT JOIN RAS1 A ON A.VAA07=C.VAE01 LEFT JOIN BCK1 D ON C.BCK01D=D.BCK01 where C.BCK01D = @BCK01 and c.vae44=5 END<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid RecNo="1" Head="1"><Sql>SELECT BCK01, BCK02, BCK03 FROM BCK1</Sql><HD><Ie FD="BCK02" CA="编码" WD="100"/><Ie FD="BCK03" CA="名称" WD="200" SZ="1"/></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=在院 2=出院 ' where Code='EMR.REVIEW.000012' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.REVIEW.000013') begin --插入 Declare @Report_Id_1041 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1041 out Declare @Report_Id_1041_CateID int Set @Report_Id_1041_CateID = (select id from Report_Categories where code='EMR.REVIEW') 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_1041, 'EMR.REVIEW.000013', '死亡病历分析', Null, Null, @Report_Id_1041_CateID, 1, 4, 600, 0, Null, '2014-04-04 13:21:16', '2014-04-25 15:29:41', Null, ' 39EA76AF3ED4A6E6FFEFB4F06317C6FF select A.RAS01 ,A.ADB01 ,ISNULL(A.ACF01, 2) ACF01 ,ISNULL(A.VAA01, B.VAA01) VAA01 ,ISNULL(A.VAA07, C.VAE01) VAA07 ,A.RAS06 ,A.RAS07 ,A.RAS08 ,isnull(A.RAS09, 0) RAS09 ,isnull(A.RAS10, 10) RAS10 ,A.RAS11 ,A.RAS12 ,A.BCE01A ,A.BCE03A ,A.RAS15 ,A.BCE01B ,A.BCE03B ,A.RAS18 ,C.VAE02 ,B.VAA04 ,B.VAA05 ,C.VAE19 ,D.BCK03 AS BCK03D from VAE1 c JOIN VAA1 B ON C.VAA01=B.VAA01 LEFT JOIN RAS1 A ON A.VAA07=C.VAE01 and A.ACF01=2 LEFT JOIN BCK1 D ON C.BCK01D=D.BCK01 where exists(select * from VAQ1 a join CAF1 b on a.CAD01=b.CAD01 join CAC1 c on b.CAC01=c.CAC01 where c.CAC02=''死亡'') ', Null, 0, 1, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' 39EA76AF3ED4A6E6FFEFB4F06317C6FF select A.RAS01 ,A.ADB01 ,ISNULL(A.ACF01, 2) ACF01 ,ISNULL(A.VAA01, B.VAA01) VAA01 ,ISNULL(A.VAA07, C.VAE01) VAA07 ,A.RAS06 ,A.RAS07 ,A.RAS08 ,isnull(A.RAS09, 0) RAS09 ,isnull(A.RAS10, 10) RAS10 ,A.RAS11 ,A.RAS12 ,A.BCE01A ,A.BCE03A ,A.RAS15 ,A.BCE01B ,A.BCE03B ,A.RAS18 ,C.VAE02 ,B.VAA04 ,B.VAA05 ,C.VAE19 ,D.BCK03 AS BCK03D from VAE1 c JOIN VAA1 B ON C.VAA01=B.VAA01 LEFT JOIN RAS1 A ON A.VAA07=C.VAE01 and A.ACF01=2 LEFT JOIN BCK1 D ON C.BCK01D=D.BCK01 where exists(select * from VAQ1 a join CAF1 b on a.CAD01=b.CAD01 join CAC1 c on b.CAC01=c.CAC01 where c.CAC02=''死亡'') ' where Code='EMR.REVIEW.000013' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.REVIEW.000014') begin --插入 Declare @Report_Id_1042 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1042 out Declare @Report_Id_1042_CateID int Set @Report_Id_1042_CateID = (select id from Report_Categories where code='EMR.REVIEW') 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_1042, 'EMR.REVIEW.000014', '交班病历分析', Null, Null, @Report_Id_1042_CateID, 1, 4, 600, 0, Null, '2014-04-04 13:22:09', '2014-04-25 15:29:50', Null, ' 72F775D758494D1FBF4EF3CD861463D6 select A.RAS01 ,A.ADB01 ,ISNULL(A.ACF01, 2) ACF01 ,ISNULL(A.VAA01, B.VAA01) VAA01 ,ISNULL(A.VAA07, C.VAE01) VAA07 ,A.RAS06 ,A.RAS07 ,A.RAS08 ,isnull(A.RAS09, 0) RAS09 ,isnull(A.RAS10, 10) RAS10 ,A.RAS11 ,A.RAS12 ,A.BCE01A ,A.BCE03A ,A.RAS15 ,A.BCE01B ,A.BCE03B ,A.RAS18 ,C.VAE02 ,B.VAA04 ,B.VAA05 ,C.VAE19 ,D.BCK03 AS BCK03D from VAE1 c JOIN VAA1 B ON C.VAA01=B.VAA01 LEFT JOIN RAS1 A ON A.VAA07=C.VAE01 and A.ACF01=2 LEFT JOIN BCK1 D ON C.BCK01D=D.BCK01 where exists(select * from VAQ1 a join CAF1 b on a.CAD01=b.CAD01 join CAC1 c on b.CAC01=c.CAC01 where c.CAC02=''交班'') ', Null, 0, 1, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' 72F775D758494D1FBF4EF3CD861463D6 select A.RAS01 ,A.ADB01 ,ISNULL(A.ACF01, 2) ACF01 ,ISNULL(A.VAA01, B.VAA01) VAA01 ,ISNULL(A.VAA07, C.VAE01) VAA07 ,A.RAS06 ,A.RAS07 ,A.RAS08 ,isnull(A.RAS09, 0) RAS09 ,isnull(A.RAS10, 10) RAS10 ,A.RAS11 ,A.RAS12 ,A.BCE01A ,A.BCE03A ,A.RAS15 ,A.BCE01B ,A.BCE03B ,A.RAS18 ,C.VAE02 ,B.VAA04 ,B.VAA05 ,C.VAE19 ,D.BCK03 AS BCK03D from VAE1 c JOIN VAA1 B ON C.VAA01=B.VAA01 LEFT JOIN RAS1 A ON A.VAA07=C.VAE01 and A.ACF01=2 LEFT JOIN BCK1 D ON C.BCK01D=D.BCK01 where exists(select * from VAQ1 a join CAF1 b on a.CAD01=b.CAD01 join CAC1 c on b.CAC01=c.CAC01 where c.CAC02=''交班'') ' where Code='EMR.REVIEW.000014' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.REVIEW.000015') begin --插入 Declare @Report_Id_1043 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1043 out Declare @Report_Id_1043_CateID int Set @Report_Id_1043_CateID = (select id from Report_Categories where code='EMR.REVIEW') 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_1043, 'EMR.REVIEW.000015', '手术病历分析', Null, Null, @Report_Id_1043_CateID, 1, 4, 600, 0, Null, '2014-04-04 13:22:52', '2014-04-25 15:27:58', Null, ' 29473029461CD207B8A2E91E31FABD27 select A.RAS01 ,A.ADB01 ,ISNULL(A.ACF01, 2) ACF01 ,ISNULL(A.VAA01, B.VAA01) VAA01 ,ISNULL(A.VAA07, C.VAE01) VAA07 ,A.RAS06 ,A.RAS07 ,A.RAS08 ,isnull(A.RAS09, 0) RAS09 ,isnull(A.RAS10, 10) RAS10 ,A.RAS11 ,A.RAS12 ,A.BCE01A ,A.BCE03A ,A.RAS15 ,A.BCE01B ,A.BCE03B ,A.RAS18 ,C.VAE02 ,B.VAA04 ,B.VAA05 ,C.VAE19 ,D.BCK03 AS BCK03D from VAE1 c JOIN VAA1 B ON C.VAA01=B.VAA01 LEFT JOIN RAS1 A ON A.VAA07=C.VAE01 and A.ACF01=2 LEFT JOIN BCK1 D ON C.BCK01D=D.BCK01 where exists(select * from VAQ1 a join CAF1 b on a.CAD01=b.CAD01 join CAC1 c on b.CAC01=c.CAC01 where c.CAC02=''手术'') ', Null, 0, 1, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' 29473029461CD207B8A2E91E31FABD27 select A.RAS01 ,A.ADB01 ,ISNULL(A.ACF01, 2) ACF01 ,ISNULL(A.VAA01, B.VAA01) VAA01 ,ISNULL(A.VAA07, C.VAE01) VAA07 ,A.RAS06 ,A.RAS07 ,A.RAS08 ,isnull(A.RAS09, 0) RAS09 ,isnull(A.RAS10, 10) RAS10 ,A.RAS11 ,A.RAS12 ,A.BCE01A ,A.BCE03A ,A.RAS15 ,A.BCE01B ,A.BCE03B ,A.RAS18 ,C.VAE02 ,B.VAA04 ,B.VAA05 ,C.VAE19 ,D.BCK03 AS BCK03D from VAE1 c JOIN VAA1 B ON C.VAA01=B.VAA01 LEFT JOIN RAS1 A ON A.VAA07=C.VAE01 and A.ACF01=2 LEFT JOIN BCK1 D ON C.BCK01D=D.BCK01 where exists(select * from VAQ1 a join CAF1 b on a.CAD01=b.CAD01 join CAC1 c on b.CAC01=c.CAC01 where c.CAC02=''手术'') ' where Code='EMR.REVIEW.000015' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.REVIEW.000016') begin --插入 Declare @Report_Id_1044 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1044 out Declare @Report_Id_1044_CateID int Set @Report_Id_1044_CateID = (select id from Report_Categories where code='EMR.REVIEW') 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_1044, 'EMR.REVIEW.000016', '危重病历分析', Null, Null, @Report_Id_1044_CateID, 1, 4, 600, 0, Null, '2014-04-04 13:23:19', '2014-04-25 15:28:10', Null, ' A2FDAC1C4A79A48957032454116C1A16 select A.RAS01 ,A.ADB01 ,ISNULL(A.ACF01, 2) ACF01 ,ISNULL(A.VAA01, B.VAA01) VAA01 ,ISNULL(A.VAA07, C.VAE01) VAA07 ,A.RAS06 ,A.RAS07 ,A.RAS08 ,isnull(A.RAS09, 0) RAS09 ,isnull(A.RAS10, 10) RAS10 ,A.RAS11 ,A.RAS12 ,A.BCE01A ,A.BCE03A ,A.RAS15 ,A.BCE01B ,A.BCE03B ,A.RAS18 ,C.VAE02 ,B.VAA04 ,B.VAA05 ,C.VAE19 ,D.BCK03 AS BCK03D from VAE1 c JOIN VAA1 B ON C.VAA01=B.VAA01 LEFT JOIN RAS1 A ON A.VAA07=C.VAE01 and A.ACF01=2 LEFT JOIN BCK1 D ON C.BCK01D=D.BCK01 where exists(select * from VAQ1 a join CAF1 b on a.CAD01=b.CAD01 join CAC1 c on b.CAC01=c.CAC01 where c.CAC02=''抢救'') ', Null, 0, 1, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' A2FDAC1C4A79A48957032454116C1A16 select A.RAS01 ,A.ADB01 ,ISNULL(A.ACF01, 2) ACF01 ,ISNULL(A.VAA01, B.VAA01) VAA01 ,ISNULL(A.VAA07, C.VAE01) VAA07 ,A.RAS06 ,A.RAS07 ,A.RAS08 ,isnull(A.RAS09, 0) RAS09 ,isnull(A.RAS10, 10) RAS10 ,A.RAS11 ,A.RAS12 ,A.BCE01A ,A.BCE03A ,A.RAS15 ,A.BCE01B ,A.BCE03B ,A.RAS18 ,C.VAE02 ,B.VAA04 ,B.VAA05 ,C.VAE19 ,D.BCK03 AS BCK03D from VAE1 c JOIN VAA1 B ON C.VAA01=B.VAA01 LEFT JOIN RAS1 A ON A.VAA07=C.VAE01 and A.ACF01=2 LEFT JOIN BCK1 D ON C.BCK01D=D.BCK01 where exists(select * from VAQ1 a join CAF1 b on a.CAD01=b.CAD01 join CAC1 c on b.CAC01=c.CAC01 where c.CAC02=''抢救'') ' where Code='EMR.REVIEW.000016' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.REVIEW.000002') begin --插入 Declare @Report_Id_1099 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1099 out Declare @Report_Id_1099_CateID int Set @Report_Id_1099_CateID = (select id from Report_Categories where code='EMR.REVIEW') 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_1099, 'EMR.REVIEW.000002', '检索住院病人', '', Null, @Report_Id_1099_CateID, 1, 4, 600, 0, Null, '2012-12-29 14:23:07', '2013-04-26 16:14:36', Null, ' 1F1B9072F7730581817063395330FD2CDECLARE @VAA07 INT, @itype int, @vae44 int set @VAA07 = :VAA07 set @itype = :itype set @vae44 = :vae44 select * from ( select A.RAS01 ,A.ADB01 ,ISNULL(A.ACF01, 2) ACF01 ,ISNULL(A.VAA01, B.VAA01) VAA01 ,ISNULL(A.VAA07, C.VAE01) VAA07 ,A.RAS06 ,A.RAS07 ,A.RAS08 ,isnull(A.RAS09, 0) RAS09 ,isnull(A.RAS10, 10) RAS10 ,A.RAS11 ,A.RAS12 ,A.BCE01A ,A.BCE03A ,A.RAS15 ,A.BCE01B ,A.BCE03B ,A.RAS18 ,C.VAE02 ,B.VAA04 ,B.VAA05 ,C.VAE19 ,D.BCK03 AS BCK03D from VAE1 c JOIN VAA1 B ON C.VAA01=B.VAA01 LEFT JOIN RAS1 A ON A.VAA07=C.VAE01 and A.ACF01=2 LEFT JOIN BCK1 D ON C.BCK01D=D.BCK01 where (c.VAE01=@VAA07 or @VAA07=0) and VAE44<=@vae44 ) as V_RAS where (@itype=0 or (@itype=1 and RAS10=10) or (@itype=2 and RAS10=0) or (@itype=3 and RAS10=1))3=仅在院病人 5=所有病人1=等待评分 2=进行中 3=已完成<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid RecNo="1" Head="1"><Sql>SELECT * FROM VAA1 a join VAE1 b on a.VAA01=b.VAA01 and VAE44 &lt;= &lt;vae44&gt;</Sql><HD><Ie FD="VAA04" CA="住院号" WD="50"/><Ie FD="VAA05" CA="姓名" WD="50"/><Ie FD="VAE11" CA="入院时间" WD="50"/><Ie FD="VAE26" CA="出院时间" WD="50"/></HD><QF><Ie FD="VAA04" WE="7"/><Ie FD="VAA05" WE="7"/><Ie FD="ABBRP" WE="7" CT="2"/><Ie FD="ABBRW" WE="7" CT="1"/></QF></Grid></Configs> ', Null, 0, 1, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' 1F1B9072F7730581817063395330FD2CDECLARE @VAA07 INT, @itype int, @vae44 int set @VAA07 = :VAA07 set @itype = :itype set @vae44 = :vae44 select * from ( select A.RAS01 ,A.ADB01 ,ISNULL(A.ACF01, 2) ACF01 ,ISNULL(A.VAA01, B.VAA01) VAA01 ,ISNULL(A.VAA07, C.VAE01) VAA07 ,A.RAS06 ,A.RAS07 ,A.RAS08 ,isnull(A.RAS09, 0) RAS09 ,isnull(A.RAS10, 10) RAS10 ,A.RAS11 ,A.RAS12 ,A.BCE01A ,A.BCE03A ,A.RAS15 ,A.BCE01B ,A.BCE03B ,A.RAS18 ,C.VAE02 ,B.VAA04 ,B.VAA05 ,C.VAE19 ,D.BCK03 AS BCK03D from VAE1 c JOIN VAA1 B ON C.VAA01=B.VAA01 LEFT JOIN RAS1 A ON A.VAA07=C.VAE01 and A.ACF01=2 LEFT JOIN BCK1 D ON C.BCK01D=D.BCK01 where (c.VAE01=@VAA07 or @VAA07=0) and VAE44<=@vae44 ) as V_RAS where (@itype=0 or (@itype=1 and RAS10=10) or (@itype=2 and RAS10=0) or (@itype=3 and RAS10=1))3=仅在院病人 5=所有病人1=等待评分 2=进行中 3=已完成<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid RecNo="1" Head="1"><Sql>SELECT * FROM VAA1 a join VAE1 b on a.VAA01=b.VAA01 and VAE44 &lt;= &lt;vae44&gt;</Sql><HD><Ie FD="VAA04" CA="住院号" WD="50"/><Ie FD="VAA05" CA="姓名" WD="50"/><Ie FD="VAE11" CA="入院时间" WD="50"/><Ie FD="VAE26" CA="出院时间" WD="50"/></HD><QF><Ie FD="VAA04" WE="7"/><Ie FD="VAA05" WE="7"/><Ie FD="ABBRP" WE="7" CT="2"/><Ie FD="ABBRW" WE="7" CT="1"/></QF></Grid></Configs> ' where Code='EMR.REVIEW.000002' end GO --delete from Report_Objects where CategoryID=100 /*质控报表*/ IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.QC.000001') begin --插入 Declare @Report_Id_1134 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1134 out Declare @Report_Id_1134_CateID int Set @Report_Id_1134_CateID = (select id from Report_Categories where code='EMR.QC') 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_1134, 'EMR.QC.000001', '住院病历质控', Null, Null, @Report_Id_1134_CateID, 1, 4, 600, 0, Null, '2014-04-24 16:21:51', '2015-06-11 17:02:37', Null, ' 6F7D304EF6038BD1FBF26920DAD7124Adeclare @qad07 VARCHAR(50) , @VAA07 int,@BDate datetime, @Edate datetime declare @qad08 varchar(64) declare @bck01 VARCHAR(500) set @qad07=isnull(:qad07,''-1'') set @bck01=:bck01 set @bck01=CASE isnull(@bck01,'''')WHEN '''' THEN ''0'' ELSE @bck01 END set @VAA07=:VAA07 set @BDate=:Bdate set @Edate=:Edate declare @userLevel int, @一二级质控员使用主治主任医生 int set @一二级质控员使用主治主任医生= isnull((SELECT value FROM dbo.SYS_Parameters WHERE ProductID=600 AND paramno=52),1) SELECT @userLevel =case when aaa02=''三'' then 3 when aaa02=''二'' then 2 when aaa02=''一'' then 1 end from (select TOP(1)baa1.AAA01,SUBSTRING(aaa02,1,1) as AAA02 FROM baa1 JOIN aaa1 ON baa1.AAA01=aaa1.AAA01 WHERE bce01 =@$EmpID and aaa02 LIKE ''__质控员'' ORDER BY CHARINDEX(SUBSTRING(aaa02,1,1),''一,二,三'') desc )t --下面注释代码可以过滤一二级质控员只显示质控员的业务科室 需要的请去掉注释 /*if @BCK01=''0'' and @userLevel<3 begin declare @_bck01s varchar(4000) set @_bck01s=( SELECT cast(bck01 as varchar(20))+'','' FROM BCK1 a Where EXISTS(SELECT b.* FROM BAZ1 b WHERE a.BCK01=b.BCK01 AND b.BAU01 IN (''01'') AND b.ACF01 IN(2,3)) AND EXISTS(SELECT c.* FROM BDR1 c WHERE a.BCK01 = c.BCK01 AND c.BCE01 = @$EmpId ) ORDER BY A.BCK03 for xml path('''')) set @BCK01= substring(@_bck01s,1,len(@_bck01s)-1) end */ SELECT a.QAD01, a.QAD01B,vaq1.QAD01 as QAD01C,vaq1.BCK01,case when VAE44<=3 then ''在院 '' else ''出院 '' end+ bck1.BCK03 BCK03, a.VAA01,VAA04,right('' ''+C.BCQ04B,4)+''床 ''+VAA05+case vaa1.abw01 when 1 then '' 男 '' when 2 then '' 女 '' else '' '' end +convert(varchar(10),c.VAE46)+c.AAU01+'' ''+VAA04 as VAA05,VAA05 as VAA05A,vaq1.VAA07,VAE02,a.QAD01A, a.QAD07, a.QAD08,VAQ1.CAD04 QAD09,a.QAD10,a.BCE01 as BCE01B,a.BCE03 as BCE03B ,VAQ1.CAD01, vaq1.BCE01,VAQ1.BCE03A,a.QAD13 ,VAE01,VAQ1.VAQ01,a.ACF01,VAQ1.CAD04,VAQ20, vaq1.BCE03C,VAQ19,case when (vaq1.CAB01 = 3 OR vaq1.CAB01=4) then VAM35 else case @一二级质控员使用主治主任医生 when 1 then VAM27 else VAMA5 end end as VAM27, case @一二级质控员使用主治主任医生 when 1 then VAM31 else VAMA4 end as VAM31,vaq1.VAQ01A into #a FROM qad1 a JOIN vaq1 ON a.QAD01=vaq1.QAD01 JOIN vaa1 ON vaa1.VAA01=vaq1.VAA01 JOIN VAE1 C ON vaq1.VAA07=C.VAE01 left join VAM1 m on vaq1.vaa07=m.vaa07 LEFT JOIN bck1 ON c.BCK01D=bck1.BCK01 WHERE (@VAA07=0 or vaq1.VAA07=@VAA07) and (@qad07=''-1'' or a.qad07 in (select * from split(@qad07,'','')))--not exists(select * from qad1 q where q.VAA01=c.VAA01 and q.acf01=2 and q.qad07<@qad07)) AND (vaq1.bck01 in (select * from split(@bck01,'','')) or @bck01=''0'') and ((@$EmpName in (case when (vaq1.CAB01 = 3 OR vaq1.CAB01=4) then VAM35 else case @一二级质控员使用主治主任医生 when 1 then VAM27 else VAMA5 end end , case @一二级质控员使用主治主任医生 when 1 then VAM31 else VAMA4 end ) and vaq1.cab01 in(2,6)) or (@userLevel=2 and vaq1.cab01=4) or (@userLevel=3 and VAE44>=4) ) and (VAE44 in(1,2,3) or VAE26 between @Bdate and @Edate) and vaq1.vaq20 in (1,2) and VAQ21<=1 union SELECT 0 QAD01,0 QAD01B,vaq1.QAD01 as QAD01C,vaq1.BCK01,case when VAE44<=3 then ''在院 '' else ''出院 '' end+ bck1.BCK03 BCK03, c.VAA01,VAA04,right('' ''+C.BCQ04B,4)+''床 ''+VAA05+case vaa1.abw01 when 1 then '' 男 '' when 2 then '' 女 '' else '' '' end +convert(varchar(10),c.VAE46)+c.AAU01+'' ''+VAA04 as VAA05, VAA05 as VAA05A,vaq1.VAA07,VAE02,0 QAD01A, 0 QAD07, null QAD08,VAQ1.CAD04+''(全部，仅显示)'' QAD09,null QAD10,null as BCE01B,null as BCE03B ,VAQ1.CAD01, vaq1.BCE01,VAQ1.BCE03A,null QAD13 ,VAE01,VAQ1.VAQ01,2 ACF01,VAQ1.CAD04,VAQ20, vaq1.BCE03C,VAQ19, case when (vaq1.CAB01 = 3 OR vaq1.CAB01=4) then VAM35 else case @一二级质控员使用主治主任医生 when 1 then VAM27 else VAMA5 end end as VAM27, case @一二级质控员使用主治主任医生 when 1 then VAM31 else VAMA4 end as VAM31 ,vaq1.VAQ01A FROM VAQ1 JOIN vaa1 ON vaa1.VAA01=vaq1.VAA01 JOIN VAE1 C ON vaq1.VAA07=C.VAE01 left join VAM1 m on vaq1.vaa07=m.vaa07 LEFT JOIN bck1 ON c.BCK01D=bck1.BCK01 WHERE (vaq1.VAA07=@VAA07 or @VAA07=0) AND (vaq1.bck01 in (select * from split(@bck01,'','')) or @bck01=''0'') and (@userLevel=3 and VAE44>=4 and @QAD07=''-1'') and (VAE26 between @Bdate and @Edate) and vaq1.vaq20=3 and VAQ21=2 select BCK03,count(distinct VAA07) p into #b from #a group by BCK03 update a set a.BCK03=a.BCK03+''(''+convert(varchar(10),p)+''人)'' from #a a join #b b on a.BCK03=b.BCK03 alter table #a add stateImage image if @userLevel=3 select a.* ,b.ADC01,e.ADC05 from #a a left join CAD1 b on a.CAD01=b.CAD01 left join ADC1 e on b.ADC01=e.adc01 left join CBV1 c on b.CBT01=c.CBT01 and exists(select * from CBU1 where CBU01=c.CBU01 and CBU05=2) order by a.BCK03,a.VAA05,c.ROWNR,b.CBT01,a.VAQ01 else select a.* ,b.ADC01,e.ADC05 from #a a left join CAD1 b on a.CAD01=b.CAD01 left join ADC1 e on b.ADC01=e.adc01 order by a.BCK03,a.VAA05,b.CBT01,a.VAQ01 drop table #a drop table #b<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid><Sql>select * from BCK1</Sql><HD><Ie FD="BCK03" WD="100"/></HD><QF><Ie FD="BCK03" WE="7"/><Ie FD="BCK01" WE="7"/><Ie FD="ABBRP" WE="7"/></QF></Grid></Configs><?xml version="1.0"?> <Configs Ver="1"><Win WD="500" SZ="1"/><Grid Head="1"><Sql>select VAE01,VAA04,VAA05,VAE11,VAE26,a.BCQ04B from VAE1 a join VAA1 b on a.VAA01=b.VAA01</Sql><HD><Ie FD="VAA04" CA="住院号" WD="100"/><Ie FD="VAA05" CA="姓名" WD="100"/><Ie FD="VAE11" CA="入院时间" WD="100"/><Ie FD="VAE26" CA="出院时间" WD="100"/><Ie FD="BCQ04B" CA="床位" WD="100"/></HD><QF><Ie FD="VAA04" WE="7"/><Ie FD="VAA05" WE="7"/><Ie FD="abbrp" WE="7"/></QF></Grid></Configs>-1=自动选择（全部） 3=三级质控（病案室已质控） 2=二级质控（主任医师已质控） 1=一级质控（主治医师已质控） 0=未质控 ', Null, 0, 0, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' 6F7D304EF6038BD1FBF26920DAD7124Adeclare @qad07 VARCHAR(50) , @VAA07 int,@BDate datetime, @Edate datetime declare @qad08 varchar(64) declare @bck01 VARCHAR(500) set @qad07=isnull(:qad07,''-1'') set @bck01=:bck01 set @bck01=CASE isnull(@bck01,'''')WHEN '''' THEN ''0'' ELSE @bck01 END set @VAA07=:VAA07 set @BDate=:Bdate set @Edate=:Edate declare @userLevel int, @一二级质控员使用主治主任医生 int set @一二级质控员使用主治主任医生= isnull((SELECT value FROM dbo.SYS_Parameters WHERE ProductID=600 AND paramno=52),1) SELECT @userLevel =case when aaa02=''三'' then 3 when aaa02=''二'' then 2 when aaa02=''一'' then 1 end from (select TOP(1)baa1.AAA01,SUBSTRING(aaa02,1,1) as AAA02 FROM baa1 JOIN aaa1 ON baa1.AAA01=aaa1.AAA01 WHERE bce01 =@$EmpID and aaa02 LIKE ''__质控员'' ORDER BY CHARINDEX(SUBSTRING(aaa02,1,1),''一,二,三'') desc )t --下面注释代码可以过滤一二级质控员只显示质控员的业务科室 需要的请去掉注释 /*if @BCK01=''0'' and @userLevel<3 begin declare @_bck01s varchar(4000) set @_bck01s=( SELECT cast(bck01 as varchar(20))+'','' FROM BCK1 a Where EXISTS(SELECT b.* FROM BAZ1 b WHERE a.BCK01=b.BCK01 AND b.BAU01 IN (''01'') AND b.ACF01 IN(2,3)) AND EXISTS(SELECT c.* FROM BDR1 c WHERE a.BCK01 = c.BCK01 AND c.BCE01 = @$EmpId ) ORDER BY A.BCK03 for xml path('''')) set @BCK01= substring(@_bck01s,1,len(@_bck01s)-1) end */ SELECT a.QAD01, a.QAD01B,vaq1.QAD01 as QAD01C,vaq1.BCK01,case when VAE44<=3 then ''在院 '' else ''出院 '' end+ bck1.BCK03 BCK03, a.VAA01,VAA04,right('' ''+C.BCQ04B,4)+''床 ''+VAA05+case vaa1.abw01 when 1 then '' 男 '' when 2 then '' 女 '' else '' '' end +convert(varchar(10),c.VAE46)+c.AAU01+'' ''+VAA04 as VAA05,VAA05 as VAA05A,vaq1.VAA07,VAE02,a.QAD01A, a.QAD07, a.QAD08,VAQ1.CAD04 QAD09,a.QAD10,a.BCE01 as BCE01B,a.BCE03 as BCE03B ,VAQ1.CAD01, vaq1.BCE01,VAQ1.BCE03A,a.QAD13 ,VAE01,VAQ1.VAQ01,a.ACF01,VAQ1.CAD04,VAQ20, vaq1.BCE03C,VAQ19,case when (vaq1.CAB01 = 3 OR vaq1.CAB01=4) then VAM35 else case @一二级质控员使用主治主任医生 when 1 then VAM27 else VAMA5 end end as VAM27, case @一二级质控员使用主治主任医生 when 1 then VAM31 else VAMA4 end as VAM31,vaq1.VAQ01A into #a FROM qad1 a JOIN vaq1 ON a.QAD01=vaq1.QAD01 JOIN vaa1 ON vaa1.VAA01=vaq1.VAA01 JOIN VAE1 C ON vaq1.VAA07=C.VAE01 left join VAM1 m on vaq1.vaa07=m.vaa07 LEFT JOIN bck1 ON c.BCK01D=bck1.BCK01 WHERE (@VAA07=0 or vaq1.VAA07=@VAA07) and (@qad07=''-1'' or a.qad07 in (select * from split(@qad07,'','')))--not exists(select * from qad1 q where q.VAA01=c.VAA01 and q.acf01=2 and q.qad07<@qad07)) AND (vaq1.bck01 in (select * from split(@bck01,'','')) or @bck01=''0'') and ((@$EmpName in (case when (vaq1.CAB01 = 3 OR vaq1.CAB01=4) then VAM35 else case @一二级质控员使用主治主任医生 when 1 then VAM27 else VAMA5 end end , case @一二级质控员使用主治主任医生 when 1 then VAM31 else VAMA4 end ) and vaq1.cab01 in(2,6)) or (@userLevel=2 and vaq1.cab01=4) or (@userLevel=3 and VAE44>=4) ) and (VAE44 in(1,2,3) or VAE26 between @Bdate and @Edate) and vaq1.vaq20 in (1,2) and VAQ21<=1 union SELECT 0 QAD01,0 QAD01B,vaq1.QAD01 as QAD01C,vaq1.BCK01,case when VAE44<=3 then ''在院 '' else ''出院 '' end+ bck1.BCK03 BCK03, c.VAA01,VAA04,right('' ''+C.BCQ04B,4)+''床 ''+VAA05+case vaa1.abw01 when 1 then '' 男 '' when 2 then '' 女 '' else '' '' end +convert(varchar(10),c.VAE46)+c.AAU01+'' ''+VAA04 as VAA05, VAA05 as VAA05A,vaq1.VAA07,VAE02,0 QAD01A, 0 QAD07, null QAD08,VAQ1.CAD04+''(全部，仅显示)'' QAD09,null QAD10,null as BCE01B,null as BCE03B ,VAQ1.CAD01, vaq1.BCE01,VAQ1.BCE03A,null QAD13 ,VAE01,VAQ1.VAQ01,2 ACF01,VAQ1.CAD04,VAQ20, vaq1.BCE03C,VAQ19, case when (vaq1.CAB01 = 3 OR vaq1.CAB01=4) then VAM35 else case @一二级质控员使用主治主任医生 when 1 then VAM27 else VAMA5 end end as VAM27, case @一二级质控员使用主治主任医生 when 1 then VAM31 else VAMA4 end as VAM31 ,vaq1.VAQ01A FROM VAQ1 JOIN vaa1 ON vaa1.VAA01=vaq1.VAA01 JOIN VAE1 C ON vaq1.VAA07=C.VAE01 left join VAM1 m on vaq1.vaa07=m.vaa07 LEFT JOIN bck1 ON c.BCK01D=bck1.BCK01 WHERE (vaq1.VAA07=@VAA07 or @VAA07=0) AND (vaq1.bck01 in (select * from split(@bck01,'','')) or @bck01=''0'') and (@userLevel=3 and VAE44>=4 and @QAD07=''-1'') and (VAE26 between @Bdate and @Edate) and vaq1.vaq20=3 and VAQ21=2 select BCK03,count(distinct VAA07) p into #b from #a group by BCK03 update a set a.BCK03=a.BCK03+''(''+convert(varchar(10),p)+''人)'' from #a a join #b b on a.BCK03=b.BCK03 alter table #a add stateImage image if @userLevel=3 select a.* ,b.ADC01,e.ADC05 from #a a left join CAD1 b on a.CAD01=b.CAD01 left join ADC1 e on b.ADC01=e.adc01 left join CBV1 c on b.CBT01=c.CBT01 and exists(select * from CBU1 where CBU01=c.CBU01 and CBU05=2) order by a.BCK03,a.VAA05,c.ROWNR,b.CBT01,a.VAQ01 else select a.* ,b.ADC01,e.ADC05 from #a a left join CAD1 b on a.CAD01=b.CAD01 left join ADC1 e on b.ADC01=e.adc01 order by a.BCK03,a.VAA05,b.CBT01,a.VAQ01 drop table #a drop table #b<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid><Sql>select * from BCK1</Sql><HD><Ie FD="BCK03" WD="100"/></HD><QF><Ie FD="BCK03" WE="7"/><Ie FD="BCK01" WE="7"/><Ie FD="ABBRP" WE="7"/></QF></Grid></Configs><?xml version="1.0"?> <Configs Ver="1"><Win WD="500" SZ="1"/><Grid Head="1"><Sql>select VAE01,VAA04,VAA05,VAE11,VAE26,a.BCQ04B from VAE1 a join VAA1 b on a.VAA01=b.VAA01</Sql><HD><Ie FD="VAA04" CA="住院号" WD="100"/><Ie FD="VAA05" CA="姓名" WD="100"/><Ie FD="VAE11" CA="入院时间" WD="100"/><Ie FD="VAE26" CA="出院时间" WD="100"/><Ie FD="BCQ04B" CA="床位" WD="100"/></HD><QF><Ie FD="VAA04" WE="7"/><Ie FD="VAA05" WE="7"/><Ie FD="abbrp" WE="7"/></QF></Grid></Configs>-1=自动选择（全部） 3=三级质控（病案室已质控） 2=二级质控（主任医师已质控） 1=一级质控（主治医师已质控） 0=未质控 ' where Code='EMR.QC.000001' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.QC.000002') begin --插入 Declare @Report_Id_1135 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1135 out Declare @Report_Id_1135_CateID int Set @Report_Id_1135_CateID = (select id from Report_Categories where code='EMR.QC') 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_1135, 'EMR.QC.000002', '护理病历质控', Null, Null, @Report_Id_1135_CateID, 1, 4, 600, 0, Null, '2014-04-24 16:21:51', '2015-06-11 17:02:21', Null, ' B6E5DAACB8C0279874C5C8A756563B99declare @qad07 VARCHAR(50) , @VAA07 int,@BDate datetime, @Edate datetime declare @qad08 varchar(64) declare @bck01 VARCHAR(500) set @qad07=isnull(:qad07,''-1'') set @bck01=:bck01 set @bck01=CASE isnull(@bck01,'''')WHEN '''' THEN ''0'' ELSE @bck01 END set @VAA07=:VAA07 set @BDate=:Bdate set @Edate=:Edate declare @userLevel int, @一二级质控员使用主治主任医生 int, @usertype varchar(10) set @usertype= (select top (1) aaa02 FROM baa1 JOIN aaa1 ON baa1.AAA01=aaa1.AAA01 WHERE bce01 =@$empID and aaa02 in (''护士'',''医生'') order by CHARINDEX(aaa02,''医生,护士'')) set @一二级质控员使用主治主任医生= isnull((SELECT value FROM dbo.SYS_Parameters WHERE ProductID=600 AND paramno=52),1) SELECT @userLevel =case when aaa02=''三'' then 3 when aaa02=''二'' then 2 when aaa02=''一'' then 1 end from (select TOP(1)baa1.AAA01,SUBSTRING(aaa02,1,1) as AAA02 FROM baa1 JOIN aaa1 ON baa1.AAA01=aaa1.AAA01 WHERE bce01 =@$EmpID and aaa02 LIKE ''__质控员'' ORDER BY CHARINDEX(SUBSTRING(aaa02,1,1),''一,二,三'') desc )t SELECT a.QAD01, a.QAD01B,vaq1.QAD01 as QAD01C,vaq1.BCK01,case when VAE44<=3 then ''在院 '' else ''出院 '' end+ bck1.BCK03 BCK03, a.VAA01,VAA04,right('' ''+C.BCQ04B,4)+''床 ''+VAA05+case vaa1.abw01 when 1 then '' 男 '' when 2 then '' 女 '' else '' '' end +convert(varchar(10),c.VAE46)+c.AAU01+'' ''+VAA04 as VAA05,VAA05 as VAA05A,vaq1.VAA07,VAE02,a.QAD01A, a.QAD07, a.QAD08,VAQ1.CAD04 QAD09,a.QAD10,a.BCE01 as BCE01B,a.BCE03 as BCE03B ,VAQ1.CAD01, vaq1.BCE01,VAQ1.BCE03A,a.QAD13 ,VAE01,VAQ1.VAQ01,a.ACF01,VAQ1.CAD04,VAQ20, vaq1.BCE03C,VAQ19,case when (vaq1.CAB01 = 3 OR vaq1.CAB01=4) then VAM35 else case @一二级质控员使用主治主任医生 when 1 then VAM27 else VAMA5 end end as VAM27, case @一二级质控员使用主治主任医生 when 1 then VAM31 else VAMA4 end as VAM31 ,VAQ1.CAB01 ,vaq1.VAQ01A into #a FROM qad1 a JOIN vaq1 ON a.QAD01=vaq1.QAD01 JOIN vaa1 ON vaa1.VAA01=vaq1.VAA01 JOIN VAE1 C ON vaq1.VAA07=C.VAE01 left join VAM1 m on vaq1.vaa07=m.vaa07 LEFT JOIN bck1 ON c.BCK01D=bck1.BCK01 WHERE (@VAA07=0 or vaq1.VAA07=@VAA07) and (@qad07=''-1'' or a.qad07 in (select * from split(@qad07,'','')))--not exists(select * from qad1 q where q.VAA01=c.VAA01 and q.acf01=2 and q.qad07<@qad07)) AND (vaq1.bck01 in (select * from split(@bck01,'','')) or @bck01=''0'') and ((@$EmpName in (case when (vaq1.CAB01 = 3 OR vaq1.CAB01=4) then VAM35 else case @一二级质控员使用主治主任医生 when 1 then VAM27 else VAMA5 end end , case @一二级质控员使用主治主任医生 when 1 then VAM31 else VAMA4 end ) and vaq1.cab01 in(2,3,4,6)) or (@userLevel=2 and vaq1.cab01=4) or (@userLevel=3 and VAE44>=4) ) and (VAE44 in(1,2,3) or VAE26 between @Bdate and @Edate) and vaq1.vaq20 in (1,2) and VAQ21<=1 union SELECT 0 QAD01,0 QAD01B,vaq1.QAD01 as QAD01C,vaq1.BCK01,case when VAE44<=3 then ''在院 '' else ''出院 '' end+ bck1.BCK03 BCK03, c.VAA01,VAA04,right('' ''+C.BCQ04B,4)+''床 ''+VAA05+case vaa1.abw01 when 1 then '' 男 '' when 2 then '' 女 '' else '' '' end +convert(varchar(10),c.VAE46)+c.AAU01+'' ''+VAA04 as VAA05, VAA05 as VAA05A,vaq1.VAA07,VAE02,0 QAD01A, 0 QAD07, null QAD08,VAQ1.CAD04+''(全部，仅显示)'' QAD09,null QAD10,null as BCE01B,null as BCE03B ,VAQ1.CAD01, vaq1.BCE01,VAQ1.BCE03A,null QAD13 ,VAE01,VAQ1.VAQ01,2 ACF01,VAQ1.CAD04,VAQ20, vaq1.BCE03C,VAQ19, case when (vaq1.CAB01 = 3 OR vaq1.CAB01=4) then VAM35 else case @一二级质控员使用主治主任医生 when 1 then VAM27 else VAMA5 end end as VAM27, case @一二级质控员使用主治主任医生 when 1 then VAM31 else VAMA4 end as VAM31 ,VAQ1.CAB01 ,vaq1.VAQ01A FROM VAQ1 JOIN vaa1 ON vaa1.VAA01=vaq1.VAA01 JOIN VAE1 C ON vaq1.VAA07=C.VAE01 left join VAM1 m on vaq1.vaa07=m.vaa07 LEFT JOIN bck1 ON c.BCK01D=bck1.BCK01 WHERE (vaq1.VAA07=@VAA07 or @VAA07=0) AND (vaq1.bck01 in (select * from split(@bck01,'','')) or @bck01=''0'') and (@userLevel=3 and VAE44>=4 --and @QAD07=''-1'' ) and (VAE26 between @Bdate and @Edate) and vaq1.vaq20=3 and VAQ21=2 select BCK03,count(distinct VAA07) p into #b from #a group by BCK03 update a set a.BCK03=a.BCK03+''(''+convert(varchar(10),p)+''人)'' from #a a join #b b on a.BCK03=b.BCK03 alter table #a add stateImage image if @usertype=''医生'' begin delete from #a where cab01 in (3,4) end else begin delete from #a where cab01 in (2,6) end if @userLevel=3 select a.* ,b.ADC01,e.ADC05 from #a a left join CAD1 b on a.CAD01=b.CAD01 left join ADC1 e on b.ADC01=e.adc01 left join CBV1 c on b.CBT01=c.CBT01 and exists(select * from CBU1 where CBU01=c.CBU01 and CBU05=2) order by a.BCK03,a.VAA05,c.ROWNR,b.CBT01,a.VAQ01 else select a.* ,b.ADC01,e.ADC05 from #a a left join CAD1 b on a.CAD01=b.CAD01 left join ADC1 e on b.ADC01=e.adc01 order by a.BCK03,a.VAA05,b.CBT01,a.VAQ01 drop table #a drop table #b<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid><Sql>select * from BCK1</Sql><HD><Ie FD="BCK03" WD="100"/></HD><QF><Ie FD="BCK03" WE="7"/><Ie FD="BCK01" WE="7"/><Ie FD="ABBRP" WE="7"/></QF></Grid></Configs><?xml version="1.0"?> <Configs Ver="1"><Win WD="500" SZ="1"/><Grid Head="1"><Sql>select VAE01,VAA04,VAA05,VAE11,VAE26,a.BCQ04B from VAE1 a join VAA1 b on a.VAA01=b.VAA01</Sql><HD><Ie FD="VAA04" CA="住院号" WD="100"/><Ie FD="VAA05" CA="姓名" WD="100"/><Ie FD="VAE11" CA="入院时间" WD="100"/><Ie FD="VAE26" CA="出院时间" WD="100"/><Ie FD="BCQ04B" CA="床位" WD="100"/></HD><QF><Ie FD="VAA04" WE="7"/><Ie FD="VAA05" WE="7"/><Ie FD="abbrp" WE="7"/></QF></Grid></Configs>-1=自动选择（全部） 3=三级质控（病案室已质控） 2=二级质控（主任医师已质控） 1=一级质控（主治医师已质控） 0=未质控 ', Null, 0, 0, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' B6E5DAACB8C0279874C5C8A756563B99declare @qad07 VARCHAR(50) , @VAA07 int,@BDate datetime, @Edate datetime declare @qad08 varchar(64) declare @bck01 VARCHAR(500) set @qad07=isnull(:qad07,''-1'') set @bck01=:bck01 set @bck01=CASE isnull(@bck01,'''')WHEN '''' THEN ''0'' ELSE @bck01 END set @VAA07=:VAA07 set @BDate=:Bdate set @Edate=:Edate declare @userLevel int, @一二级质控员使用主治主任医生 int, @usertype varchar(10) set @usertype= (select top (1) aaa02 FROM baa1 JOIN aaa1 ON baa1.AAA01=aaa1.AAA01 WHERE bce01 =@$empID and aaa02 in (''护士'',''医生'') order by CHARINDEX(aaa02,''医生,护士'')) set @一二级质控员使用主治主任医生= isnull((SELECT value FROM dbo.SYS_Parameters WHERE ProductID=600 AND paramno=52),1) SELECT @userLevel =case when aaa02=''三'' then 3 when aaa02=''二'' then 2 when aaa02=''一'' then 1 end from (select TOP(1)baa1.AAA01,SUBSTRING(aaa02,1,1) as AAA02 FROM baa1 JOIN aaa1 ON baa1.AAA01=aaa1.AAA01 WHERE bce01 =@$EmpID and aaa02 LIKE ''__质控员'' ORDER BY CHARINDEX(SUBSTRING(aaa02,1,1),''一,二,三'') desc )t SELECT a.QAD01, a.QAD01B,vaq1.QAD01 as QAD01C,vaq1.BCK01,case when VAE44<=3 then ''在院 '' else ''出院 '' end+ bck1.BCK03 BCK03, a.VAA01,VAA04,right('' ''+C.BCQ04B,4)+''床 ''+VAA05+case vaa1.abw01 when 1 then '' 男 '' when 2 then '' 女 '' else '' '' end +convert(varchar(10),c.VAE46)+c.AAU01+'' ''+VAA04 as VAA05,VAA05 as VAA05A,vaq1.VAA07,VAE02,a.QAD01A, a.QAD07, a.QAD08,VAQ1.CAD04 QAD09,a.QAD10,a.BCE01 as BCE01B,a.BCE03 as BCE03B ,VAQ1.CAD01, vaq1.BCE01,VAQ1.BCE03A,a.QAD13 ,VAE01,VAQ1.VAQ01,a.ACF01,VAQ1.CAD04,VAQ20, vaq1.BCE03C,VAQ19,case when (vaq1.CAB01 = 3 OR vaq1.CAB01=4) then VAM35 else case @一二级质控员使用主治主任医生 when 1 then VAM27 else VAMA5 end end as VAM27, case @一二级质控员使用主治主任医生 when 1 then VAM31 else VAMA4 end as VAM31 ,VAQ1.CAB01 ,vaq1.VAQ01A into #a FROM qad1 a JOIN vaq1 ON a.QAD01=vaq1.QAD01 JOIN vaa1 ON vaa1.VAA01=vaq1.VAA01 JOIN VAE1 C ON vaq1.VAA07=C.VAE01 left join VAM1 m on vaq1.vaa07=m.vaa07 LEFT JOIN bck1 ON c.BCK01D=bck1.BCK01 WHERE (@VAA07=0 or vaq1.VAA07=@VAA07) and (@qad07=''-1'' or a.qad07 in (select * from split(@qad07,'','')))--not exists(select * from qad1 q where q.VAA01=c.VAA01 and q.acf01=2 and q.qad07<@qad07)) AND (vaq1.bck01 in (select * from split(@bck01,'','')) or @bck01=''0'') and ((@$EmpName in (case when (vaq1.CAB01 = 3 OR vaq1.CAB01=4) then VAM35 else case @一二级质控员使用主治主任医生 when 1 then VAM27 else VAMA5 end end , case @一二级质控员使用主治主任医生 when 1 then VAM31 else VAMA4 end ) and vaq1.cab01 in(2,3,4,6)) or (@userLevel=2 and vaq1.cab01=4) or (@userLevel=3 and VAE44>=4) ) and (VAE44 in(1,2,3) or VAE26 between @Bdate and @Edate) and vaq1.vaq20 in (1,2) and VAQ21<=1 union SELECT 0 QAD01,0 QAD01B,vaq1.QAD01 as QAD01C,vaq1.BCK01,case when VAE44<=3 then ''在院 '' else ''出院 '' end+ bck1.BCK03 BCK03, c.VAA01,VAA04,right('' ''+C.BCQ04B,4)+''床 ''+VAA05+case vaa1.abw01 when 1 then '' 男 '' when 2 then '' 女 '' else '' '' end +convert(varchar(10),c.VAE46)+c.AAU01+'' ''+VAA04 as VAA05, VAA05 as VAA05A,vaq1.VAA07,VAE02,0 QAD01A, 0 QAD07, null QAD08,VAQ1.CAD04+''(全部，仅显示)'' QAD09,null QAD10,null as BCE01B,null as BCE03B ,VAQ1.CAD01, vaq1.BCE01,VAQ1.BCE03A,null QAD13 ,VAE01,VAQ1.VAQ01,2 ACF01,VAQ1.CAD04,VAQ20, vaq1.BCE03C,VAQ19, case when (vaq1.CAB01 = 3 OR vaq1.CAB01=4) then VAM35 else case @一二级质控员使用主治主任医生 when 1 then VAM27 else VAMA5 end end as VAM27, case @一二级质控员使用主治主任医生 when 1 then VAM31 else VAMA4 end as VAM31 ,VAQ1.CAB01 ,vaq1.VAQ01A FROM VAQ1 JOIN vaa1 ON vaa1.VAA01=vaq1.VAA01 JOIN VAE1 C ON vaq1.VAA07=C.VAE01 left join VAM1 m on vaq1.vaa07=m.vaa07 LEFT JOIN bck1 ON c.BCK01D=bck1.BCK01 WHERE (vaq1.VAA07=@VAA07 or @VAA07=0) AND (vaq1.bck01 in (select * from split(@bck01,'','')) or @bck01=''0'') and (@userLevel=3 and VAE44>=4 --and @QAD07=''-1'' ) and (VAE26 between @Bdate and @Edate) and vaq1.vaq20=3 and VAQ21=2 select BCK03,count(distinct VAA07) p into #b from #a group by BCK03 update a set a.BCK03=a.BCK03+''(''+convert(varchar(10),p)+''人)'' from #a a join #b b on a.BCK03=b.BCK03 alter table #a add stateImage image if @usertype=''医生'' begin delete from #a where cab01 in (3,4) end else begin delete from #a where cab01 in (2,6) end if @userLevel=3 select a.* ,b.ADC01,e.ADC05 from #a a left join CAD1 b on a.CAD01=b.CAD01 left join ADC1 e on b.ADC01=e.adc01 left join CBV1 c on b.CBT01=c.CBT01 and exists(select * from CBU1 where CBU01=c.CBU01 and CBU05=2) order by a.BCK03,a.VAA05,c.ROWNR,b.CBT01,a.VAQ01 else select a.* ,b.ADC01,e.ADC05 from #a a left join CAD1 b on a.CAD01=b.CAD01 left join ADC1 e on b.ADC01=e.adc01 order by a.BCK03,a.VAA05,b.CBT01,a.VAQ01 drop table #a drop table #b<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid><Sql>select * from BCK1</Sql><HD><Ie FD="BCK03" WD="100"/></HD><QF><Ie FD="BCK03" WE="7"/><Ie FD="BCK01" WE="7"/><Ie FD="ABBRP" WE="7"/></QF></Grid></Configs><?xml version="1.0"?> <Configs Ver="1"><Win WD="500" SZ="1"/><Grid Head="1"><Sql>select VAE01,VAA04,VAA05,VAE11,VAE26,a.BCQ04B from VAE1 a join VAA1 b on a.VAA01=b.VAA01</Sql><HD><Ie FD="VAA04" CA="住院号" WD="100"/><Ie FD="VAA05" CA="姓名" WD="100"/><Ie FD="VAE11" CA="入院时间" WD="100"/><Ie FD="VAE26" CA="出院时间" WD="100"/><Ie FD="BCQ04B" CA="床位" WD="100"/></HD><QF><Ie FD="VAA04" WE="7"/><Ie FD="VAA05" WE="7"/><Ie FD="abbrp" WE="7"/></QF></Grid></Configs>-1=自动选择（全部） 3=三级质控（病案室已质控） 2=二级质控（主任医师已质控） 1=一级质控（主治医师已质控） 0=未质控 ' where Code='EMR.QC.000002' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.QC.COUNT') begin --插入 Declare @Report_Id_1137 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1137 out Declare @Report_Id_1137_CateID int Set @Report_Id_1137_CateID = (select id from Report_Categories where code='EMR.QC') 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_1137, 'EMR.QC.COUNT', '获取质控统计数据', Null, Null, @Report_Id_1137_CateID, 1, 3, 600, 0, Null, '2015-07-03 17:08:38', '2015-07-03 17:11:21', Null, ' 6EAA94041E8D27848D76024D66EDD612 select ''您今天提交了''+ (SELECT cast(count(qad01) as varchar(10)) FROM qad1 bce03 WHERE qad13 =1 AND qad10> CAST(GETDATE() AS DATE ) AND bce01= @$EmpId ) +'' 退回了'' +( SELECT cast(count(qad01) as varchar(10)) FROM qad1 bce03 WHERE qad13 &3=3 AND qad10> CAST(GETDATE() AS DATE ) AND bce01= @$EmpId ) +'' 归档了''+ (SELECT cast(count(vaq01) as varchar(10)) FROM vaq1 WHERE vaq20=2 AND vaq19>CAST(GETDATE() AS DATE ) AND BCE03C= @$EmpName )+''份病历'' ', Null, 0, 1, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' 6EAA94041E8D27848D76024D66EDD612 select ''您今天提交了''+ (SELECT cast(count(qad01) as varchar(10)) FROM qad1 bce03 WHERE qad13 =1 AND qad10> CAST(GETDATE() AS DATE ) AND bce01= @$EmpId ) +'' 退回了'' +( SELECT cast(count(qad01) as varchar(10)) FROM qad1 bce03 WHERE qad13 &3=3 AND qad10> CAST(GETDATE() AS DATE ) AND bce01= @$EmpId ) +'' 归档了''+ (SELECT cast(count(vaq01) as varchar(10)) FROM vaq1 WHERE vaq20=2 AND vaq19>CAST(GETDATE() AS DATE ) AND BCE03C= @$EmpName )+''份病历'' ' where Code='EMR.QC.COUNT' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.QC.0000025') begin --插入 Declare @Report_Id_1157 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1157 out Declare @Report_Id_1157_CateID int Set @Report_Id_1157_CateID = (select id from Report_Categories where code='EMR.QC') 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_1157, 'EMR.QC.0000025', '病历质控-在院质控', Null, Null, @Report_Id_1157_CateID, 1, 4, 600, 0, Null, '2014-04-24 16:21:51', '2015-07-17 15:46:36', Null, ' E9C1E1B5FC7185B15C8422965C9D7773declare @qad07 VARCHAR(50) , @VAA07 int,@BDate datetime, @Edate datetime declare @qad08 varchar(64) declare @bck01 VARCHAR(500) set @qad07=isnull(:qad07,''-1'') set @bck01=:bck01 set @bck01=CASE isnull(@bck01,'''')WHEN '''' THEN ''0'' ELSE @bck01 END set @VAA07=:VAA07 set @BDate=:Bdate set @Edate=:Edate declare @userLevel int SELECT @userLevel =case when aaa02=''三'' then 3 when aaa02=''二'' then 2 when aaa02=''一'' then 1 end from (select TOP(1)baa1.AAA01,SUBSTRING(aaa02,1,1) as AAA02 FROM baa1 JOIN aaa1 ON baa1.AAA01=aaa1.AAA01 WHERE bce01 =@$EmpID and aaa02 LIKE ''__质控员'' ORDER BY CHARINDEX(SUBSTRING(aaa02,1,1),''一,二,三'') desc )t SELECT a.QAD01, a.QAD01B,vaq1.QAD01 as QAD01C,vaq1.BCK01,case when VAE44<=3 then ''在院 '' else ''出院 '' end+ bck1.BCK03 BCK03, a.VAA01,VAA04 ,isnull(right('' ''+C.BCQ04B,4)+''床 '','''')+VAA05+case vaa1.abw01 when 1 then '' 男 '' when 2 then '' 女 '' else '' '' end +isnull(convert(varchar(10),c.VAE46)+c.AAU01,'''')+''-''+VAA04+''<''+CONVERT(varchar(100), C.VAE11, 20)+''>''+'' ''+c.bce03c as VAA05A ,VAA05 as VAA05,vaq1.VAA07,VAE02,a.QAD01A, a.QAD07, a.QAD08,VAQ1.CAD04 QAD09,a.QAD10,a.BCE01 as BCE01B,a.BCE03 as BCE03B ,VAQ1.CAD01, vaq1.BCE01,VAQ1.BCE03A,a.QAD13 ,VAE01,VAQ1.VAQ01,a.ACF01,VAQ1.CAD04,VAQ20, vaq1.BCE03C,VAQ19, case when (vaq1.CAB01 = 3 OR vaq1.CAB01=4) then VAM35 else case @一二级质控员使用主治主任医生 when 1 then VAM27 else VAMA5 end end as VAM27, case @一二级质控员使用主治主任医生 when 1 then VAM31 else VAMA4 end as VAM31,vaq1.VAQ01A into #a FROM qad1 a JOIN vaq1 ON a.QAD01=vaq1.QAD01 JOIN vaa1 ON vaa1.VAA01=vaq1.VAA01 JOIN VAE1 C ON vaq1.VAA07=C.VAE01 left join VAM1 m on vaq1.vaa07=m.vaa07 LEFT JOIN bck1 ON c.BCK01D=bck1.BCK01 WHERE (@VAA07=0 or vaq1.VAA07=@VAA07) and (@qad07=''-1'' or a.qad07 in (select * from split(@qad07,'','')))--not exists(select * from qad1 q where q.VAA01=c.VAA01 and q.acf01=2 and q.qad07<@qad07)) AND (C.bck01D in (select * from split(@bck01,'','')) or @bck01=''0'') and ((@$EmpName in (case when (vaq1.CAB01 = 3 OR vaq1.CAB01=4) then VAM35 else case @一二级质控员使用主治主任医生 when 1 then VAM27 else VAMA5 end end , case @一二级质控员使用主治主任医生 when 1 then VAM31 else VAMA4 end ) and vaq1.cab01 in(2,6)) or (@userLevel=2 and vaq1.cab01=4) or (@userLevel=3 and VAE44 between 1 and 4) ) and (VAE44 in(1,2,3,4)) and vaq1.vaq20 in (1,2) and VAQ21<=1 union SELECT 0 QAD01,0 QAD01B,vaq1.QAD01 as QAD01C,vaq1.BCK01,case when VAE44<=3 then ''在院 '' else ''出院 '' end+ bck1.BCK03 BCK03, c.VAA01,VAA04 ,isnull(right('' ''+C.BCQ04B,4)+''床 '','''')+VAA05+case vaa1.abw01 when 1 then '' 男 '' when 2 then '' 女 '' else '' '' end +isnull(convert(varchar(10),c.VAE46)+c.AAU01,'''')+''-''+VAA04+''<''+CONVERT(varchar(100), C.VAE11, 20)+''>''+'' ''+c.bce03c as VAA05A, VAA05 as VAA05,vaq1.VAA07,VAE02,0 QAD01A, 0 QAD07, null QAD08,VAQ1.CAD04+''(全部，仅显示)'' QAD09,null QAD10,null as BCE01B,null as BCE03B ,VAQ1.CAD01, vaq1.BCE01,VAQ1.BCE03A,null QAD13 ,VAE01,VAQ1.VAQ01,2 ACF01,VAQ1.CAD04,VAQ20, vaq1.BCE03C,VAQ19, VAM27,VAM31 ,vaq1.VAQ01A FROM VAQ1 JOIN vaa1 ON vaa1.VAA01=vaq1.VAA01 JOIN VAE1 C ON vaq1.VAA07=C.VAE01 left join VAM1 m on vaq1.vaa07=m.vaa07 LEFT JOIN bck1 ON c.BCK01D=bck1.BCK01 WHERE (vaq1.VAA07=@VAA07 or @VAA07=0) AND (C.BCK01D in (select * from split(@bck01,'','')) or @bck01=''0'') and (@userLevel=3 and VAE44 between 1 and 4 --and @QAD07=''-1'' ) and (VAE26 between @Bdate and @Edate) and vaq1.vaq20=3 and VAQ21=2 select BCK03,count(distinct VAA07) p into #b from #a group by BCK03 update a set a.BCK03=a.BCK03+''(''+convert(varchar(10),p)+''人)'' from #a a join #b b on a.BCK03=b.BCK03 alter table #a add stateImage image select a.* ,b.ADC01,e.ADC05 from #a a left join CAD1 b on a.CAD01=b.CAD01 left join ADC1 e on b.ADC01=e.adc01 left join CBV1 c on b.CBT01=c.CBT01 and exists(select * from CBU1 where CBU01=c.CBU01 and CBU05=2) order by a.BCK03,a.VAA05,c.ROWNR,b.CBT01,a.VAQ01 drop table #a drop table #b<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid><Sql>select * from BCK1</Sql><HD><Ie FD="BCK03" WD="100"/></HD><QF><Ie FD="BCK03" WE="7"/><Ie FD="BCK01" WE="7"/><Ie FD="ABBRP" WE="7"/></QF></Grid></Configs><?xml version="1.0"?> <Configs Ver="1"><Win WD="500" SZ="1"/><Grid Head="1"><Sql>select VAE01,VAA04,VAA05,VAE11,VAE26,a.BCQ04B from VAE1 a join VAA1 b on a.VAA01=b.VAA01</Sql><HD><Ie FD="VAA04" CA="住院号" WD="100"/><Ie FD="VAA05" CA="姓名" WD="100"/><Ie FD="VAE11" CA="入院时间" WD="100"/><Ie FD="VAE26" CA="出院时间" WD="100"/><Ie FD="BCQ04B" CA="床位" WD="100"/></HD><QF><Ie FD="VAA04" WE="7"/><Ie FD="VAA05" WE="7"/><Ie FD="abbrp" WE="7"/></QF></Grid></Configs>-1=自动选择（全部） 3=三级质控（病案室已质控） 2=二级质控（主任医师已质控） 1=一级质控（主治医师已质控） 0=未质控 ', Null, 1, 0, Null, 1, Null) end else --更新 begin update Report_Objects set Config=' E9C1E1B5FC7185B15C8422965C9D7773declare @qad07 VARCHAR(50) , @VAA07 int,@BDate datetime, @Edate datetime declare @qad08 varchar(64) declare @bck01 VARCHAR(500) set @qad07=isnull(:qad07,''-1'') set @bck01=:bck01 set @bck01=CASE isnull(@bck01,'''')WHEN '''' THEN ''0'' ELSE @bck01 END set @VAA07=:VAA07 set @BDate=:Bdate set @Edate=:Edate declare @userLevel int SELECT @userLevel =case when aaa02=''三'' then 3 when aaa02=''二'' then 2 when aaa02=''一'' then 1 end from (select TOP(1)baa1.AAA01,SUBSTRING(aaa02,1,1) as AAA02 FROM baa1 JOIN aaa1 ON baa1.AAA01=aaa1.AAA01 WHERE bce01 =@$EmpID and aaa02 LIKE ''__质控员'' ORDER BY CHARINDEX(SUBSTRING(aaa02,1,1),''一,二,三'') desc )t SELECT a.QAD01, a.QAD01B,vaq1.QAD01 as QAD01C,vaq1.BCK01,case when VAE44<=3 then ''在院 '' else ''出院 '' end+ bck1.BCK03 BCK03, a.VAA01,VAA04 ,isnull(right('' ''+C.BCQ04B,4)+''床 '','''')+VAA05+case vaa1.abw01 when 1 then '' 男 '' when 2 then '' 女 '' else '' '' end +isnull(convert(varchar(10),c.VAE46)+c.AAU01,'''')+''-''+VAA04+''<''+CONVERT(varchar(100), C.VAE11, 20)+''>''+'' ''+c.bce03c as VAA05A ,VAA05 as VAA05,vaq1.VAA07,VAE02,a.QAD01A, a.QAD07, a.QAD08,VAQ1.CAD04 QAD09,a.QAD10,a.BCE01 as BCE01B,a.BCE03 as BCE03B ,VAQ1.CAD01, vaq1.BCE01,VAQ1.BCE03A,a.QAD13 ,VAE01,VAQ1.VAQ01,a.ACF01,VAQ1.CAD04,VAQ20, vaq1.BCE03C,VAQ19, case when (vaq1.CAB01 = 3 OR vaq1.CAB01=4) then VAM35 else case @一二级质控员使用主治主任医生 when 1 then VAM27 else VAMA5 end end as VAM27, case @一二级质控员使用主治主任医生 when 1 then VAM31 else VAMA4 end as VAM31,vaq1.VAQ01A into #a FROM qad1 a JOIN vaq1 ON a.QAD01=vaq1.QAD01 JOIN vaa1 ON vaa1.VAA01=vaq1.VAA01 JOIN VAE1 C ON vaq1.VAA07=C.VAE01 left join VAM1 m on vaq1.vaa07=m.vaa07 LEFT JOIN bck1 ON c.BCK01D=bck1.BCK01 WHERE (@VAA07=0 or vaq1.VAA07=@VAA07) and (@qad07=''-1'' or a.qad07 in (select * from split(@qad07,'','')))--not exists(select * from qad1 q where q.VAA01=c.VAA01 and q.acf01=2 and q.qad07<@qad07)) AND (C.bck01D in (select * from split(@bck01,'','')) or @bck01=''0'') and ((@$EmpName in (case when (vaq1.CAB01 = 3 OR vaq1.CAB01=4) then VAM35 else case @一二级质控员使用主治主任医生 when 1 then VAM27 else VAMA5 end end , case @一二级质控员使用主治主任医生 when 1 then VAM31 else VAMA4 end ) and vaq1.cab01 in(2,6)) or (@userLevel=2 and vaq1.cab01=4) or (@userLevel=3 and VAE44 between 1 and 4) ) and (VAE44 in(1,2,3,4)) and vaq1.vaq20 in (1,2) and VAQ21<=1 union SELECT 0 QAD01,0 QAD01B,vaq1.QAD01 as QAD01C,vaq1.BCK01,case when VAE44<=3 then ''在院 '' else ''出院 '' end+ bck1.BCK03 BCK03, c.VAA01,VAA04 ,isnull(right('' ''+C.BCQ04B,4)+''床 '','''')+VAA05+case vaa1.abw01 when 1 then '' 男 '' when 2 then '' 女 '' else '' '' end +isnull(convert(varchar(10),c.VAE46)+c.AAU01,'''')+''-''+VAA04+''<''+CONVERT(varchar(100), C.VAE11, 20)+''>''+'' ''+c.bce03c as VAA05A, VAA05 as VAA05,vaq1.VAA07,VAE02,0 QAD01A, 0 QAD07, null QAD08,VAQ1.CAD04+''(全部，仅显示)'' QAD09,null QAD10,null as BCE01B,null as BCE03B ,VAQ1.CAD01, vaq1.BCE01,VAQ1.BCE03A,null QAD13 ,VAE01,VAQ1.VAQ01,2 ACF01,VAQ1.CAD04,VAQ20, vaq1.BCE03C,VAQ19, VAM27,VAM31 ,vaq1.VAQ01A FROM VAQ1 JOIN vaa1 ON vaa1.VAA01=vaq1.VAA01 JOIN VAE1 C ON vaq1.VAA07=C.VAE01 left join VAM1 m on vaq1.vaa07=m.vaa07 LEFT JOIN bck1 ON c.BCK01D=bck1.BCK01 WHERE (vaq1.VAA07=@VAA07 or @VAA07=0) AND (C.BCK01D in (select * from split(@bck01,'','')) or @bck01=''0'') and (@userLevel=3 and VAE44 between 1 and 4 --and @QAD07=''-1'' ) and (VAE26 between @Bdate and @Edate) and vaq1.vaq20=3 and VAQ21=2 select BCK03,count(distinct VAA07) p into #b from #a group by BCK03 update a set a.BCK03=a.BCK03+''(''+convert(varchar(10),p)+''人)'' from #a a join #b b on a.BCK03=b.BCK03 alter table #a add stateImage image select a.* ,b.ADC01,e.ADC05 from #a a left join CAD1 b on a.CAD01=b.CAD01 left join ADC1 e on b.ADC01=e.adc01 left join CBV1 c on b.CBT01=c.CBT01 and exists(select * from CBU1 where CBU01=c.CBU01 and CBU05=2) order by a.BCK03,a.VAA05,c.ROWNR,b.CBT01,a.VAQ01 drop table #a drop table #b<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid><Sql>select * from BCK1</Sql><HD><Ie FD="BCK03" WD="100"/></HD><QF><Ie FD="BCK03" WE="7"/><Ie FD="BCK01" WE="7"/><Ie FD="ABBRP" WE="7"/></QF></Grid></Configs><?xml version="1.0"?> <Configs Ver="1"><Win WD="500" SZ="1"/><Grid Head="1"><Sql>select VAE01,VAA04,VAA05,VAE11,VAE26,a.BCQ04B from VAE1 a join VAA1 b on a.VAA01=b.VAA01</Sql><HD><Ie FD="VAA04" CA="住院号" WD="100"/><Ie FD="VAA05" CA="姓名" WD="100"/><Ie FD="VAE11" CA="入院时间" WD="100"/><Ie FD="VAE26" CA="出院时间" WD="100"/><Ie FD="BCQ04B" CA="床位" WD="100"/></HD><QF><Ie FD="VAA04" WE="7"/><Ie FD="VAA05" WE="7"/><Ie FD="abbrp" WE="7"/></QF></Grid></Configs>-1=自动选择（全部） 3=三级质控（病案室已质控） 2=二级质控（主任医师已质控） 1=一级质控（主治医师已质控） 0=未质控 ' where Code='EMR.QC.0000025' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.QC.100001') begin --插入 Declare @Report_Id_1272 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1272 out Declare @Report_Id_1272_CateID int Set @Report_Id_1272_CateID = (select id from Report_Categories where code='EMR.QC') 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_1272, 'EMR.QC.100001', '病人列表', Null, Null, @Report_Id_1272_CateID, 1, 4, 600, 0, Null, '2016-01-27 15:10:34', '2016-02-01 14:56:11', Null, ' 0942D1E0267365CFC3B0729181D09A02DECLARE @lBCK01 varchar(500), @lType int, @lDt1 varchar(30), @lDt2 varchar(30), @BCE01 int , @QAD07 varchar(50) set @lbck01=:bck01 set @lbck01=CASE isnull(@lbck01,'''')WHEN '''' THEN ''0'' ELSE @lbck01 END SET @lType = :type Set @lDt1 = Convert(varchar(10),Cast(:BDate as datetime),21)+'' 00:00:00.000'' Set @lDt2 = Convert(varchar(10),Cast(:EDate as datetime),21)+'' 23:59:59.999'' set @BCE01=:BCE01 set @qad07=isnull(:QAD07,''-1'') if @QAD07=''-1'' set @QAD07=''0,1,2,3'' DECLARE @userLevel INT , @一二级质控员使用主治主任医生 INT --读取一级质控员是否使用主任医生设置 1是 0否 SET @一二级质控员使用主治主任医生 = ISNULL(( SELECT value FROM dbo.SYS_Parameters WHERE ProductID = 600 AND paramno = 52 ), 1) -- 判断当前操作员是否是三级质控员 如果是设置@userlevel=3 否则@userlevel=0 SELECT @userLevel = CASE WHEN aaa02 = ''三'' THEN 3 ELSE 0 END FROM ( SELECT TOP ( 1 ) baa1.AAA01 , SUBSTRING(aaa02, 1, 1) AS AAA02 FROM baa1 JOIN aaa1 ON baa1.AAA01 = aaa1.AAA01 WHERE bce01 = @BCE01 AND aaa02 LIKE ''__质控员'' ORDER BY CHARINDEX(SUBSTRING(aaa02, 1, 1), ''一,二,三'') DESC ) t --当传入科室参数为所有部门时 如果当前操作员不是三级质控员，将科室参数改为当前操作员的所有业务科室 DECLARE @bce03 VARCHAR(20) SELECT @bce03 = BCE03 FROM BCE1 WHERE bce01 = @BCE01 IF @LBCK01 = ''0'' AND @userLevel < 3 BEGIN DECLARE @_bck01s VARCHAR(4000) SET @_bck01s = ( SELECT CAST(bck01 AS VARCHAR(20)) + '','' FROM BCK1 a WHERE EXISTS ( SELECT b.* FROM BAZ1 b WHERE a.BCK01 = b.BCK01 AND b.BAU01 IN ( ''01'' ) AND b.ACF01 IN ( 2, 3 ) ) AND EXISTS ( SELECT c.* FROM BDR1 c WHERE a.BCK01 = c.BCK01 AND c.BCE01 = @BCE01 ) ORDER BY A.BCK03 FOR XML PATH('''') ) SET @LBCK01 = SUBSTRING(@_bck01s, 1, LEN(@_bck01s) - 1) END SELECT VAE01 , VAE02 , a.VAA01 , c.VAA05 , c.VAA04 , d.ABW02 , BCK01D , BCQ04B , VAE26 , VAE27 , VAE46 , a.AAU01 , a.ACK01 , Agep = CASE WHEN ISNULL(a.VAE46, 0) = 0 THEN NULL ELSE ( LTRIM(STR(a.VAE46)) + e.AAU02 ) END , CASE @一二级质控员使用主治主任医生 WHEN 1 THEN VAM31 ELSE VAMA4 END AS 一级质控员 , CASE @ltype WHEN 2 THEN VAM35 ELSE CASE @一二级质控员使用主治主任医生 WHEN 1 THEN VAM27 ELSE VAMA5 END END AS 二级质控员 , CAST(0 AS TINYINT) AS stateImage , 0 version , B.BCK03 + ''('' + CASE WHEN VAE44 <= 3 THEN ''在院 '' ELSE ''出院 '' END + '')'' AS BCK03 , a.BCE03C , a.VAE11 , g.AAG02 INTO #医生 FROM vae1 a LEFT JOIN bck1 b ON b.bck01 = a.bck01d LEFT JOIN VAA1 c ON c.VAA01 = a.VAA01 LEFT JOIN abw1 d ON c.abw01 = d.abw01 LEFT JOIN aau1 e ON a.AAU01 = e.AAU01 LEFT JOIN VAM1 f ON a.VAE01 = f.VAA07 LEFT JOIN BBY1 e1 ON a.AAG01 = e1.BBY01 LEFT JOIN AAG1 g ON e1.BCF01 = g.AAG01 WHERE vae44 >= 2 AND vae44 <= 5 AND ( @userLevel = 3 OR a.bck01d IN ( SELECT * FROM split(@LBCK01, '','') ) ) AND ( ( VAE26 BETWEEN @lDt1 AND @lDt2 ) OR VAE44 IN ( 1, 2 ) ) -------------------------------------------------------------------------------------- --更新病人列表中的质控状态 IF @lType = 1 BEGIN UPDATE #医生 SET stateImage = ISNULL(( CASE 总病历数 WHEN 0 THEN 0 ELSE CASE ( CASE WHEN 已一级质控病历数 = 总病历数 THEN 2 ELSE 0 END ) | ( CASE WHEN 已二级质控病历数 = 总病历数 THEN 4 ELSE 0 END ) | ( CASE WHEN 已三级质控病历数 = 总病历数 THEN 8 ELSE 0 END ) WHEN 0 THEN 1 ELSE ( CASE WHEN 已一级质控病历数 = 总病历数 THEN 2 ELSE 0 END ) | ( CASE WHEN 已二级质控病历数 = 总病历数 THEN 4 ELSE 0 END ) | ( CASE WHEN 已三级质控病历数 = 总病历数 THEN 8 ELSE 0 END ) END END ), 0) , version = t.version FROM ( SELECT SUM(CAST(SUBSTRING(ISNULL(QAD08, ''001.001.000.000''), 13, 3) AS INT) & 1) AS 已一级质控病历数 , SUM(CAST(SUBSTRING(ISNULL(QAD08, ''001.001.000.000''), 13, 3) AS INT) & 2) / 2 AS 已二级质控病历数 , SUM(CAST(SUBSTRING(ISNULL(QAD08, ''001.001.000.000''), 13, 3) AS INT) & 4) / 4 AS 已三级质控病历数 , COUNT(qad1.vaq01) AS 总病历数 , VAA07 , MAX(SUBSTRING(ISNULL(qad08, ''001.001.000.000''), 1, 3)) AS version FROM VAQ1 LEFT JOIN QAD1 ON vaq1.QAD01 = qad1.QAD01 WHERE VAQ20 IN ( 0, 1, 2, 5 ) AND VAQ21 <> 2 AND CAB01 NOT IN ( 4, 5, 7, 8, 9 ) AND ISNULL(vap01, 0) = 0 AND VAA07 IN ( SELECT VAE01 FROM #医生 ) GROUP BY vaa07 ) t WHERE t.总病历数 > 0 AND t.VAA07 = #医生.VAE01 END IF @ltype = 2 BEGIN UPDATE #医生 SET stateImage = ISNULL(( CASE 总病历数 WHEN 0 THEN 0 ELSE CASE ( CASE WHEN 已一级质控病历数 = 总病历数 THEN 2 ELSE 0 END ) | ( CASE WHEN 已二级质控病历数 = 总病历数 THEN 4 ELSE 0 END ) | ( CASE WHEN 已三级质控病历数 = 总病历数 THEN 8 ELSE 0 END ) WHEN 0 THEN 1 ELSE ( CASE WHEN 已一级质控病历数 = 总病历数 THEN 2 ELSE 0 END ) | ( CASE WHEN 已二级质控病历数 = 总病历数 THEN 4 ELSE 0 END ) | ( CASE WHEN 已三级质控病历数 = 总病历数 THEN 8 ELSE 0 END ) END END ), 0) , version = t.version FROM ( SELECT SUM(CAST(SUBSTRING(ISNULL(QAD08, ''001.001.000.000''), 13, 3) AS INT) & 1) AS 已一级质控病历数 , SUM(CAST(SUBSTRING(ISNULL(QAD08, ''001.001.000.000''), 13, 3) AS INT) & 2) / 2 AS 已二级质控病历数 , SUM(CAST(SUBSTRING(ISNULL(QAD08, ''001.001.000.000''), 13, 3) AS INT) & 4) / 4 AS 已三级质控病历数 , COUNT(qad1.vaq01) AS 总病历数 , VAA07 , MAX(SUBSTRING(ISNULL(qad08, ''001.001.000.000''), 1, 3)) AS version FROM VAQ1 LEFT JOIN QAD1 ON vaq1.QAD01 = qad1.QAD01 WHERE VAQ20 IN ( 0, 1, 2, 5 ) AND VAQ21 <> 2 AND CAB01 IN ( 3, 4 ) AND ISNULL(vap01, 0) = 0 AND VAA07 IN ( SELECT VAE01 FROM #医生 ) GROUP BY vaa07 ) t WHERE t.总病历数 > 0 AND t.VAA07 = #医生.VAE01 END; update #医生 set BCK03=#医生.BCK03+''(''+rtrim(cast(countVAA01 as varchar(10))) +''人)'' from (select count(vae01) as countVAA01,BCK03 FROM #医生 where (一级质控员=@bce03 or 二级质控员=@bce03 OR @userLevel=3) and exists( select vaq1.vaq01 from vaq1 join qad1 on vaq1.qad01=qad1.qad01 where (一级质控员=@bce03 or 二级质控员=@bce03 OR @userLevel=3) and exists( select vaq1.vaq01 from vaq1 join qad1 on vaq1.qad01=qad1.qad01 where vaq1.vaq21<>2 and vaq20 not in (0,4) and QAD07 in (select * from split(@QAD07,'','')) and vaq1.VAA07=#医生.vae01)) group by #医生.BCK03) t where t.BCK03=#医生.BCK03 SELECT * FROM #医生 WHERE ( 一级质控员 = @bce03 OR 二级质控员 = @bce03 OR @userLevel = 3 ) AND EXISTS ( SELECT vaq1.vaq01 FROM vaq1 JOIN qad1 ON vaq1.qad01 = qad1.qad01 WHERE vaq1.vaq21 <> 2 AND vaq20 NOT IN ( 0, 4 ) AND QAD07 IN ( SELECT * FROM split(@QAD07, '','') ) AND vaq1.VAA07 = #医生.vae01 ) DROP TABLE #医生<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid UseW="1"><Sql>SELECT a.BCK01,a.BCK03,a.BCK02,''0'' as IfDef FROM BCK1 a Where EXISTS(SELECT b.* FROM BAZ1 b WHERE a.BCK01=b.BCK01 AND b.BAU01 IN (''01'') AND b.ACF01 IN(2,3)) AND EXISTS(SELECT c.* FROM BDR1 c WHERE a.BCK01 = c.BCK01 AND c.BCE01 =&lt;BCE01&gt; ) </Sql><HD><Ie FD="BCK01" CA="科室ID" WD="100"/><Ie FD="BCK03" CA="科室名称" WD="100"/></HD><QF><Ie FD="BCK03" WE="7"/></QF></Grid></Configs>-1=自动选择 3=三级质控（病案室已质控） 2=二级质控（主任医师已质控） 1=一级质控（主治医师已质控） 0=未质控 1=医师工作站 2=护士工作站 ', Null, 0, 1, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' 0942D1E0267365CFC3B0729181D09A02DECLARE @lBCK01 varchar(500), @lType int, @lDt1 varchar(30), @lDt2 varchar(30), @BCE01 int , @QAD07 varchar(50) set @lbck01=:bck01 set @lbck01=CASE isnull(@lbck01,'''')WHEN '''' THEN ''0'' ELSE @lbck01 END SET @lType = :type Set @lDt1 = Convert(varchar(10),Cast(:BDate as datetime),21)+'' 00:00:00.000'' Set @lDt2 = Convert(varchar(10),Cast(:EDate as datetime),21)+'' 23:59:59.999'' set @BCE01=:BCE01 set @qad07=isnull(:QAD07,''-1'') if @QAD07=''-1'' set @QAD07=''0,1,2,3'' DECLARE @userLevel INT , @一二级质控员使用主治主任医生 INT --读取一级质控员是否使用主任医生设置 1是 0否 SET @一二级质控员使用主治主任医生 = ISNULL(( SELECT value FROM dbo.SYS_Parameters WHERE ProductID = 600 AND paramno = 52 ), 1) -- 判断当前操作员是否是三级质控员 如果是设置@userlevel=3 否则@userlevel=0 SELECT @userLevel = CASE WHEN aaa02 = ''三'' THEN 3 ELSE 0 END FROM ( SELECT TOP ( 1 ) baa1.AAA01 , SUBSTRING(aaa02, 1, 1) AS AAA02 FROM baa1 JOIN aaa1 ON baa1.AAA01 = aaa1.AAA01 WHERE bce01 = @BCE01 AND aaa02 LIKE ''__质控员'' ORDER BY CHARINDEX(SUBSTRING(aaa02, 1, 1), ''一,二,三'') DESC ) t --当传入科室参数为所有部门时 如果当前操作员不是三级质控员，将科室参数改为当前操作员的所有业务科室 DECLARE @bce03 VARCHAR(20) SELECT @bce03 = BCE03 FROM BCE1 WHERE bce01 = @BCE01 IF @LBCK01 = ''0'' AND @userLevel < 3 BEGIN DECLARE @_bck01s VARCHAR(4000) SET @_bck01s = ( SELECT CAST(bck01 AS VARCHAR(20)) + '','' FROM BCK1 a WHERE EXISTS ( SELECT b.* FROM BAZ1 b WHERE a.BCK01 = b.BCK01 AND b.BAU01 IN ( ''01'' ) AND b.ACF01 IN ( 2, 3 ) ) AND EXISTS ( SELECT c.* FROM BDR1 c WHERE a.BCK01 = c.BCK01 AND c.BCE01 = @BCE01 ) ORDER BY A.BCK03 FOR XML PATH('''') ) SET @LBCK01 = SUBSTRING(@_bck01s, 1, LEN(@_bck01s) - 1) END SELECT VAE01 , VAE02 , a.VAA01 , c.VAA05 , c.VAA04 , d.ABW02 , BCK01D , BCQ04B , VAE26 , VAE27 , VAE46 , a.AAU01 , a.ACK01 , Agep = CASE WHEN ISNULL(a.VAE46, 0) = 0 THEN NULL ELSE ( LTRIM(STR(a.VAE46)) + e.AAU02 ) END , CASE @一二级质控员使用主治主任医生 WHEN 1 THEN VAM31 ELSE VAMA4 END AS 一级质控员 , CASE @ltype WHEN 2 THEN VAM35 ELSE CASE @一二级质控员使用主治主任医生 WHEN 1 THEN VAM27 ELSE VAMA5 END END AS 二级质控员 , CAST(0 AS TINYINT) AS stateImage , 0 version , B.BCK03 + ''('' + CASE WHEN VAE44 <= 3 THEN ''在院 '' ELSE ''出院 '' END + '')'' AS BCK03 , a.BCE03C , a.VAE11 , g.AAG02 INTO #医生 FROM vae1 a LEFT JOIN bck1 b ON b.bck01 = a.bck01d LEFT JOIN VAA1 c ON c.VAA01 = a.VAA01 LEFT JOIN abw1 d ON c.abw01 = d.abw01 LEFT JOIN aau1 e ON a.AAU01 = e.AAU01 LEFT JOIN VAM1 f ON a.VAE01 = f.VAA07 LEFT JOIN BBY1 e1 ON a.AAG01 = e1.BBY01 LEFT JOIN AAG1 g ON e1.BCF01 = g.AAG01 WHERE vae44 >= 2 AND vae44 <= 5 AND ( @userLevel = 3 OR a.bck01d IN ( SELECT * FROM split(@LBCK01, '','') ) ) AND ( ( VAE26 BETWEEN @lDt1 AND @lDt2 ) OR VAE44 IN ( 1, 2 ) ) -------------------------------------------------------------------------------------- --更新病人列表中的质控状态 IF @lType = 1 BEGIN UPDATE #医生 SET stateImage = ISNULL(( CASE 总病历数 WHEN 0 THEN 0 ELSE CASE ( CASE WHEN 已一级质控病历数 = 总病历数 THEN 2 ELSE 0 END ) | ( CASE WHEN 已二级质控病历数 = 总病历数 THEN 4 ELSE 0 END ) | ( CASE WHEN 已三级质控病历数 = 总病历数 THEN 8 ELSE 0 END ) WHEN 0 THEN 1 ELSE ( CASE WHEN 已一级质控病历数 = 总病历数 THEN 2 ELSE 0 END ) | ( CASE WHEN 已二级质控病历数 = 总病历数 THEN 4 ELSE 0 END ) | ( CASE WHEN 已三级质控病历数 = 总病历数 THEN 8 ELSE 0 END ) END END ), 0) , version = t.version FROM ( SELECT SUM(CAST(SUBSTRING(ISNULL(QAD08, ''001.001.000.000''), 13, 3) AS INT) & 1) AS 已一级质控病历数 , SUM(CAST(SUBSTRING(ISNULL(QAD08, ''001.001.000.000''), 13, 3) AS INT) & 2) / 2 AS 已二级质控病历数 , SUM(CAST(SUBSTRING(ISNULL(QAD08, ''001.001.000.000''), 13, 3) AS INT) & 4) / 4 AS 已三级质控病历数 , COUNT(qad1.vaq01) AS 总病历数 , VAA07 , MAX(SUBSTRING(ISNULL(qad08, ''001.001.000.000''), 1, 3)) AS version FROM VAQ1 LEFT JOIN QAD1 ON vaq1.QAD01 = qad1.QAD01 WHERE VAQ20 IN ( 0, 1, 2, 5 ) AND VAQ21 <> 2 AND CAB01 NOT IN ( 4, 5, 7, 8, 9 ) AND ISNULL(vap01, 0) = 0 AND VAA07 IN ( SELECT VAE01 FROM #医生 ) GROUP BY vaa07 ) t WHERE t.总病历数 > 0 AND t.VAA07 = #医生.VAE01 END IF @ltype = 2 BEGIN UPDATE #医生 SET stateImage = ISNULL(( CASE 总病历数 WHEN 0 THEN 0 ELSE CASE ( CASE WHEN 已一级质控病历数 = 总病历数 THEN 2 ELSE 0 END ) | ( CASE WHEN 已二级质控病历数 = 总病历数 THEN 4 ELSE 0 END ) | ( CASE WHEN 已三级质控病历数 = 总病历数 THEN 8 ELSE 0 END ) WHEN 0 THEN 1 ELSE ( CASE WHEN 已一级质控病历数 = 总病历数 THEN 2 ELSE 0 END ) | ( CASE WHEN 已二级质控病历数 = 总病历数 THEN 4 ELSE 0 END ) | ( CASE WHEN 已三级质控病历数 = 总病历数 THEN 8 ELSE 0 END ) END END ), 0) , version = t.version FROM ( SELECT SUM(CAST(SUBSTRING(ISNULL(QAD08, ''001.001.000.000''), 13, 3) AS INT) & 1) AS 已一级质控病历数 , SUM(CAST(SUBSTRING(ISNULL(QAD08, ''001.001.000.000''), 13, 3) AS INT) & 2) / 2 AS 已二级质控病历数 , SUM(CAST(SUBSTRING(ISNULL(QAD08, ''001.001.000.000''), 13, 3) AS INT) & 4) / 4 AS 已三级质控病历数 , COUNT(qad1.vaq01) AS 总病历数 , VAA07 , MAX(SUBSTRING(ISNULL(qad08, ''001.001.000.000''), 1, 3)) AS version FROM VAQ1 LEFT JOIN QAD1 ON vaq1.QAD01 = qad1.QAD01 WHERE VAQ20 IN ( 0, 1, 2, 5 ) AND VAQ21 <> 2 AND CAB01 IN ( 3, 4 ) AND ISNULL(vap01, 0) = 0 AND VAA07 IN ( SELECT VAE01 FROM #医生 ) GROUP BY vaa07 ) t WHERE t.总病历数 > 0 AND t.VAA07 = #医生.VAE01 END; update #医生 set BCK03=#医生.BCK03+''(''+rtrim(cast(countVAA01 as varchar(10))) +''人)'' from (select count(vae01) as countVAA01,BCK03 FROM #医生 where (一级质控员=@bce03 or 二级质控员=@bce03 OR @userLevel=3) and exists( select vaq1.vaq01 from vaq1 join qad1 on vaq1.qad01=qad1.qad01 where (一级质控员=@bce03 or 二级质控员=@bce03 OR @userLevel=3) and exists( select vaq1.vaq01 from vaq1 join qad1 on vaq1.qad01=qad1.qad01 where vaq1.vaq21<>2 and vaq20 not in (0,4) and QAD07 in (select * from split(@QAD07,'','')) and vaq1.VAA07=#医生.vae01)) group by #医生.BCK03) t where t.BCK03=#医生.BCK03 SELECT * FROM #医生 WHERE ( 一级质控员 = @bce03 OR 二级质控员 = @bce03 OR @userLevel = 3 ) AND EXISTS ( SELECT vaq1.vaq01 FROM vaq1 JOIN qad1 ON vaq1.qad01 = qad1.qad01 WHERE vaq1.vaq21 <> 2 AND vaq20 NOT IN ( 0, 4 ) AND QAD07 IN ( SELECT * FROM split(@QAD07, '','') ) AND vaq1.VAA07 = #医生.vae01 ) DROP TABLE #医生<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid UseW="1"><Sql>SELECT a.BCK01,a.BCK03,a.BCK02,''0'' as IfDef FROM BCK1 a Where EXISTS(SELECT b.* FROM BAZ1 b WHERE a.BCK01=b.BCK01 AND b.BAU01 IN (''01'') AND b.ACF01 IN(2,3)) AND EXISTS(SELECT c.* FROM BDR1 c WHERE a.BCK01 = c.BCK01 AND c.BCE01 =&lt;BCE01&gt; ) </Sql><HD><Ie FD="BCK01" CA="科室ID" WD="100"/><Ie FD="BCK03" CA="科室名称" WD="100"/></HD><QF><Ie FD="BCK03" WE="7"/></QF></Grid></Configs>-1=自动选择 3=三级质控（病案室已质控） 2=二级质控（主任医师已质控） 1=一级质控（主治医师已质控） 0=未质控 1=医师工作站 2=护士工作站 ' where Code='EMR.QC.100001' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.QC.100002') begin --插入 Declare @Report_Id_1324 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1324 out Declare @Report_Id_1324_CateID int Set @Report_Id_1324_CateID = (select id from Report_Categories where code='EMR.QC') 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_1324, 'EMR.QC.100002', '护士病人列表', Null, Null, @Report_Id_1324_CateID, 1, 4, 600, 0, Null, '2016-12-20 15:59:53', '2016-12-20 16:00:56', Null, ' A319EEFC89A5A53E6173570CD3ADE2CFDECLARE @lBCK01 varchar(500), @lType int, @lDt1 varchar(30), @lDt2 varchar(30), @BCE01 int , @QAD07 varchar(50) set @lbck01=:bck01 set @lbck01=CASE isnull(@lbck01,'''')WHEN '''' THEN ''0'' ELSE @lbck01 END SET @lType = :type Set @lDt1 = Convert(varchar(10),Cast(:BDate as datetime),21)+'' 00:00:00.000'' Set @lDt2 = Convert(varchar(10),Cast(:EDate as datetime),21)+'' 23:59:59.999'' set @BCE01=:BCE01 set @qad07=isnull(:QAD07,''-1'') if @QAD07=''-1'' set @QAD07=''0,1,2,3'' DECLARE @userLevel INT , @一二级质控员使用主治主任医生 INT --读取一级质控员是否使用主任医生设置 1是 0否 SET @一二级质控员使用主治主任医生 = ISNULL(( SELECT value FROM dbo.SYS_Parameters WHERE ProductID = 600 AND paramno = 52 ), 1) SELECT @userLevel = CASE WHEN aaa02 = ''三'' THEN 3 ELSE 0 END FROM ( SELECT TOP ( 1 ) baa1.AAA01 , SUBSTRING(aaa02, 1, 1) AS AAA02 FROM baa1 JOIN aaa1 ON baa1.AAA01 = aaa1.AAA01 WHERE bce01 = @BCE01 AND aaa02 LIKE ''__质控员'' ORDER BY CHARINDEX(SUBSTRING(aaa02, 1, 1), ''一,二,三'') DESC ) t --当传入科室参数为所有部门时 如果当前操作员不是三级质控员，将科室参数改为当前操作员的所有业务科室 DECLARE @bce03 VARCHAR(20) SELECT @bce03 = BCE03 FROM BCE1 WHERE bce01 = @BCE01 IF @LBCK01 = ''0'' AND @userLevel < 3 BEGIN DECLARE @_bck01s VARCHAR(4000) SET @_bck01s = ( SELECT a.BCK01 , a.BCK03 , a.BCK02 , ''0'' AS IfDef FROM BCK1 a WHERE EXISTS ( SELECT b.* FROM BAZ1 b WHERE a.BCK01 = b.BCK01 AND b.BAU01 IN ( ''02'' ) AND b.ACF01 IN ( 2, 3 ) ) AND EXISTS ( SELECT c.* FROM BDR1 c WHERE a.BCK01 = c.BCK01 AND c.BCE01 = @BCE01 ) ORDER BY A.BCK03 FOR XML PATH('''') ) SET @LBCK01 = SUBSTRING(@_bck01s, 1, LEN(@_bck01s) - 1) END SELECT VAE01 , VAE02 , a.VAA01 , c.VAA05 , c.VAA04 , d.ABW02 , BCK01D , BCQ04B , VAE26 , VAE27 , VAE46 , a.AAU01 , a.ACK01 , Agep = CASE WHEN ISNULL(a.VAE46, 0) = 0 THEN NULL ELSE ( LTRIM(STR(a.VAE46)) + e.AAU02 ) END , CASE @一二级质控员使用主治主任医生 WHEN 1 THEN VAM31 ELSE VAMA4 END AS 一级质控员 , CASE @ltype WHEN 2 THEN VAM35 ELSE CASE @一二级质控员使用主治主任医生 WHEN 1 THEN VAM27 ELSE VAMA5 END END AS 二级质控员 , CAST(0 AS TINYINT) AS stateImage , 0 version , B.BCK03 + ''('' + CASE WHEN VAE44 <= 3 THEN ''在院 '' ELSE ''出院 '' END + '')'' AS BCK03 , a.BCE03C , a.VAE11 , g.AAG02 INTO #医生 FROM vae1 a LEFT JOIN bck1 b ON b.bck01 = a.bck01d LEFT JOIN VAA1 c ON c.VAA01 = a.VAA01 LEFT JOIN abw1 d ON c.abw01 = d.abw01 LEFT JOIN aau1 e ON a.AAU01 = e.AAU01 LEFT JOIN VAM1 f ON a.VAE01 = f.VAA07 LEFT JOIN BBY1 e1 ON a.AAG01 = e1.BBY01 LEFT JOIN AAG1 g ON e1.BCF01 = g.AAG01 WHERE vae44 >= 2 AND vae44 <= 5 AND ( @userLevel = 3 OR a.bck01c IN ( SELECT * FROM split(@LBCK01, '','') ) ) AND ( ( VAE26 BETWEEN @lDt1 AND @lDt2 ) OR VAE44 IN ( 1, 2 ) ) -------------------------------------------------------------------------------------- UPDATE #医生 SET stateImage = ISNULL(( CASE 总病历数 WHEN 0 THEN 0 ELSE CASE ( CASE WHEN 已一级质控病历数 = 总病历数 THEN 2 ELSE 0 END ) | ( CASE WHEN 已二级质控病历数 = 总病历数 THEN 4 ELSE 0 END ) | ( CASE WHEN 已三级质控病历数 = 总病历数 THEN 8 ELSE 0 END ) WHEN 0 THEN 1 ELSE ( CASE WHEN 已一级质控病历数 = 总病历数 THEN 2 ELSE 0 END ) | ( CASE WHEN 已二级质控病历数 = 总病历数 THEN 4 ELSE 0 END ) | ( CASE WHEN 已三级质控病历数 = 总病历数 THEN 8 ELSE 0 END ) END END ), 0) , version = t.version FROM ( SELECT SUM(CAST(SUBSTRING(ISNULL(QAD08, ''001.001.000.000''), 13, 3) AS INT) & 1) AS 已一级质控病历数 , SUM(CAST(SUBSTRING(ISNULL(QAD08, ''001.001.000.000''), 13, 3) AS INT) & 2) / 2 AS 已二级质控病历数 , SUM(CAST(SUBSTRING(ISNULL(QAD08, ''001.001.000.000''), 13, 3) AS INT) & 4) / 4 AS 已三级质控病历数 , COUNT(qad1.vaq01) AS 总病历数 , VAA07 , MAX(SUBSTRING(ISNULL(qad08, ''001.001.000.000''), 1, 3)) AS version FROM VAQ1 LEFT JOIN QAD1 ON vaq1.QAD01 = qad1.QAD01 WHERE VAQ20 IN ( 0, 1, 2, 5 ) AND VAQ21 <> 2 AND CAB01 IN ( 3, 4 ) AND ISNULL(vap01, 0) = 0 AND VAA07 IN ( SELECT VAE01 FROM #医生 ) GROUP BY vaa07 ) t WHERE t.总病历数 > 0 AND t.VAA07 = #医生.VAE01 UPDATE #医生 SET BCK03 = #医生.BCK03 + ''('' + RTRIM(CAST(countVAA01 AS VARCHAR(10))) + ''人)'' FROM ( SELECT COUNT(vae01) AS countVAA01 , BCK03 FROM #医生 WHERE ( 一级质控员 = @bce03 OR 二级质控员 = @bce03 OR @userLevel = 3 ) AND EXISTS ( SELECT vaq1.vaq01 FROM vaq1 JOIN qad1 ON vaq1.qad01 = qad1.qad01 WHERE ( 一级质控员 = @bce03 OR 二级质控员 = @bce03 OR @userLevel = 3 ) AND EXISTS ( SELECT vaq1.vaq01 FROM vaq1 JOIN qad1 ON vaq1.qad01 = qad1.qad01 WHERE vaq1.vaq21 <> 2 AND vaq20 NOT IN ( 0, 4 ) AND QAD07 IN ( SELECT * FROM split(@QAD07, '','') ) AND vaq1.VAA07 = #医生.vae01 ) ) GROUP BY #医生.BCK03 ) t WHERE t.BCK03 = #医生.BCK03 SELECT * FROM #医生 WHERE ( 一级质控员 = @bce03 OR 二级质控员 = @bce03 OR @userLevel = 3 ) AND EXISTS ( SELECT vaq1.vaq01 FROM vaq1 JOIN qad1 ON vaq1.qad01 = qad1.qad01 WHERE vaq1.vaq21 <> 2 AND vaq20 NOT IN ( 0, 4 ) AND QAD07 IN ( SELECT * FROM split(@QAD07, '','') ) AND vaq1.VAA07 = #医生.vae01 ) DROP TABLE #医生<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid UseW="1"><Sql>SELECT a.BCK01,a.BCK03,a.BCK02,''0'' as IfDef FROM BCK1 a Where EXISTS(SELECT b.* FROM BAZ1 b WHERE a.BCK01=b.BCK01 AND b.BAU01 IN (''01'') AND b.ACF01 IN(2,3)) AND EXISTS(SELECT c.* FROM BDR1 c WHERE a.BCK01 = c.BCK01 AND c.BCE01 =&lt;BCE01&gt; ) </Sql><HD><Ie FD="BCK01" CA="科室ID" WD="100"/><Ie FD="BCK03" CA="科室名称" WD="100"/></HD><QF><Ie FD="BCK03" WE="7"/></QF></Grid></Configs>-1=自动选择 3=三级质控（病案室已质控） 2=二级质控（主任医师已质控） 1=一级质控（主治医师已质控） 0=未质控 1=医师工作站 2=护士工作站 ', Null, 0, 1, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' A319EEFC89A5A53E6173570CD3ADE2CFDECLARE @lBCK01 varchar(500), @lType int, @lDt1 varchar(30), @lDt2 varchar(30), @BCE01 int , @QAD07 varchar(50) set @lbck01=:bck01 set @lbck01=CASE isnull(@lbck01,'''')WHEN '''' THEN ''0'' ELSE @lbck01 END SET @lType = :type Set @lDt1 = Convert(varchar(10),Cast(:BDate as datetime),21)+'' 00:00:00.000'' Set @lDt2 = Convert(varchar(10),Cast(:EDate as datetime),21)+'' 23:59:59.999'' set @BCE01=:BCE01 set @qad07=isnull(:QAD07,''-1'') if @QAD07=''-1'' set @QAD07=''0,1,2,3'' DECLARE @userLevel INT , @一二级质控员使用主治主任医生 INT --读取一级质控员是否使用主任医生设置 1是 0否 SET @一二级质控员使用主治主任医生 = ISNULL(( SELECT value FROM dbo.SYS_Parameters WHERE ProductID = 600 AND paramno = 52 ), 1) SELECT @userLevel = CASE WHEN aaa02 = ''三'' THEN 3 ELSE 0 END FROM ( SELECT TOP ( 1 ) baa1.AAA01 , SUBSTRING(aaa02, 1, 1) AS AAA02 FROM baa1 JOIN aaa1 ON baa1.AAA01 = aaa1.AAA01 WHERE bce01 = @BCE01 AND aaa02 LIKE ''__质控员'' ORDER BY CHARINDEX(SUBSTRING(aaa02, 1, 1), ''一,二,三'') DESC ) t --当传入科室参数为所有部门时 如果当前操作员不是三级质控员，将科室参数改为当前操作员的所有业务科室 DECLARE @bce03 VARCHAR(20) SELECT @bce03 = BCE03 FROM BCE1 WHERE bce01 = @BCE01 IF @LBCK01 = ''0'' AND @userLevel < 3 BEGIN DECLARE @_bck01s VARCHAR(4000) SET @_bck01s = ( SELECT a.BCK01 , a.BCK03 , a.BCK02 , ''0'' AS IfDef FROM BCK1 a WHERE EXISTS ( SELECT b.* FROM BAZ1 b WHERE a.BCK01 = b.BCK01 AND b.BAU01 IN ( ''02'' ) AND b.ACF01 IN ( 2, 3 ) ) AND EXISTS ( SELECT c.* FROM BDR1 c WHERE a.BCK01 = c.BCK01 AND c.BCE01 = @BCE01 ) ORDER BY A.BCK03 FOR XML PATH('''') ) SET @LBCK01 = SUBSTRING(@_bck01s, 1, LEN(@_bck01s) - 1) END SELECT VAE01 , VAE02 , a.VAA01 , c.VAA05 , c.VAA04 , d.ABW02 , BCK01D , BCQ04B , VAE26 , VAE27 , VAE46 , a.AAU01 , a.ACK01 , Agep = CASE WHEN ISNULL(a.VAE46, 0) = 0 THEN NULL ELSE ( LTRIM(STR(a.VAE46)) + e.AAU02 ) END , CASE @一二级质控员使用主治主任医生 WHEN 1 THEN VAM31 ELSE VAMA4 END AS 一级质控员 , CASE @ltype WHEN 2 THEN VAM35 ELSE CASE @一二级质控员使用主治主任医生 WHEN 1 THEN VAM27 ELSE VAMA5 END END AS 二级质控员 , CAST(0 AS TINYINT) AS stateImage , 0 version , B.BCK03 + ''('' + CASE WHEN VAE44 <= 3 THEN ''在院 '' ELSE ''出院 '' END + '')'' AS BCK03 , a.BCE03C , a.VAE11 , g.AAG02 INTO #医生 FROM vae1 a LEFT JOIN bck1 b ON b.bck01 = a.bck01d LEFT JOIN VAA1 c ON c.VAA01 = a.VAA01 LEFT JOIN abw1 d ON c.abw01 = d.abw01 LEFT JOIN aau1 e ON a.AAU01 = e.AAU01 LEFT JOIN VAM1 f ON a.VAE01 = f.VAA07 LEFT JOIN BBY1 e1 ON a.AAG01 = e1.BBY01 LEFT JOIN AAG1 g ON e1.BCF01 = g.AAG01 WHERE vae44 >= 2 AND vae44 <= 5 AND ( @userLevel = 3 OR a.bck01c IN ( SELECT * FROM split(@LBCK01, '','') ) ) AND ( ( VAE26 BETWEEN @lDt1 AND @lDt2 ) OR VAE44 IN ( 1, 2 ) ) -------------------------------------------------------------------------------------- UPDATE #医生 SET stateImage = ISNULL(( CASE 总病历数 WHEN 0 THEN 0 ELSE CASE ( CASE WHEN 已一级质控病历数 = 总病历数 THEN 2 ELSE 0 END ) | ( CASE WHEN 已二级质控病历数 = 总病历数 THEN 4 ELSE 0 END ) | ( CASE WHEN 已三级质控病历数 = 总病历数 THEN 8 ELSE 0 END ) WHEN 0 THEN 1 ELSE ( CASE WHEN 已一级质控病历数 = 总病历数 THEN 2 ELSE 0 END ) | ( CASE WHEN 已二级质控病历数 = 总病历数 THEN 4 ELSE 0 END ) | ( CASE WHEN 已三级质控病历数 = 总病历数 THEN 8 ELSE 0 END ) END END ), 0) , version = t.version FROM ( SELECT SUM(CAST(SUBSTRING(ISNULL(QAD08, ''001.001.000.000''), 13, 3) AS INT) & 1) AS 已一级质控病历数 , SUM(CAST(SUBSTRING(ISNULL(QAD08, ''001.001.000.000''), 13, 3) AS INT) & 2) / 2 AS 已二级质控病历数 , SUM(CAST(SUBSTRING(ISNULL(QAD08, ''001.001.000.000''), 13, 3) AS INT) & 4) / 4 AS 已三级质控病历数 , COUNT(qad1.vaq01) AS 总病历数 , VAA07 , MAX(SUBSTRING(ISNULL(qad08, ''001.001.000.000''), 1, 3)) AS version FROM VAQ1 LEFT JOIN QAD1 ON vaq1.QAD01 = qad1.QAD01 WHERE VAQ20 IN ( 0, 1, 2, 5 ) AND VAQ21 <> 2 AND CAB01 IN ( 3, 4 ) AND ISNULL(vap01, 0) = 0 AND VAA07 IN ( SELECT VAE01 FROM #医生 ) GROUP BY vaa07 ) t WHERE t.总病历数 > 0 AND t.VAA07 = #医生.VAE01 UPDATE #医生 SET BCK03 = #医生.BCK03 + ''('' + RTRIM(CAST(countVAA01 AS VARCHAR(10))) + ''人)'' FROM ( SELECT COUNT(vae01) AS countVAA01 , BCK03 FROM #医生 WHERE ( 一级质控员 = @bce03 OR 二级质控员 = @bce03 OR @userLevel = 3 ) AND EXISTS ( SELECT vaq1.vaq01 FROM vaq1 JOIN qad1 ON vaq1.qad01 = qad1.qad01 WHERE ( 一级质控员 = @bce03 OR 二级质控员 = @bce03 OR @userLevel = 3 ) AND EXISTS ( SELECT vaq1.vaq01 FROM vaq1 JOIN qad1 ON vaq1.qad01 = qad1.qad01 WHERE vaq1.vaq21 <> 2 AND vaq20 NOT IN ( 0, 4 ) AND QAD07 IN ( SELECT * FROM split(@QAD07, '','') ) AND vaq1.VAA07 = #医生.vae01 ) ) GROUP BY #医生.BCK03 ) t WHERE t.BCK03 = #医生.BCK03 SELECT * FROM #医生 WHERE ( 一级质控员 = @bce03 OR 二级质控员 = @bce03 OR @userLevel = 3 ) AND EXISTS ( SELECT vaq1.vaq01 FROM vaq1 JOIN qad1 ON vaq1.qad01 = qad1.qad01 WHERE vaq1.vaq21 <> 2 AND vaq20 NOT IN ( 0, 4 ) AND QAD07 IN ( SELECT * FROM split(@QAD07, '','') ) AND vaq1.VAA07 = #医生.vae01 ) DROP TABLE #医生<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid UseW="1"><Sql>SELECT a.BCK01,a.BCK03,a.BCK02,''0'' as IfDef FROM BCK1 a Where EXISTS(SELECT b.* FROM BAZ1 b WHERE a.BCK01=b.BCK01 AND b.BAU01 IN (''01'') AND b.ACF01 IN(2,3)) AND EXISTS(SELECT c.* FROM BDR1 c WHERE a.BCK01 = c.BCK01 AND c.BCE01 =&lt;BCE01&gt; ) </Sql><HD><Ie FD="BCK01" CA="科室ID" WD="100"/><Ie FD="BCK03" CA="科室名称" WD="100"/></HD><QF><Ie FD="BCK03" WE="7"/></QF></Grid></Configs>-1=自动选择 3=三级质控（病案室已质控） 2=二级质控（主任医师已质控） 1=一级质控（主治医师已质控） 0=未质控 1=医师工作站 2=护士工作站 ' where Code='EMR.QC.100002' end GO IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.QC.100003') begin --插入 Declare @Report_Id_1354 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1354 out Declare @Report_Id_1354_CateID int Set @Report_Id_1354_CateID = (select id from Report_Categories where code='EMR.QC') 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_1354, 'EMR.QC.100003', '门诊病人列表', Null, Null, @Report_Id_1354_CateID, 1, 4, 600, 0, Null, '2017-04-11 16:46:44', '2017-04-11 16:46:44', Null, ' 10DC86659DFB59D7F31BAB07C4086849DECLARE @lBCK01 varchar(500), @lDt1 varchar(30), @lDt2 varchar(30), @BCE01 int , @QAD07 varchar(50) set @lbck01=:bck01 set @lbck01=CASE isnull(@lbck01,'''')WHEN '''' THEN ''0'' ELSE @lbck01 END Set @lDt1 = Convert(varchar(10),Cast(:BDate as datetime),21)+'' 00:00:00.000'' Set @lDt2 = Convert(varchar(10),Cast(:EDate as datetime),21)+'' 23:59:59.999'' set @BCE01=:BCE01 set @qad07=isnull(:QAD07,''-1'') if @QAD07=''-1'' set @QAD07=''0,1,2,3'' DECLARE @userLevel INT , @一二级质控员使用主治主任医生 INT -- 判断当前操作员是否是三级质控员 如果是设置@userlevel=3 否则@userlevel=0 SELECT @userLevel = CASE WHEN aaa02 = ''三'' THEN 3 ELSE 0 END FROM ( SELECT TOP ( 1 ) baa1.AAA01 , SUBSTRING(aaa02, 1, 1) AS AAA02 FROM baa1 JOIN aaa1 ON baa1.AAA01 = aaa1.AAA01 WHERE bce01 = @BCE01 AND aaa02 LIKE ''__质控员'' ORDER BY CHARINDEX(SUBSTRING(aaa02, 1, 1), ''一,二,三'') DESC ) t declare @lPara varchar(20),@VAC01 int set @lPara = ''03'' set @VAC01 = 0 if object_id(''tempdb..#tmpVAC'') is not null drop table #tmpVAC select b.VAC01,b.VAC02,b.VAC06,b.VAC10,b.BCE03A,b.VAC36,b.VAC42,b.BAK05,b.VAC51,b.VAA10,b.VAC34,b.VAC35,b.ABC02 ,b.BCK01A,b.BCE01A,b.VAC70,b.VAA01,b.AAU01,b.BDP02,0 BDP05,b.VAC09 ,b.VAC74 VAA03,b.VAC75 VAA05,b.VAC77 VAA15 ,case when b.VAC76=''1'' then ''男'' when b.VAC76=''2'' then ''女'' else ''未知'' end ABW02 , b.VAC71, CAST(0 AS TINYINT) AS stateImage , 0 as version,bck1.bck03 into #tmpVAC from VAC1 b with(nolock) LEFT JOIN bck1 with(nolock) ON b.bck01a=bck1.bck01 WHERE b.VAC34 >= 1 and b.VAC45=1 AND b.VAC09 >-3 AND b.BCK01A IN (SELECT * FROM split( @lBCK01,'','')) and b.VAC35 between @ldt1 and @ldt2 and exists( select vaq1.vaq01 from vaq1 join qad1 on vaq1.qad01=qad1.qad01 where vaq1.vaq21<>2 and vaq20 in (1) and cab01=1 and QAD07 in (select * from split(@QAD07,'','')) and vaq1.VAA07=b.vac01 ) --@VAC01 就诊ID --VAC34 --执行状态 0：候诊；1：接诊;2：完诊 --vac45 状态, -- Status, 0=预约, 1=挂号, 2=退号, 3=登记(就诊信息成功后为3，挂号收费后为1) --VAC09 --号序为0=挂号、-1= 表示收费模式直接收费病人、-2=医生站简易挂号、-3=体检 --vac35 --执行时间 --删除掉没有完成病历的人员 update a set a.BDP05=isnull(b.BDP05,0) from #tmpVAC a join BDP1 b on a.BDP02=b.BDP02 UPDATE #tmpVAC SET stateImage = ISNULL(( CASE 总病历数 WHEN 0 THEN 0 ELSE CASE ( CASE WHEN 已一级质控病历数 = 总病历数 THEN 2 ELSE 0 END ) | ( CASE WHEN 已二级质控病历数 = 总病历数 THEN 4 ELSE 0 END ) | ( CASE WHEN 已三级质控病历数 = 总病历数 THEN 8 ELSE 0 END ) WHEN 0 THEN 1 ELSE ( CASE WHEN 已一级质控病历数 = 总病历数 THEN 2 ELSE 0 END ) | ( CASE WHEN 已二级质控病历数 = 总病历数 THEN 4 ELSE 0 END ) | ( CASE WHEN 已三级质控病历数 = 总病历数 THEN 8 ELSE 0 END ) END END ), 0) , version = t.version FROM ( SELECT SUM(CAST(SUBSTRING(ISNULL(QAD08, ''001.001.000.000''), 13, 3) AS INT) & 1) AS 已一级质控病历数 , SUM(CAST(SUBSTRING(ISNULL(QAD08, ''001.001.000.000''), 13, 3) AS INT) & 2) / 2 AS 已二级质控病历数 , SUM(CAST(SUBSTRING(ISNULL(QAD08, ''001.001.000.000''), 13, 3) AS INT) & 4) / 4 AS 已三级质控病历数 , COUNT(qad1.vaq01) AS 总病历数 , VAA07 , MAX(SUBSTRING(ISNULL(qad08, ''001.001.000.000''), 1, 3)) AS version FROM VAQ1 LEFT JOIN QAD1 ON vaq1.QAD01 = qad1.QAD01 WHERE VAQ20 IN ( 0, 1, 2, 5 ) AND VAQ21 <> 2 AND CAB01 IN ( 1 ) AND ISNULL(vap01, 0) = 0 AND VAA07 IN ( SELECT vac01 FROM #tmpVAC ) GROUP BY vaa07 ) t WHERE t.总病历数 > 0 AND t.VAA07 = #tmpVAC.vac01 update #tmpVAC set BCK03=#tmpVAC.BCK03+''(''+rtrim(cast(countVAA01 as varchar(10))) +''人)'' from (select count(vaC01) as countVAA01,BCK03 FROM #tmpVAC where ( @userLevel=3) and exists( select vaq1.vaq01 from vaq1 join qad1 on vaq1.qad01=qad1.qad01 where (@userLevel=3) and exists( select vaq1.vaq01 from vaq1 join qad1 on vaq1.qad01=qad1.qad01 where vaq1.vaq21<>2 and vaq20 in (1) and QAD07 in (select * from split(@QAD07,'','')) and vaq1.VAA07=#tmpVAC.vac01)) group by #tmpVAC.BCK03) t where t.BCK03=#tmpVAC.BCK03 SELECT b.VAA01,b.VAA03,b.VAA05,b.VAA15,b.ABW02,f.AAU02 ,Agep = CASE WHEN isnull(b.VAA10,0)=0 THEN NULL else (ltrim(str(b.VAA10))+f.AAU02) END ,b.VAC01,b.VAC02,b.VAC06,b.VAC10,b.BCE03A,b.VAC36,b.VAC42,b.BAK05,b.VAC51 , b.BCK03, b.VAC34, b.VAC35, b.ABC02, b.BCK01A,b.BCE01A ,b.VAC70 AS VAKID,b.BDP02,b.BDP05,b.VAC09,b.VAC01 as VAE01, null as 一级质控员 , null AS 二级质控员 , stateImage , version , VAC71 FROM #tmpVAC b LEFT JOIN AAU1 AS f ON b.AAU01 = f.AAU01 Order By b.VAC35 DESC if object_id(''tempdb..#tmpVAC'') is not null drop table #tmpVAC<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid UseW="1"><Sql>SELECT a.BCK01,a.BCK03,a.BCK02,''0'' as IfDef FROM BCK1 a Where EXISTS(SELECT b.* FROM BAZ1 b WHERE a.BCK01=b.BCK01 AND b.BAU01 IN (''01'') AND b.ACF01 IN(2,3)) AND EXISTS(SELECT c.* FROM BDR1 c WHERE a.BCK01 = c.BCK01 AND c.BCE01 =&lt;BCE01&gt; ) </Sql><HD><Ie FD="BCK01" CA="科室ID" WD="100"/><Ie FD="BCK03" CA="科室名称" WD="100"/></HD><QF><Ie FD="BCK03" WE="7"/></QF></Grid></Configs>-1=自动选择 3=三级质控（病案室已质控） 2=二级质控（主任医师已质控） 1=一级质控（主治医师已质控） 0=未质控 ', Null, 0, 1, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' 10DC86659DFB59D7F31BAB07C4086849DECLARE @lBCK01 varchar(500), @lDt1 varchar(30), @lDt2 varchar(30), @BCE01 int , @QAD07 varchar(50) set @lbck01=:bck01 set @lbck01=CASE isnull(@lbck01,'''')WHEN '''' THEN ''0'' ELSE @lbck01 END Set @lDt1 = Convert(varchar(10),Cast(:BDate as datetime),21)+'' 00:00:00.000'' Set @lDt2 = Convert(varchar(10),Cast(:EDate as datetime),21)+'' 23:59:59.999'' set @BCE01=:BCE01 set @qad07=isnull(:QAD07,''-1'') if @QAD07=''-1'' set @QAD07=''0,1,2,3'' DECLARE @userLevel INT , @一二级质控员使用主治主任医生 INT -- 判断当前操作员是否是三级质控员 如果是设置@userlevel=3 否则@userlevel=0 SELECT @userLevel = CASE WHEN aaa02 = ''三'' THEN 3 ELSE 0 END FROM ( SELECT TOP ( 1 ) baa1.AAA01 , SUBSTRING(aaa02, 1, 1) AS AAA02 FROM baa1 JOIN aaa1 ON baa1.AAA01 = aaa1.AAA01 WHERE bce01 = @BCE01 AND aaa02 LIKE ''__质控员'' ORDER BY CHARINDEX(SUBSTRING(aaa02, 1, 1), ''一,二,三'') DESC ) t declare @lPara varchar(20),@VAC01 int set @lPara = ''03'' set @VAC01 = 0 if object_id(''tempdb..#tmpVAC'') is not null drop table #tmpVAC select b.VAC01,b.VAC02,b.VAC06,b.VAC10,b.BCE03A,b.VAC36,b.VAC42,b.BAK05,b.VAC51,b.VAA10,b.VAC34,b.VAC35,b.ABC02 ,b.BCK01A,b.BCE01A,b.VAC70,b.VAA01,b.AAU01,b.BDP02,0 BDP05,b.VAC09 ,b.VAC74 VAA03,b.VAC75 VAA05,b.VAC77 VAA15 ,case when b.VAC76=''1'' then ''男'' when b.VAC76=''2'' then ''女'' else ''未知'' end ABW02 , b.VAC71, CAST(0 AS TINYINT) AS stateImage , 0 as version,bck1.bck03 into #tmpVAC from VAC1 b with(nolock) LEFT JOIN bck1 with(nolock) ON b.bck01a=bck1.bck01 WHERE b.VAC34 >= 1 and b.VAC45=1 AND b.VAC09 >-3 AND b.BCK01A IN (SELECT * FROM split( @lBCK01,'','')) and b.VAC35 between @ldt1 and @ldt2 and exists( select vaq1.vaq01 from vaq1 join qad1 on vaq1.qad01=qad1.qad01 where vaq1.vaq21<>2 and vaq20 in (1) and cab01=1 and QAD07 in (select * from split(@QAD07,'','')) and vaq1.VAA07=b.vac01 ) --@VAC01 就诊ID --VAC34 --执行状态 0：候诊；1：接诊;2：完诊 --vac45 状态, -- Status, 0=预约, 1=挂号, 2=退号, 3=登记(就诊信息成功后为3，挂号收费后为1) --VAC09 --号序为0=挂号、-1= 表示收费模式直接收费病人、-2=医生站简易挂号、-3=体检 --vac35 --执行时间 --删除掉没有完成病历的人员 update a set a.BDP05=isnull(b.BDP05,0) from #tmpVAC a join BDP1 b on a.BDP02=b.BDP02 UPDATE #tmpVAC SET stateImage = ISNULL(( CASE 总病历数 WHEN 0 THEN 0 ELSE CASE ( CASE WHEN 已一级质控病历数 = 总病历数 THEN 2 ELSE 0 END ) | ( CASE WHEN 已二级质控病历数 = 总病历数 THEN 4 ELSE 0 END ) | ( CASE WHEN 已三级质控病历数 = 总病历数 THEN 8 ELSE 0 END ) WHEN 0 THEN 1 ELSE ( CASE WHEN 已一级质控病历数 = 总病历数 THEN 2 ELSE 0 END ) | ( CASE WHEN 已二级质控病历数 = 总病历数 THEN 4 ELSE 0 END ) | ( CASE WHEN 已三级质控病历数 = 总病历数 THEN 8 ELSE 0 END ) END END ), 0) , version = t.version FROM ( SELECT SUM(CAST(SUBSTRING(ISNULL(QAD08, ''001.001.000.000''), 13, 3) AS INT) & 1) AS 已一级质控病历数 , SUM(CAST(SUBSTRING(ISNULL(QAD08, ''001.001.000.000''), 13, 3) AS INT) & 2) / 2 AS 已二级质控病历数 , SUM(CAST(SUBSTRING(ISNULL(QAD08, ''001.001.000.000''), 13, 3) AS INT) & 4) / 4 AS 已三级质控病历数 , COUNT(qad1.vaq01) AS 总病历数 , VAA07 , MAX(SUBSTRING(ISNULL(qad08, ''001.001.000.000''), 1, 3)) AS version FROM VAQ1 LEFT JOIN QAD1 ON vaq1.QAD01 = qad1.QAD01 WHERE VAQ20 IN ( 0, 1, 2, 5 ) AND VAQ21 <> 2 AND CAB01 IN ( 1 ) AND ISNULL(vap01, 0) = 0 AND VAA07 IN ( SELECT vac01 FROM #tmpVAC ) GROUP BY vaa07 ) t WHERE t.总病历数 > 0 AND t.VAA07 = #tmpVAC.vac01 update #tmpVAC set BCK03=#tmpVAC.BCK03+''(''+rtrim(cast(countVAA01 as varchar(10))) +''人)'' from (select count(vaC01) as countVAA01,BCK03 FROM #tmpVAC where ( @userLevel=3) and exists( select vaq1.vaq01 from vaq1 join qad1 on vaq1.qad01=qad1.qad01 where (@userLevel=3) and exists( select vaq1.vaq01 from vaq1 join qad1 on vaq1.qad01=qad1.qad01 where vaq1.vaq21<>2 and vaq20 in (1) and QAD07 in (select * from split(@QAD07,'','')) and vaq1.VAA07=#tmpVAC.vac01)) group by #tmpVAC.BCK03) t where t.BCK03=#tmpVAC.BCK03 SELECT b.VAA01,b.VAA03,b.VAA05,b.VAA15,b.ABW02,f.AAU02 ,Agep = CASE WHEN isnull(b.VAA10,0)=0 THEN NULL else (ltrim(str(b.VAA10))+f.AAU02) END ,b.VAC01,b.VAC02,b.VAC06,b.VAC10,b.BCE03A,b.VAC36,b.VAC42,b.BAK05,b.VAC51 , b.BCK03, b.VAC34, b.VAC35, b.ABC02, b.BCK01A,b.BCE01A ,b.VAC70 AS VAKID,b.BDP02,b.BDP05,b.VAC09,b.VAC01 as VAE01, null as 一级质控员 , null AS 二级质控员 , stateImage , version , VAC71 FROM #tmpVAC b LEFT JOIN AAU1 AS f ON b.AAU01 = f.AAU01 Order By b.VAC35 DESC if object_id(''tempdb..#tmpVAC'') is not null drop table #tmpVAC<?xml version="1.0"?> <Configs Ver="1"><Win SZ="1"/><Grid UseW="1"><Sql>SELECT a.BCK01,a.BCK03,a.BCK02,''0'' as IfDef FROM BCK1 a Where EXISTS(SELECT b.* FROM BAZ1 b WHERE a.BCK01=b.BCK01 AND b.BAU01 IN (''01'') AND b.ACF01 IN(2,3)) AND EXISTS(SELECT c.* FROM BDR1 c WHERE a.BCK01 = c.BCK01 AND c.BCE01 =&lt;BCE01&gt; ) </Sql><HD><Ie FD="BCK01" CA="科室ID" WD="100"/><Ie FD="BCK03" CA="科室名称" WD="100"/></HD><QF><Ie FD="BCK03" WE="7"/></QF></Grid></Configs>-1=自动选择 3=三级质控（病案室已质控） 2=二级质控（主任医师已质控） 1=一级质控（主治医师已质控） 0=未质控 ' where Code='EMR.QC.100003' end GO --delete from Report_Objects where CategoryID=104 /*自动质控*/ IF not EXISTS(SELECT * FROM Report_Objects WHERE code='EMR.QC.AUTO.000001') begin --插入 Declare @Report_Id_1074 int Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1074 out Declare @Report_Id_1074_CateID int Set @Report_Id_1074_CateID = (select id from Report_Categories where code='EMR.QC.AUTO') 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_1074, 'EMR.QC.AUTO.000001', '自动质控规则参数', '质控规则脚本可以引用的参娄', Null, @Report_Id_1074_CateID, 1, 4, 600, 0, Null, '2014-08-05 10:58:49', '2014-08-05 14:12:08', Null, ' E5BBE140A7F8A74FC0B0A1FA48B3AF2Cdeclare @BCK01 int,--科室ID @VAA01 int ,--病人ID @QAE01 int select @BCK01 as ''@BCK01'',@VAA01 as ''@VAQ01'',@QAE01 as ''@QAE01'' ', Null, 0, 1, Null, 0, Null) end else --更新 begin update Report_Objects set Config=' E5BBE140A7F8A74FC0B0A1FA48B3AF2Cdeclare @BCK01 int,--科室ID @VAA01 int ,--病人ID @QAE01 int select @BCK01 as ''@BCK01'',@VAA01 as ''@VAQ01'',@QAE01 as ''@QAE01'' ' where Code='EMR.QC.AUTO.000001' end GO