--删除旧记录
--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>12E2FC02F11AC590088C7EB08F66440B</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 ''1过敏记录'' as CateName,BBX05 as Caption,VAN01 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 ''2门诊诊断'' as FType,a.VAO15,VAO01 ROWNR,'''',0,0
FROM VAO1 a with(nolock) 
WHERE a.ACF01 = @ACF01 AND a.VAA07 = @VAA07 AND VAO11=1

UNION ALL
SELECT ''3入院诊断'' as FType,a.VAO15,VAO01 ROWNR,'''',0,0
FROM VAO2 a with(nolock) 
WHERE a.ACF01 = @ACF01 AND a.VAA07 = @VAA07 AND VAO11=2

UNION ALL
SELECT ''4出院诊断'' as FType,a.VAO15,VAO01 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 ''5其他诊断记录'' as FType,a.VAO15,VAO01 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临时医嘱'' 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临时医嘱'' 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>12E2FC02F11AC590088C7EB08F66440B</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 ''1过敏记录'' as CateName,BBX05 as Caption,VAN01 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 ''2门诊诊断'' as FType,a.VAO15,VAO01 ROWNR,'''',0,0
FROM VAO1 a with(nolock) 
WHERE a.ACF01 = @ACF01 AND a.VAA07 = @VAA07 AND VAO11=1

UNION ALL
SELECT ''3入院诊断'' as FType,a.VAO15,VAO01 ROWNR,'''',0,0
FROM VAO2 a with(nolock) 
WHERE a.ACF01 = @ACF01 AND a.VAA07 = @VAA07 AND VAO11=2

UNION ALL
SELECT ''4出院诊断'' as FType,a.VAO15,VAO01 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 ''5其他诊断记录'' as FType,a.VAO15,VAO01 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临时医嘱'' 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临时医嘱'' 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