reservation.php — VikAppointments Services Booking Calendar 1.2.17

vikappointments / admin / models / reservation.php

vikappointments / admin / models Last commit date

reservation.php

1594 lines

1	<?php
2	/**
3	* @package VikAppointments
4	* @subpackage core
5	* @author E4J s.r.l.
6	* @copyright Copyright (C) 2021 E4J s.r.l. All Rights Reserved.
7	* @license http://www.gnu.org/licenses/gpl-2.0.html GNU/GPL
8	* @link https://vikwp.com
9	*/
10
11	// No direct access
12	defined('ABSPATH') or die('No script kiddies please!');
13
14	VAPLoader::import('libraries.mvc.model');
15
16	/**
17	* VikAppointments appointment model.
18	*
19	* @since 1.7
20	*/
21	class VikAppointmentsModelReservation extends JModelVAP
22	{
23	/**
24	* Basic save implementation.
25	*
26	* @param mixed $data Either an array or an object of data to save.
27	*
28	* @return mixed The ID of the record on success, false otherwise.
29	*/
30	public function save($data)
31	{
32	$dbo = JFactory::getDbo();
33	$app = JFactory::getApplication();
34	$table = $this->getTable();
35
36	$data = (array) $data;
37
38	if (empty($data['id']) && !empty($data['icaluid']))
39	{
40	// search reservation by iCal UID
41	$tmp = $this->getItem(array('icaluid' => $data['icaluid']));
42
43	if ($tmp)
44	{
45	// reservation found, do update
46	$data['id'] = $tmp->id;
47	}
48	}
49
50	if (!empty($data['validate_availability']))
51	{
52	// validate reservation availability
53	if (!$this->isAvailable($data))
54	{
55	// The selected slot doesn't seem to be available...
56	// Register data within the user state before aborting.
57	$app->setUserState('vap.reservation.data', $data);
58
59	return false;
60	}
61	}
62
63	if (!empty($data['id']))
64	{
65	// register current datetime as modified date, if not specified
66	if (!isset($data['modifiedon']))
67	{
68	$data['modifiedon'] = JFactory::getDate()->toSql();
69	}
70
71	// load order details
72	$table->load($data['id']);
73	}
74
75	/**
76	* When fetching the statistics, we may have to convert the check-in time to
77	* the offset of the assigned employee. Since SQL engines do not provide default
78	* tools, we need to always keep up-to-date the offset of the check-in in order
79	* to adjust the dates at runtime without having to care of DST issues.
80	*
81	* For this reason, every time the check-in and the employee are provided, we
82	* have to refresh the timezone offset of the check-in.
83	*/
84	if (!empty($data['id_employee']) && !empty($data['checkin_ts']) && !VAPDateHelper::isNull($data['checkin_ts']))
85	{
86	// get employee timezone
87	$tz = JModelVAP::getInstance('employee')->getTimezone($data['id_employee']);
88	// create check-in date
89	$checkin = new JDate($data['checkin_ts'], $tz);
90	// get timezone offset for the selected check-in date time
91	$data['tz_offset'] = $checkin->format('P', $local = true);
92	}
93
94	// get reservation-option model
95	$model = JModelVAP::getInstance('resoptassoc');
96
97	if (!empty($data['deletedOptions']) && !isset($data['discount']))
98	{
99	// get total discount of items to remove
100	$discount = $model->getTotalDiscount($data['deletedOptions']);
101
102	if ($discount > 0)
103	{
104	// subtract discount of items to remove from the total one
105	$data['discount'] = max(array(0, $table->discount - $discount));
106	}
107	}
108
109	if (empty($data['id']) && !isset($data['view_emp']) && !empty($data['id_service']))
110	{
111	// use only positive values, so that we can avoid a query in case of ID equals to -1
112	$id_service = $data['id_service'] > 0 ? $data['id_service'] : 0;
113
114	// while creating a new record check whether we should display
115	// the assigned employee to the customer
116	$service = JModelVAP::getInstance('service')->getItem($id_service);
117
118	if ($service)
119	{
120	// display employee in case the service allows its selection
121	$data['view_emp'] = (int) $service->choose_emp;
122	}
123	}
124
125	// always recover the service sleep time (if not specified) while creating a new appointment
126	if (!isset($data['sleep']) && empty($data['id']) && !empty($data['id_service']) && !empty($data['id_employee']))
127	{
128	// load employee overrides
129	$service = JModelVAP::getInstance('serempassoc')->getOverrides($data['id_service'], $data['id_employee']);
130
131	if ($service)
132	{
133	// use default service sleep time
134	$data['sleep'] = $service->sleep;
135	}
136	}
137
138	if (empty($data['id']) && empty($data['status']))
139	{
140	// status not specified, use the default confirmed one
141	$data['status'] = JHtml::fetch('vaphtml.status.confirmed', 'appointments', 'code');
142	}
143
144	// get order statuses handler
145	$orderStatus = VAPOrderStatus::getInstance();
146
147	$prev_status = null;
148
149	if (!empty($data['status']) && !empty($data['id']))
150	{
151	// register previous order status
152	$prev_status = $orderStatus->getStatus($data['id']);
153	}
154
155	// attempt to save the reservation
156	$id = parent::save($data);
157
158	if (!$id)
159	{
160	// an error occurred, do not go ahead
161	return false;
162	}
163
164	// always clear order from cache after saving
165	VAPLoader::import('libraries.order.factory');
166	VAPOrderFactory::changed('appointment', $id);
167
168	if (empty($data['id']) && !isset($data['id_parent']))
169	{
170	// we are creating a new single reservation, so we need
171	// to update the record to link the parent id with the PK
172	$tmp = new stdClass;
173	$tmp->id = $id;
174	$tmp->id_parent = $id;
175
176	$dbo->updateObject('#__vikappointments_reservation', $tmp, 'id');
177	}
178
179	if (!empty($data['deletedOptions']))
180	{
181	// delete specified options, needed to properly
182	// apply discount calculation (if requested)
183	$model->delete($data['deletedOptions']);
184	}
185
186	if (!empty($data['options']))
187	{
188	foreach ((array) $data['options'] as $item)
189	{
190	// check if we are dealing with a JSON object
191	$item = is_string($item) ? json_decode($item, true) : (array) $item;
192	// make relation with saved order
193	$item['id_reservation'] = $id;
194
195	// save item
196	$model->save($item);
197	}
198	}
199
200	// Check whether the status has changed.
201	// Create a new status record also for new reservations
202	if (!empty($data['status']) && $data['status'] != $prev_status)
203	{
204	if (empty($data['status_comment']))
205	{
206	// use default status comment
207	$data['status_comment'] = 'VAP_STATUS_CHANGED_ON_MANAGE';
208	}
209
210	// track status change
211	$orderStatus->keepTrack($data['status'], $id, $data['status_comment']);
212
213	// check if we have an existing parent order
214	if ($table->id && $table->id_parent <= 0)
215	{
216	// get multi-order model
217	$multiOrderModel = JModelVAP::getInstance('multiorder');
218
219	// iterate orders found
220	foreach ($multiOrderModel->getChildren($table->id, 'id') as $order_id)
221	{
222	// prepare data to save for child record
223	$childData = array(
224	'id' => (int) $order_id,
225	'status' => $data['status'],
226	'status_comment' => $data['status_comment'],
227	);
228
229	// save on cascade
230	$this->save($childData);
231	}
232	}
233	else
234	{
235	// check whether the order was previously approved
236	$was_approved = JHtml::fetch('vaphtml.status.isapproved', 'appointments', $prev_status);
237	// check whether the order has been cancelled
238	$is_now_cancelled = JHtml::fetch('vaphtml.status.iscancelled', 'appointments', $data['status']);
239
240	if ($is_now_cancelled && $was_approved)
241	{
242	/**
243	* Try to unredeem a package if the order has been cancelled.
244	* The service cost must be zero too in order to prove that a package was redeemed.
245	*
246	* @since 1.6.3
247	*/
248	if ($table->service_price == 0)
249	{
250	// get package model
251	$package = JModelVAP::getInstance('packorder');
252
253	// unredeem packages
254	$unredeemed = $package->usePackages($data['id'], $increase = false);
255
256	if ($unredeemed)
257	{
258	if ($app->isClient('administrator'))
259	{
260	// message for back-end
261	$app->enqueueMessage(JText::sprintf('VAPORDERUNREDEEMEDPACKS', $unredeemed));
262	}
263	else
264	{
265	// message for front-end
266	$app->enqueueMessage(JText::translate('VAPRESTOREPACKSONCANCEL'), 'notice');
267	}
268	}
269	}
270
271	// Check whether the appointment was paid and it is now cancelled.
272	// In case of a multi-order, the total amount will be summed recursively by each
273	// appointment assigned to the order, since the prices totals are proportional.
274	if ($table->id_user > 0 && $table->total_cost && $is_now_cancelled && JHtml::fetch('vaphtml.status.ispaid', 'appointments', $prev_status))
275	{
276	// Remove the payment charge from the total paid.
277	// Ignore the charge if it is a discount.
278	$credit = $table->total_cost - max(array($table->payment_charge + $table->payment_tax, 0));
279
280	// increase user credit by the amount paid, if any
281	JModelVAP::getInstance('customer')->addCredit($table->id_user, $credit);
282	}
283	}
284	}
285	}
286
287	// check whether we should apply or delete a discount
288	if (!empty($data['add_discount']))
289	{
290	$this->addDiscount($id, $data['add_discount']);
291	}
292	else if (!empty($data['remove_discount']))
293	{
294	$this->removeDiscount($id);
295	}
296
297	if (!empty($data['notifycust']))
298	{
299	// define options
300	$options = array(
301	'id' => isset($data['mail_custom_text']) ? $data['mail_custom_text'] : null,
302	'default' => isset($data['exclude_default_mail_texts']) ? !$data['exclude_default_mail_texts'] : null,
303	);
304
305	// send e-mail notification to customer
306	$this->sendEmailNotification($id, $options);
307	}
308
309	if (!empty($data['notifyemp']))
310	{
311	// send e-mail notification to employee
312	$this->sendEmailNotification($id, array('client' => 'employee'));
313	}
314
315	if (!empty($data['notifywl']) && !empty($data['status']))
316	{
317	// check whether we have a cancelled status
318	if (JHtml::fetch('vaphtml.status.iscancelled', 'appointments', $data['status']))
319	{
320	// process waiting list queue
321	JModelVAP::getInstance('waitinglist')->notify($id);
322	}
323	}
324
325	if (!empty($data['notes']))
326	{
327	// create new user notes for this appointment
328	JModelVAP::getInstance('usernote')->save(array(
329	'group' => 'appointments',
330	'id_parent' => $id,
331	'content' => $data['notes'],
332	'id' => isset($data['id_notes']) ? (int) $data['id_notes'] : 0,
333	));
334	}
335
336	// check if we have an existing child appointment
337	if ($table->id && $this->isChildAppointment($table))
338	{
339	// check whether the total cost has changed
340	if (isset($data['total_cost']) && $data['total_cost'] != $table->total_cost)
341	{
342	// get multi-order model
343	$multiOrderModel = JModelVAP::getInstance('multiorder');
344	// recalculate totals of parent order
345	$multiOrderModel->recalculateTotals($table->id_parent);
346
347	// This may not the best solution because it creates a structure
348	// similar to the circular dependency pattern by creating a link
349	// between the reservation model and the multi-order model.
350	// However, I have to say that this seems to be extremely effective.
351	// The workflow will be observed carefully.
352	}
353	}
354
355	// prepare event data
356	$is_new = empty($data['id']);
357	$data['id'] = $id;
358
359	/**
360	* Trigger event to allow the plugins to make something after saving
361	* an appointment into the database. Fires once all the details of
362	* the appointment has been saved.
363	*
364	* @param array $args The saved record.
365	* @param boolean $is_new True if the record was inserted.
366	* @param JModel $model The model instance.
367	*
368	* @return void
369	*
370	* @since 1.7
371	*/
372	VAPFactory::getEventDispatcher()->trigger('onAfterSaveReservationLate', array($data, $is_new, $this));
373
374	return $id;
375	}
376
377	/**
378	* Extend duplicate implementation to clone any related records
379	* stored within a separated table.
380	*
381	* @param mixed $ids Either the record ID or a list of records.
382	* @param mixed $src Specifies some values to be used while duplicating.
383	* @param array $ignore A list of columns to skip.
384	*
385	* @return mixed The ID of the records on success, false otherwise.
386	*/
387	public function duplicate($ids, $src = array(), $ignore = array())
388	{
389	$new_ids = array();
390
391	// defined default columns that should never be copied
392	$ignore[] = 'sid';
393	$ignore[] = 'conf_key';
394	$ignore[] = 'id_parent';
395	$ignore[] = 'createdon';
396	$ignore[] = 'createdby';
397	$ignore[] = 'log';
398	$ignore[] = 'cc_data';
399	$ignore[] = 'payment_attempt';
400	$ignore[] = 'conversion';
401
402	$dbo = JFactory::getDbo();
403
404	// get reservation options model
405	$optModel = JModelVAP::getInstance('resoptassoc');
406
407	foreach ($ids as $id_reservation)
408	{
409	// start by duplicating the whole record
410	$new_id = parent::duplicate($id_reservation, $src, $ignore);
411
412	if ($new_id)
413	{
414	$new_id = array_shift($new_id);
415
416	// register copied
417	$new_ids[] = $new_id;
418
419	// load any assigned option
420	$q = $dbo->getQuery(true)
421	->select($dbo->qn('id'))
422	->from($dbo->qn('#__vikappointments_res_opt_assoc'))
423	->where($dbo->qn('id_reservation') . ' = ' . (int) $id_reservation);
424
425	$dbo->setQuery($q);
426
427	if ($duplicate = $dbo->loadColumn())
428	{
429	$opt_data = array();
430	$opt_data['id_reservation'] = $new_id;
431
432	// duplicate options by using the new reservation ID
433	$optModel->duplicate($duplicate, $opt_data);
434	}
435	}
436	}
437
438	return $new_ids;
439	}
440
441	/**
442	* Extend delete implementation to delete any related records
443	* stored within a separated table.
444	*
445	* @param mixed $ids Either the record ID or a list of records.
446	*
447	* @return boolean True on success, false otherwise.
448	*/
449	public function delete($ids)
450	{
451	// only int values are accepted
452	$ids = array_map('intval', (array) $ids);
453
454	$dbo = JFactory::getDbo();
455
456	$q = $dbo->getQuery(true)
457	->select($dbo->qn('id'))
458	->from($dbo->qn('#__vikappointments_reservation'))
459	->where(array(
460	$dbo->qn('id_parent') . ' IN (' . implode(',', $ids) . ')',
461	$dbo->qn('id') . ' <> ' . $dbo->qn('id_parent'),
462	), 'AND');
463
464	$dbo->setQuery($q);
465
466	// merge children with specified IDS list
467	$ids = array_merge($ids, array_map('intval', $dbo->loadColumn()));
468
469	// invoke parent first
470	if (!parent::delete($ids))
471	{
472	// nothing to delete
473	return false;
474	}
475
476	// load any reservation-option relation
477	$q = $dbo->getQuery(true)
478	->select($dbo->qn('id'))
479	->from($dbo->qn('#__vikappointments_res_opt_assoc'))
480	->where($dbo->qn('id_reservation') . ' IN (' . implode(',', $ids) . ')' );
481
482	$dbo->setQuery($q);
483
484	if ($assoc_ids = $dbo->loadColumn())
485	{
486	// get reservation-option model
487	$model = JModelVAP::getInstance('resoptassoc');
488	// delete relations
489	$model->delete($assoc_ids);
490	}
491
492	// load any assigned order statuses
493	$q = $dbo->getQuery(true)
494	->select($dbo->qn('id'))
495	->from($dbo->qn('#__vikappointments_order_status'))
496	->where($dbo->qn('type') . ' = ' . $dbo->q('reservation'))
497	->where($dbo->qn('id_order') . ' IN (' . implode(',', $ids) . ')' );
498
499	$dbo->setQuery($q);
500
501	if ($assoc_ids = $dbo->loadColumn())
502	{
503	// get order status model
504	$model = JModelVAP::getInstance('orderstatus');
505	// delete relations
506	$model->delete($assoc_ids);
507	}
508
509	// load any assigned notes
510	$q = $dbo->getQuery(true)
511	->select($dbo->qn('id'))
512	->from($dbo->qn('#__vikappointments_user_notes'))
513	->where($dbo->qn('group') . ' = ' . $dbo->q('appointments'))
514	->where($dbo->qn('id_parent') . ' IN (' . implode(',', $ids) . ')' );
515
516	$dbo->setQuery($q);
517
518	if ($note_ids = $dbo->loadColumn())
519	{
520	// get user notes model
521	$model = JModelVAP::getInstance('usernote');
522	// delete records
523	$model->delete($note_ids);
524	}
525
526	return true;
527	}
528
529	/**
530	* Returns a list of appointments that intersects the specified date time.
531	*
532	* @param string $datetime The date time to look for.
533	* @param integer $id_emp An optional employee ID.
534	*
535	* @return array A list of appointments.
536	*/
537	public function getAppointmentsAt($datetime, $id_emp = 0)
538	{
539	$dbo = JFactory::getDbo();
540
541	// get employee timezone
542	$employee_tz = JModelVAP::getInstance('employee')->getTimezone($id_emp);
543
544	// create date instance and assume it refers to the
545	// timezone of the selected employee (or global one)
546	$date = new JDate($datetime, $employee_tz);
547
548	$q = $dbo->getQuery(true);
549
550	// select all reservation columns
551	$q->select('r.*');
552	$q->from($dbo->qn('#__vikappointments_reservation', 'r'));
553
554	// select service name
555	$q->select($dbo->qn('s.name', 'service_name'));
556	$q->leftjoin($dbo->qn('#__vikappointments_service', 's') . ' ON ' . $dbo->qn('s.id') . ' = ' . $dbo->qn('r.id_service'));
557
558	if ($id_emp)
559	{
560	// filter by employee
561	$q->where($dbo->qn('r.id_employee') . ' = ' . (int) $id_emp);
562	}
563	else
564	{
565	$q->select($dbo->qn('e.nickname', 'employee_name'));
566	$q->leftjoin($dbo->qn('#__vikappointments_employee', 'e') . ' ON ' . $dbo->qn('e.id') . ' = ' . $dbo->qn('r.id_employee'));
567	}
568
569	// get any reserved codes
570	$reserved = JHtml::fetch('vaphtml.status.find', 'code', array('appointments' => 1, 'reserved' => 1));
571
572	if ($reserved)
573	{
574	// filter by reserved status
575	$q->where($dbo->qn('r.status') . ' IN (' . implode(',', array_map(array($dbo, 'q'), $reserved)) . ')');
576	}
577
578	// make sure the specified date stays between the reservation check-in and check-out
579	$q->where($dbo->qn('r.checkin_ts') . ' <= ' . $dbo->q($date->toSql()));
580	$q->where(sprintf(
581	'DATE_ADD(%s, INTERVAL (%s + %s) MINUTE) > %s',
582	$dbo->qn('r.checkin_ts'),
583	$dbo->qn('r.duration'),
584	$dbo->qn('r.sleep'),
585	$dbo->q($date->toSql())
586	));
587
588	$dbo->setQuery($q);
589	return $dbo->loadObjectList();
590	}
591
592	/**
593	* Returns a list of appointments with check-in on the specified date.
594	*
595	* @param string $date The date to look for.
596	* @param integer $id_emp An optional employee ID.
597	*
598	* @return array A list of appointments.
599	*/
600	public function getAppointmentsOn($date, $id_emp = 0)
601	{
602	$dbo = JFactory::getDbo();
603
604	// get employee timezone
605	$employee_tz = JModelVAP::getInstance('employee')->getTimezone($id_emp);
606
607	// create dates range and assume they refer to the
608	// timezone of the selected employee (or global one)
609	$start = new JDate($date, $employee_tz);
610	$start->modify('00:00:00');
611
612	$end = new JDate($date, $employee_tz);
613	$end->modify('23:59:59');
614
615	$q = $dbo->getQuery(true);
616
617	// select all reservation columns
618	$q->select('r.*');
619	$q->from($dbo->qn('#__vikappointments_reservation', 'r'));
620
621	// select service name
622	$q->select($dbo->qn('s.name', 'service_name'));
623	$q->leftjoin($dbo->qn('#__vikappointments_service', 's') . ' ON ' . $dbo->qn('s.id') . ' = ' . $dbo->qn('r.id_service'));
624
625	if ($id_emp)
626	{
627	// filter by employee
628	$q->where($dbo->qn('r.id_employee') . ' = ' . (int) $id_emp);
629	}
630	else
631	{
632	$q->select($dbo->qn('e.nickname', 'employee_name'));
633	$q->leftjoin($dbo->qn('#__vikappointments_employee', 'e') . ' ON ' . $dbo->qn('e.id') . ' = ' . $dbo->qn('r.id_employee'));
634	}
635
636	// get any reserved codes
637	$reserved = JHtml::fetch('vaphtml.status.find', 'code', array('appointments' => 1, 'reserved' => 1));
638
639	if ($reserved)
640	{
641	// filter by reserved status
642	$q->where($dbo->qn('r.status') . ' IN (' . implode(',', array_map(array($dbo, 'q'), $reserved)) . ')');
643	}
644
645	// make sure the specified date stays between the reservation check-in and check-out
646	$q->where(sprintf('%s BETWEEN %s AND %s',
647	$dbo->qn('r.checkin_ts'),
648	$dbo->q($start->toSql()),
649	$dbo->q($end->toSql())
650	));
651
652	// sort by check-in
653	$q->order($dbo->qn('r.checkin_ts') . ' ASC');
654
655	$dbo->setQuery($q);
656	return $dbo->loadObjectList();
657	}
658
659	/**
660	* Checks whether the specified appointment is a child.
661	*
662	* @param mixed $reservation Either a reservation ID or a table object.
663	*
664	* @return boolean True if a child, false otherwise.
665	*/
666	public function isChildAppointment($reservation)
667	{
668	if (is_numeric($reservation))
669	{
670	$table = $this->getTable();
671	$table->load($reservation);
672	$reservation = $table;
673	}
674
675	// check if we have a child appointment assigned to a parent order
676	return $reservation->id_parent > 0 && $reservation->id_parent != $reservation->id;
677	}
678
679	/**
680	* Recalculates the totals of the specified reservation.
681	*
682	* @param object &$reservation The reservation details.
683	* @param mixed $service The service details. If not specified, it will
684	* be automatically loaded. It is also possible to
685	* pass a number to force the service price.
686	*
687	* @return void
688	*/
689	public function recalculateTotals(&$reservation, $service = null)
690	{
691	$wasArray = false;
692
693	if (is_array($reservation))
694	{
695	// cast array to object and register reminder
696	$reservation = (object) $reservation;
697	$wasArray = true;
698	}
699
700	if (is_null($service))
701	{
702	// get service details
703	$service = JModelVAP::getInstance('serempassoc')->getOverrides($reservation->id_service, $reservation->id_employee);
704
705	if (!$service)
706	{
707	if ($wasArray)
708	{
709	// back to array
710	$reservation = (array) $reservation;
711	}
712
713	throw new Exception('Employee/service relation not found.', 404);
714	}
715	}
716
717	if (!isset($reservation->jid))
718	{
719	// use guest user group if not specified
720	$reservation->jid = 0;
721	}
722
723	// in case of a service, calculate the resulting price
724	if (is_object($service))
725	{
726	$checkin = new JDate($reservation->checkin_ts);
727
728	if (!empty($reservation->timezone))
729	{
730	// adjust to the specified timezone
731	$checkin->setTimezone(new DateTimeZone($reservation->timezone));
732	}
733	else
734	{
735	// adjust to the system timezone
736	$checkin->setTimezone(new DateTimeZone(JFactory::getApplication()->get('offset', 'UTC')));
737	}
738
739	/**
740	* Calculate the reservation cost by using the special rates.
741	*
742	* @since 1.6
743	*/
744	$trace = array('id_user' => (int) $reservation->jid);
745
746	$service_price = $price = VAPSpecialRates::getRate($reservation->id_service, $reservation->id_employee, $checkin, $reservation->people, $trace);
747
748	if ($service->priceperpeople)
749	{
750	// multiply by the number of participants
751	$price *= $reservation->people;
752	}
753	}
754	else
755	{
756	// use the specified price
757	$price = (float) $service;
758	}
759
760	if (!empty($reservation->id_user))
761	{
762	// get details of the customer assigned to this reservation
763	$customer = VikAppointments::getCustomer($reservation->id_user);
764	// fetch check-in date time
765	$checkin = isset($reservation->checkin_ts) ? $reservation->checkin_ts : null;
766
767	if ($customer && $customer->isSubscribed($service->id, $checkin))
768	{
769	// subscribed customer, unset price
770	$price = 0;
771	}
772	}
773
774	// define default values
775	$reservation->service_gross = isset($reservation->service_gross) ? (float) $reservation->service_gross : 0;
776	$reservation->service_net = isset($reservation->service_net) ? (float) $reservation->service_net : 0;
777	$reservation->service_tax = isset($reservation->service_tax) ? (float) $reservation->service_tax : 0;
778	$reservation->total_cost = isset($reservation->total_cost) ? (float) $reservation->total_cost : 0;
779	$reservation->total_net = isset($reservation->total_net) ? (float) $reservation->total_net : 0;
780	$reservation->total_tax = isset($reservation->total_tax) ? (float) $reservation->total_tax : 0;
781
782	// subtract existing service totals (subtract at most a self unit to prevent negative values)
783	$reservation->total_cost -= min(array($reservation->service_gross, $reservation->total_cost));
784	$reservation->total_net -= min(array($reservation->service_net, $reservation->total_net));
785	$reservation->total_tax -= min(array($reservation->service_tax, $reservation->total_tax));
786
787	VAPLoader::import('libraries.tax.factory');
788
789	// prepare options for tax
790	$options = array();
791	$options['lang'] = isset($reservation->langtag) ? $reservation->langtag : null;
792	$options['subject'] = 'service';
793	$options['id_user'] = isset($reservation->id_user) ? (int) $reservation->id_user : 0;
794
795	// calculate taxes
796	$result = VAPTaxFactory::calculate($reservation->id_service, $price, $options);
797
798	// update totals with new calculated price
799	$reservation->service_price = $service_price;
800	$reservation->service_net = $result->net;
801	$reservation->service_tax = $result->tax;
802	$reservation->service_gross = $result->gross;
803	$reservation->tax_breakdown = json_encode($result->breakdown);
804
805	// sum new sub-totals to order totals
806	$reservation->total_cost += $reservation->service_gross;
807	$reservation->total_net += $reservation->service_net;
808	$reservation->total_tax += $reservation->service_tax;
809
810	if ($wasArray)
811	{
812	// back to array
813	$reservation = (array) $reservation;
814	}
815	}
816
817	/**
818	* Adds a discount to the specified reservation.
819	*
820	* @param integer $id The order ID.
821	* @param mixed $coupon Either a coupon code or an array/object
822	* containing its details.
823	*
824	* @return boolean True on success, false otherwise.
825	*/
826	public function addDiscount($id, $coupon)
827	{
828	// get coupon model
829	$couponModel = JModelVAP::getInstance('coupon');
830
831	if (is_string($coupon))
832	{
833	// get coupon code details
834	$coupon = $couponModel->getCoupon($coupon);
835	}
836	else
837	{
838	// treat as object
839	$coupon = (object) $coupon;
840	}
841
842	// make sure we have a valid coupon code
843	if (!$coupon \|\| !isset($coupon->value))
844	{
845	// invalid/missing coupon
846	$this->setError('Missing coupon code');
847
848	return false;
849	}
850
851	$dbo = JFactory::getDbo();
852
853	// load any children (options)
854	$q = $dbo->getQuery(true)
855	->select($dbo->qn(array('id', 'id_option', 'inc_price')))
856	->from($dbo->qn('#__vikappointments_res_opt_assoc'))
857	->where($dbo->qn('id_reservation') . ' = ' . (int) $id)
858	->where($dbo->qn('inc_price') . ' > 0');
859
860	$dbo->setQuery($q);
861	$items = $dbo->loadObjectList();
862
863	// load reservation details
864	$table = $this->getTable();
865	$table->load((int) $id);
866
867	// define options for tax calculation
868	$options = array(
869	'subject' => 'service',
870	'lang' => $table->langtag,
871	'id_user' => $table->id_user,
872	);
873
874	$total_c = 0;
875
876	// calculate total cost
877	foreach ($items as $item)
878	{
879	$total_c += (float) $item->inc_price;
880	}
881
882	// prepare order data
883	$orderData = array(
884	'id' => $table->id,
885	'total_cost' => $table->payment_charge + $table->payment_tax,
886	'total_net' => 0,
887	'total_tax' => $table->payment_tax,
888	'discount' => 0,
889	'coupon' => '',
890	'options' => array(),
891	);
892
893	VAPLoader::import('libraries.tax.factory');
894
895	if ($table->service_price > 0)
896	{
897	/**
898	* Multiply the service price by the number of selected attendees.
899	*
900	* @since 1.7.4 Do not multiply in case the service has the "Price per Person"
901	* setting turned off.
902	*/
903	if ((bool) JModelVAP::getInstance('service')->getItem($table->id_service, $blank = true)->priceperpeople)
904	{
905	$table->service_price *= $table->people;
906	}
907
908	// include service within the total number
909	// of items that can be discounted
910	$total_c += $table->service_price;
911
912	// recalculate service
913	$cost_with_disc = $table->service_price;
914
915	if (empty($coupon->percentot) \|\| $coupon->percentot == 1)
916	{
917	// percentage discount
918	$disc_val = round($cost_with_disc * $coupon->value / 100, 2);
919	}
920	else
921	{
922	// fixed discount, apply proportionally according to
923	// the total cost of all the items
924	$percentage = $cost_with_disc * 100 / $total_c;
925	$disc_val = round($coupon->value * $percentage / 100, 2);
926
927	// the discount cannot exceed the total price
928	$disc_val = min(array($table->service_price, $disc_val));
929	}
930
931	// save service discount
932	$orderData['service_discount'] = $disc_val;
933	// increase total discount
934	$orderData['discount'] += $orderData['service_discount'];
935
936	// subtract discount from service cost
937	$cost_with_disc -= $disc_val;
938
939	// recalculate totals
940	$totals = VAPTaxFactory::calculate($table->id_service, $cost_with_disc, $options);
941
942	// update service totals
943	$orderData['service_net'] = $totals->net;
944	$orderData['service_tax'] = $totals->tax;
945	$orderData['service_gross'] = $totals->gross;
946	$orderData['tax_breakdown'] = $totals->breakdown;
947
948	// update order totals
949	$orderData['total_net'] += $orderData['service_net'];
950	$orderData['total_tax'] += $orderData['service_tax'];
951	$orderData['total_cost'] += $orderData['service_gross'];
952	}
953
954	$options['subject'] = 'option';
955
956	// recalculate options
957	foreach ($items as $i => $item)
958	{
959	$cost_with_disc = $item->inc_price;
960
961	if (empty($coupon->percentot) \|\| $coupon->percentot == 1)
962	{
963	// percentage discount
964	$disc_val = round($cost_with_disc * $coupon->value / 100, 2);
965	}
966	else
967	{
968	if ($i < count($items) - 1)
969	{
970	// fixed discount, apply proportionally according to
971	// the total cost of all the items
972	$percentage = $cost_with_disc * 100 / $total_c;
973	$disc_val = round($coupon->value * $percentage / 100, 2);
974	}
975	else
976	{
977	// We are fetching the last element of the list, instead of calculating the
978	// proportional discount, we should subtract the total discount from the coupon
979	// value, in order to avoid rounding issues. Let's take as example a coupon of
980	// EUR 10 applied on 3 options. The final result would be 3.33 + 3.33 + 3.33,
981	// which won't match the initial discount value of the coupon. With this
982	// alternative way, the result would be: 10 - 3.33 - 3.33 = 3.34.
983	$disc_val = $coupon->value - $orderData['discount'];
984	}
985
986	// the discount cannot exceed the total price
987	$disc_val = min(array($item->inc_price, $disc_val));
988	}
989
990	// increase total discount
991	$orderData['discount'] += $disc_val;
992
993	// subtract discount from item cost
994	$cost_with_disc -= $disc_val;
995
996	// recalculate totals
997	$totals = VAPTaxFactory::calculate($item->id_option, $cost_with_disc, $options);
998
999	// prepare item to save
1000	$itemData = array(
1001	'id' => $item->id,
1002	'net' => $totals->net,
1003	'tax' => $totals->tax,
1004	'gross' => $totals->gross,
1005	'discount' => $disc_val,
1006	'tax_breakdown' => $totals->breakdown,
1007	);
1008
1009	// update order totals
1010	$orderData['total_net'] += $itemData['net'];
1011	$orderData['total_tax'] += $itemData['tax'];
1012	$orderData['total_cost'] += $itemData['gross'];
1013
1014	// append to options list
1015	$orderData['options'][] = $itemData;
1016	}
1017
1018	if (!empty($coupon->code))
1019	{
1020	// save coupon data
1021	$orderData['coupon'] = $coupon;
1022
1023	// redeem coupon usage
1024	$couponModel->redeem($coupon);
1025	}
1026
1027	// update order details
1028	return $this->save($orderData);
1029	}
1030
1031	/**
1032	* Removes discount from the specified reservation.
1033	*
1034	* @param integer $id The order ID.
1035	*
1036	* @return boolean True on success, false otherwise.
1037	*/
1038	public function removeDiscount($id)
1039	{
1040	$dbo = JFactory::getDbo();
1041
1042	// load any children
1043	$q = $dbo->getQuery(true)
1044	->select($dbo->qn(array('id', 'id_option', 'inc_price')))
1045	->from($dbo->qn('#__vikappointments_res_opt_assoc'))
1046	->where($dbo->qn('id_reservation') . ' = ' . (int) $id)
1047	->where($dbo->qn('inc_price') . ' > 0');
1048
1049	$dbo->setQuery($q);
1050	$items = $dbo->loadObjectList();
1051
1052	// load reservation details
1053	$table = $this->getTable();
1054	$table->load((int) $id);
1055
1056	if ($table->coupon_str)
1057	{
1058	// decode coupon string
1059	$coupon = explode(';;', $table->coupon_str);
1060
1061	// unredeem coupon usage
1062	JModelVAP::getInstance('coupon')->unredeem($coupon[0]);
1063	}
1064
1065	// define options for tax calculation
1066	$options = array(
1067	'subject' => 'service',
1068	'lang' => $table->langtag,
1069	'id_user' => $table->id_user,
1070	);
1071
1072	// prepare order data
1073	$orderData = array(
1074	'id' => $table->id,
1075	'total_cost' => $table->payment_charge + $table->payment_tax,
1076	'total_net' => 0,
1077	'total_tax' => $table->payment_tax,
1078	'discount' => 0,
1079	'coupon_str' => '',
1080	'options' => array(),
1081	);
1082
1083	VAPLoader::import('libraries.tax.factory');
1084
1085	if ($table->service_price > 0)
1086	{
1087	// multiply the service price by the number of selected attendees
1088	$table->service_price *= $table->people;
1089
1090	$cost_no_disc = $table->service_price;
1091
1092	// recalculate totals
1093	$totals = VAPTaxFactory::calculate($table->id_service, $cost_no_disc, $options);
1094
1095	// update service totals
1096	$orderData['service_net'] = $totals->net;
1097	$orderData['service_tax'] = $totals->tax;
1098	$orderData['service_gross'] = $totals->gross;
1099	$orderData['service_discount'] = 0;
1100	$orderData['tax_breakdown'] = $totals->breakdown;
1101
1102	// update order totals
1103	$orderData['total_net'] += $orderData['service_net'];
1104	$orderData['total_tax'] += $orderData['service_tax'];
1105	$orderData['total_cost'] += $orderData['service_gross'];
1106	}
1107
1108	$options['subject'] = 'option';
1109
1110	foreach ($items as $i => $item)
1111	{
1112	$cost_no_disc = $item->inc_price;
1113
1114	// recalculate totals
1115	$totals = VAPTaxFactory::calculate($item->id_option, $cost_no_disc, $options);
1116
1117	// prepare item to save
1118	$itemData = array(
1119	'id' => $item->id,
1120	'net' => $totals->net,
1121	'tax' => $totals->tax,
1122	'gross' => $totals->gross,
1123	'discount' => 0,
1124	'tax_breakdown' => $totals->breakdown,
1125	);
1126
1127	// update order totals
1128	$orderData['total_net'] += $itemData['net'];
1129	$orderData['total_tax'] += $itemData['tax'];
1130	$orderData['total_cost'] += $itemData['gross'];
1131
1132	// append to items list
1133	$orderData['options'][] = $itemData;
1134	}
1135
1136	// update order details
1137	return $this->save($orderData);
1138	}
1139
1140	/**
1141	* Sends an e-mail notification to the customer of the
1142	* specified reservation.
1143	*
1144	* @param integer $id The reservation ID.
1145	* @param array $options An array of options.
1146	*
1147	* @return boolean True on success, false otherwise.
1148	*/
1149	public function sendEmailNotification($id, array $options = array())
1150	{
1151	VAPLoader::import('libraries.mail.factory');
1152
1153	// fetch receiver alias
1154	$client = isset($options['client']) ? $options['client'] : 'customer';
1155
1156	try
1157	{
1158	// instantiate mail
1159	$mail = VAPMailFactory::getInstance($client, $id, $options);
1160	}
1161	catch (Exception $e)
1162	{
1163	// probably order not found, register error message
1164	$this->setError($e->getMessage());
1165
1166	return false;
1167	}
1168
1169	// in case the "check" attribute is set, we need to make
1170	// sure whether the specified client should receive the
1171	// e-mail according to the configuration rules
1172	if (!empty($options['check']) && !$mail->shouldSend())
1173	{
1174	// configured to avoid receiving this kind of e-mails
1175	return false;
1176	}
1177
1178	// send notification
1179	return $mail->send();
1180	}
1181
1182	/**
1183	* Sends a SMS notification to the customer of the
1184	* specified reservation.
1185	*
1186	* @param integer $id The reservation ID.
1187	*
1188	* @return boolean True on success, false otherwise.
1189	*/
1190	public function sendSmsNotification($id)
1191	{
1192	try
1193	{
1194	// get current SMS instance
1195	$smsapi = VAPApplication::getInstance()->getSmsInstance();
1196	}
1197	catch (Exception $e)
1198	{
1199	// SMS API not configured
1200	$this->setError(JText::translate('VAPSMSESTIMATEERR1'));
1201
1202	return false;
1203	}
1204
1205	VAPLoader::import('libraries.order.factory');
1206
1207	try
1208	{
1209	// load appointment details
1210	$order = VAPOrderFactory::getAppointments($id);
1211	}
1212	catch (Exception $e)
1213	{
1214	// order not found
1215	$this->setError($e->getMessage());
1216
1217	return false;
1218	}
1219
1220	// make sure we have a phone number
1221	if (!$order->purchaser_phone)
1222	{
1223	// register error
1224	$this->setError('Missing phone number.');
1225
1226	return false;
1227	}
1228
1229	// make sure the phone number reports a dial code
1230	if ($order->purchaser_prefix && !preg_match("/^\+/", $order->purchaser_phone))
1231	{
1232	// nope, add the specified one (backward compatibility)
1233	$order->purchaser_phone = $order->purchaser_prefix . $order->purchaser_phone;
1234	}
1235
1236	// fetch sms message
1237	$text = VikAppointments::getSmsCustomerTextMessage($order);
1238
1239	// send message
1240	$response = $smsapi->sendMessage($order->purchaser_phone, $text);
1241
1242	// validate response
1243	if (!$smsapi->validateResponse($response))
1244	{
1245	// unable to send the notification, register error message
1246	$log = $smsapi->getLog();
1247
1248	if ($log)
1249	{
1250	$this->setError($log);
1251	}
1252
1253	return false;
1254	}
1255
1256	return true;
1257	}
1258
1259	/**
1260	* Returns a list of available times for the specified data.
1261	*
1262	* @param array $data An array of search data.
1263	*
1264	* @return mixed An array of available times. False in case of error.
1265	*/
1266	public function getAvailableTimes($data)
1267	{
1268	// prepare search options
1269	$options = array();
1270	$options['people'] = !empty($data['people']) ? (int) $data['people'] : 1;
1271	$options['id_res'] = !empty($data['id']) ? (int) $data['id'] : 0;
1272
1273	// number of people cannot be lower than 1
1274	$options['people'] = max(array(1, (int) $options['people']));
1275
1276	if (JFactory::getApplication()->isClient('administrator') \|\| (!empty($data['validate_availability']) && $data['validate_availability'] == 'admin'))
1277	{
1278	// grant administrator access
1279	$options['admin'] = true;
1280	}
1281
1282	VAPLoader::import('libraries.availability.manager');
1283	// create availability search instance
1284	$search = VAPAvailabilityManager::getInstance($data['id_service'], $data['id_employee'], $options);
1285
1286	try
1287	{
1288	// create timeline parser instance
1289	VAPLoader::import('libraries.availability.timeline.factory');
1290	$parser = VAPAvailabilityTimelineFactory::getParser($search);
1291	}
1292	catch (Exception $e)
1293	{
1294	// register exception as error
1295	$this->setError($e);
1296
1297	return false;
1298	}
1299
1300	// get employee timezone
1301	$tz = JModelVAP::getInstance('employee')->getTimezone($search->get('id_employee'));
1302
1303	// create check-in date and adjust it to the employee timezone
1304	$checkin = JDate::getInstance($data['checkin_ts']);
1305	$checkin->setTimezone(new DateTimeZone($tz));
1306
1307	// elaborate timeline
1308	$timeline = $parser->getTimeline($checkin->format('Y-m-d', true), $options['people'], $options['id_res']);
1309
1310	if (!$timeline)
1311	{
1312	// propagate error message
1313	$this->setError($parser->getError());
1314
1315	return false;
1316	}
1317
1318	return $timeline;
1319	}
1320
1321	/**
1322	* Checks the availability within the system according to the specified
1323	* search details.
1324	*
1325	* @param array $data An array of search data.
1326	*
1327	* @return mixed True if available, false otherwise. In case the employee
1328	* was not specified, the ID of the available employee will
1329	* be returned instead.
1330	*/
1331	public function isAvailable($data)
1332	{
1333	// get availability timeline
1334	$timeline = $this->getAvailableTimes($data);
1335
1336	if (!$timeline)
1337	{
1338	// not available for the current day
1339	return false;
1340	}
1341
1342	// convert times into an array
1343	$times = $timeline->toArray($flatten = true);
1344
1345	// get employee timezone
1346	$tz = JModelVAP::getInstance('employee')->getTimezone($timeline->getSearch()->get('id_employee'));
1347
1348	// create check-in date and adjust it to the employee timezone
1349	$checkin = JDate::getInstance($data['checkin_ts']);
1350	$checkin->setTimezone(new DateTimeZone($tz));
1351
1352	// extract time
1353	$hm = $checkin->format('H:i', $local = true);
1354	// convert time to minutes
1355	$hm = JHtml::fetch('vikappointments.time2min', $hm);
1356
1357	// make sure the time is available
1358	if (!isset($times[$hm]) \|\| $times[$hm] != 1)
1359	{
1360	// the time is not available/supported
1361	$this->setError(JText::translate('VAPRESDATETIMENOTAVERR'));
1362
1363	return false;
1364	}
1365
1366	$options = array();
1367
1368	if (isset($data['exclude_employees']))
1369	{
1370	// check if we should exclude certain employees from the availability check
1371	$options['exclude_employees'] = $data['exclude_employees'];
1372	}
1373
1374	// create availability search instance
1375	$search = VAPAvailabilityManager::getInstance($data['id_service'], $data['id_employee'], $options);
1376
1377	$duration = 0;
1378
1379	if (!empty($data['duration']))
1380	{
1381	$duration += $data['duration'];
1382
1383	if (!empty($data['sleep']))
1384	{
1385	$duration += $data['sleep'];
1386	}
1387	}
1388
1389	// fetch number of participants
1390	$people = !empty($data['people']) ? max(array(1, (int) $data['people'])) : 1;
1391	// check if we are editing a reservation
1392	$id = !empty($data['id']) ? (int) $data['id'] : 0;
1393
1394	// exmployee was specified, validate its availability
1395	if ($data['id_employee'] > 0)
1396	{
1397	// check if the employee is able to host the appointment
1398	$is = $search->isEmployeeAvailable($data['checkin_ts'], $duration, $people, $id);
1399	}
1400	else
1401	{
1402	// Employee not specified, we need to load all the employees assigned
1403	// to this service and take the first available one. In case of available
1404	// employee, its ID will be returned here.
1405	$is = $search->isServiceAvailable($data['checkin_ts'], $duration, $people, $id);
1406	}
1407
1408	if (!$is)
1409	{
1410	// the time is not available/supported
1411	$this->setError(JText::translate('VAPRESDATETIMENOTAVERR'));
1412
1413	return false;
1414	}
1415
1416	// employee available (true or its ID)
1417	return $is;
1418	}
1419
1420	/**
1421	* Updates the status of all the appointments out of time to REMOVED.
1422	* This method is used to free the slots occupied by pending orders
1423	* that haven't been confirmed within the specified range of time.
1424	*
1425	* Affects only the reservations that match the specified employee ID.
1426	*
1427	* @param array $options An array of options to filter the records.
1428	*
1429	* @return void
1430	*/
1431	public function checkExpired(array $options = array())
1432	{
1433	$dbo = JFactory::getDbo();
1434
1435	// get any pending codes
1436	$pending = JHtml::fetch('vaphtml.status.find', 'code', array('appointments' => 1, 'reserved' => 1, 'approved' => 0));
1437
1438	/**
1439	* Do not proceed in case the PENDING status is not available, otherwise we would end to
1440	* auto-remove all the existing reservations.
1441	*
1442	* @since 1.7.7
1443	*/
1444	if (!$pending)
1445	{
1446	// pending status not found, abort immediately
1447	return;
1448	}
1449
1450	// select all the expired appointments
1451	$q = $dbo->getQuery(true);
1452	$q->select($dbo->qn('id'));
1453	$q->from($dbo->qn('#__vikappointments_reservation'));
1454
1455	// filter by pending status
1456	$q->where($dbo->qn('status') . ' IN (' . implode(',', array_map(array($dbo, 'q'), $pending)) . ')');
1457	// take the expired appointments
1458	$q->where($dbo->qn('locked_until') . ' < ' . time());
1459
1460	if (!empty($options['id_service']))
1461	{
1462	// get service model
1463	$serviceModel = JModelVAP::getInstance('service');
1464
1465	// check if we have a service with private calendar
1466	if ($serviceModel->hasOwnCalendar($options['id_service']))
1467	{
1468	// we can directly filter the reservations by service, because the
1469	// availability is related to the appointments assigned to the latter
1470	$q->where($dbo->qn('id_service') . ' = ' . (int) $options['id_service']);
1471	// unset employees filter
1472	$options['id_employee'] = 0;
1473	}
1474	else
1475	{
1476	/**
1477	* Service set, recover all the employees assigned to this service
1478	* in order to properly refresh the availability. It is not enough
1479	* to remove only the appointments assigned to the specified service,
1480	* because a time-slot of the employees might have been locked by
1481	* an appointment assigned to a different service.
1482	*
1483	* @since 1.7
1484	*/
1485	$employees = $dbo->getQuery(true)
1486	->select($dbo->qn('id_employee'))
1487	->from($dbo->qn('#__vikappointments_ser_emp_assoc'))
1488	->where($dbo->qn('id_service') . ' = ' . (int) $options['id_service']);
1489
1490	$dbo->setQuery($employees);
1491
1492	// filter by the specified employees
1493	$options['employees'] = $dbo->loadColumn();
1494	}
1495	}
1496
1497	if (!empty($options['id_employee']))
1498	{
1499	// affects only the reservations that match the specified employee ID
1500	if (is_array($options['id_employee']))
1501	{
1502	// sanitize employees array
1503	$ids = implode(',', array_map('intval', $options['id_employee']));
1504	$q->where($dbo->qn('id_employee') . ' IN (' . $ids . ')');
1505	}
1506	else
1507	{
1508	$q->where($dbo->qn('id_employee') . ' = ' . (int) $options['id_employee']);
1509	}
1510	}
1511
1512	if (!empty($options['id']))
1513	{
1514	// take only the specified reservation
1515	$q->andWhere(array(
1516	$dbo->qn('id') . ' = ' . (int) $options['id'],
1517	$dbo->qn('id_parent') . ' = ' . (int) $options['id'],
1518	), 'OR');
1519	}
1520
1521	$dbo->setQuery($q);
1522	$rows = $dbo->loadColumn();
1523
1524	$handler = VAPOrderStatus::getInstance();
1525
1526	foreach ($rows as $id)
1527	{
1528	// Remove and track the status change (REMOVED).
1529	// Do not use the model to save the status change
1530	// to speed up the whole process. It is still possible
1531	// to track the status change by using the hook
1532	// provided by VAPOrderStatus class.
1533	$handler->remove($id, 'VAP_STATUS_ORDER_REMOVED');
1534	}
1535	}
1536
1537	/**
1538	* Counts the actual number of options already sold.
1539	*
1540	* @param int $idOption The option to count.
1541	* @param int $idVariation The variation to count, if any.
1542	* @param int $idIndex The reservation item to exlcude, if any.
1543	*
1544	* @return int The number of sold units.
1545	*
1546	* @since 1.7.7
1547	*/
1548	public function countSoldOptions($idOption, $idVariation = null, $idIndex = null)
1549	{
1550	$db = JFactory::getDbo();
1551
1552	$query = $db->getQuery(true)
1553	->select('SUM(i.quantity)')
1554	->from($db->qn('#__vikappointments_reservation', 'r'))
1555	->innerjoin($db->qn('#__vikappointments_res_opt_assoc', 'i') . ' ON ' . $db->qn('i.id_reservation') . ' = ' . $db->qn('r.id'));
1556
1557	if ($idIndex)
1558	{
1559	// exclude the specified reservation item
1560	$query->where($db->qn('i.id') . ' <> ' . (int) $idIndex);
1561	}
1562
1563	// filter by option
1564	$query->where($db->qn('i.id_option') . ' = ' . (int) $idOption);
1565
1566	// get any reserved status codes
1567	$reserved = JHtml::fetch('vaphtml.status.find', 'code', ['appointments' => 1, 'reserved' => 1]);
1568
1569	if ($reserved)
1570	{
1571	// filter by reserved status
1572	$query->where($db->qn('r.status') . ' IN (' . implode(',', array_map(array($db, 'q'), $reserved)) . ')');
1573	}
1574
1575	if ($idVariation)
1576	{
1577	// take only the specified variation
1578	$query->where($db->qn('i.id_variation') . ' = ' . (int) $idVariation);
1579	}
1580	else
1581	{
1582	// take all the variations of the option without self stock
1583	$query->leftjoin($db->qn('#__vikappointments_option_value', 'v') . ' ON ' . $db->qn('i.id_variation') . ' = ' . $db->qn('v.id'));
1584	$query->andWhere([
1585	$db->qn('v.id') . ' IS NULL',
1586	$db->qn('v.stock') . ' = 0',
1587	], 'OR');
1588	}
1589
1590	$db->setQuery($query, 0, 1);
1591	return (int) $db->loadResult();
1592	}
1593	}
1594