· 6 years ago · Sep 01, 2019, 01:46 PM
1use hcs_database
2go
3--db10
4
5sp_define AutoPay_Create_Invoice_Batch_Schedule
6
7select *
8from zg_AutoPay_Batch_Type
9
10update zabt
11set default_cc_checkout_gateway_fk = 1,
12 default_ach_checkout_gateway_fk = 3--,
13 --default_reminder_offset_days = 5
14from zg_AutoPay_Batch_Type zabt
15where zabt.id = 1
16
17
18select *
19from zga_Payment_Frequency
20--1: 1-Pay
21--4: 2-Pay
22--2: 4-Pay
23--5: 11-Pay 1st of Month
24--9: 11-Pay 15th of Month
25--6: 22-Pay
26--7: International Plan
27
28
29select *
30from zp_Checkout_Gateway
31--1 Bluesnap ppf1
32--3 Bluesnap spf1
33
34select *
35from zga_AR_Invoice_Types
36--2 Comprehensive TUI
37
38--exec AutoPay_Create_Invoice_Batch_Schedule 5, 2, 2019 not creating this was full amount due with deposit, no installments
39exec AutoPay_Create_Invoice_Batch_Schedule 1, 2, 2019
40exec AutoPay_Create_Invoice_Batch_Schedule 4, 2, 2019
41exec AutoPay_Create_Invoice_Batch_Schedule 2, 2, 2019
42exec AutoPay_Create_Invoice_Batch_Schedule 5, 2, 2019
43exec AutoPay_Create_Invoice_Batch_Schedule 9, 2, 2019
44exec AutoPay_Create_Invoice_Batch_Schedule 6, 2, 2019
45exec AutoPay_Create_Invoice_Batch_Schedule 7, 2, 2019
46
47
48select *
49from VCPay_Autopay_Batch
50order by batch_date
51
52insert VCPay_Autopay_Batch (batch_type, batch_date, notification_reminder_1_date, processing_type, processing_cc_checkout_gateway_fk, processing_ach_checkout_gateway_fk,
53 invoice_school_year, invoice_invoice_type, invoice_payment_plan, invoice_installment_number, begin_due_date, end_due_date)
54select batch_type,
55 '20190520',
56 null,
57 processing_type,
58 processing_cc_checkout_gateway_fk,
59 processing_ach_checkout_gateway_fk,
60 invoice_school_year,
61 invoice_invoice_type,
62 invoice_payment_plan,
63 0,
64 '20190520',
65 '20190520'
66from VCPay_Autopay_Batch
67where vcpay_autopay_batch_pk = 12
68
69delete
70from VCPay_Autopay_Batch
71where vcpay_autopay_batch_pk = 1
72
73delete
74from VCPay_Autopay_Batch
75where vcpay_autopay_batch_pk in (1, 3)
76
77update vab
78set begin_due_date = batch_date,
79 end_due_date = batch_date
80from VCPay_Autopay_Batch vab
81where vcpay_autopay_batch_pk <> 0
82
83update vab
84set processing_cc_checkout_gateway_fk = 5,
85 processing_ach_checkout_gateway_fk = 6
86from VCPay_Autopay_Batch vab
87where vab.vcpay_autopay_batch_pk <> 0
88
89update vab
90set notification_reminder_1_date = null
91from VCPay_Autopay_Batch vab
92where vab.vcpay_autopay_batch_pk = 15
93
94
95update vab
96set batch_date = '20200102'--,
97 --notification_reminder_1_date = '20200116'
98from VCPay_Autopay_Batch vab
99where vab.vcpay_autopay_batch_pk in (15, 44)
100
101
102update vab
103set batch_date = '20200218'--,
104 --notification_reminder_1_date = '20200116'
105from VCPay_Autopay_Batch vab
106where vab.vcpay_autopay_batch_pk in (27, 47)
107
108
109update vab
110set batch_date = '20190903'--,
111 --notification_reminder_1_date = '20190829'
112from VCPay_Autopay_Batch vab
113where vab.vcpay_autopay_batch_pk in (5, 11, 36)
114
115
116update vab
117set invoice_installment_number = invoice_installment_number + 12
118from VCPay_Autopay_Batch vab
119where vab.vcpay_autopay_batch_pk between 42 and 51
120
121
122SELECT DATEADD(day, 19, DATEADD(month, DATEDIFF(month, 0, batch_date), 0))
123from VCPay_Autopay_Batch vab
124where vab.vcpay_autopay_batch_pk = 3
125
126update vab
127set begin_due_date = (SELECT DATEADD(day, 19, DATEADD(month, DATEDIFF(month, 0, batch_date), 0))),
128 end_due_date = (SELECT DATEADD(day, 19, DATEADD(month, DATEDIFF(month, 0, batch_date), 0)))
129from VCPay_Autopay_Batch vab
130where vcpay_autopay_batch_pk <> 0
131
132
133update vab
134set processing_cc_checkout_gateway_fk = 1,
135 processing_ach_checkout_gateway_fk = 3
136from VCPay_Autopay_Batch vab
137where vcpay_autopay_batch_pk <> 0
138
139
140update vab
141set begin_due_date = batch_date,
142 end_due_date = batch_date
143--select *
144from VCPay_Autopay_Batch vab
145where vcpay_autopay_batch_pk <> 0
146 and approval_status = 0
147order by batch_date
148
149
150
151
152create or alter procedure dbo.AutoPay_Create_Invoice_Batch_Schedule(
153 @payment_plan int, -- zga_Payment_Frequency
154 @invoice_type tinyint = 0, -- zga_AR_Invoice_Types
155 @school_year smallint
156)
157as
158begin
159
160 declare @new_autopay_batches table(
161 invoice_payment_plan smallint,
162 batch_type tinyint,
163 batch_date smalldatetime,
164 invoice_school_year smallint,
165 invoice_installment_number int,
166 begin_due_date smalldatetime,
167 end_due_date smalldatetime,
168 invoice_type smallint,
169 processing_type tinyint
170 )
171
172 if @school_year is null
173 set @school_year = (select dbo.Current_School_Year()) + 1;
174
175 --------------------------
176 -- CREATE AUTOPAY BATCHES
177 --------------------------
178 with payment_schedule as(
179 select distinct
180 invoice_payment_plan = zgp.id,
181 cd.calendar_date,
182 zgp.number_of_payments
183
184 from zga_Payment_Frequency zgp
185 cross join Calendar_Day cd
186
187 where ( zgp.id = @payment_plan -- use provided payment plan if specified
188 or @payment_plan is null -- otherwise create all records
189 )
190 and zgp.id != 0
191 and cd.calendar_date in (zgp.due_date_1, zgp.due_date_2, zgp.due_date_3, zgp.due_date_4, zgp.due_date_5, zgp.due_date_6, zgp.due_date_7, zgp.due_date_8, zgp.due_date_9, zgp.due_date_10, zgp.due_date_11, zgp.due_date_12)
192 ),
193 batch_dates as(
194 select *,
195 invoice_installment_number = row_number() over (partition by ps.invoice_payment_plan order by ps.calendar_date),
196 batch_date = case -- if payment date lands on a weekend, move to the following monday
197 when datename(dw, ps.calendar_date) = 'Saturday' then dateadd(day, 2, ps.calendar_date)
198 when datename(dw, ps.calendar_date) = 'Sunday' then dateadd(day, 1, ps.calendar_date)
199 else ps.calendar_date
200 end
201
202 from payment_schedule ps
203 )
204
205 insert @new_autopay_batches
206 select bd.invoice_payment_plan,
207 1, -- invoice
208 bd.batch_date,
209 invoice_school_year = @school_year,
210 bd.invoice_installment_number,
211 begin_due_date = lag(bd.batch_date, 1, bd.batch_date) over (partition by bd.invoice_payment_plan order by bd.invoice_installment_number), -- get batch date of previous batch. If first batch, begin date = end date
212 end_due_date = case
213 when bd.invoice_installment_number = 1 then bd.batch_date -- end on day prior to this batch's batch date. If first batch, use batch date
214 else dateadd(day, -1, bd.batch_date)
215 end,
216 @invoice_type,
217 1 -- processing type = scheduled
218
219 from batch_dates bd
220
221 where bd.invoice_installment_number <= bd.number_of_payments
222 and not exists(
223 select 1
224
225 from VCPay_Autopay_Batch _vcp
226
227 where _vcp.invoice_payment_plan = bd.invoice_payment_plan
228 and _vcp.batch_type = 1 -- invoice
229 and _vcp.batch_date = bd.batch_date
230 and _vcp.invoice_school_year = @school_year
231 and _vcp.invoice_installment_number = bd.invoice_installment_number
232 and _vcp.invoice_invoice_type = @invoice_type
233 )
234
235 ------------------------------
236 -- ADJUST BEGIN/END DUE DATES
237 ------------------------------
238 -- First batch has same start/end date. We want batch 2 date range to not overlap with batch 1.
239 update @new_autopay_batches
240
241 set begin_due_date = dateadd(day, 1, begin_due_date)
242
243 where invoice_installment_number = 2
244
245 -- give last batch an end date including batch date
246 update nab
247
248 set nab.end_due_date = nab.batch_date
249
250 from @new_autopay_batches nab
251
252 where invoice_installment_number = (
253 select max(invoice_installment_number)
254
255 from @new_autopay_batches _nab
256
257 where _nab.invoice_payment_plan = nab.invoice_payment_plan
258 )
259 and nab.end_due_date != nab.batch_date
260
261 ------------------------
262 -- INSERT TO PRODUCTION
263 ------------------------
264
265 insert VCPay_Autopay_Batch(invoice_payment_plan, batch_type, batch_date, invoice_school_year, invoice_installment_number, begin_due_date, end_due_date, invoice_invoice_type, processing_type)
266 select *
267 from @new_autopay_batches
268
269end
270go
271
272
273
274create or alter procedure dbo.Reset_AutoPay_Batch_Notification_Default(
275 @default_notification_days_before int
276)
277as
278begin
279
280 update zabt
281 set default_reminder_offset_days = @default_notification_days_before
282 from zg_AutoPay_Batch_Type zabt
283 where zabt.id = 1
284
285 update vab
286 set notification_reminder_1_date = (select dateadd(dd, -1 * @default_notification_days_before, batch_date))
287 from VCPay_Autopay_Batch vab
288 where vab.vcpay_autopay_batch_pk <> 0
289 and approval_status = 0
290end
291go
292
293exec Reset_AutoPay_Batch_Notification_Default 5