SHARE
TWEET

StatsData.class.php

a guest Jun 21st, 2018 345 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. <?php
  2.  
  3. /**
  4.  * phpVMS - Virtual Airline Administration Software
  5.  * Copyright (c) 2008 Nabeel Shahzad
  6.  * For more information, visit www.phpvms.net
  7.  *  Forums: http://www.phpvms.net/forum
  8.  *  Documentation: http://www.phpvms.net/docs
  9.  *
  10.  * phpVMS is licenced under the following license:
  11.  *   Creative Commons Attribution Non-commercial Share Alike (by-nc-sa)
  12.  *   View license.txt in the root, or visit http://creativecommons.org/licenses/by-nc-sa/3.0/
  13.  *
  14.  * @author Nabeel Shahzad
  15.  * @copyright Copyright (c) 2008, Nabeel Shahzad
  16.  * @link http://www.phpvms.net
  17.  * @license http://creativecommons.org/licenses/by-nc-sa/3.0/
  18.  */
  19.  
  20. class StatsData extends CodonData {
  21.    
  22.     /**
  23.      * Return the total from a table given the conditions specified
  24.      * Also handle any caching for said query
  25.      *
  26.      * @param array $params See function for parameters
  27.      * @return int Total number
  28.      */
  29.     public static function getTotalForCol($params) {
  30.        
  31.         $params = array_merge(array(
  32.             'table' => '',
  33.             'column' => '',
  34.             'airline_code' => '', // optional
  35.             'where' => array(),  // optional
  36.             'func' => 'COUNT', //optional
  37.             ), $params
  38.         );
  39.        
  40.         if($params['table'] == '' || $params['table'] == '') {
  41.             return false;
  42.         }
  43.        
  44.         if($params['func'] == '') {
  45.             $params['func'] = 'COUNT';
  46.         }
  47.        
  48.         if(!is_array($params['where'])) {
  49.             $params['where'] = array();
  50.         }
  51.        
  52.         if(!empty($params['airline_code'])) {
  53.             $params['airline_code'] = strtoupper($params['airline_code']);
  54.             $params['where']['code'] = $params['airline_code'];
  55.         }
  56.        
  57.         $mixed = substr(md5(implode('', $params)), 0, 8);
  58.         $key = 'total_'.$mixed;
  59.        
  60.         $total = CodonCache::read($key);
  61.        
  62.         if($total === false) {
  63.            
  64.             $params['column'] = trim($params['column']);
  65.             if($params['column'] != '*') {
  66.                 $params['column'] = '`'.$params['column'].'`';
  67.             }
  68.        
  69.             $sql="SELECT ".$params['func']."(".$params['column'].") as `total` "
  70.                 ."FROM ".TABLE_PREFIX.$params['table'];
  71.            
  72.             $sql .= DB::build_where($params['where']);
  73.             $total = DB::get_row($sql);
  74.                                    
  75.             if(!$total) {
  76.                 $total = 0;
  77.             } else {
  78.                 $total = $total->total;
  79.             }
  80.            
  81.             CodonCache::write($key, $total, '15minute');
  82.         }
  83.        
  84.         return $total;
  85.     }
  86.    
  87.     /**
  88.      * Get the date of the very first PIREP, use this as the "start date"
  89.      * of the VA
  90.      *
  91.      * @return array, PIREP ID and submit date
  92.      */
  93.     public static function getStartDate() {
  94.        
  95.         $start_date = CodonCache::read('start_date');
  96.  
  97.         if ($start_date === false) {
  98.             $sql = 'SELECT `pirepid`, `submitdate`
  99.                     FROM ' . TABLE_PREFIX . 'pireps
  100.                     ORDER BY `submitdate` ASC
  101.                     LIMIT 1';
  102.  
  103.             $start_date = DB::get_row($sql);
  104.             CodonCache::write('start_date', $start_date, 'long');
  105.         }
  106.  
  107.         return $start_date;
  108.     }
  109.  
  110.     /**
  111.      * Get all of the months since the VA started
  112.      */
  113.     public static function getMonthsSinceStart() {
  114.        
  115.         $months_list = CodonCache::read('months_since_start');
  116.  
  117.         if ($months_list === false) {
  118.             $date = self::GetStartDate();
  119.  
  120.             if (!$date) {
  121.                 $startdate = time();
  122.             } else  {
  123.                 $startdate = $date->submitdate;
  124.             }
  125.  
  126.             $months_list = self::getMonthsSinceDate($startdate);
  127.             CodonCache::write('months_since_start', $months_list, 'long');
  128.         }
  129.  
  130.         return $months_list;
  131.     }
  132.  
  133.     /**
  134.      * Get years since the VA started
  135.      */
  136.     public static function getYearsSinceStart() {
  137.        
  138.         $key = 'years_since_start';
  139.         $years_start = CodonCache::read($key);
  140.  
  141.         if ($years_start === false) {
  142.             $date = self::getStartDate();
  143.  
  144.             if (!$date) {
  145.                 $startdate = 'Today';
  146.             } else {
  147.                 $startdate = $date->submitdate;
  148.             }
  149.  
  150.             $start = strtotime($startdate);
  151.             $end = date('Y');
  152.             do {
  153.                 $year = date('Y', $start); # Get the months
  154.                $years[$year] = $start; # Set the timestamp
  155.                $start = strtotime('+1 Year', $start);
  156.  
  157.             } while ($year < $end);
  158.  
  159.             $years_start = array_reverse($years, true);
  160.             CodonCache::write($key, $years_start, 'long');
  161.         }
  162.  
  163.         return $years_start;
  164.     }
  165.  
  166.     /**
  167.      * Get all of the months since a certain date
  168.      */
  169.     public static function getMonthsSinceDate($start) {
  170.        
  171.         $key_month = date('MY', $start);
  172.         $key = 'months_since_' . $key_month;
  173.         $months = CodonCache::read($key);
  174.  
  175.         if ($months === false) {
  176.             if (!is_numeric($start)) {
  177.                 $start = strtotime($start);
  178.             }
  179.  
  180.             $end = date('Ym');
  181.  
  182.             do {
  183.                 # Get the months
  184.                $month = date('M Y', $start);
  185.                 $months[$month] = $start; # Set the timestamp
  186.                $start = strtotime('+1 month +1 day', strtotime($month));
  187.  
  188.                 # Convert to YYYYMM to compare
  189.                $check = intval(date('Ym', $start));
  190.  
  191.             } while ($check <= $end);
  192.  
  193.             CodonCache::write($key, $months, 'long');
  194.         }
  195.  
  196.         return $months;
  197.     }
  198.    
  199.     public static function TotalPilotMiles($pilotid)
  200.     {
  201.         $key = 'total_miles';
  202.         $key .= '_'.$pilotid;
  203.  
  204.         $total = CodonCache::read($key);
  205.  
  206.         if($total === false)
  207.         {
  208.             $total = 0;
  209.             $sql = "SELECT * FROM ".TABLE_PREFIX."pireps WHERE pilotid='$pilotid' AND accepted=1";
  210.             $results = DB::get_results($sql);
  211.             if($results) { foreach($results as $result) { $total += $result->distance; } }
  212.  
  213.             CodonCache::write($key, $total, '15minute');
  214.         }
  215.        
  216.         return $total;
  217.     }
  218.  
  219.     /**
  220.      * Get all the months within a certain range
  221.      * Pass timestamp, or textual date
  222.      */
  223.     public static function getMonthsInRange($start, $end) {
  224.        
  225.         $key = "months_in_{$start}_{$end}";
  226.         $months = CodonCache::read($key);
  227.  
  228.         if ($months === false) {
  229.            
  230.             if (!is_numeric($start)) {
  231.                 $start = strtotime($start);
  232.             }
  233.  
  234.             if (!is_numeric($end)) {
  235.                 $end = strtotime($end);
  236.             }
  237.  
  238.             $end = intval(date('Ym', $end));
  239.  
  240.             /* Loop through, adding one month to $start each time
  241.             */
  242.             do {
  243.                 $month = date('M Y', $start); # Get the month
  244.                $months[$month] = $start; # Set the timestamp
  245.                $start = strtotime('+1 month +1 day', strtotime($month));
  246.                 //$start += (SECONDS_PER_DAY * 25); # Move it up a month
  247.  
  248.                 $check = intval(date('Ym', $start));
  249.  
  250.             } while ($check <= $end);
  251.  
  252.             CodonCache::write($key, $months, 'long');
  253.         }
  254.  
  255.         return $months;
  256.     }
  257.  
  258.     /**
  259.      * StatsData::updateTotalHours()
  260.      *
  261.      * @return
  262.      */
  263.     public static function updateTotalHours() {
  264.        
  265.         $sql = "SELECT SUM(TIME_TO_SEC(flighttime_stamp)) AS `total`
  266.                FROM `".TABLE_PREFIX."pireps`
  267.                WHERE `accepted`=".PIREP_ACCEPTED;
  268.        
  269.         $totaltime = DB::get_row($sql);
  270.         if(!$totaltime) {
  271.             $totaltime = '00:00:00';
  272.         } else {
  273.             $totaltime = Util::secondsToTime($totaltime->total);
  274.         }
  275.  
  276.         SettingsData::SaveSetting('TOTAL_HOURS', $totaltime);
  277.     }
  278.  
  279.     /**
  280.      * Get the total number of hours flown by pilots
  281.      */
  282.     public static function TotalHours() {
  283.         return SettingsData::GetSettingValue('TOTAL_HOURS');
  284.     }
  285.  
  286.     /**
  287.      * Get the top routes
  288.      */
  289.     public static function TopRoutes($airline_code = '') {
  290.        
  291.         $key = 'top_routes';
  292.         if ($airline_code != '') {
  293.             $key .= '_' . $airline_code;
  294.         }
  295.  
  296.         $top_routes = CodonCache::read($key);
  297.         if ($top_routes === false) {
  298.             $sql = 'SELECT * FROM `' . TABLE_PREFIX . 'schedules`';
  299.  
  300.             if ($airline_code != '') {
  301.                 $sql .= " WHERE `code`='{$airline_code}'";
  302.             }
  303.  
  304.             $sql = $sql . ' ORDER BY `timesflown` DESC LIMIT 10';
  305.  
  306.             $top_routes = DB::get_results($sql);
  307.             CodonCache::write($key, $top_routes, 'medium');
  308.         }
  309.         return $top_routes;
  310.     }
  311.  
  312.     /**
  313.      * StatsData::UsersOnline()
  314.      *
  315.      * @param string $minutes
  316.      * @return
  317.      */
  318.     public static function UsersOnline($minutes = '') {
  319.        
  320.         $key = 'users_online';
  321.         $users_online = CodonCache::read($key);
  322.  
  323.         if ($users_online === false) {
  324.             if ($minutes == '') $minutes = Config::Get('USERS_ONLINE_TIME');
  325.  
  326.             $sql = "SELECT p.*
  327.                     FROM " . TABLE_PREFIX . "pilots p, " . TABLE_PREFIX . "sessions s
  328.                     WHERE s.pilotid = p.pilotid
  329.                     AND DATE_SUB(NOW(), INTERVAL {$minutes} MINUTE) <= s.`logintime`";
  330.  
  331.             $users_online = DB::get_results($sql);
  332.  
  333.             # Check if it's blank, then return an empty array
  334.            if (!$users_online) $users_online = array();
  335.  
  336.             CodonCache::write($key, $users_online, 'short');
  337.         }
  338.  
  339.         return $users_online;
  340.     }
  341.  
  342.     /**
  343.      * StatsData::GuestsOnline()
  344.      *
  345.      * @param string $minutes
  346.      * @return
  347.      */
  348.     public static function GuestsOnline($minutes = '') {
  349.        
  350.         $key = 'guests_online';
  351.         $guests_online = CodonCache::read($key);
  352.  
  353.         if ($guests_online === false) {
  354.             if ($minutes == '') $minutes = Config::Get('USERS_ONLINE_TIME');
  355.  
  356.             $sql = "SELECT s.*
  357.                     FROM " . TABLE_PREFIX . "sessions s
  358.                     WHERE s.pilotid = 0
  359.                     AND DATE_SUB(NOW(), INTERVAL {$minutes} MINUTE) <= s.`logintime`";
  360.  
  361.             $guests_online = DB::get_results($sql);
  362.  
  363.             if (!$guests_online) $guests_online = array();
  364.  
  365.             CodonCache::write($key, $guests_online, 'short');
  366.         }
  367.  
  368.         return $guests_online;
  369.     }
  370.  
  371.     /**
  372.      * Get the current aircraft usage
  373.      */
  374.     public static function AircraftUsage() {
  375.        
  376.         $key = 'stats_aircraft_usage';
  377.  
  378.         $aircraft_usage = CodonCache::read($key);
  379.         if ($aircraft_usage === false) {
  380.             //SEC_TO_TIME(SUM(p.flighttime*60*60)) AS totaltime,
  381.             $sql = 'SELECT a.*, a.name AS aircraft,
  382.                         COUNT(p.pirepid) AS routesflown,
  383.                         SUM(p.distance) AS distance,
  384.                         SEC_TO_TIME(SUM(TIME_TO_SEC(p.flighttime_stamp))) as totaltime,
  385.                         AVG(p.distance) AS averagedistance,
  386.                         AVG(p.flighttime) as averagetime
  387.                     FROM   ' . TABLE_PREFIX . 'aircraft a
  388.                     INNER JOIN ' . TABLE_PREFIX . 'pireps p ON (p.aircraft = a.id)
  389.                     GROUP BY a.registration';
  390.  
  391.             $aircraft_usage = DB::get_results($sql);
  392.             CodonCache::write($key, $aircraft_usage, 'short');
  393.         }
  394.  
  395.         return $aircraft_usage;
  396.     }
  397.  
  398.     /**
  399.      * Show pie chart for all of the aircraft flown
  400.      *  by a certain pilot. Outputs image, unless $ret == true,
  401.      *  then it returns the URL.
  402.      */
  403.     public static function AircraftFlownGraph($ret = false) {
  404.        
  405.         //Select aircraft types
  406.         $sql = 'SELECT a.name AS aircraft, COUNT(p.aircraft) AS count
  407.                 FROM ' . TABLE_PREFIX . 'pireps p, ' . TABLE_PREFIX . 'aircraft a
  408.                 WHERE p.aircraft = a.id
  409.                 GROUP BY a.name';
  410.  
  411.         $stats = DB::get_results($sql);
  412.  
  413.         if (!$stats) {
  414.             return;
  415.         }
  416.  
  417.         $data = '';
  418.         $labels = '';
  419.         foreach ($stats as $stat) {
  420.             if ($stat->aircraft == '') continue;
  421.  
  422.             $data .= $stat->count . ',';
  423.             $labels .= $stat->aircraft . '|';
  424.         }
  425.  
  426.         // remove that final lone char
  427.         $data = substr($data, 0, strlen($data) - 1);
  428.         $labels = substr($labels, 0, strlen($labels) - 1);
  429.  
  430.         $chart = new googleChart($data, 'pie');
  431.         $chart->dimensions = '350x200';
  432.         $chart->setLabels($labels);
  433.  
  434.         if ($ret == true) return $chart->draw(false);
  435.         else  echo '<img src="' . $chart->draw(false) . '" />';
  436.     }
  437.  
  438.     /**
  439.      * StatsData::PilotAircraftFlownCounts()
  440.      *
  441.      * @param mixed $pilotid
  442.      * @return
  443.      */
  444.     public static function PilotAircraftFlownCounts($pilotid) {
  445.        
  446.         $key = 'ac_flown_counts_' . $pilotid;
  447.  
  448.         $counts = CodonCache::read($key);
  449.         if ($counts === false) {
  450.             //Select aircraft types
  451.             $sql = 'SELECT a.name AS aircraft, COUNT(p.aircraft) AS count, SUM(p.flighttime) AS hours
  452.                     FROM ' . TABLE_PREFIX . 'pireps p, ' . TABLE_PREFIX . 'aircraft a
  453.                     WHERE p.aircraft = a.id AND p.pilotid=' . intval($pilotid) . '
  454.                     GROUP BY a.name';
  455.  
  456.             $counts = DB::get_results($sql);
  457.             CodonCache::write($key, $counts, 'medium');
  458.         }
  459.  
  460.         return $counts;
  461.     }
  462.  
  463.     /**
  464.      * Show pie chart for all of the aircraft flown
  465.      *  by a certain pilot. Outputs image, unless $ret == true,
  466.      *  then it returns the URL.
  467.      *
  468.      * @param int $pilotid The ID of the pilot
  469.      * @param bool $ret Return the URL, or display it in an IMG tag
  470.      */
  471.     public static function PilotAircraftFlownGraph($pilotid, $ret = false) {
  472.        
  473.         $stats = self::PilotAircraftFlownCounts($pilotid);
  474.  
  475.         if (!$stats) {
  476.             return;
  477.         }
  478.  
  479.         $data = '';
  480.         $labels = '';
  481.         foreach ($stats as $stat) {
  482.             if ($stat->aircraft == '') continue;
  483.  
  484.             $data .= $stat->count . ',';
  485.             $labels .= $stat->aircraft . '|';
  486.         }
  487.  
  488.         // remove that final lone char
  489.         $data = substr($data, 0, strlen($data) - 1);
  490.         $labels = substr($labels, 0, strlen($labels) - 1);
  491.  
  492.         $chart = new GoogleChart($data, 'pie');
  493.         $chart->dimensions = '350x200';
  494.         $chart->setLabels($labels);
  495.  
  496.  
  497.         $url = $chart->draw(false);
  498.         unset($chart);
  499.  
  500.         if ($ret == true) return $url;
  501.         else  echo '<img src="' . $url . '" alt="" />';
  502.     }
  503.  
  504.     /* These contributed by simpilot from phpVMS forums
  505.     */
  506.  
  507.     /**
  508.      * Get the total number of pilots
  509.      */
  510.     public static function PilotCount($airline_code = '') {
  511.        
  512.         return self::getTotalForCol(array(
  513.             'table' => 'pilots',
  514.             'column' => '*',
  515.             'airline_code' => $airline_code,
  516.             )
  517.         );
  518.        
  519.     }
  520.  
  521.     /**
  522.      * Get the total number of flights flown
  523.      */
  524.     public static function TotalFlights($airline_code = '') {
  525.        
  526.         return self::getTotalForCol(array(
  527.             'table' => 'pireps',
  528.             'column' => '*',
  529.             'airline_code' => $airline_code,
  530.             'where' => array('accepted' => PIREP_ACCEPTED),
  531.             'func' => 'COUNT',
  532.             )
  533.         );
  534.        
  535.     }
  536.  
  537.     /**
  538.      * Return the total number of passengers carried
  539.      *
  540.      * @param string $airline_code Airline code specifically to call for, optional
  541.      * @return int
  542.      *
  543.      */
  544.     public static function TotalPaxCarried($airline_code = '') {
  545.        
  546.         return self::getTotalForCol(array(
  547.             'table' => 'pireps',
  548.             'column' => 'load',
  549.             'airline_code' => $airline_code,
  550.             'where' => array(
  551.                 'accepted' => PIREP_ACCEPTED,
  552.                 'flighttype' => 'P',
  553.             ),
  554.             'func' => 'SUM',
  555.             )
  556.         );
  557.     }
  558.  
  559.  
  560.     /**
  561.      * Return the number of flights flown today
  562.      *
  563.      * @return int Total number of flights
  564.      *
  565.      */
  566.     public static function TotalFlightsToday($airline_code = '') {
  567.        
  568.         return self::getTotalForCol(array(
  569.             'table' => 'pireps',
  570.             'column' => '*',
  571.             'airline_code' => $airline_code,
  572.             'where' => array('DATE(`submitdate`) = CURDATE()')
  573.             )
  574.         );
  575.        
  576.     }
  577.  
  578.     /**
  579.      * Total amount of fuel burned among all accepted PIREPS
  580.      *
  581.      * @return float In units specified in config
  582.      *
  583.      */
  584.     public static function TotalFuelBurned($airline_code = '') {
  585.    
  586.         return self::getTotalForCol(array(
  587.             'table' => 'pireps',
  588.             'column' => 'fuelused',
  589.             'airline_code' => $airline_code,
  590.             'where' => array('accepted' => PIREP_ACCEPTED),
  591.             'func' => 'SUM',
  592.             )
  593.         );
  594.    
  595.     }
  596.  
  597.     /**
  598.      * Get the total miles/km flown
  599.      *
  600.      * @return float Total distance flown in units in config
  601.      *
  602.      */
  603.     public static function TotalMilesFlown($airline_code = '') {
  604.           return self::TotalDistanceFlown($airline_code);      
  605.     }
  606.    
  607.     /**
  608.      * Get the total miles/km flown
  609.      *
  610.      * @return float Total distance flown in units in config
  611.      *
  612.      */
  613.     public static function TotalDistanceFlown($airline_code = '') {
  614.         return self::getTotalForCol(array(
  615.             'table' => 'pireps',
  616.             'column' => 'distance',
  617.             'airline_code' => $airline_code,
  618.             'where' => array('accepted' => PIREP_ACCEPTED),
  619.             'func' => 'SUM',
  620.             )
  621.         );
  622.     }
  623.  
  624.  
  625.     /**
  626.      * Return the total number of aircraft in the fleet
  627.      *
  628.      * @return int Total
  629.      *
  630.      */
  631.     public static function TotalAircraftInFleet($airline_code = '') {
  632.        
  633.         return self::getTotalForCol(array(
  634.             'table' => 'aircraft',
  635.             'column' => 'id',
  636.             'airline_code' => $airline_code,
  637.         ));
  638.        
  639.     }
  640.  
  641.  
  642.     /**
  643.      * Return the total number of news stories
  644.      *
  645.      * @return int Total
  646.      *
  647.      */
  648.     public static function TotalNewsItems() {
  649.        
  650.         return self::getTotalForCol(array(
  651.             'table' => 'news',
  652.             'column' => 'id',
  653.             )
  654.         );
  655.        
  656.     }
  657.  
  658.  
  659.     /**
  660.      * Return the total number of schedules in the system
  661.      *
  662.      * @return int $airline_code Total number
  663.      *
  664.      */
  665.     public static function TotalSchedules($airline_code = '') {
  666.        
  667.         return self::getTotalForCol(array(
  668.             'table' => 'schedules',
  669.             'column' => 'id',
  670.             'airline_code' => $airline_code,
  671.             )
  672.         );
  673.                
  674.     }
  675. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top