AppFixtures.php

<?php

namespace App\DataFixtures;

use App\Entity\Commune;
use App\Entity\Departement;
use App\Entity\Region;
use App\Entity\Taxe;
use App\Entity\TypeTaxe;
use App\Service\CommuneService;
use App\Service\DepartementService;
use App\Service\RegionService;
use App\Service\TypeTaxeService;
use Doctrine\Bundle\FixturesBundle\Fixture;
use Doctrine\Persistence\ObjectManager;

class AppFixtures extends Fixture
{
	public function __construct(
		private RegionService $regionService,
		private DepartementService $departementService,
		private CommuneService $communeService,
		private TypeTaxeService $typeTaxeService,
	) {}

	const DATA_FILES = [
		'data/REI_2018.csv',
		'data/REI_COMPLET_2019.csv',
		'data/REI_COMPLET_2020.csv',
		'data/REI_2021.csv',
		'data/REI_2022.csv',
	];

	const BATCH_SIZE = 1000;

	public function load(ObjectManager $manager): void {
		ini_set('memory_limit', -1); // les fichiers excels sont tellement énormes qu'il faut beaucoup de RAM

		$this->loadCsv($manager);

		$manager->flush();
	}

	public function loadCsv(ObjectManager $manager): void {
		// $pool = new ApcuCachePool();
		// $simpleCache = new SimpleCacheBridge($pool);

		// \PhpOffice\PhpSpreadsheet\Settings::setCache($simpleCache);

		$typeTFPNB = new TypeTaxe();
		$typeTFPNB->setCode(TypeTaxe::TFPNB_CODE);
		$typeTFPNB->setLabel("Taxe foncière sur les propriétés non bâties");
		$manager->persist($typeTFPNB);

		$typeTFPB = new TypeTaxe();
		$typeTFPB->setCode(TypeTaxe::TFPB_CODE);
		$typeTFPB->setLabel("Taxe foncière sur les propriétés bâties");
		$manager->persist($typeTFPB);

		$typeTH = new TypeTaxe();
		$typeTH->setCode(TypeTaxe::TH_CODE);
		$typeTH->setLabel("Taxe d'habitation");
		$manager->persist($typeTH);

		$typeCFE = new TypeTaxe();
		$typeCFE->setCode(TypeTaxe::CFE_CODE);
		$typeCFE->setLabel("Cotisation foncière des entreprises");
		$manager->persist($typeCFE);
		
		$manager->flush();

		$departements = [];
		$communes = [];
		$regions = [];

		foreach (self::DATA_FILES as $path) {
			$explodedPath = explode('_', str_replace('.csv', '', $path));
			$annee = intval(end($explodedPath));

			echo $annee . "\r\n";

			$depNumCol = null;
			$depNomCol = null;
			$regNomCol = null;
			$comNumCol = null;
			$comNomCol = null;
			$tauxTFPNBCol = null;
			$volTFPNBCol = null;
			$tauxTFPBCol = null;
			$volTFPBCol = null;
			$tauxTHCol = null;
			$volTHCol = null;
			$tauxCFECol = null;
			$volCFECol = null;

			$colsFound = false;

			switch ($annee) {
				case 2018:
					$colsFound = true;

					$depNumCol = 1;
					$depNomCol = 935;
					$regNomCol = 936;
					$comNumCol = 3;
					$comNomCol = 10;
					$tauxTFPNBCol = 13;
					$volTFPNBCol = 14;
					$tauxTFPBCol = 68;
					$volTFPBCol = 69;
					$tauxTHCol = 167;
					$volTHCol = 168;
					$tauxCFECol = 327;
					$volCFECol = 328;
					break;
				case 2019:
					$colsFound = true;

					$depNumCol = 1;
					$depNomCol = 1010;
					$regNomCol = 1011;
					$comNumCol = 3;
					$comNomCol = 10;
					$tauxTFPNBCol = 13;
					$volTFPNBCol = 14;
					$tauxTFPBCol = 68;
					$volTFPBCol = 69;
					$tauxTHCol = 185;
					$volTHCol = 186;
					$tauxCFECol = 355;
					$volCFECol = 356;
					break;
				case 2020:
					$colsFound = true;

					$depNumCol = 1;
					$depNomCol = 1066;
					$regNomCol = 1067;
					$comNumCol = 3;
					$comNomCol = 10;
					$tauxTFPNBCol = 13;
					$volTFPNBCol = 14;
					$tauxTFPBCol = 68;
					$volTFPBCol = 69;
					$tauxTHCol = 185;
					$volTHCol = 186;
					$tauxCFECol = 347;
					$volCFECol = 348;
					break;
				case 2021:
					$colsFound = true;

					$depNumCol = 1;
					$depNomCol = 1108;
					$regNomCol = 1109;
					$comNumCol = 3;
					$comNomCol = 10;
					$tauxTFPNBCol = 13;
					$volTFPNBCol = 14;
					$tauxTFPBCol = 68;
					$volTFPBCol = 69;
					$tauxTHCol = 171;
					$volTHCol = 172;
					$tauxCFECol = 322;
					$volCFECol = 323;
					break;
				case 2022:
					$colsFound = true;

					$depNumCol = 1;
					$depNomCol = 1145;
					$regNomCol = 1146;
					$comNumCol = 3;
					$comNomCol = 10;
					$tauxTFPNBCol = 13;
					$volTFPNBCol = 14;
					$tauxTFPBCol = 68;
					$volTFPBCol = 69;
					$tauxTHCol = 175;
					$volTHCol = 176;
					$tauxCFECol = 332;
					$volCFECol = 333;
					break;
			}

			if (!$colsFound) {
				continue;
			}

			$titleLine = true;
			$i = 0;
			if (($handle = fopen($path, "r")) !== FALSE) {
				while (($data = fgetcsv($handle, separator: ",")) !== FALSE) {
					if ($titleLine) {
						$titleLine = false;
						continue;
					}

					$depNum = intval(self::decoderExcelHex($data[$depNumCol - 1] ?? ''));
					$depNom = self::decoderExcelHex($data[$depNomCol - 1] ?? '');
					$regNom = self::decoderExcelHex($data[$regNomCol - 1] ?? '');
					$comNum = intval(self::decoderExcelHex($data[$comNumCol - 1] ?? ''));
					$comNom = self::decoderExcelHex($data[$comNomCol - 1] ?? '');
					$tauxTFPNB = floatval(self::decoderExcelHex($data[$tauxTFPNBCol - 1] ?? ''));
					$volTFPNB = floatval(self::decoderExcelHex($data[$volTFPNBCol - 1] ?? ''));
					$tauxTFPB = floatval(self::decoderExcelHex($data[$tauxTFPBCol - 1] ?? ''));
					$volTFPB = floatval(self::decoderExcelHex($data[$volTFPBCol - 1] ?? ''));
					$tauxTH = floatval(self::decoderExcelHex($data[$tauxTHCol - 1] ?? ''));
					$volTH = floatval(self::decoderExcelHex($data[$volTHCol - 1] ?? ''));
					$tauxCFE = floatval(self::decoderExcelHex($data[$tauxCFECol - 1] ?? ''));
					$volCFE = floatval(self::decoderExcelHex($data[$volCFECol - 1] ?? ''));

					$args = [
						$annee, $depNum, $depNom, $regNom, $comNum, $comNom, $tauxTFPNB, $volTFPNB,
						$tauxTFPB, $volTFPB, $tauxTH, $volTH, $tauxCFE, $volCFE
					];

					$departement = $departements[$depNum] ?? null;
					$commune = $communes[$comNum] ?? null;

					if(is_null($departement)) {
						$region = $regions[$regNom] ?? null;

						if(is_null($region)) {
							$region = new Region();
							$region->setNom($regNom);

							$manager->persist($region);
						}

						$departement = new Departement();
						$departement->setCode($depNum);
						$departement->setNom($depNom);
						$departement->setRegion($region);

						$manager->persist($departement);
						$manager->flush();
						$departements[$depNum] = $departement;
						$regions[$regNom] ??= $region;
					}

					if (is_null($commune)) {
						$commune = new Commune();
						$commune->setCode($comNum);
						$commune->setNom($comNom);
						$commune->setDepartement($departement);

						$manager->persist($commune);
						$manager->flush();

						$communes[$comNum] = $commune;
					}

					$lineUseful = false;
					if(in_array(null, [
						$typeTFPNB->getId(),
						$typeTFPB->getId(),
						$typeTH->getId(),
						$typeCFE->getId(),
					])) {
					}

					if($tauxTFPNB > 0 && $volTFPNB > 0) {
						$taxeTFPNB = new Taxe();
						$taxeTFPNB->setType($typeTFPNB);
						$taxeTFPNB->setCommune($commune);
						$taxeTFPNB->setTaux($tauxTFPNB);
						$taxeTFPNB->setVolume($volTFPNB);
						$taxeTFPNB->setAnnee($annee);
						$manager->persist($taxeTFPNB);

						$lineUseful = true;
					}

					if($tauxTFPB > 0 && $volTFPB > 0) {
						$taxeTFPB = new Taxe();
						$taxeTFPB->setType($typeTFPB);
						$taxeTFPB->setCommune($commune);
						$taxeTFPB->setTaux($tauxTFPB);
						$taxeTFPB->setVolume($volTFPB);
						$taxeTFPB->setAnnee($annee);
						$manager->persist($taxeTFPB);

						$lineUseful = true;
					}

					if($tauxTH > 0 && $volTH > 0) {
						$taxeTH = new Taxe();
						$taxeTH->setType($typeTH);
						$taxeTH->setCommune($commune);
						$taxeTH->setTaux($tauxTH);
						$taxeTH->setVolume($volTH);
						$taxeTH->setAnnee($annee);
						$manager->persist($taxeTH);

						$lineUseful = true;
					}

					if($tauxCFE > 0 && $volCFE > 0) {
						$taxeCFE = new Taxe();
						$taxeCFE->setType($typeCFE);
						$taxeCFE->setCommune($commune);
						$taxeCFE->setTaux($tauxCFE);
						$taxeCFE->setVolume($volCFE);
						$taxeCFE->setAnnee($annee);
						$manager->persist($taxeCFE);

						$lineUseful = true;
					}

					if(!$lineUseful) {
						// print_r($args);
						continue;
					}
			
					if (++$i % self::BATCH_SIZE == 0) {
						$manager->flush();
					}
				}
				fclose($handle);
			}
		}
	}

	private static function decoderExcelHex($chaine) {
		// Le regex cherche le motif : _x suivi de 4 caractères hexadécimaux (0-9, A-F), suivi de _
		return trim(preg_replace_callback(
			'/_x([0-9A-Fa-f]{4})_/',
			function ($matches) {
				// $matches[1] contient le code hexadécimal (ex: "0020")
				// hexdec() convertit l'hexa en nombre décimal
				// mb_chr() convertit le nombre décimal en caractère (compatible UTF-8)
				return mb_chr(hexdec($matches[1]));
			},
			$chaine
		));
	}
}