CREATE PROCEDURE dbo.ReportAutomationAdd @AName VARCHAR(50), @ABasedOnReport SMALLINT, @ADateType INT, @AActive BIT, @ADaysBackward INT, @ADaysForward INT, @AReportAutomationType SMALLINT AS
/*
  Created by Kent Johansen August 2020
  Changes:
    20220705: Bug: Fix for "Reports not added for new Domino Hotels".

*/
SET NOCOUNT ON

DECLARE @LIdent INT

DECLARE @LFK_DSLUserIdNumber INT
DECLARE @LExportType TINYINT
DECLARE @LHotelCustNo INT
DECLARE @LMailAddress VARCHAR(200)
DECLARE @LFileType SMALLINT
DECLARE @LScheduleType SMALLINT
DECLARE @LFK_RecurringPatternId INT
DECLARE @LAllowOverwriteExisting BIT

SELECT @LFK_RecurringPatternId = Id FROM RecurringPattern WITH (NOLOCK) WHERE RecurringType = 1 --Recurringpattern in security pattern
SELECT @LFK_DSLUserIdNumber = UserIdNumber FROM Personel WITH (NOLOCK) WHERE UserTypeNumber = 4 -- AutoNight/Digital report 1
SET @LExportType = 2 -- Mail
SET @LFileType = 2 -- pdf
SET @LScheduleType = 4 -- Security
SET @LAllowOverwriteExisting = 1 -- allow overwrite

DECLARE #HotelCur CURSOR FAST_FORWARD READ_ONLY FOR
  SELECT Customer.CustomerNumber, MailAddress
  FROM Customer WITH (NOLOCK) 
  INNER JOIN MailAddresses WITH (NOLOCK) ON MailAddresses.CustomerNumber = Customer.CustomerNumber
    AND MailAddressType = 0
  WHERE CustomerTypeNumber = 7 
  AND (ISNULL(HotelNumber, -1) >= 0 OR LocalHotel = 1)
  AND ISNULL(MarkAsDeleted, 0) = 0
  AND Customer.CustomerNumber NOT IN
  (
    SELECT FK_CustomerNumber
    FROM ReportAutomationCustomerXTab WITH (NOLOCK)
    INNER JOIN ReportAutomation WITH (NOLOCK) ON ReportAutomation.PK_ReportAutomationId = ReportAutomationCustomerXTab.FK_ReportAutomationId
    WHERE ReportAutomation.ScheduleType = @LScheduleType
    AND BasedOnReport = @ABasedOnReport
    AND ReportAutomation.Name = @AName
  )
OPEN #HotelCur

FETCH NEXT FROM #HotelCur INTO @LHotelCustNo, @LMailAddress
WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT INTO ReportAutomation
    (Name, BasedOnReport, FK_DSLUserIdNumber, ExportType, ExportName, MailType, ScheduleType, FK_RecurringPatternId, DateType, Active, AllowOverwriteExisting, 
      EmailBody, ConfirmationMail, DaysForward, DaysBackward, ReportAutomationType)
  VALUES
    (@AName, @ABasedOnReport, @LFK_DSLUserIdNumber, @LExportType, @LMailAddress, @LFileType, @LScheduleType, @LFK_RecurringPatternId, @ADateType, @AActive, @LAllowOverwriteExisting,
      NULL, '', @ADaysForward, @ADaysBackward, @AReportAutomationType)

  SELECT @LIdent = @@Identity

  INSERT INTO dbo.ReportAutomationCustomerXTab (FK_ReportAutomationId, FK_CustomerNumber) VALUES (@LIdent, @LHotelCustNo)

  FETCH NEXT FROM #HotelCur INTO @LHotelCustNo, @LMailAddress
END

CLOSE #HotelCur
DEALLOCATE #HotelCur