SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--打印瓶签、注射单、口服单、治疗单、护理单生成条码    住院通用
--Alter 8058 2017.8.21
ALTER PROC [dbo].[Nurse_Ivggt_Bills_Print_Auto]
  @lxml  varchar(1024)
  ,@lACF01 tinyint --
  ,@BBX20 varchar(128)
  ,@lBCK01 int  
  ,@aType tinyint  --1：瓶签 2:口服单、注射单、治疗单、护理单
  ,@lBCE03 varchar(20)-- 采样人   
AS
  --单类型、单、组、执行日期、总频次数、次数
  --
  Declare @lVBI29A tinyint,@lVBI29B tinyint,@smsg varchar(1028) 
  DECLARE @iDOM int, @iret int  
  DECLARE @lDateG datetime, @lid int, @lVBI21 varchar(20), @lStr varchar(10) , @lPara262 varchar(10) , @lPara428 varchar(10)
  declare @TmpVAFsztmp Table(ID int IDENTITY(1,1),VAF01 int, 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
     ,VAF18 numeric(18,4),VAF21 numeric(18,4),VAA01 int,VAA07 int,VAP01 int
     ,BCK01A int,VAF35 tinyint,VAF22 varchar(1024),VAF04 tinyint,VAF54 tinyint
     ,VAF36 datetime,VAF14 varchar(60),BBX01 int,VAF31 tinyint,VAF59 int,VAF11 tinyint
     ,VAF23 varchar(128),VAF27 int,VAF15 varchar(30),VAF61 numeric(18,2),VAF53 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   
  INSERT INTO @TmpVAFsztmp(VAF01,VAA01)
  SELECT VAF01,VAA01
  from OpenXml(@iDOM,'/Root/VAF1/Ie',8)
    WITH (VAF01 int ,VAA01 int,VAF11 tinyint)	  
     
  If not exists(select * from @TmpVAFsztmp)or (@@ERROR <> 0)
  BEGIN
   exec sp_xml_removedocument @iDOM  
  -- RAISERROR('从文档中提取医嘱信息错误.', 16, 1) with nowait
   RETURN 2
  END
  exec sp_xml_removedocument @iDOM  
   
  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.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.VAF59=b.VAF59,a.VAF11=b.VAF11   
    ,a.VAF36 = b.VAF36,a.VAF14 = b.VAF14,a.BBX01 = b.BBX01
    ,a.VAF31 = b.VAF31 ,a.VAF27 = b.VAF27,a.VAF61=b.VAF61
    ,a.VAF15=b.VAF15,a.VAF04=b.VAF04,a.VAF54=b.VAF54,a.VAF53=b.VAF53
  FROM @TmpVAFsztmp a
  JOIN VAF2 b with(nolock) ON b.VAF01 = a.VAF01 

  Insert into @TmpVAFsztmp(VAF01, BDA01 ,VAF01A, Rownr, CBM01,VAF10,BCK01B , BCK01C ,BCK01D ,BCE02A ,BCE03A 
      ,VAF32 ,VAF58,VAF18,VAF21,VAA01,VAA07,VAP01,BCK01A,VAF35,VAF22,VAF36
      ,VAF14,BCE01A,BBX01,VAF31,VAF23,VAF27,VAF15,VAF04,VAF54,VAF59,VAF11,VAF61,VAF53)
  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.VAF18,a.VAF21,a.VAA01,a.VAF06,a.VAF07,a.BCK01A,a.VAF35
    ,a.VAF22,a.VAF36,a.VAF14,a.BCE01A,a.BBX01,a.VAF31
    ,a.VAF23,a.VAF27,a.VAF15,a.VAF04,a.VAF54,a.VAF59,a.VAF11,a.VAF61,a.VAF53
  From @TmpVAFsztmp b
  Join VAF2 a with(nolock) on a.VAF01 = b.VAF01A 
  Where b.BDA01 >= '1' And b.BDA01 <= '2' And b.Rownr = 1 and a.VAF32=1 
  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.VAF18,a.VAF21,a.VAA01,a.VAF06,a.VAF07,a.BCK01A,a.VAF35
    ,a.VAF22,a.VAF36,a.VAF14,a.BCE01A,a.BBX01,a.VAF31
    ,a.VAF23,a.VAF27,a.VAF15,a.VAF04,a.VAF54,a.VAF59,a.VAF11,a.VAF61,a.VAF53
  From @TmpVAFsztmp b
  Join VAF2 a with(nolock) on a.VAF01A = b.VAF01
  Where b.BDA01 >='8' and a.VAF01A>0 and a.VAF32<>1  

  set @lPara262 = dbo.GetSysParamValue(100,105003,262)
  set @lPara428 = dbo.GetSysParamValue(100,105001,428)
  Set @lVBI29A = 0
  Set @lVBI29B = 0
  set @smsg = ''
  if @aType = 0
  begin
    Set @lVBI29A = 0
    Set @lVBI29B = 2
  end
  if @aType = 1
  begin
    Set @lVBI29A = 0
    Set @lVBI29B = 1
  end
  
  SET @lDateG = Getdate()

  declare @tmpBBX table(BBX20 int)
  
  declare @tmpVAF Table(uid int IDENTITY(1,1),VAF06 int,VAP01 int,VBI36 varchar(20),BCK01B int,CBM01 int,VAF59 int
                        ,VBI10 datetime,VBI21 int,VAF01 int,VAF27 int,VCM12 int,VBI01 int,VAF11 tinyint)
    
  insert into @tmpBBX(BBX20)
  select Value from dbo.Split(@BBX20,',')    
  
 -- if not exists(select * from @tmpVAE)
 -- begin
 --   RAISERROR('请先选择病人.',16,1) with nowait
 --   return 1
 -- end
  
  select b.* 
  into #kbmVBI
  from VBI2 b with(nolock) 
  join @TmpVAFsztmp a on a.VAF01=b.VAF01
  where (isnull(b.VBI29,0) = @lVBI29A or isnull(b.VBI29,0) = @lVBI29B)



    SELECT a.VAA07,a.VAP01,a.BCK01D,a.CBM01,a.VAF59,a.ROWNR
      ,case when ISNULL(a.VAF27,0)>0 then a.VAF27 else 1 end VAF27
      ,a.VAF61,CAST(1 as int) VCM12,cast('瓶签' as varchar(20)) VBI36
      ,convert(char(10),b.VBI10,120) VBI10,convert(char(10),a.VAF36,120) VAF36
      ,b.VBI01 ,a.VAF01,a.VAF11,a.BDA01
    into #tmpVCM  
    FROM @TmpVAFsztmp a
    JOIN @TmpVAFsztmp a1 ON a.VAF01A = a1.VAF01
    JOIN BBX1 a2 with(nolock) ON a1.BBX01 = a2.BBX01   
    JOIN #kbmVBI b ON a.VAF01 = b.VAF01
    WHERE @aType = 1 AND (a.BDA01 = '1' or a.BDA01 = '2') AND a2.BDA01 = 'T' AND a2.BBX13 = '2'
     -- AND Exists(Select s.* from BCV1 s with(nolock) where s.BCK01A = @lBCK01 and (a.BCK01D = s.BCK01A or a.BCK01D = s.BCK01B)) 
      AND a2.BBX20 =2 and (isnull(b.VBI29,0) = @lVBI29A or isnull(b.VBI29,0) = @lVBI29B) and a.VAF54 = 0   
    union all
    SELECT a.VAA07,a.VAP01,a.BCK01D, a.CBM01, a.VAF59, a.Rownr
      ,case when ISNULL(a.VAF27,0)>0 then a.VAF27 else 1 end VAF27
      ,a.VAF61,CAST(1 as int) VCM12
      ,CASE WHEN a2.BBX20 = 0 THEN '口服单' WHEN a2.BBX20 = 1 OR (@lPara428='1' and a2.BBX20=4) THEN '注射单'
        when (a2.BBX20 = 2) or (@lPara428<>'1' and a2.BBX20 = 4) then '输液单'
        When a2.BBX20 = 3 then '治疗单' When a2.BBX20 = 5 Then '输血单'
        When a2.BBX20 = 6 Then '护理单' end as VBI36
      ,convert(char(10),b.VBI10,120) VBI10,convert(char(10),a.VAF36,120) VAF36
      ,b.VBI01 ,a.VAF01,a.VAF11,a.BDA01
    FROM @TmpVAFsztmp a
    JOIN @TmpVAFsztmp a1 ON a.VAF01A = a1.VAF01
    JOIN BBX1 a2 with(nolock) ON a1.BBX01 = a2.BBX01 
    JOIN #kbmVBI b ON a.VAF01 = b.VAF01
    WHERE @aType = 2 and a.VAF54 = 0
      AND (a.BDA01 >= '1' And a.BDA01 <= '3') AND a2.BDA01 = 'T' AND a2.BBX13 in ('2','4')
     -- AND Exists(Select s.* from BCV1 s with(nolock) where s.BCK01A = @lBCK01 and (a.BCK01D = s.BCK01A or a.BCK01D = s.BCK01B))
      AND exists(select * from @tmpBBX n where a2.BBX20 = n.BBX20) and (isnull(b.VBI29,0) = @lVBI29A or isnull(b.VBI29,0) = @lVBI29B)    
    Union All
    Select a.VAA07,a.VAP01,a.BCK01B, a.CBM01, a.VAF59, a.Rownr
      ,case when ISNULL(a.VAF27,0)>0 then a.VAF27 else 1 end VAF27
      ,a.VAF61,CAST(1 as int) VCM12
      ,CASE WHEN a1.BBX20 = 0 THEN '口服单' WHEN a1.BBX20 = 1 OR (@lPara428='1' and a1.BBX20=4) THEN '注射单'
        when (a1.BBX20 = 2) or (@lPara428<>'1' and a1.BBX20 = 4) then '输液单'
        When a1.BBX20 = 3 then '治疗单' When a1.BBX20 = 5 Then '输血单'
        When a1.BBX20 = 6 Then '护理单' end VBI36
      ,convert(char(10),b.VBI10,120) VBI10,convert(char(10),a.VAF36,120) VAF36
      ,b.VBI01 ,a.VAF01,a.VAF11,a.BDA01
    From @TmpVAFsztmp a
    Join BBX1 a1 with(nolock) on a.BBX01 = a1.BBX01
    JOIN #kbmVBI b ON a.VAF01 = b.VAF01
    where @aType = 2 and a.BDA01 in ('B','D','N','O','T','Z') and a.VAF32 = 0 and a.VAF01A = 0 And a.VAF54 = 0
     -- And Exists(Select s.* from BCV1 s with(nolock) where s.BCK01A = @lBCK01 and (a.BCK01B = s.BCK01A or a.BCK01B = s.BCK01B))
      and exists(select * from @tmpBBX n where a1.BBX20 = n.BBX20) And (isnull(b.VBI29,0) = @lVBI29A or isnull(b.VBI29,0) = @lVBI29B)   
    Union All
    Select a.VAA07,a.VAP01,a.BCK01B, a.CBM01, a.VAF59, a.Rownr
      ,case when ISNULL(a.VAF27,0)>0 then a.VAF27 else 1 end VAF27
      ,a.VAF61,CAST(1 as int) VCM12
      ,CASE WHEN a.VAF53 = 0 THEN '口服单' WHEN a.VAF53 = 1 OR (@lPara428='1' and a.VAF53=4) THEN '注射单'
        when (a.VAF53 = 2) or (@lPara428<>'1' and a.VAF53 = 4) then '输液单'
        When a.VAF53 = 3 then '治疗单' When a.VAF53 = 5 Then '输血单'
        When a.VAF53 = 6 Then '护理单' end VBI36
      ,convert(char(10),b.VBI10,121) VBI10,convert(char(10),a.VAF36,121) VAF36
      ,b.VBI01 ,a.VAF01,a.VAF11,a.BDA01
    From @TmpVAFsztmp a
    JOIN #kbmVBI b ON a.VAF01 = b.VAF01    
    where @aType = 2 and a.BDA01 = '0' And a.VAF54 = 0
      and exists(select * from @tmpBBX n where a.VAF53 = n.BBX20) And (isnull(b.VBI29,0) = @lVBI29A or isnull(b.VBI29,0) = @lVBI29B)
      --And Exists(Select s.* from BCV1 s with(nolock) where s.BCK01A = @lBCK01 and (a.BCK01B = s.BCK01A or a.BCK01B = s.BCK01B))       
  
  if not exists(select * from #tmpVCM)
  begin
    RAISERROR('无符合查询条件的医嘱.',16,1) with nowait
    return 2
  end
   
  declare @MaxVAF27 int,@VAF27 int,@VCM12 int
  update #tmpVCM set VAF27 = 1 where VAF27 = 0
  update #tmpVCM set VAF61 = VAF27 where (VAF61 is null) or VAF11 = 2  
  
  delete from #tmpVCM where VBI10 = VAF36 and VAF61 = 0 and VAF11 = 1 and (@lPara262 = '1' or (@lPara262 <> '1' and BDA01 >= '1' and BDA01 <= '3'))
  
  update #tmpVCM set VAF27 = VAF61 where VBI10 = VAF36 and VAF61 <> VAF27 and VAF11 = 1
   
  insert into @tmpVAF(VAF06,VAP01,VBI36,BCK01B,CBM01,VAF59,VBI10,VAF01,VAF27,VCM12,VBI01,VAF11)
  select VAA07,VAP01,VBI36,BCK01D,CBM01,VAF59,VBI10,VAF01,VAF27,VCM12,VBI01,VAF11
  from #tmpVCM   
  
  select @MaxVAF27 = max(VAF27) from #tmpVCM
  set @VAF27 = 2

  while @VAF27 <= @MaxVAF27
  begin
    insert into @tmpVAF(VAF06,VAP01,VBI36,BCK01B,CBM01,VAF59,VBI10,VAF01,VAF27,VCM12,VBI01,VAF11)
    select VAA07,VAP01,VBI36,BCK01D,CBM01,VAF59,VBI10,VAF01,VAF27,@VAF27,VBI01,VAF11
    from #tmpVCM 
    where VAF27 >= @VAF27
    
    set @VAF27 = @VAF27 + 1
  end 
  
  if exists(
    select a.*
    from @tmpVAF a
    join VCM1 b with(nolock) on b.VAA07 = a.VAF06 and b.VAP01 = a.VAP01 and b.VCM13 = a.VBI36 and b.VBI01 = a.VBI01
      and b.VCM10 = a.VBI10 and b.VCM12 = a.VCM12 and a.VAF27 = b.VCM11
    where b.ACF01 = @lACF01 )
  begin
    update s set s.VBI29 = Case when @aType = 0 and ISNULL(s.VBI29,0) in (0,2) then ISNULL(s.VBI29,0)+1 
                when @aType=1 and ISNULL(s.VBI29,0) in (0,1) then ISNULL(s.VBI29,0)+2 
				when @aType=2  then  s.VBI29
				else 1 end 
    from vbi2 s with(rowlock)
    where exists(
      select a.*
      from @tmpVAF a
      join VCM1 b with(nolock) on b.VAA07 = a.VAF06 and b.VAP01 = a.VAP01 and b.VCM13 = a.VBI36 and b.VBI01 = a.VBI01
         and b.VCM10 = a.VBI10 and b.VCM12 = a.VCM12 and a.VAF27 = b.VCM11
      where b.ACF01 = @lACF01 and s.vbi01 = a.VBI01 )
    
    delete a
    from @tmpVAF a
    join VCM1 b with(nolock) on b.VAA07 = a.VAF06 and b.VAP01 = a.VAP01 and b.VCM13 = a.VBI36 and b.VBI01 = a.VBI01
      and b.VCM10 = a.VBI10 and b.VCM12 = a.VCM12 and a.VAF27 = b.VCM11
    where b.ACF01 = @lACF01
  end      
  
  select IDENTITY(int,1,1) uid,0 ID,a.*
  into #kbmVCM
  from (select distinct VAF06,VAP01,VBI36,BCK01B,VBI10,CBM01,VAF59,VCM12,VAF11 from @tmpVAF) a
  group by a.VAF06,a.VAP01,VAF11,a.VBI36,a.VBI10,a.CBM01,a.VAF59,a.VCM12,a.BCK01B
  
  if not exists(select * from #kbmVCM)
  begin
    RAISERROR('无符合查询条件的执行记录.',16,1) with nowait
    return 3
  end
  declare @vPrefix varchar(10),@xhLen int
  Select @MaxVAF27 = Max(uid) From #kbmVCM
  
  exec Core_NewId_CBW1 '27',@xhLen out,@vPrefix out,@lid out,@MaxVAF27
  set @lid = @lid - @MaxVAF27
  SET @lStr = REPLICATE('0',@xhLen)
  update #kbmVCM set ID = @lid + uid
  
  update a set a.VBI21 = b.ID
  from @tmpVAF a
  join #kbmVCM b on b.VAF06 = a.VAF06 and b.VAP01 = a.VAP01 and b.VBI36 = a.VBI36 and b.CBM01 = a.CBM01
       and b.VAF59 = a.VAF59 and b.VBI10 = a.VBI10 and b.VCM12 = a.VCM12 and b.BCK01B = a.BCK01B and b.VAF11 = a.VAF11
  
  insert into VCM1(VAA01,VAA07,VAP01,ACF01,VBI01,VAF01,VAF22,VBI21,VCM10,VCM11,VCM12,VCM13,BCE03B)
  select b.VAA01,a.VAF06,a.VAP01,b.VAF04,a.VBI01,a.VAF01,b.VAF22
    ,@vPrefix+substring(@lstr,1,len(@lStr)-len(a.VBI21))+cast(a.VBI21 AS varchar)
    ,a.VBI10,a.VAF27,a.VCM12,a.VBI36,@lBCE03
  from @tmpVAF a
  join @TmpVAFsztmp b on b.VAF01 = a.VAF01
  
  --更新为已打印
  update s set s.VBI29 = Case when @aType = 0 and ISNULL(s.VBI29,0) in (0,2) then ISNULL(s.VBI29,0)+1 
                when @aType=1 and ISNULL(s.VBI29,0) in (0,1) then ISNULL(s.VBI29,0)+2
				when @aType=2  then  s.VBI29
				else 1 end 
    from vbi2 s with(rowlock)
    where exists(
      select a.*
      from @tmpVAF a
      join VCM1 b with(nolock) on b.VAA07 = a.VAF06 and b.VAP01 = a.VAP01 and b.VCM13 = a.VBI36 and b.VBI01 = a.VBI01
         and b.VCM10 = a.VBI10 and b.VCM12 = a.VCM12 and a.VAF27 = b.VCM11
      where b.ACF01 = @lACF01 and s.vbi01 = a.VBI01 )