reportFile = basename(__FILE__, '.php');
$this->reportName = JText::translate('VBO_GUESTS_REGISTRATION_DATA');
$this->reportFilters = [];
$this->cols = [];
$this->rows = [];
$this->footerRow = [];
$this->registerExportCSVFileName();
parent::__construct();
}
/**
* Returns the name of this report.
*
* @return string
*/
public function getName()
{
return $this->reportName;
}
/**
* Returns the name of this file without .php.
*
* @return string
*/
public function getFileName()
{
return $this->reportFile;
}
/**
* Returns the filters of this report.
*
* @return array
*/
public function getFilters()
{
if ($this->reportFilters) {
//do not run this method twice, as it could load JS and CSS files.
return $this->reportFilters;
}
// get VBO Application Object
$vbo_app = VikBooking::getVboApplication();
// load the jQuery UI Datepicker
$this->loadDatePicker();
// From Date Filter
$filter_opt = array(
'label' => '',
'html' => '',
'type' => 'calendar',
'name' => 'fromdate'
);
array_push($this->reportFilters, $filter_opt);
// To Date Filter
$filter_opt = array(
'label' => '',
'html' => '',
'type' => 'calendar',
'name' => 'todate'
);
array_push($this->reportFilters, $filter_opt);
// Listings Filter
$filter_opt = array(
'label' => '',
'html' => '' . $vbo_app->renderElementsDropDown([
'id' => 'listingsfilt',
'elements' => 'listings',
'placeholder' => JText::translate('VBO_LISTINGS'),
'allow_clear' => 1,
'attributes' => [
'name' => 'listings[]',
'multiple' => 'multiple',
],
'selected_values' => (array) JFactory::getApplication()->input->get('listings', [], 'array'),
]) . '',
'type' => 'select',
'multiple' => true,
'name' => 'listings',
);
array_push($this->reportFilters, $filter_opt);
// get minimum check-in and maximum check-out for dates filters
$df = $this->getDateFormat();
$mincheckin = 0;
$maxcheckout = 0;
$q = "SELECT MIN(`checkin`) AS `mincheckin`, MAX(`checkout`) AS `maxcheckout` FROM `#__vikbooking_orders` WHERE `status`='confirmed' AND `closure`=0;";
$this->dbo->setQuery($q);
$this->dbo->execute();
if ($this->dbo->getNumRows()) {
$data = $this->dbo->loadAssoc();
if (!empty($data['mincheckin']) && !empty($data['maxcheckout'])) {
$mincheckin = $data['mincheckin'];
$maxcheckout = $data['maxcheckout'];
}
}
// calendars setup
$pfromdate = VikRequest::getString('fromdate', '', 'request');
$ptodate = VikRequest::getString('todate', '', 'request');
$js = 'jQuery(function() {
jQuery(".vbo-report-datepicker:input").datepicker({
'.(!empty($mincheckin) ? 'minDate: "'.date($df, $mincheckin).'", ' : '').'
'.(!empty($maxcheckout) ? 'maxDate: "'.date($df, $maxcheckout).'", ' : '').'
'.(!empty($mincheckin) && !empty($maxcheckout) ? 'yearRange: "'.(date('Y', $mincheckin)).':'.date('Y', $maxcheckout).'", changeMonth: true, changeYear: true, ' : '').'
dateFormat: "'.$this->getDateFormat('jui').'",
onSelect: vboReportCheckDates
});
'.(!empty($pfromdate) ? 'jQuery(".vbo-report-datepicker-from").datepicker("setDate", "'.$pfromdate.'");' : '').'
'.(!empty($ptodate) ? 'jQuery(".vbo-report-datepicker-to").datepicker("setDate", "'.$ptodate.'");' : '').'
});
function vboReportCheckDates(selectedDate, inst) {
if (selectedDate === null || inst === null) {
return;
}
var cur_from_date = jQuery(this).val();
if (jQuery(this).hasClass("vbo-report-datepicker-from") && cur_from_date.length) {
var nowstart = jQuery(this).datepicker("getDate");
var nowstartdate = new Date(nowstart.getTime());
jQuery(".vbo-report-datepicker-to").datepicker("option", {minDate: nowstartdate});
}
}';
$this->setScript($js);
return $this->reportFilters;
}
/**
* Loads the report data from the DB.
* Returns true in case of success, false otherwise.
* Sets the columns and rows for the report to be displayed.
*
* @return boolean
*/
public function getReportData()
{
if ($this->getError()) {
// export functions may set errors rather than exiting the process, and the View may continue the execution to attempt to render the report.
return false;
}
if ($this->rows) {
// method must have run already
return true;
}
// get the possibly injected report options
$options = $this->getReportOptions();
// injected options will replace request variables, if any
$opt_fromdate = $options->get('fromdate', '');
$opt_todate = $options->get('todate', '');
// input fields and other vars
$pfromdate = $opt_fromdate ?: VikRequest::getString('fromdate', '', 'request');
$ptodate = $opt_todate ?: VikRequest::getString('todate', '', 'request');
$pkrsort = VikRequest::getString('krsort', $this->defaultKeySort, 'request');
$pkrsort = empty($pkrsort) ? $this->defaultKeySort : $pkrsort;
$pkrorder = VikRequest::getString('krorder', $this->defaultKeyOrder, 'request');
$pkrorder = empty($pkrorder) ? $this->defaultKeyOrder : $pkrorder;
$pkrorder = $pkrorder == 'DESC' ? 'DESC' : 'ASC';
$plistings = ((array) VikRequest::getVar('listings', array())) ?: ((array) $options->get('listings', []));
$plistings = array_filter(array_map('intval', $plistings));
$currency_symb = VikBooking::getCurrencySymb();
$df = $this->getDateFormat();
$datesep = VikBooking::getDateSeparator();
if (empty($ptodate)) {
$ptodate = $pfromdate;
}
// get date timestamps
$from_ts = VikBooking::getDateTimestamp($pfromdate, 0, 0);
$to_ts = VikBooking::getDateTimestamp($ptodate, 23, 59, 59);
if (empty($pfromdate) || empty($from_ts) || empty($to_ts) || $from_ts > $to_ts) {
$this->setError(JText::translate('VBOREPORTSERRNODATES'));
return false;
}
// query to obtain the records (all check-ins within the dates filter)
$q = $this->dbo->getQuery(true)
->select([
$this->dbo->qn('o.id'),
$this->dbo->qn('o.custdata'),
$this->dbo->qn('o.ts'),
$this->dbo->qn('o.days'),
$this->dbo->qn('o.checkin'),
$this->dbo->qn('o.checkout'),
$this->dbo->qn('o.totpaid'),
$this->dbo->qn('o.roomsnum'),
$this->dbo->qn('o.total'),
$this->dbo->qn('o.idorderota'),
$this->dbo->qn('o.channel'),
$this->dbo->qn('o.country'),
$this->dbo->qn('o.custmail'),
$this->dbo->qn('o.phone'),
$this->dbo->qn('or.idorder'),
$this->dbo->qn('or.idroom'),
$this->dbo->qn('or.adults'),
$this->dbo->qn('or.children'),
$this->dbo->qn('or.t_first_name'),
$this->dbo->qn('or.t_last_name'),
$this->dbo->qn('or.cust_cost'),
$this->dbo->qn('or.cust_idiva'),
$this->dbo->qn('or.extracosts'),
$this->dbo->qn('or.room_cost'),
$this->dbo->qn('co.idcustomer'),
$this->dbo->qn('co.pax_data'),
$this->dbo->qn('c.first_name'),
$this->dbo->qn('c.last_name'),
$this->dbo->qn('c.country', 'customer_country'),
$this->dbo->qn('c.address'),
$this->dbo->qn('c.doctype'),
$this->dbo->qn('c.docnum'),
$this->dbo->qn('c.gender'),
$this->dbo->qn('c.bdate'),
$this->dbo->qn('c.pbirth'),
])
->from($this->dbo->qn('#__vikbooking_orders', 'o'))
->leftJoin($this->dbo->qn('#__vikbooking_ordersrooms', 'or') . ' ON ' . $this->dbo->qn('or.idorder') . ' = ' . $this->dbo->qn('o.id'))
->leftJoin($this->dbo->qn('#__vikbooking_customers_orders', 'co') . ' ON ' . $this->dbo->qn('co.idorder') . ' = ' . $this->dbo->qn('o.id'))
->leftJoin($this->dbo->qn('#__vikbooking_customers', 'c') . ' ON ' . $this->dbo->qn('c.id') . ' = ' . $this->dbo->qn('co.idcustomer'))
->where($this->dbo->qn('o.status') . ' = ' . $this->dbo->q('confirmed'))
->where($this->dbo->qn('o.closure') . ' = 0')
// fetch all bookings with check-in, check-out or reservation date within date filters
->where('(' . $this->dbo->qn('o.checkin') . ' BETWEEN ' . $from_ts . ' AND ' . $to_ts . ')')
// fetch all bookings with pre-check-in information available
->where($this->dbo->qn('co.pax_data') . ' IS NOT NULL')
->order($this->dbo->qn('o.checkin') . ' ASC')
->order($this->dbo->qn('o.id') . ' ASC')
->order($this->dbo->qn('or.id') . ' ASC');
if ($plistings) {
$q->where($this->dbo->qn('or.idroom') . ' IN (' . implode(', ', $plistings) . ')');
}
$this->dbo->setQuery($q);
$records = $this->dbo->loadAssocList();
if (!$records) {
// set error message
$this->setError(JText::translate('VBOREPORTSERRNORESERV'));
// abort
return false;
}
// nest records with multiple rooms booked inside sub-array, and build all registration keys
$bookings = [];
$registration_keys = [];
foreach ($records as $v) {
$v['pax_data'] = (array) json_decode($v['pax_data'], true);
foreach ($v['pax_data'] as $pax_room) {
foreach ($pax_room as $pax_guest) {
$registration_keys = array_merge($registration_keys, array_keys((array) $pax_guest));
}
}
if (!isset($bookings[$v['id']])) {
$bookings[$v['id']] = [];
}
array_push($bookings[$v['id']], $v);
}
// make the registration keys unique
$registration_keys = array_values(array_unique(array_filter($registration_keys)));
// free memory up
unset($records);
// define the columns of the report
$this->cols = [
// id booking
[
'key' => 'idbooking',
'label' => 'ID',
],
];
foreach ($registration_keys as $registration_key) {
$this->cols[] = [
'key' => $registration_key,
'label' => ucwords(str_replace('_', ' ', $registration_key)),
];
}
// loop over the dates of the report to build the rows
foreach ($bookings as $gbook) {
if (!is_array($gbook[0]['pax_data'] ?? null)) {
continue;
}
foreach ($gbook[0]['pax_data'] as $pax_room) {
foreach ($pax_room as $pax_guest) {
// prepare row record for this room-guest
$insert_row = [];
// ID booking
$insert_row[] = [
'key' => 'idbooking',
'callback' => function($val) {
return ' ' . $val . '';
},
'no_export_callback' => 1,
'value' => $gbook[0]['id'],
];
foreach ($registration_keys as $registration_key) {
// build guest key value
$guest_value = is_scalar($pax_guest[$registration_key] ?? null) ? $pax_guest[$registration_key] : null;
if (is_object($pax_guest[$registration_key] ?? null) || is_array($pax_guest[$registration_key] ?? null)) {
$guest_value = json_encode($pax_guest[$registration_key]);
}
// push guest registration key value
$insert_row[] = [
'key' => $registration_key,
'callback' => function($val) {
if (is_string($val) && strlen($val) > 24) {
return mb_substr($val, 0, 24, 'UTF-8') . '...';
}
return $val;
},
'no_export_callback' => 1,
'value' => $guest_value,
];
}
// push fields in the rows array as a new row
$this->rows[] = $insert_row;
}
}
}
return true;
}
/**
* Registers the name to give to the CSV file being exported.
*
* @return void
*/
private function registerExportCSVFileName()
{
$pfromdate = VikRequest::getString('fromdate', '', 'request');
$ptodate = VikRequest::getString('todate', '', 'request');
$this->setExportCSVFileName($this->reportName . '-' . str_replace('/', '_', $pfromdate) . '-' . str_replace('/', '_', $ptodate) . '.csv');
}
}