PageRenderTime 47ms CodeModel.GetById 17ms RepoModel.GetById 0ms app.codeStats 1ms

/bdd/migre.php

http://gevu.googlecode.com/
PHP | 1394 lines | 1112 code | 133 blank | 149 comment | 83 complexity | 1948bc906aa01b1f1e6fa8068a8a46b2 MD5 | raw file
Possible License(s): MPL-2.0-no-copyleft-exception

Large files files are truncated, but you can click here to view the full file

  1. <?php
  2. //connexion ? l'ancienne base
  3. $ldb = mysql_connect("localhost", "root", "")
  4. or die("Impossible de se connecter : " . mysql_error());
  5. mysql_select_db('gevu_solus');
  6. $pdo = new PDO("mysql:host=localhost;port='3306';dbname=gevu_solus", 'root', '');
  7. updLieuxHierarchie(1, 10, 0, 1, 0);
  8. $dbN = "gevu_solus";
  9. //DeleteLieu(500);
  10. $dbO = "gevu_alceane";
  11. //migreBase($dbN, $dbO);
  12. //ajusteBase(1);
  13. $dbO = "gevu_pc1";
  14. //migreBase($dbN, $dbO);
  15. $dbO = "gevu_pc2";
  16. //migreBase($dbN, $dbO);
  17. $dbO = "gevu_trouville";
  18. $idInstant = 4;
  19. //migreBase($dbN, $dbO);
  20. //ajusteBase(1);
  21. $dbO = "gevutrouville";
  22. $idInstant = 5;
  23. //migreBase($dbN, $dbO, $idInstant);
  24. $dbO = "gevutrouville1";
  25. $idInstant = 6;
  26. //migreBase($dbN, $dbO, $idInstant);
  27. $dbO = "gevutrouville2";
  28. $idInstant = 7;
  29. //migreBase($dbN, $dbO, $idInstant);
  30. $dbO = "gevutrouvillevoirie";
  31. $idInstant = 8;
  32. //migreBase($dbN, $dbO, $idInstant);
  33. /*
  34. $dbO = "mundimuzgevu1";
  35. $idInstant = 9;
  36. migreBase($dbN, $dbO, $idInstant);
  37. $dbO = "mundimuzgevu2";
  38. $idInstant = 10;
  39. migreBase($dbN, $dbO, $idInstant);
  40. $dbO = "mundimuzgevu3";
  41. $idInstant = 11;
  42. migreBase($dbN, $dbO, $idInstant);
  43. $dbO = "mundimuzgevu4";
  44. $idInstant = 12;
  45. migreBase($dbN, $dbO, $idInstant);
  46. */
  47. function ModifBranche($r1, $oR, $idInstant){
  48. echo "ModifBranche: ".str_repeat("- ", $r1['niv'])." : ".$r1['lib']."<br/>";
  49. //on ajoute ? la référence
  50. //les éléments lié au lieu ayant le m?me nom que la référence
  51. if($r1['nbLieux']>0){
  52. //récup?ration des éléments liés
  53. $sql="SELECT CONCAT(e.ids, eR.ids) ids
  54. FROM (SELECT GROUP_CONCAT(e.id_lieu) ids
  55. FROM gevu_lieux e
  56. WHERE e.id_instant = ".$r1['id_instant']." AND e.lft > ".$r1['lft']." AND e.rgt < ".$r1['rgt'].") e
  57. , (SELECT GROUP_CONCAT(e.id_lieu) ids
  58. FROM gevu_lieux e
  59. WHERE e.id_instant = ".$idInstant." AND e.lft > ".$oR['lft']." AND e.rgt < ".$oR['rgt'].") eR";
  60. //on récup?re les enfants de reférence et ceux ? comparer pour le niveau suivant
  61. $rs2 = mysql_query($sql);
  62. $r2=mysql_fetch_array($rs2);
  63. if($r2["ids"]=="")$ids=-1;else $ids = $r2["ids"];
  64. if(substr($r2["ids"],-1)==",")$ids=$r2["ids"]."-1";
  65. CompareBranche($ids.",-2", $idInstant, $oR['id_lieu']);
  66. }
  67. //si seulement l'identifiant de lieu est différent
  68. //on met ? jour les éléments liés
  69. //et on supprime le lieu
  70. if($r1['id_lieu']!= $oR['id_lieu']){
  71. if($r1['nbBat']>0){
  72. $sql="UPDATE gevu_batiments SET id_lieu=".$oR['id_lieu']." WHERE id_lieu = ".$r1['id_lieu'];
  73. mysql_query($sql);
  74. }
  75. if($r1['nbDiag']>0){
  76. $sql="UPDATE gevu_diagnostics SET id_lieu=".$oR['id_lieu']." WHERE id_lieu = ".$r1['id_lieu'];
  77. mysql_query($sql);
  78. }
  79. if($r1['nbDiagVoi']>0){
  80. $sql="UPDATE gevu_diagnosticsxvoirie SET id_lieu=".$oR['id_lieu']." WHERE id_lieu = ".$r1['id_lieu'];
  81. mysql_query($sql);
  82. }
  83. if($r1['nbDoc']>0){
  84. $sql="UPDATE gevu_docsxlieux SET id_lieu=".$oR['id_lieu']." WHERE id_lieu = ".$r1['id_lieu'];
  85. mysql_query($sql);
  86. }
  87. if($r1['nbEsp']>0){
  88. $sql="UPDATE gevu_espaces SET id_lieu=".$oR['id_lieu']." WHERE id_lieu = ".$r1['id_lieu'];
  89. mysql_query($sql);
  90. }
  91. if($r1['nbEspEx']>0){
  92. $sql="UPDATE gevu_espacesxexterieurs SET id_lieu=".$oR['id_lieu']." WHERE id_lieu = ".$r1['id_lieu'];
  93. mysql_query($sql);
  94. }
  95. if($r1['nbEspIn']>0){
  96. $sql="UPDATE gevu_espacesxinterieurs SET id_lieu=".$oR['id_lieu']." WHERE id_lieu = ".$r1['id_lieu'];
  97. mysql_query($sql);
  98. }
  99. if($r1['nbEtab']>0){
  100. $sql="UPDATE gevu_etablissements SET id_lieu=".$oR['id_lieu']." WHERE id_lieu = ".$r1['id_lieu'];
  101. mysql_query($sql);
  102. }
  103. if($r1['nbGeoR']>0){
  104. $sql="UPDATE gevu_georss SET id_lieu=".$oR['id_lieu']." WHERE id_lieu = ".$r1['id_lieu'];
  105. mysql_query($sql);
  106. }
  107. if($r1['nbGeo']>0){
  108. $sql="UPDATE gevu_geos SET id_lieu=".$oR['id_lieu']." WHERE id_lieu = ".$r1['id_lieu'];
  109. mysql_query($sql);
  110. }
  111. if($r1['nbNiv']>0){
  112. $sql="UPDATE gevu_niveaux SET id_lieu=".$oR['id_lieu']." WHERE id_lieu = ".$r1['id_lieu'];
  113. mysql_query($sql);
  114. }
  115. if($r1['nbObjExt']>0){
  116. $sql="UPDATE gevu_objetsxexterieurs SET id_lieu=".$oR['id_lieu']." WHERE id_lieu = ".$r1['id_lieu'];
  117. mysql_query($sql);
  118. }
  119. if($r1['nbObjInt']>0){
  120. $sql="UPDATE gevu_objetsxinterieurs SET id_lieu=".$oR['id_lieu']." WHERE id_lieu = ".$r1['id_lieu'];
  121. mysql_query($sql);
  122. }
  123. if($r1['nbObjVoi']>0){
  124. $sql="UPDATE gevu_objetsxvoiries SET id_lieu=".$oR['id_lieu']." WHERE id_lieu = ".$r1['id_lieu'];
  125. mysql_query($sql);
  126. }
  127. if($r1['nbObs']>0){
  128. $sql="UPDATE gevu_observations SET id_lieu=".$oR['id_lieu']." WHERE id_lieu = ".$r1['id_lieu'];
  129. mysql_query($sql);
  130. }
  131. if($r1['nbPar']>0){
  132. $sql="UPDATE gevu_parcelles SET id_lieu=".$oR['id_lieu']." WHERE id_lieu = ".$r1['id_lieu'];
  133. mysql_query($sql);
  134. }
  135. if($r1['nbPro']>0){
  136. $sql="UPDATE gevu_problemes SET id_lieu=".$oR['id_lieu']." WHERE id_lieu = ".$r1['id_lieu'];
  137. mysql_query($sql);
  138. }
  139. //on supprime le lieu
  140. DeleteLieu($r1['id_lieu'], $r1['lft'], $r1['rgt'], $r1['id_instant']);
  141. }
  142. }
  143. function ChangeBranche($id_lieuSrc, $lftDst, $idInstantDst, $niv=0){
  144. //http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html
  145. global $pdo;
  146. $stmt = $pdo->prepare("CALL MoveNode(p1,p2,p3)");
  147. $stmt->bindParam('p1', $id_lieuSrc, PDO::PARAM_INT);
  148. $stmt->bindParam('p2', $lftDst, PDO::PARAM_INT);
  149. $stmt->bindParam('p3', $idInstantDst, PDO::PARAM_INT);
  150. $stmt->execute();
  151. /*
  152. $sql="UPDATE gevu_lieux SET rgt = rgt + 2 WHERE rgt > ".$lftDst." AND id_instant=".$idInstantDst;
  153. $result = mysql_query($sql);
  154. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  155. //echo "ChangeBranche rgt = rgt + 2: ".$id_lieuSrc." ".mysql_affected_rows()."<br/>";
  156. $sql="UPDATE gevu_lieux SET lft = lft + 2 WHERE lft > ".$lftDst." AND id_instant=".$idInstantDst;
  157. $result = mysql_query($sql);
  158. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  159. //echo "ChangeBranche lft = lft + 2 : ".$id_lieuSrc." ".mysql_affected_rows()."<br/>";
  160. $sql="UPDATE gevu_lieux SET lft = ".($lftDst+1).", rgt = ".($lftDst+2).", id_instant=".$idInstantDst." WHERE id_lieu = ".$id_lieuSrc."";
  161. $result = mysql_query($sql);
  162. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  163. //echo "ChangeBranche: ".str_repeat("- ", $niv)." < ".$id_lieuSrc." < : ".mysql_affected_rows()."<br/>";
  164. */
  165. //Met ? jour les enfants du lieu ? ajouter
  166. $sql="SELECT e.id_lieu, e.niv
  167. FROM gevu_lieux e
  168. WHERE e.lieu_parent = $id_lieuSrc";
  169. $result = mysql_query($sql);
  170. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  171. //echo "ChangeBranche: ".str_repeat("- ", $niv)." > ".$id_lieuSrc." > : ".mysql_affected_rows()."<br/>";
  172. while($r=mysql_fetch_array($result)){
  173. //on ajoute la branche ? la référence
  174. ChangeBranche($r['id_lieu'], $lftDst+1, $idInstantDst, $r['niv']);
  175. }
  176. }
  177. function DeleteLieu($id_lieu, $lft=-1, $rgt=-1, $idInstant=-1){
  178. $sql="DELETE FROM gevu_batiments WHERE id_lieu = ".$id_lieu;
  179. $result = mysql_query($sql);
  180. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  181. echo "DeleteLieu: ".$id_lieu." ".mysql_affected_rows()."<br/>";
  182. $sql="DELETE FROM gevu_diagnostics WHERE id_lieu = ".$id_lieu;
  183. $result = mysql_query($sql);
  184. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  185. echo "DeleteLieu: ".$id_lieu." ".mysql_affected_rows()."<br/>";
  186. $sql="DELETE FROM gevu_diagnosticsxvoirie WHERE id_lieu = ".$id_lieu;
  187. $result = mysql_query($sql);
  188. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  189. echo "DeleteLieu: ".$id_lieu." ".mysql_affected_rows()."<br/>";
  190. $sql="DELETE FROM gevu_docsxlieux WHERE id_lieu = ".$id_lieu;
  191. $result = mysql_query($sql);
  192. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  193. echo "DeleteLieu: ".$id_lieu." ".mysql_affected_rows()."<br/>";
  194. $sql="DELETE FROM gevu_espaces WHERE id_lieu = ".$id_lieu;
  195. $result = mysql_query($sql);
  196. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  197. echo "DeleteLieu: ".$id_lieu." ".mysql_affected_rows()."<br/>";
  198. $sql="DELETE FROM gevu_espacesxexterieurs WHERE id_lieu = ".$id_lieu;
  199. $result = mysql_query($sql);
  200. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  201. echo "DeleteLieu: ".$id_lieu." ".mysql_affected_rows()."<br/>";
  202. $sql="DELETE FROM gevu_espacesxinterieurs WHERE id_lieu = ".$id_lieu;
  203. $result = mysql_query($sql);
  204. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  205. echo "DeleteLieu: ".$id_lieu." ".mysql_affected_rows()."<br/>";
  206. $sql="DELETE FROM gevu_etablissements WHERE id_lieu = ".$id_lieu;
  207. $result = mysql_query($sql);
  208. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  209. echo "DeleteLieu: ".$id_lieu." ".mysql_affected_rows()."<br/>";
  210. $sql="DELETE FROM gevu_georss WHERE id_lieu = ".$id_lieu;
  211. $result = mysql_query($sql);
  212. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  213. echo "DeleteLieu: ".$id_lieu." ".mysql_affected_rows()."<br/>";
  214. $sql="DELETE FROM gevu_geos WHERE id_lieu = ".$id_lieu;
  215. $result = mysql_query($sql);
  216. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  217. echo "DeleteLieu: ".$id_lieu." ".mysql_affected_rows()."<br/>";
  218. $sql="DELETE FROM gevu_niveaux WHERE id_lieu = ".$id_lieu;
  219. $result = mysql_query($sql);
  220. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  221. echo "DeleteLieu: ".$id_lieu." ".mysql_affected_rows()."<br/>";
  222. $sql="DELETE FROM gevu_objetsxexterieurs WHERE id_lieu = ".$id_lieu;
  223. $result = mysql_query($sql);
  224. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  225. echo "DeleteLieu: ".$id_lieu." ".mysql_affected_rows()."<br/>";
  226. $sql="DELETE FROM gevu_objetsxinterieurs WHERE id_lieu = ".$id_lieu;
  227. $result = mysql_query($sql);
  228. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  229. echo "DeleteLieu: ".$id_lieu." ".mysql_affected_rows()."<br/>";
  230. $sql="DELETE FROM gevu_objetsxvoiries WHERE id_lieu = ".$id_lieu;
  231. $result = mysql_query($sql);
  232. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  233. echo "DeleteLieu: ".$id_lieu." ".mysql_affected_rows()."<br/>";
  234. $sql="DELETE FROM gevu_observations WHERE id_lieu = ".$id_lieu;
  235. $result = mysql_query($sql);
  236. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  237. echo "DeleteLieu: ".$id_lieu." ".mysql_affected_rows()."<br/>";
  238. $sql="DELETE FROM gevu_parcelles WHERE id_lieu = ".$id_lieu;
  239. $result = mysql_query($sql);
  240. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  241. echo "DeleteLieu: ".$id_lieu." ".mysql_affected_rows()."<br/>";
  242. $sql="DELETE FROM gevu_problemes WHERE id_lieu = ".$id_lieu;
  243. $result = mysql_query($sql);
  244. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  245. echo "DeleteLieu: ".$id_lieu." ".mysql_affected_rows()."<br/>";
  246. if($idInstant==-1){
  247. $sql="SELECT lft, rgt, id_instant FROM gevu_lieux WHERE id_lieu = ".$id_lieu;
  248. $result = mysql_query($sql);
  249. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  250. $r=mysql_fetch_array($result);
  251. $lft = $r['lft'];
  252. $rgt = $r['rgt'];
  253. $idInstant = $r['id_instant'];
  254. }
  255. /*
  256. global $pdo;
  257. $p1 ='PROMOTE';
  258. $stmt = $pdo->prepare("CALL DeleteNestedSetNode(p1,p2)");
  259. $stmt->bindParam('p1', $p1, PDO::PARAM_STR);
  260. $stmt->bindParam('p2', $id_lieu, PDO::PARAM_INT);
  261. $stmt->execute();
  262. */
  263. $sql="DELETE FROM gevu_lieux WHERE id_lieu = ".$id_lieu;
  264. $result = mysql_query($sql);
  265. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  266. echo "DeleteLieu: ".$id_lieu." ".mysql_affected_rows()."<br/>";
  267. //mise ? jour de la hiérarchie
  268. $sql="UPDATE gevu_lieux SET rgt = rgt - 1, lft = lft - 1 WHERE id_instant = $idInstant AND lft BETWEEN $lft AND ".$rgt;
  269. $result = mysql_query($sql);
  270. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  271. $sql="UPDATE gevu_lieux SET rgt = rgt - 2 WHERE id_instant = $idInstant AND rgt > ".$rgt;
  272. $result = mysql_query($sql);
  273. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  274. $sql="UPDATE gevu_lieux SET lft = lft - 2 WHERE id_instant = $idInstant AND lft > ".$rgt;
  275. $result = mysql_query($sql);
  276. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  277. //supprime tous les enfants
  278. $sql="SELECT id_lieu, lft, rgt, id_instant FROM gevu_lieux WHERE lieu_parent = ".$id_lieu;
  279. $result = mysql_query($sql);
  280. if (!$result)echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  281. while($r=mysql_fetch_array($result)){
  282. DeleteLieu($r['id_lieu'], $r['lft'], $r['rgt'], $idInstant);
  283. }
  284. }
  285. function migreBase($dbN, $dbO){
  286. //création d'un instant
  287. $sql = "INSERT INTO $dbN.gevu_instants (`maintenant`, `ici`, `id_exi`, `nom`) VALUES
  288. (now(), '-1', 1, 'Migre_GEVU $dbO')";
  289. $result = mysql_query($sql);
  290. if (!$result) die('Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>');
  291. $idInstant = mysql_insert_id();
  292. echo "$dbO Instant : ".mysql_affected_rows()."<br/>";
  293. //mise ? jour de l'instant de l'univers
  294. //récup?re les lieux
  295. $sql = "UPDATE gevu_lieux SET id_instant=".$idInstant." WHERE id_lieu = 1";
  296. $result = mysql_query($sql);
  297. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  298. //récup?re les propriété de l'univers
  299. $sql = "SELECT id_lieu, lft, rgt FROM $dbN.gevu_lieux WHERE id_lieu = 1";
  300. $result = mysql_query($sql);
  301. if (!$result) die('Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>');
  302. $r=mysql_fetch_array($result);
  303. echo "$dbO univers : ".$r['lft']." - ".$r['rgt']."<br/>";
  304. //LIEUX
  305. setLieuxHierarchie($r['id_lieu'], $dbN, $dbO, $idInstant, 5479, $r['lft'], $r['rgt']);
  306. /*pour vérifier
  307. SELECT CONCAT( REPEAT(' ', COUNT(pl.lib) - 1), l.lib) AS name
  308. FROM gevu_lieux AS l,
  309. gevu_lieux AS pl
  310. WHERE l.lft BETWEEN pl.lft AND pl.rgt
  311. GROUP BY l.lib
  312. ORDER BY l.lft
  313. */
  314. //on met ? jour le droite de l'univers
  315. $sql = "SELECT MAX(rgt)+1 m FROM gevu_lieux";
  316. $result = mysql_query($sql);
  317. $r=mysql_fetch_array($result);
  318. $sql = "UPDATE gevu_lieux SET rgt=".$r['m']." WHERE id_lieu = 1";
  319. $result = mysql_query($sql);
  320. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  321. //DIAGNOSTICS
  322. //on ne conserve que les réponses 'non' et 'sous réserve'
  323. // pour les questions qui ne sont pas interméfiaire
  324. $sql = "INSERT INTO $dbN.gevu_diagnostics
  325. (id_critere, id_reponse, id_instant, id_lieu, id_donnee, maj)
  326. SELECT c.id_critere, fdc1.valeur, $idInstant, l.id_lieu, fd.id_donnee, fd.date
  327. FROM $dbO.spip_forms_donnees fd
  328. INNER JOIN $dbO.spip_forms_donnees_champs fdc1 ON fdc1.id_donnee = fd.id_donnee
  329. AND fdc1.champ = 'mot_1'
  330. INNER JOIN $dbO.spip_forms_donnees_champs fdc2 ON fdc2.id_donnee = fd.id_donnee
  331. AND fdc2.champ = 'ligne_1'
  332. INNER JOIN $dbO.spip_forms_donnees_articles fda ON fda.id_donnee = fd.id_donnee
  333. INNER JOIN $dbO.spip_articles a ON a.id_article = fda.id_article
  334. INNER JOIN $dbN.gevu_lieux l ON l.id_rubrique = a.id_rubrique AND l.id_instant = $idInstant
  335. LEFT JOIN $dbN.gevu_criteres c ON c.ref = fdc2.valeur
  336. WHERE fd.id_form =59
  337. GROUP BY fd.id_donnee";
  338. $result = mysql_query($sql);
  339. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  340. echo "$dbO DIAGNOSTICS : ".mysql_affected_rows()."<br/>";
  341. //PROBLEMES
  342. //on ne conserve que le champ photo
  343. //les champs 'fichier' et 'doc' devront ?tre ajouter ? la table gevu_doc
  344. $sql = "INSERT INTO $dbN.gevu_problemes
  345. (id_lieu, id_critere, num_marker, mesure, observations, fichier, doc, id_instant, id_donnee, maj)
  346. SELECT l.id_lieu
  347. , c.id_critere
  348. , fdc1.valeur
  349. , fdc11.valeur
  350. , fdc3.valeur
  351. , fdc4.valeur
  352. , fdc6.valeur
  353. , $idInstant
  354. , fd.id_donnee, fd.date
  355. FROM $dbO.spip_forms_donnees fd
  356. INNER JOIN $dbO.spip_forms_donnees_articles fda ON fd.id_donnee = fda.id_donnee
  357. INNER JOIN $dbO.spip_articles a ON a.id_article = fda.id_article
  358. INNER JOIN $dbN.gevu_lieux l ON l.id_rubrique = a.id_rubrique AND l.id_instant = $idInstant
  359. LEFT JOIN $dbO.spip_forms_donnees_champs fdc1 ON fdc1.id_donnee = fd.id_donnee
  360. AND fdc1.champ = 'ligne_1'
  361. LEFT JOIN $dbO.spip_forms_donnees_champs fdc11 ON fdc11.id_donnee = fd.id_donnee
  362. AND fdc11.champ = 'ligne_2'
  363. LEFT JOIN $dbO.spip_forms_donnees_champs fdc ON fdc.id_donnee = fd.id_donnee
  364. AND fdc.champ = 'ligne_3'
  365. LEFT JOIN $dbO.spip_forms_donnees_champs fdc3 ON fdc3.id_donnee = fd.id_donnee
  366. AND fdc3.champ = 'texte_1'
  367. LEFT JOIN $dbO.spip_forms_donnees_champs fdc2 ON fdc2.id_donnee = fd.id_donnee
  368. AND fdc2.champ = 'mot_1'
  369. LEFT JOIN $dbO.spip_forms_donnees_champs fdc4 ON fdc4.id_donnee = fd.id_donnee
  370. AND fdc4.champ = 'fichier_1'
  371. LEFT JOIN $dbO.spip_forms_donnees_champs fdc5 ON fdc5.id_donnee = fd.id_donnee
  372. AND fdc5.champ = 'ligne_5'
  373. LEFT JOIN $dbO.spip_forms_donnees_champs fdc6 ON fdc6.id_donnee = fd.id_donnee
  374. AND fdc6.champ = 'ligne_4'
  375. LEFT JOIN $dbN.gevu_criteres c ON c.ref = fdc.valeur
  376. WHERE fd.id_form =60
  377. GROUP BY fd.id_donnee";
  378. $result = mysql_query($sql);
  379. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  380. echo "$dbO PROBLEMES : ".mysql_affected_rows()."<br/>";
  381. //GEOGRAPHIE
  382. $sql = "INSERT INTO $dbN.gevu_geos
  383. (id_lieu, id_instant, lat, lng, zoom_min, zoom_max, adresse, id_type_carte, kml, id_donnee, maj)
  384. SELECT l.id_lieu
  385. , $idInstant
  386. , fdc1.valeur
  387. , fdc2.valeur
  388. , fdc3.valeur
  389. , fdc4.valeur
  390. , fdc5.valeur
  391. , fdc6.valeur
  392. , fdc7.valeur
  393. , fd.id_donnee, fd.date
  394. FROM $dbO.spip_forms_donnees fd
  395. INNER JOIN $dbO.spip_forms_donnees_articles fda ON fd.id_donnee = fda.id_donnee
  396. INNER JOIN $dbO.spip_articles a ON a.id_article = fda.id_article
  397. INNER JOIN $dbN.gevu_lieux l ON l.id_rubrique = a.id_rubrique AND l.id_instant = $idInstant
  398. LEFT JOIN $dbO.spip_forms_donnees_champs fdc1 ON fdc1.id_donnee = fd.id_donnee
  399. AND fdc1.champ = 'ligne_1'
  400. LEFT JOIN $dbO.spip_forms_donnees_champs fdc2 ON fdc2.id_donnee = fd.id_donnee
  401. AND fdc2.champ = 'ligne_2'
  402. LEFT JOIN $dbO.spip_forms_donnees_champs fdc3 ON fdc3.id_donnee = fd.id_donnee
  403. AND fdc3.champ = 'ligne_3'
  404. LEFT JOIN $dbO.spip_forms_donnees_champs fdc4 ON fdc4.id_donnee = fd.id_donnee
  405. AND fdc4.champ = 'ligne_4'
  406. LEFT JOIN $dbO.spip_forms_donnees_champs fdc5 ON fdc5.id_donnee = fd.id_donnee
  407. AND fdc5.champ = 'ligne_7'
  408. LEFT JOIN $dbO.spip_forms_donnees_champs fdc6 ON fdc6.id_donnee = fd.id_donnee
  409. AND fdc6.champ = 'mot_1'
  410. LEFT JOIN $dbO.spip_forms_donnees_champs fdc7 ON fdc7.id_donnee = fd.id_donnee
  411. AND fdc7.champ = 'texte_1'
  412. WHERE fd.id_form = 1
  413. GROUP BY fd.id_donnee";
  414. $result = mysql_query($sql);
  415. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  416. echo "$dbO GEOGRAPHIE : ".mysql_affected_rows()."<br/>";
  417. //GEORSS
  418. $sql = "INSERT INTO $dbN.gevu_georss
  419. (id_lieu, id_instant, url, id_donnee, maj)
  420. SELECT l.id_lieu
  421. , $idInstant
  422. , fdc1.valeur
  423. , fd.id_donnee, fd.date
  424. FROM $dbO.spip_forms_donnees fd
  425. INNER JOIN $dbO.spip_forms_donnees_articles fda ON fd.id_donnee = fda.id_donnee
  426. INNER JOIN $dbO.spip_articles a ON a.id_article = fda.id_article
  427. INNER JOIN $dbN.gevu_lieux l ON l.id_rubrique = a.id_rubrique AND l.id_instant = $idInstant
  428. LEFT JOIN $dbO.spip_forms_donnees_champs fdc1 ON fdc1.id_donnee = fd.id_donnee
  429. AND fdc1.champ = 'url_1'
  430. WHERE fd.id_form = 81
  431. GROUP BY fd.id_donnee";
  432. $result = mysql_query($sql);
  433. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  434. echo "$dbO GEORSS : ".mysql_affected_rows()."<br/>";
  435. //NIVEAU
  436. $sql = "INSERT INTO $dbN.gevu_niveaux
  437. (id_lieu, id_instant, nom, ref, id_reponse_1, id_reponse_2, id_reponse_3, id_donnee, maj)
  438. SELECT l.id_lieu
  439. , $idInstant
  440. , fdc1.valeur
  441. , fdc2.valeur
  442. , fdc3.valeur
  443. , fdc4.valeur
  444. , fdc5.valeur
  445. , fd.id_donnee, fd.date
  446. FROM $dbO.spip_forms_donnees fd
  447. INNER JOIN $dbO.spip_forms_donnees_articles fda ON fd.id_donnee = fda.id_donnee
  448. INNER JOIN $dbO.spip_articles a ON a.id_article = fda.id_article
  449. INNER JOIN $dbN.gevu_lieux l ON l.id_rubrique = a.id_rubrique AND l.id_instant = $idInstant
  450. LEFT JOIN $dbO.spip_forms_donnees_champs fdc1 ON fdc1.id_donnee = fd.id_donnee
  451. AND fdc1.champ = 'ligne_1'
  452. LEFT JOIN $dbO.spip_forms_donnees_champs fdc2 ON fdc2.id_donnee = fd.id_donnee
  453. AND fdc2.champ = 'ligne_2'
  454. LEFT JOIN $dbO.spip_forms_donnees_champs fdc3 ON fdc3.id_donnee = fd.id_donnee
  455. AND fdc3.champ = 'mot_1'
  456. LEFT JOIN $dbO.spip_forms_donnees_champs fdc4 ON fdc4.id_donnee = fd.id_donnee
  457. AND fdc4.champ = 'mot_2'
  458. LEFT JOIN $dbO.spip_forms_donnees_champs fdc5 ON fdc5.id_donnee = fd.id_donnee
  459. AND fdc5.champ = 'mot_3'
  460. WHERE fd.id_form = 35
  461. GROUP BY fd.id_donnee";
  462. $result = mysql_query($sql);
  463. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  464. echo "$dbO NIVEAU : ".mysql_affected_rows()."<br/>";
  465. //BATIMENT
  466. //les contacts devront ?tre retraités
  467. //(53, 'ligne_10', 12, 'Coordonnées du gardien'
  468. //les horaires devront ?tre retraité
  469. $sql = "INSERT INTO $dbN.gevu_batiments
  470. (id_lieu, id_instant, nom, ref, adresse, commune, pays, code_postal
  471. , contact_proprietaire, contact_delegataire, contact_gardien
  472. , horaires_gardien, horaires_batiment
  473. , superficie_parcelle, superficie_batiment
  474. , date_achevement, date_depot_permis, date_reha
  475. , reponse_1
  476. , reponse_2
  477. , reponse_3
  478. , reponse_4
  479. , reponse_5
  480. , reponse_6
  481. , reponse_7
  482. , reponse_8
  483. , reponse_9
  484. , reponse_10
  485. , reponse_11
  486. , reponse_12
  487. , reponse_13
  488. , reponse_14
  489. , reponse_15
  490. , id_donnee, maj)
  491. SELECT l.id_lieu
  492. , $idInstant
  493. , fdc1.valeur
  494. , fdc2.valeur
  495. , fdc3.valeur
  496. , fdc4.valeur
  497. , fdc5.valeur
  498. , fdc6.valeur
  499. , fdc7.valeur
  500. , fdc8.valeur
  501. , fdc9.valeur
  502. , fdc10.valeur
  503. , fdc11.valeur
  504. , fdc12.valeur
  505. , fdc13.valeur
  506. , fdc14.valeur
  507. , fdc15.valeur
  508. , fdc16.valeur
  509. , fdc17.valeur
  510. , fdc18.valeur
  511. , fdc19.valeur
  512. , fdc20.valeur
  513. , fdc21.valeur
  514. , fdc22.valeur
  515. , fdc23.valeur
  516. , fdc24.valeur
  517. , fdc25.valeur
  518. , fdc26.valeur
  519. , fdc27.valeur
  520. , fdc28.valeur
  521. , fdc29.valeur
  522. , fdc30.valeur
  523. , fdc31.valeur
  524. , fd.id_donnee, fd.date
  525. FROM $dbO.spip_forms_donnees fd
  526. INNER JOIN $dbO.spip_forms_donnees_articles fda ON fd.id_donnee = fda.id_donnee
  527. INNER JOIN $dbO.spip_articles a ON a.id_article = fda.id_article
  528. INNER JOIN $dbN.gevu_lieux l ON l.id_rubrique = a.id_rubrique AND l.id_instant = $idInstant
  529. LEFT JOIN $dbO.spip_forms_donnees_champs fdc1 ON fdc1.id_donnee = fd.id_donnee
  530. AND fdc1.champ = 'ligne_1'
  531. LEFT JOIN $dbO.spip_forms_donnees_champs fdc2 ON fdc2.id_donnee = fd.id_donnee
  532. AND fdc2.champ = 'ligne_2'
  533. LEFT JOIN $dbO.spip_forms_donnees_champs fdc3 ON fdc3.id_donnee = fd.id_donnee
  534. AND fdc3.champ = 'ligne_3'
  535. LEFT JOIN $dbO.spip_forms_donnees_champs fdc4 ON fdc4.id_donnee = fd.id_donnee
  536. AND fdc4.champ = 'ligne_4'
  537. LEFT JOIN $dbO.spip_forms_donnees_champs fdc5 ON fdc5.id_donnee = fd.id_donnee
  538. AND fdc5.champ = 'ligne_5'
  539. LEFT JOIN $dbO.spip_forms_donnees_champs fdc6 ON fdc6.id_donnee = fd.id_donnee
  540. AND fdc6.champ = 'code_postal_1'
  541. LEFT JOIN $dbO.spip_forms_donnees_champs fdc7 ON fdc7.id_donnee = fd.id_donnee
  542. AND fdc7.champ = 'ligne_6'
  543. LEFT JOIN $dbO.spip_forms_donnees_champs fdc8 ON fdc8.id_donnee = fd.id_donnee
  544. AND fdc8.champ = 'ligne_7'
  545. LEFT JOIN $dbO.spip_forms_donnees_champs fdc9 ON fdc9.id_donnee = fd.id_donnee
  546. AND fdc9.champ = 'ligne_10'
  547. LEFT JOIN $dbO.spip_forms_donnees_champs fdc10 ON fdc10.id_donnee = fd.id_donnee
  548. AND fdc10.champ = 'ligne_9'
  549. LEFT JOIN $dbO.spip_forms_donnees_champs fdc11 ON fdc11.id_donnee = fd.id_donnee
  550. AND fdc11.champ = 'ligne_11'
  551. LEFT JOIN $dbO.spip_forms_donnees_champs fdc12 ON fdc12.id_donnee = fd.id_donnee
  552. AND fdc12.champ = 'ligne_13'
  553. LEFT JOIN $dbO.spip_forms_donnees_champs fdc13 ON fdc13.id_donnee = fd.id_donnee
  554. AND fdc13.champ = 'ligne_12'
  555. LEFT JOIN $dbO.spip_forms_donnees_champs fdc14 ON fdc14.id_donnee = fd.id_donnee
  556. AND fdc14.champ = 'ligne_14'
  557. LEFT JOIN $dbO.spip_forms_donnees_champs fdc15 ON fdc15.id_donnee = fd.id_donnee
  558. AND fdc15.champ = 'ligne_15'
  559. LEFT JOIN $dbO.spip_forms_donnees_champs fdc16 ON fdc16.id_donnee = fd.id_donnee
  560. AND fdc16.champ = 'ligne_16'
  561. LEFT JOIN $dbO.spip_forms_donnees_champs fdc17 ON fdc17.id_donnee = fd.id_donnee
  562. AND fdc17.champ = 'mot_4'
  563. LEFT JOIN $dbO.spip_forms_donnees_champs fdc18 ON fdc18.id_donnee = fd.id_donnee
  564. AND fdc18.champ = 'mot_5'
  565. LEFT JOIN $dbO.spip_forms_donnees_champs fdc19 ON fdc19.id_donnee = fd.id_donnee
  566. AND fdc19.champ = 'mot_6'
  567. LEFT JOIN $dbO.spip_forms_donnees_champs fdc20 ON fdc20.id_donnee = fd.id_donnee
  568. AND fdc20.champ = 'mot_7'
  569. LEFT JOIN $dbO.spip_forms_donnees_champs fdc21 ON fdc21.id_donnee = fd.id_donnee
  570. AND fdc21.champ = 'mot_9'
  571. LEFT JOIN $dbO.spip_forms_donnees_champs fdc22 ON fdc22.id_donnee = fd.id_donnee
  572. AND fdc22.champ = 'mot_10'
  573. LEFT JOIN $dbO.spip_forms_donnees_champs fdc23 ON fdc23.id_donnee = fd.id_donnee
  574. AND fdc23.champ = 'mot_12'
  575. LEFT JOIN $dbO.spip_forms_donnees_champs fdc24 ON fdc24.id_donnee = fd.id_donnee
  576. AND fdc24.champ = 'mot_13'
  577. LEFT JOIN $dbO.spip_forms_donnees_champs fdc25 ON fdc25.id_donnee = fd.id_donnee
  578. AND fdc25.champ = 'mot_14'
  579. LEFT JOIN $dbO.spip_forms_donnees_champs fdc26 ON fdc26.id_donnee = fd.id_donnee
  580. AND fdc26.champ = 'mot_15'
  581. LEFT JOIN $dbO.spip_forms_donnees_champs fdc27 ON fdc27.id_donnee = fd.id_donnee
  582. AND fdc27.champ = 'mot_16'
  583. LEFT JOIN $dbO.spip_forms_donnees_champs fdc28 ON fdc28.id_donnee = fd.id_donnee
  584. AND fdc28.champ = 'mot_17'
  585. LEFT JOIN $dbO.spip_forms_donnees_champs fdc29 ON fdc29.id_donnee = fd.id_donnee
  586. AND fdc29.champ = 'mot_25'
  587. LEFT JOIN $dbO.spip_forms_donnees_champs fdc30 ON fdc30.id_donnee = fd.id_donnee
  588. AND fdc30.champ = 'mot_27'
  589. LEFT JOIN $dbO.spip_forms_donnees_champs fdc31 ON fdc31.id_donnee = fd.id_donnee
  590. AND fdc31.champ = 'mot_28'
  591. WHERE fd.id_form = 53
  592. GROUP BY fd.id_donnee";
  593. $result = mysql_query($sql);
  594. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  595. echo "$dbO BATIMENT : ".mysql_affected_rows()."<br/>";
  596. //ETABLISSEMENT
  597. $sql = "INSERT INTO $dbN.gevu_etablissements
  598. (id_lieu, id_instant, nom, ref, adresse, commune, pays, code_postal
  599. , contact_proprietaire, contact_delegataire
  600. , reponse_1
  601. , reponse_2
  602. , reponse_3
  603. , reponse_4
  604. , reponse_5
  605. , id_donnee, maj)
  606. SELECT l.id_lieu
  607. , $idInstant
  608. , fdc1.valeur
  609. , fdc2.valeur
  610. , fdc3.valeur
  611. , fdc4.valeur
  612. , fdc5.valeur
  613. , fdc6.valeur
  614. , fdc7.valeur
  615. , fdc8.valeur
  616. , fdc17.valeur
  617. , fdc18.valeur
  618. , fdc19.valeur
  619. , fdc20.valeur
  620. , fdc21.valeur
  621. , fd.id_donnee, fd.date
  622. FROM $dbO.spip_forms_donnees fd
  623. INNER JOIN $dbO.spip_forms_donnees_articles fda ON fd.id_donnee = fda.id_donnee
  624. INNER JOIN $dbO.spip_articles a ON a.id_article = fda.id_article
  625. INNER JOIN $dbN.gevu_lieux l ON l.id_rubrique = a.id_rubrique AND l.id_instant = $idInstant
  626. LEFT JOIN $dbO.spip_forms_donnees_champs fdc1 ON fdc1.id_donnee = fd.id_donnee
  627. AND fdc1.champ = 'ligne_1'
  628. LEFT JOIN $dbO.spip_forms_donnees_champs fdc2 ON fdc2.id_donnee = fd.id_donnee
  629. AND fdc2.champ = 'ligne_2'
  630. LEFT JOIN $dbO.spip_forms_donnees_champs fdc3 ON fdc3.id_donnee = fd.id_donnee
  631. AND fdc3.champ = 'ligne_3'
  632. LEFT JOIN $dbO.spip_forms_donnees_champs fdc4 ON fdc4.id_donnee = fd.id_donnee
  633. AND fdc4.champ = 'ligne_4'
  634. LEFT JOIN $dbO.spip_forms_donnees_champs fdc5 ON fdc5.id_donnee = fd.id_donnee
  635. AND fdc5.champ = 'ligne_5'
  636. LEFT JOIN $dbO.spip_forms_donnees_champs fdc6 ON fdc6.id_donnee = fd.id_donnee
  637. AND fdc6.champ = 'code_postal_1'
  638. LEFT JOIN $dbO.spip_forms_donnees_champs fdc7 ON fdc7.id_donnee = fd.id_donnee
  639. AND fdc7.champ = 'ligne_6'
  640. LEFT JOIN $dbO.spip_forms_donnees_champs fdc8 ON fdc8.id_donnee = fd.id_donnee
  641. AND fdc8.champ = 'ligne_7'
  642. LEFT JOIN $dbO.spip_forms_donnees_champs fdc17 ON fdc17.id_donnee = fd.id_donnee
  643. AND fdc17.champ = 'mot_1'
  644. LEFT JOIN $dbO.spip_forms_donnees_champs fdc18 ON fdc18.id_donnee = fd.id_donnee
  645. AND fdc18.champ = 'mot_2'
  646. LEFT JOIN $dbO.spip_forms_donnees_champs fdc19 ON fdc19.id_donnee = fd.id_donnee
  647. AND fdc19.champ = 'mot_3'
  648. LEFT JOIN $dbO.spip_forms_donnees_champs fdc20 ON fdc20.id_donnee = fd.id_donnee
  649. AND fdc20.champ = 'select_1'
  650. LEFT JOIN $dbO.spip_forms_donnees_champs fdc21 ON fdc21.id_donnee = fd.id_donnee
  651. AND fdc21.champ = 'select_2'
  652. WHERE fd.id_form = 55
  653. GROUP BY fd.id_donnee";
  654. $result = mysql_query($sql);
  655. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  656. echo "$dbO ETABLISSEMENT : ".mysql_affected_rows()."<br/>";
  657. //ESPACES
  658. $sql = "INSERT INTO $dbN.gevu_espaces
  659. (id_lieu, id_instant, ref
  660. , id_type_espace
  661. , reponse_1
  662. , reponse_2
  663. , id_type_specifique_int
  664. , id_type_specifique_ext
  665. , id_donnee, maj)
  666. SELECT l.id_lieu
  667. , $idInstant
  668. , fdc2.valeur
  669. , fdc17.valeur
  670. , fdc18.valeur
  671. , fdc19.valeur
  672. , fdc20.valeur
  673. , fdc21.valeur
  674. , fd.id_donnee, fd.date
  675. FROM $dbO.spip_forms_donnees fd
  676. INNER JOIN $dbO.spip_forms_donnees_articles fda ON fd.id_donnee = fda.id_donnee
  677. INNER JOIN $dbO.spip_articles a ON a.id_article = fda.id_article
  678. INNER JOIN $dbN.gevu_lieux l ON l.id_rubrique = a.id_rubrique AND l.id_instant = $idInstant
  679. LEFT JOIN $dbO.spip_forms_donnees_champs fdc2 ON fdc2.id_donnee = fd.id_donnee
  680. AND fdc2.champ = 'ligne_2'
  681. LEFT JOIN $dbO.spip_forms_donnees_champs fdc17 ON fdc17.id_donnee = fd.id_donnee
  682. AND fdc17.champ = 'mot_1'
  683. LEFT JOIN $dbO.spip_forms_donnees_champs fdc18 ON fdc18.id_donnee = fd.id_donnee
  684. AND fdc18.champ = 'mot_2'
  685. LEFT JOIN $dbO.spip_forms_donnees_champs fdc19 ON fdc19.id_donnee = fd.id_donnee
  686. AND fdc19.champ = 'mot_3'
  687. LEFT JOIN $dbO.spip_forms_donnees_champs fdc20 ON fdc20.id_donnee = fd.id_donnee
  688. AND fdc20.champ = 'mot_4'
  689. LEFT JOIN $dbO.spip_forms_donnees_champs fdc21 ON fdc21.id_donnee = fd.id_donnee
  690. AND fdc21.champ = 'mot_5'
  691. WHERE fd.id_form = 56
  692. GROUP BY fd.id_donnee";
  693. $result = mysql_query($sql);
  694. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  695. echo "$dbO ESPACES : ".mysql_affected_rows()."<br/>";
  696. //ESPACES INTERIEURS
  697. $sql = "INSERT INTO $dbN.gevu_espacesxinterieurs
  698. (id_lieu, id_instant, nom, ref
  699. , fonction
  700. , id_type_specifique_int
  701. , id_donnee, maj)
  702. SELECT l.id_lieu
  703. , $idInstant
  704. , fdc1.valeur
  705. , fdc2.valeur
  706. , fdc3.valeur
  707. , fdc4.valeur
  708. , fd.id_donnee, fd.date
  709. FROM $dbO.spip_forms_donnees fd
  710. INNER JOIN $dbO.spip_forms_donnees_articles fda ON fd.id_donnee = fda.id_donnee
  711. INNER JOIN $dbO.spip_articles a ON a.id_article = fda.id_article
  712. INNER JOIN $dbN.gevu_lieux l ON l.id_rubrique = a.id_rubrique AND l.id_instant = $idInstant
  713. LEFT JOIN $dbO.spip_forms_donnees_champs fdc1 ON fdc1.id_donnee = fd.id_donnee
  714. AND fdc1.champ = 'ligne_1'
  715. LEFT JOIN $dbO.spip_forms_donnees_champs fdc2 ON fdc2.id_donnee = fd.id_donnee
  716. AND fdc2.champ = 'ligne_2'
  717. LEFT JOIN $dbO.spip_forms_donnees_champs fdc3 ON fdc3.id_donnee = fd.id_donnee
  718. AND fdc3.champ = 'ligne_3'
  719. LEFT JOIN $dbO.spip_forms_donnees_champs fdc4 ON fdc4.id_donnee = fd.id_donnee
  720. AND fdc4.champ = 'select_2'
  721. WHERE fd.id_form = 57
  722. GROUP BY fd.id_donnee";
  723. $result = mysql_query($sql);
  724. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  725. echo "$dbO ESPACES INTERIEURS : ".mysql_affected_rows()."<br/>";
  726. //PARCELLES
  727. $sql = "INSERT INTO $dbN.gevu_parcelles
  728. (id_lieu, id_instant, nom, ref, adresse, commune, pays, code_postal
  729. , contact_proprietaire
  730. , reponse_1
  731. , reponse_2
  732. , id_donnee, maj)
  733. SELECT l.id_lieu
  734. , $idInstant
  735. , fdc1.valeur
  736. , fdc2.valeur
  737. , fdc3.valeur
  738. , fdc4.valeur
  739. , fdc5.valeur
  740. , ''
  741. , fdc6.valeur
  742. , fdc7.valeur
  743. , fdc17.valeur
  744. , fd.id_donnee, fd.date
  745. FROM $dbO.spip_forms_donnees fd
  746. INNER JOIN $dbO.spip_forms_donnees_articles fda ON fd.id_donnee = fda.id_donnee
  747. INNER JOIN $dbO.spip_articles a ON a.id_article = fda.id_article
  748. INNER JOIN $dbN.gevu_lieux l ON l.id_rubrique = a.id_rubrique AND l.id_instant = $idInstant
  749. LEFT JOIN $dbO.spip_forms_donnees_champs fdc1 ON fdc1.id_donnee = fd.id_donnee
  750. AND fdc1.champ = 'ligne_1'
  751. LEFT JOIN $dbO.spip_forms_donnees_champs fdc2 ON fdc2.id_donnee = fd.id_donnee
  752. AND fdc2.champ = 'ligne_2'
  753. LEFT JOIN $dbO.spip_forms_donnees_champs fdc3 ON fdc3.id_donnee = fd.id_donnee
  754. AND fdc3.champ = 'ligne_3'
  755. LEFT JOIN $dbO.spip_forms_donnees_champs fdc4 ON fdc4.id_donnee = fd.id_donnee
  756. AND fdc4.champ = 'ligne_4'
  757. LEFT JOIN $dbO.spip_forms_donnees_champs fdc5 ON fdc5.id_donnee = fd.id_donnee
  758. AND fdc5.champ = 'ligne_5'
  759. LEFT JOIN $dbO.spip_forms_donnees_champs fdc6 ON fdc6.id_donnee = fd.id_donnee
  760. AND fdc6.champ = 'code_postal_1'
  761. LEFT JOIN $dbO.spip_forms_donnees_champs fdc7 ON fdc7.id_donnee = fd.id_donnee
  762. AND fdc7.champ = 'ligne_6'
  763. LEFT JOIN $dbO.spip_forms_donnees_champs fdc8 ON fdc8.id_donnee = fd.id_donnee
  764. AND fdc8.champ = 'ligne_7'
  765. LEFT JOIN $dbO.spip_forms_donnees_champs fdc17 ON fdc17.id_donnee = fd.id_donnee
  766. AND fdc17.champ = 'mot_1'
  767. WHERE fd.id_form = 58
  768. GROUP BY fd.id_donnee";
  769. $result = mysql_query($sql);
  770. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  771. echo "$dbO PARCELLES : ".mysql_affected_rows()."<br/>";
  772. //ESPACES EXTERIEURS
  773. $sql = "INSERT INTO $dbN.gevu_espacesxexterieurs
  774. (id_lieu, id_instant, nom, ref
  775. , fonction
  776. , id_type_espace
  777. , id_type_specifique_ext
  778. , id_donnee, maj)
  779. SELECT l.id_lieu
  780. , $idInstant
  781. , fdc1.valeur
  782. , fdc2.valeur
  783. , fdc3.valeur
  784. , fdc4.valeur
  785. , fdc4.valeur
  786. , fd.id_donnee, fd.date
  787. FROM $dbO.spip_forms_donnees fd
  788. INNER JOIN $dbO.spip_forms_donnees_articles fda ON fd.id_donnee = fda.id_donnee
  789. INNER JOIN $dbO.spip_articles a ON a.id_article = fda.id_article
  790. INNER JOIN $dbN.gevu_lieux l ON l.id_rubrique = a.id_rubrique AND l.id_instant = $idInstant
  791. LEFT JOIN $dbO.spip_forms_donnees_champs fdc1 ON fdc1.id_donnee = fd.id_donnee
  792. AND fdc1.champ = 'ligne_1'
  793. LEFT JOIN $dbO.spip_forms_donnees_champs fdc2 ON fdc2.id_donnee = fd.id_donnee
  794. AND fdc2.champ = 'ligne_2'
  795. LEFT JOIN $dbO.spip_forms_donnees_champs fdc3 ON fdc3.id_donnee = fd.id_donnee
  796. AND fdc3.champ = 'ligne_3'
  797. LEFT JOIN $dbO.spip_forms_donnees_champs fdc4 ON fdc4.id_donnee = fd.id_donnee
  798. AND fdc4.champ = 'select_1'
  799. LEFT JOIN $dbO.spip_forms_donnees_champs fdc5 ON fdc5.id_donnee = fd.id_donnee
  800. AND fdc5.champ = 'select_2'
  801. WHERE fd.id_form = 61
  802. GROUP BY fd.id_donnee";
  803. $result = mysql_query($sql);
  804. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  805. echo "$dbO ESPACES EXTERIEURS : ".mysql_affected_rows()."<br/>";
  806. //DIAGNOSTICS VOIRIE
  807. $sql = "INSERT INTO $dbN.gevu_diagnosticsxvoirie
  808. (id_lieu, id_instant, nom, ref, id_donnee, maj)
  809. SELECT l.id_lieu
  810. , $idInstant
  811. , fdc1.valeur
  812. , fdc3.valeur
  813. , fd.id_donnee, fd.date
  814. FROM $dbO.spip_forms_donnees fd
  815. INNER JOIN $dbO.spip_forms_donnees_articles fda ON fd.id_donnee = fda.id_donnee
  816. INNER JOIN $dbO.spip_articles a ON a.id_article = fda.id_article
  817. INNER JOIN $dbN.gevu_lieux l ON l.id_rubrique = a.id_rubrique AND l.id_instant = $idInstant
  818. LEFT JOIN $dbO.spip_forms_donnees_champs fdc1 ON fdc1.id_donnee = fd.id_donnee
  819. AND fdc1.champ = 'ligne_1'
  820. LEFT JOIN $dbO.spip_forms_donnees_champs fdc3 ON fdc3.id_donnee = fd.id_donnee
  821. AND fdc3.champ = 'ligne_3'
  822. WHERE fd.id_form = 62
  823. GROUP BY fd.id_donnee";
  824. $result = mysql_query($sql);
  825. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  826. echo "$dbO DIAGNOSTICS VOIRIE : ".mysql_affected_rows()."<br/>";
  827. //OBJETS INTERIEURS
  828. $sql = "INSERT INTO $dbN.gevu_objetsxinterieurs
  829. (id_lieu, id_instant, nom, ref
  830. , fonctions
  831. , reponse_1
  832. , reponse_2
  833. , id_type_objet
  834. , id_donnee, maj)
  835. SELECT l.id_lieu
  836. , $idInstant
  837. , fdc1.valeur
  838. , fdc2.valeur
  839. , fdc3.valeur
  840. , fdc17.valeur
  841. , fdc18.valeur
  842. , fdc19.valeur
  843. , fd.id_donnee, fd.date
  844. FROM $dbO.spip_forms_donnees fd
  845. INNER JOIN $dbO.spip_forms_donnees_articles fda ON fd.id_donnee = fda.id_donnee
  846. INNER JOIN $dbO.spip_articles a ON a.id_article = fda.id_article
  847. INNER JOIN $dbN.gevu_lieux l ON l.id_rubrique = a.id_rubrique AND l.id_instant = $idInstant
  848. LEFT JOIN $dbO.spip_forms_donnees_champs fdc1 ON fdc1.id_donnee = fd.id_donnee
  849. AND fdc1.champ = 'ligne_1'
  850. LEFT JOIN $dbO.spip_forms_donnees_champs fdc2 ON fdc2.id_donnee = fd.id_donnee
  851. AND fdc2.champ = 'ligne_2'
  852. LEFT JOIN $dbO.spip_forms_donnees_champs fdc3 ON fdc3.id_donnee = fd.id_donnee
  853. AND fdc3.champ = 'ligne_3'
  854. LEFT JOIN $dbO.spip_forms_donnees_champs fdc17 ON fdc17.id_donnee = fd.id_donnee
  855. AND fdc17.champ = 'mot_1'
  856. LEFT JOIN $dbO.spip_forms_donnees_champs fdc18 ON fdc18.id_donnee = fd.id_donnee
  857. AND fdc18.champ = 'mot_2'
  858. LEFT JOIN $dbO.spip_forms_donnees_champs fdc19 ON fdc19.id_donnee = fd.id_donnee
  859. AND fdc19.champ = 'mot_3'
  860. WHERE fd.id_form = 63
  861. GROUP BY fd.id_donnee";
  862. $result = mysql_query($sql);
  863. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  864. echo "$dbO OBJETS INTERIEURS : ".mysql_affected_rows()."<br/>";
  865. //OBJETS EXTERIEURS
  866. $sql = "INSERT INTO $dbN.gevu_objetsxexterieurs
  867. (id_lieu, id_instant, nom, ref
  868. , fonctions
  869. , id_type_objet
  870. , id_type_objet_ext
  871. , id_donnee, maj)
  872. SELECT l.id_lieu
  873. , $idInstant
  874. , fdc1.valeur
  875. , fdc2.valeur
  876. , fdc3.valeur
  877. , fdc17.valeur
  878. , fdc18.valeur
  879. , fd.id_donnee, fd.date
  880. FROM $dbO.spip_forms_donnees fd
  881. INNER JOIN $dbO.spip_forms_donnees_articles fda ON fd.id_donnee = fda.id_donnee
  882. INNER JOIN $dbO.spip_articles a ON a.id_article = fda.id_article
  883. INNER JOIN $dbN.gevu_lieux l ON l.id_rubrique = a.id_rubrique AND l.id_instant = $idInstant
  884. LEFT JOIN $dbO.spip_forms_donnees_champs fdc1 ON fdc1.id_donnee = fd.id_donnee
  885. AND fdc1.champ = 'ligne_1'
  886. LEFT JOIN $dbO.spip_forms_donnees_champs fdc2 ON fdc2.id_donnee = fd.id_donnee
  887. AND fdc2.champ = 'ligne_2'
  888. LEFT JOIN $dbO.spip_forms_donnees_champs fdc3 ON fdc3.id_donnee = fd.id_donnee
  889. AND fdc3.champ = 'ligne_3'
  890. LEFT JOIN $dbO.spip_forms_donnees_champs fdc17 ON fdc17.id_donnee = fd.id_donnee
  891. AND fdc17.champ = 'select_1'
  892. LEFT JOIN $dbO.spip_forms_donnees_champs fdc18 ON fdc18.id_donnee = fd.id_donnee
  893. AND fdc18.champ = 'select_2'
  894. WHERE fd.id_form = 64
  895. GROUP BY fd.id_donnee";
  896. $result = mysql_query($sql);
  897. if (!$result) echo 'Requ?te invalide : ' . mysql_error().'<br/>'.$sql.'<br/>';
  898. echo "$dbO OBJETS EXTERIEURS : ".mysql_affected_rows()."<br/>";
  899. //ELEMENTS VOIRIE
  900. $sql = "INSERT INTO $dbN.gevu_objetsxvoiries
  901. (id_lieu, id_instant, nom, ref
  902. , id_type_objet_voirie, id_donnee, maj
  903. )
  904. SELECT l.id_lieu
  905. , $idInstant
  906. , fdc1.valeur
  907. , fdc2.valeur
  908. , fdc18.valeur
  909. , fd.id_donnee, fd.date
  910. FROM $dbO.spip_forms_donnees fd
  911. INNER JOIN $dbO.spip_forms_donnees_articles fda ON fd.id_donnee = fda.id_donnee
  912. INNER JOIN $dbO.spip_articles a ON a.id_a

Large files files are truncated, but you can click here to view the full file