--增加分类
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=7729 --删除脚本内容
GO


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=7729)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(7729, 33, 'Sql_临床路径医嘱编辑', '治疗方案临床路径', '临床路径医嘱项目维护时，修改医嘱项目用', 1, 1, Null, 1, 'DECLARE @bii01 int,@ACF01 int,@Check int
DECLARE @aDate DateTime
SET @bii01 = %d
SET @ACF01 = %d
SET @Check = %d
SET @aDate = Convert(varchar(10),GetDate(),21)+'' 23:59:59.999''
select FCheck = @Check,a.BIJ01 BEF01, a1.BII01 BBX01A, a.ROWNR, a.BIJ01A BBX01B, NBEF05=CASE a.BIJ04 WHEN 1 THEN ''长'' ELSE ''临'' end
  , a.BBX01 BBX01C, a.BIJ07 BEF07, 1 BEF08, a.BIJ08 BEF09,cast(a.BIJ08 as varchar) BEF10, a.BIJ09 BEF11, a.BBY01
  , a.BIJ10 BEF13, a.BIJ11 BEF14, a.BIJ12 BEF15, a.BIJ13 BEF16, a.BIJ14 BEF17, a.BIJ15 BEF18, a.BIJ16 BEF19
  , NBEF20=CASE a.BIJ17 WHEN 1 THEN ''自备药'' WHEN 2 THEN ''离院带药'' else ''正常'' end
  , a.BCK01, a.BIJ19 BEF22,b.BIJ07 as SubBEF07,c.BCK03
  ,case when @ACF01 = 1 then f.BDG02A when @ACF01 = 2 then f.BDG02B else e.BBY08 end as BDG02 ,d.BDA01
  , case when isnull(a.BIJ09,0)>0 and @ACF01 = 1 and a.BIJ04=2 then cast(cast(a.BIJ09 as int) AS varchar(10))+f.BDG02A
         when isnull(a.BIJ09,0)>0 and @ACF01 = 2 and a.BIJ04=2 then cast(cast(a.BIJ09 as int) AS varchar(10))+f.BDG02B
         when isnull(a.BIJ09,0)>0 and @ACF01 = 0 and a.BIJ04=2 then cast(cast(a.BIJ09 as int) AS varchar(10))+e.BBY08
         else '''' end as FUnit  
  , a.BIJ04 BEF05, d.BBX05, a.BIJ17 BEF20,b.BBX01 as BBX01F,d.BBX17,d.BBX15,d.BBX11,a.BIJ22 BEF01A
  ,NBEF05A = CONVERT(varchar(10),a.BIJ22),NBEF07  = (a.BIJ07+'',每次''+isnull(cast(a.BIJ08 as varchar),''''))
  ,CASE WHEN a.BIJ04=2 THEN (b.BIJ07+''，''+a.BIJ13+'' /天'')
        ELSE (b.BIJ07+'',''+a.BIJ13) END AS FUsage
  ,FUsageN=CONVERT(varchar(10),a.BIJ22)
  ,NBEF22 = CONVERT(varchar(10),a.BIJ22),ISNULL(F.BAG27,0) AS BAG27
  ,a.BIJ23 BEF23,a.BIJ14 BEF24,f.BAG03
from BIJ1 a
join BIK1 a1 on a1.BIJ01 = a.BIJ01
join BBX1 d on d.BBX01 = a.BBX01
join BBY1 e on e.BBY01 = a.BBY01
join BAG1 f on e.BBY01 = f.BBY01 
join BIJ1 b on b.BIJ01 = a.BIJ01A
left join BCK1 c on a.BCK01=c.BCK01  
where a1.BII01 = @bii01 and a.BIJ01A > 0 and d.BDA01 >= ''1'' and d.BDA01 <= ''2'' and e.BBY31 > @aDate
union all 
select FCheck = @Check,a.BIJ01 BEF01, a1.BII01 BBX01A, a.ROWNR, a.BIJ01A BBX01B, NBEF05=CASE a.BIJ04 WHEN 1 THEN ''长'' ELSE ''临'' end
   , a.BBX01 BBX01C, a.BIJ07, 1 BEF08, a.BIJ08 BEF09, cast(a.BIJ08 as varchar) BEF10, a.BIJ09 BEF11, a.BBY01
  , a.BIJ10 BEF13, a.BIJ11 BEF14, a.BIJ12 BEF15, a.BIJ13 BEF16, a.BIJ14 BEF17, a.BIJ15 BEF18, a.BIJ16 BEF19, NBEF20=''正常''
   , a.BCK01, a.BIJ19 BEF22,'''' as SubBEF07,c.BCK03,d.BDG02,d.BDA01
   , case when isnull(a.BIJ09,0)>0 and a.BIJ04=2 then cast(cast(a.BIJ09 as int) AS varchar(10))+isnull(d.BDG02,'''') ELSE '''' END AS FUnit
   , a.BIJ04 BEF05, d.BBX05, a.BIJ17 BEF20,0,d.BBX17,d.BBX15,d.BBX11 ,a.BIJ22 BEF01A
   ,NBEF05A = CONVERT(varchar(10),a.BIJ22),NBEF07  = a.BIJ07
  ,FUsage=null, FUsageN=CONVERT(varchar(10),a.BIJ22)
  ,NBEF22 = CONVERT(varchar(10),a.BIJ22),0
  ,a.BIJ23 BEF23,a.BIJ14 BEF24,1 BAG03
from BIJ1 a
join BIK1 a1 on a1.BIJ01 = a.BIJ01
join BBX1 d on d.BBX01 = a.BBX01
left join BCK1 c on a.BCK01=c.BCK01   
where a1.BII01 = @bii01 and a.BIJ01A = 0 AND (d.BDA01=''S'' or d.BDA01 = ''E'') and d.BBX25 > @aDate
union all
select FCheck = @Check,a.BIJ01, a1.BII01 BBX01A, a.ROWNR, a.BIJ01A BBX01B, NBEF05=CASE a.BIJ04 WHEN 1 THEN ''长'' ELSE ''临'' end
   , a.BBX01 BBX01C, a.BIJ07, 1 BEF08, a.BIJ08 BEF09, cast(a.BIJ08 as varchar) BEF10, a.BIJ09 BEF11, a.BBY01
  , a.BIJ10 BEF13, a.BIJ11 BEF14, a.BIJ12 BEF15, a.BIJ13 BEF16, a.BIJ14 BEF17, a.BIJ15 BEF18, a.BIJ16 BEF19
  , NBEF20=CASE a.BIJ17 WHEN 1 THEN ''自备药'' WHEN 2 THEN ''离院带药'' else ''正常'' end
   , a.BCK01, a.BIJ19 BEF22,d.BBX05 as SubBEF07,c.BCK03,''付''as BDG02,''8'' as BDA01
   , CASE WHEN isnull(a.BIJ09,0)>0 AND a.BIJ04=2 THEN cast(cast(a.BIJ09 as int) AS varchar(10))+''付'' ELSE '''' END AS FUnit
   , a.BIJ04 BEF05, d.BBX05, a.BIJ17 BEF20,0,d.BBX17,d.BBX15,d.BBX11 ,a.BIJ22 BEF01A
   ,NBEF05A = CONVERT(varchar(10),a.BIJ22),NBEF07  = a.BIJ07
  ,FUsage=null, FUsageN=CONVERT(varchar(10),a.BIJ22)
  ,NBEF22 = CONVERT(varchar(10),a.BIJ22),0
  ,a.BIJ23 BEF23,a.BIJ14 BEF24,1 BAG03
from BIJ1 a
join BIK1 a1 on a1.BIJ01 = a.BIJ01
join BBX1 d on a.BBX01=d.BBX01  
left join BCK1 c on a.BCK01=c.BCK01   
where a1.BII01 = @bii01 and a.BIJ01A = 0  AND d.BDA01=''T'' and d.BBX25 > @aDate
  and exists(select b.* from BIJ1 b JOIN BBX1 b2 ON b.BBX01 = b2.BBX01 AND b2.BDA01=''3'' where b.BIJ01A = a.BIJ01)
union all
select FCheck = @Check,a.BIJ01, a1.BII01 BBX01A, a.ROWNR, a.BIJ01A BBX01B, NBEF05=CASE a.BIJ04 WHEN 1 THEN ''长'' ELSE ''临'' end
   , a.BBX01 BBX01C, a.BIJ07, 1 BEF08, a.BIJ08 BEF09, cast(a.BIJ08 as varchar) BEF10, a.BIJ09 BEF11, a.BBY01
  , a.BIJ10 BEF13, a.BIJ11 BEF14, a.BIJ12 BEF15, a.BIJ13 BEF16, a.BIJ14 BEF17, a.BIJ15 BEF18, a.BIJ16 BEF19, NBEF20=''正常''
   , a.BCK01, a.BIJ19 BEF22,'''' as SubBEF07,c.BCK03,d.BDG02,d.BDA01
   , case when isnull(a.BIJ09,0)>0 and a.BIJ04=2 then cast(cast(a.BIJ09 as int) AS varchar(10))+isnull(d.BDG02,'''') ELSE '''' END AS FUnit
   , a.BIJ04 BEF05, d.BBX05, a.BIJ17 BEF20,0,d.BBX17,d.BBX15,d.BBX11,a.BIJ22 BEF01A
   ,NBEF05A = CONVERT(varchar(10),a.BIJ22),NBEF07  = a.BIJ07
  ,FUsage=null, FUsageN=CONVERT(varchar(10),a.BIJ22)
  ,NBEF22 = CONVERT(varchar(10),a.BIJ22),0
  ,a.BIJ23 BEF23,a.BIJ14 BEF24,1 BAG03
from BIJ1 a
join BIK1 a1 on a1.BIJ01 = a.BIJ01
join BBX1 d on a.BBX01=d.BBX01
left join BCK1 c on a.BCK01=c.BCK01  
where a1.BII01 = @bii01 and a.BIJ01A = 0 AND d.BDA01 NOT IN (''1'',''2'',''3'',''8'',''S'',''E'',''4'',''M'')
   and d.BBX25 > @aDate and not exists(select b.* from BIJ1 b where b.BIJ01A = a.BIJ01)
union all
select FCheck = @Check,a.BIJ01, a1.BII01 BBX01A, a.ROWNR, a.BIJ01A BBX01B, NBEF05=CASE a.BIJ04 WHEN 1 THEN ''长'' ELSE ''临'' end
   , a.BBX01 BBX01C, a.BIJ07, 1 BEF08, a.BIJ08 BEF09, cast(a.BIJ08 as varchar) BEF10, a.BIJ09 BEF11, a.BBY01
  , a.BIJ10 BEF13, a.BIJ11 BEF14, a.BIJ12 BEF15, a.BIJ13 BEF16, a.BIJ14 BEF17, a.BIJ15 BEF18, a.BIJ16 BEF19
  , NBEF20=''正常'' , a.BCK01, a.BIJ19 BEF22,'''' as SubBEF07,c.BCK03,d.BBY08,d.BDN01
   , case when isnull(a.BIJ09,0)>0 and a.BIJ04=2 then cast(cast(a.BIJ09 as int) AS varchar(10))+isnull(d.BBY08,'''') ELSE '''' END AS FUnit
   , a.BIJ04 BEF05, d.BBY05, a.BIJ17 BEF20,0,1 BBX17,1 BBX15,1 BBX11,a.BIJ22 BEF01A
   ,NBEF05A = CONVERT(varchar(10),a.BIJ22),NBEF07  = a.BIJ07
  ,FUsage=null, FUsageN=CONVERT(varchar(10),a.BIJ22)
  ,NBEF22 = CONVERT(varchar(10),a.BIJ22),0
  ,a.BIJ23 BEF23,a.BIJ14 BEF24,1 BAG03
from BIJ1 a
join BIK1 a1 on a1.BIJ01 = a.BIJ01
join BBY1 d on d.BBY01 = a.BBY01
left join BCK1 c on a.BCK01=c.BCK01  
where a1.BII01 = @bii01 and a.BIJ01A = 0 AND d.BDN01 IN (''4'',''M'') and d.BBY31 > @aDate
  and not exists(select b.* from BIJ1 b where b.BIJ01A = a.BIJ01)  
union all
select FCheck = @Check,a.BIJ01, a1.BII01 BBX01A, a.ROWNR, a.BIJ01A BBX01B, NBEF05=CASE a.BIJ04 WHEN 1 THEN ''长'' ELSE ''临'' end
  , a.BBX01 BBX01C, a.BIJ07, 1 BEF08, a.BIJ08 BEF09, cast(a.BIJ08 as varchar) BEF10, a.BIJ09 BEF11, a.BBY01
  , a.BIJ10 BEF13, a.BIJ11 BEF14, a.BIJ12 BEF15, a.BIJ13 BEF16, a.BIJ14 BEF17, a.BIJ15 BEF18, a.BIJ16 BEF19, NBEF20=''正常''
   , a.BCK01, a.BIJ19 BEF22,'''' as SubBEF07,c.BCK03,'''',''0'' BDA01
   , cast(a.BIJ09 as varchar) AS FUnit
   , a.BIJ04 BEF05, '''' BBX05, a.BIJ17 BEF20,0,1 BBX17,1 BBX15,1 BBX11 ,a.BIJ22 BEF01A
   ,NBEF05A = CONVERT(varchar(10),a.BIJ22),NBEF07  = a.BIJ07
  ,FUsage=null, FUsageN=CONVERT(varchar(10),a.BIJ22)
  ,NBEF22 = CONVERT(varchar(10),a.BIJ22),0
  ,a.BIJ23 BEF23,a.BIJ14 BEF24,1 BAG03
from BIJ1 a
join BIK1 a1 on a1.BIJ01 = a.BIJ01
left join BCK1 c on c.BCK01 = a.BCK01
where a1.BII01 = @bii01 and a.BBX01 = 0
ORDER BY a.BIJ22,a.ROWNR  ', '2016-07-22 14:53:03', '(8058)高瑜', 0)
else
print 'SYS_Scripts.id=7729 已经存在.'

GO