ESP - Add Notifications to All Active Companies

/*

This script will add Notifications to all active companies

Please run in SQLAgent every 15 minutes.

Modified	Steven F Ling	2019-11-27

Modified	Steven F Ling	2023-08-14

*/

DECLARE @companyid INT

DECLARE @name		VARCHAR(100)

DECLARE @regionName	VARCHAR(100)

DECLARE @not1id INT, @not2id INT, @not3id INT, @not4id INT, @not5id	INT

--##### User Variables

--##### Please set these variables before running this script

DECLARE @updateUser VARCHAR(100) = 'AddNotificationScript'

DECLARE @messageSystemID1 INT = (SELECT id FROM mscmessagesystem WHERE name = 'Invoice_Export')

DECLARE @messageSystemID2 INT = (SELECT id FROM mscmessagesystem WHERE name = 'espOrderExport')

DECLARE @messageSystemID3 INT = (SELECT id FROM mscmessagesystem WHERE name = 'ESP Docket Export')

DECLARE @messageSystemID4 INT = (SELECT id FROM mscmessagesystem WHERE name = 'CreditNote_Export')

DECLARE @messageSystemID5 INT = (SELECT id FROM mscmessagesystem WHERE name = 'espOrderExport')

-- Where clause added to look for only Active companies

DECLARE look CURSOR FOR SELECT c.id,c.name

						FROM orgcompany c

 INNER JOIN orgaddress a ON c.billingAddressID=a.id

						LEFT Join orgNotification n on n.forCompanyID = c.id

 WHERE companystatus='Active'

						and n.id is null

						--and n.deliverbyMessageSystemID != @messageSystemID3

 ORDER BY c.id

						 

OPEN look

FETCH NEXT FROM look INTO @companyid, @name

WHILE @@FETCH_status=0

BEGIN

print ''

print '=====Starting with company and looking for Notifications: '+CONVERT(VARCHAR,@name)+'====='

print ''

--##### The notificationtype must be changed on each of the following lines to reflect the notifications being used

SELECT @not1id=(SELECT id FROM orgnotification WHERE forcompanyid=@companyid AND notificationtype ='InvoicePosted' and name ='Invoice')

SELECT @not2id=(SELECT id FROM orgnotification WHERE FORcompanyid=@companyid AND notificationtype ='OrderConfirmed')

SELECT @not3id=1 --(SELECT id FROM orgnotification WHERE FORcompanyid=@companyid AND notificationtype ='DocketImported')

SELECT @not4id=1 --(SELECT id FROM orgnotification WHERE forcompanyid=@companyid AND notificationtype ='InvoicePosted' and name ='Credit')

SELECT @not5id=(SELECT id FROM orgnotification WHERE FORcompanyid=@companyid AND notificationtype ='OrderCancelled')

--##### END notificationtype changes

prINT 'Notification adding process FOR: '+'Company ID: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name)

--Check to see if notification 1 exists the begin`

--IF @not1id IS NULL

--				 BEGIN

--				 print 'Adding Invoice Export FOR company: '+'Company ID: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name)

 

--					 INSERT INTO orgNotification (mainttime, userid, notificationtype, warninglevel, contenttype, contentname, forCompanyID, deliverbyMessageSystemID, toAddressID, toContactID, name, referenceRule) 

--					 VALUES(GETDATE(),@updateUser,'InvoicePosted',0,2,'Invoice_Export',@companyid,@messageSystemID1,NULL,NULL,'SAPInvoiceExport',null)

--				 print 'Added'

--				 END

--				 Else

--				 print 'Already exists: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name)

/* Comment out this section if you are not using Order Confirmations */

 IF @not2id IS NULL

 BEGIN

 

 print 'Adding the ESP_Order Confirmation Export NotIFication FOR company: '+'Company ID: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name)

 

					INSERT INTO orgNotification (mainttime, userid, notificationtype, warninglevel, contenttype, contentname, forCompanyID, deliverbyMessageSystemID, toAddressID, toContactID, name, referenceRule) 

					VALUES(GETDATE(),@updateUser,'OrderConfirmed',0,2,'ESPOrder_Export',@companyid,@messageSystemID2,NULL,NULL,'OrderConfirmed', null) 

 print 'Added'

 END

				 Else

				 print 'Already exists: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name)

/* This is the Docket Export Section */ 

--IF @not3id IS NULL

-- BEGIN

-- print 'Added the ESP_Docket Export FOR company: '+'Company ID: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name)

 

--					INSERT INTO orgNotification (mainttime, userid, notificationtype, warninglevel, contenttype, contentname, forCompanyID, deliverbyMessageSystemID, toAddressID, toContactID, name, referenceRule) 

--					VALUES(GETDATE(),@updateUser,'DocketFirstShipped',0,2,'ESP Docket Export',@companyid,@messageSystemID3,NULL,NULL,'ESP_Docket Export Notification', '[masterbilloflading.masterbolnumber]&"_"&[docketnumber]&"_"&[plant.plantcode]&"_"') 

-- print 'Added'

-- END

--				Else

--				print 'Already exists: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name)

--Check to see if notification 4 exists the begin`

--IF @not4id IS NULL

--				 BEGIN

--				 print 'Adding the Invoice Export FOR company: '+'Company ID: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name)

 

--				 INSERT INTO orgNotification (mainttime, userid, notificationtype, warninglevel, contenttype, contentname, forCompanyID, deliverbyMessageSystemID, toAddressID, toContactID, name, referenceRule) 

--				 VALUES(GETDATE(),@updateUser,'InvoicePosted',0,2,'SFR015_CreditNote_Export',@companyid,@messageSystemID4,NULL,NULL,'SAPCreditExport',null)

--				 print 'Added'

--				 END

--				 Else

--				 print 'Already exists: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name)

--Check to see if notification 5 exists the begin`

IF @not5id IS NULL

 BEGIN

 

 print 'Adding the ESP_Order Cancelled Export NotIfication FOR company: '+'Company ID: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name)

 

					INSERT INTO orgNotification (mainttime, userid, notificationtype, warninglevel, contenttype, contentname, forCompanyID, deliverbyMessageSystemID, toAddressID, toContactID, name, referenceRule) 

					VALUES(GETDATE(),@updateUser,'OrderCancelled',0,2,'ESPOrder_Export',@companyid,@messageSystemID2,NULL,NULL,'OrderCancelled', null) 

 print 'Added'

 END

				 Else

				 print 'Already exists: '+CONVERT(VARCHAR,@companyid)+ ' '+'Company Name: '+CONVERT(VARCHAR,@name)

 

FETCH NEXT FROM look INTO @companyid,@name

print ''

print '=====Ended with '+'Company Name: '+CONVERT(VARCHAR,@name)+' ====='

print ''

print ''

END

CLOSE look

DEALLOCATE look