| 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 |
* Occupancy Ranking child Class of VikBookingReport |
| 15 |
*/ |
| 16 |
class VikBookingReportOccupancyRanking extends VikBookingReport |
| 17 |
{ |
| 18 |
/** |
| 19 |
* Property 'defaultKeySort' is used by the View that renders the report. |
| 20 |
*/ |
| 21 |
public $defaultKeySort = 'occupancy'; |
| 22 |
|
| 23 |
/** |
| 24 |
* Property 'defaultKeyOrder' is used by the View that renders the report. |
| 25 |
*/ |
| 26 |
public $defaultKeyOrder = 'DESC'; |
| 27 |
|
| 28 |
/** |
| 29 |
* Property 'exportAllowed' is used by the View to display the export button. |
| 30 |
*/ |
| 31 |
public $exportAllowed = 1; |
| 32 |
|
| 33 |
/** |
| 34 |
* The script to render the Chart |
| 35 |
*/ |
| 36 |
protected $chartScript; |
| 37 |
|
| 38 |
/** |
| 39 |
* The current Chart title |
| 40 |
*/ |
| 41 |
protected $chartTitle; |
| 42 |
|
| 43 |
/** |
| 44 |
* The Chart meta data |
| 45 |
*/ |
| 46 |
protected $chartMetaData; |
| 47 |
|
| 48 |
/** |
| 49 |
* The Chart labels |
| 50 |
*/ |
| 51 |
protected $chartJsLabels; |
| 52 |
|
| 53 |
/** |
| 54 |
* The Chart Dataset label(s) |
| 55 |
*/ |
| 56 |
protected $chartJsDataSetLabel; |
| 57 |
|
| 58 |
/** |
| 59 |
* The Chart colors |
| 60 |
*/ |
| 61 |
protected $chartJsColors; |
| 62 |
|
| 63 |
/** |
| 64 |
* The Chart Data |
| 65 |
*/ |
| 66 |
protected $chartJsData; |
| 67 |
|
| 68 |
/** |
| 69 |
* Debug mode is activated by passing the value 'e4j_debug' > 0 |
| 70 |
*/ |
| 71 |
private $debug; |
| 72 |
|
| 73 |
/** |
| 74 |
* Class constructor should define the name of the report and |
| 75 |
* other vars. Call the parent constructor to define the DB object. |
| 76 |
*/ |
| 77 |
public function __construct() |
| 78 |
{ |
| 79 |
$this->reportFile = basename(__FILE__, '.php'); |
| 80 |
$this->reportName = JText::translate('VBOREPORT'.strtoupper(str_replace('_', '', $this->reportFile))); |
| 81 |
$this->reportFilters = array(); |
| 82 |
|
| 83 |
$this->cols = array(); |
| 84 |
$this->rows = array(); |
| 85 |
$this->footerRow = array(); |
| 86 |
|
| 87 |
$this->chartScript = ''; |
| 88 |
$this->chartTitle = ''; |
| 89 |
$this->chartMetaData = array(); |
| 90 |
$this->chartJsLabels = array(); |
| 91 |
$this->chartJsDataSetLabel = ''; |
| 92 |
$this->chartJsColors = array(); |
| 93 |
$this->chartJsData = array(); |
| 94 |
|
| 95 |
$this->debug = (VikRequest::getInt('e4j_debug', 0, 'request') > 0); |
| 96 |
|
| 97 |
$this->registerExportCSVFileName(); |
| 98 |
|
| 99 |
parent::__construct(); |
| 100 |
} |
| 101 |
|
| 102 |
/** |
| 103 |
* Returns the name of this report. |
| 104 |
* |
| 105 |
* @return string |
| 106 |
*/ |
| 107 |
public function getName() |
| 108 |
{ |
| 109 |
return $this->reportName; |
| 110 |
} |
| 111 |
|
| 112 |
/** |
| 113 |
* Returns the name of this file without .php. |
| 114 |
* |
| 115 |
* @return string |
| 116 |
*/ |
| 117 |
public function getFileName() |
| 118 |
{ |
| 119 |
return $this->reportFile; |
| 120 |
} |
| 121 |
|
| 122 |
/** |
| 123 |
* Returns the filters of this report. |
| 124 |
* |
| 125 |
* @return array |
| 126 |
*/ |
| 127 |
public function getFilters() |
| 128 |
{ |
| 129 |
if (count($this->reportFilters)) { |
| 130 |
// do not run this method twice, as it could load JS and CSS files. |
| 131 |
return $this->reportFilters; |
| 132 |
} |
| 133 |
|
| 134 |
// get VBO Application Object |
| 135 |
$vbo_app = VikBooking::getVboApplication(); |
| 136 |
|
| 137 |
// load the jQuery UI Datepicker |
| 138 |
$this->loadDatePicker(); |
| 139 |
|
| 140 |
// load Charts assets |
| 141 |
$this->loadChartsAssets(); |
| 142 |
|
| 143 |
// from Date Filter |
| 144 |
$filter_opt = array( |
| 145 |
'label' => '<label for="fromdate">'.JText::translate('VBOREPORTSDATEFROM').'</label>', |
| 146 |
'html' => '<input type="text" id="fromdate" name="fromdate" value="" class="vbo-report-datepicker vbo-report-datepicker-from" />', |
| 147 |
'type' => 'calendar', |
| 148 |
'name' => 'fromdate' |
| 149 |
); |
| 150 |
array_push($this->reportFilters, $filter_opt); |
| 151 |
|
| 152 |
// to Date Filter |
| 153 |
$filter_opt = array( |
| 154 |
'label' => '<label for="todate">'.JText::translate('VBOREPORTSDATETO').'</label>', |
| 155 |
'html' => '<input type="text" id="todate" name="todate" value="" class="vbo-report-datepicker vbo-report-datepicker-to" />', |
| 156 |
'type' => 'calendar', |
| 157 |
'name' => 'todate' |
| 158 |
); |
| 159 |
array_push($this->reportFilters, $filter_opt); |
| 160 |
|
| 161 |
// period type filter |
| 162 |
$pperiod = VikRequest::getString('period', 'month', 'request'); |
| 163 |
$periods = array( |
| 164 |
'month' => JText::translate('VBPVIEWRESTRICTIONSTWO'), |
| 165 |
'week' => JText::translate('VBOWEEK'), |
| 166 |
'day' => JText::translate('VBODAY'), |
| 167 |
); |
| 168 |
$periods_sel_html = $vbo_app->getNiceSelect($periods, $pperiod, 'period', '', '', '', '', 'period'); |
| 169 |
$filter_opt = array( |
| 170 |
'label' => '<label for="period">'.JText::translate('VBOGROUPBY').'</label>', |
| 171 |
'html' => $periods_sel_html, |
| 172 |
'type' => 'select', |
| 173 |
'name' => 'period' |
| 174 |
); |
| 175 |
array_push($this->reportFilters, $filter_opt); |
| 176 |
|
| 177 |
// room ID filter |
| 178 |
$pidroom = VikRequest::getInt('idroom', '', 'request'); |
| 179 |
$all_rooms = $this->getRooms(); |
| 180 |
$rooms = array(); |
| 181 |
foreach ($all_rooms as $room) { |
| 182 |
$rooms[$room['id']] = $room['name']; |
| 183 |
} |
| 184 |
if (count($rooms)) { |
| 185 |
$rooms_sel_html = $vbo_app->getNiceSelect($rooms, $pidroom, 'idroom', JText::translate('VBOSTATSALLROOMS'), JText::translate('VBOSTATSALLROOMS'), '', '', 'idroom'); |
| 186 |
$filter_opt = array( |
| 187 |
'label' => '<label for="idroom">'.JText::translate('VBOREPORTSROOMFILT').'</label>', |
| 188 |
'html' => $rooms_sel_html, |
| 189 |
'type' => 'select', |
| 190 |
'name' => 'idroom' |
| 191 |
); |
| 192 |
array_push($this->reportFilters, $filter_opt); |
| 193 |
} |
| 194 |
|
| 195 |
// channel filter |
| 196 |
$all_channels = array(); |
| 197 |
$pchannel = VikRequest::getString('channel', '', 'request'); |
| 198 |
$q = "SELECT `channel` FROM `#__vikbooking_orders` WHERE `channel` IS NOT NULL GROUP BY `channel`;"; |
| 199 |
$this->dbo->setQuery($q); |
| 200 |
$this->dbo->execute(); |
| 201 |
if ($this->dbo->getNumRows()) { |
| 202 |
$ord_channels = $this->dbo->loadAssocList(); |
| 203 |
// push website as first option |
| 204 |
$all_channels['-1'] = JText::translate('VBORDFROMSITE'); |
| 205 |
// push all channel names |
| 206 |
foreach ($ord_channels as $o_channel) { |
| 207 |
$channel_parts = explode('_', $o_channel['channel']); |
| 208 |
$channel_name = count($channel_parts) > 1 ? trim($channel_parts[1]) : trim($channel_parts[0]); |
| 209 |
if (isset($all_channels[$channel_name])) { |
| 210 |
continue; |
| 211 |
} |
| 212 |
$say_channel_name = $channel_name == 'googlehotel' ? 'Google Hotel' : ucwords($channel_name); |
| 213 |
$all_channels[$channel_name] = $say_channel_name; |
| 214 |
} |
| 215 |
// push filter |
| 216 |
$channels_sel_html = $vbo_app->getNiceSelect($all_channels, $pchannel, 'channel', '- - - -', '- - - -', '', '', 'channel'); |
| 217 |
$filter_opt = array( |
| 218 |
'label' => '<label for="channel">'.JText::translate('VBCHANNELFILTER').'</label>', |
| 219 |
'html' => $channels_sel_html, |
| 220 |
'type' => 'select', |
| 221 |
'name' => 'channel' |
| 222 |
); |
| 223 |
array_push($this->reportFilters, $filter_opt); |
| 224 |
} |
| 225 |
|
| 226 |
// get minimum check-in and maximum check-out for dates filters |
| 227 |
$df = $this->getDateFormat(); |
| 228 |
$mincheckin = 0; |
| 229 |
$maxcheckout = 0; |
| 230 |
$q = "SELECT MIN(`checkin`) AS `mincheckin`, MAX(`checkout`) AS `maxcheckout` FROM `#__vikbooking_orders` WHERE `status`='confirmed' AND `closure`=0;"; |
| 231 |
$this->dbo->setQuery($q); |
| 232 |
$this->dbo->execute(); |
| 233 |
if ($this->dbo->getNumRows()) { |
| 234 |
$data = $this->dbo->loadAssoc(); |
| 235 |
if (!empty($data['mincheckin']) && !empty($data['maxcheckout'])) { |
| 236 |
$mincheckin = $data['mincheckin']; |
| 237 |
$maxcheckout = $data['maxcheckout']; |
| 238 |
} |
| 239 |
} |
| 240 |
// |
| 241 |
|
| 242 |
// jQuery code for the datepicker calendars and select2 |
| 243 |
$pfromdate = VikRequest::getString('fromdate', '', 'request'); |
| 244 |
$pfromdate = empty($pfromdate) && !empty($mincheckin) ? date($df, $mincheckin) : $pfromdate; |
| 245 |
$ptodate = VikRequest::getString('todate', '', 'request'); |
| 246 |
$ptodate = empty($ptodate) && !empty($maxcheckout) ? date($df, $maxcheckout) : $ptodate; |
| 247 |
$js = 'jQuery(document).ready(function() { |
| 248 |
jQuery(".vbo-report-datepicker:input").datepicker({ |
| 249 |
'.(!empty($mincheckin) ? 'minDate: "'.date($df, $mincheckin).'", ' : '').' |
| 250 |
'.(!empty($maxcheckout) ? 'maxDate: "'.date($df, $maxcheckout).'", ' : '').' |
| 251 |
'.(!empty($mincheckin) && !empty($maxcheckout) ? 'yearRange: "'.(date('Y', $mincheckin)).':'.date('Y', $maxcheckout).'", changeMonth: true, changeYear: true, ' : '').' |
| 252 |
dateFormat: "'.$this->getDateFormat('jui').'", |
| 253 |
onSelect: vboReportCheckDates |
| 254 |
}); |
| 255 |
'.(!empty($pfromdate) ? 'jQuery(".vbo-report-datepicker-from").datepicker("setDate", "'.$pfromdate.'");' : '').' |
| 256 |
'.(!empty($ptodate) ? 'jQuery(".vbo-report-datepicker-to").datepicker("setDate", "'.$ptodate.'");' : '').' |
| 257 |
}); |
| 258 |
function vboReportCheckDates(selectedDate, inst) { |
| 259 |
if (selectedDate === null || inst === null) { |
| 260 |
return; |
| 261 |
} |
| 262 |
var cur_from_date = jQuery(this).val(); |
| 263 |
if (jQuery(this).hasClass("vbo-report-datepicker-from") && cur_from_date.length) { |
| 264 |
var nowstart = jQuery(this).datepicker("getDate"); |
| 265 |
var nowstartdate = new Date(nowstart.getTime()); |
| 266 |
jQuery(".vbo-report-datepicker-to").datepicker("option", {minDate: nowstartdate}); |
| 267 |
} |
| 268 |
}'; |
| 269 |
$this->setScript($js); |
| 270 |
|
| 271 |
return $this->reportFilters; |
| 272 |
} |
| 273 |
|
| 274 |
/** |
| 275 |
* Loads the report data from the DB. |
| 276 |
* Returns true in case of success, false otherwise. |
| 277 |
* Sets the columns and rows for the report to be displayed. |
| 278 |
* |
| 279 |
* @return boolean |
| 280 |
*/ |
| 281 |
public function getReportData() |
| 282 |
{ |
| 283 |
if (strlen($this->getError())) { |
| 284 |
// export functions may set errors rather than exiting the process, and the View may continue the execution to attempt to render the report. |
| 285 |
return false; |
| 286 |
} |
| 287 |
// input fields and other vars |
| 288 |
$pfromdate = VikRequest::getString('fromdate', '', 'request'); |
| 289 |
$ptodate = VikRequest::getString('todate', '', 'request'); |
| 290 |
$pperiod = VikRequest::getString('period', 'month', 'request'); |
| 291 |
// idroom can be an array of IDs or just one ID as int/string |
| 292 |
$pidroom = VikRequest::getVar('idroom', null, 'request'); |
| 293 |
// |
| 294 |
$pchannel = VikRequest::getString('channel', '', 'request'); |
| 295 |
$pkrsort = VikRequest::getString('krsort', $this->defaultKeySort, 'request'); |
| 296 |
$pkrsort = empty($pkrsort) ? $this->defaultKeySort : $pkrsort; |
| 297 |
$pkrorder = VikRequest::getString('krorder', $this->defaultKeyOrder, 'request'); |
| 298 |
$pkrorder = empty($pkrorder) ? $this->defaultKeyOrder : $pkrorder; |
| 299 |
$pkrorder = $pkrorder == 'DESC' ? 'DESC' : 'ASC'; |
| 300 |
// bookings max creation date |
| 301 |
$pmaxdate = VikRequest::getString('maxdate', '', 'request'); |
| 302 |
$pmaxdate = !empty($pmaxdate) ? VikBooking::getDateTimestamp($pmaxdate, 23, 59, 59) : $pmaxdate; |
| 303 |
// |
| 304 |
$currency_symb = VikBooking::getCurrencySymb(); |
| 305 |
$df = $this->getDateFormat(); |
| 306 |
$datesep = VikBooking::getDateSeparator(); |
| 307 |
if (empty($ptodate)) { |
| 308 |
$ptodate = $pfromdate; |
| 309 |
} |
| 310 |
// get dates timestamps |
| 311 |
$from_ts = VikBooking::getDateTimestamp($pfromdate, 0, 0); |
| 312 |
$to_ts = VikBooking::getDateTimestamp($ptodate, 23, 59, 59); |
| 313 |
if (empty($pfromdate) || empty($from_ts) || empty($to_ts)) { |
| 314 |
$this->setError(JText::translate('VBOREPORTSERRNODATES')); |
| 315 |
return false; |
| 316 |
} |
| 317 |
|
| 318 |
// months map |
| 319 |
$months_map = array( |
| 320 |
JText::translate('VBMONTHONE'), |
| 321 |
JText::translate('VBMONTHTWO'), |
| 322 |
JText::translate('VBMONTHTHREE'), |
| 323 |
JText::translate('VBMONTHFOUR'), |
| 324 |
JText::translate('VBMONTHFIVE'), |
| 325 |
JText::translate('VBMONTHSIX'), |
| 326 |
JText::translate('VBMONTHSEVEN'), |
| 327 |
JText::translate('VBMONTHEIGHT'), |
| 328 |
JText::translate('VBMONTHNINE'), |
| 329 |
JText::translate('VBMONTHTEN'), |
| 330 |
JText::translate('VBMONTHELEVEN'), |
| 331 |
JText::translate('VBMONTHTWELVE'), |
| 332 |
); |
| 333 |
|
| 334 |
// query to obtain the records |
| 335 |
$records = array(); |
| 336 |
$q = "SELECT `o`.`id`,`o`.`ts`,`o`.`days`,`o`.`checkin`,`o`.`checkout`,`o`.`totpaid`,`o`.`roomsnum`,`o`.`total`,`o`.`idorderota`,`o`.`channel`,`o`.`country`,`o`.`tot_taxes`," . |
| 337 |
"`o`.`tot_city_taxes`,`o`.`tot_fees`,`o`.`cmms`,`or`.`idorder`,`or`.`idroom`,`or`.`optionals`,`or`.`cust_cost`,`or`.`cust_idiva`,`or`.`extracosts`,`or`.`room_cost`,`r`.`name` AS `room_name` " . |
| 338 |
"FROM `#__vikbooking_orders` AS `o` LEFT JOIN `#__vikbooking_ordersrooms` AS `or` ON `or`.`idorder`=`o`.`id` " . |
| 339 |
"LEFT JOIN `#__vikbooking_rooms` AS `r` ON `or`.`idroom`=`r`.`id` " . |
| 340 |
"WHERE ".(!empty($pmaxdate) ? "`o`.`ts`<={$pmaxdate} AND " : "")."`r`.`name` IS NOT NULL AND `o`.`status`='confirmed' AND `o`.`closure`=0 AND `o`.`checkout`>={$from_ts} AND `o`.`checkin`<={$to_ts} " . |
| 341 |
(!empty($pidroom) && !is_array($pidroom) ? "AND `or`.`idroom`=" . (int)$pidroom . " " : (is_array($pidroom) && count($pidroom) ? "AND `or`.`idroom` IN (" . implode(', ', $pidroom) . ") " : '')) . |
| 342 |
(strlen($pchannel) ? "AND `o`.`channel` " . ($pchannel == '-1' ? 'IS NULL' : "LIKE " . $this->dbo->quote("%{$pchannel}%")) . ' ' : '') . |
| 343 |
"ORDER BY `o`.`checkin` ASC, `o`.`id` ASC;"; |
| 344 |
$this->dbo->setQuery($q); |
| 345 |
$this->dbo->execute(); |
| 346 |
if ($this->dbo->getNumRows()) { |
| 347 |
$records = $this->dbo->loadAssocList(); |
| 348 |
} |
| 349 |
|
| 350 |
$dummy_values = false; |
| 351 |
if (!count($records)) { |
| 352 |
if ($pperiod != 'full') { |
| 353 |
// when using the regular report interface, we display an error in case of no bookings found |
| 354 |
$this->setError(JText::translate('VBOREPORTSERRNORESERV')); |
| 355 |
return false; |
| 356 |
} |
| 357 |
|
| 358 |
// layout file building the chart prefers empty values rather than an error |
| 359 |
$dummy_values = true; |
| 360 |
// populate array with one dummy booking with empty values |
| 361 |
$records = array( |
| 362 |
array( |
| 363 |
'id' => -1, |
| 364 |
'ts' => time(), |
| 365 |
'days' => 1, |
| 366 |
'checkin' => $from_ts, |
| 367 |
'checkout' => strtotime("+1 day", $from_ts), |
| 368 |
'totpaid' => 0, |
| 369 |
'roomsnum' => 1, |
| 370 |
'total' => 0, |
| 371 |
'idorderota' => null, |
| 372 |
'channel' => null, |
| 373 |
'country' => null, |
| 374 |
'tot_taxes' => 0, |
| 375 |
'tot_city_taxes' => 0, |
| 376 |
'tot_fees' => 0, |
| 377 |
'cmms' => 0, |
| 378 |
'idorder' => -1, |
| 379 |
'idroom' => -1, |
| 380 |
'optionals' => null, |
| 381 |
'cust_cost' => null, |
| 382 |
'cust_idiva' => null, |
| 383 |
'extracosts' => null, |
| 384 |
'room_cost' => 0, |
| 385 |
), |
| 386 |
); |
| 387 |
} |
| 388 |
|
| 389 |
// nest records with multiple rooms booked inside sub-array |
| 390 |
$bookings = array(); |
| 391 |
foreach ($records as $v) { |
| 392 |
if (!isset($bookings[$v['id']])) { |
| 393 |
$bookings[$v['id']] = array(); |
| 394 |
} |
| 395 |
// calculate the from_ts and to_ts values for later comparison |
| 396 |
$in_info = getdate($v['checkin']); |
| 397 |
$out_info = getdate($v['checkout']); |
| 398 |
// these two properties are necessary for many other controls below |
| 399 |
$v['from_ts'] = mktime(0, 0, 0, $in_info['mon'], $in_info['mday'], $in_info['year']); |
| 400 |
$v['to_ts'] = mktime(23, 59, 59, $out_info['mon'], ($out_info['mday'] - 1), $out_info['year']); |
| 401 |
// |
| 402 |
array_push($bookings[$v['id']], $v); |
| 403 |
} |
| 404 |
|
| 405 |
// first day of the week for weekly periods (0 for Sunday till 6 for Saturday) |
| 406 |
$firstwday = (int)VikBooking::getFirstWeekDay(); |
| 407 |
// we make it end to the day before as weeks should start on this weekday |
| 408 |
$firstwday -= 1; |
| 409 |
$firstwday = $firstwday < 0 ? 6 : $firstwday; |
| 410 |
|
| 411 |
// build ranges of periods by looping over the dates of the report |
| 412 |
$ranges = array(); |
| 413 |
$from_info = getdate($from_ts); |
| 414 |
$to_info = getdate($to_ts); |
| 415 |
$cur_month = array('from_ts' => $from_info[0]); |
| 416 |
$cur_week = array('from_ts' => $from_info[0]); |
| 417 |
while ($from_info[0] <= $to_info[0]) { |
| 418 |
if ($pperiod == 'month') { |
| 419 |
if (date('n', $from_info[0]) != date('n', $cur_month['from_ts'])) { |
| 420 |
// month has changed, set to_ts to previous day at midnight |
| 421 |
$cur_month['to_ts'] = mktime(23, 59, 59, $from_info['mon'], ($from_info['mday'] - 1), $from_info['year']); |
| 422 |
// push month delimiter to ranges |
| 423 |
array_push($ranges, array( |
| 424 |
'from_ts' => $cur_month['from_ts'], |
| 425 |
'to_ts' => $cur_month['to_ts'], |
| 426 |
)); |
| 427 |
// reset current month handler to current day (1st of the new month) |
| 428 |
$cur_month = array( |
| 429 |
'from_ts' => $from_info[0] |
| 430 |
); |
| 431 |
} |
| 432 |
} elseif ($pperiod == 'week') { |
| 433 |
if (!isset($cur_week['from_ts'])) { |
| 434 |
// 1st day of the new week |
| 435 |
$cur_week['from_ts'] = $from_info[0]; |
| 436 |
} |
| 437 |
if ($from_info[0] != $cur_week['from_ts'] && (int)$from_info['wday'] == $firstwday) { |
| 438 |
// not the first day of the loop, but same weekday, so it's the week after |
| 439 |
$cur_week['to_ts'] = mktime(23, 59, 59, $from_info['mon'], $from_info['mday'], $from_info['year']); |
| 440 |
// push week delimiter to ranges |
| 441 |
array_push($ranges, array( |
| 442 |
'from_ts' => $cur_week['from_ts'], |
| 443 |
'to_ts' => $cur_week['to_ts'], |
| 444 |
)); |
| 445 |
// reset current week handler |
| 446 |
$cur_week = array(); |
| 447 |
} |
| 448 |
} elseif ($pperiod == 'day') { |
| 449 |
// push the range until the end of the current day |
| 450 |
array_push($ranges, array( |
| 451 |
'from_ts' => $from_info[0], |
| 452 |
'to_ts' => mktime(23, 59, 59, $from_info['mon'], $from_info['mday'], $from_info['year']), |
| 453 |
)); |
| 454 |
} else { |
| 455 |
// (full) push the range until the "to date" |
| 456 |
array_push($ranges, array( |
| 457 |
'from_ts' => $from_info[0], |
| 458 |
'to_ts' => $to_info[0], |
| 459 |
)); |
| 460 |
// do not loop any further date as we need the entire range requested |
| 461 |
break; |
| 462 |
} |
| 463 |
|
| 464 |
// next day iteration |
| 465 |
$from_info = getdate(mktime(0, 0, 0, $from_info['mon'], ($from_info['mday'] + 1), $from_info['year'])); |
| 466 |
} |
| 467 |
|
| 468 |
// finalize ranges of period delimiters in case the loop ended on a non-precise date |
| 469 |
if ($pperiod == 'month' && date('Y-m-d', $cur_month['from_ts']) != date('Y-m-d', $to_info[0])) { |
| 470 |
// push last month delimiter to ranges |
| 471 |
array_push($ranges, array( |
| 472 |
'from_ts' => $cur_month['from_ts'], |
| 473 |
'to_ts' => $to_info[0], |
| 474 |
)); |
| 475 |
} elseif ($pperiod == 'week' && isset($cur_week['from_ts'])) { |
| 476 |
// push last week delimiter to ranges |
| 477 |
array_push($ranges, array( |
| 478 |
'from_ts' => $cur_week['from_ts'], |
| 479 |
'to_ts' => $to_info[0], |
| 480 |
)); |
| 481 |
} |
| 482 |
|
| 483 |
// total number of rooms |
| 484 |
$total_rooms_units = $this->countRooms($pidroom); |
| 485 |
|
| 486 |
// define the columns of the report |
| 487 |
$this->cols = array( |
| 488 |
// date |
| 489 |
array( |
| 490 |
'key' => 'day', |
| 491 |
'sortable' => 1, |
| 492 |
'label' => JText::translate('VBOREPORTREVENUEDAY') |
| 493 |
), |
| 494 |
// rooms sold |
| 495 |
array( |
| 496 |
'key' => 'rooms_sold', |
| 497 |
'attr' => array( |
| 498 |
'class="center"' |
| 499 |
), |
| 500 |
'sortable' => 1, |
| 501 |
'label' => JText::translate('VBOREPORTREVENUERSOLD'), |
| 502 |
'tip' => JText::sprintf('VBOREPORTTOTROOMSHELP', $total_rooms_units) |
| 503 |
), |
| 504 |
// nights booked |
| 505 |
array( |
| 506 |
'key' => 'nights_booked', |
| 507 |
'attr' => array( |
| 508 |
'class="center"' |
| 509 |
), |
| 510 |
'sortable' => 1, |
| 511 |
'label' => JText::translate('VBOGRAPHTOTNIGHTSLBL') |
| 512 |
), |
| 513 |
// total bookings |
| 514 |
array( |
| 515 |
'key' => 'tot_bookings', |
| 516 |
'attr' => array( |
| 517 |
'class="center"' |
| 518 |
), |
| 519 |
'sortable' => 1, |
| 520 |
'label' => JText::translate('VBOREPORTREVENUETOTB') |
| 521 |
), |
| 522 |
// % occupancy |
| 523 |
array( |
| 524 |
'key' => 'occupancy', |
| 525 |
'attr' => array( |
| 526 |
'class="center"' |
| 527 |
), |
| 528 |
'sortable' => 1, |
| 529 |
'label' => JText::translate('VBOREPORTREVENUEPOCC') |
| 530 |
), |
| 531 |
// IBE revenue |
| 532 |
array( |
| 533 |
'key' => 'ibe_revenue', |
| 534 |
'attr' => array( |
| 535 |
'class="center vbo-report-col-hideable"' |
| 536 |
), |
| 537 |
'sortable' => 1, |
| 538 |
'label' => JText::translate('VBOREPORTREVENUEREVWEB') |
| 539 |
), |
| 540 |
// OTAs revenue |
| 541 |
array( |
| 542 |
'key' => 'ota_revenue', |
| 543 |
'attr' => array( |
| 544 |
'class="center vbo-report-col-hideable"' |
| 545 |
), |
| 546 |
'sortable' => 1, |
| 547 |
'label' => JText::translate('VBOREPORTREVENUEREVOTA') |
| 548 |
), |
| 549 |
// ADR |
| 550 |
array( |
| 551 |
'key' => 'adr', |
| 552 |
'attr' => array( |
| 553 |
'class="center vbo-report-col-hideable"' |
| 554 |
), |
| 555 |
'sortable' => 1, |
| 556 |
'label' => JText::translate('VBOREPORTREVENUEADR'), |
| 557 |
'tip' => JText::translate('VBOREPORTREVENUEADRHELP') |
| 558 |
), |
| 559 |
// RevPAR |
| 560 |
array( |
| 561 |
'key' => 'revpar', |
| 562 |
'attr' => array( |
| 563 |
'class="center vbo-report-col-hideable"' |
| 564 |
), |
| 565 |
'sortable' => 1, |
| 566 |
'label' => JText::translate('VBOREPORTREVENUEREVPAR'), |
| 567 |
'tip' => JText::translate('VBOREPORTREVENUEREVPARH') |
| 568 |
), |
| 569 |
// Taxes |
| 570 |
array( |
| 571 |
'key' => 'taxes', |
| 572 |
'attr' => array( |
| 573 |
'class="center vbo-report-col-hideable"' |
| 574 |
), |
| 575 |
'sortable' => 1, |
| 576 |
'label' => JText::translate('VBOREPORTREVENUETAX') |
| 577 |
), |
| 578 |
// Revenue |
| 579 |
array( |
| 580 |
'key' => 'revenue', |
| 581 |
'attr' => array( |
| 582 |
'class="center"' |
| 583 |
), |
| 584 |
'sortable' => 1, |
| 585 |
'label' => JText::translate('VBOREPORTREVENUEREV') |
| 586 |
) |
| 587 |
); |
| 588 |
|
| 589 |
// loop over the ranges to build the rows |
| 590 |
foreach ($ranges as $ind => $range) { |
| 591 |
// prepare default fields for this row |
| 592 |
$range_ts_from = $range['from_ts']; |
| 593 |
$info_ts_from = getdate($range['from_ts']); |
| 594 |
$range_ts_to = $range['to_ts']; |
| 595 |
$info_ts_to = getdate($range['to_ts']); |
| 596 |
$curwday_from = $this->getWdayString($info_ts_from['wday'], 'short'); |
| 597 |
$curwday_to = $this->getWdayString($info_ts_to['wday'], 'short'); |
| 598 |
$range_same_day = (date('Y-m-d', $range_ts_from) == date('Y-m-d', $range_ts_to)); |
| 599 |
$rooms_sold = 0; |
| 600 |
$nights_booked = 0; |
| 601 |
$tot_bookings = 0; |
| 602 |
$occupancy = 0; |
| 603 |
$ibe_revenue = 0; |
| 604 |
$ota_revenue = 0; |
| 605 |
$adr = 0; |
| 606 |
$revpar = 0; |
| 607 |
$taxes = 0; |
| 608 |
$revenue = 0; |
| 609 |
// count the days in this range |
| 610 |
if (!isset($ranges[$ind]['days'])) { |
| 611 |
$ranges[$ind]['days'] = $this->countDaysInRange($info_ts_from, $info_ts_to); |
| 612 |
} |
| 613 |
// maximum occupancy of this range is given by the days in the range times the total rooms units |
| 614 |
$range_max_occupancy = $ranges[$ind]['days'] * $total_rooms_units; |
| 615 |
$range_max_occupancy = $range_max_occupancy < 1 ? 1 : $range_max_occupancy; |
| 616 |
// calculate the report details for this day |
| 617 |
foreach ($bookings as $gbook) { |
| 618 |
if ($dummy_values) { |
| 619 |
// we need all values to be left as 0 |
| 620 |
break; |
| 621 |
} |
| 622 |
if ( // range start date is between the check-in and check-out of this booking |
| 623 |
$range['from_ts'] >= $gbook[0]['from_ts'] && $range['from_ts'] <= $gbook[0]['to_ts'] || |
| 624 |
// range end date is between the check-in and check-out of this booking |
| 625 |
$range['to_ts'] >= $gbook[0]['from_ts'] && $range['to_ts'] <= $gbook[0]['to_ts'] || |
| 626 |
// range start and end dates include this booking (probably a long period or a short booking) |
| 627 |
$range['from_ts'] <= $gbook[0]['from_ts'] && $range['to_ts'] >= $gbook[0]['to_ts'] |
| 628 |
) { |
| 629 |
// this booking affects the current range of dates |
| 630 |
if (!isset($ranges[$ind]['bookings'])) { |
| 631 |
$ranges[$ind]['bookings'] = array(); |
| 632 |
} |
| 633 |
array_push($ranges[$ind]['bookings'], $gbook[0]['id']); |
| 634 |
// increase values |
| 635 |
$rooms_sold += $gbook[0]['roomsnum']; |
| 636 |
// nights booked is per rooms booked, but $booking_nights is the total nights booked per booking, not per room |
| 637 |
$booking_nights = $this->countNightsBookedRange($info_ts_from, $info_ts_to, $gbook[0]); |
| 638 |
$nights_booked += $booking_nights * $gbook[0]['roomsnum']; |
| 639 |
$tot_bookings++; |
| 640 |
// calculate net revenue and taxes |
| 641 |
$tot_net = $gbook[0]['total'] - (float)$gbook[0]['tot_taxes'] - (float)$gbook[0]['tot_city_taxes'] - (float)$gbook[0]['tot_fees'] - (float)$gbook[0]['cmms']; |
| 642 |
$tot_net = $tot_net / $gbook[0]['days'] * $booking_nights; |
| 643 |
$revenue += $tot_net; |
| 644 |
if (!empty($gbook[0]['idorderota']) && !empty($gbook[0]['channel'])) { |
| 645 |
$ota_revenue += $tot_net; |
| 646 |
} else { |
| 647 |
$ibe_revenue += $tot_net; |
| 648 |
} |
| 649 |
$tot_taxes = ((float)$gbook[0]['tot_taxes'] + (float)$gbook[0]['tot_city_taxes'] + (float)$gbook[0]['tot_fees'] + (float)$gbook[0]['cmms']) / $gbook[0]['days'] * $booking_nights; |
| 650 |
$taxes += $tot_taxes; |
| 651 |
} |
| 652 |
} |
| 653 |
$occupancy = round(($nights_booked * 100 / $range_max_occupancy), 2); |
| 654 |
$adr = $rooms_sold > 0 ? $revenue / $rooms_sold : 0; |
| 655 |
$revpar = $total_rooms_units > 0 ? ($revenue / $total_rooms_units) : 0; |
| 656 |
// push fields in the rows array as a new row |
| 657 |
array_push($this->rows, array( |
| 658 |
array( |
| 659 |
'key' => 'day', |
| 660 |
'callback' => function ($val) use ($range_ts_to, $df, $datesep, $curwday_from, $curwday_to, $pperiod, $months_map, $range_same_day) { |
| 661 |
if ($pperiod == 'day' || $range_same_day) { |
| 662 |
return $curwday_from . ', ' . date(str_replace("/", $datesep, $df), $val); |
| 663 |
} |
| 664 |
if (($pperiod == 'month' || $pperiod == 'full') && date('d', $val) == '1' && date('t', $range_ts_to) == date('d', $range_ts_to) && date('m', $val) == date('m', $range_ts_to)) { |
| 665 |
// full month |
| 666 |
return $months_map[((int)date('m', $val) - 1)] . ' ' . date('Y', $val); |
| 667 |
} |
| 668 |
return $curwday_from . ', ' . date(str_replace("/", $datesep, $df), $val) . ' - ' . $curwday_to . ', ' . date(str_replace("/", $datesep, $df), $range_ts_to); |
| 669 |
}, |
| 670 |
'value' => $range_ts_from |
| 671 |
), |
| 672 |
array( |
| 673 |
'key' => 'rooms_sold', |
| 674 |
'attr' => array( |
| 675 |
'class="center"' |
| 676 |
), |
| 677 |
'value' => $rooms_sold |
| 678 |
), |
| 679 |
array( |
| 680 |
'key' => 'nights_booked', |
| 681 |
'attr' => array( |
| 682 |
'class="center"' |
| 683 |
), |
| 684 |
'callback' => function ($val) use ($range_max_occupancy) { |
| 685 |
return $val . ' / ' . $range_max_occupancy; |
| 686 |
}, |
| 687 |
'value' => $nights_booked |
| 688 |
), |
| 689 |
array( |
| 690 |
'key' => 'tot_bookings', |
| 691 |
'attr' => array( |
| 692 |
'class="center"' |
| 693 |
), |
| 694 |
'value' => $tot_bookings |
| 695 |
), |
| 696 |
array( |
| 697 |
'key' => 'occupancy', |
| 698 |
'attr' => array( |
| 699 |
'class="center"' |
| 700 |
), |
| 701 |
'value' => $occupancy |
| 702 |
), |
| 703 |
array( |
| 704 |
'key' => 'ibe_revenue', |
| 705 |
'attr' => array( |
| 706 |
'class="center vbo-report-col-hideable"' |
| 707 |
), |
| 708 |
'callback' => function ($val) use ($currency_symb) { |
| 709 |
return $currency_symb.' '.VikBooking::numberFormat($val); |
| 710 |
}, |
| 711 |
'value' => $ibe_revenue |
| 712 |
), |
| 713 |
array( |
| 714 |
'key' => 'ota_revenue', |
| 715 |
'attr' => array( |
| 716 |
'class="center vbo-report-col-hideable"' |
| 717 |
), |
| 718 |
'callback' => function ($val) use ($currency_symb) { |
| 719 |
return $currency_symb.' '.VikBooking::numberFormat($val); |
| 720 |
}, |
| 721 |
'value' => $ota_revenue |
| 722 |
), |
| 723 |
array( |
| 724 |
'key' => 'adr', |
| 725 |
'attr' => array( |
| 726 |
'class="center vbo-report-col-hideable"' |
| 727 |
), |
| 728 |
'callback' => function ($val) use ($currency_symb) { |
| 729 |
return $currency_symb.' '.VikBooking::numberFormat($val); |
| 730 |
}, |
| 731 |
'value' => $adr |
| 732 |
), |
| 733 |
array( |
| 734 |
'key' => 'revpar', |
| 735 |
'attr' => array( |
| 736 |
'class="center vbo-report-col-hideable"' |
| 737 |
), |
| 738 |
'callback' => function ($val) use ($currency_symb) { |
| 739 |
return $currency_symb.' '.VikBooking::numberFormat($val); |
| 740 |
}, |
| 741 |
'value' => $revpar |
| 742 |
), |
| 743 |
array( |
| 744 |
'key' => 'taxes', |
| 745 |
'attr' => array( |
| 746 |
'class="center vbo-report-col-hideable"' |
| 747 |
), |
| 748 |
'callback' => function ($val) use ($currency_symb) { |
| 749 |
return $currency_symb.' '.VikBooking::numberFormat($val); |
| 750 |
}, |
| 751 |
'value' => $taxes |
| 752 |
), |
| 753 |
array( |
| 754 |
'key' => 'revenue', |
| 755 |
'attr' => array( |
| 756 |
'class="center"' |
| 757 |
), |
| 758 |
'callback' => function ($val) use ($currency_symb) { |
| 759 |
return $currency_symb.' '.VikBooking::numberFormat($val); |
| 760 |
}, |
| 761 |
'value' => $revenue |
| 762 |
) |
| 763 |
)); |
| 764 |
} |
| 765 |
|
| 766 |
// sort rows |
| 767 |
$this->sortRows($pkrsort, $pkrorder); |
| 768 |
|
| 769 |
// update sorting and ordering key |
| 770 |
$this->defaultKeySort = $pkrsort; |
| 771 |
$this->defaultKeyOrder = $pkrorder; |
| 772 |
|
| 773 |
// loop over the rows to build the footer row with the totals |
| 774 |
$foot_rooms_sold = 0; |
| 775 |
$foot_nights_booked = 0; |
| 776 |
$foot_tot_bookings = 0; |
| 777 |
$foot_ibe_revenue = 0; |
| 778 |
$foot_ota_revenue = 0; |
| 779 |
$foot_taxes = 0; |
| 780 |
$foot_revenue = 0; |
| 781 |
foreach ($this->rows as $row) { |
| 782 |
$foot_rooms_sold += $row[1]['value']; |
| 783 |
$foot_nights_booked += $row[2]['value']; |
| 784 |
$foot_tot_bookings += $row[3]['value']; |
| 785 |
$foot_ibe_revenue += $row[5]['value']; |
| 786 |
$foot_ota_revenue += $row[6]['value']; |
| 787 |
$foot_taxes += $row[9]['value']; |
| 788 |
$foot_revenue += $row[10]['value']; |
| 789 |
} |
| 790 |
array_push($this->footerRow, array( |
| 791 |
array( |
| 792 |
'attr' => array( |
| 793 |
'class="vbo-report-total"' |
| 794 |
), |
| 795 |
'value' => '<h3>'.JText::translate('VBOREPORTSTOTALROW').'</h3>' |
| 796 |
), |
| 797 |
array( |
| 798 |
'attr' => array( |
| 799 |
'class="center"' |
| 800 |
), |
| 801 |
'value' => $foot_rooms_sold |
| 802 |
), |
| 803 |
array( |
| 804 |
'attr' => array( |
| 805 |
'class="center"' |
| 806 |
), |
| 807 |
'value' => $foot_nights_booked |
| 808 |
), |
| 809 |
array( |
| 810 |
'attr' => array( |
| 811 |
'class="center"' |
| 812 |
), |
| 813 |
'value' => $foot_tot_bookings |
| 814 |
), |
| 815 |
array( |
| 816 |
'value' => '' |
| 817 |
), |
| 818 |
array( |
| 819 |
'attr' => array( |
| 820 |
'class="center vbo-report-col-hideable"' |
| 821 |
), |
| 822 |
'callback' => function ($val) use ($currency_symb) { |
| 823 |
return $currency_symb.' '.VikBooking::numberFormat($val); |
| 824 |
}, |
| 825 |
'value' => $foot_ibe_revenue |
| 826 |
), |
| 827 |
array( |
| 828 |
'attr' => array( |
| 829 |
'class="center vbo-report-col-hideable"' |
| 830 |
), |
| 831 |
'callback' => function ($val) use ($currency_symb) { |
| 832 |
return $currency_symb.' '.VikBooking::numberFormat($val); |
| 833 |
}, |
| 834 |
'value' => $foot_ota_revenue |
| 835 |
), |
| 836 |
array( |
| 837 |
'attr' => array( |
| 838 |
'class="center vbo-report-col-hideable"' |
| 839 |
), |
| 840 |
'value' => '' |
| 841 |
), |
| 842 |
array( |
| 843 |
'attr' => array( |
| 844 |
'class="center vbo-report-col-hideable"' |
| 845 |
), |
| 846 |
'value' => '' |
| 847 |
), |
| 848 |
array( |
| 849 |
'attr' => array( |
| 850 |
'class="center vbo-report-col-hideable"' |
| 851 |
), |
| 852 |
'callback' => function ($val) use ($currency_symb) { |
| 853 |
return $currency_symb.' '.VikBooking::numberFormat($val); |
| 854 |
}, |
| 855 |
'value' => $foot_taxes |
| 856 |
), |
| 857 |
array( |
| 858 |
'attr' => array( |
| 859 |
'class="center"' |
| 860 |
), |
| 861 |
'callback' => function ($val) use ($currency_symb) { |
| 862 |
return $currency_symb.' '.VikBooking::numberFormat($val); |
| 863 |
}, |
| 864 |
'value' => $foot_revenue |
| 865 |
) |
| 866 |
)); |
| 867 |
|
| 868 |
// debug data |
| 869 |
$debug_str = 'Periods COUNT = ' . count($ranges) . '<br/>'; |
| 870 |
foreach ($ranges as $range) { |
| 871 |
$debug_str .= date('D, Y-m-d', $range['from_ts']) . ' - ' . date('D, Y-m-d', $range['to_ts']) . (isset($range['days']) ? ' ('.$range['days'].'d)' : '') . (isset($range['bookings']) ? ' - ' . implode(', ', $range['bookings']) : '') . '<br/>'; |
| 872 |
} |
| 873 |
$debug_str .= '<br/><pre>' . print_r($bookings, true) . '</pre><br/>'; |
| 874 |
$debug_str .= 'Total Room Units = ' . $total_rooms_units . '<br/>'; |
| 875 |
if ($this->debug) { |
| 876 |
$this->setWarning($debug_str); |
| 877 |
$this->setWarning('path to report file = '.urlencode(dirname(__FILE__)).'<br/>'); |
| 878 |
$this->setWarning('$total_rooms_units = '.$total_rooms_units.'<br/>'); |
| 879 |
$this->setWarning('$bookings:<pre>'.print_r($bookings, true).'</pre><br/>'); |
| 880 |
} |
| 881 |
|
| 882 |
return true; |
| 883 |
} |
| 884 |
|
| 885 |
/** |
| 886 |
* Counts the number of nights booked by the |
| 887 |
* given booking in the given range of dates. |
| 888 |
* |
| 889 |
* @param array $from_info the getdate() info of the range start date timestamp. |
| 890 |
* @param array $to_info the getdate() info of the range end date timestamp. |
| 891 |
* @param array $booking the booking array (one room record). |
| 892 |
* |
| 893 |
* @return int the total number of nights booked in the given range. |
| 894 |
*/ |
| 895 |
private function countNightsBookedRange($from_info, $to_info, $booking) |
| 896 |
{ |
| 897 |
$tot_nights = 0; |
| 898 |
if (!is_array($from_info) || !is_array($to_info) || $from_info[0] > $to_info[0]) { |
| 899 |
return $tot_nights; |
| 900 |
} |
| 901 |
|
| 902 |
$checkout_ymd = date('Y-m-d', $booking['checkout']); |
| 903 |
|
| 904 |
while ($from_info[0] <= $to_info[0]) { |
| 905 |
if ($from_info[0] >= $booking['from_ts'] && $from_info[0] <= $booking['to_ts']) { |
| 906 |
// range day is inside booking dates |
| 907 |
if (date('Y-m-d', $from_info[0]) == $checkout_ymd) { |
| 908 |
// this is the check-out day, so it is not a night booked |
| 909 |
return $tot_nights; |
| 910 |
} |
| 911 |
$tot_nights++; |
| 912 |
} |
| 913 |
// next date |
| 914 |
$from_info = getdate(mktime(0, 0, 0, $from_info['mon'], ($from_info['mday'] + 1), $from_info['year'])); |
| 915 |
} |
| 916 |
|
| 917 |
return $tot_nights; |
| 918 |
} |
| 919 |
|
| 920 |
/** |
| 921 |
* Counts the number of nights in the given range of dates. |
| 922 |
* End date of range is always inclusive for the bookings. |
| 923 |
* |
| 924 |
* @param array $from_info the getdate() info of the range start date timestamp. |
| 925 |
* @param array $to_info the getdate() info of the range end date timestamp. |
| 926 |
* |
| 927 |
* @return int the total number of days in the given range. |
| 928 |
*/ |
| 929 |
private function countDaysInRange($from_info, $to_info) |
| 930 |
{ |
| 931 |
$tot_days = 0; |
| 932 |
if (!is_array($from_info) || !is_array($to_info) || $from_info[0] > $to_info[0]) { |
| 933 |
return $tot_days; |
| 934 |
} |
| 935 |
|
| 936 |
if (date('Y-m-d', $from_info[0]) == date('Y-m-d', $to_info[0])) { |
| 937 |
return 1; |
| 938 |
} |
| 939 |
|
| 940 |
while ($from_info[0] <= $to_info[0]) { |
| 941 |
$tot_days++; |
| 942 |
|
| 943 |
// next date |
| 944 |
$from_info = getdate(mktime(0, 0, 0, $from_info['mon'], ($from_info['mday'] + 1), $from_info['year'])); |
| 945 |
} |
| 946 |
|
| 947 |
return $tot_days; |
| 948 |
|
| 949 |
} |
| 950 |
|
| 951 |
/** |
| 952 |
* Registers the name to give to the CSV file being exported. |
| 953 |
* |
| 954 |
* @return void |
| 955 |
* |
| 956 |
* @since 1.16.1 (J) - 1.6.1 (WP) |
| 957 |
*/ |
| 958 |
private function registerExportCSVFileName() |
| 959 |
{ |
| 960 |
$pfromdate = VikRequest::getString('fromdate', '', 'request'); |
| 961 |
$ptodate = VikRequest::getString('todate', '', 'request'); |
| 962 |
|
| 963 |
$report_extraname = ''; |
| 964 |
$pchannel = VikRequest::getString('channel', '', 'request'); |
| 965 |
if (strlen($pchannel)) { |
| 966 |
// set channel name for exported file |
| 967 |
if ($pchannel == '-1') { |
| 968 |
$report_extraname = JText::translate('VBORDFROMSITE'); |
| 969 |
} else { |
| 970 |
$report_extraname = $pchannel; |
| 971 |
} |
| 972 |
} |
| 973 |
|
| 974 |
$this->setExportCSVFileName($this->reportName . (!empty($report_extraname) ? '-' . $report_extraname : '') . '-' . str_replace('/', '_', $pfromdate) . '-' . str_replace('/', '_', $ptodate) . '.csv'); |
| 975 |
} |
| 976 |
|
| 977 |
/** |
| 978 |
* Returns the Chart title. |
| 979 |
* |
| 980 |
* @return string the title of the Chart |
| 981 |
*/ |
| 982 |
public function getChartTitle() |
| 983 |
{ |
| 984 |
$name_parts = array($this->reportName, $this->chartTitle); |
| 985 |
|
| 986 |
$pchannel = VikRequest::getString('channel', '', 'request'); |
| 987 |
if (strlen($pchannel)) { |
| 988 |
// push channel name in report |
| 989 |
if ($pchannel == '-1') { |
| 990 |
array_push($name_parts, JText::translate('VBORDFROMSITE')); |
| 991 |
} else { |
| 992 |
array_push($name_parts, $pchannel); |
| 993 |
} |
| 994 |
} |
| 995 |
|
| 996 |
return implode(' - ', $name_parts); |
| 997 |
} |
| 998 |
|
| 999 |
/** |
| 1000 |
* This report can render a Chart. Main method called to whoever |
| 1001 |
* needs to access the Chart data rendered by this report. |
| 1002 |
* Defines some properties and accepts instructions through the arg. |
| 1003 |
* |
| 1004 |
* @param mixed $data null or mixed for requested Chart data. |
| 1005 |
* |
| 1006 |
* @return string the HTML of the canvas element. |
| 1007 |
*/ |
| 1008 |
public function getChart($data = null) |
| 1009 |
{ |
| 1010 |
if (!count($this->rows) && !$this->getReportData()) { |
| 1011 |
return ''; |
| 1012 |
} |
| 1013 |
|
| 1014 |
// find the dataset label depending on the active sorting (i.e. "occupancy") |
| 1015 |
$dataset_label = ''; |
| 1016 |
foreach ($this->cols as $col) { |
| 1017 |
if ($col['key'] == $this->defaultKeySort) { |
| 1018 |
$dataset_label = $col['label']; |
| 1019 |
break; |
| 1020 |
} |
| 1021 |
} |
| 1022 |
if (empty($dataset_label)) { |
| 1023 |
// ordering column label not found |
| 1024 |
return ''; |
| 1025 |
} |
| 1026 |
// set Chart title |
| 1027 |
$this->chartTitle = $dataset_label; |
| 1028 |
|
| 1029 |
// push data values for the requested key depending on the active sorting |
| 1030 |
$chart_labels = array(); |
| 1031 |
$chart_data = array(); |
| 1032 |
$chart_indexes = array(); |
| 1033 |
$counter = 0; |
| 1034 |
$max_points = 10; |
| 1035 |
foreach ($this->rows as $row) { |
| 1036 |
foreach ($row as $ind => $field) { |
| 1037 |
if ($counter >= $max_points) { |
| 1038 |
break 2; |
| 1039 |
} |
| 1040 |
if ($ind === 0) { |
| 1041 |
// make sure this field is not the same as the active sorting |
| 1042 |
if ($field['key'] == $this->defaultKeySort) { |
| 1043 |
// we cannot build the Chart, or the X and Y would have the same values |
| 1044 |
return ''; |
| 1045 |
} |
| 1046 |
// the first column is the label |
| 1047 |
array_push($chart_labels, (isset($field['callback']) && is_callable($field['callback']) ? $field['callback']($field['value']) : $field['value'])); |
| 1048 |
// save the raw value for this index |
| 1049 |
array_push($chart_indexes, $field['value']); |
| 1050 |
} |
| 1051 |
if ($field['key'] != $this->defaultKeySort) { |
| 1052 |
// we do not care about this column |
| 1053 |
continue; |
| 1054 |
} |
| 1055 |
array_push($chart_data, $field['value']); |
| 1056 |
$counter++; |
| 1057 |
} |
| 1058 |
} |
| 1059 |
if (!count($chart_labels) || count($chart_labels) != count($chart_data)) { |
| 1060 |
// missing or invalid chart values |
| 1061 |
return ''; |
| 1062 |
} |
| 1063 |
|
| 1064 |
// check whether a slice of the data was requested through the depth property |
| 1065 |
if (is_array($data) && isset($data['depth']) && $data['depth'] > 0 && count($chart_labels) >= $data['depth']) { |
| 1066 |
// get a slice of the labels and data |
| 1067 |
$chart_labels = array_slice($chart_labels, 0, $data['depth']); |
| 1068 |
$chart_data = array_slice($chart_data, 0, $data['depth']); |
| 1069 |
} else { |
| 1070 |
// sort labels and data by time ascending, to obtain a readable line chart |
| 1071 |
asort($chart_indexes); |
| 1072 |
$sorted_labels = array(); |
| 1073 |
$sorted_data = array(); |
| 1074 |
foreach ($chart_indexes as $k => $v) { |
| 1075 |
array_push($sorted_labels, $chart_labels[$k]); |
| 1076 |
array_push($sorted_data, $chart_data[$k]); |
| 1077 |
} |
| 1078 |
$chart_labels = $sorted_labels; |
| 1079 |
$chart_data = $sorted_data; |
| 1080 |
} |
| 1081 |
|
| 1082 |
// the canvas element ID and tag |
| 1083 |
$canvas_id = 'vbo-report-chart-canvas'; |
| 1084 |
$canvas_html = '<canvas id="' . $canvas_id . '"></canvas>'; |
| 1085 |
|
| 1086 |
// additional Chart properties |
| 1087 |
$chart_type = is_array($data) && !empty($data['type']) ? $data['type'] : 'line'; |
| 1088 |
$chart_colors = array( |
| 1089 |
'backgroundColor' => 'rgba(34,72,93,0.2)', |
| 1090 |
'borderColor' => 'rgba(34,72,93,1)', |
| 1091 |
'pieBackgroundColor' => '["rgba(34,72,93,1)"]', |
| 1092 |
'pieHoverBorderColor' => '["rgba(34,72,93,0.9)"]', |
| 1093 |
); |
| 1094 |
|
| 1095 |
// add a new label for the "free rooms" if pie, count = 1 and occupancy filtering |
| 1096 |
$pie_tooltip_format = ''; |
| 1097 |
if ($chart_type != 'line' && count($chart_data) === 1 && is_array($data) && isset($data['keys'])) { |
| 1098 |
if (((is_array($data['keys']) && in_array('occupancy', $data['keys'])) || $data['keys'] == 'occupancy')) { |
| 1099 |
// push "free rooms" label and data to complete the doughnut chart |
| 1100 |
$chart_labels = array( |
| 1101 |
JText::translate('VBOROOMSOCCUPANCY'), |
| 1102 |
JText::translate('VBOROOMSUNSOLD'), |
| 1103 |
); |
| 1104 |
array_push($chart_data, round((100 - $chart_data[0]), 2)); |
| 1105 |
$chart_colors['pieBackgroundColor'] = json_encode(array( |
| 1106 |
'rgba(34,72,93,1)', |
| 1107 |
'rgba(77,152,198,1)', |
| 1108 |
)); |
| 1109 |
$chart_colors['pieHoverBorderColor'] = json_encode(array( |
| 1110 |
'rgba(34,72,93,0.9)', |
| 1111 |
'rgba(77,152,198,0.9)', |
| 1112 |
)); |
| 1113 |
$pie_tooltip_format = ' + " %"'; |
| 1114 |
} |
| 1115 |
} |
| 1116 |
|
| 1117 |
/** |
| 1118 |
* Set some Chart properties that can be accessed through getProperty() |
| 1119 |
* |
| 1120 |
* @see getProperty() |
| 1121 |
*/ |
| 1122 |
$this->chartJsLabels = $chart_labels; |
| 1123 |
$this->chartJsDataSetLabel = $dataset_label; |
| 1124 |
$this->chartJsColors = $chart_colors; |
| 1125 |
$this->chartJsData = $chart_data; |
| 1126 |
// |
| 1127 |
|
| 1128 |
if (!empty($this->chartScript)) { |
| 1129 |
// the script has already been set, return just the HTML |
| 1130 |
return $canvas_html; |
| 1131 |
} |
| 1132 |
|
| 1133 |
// prepare the necessary script to render the Chart |
| 1134 |
$this->chartScript .= 'jQuery(function() {' . "\n"; |
| 1135 |
$this->chartScript .= 'var vbo_report_ctx = document.getElementById("' . $canvas_id . '").getContext("2d");' . "\n"; |
| 1136 |
$this->chartScript .= ' |
| 1137 |
var vboReportType = "' . $chart_type . '"; |
| 1138 |
var vboReportLineData = { |
| 1139 |
labels: ' . json_encode($this->chartJsLabels) . ', |
| 1140 |
datasets: [{ |
| 1141 |
label: "' . addslashes($this->chartJsDataSetLabel) . '", |
| 1142 |
backgroundColor: "' . $this->chartJsColors['backgroundColor'] . '", |
| 1143 |
borderColor: "' . $this->chartJsColors['borderColor'] . '", |
| 1144 |
data: ' . json_encode($this->chartJsData) . ', |
| 1145 |
}], |
| 1146 |
}; |
| 1147 |
// since we are inside a callback, the var vboReportPieData must declared globally |
| 1148 |
window[\'vboReportPieData\'] = { |
| 1149 |
labels: ' . json_encode($this->chartJsLabels) . ', |
| 1150 |
datasets: [{ |
| 1151 |
label: "' . addslashes($this->chartJsDataSetLabel) . '", |
| 1152 |
backgroundColor: ' . $this->chartJsColors['pieBackgroundColor'] . ', |
| 1153 |
hoverBorderColor: ' . $this->chartJsColors['pieHoverBorderColor'] . ', |
| 1154 |
data: ' . json_encode($this->chartJsData) . ', |
| 1155 |
}], |
| 1156 |
}; |
| 1157 |
var vboReportLineOptions = { |
| 1158 |
responsive: true, |
| 1159 |
legend: { |
| 1160 |
display: false, |
| 1161 |
}, |
| 1162 |
legendCallback: function (chart) { |
| 1163 |
// Return the HTML string here. |
| 1164 |
var text = []; |
| 1165 |
text.push("<ul class=\"chart-line-legend\">"); |
| 1166 |
for (var i = 0; i < chart.data.datasets.length; i++) { |
| 1167 |
text.push("<li>"); |
| 1168 |
text.push("<span class=\"legend-entry\" style=\"background-color: " + chart.data.datasets[i].backgroundColor + "\"></span>"); |
| 1169 |
text.push("<span class=\"legend-label\">" + chart.data.datasets[i].label + "</span>"); |
| 1170 |
text.push("</li>"); |
| 1171 |
} |
| 1172 |
text.push("</ul>"); |
| 1173 |
return text.join(""); |
| 1174 |
}, |
| 1175 |
}; |
| 1176 |
var vboReportPieOptions = { |
| 1177 |
responsive: true, |
| 1178 |
legend: { |
| 1179 |
display: false, |
| 1180 |
}, |
| 1181 |
legendCallback: function (chart) { |
| 1182 |
// Return the HTML string here. |
| 1183 |
var text = []; |
| 1184 |
text.push("<ul class=\"chart-line-legend chart-pie-legend\">"); |
| 1185 |
for (var i = 0; i < chart.data.labels.length; i++) { |
| 1186 |
text.push("<li>"); |
| 1187 |
text.push("<span class=\"legend-entry\" style=\"background-color: " + chart.data.datasets[0].backgroundColor[i] + "\"></span>"); |
| 1188 |
text.push("<span class=\"legend-label\">" + chart.data.labels[i] + "</span>"); |
| 1189 |
text.push("</li>"); |
| 1190 |
} |
| 1191 |
text.push("</ul>"); |
| 1192 |
return text.join(""); |
| 1193 |
}, |
| 1194 |
tooltips: { |
| 1195 |
callbacks: { |
| 1196 |
// format the tooltip text displayed when hovering a point |
| 1197 |
label: function(tooltipItem, data) { |
| 1198 |
// keep default label |
| 1199 |
var label = data.labels[tooltipItem.index] || ""; |
| 1200 |
if (label) { |
| 1201 |
label += ": "; |
| 1202 |
} |
| 1203 |
label += data.datasets[tooltipItem.datasetIndex].data[tooltipItem.index]' . $pie_tooltip_format . '; |
| 1204 |
return " " + label; |
| 1205 |
}, |
| 1206 |
}, |
| 1207 |
}, |
| 1208 |
}; |
| 1209 |
var vboReportChart = new Chart(vbo_report_ctx, { |
| 1210 |
type: vboReportType, |
| 1211 |
data: (vboReportType == "line" ? vboReportLineData : vboReportPieData), |
| 1212 |
options: (vboReportType == "line" ? vboReportLineOptions : vboReportPieOptions), |
| 1213 |
}); |
| 1214 |
jQuery("#' . $canvas_id . '").parent().append(vboReportChart.generateLegend()); |
| 1215 |
jQuery("#' . $canvas_id . '").on("vbo_update_report_chart", function() { |
| 1216 |
jQuery(".chart-line-legend").remove(); |
| 1217 |
vboReportChart.update(); |
| 1218 |
jQuery("#' . $canvas_id . '").parent().append(vboReportChart.generateLegend()); |
| 1219 |
});'; |
| 1220 |
$this->chartScript .= '});' . "\n"; |
| 1221 |
|
| 1222 |
// set the necessary script |
| 1223 |
$this->setScript($this->chartScript); |
| 1224 |
|
| 1225 |
// return the HTML to render the chart |
| 1226 |
return $canvas_html; |
| 1227 |
} |
| 1228 |
|
| 1229 |
/** |
| 1230 |
* Returns an array of information (meta boxes) about the Chart. |
| 1231 |
* Information can be filtered by different positions. |
| 1232 |
* |
| 1233 |
* @param mixed $position null, top, right or bottom. |
| 1234 |
* @param mixed $data null or an associative array of values. |
| 1235 |
* |
| 1236 |
* @return array the list of meta data for the position. |
| 1237 |
*/ |
| 1238 |
public function getChartMetaData($position = null, $data = null) |
| 1239 |
{ |
| 1240 |
if (!count($this->rows) && !$this->getReportData()) { |
| 1241 |
return array(); |
| 1242 |
} |
| 1243 |
|
| 1244 |
if (!count($this->chartMetaData)) { |
| 1245 |
// prepare the meta data only once |
| 1246 |
$this->generateChartMetaData($data); |
| 1247 |
} |
| 1248 |
|
| 1249 |
if (!empty($position)) { |
| 1250 |
return isset($this->chartMetaData[$position]) ? $this->chartMetaData[$position] : array(); |
| 1251 |
} |
| 1252 |
|
| 1253 |
if (!count($this->chartMetaData['top']) && !count($this->chartMetaData['right']) && !count($this->chartMetaData['bottom'])) { |
| 1254 |
// no positions requested and no count, return an empty array |
| 1255 |
return array(); |
| 1256 |
} |
| 1257 |
|
| 1258 |
return $this->chartMetaData; |
| 1259 |
} |
| 1260 |
|
| 1261 |
/** |
| 1262 |
* Prepares the Chart meta data. |
| 1263 |
* |
| 1264 |
* @param mixed $data null or an associative array of values. |
| 1265 |
* |
| 1266 |
* @return void |
| 1267 |
*/ |
| 1268 |
private function generateChartMetaData($data = null) |
| 1269 |
{ |
| 1270 |
// reset container |
| 1271 |
$this->chartMetaData = array(); |
| 1272 |
|
| 1273 |
if (!count($this->rows)) { |
| 1274 |
return; |
| 1275 |
} |
| 1276 |
|
| 1277 |
// whether custom data has been requested |
| 1278 |
$is_custom_data = (is_array($data) && isset($data['keys']) && is_array($data['keys'])); |
| 1279 |
$good_threshold = is_array($data) && isset($data['threshold']) ? (int)$data['threshold'] : 60; |
| 1280 |
|
| 1281 |
// currency symbol to format some data |
| 1282 |
$currency_symb = VikBooking::getCurrencySymb(); |
| 1283 |
|
| 1284 |
// all meta box containers |
| 1285 |
$meta_top = array(); |
| 1286 |
$meta_right = array(); |
| 1287 |
$meta_bottom = array(); |
| 1288 |
|
| 1289 |
// collect data |
| 1290 |
$dates_pool = array(); |
| 1291 |
$revenue_pool = array(); |
| 1292 |
$tbookings_pool = array(); |
| 1293 |
$occupancy_pool = array(); |
| 1294 |
$nightsbkd_pool = array(); |
| 1295 |
$nightsbkd_totl = array(); |
| 1296 |
foreach ($this->rows as $ind => $row) { |
| 1297 |
foreach ($row as $field) { |
| 1298 |
if ($field['key'] == 'day') { |
| 1299 |
$dates_pool[$ind] = (isset($field['callback']) && is_callable($field['callback']) ? $field['callback']($field['value']) : $field['value']); |
| 1300 |
} elseif ($field['key'] == 'revenue') { |
| 1301 |
$revenue_pool[$ind] = $field['value']; |
| 1302 |
} elseif ($field['key'] == 'tot_bookings') { |
| 1303 |
$tbookings_pool[$ind] = $field['value']; |
| 1304 |
} elseif ($field['key'] == 'occupancy') { |
| 1305 |
$occupancy_pool[$ind] = $field['value']; |
| 1306 |
} elseif ($field['key'] == 'nights_booked') { |
| 1307 |
$nightsbkd_pool[$ind] = $field['value']; |
| 1308 |
$nightsbkd_totl[$ind] = (isset($field['callback']) && is_callable($field['callback']) ? $field['callback']($field['value']) : $field['value']); |
| 1309 |
} |
| 1310 |
} |
| 1311 |
} |
| 1312 |
|
| 1313 |
// get min/max data |
| 1314 |
$min_occ = min($occupancy_pool); |
| 1315 |
$max_occ = max($occupancy_pool); |
| 1316 |
$min_occ_dt = $dates_pool[array_search($min_occ, $occupancy_pool)]; |
| 1317 |
$max_occ_dt = $dates_pool[array_search($max_occ, $occupancy_pool)]; |
| 1318 |
$min_tbook = min($tbookings_pool); |
| 1319 |
$max_tbook = max($tbookings_pool); |
| 1320 |
$min_tbook_dt = $dates_pool[array_search($min_tbook, $tbookings_pool)]; |
| 1321 |
$max_tbook_dt = $dates_pool[array_search($max_tbook, $tbookings_pool)]; |
| 1322 |
$max_revenue = max($revenue_pool); |
| 1323 |
$max_revenue_dt = $dates_pool[array_search($max_revenue, $revenue_pool)]; |
| 1324 |
$max_nightsbk = max($nightsbkd_pool); |
| 1325 |
$max_nightsbk_dt = $nightsbkd_pool[array_search($max_nightsbk, $nightsbkd_pool)]; |
| 1326 |
// override max nights booked with value formatted by the callback |
| 1327 |
$max_nightsbk = $nightsbkd_totl[array_search($max_nightsbk, $nightsbkd_pool)]; |
| 1328 |
// |
| 1329 |
|
| 1330 |
// populate Chart meta boxes |
| 1331 |
$occ_lbl = trim(str_replace('%', '', JText::translate('VBOREPORTREVENUEPOCC'))); |
| 1332 |
array_push($meta_top, array( |
| 1333 |
'key' => 'occupancy', |
| 1334 |
'label' => $occ_lbl, |
| 1335 |
'value' => $max_occ . ' %', |
| 1336 |
'class' => ($is_custom_data && $max_occ < $good_threshold ? 'vbo-report-chart-meta-min' : 'vbo-report-chart-meta-max'), |
| 1337 |
'descr' => ($is_custom_data ? '' : $max_occ_dt), |
| 1338 |
)); |
| 1339 |
if ($min_occ != $max_occ) { |
| 1340 |
array_push($meta_top, array( |
| 1341 |
'key' => 'occupancy', |
| 1342 |
'label' => $occ_lbl, |
| 1343 |
'value' => $min_occ . ' %', |
| 1344 |
'class' => 'vbo-report-chart-meta-min', |
| 1345 |
'descr' => ($is_custom_data ? '' : $min_occ_dt), |
| 1346 |
)); |
| 1347 |
} |
| 1348 |
array_push($meta_right, array( |
| 1349 |
'key' => 'tot_bookings', |
| 1350 |
'label' => JText::translate('VBOREPORTREVENUETOTB'), |
| 1351 |
'value' => $max_tbook, |
| 1352 |
'class' => ($is_custom_data && $max_occ < $good_threshold ? 'vbo-report-chart-meta-min' : 'vbo-report-chart-meta-max'), |
| 1353 |
'descr' => ($is_custom_data ? '' : $max_tbook_dt), |
| 1354 |
)); |
| 1355 |
if ($min_tbook != $max_tbook) { |
| 1356 |
array_push($meta_right, array( |
| 1357 |
'key' => 'tot_bookings', |
| 1358 |
'label' => JText::translate('VBOREPORTREVENUETOTB'), |
| 1359 |
'value' => $min_tbook, |
| 1360 |
'class' => 'vbo-report-chart-meta-min', |
| 1361 |
'descr' => ($is_custom_data ? '' : $min_tbook_dt), |
| 1362 |
)); |
| 1363 |
} |
| 1364 |
if (!is_array($data) || ($is_custom_data && in_array('revenue', $data['keys']))) { |
| 1365 |
array_push($meta_bottom, array( |
| 1366 |
'key' => 'revenue', |
| 1367 |
'label' => JText::translate('VBOREPORTREVENUEREV'), |
| 1368 |
'value' => $currency_symb . ' ' . VikBooking::numberFormat($max_revenue), |
| 1369 |
'class' => 'vbo-report-chart-meta-max', |
| 1370 |
'descr' => ($is_custom_data ? '' : $max_revenue_dt), |
| 1371 |
)); |
| 1372 |
} |
| 1373 |
if ($is_custom_data && in_array('nights_booked', $data['keys'])) { |
| 1374 |
array_push($meta_bottom, array( |
| 1375 |
'key' => 'nights_booked', |
| 1376 |
'label' => JText::translate('VBOGRAPHTOTNIGHTSLBL'), |
| 1377 |
'value' => $max_nightsbk, |
| 1378 |
'class' => ($is_custom_data && $max_occ < $good_threshold ? 'vbo-report-chart-meta-min' : 'vbo-report-chart-meta-max'), |
| 1379 |
'descr' => ($is_custom_data ? '' : $max_nightsbk_dt), |
| 1380 |
)); |
| 1381 |
} |
| 1382 |
|
| 1383 |
// build container for all positions |
| 1384 |
$this->chartMetaData = array( |
| 1385 |
'top' => $meta_top, |
| 1386 |
'right' => $meta_right, |
| 1387 |
'bottom' => $meta_bottom, |
| 1388 |
); |
| 1389 |
} |
| 1390 |
} |
| 1391 |
|
