/****** Object:  StoredProcedure [rdld].[[GetStatementAndReserveForDrivers]]    Script Date: 12/21/22 ******/
-- =============================================
-- Author:     Jeff Spindler
-- Date: 12/21/22
-- Description:   Gets statement and reserve finance info for all drivers
-- =============================================

CREATE PROCEDURE [rdld].[GetStatementAndReserveForDrivers]
  @AcctNumbersCSV AS VARCHAR(MAX)
AS
BEGIN
   SET NOCOUNT ON;
   SET DEADLOCK_PRIORITY LOW;

   DECLARE @AcctNumbers AS dbo.Nvarchar32List;
   INSERT INTO             @AcctNumbers(Item)
   SELECT tuple
   FROM
        rdld.split_string(@AcctNumbersCSV, ',');

   declare @numAcctNumbers int = (select count(*) from @AcctNumbers);

   SELECT
          VENDORID AS AccountNumber,
          ISNULL(SUM(CASE
                         WHEN DOCTYPE = 5
                         THEN -1
                         ELSE 1
                     END * CURTRXAM), 0) AS CurrentBalance
   FROM
        PM20000_Synonym AS PM20000
   WHERE    (VENDORID IN
   (
       SELECT * FROM @AcctNumbers
   ))
            AND (DOCDATE <= GETDATE())
   GROUP BY VENDORID
   UNION ALL
   SELECT
          GL00105.ACTNUMST AS AccountNumber,
          ISNULL(SUM(GL20000.CRDTAMNT - GL20000.DEBITAMT), 0) AS CurrentBalance
   FROM
        GL20000_Synonym AS GL20000
        LEFT OUTER JOIN GL00105_Synonym AS GL00105 ON GL00105.ACTINDX = GL20000.ACTINDX
   WHERE  (GL00105.ACTNUMST IN
   (
       SELECT * FROM @AcctNumbers
   ))
          AND (GL20000.DOCDATE <= GETDATE())
   GROUP BY GL00105.ACTNUMST
   FOR XML RAW;

   return @numAcctNumbers;
END;
GO
GRANT EXECUTE ON  [rdld].[GetStatementAndReserveForDrivers] TO [MssExec]
GO