/**
*
*  This function is called to get the date upon which a non-importable marked van line
*  statement can be imported on based upon the van line group and statement year and
*  number.
*
*  See also the IsVanLineStatementImportable() function which relies upon this
*  function and the GetVanLineStatementPeriodEndDate() which this function
*  relies upon.
*/
CREATE FUNCTION [dbo].[GetVanLineStatementImportableDate](
    @inVanlineGroupExternalCode varchar(10),
    @inStatementYear int,
    @inStatementNumber int,
    @inVanLineStatementDefaultEndDate datetime )
returns datetime as
begin
    declare @outStatementImportableDate datetime

    declare @theVanLineStatementPeriodEndDate datetime = dbo.GetVanLineStatementPeriodEndDate(
        @inVanlineGroupExternalCode, @inStatementYear, @inStatementNumber,
        @inVanLineStatementDefaultEndDate )

    if( @inVanlineGroupExternalCode = 'SIRVA' )
    begin
        -- Sirva periods end on the 15th and end of the month.  Sirva statements send the posting tickets on
        -- the second business day after the end of the period.  We'll just assume a statement is importable
        -- the day after the second business day after the period end date.  We don't worry about holidays.
        declare @theCounter int = 2;
        set @outStatementImportableDate = @theVanLineStatementPeriodEndDate
        while( @theCounter > 0 )
        begin
            set @theCounter = @theCounter - 1
            set @outStatementImportableDate = dateadd( day, 1, @outStatementImportableDate )

            -- This case when works no matter what @@datefirst is set to => 1 means it is a Saturday or Sunday.
            if( case when ( ( ( datepart( dw, @outStatementImportableDate ) - 1 ) + @@datefirst ) % 7 ) in ( 0, 6 ) then 1 else 0 end = 1 )
            begin
                set @theCounter = @theCounter + 1
            end
        end
        -- Now, get the day after that, even if the new date is a Saturday or Sunday.
        set @outStatementImportableDate = dateadd( day, 1, @outStatementImportableDate )
    end

    return isnull( @outStatementImportableDate, dateadd( day, 1, @theVanLineStatementPeriodEndDate ) )
end
GO
GRANT EXECUTE ON  [dbo].[GetVanLineStatementImportableDate] TO [MssExec]
GO