-
Notifications
You must be signed in to change notification settings - Fork 5
/
ClosedBranch_Maintenance.sql
162 lines (139 loc) · 5.74 KB
/
ClosedBranch_Maintenance.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
USE [Polaris]
GO
/****** Object: StoredProcedure [Polaris].[SILS_ClosedBranch_Maintenance] Script Date: 18/03/2021 2:13:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Jason Tenter
-- Create date: 2020-08-04
-- Description: This procedure is for removing hold routing, and pushing forward checkout due dates/hold unclaimed dates, for branches that are closed for a long period.
-- It was created during the Covid19 closures, to prevent physical materials being shipped to branches without staff.
-- =============================================
ALTER PROCEDURE [Polaris].[SILS_ClosedBranch_Maintenance]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @NewDate datetime = (SELECT DATEADD(ss, -1, CONVERT(Datetime, CONVERT(Date, DATEADD(ww, 2, GETDATE())))))
-- List of Branches to include in the update, based on the Polaris.SILS_ClosedBranch_List table
-- That table also includes exception branches, which this process excludes
-- Hold Routing updating branches - includes all DatesCloded branches
CREATE TABLE #LibList (OrganizationID int, ParentOrganizationID int, OrganizationCodeID int)
INSERT INTO #LibList
SELECT o.OrganizationID, o.ParentOrganizationID, o.OrganizationCodeID
FROM Polaris.Organizations o (NOLOCK)
LEFT JOIN Polaris.SILS_ClosedBranch_List cb
on o.OrganizationID = cb.OrganizationID and cb.OrganizationCodeID = 3
and cb.Exception != 1 and cb.DatesClosed = 1
LEFT JOIN Polaris.SILS_ClosedBranch_List cbe -- Exceptions
on o.OrganizationID = cbe.OrganizationID and cbe.OrganizationCodeID = 3
and cbe.Exception = 1
WHERE o.OrganizationCodeID = 3
AND cbe.OrganizationID is NULL
AND (cb.OrganizationID is not null
OR o.ParentOrganizationID IN
(SELECT
cb.OrganizationID
FROM Polaris.SILS_ClosedBranch_List cb
WHERE cb.OrganizationCodeID = 2
))
-- Due Date updating branches
CREATE TABLE #LibListDue (OrganizationID int, ParentOrganizationID int, OrganizationCodeID int)
INSERT INTO #LibListDue
SELECT o.OrganizationID, o.ParentOrganizationID, o.OrganizationCodeID
FROM Polaris.Organizations o (NOLOCK)
LEFT JOIN Polaris.SILS_ClosedBranch_List cb
on o.OrganizationID = cb.OrganizationID and cb.OrganizationCodeID = 3
and cb.Exception != 1 AND cb.DueDates = 1 -- specifies due date updating
LEFT JOIN Polaris.SILS_ClosedBranch_List cbe -- Exceptions
on o.OrganizationID = cbe.OrganizationID and cbe.OrganizationCodeID = 3
and cbe.Exception = 1
WHERE o.OrganizationCodeID = 3
AND cbe.OrganizationID is NULL
AND (cb.OrganizationID is not null
OR o.ParentOrganizationID IN
(SELECT
cb.OrganizationID
FROM Polaris.SILS_ClosedBranch_List cb
WHERE cb.OrganizationCodeID = 2
AND cb.DueDates = 1
))
-- Hold Unclaimed date updating branches
CREATE TABLE #LibListHold (OrganizationID int, ParentOrganizationID int, OrganizationCodeID int)
INSERT INTO #LibListHold
SELECT o.OrganizationID, o.ParentOrganizationID, o.OrganizationCodeID
FROM Polaris.Organizations o (NOLOCK)
LEFT JOIN Polaris.SILS_ClosedBranch_List cb
on o.OrganizationID = cb.OrganizationID and cb.OrganizationCodeID = 3
and cb.Exception != 1 AND cb.HoldUntilDates = 1 -- specifies hold unclaimed dates updating
LEFT JOIN Polaris.SILS_ClosedBranch_List cbe -- Exceptions
on o.OrganizationID = cbe.OrganizationID and cbe.OrganizationCodeID = 3
and cbe.Exception = 1
WHERE o.OrganizationCodeID = 3
AND cbe.OrganizationID is NULL
AND (cb.OrganizationID is not null
OR o.ParentOrganizationID IN
(SELECT
cb.OrganizationID
FROM Polaris.SILS_ClosedBranch_List cb
WHERE cb.OrganizationCodeID = 2
AND cb.DueDates = 1
))
-- Update branch closed days
-- Add to backup table, then update the branch hours to be closed
INSERT INTO Polaris.SILS_ClosedBranch_OriginalHours (OrganizationID, BranchHours, AddedDate)
SELECT
h.OrganizationID,
h.Value,
CURRENT_TIMESTAMP
FROM polaris.polaris.OrganizationsPPPP h (nolock)
INNER JOIN #LibList ll
on h.OrganizationID = ll.OrganizationID
LEFT JOIN Polaris.SILS_ClosedBranch_OriginalHours oh
on h.OrganizationID = oh.organizationid
AND oh.RestoredDate IS NULL
WHERE h.AttrID = 96
and h.Value != 'MONCLOSED,TUECLOSED,WEDCLOSED,THUCLOSED,FRICLOSED,SATCLOSED,SUNCLOSED'
and oh.organizationid is null
UPDATE h
SET h.value = 'MONCLOSED,TUECLOSED,WEDCLOSED,THUCLOSED,FRICLOSED,SATCLOSED,SUNCLOSED'
FROM polaris.polaris.OrganizationsPPPP h (nolock)
INNER JOIN #LibList ll
on h.OrganizationID = ll.OrganizationID
WHERE h.AttrID = 96
and h.Value != 'MONCLOSED,TUECLOSED,WEDCLOSED,THUCLOSED,FRICLOSED,SATCLOSED,SUNCLOSED'
-- Update overdue settings on checkouts, if there are any
UPDATE ic
SET ic.OVDNoticeCount = 0,
ic.OVDNoticeDate = NULL
FROM Polaris.ItemCheckouts ic
INNER JOIN Polaris.Organizations o (nolock)
on ic.OrganizationID = o.OrganizationID
INNER JOIN #LibListDue ll
on o.OrganizationID = ll.OrganizationID
WHERE ic.dueDate BETWEEN '2020-03-01' AND @NewDate
and ic.OVDNoticeCount != 0
-- Update due dates
UPDATE ic
SET ic.dueDate = @NewDate
FROM Polaris.ItemCheckouts ic
INNER JOIN Polaris.Organizations o (nolock)
on ic.OrganizationID = o.OrganizationID
INNER JOIN #LibListDue ll
on o.OrganizationID = ll.OrganizationID
WHERE ic.dueDate BETWEEN '2020-03-01' AND @NewDate
-- Update hold unclaimed dates
UPDATE shr
SET shr.HoldTillDate = @NewDate
FROM Polaris.SysHoldRequests shr
INNER JOIN Polaris.Organizations o (nolock)
on shr.PickupBranchID = o.OrganizationID
INNER JOIN #LibListHold ll
on o.OrganizationID = ll.OrganizationID
WHERE shr.SysHoldStatusID = 6
AND shr.HoldTillDate < @NewDate
DROP TABLE #LibList
DROP TABLE #LibListDue
DROP TABLE #LibListHold
END