Guest User

StatsData.class.php

a guest
Jun 21st, 2018
644
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

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×