Projekt

Obecné

Profil

Stáhnout (8.61 KB) Statistiky
| Větev: | Tag: | Revize:

freenetis-github/application/models/voip_sip.php @ 399be2b6

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);
}

}