--增加分类 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 --删除旧记录 delete from SYS_Scripts where id=7690 --删除脚本内容 GO GO IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=7690) insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode) Values(7690, 33, 'Sql_医嘱计价计价信息', '', '医嘱计价时，提取计价信息', 1, 1, Null, 1, 'Declare @aDate DateTime, @pValue varchar(10),@ACF01 tinyint declare @lVAF06 int set @lVAF06 = %d Set @aDate = Convert(varchar(10),GetDate(),21) +'' 23:59:59.999'' set @pValue = dbo.GetSysParamValue(100,105003,177) set @ACF01 = 2 IF Object_id(''tempdb..#kbmVBDp'') IS NOT NULL DROP TABLE #kbmVBDp IF Object_id(''tempdb..#kbmVAFsd'') IS NOT NULL DROP TABLE #kbmVAFsd IF Object_id(''tempdb..#tmpVBD'') IS NOT NULL DROP TABLE #tmpVBD IF Object_id(''tempdb..#kbmDpt'') IS NOT NULL DROP TABLE #kbmDpt CREATE TABLE #kbmVBDp( id int IDENTITY(1,1), NVAF22 varchar(200), --医嘱 BDA02 varchar(20),--类别 VAF22 varchar(128),--收费项目 VAF21 numeric(18,4),--数量 BCK03 varchar(64), AAS02 varchar(32), BBY08 varchar(20), FBDU06 varchar(10), FBDU10 varchar(64), BDU06 tinyint, --从项 BDU05 tinyint, --是否固定项目 lPrice numeric(18,4), BDU07 tinyint, BDU10 tinyint, BCK01 int, VAF01 int, BBY01 int, VAFID int, lMoney numeric(18,4) default(0), lSum numeric(18,4) default(0), lVAFIDSum numeric(18,4) default(0), BDA01 varchar(2), A_Price numeric(18,4), B_Price numeric(18,4), max_Price numeric(18,4), min_Price numeric(18,4), BBY19 tinyint, DSK01 int, BAG23 numeric(18,4),VBD12 numeric(18,4),BBY01A int ) Select VAF01,VAF01A,BBY01,BBX01,BDA01,VAF06,VAF14,VAF15,VAF21,BCK01B,BCK01A,BCK01D,VAF32,VAF22 ,ROWNR,VAF01 as VAFID,DSK01 INTO #kbmVAFsd From VAF2 Where VAF04 = @ACF01 and VAF06 = @lVAF06 and VAF10 = 3 And BDA01 <> ''3'' UNION ALL Select VAF01,VAF01A,BBY01,BBX01,BDA01,VAF06,VAF14,VAF15,VAF20,BCK01B,BCK01A,BCK01D,VAF32,VAF22 ,ROWNR,VAF01 as VAFID,DSK01 From VAF2 Where VAF04 = @ACF01 and VAF06 = @lVAF06 and VAF10 = 3 And BDA01 = ''3'' update #kbmVAFsd set VAFID = VAF01A where BDA01 >= ''3'' and VAF01A > 0 update a set a.VAFID = b.VAF01 from #kbmVAFsd a join (select VAF01,VAF01A from #kbmVAFsd where BDA01 <= ''2'' and ROWNR = 1) b on b.VAF01A = a.VAF01 where a.BDA01 = ''T'' and a.VAF32 = 1 select a2.BBX05 NVAF22, c.BDA02,b.bby05 VAF22,f.VBD04 VAF21,d.BCK03,e.AAS02,b2.BDG02B BBY08 ,CASE WHEN f.VBD06=0 THEN ''否'' ELSE ''是'' END AS FBDU06 , '''' AS FBDU10,f.VBD06 as BDU06,1 as BDU05,b.BBY25 lPrice,f.VBD08 BDU07,f.VBD09 BDU10,f.BCK01,a.VAF01,b.BBY01,a.VAFID ,a.BDA01,b.BBY25 A_Price,b.BBY25 B_price,isnull(b.BBY28,0) max_Price,isnull(b.BBY27,0) min_Price ,isnull(b.BBY19,0) BBY19,b2.BAG07,a3.ABC02,b.BDN01,b.BCH01,a.DSK01,b2.BAG23,a.BCK01A,0 ABC01,f.VBD12,f.BBY01A into #tmpVBD FROM #kbmVAFsd a join BBX1 a2 on a2.BBX01 = a.BBX01 join VBD2 f on f.VAF01 = a.VAF01 JOIN BBY1 b ON b.BBY01 = f.BBY01 JOIN BAG1 b2 ON b.BBY01 = b2.BBY01 JOIN VAE1 a3 ON a3.VAE01 = a.VAF06 join BDA1 c on c.BDA01 = a.BDA01 JOIN BCK1 d ON d.BCK01 = a.BCK01B left JOIN AAS1 e ON b.AAS01 = e.AAS01 WHERE a.BDA01 >= ''1'' And a.BDA01 <= ''3'' And @aDate < b.BBY31 UNION ALL SELECT a2.BBX05 As NVAF22,c.BDA02,b.BBY05 as VAF22,f.VBD04 VAF21 ,case when (b.BDN01 <= ''3'') then dbo.GetDrugDept(@ACF01,b.BBY01,a.BCK01A,1) else d.BCK03 end BCK03 ,e.AAS02,b.BBY08,CASE b2.BDU06 WHEN 1 THEN ''是'' ELSE ''否'' END AS FBDU06,'''' FBDU10,b2.BDU06,b2.BDU05 ,b.BBY25 lPrice,b2.BDU07,b2.BDU10 ,case when (b.BDN01 <= ''3'') then cast(dbo.GetDrugDept(@ACF01,b.BBY01,a.BCK01A,0) as int) else a.BCK01B end BCK01B ,a.VAF01,b.BBY01,a.VAFID ,a.BDA01,b.BBY25 A_Price,b.BBY25 B_price,isnull(b.BBY28,0),isnull(b.BBY27,0),isnull(b.BBY19,0) ,0 BAG07,a3.ABC02,b.BDN01,b.BCH01,a.DSK01,0 BAG23,a.BCK01A,0 ABC01,f.VBD12,f.BBY01A FROM #kbmVAFsd a JOIN BBX1 a2 ON a.BBX01 = a2.BBX01 join VBD2 f on f.VAF01 = a.VAF01 JOIN BDA1 c ON a.BDA01 = c.BDA01 JOIN BBY1 b ON b.BBY01 = f.BBY01 JOIN VAE1 a3 ON a3.VAE01 = a.VAF06 left JOIN BDU1 b2 ON a.BBX01 = b2.BBX01 and b2.BBY01 = f.BBY01 and b2.BDU07=1 LEFT JOIN BCK1 d ON a.BCK01B = d.BCK01 LEFT JOIN AAS1 e ON b.AAS01 = e.AAS01 WHERE a.VAF01A = 0 AND a.BDA01 = ''E'' And @aDate < b.BBY31 UNION ALL SELECT a.VAF14+'' : ''+a.VAF15 As NVAF22,c.BDA02,b.BBY05 as VAF22,f.VBD04 AS VAF21 ,case when (b.BDN01 <= ''3'') then dbo.GetDrugDept(@ACF01,b.BBY01,a.BCK01A,1) else d.BCK03 end BCK03 ,e.AAS02,b.BBY08,CASE b2.BDU06 WHEN 1 THEN ''是'' ELSE ''否'' END AS FBDU06,'''' FBDU10,b2.BDU06,b2.BDU05 ,b.BBY25 lPrice,b2.BDU07,b2.BDU10 ,case when (b.BDN01 <= ''3'') then cast(dbo.GetDrugDept(@ACF01,b.BBY01,a.BCK01A,0) as int) else a.BCK01B end BCK01B ,a.VAF01,b.BBY01,a.VAFID ,a.BDA01,b.BBY25,b.BBY25,isnull(b.BBY28,0),isnull(b.BBY27,0),isnull(b.BBY19,0) ,0 BAG07,a3.ABC02,b.BDN01,b.BCH01,a.DSK01,0 BAG23,a.BCK01A,0 ABC01,f.VBD12,f.BBY01A FROM #kbmVAFsd a JOIN BDA1 c ON a.BDA01 = c.BDA01 join VBD2 f on f.VAF01 = a.VAF01 JOIN BBY1 b ON b.BBY01 = f.BBY01 JOIN VAE1 a3 ON a3.VAE01 = a.VAF06 left JOIN BDU1 b2 ON a.BBX01 = b2.BBX01 and b2.BBY01 = f.BBY01 and b2.BDU07=2 and a.VAF14=b2.BDU08 and a.VAF15 = b2.BDU09 LEFT JOIN BCK1 d ON a.BCK01B = d.BCK01 LEFT JOIN AAS1 e ON b.AAS01 = e.AAS01 WHERE a.VAF01A > 0 AND a.BDA01 = ''E'' And @aDate < b.BBY31 UNION ALL SELECT isnull(a2.BBX05,a.VAF22) As NVAF22,isnull(c.BDA02,''描嘱''),b.BBY05 as VAF22,f.VBD04 VAF21 ,case when (b.BDN01 <= ''3'') then dbo.GetDrugDept(@ACF01,b.BBY01,a.BCK01A,1) else d.BCK03 end BCK03 ,e.AAS02,b.BBY08,CASE b2.BDU06 WHEN 1 THEN ''是'' ELSE ''否'' END AS FBDU06 ,CASE WHEN (a.BDA01 = ''T'')and(a.VAF32 = 1)and(f.VBD09=2) then ''当天只收取一次'' else ''正常收取'' END AS FBDU10 ,b2.BDU06,b2.BDU05,b.BBY25 lPrice,b2.BDU07,f.VBD09 BDU10 ,case when (b.BDN01 <= ''3'') then cast(dbo.GetDrugDept(@ACF01,b.BBY01,a.BCK01A,0) as int) else a.BCK01B end BCK01B ,a.VAF01,b.BBY01,a.VAFID ,a.BDA01,b.BBY25,b.BBY25,isnull(b.BBY28,0),isnull(b.BBY27,0),isnull(b.BBY19,0) ,0 BAG07,a3.ABC02,b.BDN01,b.BCH01,a.DSK01,0 BAG23,a.BCK01A,0 ABC01,f.VBD12,f.BBY01A FROM #kbmVAFsd a join VBD2 f on f.VAF01 = a.VAF01 JOIN BBY1 b ON b.BBY01 = f.BBY01 JOIN VAE1 a3 ON a3.VAE01 = a.VAF06 left JOIN BDA1 c ON a.BDA01 = c.BDA01 left JOIN BBX1 a2 ON a.BBX01 = a2.BBX01 left JOIN BDU1 b2 ON a.BBX01 = b2.BBX01 and b2.BBY01 = f.BBY01 and b2.BDU07=1 LEFT JOIN BCK1 d ON a.BCK01B = d.BCK01 LEFT JOIN AAS1 e ON b.AAS01 = e.AAS01 WHERE ((a.BDA01 > ''3'' And a.BDA01 <> ''E'') or a.BDA01=''0'') And @aDate < b.BBY31 and (a.VAF01A=0 or (a.VAF01A>0 and isnull(a.VAF15,'''')='''')) UNION ALL SELECT isnull(a2.BBX05,a.VAF22) As NVAF22,isnull(c.BDA02,''描嘱''),b.BBY05 as VAF22,f.VBD04 VAF21 ,case when (b.BDN01 <= ''3'') then dbo.GetDrugDept(@ACF01,b.BBY01,a.BCK01A,1) else d.BCK03 end BCK03 ,e.AAS02,b.BBY08,CASE b2.BDU06 WHEN 1 THEN ''是'' ELSE ''否'' END AS FBDU06 ,CASE WHEN (a.BDA01 = ''T'')and(a.VAF32 = 1)and(f.VBD09=2) then ''当天只收取一次'' else ''正常收取'' END AS FBDU10 ,b2.BDU06,b2.BDU05,b.BBY25 lPrice,b2.BDU07,f.VBD09 BDU10 ,case when (b.BDN01 <= ''3'') then cast(dbo.GetDrugDept(@ACF01,b.BBY01,a.BCK01A,0) as int) else a.BCK01B end BCK01B ,a.VAF01,b.BBY01,a.VAFID ,a.BDA01,b.BBY25,b.BBY25,isnull(b.BBY28,0),isnull(b.BBY27,0),isnull(b.BBY19,0) ,0 BAG07,a3.ABC02,b.BDN01,b.BCH01,a.DSK01,0 BAG23,a.BCK01A,0 ABC01,f.VBD12,f.BBY01A FROM #kbmVAFsd a join VBD2 f on f.VAF01 = a.VAF01 JOIN BBY1 b ON b.BBY01 = f.BBY01 JOIN VAE1 a3 ON a3.VAE01 = a.VAF06 left JOIN BDA1 c ON a.BDA01 = c.BDA01 left JOIN BBX1 a2 ON a.BBX01 = a2.BBX01 left JOIN BDU1 b2 ON a.BBX01 = b2.BBX01 and b2.BBY01 = f.BBY01 and b2.BDU07=2 and b2.BDU08=a.VAF15 LEFT JOIN BCK1 d ON a.BCK01B = d.BCK01 LEFT JOIN AAS1 e ON b.AAS01 = e.AAS01 WHERE a.VAF01A>0 and (a.BDA01 >= ''A'' And a.BDA01 <> ''E'') And @aDate < b.BBY31 and isnull(a.VAF15,'''')>'''' select a.BCK01,a.BBY01,min(a.DSK_ID) DSK01 into #kbmDpt from DPT1 a join (select BCK01,BBY01 from #tmpVBD 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.lPrice = case when c.BCK18 = 1 and isnull(b.BAG23,0) > 0.000001 then b.BAG23 else a.lPrice end ,a.B_price = case when c.BCK18 = 1 and isnull(b.BAG23,0) > 0.000001 then b.BAG23 else a.B_price end ,a.A_Price = case when c.BCK18 = 1 and isnull(b.BAG23,0) > 0.000001 then b.BAG23 else a.A_Price end ,a.BAG23 = b.BAG23,a.DSK01 = d.DSK01,a.BBY08 = b.BDG02B from #tmpVBD a join BAG1 b on b.BBY01 = a.BBY01 join BCK1 c on c.BCK01 = a.BCK01 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.lPrice = case when d.BCK18 = 1 then c.PurchasePrice else c.PresellPrice end ,a.B_price = case when d.BCK18 = 1 then c.PurchasePrice else c.PresellPrice end ,a.A_Price = case when d.BCK18 = 1 then c.PurchasePrice else c.PresellPrice end ,a.DSK01 = b.DSK01,a.BAG23 = c.PurchasePrice from #tmpVBD 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 join BCK1 d on d.BCK01 = a.BCK01 where a.BDN01 >=''1'' and a.BDN01 <= ''3'' and (a.BBY19 = 1 or (a.BBY19 = 0 and d.BCK18 = 1)) UPDATE a set a.ABC01 = 1 from #tmpVBD a join ABC1 b 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 where c.ABC02 = b.ABC02 and a.BCK01A = c.BCK01))) INSERT INTO #kbmVBDp(NVAF22,BDA02,VAF22,VAF21,BCK03,AAS02,BBY08,FBDU06, FBDU10,BDU06,BDU05,lPrice,BDU07,BDU10,BCK01,VAF01,BBY01,VAFID,BDA01 ,A_Price,B_Price,max_Price,min_Price,BBY19,DSK01,lMoney,BAG23,VBD12,BBY01A) SELECT a.NVAF22,a.BDA02,a.VAF22,a.VAF21,a.BCK03,a.AAS02,a.BBY08,a.FBDU06 ,a.FBDU10,a.BDU06,a.BDU05 ,CASE WHEN f1.BBY01 IS NOT NULL THEN (a.lPrice*a.BAG07*f1.ACV08)/f1.ACV09 WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.lPrice*a.BAG07*g1.ACV08)/g1.ACV09 ELSE a.lPrice*a.BAG07 END AS lPrice ,a.BDU07,a.BDU10,a.BCK01,a.VAF01,a.BBY01,a.VAFID,a.BDA01 ,a.A_Price,a.B_price,a.max_Price,a.min_Price,a.BBY19,a.DSK01 ,CASE WHEN f1.BBY01 IS NOT NULL THEN (a.lPrice*a.BAG07*f1.ACV08*a.VAF21)/f1.ACV09 WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.lPrice*a.BAG07*g1.ACV08*a.VAF21)/g1.ACV09 ELSE a.lPrice*a.BAG07*a.VAF21 END AS lMoney,a.BAG23,a.VBD12,a.BBY01A FROM #tmpVBD 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.lPrice*a.BAG07*a.VAF21) AND f1.ACV07>=(a.lPrice*a.BAG07*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.lPrice*a.BAG07*a.VAF21) AND g1.ACV07>=(a.lPrice*a.BAG07*a.VAF21) WHERE a.BDA01 >= ''1'' And a.BDA01 <= ''3'' union all SELECT a.NVAF22,a.BDA02,a.VAF22,a.VAF21,a.BCK03,a.AAS02,a.BBY08,a.FBDU06 ,a.FBDU10,a.BDU06,a.BDU05 ,CASE WHEN f1.BBY01 IS NOT NULL THEN (a.lPrice*f1.ACV08)/f1.ACV09 WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.lPrice*g1.ACV08)/g1.ACV09 ELSE a.lPrice END AS lPrice ,a.BDU07,a.BDU10,a.BCK01,a.VAF01,a.BBY01,a.VAFID,a.BDA01 ,a.A_Price,a.B_price,a.max_Price,a.min_Price,a.BBY19,a.DSK01 ,CASE WHEN f1.BBY01 IS NOT NULL THEN (a.lPrice*f1.ACV08*a.VAF21)/f1.ACV09 WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.lPrice*g1.ACV08*a.VAF21)/g1.ACV09 ELSE a.lPrice*a.VAF21 END AS lMoney,a.BAG23,a.VBD12,a.BBY01A FROM #tmpVBD 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.lPrice*a.VAF21) AND f1.ACV07>=(a.lPrice*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.lPrice*a.VAF21) AND g1.ACV07>=(a.lPrice*a.VAF21) WHERE a.BDA01 >= ''4'' or a.BDA01=''0'' DECLARE @lSum numeric(18,4) SET @lSum = (SELECT sum(lMoney) FROM #kbmVBDp) UPDATE #kbmVBDp SET lSum = @lSum Update a Set a.lVAFIDSum = b.lVSum From #kbmVBDp a join (Select VAFID,Sum(lMoney)as lVSum From #kbmVBDp Group by VAFID) b on a.VAFID = b.VAFID SELECT * FROM #kbmVBDp ORDER BY VAFID,VAF01 IF Object_id(''tempdb..#kbmVBDp'') IS NOT NULL DROP TABLE #kbmVBDp IF Object_id(''tempdb..#kbmVAFsd'') IS NOT NULL DROP TABLE #kbmVAFsd IF Object_id(''tempdb..#tmpVBD'') IS NOT NULL DROP TABLE #tmpVBD IF Object_id(''tempdb..#kbmDpt'') IS NOT NULL DROP TABLE #kbmDpt ', '2016-09-19 18:29:16', '(8058)高瑜', 0) else print 'SYS_Scripts.id=7690 已经存在.' GO