/** * Get the total number of schedules based on criterea * * @param array $params key => value list * @return int Returns the total number * */ public static function countSchedules($params) { $sql = 'SELECT COUNT(s.id) as total FROM ' . TABLE_PREFIX . 'schedules AS s LEFT JOIN ' . TABLE_PREFIX . 'airports AS dep ON dep.icao = s.depicao LEFT JOIN ' . TABLE_PREFIX . 'airports AS arr ON arr.icao = s.arricao LEFT JOIN ' . TABLE_PREFIX . 'aircraft AS a ON a.id = s.aircraft '; $sql .= DB::build_where($params); $res = DB::get_row($sql); return $res->total; }
/** * ActivityData::getActivity() * * @param integer $count * @return void */ public static function getActivity($where = '', $limit = 10, $start = '') { $sql = "SELECT p.*, a.*\r\n\t\t\t\tFROM `" . TABLE_PREFIX . "activityfeed` a\r\n\t\t\t\tLEFT JOIN `" . TABLE_PREFIX . "pilots` p ON p.pilotid=a.pilotid "; if (is_array($where)) { $sql .= DB::build_where($where); } $sql .= ' ORDER BY `submitdate` DESC'; if (strlen($limit) != 0) { $sql .= ' LIMIT ' . $limit; } if (strlen($start) != 0) { $sql .= ' OFFSET ' . $start; } $ret = DB::get_results($sql); return $ret; }
public static function findLogs($params, $count = '', $start = '') { $sql = 'SELECT l.*, p.* FROM ' . TABLE_PREFIX . 'adminlog l INNER JOIN ' . TABLE_PREFIX . 'pilots p ON l.pilotid = p.pilotid '; /* Build the select "WHERE" based on the columns passed */ $sql .= DB::build_where($params); $sql .= ' ORDER BY l.datestamp DESC'; if (strlen($count) != 0) { $sql .= ' LIMIT ' . $count; } if (strlen($start) != 0) { $sql .= ' OFFSET ' . $start; } $ret = DB::get_results($sql); return $ret; }
/** * Find any pilots based on the parameters passed in * * @param array $params All the parameters * @param int $limit Number of results to return * @param int $start Record to start from * @return array Returns all the pilots requested * */ public static function findPilots($params, $limit = '', $start = '') { $sql = "SELECT p.*, r.`rankimage`, r.`payrate`\n\t\t\t\tFROM " . TABLE_PREFIX . "pilots p\n\t\t\t\tLEFT JOIN " . TABLE_PREFIX . "ranks r ON r.rank=p.rank "; /* Build the select "WHERE" based on the columns passed, this is a generic function */ $sql .= DB::build_where($params); // Order matters if (Config::Get('PILOT_ORDER_BY') != '') { $sql .= ' ORDER BY ' . Config::Get('PILOT_ORDER_BY'); } if (strlen($limit) != 0) { $sql .= ' LIMIT ' . $limit; } if (strlen($start) != 0) { $sql .= ' OFFSET ' . $start; } $ret = DB::get_results($sql); return $ret; }
public static function findAirport($params, $count = '', $start = '', $order_by = '') { $sql = 'SELECT * FROM ' . TABLE_PREFIX . 'airports '; /* Build the select "WHERE" based on the columns passed, this is a generic function */ $sql .= DB::build_where($params); // Order matters if (strlen($order_by) > 0) { $sql .= ' ORDER BY ' . $order_by; } if (strlen($count) != 0) { $sql .= ' LIMIT ' . $count; } if (strlen($start) != 0) { $sql .= ' OFFSET ' . $start; } $ret = DB::get_results($sql); return $ret; }
/** * Return the total from a table given the conditions specified * Also handle any caching for said query * * @param array $params See function for parameters * @return int Total number */ public static function getTotalForCol($params) { $params = array_merge(array('table' => '', 'column' => '', 'airline_code' => '', 'where' => array(), 'func' => 'COUNT'), $params); if ($params['table'] == '' || $params['table'] == '') { return false; } if ($params['func'] == '') { $params['func'] = 'COUNT'; } if (!is_array($params['where'])) { $params['where'] = array(); } if (!empty($params['airline_code'])) { $params['airline_code'] = strtoupper($params['airline_code']); $params['where']['code'] = $params['airline_code']; } $mixed = substr(md5(implode('', $params)), 0, 8); $key = 'total_' . $mixed; $total = CodonCache::read($key); if ($total === false) { $params['column'] = trim($params['column']); if ($params['column'] != '*') { $params['column'] = '`' . $params['column'] . '`'; } $sql = "SELECT " . $params['func'] . "(" . $params['column'] . ") as `total` " . "FROM " . TABLE_PREFIX . $params['table']; $sql .= DB::build_where($params['where']); $total = DB::get_row($sql); if (!$total) { $total = 0; } else { $total = $total->total; } CodonCache::write($key, $total, '15minute'); } return $total; }
/** * Get interval financial data, with the date clause * passed in as a WHERE: * * "DATE_SUB(CURDATE(), INTERVAL {$interval} DAY) <= p.submitdate"; * * Or some form of a date limitation, but has to be within the * where clause * * @param array $where_params Any WHERE parameters * @param char $grouping How to group data - Y for yearly, M for monthly, D for daily * @return array Returns finance data according to the above grouping * */ public static function getIntervalData($where_params, $grouping = 'M') { $grouping = strtolower($grouping); if ($grouping == 'y') { $format = '%Y'; } elseif ($grouping == 'm') { $format = '%Y-%m'; } elseif ($grouping == 'd') { $format = '%Y-%m-%d'; } $sql = "SELECT DATE_FORMAT(p.submitdate, '{$format}') AS ym,\n\t\t\t\t\tUNIX_TIMESTAMP(p.submitdate) AS timestamp,\n\t\t\t\t\tCOUNT(p.pirepid) AS total,\n\t\t\t\t\tSUM(p.revenue) as revenue,\n\t\t\t\t\tSUM(p.gross) as gross,\n\t\t\t\t\tSUM(p.fuelprice) as fuelprice,\n\t\t\t\t\tSUM(p.price) as price,\n\t\t\t\t\tSUM(p.expenses) as expenses,\n\t\t\t\t\t(SELECT SUM(`amount`) \n FROM `" . TABLE_PREFIX . "ledger`\n WHERE DATE_FORMAT(submitdate, '{$format}') \n AND `paysource` = " . PAYSOURCE_PIREP . "\n ) AS `pilotpay`\n\t\t\t\tFROM " . TABLE_PREFIX . "pireps p"; $sql .= DB::build_where($where_params); $sql .= ' GROUP BY `ym` ORDER BY `timestamp` ASC'; $results = DB::get_results($sql); return $results; }
/** * Get interval financial data, with the date clause * passed in as a WHERE: * * "DATE_SUB(CURDATE(), INTERVAL {$interval} DAY) <= p.submitdate"; * * Or some form of a date limitation, but has to be within the * where clause * * @param array $where_params Any WHERE parameters * @param char $grouping How to group data - Y for yearly, M for monthly, D for daily * @return array Returns finance data according to the above grouping * */ public static function getIntervalData($where_params, $grouping = 'M') { $grouping = strtolower($grouping); if ($grouping == 'y') { $format = '%Y'; } elseif ($grouping == 'm') { $format = '%Y-%m'; } elseif ($grouping == 'd') { $format = '%Y-%m-%d'; } $sql = "SELECT DATE_FORMAT(p.submitdate, '{$format}') AS ym,\n\t\t\t\t\tUNIX_TIMESTAMP(p.submitdate) AS timestamp,\n\t\t\t\t\tCOUNT(p.pirepid) AS total,\n\t\t\t\t\tSUM(p.revenue) as revenue,\n\t\t\t\t\tSUM(p.gross) as gross,\n\t\t\t\t\tSUM(p.fuelprice) as fuelprice,\n\t\t\t\t\tSUM(p.price) as price,\n\t\t\t\t\tSUM(p.expenses) as expenses,\n\t\t\t\t\tSUM((TIME_TO_SEC(flighttime_stamp)/60) * (pilotpay/60)) as pilotpay\n\t\t\t\tFROM " . TABLE_PREFIX . "pireps p"; $sql .= DB::build_where($where_params); $sql .= ' GROUP BY `ym` ORDER BY `timestamp` ASC'; $results = DB::get_results($sql); return $results; }
<?php include 'core/codon.config.php'; error_reporting(E_ALL); ini_set('display_errors', 'on'); echo '<pre>'; CodonCache::setStatus(false); $cws = new CodonWebService(); $xml = $cws->get('http://www.vacentral.net/airline/cva_canadianvirtualairlines/xml'); $xml = simplexml_load_string($xml); echo "Our airline's rank is {$xml->rank}"; print_r($_POST); echo 'set names<br>'; //$results = DB::query("SET NAMES 'utf8'"); DB::debug(); $params = array('column' => 'value', 'dateadded = CURDATE()', 'fruit' => array('apples', 'oranges', 'grapes')); echo DB::build_where($params); echo 'schedules<br>'; echo DB::build_where(array('params' => array('value1'))); //$results = DB::query('SELECT * FROM phpvms_schedules'); //$results = DB::query("SET NAMES 'utf8'"); ?> <form action="" method="post"> <input type="hidden" name="name" value="Omega-Air" /> <input type="submit" name="submit" value="Submit" /> </form>