From c4035ce651b94fe581e22f38eb0dffaf518cfc7c Mon Sep 17 00:00:00 2001 From: Pascal Corpet Date: Sun, 4 Nov 2007 23:45:59 +0100 Subject: [PATCH] new names tables --- bin/cron/checkdb.php | 19 +++++++++++++++++++ modules/fusionax.php | 4 +++- upgrade/0.9.16/01_fusionax.sql | 39 +++++++++++++++++++++++++++++++++++++++ 3 files changed, 61 insertions(+), 1 deletion(-) create mode 100644 upgrade/0.9.16/01_fusionax.sql diff --git a/bin/cron/checkdb.php b/bin/cron/checkdb.php index 7abab81..6486fe7 100755 --- a/bin/cron/checkdb.php +++ b/bin/cron/checkdb.php @@ -182,5 +182,24 @@ check("SELECT matricule,nom,prenom,matricule_ax,COUNT(matricule_ax) AS c WHERE matricule_ax != '0' GROUP BY matricule_ax having c > 1", "à chaque personne de l'annuaire de l'AX (identification_ax) doit correspondre AU PLUS UNE personne de notre annuaire (auth_user_md5) -> si ce n'est pas le cas il faut regarder en manuel ce qui ne va pas !"); + +/* each alumni has one and only one display name by default and one and only one name when we talk to him directly */ +check("SELECT u.`user_id`, u.`nom`, u.`prenom`, COUNT(n.`display`) AS c + FROM `auth_user_md5` AS u + LEFT JOIN `profile_names_display` AS n ON(u.`user_id` = n.`user_id` AND FIND_IN_SET(n.`reason`, 'default')) + GROUP BY u.`user_id` + HAVING c != 1", "chaque personne doit avoir un et un seul nom par défaut"); +check("SELECT u.`user_id`, u.`nom`, u.`prenom`, COUNT(n.`display`) AS c + FROM `auth_user_md5` AS u + LEFT JOIN `profile_names_display` AS n ON(u.`user_id` = n.`user_id` AND FIND_IN_SET(n.`reason`, 'yourself')) + GROUP BY u.`user_id` + HAVING c != 1", "chaque personne doit avoir un et un seul nom quand on lui parle"); + +/* no alumni is allowed to have empty names */ +check("SELECT u.`user_id`, u.`nom`, u.`prenom` + FROM `auth_user_md5` AS u + INNER JOIN `profile_names_search` AS n USING(`user_id`) + WHERE n.`search_name` = ''", "liste des personnes qui ont un de leur nom de recherche vide"); + // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: ?> diff --git a/modules/fusionax.php b/modules/fusionax.php index ffb2b22..b89d0af 100644 --- a/modules/fusionax.php +++ b/modules/fusionax.php @@ -202,7 +202,8 @@ class FusionAxModule extends PLModule{ (ax.Nom_complet = xorg.nom OR ax.Nom_complet LIKE CONCAT(xorg.nom,' %') OR ax.Nom_complet LIKE CONCAT(xorg.nom,'-%') - OR ax.Nom_usuel = xorg.nom) AND + OR ax.Nom_usuel = xorg.nom + OR xorg.nom LIKE CONCAT('% ',ax.Nom_complet)) AND xorg.promo < ax.promotion_etude + 2 AND xorg.promo > ax.promotion_etude - 2) GROUP BY xorg.user_id @@ -285,6 +286,7 @@ class FusionAxModule extends PLModule{ } } } + function handler_misc(&$page) { $page->changeTpl('fusionax/misc.tpl'); diff --git a/upgrade/0.9.16/01_fusionax.sql b/upgrade/0.9.16/01_fusionax.sql new file mode 100644 index 0000000..3cff12c --- /dev/null +++ b/upgrade/0.9.16/01_fusionax.sql @@ -0,0 +1,39 @@ +CREATE TABLE IF NOT EXISTS `profile_names_display` ( + `user_id` int(11) NOT NULL COMMENT 'id of alumni', + `display` varchar(100) NOT NULL COMMENT 'name to display', + `sort` varchar(100) NOT NULL COMMENT 'name used for sorting', + `reason` set('default','yourself','profile','list') NOT NULL COMMENT 'where to display it', + `firstname` varchar(50) NOT NULL COMMENT 'first name', + `lastname` varchar(50) NOT NULL COMMENT 'last name', + PRIMARY KEY (`user_id`,`display`), + KEY `sort` (`sort`) +) CHARSET=utf8 COMMENT='Alumnis'' names to display'; + +INSERT INTO `profile_names_display` ( + SELECT + `user_id`, + CONCAT(`prenom`, ' ',IF(`nom_usage` != '',CONCAT(`nom_usage`,' (',`nom`,')'),`nom`)), + CONCAT(IF(`nom_usage` != '',`nom_usage`,`nom`),' ',`prenom`), + 'default', + `prenom`, + IF(`nom_usage` != '',`nom_usage`,`nom`) + FROM `auth_user_md5`); +INSERT INTO `profile_names_display` (SELECT `user_id`, `prenom`, `prenom`, 'yourself', `prenom`, `nom` FROM `auth_user_md5`); + +CREATE TABLE IF NOT EXISTS `profile_names_search` ( + `user_id` int(11) NOT NULL COMMENT 'id of alumni', + `search_name` varchar(50) NOT NULL COMMENT 'name to search for', + `name_type` enum('firstname','lastname','surname') NOT NULL default 'lastname' COMMENT 'type of name', + `search_score` smallint(6) NOT NULL COMMENT 'used to sort search results', + PRIMARY KEY (`nametype`,`name`,`user_id`), + KEY `user_id` (`user_id`) +) CHARSET=utf8 COMMENT='Names of alumni (search table)'; + +INSERT INTO `profile_names_search` ( SELECT `user_id`, `nom`, 'lastname', 10 FROM `auth_user_md5` WHERE `nom` != ''); +INSERT INTO `profile_names_search` ( SELECT `user_id`, `nom_usage`, 'lastname', 10 FROM `auth_user_md5` WHERE `nom` != `nom_usage` AND `nom_usage` != ''); +INSERT INTO `profile_names_search` ( SELECT `user_id`, `nom_ini`, 'lastname', 7 FROM `auth_user_md5` WHERE `nom` != `nom_ini` AND `nom_ini` != '' AND `nom_ini` != `nom_usage`); +INSERT INTO `profile_names_search` ( SELECT `user_id`, `prenom`, 'firstname', 8 FROM `auth_user_md5` WHERE `prenom` != ''); +INSERT INTO `profile_names_search` ( SELECT `user_id`, `prenom_ini`, 'firstname', 5 FROM `auth_user_md5` WHERE `prenom_ini` != `prenom` AND `prenom_ini` != ''); +INSERT INTO `profile_names_search` ( SELECT `user_id`, `profile_nick`, 'surname', 7 FROM `auth_user_quick` WHERE `profile_nick` != ''); + +-- vim:set syntax=mysql: -- 2.1.4