FreenetIS

padding-bottom:5px;

padding-left:45px;

'list of connection requests' => 'Seznam žádostí o připojení',

'clean_temp_whitelist' => 'Po importu vymaže členy z dočasné bílé listiny',

'ignore_whitelist' => 'Umožňuje této zprávě, je-li aktivována, ignorovat bílou listinu. Určeno pro velmi speciální případy, například pokud je připojen městský úřad, který je na trvalé bílé listině, aby nebyl obtěžován a i přesto chceme jeho počítače přesměrovat kvůli informaci o výpadku.',

'set_whitelist_to_member' => 'Trvalá bílá listina je seznam takových IP adres, pro které neplatí přesměrování. Jde například o důležitou partnerskou instituci zapojenou do sítě, například městský úřad apod. Všechna systémová přesměrování tedy nemohou ve výchozím stavu obtěžovat počítače s IP adresou na bílé listině. Lze ovšem vytvořit takové zprávy, které budou ignorovat tuto listinu, např. informování o nadcházejícím výpadku. Pro dočasnou bílou listinu platí to samé, ale IP adresy v ní jsou po určeném čase z této listiny mazány. Např. uživatel byl přesměrován kvůli neplacení a chceme mu umožnit krátkodobý přístup na internet do internetového bankovnictví.',

'whitelist' => 'IP adresám, které jsou umístěny na bílé listině, je sice možné aktivovat přesměrování, ale nebudou odeslány na centrální router. Tedy nebudou přesměrovány, dokud nebudou odebrány z bílé listiny. Dočasná bílá listina se od trvalé liší v tom, že je pravidelně promazávána.',

'clean_temp_whitelist' => 'Removes members from temporary whilelist after import',

'set_whitelist_to_member' => 'Permanent white list is a list of such IP addresses for which it does not redirect. This is such an important partner institution involved in the network, such as municipal authorities, etc. Any diversion system is not in default bother computer with an IP address on the white list. But you can create a message that will ignore this list, such as informing of impending failure. For temporary whitelist does the same, but IP addresses in it after a specified time from the instrument clean. Eg. user was redirected due to non-payment and we want him to allow short-term Internet access to internet banking.',

'whitelist' => 'IP addresses, which are located on the white list, you may be able to activate the redirect, but not sent to the central router. Will not be diverted until removed from the white list. Temporary White List from the persistent differ in that it is regularly lubricated.',

case Message_Model::UNALLOWED_CONNECTING_PLACE_MESSAGE:

if (!$item->allowed && (!$item->interrupt || ($name == 'redirection' && $item->interrupt)))

$selected = Notifications_Controller::ACTIVATE;

break;

case Ip_address_Model::PERNAMENT_WHITELIST:

* @property bool $service

* @property integer $whitelisted

const PERNAMENT_WHITELIST = 1;

self::PERNAMENT_WHITELIST => 'Permanent whitelist',

* Gets translated whitelist types

*

* @param integer $white_list_type

* @return string

*/

public function get_whitelist_type($white_list_type = NULL)

{

if (empty($white_list_type) && $this->id)

{

$white_list_type = $this->whitelisted;

}

if (array_key_exists($white_list_type, self::$whitelist_types))

{

return __(self::$whitelist_types[$white_list_type]);

}

return __(self::$whitelist_types[self::NO_WHITELIST]);

}

/**

d.id AS device_id, d.name AS device_name

d.id AS device_id, d.name AS device_name

WHERE mip.ip_address_id IS NULL OR

(

whitelisted > 0 AND ip_address NOT IN

(

SELECT DISTINCT ip.ip_address

FROM ip_addresses ip

JOIN messages_ip_addresses mip ON mip.ip_address_id = ip.id

JOIN messages m ON m.id = mip.message_id

WHERE m.ignore_whitelist = 1

)

ORDER BY INET_ATON(ip_address)

SELECT ip_address

WHERE IFNULL(ip.whitelisted,0) = 0 OR m.ignore_whitelist = 1

GROUP BY ip.id

ORDER BY INET_ATON(ip_address)

* Gets all IDs of IP addresses of member who have currently interrupted

* membership. These IP addresses are redirected.

*

* @author Jiri Svitak

* @return Mysql_Result

*/

public function get_ip_addresses_with_interrupted_membership()

{

return $this->db->query("

SELECT ip.id

FROM

(

SELECT ip.id, ip.ip_address, ip.whitelisted,

s.name AS subnet_name,

IFNULL(u.member_id, ip.member_id) AS member_id

FROM ip_addresses ip

LEFT JOIN ifaces i ON ip.iface_id = i.id

LEFT JOIN subnets s ON s.id = ip.subnet_id

LEFT JOIN devices d ON d.id = i.device_id

LEFT JOIN users u ON u.id = d.user_id

) ip

JOIN members m ON m.id = ip.member_id

JOIN membership_interrupts mi ON mi.member_id = m.id

JOIN members_fees mf ON mi.members_fee_id = mf.id

JOIN fees f ON f.id = mf.fee_id

JOIN accounts a ON a.member_id = m.id

WHERE mf.activation_date <= CURDATE() AND f.special_type_id = ? AND

CURDATE() <= mf.deactivation_date

", Fee_Model::MEMBERSHIP_INTERRUPT);

}

/**

/**

* Gets all IP addresses of members who have credit negative credit status.

*

* @author Jiri Svitak

* @param duble $debtor_boundary

* @return Mysql_Result

*/

public function get_ip_addresses_of_debtors($debtor_boundary)

{

return $this->db->query("

SELECT ip.id, ip.ip_address, ip.whitelisted, subnet_name,

m.name AS member_name, a.balance,

(

SELECT GROUP_CONCAT(vs.variable_symbol) AS variable_symbol

FROM variable_symbols vs

LEFT JOIN accounts a ON a.id = vs.account_id

WHERE a.member_id = m.id

) AS variable_symbol

FROM

(

SELECT ip.id, ip.ip_address, ip.whitelisted, s.name AS subnet_name,

IFNULL(u.member_id, ip.member_id) AS member_id

FROM ip_addresses ip

LEFT JOIN ifaces i ON ip.iface_id = i.id

JOIN subnets s ON s.id = ip.subnet_id

LEFT JOIN devices d ON d.id = i.device_id

LEFT JOIN users u ON u.id = d.user_id

) ip

JOIN members m ON m.id = ip.member_id

JOIN accounts a ON a.member_id = m.id AND m.id <> ?

WHERE a.balance < ?

AND DATEDIFF(CURDATE(), m.entrance_date) >= ?

AND (ip.whitelisted IS NULL OR ip.whitelisted = 0)

", array

(

Member_Model::ASSOCIATION, $debtor_boundary,

Settings::get('initial_debtor_immunity')

));

}

/**

* Gets all IP addresses of members who have low credit and should pay

* in short time.

*

* @author Jiri Svitak

* @param double $payment_notice_boundary

* @param double $debtor_boundary

* @return Mysql_Result

*/

public function get_ip_addresses_of_almostdebtors($payment_notice_boundary, $debtor_boundary)

{

return $this->db->query("

SELECT ip.id, ip.ip_address, ip.whitelisted, subnet_name,

m.name AS member_name, a.balance,

(

SELECT GROUP_CONCAT(vs.variable_symbol) AS variable_symbol

FROM variable_symbols vs

LEFT JOIN accounts a ON a.id = vs.account_id

WHERE a.member_id = m.id

) AS variable_symbol

FROM

(

SELECT ip.id, ip.ip_address, ip.whitelisted,

s.name AS subnet_name,

IFNULL(u.member_id, ip.member_id) AS member_id

FROM ip_addresses ip

LEFT JOIN ifaces i ON ip.iface_id = i.id

JOIN subnets s ON s.id = ip.subnet_id

LEFT JOIN devices d ON d.id = i.device_id

LEFT JOIN users u ON u.id = d.user_id

) ip

JOIN members m ON m.id = ip.member_id

JOIN accounts a ON a.member_id = m.id AND m.id <> ?

WHERE (

DATEDIFF(CURDATE(), m.entrance_date) >= ? AND

a.balance >= ? OR DATEDIFF(CURDATE(), m.entrance_date) < ? AND

DATEDIFF(CURDATE(), m.entrance_date) >= ?

)

AND a.balance < ?

AND (ip.whitelisted IS NULL OR ip.whitelisted = 0)

", array

(

Member_Model::ASSOCIATION, Settings::get('initial_debtor_immunity'),

$debtor_boundary, Settings::get('initial_debtor_immunity'),

Settings::get('initial_immunity'), $payment_notice_boundary

));

}

SELECT ip.id AS ip_address_id, ip.ip_address, ip.whitelisted,

m.id AS message_id, m.name AS message, m.type, ? AS member_id,

mip.datetime AS active_redir_datetime

LEFT JOIN messages m ON m.id = mip.message_id

", $member_id, $member_id, $member_id)->current()->total;

/**

* Removes all IP addresses from temporary whitelist. Used when bank statement

* is imported, then all whitelisted whould have payed their fees, so they are

* no longer protected from redirection.

*

* @author Jiri Svitak

*/

public function clean_temporary_whitelist()

{

$this->db->query("

UPDATE ip_addresses

SET whitelisted = ?

WHERE whitelisted = ?

", self::NO_WHITELIST, self::TEMPORARY_WHITELIST);

}

* Sets whitelist

*

* @param ineteger $whitelist

* @param integer $ip_address_id

* @return boolean

*/

public function set_whitelist($whitelist, $ip_address_id = NULL)

{

if (!$ip_address_id && isset($this))

$ip_address_id = $this->id;

return $this->db->query("

UPDATE ip_addresses ip

SET whitelisted = ?

WHERE id = ?

", array($whitelist, $ip_address_id));

}

/**

const USER_MESSAGE = 0;

const CONTACT_INFORMATION = 1;

$type == self::PAYMENT_NOTICE_MESSAGE ||

$type == self::UNALLOWED_CONNECTING_PLACE_MESSAGE

/** Contact is not whitelisted */

const NO_WHITELIST = 0;

/** Contact is under pernament whitelisted */

const PERMANENT_WHITELIST = 1;

/** Contact is under temporary whitelisted */

const TEMPORARY_WHITELIST = 2;

/**

* Sets whitelist flag for member's conntact

*

* @author Michal Kliment

* @param integer $whitelist

* @param integer $member_id

* @param integer $type

*/

public function set_whitelist_by_member_and_type($whitelist, $member_id, $type)

{

$this->db->query("

UPDATE users_contacts uc, contacts c, users u

SET uc.whitelisted = ?

WHERE

uc.contact_id = c.id

AND c.type = ?

AND uc.user_id = u.id

AND u.member_id = ?

", $whitelist, $type, $member_id);

}

* Cleans temporary whitelist for contacts

*

* @author Michal Kliment

*/

public function clean_temporary_whitelist()

{

$this->db->query("

UPDATE users_contacts uc

SET uc.whitelisted = ?

WHERE uc.whitelisted = ?

", self::NO_WHITELIST, self::TEMPORARY_WHITELIST);

}

/**

* Returns all contacts of debtors by type

*

* @author Michal Kliment

* @param double $debtor_boundary

* @param integer $type

* @return Mysql_Result

*/

public function get_contacts_of_debtors_by_type($debtor_boundary, $type)

{

return $this->db->query("

SELECT

c.value, a.balance, m.id AS member_id, m.name AS member_name,

(

SELECT GROUP_CONCAT(vs.variable_symbol) AS variable_symbol

FROM variable_symbols vs

WHERE vs.account_id = a.id

) AS variable_symbol, u.login, cou.country_code

FROM members m

JOIN accounts a ON a.member_id = m.id AND m.id <> 1

JOIN users u ON u.member_id = m.id

JOIN users_contacts uc ON uc.user_id = u.id

JOIN contacts c ON uc.contact_id = c.id AND c.type = ?

LEFT JOIN contacts_countries cc ON cc.contact_id = c.id

LEFT JOIN countries cou ON cou.id = cc.country_id

WHERE uc.whitelisted = ? AND m.type <> ? AND m.id NOT IN

(

SELECT m.id

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 mf.deactivation_date >= CURDATE()

) AND DATEDIFF(CURDATE(), m.entrance_date) >= ".intval(Settings::get('initial_debtor_immunity'))."

AND a.balance < ".intval($debtor_boundary)."

", $type, self::NO_WHITELIST, Member_Model::TYPE_FORMER);

}

/**

* Returns all contacts of almost-debtors

*

* @author Michal Kliment

* @param double $payment_notice_boundary

* @param double $debtor_boundary

* @param integer $type

* @return Mysql_Result

*/

public function get_contacts_of_almostdebtors_by_type(

$payment_notice_boundary, $debtor_boundary, $type)

{

return $this->db->query("

SELECT

c.value, a.balance, m.id AS member_id, m.name AS member_name,

(

SELECT GROUP_CONCAT(vs.variable_symbol) AS variable_symbol

FROM variable_symbols vs

WHERE vs.account_id = a.id

) AS variable_symbol, u.login, cou.country_code

FROM members m

JOIN accounts a ON a.member_id = m.id AND m.id <> 1

JOIN users u ON u.member_id = m.id

JOIN users_contacts uc ON uc.user_id = u.id

JOIN contacts c ON uc.contact_id = c.id AND c.type = ?

LEFT JOIN contacts_countries cc ON cc.contact_id = c.id

LEFT JOIN countries cou ON cou.id = cc.country_id

WHERE uc.whitelisted = ? AND m.type <> ? AND m.id NOT IN

(

SELECT m.id

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 mf.deactivation_date >= CURDATE()

)

AND a.balance < ".intval($payment_notice_boundary)."

AND

(

DATEDIFF(CURDATE(), m.entrance_date) >= ".intval(Settings::get('initial_debtor_immunity'))."

AND a.balance >= ".intval($debtor_boundary)."

OR DATEDIFF(CURDATE(), m.entrance_date) < ".intval(Settings::get('initial_debtor_immunity'))."

AND DATEDIFF(CURDATE(), m.entrance_date) >= ".intval(Settings::get('initial_immunity'))."

)

",

array

(

$type,

self::NO_WHITELIST,

Member_Model::TYPE_FORMER

));

}

/**

if ($ignore_whitelisted)

$whitelisted = "";

else

$whitelisted = "AND uc.whitelisted = 0";

SELECT GROUP_CONCAT(vs.variable_symbol) AS variable_symbol

FROM variable_symbols vs

WHERE vs.account_id = a.id

JOIN users_contacts uc ON uc.user_id = u.id $whitelisted

WHERE m.id = ? AND m.type <> ? AND m.id NOT in

SELECT m.id

FROM members m

JOIN membership_interrupts mi ON mi.member_id = m.id

WHERE mf.activation_date <= CURDATE() AND mf.deactivation_date >= CURDATE()

", $type, $member_id, Member_Model::TYPE_FORMER);

if ($ignore_whitelisted)

$whitelisted = "";

else

$whitelisted = "AND uc.whitelisted = 0";

SELECT GROUP_CONCAT(vs.variable_symbol) AS variable_symbol

FROM variable_symbols vs

WHERE vs.account_id = a.id

JOIN users_contacts uc ON uc.contact_id = c.id $whitelisted

WHERE m.type <> ? AND c.type = ? AND m.id NOT in

SELECT m.id

FROM members m

JOIN membership_interrupts mi ON mi.member_id = m.id

WHERE mf.activation_date <= CURDATE() AND mf.deactivation_date >= CURDATE()

SELECT IFNULL(ip.whitelisted,0) AS whitelisted, ip.member_id

FROM

(

SELECT ip.whitelisted, IFNULL(u.member_id, ip.member_id) AS member_id

FROM ip_addresses ip

LEFT JOIN ifaces i ON ip.iface_id = i.id

LEFT JOIN devices d ON i.device_id = d.id

LEFT JOIN users u ON d.user_id = u.id

ORDER BY ip.whitelisted DESC

) ip

GROUP BY member_id

IFNULL(u.member_id,ms.member_id) AS member_id, whitelisted

SELECT ms.*, i.device_id, ip.member_id, ip.whitelisted

SELECT IFNULL(ip.whitelisted,0) AS whitelisted, ip.member_id

FROM

(

SELECT ip.whitelisted, IFNULL(u.member_id,ip.member_id) AS member_id

FROM

(

SELECT ip.whitelisted, i.device_id, ip.member_id

FROM ip_addresses ip

LEFT JOIN ifaces i ON ip.iface_id = i.id

) ip

LEFT JOIN devices d ON ip.device_id = d.id

LEFT JOIN users u ON d.user_id = u.id

ORDER BY ip.whitelisted DESC

) ip

GROUP BY member_id

die($this->db->last_query());

SELECT *

FROM

(

SELECT

IFNULL(ip.whitelisted,0) AS whitelisted,

IFNULL(ip.member_id,u.member_id) AS member_id

FROM ip_addresses ip

LEFT JOIN ifaces i ON ip.iface_id = i.id

LEFT JOIN devices d ON i.device_id = d.id

LEFT JOIN users u ON d.user_id = u.id

UNION

SELECT IFNULL(uc.whitelisted,0) AS whitelisted, u.member_id

FROM users u

LEFT JOIN users_contacts uc ON uc.user_id = u.id

ORDER BY whitelisted DESC

) w

GROUP BY member_id

SELECT *

FROM

(

SELECT IFNULL(ip.whitelisted,0) AS whitelisted, IFNULL(ip.member_id,u.member_id) AS member_id

FROM ip_addresses ip

LEFT JOIN ifaces i ON ip.iface_id = i.id

LEFT JOIN devices d ON i.device_id = d.id

LEFT JOIN users u ON d.user_id = u.id

UNION

SELECT IFNULL(uc.whitelisted,0) AS whitelisted, u.member_id

FROM users u

LEFT JOIN users_contacts uc ON uc.user_id = u.id

ORDER BY whitelisted DESC

) w

GROUP BY member_id

SELECT *

FROM

(

SELECT IFNULL(ip.whitelisted,0) AS whitelisted, IFNULL(ip.member_id,u.member_id) AS member_id

FROM ip_addresses ip

LEFT JOIN ifaces i ON ip.iface_id = i.id

LEFT JOIN devices d ON i.device_id = d.id

LEFT JOIN users u ON d.user_id = u.id

UNION

SELECT IFNULL(uc.whitelisted,0) AS whitelisted, u.member_id

FROM users u

LEFT JOIN users_contacts uc ON uc.user_id = u.id

ORDER BY whitelisted DESC

) w

GROUP BY member_id

/**

* Returns all members belongs to link

*

* @author Michal Kliment

* @param integer $link_id

* @param boolean $with_assoc with association or without association

* @return MySQL iterator object

*/

public function get_all_by_segment($link_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.link_id = ? $where

GROUP BY m.id

", array($link_id));

}

/**

* Gets members whose at least one ip address is set as whitelisted.

* @author Jiri Svitak

* @return Mysql_Result

*/

public function get_whitelisted_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';

}

return $this->db->query("

SELECT

m.*, COUNT(m.id) AS items_count,

GROUP_CONCAT(ip_address ORDER BY INET_ATON(ip_address) SEPARATOR ', \n')

AS items_count_title, a_comment

FROM

(

SELECT

m.id, IFNULL(f.translated_term, e.value) AS type,

m.name, m.name AS member_name, a.balance,

a.id AS aid, a.comments_thread_id AS a_comments_thread_id,

ip.whitelisted, ip.ip_address, ip.id AS ip_address_id,

a_comment

FROM

(

SELECT ip.id, ip.whitelisted, ip.ip_address,

IFNULL(u.member_id, ip.member_id) AS member_id

FROM ip_addresses ip

LEFT JOIN ifaces i ON ip.iface_id = i.id

LEFT JOIN devices d ON i.device_id = d.id

LEFT JOIN users u ON d.user_id = u.id

WHERE ip.whitelisted > 0

) ip

JOIN members m ON ip.member_id = m.id

LEFT JOIN accounts a ON a.member_id = m.id AND m.id <> 1

LEFT JOIN

(

SELECT c.comments_thread_id,

GROUP_CONCAT(CONCAT(u.surname,' ',u.name,' (',SUBSTRING(c.datetime,1,10),'):\n',c.text)

ORDER BY datetime DESC SEPARATOR ', \n\n') AS a_comment

FROM comments c

JOIN users u ON c.user_id = u.id

GROUP BY c.comments_thread_id

) c ON a.comments_thread_id = c.comments_thread_id

LEFT JOIN comments_threads ct ON a.comments_thread_id = ct.id

LEFT JOIN enum_types e ON m.type = e.id

LEFT JOIN translations f ON e.value = f.original_term AND lang = ?

WHERE ip.whitelisted > 0

) m

$where

GROUP BY m.id

ORDER BY " . $this->db->escape_column($order_by) . " $order_by_direction

LIMIT " . intval($limit_from) . ", " . intval($limit_results) . "

", Config::get('lang'));

}

/**

* Counts members whose at least one ip address is set as whitelisted.

* @author Jiri Svitak

* @return integer

*/

public function count_whitelisted_members($filter_sql = '')

{

$where = "";

if ($filter_sql)

{

$where = "WHERE $filter_sql";

}

return $this->db->query("

SELECT COUNT(*) AS total FROM

(

SELECT m.id FROM

(

SELECT

m.id, IFNULL(f.translated_term, e.value) AS type,

m.name AS member_name, ip.whitelisted, a.balance

FROM

(

SELECT ip.id, ip.whitelisted, ip.ip_address,

IFNULL(u.member_id, ip.member_id) AS member_id

FROM ip_addresses ip

LEFT JOIN ifaces i ON ip.iface_id = i.id

LEFT JOIN devices d ON i.device_id = d.id

LEFT JOIN users u ON d.user_id = u.id

WHERE ip.whitelisted > 0

) ip

JOIN members m ON ip.member_id = m.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 translations f ON e.value = f.original_term AND lang = ?

WHERE ip.whitelisted > 0

) m

$where

GROUP BY m.id

) m

", Config::get('lang'))->current()->total;

}

$message->type == Message_Model::PAYMENT_NOTICE_MESSAGE ||

$message->type == Message_Model::UNALLOWED_CONNECTING_PLACE_MESSAGE)

$message->type == Message_Model::PAYMENT_NOTICE_MESSAGE ||

$message->type == Message_Model::UNALLOWED_CONNECTING_PLACE_MESSAGE)

case Message_Model::UNALLOWED_CONNECTING_PLACE_MESSAGE:

$order_by = 'whitelisted DESC, allowed ASC';

break;

if ($message->type == Message_Model::UNALLOWED_CONNECTING_PLACE_MESSAGE ||

$message->type == Message_Model::USER_MESSAGE)