Newer
Older
use App\Models\Department;
use App\Models\Taxe;
use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;
class TaxeSeeder extends Seeder
{
private array $departmentMap = [];
/**
* CSV column indices (0-based) from TRACE_REI.csv.
* Variable numbers in TRACE are 1-based, so index = variable_number - 1.
*/
private const COL_DEPARTMENT = 0; // Variable 1: DEP
private const COL_COMMUNE = 2; // Variable 3: COM
private const COL_COMMUNE_NAME = 9; // Variable 10: LIBCOM
private const COL_TFPNB_RATE = 12; // Variable 13: B12 - FNB COMMUNE TAUX NET
private const COL_TFPNB_AMOUNT = 13; // Variable 14: B13 - FNB COMMUNE MONTANT REEL
private const COL_TFPB_RATE = 67; // Variable 68: E12 - FB COMMUNE TAUX NET
private const COL_TFPB_AMOUNT = 68; // Variable 69: E13 - FB COMMUNE MONTANT REEL
private const COL_TH_RATE = 174; // Variable 175: H12 - TH COMMUNE TAUX NET
private const COL_TH_AMOUNT = 175; // Variable 176: H13 - TH COMMUNE MONTANT REEL
private const COL_CFE_RATE = 331; // Variable 332: P12 - CFE COMMUNE TAUX NET
private const COL_CFE_AMOUNT = 332; // Variable 333: P13 - CFE COMMUNE PRODUIT REEL NET
private const CHUNK_SIZE = 500;
private const DATA_DIR = '/srv/data';
/**
* CSV files to import: year => path relative to DATA_DIR.
* Add new years here as needed.
*/
private const CSV_FILES = [
2020 => 'rei-2020/REI_2020.csv',
2021 => 'rei-2021/REI_2021.csv',
2022 => 'rei-2022/REI_2022.csv',
];
public function run(): void
{
$this->departmentMap = Department::pluck('id', 'department_code')->toArray();
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
$totalImported = 0;
foreach (self::CSV_FILES as $year => $relativePath) {
$csvPath = self::DATA_DIR . '/' . $relativePath;
if (!file_exists($csvPath)) {
$this->command->warn("Skipping {$year}: file not found at {$csvPath}");
continue;
}
$count = $this->importYear($year, $csvPath);
$totalImported += $count;
}
if ($totalImported === 0) {
$this->command->error('No CSV files found. Make sure the data/ directory is mounted in the container.');
} else {
$this->command->info("Done. Total: {$totalImported} tax records imported.");
}
}
private function importYear(int $year, string $csvPath): int
{
$handle = fopen($csvPath, 'r');
if ($handle === false) {
$this->command->error("Cannot open: {$csvPath}");
return 0;
}
$header = fgetcsv($handle, 0, ';');
if ($header === false) {
$this->command->error("Empty CSV: {$csvPath}");
fclose($handle);
return 0;
}
$this->command->info("Importing {$year}...");
Taxe::query()->where('year', $year)->delete();
$batch = [];
$count = 0;
while (($row = fgetcsv($handle, 0, ';')) !== false) {
$dep = trim($row[self::COL_DEPARTMENT] ?? '');
$com = trim($row[self::COL_COMMUNE] ?? '');
$communeName = trim($row[self::COL_COMMUNE_NAME] ?? '');
if ($dep === '' || $com === '' || !isset($this->departmentMap[$dep])) {
continue;
}
$communeCode = $dep . $com;
if (strlen($communeCode) > 5) {
$communeCode = substr($communeCode, 0, 5);
}
$batch[] = [
'commune_code' => $communeCode,
'commune_name' => $communeName,
'department_id' => $this->departmentMap[$dep],
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
'tfpnb_amount' => $this->parseDecimal($row[self::COL_TFPNB_AMOUNT] ?? ''),
'tfpnb_percentage' => $this->parseDecimal($row[self::COL_TFPNB_RATE] ?? ''),
'tfpb_amount' => $this->parseDecimal($row[self::COL_TFPB_AMOUNT] ?? ''),
'tfpb_percentage' => $this->parseDecimal($row[self::COL_TFPB_RATE] ?? ''),
'th_amount' => $this->parseDecimal($row[self::COL_TH_AMOUNT] ?? ''),
'th_percentage' => $this->parseDecimal($row[self::COL_TH_RATE] ?? ''),
'cfe_amount' => $this->parseDecimal($row[self::COL_CFE_AMOUNT] ?? ''),
'cfe_percentage' => $this->parseDecimal($row[self::COL_CFE_RATE] ?? ''),
'year' => $year,
];
if (count($batch) >= self::CHUNK_SIZE) {
DB::table('taxes')->insert($batch);
$count += count($batch);
$batch = [];
$this->command->getOutput()->write("\r {$year}: {$count} rows...");
}
}
if (!empty($batch)) {
DB::table('taxes')->insert($batch);
$count += count($batch);
}
fclose($handle);
$this->command->newLine();
$this->command->info(" {$year}: {$count} records imported.");
return $count;
}
private function parseDecimal(string $value): float
{
$value = trim($value);
if ($value === '' || $value === '-') {
return 0.0;
}
$value = str_replace(',', '.', $value);
$value = str_replace(' ', '', $value);
return (float) $value;
}
}