freenetis-github/application/models/voip_sip.php @ 53cf4ce8
| 8baed187 | Michal Kliment | <?php defined('SYSPATH') or die('No direct script access.');
|
|
/*
|
|||
* This file is part of open source system FreenetIS
|
|||
* and it is released 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/
|
|||
*
|
|||
*/
|
|||
/**
|
|||
* VoIP SIP account
|
|||
*
|
|||
* @package Model
|
|||
*
|
|||
* @property integer $id
|
|||
* @property integer $user_id
|
|||
* @property User_Model $user
|
|||
* @property string $name
|
|||
* @property string $accountcode
|
|||
* @property string $amaflags
|
|||
* @property string $callgroup
|
|||
* @property string $callerid
|
|||
* @property string $canreinvite
|
|||
* @property string $context
|
|||
* @property string $defaultip
|
|||
* @property string $dtmfmode
|
|||
* @property string $fromuser
|
|||
* @property string $fromdomain
|
|||
* @property string $fullcontact
|
|||
* @property string $host
|
|||
* @property string $insecure
|
|||
* @property string $language
|
|||
* @property string $mailbox
|
|||
* @property string $md5secret
|
|||
* @property string $nat
|
|||
* @property string $deny
|
|||
* @property string $permit
|
|||
* @property string $mask
|
|||
* @property string $pickupgroup
|
|||
* @property string $port
|
|||
* @property string $qualify
|
|||
* @property string $restrictcid
|
|||
* @property string $rtptimeout
|
|||
* @property string $rtpholdtimeout
|
|||
* @property string $secret
|
|||
* @property string $type
|
|||
* @property string $username
|
|||
* @property string $disallow
|
|||
* @property string $allow
|
|||
* @property string $musiconhold
|
|||
* @property integer $regseconds
|
|||
* @property string $ipaddr
|
|||
* @property string $regexten
|
|||
* @property string $cancallforward
|
|||
* @property string $setvar
|
|||
* @property string $auth
|
|||
*/
|
|||
class Voip_sip_Model extends ORM
|
|||
{
|
|||
protected $belongs_to = array('user');
|
|||
/**
|
|||
* Creates function for VoIP views
|
|||
*
|
|||
* @author Ondřej Fibich
|
|||
* @see Voip_sip_Model#create_views
|
|||
*/
|
|||
public static function create_functions()
|
|||
{
|
|||
$db = Database::instance();
|
|||
$db->query("DROP FUNCTION IF EXISTS getstate;");
|
|||
$db->query("
|
|||
CREATE FUNCTION `getstate`(state INT) RETURNS varchar(255) CHARSET utf8
|
|||
NO SQL
|
|||
IF state = 0 THEN
|
|||
return \"active\";
|
|||
ELSE
|
|||
return \"blocked\";
|
|||
END IF
|
|||
");
|
|||
$db->query("DROP FUNCTION IF EXISTS gettype;");
|
|||
$db->query("
|
|||
CREATE FUNCTION `gettype`(type INT) RETURNS varchar(255) CHARSET utf8
|
|||
NO SQL
|
|||
IF type = 0 THEN
|
|||
return \"prepaid\";
|
|||
ELSE
|
|||
return \"postpaid\";
|
|||
END IF
|
|||
");
|
|||
}
|
|||
/**
|
|||
* Creates views for VoIP synchronization to SIP server
|
|||
*
|
|||
* @author Ondřej Fibich
|
|||
*/
|
|||
public static function create_views()
|
|||
{
|
|||
$db = Database::instance();
|
|||
/* Add view voip_lbilling_accounts */
|
|||
$db->query("DROP VIEW IF EXISTS voip_lbilling_accounts;");
|
|||
$db->query("
|
|||
CREATE VIEW `voip_lbilling_accounts` AS
|
|||
select
|
|||
`u`.`id` AS `id`,
|
|||
`m`.`id` AS `userid`,
|
|||
`v`.`name` AS `cid`,
|
|||
`getstate`(`m`.`locked`) AS `state`,
|
|||
_utf8'0' AS `limit`,
|
|||
_utf8'unart-basic' AS `tarif`,
|
|||
concat(`m`.`name`,_utf8' - ',`u`.`login`) AS `descr`
|
|||
from
|
|||
(
|
|||
(
|
|||
`members` `m` join `users` `u` on
|
|||
(
|
|||
(
|
|||
`u`.`member_id` = `m`.`id`
|
|||
)
|
|||
)
|
|||
) join `voip_sips` `v` on
|
|||
(
|
|||
(
|
|||
`v`.`user_id` = `u`.`id`
|
|||
)
|
|||
)
|
|||
) where (`m`.`type` <> _utf8'15');
|
|||
");
|
|||
/* Add view voip_lbilling_payments */
|
|||
$db->query("DROP VIEW IF EXISTS voip_lbilling_payments;");
|
|||
$db->query("
|
|||
CREATE VIEW `voip_lbilling_payments` AS
|
|||
select
|
|||
`t`.`id` AS `id`,
|
|||
`m`.`id` AS `userid`,
|
|||
unix_timestamp(`t`.`creation_datetime`) AS `date`,
|
|||
`t`.`amount` AS `value`,
|
|||
`t`.`type` AS `state`,
|
|||
`t`.`text` AS `descr`
|
|||
from
|
|||
(
|
|||
(
|
|||
`transfers` `t` join `accounts` `a` on
|
|||
(
|
|||
(
|
|||
`t`.`origin_id` = `a`.`id`
|
|||
)
|
|||
)
|
|||
) join `members` `m` on
|
|||
(
|
|||
(
|
|||
`m`.`id` = `a`.`member_id`
|
|||
)
|
|||
)
|
|||
) where (`t`.`type` = _utf8'3');
|
|||
");
|
|||
/* Add view voip_lbilling_users */
|
|||
$db->query("DROP VIEW IF EXISTS `voip_lbilling_users`;");
|
|||
$db->query("
|
|||
CREATE VIEW `voip_lbilling_users` AS
|
|||
select
|
|||
distinct `m`.`id` AS `id`,
|
|||
`m`.`voip_billing_type` AS `type`,
|
|||
`getstate`(`m`.`locked`) AS `state`,
|
|||
`m`.`voip_billing_limit` AS `limit`,
|
|||
_utf8'CZK' AS `currency`,
|
|||
`m`.`name` AS `descr`
|
|||
from
|
|||
(
|
|||
(
|
|||
`members` `m` join `users` `u` on
|
|||
(
|
|||
(
|
|||
`u`.`member_id` = `m`.`id`
|
|||
)
|
|||
)
|
|||
) join `voip_sips` `v` on
|
|||
(
|
|||
(
|
|||
`v`.`user_id` = `u`.`id`
|
|||
)
|
|||
)
|
|||
) where (`m`.`type` <> _utf8'15') order by `m`.`id`;
|
|||
");
|
|||
}
|
|||
/**
|
|||
* Checks pre requirements for VoIP
|
|||
*
|
|||
* @author Ondřej Fibich
|
|||
* @return boolean
|
|||
*/
|
|||
public function check_pre_requirements()
|
|||
{
|
|||
return ($this->function_exists('getstate') &&
|
|||
$this->function_exists('gettype') &&
|
|||
$this->table_exists('voip_lbilling_accounts') &&
|
|||
$this->table_exists('voip_lbilling_payments') &&
|
|||
$this->table_exists('voip_lbilling_users'));
|
|||
}
|
|||
/**
|
|||
* Function counts all records.
|
|||
*
|
|||
* @return integer
|
|||
*/
|
|||
public function count_all_records()
|
|||
{
|
|||
return $this->db->count_records('voip_sips');
|
|||
}
|
|||
/**
|
|||
* Function gets record by member.
|
|||
*
|
|||
* @param integer $user_id
|
|||
* @return integer
|
|||
*/
|
|||
public function cout_record_by_member($member_id)
|
|||
{
|
|||
return $this->db->query("
|
|||
SELECT COUNT(v.id) AS count
|
|||
FROM voip_sips AS v
|
|||
INNER JOIN users AS u ON v.user_id = u.id
|
|||
WHERE u.member_id = ?
|
|||
", $member_id)->current()->count;
|
|||
}
|
|||
/**
|
|||
* Function gets one record.
|
|||
*
|
|||
* @param integer $id
|
|||
* @return Mysql_Result
|
|||
*/
|
|||
public function get_record_limited($id)
|
|||
{
|
|||
return $this->db->query("
|
|||
SELECT id, name, user_id
|
|||
FROM voip_sips WHERE id = ?
|
|||
", $id);
|
|||
}
|
|||
/**
|
|||
* Gets VoIP SIP with name.
|
|||
*
|
|||
* @param string $name VoIP number
|
|||
* @return Mysql_Result
|
|||
*/
|
|||
public function get_voip_sip_by_name($name)
|
|||
{
|
|||
return $this->db->query("
|
|||
SELECT DISTINCT name, secret
|
|||
FROM voip_sips
|
|||
WHERE name = ?
|
|||
", $name);
|
|||
}
|
|||
/**
|
|||
* Function gets one record by user.
|
|||
*
|
|||
* @param integer $user_id
|
|||
* @return Mysql_Result
|
|||
*/
|
|||
public function get_record_by_user_limited($user_id)
|
|||
{
|
|||
return $this->db->query("
|
|||
SELECT id, name, user_id
|
|||
FROM voip_sips WHERE user_id=?
|
|||
", $user_id);
|
|||
}
|
|||
/**
|
|||
* Function gets one record by user.
|
|||
*
|
|||
* @param integer $user_id
|
|||
* @return Mysql_Result
|
|||
*/
|
|||
public function get_record_by_user($user_id)
|
|||
{
|
|||
return $this->db->query("
|
|||
SELECT *
|
|||
FROM voip_sips
|
|||
WHERE user_id=?
|
|||
", $user_id);
|
|||
}
|
|||
/**
|
|||
* Function gets all records limited.
|
|||
*
|
|||
* @param integer $limit_from
|
|||
* @param integer $limit_results
|
|||
* @param string $order_by
|
|||
* @param string $order_by_direction
|
|||
* @return Mysql_Result
|
|||
*/
|
|||
public function get_all_records_limited(
|
|||
$limit_from = 0, $limit_results = 20,
|
|||
$order_by = 'user_id', $order_by_direction = 'asc')
|
|||
{
|
|||
// order by check
|
|||
if (!$this->has_column($order_by))
|
|||
{
|
|||
$order_by = 'id';
|
|||
}
|
|||
// order by direction check
|
|||
$order_by_direction = strtolower($order_by_direction);
|
|||
if ($order_by_direction != 'desc')
|
|||
{
|
|||
$order_by_direction = 'asc';
|
|||
}
|
|||
// query
|
|||
return $this->db->query("
|
|||
SELECT id, name, user_id
|
|||
FROM voip_sips
|
|||
ORDER BY $order_by $order_by_direction
|
|||
LIMIT " . intval($limit_from) . ", " . intval($limit_results) ."
|
|||
");
|
|||
}
|
|||
/**
|
|||
* Function gets all records.
|
|||
*
|
|||
* @param integer $limit_from
|
|||
* @param integer $limit_results
|
|||
* @param string $order_by
|
|||
* @param string $order_by_direction
|
|||
* @return Mysql_Result
|
|||
*/
|
|||
public function get_all_records(
|
|||
$limit_from = 0, $limit_results = 20,
|
|||
$order_by = 'name', $order_by_direction = 'asc')
|
|||
{
|
|||
// order by direction check
|
|||
$order_by_direction = strtolower($order_by_direction);
|
|||
if ($order_by_direction != 'desc')
|
|||
{
|
|||
$order_by_direction = 'asc';
|
|||
}
|
|||
// query
|
|||
return $this->db->query("
|
|||
SELECT voip_sips.id AS id,
|
|||
voip_sips.user_id AS user_id,
|
|||
voip_sips.name AS name,
|
|||
voip_sips.regseconds AS regseconds,
|
|||
users.name AS uname,
|
|||
users.surname AS usurname,
|
|||
CONCAT(users.name, ' ', users.surname, ' - ', users.login) AS ufname,
|
|||
users.member_id AS member_id,
|
|||
members.name AS mname,
|
|||
members.locked AS locked
|
|||
FROM voip_sips
|
|||
INNER JOIN users ON voip_sips.user_id = users.id
|
|||
INNER JOIN members ON users.member_id = members.id
|
|||
ORDER BY " . $this->db->escape_column($order_by) . " $order_by_direction
|
|||
LIMIT " . intval($limit_from) . ", " . intval($limit_results) ."
|
|||
");
|
|||
}
|
|||
/**
|
|||
* Function gets all records by member
|
|||
*
|
|||
* @param integer $id
|
|||
* @return Mysql_Result
|
|||
*/
|
|||
public function get_all_record_by_member_limited($member_id)
|
|||
{
|
|||
return $this->db->query("
|
|||
SELECT v.id, v.name, v.user_id, v.callerid
|
|||
FROM voip_sips v
|
|||
INNER JOIN users u ON v.user_id = u.id
|
|||
WHERE u.member_id = ?
|
|||
", $member_id);
|
|||
}
|
|||
}
|