| @@ -1,0 +1,309 @@ | ||
| 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 | +} | |
