Revize 1151
Přidáno uživatelem Michal Kliment před více než 13 roky(ů)
| freenetis/branches/testing/application/vendors/unit_tester/unit_testing_config.xml | ||
|---|---|---|
|
</input>
|
||
|
</values>
|
||
|
</method>
|
||
|
<method name="get_whitelisted_members" autogenerate="on">
|
||
|
<attributes></attributes>
|
||
|
<values>
|
||
|
<input></input>
|
||
|
</values>
|
||
|
</method>
|
||
|
<method name="count_whitelisted_members" autogenerate="on">
|
||
|
<attributes></attributes>
|
||
|
<attributes>
|
||
|
<attribute name="filter_sql" default_value="" />
|
||
|
</attributes>
|
||
|
<values>
|
||
|
<input></input>
|
||
|
<input>
|
||
|
<param value="" />
|
||
|
</input>
|
||
|
</values>
|
||
|
</method>
|
||
|
</model>
|
||
| ... | ... | |
|
</method></model>
|
||
|
<model name="message">
|
||
|
<method name="count_all_redirections" autogenerate="on">
|
||
|
<attributes></attributes>
|
||
|
<attributes>
|
||
|
<attribute name="filter_sql" default_value="" />
|
||
|
</attributes>
|
||
|
<values>
|
||
|
<input></input>
|
||
|
<input>
|
||
|
<param value="" />
|
||
|
</input>
|
||
|
</values>
|
||
|
</method>
|
||
|
<method name="count_all_messages" autogenerate="on">
|
||
| freenetis/branches/testing/application/models/message.php | ||
|---|---|---|
|
* @author Jiri Svitak
|
||
|
* @return unknown_type
|
||
|
*/
|
||
|
public function count_all_redirections()
|
||
|
public function count_all_redirections($filter_sql = '')
|
||
|
{
|
||
|
return $this->db->count_records('messages_ip_addresses');
|
||
|
$where = "";
|
||
|
if ($filter_sql)
|
||
|
$where = "WHERE $filter_sql";
|
||
|
|
||
|
return $this->db->query("
|
||
|
SELECT COUNT(*) AS total FROM
|
||
|
(
|
||
|
SELECT mip.*, mm.name AS member_name FROM
|
||
|
(
|
||
|
SELECT mip.ip_address_id, ip.ip_address, ms.name AS message,
|
||
|
mip.datetime, mip.comment, ms.self_cancel, ms.type,
|
||
|
IFNULL(u.member_id,ip.member_id) AS member_id
|
||
|
FROM messages_ip_addresses mip
|
||
|
LEFT JOIN
|
||
|
(
|
||
|
SELECT ip.*, IFNULL(i1.device_id,i2.device_id) AS device_id
|
||
|
FROM ip_addresses ip
|
||
|
LEFT JOIN ifaces i1 ON ip.iface_id = i1.id
|
||
|
LEFT JOIN vlan_ifaces vi ON ip.vlan_iface_id = vi.id
|
||
|
LEFT JOIN ifaces i2 ON vi.iface_id = i2.id
|
||
|
) ip ON mip.ip_address_id = ip.id
|
||
|
LEFT JOIN messages ms ON mip.message_id = ms.id
|
||
|
LEFT JOIN devices d ON d.id = ip.device_id
|
||
|
LEFT JOIN users u ON u.id = d.user_id
|
||
|
) mip
|
||
|
LEFT JOIN members mm ON mm.id = mip.member_id
|
||
|
) mip
|
||
|
$where
|
||
|
")->current()->total;
|
||
|
}
|
||
|
|
||
|
/**
|
||
| ... | ... | |
|
* @return unknown_type
|
||
|
*/
|
||
|
public function get_all_redirections($limit_from = 0, $limit_results = 20,
|
||
|
$order_by = 'ip_address', $order_by_direction = 'ASC', $filter_values = array())
|
||
|
$order_by = 'ip_address', $order_by_direction = 'ASC', $filter_sql = '')
|
||
|
{
|
||
|
// direction
|
||
|
if (strtolower($order_by_direction) != 'asc')
|
||
| ... | ... | |
|
if ($order_by == 'ip_address')
|
||
|
{
|
||
|
//$order_by = 'inet_aton(ip_address)';
|
||
|
$order_by = 'inet_aton(ip.ip_address) ASC, ms.self_cancel DESC, mip.datetime ASC';
|
||
|
$order_by = 'inet_aton(ip_address) ASC, self_cancel DESC, mip.datetime ASC';
|
||
|
$order_by_direction = '';
|
||
|
}
|
||
|
else
|
||
|
{
|
||
|
$order_by = $this->db->escape_column($order_by);
|
||
|
}
|
||
|
|
||
|
$where = '';
|
||
|
if ($filter_sql)
|
||
|
$where = "WHERE $filter_sql";
|
||
|
|
||
|
// query
|
||
|
return $this->db->query("
|
||
|
SELECT mip.ip_address_id, ip.ip_address, ms.name AS message,
|
||
|
mip.datetime, mip.comment, ms.type, ms.self_cancel,
|
||
|
mm.name AS member_name, mm.id AS member_id
|
||
|
FROM messages_ip_addresses mip
|
||
|
LEFT JOIN ip_addresses ip ON ip.id = mip.ip_address_id
|
||
|
LEFT JOIN messages ms ON ms.id = mip.message_id
|
||
|
LEFT JOIN ifaces i ON i.id = ip.iface_id
|
||
|
LEFT JOIN devices d ON d.id = i.device_id
|
||
|
LEFT JOIN users u ON u.id = d.user_id
|
||
|
LEFT JOIN members mm ON mm.id = u.member_id
|
||
|
SELECT * FROM
|
||
|
(
|
||
|
SELECT mip.*, mm.name AS member_name FROM
|
||
|
(
|
||
|
SELECT mip.ip_address_id, ip.ip_address, ms.name AS message,
|
||
|
mip.datetime, mip.comment, ms.self_cancel, ms.type,
|
||
|
IFNULL(u.member_id,ip.member_id) AS member_id
|
||
|
FROM messages_ip_addresses mip
|
||
|
LEFT JOIN
|
||
|
(
|
||
|
SELECT ip.*, IFNULL(i1.device_id,i2.device_id) AS device_id
|
||
|
FROM ip_addresses ip
|
||
|
LEFT JOIN ifaces i1 ON ip.iface_id = i1.id
|
||
|
LEFT JOIN vlan_ifaces vi ON ip.vlan_iface_id = vi.id
|
||
|
LEFT JOIN ifaces i2 ON vi.iface_id = i2.id
|
||
|
) ip ON mip.ip_address_id = ip.id
|
||
|
LEFT JOIN messages ms ON mip.message_id = ms.id
|
||
|
LEFT JOIN devices d ON d.id = ip.device_id
|
||
|
LEFT JOIN users u ON u.id = d.user_id
|
||
|
) mip
|
||
|
LEFT JOIN members mm ON mm.id = mip.member_id
|
||
|
) mip
|
||
|
$where
|
||
|
ORDER BY $order_by $order_by_direction
|
||
|
LIMIT " . intval($limit_from) . ", " . intval($limit_results) . "
|
||
|
");
|
||
| freenetis/branches/testing/application/models/member.php | ||
|---|---|---|
|
* Gets members whose at least one ip address is set as whitelisted.
|
||
|
* @author Jiri Svitak
|
||
|
*/
|
||
|
public function get_whitelisted_members()
|
||
|
public function get_whitelisted_members($limit_from = 0, $limit_results = 50, $order_by = 'id',
|
||
|
$order_by_direction = 'asc', $filter_sql = "")
|
||
|
{
|
||
|
$where = "";
|
||
|
if ($filter_sql)
|
||
|
$where = "WHERE $filter_sql";
|
||
|
|
||
|
return $this->db->query("
|
||
|
SELECT m.id, IFNULL(f.translated_term, e.value) AS type, m.name, a.balance,
|
||
|
a.id AS aid, a.comments_thread_id AS a_comments_thread_id,
|
||
|
ip.whitelisted,
|
||
|
COUNT(ip.id) AS ip_count
|
||
|
FROM members m
|
||
|
JOIN users u ON u.member_id = m.id
|
||
|
JOIN devices d ON d.user_id = u.id
|
||
|
JOIN ifaces i ON i.device_id = d.id
|
||
|
JOIN ip_addresses ip ON ip.iface_id = i.id
|
||
|
LEFT JOIN accounts a ON a.member_id = m.id AND m.id <> 1
|
||
|
LEFT JOIN comments_threads ct ON a.comments_thread_id = ct.id
|
||
|
LEFT JOIN enum_types e ON m.type = e.id
|
||
|
LEFT JOIN (SELECT * FROM translations WHERE lang = '".Config::get('lang')."') f ON e.value = f.original_term
|
||
|
WHERE ip.whitelisted > 0
|
||
|
SELECT m.*, COUNT(m.id) AS items_count,
|
||
|
GROUP_CONCAT(ip_address ORDER BY INET_ATON(ip_address) SEPARATOR ', \n')
|
||
|
AS items_count_title, a_comment
|
||
|
FROM
|
||
|
(
|
||
|
SELECT m.id, IFNULL(f.translated_term, e.value) AS type,
|
||
|
m.name, m.name AS member_name, a.balance,
|
||
|
a.id AS aid, a.comments_thread_id AS a_comments_thread_id,
|
||
|
ip.whitelisted, ip.ip_address, ip.id AS ip_address_id,
|
||
|
a_comment
|
||
|
FROM members m
|
||
|
LEFT JOIN users u ON u.member_id = m.id
|
||
|
LEFT JOIN devices d ON d.user_id = u.id
|
||
|
LEFT JOIN ifaces i ON i.device_id = d.id
|
||
|
LEFT JOIN vlan_ifaces vi ON vi.iface_id = i.id
|
||
|
LEFT JOIN ip_addresses ip ON (ip.iface_id = i.id OR ip.vlan_iface_id OR ip.member_id = m.id)
|
||
|
LEFT JOIN accounts a ON a.member_id = m.id AND m.id <> 1
|
||
|
LEFT JOIN
|
||
|
(
|
||
|
SELECT c.comments_thread_id,
|
||
|
GROUP_CONCAT(CONCAT(u.surname,' ',u.name,' (',SUBSTRING(c.datetime,1,10),'):\n',c.text)
|
||
|
ORDER BY datetime DESC SEPARATOR ', \n\n') AS a_comment
|
||
|
FROM comments c
|
||
|
JOIN users u ON c.user_id = u.id
|
||
|
GROUP BY c.comments_thread_id
|
||
|
) c ON a.comments_thread_id = c.comments_thread_id
|
||
|
LEFT JOIN comments_threads ct ON a.comments_thread_id = ct.id
|
||
|
LEFT JOIN enum_types e ON m.type = e.id
|
||
|
LEFT JOIN (SELECT * FROM translations WHERE lang = '".Config::get('lang')."') f ON e.value = f.original_term
|
||
|
WHERE ip.whitelisted > 0
|
||
|
) m
|
||
|
$where
|
||
|
GROUP BY m.id");
|
||
|
}
|
||
|
|
||
| ... | ... | |
|
* Counts members whose at least one ip address is set as whitelisted.
|
||
|
* @author Jiri Svitak
|
||
|
*/
|
||
|
public function count_whitelisted_members()
|
||
|
public function count_whitelisted_members($filter_sql = '')
|
||
|
{
|
||
|
$where = "";
|
||
|
if ($filter_sql)
|
||
|
$where = "WHERE $filter_sql";
|
||
|
|
||
|
return $this->db->query("
|
||
|
SELECT COUNT(*) AS total
|
||
|
FROM members m
|
||
|
JOIN users u ON u.member_id = m.id
|
||
|
JOIN devices d ON d.user_id = u.id
|
||
|
JOIN ifaces i ON i.device_id = d.id
|
||
|
JOIN ip_addresses ip ON ip.iface_id = i.id
|
||
|
WHERE ip.whitelisted > 0
|
||
|
GROUP BY m.id")->current()->total;
|
||
|
SELECT COUNT(*) AS total FROM
|
||
|
(
|
||
|
SELECT * FROM
|
||
|
(
|
||
|
SELECT m.id, IFNULL(f.translated_term, e.value) AS type,
|
||
|
m.name, m.name AS member_name, a.balance,
|
||
|
a.id AS aid, a.comments_thread_id AS a_comments_thread_id,
|
||
|
ip.whitelisted, ip.ip_address, ip.id AS ip_address_id
|
||
|
FROM members m
|
||
|
LEFT JOIN users u ON u.member_id = m.id
|
||
|
LEFT JOIN devices d ON d.user_id = u.id
|
||
|
LEFT JOIN ifaces i ON i.device_id = d.id
|
||
|
LEFT JOIN vlan_ifaces vi ON vi.iface_id = i.id
|
||
|
LEFT JOIN ip_addresses ip ON (ip.iface_id = i.id OR ip.vlan_iface_id OR ip.member_id = m.id)
|
||
|
LEFT JOIN accounts a ON a.member_id = m.id AND m.id <> 1
|
||
|
LEFT JOIN comments_threads ct ON a.comments_thread_id = ct.id
|
||
|
LEFT JOIN enum_types e ON m.type = e.id
|
||
|
LEFT JOIN (SELECT * FROM translations WHERE lang = '".Config::get('lang')."') f ON e.value = f.original_term
|
||
|
WHERE ip.whitelisted > 0
|
||
|
) m
|
||
|
$where
|
||
|
GROUP BY m.id
|
||
|
) m
|
||
|
")->current()->total;
|
||
|
}
|
||
|
}
|
||
| freenetis/branches/testing/application/controllers/redirect.php | ||
|---|---|---|
|
{
|
||
|
$order_by_direction = 'desc';
|
||
|
}
|
||
|
|
||
|
$filter_form = new Filter_form('mip');
|
||
|
|
||
|
$filter_form->add('ip_address')
|
||
|
->type('network_address')
|
||
|
->class(array
|
||
|
(
|
||
|
Filter_form::OPER_IS => 'ip_address',
|
||
|
Filter_form::OPER_IS_NOT => 'ip_address',
|
||
|
Filter_form::OPER_NETWORK_IS_IN => 'cidr',
|
||
|
Filter_form::OPER_NETWORK_IS_NOT_IN => 'cidr',
|
||
|
));
|
||
|
|
||
|
$filter_form->add('member_name')
|
||
|
->type('combo')
|
||
|
->callback('json/member_name');
|
||
|
|
||
|
$filter_form->add('type')
|
||
|
->type('select')
|
||
|
->values(array
|
||
|
(
|
||
|
Message_Model::$user_message => __('User message'),
|
||
|
Message_Model::$unknown_device_message => __('Unknown device'),
|
||
|
Message_Model::$interrupted_membership_message => __('Membership interrupt'),
|
||
|
Message_Model::$debtor_message => __('Debtor'),
|
||
|
Message_Model::$payment_notice_message => __('Payment notice')
|
||
|
));
|
||
|
|
||
|
$filter_form->add('datetime')
|
||
|
->type('date')
|
||
|
->label(__('Date and time'));
|
||
|
|
||
|
$filter_form->add('comment');
|
||
|
|
||
|
// model
|
||
|
$message_model = new Message_Model();
|
||
|
$total_redirections = $message_model->count_all_redirections();
|
||
|
$total_redirections = $message_model->count_all_redirections($filter_form->as_sql());
|
||
|
|
||
|
if (($sql_offset = ($page - 1) * $limit_results) > $total_redirections)
|
||
|
$sql_offset = 0;
|
||
|
|
||
|
$redirections = $message_model->get_all_redirections(
|
||
|
$sql_offset, (int)$limit_results, $order_by, $order_by_direction
|
||
|
$sql_offset, (int)$limit_results, $order_by, $order_by_direction,
|
||
|
$filter_form->as_sql()
|
||
|
);
|
||
|
|
||
|
$headline = __('Activated redirections');
|
||
| ... | ... | |
|
'order_by_direction' => $order_by_direction,
|
||
|
'limit_results' => $limit_results,
|
||
|
//'url_array_ofset' => 1,
|
||
|
'filter' => $filter_form
|
||
|
));
|
||
|
|
||
|
$grid->order_callback_field('ip_address')
|
||
| ... | ... | |
|
if (strtolower($order_by_direction) != 'desc')
|
||
|
$order_by_direction = 'asc';
|
||
|
|
||
|
$filter_form = new Filter_form('m');
|
||
|
|
||
|
$filter_form->add('member_name')
|
||
|
->type('combo')
|
||
|
->callback('json/member_name');
|
||
|
|
||
|
$filter_form->add('type')
|
||
|
->type('combo')
|
||
|
->values(ORM::factory('enum_type')->get_values(Enum_type_Model::$member_type_id));
|
||
|
|
||
|
$filter_form->add('whitelisted')
|
||
|
->type('select')
|
||
|
->label(__('Whitelist'))
|
||
|
->values(array
|
||
|
(
|
||
|
Ip_address_Model::$no_whitelist => __('No whitelist'),
|
||
|
Ip_address_Model::$permanent_whitelist => __('Permanent whitelist'),
|
||
|
Ip_address_Model::$temporary_whitelist => __('Temporary whitelist')
|
||
|
));
|
||
|
|
||
|
$filter_form->add('balance')
|
||
|
->type('number');
|
||
|
|
||
|
// load members
|
||
|
$model_members = new Member_Model();
|
||
|
$total_members = $model_members->count_whitelisted_members();
|
||
|
$total_members = $model_members->count_whitelisted_members($filter_form->as_sql());
|
||
|
|
||
|
if (($sql_offset = ($page - 1) * $limit_results) > $total_members)
|
||
|
$sql_offset = 0;
|
||
|
|
||
|
$query = $model_members->get_whitelisted_members(
|
||
|
$sql_offset, (int)$limit_results, $order_by,
|
||
|
$order_by_direction, array()
|
||
|
$order_by_direction, $filter_form->as_sql()
|
||
|
);
|
||
|
// it creates grid to view all members
|
||
|
$headline = __('List of whitelisted members');
|
||
| ... | ... | |
|
'style' => 'classic',
|
||
|
'order_by' => $order_by,
|
||
|
'order_by_direction' => $order_by_direction,
|
||
|
'limit_results' => $limit_results
|
||
|
'limit_results' => $limit_results,
|
||
|
'filter' => $filter_form
|
||
|
));
|
||
|
|
||
|
// database columns - some are commented out because of lack of space
|
||
| ... | ... | |
|
->label(__('Whitelist'))
|
||
|
->callback('callback::whitelisted_field');
|
||
|
|
||
|
$grid->order_field('ip_count')
|
||
|
->label(__('IP address count on the list'));
|
||
|
$grid->order_callback_field('ip_count')
|
||
|
->label(__('IP address count on the list'))
|
||
|
->callback('callback::items_count_field');
|
||
|
|
||
|
$grid->order_callback_field('balance')
|
||
|
->label(__('Balance'))
|
||
| freenetis/branches/testing/application/controllers/unit_tester.php | ||
|---|---|---|
|
(
|
||
|
'localhost',
|
||
|
'127.0.0.1',
|
||
|
'freenetis.coder.slfree.net',
|
||
|
);
|
||
|
|
||
|
/**
|
||
Také k dispozici: Unified diff
Pridana podpora novych filtru do noveho presmerovani.