/********************************************************/
-- Charge  : KIM JU YOUNG
-- tabled  : SP
-- Update  : 2008.07.15
-- Command : 일용급여재집계
-- [SPH_TWORKER_SUMFILE]
ALTER TABLE HDAYSUM ADD EX_NPAY_AM numeric(17, 4) NULL
ALTER TABLE HPOTLPM_D ADD EX_NPAY_AM numeric(17, 4) NULL
/********************************************************/
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[DZGOLD].[SPH_TWORKER_SUMFILE]') AND type in (N'P', N'PC'))
DROP PROCEDURE [DZGOLD].[SPH_TWORKER_SUMFILE]
GO



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [DZGOLD].[SPH_TWORKER_SUMFILE]
(
	@LANGKIND   	NVARCHAR(3),	--언어종류( 필수, KOR, CHS, ENG, JPN 등)	
	@CO_CD 		NVARCHAR(4),	--회사코드
	@RVRS_YM 	NVARCHAR(6),	--귀속년월
	@EMP_CD 	NVARCHAR(10),	--사번
	@DIV_CD 	NVARCHAR(10),	--본점사업장
	@CHK		NVARCHAR(1),	--구분(1, 전체, 0-해당월)
	@CLSE_FG	NVARCHAR(1),	--수정구분(0-마감,1-재집계)
	@CLCT_YM	NVARCHAR(6)		--지급연월
)
WITH ENCRYPTION
AS
BEGIN
/************************************************************************/
/*내    용 : 일용직급여입력- 월집계					*/
/*작 성 자 : 이주현 <- 김경인	                            			*/
/*작성일자 : 2007/0426<-2005						*/
/*수정내역 : 2007/04/26 월집계지급총액 부담금 합산			*/
/*			 2007/07/19 : HDAYSUM에 SAUP_AM, CLCT_YM 필드 추가 과세,비과세 저장 분리*/
--			 2008/04/29 : 비과세(신고분),비과세(신고제외분) 추가
--			 2008/07/14 : 수정구분 파라미터 추가, 재집계인경우 HPOTLPM_D지급연월 업데이트
/************************************************************************/

SET ANSI_WARNINGS OFF


  DECLARE @P_ERR_MSG  	NVARCHAR(255) 


  BEGIN TRANSACTION 


   IF(@CHK='1')
      BEGIN	

        IF  EXISTS( SELECT EMP_CD FROM HDAYSUM WHERE CO_CD=@CO_CD AND SUBSTRING(RVRS_YM,1,4)=SUBSTRING(@RVRS_YM,1,4)  )
			DELETE FROM HDAYSUM WHERE  CO_CD = @CO_CD AND SUBSTRING(RVRS_YM,1,4)=SUBSTRING(@RVRS_YM,1,4) 

      END
   ELSE
     BEGIN

       IF EXISTS( SELECT EMP_CD FROM HDAYSUM WHERE CO_CD=@CO_CD AND RVRS_YM=@RVRS_YM AND  EMP_CD= @EMP_CD )
			DELETE FROM HDAYSUM WHERE  CO_CD = @CO_CD AND RVRS_YM = @RVRS_YM AND  EMP_CD= @EMP_CD
   
	 END


    IF( @@ERROR <> 0 )
      BEGIN
     	SET @P_ERR_MSG = DZGOLD.UFN_GET_STRING_BY_LANGKIND( @LANGKIND, '월집계파일을 삭제할 수 없습니다.')
        GOTO ERROR
      END


	--재집계인경우
	IF(@CLSE_FG = '1')
	 BEGIN
		UPDATE HPOTLPM_D
		SET CLCT_YM = @CLCT_YM
		WHERE CO_CD = @CO_CD AND RVRS_YM = @RVRS_YM AND EMP_CD = @EMP_CD AND CLSE_YN ='001'
	 END

    --월집계파일생성
   IF(@CHK='1')
      BEGIN
         INSERT INTO HDAYSUM  ( CO_CD, DIV_CD, EMP_CD, RVRS_YM, TPAY_AM, HIFE_AM, NAPE_AM, DDCT_AM, INTX_AM, RSTX_AM, EXCL_YN,SAUP_AM, CLCT_YM 
			,NPAY_AM, EX_NPAY_AM) 
   			SELECT @CO_CD,  CASE WHEN ISNULL(MAX(V.DIV_CD), '')='' THEN @DIV_CD ELSE MAX(V.DIV_CD) END, @EMP_CD, H.RVRS_YM,  
				ISNULL(SUM(H.PAY_AM),0), SUM(H.HIFE_AM), SUM(H.NAPE_AM), SUM(H.EMIF_AM), SUM(H.INTX_AM) INTX_AM,
				 SUM(H.RSTX_AM) RSTX_AM, '000', ISNULL(SUM(H.SAUP_AM),0), H.CLCT_YM, SUM(ISNULL(H.NPAY_AM,0)),SUM(ISNULL(H.EX_NPAY_AM,0))
			   FROM  HPOTLPM_D H
				LEFT OUTER JOIN SDIV V ON H.CO_CD=V.CO_CD AND H.DIV_CD=V.DIV_CD
			WHERE H.CO_CD = @CO_CD  AND SUBSTRING(RVRS_YM,1,4)=SUBSTRING(@RVRS_YM,1,4)AND H.EMP_CD = @EMP_CD AND H.CLSE_YN='001'
			GROUP BY H.RVRS_YM	,H.CLCT_YM

     END
   ELSE
    BEGIN
        INSERT INTO HDAYSUM  ( CO_CD, DIV_CD, EMP_CD, RVRS_YM, TPAY_AM, HIFE_AM, NAPE_AM, DDCT_AM, INTX_AM, RSTX_AM, EXCL_YN ,SAUP_AM, CLCT_YM,NPAY_AM,EX_NPAY_AM) 
            SELECT @CO_CD, CASE WHEN ISNULL(MAX(V.DIV_CD), '')='' THEN @DIV_CD ELSE MAX(V.DIV_CD) END,  @EMP_CD, H.RVRS_YM,  
				ISNULL(SUM(H.PAY_AM),0) , SUM(H.HIFE_AM), SUM(H.NAPE_AM), SUM(H.EMIF_AM), SUM(H.INTX_AM) INTX_AM, 
	    		SUM(H.RSTX_AM) RSTX_AM, '000', ISNULL(SUM(H.SAUP_AM),0), H.CLCT_YM, SUM(ISNULL(H.NPAY_AM,0)),SUM(ISNULL(H.EX_NPAY_AM,0))
	   FROM  HPOTLPM_D H
		LEFT OUTER JOIN SDIV V ON H.CO_CD=V.CO_CD AND H.DIV_CD=V.DIV_CD
	WHERE H.CO_CD = @CO_CD  AND H.RVRS_YM = @RVRS_YM AND H.EMP_CD = @EMP_CD AND H.CLSE_YN='001'
	GROUP BY H.RVRS_YM	,H.CLCT_YM


    END
	
   
   IF( @@ERROR <> 0 )
      BEGIN
     	SET @P_ERR_MSG = DZGOLD.UFN_GET_STRING_BY_LANGKIND( @LANGKIND, '월집계파일을 생성할 수 없습니다.')
        GOTO ERROR
      END



   COMMIT TRANSACTION 
RETURN 1


END

ERROR:
     	RAISERROR( @P_ERR_MSG, 16, 1 )
        ROLLBACK TRANSACTION 
        SET ANSI_WARNINGS ON
	SET ANSI_WARNINGS ON
	RETURN
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


declare @module_cd as varchar(3),
	@ver_info as varchar(8)
set @module_cd = 'XHP'
set @ver_info = '20080714'
delete from smadiary where module_cd = @module_cd and ver_info = @ver_info
insert into smadiary (module_cd, upgrade_dt, ver_info, solution_cd) values (@module_cd, getdate(), @ver_info, 'X')
GO