vikbooking / site / helpers / history.php

history.php in VikBooking Hotel Booking Engine & PMS 1.7.2, at site/helpers/history.php

1,061 lines 29.2 KB

Raw Download Zip

1	<?php
2	/**
3	* @package VikBooking
4	* @subpackage com_vikbooking
5	* @author Alessio Gaggii - e4j - Extensionsforjoomla.com
6	* @copyright Copyright (C) 2018 e4j - Extensionsforjoomla.com. All rights reserved.
7	* @license GNU General Public License version 2 or later; see LICENSE
8	* @link https://vikwp.com
9	*/
10
11	defined('ABSPATH') or die('No script kiddies please!');
12
13	/**
14	* Handles all the events involving a reservation.
15	*/
16	class VboBookingHistory
17	{
18	/**
19	* @var int
20	*/
21	protected $bid = null;
22
23	/**
24	* @var array
25	*/
26	protected $prevBooking = null;
27
28	/**
29	* @var mixed
30	*/
31	protected $data = null;
32
33	/**
34	* @var object
35	*/
36	protected $dbo = null;
37
38	/**
39	* @var array
40	*/
41	protected $typesMap = [];
42
43	/**
44	* List of event types worthy of a notification.
45	*
46	* @var array
47	*
48	* @since 1.15.0 (J) - 1.5.0 (WP)
49	*/
50	protected $worthy_events = [
51	'NC',
52	'MW',
53	'NP',
54	'P0',
55	'PN',
56	'CR',
57	'CW',
58	'MC',
59	'CC',
60	'NO',
61	'PO',
62	'IR',
63	'PC',
64	'UR',
65	];
66
67	/**
68	* List of event types supported by the Notifications Center.
69	*
70	* @var array
71	*
72	* @since 1.16.8 (J) - 1.6.8 (WP)
73	*/
74	protected $notificationsCenterTypes = [
75	// New booking with status Confirmed
76	'NC',
77	// Booking paid for the first time
78	'P0',
79	// Booking paid for a second time
80	'PN',
81	// Booking modified from website
82	'MW',
83	// Cancellation request message
84	'CR',
85	// Booking cancelled via front-end website
86	'CW',
87	// Booking modified from channel
88	'MC',
89	// Booking cancelled from channel
90	'CC',
91	// New Booking from OTA
92	'NO',
93	// Overbooking
94	'OB',
95	// Channel Manager payout notification
96	'PO',
97	// Pre-checkin updated via front-end
98	'PC',
99	// Guest review received
100	'GR',
101	];
102
103	/**
104	* Class constructor.
105	*
106	* @param int $bid optional booking ID to set.
107	*
108	* @since 1.16.5 (J) - 1.6.5 (WP) added argument $bid.
109	*/
110	public function __construct($bid = 0)
111	{
112	$this->dbo = JFactory::getDbo();
113	$this->typesMap = $this->getTypesMap();
114
115	if ($bid) {
116	$this->bid = (int)$bid;
117	}
118	}
119
120	/**
121	* Returns an array of types mapped to
122	* the corresponding language definition.
123	* All the history types should be listed here.
124	*
125	* @return array
126	*/
127	public function getTypesMap()
128	{
129	return [
130	// New booking with status Confirmed
131	'NC' => JText::translate('VBOBOOKHISTORYTNC'),
132	// Booking modified from website
133	'MW' => JText::translate('VBOBOOKHISTORYTMW'),
134	// Booking modified from back-end
135	'MB' => JText::translate('VBOBOOKHISTORYTMB'),
136	// New booking from back-end
137	'NB' => JText::translate('VBOBOOKHISTORYTNB'),
138	// New booking with status Pending
139	'NP' => JText::translate('VBOBOOKHISTORYTNP'),
140	// Booking paid for the first time
141	'P0' => JText::translate('VBOBOOKHISTORYTP0'),
142	// Booking paid for a second time
143	'PN' => JText::translate('VBOBOOKHISTORYTPN'),
144	// Cancellation request message
145	'CR' => JText::translate('VBOBOOKHISTORYTCR'),
146	// Booking cancelled via front-end website
147	'CW' => JText::translate('VBOBOOKHISTORYTCW'),
148	// Booking auto cancelled via front-end
149	'CA' => JText::translate('VBOBOOKHISTORYTCA'),
150	// Booking cancelled via back-end by admin
151	'CB' => JText::translate('VBOBOOKHISTORYTCB'),
152	// Booking Receipt generated via back-end
153	'BR' => JText::translate('VBOBOOKHISTORYTBR'),
154	// Booking Invoice generated
155	'BI' => JText::translate('VBOBOOKHISTORYTBI'),
156	// Booking registration unset status by admin
157	'RA' => JText::translate('VBOBOOKHISTORYTRA'),
158	// Booking checked-in status set by admin
159	'RB' => JText::translate('VBOBOOKHISTORYTRB'),
160	// Booking checked-out status set by admin
161	'RC' => JText::translate('VBOBOOKHISTORYTRC'),
162	// Booking no-show status set by admin
163	'RZ' => JText::translate('VBOBOOKHISTORYTRZ'),
164	// Booking set to Confirmed by admin
165	'TC' => JText::translate('VBOBOOKHISTORYTTC'),
166	// Booking set to Confirmed via App
167	'AC' => JText::translate('VBOBOOKHISTORYTAC'),
168	// Booking modified from channel
169	'MC' => JText::translate('VBOBOOKHISTORYTMC'),
170	// Booking cancelled from channel
171	'CC' => JText::translate('VBOBOOKHISTORYTCC'),
172	// Booking removed via App
173	'AR' => JText::translate('VBOBOOKHISTORYTAR'),
174	// Booking modified via App
175	'AM' => JText::translate('VBOBOOKHISTORYTAM'),
176	// New booking via App
177	'AN' => JText::translate('VBOBOOKHISTORYTAN'),
178	// New Booking from OTA
179	'NO' => JText::translate('VBOBOOKHISTORYTNO'),
180	// Report affecting the booking
181	'RP' => JText::translate('VBOBOOKHISTORYTRP'),
182	// Custom email sent to the customer by admin
183	'CE' => JText::translate('VBOBOOKHISTORYTCE'),
184	// Custom SMS sent to the customer by admin
185	'CS' => JText::translate('VBOBOOKHISTORYTCS'),
186	// Confirmation email re-sent by admin to guest
187	'ER' => JText::translate('VBRESENDORDEMAIL'),
188	// Payment Update (a new amount paid has been set)
189	'PU' => JText::translate('VBOBOOKHISTORYTPU'),
190	// Upsell Extras via front-end
191	'UE' => JText::translate('VBOBOOKHISTORYTUE'),
192	// Report guest misconduct to OTA
193	'GM' => JText::translate('VBOBOOKHISTORYTGM'),
194	// Send Email Cancellation by admin
195	'EC' => JText::translate('VBOBOOKHISTORYTEC'),
196	// Amount refunded
197	'RF' => JText::translate('VBOBOOKHISTORYTRF'),
198	// Refund Updated
199	'RU' => JText::translate('VBOBOOKHISTORYTRU'),
200	// Payable Amount Updated
201	'PB' => JText::translate('VBOBOOKHISTORYTPB'),
202	// Channel Manager custom event
203	'CM' => JText::translate('VBOBOOKHISTORYTCM'),
204	// Channel Manager payout notification
205	'PO' => JText::translate('VBOBOOKHISTORYTPO'),
206	// Inquiry reservation (website)
207	'IR' => JText::translate('VBOBOOKHISTORYTIR'),
208	// Pre-checkin updated via front-end
209	'PC' => JText::translate('VBOBOOKHISTORYTPC'),
210	// Guest review received
211	'GR' => JText::translate('VBOBOOKHISTORYTGR'),
212	// Upgrade room
213	'UR' => JText::translate('VBOBOOKHISTORYTUR'),
214	// Overbooking
215	'OB' => JText::translate('VBOBOOKHISTORYTOB'),
216	];
217	}
218
219	/**
220	* Accesses the groups of history event types, useful to group multiple events.
221	* Groups are categories of events containing worthy event types of the same context.
222	*
223	* @param string $group get the list of event types of this group.
224	*
225	* @return array associative array of types group or empty array.
226	*
227	* @since 1.16.0 (J) - 1.6.0 (WP)
228	*/
229	public function getTypeGroups($group = null)
230	{
231	// we group various types of events per category
232	$type_groups = [
233	// group "Bookings"
234	'GBK' => [
235	'name' => JText::translate('VBMENUTHREE'),
236	'types' => [
237	'NC',
238	'MW',
239	'NP',
240	'CR',
241	'CW',
242	'MC',
243	'CC',
244	'NO',
245	'IR',
246	'UR',
247	'OB',
248	],
249	],
250	// group "Channel Manager"
251	'GCM' => [
252	'name' => JText::translate('VBMENUCHANNELMANAGER'),
253	'types' => [
254	'MC',
255	'CC',
256	'NO',
257	'PO',
258	'CM',
259	'OB',
260	],
261	],
262	// group "Website"
263	'GWB' => [
264	'name' => JText::translate('VBORDFROMSITE'),
265	'types' => [
266	'NC',
267	'MW',
268	'NP',
269	'CR',
270	'CW',
271	'IR',
272	'PC',
273	'UE',
274	'GR',
275	'UR',
276	],
277	],
278	// group "Payments"
279	'GPM' => [
280	'name' => JText::translate('VBO_HISTORY_GPM'),
281	'types' => [
282	'P0',
283	'PN',
284	'PO',
285	'RF',
286	],
287	],
288	];
289
290	if ($group) {
291	// attempt to fetch the requested group
292	return $type_groups[$group] ?? [];
293	}
294
295	// return the whole associative list
296	return $type_groups;
297	}
298
299	/**
300	* Sets the current booking ID.
301	*
302	* @param int $bid
303	*
304	* @return self
305	**/
306	public function setBid($bid)
307	{
308	$this->bid = (int)$bid;
309
310	return $this;
311	}
312
313	/**
314	* Sets the previous booking array.
315	* To calculate what has changed in the booking after the
316	* modification, VBO uses the method getLogBookingModification().
317	* VCM instead should use this method to tell the class that
318	* what has changed should be calculated to obtain the 'descr'
319	* text of the history record that will be stored.
320	*
321	* @param array $booking
322	*
323	* @return self
324	**/
325	public function setPrevBooking($booking)
326	{
327	if (is_array($booking)) {
328	$this->prevBooking = $booking;
329	}
330
331	return $this;
332	}
333
334	/**
335	* Sets extra data for the current history log.
336	*
337	* @param mixed $data array, object or string of extra data.
338	* Useful to store the amount paid to be invoiced,
339	* or the transaction details of the payments.
340	*
341	* @return self
342	**/
343	public function setExtraData($data)
344	{
345	$this->data = $data;
346
347	return $this;
348	}
349
350	/**
351	* Checks whether the type for the history record is valid.
352	*
353	* @param string $type
354	* @param [bool] $returnit if true, the translated value is returned. Otherwise boolean is returned
355	*
356	* @return boolean
357	*/
358	public function validType($type, $returnit = false)
359	{
360	if ($returnit) {
361	return isset($this->typesMap[strtoupper($type)]) ? $this->typesMap[strtoupper($type)] : $type;
362	}
363
364	return isset($this->typesMap[strtoupper($type)]);
365	}
366
367	/**
368	* Reads the current booking record.
369	*
370	* @param mixed
371	*
372	* @return false\|array
373	*/
374	protected function getBookingInfo()
375	{
376	$q = $this->dbo->getQuery(true)
377	->select('*')
378	->from($this->dbo->qn('#__vikbooking_orders'))
379	->where($this->dbo->qn('id') . ' = ' . (int)$this->bid);
380
381	$this->dbo->setQuery($q, 0, 1);
382	$record = $this->dbo->loadAssoc();
383
384	if (!$record) {
385	return false;
386	}
387
388	return $record;
389	}
390
391	/**
392	* Stores a new history record for the booking.
393	*
394	* @param string $type the char-type of store we are making for the history
395	* @param [string] $descr the description of this booking record (optional)
396	*
397	* @return boolean
398	*/
399	public function store($type, $descr = '')
400	{
401	if (is_null($this->bid) \|\| !$this->validType($type)) {
402	return false;
403	}
404
405	if (!$booking_info = $this->getBookingInfo()) {
406	return false;
407	}
408
409	if (empty($descr) && $this->prevBooking) {
410	/**
411	* VCM (including the App) could set the previous booking information,
412	* so we need to calculate what has changed with the booking.
413	* Load VBO language.
414	*/
415	$lang = JFactory::getLanguage();
416	$lang->load('com_vikbooking', (VBOPlatformDetection::isWordPress() ? VIKBOOKING_ADMIN_LANG : JPATH_ADMINISTRATOR), $lang->getTag(), true);
417	if (!class_exists('VikBooking')) {
418	require_once(VBO_SITE_PATH . DIRECTORY_SEPARATOR . 'helpers' . DIRECTORY_SEPARATOR . 'lib.vikbooking.php');
419	}
420	$descr = VikBooking::getLogBookingModification($this->prevBooking);
421	}
422
423	// get the event dispatcher
424	$dispatcher = VBOFactory::getPlatform()->getDispatcher();
425
426	// build record object
427	$history_record = new stdClass;
428	$history_record->idorder = $this->bid;
429	$history_record->dt = JFactory::getDate()->toSql();
430	$history_record->type = $type;
431	$history_record->descr = $descr ?: null;
432	$history_record->totpaid = (float)$booking_info['totpaid'];
433	$history_record->total = (float)$booking_info['total'];
434	$history_record->data = $this->data;
435
436	/**
437	* Trigger event before saving a new history record.
438	*
439	* @since 1.16.0 (J) - 1.6.0 (WP)
440	*/
441	$results = $dispatcher->filter('onBeforeSaveBookingHistoryVikBooking', [$history_record, $this->prevBooking]);
442	if (in_array(false, $results, true)) {
443	return false;
444	}
445
446	/**
447	* Store the extra data for this history log. Useful to store
448	* information about the amount paid, its invoicing status or any custom data.
449	*
450	* @since 1.12 (J) - 1.1.7 (WP)
451	*/
452	if (!is_null($history_record->data) && !is_scalar($history_record->data)) {
453	$history_record->data = json_encode($history_record->data);
454	}
455
456	// store record
457	if (!$this->dbo->insertObject('#__vikbooking_orderhistory', $history_record, 'id') \|\| !isset($history_record->id)) {
458	return false;
459	}
460
461	/**
462	* Trigger event for the newly history record added.
463	*
464	* @since 1.16.0 (J) - 1.6.0 (WP)
465	*/
466	$dispatcher->trigger('onAfterSaveBookingHistoryVikBooking', [$history_record, $this->prevBooking]);
467
468	/**
469	* Check if the event requires an entry to be stored in the Notifications Center.
470	*
471	* @since 1.16.8 (J) - 1.6.8 (WP)
472	*/
473	$results = $dispatcher->filter('onBeforeSaveHistoryNotificationsCenter', [$history_record, $this->notificationsCenterTypes]);
474	if (in_array($type, $this->notificationsCenterTypes) \|\| in_array(true, $results, true)) {
475	$this->addToNotificationsCenter($history_record, $booking_info);
476	}
477
478	return true;
479	}
480
481	/**
482	* Loads the history records for the booking ID set.
483	*
484	* @param int $offset query offset limit start.
485	* @param int $limit query maximum records to fetch.
486	*
487	* @return array
488	*
489	* @since 1.16.5 (J) - 1.6.5 (WP) added arguments $offset and $limit.
490	*/
491	public function loadHistory($offset = 0, $limit = 0)
492	{
493	if (empty($this->bid)) {
494	return [];
495	}
496
497	$q = $this->dbo->getQuery(true)
498	->select('*')
499	->from($this->dbo->qn('#__vikbooking_orderhistory'))
500	->where($this->dbo->qn('idorder') . ' = ' . (int)$this->bid)
501	->order($this->dbo->qn('dt') . ' DESC')
502	->order($this->dbo->qn('id') . ' DESC');
503
504	$this->dbo->setQuery($q, $offset, $limit);
505
506	return $this->dbo->loadAssocList();
507	}
508
509	/**
510	* Checks whether this booking has an event of the given type.
511	*
512	* @param string $type the type of the event.
513	*
514	* @return mixed last date on success, false otherwise.
515	*
516	* @since 1.13.5 (J) - 1.3.5 (WP)
517	*/
518	public function hasEvent($type)
519	{
520	if (empty($type) \|\| empty($this->bid)) {
521	return false;
522	}
523
524	$q = $this->dbo->getQuery(true)
525	->select($this->dbo->qn('dt'))
526	->from($this->dbo->qn('#__vikbooking_orderhistory'))
527	->where($this->dbo->qn('idorder') . ' = ' . (int)$this->bid)
528	->where($this->dbo->qn('type') . ' = ' . $this->dbo->q($type))
529	->order($this->dbo->qn('dt') . ' DESC');
530
531	$this->dbo->setQuery($q, 0, 1);
532
533	$dt = $this->dbo->loadResult();
534
535	if (!$dt) {
536	return false;
537	}
538
539	return $dt;
540	}
541
542	/**
543	* Returns a list of records with data defined for the given event type.
544	* Useful to get a list of transactions data for the refund operations.
545	*
546	* @param mixed $type string or array event type(s).
547	* @param callable $callvalid callback for the data validation.
548	* @param bool $onlydata whether to get just the event data.
549	*
550	* @return mixed false or array with data records.
551	*
552	* @since 1.14.0 (J) - 1.4.0 (WP)
553	*/
554	public function getEventsWithData($type, $callvalid = null, $onlydata = true)
555	{
556	if (empty($type) \|\| empty($this->bid)) {
557	return false;
558	}
559
560	if (!is_array($type)) {
561	$type = [$type];
562	}
563
564	// quote all given types
565	$types = array_map([$this->dbo, 'q'], $type);
566
567	$q = $this->dbo->getQuery(true)
568	->select('*')
569	->from($this->dbo->qn('#__vikbooking_orderhistory'))
570	->where($this->dbo->qn('idorder') . ' = ' . (int)$this->bid)
571	->where($this->dbo->qn('type') . ' IN (' . implode(', ', $types) . ')')
572	->order($this->dbo->qn('dt') . ' ASC');
573
574	$this->dbo->setQuery($q);
575	$events = $this->dbo->loadAssocList();
576
577	if ($events) {
578	$datas = [];
579	foreach ($events as $k => $e) {
580	$data = json_decode($e['data']);
581	if (!empty($data)) {
582	$events[$k]['data'] = $data;
583	}
584	$valid_data = true;
585	if (is_callable($callvalid)) {
586	$valid_data = call_user_func($callvalid, $events[$k]['data']);
587	}
588	if ($valid_data) {
589	array_push($datas, $events[$k]['data']);
590	}
591	}
592
593	return $onlydata ? $datas : $events;
594	}
595
596	return false;
597	}
598
599	/**
600	* Gets the latest history event per booking within a list.
601	* Only the latest event per booking will be considered.
602	*
603	* @param int $start the query start offset.
604	* @param int $limit the query limit.
605	* @param int $min_id optional minimum history ID to fetch.
606	* @param array $types optional list of event types (or groups) to fetch.
607	*
608	* @return array the list of recent history record objects.
609	*
610	* @since 1.15.0 (J) - 1.5.0 (WP)
611	* @since 1.16.0 (J) - 1.6.0 (WP) query modified to fetch the customer picture.
612	* @since 1.16.0 (J) - 1.6.0 (WP) fourth argument $types introduced.
613	*/
614	public function getLatestBookingEvents($start = 0, $limit = 20, $min_id = 0, array $types = [])
615	{
616	$clauses = [];
617
618	if ($min_id > 0) {
619	$clauses[] = "`h`.`id` > " . (int)$min_id;
620	} else {
621	$clauses[] = "`h`.`id` > 0";
622	}
623
624	if ($types) {
625	// scan for groups
626	$groups = [];
627	foreach ($types as $k => $type) {
628	if ($type && strlen($type) === 3 && $group = $this->getTypeGroups($type)) {
629	$groups = array_merge($groups, $group['types']);
630	unset($types[$k]);
631	}
632	}
633
634	if ($groups) {
635	// merge all types
636	$types = array_merge(array_values($types), array_unique($groups));
637	}
638
639	// quote all values
640	$types = array_map(array($this->dbo, 'q'), array_filter($types));
641
642	if ($types) {
643	// add query clause
644	$clauses[] = "`h`.`type` IN (" . implode(', ', $types) . ")";
645	}
646	}
647
648	/**
649	* On some servers with just 30k records in the history and 5k in bookings,
650	* the INNER JOIN to group the latest history events by booking ID is taking
651	* ~25 seconds to complete, which is not acceptable. This doesn't seem to happen
652	* on every database, but since the admin widget "latest events" requires to get
653	* just the latest history event ID by passing the limit to 1, we should not
654	* use the INNER JOIN all the times to save server resources.
655	* Refactoring performed to abandon also the LEFT JOINS which were timing out in
656	* case of several thousands of records. The widget "latest_events" crashed websites.
657	*
658	* @since 1.15.6 (J) - 1.5.12 (WP)
659	* @since 1.16.0 (J) - 1.6.0 (WP) INNER JOIN dismissed because too slow and not needed.
660	* @since 1.16.1 (J) - 1.6.1 (WP) LEFT JOIN dismissed because too slow, queries split.
661	*/
662
663	// query just the history records without joining any other table, or the records would become hundreds of thousands
664	$q = $this->dbo->getQuery(true)
665	->select($this->dbo->qn('h') . '.*')
666	->from($this->dbo->qn('#__vikbooking_orderhistory', 'h'))
667	->where(implode(' AND ', $clauses))
668	->order($this->dbo->qn('h.dt') . ' DESC')
669	->order($this->dbo->qn('h.id') . ' DESC');
670
671	$this->dbo->setQuery($q, $start, $limit);
672	$rows = $this->dbo->loadObjectList();
673	if (!$rows) {
674	return [];
675	}
676
677	// build a list of reservation IDs
678	$history_orders = [];
679	foreach ($rows as $row) {
680	$history_orders[] = (int)$row->idorder;
681	}
682	$history_orders = array_unique($history_orders);
683
684	// fetch the rest of the information
685	$q = "SELECT `o`.`id` AS `idorder`, `o`.`custdata`, `o`.`status`, `o`.`days`, `o`.`checkin`, `o`.`checkout`, `o`.`totpaid`,
686	`o`.`total`, `o`.`idorderota`, `o`.`channel`, `o`.`country`, `o`.`closure`, `o`.`type` AS `booking_type`, `c`.`pic`,
687	(
688	SELECT CONCAT_WS(' ',`or`.`t_first_name`,`or`.`t_last_name`)
689	FROM `#__vikbooking_ordersrooms` AS `or`
690	WHERE `or`.`idorder`=`o`.`id` LIMIT 1
691	) AS `nominative`
692	FROM `#__vikbooking_orders` AS `o`
693	LEFT JOIN `#__vikbooking_customers_orders` AS `co` ON `co`.`idorder`=`o`.`id`
694	LEFT JOIN `#__vikbooking_customers` AS `c` ON `c`.`id`=`co`.`idcustomer`
695	WHERE `o`.`id` IN (" . implode(', ', $history_orders) . ");";
696
697	$this->dbo->setQuery($q);
698	$rows_data = $this->dbo->loadObjectList();
699	if (!$rows_data) {
700	return [];
701	}
702
703	// merge information to object records
704	foreach ($rows as &$row) {
705	foreach ($rows_data as $row_data) {
706	if ($row_data->idorder != $row->idorder) {
707	continue;
708	}
709
710	// merge object properties by using casting
711	$row = (object) array_merge((array) $row, (array) $row_data);
712
713	break;
714	}
715	}
716
717	// unset last reference
718	unset($row);
719
720	// return the final list
721	return $rows;
722	}
723
724	/**
725	* Helper method to set a new list of worthy event types.
726	*
727	* @param array $worthy_events list of event type identifiers.
728	*
729	* @return self
730	*
731	* @since 1.15.0 (J) - 1.5.0 (WP)
732	*/
733	public function setWorthyEventTypes($worthy_events = [])
734	{
735	if (is_array($worthy_events)) {
736	$this->worthy_events = $worthy_events;
737	}
738
739	return $this;
740	}
741
742	/**
743	* Helper method to get the list of worthy event types.
744	*
745	* @return array
746	*
747	* @since 1.15.4 (J) - 1.5.10 (WP)
748	*/
749	public function getWorthyEventTypes()
750	{
751	return $this->worthy_events;
752	}
753
754	/**
755	* Gets the latest history events worthy of a notification.
756	*
757	* @param int $min_id optional minimum history ID to fetch.
758	* @param int $limit the query limit.
759	*
760	* @return array the list of worthy history record objects.
761	*
762	* @since 1.15.0 (J) - 1.5.0 (WP)
763	* @since 1.16.0 (J) - 1.6.0 (WP) query modified to fetch the customer picture.
764	* INNER JOIN dismissed.
765	*/
766	public function getWorthyEvents($min_id = 0, $limit = 0)
767	{
768	$clauses = [
769	"`o`.`status` IS NOT NULL",
770	"`o`.`closure` = 0"
771	];
772
773	if ($min_id > 0) {
774	$clauses[] = "`h`.`id` > " . (int)$min_id;
775	}
776
777	if ($this->worthy_events) {
778	// quote all worthy types of event
779	$worthy_types = array_map(array($this->dbo, 'quote'), $this->worthy_events);
780	$clauses[] = "`h`.`type` IN (" . implode(', ', $worthy_types) . ")";
781	}
782
783	// join as few tables as possible to reduce the execution timing
784	$q = $this->dbo->getQuery(true)
785	->select($this->dbo->qn('h') . '.*')
786	->select([
787	$this->dbo->qn('o.status'),
788	$this->dbo->qn('o.days'),
789	$this->dbo->qn('o.checkin'),
790	$this->dbo->qn('o.checkout'),
791	$this->dbo->qn('o.totpaid'),
792	$this->dbo->qn('o.total'),
793	$this->dbo->qn('o.idorderota'),
794	$this->dbo->qn('o.channel'),
795	$this->dbo->qn('o.country'),
796	])
797	->from($this->dbo->qn('#__vikbooking_orderhistory', 'h'))
798	->leftJoin($this->dbo->qn('#__vikbooking_orders', 'o') . ' ON ' . $this->dbo->qn('h.idorder') . ' = ' . $this->dbo->qn('o.id'))
799	->where(implode(' AND ', $clauses))
800	->order($this->dbo->qn('h.dt') . ' DESC')
801	->order($this->dbo->qn('h.id') . ' DESC');
802
803	$this->dbo->setQuery($q, 0, $limit);
804	$rows = $this->dbo->loadObjectList();
805
806	if (!$rows) {
807	return [];
808	}
809
810	// set the "pic" property to a default empty value
811	foreach ($rows as &$row) {
812	$row->pic = '';
813	}
814
815	// unset last reference
816	unset($row);
817
818	// build a list of reservation IDs
819	$history_orders = [];
820	foreach ($rows as $row) {
821	$history_orders[] = (int)$row->idorder;
822	}
823	$history_orders = array_unique($history_orders);
824
825	// fetch the rest of the information
826	$q = $this->dbo->getQuery(true)
827	->select([
828	$this->dbo->qn('o.id'),
829	$this->dbo->qn('c.pic'),
830	])
831	->from($this->dbo->qn('#__vikbooking_orders', 'o'))
832	->leftJoin($this->dbo->qn('#__vikbooking_customers_orders', 'co') . ' ON ' . $this->dbo->qn('co.idorder') . ' = ' . $this->dbo->qn('o.id'))
833	->leftJoin($this->dbo->qn('#__vikbooking_customers', 'c') . ' ON ' . $this->dbo->qn('c.id') . ' = ' . $this->dbo->qn('co.idcustomer'))
834	->where($this->dbo->qn('o.id') . ' IN (' . implode(', ', $history_orders) . ')');
835
836	$this->dbo->setQuery($q);
837	$rows_data = $this->dbo->loadObjectList();
838
839	if ($rows_data) {
840	// merge information
841	foreach ($rows as &$row) {
842	foreach ($rows_data as $row_data) {
843	if ($row_data->id != $row->idorder) {
844	continue;
845	}
846
847	// set picture property
848	$row->pic = $row_data->pic;
849	break;
850	}
851	}
852	}
853
854	// return all records found
855	return $rows;
856	}
857
858	/**
859	* Builds a summary of what was changed with a booking modification event.
860	*
861	* @param array $booking_info the booking information record.
862	*
863	* @return string
864	*
865	* @since 1.16.8 (J) - 1.6.8 (WP)
866	*/
867	public function getBookingModificationSummary(array $booking_info)
868	{
869	if ($this->prevBooking) {
870	return '';
871	}
872
873	$data_changed = [];
874
875	// check the stay dates
876	if (date('Y-m-d', $booking_info['checkin']) != date('Y-m-d', $this->prevBooking['checkin'])) {
877	$data_changed[] = 'checkin';
878	}
879	if (date('Y-m-d', $booking_info['checkout']) != date('Y-m-d', $this->prevBooking['checkout'])) {
880	$data_changed[] = 'checkout';
881	}
882	if ($booking_info['days'] != $this->prevBooking['days']) {
883	$data_changed[] = 'days';
884	}
885
886	// check rooms and guests
887	if (!empty($this->prevBooking['rooms_info'])) {
888	// build previous values
889	$prev_room_ids = array_column($this->prevBooking['rooms_info'], 'idroom');
890	$prev_adults = array_column($this->prevBooking['rooms_info'], 'adults');
891	$prev_children = array_column($this->prevBooking['rooms_info'], 'children');
892
893	// build new values
894	$new_rooms_info = VikBooking::loadOrdersRoomsData($booking_info['id']);
895	if ($new_rooms_info && $prev_room_ids) {
896	$now_room_ids = array_column($new_rooms_info, 'idroom');
897	$now_adults = array_column($new_rooms_info, 'adults');
898	$now_children = array_column($new_rooms_info, 'children');
899
900	// normalize arrays
901	$prev_room_ids = array_map('intval', $prev_room_ids);
902	$now_room_ids = array_map('intval', $now_room_ids);
903	sort($prev_room_ids);
904	sort($now_room_ids);
905
906	// check if anything has changed
907	if ($prev_room_ids != $now_room_ids) {
908	$data_changed[] = 'rooms';
909	}
910	if (array_sum($prev_adults) != array_sum($now_adults) \|\| array_sum($prev_children) != array_sum($now_children)) {
911	$data_changed[] = 'guests';
912	}
913	}
914	}
915
916	// check totals
917	if ($booking_info['total'] != $this->prevBooking['total']) {
918	$data_changed[] = 'total';
919	}
920
921	if (!$data_changed) {
922	// no changes detected
923	return '';
924	}
925
926	// obtain the translations for what has changed
927	$factors_changed = array_map(function($changed) {
928	switch ($changed) {
929	case 'checkin':
930	return JText::translate('VBPICKUPAT');
931
932	case 'checkout':
933	return JText::translate('VBRELEASEAT');
934
935	case 'days':
936	return JText::translate('VBOINVTOTNIGHTS');
937
938	case 'rooms':
939	return JText::translate('VBPVIEWORDERSTHREE');
940
941	case 'guests':
942	return JText::translate('VBPVIEWORDERSPEOPLE');
943
944	case 'total':
945	return JText::translate('VBPVIEWORDERSSEVEN');
946
947	default:
948	return $changed;
949	}
950	}, $data_changed);
951
952	if (count($factors_changed) > 1) {
953	// plural version
954	return JText::sprintf('VBO_BOOK_MOD_SUMMARY_PLR', implode(', ', $factors_changed));
955	}
956
957	// singular version
958	return JText::sprintf('VBO_BOOK_MOD_SUMMARY_SNG', implode(', ', $factors_changed));
959	}
960
961	/**
962	* Adds the history event to the Notifications Center.
963	*
964	* @param object $history_record the history record created.
965	* @param array $booking_info the booking information record.
966	*
967	* @return bool
968	*
969	* @since 1.16.8 (J) - 1.6.8 (WP)
970	*/
971	protected function addToNotificationsCenter($history_record, array $booking_info)
972	{
973	if (VikBooking::isSite()) {
974	// load language according to platform
975	$lang = JFactory::getLanguage();
976	if (VBOPlatformDetection::isJoomla()) {
977	// make sure to load the back-end language definitions and any override
978	$lang->load('com_vikbooking', JPATH_ADMINISTRATOR, $lang->getTag(), true);
979	$lang->load('joomla', JPATH_ADMINISTRATOR, $lang->getTag(), true);
980	} else {
981	$lang->load('com_vikbooking', VIKBOOKING_LANG, $lang->getTag(), true);
982
983	// make sure to register the language handler
984	$lib_base_path = defined('VIKBOOKING_LIBRARIES') ? VIKBOOKING_LIBRARIES : '';
985	if (!$lib_base_path && defined('VIKCHANNELMANAGER_LIBRARIES')) {
986	$lib_base_path = str_replace('vikchannelmanager' . DIRECTORY_SEPARATOR . 'libraries', 'vikbooking' . DIRECTORY_SEPARATOR . 'libraries', VIKCHANNELMANAGER_LIBRARIES);
987	}
988
989	if ($lib_base_path) {
990	$lang->attachHandler($lib_base_path . DIRECTORY_SEPARATOR . 'language' . DIRECTORY_SEPARATOR . 'admin.php', 'vikbooking');
991	}
992	}
993
994	// reload history types
995	$this->typesMap = $this->getTypesMap();
996	}
997
998	// notification payload extra options
999	$notif_payload_opts = [];
1000
1001	// determine the notification sender
1002	$sender = 'website';
1003	$channel = null;
1004	if (!empty($booking_info['channel']) && !empty($booking_info['idorderota'])) {
1005	// set proper notification group
1006	$sender = 'otas';
1007	$channel = $booking_info['channel'];
1008
1009	/**
1010	* Set proper notification group and payload in case of a new OTA guest review.
1011	*
1012	* @since 1.16.10 (J) - 1.6.10 (WP)
1013	*/
1014	if (!strcasecmp($history_record->type, 'GR')) {
1015	$sender = 'guests';
1016	$ev_edata = (array) $this->data;
1017	if (($ev_edata['review_id'] ?? null) \|\| ($ev_edata['ota_review_id'] ?? null)) {
1018	// build CTA payload for the notification
1019	$notif_payload_opts = [
1020	'summary' => JText::sprintf('VBO_NEW_GUEST_REVIEW_SUMM', ($ev_edata['guest_name'] ?? ''), ($ev_edata['score'] ?? '?') . '/10'),
1021	'label' => JText::translate('VBO_NEW_GUEST_REVIEW'),
1022	'widget' => 'guest_reviews',
1023	'widget_options' => [
1024	'review_id' => $ev_edata['review_id'] ?? 0,
1025	'ota_review_id' => $ev_edata['ota_review_id'] ?? '',
1026	],
1027	];
1028	}
1029	}
1030	}
1031
1032	// build the notification summary
1033	$summary = $history_record->descr ?? null;
1034	if ($this->prevBooking) {
1035	// in case of booking modification, we compose a summary of just what was changed
1036	$summary = $this->getBookingModificationSummary($booking_info);
1037	$summary = $summary ?: null;
1038	}
1039
1040	// store the notification
1041	try {
1042	$result = VBOFactory::getNotificationCenter()
1043	->store([
1044	array_merge([
1045	'sender' => $sender,
1046	'type' => $history_record->type,
1047	'title' => $this->validType($history_record->type, true),
1048	'summary' => $history_record->descr ?? null,
1049	'idorder' => $history_record->idorder,
1050	'idorderota' => $booking_info['idorderota'] ?: null,
1051	'channel' => $channel,
1052	], $notif_payload_opts),
1053	]);
1054	} catch (Exception $e) {
1055	return false;
1056	}
1057
1058	return !empty($result['new_notifications']);
1059	}
1060	}
1061