Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
Total | |
3.61% |
41 / 1137 |
|
1.79% |
1 / 56 |
CRAP | |
0.00% |
0 / 1 |
Voyager | |
3.61% |
41 / 1137 |
|
1.79% |
1 / 56 |
55781.72 | |
0.00% |
0 / 1 |
__construct | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
debugSQL | |
0.00% |
0 / 5 |
|
0.00% |
0 / 1 |
12 | |||
init | |
18.18% |
2 / 11 |
|
0.00% |
0 / 1 |
7.93 | |||
getDb | |
0.00% |
0 / 29 |
|
0.00% |
0 / 1 |
30 | |||
buildSqlFromArray | |
0.00% |
0 / 10 |
|
0.00% |
0 / 1 |
30 | |||
pickStatus | |
0.00% |
0 / 8 |
|
0.00% |
0 / 1 |
12 | |||
getStatusRanking | |
0.00% |
0 / 14 |
|
0.00% |
0 / 1 |
30 | |||
determineAvailability | |
0.00% |
0 / 10 |
|
0.00% |
0 / 1 |
30 | |||
getItemSortSequenceSQL | |
0.00% |
0 / 7 |
|
0.00% |
0 / 1 |
6 | |||
getStatusSQL | |
0.00% |
0 / 25 |
|
0.00% |
0 / 1 |
2 | |||
getStatusNoItemsSQL | |
0.00% |
0 / 31 |
|
0.00% |
0 / 1 |
2 | |||
getStatusData | |
0.00% |
0 / 23 |
|
0.00% |
0 / 1 |
30 | |||
processStatusData | |
0.00% |
0 / 17 |
|
0.00% |
0 / 1 |
30 | |||
getStatus | |
0.00% |
0 / 16 |
|
0.00% |
0 / 1 |
20 | |||
getStatuses | |
0.00% |
0 / 5 |
|
0.00% |
0 / 1 |
12 | |||
getHoldingItemsSQL | |
0.00% |
0 / 39 |
|
0.00% |
0 / 1 |
2 | |||
getHoldingNoItemsSQL | |
0.00% |
0 / 36 |
|
0.00% |
0 / 1 |
2 | |||
getHoldingData | |
0.00% |
0 / 16 |
|
0.00% |
0 / 1 |
42 | |||
getPurchaseHistoryData | |
0.00% |
0 / 24 |
|
0.00% |
0 / 1 |
20 | |||
getMFHDData | |
95.65% |
22 / 23 |
|
0.00% |
0 / 1 |
12 | |||
processRecordSegment | |
47.06% |
16 / 34 |
|
0.00% |
0 / 1 |
17.50 | |||
getLocationName | |
0.00% |
0 / 9 |
|
0.00% |
0 / 1 |
6 | |||
processHoldingRow | |
0.00% |
0 / 16 |
|
0.00% |
0 / 1 |
6 | |||
processHoldingDueDate | |
0.00% |
0 / 6 |
|
0.00% |
0 / 1 |
6 | |||
processHoldingReturnDate | |
0.00% |
0 / 6 |
|
0.00% |
0 / 1 |
6 | |||
processHoldingData | |
0.00% |
0 / 43 |
|
0.00% |
0 / 1 |
210 | |||
getHolding | |
0.00% |
0 / 15 |
|
0.00% |
0 / 1 |
20 | |||
getPurchaseHistory | |
0.00% |
0 / 3 |
|
0.00% |
0 / 1 |
12 | |||
sanitizePIN | |
0.00% |
0 / 2 |
|
0.00% |
0 / 1 |
2 | |||
patronLogin | |
0.00% |
0 / 55 |
|
0.00% |
0 / 1 |
240 | |||
getMyTransactionsSQL | |
0.00% |
0 / 39 |
|
0.00% |
0 / 1 |
2 | |||
pickTransactionStatus | |
0.00% |
0 / 6 |
|
0.00% |
0 / 1 |
20 | |||
processMyTransactionsData | |
0.00% |
0 / 40 |
|
0.00% |
0 / 1 |
110 | |||
getMyTransactions | |
0.00% |
0 / 10 |
|
0.00% |
0 / 1 |
12 | |||
getFineSQL | |
0.00% |
0 / 12 |
|
0.00% |
0 / 1 |
2 | |||
processFinesData | |
0.00% |
0 / 25 |
|
0.00% |
0 / 1 |
20 | |||
getMyFines | |
0.00% |
0 / 10 |
|
0.00% |
0 / 1 |
12 | |||
getMyHoldsSQL | |
0.00% |
0 / 17 |
|
0.00% |
0 / 1 |
2 | |||
processMyHoldsData | |
0.00% |
0 / 29 |
|
0.00% |
0 / 1 |
30 | |||
processHoldsList | |
0.00% |
0 / 8 |
|
0.00% |
0 / 1 |
20 | |||
getMyHolds | |
0.00% |
0 / 12 |
|
0.00% |
0 / 1 |
12 | |||
getMyStorageRetrievalRequestsSQL | |
0.00% |
0 / 31 |
|
0.00% |
0 / 1 |
12 | |||
processMyStorageRetrievalRequestsData | |
0.00% |
0 / 42 |
|
0.00% |
0 / 1 |
56 | |||
getMyStorageRetrievalRequests | |
0.00% |
0 / 9 |
|
0.00% |
0 / 1 |
12 | |||
getMyProfile | |
0.00% |
0 / 50 |
|
0.00% |
0 / 1 |
306 | |||
getHoldLink | |
0.00% |
0 / 1 |
|
0.00% |
0 / 1 |
2 | |||
getNewItems | |
0.00% |
0 / 52 |
|
0.00% |
0 / 1 |
72 | |||
getFunds | |
0.00% |
0 / 30 |
|
0.00% |
0 / 1 |
132 | |||
getDepartments | |
0.00% |
0 / 16 |
|
0.00% |
0 / 1 |
12 | |||
getInstructors | |
0.00% |
0 / 16 |
|
0.00% |
0 / 1 |
12 | |||
getCourses | |
0.00% |
0 / 16 |
|
0.00% |
0 / 1 |
12 | |||
findReserves | |
0.00% |
0 / 57 |
|
0.00% |
0 / 1 |
56 | |||
getRecentlyReturnedBibs | |
0.00% |
0 / 24 |
|
0.00% |
0 / 1 |
30 | |||
getTrendingBibs | |
0.00% |
0 / 27 |
|
0.00% |
0 / 1 |
30 | |||
getSuppressedRecords | |
0.00% |
0 / 10 |
|
0.00% |
0 / 1 |
12 | |||
executeSQL | |
0.00% |
0 / 9 |
|
0.00% |
0 / 1 |
12 |
1 | <?php |
2 | |
3 | /** |
4 | * Voyager ILS Driver |
5 | * |
6 | * PHP version 8 |
7 | * |
8 | * Copyright (C) Villanova University 2007. |
9 | * Copyright (C) The National Library of Finland 2014-2016. |
10 | * |
11 | * This program is free software; you can redistribute it and/or modify |
12 | * it under the terms of the GNU General Public License version 2, |
13 | * as published by the Free Software Foundation. |
14 | * |
15 | * This program is distributed in the hope that it will be useful, |
16 | * but WITHOUT ANY WARRANTY; without even the implied warranty of |
17 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
18 | * GNU General Public License for more details. |
19 | * |
20 | * You should have received a copy of the GNU General Public License |
21 | * along with this program; if not, write to the Free Software |
22 | * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA |
23 | * |
24 | * @category VuFind |
25 | * @package ILS_Drivers |
26 | * @author Andrew S. Nagy <vufind-tech@lists.sourceforge.net> |
27 | * @author Demian Katz <demian.katz@villanova.edu> |
28 | * @author Ere Maijala <ere.maijala@helsinki.fi> |
29 | * @license http://opensource.org/licenses/gpl-2.0.php GNU General Public License |
30 | * @link https://vufind.org/wiki/development:plugins:ils_drivers Wiki |
31 | */ |
32 | |
33 | namespace VuFind\ILS\Driver; |
34 | |
35 | use Laminas\Validator\EmailAddress as EmailAddressValidator; |
36 | use PDO; |
37 | use PDOException; |
38 | use VuFind\Date\DateException; |
39 | use VuFind\Exception\ILS as ILSException; |
40 | use VuFind\I18n\Translator\TranslatorAwareInterface; |
41 | use VuFind\Marc\MarcReader; |
42 | use Yajra\Pdo\Oci8; |
43 | |
44 | use function chr; |
45 | use function count; |
46 | use function in_array; |
47 | use function intval; |
48 | use function is_array; |
49 | |
50 | /** |
51 | * Voyager ILS Driver |
52 | * |
53 | * @category VuFind |
54 | * @package ILS_Drivers |
55 | * @author Andrew S. Nagy <vufind-tech@lists.sourceforge.net> |
56 | * @author Demian Katz <demian.katz@villanova.edu> |
57 | * @author Ere Maijala <ere.maijala@helsinki.fi> |
58 | * @license http://opensource.org/licenses/gpl-2.0.php GNU General Public License |
59 | * @link https://vufind.org/wiki/development:plugins:ils_drivers Wiki |
60 | */ |
61 | class Voyager extends AbstractBase implements TranslatorAwareInterface, \Laminas\Log\LoggerAwareInterface |
62 | { |
63 | use \VuFind\I18n\Translator\TranslatorAwareTrait; |
64 | use \VuFind\Log\LoggerAwareTrait { |
65 | logError as error; |
66 | } |
67 | |
68 | /** |
69 | * Lazily instantiated database connection. Use getDb() to access it. |
70 | * |
71 | * @var Oci8 |
72 | */ |
73 | protected $lazyDb; |
74 | |
75 | /** |
76 | * Name of database |
77 | * |
78 | * @var string |
79 | */ |
80 | protected $dbName; |
81 | |
82 | /** |
83 | * Stored status rankings from the database; initialized to false but populated |
84 | * by the pickStatus() method. |
85 | * |
86 | * @var array|bool |
87 | */ |
88 | protected $statusRankings = false; |
89 | |
90 | /** |
91 | * Date formatting object |
92 | * |
93 | * @var \VuFind\Date\Converter |
94 | */ |
95 | protected $dateFormat; |
96 | |
97 | /** |
98 | * Whether to use holdings sort groups to sort holdings records |
99 | * |
100 | * @var bool |
101 | */ |
102 | protected $useHoldingsSortGroups; |
103 | |
104 | /** |
105 | * Loan interval types for which to display the due time (empty = all) |
106 | * |
107 | * @var array |
108 | */ |
109 | protected $displayDueTimeIntervals; |
110 | |
111 | /** |
112 | * Constructor |
113 | * |
114 | * @param \VuFind\Date\Converter $dateConverter Date converter object |
115 | */ |
116 | public function __construct(\VuFind\Date\Converter $dateConverter) |
117 | { |
118 | $this->dateFormat = $dateConverter; |
119 | } |
120 | |
121 | /** |
122 | * Log an SQL statement debug message. |
123 | * |
124 | * @param string $func Function name or description |
125 | * @param string $sql The SQL statement |
126 | * @param array $params SQL bind parameters |
127 | * |
128 | * @return void |
129 | */ |
130 | protected function debugSQL($func, $sql, $params = null) |
131 | { |
132 | if ($this->logger) { |
133 | $logString = "[$func] $sql"; |
134 | if (isset($params)) { |
135 | $logString .= ', params: ' . $this->varDump($params); |
136 | } |
137 | $this->debug($logString); |
138 | } |
139 | } |
140 | |
141 | /** |
142 | * Initialize the driver. |
143 | * |
144 | * Validate configuration and perform all resource-intensive tasks needed to |
145 | * make the driver active. |
146 | * |
147 | * @throws ILSException |
148 | * @return void |
149 | */ |
150 | public function init() |
151 | { |
152 | if (empty($this->config)) { |
153 | throw new ILSException('Configuration needs to be set.'); |
154 | } |
155 | |
156 | // Define Database Name |
157 | $this->dbName = $this->config['Catalog']['database']; |
158 | |
159 | $this->useHoldingsSortGroups |
160 | = $this->config['Holdings']['use_sort_groups'] ?? true; |
161 | |
162 | $this->displayDueTimeIntervals |
163 | = isset($this->config['Loans']['display_due_time_only_for_intervals']) |
164 | ? explode( |
165 | ':', |
166 | $this->config['Loans']['display_due_time_only_for_intervals'] |
167 | ) : []; |
168 | } |
169 | |
170 | /** |
171 | * Initialize database connection if necessary and return it. |
172 | * |
173 | * @throws ILSException |
174 | * @return \PDO |
175 | */ |
176 | protected function getDb() |
177 | { |
178 | if (null === $this->lazyDb) { |
179 | // Based on the configuration file, use either "SID" or "SERVICE_NAME" |
180 | // to connect (correct value varies depending on Voyager's Oracle setup): |
181 | $connectType = isset($this->config['Catalog']['connect_with_sid']) && |
182 | $this->config['Catalog']['connect_with_sid'] ? |
183 | 'SID' : 'SERVICE_NAME'; |
184 | |
185 | $tns = '(DESCRIPTION=' . |
186 | '(ADDRESS_LIST=' . |
187 | '(ADDRESS=' . |
188 | '(PROTOCOL=TCP)' . |
189 | '(HOST=' . $this->config['Catalog']['host'] . ')' . |
190 | '(PORT=' . $this->config['Catalog']['port'] . ')' . |
191 | ')' . |
192 | ')' . |
193 | '(CONNECT_DATA=' . |
194 | "({$connectType}={$this->config['Catalog']['service']})" . |
195 | ')' . |
196 | ')'; |
197 | try { |
198 | $this->lazyDb = new Oci8( |
199 | "oci:dbname=$tns;charset=US7ASCII", |
200 | $this->config['Catalog']['user'], |
201 | $this->config['Catalog']['password'] |
202 | ); |
203 | $this->lazyDb |
204 | ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); |
205 | } catch (PDOException $e) { |
206 | $this->error( |
207 | "PDO Connection failed ($this->dbName): " . $e->getMessage() |
208 | ); |
209 | $this->throwAsIlsException($e); |
210 | } |
211 | } |
212 | return $this->lazyDb; |
213 | } |
214 | |
215 | /** |
216 | * Protected support method for building sql strings. |
217 | * |
218 | * @param array $sql An array of keyed sql data |
219 | * |
220 | * @return array An string query string and bind data |
221 | */ |
222 | protected function buildSqlFromArray($sql) |
223 | { |
224 | $modifier = isset($sql['modifier']) ? $sql['modifier'] . ' ' : ''; |
225 | |
226 | // Put String Together |
227 | $sqlString = 'SELECT ' . $modifier . implode(', ', $sql['expressions']); |
228 | $sqlString .= ' FROM ' . implode(', ', $sql['from']); |
229 | $sqlString .= (!empty($sql['where'])) |
230 | ? ' WHERE ' . implode(' AND ', $sql['where']) : ''; |
231 | $sqlString .= (!empty($sql['group'])) |
232 | ? ' GROUP BY ' . implode(', ', $sql['group']) : ''; |
233 | $sqlString .= (!empty($sql['order'])) |
234 | ? ' ORDER BY ' . implode(', ', $sql['order']) : ''; |
235 | |
236 | return ['string' => $sqlString, 'bind' => $sql['bind']]; |
237 | } |
238 | |
239 | /** |
240 | * Protected support method to pick which status message to display when multiple |
241 | * options are present. |
242 | * |
243 | * @param array $statusArray Array of status messages to choose from. |
244 | * |
245 | * @throws ILSException |
246 | * @return string The best status message to display. |
247 | */ |
248 | protected function pickStatus($statusArray) |
249 | { |
250 | // Pick the first entry by default, then see if we can find a better match: |
251 | $status = $statusArray[0]; |
252 | $rank = $this->getStatusRanking($status); |
253 | for ($x = 1; $x < count($statusArray); $x++) { |
254 | $thisRank = $this->getStatusRanking($statusArray[$x]); |
255 | if ($thisRank < $rank) { |
256 | $status = $statusArray[$x]; |
257 | $rank = $thisRank; |
258 | } |
259 | } |
260 | |
261 | return $status; |
262 | } |
263 | |
264 | /** |
265 | * Support method for pickStatus() -- get the ranking value of the specified |
266 | * status message. |
267 | * |
268 | * @param string $status Status message to look up |
269 | * |
270 | * @return int |
271 | */ |
272 | protected function getStatusRanking($status) |
273 | { |
274 | // This array controls the rankings of possible status messages. The lower |
275 | // the ID in the ITEM_STATUS_TYPE table, the higher the priority of the |
276 | // message. We only need to load it once -- after that, it's cached in the |
277 | // driver. |
278 | if ($this->statusRankings == false) { |
279 | // Execute SQL |
280 | $sql = "SELECT * FROM $this->dbName.ITEM_STATUS_TYPE"; |
281 | try { |
282 | $sqlStmt = $this->executeSQL($sql); |
283 | } catch (PDOException $e) { |
284 | $this->throwAsIlsException($e); |
285 | } |
286 | |
287 | // Read results |
288 | while ($row = $sqlStmt->fetch(PDO::FETCH_ASSOC)) { |
289 | $this->statusRankings[$row['ITEM_STATUS_DESC']] |
290 | = $row['ITEM_STATUS_TYPE']; |
291 | } |
292 | |
293 | if (!empty($this->config['StatusRankings'])) { |
294 | $this->statusRankings = array_merge( |
295 | $this->statusRankings, |
296 | $this->config['StatusRankings'] |
297 | ); |
298 | } |
299 | } |
300 | |
301 | // We may occasionally get a status message not found in the array (i.e. the |
302 | // "No information available" message that we hard-code when items are |
303 | // missing); return a large number in this case to avoid an undefined index |
304 | // error and to allow recognized statuses to take precedence. |
305 | return $this->statusRankings[$status] ?? 32000; |
306 | } |
307 | |
308 | /** |
309 | * Protected support method to take an array of status strings and determine |
310 | * whether or not this indicates an available item. Returns an array with |
311 | * two keys: 'available', the boolean availability status, and 'otherStatuses', |
312 | * every status code found other than "Not Charged" - for use with |
313 | * pickStatus(). |
314 | * |
315 | * @param array $statusArray The status codes to analyze. |
316 | * |
317 | * @return array Availability and other status information. |
318 | */ |
319 | protected function determineAvailability($statusArray) |
320 | { |
321 | // It's possible for a record to have multiple status codes. We |
322 | // need to loop through in search of the "Not Charged" (i.e. on |
323 | // shelf) status, collecting any other statuses we find along the |
324 | // way... |
325 | $notCharged = false; |
326 | $otherStatuses = []; |
327 | foreach ($statusArray as $status) { |
328 | switch ($status) { |
329 | case 'Not Charged': |
330 | $notCharged = true; |
331 | break; |
332 | default: |
333 | $otherStatuses[] = $status; |
334 | break; |
335 | } |
336 | } |
337 | |
338 | // If we found other statuses or if we failed to find "Not Charged," |
339 | // the item is not available! |
340 | $available = (count($otherStatuses) == 0 && $notCharged); |
341 | |
342 | return ['available' => $available, 'otherStatuses' => $otherStatuses]; |
343 | } |
344 | |
345 | /** |
346 | * Helper function that returns SQL for getting a sort sequence for a location |
347 | * |
348 | * @param string $locationColumn Column in the full where clause containing |
349 | * the column id |
350 | * |
351 | * @return string |
352 | */ |
353 | protected function getItemSortSequenceSQL($locationColumn) |
354 | { |
355 | if (!$this->useHoldingsSortGroups) { |
356 | return '0 as SORT_SEQ'; |
357 | } |
358 | |
359 | return '(SELECT SORT_GROUP_LOCATION.SEQUENCE_NUMBER ' . |
360 | "FROM $this->dbName.SORT_GROUP, $this->dbName.SORT_GROUP_LOCATION " . |
361 | "WHERE SORT_GROUP.SORT_GROUP_DEFAULT = 'Y' " . |
362 | 'AND SORT_GROUP_LOCATION.SORT_GROUP_ID = SORT_GROUP.SORT_GROUP_ID ' . |
363 | "AND SORT_GROUP_LOCATION.LOCATION_ID = $locationColumn) SORT_SEQ"; |
364 | } |
365 | |
366 | /** |
367 | * Protected support method for getStatus -- get components required for standard |
368 | * status lookup SQL. |
369 | * |
370 | * @param array $id A Bibliographic id |
371 | * |
372 | * @return array Keyed data for use in an sql query |
373 | */ |
374 | protected function getStatusSQL($id) |
375 | { |
376 | // Expressions |
377 | $sqlExpressions = [ |
378 | 'BIB_ITEM.BIB_ID', 'ITEM.ITEM_ID', 'MFHD_MASTER.MFHD_ID', |
379 | 'ITEM.ON_RESERVE', 'ITEM_STATUS_DESC as status', |
380 | 'NVL(LOCATION.LOCATION_DISPLAY_NAME, ' . |
381 | 'LOCATION.LOCATION_NAME) as location', |
382 | 'MFHD_MASTER.DISPLAY_CALL_NO as callnumber', |
383 | 'ITEM.TEMP_LOCATION', 'ITEM.ITEM_TYPE_ID', |
384 | 'ITEM.ITEM_SEQUENCE_NUMBER', |
385 | $this->getItemSortSequenceSQL('ITEM.PERM_LOCATION'), |
386 | ]; |
387 | |
388 | // From |
389 | $sqlFrom = [ |
390 | $this->dbName . '.BIB_ITEM', $this->dbName . '.ITEM', |
391 | $this->dbName . '.ITEM_STATUS_TYPE', |
392 | $this->dbName . '.ITEM_STATUS', |
393 | $this->dbName . '.LOCATION', $this->dbName . '.MFHD_ITEM', |
394 | $this->dbName . '.MFHD_MASTER', |
395 | ]; |
396 | |
397 | // Where |
398 | $sqlWhere = [ |
399 | 'BIB_ITEM.BIB_ID = :id', |
400 | 'BIB_ITEM.ITEM_ID = ITEM.ITEM_ID', |
401 | 'ITEM.ITEM_ID = ITEM_STATUS.ITEM_ID', |
402 | 'ITEM_STATUS.ITEM_STATUS = ITEM_STATUS_TYPE.ITEM_STATUS_TYPE', |
403 | 'LOCATION.LOCATION_ID = ITEM.PERM_LOCATION', |
404 | 'MFHD_ITEM.ITEM_ID = ITEM.ITEM_ID', |
405 | 'MFHD_MASTER.MFHD_ID = MFHD_ITEM.MFHD_ID', |
406 | "MFHD_MASTER.SUPPRESS_IN_OPAC='N'", |
407 | ]; |
408 | |
409 | // Bind |
410 | $sqlBind = [':id' => $id]; |
411 | |
412 | $sqlArray = [ |
413 | 'expressions' => $sqlExpressions, |
414 | 'from' => $sqlFrom, |
415 | 'where' => $sqlWhere, |
416 | 'bind' => $sqlBind, |
417 | ]; |
418 | |
419 | return $sqlArray; |
420 | } |
421 | |
422 | /** |
423 | * Protected support method for getStatus -- get components for status lookup |
424 | * SQL to use when a bib record has no items. |
425 | * |
426 | * @param array $id A Bibliographic id |
427 | * |
428 | * @return array Keyed data for use in an sql query |
429 | */ |
430 | protected function getStatusNoItemsSQL($id) |
431 | { |
432 | // Expressions |
433 | $sqlExpressions = [ |
434 | 'BIB_MFHD.BIB_ID', |
435 | 'null as ITEM_ID', 'MFHD_MASTER.MFHD_ID', "'N' as ON_RESERVE", |
436 | "'No information available' as status", |
437 | 'NVL(LOCATION.LOCATION_DISPLAY_NAME, ' . |
438 | 'LOCATION.LOCATION_NAME) as location', |
439 | 'MFHD_MASTER.DISPLAY_CALL_NO as callnumber', |
440 | '0 AS TEMP_LOCATION', |
441 | '0 as ITEM_SEQUENCE_NUMBER', |
442 | $this->getItemSortSequenceSQL('LOCATION.LOCATION_ID'), |
443 | ]; |
444 | |
445 | // From |
446 | $sqlFrom = [ |
447 | $this->dbName . '.BIB_MFHD', $this->dbName . '.LOCATION', |
448 | $this->dbName . '.MFHD_MASTER', |
449 | ]; |
450 | |
451 | // Where |
452 | $sqlWhere = [ |
453 | 'BIB_MFHD.BIB_ID = :id', |
454 | 'LOCATION.LOCATION_ID = MFHD_MASTER.LOCATION_ID', |
455 | 'MFHD_MASTER.MFHD_ID = BIB_MFHD.MFHD_ID', |
456 | "MFHD_MASTER.SUPPRESS_IN_OPAC='N'", |
457 | "NOT EXISTS (SELECT MFHD_ID FROM {$this->dbName}.MFHD_ITEM " . |
458 | 'WHERE MFHD_ITEM.MFHD_ID=MFHD_MASTER.MFHD_ID)', |
459 | ]; |
460 | |
461 | // Bind |
462 | $sqlBind = [':id' => $id]; |
463 | |
464 | $sqlArray = [ |
465 | 'expressions' => $sqlExpressions, |
466 | 'from' => $sqlFrom, |
467 | 'where' => $sqlWhere, |
468 | 'bind' => $sqlBind, |
469 | ]; |
470 | |
471 | return $sqlArray; |
472 | } |
473 | |
474 | /** |
475 | * Protected support method for getStatus -- process rows returned by SQL |
476 | * lookup. |
477 | * |
478 | * @param array $sqlRows Sql Data |
479 | * |
480 | * @return array Keyed data |
481 | */ |
482 | protected function getStatusData($sqlRows) |
483 | { |
484 | $data = []; |
485 | |
486 | foreach ($sqlRows as $row) { |
487 | $rowId = $row['ITEM_ID'] ?? 'MFHD' . $row['MFHD_ID']; |
488 | if (!isset($data[$rowId])) { |
489 | $data[$rowId] = [ |
490 | 'id' => $row['BIB_ID'], |
491 | 'status' => $row['STATUS'], |
492 | 'status_array' => [$row['STATUS']], |
493 | 'location' => $row['TEMP_LOCATION'] > 0 |
494 | ? $this->getLocationName($row['TEMP_LOCATION']) |
495 | : utf8_encode($row['LOCATION']), |
496 | 'reserve' => $row['ON_RESERVE'], |
497 | 'callnumber' => $row['CALLNUMBER'], |
498 | 'item_sort_seq' => $row['ITEM_SEQUENCE_NUMBER'], |
499 | 'sort_seq' => $row['SORT_SEQ'] ?? PHP_INT_MAX, |
500 | ]; |
501 | } else { |
502 | $statusFound = in_array( |
503 | $row['STATUS'], |
504 | $data[$rowId]['status_array'] |
505 | ); |
506 | if (!$statusFound) { |
507 | $data[$rowId]['status_array'][] = $row['STATUS']; |
508 | } |
509 | } |
510 | } |
511 | return $data; |
512 | } |
513 | |
514 | /** |
515 | * Protected support method for getStatus -- process all details collected by |
516 | * getStatusData(). |
517 | * |
518 | * @param array $data SQL Row Data |
519 | * |
520 | * @throws ILSException |
521 | * @return array Keyed data |
522 | */ |
523 | protected function processStatusData($data) |
524 | { |
525 | // Process the raw data into final status information: |
526 | $status = []; |
527 | foreach ($data as $current) { |
528 | // Get availability/status info based on the array of status codes: |
529 | $availability = $this->determineAvailability($current['status_array']); |
530 | |
531 | // If we found other statuses, we should override the display value |
532 | // appropriately: |
533 | if (count($availability['otherStatuses']) > 0) { |
534 | $current['status'] |
535 | = $this->pickStatus($availability['otherStatuses']); |
536 | } |
537 | $current['availability'] = $availability['available']; |
538 | $current['use_unknown_message'] |
539 | = in_array('No information available', $current['status_array']); |
540 | |
541 | $status[] = $current; |
542 | } |
543 | |
544 | if ($this->useHoldingsSortGroups) { |
545 | usort( |
546 | $status, |
547 | function ($a, $b) { |
548 | return $a['sort_seq'] == $b['sort_seq'] |
549 | ? $a['item_sort_seq'] - $b['item_sort_seq'] |
550 | : $a['sort_seq'] - $b['sort_seq']; |
551 | } |
552 | ); |
553 | } |
554 | |
555 | return $status; |
556 | } |
557 | |
558 | /** |
559 | * Get Status |
560 | * |
561 | * This is responsible for retrieving the status information of a certain |
562 | * record. |
563 | * |
564 | * @param string $id The record id to retrieve the holdings for |
565 | * |
566 | * @throws ILSException |
567 | * @return mixed On success, an associative array with the following keys: |
568 | * id, availability (boolean), status, location, reserve, callnumber. |
569 | */ |
570 | public function getStatus($id) |
571 | { |
572 | // There are two possible queries we can use to obtain status information. |
573 | // The first (and most common) obtains information from a combination of |
574 | // items and holdings records. The second (a rare case) obtains |
575 | // information from the holdings record when no items are available. |
576 | $sqlArrayItems = $this->getStatusSQL($id); |
577 | $sqlArrayNoItems = $this->getStatusNoItemsSQL($id); |
578 | $possibleQueries = [ |
579 | $this->buildSqlFromArray($sqlArrayItems), |
580 | $this->buildSqlFromArray($sqlArrayNoItems), |
581 | ]; |
582 | |
583 | // Loop through the possible queries and merge results. |
584 | $data = []; |
585 | foreach ($possibleQueries as $sql) { |
586 | // Execute SQL |
587 | try { |
588 | $sqlStmt = $this->executeSQL($sql); |
589 | } catch (PDOException $e) { |
590 | $this->throwAsIlsException($e); |
591 | } |
592 | |
593 | $sqlRows = []; |
594 | while ($row = $sqlStmt->fetch(PDO::FETCH_ASSOC)) { |
595 | $sqlRows[] = $row; |
596 | } |
597 | |
598 | $data += $this->getStatusData($sqlRows); |
599 | } |
600 | return $this->processStatusData($data); |
601 | } |
602 | |
603 | /** |
604 | * Get Statuses |
605 | * |
606 | * This is responsible for retrieving the status information for a |
607 | * collection of records. |
608 | * |
609 | * @param array $idList The array of record ids to retrieve the status for |
610 | * |
611 | * @throws ILSException |
612 | * @return array An array of getStatus() return values on success. |
613 | */ |
614 | public function getStatuses($idList) |
615 | { |
616 | $status = []; |
617 | if (is_array($idList)) { |
618 | foreach ($idList as $id) { |
619 | $status[] = $this->getStatus($id); |
620 | } |
621 | } |
622 | return $status; |
623 | } |
624 | |
625 | /** |
626 | * Protected support method for getHolding. |
627 | * |
628 | * @param array $id A Bibliographic id |
629 | * |
630 | * @return array Keyed data for use in an sql query |
631 | */ |
632 | protected function getHoldingItemsSQL($id) |
633 | { |
634 | // Expressions |
635 | $returnDate = <<<EOT |
636 | CASE WHEN ITEM_STATUS_TYPE.ITEM_STATUS_DESC = 'Discharged' THEN ( |
637 | SELECT TO_CHAR(MAX(CIRC_TRANS_ARCHIVE.DISCHARGE_DATE), 'MM-DD-YY HH24:MI') |
638 | FROM $this->dbName.CIRC_TRANS_ARCHIVE |
639 | WHERE CIRC_TRANS_ARCHIVE.ITEM_ID = ITEM.ITEM_ID |
640 | ) ELSE NULL END RETURNDATE |
641 | EOT; |
642 | $sqlExpressions = [ |
643 | 'BIB_ITEM.BIB_ID', 'MFHD_ITEM.MFHD_ID', |
644 | 'ITEM_BARCODE.ITEM_BARCODE', 'ITEM.ITEM_ID', |
645 | 'ITEM.ON_RESERVE', 'ITEM.ITEM_SEQUENCE_NUMBER', |
646 | 'ITEM.RECALLS_PLACED', 'ITEM.HOLDS_PLACED', |
647 | 'ITEM_STATUS_TYPE.ITEM_STATUS_DESC as status', |
648 | 'MFHD_DATA.RECORD_SEGMENT', 'MFHD_ITEM.ITEM_ENUM', |
649 | 'NVL(LOCATION.LOCATION_DISPLAY_NAME, ' . |
650 | 'LOCATION.LOCATION_NAME) as location', |
651 | 'ITEM.TEMP_LOCATION', |
652 | 'ITEM.PERM_LOCATION', |
653 | 'MFHD_MASTER.DISPLAY_CALL_NO as callnumber', |
654 | "to_char(CIRC_TRANSACTIONS.CURRENT_DUE_DATE, 'MM-DD-YY') as duedate", |
655 | $returnDate, |
656 | 'ITEM.ITEM_SEQUENCE_NUMBER', |
657 | $this->getItemSortSequenceSQL('ITEM.PERM_LOCATION'), |
658 | ]; |
659 | |
660 | // From |
661 | $sqlFrom = [ |
662 | $this->dbName . '.BIB_ITEM', $this->dbName . '.ITEM', |
663 | $this->dbName . '.ITEM_STATUS_TYPE', |
664 | $this->dbName . '.ITEM_STATUS', |
665 | $this->dbName . '.LOCATION', $this->dbName . '.MFHD_ITEM', |
666 | $this->dbName . '.MFHD_MASTER', $this->dbName . '.MFHD_DATA', |
667 | $this->dbName . '.CIRC_TRANSACTIONS', |
668 | $this->dbName . '.ITEM_BARCODE', |
669 | ]; |
670 | |
671 | // Where |
672 | $sqlWhere = [ |
673 | 'BIB_ITEM.BIB_ID = :id', |
674 | 'BIB_ITEM.ITEM_ID = ITEM.ITEM_ID', |
675 | 'ITEM.ITEM_ID = ITEM_STATUS.ITEM_ID', |
676 | 'ITEM_STATUS.ITEM_STATUS = ITEM_STATUS_TYPE.ITEM_STATUS_TYPE', |
677 | 'ITEM_BARCODE.ITEM_ID (+)= ITEM.ITEM_ID', |
678 | 'LOCATION.LOCATION_ID = ITEM.PERM_LOCATION', |
679 | 'CIRC_TRANSACTIONS.ITEM_ID (+)= ITEM.ITEM_ID', |
680 | 'MFHD_ITEM.ITEM_ID = ITEM.ITEM_ID', |
681 | 'MFHD_MASTER.MFHD_ID = MFHD_ITEM.MFHD_ID', |
682 | 'MFHD_DATA.MFHD_ID = MFHD_ITEM.MFHD_ID', |
683 | "MFHD_MASTER.SUPPRESS_IN_OPAC='N'", |
684 | ]; |
685 | |
686 | // Order |
687 | $sqlOrder = [ |
688 | 'ITEM.ITEM_SEQUENCE_NUMBER', 'MFHD_DATA.MFHD_ID', 'MFHD_DATA.SEQNUM', |
689 | ]; |
690 | |
691 | // Bind |
692 | $sqlBind = [':id' => $id]; |
693 | |
694 | $sqlArray = [ |
695 | 'expressions' => $sqlExpressions, |
696 | 'from' => $sqlFrom, |
697 | 'where' => $sqlWhere, |
698 | 'order' => $sqlOrder, |
699 | 'bind' => $sqlBind, |
700 | ]; |
701 | |
702 | return $sqlArray; |
703 | } |
704 | |
705 | /** |
706 | * Protected support method for getHolding. |
707 | * |
708 | * @param array $id A Bibliographic id |
709 | * |
710 | * @return array Keyed data for use in an sql query |
711 | */ |
712 | protected function getHoldingNoItemsSQL($id) |
713 | { |
714 | // Expressions |
715 | $sqlExpressions = [ |
716 | 'null as ITEM_BARCODE', 'null as ITEM_ID', |
717 | 'MFHD_DATA.RECORD_SEGMENT', 'null as ITEM_ENUM', |
718 | "'N' as ON_RESERVE", '1 as ITEM_SEQUENCE_NUMBER', |
719 | "'No information available' as status", |
720 | 'NVL(LOCATION.LOCATION_DISPLAY_NAME, ' . |
721 | 'LOCATION.LOCATION_NAME) as location', |
722 | 'MFHD_MASTER.DISPLAY_CALL_NO as callnumber', |
723 | 'BIB_MFHD.BIB_ID', 'MFHD_MASTER.MFHD_ID', |
724 | 'null as duedate', 'null as RETURNDATE', '0 AS TEMP_LOCATION', |
725 | '0 as PERM_LOCATION', |
726 | '0 as ITEM_SEQUENCE_NUMBER', |
727 | $this->getItemSortSequenceSQL('LOCATION.LOCATION_ID'), |
728 | ]; |
729 | |
730 | // From |
731 | $sqlFrom = [ |
732 | $this->dbName . '.BIB_MFHD', $this->dbName . '.LOCATION', |
733 | $this->dbName . '.MFHD_MASTER', $this->dbName . '.MFHD_DATA', |
734 | ]; |
735 | |
736 | // Where |
737 | $sqlWhere = [ |
738 | 'BIB_MFHD.BIB_ID = :id', |
739 | 'LOCATION.LOCATION_ID = MFHD_MASTER.LOCATION_ID', |
740 | 'MFHD_MASTER.MFHD_ID = BIB_MFHD.MFHD_ID', |
741 | 'MFHD_DATA.MFHD_ID = BIB_MFHD.MFHD_ID', |
742 | "MFHD_MASTER.SUPPRESS_IN_OPAC='N'", |
743 | "NOT EXISTS (SELECT MFHD_ID FROM {$this->dbName}.MFHD_ITEM" |
744 | . ' WHERE MFHD_ITEM.MFHD_ID=MFHD_MASTER.MFHD_ID)', |
745 | ]; |
746 | |
747 | // Order |
748 | $sqlOrder = ['MFHD_DATA.MFHD_ID', 'MFHD_DATA.SEQNUM']; |
749 | |
750 | // Bind |
751 | $sqlBind = [':id' => $id]; |
752 | |
753 | $sqlArray = [ |
754 | 'expressions' => $sqlExpressions, |
755 | 'from' => $sqlFrom, |
756 | 'where' => $sqlWhere, |
757 | 'order' => $sqlOrder, |
758 | 'bind' => $sqlBind, |
759 | ]; |
760 | |
761 | return $sqlArray; |
762 | } |
763 | |
764 | /** |
765 | * Protected support method for getHolding. |
766 | * |
767 | * @param array $sqlRows Sql Data |
768 | * |
769 | * @return array Keyed data |
770 | */ |
771 | protected function getHoldingData($sqlRows) |
772 | { |
773 | $data = []; |
774 | |
775 | foreach ($sqlRows as $row) { |
776 | // Determine Copy Number |
777 | $number = $row['ITEM_SEQUENCE_NUMBER']; |
778 | |
779 | // Concat wrapped rows (MARC data more than 300 bytes gets split |
780 | // into multiple rows) |
781 | $rowId = $row['ITEM_ID'] ?? 'MFHD' . $row['MFHD_ID']; |
782 | if (isset($data[$rowId][$number])) { |
783 | // We don't want to concatenate the same MARC information to |
784 | // itself over and over due to a record with multiple status |
785 | // codes -- we should only concat wrapped rows for the FIRST |
786 | // status code we encounter! |
787 | $record = & $data[$rowId][$number]; |
788 | if ($record['STATUS_ARRAY'][0] == $row['STATUS']) { |
789 | $record['RECORD_SEGMENT'] .= $row['RECORD_SEGMENT']; |
790 | } |
791 | |
792 | // If we've encountered a new status code, we should track it: |
793 | if (!in_array($row['STATUS'], $record['STATUS_ARRAY'])) { |
794 | $record['STATUS_ARRAY'][] = $row['STATUS']; |
795 | } |
796 | |
797 | // If we have a return date for this status, take it |
798 | if (null !== $row['RETURNDATE']) { |
799 | $record['RETURNDATE'] = $row['RETURNDATE']; |
800 | } |
801 | } else { |
802 | // This is the first time we've encountered this row number -- |
803 | // initialize the row and start an array of statuses. |
804 | $data[$rowId][$number] = $row; |
805 | $data[$rowId][$number]['STATUS_ARRAY'] |
806 | = [$row['STATUS']]; |
807 | } |
808 | } |
809 | return $data; |
810 | } |
811 | |
812 | /** |
813 | * Get Purchase History Data |
814 | * |
815 | * This is responsible for retrieving the acquisitions history data for the |
816 | * specific record (usually recently received issues of a serial). It is used |
817 | * by getHoldings() and getPurchaseHistory() depending on whether the purchase |
818 | * history is displayed by holdings or in a separate list. |
819 | * |
820 | * @param string $id The record id to retrieve the info for |
821 | * |
822 | * @throws ILSException |
823 | * @return array An array with the acquisitions data on success. |
824 | */ |
825 | protected function getPurchaseHistoryData($id) |
826 | { |
827 | $sql = 'select LINE_ITEM_COPY_STATUS.MFHD_ID, SERIAL_ISSUES.ENUMCHRON ' . |
828 | "from $this->dbName.SERIAL_ISSUES, $this->dbName.COMPONENT, " . |
829 | "$this->dbName.ISSUES_RECEIVED, $this->dbName.SUBSCRIPTION, " . |
830 | "$this->dbName.LINE_ITEM, $this->dbName.LINE_ITEM_COPY_STATUS " . |
831 | 'where SERIAL_ISSUES.COMPONENT_ID = COMPONENT.COMPONENT_ID ' . |
832 | 'and ISSUES_RECEIVED.ISSUE_ID = SERIAL_ISSUES.ISSUE_ID ' . |
833 | 'and ISSUES_RECEIVED.COMPONENT_ID = COMPONENT.COMPONENT_ID ' . |
834 | 'and COMPONENT.SUBSCRIPTION_ID = SUBSCRIPTION.SUBSCRIPTION_ID ' . |
835 | 'and SUBSCRIPTION.LINE_ITEM_ID = LINE_ITEM.LINE_ITEM_ID ' . |
836 | 'and LINE_ITEM_COPY_STATUS.LINE_ITEM_ID = LINE_ITEM.LINE_ITEM_ID ' . |
837 | 'and SERIAL_ISSUES.RECEIVED > 0 ' . |
838 | 'and ISSUES_RECEIVED.OPAC_SUPPRESSED = 1 ' . |
839 | 'and LINE_ITEM.BIB_ID = :id ' . |
840 | 'order by LINE_ITEM_COPY_STATUS.MFHD_ID, SERIAL_ISSUES.ISSUE_ID DESC'; |
841 | try { |
842 | $sqlStmt = $this->executeSQL($sql, [':id' => $id]); |
843 | } catch (PDOException $e) { |
844 | $this->throwAsIlsException($e); |
845 | } |
846 | $raw = $processed = []; |
847 | // Collect raw data: |
848 | while ($row = $sqlStmt->fetch(PDO::FETCH_ASSOC)) { |
849 | $raw[] = $row['MFHD_ID'] . '||' . utf8_encode($row['ENUMCHRON']); |
850 | } |
851 | // Deduplicate data and format it: |
852 | foreach (array_unique($raw) as $current) { |
853 | [$holdings_id, $issue] = explode('||', $current, 2); |
854 | $processed[] = compact('issue', 'holdings_id'); |
855 | } |
856 | return $processed; |
857 | } |
858 | |
859 | /** |
860 | * Get specified fields from an MFHD MARC Record |
861 | * |
862 | * @param MarcReader $record Marc reader |
863 | * @param array|string $fieldSpecs Array or colon-separated list of |
864 | * field/subfield specifications (3 chars for field code and then subfields, |
865 | * e.g. 866az) |
866 | * |
867 | * @return string|array Results as a string if single, array if multiple |
868 | */ |
869 | protected function getMFHDData(MarcReader $record, $fieldSpecs) |
870 | { |
871 | if (!is_array($fieldSpecs)) { |
872 | $fieldSpecs = explode(':', $fieldSpecs); |
873 | } |
874 | $results = ''; |
875 | foreach ($fieldSpecs as $fieldSpec) { |
876 | $fieldCode = substr($fieldSpec, 0, 3); |
877 | $subfieldCodes = substr($fieldSpec, 3); |
878 | if ($fields = $record->getFields($fieldCode)) { |
879 | foreach ($fields as $field) { |
880 | if ($subfields = $field['subfields'] ?? []) { |
881 | $line = ''; |
882 | foreach ($subfields as $subfield) { |
883 | if ( |
884 | !str_contains($subfieldCodes, $subfield['code']) |
885 | ) { |
886 | continue; |
887 | } |
888 | if ($line) { |
889 | $line .= ' '; |
890 | } |
891 | $line .= $subfield['data']; |
892 | } |
893 | if ($line) { |
894 | if (!$results) { |
895 | $results = $line; |
896 | } else { |
897 | if (!is_array($results)) { |
898 | $results = [$results]; |
899 | } |
900 | $results[] = $line; |
901 | } |
902 | } |
903 | } |
904 | } |
905 | } |
906 | } |
907 | return $results; |
908 | } |
909 | |
910 | /** |
911 | * Protected support method for getHolding. |
912 | * |
913 | * @param array $recordSegment A Marc Record Segment obtained from an SQL query |
914 | * |
915 | * @return array Keyed data |
916 | */ |
917 | protected function processRecordSegment($recordSegment) |
918 | { |
919 | $marcDetails = []; |
920 | |
921 | try { |
922 | $record = new MarcReader(str_replace(["\n", "\r"], '', $recordSegment)); |
923 | // Get Notes |
924 | $data = $this->getMFHDData( |
925 | $record, |
926 | $this->config['Holdings']['notes'] ?? '852z' |
927 | ); |
928 | if ($data) { |
929 | $marcDetails['notes'] = $data; |
930 | } |
931 | |
932 | // Get Summary (may be multiple lines) |
933 | $data = $this->getMFHDData( |
934 | $record, |
935 | $this->config['Holdings']['summary'] ?? '866a' |
936 | ); |
937 | if ($data) { |
938 | $marcDetails['summary'] = $data; |
939 | } |
940 | |
941 | // Get Supplements |
942 | if (isset($this->config['Holdings']['supplements'])) { |
943 | $data = $this->getMFHDData( |
944 | $record, |
945 | $this->config['Holdings']['supplements'] |
946 | ); |
947 | if ($data) { |
948 | $marcDetails['supplements'] = $data; |
949 | } |
950 | } |
951 | |
952 | // Get Indexes |
953 | if (isset($this->config['Holdings']['indexes'])) { |
954 | $data = $this->getMFHDData( |
955 | $record, |
956 | $this->config['Holdings']['indexes'] |
957 | ); |
958 | if ($data) { |
959 | $marcDetails['indexes'] = $data; |
960 | } |
961 | } |
962 | } catch (\Exception $e) { |
963 | trigger_error( |
964 | 'Poorly Formatted MFHD Record', |
965 | E_USER_NOTICE |
966 | ); |
967 | } |
968 | return $marcDetails; |
969 | } |
970 | |
971 | /** |
972 | * Look up a location name by ID. |
973 | * |
974 | * @param int $id Location ID to look up |
975 | * |
976 | * @return string |
977 | */ |
978 | protected function getLocationName($id) |
979 | { |
980 | static $cache = []; |
981 | |
982 | // Fill cache if empty: |
983 | if (!isset($cache[$id])) { |
984 | $sql = 'SELECT NVL(LOCATION_DISPLAY_NAME, LOCATION_NAME) as location ' . |
985 | "FROM {$this->dbName}.LOCATION WHERE LOCATION_ID=:id"; |
986 | $bind = ['id' => $id]; |
987 | $sqlStmt = $this->executeSQL($sql, $bind); |
988 | $sqlRow = $sqlStmt->fetch(PDO::FETCH_ASSOC); |
989 | $cache[$id] = utf8_encode($sqlRow['LOCATION']); |
990 | } |
991 | |
992 | return $cache[$id]; |
993 | } |
994 | |
995 | /** |
996 | * Protected support method for getHolding. |
997 | * |
998 | * @param array $sqlRow SQL Row Data |
999 | * |
1000 | * @return array Keyed data |
1001 | */ |
1002 | protected function processHoldingRow($sqlRow) |
1003 | { |
1004 | return [ |
1005 | 'id' => $sqlRow['BIB_ID'], |
1006 | 'holdings_id' => $sqlRow['MFHD_ID'], |
1007 | 'item_id' => $sqlRow['ITEM_ID'], |
1008 | 'status' => $sqlRow['STATUS'], |
1009 | 'location' => $sqlRow['TEMP_LOCATION'] > 0 |
1010 | ? $this->getLocationName($sqlRow['TEMP_LOCATION']) |
1011 | : utf8_encode($sqlRow['LOCATION']), |
1012 | 'reserve' => $sqlRow['ON_RESERVE'], |
1013 | 'callnumber' => $sqlRow['CALLNUMBER'], |
1014 | 'barcode' => $sqlRow['ITEM_BARCODE'], |
1015 | 'use_unknown_message' => |
1016 | in_array('No information available', $sqlRow['STATUS_ARRAY']), |
1017 | 'item_sort_seq' => $sqlRow['ITEM_SEQUENCE_NUMBER'], |
1018 | 'sort_seq' => $sqlRow['SORT_SEQ'] ?? PHP_INT_MAX, |
1019 | ]; |
1020 | } |
1021 | |
1022 | /** |
1023 | * Support method for processHoldingData: format a due date for inclusion in |
1024 | * holdings data. |
1025 | * |
1026 | * @param array $row Row to process |
1027 | * |
1028 | * @return string|bool |
1029 | */ |
1030 | protected function processHoldingDueDate(array $row) |
1031 | { |
1032 | if (!empty($row['DUEDATE'])) { |
1033 | return $this->dateFormat->convertToDisplayDate( |
1034 | 'm-d-y', |
1035 | $row['DUEDATE'] |
1036 | ); |
1037 | } |
1038 | return false; |
1039 | } |
1040 | |
1041 | /** |
1042 | * Support method for processHoldingData: format a return date for inclusion in |
1043 | * holdings data. |
1044 | * |
1045 | * @param array $row Row to process |
1046 | * |
1047 | * @return string|bool |
1048 | */ |
1049 | protected function processHoldingReturnDate(array $row) |
1050 | { |
1051 | if (!empty($row['RETURNDATE'])) { |
1052 | return $this->dateFormat->convertToDisplayDateAndTime( |
1053 | 'm-d-y H:i', |
1054 | $row['RETURNDATE'] |
1055 | ); |
1056 | } |
1057 | return false; |
1058 | } |
1059 | |
1060 | /** |
1061 | * Protected support method for getHolding. |
1062 | * |
1063 | * @param array $data Item Data |
1064 | * @param string $id The BIB record id |
1065 | * @param array $patron Patron Data |
1066 | * |
1067 | * @throws DateException |
1068 | * @throws ILSException |
1069 | * @return array Keyed data |
1070 | * |
1071 | * @SuppressWarnings(PHPMD.UnusedFormalParameter) |
1072 | */ |
1073 | protected function processHoldingData($data, $id, $patron = null) |
1074 | { |
1075 | $holding = []; |
1076 | |
1077 | // Build Holdings Array |
1078 | $purchaseHistory = []; |
1079 | if ( |
1080 | isset($this->config['Holdings']['purchase_history']) |
1081 | && $this->config['Holdings']['purchase_history'] === 'split' |
1082 | ) { |
1083 | $purchaseHistory = $this->getPurchaseHistoryData($id); |
1084 | } |
1085 | $i = 0; |
1086 | foreach ($data as $item) { |
1087 | foreach ($item as $number => $row) { |
1088 | // Get availability/status info based on the array of status codes: |
1089 | $availability = $this->determineAvailability($row['STATUS_ARRAY']); |
1090 | |
1091 | // If we found other statuses, we should override the display value |
1092 | // appropriately: |
1093 | if (count($availability['otherStatuses']) > 0) { |
1094 | $row['STATUS'] |
1095 | = $this->pickStatus($availability['otherStatuses']); |
1096 | } |
1097 | |
1098 | $requests_placed = $row['HOLDS_PLACED'] ?? 0; |
1099 | if (isset($row['RECALLS_PLACED'])) { |
1100 | $requests_placed += $row['RECALLS_PLACED']; |
1101 | } |
1102 | |
1103 | $holding[$i] = $this->processHoldingRow($row); |
1104 | $purchases = []; |
1105 | foreach ($purchaseHistory as $historyItem) { |
1106 | if ($holding[$i]['holdings_id'] == $historyItem['holdings_id']) { |
1107 | $purchases[] = $historyItem; |
1108 | } |
1109 | } |
1110 | $holding[$i] += [ |
1111 | 'availability' => $availability['available'], |
1112 | 'enumchron' => isset($row['ITEM_ENUM']) |
1113 | ? utf8_encode($row['ITEM_ENUM']) : null, |
1114 | 'duedate' => $this->processHoldingDueDate($row), |
1115 | 'number' => $number, |
1116 | 'requests_placed' => $requests_placed, |
1117 | 'returnDate' => $this->processHoldingReturnDate($row), |
1118 | 'purchase_history' => $purchases, |
1119 | ]; |
1120 | |
1121 | // Parse Holding Record |
1122 | if ($row['RECORD_SEGMENT']) { |
1123 | $marcDetails |
1124 | = $this->processRecordSegment($row['RECORD_SEGMENT']); |
1125 | if (!empty($marcDetails)) { |
1126 | $holding[$i] += $marcDetails; |
1127 | } |
1128 | } |
1129 | |
1130 | $i++; |
1131 | } |
1132 | } |
1133 | |
1134 | if ($this->useHoldingsSortGroups) { |
1135 | usort( |
1136 | $holding, |
1137 | function ($a, $b) { |
1138 | return $a['sort_seq'] == $b['sort_seq'] |
1139 | ? $a['item_sort_seq'] - $b['item_sort_seq'] |
1140 | : $a['sort_seq'] - $b['sort_seq']; |
1141 | } |
1142 | ); |
1143 | } |
1144 | |
1145 | return $holding; |
1146 | } |
1147 | |
1148 | /** |
1149 | * Get Holding |
1150 | * |
1151 | * This is responsible for retrieving the holding information of a certain |
1152 | * record. |
1153 | * |
1154 | * @param string $id The record id to retrieve the holdings for |
1155 | * @param array $patron Patron data |
1156 | * @param array $options Extra options (not currently used) |
1157 | * |
1158 | * @throws DateException |
1159 | * @throws ILSException |
1160 | * @return array On success, an associative array with the following |
1161 | * keys: id, availability (boolean), status, location, reserve, callnumber, |
1162 | * duedate, number, barcode. |
1163 | * |
1164 | * @SuppressWarnings(PHPMD.UnusedFormalParameter) |
1165 | */ |
1166 | public function getHolding($id, array $patron = null, array $options = []) |
1167 | { |
1168 | $possibleQueries = []; |
1169 | |
1170 | // There are two possible queries we can use to obtain status information. |
1171 | // The first (and most common) obtains information from a combination of |
1172 | // items and holdings records. The second (a rare case) obtains |
1173 | // information from the holdings record when no items are available. |
1174 | |
1175 | $sqlArrayItems = $this->getHoldingItemsSQL($id); |
1176 | $possibleQueries[] = $this->buildSqlFromArray($sqlArrayItems); |
1177 | |
1178 | $sqlArrayNoItems = $this->getHoldingNoItemsSQL($id); |
1179 | $possibleQueries[] = $this->buildSqlFromArray($sqlArrayNoItems); |
1180 | |
1181 | // Loop through the possible queries and merge results. |
1182 | $data = []; |
1183 | foreach ($possibleQueries as $sql) { |
1184 | // Execute SQL |
1185 | try { |
1186 | $sqlStmt = $this->executeSQL($sql); |
1187 | } catch (PDOException $e) { |
1188 | $this->throwAsIlsException($e); |
1189 | } |
1190 | |
1191 | $sqlRows = []; |
1192 | while ($row = $sqlStmt->fetch(PDO::FETCH_ASSOC)) { |
1193 | $sqlRows[] = $row; |
1194 | } |
1195 | |
1196 | $data = array_merge($data, $this->getHoldingData($sqlRows)); |
1197 | } |
1198 | return $this->processHoldingData($data, $id, $patron); |
1199 | } |
1200 | |
1201 | /** |
1202 | * Get Purchase History |
1203 | * |
1204 | * This is responsible for retrieving the acquisitions history data for the |
1205 | * specific record (usually recently received issues of a serial). |
1206 | * |
1207 | * @param string $id The record id to retrieve the info for |
1208 | * |
1209 | * @throws ILSException |
1210 | * @return array An array with the acquisitions data on success. |
1211 | */ |
1212 | public function getPurchaseHistory($id) |
1213 | { |
1214 | // Return empty array if purchase history is disabled or embedded |
1215 | // in holdings |
1216 | $setting = $this->config['Holdings']['purchase_history'] ?? true; |
1217 | return (!$setting || $setting === 'split') |
1218 | ? [] : $this->getPurchaseHistoryData($id); |
1219 | } |
1220 | |
1221 | /** |
1222 | * Sanitize patron PIN code (remove characters Voyager doesn't handle properly) |
1223 | * |
1224 | * @param string $pin PIN code to sanitize |
1225 | * |
1226 | * @return string Sanitized PIN code |
1227 | */ |
1228 | protected function sanitizePIN($pin) |
1229 | { |
1230 | $pin = preg_replace('/[^0-9a-zA-Z#&<>+^`~]+/', '', $pin); |
1231 | return $pin; |
1232 | } |
1233 | |
1234 | /** |
1235 | * Patron Login |
1236 | * |
1237 | * This is responsible for authenticating a patron against the catalog. |
1238 | * |
1239 | * @param string $username The patron barcode or institution ID (depending on |
1240 | * config) |
1241 | * @param string $login The patron's last name or PIN (depending on config) |
1242 | * |
1243 | * @throws ILSException |
1244 | * @return mixed Associative array of patron info on successful login, |
1245 | * null on unsuccessful login. |
1246 | */ |
1247 | public function patronLogin($username, $login) |
1248 | { |
1249 | // Load the field used for verifying the login from the config file, and |
1250 | // make sure there's nothing crazy in there: |
1251 | $usernameField = preg_replace( |
1252 | '/[^\w]/', |
1253 | '', |
1254 | $this->config['Catalog']['username_field'] ?? 'PATRON_BARCODE' |
1255 | ); |
1256 | $loginField = $this->config['Catalog']['login_field'] ?? 'LAST_NAME'; |
1257 | $loginField = preg_replace('/[^\w]/', '', $loginField); |
1258 | $fallbackLoginField = preg_replace( |
1259 | '/[^\w]/', |
1260 | '', |
1261 | $this->config['Catalog']['fallback_login_field'] ?? '' |
1262 | ); |
1263 | |
1264 | // Turns out it's difficult and inefficient to handle the mismatching |
1265 | // character sets of the Voyager database in the query (in theory something |
1266 | // like |
1267 | // "UPPER(UTL_I18N.RAW_TO_NCHAR(UTL_RAW.CAST_TO_RAW(field), 'WE8ISO8859P1'))" |
1268 | // could be used, but it's SLOW and ugly). We'll rely on the fact that the |
1269 | // barcode shouldn't contain any characters outside the basic latin |
1270 | // characters and check login verification fields here. |
1271 | |
1272 | $sql = 'SELECT PATRON.PATRON_ID, PATRON.FIRST_NAME, PATRON.LAST_NAME, ' . |
1273 | "PATRON.{$loginField} as LOGIN"; |
1274 | if ($fallbackLoginField) { |
1275 | $sql .= ", PATRON.{$fallbackLoginField} as FALLBACK_LOGIN"; |
1276 | } |
1277 | $sql .= " FROM $this->dbName.PATRON, $this->dbName.PATRON_BARCODE " . |
1278 | 'WHERE PATRON.PATRON_ID = PATRON_BARCODE.PATRON_ID AND '; |
1279 | $sql .= $usernameField === 'PATRON_BARCODE' |
1280 | ? 'lower(PATRON_BARCODE.PATRON_BARCODE) = :username' |
1281 | : "lower(PATRON.{$usernameField}) = :username"; |
1282 | |
1283 | // Limit the barcode statuses that allow logging in. By default only |
1284 | // 1 (active) and 4 (expired) are allowed. |
1285 | $allowedStatuses = preg_replace( |
1286 | '/[^:\d]*/', |
1287 | '', |
1288 | $this->config['Catalog']['allowed_barcode_statuses'] ?? '1:4' |
1289 | ); |
1290 | if ($allowedStatuses) { |
1291 | $sql .= ' AND PATRON_BARCODE.BARCODE_STATUS IN (' |
1292 | . str_replace(':', ',', $allowedStatuses) . ')'; |
1293 | } |
1294 | |
1295 | try { |
1296 | $bindUsername = strtolower(utf8_decode($username)); |
1297 | $compareLogin = mb_strtolower($login, 'UTF-8'); |
1298 | |
1299 | $sqlStmt = $this->executeSQL($sql, [':username' => $bindUsername]); |
1300 | // For some reason barcode is not unique, so evaluate all resulting |
1301 | // rows just to be safe |
1302 | while ($row = $sqlStmt->fetch(PDO::FETCH_ASSOC)) { |
1303 | $primary = null !== $row['LOGIN'] |
1304 | ? mb_strtolower(utf8_encode($row['LOGIN']), 'UTF-8') |
1305 | : null; |
1306 | $fallback = $fallbackLoginField && null === $row['LOGIN'] |
1307 | ? mb_strtolower(utf8_encode($row['FALLBACK_LOGIN']), 'UTF-8') |
1308 | : null; |
1309 | |
1310 | if ( |
1311 | (null !== $primary && ($primary == $compareLogin |
1312 | || $primary == $this->sanitizePIN($compareLogin))) |
1313 | || ($fallbackLoginField && null === $primary |
1314 | && $fallback == $compareLogin) |
1315 | ) { |
1316 | return [ |
1317 | 'id' => utf8_encode($row['PATRON_ID']), |
1318 | 'firstname' => utf8_encode($row['FIRST_NAME']), |
1319 | 'lastname' => utf8_encode($row['LAST_NAME']), |
1320 | 'cat_username' => $username, |
1321 | 'cat_password' => $login, |
1322 | // There's supposed to be a getPatronEmailAddress stored |
1323 | // procedure in Oracle, but I couldn't get it to work here; |
1324 | // might be worth investigating further if needed later. |
1325 | 'email' => null, |
1326 | 'major' => null, |
1327 | 'college' => null]; |
1328 | } |
1329 | } |
1330 | return null; |
1331 | } catch (PDOException $e) { |
1332 | $this->throwAsIlsException($e); |
1333 | } |
1334 | } |
1335 | |
1336 | /** |
1337 | * Protected support method for getMyTransactions. |
1338 | * |
1339 | * @param array $patron Patron data for use in an sql query |
1340 | * |
1341 | * @return array Keyed data for use in an sql query |
1342 | */ |
1343 | protected function getMyTransactionsSQL($patron) |
1344 | { |
1345 | // Expressions |
1346 | $sqlExpressions = [ |
1347 | "to_char(MAX(CIRC_TRANSACTIONS.CURRENT_DUE_DATE), 'MM-DD-YY HH24:MI')" . |
1348 | ' as DUEDATE', |
1349 | "to_char(MAX(CURRENT_DUE_DATE), 'YYYYMMDD HH24:MI') as FULLDATE", |
1350 | 'MAX(BIB_ITEM.BIB_ID) AS BIB_ID', |
1351 | 'MAX(CIRC_TRANSACTIONS.ITEM_ID) as ITEM_ID', |
1352 | 'MAX(MFHD_ITEM.ITEM_ENUM) AS ITEM_ENUM', |
1353 | 'MAX(MFHD_ITEM.YEAR) AS YEAR', |
1354 | 'MAX(ITEM_BARCODE.ITEM_BARCODE) AS ITEM_BARCODE', |
1355 | 'MAX(BIB_TEXT.TITLE_BRIEF) AS TITLE_BRIEF', |
1356 | 'MAX(BIB_TEXT.TITLE) AS TITLE', |
1357 | 'LISTAGG(ITEM_STATUS_DESC, CHR(9)) ' |
1358 | . 'WITHIN GROUP (ORDER BY ITEM_STATUS_DESC) as status', |
1359 | 'MAX(CIRC_TRANSACTIONS.RENEWAL_COUNT) AS RENEWAL_COUNT', |
1360 | 'MAX(CIRC_POLICY_MATRIX.RENEWAL_COUNT) as RENEWAL_LIMIT', |
1361 | 'MAX(LOCATION.LOCATION_DISPLAY_NAME) as BORROWING_LOCATION', |
1362 | 'MAX(CIRC_POLICY_MATRIX.LOAN_INTERVAL) as LOAN_INTERVAL', |
1363 | ]; |
1364 | |
1365 | // From |
1366 | $sqlFrom = [ |
1367 | $this->dbName . '.CIRC_TRANSACTIONS', |
1368 | $this->dbName . '.BIB_ITEM', |
1369 | $this->dbName . '.ITEM', |
1370 | $this->dbName . '.ITEM_STATUS', |
1371 | $this->dbName . '.ITEM_STATUS_TYPE', |
1372 | $this->dbName . '.ITEM_BARCODE', |
1373 | $this->dbName . '.MFHD_ITEM', |
1374 | $this->dbName . '.BIB_TEXT', |
1375 | $this->dbName . '.CIRC_POLICY_MATRIX', |
1376 | $this->dbName . '.LOCATION', |
1377 | ]; |
1378 | |
1379 | // Where |
1380 | $sqlWhere = [ |
1381 | 'CIRC_TRANSACTIONS.PATRON_ID = :id', |
1382 | 'BIB_ITEM.ITEM_ID = CIRC_TRANSACTIONS.ITEM_ID', |
1383 | 'CIRC_TRANSACTIONS.ITEM_ID = MFHD_ITEM.ITEM_ID(+)', |
1384 | 'BIB_TEXT.BIB_ID = BIB_ITEM.BIB_ID', |
1385 | 'CIRC_TRANSACTIONS.CIRC_POLICY_MATRIX_ID = ' . |
1386 | 'CIRC_POLICY_MATRIX.CIRC_POLICY_MATRIX_ID', |
1387 | 'CIRC_TRANSACTIONS.CHARGE_LOCATION = LOCATION.LOCATION_ID', |
1388 | 'BIB_ITEM.ITEM_ID = ITEM.ITEM_ID', |
1389 | 'ITEM.ITEM_ID = ITEM_STATUS.ITEM_ID', |
1390 | 'ITEM_STATUS.ITEM_STATUS = ITEM_STATUS_TYPE.ITEM_STATUS_TYPE', |
1391 | 'ITEM.ITEM_ID = ITEM_BARCODE.ITEM_ID(+)', |
1392 | '(ITEM_BARCODE.BARCODE_STATUS IS NULL OR ' . |
1393 | 'ITEM_BARCODE.BARCODE_STATUS IN (SELECT BARCODE_STATUS_TYPE FROM ' . |
1394 | "$this->dbName.ITEM_BARCODE_STATUS " . |
1395 | " WHERE BARCODE_STATUS_DESC = 'Active'))", |
1396 | ]; |
1397 | |
1398 | // Order |
1399 | $sqlOrder = ['FULLDATE ASC', 'TITLE ASC']; |
1400 | |
1401 | // Bind |
1402 | $sqlBind = [':id' => $patron['id']]; |
1403 | |
1404 | $sqlArray = [ |
1405 | 'expressions' => $sqlExpressions, |
1406 | 'from' => $sqlFrom, |
1407 | 'where' => $sqlWhere, |
1408 | 'order' => $sqlOrder, |
1409 | 'bind' => $sqlBind, |
1410 | 'group' => ['CIRC_TRANSACTIONS.ITEM_ID'], |
1411 | ]; |
1412 | |
1413 | return $sqlArray; |
1414 | } |
1415 | |
1416 | /** |
1417 | * Pick a transaction status worth displaying to the user (or return false |
1418 | * if nothing important is found). |
1419 | * |
1420 | * @param array $statuses Status strings |
1421 | * |
1422 | * @return string|bool |
1423 | */ |
1424 | protected function pickTransactionStatus($statuses) |
1425 | { |
1426 | $regex = $this->config['Loans']['show_statuses'] ?? '/lost|missing|claim/i'; |
1427 | $retVal = []; |
1428 | foreach ($statuses as $status) { |
1429 | if (preg_match($regex, $status)) { |
1430 | $retVal[] = $status; |
1431 | } |
1432 | } |
1433 | return empty($retVal) ? false : implode(', ', $retVal); |
1434 | } |
1435 | |
1436 | /** |
1437 | * Protected support method for getMyTransactions. |
1438 | * |
1439 | * @param array $sqlRow An array of keyed data |
1440 | * @param array $patron An array of keyed patron data |
1441 | * |
1442 | * @throws DateException |
1443 | * @return array Keyed data for display by template files |
1444 | * |
1445 | * @SuppressWarnings(PHPMD.UnusedFormalParameter) |
1446 | */ |
1447 | protected function processMyTransactionsData($sqlRow, $patron = false) |
1448 | { |
1449 | // Convert Voyager Format to display format |
1450 | if (!empty($sqlRow['DUEDATE'])) { |
1451 | $dueDate = $this->dateFormat->convertToDisplayDate( |
1452 | 'm-d-y H:i', |
1453 | $sqlRow['DUEDATE'] |
1454 | ); |
1455 | $dueTime = $this->dateFormat->convertToDisplayTime( |
1456 | 'm-d-y H:i', |
1457 | $sqlRow['DUEDATE'] |
1458 | ); |
1459 | } |
1460 | |
1461 | $dueStatus = false; |
1462 | if (!empty($sqlRow['FULLDATE'])) { |
1463 | $now = time(); |
1464 | $dueTimeStamp = strtotime($sqlRow['FULLDATE']); |
1465 | if (is_numeric($dueTimeStamp)) { |
1466 | if ($now > $dueTimeStamp) { |
1467 | $dueStatus = 'overdue'; |
1468 | } elseif ($now > $dueTimeStamp - (1 * 24 * 60 * 60)) { |
1469 | $dueStatus = 'due'; |
1470 | } |
1471 | } |
1472 | } |
1473 | |
1474 | $transaction = [ |
1475 | 'id' => $sqlRow['BIB_ID'], |
1476 | 'item_id' => $sqlRow['ITEM_ID'], |
1477 | 'barcode' => utf8_encode($sqlRow['ITEM_BARCODE']), |
1478 | 'duedate' => $dueDate, |
1479 | 'dueStatus' => $dueStatus, |
1480 | 'volume' => str_replace('v.', '', utf8_encode($sqlRow['ITEM_ENUM'])), |
1481 | 'publication_year' => $sqlRow['YEAR'], |
1482 | 'title' => empty($sqlRow['TITLE_BRIEF']) |
1483 | ? $sqlRow['TITLE'] : $sqlRow['TITLE_BRIEF'], |
1484 | 'renew' => $sqlRow['RENEWAL_COUNT'], |
1485 | 'renewLimit' => $sqlRow['RENEWAL_LIMIT'], |
1486 | 'message' => |
1487 | $this->pickTransactionStatus(explode(chr(9), $sqlRow['STATUS'])), |
1488 | ]; |
1489 | // Display due time only if loan interval is not in days if configured |
1490 | if ( |
1491 | empty($this->displayDueTimeIntervals) |
1492 | || in_array($sqlRow['LOAN_INTERVAL'], $this->displayDueTimeIntervals) |
1493 | ) { |
1494 | $transaction['dueTime'] = $dueTime; |
1495 | } |
1496 | if (!empty($this->config['Loans']['display_borrowing_location'])) { |
1497 | $transaction['borrowingLocation'] |
1498 | = utf8_encode($sqlRow['BORROWING_LOCATION']); |
1499 | } |
1500 | |
1501 | return $transaction; |
1502 | } |
1503 | |
1504 | /** |
1505 | * Get Patron Transactions |
1506 | * |
1507 | * This is responsible for retrieving all transactions (i.e. checked out items) |
1508 | * by a specific patron. |
1509 | * |
1510 | * @param array $patron The patron array from patronLogin |
1511 | * |
1512 | * @throws DateException |
1513 | * @throws ILSException |
1514 | * @return array Array of the patron's transactions on success. |
1515 | */ |
1516 | public function getMyTransactions($patron) |
1517 | { |
1518 | $transList = []; |
1519 | |
1520 | $sqlArray = $this->getMyTransactionsSQL($patron); |
1521 | |
1522 | $sql = $this->buildSqlFromArray($sqlArray); |
1523 | |
1524 | try { |
1525 | $sqlStmt = $this->executeSQL($sql); |
1526 | while ($row = $sqlStmt->fetch(PDO::FETCH_ASSOC)) { |
1527 | $processRow = $this->processMyTransactionsData($row, $patron); |
1528 | $transList[] = $processRow; |
1529 | } |
1530 | return $transList; |
1531 | } catch (PDOException $e) { |
1532 | $this->throwAsIlsException($e); |
1533 | } |
1534 | } |
1535 | |
1536 | /** |
1537 | * Protected support method for getMyFines. |
1538 | * |
1539 | * @param array $patron Patron data for use in an sql query |
1540 | * |
1541 | * @return array Keyed data for use in an sql query |
1542 | */ |
1543 | protected function getFineSQL($patron) |
1544 | { |
1545 | // Expressions |
1546 | $sqlExpressions = [ |
1547 | 'FINE_FEE_TYPE.FINE_FEE_DESC', |
1548 | 'PATRON.PATRON_ID', 'FINE_FEE.FINE_FEE_AMOUNT', |
1549 | 'FINE_FEE.FINE_FEE_BALANCE', |
1550 | "to_char(FINE_FEE.CREATE_DATE, 'MM-DD-YY HH:MI:SS') as CREATEDATE", |
1551 | "to_char(FINE_FEE.ORIG_CHARGE_DATE, 'MM-DD-YY') as CHARGEDATE", |
1552 | "to_char(FINE_FEE.DUE_DATE, 'MM-DD-YY') as DUEDATE", |
1553 | 'BIB_ITEM.BIB_ID', |
1554 | ]; |
1555 | |
1556 | // From |
1557 | $sqlFrom = [ |
1558 | $this->dbName . '.FINE_FEE', $this->dbName . '.FINE_FEE_TYPE', |
1559 | $this->dbName . '.PATRON', $this->dbName . '.BIB_ITEM', |
1560 | ]; |
1561 | |
1562 | // Where |
1563 | $sqlWhere = [ |
1564 | 'PATRON.PATRON_ID = :id', |
1565 | 'FINE_FEE.FINE_FEE_TYPE = FINE_FEE_TYPE.FINE_FEE_TYPE', |
1566 | 'FINE_FEE.PATRON_ID = PATRON.PATRON_ID', |
1567 | 'FINE_FEE.ITEM_ID = BIB_ITEM.ITEM_ID(+)', |
1568 | 'FINE_FEE.FINE_FEE_BALANCE > 0', |
1569 | ]; |
1570 | |
1571 | // Bind |
1572 | $sqlBind = [':id' => $patron['id']]; |
1573 | |
1574 | $sqlArray = [ |
1575 | 'expressions' => $sqlExpressions, |
1576 | 'from' => $sqlFrom, |
1577 | 'where' => $sqlWhere, |
1578 | 'bind' => $sqlBind, |
1579 | ]; |
1580 | |
1581 | return $sqlArray; |
1582 | } |
1583 | |
1584 | /** |
1585 | * Protected support method for getMyFines. |
1586 | * |
1587 | * @param array $sqlRow An array of keyed data |
1588 | * |
1589 | * @throws DateException |
1590 | * @return array Keyed data for display by template files |
1591 | */ |
1592 | protected function processFinesData($sqlRow) |
1593 | { |
1594 | $dueDate = $this->translate('not_applicable'); |
1595 | // Convert Voyager Format to display format |
1596 | if (!empty($sqlRow['DUEDATE'])) { |
1597 | $dueDate = $this->dateFormat->convertToDisplayDate( |
1598 | 'm-d-y', |
1599 | $sqlRow['DUEDATE'] |
1600 | ); |
1601 | } |
1602 | |
1603 | $createDate = $this->translate('not_applicable'); |
1604 | // Convert Voyager Format to display format |
1605 | if (!empty($sqlRow['CREATEDATE'])) { |
1606 | $createDate = $this->dateFormat->convertToDisplayDate( |
1607 | 'm-d-y', |
1608 | $sqlRow['CREATEDATE'] |
1609 | ); |
1610 | } |
1611 | |
1612 | $chargeDate = $this->translate('not_applicable'); |
1613 | // Convert Voyager Format to display format |
1614 | if (!empty($sqlRow['CHARGEDATE'])) { |
1615 | $chargeDate = $this->dateFormat->convertToDisplayDate( |
1616 | 'm-d-y', |
1617 | $sqlRow['CHARGEDATE'] |
1618 | ); |
1619 | } |
1620 | |
1621 | return ['amount' => $sqlRow['FINE_FEE_AMOUNT'], |
1622 | 'fine' => utf8_encode($sqlRow['FINE_FEE_DESC']), |
1623 | 'balance' => $sqlRow['FINE_FEE_BALANCE'], |
1624 | 'createdate' => $createDate, |
1625 | 'checkout' => $chargeDate, |
1626 | 'duedate' => $dueDate, |
1627 | 'id' => $sqlRow['BIB_ID']]; |
1628 | } |
1629 | |
1630 | /** |
1631 | * Get Patron Fines |
1632 | * |
1633 | * This is responsible for retrieving all fines by a specific patron. |
1634 | * |
1635 | * @param array $patron The patron array from patronLogin |
1636 | * |
1637 | * @throws DateException |
1638 | * @throws ILSException |
1639 | * @return mixed Array of the patron's fines on success. |
1640 | */ |
1641 | public function getMyFines($patron) |
1642 | { |
1643 | $fineList = []; |
1644 | |
1645 | $sqlArray = $this->getFineSQL($patron); |
1646 | |
1647 | $sql = $this->buildSqlFromArray($sqlArray); |
1648 | |
1649 | try { |
1650 | $sqlStmt = $this->executeSQL($sql); |
1651 | while ($row = $sqlStmt->fetch(PDO::FETCH_ASSOC)) { |
1652 | $processFine = $this->processFinesData($row); |
1653 | $fineList[] = $processFine; |
1654 | } |
1655 | return $fineList; |
1656 | } catch (PDOException $e) { |
1657 | $this->throwAsIlsException($e); |
1658 | } |
1659 | } |
1660 | |
1661 | /** |
1662 | * Protected support method for getMyHolds. |
1663 | * |
1664 | * @param array $patron Patron data for use in an sql query |
1665 | * |
1666 | * @return array Keyed data for use in an sql query |
1667 | */ |
1668 | protected function getMyHoldsSQL($patron) |
1669 | { |
1670 | // Modifier |
1671 | $sqlSelectModifier = 'distinct'; |
1672 | |
1673 | // Expressions |
1674 | $sqlExpressions = [ |
1675 | 'HOLD_RECALL.HOLD_RECALL_ID', 'HOLD_RECALL.BIB_ID', |
1676 | 'HOLD_RECALL.PICKUP_LOCATION', |
1677 | 'HOLD_RECALL.HOLD_RECALL_TYPE', |
1678 | "to_char(HOLD_RECALL.EXPIRE_DATE, 'MM-DD-YY') as EXPIRE_DATE", |
1679 | "to_char(HOLD_RECALL.CREATE_DATE, 'MM-DD-YY') as CREATE_DATE", |
1680 | 'HOLD_RECALL_ITEMS.ITEM_ID', |
1681 | 'HOLD_RECALL_ITEMS.HOLD_RECALL_STATUS', |
1682 | 'HOLD_RECALL_ITEMS.QUEUE_POSITION', |
1683 | 'MFHD_ITEM.ITEM_ENUM', |
1684 | 'MFHD_ITEM.YEAR', |
1685 | 'BIB_TEXT.TITLE_BRIEF', |
1686 | 'BIB_TEXT.TITLE', |
1687 | 'REQUEST_GROUP.GROUP_NAME as REQUEST_GROUP_NAME', |
1688 | ]; |
1689 | |
1690 | // From |
1691 | $sqlFrom = [ |
1692 | $this->dbName . '.HOLD_RECALL', |
1693 | $this->dbName . '.HOLD_RECALL_ITEMS', |
1694 | $this->dbName . '.MFHD_ITEM', |
1695 | $this->dbName . '.BIB_TEXT', |
1696 | $this->dbName . '.VOYAGER_DATABASES', |
1697 | $this->dbName . '.REQUEST_GROUP', |
1698 | ]; |
1699 | |
1700 | // Where |
1701 | $sqlWhere = [ |
1702 | 'HOLD_RECALL.PATRON_ID = :id', |
1703 | 'HOLD_RECALL.HOLD_RECALL_ID = HOLD_RECALL_ITEMS.HOLD_RECALL_ID(+)', |
1704 | 'HOLD_RECALL_ITEMS.ITEM_ID = MFHD_ITEM.ITEM_ID(+)', |
1705 | '(HOLD_RECALL_ITEMS.HOLD_RECALL_STATUS IS NULL OR ' . |
1706 | 'HOLD_RECALL_ITEMS.HOLD_RECALL_STATUS < 3)', |
1707 | 'BIB_TEXT.BIB_ID = HOLD_RECALL.BIB_ID', |
1708 | '(HOLD_RECALL.HOLDING_DB_ID IS NULL OR HOLD_RECALL.HOLDING_DB_ID = 0 ' . |
1709 | 'OR (HOLD_RECALL.HOLDING_DB_ID = ' . |
1710 | "VOYAGER_DATABASES.DB_ID AND VOYAGER_DATABASES.DB_CODE = 'LOCAL'))", |
1711 | 'HOLD_RECALL.REQUEST_GROUP_ID = REQUEST_GROUP.GROUP_ID(+)', |
1712 | ]; |
1713 | |
1714 | // Bind |
1715 | $sqlBind = [':id' => $patron['id']]; |
1716 | |
1717 | $sqlArray = [ |
1718 | 'modifier' => $sqlSelectModifier, |
1719 | 'expressions' => $sqlExpressions, |
1720 | 'from' => $sqlFrom, |
1721 | 'where' => $sqlWhere, |
1722 | 'bind' => $sqlBind, |
1723 | ]; |
1724 | |
1725 | return $sqlArray; |
1726 | } |
1727 | |
1728 | /** |
1729 | * Protected support method for getMyHolds. |
1730 | * |
1731 | * @param array $sqlRow An array of keyed data |
1732 | * |
1733 | * @throws DateException |
1734 | * @return array Keyed data for display by template files |
1735 | */ |
1736 | protected function processMyHoldsData($sqlRow) |
1737 | { |
1738 | $available = ($sqlRow['HOLD_RECALL_STATUS'] == 2) ? true : false; |
1739 | $expireDate = $this->translate('Unknown'); |
1740 | // Convert Voyager Format to display format |
1741 | if (!empty($sqlRow['EXPIRE_DATE'])) { |
1742 | $expireDate = $this->dateFormat->convertToDisplayDate( |
1743 | 'm-d-y', |
1744 | $sqlRow['EXPIRE_DATE'] |
1745 | ); |
1746 | } |
1747 | |
1748 | $createDate = $this->translate('Unknown'); |
1749 | // Convert Voyager Format to display format |
1750 | if (!empty($sqlRow['CREATE_DATE'])) { |
1751 | $createDate = $this->dateFormat->convertToDisplayDate( |
1752 | 'm-d-y', |
1753 | $sqlRow['CREATE_DATE'] |
1754 | ); |
1755 | } |
1756 | |
1757 | return [ |
1758 | 'id' => $sqlRow['BIB_ID'], |
1759 | 'type' => $sqlRow['HOLD_RECALL_TYPE'], |
1760 | 'location' => $sqlRow['PICKUP_LOCATION'], |
1761 | 'requestGroup' => $sqlRow['REQUEST_GROUP_NAME'], |
1762 | 'expire' => $expireDate, |
1763 | 'create' => $createDate, |
1764 | 'position' => $sqlRow['QUEUE_POSITION'], |
1765 | 'available' => $available, |
1766 | 'reqnum' => $sqlRow['HOLD_RECALL_ID'], |
1767 | 'item_id' => $sqlRow['ITEM_ID'], |
1768 | 'volume' => str_replace('v.', '', utf8_encode($sqlRow['ITEM_ENUM'])), |
1769 | 'publication_year' => $sqlRow['YEAR'], |
1770 | 'title' => empty($sqlRow['TITLE_BRIEF']) |
1771 | ? $sqlRow['TITLE'] : $sqlRow['TITLE_BRIEF'], |
1772 | ]; |
1773 | } |
1774 | |
1775 | /** |
1776 | * Process Holds List |
1777 | * |
1778 | * This is responsible for processing holds to ensure only one record is shown |
1779 | * for each hold. |
1780 | * |
1781 | * @param array $holdList The Hold List Array |
1782 | * |
1783 | * @return mixed Array of the patron's holds. |
1784 | */ |
1785 | protected function processHoldsList($holdList) |
1786 | { |
1787 | $returnList = []; |
1788 | |
1789 | if (!empty($holdList)) { |
1790 | $sortHoldList = []; |
1791 | // Get a unique List of Bib Ids |
1792 | foreach ($holdList as $holdItem) { |
1793 | $sortHoldList[$holdItem['id']][] = $holdItem; |
1794 | } |
1795 | |
1796 | // Use the first copy hold only |
1797 | foreach ($sortHoldList as $bibHold) { |
1798 | $returnList[] = $bibHold[0]; |
1799 | } |
1800 | } |
1801 | return $returnList; |
1802 | } |
1803 | |
1804 | /** |
1805 | * Get Patron Holds |
1806 | * |
1807 | * This is responsible for retrieving all holds by a specific patron. |
1808 | * |
1809 | * @param array $patron The patron array from patronLogin |
1810 | * |
1811 | * @throws DateException |
1812 | * @throws ILSException |
1813 | * @return array Array of the patron's holds on success. |
1814 | */ |
1815 | public function getMyHolds($patron) |
1816 | { |
1817 | $holdList = []; |
1818 | $returnList = []; |
1819 | |
1820 | $sqlArray = $this->getMyHoldsSQL($patron); |
1821 | |
1822 | $sql = $this->buildSqlFromArray($sqlArray); |
1823 | |
1824 | try { |
1825 | $sqlStmt = $this->executeSQL($sql); |
1826 | while ($sqlRow = $sqlStmt->fetch(PDO::FETCH_ASSOC)) { |
1827 | $holds = $this->processMyHoldsData($sqlRow); |
1828 | $holdList[] = $holds; |
1829 | } |
1830 | $returnList = $this->processHoldsList($holdList); |
1831 | return $returnList; |
1832 | } catch (PDOException $e) { |
1833 | $this->throwAsIlsException($e); |
1834 | } |
1835 | } |
1836 | |
1837 | /** |
1838 | * Protected support method for getMyStorageRetrievalRequests. |
1839 | * |
1840 | * @param array $patron Patron data for use in an sql query |
1841 | * |
1842 | * @return array Keyed data for use in an sql query |
1843 | */ |
1844 | protected function getMyStorageRetrievalRequestsSQL($patron) |
1845 | { |
1846 | // Modifier |
1847 | $sqlSelectModifier = 'distinct'; |
1848 | |
1849 | // Expressions |
1850 | $sqlExpressions = [ |
1851 | 'CALL_SLIP.CALL_SLIP_ID', 'CALL_SLIP.BIB_ID', |
1852 | 'CALL_SLIP.PICKUP_LOCATION_ID', |
1853 | "to_char(CALL_SLIP.DATE_REQUESTED, 'YYYY-MM-DD HH24:MI:SS')" |
1854 | . ' as CREATE_DATE', |
1855 | "to_char(CALL_SLIP.DATE_PROCESSED, 'YYYY-MM-DD HH24:MI:SS')" |
1856 | . ' as PROCESSED_DATE', |
1857 | "to_char(CALL_SLIP.STATUS_DATE, 'YYYY-MM-DD HH24:MI:SS')" |
1858 | . ' as STATUS_DATE', |
1859 | 'CALL_SLIP.ITEM_ID', |
1860 | 'CALL_SLIP.MFHD_ID', |
1861 | 'CALL_SLIP.STATUS', |
1862 | 'CALL_SLIP_STATUS_TYPE.STATUS_DESC', |
1863 | 'CALL_SLIP.ITEM_YEAR', |
1864 | 'CALL_SLIP.ITEM_ENUM', |
1865 | 'CALL_SLIP.ITEM_CHRON', |
1866 | 'CALL_SLIP.REPLY_NOTE', |
1867 | 'CALL_SLIP.PICKUP_LOCATION_ID', |
1868 | 'MFHD_ITEM.ITEM_ENUM', |
1869 | 'MFHD_ITEM.YEAR', |
1870 | 'BIB_TEXT.TITLE_BRIEF', |
1871 | 'BIB_TEXT.TITLE', |
1872 | ]; |
1873 | |
1874 | // From |
1875 | $sqlFrom = [ |
1876 | $this->dbName . '.CALL_SLIP', |
1877 | $this->dbName . '.CALL_SLIP_STATUS_TYPE', |
1878 | $this->dbName . '.MFHD_ITEM', |
1879 | $this->dbName . '.BIB_TEXT', |
1880 | ]; |
1881 | |
1882 | // Where |
1883 | $sqlWhere = [ |
1884 | 'CALL_SLIP.PATRON_ID = :id', |
1885 | 'CALL_SLIP.STATUS = CALL_SLIP_STATUS_TYPE.STATUS_TYPE(+)', |
1886 | 'CALL_SLIP.ITEM_ID = MFHD_ITEM.ITEM_ID(+)', |
1887 | 'BIB_TEXT.BIB_ID = CALL_SLIP.BIB_ID', |
1888 | ]; |
1889 | |
1890 | if (!empty($this->config['StorageRetrievalRequests']['display_statuses'])) { |
1891 | $statuses = preg_replace( |
1892 | '/[^:\d]*/', |
1893 | '', |
1894 | $this->config['StorageRetrievalRequests']['display_statuses'] |
1895 | ); |
1896 | if ($statuses) { |
1897 | $sqlWhere[] = 'CALL_SLIP.STATUS IN (' |
1898 | . str_replace(':', ',', $statuses) . ')'; |
1899 | } |
1900 | } |
1901 | |
1902 | // Order by |
1903 | $sqlOrderBy = [ |
1904 | "to_char(CALL_SLIP.DATE_REQUESTED, 'YYYY-MM-DD HH24:MI:SS')", |
1905 | ]; |
1906 | |
1907 | // Bind |
1908 | $sqlBind = [':id' => $patron['id']]; |
1909 | |
1910 | $sqlArray = [ |
1911 | 'modifier' => $sqlSelectModifier, |
1912 | 'expressions' => $sqlExpressions, |
1913 | 'from' => $sqlFrom, |
1914 | 'where' => $sqlWhere, |
1915 | 'order' => $sqlOrderBy, |
1916 | 'bind' => $sqlBind, |
1917 | ]; |
1918 | |
1919 | return $sqlArray; |
1920 | } |
1921 | |
1922 | /** |
1923 | * Protected support method for getMyStorageRetrievalRequests. |
1924 | * |
1925 | * @param array $sqlRow An array of keyed data |
1926 | * |
1927 | * @return array Keyed data for display by template files |
1928 | */ |
1929 | protected function processMyStorageRetrievalRequestsData($sqlRow) |
1930 | { |
1931 | $available = ($sqlRow['STATUS'] == 4) ? true : false; |
1932 | $expireDate = ''; |
1933 | $processedDate = ''; |
1934 | $statusDate = ''; |
1935 | // Convert Voyager Format to display format |
1936 | if (!empty($sqlRow['PROCESSED_DATE'])) { |
1937 | $processedDate = $this->dateFormat->convertToDisplayDate( |
1938 | 'm-d-y', |
1939 | $sqlRow['PROCESSED_DATE'] |
1940 | ); |
1941 | } |
1942 | if (!empty($sqlRow['STATUS_DATE'])) { |
1943 | $statusDate = $this->dateFormat->convertToDisplayDate( |
1944 | 'm-d-y', |
1945 | $sqlRow['STATUS_DATE'] |
1946 | ); |
1947 | } |
1948 | |
1949 | $createDate = $this->translate('Unknown'); |
1950 | // Convert Voyager Format to display format |
1951 | if (!empty($sqlRow['CREATE_DATE'])) { |
1952 | $createDate = $this->dateFormat->convertToDisplayDate( |
1953 | 'm-d-y', |
1954 | $sqlRow['CREATE_DATE'] |
1955 | ); |
1956 | } |
1957 | |
1958 | return [ |
1959 | 'id' => $sqlRow['BIB_ID'], |
1960 | 'status' => utf8_encode($sqlRow['STATUS_DESC']), |
1961 | 'statusDate' => $statusDate, |
1962 | 'location' => $this->getLocationName($sqlRow['PICKUP_LOCATION_ID']), |
1963 | 'create' => $createDate, |
1964 | 'processed' => $processedDate, |
1965 | 'expire' => $expireDate, |
1966 | 'reply' => utf8_encode($sqlRow['REPLY_NOTE']), |
1967 | 'available' => $available, |
1968 | 'canceled' => $sqlRow['STATUS'] == 7 ? $statusDate : false, |
1969 | 'reqnum' => $sqlRow['CALL_SLIP_ID'], |
1970 | 'item_id' => $sqlRow['ITEM_ID'], |
1971 | 'volume' => str_replace( |
1972 | 'v.', |
1973 | '', |
1974 | utf8_encode($sqlRow['ITEM_ENUM']) |
1975 | ), |
1976 | 'issue' => utf8_encode($sqlRow['ITEM_CHRON']), |
1977 | 'year' => utf8_encode($sqlRow['ITEM_YEAR']), |
1978 | 'title' => empty($sqlRow['TITLE_BRIEF']) |
1979 | ? $sqlRow['TITLE'] : $sqlRow['TITLE_BRIEF'], |
1980 | ]; |
1981 | } |
1982 | |
1983 | /** |
1984 | * Get Patron Storage Retrieval Requests |
1985 | * |
1986 | * This is responsible for retrieving all call slips by a specific patron. |
1987 | * |
1988 | * @param array $patron The patron array from patronLogin |
1989 | * |
1990 | * @return array Array of the patron's storage retrieval requests. |
1991 | */ |
1992 | public function getMyStorageRetrievalRequests($patron) |
1993 | { |
1994 | $list = []; |
1995 | |
1996 | $sqlArray = $this->getMyStorageRetrievalRequestsSQL($patron); |
1997 | |
1998 | $sql = $this->buildSqlFromArray($sqlArray); |
1999 | try { |
2000 | $sqlStmt = $this->executeSQL($sql); |
2001 | while ($sqlRow = $sqlStmt->fetch(PDO::FETCH_ASSOC)) { |
2002 | $list[] = $this->processMyStorageRetrievalRequestsData($sqlRow); |
2003 | } |
2004 | return $list; |
2005 | } catch (PDOException $e) { |
2006 | $this->throwAsIlsException($e); |
2007 | } |
2008 | } |
2009 | |
2010 | /** |
2011 | * Get Patron Profile |
2012 | * |
2013 | * This is responsible for retrieving the profile for a specific patron. |
2014 | * |
2015 | * @param array $patron The patron array |
2016 | * |
2017 | * @throws ILSException |
2018 | * @return array Array of the patron's profile data on success. |
2019 | */ |
2020 | public function getMyProfile($patron) |
2021 | { |
2022 | $sql = 'SELECT PATRON.LAST_NAME, PATRON.FIRST_NAME, ' . |
2023 | 'PATRON.HISTORICAL_CHARGES, PATRON_ADDRESS.ADDRESS_LINE1, ' . |
2024 | 'PATRON_ADDRESS.ADDRESS_LINE2, PATRON_ADDRESS.ZIP_POSTAL, ' . |
2025 | 'PATRON_ADDRESS.CITY, PATRON_ADDRESS.COUNTRY, ' . |
2026 | 'PATRON_PHONE.PHONE_NUMBER, PHONE_TYPE.PHONE_DESC, ' . |
2027 | 'PATRON_GROUP.PATRON_GROUP_NAME ' . |
2028 | "FROM $this->dbName.PATRON, $this->dbName.PATRON_ADDRESS, " . |
2029 | "$this->dbName.PATRON_PHONE, $this->dbName.PHONE_TYPE, " . |
2030 | "$this->dbName.PATRON_BARCODE, $this->dbName.PATRON_GROUP " . |
2031 | 'WHERE PATRON.PATRON_ID = PATRON_ADDRESS.PATRON_ID (+) ' . |
2032 | 'AND PATRON_ADDRESS.ADDRESS_ID = PATRON_PHONE.ADDRESS_ID (+) ' . |
2033 | 'AND PATRON.PATRON_ID = PATRON_BARCODE.PATRON_ID (+) ' . |
2034 | 'AND PATRON_BARCODE.PATRON_GROUP_ID = ' . |
2035 | 'PATRON_GROUP.PATRON_GROUP_ID (+) ' . |
2036 | 'AND PATRON_PHONE.PHONE_TYPE = PHONE_TYPE.PHONE_TYPE (+) ' . |
2037 | 'AND PATRON.PATRON_ID = :id'; |
2038 | $primaryPhoneType = $this->config['Profile']['primary_phone'] ?? 'Primary'; |
2039 | $mobilePhoneType = $this->config['Profile']['mobile_phone'] ?? 'Mobile'; |
2040 | try { |
2041 | $sqlStmt = $this->executeSQL($sql, [':id' => $patron['id']]); |
2042 | $patron = []; |
2043 | while ($row = $sqlStmt->fetch(PDO::FETCH_ASSOC)) { |
2044 | if (!empty($row['FIRST_NAME'])) { |
2045 | $patron['firstname'] = utf8_encode($row['FIRST_NAME']); |
2046 | } |
2047 | if (!empty($row['LAST_NAME'])) { |
2048 | $patron['lastname'] = utf8_encode($row['LAST_NAME']); |
2049 | } |
2050 | if (!empty($row['PHONE_NUMBER'])) { |
2051 | if ($primaryPhoneType === $row['PHONE_DESC']) { |
2052 | $patron['phone'] = utf8_encode($row['PHONE_NUMBER']); |
2053 | } elseif ($mobilePhoneType === $row['PHONE_DESC']) { |
2054 | $patron['mobile_phone'] = utf8_encode($row['PHONE_NUMBER']); |
2055 | } |
2056 | } |
2057 | if (!empty($row['PATRON_GROUP_NAME'])) { |
2058 | $patron['group'] = utf8_encode($row['PATRON_GROUP_NAME']); |
2059 | } |
2060 | $validator = new EmailAddressValidator(); |
2061 | $addr1 = utf8_encode($row['ADDRESS_LINE1']); |
2062 | if ($validator->isValid($addr1)) { |
2063 | $patron['email'] = $addr1; |
2064 | } elseif (!isset($patron['address1'])) { |
2065 | if (!empty($addr1)) { |
2066 | $patron['address1'] = $addr1; |
2067 | } |
2068 | if (!empty($row['ADDRESS_LINE2'])) { |
2069 | $patron['address2'] = utf8_encode($row['ADDRESS_LINE2']); |
2070 | } |
2071 | if (!empty($row['ZIP_POSTAL'])) { |
2072 | $patron['zip'] = utf8_encode($row['ZIP_POSTAL']); |
2073 | } |
2074 | if (!empty($row['CITY'])) { |
2075 | $patron['city'] = utf8_encode($row['CITY']); |
2076 | } |
2077 | if (!empty($row['COUNTRY'])) { |
2078 | $patron['country'] = utf8_encode($row['COUNTRY']); |
2079 | } |
2080 | } |
2081 | } |
2082 | return empty($patron) ? null : $patron; |
2083 | } catch (PDOException $e) { |
2084 | $this->throwAsIlsException($e); |
2085 | } |
2086 | } |
2087 | |
2088 | /** |
2089 | * Get Hold Link |
2090 | * |
2091 | * The goal for this method is to return a URL to a "place hold" web page on |
2092 | * the ILS OPAC. This is used for ILSs that do not support an API or method |
2093 | * to place Holds. |
2094 | * |
2095 | * @param string $recordId The id of the bib record |
2096 | * @param array $details Item details from getHoldings return array |
2097 | * |
2098 | * @return string URL to ILS's OPAC's place hold screen. |
2099 | * |
2100 | * @SuppressWarnings(PHPMD.UnusedFormalParameter) |
2101 | */ |
2102 | public function getHoldLink($recordId, $details) |
2103 | { |
2104 | // There is no easy way to link directly to hold screen; let's just use |
2105 | // the record view. For better hold behavior, use the VoyagerRestful |
2106 | // driver. |
2107 | return $this->config['Catalog']['pwebrecon'] . '?BBID=' . $recordId; |
2108 | } |
2109 | |
2110 | /** |
2111 | * Get New Items |
2112 | * |
2113 | * Retrieve the IDs of items recently added to the catalog. |
2114 | * |
2115 | * @param int $page Page number of results to retrieve (counting starts at 1) |
2116 | * @param int $limit The size of each page of results to retrieve |
2117 | * @param int $daysOld The maximum age of records to retrieve in days (max. 30) |
2118 | * @param int $fundId optional fund ID to use for limiting results (use a value |
2119 | * returned by getFunds, or exclude for no limit); note that "fund" may be a |
2120 | * misnomer - if funds are not an appropriate way to limit your new item |
2121 | * results, you can return a different set of values from getFunds. The |
2122 | * important thing is that this parameter supports an ID returned by getFunds, |
2123 | * whatever that may mean. |
2124 | * |
2125 | * @throws ILSException |
2126 | * @return array Associative array with 'count' and 'results' keys |
2127 | */ |
2128 | public function getNewItems($page, $limit, $daysOld, $fundId = null) |
2129 | { |
2130 | $items = []; |
2131 | |
2132 | $bindParams = [ |
2133 | ':enddate' => date('d-m-Y', strtotime('now')), |
2134 | ':startdate' => date('d-m-Y', strtotime("-$daysOld day")), |
2135 | ]; |
2136 | |
2137 | $sql = 'select count(distinct LINE_ITEM.BIB_ID) as count ' . |
2138 | "from $this->dbName.LINE_ITEM, " . |
2139 | "$this->dbName.LINE_ITEM_COPY_STATUS, " . |
2140 | "$this->dbName.LINE_ITEM_FUNDS, $this->dbName.FUND " . |
2141 | 'where LINE_ITEM.LINE_ITEM_ID = LINE_ITEM_COPY_STATUS.LINE_ITEM_ID ' . |
2142 | 'and LINE_ITEM_COPY_STATUS.COPY_ID = LINE_ITEM_FUNDS.COPY_ID ' . |
2143 | 'and LINE_ITEM_FUNDS.FUND_ID = FUND.FUND_ID '; |
2144 | if ($fundId) { |
2145 | // Although we're getting an ID value from getFunds() passed in here, |
2146 | // it's not actually an ID -- we use names as IDs (see note in getFunds |
2147 | // itself for more details). |
2148 | $sql .= 'and lower(FUND.FUND_NAME) = :fund '; |
2149 | $bindParams[':fund'] = strtolower($fundId); |
2150 | } |
2151 | $sql .= "and LINE_ITEM.CREATE_DATE >= to_date(:startdate, 'dd-mm-yyyy') " . |
2152 | "and LINE_ITEM.CREATE_DATE < to_date(:enddate, 'dd-mm-yyyy')"; |
2153 | try { |
2154 | $sqlStmt = $this->executeSQL($sql, $bindParams); |
2155 | $row = $sqlStmt->fetch(PDO::FETCH_ASSOC); |
2156 | $items['count'] = $row['COUNT']; |
2157 | } catch (PDOException $e) { |
2158 | $this->throwAsIlsException($e); |
2159 | } |
2160 | |
2161 | $page = ($page) ? $page : 1; |
2162 | $limit = ($limit) ? $limit : 20; |
2163 | $bindParams[':startRow'] = (($page - 1) * $limit) + 1; |
2164 | $bindParams[':endRow'] = ($page * $limit); |
2165 | $sql = 'select * from ' . |
2166 | '(select a.*, rownum rnum from ' . |
2167 | '(select LINE_ITEM.BIB_ID, LINE_ITEM.CREATE_DATE ' . |
2168 | "from $this->dbName.LINE_ITEM, " . |
2169 | "$this->dbName.LINE_ITEM_COPY_STATUS, " . |
2170 | "$this->dbName.LINE_ITEM_STATUS, $this->dbName.LINE_ITEM_FUNDS, " . |
2171 | "$this->dbName.FUND " . |
2172 | 'where LINE_ITEM.LINE_ITEM_ID = LINE_ITEM_COPY_STATUS.LINE_ITEM_ID ' . |
2173 | 'and LINE_ITEM_COPY_STATUS.COPY_ID = LINE_ITEM_FUNDS.COPY_ID ' . |
2174 | 'and LINE_ITEM_STATUS.LINE_ITEM_STATUS = ' . |
2175 | 'LINE_ITEM_COPY_STATUS.LINE_ITEM_STATUS ' . |
2176 | 'and LINE_ITEM_FUNDS.FUND_ID = FUND.FUND_ID '; |
2177 | if ($fundId) { |
2178 | $sql .= 'and lower(FUND.FUND_NAME) = :fund '; |
2179 | } |
2180 | $sql .= "and LINE_ITEM.CREATE_DATE >= to_date(:startdate, 'dd-mm-yyyy') " . |
2181 | "and LINE_ITEM.CREATE_DATE < to_date(:enddate, 'dd-mm-yyyy') " . |
2182 | 'group by LINE_ITEM.BIB_ID, LINE_ITEM.CREATE_DATE ' . |
2183 | 'order by LINE_ITEM.CREATE_DATE desc) a ' . |
2184 | 'where rownum <= :endRow) ' . |
2185 | 'where rnum >= :startRow'; |
2186 | try { |
2187 | $sqlStmt = $this->executeSQL($sql, $bindParams); |
2188 | while ($row = $sqlStmt->fetch(PDO::FETCH_ASSOC)) { |
2189 | $items['results'][]['id'] = $row['BIB_ID']; |
2190 | } |
2191 | return $items; |
2192 | } catch (PDOException $e) { |
2193 | $this->throwAsIlsException($e); |
2194 | } |
2195 | } |
2196 | |
2197 | /** |
2198 | * Get Funds |
2199 | * |
2200 | * Return a list of funds which may be used to limit the getNewItems list. |
2201 | * |
2202 | * @throws ILSException |
2203 | * @return array An associative array with key = fund ID, value = fund name. |
2204 | */ |
2205 | public function getFunds() |
2206 | { |
2207 | $list = []; |
2208 | |
2209 | // Are funds disabled? If so, do no work! |
2210 | if ($this->config['Funds']['disabled'] ?? false) { |
2211 | return $list; |
2212 | } |
2213 | |
2214 | // Load and normalize inclusion/exclusion lists if necessary: |
2215 | $rawIncludeList = $this->config['Funds']['include_list'] |
2216 | ?? $this->config['Funds']['whitelist'] // deprecated terminology |
2217 | ?? null; |
2218 | $include = is_array($rawIncludeList) |
2219 | ? array_map('strtolower', $rawIncludeList) : false; |
2220 | $rawExcludeList = $this->config['Funds']['exclude_list'] |
2221 | ?? $this->config['Funds']['blacklist'] // deprecated terminology |
2222 | ?? null; |
2223 | $exclude = is_array($rawExcludeList) |
2224 | ? array_map('strtolower', $rawExcludeList) : false; |
2225 | |
2226 | // Retrieve the data from Voyager; if we're limiting to a parent fund, we |
2227 | // need to apply a special WHERE clause and bind parameter. |
2228 | if (isset($this->config['Funds']['parent_fund'])) { |
2229 | $bindParams = [':parent' => $this->config['Funds']['parent_fund']]; |
2230 | $whereClause = 'WHERE FUND.PARENT_FUND = :parent'; |
2231 | } else { |
2232 | $bindParams = []; |
2233 | $whereClause = ''; |
2234 | } |
2235 | $sql = 'select distinct lower(FUND.FUND_NAME) as name ' . |
2236 | "from $this->dbName.FUND {$whereClause} order by name"; |
2237 | try { |
2238 | $sqlStmt = $this->executeSQL($sql, $bindParams); |
2239 | while ($row = $sqlStmt->fetch(PDO::FETCH_ASSOC)) { |
2240 | // Process inclusion/exclusion lists to skip illegal values: |
2241 | if ( |
2242 | (is_array($exclude) && in_array($row['NAME'], $exclude)) |
2243 | || (is_array($include) && !in_array($row['NAME'], $include)) |
2244 | ) { |
2245 | continue; |
2246 | } |
2247 | |
2248 | // Normalize the capitalization of the name: |
2249 | $name = ucwords($row['NAME']); |
2250 | |
2251 | // Set the array key to the lookup ID used by getNewItems and the |
2252 | // array value to the on-screen display name. |
2253 | // |
2254 | // We actually want to use the NAME of the fund to do lookups, not |
2255 | // its ID. This is because multiple funds may share the same name, |
2256 | // and it is useful to collate all these results together. To |
2257 | // achieve the effect, we just fill the same value in as the name |
2258 | // and the ID in the return array. |
2259 | // |
2260 | // If you want to change this code to use numeric IDs instead, |
2261 | // you can adjust the SQL above, change the array key used in the |
2262 | // line below, and adjust the lookups done in getNewItems(). |
2263 | $list[$name] = $name; |
2264 | } |
2265 | } catch (PDOException $e) { |
2266 | $this->throwAsIlsException($e); |
2267 | } |
2268 | |
2269 | return $list; |
2270 | } |
2271 | |
2272 | /** |
2273 | * Get Departments |
2274 | * |
2275 | * Obtain a list of departments for use in limiting the reserves list. |
2276 | * |
2277 | * @throws ILSException |
2278 | * @return array An associative array with key = dept. ID, value = dept. name. |
2279 | */ |
2280 | public function getDepartments() |
2281 | { |
2282 | $deptList = []; |
2283 | |
2284 | $sql = 'select DEPARTMENT.DEPARTMENT_ID, DEPARTMENT.DEPARTMENT_NAME ' . |
2285 | "from $this->dbName.RESERVE_LIST, " . |
2286 | "$this->dbName.RESERVE_LIST_COURSES, $this->dbName.DEPARTMENT " . |
2287 | 'where ' . |
2288 | 'RESERVE_LIST.RESERVE_LIST_ID = ' . |
2289 | 'RESERVE_LIST_COURSES.RESERVE_LIST_ID and ' . |
2290 | 'RESERVE_LIST_COURSES.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID ' . |
2291 | 'group by DEPARTMENT.DEPARTMENT_ID, DEPARTMENT_NAME ' . |
2292 | 'order by DEPARTMENT_NAME'; |
2293 | try { |
2294 | $sqlStmt = $this->executeSQL($sql); |
2295 | while ($row = $sqlStmt->fetch(PDO::FETCH_ASSOC)) { |
2296 | $deptList[$row['DEPARTMENT_ID']] = $row['DEPARTMENT_NAME']; |
2297 | } |
2298 | } catch (PDOException $e) { |
2299 | $this->throwAsIlsException($e); |
2300 | } |
2301 | |
2302 | return $deptList; |
2303 | } |
2304 | |
2305 | /** |
2306 | * Get Instructors |
2307 | * |
2308 | * Obtain a list of instructors for use in limiting the reserves list. |
2309 | * |
2310 | * @throws ILSException |
2311 | * @return array An associative array with key = ID, value = name. |
2312 | */ |
2313 | public function getInstructors() |
2314 | { |
2315 | $instList = []; |
2316 | |
2317 | $sql = 'select INSTRUCTOR.INSTRUCTOR_ID, ' . |
2318 | "INSTRUCTOR.LAST_NAME || ', ' || INSTRUCTOR.FIRST_NAME as NAME " . |
2319 | "from $this->dbName.RESERVE_LIST, " . |
2320 | "$this->dbName.RESERVE_LIST_COURSES, $this->dbName.INSTRUCTOR " . |
2321 | 'where RESERVE_LIST.RESERVE_LIST_ID = ' . |
2322 | 'RESERVE_LIST_COURSES.RESERVE_LIST_ID and ' . |
2323 | 'RESERVE_LIST_COURSES.INSTRUCTOR_ID = INSTRUCTOR.INSTRUCTOR_ID ' . |
2324 | 'group by INSTRUCTOR.INSTRUCTOR_ID, LAST_NAME, FIRST_NAME ' . |
2325 | 'order by LAST_NAME'; |
2326 | try { |
2327 | $sqlStmt = $this->executeSQL($sql); |
2328 | while ($row = $sqlStmt->fetch(PDO::FETCH_ASSOC)) { |
2329 | $instList[$row['INSTRUCTOR_ID']] = $row['NAME']; |
2330 | } |
2331 | } catch (PDOException $e) { |
2332 | $this->throwAsIlsException($e); |
2333 | } |
2334 | |
2335 | return $instList; |
2336 | } |
2337 | |
2338 | /** |
2339 | * Get Courses |
2340 | * |
2341 | * Obtain a list of courses for use in limiting the reserves list. |
2342 | * |
2343 | * @throws ILSException |
2344 | * @return array An associative array with key = ID, value = name. |
2345 | */ |
2346 | public function getCourses() |
2347 | { |
2348 | $courseList = []; |
2349 | |
2350 | $sql = "select COURSE.COURSE_NUMBER || ': ' || COURSE.COURSE_NAME as NAME," . |
2351 | ' COURSE.COURSE_ID ' . |
2352 | "from $this->dbName.RESERVE_LIST, " . |
2353 | "$this->dbName.RESERVE_LIST_COURSES, $this->dbName.COURSE " . |
2354 | 'where RESERVE_LIST.RESERVE_LIST_ID = ' . |
2355 | 'RESERVE_LIST_COURSES.RESERVE_LIST_ID and ' . |
2356 | 'RESERVE_LIST_COURSES.COURSE_ID = COURSE.COURSE_ID ' . |
2357 | 'group by COURSE.COURSE_ID, COURSE_NUMBER, COURSE_NAME ' . |
2358 | 'order by COURSE_NUMBER'; |
2359 | try { |
2360 | $sqlStmt = $this->executeSQL($sql); |
2361 | while ($row = $sqlStmt->fetch(PDO::FETCH_ASSOC)) { |
2362 | $courseList[$row['COURSE_ID']] = $row['NAME']; |
2363 | } |
2364 | } catch (PDOException $e) { |
2365 | $this->throwAsIlsException($e); |
2366 | } |
2367 | |
2368 | return $courseList; |
2369 | } |
2370 | |
2371 | /** |
2372 | * Find Reserves |
2373 | * |
2374 | * Obtain information on course reserves. |
2375 | * |
2376 | * This version of findReserves was contributed by Matthew Hooper and includes |
2377 | * support for electronic reserves (though eReserve support is still a work in |
2378 | * progress). |
2379 | * |
2380 | * @param string $course ID from getCourses (empty string to match all) |
2381 | * @param string $inst ID from getInstructors (empty string to match all) |
2382 | * @param string $dept ID from getDepartments (empty string to match all) |
2383 | * |
2384 | * @throws ILSException |
2385 | * @return array An array of associative arrays representing reserve items. |
2386 | */ |
2387 | public function findReserves($course, $inst, $dept) |
2388 | { |
2389 | $recordList = []; |
2390 | $reserveWhere = []; |
2391 | $bindParams = []; |
2392 | |
2393 | if ($course != '') { |
2394 | $reserveWhere[] = 'RESERVE_LIST_COURSES.COURSE_ID = :course'; |
2395 | $bindParams[':course'] = $course; |
2396 | } |
2397 | if ($inst != '') { |
2398 | $reserveWhere[] = 'RESERVE_LIST_COURSES.INSTRUCTOR_ID = :inst'; |
2399 | $bindParams[':inst'] = $inst; |
2400 | } |
2401 | if ($dept != '') { |
2402 | $reserveWhere[] = 'RESERVE_LIST_COURSES.DEPARTMENT_ID = :dept'; |
2403 | $bindParams[':dept'] = $dept; |
2404 | } |
2405 | |
2406 | $reserveWhere = empty($reserveWhere) ? |
2407 | '' : 'where (' . implode(' AND ', $reserveWhere) . ')'; |
2408 | |
2409 | /* OLD SQL -- simpler but without support for the Solr-based reserves |
2410 | * module: |
2411 | $sql = " select MFHD_MASTER.DISPLAY_CALL_NO, BIB_TEXT.BIB_ID, " . |
2412 | " BIB_TEXT.AUTHOR, BIB_TEXT.TITLE, " . |
2413 | " BIB_TEXT.PUBLISHER, BIB_TEXT.PUBLISHER_DATE " . |
2414 | " FROM $this->dbName.BIB_TEXT, $this->dbName.MFHD_MASTER where " . |
2415 | " bib_text.bib_id = (select bib_mfhd.bib_id " . |
2416 | " from $this->dbName.bib_mfhd " . |
2417 | " where bib_mfhd.mfhd_id = mfhd_master.mfhd_id) " . |
2418 | " and " . |
2419 | " mfhd_master.mfhd_id in ( ". |
2420 | " ((select distinct eitem.mfhd_id from $this->dbName.eitem where " . |
2421 | " eitem.eitem_id in " . |
2422 | " (select distinct reserve_list_eitems.eitem_id from " . |
2423 | " $this->dbName.reserve_list_eitems" . |
2424 | " where reserve_list_eitems.reserve_list_id in " . |
2425 | " (select distinct reserve_list_courses.reserve_list_id from " . |
2426 | " $this->dbName.reserve_list_courses " . |
2427 | " $reserveWhere )) )) union " . |
2428 | " ((select distinct mfhd_item.mfhd_id from $this->dbName.mfhd_item" . |
2429 | " where mfhd_item.item_id in " . |
2430 | " (select distinct reserve_list_items.item_id from " . |
2431 | " $this->dbName.reserve_list_items" . |
2432 | " where reserve_list_items.reserve_list_id in " . |
2433 | " (select distinct reserve_list_courses.reserve_list_id from " . |
2434 | " $this->dbName.reserve_list_courses $reserveWhere )) )) " . |
2435 | " ) "; |
2436 | */ |
2437 | $sql = ' select MFHD_MASTER.DISPLAY_CALL_NO, BIB_TEXT.BIB_ID, ' . |
2438 | ' BIB_TEXT.AUTHOR, BIB_TEXT.TITLE, ' . |
2439 | ' BIB_TEXT.PUBLISHER, BIB_TEXT.PUBLISHER_DATE, subquery.COURSE_ID, ' . |
2440 | ' subquery.INSTRUCTOR_ID, subquery.DEPARTMENT_ID ' . |
2441 | " FROM $this->dbName.BIB_TEXT " . |
2442 | " JOIN $this->dbName.BIB_MFHD ON BIB_TEXT.BIB_ID=BIB_MFHD.BIB_ID " . |
2443 | " JOIN $this->dbName.MFHD_MASTER " . |
2444 | ' ON BIB_MFHD.MFHD_ID = MFHD_MASTER.MFHD_ID' . |
2445 | ' JOIN ' . |
2446 | ' ( ' . |
2447 | ' ((select distinct eitem.mfhd_id, subsubquery1.COURSE_ID, ' . |
2448 | ' subsubquery1.INSTRUCTOR_ID, subsubquery1.DEPARTMENT_ID ' . |
2449 | " from $this->dbName.eitem join " . |
2450 | ' (select distinct reserve_list_eitems.eitem_id, ' . |
2451 | ' RESERVE_LIST_COURSES.COURSE_ID, ' . |
2452 | ' RESERVE_LIST_COURSES.INSTRUCTOR_ID, ' . |
2453 | ' RESERVE_LIST_COURSES.DEPARTMENT_ID from ' . |
2454 | " $this->dbName.reserve_list_eitems" . |
2455 | " JOIN $this->dbName.reserve_list_courses ON " . |
2456 | ' reserve_list_courses.reserve_list_id = ' . |
2457 | ' reserve_list_eitems.reserve_list_id' . |
2458 | " $reserveWhere ) subsubquery1 ON " . |
2459 | ' subsubquery1.eitem_id = eitem.eitem_id)) union ' . |
2460 | ' ((select distinct mfhd_item.mfhd_id, subsubquery2.COURSE_ID, ' . |
2461 | ' subsubquery2.INSTRUCTOR_ID, subsubquery2.DEPARTMENT_ID ' . |
2462 | " from $this->dbName.mfhd_item join" . |
2463 | ' (select distinct reserve_list_items.item_id, ' . |
2464 | ' RESERVE_LIST_COURSES.COURSE_ID, ' . |
2465 | ' RESERVE_LIST_COURSES.INSTRUCTOR_ID, ' . |
2466 | ' RESERVE_LIST_COURSES.DEPARTMENT_ID from ' . |
2467 | " $this->dbName.reserve_list_items" . |
2468 | " JOIN $this->dbName.reserve_list_courses on " . |
2469 | ' reserve_list_items.reserve_list_id = ' . |
2470 | ' reserve_list_courses.reserve_list_id' . |
2471 | " $reserveWhere) subsubquery2 ON " . |
2472 | ' subsubquery2.item_id = mfhd_item.item_id )) ' . |
2473 | ' ) subquery ON mfhd_master.mfhd_id = subquery.mfhd_id '; |
2474 | |
2475 | try { |
2476 | $sqlStmt = $this->executeSQL($sql, $bindParams); |
2477 | while ($row = $sqlStmt->fetch(PDO::FETCH_ASSOC)) { |
2478 | $recordList[] = $row; |
2479 | } |
2480 | } catch (PDOException $e) { |
2481 | $this->throwAsIlsException($e); |
2482 | } |
2483 | |
2484 | return $recordList; |
2485 | } |
2486 | |
2487 | /** |
2488 | * Get bib records for recently returned items. |
2489 | * |
2490 | * @param int $limit Maximum number of records to retrieve (default = 30) |
2491 | * @param int $maxage The maximum number of days to consider "recently |
2492 | * returned." |
2493 | * @param array $patron Patron Data |
2494 | * |
2495 | * @return array |
2496 | * |
2497 | * @SuppressWarnings(PHPMD.UnusedFormalParameter) |
2498 | */ |
2499 | public function getRecentlyReturnedBibs( |
2500 | $limit = 30, |
2501 | $maxage = 30, |
2502 | $patron = null |
2503 | ) { |
2504 | $recordList = []; |
2505 | |
2506 | // Oracle does not support the SQL LIMIT clause before version 12, so |
2507 | // instead we need to provide an optimizer hint, which requires us to |
2508 | // ensure that $limit is a valid integer. |
2509 | $intLimit = intval($limit); |
2510 | $safeLimit = $intLimit < 1 ? 30 : $intLimit; |
2511 | |
2512 | $sql = "select /*+ FIRST_ROWS($safeLimit) */ BIB_MFHD.BIB_ID, " |
2513 | . 'max(CIRC_TRANS_ARCHIVE.DISCHARGE_DATE) as RETURNED ' |
2514 | . "from $this->dbName.CIRC_TRANS_ARCHIVE " |
2515 | . "join $this->dbName.MFHD_ITEM " |
2516 | . 'on CIRC_TRANS_ARCHIVE.ITEM_ID = MFHD_ITEM.ITEM_ID ' |
2517 | . "join $this->dbName.BIB_MFHD " |
2518 | . 'on BIB_MFHD.MFHD_ID = MFHD_ITEM.MFHD_ID ' |
2519 | . "join $this->dbName.BIB_MASTER " |
2520 | . 'on BIB_MASTER.BIB_ID = BIB_MFHD.BIB_ID ' |
2521 | . 'where CIRC_TRANS_ARCHIVE.DISCHARGE_DATE is not null ' |
2522 | . 'and CIRC_TRANS_ARCHIVE.DISCHARGE_DATE > SYSDATE - :maxage ' |
2523 | . "and BIB_MASTER.SUPPRESS_IN_OPAC='N' " |
2524 | . 'group by BIB_MFHD.BIB_ID ' |
2525 | . 'order by RETURNED desc'; |
2526 | try { |
2527 | $sqlStmt = $this->executeSQL($sql, [':maxage' => $maxage]); |
2528 | while ( |
2529 | count($recordList) < $limit |
2530 | && $row = $sqlStmt->fetch(PDO::FETCH_ASSOC) |
2531 | ) { |
2532 | $recordList[] = ['id' => $row['BIB_ID']]; |
2533 | } |
2534 | } catch (PDOException $e) { |
2535 | $this->throwAsIlsException($e); |
2536 | } |
2537 | return $recordList; |
2538 | } |
2539 | |
2540 | /** |
2541 | * Get bib records for "trending" items (recently returned with high usage). |
2542 | * |
2543 | * @param int $limit Maximum number of records to retrieve (default = 30) |
2544 | * @param int $maxage The maximum number of days' worth of data to examine. |
2545 | * @param array $patron Patron Data |
2546 | * |
2547 | * @return array |
2548 | * |
2549 | * @SuppressWarnings(PHPMD.UnusedFormalParameter) |
2550 | */ |
2551 | public function getTrendingBibs($limit = 30, $maxage = 30, $patron = null) |
2552 | { |
2553 | $recordList = []; |
2554 | |
2555 | // Oracle does not support the SQL LIMIT clause before version 12, so |
2556 | // instead we need to provide an optimizer hint, which requires us to |
2557 | // ensure that $limit is a valid integer. |
2558 | $intLimit = intval($limit); |
2559 | $safeLimit = $intLimit < 1 ? 30 : $intLimit; |
2560 | |
2561 | $sql = "select /*+ FIRST_ROWS($safeLimit) */ BIB_MFHD.BIB_ID, " |
2562 | . 'count(CIRC_TRANS_ARCHIVE.DISCHARGE_DATE) as RECENT, ' |
2563 | . 'sum(ITEM.HISTORICAL_CHARGES) as OVERALL ' |
2564 | . "from $this->dbName.CIRC_TRANS_ARCHIVE " |
2565 | . "join $this->dbName.MFHD_ITEM " |
2566 | . 'on CIRC_TRANS_ARCHIVE.ITEM_ID = MFHD_ITEM.ITEM_ID ' |
2567 | . "join $this->dbName.BIB_MFHD " |
2568 | . 'on BIB_MFHD.MFHD_ID = MFHD_ITEM.MFHD_ID ' |
2569 | . "join $this->dbName.ITEM " |
2570 | . 'on CIRC_TRANS_ARCHIVE.ITEM_ID = ITEM.ITEM_ID ' |
2571 | . "join $this->dbName.BIB_MASTER " |
2572 | . 'on BIB_MASTER.BIB_ID = BIB_MFHD.BIB_ID ' |
2573 | . 'where CIRC_TRANS_ARCHIVE.DISCHARGE_DATE is not null ' |
2574 | . 'and CIRC_TRANS_ARCHIVE.DISCHARGE_DATE > SYSDATE - :maxage ' |
2575 | . "and BIB_MASTER.SUPPRESS_IN_OPAC='N' " |
2576 | . 'group by BIB_MFHD.BIB_ID ' |
2577 | . 'order by RECENT desc, OVERALL desc'; |
2578 | try { |
2579 | $sqlStmt = $this->executeSQL($sql, [':maxage' => $maxage]); |
2580 | while ( |
2581 | count($recordList) < $limit |
2582 | && $row = $sqlStmt->fetch(PDO::FETCH_ASSOC) |
2583 | ) { |
2584 | $recordList[] = ['id' => $row['BIB_ID']]; |
2585 | } |
2586 | } catch (PDOException $e) { |
2587 | $this->throwAsIlsException($e); |
2588 | } |
2589 | return $recordList; |
2590 | } |
2591 | |
2592 | /** |
2593 | * Get suppressed records. |
2594 | * |
2595 | * @throws ILSException |
2596 | * @return array ID numbers of suppressed records in the system. |
2597 | */ |
2598 | public function getSuppressedRecords() |
2599 | { |
2600 | $list = []; |
2601 | |
2602 | $sql = 'select BIB_MASTER.BIB_ID ' . |
2603 | "from $this->dbName.BIB_MASTER " . |
2604 | "where BIB_MASTER.SUPPRESS_IN_OPAC='Y'"; |
2605 | try { |
2606 | $sqlStmt = $this->executeSQL($sql); |
2607 | while ($row = $sqlStmt->fetch(PDO::FETCH_ASSOC)) { |
2608 | $list[] = $row['BIB_ID']; |
2609 | } |
2610 | } catch (PDOException $e) { |
2611 | $this->throwAsIlsException($e); |
2612 | } |
2613 | |
2614 | return $list; |
2615 | } |
2616 | |
2617 | /** |
2618 | * Execute an SQL query |
2619 | * |
2620 | * @param string|array $sql SQL statement (string or array that includes |
2621 | * bind params) |
2622 | * @param array $bind Bind parameters (if $sql is string) |
2623 | * |
2624 | * @return PDOStatement |
2625 | */ |
2626 | protected function executeSQL($sql, $bind = []) |
2627 | { |
2628 | if (is_array($sql)) { |
2629 | $bind = $sql['bind']; |
2630 | $sql = $sql['string']; |
2631 | } |
2632 | if ($this->logger) { |
2633 | [, $caller] = debug_backtrace(false); |
2634 | $this->debugSQL($caller['function'], $sql, $bind); |
2635 | } |
2636 | $sqlStmt = $this->getDb()->prepare($sql); |
2637 | $sqlStmt->execute($bind); |
2638 | |
2639 | return $sqlStmt; |
2640 | } |
2641 | } |