|
<?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/
|
|
*
|
|
*/
|
|
|
|
/**
|
|
* @package Model
|
|
*
|
|
* @property integer $id
|
|
* @property integer $user_id
|
|
*/
|
|
class Member_Model extends ORM
|
|
{
|
|
protected $has_one = array('allowed_subnets_count', 'members_traffic','members_domicile');
|
|
protected $has_many = array
|
|
(
|
|
'allowed_subnets', 'invoices', 'users', 'accounts',
|
|
'transfers', 'bank_accounts', 'membership_interrupts'
|
|
);
|
|
protected $belongs_to = array('address_point','user');
|
|
|
|
// 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;
|
|
|
|
const TYPE_APPLICANT = 1;
|
|
const TYPE_HONORARY = 3;
|
|
const TYPE_SYMPATHIZING = 4;
|
|
const TYPE_NON = 5;
|
|
const TYPE_FORMER = 15;
|
|
|
|
/**
|
|
* Gets joined values of member for members fees
|
|
*
|
|
* @param integer $member_id
|
|
* @return unknown_type
|
|
*/
|
|
public function get_member_joined($member_id = NULL)
|
|
{
|
|
if (empty($member_id))
|
|
{
|
|
$member_id = $this->id;
|
|
}
|
|
|
|
return $this->select(
|
|
'members.id, members.name as member_name, users.name as name,' .
|
|
'users.surname as surname, members.entrance_date,' .
|
|
'members.leaving_date'
|
|
)->join('users', array
|
|
(
|
|
'users.member_id' => 'members.id',
|
|
'users.type' => 1
|
|
))->where('members.id', $member_id)
|
|
->find();
|
|
}
|
|
|
|
/**
|
|
* Gets joined values of members for members fees
|
|
*
|
|
* @return unknown_type
|
|
*/
|
|
public function get_members_joined()
|
|
{
|
|
if (empty($member_id))
|
|
{
|
|
$member_id = $this->id;
|
|
}
|
|
|
|
return $this->select(
|
|
'members.id, members.name as member_name, users.name as name,' .
|
|
'users.surname as surname, members.entrance_date, ' .
|
|
'members.leaving_date'
|
|
)->join('users', array
|
|
(
|
|
'users.member_id' => 'members.id',
|
|
'users.type' => 1
|
|
))->orderby('surname')
|
|
->find_all();
|
|
}
|
|
|
|
/**
|
|
* Function gets list of all members from database.
|
|
*
|
|
* !!!!!! SECURITY WARNING !!!!!!
|
|
* Be careful when you using this method, param $filter_sql is unprotected
|
|
* for SQL injections, security should be made at controller site using
|
|
* Filter_form class.
|
|
* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
|
|
*
|
|
* @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_sql = "")
|
|
{
|
|
$where = '';
|
|
if ($filter_sql != '')
|
|
$where = "WHERE $filter_sql";
|
|
|
|
// order by direction check
|
|
if (strtolower($order_by_direction) != 'desc')
|
|
{
|
|
$order_by_direction = 'asc';
|
|
}
|
|
// query
|
|
return $this->db->query("
|
|
SELECT id, id AS member_id, registration, name, street, street_number,
|
|
town, quarter, variable_symbol, aid, balance, redirect,
|
|
GROUP_CONCAT(a_comment SEPARATOR ', \n\n') AS a_comment,
|
|
a_comments_thread_id, type, entrance_date, leaving_date
|
|
FROM
|
|
(
|
|
SELECT
|
|
m.id, m.registration, m.name,
|
|
s.street, ap.street_number, t.town, t.quarter,
|
|
m.variable_symbol, a.id AS aid,
|
|
a.balance, m.redirect,
|
|
CONCAT(u.surname,' ',u.name,' (',SUBSTRING(c.datetime,1,10),'):\n',c.text) AS a_comment,
|
|
a.comments_thread_id AS a_comments_thread_id,
|
|
m.type, m.entrance_date, m.leaving_date
|
|
FROM
|
|
(
|
|
SELECT m.id,
|
|
m.name, m.variable_symbol, m.redirect,
|
|
m.address_point_id,
|
|
IF(m.registration = 1, ?, ?) AS registration,
|
|
IFNULL(t.translated_term, e.value) AS type,
|
|
IF(mi.id IS NOT NULL, ?, ?) AS membership_interrupt,
|
|
m.organization_identifier, m.comment,
|
|
m.entrance_date, m.leaving_date
|
|
FROM members m
|
|
LEFT JOIN enum_types e ON m.type = e.id
|
|
LEFT JOIN translations t ON e.value = t.original_term AND lang = ?
|
|
LEFT JOIN
|
|
(
|
|
SELECT mi.id, mi.member_id
|
|
FROM membership_interrupts mi
|
|
LEFT JOIN members_fees mf ON mi.members_fee_id = mf.id
|
|
WHERE mf.activation_date <= CURDATE() AND mf.deactivation_date >= CURDATE()
|
|
) mi ON mi.member_id = m.id
|
|
) AS 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 comments_threads ct ON a.comments_thread_id = ct.id
|
|
LEFT JOIN comments c ON ct.id = c.comments_thread_id
|
|
LEFT JOIN users u ON c.user_id = u.id
|
|
LEFT JOIN enum_types e ON m.type = e.id
|
|
LEFT JOIN (
|
|
SELECT *
|
|
FROM translations
|
|
WHERE lang = ?
|
|
) f ON e.value = f.original_term
|
|
$where
|
|
ORDER BY c.datetime DESC
|
|
) AS q
|
|
GROUP BY id
|
|
ORDER BY " . $this->db->escape_column($order_by) . " $order_by_direction
|
|
LIMIT " . intval($limit_from) . ", " . $limit_results . "
|
|
", array
|
|
(
|
|
url_lang::lang('texts.Yes'),
|
|
url_lang::lang('texts.No'),
|
|
url_lang::lang('texts.Yes'),
|
|
url_lang::lang('texts.No'),
|
|
Config::get('lang'),
|
|
Config::get('lang')
|
|
));
|
|
}
|
|
|
|
/**
|
|
* Function gets list of registered applicans.
|
|
*
|
|
* @return unknown_type
|
|
*/
|
|
public function get_registered_members()
|
|
{
|
|
// query
|
|
return $this->db->query("
|
|
SELECT id, id AS member_id, registration, name, street, street_number,
|
|
town, quarter, variable_symbol, aid, balance, redirect,
|
|
GROUP_CONCAT(a_comment SEPARATOR ', \n\n') AS a_comment,
|
|
a_comments_thread_id, type, entrance_date, leaving_date
|
|
FROM
|
|
(
|
|
SELECT
|
|
m.id, m.registration, m.name,
|
|
s.street, ap.street_number, t.town, t.quarter,
|
|
m.variable_symbol, a.id AS aid,
|
|
a.balance, m.redirect,
|
|
CONCAT(u.surname,' ',u.name,' (',SUBSTRING(c.datetime,1,10),'):\n',c.text) AS a_comment,
|
|
a.comments_thread_id AS a_comments_thread_id,
|
|
m.type, m.entrance_date, m.leaving_date
|
|
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 comments_threads ct ON a.comments_thread_id = ct.id
|
|
LEFT JOIN comments c ON ct.id = c.comments_thread_id
|
|
LEFT JOIN users u ON c.user_id = u.id
|
|
LEFT JOIN enum_types e ON m.type = e.id
|
|
LEFT JOIN (
|
|
SELECT *
|
|
FROM translations
|
|
WHERE lang = ?
|
|
) f ON e.value = f.original_term
|
|
WHERE m.type = ?
|
|
ORDER BY c.datetime DESC
|
|
) AS q
|
|
GROUP BY id
|
|
ORDER BY id DESC
|
|
", array
|
|
(
|
|
Config::get('lang'),
|
|
Member_Model::TYPE_APPLICANT
|
|
));
|
|
}
|
|
|
|
/**
|
|
* Function gets count of registered applicans.
|
|
*
|
|
* @return integer
|
|
*/
|
|
public function count_of_registered_members()
|
|
{
|
|
return $this->db->query("
|
|
SELECT COUNT(*) AS count
|
|
FROM members m
|
|
WHERE m.type = ?
|
|
", Member_Model::TYPE_APPLICANT)->current()->count;
|
|
}
|
|
|
|
/**
|
|
* Function counts all members.
|
|
*
|
|
* !!!!!! SECURITY WARNING !!!!!!
|
|
* Be careful when you using this method, param $filter_sql is unprotected
|
|
* for SQL injections, security should be made at controller site using
|
|
* Filter_form class.
|
|
* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
|
|
*
|
|
* @param $filter_values
|
|
* @return unknown_type
|
|
*/
|
|
public function count_all_members($filter_sql = "")
|
|
{
|
|
$where = '';
|
|
if ($filter_sql != '')
|
|
$where = "WHERE $filter_sql";
|
|
|
|
return $this->db->query("
|
|
SELECT COUNT(*) AS total
|
|
FROM
|
|
(
|
|
SELECT *
|
|
FROM
|
|
(
|
|
SELECT m.id
|
|
FROM
|
|
(
|
|
SELECT m.id, m.name, m.variable_symbol, m.redirect,
|
|
IF(m.registration = 1, ?, ?) AS registration,
|
|
IFNULL(t.translated_term, e.value) AS type,
|
|
IF(mi.id IS NOT NULL, ?, ?) AS membership_interrupt,
|
|
IFNULL(r.name,?) AS redirection,
|
|
m.organization_identifier, m.address_point_id,
|
|
m.comment, m.entrance_date, m.leaving_date
|
|
FROM members m
|
|
LEFT JOIN enum_types e ON m.type = e.id
|
|
LEFT JOIN translations t ON e.value = t.original_term AND lang = ?
|
|
LEFT JOIN
|
|
(
|
|
SELECT mi.id, mi.member_id FROM membership_interrupts mi
|
|
LEFT JOIN members_fees mf ON mi.members_fee_id = mf.id
|
|
WHERE mf.activation_date <= CURDATE() AND mf.deactivation_date >= CURDATE()
|
|
) mi ON mi.member_id = m.id
|
|
LEFT JOIN
|
|
(
|
|
SELECT ? AS name, 1 AS redirect
|
|
UNION
|
|
SELECT ? AS name, 2 AS redirect
|
|
UNION
|
|
SELECT ? AS name, 4 AS redirect
|
|
UNION
|
|
SELECT ? AS name, 8 AS redirect
|
|
) AS r ON m.redirect & r.redirect
|
|
) AS 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 comments_threads ct ON a.comments_thread_id = ct.id
|
|
LEFT JOIN comments c ON ct.id = c.comments_thread_id
|
|
LEFT JOIN users u ON c.user_id = u.id
|
|
$where
|
|
ORDER BY c.datetime DESC
|
|
) AS q1
|
|
GROUP BY id
|
|
) AS q2
|
|
", array
|
|
(
|
|
url_lang::lang('texts.Yes'),
|
|
url_lang::lang('texts.No'),
|
|
url_lang::lang('texts.Yes'),
|
|
url_lang::lang('texts.No'),
|
|
url_lang::lang('texts.nonei'),
|
|
Config::get('lang'),
|
|
url_lang::lang('texts.Membership interrupt'),
|
|
url_lang::lang('texts.Debtor'),
|
|
url_lang::lang('texts.Payment notice'),
|
|
url_lang::lang('texts.Optional message')
|
|
))->current()->total;
|
|
}
|
|
|
|
/**
|
|
* Function gets member for registration table.
|
|
*
|
|
* @param integer $limit
|
|
* @param integer $limit_results
|
|
* @return unknown_type
|
|
*/
|
|
public function get_all_members_to_registration($limit = 0, $limit_results = 50)
|
|
{
|
|
return $this->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 " . intval($limit) . ", " . intval($limit_results) ."
|
|
");
|
|
}
|
|
|
|
/**
|
|
* Function gets all members to export.
|
|
*
|
|
* @author Jiri Svitak
|
|
* @return unknown_type
|
|
*/
|
|
public function get_all_members_to_export()
|
|
{
|
|
return $this->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.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 = ?
|
|
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 = ?
|
|
) f ON e.value = f.original_term
|
|
", User_Model::$member, Config::get('lang'));
|
|
}
|
|
|
|
/**
|
|
* Function gets all members who have at least one ip address in given subnet.
|
|
*
|
|
* @author Jiri Svitak
|
|
* @param integer $subnet_id
|
|
* @return unknown_type
|
|
*/
|
|
public function get_members_of_subnet($subnet_id)
|
|
{
|
|
return $this->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 integer $id
|
|
* @return unknown_type
|
|
*/
|
|
public function get_member($id)
|
|
{
|
|
return $this->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', 'users.member_id',
|
|
'users.name', 'middle_name', 'surname',
|
|
'pre_title', 'post_title',
|
|
'login', 'users.type', 'birthday', 'leaving_date','users.application_password','users.comment as ucomment',
|
|
'members_traffics.upload', 'members_traffics.download', 'members_traffics.active',
|
|
'SUM(members_traffics_daily.upload) as total_upload',
|
|
'SUM(members_traffics_daily.download) as total_download')
|
|
)->from('members')
|
|
->join('enum_types', 'members.type', 'enum_types.id', 'LEFT')
|
|
->join('users', 'users.member_id', 'members.id', 'LEFT')
|
|
->join('members_traffics', 'members_traffics.member_id', 'members.id', 'LEFT')
|
|
->join('members_traffics_daily', 'members_traffics_daily.member_id', 'members.id', 'LEFT')
|
|
->where('members.id=' . intval($id) . ' AND users.id IS NOT NULL')
|
|
->groupby('members.id')->limit(1)->get();
|
|
}
|
|
|
|
/**
|
|
* 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
|
|
*
|
|
* @param string $phone string containing a phone number
|
|
* @return Mysql_Object first member_id of a member with given phone number
|
|
*/
|
|
public function find_member_id_by_phone($phone)
|
|
{
|
|
$result = $this->db->query("
|
|
SELECT m.id FROM members m
|
|
JOIN users ON m.id = users.member_id
|
|
JOIN users_contacts ON users.id = users_contacts.user_id
|
|
JOIN contacts c ON users_contacts.contact_id = c.id
|
|
WHERE c.type = ? AND c.value = ?
|
|
", array(Contact_Model::TYPE_PHONE, $phone));
|
|
|
|
return ($result && $result->count()) ? $result->current()->id : false;
|
|
}
|
|
|
|
/**
|
|
* Find member by phone
|
|
*
|
|
* @author Tomas Dulik, Ondřej Fibich
|
|
* @return unknown_type
|
|
*/
|
|
public function find_member_by_phone($phone)
|
|
{
|
|
$result = $this->db->query("
|
|
SELECT m.* FROM members m
|
|
JOIN users ON m.id = users.member_id
|
|
JOIN users_contacts ON users.id = users_contacts.user_id
|
|
JOIN contacts c ON users_contacts.contact_id = c.id
|
|
WHERE c.type = ? AND c.value = ?
|
|
", array(Contact_Model::TYPE_PHONE, $phone));
|
|
|
|
return ($result && $result->count()) ? $result->current() : FALSE;
|
|
}
|
|
|
|
/**
|
|
* 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.
|
|
*
|
|
* @author Tomas Dulik, using http://us3.php.net/manual/en/function.crc32.php#86628
|
|
* @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;
|
|
}
|
|
|
|
/**
|
|
* This function can be used for generating variable symbols from member id
|
|
*
|
|
* @author Tomas Dulik
|
|
* @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", $this->crc16($member_id));
|
|
}
|
|
/**
|
|
* This function can be used for checking the validity of payment variable symbols
|
|
* and finding the related member.
|
|
* If the variable symbol was generated from member id using crc16 function,
|
|
* then it is easy to check if the variable symbol is OK and extract the member id from it.
|
|
*
|
|
* @author Tomas Dulik
|
|
* @param $vs - string containing variable symbol (concatenation of member id and its crc16)
|
|
* @return object containing the member
|
|
*/
|
|
public function get_member_by_crc_id($vs)
|
|
{
|
|
if (($vs_len = strlen($vs)) > 5)
|
|
{
|
|
$member_id = (int) substr($vs, 0, $vs_len - 5);
|
|
$crc = $this->crc16($member_id);
|
|
$vs_crc = (int) substr($vs, $vs_len - 5, 5);
|
|
if ($crc == $vs_crc)
|
|
$member = $this->find($member_id);
|
|
}
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* Function updates lock status.
|
|
*
|
|
* @author Roman Sevcik
|
|
*/
|
|
public function update_lock_status()
|
|
{
|
|
$this->db->query("UPDATE members SET locked = 0");
|
|
$this->db->query("UPDATE members SET locked = 1 where type = 15");
|
|
$this->db->query("
|
|
UPDATE members m,
|
|
(
|
|
SELECT m.id as mid
|
|
FROM members m
|
|
JOIN membership_interrupts mi ON mi.member_id = m.id
|
|
JOIN members_fees mf ON mi.members_fee_id = mf.id
|
|
WHERE mf.activation_date <= CURDATE() AND CURDATE() <= mf.deactivation_date
|
|
) mi
|
|
SET m.locked = 1
|
|
WHERE m.id = mi.mid");
|
|
}
|
|
|
|
/**
|
|
* Returns count of all non-former members without membership interrupt in that time and without set-up qos rate
|
|
*
|
|
* @author Michal Kliment
|
|
* @return integer
|
|
*/
|
|
public function count_all_members_to_ulogd()
|
|
{
|
|
$result = $this->db->query("
|
|
SELECT COUNT(m.id) AS total_members_count
|
|
FROM members m
|
|
WHERE m.type <> 15 AND (m.qos_rate IS NULL OR m.qos_rate = 0) AND m.id NOT IN
|
|
(
|
|
SELECT mi.member_id
|
|
FROM membership_interrupts mi
|
|
JOIN members_fees mf ON mi.members_fee_id = mf.id
|
|
WHERE mf.activation_date <= CURDATE() AND CURDATE() <= mf.deactivation_date
|
|
)
|
|
");
|
|
|
|
return ($result && $result->current()) ? $result->current()->total_members_count : 0;
|
|
}
|
|
|
|
/**
|
|
* Tries to find member by ip address
|
|
*
|
|
* @author Michal Kliment
|
|
* @param string $ip_address
|
|
* @return MySQL Result object
|
|
*/
|
|
public function get_member_by_ip_address($ip_address)
|
|
{
|
|
$result = $this->db->query("
|
|
SELECT m.*, q.login FROM
|
|
(
|
|
SELECT IFNULL(
|
|
(
|
|
SELECT m.id FROM
|
|
(
|
|
SELECT IFNULL(i1.device_id,i2.device_id) AS device_id
|
|
FROM ip_addresses ip
|
|
LEFT JOIN ifaces i1 ON ip.iface_id = i1.id
|
|
LEFT JOIN vlan_ifaces vi ON ip.vlan_iface_id = vi.id
|
|
LEFT JOIN ifaces i2 ON vi.iface_id = i2.id
|
|
WHERE ip.ip_address = ?
|
|
) AS q
|
|
JOIN devices d ON q.device_id = d.id
|
|
JOIN users u ON d.user_id = u.id
|
|
JOIN members m ON u.member_id = m.id
|
|
),
|
|
(
|
|
SELECT m.id
|
|
FROM subnets s
|
|
JOIN subnets_owners mo ON s.id = mo.subnet_id
|
|
JOIN members m ON mo.member_id = m.id
|
|
WHERE inet_aton(?) & inet_aton(netmask) = inet_aton(network_address)
|
|
)
|
|
) AS member_id,
|
|
IFNULL(
|
|
(
|
|
SELECT u.login FROM
|
|
(
|
|
SELECT IFNULL(i1.device_id,i2.device_id) AS device_id
|
|
FROM ip_addresses ip
|
|
LEFT JOIN ifaces i1 ON ip.iface_id = i1.id
|
|
LEFT JOIN vlan_ifaces vi ON ip.vlan_iface_id = vi.id
|
|
LEFT JOIN ifaces i2 ON vi.iface_id = i2.id
|
|
WHERE ip.ip_address = ?
|
|
) AS q
|
|
JOIN devices d ON q.device_id = d.id
|
|
JOIN users u ON d.user_id = u.id
|
|
),
|
|
(
|
|
SELECT u.login
|
|
FROM subnets s
|
|
JOIN subnets_owners mo ON s.id = mo.subnet_id
|
|
JOIN members m ON mo.member_id = m.id
|
|
JOIN users u ON m.id = u.member_id AND u.type = 1
|
|
WHERE inet_aton(?) & inet_aton(netmask) = inet_aton(network_address)
|
|
)
|
|
) AS login
|
|
) AS q
|
|
JOIN members m ON q.member_id = m.id
|
|
", array ($ip_address,$ip_address,$ip_address,$ip_address));
|
|
|
|
return ($result && $result->count()) ? $result->current() : null;
|
|
}
|
|
|
|
/**
|
|
* Returns all members to dropdown
|
|
*
|
|
* @author Michal Kliment
|
|
* @return MySQL Iterator object
|
|
*/
|
|
public function get_all_members_to_dropdown ()
|
|
{
|
|
return $this->db->query("
|
|
SELECT m.id, CONCAT(u.surname,' ',u.name, ' (ID ',m.id,')') AS name FROM members m
|
|
JOIN users u ON u.member_id = m.id AND u.type = ?
|
|
ORDER BY name
|
|
", User_Model::$member);
|
|
}
|
|
|
|
/**
|
|
* Returns doubleentry account of member by given account attribute id
|
|
*
|
|
* @author Michal Kliment
|
|
* @param integer $account_attribute_id
|
|
* @return MySQL Result object
|
|
*/
|
|
public function get_doubleentry_account ($account_attribute_id)
|
|
{
|
|
if ($this->id)
|
|
{
|
|
return $this->db->query("
|
|
SELECT * FROM accounts a
|
|
WHERE account_attribute_id = ? AND member_id = ?
|
|
", array($account_attribute_id, $this->id))->current();
|
|
}
|
|
|
|
return false;
|
|
}
|
|
|
|
/**
|
|
* Returns all members as array
|
|
*
|
|
* @author Michal Kliment
|
|
* @return array
|
|
*/
|
|
public function get_all_as_array()
|
|
{
|
|
$members = $this->orderby('name')->find_all();
|
|
|
|
$arr_members = array();
|
|
foreach ($members as $member)
|
|
$arr_members[$member->id] = $member->name;
|
|
|
|
return $arr_members;
|
|
}
|
|
|
|
/**
|
|
* Returns login of member
|
|
*
|
|
* @author Michal Kliment
|
|
* @return string
|
|
*/
|
|
public function get_login()
|
|
{
|
|
if ($this->id)
|
|
{
|
|
return $this->db->query("
|
|
SELECT u.login
|
|
FROM users u
|
|
WHERE u.member_id = ? AND u.type = ?
|
|
", array($this->id, User_Model::$member))->current()->login;
|
|
}
|
|
|
|
return false;
|
|
}
|
|
|
|
/**
|
|
* Returns all members belongs to segment
|
|
*
|
|
* @author Michal Kliment
|
|
* @param integer $segment_id
|
|
* @param boolean $with_assoc with association or without association
|
|
* @return MySQL iterator object
|
|
*/
|
|
public function get_all_by_segment($segment_id, $with_assoc = TRUE)
|
|
{
|
|
$where = (!$with_assoc) ? ' AND m.id <> 1' : '';
|
|
|
|
return $this->db->query("
|
|
SELECT m.id FROM members m
|
|
JOIN users u ON u.member_id = m.id
|
|
JOIN devices d ON d.user_id = u.id
|
|
JOIN ifaces i ON i.device_id = d.id
|
|
WHERE i.segment_id = ? $where
|
|
GROUP BY m.id
|
|
", array($segment_id));
|
|
}
|
|
|
|
public function get_all_entrance_and_leaving_dates ($filter_sql = '')
|
|
{
|
|
$where = '';
|
|
if ($filter_sql != '')
|
|
$where = "WHERE $filter_sql";
|
|
|
|
return $this->db->query("
|
|
SELECT date AS date, SUM(increase) AS increase, SUM(decrease) AS decrease
|
|
FROM
|
|
(
|
|
SELECT SUBSTR(date,1,7) AS date, SUM(increase) AS increase, SUM(decrease) AS decrease
|
|
FROM
|
|
(
|
|
SELECT entrance_date AS date, COUNT(entrance_date) AS increase, 0 AS decrease
|
|
FROM members m
|
|
GROUP BY entrance_date
|
|
UNION
|
|
SELECT leaving_date AS date, 0 AS increase, COUNT(leaving_date) AS decrease
|
|
FROM members m
|
|
WHERE m.leaving_date IS NOT NULL AND m.leaving_date <> '' AND m.leaving_date <> '0000-00-00'
|
|
GROUP BY leaving_date
|
|
) m
|
|
$where
|
|
GROUP BY date
|
|
) m
|
|
GROUP BY date
|
|
ORDER BY date
|
|
");
|
|
}
|
|
|
|
/**
|
|
* Deletes members accounts
|
|
*
|
|
* @param integer $member_id
|
|
*/
|
|
public function delete_accounts($member_id)
|
|
{
|
|
$this->db->query("
|
|
DELETE FROM accounts WHERE member_id = ?
|
|
", $member_id);
|
|
}
|
|
|
|
/**
|
|
* Gets members whose at least one ip address is set as whitelisted.
|
|
* @author Jiri Svitak
|
|
*/
|
|
public function get_whitelisted_members()
|
|
{
|
|
return $this->db->query("
|
|
SELECT m.id, IFNULL(f.translated_term, e.value) AS type, m.name, a.balance,
|
|
a.id AS aid, a.comments_thread_id AS a_comments_thread_id,
|
|
ip.whitelisted,
|
|
COUNT(ip.id) AS ip_count
|
|
FROM members m
|
|
JOIN users u ON u.member_id = m.id
|
|
JOIN devices d ON d.user_id = u.id
|
|
JOIN ifaces i ON i.device_id = d.id
|
|
JOIN ip_addresses ip ON ip.iface_id = i.id
|
|
LEFT JOIN accounts a ON a.member_id = m.id AND m.id <> 1
|
|
LEFT JOIN comments_threads ct ON a.comments_thread_id = ct.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
|
|
WHERE ip.whitelisted > 0
|
|
GROUP BY m.id");
|
|
}
|
|
|
|
/**
|
|
* Counts members whose at least one ip address is set as whitelisted.
|
|
* @author Jiri Svitak
|
|
*/
|
|
public function count_whitelisted_members()
|
|
{
|
|
return $this->db->query("
|
|
SELECT COUNT(*) AS total
|
|
FROM members m
|
|
JOIN users u ON u.member_id = m.id
|
|
JOIN devices d ON d.user_id = u.id
|
|
JOIN ifaces i ON i.device_id = d.id
|
|
JOIN ip_addresses ip ON ip.iface_id = i.id
|
|
WHERE ip.whitelisted > 0
|
|
GROUP BY m.id")->current()->total;
|
|
}
|
|
}
|