--增加分类
GO
IF not EXISTS(SELECT * FROM SYS_ScriptCategories WHERE id=33)
insert into SYS_ScriptCategories(ID,Code,Name,ComponentID,xRowNum,ParentID,xLevel,Description)  Values(33, 'SC33', '住院医师工作站', 17, 0, 31, 0, Null)
else
print 'SYS_ScriptCategories.id=33 已经存在.'

GO


--删除旧记录
delete from SYS_Scripts where id=7718 --删除脚本内容
GO


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=7718)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(7718, 33, 'Sql_路径项目对应医嘱', '临床路径', '路径项目对应医嘱', 1, 1, Null, 1, 'declare @vcn01 int,@adate datetime,@BCK01C int,@BCK03C varchar(64),@BCE01A int,@BCE02A varchar(20),@BCE03A varchar(64)
declare @VAF14 varchar(60),@ahour int ,@ltype tinyint,@para382 varchar(10)
set @ltype = %d
set @vcn01 = %d
set @BCK01C = %d
set @BCE01A = %d
set @BCE02A = %s
set @BCE03A = %s
set @BCK03C = ISNULL((select BCK03 from BCK1 WHERE BCK01 = @BCK01C),'''')
set @adate = GETDATE()
if @ltype=1
begin
  set @ahour = cast(dbo.GetSysParamValue(100,105003,199) as int)
  set @para382 = dbo.GetSysParamValue(100,105003,382)
end
else
begin
  set @ahour = cast(dbo.GetSysParamValue(100,105001,199) as int)
  set @para382 = dbo.GetSysParamValue(100,105001,382)
end  
set @VAF14 = convert(varchar(16),DATEADD(HOUR,@ahour,@adate),120)
if OBJECT_ID(''tempdb..#kbmVCR'') is not null drop table #kbmVCR 
if OBJECT_ID(''tempdb..#tmpvaf'') is not null drop table #tmpvaf 
select a.VCO01,b.BIJ01 into #kbmVCR from VCO1 a join VCR1 b on b.VCO01 = a.VCO01 where a.VCN01 = @vcn01
select s.*
into #tmpvaf
from (
select 0 acheck,c.VAF01,c.VAF01A,c.ROWNR,c.BDA01,c.VAF11,c.BBX01,c.BBY01
  ,case when ((c.BDA01 = ''S'' or c.BDA01 = ''A'') and c.VAF01A > 0)OR (c.BDA01 = ''S'' and c.VAF01A = 0) 
      then REPLACE(c.VAF22,c.VAF14,@VAF14) else c.VAF22 end VAF22 
  ,case when c.VAF18 > 0 then c.VAF18 else null end VAF18,c.VAF19,c.VAF20,c.VAF21
  ,case when ((c.BDA01 = ''S'' or c.BDA01 = ''A'') and c.VAF01A > 0)OR (c.BDA01 = ''S'' and c.VAF01A = 0) then @VAF14 else c.VAF14 end VAF14
  ,c.VAF15,c.VAF23,c.VAF26,c.VAF27,c.VAF28,c.VAF29,c.VAF58,c.BCK01B,d.BCK03 BCK03B,c.BCK01C,e.BCK03 BCK03C
  ,c.VAF30,c.VAF32,c.VAF55,c.VAF59,c.VAF60,c.VAF61,c.VAF62,@adate VAF36,c.CBM01,c.BCE01A,c.BCE02A,c.BCE03A
  ,a.BII01,a.BIG01,a.VCO22 rnbig,a.ROWNR rnbii,1 BIJ20,a.VCO01
  ,case when c.BDA01 = ''T'' and c.VAF32 = 1 and c1.BBX13 = ''2'' then c.ROWNR + 99 else c.ROWNR end aRownr 
  ,case when c.BDA01 >= ''A'' and c.VAF01A > 0 then 1 else 0 end asign,0 fnew,1 BII12,1 mselect
  ,CASE WHEN c.BDA01 >= ''1'' and c.BDA01 <= ''3'' THEN g.BDG02B ELSE g1.BBY08 END AS funit,c.VAF31,c.VAF35,c.BCK01D
  ,CASE WHEN c.BDA01 >= ''1'' and c.BDA01 <= ''3'' THEN c1.BDG02 ELSE '''' END AS BDG02,c.VAF37,c.VAF07,f.BCK03 BCK03F,c.BIW02
  ,case when c.BDA01 >=''1'' and c.BDA01 <= ''2'' then h.VAF22+c.VAF26 else '''' end as UNameA,0 lUnite
from VCO1 a
join VCR1 b on b.VCO01 = a.VCO01
join VAF2 c on c.VAF01 = b.VAF01
left join VAF2 h on c.BDA01 >=''1'' and c.BDA01 <= ''2'' and h.BDA01=''T'' and h.VAF32=1 and h.VAF01 = c.VAF01A
left join BBX1 c1 on c1.BBX01 = c.BBX01
left join BCK1 d on d.BCK01 = c.BCK01B
left join BCK1 e on e.BCK01 = c.BCK01C
left join BCK1 f on f.BCK01 = c.BCK01D
LEFT JOIN BAG1 g ON c.BDA01 >= ''1'' and c.BDA01 <= ''3'' and g.BBY01 = c.BBY01
left join BBY1 g1 on g1.BBY01 = g.BBY01
where a.VCN01 = @vcn01 and a.VCO10=1 and ISNULL(a.VCO16,''1901-01-01'') > ''1911-12-01'' 
union all
select 1 acheck,a.BIJ01 VAF01,a.BIJ01A VAF01A,a.ROWNR
  ,case when f.BDA01 = ''T'' and f.BBX13 = ''4'' and a.BIJ21 = 1 then ''8'' else isnull(f.BDA01,''0'') end BDA01
  ,a.BIJ04 VAF11,a.BBX01,a.BBY01
  ,case when f.BDA01 = ''S'' and a.BIJ01A = 0 and @para382=''1'' then REPLACE(SUBSTRING(a.BIJ07,17,300),''拟行'',''行'')
        when f.BDA01 = ''S'' and a.BIJ01A = 0 and @para382<>''1'' then REPLACE(a.BIJ07,a.BIJ10,@VAF14) else a.BIJ07 end VAF22 
  ,case when a.BIJ08 > 0 then a.BIJ08 else null end VAF18
  ,case when a.BIJ08 > 0 and f.BDA01 >= ''1'' and f.BDA01 <= ''3'' then cast(CONVERT(FLOAT,a.BIJ08) as varchar)+'' ''+f.BDG02 else null end VAF19
  ,case when f.BDA01 = ''3'' then a.BIJ08 else 0 end VAF20
  ,CASE WHEN f.BDA01 >= ''1'' and f.BDA01 <= ''2'' then cast(a.BIJ09/g.BAG07 as numeric(12,2)) else a.BIJ09 END AS VAF21  
  ,case when ((f.BDA01 = ''S'' or f.BDA01 = ''A'') and a.BIJ01A > 0)OR (f.BDA01 = ''S'' and a.BIJ01A = 0) then @VAF14 else a.BIJ10 end VAF14
  ,a.BIJ11 VAF15,a.BIJ12 VAF23,a.BIJ13 VAF26,a.BIJ14 VAF27,a.BIJ15 VAF28,a.BIJ16 VAF29
  ,a.BIJ17 VAF58,a.BCK01 BCK01B,e.BCK03 BCK03B,@BCK01C ,@BCK03C,a.BIJ19 VAF30,a.BIJ21 VAF32,'''' VAF55,a.BIJ22 VAF59,a.BIJ23 VAF60
  ,a.BIJ14 VAF61,a.BIJ14 VAF62,@adate VAF36,c.VCO01 CBM01,@BCE01A,@BCE02A,@BCE03A,c.BII01,c.BIG01,c.VCO22 rnbig,c.ROWNR rnbii,a.BIJ20,c.VCO01
  ,case when isnull(f.BDA01,''0'') = ''T'' and a.BIJ21 = 1 and f.BBX13 = ''2'' then a.ROWNR + 99 else a.ROWNR end aRownr 
  ,case when isnull(f.BDA01,''0'') >= ''A'' and a.BIJ01A > 0 then 1 else 0 end asign,1 fnew,c.BII12
  ,case when c.BII12 = 1 then 1 when c.BII12 in (0,2) and a.BIJ20 = 1 then 1 else 0 end mselect
  ,CASE WHEN f.BDA01 >= ''1'' and f.BDA01 <= ''3'' THEN g.BDG02B ELSE g1.BBY08 END AS funit,0 VAF31,0 VAF35,0 BCK01D
  ,CASE WHEN f.BDA01 >= ''1'' and f.BDA01 <= ''3'' THEN f.BDG02 ELSE '''' END AS BDG02,''1899-12-01'' VAF37,0 VAF07,'''' BCK03F,'''' BIW02
  ,case when f.BDA01 >=''1'' and f.BDA01 <= ''2'' then h.BIJ07 + a.BIJ13 else '''' end as UNameA,0 lUnite
from VCO1 c
join VDB1 a on a.BII01 = c.BII01 and a.VCN01 = c.VCN01
left join BBX1 f on f.BBX01 = a.BBX01
left join VDB1 h on f.BDA01>=''1'' and f.BDA01 <=''2'' and h.BIJ21=1 and h.BIJ01 = a.BIJ01A and h.vcn01 = a.vcn01 and h.bii01 = a.bii01
left join BCK1 e on e.BCK01 = a.BCK01
LEFT JOIN BAG1 g ON f.BDA01 >= ''1'' and f.BDA01 <= ''3'' and g.BBY01 = a.BBY01
left join BBY1 g1 on g1.BBY01 = g.BBY01
where c.VCN01 = @vcn01 and c.VCO10=1 and ((ISNULL(c.VCO16,''1901-01-01'') < ''1911-12-01'')
  or(ISNULL(c.VCO16,''1901-01-01'') > ''1911-12-01'' and not exists(select * from #kbmVCR k where k.VCO01 = c.VCO01 and k.BIJ01 = a.BIJ01)) ) ) s
order by s.rnbig,s.rnbii,s.VAF59,s.aRownr

if OBJECT_ID(''tempdb..#TmpNum'') is not null drop table #TmpNum 
--修改合并标志
  SELECT VAF59,COUNT(*) AS lnum
    INTO #TmpNum
  FROM #tmpvaf
  WHERE BDA01 >= ''1'' and BDA01 <= ''2''
  GROUP BY VAF59
  
  UPDATE a
  SET a.lUnite=1
  FROM #tmpvaf a
  JOIN #TmpNum b ON a.VAF59=b.VAF59
  WHERE b.lnum>1 and BDA01 >= ''1'' and BDA01 <= ''2'' 
  select s.* from #tmpvaf s order by s.rnbig,s.rnbii,s.VAF59,s.aRownr
if OBJECT_ID(''tempdb..#TmpNum'') is not null drop table #TmpNum 
if OBJECT_ID(''tempdb..#kbmVCR'') is not null drop table #kbmVCR 
if OBJECT_ID(''tempdb..#tmpvaf'') is not null drop table #tmpvaf ', '2016-07-21 11:42:18', '(8058)高瑜', 0)
else
print 'SYS_Scripts.id=7718 已经存在.'

GO