/**
* Description: Neither Csi-Pay nor Remedy Payments services provides the banking date (meaning, the date that
* a given payment or refund transaction will actually hit the merchant's bank account).  We will use the
* transaction's created on UTC date to determine this.  One reason this is a stored procedure is to have
* a consistent local time zone (that being the server's time zone) for all users, regardless of the time
* zone set on their workstation.  On the PlasticProvider record, users can configure the rules for how this
* is calculated as CSI-Pay and Remedy do this differently or they can continue to just use the date of the
* charge/refund transaction.
*/
create procedure [dbo].[GetPlasticProviderDepositDate]
    @inProviderId int,
    @inCreatedOnUtcDate datetime,
    @outTransactionDate datetime out
as
set nocount on

declare @theCurrentDate datetime
declare @theCurrentUtcDate datetime
declare @theUtcMinutesOffset int
declare @theCreatedOnUtcDate datetime
declare @theCalculateBestGuessDepositDate bit
declare @theAdjustmentDays int
declare @theMerchantCutoffTime time
declare @theMerchantIsSaturdayBusinessDay bit
declare @theMerchantIsSundayBusinessDay bit
declare @theAdjustForHolidays bit

-- SQL Server's concept for day of the week differs greatly from C#'s interpretation.
-- Specifically, that the datepart(dw) where dw=day of the week, depends upon the current
-- setting for DATEFIRST in the database (which is retrieved via the @@datefirst global
-- variable).  The SQL Server default @@datefirst value is 7 (which is the English US
-- default), meaning Sunday is the first day of the week (and hence = 1) while Saturday = 7.
-- C# always interprets day of the week as Sunday = 0 and Saturday = 6 since it's an
-- enumerated set of values.
declare @SATURDAY int
declare @SUNDAY int

select
    @theCurrentUtcDate = getutcdate(),
    @theCurrentDate = getdate()

if( @inProviderId is null )
begin
    -- There is not enough info to calculate, so use today's date.
    set @outTransactionDate = convert( datetime, convert( date, @theCurrentDate ) )
end
else
begin
    select
        @theUtcMinutesOffset = datediff( minute, @theCurrentUtcDate, @theCurrentDate ),
        @theCreatedOnUtcDate = isnull( @inCreatedOnUtcDate, @theCurrentUtcDate ),
        @theCalculateBestGuessDepositDate = PlasticProvider.CalculateBestGuessDepositDate,
        @theAdjustmentDays = PlasticProvider.AdjustmentDays,
        @theMerchantCutoffTime = convert( time, PlasticProvider.CutoffTime ),
        @theMerchantIsSaturdayBusinessDay = PlasticProvider.IsSaturdayBusinessDay,
        @theMerchantIsSundayBusinessDay = PlasticProvider.IsSundayBusinessDay,
        @theAdjustForHolidays = PlasticProvider.AdjustForHolidays,
        @SATURDAY = case @@datefirst
            when 7 then 7
            else 7 - @@datefirst
        end,
        --case @@datefirst
        --    when 7 then 7        -- When Sunday (7) is the first day of the week, then datepart(dw) returns 7 for Saturday.
        --    when 1 then 6        -- When Monday (1) is the first day of the week, then datepart(dw) returns 6 for Saturday.
        --    when 2 then 5        -- and so on.
        --    when 3 then 4
        --    when 4 then 3
        --    when 5 then 2
        --    else 1
        --end,
        @SUNDAY = 8 - @@datefirst
        --case @@datefirst
        --    when 7 then 1        -- When Sunday (7) is the first day of the week, then datepart(dw) returns 1 for Sunday.
        --    when 1 then 7        -- When Monday (1) is the first day of the week, then datepart(dw) returns 7 for Sunday.
        --    when 2 then 6        -- and so on.
        --    when 3 then 5
        --    when 4 then 4
        --    when 5 then 3
        --    else 2
        --end
    from PlasticProvider
    where PlasticProvider.ProviderID = @inProviderId

    declare @theLocalDateTime datetime = convert( datetime, dateadd( minute, @theUtcMinutesOffset, @theCreatedOnUtcDate ) )
    declare @theCreationDate date = convert( date, @theLocalDateTime )
    declare @theCreationTimeOfDay time = convert( time, @theLocalDateTime )

    if( @theCalculateBestGuessDepositDate = 0 )
    begin
        set @outTransactionDate = @theCreationDate
    end
    else
    begin
        declare @theTransDateBusinessDateAdjustment int = 0;
        declare @theTransDateBusinessDateDayOfTheWeek int;

        -- Now calculate the deposit date starting with today (if today is a Holiday, then that fact means we
        -- need to add an extra day to the calculation).  No negative days are allowed in this calculation.
        set @theAdjustmentDays = case
            when @theAdjustmentDays <= 0 then 0
            else @theAdjustmentDays
        end

        -- If the @theMerchantCutoffTime is 23:59 (the maximum possible value), then ignore the
        -- @theMerchantCutoffTime part of this calculation.
        if( @theMerchantCutoffTime < convert( time, '23:59:00' ) and @theCreationTimeOfDay >= @theMerchantCutoffTime )
        begin
            -- We are past the transaction auto-close time, so it's just like we ar actually
            -- creating it tomorrow, no matter what day of the week today is.
            set @theTransDateBusinessDateAdjustment = 1
        end

        while( @theAdjustmentDays >= 0 )
        begin
            -- Now calculate the actual transaction date.
            set @outTransactionDate = dateadd( day, @theTransDateBusinessDateAdjustment, @theCreationDate )
            set @theTransDateBusinessDateDayOfTheWeek = datepart( dw, @outTransactionDate )

            -- Check for any Holidays.  Not all users will want this nor will they set these up.  While the
            -- Holidays table is really a table of company holidays, it may not neccessarially be all of the
            -- true "public (banking)" holidays. But whatever holidays are in there are likely to be, but not
            -- guaranteed to be, true "public (banking)" holidays.
            if( @theAdjustForHolidays = 1 and exists( select top 1 1 from Holiday where [Date] = @outTransactionDate ) )
            begin
                -- If this is a Holiday, then this is not a business day for this merchant's
                -- bank, so we need to add an additional day.
                set @theAdjustmentDays = @theAdjustmentDays + 1
            end
            else if( @theTransDateBusinessDateDayOfTheWeek = @SATURDAY )
            begin
                -- This potential transaction day is a Saturday.  Saturday may or may not be a
                -- business day for this merchant's bank.
                if( @theMerchantIsSaturdayBusinessDay = 0 )
                begin
                    -- Saturday is not a business day for this merchant's bank, so we need to
                    -- add an additional day.
                    set @theAdjustmentDays = @theAdjustmentDays + 1
                end
            end
            else if(@theTransDateBusinessDateDayOfTheWeek = @SUNDAY )
            begin
                -- This potential transaction day is a Sunday.  Sunday probably won't be a
                -- business day for this merchant's bank.
                if( @theMerchantIsSundayBusinessDay = 0 )
                begin
                    -- Sunday is not a business day for this merchant's bank, so we need to
                    -- add an additional day.
                    set @theAdjustmentDays = @theAdjustmentDays + 1
                end
            end

            set @theAdjustmentDays = @theAdjustmentDays - 1
            set @theTransDateBusinessDateAdjustment = @theTransDateBusinessDateAdjustment + 1
        end
    end
end
GO
GRANT EXECUTE ON  [dbo].[GetPlasticProviderDepositDate] TO [MssExec]
GO