/** * Search in whole database * <p>We do multiple queries.</p> * @param $query string String to search * @return array Results sorted by type */ public function rechercher($query) { $like = $this->_bdd->echapper("%{$query}%"); $results = []; // "cotisations" for companies $select = <<<SQL SELECT pers.nom, pers.prenom, pers.email, pers.raison_sociale, cotis.* FROM afup_cotisations AS cotis LEFT JOIN afup_personnes_morales AS pers ON pers.id = cotis.id_personne WHERE cotis.type_personne = 1 AND ( cotis.informations_reglement LIKE {$like} OR cotis.numero_facture LIKE {$like} OR cotis.commentaires LIKE {$like} OR pers.email LIKE {$like} OR pers.nom LIKE {$like} OR pers.prenom LIKE {$like} ) ; SQL; if ($cotisations = $this->_bdd->obtenirTous($select, MYSQLI_ASSOC)) { $results['cotisations_personnes_morales'] = $cotisations; } // "cotisations" for people $select = <<<SQL SELECT pers.nom, pers.prenom, pers.email, pers.login, cotis.* FROM afup_cotisations AS cotis LEFT JOIN afup_personnes_physiques AS pers ON pers.id = cotis.id_personne WHERE cotis.type_personne = 0 AND ( cotis.informations_reglement LIKE {$like} OR cotis.numero_facture LIKE {$like} OR cotis.commentaires LIKE {$like} OR pers.login LIKE {$like} OR pers.email LIKE {$like} OR pers.nom LIKE {$like} OR pers.prenom LIKE {$like} ) ; SQL; if ($cotisations = $this->_bdd->obtenirTous($select, MYSQLI_ASSOC)) { $results['cotisations_personnes_physiques'] = $cotisations; } // Forum registrations $select = <<<SQL SELECT insc.*, forum.titre AS forum_titre FROM afup_inscription_forum AS insc LEFT JOIN afup_forum AS forum ON insc.id_forum = forum.id WHERE insc.reference LIKE {$like} OR insc.informations_reglement LIKE {$like} OR insc.commentaires LIKE {$like} OR insc.nom LIKE {$like} OR insc.prenom LIKE {$like} OR insc.email LIKE {$like} ; SQL; if ($registrations = $this->_bdd->obtenirTous($select, MYSQLI_ASSOC)) { $results['forum_inscriptions'] = $registrations; } // Forum invoicing $select = <<<SQL SELECT inv.*, forum.titre AS forum_titre FROM afup_facturation_forum AS inv LEFT JOIN afup_forum AS forum ON inv.id_forum = forum.id WHERE inv.reference LIKE {$like} OR inv.informations_reglement LIKE {$like} OR inv.email LIKE {$like} OR inv.societe LIKE {$like} OR inv.nom LIKE {$like} OR inv.prenom LIKE {$like} OR inv.autorisation LIKE {$like} OR inv.transaction LIKE {$like} ; SQL; if ($invoices = $this->_bdd->obtenirTous($select, MYSQLI_ASSOC)) { $results['forum_factures'] = $invoices; } // Global invoicing $select = <<<SQL SELECT inv.*, SUM(det.pu * det.quantite) AS total, GROUP_CONCAT(det.ref SEPARATOR ', ') AS refs, GROUP_CONCAT(det.designation SEPARATOR ', ') AS details FROM afup_compta_facture AS inv LEFT JOIN afup_compta_facture_details AS det ON det.idafup_compta_facture = inv.id AND det.quantite > 0 WHERE inv.numero_devis LIKE {$like} OR inv.numero_facture LIKE {$like} OR inv.societe LIKE {$like} OR inv.service LIKE {$like} OR inv.email LIKE {$like} OR inv.ref_clt1 LIKE {$like} OR inv.ref_clt2 LIKE {$like} OR inv.ref_clt3 LIKE {$like} OR inv.observation LIKE {$like} GROUP BY inv.id ; SQL; if ($invoices = $this->_bdd->obtenirTous($select, MYSQLI_ASSOC)) { $results['factures'] = $invoices; } return $results; }
<?php require_once __DIR__ . '/../sources/Afup/Bootstrap/Cli.php'; echo "Synchronisation Redmine " . date('Y-m-d H:i:s') . PHP_EOL; $redmine = new AFUP_Base_De_Donnees($conf->obtenir('redmine|hote'), $conf->obtenir('redmine|base'), $conf->obtenir('redmine|utilisateur'), $conf->obtenir('redmine|mot_de_passe')); echo " - recuperation membres Afup...\n"; $membresAfup = $bdd->obtenirTous('SELECT id as multipass_remote_uid, login, prenom as firstname, nom as lastname, email as mail, etat as status FROM afup_personnes_physiques WHERE etat = 1'); echo " - recuperation comptes Redmine...\n"; $tmpMembresRedmine = $redmine->obtenirTous('SELECT multipass_remote_uid, login, firstname, lastname, mail, status FROM users WHERE multipass_remote_uid is not null and multipass_remote_uid > 0'); foreach ($tmpMembresRedmine as $mR) { $membresRedmine[$mR['multipass_remote_uid']] = $mR; } foreach ($membresAfup as $mA) { $id = $mA['multipass_remote_uid']; if (array_key_exists($id, $membresRedmine)) { $mR = $membresRedmine[$id]; // On teste les différences $diff = array_diff($mA, $mR); if (count($diff)) { $data = array(); foreach ($diff as $k => $v) { $data[] = "{$k} = '" . mysqli_real_escape_string($bdd->getDbLink(), $v) . "'"; } // On modifie l'utilisateur echo " - modification: {$id}\n"; $redmine->executer("UPDATE users SET " . implode(', ', $data) . " WHERE multipass_remote_uid = '" . $id . "'"); } } else { // On crée l'utilisateur echo " - creation: {$id}\n"; $redmine->executer("INSERT INTO users VALUES(null,\n '" . mysql_real_escape_string($mA['login']) . "',\n sha1(concat('" . mysql_real_escape_string($mA['mail']) . "', now())),\n '" . mysql_real_escape_string($mA['firstname']) . "',\n '" . mysql_real_escape_string($mA['lastname']) . "',\n '" . mysql_real_escape_string($mA['mail']) . "',\n 0, 0, 1, null,\n 'fr', null, now(), now(),\n 'User', null, {$id})");