Projekt

Obecné

Profil

Stáhnout (7.27 KB) Statistiky
| Větev: | Tag: | Revize:
<?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();
}
}
(23-23/101)