/**
*    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 uploading this document's content via some interface.  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 uploaded, 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 that rule".
*
*    Input Parameters:
*    @param @inOrderID: The primary key for the order the document to possibly be uploaded is attached to.
*    @param @inDocumentType: The DocumentType.Type for the document to possibly be uploaded.
*    @param @inSourceSystemDataTypeMapName: The XmlSystemDataTypeMap.Name of the XmlSystem the document came from.  We
*           do not want to upload a document to the system it came from.
*    @param @inValidateRuleID Set when admin is validating a rule and null or 0 when we are working with live data.  If
*           this is set, then examine @outErrorCode and @outErrorMessage to see if the specified rule is valid.
*
*    Output Parameters:
*    Note: @outErrorCode and @outErrorMessage are only set if @inValidateRuleID is set.
*    @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.
*
*    Output Results
*    A list of XmlSystemDataTypeMap.Names for document systems we need to upload this document to, if any.
*/

CREATE PROCEDURE [dbo].[DocumentAutoUploadApplyRules]
    @inOrderID int,
    @inDocumentType varchar(10),
    @inSourceSystemDataTypeMapName varchar(64),
    @inValidateRuleID int = null,
    @outErrorCode int output,
    @outErrorMessage varchar(1000) output
as
set nocount on

declare @theRuleCounter int
declare @theTotalDetailsCount int
declare @theDetailCounter int
declare @theInsertedDetailsCount int
declare @theNetParenthesisCount int
declare @theDidNetParenthesisCountEverGoNegative bit
declare @theSourceXmlInterfaceID int

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

-- Constant for the system type of the upload
declare @DOCUMENTS_XML_SYSTEM_DATA_TYPE_NAME varchar(64)

select
    @outErrorCode = 0,
    @outErrorMessage = null,
    @theRuleCounter = 1,
    @theTotalDetailsCount = 0,
    @theDetailCounter = 1,
    @theSourceXmlInterfaceID = -1,
    @theDidNetParenthesisCountEverGoNegative = 0,
    @UNKNOWN_SQL_ERROR = 1,    -- Not an ErrorCode.ECPriKey
    @MISSING_CLOSING_PARENTHESIS = 8500,
    @MISSING_OPENING_PARENTHESIS = 8501,
    @NEGATIVE_PARENTHESIS_COUNT = 8502,
    @inValidateRuleID = isnull( @inValidateRuleID, 0 ),
    @DOCUMENTS_XML_SYSTEM_DATA_TYPE_NAME = 'Documents'

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 @theDocumentID int

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

declare @theRuleDescription varchar(128)
declare @theXmlSystemDataTypeMapName varchar(64)

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 @theDocumentAutoUploadRuleDetailFID int
declare @theRuleSaysToUpload bit
declare @theCTE_Name nvarchar(64)

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

declare @theRuleDetails table
(
    DetailID int identity (1, 1) not null,
    SortOrder int not null,
    DocumentAutoUploadRuleDetailFID int not null,
    DocumentAutoUploadRuleItemFID int not null
)

declare @theUploadableSystems table
(
    XmlSystemDataTypeMapName varchar(64) not null
)

if( @inValidateRuleID > 0 )
begin
    -- We are just testing the SQL so we don't need live data.
    select
        @theXmlInterfaceID = XmlInterface.XmlInterfaceID,
        @inOrderID = -1,
        @theDocumentID = -1,
        @theBranchPriKey = -1,
        @theShipmentTypeID = -1,
        @theMoveTypeGroupID = -1,
        @theHaulModeID = -1,
        @theVanLineID = -1
    from DocumentAutoUploadRule
    inner join XmlInterface on XmlInterface.XmlInterfaceID = DocumentAutoUploadRule.XmlInterfaceFID
    where DocumentAutoUploadRuleID = @inValidateRuleID
end
else
begin
    -- Get the key info off of the order.
    select
        @theBranchPriKey = Orders.BranchPriKey,
        @theShipmentTypeID = Orders.ShipmentTypeFID,
        @theHaulModeID = OrdersExtended.HaulModeFID,
        @theVanLineID = Orders.VanLineFID,
        @theMoveTypeGroupID = MoveType.MTGroupPriKey,
        @theDocumentID = DocumentType.DocumentTypeID
    from Orders
    left outer join OrdersExtended on OrdersExtended.OrderFID = Orders.PriKey
    left outer join MoveType on MoveType.PriKey = Orders.MoveType
    left outer join DocumentType on DocumentType.[Type] = @inDocumentType
    where Orders.PriKey = @inOrderID

    if( @inSourceSystemDataTypeMapName is not null )
    begin
        select
            @theSourceXmlInterfaceID = XmlSystem.XmlInterfaceFID
        from XmlSystemDataTypeMap
        inner join XmlSystem on XmlSystem.XmlSystemID = XmlSystemDataTypeMap.XmlSystemFID
        where XmlSystemDataTypeMap.[Name] = @inSourceSystemDataTypeMapName
    end
end

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

    if( @inValidateRuleID > 0 )
    begin
        -- We are validating a specific rule so that is the only rule we will test.
        insert into @thePossibleRules
        (
            DocumentAutoUploadRuleID,
            RuleDescription,
            XmlSystemDataTypeMapName
        )
        select top 1
            DocumentAutoUploadRuleID,
            RuleDescription,
            XmlSystemDataTypeMap.[Name]
        from DocumentAutoUploadRule
        -- Find the first XmlSystemDataType.[Name] that is linked to the XmlInterfaceFID on this rule.
        inner join XmlSystem on XmlSystem.XmlInterfaceFID = DocumentAutoUploadRule.XmlInterfaceFID
        inner join XmlSystemDataTypeMap on XmlSystemDataTypeMap.XmlSystemFID = XmlSystem.XmlSystemID
        inner join XmlSystemDataType on XmlSystemDataType.XmlSystemDataTypeID = XmlSystemDataTypeMap.XmlSystemDataTypeFID and
            XmlSystemDataType.[Name] = @DOCUMENTS_XML_SYSTEM_DATA_TYPE_NAME
        where DocumentAutoUploadRuleID = @inValidateRuleID

        set @theTotalRulesCount = @@ROWCOUNT

        if( @theTotalRulesCount = 0 )
        begin
            -- There is no configured XmlSystemDataTypeMapName that is set for this XmlInterface of type
            -- @DOCUMENTS_XML_SYSTEM_DATA_TYPE_NAME so insert the rule will an appropriate XmlSystemDataTypeMapName.
            insert into @thePossibleRules
            (
                DocumentAutoUploadRuleID,
                RuleDescription,
                XmlSystemDataTypeMapName
            )
            select
                DocumentAutoUploadRuleID,
                RuleDescription,
                'ThereIsNoDocumentDataTypeSystemConfigureForThisXmlInterface'
            from DocumentAutoUploadRule
            where DocumentAutoUploadRuleID = @inValidateRuleID

            set @theTotalRulesCount = @@ROWCOUNT
        end
    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
        (
            DocumentAutoUploadRuleID,
            RuleDescription,
            XmlSystemDataTypeMapName
        )
        select distinct
            DocumentAutoUploadRule.DocumentAutoUploadRuleID,
            DocumentAutoUploadRule.RuleDescription,
            XmlSystemDataTypeMapName = XmlSystemDataTypeMap.[Name]
        from DocumentAutoUploadRule
        inner join XmlSystem on XmlSystem.XmlInterfaceFID = DocumentAutoUploadRule.XmlInterfaceFID
        inner join XmlSystemDataTypeMap on XmlSystemDataTypeMap.XmlSystemFID = XmlSystem.XmlSystemID
        inner join XmlSystemDataType on XmlSystemDataType.XmlSystemDataTypeID = XmlSystemDataTypeMap.XmlSystemDataTypeFID
        left outer join XmlSystemOptions on XmlSystemOptions.XmlSystemFID = XmlSystem.XmlSystemID and
            XmlSystemOptions.[Name] = 'AutoUploadDocumentsDisabled'
        -- @theSourceXmlInterfaceID will never be NULL if we get here!!
        where DocumentAutoUploadRule.XmlInterfaceFID != @theSourceXmlInterfaceID and
            DocumentAutoUploadRule.Inactive = 0 and
            DocumentAutoUploadRule.Validated = 1 and
            XmlSystemDataType.[Name] = @DOCUMENTS_XML_SYSTEM_DATA_TYPE_NAME and
            isnull( XmlSystemOptions.[Value], 'false' ) != 'true'

        set @theTotalRulesCount = @@ROWCOUNT
    end

    while( @theRuleCounter <= @theTotalRulesCount )
    begin
        select
            @theCurrentRuleID = DocumentAutoUploadRuleID,
            @theRuleDescription = RuleDescription,
            @theXmlSystemDataTypeMapName = XmlSystemDataTypeMapName
        from @thePossibleRules
        where RuleID = @theRuleCounter

        insert into @theRuleDetails
        (
            SortOrder,
            DocumentAutoUploadRuleDetailFID,
            DocumentAutoUploadRuleItemFID
        )
        select
            SortOrder,
            DocumentAutoUploadRuleDetailID,
            DocumentAutoUploadRuleItemFID
        from DocumentAutoUploadRuleDetail
        where DocumentAutoUploadRuleFID = @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
                @theDocumentAutoUploadRuleDetailFID = theRuleDetails.DocumentAutoUploadRuleDetailFID,
                @theTableName = DocumentAutoUploadRuleItem.TableName,
                @thePriKeyName = DocumentAutoUploadRuleItem.PriKeyName,
                @theDetailKeyTableName = DocumentAutoUploadRuleItem.DetailKeyTableName,
                @theDetailKeyColumnName = DocumentAutoUploadRuleItem.DetailKeyColumnName,
                @theItemSqlText = DocumentAutoUploadRuleItem.SqlText,
                @theParenthesisCount = DocumentAutoUploadRuleItem.ParenthesisCount,
                @theIsBooleanCombiner = DocumentAutoUploadRuleItem.IsBooleanCombiner
            from @theRuleDetails as theRuleDetails
            inner join DocumentAutoUploadRuleItem on DocumentAutoUploadRuleItem.DocumentAutoUploadRuleItemID = theRuleDetails.DocumentAutoUploadRuleItemFID
            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( @theDocumentAutoUploadRuleDetailFID ) ) ), N'_CTE' )

                set @theSqlCTE = @theSqlCTE + N',
' + @theCTE_Name + N' ( ' + @thePriKeyName + N' )
as
(
    select ' + @thePriKeyName + N' = ' + @theDetailKeyColumnName + N'
    from ' + @theDetailKeyTableName + N'
    where DocumentAutoUploadRuleDetailFID = ' + ltrim( rtrim( str( @theDocumentAutoUploadRuleDetailFID ) ) ) + 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( @theDocumentID, -1 ) ) ) )
    when N'HaulMode' then ltrim( rtrim( str( isnull( @theHaulModeID, -1 ) ) ) )
    when N'VanLine' then ltrim( rtrim( str( isnull( @theVanLineID, -1 ) ) ) )
    else N'-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,
            @theRuleSaysToUpload = 0

        set xact_abort on
        begin try

            exec sp_executesql @theSqlText, N'@ItExists bit output', @theRuleSaysToUpload output
            if( isnull( @theRuleSaysToUpload, 0 ) = 1 )
            begin
                -- Found one!!  We found a rule that says we need to upload this document type for this order.
                if( not exists( select top 1 1 from @theUploadableSystems where XmlSystemDataTypeMapName = @theXmlSystemDataTypeMapName ) )
                begin
                    insert into @theUploadableSystems( XmlSystemDataTypeMapName )
                    values ( @theXmlSystemDataTypeMapName )
                end
            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

if( @outErrorCode > 0 and isnull( @outErrorMessage, '' ) = '' )
begin
    select
        @outErrorMessage = ErrorCode.[Description]
    from ErrorCode
    where ErrorCode.ECPriKey = @outErrorCode
end

-- Return the names of document libraries we need to upload to (if any)
select * from @theUploadableSystems
GO
GRANT EXECUTE ON  [dbo].[DocumentAutoUploadApplyRules] TO [MssExec]
GO