if (exists (select * from sys.objects where name = 'Clinic_VAF1_InRest'))
    drop proc Clinic_VAF1_InRest
go 
create proc [dbo].[Clinic_VAF1_InRest]

  @lVER varchar(19) --文件版本号
  ,@lXml ntext
  ,@lType int --0=门诊
  ,@lMode tinyint --0:新增 1：修改 2=复诊预约计划
  ,@lCBM01A int --修改医嘱单的id
  ,@lACF01 int
  ,@lBCK01 int
  ,@lBCE01 int
  ,@lBCE02 varchar(20)
  ,@lBCE03 varchar(20)
	,@lURIID VARCHAR(40)
	,@lMac VARCHAR(20)
  ,@rCBM Varchar(1024) = '' output
AS

  DECLARE @LowVer varchar(19)   --存储过程要求客户端最低版本号
  
  SET @LowVer = '0008.0000.0001.0143'

  IF @lVER < '0008.0000.0001.0143'
  Begin
    RAISERROR('客户端版本(%s)校验失败，最低版本号(%s),请重启软件进行升级.', 16, 1, @lVer, @LowVer) with nowait
    RETURN 100	
  End

  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 
  select @lBCE02 = BCE02, @lBCE03 = BCE03 from BCE1 where BCE01 = @lBCE01
  --提取内容
  SELECT IDENTITY(int,1,1) AS ID,@lBCE01 BCE01,@lBCE03 BCE03,cast(0 as int) CBM01,cast(0 as int) VBQ01,*
    INTO #TmpCBM
  from OpenXml(@iDOM,'/Root/CBM1/Ie',8)
    WITH
     (CBMID int
      ,VAA01 int--病人ID
      ,VAA07 int--就诊ID
      ,VAP01 int --婴儿id
      ,ACF01 int--病人来源
      ,CBM06 int
      ,CBM07 int
      ,BJW02 varchar(22)
      ,BCK01A int
      ,BCK01B int
      ,BCK01C int
      ,CBM19  tinyint
      ,IAI03 varchar(10),VAA04 varchar(20),VAA05 varchar(64),BCQ04 varchar(20)
      ,VCO12 varchar(10),VCO18 varchar(200),VCO21 varchar(512),VCO01 int
     )	
  If not exists(select * from #TmpCBM)
  BEGIN
   exec sp_xml_removedocument @iDOM
   RAISERROR('提交文档错误，医嘱单信息为空', 16, 1) with nowait
   RETURN 2
  END

  SELECT IDENTITY(int,1,1) AS ID,CAST(0 as int) VAF01,CAST(0 as int) VAF01A,CAST(0 as tinyint) VAF75,0 VAF51,0 CBM01,cast(0.0 as numeric(18,4)) BAG46,*
    INTO #TmpVAF
  from OpenXml(@iDOM,'/Root/VAF1/Ie',8)
    WITH
     (CBMID int
      ,VAF04 tinyint
      ,VAA01 int--病人ID
      ,VAF06 int--就诊ID
      ,VAF07 int --婴儿id
      ,BCK01A int
      ,ROWNR int
      ,VAF10 tinyint
      ,VAF11 tinyint
      ,BDA01 varchar(2)
      ,BBX01 int
      ,VAF14 varchar(60)
      ,VAF15 varchar(30)
      ,BBY01 int
      ,VAF17 int
      ,VAF18 numeric(18,4)
      ,VAF19 varchar(10)
      ,VAF20 numeric(18,4)
      ,VAF21 numeric(18,4)
      ,VAF22 varchar(1024)
      ,VAF23 varchar(128)
      ,BCK01B int
      ,VAF26 varchar(20)
      ,VAF27 int
      ,VAF28 tinyint
      ,VAF29 varchar(4)
      ,VAF30 varchar(64)
      ,VAF31 tinyint
      ,VAF32 tinyint
      ,VAF33 tinyint
      ,VAF34 tinyint
      ,VAF35 tinyint
      ,VAF36 Datetime
      ,VAF37 Datetime
      ,BCK01C int
      ,BCE02A varchar(20)
      ,BCE03A varchar(20)
      ,BCE03B varchar(20)     
      ,VAF55 varchar(1024)
      ,BCK01D int
      ,VAF58 tinyint
      ,VAF59 int
      ,VAF60 varchar(10)
      ,VAF61 numeric(8,2)
      ,VAF62 numeric(8,2)
      ,lSign int
      ,BDA01A varchar(2)
      ,lRoute int
      ,BCE01A int
      ,BCE01B int
      ,VAF01B int
      ,VCO01 int,BCE01H int,BCE03H varchar(20),BIJ01 int,VAF53 int,BIW02 varchar(64)
     )	
   where lSign = 0
   order by cbmid,vaf59,rownr
  
  delete a from #TmpVAF a where not exists(select * from #TmpCBM b where b.CBMid = a.CBMid) 
  If not exists(select * from #TmpVAF)
  BEGIN
   exec sp_xml_removedocument @iDOM
   RAISERROR('提交文档错误，医嘱信息为空', 16, 1) with nowait
   RETURN 3
  END
  
  If exists(select * from #TmpVAF where VAF36 is null)
  BEGIN
   exec sp_xml_removedocument @iDOM
   RAISERROR('提交文档错误，医嘱开始时间不能省略.', 16, 1) with nowait
   RETURN 3
  END
  
  SELECT IDENTITY(int,1,1) AS ID,CAST(0 as int) VAO01,0 CBM01,*
    INTO #TmpVAO
  from OpenXml(@iDOM,'/Root/VAO1/Ie',8)
    WITH
     (CBMID int
      ,VAA01 int--病人ID
      ,VAA07 int--就诊ID
      ,ACF01 int--病人来源
      ,VAO06 int
      ,VAO07 int
      ,VAO10 tinyint
      ,VAO11 tinyint
      ,BAK01A int
  ,CAM01 int
      ,VAO15 varchar(150)
      ,VAO18 tinyint
      ,VAO22 tinyint ,IAA01 int ,IAD03 varchar(20)
     )	

  SELECT IDENTITY(int,1,1) AS ID,0 vbqid,*
    INTO #TmpVBQ
  from OpenXml(@iDOM,'/Root/VBQ1/Ie',8)
    WITH
     (VBQ01 int
      ,VAA01 int--病人ID
      ,VAA07 int--就诊ID
      ,ACF01 tinyint--病人来源
      ,VAP01 int
      ,VBQ07 int
      ,BCK01A int
      ,BCK01B int
      ,BCE03A varchar(20)
      ,BCE01A int
      ,VBQ27 varchar(1024)
      ,VBQ28 varchar(1024)
      ,VBQ29 varchar(2048)
      ,VBQ30 varchar(2048)
     )	
     
  SELECT IDENTITY(int,1,1) AS ID,0 VAF01,0 VAX01,0 VAT01,*
    INTO #TmpVAX
  from OpenXml(@iDOM,'/Root/VAX1/Ie',8)
    WITH
     (CBMID int
      ,VAA01 int--病人ID
      ,VAA07 int--就诊ID
      ,ACF01 int--病人来源
      ,VAF59 int
      ,Rownr int
      ,ACE01 varchar(2)     
      ,BCE01 int
      ,BCE02 varchar(20)
      ,BCE03 varchar(64)
     )	
        
 exec sp_xml_removedocument @iDOM    
 
 --*************
 DECLARE @lID int, @lDate Datetime, @aMin int, @aMax int,@VAF36 datetime,@VCYid int
 declare @smsg varchar(512), @lStr Varchar(10) ,@VAA07 int,@mzpara370 varchar(10)
 declare @pValue varchar(10), @fChange tinyint, @BCE01G int,@BCE03G varchar(20),@para203 varchar(10),@gpara50 varchar(32)
 set @gpara50 = dbo.GetSysParamValue(9999,9999,50)
 set @mzpara370 = dbo.GetSysParamValue(100,105002,370)
 set @smsg = ''
 If (@lACF01 = 1) and exists(select * from #TmpVAF where BDA01 >= '1' and BDA01 <= '2' AND isnull(VAF26,'')='')
 BEGIN
   set @smsg = (select top 1 VAF22 from  #TmpVAF where BDA01 >= '1' and BDA01 <= '2' AND isnull(VAF26,'')='')
   set @smsg = isnull(@smsg,'') + ' 没有用药频次，请修改.'
   RAISERROR(@smsg, 16, 1) with nowait
   RETURN 3
 END
 
 SET @lDate = GetDate()
 if @lType >= 1 and @lType <= 2
 begin
   set @pValue = dbo.GetSysParamValue(100,105003,177)
   set @para203 = dbo.GetSysParamValue(100,105003,203)
   if exists(select a.*
             from BCE1 a
             join BCK1 b on b.BCK01 = a.BCK01
             join BAZ1 c on c.BCK01 = b.BCK01
             join BAA1 d on d.BCE01 = a.BCE01
             where c.BAU01 = '08' and c.ACF01 in (2,3) and d.AAA01 = '11' and a.BCE01 = @lBCE01)
      set @fChange = 0
   else
     set @fChange = 1   
   --***更新全责医师  ,当没有授权 允许修改开嘱医师时，且操作员聘任职务>4时，默认住院医师为全责医师 
   if @pValue = '1' and @fChange = 1 and exists(select * from BCE1 where isnull(AAH01,0)>4 and BCE01 = @lBCE01) and (@lType = 1)
   begin
     update a
     set a.BCE01A = c.BCE01,a.BCE02A = c.BCE02,a.BCE03A = c.BCE03
     from #TmpVAF a
     join VAE1 b on b.VAE01 = a.VAF06 and a.VAF04 = 2
     join BCE1 c on c.BCE02 = b.BCE02C
     where a.BCE01A = @lBCE01
     
     update a
     set a.BCE01 = c.BCE01,a.BCE03 = c.BCE03
     from #TmpCBM a
     join VAE1 b on b.VAE01 = a.VAA07 and a.ACF01 = 2
     join BCE1 c on c.BCE02 = b.BCE02C
     where a.BCE01 = @lBCE01
     
     update a
     set a.BCE01A = c.BCE01,a.BCE03A = c.BCE03
     from #TmpVBQ a
     join VAE1 b on b.VAE01 = a.VAA07 and a.ACF01 = 2
     join BCE1 c on c.BCE02 = b.BCE02C
     where a.BCE01A = @lBCE01
   end           
 end
 else
 begin
   set @pValue = '0'
   set @fChange = 0
   set @para203 = '0'
 end
 
 SET @lStr = '0000000000'
  
 set @rCBM = ''  
 Set @smsg = ''
 IF Exists(select * from #TmpVAF a where not exists(select b.* from BCK1 b where a.BCK01B = b.BCK01))
 begin
   Set @smsg = (select top 1 VAF22 from #TmpVAF  a where not exists(select b.* from BCK1 b where a.BCK01B = b.BCK01))
   Set @smsg = @smsg + '医嘱中执行科室不正确，请检查修改！'
   RAISERROR(@smsg, 16, 1) with nowait
   RETURN 5
 end 
 --判断药品
 IF Exists(select * from #TmpVAF a where a.BDA01 >= '1' and a.BDA01 <= '3' And 
      not Exists(select b.* from BCK1 b where a.BCK01B = b.BCK01))
 begin
   Set @smsg = (select top 1 VAF22 from #TmpVAF  a where a.BDA01 >= '1' and a.BDA01 <= '3' And 
      not Exists(select b.* from BCK1 b where a.BCK01B = b.BCK01))
   Set @smsg = @smsg + '医嘱中对应附加执行科室不正确，请检查修改！'
   RAISERROR(@smsg, 16, 1) with nowait
   RETURN 6
 end
 IF Exists(select * from #TmpVAF a where (a.BCK01C > 0 And 
      not Exists(select b.* from BCK1 b where a.BCK01C = b.BCK01)) or ISNULL(a.BCK01C,0)=0)
 begin
   Set @smsg = (select top 1 VAF22 from #TmpVAF  a where (a.BCK01C > 0 And 
      not Exists(select b.* from BCK1 b where a.BCK01C = b.BCK01)) or ISNULL(a.BCK01C,0)=0)
   Set @smsg = @smsg + '医嘱中对应开嘱科室不正确，请检查修改！'
   RAISERROR(@smsg, 16, 1) with nowait
   RETURN 7
 end
 
  Declare @lError int,@maxid int,@VBGid int
  Set @lError = 0
  
  select * into #kbmBDJ from BDJ1 where BCE01 = @lBCE01
  
  update a set a.VAA04 = b.VAA04,a.VAA05 = b.VAA05,a.BCQ04 = c.BCQ04B
  from #TmpCBM a
  join VAA1 b on b.VAA01 = a.VAA01
  join VAE1 c on c.VAE01 = a.VAA07
  
  update a set a.VAF75 = case when isnull(b.BAG26,0) = 2 then 1 else 0 end
  from #TmpVAF a
  join BAG1 b on b.BBY01 = a.BBY01
  where a.BDA01 >='1' and a.BDA01 <='3'
  
  Select @aMax = MAX(ID) From #TmpCBM
  set @lID = 0
  EXEC Core_NewIDEx 'CBM1','CBM01',@lID out, @aMax
  Set @lID = @lID - @aMax
  update #TmpCBM set CBM01 = @lID + ID
  set @rCBM = ''  
  select @rCBM = case when @rCBM = '' then CONVERT(varchar(20),CBM01) else @rCBM + ','+ CONVERT(varchar(20),CBM01) end from #TmpCBM
    
  Select @aMax = MAX(ID) From #TmpVAF
  set @lID = 0
  EXEC Core_NewIDEx 'VAF1','VAF01',@lID out, @aMax
  Set @lID = @lID - @aMax
  
  set @VBGid = 0
  EXEC Core_NewIDEx 'VBG1','VBG01',@VBGid out,@aMax
  set @VBGid = @VBGid - @aMax
        
  update #TmpVAF set VAF01 = @lID + ID,VAF35 = case when VAF11 = 1 THEN 0 else VAF35 end
    ,VAF27 = case when isnull(VAF27,0)<1 then 1 else VAF27 end
  
  update a set a.CBM01 = b.CBM01
  from #TmpVAF a
  join #TmpCBM b on b.CBMid = a.CBMid
     
  update a set a.VAF01A = c.VAF01
  from #TmpVAF a
  join (select CBM01,VAF59,COUNT(1) fnum from #TmpVAF group by CBM01,VAF59 having COUNT(1) >= 2) b on b.CBM01 = a.CBM01 and b.VAF59 = a.VAF59
  join #TmpVAF c on c.CBM01 = b.CBM01 and c.VAF59 = b.VAF59 and c.rownr = 0
  where a.rownr >= 1  
    
  delete from #TmpVAF where bda01 >= '1' and bda01 <= '3' and VAF01A = 0
  delete a
  from #TmpVAF a
  where a.bda01 >= '1' and a.bda01 <= '3' 
    and not exists(select * from #TmpVAF b where b.VAF01 = a.VAF01A and b.CBM01 = a.CBM01 and b.vaf59 = a.vaf59)
    
  delete a
  from #TmpVAF a
  where a.bda01 = 'T' and a.VAF32 = 1
    and not exists(select * from #TmpVAF b where b.VAF01A = a.VAF01 and b.bda01 >= '1' and b.bda01 <= '3' and b.CBM01 = a.CBM01 and b.vaf59 = a.vaf59)  
  update #tmpvaf set vaf17 = 1 where VAF04 = 2 and vaf11 = 2 and (bda01 in ('1','2')or(bda01 = 'T' and VAF32=1)) and isnull(vaf17,0)=0

  if Exists(Select * from #TmpVBQ)
  begin
    Select @aMin = MIN(ID),@aMax = MAX(ID) From #TmpVBQ
    set @lID = 0
    EXEC Core_NewId_VBQ01 @lID out, @aMax
    Set @lID = @lID - @aMax
    update #TmpVBQ set vbqid = @lID + ID
    delete a from #TmpVBQ a where not exists(select * from #TmpCBM b where b.CBMid = a.VBQ01)
    update a set a.VBQ01 = b.VBQid
    from #TmpCBM a
    join #TmpVBQ b on b.VBQ01 = a.CBMid
  end
  if Exists(Select * from #TmpVAO)
  begin
    Select @aMin = MIN(ID),@aMax = MAX(ID) From #TmpVAO
    set @lID = 0
    EXEC Core_NewIDEx 'VAO1','VAO01',@lID out, @aMax
    Set @lID = @lID - @aMax
    update #TmpVAO set VAO01 = @lID + ID
    UPDATE a set a.CBM01 = b.CBM01
    from #TmpVAO a
    join #TmpCBM b on b.CBMID = a.CBMid
  end
  -- 
  if Exists(Select * from #TmpVAX)
  begin
    update a
    set a.VAF01 = b.VAF01
    from #TmpVAX a
    join #TmpVAF b on b.CBMID = a.CBMID and b.VAF59 = a.VAF59 and b.Rownr = a.Rownr
    
    Select @aMin = MIN(ID),@aMax = MAX(ID) From #TmpVAX
    set @lID = 0
    EXEC Core_NewIDEx 'VAX1','VAX01',@lID out, @aMax    
    Set @lID = @lID - @aMax
    update #TmpVAX set VAX01 = @lID + ID
    
    select IDENTITY(int,1,1) uid,cast(0 as int) VBF01,VAF01,BCE02,BCE03
    into #tmpVBF
    from #tmpVAX
    where ACE01 = '--'
    if exists(select * from #tmpVBF)
    begin
      Select @aMin = MIN(uID),@aMax = MAX(uID) From #tmpVBF
      set @lID = 0
      EXEC Core_NewIDEx 'VBF1','VBF01',@lID out, @aMax    
      Set @lID = @lID - @aMax
      update #TmpVBF set VBF01 = @lID + UID
    end  
  end
  if exists(select * from #TmpVAF where isnull(VCO01,0)>0 and VAF01 > 0)
  begin
    select IDENTITY(int,1,1) AS ID,VAF01,VCO01,0 VCR01,BIJ01 
    into #tmpVCR1
    from #TmpVAF 
    where isnull(VCO01,0)>0 and VAF01 >0
    
    Select @aMax = max(ID) From #tmpVCR1      
    exec Core_NewId_VCR01 @lID out,@aMax
    Set @lID = @lID - @aMax
    update #tmpVCR1 set VCR01 = @lID + ID
  end
  Declare @tmpVAT Table(ID int IDENTITY(1,1),VAF01 int,VAT01 int)
  Declare @tmpVAV Table(ID int IDENTITY(1,1),VAT01 int,VAV01 int,VAV05 varchar(128),BBX01 int)
  if @lACF01 = 1 and @lMode = 2
  begin    
    set @smsg = ''
    if exists(select * from VCY1 a join #TmpVAF b on b.vaa01 = a.VAA01 where a.ACF01 = 1 and a.VAA07 = 0 and a.VCY10 = 0)
    begin
      select @VCYid = a.VCY01
      from VCY1 a 
      join #TmpVAF b on b.vaa01 = a.VAA01 
      where a.ACF01 = 1 and a.VAA07 = 0 and a.VCY10 = 0
      
      select @smsg = case when @smsg = '' then isnull(b.BBX36,'') else @smsg+' ; '+isnull(b.BBX36,'') end
      from VAFT a
      join BBX1 b on b.BBX01 = a.BBX01
      where a.VAF01 >0 and a.VAF01A = 0 and a.VCY01 = @VCYid
    end
    else
      exec Core_NewId_VCY01 @VCYid out
    set @VAF36 = isnull((select top 1 VAF36 from #TmpVAF ),@ldate)    
    select @smsg = case when @smsg = '' then isnull(b.BBX36,'') else @smsg+' ; '+isnull(b.BBX36,'') end
    from #TmpVAF a
    join BBX1 b on b.BBX01 = a.BBX01
    where a.VAF01 >0 and a.VAF01A = 0    
 
  --发送手术医嘱到手术记录表
    if exists(SELECT e.* 
            FROM #TmpVAF 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 (1,3) and e.VAF01 > 0 )
    BEGIN	
      insert into @tmpVAT(VAF01) 
      SELECT e.VAF01
      FROM #TmpVaf e
      WHERE e.BDA01 = 'S' And e.VAF01A = 0 and e.VAF01 > 0
        and exists(select * from BAZ1 f where f.BCK01 = e.BCK01B
               And f.BAU01 = '03' AND f.ACF01 IN (1,3) )
    
      Select @aMax = MAX(ID) From @tmpVAT
      set @lID = 0
      EXEC Core_NewIDEx 'CBM1','CBM01',@lID out, @aMax
      Set @lID = @lID - @aMax
      
      update @tmpVAT set VAT01 = @lID + ID  
      
      update a set a.VAT01 = b.VAT01
      from #TmpVAX a
      join @tmpVAT b on b.VAF01 = a.VAF01     
      
      insert into @tmpVAV(VAT01,VAV05,BBX01)
      select a.VAT01,c.BBX05,b.BBX01
      from @tmpVAT a
      join #TmpVAF b on b.VAF01 = a.VAF01
      join BBX1 c on c.BBX01 = b.BBX01
      
      Select @aMax = MAX(ID) From @tmpVAV
      set @lID = 0
      EXEC Core_NewIDEx 'VAV1','VAV01',@lID out, @aMax
      Set @lID = @lID - @aMax
      UPDATE @tmpVAV set VAV01 = @lID + ID
    END  
  end
  
  update a set a.BCK01D = b.BCK01B
  from #TmpVAF a
  join #TmpVAF b on b.VAF01 = a.VAF01A
  where a.BDA01 >= '1' and a.BDA01 <= '2' and a.BCK01D <> b.BCK01B
BEGIN TRAN 
  --先删除修改的医嘱单
  IF (@lMode = 1)AND(@lCBM01A>0)
  begin
    Exec @lError = Clinic_Advice_CBM01_Del @lACF01,@lCBM01A,@lBCE03
    IF @lError > 0
    Begin
      if @@TRANCOUNT > 0
           ROLLBACK TRAN  
      IF Object_id('tempdb..#TmpCBM') IS NOT NULL DROP TABLE #TmpCBM
      IF Object_id('tempdb..#TmpVAO') IS NOT NULL DROP TABLE #TmpVAO
      IF Object_id('tempdb..#TmpVAF') IS NOT NULL DROP TABLE #TmpVAF  
      IF Object_id('tempdb..#TmpVBQ') IS NOT NULL DROP TABLE #TmpVBQ 
      IF Object_id('tempdb..#TmpVAX') IS NOT NULL DROP TABLE #TmpVAX 
      RAISERROR('删除医嘱出现错误！', 16, 1) with nowait   
      return 11
    End
  end
  --先增加医嘱单
  if @lACF01=1
  begin
   INSERT INTO CBM1(CBM01,VAA01,VAA07,VAP01,ACF01,CBM06,CBM07,CBM08,CBM09,BCK01A
       ,BCK01B,BCK01C,BCE01,BCE03,CBM18,CBM19,CBM15,CBM16,BJW02,VBQ01,IAI03)
   SELECT a.CBM01,a.VAA01,a.VAA07,a.VAP01,a.ACF01,a.CBM06,a.CBM07,cast(a.CBM01 AS varchar(20)),'医嘱单',b.BCK01C
    ,a.BCK01B,a.BCK01C,@lBCE01,@lBCE03,'医嘱单',a.CBM19,@lDate,@lDate,a.BJW02,a.VBQ01,a.IAI03
   FROM #TmpCBM a
   LEFT JOIN VAE1 b ON a.ACF01=2 AND a.VAA07 = b.VAE01
  end
  else
  begin
   INSERT INTO CBM2(CBM01,VAA01,VAA07,VAP01,ACF01,CBM06,CBM07,CBM08,CBM09,BCK01A
       ,BCK01B,BCK01C,BCE01,BCE03,CBM18,CBM19,CBM15,CBM16,BJW02,VBQ01,IAI03)
   SELECT a.CBM01,a.VAA01,a.VAA07,a.VAP01,a.ACF01,a.CBM06,a.CBM07,cast(a.CBM01 AS varchar(20)),'医嘱单',b.BCK01C
    ,a.BCK01B,a.BCK01C,@lBCE01,@lBCE03,'医嘱单',a.CBM19,@lDate,@lDate,a.BJW02,a.VBQ01,a.IAI03
   FROM #TmpCBM a
   LEFT JOIN VAE1 b ON a.ACF01=2 AND a.VAA07 = b.VAE01 
  end 
  if Exists(Select * from #TmpVBQ)
  Begin
   if @lACF01 <> 2
   begin
    Insert Into VBQ1(VBQ01,VAA01,VAA07,ACF01,VAP01,VBQ07,BCK01A,BCK01B,BCE03A
        ,BCE01A ,VBQ27 ,VBQ28 ,VBQ29 ,VBQ30,VBQ15)
    Select vbqid,VAA01,VAA07,ACF01,VAP01,VBQ07,BCK01A,BCK01B,BCE03A
        ,BCE01A ,VBQ27 ,VBQ28 ,VBQ29 ,VBQ30
        ,'JC'+Convert(varchar(4),@lDate,21)+substring(@lstr,1,10-len(vbqid))+cast(vbqid AS varchar)
    From #TmpVBQ 
   end
   else
   begin
    Insert Into VBQ2(VBQ01,VAA01,VAA07,ACF01,VAP01,VBQ07,BCK01A,BCK01B,BCE03A
        ,BCE01A ,VBQ27 ,VBQ28 ,VBQ29 ,VBQ30,VBQ15)
    Select vbqid,VAA01,VAA07,ACF01,VAP01,VBQ07,BCK01A,BCK01B,BCE03A
        ,BCE01A ,VBQ27 ,VBQ28 ,VBQ29 ,VBQ30
        ,'JC'+Convert(varchar(4),@lDate,21)+substring(@lstr,1,10-len(vbqid))+cast(vbqid AS varchar)
    From #TmpVBQ 
   end   
  End
  if @lACF01 = 1 and @lMode = 2
  begin
    if not exists(select * from VCY1 where VCY01 = @VCYid)
    begin
      insert into VCY1(VCY01,VAA01,VAA07,ACF01,BCK01,BCE01,BCE03,VCY08,VCY09,VCY10,VCY11)
      select top 1 @VCYid,VAA01,0,1,BCK01A,@lBCE01,@lBCE03,@smsg,VAF36,0,@lDate
      from #tmpVAF
    end
    else 
    begin
      update VCY1 set VCY08 = @smsg where VCY01 = @VCYid
    end
    
    INSERT INTO VAFT
    (VAF01, VAF01A, VAF01B, VAF04, VAA01, VAF06, VAF07, BCK01A, ROWNR
     ,VAF10, VAF11, BDA01, BBX01, VAF14, VAF15, BBY01, VAF17, VAF18
     ,VAF19, VAF20, VAF21, VAF22, VAF23, BCK01B, VAF25, VAF26, VAF27
     ,VAF28, VAF29, VAF30, VAF31, VAF32, VAF33, VAF34, VAF35, VAF36
     ,VAF37, VAF38, BCK01C, BCE02A, BCE03A, BCE03B, BCE03C
     ,VAF45, BCE03D, VAF47, BCE03E, BCE03F, VAF50, VAF51, VAF52
     ,VAF53, VAF54, VAF55, CBM01, BCK01D, VAF58, VAF59, VAF60, VAF42
     ,VAF61, VAF62, BCE01A, BCE01B,BCE01G,BCE03G,VAF71,VAF74,VAF75,VCY01,BIW02)
    SELECT VAF01, VAF01A, VAF01B, VAF04, VAA01, 0, 0, BCK01A, ROWNR
    ,VAF10, VAF11, BDA01, BBX01, VAF14, VAF15, BBY01, VAF17, VAF18
    ,VAF19, VAF20, VAF21, VAF22, VAF23, BCK01B
    , case when BDA01 >= '1' and BDA01 <= '2' and VAF23=@gpara50 then '?' else '' end VAF25, VAF26, VAF27
    ,isnull(VAF28,1), isnull(VAF29,''), VAF30, VAF31, VAF32, VAF33, VAF34, VAF35, VAF36
    ,isnull(VAF37,'1899-12-30'), '1899-12-30', BCK01C, BCE02A, BCE03A, BCE03B, NULL
    ,NULL, NULL, Null, NULL, NULL, Null, 0 VAF51, 0
    ,-1, 0, VAF55, CBM01, BCK01D, VAF58, VAF59, VAF60, @lDate
    ,isnull(VAF61,0), isnull(VAF62,0), BCE01A, BCE01B,@lBCE01,@lBCE03
    ,Case when @lType >= 1 and @lType <= 2 and @pValue = '1' and @fChange = 1 
            and (@para203 <> '1' or (@para203='1' and @lBCE01 <> BCE01A)) then '1899-12-30' else @lDate end
    ,VAF36,isnull(VAF75,0),@VCYid,BIW02
    FROM #TmpVAF
    WHERE VAF01 > 0
  end
  else
  begin
   if @lACF01 <> 2
   begin
    INSERT INTO VAF1(VAF01, VAF01A, VAF01B, VAF04, VAA01, VAF06, VAF07, BCK01A, ROWNR
     ,VAF10, VAF11, BDA01, BBX01, VAF14, VAF15, BBY01, VAF17, VAF18
     ,VAF19, VAF20, VAF21, VAF22, VAF23, BCK01B, VAF25, VAF26, VAF27
     ,VAF28, VAF29, VAF30, VAF31, VAF32, VAF33, VAF34, VAF35, VAF36
     ,VAF37, VAF38, BCK01C, BCE02A, BCE03A, BCE03B, BCE03C
     ,VAF45, BCE03D, VAF47, BCE03E, BCE03F, VAF50, VAF51, VAF52
     ,VAF53, VAF54, VAF55, CBM01, BCK01D, VAF58, VAF59, VAF60, VAF42
     ,VAF61, VAF62, BCE01A, BCE01B,BCE01G,BCE03G,VAF71,VAF74,VAF75,BCE01H,BCE03H,BIW02)
    SELECT VAF01, VAF01A, VAF01B, VAF04, VAA01, VAF06, VAF07, BCK01A, ROWNR
    ,VAF10, VAF11, BDA01, BBX01, VAF14, VAF15, BBY01, VAF17, VAF18
    ,VAF19, VAF20, VAF21, VAF22, VAF23, BCK01B
    , case when BDA01 >= '1' and BDA01 <= '2' and VAF23=@gpara50 then '?' else '' end VAF25, VAF26, VAF27
    ,isnull(VAF28,1), isnull(VAF29,''), VAF30, VAF31, VAF32, VAF33, VAF34, VAF35, VAF36
    ,isnull(VAF37,'1899-12-30'), '1899-12-30', BCK01C, BCE02A, BCE03A, BCE03B, NULL
    ,NULL, NULL, Null, NULL, NULL, Null, 0 VAF51, 0
    ,VAF53, 0, VAF55, CBM01, BCK01D, VAF58, VAF59, VAF60, @lDate
    ,isnull(VAF61,0), isnull(VAF62,0), BCE01A, BCE01B,@lBCE01,@lBCE03
    ,Case when @lType >= 1 and @lType <= 2 and @pValue = '1' and @fChange = 1 
            and (@para203 <> '1' or (@para203='1' and @lBCE01 <> BCE01A)) then '1899-12-30' else @lDate end
    ,VAF36,isnull(VAF75,0),BCE01H,BCE03H,BIW02
    FROM #TmpVAF
    WHERE VAF01 > 0
   end
   else
   begin
    INSERT INTO VAF2(VAF01, VAF01A, VAF01B, VAF04, VAA01, VAF06, VAF07, BCK01A, ROWNR
     ,VAF10, VAF11, BDA01, BBX01, VAF14, VAF15, BBY01, VAF17, VAF18
     ,VAF19, VAF20, VAF21, VAF22, VAF23, BCK01B, VAF25, VAF26, VAF27
     ,VAF28, VAF29, VAF30, VAF31, VAF32, VAF33, VAF34, VAF35, VAF36
     ,VAF37, VAF38, BCK01C, BCE02A, BCE03A, BCE03B, BCE03C
     ,VAF45, BCE03D, VAF47, BCE03E, BCE03F, VAF50, VAF51, VAF52
     ,VAF53, VAF54, VAF55, CBM01, BCK01D, VAF58, VAF59, VAF60, VAF42
     ,VAF61, VAF62, BCE01A, BCE01B,BCE01G,BCE03G,VAF71,VAF74,VAF75,BCE01H,BCE03H,BIW02)
    SELECT VAF01, VAF01A, VAF01B, VAF04, VAA01, VAF06, VAF07, BCK01A, ROWNR
    ,VAF10, VAF11, BDA01, BBX01, VAF14, VAF15, BBY01, VAF17, VAF18
    ,VAF19, VAF20, VAF21, VAF22, VAF23, BCK01B
    , case when BDA01 >= '1' and BDA01 <= '2' and VAF23=@gpara50 then '?' else '' end VAF25, VAF26, VAF27
    ,isnull(VAF28,1), isnull(VAF29,''), VAF30, VAF31, VAF32, VAF33, VAF34, VAF35, VAF36
    ,isnull(VAF37,'1899-12-30'), '1899-12-30', BCK01C, BCE02A, BCE03A, BCE03B, NULL
    ,NULL, NULL, Null, NULL, NULL, Null, 0 VAF51, 0
    ,VAF53, 0, VAF55, CBM01, BCK01D, VAF58, VAF59, VAF60, @lDate
    ,isnull(VAF61,0), isnull(VAF62,0), BCE01A, BCE01B,@lBCE01,@lBCE03
    ,Case when @lType >= 1 and @lType <= 2 and @pValue = '1' and @fChange = 1 
            and (@para203 <> '1' or (@para203='1' and @lBCE01 <> BCE01A)) then '1899-12-30' else @lDate end
    ,VAF36,isnull(VAF75,0),BCE01H,BCE03H,BIW02
    FROM #TmpVAF
    WHERE VAF01 > 0 
   end 
  end




  IF Exists(Select * From #TmpVAO)
  Begin
   if @lACF01 <> 2
   begin
    INSERT INTO VAO1(VAO01,VAA01,VAA07,VAF01,ACF01,VAO06,VAO07,VAO10,VAO11
       ,BAK01A,CAM01,VAO15,BCE03A,VAO18,VAO22,VAO19,IAA01,IAD03)
    SELECT a.VAO01,a.VAA01,a.VAA07,CBM01,a.ACF01,a.VAO06,a.VAO07,a.VAO10,a.VAO11
       ,a.BAK01A,a.CAM01,a.VAO15,@lBCE03,a.VAO18,a.VAO22,@lDate,a.IAA01,a.IAD03
    FROM #TmpVAO a
   end
   else
   begin
    INSERT INTO VAO2(VAO01,VAA01,VAA07,VAF01,ACF01,VAO06,VAO07,VAO10,VAO11
       ,BAK01A,CAM01,VAO15,BCE03A,VAO18,VAO22,VAO19,IAA01,IAD03)
    SELECT a.VAO01,a.VAA01,a.VAA07,CBM01,a.ACF01,a.VAO06,a.VAO07,a.VAO10,a.VAO11
       ,a.BAK01A,a.CAM01,a.VAO15,@lBCE03,a.VAO18,a.VAO22,@lDate,a.IAA01,a.IAD03
    FROM #TmpVAO a
   end 
  End 
  --插入历史记录
        INSERT INTO VBG1(VBG01, VAF01, VBG03, BCE03, VBG06, VBG05)
        select @VBGid + ID,VAF01,1,@lBCE03,'新开医嘱',@lDate
        from #TmpVAF     
        --*****
      if not ((@lACF01 = 1) and (@lMode = 2))
      begin
        update n set n.BDJ04 =isnull(n.BDJ04,0)+m.fnum,n.BDA01 = m.BDA01 ,n.VAF01 = m.VAF01
        from (                      
        select a.BDA01,case when (a.BDA01 >= '1' and a.BDA01 <= '4') or a.BDA01 = 'M' then a.BBY01
                    when a.BDA01 >= 'A' and a.BDA01 <> 'M' then a.BBX01 end BBX01
          ,max(case when ((a.BDA01 = 'E' or a.BDA01 = 'S' or a.BDA01 = 'A') and a.VAF01A > 0) or (a.BDA01 = '3') 
                              then 0 else a.VAF01 end) VAF01 
          ,count(1) fnum                      
        from #tmpVAF a
        join #kbmBDJ b on (((a.BDA01 >= '1' and a.BDA01 <= '4')or a.BDA01 = 'M') and b.BBX01 = a.BBY01) or (a.BDA01 >= 'A' and a.BDA01 <> 'M' and b.BBX01 = a.BBX01)
        where a.BDA01 not in ('0','8') and b.BCE01 = @lBCE01
        group by a.BDA01,case when (a.BDA01 >= '1' and a.BDA01 <= '4') or a.BDA01 = 'M' then a.BBY01
                    when a.BDA01 >= 'A' and a.BDA01 <> 'M' then a.BBX01 end ) m
        join BDJ1 n on n.BDA01 = m.BDA01 and n.BBX01 = m.BBX01  
        where n.BCE01 = @lBCE01          
        
        select IDENTITY(int,1,1) AS ID,m.*
        into #tmpBDJ
        from (
        select a.BDA01,case when (a.BDA01 >= '1' and a.BDA01 <= '4') or a.BDA01 = 'M' then a.BBY01
                    when a.BDA01 >= 'A' and a.BDA01 <> 'M' then a.BBX01 end BBX01
          ,max(case when ((a.BDA01 = 'E' or a.BDA01 = 'S' or a.BDA01 = 'A') and a.VAF01A > 0) or (a.BDA01 = '3') 
                              then 0 else a.VAF01 end) VAF01 
          ,count(1) fnum
        from #tmpVAF a
        where a.BDA01 not in ('0','8')
          and not exists(select * from #kbmBDJ b where ((((a.BDA01 >= '1' and a.BDA01 <= '4')or a.BDA01 = 'M') and b.BBX01 = a.BBY01) or (a.BDA01 >= 'A' and a.BDA01 <> 'M' and b.BBX01 = a.BBX01)) and b.BCE01 = @lBCE01)
        group by a.BDA01,case when (a.BDA01 >= '1' and a.BDA01 <= '4') or a.BDA01 = 'M' then a.BBY01
                    when a.BDA01 >= 'A' and a.BDA01 <> 'M' then a.BBX01 end  )m
                    
        EXEC Core_NewID 'BDJ1','BDJ01',@lID out
            
        INSERT INTO BDJ1 (BDJ01,BCE01,BBX01,BDJ04,BDA01,BCK01,VAF01)            
        select @lID + ID,@lBCE01,BBX01,fnum,BDA01,0,VAF01
        from #tmpBDJ
        
        if @@error <> 0
        begin
          rollback tran
          RAISERROR('增加自定项目出错.', 16, 1) with nowait
          RETURN 9
        end 
        --提醒
        IF @lACF01 = 2
        Begin
          InSert Into VBZ1(VAF01,ACF01,VAA07,VAF11,VAF10,VAA01,VAA04,VAA05,BCQ04,BCK01,VAF35) 
          SELECT a.VAF01,@lACF01,a.VAF06,a.VAF11,1,a.VAA01,b.VAA04,b.VAA05,b.BCQ04,a.BCK01A,a.VAF35
          FROM #TmpVAF a
          join #TmpCBM b on b.CBM01 = a.CBM01
          WHERE a.VAF01 > 0 and a.VAF32 = 0 and ((a.BDA01 < 'A')or(a.BDA01 >= 'A' and a.VAF01A = 0))
        End  
      end     
  --********
  if exists(select * from #tmpVAX where VAF01 > 0)
  begin    
    insert into VAX1(VAX01,VAT01,VAY03,BCK01,ACE01,BCE01,BCE02,BCE03)
    select VAX01,case when @lACF01 = 1 and @lMode = 2 and exists(select * from @tmpVAT) then VAT01 else -VAF01 end
      ,0,0,ACE01,BCE01,BCE02,BCE03
    from #tmpVAX
    where VAF01 > 0 and ACE01 <> '--'
    
    if exists(select * from #tmpVBF)
    begin
      insert into VBF1(VBF01,VAF01,VBF03,VBF05,VBF07)
      select VBF01,VAF01,BCE02,uid,BCE03
      from #tmpVBF
    end
  end
  --挂号附表
  if @lACF01 = 1 and @lMode <> 2
  begin
    if exists(select * from #TmpCBM a where not exists(select * from VCJ1 b where b.VAA07 = a.VAA07))    
      insert into VCJ1(VAA07,VCJ09)
      select distinct VAA07,@lDate
      from #TmpCBM      
    else
    begin
      update a set a.VCJ09 = @lDate
      from VCJ1 a
      join #TmpCBM b on b.VAA07 = a.VAA07
    end  
    if @mzpara370='1'
    begin
      update a set a.BCE01A = @lBCE01,a.BCE02A=@lBCE02,a.BCE03A = @lBCE03
      from VAC1 a
      where exists(select * from #tmpCBM b where b.VAA07 = a.VAC01)
    end 
  end
  --临床路径
  if exists(select * from #TmpVAF where isnull(VCO01,0)>0 and VAF01 > 0) and @lMode <> 2
  begin    
    insert into VCR1(VCR01,VCO01,VAF01,BIJ01)
    select VCR01,VCO01,VAF01,BIJ01
    from #tmpVCR1
    
    update a 
    set a.VCO13 = case when ISNULL(a.VCO16,'1901-01-01') < '2000-01-01' then '' else '上组项目不适，选用此组项目' end,
       a.VCO16 = @lDate,a.BCE03A = @lBCE03,a.VCO12 = b.VCO12,a.VCO18 = b.VCO18,a.VCO21 = b.VCO21
    from VCO1 a
    join #tmpCBM b on b.VCO01 = a.VCO01
  end
  if @lACF01 = 1 and @lMode = 2 and exists(select * from @tmpVAT)       
  begin
    INSERT INTO CBM1
      (CBM01,VAA01,VAA07,VAP01,ACF01,CBM06,CBM07,CBM08,CBM09,BCK01A,BCK01B,BCK01C,BCE01,BCE03,CBM15,CBM16,VCY01)
    SELECT c.VAT01,a.VAA01,0,0,1,3,99,'CM'+substring(REPLICATE('0',10),1,10 -len(c.VAT01))+cast((c.VAT01) AS varchar)	
      ,'手术记录单',0,a.BCK01A,a.BCK01C,a.BCE01A,a.BCE03A,@lDate,a.VAF36,@VCYid
    FROM #TmpVaf a
    join @tmpVAT c on c.VAF01 = a.VAF01

    INSERT INTO VAT1(VAT01, VAA01, VAA07, VAT04, ACF01, VAF01, VAT08,VAT16, BBX01, ACI02, BCK01,VCY01,VAT33)
    SELECT d.VAT01,a.VAA01,0,0,a.VAF04,a.VAF01,a.VAF14 ,b.VAF22, b.BBX01,b3.ACI02 ,a.BCK01B,@VCYid,a.VAF23
    FROM @tmpVAT d
    join #TmpVaf a on a.VAF01 = d.VAF01
    LEFT JOIN #TmpVaf b ON b.VAF01A = a.VAF01 AND b.BDA01 = 'A'
    LEFT JOIN BBX1 b2 ON b.BBX01 = b2.BBX01
    LEFT JOIN ACI1 b3 ON b2.BBX13 = b3.ACI01
    
    insert into VAV1(VAV01, VAT01, VAV03, VAV04, VAV05, BAK01, BBX01,ACI02,VAV09)
    select VAV01,VAT01,1,0,VAV05,0,BBX01,'',0
    from @tmpVAV
  end

DECLARE @llbce02mac VARCHAR(30)
set @llbce02mac = @lBCE02+@lMac

INSERT INTO RESTIN1(URIID,USRCODE,TABID) 
VALUES (@lURIID,@llbce02mac,0)

COMMIT TRAN

  IF Object_id('tempdb..#TmpCBM') IS NOT NULL DROP TABLE #TmpCBM
  IF Object_id('tempdb..#TmpVAO') IS NOT NULL DROP TABLE #TmpVAO
  IF Object_id('tempdb..#TmpVAF') IS NOT NULL DROP TABLE #TmpVAF
  IF Object_id('tempdb..#TmpVBQ') IS NOT NULL DROP TABLE #TmpVBQ 
  IF Object_id('tempdb..#TmpVAX') IS NOT NULL DROP TABLE #TmpVAX