SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--=========================================================================================================
--Author     <...fyq>
--Alter Date <...2015-12-14>
--Description<...挂号过程>
/*
2011-08-08,by HHy,会员病人绑定病人费别
*/
--2012.02.01 挂号时写入CRM客户经理、咨询顾问
--2012.03.13 挂号时增加其他证件维护
--2012.04.03 挂号时默认加入病人挂号附表
--2012.04.17 预约病人挂号时优先级与普通挂号病人区分
--2012.05.07 CRM跟进人增加场合、就诊ID、状态、金额、备注等信息
--2012.05.30 挂号记录增加就诊次数字段
--2012.08.02 挂号情况与病人队列表过程从挂号过程中分离出去
--2012.08.06 挂号预约表增加字段删除标记，当该标记为1时，系统不做检索
--2012.09.12 挂号附表VCJ09默认插入挂号时间
--2012.11.08 CRM客户经理录入调用另外一个过程(客户资源数限制)
--2012.12.25 挂号时同步CRM客户资料
--2012.12.31 更新CRM预约状态放在结帐过程里
--2013.01.09 增加挂号有效期维护
--2013.01.25 明细年龄调用医生站获取函数过程
--2013.03.27 挂号就诊号改为由挂号就诊号独立序列号表管理
--2013.06.20 第三方体检调用，VAC45直接写为1
--2013.07.25 挂号时允许编辑是否查看病人健康档案
--2013.09.12 如果存在CRM预约挂号记录，修改删除标记
--2015.07.20 前台增加传入Pos主结算记录
--2015.08.27 增加健康卡号录入
--2015.12.04 预约确认时写入VCJ1记录
--2015.12.14
--=========================================================================================================
ALTER  PROC [HORate_VAC1_Update] @XML nText,@Id int out,@VAA01 int out ,@VAC02 varchar(20) Out, @State int = 0
AS
  DECLARE @iDOM int, @iret int
  --解析XML文档	
  Exec @iret = sp_xml_preparedocument @iDOM OUTPUT, @XML
  if @iret > 0
  BEGIN
    exec sp_xml_removedocument @iDOM  	
    RAISERROR('解析XML文档时发生错误，错误号b. %d.', 16, 1, @iret) with nowait 
    return @iret
  END
  /*Root/VAC挂号记录信息基本信息*/
  SELECT * INTO #tmpVAC
  FROM OpenXml(@iDOM, '/Root/VAC1/Ie', 8) 	 
   WITH 
   (    VAC01 int
      , VCB04 varchar(64)
	  , VAC02 varchar(20)
	  , VAA01 int
	  , VAA10 int
	  , AAU01 varchar(1)
	  , VAC06 tinyint
	  , BAO02 varchar(10)
	  , BCB03A varchar(20)
	  , VAC09 int
	  , VAC10 tinyint
	  , VAA16 varchar(20)
	  , BAS02A varchar(20)
	  , BDP02 varchar(50)
	  , ABC02 varchar(20)
	  , ABT02 varchar(20)
	  , ACK01 varchar(2)
	  , VAC16 varchar(64)
	  , VAC17 varchar(20)
	  , VAC18 varchar(20)
	  , VAC19 varchar(64)
	  , VAC20 varchar(20)
	  , VAC21 varchar(32)
	  , AAZ02 varchar(32)
	  , VAC23 varchar(64)
	  , VAC24 varchar(20)
	  , BAQ01 int
	  , VAC26 varchar(10)
	  , VAC27 varchar(64)
	  , VAC28 varchar(20)
	  , VAC29 varchar(64)
	  , VAC30 varchar(20)
	  , VAC31 int
	  , BCK01A int
	  , BCE03A varchar(20)
	  , VAC34 tinyint
	  , VAC35 datetime
	  , VAC37 datetime
	  , BCE02 varchar(20)
	  , BCE03B varchar(20)
	  , VAC40 tinyint
	  , VAC41 datetime
	  , VAC42 datetime
	  , VAC43 tinyint
	  , VAC44 datetime
	  , VAC45 tinyint
	  , VAC46 varchar(255)
	  , VAC56 varchar(56)
	  , VAC57 varchar(32)
	  , BAK05 varchar(128)
	  , IAA01 int
	  , UAA01 int
	  , BCE02A varchar(20)
	  , IsFzByOper int
	  , BCB01A int
	  , BCE01A int
	  , BGG01 int
	  , SCF01 int 
	  , SCA01 int
	  , EmpId int
	  , EmpNo varchar(20)
	  , EmpName varchar(20)
	  , BAH03 varchar(128)
	  , CManageId int
	  , CManage varchar(20)
	  , BCE01D int 
	  , BCE03D varchar(20)
	  , BCE03E varchar(20)
	  , VCJ15 tinyint 
	  , FAF01 int 
	  , VAA82 varchar(82)
   )  
  exec sp_xml_removedocument @iDOM
  IF NOT EXISTS(SELECT * FROM #tmpVAC)
  BEGIN    
    RAISERROR('提交错误，挂号信息为空.', 16, 1) with nowait 
    RETURN 2
  END
  --判断编号是否重复  
  DECLARE @TableID INT,
          @IsFzByOper int ,
          @VAC01 int , 
          @VAC43 INT , 
          @VAC45 INT ,
          @VAA10 INT ,
          @BCB01A INT ,
          @AAU01 varchar(1) ,
          @ABW01 varchar(1) ,
          @BDP02 varchar(50) ,
          @ABC02 varchar(20) ,
          @BCB03A varchar(20) ,
          @VAC36 varchar(10) , 
          @BCE03B varchar(20) , 
          @BCE03E varchar(20) ,
          @VAD05 INT ,
          @BCK01 INT , 
          @BBY01 INT , 
          @BCE01 INT , 
          @BCE03 varchar(20) , 
          @VAH07 INT , 
          @VAH08 INT , 
          @VAH01 INT , 
          @VAD01 INT ,
          @VAC44 datetime ,
          @ErrId int ,         
          @BGG01 int ,
          @SCA01 int ,
          @SCF01 int ,
          @BCZ01 int ,
          @SCH01 int ,
          @EmpId int ,
          @BCK03A varchar(64) ,
          @VAA05 varchar(64) ,          
          @EmpNo varchar(20) ,
          @EmpName varchar(20) ,
          @VAC68 varchar(20) ,
          @SCH12 varchar(1024) ,
          @OBEP06B numeric(18 ,2),
          @BCE01D int ,
          @BCE03D varchar(20) ,
          @CManageId int ,
          @CManage varchar(20) ,
          @SCI01 int ,
          @VAD18 smallint ,
          @BCE01A int ,
          @VAC71 int ,
          @VAA12 datetime ,
          @VCJ15 tinyint ,
          @FAF01 int ,
	      @VAC06 int 
          
  SELECT @VAC01 = VAC01, @VAC02 = VAC02 , @BCB01A = BCB01A ,@BCB03A = BCB03A,  @VAC36 = Convert(varchar(10), getdate(), 120),
     @BCK01 = BCK01A,  @BCE03 = BCE03A, @VAC43 = VAC43, @VAC45 = VAC45, @BCE03B=BCE03B, @VAD05 = Isnull(VAC34,0),
     @BGG01 = BGG01 , @EmpId = EmpId , @EmpNo = EmpNo , @EmpName = EmpName , @BDP02 = BDP02 , @ABC02 = ABC02 , @VAC44 = VAC44 , 
     @VAA10 = VAA10 , @AAU01 = AAU01 , @SCF01 = SCF01 , @SCA01 = SCA01 , @CManageId = CManageId , @CManage = CManage ,
     @BCE01D = BCE01D , @BCE03D = BCE03D , @BCE01A = BCE01A , @VCJ15 = VCJ15 , @FAF01 = FAF01,@VAC06 = VAC06 
  from #tmpVAC
  
  --如果医生ID存在，不存在编码，更新其编码
  If exists(select * from #tmpVAC where ISNULL(BCE02A , '') = '' and BCE01A > 0)
  BEGIN 
    Update a 
      SET a.BCE02A = b.BCE02 
    from #tmpVAC a 
      join BCE1 b on b.BCE01 = a.BCE01A 
  END
  
  SELECT top 1 @VAC71 = VAC71 from VAC1 where VAA01 = @VAA01  
  order by VAC01 desc 
  
  SET @VAC71 = ISNULL(@VAC71 , 0) + 1 
  
  SELECT @IsFzByOper = isnull(Value ,0) FROM SYS_Parameters WHERE ProgramID = 103005 AND ParamNo = 36
  --如果VAC01大于0，
  IF @VAC01 > 0 
  BEGIN
    If exists(select * from VAC1 where VAC01 = @VAC01 and VAA01 <> @VAA01 )
    BEGIN
       RAISERROR('提交错误，就诊ID与病人ID不一致.', 16, 1) with nowait 
       RETURN 2
    END
  END
  --如果是预约，判断预约时间不能小于当前时间
  If @VAC45 = 0
  BEGIN
    If @VAC44 <= GETDATE()
    BEGIN
       RAISERROR('提交错误，预约时间不能小于当前时间.', 16, 1) with nowait 
       RETURN 2
    END
  END
  --挂号类别不能为空
  IF isnull(@BCB01A , 0) = 0 
  BEGIN
     RAISERROR('提交错误，病人挂号类别不能为空.', 16, 1) with nowait 
     RETURN 2
  END
  --挂号科室不能为空
  If ISNULL(@BCK01 , 0 ) = 0 and exists(select * from sys_parameters 
      where programid = 103005 and paramno = 12 and value ='1')
  BEGIN
     RAISERROR('提交错误，病人挂号不能科室为空.', 16, 1) with nowait 
     RETURN 2
  END
  --通过病人类别来确定费别
  If exists(select * from SYS_Parameters where ProductID = 9999 and ProgramID = 9999 and ParamNo = 18  and Value = '1')
  BEGIN
    If not exists(select * from BGY1 where ABC02 = @ABC02 and BDP02 = @BDP02)
    BEGIN
       RAISERROR('提交错误，病人类别与费别不对应.', 16, 1) with nowait 
       RETURN 2
    END
  END 
  
  --会员病人绑定病人费别
  IF EXISTS(SELECT * FROM SYS_PARAMETERS WHERE PRODUCTID=100 AND PROGRAMID=116001 AND PRIVILEGE=0 AND PARAMNO='2' AND VALUE='1')
    AND EXISTS(SELECT * FROM VAA1 WHERE VAA01=@VAA01 AND VBU01>0 AND ABC02>'' AND ABC02<>@ABC02)
  BEGIN
    RAISERROR('已绑定会员卡类型，禁止修改病人费别.', 16, 1) with nowait 
    RETURN 2
  END
  
  --如果是预约挂号病人，如果是挂号状态，则提示错误
  If Exists(select * from VAC1 where VAC45 = 1 and VAC01 = @Id)
  BEGIN
     RAISERROR('提交错误，病人已经是挂号状态.', 16, 1) with nowait 
     RETURN 2 
  END
 --同一个病人当天同一个科室同一个医生只能挂号一次
 if exists(select a.* from VAC1 a with(nolock) join #tmpVAC b on b.VAA01 = a.VAA01 
     where a.VAC45 =1 and b.VAA01 > 0 and b.BCK01A = a.BCK01A and b.BCE03A=a.BCE03A and datediff(day,a.VAC36,@VAC36)=0)
  begin
    RAISERROR('同一个病人当天同一个科室同一个医生只能挂号一次，此病人已挂号，不能重复挂号。', 16, 1)  WITH NOWAIT
    return 3
  end
  
  BEGIN TRAN
    /*======================================保存病人信息=======================================*/
    EXEC @ErrId = HOPatient_VAA1_Update @XML,@VAA01 out , 0 , 0 , 0 , @EmpId 
    IF @ErrId > 0
    BEGIN
    	IF @@TRANCOUNT > 0 ROLLBACK TRAN
        RAISERROR('保存病人信息时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
        Return 1
    END
    
    SELECT @VAA12 = VAA12 from VAA1 where VAA01 = @VAA01 
    
    SELECT @VAC68 = [dbo].[GetAgeDetailFromAge](0 , 'Y' , @VAA12)
    
    UPDATE #tmpVAC 
      SET VAA01 = @VAA01 
      
    Set @Id = Isnull(@VAC01,0) 
    --编辑挂号记录信息vaa1
    IF Isnull(@VAC01,0) <= 0 
    BEGIN    
	     Declare @sStr varChar(30),
	             @curID Int 	           
	        
	         EXEC @Errid = Core_NewIDEx 'VAC1','VAC01',@Id out --如果为新增，获取新的ID
	         
	         If @ErrId > 0 
             BEGIN
                IF @@TRANCOUNT > 0 ROLLBACK TRAN
                Return 1
             END
             
             If ISNULL(@VAC02 , '') = '' 
             BEGIN
                Exec Core_NewId_VAC02 @curID out           
                SELECT @VAC02 = dbo.[GetNewSquenceNo](1 , @curID)
             END
             --如果同一个病人，挂同一个科室，则视该病人为复诊
             if isnull(@IsFzByOper,0)=0--复诊由程序控制
             begin
                  if exists(SELECT * FROM VAC1 a JOIN VAC1 b ON a.VAA01 = b.VAA01 AND a.BCK01A = b.BCK01A where a.VAA01 = @VAA01)	
                      set @VAC06 =1 
                  else 
             	      set @VAC06 =0
             end else  --复诊由操作员控制
             BEGIN
             	  select @VAC06 = VAC06 from #tmpVAC 
             END
                                                 
             INSERT INTO VAC1(VAC01, VAC02, VAA01, VAA10, AAU01, VAC06, BAO02, BCB03A, VAC09, VAC10, BAS02A, 
                    BDP02, ABC02, ABT02, ACK01, VAC16, VAC17, VAC18, VAC19, VAC20, VAC21, AAZ02, VAC23, VAC24, BAQ01, VAC26, 
                    VAC27, VAC28, VAC29, VAC30, VAC31, BCK01A, BCE03A, VAC34, VAC35, VAC36, VAC37, BCE02, BCE03B, VAC40, 
                    VAC41, VAC42, VAC43, VAC44, VAC45, VAC46, BAK05, IAA01, UAA01,
                    BCE02A , VAC56 ,VAC57 , BCB01A  , BCE01A , BCE01D , BCE03D , BCE03E , BGG01 , VAC68 , SCF01  , VAC71) 
                    
             SELECT @Id, @VAC02, VAA01, VAA10, AAU01, Isnull(@VAC06 , 0), BAO02, BCB03A, isnull(VAC09 , 0), Isnull(VAC10 , 0), BAS02A,
                    BDP02, ABC02, ABT02, ACK01, VAC16, VAC17, VAC18, VAC19, VAC20, VAC21, AAZ02, VAC23, VAC24, BAQ01, VAC26,
                    VAC27, VAC28, VAC29, VAC30, VAC31, Isnull(BCK01A , -1), BCE03A, Isnull(VAC34,0), VAC35, getdate() , getdate(), BCE02, BCE03B, VAC40,
                    VAC41, VAC42, VAC43, NULL, VAC45, VAC46, BAK05, IAA01, UAA01 ,
                    BCE02A ,VAC56 ,VAC57 , BCB01A , BCE01A , isnull(BCE01A, 0) , isnull(BCE03A ,'') , NULL , BGG01 , @VAC68 , ISNULL(SCF01 , 0) , @VAC71 
             from #tmpVAC  
               
             IF @@ERROR > 0 --如果发生错误则回滚数据     	            
             BEGIN
   	             IF @@TRANCOUNT > 0 ROLLBACK TRAN
                 RAISERROR('写病人挂号记录时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
                 Return 1
             END 
             --更新客户预约状态、咨询顾问
             If ISNULL(@SCF01 , 0) > 0 
             BEGIN
             	Update SCF1 
             	  --set SCF11 = 4 ,
             	  SET 
             	      VAA07 = @Id 
             	where SCF01 = @SCF01 and SCF11 <= 2 
				--如果存在CRM预约挂号记录,更新预约表中删除状态
				  If EXISTS(select * from VAC4 where SCF01 = @SCF01)
				  BEGIN
      				 Update VAC4 
      				   SET DFLAG = 1
      				 WHERE SCF01 = @SCF01                           	           
				  END
		  	 END               
    END ELSE BEGIN
      --如果是预约挂号转挂号,更新预约表中删除状态，插入到挂号表中
      If EXISTS(select * from VAC4 where VAC01 = @Id)
      BEGIN
      	 Update VAC4 
      	   SET DFLAG = 1
      	 WHERE VAC01 = @Id 
      	 
      	 INSERT INTO VAC1(VAC01, VAC02, VAA01, VAA10, AAU01, VAC06, BAO02, BCB03A, VAC09, VAC10, BAS02A, 
                    BDP02, ABC02, ABT02, ACK01, VAC16, VAC17, VAC18, VAC19, VAC20, VAC21, AAZ02, VAC23, VAC24, BAQ01, VAC26, 
                    VAC27, VAC28, VAC29, VAC30, VAC31, BCK01A, BCE03A, VAC34, VAC35, VAC36, VAC37, BCE02, BCE03B, VAC40, 
                    VAC41, VAC42, VAC43, VAC44, VAC45, VAC46, BAK05, IAA01, UAA01,
                    BCE02A , VAC56 ,VAC57 , BCB01A  , BCE01A , BCE01D , BCE03D , BCE03E , BGG01 , VAC68 , SCF01  , VAC71) 
                    
         SELECT @Id, @VAC02, VAA01, VAA10, AAU01, Isnull(@VAC06 , 0), BAO02, BCB03A, isnull(VAC09 , 0), Isnull(VAC10 , 0), BAS02A,
                    BDP02, ABC02, ABT02, ACK01, VAC16, VAC17, VAC18, VAC19, VAC20, VAC21, AAZ02, VAC23, VAC24, BAQ01, VAC26,
                    VAC27, VAC28, VAC29, VAC30, VAC31, Isnull(BCK01A , -1), BCE03A, Isnull(VAC34,0), VAC35, getdate() , getdate(), BCE02, BCE03B, VAC40,
                    VAC41, VAC42, VAC43, VAC44, VAC45, VAC46, BAK05, IAA01, UAA01 ,
                    BCE02A ,VAC56 ,VAC57 , BCB01A , BCE01A , isnull(BCE01A, 0) , isnull(BCE03A ,'') , BCE03E , BGG01 , @VAC68 , ISNULL(SCF01 , 0) , @VAC71 
         from #tmpVAC  
               
         IF @@ERROR > 0 --如果发生错误则回滚数据     	            
         BEGIN
   	         IF @@TRANCOUNT > 0 ROLLBACK TRAN
             RAISERROR('写病人挂号记录时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
             Return 1
         END
         
         Update a 
           SEt a.VAC43 = b.VAC43 ,
               a.VAC44 = b.VAC44 ,
               a.BCE03E = b.BCE03E 
         from VAC1 a 
           join VAC4 b on a.VAC01 = b.VAC01 
         where a.VAC01 = @Id 
      END ELSE BEGIN 
      	 UPDATE a SET
	      a.VAA10 = b.VAA10
	      ,a.AAU01 = b.AAU01
	      ,a.BAO02 = b.BAO02
	      ,a.VAC09 = Isnull(b.VAC09 , 0)
	      ,a.VAC10 = Isnull(b.VAC10 , 0)
	      ,a.BAS02A = b.BAS02A
	      ,a.BDP02 = b.BDP02
	      ,a.ABC02 = b.ABC02
	      ,a.ABT02 = b.ABT02
	      ,a.ACK01 = b.ACK01
	      ,a.VAC16 = b.VAC16
	      ,a.VAC17 = b.VAC17
	      ,a.VAC18 = b.VAC18
	      ,a.VAC19 = b.VAC19
	      ,a.VAC20 = b.VAC20
	      ,a.VAC21 = b.VAC21
	      ,a.AAZ02 = b.AAZ02
	      ,a.VAC23 = b.VAC23
	      ,a.VAC24 = b.VAC24
	      ,a.BAQ01 = b.BAQ01
	      ,a.VAC26 = b.VAC26
	      ,a.VAC27 = b.VAC27
	      ,a.VAC28 = b.VAC28
	      ,a.VAC29 = b.VAC29
	      ,a.VAC30 = b.VAC30
	      ,a.VAC31 = b.VAC31
	      ,a.BCK01A = Isnull(b.BCK01A , -1)
	      ,a.VAC40 = b.VAC40
	      ,a.VAC41 = b.VAC41
	      ,a.VAC42 = b.VAC42
	      ,a.VAC43 = b.VAC43
	      ,a.VAC45 = b.VAC45
	      ,a.VAC46 = b.VAC46
	      ,a.BAK05 = b.BAK05
	      ,a.IAA01 = b.IAA01
	      ,a.UAA01 = b.UAA01
	      ,a.SCF01 = b.SCF01
	      ,a.VAC56 = b.VAC56 
	      ,a.VAC57 = b.VAC57 
	      ,a.BCE01A = b.BCE01A 
	      ,a.BCE02A = b.BCE02A
	      ,a.BCE03A = b.BCE03A
	      ,a.BCB01A = b.BCB01A
	      ,a.BCB03A = b.BCB03A 
	  FROM VAC1 a,#tmpVAC b 
      WHERE a.VAC01 = @Id 
      
      IF @@ERROR > 0 --如果发生错误则回滚数据     	            
      BEGIN
   	     IF @@TRANCOUNT > 0 ROLLBACK TRAN
         RAISERROR('写病人挂号记录时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
         Return 1
      END  
    END 
   END   
      --如果预约挂号中未改变挂号排班ID,取原来挂号排班ID
      If ISNULL(@BGG01 , 0) = 0
      BEGIN
        select @BGG01 = BGG01 from VAC1 where VAC01 = @Id 
        set @BGG01 = ISNULL(@BGG01 , 0) 
      END else 
      BEGIN
      	update VAC1 
      	  set BGG01 = @BGG01 
      	where VAC01 = @Id
      END
      select @VAC02 = VAC02  from VAC1 Where VAC01= @Id             
    
      --如果是挂号，状态默认改为3 ,改变挂号有效期
      If @VAC45 = 1 and exists(select * from #tmpVAC where ISNULL(VAC09 ,0) <> -3)
      BEGIN
        Update VAC1 
          set VAC45 = 3 ,
              VAC72 = ISNULL(dbo.GetClinicExpiryDate(@BCK01 , @BDP02 , @ABC02) , '9999-12-31' )
        where VAC01 = @Id 
      END
   /*==================================================== 写入病人病人挂号情况====================================================*/
   If @BCB03A <> ''
      SELECT @BBY01 = BBY01 FROM BCB1 Where BCB02 = @BCB01A
     
   IF @BCE03 <> ''
      SELECT @BCE01 = BCE01 FROM BCE1 Where BCE03 = @BCE03 	
   
   SELECT @VAH01 = VAH01,@VAH07 = ISNULL(VAH07, 0), @VAH08 = ISNULL(VAH08, 0) 
   FROM VAH1 
   WHERE VAH02 = @VAC36 and BCB01 = @BCB01A and BCE01 = @BCE01  
   
   IF @VAC45 = 0        --预约
	 SET @VAH08 = ISNULL(@VAH08 , 0) + 1
   ELSE IF @VAC45 = 1   --挂号
	 SET @VAH07 = ISNULL(@VAH07 , 0) + 1	
   ELSE IF @VAC45 = 2   --退号
	 SET @VAH07 = ISNULL(@VAH07 , 0) - 1
	 
   Exec @ErrId = HORate_VAH1_Update @VAH01 , @VAC36 , @BCK01 , @BBY01 , @BCE01 , @BCE03 , @VAH07 , @VAH08 , @BCB01A 
   
   IF @ErrId > 0 --如果发生错误则回滚数据     	            
   BEGIN
   	    IF @@TRANCOUNT > 0 ROLLBACK TRAN
        RAISERROR('写病人挂号情况时发生错误，错误号为: %d.', 16, 1, @ErrId)  WITH NOWAIT
        Return 1
   END
    --====================================================如果排班中启用了队列，插入挂号队列====================================================
    IF exists(select * from BGG1 where BGG01 = @BGG01 and BGG04 = 1 and @VAC45 = 1) 
    BEGIN
       If ISNULL(@VAC44 , '') <> ''
         SET @VAD18 = 1
       else If ISNULL(@VAC44 , '') = '' 
         SET @VAD18 = 0 
               
       IF not EXISTS(Select VAD01 FROM VAD1 Where VAC01 = @ID)
       BEGIN
           SELECT @VAA05 = VAA05 , @ABW01 = ABW01 from VAA1 where VAA01 = @VAA01 
    	   SELECT @BCK03A = BCK03 from BCK1 where BCK01 = @BCK01 
    	   
    	   Exec @ErrID = HORate_VAD1_Update @VAC36, @Id, @VAD05, @BCE03B , @BCB01A , @VAA05 ,@ABW01 , @VAA10 , @AAU01 , @BCK03A , @BCE03 , @VAD18 , @BCK01 , @BCE01A 
    	   
    	   IF @ErrID > 0 --如果发生错误则回滚数据     	            
           BEGIN
   	           IF @@TRANCOUNT > 0 ROLLBACK TRAN
               RAISERROR('写病人挂号队列时发生错误，错误号为: %d.', 16, 1, @ErrID)  WITH NOWAIT
               Return 1
           END          	      
       END
    END
    --==============================================如果挂号班次指定了自动分诊，则写入病人分诊记录=========================================
    If Exists(select * from BGG1 where BGG01 = @BGG01 and BGG15 = 2 and @VAC45 = 1) 
    BEGIN      
       SET @BCZ01 = 0 
       Exec @ErrId = HORate_BCZ1_Update @BCZ01 out , @BCB01A , @BCK01 , @Id , @BGG01 , @BCE01A 
       
       If @ErrId = 3  --如果发生错误，则回滚数据
       BEGIN
       	  IF @@TRANCOUNT > 0 ROLLBACK TRAN
          RAISERROR('病人分诊时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
          Return 1
       END
    END
    /*==============================================写入Pos交易记录中病人ID及就诊ID====================================================*/
    If ISNULL(@FAF01 , 0) > 0
    BEGIN
      Update FAF1 
        SET VAA01 = @VAA01 ,
            VAA07 = @Id 
      where (FAF01A = @FAF01 or FAF01 = @FAF01 )
    END
    /*====================================================写入挂号附表==================================================================*/
     --插入病人挂号附表
     IF NOT EXISTS(SELECT * from VCJ1 where VAA07 = @Id)
     BEGIN
        INSERT INTO VCJ1(VAA07 , VCJ09 , VCJ15)  
        SELECT @Id , @VAC36 , @VCJ15 
     END
    /*====================================================写入CRM信息===================================================================*/
    If ISNULL(@SCA01 , 0) > 0 
    BEGIN       
         Update SCA1 
           SET VAA01 = @VAA01 ,SCA06 = @VAA05,ABBRP=dbo.GETABBRP(@VAA05),ABW01=@ABW01,SCA12=@VAA10,AAU01=@AAU01
         where SCA01 = @SCA01 
         
         Exec HOCRM_VAA1_Synchronize @SCA01 
        /*=================================================写入CRM跟进人==============================================================*/
        If ISNULL(@CManageId , 0) > 0
        BEGIN
           SET @SCI01 = 0 
           --Exec @ErrId = HOCRM_SCI1_Update @SCI01 out , @SCA01 , 1 , @SCF01 , 0 , @CManageId , @CManage , '' , 0 , 1 , 0,  0  
           Exec @ErrId = HOCRM_SCA1_Status @SCA01 , @CManageId , @CManage , 0
           /*IF @ErrId > 0 
           BEGIN
             IF @@TRANCOUNT > 0 ROLLBACK TRAN
             RAISERROR('写入CRM跟进人时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
             Return 1
           END
           */
        END
        --如果当前的CRM预约ID为空，取表中SCF01,更新客户预约状态
        If ISNULL(@SCF01 , 0) <= 0
          SELECT @SCF01 = SCF01 from SCF1 where VAA07 = @Id and SCF11 = 2
        
        If ISNULL(@SCF01 , 0) > 0 
        BEGIN
           /*Update SCF1 
             set SCF11 = 4 ,
                 VAA07 = @Id 
           where SCF01 = @SCF01 and SCF11 <= 2*/
         
           If ISNULL(@BCE01D , 0) > 0
           BEGIN
              Update SCF1 
                SET BCE01D = @BCE01D ,
                  BCE03D = @BCE03D                   
              where SCF01 = @SCF01  
           
              SET @SCI01 = 0          
              Exec @ErrId = HOCRM_SCI1_Update @SCI01 out , @SCA01 , 2 , @SCF01 , 0 , @BCE01D , @BCE03D , '' , 0 , 1 , 0,  @Id , 4 , ''  
    
              /*IF @ErrId > 0 
              BEGIN
                IF @@TRANCOUNT > 0 ROLLBACK TRAN
                RAISERROR('写入CRM跟进人时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
                Return 1
              END
              */                
           END           
        END 
        /*=========================================================客户日志====================================================*/   
        SET @SCH12 = '挂号时间：' + convert(varchar(30) , @VAC36 , 111) + ' 挂号科室：' + @BCK03A + ' 挂号医生：' + @BCE03  
        Exec @ErrId = HOCRM_SCH1_Update @SCH01 out , @SCA01 , 0 , 'VAC1' , 'VAC01' , 101 , '挂号' , @SCH12 , 0 , 0 , @EmpId ,@EmpName             
    END 
   COMMIT TRAN  
  /*=======================================================================================================================================*/
  IF Object_id('tempdb..#tmpVAC') IS NOT NULL DROP TABLE #tmpVAC