--增加分类
GO
IF not EXISTS(SELECT * FROM SYS_ScriptCategories WHERE id=31)
insert into SYS_ScriptCategories(ID,Code,Name,ComponentID,xRowNum,ParentID,xLevel,Description)  Values(31, 'SC31', '临床医护管理', 17, 0, 0, 0, Null)
else
print 'SYS_ScriptCategories.id=31 已经存在.'

GO


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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=717)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(717, 31, 'Advice_BDO1_Sel', '下达医嘱脚本', '树形左边列表：医嘱编辑窗口检索医嘱项目', 1, 1, Null, 1, 'select case when len(BDM01) <= 1 then -ASCII(BDM01) else -(ASCII(substring(BDM01,1,1)) + ASCII(substring(BDM01,2,1))) end AS BDO01
  ,BDM02 AS BDO02,0 AS PatientID, BDM01 
FROM BDM1
UNION all
select
  BDO01 
  ,BDO03
  ,PatientID = CASE WHEN BDO01A IS null THEN case when len(BDM01) <= 1 then -ASCII(BDM01) else -(ASCII(substring(BDM01,1,1)) + ASCII(substring(BDM01,2,1))) end ELSE BDO01A end
  ,BDM01
from BDO1', '2014-02-18 11:15:25', '(8088)王海涛', 0)
else
print 'SYS_Scripts.id=717 已经存在.'

GO


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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=725)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(725, 31, 'Advice_List_sel', '下达医嘱脚本', '树形：按药品规格 检索品种分类对应的明细项目', 1, 1, Null, 1, 'DECLARE @lACF01 int, @lBDO01 int, @lSql varchar(100),@abc02 varchar(20),@bdp02 varchar(20)
DECLARE @lBDA01 varchar(10), @lBCK01 int, @lDate Datetime,@gparam60 varchar(10)
declare @aParamno varchar(10),@aParam1 varchar(10),@BCKyf int,@vaf58 tinyint
SET @lACF01 = %d
SET @lBDO01 = %d
SET @lSql = %s
SET @lBDA01 = %s
SET @lBCK01 = %d
set @BCKyf = %d
set @abc02 = %s
set @bdp02 = %s
set @vaf58 = %d
set @gparam60 = dbo.GetSysParamValue(9999,9999,60)
Set @lDate = Convert(varchar(10),Getdate(),21)+'' 23:59:59.999''
set @aParamno = dbo.GetSysParamValue(9999,9999,15)
set @aParam1 = dbo.GetSysParamValue(9999,9999,1)
declare @tmpBAU table(BCK01 int,ACF01 int)
declare @tmpBCK table(BCK01 int)
/*
if object_id(''tempdb..#tmp_BDO1'') is not null drop table #tmp_BDO1
;with subqry(BDO01,BDO03,BDO01A,BDM01) 
  as (
  select BDO01,BDO03,BDO01A,BDM01 from BDO1 where BDO01=@lBDO01
  union all
  select a.BDO01,a.BDO03,a.BDO01A,a.BDM01 from BDO1 a,subqry
  where a.BDO01A = subqry.BDO01
  ) select * into #tmp_BDO1 from subqry
            */
if @aParamno = ''0''
begin
  insert into @tmpBAU(BCK01,ACF01)
  select BCK01,ACF01 from baz1 where bau01 in (''51'',''52'',''53'')  

  SELECT e.BDA01, e.BDA02,f.AAS02, a.BBX01, a.BDO01, d.BBY04 as BBX04, a.BBX05, d.BBY05, a.BDG02
    , (Isnull(b.BAP02,'''')+''、''+Isnull(c.BAG11,'''')+''、''+ case when ISNULL(f2.BHJ01,0)=0 then '''' else f2.BHJ02 end) as NBBX13
    , b.BBT10, b.BBT11, d.BBY06, d.BBE02, d.BBY01,d.BBY08,c.BAG27
    , CASE WHEN @lACF01=1 THEN c.BDG02A ELSE c.BDG02B END AS NUnit
    , case when @gparam60=''2'' then dbo.GetDrugPrice(h1.BCK01 , d.BBY01 , @lACF01 , @abc02)
        else CASE WHEN @lACF01=1 THEN d.BBY25*c.BAG05 ELSE d.BBY25*c.BAG07 END end AS Price   
    , CASE WHEN @lACF01 = 1 THEN ISNULL(h1.LSQty,0)/c.BAG05 ELSE ISNULL(h1.LSQty,0)/c.BAG07 END AS amount
    ,h2.BCK03 , f1.BCG02,d.BBY29,f.AAS07,isnull(h1.BCK01,0) BCK01,d.BBY44
  FROM BBX1 a
  JOIN BBT1 b ON a.BBX01 = b.BBX01
  JOIN BAG1 c ON b.BBX01 = c.BBX01
  JOIN BBY1 d ON c.BBY01 = d.BBY01 
  JOIN BDA1 e ON a.BDA01 = e.BDA01
  JOIN BBJ1 h ON d.BBY01 = h.BBY01
  left JOIN V_DPK6 h1 ON h.BBY01 = h1.BBY01 AND h.BCK01B = h1.BCK01 and @lACF01 = (h1.ACF01 & @lACF01)
  left join BCK1 h2 on h2.BCK01 = h1.BCK01
  LEFT JOIN AAS1 f ON f.AAS01 = d.AAS01
  left join BCG1 f1 on f1.BCG01 = d.BCG01
  left join BHJ1 f2 on f2.BHJ01 = b.BBT08
  WHERE ((@lBDO01>-1 AND a.bdo01=@lbdo01)OR(@lBDO01=-1 AND (@lBDA01 = ''-1'' or e.BEH01 = @lBDA01)))
    and ((@aParam1 = ''1'') or (@aParam1 <> ''1'' and ((@vaf58 <>1 and ISNULL(h1.LSQty,0)>0) or @vaf58=1)))
    AND h.BCK01A = @lBCK01 AND a.BDA01 < ''4'' And @lDate < d.BBY31
    and ((d.BBY47 = 0)or(d.BBY47 = 1 and exists(select * from BNN1 c2 where c2.BBY01 = d.BBY01 and c2.BCK01 = @lBCK01)))
   and ((d.BBY49 = 0)or(d.BBY49 = 1 and exists(select * from BNR1 c3 where c3.BBY01 = d.BBY01 and c3.BDP02 = @bdp02)))
    and exists(select * from @tmpBAU h3 where h3.BCK01 = h.BCK01B and @lACF01 = (h3.ACF01 & @lACF01) )
    AND @lACF01 = (d.ACF01 & @lACF01) AND (d.BBY04 LIKE @lSql OR d.BBY44 LIKE @lSql OR 
      (EXISTS(SELECT g.* FROM BDK1 g WHERE g.BBX01 = a.BBX01
        AND (g.BDK03 LIKE @lSql OR g.ABBRP LIKE @lSql OR g.ABBRW LIKE @lSql))))  
end
else
begin
  if @aParamno = ''3'' 
  begin
    if @lACF01 = 1
    begin
      insert into @tmpBCK(BCK01)
      select @BCKyf
    end
    if @lACF01 = 2
    begin
      insert into @tmpBCK(BCK01)
      select BCK01
      from dbo.GetSearchDrugBck(@lBCK01)
    end
  end
  SELECT e.BDA01, e.BDA02,f.AAS02, a.BBX01, a.BDO01, d.BBY04 as BBX04, a.BBX05, d.BBY05, a.BDG02
    , (Isnull(b.BAP02,'''')+''、''+Isnull(c.BAG11,'''')+''、''+ case when ISNULL(f2.BHJ01,0)=0 then '''' else f2.BHJ02 end) as NBBX13
    , b.BBT10, b.BBT11, d.BBY06, d.BBE02, d.BBY01,d.BBY08,c.BAG27
    , CASE WHEN @lACF01=1 THEN c.BDG02A ELSE c.BDG02B END AS NUnit
    , case when @gparam60=''2'' then dbo.GetDrugPrice(h1.BCK01 , d.BBY01 , @lACF01 , @abc02)
        else CASE WHEN @lACF01=1 THEN d.BBY25*c.BAG05 ELSE d.BBY25*c.BAG07 END end AS Price   
    , CASE WHEN @lACF01 = 1 THEN ISNULL(h1.LSQty,0)/c.BAG05 ELSE ISNULL(h1.LSQty,0)/c.BAG07 END AS amount
    ,h2.BCK03 , f1.BCG02,d.BBY29 ,f.AAS07,isnull(h1.BCK01,0) BCK01,d.BBY44
  FROM BBX1 a
  JOIN BBT1 b ON a.BBX01 = b.BBX01
  JOIN BAG1 c ON b.BBX01 = c.BBX01
  JOIN BBY1 d ON c.BBY01 = d.BBY01 
  JOIN BDA1 e ON a.BDA01 = e.BDA01
  left JOIN V_DPK6 h1 ON h1.BBY01 = d.BBY01 and (@aParamno <> ''3'' or (@aParamno = ''3'' and exists(select * from @tmpBCK h3 where h3.BCK01 = h1.BCK01))) 
  left join BCK1 h2 on h2.BCK01 = h1.BCK01
  LEFT JOIN AAS1 f ON f.AAS01 = d.AAS01
  left join BCG1 f1 on f1.BCG01 = d.BCG01
  left join BHJ1 f2 on f2.BHJ01 = b.BBT08
  WHERE ((@lBDO01>-1 AND a.bdo01=@lbdo01)OR(@lBDO01=-1 AND (@lBDA01 = ''-1'' or e.BEH01 = @lBDA01)))
    and ((@aParam1 = ''1'') or (@aParam1 <> ''1'' and ((@vaf58 <>1 and ISNULL(h1.LSQty,0)>0) or @vaf58=1)))
    AND a.BDA01 < ''4'' And @lDate < d.BBY31    
    and ((d.BBY47 = 0)or(d.BBY47 = 1 and exists(select * from BNN1 c2 where c2.BBY01 = d.BBY01 and c2.BCK01 = @lBCK01)))
   and ((d.BBY49 = 0)or(d.BBY49 = 1 and exists(select * from BNR1 c3 where c3.BBY01 = d.BBY01 and c3.BDP02 = @bdp02)))
    AND @lACF01 = (d.ACF01 & @lACF01) AND (d.BBY04 LIKE @lSql OR d.BBY44 LIKE @lSql OR 
      (EXISTS(SELECT g.* FROM BCL1 g WHERE g.BBY01 = d.BBY01
        AND (g.BCL03 LIKE @lSql OR g.ABBRP LIKE @lSql OR g.ABBRW LIKE @lSql))))  
end

--if object_id(''tempdb..#tmp_BDO1'') is not null drop table #tmp_BDO1', '2016-08-09 17:47:39', '(8088)王海涛', 0)
else
print 'SYS_Scripts.id=725 已经存在.'

GO


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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=726)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(726, 31, 'Advice_List_all', '下达医嘱脚本', ' 表格：检索医嘱项目，用于表格形式，药品按规格', 1, 1, Null, 1, 'DECLARE @lSqlText varchar(100), @lACF01 int, @lBBX09 int,@lBCK01 int, @lBCE01 int,@abc02 varchar(20),@bdp02 varchar(20)
Declare @lDate DateTime, @aParamno varchar(10),@aParam1 varchar(10),@BCKyf int,@gparam60 varchar(10)
declare @vaf58 tinyint
SET @lSqlText = %s
SET @lACF01 = %d
SET @lBBX09 = %d  
SET @lBCK01 = %d
SET @lBCE01 = %d
set @BCKyf = %d
set @abc02 = %s
set @bdp02 = %s
set @vaf58 = %d
set @gparam60 = dbo.GetSysParamValue(9999,9999,60)
Set @lDate = Convert(varchar(10),Getdate(),21)+'' 23:59:59.999''
set @aParamno = dbo.GetSysParamValue(9999,9999,15)
set @aParam1 = dbo.GetSysParamValue(9999,9999,1)
declare @tmpBCK table(BCK01 int)
if object_id(''tempdb..#kbmbbj'') is not null drop table #kbmbbj
  if @aParamno = ''3'' 
  begin
    if @lACF01 = 1
    begin
      insert into @tmpBCK(BCK01)
      select @BCKyf
    end
    if @lACF01 = 2
    begin
      insert into @tmpBCK(BCK01)
      select BCK01
      from dbo.GetSearchDrugBck(@lBCK01)
    end
  end  
  select a.BBY01,a.BCK01A,a.BCK01B into #kbmbbj 
  from BBJ1 a  
  where @aParamno = ''0'' and a.BCK01A = @lBCK01
    and exists(select *from baz1 b where b.BCK01 = a.BCK01B and @lACF01 = (b.ACF01 & @lACF01)  )
SELECT e.BDA01, e.BDA02, f.AAS02, a.BBX01, a.BDO01, d.BBY04 as BBX04, a.BBX05
  , (d.BBY05+case when bag16>'''' then ''(''+bag16+'')'' else '''' end) BBY05, a.BDG02  
  , (Isnull(b.BAP02,'''')+''、''+Isnull(c.BAG11,'''')+''、''+ case when ISNULL(f2.BHJ01,0)=0 then '''' else f2.BHJ02 end) as NBBX13
  , d.BBY06, d.BBE02, d.BBY01, c.BAG27
  , CASE WHEN @lACF01=1 THEN c.BDG02A ELSE c.BDG02B END AS NUnit
  , case when @gparam60=''2'' then dbo.GetDrugPrice(h1.BCK01 , d.BBY01 , @lACF01 , @abc02)
        else CASE WHEN @lACF01=1 THEN d.BBY25*c.BAG05 ELSE d.BBY25*c.BAG07 END end AS Price   
  , CASE WHEN @lACF01 = 1 THEN ISNULL(h1.LSQty,0)/c.BAG05 ELSE ISNULL(h1.LSQty,0)/c.BAG07 END AS amount
  ,h2.BCK03, f1.BCG02,d.BBY29 ,f.AAS07,isnull(h1.BCK01,0) BCK01,d.BBY44
FROM BBX1 a
  JOIN BBT1 b ON a.BBX01 = b.BBX01
  JOIN BAG1 c ON b.BBX01 = c.BBX01
  JOIN BBY1 d ON c.BBY01 = d.BBY01 
  JOIN BDA1 e ON a.BDA01 = e.BDA01
  JOIN #kbmbbj h ON d.BBY01 = h.BBY01
  left JOIN V_DPK6 h1 ON h.BBY01 = h1.BBY01 AND h.BCK01B = h1.BCK01 and @lACF01 = (h1.ACF01 & @lACF01)
  left join BCK1 h2 on h2.BCK01 = h1.BCK01
  LEFT JOIN AAS1 f ON f.AAS01 = d.AAS01
  left join BCG1 f1 on f1.BCG01 = d.BCG01 
  left join BHJ1 f2 on f2.BHJ01 = b.BBT08
WHERE @aParamno = ''0'' and ((@aParam1 = ''1'') or (@aParam1 <> ''1'' and ((@vaf58 <>1 and ISNULL(h1.LSQty,0)>0) or @vaf58=1)))
  and @lACF01 = (d.ACF01 & @lACF01) AND a.BDA01 < ''4'' AND @lDate < d.BBY31
  and ((d.BBY47 = 0)or(d.BBY47 = 1 and exists(select * from BNN1 c2 where c2.BBY01 = d.BBY01 and c2.BCK01 = @lBCK01)))
   and ((d.BBY49 = 0)or(d.BBY49 = 1 and exists(select * from BNR1 c3 where c3.BBY01 = d.BBY01 and c3.BDP02 = @bdp02)))
  and (d.BBY04 LIKE @lSqlText OR d.BBY44 LIKE @lSqlText 
  OR (EXISTS(SELECT g.* FROM BDK1 g WHERE g.BBX01 = a.BBX01
    AND (g.BDK03 LIKE @lSqlText OR g.ABBRP LIKE @lSqlText OR g.ABBRW LIKE @lSqlText)))) 
union all
SELECT e.BDA01, e.BDA02, f.AAS02, a.BBX01, a.BDO01, d.BBY04 as BBX04, a.BBX05
  , d.BBY05+case when bag16>'''' then ''(''+bag16+'')'' else '''' end BBY05, a.BDG02
  , (Isnull(b.BAP02,'''')+''、''+Isnull(c.BAG11,'''')+''、''+ case when ISNULL(f2.BHJ01,0)=0 then '''' else f2.BHJ02 end) as NBBX13
  , d.BBY06, d.BBE02, d.BBY01, c.BAG27
  , CASE WHEN @lACF01=1 THEN c.BDG02A ELSE c.BDG02B END AS NUnit
  , case when @gparam60=''2'' then dbo.GetDrugPrice(h1.BCK01 , d.BBY01 , @lACF01 , @abc02)
        else CASE WHEN @lACF01=1 THEN d.BBY25*c.BAG05 ELSE d.BBY25*c.BAG07 END end AS Price     
  , CASE WHEN @lACF01 = 1 THEN ISNULL(h1.LSQty,0)/c.BAG05 ELSE ISNULL(h1.LSQty,0)/c.BAG07 END AS amount
  ,h2.BCK03, f1.BCG02,d.BBY29,f.AAS07,isnull(h1.BCK01,0) BCK01,d.BBY44
FROM BBX1 a
  JOIN BBT1 b ON a.BBX01 = b.BBX01
  JOIN BAG1 c ON b.BBX01 = c.BBX01
  JOIN BBY1 d ON c.BBY01 = d.BBY01 
  JOIN BDA1 e ON a.BDA01 = e.BDA01
  left JOIN V_DPK6 h1 ON h1.BBY01 = d.BBY01 and (@aParamno <> ''3'' or (@aParamno = ''3'' and exists(select * from @tmpBCK h3 where h3.BCK01 = h1.BCK01))) 
  left join BCK1 h2 on h2.BCK01 = h1.BCK01
  LEFT JOIN AAS1 f ON f.AAS01 = d.AAS01
  left join BCG1 f1 on f1.BCG01 = d.BCG01
  left join BHJ1 f2 on f2.BHJ01 = b.BBT08 
WHERE @aParamno <> ''0'' and ((@aParam1 = ''1'') or (@aParam1 <> ''1'' and ((@vaf58 <>1 and ISNULL(h1.LSQty,0)>0) or @vaf58=1)))
  and @lACF01 = (d.ACF01 & @lACF01) AND a.BDA01 < ''4'' AND @lDate < d.BBY31  
  and ((d.BBY47 = 0)or(d.BBY47 = 1 and exists(select * from BNN1 c2 where c2.BBY01 = d.BBY01 and c2.BCK01 = @lBCK01)))
   and ((d.BBY49 = 0)or(d.BBY49 = 1 and exists(select * from BNR1 c3 where c3.BBY01 = d.BBY01 and c3.BDP02 = @bdp02)))
  and (d.BBY04 LIKE @lSqlText OR d.BBY44 LIKE @lSqlText 
  OR (EXISTS(SELECT g.* FROM BDK1 g WHERE g.BBX01 = a.BBX01
    AND (g.BDK03 LIKE @lSqlText OR g.ABBRP LIKE @lSqlText OR g.ABBRW LIKE @lSqlText)))) 
union all
SELECT e.BDA01, e.BDA02, f.AAS02, a.BBX01, a.BDO01, d.BBY04 as BBX04, d.BBY05 BBX05, d.BBY05
  , a.BDG02, '''' NBBX13, d.BBY06, d.BBE02, d.BBY01, 0 BAG27, d.BBY08 NUnit
  ,case when @gparam60=''2'' then dbo.GetBBY1Price(d.BBY01,@abc02) else d.BBY25 end price 
  , NULL AS amount,'''' BCK03, h.BCG02,d.BBY29,f.AAS07,0,d.BBY44
FROM BBX1 a
JOIN BCT1 c ON c.BBX01 = a.BBX01
JOIN BBY1 d ON d.BBY01 = c.BBY01 
JOIN BDA1 e ON e.BDA01 = a.BDA01  
LEFT JOIN AAS1 f ON f.AAS01 = d.AAS01
left join BCG1 h on h.BCG01 = d.BCG01
WHERE @lACF01 = (d.ACF01 & @lACF01) AND e.BDA01 in (''4'',''M'') and c.BCT33 = 1 AND d.BBY31 > @lDate
   and ((d.BBY47 = 0)or(d.BBY47 = 1 and exists(select * from BNN1 c2 where c2.BBY01 = d.BBY01 and c2.BCK01 = @lBCK01)))
   and ((d.BBY49 = 0)or(d.BBY49 = 1 and exists(select * from BNR1 c3 where c3.BBY01 = d.BBY01 and c3.BDP02 = @bdp02)))
  and (d.BBY04 LIKE @lSqlText OR d.BBY44 LIKE @lSqlText 
  OR (EXISTS(SELECT g.* FROM BCL1 g WHERE g.BBY01 = d.BBY01
    AND (g.BCL03 LIKE @lSqlText OR g.ABBRP LIKE @lSqlText OR g.ABBRW LIKE @lSqlText))))       
UNION ALL 
SELECT e.BDA01,e.BDA02,f.AAS02
  ,a.BBX01,a.BDO01,a.BBX04,a.BBX05,a.BBX05,a.BDG02
  ,CASE WHEN a.BDA01 IN (''8'',''9'') THEN a.BBX06
         WHEN a.BDA01 = ''T'' THEN a1.AAF02
         WHEN a.BDA01 = ''E'' THEN a2.BBC02
         WHEN a.BDA01 = ''L'' THEN a3.AAV02
         WHEN a.BDA01 = ''S'' THEN a4.ACH02
         WHEN a.BDA01 = ''A'' THEN a5.ACI02         
         WHEN a.BDA01 = ''N'' THEN a6.AAG02
         WHEN a.BDA01 = ''Z'' THEN a7.AAP02
       ELSE NULL END NBBX13 
  ,a.BBX06, NULL AS BBE02,a.BBX01,a.BBX12,a.BDG02 AS NUnit
  ,case when @gparam60=''2'' then dbo.GetBBX1Price(a.BBX01,@abc02) else vb.Price end price 
  ,NULL ,'''', '''',a.BBX29,f.AAS07,0 BCK01,a.BBX36
FROM BBX1 a
  JOIN BDA1 e ON a.BDA01 = e.BDA01
  LEFT JOIN V_BDU vb ON a.BBX01 = vb.BBX01
  LEFT JOIN AAS1 f ON f.AAS01 = a.AAS01
  LEFT JOIN AAF1 a1 ON a.BDA01=''T'' AND a.BBX13=a1.AAF01
  LEFT JOIN BBC1 a2 ON a.BDA01=''E''  AND a.BBX13=a2.BBC01
  LEFT JOIN AAV1 a3 ON a.BDA01=''L'' AND a.BBX13=a3.AAV01
  LEFT JOIN ACH1 a4 ON a.BDA01=''S'' AND a.bbx13=a4.ACH01
  LEFT JOIN ACI1 a5 ON a.BDA01=''A'' AND a.BBX01=a5.ACI01 
  LEFT JOIN AAP1 a7 ON a.BDA01=''Z'' AND a.BBX13=a7.AAP01 
  LEFT JOIN AAG1 a6 ON a.BDA01=''N'' AND a.BBX13=CONVERT(varchar(10),a6.AAG01)  
WHERE e.BDA01 >''7'' and e.BDA01 <> ''M'' and ((a.BBX11 =1)or(a.BBX11 = 0 and a.BBX12  in (1,3,4))) AND a.BBX09 IN (0,@lBBX09)
 AND @lDate < a.BBX25 
 and ((a.BBX30 = 0)or(a.BBX30 = 1 and exists(select * from BIP1 c2 where c2.BBX01 = a.BBX01 and c2.BCK01 = @lBCK01)))
 and ((a.BBX46 = 0)or(a.BBX46 = 1 and exists(select * from BMP1 c3 where c3.BBX01 = a.BBX01 and c3.BDP02 = @bdp02)))
 AND ((a.BDA01 <> ''9'')OR((a.BDA01=''9'') AND ((a.BBX15=0 and a.BCE01 = @lBCE01)
    or a.BBX15=2 or (a.BBX15=1 and exists(select c.* from BAR1 c where a.BBX01=c.BBX01 and c.BCK01 =@lBCK01)))))
 AND @lACF01 = (a.ACF01 & @lACF01) and (a.BBX04 LIKE @lSqlText OR a.bbx36 LIKE @lSqlText 
  OR (EXISTS(SELECT g.* FROM BDK1 g WHERE g.BBX01 = a.BBX01
    AND (g.BDK03 LIKE @lSqlText OR g.ABBRP LIKE @lSqlText OR g.ABBRW LIKE @lSqlText))))  
order by e.BDA01,a.BBX01

if object_id(''tempdb..#kbmbbj'') is not null drop table #kbmbbj', '2016-08-09 17:43:20', '(8088)王海涛', 0)
else
print 'SYS_Scripts.id=726 已经存在.'

GO


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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=757)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(757, 31, 'Advice_List_Sel_B', '下达医嘱脚本', '临嘱 树形： 诊疗  检索医嘱分类对应的明细项目', 1, 1, Null, 1, 'DECLARE @lBDO01 int, @lACF01 int, @lSql varchar(100), @lBBX09 int,@abc02 varchar(20),@bdp02 varchar(20)
DECLARE @lBCE01 int, @lBCK01 int, @lDate DateTime,@gparam60 varchar(10)
set @lBDO01 = %d
set @lACF01 = %d
SET @lSql = %s
SET @lBBX09 = %d   
SET @lBCE01 = %d
SET @lBCK01 = %d
set @abc02 = %s
set @bdp02 = %s
set @gparam60 = dbo.GetSysParamValue(9999,9999,60)
Set @lDate = Convert(varchar(10),GetDate(),21)+'' 23:59:59.999''
SELECT e.BDA01, e.BDA02,d.BBY08 NUnit, a.BBX01, a.BDO01, d.BBY04 as BBX04, d.BBY05 BBX05, d.BBY05
  , '''' NBBX13, 0 BAG27
  , case when @gparam60=''2'' then dbo.GetBBY1Price(d.BBY01,@abc02) else d.BBY25 end price 
  ,d.BBY29, f.AAS02 ,h.BCG02,d.BBY06, d.BBE02, d.BBY01,f.AAS07,d.BBY44  
FROM BBX1 a
JOIN BCT1 c ON c.BBX01 = a.BBX01
JOIN BBY1 d ON d.BBY01 = c.BBY01 
JOIN BDA1 e ON e.BDA01 = a.BDA01  
LEFT JOIN AAS1 f ON f.AAS01 = d.AAS01
left join BCG1 h on h.BCG01 = d.BCG01
WHERE @lACF01 = (d.ACF01 & @lACF01) and ((@lBDO01>-1 AND a.BDO01 = @lBDO01)OR(@lBDO01=-1))
  AND e.BDA01 in (''4'',''M'') and c.BCT33 = 1 AND d.BBY31 > @lDate  
  and ((d.BBY47 = 0)or(d.BBY47 = 1 and exists(select * from BNN1 c2 where c2.BBY01 = d.BBY01 and c2.BCK01 = @lBCK01)))
   and ((d.BBY49 = 0)or(d.BBY49 = 1 and exists(select * from BNR1 c3 where c3.BBY01 = d.BBY01 and c3.BDP02 = @bdp02)))
UNION ALL
SELECT e.BDA01, e.BDA02, a.BDG02 AS NUnit, a.BBX01, a.BDO01, a.BBX04, a.BBX05,a.BBX05 BBY05 
  , CASE WHEN a.BDA01 IN (''8'',''9'') THEN a.BBX06
         WHEN a.BDA01 = ''T'' THEN a1.AAF02
         WHEN a.BDA01 = ''E'' THEN a2.BBC02
         WHEN a.BDA01 = ''L'' THEN a3.AAV02
         WHEN a.BDA01 = ''S'' THEN a4.ACH02
         WHEN a.BDA01 = ''A'' THEN a5.ACI02         
         WHEN a.BDA01 = ''N'' THEN a6.AAG02
         WHEN a.BDA01 = ''Z'' THEN a7.AAP02
       ELSE NULL END NBBX13
  ,a.BBX12 as BAG27
  ,case when @gparam60=''2'' then dbo.GetBBX1Price(a.BBX01,@abc02) else vb.Price end price 
  ,a.BBX29,f.AAS02,'''' BCG02,'''' BBY06,'''' BBE02,a.BBX01 ,f.AAS07,A.BBX36        
FROM BBX1 a
JOIN BDA1 e ON a.BDA01 = e.BDA01
LEFT JOIN V_BDU vb ON a.BBX01 = vb.BBX01
LEFT JOIN AAS1 f ON f.AAS01 = a.AAS01
LEFT JOIN AAF1 a1 ON a.BDA01=''T'' AND a.BBX13=a1.AAF01
LEFT JOIN BBC1 a2 ON a.BDA01=''E''  AND a.BBX13=a2.BBC01
LEFT JOIN AAV1 a3 ON a.BDA01=''L'' AND a.BBX13=a3.AAV01
LEFT JOIN ACH1 a4 ON a.BDA01=''S'' AND a.bbx13=a4.ACH01
LEFT JOIN ACI1 a5 ON a.BDA01=''A'' AND a.BBX01=a5.ACI01 
LEFT JOIN AAP1 a7 ON a.BDA01=''Z'' AND a.BBX13=a7.AAP01 
LEFT JOIN AAG1 a6 ON a.BDA01=''N'' AND a.BBX13=CONVERT(varchar(10),a6.AAG01)
WHERE ((a.BBX11 =1)or(a.BBX11 = 0 and a.BBX12 in (1,3,4))) AND a.BBX09 IN (0,@lBBX09) AND a.BDA01 >''7'' and a.BDA01 <> ''M''
   AND a.BBX25 > @lDate and ((@lBDO01>-1 AND a.BDO01 = @lBDO01)OR(@lBDO01=-1))
   and ((a.BBX30 = 0)or(a.BBX30 = 1 and exists(select * from BIP1 c2 where c2.BBX01 = a.BBX01 and c2.BCK01 = @lBCK01)))
   and ((a.BBX46 = 0)or(a.BBX46 = 1 and exists(select * from BMP1 c3 where c3.BBX01 = a.BBX01 and c3.BDP02 = @bdp02)))
   AND ((a.BDA01 <> ''9'')OR((a.BDA01=''9'') AND ((a.BBX15=0 and a.BCE01 = @lBCE01)
    or a.BBX15=2 or (a.BBX15=1 and exists(select c.* from BAR1 c where a.BBX01=c.BBX01 and c.BCK01 =@lBCK01)))))
   and @lACF01 = (a.ACF01 & @lACF01) AND (a.BBX04 LIKE @lSql OR a.bbx36 LIKE @lSql OR
   (EXISTS(SELECT b.* FROM BDK1 b WHERE b.BBX01 = a.BBX01 
     AND (b.BDK03 LIKE @lSql OR b.ABBRP LIKE @lSql OR b.ABBRW LIKE @lSql))))', '2016-08-09 17:49:24', '(8058)高瑜', 0)
else
print 'SYS_Scripts.id=757 已经存在.'

GO


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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=1438)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(1438, 31, 'Sql_分类检索医嘱项目', '下达医嘱脚本', '按分类检索医嘱项目，树形左边列表', 1, 0, Null, 1, 'DECLARE @lBEH01 varchar(10),@BCE01 int,@BCK01 int
SET @lBEH01 = %s
set @BCE01 = %d
set @BCK01 = %d
SELECT DISTINCT
  a.BDO01 
  ,a.BDO03 AS BDO02
  ,PatientID = CASE WHEN a.BDO01A IS null THEN case when len(BDM01) <= 1 then -ASCII(BDM01) else -(ASCII(substring(BDM01,1,1)) + ASCII(substring(BDM01,2,1))) end ELSE a.BDO01A end
  ,a.BDM01
from BDO1 a  
  JOIN (SELECT distinct a1.BDO01,a1.BDO01A
		FROM BDO1 a1  
		  JOIN BBX1 b1 ON a1.BDO01=b1.BDO01
		  JOIN BDA1 c ON b1.BDA01=c.BDA01
		WHERE (c.BEH01= @lBEH01) and ((@lBEH01 <> ''9'') 
		  or (@lBEH01 = ''9'' and (b1.BBX15 = 2 or(b1.BBX15 = 0 and b1.BCE01 = @BCE01)
		    or (b1.BBX15 = 1 and Exists(Select * From BAR1 b Where b1.BBX01 = b.BBX01 And b.BCK01 = @BCK01)) )))) b ON a.BDO01 = b.BDO01 OR a.BDO01 = b.BDO01A
ORDER BY a.BDO01

', '2012-08-29 09:43:30', '(8058)高瑜', 0)
else
print 'SYS_Scripts.id=1438 已经存在.'

GO


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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=1439)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(1439, 31, 'Sql_分类医嘱明细', '下达医嘱脚本', '医嘱分类明细项目检索，按分类检索', 1, 0, Null, 1, 'DECLARE @lBDO01 int, @lACF01 int, @lSql varchar(100), @lBBX09 int,@abc02 varchar(20),@bdp02 varchar(20)
DECLARE @lBEH01 varchar(10), @lBCE01 int, @lBCK01 int, @lDate Datetime,@gparam60 varchar(10)
set @lBDO01 = %d
set @lACF01 = %d
SET @lSql = %s
SET @lBBX09 = %d  
SET @lBEH01 = %s   
SET @lBCE01 = %d
SET @lBCK01 = %d
set @abc02 = %s
set @bdp02 = %s
set @gparam60 = dbo.GetSysParamValue(9999,9999,60)
Set @lDate = Convert(varchar(10),Getdate(),21)+'' 23:59:59.999''
SELECT e.BDA01, e.BDA02,d.BBY08 NUnit, a.BBX01, a.BDO01, d.BBY04 as BBX04, d.BBY05 BBX05, d.BBY05, '''' NBBX13, 0 BAG27
  ,case when @gparam60=''2'' then dbo.GetBBY1Price(d.BBY01,@abc02) else d.BBY25 end price 
  ,d.BBY29, f.AAS02 ,h.BCG02,d.BBY06, d.BBE02, d.BBY01,f.AAS07,d.BBY44  
FROM BBX1 a
JOIN BCT1 c ON c.BBX01 = a.BBX01
JOIN BBY1 d ON d.BBY01 = c.BBY01 
JOIN BDA1 e ON e.BDA01 = a.BDA01  
LEFT JOIN AAS1 f ON f.AAS01 = d.AAS01
left join BCG1 h on h.BCG01 = d.BCG01
WHERE @lACF01 = (d.ACF01 & @lACF01) and a.BDO01 = @lBDO01 AND e.BEH01=@lBEH01 AND e.BDA01 in (''4'',''M'')
  and c.BCT33 = 1 AND d.BBY31 > @lDate
  and ((d.BBY47 = 0)or(d.BBY47 = 1 and exists(select * from BNN1 c2 where c2.BBY01 = d.BBY01 and c2.BCK01 = @lBCK01)))
   and ((d.BBY49 = 0)or(d.BBY49 = 1 and exists(select * from BNR1 c3 where c3.BBY01 = d.BBY01 and c3.BDP02 = @bdp02))) 
UNION ALL
SELECT e.BDA01, e.BDA02, a.BDG02 AS NUnit
  , a.BBX01, a.BDO01, a.BBX04, a.BBX05,a.BBX05 BBY05 
  , CASE WHEN a.BDA01 IN (''8'',''9'') THEN a.BBX06
         WHEN a.BDA01 = ''T'' THEN a1.AAF02
         WHEN a.BDA01 = ''E'' THEN a2.BBC02
         WHEN a.BDA01 = ''L'' THEN a3.AAV02
         WHEN a.BDA01 = ''S'' THEN a4.ACH02
         WHEN a.BDA01 = ''A'' THEN a5.ACI02         
         WHEN a.BDA01 = ''N'' THEN a6.AAG02
         WHEN a.BDA01 = ''Z'' THEN a7.AAP02
       ELSE NULL END NBBX13,a.BBX12 as BAG27
  ,case when @gparam60=''2'' then dbo.GetBBX1Price(a.BBX01,@abc02) else vb.Price end price 
  ,a.BBX29,f.AAS02,'''' BCG02,'''' BBY06,'''' BBE02,a.BBX01,f.AAS07,A.BBX36  
FROM BBX1 a
  JOIN BDA1 e ON a.BDA01 = e.BDA01
  LEFT JOIN V_BDU vb ON a.BBX01 = vb.BBX01
  LEFT JOIN AAS1 f ON f.AAS01 = a.AAS01
  LEFT JOIN AAF1 a1 ON a.BDA01=''T'' AND a.BBX13=a1.AAF01
  LEFT JOIN BBC1 a2 ON a.BDA01=''E''  AND a.BBX13=a2.BBC01
  LEFT JOIN AAV1 a3 ON a.BDA01=''L'' AND a.BBX13=a3.AAV01
  LEFT JOIN ACH1 a4 ON a.BDA01=''S'' AND a.bbx13=a4.ACH01
  LEFT JOIN ACI1 a5 ON a.BDA01=''A'' AND a.BBX01=a5.ACI01 
  LEFT JOIN AAP1 a7 ON a.BDA01=''Z'' AND a.BBX13=a7.AAP01 
  LEFT JOIN AAG1 a6 ON a.BDA01=''N'' AND a.BBX13=CONVERT(varchar(10),a6.AAG01)   
WHERE ((a.BBX11 =1)or(a.BBX11 = 0 and a.BBX12  in (1,3,4))) AND a.BBX09 IN (0,@lBBX09) 
   AND a.BBX25 > @lDate and a.BDO01 = @lBDO01 AND e.BEH01= @lBEH01 AND a.BDA01 >''7'' and a.BDA01 <> ''M''
   and ((a.BBX30 = 0)or(a.BBX30 = 1 and exists(select * from BIP1 c2 where c2.BBX01 = a.BBX01 and c2.BCK01 = @lBCK01)))
   and ((a.BBX46 = 0)or(a.BBX46 = 1 and exists(select * from BMP1 c3 where c3.BBX01 = a.BBX01 and c3.BDP02 = @bdp02)))
   AND ((a.BDA01 <> ''9'')OR((a.BDA01=''9'') AND ((a.BBX15=0 and a.BCE01 = @lBCE01)
    or a.BBX15=2 or (a.BBX15=1 and exists(select c.* from BAR1 c where a.BBX01=c.BBX01 and c.BCK01 =@lBCK01)))))   
   and @lACF01 = (a.ACF01 & @lACF01) AND (a.BBX04 LIKE @lSql OR a.bbx36 LIKE @lSql OR
   (EXISTS(SELECT b.* FROM BDK1 b WHERE b.BBX01 = a.BBX01 
     AND (b.BDK03 LIKE @lSql OR b.ABBRP LIKE @lSql OR b.ABBRW LIKE @lSql))))', '2016-08-09 17:50:03', '(8058)高瑜', 0)
else
print 'SYS_Scripts.id=1439 已经存在.'

GO


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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=1451)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(1451, 31, 'Sql_分类医嘱明细检索', '下达医嘱脚本', '医嘱分类明细项目检索，分类检索，按输入条件', 1, 0, Null, 1, 'DECLARE @lBDO01 int, @lACF01 int, @lSql varchar(100), @lBBX09 int,@abc02 varchar(20),@bdp02 varchar(20)
DECLARE @lBEH01 varchar(10), @lBCE01 int, @lBCK01 int, @lDate Datetime,@gparam60 varchar(10)
set @lBDO01 = %d
set @lACF01 = %d
SET @lSql = %s
SET @lBBX09 = %d  
SET @lBEH01 = %s  
SET @lBCE01 = %d
SET @lBCK01 = %d  
set @abc02 = %s
set @bdp02 = %s
set @gparam60 = dbo.GetSysParamValue(9999,9999,60)
Set @lDate = Convert(varchar(10),Getdate(),21)+'' 23:59:59.999'' 
SELECT e.BDA01, e.BDA02,d.BBY08 NUnit, a.BBX01, a.BDO01, d.BBY04 as BBX04, d.BBY05 BBX05, d.BBY05, '''' NBBX13, 0 BAG27
  ,case when @gparam60=''2'' then dbo.GetBBY1Price(d.BBY01,@abc02) else d.BBY25 end price 
  ,d.BBY29, f.AAS02 ,h.BCG02,d.BBY06, d.BBE02, d.BBY01 ,f.AAS07,d.BBY44 
FROM BBX1 a
JOIN BCT1 c ON c.BBX01 = a.BBX01
JOIN BBY1 d ON d.BBY01 = c.BBY01 
JOIN BDA1 e ON e.BDA01 = a.BDA01  
LEFT JOIN AAS1 f ON f.AAS01 = d.AAS01
left join BCG1 h on h.BCG01 = d.BCG01
WHERE @lACF01 = (d.ACF01 & @lACF01) AND e.BEH01=@lBEH01 AND e.BDA01 in (''4'',''M'') and c.BCT33 = 1 AND d.BBY31 > @lDate 
  and ((d.BBY47 = 0)or(d.BBY47 = 1 and exists(select * from BNN1 c2 where c2.BBY01 = d.BBY01 and c2.BCK01 = @lBCK01)))
   and ((d.BBY49 = 0)or(d.BBY49 = 1 and exists(select * from BNR1 c3 where c3.BBY01 = d.BBY01 and c3.BDP02 = @bdp02)))
UNION ALL
SELECT e.BDA01, e.BDA02, a.BDG02 AS NUnit, a.BBX01, a.BDO01, a.BBX04, a.BBX05,a.BBX05 BBY05 
  , CASE WHEN a.BDA01 IN (''8'',''9'') THEN a.BBX06
         WHEN a.BDA01 = ''T'' THEN a1.AAF02
         WHEN a.BDA01 = ''E'' THEN a2.BBC02
         WHEN a.BDA01 = ''L'' THEN a3.AAV02
         WHEN a.BDA01 = ''S'' THEN a4.ACH02
         WHEN a.BDA01 = ''A'' THEN a5.ACI02         
         WHEN a.BDA01 = ''N'' THEN a6.AAG02
         WHEN a.BDA01 = ''Z'' THEN a7.AAP02
       ELSE NULL END NBBX13,a.BBX12 as BAG27
  ,case when @gparam60=''2'' then dbo.GetBBX1Price(a.BBX01,@abc02) else vb.Price end price 
  ,a.BBX29,f.AAS02,'''' BCG02,'''' BBY06,'''' BBE02,a.BBX01,f.AAS07,A.BBX36
FROM BBX1 a
  JOIN BDA1 e ON a.BDA01 = e.BDA01
  LEFT JOIN V_BDU vb ON a.BBX01 = vb.BBX01
  LEFT JOIN AAS1 f ON f.AAS01 = a.AAS01
  LEFT JOIN AAF1 a1 ON a.BDA01=''T'' AND a.BBX13=a1.AAF01
  LEFT JOIN BBC1 a2 ON a.BDA01=''E''  AND a.BBX13=a2.BBC01
  LEFT JOIN AAV1 a3 ON a.BDA01=''L'' AND a.BBX13=a3.AAV01
  LEFT JOIN ACH1 a4 ON a.BDA01=''S'' AND a.bbx13=a4.ACH01
  LEFT JOIN ACI1 a5 ON a.BDA01=''A'' AND a.BBX01=a5.ACI01 
  LEFT JOIN AAP1 a7 ON a.BDA01=''Z'' AND a.BBX13=a7.AAP01 
  LEFT JOIN AAG1 a6 ON a.BDA01=''N'' AND a.BBX13=CONVERT(varchar(10),a6.AAG01)
WHERE ((a.BBX11 =1)or(a.BBX11 = 0 and a.BBX12  in (1,3,4))) AND a.BBX09 IN (0,@lBBX09) AND a.BDA01 >''7'' and a.BDA01 <> ''M''
   AND a.BBX25 > @lDate AND e.BEH01= @lBEH01 
   and ((a.BBX30 = 0)or(a.BBX30 = 1 and exists(select * from BIP1 c2 where c2.BBX01 = a.BBX01 and c2.BCK01 = @lBCK01)))
   and ((a.BBX46 = 0)or(a.BBX46 = 1 and exists(select * from BMP1 c3 where c3.BBX01 = a.BBX01 and c3.BDP02 = @bdp02)))
   AND ((a.BDA01 <> ''9'')OR((a.BDA01=''9'') AND ((a.BBX15=0 and a.BCE01 = @lBCE01)
    or a.BBX15=2 or (a.BBX15=1 and exists(select c.* from BAR1 c where a.BBX01=c.BBX01 and c.BCK01 =@lBCK01)))))
   and @lACF01 = (a.ACF01 & @lACF01) AND (a.BBX04 LIKE @lSql OR a.bbx36 LIKE @lSql OR
   (EXISTS(SELECT b.* FROM BDK1 b WHERE b.BBX01 = a.BBX01 
     AND (b.BDK03 LIKE @lSql OR b.ABBRP LIKE @lSql OR b.ABBRW LIKE @lSql))))', '2016-08-09 17:50:15', '(8058)高瑜', 0)
else
print 'SYS_Scripts.id=1451 已经存在.'

GO


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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=1455)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(1455, 31, 'Sql_表格检索分类医嘱', '下达医嘱脚本', '表格：按分类检索医嘱项目', 1, 0, Null, 1, 'DECLARE @lSqlText varchar(100), @lACF01 int, @lBBX09 int,@lBCK01 int,@lBCE01 int,@BCKyf int,@abc02 varchar(20),@bdp02 varchar(20)
DECLARE @lBEH01 varchar(10), @lDate Datetime, @aParamno varchar(10),@aParam1 varchar(10),@gparam60 varchar(10)
declare @vaf58 tinyint
SET @lSqlText = %s
SET @lACF01 = %d
SET @lBBX09 = %d
SET @lBEH01 = %s
SET @lBCK01 = %d
SET @lBCE01 = %d
set @BCKyf = %d
set @abc02 = %s
set @bdp02 = %s
set @vaf58 = %d
Set @lDate = Convert(varchar(10),Getdate(),21)+'' 23:59:59.999''
set @aParamno = dbo.GetSysParamValue(9999,9999,15)
set @aParam1 = dbo.GetSysParamValue(9999,9999,1)
set @gparam60 = dbo.GetSysParamValue(9999,9999,60)
declare @tmpBCK table(BCK01 int)
if object_id(''tempdb..#kbmbbj'') is not null drop table #kbmbbj
  if @aParamno = ''3'' 
  begin
    if @lACF01 = 1
    begin
      insert into @tmpBCK(BCK01)
      select @BCKyf
    end
    if @lACF01 = 2
    begin
      insert into @tmpBCK(BCK01)
      select BCK01
      from dbo.GetSearchDrugBck(@lBCK01)
    end
  end
  select a.BBY01,a.BCK01A,a.BCK01B into #kbmbbj 
  from BBJ1 a  
  where @aParamno = ''0'' and a.BCK01A = @lBCK01
    and exists(select *from baz1 b where b.BCK01 = a.BCK01B and @lACF01 = (b.ACF01 & @lACF01)  )

SELECT e.BDA01, e.BDA02, f.AAS02, a.BBX01, a.BDO01, d.BBY04 as BBX04, a.BBX05
  , d.BBY05+case when bag16>'''' then ''(''+bag16+'')'' else '''' end BBY05 , a.BDG02
  , (Isnull(b.BAP02,'''')+''、''+Isnull(c.BAG11,'''')+''、''+ case when ISNULL(f2.BHJ01,0)=0 then '''' else f2.BHJ02 end) as NBBX13
  , d.BBY06, d.BBE02, d.BBY01, c.BAG27
  , CASE WHEN @lACF01=1 THEN c.BDG02A ELSE c.BDG02B END AS NUnit
  , case when @gparam60=''2'' then dbo.GetDrugPrice(h1.BCK01 , d.BBY01 , @lACF01 , @abc02)
        else CASE WHEN @lACF01=1 THEN d.BBY25*c.BAG05 ELSE d.BBY25*c.BAG07 END end AS Price 
  , CASE WHEN @lACF01 = 1 THEN ISNULL(h1.LSQty,0)/c.BAG05 ELSE ISNULL(h1.LSQty,0)/c.BAG07 END AS amount
  ,h2.BCK03, f1.BCG02,d.BBY29  ,f.AAS07,isnull(h1.BCK01,0) BCK01,d.BBY44
FROM BBX1 a
  JOIN BBT1 b ON a.BBX01 = b.BBX01
  JOIN BAG1 c ON b.BBX01 = c.BBX01
  JOIN BBY1 d ON c.BBY01 = d.BBY01 
  JOIN BDA1 e ON a.BDA01 = e.BDA01
  JOIN #kbmbbj h ON d.BBY01 = h.BBY01
  left JOIN V_DPK6 h1 ON h.BBY01 = h1.BBY01 AND h.BCK01B = h1.BCK01 and @lACF01 = (h1.ACF01 & @lACF01)
  left join BCK1 h2 on h2.BCK01 = h1.BCK01
  LEFT JOIN AAS1 f ON f.AAS01 = d.AAS01
  left join BCG1 f1 on f1.BCG01 = d.BCG01
  left join BHJ1 f2 on f2.BHJ01 = b.BBT08
WHERE @aParamno =''0'' and ((@aParam1 = ''1'') or (@aParam1 <> ''1'' and ((@vaf58 <>1 and ISNULL(h1.LSQty,0)>0) or @vaf58=1)))
  and @lACF01 = (d.ACF01 & @lACF01) And a.BDA01<''4''
  And d.BBY31 > @lDate AND e.BEH01=@lBEH01  
  and ((d.BBY47 = 0)or(d.BBY47 = 1 and exists(select * from BNN1 c2 where c2.BBY01 = d.BBY01 and c2.BCK01 = @lBCK01)))
   and ((d.BBY49 = 0)or(d.BBY49 = 1 and exists(select * from BNR1 c3 where c3.BBY01 = d.BBY01 and c3.BDP02 = @bdp02)))
  and (d.BBY04 LIKE @lSqlText OR d.BBY44 LIKE @lSqlText 
  OR (EXISTS(SELECT g.* FROM BDK1 g WHERE g.BBX01 = a.BBX01
    AND (g.BDK03 LIKE @lSqlText OR g.ABBRP LIKE @lSqlText OR g.ABBRW LIKE @lSqlText)))) 
UNION ALL
SELECT e.BDA01, e.BDA02, f.AAS02, a.BBX01, a.BDO01, d.BBY04 as BBX04, a.BBX05
  , d.BBY05+case when bag16>'''' then ''(''+bag16+'')'' else '''' end BBY05, a.BDG02
  , (Isnull(b.BAP02,'''')+''、''+Isnull(c.BAG11,'''')+''、''+ case when ISNULL(f2.BHJ01,0)=0 then '''' else f2.BHJ02 end) as NBBX13
  , d.BBY06, d.BBE02, d.BBY01, c.BAG27
  , CASE WHEN @lACF01=1 THEN c.BDG02A ELSE c.BDG02B END AS NUnit
  , case when @gparam60=''2'' then dbo.GetDrugPrice(h1.BCK01 , d.BBY01 , @lACF01 , @abc02)
        else CASE WHEN @lACF01=1 THEN d.BBY25*c.BAG05 ELSE d.BBY25*c.BAG07 END end AS Price   
  , CASE WHEN @lACF01 = 1 THEN ISNULL(h1.LSQty,0)/c.BAG05 ELSE ISNULL(h1.LSQty,0)/c.BAG07 END AS amount
  ,case when isnull(h1.BCK01,0)=0 then '''' else h2.BCK03 end BCK03, f1.BCG02,d.BBY29 ,f.AAS07,isnull(h1.BCK01,0) BCK01,d.BBY44
FROM BBX1 a
  JOIN BBT1 b ON a.BBX01 = b.BBX01
  JOIN BAG1 c ON b.BBX01 = c.BBX01
  JOIN BBY1 d ON c.BBY01 = d.BBY01 
  JOIN BDA1 e ON a.BDA01 = e.BDA01
  left JOIN V_DPK6 h1 ON h1.BBY01 = d.BBY01 and (@aParamno <> ''3'' or (@aParamno = ''3'' and exists(select * from @tmpBCK h3 where h3.BCK01 = h1.BCK01))) 
  left join BCK1 h2 on h2.BCK01 = h1.BCK01
  LEFT JOIN AAS1 f ON f.AAS01 = d.AAS01
  left join BCG1 f1 on f1.BCG01 = d.BCG01
  left join BHJ1 f2 on f2.BHJ01 = b.BBT08
WHERE @aParamno <> ''0'' and @lACF01 = (d.ACF01 & @lACF01) AND a.BDA01<''4''
  and ((@aParam1 = ''1'') or (@aParam1 <> ''1'' and ((@vaf58 <>1 and ISNULL(h1.LSQty,0)>0) or @vaf58=1)))
  And d.BBY31 > @lDate AND e.BEH01=@lBEH01
  and ((d.BBY47 = 0)or(d.BBY47 = 1 and exists(select * from BNN1 c2 where c2.BBY01 = d.BBY01 and c2.BCK01 = @lBCK01)))
   and ((d.BBY49 = 0)or(d.BBY49 = 1 and exists(select * from BNR1 c3 where c3.BBY01 = d.BBY01 and c3.BDP02 = @bdp02)))
  and (d.BBY04 LIKE @lSqlText OR d.BBY44 LIKE @lSqlText 
  OR (EXISTS(SELECT g.* FROM BDK1 g WHERE g.BBX01 = a.BBX01
    AND (g.BDK03 LIKE @lSqlText OR g.ABBRP LIKE @lSqlText OR g.ABBRW LIKE @lSqlText)))) 
union all
SELECT e.BDA01, e.BDA02, f.AAS02, a.BBX01, a.BDO01, d.BBY04 as BBX04, d.BBY05 BBX05, d.BBY05
  , a.BDG02, '''' NBBX13, d.BBY06, d.BBE02, d.BBY01, 0 BAG27, d.BBY08 NUnit
  ,case when @gparam60=''2'' then dbo.GetBBY1Price(d.BBY01,@abc02) else d.BBY25 end price  
  , NULL AS amount,'''' BCK03, h.BCG02,d.BBY29,f.AAS07,0 ,d.BBY44
FROM BBX1 a
JOIN BCT1 c ON c.BBX01 = a.BBX01
JOIN BBY1 d ON d.BBY01 = c.BBY01 
JOIN BDA1 e ON e.BDA01 = a.BDA01  
LEFT JOIN AAS1 f ON f.AAS01 = d.AAS01
left join BCG1 h on h.BCG01 = d.BCG01
WHERE @lACF01 = (d.ACF01 & @lACF01) AND e.BEH01=@lBEH01 AND e.BDA01 in (''4'',''M'') and c.BCT33 = 1 AND d.BBY31 > @lDate
  and ((d.BBY47 = 0)or(d.BBY47 = 1 and exists(select * from BNN1 c2 where c2.BBY01 = d.BBY01 and c2.BCK01 = @lBCK01)))
   and ((d.BBY49 = 0)or(d.BBY49 = 1 and exists(select * from BNR1 c3 where c3.BBY01 = d.BBY01 and c3.BDP02 = @bdp02)))
  and (d.BBY04 LIKE @lSqlText OR d.BBY44 LIKE @lSqlText 
  OR (EXISTS(SELECT g.* FROM BCL1 g WHERE g.BBY01 = d.BBY01
    AND (g.BCL03 LIKE @lSqlText OR g.ABBRP LIKE @lSqlText OR g.ABBRW LIKE @lSqlText))))       
UNION ALL
SELECT e.BDA01,e.BDA02,f.AAS02
  ,a.BBX01,a.BDO01,a.BBX04,a.BBX05,a.BBX05,a.BDG02
  ,CASE WHEN a.BDA01 IN (''8'',''9'') THEN a.BBX06
         WHEN a.BDA01 = ''T'' THEN a1.AAF02
         WHEN a.BDA01 = ''E'' THEN a2.BBC02
         WHEN a.BDA01 = ''L'' THEN a3.AAV02
         WHEN a.BDA01 = ''S'' THEN a4.ACH02
         WHEN a.BDA01 = ''A'' THEN a5.ACI02         
         WHEN a.BDA01 = ''N'' THEN a6.AAG02
         WHEN a.BDA01 = ''Z'' THEN a7.AAP02
       ELSE NULL END NBBX13 
  ,a.BBX06, NULL AS BBE02,a.BBX01,a.BBX12,a.BDG02 AS NUnit
  ,case when @gparam60=''2'' then dbo.GetBBX1Price(a.BBX01,@abc02) else vb.Price end price,NULL 
  ,'''','''' ,a.BBX29  ,f.AAS07,0,A.BBX36
FROM BBX1 a
  JOIN BDA1 e ON a.BDA01 = e.BDA01
  LEFT JOIN V_BDU vb ON a.BBX01 = vb.BBX01
  LEFT JOIN AAS1 f ON f.AAS01 = a.AAS01
  LEFT JOIN AAF1 a1 ON a.BDA01=''T'' AND a.BBX13=a1.AAF01
  LEFT JOIN BBC1 a2 ON a.BDA01=''E''  AND a.BBX13=a2.BBC01
  LEFT JOIN AAV1 a3 ON a.BDA01=''L'' AND a.BBX13=a3.AAV01
  LEFT JOIN ACH1 a4 ON a.BDA01=''S'' AND a.bbx13=a4.ACH01
  LEFT JOIN ACI1 a5 ON a.BDA01=''A'' AND a.BBX01=a5.ACI01 
  LEFT JOIN AAP1 a7 ON a.BDA01=''Z'' AND a.BBX13=a7.AAP01 
  LEFT JOIN AAG1 a6 ON a.BDA01=''N'' AND a.BBX13=CONVERT(varchar(10),a6.AAG01)  
WHERE e.BEH01=@lBEH01 AND a.BDA01 >''7'' and a.BDA01 <> ''M'' 
 and ((a.BBX11 =1)or(a.BBX11 = 0 and a.BBX12  in (1,3,4))) AND a.BBX09 IN (0,@lBBX09) And a.BBX25 > @lDate
 and ((a.BBX30 = 0)or(a.BBX30 = 1 and exists(select * from BIP1 c2 where c2.BBX01 = a.BBX01 and c2.BCK01 = @lBCK01)))
 and ((a.BBX46 = 0)or(a.BBX46 = 1 and exists(select * from BMP1 c3 where c3.BBX01 = a.BBX01 and c3.BDP02 = @bdp02)))
 AND ((a.BDA01 <> ''9'')OR((a.BDA01=''9'') AND ((a.BBX15=0 and a.BCE01 = @lBCE01)
    or a.BBX15=2 or (a.BBX15=1 and exists(select c.* from BAR1 c where a.BBX01=c.BBX01 and c.BCK01 =@lBCK01)))))
 AND @lACF01 = (a.ACF01 & @lACF01) and (a.BBX04 LIKE @lSqlText OR a.bbx36 LIKE @lSqlText 
  OR (EXISTS(SELECT g.* FROM BDK1 g WHERE g.BBX01 = a.BBX01
    AND (g.BDK03 LIKE @lSqlText OR g.ABBRP LIKE @lSqlText OR g.ABBRW LIKE @lSqlText))))  
order by e.BDA01,a.BBX01

if object_id(''tempdb..#kbmbbj'') is not null drop table #kbmbbj', '2016-08-09 17:50:43', '(8058)高瑜', 0)
else
print 'SYS_Scripts.id=1455 已经存在.'

GO


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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=1648)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(1648, 31, 'Sql_表格方案医嘱项目', '下达医嘱脚本', '医嘱表格项目，用于治疗方案维护', 1, 0, Null, 1, 'DECLARE @lSqlText varchar(100), @lACF01 int, @lBBX09 int
Declare @lDate DateTime
SET @lSqlText = %s
SET @lACF01 = %d
SET @lBBX09 = %d  
Set @lDate = Convert(varchar(10),GetDate(),21)+'' 23:59:59.999''
SELECT e.BDA01, e.BDA02, f.AAS02, a.BBX01, a.BDO01, d.BBY04 as BBX04, a.BBX05, d.BBY05, a.BDG02
  , (Isnull(b.BAP02,'''')+''、''+Isnull(c.BAG11,'''')+''、''+ case when ISNULL(f2.BHJ01,0)=0 then '''' else f2.BHJ02 end) as NBBX13
  , d.BBY06, d.BBE02, d.BBY01, c.BAG27
  , CASE WHEN @lACF01=1 THEN c.BDG02A ELSE c.BDG02B END AS NUnit
  , CASE WHEN @lACF01=1 THEN d.BBY25*c.BAG05 ELSE d.BBY25*c.BAG07 END AS Price  
  , NULL AS amount, h.BCG02,d.BBY29,f.AAS07,d.BBY44
FROM BBX1 a
  JOIN BBT1 b ON a.BBX01 = b.BBX01
  JOIN BAG1 c ON b.BBX01 = c.BBX01
  JOIN BBY1 d ON c.BBY01 = d.BBY01 
  JOIN BDA1 e ON a.BDA01 = e.BDA01  
  LEFT JOIN AAS1 f ON f.AAS01 = d.AAS01
  left join BCG1 h on h.BCG01 = d.BCG01
  left join BHJ1 f2 on f2.BHJ01 = b.BBT08
WHERE @lACF01 = (d.ACF01 & @lACF01) AND a.BDA01 < ''4'' AND d.BBY31 > @lDate
  and (d.BBY04 LIKE @lSqlText OR d.BBY44 LIKE @lSqlText 
  OR (EXISTS(SELECT g.* FROM BDK1 g WHERE g.BBX01 = a.BBX01
    AND (g.BDK03 LIKE @lSqlText OR g.ABBRP LIKE @lSqlText OR g.ABBRW LIKE @lSqlText)))) 
union all
SELECT e.BDA01, e.BDA02, f.AAS02
  , a.BBX01, a.BDO01, d.BBY04 as BBX04, d.BBY05 BBX05, d.BBY05
  , a.BDG02, '''' NBBX13, d.BBY06, d.BBE02, d.BBY01, 0 BAG27
  , d.BBY08 NUnit, d.BBY25 Price , NULL AS amount, h.BCG02,d.BBY29,f.AAS07,d.BBY44
FROM BBX1 a
JOIN BCT1 c ON c.BBX01 = a.BBX01
JOIN BBY1 d ON d.BBY01 = c.BBY01 
JOIN BDA1 e ON e.BDA01 = a.BDA01  
LEFT JOIN AAS1 f ON f.AAS01 = d.AAS01
left join BCG1 h on h.BCG01 = d.BCG01
WHERE @lACF01 = (d.ACF01 & @lACF01) AND e.BDA01 in (''4'',''M'') and c.BCT33 = 1 AND d.BBY31 > @lDate
  and (d.BBY04 LIKE @lSqlText OR d.BBY44 LIKE @lSqlText 
  OR (EXISTS(SELECT g.* FROM BCL1 g WHERE g.BBY01 = d.BBY01
    AND (g.BCL03 LIKE @lSqlText OR g.ABBRP LIKE @lSqlText OR g.ABBRW LIKE @lSqlText))))     
UNION ALL 
SELECT e.BDA01,e.BDA02,f.AAS02
  ,a.BBX01,a.BDO01,a.BBX04,a.BBX05,a.BBX05,a.BDG02
  ,CASE WHEN a.BDA01 IN (''8'',''9'') THEN a.BBX06
         WHEN a.BDA01 = ''T'' THEN a1.AAF02
         WHEN a.BDA01 = ''E'' THEN a2.BBC02
         WHEN a.BDA01 = ''L'' THEN a3.AAV02
         WHEN a.BDA01 = ''S'' THEN a4.ACH02
         WHEN a.BDA01 = ''A'' THEN a5.ACI02         
         WHEN a.BDA01 = ''N'' THEN a6.AAG02
         WHEN a.BDA01 = ''Z'' THEN a7.AAP02
       ELSE NULL END NBBX13 
  ,a.BBX06, NULL AS BBE02,a.BBX01,a.BBX12,a.BDG02 AS NUnit,vb.Price
  ,NULL,'''',a.BBX29 ,f.AAS07,A.BBX36
FROM BBX1 a
  JOIN BDA1 e ON a.BDA01 = e.BDA01
  LEFT JOIN V_BDU vb ON a.BBX01 = vb.BBX01
  LEFT JOIN AAS1 f ON f.AAS01 = a.AAS01
  LEFT JOIN AAF1 a1 ON a.BDA01=''T'' AND a.BBX13=a1.AAF01
  LEFT JOIN BBC1 a2 ON a.BDA01=''E''  AND a.BBX13=a2.BBC01
  LEFT JOIN AAV1 a3 ON a.BDA01=''L'' AND a.BBX13=a3.AAV01
  LEFT JOIN ACH1 a4 ON a.BDA01=''S'' AND a.bbx13=a4.ACH01
  LEFT JOIN ACI1 a5 ON a.BDA01=''A'' AND a.BBX01=a5.ACI01 
  LEFT JOIN AAP1 a7 ON a.BDA01=''Z'' AND a.BBX13=a7.AAP01 
  LEFT JOIN AAG1 a6 ON a.BDA01=''N'' AND a.BBX13=CONVERT(varchar(10),a6.AAG01)  
WHERE e.BDA01 >''7'' and e.BDA01 <> ''M'' and ((a.BBX11 =1)or(a.BBX11 = 0 and a.BBX12  in (1,3,4))) AND a.BBX09 IN (0,@lBBX09)
 AND a.BBX25 > @lDate AND @lACF01 = (a.ACF01 & @lACF01) and (a.BBX04 LIKE @lSqlText OR a.bbx36 LIKE @lSqlText 
  OR (EXISTS(SELECT g.* FROM BDK1 g WHERE g.BBX01 = a.BBX01
    AND (g.BDK03 LIKE @lSqlText OR g.ABBRP LIKE @lSqlText OR g.ABBRW LIKE @lSqlText))))  
order by e.BDA01,a.BBX01', '2015-10-29 15:27:59', '(8058)高瑜', 0)
else
print 'SYS_Scripts.id=1648 已经存在.'

GO


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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=1649)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(1649, 31, 'Sql_表格分类方案项目', '下达医嘱脚本', '分类表格，用于治疗方案维护医嘱项目检索', 1, 0, Null, 1, 'DECLARE @lSqlText varchar(100), @lACF01 int, @lBBX09 int
DECLARE @lBEH01 varchar(10), @lDate DateTime
SET @lSqlText = %s
SET @lACF01 = %d
SET @lBBX09 = %d
SET @lBEH01 = %s
Set @lDate = Convert(varchar(10),GetDate(),21)+'' 23:59:59.999''
SELECT e.BDA01, e.BDA02, f.AAS02, a.BBX01, a.BDO01, d.BBY04 as BBX04, a.BBX05, d.BBY05, a.BDG02
  , (Isnull(b.BAP02,'''')+''、''+Isnull(c.BAG11,'''')+''、''+ case when ISNULL(f2.BHJ01,0)=0 then '''' else f2.BHJ02 end) as NBBX13
  , d.BBY06, d.BBE02, d.BBY01, c.BAG27
  , CASE WHEN @lACF01=1 THEN c.BDG02A ELSE c.BDG02B END AS NUnit
  , CASE WHEN @lACF01=1 THEN d.BBY25*c.BAG05 ELSE d.BBY25*c.BAG07 END AS Price
  , NULL AS amount, h.BCG02,d.BBY29,f.AAS07,d.BBY44
FROM BBX1 a
  JOIN BBT1 b ON a.BBX01 = b.BBX01
  JOIN BAG1 c ON b.BBX01 = c.BBX01
  JOIN BBY1 d ON c.BBY01 = d.BBY01 
  JOIN BDA1 e ON a.BDA01 = e.BDA01  
  LEFT JOIN AAS1 f ON f.AAS01 = d.AAS01
  left join BCG1 h on h.BCG01 = d.BCG01
  left join BHJ1 f2 on f2.BHJ01 = b.BBT08
WHERE @lACF01 = (d.ACF01 & @lACF01) And a.BDA01<''4'' AND d.BBY31 > @lDate
  AND e.BEH01=@lBEH01 and (d.BBY04 LIKE @lSqlText OR d.BBY44 LIKE @lSqlText 
  OR (EXISTS(SELECT g.* FROM BDK1 g WHERE g.BBX01 = a.BBX01
    AND (g.BDK03 LIKE @lSqlText OR g.ABBRP LIKE @lSqlText OR g.ABBRW LIKE @lSqlText)))) 
union all
SELECT e.BDA01, e.BDA02, f.AAS02
  , a.BBX01, a.BDO01, d.BBY04 as BBX04, d.BBY05 BBX05, d.BBY05
  , a.BDG02, '''' NBBX13, d.BBY06, d.BBE02, d.BBY01, 0 BAG27
  , d.BBY08 NUnit, d.BBY25 Price , NULL AS amount, h.BCG02,d.BBY29 ,f.AAS07 ,d.BBY44
FROM BBX1 a
JOIN BCT1 c ON c.BBX01 = a.BBX01
JOIN BBY1 d ON d.BBY01 = c.BBY01 
JOIN BDA1 e ON e.BDA01 = a.BDA01  
LEFT JOIN AAS1 f ON f.AAS01 = d.AAS01
left join BCG1 h on h.BCG01 = d.BCG01
WHERE @lACF01 = (d.ACF01 & @lACF01) AND e.BEH01=@lBEH01 AND e.BDA01 in (''4'',''M'') and c.BCT33 = 1 AND d.BBY31 > @lDate
  and (d.BBY04 LIKE @lSqlText OR d.BBY44 LIKE @lSqlText 
  OR (EXISTS(SELECT g.* FROM BCL1 g WHERE g.BBY01 = d.BBY01
    AND (g.BCL03 LIKE @lSqlText OR g.ABBRP LIKE @lSqlText OR g.ABBRW LIKE @lSqlText))))       
UNION ALL
SELECT e.BDA01,e.BDA02,f.AAS02
  ,a.BBX01,a.BDO01,a.BBX04,a.BBX05,a.BBX05,a.BDG02
  ,CASE WHEN a.BDA01 IN (''8'',''9'') THEN a.BBX06
         WHEN a.BDA01 = ''T'' THEN a1.AAF02
         WHEN a.BDA01 = ''E'' THEN a2.BBC02
         WHEN a.BDA01 = ''L'' THEN a3.AAV02
         WHEN a.BDA01 = ''S'' THEN a4.ACH02
         WHEN a.BDA01 = ''A'' THEN a5.ACI02         
         WHEN a.BDA01 = ''N'' THEN a6.AAG02
         WHEN a.BDA01 = ''Z'' THEN a7.AAP02
       ELSE NULL END NBBX13 
  ,a.BBX06, NULL AS BBE02,a.BBX01,a.BBX12,a.BDG02 AS NUnit,vb.Price,NULL,'''' 
  ,a.BBX29 ,f.AAS07,A.BBX36
FROM BBX1 a
  JOIN BDA1 e ON a.BDA01 = e.BDA01
  LEFT JOIN V_BDU vb ON a.BBX01 = vb.BBX01
  LEFT JOIN AAS1 f ON f.AAS01 = a.AAS01
  LEFT JOIN AAF1 a1 ON a.BDA01=''T'' AND a.BBX13=a1.AAF01
  LEFT JOIN BBC1 a2 ON a.BDA01=''E''  AND a.BBX13=a2.BBC01
  LEFT JOIN AAV1 a3 ON a.BDA01=''L'' AND a.BBX13=a3.AAV01
  LEFT JOIN ACH1 a4 ON a.BDA01=''S'' AND a.bbx13=a4.ACH01
  LEFT JOIN ACI1 a5 ON a.BDA01=''A'' AND a.BBX01=a5.ACI01 
  LEFT JOIN AAP1 a7 ON a.BDA01=''Z'' AND a.BBX13=a7.AAP01 
  LEFT JOIN AAG1 a6 ON a.BDA01=''N'' AND a.BBX13=CONVERT(varchar(10),a6.AAG01)  
WHERE e.BEH01=@lBEH01 AND e.BDA01 >''7'' and e.BDA01 <> ''M'' AND a.BBX25 > @lDate 
 and ((a.BBX11 =1)or(a.BBX11 = 0 and a.BBX12 in (1,3,4))) AND a.BBX09 IN (0,@lBBX09)   
 AND @lACF01 = (a.ACF01 & @lACF01) and (a.BBX04 LIKE @lSqlText OR a.bbx36 LIKE @lSqlText 
  OR (EXISTS(SELECT g.* FROM BDK1 g WHERE g.BBX01 = a.BBX01
    AND (g.BDK03 LIKE @lSqlText OR g.ABBRP LIKE @lSqlText OR g.ABBRW LIKE @lSqlText))))  
order by e.BDA01,a.BBX01', '2015-10-29 15:28:29', '(8058)高瑜', 0)
else
print 'SYS_Scripts.id=1649 已经存在.'

GO


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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=1650)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(1650, 31, 'Sql_方案树形药品检索', '下达医嘱脚本', '树形：用于治疗方案检索药品', 1, 0, Null, 1, 'DECLARE @lACF01 int, @lBDO01 int, @lSqlText varchar(100)
DECLARE @lBDA01 varchar(10), @lDate Datetime
SET @lACF01 = %d
SET @lBDO01 = %d
SET @lSqlText = %s
SET @lBDA01 = %s
Set @lDate = Convert(varchar(10),Getdate(),21)+'' 23:59:59.999''
SELECT e.BDA01, e.BDA02,f.AAS02, a.BBX01, a.BDO01, d.BBY04 as BBX04, a.BBX05, d.BBY05, a.BDG02
  , (Isnull(b.BAP02,'''')+''、''+Isnull(c.BAG11,'''')+''、''+ case when ISNULL(f2.BHJ01,0)=0 then '''' else f2.BHJ02 end) as NBBX13
  , b.BBT10, b.BBT11, d.BBY06, d.BBE02, d.BBY01,d.BBY08,c.BAG27
  , CASE WHEN @lACF01=1 THEN c.BDG02A ELSE c.BDG02B END AS NUnit
  , CASE WHEN @lACF01=1 THEN d.BBY25*c.BAG05 ELSE d.BBY25*c.BAG07 END AS Price
  , NULL AS amount , f1.BCG02,d.BBY29,f.AAS07 ,d.BBY44
FROM BBX1 a
  JOIN BBT1 b ON a.BBX01 = b.BBX01
  JOIN BAG1 c ON b.BBX01 = c.BBX01
  JOIN BBY1 d ON c.BBY01 = d.BBY01 
  JOIN BDA1 e ON a.BDA01 = e.BDA01  
  LEFT JOIN AAS1 f ON f.AAS01 = d.AAS01
  left join BCG1 f1 on f1.BCG01 = d.BCG01
  left join BHJ1 f2 on f2.BHJ01 = b.BBT08
WHERE ((@lBDO01>-1 AND a.BDO01 = @lBDO01)OR(@lBDO01=-1 AND (@lBDA01 = ''-1'' or e.BEH01 = @lBDA01)))
  AND a.BDA01 < ''4'' And d.BBY31 > @lDate
  AND @lACF01 = (d.ACF01 & @lACF01) AND (d.BBY04 LIKE @lSqlText OR d.BBY44 LIKE @lSqlText 
  OR (EXISTS(SELECT g.* FROM BDK1 g WHERE g.BBX01 = a.BBX01
    AND (g.BDK03 LIKE @lSqlText OR g.ABBRP LIKE @lSqlText OR g.ABBRW LIKE @lSqlText))))', '2014-01-20 15:24:39', '(8058)高瑜', 0)
else
print 'SYS_Scripts.id=1650 已经存在.'

GO


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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=1767)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(1767, 31, 'Sql_草药医嘱树形列表', '下达医嘱脚本', '草药医嘱左边树形列表', 1, 0, Null, 1, 'SELECT -cast(BDM01 AS int)AS BDO01,BDM02 AS BDO02,0 AS PatientID, BDM01 
FROM BDM1
WHERE BDM01 = ''3''
UNION all
select
  BDO01 
  ,BDO03
  ,PatientID = CASE WHEN BDO01A IS null THEN -cast(BDM01 AS int) ELSE BDO01A end
  ,BDM01
from
  BDO1
WHERE BDM01 = ''3''', '2012-08-28 18:00:09', '(8058)高瑜', 0)
else
print 'SYS_Scripts.id=1767 已经存在.'

GO


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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=1768)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(1768, 31, 'Sql_表格草药医嘱检索', '下达医嘱脚本', '表格：草药医嘱检索', 1, 0, Null, 1, 'DECLARE @lSqlText varchar(100), @lACF01 int,@lBCK01 int,@BCKyf int,@abc02 varchar(20),@bdp02 varchar(20)
Declare @lDate Datetime, @aParamno varchar(10),@aParam1 varchar(10),@gparam60 varchar(10)
SET @lSqlText = %s
SET @lACF01 = %d
SET @lBCK01 = %d
set @BCKyf = %d
set @abc02 = %s
set @bdp02 = %s
Set @lDate = Convert(varchar(10),GetDate(),21)+'' 23:59:59.999''
set @aParamno = dbo.GetSysParamValue(9999,9999,15)
set @aParam1 = dbo.GetSysParamValue(9999,9999,1)
set @gparam60 = dbo.GetSysParamValue(9999,9999,60)
declare @tmpBCK table(BCK01 int)
if @aParamno <> ''0''
begin
  if @aParamno = ''3'' 
  begin
    if @lACF01 = 1
    begin
      insert into @tmpBCK(BCK01)
      select @BCKyf
    end
    if @lACF01 = 2
    begin
      insert into @tmpBCK(BCK01)
      select BCK01
      from dbo.GetSearchDrugBck(@lBCK01)
    end
  end
  SELECT e.BDA01, e.BDA02, f.AAS02
    , a.BBX01, a.BDO01, d.BBY04 as BBX04, a.BBX05, d.BBY05
    , a.BDG02, b.BAP02 AS NBBX13, d.BBY06, d.BBE02, d.BBY01, c.BAG27
    , CASE WHEN @lACF01=1 THEN c.BDG02A ELSE c.BDG02B END AS NUnit
    , case when @gparam60=''2'' then dbo.GetDrugPrice(h1.BCK01 , d.BBY01 , @lACF01 , @abc02)
        else CASE WHEN @lACF01=1 THEN d.BBY25*c.BAG05 ELSE d.BBY25*c.BAG07 END end AS Price  
    , CASE WHEN @lACF01 = 1 THEN ISNULL(h1.LSQty,0)/c.BAG05 ELSE ISNULL(h1.LSQty,0)/c.BAG07 END AS amount
    ,case when isnull(h1.BCK01,0)=0 then '''' else h2.BCK03 end BCK03 , f1.BCG02,d.BBY29,f.AAS07,d.BBY44
  FROM BBX1 a
  JOIN BBT1 b ON a.BBX01 = b.BBX01
  JOIN BAG1 c ON b.BBX01 = c.BBX01
  JOIN BBY1 d ON c.BBY01 = d.BBY01 
  JOIN BDA1 e ON a.BDA01 = e.BDA01
  left JOIN V_DPK6 h1 ON h1.BBY01 = d.BBY01 and (@aParamno <> ''3'' or (@aParamno = ''3'' and exists(select * from @tmpBCK h3 where h3.BCK01 = h1.BCK01))) 
  left join BCK1 h2 on h2.BCK01 = h1.BCK01
  LEFT JOIN AAS1 f ON f.AAS01 = d.AAS01
  left join BCG1 f1 on f1.BCG01 = d.BCG01  
  WHERE @lACF01 = (d.ACF01 & @lACF01) AND a.BDA01 = ''3'' AND d.BBY31 > @lDate
    and ((@aParam1 = ''1'') or (@aParam1 <> ''1'' and ISNULL(h1.LSQty,0)>0))
    and ((d.BBY47 = 0)or(d.BBY47 = 1 and exists(select * from BNN1 c2 where c2.BBY01 = d.BBY01 and c2.BCK01 = @lBCK01)))
   and ((d.BBY49 = 0)or(d.BBY49 = 1 and exists(select * from BNR1 c3 where c3.BBY01 = d.BBY01 and c3.BDP02 = @bdp02)))
    and (d.BBY04 LIKE @lSqlText OR d.BBY44 LIKE @lSqlText 
  OR (EXISTS(SELECT g.* FROM BDK1 g WHERE g.BBX01 = a.BBX01
    AND (g.BDK03 LIKE @lSqlText OR g.ABBRP LIKE @lSqlText OR g.ABBRW LIKE @lSqlText)))) 
  order by e.BDA01,a.BBX01
end
else
begin
  SELECT e.BDA01, e.BDA02, f.AAS02
    , a.BBX01, a.BDO01, d.BBY04 as BBX04, a.BBX05, d.BBY05
    , a.BDG02, b.BAP02 AS NBBX13, d.BBY06, d.BBE02, d.BBY01, c.BAG27
    , CASE WHEN @lACF01=1 THEN c.BDG02A ELSE c.BDG02B END AS NUnit
    , case when @gparam60=''2'' then dbo.GetDrugPrice(h1.BCK01 , d.BBY01 , @lACF01 , @abc02)
         else CASE WHEN @lACF01=1 THEN d.BBY25*c.BAG05 ELSE d.BBY25*c.BAG07 END end AS Price  
    , CASE WHEN @lACF01 = 1 THEN ISNULL(h1.LSQty,0)/c.BAG05 ELSE ISNULL(h1.LSQty,0)/c.BAG07 END AS amount
    ,h2.BCK03 , f1.BCG02,d.BBY29 ,f.AAS07,d.BBY44
  FROM BBX1 a
  JOIN BBT1 b ON a.BBX01 = b.BBX01
  JOIN BAG1 c ON b.BBX01 = c.BBX01
  JOIN BBY1 d ON c.BBY01 = d.BBY01 
  JOIN BDA1 e ON a.BDA01 = e.BDA01
  JOIN BBJ1 h ON d.BBY01 = h.BBY01
  left JOIN V_DPK6 h1 ON h1.BBY01 = h.BBY01 AND h.BCK01B = h1.BCK01 and h1.ACF01 in (3,@lACF01)
  left join BCK1 h2 on h2.BCK01 = h1.BCK01
  LEFT JOIN AAS1 f ON f.AAS01 = d.AAS01
  left join BCG1 f1 on f1.BCG01 = d.BCG01
  WHERE @lACF01 = (d.ACF01 & @lACF01) AND a.BDA01 = ''3'' AND h.BCK01A = @lBCK01 AND d.BBY31 > @lDate
    and ((@aParam1 = ''1'') or (@aParam1 <> ''1'' and ISNULL(h1.LSQty,0)>0))
    and exists(select * from BAZ1 h3 where h3.BCK01 = h.BCK01B and h3.ACF01 in (3,@lACF01))
    and ((d.BBY47 = 0)or(d.BBY47 = 1 and exists(select * from BNN1 c2 where c2.BBY01 = d.BBY01 and c2.BCK01 = @lBCK01)))
   and ((d.BBY49 = 0)or(d.BBY49 = 1 and exists(select * from BNR1 c3 where c3.BBY01 = d.BBY01 and c3.BDP02 = @bdp02)))
    and (a.BBX04 LIKE @lSqlText OR d.BBY44 LIKE @lSqlText 
    OR (EXISTS(SELECT g.* FROM BDK1 g WHERE g.BBX01 = a.BBX01
      AND (g.BDK03 LIKE @lSqlText OR g.ABBRP LIKE @lSqlText OR g.ABBRW LIKE @lSqlText)))) 
  order by e.BDA01,a.BBX01
end', '2016-08-09 17:51:18', '(8058)高瑜', 0)
else
print 'SYS_Scripts.id=1768 已经存在.'

GO


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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=1769)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(1769, 31, 'Sql_提取项目分类名称', '下达医嘱脚本', '医嘱检索窗口，提取项目分类名称', 1, 0, Null, 1, 'SELECT * FROM BEH1 WHERE BEH01 = %s', '2012-08-29 10:48:03', '(8058)高瑜', 0)
else
print 'SYS_Scripts.id=1769 已经存在.'

GO


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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=7260)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(7260, 31, 'Sql_方案草药表格检索', '下达医嘱脚本', '方案草药表格检索', 1, 0, Null, 1, 'DECLARE @lSqlText varchar(100), @lACF01 int,@lBCK01 int
Declare @lDate Datetime, @aParamno varchar(10)
SET @lSqlText = %s
SET @lACF01 = %d
Set @lDate = Convert(varchar(10),GetDate(),21)+'' 23:59:59.999''

  SELECT e.BDA01, e.BDA02, f.AAS02
    , a.BBX01, a.BDO01, d.BBY04 as BBX04, a.BBX05,d.BBY05
    , a.BDG02, b.BAP02 AS NBBX13, d.BBY06, d.BBE02, d.BBY01, c.BAG27
    , CASE WHEN @lACF01=1 THEN c.BDG02A when @lACF01 = 2 then c.BDG02B else d.BBY08 END AS NUnit
    , CASE WHEN @lACF01=1 THEN d.BBY25*c.BAG05 when @lACF01 = 2 then d.BBY25*c.BAG07 else d.BBY25 END AS Price  
    , null AS amount
    ,'''' BCK03 , f1.BCG02,d.BBY29  ,f.AAS07,d.BBY44
  FROM BBX1 a
  JOIN BBT1 b ON a.BBX01 = b.BBX01
  JOIN BAG1 c ON b.BBX01 = c.BBX01
  JOIN BBY1 d ON c.BBY01 = d.BBY01 
  JOIN BDA1 e ON a.BDA01 = e.BDA01  
  LEFT JOIN AAS1 f ON f.AAS01 = d.AAS01
  left join BCG1 f1 on f1.BCG01 = d.BCG01
  WHERE d.ACF01 >=1 and d.ACF01 <= 3 AND a.BDA01 = ''3'' AND d.BBY31 > @lDate
    and (d.BBY04 LIKE @lSqlText OR d.BBY44 LIKE @lSqlText  
  OR (EXISTS(SELECT g.* FROM BDK1 g WHERE g.BBX01 = a.BBX01
    AND (g.BDK03 LIKE @lSqlText OR g.ABBRP LIKE @lSqlText OR g.ABBRW LIKE @lSqlText)))) 
  order by e.BDA01,a.BBX01', '2013-03-27 14:16:26', '(8058)高瑜', 0)
else
print 'SYS_Scripts.id=7260 已经存在.'

GO