/*
*    Description: Processes the revenue transactions for the specified order which are using the specified BATSession
*    identifier.  This procedure mainly summarizes the BATProcess transactions by ExpenseType and then updates all
*    such BATProcess records of the same ExpenseType for this session with that Xgl identifier.  Any errors are
*    logged via spBATRecordError or spStorageRecordError if @inIsStorageProcessing = 1.
*
*    Input @params:
*    ===========================================================================================================================
*    @inOrderID: The primary key for the order of interest.  If not specified, then the OrderID on the BATProcess records are
*        used.  AdvanceBATPayables does not pass in OrderID.
*    @inBatSessionID: If specified, this is a BatSession identifier used to locate our BATProcess records. This will be ignored
*        if @inBatProcessPriKey is > 0.
*    @inErrorLoggingSessionId: A BAT or Storage session identifier used for any error logging.  This could be the blank session identifier.
*    @inBatProcessPriKey: If > 0, is the ONE BATProcess record to be processed and @inBatSessionID is ignored.
*    @inIsStorageProcessing: If 1, then any errors are reported via spStorageRecordError.  Otherwise, errors are reported
*        via spBATRecordError.
*
*    For non-storage errors (spBATRecordError), these can be set (spBATGenerateStatementTransactions sets these).  These values
*    are just passed on to spBATRecordError if specified. Otherwise, the defaults shown are used ONLY IF @inSourceTable is not
*        specified:
*        @inSourceTable: defaults to 'Order',
*        @inSourceID: defaults to the @inOrderID or BATProcess.OrdPriKey value.
*        @inObjectTable: defaults to 'Order',
*        @inObjectID: defaults to the @inOrderID or BATProcess.OrdPriKey value.
*    Also, if @inSourceTable/@inObjectTable ends up being 'Order', then the @inSourceID/@inObjectID will be the appropriate OrderId.
*    Also, if @inSourceTable is 'Advance', then the @inSourceID will be the BATProcess.SourceRecrd (which is the AdvanceID).
*    
*    Returned parameters:
*    ===========================================================================================================================
*    None.  But the stored proc itself return values are:
*    0 means no error
*    1 means had one or more errors.
*
*    Any detected errors are logged to the BATErrorLog via spBATRecordError or to the StorageErrorLog via spStorageRecordError
*    if @inIsStorageProcessing = 1.
*/
CREATE procedure [dbo].[spBATGetXLedgerXgls]
    @inOrderID int,
    @inBatSessionID varchar(50),
    @inErrorLoggingSessionId varchar(50),
    @inBatProcessPriKey int = null,
    @inIsStorageProcessing bit = null,
    @inSourceTable varchar(50) = null,
    @inSourceID int = null,
    @inAllowNullsAuthorityMoveTypeCommodity bit = 0,
    @inObjectTable varchar(20) = null,
    @inObjectID int = null
as

set nocount on

-- initialize output parameters
declare @theReturnValue int = 0
declare @theHadError bit
declare @theXGlId int
declare @theExpenseTypeRowCount int
declare @theCounter int
declare @theOrderId int
declare @theExpenseTypeName varchar(16)
declare @theSourceTable varchar(30) = @inSourceTable
declare @theSourceID int
declare @theObjectTable varchar(20) = @inObjectTable
declare @theObjectID int

-- RevTransactions and Storage use the defaults while Advances, RevStatements pass in values.
if( @inSourceTable is null )
begin
    -- @theSourceID and @theObjectID will get set to the correct OrderId.
    select
        @theSourceTable = 'Order',
        @theObjectTable = 'Order'
end

select
    @theHadError = 0,
    @theXGlId = null,
    @inBatProcessPriKey = case
        -- Make sure no one passes in 0 or a negative value for @inBatProcessPriKey!!
        when isnull( @inBatProcessPriKey, 0 ) > 0 then @inBatProcessPriKey
        -- Treat zero and negative values as 'not specified'.
        else null
    end

declare @theBATProcessInfo table
(
    BATProcessID int,
    OrderId int,
    TransactionType varchar(30),
    GlArAp varchar(2),
    Source varchar(50),
    SourceRecord int,
    ExpenseTypeName varchar(16),
    LaborTypeFid int
)

declare @theOrderExpenseTypesInUse table
(
    ID int identity( 1, 1 ) not null,
    OrderId int,
    ExpenseTypeName varchar(16),
    -- SourceId only used if @inSourceTable is 'Advance'
    SourceId int
)

insert into @theBATProcessInfo
(
    BATProcessID,
    OrderId,
    TransactionType,
    Source,
    SourceRecord

    -- LaborTypeFid int
    -- We'll populate LaborTypeFid from the Source and SourceRecord in
    -- subsequent passes for the various possible Source tables since
    -- each Source needs different joins to determine the LaborTypeFID.
)
-- All BATProcess records for a specific order (revenue transactions).
-- Sometimes, BATProcess.OrdPriKey is a negative number that points to a
-- StatementDetail.SDPriKey.  Search for "WTF" in spBATGenerateStatementTransactions.
select
    BATProcessID = BATProcess.BATProcessPriKey,
    OrderId = case
        when BATProcess.OrdPriKey > 0 then BATProcess.OrdPriKey
        else null
    end,
    TransactionType = BATProcess.TransactionType,
    Source = BATProcess.Source,
    SourceRecord = BATProcess.SourceRecord
from BATProcess
left outer join Xgl on Xgl.XglId = BATProcess.XglFid
where @inBatProcessPriKey is null and
    BATProcess.OrdPriKey = @inOrderID and
    Xgl.XglId is null and
    BATProcess.BATSessionID = @inBatSessionID
union all
-- One specific BATProcess record (storage processing)
select
    BATProcessID = BATProcess.BATProcessPriKey,
    OrderId = case
        when BATProcess.OrdPriKey > 0 then BATProcess.OrdPriKey
        else null
    end,
    TransactionType = BATProcess.TransactionType,
    Source = BATProcess.Source,
    SourceRecord = BATProcess.SourceRecord
from BATProcess
left outer join Xgl on Xgl.XglId = BATProcess.XglFid
where @inBatProcessPriKey is not null and
    Xgl.XglId is null and
    BATProcess.BATProcessPriKey = @inBatProcessPriKey
union all
-- All BATProcess records for all orders (Advances, Statement transactions)
select
    BATProcessID = BATProcess.BATProcessPriKey,
    OrderId = case
        when BATProcess.OrdPriKey > 0 then BATProcess.OrdPriKey
        else null
    end,
    TransactionType = BATProcess.TransactionType,
    Source = BATProcess.Source,
    SourceRecord = BATProcess.SourceRecord
from BATProcess
left outer join Xgl on Xgl.XglId = BATProcess.XglFid
where @inBatProcessPriKey is null and
    @inOrderID is null and
    Xgl.XglId is null and
    BATProcess.BATSessionID = @inBatSessionID

-- Update the GlArAp and ExpenseTypeName
update @theBATProcessInfo set
    GlArAp = case TransactionType
        when 'A/P Credit Memo' then 'AP'
        when 'A/P Invoice' then 'AP'
        when 'A/R Cash Receipt' then 'AR'
        when 'A/R Credit Memo' then 'AR'
        when 'A/R Debit Memo' then 'AR'
        when 'A/R Sales / Invoice' then 'AR'
        when 'Journal Entry' then 'GL'
        else '??'    -- We shouldn't get here
    end,
    ExpenseTypeName = case Source
        when 'ClaimItemDueFrom' then 'Claims'
        when 'ClaimHandlingRevenue' then 'Claims'
        when 'ClaimSettlement' then 'Claims'
        when 'ClaimLiabilityItem' then 'Claims'
        when 'ThirdParty' then 'ThirdParty'
        else ''
    end
from @theBATProcessInfo

-- Set Expense type for commissioned details based upon the labor type associated to the commission.
update @theBATProcessInfo set
    ExpenseTypeName = XLedgerExpenseType.[TypeName]
from @theBATProcessInfo as theBATProcessInfo
inner join CommissionedDetail on CommissionedDetail.CDPriKey = theBATProcessInfo.SourceRecord
inner join XLedgerLaborTypeToExpenseTypeMap on XLedgerLaborTypeToExpenseTypeMap.LaborTypeFID = CommissionedDetail.LaborTypeFID
inner join XLedgerExpenseType on XLedgerExpenseType.XLedgerExpenseTypeID = XLedgerLaborTypeToExpenseTypeMap.XLedgerExpenseTypeFID
where theBATProcessInfo.ExpenseTypeName = '' and
    theBATProcessInfo.Source = 'CommissionedDetail' and
    theBATProcessInfo.GlArAp = 'AP'

-- Set Expense type for split commissions details based upon the labor type associated to the split commission.
update @theBATProcessInfo set
    ExpenseTypeName = XLedgerExpenseType.[TypeName]
from @theBATProcessInfo as theBATProcessInfo
inner join SplitCommissions on SplitCommissions.SCPriKey = theBATProcessInfo.SourceRecord
inner join XLedgerLaborTypeToExpenseTypeMap on XLedgerLaborTypeToExpenseTypeMap.LaborTypeFID = SplitCommissions.LaborTypeFID
inner join XLedgerExpenseType on XLedgerExpenseType.XLedgerExpenseTypeID = XLedgerLaborTypeToExpenseTypeMap.XLedgerExpenseTypeFID
where theBATProcessInfo.ExpenseTypeName = '' and
    theBATProcessInfo.Source = 'SplitCommissions' and
    theBATProcessInfo.GlArAp = 'AP'

-- Set Expense type for advances details based upon the default labor type associated to the advancee.
update @theBATProcessInfo set
    ExpenseTypeName = XLedgerExpenseType.[TypeName]
from @theBATProcessInfo as theBATProcessInfo
inner join Advance on Advance.PriKey = theBATProcessInfo.SourceRecord
inner join SysUser on SysUser.SysUserID = Advance.AdvanceToSysUserFID
inner join SysUserLaborTypeMap on SysUserLaborTypeMap.SysUserFID = Sysuser.SysUserID and SysUserLaborTypeMap.[Default] = 1
inner join XLedgerLaborTypeToExpenseTypeMap on XLedgerLaborTypeToExpenseTypeMap.LaborTypeFID = SysUserLaborTypeMap.LaborTypeFID
inner join XLedgerExpenseType on XLedgerExpenseType.XLedgerExpenseTypeID = XLedgerLaborTypeToExpenseTypeMap.XLedgerExpenseTypeFID
where theBATProcessInfo.ExpenseTypeName = '' and
    theBATProcessInfo.Source = 'Advance'

-- XLTD: Might need more clarification/modifications once we get further into this, like for PM, etc.
-- Maybe we might need to log an error if there is some new Source type(s) we don't know about that we
-- haven't handled with the above code.

-- For StatementPayables, I don't see a way to link Vendors to a specific labor type.  The same Vendor can
-- be assigned to multiple labor types for multiple SysUsers so there is no way to know which ONE is the
-- SysUser for a specific vendor on the StatementPayable.  If we are trying to make sure we handle all
-- Source types, we might need to do this:
--    update @theBATProcessInfo set
--        ExpenseTypeName = 'None'
--    from @theBATProcessInfo as theBATProcessInfo
--    where theBATProcessInfo.ExpenseTypeName = '' and
--        theBATProcessInfo.Source = 'StatementPayable'
--
-- and then, log an error for each Source that still has an ExpenseTypeName of ''.

-- For whatever is left, set any other remaining items to None.
update @theBATProcessInfo set
    ExpenseTypeName = 'None'
from @theBATProcessInfo as theBATProcessInfo
where theBATProcessInfo.ExpenseTypeName = ''

-- See what distinct OrderId / ExpenseTypeName(s) were actually used by the complete
-- list of transactions we were asked to update.  It's possible that it's only 'None'
-- that we have to deal with.  For advances, since we need the AdvanceID for error
-- logging, this is pretty much every single record in @theBATProcessInfo.
insert into @theOrderExpenseTypesInUse
(
    OrderId,
    ExpenseTypeName,
    SourceId
)
select distinct
    OrderId = theBATProcessInfo.OrderId,
    ExpenseTypeName = theBATProcessInfo.ExpenseTypeName,
    SourceId = case
        when @theSourceTable = 'Advance' then theBATProcessInfo.SourceRecord
        else null
    end
from @theBATProcessInfo as theBATProcessInfo

set @theExpenseTypeRowCount = @@rowcount
set @theCounter = 1

while( @theCounter <= @theExpenseTypeRowCount )
begin
    select
        @theOrderId = theOrderExpenseTypesInUse.OrderId,
        @theExpenseTypeName = theOrderExpenseTypesInUse.ExpenseTypeName,
        @theSourceID = case
            when @theSourceTable = 'Advance' then theOrderExpenseTypesInUse.SourceId
            when @theSourceTable = 'Order' then theOrderExpenseTypesInUse.OrderId
            else @inSourceID
        end,
        @theObjectID = case
            when @theObjectTable = 'Order' then theOrderExpenseTypesInUse.OrderId
            else @inObjectID
        end
    from @theOrderExpenseTypesInUse as theOrderExpenseTypesInUse
    where theOrderExpenseTypesInUse.ID = @theCounter

    exec spBATGetSingleXLedgerXglId
        @inOrderID = @theOrderId,
        @inSessionID = @inErrorLoggingSessionId,
        @inSourceTable = @theSourceTable,
        @inSourceID = @theSourceID,
        @inObjectTable = @theObjectTable,
        @inObjectID = @theObjectID,
        @inXLedgerExpenseType = @theExpenseTypeName,
        @inIsStorageProcessing = @inIsStorageProcessing,
        @inAllowNullsAuthorityMoveTypeCommodity = @inAllowNullsAuthorityMoveTypeCommodity,
        @outXGlId = @theXGlId out,
        @outHadError = @theHadError out

    if( @theHadError = 0 )
    begin
        update BATProcess set
            XglFid = @theXGlId
        from @theBATProcessInfo as theBATProcessInfo
        inner join BATProcess on BATProcess.BATProcessPriKey = theBATProcessInfo.BATProcessID
        where theBATProcessInfo.ExpenseTypeName = @theExpenseTypeName and
            (
                ( @theOrderId is not null and theBATProcessInfo.OrderId = @theOrderId ) or
                ( @theOrderId is null and theBATProcessInfo.OrderId is null )
            ) and
            (
                ( @theSourceTable = 'Advance' and theBATProcessInfo.SourceRecord = @theSourceID ) or
                ( @theSourceTable != 'Advance' )
            )
    end
    else
    begin
        set @theReturnValue = 1
    end
    
    set @theCounter = @theCounter + 1
end

return @theReturnValue
GO
GRANT EXECUTE ON  [dbo].[spBATGetXLedgerXgls] TO [MssExec]
GO