--门诊套餐执行时生成条码信息
--Alter 8058 2017.5.22
ALTER PROC [dbo].[Clinic_VEB1_Bills]
  @scbm01 varchar(512) 
  ,@lBCE03 varchar(20)--采样人
AS
  --先统计出有多少个试管，需要生成多少个条码
  Declare @aDateB Datetime, @aDateE Datetime,@lDateB datetime
  set @lDateB=GETDATE()
  set @aDateB = Convert(varchar(19),@lDateB,21)+'.000'
  set @aDateE = Convert(varchar(19),@lDateB,21)+'.999'

  DECLARE @lDateG datetime, @lid int, @lVBI21 varchar(20), @lStr varchar(10),@lvap01 int,@bda01 varchar(2),@cbmid int 
  DECLARE @lBEL01 varchar(4), @lVAF06 int, @lBCK01 int, @lVAF14 varchar(60), @lVAF35 tinyint,@lBCE01A int,@lBCE03A varchar(20)
  Declare @aMin int, @aMax int, @lVBI29 tinyint, @lVBQ01 int, @aPart tinyint,@BBC01 varchar(10),@VAF01 int,@VBQ12 datetime,@xhLen int
  declare @kbCBMid table(cbm01 int)
  SET @lDateG = Getdate()
  create table #tmpVAFid(VAF01 int,VAF01A int)    
  Create Table #tmpBEL (ID int IDENTITY(1,1),VAF06 int,BEL01 varchar(20),BCK01B int,VAF35 tinyint,VAF14 varchar(60)
                        ,BBC01 varchar(10),VBI21 int,VAF01 int,BCE01A int,VAP01 int,BDA01 varchar(2),CBM01 int)
  create Table #tmpVBQd(VBQ01 int)
  insert into @kbCBMid(cbm01)
  select * from dbo.Split(@scbm01,',')    

      insert into #tmpVAFid(VAF01,VAF01A)
      select VAF01,VAF01A 
      from VAF1 
      where CBM01 = @cbmid AND VAF10 = 8
      
        Insert Into #tmpBEL(CBM01,VAF06,BEL01,BCK01B,VAF35,VAF14,BCE01A,VAP01,VAF01,BDA01)
        select distinct s.CBM01,s.VAF06,s.BEL01,s.BCK01B,s.VAF35,s.VAF14,s.BCE01A,s.VAP01,s.sVAF01,'L'
        from (
          SELECT a.CBM01,a.VAF06,Isnull(b.BBX19,'') AS BEL01,a.BCK01B,a.VAF35,a.VAF14,a.BCE01A,0 VAP01
            ,case when a.VAF58=3 then a.VAF01 else 0 end sVAF01
	      FROM VAF1 a with(nolock) 
	      join VBI1 c with(nolock) on c.VAF01 = a.VAF01
          join BBX1 b with(nolock) on a.BBX01 = b.BBX01
	      WHERE exists(select * from @kbCBMid m where m.cbm01=a.CBM01) and a.BDA01 = 'L' and a.VAF04 = 1 and a.VAF10 = 8
	        and ISNULL(c.VBI21,'')='' and Isnull(c.VBI29,0) = 0 ) s	     
        union all      
        select Distinct s.CBM01,s.VAF06,'' bel01,s.BCK01B,s.VAF35,s.BBC01,s.BCE01A,s.VAP01,s.sVAF01,'E'
        from (       
          select a.CBM01,a.VAF06,a.BCK01B,a.VAF35,isnull(b.BBX13,'') BBC01,a.BCE01A,0 VAP01
            ,case when a.VAF58=3 then a.VAF01 else 0 end sVAF01
          FROM VAF1 a with(nolock) 
	      join VBI1 c with(nolock) on c.VAF01 = a.VAF01
          join BBX1 b with(nolock) on a.BBX01 = b.BBX01
	      WHERE exists(select * from @kbCBMid m where m.cbm01=a.CBM01) and a.BDA01 = 'E' and a.VAF04 = 1 and a.VAF10 = 8 and a.VAF01A = 0
	        and ISNULL(c.VBI21,'')='' and Isnull(c.VBI29,0) = 0 ) s	     
        union all      
        select Distinct s.CBM01,s.VAF06,'' bel01,s.BCK01B,s.VAF35,s.BBC01,s.BCE01A,s.VAP01,s.sVAF01,s.BDA01
        from (       
          select a.CBM01,a.VAF06,a.BCK01B,a.VAF35,'' BBC01,a.BCE01A,0 VAP01,0 sVAF01
            ,case when a.BDA01='3' then '3' else '1' end BDA01
          FROM VAF1 a with(nolock) 
	      join VBI1 c with(nolock) on c.VAF01 = a.VAF01
          join BBX1 b with(nolock) on a.BBX01 = b.BBX01
	      WHERE exists(select * from @kbCBMid m where m.cbm01=a.CBM01) and a.BDA01 in ('1','2','3') and a.VAF10 = 8
	        and ISNULL(c.VBI21,'')='' and Isnull(c.VBI29,0) = 0 ) s	   
	    union all      
        select Distinct s.CBM01,s.VAF06,'' bel01,s.BCK01B,s.VAF35,s.BBC01,s.BCE01A,s.VAP01,s.sVAF01,s.BDA01
        from (       
          select a.CBM01,a.VAF06,a.BCK01B,a.VAF35,'' BBC01,a.BCE01A,0 VAP01,a.VAF01 sVAF01,a.BDA01
          FROM VAF1 a with(nolock) 
	      join VBI1 c with(nolock) on c.VAF01 = a.VAF01
          join BBX1 b with(nolock) on a.BBX01 = b.BBX01
	      WHERE exists(select * from @kbCBMid m where m.cbm01=a.CBM01) and a.BDA01 not in ('1','2','3','E','L') and a.VAF10 = 8 and a.VAF32=0 and a.VAF01A=0
	        and ISNULL(c.VBI21,'')='' and Isnull(c.VBI29,0) = 0 ) s	       
    
    Set @lVBI29 = 1
  if not Exists(select * from #TmpBEL)
  begin
    if object_id('tempdb.dbo.#TmpBEL') is not null drop table #TmpBEL
    Return 1
  end
  declare @vPrefix varchar(10),@apara1 varchar(2)
    set @apara1 = '13'   
  Select @aMin = Min(Id),@aMax = Max(ID) From #tmpBEL
 
  exec Core_NewId_CBW1 @apara1,@xhLen out,@vPrefix out,@lid out,@aMax
  set @lid = @lid - @aMax
  SET @lStr = REPLICATE('0',@xhLen)
  update #tmpBEL set VBI21 = @lid + ID
BEGIN TRAN  
  WHILE @aMin <= @aMax
  BEGIN
    Select @lVAF06 = VAF06,@lBEL01 = isnull(BEL01,''),@lBCK01 = isnull(BCK01B,0),@lVAF35 = isnull(VAF35,0),@lVAF14 = isnull(VAF14,'')
       ,@lid = VBI21,@BBC01 = ISNULL(BBC01,''),@VAF01 = ISNULL(VAF01,0),@lBCE01A = ISNULL(BCE01A,0),@bda01=BDA01,@cbmid=CBM01
    From #tmpBEL
    Where ID = @aMin

  	SET @lVBI21 = @vPrefix+substring(@lstr,1,len(@lStr)-len(@lid))+cast(@lid AS varchar)  
  	select @lBCE03A = BCE03 from BCE1 with(nolock) where BCE01 = @lBCE01A  
  	 EXEC Core_NewId_VBQ01 @lVBQ01 out
        Insert Into VBQ1(VBQ01,VBQ02,VAA01,VAA07,ACF01,VAP01,VBQ07,BCK01A,BCK01B,BCE03A
          ,BCE01A ,VBQ12,VBQ15,BEL01,ABB02,BCK01C,VBQ19,PRNCP,BCE03D,BDA01)
        Select @lVBQ01,b.VAA03,a.VAA01,a.VAA07,a.ACF01,a.VAP01,@lVAF35,a.BCK01B,a.BCK01C,a.BCE03
          ,a.BCE01 ,a.CBM15,@lVBI21,@lBEL01,@lVAF14,@lBCK01,0,1,@lBCE03,@BDA01
        From CBM1 a with(nolock)
        join VAA1 b with(nolock) on b.VAA01 = a.VAA01
        Where CBM01 = @cbmid
        
  	  if @BDA01 = 'L'
      begin
        UPDATE a
        SET a.VAF51 = @lVBQ01
        FROM VAF1 a 
	    JOIN VBI1 d ON a.VAF01 = d.VAF01
        join BBX1 b on a.BBX01 = b.BBX01
	    WHERE a.CBM01 = @cbmid and a.BDA01 = 'L' AND a.VAF10 = 8
	      AND isnull(b.BBX19,'') = @lBEL01 and a.BCE01A = @lBCE01A And Isnull(d.VBI29,0) = 0  
          AND a.VAF06 = @lVAF06 and a.BCK01B = @lBCK01 and a.VAF35 = @lVAF35 And a.VAF14 = @lVAF14
	      and ((@VAF01>0 and a.VAF01=@VAF01)or @VAF01=0)	        
        --医嘱发送记录
        UPDATE d
        SET d.VBI21 = @lVBI21,d.BCE03B = @lBCE03,d.VBI20 = @lDateG,d.VBI29 = @lVBI29
        FROM VAF1 a
	    JOIN VBI1 d ON a.VAF01 = d.VAF01
        join BBX1 b on a.BBX01 = b.BBX01
	    WHERE a.CBM01 = @cbmid and a.BDA01 = 'L' and a.VAF04 = 1 AND a.VAF10 = 8
	      AND isnull(b.BBX19,'') = @lBEL01 and a.BCE01A = @lBCE01A And Isnull(d.VBI29,0) = 0  
          AND a.VAF06 = @lVAF06 and a.BCK01B = @lBCK01 and a.VAF35 = @lVAF35 And a.VAF14 = @lVAF14
          and ((@VAF01>0 and a.VAF01=@VAF01)or @VAF01=0)
	  end  
	  if @BDA01 = 'E'
      begin
        UPDATE a
        SET a.VAF51 = @lVBQ01
        FROM VAF1 a
        join BBX1 b on a.BBX01 = b.BBX01
        join VBI1 c on c.VAF01 = a.VAF01
        WHERE a.CBM01 = @cbmid and a.BDA01 = 'E' AND a.VAF10 = 8 and a.BCE01A = @lBCE01A
          AND a.VAF06 = @lVAF06 and Isnull(c.VBI29,0) = 0  
          and a.BCK01B = @lBCK01 and a.VAF35 = @lVAF35 And @BBC01 = ISNULL(b.BBX13,'')  
          and ((@VAF01>0 and (a.VAF01=@VAF01 or a.VAF01A=@VAF01))or @VAF01=0)      
        UPDATE d
        SET d.VBI21 = @lVBI21,d.BCE03B = @lBCE03,d.VBI20 = @lDateG,d.VBI29 = @lVBI29
        FROM VAF1 a
        join BBX1 b on a.BBX01 = b.BBX01
        join VBI1 d on d.VAF01 = a.VAF01
        WHERE a.CBM01 = @cbmid and a.BDA01 = 'E' and a.VAF04 = 1 AND a.VAF10 = 8 and a.BCE01A = @lBCE01A
          AND a.VAF06 = @lVAF06 and Isnull(d.VBI29,0) = 0  
          and a.BCK01B = @lBCK01 and a.VAF35 = @lVAF35 And @BBC01 = ISNULL(b.BBX13,'')   
          and ((@VAF01>0 and (a.VAF01=@VAF01 or a.VAF01A=@VAF01))or @VAF01=0)          
      end 
      if @bda01='1' or @bda01='3'
      begin
        UPDATE a
        SET a.VAF51 = @lVBQ01
        FROM VAF1 a
        join BBX1 b on a.BBX01 = b.BBX01
        join VBI1 c on c.VAF01 = a.VAF01
        WHERE a.CBM01 = @cbmid and a.BDA01 in ('1','2','3') AND a.VAF10 = 8 and a.BCE01A = @lBCE01A
          AND a.VAF06 = @lVAF06 and Isnull(c.VBI29,0) = 0  and a.BCK01B = @lBCK01
        UPDATE a1
        SET a1.VAF51 = @lVBQ01
        FROM VAF1 a
        join VAF1 a1 on a1.VAF01=a.VAF01A
        join BBX1 b on a.BBX01 = b.BBX01
        join VBI1 c on c.VAF01 = a.VAF01
        WHERE a.CBM01 = @cbmid and a.BDA01 ='3' AND a.VAF10 = 8 and a.BCE01A = @lBCE01A
          AND a.VAF06 = @lVAF06 and Isnull(c.VBI29,0) = 0  and a.BCK01B = @lBCK01
              
        UPDATE d
        SET d.VBI21 = @lVBI21,d.BCE03B = @lBCE03,d.VBI20 = @lDateG,d.VBI29 = @lVBI29
        FROM VAF1 a
        join BBX1 b on a.BBX01 = b.BBX01
        join VBI1 d on d.VAF01 = a.VAF01
        WHERE a.CBM01 = @cbmid and a.BDA01 in ('1','2','3') AND a.VAF10 = 8 and a.BCE01A = @lBCE01A
          AND a.VAF06 = @lVAF06 and Isnull(d.VBI29,0) = 0  and a.BCK01B = @lBCK01  
          
        UPDATE d
        SET d.VBI21 = @lVBI21,d.BCE03B = @lBCE03,d.VBI20 = @lDateG,d.VBI29 = @lVBI29
        FROM VAF1 a
        join VAF1 a1 on a1.VAF01=a.VAF01A
        join BBX1 b on a.BBX01 = b.BBX01
        join VBI1 d on d.VAF01 = a1.VAF01
        WHERE a.CBM01 = @cbmid and a.BDA01 ='3' AND a.VAF10 = 8 and a.BCE01A = @lBCE01A
          AND a.VAF06 = @lVAF06 and Isnull(d.VBI29,0) = 0  and a.BCK01B = @lBCK01  
      end
      if @bda01 not in ('1','2','3','E','L') 	
      begin
        UPDATE a
        SET a.VAF51 = @lVBQ01
        FROM VAF1 a 
        WHERE a.VAF01 = @VAF01
          
        UPDATE d
        SET d.VBI21 = @lVBI21,d.BCE03B = @lBCE03,d.VBI20 = @lDateG,d.VBI29 = @lVBI29,d.VBI36 = ''
        FROM VBI2 d        
        WHERE d.VAF01 = @VAF01   
  	  end
  	 Set @aMin = @aMin + 1
  end	 
COMMIT TRAN

if object_id('tempdb.dbo.#TmpBEL') is not null drop table #TmpBEL