USE CRISDB
GO
DECLARE
@DissolutionAdminScheduleID int,
@RevocationAdminScheduleID int,
@DissolutionFirstNoticeAdminScheduleListID int,
@RevocationFirstNoticeAdminScheduleListID int,
@DissolutionSecondNoticeAdminScheduleListID int,
@RevocationSecondNoticeAdminScheduleListID int,
@DissolutionPublicationNoticeAdminScheduleListID int,
@RevocationPublicationNoticeAdminScheduleListID int,
@DRFirstNoticeDRDate datetime,
@DRSecondNoticeDate datetime,
@DRSecondNoticeDRDate datetime,
@DRThirdNoticeDate datetime,
@DRThirdNoticeCorrectionDate datetime,
@DRThirdNoticeDRDate datetime
/* check what day of the week it is and adjust according to Mon-Fri work week*/
SELECT @DRFirstNoticeDRDate = (case
when /* the date for dissolution 1st notice dissolution and revocation 1st notice revocation will by default be
1 day after the day dissolution 1st notice and revocation 1st notice ran. when the day this list was scheduled to
run on is a Saturday making the day 1st notice ran on a Friday, push it 3 days forward to the following Monday.*/
DATEPART(DW, DATEADD(day, +1, GETDATE())) = 7 then CONVERT(datetime, FORMAT(DATEADD(day, +3, GETDATE()), 'yyyy-MM-dd') + ' 00:00:00.000', 120)
else CONVERT(datetime, FORMAT(DATEADD(day, +1, GETDATE()), 'yyyy-MM-dd') + ' 00:00:00.000', 120) END)
SELECT @DRSecondNoticeDate = (case
when /* the date for dissolution 2nd notice and revocation 2nd notice will by default be
2 days after the day dissolution 1st notice and revocation 1st notice ran. when the day this list was scheduled to run on
is a Saturday making the day 1st notice ran on a Thursday, push it 4 days forward to the following Monday.*/
DATEPART(DW, DATEADD(day, +2, GETDATE())) = 7 then CONVERT(datetime, FORMAT(DATEADD(day, +4, GETDATE()), 'yyyy-MM-dd') + ' 00:00:00.000', 120)
else CONVERT(datetime, FORMAT(DATEADD(day, +2, GETDATE()), 'yyyy-MM-dd') + ' 00:00:00.000', 120) END)
SELECT @DRSecondNoticeDRDate = (case
when /* the date for dissolution 2nd notice dissolution and revocation 2nd notice revocation will by default be
3 days after the day dissolution 1st notice and revocation 1st notice ran. when the day this list was scheduled to run on
is a Saturday making the day 1st notice ran on a Wednesday, push it 5 days forward to the following Monday.*/
DATEPART(DW, DATEADD(day, +3, GETDATE())) = 7 then CONVERT(datetime, FORMAT(DATEADD(day, +5, GETDATE()), 'yyyy-MM-dd') + ' 00:00:00.000', 120)
else CONVERT(datetime, FORMAT(DATEADD(day, +3, GETDATE()), 'yyyy-MM-dd') + ' 00:00:00.000', 120) END)
SELECT @DRThirdNoticeDate = (case
when /* the date for dissolution 3rd notice and revocation 3rd notice will by default be
4 days after the day dissolution 1st notice and revocation 1st notice ran. when the day this list was scheduled to run on
is a Saturday making the day 1st notice ran on a Tuesday, push it 6 days forward to the following Monday.*/
DATEPART(DW, DATEADD(day, +4, GETDATE())) = 7 then CONVERT(datetime, FORMAT(DATEADD(day, +6, GETDATE()), 'yyyy-MM-dd') + ' 00:00:00.000', 120)
else CONVERT(datetime, FORMAT(DATEADD(day, +4, GETDATE()), 'yyyy-MM-dd') + ' 00:00:00.000', 120) END)
SELECT @DRThirdNoticeCorrectionDate = (case
when /* the date for dissolution 3rd notice correction and revocation 3rd notice correction will by default be
14 days after the day dissolution 1st notice and revocation 1st notice ran because on this date the lists will
clear out so testing for this step needs to be completed before that.*/
DATEPART(DW, DATEADD(day, +14, GETDATE())) = 7 then CONVERT(datetime, FORMAT(DATEADD(day, +16, GETDATE()), 'yyyy-MM-dd') + ' 00:00:00.000', 120)
else CONVERT(datetime, FORMAT(DATEADD(day, +14, GETDATE()), 'yyyy-MM-dd') + ' 00:00:00.000', 120) END)
SELECT @DRThirdNoticeDRDate = (case
when /* the date for dissolution 3rd notice and revocation 3rd notice will by default be
5 days after the day dissolution 1st notice and revocation 1st notice ran. when the day this list was scheduled to run on
is a Saturday making the day 1st notice ran on a Monday, push it 7 days forward to the following Monday.*/
DATEPART(DW, DATEADD(day, +5, GETDATE())) = 7 then CONVERT(datetime, FORMAT(DATEADD(day, +7, GETDATE()), 'yyyy-MM-dd') + ' 00:00:00.000', 120)
else CONVERT(datetime, FORMAT(DATEADD(day, +5, GETDATE()), 'yyyy-MM-dd') + ' 00:00:00.000', 120) END)
/*change dates on any schedules within the next 14 days of the present*/
use CRISDB
update TAdminScheduleList
set runDate = DATEADD(day, +15, GETDATE()), noticeDate = DATEADD(day, +15, GETDATE()),
lastDateForCorrection = DATEADD(day, +15, GETDATE())
from TAdminScheduleList
WHERE runDate >= CONVERT(datetime, FORMAT(GETDATE(), 'yyyy-MM-dd') + ' 00:00:00.000', 120) AND runDate <= DATEADD(day, +14, GETDATE())
/* Create the schedules: It will be always one record per quarter for dissolution and one record per year for revocation.*/
/*Insert the admin schedule record for Dissolutions.*/
INSERT INTO [dbo].[TAdminSchedule]
([scheduleTypeID], [scheduleDate], [description])
VALUES (1, GETDATE(), CONVERT(varchar, 'Q' + CONVERT(varchar, DATEPART(QUARTER, YEAR(GETDATE())), 50) + ' ' +
CONVERT(varchar, DATEPART(YEAR, GETDATE())) + ' Dissolution test', 50));
SET @DissolutionAdminScheduleID = @@IDENTITY;
/*Insert the admin schedule record for Revocations.*/
INSERT INTO [dbo].[TAdminSchedule]
([scheduleTypeID], [scheduleDate], [description])
VALUES (2, GETDATE(), CONVERT(varchar, YEAR(GETDATE()), 50) + ' Revocation test');
SET @RevocationAdminScheduleID = @@IDENTITY;
/*Insert record for Dissolution 1st notice.*/
INSERT INTO [dbo].[TAdminScheduleList]
([description],[adminScheduleID],[scheduleStatusID],[noticeTypeID]
,[returnedUndeliveredOnly],[runDate],[noticeDate],[lastDateForCorrection],[parentScheduleListID])
VALUES ('Dissolution 1stNotice test', @DissolutionAdminScheduleID, 1, 1, 1,
CONVERT(datetime, FORMAT(GETDATE(), 'yyyy-MM-dd') + ' 00:00:00.000', 120),
CONVERT(datetime, FORMAT(GETDATE(), 'yyyy-MM-dd') + ' 00:00:00.000', 120), @DRFirstNoticeDRDate, null);
SET @DissolutionFirstNoticeAdminScheduleListID = @@IDENTITY;
/*Insert record for Revocation 1st notice.*/
INSERT INTO [dbo].[TAdminScheduleList]
([description],[adminScheduleID],[scheduleStatusID],[noticeTypeID]
,[returnedUndeliveredOnly],[runDate],[noticeDate],[lastDateForCorrection],[parentScheduleListID])
VALUES ('Revocation 1stNotice test', @RevocationAdminScheduleID, 1, 1, 1,
CONVERT(datetime, FORMAT(GETDATE(), 'yyyy-MM-dd') + ' 00:00:00.000', 120),
CONVERT(datetime, FORMAT(GETDATE(), 'yyyy-MM-dd') + ' 00:00:00.000', 120), @DRFirstNoticeDRDate, null);
SET @RevocationFirstNoticeAdminScheduleListID = @@IDENTITY;
/*Insert record for Dissolution 1st notice Dissolution.*/
INSERT INTO [dbo].[TAdminScheduleList]
([description],[adminScheduleID],[scheduleStatusID],[noticeTypeID]
,[returnedUndeliveredOnly],[runDate],[noticeDate],[lastDateForCorrection],[parentScheduleListID])
VALUES ('Dissolution 1stNotice - Dissolution test', @DissolutionAdminScheduleID, 1, 5, 1, @DRFirstNoticeDRDate,
@DRFirstNoticeDRDate, DATEADD(day, +2, GETDATE()), @DissolutionFirstNoticeAdminScheduleListID);
/*Insert record for Revocation 1st notice Revocation.*/
INSERT INTO [dbo].[TAdminScheduleList]
([description],[adminScheduleID],[scheduleStatusID],[noticeTypeID]
,[returnedUndeliveredOnly],[runDate],[noticeDate],[lastDateForCorrection],[parentScheduleListID])
VALUES ('Revocation 1stNotice - Revocation test', @RevocationAdminScheduleID, 1, 5, 1, @DRFirstNoticeDRDate,
@DRFirstNoticeDRDate, @DRSecondNoticeDate, @RevocationFirstNoticeAdminScheduleListID);
/*Insert record for Dissolution 2nd notice.*/
INSERT INTO [dbo].[TAdminScheduleList]
([description],[adminScheduleID],[scheduleStatusID],[noticeTypeID]
,[returnedUndeliveredOnly],[runDate],[noticeDate],[lastDateForCorrection],[parentScheduleListID])
VALUES ('Dissolution 2ndNotice test', @DissolutionAdminScheduleID, 1, 2, 1, @DRSecondNoticeDate, @DRSecondNoticeDate,
@DRSecondNoticeDRDate, @DissolutionFirstNoticeAdminScheduleListID);
SET @DissolutionSecondNoticeAdminScheduleListID = @@IDENTITY;
/*Insert record for Revocation 2nd notice.*/
INSERT INTO [dbo].[TAdminScheduleList]
([description],[adminScheduleID],[scheduleStatusID],[noticeTypeID]
,[returnedUndeliveredOnly],[runDate],[noticeDate],[lastDateForCorrection],[parentScheduleListID])
VALUES ('Revocation 2ndNotice test', @RevocationAdminScheduleID, 1, 2, 1, @DRSecondNoticeDate, @DRSecondNoticeDate,
@DRSecondNoticeDRDate, @RevocationFirstNoticeAdminScheduleListID);
SET @RevocationSecondNoticeAdminScheduleListID = @@IDENTITY;
/*Insert record for Dissolution 2nd notice dissolution.*/
INSERT INTO [dbo].[TAdminScheduleList]
([description],[adminScheduleID],[scheduleStatusID],[noticeTypeID]
,[returnedUndeliveredOnly],[runDate],[noticeDate],[lastDateForCorrection],[parentScheduleListID])
VALUES ('Dissolution 2ndNotice - Dissolution test', @DissolutionAdminScheduleID, 1, 5, 1, @DRSecondNoticeDRDate, @DRSecondNoticeDRDate,
@DRSecondNoticeDate, @DissolutionSecondNoticeAdminScheduleListID);
/*Insert record for Revocation 2nd notice Revocation.*/
INSERT INTO [dbo].[TAdminScheduleList]
([description],[adminScheduleID],[scheduleStatusID],[noticeTypeID]
,[returnedUndeliveredOnly],[runDate],[noticeDate],[lastDateForCorrection],[parentScheduleListID])
VALUES ('Revocation 2ndNotice - Revocation test', @RevocationAdminScheduleID, 1, 5, 1, @DRSecondNoticeDRDate, @DRSecondNoticeDRDate,
@DRSecondNoticeDate, @RevocationSecondNoticeAdminScheduleListID);
/*Insert record for Dissolution publication notice.*/
INSERT INTO [dbo].[TAdminScheduleList]
([description],[adminScheduleID],[scheduleStatusID],[noticeTypeID]
,[returnedUndeliveredOnly],[runDate],[noticeDate],[lastDateForCorrection],[parentScheduleListID])
VALUES ('Dissolution 3rdNotice test', @DissolutionAdminScheduleID, 1, 4, 1, @DRThirdNoticeDate, @DRThirdNoticeDate,
@DRThirdNoticeCorrectionDate, @DissolutionSecondNoticeAdminScheduleListID); SET @DissolutionPublicationNoticeAdminScheduleListID = @@IDENTITY;
/*Insert record for Revocation publication notice.*/
INSERT INTO [dbo].[TAdminScheduleList]
([description],[adminScheduleID],[scheduleStatusID],[noticeTypeID]
,[returnedUndeliveredOnly],[runDate],[noticeDate],[lastDateForCorrection],[parentScheduleListID])
VALUES ('Revocation 3rdNotice test', @RevocationAdminScheduleID, 1, 4, 1, @DRThirdNoticeDate, @DRThirdNoticeDate,
@DRThirdNoticeCorrectionDate, @RevocationSecondNoticeAdminScheduleListID); SET @RevocationPublicationNoticeAdminScheduleListID = @@IDENTITY;
/*Insert record for Dissolution publication notice dissolution.*/
INSERT INTO [dbo].[TAdminScheduleList]
([description],[adminScheduleID],[scheduleStatusID],[noticeTypeID]
,[returnedUndeliveredOnly],[runDate],[noticeDate],[lastDateForCorrection],[parentScheduleListID])
VALUES ('Dissolution 3rdNotice - Dissolution test', @DissolutionAdminScheduleID, 1, 5, 1, @DRThirdNoticeDRDate, @DRThirdNoticeDRDate,
@DRThirdNoticeDate, @DissolutionPublicationNoticeAdminScheduleListID);
/*Insert record for Revocation publication notice Revocation.*/
INSERT INTO [dbo].[TAdminScheduleList]
([description],[adminScheduleID],[scheduleStatusID],[noticeTypeID]
,[returnedUndeliveredOnly],[runDate],[noticeDate],[lastDateForCorrection],[parentScheduleListID])
VALUES ('Revocation 3rdNotice - Revocation test', @RevocationAdminScheduleID, 1, 5, 1, @DRThirdNoticeDRDate, @DRThirdNoticeDRDate,
@DRThirdNoticeDate, @RevocationPublicationNoticeAdminScheduleListID);