/**
*    This procedure is called to determine if the specified document type for the specified XmlSystem and Order meet
*    the user's defined rules to warrant downloading this document's content now.  This is also called by admin to
*    verify that a rule is properly constructed.  Therefore, any rule that is attempted for purposes of actually
*    deciding if a real document should be downloaded, and that rule is "invalid" (i.e. an exception occurs), we will
*    just ignore that rule and apply any other rules (if any). We will just assume the user is "in process of creating
*    or modifying any rules".
*
*    Input Parameters:
*    @param @inVendorConnectKeyId The vendor connect key id that sent in the document meta data.
*    @param @inVanLineExternalCode The XmlSystem external code for this van line.
*    @param @inOrderNumber The order number (as formatted by the external source).
*    @param @inAgentID The agency number (as formatted by the external source).
*    @param @inDocumentTypeExternalCode The external code for the document type.
*    @param @inValidateRuleID Set when admin is validating a rule and null or 0 when were are working with live data.
*    @param @inOverrideOrderID Optionally set on reprocessing third party errors from MoversSuite.  When set, this is
*           the Orders.PriKey for the order this document is to be attached to. @outOrderID will be set to this int value if set.
*    @param @inOverrideDocumentTypeID Optionally set on reprocessing third party errors from MoversSuite.  When set, this is
*           the DocumentType.DocumentTypeID to use for this document. @outDocumentTypeID will be set to this int value if set.
*
  *    Output Parameters:
*    @param @outDownloadOfDocumentIsApproved The flag where 1 means we should download this document and 0 means do not.
*    @param @outOrderID The primary key of the matching Orders record, if it exists.
*    @param @outDocumentTypeID The primary key of the matching document type, if it exists.
*    @param @outErrorCode An error code from the ErrorCode table. 0 means success (no error), -1 means see the
*           @outErrorMessage parameter for the message.
*    @param @outErrorMessage An error message, should an exception occur.  @outErrorCode will be -1 if this is set.
*    @param @outXmlSystemDataTypeMapName The XML system data type map name linked back to the vendor connect config.
*/

CREATE PROCEDURE [dbo].[DocumentAutoDownloadApplyRules]
    @inVendorConnectKeyId nvarchar(4),
    @inVanLineExternalCode varchar(32),
    @inOrderNumber varchar(30),
    @inAgentID varchar(10),
    @inDocumentTypeExternalCode varchar(32),
    @inValidateRuleID int = null,
    @inOverrideOrderID varchar(10) = null,
    @inOverrideDocumentTypeID varchar(10) = null,
    @outDownloadOfDocumentIsApproved bit output,
    @outOrderID int output,
    @outDocumentTypeID int output,
    @outErrorCode int output,
    @outErrorMessage varchar(1000) output,
    @outXmlSystemDataTypeMapName varchar(64) output
as
set nocount on

declare @theRuleCounter int
declare @theTotalDetailsCount int
declare @theDetailCounter int
declare @theInsertedDetailsCount int
declare @theNetParenthesisCount int
declare @theDidNetParenthesisCountEverGoNegative bit
declare @ATLAS_VAN_LINE_EXTERNAL_CODE char(1)

-- Possible Error codes
declare @UNKNOWN_SQL_ERROR int
declare @MISSING_CLOSING_PARENTHESIS int
declare @MISSING_OPENING_PARENTHESIS int
declare @NEGATIVE_PARENTHESIS_COUNT int

select
    @outDownloadOfDocumentIsApproved = 0,
    @outDocumentTypeID = null,
    @outOrderID = null,
    @outErrorCode = 0,
    @outErrorMessage = null,
    @outXmlSystemDataTypeMapName = null,
    @theRuleCounter = 1,
    @theTotalDetailsCount = 0,
    @theDetailCounter = 1,
    @theDidNetParenthesisCountEverGoNegative = 0,
    @ATLAS_VAN_LINE_EXTERNAL_CODE = 'A',
    @UNKNOWN_SQL_ERROR = 1,    -- Not an ErrorCode.ECPriKey
    @MISSING_CLOSING_PARENTHESIS = 8500,
    @MISSING_OPENING_PARENTHESIS = 8501,
    @NEGATIVE_PARENTHESIS_COUNT = 8502,
    @inValidateRuleID = isnull( @inValidateRuleID, 0 )

declare @theSqlText nvarchar(max)
declare @theSqlCTE nvarchar(max)
declare @theSqlSelect nvarchar(max)
declare @theSqlWhere nvarchar(max)
declare @theTotalRulesCount int
declare @theCurrentRuleID int

declare @theXmlInterfaceID int
declare @theXmlInterfaceVendorName varchar(50)

declare @theOrderID int
declare @theOrderControl varchar(30)
declare @theShipperName varchar(26)
declare @theErrorCode int
declare @theInvoiceCustomerNumber varchar(15)
declare @theOrderControlInvoiceNumber varchar(30)
declare @theAgentPriKey int

declare @theBranchPriKey int
declare @theShipmentTypeID int
declare @theMoveTypeGroupID int
declare @theHaulModeID int

declare @theRuleDescription varchar(128)
declare @theSortOrder int
declare @theTableName nvarchar(30)
declare @thePriKeyName nvarchar(30)
declare @theDetailKeyTableName nvarchar(50)
declare @theDetailKeyColumnName nvarchar(50)

declare @theItemSqlText nvarchar(5)
declare @theParenthesisCount smallint
declare @theIsBooleanCombiner bit
declare @theDocumentAutoDownloadRuleDetailFID int
declare @theRuleSaysToDownload bit
declare @theCTE_Name nvarchar(64)

declare @thePossibleRules table
(
    RuleID int identity (1, 1) not null,
    RuleDescription varchar(128),
    DocumentAutoDownloadRuleID int not null
)

declare @theRuleDetails table
(
    DetailID int identity (1, 1) not null,
    SortOrder int not null,
    DocumentAutoDownloadRuleDetailFID int not null,
    DocumentAutoDownloadRuleItemFID int not null
)

if( @inValidateRuleID > 0 )
begin
    -- We are just testing the SQL so we don't need live data.
    select
        @theXmlInterfaceID = XmlInterface.XmlInterfaceID,
        @theXmlInterfaceVendorName = XmlInterface.VendorName,
        @theOrderID = -1,
        @outOrderID = -1,
        @outDocumentTypeID = -1,
        @theBranchPriKey = -1,
        @theShipmentTypeID = -1,
        @theMoveTypeGroupID = -1,
        @theHaulModeID = -1
    from DocumentAutoDownloadRule
    inner join XmlInterface on XmlInterface.XmlInterfaceID = DocumentAutoDownloadRule.XmlInterfaceFID
    where DocumentAutoDownloadRuleID = @inValidateRuleID
end
else
begin
    -- Determine the apprpriate XmlInterface we are to use.
    select
        @theXmlInterfaceID = XmlSystem.XmlInterfaceFID,
        @theXmlInterfaceVendorName = XmlInterface.VendorName,
        @outXmlSystemDataTypeMapName = XmlSystemDataTypeMap.[Name]
    from MoversConnectVendor
    inner join VendorConnect on VendorConnect.MoversConnectVendorFID = MoversConnectVendor.MoversConnectVendorID
    inner join XmlSystemDataTypeMap on XmlSystemDataTypeMap.XmlSystemDataTypeMapID = VendorConnect.XmlSystemDataTypeMapFID
    inner join XmlSystem on XmlSystem.XmlSystemID = XmlSystemDataTypeMap.XmlSystemFID
    inner join XmlInterface on XmlInterface.XmlInterfaceID = XmlSystem.XmlInterfaceFID
    where MoversConnectVendor.IntegratorKeyID = @inVendorConnectKeyId and
        MoversConnectVendor.MoversConnectInactive = 0 and
        VendorConnect.Inactive = 0

    if( @theXmlInterfaceID is not null )
    begin
        -- Determine the Agent...
        select
            @theAgentPriKey = Agent.AgentPriKey
        from Agent
        inner join VanLine on VanLine.VLPriKey = Agent.VLPriKey
        inner join XmlExternalInterfaceVanlineMap on XmlExternalInterfaceVanlineMap.XmlInterfaceFID = @theXmlInterfaceID and
            XmlExternalInterfaceVanlineMap.ExternalCode = @inVanLineExternalCode
        where Agent.AgentID in ( @inAgentID, concat( VanLine.VanLineCode, @inAgentID ) ) and
            XmlExternalInterfaceVanlineMap.VanlineFID = VanLine.VLPriKey

        -- Get the DocumentType...
        if( isnull( @inOverrideDocumentTypeID, '' ) != '' )
        begin
            set @outDocumentTypeID = convert( int, @inOverrideDocumentTypeID )
        end
        else
        begin
            select top 1
                @outDocumentTypeID = XmlDocInterfaceMap.DocumentTypeFID
            from XmlDocInterfaceMap
            where XmlDocInterfaceMap.XmlInterfaceFID = @theXmlInterfaceID and
                XmlDocInterfaceMap.ExternalDocumentTypeCode = @inDocumentTypeExternalCode
            order by XmlInterfaceFID, ExternalDocumentTypeCode, XmlDocInterfaceMapID
        end
    end

    if( isnull( @inOverrideOrderID, '' ) != '' )
    begin
        set @theOrderID = convert( int, @inOverrideOrderID )
    end
    else if( @theAgentPriKey is not null )
    begin
        -- Get the Order for the appropriate van line
        if( @inVanLineExternalCode = @ATLAS_VAN_LINE_EXTERNAL_CODE )
        begin
            exec PMGetAtlasOrderID
                @inExternalInterface = @theXmlInterfaceVendorName,
                @inAgentID = @theAgentPriKey,
                @inOrderNumber = @inOrderNumber,
                @outOrderID = @theOrderID output,
                @outOrderControl = @theOrderControl output,
                @outShipperName = @theShipperName output,
                @outErrorCode = @theErrorCode output

                if( @theErrorCode > 0 )
                begin
                    set @outErrorCode = @theErrorCode
                end
        end
        -- Add this later or more as needed. Might use Sirva for SISTRS document system.
        /*
        else if( @inVanLineExternalCode = 'AVL' or @inVanLineExternalCode = 'NVL' )
        begin
            exec PMSIGetSIRVAOrderPriKey
                @inExternalInterface = @theXmlInterfaceVendorName,
                @inExternalVanlineCode = @inVanLineExternalCode,
                @inAgentID = @theAgentPriKey,
                @inOrderNumber = @inOrderNumber,
                @outOrderID = @theOrderID output,
                @outOrderControl = @theOrderControl output,
                @outShipperName = @theShipperName output,
                @outErrorCode = @theErrorCode output

                if( @theErrorCode > 0 )
                begin
                    set @outErrorCode = @theErrorCode
                end
        end */
    end

    -- For other non-van line vendor connect integrators (like Arrivy).
    if( @theOrderID is null )
    begin
        exec PMGetOrderID
            @inOrderControl= @inOrderNumber,
            @inOrderControlInvoiceNumber = @theOrderControlInvoiceNumber,
            @outOrderID = @theOrderID output,
            @outOrderControl = @theOrderControl output,
            @outShipperName = @theShipperName output,
            @outCustomerNumber = @theInvoiceCustomerNumber output,
            @outErrorCode = @theErrorCode output

            if( @theErrorCode > 0 )
            begin
                set @outErrorCode = @theErrorCode
            end
    end
    set @outOrderID = @theOrderID

    if( @theOrderID is not null and @outDocumentTypeID is not null )
    begin
        -- Get the key info off of the order.
        select
            @theBranchPriKey = Orders.BranchPriKey,
            @theShipmentTypeID = Orders.ShipmentTypeFID,
            @theHaulModeID = OrdersExtended.HaulModeFID,
            @theMoveTypeGroupID = MoveType.MTGroupPriKey
        from Orders
        left outer join OrdersExtended on OrdersExtended.OrderFID = Orders.PriKey
        left outer join MoveType on MoveType.PriKey = Orders.MoveType
        where Orders.PriKey = @theOrderID
    end
end

if( @theOrderID is not null and @outDocumentTypeID is not null )
begin
    -- If we had a problem finding the Order, it got resolved.
    set @outErrorCode = 0

    if( @inValidateRuleID > 0 )
    begin
        insert into @thePossibleRules
        (
            DocumentAutoDownloadRuleID,
            RuleDescription
        )
        -- We are validating a specific rule so that is the only rule we will test.
        select
            DocumentAutoDownloadRuleID,
            RuleDescription
        from DocumentAutoDownloadRule
        where DocumentAutoDownloadRuleID = @inValidateRuleID

        set @theTotalRulesCount = @@ROWCOUNT
    end
    else
    begin
        -- We only process rules that are active and validated, in case someone is
        -- creating, updating, etc. rules while documents are actually coming in.
        insert into @thePossibleRules
        (
            DocumentAutoDownloadRuleID,
            RuleDescription
        )
        select
            DocumentAutoDownloadRuleID,
            RuleDescription
        from DocumentAutoDownloadRule
        where XmlInterfaceFID = @theXmlInterfaceID and
            Inactive = 0 and
            Validated = 1

        set @theTotalRulesCount = @@ROWCOUNT
    end

    while( @outDownloadOfDocumentIsApproved = 0 and @theRuleCounter <= @theTotalRulesCount )
    begin
        select
            @theCurrentRuleID = DocumentAutoDownloadRuleID,
            @theRuleDescription = RuleDescription
        from @thePossibleRules
        where RuleID = @theRuleCounter

        insert into @theRuleDetails
        (
            SortOrder,
            DocumentAutoDownloadRuleDetailFID,
            DocumentAutoDownloadRuleItemFID
        )
        select
            SortOrder,
            DocumentAutoDownloadRuleDetailID,
            DocumentAutoDownloadRuleItemFID
        from DocumentAutoDownloadRuleDetail
        where DocumentAutoDownloadRuleFID = @theCurrentRuleID
        order by SortOrder

        set @theInsertedDetailsCount = @@ROWCOUNT

        select
            @theTotalDetailsCount = @theTotalDetailsCount + @theInsertedDetailsCount,
            @theSqlCTE = N'with Acceptable_CTE ( IsAcceptable )
as
(
    select IsAcceptable = convert( bit, 1 )
)',
            @theSqlSelect = N'
select top 1
    @ItExists = Acceptable_CTE.IsAcceptable
from Acceptable_CTE',

            @theSqlWhere = N'',
            @theNetParenthesisCount = 0,
            @theDidNetParenthesisCountEverGoNegative = 0

        while( @theDetailCounter <= @theTotalDetailsCount )
        begin
            select
                @theDocumentAutoDownloadRuleDetailFID = theRuleDetails.DocumentAutoDownloadRuleDetailFID,
                @theTableName = DocumentAutoDownloadRuleItem.TableName,
                @thePriKeyName = DocumentAutoDownloadRuleItem.PriKeyName,
                @theDetailKeyTableName = DocumentAutoDownloadRuleItem.DetailKeyTableName,
                @theDetailKeyColumnName = DocumentAutoDownloadRuleItem.DetailKeyColumnName,
                @theItemSqlText = DocumentAutoDownloadRuleItem.SqlText,
                @theParenthesisCount = DocumentAutoDownloadRuleItem.ParenthesisCount,
                @theIsBooleanCombiner = DocumentAutoDownloadRuleItem.IsBooleanCombiner
            from @theRuleDetails as theRuleDetails
            inner join DocumentAutoDownloadRuleItem on DocumentAutoDownloadRuleItem.DocumentAutoDownloadRuleItemID = theRuleDetails.DocumentAutoDownloadRuleItemFID
            where theRuleDetails.DetailID = @theDetailCounter

            set @theNetParenthesisCount = @theNetParenthesisCount + @theParenthesisCount
            if( @theNetParenthesisCount < 0 )
            begin
                set @theDidNetParenthesisCountEverGoNegative = 1
            end

            if( @theParenthesisCount != 0 or @theIsBooleanCombiner = 1 )
            begin
                set @theSqlWhere = @theSqlWhere + @theItemSqlText
            end
            else if( isnull( @theTableName, N'' ) != N'' )
            begin
                -- We use the CTE table name multiple times so build it just once here.  The unique DetailFID
                -- is part of the name since the rule can include the same table multiple times.
                set @theCTE_Name = concat( @theTableName, ltrim( rtrim( str( @theDocumentAutoDownloadRuleDetailFID ) ) ), N'_CTE' )

                set @theSqlCTE = @theSqlCTE + N',
' + @theCTE_Name + N' ( ' + @thePriKeyName + N' )
as
(
    select ' + @thePriKeyName + N' = ' + @theDetailKeyColumnName + N'
    from ' + @theDetailKeyTableName + N'
    where DocumentAutoDownloadRuleDetailFID = ' + ltrim( rtrim( str( @theDocumentAutoDownloadRuleDetailFID ) ) ) + N'
)'

                set @theSqlSelect = @theSqlSelect + N'
left outer join ' + @theCTE_Name + N' on ' + @theCTE_Name + N'.' + @thePriKeyName + N' = ' + case @theTableName
    when N'Branch' then ltrim( rtrim( str( @theBranchPriKey ) ) )
    when N'ShipmentType' then ltrim( rtrim( str( isnull( @theShipmentTypeID, -1 ) ) ) )
    when N'MoveTypeGroups' then ltrim( rtrim( str( isnull( @theMoveTypeGroupID, -1 ) ) ) )
    when N'DocumentType' then ltrim( rtrim( str( isnull( @outDocumentTypeID, -1 ) ) ) )
    when N'HaulMode' then ltrim( rtrim( str( isnull( @theHaulModeID, -1 ) ) ) )
end
                set @theSqlWhere = @theSqlWhere + N'
    ' + @theCTE_Name + N'.' + @thePriKeyName + N' is not null'

            end

            set @theDetailCounter = @theDetailCounter + 1
        end

        select
            @theSqlText = @theSqlCTE + @theSqlSelect + case
                when isnull( @theSqlWhere, N'' ) = N'' then N''
                else N'
where ' + @theSqlWhere
            end,
            @theRuleSaysToDownload = 0

        set xact_abort on
        begin try

            exec sp_executesql @theSqlText, N'@ItExists bit output', @theRuleSaysToDownload output
            if( isnull( @theRuleSaysToDownload, 0 ) = 1 )
            begin
                -- All done.  We found a rule that says we need to download this document type for this order.
                set @outDownloadOfDocumentIsApproved = 1
            end

        end try
        begin catch
            if( @inValidateRuleID > 0 )
            begin
                select
                    @outErrorCode = @UNKNOWN_SQL_ERROR,
                    @outErrorMessage = ''

                if( @theDidNetParenthesisCountEverGoNegative = 1 )
                begin
                    set @outErrorCode = @NEGATIVE_PARENTHESIS_COUNT
                end
                else if( @theNetParenthesisCount > 0 )
                begin
                    set @outErrorCode = @MISSING_CLOSING_PARENTHESIS
                end
                else if( @theNetParenthesisCount < 0 )
                begin
                    set @outErrorCode = @MISSING_OPENING_PARENTHESIS
                end
                else
                begin
                    set @outErrorMessage = @outErrorMessage + 'Error: ' + ltrim( str( error_number() ) ) + ': ' + error_message()
                end

                if( @outErrorCode != @UNKNOWN_SQL_ERROR )
                begin
                    set @outErrorMessage = @outErrorMessage + isnull( ( select [Description] from ErrorCode where ECPriKey = @outErrorCode ), error_message() )
                end
            end
        end catch

        delete from @theRuleDetails
        set @theRuleCounter = @theRuleCounter + 1
    end
end
select
    @outOrderID = isnull( @outOrderID, 0 ),
    @outDocumentTypeID = isnull( @outDocumentTypeID, 0 )

if( @outErrorCode > 0 and isnull( @outErrorMessage, '' ) = '' )
begin
    select
        @outErrorMessage = ErrorCode.[Description]
    from ErrorCode
    where ErrorCode.ECPriKey = @outErrorCode
end
GO
GRANT EXECUTE ON  [dbo].[DocumentAutoDownloadApplyRules] TO [MssExec]
GO