-- returns the best dimension value for the account transaction.  Returns one row if one was found, or zero rows
-- if not applicable or nothing found
CREATE function dbo.BcDetermineSysUserRevenueDimensionValue
(
    @accountTransactionsId int
)
returns table as return
(

    with ApplicableAccountTransaction as
    (
        --behaves as a filter, ensuring we only produce a dimension value here if we really want one.
        select
            AcctTransactions.ATPriKey,
            AcctTransactions.Source,
            AcctTransactions.SourceRecord,
            AcctTransactions.OrdPriKey
        from AcctTransactions where
        ATPriKey = @accountTransactionsId and
        AcctTransactions.Source in ( 'BillingMinorItem', 'BillingMinorDist' )
    )
    , RelevantCommissions as
    (
        select CommissionedDetail.Amount,
            IsDriver = IsDriver.Val,
            CommissionedDetail.CDPriKey,
            SysUserId = CommissionedDetail.SysUserPriKey,
            ApplicableAccountTransaction.ATPriKey

        from ApplicableAccountTransaction
        inner join CommissionedDetail
            on
                ( ApplicableAccountTransaction.Source = 'BillingMinorItem' and ApplicableAccountTransaction.SourceRecord = CommissionedDetail.BMinPriKey )
            or
                ( ApplicableAccountTransaction.Source = 'BillingMinorDist' and ApplicableAccountTransaction.SourceRecord = CommissionedDetail.BMinDistPriKey )
        inner join LaborType on CommissionedDetail.LaborTypeFid = LaborType.PriKey
        inner join SysUser on CommissionedDetail.SysUserPriKey = SysUser.SysUserId
        cross apply
        (
            select Val = convert(bit, isnull( SysUser.DTPriKey, 0 ) )
        ) IsDriver
        where ATPriKey = @accountTransactionsId and
        (
            LaborType.LaborType = 'Sales' or
            IsDriver.Val = 1
        )
    )
    --BCTD:
        --Incorporate a layer of grouping
            --group by SysUser and IsLongDistance
            -- Sum the "amount" (bipolar)
            -- Use the greatest absolute value amount when determining the match.

    , CommissionedPersonOrOrderSalesperson as
    (

        --Use the best commissioned person
        select top 1 SysUserId, FromCommission = 1
        from RelevantCommissions
        order by IsDriver desc, Amount desc, CDPriKey asc
        union all
        --if no commissioned person then use the salesperson from the order.
        select Orders.SalesPerson, FromCommission = 0
        from ApplicableAccountTransaction
        inner join Orders on ApplicableAccountTransaction.OrdPriKey = Orders.PriKey
        where ATPriKey = @accountTransactionsId
    )
    , TargetUser as
    (
        select top 1 SysUserId from CommissionedPersonOrOrderSalesperson order by FromCommission desc
    )
    select
        BcId = BcDimensionValue.BcId
        from TargetUser
        inner join BcSysUserDimensionValueMap on TargetUser.SysUserId = BcSysUserDimensionValueMap.SysUserFid
        inner join BcDimensionValue on BcSysUserDimensionValueMap.BcDimensionValueFid = BcDimensionValue.BcDimensionValueId
)
GO
GRANT SELECT ON  [dbo].[BcDetermineSysUserRevenueDimensionValue] TO [MssExec]
GO