--存储过程.Nurse_Long_Advice_Send --Alter 8058 2017.8.14 --护士工作站 长嘱发送 住院病人 ALTER PROC [dbo].[Nurse_Long_Advice_Send] @lXml nText , @lBCKxml nText --药房置换 , @lSign smallint --6:住院记账 , @lOne smallint --0:发送为多张单据；1：发送为单张单据 , @lBCE02 varchar(20) --当前操作员编号 , @lBCE03 varchar(20) --当前操作员姓名 , @lBCK01B int , @lEndDate datetime , @lFlag tinyint --1:表示给药途径已结束时间计算 0: 否 , @lOneDay tinyint --1:表示只执行当天的 2=护士确认停嘱执行 , @lBCE01 int ,@ReVAIid varchar(256) = '' out ,@ReVafid_hz varchar(256) = '' out ,@ReVafid_zk varchar(256) = '' out AS set @ReVAIid = '' set @ReVafid_hz = '' set @ReVafid_zk = '' DECLARE @iDOM int, @iret int Exec @iret = sp_xml_preparedocument @iDOM OUTPUT, @lXml if @iret > 0 BEGIN exec sp_xml_removedocument @iDOM RAISERROR('解析XML文档时发生错误，错误号: %d.', 16, 1, @iret) with nowait return @iret END -- IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI select @lBCE02 = BCE02,@lBCE03 = BCE03 from BCE1 where BCE01 = @lBCE01 --取得需要发送的医嘱id declare @TmpVAFcz Table(ID int IDENTITY(1,1),VAF01 int primary key nonclustered, BDA01 varchar(2) ,VAF01A int, Rownr int, CBM01 int,VAF10 tinyint,BCK01B int, BCK01C int,BCK01D int ,BCE01A int,BCE02A varchar(20),BCE03A varchar(20),VAF32 tinyint,VAF58 tinyint ,ACF01 tinyint,VAF18 numeric(18,4),VAF21 numeric(18,4),VAA01 int,VAA07 int,VAP01 int ,BCK01A int,VAF35 tinyint,VAF22 varchar(1024),VBI13 tinyint,VAI01 int,VBI07 numeric(18,4) ,VAF36 datetime,BCK01F int,ABC02 varchar(20),VAF14 varchar(60),BBX01 int,VAF31 tinyint,VAJ57 varchar(128),VAF01B int ,VAF37 datetime,VAF38 datetime,VAF27 int,VAF28 tinyint,VAF29 varchar(4),VAF61 numeric(8,2),VBG01 int ,VAF62 numeric(8,2),aVAF38 datetime,VAF47 datetime,VAF50 datetime,BCE01D int,BCE03D varchar(20) ,BCE01E int,BCE03E varchar(20),BCE01F int,BCE03F varchar(20),BBX17 tinyint,VAF34 tinyint,VAF75 tinyint ,BCK01E int,VAF23 varchar(128),VAJ57B varchar(128),bck01c_br int,bck01d_br int,VAF15 varchar(30),VEH07 varchar(20) ) Declare @Tmp_BCK Table(BCK01 int, BCK01A int) INSERT INTO @TmpVAFcz(VAF01,VAA01,VAF62,BCE01E,BCE03E) SELECT VAF01,VAA01,case when isnull(VAF62,0) < 0 then 0 else isnull(VAF62,0) end,BCE01E,BCE03E from OpenXml(@iDOM,'/Root/VAF1/Ie',8) WITH (VAF01 int ,VAA01 int,VAF62 numeric(8,2),VAF11 tinyint,BCE01E int,BCE03E varchar(64)) where VAF11 = 1 exec sp_xml_removedocument @iDOM If not exists(select * from @TmpVAFcz)or (@@ERROR <> 0) BEGIN --IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz --RAISERROR('从文档中提取医嘱信息错误.', 16, 1) with nowait RETURN 2 END Exec @iret = sp_xml_preparedocument @iDOM OUTPUT, @lBCKxml if @iret > 0 BEGIN exec sp_xml_removedocument @iDOM --IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI RAISERROR('解析药房置换XML文档时发生错误，错误号: %d.', 16, 1, @iret) with nowait return @iret END INSERT INTO @Tmp_BCK(BCK01,BCK01A) SELECT BCK01,BCK01A from OpenXml(@iDOM,'/Root/BCK1/Ie',8) WITH (BCK01 int, BCK01A int) exec sp_xml_removedocument @iDOM If not exists(select * from @TmpVAFcz) BEGIN --IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI RETURN 1 END UPDATE a SET a.BDA01 = b.BDA01,a.VAF01A = b.VAF01A,a.Rownr = b.ROWNR,a.CBM01 = b.CBM01,a.VAF10 = b.VAF10 ,a.BCK01B = b.BCK01B,a.BCK01C = b.BCK01C,a.BCK01D = b.BCK01D,a.BCE02A = b.BCE02A,a.BCE03A = b.BCE03A ,a.VAF32 = b.VAF32,a.VAF58 = b.VAF58,a.ACF01 = b.VAF04,a.VAF18 = b.VAF18,a.BCE01A = b.BCE01A ,a.VAF21 = b.VAF21,a.VAA01 = b.VAA01,a.VAA07 = b.VAF06,a.VAP01 = b.VAF07,a.BCK01A = b.BCK01A ,a.VAF35 = b.VAF35,a.VAF22 = b.VAF22,a.VAF23 = b.VAF23 ,a.BCK01E = c.BCK01D ,a.VAF36 = b.VAF36,a.BCK01F = c.BCK01C,a.ABC02 = c.ABC02,a.VAF14 = b.VAF14,a.BBX01 = b.BBX01 ,a.VAF31 = b.VAF31 ,a.VAJ57 = d.BBX05,a.VAF01B = b.VAF01B,a.VAF38 = b.VAF38,a.VAF27 = b.VAF27 ,a.VAF28 = b.VAF28, a.VAF29 = b.VAF29,a.VAF37 = b.VAF37,a.VAF61 = b.VAF61,a.VAF62 = case when @lOneDay = 2 then a.VAF62 else b.VAF62 end ,a.aVAF38 = b.VAF38,a.VAF47 = b.VAF47,a.VAF50 = b.VAF50,a.BCE01D = b.BCE01D,a.BCE03D = b.BCE03D ,a.BCE01E =case when @lOneDay = 2 then a.BCE01E else b.BCE01E end ,a.BCE03E = case when @lOneDay = 2 then a.BCE03E else b.BCE03E end ,a.BCE01F = b.BCE01F,a.BCE03F = b.BCE03F,a.VAF34 = isnull(b.VAF34,0) ,a.VAF75 = b.VAF75,a.bck01c_br = c.BCK01C,a.bck01d_br = c.BCK01D,a.VBI13 = 0,a.VAF15=b.VAF15 FROM @TmpVAFcz a JOIN VAF2 b ON b.VAF01 = a.VAF01 JOIN VAE1 c on c.VAE01 = b.VAF06 left join BBX1 d on d.BBX01 = b.VAF01B Insert into @TmpVAFcz(VAF01, BDA01 ,VAF01A, Rownr, CBM01,VAF10,BCK01B , BCK01C ,BCK01D ,BCE02A ,BCE03A ,VAF32 ,VAF58,ACF01,VAF18,VAF21,VAA01,VAA07,VAP01,BCK01A,VAF35,VAF22,VAF36,BCK01F,ABC02 ,VAF14,BCE01A,BBX01,VAF31,VAJ57,VAF01B,VAF37,VAF38,VAF27,VAF28,VAF29,VAF61,VAF62,aVAF38 ,VAF47,VAF50,BCE01D,BCE03D,BCE01E,BCE03E,BCE01F,BCE03F,VAF34,VAF75,BCK01E,VAF23,bck01c_br,bck01d_br,VBI13,VAF15) Select a.VAF01, a.BDA01 ,a.VAF01A, a.Rownr, a.CBM01,a.VAF10,a.BCK01B ,a.BCK01C ,a.BCK01D ,a.BCE02A ,a.BCE03A,a.VAF32 ,a.VAF58,a.VAF04,a.VAF18,a.VAF21,a.VAA01,a.VAF06,a.VAF07,a.BCK01A,a.VAF35 ,a.VAF22,a.VAF36,c.BCK01C,c.ABC02,a.VAF14,a.BCE01A,a.BBX01,a.VAF31,d.BBX05,a.VAF01B ,a.VAF37,a.VAF38,a.VAF27,a.VAF28,a.VAF29,a.VAF61,case when @lOneDay = 2 then b.VAF62 else a.VAF62 end ,a.VAF38 ,a.VAF47,a.VAF50,a.BCE01D,a.BCE03D ,case when @lOneDay = 2 then b.BCE01E else a.BCE01E end,case when @lOneDay = 2 then b.BCE03E else a.BCE03E end ,a.BCE01F,a.BCE03F,a.VAF34,a.VAF75,c.BCK01D,a.VAF23,c.BCK01C,c.BCK01D,0,a.VAF15 From @TmpVAFcz b Join VAF2 a on a.VAF01 = b.VAF01A join VAE1 c on c.VAE01 = a.VAF06 left join BBX1 d on d.BBX01 = a.VAF01B Where b.BDA01 >= '1' And b.BDA01 <= '2' And b.Rownr = 1 and a.vaf32=1 and a.vaf01a=0 Union All Select a.VAF01, a.BDA01 ,a.VAF01A, a.Rownr, a.CBM01,a.VAF10,a.BCK01B ,a.BCK01C ,a.BCK01D ,a.BCE02A ,a.BCE03A,a.VAF32 ,a.VAF58,a.VAF04,a.VAF18,a.VAF21,a.VAA01,a.VAF06,a.VAF07,a.BCK01A,a.VAF35 ,a.VAF22,a.VAF36,c.BCK01C,c.ABC02,a.VAF14,a.BCE01A,a.BBX01,a.VAF31,d.BBX05,a.VAF01B ,a.VAF37,a.VAF38,a.VAF27,a.VAF28,a.VAF29,a.VAF61,case when @lOneDay = 2 then b.VAF62 else a.VAF62 end,a.VAF38 ,a.VAF47,a.VAF50,a.BCE01D,a.BCE03D ,case when @lOneDay = 2 then b.BCE01E else a.BCE01E end,case when @lOneDay = 2 then b.BCE03E else a.BCE03E end ,a.BCE01F,a.BCE03F,a.VAF34,a.VAF75,c.BCK01D,a.VAF23,c.BCK01C,c.BCK01D,0,a.VAF15 From @TmpVAFcz b Join VAF2 a on a.VAF01A = b.VAF01 join VAE1 c on a.VAF06 = c.VAE01 left join BBX1 d on d.BBX01 = a.VAF01B Where b.BDA01 >='8' and a.VAF01A>0 and a.VAF32<>1 IF @@ERROR <> 0 begin --IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI RAISERROR('从数据中提取医嘱明细信息错误.', 16, 1) with nowait RETURN 2 end update a set a.VAF01B = b.BBX01,a.VAJ57B = '.'+b.BBX05 from @TmpVAFcz a join BBX1 b on b.BBX01 = a.BBX01 where a.BDA01 = 'L' and b.BBX12 = 1 and b.BDA01 = 'L' and a.VAF01A = 0 declare @Para53 varchar(10),@para209 varchar(20),@para262 varchar(20),@para414 varchar(20) declare @Para140 varchar(10),@gpara90 varchar(10),@para429 varchar(20) set @para209 = dbo.GetSysParamValue(100,105001,209) set @para262 = dbo.GetSysParamValue(100,105003,262) set @Para53 = dbo.GetSysParamValue(9999,9999,53) set @para414 = dbo.GetSysParamValue(100,105001,414) set @Para140 = dbo.GetSysParamValue(100,105001,140) set @gpara90 = dbo.GetSysParamValue(9999,9999,90) set @para429 = dbo.GetSysParamValue(100,105001,429) if @para414='0' or @para414='' set @para414 = ' 09:00:00' else set @para414 = ' ' +@para414 if @Para53 = '1' begin update a set a.BBX17 = Case when a.BDA01 >= '1' and a.BDA01 <= '3' then 1 else ISNULL(b.BBX17,1) end from @TmpVAFcz a join BBX1 b on b.BBX01 = a.BBX01 update @TmpVAFcz set BBX17 = 1 where BDA01='0' end else begin update @TmpVAFcz set BBX17 = 1 end --请假离院病人医嘱不计费 update a set a.BBX17 = 9 from @TmpVAFcz a join VAE1 b on b.VAE01 = a.VAA07 where b.ABV01 = '99' and @para209 = '1' if @para140 = '1' and exists(select * from @TmpVAFcz where (BDA01='0' or BDA01 > '4') and VAF32 =0 and (BCK01B=bck01c_br or BCK01B=bck01d_br)) begin update a set a.VBI13 = 1 from @TmpVAFcz a join bbx1 b on b.BBX01=a.BBX01 where (a.BDA01='0' or a.BDA01 > '4') and a.VAF32 =0 and (a.BCK01B=a.bck01c_br or a.BCK01B=a.bck01d_br) and not (a.BDA01='Z' AND b.BBX13 ='5') end Create TABLE #tmpBDA (VAF01 int,VAF01A int,VAF36 datetime,VAF38 datetime,FDay int,IsFirst int,VAF28 tinyint ,VAF29 varchar(4),BDA01 varchar(2),VEH07 varchar(20) ) Create Table #TmpVAJ (ID int IDENTITY(1,1),VAJ01 int,VAA01 int,VAA07 int,ROWNR int,VAF01 int,ACF01 tinyint ,BCK01A int,BCK01B int,BDN01 varchar(2),BBY01 int,VAJ23 int,VAJ24 numeric(18,4),VAJ25 numeric(18,4) ,VAJ26 tinyint,VAJ27 int,BCK01C int,BCE02B varchar(20),BCE03B varchar(20),BCK01D int,VAJ53 tinyint ,VAJ54 varchar(255),VAJ57 varchar(1024),BCK01E int,VAI01 int,CBM01 int,ABF01 varchar(8) ,ABC02 varchar(20),VAJ34 numeric(18,4),VAJ59 numeric(18,4),VAJ39 tinyint,VAJ35 varchar(20) ,VAJ32 numeric(18,4),vaj30 numeric(9,4),vaj31 numeric(9,4),vaj33 numeric(18,4),vaj36 numeric(18,2) ,vaj37 numeric(18,2),vaj38 numeric(18,2),vaj61 numeric(18,2),VAJ01A int,DSK01 int,FAB03 varchar(20),ABC01 tinyint,BCK18 tinyint,VAJ40 numeric(18,4) ,VAJ64 datetime,BCE01E int,BCE03E varchar(20),BCK01F int,BCQ04 varchar(20),BAG46 numeric(18,4),VAJ72 tinyint default(0) ,VEG04 varchar(64),BDA01 varchar(2) ) Create Table #tmpVAI (ID int IDENTITY(1,1), VAI01 int, VAI04 varchar(20),VAA01 int,VAA07 int ,BCK01B int,BCE02 varchar(20),BCE03 varchar(20),CBM01 int,ACF01 tinyint,VAI22 numeric(18,4),VAI23 numeric(18,4),BCE01C int,BCE03C varchar(20)) Declare @tmpDPT Table(uid int IDENTITY(1,1),BCK01 int,BBY01 int,DSK01 int,VAJ25 numeric(18,4)) Declare @lStop tinyint, @eVAF38 varchar(20),@BBY31 datetime,@curDate datetime,@curtime varchar(20) declare @aVAIid int, @aVAIMax int, @aStr varchar(20), @smsg varchar(2560) Set @aStr = REPLICATE('0',10) set @curDate = GETDATE() set @curtime = substring(CONVERT(char(23),getdate(),121),11,13) set @BBY31 = Convert(varchar(10),@curDate,121)+' 23:59:59.999' Set @eVAF38 = Convert(varchar(10),@lEndDate,21) set @smsg = '' update a set a.VEH07=ISNULL(b.VEH07,'') from @TmpVAFcz a join VEH2 b on b.VAF01=a.VAF01 set datefirst 1 --执行天数、及日期 Insert Into #tmpBDA(VAF01,VAF01A,VAF36,VAF38,FDay,IsFirst,VAF28,VAF29,BDA01,VEH07) SELECT a.VAF01,a.VAF01A ,CASE WHEN a.VAF38 < a.VAF36 THEN a.VAF36 ELSE case when (a.VAF29 <> 'D')OR(a.VAF29 = 'D' and a.VAF28 <= 1) then CONVERT(varchar(10),a.VAF38+1,21)+@para414 when a.VAF29 = 'D' and a.VAF28 >= 2 then CONVERT(varchar(10),a.VAF38+a.VAF28,21)+@para414 end END AS VAF36 ,CASE WHEN DATEDIFF(DAY,a.VAF36,a.VAF37) >= 0 AND a.VAF37<=@lEndDate THEN a.VAF37 ELSE case when (a.VAF29 <> 'D')OR(a.VAF29 = 'D' and a.VAF28 <= 1) then @lEndDate when a.VAF29 = 'D' and a.VAF28 >= 2 then CONVERT(varchar(10),a.VAF36+Floor(cast(DATEDIFF(day,a.VAF36,@eVAF38) as numeric(18,2))/cast(isnull(a.VAF28,1) as numeric(18,2))*cast(isnull(a.VAF28,1) as numeric(18,2))),21)+' 23:59:59' end END AS VAF38,1 AS FDay ,CASE WHEN isnull(a.VAF38,'1899-12-30')<=a.VAF36 THEN 1 ELSE 0 END AS IsFirst,a.VAF28,a.VAF29,a.BDA01,a.VEH07 FROM @TmpVAFcz a IF @lOneDay = 1 UPDATE #tmpBDA SET FDay = 1 ELSE begin if @lOneDay = 2 update #tmpBDA set FDay = 1 ,IsFirst = 2 else begin if Exists(Select * from #tmpBDA where DateDiff(Day,VAF36,VAF38)<0) begin -- IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI RAISERROR('存在医嘱发送的终止时间小于起始时间，请返回重新选择过滤条件，在发送医嘱！', 16, 1) with nowait RETURN 3 end UPDATE #tmpBDA SET FDay = DATEDIFF(day,VAF36,VAF38)+1 where VAF29 <> 'D' or (VAF29 = 'D' and VAF28 <= 1) UPDATE #tmpBDA SET FDay = Floor((DATEDIFF(day,VAF36,VAF38)+VAF28)/VAF28) where VAF29 = 'D' and VAF28 >= 2 UPDATE #tmpBDA SET VAF38 = CONVERT(varchar(10),DATEADD(day,(FDay-1)*VAF28,VAF36),21)+' 23:59:59' where VAF29 = 'D' and VAF28 >= 2 --2017.7.1 增加每周几次按指定礼拜几执行 update a set a.FDay=(select fday from dbo.GetBDI1_BDI14(a.VAF01,a.VEH07,a.VAF36,@lEndDate,0)) ,a.VAF38=(select fVAF38 from dbo.GetBDI1_BDI14(a.VAF01,a.VEH07,a.VAF36,@lEndDate,0)) ,a.VAF36=(select fVAF36 from dbo.GetBDI1_BDI14(a.VAF01,a.VEH07,a.VAF36,@lEndDate,0)) from #tmpBDA a where a.VEH07>'' and a.VAF28>1 and a.VAF29='D' end end UPDATE a set a.IsFirst = 0 from #tmpBDA a join @TmpVAFcz b on b.VAF01 = a.VAF01 where @para262 = '0' and b.BDA01 >= '4' and b.BDA01 <> '8' and b.VAF32 = 0 and a.IsFirst <> 2 --提取医嘱计价信息 Create Table #tmpVBD(uid int IDENTITY(1,1),VBD01 int ,VAF01 int ,BBY01 int ,VBD04 numeric(18,4) ,VBD05 numeric(18,4) ,VBD06 tinyint,BCK01 int,VBD08 tinyint ,VBD09 tinyint ,BDN01 varchar(2),VAJ32 numeric(18,6),VAJ24 numeric(18,4),VAJ25 numeric(18,4),VAJ23 tinyint,VAJ59 numeric(18,6),VAJ34 numeric(18,4) ,VAJ35 varchar(20),VBD12 numeric(18,4),VAJ64 datetime,BBY01A int,jzbs tinyint default(0),VAJ72 tinyint default(0) ) --执行记录 create table #tmpVBI(ID int IDENTITY(1,1),VBI01 int,VAF01 int,VBI05 int,VBI06 int,VBI07 int,VBI10 datetime,VBI11 datetime,BCK01 int,VAA01 int,VAA07 int,VAP01 int ,VBI13 tinyint) --判断药品禁用 if exists(select a.* from @TmpVAFcz a join VBD2 b on b.VAF01 = a.VAF01 join BBY1 c on c.BBY01 = b.BBY01 join #tmpBDA d on d.VAF01 = a.VAF01 where b.VBD04 >= 0 and c.BBY31 <= @BBY31 and d.IsFirst <> 2 and a.BDA01 >= '1' and a.BDA01 <= '3' and a.VAF58 <> 1 ) begin select @smsg =@smsg +char(13)+(d.VAA05+'：'+ replace(isnull(c.BBY05,'')+isnull(c.BBY06,''),'%','％')) from @TmpVAFcz a join VBD2 b on b.VAF01 = a.VAF01 join BBY1 c on c.BBY01 = b.BBY01 join VAA1 d on a.VAA01 = d.VAA01 join #tmpBDA e on e.VAF01 = a.VAF01 where b.VBD04 >= 0 and c.BBY31 <= @BBY31 and e.IsFirst <> 2 and a.BDA01 >= '1' and a.BDA01 <= '3' and a.VAF58 <> 1 -- IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI set @smsg =@smsg +char(13)+'--以上药品已过有效期或已禁用，不能继续使用.' RAISERROR(@smsg, 16, 1) with nowait RETURN 4 end select b.* into #tmpvbdex from @TmpVAFcz a join VBD2 b on b.VAF01 = a.VAF01 where b.VBD04 >= 0 and (@gpara90 <> '1' or (@gpara90='1' and a.BDA01 > '3' and a.VAF32 <> 1 )) insert into #tmpVBD(VBD01,VAF01,BBY01,VBD04,VBD05,VBD06,BCK01,VBD08,VBD09,BDN01,VAJ32,VAJ35,VBD12,BBY01A ) select distinct s.VBD01,s.VAF01,s.BBY01,s.VBD04,s.VBD05,s.VBD06,s.BCK01,s.VBD08,s.VBD09,s.BDN01,s.BBY25,s.BBY08,s.VBD12,s.BBY01A from ( select b.VBD01,b.VAF01,b.BBY01,b.VBD04,b.VBD05,b.VBD06,b.BCK01,b.VBD08,b.VBD09,c.BDN01,c.BBY25,c.BBY08,b.VBD12 ,a.VAF58,a.BDA01,a.VAF01A,a.BCK01B,a.BCK01E,a.BCK01F,b.BBY01A from @TmpVAFcz a join #tmpvbdex b on b.VAF01 = a.VAF01 join BBY1 c on c.BBY01 = b.BBY01 where c.BBY31 > @BBY31 /* --2017.7.20 当套餐明细中包含的收费项目仍为套餐，这一段会造成重复收费 and isnull(c.BBY34,0)<>2 union all select b.VBD01,b.VAF01,e.BBY01,b.VBD04*d.BCR04,b.VBD05,b.VBD06,b.BCK01,b.VBD08,b.VBD09,e.BDN01 ,Case when d.BCR10 = 1 then e.BBY25 else d.BCR07 end BBY25 ,e.BBY08,b.VBD12 ,a.VAF58,a.BDA01,a.VAF01A,a.BCK01B,a.BCK01E,a.BCK01F,b.BBY01A from @TmpVAFcz a join #tmpvbdex b on b.VAF01 = a.VAF01 join BBY1 c on c.BBY01 = b.BBY01 join BCR1 d on d.BBY01A = b.BBY01 join BBY1 e with(nolock) on e.BBY01 = d.BBY01B where c.BBY31 > @BBY31 and c.bby34=2 and d.BCR09 > @BBY31 and e.BBY31 > @BBY31 */ ) s where ((s.BDA01 >= '1' and s.BDA01 <= '3' and s.VAF58 <> 1) or(s.BDA01='0') or (s.BDA01 >= '4' and s.VAF58 <> 2 and (@para429='1' or (@para429<>'1' and not exists(SELECT e.* FROM @TmpVAFcz e JOIN BAZ1 f ON e.BCK01B = f.BCK01 WHERE e.BDA01 = 'S' And e.VAF01A = 0 And f.BAU01 = '03' AND f.ACF01 IN (2,3) and (s.VAF01 = e.VAF01 or s.VAF01A = e.VAF01) )) ) and (not (exists(select a1.* from #tmpvbdex a1 join BBY1 b1 on a1.BBY01=b1.BBY01 join AAG1 c1 on b1.BCF01=c1.AAG01 join @TmpVAFcz d1 on d1.VAF01 = a1.VAF01 where d1.BDA01 = 'N' and b1.BDN01='N' and c1.AAG01<=7 and d1.VAF01=s.VAF01 and d1.VAP01 = 0) and exists(select * from BEO1 f1 where (f1.BCK01 = s.BCK01B or (f1.BCK01 = s.BCK01F and s.BCK01B = s.BCK01E)) and f1.BEO04 = -2 and DateDiff(day,f1.BEO06,@curDate)>= 0))) ) ) order by s.VAF01 select a.vaa07,a.vaf01,a.bby01,a.vaj46,a.vaj64 into #kbmVAJpg from VAJ2 a join (select distinct b.VAA07,c.BBY01 from @TmpVAFcz b join #tmpVBD c on c.VAF01 = b.VAF01 where b.VAF32 = 1 and b.BDA01 = 'T' and c.VBD09 = 2) d on d.VAA07 = a.VAA07 and d.BBY01 = a.BBY01 where a.ACF01 = 2 and a.VAK01 = 0 select a.BBY01,MAX(b.VAF27) sVAF27,0 VAF01 into #kbmBDU10 from #tmpVBD a join @TmpVAFcz b on b.VAF01 = a.VAF01 where b.BDA01 = 'T' and b.VAF32 = 1 and a.VBD09 = 1 group by a.BBY01 update m set m.VAF01 = n.svaf01 from #kbmBDU10 m join ( select a.BBY01,MIN(b.VAF01) svaf01 from #tmpVBD a join #kbmBDU10 c on c.BBY01 = a.BBY01 join @TmpVAFcz b on b.VAF01 = a.VAF01 and b.VAF27 = c.sVAF27 where b.BDA01 = 'T' and b.VAF32 = 1 and a.VBD09 = 1 group by a.BBY01) n on n.BBY01 = m.BBY01 delete a from #tmpVBD a join @TmpVAFcz b on b.VAF01 = a.VAF01 join #kbmBDU10 c on c.bby01 = a.bby01 where b.BDA01 = 'T' and b.VAF32 = 1 and a.VBD09 = 1 and not exists(select * from #kbmBDU10 d where d.VAF01 = a.VAF01) declare @mDay int,@anum int select a.VBD01,a.VAF01,b.BBX01,a.BBY01,b.bda01,a.bdn01,b.vaf32,a.VBD09,b.VAA07 ,VAJ64 = case when c.IsFirst = 2 then c.VAF36 else case when DateDiff(Day,c.VAF36,c.VAF38) > 0 then Convert(varchar(10),c.VAF38,21)+@curtime else c.VAF36 end end,0 jzbs into #kbmjzbs from #tmpVBD a join @TmpVAFcz b on b.VAF01 = a.VAF01 join #tmpBDA c on c.VAF01 = b.VAF01 join BAG1 e on e.BBY01 = a.BBY01 where (b.BDA01 >= '4' and b.VAF32 = 1 and a.BDN01 <= '3') and ((c.IsFirst = 2 and b.VAF62 > 0) or (c.IsFirst <= 1 and c.FDay <= 1)) union all select a.VBD01,a.VAF01,b.BBX01,a.BBY01,b.bda01,a.bdn01,b.vaf32,a.VBD09,b.VAA07 ,VAJ64 = case when c.IsFirst = 2 then c.VAF36 else case when DateDiff(Day,c.VAF36,c.VAF38) > 0 then Convert(varchar(10),c.VAF38,21)+@curtime else c.VAF36 end end ,0 jzbs from #tmpVBD a join @TmpVAFcz b on b.VAF01 = a.VAF01 join #tmpBDA c on c.VAF01 = b.VAF01 where (b.BDA01 >= '4' and b.VAF32 = 1 and a.BDN01 >= '4') and ((c.IsFirst = 2 and b.VAF62 > 0) or (c.IsFirst <= 1 and c.FDay <= 1)) set @mDay = ISNULL((select MAX(b.Fday) from @TmpVAFcz a join #tmpBDA b on b.VAF01 = a.VAF01 where b.IsFirst <= 1 and b.FDay >= 2),0) if @mDay >= 2 begin set @anum = 1 while @anum <= @mDay begin insert into #kbmjzbs(VBD01,VAF01,BBX01,BBY01,bda01,bdn01,vaf32,vbd09,VAJ64,jzbs,VAA07 ) select a.VBD01,a.VAF01,b.BBX01,a.BBY01,b.bda01,a.bdn01,b.vaf32,a.VBD09 ,VAJ64 = case when @anum > 1 then (case when b.VAF29 <> 'D' OR (b.VAF29 = 'D' and b.VAF28 <= 1) then Convert(varchar(10),(c.VAF36 + (@anum - 1)),21)+@curtime else case when c.VEH07>'' then (select s1.fvaf36 from dbo.GetBDI1_BDI14(c.VAF01,c.VEH07,c.VAF36,c.VAF38,1) s1 where s1.fday=@anum ) else Convert(varchar(10),(c.VAF36 + (@anum - 1)* b.VAF28),21)+@curtime end end ) else c.VAF36 end,0,b.VAA07 from #tmpVBD a join @TmpVAFcz b on b.VAF01 = a.VAF01 join #tmpBDA c on c.VAF01 = b.VAF01 join BAG1 e on e.BBY01 = a.BBY01 where (b.BDA01 >= '4' and b.VAF32 = 1 and a.BDN01 <= '3') and (c.IsFirst <= 1 and c.FDay >= 2 and c.FDay >= @anum) union all select a.VBD01,a.VAF01,b.BBX01,a.BBY01,b.bda01,a.bdn01,b.vaf32,a.VBD09 ,VAJ64 = case when @anum > 1 then (case when b.VAF29 <> 'D' OR (b.VAF29 = 'D' and b.VAF28 <= 1) then Convert(varchar(10),(c.VAF36 + (@anum - 1)),21)+@curtime else case when c.VEH07>'' then (select s1.fvaf36 from dbo.GetBDI1_BDI14(c.VAF01,c.VEH07,c.VAF36,c.VAF38,1) s1 where s1.fday=@anum ) else Convert(varchar(10),(c.VAF36 + (@anum - 1)* b.VAF28),21)+@curtime end end ) else c.VAF36 end,0,b.VAA07 from #tmpVBD a join @TmpVAFcz b on b.VAF01 = a.VAF01 join #tmpBDA c on c.VAF01 = b.VAF01 where (b.BDA01 >= '4' and b.VAF32 = 1 and a.BDN01 >= '4') and (c.IsFirst <= 1 and c.FDay >= 2 and c.FDay >= @anum) set @anum = @anum + 1 end end update a set a.jzbs = 9 from #kbmjzbs a where a.VAF32 = 1 and a.BDA01 = 'T' and a.VBD09 = 2 and Exists(select * from #kbmVAJpg d where d.VAA07 = a.VAA07 and d.BBY01 = a.BBY01 and DateDiff(Day,a.VAJ64,d.VAJ64) = 0) select CONVERT(varchar(10),a.vaj64,121) vaj64,a.VAA07,a.bby01,MIN(a.vaf01) svaf01 into #kbmvafbs from #kbmjzbs a where a.VAF32 = 1 and a.BDA01 = 'T' and a.VBD09 = 2 and a.jzbs = 0 group by CONVERT(varchar(10),a.vaj64,121),a.VAA07,a.bby01 update a set a.jzbs = 1 from #kbmjzbs a where a.VAF32 = 1 and a.BDA01 = 'T' and a.VBD09 = 2 and a.jzbs = 0 and Exists(select * from #kbmvafbs d where d.VAA07 = a.VAA07 and d.BBY01 = a.BBY01 and DateDiff(Day,a.VAJ64,d.VAJ64) = 0 and d.svaf01 = a.VAF01) update #kbmjzbs set jzbs=9 where jzbs <> 1 and vbd09 = 2 update a set a.jzbs = 1 from #kbmjzbs a where a.vbd09 = 3 and a.VAF32 = 1 and a.BDA01 = 'T' and Exists(select * from #kbmjzbs d where d.VAA07 = a.VAA07 and d.bbx01 = a.bbx01 and d.vbd09=2 and d.jzbs = 9 and DateDiff(Day,a.VAJ64,d.VAJ64) = 0 and d.vaf01 = a.VAF01) update a set a.jzbs = 9 from #kbmjzbs a where a.vbd09 = 3 and a.VAF32 = 1 and a.BDA01 = 'T' and Exists(select * from #kbmjzbs d where d.VAA07 = a.VAA07 and d.bbx01 = a.bbx01 and d.vbd09=2 and d.jzbs = 1 and DateDiff(Day,a.VAJ64,d.VAJ64) = 0 and d.vaf01 = a.VAF01) update #kbmjzbs set jzbs=1 where jzbs <> 9 and vbd09 = 3 if OBJECT_ID('tempdb..#kbmVAJpg') is not null drop table #kbmVAJpg if OBJECT_ID('tempdb..#kbmBDU10') is not null drop table #kbmBDU10 -- select * into #kbmVBD from #tmpVBD truncate table #tmpVBD insert into #tmpVBD(VBD01,VAF01,BBY01,VBD04,VBD05,VBD06,BCK01,VBD08,VBD09,BDN01,VAJ32,VAJ35,VBD12,VAJ25,VAJ23,VAJ34,VAJ59,VAJ64,bby01a,VAJ72 ) select a.VBD01,a.VAF01,a.BBY01,a.VBD04,a.VBD05,a.VBD06,a.BCK01,a.VBD08,a.VBD09,a.BDN01,a.VAJ32,a.VAJ35,a.VBD12 ,VAJ25 = case when a.BDN01 <= '2' then ( CASE WHEN c.IsFirst = 0 THEN CEILING(cast(a.VBD04*e.BAG07* c.FDay * b.VAF27 AS NUMERIC(18,4))) WHEN c.IsFirst = 1 THEN CEILING(cast(a.VBD04*e.BAG07*(b.VAF61+(c.FDay-1)*b.VAF27) AS NUMERIC(18,4))) WHEN c.IsFirst = 2 THEN CEILING(cast(a.VBD04*e.BAG07*b.VAF62 AS NUMERIC(18,4))) END ) else ( CASE WHEN c.IsFirst = 0 THEN a.VBD04*e.BAG07*c.FDay WHEN c.IsFirst = 1 THEN a.VBD04*e.BAG07*c.FDay WHEN c.IsFirst = 2 THEN a.VBD04*e.BAG07*c.FDay END ) end ,VAJ23 = case when a.BDN01 = '3' then (CASE WHEN c.IsFirst = 0 THEN c.FDay*b.VAF21 WHEN c.IsFirst = 1 THEN (c.FDay)*b.VAF21 WHEN c.IsFirst = 2 THEN b.VAF21*c.FDay END) else 1 end ,VAJ34 = e.BAG07,0 ,VAJ64 = case when c.IsFirst = 2 then c.VAF36 else case when DateDiff(Day,c.VAF36,c.VAF38) > 0 then Convert(varchar(10),c.VAF38,21)+@curtime else c.VAF36 end end ,a.BBY01A,VAJ72 = case when c.IsFirst = 2 then 2 when c.IsFirst=1 then 1 else 0 end from #kbmVBD a join @TmpVAFcz b on b.VAF01 = a.VAF01 join #tmpBDA c on c.VAF01 = b.VAF01 join BAG1 e on e.BBY01 = a.BBY01 where (b.BDA01 >= '1' and b.BDA01 <= '3' and a.BDN01 <= '3') and ((c.IsFirst = 2 and b.VAF62 > 0) or (c.IsFirst <= 1 and c.FDay <= 1)) union all select s.VBD01,s.VAF01,s.BBY01,s.VBD04,s.VBD05,s.VBD06,s.BCK01,s.VBD08,s.VBD09,s.BDN01,s.VAJ32,s.VAJ35,s.VBD12 ,VAJ25 = case when s.BDN01 <= '2' then ( CASE WHEN s.IsFirst = 0 THEN case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2 and t.bby01 = s.bby01 and t.jzbs=1) then CEILING(cast(s.VBD04*s.VAJ34* s.FDay AS NUMERIC(18,4))) when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3 and t.bby01 = s.bby01 and t.jzbs=9) then CEILING(cast(s.VBD04*s.VAJ34* s.FDay*(s.VAF27-1) AS NUMERIC(18,4))) else CEILING(cast(s.VBD04*s.VAJ34* s.FDay * s.VAF27 AS NUMERIC(18,4))) end WHEN s.IsFirst = 1 THEN case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2 and t.bby01 = s.bby01 and t.jzbs=1) then CEILING(cast(s.VBD04*s.VAJ34 AS NUMERIC(18,4))) when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3 and t.bby01 = s.bby01 and t.jzbs=9) then CEILING(cast(s.VBD04*s.VAJ34*(s.VAF61-1) AS NUMERIC(18,4))) else CEILING(cast(s.VBD04*s.VAJ34*s.VAF61 AS NUMERIC(18,4))) end WHEN s.IsFirst = 2 THEN case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2 and t.bby01 = s.bby01 and t.jzbs=1) then CEILING(cast(s.VBD04*s.VAJ34 AS NUMERIC(18,4))) when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3 and t.bby01 = s.bby01 and t.jzbs=9) then CEILING(cast(s.VBD04*s.VAJ34*(s.VAF62-1) AS NUMERIC(18,4))) else CEILING(cast(s.VBD04*s.VAJ34*s.VAF62 AS NUMERIC(18,4))) end END ) else ( CASE WHEN s.IsFirst = 0 THEN s.VBD04*s.VAJ34*s.FDay WHEN s.IsFirst = 1 THEN s.VBD04*s.VAJ34*s.FDay WHEN s.IsFirst = 2 THEN s.VBD04*s.VAJ34*s.FDay END ) end ,VAJ23 = case when s.BDN01 = '3' then (CASE WHEN s.IsFirst = 0 THEN s.FDay*s.VAF21 WHEN s.IsFirst = 1 THEN (s.FDay)*s.VAF21 WHEN s.IsFirst = 2 THEN s.VAF21*s.FDay END) else 1 end ,s.VAJ34,s.VAJ59,s.VAJ64,s.BBY01A ,VAJ72 = case when s.IsFirst = 2 then 2 when s.IsFirst=1 then 1 else 0 end from ( select a.VBD01,a.VAF01,a.BBY01,a.VBD04,a.VBD05,a.VBD06,a.BCK01,a.VBD08,a.VBD09,a.BDN01,a.VAJ32,a.VAJ35,a.VBD12 ,VAJ34 = e.BAG07,0 VAJ59 ,VAJ64 = case when c.IsFirst = 2 then c.VAF36 else case when DateDiff(Day,c.VAF36,c.VAF38) > 0 then Convert(varchar(10),c.VAF38,21)+@curtime else c.VAF36 end end ,a.BBY01A,c.IsFirst,c.FDay,b.VAF21,b.VAF61,b.VAF62,b.VAF27,b.BBX01 from #kbmVBD a join @TmpVAFcz b on b.VAF01 = a.VAF01 join #tmpBDA c on c.VAF01 = b.VAF01 join BAG1 e on e.BBY01 = a.BBY01 where (b.BDA01 >= '4' and b.VAF32 = 1 and a.BDN01 <= '3') and ((c.IsFirst = 2 and b.VAF62 > 0) or (c.IsFirst <= 1 and c.FDay <= 1)) ) s where not exists(select * from #kbmjzbs s1 where s1.vbd09=2 and s1.jzbs=9 and s1.vaf01 = s.vaf01 and DateDiff(Day,s1.VAJ64,s.VAJ64) = 0 and s1.bby01 = s.bby01) union all select a.VBD01,a.VAF01,a.BBY01,a.VBD04,a.VBD05,a.VBD06,a.BCK01,a.VBD08,a.VBD09,a.BDN01,a.VAJ32,a.VAJ35,a.VBD12 ,VAJ25 = case when b.BDA01 >= 'A' and b.BDA01 <> 'M' and c.IsFirst = 1 and ISNULL(a.VBD12,0) > 0 then (b.VAF61 * a.VBD12 + (c.FDay-1)*a.VBD04) when b.BDA01 >= 'A' and b.BDA01 <> 'M' and c.IsFirst = 2 and ISNULL(a.VBD12,0) > 0 then b.VAF62 * a.VBD12 else a.VBD04 * c.FDay end ,VAJ23 = 1 ,VAJ34 = e.BAG07,0 ,VAJ64 = case when c.IsFirst = 2 then c.VAF36 else case when DateDiff(Day,c.VAF36,c.VAF38) > 0 then Convert(varchar(10),c.VAF38,21)+@curtime else c.VAF36 end end ,a.BBY01A,VAJ72 = case when c.IsFirst = 2 then 2 when c.IsFirst=1 then 1 else 0 end from #kbmVBD a join @TmpVAFcz b on b.VAF01 = a.VAF01 join #tmpBDA c on c.VAF01 = b.VAF01 join BAG1 e on e.BBY01 = a.BBY01 where (b.BDA01 >= '4' or b.BDA01='0') and b.VAF32 <> 1 and a.BDN01 <= '3' and ((c.IsFirst = 2 and b.VAF62 > 0) or (c.IsFirst <= 1 and c.FDay <= 1)) union all select s.VBD01,s.VAF01,s.BBY01,s.VBD04,s.VBD05,s.VBD06,s.BCK01,s.VBD08,s.VBD09,s.BDN01,s.VAJ32,s.VAJ35,s.VBD12 ,VAJ25 = Case when s.BDA01 = '8' then (CASE WHEN s.IsFirst = 0 THEN s.VBD04*s.FDay WHEN s.IsFirst = 1 THEN s.VBD04*(s.FDay) WHEN s.IsFirst = 2 THEN s.VBD04*s.FDay END ) else (CASE WHEN s.IsFirst = 0 THEN (case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2 and t.bby01 = s.bby01 and t.jzbs=1) then s.VBD04*s.FDay when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3 and t.bby01 = s.bby01 and t.jzbs=9) then s.VBD04*s.FDay*(s.VAF27-1) else s.VBD04*s.FDay*s.VAF27 end ) WHEN s.IsFirst = 1 THEN (case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2 and t.bby01 = s.bby01 and t.jzbs=1) then s.VBD04 when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3 and t.bby01 = s.bby01 and t.jzbs=9) then s.VBD04*(s.VAF61-1) else s.VBD04*s.VAF61 end ) WHEN s.IsFirst = 2 THEN (case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2 and t.bby01 = s.bby01 and t.jzbs=1) then s.VBD04 when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3 and t.bby01 = s.bby01 and t.jzbs=9) then s.VBD04*(s.VAF62-1) else s.VBD04*s.VAF62 end ) END ) end ,s.VAJ23,s.VAJ34,s.VAJ59,s.VAJ64,s.BBY01A ,VAJ72 = case when s.IsFirst = 2 then 2 when s.IsFirst=1 then 1 else 0 end from ( select a.VBD01,a.VAF01,a.BBY01,a.VBD04,a.VBD05,a.VBD06,a.BCK01,a.VBD08,a.VBD09,a.BDN01,a.VAJ32,a.VAJ35,a.VBD12 ,VAJ23 = 1 ,VAJ34 = null,VAJ59 = a.VAJ32 ,VAJ64 = case when c.IsFirst = 2 then c.VAF36 else case when DateDiff(Day,c.VAF36,c.VAF38) > 0 then Convert(varchar(10),c.VAF38,21)+@curtime else c.VAF36 end end ,a.BBY01A,c.IsFirst,c.FDay,b.VAF21,b.VAF61,b.VAF62,b.VAF27,b.BBX01,b.BDA01 from #kbmVBD a join @TmpVAFcz b on b.VAF01 = a.VAF01 join #tmpBDA c on c.VAF01 = b.VAF01 where (b.BDA01 >= '4' and b.VAF32 = 1 and a.BDN01 >= '4') and ((c.IsFirst = 2 and b.VAF62 > 0) or (c.IsFirst <= 1 and c.FDay <= 1)) ) s where not exists(select * from #kbmjzbs s1 where s1.vbd09=2 and s1.jzbs=9 and s1.vaf01 = s.vaf01 and DateDiff(Day,s1.VAJ64,s.VAJ64) = 0 and s1.bby01 = s.bby01) union all select a.VBD01,a.VAF01,a.BBY01,a.VBD04,a.VBD05,a.VBD06,a.BCK01,a.VBD08,a.VBD09,a.BDN01,a.VAJ32,a.VAJ35,a.VBD12 ,VAJ25 = Case when b.BDA01 = '8' then (CASE WHEN c.IsFirst = 0 THEN a.VBD04*c.FDay WHEN c.IsFirst = 1 THEN a.VBD04*(c.FDay) WHEN c.IsFirst = 2 THEN a.VBD04*c.FDay END ) else (case when a.VBD09 = 2 then (CASE WHEN c.IsFirst = 2 THEN a.VBD04 else a.VBD04*c.FDay END) else (CASE WHEN c.IsFirst = 0 THEN a.VBD04*c.FDay*b.VAF27 WHEN c.IsFirst = 1 THEN a.VBD04*(b.VAF61+(c.FDay-1)*b.VAF27) WHEN c.IsFirst = 2 THEN a.VBD04*b.VAF62 END) end ) end ,VAJ23 = 1 ,VAJ34 = null,VAJ59 = a.VAJ32 ,VAJ64 = case when c.IsFirst = 2 then c.VAF36 else case when DateDiff(Day,c.VAF36,c.VAF38) > 0 then Convert(varchar(10),c.VAF38,21)+@curtime else c.VAF36 end end ,a.BBY01A,VAJ72 = case when c.IsFirst = 2 then 2 when c.IsFirst=1 then 1 else 0 end from #kbmVBD a join @TmpVAFcz b on b.VAF01 = a.VAF01 join #tmpBDA c on c.VAF01 = b.VAF01 where (b.BDA01>='1' and b.BDA01 <= '3' and a.BDN01 >= '4') and ((c.IsFirst = 2 and b.VAF62 > 0) or (c.IsFirst <= 1 and c.FDay <= 1)) union all select a.VBD01,a.VAF01,a.BBY01,a.VBD04,a.VBD05,a.VBD06,a.BCK01,a.VBD08,a.VBD09,a.BDN01,a.VAJ32,a.VAJ35,a.VBD12 ,VAJ25 = case when b.BDA01 >= 'A' and b.BDA01 <> 'M' and c.IsFirst = 1 and ISNULL(a.VBD12,0) > 0 then (b.VAF61 * a.VBD12 + (c.FDay-1)*a.VBD04) when b.BDA01 >= 'A' and b.BDA01 <> 'M' and c.IsFirst = 2 and ISNULL(a.VBD12,0) > 0 then (b.VAF62 * a.VBD12 ) when b.BDA01 in ('4','M') and c.IsFirst = 2 then b.VAF62 * a.VBD04 else a.VBD04 * c.FDay end ,VAJ23 = 1 ,VAJ34 = null,VAJ59 = a.VAJ32 ,VAJ64 = case when c.IsFirst = 2 then c.VAF36 else case when DateDiff(Day,c.VAF36,c.VAF38) > 0 then Convert(varchar(10),c.VAF38,21)+@curtime else c.VAF36 end end ,a.BBY01A,VAJ72 = case when c.IsFirst = 2 then 2 when c.IsFirst=1 then 1 else 0 end from #kbmVBD a join @TmpVAFcz b on b.VAF01 = a.VAF01 join #tmpBDA c on c.VAF01 = b.VAF01 where ((b.BDA01 >= '4' or b.BDA01='0') and b.VAF32 <> 1 and a.BDN01 >= '4') and ((c.IsFirst = 2 and b.VAF62 > 0) or (c.IsFirst <= 1 and c.FDay <= 1)) set @mDay = ISNULL((select MAX(b.Fday) from @TmpVAFcz a join #tmpBDA b on b.VAF01 = a.VAF01 where b.IsFirst <= 1 and b.FDay >= 2),0) if @mDay >= 2 begin set @anum = 1 while @anum <= @mDay begin insert into #tmpVBD(VBD01,VAF01,BBY01,VBD04,VBD05,VBD06,BCK01,VBD08,VBD09,BDN01,VAJ32,VAJ35,VBD12,VAJ25,VAJ23,VAJ34,VAJ59,VAJ64,BBY01A,VAJ72 ) select a.VBD01,a.VAF01,a.BBY01,a.VBD04,a.VBD05,a.VBD06,a.BCK01,a.VBD08,a.VBD09,a.BDN01,a.VAJ32,a.VAJ35,a.VBD12 ,VAJ25 = case when a.BDN01 <= '2' then ( CASE WHEN c.IsFirst = 1 and @anum = 1 THEN CEILING(cast(a.VBD04*e.BAG07*b.VAF61 AS NUMERIC(18,4))) else CEILING(cast(a.VBD04*e.BAG07* b.VAF27 AS NUMERIC(18,4))) END ) else a.VBD04*e.BAG07 end ,VAJ23 = case when a.BDN01 = '3' then b.VAF21 else 1 end ,VAJ34 = e.BAG07,0 ,VAJ64 = case when @anum > 1 then (case when b.VAF29 <> 'D' OR (b.VAF29 = 'D' and b.VAF28 <= 1) then Convert(varchar(10),(c.VAF36 + (@anum - 1)),21)+@curtime else case when c.VEH07>'' then (select Convert(varchar(10),s1.fvaf36,21)+@curtime from dbo.GetBDI1_BDI14(c.VAF01,c.VEH07,c.VAF36,c.VAF38,1) s1 where s1.fday=@anum ) else Convert(varchar(10),(c.VAF36 + (@anum - 1)* b.VAF28),21)+@curtime end end ) else c.VAF36 end,a.BBY01A ,VAJ72 = case when c.IsFirst=1 and @anum=1 then 1 else 0 end from #kbmVBD a join @TmpVAFcz b on b.VAF01 = a.VAF01 join #tmpBDA c on c.VAF01 = b.VAF01 join BAG1 e on e.BBY01 = a.BBY01 where (b.BDA01 >= '1' and b.BDA01 <= '3' and a.BDN01 <= '3')and c.FDay >= 2 and c.FDay >= @anum and ((c.IsFirst =0 )or (c.IsFirst = 1 and (@anum=1 or (@anum>=2 and b.vaf75=0)) ) ) union all select s.VBD01,s.VAF01,s.BBY01,s.VBD04,s.VBD05,s.VBD06,s.BCK01,s.VBD08,s.VBD09,s.BDN01,s.VAJ32,s.VAJ35,s.VBD12 ,VAJ25 = case when s.BDN01 <= '2' then ( CASE WHEN s.IsFirst = 1 and @anum = 1 THEN (case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2 and t.bby01 = s.bby01 and t.jzbs=1) then CEILING(cast(s.VBD04*s.BAG07 AS NUMERIC(18,4))) when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3 and t.bby01 = s.bby01 and t.jzbs=9) then CEILING(cast(s.VBD04*s.BAG07*(s.VAF61-1) AS NUMERIC(18,4))) else CEILING(cast(s.VBD04*s.BAG07*s.VAF61 AS NUMERIC(18,4))) end ) else (case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2 and t.bby01 = s.bby01 and t.jzbs=1) then CEILING(cast(s.VBD04*s.BAG07 AS NUMERIC(18,4))) when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3 and t.bby01 = s.bby01 and t.jzbs=9) then CEILING(cast(s.VBD04*s.BAG07*(s.VAF27-1) AS NUMERIC(18,4))) else CEILING(cast(s.VBD04*s.BAG07*s.VAF27 AS NUMERIC(18,4))) end ) END ) else s.VBD04*s.BAG07 end ,s.VAJ23,s.VAJ34,s.VAJ59,s.VAJ64,s.BBY01A ,VAJ72 = case when s.IsFirst=1 and @anum=1 then 1 else 0 end from ( select a.VBD01,a.VAF01,a.BBY01,a.VBD04,a.VBD05,a.VBD06,a.BCK01,a.VBD08,a.VBD09,a.BDN01,a.VAJ32,a.VAJ35,a.VBD12 ,VAJ23 = case when a.BDN01 = '3' then b.VAF21 else 1 end ,VAJ34 = e.BAG07,0 VAJ59 ,VAJ64 = case when @anum > 1 then (case when b.VAF29 <> 'D' OR (b.VAF29 = 'D' and b.VAF28 <= 1) then Convert(varchar(10),(c.VAF36 + (@anum - 1)),21)+@curtime else case when c.VEH07>'' then (select Convert(varchar(10),s1.fvaf36,21)+@curtime from dbo.GetBDI1_BDI14(c.VAF01,c.VEH07,c.VAF36,c.VAF38,1) s1 where s1.fday=@anum ) else Convert(varchar(10),(c.VAF36 + (@anum - 1)* b.VAF28),21)+@curtime end end ) else c.VAF36 end,a.BBY01A,c.IsFirst,c.FDay,b.VAF21,b.VAF61,b.VAF62,b.VAF27,b.BBX01,b.BDA01,e.BAG07 from #kbmVBD a join @TmpVAFcz b on b.VAF01 = a.VAF01 join #tmpBDA c on c.VAF01 = b.VAF01 join BAG1 e on e.BBY01 = a.BBY01 where (b.BDA01 >= '4' and b.VAF32 = 1 and a.BDN01 <= '3') and (c.IsFirst <= 1 and c.FDay >= 2 and c.FDay >= @anum) ) s where not exists(select * from #kbmjzbs s1 where s1.vbd09=2 and s1.jzbs=9 and s1.vaf01 = s.vaf01 and DateDiff(Day,s1.VAJ64,s.VAJ64) = 0 and s1.bby01 = s.bby01) union all select a.VBD01,a.VAF01,a.BBY01,a.VBD04,a.VBD05,a.VBD06,a.BCK01,a.VBD08,a.VBD09,a.BDN01,a.VAJ32,a.VAJ35,a.VBD12 ,VAJ25 = case when b.BDA01 >= 'A' and b.BDA01 <> 'M' and c.IsFirst = 1 and @anum = 1 and ISNULL(a.VBD12,0) > 0 then (b.VAF61 * a.VBD12) else a.VBD04 end ,VAJ23 = 1 ,VAJ34 = e.BAG07,0 ,VAJ64 = case when @anum > 1 then (case when b.VAF29 <> 'D' OR (b.VAF29 = 'D' and b.VAF28 <= 1) then Convert(varchar(10),(c.VAF36 + (@anum - 1)),21)+@curtime else case when c.VEH07>'' then (select Convert(varchar(10),s1.fvaf36,21)+@curtime from dbo.GetBDI1_BDI14(c.VAF01,c.VEH07,c.VAF36,c.VAF38,1) s1 where s1.fday=@anum ) else Convert(varchar(10),(c.VAF36 + (@anum - 1)* b.VAF28),21)+@curtime end end ) else c.VAF36 end ,a.BBY01A ,VAJ72 = case when c.IsFirst=1 and @anum=1 then 1 else 0 end from #kbmVBD a join @TmpVAFcz b on b.VAF01 = a.VAF01 join #tmpBDA c on c.VAF01 = b.VAF01 join BAG1 e on e.BBY01 = a.BBY01 where (b.BDA01 >= '4' or b.BDA01='0') and b.VAF32 <> 1 and a.BDN01 <= '3' and (c.IsFirst <= 1 and c.FDay >= 2 and c.FDay >= @anum) union all select s.VBD01,s.VAF01,s.BBY01,s.VBD04,s.VBD05,s.VBD06,s.BCK01,s.VBD08,s.VBD09,s.BDN01,s.VAJ32,s.VAJ35,s.VBD12 ,VAJ25 = case when s.BDA01 = '8' then s.vbd04 else (case WHEN s.IsFirst = 1 and @anum = 1 THEN (case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2 and t.bby01 = s.bby01 and t.jzbs=1) then s.VBD04 when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3 and t.bby01 = s.bby01 and t.jzbs=9) then s.VBD04*(s.VAF61-1) else s.VBD04*s.VAF61 end ) else (case when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=2 and t.bby01 = s.bby01 and t.jzbs=1) then s.VBD04 when exists(select * from #kbmjzbs t where t.VAF01 = s.VAF01 and DateDiff(Day,t.VAJ64,s.VAJ64) = 0 and t.vbd09=3 and t.bby01 = s.bby01 and t.jzbs=9) then s.VBD04*(s.VAF27-1) else s.VBD04*s.VAF27 end ) END) end ,s.VAJ23,s.VAJ34,s.VAJ59,s.VAJ64,s.BBY01A ,VAJ72 = case when s.IsFirst=1 and @anum=1 then 1 else 0 end from ( select a.VBD01,a.VAF01,a.BBY01,a.VBD04,a.VBD05,a.VBD06,a.BCK01,a.VBD08,a.VBD09,a.BDN01,a.VAJ32,a.VAJ35,a.VBD12 ,VAJ23 = 1 ,VAJ34 = null,VAJ59 = a.VAJ32 ,VAJ64 = case when @anum > 1 then (case when b.VAF29 <> 'D' OR (b.VAF29 = 'D' and b.VAF28 <= 1) then Convert(varchar(10),(c.VAF36 + (@anum - 1)),21)+@curtime else case when c.VEH07>'' then (select Convert(varchar(10),s1.fvaf36,21)+@curtime from dbo.GetBDI1_BDI14(c.VAF01,c.VEH07,c.VAF36,c.VAF38,1) s1 where s1.fday=@anum ) else Convert(varchar(10),(c.VAF36 + (@anum - 1)* b.VAF28),21)+@curtime end end ) else c.VAF36 end ,a.BBY01A,c.IsFirst,c.FDay,b.VAF21,b.VAF61,b.VAF62,b.VAF27,b.BBX01,b.BDA01 from #kbmVBD a join @TmpVAFcz b on b.VAF01 = a.VAF01 join #tmpBDA c on c.VAF01 = b.VAF01 where (b.BDA01 >= '4' and b.VAF32 = 1 and a.BDN01 >= '4') and (c.IsFirst <= 1 and c.FDay >= 2 and c.FDay >= @anum) ) s where not exists(select * from #kbmjzbs s1 where s1.vbd09=2 and s1.jzbs=9 and s1.vaf01 = s.vaf01 and DateDiff(Day,s1.VAJ64,s.VAJ64) = 0 and s1.bby01 = s.bby01) union all select a.VBD01,a.VAF01,a.BBY01,a.VBD04,a.VBD05,a.VBD06,a.BCK01,a.VBD08,a.VBD09,a.BDN01,a.VAJ32,a.VAJ35,a.VBD12 ,VAJ25 = Case when b.BDA01 = '8' then a.VBD04 else (case when a.VBD09 = 2 then a.VBD04 else (CASE WHEN c.IsFirst = 1 and @anum = 1 THEN a.VBD04*b.VAF61 else a.VBD04*b.VAF27 END) end ) end ,VAJ23 = 1 ,VAJ34 = null,VAJ59 = a.VAJ32 ,VAJ64 = case when @anum > 1 then (case when b.VAF29 <> 'D' OR (b.VAF29 = 'D' and b.VAF28 <= 1) then Convert(varchar(10),(c.VAF36 + (@anum - 1)),21)+@curtime else case when c.VEH07>'' then (select Convert(varchar(10),s1.fvaf36,21)+@curtime from dbo.GetBDI1_BDI14(c.VAF01,c.VEH07,c.VAF36,c.VAF38,1) s1 where s1.fday=@anum ) else Convert(varchar(10),(c.VAF36 + (@anum - 1)* b.VAF28),21)+@curtime end end ) else c.VAF36 end ,a.BBY01A ,VAJ72 = case when c.IsFirst=1 and @anum=1 then 1 else 0 end from #kbmVBD a join @TmpVAFcz b on b.VAF01 = a.VAF01 join #tmpBDA c on c.VAF01 = b.VAF01 where (b.BDA01>='1' and b.BDA01 <= '3' and a.BDN01 >= '4') and (c.IsFirst <= 1 and c.FDay >= 2 and c.FDay >= @anum) union all select a.VBD01,a.VAF01,a.BBY01,a.VBD04,a.VBD05,a.VBD06,a.BCK01,a.VBD08,a.VBD09,a.BDN01,a.VAJ32,a.VAJ35,a.VBD12 ,VAJ25 = case when b.BDA01 >= 'A' and b.BDA01 <> 'M' and c.IsFirst = 1 and @anum = 1 and ISNULL(a.VBD12,0) > 0 then (b.VAF61 * a.VBD12) else a.VBD04 end ,VAJ23 = 1 ,VAJ34 = null,VAJ59 = a.VAJ32 ,VAJ64 = case when @anum > 1 then (case when b.VAF29 <> 'D' OR (b.VAF29 = 'D' and b.VAF28 <= 1) then Convert(varchar(10),(c.VAF36 + (@anum - 1)),21)+@curtime else case when c.VEH07>'' then (select Convert(varchar(10),s1.fvaf36,21)+@curtime from dbo.GetBDI1_BDI14(c.VAF01,c.VEH07,c.VAF36,c.VAF38,1) s1 where s1.fday=@anum ) else Convert(varchar(10),(c.VAF36 + (@anum - 1)* b.VAF28),21)+@curtime end end ) else c.VAF36 end ,a.BBY01A,VAJ72 = case when c.IsFirst=1 and @anum=1 then 1 else 0 end from #kbmVBD a join @TmpVAFcz b on b.VAF01 = a.VAF01 join #tmpBDA c on c.VAF01 = b.VAF01 where ((b.BDA01 >= '4' or b.BDA01='0') and b.VAF32 <> 1 and a.BDN01 >= '4') and (c.IsFirst <= 1 and c.FDay >= 2 and c.FDay >= @anum) set @anum = @anum + 1 end end --判断没有计价内容药品医嘱，不允许发送 if @gpara90 <> '1' and exists(select * from @TmpVAFcz a where a.VAF58 <> 1 and a.BDA01 >='1' and a.BDA01 <= '3' and a.vaf21 > 0.00001 and (@lOneDay <> 2 or (@lOneDay = 2 and a.VAF62 > 0)) and not exists(select * from #tmpVBD b where b.VAF01 = a.VAF01)) begin set @smsg = (select top 1 a.VAF22 as fname from @TmpVAFcz a where a.VAF58 <> 1 and a.BDA01 >='1' and a.BDA01 <= '3' and (@lOneDay <> 2 or (@lOneDay = 2 and a.VAF62 > 0)) and not exists(select * from #tmpVBD b where b.VAF01 = a.VAF01)) --IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI set @smsg = '【'+@smsg+'】此医嘱没有计价内容，不能发送，请先联系管理员.' RAISERROR(@smsg, 16, 1) with nowait RETURN 6 end delete from #tmpVBD where isnull(VAJ25,0) <= 0 delete a from #tmpVBD a join @TmpVAFcz b on b.VAF01 = a.VAF01 where (b.BBX17 = 9) or (b.BDA01 >= '1' and b.BDA01 <= '2' and b.VAF75 = 1 and b.VAF34 = 1) if exists(select * from @TmpVAFcz where BDA01 >= '1' and BDA01 <= '3') and exists(select * from @Tmp_BCK) begin update a set a.BCK01 = b.BCK01A from #tmpVBD a join @Tmp_BCK b on b.BCK01 = a.BCK01 join @TmpVAFcz c on c.VAF01 = a.VAF01 where c.BDA01 >= '1' and c.BDA01 <= '3' and a.BDN01 <= '3' end Insert Into #TmpVAJ(VAA01,VAA07,Rownr,VAF01,ACF01,BCK01A,BCK01B,BDN01,BBY01,VAJ23,VAJ24,VAJ25 ,VAJ26,VAJ27,BCK01C,BCE02B,BCE03B,BCK01D,VAJ53,VAJ54,VAJ57,BCK01E,CBM01,ABF01,ABC02,VAJ39 ,VAJ32,VAJ01A,VAJ34,VAJ35,VAJ59,ABC01,BCK18,VAJ64,BCK01F,BCQ04,VAJ72,BDA01) select a.VAA01,a.VAA07,a.Rownr,a.VAF01,a.ACF01,a.BCK01F,a.BCK01A,b.BDN01,b.BBY01,b.VAJ23,b.VAJ25,b.VAJ25 ,a.VAF35,a.VAP01,a.BCK01C,a.BCE02A,a.BCE03A,b.BCK01 ,case when a.VBI13=1 and (b.BCK01 = a.bck01c_br or b.bck01 = a.bck01d_br) then 1 else 0 end VAJ53 ,case when @lOneDay = 2 then '长嘱确认停止' else '长嘱发送' end VAJ54 ,case when b.BBY01A > 0 or a.VAF01B >0 then isnull(a.VAJ57,'')+'.'+isnull(d.BBY05,'')+ISNULL(a.VAJ57B,'') else a.VAJ57 end ,a.BCK01D,a.CBM01,c.ABF01,a.ABC02,a.VAF31 ,b.VAJ32,a.VAF01B,b.VAJ34,b.VAJ35,b.VAJ59,0,9 ,b.VAJ64,0,'' ,b.VAJ72,a.BDA01 from @TmpVAFcz a join #tmpVBD b on b.VAF01 = a.VAF01 join BBY1 c on c.BBY01 = b.BBY01 left join BBY1 d on d.BBY01 = b.BBY01A UPDATE a set a.ABC01 = 1 from #tmpVAJ a join ABC1 b on b.ABC02 = a.ABC02 where b.ACF01 in (2,3) and DATEDIFF(DAY,b.ABC12,@curDate)>=0 and DATEDIFF(DAY,@curDate,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.BCK01B = 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 #tmpVAJ 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 #tmpVAJ a join BCK1 c on c.BCK01 = a.BCK01D where a.BDN01 >='1' and a.BDN01 <= '3' and a.BCK18 = 9 update a set a.FAB03 = b.BDG02B,a.VAJ59 = case when isnull(b.BAG23,0) > 0.000001 then b.BAG23 else c.BBY25 end from #TmpVAJ a join BAG1 b on b.BBY01 = a.BBY01 join BBY1 c on c.BBY01 = a.BBY01 where a.BDN01 >= '1' and a.BDN01 <= '3' select a.BCK01,a.BBY01,min(a.DSK_ID) DSK01 into #kbmDpt from DPT1 a join (select BCK01D BCK01,BBY01 from #tmpVAJ 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.VAJ32 = case when a.BCK18 = 1 and isnull(b.BAG23,0) > 0.000001 then b.BAG23 else a.VAJ32 end,a.DSK01 = d.DSK01 from #tmpVAJ a join BAG1 b on b.BBY01 = a.BBY01 left join #kbmDpt d on d.BCK01 = a.BCK01D and d.BBY01 = a.BBY01 where a.BDN01 >='1' and a.BDN01 <= '3' update a set a.VAJ32 = case when a.BCK18 = 1 then c.PurchasePrice else c.PresellPrice end,a.DSK01 = b.DSK01,a.VAJ59 = c.PurchasePrice from #tmpVAJ a join #kbmDpt b on b.BCK01 = a.BCK01D 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 BBY1 e on e.BBY01 = a.BBY01 where a.BDN01 >='1' and a.BDN01 <= '3' and (e.BBY19 = 1 or (e.BBY19 = 0 and a.BCK18 = 1)) update a set a.VAJ30 = CASE WHEN f1.BBY01 IS NOT NULL THEN f1.ACV08 WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN g1.ACV08 ELSE 100 END ,a.VAJ31 = CASE WHEN f1.BBY01 IS NOT NULL THEN f1.ACV09 WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN g1.ACV09 ELSE 100 END ,a.VAJ33 = CASE WHEN f1.BBY01 IS NOT NULL THEN (a.VAJ32*f1.ACV08)/f1.ACV09 WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.VAJ32*g1.ACV08)/g1.ACV09 ELSE a.VAJ32 END ,a.VAJ36 = a.VAJ25 * a.VAJ32 ,a.VAJ37 = CASE WHEN f1.BBY01 IS NOT NULL THEN (a.VAJ25 * a.VAJ32 * f1.ACV08)/f1.ACV09 WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.VAJ25 * a.VAJ32 * g1.ACV08)/g1.ACV09 ELSE a.VAJ25 * a.VAJ32 END ,a.VAJ38 = CASE WHEN f1.BBY01 IS NOT NULL THEN (a.VAJ25 * a.VAJ32 * f1.ACV08)/f1.ACV09 WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.VAJ25 * a.VAJ32 * g1.ACV08)/g1.ACV09 ELSE a.VAJ25 * a.VAJ32 END ,a.VAJ61 = CASE WHEN f1.BBY01 IS NOT NULL THEN (a.VAJ25 * a.VAJ32 * f1.ACV08)/f1.ACV09 WHEN f1.BBY01 IS NULL AND g1.BCH01 IS NOT NULL THEN (a.VAJ25*a.VAJ32*g1.ACV08)/g1.ACV09 ELSE a.VAJ25*a.VAJ32 END FROM #tmpVAJ a JOIN BBY1 c ON a.BBY01 = c.BBY01 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.VAJ32*a.VAJ25) AND f1.ACV07>=(a.VAJ32*a.VAJ25) 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 c.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.VAJ32*a.VAJ25) AND g1.ACV07>=(a.VAJ32*a.VAJ25) -- insert into #tmpVAI(VAA01,VAA07,BCK01B,BCE02,BCE03,CBM01,ACF01,VAI22,VAI23,BCE01C,BCE03C) select b.VAA01,b.VAA07,b.BCK01C,c.BCE02,c.BCE03,a.CBM01,2,a.FVAJ36,a.FVAJ38,e.BCE01,e.BCE03 from (select e.CBM01,(e.FVAJ36 - ISNULL(f.FVAJ36,0)) FVAJ36 ,(e.FVAJ38 - ISNULL(f.FVAJ38,0)) FVAJ38 from (select CBM01,SUM(vaj36) FVAJ36,SUM(vaj38) FVAJ38 from #TmpVAJ group by CBM01) e left join (select m.CBM01,SUM(m.vaj36) FVAJ36,SUM(m.vaj38) FVAJ38 from #TmpVAJ m where exists(select * from @TmpVAFcz n where n.VAF01 = m.VAF01 and n.BBX17 = 3) group by m.CBM01) f on f.CBM01 = e.CBM01 )a join CBM2 b on b.CBM01 = a.CBM01 join VAE1 d on d.VAE01 = b.VAA07 left join BCE1 c on c.BCE01 = b.BCE01 left join BCE1 e on e.BCE02 = d.BCE02C Select @aVAIMax = Max(ID) From #tmpVAI set @aVAIMax = ISNULL(@aVAIMax,0) Exec Core_NewIDEX 'VAI1','VAI01',@aVAIid out, @aVAIMax Set @aVAIid = @aVAIid - @aVAIMax Update #tmpVAI Set VAI01 = @aVAIid + ID, VAI04 = 'CZ'+substring(@aStr,1,10 -len(@aVAIid + ID))+cast((@aVAIid + ID) AS varchar) update a set a.VAI01 = b.VAI01,a.BCE01E=b.BCE01C,a.BCE03E = b.BCE03C from #TmpVAJ a join #tmpVAI b on b.CBM01 = a.CBM01 update a set a.VAI01 = b.VAI01 from @TmpVAFcz a join #tmpVAI b on b.CBM01 = a.CBM01 update a set a.BCK01F = c.BCK01A,a.BCQ04 = b.BCQ04B From #tmpVAJ a join VAE1 b on b.VAE01 = a.VAA07 join BCQ1 c on c.VAA01 = b.VAA01 and c.BCQ04 = b.BCQ04B and c.BCQ13 in (1,3) if Exists(Select * from #TmpVAJ where isnull(VAI01,0)=0) begin --IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI RAISERROR('费用明细中单据ID不能为空值.', 16, 1) with nowait RETURN 5 end select @ReVAIid = case when @ReVAIid = '' then cast(VAI01 as varchar) else @ReVAIid+','+cast(VAI01 as varchar) end from #tmpVAI where isnull(VAI01,'')<>'' select @ReVafid_hz = case when @ReVafid_hz = '' then cast(a.VAF01 as varchar) else @ReVafid_hz+','+cast(a.VAF01 as varchar) end from @TmpVAFcz a join bbx1 b on b.BBX01=a.BBX01 where a.BDA01='Z' and b.BBX13 ='7' select @ReVafid_zk = case when @ReVafid_zk = '' then cast(a.VAF01 as varchar) else @ReVafid_zk+','+cast(a.VAF01 as varchar) end from @TmpVAFcz a join bbx1 b on b.BBX01=a.BBX01 where a.BDA01='Z' and b.BBX13 ='5' Select @aVAIid = 0, @aVAIMax = 0 Select @aVAIMax = Max(ID) From #tmpVAJ set @aVAIMax = ISNULL(@aVAIMax,0) Exec Core_NewIDEX 'VAJ1','VAJ01',@aVAIid out, @aVAIMax Set @aVAIid = @aVAIid - @aVAIMax Update #tmpVAJ Set VAJ01 = @aVAIid + ID,BAG46 = 0 Select @aVAIid = 0, @aVAIMax = 0 Select @aVAIMax = Max(ID) From @TmpVAFcz set @aVAIMax = ISNULL(@aVAIMax,0) Exec Core_NewIDEX 'VBG1','VBG01',@aVAIid out, @aVAIMax Set @aVAIid = @aVAIid - @aVAIMax Update @TmpVAFcz Set VBG01 = @aVAIid + ID update a SET a.VAF10 = 9, a.BCE03E =case when a.BCE01E>0 then a.BCE03E else @lBCE03 end ,a.BCE01E =case when a.BCE01E > 0 then a.BCE01E else @lBCE01 end ,a.BCE03F = @lBCE03,a.BCE01F = @lBCE01, a.VAF50 = @curDate from @TmpVAFcz a join #tmpBDA b on b.VAF01 = a.VAF01 where b.IsFirst = 2 update a SET a.VAF10 = 8,a.aVAF38 = a.VAF37 ,a.BCE03D = a.BCE03A,a.BCE01D = a.BCE01A,a.BCE03E = @lBCE03,a.BCE01E = @lBCE01 ,a.VAF47 = case when @curDate > a.VAF37 then @curDate else a.VAF37 end from @TmpVAFcz a join #tmpBDA b on b.VAF01 = a.VAF01 where b.IsFirst <= 1 and datediff(day,a.vaf36,a.vaf37) >= 0 AND a.VAF37 <= b.VAF38 update a SET a.VAF10 = 3,a.aVAF38 = b.VAF38 from @TmpVAFcz a join #tmpBDA b on b.VAF01 = a.VAF01 where b.IsFirst <= 1 and ((datediff(day,a.vaf36,a.vaf37) < 0) OR(datediff(day,a.vaf36,a.vaf37) >= 0 AND a.VAF37 > b.VAF38)) --****** insert into #tmpVBI(VBI05,VBI06,VBI07,VBI10,VBI11,BCK01,VAF01,VAA01,VAA07,VAP01,VBI13) select a.VAI01,a.Rownr,case when a.BDA01 >= 'A' and a.VAF32 = 0 then a.VAF21 else case when b.IsFirst = 2 then a.VAF62 when b.IsFirst = 1 and (a.bda01 in ('1','2') or (a.bda01 = 'T' and a.vaf32=1)) then a.VAF61 else a.VAF27 end end ,case when b.IsFirst = 2 then b.VAF36 else case when DateDiff(Day,b.VAF36,b.VAF38) > 0 then Convert(varchar(10),b.VAF38,21)+@para414 else b.VAF36 end end ,b.VAF38,a.BCK01B,a.VAF01,a.VAA01,a.VAA07,a.VAP01,a.VBI13 from @TmpVAFcz a join #tmpBDA b on b.VAF01 = a.VAF01 where ((b.IsFirst = 2 and a.VAF62 > 0.00001) or (b.IsFirst <= 1 and b.FDay <= 1)) and a.BBX17 <> 9 set @mDay = ISNULL((select MAX(b.Fday) from @TmpVAFcz a join #tmpBDA b on b.VAF01 = a.VAF01 where b.IsFirst <= 1 and b.FDay >= 2),0) if @mDay >= 2 begin set @anum = 1 while @anum <= @mDay begin insert into #tmpVBI(VBI05,VBI06,VBI07,VBI10,VBI11,BCK01,VAF01,VAA01,VAA07,VAP01,VBI13) select a.VAI01,a.Rownr,case when a.BDA01 >= 'A' and a.VAF32 = 0 then a.VAF21 else case when b.IsFirst = 1 and @anum = 1 then a.VAF61 else a.VAF27 end end ,case when @anum > 1 then (case when a.VAF29 <> 'D' OR (a.VAF29 = 'D' and a.VAF28 <= 1) then Convert(varchar(10),(b.VAF36 + (@anum - 1)),21)+@para414 else case when b.VEH07>'' then (select Convert(varchar(10),s1.fvaf36,21)+@para414 from dbo.GetBDI1_BDI14(b.VAF01,b.VEH07,b.VAF36,b.VAF38,1) s1 where s1.fday=@anum ) else Convert(varchar(10),(b.VAF36 + (@anum - 1)* a.VAF28),21)+@para414 end end ) else b.VAF36 end ,Convert(varchar(10),(case when @anum > 1 then case when a.VAF29 <> 'D' OR (a.VAF29 = 'D' and a.VAF28 <= 1) then Convert(varchar(10),(b.VAF36 + (@anum - 1)),21)+@para414 else case when b.VEH07>'' then (select Convert(varchar(10),s1.fvaf36,21)+@para414 from dbo.GetBDI1_BDI14(b.VAF01,b.VEH07,b.VAF36,b.VAF38,1) s1 where s1.fday=@anum ) else Convert(varchar(10),(b.VAF36 + (@anum - 1)* a.VAF28),21)+@para414 end end else b.VAF36 end),21)+' 23:59:00' ,a.BCK01B,a.VAF01,a.VAA01,a.VAA07,a.VAP01,a.VBI13 from @TmpVAFcz a join #tmpBDA b on b.VAF01 = a.VAF01 where (b.IsFirst <= 1 and b.FDay >= 2 and b.FDay >= @anum) and a.BBX17 <> 9 set @anum = @anum + 1 end end Select @aVAIid = 0, @aVAIMax = 0 Select @aVAIMax = Max(ID) From #tmpVBI set @aVAIMax = ISNULL(@aVAIMax,0) Exec Core_NewIDEX 'VBI1','VBI01',@aVAIid out, @aVAIMax Set @aVAIid = @aVAIid - @aVAIMax Update #tmpVBI Set VBI01 = @aVAIid + ID --*** declare @kbmVAT table(ID int IDENTITY(1,1),VAT01 int,VAF01 int) Declare @tmpVAV Table(ID int IDENTITY(1,1),VAT01 int,VAV01 int,VAV05 varchar(128),BBX01 int,VAV03 tinyint,VAV04 tinyint,VAV09 tinyint,ACI02 varchar(30)) if exists(SELECT a.* FROM @TmpVAFcz a JOIN BAZ1 b ON a.BCK01B = b.BCK01 WHERE a.BDA01 = 'S' and a.BBX17 <> 9 and a.VAF01A = 0 AND b.BAU01 = '03' AND b.ACF01 IN (2,3)) begin insert into @kbmVAT(VAF01) select a.VAF01 FROM @TmpVAFcz a WHERE a.BDA01 = 'S' and a.VAF01A = 0 and a.BBX17 <> 9 and exists(select * from BAZ1 b where b.BCK01 = a.BCK01B AND b.BAU01 = '03' AND b.ACF01 IN (2,3) ) Select @aVAIid = 0, @aVAIMax = 0 Select @aVAIMax = Max(ID) From @kbmVAT set @aVAIMax = ISNULL(@aVAIMax,0) EXEC Core_NewIDEx 'CBM1','CBM01',@aVAIid out, @aVAIMax Set @aVAIid = @aVAIid - @aVAIMax Update @kbmVAT Set VAT01 = @aVAIid + ID insert into @tmpVAV(VAT01,VAV05,BBX01,VAV03,VAV04,VAV09,ACI02) select a.VAT01,c.BBX05,b.BBX01,1,0,0,'' from @kbmVAT a join @TmpVAFcz b on b.VAF01 = a.VAF01 join BBX1 c on c.BBX01 = b.BBX01 where b.BDA01 = 'S' union all select a.VAT01,c.BBX05,b.BBX01,0,0,0,'' from @kbmVAT a join @TmpVAFcz b on b.VAF01A = a.VAF01 join BBX1 c on c.BBX01 = b.BBX01 where b.VAF01A > 0 and c.BDA01 ='S' and b.VAF15<='' union all select a.VAT01,c.BBX05,b.BBX01,0,0,1,d.ACI02 from @kbmVAT a join @TmpVAFcz b on b.VAF01A = a.VAF01 join BBX1 c on c.BBX01 = b.BBX01 left join ACI1 d on d.ACI01 = c.BBX13 where b.VAF01A > 0 and c.BDA01 ='A' Select @aVAIid = 0, @aVAIMax = 0 Select @aVAIMax = MAX(ID) From @tmpVAV EXEC Core_NewIDEx 'VAV1','VAV01',@aVAIid out, @aVAIMax Set @aVAIid = @aVAIid - @aVAIMax UPDATE @tmpVAV set VAV01 = @aVAIid + ID end --***** insert into @tmpDPT(BCK01,BBY01,DSK01,VAJ25) select a.BCK01D,a.BBY01,a.DSK01,SUM(a.VAJ25) from #TmpVAJ a where a.BDN01 >= '1' and a.BDN01 <= '3' group by a.BCK01D,a.BBY01,a.DSK01 declare @BBY01 int,@BCK01 int,@DSK01 int,@VAJ25 numeric(18,4) --**** --判断已发送医嘱 if Exists(select * FROM @TmpVAFcz a, VAF2 b WHERE a.VAF01=b.VAF01 AND b.VAF10 <> 3 and @lOneDay <= 1) begin --IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI RAISERROR('已有医嘱被作废或停止，请返回重新选择过滤条件，在发送医嘱！', 16, 1) with nowait RETURN 2 end if exists(select * from @TmpVAFcz a join VAF2 b on b.VAF01 = a.VAF01 where DATEDIFF(day,@lEndDate,b.VAF38) >= 0 and @lOneDay <= 1) begin -- IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI RAISERROR('已有医嘱被作废或已执行，请返回重新选择过滤条件，再发送医嘱！', 16, 1) with nowait RETURN 3 end --药品如果禁售，就不允许在发药 if exists(select * from #tmpVBD a join BAL1 b on b.BCK01 = a.BCK01 and b.BBY01 = a.BBY01 where b.BAL08 = 1) begin set @smsg = (select top 1 c.BBY05 from #tmpVBD a join BAL1 b on b.BCK01 = a.BCK01 and b.BBY01 = a.BBY01 join BBY1 c on a.BBY01 = c.BBY01 where b.BAL08 = 1) --IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI set @smsg = '【'+@smsg+'】药品药房已经禁售.' RAISERROR(@smsg, 16, 1) with nowait RETURN 4 end --判断药品最大限制销售额 Declare @aVAF38 numeric(18,4),@VAA01 int,@VAA07 int declare @gpara69 varchar(10),@vaj47 datetime set @VAA01 = ISNULL((select top 1 VAA01 from #tmpVAI),0) set @VAA07 = ISNULL((select top 1 VAA07 from #tmpVAI),0) set @aVAF38 = (select SUM(VAI23) FVAI23 from #tmpVAI) --********** /* select d.BBY01,d.BCK01,d.RefQty,d.SellQty,d.LSQty,c.VAJ25,0 DSK_ID,0 PurchasePrice,0 PresellPrice into #tmpDpt1log from (select BCK01,BBY01,SUM(Quantity) LSQty,sum(RefQty) RefQty,sum(SellQty) SellQty from DPT1 group by BCK01,BBY01) d join (select a.BCK01D BCK01,a.BBY01,sum(a.VAJ25) VAJ25 from #tmpVAJ a where a.BDN01 >= '1' and a.BDN01 <= '3' group by a.BCK01D,a.BBY01 ) c on c.BCK01 = d.BCK01 and c.BBY01 = d.BBY01 union all select a.BBY01,b.BCK01,b.RefQty,b.SellQty,b.Quantity,a.vaj25,a.DSK01,b.PurchasePrice,b.PresellPrice from #tmpvaj a join DPT1 b on b.BBY01 = a.BBY01 and b.BCK01 = a.BCK01D and a.DSK01 = b.DSK_ID where a.BDN01 >= '1' and a.BDN01 <= '3' and a.DSK01 > 0 */ update a set a.VEG04 = b.VEH02 from #TmpVAJ a join VEH2 b on b.VAF01 = a.VAF01 update a set a.VAJ53=2 from #TmpVAJ a join (select c.BBY01 from blp1 b with(nolock) join bby1 c with(nolock) on c.BBY01=b.blp03 where b.blp02=93 and b.blp03>'') d on d.bby01=a.bby01 where a.BDN01<='3' BEGIN TRAN INSERT INTO VAI2 (VAI01,VAA01,VAA07,VAI04,VAI05,BCK01A,BCK01B,BCE02A,BCE03A,BCE02B,BCE03B,VAI12,VAI13,VAI14 ,VAI01A,VAI16,VAI17,VAI18,CBM01,ACF01,VAI22,VAI23,BCE01C,BCE03C) SELECT VAI01,VAA01,VAA07,VAI04,'住院长嘱发送' ,0,BCK01B,BCE02,BCE03,@lBCE02,@lBCE03,@curDate ,@curDate, '住院长嘱发送' ,0,1,1,@lSign,CBM01,ACF01,VAI22,VAI23,BCE01C,BCE03C FROM #tmpVAI INSERT INTO VAJ2(VAJ01,VAA01,VAA07,VAJ04,VAJ05,ROWNR,VAJ09,VAI01,VAF01,VAK01,ACF01,VAJ15,BCK01A,BCK01B ,BDN01,BBY01,BCJ02,VAJ21,VAJ22,VAJ23,VAJ24,VAJ25,VAJ26,VAJ27,VAJ30,VAJ31 ,VAJ32,VAJ33,VAJ34,VAJ35,VAJ36,VAJ37,VAJ38,BCE03A,BCK01C,BCE02B,BCE03B,VAJ46 ,VAJ47,VAJ48,BCK01D,BCE02C,BCE03C,VAJ51,VAJ52,VAJ53,VAJ54,BCE02D,BCE03D,VAJ57,VAJ59,VAJ61,VAJ62,BCK01E,VAJ39,VAJ01A,DSK01,FAB03,VAJ64 ,VAJ67,BCE01E,BCE03E,BCK01F,BCQ04,VAJ72) SELECT a.VAJ01,a.VAA01,a.VAA07,@lSign,1,a.ROWNR,0,a.VAI01,a.VAF01,0,a.ACF01,0,a.BCK01A,a.BCK01B ,a.BDN01,a.BBY01,null,0,0,a.VAJ23,a.VAJ24,a.VAJ25, a.VAJ26,a.VAJ27,a.VAJ30 ,a.VAJ31 ,a.VAJ32,a.VAJ33,a.VAJ34,a.VAJ35,a.VAJ36,a.VAJ37,a.VAJ38 ,a.BCE03B,a.BCK01C,a.BCE02B,a.BCE03B,@curdate ,@curDate,0,a.BCK01D,case when a.VAJ53=1 then @lBCE02 else null end BCE02C ,case when a.VAJ53=1 then @lBCE03 else null end BCE03C,case when a.VAJ53=1 then a.VAJ64 else null end VAJ51 ,case when a.VAJ53=1 then @curDate else null end VAJ52,a.VAJ53 ,a.VAJ54,@lBCE02,@lBCE03,a.VAJ57,a.VAJ59,a.VAJ61,@curDate, a.BCK01E,a.VAJ39,a.VAJ01A,DSK01,FAB03,a.VAJ64 ,a.VAJ32,a.BCE01E,a.BCE03E,a.BCK01F,a.BCQ04,a.VAJ72 FROM #tmpVAJ a where exists(select * from @TmpVAFcz b where b.VAF01 = a.VAF01 and b.BBX17 < 3) INSERT INTO VAJT(VAJ01,VAA01,VAA07,VAJ04,VAJ05,ROWNR,VAJ09,VAI01,VAF01,VAK01,ACF01,VAJ15,BCK01A,BCK01B ,BDN01,BBY01,BCJ02,VAJ21,VAJ22,VAJ23,VAJ24,VAJ25,VAJ26,VAJ27,VAJ30,VAJ31 ,VAJ32,VAJ33,VAJ34,VAJ35,VAJ36,VAJ37,VAJ38,BCE03A,BCK01C,BCE02B,BCE03B,VAJ46 ,VAJ47,VAJ48,BCK01D,BCE03C,VAJ51,VAJ52,VAJ53,VAJ54,BCE02D,BCE03D,VAJ57,VAJ59,VAJ61,VAJ62,BCK01E,VAJ39,VAJ01A,DSK01,FAB03,VAJ64 ,VAJ67,BCE01E,BCE03E,BCK01F,BCQ04,VAJ72) SELECT a.VAJ01,a.VAA01,a.VAA07,@lSign,1,a.ROWNR,0,a.VAI01,a.VAF01,0,a.ACF01,0,a.BCK01A,a.BCK01B ,a.BDN01,a.BBY01,null,0,0,a.VAJ23,a.VAJ24,a.VAJ25, a.VAJ26,a.VAJ27,a.VAJ30 ,a.VAJ31 ,a.VAJ32,a.VAJ33,a.VAJ34,a.VAJ35,a.VAJ36,a.VAJ37,a.VAJ38 ,a.BCE03B,a.BCK01C,a.BCE02B,a.BCE03B,@curDate ,@curDate,0,a.BCK01D,null,null,null,0,a.VAJ54,@lBCE02,@lBCE03,a.VAJ57,a.VAJ59,a.VAJ61,@curDate, a.BCK01E,a.VAJ39,a.VAJ01A,DSK01,FAB03,a.VAJ64 ,a.VAJ32,a.BCE01E,a.BCE03E,a.BCK01F,a.BCQ04,a.VAJ72 FROM #tmpVAJ a where exists(select * from @TmpVAFcz b where b.VAF01 = a.VAF01 and b.BBX17 = 3) if @@ERROR <> 0 begin RollBack tran --IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI RAISERROR('插入费用记录时错误.', 16, 1) with nowait return 2 end insert into VEG2(VAJ01,VEG02,VEG03,VEG04) select VAJ01,0,VAJ25,VEG04 from #tmpVAJ --****** update a SET a.VAF10 = b.VAF10, a.BCE03E = b.BCE03E,a.BCE01E = b.BCE01E,a.BCE03F = b.BCE03F,a.BCE01F = b.BCE01F, a.VAF50 = b.VAF50, a.VAF62 = b.VAF62 ,a.VAF38 = b.aVAF38,a.VAF47 = b.VAF47,a.BCE01D = b.BCE01D,a.BCE03D = b.BCE03D ,a.VAF34 = case when b.BDA01 >='1' and b.BDA01 <= '2' and b.VAF75=1 and b.VAF34 = 0 then 1 else a.VAF34 end ,a.VAF33 = case when b.VAF10 = 8 then 2 else 1 end from VAF2 a join @TmpVAFcz b on b.VAF01 = a.VAF01 if @@ERROR <> 0 begin RollBack tran -- IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI RAISERROR('修改医嘱记录时错误.', 16, 1) with nowait return 4 end INSERT INTO VBI2(VBI01,VAF01,VBI03,VBI04,VBI05,VBI06,VBI07,BCE03A,VBI09,VBI10,VBI11,VBI13,BCK01,VBI15,BCE01A ,VBI29,ACF01,VAA01,VAA07,VAP01,VBI35,VBI12,VBI24) SELECT VBI01,VAF01,VBI01,@lSign,VBI05,VBI06,VBI07,@lBCE03,@curDate,VBI10,VBI11,VBI13,BCK01,2,@lBCE01 ,0,2,VAA01,VAA07,VAP01,VBI07,case when VBI13=1 then VBI10 else null end VBI12 ,case when VBI13=1 then '发送医嘱自动执行,操作人：'+@lbce03 else '' end VBI24 FROM #tmpVBI if @@ERROR <> 0 begin RollBack tran -- IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI RAISERROR('插入执行记录时错误.', 16, 1) with nowait return 5 end insert into VBG1(VBG01,VAF01,VBG03,BCE03,VBG05,VBG06) select VBG01,VAF01,VAF10,@lBCE03,@curDate,case when VAF10 = 3 then '长嘱执行' when VAF10 = 8 then '长嘱执行自动停止' when VAF10 = 9 then '长嘱确认停嘱' end from @TmpVAFcz declare @VAE01 int,@lAAG01 int select @VAE01 = VAA07 from @TmpVAFcz if exists(select a.* from @TmpVAFcz d join VBD2 a on a.VAF01 = d.VAF01 join BBY1 b on b.BBY01 = a.BBY01 join AAG1 c on c.AAG01 = b.BCF01 where d.BDA01 = 'N' and d.BBX17 <> 9 and d.VAF01A = 0 and b.BDN01='N' and c.AAG01<=7 and d.VAP01 = 0) begin set @lAAG01 =(select top 1 a.BBY01 from @TmpVAFcz d join VBD2 a on a.VAF01 = d.VAF01 join BBY1 b on b.BBY01 = a.BBY01 join AAG1 c on c.AAG01 = b.BCF01 where d.BDA01 = 'N' and d.BBX17 <> 9 and d.VAF01A = 0 and b.BDN01='N' and c.AAG01<=7 and d.VAP01 = 0 order by d.VAF36 desc) if not exists(select * from VAE1 where AAG01=@lAAG01 and VAE01= @VAE01) BEGIN update VAE1 set AAG01=@lAAG01 where VAE01 = @VAE01 UPDATE VBO1 SET BCE03E = @lBCE03,VBO19 = @curDate,VBO20 = @curDate,VBO21 = 6 WHERE VAA07 = @VAE01 AND isnull(VBO21,0)=0 AND VBO19 IS NULL EXEC Core_NewIDEx 'VBO1','VBO01',@aVAIid out INSERT INTO VBO1 (VBO01,VAA01,VAA07,VBO04,VBO05,VBO06,BCK01A,BCK01B,BBY01A,BBY01B,BCQ04 ,BCE03A,BCE03B,ABO01,BCE01,BCE03F,VBO21) SELECT @aVAIid,a.VAA01,a.VAE01,@curDate,@curDate,6,a.BCK01C,a.BCK01D,a.AAG01,b.BBY01,a.BCQ04B ,a.BCE03B,a.BCE03C,a.ABO01,@lBCE01,@lBCE03,0 FROM VAE1 a JOIN BCQ1 b ON a.BCQ04B = b.BCQ04 and b.VAA01=a.VAA01 and a.BCK01C=b.BCK01A WHERE a.VAE01 = @VAE01 END end --发送手术医嘱到手术记录表 if exists(SELECT a.* FROM @TmpVAFcz a JOIN BAZ1 b ON a.BCK01B = b.BCK01 WHERE a.BDA01 = 'S' and a.BBX17 <> 9 and a.VAF01A = 0 AND b.BAU01 = '03' AND b.ACF01 IN (2,3)) begin INSERT INTO CBM2 (CBM01,VAA01,VAA07,VAP01,ACF01,CBM06,CBM07,CBM08,CBM09,BCK01A,BCK01B,BCK01C,BCE01,BCE03,CBM15,CBM16) SELECT d.VAT01,a.VAA01,a.VAA07,a.VAP01,a.ACF01,3,99,d.VAT01,'手术记录单',c.BCK01C,a.BCK01A,a.BCK01C,b.BCE01,b.BCE03,@curDate,a.VAF36 FROM @kbmVAT d join @TmpVAFcz a on a.VAF01 = d.VAF01 JOIN BCE1 b ON b.BCE02 = a.BCE02A JOIN VAE1 c ON c.VAE01 = a.VAA07 INSERT INTO VAT1(VAT01, VAA01, VAA07, VAT04, ACF01, VAF01, VAT08 ,VAT16, BBX01, ACI02, BCK01,VAT33,AAB01) SELECT d.VAT01,a.VAA01,a.VAa07,0,a.ACF01,a.VAF01 ,case when isnull(a.vaf14,'')='' or a.vaf14='0000-00-00 00:00' then dateadd(day,1,@curDate) else a.vaf14 end VAF14 ,b.VAF22, b.BBX01,b3.ACI02 ,a.BCK01B,a.VAF23,c1.AAB01 FROM @kbmVAT d join @TmpVAFcz a on a.VAF01 = d.VAF01 LEFT JOIN @TmpVAFcz b ON b.VAF01A = b.VAF01 AND b.BDA01 = 'A' LEFT JOIN BBX1 b2 ON b.BBX01 = b2.BBX01 LEFT JOIN ACI1 b3 ON b2.BBX13 = b3.ACI01 left join VBF1 c on c.VAF01 = d.VAF01 and c.VBF03 = '切口等级' left join AAB1 c1 on c1.AAB02 = c.VBF07 insert into VAV1(VAV01,VAT01,VAV03,VAV04,VAV05,BAK01,BBX01,ACI02,VAV09) select VAV01,VAT01,VAV03,VAV04,VAV05,0,BBX01,ACI02,VAV09 from @tmpVAV end insert into VBX1(ACF01,VBX03,VBX04,VBX05,VBX06,VBX07,BCK01A,BCK01B,BCK01C,BCK01D ,BCE01A,BCE03A,VBX14,VAA01,VAA07,VBX17,VBX18,VBX19,VBX20,VBX21,VBX22) select distinct 2,2,1,1,f.BBX20,b.VAI04,a.BCK01A,a.BCK01B,a.BCK01E,a.BCK01D ,@lBCE01,@lBCE03,@curDate,a.VAA01,a.VAA07,0,a.VAI01,0,'护士站长嘱发送药品医嘱',0,0 FROM #tmpVAJ a JOIN BBY1 c ON a.BBY01 = c.BBY01 join #tmpVAI b on b.VAI01 = a.VAI01 join @TmpVAFcz d on d.VAF01 = a.VAF01 join @TmpVAFcz e on e.VAF01 = d.VAF01A join BBX1 f on f.BBX01 = e.BBX01 where c.BDN01 <= '3' update a set a.VAF10 = b.VAF10 from VBZ1 a join @TmpVAFcz b on b.VAF01 = a.VAF01 update a set a.VCL19=1 From VCL1 a Join @TmpVAFcz b on b.VAF01 = a.VCL08 where a.ACF01=2 and a.VCL02=1 --病人余额 if @VAA01 > 0 begin exec Nurse_VBM1_Update 2,0,@VAA01,@aVAF38,2,@VAA07 end COMMIT TRAN --库存处理 if exists(select * from @tmpDPT) begin select @aVAIid = MIN(uid),@aVAIMax = MAX(uid) from @tmpDPT while @aVAIid <= @aVAIMax begin select @BCK01 = BCK01,@BBY01 = BBY01,@DSK01 = ISNULL(DSK01,0),@VAJ25 = VAJ25 from @tmpDPT where uid = @aVAIid exec DPS_DPT1_Update @BCK01,@BBY01,@DSK01,@VAJ25 set @aVAIid = @aVAIid + 1 end end --tiao shi ku cun /* insert into DPT1_log(BBY01,BCK01,RefQty,SellQty,Quantity,SysDate,BCE01,Bce03,ACF01,VAJ25,DSK_ID, PurchasePrice, PresellPrice) select BBY01,BCK01,RefQty,SellQty,LSQty,@curDate,@lBCE01,@lbce03,2,VAJ25,DSK_ID, PurchasePrice, PresellPrice from #tmpDpt1log */ --IF Object_id('tempdb..#TmpVAFcz') IS NOT NULL DROP TABLE #TmpVAFcz IF Object_id('tempdb..#tmpBDA') IS NOT NULL DROP TABLE #tmpBDA IF Object_id('tempdb..#TmpVAJ') IS NOT NULL DROP TABLE #TmpVAJ IF Object_id('tempdb..#tmpVBD') IS NOT NULL DROP TABLE #tmpVBD IF Object_id('tempdb..#tmpVBI') IS NOT NULL DROP TABLE #tmpVBI