freenetis-github/application/models/connection_request.php @ 18ac9009
| 31ca0a32 | Michal Kliment | <?php defined('SYSPATH') or die('No direct script access.');
|
|
/*
|
|||
* This file is part of open source system FreenetIS
|
|||
* and it is release under GPLv3 licence.
|
|||
*
|
|||
* More info about licence can be found:
|
|||
* http://www.gnu.org/licenses/gpl-3.0.html
|
|||
*
|
|||
* More info about project can be found:
|
|||
* http://www.freenetis.org/
|
|||
*
|
|||
*/
|
|||
/**
|
|||
* Connecttion request.
|
|||
*
|
|||
* @author Ondřej Fibich
|
|||
* @package Model
|
|||
*
|
|||
* @property integer $id
|
|||
* @property integer $member_id
|
|||
* @property Member_Model $member
|
|||
* @property integer $added_user_id
|
|||
* @property User_Model $added_user
|
|||
* @property integer $decided_user_id
|
|||
* @property User_Model $decided_user
|
|||
* @property integer $state
|
|||
* @property datetime $created_at
|
|||
* @property datetime $decided_at
|
|||
* @property string $ip_address
|
|||
* @property integer $subnet_id
|
|||
* @property Subnet_Model $subnet
|
|||
* @property string $mac_address
|
|||
* @property integer $device_id
|
|||
* @property Device_Model $device
|
|||
* @property integer $device_type_id
|
|||
* @property Enum_type_Model $device_type
|
|||
* @property integer $device_template_id
|
|||
* @property Device_template_Model $device_template
|
|||
* @property string $comment
|
|||
* @property integer $comments_thread_id
|
|||
* @property Comments_thread_Model $comments_thread
|
|||
*/
|
|||
class Connection_request_Model extends ORM
|
|||
{
|
|||
// states of request
|
|||
/** Undecided state (default state) */
|
|||
const STATE_UNDECIDED = 0;
|
|||
/** Denied state - request was not approved */
|
|||
const STATE_REJECTED = 1;
|
|||
/** Approved state - request was approved */
|
|||
const STATE_APPROVED = 2;
|
|||
/**
|
|||
* State messages
|
|||
*
|
|||
* @var array
|
|||
*/
|
|||
private static $state_messages = array
|
|||
(
|
|||
self::STATE_UNDECIDED => 'Undecided',
|
|||
self::STATE_REJECTED => 'Rejected',
|
|||
self::STATE_APPROVED => 'Approved'
|
|||
);
|
|||
// database relations
|
|||
protected $belongs_to = array
|
|||
(
|
|||
'member', 'device', 'device_template',
|
|||
'comments_thread', 'subnet', 'device_type' => 'enum_type',
|
|||
'added_user' => 'user', 'decided_user' => 'user'
|
|||
);
|
|||
// functions
|
|||
/**
|
|||
* Gets states messages
|
|||
*
|
|||
* @param boolean $translate Translate messages?
|
|||
* @return array
|
|||
*/
|
|||
public static function get_state_messages($translate = TRUE)
|
|||
{
|
|||
if ($translate)
|
|||
{
|
|||
return array_map('__', self::$state_messages);
|
|||
}
|
|||
return self::$state_messages;
|
|||
}
|
|||
/**
|
|||
* Counts filtered connection requests
|
|||
*
|
|||
* @param string $filter_sql SQL filter
|
|||
* @return integer Count
|
|||
*/
|
|||
public function count_all_connection_requests($filter_sql = '')
|
|||
{
|
|||
$where = '';
|
|||
if (!empty($filter_sql))
|
|||
{
|
|||
$where = 'WHERE ' . $filter_sql;
|
|||
}
|
|||
else
|
|||
{
|
|||
return $this->count_all();
|
|||
}
|
|||
return $this->db->query("
|
|||
SELECT COUNT(*) AS total FROM (
|
|||
SELECT cr.id, cr.mac_address, cr.ip_address, cr.comment, cr.state,
|
|||
cr.created_at, cr.member_id, m.name AS member_name,
|
|||
u.id AS user_id, CONCAT(u.name, ' ', u.surname) AS user_name,
|
|||
s.id AS subnet_id, s.name AS subnet_name,
|
|||
IFNULL(t.translated_term, e.value) AS device_type
|
|||
FROM connection_requests cr
|
|||
JOIN members m ON m.id = cr.member_id
|
|||
JOIN subnets s ON s.id = cr.subnet_id
|
|||
LEFT JOIN users u ON u.id = cr.added_user_id
|
|||
LEFT JOIN enum_types e ON cr.device_type_id = e.id
|
|||
LEFT JOIN translations t ON e.value LIKE t.original_term AND t.lang = ?
|
|||
) c
|
|||
$where
|
|||
", Config::get('lang'))->current()->total;
|
|||
}
|
|||
/**
|
|||
* Counts connection requests of members
|
|||
*
|
|||
* @param integer $member_id
|
|||
* @return integer Count
|
|||
*/
|
|||
public function count_all_connection_requests_of_member($member_id)
|
|||
{
|
|||
return $this->db->query("
|
|||
SELECT COUNT(*) AS total
|
|||
FROM connection_requests cr
|
|||
WHERE cr.member_id = ?
|
|||
", $member_id)->current()->total;
|
|||
}
|
|||
/**
|
|||
* Gets filtered connection requests
|
|||
*
|
|||
* @param integer $limit_from
|
|||
* @param integer $limit_results
|
|||
* @param string $order_by
|
|||
* @param string $order_by_direction
|
|||
* @param string $filter_sql SQL filter
|
|||
* @return Mysql_Result
|
|||
*/
|
|||
public function get_all_connection_requests(
|
|||
$limit_from = 0, $limit_results = 50, $order_by = 'id',
|
|||
$order_by_direction = 'asc', $filter_sql = '')
|
|||
{
|
|||
$where = '';
|
|||
if (!empty($filter_sql))
|
|||
{
|
|||
$where = 'WHERE ' . $filter_sql;
|
|||
}
|
|||
if (strtolower($order_by_direction) != 'asc')
|
|||
{
|
|||
$order_by_direction = 'desc';
|
|||
}
|
|||
return $this->db->query("
|
|||
SELECT * FROM (
|
|||
SELECT cr.id, cr.mac_address, cr.ip_address, cr.comment, cr.state,
|
|||
cr.created_at, cr.member_id, m.name AS member_name,
|
|||
u.id AS user_id, CONCAT(u.name, ' ', u.surname) AS user_name,
|
|||
s.id AS subnet_id, s.name AS subnet_name,
|
|||
IFNULL(t.translated_term, e.value) AS device_type,
|
|||
co.a_comment, cr.comments_thread_id AS a_comments_thread_id,
|
|||
1 AS a_comment_add
|
|||
FROM connection_requests cr
|
|||
JOIN members m ON m.id = cr.member_id
|
|||
JOIN subnets s ON s.id = cr.subnet_id
|
|||
LEFT JOIN users u ON u.id = cr.added_user_id
|
|||
LEFT JOIN enum_types e ON cr.device_type_id = e.id
|
|||
LEFT JOIN translations t ON e.value LIKE t.original_term AND t.lang = ?
|
|||
LEFT JOIN
|
|||
(
|
|||
SELECT comments_thread_id,
|
|||
GROUP_CONCAT(
|
|||
CONCAT(
|
|||
u.surname,' ',u.name,
|
|||
' (',SUBSTRING(c.datetime,1,10),'):\n',c.text)
|
|||
ORDER BY c.datetime DESC
|
|||
SEPARATOR '\n\n'
|
|||
) AS a_comment
|
|||
FROM comments c
|
|||
JOIN users u ON c.user_id = u.id
|
|||
GROUP BY comments_thread_id
|
|||
) co ON co.comments_thread_id = cr.comments_thread_id
|
|||
) c
|
|||
$where
|
|||
ORDER BY " . $this->db->escape_column($order_by) . " $order_by_direction
|
|||
LIMIT " . intval($limit_from) . ", " . intval($limit_results) . "
|
|||
", Config::get('lang'));
|
|||
}
|
|||
/**
|
|||
* Gets connection requests of member
|
|||
*
|
|||
* @param integer $member_id
|
|||
* @return Mysql_Result
|
|||
*/
|
|||
public function get_all_connection_requests_of_member($member_id)
|
|||
{
|
|||
return $this->db->query("
|
|||
SELECT cr.id, cr.mac_address, cr.ip_address, cr.comment, cr.state,
|
|||
cr.created_at, cr.member_id, m.name AS member_name,
|
|||
u.id AS user_id, CONCAT(u.name, ' ', u.surname) AS user_name,
|
|||
s.id AS subnet_id, s.name AS subnet_name,
|
|||
IFNULL(t.translated_term, e.value) AS device_type,
|
|||
co.a_comment
|
|||
FROM connection_requests cr
|
|||
JOIN members m ON m.id = cr.member_id
|
|||
JOIN subnets s ON s.id = cr.subnet_id
|
|||
LEFT JOIN users u ON u.id = cr.added_user_id
|
|||
LEFT JOIN enum_types e ON cr.device_type_id = e.id
|
|||
LEFT JOIN translations t ON e.value LIKE t.original_term AND t.lang = ?
|
|||
LEFT JOIN
|
|||
(
|
|||
SELECT comments_thread_id,
|
|||
GROUP_CONCAT(
|
|||
CONCAT(
|
|||
u.surname,' ',u.name,
|
|||
' (',SUBSTRING(c.datetime,1,10),'):\n',c.text)
|
|||
ORDER BY c.datetime DESC
|
|||
SEPARATOR '\n\n'
|
|||
) AS a_comment
|
|||
FROM comments c
|
|||
JOIN users u ON c.user_id = u.id
|
|||
GROUP BY comments_thread_id
|
|||
) co ON co.comments_thread_id = cr.comments_thread_id
|
|||
WHERE cr.member_id = ?
|
|||
ORDER BY cr.created_at DESC
|
|||
", Config::get('lang'), $member_id);
|
|||
}
|
|||
/**
|
|||
* Gets an undecided connection that contains given IP
|
|||
*
|
|||
* @param string $ip_address
|
|||
* @return ORM_Iterator
|
|||
*/
|
|||
public function get_undecided_connection_with_ip($ip_address)
|
|||
{
|
|||
return $this->where(array(
|
|||
'ip_address' => $ip_address,
|
|||
'state' => self::STATE_UNDECIDED
|
|||
))->find_all();
|
|||
}
|
|||
}
|