0
*/
private $debug;
/**
* Class constructor should define the name of the report and
* other vars. Call the parent constructor to define the DB object.
*/
public function __construct()
{
$this->reportFile = basename(__FILE__, '.php');
$this->reportName = JText::translate('VBOREPORT'.strtoupper(str_replace('_', '', $this->reportFile)));
$this->reportFilters = array();
$this->cols = array();
$this->rows = array();
$this->footerRow = array();
$this->debug = (VikRequest::getInt('e4j_debug', 0, 'request') > 0);
$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 (count($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);
//Room ID filter
$pidroom = VikRequest::getInt('idroom', '', 'request');
$all_rooms = $this->getRooms();
$rooms = array();
foreach ($all_rooms as $room) {
$rooms[$room['id']] = $room['name'];
}
if (count($rooms)) {
$rooms_sel_html = $vbo_app->getNiceSelect($rooms, $pidroom, 'idroom', JText::translate('VBOSTATSALLROOMS'), JText::translate('VBOSTATSALLROOMS'), '', '', 'idroom');
$filter_opt = array(
'label' => '',
'html' => $rooms_sel_html,
'type' => 'select',
'name' => 'idroom'
);
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'];
}
}
//
//jQuery code for the datepicker calendars and select2
$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).'", ' : '').'
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 (strlen($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;
}
//Input fields and other vars
$pfromdate = VikRequest::getString('fromdate', '', 'request');
$ptodate = VikRequest::getString('todate', '', 'request');
$pidroom = VikRequest::getInt('idroom', '', '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';
$currency_symb = VikBooking::getCurrencySymb();
$df = $this->getDateFormat();
$datesep = VikBooking::getDateSeparator();
if (empty($ptodate)) {
$ptodate = $pfromdate;
}
//Get dates 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)) {
$this->setError(JText::translate('VBOREPORTSERRNODATES'));
return false;
}
/**
* We do not make a left join onto the orders roooms table
* or we may obtain multiple records with the query. Since we cannot
* group by booking ID as we need all history logs of type PU,
* we try to make a sub-query to concatenate all room IDs.
*/
// Query to obtain the records
$bookings = array();
$q = "SELECT `o`.`id`,`o`.`ts`,`o`.`days`,`o`.`checkin`,`o`.`checkout`,`o`.`totpaid`,`o`.`idpayment`,`o`.`roomsnum`,`o`.`total`,`o`.`idorderota`,`o`.`channel`,`o`.`country`,`o`.`tot_taxes`,".
"`o`.`tot_city_taxes`,`o`.`tot_fees`,`o`.`cmms`,`h`.`dt`,`h`.`data`, (SELECT GROUP_CONCAT(`or`.`idroom` SEPARATOR ';') FROM `#__vikbooking_ordersrooms` AS `or` WHERE `or`.`idorder`=`o`.`id`) AS `idrooms` ".
"FROM `#__vikbooking_orders` AS `o` LEFT JOIN `#__vikbooking_orderhistory` AS `h` ON `h`.`idorder`=`o`.`id` AND `h`.`type`='PU' ".
"WHERE `o`.`status`='confirmed' AND `o`.`closure`=0 AND `o`.`totpaid` > 0 ".
"AND ((`o`.`ts`>=".$from_ts." AND `o`.`ts`<=".$to_ts.") OR (`h`.`data` IS NOT NULL AND `h`.`dt`>=".$this->dbo->quote(date('Y-m-d H:i:s', $from_ts))." AND `h`.`dt`<=".$this->dbo->quote(date('Y-m-d H:i:s', $to_ts)).")) ".
"ORDER BY `o`.`ts` ASC, `h`.`dt` DESC;";
$this->dbo->setQuery($q);
$this->dbo->execute();
if ($this->dbo->getNumRows()) {
$bookings = $this->dbo->loadAssocList();
// apply room ID filter that was not used via SQL
if (!empty($pidroom)) {
foreach ($bookings as $k => $gbook) {
$roomids = explode(';', $gbook['idrooms']);
if (!in_array($pidroom, $roomids)) {
unset($bookings[$k]);
}
}
}
}
if (!count($bookings)) {
$this->setError(JText::translate('VBOREPORTSERRNORESERV'));
return false;
}
// Debug
// $this->setWarning('
'.print_r($bookings, true).'
');
//
//define the columns of the report
$this->cols = array(
//date
array(
'key' => 'paymeth',
'sortable' => 1,
'label' => JText::translate('VBPAYMENTMETHOD')
),
//rooms sold
array(
'key' => 'tot',
'attr' => array(
'class="center"'
),
'sortable' => 1,
'label' => JText::translate('VBLIBSIX')
),
//bookings affected
array(
'key' => 'ids',
'attr' => array(
'class="center"'
),
'sortable' => 0,
'ignore_export' => 0,
'label' => JText::translate('VBMENUTHREE')
),
);
$paystats = array();
$allbids = array();
// loop over the bookings to build the payment stats
foreach ($bookings as &$gbook) {
if (!in_array($gbook['id'], $allbids)) {
array_push($allbids, $gbook['id']);
}
$payname = null;
if (!empty($gbook['data'])) {
$paydata = json_decode($gbook['data']);
$payname = is_object($paydata) && isset($paydata->payment_method) ? $paydata->payment_method : $payname;
// update totpaid if history data available
if (is_object($paydata) && isset($paydata->amount_paid) && $paydata->amount_paid > 0) {
$gbook['totpaid'] = (float)$paydata->amount_paid;
}
} elseif (!empty($gbook['idpayment'])) {
if (strpos($gbook['idpayment'], '=') !== false) {
$parts = explode('=', $gbook['idpayment']);
$payname = $parts[1];
} else {
$payname = $gbook['idpayment'];
}
} elseif (!empty($gbook['channel']) && !empty($gbook['idorderota'])) {
$parts = explode('_', $gbook['channel']);
unset($parts[0]);
$payname = implode('_', $parts);
}
if (is_null($payname)) {
// unknown
$payname = JText::translate('VBOREPORTTOPCUNKNC');
}
// update reference
$gbook['payment_method'] = $payname;
// update stats for this payment method
if (!isset($paystats[$payname])) {
$paystats[$payname] = array(
'totpaid' => 0,
'bids' => array()
);
}
// increase total paid and push booking ID
$paystats[$payname]['totpaid'] += $gbook['totpaid'];
if (!in_array($gbook['id'], $paystats[$payname]['bids'])) {
array_push($paystats[$payname]['bids'], $gbook['id']);
}
}
// loop over the stats to build the rows
foreach ($paystats as $payname => $stats) {
//push fields in the rows array as a new row
array_push($this->rows, array(
array(
'key' => 'paymeth',
'value' => $payname
),
array(
'key' => 'tot',
'attr' => array(
'class="center"'
),
'callback' => function ($val) use ($currency_symb) {
return $currency_symb.' '.VikBooking::numberFormat($val);
},
'export_callback' => function ($val) use ($currency_symb) {
return $currency_symb.' '.VikBooking::numberFormat($val);
},
'value' => $stats['totpaid']
),
array(
'key' => 'ids',
'attr' => array(
'class="center"'
),
'callback' => function ($val) {
$str = '';
foreach ($val as $bid) {
$str .= ' '.$bid.'';
}
return $str;
},
'ignore_export' => 1,
'value' => $stats['bids']
),
));
}
// sort rows
$this->sortRows($pkrsort, $pkrorder);
// loop over the rows to build the footer row with the totals
$foot_tot_collected = 0;
foreach ($this->rows as $row) {
$foot_tot_collected += $row[1]['value'];
}
// push footer row
array_push($this->footerRow, array(
array(
'attr' => array(
'class="vbo-report-total"'
),
'value' => '