Projekt

Obecné

Profil

« Předchozí | Další » 

Revize 1636

Přidáno uživatelem Ondřej Fibich před asi 12 roky(ů)

Upravy:

- optimalizace v provozu

Zobrazit rozdíly:

freenetis/branches/testing/application/models/members_traffic.php
protected $belongs_to = array('member');
/**
* Returns all daily traffics of members
*
* @author Michal Kliment
* @param date $date_from
* @param date $date_to
* @param integer $sql_offset
* @param integer $limit_results
* @param string $order_by
* @param string $order_by_direction
* @return MySQL_Result object
*/
public function get_all_members_traffics_by_dates(
$date_from, $date_to, $sql_offset = 0,
$limit_results = 100, $order_by = 'total',
$order_by_direction = 'DESC')
{
// order by direction check
if (strtolower($order_by_direction) != 'desc')
{
$order_by_direction = 'asc';
}
// query
return $this->db->query("
SELECT q.*, m.name AS member_name FROM
(
SELECT member_id, SUM(upload) AS upload, SUM(download) AS download,
SUM(upload) + SUM(download) AS total
FROM members_traffics d
WHERE day BETWEEN ? AND ?
GROUP BY member_id
) q
LEFT JOIN members m ON q.member_id = m.id
ORDER BY " . $this->db->escape_column($order_by) . " $order_by_direction
LIMIT " . intval($sql_offset) . ", " . intval($limit_results) . "
", $date_from, $date_to);
}
/**
* Counts all daily traffics of members
*
* @author Michal Kliment
* @param date $date_from
* @param date $date_to
* @return integer
*/
public function count_all_members_traffics_by_dates($date_from, $date_to)
{
// validates dates
if (!valid::date_string($date_from) || !valid::date_string($date_to))
return FALSE;
return $this->db->query("
SELECT COUNT(DISTINCT member_id) AS count
FROM members_traffics
WHERE day BETWEEN ? AND ?
", array($date_from, $date_to))->current()->count;
}
/**
* Returns total traffics of member
*
* @author Michal Kliment
......
{
$result = $this->db->query("
SELECT
IFNULL(SUM(upload),0) AS upload,
IFNULL(SUM(download),0) AS download
IFNULL(SUM(upload+local_upload),0) AS upload,
IFNULL(SUM(download+local_download),0) AS download
FROM members_traffics d WHERE d.member_id = ?
", $member_id);
......
{
return $this->db->query("
SELECT
IFNULL(SUM(upload),0) AS upload,
IFNULL(SUM(download),0) AS download
IFNULL(SUM(upload+local_upload),0) AS upload,
IFNULL(SUM(download+local_download),0) AS download
FROM members_traffics d WHERE d.member_id = ? AND day = CURDATE()
", array($member_id))->current();
}
......
{
return $this->db->query("
SELECT
IFNULL(SUM(upload),0) AS upload,
IFNULL(SUM(download),0) AS download
IFNULL(SUM(upload+local_upload),0) AS upload,
IFNULL(SUM(download+local_download),0) AS download
FROM members_traffics d WHERE d.member_id = ? AND day BETWEEN ? AND CURDATE()
", array($member_id, date::start_of_week (date("W"))))->current();
}
......
{
return $this->db->query("
SELECT
IFNULL(SUM(upload),0) AS upload,
IFNULL(SUM(download),0) AS download
IFNULL(SUM(upload+local_upload),0) AS upload,
IFNULL(SUM(download+local_download),0) AS download
FROM members_traffics d WHERE d.member_id = '?' AND day BETWEEN ? AND CURDATE()
", array($member_id, date("Y-m").'-01'))->current();
}
......
$where = '';
if ($filter_sql != '')
$where .= 'WHERE '.$filter_sql;
$where .= 'HAVING '.$filter_sql;
$limit = '';
if ($limit_results > 0)
......
YEAR(day) AS year
FROM members_traffics d
$group_by
$where
) d
$where
$order
$limit
");
......
$where = '';
if ($filter_sql != '')
$where .= 'WHERE '.$filter_sql;
$where .= 'HAVING '.$filter_sql;
$limit = '';
if ($limit_results > 0)
......
active
FROM members_traffics d
$group_by
$where
) d
JOIN members m ON d.member_id = m.id
$where
$order
$limit
");
......
$where = '';
if ($filter_sql != '')
$where .= 'WHERE '.$filter_sql;
$where .= 'HAVING '.$filter_sql;
$order = '';
if ($order_by != '')
......
FROM members_traffics d
WHERE d.member_id = ?
$group_by
$where
) d
$where
", array($member_id));
$arr_traffics = array();
......
if ($filter_sql != '')
{
$where .= 'WHERE '.$filter_sql;
$where .= 'HAVING '.$filter_sql;
}
return $this->db->query("
......
YEAR(day) AS year
FROM members_traffics d
$group_by
$where
) d
$where
")->current()->count;
}
......
$where = '';
if ($filter_sql != '')
$where .= 'WHERE '.$filter_sql;
$where .= 'HAVING '.$filter_sql;
return $this->db->query("
SELECT COUNT(*) AS total
......
YEAR(day) AS year
FROM members_traffics d
$group_by
$where
) d
JOIN members m ON d.member_id = m.id
$where
")->current()->total;
}
/**
* Counts all traffics of member
*
* !!!!!! SECURITY WARNING !!!!!!
* Be careful when you using this method, param $filter_sql is unprotected
* for SQL injections, security should be made at controller site using
* Filter_form class.
* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
*
* @author Michal Kliment
* @param integer $member_id
* @param string $type
* @param string $filter_sql
* @return integer
*/
public function count_member_traffics ($member_id, $type, $filter_sql = '')
{
switch ($type)
{
case 'daily':
$group_by = 'GROUP BY TO_DAYS(day)';
break;
case 'weekly':
$group_by = 'GROUP BY WEEK(day)';
break;
case 'monthly':
$group_by = 'GROUP BY MONTH(day)';
break;
case 'yearly':
$group_by = 'GROUP BY YEAR(day)';
break;
default:
$group_by = '';
break;
}
$where = '';
if ($filter_sql != '')
$where .= 'WHERE '.$filter_sql;
return $this->db->query("
SELECT COUNT(*) AS total
FROM
(
SELECT
IFNULL(SUM(upload),0) AS upload,
IFNULL(SUM(download),0) AS download,
IFNULL(SUM(upload),0) + IFNULL(SUM(download),0) AS total,
IFNULL(AVG(upload),0) AS avg_upload,
IFNULL(AVG(download),0) AS avg_download,
IFNULL(SUM(local_upload),0) AS local_upload,
IFNULL(SUM(local_download),0) AS local_download,
SUM(upload - local_upload) AS foreign_upload,
SUM(download - local_download) AS foreign_download,
day,
WEEK(day) AS week,
MONTH(day) AS month,
YEAR(day) AS year
FROM members_traffics d
WHERE d.member_id = ?
$group_by
) d
$where
", array($member_id))->current()->total;
}
/**
* Averages total traffics
*
* @author Michal Kliment
freenetis/branches/testing/application/controllers/traffic.php
// filter
$filter_form = new Filter_form('d');
$filter_form = new Filter_form('');
if ($type_number == 0)
{
$month_before = time() - 60 * 60 * 24 * 30;
$filter_form->add('day')
->type('date');
->type('date')
->default(Filter_form::OPER_GREATER_OR_EQUAL, date('Y-m-d', $month_before));
}
if ($type_number == 1)
{
$month3_before = time() - 60 * 60 * 24 * 30 * 3;
$filter_form->add('week')
->type('number');
->type('number')
->default(Filter_form::OPER_GREATER_OR_EQUAL, date('W', $month3_before));
}
if ($type_number == 2)
{
$month6_before = time() - 60 * 60 * 24 * 30 * 6;
$filter_form->add('month')
->type('select_number')
->values(array
......
10 => __('October'),
11 => __('November'),
12 => __('December')
));
))
->default(Filter_form::OPER_GREATER_OR_EQUAL, date('W', $month6_before));
}
if ($type_number == 3)
{
$filter_form->add('year')
->type('number')
->values(date::years());
->values(date::years())
->default(Filter_form::OPER_GREATER_OR_EQUAL, date('Y') - 5);
}
$filter_form->add('upload')
......
.$order_by.'/'.$order_by_direction,
'uri_segment' => 'page',
'style' => 'classic',
'total_items' => $total_traffics,
'items_per_page' => $limit_results,
'filter' => $filter_form
));
......
$order_by_direction = 'DESC';
// filter
$filter_form = new Filter_form('d');
$filter_form = new Filter_form('');
if ($type_number == 0)
{
$filter_form->add('day')
->type('date');
->type('date')
->default(Filter_form::OPER_IS, date('Y-m-d'));
}
if ($type_number == 1)
{
$filter_form->add('week')
->type('number');
->type('number')
->default(Filter_form::OPER_IS, intval(date('W')));
}
if ($type_number == 2)
......
10 => __('October'),
11 => __('November'),
12 => __('December')
));
))
->default(Filter_form::OPER_IS, intval(date('m')));
}
if ($type_number != 0)
{
$filter_form->add('year')
->type('number')
->values(date::years());
->values(date::years())
->default(Filter_form::OPER_IS, intval(date('Y')));
}
$filter_form->add('upload')
......
// filter
$filter_form = new Filter_form('d');
$filter_form = new Filter_form('');
//if ($type_number == 0)
//{
......
if ($type_number > 0)
{
$filter_form->add('year')
->type('number')->values(date::years());
->type('number')
->values(date::years());
}
$filter_form->add('upload')
......
{
if ($filter['key'] == 'day')
{
if (is_array($filter['value']))
{
$filter['value'] = $filter['value'][0];
}
switch ($filter['op'])
{
{
case Filter_form::OPER_EQUAL:
$time = strtotime($filter['value']);
$to_time = strtotime($filter['value']);

Také k dispozici: Unified diff