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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=7806)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode,Custom)  Values(7806, 33, 'Sql_医保上传医嘱A', '医嘱删除上传', '包含多张医嘱单的医嘱删除', 1, 1, Null, 1, 'declare @adate datetime
  declare @tmpVAFdf TABLE(VAF01 int primary key nonclustered, VAF01A int, BDA01 varchar(2),Rownr int, CBM01 int,VAF51 int,VAF10 tinyint)  
  IF Object_id(''tempdb..#kbmVAFsd'') IS NOT NULL DROP TABLE #kbmVAFsd
  IF Object_id(''tempdb..#kbmVBD'') IS NOT NULL DROP TABLE #kbmVBD
  IF Object_id(''tempdb..#kbmDpt'') IS NOT NULL DROP TABLE #kbmDpt 
  IF Object_id(''tempdb..#kbmbdu'') IS NOT NULL DROP TABLE #kbmbdu
  INSERT INTO @tmpVAFdf(VAF01)
  EXEC (''%s'')      
  set @adate=GETDATE()   
  UPDATE a
  SET a.BDA01 = b.BDA01,a.VAF01A = b.VAF01A,a.Rownr = b.Rownr, a.CBM01 = b.CBM01,a.VAF51 = b.VAF51
  FROM @tmpVAFdf a
  JOIN VAF2 b with(nolock) ON a.VAF01 = b.VAF01 
  
  InSert Into @tmpVAFdf(VAF01,CBM01,VAF51)
  Select a.VAF01,a.CBM01,a.VAF51
  From VAF2 a with(nolock)
  Join @tmpVAFdf b on a.VAF01 = b.VAF01A
  Where b.BDA01 >= ''1'' And b.BDA01 <= ''2'' And a.VAF32 = 1 And b.Rownr = 1
  UNION ALL
  Select a.VAF01,a.CBM01,a.VAF51
  From VAF2 a with(nolock)
    Join @tmpVAFdf b on a.VAF01A = b.VAF01
  Where b.BDA01 in (''8'',''S'',''E'')
  
  update a
  set a.VAF10 = b.VAF10
  from @tmpVAFdf a
  join VAF2 b with(nolock) on b.VAF01 = a.VAF01
    
  delete from @tmpVAFdf where VAF10 >= 3
  
Select VAF01,VAF01A,BBY01,BBX01,BDA01,VAF06,VAF14,VAF15,VAF21,BCK01B,BCK01A,BCK01D,VAF32,DSK01
  ,CBM01,VAF59,VAF58,VAF17,VAF18,VAF26,VAF27,VAF28,VAF29,VAF11,VAF36,VAF37,1 VAJ23,VAF35 VAJ26
  ,VAF07 VAJ27,VAF22,BIW02,VAF23,0 Compound
INTO #kbmVAFsd
From VAF2 with(nolock) 
Where BDA01 <> ''3'' and exists(select * from @tmpVAFdf b where b.VAF01=VAF2.VAF01)
UNION ALL
Select VAF01,VAF01A,BBY01,BBX01,BDA01,VAF06,VAF14,VAF15,VAF20,BCK01B,BCK01A,BCK01D,VAF32,DSK01
  ,CBM01,VAF59,VAF58,VAF17,VAF18,VAF26,VAF27,VAF28,VAF29,VAF11,VAF36,VAF37,VAF21 VAJ23,VAF35 VAJ26
  ,VAF07 VAJ27,VAF22,BIW02,VAF23,0 Compound
From VAF2 with(nolock)
Where BDA01 = ''3'' and exists(select * from @tmpVAFdf b where b.VAF01=VAF2.VAF01)

update a set a.Compound=1
from #kbmVAFsd a
join (select CBM01,VAF59,COUNT(1) fnum
      from #kbmVAFsd
      where BDA01=''3''
      group by CBM01,VAF59
      having COUNT(1)>1) b on b.CBM01=a.CBM01 and b.VAF59=a.VAF59

select a.BDU04,a.BDU05,a.BDU06,a.BDU07,a.BDU08,a.BDU09,a.BDU10,c.BBY01,a.BBX01,c.BBY25,c.BDN01,c.BBY19,c.BCH01,0 BBY01A  
into #kbmbdu
from BDU1 a with(nolock)
join BBY1 c with(nolock) on c.BBY01 = a.BBY01
where c.BBY34 <> 2 and c.BBY31 > @aDate
  and exists(select * from #kbmVAFsd b where b.BDA01 >= ''8'' and b.BDA01 <> ''M'' and a.BBX01 = b.BBX01)
union all
select a.BDU04*d.BCR04,a.BDU05,a.BDU06,a.BDU07,a.BDU08,a.BDU09,a.BDU10,e.BBY01,a.BBX01
 ,Case when d.BCR10 = 1 then e.BBY25 else d.BCR07 end BBY25,e.BDN01,e.BBY19,e.BCH01,d.BBY01A 
from BDU1 a with(nolock)
join BBY1 c with(nolock) on c.BBY01 = a.BBY01
join BCR1 d with(nolock) on d.BBY01A = c.BBY01
join BBY1 e with(nolock) on e.BBY01 = d.BBY01B
where c.BBY34 = 2 and c.BBY31 > @aDate and e.BBY31 > @aDate and d.BCR09 > @aDate
  and exists(select * from #kbmVAFsd b where b.BDA01 >= ''8'' and b.BDA01 <> ''M'' and a.BBX01 = b.BBX01)

SELECT a.VAF21,a.BCK01B BCK01,a.VAF01,b.BBY01,b.BBY25,a.DSK01,a.BDA01,b.BDN01
  ,c.ABC02,b.BBY19,b.BCH01,b2.BAG07,b2.BAG23,0 ABC01,a.BCK01A ,0 BBY01A,a.VAF21 VBD12 
into #kbmVBD   
FROM #kbmVAFsd a
JOIN BBY1 b with(nolock) ON a.BBY01 = b.BBY01 
join BAG1 b2 with(nolock) on b2.BBY01 = b.BBY01  
join VAE1 c with(nolock) on c.VAE01 = a.VAF06
WHERE a.BDA01 >= ''1'' And a.BDA01 <= ''3'' And @aDate < b.BBY31
UNION ALL
SELECT case when b2.BDU10=6 then b2.BDU04 else a.VAF21*b2.BDU04 end VAF21
    ,case when (b2.BDN01 <= ''3'') then cast(dbo.GetDrugDept(2,b2.BBY01,a.BCK01A,0) as int)
                         else a.BCK01B end as BCK01
    ,a.VAF01,b2.BBY01,b2.BBY25,a.DSK01,a.BDA01,b2.BDN01,c.ABC02,b2.BBY19,b2.BCH01,0 BAG07
    ,0 BAG23,0 ABC01,a.BCK01A,b2.BBY01A,b2.BDU04 VBD12
FROM #kbmVAFsd a        
JOIN #kbmbdu b2 ON a.BBX01 = b2.BBX01 and b2.BDU07=1
join VAE1 c with(nolock) on c.VAE01 = a.VAF06
WHERE a.VAF01A = 0 AND a.BDA01 = ''E'' 
UNION ALL
SELECT a.VAF21*b2.BDU04 AS VAF21
    ,case when (b2.BDN01 <= ''3'') then cast(dbo.GetDrugDept(2,b2.BBY01,a.BCK01A,0) as int)
                         else a.BCK01B end as BCK01
    ,a.VAF01,b2.BBY01,b2.BBY25,a.DSK01,a.BDA01,b2.BDN01,c.ABC02,b2.BBY19,b2.BCH01
    ,0 BAG07,0 BAG23,0 ABC01,a.BCK01A,b2.BBY01A,b2.BDU04 VBD12
FROM #kbmVAFsd a	              
JOIN #kbmbdu b2 ON a.BBX01 = b2.BBX01 and b2.BDU07=2 and a.VAF14=b2.BDU08 and a.VAF15 = b2.BDU09
join VAE1 c with(nolock) on c.VAE01 = a.VAF06
WHERE a.VAF01A > 0 AND a.BDA01 = ''E'' 
UNION ALL
SELECT case when b2.BDU10=6 then b2.BDU04 else a.VAF21*b2.BDU04 end VAF21  
   ,case when (b2.BDN01 <= ''3'') and a.VAF32=0 then cast(dbo.GetDrugDept(2,b2.BBY01,a.BCK01A,0) as int)
         when b2.BDN01<=''3'' and a.VAF32=1 then a.BCK01D
                         else a.BCK01B end as BCK01
   ,a.VAF01,b2.BBY01,b2.BBY25,a.DSK01,a.BDA01,b2.BDN01,c.ABC02,b2.BBY19,b2.BCH01
   ,0 BAG07,0 BAG23,0 ABC01,a.BCK01A,b2.BBY01A,b2.BDU04 VBD12
FROM #kbmVAFsd a              
JOIN #kbmbdu b2 ON a.BBX01 = b2.BBX01 and b2.BDU07=1
join VAE1 c with(nolock) on c.VAE01 = a.VAF06
WHERE a.BDA01 >= ''8'' And a.BDA01 not in(''E'',''M'') and (a.VAF01A=0 or (a.VAF01A>0 and isnull(a.VAF15,'''')=''''))
UNION ALL
SELECT a.VAF21*b2.BDU04 AS VAF21
   ,case when (b2.BDN01 <= ''3'') then cast(dbo.GetDrugDept(2,b2.BBY01,a.BCK01A,0) as int)
                         else a.BCK01B end as BCK01
   ,a.VAF01,b2.BBY01,b2.BBY25,a.DSK01,a.BDA01,b2.BDN01,c.ABC02,b2.BBY19,b2.BCH01
   ,0 BAG07,0 BAG23,0 ABC01,a.BCK01A,b2.BBY01A,b2.BDU04 VBD12
FROM #kbmVAFsd a              
JOIN #kbmbdu b2 ON a.BBX01 = b2.BBX01 and b2.BDU07=2 and a.VAF15 = b2.BDU08
join VAE1 c with(nolock) on c.VAE01 = a.VAF06
WHERE a.VAF01A>0 and a.BDA01 >= ''8'' And a.BDA01 not in(''E'',''M'') and isnull(a.VAF15,'''') > '''' 
union all
SELECT a.VAF21,a.BCK01B BCK01,a.VAF01,b.BBY01,b.BBY25,a.DSK01,a.BDA01,b.BDN01
  ,c.ABC02,b.BBY19,b.BCH01,0 BAG07,0 BAG23,0 ABC01,a.BCK01A ,0,a.VAF21 VBD12
FROM #kbmVAFsd a
JOIN BBY1 b with(nolock) ON a.BBY01 = b.BBY01 
join BCT1 d with(nolock) on d.BBY01 = b.BBY01
join VAE1 c with(nolock) on c.VAE01 = a.VAF06
WHERE a.BDA01 in (''4'',''M'') And @aDate < b.BBY31 and d.BCT33 = 1

UPDATE a set a.ABC01 = 1 
from #kbmVBD a
join ABC1 b with(nolock) on b.ABC02 = a.ABC02
where b.ACF01 in (2,3) and DATEDIFF(DAY,b.ABC12,@aDate)>=0 and DATEDIFF(DAY,@aDate,b.ABC13)>= 0 
  and (isnull(b.ABC05,0)=0 or (isnull(b.ABC05,0)=1 and exists(select * from ACW1 c with(nolock) where c.ABC02 = b.ABC02 and a.BCK01A = c.BCK01))) 

SELECT a.BBY01,b1.BBY05,b1.BBY08,b.VAF17,b.VAF18,a.VAF21,a.BBY25,b.VAF26,b.VAF27
  ,b.VAF28,b.VAF29,b.VAF32,a.BDN01,b1.BBY04,b1.BBY08 VAJ35,b1.BBY06,b2.BBT10,b.VAF11
  ,b.BIW02,b.VAF23 TMR,b.VAF36,b.VAF37,b3.BAG03,b.VAF15,b2.BAP02,b4.BDG02,b9.VAF22 VAF22B
  ,b.VAJ23,b.VAF18 VAJ24,a.VAF21*a.BAG07 VAJ25,b.VAJ26,b.VAJ27,b.Compound
  ,CASE WHEN f1.BBY01 IS NOT NULL THEN (a.BBY25*a.BAG07*f1.ACV08)/f1.ACV09 
         WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.BBY25*a.BAG07*g1.ACV08)/g1.ACV09 
      ELSE a.BBY25*a.BAG07 END AS VAJ33,b.VAF58,b.CBM01,b.VAF59,a.BDA01
FROM #kbmVBD a
join #kbmVAFsd b on b.VAF01=a.VAF01
join #kbmVAFsd b9 on b9.VAF01=b.VAF01A
join BBY1 b1 with(nolock) on b1.BBY01=a.BBY01
join BAG1 b3 with(nolock) on b3.BBY01=b1.BBY01
join BBT1 b2 with(nolock) on b2.BBX01=b3.BBX01
join BBX1 b4 with(nolock) on b4.BBX01=b3.BBX01
LEFT JOIN (SELECT f.ABC02,f.BBY01,f.ACV06,f.ACV07,f.ACV08,f.ACV09,f.ACV10 
           FROM ACV1 f with(nolock) 
           WHERE f.BCH01 IS NULL) AS f1 ON a.ABC02=f1.ABC02 AND a.BBY01=f1.BBY01 and a.ABC01 = 1
                 AND f1.ACV06<= (a.BBY25*a.BAG07*a.VAF21) AND f1.ACV07>=(a.BBY25*a.BAG07*a.VAF21)    
LEFT JOIN (SELECT g.ABC02,g.BCH01,g.ACV06,g.ACV07,g.ACV08,g.ACV09,g.ACV10 
           FROM ACV1 g with(nolock) 
           WHERE g.BBY01 IS NULL) AS g1 ON a.ABC02=g1.ABC02 AND a.BCH01=g1.BCH01 and a.ABC01 = 1
                 AND NOT EXISTS(SELECT g2.BBY01 FROM ACV1 g2 with(nolock) 
                                WHERE g2.BCH01 IS NULL AND a.ABC02=g2.ABC02 AND a.BBY01=g2.BBY01)
                 AND g1.ACV06<= (a.BBY25*a.BAG07*a.VAF21) AND g1.ACV07>=(a.BBY25*a.BAG07*a.VAF21)    
WHERE a.BDA01 >= ''1'' And a.BDA01 <= ''3''
UNION ALL
SELECT a.BBY01,b1.BBY05,b1.BBY08,b.VAF17,b.VAF18,a.VAF21,a.BBY25,b.VAF26,b.VAF27
  ,b.VAF28,b.VAF29,b.VAF32,a.BDN01,b1.BBY04,b1.BBY08 VAJ35,b1.BBY06,0 BBT10,b.VAF11
  ,b.BIW02,b.VAF23 TMR,b.VAF36,b.VAF37,1 BAG03,b.VAF15,'''' BAP02,'''' BDG02,'''' VAF22B
  ,b.VAJ23,b.VAF18 VAJ24,a.VAF21 VAJ25,b.VAJ26,b.VAJ27,b.Compound
  ,CASE WHEN f1.BBY01 IS NOT NULL THEN (a.BBY25*f1.ACV08)/f1.ACV09 
          WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.BBY25*g1.ACV08)/g1.ACV09 
      ELSE a.BBY25 END AS VAJ33,b.VAF58,b.CBM01,b.VAF59,a.BDA01
FROM #kbmVBD a 
join #kbmVAFsd b on b.VAF01=a.VAF01 
join BBY1 b1 with(nolock) on b1.BBY01=a.BBY01           
LEFT JOIN (SELECT f.ABC02,f.BBY01,f.ACV06,f.ACV07,f.ACV08,f.ACV09,f.ACV10 
           FROM ACV1 f with(nolock) 
           WHERE f.BCH01 IS NULL) AS f1 ON a.ABC02=f1.ABC02 AND a.BBY01=f1.BBY01 and a.ABC01 = 1
                 AND f1.ACV06<= (a.BBY25*a.VAF21) AND f1.ACV07>=(a.BBY25*a.VAF21)    
LEFT JOIN (SELECT g.ABC02,g.BCH01,g.ACV06,g.ACV07,g.ACV08,g.ACV09,g.ACV10 
           FROM ACV1 g with(nolock) 
           WHERE g.BBY01 IS NULL) AS g1 ON a.ABC02=g1.ABC02 AND a.BCH01=g1.BCH01 and a.ABC01 = 1
                 AND NOT EXISTS(SELECT g2.BBY01 FROM ACV1 g2 with(nolock) 
                                WHERE g2.BCH01 IS NULL AND a.ABC02=g2.ABC02 AND a.BBY01=g2.BBY01)
                 AND g1.ACV06<= (a.BBY25*a.VAF21) AND g1.ACV07>=(a.BBY25*a.VAF21) 
WHERE a.BDA01 > ''3''
  
IF Object_id(''tempdb..#kbmVAFsd'') IS NOT NULL DROP TABLE #kbmVAFsd
IF Object_id(''tempdb..#kbmVBD'') IS NOT NULL DROP TABLE #kbmVBD
IF Object_id(''tempdb..#kbmDpt'') IS NOT NULL DROP TABLE #kbmDpt 
IF Object_id(''tempdb..#kbmbdu'') IS NOT NULL DROP TABLE #kbmbdu  ', '2017-08-02 11:46:17', '(8058)高瑜', 0, 0)
else
print 'SYS_Scripts.id=7806 已经存在.'

GO


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