SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[WeChat_RegInfo_Update] @VAA01 int ,      --病人ID
                                  @SCA01 int ,             --客户ID
                                  @VAC01 int out ,         --就诊ID
                                  @BCK01A int ,            --科室ID
                                  @BCE01D int ,            --医生ID                                                                
                                  @BCB01A int,             --挂号类别                             
                                  @VAC36 datetime ,        --挂号日期
                                  @Memo varchar(256) ,     --备注信息
                                  @VAC02 varchar(20) out , --挂号
                                  @ErrMsg varchar(128) out,  --返回错误信息
                                  @EmpId int = 0 ,            --操作员ID 
								  @VAC06 tinyint = 0 ,      --是否复诊
								  @BDP02 varchar(50)='' --病人类别
As
----------------------------------------------组织待生成费用明细-------------------------------------------------
Declare @i int ,
        @Count int ,
        @VAJ01 int ,
        @VAI01 int ,
        @BBY01 int ,
        @curID int ,
        @BBY34 tinyint ,
        @CDate datetime ,
        @CurDate datetime ,        
        @VAI04 varchar(20) ,
        @BCE02D varchar(20) ,
        @VAA05 varchar(64) ,            
        @BCE03D varchar(20) ,
        @VAA10 int ,       
        @VAK08 numeric(18,4) ,
        @SumVAJ38 numeric(18,4) ,
        @VAA03 varchar(20) ,
        @MzPre varchar(20), 
        @MzLen int ,
        @sStr  VarChar(30) ,
        @ID03 int ,
		--@BDP02 varchar(50),
		@ABC02 varchar(20),
		@EmpNo varchar(20),
		@EmpName varchar(20),
		@VAA12 datetime ,
		@VAC68 varchar(20),
		@AAU01 varchar(1),
		@ABW01 varchar(1),
		@VAA15 varchar(20),
		@BCB03A varchar(20),
		@BAO02 varchar(10),
		@VAC71 int
       
                
Create table #tmpWeChatVAJ1
(
  ID int IDENTITY(1 , 1) ,
  VAJ01 int ,
  BBY01 int ,   
  BCF01 tinyint ,
  BDN01 varchar(2) ,
  VAJ25 numeric(18,4) ,
  VAJ32 numeric(18,6) ,
  VAJ33 numeric(18,6) ,
  VAJ35 varchar(20) ,
  VAJ36 numeric(18,4)
)

--SET @Amount = 0
SET @CurDate = GETDATE() 
If @EmpId > 0
  SELECT @EmpNo = BCE02 , @EmpName = BCE03 from BCE1 where BCE01 = @EmpId 
  
SELECT @BCE02D = BCE02,@BCE03D=BCE03 from BCE1 where BCE01 = @BCE01D 
SELECT  @BBY01 = BBY01,@BCB03A=BCB03,@BAO02=BAO02 from BCB1 where BCB01 = @BCB01A        
--SELECT @BBY34 = BBY34 FROM BBY1 WHERE BBY01 = @BBY01
SET @CDate = CONVERT(datetime, CONVERT(varchar(10), getdate(), 120))  
if @VAA01>0 and isnull(@BDP02,'')=''
	select @BDP02=BDP02,@ABC02=ABC02 from VAA1 with(nolock) where VAA01=@VAA01
if isnull(@BDP02,'')='' 
	select @BDP02=BDP02 from BGY1 where ISDEF=1
if isnull(@ABC02,'')=''
	select @ABC02=ABC02 from BGY1 where BDP02=@BDP02
if isnull(@BDP02,'')=''
	select @BDP02=BDP02 from BDP1 where ISDEF=1
if isnull(@ABC02,'')=''
	select @ABC02=ABC02 from ABC1 where ISDEF=1
if isnull(@BDP02,'')=''
	set @BDP02=(select top 1 BDP02 from BDP1)
if isnull(@ABC02,'')=''
	set @ABC02=(select top 1 ABC02 from ABC1)

--如果同一个病人，挂同一个科室，则视该病人为复诊
if exists(SELECT * FROM VAC1 a with(nolock) where a.VAA01 = @VAA01 and a.BCK01A=@BCK01A and VAC45=1)	
	set @VAC06 =1 
else 
	set @VAC06 =0


INSERT INTO #tmpWeChatVAJ1(BBY01 , BCF01 ,BDN01 , VAJ25 , VAJ32 , VAJ33 , VAJ35 ,VAJ36)
SELECT BBY01, a.BCF01 , a.BDN01 ,1 , BBY25 ,dbo.GetBBY1Price(a.BBY01,@ABC02) VAJ33 , BBY08 , BBY25  
FROM BBY1 a 
  LEFT JOIN BCF1 b on a.BCF01 = b.BCF01 
WHERE BBY01 = @BBY01 AND BBY34 <> 2
UNION 
SELECT BBY01, a.BCF01, a.BDN01 , 1 , BBY25 ,dbo.GetBBY1Price(a.BBY01,@ABC02) VAJ33,
 BBY08 , BBY25
FROM BBY1 a
  JOIN BCR1 b ON a.BBY01 = b.BBY01B
  LEFT JOIN BCF1 c on a.BCF01 = c.BCF01  
WHERE b.BBY01A = @BBY01

if not exists(select * from #tmpWeChatVAJ1)
BEGIN
   SET @ErrMsg = '挂号类别对应的费用明细为空。'   
   Return 1
END

SELECT @i = 0 , @Count = COUNT(1) from #tmpWeChatVAJ1 
Exec Core_NewIDEx 'VAJ1' , 'VAJ01' , @VAJ01 out , @Count 
While @i < @Count 
BEGIN
  update #tmpWeChatVAJ1 
    SET VAJ01 = @VAJ01 - @Count + @i + 1
  where ID = @i + 1
  SET @i = @i + 1
END

Exec Core_NewIDEx 'VAI1' , 'VAI01' , @VAI01 out 
SET @VAI04 = '000000000'
SET @VAI04 = @VAI04 + Cast(@VAI01 AS VARCHAR)
SET @VAI04 = 'I' +  SUBSTRING(@VAI04, Len(@VAI04) - 9, 10) 
SELECT @SumVAJ38 = SUM(VAJ33*VAJ25) from #tmpWeChatVAJ1 

BEGIN TRAN
-------------------------------------------------保存病人信息-----------------------------------------------------
If ISNULL(@VAA01 , 0) <= 0 
BEGIN
   EXEC Core_NewIDEx 'VAA1','VAA01',@VAA01  out
   select @MzPre = Value from SYS_Parameters where ProductID = 100 and ProgramID = 103005 and ParamNo = 39  
   select @Mzlen = Value from SYS_Parameters where ProductID = 100 and ProgramID = 103005 and ParamNo = 40
   SET @MzPre = ISNULL(@MzPre , 'MZ')
   SET @Mzlen = ISNULL(@Mzlen , 6)
   SET @sStr = '00000000000000000000'
   --如果前缀为YYYY,取当前的年份
   IF isnull(@MzPre,'') = 'YYYY'
   SET @MzPre = convert(varchar(4),getdate(),112)
   EXEC Core_NewId_VAA03  @ID03 out	        	             
 	      
   SET @sStr = @sStr + Cast(@Id03 AS VARCHAR)
   SET @sStr = RIGHT(@sStr , @Mzlen - len(@MzPre))
   SET @VAA03 = @MzPre  + @sStr 
   
   INSERT INTO VAA1(VAA01 , VAA03 , VAA05 , ABBRP , ABBRW ,ABW01 , VAA10 , VAA12 , VAA15 ,VAA35,BDP02,ABC02)
   SELECT @VAA01 , @VAA03 , SCA06 , ABBRP , ABBRW , ABW01 , SCA12 , SCA11 , SCA22 , SCA26 ,@BDP02,@ABC02 from SCA1 
   where SCA01 = @SCA01 
   -- 病人信息关联CRM客户信息
   UPDATE SCA1 SET VAA01 = @VAA01 WHERE SCA01 = @SCA01 
   UPDATE wxHOCRM_SCA1 SET VAA01 = @VAA01 WHERE SCA01 = @SCA01 
END	  
-- 2016-10-26 10:12:35 如果存在病人信息，判断其门诊号是否为空,为空则生成一个门诊号
ELSE
BEGIN
  IF EXISTS (SELECT 1 FROM VAA1 WHERE VAA01 = @VAA01 AND ISNULL(VAA03, '') = '') or dbo.GetSysParamValue(100,103005,66)=1
  BEGIN
  	select @MzPre = Value from SYS_Parameters where ProductID = 100 and ProgramID = 103005 and ParamNo = 39  
   select @Mzlen = Value from SYS_Parameters where ProductID = 100 and ProgramID = 103005 and ParamNo = 40
   SET @MzPre = ISNULL(@MzPre , 'MZ')
   SET @Mzlen = ISNULL(@Mzlen , 6)
   SET @sStr = '00000000000000000000'
   --如果前缀为YYYY,取当前的年份
   IF isnull(@MzPre,'') = 'YYYY'
   SET @MzPre = convert(varchar(4),getdate(),112)
   EXEC Core_NewId_VAA03  @ID03 out	        	             
 	      
   SET @sStr = @sStr + Cast(@Id03 AS VARCHAR)
   SET @sStr = RIGHT(@sStr , @Mzlen - len(@MzPre))
   SET @VAA03 = @MzPre  + @sStr    
   UPDATE VAA1 SET VAA03 = @VAA03 WHERE VAA01 = @VAA01
  END	
END

-------------------------------------------------保存就诊记录-----------------------------------------------------
If ISNULL(@VAA01 , 0) > 0
IF not exists(select * from VAA1 with(nolock) where VAA01=@VAA01)  --如果发生错误则回滚数据
BEGIN
   IF @@TRANCOUNT > 0 ROLLBACK TRAN
   SET @ErrMsg = '病人信息不存在，不允许挂号。'   
   Return 1
END

  SELECT @VAA03=VAA03,@VAA05 = VAA05 , @VAA10 = VAA10 , @VAA12 = VAA12,@VAA15=VAA15 , @AAU01 = AAU01,@ABW01=ABW01  
  ,@BDP02=isnull(@BDP02,BDP02),@ABC02=isnull(@ABC02,ABC02)
  from VAA1 where VAA01 = @VAA01 

  
Exec Core_NewIdEx 'VAC1' , 'VAC01' , @VAC01 out 
Exec Core_NewId_VAC02 @curID out           
SELECT @VAC02 = dbo.[GetNewSquenceNo](1 , @curID)

SELECT @VAC68 = [dbo].[GetAgeDetailFromAge](0 , 'Y' , @VAA12)

declare @VAC72 datetime
set @VAC72=ISNULL(dbo.GetClinicExpiryDate2(@BCK01A , @BDP02 , @ABC02 , @VAC36) , '9999-12-31' )

  SELECT top 1 @VAC71 = VAC71 from VAC1 with(nolock) where VAA01 = @VAA01    
  order by VAC01 desc   
    
  SET @VAC71 = ISNULL(@VAC71 , 0) + 1  

INSERT INTO VAC1(VAA01 , VAC01 , VAC02 ,VAA10 ,AAU01 , BCK01A ,BCE02A ,BCE03A ,BCE01A , BCE01D , BCE03D , BCB01A ,BAO02,BCB03A, VAC09,VAC34,VAC36 , BCE03B , VAC45 , VAC46,VAC51,BDP02,ABC02 , BCE02,VAC06, VAC68,
	VAC43,VAC44,VAC71,VAC72,VAC74,VAC75,VAC76,VAC77,VAC78)                               
SELECT @VAA01 , @VAC01 , @VAC02 , @VAA10 , @AAU01 ,@BCK01A , @BCE02D , @BCE03D ,@BCE01D , @BCE01D , @BCE03D , @BCB01A ,@BAO02,@BCB03A,0,0, @VAC36 , ISNULL(@EmpName ,@VAA05) , 3 , @Memo,0,@BDP02,@ABC02 , @EmpNo,@VAC06 , @VAC68,
	1,@VAC36,@VAC71,@VAC72,@VAA03,@VAA05,@ABW01,@VAA15,@VAA12
IF @@ERROR > 0 --如果发生错误则回滚数据
BEGIN
   IF @@TRANCOUNT > 0 ROLLBACK TRAN
   SET @ErrMsg = '写入就诊记录时发生错误。'   
   Return 1
END
--插入病人挂号附表
     IF NOT EXISTS(SELECT * from VCJ1 where VAA07 = @VAC01)
     BEGIN
        INSERT INTO VCJ1(VAA07 , VCJ09)  
        SELECT @VAC01 , @VAC36  
     END

if exists(select * from BGG1 where BCB01=@BCB01A and BGG04=1)
begin
--插入队列
DECLARE @ROWNR int ,
        @VAD01 int , 
        @VAD21 int , 
        @VAD22 int ,
        @VAD17 int ,
		@VAD03 datetime
set @VAD03=convert(varchar(10),@VAC36,121)
--全院次序
SELECT @ROWNR = ISNULL(max(ROWNR) ,0) 
from VAD1 with(nolock)
where VAD03 = @VAD03    
--科室次序
SELECT @VAD21 = ISNULL(max(VAD21) ,0)
from VAD1 with(nolock)
where VAD03 = @VAD03 and BCK01A = @BCK01A  
--医生次序
SELECT @VAD22 = ISNULL(max(VAD22) ,0)
from VAD1 with(nolock)
where VAD03 = @VAD03 and BCE01B = @BCE01D   
SET @VAD17 = ISNULL(@VAD22 , 0) + 1
EXEC Core_NewID 'VAD1','VAD01', @VAD01 out --如果为新增，获取新的ID 
INSERT INTO VAD1(VAD01, ROWNR, VAD03, VAC01, VAD05, BCE03 , BCB01 , VAD08 , ABW01 , VAD10 , 
    	 AAU01 , BCK03A , BCE03B , VAD17 , VAD18 , VAD21 , VAD22 , BCK01A , BCE01B )     	     
VALUES(@VAD01, ISNULL(@ROWNR,0) + 1, @VAD03, @VAC01, 0, @EmpName , @BCB01A , @VAA05 ,@ABW01 ,
   @VAA10 , @AAU01 , '' , '' , @VAD17 ,1 , ISNULL(@VAD21 ,0) + 1 , ISNULL(@VAD22 ,0) + 1 , @BCK01A , @BCE01D ) 
end
-------------------------------------------------保存费用明细------------------------------------------------------
 INSERT INTO VAI1(VAI01, VAA01, VAA07, VAI04, VAI05, BCK01A, BCK01B, BCE02A, BCE03A, BCE02B, BCE03B, 
   VAI12, VAI13, VAI14, VAI01A, VAI16, VAI17, VAI18 , CBM01 , ACF01 , VAI22 , VAI23 , VAK01 ,VAI25 ,VAP01) 
                     
  SELECT @VAI01, @VAA01, @VAC01, @VAI04, '微信挂号', 0, @BCK01A , @BCE02D , @BCE03D , @EmpNo , ISNULL(@EmpName ,@VAA05) , 
       @CurDate, @CurDate , '微信挂号', 0, 1, 1, 1 ,0 , 1 , @SumVAJ38  , @SumVAJ38 , 0 , 0 ,0 
 
IF @@ERROR > 0 --如果发生错误则回滚数据
BEGIN
   IF @@TRANCOUNT > 0 ROLLBACK TRAN
   SET @ErrMsg = '写入病人单据记录时发生错误。'   
   Return 1
END

INSERT INTO VAJ1(VAJ01, VAA01, VAA07, VAJ04, VAJ05, ROWNR, VAJ01A, VAJ01B, VAJ09, VAJ10, VAI01, VAF01, VAK01, ACF01, VAJ15,
         BCK01A, BCK01B, BDN01, BBY01, BCJ02, VAJ21, VAJ22, VAJ23, VAJ24, VAJ25, VAJ26, VAJ27, VAJ28, VAJ29, VAJ30, VAJ31, VAJ32,
         VAJ33, VAJ34, VAJ35, VAJ36, VAJ37, VAJ38, VAJ39, BCE03A, BCK01C, BCE02B, BCE03B, VAJ46, VAJ47, VAJ48, BCK01D, VAJ54, BCE02D,
         BCE03D,VAJ53, VAJ57, FAB03, VAJ59 , VAJ61 , VAJ62 , BCK01E , DSK01 , VAJ67 )
         
SELECT VAJ01, @VAA01, @VAC01 , 1 , 1, ID , 0, 0, 0, 0 , @VAI01, 0 , 0 , 1, 0,        	        
       0, @BCK01A , BDN01, BBY01, '', 0, BCF01 , 1, 1, VAJ25, 0, 0, 1, 1, 100, 100, VAJ32,
       VAJ33, 1, VAJ35, VAJ36, VAJ33*VAJ25, VAJ33*VAJ25 , 0, @BCE03D , @BCK01A , @BCE02D , @BCE03D , @CurDate, @CurDate, 0, @BCK01A, '微信挂号', @EmpNo,
       ISNULL(@EmpName ,@VAA05) , 0 ,'微信挂号', '', VAJ32 , VAJ36 , @CurDate , 0 , 0 ,VAJ32    
from #tmpWeChatVAJ1 


SET @ErrMsg = ''
--------------------------------------------------------------------------------------------------------------------
COMMIT TRAN