/****** 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;
   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 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.SysUserFID = @DriverID
                  Where CommissionedDetail.VendorID = SysUserLaborTypeMap.VendorID
                    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.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 SysUserLaborTypeMap.SysUserFID = @DriverID
                  Where CommissionedDetail.VendorID = SysUserLaborTypeMap.VendorID
               ) as Tbl1
               WHERE OrderNumber = @OrderNo AND TransactionDate IS NOT NULL
FOR XML RAW;

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