You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
103 lines
3.6 KiB
103 lines
3.6 KiB
<?php
|
|
|
|
define('__ROOT__', dirname(dirname(dirname(dirname(__FILE__)))));
|
|
|
|
require_once __ROOT__.'/modules/base/php/lib/cti/Database/Database2Factory.class.php';
|
|
require_once __ROOT__.'/modules/base/php/lib/cti/Environnement/Environnement.class.php';
|
|
|
|
$root = __ROOT__.'/prodsgbd/meta4/data';
|
|
|
|
// Chargement des paramètres et connexion BD.
|
|
$conn = Database2Factory::getInstance(Environnement::PROVIDER_CTI);
|
|
$ret = $conn->connect();
|
|
|
|
$allCsv = ListIn($root);
|
|
|
|
$conn->query("DROP SCHEMA prod_meta4 CASCADE;");
|
|
$conn->query("CREATE SCHEMA IF NOT EXISTS prod_meta4;");
|
|
|
|
foreach ($allCsv as $csvFile) {
|
|
echo PHP_EOL."TRAITEMENT DE LA TABLE " .$csvFile[0]."/".$csvFile[1];
|
|
$buffer = '';
|
|
$tmpCols = array();
|
|
$tmpCols2 = array();
|
|
$tmpCols3 = array();
|
|
$tmpCols4 = array();
|
|
$colDef = '';
|
|
$colSELECT = '';
|
|
$colSELECT4 = '';
|
|
|
|
$handle = fopen($csvFile[0].DIRECTORY_SEPARATOR.$csvFile[1], "r");
|
|
if ($handle) {
|
|
$buffer = fgets($handle);
|
|
fclose($handle);
|
|
}
|
|
|
|
$tmpCols = explode('|', $buffer);
|
|
|
|
foreach ($tmpCols as $value) {
|
|
if (trim($value) !== '') {
|
|
$tmpCols2[] = "''::text as ".$value;
|
|
$tmpCols3[] = $value;
|
|
$tmpCols4[] = strtolower("'".trim($value)."'");
|
|
} else {
|
|
echo " (Colonne en plus)";
|
|
}
|
|
}
|
|
$colDef= implode(',', $tmpCols2);
|
|
$colSELECT = implode(',', $tmpCols3);
|
|
$colSELECT4 = implode(',', $tmpCols4);
|
|
|
|
// Création de la table de réception de la données si n'existe pas.
|
|
$conn->query("create table if not exists prod_meta4.".$csvFile[2]." as select $colDef limit 0");
|
|
|
|
// Ajout des colonnes manquantes.
|
|
$toto = $conn->queryFetchAllAssoc("select unnest(ARRAY[$colSELECT4]) as new_col
|
|
EXCEPT
|
|
select column_name from information_schema.columns where table_schema = 'prod_meta4' and table_name = lower('".$csvFile[2]."'::text)");
|
|
if ($toto !== false) {
|
|
foreach ($toto as $value) {
|
|
echo PHP_EOL."NOUVELLE colonne : prod_meta4.".$csvFile[2].".".$value['new_col'];
|
|
$conn->query("ALTER TABLE prod_meta4.".$csvFile[2]." ADD COLUMN ".$value['new_col']." text");
|
|
}
|
|
}
|
|
|
|
// Suppression puis création de la table héritée pour le mois donné.
|
|
$mois = substr($csvFile[0], -6);
|
|
$childTable = $csvFile[2]."_".$mois;
|
|
$conn->query("drop table if exists prod_meta4.$childTable; create table if not exists prod_meta4.$childTable() INHERITS (prod_meta4.".$csvFile[2].")");
|
|
|
|
// Création de la table temporaire de chargement de la donnée.
|
|
$conn->query("drop table if exists w_meta4_load; create temp table w_meta4_load as select $colDef limit 0");
|
|
$tlt = $conn->query("copy w_meta4_load
|
|
from '".$csvFile[0]."/".$csvFile[1]."'
|
|
CSV HEADER delimiter '|' encoding 'LATIN9'");
|
|
$conn->query("insert into prod_meta4.$childTable($colSELECT) select $colSELECT from w_meta4_load;");
|
|
if ($tlt === false){
|
|
echo PHP_EOL.$conn->lastError();
|
|
}
|
|
}
|
|
|
|
// Déconnexion BD
|
|
$conn->close();
|
|
|
|
function ListIn($dir) {
|
|
$result = array();
|
|
|
|
foreach (scandir($dir) as $f) {
|
|
if ($f !== '.' && $f !== '..') {
|
|
if (is_dir($dir.DIRECTORY_SEPARATOR.$f)) {
|
|
$result = array_merge($result, ListIn($dir.DIRECTORY_SEPARATOR.$f, $f));
|
|
} else {
|
|
if (substr($f, -4) === '.csv') {
|
|
if (substr($f, 0, 7) == 'CTI_KER') {
|
|
$result[] = array($dir, $f, substr($f, 8, -4)); // suppression du prefixe CTI_KER et de l'extension .csv.
|
|
} else {
|
|
$result[] = array($dir, $f, substr($f, 4, -4));
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
return $result;
|
|
}
|