/** * 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; }