| 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 |
// import Joomla view library |
| 14 |
jimport('joomla.application.component.view'); |
| 15 |
|
| 16 |
class VikBookingViewTrackings extends JViewVikBooking { |
| 17 |
|
| 18 |
function display($tpl = null) { |
| 19 |
// Set the toolbar |
| 20 |
$this->addToolBar(); |
| 21 |
|
| 22 |
// require the tracker class |
| 23 |
VikBooking::getTracker(true); |
| 24 |
// |
| 25 |
|
| 26 |
$dbo = JFactory::getDbo(); |
| 27 |
$mainframe = JFactory::getApplication(); |
| 28 |
$lim = $mainframe->getUserStateFromRequest("com_vikbooking.limit", 'limit', $mainframe->get('list_limit'), 'int'); |
| 29 |
$lim0 = VikRequest::getVar('limitstart', 0, '', 'int'); |
| 30 |
$session = JFactory::getSession(); |
| 31 |
$pvborderby = VikRequest::getString('vborderby', '', 'request'); |
| 32 |
$pvbordersort = VikRequest::getString('vbordersort', '', 'request'); |
| 33 |
$validorderby = array('id', 'dt', 'lastdt', 'published', 'country', 'geo'); |
| 34 |
$orderby = $session->get('vbViewTrackingsOrderby', 'lastdt'); |
| 35 |
$ordersort = $session->get('vbViewTrackingsOrdersort', 'DESC'); |
| 36 |
if (!empty($pvborderby) && in_array($pvborderby, $validorderby)) { |
| 37 |
$orderby = $pvborderby; |
| 38 |
$session->set('vbViewTrackingsOrderby', $orderby); |
| 39 |
if (!empty($pvbordersort) && in_array($pvbordersort, array('ASC', 'DESC'))) { |
| 40 |
$ordersort = $pvbordersort; |
| 41 |
$session->set('vbViewTrackingsOrdersort', $ordersort); |
| 42 |
} |
| 43 |
} |
| 44 |
|
| 45 |
// get min and max dates for filters |
| 46 |
$minmaxvals = array( |
| 47 |
'mintrackingdt' => 0, |
| 48 |
'maxtrackingdt' => 0, |
| 49 |
'mincheckin' => 0, |
| 50 |
'maxcheckin' => 0, |
| 51 |
'mincheckout' => 0, |
| 52 |
'maxcheckout' => 0, |
| 53 |
); |
| 54 |
$q = "SELECT MIN(`trackingdt`) AS `mintrackingdt`, MAX(`trackingdt`) AS `maxtrackingdt`, MIN(`checkin`) AS `mincheckin`, MAX(`checkin`) AS `maxcheckin`, MIN(`checkout`) AS `mincheckout`, MAX(`checkout`) AS `maxcheckout` |
| 55 |
FROM `#__vikbooking_tracking_infos`;"; |
| 56 |
$dbo->setQuery($q); |
| 57 |
$dbo->execute(); |
| 58 |
if ($dbo->getNumRows()) { |
| 59 |
$data = $dbo->loadAssoc(); |
| 60 |
$minmaxvals = array( |
| 61 |
'mintrackingdt' => (!empty($data['mintrackingdt']) ? strtotime($data['mintrackingdt']) : 0), |
| 62 |
'maxtrackingdt' => (!empty($data['maxtrackingdt']) ? strtotime($data['maxtrackingdt']) : 0), |
| 63 |
'mincheckin' => (!empty($data['mincheckin']) ? strtotime($data['mincheckin']) : 0), |
| 64 |
'maxcheckin' => (!empty($data['maxcheckin']) ? strtotime($data['maxcheckin']) : 0), |
| 65 |
'mincheckout' => (!empty($data['mincheckout']) ? strtotime($data['mincheckout']) : 0), |
| 66 |
'maxcheckout' => (!empty($data['maxcheckout']) ? strtotime($data['maxcheckout']) : 0), |
| 67 |
); |
| 68 |
} |
| 69 |
$mindate = min($minmaxvals); |
| 70 |
$maxdate = max($minmaxvals); |
| 71 |
|
| 72 |
// get all tracked countries for filters |
| 73 |
$countries = array(); |
| 74 |
$q = "SELECT DISTINCT `t`.`country`,`c`.`country_name` FROM `#__vikbooking_trackings` AS `t` LEFT JOIN `#__vikbooking_countries` AS `c` ON `t`.`country`=`c`.`country_3_code` ORDER BY `c`.`country_name` ASC;"; |
| 75 |
$dbo->setQuery($q); |
| 76 |
$dbo->execute(); |
| 77 |
if ($dbo->getNumRows()) { |
| 78 |
$countries = $dbo->loadAssocList(); |
| 79 |
// unset NULL values |
| 80 |
foreach ($countries as $k => $v) { |
| 81 |
if (empty($v['country'])) { |
| 82 |
unset($countries[$k]); |
| 83 |
} |
| 84 |
} |
| 85 |
} |
| 86 |
|
| 87 |
// get all tracked referrers for filters |
| 88 |
$referrers = array(); |
| 89 |
$q = "SELECT DISTINCT `referrer` FROM `#__vikbooking_tracking_infos` ORDER BY `referrer` ASC;"; |
| 90 |
$dbo->setQuery($q); |
| 91 |
$dbo->execute(); |
| 92 |
if ($dbo->getNumRows()) { |
| 93 |
$referrers = $dbo->loadAssocList(); |
| 94 |
// unset NULL values |
| 95 |
foreach ($referrers as $k => $v) { |
| 96 |
if (empty($v['referrer'])) { |
| 97 |
unset($referrers[$k]); |
| 98 |
} |
| 99 |
} |
| 100 |
} |
| 101 |
|
| 102 |
// query filters |
| 103 |
$filters = array(); |
| 104 |
|
| 105 |
// date filter |
| 106 |
$nowdf = VikBooking::getDateFormat(true); |
| 107 |
if ($nowdf == "%d/%m/%Y") { |
| 108 |
$df = 'd/m/Y'; |
| 109 |
} elseif ($nowdf == "%m/%d/%Y") { |
| 110 |
$df = 'm/d/Y'; |
| 111 |
} else { |
| 112 |
$df = 'Y/m/d'; |
| 113 |
} |
| 114 |
$now_info = getdate(); |
| 115 |
$def_dt_from = date($df, mktime(0, 0, 0, $now_info['mon'], ($now_info['mday'] - 7), $now_info['year'])); |
| 116 |
$def_dt_to = date($df); |
| 117 |
$pdatefilt = $mainframe->getUserStateFromRequest("vbo.trackings.datefilt", 'datefilt', 1, 'int'); |
| 118 |
$pdatefiltfrom = $mainframe->getUserStateFromRequest("vbo.trackings.datefiltfrom", 'datefiltfrom', $def_dt_from, 'string'); |
| 119 |
$pdatefiltto = $mainframe->getUserStateFromRequest("vbo.trackings.datefiltto", 'datefiltto', $def_dt_to, 'string'); |
| 120 |
if ((!empty($pdatefiltfrom) || !empty($pdatefiltto))) { |
| 121 |
if ($pdatefilt == 1) { |
| 122 |
// tracking dates |
| 123 |
if (!empty($pdatefiltfrom)) { |
| 124 |
array_push($filters, '`t`.`lastdt` >= '.$dbo->quote(JDate::getInstance(date('Y-m-d H:i:s', VikBooking::getDateTimestamp($pdatefiltfrom, 0, 0)))->toSql())); |
| 125 |
} |
| 126 |
if (!empty($pdatefiltto)) { |
| 127 |
array_push($filters, '`t`.`lastdt` <= '.$dbo->quote(JDate::getInstance(date('Y-m-d H:i:s', VikBooking::getDateTimestamp($pdatefiltto, 23, 59, 59)))->toSql())); |
| 128 |
} |
| 129 |
} elseif ($pdatefilt == 2) { |
| 130 |
// booking dates |
| 131 |
$bookdatesfilt = array(); |
| 132 |
if (!empty($pdatefiltfrom)) { |
| 133 |
// filter from-date inside a range of dates booked (we use 23:59:59 to avoid calculating the check-in time) |
| 134 |
array_push( |
| 135 |
$bookdatesfilt, |
| 136 |
'(' . |
| 137 |
'`i`.`checkin` <= '.$dbo->quote(JDate::getInstance(date('Y-m-d H:i:s', VikBooking::getDateTimestamp($pdatefiltfrom, 23, 59, 59)))->toSql()) . |
| 138 |
' AND ' . |
| 139 |
'`i`.`checkout` >= '.$dbo->quote(JDate::getInstance(date('Y-m-d H:i:s', VikBooking::getDateTimestamp($pdatefiltfrom, 0, 0, 0)))->toSql()) . |
| 140 |
')' |
| 141 |
); |
| 142 |
} |
| 143 |
if (!empty($pdatefiltto)) { |
| 144 |
// filter to-date inside a range of dates booked (we use 00:00:00 to avoid calculating the check-out time) |
| 145 |
array_push( |
| 146 |
$bookdatesfilt, |
| 147 |
'(' . |
| 148 |
'`i`.`checkin` <= '.$dbo->quote(JDate::getInstance(date('Y-m-d H:i:s', VikBooking::getDateTimestamp($pdatefiltto, 23, 59, 59)))->toSql()) . |
| 149 |
' AND ' . |
| 150 |
'`i`.`checkout` >= '.$dbo->quote(JDate::getInstance(date('Y-m-d H:i:s', VikBooking::getDateTimestamp($pdatefiltto, 0, 0, 0)))->toSql()) . |
| 151 |
')' |
| 152 |
); |
| 153 |
} |
| 154 |
if (!empty($pdatefiltfrom) && !empty($pdatefiltto)) { |
| 155 |
// filter dates including booking dates (bigger than) |
| 156 |
array_push( |
| 157 |
$bookdatesfilt, |
| 158 |
'(' . |
| 159 |
'`i`.`checkin` >= '.$dbo->quote(JDate::getInstance(date('Y-m-d H:i:s', VikBooking::getDateTimestamp($pdatefiltfrom, 0, 0)))->toSql()) . |
| 160 |
' AND ' . |
| 161 |
'`i`.`checkout` <= '.$dbo->quote(JDate::getInstance(date('Y-m-d H:i:s', VikBooking::getDateTimestamp($pdatefiltto, 23, 59, 59)))->toSql()) . |
| 162 |
')' |
| 163 |
); |
| 164 |
} |
| 165 |
array_push($filters, '(' . implode(' OR ', $bookdatesfilt) . ')'); |
| 166 |
} elseif ($pdatefilt == 3) { |
| 167 |
// checkin date |
| 168 |
if (!empty($pdatefiltfrom)) { |
| 169 |
array_push($filters, '`i`.`checkin` >= '.$dbo->quote(JDate::getInstance(date('Y-m-d H:i:s', VikBooking::getDateTimestamp($pdatefiltfrom, 0, 0)))->toSql())); |
| 170 |
} |
| 171 |
if (!empty($pdatefiltto)) { |
| 172 |
array_push($filters, '`i`.`checkin` <= '.$dbo->quote(JDate::getInstance(date('Y-m-d H:i:s', VikBooking::getDateTimestamp($pdatefiltto, 23, 59, 59)))->toSql())); |
| 173 |
} |
| 174 |
} elseif ($pdatefilt == 4) { |
| 175 |
// checkout date |
| 176 |
if (!empty($pdatefiltfrom)) { |
| 177 |
array_push($filters, '`i`.`checkout` >= '.$dbo->quote(JDate::getInstance(date('Y-m-d H:i:s', VikBooking::getDateTimestamp($pdatefiltfrom, 0, 0)))->toSql())); |
| 178 |
} |
| 179 |
if (!empty($pdatefiltto)) { |
| 180 |
array_push($filters, '`i`.`checkout` <= '.$dbo->quote(JDate::getInstance(date('Y-m-d H:i:s', VikBooking::getDateTimestamp($pdatefiltto, 23, 59, 59)))->toSql())); |
| 181 |
} |
| 182 |
} |
| 183 |
} |
| 184 |
|
| 185 |
// country filter |
| 186 |
$pcountryfilt = VikRequest::getString('countryfilt', '', 'request'); |
| 187 |
if (!empty($pcountryfilt)) { |
| 188 |
array_push($filters, '`t`.`country` = '.$dbo->quote($pcountryfilt)); |
| 189 |
} |
| 190 |
|
| 191 |
// referrer filter |
| 192 |
$preferrer = VikRequest::getString('referrer', '', 'request'); |
| 193 |
if (!empty($preferrer)) { |
| 194 |
array_push($filters, '`i`.`referrer` = '.$dbo->quote($preferrer)); |
| 195 |
} |
| 196 |
|
| 197 |
// calculate most demanded nights, conversion rates, best referrers |
| 198 |
$stats_data = array(); |
| 199 |
$tomorrowdt = JDate::getInstance(date('Y-m-d', strtotime('tomorrow')))->toSql(); |
| 200 |
$q = "SELECT `i`.`id`, `i`.`idtracking`, `i`.`identifier`, `i`.`checkin`, `i`.`checkout`, `i`.`idorder`, `i`.`referrer`, `t`.`lastdt`, `t`.`published` |
| 201 |
FROM `#__vikbooking_tracking_infos` AS `i` |
| 202 |
LEFT JOIN `#__vikbooking_trackings` AS `t` ON `i`.`idtracking`=`t`.`id` |
| 203 |
WHERE `t`.`published`=1 AND ".(count($filters) ? implode(' AND ', $filters) : '`i`.`checkin` > '.$dbo->quote($tomorrowdt))." |
| 204 |
ORDER BY `i`.`checkin` ASC, `i`.`id` DESC;"; |
| 205 |
$dbo->setQuery($q); |
| 206 |
$dbo->execute(); |
| 207 |
if ($dbo->getNumRows()) { |
| 208 |
$stats_data = $dbo->loadAssocList(); |
| 209 |
} |
| 210 |
|
| 211 |
// build the records with pagination |
| 212 |
$rows = ""; |
| 213 |
$navbut = ""; |
| 214 |
// this query cannot be compatible with SQL ONLY_FULL_GROUP_BY as we always need to join `#__vikbooking_tracking_infos` |
| 215 |
$q = "SELECT SQL_CALC_FOUND_ROWS `t`.*,`c`.`country_name`,`cu`.`first_name`,`cu`.`last_name`,`cu`.`country` AS `c_country` ". |
| 216 |
"FROM `#__vikbooking_trackings` AS `t` ". |
| 217 |
"LEFT JOIN `#__vikbooking_tracking_infos` AS `i` ON `t`.`id`=`i`.`idtracking` ". |
| 218 |
"LEFT JOIN `#__vikbooking_countries` AS `c` ON `t`.`country`=`c`.`country_3_code` ". |
| 219 |
"LEFT JOIN `#__vikbooking_customers` AS `cu` ON `t`.`idcustomer`=`cu`.`id` ". |
| 220 |
(count($filters) ? 'WHERE '.implode(' AND ', $filters).' ' : ''). |
| 221 |
"GROUP BY `t`.`id` ". |
| 222 |
"ORDER BY `t`.`".$orderby."` ".$ordersort; |
| 223 |
$dbo->setQuery($q, $lim0, $lim); |
| 224 |
$dbo->execute(); |
| 225 |
if ($dbo->getNumRows() > 0) { |
| 226 |
$rows = $dbo->loadAssocList(); |
| 227 |
$dbo->setQuery('SELECT FOUND_ROWS();'); |
| 228 |
jimport('joomla.html.pagination'); |
| 229 |
$pageNav = new JPagination( $dbo->loadResult(), $lim0, $lim ); |
| 230 |
$navbut = "<table align=\"center\"><tr><td>".$pageNav->getListFooter()."</td></tr></table>"; |
| 231 |
|
| 232 |
// load tracking infos |
| 233 |
foreach ($rows as $k => $v) { |
| 234 |
$infos = array(); |
| 235 |
$q = "SELECT `i`.*,`o`.`status` |
| 236 |
FROM `#__vikbooking_tracking_infos` AS `i` |
| 237 |
LEFT JOIN `#__vikbooking_orders` AS `o` ON `i`.`idorder`=`o`.`id` |
| 238 |
WHERE `i`.`idtracking`=".(int)$v['id']." ORDER BY `i`.`identifier` DESC, `i`.`id` ASC;"; |
| 239 |
$dbo->setQuery($q); |
| 240 |
$dbo->execute(); |
| 241 |
if ($dbo->getNumRows()) { |
| 242 |
$infos = $dbo->loadAssocList(); |
| 243 |
} |
| 244 |
$rows[$k]['infos'] = $infos; |
| 245 |
} |
| 246 |
} |
| 247 |
|
| 248 |
// load all rooms |
| 249 |
$rooms = array(); |
| 250 |
$q = "SELECT `id`,`name` FROM `#__vikbooking_rooms`;"; |
| 251 |
$dbo->setQuery($q); |
| 252 |
$dbo->execute(); |
| 253 |
if ($dbo->getNumRows()) { |
| 254 |
$all_rooms = $dbo->loadAssocList(); |
| 255 |
foreach ($all_rooms as $v) { |
| 256 |
$rooms[$v['id']] = $v['name']; |
| 257 |
} |
| 258 |
} |
| 259 |
|
| 260 |
// load all prices |
| 261 |
$prices = array(); |
| 262 |
$q = "SELECT `id`,`name` FROM `#__vikbooking_prices`;"; |
| 263 |
$dbo->setQuery($q); |
| 264 |
$dbo->execute(); |
| 265 |
if ($dbo->getNumRows()) { |
| 266 |
$all_prices = $dbo->loadAssocList(); |
| 267 |
foreach ($all_prices as $v) { |
| 268 |
$prices[$v['id']] = $v['name']; |
| 269 |
} |
| 270 |
} |
| 271 |
|
| 272 |
$this->rows = $rows; |
| 273 |
$this->stats_data = $stats_data; |
| 274 |
$this->rooms = $rooms; |
| 275 |
$this->prices = $prices; |
| 276 |
$this->mindate = $mindate; |
| 277 |
$this->maxdate = $maxdate; |
| 278 |
$this->countries = $countries; |
| 279 |
$this->referrers = $referrers; |
| 280 |
$this->lim0 = $lim0; |
| 281 |
$this->navbut = $navbut; |
| 282 |
$this->orderby = $orderby; |
| 283 |
$this->ordersort = $ordersort; |
| 284 |
$this->datefilt = $pdatefilt; |
| 285 |
$this->datefiltfrom = $pdatefiltfrom; |
| 286 |
$this->datefiltto = $pdatefiltto; |
| 287 |
|
| 288 |
// Display the template |
| 289 |
parent::display($tpl); |
| 290 |
} |
| 291 |
|
| 292 |
/** |
| 293 |
* Sets the toolbar |
| 294 |
*/ |
| 295 |
protected function addToolBar() { |
| 296 |
JToolBarHelper::title(JText::translate('VBMAINTRACKINGSTITLE'), 'vikbooking'); |
| 297 |
if (JFactory::getUser()->authorise('core.delete', 'com_vikbooking')) { |
| 298 |
JToolBarHelper::deleteList(JText::translate('VBDELCONFIRM'), 'removetrackings', JText::translate('VBMAINCRONDEL')); |
| 299 |
JToolBarHelper::spacer(); |
| 300 |
} |
| 301 |
if (JFactory::getUser()->authorise('core.edit', 'com_vikbooking')) { |
| 302 |
JToolBarHelper::custom('modtracking', 'refresh', 'refresh', JText::translate('VBTRKTGLPUBLISHED'), true, false); |
| 303 |
JToolBarHelper::spacer(); |
| 304 |
} |
| 305 |
JToolBarHelper::cancel( 'cancel', JText::translate('VBBACK')); |
| 306 |
JToolBarHelper::spacer(); |
| 307 |
} |
| 308 |
|
| 309 |
} |
| 310 |
|
