netgescon-day0/app/Console/Commands/GesconImportStabiliBankNotesCommand.php

<?php

namespace App\Console\Commands;

use App\Models\Stabile;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class GesconImportStabiliBankNotesCommand extends Command
{
    protected $signature = 'gescon:import-stabili-bank-notes {--mdb= : Percorso opzionale Stabili.mdb}';

    protected $description = 'Importa ed allinea note, banche, IBAN e conti correnti postali da Stabili.mdb sugli stabili consolidati';

    public function handle(): int
    {
        $this->info('Avvio importazione note e conti correnti da Stabili.mdb...');

        $candidateMdbs = [
            $this->option('mdb'),
            '/mnt/gescon-archives/gescon/dbc/Stabili.mdb',
            '/mnt/gescon-archives/gescon/DBC/Stabili.mdb',
            '/mnt/gescon-archives/gescon_vuoto/DBC/Stabili.mdb',
        ];

        $mdbPath = null;
        foreach ($candidateMdbs as $p) {
            if ($p && file_exists($p) && is_readable($p)) {
                $mdbPath = $p;
                break;
            }
        }

        if (! $mdbPath) {
            $this->error('File Stabili.mdb non trovato.');
            return self::FAILURE;
        }

        $this->line("Utilizzo file MDB: {$mdbPath}");

        $proc = new \Symfony\Component\Process\Process(['mdb-export', $mdbPath, 'Stabili']);
        $proc->run();

        if (! $proc->isSuccessful()) {
            $this->error('Impossibile eseguire mdb-export.');
            return self::FAILURE;
        }

        $csvContent = $proc->getOutput();
        $stream     = fopen('php://memory', 'r+');
        fwrite($stream, $csvContent);
        rewind($stream);

        $header = fgetcsv($stream);
        if (! $header) {
            fclose($stream);
            $this->error('Header CSV non valido.');
            return self::FAILURE;
        }

        $updated = 0;
        while (($data = fgetcsv($stream)) !== false) {
            if (count($data) === count($header)) {
                $row        = array_combine($header, $data);
                $codLegacy  = sprintf('%04d', (int) ($row['cod_stabile'] ?? 0));
                $cfLegacy   = trim((string) ($row['codice_fisc'] ?? ''));
                $den        = trim((string) ($row['denominazione'] ?? ''));
                $note1      = trim((string) ($row['note1'] ?? ''));
                $note2      = trim((string) ($row['Note'] ?? ''));
                $banca1     = trim((string) ($row['Banca'] ?? ''));
                $ibanBanca1 = trim((string) ($row['IBAN_Banca'] ?? ''));
                $ccBanca1   = trim((string) ($row['Banca_num_cc'] ?? ''));
                $abi1       = trim((string) ($row['ABI'] ?? ''));
                $cab1       = trim((string) ($row['CAB'] ?? ''));
                $sia1       = trim((string) ($row['SIA'] ?? ''));
                $cin1       = trim((string) ($row['CIN'] ?? ''));

                $banca2     = trim((string) ($row['Banca2'] ?? ''));
                $ibanBanca2 = trim((string) ($row['IBAN_Banca2'] ?? ''));
                $ccBanca2   = trim((string) ($row['Banca2_num_cc'] ?? ''));

                $numCcp1    = trim((string) ($row['num_ccp'] ?? ''));
                $ibanPosta1 = trim((string) ($row['IBAN_Posta'] ?? ''));
                $numCcp2    = trim((string) ($row['num_ccp_2'] ?? ''));
                $ibanPosta2 = trim((string) ($row['IBAN_Posta_2'] ?? ''));

                $stabile = null;
                if ($cfLegacy !== '') {
                    $stabile = Stabile::query()->where('codice_fiscale', $cfLegacy)->first();
                }
                if (! $stabile) {
                    $stabile = Stabile::query()
                        ->where('codice_stabile', $codLegacy)
                        ->orWhere('cod_stabile', $codLegacy)
                        ->orWhere('codice_stabile', ltrim($codLegacy, '0'))
                        ->first();
                }

                if ($stabile) {
                    $noteFull = array_filter([$note1, $note2]);
                    $noteStr  = implode("\n", $noteFull);

                    // Leggi anagr_casse dal file generale_stabile.mdb dello stabile se presente
                    $casseAnagr = [];
                    $stabileDirCandidates = [
                        "/mnt/gescon-archives/gescon/{$codLegacy}/generale_stabile.mdb",
                        "/mnt/gescon-archives/gescon/" . sprintf('%04d', (int)$stabile->codice_stabile) . "/generale_stabile.mdb",
                    ];
                    foreach ($stabileDirCandidates as $gsMdb) {
                        if (file_exists($gsMdb) && is_readable($gsMdb)) {
                            $pCasse = new \Symfony\Component\Process\Process(['mdb-export', $gsMdb, 'anagr_casse']);
                            $pCasse->run();
                            if ($pCasse->isSuccessful() && $pCasse->getOutput()) {
                                $cStream = fopen('php://memory', 'r+');
                                fwrite($cStream, $pCasse->getOutput());
                                rewind($cStream);
                                $cHeader = fgetcsv($cStream);
                                if ($cHeader) {
                                    while (($cData = fgetcsv($cStream)) !== false) {
                                        if (count($cData) === count($cHeader)) {
                                            $cRow = array_combine($cHeader, $cData);
                                            $casseAnagr[] = [
                                                'id_cassa'           => (int) ($cRow['Id_cassa'] ?? 0),
                                                'codice'             => trim((string) ($cRow['Codice'] ?? '')),
                                                'descrizione'        => trim((string) ($cRow['Descrizione'] ?? '')),
                                                'saldo_iniziale_euro'=> (float) ($cRow['Saldo_iniziale_EURO'] ?? 0),
                                            ];
                                        }
                                    }
                                }
                                fclose($cStream);
                            }
                            break;
                        }
                    }

                    $bancherieAdv = [
                        'banca_1' => [
                            'banca'     => $banca1,
                            'iban'      => $ibanBanca1,
                            'conto_cc'  => $ccBanca1,
                            'abi'       => $abi1,
                            'cab'       => $cab1,
                            'sia'       => $sia1,
                            'cin'       => $cin1,
                        ],
                        'banca_2' => [
                            'banca'    => $banca2,
                            'iban'     => $ibanBanca2,
                            'conto_cc' => $ccBanca2,
                        ],
                        'posta_1' => [
                            'num_ccp' => $numCcp1,
                            'iban'    => $ibanPosta1,
                        ],
                        'posta_2' => [
                            'num_ccp' => $numCcp2,
                            'iban'    => $ibanPosta2,
                        ],
                        'anagr_casse' => $casseAnagr,
                    ];

                    $existingAdv = is_array($stabile->configurazione_avanzata) ? $stabile->configurazione_avanzata : [];
                    $mergedAdv   = array_merge($existingAdv, ['conti_correnti' => $bancherieAdv]);

                    $stabile->update([
                        'note'                  => $noteStr ?: $stabile->note,
                        'banca_principale'      => $banca1 ?: ($ibanPosta1 ? 'Poste Italiane' : $stabile->banca_principale),
                        'iban_principale'       => $ibanBanca1 ?: ($ibanPosta1 ?: $stabile->iban_principale),
                        'banca_secondaria'      => $banca2 ?: $stabile->banca_secondaria,
                        'iban_secondario'       => ($ibanBanca2 ?: $ibanPosta1) ?: $stabile->iban_secondario,
                        'configurazione_avanzata'=> $mergedAdv,
                    ]);

                    $updated++;
                    $this->line("  ✓ Stabile {$stabile->codice_stabile} ({$den}): Note, IBAN e " . count($casseAnagr) . " Casse (CCB/CCP/CON) sincronizzate.");
                }
            }
        }
        fclose($stream);

        $this->info("Completata sincronizzazione conti correnti e tabelle anagr_casse per {$updated} stabili!");
        return self::SUCCESS;
    }
}