--增加分类
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


If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 33)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=33
GO


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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=7692)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode,Custom)  Values(7692, 33, 'Sql_瓶签条码分组次数', '', '瓶签条码分组次数', 1, 1, Null, 1, 'DECLARE @lVAF11 int, @lDt1 varchar(30),@lDt2 varchar(30)
Declare @lBCK01 int, @rPrint tinyint, @lStop tinyint
Declare @lVBI29A tinyint,@lVBI29B tinyint ,@aVAF07 int,@Param208 int
declare @lVAE01s varchar(1024),@lVAP01s varchar(1024)
Set @lBCK01 = %d
SET @lVAF11 = %d
Set @lDt1 = Convert(varchar(16),Cast(%s as datetime),121)+'':00''
Set @lDt2 = Convert(varchar(16),Cast(%s as datetime),121)+'':59''
Set @rPrint = %d
Set @lStop = %d
Set @aVAF07 = %d
set @lVAE01s =''%s''
set @lVAP01s = ''%s''

Set @lVBI29A = 0
Set @lVBI29B = 0

if @rPrint = 0
begin
  Set @lVBI29A = 0
  Set @lVBI29B = 1
end
if @rPrint = 1
begin
  Set @lVBI29A = 2
  Set @lVBI29B = 3
end

declare @kbVAE01 table(VAE01 int)
insert into @kbVAE01(VAE01)
select * from dbo.Split(@lVAE01s,'','') 

declare @kbVAP01 table(VAP01 int)
insert into @kbVAP01(VAP01)
select * from dbo.Split(@lVAP01s,'','') 

select @Param208 = isnull(BLP05,0) from BLP1 where BLP02 = 208
set @Param208= isnull(@Param208,0)
  IF Object_id(''tempdb..#tmpVAF7692'') IS NOT NULL DROP TABLE #tmpVAF7692
   select * into #tmpVAF7691 from VAF2 a with(nolock) where exists(select * from @kbVAE01 where VAE01=a.VAF06)

select distinct d.床号,d.就诊id,d.婴儿id,d.婴儿姓名,d.单号,d.次数,d.组号,d.用法,d.用法说明
    ,d.频次,d.滴速,d.执行日期,d.条码,d.VAF11,d.医嘱类型
from(
  SELECT a.VAF22 医嘱,a3.BBY05 药品名称,a3.BBY06 规格, a.VAF19 剂量, a2.BBX05 用法, a.VAF26 频次
    ,a1.VAF23 用法说明, a.VAF59 组号, a.CBM01 单号,s.VCM12 次数,s.VBI21 条码
    , a.Rownr 序号,a.VAF06 就诊id,a.VAF07 婴儿id,a.VAF23 医嘱嘱托 
    , CASE a.VAF11 WHEN 1 THEN ''长嘱'' WHEN 2 THEN ''临嘱'' end as 医嘱类型,a.VAF60 滴速 
    ,''瓶签'' as 执行单类型,a.VAF27 总次数,Isnull(c.VAP05,'''') 婴儿姓名,c1.BCQ04B 床号
    ,s.VCM10 执行日期,a.VAF36 开始时间,a.VAF58 给药性质,a.VAF35 紧急标志,a.VAF11
    ,b.VBI01
  FROM #tmpVAF7692 a
  JOIN #tmpVAF7692 a1 ON a.VAF01A = a1.VAF01
  JOIN BBX1 a2 ON a1.BBX01 = a2.BBX01
  join BBY1 a3 on a.BBY01 = a3.BBY01
  JOIN VBI2 b ON a.VAF01 = b.VAF01
  JOin VAE1 c1 on a.VAF06 = c1.VAE01
  join VCM1 s on s.VBI01 = b.VBI01  
  Join BCV1 d on a.BCK01A = d.BCK01B
  Left Join VAP1 c ON a.VAF07 = c.VAP01
  WHERE isnull(a.VAF07,0)  = 0 and c1.BCK01C = @lBCK01 and d.BCK01A = @lBCK01 and a.VAF04 = 2  and ((@lVAF11 = 3)or(@lVAF11 <> 3 and a.VAF11 = @lVAF11))
    AND a.VAF06 IN (select * from @kbVAE01) and Exists(Select s.* from BCV1 s where s.BCK01A = @lBCK01 and (a.BCK01D = s.BCK01A or a.BCK01D = s.BCK01B)) 
    AND a2.BBX20 =2 and (isnull(b.VBI29,0) = @lVBI29A or isnull(b.VBI29,0) = @lVBI29B) and a.VAF54 = 0
    and ((a.VAF11 = 2 And a.VAF10 = 8) or (a.VAF11 = 1 and ((@lStop = 0 and a.VAF10 = 3)or (@lStop = 1 and a.VAF10 >= 8))))
    AND (a.BDA01 = ''1'' or a.BDA01 = ''2'') AND a2.BDA01 = ''T'' AND a2.BBX13 = ''2''
    and datediff(MINUTE,@lDt1,b.VBI10)>=0 and datediff(MINUTE,b.VBI10,@lDt2)>=0) d
union all
select distinct d.床号,d.就诊id,d.婴儿id,d.婴儿姓名,d.单号,d.次数,d.组号,d.用法,d.用法说明
    ,d.频次,d.滴速,d.执行日期,d.条码,d.VAF11,d.医嘱类型
from(
  SELECT a.VAF22 医嘱,a3.BBY05 药品名称,a3.BBY06 规格, a.VAF19 剂量, a2.BBX05 用法, a.VAF26 频次
    ,a1.VAF23 用法说明, a.VAF59 组号, a.CBM01 单号,s.VCM12 次数,s.VBI21 条码
    , a.Rownr 序号,a.VAF06 就诊id,a.VAF07 婴儿id,a.VAF23 医嘱嘱托 
    , CASE a.VAF11 WHEN 1 THEN ''长嘱'' WHEN 2 THEN ''临嘱'' end as 医嘱类型,a.VAF60 滴速 
    ,''瓶签'' as 执行单类型,a.VAF27 总次数,Isnull(c.VAP05,'''') 婴儿姓名,c1.BCQ04B 床号
    ,s.VCM10 执行日期,a.VAF36 开始时间,a.VAF58 给药性质,a.VAF35 紧急标志,a.VAF11
    ,b.VBI01
  FROM #tmpVAF7692 a
  JOIN #tmpVAF7692 a1 ON a.VAF01A = a1.VAF01
  JOIN BBX1 a2 ON a1.BBX01 = a2.BBX01
  join BBY1 a3 on a.BBY01 = a3.BBY01
  JOIN VBI2 b ON a.VAF01 = b.VAF01
  JOin VAE1 c1 on a.VAF06 = c1.VAE01
  join VCM1 s on s.VBI01 = b.VBI01  
  Join BCV1 d on a.BCK01A = d.BCK01B
  Left Join VAP1 c ON a.VAF07 = c.VAP01
  WHERE @Param208 = 1 and c1.BCK01C = @lBCK01 and d.BCK01A = @lBCK01 and a.VAF04 = 2  and ((@lVAF11 = 3)or(@lVAF11 <> 3 and a.VAF11 = @lVAF11))
    AND a.VAF07 IN (select * from @kbVAP01) and Exists(Select s.* from BCV1 s where s.BCK01A = @lBCK01 and (a.BCK01D = s.BCK01A or a.BCK01D = s.BCK01B)) 
    AND a2.BBX20 =2 and (isnull(b.VBI29,0) = @lVBI29A or isnull(b.VBI29,0) = @lVBI29B) and a.VAF54 = 0
    and ((a.VAF11 = 2 And a.VAF10 = 8) or (a.VAF11 = 1 and ((@lStop = 0 and a.VAF10 = 3)or (@lStop = 1 and a.VAF10 >= 8))))
    AND (a.BDA01 = ''1'' or a.BDA01 = ''2'') AND a2.BDA01 = ''T'' AND a2.BBX13 = ''2''
    and datediff(MINUTE,@lDt1,b.VBI10)>=0 and datediff(MINUTE,b.VBI10,@lDt2)>=0) d    
  order by d.VAF11,d.就诊id,d.婴儿id,d.条码,d.单号,d.组号,d.次数,d.执行日期
  IF Object_id(''tempdb..#tmpVAF7692'') IS NOT NULL DROP TABLE #tmpVAF7692', '2019-07-18 09:11:19', '(8088)王海涛', 0, 0)
else
print 'SYS_Scripts.id=7692 已经存在.'

GO


If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 7692)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=7692
GO


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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=7691)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode,Custom)  Values(7691, 33, 'Sql_瓶签执行单条码', '', '包含条码的瓶签执行单', 1, 1, Null, 1, 'DECLARE @lVAF11 int, @lDt1 varchar(30),@lDt2 varchar(30)
Declare @lBCK01 int, @rPrint tinyint, @lStop tinyint
Declare @lVBI29A tinyint,@lVBI29B tinyint ,@aVAF07 int,@Param208 int
declare @lVAE01s varchar(1024),@lVAP01s varchar(1024)
Set @lBCK01 = %d
SET @lVAF11 = %d
Set @lDt1 = Convert(varchar(16),Cast(%s as datetime),121)+'':00''
Set @lDt2 = Convert(varchar(16),Cast(%s as datetime),121)+'':59''
Set @rPrint = %d
Set @lStop = %d
Set @aVAF07 = %d
set @lVAE01s =''%s''
set @lVAP01s = ''%s''

declare @kbVAE01 table(VAE01 int)
insert into @kbVAE01(VAE01)
select * from dbo.Split(@lVAE01s,'','') 

declare @kbVAP01 table(VAP01 int)
insert into @kbVAP01(VAP01)
select * from dbo.Split(@lVAP01s,'','') 

Set @lVBI29A = 0
Set @lVBI29B = 0

if @rPrint = 0
begin
  Set @lVBI29A = 0
  Set @lVBI29B = 1
end
if @rPrint = 1
begin
  Set @lVBI29A = 2
  Set @lVBI29B = 3
end
  select @Param208 = isnull(BLP05,0) from BLP1 where BLP02 = 208 
  set @Param208= isnull(@Param208,0)
  IF Object_id(''tempdb..#tmpVAF7691'') IS NOT NULL DROP TABLE #tmpVAF7691
   select * into #tmpVAF7691 from VAF2 a with(nolock) where exists(select * from @kbVAE01 where VAE01=a.VAF06)
  SELECT a.VAF22 医嘱,a3.BBY05 药品名称,a3.BBY06 规格, a.VAF19 剂量, a2.BBX05 用法, a.VAF26 频次
    ,a1.VAF23 用法说明, a.VAF59 组号, a.CBM01 单号,s.VCM12 次数,s.VBI21 条码
    , a.Rownr 序号,a.VAF06 就诊id,a.VAF07 婴儿id,a.VAF23 医嘱嘱托 
    , CASE a.VAF11 WHEN 1 THEN ''长嘱'' WHEN 2 THEN ''临嘱'' end as 医嘱类型,a.VAF60 滴速 
    ,''瓶签'' as 执行单类型,a.VAF27 总次数,Isnull(c.VAP05,'''') 婴儿姓名,c1.BCQ04B 床号
    ,s.VCM10 执行日期,a.VAF36 开始时间,a.VAF58 给药性质,a.VAF35 紧急标志
    ,b.VBI01,a.VAF11,a.crypt,a.VAF22,a.BDA01
  FROM #tmpVAF7691 a
  JOIN #tmpVAF7691 a1 ON a.VAF01A = a1.VAF01
  JOIN BBX1 a2 ON a1.BBX01 = a2.BBX01
  join BBY1 a3 on a.BBY01 = a3.BBY01
  JOIN VBI2 b ON a.VAF01 = b.VAF01
  JOin VAE1 c1 on a.VAF06 = c1.VAE01
  join VCM1 s on s.VBI01 = b.VBI01  
  Join BCV1 d on a.BCK01A = d.BCK01B
  Left Join VAP1 c ON a.VAF07 = c.VAP01
  WHERE c1.BCK01C = @lBCK01 and d.BCK01A = @lBCK01 and a.VAF04 = 2  and ((@lVAF11 = 3)or(@lVAF11 <> 3 and a.VAF11 = @lVAF11))
    AND a.VAF06 IN (select * from @kbVAE01) and Exists(Select s.* from BCV1 s where s.BCK01A = @lBCK01 and (a.BCK01D = s.BCK01A or a.BCK01D = s.BCK01B)) 
    AND a2.BBX20 =2 and (isnull(b.VBI29,0) = @lVBI29A or isnull(b.VBI29,0) = @lVBI29B) and a.VAF54 = 0
    and ((a.VAF11 = 2 And a.VAF10 = 8) or (a.VAF11 = 1 and ((@lStop = 0 and a.VAF10 = 3)or (@lStop = 1 and a.VAF10 >= 8))))
    AND (a.BDA01 = ''1'' or a.BDA01 = ''2'') AND a2.BDA01 = ''T'' AND a2.BBX13 = ''2''
    and datediff(MINUTE,@lDt1,b.VBI10)>=0 and datediff(MINUTE,b.VBI10,@lDt2)>=0
    and (@Param208 = 0 or isnull(a.VAF07,0)=0)
  union all
  SELECT a.VAF22 医嘱,a3.BBY05 药品名称,a3.BBY06 规格, a.VAF19 剂量, a2.BBX05 用法, a.VAF26 频次
    ,a1.VAF23 用法说明, a.VAF59 组号, a.CBM01 单号,s.VCM12 次数,s.VBI21 条码
    , a.Rownr 序号,a.VAF06 就诊id,a.VAF07 婴儿id,a.VAF23 医嘱嘱托 
    , CASE a.VAF11 WHEN 1 THEN ''长嘱'' WHEN 2 THEN ''临嘱'' end as 医嘱类型,a.VAF60 滴速 
    ,''瓶签'' as 执行单类型,a.VAF27 总次数,Isnull(c.VAP05,'''') 婴儿姓名,c1.BCQ04B 床号
    ,s.VCM10 执行日期,a.VAF36 开始时间,a.VAF58 给药性质,a.VAF35 紧急标志
    ,b.VBI01,a.VAF11,a.crypt,a.VAF22,a.BDA01
  FROM #tmpVAF7691 a
  JOIN #tmpVAF7691 a1 ON a.VAF01A = a1.VAF01
  JOIN BBX1 a2 ON a1.BBX01 = a2.BBX01
  join BBY1 a3 on a.BBY01 = a3.BBY01
  JOIN VBI2 b ON a.VAF01 = b.VAF01
  JOin VAE1 c1 on a.VAF06 = c1.VAE01
  join VCM1 s on s.VBI01 = b.VBI01  
  Join BCV1 d on a.BCK01A = d.BCK01B
  Left Join VAP1 c ON a.VAF07 = c.VAP01
  WHERE @Param208 = 1 and c1.BCK01C = @lBCK01 and d.BCK01A = @lBCK01 and a.VAF04 = 2  and ((@lVAF11 = 3)or(@lVAF11 <> 3 and a.VAF11 = @lVAF11))
    AND a.VAF07 IN (select * from @kbVAP01) and Exists(Select s.* from BCV1 s where s.BCK01A = @lBCK01 and (a.BCK01D = s.BCK01A or a.BCK01D = s.BCK01B)) 
    AND a2.BBX20 =2 and (isnull(b.VBI29,0) = @lVBI29A or isnull(b.VBI29,0) = @lVBI29B) and a.VAF54 = 0
    and ((a.VAF11 = 2 And a.VAF10 = 8) or (a.VAF11 = 1 and ((@lStop = 0 and a.VAF10 = 3)or (@lStop = 1 and a.VAF10 >= 8))))
    AND (a.BDA01 = ''1'' or a.BDA01 = ''2'') AND a2.BDA01 = ''T'' AND a2.BBX13 = ''2''
    and datediff(MINUTE,@lDt1,b.VBI10)>=0 and datediff(MINUTE,b.VBI10,@lDt2)>=0      
  order by a.VAF11,a.VAF06,a.VAF07,s.VBI21,a.VAF36,a.CBM01,a.VAF59,a.ROWNR,s.VCM12,s.VCM10  
    IF Object_id(''tempdb..#tmpVAF7691'') IS NOT NULL DROP TABLE #tmpVAF7691', '2019-07-18 09:08:18', '(8088)王海涛', 0, 0)
else
print 'SYS_Scripts.id=7691 已经存在.'

GO


If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 7691)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=7691
GO


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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=2002)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode,Custom)  Values(2002, 33, 'Sql_病区执行单明细', '执行单', '各种执行单明细', 1, 0, Null, 1, 'DECLARE @lVAF11 int, @lBCK01 int, @lType int, @rPrint tinyint
Declare @lVBI29A tinyint,@lVBI29B tinyint, @lStop tinyint
DECLARE @lDt1 varchar(30),@lDt2 varchar(30), @aVAF07 int,@p262 varchar(10),@Param208 int
declare @lVAE01s varchar(1024),@lVAP01s varchar(1024),@lBBX20s varchar(64)
SET @lBCK01 = %d
SET @lVAF11 = %d
Set @lDt1 = Convert(varchar(16),Cast(%s as datetime),121)+'':00''
Set @lDt2 = Convert(varchar(16),Cast(%s as datetime),121)+'':59''
Set @lType = %d
Set @rPrint = %d
Set @lStop = %d
Set @aVAF07 = %d
set @p262 = dbo.GetSysParamValue(100,105003,262)
Set @lVBI29A = 0
Set @lVBI29B = 0
set @lVAE01s =''%s''
set @lVAP01s = ''%s''
set @lBBX20s = ''%s''

declare @kbVAE01 table(VAE01 int)
insert into @kbVAE01(VAE01)
select * from dbo.Split(@lVAE01s,'','') 

declare @kbVAP01 table(VAP01 int)
insert into @kbVAP01(VAP01)
select * from dbo.Split(@lVAP01s,'','') 

declare @kbBBX20 table(BBX20 int)
insert into @kbBBX20(BBX20)
select * from dbo.Split(@lBBX20s,'','') 

if (@lType = 0 and @rPrint = 0)
begin
  Set @lVBI29A = 0
  Set @lVBI29B = 2
end
if (@lType = 0 and @rPrint = 1)
begin
  Set @lVBI29A = 1
  Set @lVBI29B = 3
end
if (@lType = 1 and @rPrint = 0)
begin
  Set @lVBI29A = 0
  Set @lVBI29B = 1
end
if (@lType = 1 and @rPrint = 1)
begin
  Set @lVBI29A = 2
  Set @lVBI29B = 3
end
if (@lType > 1 and @rPrint = 1)
begin
  Set @lVBI29A = 1
  Set @lVBI29B = 1
end

IF Object_id(''tempdb..#TmpVbi'') IS NOT NULL DROP TABLE #TmpVbi
IF Object_id(''tempdb..#tmpview'') IS NOT NULL DROP TABLE #tmpview
IF Object_id(''tempdb..#TmpVaf2002'') IS NOT NULL DROP TABLE #TmpVaf2002
set @Param208 = isnull((select BLP05 from BLP1 where BLP02 = 208),0)
select * into #TmpVaf2002 from VAF2 a with(nolock) where exists(select * from @kbVAE01 where VAE01 = a.VAF06 ) 
SELECT c.VAE01 就诊id,c.VAE94 住院号, case when a.VAF07 > 0 and @Param208 = 1 then c5.VAP05 else c.VAE95 end  姓名
  ,Case c.VAE96 When ''1'' then ''男'' When ''2'' then ''女'' Else ''未知'' End As 性别
  , CAST(c.VAE46 as varchar(10))+isnull(c3.AAU02,'''') as 年龄 ,a4.BCK03 科室,c.BCQ04B 床位,a.VAF22 医嘱名称
  ,case when a.BDA01 = ''3'' then a1.VAF23 else a.VAF23 end 医嘱嘱托 ,a.VAF25 皮试结果 ,a3.BBY05 药品名称,a3.BBY06 规格
  , a.VAF19 剂量, a2.BBX05 途径,a1.VAF23 用法说明, a.VAF26 频次, a.CBM01 单号, a.VAF59 组号, a.Rownr 成员序号
  ,b.VBI10 执行日期,a.VAF27 频率次数,a.VAF60 滴速,a.VAF61 首次,a.VAF62 末次 
  , CASE a.VAF11 WHEN 1 THEN ''长嘱'' WHEN 2 THEN ''临嘱'' end as 医嘱期效
  ,CASE WHEN a2.BBX20 = 0 THEN ''口服单'' WHEN a2.BBX20 = 1 THEN ''注射单''
        when (a2.BBX20 = 2) or (a2.BBX20 = 4) then ''输液单''
        When a2.BBX20 = 3 then ''治疗单'' When a2.BBX20 = 5 Then ''输血单''
        When a2.BBX20 = 6 Then ''护理单'' end as 执行单类型
  ,d.BAG03 含量,d1.BDG02 含量单位,d.BDG02B 住院单位,d.BAG07 住院包装,d.BDG02C 价格单位 
  ,d.BAG09 价格包装,a.VAF21 总量 
  ,a.VAF58 给药性质,a.VAF35 紧急标志,a.VAF07 婴儿id,c5.VAP05 婴儿姓名,c5.VAP07 婴儿出生日期
  ,a.VAF36 开始时间,a.BDA01 医嘱类型,a2.BBX20,a.VAF11,b.VBI01,c.BCK01C,a.VAF42 开嘱时间,a.VAF30 执行方案,a.VAF54 屏蔽打印
  ,d.BLW01 高危药品,a.crypt,a.VAF22,a.BDA01 
Into #TmpVbi
FROM #TmpVaf2002 a
  JOIN #TmpVaf2002 a1 ON a.VAF01A = a1.VAF01
  JOIN BBX1 a2 ON a1.BBX01 = a2.BBX01
  join BBY1 a3 on a.BBY01 = a3.BBY01
  join BAG1 d on a3.BBY01 = d.BBY01
  join BBX1 d1 on d.BBX01 = d1.BBX01
  JOIN VBI2 b ON a.VAF01 = b.VAF01
  JOIN VAE1 c ON a.VAF06 = c.VAE01
  Join BCK1 a4 on a.BCK01A = a4.BCK01
  Join BCV1 a5 on a.BCK01A = a5.BCK01B  
  Left Join VAP1 c5 ON a.VAF07 = c5.VAP01
  LEFT JOIN AAU1 c3 ON c.AAU01 = c3.AAU01
WHERE c.BCK01C = @lBCK01 and a5.BCK01A = @lBCK01 AND a.VAF04 = 2 and b.VBI07 > 0
   and ((@lVAF11 = 3)or(@lVAF11 <> 3 and a.VAF11 = @lVAF11)) 
  and Exists(Select s.* from BCV1 s where s.BCK01A = @lBCK01 and (a.BCK01D = s.BCK01A or a.BCK01D = s.BCK01B))
  and (isnull(b.VBI29,0) = @lVBI29A or isnull(b.VBI29,0) = @lVBI29B)
  and ((a.VAF11 = 2 And a.VAF10 >= 8) or (a.VAF11 = 1 and ((@lStop = 0 and a.VAF10 = 3)or (@lStop = 1 and a.VAF10 >= 8))))
  AND (a.BDA01 >= ''1'' And a.BDA01 <= ''3'') AND a2.BDA01 = ''T'' AND a2.BBX13 in (''2'',''4'')
  and datediff(MINUTE,@lDt1,b.VBI10)>=0 and datediff(MINUTE,b.VBI10,@lDt2)>=0
  AND (c.VAE01 IN (select * from @kbVAE01) or a.VAF07 in (select * from @kbVAP01)) 
  AND a2.BBX20 IN (select * from @kbBBX20)   
Union All
Select c.VAE01 就诊id,c.VAE94 住院号, case when a.VAF07 > 0 and @Param208 = 1 then c5.VAP05 else c.VAE95 end  姓名
  ,Case c.VAE96 When ''1'' then ''男'' When ''2'' then ''女'' Else ''未知'' End As 性别
  , CAST(c.VAE46 as varchar(10))+isnull(c3.AAU02,'''') as 年龄
  ,a4.BCK03 科室,c.BCQ04B 床位,a.VAF22 医嘱名称,a.VAF23 医嘱嘱托,a.VAF25 皮试结果,a1.BBX05 药品名称,'''' 规格
  ,'''' 剂量, '''' 途径,a.VAF23 用法说明, a.VAF26 频次, a.CBM01 单号, a.VAF59 组号, a.Rownr 成员序号
  ,b.VBI10 执行日期,a.VAF27 频率次数,''0'' 滴速,case when @p262=''1'' then a.VAF61 else a.VAF27 end 首次,0 末次 
  , CASE a.VAF11 WHEN 1 THEN ''长嘱'' WHEN 2 THEN ''临嘱'' end as 医嘱期效
  ,CASE WHEN a1.BBX20 = 0 THEN ''口服单'' WHEN a1.BBX20 = 1 THEN ''注射单''
        when (a1.BBX20 = 2) or (a1.BBX20 = 4) then ''输液单''
        When a1.BBX20 = 3 then ''治疗单'' When a1.BBX20 = 5 Then ''输血单''
        When a1.BBX20 = 6 Then ''护理单'' end as 执行单类型
  ,1 含量,'''' 含量单位,'''' 住院单位,1 住院包装,'''' 价格单位,1 价格包装,a.VAF21 总量 
  ,a.VAF58 给药性质,a.VAF35 紧急标志,a.VAF07 婴儿id,c5.VAP05 婴儿姓名,c5.VAP07 婴儿出生日期
  ,a.VAF36 开始时间,a.BDA01 医嘱类型,a1.BBX20,a.VAF11,b.VBI01,c.BCK01C,a.VAF42 开嘱时间,a.VAF30 执行方案,a.VAF54 屏蔽打印
  ,'''' 高危药品,a.crypt,a.VAF22,a.bda01
From #TmpVaf2002 a
  Join BBX1 a1 on a.BBX01 = a1.BBX01
  JOIN VBI2 b ON a.VAF01 = b.VAF01
  JOIN VAE1 c ON a.VAF06 = c.VAE01  
  Join BCK1 a4 on a.BCK01A = a4.BCK01
  Join BCV1 a5 on a.BCK01A = a5.BCK01B
  Left Join VAP1 c5 ON a.VAF07 = c5.VAP01
  LEFT JOIN AAU1 c3 ON c.AAU01 = c3.AAU01
where a.BDA01 in (''B'',''D'',''N'',''O'',''T'',''Z'') and a.VAF32 = 0 and a.VAF01A = 0 and ((@lVAF11 = 3)or(@lVAF11 <> 3 and a.VAF11 = @lVAF11))
  and a.VAF04 = 2 and c.BCK01C = @lBCK01 and a5.BCK01A = @lBCK01
  And Exists(Select s.* from BCV1 s where s.BCK01A = @lBCK01 ) 
  And (isnull(b.VBI29,0) = @lVBI29A or isnull(b.VBI29,0) = @lVBI29B)
  and ((a.VAF11 = 2 And a.VAF10 >= 8) or (a.VAF11 = 1 and ((@lStop = 0 and a.VAF10 = 3)or (@lStop = 1 and a.VAF10 >= 8))))
  and datediff(MINUTE,@lDt1,b.VBI10)>=0 and datediff(MINUTE,b.VBI10,@lDt2)>=0  
  AND (c.VAE01 IN (select * from @kbVAE01) or a.VAF07 in (select * from @kbVAP01)) 
  AND a1.BBX20 IN (select * from @kbBBX20)  
Union All
Select c.VAE01 就诊id,c.VAE94 住院号, case when a.VAF07 > 0 and @Param208 = 1 then c5.VAP05 else c.VAE95 end 姓名
  ,Case c.VAE96 When ''1'' then ''男'' When ''2'' then ''女'' Else ''未知'' End As 性别
  , CAST(c.VAE46 as varchar(10))+isnull(c3.AAU02,'''') as 年龄
  ,a4.BCK03 科室,c.BCQ04B 床位,a.VAF22 医嘱名称,a.VAF23 医嘱嘱托,a.VAF25 皮试结果,a.VAF22 药品名称,'''' 规格
  ,'''' 剂量, '''' 途径,a.VAF23 用法说明, a.VAF26 频次, a.CBM01 单号, a.VAF59 组号, a.Rownr 成员序号
  ,b.VBI10 执行日期,a.VAF27 频率次数,''0'' 滴速,a.VAF27 首次,0 末次 
  , CASE a.VAF11 WHEN 1 THEN ''长嘱'' WHEN 2 THEN ''临嘱'' end as 医嘱期效
  ,CASE WHEN a.VAF53 = 0 THEN ''口服单'' WHEN a.VAF53 = 1 THEN ''注射单''
        when (a.VAF53 = 2) or (a.VAF53 = 4) then ''输液单''
        When a.VAF53 = 3 then ''治疗单'' When a.VAF53 = 5 Then ''输血单''
        When a.VAF53 = 6 Then ''护理单'' end as 执行单类型
  ,1 含量,'''' 含量单位,'''' 住院单位,1 住院包装,'''' 价格单位,1 价格包装,a.VAF21 总量 
  ,0 给药性质,a.VAF35 紧急标志,a.VAF07 婴儿id,c5.VAP05 婴儿姓名,c5.VAP07 婴儿出生日期
  ,a.VAF36 开始时间,a.BDA01 医嘱类型,a.VAF53,a.VAF11,b.VBI01,c.BCK01C,a.VAF42 开嘱时间,a.VAF30 执行方案,a.VAF54 屏蔽打印
  ,'''' 高危药品,a.crypt,a.VAF22,a.BDA01
From #TmpVaf2002 a
  JOIN VBI2 b ON a.VAF01 = b.VAF01
  JOIN VAE1 c ON a.VAF06 = c.VAE01     
  Join BCK1 a4 on a.BCK01A = a4.BCK01
  Join BCV1 a5 on a.BCK01A = a5.BCK01B
  Left Join VAP1 c5 ON a.VAF07 = c5.VAP01
  LEFT JOIN AAU1 c3 ON c.AAU01 = c3.AAU01
where a.BDA01 = ''0'' and ((@lVAF11 = 3)or(@lVAF11 <> 3 and a.VAF11 = @lVAF11)) and a.VAF04 = 2 and c.BCK01C = @lBCK01 and a5.BCK01A = @lBCK01
  And (isnull(b.VBI29,0) = @lVBI29A or isnull(b.VBI29,0) = @lVBI29B)
  and ((a.VAF11 = 2 And a.VAF10 >= 8) or (a.VAF11 = 1 and ((@lStop = 0 and a.VAF10 = 3)or (@lStop = 1 and a.VAF10 >= 8))))
  and datediff(MINUTE,@lDt1,b.VBI10)>=0 and datediff(MINUTE,b.VBI10,@lDt2)>=0  
  And Exists(Select s.* from BCV1 s where s.BCK01A = @lBCK01 and (a.BCK01B = s.BCK01A or a.BCK01B = s.BCK01B)) 
  AND (c.VAE01 IN (select * from @kbVAE01) or a.VAF07 in (select * from @kbVAP01)) 
  AND a.VAF53 IN (select * from @kbBBX20) 

  select a.*,Case b.组成员数 When 0 then 1 else b.组成员数 End As 组成员数,c.ROWNR,case when 频率次数=1 then '''' else ''第1次'' end 当前次数
  into #tmpview
  from #TmpVbi a
  join (select 单号,组号,Max(成员序号) 组成员数 from #TmpVbi group by 单号,组号) b on a.单号=b.单号 and a.组号 = b.组号
  left join BCQ1 c on c.BCK01A = a.BCK01C and c.BCQ04 = a.床位
  where ((datediff(day,开始时间,执行日期)<>0 or 医嘱期效=''临嘱'')) or ((医嘱类型>=''A'' or 首次>=1) and datediff(day,开始时间,执行日期)=0)
  union all
  select a.*,Case b.组成员数 When 0 then 1 else b.组成员数 End As 组成员数,c.ROWNR,''第2次'' 当前次数
  from #TmpVbi a
  join (select 单号,组号,Max(成员序号) 组成员数 from #TmpVbi group by 单号,组号) b on a.单号=b.单号 and a.组号 = b.组号
  left join BCQ1 c on c.BCK01A = a.BCK01C and c.BCQ04 = a.床位
  where (频率次数>=2 and (datediff(day,开始时间,执行日期)<>0 or 医嘱期效=''临嘱'')) or (首次>=2 and datediff(day,开始时间,执行日期)=0)
  union all
  select a.*,Case b.组成员数 When 0 then 1 else b.组成员数 End As 组成员数,c.ROWNR,''第3次'' 当前次数
  from #TmpVbi a
  join (select 单号,组号,Max(成员序号) 组成员数 from #TmpVbi group by 单号,组号) b on a.单号=b.单号 and a.组号 = b.组号
  left join BCQ1 c on c.BCK01A = a.BCK01C and c.BCQ04 = a.床位
  where (频率次数>=3 and (datediff(day,开始时间,执行日期)<>0 or 医嘱期效=''临嘱'')) or (首次>=3 and datediff(day,开始时间,执行日期)=0)
  union all
  select a.*,Case b.组成员数 When 0 then 1 else b.组成员数 End As 组成员数,c.ROWNR,''第4次'' 当前次数
  from #TmpVbi a
  join (select 单号,组号,Max(成员序号) 组成员数 from #TmpVbi group by 单号,组号) b on a.单号=b.单号 and a.组号 = b.组号
  left join BCQ1 c on c.BCK01A = a.BCK01C and c.BCQ04 = a.床位
  where (频率次数>=4 and (datediff(day,开始时间,执行日期)<>0 or 医嘱期效=''临嘱'')) or (首次>=4 and datediff(day,开始时间,执行日期)=0)
  union all
  select a.*,Case b.组成员数 When 0 then 1 else b.组成员数 End As 组成员数,c.ROWNR,''第5次'' 当前次数
  from #TmpVbi a
  join (select 单号,组号,Max(成员序号) 组成员数 from #TmpVbi group by 单号,组号) b on a.单号=b.单号 and a.组号 = b.组号
  left join BCQ1 c on c.BCK01A = a.BCK01C and c.BCQ04 = a.床位
  where (频率次数>=5 and (datediff(day,开始时间,执行日期)<>0 or 医嘱期效=''临嘱'')) or (首次>=5 and datediff(day,开始时间,执行日期)=0)
 
  union all
  select a.*,Case b.组成员数 When 0 then 1 else b.组成员数 End As 组成员数,c.ROWNR,''第6次'' 当前次数
  from #TmpVbi a
  join (select 单号,组号,Max(成员序号) 组成员数 from #TmpVbi group by 单号,组号) b on a.单号=b.单号 and a.组号 = b.组号
  left join BCQ1 c on c.BCK01A = a.BCK01C and c.BCQ04 = a.床位
  where (频率次数>=6 and (datediff(day,开始时间,执行日期)<>0 or 医嘱期效=''临嘱'')) or (首次>=6 and datediff(day,开始时间,执行日期)=0)
 union all
  select a.*,Case b.组成员数 When 0 then 1 else b.组成员数 End As 组成员数,c.ROWNR,''第7次'' 当前次数
  from #TmpVbi a
  join (select 单号,组号,Max(成员序号) 组成员数 from #TmpVbi group by 单号,组号) b on a.单号=b.单号 and a.组号 = b.组号
  left join BCQ1 c on c.BCK01A = a.BCK01C and c.BCQ04 = a.床位
  where (频率次数>=7 and (datediff(day,开始时间,执行日期)<>0 or 医嘱期效=''临嘱'')) or (首次>=7 and datediff(day,开始时间,执行日期)=0)
 union all
  select a.*,Case b.组成员数 When 0 then 1 else b.组成员数 End As 组成员数,c.ROWNR,''第8次'' 当前次数
  from #TmpVbi a
  join (select 单号,组号,Max(成员序号) 组成员数 from #TmpVbi group by 单号,组号) b on a.单号=b.单号 and a.组号 = b.组号
  left join BCQ1 c on c.BCK01A = a.BCK01C and c.BCQ04 = a.床位
  where (频率次数>=8 and (datediff(day,开始时间,执行日期)<>0 or 医嘱期效=''临嘱'')) or (首次>=8 and datediff(day,开始时间,执行日期)=0)
 union all
  select a.*,Case b.组成员数 When 0 then 1 else b.组成员数 End As 组成员数,c.ROWNR,''第9次'' 当前次数
  from #TmpVbi a
  join (select 单号,组号,Max(成员序号) 组成员数 from #TmpVbi group by 单号,组号) b on a.单号=b.单号 and a.组号 = b.组号
  left join BCQ1 c on c.BCK01A = a.BCK01C and c.BCQ04 = a.床位
  where (频率次数>=9 and (datediff(day,开始时间,执行日期)<>0 or 医嘱期效=''临嘱'')) or (首次>=9 and datediff(day,开始时间,执行日期)=0)
 union all
  select a.*,Case b.组成员数 When 0 then 1 else b.组成员数 End As 组成员数,c.ROWNR,''第10次'' 当前次数
  from #TmpVbi a
  join (select 单号,组号,Max(成员序号) 组成员数 from #TmpVbi group by 单号,组号) b on a.单号=b.单号 and a.组号 = b.组号
  left join BCQ1 c on c.BCK01A = a.BCK01C and c.BCQ04 = a.床位
  where (频率次数>=10 and (datediff(day,开始时间,执行日期)<>0 or 医嘱期效=''临嘱'')) or (首次>=10 and datediff(day,开始时间,执行日期)=0)
 union all
  select a.*,Case b.组成员数 When 0 then 1 else b.组成员数 End As 组成员数,c.ROWNR,''第11次'' 当前次数
  from #TmpVbi a
  join (select 单号,组号,Max(成员序号) 组成员数 from #TmpVbi group by 单号,组号) b on a.单号=b.单号 and a.组号 = b.组号
  left join BCQ1 c on c.BCK01A = a.BCK01C and c.BCQ04 = a.床位
  where (频率次数>=11 and (datediff(day,开始时间,执行日期)<>0 or 医嘱期效=''临嘱'')) or (首次>=11 and datediff(day,开始时间,执行日期)=0)
 union all
  select a.*,Case b.组成员数 When 0 then 1 else b.组成员数 End As 组成员数,c.ROWNR,''第12次'' 当前次数
  from #TmpVbi a
  join (select 单号,组号,Max(成员序号) 组成员数 from #TmpVbi group by 单号,组号) b on a.单号=b.单号 and a.组号 = b.组号
  left join BCQ1 c on c.BCK01A = a.BCK01C and c.BCQ04 = a.床位
  where (频率次数>=12 and (datediff(day,开始时间,执行日期)<>0 or 医嘱期效=''临嘱'')) or (首次>=12 and datediff(day,开始时间,执行日期)=0)
 
  select * from #tmpview 
  Order by VAF11,ROWNR,就诊id,执行日期,当前次数,单号,组号,成员序号

IF Object_id(''tempdb..#TmpVbi'') IS NOT NULL DROP TABLE #TmpVbi
IF Object_id(''tempdb..#tmpview'') IS NOT NULL DROP TABLE #tmpview
IF Object_id(''tempdb..#TmpVaf2002'') IS NOT NULL DROP TABLE #TmpVaf2002', '2019-07-18 09:01:31', '(8088)王海涛', 0, 0)
else
print 'SYS_Scripts.id=2002 已经存在.'

GO


If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 2002)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=2002
GO


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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=7693)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode,Custom)  Values(7693, 33, 'Sql_病区执行单条码', '执行单', '病区执行单，注射、口服、护理、治疗单带条码脚本', 1, 1, Null, 1, 'DECLARE @lVAF11 int, @lBCK01 int, @lType int, @rPrint tinyint
Declare @lVBI29A tinyint,@lVBI29B tinyint, @lStop tinyint
DECLARE @lDt1 varchar(30),@lDt2 varchar(30), @aVAF07 int,@Param208 int
declare @lVAE01s varchar(1024),@lVAP01s varchar(1024),@lBBX20s varchar(64)
SET @lBCK01 = %d
SET @lVAF11 = %d
Set @lDt1 = Convert(varchar(16),Cast(%s as datetime),121)+'':00''
Set @lDt2 = Convert(varchar(16),Cast(%s as datetime),121)+'':59''
Set @lType = %d
Set @rPrint = %d
Set @lStop = %d
Set @aVAF07 = %d
set @lVAE01s =''%s''
set @lVAP01s = ''%s''
set @lBBX20s = ''%s''

declare @kbVAE01 table(VAE01 int)
insert into @kbVAE01(VAE01)
select * from dbo.Split(@lVAE01s,'','') 

declare @kbVAP01 table(VAP01 int)
insert into @kbVAP01(VAP01)
select * from dbo.Split(@lVAP01s,'','') 

declare @kbBBX20 table(BBX20 int)
insert into @kbBBX20(BBX20)
select * from dbo.Split(@lBBX20s,'','') 

Set @lVBI29A = 0
Set @lVBI29B = 0

if (@lType = 0 and @rPrint = 0)
begin
  Set @lVBI29A = 0
  Set @lVBI29B = 2
end
if (@lType = 0 and @rPrint = 1)
begin
  Set @lVBI29A = 1
  Set @lVBI29B = 3
end
if (@lType = 1 and @rPrint = 0)
begin
  Set @lVBI29A = 0
  Set @lVBI29B = 1
end
if (@lType = 1 and @rPrint = 1)
begin
  Set @lVBI29A = 2
  Set @lVBI29B = 3
end
if (@lType > 1 and @rPrint = 1)
begin
  Set @lVBI29A = 1
  Set @lVBI29B = 1
end

IF Object_id(''tempdb..#TmpVbi'') IS NOT NULL DROP TABLE #TmpVbi
IF Object_id(''tempdb..#TmpVaf1693'') IS NOT NULL DROP TABLE #TmpVaf1693

--select @Param208=isnull(BLP05,0) from BLP1 where BLP02 = 208
select * into #TmpVaf1693  from VAF2 a where exists(select * from @kbVAE01 where VAE01=a.VAF06)
SELECT c.VAE01 就诊id,c.VAE94 住院号, case when a.VAF07 > 0 and @Param208 = 1 then c5.VAP05 else c.VAE95 end 姓名
  ,Case c.VAE96 When ''1'' then ''男'' When ''2'' then ''女'' Else ''未知'' End As 性别
  , CAST(c.VAE46 as varchar(10))+isnull(c3.AAU02,'''') as 年龄
  ,a4.BCK03 科室,c.BCQ04B 床位,a.VAF22 医嘱名称
  ,case when a.BDA01 = ''3'' then a1.VAF23 else a.VAF23 end 医嘱嘱托
  ,a3.BBY05 药品名称,a3.BBY06 规格
  , a.VAF19 剂量, a2.BBX05 途径,a1.VAF23 用法说明, a.VAF26 频次, a.CBM01 单号, a.VAF59 组号, a.Rownr 成员序号
  ,b.VBI10 执行日期,a.VAF27 频率次数,a.VAF60 滴速,a.VAF61 首次,a.VAF62 末次 
  , CASE a.VAF11 WHEN 1 THEN ''长嘱'' WHEN 2 THEN ''临嘱'' end as 医嘱期效
  ,CASE WHEN a2.BBX20 = 0 THEN ''口服单'' WHEN a2.BBX20 = 1 THEN ''注射单''
        when (a2.BBX20 = 2) or (a2.BBX20 = 4) then ''输液单''
        When a2.BBX20 = 3 then ''治疗单'' When a2.BBX20 = 5 Then ''输血单''
        When a2.BBX20 = 6 Then ''护理单'' end as 执行单类型
  ,d.BAG03 含量,d1.BDG02 含量单位,d.BDG02B 住院单位,d.BAG07 住院包装,d.BDG02C 价格单位 
  ,d.BAG09 价格包装,a.VAF21 总量 
  ,a.VAF58 给药性质,a.VAF35 紧急标志,a.VAF07 婴儿id,c5.VAP05 婴儿姓名,c5.VAP07 婴儿出生日期
  ,a.VAF36 开始时间,a.BDA01 医嘱类型,a2.BBX20,a.VAF11,b.VBI01,c.BCK01C ,a.VAF30 执行方案,a.VAF25 皮试结果,a.VAF42 开嘱时间
  ,d.BLW01 高危药品,a.crypt,a.VAF22,a.BDA01
Into #TmpVbi
FROM #TmpVaf1693 a
  JOIN #TmpVaf1693 a1 ON a.VAF01A = a1.VAF01
  JOIN BBX1 a2 ON a1.BBX01 = a2.BBX01
  join BBY1 a3 on a.BBY01 = a3.BBY01
  join BAG1 d on a3.BBY01 = d.BBY01
  join BBX1 d1 on d.BBX01 = d1.BBX01
  JOIN VBI2 b ON a.VAF01 = b.VAF01
  JOIN VAE1 c ON a.VAF06 = c.VAE01
  Join BCK1 a4 on a.BCK01A = a4.BCK01
  Join BCV1 a5 on a.BCK01A = a5.BCK01B  
  Left Join VAP1 c5 ON a.VAF07 = c5.VAP01
  LEFT JOIN AAU1 c3 ON c.AAU01 = c3.AAU01
WHERE c.BCK01C = @lBCK01 and a5.BCK01A = @lBCK01 AND a.VAF04 = 2 and ((@lVAF11 = 3)or(@lVAF11 <> 3 and a.VAF11 = @lVAF11)) and a.VAF54 = 0
  AND (c.VAE01 IN (select * from @kbVAE01) or a.VAF07 IN (select * from @kbVAP01)) 
  and Exists(Select s.* from BCV1 s where s.BCK01A = @lBCK01 and (a.BCK01D = s.BCK01A or a.BCK01D = s.BCK01B))
  AND a2.BBX20 IN (select * from @kbBBX20) and (isnull(b.VBI29,0) = @lVBI29A or isnull(b.VBI29,0) = @lVBI29B)
  and ((a.VAF11 = 2 And a.VAF10 = 8) or (a.VAF11 = 1 and ((@lStop = 0 and a.VAF10 = 3)or (@lStop = 1 and a.VAF10 >= 8))))
  AND (a.BDA01 >= ''1'' And a.BDA01 <= ''3'') AND a2.BDA01 = ''T'' AND a2.BBX13 in (''2'',''4'')
  And datediff(MINUTE,@lDt1,b.VBI10)>=0 and datediff(MINUTE,b.VBI10,@lDt2)>=0  
Union All
Select c.VAE01 就诊id,c.VAE94 住院号, case when a.VAF07 > 0 and @Param208 = 1 then c5.VAP05 else c.VAE95 end 姓名
  ,Case c.VAE96 When ''1'' then ''男'' When ''2'' then ''女'' Else ''未知'' End As 性别
  , CAST(c.VAE46 as varchar(10))+isnull(c3.AAU02,'''') as 年龄
  ,a4.BCK03 科室,c.BCQ04B 床位,a.VAF22 医嘱名称,a.VAF23 医嘱嘱托,a1.BBX05 药品名称,'''' 规格
  ,'''' 剂量, '''' 途径,a.VAF23 用法说明, a.VAF26 频次, a.CBM01 单号, a.VAF59 组号, a.Rownr 成员序号
  ,b.VBI10 执行日期,a.VAF27 频率次数,null 滴速,0 首次,0 末次 
  , CASE a.VAF11 WHEN 1 THEN ''长嘱'' WHEN 2 THEN ''临嘱'' end as 医嘱期效
  ,CASE WHEN a1.BBX20 = 0 THEN ''口服单'' WHEN a1.BBX20 = 1 THEN ''注射单''
        when (a1.BBX20 = 2) or (a1.BBX20 = 4) then ''输液单''
        When a1.BBX20 = 3 then ''治疗单'' When a1.BBX20 = 5 Then ''输血单''
        When a1.BBX20 = 6 Then ''护理单'' end as 执行单类型
  ,1 含量,'''' 含量单位,'''' 住院单位,1 住院包装,'''' 价格单位,1 价格包装,a.VAF21 总量 
  ,a.VAF58 给药性质,a.VAF35 紧急标志,a.VAF07 婴儿id,c5.VAP05 婴儿姓名,c5.VAP07 婴儿出生日期
  ,a.VAF36 开始时间,a.BDA01 医嘱类型,a1.BBX20,a.VAF11,b.VBI01,c.BCK01C,a.VAF30 执行方案,a.VAF25 皮试结果,a.VAF42 开嘱时间
  ,'''' 高危药品,a.crypt,a.VAF22,a.BDA01
From #TmpVaf1693 a
  Join BBX1 a1 on a.BBX01 = a1.BBX01
  JOIN VBI2 b ON a.VAF01 = b.VAF01
  JOIN VAE1 c ON a.VAF06 = c.VAE01
  Join BCK1 a4 on a.BCK01A = a4.BCK01
  Join BCV1 a5 on a.BCK01A = a5.BCK01B
  Left Join VAP1 c5 ON a.VAF07 = c5.VAP01
  LEFT JOIN AAU1 c3 ON c.AAU01 = c3.AAU01
where a.BDA01 in (''B'',''D'',''N'',''O'',''T'',''Z'') and a.VAF32 = 0 and a.VAF01A = 0 and ((@lVAF11 = 3)or(@lVAF11 <> 3 and a.VAF11 = @lVAF11))
  And a.VAF54 = 0 and a.VAF04 = 2 and c.BCK01C = @lBCK01 and a5.BCK01A = @lBCK01
  And Exists(Select s.* from BCV1 s where s.BCK01A = @lBCK01 and (a.BCK01B = s.BCK01A or a.BCK01B = s.BCK01B)) 
  And (c.VAE01 IN (select * from @kbVAE01) or a.VAF07 IN (select * from @kbVAP01))
  and a1.BBX20 in (select * from @kbBBX20) And (isnull(b.VBI29,0) = @lVBI29A or isnull(b.VBI29,0) = @lVBI29B)
  and ((a.VAF11 = 2 And a.VAF10 = 8) or (a.VAF11 = 1 and ((@lStop = 0 and a.VAF10 = 3)or (@lStop = 1 and a.VAF10 >= 8))))
  and datediff(MINUTE,@lDt1,b.VBI10)>=0 and datediff(MINUTE,b.VBI10,@lDt2)>=0  
Union All
Select c.VAE01 就诊id,c.VAE94 住院号, case when a.VAF07 > 0 and @Param208 = 1 then c5.VAP05 else c.VAE95 end 姓名
  ,Case c.VAE96 When ''1'' then ''男'' When ''2'' then ''女'' Else ''未知'' End As 性别
  , CAST(c.VAE46 as varchar(10))+isnull(c3.AAU02,'''') as 年龄
  ,a4.BCK03 科室,c.BCQ04B 床位,a.VAF22 医嘱名称,a.VAF23 医嘱嘱托,a.VAF22 药品名称,'''' 规格
  ,'''' 剂量, '''' 途径,a.VAF23 用法说明, a.VAF26 频次, a.CBM01 单号, a.VAF59 组号, a.Rownr 成员序号
  ,b.VBI10 执行日期,a.VAF27 频率次数,null 滴速,0 首次,0 末次 
  , CASE a.VAF11 WHEN 1 THEN ''长嘱'' WHEN 2 THEN ''临嘱'' end as 医嘱期效
  ,CASE WHEN a.VAF53 = 0 THEN ''口服单'' WHEN a.VAF53 = 1 THEN ''注射单''
        when (a.VAF53 = 2) or (a.VAF53 = 4) then ''输液单''
        When a.VAF53 = 3 then ''治疗单'' When a.VAF53 = 5 Then ''输血单''
        When a.VAF53 = 6 Then ''护理单'' end as 执行单类型
  ,1 含量,'''' 含量单位,'''' 住院单位,1 住院包装,'''' 价格单位,1 价格包装,a.VAF21 总量 
  ,0 给药性质,a.VAF35 紧急标志,a.VAF07 婴儿id,c5.VAP05 婴儿姓名,c5.VAP07 婴儿出生日期
  ,a.VAF36 开始时间,a.BDA01 医嘱类型,a.VAF53,a.VAF11,b.VBI01,c.BCK01C ,a.VAF30 执行方案,a.VAF25 皮试结果,a.VAF42 开嘱时间
  ,'''' 高危药品,a.crypt,a.VAF22,a.BDA01
From #TmpVaf1693 a
  JOIN VBI2 b ON a.VAF01 = b.VAF01
  JOIN VAE1 c ON a.VAF06 = c.VAE01
  Join BCK1 a4 on a.BCK01A = a4.BCK01
  Join BCV1 a5 on a.BCK01A = a5.BCK01B
  Left Join VAP1 c5 ON a.VAF07 = c5.VAP01
  LEFT JOIN AAU1 c3 ON c.AAU01 = c3.AAU01
where a.BDA01 = ''0'' and ((@lVAF11 = 3)or(@lVAF11 <> 3 and a.VAF11 = @lVAF11)) And a.VAF54 = 0 and a.VAF04 = 2 
  and c.BCK01C = @lBCK01 and a5.BCK01A = @lBCK01
  And (c.VAE01 IN (select * from @kbVAE01) or a.VAF07 IN (select * from @kbVAP01)) 
  and a.VAF53 in (select * from @kbBBX20) And (isnull(b.VBI29,0) = @lVBI29A or isnull(b.VBI29,0) = @lVBI29B)
  and ((a.VAF11 = 2 And a.VAF10 = 8) or (a.VAF11 = 1 and ((@lStop = 0 and a.VAF10 = 3)or (@lStop = 1 and a.VAF10 >= 8))))
  and datediff(MINUTE,@lDt1,b.VBI10)>=0 and datediff(MINUTE,b.VBI10,@lDt2)>=0  
  And Exists(Select s.* from BCV1 s where s.BCK01A = @lBCK01 and (a.BCK01B = s.BCK01A or a.BCK01B = s.BCK01B)) 

  select a.*,Case b.组成员数 When 0 then 1 else b.组成员数 End As 组成员数,c.ROWNR,''第''+CAST(ISNULL(d.VCM12,1) as varchar)+''次'' 当前次数,d.VBI21 条码
  from #TmpVbi a
  join (select 单号,组号,Max(成员序号) 组成员数 from #TmpVbi group by 单号,组号) b on a.单号=b.单号 and a.组号 = b.组号
  left join VCM1 d on d.VBI01 = a.VBI01
  left join BCQ1 c on c.BCK01A = a.BCK01C and c.BCQ04 = a.床位
  Order by a.VAF11,c.ROWNR,a.就诊id,a.婴儿id,d.VBI21,a.BBX20,a.开始时间,a.单号,a.组号,d.VCM12,d.VCM10

IF Object_id(''tempdb..#TmpVbi'') IS NOT NULL DROP TABLE #TmpVbi
IF Object_id(''tempdb..#TmpVaf1693'') IS NOT NULL DROP TABLE #TmpVaf1693', '2019-07-18 08:55:59', '(8088)王海涛', 0, 0)
else
print 'SYS_Scripts.id=7693 已经存在.'

GO


If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 7693)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=7693
GO