Revize c1bdc1c4
Přidáno uživatelem Michal Kliment před více než 9 roky(ů)
| application/models/device_admin.php | ||
|---|---|---|
|
ORDER BY d.name
|
||
|
", array($user_id));
|
||
|
}
|
||
|
|
||
|
/**
|
||
|
* Gets all devices admins from database. Database query returns member's device parameters (id, name, type),
|
||
|
* owner of device (user name and surname), MAC addresses of interfaces, names of segments and ip addresses.
|
||
|
*
|
||
|
* @param array $params
|
||
|
* @param integer $user_id
|
||
|
* @return Mysql_Result
|
||
|
*/
|
||
|
public function get_all_devices_admins($params = array(), $user_id = NULL)
|
||
|
{
|
||
|
// default params
|
||
|
$default_params = array
|
||
|
(
|
||
|
'order_by' => 'id',
|
||
|
'order_by_direction' => 'asc'
|
||
|
);
|
||
|
|
||
|
$params = array_merge($default_params, $params);
|
||
|
|
||
|
$conds = array();
|
||
|
|
||
|
// filter
|
||
|
if (isset($params['filter_sql']) && $params['filter_sql'] != '')
|
||
|
$conds[] = $params['filter_sql'];
|
||
|
|
||
|
// user id
|
||
|
if ($user_id)
|
||
|
$conds[] = "d.user_id = " . intval($user_id);
|
||
|
|
||
|
$where = count($conds) ? 'WHERE '.implode(' AND ', $conds) : '';
|
||
|
|
||
|
|
||
|
$order_by = $this->db->escape_column($params['order_by']);
|
||
|
|
||
|
// order by direction check
|
||
|
if (strtolower($params['order_by_direction']) != 'desc')
|
||
|
$order_by_direction = 'asc';
|
||
|
else
|
||
|
$order_by_direction = 'desc';
|
||
|
|
||
|
if (isset($params['limit']) && isset($params['offset']))
|
||
|
$limit = "LIMIT " . intval($params['offset']) . ", " . intval($params['limit']);
|
||
|
else
|
||
|
$limit = "";
|
||
|
|
||
|
// HACK FOR IMPROVING PERFORMANCE (fixes #362)
|
||
|
$select_cloud_iface = '';
|
||
|
$join_cloud_iface = '';
|
||
|
|
||
|
if (isset($params['filter_sql']) && $params['filter_sql'] != '' &&
|
||
|
(strpos($params['filter_sql'], '.`cloud` LIKE ') ||
|
||
|
strpos($params['filter_sql'], '.`mac` LIKE ')))
|
||
|
{
|
||
|
$select_cloud_iface = ', c.id AS cloud, i.mac';
|
||
|
$join_cloud_iface = "
|
||
|
LEFT JOIN ifaces i ON i.device_id = d.id
|
||
|
LEFT JOIN ip_addresses ip ON ip.iface_id = i.id
|
||
|
LEFT JOIN clouds_subnets cs ON cs.subnet_id = ip.subnet_id
|
||
|
LEFT JOIN clouds c ON cs.cloud_id = c.id";
|
||
|
}
|
||
|
|
||
|
// query
|
||
|
return $this->db->query("
|
||
|
SELECT * FROM
|
||
|
(
|
||
|
SELECT dau.id, d.id AS device_id, d.type,
|
||
|
IFNULL(f.translated_term, e.value) AS type_name,
|
||
|
d.name, d.name AS device_name, u.id AS user_id,
|
||
|
u.name AS user_name, u.surname AS user_surname, u.login AS user_login,
|
||
|
d.login, d.password, d.price, d.trade_name, d.payment_rate,
|
||
|
d.buy_date, m.name AS device_member_name, s.street, t.town,
|
||
|
ap.street_number, d.comment, 1 AS redirection, 1 AS email,
|
||
|
1 AS sms, dam.id AS member_id, dam.type as member_type,
|
||
|
whitelisted, IF(mi.id IS NOT NULL, 1, 0) AS interrupt,
|
||
|
dam.name AS member_name, dau.id AS dau_id,
|
||
|
CONCAT(dau.name, ' ', dau.surname) AS dau_name,
|
||
|
m.notification_by_redirection, m.notification_by_email,
|
||
|
m.notification_by_sms
|
||
|
$select_cloud_iface
|
||
|
FROM devices d
|
||
|
JOIN users u ON d.user_id = u.id
|
||
|
JOIN members m ON u.member_id = m.id
|
||
|
JOIN device_admins da ON da.device_id = d.id
|
||
|
JOIN users dau ON da.user_id = dau.id
|
||
|
JOIN members dam ON dau.member_id = dam.id
|
||
|
LEFT JOIN address_points ap ON d.address_point_id = ap.id
|
||
|
LEFT JOIN streets s ON ap.street_id = s.id
|
||
|
LEFT JOIN towns t ON ap.town_id = t.id
|
||
|
LEFT JOIN enum_types e ON d.type = e.id
|
||
|
LEFT JOIN translations f ON lang = ? AND e.value = f.original_term
|
||
|
LEFT JOIN
|
||
|
(
|
||
|
SELECT mi.id, mi.member_id
|
||
|
FROM membership_interrupts mi
|
||
|
LEFT JOIN members_fees mf ON mi.members_fee_id = mf.id
|
||
|
WHERE mf.activation_date <= CURDATE() AND mf.deactivation_date >= CURDATE()
|
||
|
) mi ON mi.member_id = dam.id
|
||
|
LEFT JOIN
|
||
|
(
|
||
|
SELECT m2.id AS member_id, IF(mw.member_id IS NULL, 0, 2 - mw.permanent) AS whitelisted
|
||
|
FROM members m2
|
||
|
LEFT JOIN members_whitelists mw ON mw.member_id = m2.id
|
||
|
AND mw.since <= CURDATE() AND mw.until >= CURDATE()
|
||
|
) ip ON ip.member_id = dam.id
|
||
|
$join_cloud_iface
|
||
|
) d
|
||
|
$where
|
||
|
GROUP BY dau_id
|
||
|
ORDER BY $order_by $order_by_direction
|
||
|
$limit
|
||
|
", Config::get('lang'));
|
||
|
} // end of get_all_devices_admins
|
||
|
|
||
|
/**
|
||
|
* Count of all devices admins
|
||
|
* @param array $filter_values
|
||
|
* @return integer
|
||
|
*/
|
||
|
public function count_all_devices_admins($filter_sql = '')
|
||
|
{
|
||
|
$where = '';
|
||
|
|
||
|
// filter
|
||
|
if ($filter_sql != '')
|
||
|
$where = "WHERE $filter_sql";
|
||
|
|
||
|
// HACK FOR IMPROVING PERFORMANCE (fixes #362)
|
||
|
$select_cloud_iface = '';
|
||
|
$join_cloud_iface = '';
|
||
|
|
||
|
if (strpos($filter_sql, '.`cloud` LIKE ') || strpos($filter_sql, '.`mac` LIKE '))
|
||
|
{
|
||
|
$select_cloud_iface = ', c.id AS cloud, i.mac';
|
||
|
$join_cloud_iface = "
|
||
|
LEFT JOIN ifaces i ON i.device_id = d.id
|
||
|
LEFT JOIN ip_addresses ip ON ip.iface_id = i.id
|
||
|
LEFT JOIN clouds_subnets cs ON cs.subnet_id = ip.subnet_id
|
||
|
LEFT JOIN clouds c ON cs.cloud_id = c.id";
|
||
|
}
|
||
|
|
||
|
// query
|
||
|
return $this->db->query("
|
||
|
SELECT COUNT(member_id) AS total FROM
|
||
|
(
|
||
|
SELECT member_id FROM
|
||
|
(
|
||
|
SELECT d.id AS device_id, d.type,
|
||
|
IFNULL(f.translated_term, e.value) AS type_name, d.name,
|
||
|
d.name AS device_name, u.id AS user_id, u.name AS user_name,
|
||
|
u.surname AS user_surname, u.login AS user_login,
|
||
|
d.login, d.password, d.price, d.trade_name, d.payment_rate,
|
||
|
d.buy_date, m.name AS member_name, s.street, t.town,
|
||
|
ap.street_number, d.comment, dam.id AS member_id $select_cloud_iface
|
||
|
FROM devices d
|
||
|
JOIN users u ON d.user_id = u.id
|
||
|
JOIN members m ON u.member_id = m.id
|
||
|
LEFT JOIN address_points ap ON d.address_point_id = ap.id
|
||
|
LEFT JOIN streets s ON ap.street_id = s.id
|
||
|
LEFT JOIN towns t ON ap.town_id = t.id
|
||
|
LEFT JOIN enum_types e ON d.type = e.id
|
||
|
LEFT JOIN translations f ON lang = ? AND e.value = f.original_term
|
||
|
JOIN device_admins da ON da.device_id = d.id
|
||
|
JOIN users dau ON da.user_id = dau.id
|
||
|
JOIN members dam ON dau.member_id = dam.id
|
||
|
$join_cloud_iface
|
||
|
) d
|
||
|
$where
|
||
|
GROUP BY member_id
|
||
|
) count
|
||
|
", Config::get('lang'))->current()->total;
|
||
|
} // end of count_all_devices_admins
|
||
|
}
|
||
Také k dispozici: Unified diff
Release 1.1.0