/****** Object:  StoredProcedure [rdld].[GetFinanceDetailForDriverOrder]  ******/
-- =============================================
-- Author:     Jeff Spindler
-- Date: 11/22
-- Description:   Gets the finance info for a driver's order
-- =============================================
CREATE PROCEDURE [rdld].[GetFinanceDetailForDriverOrder]
  @DriverId int,
  @OrderNo as varchar(50)
AS
BEGIN
    SET NOCOUNT ON;
    SET DEADLOCK_PRIORITY LOW;

    DECLARE @AccountNumber AS VARCHAR(50);
    SET @AccountNumber =
    (
        SELECT TOP 1 VendorID
        FROM SysUserLaborTypeMap
        INNER JOIN LaborType ON LaborType.PriKey = SysUserLaborTypeMap.LaborTypeFID AND LaborType.IsLongDistance = 1
        WHERE SysUserFID = @DriverId
        AND VendorID IS NOT NULL
        GROUP BY VendorID
        ORDER BY COUNT(*) DESC
    );

    SELECT
        FirstName,
        LastName,
        LineHaul,
        Description,
        TransactionDate,
        Commission,
        Percentage

    FROM

        (

            SELECT
                Orders.OrderNo AS OrderNumber,
                Orders.FirstName,
                Orders.LastName,
                Orders.LineHaul,
                CommissionedDetail.VendorID AS VendorID,
                DATEADD(dd, 0, DATEDIFF(dd, 0, CommissionedDetail.PostedDate)) AS TransactionDate,
                BillingMinorItem.Description,

                ISNULL(CommissionedDetail.Amount,0) AS Commission,
                ISNULL(CommissionedDetail.CommissionPerc,0) AS Percentage
            FROM
                CommissionedDetail
                INNER JOIN BillingMinorDist ON CommissionedDetail.BminDistPriKey = BillingMinorDist.BMinDistPriKey
                INNER JOIN BillingMinorItem ON BillingMinorDist.BMinPriKey = BillingMinorItem.BMinPriKey
                INNER JOIN BillingMajorItem ON BillingMinorItem.BMajPriKey = BillingMajorItem.BMajPriKey
                INNER JOIN Orders ON BillingMajorItem.OrdPriKey = Orders.PriKey
                --INNER JOIN SysUserLaborTypeMap ON CommissionedDetail.VendorID = SysUserLaborTypeMap.VendorID AND SysUserLaborTypeMap.LaborTypeFID = CommissionedDetail.LaborTypeFID
            WHERE
                Orders.OrderNo = @OrderNo
                --AND SysUserLaborTypeMap.SysUserFID = @DriverId
                AND CommissionedDetail.VendorID = @AccountNumber

            UNION ALL

            SELECT
                Orders.OrderNo AS OrderNumber,
                Orders.FirstName,
                Orders.LastName,
                Orders.LineHaul,
                CommissionedDetail.VendorID AS VendorID,
                DATEADD(dd, 0, DATEDIFF(dd, 0, CommissionedDetail.PostedDate)) AS TransactionDate,
                BillingMinorItem.Description,

                ISNULL(CommissionedDetail.CommissionAfterSplit,0) AS Commission,
                ISNULL(CommissionedDetail.CommissionPerc,0)
                    * IIF(ISNULL(CommissionedDetail.Amount,0) != 0, ISNULL(CommissionedDetail.CommissionAfterSplit,0) / ISNULL(CommissionedDetail.Amount,0), 0)
                    AS Percentage
            FROM
                CommissionedDetail
                INNER JOIN BillingMinorItem ON CommissionedDetail.BMinPriKey = BillingMinorItem.BMinPriKey
                INNER JOIN BillingMajorItem ON BillingMinorItem.BMajPriKey = BillingMajorItem.BMajPriKey
                INNER JOIN Orders ON BillingMajorItem.OrdPriKey = Orders.PriKey
                --INNER JOIN SysUserLaborTypeMap ON SysUserLaborTypeMap.VendorID = CommissionedDetail.VendorID AND SysUserLaborTypeMap.LaborTypeFID = CommissionedDetail.LaborTypeFID
            WHERE
                Orders.OrderNo = @OrderNo
                --AND SysUserLaborTypeMap.SysUserFID = @DriverId
                AND CommissionedDetail.VendorID = @AccountNumber

            UNION ALL

            SELECT
                Orders.OrderNo AS OrderNumber,
                Orders.FirstName,
                Orders.LastName,
                Orders.LineHaul,
                SplitCommissions.VendorID AS VendorID,
                DATEADD(dd, 0, DATEDIFF(dd, 0, SplitCommissions.PostedDate)) AS TransactionDate,
                BillingMinorItem.Description,

                ISNULL(SplitCommissions.Amount,0) AS Commission,
                ISNULL(CommissionedDetail.CommissionPerc,0) * ISNULL(SplitCommissions.CommissionPerc,0) / 100 AS Percentage
            FROM
                SplitCommissions
                INNER JOIN CommissionedDetail ON SplitCommissions.CDPriKey = CommissionedDetail.CDPriKey
                INNER JOIN BillingMinorItem ON CommissionedDetail.BMinPriKey = BillingMinorItem.BMinPriKey
                INNER JOIN BillingMajorItem ON BillingMinorItem.BMajPriKey = BillingMajorItem.BMajPriKey
                INNER JOIN Orders ON BillingMajorItem.OrdPriKey = Orders.PriKey
                --INNER JOIN SysUserLaborTypeMap ON SysUserLaborTypeMap.VendorID = SplitCommissions.VendorID AND SysUserLaborTypeMap.LaborTypeFID = SplitCommissions.LaborTypeFID
            WHERE
                Orders.OrderNo = @OrderNo
                --AND SysUserLaborTypeMap.SysUserFID = @DriverId
                AND SplitCommissions.VendorID = @AccountNumber

            UNION ALL

            SELECT DISTINCT
                Orders.OrderNo AS OrderNumber,
                Orders.FirstName,
                Orders.LastName,
                Orders.LineHaul,
                ThirdParty.VendorID AS VendorID,
                DATEADD(dd, 0, DATEDIFF(dd, 0, ThirdParty.PostedDate))  AS TransactionDate,
                BillingMinorItem.Description AS Description,

                ThirdParty.PayableAmount AS Commission,
                100 AS Percentage
            FROM
                ThirdParty
                INNER JOIN BillingMinorItem ON ThirdParty.BMinPriKey = BillingMinorItem.BMinPriKey
                INNER JOIN BillingMajorItem ON BillingMinorItem.BMajPriKey = BillingMajorItem.BMajPriKey
                INNER JOIN Orders ON BillingMajorItem.OrdPriKey = Orders.PriKey
                --INNER JOIN SysUserLaborTypeMap ON SysUserLaborTypeMap.VendorID = ThirdParty.VendorID
            WHERE
                Orders.OrderNo = @OrderNo
                --AND SysUserLaborTypeMap.SysUserFID = @DriverId
                AND ThirdParty.VendorID = @AccountNumber

        ) AS Tbl1
    
    WHERE
        TransactionDate IS NOT NULL

    FOR XML RAW;

END;
GO
GRANT EXECUTE ON  [rdld].[GetFinanceDetailForDriverOrder] TO [MssExec]
GO