316 lines
15 KiB
PHP
316 lines
15 KiB
PHP
<?php
|
|
|
|
namespace App\Console\Commands;
|
|
|
|
use Illuminate\Console\Command;
|
|
use Illuminate\Support\Facades\DB;
|
|
use Symfony\Component\Process\Process;
|
|
|
|
class ImportCondominMirror0021 extends Command
|
|
{
|
|
protected $signature = 'gescon:import-mirror-0021
|
|
{--stabile=0021 : Codice dello stabile pilot}
|
|
{--refresh : Svuota la tabella mirror prima di importare}';
|
|
|
|
protected $description = 'Importa in modalità append-only lossless la tabella condomin dello stabile 0021 nella staging mirror (condomin_mirror)';
|
|
|
|
public function handle(): int
|
|
{
|
|
$stabileCode = str_pad((string) $this->option('stabile'), 4, '0', STR_PAD_LEFT);
|
|
$ammCode = 'HWFGITXK';
|
|
|
|
if ($this->option('refresh')) {
|
|
DB::connection('gescon_import')->table('condomin_mirror')->truncate();
|
|
$this->info("Tabella gescon_import.condomin_mirror svuotata.");
|
|
}
|
|
|
|
$baseDirs = [
|
|
"/mnt/gescon-archives/gescon/{$stabileCode}",
|
|
storage_path("app/amministratori/{$ammCode}/legacy/{$stabileCode}")
|
|
];
|
|
|
|
$targetDir = null;
|
|
foreach ($baseDirs as $dir) {
|
|
if (is_dir($dir)) {
|
|
$targetDir = $dir;
|
|
break;
|
|
}
|
|
}
|
|
|
|
if (! $targetDir) {
|
|
$this->error("Directory MDB non trovata per lo stabile {$stabileCode}");
|
|
return Command::FAILURE;
|
|
}
|
|
|
|
$fileConfigs = [];
|
|
$globMatches = glob("{$targetDir}/*/singolo_anno.mdb");
|
|
if (is_array($globMatches)) {
|
|
sort($globMatches);
|
|
foreach ($globMatches as $mdbPath) {
|
|
$year = basename(dirname($mdbPath));
|
|
$rel = "{$stabileCode}/{$year}/singolo_anno.mdb";
|
|
$fileConfigs[$year] = ['year' => $year, 'rel' => $rel, 'path' => $mdbPath];
|
|
}
|
|
}
|
|
|
|
$perFileCounts = [];
|
|
$totalMdbRows = 0;
|
|
|
|
foreach ($fileConfigs as $key => $cfg) {
|
|
if (! file_exists($cfg['path'])) {
|
|
$this->warn("File non trovato: {$cfg['path']}");
|
|
continue;
|
|
}
|
|
|
|
$rows = $this->extractMdbCondominRows($cfg['path']);
|
|
$count = count($rows);
|
|
$totalMdbRows += $count;
|
|
$perFileCounts[$key] = [
|
|
'rel' => $cfg['rel'],
|
|
'year' => $cfg['year'],
|
|
'mdb_rows' => $count,
|
|
'imported' => 0
|
|
];
|
|
|
|
$rowNum = 0;
|
|
foreach ($rows as $r) {
|
|
$rowNum++;
|
|
$provHash = hash('sha256', "{$ammCode}|{$stabileCode}|{$cfg['rel']}|{$cfg['year']}|condomin|{$rowNum}");
|
|
|
|
// Extract real MDB interno column (int or interno) without reconstructing from id_cond/cod_cond
|
|
$rawInterno = isset($r['int']) ? (string)$r['int'] : (isset($r['interno']) ? (string)$r['interno'] : '');
|
|
$rawScala = isset($r['scala']) ? (string)$r['scala'] : '';
|
|
$rawPiano = isset($r['piano']) ? (string)$r['piano'] : '';
|
|
|
|
$idCond = isset($r['id_cond']) ? (string)$r['id_cond'] : null;
|
|
$codCond = isset($r['cod_cond']) ? (string)$r['cod_cond'] : null;
|
|
$cognome = isset($r['cognome']) ? (string)$r['cognome'] : null;
|
|
$nome = isset($r['nome']) ? (string)$r['nome'] : null;
|
|
$nomCond = isset($r['nom_cond']) ? (string)$r['nom_cond'] : null;
|
|
$codFisc = isset($r['cond_cod_fisc']) ? (string)$r['cond_cod_fisc'] : (isset($r['codice_fiscale']) ? (string)$r['codice_fiscale'] : null);
|
|
$tel = isset($r['tel1']) ? (string)$r['tel1'] : (isset($r['telefono']) ? (string)$r['telefono'] : null);
|
|
$cell = isset($r['cell_cond']) ? (string)$r['cell_cond'] : (isset($r['cellulare']) ? (string)$r['cellulare'] : null);
|
|
$email = isset($r['e_mail_condomino']) ? (string)$r['e_mail_condomino'] : (isset($r['email']) ? (string)$r['email'] : null);
|
|
$pec = isset($r['pec_condomino']) ? (string)$r['pec_condomino'] : (isset($r['pec']) ? (string)$r['pec'] : null);
|
|
|
|
$proprietario = isset($r['proprietario']) ? (string)$r['proprietario'] : null;
|
|
$inquilino = isset($r['inquilino']) ? (string)$r['inquilino'] : (isset($r['inquil_nome']) ? (string)$r['inquil_nome'] : null);
|
|
|
|
$subPrima = isset($r['subentro_prima_cera']) ? (string)$r['subentro_prima_cera'] : null;
|
|
$subAdesso = isset($r['subentro_adesso_ce']) ? (string)$r['subentro_adesso_ce'] : null;
|
|
$subDal = isset($r['subentrato_dal']) ? (string)$r['subentrato_dal'] : null;
|
|
$attivoAl = isset($r['attivo_fino_al']) ? (string)$r['attivo_fino_al'] : null;
|
|
$inquilDal = isset($r['inquil_dal']) ? (string)$r['inquil_dal'] : null;
|
|
$inquilAl = isset($r['inquil_al']) ? (string)$r['inquil_al'] : null;
|
|
|
|
$dirReale = isset($r['diritto_reale']) ? (string)$r['diritto_reale'] : null;
|
|
$dirGodimento = isset($r['diritto_godimento']) ? (string)$r['diritto_godimento'] : null;
|
|
$percDir = isset($r['perc_diritto_reale']) ? (string)$r['perc_diritto_reale'] : null;
|
|
|
|
$millProp = isset($r['millesimi_proprieta']) && is_numeric($r['millesimi_proprieta']) ? (float)$r['millesimi_proprieta'] : null;
|
|
$millRisc = isset($r['millesimi_riscaldamento']) && is_numeric($r['millesimi_riscaldamento']) ? (float)$r['millesimi_riscaldamento'] : null;
|
|
$millAsc = isset($r['millesimi_ascensore']) && is_numeric($r['millesimi_ascensore']) ? (float)$r['millesimi_ascensore'] : null;
|
|
|
|
$record = [
|
|
'amministratore_code' => $ammCode,
|
|
'cod_stabile' => $stabileCode,
|
|
'source_file' => $cfg['rel'],
|
|
'source_year' => $cfg['year'],
|
|
'source_table' => 'condomin',
|
|
'source_row' => $rowNum,
|
|
'provenance_hash' => $provHash,
|
|
'id_cond' => $idCond,
|
|
'cod_cond' => $codCond,
|
|
'scala' => trim($rawScala) !== '' ? trim($rawScala) : null,
|
|
'interno' => trim($rawInterno) !== '' ? trim($rawInterno) : null,
|
|
'piano' => trim($rawPiano) !== '' ? trim($rawPiano) : null,
|
|
'cognome' => $cognome,
|
|
'nome' => $nome,
|
|
'nom_cond' => $nomCond,
|
|
'codice_fiscale' => $codFisc,
|
|
'telefono' => $tel,
|
|
'cellulare' => $cell,
|
|
'email' => $email,
|
|
'pec' => $pec,
|
|
'millesimi_proprieta' => $millProp,
|
|
'millesimi_riscaldamento' => $millRisc,
|
|
'millesimi_ascensore' => $millAsc,
|
|
'proprietario' => $proprietario,
|
|
'inquilino' => $inquilino,
|
|
'subentro_prima_cera' => $subPrima,
|
|
'subentro_adesso_ce' => $subAdesso,
|
|
'subentrato_dal' => $subDal,
|
|
'attivo_fino_al' => $attivoAl,
|
|
'inquil_dal' => $inquilDal,
|
|
'inquil_al' => $inquilAl,
|
|
'diritto_reale' => $dirReale,
|
|
'diritto_godimento' => $dirGodimento,
|
|
'perc_diritto_reale' => $percDir,
|
|
'legacy_payload' => json_encode($r, JSON_UNESCAPED_UNICODE),
|
|
'created_at' => now(),
|
|
'updated_at' => now(),
|
|
];
|
|
|
|
DB::connection('gescon_import')->table('condomin_mirror')->updateOrInsert(
|
|
['provenance_hash' => $provHash],
|
|
$record
|
|
);
|
|
|
|
$perFileCounts[$key]['imported']++;
|
|
}
|
|
|
|
$unitMapByIdCond = [];
|
|
$unitMapByCodCond = [];
|
|
|
|
foreach ($rows as $r) {
|
|
$rawInterno = isset($r['int']) ? (string)$r['int'] : (isset($r['interno']) ? (string)$r['interno'] : '');
|
|
$rawScala = isset($r['scala']) ? (string)$r['scala'] : '';
|
|
$rawPiano = isset($r['piano']) ? (string)$r['piano'] : '';
|
|
$idCond = isset($r['id_cond']) ? (string)$r['id_cond'] : null;
|
|
$codCond = isset($r['cod_cond']) ? (string)$r['cod_cond'] : null;
|
|
|
|
if ($idCond) {
|
|
$unitMapByIdCond[$idCond] = ['scala' => $rawScala, 'interno' => $rawInterno, 'piano' => $rawPiano];
|
|
}
|
|
if ($codCond) {
|
|
$unitMapByCodCond[$codCond] = ['scala' => $rawScala, 'interno' => $rawInterno, 'piano' => $rawPiano];
|
|
}
|
|
}
|
|
|
|
// Extract and mirror comproprietari if present in MDB
|
|
$compRows = $this->extractMdbTableRows($cfg['path'], 'comproprietari');
|
|
$compNum = 0;
|
|
foreach ($compRows as $c) {
|
|
$compNum++;
|
|
$provHash = hash('sha256', "{$ammCode}|{$stabileCode}|{$cfg['rel']}|{$cfg['year']}|comproprietari|{$compNum}");
|
|
|
|
$cIdCond = isset($c['id_cond']) ? (string)$c['id_cond'] : null;
|
|
$cCodCond = isset($c['ex_cod_cond']) ? (string)$c['ex_cod_cond'] : (isset($c['cod_cond']) ? (string)$c['cod_cond'] : null);
|
|
|
|
$unitInfo = ($cIdCond && isset($unitMapByIdCond[$cIdCond]))
|
|
? $unitMapByIdCond[$cIdCond]
|
|
: null;
|
|
|
|
$rawScala = $unitInfo['scala'] ?? (isset($c['ex_scala']) ? (string)$c['ex_scala'] : '');
|
|
$rawInterno = $unitInfo['interno'] ?? (isset($c['ex_int']) ? (string)$c['ex_int'] : '');
|
|
$rawPiano = $unitInfo['piano'] ?? '';
|
|
|
|
$nomCond = isset($c['nom_cond']) ? (string)$c['nom_cond'] : (isset($c['Detraz_cess_nome']) ? (string)$c['Detraz_cess_nome'] : null);
|
|
$codFisc = isset($c['Cond_cod_fisc']) ? (string)$c['Cond_cod_fisc'] : (isset($c['Detraz_cess_CF']) ? (string)$c['Detraz_cess_CF'] : null);
|
|
$tel = isset($c['tel1']) ? (string)$c['tel1'] : null;
|
|
$cell = isset($c['Cell_cond']) ? (string)$c['Cell_cond'] : null;
|
|
$email = isset($c['E_mail_condomino']) ? (string)$c['E_mail_condomino'] : null;
|
|
$pec = isset($c['PEC_compr']) ? (string)$c['PEC_compr'] : null;
|
|
$dirReale= isset($c['Diritto_reale']) && trim((string)$c['Diritto_reale']) !== '' ? (string)$c['Diritto_reale'] : (isset($c['Descriz']) ? (string)$c['Descriz'] : 'Comproprietà');
|
|
$percDir = isset($c['Perc_Diritto_reale']) && is_numeric($c['Perc_Diritto_reale']) ? (string)$c['Perc_Diritto_reale'] : null;
|
|
|
|
$recordComp = [
|
|
'amministratore_code' => $ammCode,
|
|
'cod_stabile' => $stabileCode,
|
|
'source_file' => $cfg['rel'],
|
|
'source_year' => $cfg['year'],
|
|
'source_table' => 'comproprietari',
|
|
'source_row' => $compNum,
|
|
'provenance_hash' => $provHash,
|
|
'id_cond' => $cIdCond,
|
|
'cod_cond' => $cCodCond,
|
|
'scala' => trim($rawScala) !== '' ? trim($rawScala) : null,
|
|
'interno' => trim($rawInterno) !== '' ? trim($rawInterno) : null,
|
|
'piano' => trim($rawPiano) !== '' ? trim($rawPiano) : null,
|
|
'cognome' => null,
|
|
'nome' => null,
|
|
'nom_cond' => $nomCond,
|
|
'codice_fiscale' => $codFisc,
|
|
'telefono' => $tel,
|
|
'cellulare' => $cell,
|
|
'email' => $email,
|
|
'pec' => $pec,
|
|
'millesimi_proprieta' => null,
|
|
'millesimi_riscaldamento' => null,
|
|
'millesimi_ascensore' => null,
|
|
'proprietario' => $nomCond,
|
|
'inquilino' => null,
|
|
'subentro_prima_cera' => null,
|
|
'subentro_adesso_ce' => null,
|
|
'subentrato_dal' => null,
|
|
'attivo_fino_al' => null,
|
|
'inquil_dal' => null,
|
|
'inquil_al' => null,
|
|
'diritto_reale' => $dirReale,
|
|
'diritto_godimento' => null,
|
|
'perc_diritto_reale' => $percDir,
|
|
'legacy_payload' => json_encode($c, JSON_UNESCAPED_UNICODE),
|
|
'created_at' => now(),
|
|
'updated_at' => now(),
|
|
];
|
|
|
|
DB::connection('gescon_import')->table('condomin_mirror')->updateOrInsert(
|
|
['provenance_hash' => $provHash],
|
|
$recordComp
|
|
);
|
|
}
|
|
}
|
|
|
|
$stagingRows = DB::connection('gescon_import')->table('condomin_mirror')->where('cod_stabile', $stabileCode)->count();
|
|
|
|
$duplicates = DB::connection('gescon_import')->select("
|
|
SELECT provenance_hash, COUNT(*) as cnt
|
|
FROM condomin_mirror
|
|
WHERE cod_stabile = ?
|
|
GROUP BY provenance_hash
|
|
HAVING cnt > 1
|
|
", [$stabileCode]);
|
|
|
|
$duplicateCount = count($duplicates);
|
|
$collisionCount = $stagingRows < $totalMdbRows ? ($totalMdbRows - $stagingRows) : 0;
|
|
|
|
$this->info("=== FASE 1A MIRROR STAGING 0021 COMPLETATA ===");
|
|
$this->line("MDB_ROWS_TOTAL: {$totalMdbRows}");
|
|
$this->line("STAGING_ROWS: {$stagingRows}");
|
|
$this->line("COLLISION_COUNT: {$collisionCount}");
|
|
$this->line("DUPLICATE_PROVENANCE_COUNT: {$duplicateCount}");
|
|
|
|
foreach ($perFileCounts as $k => $info) {
|
|
$this->line("PER_FILE: {$info['rel']} | YEAR: {$info['year']} | MDB: {$info['mdb_rows']} | STAGING: {$info['imported']}");
|
|
}
|
|
|
|
return Command::SUCCESS;
|
|
}
|
|
|
|
private function extractMdbCondominRows(string $mdbPath): array
|
|
{
|
|
return $this->extractMdbTableRows($mdbPath, 'condomin');
|
|
}
|
|
|
|
private function extractMdbTableRows(string $mdbPath, string $tableName): array
|
|
{
|
|
$proc = new Process(['mdb-export', $mdbPath, $tableName]);
|
|
$proc->run();
|
|
|
|
if (! $proc->isSuccessful()) {
|
|
return [];
|
|
}
|
|
|
|
$csvContent = $proc->getOutput();
|
|
$stream = fopen('php://memory', 'r+');
|
|
fwrite($stream, $csvContent);
|
|
rewind($stream);
|
|
|
|
$header = fgetcsv($stream);
|
|
if (! $header) {
|
|
fclose($stream);
|
|
return [];
|
|
}
|
|
|
|
$rows = [];
|
|
while (($data = fgetcsv($stream)) !== false) {
|
|
if (count($data) === count($header)) {
|
|
$rows[] = array_combine($header, $data);
|
|
}
|
|
}
|
|
|
|
fclose($stream);
|
|
return $rows;
|
|
}
|
|
}
|