--检验系统医技执行完成
--Alter 8058 2020.6.10 增加了医嘱id是0的问题处理，只处理有医嘱id项目
ALTER Proc [dbo].[Nurse_VBI13_update]
  @lXml ntext
  ,@lBCE01 int
  ,@BCE02 varchar(20)
  ,@BCE03 varchar(20)   
As
  DECLARE @iDOM int, @iret int

  if not exists(select * from syscolumns where id=object_id('TableLog'))
  create table TableLog(
    FText varchar(8000)
	, FNow datetime
  )

  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  
  Create Table #tmpVAF1(VAF01 int,VAF21 numeric,VAF23 varchar(512),VBI13 tinyint,uid int IDENTITY(1,1))

    SELECT distinct VAF01, 1 as ID, LAB01
	into #tmp001
    from OpenXml(@iDOM,'/Root/VAJT/Ie',8)
      WITH (VAF01 int, LAB01 int)
    where isnull(VAF01,0) > 0

	insert into TableLog(FNow, FText)
    select top 1 getdate(), convert(varchar, LAB01) from #tmp001

	INSERT INTO #tmpVAF1(VAF01,VBI13)
	select VAF01, 1 from #tmp001
  exec sp_xml_removedocument @iDOM  
  -----------------------------------------------------------
  /*20200311谢升周增加：无VAF01时，通过条码检索VAF01*/
  if object_id('tempdb..#tmpVAF') is not null drop table #tmpVAF
  select * into #tmpVAF from #tmpVAF1 where 1=2
  if not exists(select * from #tmpVAF1 where VAF01 > 0)
  begin
    INSERT INTO #tmpVAF(VAF01,VBI13)
    SELECT  A.VAF01,1 FROM VBI1 A
     join LAB1 b on b.LAB42 = a.VBI21
    WHERE VBI21 = LAB42 AND LAB32=3 and EXISTS(SELECT * FROM #tmp001 c WHERE c.LAB01 = b.LAB01)
   end
   else
   begin
     INSERT INTO #tmpVAF(VAF01,VBI13)
	   select VAF01, 1 from #tmpVAF1
   end

  drop table #tmp001
  drop table #tmpVAF1
  -----------------------------------------------------------
  delete from #tmpVAF where VAF01<1
  If not exists(select * from #tmpVAF)
  BEGIN
   IF Object_id('tempdb..#tmpVAF') IS NOT NULL DROP TABLE #tmpVAF
   --RAISERROR('提交费用信息为空，没有需要执行扣费的明细.', 16, 1) with nowait
   RETURN 1
  END
  declare @lDate datetime
  set @lDate = Getdate()
  select @lBCE01 = BCE01 from BCE1 where BCE02 = @BCE02
begin try
begin tran
  update a 
  set a.VBI13 = b.VBI13,a.VBI12 = @lDate,a.VBI24 = '医技执行完成，操作人：'+@BCE03
  from VBI1 a
  join #tmpVAF b on b.VAF01 = a.VAF01
  
  update a
  set a.VAJ53 = b.VBI13,a.VAJ51 = @lDate,a.VAJ52 = @lDate,a.BCE03C = @BCE03,a.BCE02C = @BCE02
  from VAJ1 a
  join #tmpVAF b on b.VAF01 = a.VAF01 
  where a.BDN01 > '3'   
  if exists(select a.VAF01 from #tmpVAF a join VAF2 b on b.VAF01 = a.VAF01)
  begin
    update a 
    set a.VBI13 = b.VBI13,a.VBI12 = @lDate,a.VBI24 = '医技执行完成，操作人：'+@BCE03
    from VBI2 a
    join #tmpVAF b on b.VAF01 = a.VAF01
  
    update a
    set a.VAJ53 = b.VBI13,a.VAJ51 = @lDate,a.VAJ52 = @lDate,a.BCE03C = @BCE03,a.BCE02C = @BCE02
    from VAJ2 a
    join #tmpVAF b on b.VAF01 = a.VAF01 
    where a.BDN01 > '3'  
	
	--插入医技记录
	declare @lVBJ01 int,@aMax int, @Para1 varchar(10)  
	set @Para1 = dbo.GetSysParamValue(100,107001,1)   
	EXEC Core_NewIDEx 'VBJ1','VBJ01',@lVBJ01 out,@aMax                           
    Set @lVBJ01 = @lVBJ01 - @aMax           
                           
    INSERT INTO VBJ1(VBJ01,VAF01,VBI03,VBJ04,VBJ05,VBJ06,BCE03A                          
      ,VBJ08,BCE03B,VBJ10,VBJ11,BCE03C,BCE03D,VBJ14,VBJ15,VBJ16                          
      ,VBJ17,VBJ18,VBJ19,VBJ20,VBJ21,VAA01,VAA07,ACF01)                          
    SELECT @lVBJ01 + a.uid,a.VAF01,c.VBI03,@lDate,a.VAF21,'',@BCE03                          
      ,case when @Para1 = '1' then @lDate ELSE null end,@BCE03,@lDate,0,@BCE03 ,@BCE03 ,0,c.VBI06,0                        
      ,0,0 ,0,0,a.VAF23,c.VAA01,c.VAA07,c.ACF01                          
    FROM #tmpVAF a                          
    Join VBI1 c On a.VAF01 = c.VAF01    
  end
commit tran
end try
begin catch
	if @@TRANCOUNT > 0
	begin
	  rollback tran
	  declare @msg varchar(1000)
	  set @msg=error_message()
	  RAISERROR(@msg, 16, 1,1) with nowait
	end
end catch
  IF Object_id('tempdb..#tmpVAF') IS NOT NULL DROP TABLE #tmpVAF