FreenetIS

const MEMBERS_QOS_ACTIVE = 3;

/**

* Squid configuration for needs of PVFREE

*

* @author Ondrej Fibich

* @todo THIS MUST BE REMOVED BEFORE RELEASING OF 1.1.

*/

public function squid()

{

$db = Database::instance();

$mids = array();

$TERMINPLATBA = 14;

$TERMINZKOUSKA = 21;

$HRANICEDLUZNIKA = -200;

echo "# konfigurace squidu\n";

echo "# generovano dne " . date('d.m.Y H:i:s') . "\n";

echo "# freenetis " . Version::get_version() . "\n";

echo "\n######## anonym ################################\n";

echo "\n######## limit ################################\n";

echo "\n######## noemail ################################\n";

// noemail

$noemails = $db->query("

SELECT DISTINCT m.id, u.login, ip.ip_address

FROM members m

JOIN users u ON u.member_id = m.id AND u.type = ?

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

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

JOIN ip_addresses ip ON ip.iface_id = i.id OR ip.member_id = m.id

WHERE u.id NOT IN (

SELECT u2.id

FROM users u2

JOIN users_contacts uc ON uc.user_id = u2.id

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

) AND m.id <> 1

ORDER BY m.id, ip.id

", User_Model::MAIN_USER, Contact_Model::TYPE_EMAIL);

$current = NULL;

foreach ($noemails as $noemail)

{

$mids[] = $noemail->id;

if ($noemail->id != $current)

{

echo "# $noemail->id $noemail->login\n";

$current = $noemail->id;

}

echo "acl noemail src $noemail->ip_address\n";

}

echo "\n######## sosak ################################\n";

echo "\n######## zavirovany ################################\n";

echo "\n######## NECLEN ################################\n";

$nonmembers = $db->query("

SELECT DISTINCT m.id, u.login, ip.ip_address

FROM members m

JOIN users u ON u.member_id = m.id AND u.type = ?

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

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

JOIN ip_addresses ip ON ip.iface_id = i.id OR ip.member_id = m.id

LEFT JOIN (

SELECT m.id AS member_id, COUNT(*) AS count

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 NOW() BETWEEN mf.activation_date AND mf.deactivation_date

GROUP BY m.id

) r ON r.member_id = m.id

WHERE m.id <> 1 AND ((

m.type = ? AND m.registration = 0 AND

DATEDIFF(NOW(), m.applicant_connected_from) > ?

) OR (r.count IS NOT NULL AND r.count > 0) OR (m.type = ?))

ORDER BY m.id, ip.id

", array

(

User_Model::MAIN_USER,

Member_Model::TYPE_APPLICANT,

$TERMINZKOUSKA,

Member_Model::TYPE_FORMER

));

$current = NULL;

foreach ($nonmembers as $nonmember)

{

$mids[] = $nonmember->id;

if ($nonmember->id != $current)

{

echo "# $nonmember->id $nonmember->login\n";

$current = $nonmember->id;

}

echo "acl neclen src $nonmember->ip_address\n";

}

echo "\n######## NEPLATIC ################################\n";

$debtors = $db->query("

SELECT DISTINCT m.id, u.login, ip.ip_address

FROM members m

JOIN accounts a ON a.member_id = m.id

JOIN users u ON u.member_id = m.id AND u.type = ?

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

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

JOIN ip_addresses ip ON ip.iface_id = i.id OR ip.member_id = m.id

WHERE m.id <> 1 AND m.type <> ? AND m.type <> ?

AND (a.balance < ? OR (

a.balance = ? AND

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

) AND m.id NOT IN(" . implode(',', $mids) . ")

ORDER BY m.id, ip.id

", array

(

User_Model::MAIN_USER,

Member_Model::TYPE_APPLICANT,

Member_Model::TYPE_FORMER,

$HRANICEDLUZNIKA,

$HRANICEDLUZNIKA,

$TERMINPLATBA

));

// (

// (m.entrance_fee_date IS NULL OR m.entrance_fee_date = '0000-00-00') AND

// DATEDIFF(NOW(), m.entrance_date) > ?

// )

$current = NULL;

foreach ($debtors as $debtor)

{

$mids[] = $debtor->id;

if ($debtor->id != $current)

{

echo "# $debtor->id $debtor->login\n";

$current = $debtor->id;

}

echo "acl neplatic src $debtor->ip_address\n";

}

echo "\n######## FREENET ################################\n";

$members = $db->query("

SELECT DISTINCT m.id, u.login, ip.ip_address

FROM members m

JOIN users u ON u.member_id = m.id AND u.type = ?

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

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

JOIN ip_addresses ip ON ip.iface_id = i.id OR ip.member_id = m.id

WHERE m.id <> 1 AND m.id NOT IN (" . implode(', ', $mids) . ")

ORDER BY m.id, ip.id

", User_Model::MAIN_USER);

$current = NULL;

foreach ($members as $member)

{

if ($member->id != $current)

{

echo "# $member->id $member->login\n";

$current = $member->id;

}

echo "acl freenet src $member->ip_address\n";

}

echo "\n";

}