IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'ky_NETImportDiaryFromExcel'
      AND SO.[type] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[ky_NETImportDiaryFromExcel]
  END
GO

CREATE PROCEDURE
       [dbo].[ky_NETImportDiaryFromExcel]
      (@ClientCode            VARCHAR(10),
       @FilterByColumn        VARCHAR(20),
       @LookupColumn          VARCHAR(20),
       @ActionCode            VARCHAR(10),
       @FeeEarnerCode         VARCHAR(10),
       @CurrentHandlerTeam    VARCHAR(10),
       @IsComplete            BIT,
       @Text1                 VARCHAR(MAX),
       @LoginHandler          VARCHAR(20),
       @PostPayment           VARCHAR(20),
       @ChargeValue           VARCHAR(20),
       @PaymentDate			  DATETIME,
       @PostInterest		  VARCHAR(20))
AS
/**********************************************************************************
 * ky_NETImportDiaryFromExcel
 *
 * This procedure is used to import case diary information from excel file
 *
 *
 * Modification History:
 * 2014-12-16 Arun.V         During import action flag value added in case diary
 * 2015-06-23 Sridharen      KEYD - 2332 - Use of LTRIM(RTRIM(
 * 2015-06-29 Arun V         Added Post Payment and Commission calculation added
 * 2015-09-30 Pino           Use ky_NETGetNextActionID
 * 2016-06-08 Pino           KEYD_3627 and KEYD_3595
 * 2016-09-13 Pino           KEYD-4040
 * 2016-11-23 John           KEYD-4275 - Added "0" to call to [Ky_NETDebtSpPostpayment]
 * 2017-01-27 Arun           Have added PostInterest and Payment date field
 * 2017-02-03 Arun			 Have corrected the post interest and payment date KEYD-4389
 * 2017-02-14 Arun			 Have corrected the post interest charages value
 **********************************************************************************/
BEGIN

SET NOCOUNT ON
DECLARE @ActionId INT
DECLARE @CaseCode VARCHAR(20)
DECLARE @Query VARCHAR(200)
DECLARE @KYC CHAR(1)

SET @Query = '
SELECT TOP 1 [MAT].[Code]
  FROM [dbo].[matters] [MAT]
 WHERE [MAT].[Closed] <> ''Y''
   AND [MAT].[ClientCode] = ''' +  @ClientCode  + '''
   AND ' + @LookupColumn + ' = ''' + @FilterByColumn  + ''''

DECLARE @temp        TABLE([caseCode] VARCHAR(20))
DECLARE @Flag        TINYINT
DECLARE @publish     CHAR(1)

INSERT INTO @temp([caseCode])
EXEC(@query)

SET @CaseCode = (SELECT TOP 1 [TMP].[caseCode] FROM @temp [TMP])

SELECT @Flag = [TAC].[Flag],
       @publish = CASE [TAC].[PUBLISHER]
                       WHEN 'A' THEN 'A'
                       WHEN 'P' THEN 'P'
                       WHEN 'E' THEN 'E'
                       ELSE 'N' END,
       @KYC = [TAC].[KYC]
  FROM [dbo].[TemplateActions] [TAC]
 WHERE [TAC].[ACTIONCODE] = @ActionCode

SET @publish = ISNULL(@publish, 'N')

IF(@CaseCode<>'')
  BEGIN

    -- Pino 2015-09-30 Start
    EXEC @ActionId = [dbo].[ky_NETGetNextActionID]
    --SET @ActionId = (select ISNULL(MAX(ACTIONID),0)+1 FROM diary )
    -- Pino 2015-09-30 Start

    INSERT
      INTO [dbo].[diary]
          ([ACTIONID],
           [CASECODE],
           [DATE],
           [STATUS],
           [ACTIONCODE],
           [TEXT1],
           [DUEDATE],
           [FNCODE],
           [TEAMCODE],
           [DYSTARTTIME],
           [DYENDTIME],
           [Flag],
           [PUBLISH],
           [KYC])
    VALUES(@ActionId,
           @caseCode,
           ISNULL(@PaymentDate,GETDATE()),
           @IsComplete,
           @ActionCode,
           @Text1,
           ISNULL(@PaymentDate,GETDATE()),
           @FeeEarnerCode,
           @CurrentHandlerTeam,
           dbo.ky_ConvertTimeToClarion(ISNULL(@PaymentDate,GETDATE())),
           dbo.ky_ConvertTimeToClarion(ISNULL(@PaymentDate,GETDATE())),
           @Flag,
           @publish,
           @KYC)

    INSERT
      INTO [dbo].[DiaryDelegations]
          ([ACTIONID],
           [HANDLER],
           [TEAM],
           [DATE],
           [TIME],
           [DUEDATE],
           [DUETIME],
           [STATUS],
           [OWNER],
           [DELEGATE],
           [DELEGATESTATUS],
           [ActionType],
           [FromHandler],
           [DelType],
           [DATER],
           [TIMER])
    VALUES(@ActionId,
           @FeeEarnerCode,
           @CurrentHandlerTeam,
           ISNULL(@PaymentDate,GETDATE()),
           dbo.ky_ConvertTimeToClarion(ISNULL(@PaymentDate,GETDATE())),
           ISNULL(@PaymentDate,GETDATE()) ,
           dbo.ky_ConvertTimeToClarion(ISNULL(@PaymentDate,GETDATE())),
           @IsComplete ,
           'Y',
           @FeeEarnerCode,
           0,
           'A',
           @LoginHandler,
           (CASE WHEN @IsComplete = 1 then 'Completed' ELSE 'Created' END),
           (CASE WHEN @IsComplete = 1 then ISNULL(@PaymentDate,GETDATE()) ELSE null END),
           (CASE WHEN @IsComplete = 1 then dbo.ky_ConvertTimeToClarion(ISNULL(@PaymentDate,GETDATE())) ELSE null END))

    IF @PostPayment <> '0.00'
      BEGIN

        DECLARE @OriginalDebt        DECIMAL(18,2)
        DECLARE @DebtInterest        DECIMAL(18,2)
        DECLARE @RecoverableCosts    DECIMAL(18,2)
        DECLARE @DebtCollected       DECIMAL(18,2)
        DECLARE @OutstandingBalance  DECIMAL(18,2)
        DECLARE @TodaysDate          DATETIME
        DECLARE @Reference           NVARCHAR(20)
        DECLARE @Amount              DECIMAL(18,2)
		DECLARE @PrincipalPaid       DECIMAL(18,2)
		DECLARE @Payment       DECIMAL(18,2)
		
        SELECT @OriginalDebt     = ISNULL([MAT].[OriginalDebt], 0.00),
               @DebtInterest     = ISNULL([MAT].[DebtInterest], 0.00),
               @RecoverableCosts = ISNULL([MAT].[RecoverableCosts], 0.00),
               @DebtCollected    = ISNULL([MAT].[DebtCollected], 0.00),
               @Reference        = [MAT].[User1]
          FROM [dbo].[matters] [MAT]
         WHERE [MAT].[code] = @CaseCode
         
        SET @OutstandingBalance = (@OriginalDebt + @DebtInterest + @RecoverableCosts - @DebtCollected)
        SET @TodaysDate = ISNULL(@PaymentDate,GETDATE())
        SET @PrincipalPaid = (@OutstandingBalance-@PostPayment)

        IF @PostPayment <> @OutstandingBalance
          BEGIN
            DECLARE @Date DATETIME
            SET @Date = NULL
            
            --Interest balance caculation
            IF(@PostInterest <> '0.00')
            BEGIN
				DECLARE @InterestBalance DECIMAL(18,2) 
				
				SET @InterestBalance = (SELECT SUM(ISNULL(INTEREST,0))-SUM(ISNULL(INTERESTPAID,0))  FROM DebtCostingLedger WHERE MATTERCODE = @CaseCode )
				
				SET @PostInterest = @InterestBalance - @PostInterest 
				
            END

			set @Payment = (ISNULL(@PrincipalPaid,0) + ISNULL(@PostInterest,0))
			
            EXEC [Ky_NETDebtSpPostpayment] @caseCode,
                                           @TodaysDate,
                                           @Reference,
                                           'Direct Payment',
                                           'Cheque',
                                           @Payment,
                                           @PrincipalPaid, 
                                           0,
                                           @PostInterest, 
                                           'P',
                                           '',
                                           '',
                                           @Reference,
                                           '',
                                           @LoginHandler,
                                           0,
                                           0,
                                           @ChargeValue,
                                           'C',
                                           'N',
                                           '',
                                           @Date,
                                           @Date,
                                           0
          END

      END

  END

SET NOCOUNT OFF
END

GO