vikbooking / admin / helpers / report / revenue.php

revenue.php in VikBooking Hotel Booking Engine & PMS trunk, at admin/helpers/report/revenue.php

919 lines 27.1 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	* Revenue child Class of VikBookingReport
15	*/
16	class VikBookingReportRevenue extends VikBookingReport
17	{
18	/**
19	* Property 'defaultKeySort' is used by the View that renders the report.
20	*/
21	public $defaultKeySort = 'day';
22
23	/**
24	* Property 'defaultKeyOrder' is used by the View that renders the report.
25	*/
26	public $defaultKeyOrder = 'ASC';
27
28	/**
29	* Property 'exportAllowed' is used by the View to display the export button.
30	*/
31	public $exportAllowed = 1;
32
33	/**
34	* Debug mode is activated by passing the value 'e4j_debug' > 0
35	*/
36	private $debug;
37
38	/**
39	* Class constructor should define the name of the report and
40	* other vars. Call the parent constructor to define the DB object.
41	*/
42	public function __construct()
43	{
44	$this->reportFile = basename(__FILE__, '.php');
45	$this->reportName = JText::translate('VBOREPORT'.strtoupper(str_replace('_', '', $this->reportFile)));
46	$this->reportFilters = [];
47
48	$this->cols = [];
49	$this->rows = [];
50	$this->footerRow = [];
51
52	$this->debug = (VikRequest::getInt('e4j_debug', 0, 'request') > 0);
53
54	$this->registerExportCSVFileName();
55
56	parent::__construct();
57	}
58
59	/**
60	* Returns the name of this report.
61	*
62	* @return string
63	*/
64	public function getName()
65	{
66	return $this->reportName;
67	}
68
69	/**
70	* Returns the name of this file without .php.
71	*
72	* @return string
73	*/
74	public function getFileName()
75	{
76	return $this->reportFile;
77	}
78
79	/**
80	* Returns the filters of this report.
81	*
82	* @return array
83	*/
84	public function getFilters()
85	{
86	if (count($this->reportFilters)) {
87	//do not run this method twice, as it could load JS and CSS files.
88	return $this->reportFilters;
89	}
90
91	//get VBO Application Object
92	$vbo_app = VikBooking::getVboApplication();
93
94	//load the jQuery UI Datepicker
95	$this->loadDatePicker();
96
97	//From Date Filter
98	$filter_opt = array(
99	'label' => '<label for="fromdate">'.JText::translate('VBOREPORTSDATEFROM').'</label>',
100	'html' => '<input type="text" id="fromdate" name="fromdate" value="" class="vbo-report-datepicker vbo-report-datepicker-from" />',
101	'type' => 'calendar',
102	'name' => 'fromdate'
103	);
104	array_push($this->reportFilters, $filter_opt);
105
106	//To Date Filter
107	$filter_opt = array(
108	'label' => '<label for="todate">'.JText::translate('VBOREPORTSDATETO').'</label>',
109	'html' => '<input type="text" id="todate" name="todate" value="" class="vbo-report-datepicker vbo-report-datepicker-to" />',
110	'type' => 'calendar',
111	'name' => 'todate'
112	);
113	array_push($this->reportFilters, $filter_opt);
114
115	// Listings Filter
116	$filter_opt = array(
117	'label' => '<label for="listingsfilt">' . JText::translate('VBO_LISTINGS') . '</label>',
118	'html' => '<span class="vbo-toolbar-multiselect-wrap">' . $vbo_app->renderElementsDropDown([
119	'id' => 'listingsfilt',
120	'elements' => 'listings',
121	'placeholder' => JText::translate('VBO_LISTINGS'),
122	'allow_clear' => 1,
123	'attributes' => [
124	'name' => 'listings[]',
125	'multiple' => 'multiple',
126	],
127	'selected_values' => (array) JFactory::getApplication()->input->get('listings', [], 'array'),
128	]) . '</span>',
129	'type' => 'select',
130	'multiple' => true,
131	'name' => 'listings',
132	);
133	array_push($this->reportFilters, $filter_opt);
134
135	// channel filter
136	$all_channels = [];
137	$pchannel = VikRequest::getString('channel', '', 'request');
138	$q = "SELECT `channel` FROM `#__vikbooking_orders` WHERE `channel` IS NOT NULL GROUP BY `channel`;";
139	$this->dbo->setQuery($q);
140	$this->dbo->execute();
141	if ($this->dbo->getNumRows()) {
142	$ord_channels = $this->dbo->loadAssocList();
143	// push website as first option
144	$all_channels['-1'] = JText::translate('VBORDFROMSITE');
145	// push all channel names
146	foreach ($ord_channels as $o_channel) {
147	$channel_parts = explode('_', $o_channel['channel']);
148	$channel_name = count($channel_parts) > 1 ? trim($channel_parts[1]) : trim($channel_parts[0]);
149	if (isset($all_channels[$channel_name])) {
150	continue;
151	}
152	$all_channels[$channel_name] = $channel_name;
153	}
154	// push filter
155	$channels_sel_html = $vbo_app->getNiceSelect($all_channels, $pchannel, 'channel', '- - - -', '- - - -', '', '', 'channel');
156	$filter_opt = array(
157	'label' => '<label for="channel">'.JText::translate('VBCHANNELFILTER').'</label>',
158	'html' => $channels_sel_html,
159	'type' => 'select',
160	'name' => 'channel'
161	);
162	array_push($this->reportFilters, $filter_opt);
163	}
164
165	// get minimum check-in and maximum check-out for dates filters
166	$df = $this->getDateFormat();
167	$mincheckin = 0;
168	$maxcheckout = 0;
169	$q = "SELECT MIN(`checkin`) AS `mincheckin`, MAX(`checkout`) AS `maxcheckout` FROM `#__vikbooking_orders` WHERE `status`='confirmed' AND `closure`=0;";
170	$this->dbo->setQuery($q);
171	$this->dbo->execute();
172	if ($this->dbo->getNumRows()) {
173	$data = $this->dbo->loadAssoc();
174	if (!empty($data['mincheckin']) && !empty($data['maxcheckout'])) {
175	$mincheckin = $data['mincheckin'];
176	$maxcheckout = $data['maxcheckout'];
177	}
178	}
179	//
180
181	//jQuery code for the datepicker calendars and select2
182	$pfromdate = VikRequest::getString('fromdate', '', 'request');
183	$ptodate = VikRequest::getString('todate', '', 'request');
184	$js = 'jQuery(function() {
185	jQuery(".vbo-report-datepicker:input").datepicker({
186	'.(!empty($mincheckin) ? 'minDate: "'.date($df, $mincheckin).'", ' : '').'
187	'.(!empty($maxcheckout) ? 'maxDate: "'.date($df, $maxcheckout).'", ' : '').'
188	'.(!empty($mincheckin) && !empty($maxcheckout) ? 'yearRange: "'.(date('Y', $mincheckin)).':'.date('Y', $maxcheckout).'", changeMonth: true, changeYear: true, ' : '').'
189	dateFormat: "'.$this->getDateFormat('jui').'",
190	onSelect: vboReportCheckDates
191	});
192	'.(!empty($pfromdate) ? 'jQuery(".vbo-report-datepicker-from").datepicker("setDate", "'.$pfromdate.'");' : '').'
193	'.(!empty($ptodate) ? 'jQuery(".vbo-report-datepicker-to").datepicker("setDate", "'.$ptodate.'");' : '').'
194	});
195	function vboReportCheckDates(selectedDate, inst) {
196	if (selectedDate === null \|\| inst === null) {
197	return;
198	}
199	var cur_from_date = jQuery(this).val();
200	if (jQuery(this).hasClass("vbo-report-datepicker-from") && cur_from_date.length) {
201	var nowstart = jQuery(this).datepicker("getDate");
202	var nowstartdate = new Date(nowstart.getTime());
203	jQuery(".vbo-report-datepicker-to").datepicker("option", {minDate: nowstartdate});
204	}
205	}';
206	$this->setScript($js);
207
208	return $this->reportFilters;
209	}
210
211	/**
212	* Loads the report data from the DB.
213	* Returns true in case of success, false otherwise.
214	* Sets the columns and rows for the report to be displayed.
215	*
216	* @return boolean
217	*/
218	public function getReportData()
219	{
220	if ($this->getError()) {
221	// export functions may set errors rather than exiting the process, and the View may continue the execution to attempt to render the report.
222	return false;
223	}
224
225	// input fields and other vars
226	$pfromdate = VikRequest::getString('fromdate', '', 'request');
227	$ptodate = VikRequest::getString('todate', '', 'request');
228	$pidroom = VikRequest::getInt('idroom', '', 'request');
229	$plistings = (array) VikRequest::getVar('listings', array());
230	$pchannel = VikRequest::getString('channel', '', 'request');
231	$pkrsort = VikRequest::getString('krsort', $this->defaultKeySort, 'request');
232	$pkrsort = empty($pkrsort) ? $this->defaultKeySort : $pkrsort;
233	$pkrorder = VikRequest::getString('krorder', $this->defaultKeyOrder, 'request');
234	$pkrorder = empty($pkrorder) ? $this->defaultKeyOrder : $pkrorder;
235	$pkrorder = $pkrorder == 'DESC' ? 'DESC' : 'ASC';
236	$currency_symb = VikBooking::getCurrencySymb();
237	$df = $this->getDateFormat();
238	$datesep = VikBooking::getDateSeparator();
239	if (empty($ptodate)) {
240	$ptodate = $pfromdate;
241	}
242
243	// get dates timestamps
244	$from_ts = VikBooking::getDateTimestamp($pfromdate, 0, 0);
245	$to_ts = VikBooking::getDateTimestamp($ptodate, 23, 59, 59);
246	if (empty($pfromdate) \|\| empty($from_ts) \|\| empty($to_ts)) {
247	$this->setError(JText::translate('VBOREPORTSERRNODATES'));
248	return false;
249	}
250
251	// query to obtain the records
252	$q = $this->dbo->getQuery(true)
253	->select([
254	$this->dbo->qn('o.id'),
255	$this->dbo->qn('o.custdata'),
256	$this->dbo->qn('o.ts'),
257	$this->dbo->qn('o.status'),
258	$this->dbo->qn('o.days'),
259	$this->dbo->qn('o.checkin'),
260	$this->dbo->qn('o.checkout'),
261	$this->dbo->qn('o.totpaid'),
262	$this->dbo->qn('o.coupon'),
263	$this->dbo->qn('o.roomsnum'),
264	$this->dbo->qn('o.total'),
265	$this->dbo->qn('o.idorderota'),
266	$this->dbo->qn('o.channel'),
267	$this->dbo->qn('o.country'),
268	$this->dbo->qn('o.tot_taxes'),
269	$this->dbo->qn('o.tot_city_taxes'),
270	$this->dbo->qn('o.tot_fees'),
271	$this->dbo->qn('o.tot_damage_dep'),
272	$this->dbo->qn('o.cmms'),
273	$this->dbo->qn('o.refund'),
274	$this->dbo->qn('o.canc_fee'),
275	$this->dbo->qn('or.idorder'),
276	$this->dbo->qn('or.idroom'),
277	$this->dbo->qn('or.optionals'),
278	$this->dbo->qn('or.cust_cost'),
279	$this->dbo->qn('or.cust_idiva'),
280	$this->dbo->qn('or.extracosts'),
281	$this->dbo->qn('or.room_cost'),
282	])
283	->from($this->dbo->qn('#__vikbooking_orders', 'o'))
284	->leftJoin($this->dbo->qn('#__vikbooking_ordersrooms', 'or') . ' ON ' . $this->dbo->qn('or.idorder') . ' = ' . $this->dbo->qn('o.id'))
285	->where('(' . $this->dbo->qn('o.status') . ' = ' . $this->dbo->q('confirmed') . ' OR (' . $this->dbo->qn('o.status') . ' = ' . $this->dbo->q('cancelled') . ' AND ' . $this->dbo->qn('o.canc_fee') . ' > 0))')
286	->where($this->dbo->qn('o.closure') . ' = 0')
287	->where($this->dbo->qn('o.checkout') . ' >= ' . $from_ts)
288	->where($this->dbo->qn('o.checkin') . ' <= ' . $to_ts)
289	->order($this->dbo->qn('o.checkin') . ' ASC')
290	->order($this->dbo->qn('o.id') . ' ASC')
291	->order($this->dbo->qn('or.id') . ' ASC');
292
293	/**
294	* Do not filter the room booking records by room ID, but always join them, to improve accuracy with calculations.
295	*
296	* @since 1.18.0 (J) - 1.8.0 (WP)
297	*/
298	if (!empty($pidroom)) {
299	// keep supporting the old single-listing filter, no longer used
300	// $q->where($this->dbo->qn('or.idroom') . ' = ' . (int) $pidroom);
301	} elseif ($plistings) {
302	// new multi-listing filter
303	// $q->where($this->dbo->qn('or.idroom') . ' IN (' . implode(', ', array_map('intval', $plistings)) . ')');
304	}
305
306	if (strlen($pchannel)) {
307	// filter by channel
308	if ($pchannel == -1) {
309	$q->where($this->dbo->qn('o.channel') . ' IS NULL');
310	} else {
311	$q->where($this->dbo->qn('o.channel') . ' LIKE ' . $this->dbo->q('%' . $pchannel . '%'));
312	}
313	}
314
315	$this->dbo->setQuery($q);
316	$records = $this->dbo->loadAssocList();
317
318	if (!$records) {
319	$this->setError(JText::translate('VBOREPORTSERRNORESERV'));
320	return false;
321	}
322
323	// nest records with multiple rooms booked inside sub-array
324	$bookings = [];
325	foreach ($records as $v) {
326	if (!isset($bookings[$v['id']])) {
327	$bookings[$v['id']] = [];
328	}
329
330	// calculate the from_ts and to_ts values for later comparison
331	$in_info = getdate($v['checkin']);
332	$out_info = getdate($v['checkout']);
333	$v['from_ts'] = mktime(0, 0, 0, $in_info['mon'], $in_info['mday'], $in_info['year']);
334	$v['to_ts'] = mktime(23, 59, 59, $out_info['mon'], ($out_info['mday'] - 1), $out_info['year']);
335
336	if ($v['status'] == 'cancelled' && count($bookings[$v['id']])) {
337	// one room is sufficient for a cancelled booking with cancellation fees
338	continue;
339	}
340
341	// push nested room-booking
342	array_push($bookings[$v['id']], $v);
343	}
344
345	unset($records);
346
347	// define the columns of the report
348	$this->cols = array(
349	//date
350	array(
351	'key' => 'day',
352	'sortable' => 1,
353	'label' => JText::translate('VBOREPORTREVENUEDAY')
354	),
355	//rooms sold
356	array(
357	'key' => 'rooms_sold',
358	'attr' => array(
359	'class="center"'
360	),
361	'sortable' => 1,
362	'label' => JText::translate('VBOREPORTREVENUERSOLD')
363	),
364	//total bookings
365	array(
366	'key' => 'tot_bookings',
367	'attr' => array(
368	'class="center"'
369	),
370	'sortable' => 1,
371	'label' => JText::translate('VBOREPORTREVENUETOTB')
372	),
373	//% occupancy
374	array(
375	'key' => 'occupancy',
376	'attr' => array(
377	'class="center"'
378	),
379	'sortable' => 1,
380	'label' => JText::translate('VBOREPORTREVENUEPOCC')
381	),
382	//IBE revenue
383	array(
384	'key' => 'ibe_revenue',
385	'attr' => array(
386	'class="center"'
387	),
388	'sortable' => 1,
389	'label' => JText::translate('VBOREPORTREVENUEREVWEB')
390	),
391	//OTAs revenue
392	array(
393	'key' => 'ota_revenue',
394	'attr' => array(
395	'class="center"'
396	),
397	'sortable' => 1,
398	'label' => JText::translate('VBOREPORTREVENUEREVOTA')
399	),
400	// Refunds
401	array(
402	'key' => 'refunds',
403	'attr' => array(
404	'class="center"'
405	),
406	'sortable' => 1,
407	'label' => JText::translate('VBO_TOT_REFUNDS')
408	),
409	//ADR
410	array(
411	'key' => 'adr',
412	'attr' => array(
413	'class="center"'
414	),
415	'sortable' => 1,
416	'label' => JText::translate('VBOREPORTREVENUEADR'),
417	'tip' => JText::translate('VBOREPORTREVENUEADRHELP')
418	),
419	//RevPAR
420	array(
421	'key' => 'revpar',
422	'attr' => array(
423	'class="center"'
424	),
425	'sortable' => 1,
426	'label' => JText::translate('VBOREPORTREVENUEREVPAR'),
427	'tip' => JText::translate('VBOREPORTREVENUEREVPARH')
428	),
429	//Taxes
430	array(
431	'key' => 'taxes',
432	'attr' => array(
433	'class="center"'
434	),
435	'sortable' => 1,
436	'label' => JText::translate('VBOREPORTREVENUETAX')
437	),
438	// Damage deposit
439	array(
440	'key' => 'damagedep',
441	'attr' => array(
442	'class="center"'
443	),
444	'sortable' => 1,
445	'label' => JText::translate('VBO_DAMAGE_DEPOSIT')
446	),
447	// Commissions
448	array(
449	'key' => 'cmms',
450	'attr' => array(
451	'class="center"'
452	),
453	'sortable' => 1,
454	'label' => JText::translate('VBTOTALCOMMISSIONS')
455	),
456	// Cancellation fees
457	array(
458	'key' => 'canc_fees',
459	'attr' => array(
460	'class="center"'
461	),
462	'sortable' => 1,
463	'label' => JText::translate('VBO_CANC_FEE')
464	),
465	//Revenue
466	array(
467	'key' => 'revenue',
468	'attr' => array(
469	'class="center"'
470	),
471	'sortable' => 1,
472	'label' => JText::translate('VBOREPORTREVENUEREV')
473	)
474	);
475
476	$total_rooms_units = $this->countRooms($pidroom ?: $plistings) ?: 1;
477
478	// loop over the dates of the report to build the rows
479	$from_info = getdate($from_ts);
480	$to_info = getdate($to_ts);
481	$canc_fee_bookings = [];
482	while ($from_info[0] <= $to_info[0]) {
483	// prepare default fields for this row
484	$day_ts = $from_info[0];
485	$curwday = $this->getWdayString($from_info['wday'], 'short');
486	$rooms_sold = 0;
487	$tot_bookings = 0;
488	$occupancy = 0;
489	$ibe_revenue = 0;
490	$ota_revenue = 0;
491	$tot_refunds = 0;
492	$adr = 0;
493	$revpar = 0;
494	$taxes = 0;
495	$tot_damage_deps = 0;
496	$cmms = 0;
497	$canc_fees = 0;
498	$revenue = 0;
499
500	// calculate the report details for this day
501	foreach ($bookings as $gbook) {
502	// skip already parsed cancelled bookings with cancellation fees
503	if (in_array($gbook[0]['id'], $canc_fee_bookings)) {
504	continue;
505	}
506
507	// check if booking affects the current day
508	if ($from_info[0] >= $gbook[0]['from_ts'] && $from_info[0] <= $gbook[0]['to_ts']) {
509	// immediately check for cancelled bookings with cancellation fees
510	if ($gbook[0]['status'] == 'cancelled') {
511	// increase value
512	$canc_fees += $gbook[0]['canc_fee'];
513	// record this booking as already counted
514	$canc_fee_bookings[] = $gbook[0]['id'];
515	continue;
516	}
517
518	// this booking affects the current day
519	$tot_bookings++;
520
521	// get the optionally filtered rooms (single listing no longer used, or multiple listings)
522	$filter_rooms = (array) ($pidroom ?: $plistings);
523
524	// count the rooms booked in case of rooms filtering
525	$booking_rooms_aff = 0;
526	if ($filter_rooms) {
527	foreach ($gbook as $sgbook) {
528	if (in_array($sgbook['idroom'], $filter_rooms)) {
529	$rooms_sold++;
530	$booking_rooms_aff++;
531	}
532	}
533	} else {
534	$rooms_sold += $gbook[0]['roomsnum'];
535	}
536
537	// determine the base total amount ONLY in case of rooms filtering
538	$room_base_total = 0;
539	if ($filter_rooms && $rooms_sold > 0) {
540	foreach ($gbook as $room_book) {
541	if (!in_array($room_book['idroom'], $filter_rooms)) {
542	continue;
543	}
544	if ($room_book['cust_cost'] > 0) {
545	$room_base_total += $room_book['cust_cost'];
546	} elseif ($room_book['room_cost'] > 0) {
547	$room_base_total += $room_book['room_cost'];
548	}
549	}
550	}
551
552	// check if a (coupon) discount was applied
553	$discount = 0;
554	if (!empty($gbook[0]['coupon'])) {
555	$coupon_parts = explode(';', $gbook[0]['coupon']);
556	$discount = $coupon_parts[1] ?? $coupon_parts[0];
557	$discount = (float) preg_replace('/[^0-9\.]/', '', $discount);
558	}
559
560	// calculate net revenue and taxes
561	$tot_net = $gbook[0]['total'] - (float) $gbook[0]['tot_taxes'] - (float) $gbook[0]['tot_city_taxes'] - (float) $gbook[0]['tot_fees'] - (float) $gbook[0]['tot_damage_dep'] - (float) $gbook[0]['cmms'];
562
563	if ($filter_rooms && !$booking_rooms_aff) {
564	// no revenue involved
565	$tot_net = 0;
566	}
567
568	if ($room_base_total && (!((float) $gbook[0]['tot_taxes']) \|\| !VikBooking::ivaInclusa())) {
569	/**
570	* Overwrite total net value in case of rooms filtering only in case of no taxes, or in
571	* case of prices before taxes, because the amount of tax per room is not available.
572	*
573	* @since 1.16.9 (J) - 1.6.9 (WP)
574	* @since 1.18.0 (J) - 1.8.0 (WP) when relying on the room costs, ensure to deduct the discount.
575	*/
576	$tot_net = $room_base_total - ($discount / max(1, count(array_intersect(array_column($gbook, 'idroom'), $filter_rooms))));
577	} elseif ($room_base_total && $gbook[0]['roomsnum'] > 1 && array_diff(array_column($gbook, 'idroom'), $filter_rooms)) {
578	/**
579	* Overwrite total net value in case of rooms filtering, multi-room booking and booking rooms different than filters.
580	* The amount of tax per room is not available, hence the tax calculation may not be accurate in these cases.
581	*
582	* @since 1.18.0 (J) - 1.8.0 (WP)
583	*/
584	$tot_net = $room_base_total - ($discount / max(1, count(array_intersect(array_column($gbook, 'idroom'), $filter_rooms))));
585	}
586
587	// ensure we do not have negative values
588	$tot_net = $tot_net > 0 ? $tot_net : 0;
589
590	$tot_net = $tot_net / (int) $gbook[0]['days'];
591	$revenue += $tot_net;
592
593	if (!empty($gbook[0]['idorderota']) && !empty($gbook[0]['channel'])) {
594	$ota_revenue += $tot_net;
595	} else {
596	$ibe_revenue += $tot_net;
597	}
598
599	$taxes += ((float) $gbook[0]['tot_taxes'] + (float) $gbook[0]['tot_city_taxes'] + (float) $gbook[0]['tot_fees']) / (int) $gbook[0]['days'];
600	$cmms += (float) $gbook[0]['cmms'] / (int) $gbook[0]['days'];
601	$tot_refunds += $gbook[0]['refund'] / $gbook[0]['days'];
602
603	// attempt to calculate the damage deposit for the current day and room
604	$room_day_damage_dep = (float) $gbook[0]['tot_damage_dep'] / (int) $gbook[0]['days'];
605	if ($room_base_total) {
606	// when filters applied, calculate the amount of damage deposit per room for this day
607	$room_day_damage_dep = $room_day_damage_dep / (int) $gbook[0]['roomsnum'] * max(1, count(array_intersect(array_column($gbook, 'idroom'), $filter_rooms)));
608	}
609	$tot_damage_deps += $room_day_damage_dep;
610	}
611	}
612
613	$occupancy = round(($rooms_sold * 100 / $total_rooms_units), 2);
614	$adr = $rooms_sold > 0 ? $revenue / $rooms_sold : 0;
615	$revpar = $revenue / $total_rooms_units;
616
617	// push fields in the rows array as a new row
618	array_push($this->rows, array(
619	array(
620	'key' => 'day',
621	'callback' => function ($val) use ($df, $datesep, $curwday) {
622	return $curwday.', '.date(str_replace("/", $datesep, $df), $val);
623	},
624	'value' => $day_ts,
625	),
626	array(
627	'key' => 'rooms_sold',
628	'attr' => array(
629	'class="center"'
630	),
631	'value' => $rooms_sold,
632	),
633	array(
634	'key' => 'tot_bookings',
635	'attr' => array(
636	'class="center"'
637	),
638	'value' => $tot_bookings,
639	),
640	array(
641	'key' => 'occupancy',
642	'attr' => array(
643	'class="center"'
644	),
645	'value' => $occupancy,
646	),
647	array(
648	'key' => 'ibe_revenue',
649	'attr' => array(
650	'class="center"'
651	),
652	'callback' => function ($val) use ($currency_symb) {
653	return $currency_symb . ' ' . VikBooking::numberFormat($val);
654	},
655	'value' => $ibe_revenue,
656	),
657	array(
658	'key' => 'ota_revenue',
659	'attr' => array(
660	'class="center"'
661	),
662	'callback' => function ($val) use ($currency_symb) {
663	return $currency_symb . ' ' . VikBooking::numberFormat($val);
664	},
665	'value' => $ota_revenue,
666	),
667	array(
668	'key' => 'refunds',
669	'attr' => array(
670	'class="center"'
671	),
672	'callback' => function ($val) use ($currency_symb) {
673	return $currency_symb . ' ' . VikBooking::numberFormat($val);
674	},
675	'value' => $tot_refunds,
676	),
677	array(
678	'key' => 'adr',
679	'attr' => array(
680	'class="center"'
681	),
682	'callback' => function ($val) use ($currency_symb) {
683	return $currency_symb . ' ' . VikBooking::numberFormat($val);
684	},
685	'value' => $adr,
686	),
687	array(
688	'key' => 'revpar',
689	'attr' => array(
690	'class="center"'
691	),
692	'callback' => function ($val) use ($currency_symb) {
693	return $currency_symb . ' ' . VikBooking::numberFormat($val);
694	},
695	'value' => $revpar,
696	),
697	array(
698	'key' => 'taxes',
699	'attr' => array(
700	'class="center"'
701	),
702	'callback' => function ($val) use ($currency_symb) {
703	return $currency_symb . ' ' . VikBooking::numberFormat($val);
704	},
705	'value' => $taxes,
706	),
707	array(
708	'key' => 'damagedep',
709	'attr' => array(
710	'class="center"'
711	),
712	'callback' => function ($val) use ($currency_symb) {
713	return $currency_symb . ' ' . VikBooking::numberFormat($val);
714	},
715	'value' => $tot_damage_deps,
716	),
717	array(
718	'key' => 'cmms',
719	'attr' => array(
720	'class="center"'
721	),
722	'callback' => function ($val) use ($currency_symb) {
723	return $currency_symb . ' ' . VikBooking::numberFormat($val);
724	},
725	'value' => $cmms,
726	),
727	array(
728	'key' => 'canc_fees',
729	'attr' => array(
730	'class="center"'
731	),
732	'callback' => function ($val) use ($currency_symb) {
733	return $currency_symb . ' ' . VikBooking::numberFormat($val);
734	},
735	'value' => $canc_fees,
736	),
737	array(
738	'key' => 'revenue',
739	'attr' => array(
740	'class="center"'
741	),
742	'callback' => function ($val) use ($currency_symb) {
743	return $currency_symb . ' ' . VikBooking::numberFormat($val);
744	},
745	'value' => ($revenue + $canc_fees),
746	)
747	));
748
749	// next day iteration
750	$from_info = getdate(mktime(0, 0, 0, $from_info['mon'], ($from_info['mday'] + 1), $from_info['year']));
751	}
752
753	// sort rows
754	$this->sortRows($pkrsort, $pkrorder);
755
756	// loop over the rows to build the footer row with the totals
757	$foot_rooms_sold = 0;
758	$foot_tot_bookings = 0;
759	$foot_ibe_revenue = 0;
760	$foot_ota_revenue = 0;
761	$foot_tot_refunds = 0;
762	$foot_taxes = 0;
763	$foot_damage_deps = 0;
764	$foot_cmms = 0;
765	$foot_canc_fees = 0;
766	$foot_revenue = 0;
767
768	foreach ($this->rows as $row) {
769	$foot_rooms_sold += $row[1]['value'];
770	$foot_tot_bookings += $row[2]['value'];
771	$foot_ibe_revenue += $row[4]['value'];
772	$foot_ota_revenue += $row[5]['value'];
773	$foot_tot_refunds += $row[6]['value'];
774	$foot_taxes += $row[9]['value'];
775	$foot_damage_deps += $row[10]['value'];
776	$foot_cmms += $row[11]['value'];
777	$foot_canc_fees += $row[12]['value'];
778	$foot_revenue += $row[13]['value'];
779	}
780
781	array_push($this->footerRow, array(
782	array(
783	'attr' => array(
784	'class="vbo-report-total"'
785	),
786	'value' => '<h3>'.JText::translate('VBOREPORTSTOTALROW').'</h3>',
787	),
788	array(
789	'attr' => array(
790	'class="center"'
791	),
792	'value' => $foot_rooms_sold,
793	),
794	array(
795	'attr' => array(
796	'class="center"'
797	),
798	'value' => $foot_tot_bookings,
799	),
800	array(
801	'value' => '',
802	),
803	array(
804	'attr' => array(
805	'class="center"'
806	),
807	'callback' => function ($val) use ($currency_symb) {
808	return $currency_symb . ' ' . VikBooking::numberFormat($val);
809	},
810	'value' => $foot_ibe_revenue,
811	),
812	array(
813	'attr' => array(
814	'class="center"'
815	),
816	'callback' => function ($val) use ($currency_symb) {
817	return $currency_symb . ' ' . VikBooking::numberFormat($val);
818	},
819	'value' => $foot_ota_revenue,
820	),
821	array(
822	'attr' => array(
823	'class="center"'
824	),
825	'callback' => function ($val) use ($currency_symb) {
826	return $currency_symb . ' ' . VikBooking::numberFormat($val);
827	},
828	'value' => $foot_tot_refunds,
829	),
830	array(
831	'value' => '',
832	),
833	array(
834	'value' => '',
835	),
836	array(
837	'attr' => array(
838	'class="center"'
839	),
840	'callback' => function ($val) use ($currency_symb) {
841	return $currency_symb . ' ' . VikBooking::numberFormat($val);
842	},
843	'value' => $foot_taxes,
844	),
845	array(
846	'attr' => array(
847	'class="center"'
848	),
849	'callback' => function ($val) use ($currency_symb) {
850	return $currency_symb . ' ' . VikBooking::numberFormat($val);
851	},
852	'value' => $foot_damage_deps,
853	),
854	array(
855	'attr' => array(
856	'class="center"'
857	),
858	'callback' => function ($val) use ($currency_symb) {
859	return $currency_symb . ' ' . VikBooking::numberFormat($val);
860	},
861	'value' => $foot_cmms,
862	),
863	array(
864	'attr' => array(
865	'class="center"'
866	),
867	'callback' => function ($val) use ($currency_symb) {
868	return $currency_symb . ' ' . VikBooking::numberFormat($val);
869	},
870	'value' => $foot_canc_fees,
871	),
872	array(
873	'attr' => array(
874	'class="center"'
875	),
876	'callback' => function ($val) use ($currency_symb) {
877	return $currency_symb . ' ' . VikBooking::numberFormat($val);
878	},
879	'value' => $foot_revenue,
880	)
881	));
882
883	// Debug
884	if ($this->debug) {
885	$this->setWarning('path to report file = '.urlencode(dirname(__FILE__)).'<br/>');
886	$this->setWarning('$total_rooms_units = '.$total_rooms_units.'<br/>');
887	$this->setWarning('$bookings:<pre>'.print_r($bookings, true).'</pre><br/>');
888	}
889
890	return true;
891	}
892
893	/**
894	* Registers the name to give to the CSV file being exported.
895	*
896	* @return void
897	*
898	* @since 1.16.1 (J) - 1.6.1 (WP)
899	*/
900	private function registerExportCSVFileName()
901	{
902	$pfromdate = VikRequest::getString('fromdate', '', 'request');
903	$ptodate = VikRequest::getString('todate', '', 'request');
904
905	$report_extraname = '';
906	$pchannel = VikRequest::getString('channel', '', 'request');
907	if (strlen($pchannel)) {
908	// set channel name for exported file
909	if ($pchannel == '-1') {
910	$report_extraname = JText::translate('VBORDFROMSITE');
911	} else {
912	$report_extraname = $pchannel;
913	}
914	}
915
916	$this->setExportCSVFileName($this->reportName . (!empty($report_extraname) ? '-' . $report_extraname : '') . '-' . str_replace('/', '_', $pfromdate) . '-' . str_replace('/', '_', $ptodate) . '.csv');
917	}
918	}
919