SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[HORate_VAC4_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [HORate_VAC4_Update] AS' 
END
GO


--===================================================================================================
--Author     <...8065>
--Alter Date <...2018.12.24>
--Description<...挂号预约保存过程>
--2013.08.12 <...接受外部传入就诊号>
--2016.01.19 <...增加传入预约截至时间VAC73>
--2016.12.01 <...根据参数决定门诊预约时不生成门诊号>
--2018.11.19 <...增加参数支持生成队列>
--===================================================================================================
ALTER PROC [HORate_VAC4_Update] @XML nText,
                                      @Id int out,
									  @VAA01 int out ,
									  @VAC02 varchar(20) Out ,
									  @IsQueue tinyint = 1									  
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 #tmpVAC4
  FROM OpenXml(@iDOM, '/Root/VAC1/Ie', 8) 	 
   WITH 
   (    VAC01 int
      , VCB04 varchar(20)
	  , 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)
	  , VAC73 datetime 
	  , VCJ41 varchar(64)
	  , VCJ42 varchar(20)
   )  

  Exec sp_xml_removedocument @iDOM

  IF NOT EXISTS(SELECT * FROM #tmpVAC4)
  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,
		  @VCJ41 varchar(64) ,
		  @CurDate datetime  ,
		  @BCK01A int ,
		  @VCJ42 varchar(20)
  
  SET @CurDate = GETDATE()        
  SELECT @VAC01 = VAC01, @VAC02 = VAC02 , @BCB01A = BCB01A ,@BCB03A = BCB03A,  @VAC36 = CONVERT(varchar(10), @CurDate, 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 , @VCJ41 = VCJ41 , @BCK01A = BCK01A , @VCJ42 = ISNULL(VCJ42 , '')
  from #tmpVAC4
  
  SELECT top 1 @VAC71 = VAC71 from VAC1 where VAA01 = @VAA01  
  order by VAC01 desc 
  
  SET @VAC71 = ISNULL(@VAC71 , 0) + 1 
  
  SELECT @VAC68 = [dbo].[GetAgeDetail](@VAA10 , @AAU01 )
  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 <= @CurDate
    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
  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
  
  BEGIN TRAN
    /*======================================保存病人信息=======================================*/
    EXEC @ErrId = HOPatient_VAA1_Update @XML,@VAA01 out , 0 , 0 , 0 , @EmpId , 2
    IF @ErrId > 0
    BEGIN
    	IF @@TRANCOUNT > 0 ROLLBACK TRAN
        RAISERROR('保存病人信息时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
        Return 1
    END
    UPDATE #tmpVAC4 SET VAA01 = @VAA01 
    Set @Id = ISNULL(@VAC01,0) 
    --编辑挂号记录信息vaa1
    IF ISNULL(@VAC01,0) <= 0 
    BEGIN    
	     Declare @sStr varChar(30),
	             @curID Int ,
	             @VAC06 int 	           
	        
	         EXEC @Errid = Core_NewIDEx 'VAC1','VAC01',@Id out --如果为新增，获取新的ID
	         
	         If @ErrId > 0 
             BEGIN
                IF @@TRANCOUNT > 0 ROLLBACK TRAN
                Return 1
             END

             Set @curID = @ID
             If ISNULL(@VAC02 , '') = ''
             BEGIN
               SET @sStr = '0000000000'
               SET @sStr = @sStr + Cast(@curID AS VARCHAR)
               SET @sStr = 'R' +  right(@sStr, 10)
               SET @VAC02 = @sStr 	
             END
             --如果同一个病人，挂同一个科室，则视该病人为复诊
             If ISNULL(@IsFzByOper,0)=0--复诊由程序控制
             begin
                  if exists(SELECT * FROM #tmpVAC4 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 #tmpVAC4 
             END
             
             if @VAC45=0 --预约         
             	set @BCE03E = @BCE03B              
                                     
             INSERT INTO VAC4(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 , VAC73) 
                    
             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, @CurDate , @CurDate, 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 , ISNULL(VAC73 , VAC44)
             from #tmpVAC4  
               
             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 
             	where SCF01 = @SCF01 and SCF11 <= 2                            	           
             END   
             --插入病人挂号附表
             IF NOT EXISTS(SELECT * from VCJ1 where VAA07 = @Id)
             BEGIN
               INSERT INTO VCJ1(VAA07 , VCJ41 , VCJ42)  
			   SELECT @Id  , @VCJ41 , @VCJ42       
             END
    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  
   
   IF @VAC45 = 0
	 SET @VAH08 = @VAH08 + 1
   ELSE IF @VAC45 = 1
	 SET @VAH07 = @VAH07 + 1	
   ELSE IF @VAC45 >= 2
	 SET @VAH07 = @VAH07 - 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) 
	If @IsQueue = 1
    BEGIN     
       SET @VAD18 = 1
                
       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 
    	   SET @VAC44 = CONVERT(varchar(10), @VAC44, 120)
    	   Exec @ErrID = HORate_VAD1_Update @VAC44, @Id, @VAD05, @BCE03B , @BCB01A , @VAA05 ,@ABW01 , @VAA10 , @AAU01 , @BCK03A , @BCE03 , @VAD18 , @BCK01A  , @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*/
    /*====================================================写入CRM信息===================================================================*/
    If ISNULL(@SCA01 , 0) > 0 
    BEGIN       
         Update SCA1 
           SET VAA01 = @VAA01 
         where SCA01 = @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  
    
           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..#tmpVAC4') IS NOT NULL DROP TABLE #tmpVAC4












GO