1 <?php
   2 /* --------------------------------------------------------------
   3    StatisticsService.inc.php 2016-02-18
   4    Gambio GmbH
   5    http://www.gambio.de
   6    Copyright (c) 2016 Gambio GmbH
   7    Released under the GNU General Public License (Version 2)
   8    [http://www.gnu.org/licenses/gpl-2.0.html]
   9    --------------------------------------------------------------
  10 */
  11 
  12 /**
  13  * Class StatisticsService
  14  *
  15  * Provides the statistic data for the dashboard
  16  *
  17  * @category   System
  18  * @package    Statistics
  19  */
  20 class StatisticsService implements StatisticsServiceInterface
  21 {
  22 
  23     /**
  24      * @var CI_DB_query_builder
  25      */
  26     protected $db;
  27 
  28     /**
  29      * @var xtcPrice
  30      */
  31     protected $xtcPrice;
  32 
  33     /**
  34      * @var int
  35      */
  36     protected $conversionRateDecimalPlaces = 2;
  37     
  38     /**
  39      * @var array
  40      */
  41     protected $excludeOrderStatusIds = array(99);
  42 
  43 
  44     public function __construct(CI_DB_query_builder $db, xtcPrice $xtcPrice)
  45     {
  46         $this->db       = $db;
  47         $this->xtcPrice = $xtcPrice;
  48     }
  49 
  50 
  51     // -----------------------------------------------------------------------------------------------------------------
  52     // GET ONLINE USERS
  53     // -----------------------------------------------------------------------------------------------------------------
  54 
  55     /**
  56      * Gets the amount of users who are currently online
  57      *
  58      * @return int
  59      */
  60     public function getUsersOnline()
  61     {
  62         $result = $this->db->query('SELECT COUNT(*) AS users_online FROM whos_online')->row_array(1);
  63 
  64         return (int)$result['users_online'];
  65     }
  66 
  67 
  68 
  69     // -----------------------------------------------------------------------------------------------------------------
  70     // GET VISITORS
  71     // -----------------------------------------------------------------------------------------------------------------
  72 
  73     /**
  74      * Get visitors data for the last week.
  75      *
  76      * @return array
  77      */
  78     public function getVisitorsStatisticsDataLastWeek()
  79     {
  80         $returnArray = array();
  81         $dataArray   = array();
  82 
  83         for($i = 7; $i > 1; $i--)
  84         {
  85             $dataArray[] = $this->_getVisitorsFromDayIntervalHelper($i);
  86         }
  87         $dataArray[] = $this->_getVisitorsFromCurrentDayHelper();
  88 
  89         $returnArray['type'] = 'day';
  90         $returnArray['data'] = $dataArray;
  91 
  92         return $returnArray;
  93     }
  94 
  95 
  96     /**
  97      * Get visitors data for the last two weeks.
  98      *
  99      * @return array
 100      */
 101     public function getVisitorsStatisticsDataLastTwoWeeks()
 102     {
 103         $returnArray = array();
 104         $dataArray   = array();
 105 
 106         for($i = 14; $i > 1; $i--)
 107         {
 108             $dataArray[] = $this->_getVisitorsFromDayIntervalHelper($i);
 109         }
 110         $dataArray[] = $this->_getVisitorsFromCurrentDayHelper();
 111 
 112         $returnArray['type'] = 'day';
 113         $returnArray['data'] = $dataArray;
 114 
 115         return $returnArray;
 116     }
 117 
 118 
 119     /**
 120      * Get visitors data for the last month.
 121      *
 122      * @return array
 123      */
 124     public function getVisitorsStatisticsDataLastMonth()
 125     {
 126         $returnArray = array();
 127         $dataArray   = array();
 128         $dayDiff     = time() - strtotime('-1 months');
 129         $days        = floor($dayDiff / (60 * 60 * 24));
 130 
 131         for($i = $days; $i > 1; $i--)
 132         {
 133             $dataArray[] = $this->_getVisitorsFromDayIntervalHelper($i);
 134         }
 135         $dataArray[] = $this->_getVisitorsFromCurrentDayHelper();
 136 
 137         $returnArray['type'] = 'day';
 138         $returnArray['data'] = $dataArray;
 139 
 140         return $returnArray;
 141     }
 142 
 143 
 144     /**
 145      * Get visitors data for the last three months.
 146      *
 147      * @return array
 148      */
 149     public function getVisitorsStatisticsDataLastThreeMonth()
 150     {
 151         $returnArray = array();
 152         $dataArray   = array();
 153 
 154         for($i = 3; $i > 1; $i--)
 155         {
 156             $dataArray[] = $this->_getVisitorsFromMonthIntervalHelper($i);
 157         }
 158         $dataArray[] = $this->_getVisitorsFromCurrentMonthHelper();
 159 
 160         $returnArray['type'] = 'month';
 161         $returnArray['data'] = $dataArray;
 162 
 163         return $returnArray;
 164     }
 165 
 166 
 167     /**
 168      * Get visitors data for the last six months.
 169      *
 170      * @return array
 171      */
 172     public function getVisitorsStatisticsDataLastSixMonth()
 173     {
 174         $returnArray = array();
 175         $dataArray   = array();
 176 
 177         for($i = 6; $i > 1; $i--)
 178         {
 179             $dataArray[] = $this->_getVisitorsFromMonthIntervalHelper($i);
 180         }
 181         $dataArray[] = $this->_getVisitorsFromCurrentMonthHelper();
 182 
 183         $returnArray['type'] = 'month';
 184         $returnArray['data'] = $dataArray;
 185 
 186         return $returnArray;
 187     }
 188 
 189 
 190     /**
 191      * Get visitors data for the last six months.
 192      *
 193      * @return array
 194      */
 195     public function getVisitorsStatisticsDataLastYear()
 196     {
 197         $returnArray = array();
 198         $dataArray   = array();
 199 
 200         for($i = 12; $i > 1; $i--)
 201         {
 202             $dataArray[] = $this->_getVisitorsFromMonthIntervalHelper($i);
 203         }
 204         $dataArray[] = $this->_getVisitorsFromCurrentMonthHelper();
 205 
 206         $returnArray['type'] = 'month';
 207         $returnArray['data'] = $dataArray;
 208 
 209         return $returnArray;
 210     }
 211 
 212 
 213     /**
 214      * Helper function to fetch visitors data of a month time span.
 215      *
 216      * @param int $fromInterval
 217      *
 218      * @return array
 219      */
 220     protected function _getVisitorsFromDayIntervalHelper($fromInterval)
 221     {
 222         $query = 'SELECT SUM(gm_counter_visits_total) AS amount 
 223                   FROM gm_counter_visits 
 224                   WHERE DATE(gm_counter_date) BETWEEN DATE_SUB(NOW(),INTERVAL ' . $fromInterval
 225                  . ' DAY) AND DATE_SUB(NOW(),INTERVAL ' . ((int)$fromInterval - 1) . ' DAY)';
 226         $data  = $this->db->query($query)->row_array(1);
 227         if(null === $data['amount'])
 228         {
 229             $data['amount'] = '0';
 230         }
 231 
 232         return array_merge(array('period' => strtotime('-' . ((int)$fromInterval - 1) . ' days') * 1000), $data);
 233     }
 234 
 235 
 236     /**
 237      * Helper function to fetch visitors data of the current month.
 238      *
 239      * @return array
 240      */
 241     protected function _getVisitorsFromCurrentDayHelper()
 242     {
 243         $query = 'SELECT SUM(gm_counter_visits_total) AS amount 
 244                   FROM gm_counter_visits 
 245                   WHERE DATE(gm_counter_date) BETWEEN DATE_SUB(NOW(),INTERVAL 1 DAY) AND NOW()';
 246         $data  = $this->db->query($query)->row_array(1);
 247         if(null === $data['amount'])
 248         {
 249             $data['amount'] = '0';
 250         }
 251 
 252         return array_merge(array('period' => time() * 1000), $data);
 253     }
 254 
 255 
 256     /**
 257      * Helper function to fetch visitors data of a month time span.
 258      *
 259      * @param int $fromInterval
 260      *
 261      * @return array
 262      */
 263     protected function _getVisitorsFromMonthIntervalHelper($fromInterval)
 264     {
 265         $query = 'SELECT SUM(gm_counter_visits_total) AS amount 
 266                   FROM gm_counter_visits 
 267                   WHERE DATE(gm_counter_date) BETWEEN DATE_SUB(CURDATE(),INTERVAL ' . $fromInterval
 268                  . ' MONTH) AND DATE_SUB(CURDATE(),INTERVAL ' . ((int)$fromInterval - 1) . ' MONTH)';
 269         $data  = $this->db->query($query)->row_array(1);
 270         if(null === $data['amount'])
 271         {
 272             $data['amount'] = '0';
 273         }
 274 
 275         return array_merge(array('period' => strtotime('-' . ((int)$fromInterval - 1) . ' months') * 1000), $data);
 276     }
 277 
 278 
 279     /**
 280      * Helper function to fetch visitors data of the current month.
 281      *
 282      * @return array
 283      */
 284     protected function _getVisitorsFromCurrentMonthHelper()
 285     {
 286         $query = 'SELECT SUM(gm_counter_visits_total) AS amount 
 287                   FROM gm_counter_visits 
 288                   WHERE DATE(gm_counter_date) BETWEEN DATE_SUB(CURDATE(),INTERVAL 1 MONTH) AND CURDATE()';
 289         $data  = $this->db->query($query)->row_array(1);
 290         if(null === $data['amount'])
 291         {
 292             $data['amount'] = '0';
 293         }
 294 
 295         return array_merge(array('period' => time() * 1000), $data);
 296     }
 297 
 298 
 299     /**
 300      * Gets the amount of visitors of the current date
 301      *
 302      * @return int
 303      */
 304     public function getVisitorsToday()
 305     {
 306         $result = $this->db->query('SELECT gm_counter_visits_total AS visitors 
 307                                             FROM gm_counter_visits 
 308                                             WHERE DATE(gm_counter_date) = CURDATE() ')->row_array(1);
 309 
 310         return (int)$result['visitors'];
 311     }
 312 
 313 
 314     /**
 315      * Gets the amount of visitors of the last week
 316      *
 317      * @return int
 318      */
 319     public function getVisitorsLastWeek()
 320     {
 321         $result = $this->db->query('SELECT SUM(gm_counter_visits_total) AS visitors 
 322                                   FROM gm_counter_visits 
 323                                   WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= DATE(gm_counter_date)')->row_array(1);
 324 
 325         return (int)$result['visitors'];
 326     }
 327 
 328 
 329     /**
 330      * Gets the amount of visitors of the last two week
 331      *
 332      * @return int
 333      */
 334     public function getVisitorsLastTwoWeeks()
 335     {
 336         $result = $this->db->query('SELECT SUM(gm_counter_visits_total) AS visitors 
 337                                   FROM gm_counter_visits 
 338                                   WHERE DATE_SUB(CURDATE(),INTERVAL 14 DAY) <= DATE(gm_counter_date)')->row_array(1);
 339 
 340         return (int)$result['visitors'];
 341     }
 342 
 343 
 344     /**
 345      * Gets the amount of visitors of the last month
 346      *
 347      * @return int
 348      */
 349     public function getVisitorsLastMonth()
 350     {
 351         $result = $this->db->query('SELECT SUM(gm_counter_visits_total) AS visitors 
 352                                   FROM gm_counter_visits 
 353                                   WHERE DATE_SUB(CURDATE(),INTERVAL 1 MONTH) <= DATE(gm_counter_date)')->row_array(1);
 354 
 355         return (int)$result['visitors'];
 356     }
 357 
 358 
 359     /**
 360      * Gets the amount of visitors of the last three months
 361      *
 362      * @return int
 363      */
 364     public function getVisitorsLastThreeMonths()
 365     {
 366         $result = $this->db->query('SELECT SUM(gm_counter_visits_total) AS visitors 
 367                                   FROM gm_counter_visits 
 368                                   WHERE DATE_SUB(CURDATE(),INTERVAL 3 MONTH) <= DATE(gm_counter_date)')->row_array(1);
 369 
 370         return (int)$result['visitors'];
 371     }
 372 
 373 
 374     /**
 375      * Gets the amount of visitors of the last six months
 376      *
 377      * @return int
 378      */
 379     public function getVisitorsLastSixMonths()
 380     {
 381         $result = $this->db->query('SELECT SUM(gm_counter_visits_total) AS visitors 
 382                                   FROM gm_counter_visits 
 383                                   WHERE DATE_SUB(CURDATE(),INTERVAL 6 MONTH) <= DATE(gm_counter_date)')->row_array(1);
 384 
 385         return (int)$result['visitors'];
 386     }
 387 
 388 
 389     /**
 390      * Gets the amount of visitors of the last year
 391      *
 392      * @return int
 393      */
 394     public function getVisitorsLastYear()
 395     {
 396         $result = $this->db->query('SELECT SUM(gm_counter_visits_total) AS visitors 
 397                                   FROM gm_counter_visits 
 398                                   WHERE DATE_SUB(CURDATE(),INTERVAL 1 YEAR) <= DATE(gm_counter_date)')->row_array(1);
 399 
 400         return (int)$result['visitors'];
 401     }
 402 
 403     // -----------------------------------------------------------------------------------------------------------------
 404     // GET NEW CUSTOMERS
 405     // -----------------------------------------------------------------------------------------------------------------
 406 
 407     /**
 408      * Get new customer data of the last week.
 409      *
 410      * @return array
 411      */
 412     public function getNewCustomersStatisticsDataLastWeek()
 413     {
 414         $returnArray = array();
 415         $dataArray   = array();
 416 
 417         for($i = 7; $i > 1; $i--)
 418         {
 419             $dataArray[] = $this->_getNewCustomersFromDayIntervalHelper($i);
 420         }
 421         $dataArray[] = $this->_getNewCustomersFromCurrentDayHelper();
 422 
 423         $returnArray['type'] = 'month';
 424         $returnArray['data'] = $dataArray;
 425 
 426         return $returnArray;
 427     }
 428 
 429 
 430     /**
 431      * Get new customer data of the last two weeks.
 432      *
 433      * @return array
 434      */
 435     public function getNewCustomersStatisticsDataLastTwoWeeks()
 436     {
 437         $returnArray = array();
 438         $dataArray   = array();
 439 
 440         for($i = 14; $i > 1; $i--)
 441         {
 442             $dataArray[] = $this->_getNewCustomersFromDayIntervalHelper($i);
 443         }
 444         $dataArray[] = $this->_getNewCustomersFromCurrentDayHelper();
 445 
 446         $returnArray['type'] = 'month';
 447         $returnArray['data'] = $dataArray;
 448 
 449         return $returnArray;
 450     }
 451 
 452 
 453     /**
 454      * Get new customer data of the last month.
 455      *
 456      * @return array
 457      */
 458     public function getNewCustomersStatisticsDataLastMonth()
 459     {
 460         $returnArray = array();
 461         $dataArray   = array();
 462         $dayDiff     = time() - strtotime('-1 months');
 463         $days        = floor($dayDiff / (60 * 60 * 24));
 464 
 465         for($i = $days; $i > 1; $i--)
 466         {
 467             $dataArray[] = $this->_getNewCustomersFromDayIntervalHelper($i);
 468         }
 469         $dataArray[] = $this->_getNewCustomersFromCurrentDayHelper();
 470 
 471         $returnArray['type'] = 'month';
 472         $returnArray['data'] = $dataArray;
 473 
 474         return $returnArray;
 475     }
 476 
 477 
 478     /**
 479      * Get new customer data of the last three months.
 480      *
 481      * @return array
 482      */
 483     public function getNewCustomersStatisticsDataLastThreeMonth()
 484     {
 485         $returnArray = array();
 486         $dataArray   = array();
 487 
 488         for($i = 3; $i > 1; $i--)
 489         {
 490             $dataArray[] = $this->_getNewCustomersFromMonthIntervalHelper($i);
 491         }
 492         $dataArray[] = $this->_getNewCustomersFromCurrentMonthHelper();
 493 
 494         $returnArray['type'] = 'month';
 495         $returnArray['data'] = $dataArray;
 496 
 497         return $returnArray;
 498     }
 499 
 500 
 501     /**
 502      * Get new customer data of the last six months.
 503      *
 504      * @return array
 505      */
 506     public function getNewCustomersStatisticsDataLastSixMonth()
 507     {
 508         $returnArray = array();
 509         $dataArray   = array();
 510 
 511         for($i = 6; $i > 1; $i--)
 512         {
 513             $dataArray[] = $this->_getNewCustomersFromMonthIntervalHelper($i);
 514         }
 515         $dataArray[] = $this->_getNewCustomersFromCurrentMonthHelper();
 516 
 517         $returnArray['type'] = 'month';
 518         $returnArray['data'] = $dataArray;
 519 
 520         return $returnArray;
 521     }
 522 
 523 
 524     /**
 525      * Get new customer data of the last year.
 526      *
 527      * @return array
 528      */
 529     public function getNewCustomersStatisticsDataLastYear()
 530     {
 531         $returnArray = array();
 532         $dataArray   = array();
 533 
 534         for($i = 12; $i > 1; $i--)
 535         {
 536             $dataArray[] = $this->_getNewCustomersFromMonthIntervalHelper($i);
 537         }
 538         $dataArray[] = $this->_getNewCustomersFromCurrentMonthHelper();
 539 
 540         $returnArray['type'] = 'month';
 541         $returnArray['data'] = $dataArray;
 542 
 543         return $returnArray;
 544     }
 545 
 546 
 547     /**
 548      * Helper function to fetch new customer data of a day time span.
 549      *
 550      * @param int $fromInterval
 551      *
 552      * @return array
 553      */
 554     protected function _getNewCustomersFromDayIntervalHelper($fromInterval)
 555     {
 556         $query = 'SELECT COUNT(*) AS amount
 557                   FROM customers_info 
 558                   WHERE DATE(customers_info_date_account_created) BETWEEN DATE_SUB(NOW(),INTERVAL ' . $fromInterval
 559                  . ' DAY) AND DATE_SUB(NOW(),INTERVAL ' . ((int)$fromInterval - 1) . ' DAY)';
 560         $data  = $this->db->query($query)->row_array(1);
 561         if(null === $data['amount'])
 562         {
 563             $data['amount'] = '0';
 564         }
 565 
 566         return array_merge(array('period' => strtotime('-' . ((int)$fromInterval - 1) . ' days') * 1000), $data);
 567     }
 568 
 569 
 570     /**
 571      * Helper function to fetch new customer data of the current day.
 572      *
 573      * @return array
 574      */
 575     protected function _getNewCustomersFromCurrentDayHelper()
 576     {
 577         $query = 'SELECT COUNT(*) AS amount
 578                   FROM customers_info 
 579                   WHERE DATE(customers_info_date_account_created) BETWEEN DATE_SUB(NOW(),INTERVAL 1 DAY) AND NOW()';
 580         $data  = $this->db->query($query)->row_array(1);
 581         if(null === $data['amount'])
 582         {
 583             $data['amount'] = '0';
 584         }
 585 
 586         return array_merge(array('period' => time() * 1000), $data);
 587     }
 588 
 589 
 590     /**
 591      * Helper function to fetch new customer data of a month time span.
 592      *
 593      * @param int $fromInterval
 594      *
 595      * @return array
 596      */
 597     protected function _getNewCustomersFromMonthIntervalHelper($fromInterval)
 598     {
 599         $query = 'SELECT COUNT(*) AS amount
 600                   FROM customers_info 
 601                   WHERE DATE(customers_info_date_account_created) BETWEEN DATE_SUB(CURDATE(),INTERVAL ' . $fromInterval
 602                  . ' MONTH) AND DATE_SUB(CURDATE(),INTERVAL ' . ((int)$fromInterval - 1) . ' MONTH)';
 603         $data  = $this->db->query($query)->row_array(1);
 604         if(null === $data['amount'])
 605         {
 606             $data['amount'] = '0';
 607         }
 608 
 609         return array_merge(array('period' => strtotime('-' . ((int)$fromInterval - 1) . ' months') * 1000), $data);
 610     }
 611 
 612 
 613     /**
 614      * Helper function to fetch new customer data of the current month.
 615      *
 616      * @return array
 617      */
 618     protected function _getNewCustomersFromCurrentMonthHelper()
 619     {
 620         $query = 'SELECT COUNT(*) AS amount
 621                   FROM customers_info 
 622                   WHERE DATE(customers_info_date_account_created) BETWEEN DATE_SUB(CURDATE(),INTERVAL 1 MONTH) AND CURDATE()';
 623         $data  = $this->db->query($query)->row_array(1);
 624         if(null === $data['amount'])
 625         {
 626             $data['amount'] = '0';
 627         }
 628 
 629         return array_merge(array('period' => time() * 1000), $data);
 630     }
 631 
 632 
 633     /**
 634      * Gets the amount of new customers of the current date
 635      *
 636      * @return int
 637      */
 638     public function getNewCustomersToday()
 639     {
 640         $result = $this->db->query('SELECT COUNT(*) AS new_customers 
 641                                             FROM customers_info 
 642                                             WHERE DATE(customers_info_date_account_created) = CURDATE()')->row_array(1);
 643 
 644         return (int)$result['new_customers'];
 645     }
 646 
 647 
 648     /**
 649      * Gets the amount of new customers of the last week
 650      *
 651      * @return int
 652      */
 653     public function getNewCustomersLastWeek()
 654     {
 655         $result = $this->db->query('SELECT COUNT(*) AS new_customers
 656                                   FROM customers_info 
 657                                   WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= DATE(customers_info_date_account_created)')
 658                            ->row_array(1);
 659 
 660         return (int)$result['new_customers'];
 661     }
 662 
 663 
 664     /**
 665      * Gets the amount of new customers of the last two week
 666      *
 667      * @return int
 668      */
 669     public function getNewCustomersLastTwoWeeks()
 670     {
 671         $result = $this->db->query('SELECT COUNT(*) AS new_customers
 672                                   FROM customers_info 
 673                                   WHERE DATE_SUB(CURDATE(),INTERVAL 14 DAY) <= DATE(customers_info_date_account_created)')
 674                            ->row_array(1);
 675 
 676         return (int)$result['new_customers'];
 677     }
 678 
 679 
 680     /**
 681      * Gets the amount of new customers of the last month
 682      *
 683      * @return int
 684      */
 685     public function getNewCustomersLastMonth()
 686     {
 687         $result = $this->db->query('SELECT COUNT(*) AS new_customers
 688                                   FROM customers_info 
 689                                   WHERE DATE_SUB(CURDATE(),INTERVAL 1 MONTH) <= DATE(customers_info_date_account_created)')
 690                            ->row_array(1);
 691 
 692         return (int)$result['new_customers'];
 693     }
 694 
 695 
 696     /**
 697      * Gets the amount of new customers of the last three months
 698      *
 699      * @return int
 700      */
 701     public function getNewCustomersLastThreeMonths()
 702     {
 703         $result = $this->db->query('SELECT COUNT(*) AS new_customers
 704                                   FROM customers_info 
 705                                   WHERE DATE_SUB(CURDATE(),INTERVAL 3 MONTH) <= DATE(customers_info_date_account_created)')
 706                            ->row_array(1);
 707 
 708         return (int)$result['new_customers'];
 709     }
 710 
 711 
 712     /**
 713      * Gets the amount of new customers of the last six months
 714      *
 715      * @return int
 716      */
 717     public function getNewCustomersLastSixMonths()
 718     {
 719         $result = $this->db->query('SELECT COUNT(*) AS new_customers
 720                                   FROM customers_info
 721                                   WHERE DATE_SUB(CURDATE(),INTERVAL 6 MONTH) <= DATE(customers_info_date_account_created)')
 722                            ->row_array(1);
 723 
 724         return (int)$result['new_customers'];
 725     }
 726 
 727 
 728     /**
 729      * Gets the amount of new customers of the last year
 730      *
 731      * @return int
 732      */
 733     public function getNewCustomersLastYear()
 734     {
 735         $result = $this->db->query('SELECT COUNT(*) AS new_customers
 736                                   FROM customers_info
 737                                   WHERE DATE_SUB(CURDATE(),INTERVAL 1 YEAR) <= DATE(customers_info_date_account_created)')
 738                            ->row_array(1);
 739 
 740         return (int)$result['new_customers'];
 741     }
 742 
 743 
 744     // -----------------------------------------------------------------------------------------------------------------
 745     // GET ORDERS COUNT
 746     // -----------------------------------------------------------------------------------------------------------------
 747 
 748     /**
 749      * Get orders data for the last week.
 750      *
 751      * @return array
 752      */
 753     public function getOrdersStatisticsDataLastWeek()
 754     {
 755         $returnArray = array();
 756         $dataArray   = array();
 757 
 758         for($i = 7; $i > 1; $i--)
 759         {
 760             $dataArray[] = $this->_getOrdersFromDayIntervalHelper($i);
 761         }
 762         $dataArray[] = $this->_getOrdersFromCurrentDayHelper();
 763 
 764         $returnArray['type'] = 'day';
 765         $returnArray['data'] = $dataArray;
 766 
 767         return $returnArray;
 768     }
 769 
 770 
 771     /**
 772      * Get orders data for the last two weeks.
 773      *
 774      * @return array
 775      */
 776     public function getOrdersStatisticsDataLastTwoWeek()
 777     {
 778         $returnArray = array();
 779         $dataArray   = array();
 780 
 781         for($i = 14; $i > 1; $i--)
 782         {
 783             $dataArray[] = $this->_getOrdersFromDayIntervalHelper($i);
 784         }
 785         $dataArray[] = $this->_getOrdersFromCurrentDayHelper();
 786 
 787         $returnArray['type'] = 'day';
 788         $returnArray['data'] = $dataArray;
 789 
 790         return $returnArray;
 791     }
 792 
 793 
 794     /**
 795      * Get orders data for the last month.
 796      *
 797      * @return array
 798      */
 799     public function getOrdersStatisticsDataLastMonth()
 800     {
 801         $returnArray = array();
 802         $dataArray   = array();
 803         $dayDiff     = time() - strtotime('-1 months');
 804         $days        = floor($dayDiff / (60 * 60 * 24));
 805 
 806         for($i = $days; $i > 1; $i--)
 807         {
 808             $dataArray[] = $this->_getOrdersFromDayIntervalHelper($i);
 809         }
 810         $dataArray[] = $this->_getOrdersFromCurrentDayHelper();
 811 
 812         $returnArray['type'] = 'day';
 813         $returnArray['data'] = $dataArray;
 814 
 815         return $returnArray;
 816     }
 817 
 818 
 819     /**
 820      * Get order data for the last three months.
 821      *
 822      * @return array
 823      */
 824     public function getOrderStatisticsDataLastThreeMonth()
 825     {
 826         $returnArray = array();
 827         $dataArray   = array();
 828 
 829         for($i = 3; $i > 1; $i--)
 830         {
 831             $dataArray[] = $this->_getOrdersFromMonthIntervalHelper($i);
 832         }
 833         $dataArray[] = $this->_getOrdersFromCurrentMonthHelper();
 834 
 835         $returnArray['type'] = 'month';
 836         $returnArray['data'] = $dataArray;
 837 
 838         return $returnArray;
 839     }
 840 
 841 
 842     /**
 843      * Get order data for the last six months.
 844      *
 845      * @return array
 846      */
 847     public function getOrderStatisticsDataLastSixMonth()
 848     {
 849         $returnArray = array();
 850         $dataArray   = array();
 851 
 852         for($i = 6; $i > 1; $i--)
 853         {
 854             $dataArray[] = $this->_getOrdersFromMonthIntervalHelper($i);
 855         }
 856         $dataArray[] = $this->_getOrdersFromCurrentMonthHelper();
 857 
 858         $returnArray['type'] = 'month';
 859         $returnArray['data'] = $dataArray;
 860 
 861         return $returnArray;
 862     }
 863 
 864 
 865     /**
 866      * Get order data for the last year.
 867      *
 868      * @return array
 869      */
 870     public function getOrderStatisticsDataLastYear()
 871     {
 872         $returnArray = array();
 873         $dataArray   = array();
 874 
 875         for($i = 12; $i > 1; $i--)
 876         {
 877             $dataArray[] = $this->_getOrdersFromMonthIntervalHelper($i);
 878         }
 879         $dataArray[] = $this->_getOrdersFromCurrentMonthHelper();
 880 
 881         $returnArray['type'] = 'month';
 882         $returnArray['data'] = $dataArray;
 883 
 884         return $returnArray;
 885     }
 886 
 887 
 888     /**
 889      * Helper function to fetch order data of a month time span.
 890      *
 891      * @param int $fromInterval
 892      *
 893      * @return array
 894      */
 895     protected function _getOrdersFromDayIntervalHelper($fromInterval)
 896     {
 897         $query = 'SELECT COUNT(*) AS amount 
 898                   FROM orders 
 899                   WHERE
 900                   orders_status NOT IN (' . implode(',', $this->excludeOrderStatusIds) . ') AND
 901                   DATE(date_purchased) BETWEEN DATE_SUB(NOW(),INTERVAL ' . $fromInterval
 902                  . ' DAY) AND DATE_SUB(NOW(),INTERVAL ' . ((int)$fromInterval - 1) . ' DAY)';
 903         $data  = $this->db->query($query)->row_array(1);
 904         if(null === $data['amount'])
 905         {
 906             $data['amount'] = '0';
 907         }
 908 
 909         return array_merge(array('period' => strtotime('-' . ((int)$fromInterval - 1) . ' days') * 1000), $data);
 910     }
 911 
 912 
 913     /**
 914      * Helper function to fetch order data of the current month.
 915      *
 916      * @return array
 917      */
 918     protected function _getOrdersFromCurrentDayHelper()
 919     {
 920         $query = 'SELECT COUNT(*) AS amount 
 921                   FROM orders 
 922                   WHERE
 923                   orders_status NOT IN (' . implode(',', $this->excludeOrderStatusIds) . ') AND
 924                   DATE(date_purchased) BETWEEN DATE_SUB(NOW(),INTERVAL 1 DAY) AND NOW()';
 925         $data  = $this->db->query($query)->row_array(1);
 926         if(null === $data['amount'])
 927         {
 928             $data['amount'] = '0';
 929         }
 930 
 931         return array_merge(array('period' => time() * 1000), $data);
 932     }
 933 
 934 
 935     /**
 936      * Helper function to fetch order data of a month time span.
 937      *
 938      * @param int $fromInterval
 939      *
 940      * @return array
 941      */
 942     protected function _getOrdersFromMonthIntervalHelper($fromInterval)
 943     {
 944         $query = 'SELECT COUNT(*) AS amount 
 945                   FROM orders 
 946                   WHERE
 947                   orders_status NOT IN (' . implode(',', $this->excludeOrderStatusIds) . ') AND
 948                   DATE(date_purchased) BETWEEN DATE_SUB(CURDATE(),INTERVAL ' . $fromInterval
 949                  . ' MONTH) AND DATE_SUB(CURDATE(),INTERVAL ' . ((int)$fromInterval - 1) . ' MONTH)';
 950         $data  = $this->db->query($query)->row_array(1);
 951         if(null === $data['amount'])
 952         {
 953             $data['amount'] = '0';
 954         }
 955 
 956         return array_merge(array('period' => strtotime('-' . ((int)$fromInterval - 1) . ' months') * 1000), $data);
 957     }
 958 
 959 
 960     /**
 961      * Helper function to fetch order data of the current month.
 962      *
 963      * @return array
 964      */
 965     protected function _getOrdersFromCurrentMonthHelper()
 966     {
 967         $query = 'SELECT COUNT(*) AS amount 
 968                   FROM orders 
 969                   WHERE
 970                   orders_status NOT IN (' . implode(',', $this->excludeOrderStatusIds) . ') AND
 971                   DATE(date_purchased) BETWEEN DATE_SUB(CURDATE(),INTERVAL 1 MONTH) AND CURDATE()';
 972         $data  = $this->db->query($query)->row_array(1);
 973         if(null === $data['amount'])
 974         {
 975             $data['amount'] = '0';
 976         }
 977 
 978         return array_merge(array('period' => time() * 1000), $data);
 979     }
 980 
 981 
 982     /**
 983      * Gets the orders count of today
 984      *
 985      * @return int
 986      */
 987     public function getOrdersCountToday()
 988     {
 989         $result = $this->db->query('SELECT COUNT(*) AS orders_count 
 990                                       FROM orders 
 991                                       WHERE 
 992                                         orders_status NOT IN (' . implode(',', $this->excludeOrderStatusIds) . ') AND
 993                                         DATE(date_purchased) = CURDATE()')->row_array(1);
 994 
 995         return (int)$result['orders_count'];
 996     }
 997 
 998 
 999     /**
1000      * Gets the orders count of the last week
1001      *
1002      * @return int
1003      */
1004     public function getOrdersCountLastWeek()
1005     {
1006         $result = $this->db->query('SELECT COUNT(*) AS orders_count 
1007                                       FROM orders 
1008                                       WHERE
1009                                         orders_status NOT IN (' . implode(',', $this->excludeOrderStatusIds) . ') AND
1010                                         DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= DATE(date_purchased)')->row_array(1);
1011 
1012         return (int)$result['orders_count'];
1013     }
1014 
1015 
1016     /**
1017      * Gets the orders count of the last two weeks
1018      *
1019      * @return int
1020      */
1021     public function getOrdersCountLastTwoWeeks()
1022     {
1023         $result = $this->db->query('SELECT COUNT(*) AS orders_count 
1024                                       FROM orders 
1025                                       WHERE
1026                                         orders_status NOT IN (' . implode(',', $this->excludeOrderStatusIds) . ') AND
1027                                         DATE_SUB(CURDATE(),INTERVAL 14 DAY) <= DATE(date_purchased)')->row_array(1);
1028 
1029         return (int)$result['orders_count'];
1030     }
1031 
1032 
1033     /**
1034      * Gets the orders count of the last month
1035      *
1036      * @return int
1037      */
1038     public function getOrdersCountLastMonth()
1039     {
1040         $result = $this->db->query('SELECT COUNT(*) AS orders_count 
1041                                   FROM orders 
1042                                   WHERE
1043                                         orders_status NOT IN (' . implode(',', $this->excludeOrderStatusIds) . ') AND
1044                                         DATE_SUB(CURDATE(),INTERVAL 1 MONTH) <= DATE(date_purchased)')->row_array(1);
1045 
1046         return (int)$result['orders_count'];
1047     }
1048 
1049 
1050     /**
1051      * Gets the orders count of the last three months
1052      *
1053      * @return int
1054      */
1055     public function getOrdersCountLastThreeMonths()
1056     {
1057         $result = $this->db->query('SELECT COUNT(*) AS orders_count 
1058                                   FROM orders 
1059                                   WHERE
1060                                         orders_status NOT IN (' . implode(',', $this->excludeOrderStatusIds) . ') AND
1061                                         DATE_SUB(CURDATE(),INTERVAL 3 MONTH) <= DATE(date_purchased)')->row_array(1);
1062 
1063         return (int)$result['orders_count'];
1064     }
1065 
1066 
1067     /**
1068      * Gets the orders count of the last six months
1069      *
1070      * @return int
1071      */
1072     public function getOrdersCountLastSixMonths()
1073     {
1074         $result = $this->db->query('SELECT COUNT(*) AS orders_count 
1075                                   FROM orders 
1076                                   WHERE
1077                                         orders_status NOT IN (' . implode(',', $this->excludeOrderStatusIds) . ') AND
1078                                         DATE_SUB(CURDATE(),INTERVAL 6 MONTH) <= DATE(date_purchased)')->row_array(1);
1079 
1080         return (int)$result['orders_count'];
1081     }
1082 
1083 
1084     /**
1085      * Gets the orders count of the last year
1086      *
1087      * @return int
1088      */
1089     public function getOrdersCountLastYear()
1090     {
1091         $result = $this->db->query('SELECT COUNT(*) AS orders_count 
1092                                   FROM orders 
1093                                   WHERE
1094                                         orders_status NOT IN (' . implode(',', $this->excludeOrderStatusIds) . ') AND
1095                                         DATE_SUB(CURDATE(),INTERVAL 1 YEAR) <= DATE(date_purchased)')->row_array(1);
1096 
1097         return (int)$result['orders_count'];
1098     }
1099 
1100 
1101     // -----------------------------------------------------------------------------------------------------------------
1102     // GET CONVERSION RATE
1103     // -----------------------------------------------------------------------------------------------------------------
1104 
1105     /**
1106      * Gets the conversion rate of today as a formatted number (i.e.: 0,27)
1107      *
1108      * @return string
1109      */
1110     public function getConversionRateToday()
1111     {
1112         return $this->_getConversionRate($this->getOrdersCountToday(), $this->getVisitorsToday());
1113     }
1114 
1115 
1116     /**
1117      * Gets the conversion rate of the last week as a formatted number (i.e.: 0,27)
1118      *
1119      * @return string
1120      */
1121     public function getConversionRateLastWeek()
1122     {
1123         return $this->_getConversionRate($this->getOrdersCountLastWeek(), $this->getVisitorsLastWeek());
1124     }
1125 
1126 
1127     /**
1128      * Gets the conversion rate of the last two weeks as a formatted number (i.e.: 0,27)
1129      *
1130      * @return string
1131      */
1132     public function getConversionRateLastTwoWeeks()
1133     {
1134         return $this->_getConversionRate($this->getOrdersCountLastTwoWeeks(), $this->getVisitorsLastTwoWeeks());
1135     }
1136 
1137 
1138     /**
1139      * Gets the conversion rate of the last month as a formatted number (i.e.: 0,27)
1140      *
1141      * @return string
1142      */
1143     public function getConversionRateLastMonth()
1144     {
1145         return $this->_getConversionRate($this->getOrdersCountLastMonth(), $this->getVisitorsLastMonth());
1146     }
1147 
1148 
1149     /**
1150      * Gets the conversion rate of the last three months as a formatted number (i.e.: 0,27)
1151      *
1152      * @return string
1153      */
1154     public function getConversionRateLastThreeMonths()
1155     {
1156         return $this->_getConversionRate($this->getOrdersCountLastThreeMonths(), $this->getVisitorsLastThreeMonths());
1157     }
1158 
1159 
1160     /**
1161      * Gets the conversion rate of the last six months as a formatted number (i.e.: 0,27)
1162      *
1163      * @return string
1164      */
1165     public function getConversionRateLastSixMonths()
1166     {
1167         return $this->_getConversionRate($this->getOrdersCountLastSixMonths(), $this->getVisitorsLastSixMonths());
1168     }
1169 
1170 
1171     /**
1172      * Gets the conversion rate of the last year as a formatted number (i.e.: 0,27)
1173      *
1174      * @return string
1175      */
1176     public function getConversionRateLastYear()
1177     {
1178         return $this->_getConversionRate($this->getOrdersCountLastYear(), $this->getVisitorsLastYear());
1179     }
1180 
1181 
1182     /**
1183      * Calculates the conversion rate
1184      *
1185      * @param int $p_ordersCount
1186      * @param int $p_visitorsCount
1187      *
1188      * @return double
1189      */
1190     protected function _calculateConversionRate($p_ordersCount, $p_visitorsCount)
1191     {
1192         $conversionRate = 0;
1193         $ordersCount    = (int)$p_ordersCount;
1194         $visitorsCount  = (int)$p_visitorsCount;
1195 
1196         if($visitorsCount > 0)
1197         {
1198             $conversionRate = round($ordersCount / $visitorsCount * 100, $this->conversionRateMaxDecimalPlaces);
1199         }
1200 
1201         return $conversionRate;
1202     }
1203 
1204 
1205     /**
1206      * Returns the conversion rate as a formatted number (i.e.: 0,27)
1207      *
1208      * @param int $p_ordersCount
1209      * @param int $p_visitorsCount
1210      *
1211      * @return string
1212      */
1213     protected function _getConversionRate($p_ordersCount, $p_visitorsCount)
1214     {
1215         $conversionRate = $this->_calculateConversionRate($p_ordersCount, $p_visitorsCount);
1216 
1217         $decimalPoint = ',';
1218 
1219         if(isset($this->xtcPrice->currencies[$this->xtcPrice->actualCurr]))
1220         {
1221             $decimalPoint = $this->xtcPrice->currencies[$this->xtcPrice->actualCurr]['decimal_point'];
1222         }
1223 
1224         $conversionRate = number_format($conversionRate, $this->conversionRateDecimalPlaces, $decimalPoint, '');
1225 
1226         return $conversionRate;
1227     }
1228 
1229 
1230     // -----------------------------------------------------------------------------------------------------------------
1231     // GET SALES
1232     // -----------------------------------------------------------------------------------------------------------------
1233 
1234     /**
1235      * Gets the amount of sales of the current date
1236      *
1237      * @return string
1238      */
1239     public function getSalesToday()
1240     {
1241         $formattedResult = $this->xtcPrice->xtcFormat($this->_getSalesToday(), true);
1242 
1243         return $formattedResult;
1244     }
1245 
1246 
1247     /**
1248      * Gets the amount of sales of the last week
1249      *
1250      * @return string
1251      */
1252     public function getSalesLastWeek()
1253     {
1254         $formattedResult = $this->xtcPrice->xtcFormat($this->_getSalesLastWeek(), true);
1255 
1256         return $formattedResult;
1257     }
1258 
1259 
1260     /**
1261      * Gets the amount of sales of the last two week
1262      *
1263      * @return string
1264      */
1265     public function getSalesLastTwoWeeks()
1266     {
1267         $formattedResult = $this->xtcPrice->xtcFormat($this->_getSalesLastTwoWeeks(), true);
1268 
1269         return $formattedResult;
1270     }
1271 
1272 
1273     /**
1274      * Gets the amount of sales of the last month
1275      *
1276      * @return string
1277      */
1278     public function getSalesLastMonth()
1279     {
1280         $formattedResult = $this->xtcPrice->xtcFormat($this->_getSalesLastMonth(), true);
1281 
1282         return $formattedResult;
1283     }
1284 
1285 
1286     /**
1287      * Gets the amount of sales of the last three months
1288      *
1289      * @return string
1290      */
1291     public function getSalesLastThreeMonths()
1292     {
1293         $formattedResult = $this->xtcPrice->xtcFormat($this->_getSalesLastThreeMonths(), true);
1294 
1295         return $formattedResult;
1296     }
1297 
1298 
1299     /**
1300      * Gets the amount of sales of the last six months
1301      *
1302      * @return string
1303      */
1304     public function getSalesLastSixMonths()
1305     {
1306         $formattedResult = $this->xtcPrice->xtcFormat($this->_getSalesLastSixMonths(), true);
1307 
1308         return $formattedResult;
1309     }
1310 
1311 
1312     /**
1313      * Gets the amount of sales of the last year
1314      *
1315      * @return string
1316      */
1317     public function getSalesLastYear()
1318     {
1319         $formattedResult = $this->xtcPrice->xtcFormat($this->_getSalesLastYear(), true);
1320 
1321         return $formattedResult;
1322     }
1323 
1324 
1325     /**
1326      * Get sales data for the last week.
1327      *
1328      * @return array
1329      */
1330     public function getSalesStatisticsDataLastWeek()
1331     {
1332         $returnArray = array();
1333         $dataArray   = array();
1334 
1335         for($i = 7; $i > 1; $i--)
1336         {
1337             $dataArray[] = $this->_getSalesFromDayIntervalHelper($i);
1338         }
1339         $dataArray[] = $this->_getSalesFromCurrentDayHelper();
1340 
1341         $returnArray['type'] = 'day';
1342         $returnArray['data'] = $dataArray;
1343 
1344         return $returnArray;
1345     }
1346 
1347 
1348     /**
1349      * Get sales data for the last two weeks.
1350      *
1351      * @return array
1352      */
1353     public function getSalesStatisticsDataLastTwoWeeks()
1354     {
1355         $returnArray = array();
1356         $dataArray   = array();
1357 
1358         for($i = 14; $i > 1; $i--)
1359         {
1360             $dataArray[] = $this->_getSalesFromDayIntervalHelper($i);
1361         }
1362         $dataArray[] = $this->_getSalesFromCurrentDayHelper();
1363 
1364         $returnArray['type'] = 'day';
1365         $returnArray['data'] = $dataArray;
1366 
1367         return $returnArray;
1368     }
1369 
1370 
1371     /**
1372      * Get sales data for the last month.
1373      *
1374      * @return array
1375      */
1376     public function getSalesStatisticsDataLastMonth()
1377     {
1378         $returnArray = array();
1379         $dataArray   = array();
1380         $dayDiff     = time() - strtotime('-1 months');
1381         $days        = floor($dayDiff / (60 * 60 * 24));
1382 
1383         for($i = $days; $i > 1; $i--)
1384         {
1385             $dataArray[] = $this->_getSalesFromDayIntervalHelper($i);
1386         }
1387         $dataArray[] = $this->_getSalesFromCurrentDayHelper();
1388 
1389         $returnArray['type'] = 'day';
1390         $returnArray['data'] = $dataArray;
1391 
1392         return $returnArray;
1393     }
1394 
1395 
1396     /**
1397      * Get sales data for the last three month.
1398      *
1399      * @return array
1400      */
1401     public function getSalesStatisticsDataLastThreeMonth()
1402     {
1403         $returnArray = array();
1404         $dataArray   = array();
1405 
1406         $dataArray[] = $this->_getSalesFromMonthIntervalHelper(3);
1407         $dataArray[] = $this->_getSalesFromMonthIntervalHelper(2);
1408         $dataArray[] = $this->_getSalesFromCurrentMonthHelper();
1409 
1410         $returnArray['type'] = 'month';
1411         $returnArray['data'] = $dataArray;
1412 
1413         return $returnArray;
1414     }
1415 
1416 
1417     /**
1418      * Get sales data for the last six month.
1419      *
1420      * @return array
1421      */
1422     public function getSalesStatisticsDataLastSixMonth()
1423     {
1424         $returnArray = array();
1425         $dataArray   = array();
1426 
1427         for($i = 6; $i > 1; $i--)
1428         {
1429             $dataArray[] = $this->_getSalesFromMonthIntervalHelper($i);
1430         }
1431         $dataArray[] = $this->_getSalesFromCurrentMonthHelper();
1432 
1433         $returnArray['type'] = 'month';
1434         $returnArray['data'] = $dataArray;
1435 
1436         return $returnArray;
1437     }
1438 
1439 
1440     /**
1441      * Get sales data for the last year.
1442      *
1443      * @return array
1444      */
1445     public function getSalesStatisticsDataLastYear()
1446     {
1447         $returnArray = array();
1448         $dataArray   = array();
1449 
1450         for($i = 12; $i > 1; $i--)
1451         {
1452             $dataArray[] = $this->_getSalesFromMonthIntervalHelper($i);
1453         }
1454         $dataArray[] = $this->_getSalesFromCurrentMonthHelper();
1455 
1456         $returnArray['type'] = 'month';
1457         $returnArray['data'] = $dataArray;
1458 
1459         return $returnArray;
1460     }
1461 
1462 
1463     /**
1464      * Helper function to fetch sales data of a day time span.
1465      *
1466      * @param int $fromInterval
1467      *
1468      * @return array
1469      */
1470     protected function _getSalesFromDayIntervalHelper($fromInterval)
1471     {
1472         $query = 'SELECT SUM(ot.value*o.currency_value) AS amount 
1473                   FROM orders o, orders_total ot 
1474                   WHERE 
1475                       o.orders_id = ot.orders_id AND
1476                       o.orders_status NOT IN (' . implode(',', $this->excludeOrderStatusIds) . ') AND
1477                       ot.class = "ot_total" AND
1478                       DATE(o.date_purchased) BETWEEN DATE_SUB(NOW(),INTERVAL ' . $fromInterval
1479                  . ' DAY) AND DATE_SUB(NOW(),INTERVAL ' . ((int)$fromInterval - 1) . ' DAY)';
1480         $data  = $this->db->query($query)->row_array(1);
1481         if(null === $data['amount'])
1482         {
1483             $data['amount'] = '0';
1484         }
1485 
1486         return array_merge(array('period' => strtotime('-' . ((int)$fromInterval - 1) . ' days') * 1000), $data);
1487     }
1488 
1489 
1490     /**
1491      * Helper function to fetch sales data of the current day.
1492      *
1493      * @return array
1494      */
1495     protected function _getSalesFromCurrentDayHelper()
1496     {
1497         $query = 'SELECT SUM(ot.value*o.currency_value) AS amount 
1498                   FROM orders o, orders_total ot 
1499                   WHERE 
1500                       o.orders_id = ot.orders_id AND
1501                       o.orders_status NOT IN (' . implode(',', $this->excludeOrderStatusIds) . ') AND
1502                       ot.class = "ot_total" AND
1503                       DATE(o.date_purchased) BETWEEN DATE_SUB(NOW(),INTERVAL 1 DAY) AND NOW()';
1504         $data  = $this->db->query($query)->row_array(1);
1505         if(null === $data['amount'])
1506         {
1507             $data['amount'] = '0';
1508         }
1509 
1510         return array_merge(array('period' => time() * 1000), $data);
1511     }
1512 
1513 
1514     /**
1515      * Helper function to fetch sales data of a month time span.
1516      *
1517      * @param int $fromInterval
1518      *
1519      * @return array
1520      */
1521     protected function _getSalesFromMonthIntervalHelper($fromInterval)
1522     {
1523         $query = 'SELECT SUM(ot.value*o.currency_value) AS amount 
1524                   FROM orders o, orders_total ot 
1525                   WHERE 
1526                       o.orders_id = ot.orders_id AND
1527                       o.orders_status NOT IN (' . implode(',', $this->excludeOrderStatusIds) . ') AND
1528                       ot.class = "ot_total" AND
1529                       DATE(o.date_purchased) BETWEEN DATE_SUB(CURDATE(),INTERVAL ' . $fromInterval
1530                  . ' MONTH) AND DATE_SUB(CURDATE(),INTERVAL ' . ((int)$fromInterval - 1) . ' MONTH)';
1531         $data  = $this->db->query($query)->row_array(1);
1532         if(null === $data['amount'])
1533         {
1534             $data['amount'] = '0';
1535         }
1536 
1537         return array_merge(array('period' => strtotime('-' . ((int)$fromInterval - 1) . ' months') * 1000), $data);
1538     }
1539 
1540 
1541     /**
1542      * Helper function to fetch sales data of the current month.
1543      *
1544      * @return array
1545      */
1546     protected function _getSalesFromCurrentMonthHelper()
1547     {
1548         $query = 'SELECT SUM(ot.value*o.currency_value) AS amount 
1549                   FROM orders o, orders_total ot 
1550                   WHERE 
1551                       o.orders_id = ot.orders_id AND
1552                       o.orders_status NOT IN (' . implode(',', $this->excludeOrderStatusIds) . ') AND
1553                       ot.class = "ot_total" AND
1554                       DATE(o.date_purchased) BETWEEN DATE_SUB(CURDATE(),INTERVAL 1 MONTH) AND CURDATE()';
1555         $data  = $this->db->query($query)->row_array(1);
1556         if(null === $data['amount'])
1557         {
1558             $data['amount'] = '0';
1559         }
1560 
1561         return array_merge(array('period' => time() * 1000), $data);
1562     }
1563 
1564 
1565     /**
1566      * @return double
1567      */
1568     protected function _getSalesToday()
1569     {
1570         $result = $this->db->query('SELECT SUM(ot.value * o.currency_value) AS sales 
1571                                     FROM orders o, orders_total ot 
1572                                     WHERE 
1573                                         o.orders_id = ot.orders_id AND
1574                                         o.orders_status NOT IN (' . implode(',', $this->excludeOrderStatusIds) . ') AND
1575                                         ot.class = "ot_total" AND
1576                                         DATE(o.date_purchased) = CURDATE()')->row_array(1);
1577 
1578         return (double)$result['sales'];
1579     }
1580 
1581 
1582     /**
1583      * @return double
1584      */
1585     protected function _getSalesLastWeek()
1586     {
1587         $result = $this->db->query('SELECT SUM(ot.value*o.currency_value) AS sales 
1588                                     FROM orders o, orders_total ot 
1589                                     WHERE 
1590                                         o.orders_id = ot.orders_id AND
1591                                         o.orders_status NOT IN (' . implode(',', $this->excludeOrderStatusIds) . ') AND
1592                                         ot.class = "ot_total" AND
1593                                         DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= DATE(o.date_purchased)')->row_array(1);
1594 
1595         return (double)$result['sales'];
1596     }
1597 
1598 
1599     /**
1600      * @return double
1601      */
1602     protected function _getSalesLastTwoWeeks()
1603     {
1604         $result = $this->db->query('SELECT SUM(ot.value*o.currency_value) AS sales 
1605                                     FROM orders o, orders_total ot 
1606                                     WHERE 
1607                                         o.orders_id = ot.orders_id AND
1608                                         o.orders_status NOT IN (' . implode(',', $this->excludeOrderStatusIds) . ') AND
1609                                         ot.class = "ot_total" AND
1610                                         DATE_SUB(CURDATE(),INTERVAL 14 DAY) <= DATE(o.date_purchased)')->row_array(1);
1611 
1612         return (double)$result['sales'];
1613     }
1614 
1615 
1616     /**
1617      * @return double
1618      */
1619     protected function _getSalesLastMonth()
1620     {
1621         $result = $this->db->query('SELECT SUM(ot.value*o.currency_value) AS sales 
1622                                     FROM orders o, orders_total ot 
1623                                     WHERE 
1624                                         o.orders_id = ot.orders_id AND
1625                                         o.orders_status NOT IN (' . implode(',', $this->excludeOrderStatusIds) . ') AND
1626                                         ot.class = "ot_total" AND
1627                                         DATE_SUB(CURDATE(),INTERVAL 1 MONTH) <= DATE(o.date_purchased)')->row_array(1);
1628 
1629         return (double)$result['sales'];
1630     }
1631 
1632 
1633     /**
1634      * @return double
1635      */
1636     protected function _getSalesLastThreeMonths()
1637     {
1638         $result = $this->db->query('SELECT SUM(ot.value*o.currency_value) AS sales 
1639                                     FROM orders o, orders_total ot 
1640                                     WHERE 
1641                                         o.orders_id = ot.orders_id AND
1642                                         o.orders_status NOT IN (' . implode(',', $this->excludeOrderStatusIds) . ') AND
1643                                         ot.class = "ot_total" AND
1644                                         DATE_SUB(CURDATE(),INTERVAL 3 MONTH) <= DATE(o.date_purchased)')->row_array(1);
1645 
1646         return (double)$result['sales'];
1647     }
1648 
1649 
1650     /**
1651      * @return double
1652      */
1653     protected function _getSalesLastSixMonths()
1654     {
1655         $result = $this->db->query('SELECT SUM(ot.value*o.currency_value) AS sales 
1656                                     FROM orders o, orders_total ot 
1657                                     WHERE 
1658                                         o.orders_id = ot.orders_id AND
1659                                         o.orders_status NOT IN (' . implode(',', $this->excludeOrderStatusIds) . ') AND
1660                                         ot.class = "ot_total" AND
1661                                         DATE_SUB(CURDATE(),INTERVAL 6 MONTH) <= DATE(o.date_purchased)')->row_array(1);
1662 
1663         return (double)$result['sales'];
1664     }
1665 
1666 
1667     /**
1668      * @return double
1669      */
1670     protected function _getSalesLastYear()
1671     {
1672         $result = $this->db->query('SELECT SUM(ot.value*o.currency_value) AS sales 
1673                                     FROM orders o, orders_total ot 
1674                                     WHERE 
1675                                         o.orders_id = ot.orders_id AND
1676                                         o.orders_status NOT IN (' . implode(',', $this->excludeOrderStatusIds) . ') AND
1677                                         ot.class = "ot_total" AND
1678                                         DATE_SUB(CURDATE(),INTERVAL 1 YEAR) <= DATE(o.date_purchased)')->row_array(1);
1679 
1680         return (double)$result['sales'];
1681     }
1682 
1683 
1684     // -----------------------------------------------------------------------------------------------------------------
1685     // GET AVERAGE ORDER VALUE
1686     // -----------------------------------------------------------------------------------------------------------------
1687 
1688     /**
1689      * Gets the average order value of today as a formatted price (i.e.: 1.234,56 EUR)
1690      *
1691      * @return string
1692      */
1693     public function getAverageOrderValueToday()
1694     {
1695         return $this->_getAverageOrderValue($this->_getSalesToday(), $this->getOrdersCountToday());
1696     }
1697 
1698 
1699     /**
1700      * Gets the average order value of the last week as a formatted price (i.e.: 1.234,56 EUR)
1701      *
1702      * @return string
1703      */
1704     public function getAverageOrderValueLastWeek()
1705     {
1706         return $this->_getAverageOrderValue($this->_getSalesLastWeek(), $this->getOrdersCountLastWeek());
1707     }
1708 
1709 
1710     /**
1711      * Gets the average order value of the last two weeks as a formatted price (i.e.: 1.234,56 EUR)
1712      *
1713      * @return string
1714      */
1715     public function getAverageOrderValueLastTwoWeeks()
1716     {
1717         return $this->_getAverageOrderValue($this->_getSalesLastTwoWeeks(), $this->getOrdersCountLastTwoWeeks());
1718     }
1719 
1720 
1721     /**
1722      * Gets the average order value of the last month as a formatted price (i.e.: 1.234,56 EUR)
1723      *
1724      * @return string
1725      */
1726     public function getAverageOrderValueLastMonth()
1727     {
1728         return $this->_getAverageOrderValue($this->_getSalesLastMonth(), $this->getOrdersCountLastMonth());
1729     }
1730 
1731 
1732     /**
1733      * Gets the average order value of the last three months as a formatted price (i.e.: 1.234,56 EUR)
1734      *
1735      * @return string
1736      */
1737     public function getAverageOrderValueLastThreeMonths()
1738     {
1739         return $this->_getAverageOrderValue($this->_getSalesLastThreeMonths(), $this->getOrdersCountLastThreeMonths());
1740     }
1741 
1742 
1743     /**
1744      * Gets the average order value of the last six months as a formatted price (i.e.: 1.234,56 EUR)
1745      *
1746      * @return string
1747      */
1748     public function getAverageOrderValueLastSixMonths()
1749     {
1750         return $this->_getAverageOrderValue($this->_getSalesLastSixMonths(), $this->getOrdersCountLastSixMonths());
1751     }
1752 
1753 
1754     /**
1755      * Gets the average order value of the last year as a formatted price (i.e.: 1.234,56 EUR)
1756      *
1757      * @return string
1758      */
1759     public function getAverageOrderValueLastYear()
1760     {
1761         return $this->_getAverageOrderValue($this->_getSalesLastYear(), $this->getOrdersCountLastYear());
1762     }
1763 
1764 
1765     /**
1766      * Calculates the average order value
1767      *
1768      * @param double $p_sales
1769      * @param int    $p_ordersCount
1770      *
1771      * @return double
1772      */
1773     protected function _calculateAverageOrderValue($p_sales, $p_ordersCount)
1774     {
1775         $averageOrderValue = 0;
1776         $sales             = (double)$p_sales;
1777         $ordersCount       = (int)$p_ordersCount;
1778 
1779         if($ordersCount > 0)
1780         {
1781             $averageOrderValue = $sales / $ordersCount;
1782         }
1783 
1784         return $averageOrderValue;
1785     }
1786 
1787 
1788     /**
1789      * Returns the conversion rate as a formatted number (i.e.: 0,27)
1790      *
1791      * @param double $p_sales
1792      * @param int    $p_ordersCount
1793      *
1794      * @return string
1795      */
1796     protected function _getAverageOrderValue($p_sales, $p_ordersCount)
1797     {
1798         $averageOrderValue = $this->_calculateAverageOrderValue($p_sales, $p_ordersCount);
1799         $averageOrderValue = $this->xtcPrice->xtcFormat($averageOrderValue, true);
1800 
1801         return $averageOrderValue;
1802     }
1803 }

API documentation generated by ApiGen