Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
Total | |
0.63% |
3 / 473 |
|
4.00% |
1 / 25 |
CRAP | |
0.00% |
0 / 1 |
Horizon | |
0.63% |
3 / 473 |
|
4.00% |
1 / 25 |
7342.16 | |
0.00% |
0 / 1 |
__construct | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
init | |
12.50% |
2 / 16 |
|
0.00% |
0 / 1 |
9.03 | |||
buildSqlFromArray | |
0.00% |
0 / 15 |
|
0.00% |
0 / 1 |
42 | |||
parseStatus | |
0.00% |
0 / 45 |
|
0.00% |
0 / 1 |
210 | |||
getHoldingSQL | |
0.00% |
0 / 12 |
|
0.00% |
0 / 1 |
2 | |||
processHoldingRow | |
0.00% |
0 / 24 |
|
0.00% |
0 / 1 |
20 | |||
getHolding | |
0.00% |
0 / 11 |
|
0.00% |
0 / 1 |
12 | |||
processStatusRow | |
0.00% |
0 / 11 |
|
0.00% |
0 / 1 |
2 | |||
getStatus | |
0.00% |
0 / 3 |
|
0.00% |
0 / 1 |
2 | |||
getStatusesSQL | |
0.00% |
0 / 10 |
|
0.00% |
0 / 1 |
2 | |||
getStatuses | |
0.00% |
0 / 16 |
|
0.00% |
0 / 1 |
20 | |||
getPurchaseHistory | |
0.00% |
0 / 1 |
|
0.00% |
0 / 1 |
2 | |||
patronLogin | |
0.00% |
0 / 30 |
|
0.00% |
0 / 1 |
12 | |||
getHoldsSQL | |
0.00% |
0 / 12 |
|
0.00% |
0 / 1 |
2 | |||
processHoldsRow | |
0.00% |
0 / 36 |
|
0.00% |
0 / 1 |
72 | |||
getMyHolds | |
0.00% |
0 / 13 |
|
0.00% |
0 / 1 |
20 | |||
getMyFines | |
0.00% |
0 / 77 |
|
0.00% |
0 / 1 |
12 | |||
getMyProfile | |
0.00% |
0 / 33 |
|
0.00% |
0 / 1 |
12 | |||
getTransactionSQL | |
0.00% |
0 / 11 |
|
0.00% |
0 / 1 |
2 | |||
processTransactionsRow | |
0.00% |
0 / 29 |
|
0.00% |
0 / 1 |
30 | |||
getMyTransactions | |
0.00% |
0 / 11 |
|
0.00% |
0 / 1 |
12 | |||
getFunds | |
0.00% |
0 / 1 |
|
0.00% |
0 / 1 |
2 | |||
getNewItems | |
0.00% |
0 / 24 |
|
0.00% |
0 / 1 |
30 | |||
checkHzVersion | |
0.00% |
0 / 20 |
|
0.00% |
0 / 1 |
42 | |||
getSuppressedRecords | |
0.00% |
0 / 11 |
|
0.00% |
0 / 1 |
12 |
1 | <?php |
2 | |
3 | /** |
4 | * Horizon ILS Driver |
5 | * |
6 | * PHP version 8 |
7 | * |
8 | * Copyright (C) Villanova University 2007. |
9 | * |
10 | * This program is free software; you can redistribute it and/or modify |
11 | * it under the terms of the GNU General Public License version 2, |
12 | * as published by the Free Software Foundation. |
13 | * |
14 | * This program is distributed in the hope that it will be useful, |
15 | * but WITHOUT ANY WARRANTY; without even the implied warranty of |
16 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
17 | * GNU General Public License for more details. |
18 | * |
19 | * You should have received a copy of the GNU General Public License |
20 | * along with this program; if not, write to the Free Software |
21 | * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA |
22 | * |
23 | * @category VuFind |
24 | * @package ILS_Drivers |
25 | * @author Matt Mackey <vufind-tech@lists.sourceforge.net> |
26 | * @author Ray Cummins <vufind-tech@lists.sourceforge.net> |
27 | * @license http://opensource.org/licenses/gpl-2.0.php GNU General Public License |
28 | * @link https://vufind.org/wiki/development:plugins:ils_drivers Wiki |
29 | */ |
30 | |
31 | namespace VuFind\ILS\Driver; |
32 | |
33 | use Laminas\Log\LoggerAwareInterface; |
34 | use PDO; |
35 | use VuFind\Date\DateException; |
36 | use VuFind\Exception\ILS as ILSException; |
37 | use VuFind\Log\LoggerAwareTrait; |
38 | |
39 | use function count; |
40 | use function in_array; |
41 | use function intval; |
42 | |
43 | /** |
44 | * Horizon ILS Driver |
45 | * |
46 | * @category VuFind |
47 | * @package ILS_Drivers |
48 | * @author Matt Mackey <vufind-tech@lists.sourceforge.net> |
49 | * @author Ray Cummins <vufind-tech@lists.sourceforge.net> |
50 | * @license http://opensource.org/licenses/gpl-2.0.php GNU General Public License |
51 | * @link https://vufind.org/wiki/development:plugins:ils_drivers Wiki |
52 | */ |
53 | class Horizon extends AbstractBase implements LoggerAwareInterface |
54 | { |
55 | use LoggerAwareTrait; |
56 | |
57 | /** |
58 | * Date converter object |
59 | * |
60 | * @var \VuFind\Date\Converter |
61 | */ |
62 | protected $dateFormat; |
63 | |
64 | /** |
65 | * Database connection |
66 | * |
67 | * @var PDO |
68 | */ |
69 | protected $db; |
70 | |
71 | /** |
72 | * Constructor |
73 | * |
74 | * @param \VuFind\Date\Converter $dateConverter Date converter object |
75 | */ |
76 | public function __construct(\VuFind\Date\Converter $dateConverter) |
77 | { |
78 | $this->dateFormat = $dateConverter; |
79 | } |
80 | |
81 | /** |
82 | * Initialize the driver. |
83 | * |
84 | * Validate configuration and perform all resource-intensive tasks needed to |
85 | * make the driver active. |
86 | * |
87 | * @throws ILSException |
88 | * @return void |
89 | */ |
90 | public function init() |
91 | { |
92 | if (empty($this->config)) { |
93 | throw new ILSException('Configuration needs to be set.'); |
94 | } |
95 | |
96 | // Connect to database |
97 | try { |
98 | $this->db = new PDO( |
99 | 'dblib:host=' . $this->config['Catalog']['host'] . |
100 | ':' . $this->config['Catalog']['port'] . |
101 | ';dbname=' . $this->config['Catalog']['database'], |
102 | $this->config['Catalog']['username'], |
103 | $this->config['Catalog']['password'] |
104 | ); |
105 | |
106 | // throw an exception instead of false on sql errors |
107 | $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); |
108 | } catch (\Exception $e) { |
109 | $this->logError($e->getMessage()); |
110 | $this->throwAsIlsException( |
111 | $e, |
112 | 'ILS Configuration problem : ' . $e->getMessage() |
113 | ); |
114 | } |
115 | } |
116 | |
117 | /** |
118 | * Protected support method for building sql strings. |
119 | * |
120 | * @param array $sql An array of keyed sql data |
121 | * |
122 | * @return array An string query string |
123 | */ |
124 | protected function buildSqlFromArray($sql) |
125 | { |
126 | $modifier = isset($sql['modifier']) ? $sql['modifier'] . ' ' : ''; |
127 | |
128 | // Put String Together |
129 | $sqlString = 'select ' . $modifier . implode(', ', $sql['expressions']); |
130 | $sqlString .= ' from ' . implode(', ', $sql['from']); |
131 | $sqlString .= (!empty($sql['join'])) |
132 | ? ' join ' . implode(' join ', $sql['join']) : ''; |
133 | $sqlString .= (!empty($sql['innerJoin'])) |
134 | ? ' inner join ' . implode(' inner join ', $sql['innerJoin']) : ''; |
135 | $sqlString .= (!empty($sql['leftOuterJoin'])) |
136 | ? ' left outer join ' |
137 | . implode(' left outer join ', $sql['leftOuterJoin']) |
138 | : ''; |
139 | $sqlString .= ' where ' . implode(' AND ', $sql['where']); |
140 | $sqlString .= (!empty($sql['order'])) |
141 | ? ' ORDER BY ' . implode(', ', $sql['order']) : ''; |
142 | |
143 | return $sqlString; |
144 | } |
145 | |
146 | /** |
147 | * Protected support method determine availability, reserve and duedate values |
148 | * based on item status. Used by getHolding, getStatus and getStatuses. |
149 | * |
150 | * @param string $status Item status code |
151 | * |
152 | * @return array |
153 | */ |
154 | protected function parseStatus($status) |
155 | { |
156 | $duedate = null; |
157 | $statuses = $this->config['Statuses'][$status] ?? null; |
158 | $reserve = 'N'; |
159 | $available = 0; |
160 | |
161 | // query the config file for the item status if there are |
162 | // config values, use the configuration otherwise execute the switch |
163 | if (!$statuses == null) { |
164 | // break out the values |
165 | $arrayValues = array_map('strtolower', explode(',', $statuses)); |
166 | |
167 | //set the variables based on what we find in the config file |
168 | if (in_array(strtolower('available:1'), $arrayValues)) { |
169 | $available = 1; |
170 | } |
171 | if (in_array(strtolower('available:0'), $arrayValues)) { |
172 | $available = 0; |
173 | } |
174 | if (in_array(strtolower('reserve:N'), $arrayValues)) { |
175 | $reserve = 'N'; |
176 | } |
177 | if (in_array(strtolower('reserve:Y'), $arrayValues)) { |
178 | $reserve = 'Y'; |
179 | } |
180 | if (in_array(strtolower('duedate:0'), $arrayValues)) { |
181 | $duedate = ''; |
182 | } |
183 | } else { |
184 | switch ($status) { |
185 | case 'i': // checked in |
186 | $available = 1; |
187 | $reserve = 'N'; |
188 | break; |
189 | case 'rb': // Reserve Bookroom |
190 | $available = 0; |
191 | $reserve = 'Y'; |
192 | break; |
193 | case 'h': // being held |
194 | $available = 0; |
195 | $reserve = 'N'; |
196 | break; |
197 | case 'l': // lost |
198 | $available = 0; |
199 | $reserve = 'N'; |
200 | $duedate = ''; // No due date for lost items |
201 | break; |
202 | case 'm': // missing |
203 | $available = 0; |
204 | $reserve = 'N'; |
205 | $duedate = ''; // No due date for missing items |
206 | break; |
207 | default: |
208 | $available = 0; |
209 | $reserve = 'N'; |
210 | break; |
211 | } |
212 | } |
213 | |
214 | $statusValues = ['available' => $available, |
215 | 'reserve' => $reserve]; |
216 | |
217 | if (isset($duedate)) { |
218 | $statusValues += ['duedate' => $duedate]; |
219 | } |
220 | return $statusValues; |
221 | } |
222 | |
223 | /** |
224 | * Protected support method for getHolding. |
225 | * |
226 | * @param array $id A Bibliographic id |
227 | * |
228 | * @return array Keyed data for use in an sql query |
229 | */ |
230 | protected function getHoldingSQL($id) |
231 | { |
232 | // Query holding information based on id field defined in |
233 | // import/marc.properties |
234 | // Expressions |
235 | $sqlExpressions = [ |
236 | 'i.item# as ITEM_ID', |
237 | 'i.item_status as STATUS_CODE', |
238 | 'ist.descr as STATUS', |
239 | 'l.name as LOCATION', |
240 | 'i.call_reconstructed as CALLNUMBER', |
241 | 'i.ibarcode as ITEM_BARCODE', |
242 | 'convert(varchar(10), ' . |
243 | " dateadd(dd,i.due_date,'jan 1 1970'), " . |
244 | ' 101) as DUEDATE', |
245 | 'i.copy_reconstructed as NUMBER', |
246 | 'convert(varchar(10), ' . |
247 | " dateadd(dd,ch.cki_date,'jan 1 1970'), " . |
248 | ' 101) as RETURNDATE', |
249 | '(select count(*) |
250 | from request r |
251 | where r.bib# = i.bib# |
252 | and r.reactivate_date = NULL) as REQUEST', |
253 | 'i.notes as NOTES', |
254 | 'ist.available_for_request IS_HOLDABLE', |
255 | |
256 | ]; |
257 | |
258 | // From |
259 | $sqlFrom = ['item i']; |
260 | |
261 | // inner Join |
262 | $sqlInnerJoin = [ |
263 | 'item_status ist on i.item_status = ist.item_status', |
264 | 'location l on i.location = l.location', |
265 | ]; |
266 | |
267 | $sqlLeftOuterJoin = [ |
268 | 'circ_history ch on ch.item# = i.item#', |
269 | ]; |
270 | |
271 | // Where |
272 | $sqlWhere = [ |
273 | 'i.bib# = ' . addslashes($id), |
274 | 'i.staff_only = 0', |
275 | ]; |
276 | |
277 | $sqlArray = [ |
278 | 'expressions' => $sqlExpressions, |
279 | 'from' => $sqlFrom, |
280 | 'innerJoin' => $sqlInnerJoin, |
281 | 'leftOuterJoin' => $sqlLeftOuterJoin, |
282 | 'where' => $sqlWhere, |
283 | ]; |
284 | |
285 | return $sqlArray; |
286 | } |
287 | |
288 | /** |
289 | * Protected support method for getHolding. |
290 | * |
291 | * @param string $id Bib Id |
292 | * @param array $row SQL Row Data |
293 | * @param array $patron Patron Array |
294 | * |
295 | * @return array Keyed data |
296 | * |
297 | * @SuppressWarnings(PHPMD.UnusedFormalParameter) |
298 | */ |
299 | protected function processHoldingRow($id, $row, $patron) |
300 | { |
301 | $duedate = $row['DUEDATE']; |
302 | $item_status = $row['STATUS_CODE']; //get the item status code |
303 | |
304 | $statusValues = $this->parseStatus($item_status); |
305 | |
306 | if (isset($statusValues['duedate'])) { |
307 | $duedate = $statusValues['duedate']; |
308 | } |
309 | |
310 | $holding = [ |
311 | 'id' => $id, |
312 | 'availability' => $statusValues['available'], |
313 | 'item_id' => $row['ITEM_ID'], |
314 | 'status' => $row['STATUS'], |
315 | 'location' => $row['LOCATION'], |
316 | 'reserve' => $statusValues['reserve'], |
317 | 'callnumber' => $row['CALLNUMBER'], |
318 | 'duedate' => $duedate, |
319 | 'returnDate' => $row['RETURNDATE'], |
320 | 'barcode' => $row['ITEM_BARCODE'], |
321 | 'requests_placed' => $row['REQUEST'], |
322 | 'is_holdable' => $row['IS_HOLDABLE'], |
323 | |
324 | ]; |
325 | |
326 | // Only set the number key if there is actually volume data |
327 | if ($row['NUMBER'] != '') { |
328 | $holding += ['number' => $row['NUMBER']]; |
329 | } |
330 | |
331 | // Only set the notes key if there are actually notes to display |
332 | if ($row['NOTES'] != '') { |
333 | $holding += ['notes' => [$row['NOTES']]]; |
334 | } |
335 | |
336 | return $holding; |
337 | } |
338 | |
339 | /** |
340 | * Get Holding |
341 | * |
342 | * This is responsible for retrieving the holding information of a certain |
343 | * record. |
344 | * |
345 | * @param string $id The record id to retrieve the holdings for |
346 | * @param array $patron Patron data |
347 | * @param array $options Extra options (not currently used) |
348 | * |
349 | * @throws DateException |
350 | * @throws ILSException |
351 | * @return array On success, an associative array with the following |
352 | * keys: id, availability (boolean), status, location, reserve, callnumber, |
353 | * duedate, number, barcode. |
354 | * |
355 | * @SuppressWarnings(PHPMD.UnusedFormalParameter) |
356 | */ |
357 | public function getHolding($id, array $patron = null, array $options = []) |
358 | { |
359 | $sqlArray = $this->getHoldingSql($id); |
360 | $sql = $this->buildSqlFromArray($sqlArray); |
361 | |
362 | $holding = []; |
363 | try { |
364 | $sqlStmt = $this->db->query($sql); |
365 | foreach ($sqlStmt as $row) { |
366 | $holding[] = $this->processHoldingRow($id, $row, $patron); |
367 | } |
368 | |
369 | $this->debug(json_encode($holding)); |
370 | } catch (\Exception $e) { |
371 | $this->logError($e->getMessage()); |
372 | $this->throwAsIlsException($e); |
373 | } |
374 | return $holding; |
375 | } |
376 | |
377 | /** |
378 | * Protected support method for getStatuses. |
379 | * |
380 | * @param string $id Bib Id |
381 | * @param array $row SQL Row Data |
382 | * |
383 | * @return array Keyed data |
384 | */ |
385 | protected function processStatusRow($id, $row) |
386 | { |
387 | $item_status = $row['STATUS_CODE']; //get the item status code |
388 | $statusValues = $this->parseStatus($item_status); |
389 | |
390 | $status = [ |
391 | 'id' => $id, |
392 | 'availability' => $statusValues['available'], |
393 | 'status' => $row['STATUS'], |
394 | 'location' => $row['LOCATION'], |
395 | 'reserve' => $statusValues['reserve'], |
396 | 'callnumber' => $row['CALLNUMBER'], |
397 | ]; |
398 | |
399 | return $status; |
400 | } |
401 | |
402 | /** |
403 | * Get Status |
404 | * |
405 | * This is responsible for retrieving the status information of a specific |
406 | * record. It is a proxy to getStatuses. |
407 | * |
408 | * @param string $id The record id to retrieve the holdings for |
409 | * |
410 | * @return mixed On success, an associative array with the following keys: |
411 | * id, availability (boolean), status, location, reserve, and |
412 | * callnumber. |
413 | */ |
414 | public function getStatus($id) |
415 | { |
416 | $idList = [$id]; |
417 | $status = $this->getStatuses($idList); |
418 | return current($status); |
419 | } |
420 | |
421 | /** |
422 | * Protected support method for getStatus. |
423 | * |
424 | * @param array $idList A list of Bibliographic id |
425 | * |
426 | * @return array Keyed data for use in an sql query |
427 | */ |
428 | protected function getStatusesSQL($idList) |
429 | { |
430 | // Query holding information based on id field defined in |
431 | // import/marc.properties |
432 | // Expressions |
433 | $sqlExpressions = ['i.bib# as ID', |
434 | 'i.item_status as STATUS_CODE', |
435 | 'ist.descr as STATUS', |
436 | 'l.name as LOCATION', |
437 | 'i.call_reconstructed as CALLNUMBER']; |
438 | |
439 | // From |
440 | $sqlFrom = ['item i']; |
441 | |
442 | // inner Join |
443 | $sqlInnerJoin = ['item_status ist on i.item_status = ist.item_status', |
444 | 'location l on i.location = l.location']; |
445 | |
446 | $bibIDs = implode(',', $idList); |
447 | |
448 | // Where |
449 | $sqlWhere = ['i.bib# in (' . $bibIDs . ')', |
450 | 'i.staff_only = 0']; |
451 | |
452 | $sqlArray = [ |
453 | 'expressions' => $sqlExpressions, |
454 | 'from' => $sqlFrom, |
455 | 'innerJoin' => $sqlInnerJoin, |
456 | 'where' => $sqlWhere, |
457 | ]; |
458 | |
459 | return $sqlArray; |
460 | } |
461 | |
462 | /** |
463 | * Get Statuses |
464 | * |
465 | * This is responsible for retrieving the status information for a collection of |
466 | * records. |
467 | * |
468 | * @param array $idList The array of record ids to retrieve the status for |
469 | * |
470 | * @throws ILSException |
471 | * @return array An array of getStatus() return values on success. |
472 | */ |
473 | public function getStatuses($idList) |
474 | { |
475 | // Make sure we only give Horizon integers |
476 | $callback = function ($i) { |
477 | return preg_match('/^[0-9]+$/', $i); |
478 | }; |
479 | $idList = array_filter($idList, $callback); |
480 | |
481 | // Skip DB call if we have no valid IDs. |
482 | if (empty($idList)) { |
483 | return []; |
484 | } |
485 | |
486 | $sqlArray = $this->getStatusesSQL($idList); |
487 | $sql = $this->buildSqlFromArray($sqlArray); |
488 | |
489 | $status = []; |
490 | try { |
491 | $sqlStmt = $this->db->query($sql); |
492 | foreach ($sqlStmt as $row) { |
493 | $id = $row['ID']; |
494 | $status[$id][] = $this->processStatusRow($id, $row); |
495 | } |
496 | } catch (\Exception $e) { |
497 | $this->logError($e->getMessage()); |
498 | $this->throwAsIlsException($e); |
499 | } |
500 | return $status; |
501 | } |
502 | |
503 | /** |
504 | * Get Purchase History |
505 | * |
506 | * This is responsible for retrieving the acquisitions history data for the |
507 | * specific record (usually recently received issues of a serial). |
508 | * |
509 | * @param string $id The record id to retrieve the info for |
510 | * |
511 | * @throws ILSException |
512 | * @return array An array with the acquisitions data on success. |
513 | */ |
514 | public function getPurchaseHistory($id) |
515 | { |
516 | return []; |
517 | } |
518 | |
519 | /** |
520 | * Patron Login |
521 | * |
522 | * This is responsible for authenticating a patron against the catalog. |
523 | * |
524 | * @param string $username The patron username |
525 | * @param string $password The patron's password |
526 | * |
527 | * @throws ILSException |
528 | * @return mixed Associative array of patron info on successful login, |
529 | * ILSException on unsuccessful login. |
530 | */ |
531 | public function patronLogin($username, $password) |
532 | { |
533 | $sql = 'select name_reconstructed as FULLNAME, ' . |
534 | 'email_address as EMAIL ' . |
535 | 'from borrower ' . |
536 | 'left outer join borrower_address on ' . |
537 | 'borrower_address.borrower# = borrower.borrower# ' . |
538 | 'inner join borrower_barcode on ' . |
539 | 'borrower.borrower# = borrower_barcode.borrower# ' . |
540 | 'where borrower_barcode.bbarcode = ' . |
541 | "'" . addslashes($username) . "' " . |
542 | "and pin# = '" . addslashes($password) . "'"; |
543 | |
544 | try { |
545 | $user = []; |
546 | |
547 | $sqlStmt = $this->db->query($sql); |
548 | foreach ($sqlStmt as $row) { |
549 | [$lastname, $firstname] = explode(', ', $row['FULLNAME']); |
550 | $user = [ |
551 | 'id' => $username, |
552 | 'firstname' => $firstname, |
553 | 'lastname' => $lastname, |
554 | 'cat_username' => $username, |
555 | 'cat_password' => $password, |
556 | 'email' => $row['EMAIL'], |
557 | 'major' => null, |
558 | 'college' => null, |
559 | ]; |
560 | |
561 | $this->debug(json_encode($user)); |
562 | |
563 | return $user; |
564 | } |
565 | |
566 | throw new ILSException('Unable to login patron ' . $username); |
567 | } catch (\Exception $e) { |
568 | $this->logError($e->getMessage()); |
569 | $this->throwAsIlsException($e); |
570 | } |
571 | } |
572 | |
573 | /** |
574 | * Protected support method for getMyHolds. |
575 | * |
576 | * @param array $patron Patron data for use in an sql query |
577 | * |
578 | * @return array Keyed data for use in an sql query |
579 | */ |
580 | protected function getHoldsSQL($patron) |
581 | { |
582 | // Expressions |
583 | $sqlExpressions = [ |
584 | 'r.bib# as BIB_NUM', |
585 | 'r.request# as REQNUM', |
586 | 'r.item# as ITEM_ID', |
587 | 'r.bib_queue_ord as POSITION', |
588 | 'l.name as LOCATION', |
589 | 'r.request_status as STATUS', |
590 | 'case when r.request_status = 1 ' . |
591 | 'then 0 ' . |
592 | 'else 1 ' . |
593 | 'end as SORT', |
594 | 't.processed as TITLE', |
595 | 'p.pubdate as PUBLICATION_YEAR', |
596 | 'i.volume as VOLUME', |
597 | "convert(varchar(12),dateadd(dd, r.hold_exp_date, '1 jan 1970')) " . |
598 | 'as HOLD_EXPIRE', |
599 | "convert(varchar(12),dateadd(dd, r.expire_date, '1 jan 1970')) " . |
600 | 'as REQUEST_EXPIRE', |
601 | "convert(varchar(12),dateadd(dd, r.request_date, '1 jan 1970')) " . |
602 | 'as CREATED', |
603 | ]; |
604 | |
605 | // From |
606 | $sqlFrom = ['request r']; |
607 | |
608 | // Join |
609 | $sqlJoin = [ |
610 | 'borrower_barcode bb on bb.borrower# = r.borrower#', |
611 | 'location l on l.location = r.pickup_location', |
612 | 'title t on t.bib# = r.bib#', |
613 | ]; |
614 | |
615 | $sqlLeftOuterJoin = [ |
616 | 'item i on i.item# = r.item#', |
617 | 'pubdate_inverted p on p.bib# = r.bib#', |
618 | ]; |
619 | |
620 | // Where |
621 | $sqlWhere = [ |
622 | "bb.bbarcode='" . addslashes($patron['id']) . "'", |
623 | ]; |
624 | |
625 | $sqlOrder = [ |
626 | 'SORT', |
627 | 't.processed', |
628 | ]; |
629 | |
630 | $sqlArray = [ |
631 | 'expressions' => $sqlExpressions, |
632 | 'from' => $sqlFrom, |
633 | 'join' => $sqlJoin, |
634 | 'leftOuterJoin' => $sqlLeftOuterJoin, |
635 | 'where' => $sqlWhere, |
636 | 'order' => $sqlOrder, |
637 | ]; |
638 | |
639 | return $sqlArray; |
640 | } |
641 | |
642 | /** |
643 | * Protected support method for getMyHolds. |
644 | * |
645 | * @param array $row An sql row |
646 | * |
647 | * @throws DateException |
648 | * @return array Keyed data |
649 | */ |
650 | protected function processHoldsRow($row) |
651 | { |
652 | if ($row['STATUS'] != 6) { |
653 | $position = ($row['STATUS'] != 1) ? $row['POSITION'] : false; |
654 | $available = ($row['STATUS'] == 1) ? true : false; |
655 | $expire = false; |
656 | $create = false; |
657 | // Convert Horizon Format to display format |
658 | if (!empty($row['HOLD_EXPIRE'])) { |
659 | $expire = $this->dateFormat->convertToDisplayDate( |
660 | 'M d Y', |
661 | trim($row['HOLD_EXPIRE']) |
662 | ); |
663 | } elseif (!empty($row['REQUEST_EXPIRE'])) { |
664 | // If there is no Hold Expiration date fall back to the |
665 | // Request Expiration date. |
666 | $expire = $this->dateFormat->convertToDisplayDate( |
667 | 'M d Y', |
668 | trim($row['REQUEST_EXPIRE']) |
669 | ); |
670 | } elseif ($row['STATUS'] == 2) { |
671 | // Items that are 'In Transit' have no expiration date. |
672 | $expire = 'In Transit'; |
673 | } else { |
674 | // Just in case we missed a possible scenario |
675 | $expire = false; |
676 | } |
677 | if (!empty($row['CREATED'])) { |
678 | $create = $this->dateFormat->convertToDisplayDate( |
679 | 'M d Y', |
680 | trim($row['CREATED']) |
681 | ); |
682 | } |
683 | |
684 | return [ |
685 | 'id' => $row['BIB_NUM'], |
686 | 'location' => $row['LOCATION'], |
687 | 'reqnum' => $row['REQNUM'], |
688 | 'expire' => $expire, |
689 | 'create' => $create, |
690 | 'position' => $position, |
691 | 'available' => $available, |
692 | 'item_id' => $row['ITEM_ID'], |
693 | 'volume' => $row['VOLUME'], |
694 | 'publication_year' => $row['PUBLICATION_YEAR'], |
695 | 'title' => $row['TITLE'], |
696 | ]; |
697 | } |
698 | return false; |
699 | } |
700 | |
701 | /** |
702 | * Get Patron Holds |
703 | * |
704 | * This is responsible for retrieving all holds by a specific patron. |
705 | * |
706 | * @param array $patron The patron array from patronLogin |
707 | * |
708 | * @throws DateException |
709 | * @throws ILSException |
710 | * @return array Array of the patron's holds on success. |
711 | */ |
712 | public function getMyHolds($patron) |
713 | { |
714 | $holdList = []; |
715 | $sqlArray = $this->getHoldsSQL($patron); |
716 | $sql = $this->buildSqlFromArray($sqlArray); |
717 | |
718 | try { |
719 | $sqlStmt = $this->db->query($sql); |
720 | foreach ($sqlStmt as $row) { |
721 | $hold = $this->processHoldsRow($row); |
722 | if ($hold) { |
723 | $holdList[] = $hold; |
724 | } |
725 | } |
726 | |
727 | $this->debug(json_encode($holdList)); |
728 | } catch (\Exception $e) { |
729 | $this->logError($e->getMessage()); |
730 | $this->throwAsIlsException($e); |
731 | } |
732 | return $holdList; |
733 | } |
734 | |
735 | /** |
736 | * Get Patron Fines |
737 | * |
738 | * This is responsible for retrieving all fines by a specific patron. |
739 | * |
740 | * @param array $patron The patron array from patronLogin |
741 | * |
742 | * @throws DateException |
743 | * @throws ILSException |
744 | * @return mixed Array of the patron's fines on success. |
745 | */ |
746 | public function getMyFines($patron) |
747 | { |
748 | $sql = ' select bu.amount as AMOUNT ' . |
749 | ' , coalesce( ' . |
750 | ' convert(varchar(10), ' . |
751 | " dateadd(dd, i.last_cko_date, '01jan70'), " . |
752 | ' 101), ' . |
753 | ' convert(varchar(10), ' . |
754 | " dateadd(dd, bu2.date, '01jan70'), " . |
755 | ' 101)) as CHECKOUT ' . |
756 | ' , bl.descr as FINE ' . |
757 | ' , ( select sum(b2.amount) ' . |
758 | ' from burb b2 ' . |
759 | ' where b2.reference# = bu.reference# ' . |
760 | ' group by b2.reference#) as BALANCE ' . |
761 | ' , convert(varchar(10), ' . |
762 | " dateadd(dd, bu.date, '01jan70'), " . |
763 | ' 101) as CREATEDATE ' . |
764 | ' , coalesce( ' . |
765 | ' convert(varchar(10), ' . |
766 | " dateadd(dd, i.due_date, '01jan70'), " . |
767 | ' 101), ' . |
768 | ' convert(varchar(10), ' . |
769 | " dateadd(dd, bu3.date, '01jan70'), " . |
770 | ' 101)) as DUEDATE ' . |
771 | ' , i2.bib# as ID ' . |
772 | ' , coalesce (t.processed, bu4.comment) as TITLE ' . |
773 | ' , case when bl.amount_type = 0 ' . |
774 | ' then 0 ' . |
775 | ' else 1 ' . |
776 | ' end as FEEBLOCK ' . |
777 | ' from burb bu ' . |
778 | ' join block bl ' . |
779 | ' on bl.block = bu.block ' . |
780 | ' join borrower_barcode bb ' . |
781 | ' on bb.borrower# = bu.borrower# ' . |
782 | 'left join item i ' . |
783 | ' on i.item# = bu.item# ' . |
784 | ' and i.borrower# = bu.borrower# ' . |
785 | 'left join item i2 ' . |
786 | ' on i2.item# = bu.item# ' . |
787 | 'left join burb bu2 ' . |
788 | ' on bu2.reference# = bu.reference# ' . |
789 | " and bu2.block = 'infocko' " . |
790 | 'left join burb bu3 ' . |
791 | ' on bu3.reference# = bu.reference# ' . |
792 | " and bu3.block = 'infodue' " . |
793 | 'left join title t ' . |
794 | ' on t.bib# = i2.bib# ' . |
795 | 'left join burb bu4 ' . |
796 | ' on bu4.reference# = bu.reference# ' . |
797 | ' and bu4.ord = 0 ' . |
798 | " and bu4.block in ('l', 'LostPro','fine','he') " . |
799 | " where bb.bbarcode = '" . addslashes($patron['id']) . "' " . |
800 | ' and bu.ord = 0 ' . |
801 | ' and bl.pac_display = 1 ' . |
802 | ' order by FEEBLOCK desc ' . |
803 | ' , bu.item# ' . |
804 | ' , TITLE ' . |
805 | ' , bu.block ' . |
806 | ' , bu.date'; |
807 | |
808 | try { |
809 | $sqlStmt = $this->db->query($sql); |
810 | $fineList = []; |
811 | foreach ($sqlStmt as $row) { |
812 | $fineList[] = [ |
813 | 'amount' => $row['AMOUNT'], |
814 | 'checkout' => $row['CHECKOUT'], |
815 | 'fine' => $row['FINE'], |
816 | 'balance' => $row['BALANCE'], |
817 | 'createdate' => $row['CREATEDATE'], |
818 | 'duedate' => $row['DUEDATE'], |
819 | 'id' => $row['ID'], |
820 | 'title' => $row['TITLE'], |
821 | ]; |
822 | } |
823 | |
824 | $this->debug(json_encode($fineList)); |
825 | |
826 | return $fineList; |
827 | } catch (\Exception $e) { |
828 | $this->logError($e->getMessage()); |
829 | $this->throwAsIlsException($e); |
830 | } |
831 | } |
832 | |
833 | /** |
834 | * Get Patron Profile |
835 | * |
836 | * This is responsible for retrieving the profile for a specific patron. |
837 | * |
838 | * @param array $patron The patron array |
839 | * |
840 | * @throws ILSException |
841 | * @return array Array of the patron's profile data on success, |
842 | * throw ILSException if none found |
843 | */ |
844 | public function getMyProfile($patron) |
845 | { |
846 | $profile = []; |
847 | $sql = 'select name_reconstructed as FULLNAME, address1 as ADDRESS1, ' . |
848 | 'city_st.descr as ADDRESS2, postal_code as ZIP, phone_no as PHONE ' . |
849 | 'from borrower ' . |
850 | 'left outer join borrower_phone on ' . |
851 | 'borrower_phone.borrower#=borrower.borrower# ' . |
852 | 'inner join borrower_address on ' . |
853 | 'borrower_address.borrower#=borrower.borrower# ' . |
854 | 'inner join city_st on city_st.city_st=borrower_address.city_st ' . |
855 | 'inner join borrower_barcode on ' . |
856 | 'borrower_barcode.borrower# = borrower.borrower# ' . |
857 | "where borrower_barcode.bbarcode = '" . addslashes($patron['id']) . "'"; |
858 | |
859 | try { |
860 | $sqlStmt = $this->db->query($sql); |
861 | foreach ($sqlStmt as $row) { |
862 | [$lastname, $firstname] = explode(', ', $row['FULLNAME']); |
863 | $profile = [ |
864 | 'lastname' => $lastname, |
865 | 'firstname' => $firstname, |
866 | 'address1' => $row['ADDRESS1'], |
867 | 'address2' => $row['ADDRESS2'], |
868 | 'zip' => $row['ZIP'], |
869 | 'phone' => $row['PHONE'], |
870 | 'group' => null, |
871 | ]; |
872 | |
873 | $this->debug(json_encode($profile)); |
874 | |
875 | return $profile; |
876 | } |
877 | |
878 | throw new ILSException( |
879 | 'Unable to retrieve profile for patron ' . $patron['id'] |
880 | ); |
881 | } catch (\Exception $e) { |
882 | $this->logError($e->getMessage()); |
883 | $this->throwAsIlsException($e); |
884 | } |
885 | return $profile; |
886 | } |
887 | |
888 | /** |
889 | * Protected support method for getMyTransactions. |
890 | * |
891 | * @param array $patron Patron data for use in an sql query |
892 | * |
893 | * @return array Keyed data for use in an sql query |
894 | */ |
895 | protected function getTransactionSQL($patron) |
896 | { |
897 | // Expressions |
898 | $sqlExpressions = [ |
899 | "convert(varchar(12), dateadd(dd, i.due_date, '01 jan 1970')) " . |
900 | 'as DUEDATE', |
901 | 'i.bib# as BIB_NUM', |
902 | 'i.ibarcode as ITEM_BARCODE', |
903 | 'i.n_renewals as RENEW', |
904 | 'r.bib_queue_ord as REQUEST', |
905 | 'i.volume as VOLUME', |
906 | 'p.pubdate as PUBLICATION_YEAR', |
907 | 't.processed as TITLE', |
908 | 'i.item# as ITEM_NUM', |
909 | ]; |
910 | |
911 | // From |
912 | $sqlFrom = ['circ c']; |
913 | |
914 | // Join |
915 | $sqlJoin = [ |
916 | 'item i on i.item#=c.item#', |
917 | 'borrower b on b.borrower# = c.borrower#', |
918 | 'borrower_barcode bb on bb.borrower# = c.borrower#', |
919 | 'title t on t.bib# = i.bib#', |
920 | ]; |
921 | |
922 | // Left Outer Join |
923 | $sqlLeftOuterJoin = [ |
924 | 'request r on r.item#=c.item#', |
925 | 'pubdate_inverted p on p.bib# = i.bib#', |
926 | ]; |
927 | |
928 | // Where |
929 | $sqlWhere = [ |
930 | "bb.bbarcode='" . addslashes($patron['id']) . "'"]; |
931 | |
932 | // Order by |
933 | $sqlOrder = [ |
934 | 'i.due_date', |
935 | 't.processed', |
936 | ]; |
937 | |
938 | $sqlArray = [ |
939 | 'expressions' => $sqlExpressions, |
940 | 'from' => $sqlFrom, |
941 | 'join' => $sqlJoin, |
942 | 'leftOuterJoin' => $sqlLeftOuterJoin, |
943 | 'where' => $sqlWhere, |
944 | 'order' => $sqlOrder, |
945 | ]; |
946 | |
947 | return $sqlArray; |
948 | } |
949 | |
950 | /** |
951 | * Protected support method for getMyTransactions. |
952 | * |
953 | * @param array $row An array of keyed data |
954 | * |
955 | * @throws DateException |
956 | * @return array Keyed data for display by template files |
957 | */ |
958 | protected function processTransactionsRow($row) |
959 | { |
960 | $dueStatus = false; |
961 | $dueDate = $row['DUEDATE'] ?? null; |
962 | // Convert Horizon Format to display format |
963 | if (!empty($row['DUEDATE'])) { |
964 | $dueDate = $this->dateFormat->convertToDisplayDate( |
965 | 'M d Y', |
966 | trim($row['DUEDATE']) |
967 | ); |
968 | $now = time(); |
969 | $dueTimeStamp = $this->dateFormat->convertFromDisplayDate( |
970 | 'U', |
971 | $dueDate |
972 | ); |
973 | if (is_numeric($dueTimeStamp)) { |
974 | if ($now > $dueTimeStamp) { |
975 | $dueStatus = 'overdue'; |
976 | } elseif ($now > $dueTimeStamp - (1 * 24 * 60 * 60)) { |
977 | $dueStatus = 'due'; |
978 | } |
979 | } |
980 | } |
981 | |
982 | return [ |
983 | 'id' => $row['BIB_NUM'], |
984 | 'item_id' => $row['ITEM_NUM'], |
985 | 'duedate' => $dueDate, |
986 | 'barcode' => $row['ITEM_BARCODE'], |
987 | 'renew' => $row['RENEW'], |
988 | 'request' => $row['REQUEST'], |
989 | 'dueStatus' => $dueStatus, |
990 | 'volume' => $row['VOLUME'], |
991 | 'publication_year' => $row['PUBLICATION_YEAR'], |
992 | 'title' => $row['TITLE'], |
993 | ]; |
994 | } |
995 | |
996 | /** |
997 | * Get Patron Transactions |
998 | * |
999 | * This is responsible for retrieving all transactions (i.e. checked out items) |
1000 | * by a specific patron. |
1001 | * |
1002 | * @param array $patron The patron array from patronLogin |
1003 | * |
1004 | * @throws DateException |
1005 | * @throws ILSException |
1006 | * @return array Array of the patron's transactions on success. |
1007 | */ |
1008 | public function getMyTransactions($patron) |
1009 | { |
1010 | $transList = []; |
1011 | $sqlArray = $this->getTransactionSQL($patron); |
1012 | $sql = $this->buildSqlFromArray($sqlArray); |
1013 | |
1014 | try { |
1015 | $sqlStmt = $this->db->query($sql); |
1016 | foreach ($sqlStmt as $row) { |
1017 | $transList[] = $this->processTransactionsRow($row); |
1018 | } |
1019 | |
1020 | $this->debug(json_encode($transList)); |
1021 | } catch (\Exception $e) { |
1022 | $this->logError($e->getMessage()); |
1023 | $this->throwAsIlsException($e); |
1024 | } |
1025 | return $transList; |
1026 | } |
1027 | |
1028 | /** |
1029 | * Get Funds |
1030 | * |
1031 | * Return a list of funds which may be used to limit the getNewItems list. |
1032 | * |
1033 | * @throws ILSException |
1034 | * @return array An associative array with key = fund ID, value = fund name. |
1035 | */ |
1036 | public function getFunds() |
1037 | { |
1038 | // No funds for limiting in Horizon. |
1039 | return []; |
1040 | } |
1041 | |
1042 | /** |
1043 | * Get New Items |
1044 | * |
1045 | * Retrieve the IDs of items recently added to the catalog. |
1046 | * |
1047 | * The logic in this function follows the pattern used for the "New Additions" |
1048 | * functionality of the Horizon staff client. New Additions was delivered with |
1049 | * Horizon 7.4 and requires setup. Follow instructions in the "Circulation Setup |
1050 | * Guide". The minimum setup is to set the "Track First Availability" flag for |
1051 | * each appropriate item status. |
1052 | * |
1053 | * @param int $page Not implemented in this driver - Sybase does not have SQL |
1054 | * query paging functionality. |
1055 | * @param int $limit The maximum number of results to retrieve |
1056 | * @param int $daysOld The maximum age of records to retrieve in days (max. 30) |
1057 | * @param int $fundId Not implemented in this driver - The contributing library |
1058 | * does not use acquisitions. |
1059 | * |
1060 | * @return array Associative array with 'count' and 'results' keys |
1061 | * |
1062 | * @SuppressWarnings(PHPMD.UnusedFormalParameter) |
1063 | */ |
1064 | public function getNewItems($page, $limit, $daysOld, $fundId = null) |
1065 | { |
1066 | // This functionality first appeared in Horizon 7.4 - check our version |
1067 | $hzVersionRequired = '7.4.0.0'; |
1068 | if ($this->checkHzVersion($hzVersionRequired)) { |
1069 | // Set the Sybase or MSSQL rowcount limit (TODO: account for $page) |
1070 | $limitsql = "set rowcount {$limit}"; |
1071 | // for Sybase ASE 12.5 : "set rowcount $limit" |
1072 | |
1073 | // This is the actual query for IDs. |
1074 | $newsql = ' select nb.bib# ' |
1075 | . ' from new_bib nb ' |
1076 | . ' join bib_control bc ' |
1077 | . ' on bc.bib# = nb.bib# ' |
1078 | . ' and bc.staff_only = 0 ' |
1079 | . ' where nb.date >= ' |
1080 | . " datediff(dd, '01JAN1970', getdate()) - {$daysOld} " |
1081 | . 'order by nb.date desc '; |
1082 | |
1083 | $results = []; |
1084 | |
1085 | // Set the rowcount limit before executing the query for IDs |
1086 | $this->db->query($limitsql); |
1087 | |
1088 | // Actual query for IDs |
1089 | try { |
1090 | $sqlStmt = $this->db->query($newsql); |
1091 | foreach ($sqlStmt as $row) { |
1092 | $results[] = $row['bib#']; |
1093 | } |
1094 | |
1095 | $retVal = ['count' => count($results), 'results' => []]; |
1096 | foreach ($results as $result) { |
1097 | $retVal['results'][] = ['id' => $result]; |
1098 | } |
1099 | |
1100 | return $retVal; |
1101 | } catch (\Exception $e) { |
1102 | $this->logError($e->getMessage()); |
1103 | $this->throwAsIlsException($e); |
1104 | } |
1105 | } |
1106 | return ['count' => 0, 'results' => []]; |
1107 | } |
1108 | |
1109 | /** |
1110 | * Check Horizon Version |
1111 | * |
1112 | * Check the Horizon version found in the matham table to make sure it is at |
1113 | * least the required version. |
1114 | * |
1115 | * @param string $hzVersionRequired Minimum version required |
1116 | * |
1117 | * @return bool True or False the required version is the same or higher. |
1118 | */ |
1119 | protected function checkHzVersion($hzVersionRequired) |
1120 | { |
1121 | $checkHzVersionSQL = 'select database_revision from matham'; |
1122 | |
1123 | $hzVersionFound = ''; |
1124 | try { |
1125 | $versionResult = $this->db->query($checkHzVersionSQL); |
1126 | foreach ($versionResult as $row) { |
1127 | $hzVersionFound = $row['database_revision']; |
1128 | } |
1129 | } catch (\Exception $e) { |
1130 | $this->logError($e->getMessage()); |
1131 | $this->throwAsIlsException($e); |
1132 | } |
1133 | |
1134 | /* The Horizon database version is made up of 4 numbers separated by periods. |
1135 | * Explode the string and check each segment against the required version. |
1136 | */ |
1137 | $foundVersionParts = explode('.', $hzVersionFound); |
1138 | $requiredVersionParts = explode('.', $hzVersionRequired); |
1139 | |
1140 | $versionOK = true; |
1141 | |
1142 | for ($i = 0; $i < count($foundVersionParts); $i++) { |
1143 | $required = intval($requiredVersionParts[$i]); |
1144 | $found = intval($foundVersionParts[$i]); |
1145 | |
1146 | if ($found > $required) { |
1147 | // If found is greater than required stop checking |
1148 | break; |
1149 | } elseif ($found < $required) { |
1150 | /* If found is less than required set $versionOK false |
1151 | * and stop checking |
1152 | */ |
1153 | $versionOK = false; |
1154 | break; |
1155 | } |
1156 | } |
1157 | |
1158 | return $versionOK; |
1159 | } |
1160 | |
1161 | /** |
1162 | * Get suppressed records. |
1163 | * |
1164 | * Get a list of Horizon bib numbers that have the staff-only flag set. |
1165 | * |
1166 | * @return array ID numbers of suppressed records in the system. |
1167 | */ |
1168 | public function getSuppressedRecords() |
1169 | { |
1170 | $list = []; |
1171 | |
1172 | $sql = 'select bc.bib#' . |
1173 | ' from bib_control bc' . |
1174 | ' where bc.staff_only = 1'; |
1175 | try { |
1176 | $sqlStmt = $this->db->query($sql); |
1177 | foreach ($sqlStmt as $row) { |
1178 | $list[] = $row['bib#']; |
1179 | } |
1180 | } catch (\Exception $e) { |
1181 | $this->logError($e->getMessage()); |
1182 | $this->throwAsIlsException($e); |
1183 | } |
1184 | |
1185 | return $list; |
1186 | } |
1187 | } |