--增加分类
GO
IF not EXISTS(SELECT * FROM SYS_ScriptCategories WHERE id=1)
insert into SYS_ScriptCategories(ID,Code,Name,ComponentID,xRowNum,ParentID,xLevel,Description)  Values(1, 'SC1', 'HOMaster 基础管理', 1, 0, 0, 0, Null)
else
print 'SYS_ScriptCategories.id=1 已经存在.'

GO


If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 1)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=1
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,Custom)  Values(726, 195, 'Advice_List_all', '下达医嘱脚本', ' 表格：检索医嘱项目，用于表格形式，药品按规格', 1, 1, Null, 1, 'DECLARE @lSqlText varchar(100), @lACF01 int, @lBBX09 int,@lBCK01 int, @lBCE01 int,@abc02 varchar(50),@bdp02 varchar(50)
Declare @lDate DateTime, @aParamno varchar(10),@aParam1 varchar(10),@BCKyf int,@gparam60 varchar(10),@iaa01 int
declare @vaf58 tinyint,@gpara110 varchar(10)
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 @iaa01=%d
set @gparam60 = dbo.GetSysParamValue(9999,9999,60)
set @gpara110 = dbo.GetSysParamValue(9999,9999,110)
Set @lDate = Getdate() --Convert(varchar(10),Getdate(),21)+'' 23:59:58''
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 BCK01
	  from (select @BCKyf BCK01) s
	  where @BCKyf>0
	  union all
	  select BCK01
      from dbo.GetDefDrugBck(@lBCK01,@lACF01)
      where @BCKyf=0    
    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 with(nolock)  
  where @aParamno = ''0'' and a.BCK01A = @lBCK01
    and exists(select *from baz1 b with(nolock) 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
  , cast((case when @gparam60=''2'' then dbo.GetDrugPrice(h1.BCK01 , d.BBY01 , @lACF01 , @abc02)
        else CASE WHEN @lACF01=1 THEN (case when @gpara110=''1'' then h1.PresellPrice else d.BBY25 end)*c.BAG05 
          ELSE (case when @gpara110=''1'' then h1.PresellPrice else d.BBY25 end)*c.BAG07 END end) as float) AS Price   
  , cast((CASE WHEN @lACF01 = 1 THEN ISNULL(h1.LSQty,0)/c.BAG05 ELSE ISNULL(h1.LSQty,0)/c.BAG07 END) as float) AS amount
  ,h2.BCK03, f1.BCG02,d.BBY29 ,f.AAS07,isnull(h1.BCK01,0) BCK01,d.BBY44,h1.DSK01
FROM BBX1 a with(nolock)
  JOIN BBT1 b with(nolock) ON a.BBX01 = b.BBX01
  JOIN BAG1 c with(nolock) ON b.BBX01 = c.BBX01
  JOIN BBY1 d with(nolock) ON c.BBY01 = d.BBY01 
  JOIN BDA1 e with(nolock) 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 with(nolock) on h2.BCK01 = h1.BCK01
  LEFT JOIN AAS1 f with(nolock) ON f.AAS01 = d.AAS01
  left join BCG1 f1 with(nolock) on f1.BCG01 = d.BCG01 
  left join BHJ1 f2 with(nolock) 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 with(nolock) where c2.BBY01 = d.BBY01 and c2.BCK01 = @lBCK01)))
  and ((d.BBY49 = 0)or(d.BBY49 = 1 and exists(select * from BNR1 c3 with(nolock) where c3.BBY01 = d.BBY01 and c3.BDP02 = @bdp02 and @lACF01 = (c3.ACF01 & @lACF01))))
  and ((d.BBY51 = 0)or(d.BBY51 = 1 and exists(select * from BPB1 c4 with(nolock) where c4.BBY01 = d.BBY01 and c4.BCE01 = @lBCE01)))
  and (d.BBY04 LIKE @lSqlText OR d.BBY44 LIKE @lSqlText 
  OR (EXISTS(SELECT g.* FROM BDK1 g with(nolock) 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
  , cast((case when @gparam60=''2'' then dbo.GetDrugPrice(h1.BCK01 , d.BBY01 , @lACF01 , @abc02)
        else CASE WHEN @lACF01=1 THEN (case when @gpara110=''1'' then h1.PresellPrice else d.BBY25 end)*c.BAG05 
          ELSE (case when @gpara110=''1'' then h1.PresellPrice else d.BBY25 end)*c.BAG07 END end) as float) AS Price     
  , cast((CASE WHEN @lACF01 = 1 THEN ISNULL(h1.LSQty,0)/c.BAG05 ELSE ISNULL(h1.LSQty,0)/c.BAG07 END) as float) AS amount
  ,h2.BCK03, f1.BCG02,d.BBY29,f.AAS07,isnull(h1.BCK01,0) BCK01,d.BBY44,h1.DSK01
FROM BBX1 a with(nolock)
  JOIN BBT1 b with(nolock) ON a.BBX01 = b.BBX01
  JOIN BAG1 c with(nolock) ON b.BBX01 = c.BBX01
  JOIN BBY1 d with(nolock) ON c.BBY01 = d.BBY01 
  JOIN BDA1 e with(nolock) 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 with(nolock) on h2.BCK01 = h1.BCK01
  LEFT JOIN AAS1 f with(nolock) ON f.AAS01 = d.AAS01
  left join BCG1 f1 with(nolock) on f1.BCG01 = d.BCG01
  left join BHJ1 f2 with(nolock) 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 with(nolock) where c2.BBY01 = d.BBY01 and c2.BCK01 = @lBCK01)))
  and ((d.BBY49 = 0)or(d.BBY49 = 1 and exists(select * from BNR1 c3 with(nolock) where c3.BBY01 = d.BBY01 and c3.BDP02 = @bdp02 and @lACF01 = (c3.ACF01 & @lACF01))))
  and ((d.BBY51 = 0)or(d.BBY51 = 1 and exists(select * from BPB1 c4 with(nolock) where c4.BBY01 = d.BBY01 and c4.BCE01 = @lBCE01)))
  and (d.BBY04 LIKE @lSqlText OR d.BBY44 LIKE @lSqlText 
  OR (EXISTS(SELECT g.* FROM BDK1 g with(nolock) 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,0 DSK01
FROM BBX1 a with(nolock)
JOIN BCT1 c with(nolock) ON c.BBX01 = a.BBX01
JOIN BBY1 d with(nolock) ON d.BBY01 = c.BBY01 
JOIN BDA1 e with(nolock) ON e.BDA01 = a.BDA01  
LEFT JOIN AAS1 f with(nolock) ON f.AAS01 = d.AAS01
left join BCG1 h with(nolock) 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 with(nolock) where c2.BBY01 = d.BBY01 and c2.BCK01 = @lBCK01)))
   and ((d.BBY49 = 0)or(d.BBY49 = 1 and exists(select * from BNR1 c3 with(nolock) where c3.BBY01 = d.BBY01 and c3.BDP02 = @bdp02 and @lACF01 = (c3.ACF01 & @lACF01))))
   and ((d.BBY51 = 0)or(d.BBY51 = 1 and exists(select * from BPB1 c4 with(nolock) where c4.BBY01 = d.BBY01 and c4.BCE01 = @lBCE01)))
  and (d.BBY04 LIKE @lSqlText OR d.BBY44 LIKE @lSqlText 
  OR (EXISTS(SELECT g.* FROM BCL1 g with(nolock) 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,0 DSK01
FROM BBX1 a with(nolock)
  JOIN BDA1 e with(nolock) ON a.BDA01 = e.BDA01
  LEFT JOIN V_BDU vb ON a.BBX01 = vb.BBX01
  LEFT JOIN AAS1 f with(nolock) ON f.AAS01 = a.AAS01
  LEFT JOIN AAF1 a1 with(nolock) ON a.BDA01=''T'' AND a.BBX13=a1.AAF01
  LEFT JOIN BBC1 a2 with(nolock) ON a.BDA01=''E''  AND a.BBX13=a2.BBC01
  LEFT JOIN AAV1 a3 with(nolock) ON a.BDA01=''L'' AND a.BBX13=a3.AAV01
  LEFT JOIN ACH1 a4 with(nolock) ON a.BDA01=''S'' AND a.bbx13=a4.ACH01
  LEFT JOIN ACI1 a5 with(nolock) ON a.BDA01=''A'' AND a.BBX01=a5.ACI01 
  LEFT JOIN AAP1 a7 with(nolock) ON a.BDA01=''Z'' AND a.BBX13=a7.AAP01 
  LEFT JOIN AAG1 a6 with(nolock) 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 with(nolock) where c2.BBX01 = a.BBX01 and c2.BCK01 = @lBCK01)))
 and ((a.BBX46 = 0)or(a.BBX46 = 1 and exists(select * from BMP1 c3 with(nolock) where c3.BBX01 = a.BBX01 and c3.BDP02 = @bdp02 and @lACF01 = (c3.ACF01 & @lACF01))))
 and ((a.BBX49 = 0)or(a.BBX49 = 1 and exists(select * from BPA1 c4 with(nolock) where c4.BBX01 = a.BBX01 and c4.BCE01 = @lBCE01)))
 AND ((a.BDA01 not in (''8'',''9''))OR((a.BDA01 in (''8'',''9'')) AND ((a.BBX15=0 and exists(select * from BPC1 c5 with(nolock) where c5.BBX01=a.BBX01 and c5.BCE01=@lBCE01) )
    or 2=isnull(a.BBX15,2) or (a.BBX15=1 and exists(select c.* from BAR1 c with(nolock) 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 with(nolock) 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', '2018-11-16 18:17:23', '(8058)高瑜', 0, 0)
else
print 'SYS_Scripts.id=726 已经存在.'

GO


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