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()
{
$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()
{
$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');
}
