--增加分类
GO
IF not EXISTS(SELECT * FROM SYS_ScriptCategories WHERE id=32)
insert into SYS_ScriptCategories(ID,Code,Name,ComponentID,xRowNum,ParentID,xLevel,Description)  Values(32, 'SC32', '门诊医师工作站', 17, 0, 31, 0, Null)
else
print 'SYS_ScriptCategories.id=32 已经存在.'

GO


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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=2118)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(2118, 32, 'Sql_编辑医嘱提取计价', Null, '医嘱编辑发送医嘱提取医嘱计价信息', 1, 0, Null, 1, 'Declare @aDate DateTime
Set @aDate = Convert(varchar(10),GetDate(),21) +'' 23:59:59.999''
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

Select VAF01,VAF01A,BBY01,BBX01,BDA01,VAF06,VAF14,VAF15,VAF21,BCK01B,BCK01A,BCK01D,VAF32,DSK01
INTO #kbmVAFsd
From VAF1 
Where CBM01 in (%s) And BDA01 <> ''3''
UNION ALL
Select VAF01,VAF01A,BBY01,BBX01,BDA01,VAF06,VAF14,VAF15,VAF20,BCK01B,BCK01A,BCK01D,VAF32,DSK01
From VAF1 
Where CBM01 in (%s) And BDA01 = ''3''

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
join BBY1 c 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
join BBY1 c on c.BBY01 = a.BBY01
join BCR1 d on d.BBY01A = c.BBY01
join BBY1 e 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,0 BDU06,1 BDU07,0 BDU10,a.BCK01B BCK01,a.VAF01,b.BBY01,b.BBY25,a.DSK01,a.BDA01,b.BDN01
  ,c.ABC02,b.BBY19,b.BCH01,b2.BAG05,b2.BAG23,0 ABC01,a.BCK01A, 9 BCK18 ,0 BBY01A,a.VAF21 VBD12
into #kbmVBD   
FROM #kbmVAFsd a
JOIN BBY1 b ON a.BBY01 = b.BBY01 
join BAG1 b2 on b2.BBY01 = b.BBY01  
join VAC1 c on c.VAC01 = a.VAF06
WHERE a.BDA01 >= ''1'' And a.BDA01 <= ''3'' And @aDate < b.BBY31
UNION ALL
SELECT a.VAF21*b2.BDU04 AS VAF21,b2.BDU06,b2.BDU07,b2.BDU10
    ,case when (b2.BDN01 <= ''3'') then cast(dbo.GetDrugDept(1,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 BAG05,0 BAG23
    ,0,a.BCK01A,9,b2.BBY01A,b2.BDU04 VBD12
FROM #kbmVAFsd a        
JOIN #kbmbdu b2 ON a.BBX01 = b2.BBX01 and b2.BDU07=1
JOIN VAC1 c ON c.VAC01 = a.VAF06 
WHERE a.VAF01A = 0 AND a.BDA01 = ''E'' 
UNION ALL
SELECT a.VAF21*b2.BDU04 AS VAF21,b2.BDU06,b2.BDU07,b2.BDU10
    ,case when (b2.BDN01 <= ''3'') then cast(dbo.GetDrugDept(1,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 BAG05
    ,0 BAG23,0,a.BCK01A,9 ,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 VAC1 c ON c.VAC01 = a.VAF06 
WHERE a.VAF01A > 0 AND a.BDA01 = ''E''
UNION ALL
SELECT a.VAF21*b2.BDU04 AS VAF21,b2.BDU06,b2.BDU07,b2.BDU10
   ,case when (b2.BDN01 <= ''3'') then cast(dbo.GetDrugDept(1,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 BAG05
   ,0 BAG23,0,a.BCK01A,9 ,b2.BBY01A,b2.BDU04 VBD12
FROM #kbmVAFsd a              
JOIN #kbmbdu b2 ON a.BBX01 = b2.BBX01 and b2.BDU07=1
JOIN VAC1 c ON c.VAC01 = 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,b2.BDU06,b2.BDU07,b2.BDU10
   ,case when (b2.BDN01 <= ''3'') then cast(dbo.GetDrugDept(1,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 BAG05
   ,0 BAG23,0,a.BCK01A,9 ,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 VAC1 c ON c.VAC01 = 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,0 BDU06,1 BDU07,0 BDU10,a.BCK01B BCK01,a.VAF01,b.BBY01,b.BBY25,a.DSK01,a.BDA01,b.BDN01
  ,c.ABC02,b.BBY19,b.BCH01,0 BAG05,0 BAG23,0 ABC01,a.BCK01A, 9 BCK18 ,0 BBY01A,a.VAF21 VBD12
FROM #kbmVAFsd a
JOIN BBY1 b ON a.BBY01 = b.BBY01 
join BCT1 d on d.BBY01 = b.BBY01
join VAC1 c on c.VAC01 = 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 on b.ABC02 = a.ABC02
where b.ACF01 in (1,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 where c.ABC02 = b.ABC02 and a.BCK01A = c.BCK01))) 

    update a set a.BCK18 = case when ISNULL(f1.BBY01,0)<> 0 then f1.ACV10
                                when ISNULL(f1.BBY01,0)= 0 and ISNULL(g1.BCH01,'''') <> '''' then g1.ACV10
                                else a.BCK18 end
    FROM #kbmVBD a
    JOIN BBY1 c ON a.BBY01 = c.BBY01
    LEFT JOIN (SELECT f.ABC02,f.BBY01,f.ACV10 
               FROM ACV1 f 
               WHERE f.BCH01 IS NULL and ISNULL(f.ACV10,9)<>9) AS f1 ON a.ABC02=f1.ABC02 AND a.BBY01=f1.BBY01                   
    LEFT JOIN (SELECT g.ABC02,g.BCH01,g.ACV10 
               FROM ACV1 g 
               WHERE g.BBY01 IS NULL and ISNULL(g.ACV10,9)<>9) AS g1 ON a.ABC02=g1.ABC02 AND c.BCH01=g1.BCH01
                   AND NOT EXISTS(SELECT *
                                  FROM ACV1 g2 
                                  WHERE g2.BCH01 IS NULL AND a.ABC02=g2.ABC02 AND a.BBY01=g2.BBY01)                   
    where a.ABC01 = 1 and a.BDN01 >= ''1'' and a.BDN01 <= ''3''
    
    update a set a.BCK18 = isnull(c.BCK18,0)
    from #kbmVBD a   
    join BCK1 c on c.BCK01 = a.BCK01  
    where a.BDN01 >=''1'' and a.BDN01 <= ''3'' and a.BCK18 = 9
    
select a.BCK01,a.BBY01,min(a.DSK_ID) DSK01
into #kbmDpt
from DPT1 a
join (select BCK01,BBY01
      from #kbmVBD 
      where BDN01 >=''1'' and BDN01 <= ''3'') b on b.BCK01 = a.BCK01 and b.BBY01 = a.BBY01
where a.Quantity > 0.000001 and a.RefQty > 0.000001
group by a.BCK01,a.BBY01 

update a 
set a.BBY25 = case when a.BCK18 = 1 and isnull(b.BAG23,0) > 0.000001 then b.BAG23 else a.BBY25 end ,a.BAG23 = b.BAG23,a.DSK01 = d.DSK01
from #kbmVBD a
join BAG1 b on b.BBY01 = a.BBY01
left join #kbmDpt d on d.BCK01 = a.BCK01 and d.BBY01 = a.BBY01    
where a.BDN01 >=''1'' and a.BDN01 <= ''3''
    
update a 
set a.BBY25 = case when a.BCK18 = 1 then c.PurchasePrice else c.PresellPrice end,a.DSK01 = b.DSK01,a.BAG23 = c.PurchasePrice
from #kbmVBD a
join #kbmDpt b on b.BCK01 = a.BCK01 and b.BBY01 = a.BBY01
join DPT1 c on c.BCK01 = b.BCK01 and c.BBY01 = b.BBY01 and c.DSK_ID = b.DSK01
where a.BDN01 >=''1'' and a.BDN01 <= ''3'' and (a.BBY19 = 1 or (a.BBY19 = 0 and a.BCK18 = 1))

SELECT a.VAF21,a.BDU06,a.BDU07,a.BDU10,a.BCK01,a.VAF01,a.BBY01,a.BBY25,a.DSK01,a.BDA01,a.BDN01
   ,CASE WHEN f1.BBY01 IS NOT NULL THEN (a.BBY25*a.BAG05*f1.ACV08)/f1.ACV09 
         WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.BBY25*a.BAG05*g1.ACV08)/g1.ACV09 
      ELSE a.BBY25*a.BAG05 END AS lPrice,a.BBY01A ,a.BAG23,a.VBD12   
FROM #kbmVBD a
LEFT JOIN (SELECT f.ABC02,f.BBY01,f.ACV06,f.ACV07,f.ACV08,f.ACV09,f.ACV10 
           FROM ACV1 f 
           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.BAG05*a.VAF21) AND f1.ACV07>=(a.BBY25*a.BAG05*a.VAF21)    
LEFT JOIN (SELECT g.ABC02,g.BCH01,g.ACV06,g.ACV07,g.ACV08,g.ACV09,g.ACV10 
           FROM ACV1 g 
           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 
                                WHERE g2.BCH01 IS NULL AND a.ABC02=g2.ABC02 AND a.BBY01=g2.BBY01)
                 AND g1.ACV06<= (a.BBY25*a.BAG05*a.VAF21) AND g1.ACV07>=(a.BBY25*a.BAG05*a.VAF21)    
WHERE a.BDA01 >= ''1'' And a.BDA01 <= ''3''
UNION ALL
SELECT a.VAF21,a.BDU06,a.BDU07,a.BDU10,a.BCK01,a.VAF01,a.BBY01,a.BBY25,a.DSK01,a.BDA01,a.BDN01
   ,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 lPrice,a.BBY01A,a.BAG23,a.VBD12
FROM #kbmVBD a              
LEFT JOIN (SELECT f.ABC02,f.BBY01,f.ACV06,f.ACV07,f.ACV08,f.ACV09,f.ACV10 
           FROM ACV1 f 
           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 
           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 
                                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', '2016-09-19 18:22:57', '(8058)高瑜', 0)
else
print 'SYS_Scripts.id=2118 已经存在.'

GO