function handler_geocoding($page, $category = null, $action = null, $id = null)
{
// Warning, this handler requires the following packages:
// * pkg-isocodes
// * isoquery
static $properties = array('country' => array('name' => 'pays', 'isocode' => '3166', 'table' => 'geoloc_countries', 'id' => 'iso_3166_1_a2', 'main_fields' => array('iso_3166_1_a3', 'iso_3166_1_num', 'countryEn'), 'other_fields' => array('worldRegion', 'country', 'capital', 'nationality', 'nationalityEn', 'phonePrefix', 'phoneFormat', 'licensePlate', 'belongsTo')), 'language' => array('name' => 'langages', 'isocode' => '639', 'table' => 'profile_langskill_enum', 'id' => 'iso_639_2b', 'main_fields' => array('iso_639_2t', 'iso_639_1', 'language_en'), 'other_fields' => array('language')));
if (is_null($category) || !array_key_exists($category, $properties)) {
pl_redirect('admin');
}
$data = $properties[$category];
if ($action == 'edit' || $action == 'add') {
$main_fields = array_merge(array($data['id']), $data['main_fields']);
$all_fields = array_merge($main_fields, $data['other_fields']);
if (is_null($id)) {
if (Post::has('new_id')) {
$id = Post::v('new_id');
} else {
pl_redirect('admin/geocoding/' . $category);
}
}
$list = array();
exec('isoquery --iso=' . $data['isocode'] . ' ' . $id, $list);
if (count($list) == 1) {
$array = explode("\t", $list[0]);
foreach ($main_fields as $i => $field) {
$iso[$field] = $array[$i];
}
} else {
$iso = array();
}
if ($action == 'add') {
if (Post::has('new_id')) {
S::assert_xsrf_token();
}
if (count($iso)) {
$item = $iso;
} else {
$item = array($data['id'] => $id);
}
XDB::execute('INSERT INTO ' . $data['table'] . '(' . implode(', ', array_keys($item)) . ')
VALUES ' . XDB::formatArray($item));
$page->trigSuccess($id . ' a bien été ajouté à la base.');
} elseif ($action == 'edit') {
if (Post::has('edit')) {
S::assert_xsrf_token();
$item = array();
$set = array();
foreach ($all_fields as $field) {
$item[$field] = Post::t($field);
$set[] = $field . XDB::format(' = {?}', $item[$field] ? $item[$field] : null);
}
XDB::execute('UPDATE ' . $data['table'] . '
SET ' . implode(', ', $set) . '
WHERE ' . $data['id'] . ' = {?}', $id);
call_user_func_array(array('self', 'update' . ucfirst($category)), array($item));
$page->trigSuccess($id . ' a bien été mis à jour.');
} elseif (Post::has('del')) {
S::assert_xsrf_token();
XDB::execute('DELETE FROM ' . $data['table'] . '
WHERE ' . $data['id'] . ' = {?}', $id);
$page->trigSuccessRedirect($id . ' a bien été supprimé.', 'admin/geocoding/' . $category);
} else {
$item = XDB::fetchOneAssoc('SELECT *
FROM ' . $data['table'] . '
WHERE ' . $data['id'] . ' = {?}', $id);
}
}
$page->changeTpl('admin/geocoding_edit.tpl');
$page->setTitle('Administration - ' . ucfirst($data['name']));
$page->assign('category', $category);
$page->assign('name', $data['name']);
$page->assign('all_fields', $all_fields);
$page->assign('id', $id);
$page->assign('iso', $iso);
$page->assign('item', $item);
return;
}
$page->changeTpl('admin/geocoding.tpl');
$page->setTitle('Administration - ' . ucfirst($data['name']));
$page->assign('category', $category);
$page->assign('name', $data['name']);
$page->assign('id', $data['id']);
$page->assign('main_fields', $data['main_fields']);
$page->assign('all_fields', array_merge($data['main_fields'], $data['other_fields']));
// First build the list provided by the iso codes.
$list = array();
exec('isoquery --iso=' . $data['isocode'], $list);
foreach ($list as $key => $item) {
$array = explode("\t", $item);
unset($list[$key]);
$list[$array[0]] = array();
foreach ($data['main_fields'] as $i => $field) {
$list[$array[0]][$field] = $array[$i + 1];
}
}
ksort($list);
// Retrieve all data from the database.
$db_list = XDB::rawFetchAllAssoc('SELECT *
FROM ' . $data['table'] . '
ORDER BY ' . $data['id'], $data['id']);
// Sort both iso and database data into 5 categories:
// $missing: data from the iso list not in the database,
// $non_existing: data from the database not in the iso list,
// $erroneous: data that differ on main fields,
// $incomplete: data with empty fields in the data base,
// $remaining: remaining correct and complete data from the database.
$missing = $non_existing = $erroneous = $incomplete = $remaining = array();
foreach (array_keys($list) as $id) {
if (!array_key_exists($id, $db_list)) {
$missing[$id] = $list[$id];
}
}
foreach ($db_list as $id => $item) {
if (!array_key_exists($id, $list)) {
$non_existing[$id] = $item;
} else {
$error = false;
foreach ($data['main_fields'] as $field) {
if ($item[$field] != $list[$id][$field]) {
$item[$field . '_error'] = true;
$error = true;
}
}
if ($error == true) {
$erroneous[$id] = $item;
} elseif (call_user_func_array(array('self', 'is' . ucfirst($category) . 'Incomplete'), array(&$item))) {
$incomplete[$id] = $item;
} else {
$remaining[$id] = $item;
}
}
}
$page->assign('lists', array('manquant' => $missing, 'disparu' => $non_existing, 'erroné' => $erroneous, 'incomplet' => $incomplete, 'restant' => $remaining));
}
public static function preload($pids = array())
{
if (self::$fullload) {
return;
}
// Load raw data
if (count($pids)) {
$join = 'LEFT JOIN profile_job ON (profile_job.jobid = pje.id)';
$where = 'WHERE profile_job.pid IN ' . XDB::formatArray($pids);
} else {
$join = '';
$where = '';
}
$it = XDB::iterator('SELECT pje.id, pje.name, pje.acronym, pje.url,
pa.flags, pa.text, pa.type, pa.pub
FROM profile_job_enum AS pje
LEFT JOIN profile_addresses AS pa ON (pje.id = pa.jobid AND pa.type = \'hq\')
' . $join . '
' . $where);
$newcompanies = array();
while ($row = $it->next()) {
$cp = new Company($row);
$addr = new Address($row);
$cp->setAddress($addr);
if (!array_key_exists($row['id'], self::$companies)) {
$newcompanies[] = $row['id'];
}
self::$companies[$row['id']] = $cp;
}
// Add phones to hq
if (count($newcompanies)) {
$it = Phone::iterate(array(), array(Phone::LINK_COMPANY), $newcompanies, Visibility::defaultForRead());
while ($phone = $it->next()) {
self::$companies[$phone->link_id]->setPhone($phone);
}
}
if (count($pids) == 0) {
self::$fullload = true;
}
}
public static function getPIDsFromUIDs($uids, $respect_order = true)
{
if ($respect_order) {
$order = 'ORDER BY ' . XDB::formatCustomOrder('uid', $uids);
} else {
$order = '';
}
return XDB::fetchAllAssoc('uid', 'SELECT ap.uid, ap.pid
FROM account_profiles AS ap
WHERE FIND_IN_SET(\'owner\', ap.perms)
AND ap.uid IN ' . XDB::formatArray($uids) . '
' . $order);
}
public function buildCondition(PlFilter $uf)
{
$uf->requireProfiles();
$nat = XDB::formatArray($this->val);
$conds = array('p.nationality1 IN ' . $nat, 'p.nationality2 IN ' . $nat, 'p.nationality3 IN ' . $nat);
return implode(' OR ', $conds);
}
