Created by Nick Reilingh last modified 2020-02-06
DECLARE open_batch_users CURSOR LOCAL FAST_FORWARD
FOR
SELECT
    COUNT(*) AS num_batches,
    RTRIM(b.[owner]) AS [owner],
    MIN(b.create_dt) AS create_dt,
    ISNULL(u.fname, '') AS fname,
    ISNULL(u.lname, '') AS lname,
    ISNULL(u.email_address, '') AS email_address,
    ISNULL(u.location, '') AS location
  FROM dbo.T_BATCH b
    JOIN dbo.T_METUSER u
      ON b.[owner] = u.userid
        AND u.location <> 'System Resource'
  WHERE b.[status] NOT IN ('C', 'P')
    AND DATEDIFF(day, 0, b.create_dt) < DATEDIFF(day, 0, GETDATE())
    AND u.email_address IS NOT NULL
    AND u.inactive <> 'Y'
  GROUP BY b.owner, u.fname, u.lname, u.email_address, u.location;

DECLARE @payload nvarchar(max),
        @batchreport nvarchar(max);

OPEN open_batch_users;
DECLARE @num_batches int,
        @owner varchar(8),
        @create_dt datetime,
        @fname varchar(30),
        @lname varchar(30),
        @email_address varchar(80),
        @location varchar(30);

WHILE 1 = 1
BEGIN;
  FETCH NEXT FROM open_batch_users INTO @num_batches,
                                        @owner,
                                        @create_dt,
                                        @fname,
                                        @lname,
                                        @email_address,
                                        @location;
  IF @@FETCH_STATUS <> 0
    BREAK;

  -- Part time student employees should not be emailed about this. Instead, send a slack notification to ?box_office.
  IF @location = 'Box Office'
  BEGIN;
    SET @payload = @fname + ' ' + @lname + ' (' + @owner + ') has an unclosed batch from yesterday.';
    EXEC dbo.LP_NOTIFY_SLACK @payload, '#box_office';
  END;
  ELSE
  BEGIN;
    SET @batchreport = '<table>
<thead><tr><th>batch_no</th><th>batch_type</th><th>status</th><th>owner</th><th>create_dt</th><th>open_loc</th></tr></thead>
<tbody>
  ' + CONVERT(nvarchar(max), (
        SELECT
            b.batch_no AS td,
            bt.description AS td,
            b.[status] AS td,
            b.[owner] AS td,
            b.create_dt AS td,
            ISNULL(b.open_loc, '') AS td
          FROM dbo.T_BATCH b
            JOIN dbo.TR_BATCH_TYPE bt
              ON b.batch_type = bt.id
          WHERE b.status NOT IN ('C', 'P')
            AND b.[owner] = @owner
          FOR XML RAW ('tr'), ELEMENTS )) + '
</tbody>
</table>';

    SET @payload = '<html>
<head>
<style>
  td, th { padding: 5px; }
  tbody td, thead th { border: 1px solid black; }
  table { border-collapse: collapse; }
  thead { text-align: left; }
</style>
</head>
<body>
<p>Hello ' + @fname + ',</p>
<p>This email is to inform you that your Tessitura account (' + @owner + ') currently owns ' +
  IIF(@num_batches > 1, CONVERT(varchar, @num_batches) + ' unclosed batches', 'an unclosed batch') +
  ' since ' + CONVERT(varchar(6), @create_dt) + '.</p>
<p>Please remember to close your batch at the end of the day, or whenever you are done using it. Multiple batches on the same day are fine, but accidentally processing a card transaction in a previous day''s batch is a Bad Thing&trade; to be avoided.</p>
<p>Thank you!</p>
<p>- The Happy Tessitura Batch Closing Enforcement Robot</p>
' + @batchreport + '
<img alt="Benedict Close-the-batch" src="https://misc.fc.bard.edu/assets/batch.jpg" />
</body>
</html>';

    EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'TessituraUserNotifications',
      @recipients = @email_address,
      @subject = 'Tessitura batch left open',
      @body_format = 'HTML',
      @body = @payload;
  END;
END;

CLOSE open_batch_users;
DEALLOCATE open_batch_users;
Comments (0)

HTTPS
SSH
You can clone a snippet to your computer for local editing. Learn more.
Snippets

Tessitura Network Community Unclosed Batch Notification Email

Comments (0)
