/****** Object:  StoredProcedure [rdld].[GetClaimDetailsForDriver]  ******/
-- =============================================
-- Author:     Jeff Spindler
-- Date: 11/22
-- Description:   Gets the  claim details
-- =============================================
CREATE PROCEDURE [rdld].[GetClaimDetailsForDriver]
  @DriverId int
AS
BEGIN
   SET NOCOUNT ON;
   SET DEADLOCK_PRIORITY LOW;
   SELECT
      orders.Orderno as OrderNumber,
      Claim.ClaimID,
      ClaimItem.Description,
      ClaimDamageType.Type AS ClaimItemType,
      ClaimItem.TagNumber, ClaimItem.ClaimAmount,
      0 AS LiabilityAmount,
      0 AS ChargebackAmount,
      ClaimItem.ClaimAmount AS DisplayAmount
   FROM
        ClaimItem
        INNER JOIN ClaimDamageType ON ClaimItem.ClaimDamageTypeFID = ClaimDamageType.ClaimDamageTypeID
        INNER JOIN Claim ON ClaimItem.ClaimFID = Claim.ClaimID
        inner join Orders on orders.prikey = claim.ordersfid
        INNER JOIN TripOrd ON Orders.PriKey = TripOrd.OrdPriKey
        INNER JOIN Trips ON TripOrd.TripPriKey = Trips.PriKey
        INNER JOIN Sysuser ON Trips.DriverEmpNo = Sysuser.SysUserID
        INNER JOIN ClaimStatus ON Claim.ClaimStatusFID = ClaimStatus.ClaimStatusID
   WHERE    (ClaimStatus.Status like '%Open%')
   AND (Sysuser.SysUserID = @DriverID)

   UNION ALL
   SELECT
      orders.Orderno as OrderNumber,
      Claim_1.ClaimID,
      ClaimItem_1.Description,
      ClaimDamageType_1.Type,
      ClaimItem_1.TagNumber,
      ClaimItem_1.ClaimAmount,
      SUM(ClaimLiabilityItem.LiabilityAmount) AS LiabilityAmount,
      SUM(ClaimLiabilityItem.PayableAmount) AS ChargebackAmount,
      CASE
            WHEN SUM(ClaimLiabilityItem.LiabilityAmount) > SUM(ClaimLiabilityItem.PayableAmount)
            THEN SUM(ClaimLiabilityItem.LiabilityAmount)
            ELSE SUM(ClaimLiabilityItem.PayableAmount)
      END AS DisplayAmount
   FROM
        ClaimItem AS ClaimItem_1
        INNER JOIN ClaimLiabilityItem ON ClaimItem_1.ClaimItemID = ClaimLiabilityItem.ClaimItemFID
        INNER JOIN ClaimDamageType AS ClaimDamageType_1 ON ClaimItem_1.ClaimDamageTypeFID = ClaimDamageType_1.ClaimDamageTypeID
        INNER JOIN ClaimLiability ON ClaimLiabilityItem.ClaimLiabilityFID = ClaimLiability.ClaimLiabilityID
        INNER JOIN Sysuser ON ClaimLiability.LiablePersonSysUserFID = Sysuser.SysUserID
        INNER JOIN Claim AS Claim_1 ON ClaimItem_1.ClaimFID = Claim_1.ClaimID
        inner join Orders on orders.prikey = claim_1.ordersfid
        INNER JOIN ClaimStatus as ClaimStatus_1 ON Claim_1.ClaimStatusFID = ClaimStatus_1.ClaimStatusID
   WHERE ClaimStatus_1.[Status] not like '%Open%'
      AND (Claim_1.ClosedDate > DATEADD(yy, -1, GETDATE()))
      and orders.orderno is not null
      AND (Sysuser.SysUserID = @DriverID)
   GROUP BY Claim_1.ClaimID, Sysuser.DRVNO, ClaimItem_1.Description, ClaimDamageType_1.Type, ClaimItem_1.TagNumber, ClaimItem_1.ClaimAmount, orders.orderno

   for xml raw;
END;
GO
GRANT EXECUTE ON  [rdld].[GetClaimDetailsForDriver] TO [MssExec]
GO