/****** Object:  StoredProcedure [rdld].[GetClaimsForDriver]  ******/
-- =============================================
-- Author:     Jeff Spindler
-- Date: 11/22
-- Description:   Gets the claims and claim details
-- =============================================
CREATE PROCEDURE [rdld].[GetClaimsForDriver]
  @DriverId int
AS
BEGIN
   SET NOCOUNT ON;
   SET DEADLOCK_PRIORITY LOW;
   SELECT Claim.ClaimID, Orders.OrderNo AS OrderNumber, Orders.LastName,
      SUM(ClaimItem.ClaimAmount) AS ClaimAmount,
      0 AS LiabilityAmount,
      0 AS ChargebackAmount,
      SUM(ClaimItem.ClaimAmount) AS DisplayAmount,
      Adjuster.FIRSTNAME + ' ' + Adjuster.LASTNAME AS Adjuster,
      ClaimType.Type AS ClaimType,
      ClaimStatus.STATUS,
      Claim.CreatedDate AS DisplayDate,
      Claim.CreatedDate,
      Claim.ClosedDate,
      'Open Claims' AS ClaimHeader
   FROM
        Orders
        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 Claim ON Orders.PriKey = Claim.OrdersFID
        INNER JOIN ClaimItem ON Claim.ClaimID = ClaimItem.ClaimFID
        INNER JOIN Sysuser AS Adjuster ON Claim.AdjusterSysUserFID = Adjuster.SysUserID
        INNER JOIN ClaimStatus ON Claim.ClaimStatusFID = ClaimStatus.ClaimStatusID
        INNER JOIN ClaimType ON Claim.ClaimTypeFID = ClaimType.ClaimTypeID
   WHERE    (Sysuser.SysUserID = @DriverId)
            AND (ClaimStatus.Status like '%Open%')
   GROUP BY Orders.OrderNo, Orders.LastName, Adjuster.FIRSTNAME, Adjuster.LASTNAME,
            ClaimStatus.STATUS, ClaimType.Type, Claim.ClaimID, Claim.CreatedDate, Claim.ClosedDate
   UNION ALL
   SELECT Claim_1.ClaimID, Orders_1.OrderNo AS OrderNumber, Orders_1.LastName,
      0 AS 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,
      Adjuster.FIRSTNAME + ' ' + Adjuster.LASTNAME AS Adjuster,
      ClaimType_1.Type,
      ClaimStatus_1.STATUS,
      ISNULL(Claim_1.ClosedDate, Claim_1.CreatedDate) AS DisplayDate,
      Claim_1.CreatedDate,
      Claim_1.ClosedDate,
      'Settled Claims' AS ClaimHeader
      FROM
           ClaimLiabilityItem
           INNER JOIN ClaimItem AS ClaimItem_1 ON ClaimLiabilityItem.ClaimItemFID = ClaimItem_1.ClaimItemID
           INNER JOIN ClaimLiability ON ClaimLiabilityItem.ClaimLiabilityFID = ClaimLiability.ClaimLiabilityID
           INNER JOIN Claim AS Claim_1 ON ClaimLiability.ClaimFID = Claim_1.ClaimID
           INNER JOIN Orders AS Orders_1 ON Claim_1.OrdersFID = Orders_1.PriKey
           INNER JOIN Sysuser AS Sysuser_1 ON Sysuser_1.SysUserID = ClaimLiability.LiablePersonSysUserFID
           INNER JOIN ClaimStatus AS ClaimStatus_1 ON Claim_1.ClaimStatusFID = ClaimStatus_1.ClaimStatusID
           INNER JOIN ClaimType AS ClaimType_1 ON Claim_1.ClaimTypeFID = ClaimType_1.ClaimTypeID
           INNER JOIN Sysuser AS Adjuster ON Claim_1.AdjusterSysUserFID = Adjuster.SysUserID
      WHERE  (Sysuser_1.SysUserID = @DriverId)
             AND (Claim_1.ClosedDate > DATEADD(yy, -1, GETDATE()))
             AND (ClaimStatus_1.Status not like '%Open%')
      GROUP BY Orders_1.OrderNo, Orders_1.LastName, Claim_1.CreatedDate, Claim_1.ClosedDate, Adjuster.FIRSTNAME, Adjuster.LASTNAME,
               ClaimType_1.Type, ClaimStatus_1.STATUS, Claim_1.ClaimID

      for xml raw;

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