Revize 729
Přidáno uživatelem Ondřej Fibich před asi 14 roky(ů)
freenetis/tags/0.1.1/application/models/messages_ip_addresses.php | ||
---|---|---|
<?php
|
||
class Messages_ip_addresses_Model extends Model
|
||
{
|
||
function get_redirections_of_member($member_id)
|
||
{
|
||
return $this->db->query("
|
||
SELECT ip.ip_address, m.id, m.name, mip.ip_address_id, mip.message_id, $member_id AS member_id
|
||
FROM messages m
|
||
JOIN messages_ip_addresses mip ON mip.message_id = m.id
|
||
JOIN ip_addresses ip ON mip.ip_address_id = ip.id
|
||
JOIN ifaces i ON ip.iface_id = i.id
|
||
JOIN devices d ON i.device_id = d.id
|
||
JOIN users u ON d.user_id = u.id
|
||
WHERE u.member_id = $member_id
|
||
");
|
||
}
|
||
|
||
function get_redirections_of_ip_address($ip_address_id)
|
||
{
|
||
return $this->db->query("
|
||
SELECT m.id, m.name, mip.ip_address_id, mip.message_id
|
||
FROM messages m
|
||
JOIN messages_ip_addresses mip ON mip.message_id = m.id
|
||
WHERE mip.ip_address_id = $ip_address_id
|
||
");
|
||
}
|
||
|
||
}
|
freenetis/tags/0.1.1/application/models/phone_invoice_user.php | ||
---|---|---|
<?php defined('SYSPATH') or die('No direct script access.');
|
||
/*
|
||
*/
|
||
|
||
/**
|
||
* Descriptions of Phone_invoice_user_Model
|
||
*
|
||
* @author Ondřej Fibich
|
||
*/
|
||
class Phone_invoice_user_Model extends ORM {
|
||
|
||
protected $belong_to = array('user', 'phone_invoice');
|
||
|
||
protected $has_many = array(
|
||
'phone_connections', 'phone_calls', 'phone_fixed_calls', 'phone_pays',
|
||
'phone_roaming_sms_messages', 'phone_sms_messages', 'phone_vpn_calls'
|
||
);
|
||
|
||
/**
|
||
* Gets all users phone invoices
|
||
* @param integer $user_id
|
||
* @return unknown_type
|
||
*/
|
||
public function get_phone_invoices_of_user($user_id)
|
||
{
|
||
return self::$db->query(
|
||
"SELECT phone_invoice_users.id, phone_invoices.locked,
|
||
phone_invoices.billing_period_from,
|
||
phone_invoices.billing_period_to, phone_invoice_users.user_id,
|
||
phone_invoice_users.phone_number AS number
|
||
FROM phone_invoice_users
|
||
LEFT JOIN phone_invoices ON phone_invoice_users.phone_invoice_id = phone_invoices.id
|
||
WHERE phone_invoice_users.user_id =" . intval($user_id)
|
||
);
|
||
}
|
||
|
||
/**
|
||
* Gets info about each number in invoice.
|
||
* Calculates total price for each number.
|
||
* @param integer $invoice_id
|
||
* @return unknown_type
|
||
*/
|
||
public function get_all_invoice_users($invoice_id)
|
||
{
|
||
// madness query to calculate price for each user invoicee
|
||
return self::$db->query(
|
||
"SELECT `phone_invoice_users`.`id`,
|
||
`phone_invoice_users`.`user_id`,
|
||
`phone_invoice_users`.`phone_number` ,
|
||
CONCAT( `users`.`surname`, ' ', `users`.`name` ) AS `name`,
|
||
(
|
||
(SELECT IFNULL(SUM( price ), 0)
|
||
FROM phone_calls
|
||
WHERE phone_invoice_users_id =phone_invoice_users.id)
|
||
+
|
||
(SELECT IFNULL(SUM( price ), 0)
|
||
FROM phone_fixed_calls
|
||
WHERE phone_invoice_users_id =phone_invoice_users.id)
|
||
+
|
||
(SELECT IFNULL(SUM( price ), 0)
|
||
FROM phone_vpn_calls
|
||
WHERE phone_invoice_users_id =phone_invoice_users.id)
|
||
+
|
||
(SELECT IFNULL(SUM( price ), 0)
|
||
FROM phone_sms_messages
|
||
WHERE phone_invoice_users_id =phone_invoice_users.id)
|
||
+
|
||
(SELECT IFNULL(SUM( price ), 0)
|
||
FROM phone_pays
|
||
WHERE phone_invoice_users_id =phone_invoice_users.id)
|
||
+
|
||
(SELECT IFNULL(SUM( price ), 0)
|
||
FROM phone_roaming_sms_messages
|
||
WHERE phone_invoice_users_id =phone_invoice_users.id)
|
||
+
|
||
(SELECT IFNULL(SUM( price ), 0)
|
||
FROM phone_connections
|
||
WHERE phone_invoice_users_id =phone_invoice_users.id)
|
||
) AS price
|
||
FROM `phone_invoice_users`
|
||
LEFT JOIN `users` ON `phone_invoice_users`.`user_id` = `users`.`id`
|
||
WHERE `phone_invoice_users`.`phone_invoice_id` = ?;",
|
||
array($invoice_id)
|
||
);
|
||
}
|
||
|
||
/**
|
||
* Vat and Out of tax price of each service
|
||
* @return row
|
||
*/
|
||
public function get_prices()
|
||
{
|
||
return self::$db->query(
|
||
"SELECT (
|
||
SELECT IFNULL(SUM(price), 0)
|
||
FROM phone_calls
|
||
WHERE phone_invoice_users_id=" . $this->id . " AND private=0
|
||
) AS phone_calls_company,(
|
||
SELECT IFNULL(SUM(price), 0)
|
||
FROM phone_calls
|
||
WHERE phone_invoice_users_id=" . $this->id . " AND private=1
|
||
) AS phone_calls_private,
|
||
|
||
(
|
||
SELECT IFNULL(SUM(price), 0)
|
||
FROM phone_fixed_calls
|
||
WHERE phone_invoice_users_id=" . $this->id . " AND private=0
|
||
) AS phone_fixed_calls_company,(
|
||
SELECT IFNULL(SUM(price), 0)
|
||
FROM phone_fixed_calls
|
||
WHERE phone_invoice_users_id=" . $this->id . " AND private=1
|
||
) AS phone_fixed_calls_private,
|
||
|
||
(
|
||
SELECT IFNULL(SUM(price), 0)
|
||
FROM phone_vpn_calls
|
||
WHERE phone_invoice_users_id=" . $this->id . " AND private=0
|
||
) AS phone_vpn_calls_company,(
|
||
SELECT IFNULL(SUM(price), 0)
|
||
FROM phone_vpn_calls
|
||
WHERE phone_invoice_users_id=" . $this->id . " AND private=1
|
||
) AS phone_vpn_calls_private,
|
||
|
||
(
|
||
SELECT IFNULL(SUM(price), 0)
|
||
FROM phone_pays
|
||
WHERE phone_invoice_users_id=" . $this->id . " AND private=0
|
||
) AS phone_pays_company,(
|
||
SELECT IFNULL(SUM(price), 0)
|
||
FROM phone_pays
|
||
WHERE phone_invoice_users_id=" . $this->id . " AND private=1
|
||
) AS phone_pays_private,
|
||
|
||
(
|
||
SELECT IFNULL(SUM(price), 0)
|
||
FROM phone_connections
|
||
WHERE phone_invoice_users_id=" . $this->id . " AND private=0
|
||
) AS phone_connections_company,(
|
||
SELECT IFNULL(SUM(price), 0)
|
||
FROM phone_connections
|
||
WHERE phone_invoice_users_id=" . $this->id . " AND private=1
|
||
) AS phone_connections_private,
|
||
|
||
(
|
||
SELECT IFNULL(SUM(price), 0)
|
||
FROM phone_roaming_sms_messages
|
||
WHERE phone_invoice_users_id=" . $this->id . " AND private=0
|
||
) AS phone_roaming_sms_message_company,(
|
||
SELECT IFNULL(SUM(price), 0)
|
||
FROM phone_roaming_sms_messages
|
||
WHERE phone_invoice_users_id=" . $this->id . " AND private=1
|
||
) AS phone_roaming_sms_messages_private,
|
||
|
||
(
|
||
SELECT IFNULL(SUM(price), 0)
|
||
FROM phone_sms_messages
|
||
WHERE phone_invoice_users_id=" . $this->id . " AND private=0
|
||
) AS phone_sms_messages_company,(
|
||
SELECT IFNULL(SUM(price), 0)
|
||
FROM phone_sms_messages
|
||
WHERE phone_invoice_users_id=" . $this->id . " AND private=1
|
||
) AS phone_sms_messages_private,
|
||
|
||
(
|
||
SELECT IFNULL(SUM(price), 0)
|
||
FROM phone_roaming_sms_messages
|
||
WHERE phone_invoice_users_id=" . $this->id . " AND private=0
|
||
) AS phone_roaming_sms_messages_company,(
|
||
SELECT IFNULL(SUM(price), 0)
|
||
FROM phone_roaming_sms_messages
|
||
WHERE phone_invoice_users_id=" . $this->id . " AND private=1
|
||
) AS phone_roaming_sms_messages_private"
|
||
)->current();
|
||
}
|
||
|
||
/**
|
||
* Search for user with specific phone number
|
||
* @param string $phone_number Number with prefix
|
||
* @return integer ID or zero if cannot find nuber in database
|
||
*/
|
||
public function get_user_id($phone_number)
|
||
{
|
||
$query = self::$db->query(
|
||
"SELECT id FROM users WHERE phone = ? OR phone = ?;",
|
||
array($phone_number, substr($phone_number, 4))
|
||
);
|
||
|
||
if ($query->count() > 0)
|
||
{
|
||
return $query->current()->id;
|
||
}
|
||
|
||
// type 21 is phone number
|
||
$query = self::$db->query(
|
||
"SELECT user_id FROM contacts WHERE type = 21 AND (value = ? OR value = ?);",
|
||
array($phone_number, substr($phone_number, 4))
|
||
);
|
||
|
||
return ($query->count() > 0) ? $query->current()->user_id : 0;
|
||
}
|
||
|
||
}
|
freenetis/tags/0.1.1/application/models/phone_sms_message.php | ||
---|---|---|
<?php defined('SYSPATH') or die('No direct script access.');
|
||
/*
|
||
*/
|
||
|
||
/**
|
||
* Descriptions of Phone_sms_message_Model
|
||
*
|
||
* @author Ondřej Fibich
|
||
*/
|
||
class Phone_sms_message_Model extends ORM {
|
||
|
||
protected $belong_to = array('phone_invoice_user');
|
||
|
||
public function get_sms_mesages_from($phone_invoice_users_id)
|
||
{
|
||
return self::$db->query(
|
||
"SELECT phone_invoice_users.user_id, phone_sms_messages.id, phone_sms_messages.price,
|
||
phone_sms_messages.private, phone_sms_messages.datetime,
|
||
phone_sms_messages.number, phone_sms_messages.period,
|
||
phone_sms_messages.description
|
||
FROM phone_sms_messages
|
||
LEFT JOIN phone_invoice_users ON phone_sms_messages.phone_invoice_users_id = phone_invoice_users.id
|
||
WHERE phone_sms_messages.phone_invoice_users_id=?",
|
||
array($phone_invoice_users_id)
|
||
);
|
||
}
|
||
|
||
/**
|
||
* Set private flag
|
||
* @param integer $phone_invoice_users_id
|
||
* @param array $private_ids Key id, value const 1
|
||
*/
|
||
public function set_sms_mesages_private($phone_invoice_users_id, $private_ids)
|
||
{
|
||
// reset all
|
||
self::$db->query(
|
||
"UPDATE phone_sms_messages
|
||
SET private = '0'
|
||
WHERE phone_invoice_users_id=?",
|
||
array($phone_invoice_users_id)
|
||
);
|
||
// set private
|
||
if (is_array($private_ids) && count($private_ids))
|
||
{
|
||
self::$db->query(
|
||
"UPDATE phone_sms_messages
|
||
SET private = 1
|
||
WHERE id IN(" . implode(',', array_keys($private_ids)) . ")"
|
||
);
|
||
}
|
||
}
|
||
|
||
}
|
freenetis/tags/0.1.1/application/models/phone_fixed_call.php | ||
---|---|---|
<?php defined('SYSPATH') or die('No direct script access.');
|
||
/*
|
||
*/
|
||
|
||
/**
|
||
* Descriptions of Phone_fixed_call_Model
|
||
*
|
||
* @author Ondřej Fibich
|
||
*/
|
||
class Phone_fixed_call_Model extends ORM {
|
||
|
||
protected $belong_to = array('phone_invoice_user');
|
||
|
||
public function get_fixed_calls_from($phone_invoice_users_id)
|
||
{
|
||
return self::$db->query(
|
||
"SELECT phone_invoice_users.user_id, phone_fixed_calls.id, phone_fixed_calls.price,
|
||
phone_fixed_calls.private, phone_fixed_calls.datetime,
|
||
phone_fixed_calls.length, phone_fixed_calls.number, phone_fixed_calls.period,
|
||
phone_fixed_calls.destiny
|
||
FROM phone_fixed_calls
|
||
LEFT JOIN phone_invoice_users ON phone_fixed_calls.phone_invoice_users_id = phone_invoice_users.id
|
||
WHERE phone_fixed_calls.phone_invoice_users_id=?",
|
||
array($phone_invoice_users_id)
|
||
);
|
||
}
|
||
|
||
/**
|
||
* Set private flag
|
||
* @param integer $phone_invoice_users_id
|
||
* @param array $private_ids Key id, value const 1
|
||
*/
|
||
public function set_fixed_calls_private($phone_invoice_users_id, $private_ids)
|
||
{
|
||
// reset all
|
||
self::$db->query(
|
||
"UPDATE phone_fixed_calls
|
||
SET private = '0'
|
||
WHERE phone_invoice_users_id=?",
|
||
array($phone_invoice_users_id)
|
||
);
|
||
// set private
|
||
if (is_array($private_ids) && count($private_ids))
|
||
{
|
||
self::$db->query(
|
||
"UPDATE phone_fixed_calls
|
||
SET private = 1
|
||
WHERE ID IN(" . implode(',', array_keys($private_ids)) . ")",
|
||
array($phone_invoice_users_id)
|
||
);
|
||
}
|
||
}
|
||
|
||
}
|
freenetis/tags/0.1.1/application/models/phone_vpn_call.php | ||
---|---|---|
<?php defined('SYSPATH') or die('No direct script access.');
|
||
/*
|
||
*/
|
||
|
||
/**
|
||
* Descriptions of Phone_vpn_call_Model
|
||
*
|
||
* @author Ondřej Fibich
|
||
*/
|
||
class Phone_vpn_call_Model extends ORM {
|
||
|
||
protected $belong_to = array('phone_invoice_user');
|
||
|
||
public function get_vpn_calls_from($phone_invoice_users_id)
|
||
{
|
||
return self::$db->query(
|
||
"SELECT phone_invoice_users.user_id, phone_vpn_calls.id, phone_vpn_calls.price,
|
||
phone_vpn_calls.private, phone_vpn_calls.datetime,
|
||
phone_vpn_calls.length, phone_vpn_calls.number, phone_vpn_calls.period,
|
||
phone_vpn_calls.group
|
||
FROM phone_vpn_calls
|
||
LEFT JOIN phone_invoice_users ON phone_vpn_calls.phone_invoice_users_id = phone_invoice_users.id
|
||
WHERE phone_vpn_calls.phone_invoice_users_id=?",
|
||
array($phone_invoice_users_id)
|
||
);
|
||
}
|
||
|
||
/**
|
||
* Set private flag
|
||
* @param integer $phone_invoice_users_id
|
||
* @param array $private_ids Key id, value const 1
|
||
*/
|
||
public function set_vpn_calls_private($phone_invoice_users_id, $private_ids)
|
||
{
|
||
// reset all
|
||
self::$db->query(
|
||
"UPDATE phone_vpn_calls
|
||
SET private = '0'
|
||
WHERE phone_invoice_users_id=?",
|
||
array($phone_invoice_users_id)
|
||
);
|
||
// set private
|
||
if (is_array($private_ids) && count($private_ids))
|
||
{
|
||
self::$db->query(
|
||
"UPDATE phone_vpn_calls
|
||
SET private = 1
|
||
WHERE ID IN(" . implode(',', array_keys($private_ids)) . ")",
|
||
array($phone_invoice_users_id)
|
||
);
|
||
}
|
||
}
|
||
|
||
}
|
freenetis/tags/0.1.1/application/models/phone_roaming_sms_message.php | ||
---|---|---|
<?php defined('SYSPATH') or die('No direct script access.');
|
||
/*
|
||
*/
|
||
|
||
/**
|
||
* Descriptions of Phone_roaming_sms_message_Model
|
||
*
|
||
* @author Ondřej Fibich
|
||
*/
|
||
class Phone_roaming_sms_message_Model extends ORM {
|
||
|
||
protected $belong_to = array('phone_invoice_user');
|
||
|
||
public function get_roaming_sms_messages_from($phone_invoice_users_id)
|
||
{
|
||
return self::$db->query(
|
||
"SELECT phone_invoice_users.user_id, phone_roaming_sms_messages.id, phone_roaming_sms_messages.price,
|
||
phone_roaming_sms_messages.private, phone_roaming_sms_messages.datetime,
|
||
phone_roaming_sms_messages.roaming_zone
|
||
FROM phone_roaming_sms_messages
|
||
LEFT JOIN phone_invoice_users ON phone_roaming_sms_messages.phone_invoice_users_id = phone_invoice_users.id
|
||
WHERE phone_roaming_sms_messages.phone_invoice_users_id=?",
|
||
array($phone_invoice_users_id)
|
||
);
|
||
}
|
||
|
||
/**
|
||
* Set private flag
|
||
* @param integer $phone_invoice_users_id
|
||
* @param array $private_ids Key id, value const 1
|
||
*/
|
||
public function set_roaming_sms_messages_private($phone_invoice_users_id, $private_ids)
|
||
{
|
||
// reset all
|
||
self::$db->query(
|
||
"UPDATE phone_roaming_sms_messages
|
||
SET private = '0'
|
||
WHERE phone_invoice_users_id=?",
|
||
array($phone_invoice_users_id)
|
||
);
|
||
// set private
|
||
if (is_array($private_ids) && count($private_ids))
|
||
{
|
||
self::$db->query(
|
||
"UPDATE phone_roaming_sms_messages
|
||
SET private = 1
|
||
WHERE id IN(" . implode(',', array_keys($private_ids)) . ")"
|
||
);
|
||
}
|
||
}
|
||
|
||
}
|
freenetis/tags/0.1.1/application/models/user.php | ||
---|---|---|
<?php
|
||
class User_Model extends ORM
|
||
{
|
||
// static constants for type of user, only first user has type member
|
||
public static $member = 1;
|
||
public static $user = 2;
|
||
|
||
protected $belongs_to = array('member');
|
||
protected $has_many = array('contacts', 'jobs', 'devices', 'phone_invoices_users');
|
||
|
||
public $arr_sql = array('id' => 'u.id', 'name' => 'u.name', 'surname' => 'u.surname', 'email' => 'u.email', 'phone' => 'u.phone', 'login' => 'u.login', 'member_name' => 'm.name');
|
||
|
||
public function __construct($id = false)
|
||
{
|
||
parent::__construct($id);
|
||
}
|
||
|
||
public function get_all_users($limit_from = 0, $limit_results = 50, $order_by = 'id', $order_by_direction = 'ASC', $filter_values = array(), $member_id=NULL)
|
||
{
|
||
$where = '';
|
||
if(count($filter_values)>0) $where .= 'WHERE ';
|
||
foreach($filter_values as $key => $value)
|
||
{
|
||
if($key!='submit')
|
||
{
|
||
if($where!='WHERE ') $where .= ' AND ';
|
||
$where .= $this->arr_sql[$key].' LIKE \'%'.trim($value).'%\' COLLATE utf8_general_ci';
|
||
}
|
||
}
|
||
if ($member_id)
|
||
{
|
||
if($where=='') $where = 'WHERE u.member_id = '.$member_id;
|
||
else $where .= ' AND u.member_id = '.$member_id;
|
||
}
|
||
return self::$db->query('SELECT
|
||
u.id, u.name, u.surname, u.login, u.email, u.phone, u.member_id
|
||
FROM users u
|
||
'.$where.'
|
||
ORDER BY '.$this->arr_sql[$order_by].' '.$order_by_direction.'
|
||
LIMIT '.$limit_from.','.$limit_results
|
||
);
|
||
}
|
||
|
||
public function login_request($username = '', $password = '')
|
||
{
|
||
$query = self::$db->from('users')->select('id')->where(array('login' => $username, 'password' => sha1($password)))->get();
|
||
return ($query->count()) ? $query->current()->id : 0;
|
||
// return (bool) self::$db->where('login', $username)->where('password', sha1($password))->count_records('user');
|
||
}
|
||
|
||
public function count_all_users($filter_values = array(), $member_id=NULL)
|
||
{
|
||
$where = '';
|
||
|
||
if(count($filter_values)>0) $where .= 'WHERE ';
|
||
|
||
foreach($filter_values as $key => $value)
|
||
{
|
||
if($key!='submit')
|
||
{
|
||
if($where!='WHERE ') $where .= ' AND ';
|
||
$where .= $this->arr_sql[$key].' LIKE \'%'.trim($value).'%\' COLLATE utf8_general_ci';
|
||
}
|
||
}
|
||
|
||
if($member_id)
|
||
{
|
||
if($where=='') $where = 'WHERE u.member_id = '.$member_id;
|
||
else $where .= ' AND u.member_id = '.$member_id;
|
||
}
|
||
|
||
$users = self::$db->query('select u.id from users u join members m on m.id = u.member_id '.$where);
|
||
return count($users);
|
||
}
|
||
|
||
public function count_all_users_by_member($member_id = NULL)
|
||
{
|
||
return (int) self::$db->where('member_id',$member_id)->count_records('users');
|
||
}
|
||
|
||
public function username_exist($username, $user_id = null)
|
||
{
|
||
if (isset($user_id)) {
|
||
$count=self::$db->where(array('login' => $username, 'id!=' => $user_id))->count_records('users');
|
||
//print_r("userid=$user_id, count=$count");
|
||
return (bool)$count;
|
||
}
|
||
else return (bool) self::$db->where('login', $username)->count_records('users');
|
||
}
|
||
|
||
public function phone_exist($phone, $user_id = null)
|
||
{
|
||
if (isset($user_id))
|
||
return (bool) self::$db->where(array('phone' => $phone, 'id!=' => $user_id))->count_records('users');
|
||
else
|
||
return (bool) self::$db->where('phone', $phone)->count_records('users');
|
||
}
|
||
|
||
public function email_exist($email, $user_id = null)
|
||
{
|
||
if (isset($user_id)) return (bool) self::$db->where(array('email' => $email, 'id!=' => $user_id))->count_records('users');
|
||
else return (bool) self::$db->where('email', $email)->count_records('users');
|
||
}
|
||
|
||
public function get_users($user)
|
||
{
|
||
return self::$db->query("SELECT id, CONCAT(surname,' ',name,' - ',login) as user
|
||
FROM users
|
||
WHERE CONCAT(surname,' ',name,' - ',login) LIKE '" . $user . "%'
|
||
GROUP BY CONCAT(surname,' ',name,' - ',login)
|
||
");
|
||
}
|
||
public function get_all_user_names() {
|
||
return self::$db->query("SELECT id, CONCAT(name,' ',surname) as username
|
||
FROM users
|
||
GROUP BY CONCAT(name,' ',surname)
|
||
");
|
||
}
|
||
|
||
public function get_his_users_names($user_id) {
|
||
$q="SELECT u2.id, CONCAT(u2.name,' ',u2.surname) AS username FROM users AS u1
|
||
JOIN users AS u2 ON u1.id=$user_id AND u1.member_id=u2.member_id";
|
||
return self::$db->query($q);
|
||
}
|
||
|
||
public function get_his_username($user_id) {
|
||
$q="SELECT id, CONCAT(name,' ',surname) AS username FROM users
|
||
WHERE id=$user_id";
|
||
return self::$db->query($q);
|
||
}
|
||
|
||
public function get_usernames($user)
|
||
{
|
||
return self::$db->query("SELECT id, CONCAT(name,' ',surname) as username
|
||
FROM users
|
||
WHERE CONCAT(name,' ',surname) LIKE '" . $user . "%'
|
||
GROUP BY CONCAT(name,' ',surname)
|
||
");
|
||
}
|
||
|
||
/**
|
||
* Function searches for items dependent on given user. Used for deleting user.
|
||
* @param $user_id
|
||
* @return unknown_type
|
||
*/
|
||
public function count_dependent_items($user_id)
|
||
{
|
||
return self::$db->query("
|
||
SELECT COUNT(*) AS total FROM
|
||
(
|
||
SELECT d.id
|
||
FROM devices d
|
||
WHERE d.user_id = $user_id
|
||
UNION
|
||
SELECT j.id
|
||
FROM jobs j
|
||
WHERE j.user_id = $user_id
|
||
UNION
|
||
SELECT da.id
|
||
FROM device_admins da
|
||
WHERE da.user_id = $user_id
|
||
UNION
|
||
SELECT de.id
|
||
FROM device_engineers de
|
||
WHERE de.user_id = $user_id
|
||
UNION
|
||
SELECT c.id
|
||
FROM contacts c
|
||
WHERE c.user_id = $user_id
|
||
) di
|
||
")->current()->total;
|
||
}
|
||
|
||
}
|
||
|
||
?>
|
freenetis/tags/0.1.1/application/models/private_phone_contact.php | ||
---|---|---|
<?php defined('SYSPATH') or die('No direct script access.');
|
||
/*
|
||
*/
|
||
|
||
/**
|
||
* Descriptions of Private_phone_contact_Model
|
||
*
|
||
* @author Ondřej Fibich
|
||
*/
|
||
class Private_phone_contact_Model extends ORM {
|
||
|
||
protected $belong_to = array('user');
|
||
|
||
/**
|
||
* Search for users contact with specific phone number
|
||
* @param integer $user_id
|
||
* @param string $phone_number Number with prefix
|
||
* @return integer ID or zero if cannot find nuber in database
|
||
*/
|
||
public function get_contact_id($user_id, $phone_number)
|
||
{
|
||
$query = self::$db->query(
|
||
"SELECT id FROM private_phone_contacts WHERE user_id = ? AND phone_number = ?;",
|
||
array($user_id, $phone_number)
|
||
);
|
||
|
||
if ($query->count() > 0)
|
||
{
|
||
return $query->current()->id;
|
||
}
|
||
|
||
return 0;
|
||
}
|
||
|
||
}
|
freenetis/tags/0.1.1/application/models/phone_pay.php | ||
---|---|---|
<?php defined('SYSPATH') or die('No direct script access.');
|
||
/*
|
||
*/
|
||
|
||
/**
|
||
* Descriptions of Phone_pay_Model
|
||
*
|
||
* @author Ondřej Fibich
|
||
*/
|
||
class Phone_pay_Model extends ORM {
|
||
|
||
protected $belong_to = array('phone_invoice_user');
|
||
|
||
public function get_pays_from($phone_invoice_users_id)
|
||
{
|
||
return self::$db->query(
|
||
"SELECT phone_invoice_users.user_id, phone_pays.id, phone_pays.price,
|
||
phone_pays.private, phone_pays.datetime,
|
||
phone_pays.number, phone_pays.description
|
||
FROM phone_pays
|
||
LEFT JOIN phone_invoice_users ON phone_pays.phone_invoice_users_id = phone_invoice_users.id
|
||
WHERE phone_pays.phone_invoice_users_id=?",
|
||
array($phone_invoice_users_id)
|
||
);
|
||
}
|
||
|
||
/**
|
||
* Set private flag
|
||
* @param integer $phone_invoice_users_id
|
||
* @param array $private_ids Key id, value const 1
|
||
*/
|
||
public function set_pays_private($phone_invoice_users_id, $private_ids)
|
||
{
|
||
// reset all
|
||
self::$db->query(
|
||
"UPDATE phone_pays
|
||
SET private = '0'
|
||
WHERE phone_invoice_users_id=?",
|
||
array($phone_invoice_users_id)
|
||
);
|
||
// set private
|
||
if (is_array($private_ids) && count($private_ids))
|
||
{
|
||
self::$db->query(
|
||
"UPDATE phone_pays
|
||
SET private = '1'
|
||
WHERE id IN(" . implode(',', array_keys($private_ids)) . ")"
|
||
);
|
||
}
|
||
}
|
||
|
||
}
|
freenetis/tags/0.1.1/application/models/phone_connection.php | ||
---|---|---|
<?php defined('SYSPATH') or die('No direct script access.');
|
||
/*
|
||
*/
|
||
|
||
/**
|
||
* Descriptions of Phone_connection_Model
|
||
*
|
||
* @author Ondřej Fibich
|
||
*/
|
||
class Phone_connection_Model extends ORM {
|
||
|
||
protected $belong_to = array('phone_invoice_user');
|
||
|
||
public function get_connections_from($phone_invoice_users_id)
|
||
{
|
||
return self::$db->query(
|
||
"SELECT phone_invoice_users.user_id, phone_connections.id, phone_connections.price,
|
||
phone_connections.private, phone_connections.datetime,
|
||
phone_connections.transfered, phone_connections.period,
|
||
phone_connections.apn
|
||
FROM phone_connections
|
||
LEFT JOIN phone_invoice_users ON phone_connections.phone_invoice_users_id = phone_invoice_users.id
|
||
WHERE phone_connections.phone_invoice_users_id=?",
|
||
array($phone_invoice_users_id)
|
||
);
|
||
}
|
||
|
||
/**
|
||
* Set private flag
|
||
* @param integer $phone_invoice_users_id
|
||
* @param array $private_ids Key id, value const 1
|
||
*/
|
||
public function set_connections_private($phone_invoice_users_id, $private_ids)
|
||
{
|
||
// reset all
|
||
self::$db->query(
|
||
"UPDATE phone_connections
|
||
SET private = '0'
|
||
WHERE phone_invoice_users_id=?",
|
||
array($phone_invoice_users_id)
|
||
);
|
||
// set private
|
||
if (is_array($private_ids) && count($private_ids))
|
||
{
|
||
self::$db->query(
|
||
"UPDATE phone_connections
|
||
SET private = 1
|
||
WHERE ID IN(" . implode(',', array_keys($private_ids)) . ")",
|
||
array($phone_invoice_users_id)
|
||
);
|
||
}
|
||
}
|
||
|
||
}
|
freenetis/tags/0.1.1/application/models/phone_call.php | ||
---|---|---|
<?php defined('SYSPATH') or die('No direct script access.');
|
||
/*
|
||
*/
|
||
|
||
/**
|
||
* Descriptions of Phone_calls_Model
|
||
*
|
||
* @author Ondřej Fibich
|
||
*/
|
||
class Phone_call_Model extends ORM
|
||
{
|
||
protected $belong_to = array('phone_invoice_user');
|
||
|
||
public function get_calls_from($phone_invoice_users_id)
|
||
{
|
||
return self::$db->query(
|
||
"SELECT phone_invoice_users.user_id, phone_calls.id, phone_calls.price,
|
||
phone_calls.private, phone_calls.datetime,
|
||
phone_calls.length, phone_calls.number, phone_calls.period
|
||
FROM phone_calls
|
||
LEFT JOIN phone_invoice_users ON phone_calls.phone_invoice_users_id = phone_invoice_users.id
|
||
WHERE phone_calls.phone_invoice_users_id=?",
|
||
array($phone_invoice_users_id)
|
||
);
|
||
}
|
||
|
||
/**
|
||
* Set private flag
|
||
* @param integer $phone_invoice_users_id
|
||
* @param array $private_ids Key id, value const 1
|
||
*/
|
||
public function set_calls_private($phone_invoice_users_id, $private_ids)
|
||
{
|
||
// reset all
|
||
self::$db->query(
|
||
"UPDATE phone_calls
|
||
SET private = '0'
|
||
WHERE phone_invoice_users_id=?",
|
||
array($phone_invoice_users_id)
|
||
);
|
||
// set private
|
||
if (is_array($private_ids) && count($private_ids))
|
||
{
|
||
self::$db->query(
|
||
"UPDATE phone_calls
|
||
SET private = 1
|
||
WHERE ID IN(" . implode(',', array_keys($private_ids)) . ")",
|
||
array($phone_invoice_users_id)
|
||
);
|
||
}
|
||
}
|
||
|
||
}
|
freenetis/tags/0.1.1/application/models/phone_invoice.php | ||
---|---|---|
<?php defined('SYSPATH') or die('No direct script access.');
|
||
/*
|
||
*/
|
||
|
||
/**
|
||
* Descriptions of Phone_invoice_Model
|
||
*
|
||
* @author Ondřej Fibich
|
||
*/
|
||
class Phone_invoice_Model extends ORM {
|
||
|
||
protected $has_many = array('phone_invoice_users');
|
||
|
||
/**
|
||
* Test if invoice is not in database already
|
||
* @return bool
|
||
*/
|
||
public function is_unique()
|
||
{
|
||
return (self::$db->query(
|
||
"SELECT COUNT(*) AS count FROM `phone_invoices` " .
|
||
"WHERE billing_period_from = '" . $this->billing_period_from . "' " .
|
||
"AND billing_period_to = '" . $this->billing_period_to . "' " .
|
||
"AND specific_symbol = '" . $this->specific_symbol . "' " .
|
||
"AND variable_symbol = '" . $this->variable_symbol . "' " .
|
||
"AND date_of_issuance = '" . $this->date_of_issuance . "';"
|
||
)->current()->count <= 0);
|
||
}
|
||
|
||
/**
|
||
* @return integer Count
|
||
*/
|
||
public function count_all_phone_invoices()
|
||
{
|
||
return self::$db->query(
|
||
"SELECT COUNT(id) AS count FROM `phone_invoices`;"
|
||
)->current()->count;
|
||
}
|
||
|
||
/**
|
||
* Return al phone invoicces
|
||
* @return ORM iterator
|
||
*/
|
||
function get_all_phone_invoices()
|
||
{
|
||
return self::$db->query(
|
||
"SELECT id, date_of_issuance, billing_period_from,
|
||
billing_period_to, variable_symbol, specific_symbol,
|
||
(total_price+tax) AS price, locked
|
||
FROM `phone_invoices`"
|
||
);
|
||
}
|
||
|
||
/**
|
||
* Delete phone incvoice and data which are in relation 1 to may with invoice
|
||
* @param bool $all
|
||
*/
|
||
public function delete($all = FALSE) {
|
||
$query = self::$db->query(
|
||
"SELECT id FROM phone_invoice_users " .
|
||
"WHERE phone_invoice_id = " . $this->id
|
||
);
|
||
|
||
foreach ($query as $piu)
|
||
{
|
||
self::$db->query("DELETE FROM phone_calls WHERE phone_invoice_users_id = " . $piu->id);
|
||
self::$db->query("DELETE FROM phone_connections WHERE phone_invoice_users_id = " . $piu->id);
|
||
self::$db->query("DELETE FROM phone_fixed_calls WHERE phone_invoice_users_id = " . $piu->id);
|
||
self::$db->query("DELETE FROM phone_roaming_sms_messages WHERE phone_invoice_users_id = " . $piu->id);
|
||
self::$db->query("DELETE FROM phone_sms_messages WHERE phone_invoice_users_id = " . $piu->id);
|
||
self::$db->query("DELETE FROM phone_vpn_calls WHERE phone_invoice_users_id = " . $piu->id);
|
||
self::$db->query("DELETE FROM phone_pays WHERE phone_invoice_users_id = " . $piu->id);
|
||
}
|
||
|
||
$query = self::$db->query(
|
||
"DELETE FROM phone_invoice_users WHERE phone_invoice_id= " . $this->id
|
||
);
|
||
|
||
parent::delete($all);
|
||
}
|
||
|
||
}
|
freenetis/tags/0.1.1/application/models/approval_template_item.php | ||
---|---|---|
<?php
|
||
/**
|
||
*
|
||
* @author Michal Kliment
|
||
* @copyright (c) 2010 Kliment Michal
|
||
* @license GNU GPLv3
|
||
* @license http://www.gnu.org/licenses/gpl.txt
|
||
*/
|
||
|
||
class Approval_template_item_Model extends ORM
|
||
{
|
||
protected $belongs_to = array('approval_template', 'approval_type');
|
||
|
||
/**
|
||
* @author Michal Kliment
|
||
* Function to return all approval template items belongs to approval template
|
||
* @param number $template_id
|
||
* @return Mysql_Result object
|
||
*/
|
||
public function get_all_items_by_template_id ($template_id)
|
||
{
|
||
return self::$db->query('SELECT i.id as item_id, i.priority, i.approval_template_id, t.id, t.name, ag.id AS group_id, ag.name AS group_name, t.type, t.interval, t.min_suggest_amount
|
||
FROM approval_template_items i
|
||
LEFT JOIN approval_types t ON t.id = i.approval_type_id
|
||
LEFT JOIN aro_groups ag ON t.aro_group_id = ag.id
|
||
WHERE i.approval_template_id = '.$template_id.'
|
||
ORDER BY i.priority');
|
||
}
|
||
|
||
/**
|
||
* @author Michal Kliment
|
||
* Function to return the lowest priority of approval template
|
||
* @param number $template_id
|
||
* @return number
|
||
*/
|
||
public function get_lowest_priority_of_template($template_id)
|
||
{
|
||
$result = self::$db->query('SELECT min(priority) AS min FROM approval_template_items GROUP BY approval_template_id HAVING approval_template_id = '.$template_id);
|
||
|
||
if (!$result)
|
||
return NULL;
|
||
|
||
return $result->current()->min;
|
||
}
|
||
|
||
/**
|
||
* @author Michal Kliment
|
||
* Function to return the highest priority of approval template
|
||
* @param number $template_id
|
||
* @return number
|
||
*/
|
||
public function get_highest_priority_of_template($template_id)
|
||
{
|
||
$result = self::$db->query('SELECT max(priority) AS max FROM approval_template_items GROUP BY approval_template_id HAVING approval_template_id = '.$template_id);
|
||
|
||
if (!$result)
|
||
return NULL;
|
||
|
||
return $result->current()->max;
|
||
}
|
||
|
||
/**
|
||
* @author Michal Kliment
|
||
* Function to check if user have rights to vote
|
||
* @param number $template_id
|
||
* @param number $user_id
|
||
* @return bool
|
||
*/
|
||
public function check_user_vote_rights($template_id, $user_id, $suggest_amount = 0)
|
||
{
|
||
return (bool) $this->get_aro_group_by_approval_template_id_and_user_id($template_id, $user_id, $suggest_amount);
|
||
}
|
||
|
||
/**
|
||
* @author Michal Kliment
|
||
* Returns all group assigned to approval
|
||
* @param number $template_id
|
||
* @param number $suggest_amount
|
||
* @param number $user_id
|
||
* @return Mysql Result object
|
||
*/
|
||
public function get_aro_groups_by_approval_template_id($template_id, $suggest_amount = 0, $user_id = 0)
|
||
{
|
||
$cond = ($suggest_amount) ? ' AND t.min_suggest_amount <= '.$suggest_amount : '';
|
||
|
||
return self::$db->query('SELECT a.id, a.name FROM approval_template_items i
|
||
LEFT JOIN approval_types t ON i.approval_type_id = t.id
|
||
LEFT JOIN aro_groups a ON t.aro_group_id = a.id
|
||
WHERE i.approval_template_id = '.$template_id.$cond.'
|
||
ORDER BY i.priority');
|
||
}
|
||
|
||
/**
|
||
* @author Michal Kliment
|
||
* Returns aro group assigned to user and approval
|
||
* @param number $template_id
|
||
* @param number $user_id
|
||
* @param number $suggest_amount
|
||
* @return Mysql Result object
|
||
*/
|
||
public function get_aro_group_by_approval_template_id_and_user_id($template_id, $user_id, $suggest_amount = 0)
|
||
{
|
||
$cond = ($suggest_amount) ? ' AND t.min_suggest_amount <= '.$suggest_amount : '';
|
||
|
||
$result = self::$db->query('SELECT a.id, a.name FROM approval_template_items i
|
||
LEFT JOIN approval_types t ON i.approval_type_id = t.id
|
||
LEFT JOIN aro_groups a ON t.aro_group_id = a.id
|
||
LEFT JOIN groups_aro_map m ON a.id = m.group_id
|
||
WHERE i.approval_template_id = '.$template_id.' AND m.aro_id = '.$user_id.$cond.'
|
||
ORDER BY i.priority');
|
||
// no record, returns null
|
||
if (!$result)
|
||
return NULL;
|
||
|
||
// returns first record
|
||
return $result->current();
|
||
}
|
||
|
||
/**
|
||
* @author Michal Kliment
|
||
* Returns all aro ids assigned to approval
|
||
* @param number $template_id
|
||
* @param number $suggest_amount
|
||
* @return Mysql Result object
|
||
*/
|
||
public function get_aro_ids_by_approval_template_id($template_id, $suggest_amount = 0)
|
||
{
|
||
$cond = ($suggest_amount) ? ' AND t.min_suggest_amount <= '.$suggest_amount : '';
|
||
|
||
return self::$db->query('SELECT g.aro_id AS id FROM approval_template_items i
|
||
LEFT JOIN approval_types t ON i.approval_type_id = t.id
|
||
LEFT JOIN groups_aro_map g ON t.aro_group_id = g.group_id
|
||
WHERE i.approval_template_id = '.$template_id.$cond.'
|
||
GROUP BY g.aro_id');
|
||
}
|
||
|
||
}
|
||
|
||
?>
|
freenetis/tags/0.1.1/application/models/member.php | ||
---|---|---|
<?php
|
||
class Member_Model extends ORM
|
||
{
|
||
protected $has_many = array('invoices', ' users', 'accounts',
|
||
'transfers', 'bank_accounts', 'membership_interrupts');
|
||
protected $belongs_to = array('address_point');
|
||
|
||
// filtering by redirection
|
||
public static $all = 1;
|
||
public static $all_redirected = 2;
|
||
public static $membership_interrupt = 3;
|
||
public static $debtors = 4;
|
||
public static $payment_notice = 5;
|
||
public static $optional_message = 6;
|
||
|
||
private static $keys = array(
|
||
'id' => array('type' => 'number', 'column_name' => 'm.id'),
|
||
'town' => array('type' => 'string'),
|
||
'street' => array('type' => 'string'),
|
||
'name' => array('type' => 'string', 'column_name' => 'm.name'),
|
||
'variable_symbol' => array('type' => 'string'),
|
||
'comment' => array('type' => 'string', 'column_name' => 'm.comment'),
|
||
'street_number' => array('type' => 'number')
|
||
);
|
||
|
||
public function __construct($id = false)
|
||
{
|
||
parent::__construct($id);
|
||
}
|
||
|
||
/**
|
||
* Function gets list of all members from database.
|
||
* @param $limit_from starting row
|
||
* @param $limit_results number of rows
|
||
* @param $order_by sorting column
|
||
* @param $order_by_direction sorting direction
|
||
* @param $filter_values used for filtering
|
||
* @return unknown_type
|
||
*/
|
||
public function get_all_members($limit_from = 0, $limit_results = 50, $order_by = 'id', $order_by_direction = 'asc', $filter_values = array())
|
||
{
|
||
// building of where clause
|
||
$where = array();
|
||
foreach($filter_values as $key => $value)
|
||
{
|
||
if (isset(self::$keys[$key]))
|
||
{
|
||
$column_name = (isset(self::$keys[$key]['column_name'])) ? self::$keys[$key]['column_name'] : $key;
|
||
|
||
// key is type of string
|
||
if (self::$keys[$key]['type'] == 'string')
|
||
{
|
||
$value = trim($value);
|
||
$items = explode(',',trim($value,','));
|
||
|
||
// split into array of conditions
|
||
$clauses = array();
|
||
foreach ($items as $item)
|
||
{
|
||
$item = trim($item);
|
||
if ($item!='')
|
||
$clauses[] = "$column_name LIKE '%".$item."%' COLLATE utf8_general_ci";
|
||
}
|
||
|
||
// convert array of conditions to one string condition
|
||
$where[] = '('.implode(' OR ', $clauses).')';
|
||
}
|
||
else if (self::$keys[$key]['type'] == 'number')
|
||
{
|
||
$value = trim($value);
|
||
|
||
// value is interval format
|
||
if (preg_match('/^([0-9]+)?-([0-9]+)?$/', $value, $matches))
|
||
{
|
||
// finding min and max of interval
|
||
$min = ($matches[1]!='')? $matches[1] : 0;
|
||
$max = (isset($matches[2]) && $matches[2]!='')? $matches[2] : 0;
|
||
|
||
if ($min && $max)
|
||
$where[] = "$column_name >= ".$min." AND $column_name <= ".$max;
|
||
else if ($min)
|
||
$where[] = "$column_name >= ".$min;
|
||
else
|
||
$where[] = "$column_name <= ".$max;
|
||
}
|
||
// value is one number
|
||
else
|
||
$where[] = "$column_name = ".(int)$value;
|
||
}
|
||
}
|
||
|
||
if ($key == 'type' && $value != 0)
|
||
$where[] = "m.type = $value";
|
||
|
||
if ($key == 'redirect' && $value != self::$all)
|
||
{
|
||
if ($value == self::$all_redirected)
|
||
$where[] = "m.redirect > 0";
|
||
if ($value == self::$membership_interrupt)
|
||
$where[] = "m.redirect & 1 = 1";
|
||
if ($value == self::$debtors)
|
||
$where[] = "m.redirect & 2 = 2";
|
||
if ($value == self::$payment_notice)
|
||
$where[] = "m.redirect & 4 = 4";
|
||
if ($value == self::$optional_message)
|
||
$where[] = "m.redirect & 8 = 8";
|
||
}
|
||
}
|
||
$where = implode(" AND ",$where);
|
||
$where = ($where!='') ? 'WHERE '.$where : '';
|
||
|
||
return self::$db->query("SELECT
|
||
m.id, m.registration, m.name,
|
||
s.street, ap.street_number, t.town, t.quarter,
|
||
m.variable_symbol, a.id AS aid,
|
||
(q2.inbound - q2.outbound) AS balance,
|
||
m.redirect
|
||
FROM members m
|
||
LEFT JOIN address_points ap ON m.address_point_id = ap.id
|
||
LEFT JOIN streets s ON ap.street_id = s.id
|
||
LEFT JOIN towns t ON ap.town_id = t.id
|
||
LEFT JOIN accounts a ON a.member_id = m.id AND m.id <> 1
|
||
LEFT JOIN
|
||
(
|
||
SELECT q1.id, q1.inbound, IFNULL(SUM(t.amount),0) AS outbound
|
||
FROM
|
||
(
|
||
SELECT a.id, IFNULL(SUM(t.amount),0) AS inbound
|
||
FROM accounts a
|
||
LEFT JOIN transfers t ON t.destination_id = a.id
|
||
GROUP BY a.id
|
||
) q1
|
||
LEFT JOIN transfers t ON t.origin_id = q1.id
|
||
GROUP BY q1.id
|
||
) q2 ON q2.id = a.id
|
||
$where
|
||
ORDER BY $order_by $order_by_direction
|
||
LIMIT $limit_from, $limit_results"
|
||
);
|
||
}
|
||
|
||
/**
|
||
* Function counts all members.
|
||
* @param $filter_values
|
||
* @return unknown_type
|
||
*/
|
||
public function count_all_members($filter_values = array())
|
||
{
|
||
// building of where clause
|
||
$where = array();
|
||
foreach($filter_values as $key => $value)
|
||
{
|
||
if (isset(self::$keys[$key]))
|
||
{
|
||
$column_name = (isset(self::$keys[$key]['column_name'])) ? self::$keys[$key]['column_name'] : $key;
|
||
|
||
// key is type of string
|
||
if (self::$keys[$key]['type'] == 'string')
|
||
{
|
||
$value = trim($value);
|
||
$items = explode(',',trim($value,','));
|
||
|
||
// split into array of conditions
|
||
$clauses = array();
|
||
foreach ($items as $item)
|
||
{
|
||
$item = trim($item);
|
||
if ($item!='')
|
||
$clauses[] = "$column_name LIKE '%".$item."%' COLLATE utf8_general_ci";
|
||
}
|
||
|
||
// convert array of conditions to one string condition
|
||
$where[] = '('.implode(' OR ', $clauses).')';
|
||
}
|
||
else if (self::$keys[$key]['type'] == 'number')
|
||
{
|
||
$value = trim($value);
|
||
|
||
// value is interval format
|
||
if (preg_match('/^([0-9]+)?-([0-9]+)?$/', $value, $matches))
|
||
{
|
||
// finding min and max of interval
|
||
$min = ($matches[1]!='')? $matches[1] : 0;
|
||
$max = (isset($matches[2]) && $matches[2]!='')? $matches[2] : 0;
|
||
|
||
if ($min && $max)
|
||
$where[] = "$column_name >= ".$min." AND $column_name <= ".$max;
|
||
else if ($min)
|
||
$where[] = "$column_name >= ".$min;
|
||
else
|
||
$where[] = "$column_name <= ".$max;
|
||
}
|
||
// value is one number
|
||
else
|
||
$where[] = "$column_name = ".(int)$value;
|
||
}
|
||
}
|
||
|
||
if ($key == 'type' && $value != 0)
|
||
$where[] = "m.type = $value";
|
||
|
||
if ($key == 'redirect' && $value != self::$all)
|
||
{
|
||
if ($value == self::$all_redirected)
|
||
$where[] = "m.redirect > 0";
|
||
if ($value == self::$membership_interrupt)
|
||
$where[] = "m.redirect & 1 = 1";
|
||
if ($value == self::$debtors)
|
||
$where[] = "m.redirect & 2 = 2";
|
||
if ($value == self::$payment_notice)
|
||
$where[] = "m.redirect & 4 = 4";
|
||
if ($value == self::$optional_message)
|
||
$where[] = "m.redirect & 8 = 8";
|
||
}
|
||
}
|
||
$where = implode(" AND ",$where);
|
||
$where = ($where!='') ? 'WHERE '.$where : '';
|
||
|
||
return self::$db->query("SELECT COUNT(*) AS total
|
||
FROM members m
|
||
LEFT JOIN address_points ap ON m.address_point_id = ap.id
|
||
LEFT JOIN streets s ON ap.street_id = s.id
|
||
LEFT JOIN towns t ON ap.town_id = t.id
|
||
$where
|
||
")->current()->total;
|
||
}
|
||
|
||
/**
|
||
* Function gets member for registration table.
|
||
* @param $limit
|
||
* @param $limit_results
|
||
* @return unknown_type
|
||
*/
|
||
public function get_all_members_to_registration($limit = 0, $limit_results = 50)
|
||
{
|
||
return self::$db->query("SELECT m.id, m.registration, CONCAT(u.surname,' ',u.name) as name, s.street, ap.street_number, t.town FROM members m
|
||
LEFT JOIN users u ON m.id = u.member_id and u.type = 1
|
||
LEFT JOIN address_points ap ON m.address_point_id = ap.id
|
||
LEFT JOIN streets s ON ap.street_id = s.id
|
||
LEFT JOIN towns t ON ap.town_id = t.id
|
||
ORDER BY name ASC
|
||
LIMIT $limit, $limit_results");
|
||
}
|
||
|
||
/**
|
||
* Function gets all members to export.
|
||
* @author Jiri Svitak
|
||
* @return unknown_type
|
||
*/
|
||
public function get_all_members_to_export()
|
||
{
|
||
return self::$db->query("SELECT m.id,
|
||
m.registration, m.name AS member_name, m.variable_symbol,
|
||
s.street, ap.street_number,
|
||
t.town, t.quarter,
|
||
u.phone, u.email, u.login AS login_name, u.birthday,
|
||
m.entrance_date,
|
||
IF(m.leaving_date = '0000-00-00', null, m.leaving_date) AS leaving_date,
|
||
IFNULL(f.translated_term, e.value) AS type, m.comment
|
||
FROM members m
|
||
JOIN users u ON u.member_id = m.id AND u.type = ".User_Model::$member."
|
||
LEFT JOIN address_points ap ON m.address_point_id = ap.id
|
||
LEFT JOIN towns t ON ap.town_id = t.id
|
||
LEFT JOIN streets s ON ap.street_id = s.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");
|
||
}
|
||
|
||
/**
|
||
* Function gets all members who have at least one ip address in given subnet.
|
||
* @author Jiri Svitak
|
||
* @param $subnet_id
|
||
* @return unknown_type
|
||
*/
|
||
public function get_members_of_subnet($subnet_id)
|
||
{
|
||
return self::$db->query("SELECT DISTINCT m.id
|
||
FROM members m
|
||
JOIN users u ON m.id = u.member_id
|
||
JOIN devices d ON u.id = d.user_id
|
||
JOIN ifaces i ON d.id = i.device_id
|
||
JOIN ip_addresses ip ON i.id = ip.iface_id
|
||
WHERE ip.subnet_id = $subnet_id
|
||
");
|
||
}
|
||
|
||
/**
|
||
* Function gets member to show or edit.
|
||
* @param $id
|
||
* @return unknown_type
|
||
*/
|
||
public function get_member($id)
|
||
{
|
||
return self::$db->select(
|
||
array('members.registration', 'members.name as namem', 'members.address_point_id',
|
||
'members.variable_symbol',
|
||
'members.type as typem', 'members.organization_identifier', 'members.qos_ceil', 'members.qos_rate',
|
||
'members.entrance_fee', 'members.debt_payment_rate',
|
||
'members.entrance_date', 'members.comment', 'members.locked',
|
||
'enum_types.value as member_type',
|
||
'users.id', 'member_id',
|
||
'users.name', 'middle_name', 'surname',
|
||
'pre_title', 'post_title',
|
||
'login', 'email', 'users.type', 'phone', 'birthday', 'leaving_date','users.application_password','users.comment as ucomment')
|
||
)->from('members')
|
||
->join('enum_types', 'members.type=enum_types.id', NULL, "LEFT")
|
||
->join('users', 'users.member_id = members.id', NULL, "LEFT")
|
||
->where("members.id=$id AND users.id IS NOT NULL")->limit(1)->get();
|
||
}
|
||
|
||
/**
|
||
* @param $phone - string containing a phone number
|
||
* @return first member_id of a member with given phone number
|
||
* This function is used in the Accounts_controller - e.g. in the function
|
||
* "store_transfer_ebanka" for finding the member who made a bank transaction where variable symbol = his phone number
|
||
* Similar function with different purpose can be found in get_member_by_phone
|
||
*/
|
||
public function find_member_id_by_phone($phone) {
|
||
$q="(SELECT member_id FROM users WHERE phone='$phone')
|
||
UNION
|
||
(SELECT users.member_id FROM 'contacts'
|
||
JOIN enum_types ON contacts.type=enum_types.id
|
||
AND enum_types.value='phone' AND contacts.value='$phone'
|
||
JOIN users ON users.id=contacts.user_id)
|
||
";
|
||
$query=self::$db->query($q);
|
||
return $query->current()->member_id;
|
||
|
||
|
||
}
|
||
|
||
/**
|
||
* @author Tomas Dulik
|
||
* @return unknown_type
|
||
*
|
||
*/
|
||
public function find_member_by_phone($phone) {
|
||
$q="(
|
||
SELECT members.*
|
||
FROM members
|
||
JOIN users ON members.id = users.member_id
|
||
AND users.phone = '$phone'
|
||
)
|
||
UNION
|
||
(
|
||
SELECT members.*
|
||
FROM members
|
||
JOIN users ON members.id = users.member_id
|
||
JOIN contacts ON users.id = contacts.user_id
|
||
JOIN enum_types ON contacts.type = enum_types.id
|
||
AND enum_types.value = 'phone'
|
||
AND contacts.value = '$phone'
|
||
)";
|
||
/* Slower alternative:
|
||
$q='SELECT members.id FROM members
|
||
JOIN users ON members.id=users.member_id
|
||
WHERE REPLACE(phone," ", "") LIKE '%$phone%' LIMIT 1';
|
||
*/
|
||
$query=self::$db->query($q);
|
||
return $query->current();
|
||
}
|
||
|
||
/**
|
||
* @author Tomas Dulik, using http://us3.php.net/manual/en/function.crc32.php#86628
|
||
* CRC-CCITT-16 algorithm for the polynomial 0x1021
|
||
*
|
||
* Tomas Dulik note: the algorithm is too nice to be true.
|
||
* The values it computes differ from the values returned by other calculators, e.g.
|
||
* http://zorc.breitbandkatze.de/crc.html
|
||
* @todo: rewrite this function for a proper implementation!!!
|
||
*
|
||
* This function can be used for generating the payments variable symbol from member id.
|
||
* crc16 can check error bursts up to 16bits long, so if the member mistypes such
|
||
* a generated variable symbol, almost any possible error should be detected.
|
||
* @param $data - 16bit integer for which crc16 should be computed
|
||
* @return 16bit int value containing crc16
|
||
*/
|
||
public function crc16($data) {
|
||
$crc = 0xFFFF;
|
||
for ($i = 0; $i < 2; $i++) {
|
||
$x = (($crc >> 8) ^ $data) & 0xFF;
|
||
$data=$data >> 8;
|
||
$x ^= $x >> 4;
|
||
$crc = (($crc << 8) ^ ($x << 12) ^ ($x << 5) ^ $x) & 0xFFFF;
|
||
}
|
||
return $crc;
|
||
}
|
||
|
||
/**
|
||
* @author Tomas Dulik
|
||
* This function can be used for generating variable symbols from member id
|
||
* @param $member_id
|
||
* @return string containing concatenation of member_id and 5 digits of its crc16
|
||
*/
|
||
public function make_variable_symbol($member_id) {
|
||
return $member_id . sprintf("%5d", crc16($member_id));
|
Také k dispozici: Unified diff
Bez adresare kohana