Projekt

Obecné

Profil

« Předchozí | Další » 

Revize 729

Přidáno uživatelem Ondřej Fibich před asi 14 roky(ů)

Bez adresare kohana

Zobrazit rozdíly:

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));
... Rozdílový soubor je zkrácen, protože jeho délka přesahuje max. limit.

Také k dispozici: Unified diff