Revize 1548
Přidáno uživatelem Michal Kliment před více než 12 roky(ů)
| freenetis/branches/testing/application/models/member.php | ||
|---|---|---|
|
return $this->db->query("
|
||
|
SELECT id, id AS member_id, name AS member_name, registration, registrations,
|
||
|
name, street, street_number, town, quarter, variable_symbol, aid, balance,
|
||
|
a_comment, a_comments_thread_id, type, entrance_date, leaving_date,
|
||
|
GROUP_CONCAT(DISTINCT redirect_type SEPARATOR ', ') AS redirect,
|
||
|
a_comment, a_comments_thread_id, type, entrance_date, leaving_date,
|
||
|
redirect_type_id, GROUP_CONCAT(DISTINCT redirect_type SEPARATOR ', ') AS redirect,
|
||
|
GROUP_CONCAT(DISTINCT redirect_type_text SEPARATOR ', \n') AS redirect_text,
|
||
|
whitelisted, interrupt, 1 AS redirection, 1 AS email, 1 AS sms,
|
||
|
cloud
|
||
| ... | ... | |
|
a.balance, a_comment,
|
||
|
a.comments_thread_id AS a_comments_thread_id,
|
||
|
m.type, m.entrance_date, m.leaving_date, redirect_type,
|
||
|
redirect_type_text, whitelisted, interrupt,
|
||
|
cl.cloud
|
||
|
redirect_type_id, redirect_type_text, whitelisted,
|
||
|
interrupt, cl.cloud
|
||
|
FROM
|
||
|
(
|
||
|
SELECT m.id,
|
||
| ... | ... | |
|
LEFT JOIN
|
||
|
(
|
||
|
SELECT DISTINCT
|
||
|
ms.type AS redirect_type_id,
|
||
|
IF(ms.type = 4, ? ,IF(ms.type = 5, ?, IF(ms.type = 6, ?, IF(ms.type = 7, ?, ?)))) AS redirect_type,
|
||
|
IF(ms.type = 4, ?,IF(ms.type = 5, ?, IF(ms.type = 6, ?, IF(ms.type = 7, ?, ?)))) AS redirect_type_text,
|
||
|
IFNULL(u.member_id,ms.member_id) AS member_id
|
||
| ... | ... | |
|
LEFT JOIN
|
||
|
(
|
||
|
SELECT DISTINCT
|
||
|
ms.type AS redirect_type_id,
|
||
|
IF(ms.type = 4, ? ,IF(ms.type = 5, ?, IF(ms.type = 6, ?, ?))) AS redirect_type,
|
||
|
IF(ms.type = 4, ?,IF(ms.type = 5, ?, IF(ms.type = 6, ?, ?))) AS redirect_type_text,
|
||
|
IFNULL(u.member_id,ms.member_id) AS member_id, whitelisted
|
||
| ... | ... | |
|
return $this->db->query("
|
||
|
SELECT id, member_name, variable_symbol, street, street_number,
|
||
|
town, quarter, login, birthday, entrance_date, leaving_date,
|
||
|
type, comment, registration, membership_interrupt
|
||
|
type, comment, registration, membership_interrupt,
|
||
|
redirect_type_id
|
||
|
FROM
|
||
|
(
|
||
|
SELECT id, name AS member_name, registration,
|
||
|
street, street_number, town, quarter, GROUP_CONCAT(variable_symbol) AS variable_symbol,
|
||
|
street, street_number, town, quarter,
|
||
|
GROUP_CONCAT(DISTINCT variable_symbol) AS variable_symbol,
|
||
|
type, entrance_date,
|
||
|
IF(leaving_date = '0000-00-00', null, leaving_date) AS leaving_date,
|
||
|
birthday, login, comment, membership_interrupt
|
||
|
birthday, login, comment, membership_interrupt,
|
||
|
redirect_type_id
|
||
|
FROM
|
||
|
(
|
||
|
SELECT
|
||
|
m.id, m.registration, m.name,
|
||
|
s.street, ap.street_number, t.town, t.quarter,
|
||
|
s.street, ap.street_number, t.town, t.quarter,
|
||
|
vs.variable_symbol, m.type, m.entrance_date,
|
||
|
m.leaving_date,
|
||
|
u.birthday, u.login, m.comment, m.membership_interrupt
|
||
|
u.birthday, u.login, m.comment, m.membership_interrupt,
|
||
|
ms.redirect_type_id
|
||
|
FROM
|
||
|
(
|
||
|
SELECT m.id,
|
||
|
m.name,
|
||
|
m.address_point_id,
|
||
|
IF(m.registration = 1, ?, ?) AS registration,
|
||
|
IFNULL(t.translated_term, e.value) AS type,
|
||
|
IF(mi.id IS NOT NULL, 1, 0) AS membership_interrupt,
|
||
|
m.comment, m.entrance_date, m.leaving_date
|
||
|
registration,
|
||
|
type, entrance_fee,
|
||
|
IF(mi.id IS NOT NULL, 1, 0) AS membership_interrupt,
|
||
|
m.comment, m.entrance_date, m.leaving_date,
|
||
|
organization_identifier
|
||
|
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()
|
||
|
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 variable_symbols vs ON vs.account_id = a.id
|
||
|
LEFT JOIN users u ON u.member_id = m.id AND u.type = ?
|
||
|
LEFT JOIN enum_types e ON m.type = e.id
|
||
|
LEFT JOIN devices d ON d.user_id = u.id
|
||
|
LEFT JOIN ifaces i ON i.device_id = d.id
|
||
|
LEFT JOIN ip_addresses ip ON ip.iface_id = i.id
|
||
|
LEFT JOIN messages_ip_addresses mip ON mip.ip_address_id = ip.id
|
||
|
LEFT JOIN
|
||
|
(
|
||
|
SELECT m.*, type AS redirect_type_id
|
||
|
FROM messages m
|
||
|
) ms ON mip.message_id = ms.id
|
||
|
LEFT JOIN
|
||
|
(
|
||
|
SELECT c.id AS cloud,
|
||
|
IFNULL(u.member_id, c.member_id) AS member_id
|
||
|
FROM
|
||
|
(
|
||
|
SELECT c.*, i.device_id, ip.member_id
|
||
|
FROM clouds c
|
||
|
JOIN clouds_subnets cs ON cs.cloud_id = c.id
|
||
|
JOIN subnets s ON cs.subnet_id = s.id
|
||
|
JOIN ip_addresses ip ON ip.subnet_id = s.id
|
||
|
JOIN ifaces i ON ip.iface_id = i.id
|
||
|
) c
|
||
|
LEFT JOIN devices d ON c.device_id = d.id
|
||
|
LEFT JOIN users u ON d.user_id = u.id
|
||
|
) cl ON cl.member_id = m.id
|
||
|
$filter_sql
|
||
|
) AS q
|
||
|
GROUP BY q.id
|
||
|
ORDER BY q.id
|
||
|
ORDER BY q.id
|
||
|
) AS q
|
||
|
", array
|
||
|
(
|
||
|
__('Yes'),
|
||
|
__('No'),
|
||
|
Config::get('lang'),
|
||
|
User_Model::MAIN_USER
|
||
|
));
|
||
|
|
||
|
die($this->db->last_query());
|
||
|
}
|
||
|
|
||
|
/**
|
||
| freenetis/branches/testing/application/controllers/members.php | ||
|---|---|---|
|
$filter_form->add('comment');
|
||
|
|
||
|
$filter_form->add('registration')
|
||
|
->type('combo')
|
||
|
->type('select')
|
||
|
->values(arr::bool());
|
||
|
|
||
|
$filter_form->add('organization_identifier')
|
||
| ... | ... | |
|
->table('s')
|
||
|
->values(
|
||
|
array_unique(
|
||
|
$street_model->select_list('id', 'street')
|
||
|
$street_model->select_list('street', 'street')
|
||
|
)
|
||
|
);
|
||
|
|
||
| ... | ... | |
|
->type('number')
|
||
|
->table('ap');
|
||
|
|
||
|
$filter_form->add('redirect_type')
|
||
|
$filter_form->add('redirect_type_id')
|
||
|
->label(__('Redirection'))
|
||
|
->type('select')
|
||
|
->values(array
|
||
|
(
|
||
|
__('IM') => __('Membership interrupt'),
|
||
|
__('DB') => __('Debtor'),
|
||
|
__('PN') => __('Payment notice'),
|
||
|
__('UM') => __('User message')
|
||
|
Message_Model::INTERRUPTED_MEMBERSHIP_MESSAGE => __('Membership interrupt'),
|
||
|
Message_Model::DEBTOR_MESSAGE => __('Debtor'),
|
||
|
Message_Model::PAYMENT_NOTICE_MESSAGE => __('Payment notice'),
|
||
|
Message_Model::UNALLOWED_CONNECTING_PLACE_MESSAGE => __('Unallowed connecting place'),
|
||
|
Message_Model::USER_MESSAGE => __('User message')
|
||
|
))->table('ms');
|
||
|
|
||
|
$filter_form->add('whitelisted')
|
||
Také k dispozici: Unified diff
Opraven export clenu (implementace #199).
Bohuzel nelze prenaset retezcove tvary typu filtru (doslo by k prekroceni povolenene maximalni delky URL). Implemtovano pouze prepsanim SQL dotazu pro export clenu.