<?php
namespace App\Controller\Reporting;
use App\Entity\PDossier;
use App\Entity\Pemploye;
use App\Entity\TCnssA00;
use App\Entity\TCnssA01;
use App\Entity\TCnssA02;
use App\Entity\TCnssA03;
use App\Entity\TCnssB00;
use App\Entity\TCnssB01;
use App\Entity\TCnssB02;
use App\Entity\TCnssB03;
use App\Entity\TCnssB04;
use App\Entity\TCnssB05;
use App\Entity\TCnssB06;
use App\Entity\LContract;
use App\Entity\PtypeCoti;
use App\Entity\Tbulletin;
use App\Entity\TbulletinLg;
use App\Entity\PArretTravailLg;
use App\Controller\ApiController;
use App\Entity\LmatriculationCoti;
use App\Entity\PnatureContract;
use App\Entity\PsituationFamiliale;
use App\Service\CalculPaieService;
use Doctrine\Persistence\ManagerRegistry;
use Symfony\Component\Finder\SplFileInfo;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\ResponseHeaderBag;
use Symfony\Component\HttpFoundation\BinaryFileResponse;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx as Reader;
#[Route('/reporting/cnss')]
class CnssController extends AbstractController
{
private $em;
private $calculPaieService;
private $api;
public function __construct(ManagerRegistry $doctrine, CalculPaieService $calculPaieService, ApiController $api)
{
$this->em = $doctrine->getManager();
$this->calculPaieService = $calculPaieService;
$this->api = $api;
}
#[Route('/', name: 'app_reporting_cnss', options: ['expose' => true])]
public function index(Request $request): Response
{
$operations = $this->api->check($this->getUser(), 'app_reporting_cnss', $this->em, $request);
if(!is_array($operations)) {
return $this->redirectToRoute('app_site');
}elseif(count($operations) == 0) {
return $this->render('includes/404.html.twig');
}
$affiliations = $this->em->createQueryBuilder()
->select('d.statutCimr')
->distinct()
->from(PDossier::class, 'd')
->where('d.statutCimr is not null')
->getQuery()->getResult();
// dd($affiliations);
return $this->render('reporting/cnss/index.html.twig', [
'operations' => $operations,
'affiliations' => $affiliations,
]);
}
#[Route('/app_reporting_cnss_list', name: 'app_reporting_cnss_list', options: ['expose' => true])]
public function app_reporting_cnss_list(Request $request): Response
{
$draw = $request->query->get('draw');
$start = $request->query->get('start') ?? 0;
$length = $request->query->get('length') ?? 10;
$search = $request->query->all('search')["value"];
$orderColumnIndex = $request->query->all('order')[0]['column'];
$orderColumn = $request->query->all("columns")[$orderColumnIndex]['name'];
$orderDir = $request->query->all('order')[0]['dir'] ?? 'asc';
$queryBuilder = $this->em->createQueryBuilder()
->select('cnss0.id as id, cnss0.periode,cnss0.created, cnss1.raisonSociale')
->from(TCnssA00::class, 'cnss0')
->innerJoin('cnss0.tCnssA01s', 'cnss1')
->Where('cnss0.active = 1');
if (!empty($search)) {
$queryBuilder->andWhere('(cnss0.periode LIKE :search OR cnss1.raisonSociale LIKE :search )')
->setParameter('search', "%$search%");
}
if (!empty($orderColumn)) {
$queryBuilder->orderBy("$orderColumn", $orderDir);
}
// dd($orderColumn);
$filteredRecords = count($queryBuilder->getQuery()->getResult());
// Paginate results
$queryBuilder->setFirstResult($start)
->setMaxResults($length);
$results = $queryBuilder->getQuery()->getResult();
// dd($results);
// dd($results);
$totalRecords = $this->em->createQueryBuilder()
->select('COUNT(cnss0.id)')
->from(TCnssA00::class, 'cnss0')
->innerJoin('cnss0.tCnssA01s', 'cnss1')
->Where('cnss0.active = 1')
->getQuery()
->getSingleScalarResult();
return new JsonResponse([
'draw' => $draw,
'recordsTotal' => $totalRecords,
'recordsFiltered' => $filteredRecords,
'data' => $results,
]);
}
#[Route('/app_reporting_cnss_import', name: 'app_reporting_cnss_import', options: ['expose' => true])]
public function app_reporting_cnss_import(Request $request): Response
{
$file = $request->files->get('file');
if(!$file){
return new JsonResponse('Veuillez import un fichier', 500);
}
$file = new SplFileInfo($file, '', '');
$cnssFile = explode("\n", $file->getContents());
// dd($cnssFile);
$tcnssA00 = null;
foreach($cnssFile as $cnss)
{
if(substr($cnss,0,3)=='A00')
{
$cnss00=trim($cnss);
$cnss00=trim($cnss00,'A00A0');
$tcnssA00 = $this->em->getRepository(TCnssA00::class)->findOneBy(['periode' => date('Ym', strtotime('last month')), 'typeEnreg' => 'A00', 'IdentiTransfert' =>$cnss00, 'cat' => 'A0', 'active' => true]);
if($tcnssA00) {
$tcnssA00->setActive(false);
}
// $periode=$this->calculPaieService->getPeriode(date('mY', strtotime('last month')));
$tcnssA00 = new TCnssA00();
$tcnssA00->setPeriode(date('Ym', strtotime('last month')));
$tcnssA00->setTypeEnreg('A00');
$tcnssA00->setIdentiTransfert($cnss00);
$tcnssA00->setCat('A0');
$tcnssA00->setFiller('');
$tcnssA00->setUserCreated($this->getUser());
$tcnssA00->setCreated(new \DateTime());
$this->em->persist($tcnssA00);
}
elseif(substr($cnss,0,3)=='A01')
{
$cnss01 =preg_split('/\s{2,}/', $cnss);
//dd($cnss01);
$Num_Affilie=substr($cnss01[0],3,7);
$Raison_Sociale=substr($cnss01[0],16);
$periode=substr($cnss01[0],10,-strlen($Raison_Sociale));
$C_Code=explode(" ", $cnss01[3]);
if(sizeof($C_Code) == 1)
{
$Code_Agence=substr(trim($C_Code[0]),0,-16);
$Date_Exig=substr(trim($C_Code[0]),10);
$Date_Emission=substr(trim($C_Code[0]),2,8);
$Code_Postal=null;
}
else{
$Code_Agence=substr(trim($C_Code[1]),0,-16);
$Date_Exig=substr(trim($C_Code[1]),10);
$Date_Emission=substr(trim($C_Code[1]),2,8);
$Code_Postal=$C_Code[0];
}
$tcnssA01 = new TCnssA01();
$tcnssA01->setCnssa00($tcnssA00);
$tcnssA01->setTypeEnreg('A01');
$tcnssA01->setNumAffilie($Num_Affilie);
$tcnssA01->setPeriode($periode);
$tcnssA01->setRaisonSociale($Raison_Sociale);
$tcnssA01->setAdresse($cnss01[1]);
$tcnssA01->setVille($cnss01[2]);
$tcnssA01->setCodePostal($Code_Postal);
$tcnssA01->setCodeAgence($Code_Agence);
$tcnssA01->setDateEmission($Date_Emission);
$tcnssA01->setDateExig($Date_Exig);
$this->em->persist($tcnssA01);
}
elseif(substr($cnss,0,3)=='A02'){
$cnss2=trim($cnss);
$nom_prenom=str_replace("'","''",trim(substr($cnss2,25,-20)));
$Num_Affilie=substr($cnss2,-strlen($cnss2)+3,7);
$Période=substr($cnss2,-strlen($cnss2)+10,6);
$Num_Assure=substr($cnss2,-strlen($cnss2)+16,9);
$Enfants=substr($cnss2,-20,2);
$AF_A_Payer=substr($cnss2,-18,6);
$AF_A_Deduire=substr($cnss2,-12,6);
$AF_Net_A_Payer=substr($cnss2,-6);
$tcnssA02 = new TCnssA02();
$tcnssA02->setCnssa00($tcnssA00);
$tcnssA02->setTypeEnreg('A02');
$tcnssA02->setNumAffilie($Num_Affilie);
$tcnssA02->setPeriode($Période);
$tcnssA02->setNumAssure($Num_Assure);
$tcnssA02->setNomPrenom($nom_prenom);
$tcnssA02->setEnfants($Enfants);
$tcnssA02->setAfAPayer($AF_A_Payer);
$tcnssA02->setAfADeduire($AF_A_Deduire);
$tcnssA02->setAfNetAPayer($AF_Net_A_Payer);
$this->em->persist($tcnssA02);
}
elseif(substr($cnss,0,3)=='A03'){
$cnss03=trim($cnss);
$Num_Affilie=substr($cnss03,-strlen($cnss03)+3,7);
$Période=substr($cnss03,-strlen($cnss03)+10,6);
$Nbr_Salaries=substr($cnss03,-strlen($cnss03)+16,6);
$T_Enfants=substr($cnss03,-56,5);
$T_AF_A_Payer=substr($cnss03,-51,12);
$T_AF_A_Deduire=substr($cnss03,-39,12);
$T_AF_Net_A_Payer=substr($cnss03,-27,12);
$T_Num_Imma=substr($cnss03,-15);
$tcnssA03 = new TCnssA03();
$tcnssA03->setCnssa00($tcnssA00);
$tcnssA03->setTypeEnreg('A03');
$tcnssA03->setNumAffilie($Num_Affilie);
$tcnssA03->setPeriode($Période);
$tcnssA03->setNbrSalaries($Nbr_Salaries);
$tcnssA03->setTEnfants($T_Enfants);
$tcnssA03->setTAfAPayer($T_AF_A_Payer);
$tcnssA03->setTAfADeduire($T_AF_A_Deduire);
$tcnssA03->setTAfNetAPayer($T_AF_Net_A_Payer);
$tcnssA03->setTNumImma($T_Num_Imma);
$this->em->persist($tcnssA03);
}
}
$this->em->flush();
$nbr_salaries=0;
$jour_decale=0;
$num_imma=0;
$salaireR=0;
$salaireP=0;
$T_Ctr=0;
$T_nbr_salaries=0;
$T_jour_decale=0;
$T_num_imma=0;
$T_salaireR=0;
$T_salaireP=0;
$T_Ctr6=0;
$periode = date('Ym', strtotime('last month'));
$periode1=substr($periode, 4, strlen($periode)-4).substr($periode,0, 4);
$periode1 = $this->calculPaieService->getPeriode($periode1);
$tcnssB00 = $this->em->getRepository(TCnssB00::class)->findOneBy(['cnssa00' => $tcnssA00, 'identiTransfert' => $tcnssA00->getIdentiTransfert(), 'periode' => $periode]);
if(!$tcnssB00) {
$num_affilie = substr($tcnssA00->getIdentiTransfert(), 0, 7);
$tcnssB00 = new TCnssB00();
$tcnssB00->setTypeEnreg('B00');
$tcnssB00->setCnssa00($tcnssA00);
$tcnssB00->setIdentiTransfert($tcnssA00->getIdentiTransfert());
$tcnssB00->setCat('B0');
$tcnssB00->setPeriode($periode);
$this->em->persist($tcnssB00);
$this->em->refresh($tcnssA00);
foreach ($tcnssA00->getTCnssA01s() as $key => $value) {
$tcnssB01 = new TCnssB01();
$tcnssB01->setTypeEnreg('B01');
$tcnssB01->setCnssb00($tcnssB00);
$tcnssB01->setNumAffilie($value->getNumAffilie());
$tcnssB01->setPeriode($value->getPeriode());
$tcnssB01->setRaisonSociale($value->getRaisonSociale());
$tcnssB01->setAdresse($value->getAdresse());
$tcnssB01->setVille($value->getVille());
$tcnssB01->setCodePostal($value->getCodePostal());
$tcnssB01->setCodeAgence($value->getCodeAgence());
$tcnssB01->setDateEmission($value->getDateEmission());
$tcnssB01->setDateExig($value->getDateEmission());
$this->em->persist($tcnssB01);
}
$typesContracts = $this->em->getRepository(PnatureContract::class)->findBy(['id' => [1, 18]]);
foreach($tcnssA00->getTCnssA02s() as $value)
{
$numCat=0;
$categorie='';
$reel = 0;
$montantReel = 0;
$plafond = 0;
$montantPlafond = 0;
// $cnssPersonnel = $this->em->getRepository(LmatriculationCoti::class)->findOneBy(['code' => $value->getNumAssure(), 'type_id' => $this->em->getRepository(PtypeCoti::class)->find(1), 'active' => true]);
$contract = $this->em->getRepository(LContract::class)->findOneBy(['cnss' => $value->getNumAssure(), 'active' => true, 'pnatureContract' => $typesContracts]);
if(!$contract) {
$contract = $this->em->getRepository(LContract::class)->findOneBy(['cnss' => $value->getNumAssure(), 'pnatureContract' => $typesContracts]);
}
// $contract = null;
if($contract and $contract->getActive() == 1) {
if($contract->getPnatureContract() and $contract->getPnatureContract()->getType()->getId() == 1) {
$queryBuilder = $this->em->createQueryBuilder();
$montantImposable = $queryBuilder->select('SUM(TbulletinLg.montant) as reel, CASE WHEN (SUM(TbulletinLg.montant) > 6000) THEN 6000 ELSE SUM(TbulletinLg.montant) END as plafond')
->from(TbulletinLg::class, 'TbulletinLg')
->innerJoin('TbulletinLg.bulletin', 'bulletin')
->innerJoin('TbulletinLg.rubrique', 'rubrique')
->innerJoin('bulletin.dossier', 'dossier')
->where('bulletin.contract = :contract')
->andWhere('rubrique.imposable = 1')
->andWhere('TbulletinLg.active = 1')
->andWhere('dossier.affiliation = :affiliation')
->andWhere('bulletin.periode = :periode1')
->setParameter('contract', $contract)
->setParameter('affiliation', $value->getNumAffilie())
->setParameter('periode1', $periode1)
->getQuery()
->getOneOrNullResult()
;
if($montantImposable['reel'] === null) {
$categorie='SO';
$numCat=1;
$nbrJour=0;
} else {
$reel = (int)number_format($montantImposable['reel'], 2, '', '');
$montantReel = $montantImposable['reel'];
$plafond = (int)number_format($montantImposable['plafond'], 2, '', '');
$montantPlafond = $montantImposable['plafond'];
$nbrJour = 26 - $this->em->getRepository(PArretTravailLg::class)->getNombreJoursArret($contract->getId(), $periode1);
$nbrJour = sprintf("%02d", substr($nbrJour, 0, 2));
if($nbrJour < 26) {
$motif = $this->em->getRepository(PArretTravailLg::class)->getMotifArretTravail($contract->getId(), $periode1);
$categorie = $motif['abreviation'] ? $motif['abreviation'] : '';
switch ($categorie) {
case 'DE':
$numCat=2;
break;
case 'IT':
$numCat=3;
break;
case 'IL':
$numCat=4;
break;
case 'AT':
$numCat=5;
break;
case 'CS':
$numCat=6;
break;
case 'MS':
$numCat=7;
break;
case 'MP':
$numCat=8;
break;
default:
$numCat=0;
}
}
}
} else {
$categorie='SO';
$numCat=1;
$nbrJour=0;
}
} else {
$categorie='SO';
$numCat=1;
$nbrJour=0;
}
$S_Ctr=intval($numCat+$value->getAfAPayer()+$nbrJour+$value->getEnfants()+$value->getNumAssure()+$reel+$plafond);
// dd($S_Ctr);
$tcnssB02 = new TCnssB02();
$tcnssB02->setCnssb00($tcnssB00);
$tcnssB02->setMontantPlaf($montantPlafond);
$tcnssB02->setMontantReel($montantReel);
$tcnssB02->setContract($contract);
$tcnssB02->setTypeEnreg('B02');
$tcnssB02->setNumAffilie($value->getNumAffilie());
$tcnssB02->setPeriode($periode);
$tcnssB02->setNumAssure($value->getNumAssure());
$tcnssB02->setNomPrenom(str_replace("'","''",$value->getNomPrenom()));
$tcnssB02->setEnfants($value->getEnfants());
$tcnssB02->setAfAPayer($value->getAfAPayer());
$tcnssB02->setAfADeduire($value->getAfADeduire());
$tcnssB02->setAfNetAPayer($value->getAfNetAPayer());
$tcnssB02->setSite($contract ? $contract->getDossier()->getCode() : '');
$tcnssB02->setNJoursDeclares($nbrJour);
$tcnssB02->setLSituation($categorie);
$tcnssB02->setLSituationNum($numCat);
$tcnssB02->setNSalaireReel($reel);
$tcnssB02->setNSalairePlaf($plafond);
$tcnssB02->setSCtr($S_Ctr);
$this->em->persist($tcnssB02);
$jour_decale = $jour_decale+$nbrJour;
$salaireR = intval($salaireR+$reel);
$salaireP=$salaireP+$plafond;
$T_Ctr = $T_Ctr + $S_Ctr;
}
// die;
foreach($tcnssA00->getTCnssA03s() as $value)
{
$tcnssB03 = new TCnssB03();
$tcnssB03->setCnssb00($tcnssB00);
$tcnssB03->setTypeEnreg('B03');
$tcnssB03->setNumAffilie($value->getNumAffilie());
$tcnssB03->setPeriode($periode);
$tcnssB03->setNbrSalaries($value->getNbrSalaries());
$tcnssB03->setTEnfants($value->getTEnfants());
$tcnssB03->setTAfAPayer($value->getTAfAPayer());
$tcnssB03->setTAfADeduire($value->getTAfADeduire());
$tcnssB03->setTAfNetAPayer($value->getTAfNetAPayer());
$tcnssB03->setTNumImma($value->getTNumImma());
$tcnssB03->setTAfAReverser($value->getTAfAReverser());
$tcnssB03->setTJoursDeclares($jour_decale);
$tcnssB03->setTSalaireReel($salaireR);
$tcnssB03->setTSalairePlaf($salaireP);
$tcnssB03->setTCtr($T_Ctr);
$this->em->persist($tcnssB03);
$T_nbr_salaries=$T_nbr_salaries+$value->getNbrSalaries();
$T_jour_decale=$T_jour_decale+$jour_decale;
$T_num_imma=$T_num_imma+$value->getTNumImma();
$T_salaireR=$T_salaireR+$salaireR;
$T_salaireP=$T_salaireP+$salaireP;
$T_Ctr6=$T_Ctr6+$T_Ctr;
}
$jour_decale=0;
$salaireR=0;
$salaireP=0;
$T_Ctr=0;
// dd('amine');
$personnesNoDeclares = $this->em->getRepository(Tbulletin::class)->getPersonneNoDeclarer($periode1, $num_affilie, $periode);
// $personnesNoDeclares = [];
// dd($personnesNoDeclares);
if(count($personnesNoDeclares) > 0)
{
foreach($personnesNoDeclares as $row4)
{
$contract = $this->em->getRepository(LContract::class)->find($row4['contractId']);
// $sql = "select b.Nom,B.Prénom,b.CIN,REPLACE(CAST(b.SBI AS NUMERIC(18,2)),'.','') as Reel ,REPLACE(CAST(b.SBI_Plaf AS NUMERIC(18,2)),'.','') as Plafond from Q_SBI_Vir b where Période=$periode1 and ID_Employer='".$row4["ID_Employer"]."'";
// $stmt4 = $this->getDoctrine()->getConnection()->prepare($sql);
// $stmt4->execute();
// $datab04 = $stmt4->fetch();
// $request = "select REPLACE(CAST(SUM(TbulletinLg.montant) AS NUMERIC(18,2)),'.','') as Reel, CASE WHEN (SUM(TbulletinLg.montant) > 6000) THEN 600000 ELSE REPLACE(CAST(SUM(TbulletinLg.montant) AS NUMERIC(18,2)),'.','') END as plafond"
$queryBuilder = $this->em->createQueryBuilder();
$montantImposable = $queryBuilder->select('SUM(TbulletinLg.montant) as reel, CASE WHEN (SUM(TbulletinLg.montant) > 6000) THEN 6000 ELSE SUM(TbulletinLg.montant) END as plafond')
->from(TbulletinLg::class, 'TbulletinLg')
->innerJoin('TbulletinLg.bulletin', 'bulletin')
->innerJoin('TbulletinLg.rubrique', 'rubrique')
->where('bulletin.contract = :contract')
->andWhere('rubrique.imposable = 1')
->andWhere('TbulletinLg.active = 1')
->andWhere('bulletin.periode = :periode1')
->setParameter('contract', $contract)
->setParameter('periode1', $periode1)
->getQuery()
->getOneOrNullResult()
;
$reel = (int)number_format($montantImposable['reel'], 2, '', '');
$plafond = (int)number_format($montantImposable['plafond'], 2, '', '');
if($reel == 0) {
continue;
}
// $sql = "select lg.Base_App_HRM from pBulletins b inner join pBulletins_LG lg on b.ID_Bulletin=lg.ID_Bulletin where b.obs is null and lg.ID_Eléments='R10001' and Période=$periode1 and ID_Employer='".$row4["ID_Employer"]."'";
// $stmt4 = $this->getDoctrine()->getConnection()->prepare($sql);
// $stmt4->execute();
// $dataBaselg4 = $stmt4->fetch();
$nbrJour = 26 - $this->em->getRepository(PArretTravailLg::class)->getNombreJoursArret($row4['contractId'], $periode1);
$nbrJour = substr($nbrJour,0,2);
$str = str_replace('\'',' ',$contract->getEmploye()->getNom().' '.$contract->getEmploye()->getPrenom());
if (is_numeric($row4["CNSS"]))
{
$cnss = $row4["CNSS"];
}else{
$cnss=0;
}
// dump($cnss);
$S_Ctr=round($plafond + $reel+$cnss+$nbrJour);
$tcnssB04 = new TCnssB04();
$tcnssB04->setCnssb00($tcnssB00);
$tcnssB04->setContract($contract);
$tcnssB04->setTypeEnreg('B04');
$tcnssB04->setNumAffilie($num_affilie);
$tcnssB04->setPeriode($periode);
$tcnssB04->setNumAssure($row4["CNSS"]);
$tcnssB04->setNomPrenom($str);
$tcnssB04->setNumCin($contract->getEmploye()->getCin());
$tcnssB04->setSite($contract->getDossier()->getCode());
$tcnssB04->setNbrJours($nbrJour);
$tcnssB04->setSalReel($reel);
$tcnssB04->setSalPlaf($plafond);
$tcnssB04->setSCtr($S_Ctr);
$tcnssB04->setMontantReel($montantImposable['reel']);
$tcnssB04->setMontantPlaf($montantImposable['plafond']);
$this->em->persist($tcnssB04);
// $stmt4 = $this->getDoctrine()->getConnection()->prepare($sql4);
// $stmt4->execute();
$nbr_salaries++;
$jour_decale=$jour_decale+$nbrJour;
$num_imma=$num_imma+$cnss;
$salaireR=$salaireR+$reel;
$salaireP=$salaireP+$plafond;
//dd($sql4);
}
} else {
$tcnssB04 = new TCnssB04();
$tcnssB04->setCnssb00($tcnssB00);
$tcnssB04->setTypeEnreg('B04');
$tcnssB04->setNumAffilie($num_affilie);
$tcnssB04->setPeriode($periode);
$tcnssB04->setNumAssure(0);
$tcnssB04->setNbrJours(0);
$tcnssB04->setSalReel(0);
$tcnssB04->setSalPlaf(0);
$this->em->persist($tcnssB04);
// $sql4="INSERT INTO cnss_b04( Type_Enreg, Num_Affilie, Période, Num_Assure, Nbr_Jours,Sal_Reel, Sal_Plaf) VALUES ('B04','$num_affilie','$periode','0','0','0','0')";
// $stmt4 = $this->getDoctrine()->getConnection()->prepare($sql4);
// $stmt4->execute();
}
// dd($num_imma);
$T_Ctr=$nbr_salaries+$jour_decale+$num_imma+$salaireR+$salaireP;
// $sql5="INSERT INTO cnss_b05(Type_Enreg, Num_Affilie, Période, Nbr_Salaries, T_Num_Imma, T_Jours_Declares, T_Salaire_Reel, T_Salaire_Plaf, T_Ctr) V
// ALUES ('B05','$num_affilie', '$periode','$nbr_salaries','$num_imma','$jour_decale','$salaireR','$salaireP','$T_Ctr')";
// $stmt5 = $this->getDoctrine()->getConnection()->prepare($sql5);
// $stmt5->execute();
$tcnssB05 = new TCnssB05();
$tcnssB05->setCnssb00($tcnssB00);
$tcnssB05->setTypeEnreg('B05');
$tcnssB05->setNumAffilie($num_affilie);
$tcnssB05->setPeriode($periode);
$tcnssB05->setNbrSalaries($nbr_salaries);
$tcnssB05->setTNumImma($num_imma);
$tcnssB05->setTJoursDeclares($jour_decale);
$tcnssB05->setTSalaireReel($salaireR);
$tcnssB05->setTSalairePlaf($salaireP);
$tcnssB05->setTCtr($T_Ctr);
$this->em->persist($tcnssB05);
$T_nbr_salaries=$T_nbr_salaries+$nbr_salaries;
$T_jour_decale=$T_jour_decale+$jour_decale;
$T_num_imma=$T_num_imma+$num_imma;
$T_salaireR=$T_salaireR+$salaireR;
$T_salaireP=$T_salaireP+$salaireP;
$T_Ctr6=$T_Ctr6+$T_Ctr;
// $sql6="INSERT INTO cnss_b06(Type_Enreg, Num_Affilie, Période, Nbr_Salaries, T_Num_Imma, T_Jours_Declares, T_Salaire_Reel, T_Salaire_Plaf, T_Ctr)
// VALUES ('B06','$num_affilie', '$periode','$T_nbr_salaries','$T_num_imma','$T_jour_decale','$T_salaireR','$T_salaireP','$T_Ctr6')";
// $stmt6 = $this->getDoctrine()->getConnection()->prepare($sql6);
// $stmt6->execute();
$tcnssB06 = new TCnssB06();
$tcnssB06->setCnssb00($tcnssB00);
$tcnssB06->setTypeEnreg('B06');
$tcnssB06->setNumAffilie($num_affilie);
$tcnssB06->setPeriode($periode);
$tcnssB06->setNbrSalaries($T_nbr_salaries);
$tcnssB06->setTNumImma($T_num_imma);
$tcnssB06->setTJoursDeclares($T_jour_decale);
$tcnssB06->setTSalaireReel($T_salaireR);
$tcnssB06->setTSalairePlaf($T_salaireP);
$tcnssB06->setTCtr($T_Ctr6);
$this->em->persist($tcnssB06);
}
$this->em->flush();
return new JsonResponse('Bien enregistrer');
}
#[Route('/app_reporting_cnss_export/{cnssa00}', name: 'app_reporting_cnss_export', options: ['expose' => true])]
public function app_reporting_cnss_export(TCnssA00 $cnssa00): Response
{
$myFile = 'DS_';
$myFinalData = '';
/* cnss_b00 */
$cnssB00 = $this->em->getRepository(TCnssB00::class)->findOneBy(['cnssa00' => $cnssa00]);
$table = TCnssB00::class;
$typeEnreg = $this->getFinalLength($cnssB00->getTypeEnreg(),$table, 'typeEnreg', 'AN');
$identiTransfert = $this->getFinalLength($cnssB00->getIdentiTransfert(), $table, 'identiTransfert', 'N');
$cat = $this->getFinalLength($cnssB00->getCat(), $table, 'cat', 'AN');
$filler = $this->getFinalLength($cnssB00->getFiller(), $table, 'filler', 'AN');
$myFinalData .= $typeEnreg . $identiTransfert . $cat . $filler.PHP_EOL;
// echo $myFinalData;
// die;
/* cnss_b01 */
// dd($cnssB01Data);
$table = TCnssB01::class;
foreach ($cnssB00->getTCnssB01s() as $cnssB01) {
$typeEnreg = $this->getFinalLength($cnssB01->getTypeEnreg(),$table, 'typeEnreg', 'AN');
$numAffilie = $this->getFinalLength($cnssB01->getNumAffilie(),$table, 'numAffilie', 'N');
$periode = $this->getFinalLength($cnssB01->getPeriode(),$table, 'periode', 'N');
$raisonSociale = $this->getFinalLength($cnssB01->getRaisonSociale(),$table, 'raisonSociale', 'AN');
$activite = $this->getFinalLength($cnssB01->getActivite(),$table, 'activite', 'AN');
$adresse = $this->getFinalLength($cnssB01->getAdresse(),$table, 'adresse', 'AN');
$ville = $this->getFinalLength($cnssB01->getVille(),$table, 'ville', 'AN');
$codePostal = $this->getFinalLength($cnssB01->getCodePostal(),$table, 'codePostal', 'AN');
$codeAgence = $this->getFinalLength($cnssB01->getCodeAgence(),$table, 'codeAgence', 'N');
$dateEmission = $this->getFinalLength($cnssB01->getDateEmission(),$table, 'dateEmission', 'N');
$dateExig = $this->getFinalLength($cnssB01->getDateExig(),$table, 'dateExig', 'N');
$myFinalData .= $typeEnreg . $numAffilie . $periode . $raisonSociale . $activite . $adresse . $ville . $codePostal . $codeAgence . $dateEmission . $dateExig.PHP_EOL;
}
$myFile .=$numAffilie.'_'.$cnssa00->getPeriode();
/* cnss_b02 */
// dd($cnssB02Data);
$table = TCnssB02::class;
// dd($table);
foreach ($cnssB00->getTCnssB02s() as $cnssB02) {
// dd($cnssB02->Num_Assure);
// if($cnssB02->getId() == 38) {
// $idEmployer = $this->getFinalLength($cnssB02->ID_Employer,'cnss_b02', 'ID_Employer', 'AN');
$typeEnreg = $this->getFinalLength($cnssB02->getTypeEnreg(),$table, 'typeEnreg', 'AN');
$numAffilie = $this->getFinalLength($cnssB02->getNumAffilie(),$table, 'numAffilie', 'N');
$periode = $this->getFinalLength($cnssB02->getPeriode(),$table, 'periode', 'N');
$numAssure = $this->getFinalLength($cnssB02->getNumAssure(),$table, 'numAssure', 'N');
$nomPrenom = $this->getFinalLength($cnssB02->getNomPrenom(),$table, 'nomPrenom', 'AN');
$enfants = $this->getFinalLength($cnssB02->getEnfants(),$table, 'enfants', 'N');
$aFAPayer = $this->getFinalLength($cnssB02->getAFAPayer(),$table, 'afAPayer', 'N');
$aFADeduire = $this->getFinalLength($cnssB02->getAfADeduire(),$table, 'afADeduire', 'N');
$aFNetAPayer = $this->getFinalLength($cnssB02->getAfNetAPayer(),$table, 'afNetAPayer', 'N');
$aFAReverser = $this->getFinalLength($cnssB02->getAfAReverser(),$table, 'afAReverser', 'N');
$nJoursDeclares = $this->getFinalLength($cnssB02->getNJoursDeclares(),$table, 'nJoursDeclares', 'NN');
$nSalaireReel = $this->getFinalLength($cnssB02->getNSalaireReel(),$table, 'nSalaireReel', 'NN');
$nSalairePlaf = $this->getFinalLength($cnssB02->getNSalairePlaf(),$table, 'nSalairePlaf', 'NN');
$lSituation = $this->getFinalLength($cnssB02->getLSituation(),$table, 'lSituation', 'AN');
$sCtr = $this->getFinalLength($cnssB02->getSCtr(),$table, 'sCtr', 'NN');
$filler = $this->getFinalLength($cnssB02->getFiller(),$table, 'filler', 'AN');
$myFinalData .= $typeEnreg . $numAffilie . $periode . $numAssure . $nomPrenom . $enfants . $aFAPayer . $aFADeduire . $aFNetAPayer . $aFAReverser . $nJoursDeclares . $nSalaireReel . $nSalairePlaf . $lSituation . $sCtr . $filler.PHP_EOL;
// dump($myFinalData);
// }
}
/* cnss_b03 */
// dd($cnssB03Data);
$table = TCnssB03::class;
foreach ($cnssB00->getTCnssB03s() as $cnssB03) {
// dd($cnssB02->Num_Assure);
$typeEnreg = $this->getFinalLength($cnssB03->getTypeEnreg(),$table, 'typeEnreg', 'AN');
$numAffilie = $this->getFinalLength($cnssB03->getNumAffilie(),$table, 'numAffilie', 'N');
$periode = $this->getFinalLength($cnssB03->getPeriode(),$table, 'periode', 'N');
$nbrSalaries = $this->getFinalLength($cnssB03->getNbrSalaries(),$table, 'nbrSalaries', 'N');
$enfants = $this->getFinalLength($cnssB03->getTEnfants(),$table, 'tEnfants', 'N');
$aFAPayer = $this->getFinalLength($cnssB03->getTAfAPayer(),$table, 'tAfAPayer', 'N');
$aFADeduire = $this->getFinalLength($cnssB03->getTAfADeduire(),$table, 'tAfADeduire', 'N');
$aFNetAPayer = $this->getFinalLength($cnssB03->getTAfNetAPayer(),$table, 'tAfNetAPayer', 'N');
$tNumImma = $this->getFinalLength($cnssB03->getTNumImma(),$table, 'tNumImma', 'N');
$aFAReverser = $this->getFinalLength($cnssB03->getTAfAReverser(),$table, 'tAfAReverser', 'N');
$nJoursDeclares = $this->getFinalLength($cnssB03->getTJoursDeclares(),$table, 'tJoursDeclares', 'NN');
$nSalaireReel = $this->getFinalLength($cnssB03->getTSalaireReel(),$table, 'tSalaireReel', 'NN');
$nSalairePlaf = $this->getFinalLength($cnssB03->getTSalairePlaf(),$table, 'tSalairePlaf', 'NN');
$sCtr = $this->getFinalLength($cnssB03->getTCtr(),$table, 'tCtr', 'NN');
$filler = $this->getFinalLength($cnssB03->getFiller(),$table, 'filler', 'AN');
$myFinalData .= $typeEnreg . $numAffilie . $periode . $nbrSalaries . $enfants . $aFAPayer . $aFADeduire . $aFNetAPayer . $tNumImma . $aFAReverser . $nJoursDeclares . $nSalaireReel . $nSalairePlaf . $sCtr . $filler.PHP_EOL;
}
/* cnss_b04 */
$table = TCnssB04::class;
foreach ($cnssB00->getTCnssB04s() as $cnssB04) {
// dd($cnssB04->getNumAssure() == 0 ? ' ' : $cnssB04->getNumAssure());
$typeEnreg = $this->getFinalLength($cnssB04->getTypeEnreg(),$table, 'typeEnreg', 'AN');
$numAffilie = $this->getFinalLength($cnssB04->getNumAffilie(),$table, 'numAffilie', 'N');
$periode = $this->getFinalLength($cnssB04->getPeriode(),$table, 'periode', 'N');
$numAssure = $this->getFinalLength($cnssB04->getNumAssure() == 0 ? '' : $cnssB04->getNumAssure() ,$table, 'numAssure', 'AN');
// $numAssure = $this->getFinalLength(
// $cnssB04->getNumAssure() == 0 ? ' ' : $cnssB04->getNumAssure(),
// $table,
// 'numAssure',
// $cnssB04->getNumAssure() == 0 ? 'AN' : 'N'
// );
$nomPrenom = $this->getFinalLength($cnssB04->getNomPrenom(),$table, 'nomPrenom', 'AN');
$numCIN = $this->getFinalLength($cnssB04->getNumCIN(),$table, 'numCin', 'AN');
$nbrJours = $this->getFinalLength($cnssB04->getNbrJours(),$table, 'nbrJours', 'NN');
$salReel = $this->getFinalLength($cnssB04->getSalReel(),$table, 'salReel', 'NN');
$salPlaf = $this->getFinalLength($cnssB04->getSalPlaf(),$table, 'salPlaf', 'NN');
$sCtr = $this->getFinalLength($cnssB04->getSCtr(),$table, 'sCtr', 'NN');
$filler = $this->getFinalLength($cnssB04->getFiller(),$table, 'filler', 'AN');
$myFinalData .= $typeEnreg . $numAffilie . $periode . $numAssure . $nomPrenom . $numCIN . $nbrJours . $salReel . $salPlaf . $sCtr . $filler.PHP_EOL;
}
/* cnss_b05 */
$table = TCnssB05::class;
// dd($cnssB05Data);
foreach ($cnssB00->getTCnssB05s() as $cnssB05) {
// dd($cnssB02->Num_Assure);
$typeEnreg = $this->getFinalLength($cnssB05->getTypeEnreg(),$table, 'typeEnreg', 'AN');
$numAffilie = $this->getFinalLength($cnssB05->getNumAffilie(),$table, 'numAffilie', 'N');
$periode = $this->getFinalLength($cnssB05->getPeriode(),$table, 'periode', 'N');
$nbrSalaries = $this->getFinalLength($cnssB05->getNbrSalaries(),$table, 'nbrSalaries', 'NN');
$tNumImma = $this->getFinalLength($cnssB05->getTNumImma(),$table, 'tNumImma', 'NN');
$tJoursDeclares = $this->getFinalLength($cnssB05->getTJoursDeclares(),$table, 'tJoursDeclares', 'NN');
$salReel = $this->getFinalLength($cnssB05->getTSalaireReel(),$table, 'tSalaireReel', 'NN');
$salPlaf = $this->getFinalLength($cnssB05->getTSalairePlaf(),$table, 'tSalairePlaf', 'NN');
$sCtr = $this->getFinalLength($cnssB05->getTCtr(),$table, 'tCtr', 'NN');
$filler = $this->getFinalLength($cnssB05->getFiller(),$table, 'filler', 'AN');
$myFinalData .= $typeEnreg . $numAffilie . $periode . $nbrSalaries . $tNumImma . $tJoursDeclares . $salReel . $salPlaf . $sCtr . $filler.PHP_EOL;
}
/* cnss_b06 */
// dd($cnssB06Data);
$table = TCnssB06::class;
foreach ($cnssB00->getTCnssB06s() as $cnssB06) {
// dd($cnssB02->Num_Assure);
$typeEnreg = $this->getFinalLength($cnssB06->getTypeEnreg(),$table, 'typeEnreg', 'AN');
$numAffilie = $this->getFinalLength($cnssB06->getNumAffilie(),$table, 'numAffilie', 'N');
$periode = $this->getFinalLength($cnssB06->getPeriode(),$table, 'periode', 'N');
$nbrSalaries = $this->getFinalLength($cnssB06->getNbrSalaries(),$table, 'nbrSalaries', 'NN');
$tNumImma = $this->getFinalLength($cnssB06->getTNumImma(),$table, 'tNumImma', 'NN');
$tJoursDeclares = $this->getFinalLength($cnssB06->getTJoursDeclares(),$table, 'tJoursDeclares', 'NN');
$salReel = $this->getFinalLength($cnssB06->getTSalaireReel(),$table, 'tSalaireReel', 'NN');
$salPlaf = $this->getFinalLength($cnssB06->getTSalairePlaf(),$table, 'tSalairePlaf', 'NN');
$sCtr = $this->getFinalLength($cnssB06->getTCtr(),$table, 'tCtr', 'NN');
$filler = $this->getFinalLength($cnssB06->getFiller(),$table, 'filler', 'AN');
$myFinalData .= $typeEnreg . $numAffilie . $periode . $nbrSalaries . $tNumImma . $tJoursDeclares . $salReel . $salPlaf . $sCtr . $filler.PHP_EOL;
}
$tempFileName = tempnam(sys_get_temp_dir(), 'text_file');
file_put_contents($tempFileName, $myFinalData);
// Create a BinaryFileResponse
$response = new BinaryFileResponse($tempFileName);
// Set the response headers
$response->headers->set('Content-Type', 'text/plain');
$response->headers->set('Content-Disposition', $response->headers->makeDisposition(
ResponseHeaderBag::DISPOSITION_ATTACHMENT,
$myFile.'.txt'
));
return $response;
}
// public function getFinalLengthCimr($data,$long, $type) {
// $diffrenceCaracterType = $long - (int)(strlen($data));
// if($diffrenceCaracterType==0) {
// return $data;
// }
// else {
// if($type == 'A') {
// return $data .str_repeat(' ', $diffrenceCaracterType);
// } else if ($type == 'N') {
// return str_repeat('0', $diffrenceCaracterType).$data ;
// }
// else if ($type == 'AN') {
// return $data.str_repeat(' ', $diffrenceCaracterType) ;
// }
// }
// }
public function getFinalLength($data,$table,$column, $type) {
$nom = trim($data);
if($column == 'nomPrenom' and $table=='App\Entity\TCnssB02') {
// dd(());
// $data = preg_split('/\s{2,}/', $nom); this old code because i found el kihel has more than one whitespace in her last name
$data = preg_split('/\s{3,}/', $nom);
if(count($data) < 2) {
$data = preg_split('/\s+/', $nom);
if (count($data) == 3) {
$data = [$data[0].' ' . $data[1], $data[2]];
//dd($data);
}
}
$diffrence = 30 - (int)strlen($data[0]);
$nom = $data[0] . str_repeat(' ', $diffrence);
$diffrence = 30 - (int)strlen($data[1]);
$prenom = $data[1] . str_repeat(' ', $diffrence);
// dump(strip_tags($prenom));
// dd( $nom . $prenom);
return $nom . $prenom;
}
$numberOfLengthColumn = $this->getNumberOfLength($table, $column);
if((strlen($data)) == $numberOfLengthColumn) {
return $data;
}
else {
$diffrenceCaracterType = $numberOfLengthColumn - (int)(strlen($data));
if($type == 'AN') {
return $data . str_repeat(' ', $diffrenceCaracterType);
} else if ($type == 'N') {
return $data . str_repeat('0', $diffrenceCaracterType);
}
else if ($type == 'NN') {
// dd($data);
return str_repeat('0', $diffrenceCaracterType).$data ;
}
}
}
public function getNumberOfLength($table, $column) {
// $em = $this->getDoctrine()->getManager()->getConnection();
// $sql = "SELECT CHARACTER_MAXIMUM_LENGTH FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = '$table' AND COLUMN_NAME = '$column'";
// $stmt = $em->prepare($sql);
// $stmt->execute();
// // dd($stmt->fetch(PDO::FETCH_OBJ)->CHARACTER_MAXIMUM_LENGTH);
// return $stmt->fetch(PDO::FETCH_OBJ)->CHARACTER_MAXIMUM_LENGTH;
// $entityManager = $this->getDoctrine()->getManager();
$classMetadata = $this->em->getClassMetadata($table);
$length = $classMetadata->getFieldMapping($column)['length'];
return $length;
}
#[Route('/import', name: 'app_reporting_import_cnss', options: ['expose' => true])]
public function app_reporting_import_cnss(Request $request): Response
{
$reader = new Reader();
$spreadsheet = $reader->load($request->files->get('file'));
$worksheet = $spreadsheet->getActiveSheet();
$spreadSheetArys = $worksheet->toArray();
unset($spreadSheetArys[0]);
foreach ($spreadSheetArys as $key => $sheet) {
$employe = $this->em->getRepository(Pemploye::class)->findOneBy(['id' => $sheet[0], 'cin' => $sheet[1]]);
if(!$employe) {
return new JsonResponse('Employe introuvable à la ligne '.($key + 1).' !', 500);
}
if($sheet[5] != '') {
$situation = $this->em->getRepository(PsituationFamiliale::class)->findOneBy(['Abreviation' => $sheet[5]]);
if(!$situation) {
return new JsonResponse('Situation introuvable à la ligne '.($key + 1).' !', 500);
}
$employe->setSituationFamilialeId($situation);
}
if($sheet[6] != '') {
$employe->setNbrPrisEnCharge($sheet[6]);
foreach ($employe->getContracts() as $key => $contract) {
$contract->setPriseEnCharge($sheet[6]);
}
}
if($sheet[4] != '') {
foreach ($employe->getContracts() as $key => $contract) {
$contract->setCnss(trim($sheet[4]));
}
}
if($sheet['7'] != '') {
foreach ($employe->getContracts() as $key => $contract) {
$contract->setDateCnss(new \DateTime($sheet['7']));
}
}
}
$this->em->flush();
return new JsonResponse('Bien enregistre !');
}
}