Projekt

Obecné

Profil

« Předchozí | Další » 

Revize 2046

Přidáno uživatelem David Raška před více než 11 roky(ů)

Opravy:
- Oprava exportu vCards s nastevenym fitrem

Upravy:
- Zrychleni SQL skriptu nacitani uzivatelu

Zobrazit rozdíly:

freenetis/branches/1.1/application/controllers/export.php
))
->selected('vcard40');
$form->checkbox('main_only')
->label('Export only main users')
->checked($export == 'members');
if ($export != 'users')
{
$form->checkbox('main_only')
->label('Export only main users')
->checked(TRUE);
}
$form->hidden('export')
->value($export);
$form->submit('Submit');
if ($form->validate())
{
$form_data = $form->as_array();
$main_only = (isset($form_data['main_only']) && $form_data['main_only'] == '1') || $export == 'members';
$to_export = $form_data['export'];
$filter_form = new Filter_form('u');
$main_only = isset($form_data['main_only']) && $form_data['main_only'] == '1';
$filter_form = new Filter_form();
$filter_form->autoload();
$user = new User_Model();
$user_model = new User_Model();
$items = array();
try
{
if (!is_numeric($export))
if ($to_export === 'users')
{
$export = NULL;
// export all users
$count = $user_model->count_all_users($filter_form->as_sql());
$items = $user_model->get_all_users(
0, $count, 'id', 'ASC', $filter_form->as_sql()
);
}
else if ($to_export === 'members')
{
// export main users
$member_model = new Member_Model();
$count = $member_model->count_all_members($filter_form->as_sql());
$members = $member_model->get_all_members(
0, $count, 'id', 'ASC', $filter_form->as_sql()
);
$member_ids = array();
foreach ($members as $m)
{
$member_ids[] = $m->id;
}
if (!empty($member_ids))
{
$count = $user_model->count_all_users_of_members($member_ids);
$items = $user_model->get_all_users_of_members($member_ids, 0, $count);
}
}
else if (is_numeric($export))
{
// export members users
$count = $user_model->count_all_users_by_member($export);
$items = $user_model->get_all_users_of_member($export, 0, $count);
}
$items = $user->get_all_users(0, 50, 'id', 'ASC', $filter_form->as_sql(), $export);
}
catch (Exception $e)
{
$items = array();
die;
}
// empty result?
......
/* Generate file */
// set content header
header('Content-type: text/vcard');
header('Content-type: text/vcard; charset=utf-8');
header('Content-Disposition: attachment; filename="'.__('Contacts').'.vcf"');
switch ($form_data['format'])
freenetis/branches/1.1/application/controllers/users.php
if (!$this->acl_check_view(get_class($this), 'users'))
Controller::error(ACCESS);
$filter_form = new Filter_form('u');
$filter_form = new Filter_form();
$filter_form->add('id')
->type('number');
freenetis/branches/1.1/application/models/member.php
m.address_point_id,
IF(m.registration = 1, ?, ?) AS registration,
m.registration AS registrations,
IFNULL(t.translated_term, e.value) AS type,
IF(mi.id IS NOT NULL, ?, ?) AS membership_interrupt,
m.type,
IF(mi.id IS NOT NULL, 1, 0) AS membership_interrupt,
IF(mi.id IS NOT NULL, 1, 0) AS interrupt,
m.organization_identifier, m.comment,
m.entrance_date, m.leaving_date, m.entrance_fee,
......
(
__('Yes'),
__('No'),
__('Yes'),
__('No'),
Config::get('lang'),
Config::get('lang'),
// redir shortcuts
......
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, ?, ?) AS membership_interrupt,
m.type,
IF(mi.id IS NOT NULL, 1, 0) AS membership_interrupt,
m.organization_identifier, m.comment,
m.entrance_date, m.leaving_date, m.entrance_fee,
m.speed_class_id
......
(
__('Yes'),
__('No'),
__('Yes'),
__('No'),
Config::get('lang'),
Config::get('lang'),
__('IM'),
freenetis/branches/1.1/application/models/user.php
$order_by = 'id', $order_by_direction = 'ASC',
$filter_sql='', $member_id=NULL)
{
$having = '';
$where = '';
if ($filter_sql != '')
$where .= 'WHERE '.$filter_sql;
$having .= 'HAVING '.$filter_sql;
if ($member_id)
$where .= ($where != '') ? ' AND member_id = '.intval($member_id) :
'WHERE member_id = '.intval($member_id);
$where = 'WHERE member_id = '.intval($member_id);
// order by direction check
if (strtolower($order_by_direction) != 'desc')
......
}
// optimalization
if (empty($where))
if (empty($having))
{
return $this->db->query("
SELECT u.*, m.name AS member_name
FROM users u
LEFT JOIN members m ON m.id = u.member_id
$where
ORDER BY $order_by $order_by_direction
LIMIT " . intval($limit_from) . ", " . intval($limit_results) . "
", $member_id);
}
return $this->db->query("
SELECT * FROM
(
SELECT
u.*,
m.name AS member_name,
IFNULL(email.value,'') AS email,
IFNULL(phone.value,'') AS phone,
IFNULL(jabber.value,'') AS jabber,
IFNULL(icq.value,'') AS icq
FROM users u
JOIN members m ON u.member_id = m.id
LEFT JOIN users_contacts uc_e ON uc_e.user_id = u.id
LEFT JOIN contacts email ON uc_e.contact_id = email.id AND email.type = ?
LEFT JOIN users_contacts uc_p ON uc_p.user_id = u.id
LEFT JOIN contacts phone ON uc_p.contact_id = phone.id AND phone.type = ?
LEFT JOIN users_contacts uc_j ON uc_j.user_id = u.id
LEFT JOIN contacts jabber ON uc_j.contact_id = jabber.id AND jabber.type = ?
LEFT JOIN users_contacts uc_i ON uc_i.user_id = u.id
LEFT JOIN contacts icq ON uc_i.contact_id = icq.id AND icq.type = ?
) AS u
SELECT
u.*,
m.name AS member_name,
IFNULL(email.value,'') AS email,
IFNULL(phone.value,'') AS phone,
IFNULL(jabber.value,'') AS jabber,
IFNULL(icq.value,'') AS icq
FROM users u
JOIN members m ON u.member_id = m.id
LEFT JOIN users_contacts uc_e ON uc_e.user_id = u.id
LEFT JOIN contacts email ON uc_e.contact_id = email.id AND email.type = ?
LEFT JOIN users_contacts uc_p ON uc_p.user_id = u.id
LEFT JOIN contacts phone ON uc_p.contact_id = phone.id AND phone.type = ?
LEFT JOIN users_contacts uc_j ON uc_j.user_id = u.id
LEFT JOIN contacts jabber ON uc_j.contact_id = jabber.id AND jabber.type = ?
LEFT JOIN users_contacts uc_i ON uc_i.user_id = u.id
LEFT JOIN contacts icq ON uc_i.contact_id = icq.id AND icq.type = ?
$where
GROUP BY u.id
$having
ORDER BY $order_by $order_by_direction
LIMIT " . intval($limit_from) . "," . intval($limit_results) . "
", array
......
*/
public function count_all_users($filter_sql = '', $member_id = NULL)
{
$having = '';
$where = '';
if ($filter_sql != '')
$where .= 'WHERE '.$filter_sql;
$having .= 'HAVING '.$filter_sql;
if ($member_id)
$where .= ($where != '') ? ' AND member_id = '.intval($member_id) :
'WHERE member_id = '.intval($member_id);
$where = 'WHERE member_id = '.intval($member_id);
// optimalization
if (empty($where))
return $this->count_all();
if (empty($having))
{
if (empty($where))
{
return $this->count_all();
}
return $this->db->query("
SELECT *
FROM users
$where
")->count();
}
return $this->db->query("
SELECT COUNT(*) AS total FROM
(
SELECT * FROM
(
SELECT
u.*,
m.name AS member_name,
IFNULL(email.value,'') AS email,
IFNULL(phone.value,'') AS phone,
IFNULL(jabber.value,'') AS jabber,
IFNULL(icq.value,'') AS icq
FROM users u
JOIN members m ON u.member_id = m.id
LEFT JOIN users_contacts uc_e ON uc_e.user_id = u.id
LEFT JOIN contacts email ON uc_e.contact_id = email.id AND email.type = ?
LEFT JOIN users_contacts uc_p ON uc_p.user_id = u.id
LEFT JOIN contacts phone ON uc_p.contact_id = phone.id AND phone.type = ?
LEFT JOIN users_contacts uc_j ON uc_j.user_id = u.id
LEFT JOIN contacts jabber ON uc_j.contact_id = jabber.id AND jabber.type = ?
LEFT JOIN users_contacts uc_i ON uc_i.user_id = u.id
LEFT JOIN contacts icq ON uc_i.contact_id = icq.id AND icq.type = ?
) AS u
$where
GROUP BY u.id
) q
SELECT
u.*,
m.name AS member_name,
IFNULL(email.value,'') AS email,
IFNULL(phone.value,'') AS phone,
IFNULL(jabber.value,'') AS jabber,
IFNULL(icq.value,'') AS icq
FROM users u
JOIN members m ON u.member_id = m.id
LEFT JOIN users_contacts uc_e ON uc_e.user_id = u.id
LEFT JOIN contacts email ON uc_e.contact_id = email.id AND email.type = ?
LEFT JOIN users_contacts uc_p ON uc_p.user_id = u.id
LEFT JOIN contacts phone ON uc_p.contact_id = phone.id AND phone.type = ?
LEFT JOIN users_contacts uc_j ON uc_j.user_id = u.id
LEFT JOIN contacts jabber ON uc_j.contact_id = jabber.id AND jabber.type = ?
LEFT JOIN users_contacts uc_i ON uc_i.user_id = u.id
LEFT JOIN contacts icq ON uc_i.contact_id = icq.id AND icq.type = ?
$where
GROUP BY u.id
$having
", array
(
Contact_Model::TYPE_EMAIL,
Contact_Model::TYPE_PHONE,
Contact_Model::TYPE_JABBER,
Contact_Model::TYPE_ICQ
))->current()->total;
))->count();
}
/**
......
}
/**
* Gets all users of members
*
* @author David Raska
* @param array $member_ids
* @param integer $limit_from
* @param integer $limit_results
* @param string $order_by
* @param string $order_by_direction
* @return unknown_type
*/
public function get_all_users_of_members(
$member_ids = NULL, $limit_from = 0, $limit_results = 50,
$order_by = 'id', $order_by_direction = 'ASC')
{
// order by direction check
if (strtolower($order_by_direction) != 'desc')
{
$order_by_direction = 'asc';
}
if (!$this->has_column($order_by))
{
$order_by = 'id';
}
$list = implode(' , ', $member_ids);
return $this->db->query("
SELECT u.*
FROM users u
WHERE member_id IN ($list)
ORDER BY $order_by $order_by_direction
LIMIT " . intval($limit_from) . "," . intval($limit_results) . "
");
}
/**
* Counts all users of 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.
* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
*
* @author David Raska
* @param array $member_ids
* @return integer
*/
public function count_all_users_of_members($member_ids = NULL)
{
$list = implode(' , ', $member_ids);
return $this->db->query("
SELECT u.*
FROM users u
WHERE member_id IN ($list)
")->count();
}
/**
* Function gets selected users.
*
* @param array $ids

Také k dispozici: Unified diff