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, '<?xml version="1.0"?>
<Root Ver="1.0" YIBAO="" NoDialog="" Preview="1111111111110001"><Config TimeOut="15" BrowMode="0"><DBLink Mode="1"></DBLink><Chart HasChart="0" ViewFirst="0"/><BrowUrl User="" Pass=""></BrowUrl><CTI Enabled="0" Tel1="" Tel2="" Tel3=""/><Print Edit="0" ExecSql="" Select="0" SelectDefault="1" Mode="0"/><OtherReport><File></File><Fun></Fun></OtherReport><MD5>F17610F57C0E495ECBEB9DDFC0D51218</MD5></Config><DTS><Script>DECLARE @VAA01 INT
DECLARE @VAA07 INT
DECLARE @VAP01 INT
DECLARE @ACF01 INT

SET @VAA01=:VAA01
SET @VAA07=:VAA07
SET @VAP01=:VAP01
SET @ACF01=:ACF01</Script><Ie Name="病人信息" Fixed="0" Visible="1" OrderBy="" OrderType="0" Bands="0" UseBCD="0"><Descrption></Descrption><Sql>DECLARE @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())&lt;=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())&lt;=10 and VAE87&gt;'''' 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    
END</Sql><Links/></Ie></DTS><Params><Ie Name="ACF01" Alias="类型" GroupName="" DefaultValue="2" Style="3" NotNull="1" Privilege="0"><Config KeyField="" DispField="" Flag="0">1=门诊
2=住院</Config></Ie><Ie Name="VAA01" Alias="病人ID" GroupName="" DefaultValue="" Style="0" NotNull="0" Privilege="0"><Config KeyField="" DispField="" Flag="0"></Config></Ie><Ie Name="VAA07" Alias="就诊ID" GroupName="" DefaultValue="" Style="0" NotNull="0" Privilege="0"><Config KeyField="" DispField="" Flag="0"></Config></Ie><Ie Name="VAP01" Alias="婴儿ID" GroupName="" DefaultValue="" Style="0" NotNull="0" Privilege="0"><Config KeyField="" DispField="" Flag="0"></Config></Ie></Params></Root>
', Null, 0, 0, Null, 0, Null)
end
else --更新
begin
  update Report_Objects set Config='<?xml version="1.0"?>
<Root Ver="1.0" YIBAO="" NoDialog="" Preview="1111111111110001"><Config TimeOut="15" BrowMode="0"><DBLink Mode="1"></DBLink><Chart HasChart="0" ViewFirst="0"/><BrowUrl User="" Pass=""></BrowUrl><CTI Enabled="0" Tel1="" Tel2="" Tel3=""/><Print Edit="0" ExecSql="" Select="0" SelectDefault="1" Mode="0"/><OtherReport><File></File><Fun></Fun></OtherReport><MD5>F17610F57C0E495ECBEB9DDFC0D51218</MD5></Config><DTS><Script>DECLARE @VAA01 INT
DECLARE @VAA07 INT
DECLARE @VAP01 INT
DECLARE @ACF01 INT

SET @VAA01=:VAA01
SET @VAA07=:VAA07
SET @VAP01=:VAP01
SET @ACF01=:ACF01</Script><Ie Name="病人信息" Fixed="0" Visible="1" OrderBy="" OrderType="0" Bands="0" UseBCD="0"><Descrption></Descrption><Sql>DECLARE @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())&lt;=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())&lt;=10 and VAE87&gt;'''' 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    
END</Sql><Links/></Ie></DTS><Params><Ie Name="ACF01" Alias="类型" GroupName="" DefaultValue="2" Style="3" NotNull="1" Privilege="0"><Config KeyField="" DispField="" Flag="0">1=门诊
2=住院</Config></Ie><Ie Name="VAA01" Alias="病人ID" GroupName="" DefaultValue="" Style="0" NotNull="0" Privilege="0"><Config KeyField="" DispField="" Flag="0"></Config></Ie><Ie Name="VAA07" Alias="就诊ID" GroupName="" DefaultValue="" Style="0" NotNull="0" Privilege="0"><Config KeyField="" DispField="" Flag="0"></Config></Ie><Ie Name="VAP01" Alias="婴儿ID" GroupName="" DefaultValue="" Style="0" NotNull="0" Privilege="0"><Config KeyField="" DispField="" Flag="0"></Config></Ie></Params></Root>
' where Code='EMR.PatientInfo.000001'
end

GO

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


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, '<?xml version="1.0"?>
<Root Ver="1.0" YIBAO="" NoDialog="" Preview="1111111111110001"><Config TimeOut="15" BrowMode="0"><DBLink Mode="1"></DBLink><Chart HasChart="0" ViewFirst="0"/><BrowUrl User="" Pass=""></BrowUrl><CTI Enabled="0" Tel1="" Tel2="" Tel3=""/><Print Edit="0" ExecSql="" Select="0" SelectDefault="1" Mode="0"/><OtherReport><File></File><Fun></Fun></OtherReport><MD5>B4C84933E5864E2D35784744C1C6784D</MD5></Config><DTS><Script></Script><Ie Name="新建数据源" Fixed="0" Visible="1" OrderBy="" OrderType="0" Bands="0" UseBCD="0"><Descrption></Descrption><Sql>
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&gt;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</Sql><Links/></Ie></DTS><Params/></Root>
', Null, 0, 1, Null, 0, Null)
end
else --更新
begin
  update Report_Objects set Config='<?xml version="1.0"?>
<Root Ver="1.0" YIBAO="" NoDialog="" Preview="1111111111110001"><Config TimeOut="15" BrowMode="0"><DBLink Mode="1"></DBLink><Chart HasChart="0" ViewFirst="0"/><BrowUrl User="" Pass=""></BrowUrl><CTI Enabled="0" Tel1="" Tel2="" Tel3=""/><Print Edit="0" ExecSql="" Select="0" SelectDefault="1" Mode="0"/><OtherReport><File></File><Fun></Fun></OtherReport><MD5>B4C84933E5864E2D35784744C1C6784D</MD5></Config><DTS><Script></Script><Ie Name="新建数据源" Fixed="0" Visible="1" OrderBy="" OrderType="0" Bands="0" UseBCD="0"><Descrption></Descrption><Sql>
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&gt;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</Sql><Links/></Ie></DTS><Params/></Root>
' where Code='EMR.Report.000003'
end

GO