public function execute() { $c = new Convertor(); $c->connect($this->config->getServer(), $this->config->getUser(), $this->config->getPassword(), $this->config->getDBName()); //Requesting all the streets $requestVille = "\n\t\t\t\tSELECT idVille , nom\n\t\t\t\tFROM ville\n\t\t\t\tWHERE nom != \"\"\n\t\t\t\tGROUP BY idVille\n\t\t\t\tORDER BY idVille\n\t\t\t\t"; $res = $c->execute($requestVille); $infoVille = array(); while ($fetch = mysql_fetch_assoc($res)) { $infoVille[] = $fetch; } $quartiersByVille = array(); $i = 0; foreach ($infoVille as $info) { $ville = array(); $ville['ville'] = $info['nom']; $request = "\n\t\t\t\t\tSELECT r.prefixe as prefixe, r.nom as nom\n\t\t\t\t\tFROM rue r\n\t\t\t\t\tLEFT JOIN sousQuartier sq on sq.idSousQuartier = r.idSousQuartier\n\t\t\t\t\tLEFT JOIN quartier q on q.idQuartier = sq.idQuartier\n\t\t\t\t\tLEFT JOIN ville v on v.idVille = q.idVille\n\t\t\t\t\tWHERE v.idVille = " . $info['idVille'] . "\n\t\t\t\t\t\t\tORDER BY q.idVille\n\t\t\t\t\t\t\t"; $resProcess = $c->execute($request); $quartierArray = array(); $j = 0; while ($fetchQuartier = mysql_fetch_assoc($resProcess)) { $labelQuartier = 'rues' . $j++; $quartierArray[$labelQuartier] = $fetchQuartier; } $ville['rues'] = $quartierArray; $labelVille = 'ville' . $i++; $quartiersByVille[$labelVille] = $ville; } $c->complexArrayToXML($quartiersByVille, 'xml/ruesVille.xml', 'ruesVille', 'rue'); $c->complexArrayToCSV($quartiersByVille, "csv/ruesVille.csv", 'ruesVille', 'rue'); }
public function execute() { $c = new Convertor(); $c->connect($this->config->getServer(), $this->config->getUser(), $this->config->getPassword(), $this->config->getDBName()); //Requesting all the streets $requestQuartier = "\n\t\t\t\tSELECT idSousQuartier ,nom\n\t\t\t\tFROM sousQuartier\n\t\t\t\tWHERE nom != ''\n\t\t\t\tGROUP BY idSousQuartier\n\t\t\t\tORDER BY idSousQuartier\n\t\t\t\t"; $res = $c->execute($requestQuartier); $idQuartierArray = array(); $listeQuartier = array(); while ($fetch = mysql_fetch_assoc($res)) { $listeQuartier[] = $fetch; } $ruesByQuartier = array(); $i = 0; foreach ($listeQuartier as $quartierInfo) { $quartier = array(); $quartier['nom'] = $quartierInfo['nom']; $request = "\n\t\t\t\t\tSELECT r.prefixe , r.nom\n\t\t\t\t\tFROM historiqueAdresse ha\n\t\t\t\t\tLEFT JOIN rue r on r.idRue = ha.idRue\n\t\t\t\t\tWHERE ha.idSousQuartier = " . $quartierInfo['idSousQuartier'] . "\n\t\t\t\t\t\t\tGROUP BY ha.idRue\n\t\t\t\t\t\t\tORDER BY ha.idRue\n\t\t\t\t\t\t\t"; $resProcess = $c->processRequest($request, 'rue'); $quartier['rues'] = $resProcess; $label = 'sousQuartier' . $i++; $ruesByQuartier[$label] = $quartier; } $c->complexArrayToXML($ruesByQuartier, "xml/ruesSousQuartier.xml", 'ruesSousQuartier', 'rue'); $c->complexArrayToCSV($ruesByQuartier, "csv/ruesSousQuartier.csv", 'ruesSousQuartier', 'rue'); }
public function execute() { $utils = new Utils(); $c = new Convertor(); $c->connect($this->config->getServer(), $this->config->getUser(), $this->config->getPassword(), $this->config->getDBName()); //Requesting all the borough $requestRue = "\n\t\t\t\tSELECT idRue ,nom , prefixe\n\t\t\t\tFROM rue\n\t\t\t\tWHERE nom != ''\n\t\t\t\tGROUP BY idRue\n\t\t\t\tORDER BY idRue\n\t\t\t\t"; $res = $c->execute($requestRue); $listeQuartier = array(); $ruesByQuartier = array(); $i = 0; while ($fetch = mysql_fetch_assoc($res)) { $rue = array(); $rue['prefixe'] = $fetch['prefixe']; $rue['nom'] = $fetch['nom']; $request = "\n\t\t\t\t\tSELECT hi.idHistoriqueImage, hi.dateUpload\n\t\t\t\t\tFROM _evenementImage ei\n\t\t\t\t\tLEFT JOIN _evenementEvenement ee on ee.idEvenementAssocie = ei.idEvenement\n\t\t\t\t\tLEFT JOIN _adresseEvenement ae on ae.idEvenement = ee.idEvenement\n\t\t\t\t\tLEFT JOIN historiqueAdresse ha on ha.idAdresse = ae.idAdresse\n\t\t\t\t\tLEFT JOIN historiqueImage hi on hi.idImage = ei.idImage\n\t\t\t\t\tWHERE ha.idRue = " . $fetch['idRue'] . "\n\t\t\t\t\t\t\t"; $resProcess = $c->processRequest($request, 'url'); $j = 0; $urlImage = array(); foreach ($resProcess as $infoImage) { $indice = 'url' . $j++; $urlImage[$indice] = $utils->createArchiWikiPhotoUrl($infoImage['idHistoriqueImage'], $infoImage['dateUpload'], 'http://archi-strasbourg.org', 'grand'); } $rue['url'] = $urlImage; $label = 'rue' . $i++; $ruesByQuartier[$label] = $rue; } $c->complexArrayToXML($ruesByQuartier, "xml/urlPhotosRue.xml", 'urlPhotosRue', 'urlPhoto'); $c->complexArrayToCSV($ruesByQuartier, "csv/urlPhotosRue.csv", 'urlPhotosRue', 'urlPhoto'); }
public function execute() { $c = new Convertor(); $c->connect($this->config->getServer(), $this->config->getUser(), $this->config->getPassword(), $this->config->getDBName()); //Requesting all the streets $requestStreets = "\n\t\t\t\tSELECT r.prefixe as prefixe,ha.idRue as idRue ,r.nom as nom\n\t\t\t\tFROM historiqueAdresse ha\n\t\t\t\tLEFT JOIN rue r on r.idRue = ha.idRue\n\t\t\t\tWHERE r.nom != ''\n\t\t\t\tGROUP BY ha.idRue\n\t\t\t\tORDER BY ha.idRue\n\t\t\t\t"; $res = $c->execute($requestStreets); $adressesByRue = array(); $i = 0; while ($fetch = mysql_fetch_array($res)) { $rueArray = array(); $rueArray['prefixe'] = $fetch['prefixe']; $rueArray['nom'] = $fetch['nom']; $rue = array('prefixe' => $fetch['prefixe'], 'nom' => $fetch['nom'], 'idRue' => $fetch['idRue']); $prefixe = $fetch['prefixe']; $nom = $fetch['nom']; $reqTitre = "\n\t\t\t\t\tSELECT he1.titre as titre\n\t\t\t\t\tFROM historiqueAdresse ha\n\t\t\t\t\tLEFT JOIN _adresseEvenement ae on ae.idAdresse = ha.idAdresse\n\t\t\t\t\tLEFT JOIN evenements he1 ON he1.idEvenement = ae.idEvenement\n\t\t\t\t\tWHERE he1.titre!=''\n\t\t\t\t\tAND ha.idRue =" . $rue['idRue'] . "\n\t\t\t\t\t\t\tAND he1.idTypeEvenement <>'6'\n\t\t\t\t\t\t\tGROUP BY he1.idEvenement\n\t\t\t\t\t\t\tORDER BY he1.dateDebut\n\t\t\t\t\t\t\tLIMIT 1"; $resTitre = $c->processRequest($reqTitre); if (isset($resTitre['titre'])) { $fetchTitre = $resTitre; $titre = stripslashes($fetchTitre['titre']); if (trim($fetchTitre['titre']) == '') { $noTitreDetected = true; $titre = ''; } } else { $titre = ""; } $reqidAdresse = "\n\t\t\t\t\tSELECT idAdresse, numero\n\t\t\t\t\tFROM historiqueAdresse\n\t\t\t\t\tWHERE idRue = " . $rue['idRue'] . "\n\t\t\t\t\t\t\t"; $resIdArray = $c->execute($reqidAdresse); $adresseArray = array(); $indice = ''; while ($fetchAdresse = mysql_fetch_assoc($resIdArray)) { $idAdresse = $fetchAdresse['idAdresse']; $adresseArray['numero'] = $fetchAdresse['numero']; $requeteEvenements = ' SELECT hE.idEvenement, hE.titre, hE.idSource, hE.idTypeStructure, hE.idTypeEvenement, hE.description, hE.dateDebut, hE.dateFin, tE.nom AS nomTypeEvenement, tS.nom AS nomTypeStructure, s.nom AS nomSource, tE.groupe, hE.ISMH , hE.MH, date_format(hE.dateCreationEvenement,"' . _("%e/%m/%Y - %kh%i") . '") as dateCreationEvenement, hE.isDateDebutEnviron as isDateDebutEnviron, hE.numeroArchive as numeroArchive, ae.idAdresse, ha.idVille FROM evenements he2 LEFT JOIN _adresseEvenement ae on ae.idEvenement = he2.idEvenement LEFT JOIN _evenementEvenement ee on ee.idEvenement = ae.idEvenement LEFT JOIN evenements hE on hE.idEvenement = ee.idEvenementAssocie LEFT JOIN source s ON s.idSource = hE.idSource LEFT JOIN typeStructure tS ON tS.idTypeStructure = hE.idTypeStructure LEFT JOIN typeEvenement tE ON tE.idTypeEvenement = hE.idTypeEvenement LEFT JOIN historiqueAdresse ha on ha.idAdresse = ae.idAdresse WHERE ha.idAdresse=' . $idAdresse . ' ORDER BY hE.idEvenement DESC '; $resEvenements = $c->execute($requeteEvenements); $evenementArray = array(); $k = 0; while ($fetchEvenement = mysql_fetch_assoc($resEvenements)) { $labelEvenement = 'evenement' . $k++; $evenementArray[$labelEvenement] = array('titre' => $fetchEvenement['titre'], 'source' => $fetchEvenement['nomSource'], 'dateDebut' => $fetchEvenement['dateDebut'], 'dateFin' => $fetchEvenement['dateFin'], 'nomTypeEvenement' => $fetchEvenement['nomTypeEvenement'], 'nomTypeStructure' => $fetchEvenement['nomTypeStructure'], 'ISMH' => $fetchEvenement['ISMH'], 'MH' => $fetchEvenement['MH'], 'dateCreationEvenement' => $fetchEvenement['dateCreationEvenement']); } $adresseArray['evenements'] = $evenementArray; } $rueArray['adresses'] = $adresseArray; $indice = 'rue' . $i++; $adressesByRue[$indice] = $rueArray; } $c->complexArrayToXML($adressesByRue, "xml/adressesRue.xml", 'AdresseRue', 'adresse'); $c->complexArrayToCSV($adressesByRue, "csv/adressesRue.csv", 'AdresseRue', 'adresse'); }
public function execute() { $c = new Convertor(); $c->connect($this->config->getServer(), $this->config->getUser(), $this->config->getPassword(), $this->config->getDBName()); //Requesting all the architects $requeteArchitecte = "\n\t\t\t\tSELECT p.idPersonne, p.nom, p.prenom,p.description,p.dateNaissance,p.dateDeces,p.idMetier\n\t\t\t\tFROM personne p\n\t\t\t\tLEFT JOIN metier m ON m.idMetier = p.idMetier\n\t\t\t\tWHERE m.nom = 'architecte'\n\t\t\t\tGROUP BY p.idPersonne\n\t\t\t\tORDER BY p.idPersonne\n\t\t\t\t"; $res = $c->execute($requeteArchitecte); $adressesByArchitecte = array(); $i = 0; while ($fetch = mysql_fetch_array($res)) { $architecteArray = array('nom' => $fetch['nom'], 'prenom' => $fetch['prenom'], 'description' => $fetch['description'], 'dateNaissance' => $fetch['dateNaissance'], 'dateDeces' => $fetch['dateDeces']); $reqidAdresse = "\n\t\t\t\t\tSELECT ha.idAdresse,\n\t\t\t\t\tha.numero, \n\t\t\t\t\tr.prefixe,\n\t\t\t\t\tr.nom as nomRue, \n\t\t\t\t\tsq.nom as nomSousQuartier,\n\t\t\t\t\tq.nom as nomQuartier,\n\t\t\t\t\tv.nom as nomVille,\n\t\t\t\t\tpa.nom as nomPays\n\t\t\t\t\tFROM evenements e1\n\t\t\t\t\tLEFT JOIN _evenementPersonne ep ON ep.idEvenement = e1.idEvenement\n\t\t\t\t\tLEFT JOIN personne p ON p.idPersonne = ep.idPersonne\n\t\t\t\t\tLEFT JOIN metier m ON m.idMetier = p.idMetier\n\t\t\t\t\tLEFT JOIN _evenementEvenement ee ON ee.idEvenementAssocie = e1.idEvenement\n\t\t\t\t\tLEFT JOIN evenements e2 on e2.idEvenement = ee.idEvenement\n\t\t\t\t\tLEFT JOIN _adresseEvenement ae on ae.idEvenement = e2.idEvenement\n\t\t\t\t\tLEFT JOIN historiqueAdresse ha on ha.idAdresse = ae.idAdresse\n\t\t\t\t\tLEFT JOIN rue r on r.idRue = ha.idRue\n\t\t\t\t\tLEFT JOIN sousQuartier sq on sq.idSousQuartier = ha.idSousQuartier\n\t\t\t\t\tLEFT JOIN quartier q on q.idQuartier = ha.idQuartier\n\t\t\t\t\tLEFT JOIN ville v on v.idVille = ha.idVille\n\t\t\t\t\tLEFT JOIN pays pa on pa.idPays = ha.idPays\n\t\t\t\t\tWHERE m.nom = 'architecte'\n\t\t\t\t\tAND ha.idAdresse is not null\n\t\t\t\t\tAND p.idPersonne =" . $fetch['idPersonne'] . "\n\t\t\t\t\t\t\t"; $resIdArray = $c->execute($reqidAdresse); $adresseArray = array(); $indice = ''; while ($fetchAdresse = mysql_fetch_assoc($resIdArray)) { $idAdresse = $fetchAdresse['idAdresse']; $adresseArray['numero'] = $fetchAdresse['numero']; $adresseArray['prefixe'] = $fetchAdresse['prefixe']; $adresseArray['nomRue'] = $fetchAdresse['nomRue']; $adresseArray['nomSousQuartier'] = $fetchAdresse['nomSousQuartier']; $adresseArray['nomQuartier'] = $fetchAdresse['nomQuartier']; $adresseArray['nomVille'] = $fetchAdresse['nomVille']; $adresseArray['nomPays'] = $fetchAdresse['nomPays']; $requeteEvenements = ' SELECT hE.idEvenement, hE.titre, hE.idSource, hE.idTypeStructure, hE.idTypeEvenement, hE.description, hE.dateDebut, hE.dateFin, tE.nom AS nomTypeEvenement, tS.nom AS nomTypeStructure, s.nom AS nomSource, tE.groupe, hE.ISMH , hE.MH, date_format(hE.dateCreationEvenement,"' . _("%e/%m/%Y - %kh%i") . '") as dateCreationEvenement, hE.isDateDebutEnviron as isDateDebutEnviron, hE.numeroArchive as numeroArchive, ae.idAdresse, ha.idVille FROM evenements he2 LEFT JOIN _adresseEvenement ae on ae.idEvenement = he2.idEvenement LEFT JOIN _evenementEvenement ee on ee.idEvenement = ae.idEvenement LEFT JOIN evenements hE on hE.idEvenement = ee.idEvenementAssocie LEFT JOIN source s ON s.idSource = hE.idSource LEFT JOIN typeStructure tS ON tS.idTypeStructure = hE.idTypeStructure LEFT JOIN typeEvenement tE ON tE.idTypeEvenement = hE.idTypeEvenement LEFT JOIN historiqueAdresse ha on ha.idAdresse = ae.idAdresse WHERE ha.idAdresse=' . $idAdresse . ' ORDER BY hE.idEvenement DESC '; $resEvenements = $c->execute($requeteEvenements); $evenementArray = array(); $k = 0; while ($fetchEvenement = mysql_fetch_assoc($resEvenements)) { $labelEvenement = 'evenement' . $k++; $evenementArray[$labelEvenement] = array('titre' => $fetchEvenement['titre'], 'source' => $fetchEvenement['nomSource'], 'dateDebut' => $fetchEvenement['dateDebut'], 'dateFin' => $fetchEvenement['dateFin'], 'nomTypeEvenement' => $fetchEvenement['nomTypeEvenement'], 'nomTypeStructure' => $fetchEvenement['nomTypeStructure'], 'ISMH' => $fetchEvenement['ISMH'], 'MH' => $fetchEvenement['MH'], 'dateCreationEvenement' => $fetchEvenement['dateCreationEvenement']); } $adresseArray['evenements'] = $evenementArray; } $architecteArray['adresses'] = $adresseArray; $indice = 'architecte' . $i++; $adressesByArchitecte[$indice] = $architecteArray; } $c->complexArrayToXML($adressesByArchitecte, "xml/adressesArchitecte.xml", 'adresseArchitecte', 'architecte'); $c->complexArrayToCSV($adressesByArchitecte, "csv/adressesArchitecte.csv", 'adresseArchitecte', 'architecte'); }
public function execute() { $c = new Convertor(); $c->connect($this->config->getServer(), $this->config->getUser(), $this->config->getPassword(), $this->config->getDBName()); //Requesting all the neighborohood $requestNeigh = "\n\t\t\t\tSELECT q.codepostal as codepostal,\n\t\t\t\tha.idQuartier as idQuartier ,\n\t\t\t\tq.nom as nomQuartier,\n\t\t\t\tv.nom as nomVille,\n\t\t\t\tp.nom as nomPays\n\t\t\t\t\n\t\t\t\tFROM historiqueAdresse ha\n\t\t\t\tLEFT JOIN rue r on r.idRue = ha.idRue\n\t\t\t\tLEFT JOIN quartier q on q.idQuartier = ha.idQuartier\n\t\t\t\tLEFT JOIN ville v on v.idVille = ha.idVille\n\t\t\t\tLEFT JOIN pays p on p.idPays = ha.idPays\n\t\t\t\tWHERE q.nom != ''\n\t\t\t\tGROUP BY ha.idQuartier\n\t\t\t\tORDER BY ha.idQuartier\n\t\t\t\t"; $res = $c->execute($requestNeigh); $adressesByQuartier = array(); $i = 0; while ($fetch = mysql_fetch_assoc($res)) { $quartierArray = array(); $quartierArray['nom'] = $fetch['nomQuartier']; $quartierArray['codepostal'] = $fetch['codepostal']; $quartierArray['ville'] = $fetch['nomVille']; $quartierArray['pays'] = $fetch['nomPays']; $reqidAdresse = "\n\t\t\t\t\tSELECT idAdresse, numero\n\t\t\t\t\tFROM historiqueAdresse\n\t\t\t\t\tWHERE idRue = " . $fetch['idQuartier'] . "\n\t\t\t\t\t\t\t"; $resIdArray = $c->execute($reqidAdresse); $adresseArray = array(); $indice = ''; while ($fetchAdresse = mysql_fetch_assoc($resIdArray)) { $idAdresse = $fetchAdresse['idAdresse']; $adresseArray['numero'] = $fetchAdresse['numero']; $requeteEvenements = ' SELECT hE.idEvenement, hE.titre, hE.idSource, hE.idTypeStructure, hE.idTypeEvenement, hE.description, hE.dateDebut, hE.dateFin, tE.nom AS nomTypeEvenement, tS.nom AS nomTypeStructure, s.nom AS nomSource, tE.groupe, hE.ISMH , hE.MH, date_format(hE.dateCreationEvenement,"' . _("%e/%m/%Y - %kh%i") . '") as dateCreationEvenement, hE.isDateDebutEnviron as isDateDebutEnviron, hE.numeroArchive as numeroArchive, ae.idAdresse, ha.idVille FROM evenements he2 LEFT JOIN _adresseEvenement ae on ae.idEvenement = he2.idEvenement LEFT JOIN _evenementEvenement ee on ee.idEvenement = ae.idEvenement LEFT JOIN evenements hE on hE.idEvenement = ee.idEvenementAssocie LEFT JOIN source s ON s.idSource = hE.idSource LEFT JOIN typeStructure tS ON tS.idTypeStructure = hE.idTypeStructure LEFT JOIN typeEvenement tE ON tE.idTypeEvenement = hE.idTypeEvenement LEFT JOIN historiqueAdresse ha on ha.idAdresse = ae.idAdresse WHERE ha.idAdresse=' . $idAdresse . ' ORDER BY hE.idEvenement DESC '; $resEvenements = $c->execute($requeteEvenements); $evenementArray = array(); $k = 0; while ($fetchEvenement = mysql_fetch_assoc($resEvenements)) { $labelEvenement = 'evenement' . $k++; $evenementArray[$labelEvenement] = array('titre' => $fetchEvenement['titre'], 'source' => $fetchEvenement['nomSource'], 'dateDebut' => $fetchEvenement['dateDebut'], 'dateFin' => $fetchEvenement['dateFin'], 'nomTypeEvenement' => $fetchEvenement['nomTypeEvenement'], 'nomTypeStructure' => $fetchEvenement['nomTypeStructure'], 'ISMH' => $fetchEvenement['ISMH'], 'MH' => $fetchEvenement['MH'], 'dateCreationEvenement' => $fetchEvenement['dateCreationEvenement']); } $adresseArray['evenements'] = $evenementArray; } $quartierArray['adresses'] = $adresseArray; $indice = 'quartier' . $i++; $adressesByQuartier[$indice] = $quartierArray; } $util = new Utils(); $c->complexArrayToXML($adressesByQuartier, "xml/adressesQuartier.xml", 'AdresseQuartier', 'adresse'); $c->complexArrayToCSV($adressesByQuartier, "csv/adressesQuartier.csv", 'AdresseQuartier', 'adresse'); }