/**
* 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>
