1 -- DATA FOR LIVE TESTING LP#1198465:
2 -- Support for Conditional Negative Balances
4 -- Assume stock data has been loaded.
6 -- Dates are relative when necessary; otherwise they may be hardcoded.
7 -- NOTE: Org unit settings will be handled in the perl code
10 -- Setup some LOST circs, and change copy status to LOST
11 UPDATE action.circulation SET
12 xact_start = '2014-05-14 08:39:13.070326-04',
13 due_date = '2014-05-21 23:59:59-04',
14 stop_fines_time = '2014-05-28 08:39:13.070326-04',
15 create_time = '2014-05-14 08:39:13.070326-04',
21 checkin_scan_time = NULL
22 WHERE id IN (1,2,3,4,5,6,12,13,15);
23 UPDATE asset.copy SET status = 3 WHERE id IN (2,3,4,5,6,7,13,14,16);
26 UPDATE action.circulation SET
27 xact_start = NOW() - '24 days'::interval,
28 due_date = (DATE(NOW() - '10 days'::interval) || ' 23:59:59')::TIMESTAMP,
29 stop_fines_time = NOW() - '3 days'::interval,
30 create_time = NOW() - '24 days'::interval,
36 checkin_scan_time = NULL
38 UPDATE asset.copy SET status = 3 WHERE id = 11;
40 -- Two recently LOST items for Case 10: Interval Testing (1 hour interval)
41 -- - Item 1: Lost, paid more than 1 hour later, to be returned LESS than 1 hour after payment (via perl test)
42 -- - Item 2: Lost, paid more than 1 hour later, to be returned MORE than 1 hour after payment (via perl test)
43 UPDATE action.circulation SET
44 create_time = NOW() - '1 week'::interval,
45 xact_start = NOW() - '1 week'::interval,
46 due_date = NOW() + '1 day'::interval,
47 stop_fines_time = NOW() - '3 hours'::interval,
53 checkin_scan_time = NULL
55 UPDATE asset.copy SET status = 3 WHERE id IN (9, 10);
57 -- non-lost circs, used for Amnesty Mode check-ins
58 UPDATE action.circulation SET
59 xact_start = '2014-05-14 08:39:13.070326-04',
60 due_date = '2014-05-21 23:59:59-04',
61 stop_fines_time = '2014-05-28 08:39:13.070326-04',
62 create_time = '2014-05-14 08:39:13.070326-04',
64 stop_fines = 'MAXFINES',
68 checkin_scan_time = NULL
69 WHERE id IN (7, 14, 16);
70 UPDATE asset.copy SET status = 1 WHERE id IN (8, 15, 17);
72 -- Setup a non-lost, maxfines circ
73 UPDATE action.circulation SET
74 xact_start = '2014-05-14 08:39:13.070326-04',
75 due_date = '2014-05-21 23:59:59-04',
76 stop_fines_time = '2014-05-28 08:39:13.070326-04',
77 create_time = '2014-05-14 08:39:13.070326-04',
79 stop_fines = 'MAXFINES',
83 checkin_scan_time = NULL
85 UPDATE asset.copy SET status = 1 WHERE id = 12;
88 -- Create LOST and overdue fines
89 INSERT INTO money.billing (id, xact, billing_ts, voided, voider, void_time, amount, billing_type, btype, note) VALUES
90 (DEFAULT, 1, '2014-05-28 08:39:13.070326-04', false, NULL, NULL, 50.00, 'Lost Materials', 3, 'SYSTEM GENERATED'),
91 (DEFAULT, 2, '2014-05-28 08:39:13.070326-04', false, NULL, NULL, 50.00, 'Lost Materials', 3, 'SYSTEM GENERATED'),
92 (DEFAULT, 3, '2014-05-28 08:39:13.070326-04', false, NULL, NULL, 50.00, 'Lost Materials', 3, 'SYSTEM GENERATED'),
93 (DEFAULT, 4, '2014-05-28 08:39:13.070326-04', false, NULL, NULL, 50.00, 'Lost Materials', 3, 'SYSTEM GENERATED'),
94 (DEFAULT, 5, '2014-05-28 08:39:13.070326-04', false, NULL, NULL, 50.00, 'Lost Materials', 3, 'SYSTEM GENERATED'),
95 (DEFAULT, 6, '2014-05-22 23:59:59-04', true, 1, '2014-05-28 08:39:13.070326-04', 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
96 (DEFAULT, 6, '2014-05-23 23:59:59-04', true, 1, '2014-05-28 08:39:13.070326-04', 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
97 (DEFAULT, 6, '2014-05-24 23:59:59-04', true, 1, '2014-05-28 08:39:13.070326-04', 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
98 (DEFAULT, 6, '2014-05-25 23:59:59-04', true, 1, '2014-05-28 08:39:13.070326-04', 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
99 (DEFAULT, 6, '2014-05-26 23:59:59-04', true, 1, '2014-05-28 08:39:13.070326-04', 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
100 (DEFAULT, 6, '2014-05-27 23:59:59-04', true, 1, '2014-05-28 08:39:13.070326-04', 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
101 (DEFAULT, 6, '2014-05-28 23:59:59-04', true, 1, '2014-05-28 08:39:13.070326-04', 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
102 (DEFAULT, 6, '2014-05-28 08:39:13.070326-04', false, NULL, NULL, 50.00, 'Lost Materials', 3, 'SYSTEM GENERATED'),
103 (DEFAULT, 7, '2014-05-22 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
104 (DEFAULT, 7, '2014-05-23 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
105 (DEFAULT, 7, '2014-05-24 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
106 (DEFAULT, 7, '2014-05-25 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
107 (DEFAULT, 7, '2014-05-26 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
108 (DEFAULT, 7, '2014-05-27 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
109 (DEFAULT, 7, '2014-05-28 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
110 (DEFAULT, 8, NOW() - '2 hours'::interval, false, NULL, NULL, 50.00, 'Lost Materials', 3, 'SYSTEM GENERATED'),
111 (DEFAULT, 9, NOW() - '4 hours'::interval, false, NULL, NULL, 50.00, 'Lost Materials', 3, 'SYSTEM GENERATED'),
112 (DEFAULT, 11, '2014-05-22 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
113 (DEFAULT, 11, '2014-05-23 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
114 (DEFAULT, 11, '2014-05-24 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
115 (DEFAULT, 11, '2014-05-25 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
116 (DEFAULT, 11, '2014-05-26 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
117 (DEFAULT, 11, '2014-05-27 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
118 (DEFAULT, 11, '2014-05-28 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
119 (DEFAULT, 12, '2014-05-22 23:59:59-04', true, 1, '2014-05-28 08:39:13.070326-04', 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
120 (DEFAULT, 12, '2014-05-23 23:59:59-04', true, 1, '2014-05-28 08:39:13.070326-04', 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
121 (DEFAULT, 12, '2014-05-24 23:59:59-04', true, 1, '2014-05-28 08:39:13.070326-04', 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
122 (DEFAULT, 12, '2014-05-25 23:59:59-04', true, 1, '2014-05-28 08:39:13.070326-04', 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
123 (DEFAULT, 12, '2014-05-26 23:59:59-04', true, 1, '2014-05-28 08:39:13.070326-04', 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
124 (DEFAULT, 12, '2014-05-27 23:59:59-04', true, 1, '2014-05-28 08:39:13.070326-04', 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
125 (DEFAULT, 12, '2014-05-28 23:59:59-04', true, 1, '2014-05-28 08:39:13.070326-04', 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
126 (DEFAULT, 12, '2014-05-28 08:39:13.070326-04', false, NULL, NULL, 50.00, 'Lost Materials', 3, 'SYSTEM GENERATED'),
127 (DEFAULT, 13, '2014-05-28 08:39:13.070326-04', false, NULL, NULL, 50.00, 'Lost Materials', 3, 'SYSTEM GENERATED'),
128 (DEFAULT, 14, '2014-05-22 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
129 (DEFAULT, 14, '2014-05-23 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
130 (DEFAULT, 14, '2014-05-24 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
131 (DEFAULT, 14, '2014-05-25 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
132 (DEFAULT, 14, '2014-05-26 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
133 (DEFAULT, 14, '2014-05-27 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
134 (DEFAULT, 14, '2014-05-28 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
135 (DEFAULT, 15, '2014-05-28 08:39:13.070326-04', false, NULL, NULL, 50.00, 'Lost Materials', 3, 'SYSTEM GENERATED'),
136 (DEFAULT, 16, '2014-05-22 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
137 (DEFAULT, 16, '2014-05-23 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
138 (DEFAULT, 16, '2014-05-24 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
139 (DEFAULT, 16, '2014-05-25 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
140 (DEFAULT, 16, '2014-05-26 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
141 (DEFAULT, 16, '2014-05-27 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
142 (DEFAULT, 16, '2014-05-28 23:59:59-04', false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
143 -- XACTS 5 and 10 must be last, because we use CURRVAL() to put their IDs in the account adjustments
144 (DEFAULT, 5, '2014-05-22 23:59:59-04', false, NULL, '2014-05-28 08:39:13.070326-04', 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
145 (DEFAULT, 5, '2014-05-23 23:59:59-04', false, NULL, '2014-05-28 08:39:13.070326-04', 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
146 (DEFAULT, 5, '2014-05-24 23:59:59-04', false, NULL, '2014-05-28 08:39:13.070326-04', 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
147 (DEFAULT, 5, '2014-05-25 23:59:59-04', false, NULL, '2014-05-28 08:39:13.070326-04', 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
148 (DEFAULT, 5, '2014-05-26 23:59:59-04', false, NULL, '2014-05-28 08:39:13.070326-04', 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
149 (DEFAULT, 5, '2014-05-27 23:59:59-04', false, NULL, '2014-05-28 08:39:13.070326-04', 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
150 (DEFAULT, 5, '2014-05-28 23:59:59-04', false, NULL, '2014-05-28 08:39:13.070326-04', 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
151 (DEFAULT, 10, (DATE(NOW() - '9 days'::interval) || ' 23:59:59')::TIMESTAMP, false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
152 (DEFAULT, 10, (DATE(NOW() - '8 days'::interval) || ' 23:59:59')::TIMESTAMP, false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
153 (DEFAULT, 10, (DATE(NOW() - '7 days'::interval) || ' 23:59:59')::TIMESTAMP, false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
154 (DEFAULT, 10, (DATE(NOW() - '6 days'::interval) || ' 23:59:59')::TIMESTAMP, false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
155 (DEFAULT, 10, (DATE(NOW() - '5 days'::interval) || ' 23:59:59')::TIMESTAMP, false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
156 (DEFAULT, 10, (DATE(NOW() - '4 days'::interval) || ' 23:59:59')::TIMESTAMP, false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
157 (DEFAULT, 10, (DATE(NOW() - '3 days'::interval) || ' 23:59:59')::TIMESTAMP, false, NULL, NULL, 0.10, 'Overdue materials', 1, 'System Generated Overdue Fine'),
158 (DEFAULT, 10, NOW() - '3 days'::interval, false, NULL, NULL, 50.00, 'Lost Materials', 3, 'SYSTEM GENERATED');
161 INSERT INTO money.account_adjustment (id, xact, payment_ts, voided, amount, note, amount_collected, accepting_usr, billing) VALUES
162 (DEFAULT, 5, '2014-05-28 08:39:13.070326-04', false, 0.10, '', 0.10, 1, CURRVAL('money.billing_id_seq') - 14),
163 (DEFAULT, 5, '2014-05-28 08:39:13.070326-04', false, 0.10, '', 0.10, 1, CURRVAL('money.billing_id_seq') - 13),
164 (DEFAULT, 5, '2014-05-28 08:39:13.070326-04', false, 0.10, '', 0.10, 1, CURRVAL('money.billing_id_seq') - 12),
165 (DEFAULT, 5, '2014-05-28 08:39:13.070326-04', false, 0.10, '', 0.10, 1, CURRVAL('money.billing_id_seq') - 11),
166 (DEFAULT, 5, '2014-05-28 08:39:13.070326-04', false, 0.10, '', 0.10, 1, CURRVAL('money.billing_id_seq') - 10),
167 (DEFAULT, 5, '2014-05-28 08:39:13.070326-04', false, 0.10, '', 0.10, 1, CURRVAL('money.billing_id_seq') - 9),
168 (DEFAULT, 5, '2014-05-28 08:39:13.070326-04', false, 0.10, '', 0.10, 1, CURRVAL('money.billing_id_seq') - 8),
169 (DEFAULT, 10, NOW() - '3 days'::interval, false, 0.10, '', 0.10, 1, CURRVAL('money.billing_id_seq') - 7),
170 (DEFAULT, 10, NOW() - '3 days'::interval, false, 0.10, '', 0.10, 1, CURRVAL('money.billing_id_seq') - 6),
171 (DEFAULT, 10, NOW() - '3 days'::interval, false, 0.10, '', 0.10, 1, CURRVAL('money.billing_id_seq') - 5),
172 (DEFAULT, 10, NOW() - '3 days'::interval, false, 0.10, '', 0.10, 1, CURRVAL('money.billing_id_seq') - 4),
173 (DEFAULT, 10, NOW() - '3 days'::interval, false, 0.10, '', 0.10, 1, CURRVAL('money.billing_id_seq') - 3),
174 (DEFAULT, 10, NOW() - '3 days'::interval, false, 0.10, '', 0.10, 1, CURRVAL('money.billing_id_seq') - 2),
175 (DEFAULT, 10, NOW() - '3 days'::interval, false, 0.10, '', 0.10, 1, CURRVAL('money.billing_id_seq') - 1);
177 INSERT INTO money.cash_payment (id, xact, payment_ts, voided, amount, note, amount_collected, accepting_usr, cash_drawer) VALUES
178 (DEFAULT, 8, NOW() - '30 minutes'::interval, false, 50.00, 'LOST payment', 50.00, 1, 51),
179 (DEFAULT, 9, NOW() - '2 hours'::interval, false, 50.00, 'LOST payment', 50.00, 1, 51),
180 (DEFAULT, 10, NOW() - '2 days'::interval, false, 10.00, 'Partial LOST payment', 10.00, 1, 51);
182 -- if rerunning, make sure our mangled bills have the right total in the summary
183 UPDATE money.materialized_billable_xact_summary SET balance_owed = 50.00
184 WHERE id IN (1,2,3,4,5,6,12,13,15);
185 UPDATE money.materialized_billable_xact_summary SET balance_owed = 0.70
186 WHERE id IN (7, 14, 16);
187 UPDATE money.materialized_billable_xact_summary SET balance_owed = 0.00
189 UPDATE money.materialized_billable_xact_summary SET balance_owed = 40.00
191 UPDATE money.materialized_billable_xact_summary SET balance_owed = 0.70