src/Controller/Reporting/CnssController.php line 865

Open in your IDE?
  1. <?php
  3. namespace App\Controller\Reporting;
  5. use App\Entity\PDossier;
  6. use App\Entity\Pemploye;
  7. use App\Entity\TCnssA00;
  8. use App\Entity\TCnssA01;
  9. use App\Entity\TCnssA02;
  10. use App\Entity\TCnssA03;
  11. use App\Entity\TCnssB00;
  12. use App\Entity\TCnssB01;
  13. use App\Entity\TCnssB02;
  14. use App\Entity\TCnssB03;
  15. use App\Entity\TCnssB04;
  16. use App\Entity\TCnssB05;
  17. use App\Entity\TCnssB06;
  18. use App\Entity\LContract;
  19. use App\Entity\PtypeCoti;
  20. use App\Entity\Tbulletin;
  21. use App\Entity\TbulletinLg;
  22. use App\Entity\PArretTravailLg;
  23. use App\Controller\ApiController;
  24. use App\Entity\LmatriculationCoti;
  25. use App\Entity\PnatureContract;
  26. use App\Entity\PsituationFamiliale;
  27. use App\Service\CalculPaieService;
  28. use Doctrine\Persistence\ManagerRegistry;
  29. use Symfony\Component\Finder\SplFileInfo;
  30. use Symfony\Component\HttpFoundation\Request;
  31. use Symfony\Component\HttpFoundation\Response;
  32. use Symfony\Component\Routing\Annotation\Route;
  33. use Symfony\Component\HttpFoundation\JsonResponse;
  34. use Symfony\Component\HttpFoundation\ResponseHeaderBag;
  35. use Symfony\Component\HttpFoundation\BinaryFileResponse;
  36. use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
  37. use PhpOffice\PhpSpreadsheet\Reader\Xlsx as Reader;
  40. #[Route('/reporting/cnss')]
  41. class CnssController extends AbstractController
  42. {
  43.     private $em;
  44.     private $calculPaieService;
  45.     private $api;
  46.     public function __construct(ManagerRegistry $doctrine, CalculPaieService $calculPaieService, ApiController $api)
  47.     {
  48.         $this->em = $doctrine->getManager();
  49.         $this->calculPaieService = $calculPaieService;
  50.         $this->api = $api;
  51.         
  52.     }
  53.     #[Route('/', name: 'app_reporting_cnss', options: ['expose' => true])]
  54.     public function index(Request $request): Response
  55.     {
  56.         $operations = $this->api->check($this->getUser(), 'app_reporting_cnss', $this->em, $request);
  57.         if(!is_array($operations)) {
  58.             return $this->redirectToRoute('app_site');  
  59.         }elseif(count($operations) == 0) {
  60.             return $this->render('includes/404.html.twig');
  61.         }
  62.         $affiliations = $this->em->createQueryBuilder()
  63.         ->select('d.statutCimr')
  64.         ->distinct()
  65.         ->from(PDossier::class, 'd')
  66.         ->where('d.statutCimr is not null')
  67.         ->getQuery()->getResult();
  68.         // dd($affiliations);
  69.         return $this->render('reporting/cnss/index.html.twig', [
  70.             'operations' => $operations,
  71.             'affiliations' => $affiliations,
  72.         ]);
  73.     }
  74.     #[Route('/app_reporting_cnss_list', name: 'app_reporting_cnss_list', options: ['expose' => true])]
  75.     public function app_reporting_cnss_list(Request $request): Response
  76.     {
  77.         $draw = $request->query->get('draw');
  78.         $start = $request->query->get('start') ?? 0;
  79.         $length = $request->query->get('length') ?? 10;
  80.         $search = $request->query->all('search')["value"];
  81.         $orderColumnIndex = $request->query->all('order')[0]['column'];
  82.         $orderColumn = $request->query->all("columns")[$orderColumnIndex]['name'];
  83.         $orderDir = $request->query->all('order')[0]['dir'] ?? 'asc';
  84.         $queryBuilder = $this->em->createQueryBuilder()
  85.         ->select('cnss0.id as id, cnss0.periode,cnss0.created, cnss1.raisonSociale')
  86.         ->from(TCnssA00::class, 'cnss0')
  87.         ->innerJoin('cnss0.tCnssA01s', 'cnss1')
  88.         ->Where('cnss0.active = 1');
  90.         if (!empty($search)) {
  91.             $queryBuilder->andWhere('(cnss0.periode LIKE :search OR cnss1.raisonSociale LIKE :search )')
  92.                 ->setParameter('search', "%$search%");
  93.         }
  95.         if (!empty($orderColumn)) {
  96.             $queryBuilder->orderBy("$orderColumn", $orderDir);
  97.         }
  98.         // dd($orderColumn);
  99.         $filteredRecords = count($queryBuilder->getQuery()->getResult());
  100.         
  101.         // Paginate results
  102.         $queryBuilder->setFirstResult($start)
  103.             ->setMaxResults($length);
  105.         $results = $queryBuilder->getQuery()->getResult();
  106.         // dd($results);
  107.         
  108.         // dd($results);
  109.         $totalRecords = $this->em->createQueryBuilder()
  110.         ->select('COUNT(cnss0.id)')
  111.         ->from(TCnssA00::class, 'cnss0')
  112.         ->innerJoin('cnss0.tCnssA01s', 'cnss1')
  113.         ->Where('cnss0.active = 1')
  114.         ->getQuery()
  115.         ->getSingleScalarResult();
  117.         return new JsonResponse([
  118.             'draw' => $draw,
  119.             'recordsTotal' => $totalRecords,
  120.             'recordsFiltered' => $filteredRecords,
  121.             'data' => $results,
  122.         ]);
  123.     }
  125.     #[Route('/app_reporting_cnss_import', name: 'app_reporting_cnss_import', options: ['expose' => true])]
  126.     public function app_reporting_cnss_import(Request $request): Response
  127.     {
  128.         $file = $request->files->get('file');
  129.         if(!$file){
  130.             return new JsonResponse('Veuillez import un fichier', 500);
  131.         }
  133.         $file = new SplFileInfo($file, '', '');
  134.         
  135.         $cnssFile = explode("\n", $file->getContents());
  136.         // dd($cnssFile);
  137.         $tcnssA00 = null;
  139.         foreach($cnssFile as $cnss)
  140.         {
  141.           
  142.             if(substr($cnss,0,3)=='A00')
  143.             {
  144.                 $cnss00=trim($cnss);
  145.                 $cnss00=trim($cnss00,'A00A0');
  146.                 $tcnssA00 = $this->em->getRepository(TCnssA00::class)->findOneBy(['periode' => date('Ym', strtotime('last month')), 'typeEnreg' => 'A00', 'IdentiTransfert' =>$cnss00, 'cat' => 'A0', 'active' => true]);
  147.                 if($tcnssA00) {
  148.                     $tcnssA00->setActive(false);
  149.                 }
  150.                 // $periode=$this->calculPaieService->getPeriode(date('mY', strtotime('last month')));
  151.                 $tcnssA00 = new TCnssA00();
  152.                 $tcnssA00->setPeriode(date('Ym', strtotime('last month')));
  153.                 $tcnssA00->setTypeEnreg('A00');
  154.                 $tcnssA00->setIdentiTransfert($cnss00);
  155.                 $tcnssA00->setCat('A0');
  156.                 $tcnssA00->setFiller('');
  157.                 $tcnssA00->setUserCreated($this->getUser());
  158.                 $tcnssA00->setCreated(new \DateTime());
  160.                 $this->em->persist($tcnssA00);
  161.                 
  162.             }
  163.             elseif(substr($cnss,0,3)=='A01')
  164.             {
  165.                 $cnss01 =preg_split('/\s{2,}/', $cnss);
  166.                //dd($cnss01);
  167.                 $Num_Affilie=substr($cnss01[0],3,7);
  168.                 
  169.                 $Raison_Sociale=substr($cnss01[0],16);
  170.                 
  171.                 $periode=substr($cnss01[0],10,-strlen($Raison_Sociale));
  172.                 $C_Code=explode(" ", $cnss01[3]);
  173.                
  174.                 if(sizeof($C_Code) == 1)
  175.                 {
  176.                     $Code_Agence=substr(trim($C_Code[0]),0,-16);
  177.                     $Date_Exig=substr(trim($C_Code[0]),10);
  178.                     $Date_Emission=substr(trim($C_Code[0]),2,8);
  179.                     $Code_Postal=null;
  180.                 }
  181.                 else{
  182.                     $Code_Agence=substr(trim($C_Code[1]),0,-16);
  183.                     $Date_Exig=substr(trim($C_Code[1]),10);
  184.                     $Date_Emission=substr(trim($C_Code[1]),2,8);
  185.                     $Code_Postal=$C_Code[0];
  186.                 }
  188.                 $tcnssA01 = new TCnssA01();
  189.                 $tcnssA01->setCnssa00($tcnssA00);
  190.                 $tcnssA01->setTypeEnreg('A01');
  191.                 $tcnssA01->setNumAffilie($Num_Affilie);
  192.                 $tcnssA01->setPeriode($periode);
  193.                 $tcnssA01->setRaisonSociale($Raison_Sociale);
  194.                 $tcnssA01->setAdresse($cnss01[1]);
  195.                 $tcnssA01->setVille($cnss01[2]);
  196.                 $tcnssA01->setCodePostal($Code_Postal);
  197.                 $tcnssA01->setCodeAgence($Code_Agence);
  198.                 $tcnssA01->setDateEmission($Date_Emission);
  199.                 $tcnssA01->setDateExig($Date_Exig);
  200.                 
  201.                 $this->em->persist($tcnssA01);              
  202.               
  203.               
  204.             }
  205.             elseif(substr($cnss,0,3)=='A02'){
  206.                 
  207.                 $cnss2=trim($cnss);
  208.                 $nom_prenom=str_replace("'","''",trim(substr($cnss2,25,-20)));
  209.                 $Num_Affilie=substr($cnss2,-strlen($cnss2)+3,7);
  210.                 $Période=substr($cnss2,-strlen($cnss2)+10,6);
  211.                 $Num_Assure=substr($cnss2,-strlen($cnss2)+16,9);
  212.                 $Enfants=substr($cnss2,-20,2);
  213.                 $AF_A_Payer=substr($cnss2,-18,6);
  214.                 $AF_A_Deduire=substr($cnss2,-12,6);
  215.                 $AF_Net_A_Payer=substr($cnss2,-6);
  216.                 
  217.                 $tcnssA02 = new TCnssA02();
  218.                 $tcnssA02->setCnssa00($tcnssA00);
  219.                 $tcnssA02->setTypeEnreg('A02');
  220.                 $tcnssA02->setNumAffilie($Num_Affilie);
  221.                 $tcnssA02->setPeriode($Période);
  222.                 $tcnssA02->setNumAssure($Num_Assure);
  223.                 $tcnssA02->setNomPrenom($nom_prenom);
  224.                 $tcnssA02->setEnfants($Enfants);
  225.                 $tcnssA02->setAfAPayer($AF_A_Payer);
  226.                 $tcnssA02->setAfADeduire($AF_A_Deduire);
  227.                 $tcnssA02->setAfNetAPayer($AF_Net_A_Payer);
  229.                 $this->em->persist($tcnssA02);
  230.             }
  231.             elseif(substr($cnss,0,3)=='A03'){ 
  232.                 $cnss03=trim($cnss);
  233.                 $Num_Affilie=substr($cnss03,-strlen($cnss03)+3,7);    
  234.                 $Période=substr($cnss03,-strlen($cnss03)+10,6); 
  235.                 $Nbr_Salaries=substr($cnss03,-strlen($cnss03)+16,6); 
  236.                 $T_Enfants=substr($cnss03,-56,5);
  237.                 $T_AF_A_Payer=substr($cnss03,-51,12);
  238.                 $T_AF_A_Deduire=substr($cnss03,-39,12);
  239.                 $T_AF_Net_A_Payer=substr($cnss03,-27,12);
  240.                 $T_Num_Imma=substr($cnss03,-15);
  242.                 $tcnssA03 = new TCnssA03();
  243.                 $tcnssA03->setCnssa00($tcnssA00);
  244.                 $tcnssA03->setTypeEnreg('A03');
  245.                 $tcnssA03->setNumAffilie($Num_Affilie);
  246.                 $tcnssA03->setPeriode($Période);
  247.                 $tcnssA03->setNbrSalaries($Nbr_Salaries);
  248.                 $tcnssA03->setTEnfants($T_Enfants);
  249.                 $tcnssA03->setTAfAPayer($T_AF_A_Payer);
  250.                 $tcnssA03->setTAfADeduire($T_AF_A_Deduire);
  251.                 $tcnssA03->setTAfNetAPayer($T_AF_Net_A_Payer);
  252.                 $tcnssA03->setTNumImma($T_Num_Imma);
  254.                 $this->em->persist($tcnssA03);
  255.               
  256.             }
  257.        
  258.         
  259.         }
  261.         $this->em->flush();
  263.         $nbr_salaries=0;
  264.         $jour_decale=0;
  265.         $num_imma=0;
  266.         $salaireR=0;
  267.         $salaireP=0;
  268.         $T_Ctr=0;
  270.         $T_nbr_salaries=0;
  271.         $T_jour_decale=0;
  272.         $T_num_imma=0;
  273.         $T_salaireR=0;
  274.         $T_salaireP=0;
  275.         $T_Ctr6=0;
  277.         $periode = date('Ym', strtotime('last month'));
  278.         $periode1=substr($periode, 4, strlen($periode)-4).substr($periode,0, 4);
  280.         $periode1 = $this->calculPaieService->getPeriode($periode1);
  282.         $tcnssB00 = $this->em->getRepository(TCnssB00::class)->findOneBy(['cnssa00' => $tcnssA00, 'identiTransfert' => $tcnssA00->getIdentiTransfert(), 'periode' => $periode]);
  283.         if(!$tcnssB00) {
  284.             $num_affilie = substr($tcnssA00->getIdentiTransfert(), 0, 7);
  285.             $tcnssB00 = new TCnssB00();
  286.             $tcnssB00->setTypeEnreg('B00');
  287.             $tcnssB00->setCnssa00($tcnssA00);
  288.             $tcnssB00->setIdentiTransfert($tcnssA00->getIdentiTransfert());
  289.             $tcnssB00->setCat('B0');
  290.             $tcnssB00->setPeriode($periode);
  292.             $this->em->persist($tcnssB00);
  294.             $this->em->refresh($tcnssA00);
  295.             foreach ($tcnssA00->getTCnssA01s() as $key => $value) {
  296.                 $tcnssB01 = new TCnssB01();
  297.                 $tcnssB01->setTypeEnreg('B01');
  298.                 $tcnssB01->setCnssb00($tcnssB00);
  299.                 $tcnssB01->setNumAffilie($value->getNumAffilie());
  300.                 $tcnssB01->setPeriode($value->getPeriode());
  301.                 $tcnssB01->setRaisonSociale($value->getRaisonSociale());
  302.                 $tcnssB01->setAdresse($value->getAdresse());
  303.                 $tcnssB01->setVille($value->getVille());
  304.                 $tcnssB01->setCodePostal($value->getCodePostal());
  305.                 $tcnssB01->setCodeAgence($value->getCodeAgence());
  306.                 $tcnssB01->setDateEmission($value->getDateEmission());
  307.                 $tcnssB01->setDateExig($value->getDateEmission());
  309.                 $this->em->persist($tcnssB01);
  310.             }
  312.             $typesContracts = $this->em->getRepository(PnatureContract::class)->findBy(['id' => [1, 18]]);
  313.             foreach($tcnssA00->getTCnssA02s() as $value)
  314.             {
  315.                 $numCat=0;
  316.                 $categorie='';
  317.                 $reel = 0;
  318.                 $montantReel = 0;
  319.                 $plafond = 0;
  320.                 $montantPlafond = 0;
  321.                 
  322.                 // $cnssPersonnel = $this->em->getRepository(LmatriculationCoti::class)->findOneBy(['code' => $value->getNumAssure(), 'type_id' => $this->em->getRepository(PtypeCoti::class)->find(1), 'active' => true]);
  323.                 $contract = $this->em->getRepository(LContract::class)->findOneBy(['cnss' => $value->getNumAssure(), 'active' => true, 'pnatureContract' => $typesContracts]);
  324.                 if(!$contract) {
  325.                     $contract = $this->em->getRepository(LContract::class)->findOneBy(['cnss' => $value->getNumAssure(), 'pnatureContract' => $typesContracts]);
  326.                 }
  327.                 // $contract = null;
  328.                 if($contract and $contract->getActive() == 1) {
  329.                     if($contract->getPnatureContract() and $contract->getPnatureContract()->getType()->getId() == 1) {
  330.                         $queryBuilder = $this->em->createQueryBuilder();
  331.                         $montantImposable = $queryBuilder->select('SUM(TbulletinLg.montant) as reel, CASE WHEN (SUM(TbulletinLg.montant) > 6000) THEN 6000 ELSE SUM(TbulletinLg.montant) END as plafond')
  332.                                     ->from(TbulletinLg::class, 'TbulletinLg')
  333.                                     ->innerJoin('TbulletinLg.bulletin', 'bulletin')
  334.                                     ->innerJoin('TbulletinLg.rubrique', 'rubrique')
  335.                                     ->innerJoin('bulletin.dossier', 'dossier')
  336.                                     ->where('bulletin.contract = :contract')
  337.                                     ->andWhere('rubrique.imposable = 1')
  338.                                     ->andWhere('TbulletinLg.active = 1')
  339.                                     ->andWhere('dossier.affiliation = :affiliation')
  340.                                     ->andWhere('bulletin.periode = :periode1')
  341.                                     ->setParameter('contract', $contract)
  342.                                     ->setParameter('affiliation', $value->getNumAffilie())
  343.                                     ->setParameter('periode1', $periode1)
  344.                                     ->getQuery()
  345.                                     ->getOneOrNullResult()
  346.                         ;
  347.                         if($montantImposable['reel'] === null) {
  348.                             $categorie='SO'; 
  349.                             $numCat=1;
  350.                             $nbrJour=0; 
  351.                         } else {
  352.                             $reel =  (int)number_format($montantImposable['reel'], 2, '', '');
  353.                             $montantReel = $montantImposable['reel'];
  354.                             $plafond = (int)number_format($montantImposable['plafond'], 2, '', '');
  355.                             $montantPlafond = $montantImposable['plafond'];
  357.                             $nbrJour = 26 - $this->em->getRepository(PArretTravailLg::class)->getNombreJoursArret($contract->getId(), $periode1);
  358.                             $nbrJour = sprintf("%02d", substr($nbrJour, 0, 2));
  359.                             if($nbrJour < 26) {
  360.                                 $motif = $this->em->getRepository(PArretTravailLg::class)->getMotifArretTravail($contract->getId(), $periode1);
  361.                                 $categorie = $motif['abreviation'] ? $motif['abreviation'] : '';
  362.                                 switch ($categorie) {
  363.                                     case 'DE':
  364.                                         $numCat=2;
  365.                                         break;
  366.                                     case 'IT':
  367.                                         $numCat=3;
  368.                                         break;
  369.                                     case 'IL':
  370.                                         $numCat=4;
  371.                                         break;
  372.                                     case 'AT':
  373.                                         $numCat=5;
  374.                                         break;
  375.                                     case 'CS':
  376.                                         $numCat=6;
  377.                                         break;
  378.                                     case 'MS':
  379.                                         $numCat=7;
  380.                                         break;
  381.                                     case 'MP':
  382.                                         $numCat=8;
  383.                                         break;
  384.                                     default:
  385.                                         $numCat=0;
  386.                                 }
  387.                             }
  388.                         }
  391.                     } else {
  392.                         $categorie='SO'; 
  393.                         $numCat=1;
  394.                         $nbrJour=0; 
  395.                     }
  397.                 } else {
  398.                     $categorie='SO'; 
  399.                     $numCat=1;
  400.                     $nbrJour=0; 
  401.                 }
  402.                 
  403.              
  404.                 
  405.                 
  406.                
  407.                 $S_Ctr=intval($numCat+$value->getAfAPayer()+$nbrJour+$value->getEnfants()+$value->getNumAssure()+$reel+$plafond);
  408.                 // dd($S_Ctr);
  409.                 $tcnssB02 = new TCnssB02();
  410.                 $tcnssB02->setCnssb00($tcnssB00);
  411.                 $tcnssB02->setMontantPlaf($montantPlafond);
  412.                 $tcnssB02->setMontantReel($montantReel);
  413.                 $tcnssB02->setContract($contract);
  414.                 $tcnssB02->setTypeEnreg('B02');
  415.                 $tcnssB02->setNumAffilie($value->getNumAffilie());
  416.                 $tcnssB02->setPeriode($periode);
  417.                 $tcnssB02->setNumAssure($value->getNumAssure());
  418.                 $tcnssB02->setNomPrenom(str_replace("'","''",$value->getNomPrenom()));
  419.                 $tcnssB02->setEnfants($value->getEnfants());
  420.                 $tcnssB02->setAfAPayer($value->getAfAPayer());
  421.                 $tcnssB02->setAfADeduire($value->getAfADeduire());
  422.                 $tcnssB02->setAfNetAPayer($value->getAfNetAPayer());
  423.                 $tcnssB02->setSite($contract ? $contract->getDossier()->getCode() : '');
  424.                 $tcnssB02->setNJoursDeclares($nbrJour);
  425.                 $tcnssB02->setLSituation($categorie);
  426.                 $tcnssB02->setLSituationNum($numCat);
  427.                 $tcnssB02->setNSalaireReel($reel);
  428.                 $tcnssB02->setNSalairePlaf($plafond);
  429.                 $tcnssB02->setSCtr($S_Ctr);
  431.                 $this->em->persist($tcnssB02);
  432.             
  434.                 $jour_decale = $jour_decale+$nbrJour;
  435.         
  436.                 $salaireR = intval($salaireR+$reel);
  437.                 $salaireP=$salaireP+$plafond;
  438.                 $T_Ctr = $T_Ctr + $S_Ctr; 
  439.                  
  440.             }
  441.             // die;
  442.             
  443.             foreach($tcnssA00->getTCnssA03s() as $value)
  444.             {   
  445.                 $tcnssB03 = new TCnssB03();
  447.                 $tcnssB03->setCnssb00($tcnssB00);
  448.                 $tcnssB03->setTypeEnreg('B03');
  449.                 $tcnssB03->setNumAffilie($value->getNumAffilie());
  450.                 $tcnssB03->setPeriode($periode);
  451.                 $tcnssB03->setNbrSalaries($value->getNbrSalaries());
  452.                 $tcnssB03->setTEnfants($value->getTEnfants());
  453.                 $tcnssB03->setTAfAPayer($value->getTAfAPayer());
  454.                 $tcnssB03->setTAfADeduire($value->getTAfADeduire());
  455.                 $tcnssB03->setTAfNetAPayer($value->getTAfNetAPayer());
  456.                 $tcnssB03->setTNumImma($value->getTNumImma());
  457.                 $tcnssB03->setTAfAReverser($value->getTAfAReverser());
  458.                 $tcnssB03->setTJoursDeclares($jour_decale);
  459.                 $tcnssB03->setTSalaireReel($salaireR);
  460.                 $tcnssB03->setTSalairePlaf($salaireP);
  461.                 $tcnssB03->setTCtr($T_Ctr);
  463.                 $this->em->persist($tcnssB03);
  465.                 $T_nbr_salaries=$T_nbr_salaries+$value->getNbrSalaries();
  466.                 $T_jour_decale=$T_jour_decale+$jour_decale;
  467.                 $T_num_imma=$T_num_imma+$value->getTNumImma();
  468.                 $T_salaireR=$T_salaireR+$salaireR;
  469.                 $T_salaireP=$T_salaireP+$salaireP;
  470.                 $T_Ctr6=$T_Ctr6+$T_Ctr;
  471.                 
  472.             }
  474.             $jour_decale=0;
  475.             
  476.             $salaireR=0;
  477.             $salaireP=0;
  478.             $T_Ctr=0;
  479.             // dd('amine');
  480.             $personnesNoDeclares = $this->em->getRepository(Tbulletin::class)->getPersonneNoDeclarer($periode1, $num_affilie, $periode);
  481.             // $personnesNoDeclares = [];
  482.             // dd($personnesNoDeclares);
  483.             if(count($personnesNoDeclares) > 0)
  484.             {
  485.                 foreach($personnesNoDeclares as $row4)
  486.                 {
  487.                     $contract = $this->em->getRepository(LContract::class)->find($row4['contractId']);
  489.                     // $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"]."'";
  490.                     // $stmt4 = $this->getDoctrine()->getConnection()->prepare($sql);
  491.                     // $stmt4->execute();
  492.                     // $datab04 = $stmt4->fetch();
  493.                     // $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"
  494.                     $queryBuilder = $this->em->createQueryBuilder();
  495.                     $montantImposable = $queryBuilder->select('SUM(TbulletinLg.montant) as reel, CASE WHEN (SUM(TbulletinLg.montant) > 6000) THEN 6000 ELSE SUM(TbulletinLg.montant) END as plafond')
  496.                         ->from(TbulletinLg::class, 'TbulletinLg')
  497.                         ->innerJoin('TbulletinLg.bulletin', 'bulletin')
  498.                         ->innerJoin('TbulletinLg.rubrique', 'rubrique')
  499.                         ->where('bulletin.contract = :contract')
  500.                         ->andWhere('rubrique.imposable = 1')
  501.                         ->andWhere('TbulletinLg.active = 1')
  502.                         ->andWhere('bulletin.periode = :periode1')
  503.                         ->setParameter('contract', $contract)
  504.                         ->setParameter('periode1', $periode1)
  505.                         ->getQuery()
  506.                         ->getOneOrNullResult()
  507.                     ;
  508.                     $reel =  (int)number_format($montantImposable['reel'], 2, '', '');
  509.                     $plafond = (int)number_format($montantImposable['plafond'], 2, '', '');
  511.                     if($reel == 0) {
  512.                         continue;
  513.                     }
  515.                     // $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"]."'";
  516.                     // $stmt4 = $this->getDoctrine()->getConnection()->prepare($sql);
  517.                     // $stmt4->execute();
  518.                     // $dataBaselg4 = $stmt4->fetch();
  519.                     $nbrJour = 26 - $this->em->getRepository(PArretTravailLg::class)->getNombreJoursArret($row4['contractId'], $periode1);
  520.                     $nbrJour = substr($nbrJour,0,2);
  521.                     $str = str_replace('\'',' ',$contract->getEmploye()->getNom().' '.$contract->getEmploye()->getPrenom());
  522.                     if (is_numeric($row4["CNSS"]))
  523.                     {
  524.                        $cnss = $row4["CNSS"];
  525.                     }else{
  526.                         $cnss=0;
  527.                     }
  529.                     // dump($cnss);
  530.                    
  531.                     $S_Ctr=round($plafond + $reel+$cnss+$nbrJour);
  532.                    
  533.                     $tcnssB04 = new TCnssB04();
  534.                     $tcnssB04->setCnssb00($tcnssB00);
  535.                     $tcnssB04->setContract($contract);
  536.                     $tcnssB04->setTypeEnreg('B04');
  537.                     $tcnssB04->setNumAffilie($num_affilie);
  538.                     $tcnssB04->setPeriode($periode);
  539.                     $tcnssB04->setNumAssure($row4["CNSS"]);
  540.                     $tcnssB04->setNomPrenom($str);
  541.                     $tcnssB04->setNumCin($contract->getEmploye()->getCin());
  542.                     $tcnssB04->setSite($contract->getDossier()->getCode());
  543.                     $tcnssB04->setNbrJours($nbrJour);
  544.                     $tcnssB04->setSalReel($reel);
  545.                     $tcnssB04->setSalPlaf($plafond);
  546.                     $tcnssB04->setSCtr($S_Ctr);
  547.                     $tcnssB04->setMontantReel($montantImposable['reel']);
  548.                     $tcnssB04->setMontantPlaf($montantImposable['plafond']);
  549.                     
  551.                     $this->em->persist($tcnssB04);
  553.                     // $stmt4 = $this->getDoctrine()->getConnection()->prepare($sql4);
  554.                     // $stmt4->execute();
  555.                     $nbr_salaries++;
  556.                     $jour_decale=$jour_decale+$nbrJour;
  557.                     $num_imma=$num_imma+$cnss;
  558.                     $salaireR=$salaireR+$reel;
  559.                     $salaireP=$salaireP+$plafond;
  560.                     
  561.                 
  562.                     //dd($sql4);
  563.                 }
  564.             } else {
  565.                 $tcnssB04 = new TCnssB04();
  566.                 $tcnssB04->setCnssb00($tcnssB00);
  567.                 $tcnssB04->setTypeEnreg('B04');
  568.                 $tcnssB04->setNumAffilie($num_affilie);
  569.                 $tcnssB04->setPeriode($periode);
  570.                 $tcnssB04->setNumAssure(0);
  571.                 $tcnssB04->setNbrJours(0);
  572.                 $tcnssB04->setSalReel(0);
  573.                 $tcnssB04->setSalPlaf(0);
  574.                 
  575.                 $this->em->persist($tcnssB04);
  577.                 // $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')";
  578.                 // $stmt4 = $this->getDoctrine()->getConnection()->prepare($sql4);
  579.                 // $stmt4->execute();
  580.             }
  581.             // dd($num_imma);
  582.             $T_Ctr=$nbr_salaries+$jour_decale+$num_imma+$salaireR+$salaireP;
  583.             // $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
  584.             // ALUES ('B05','$num_affilie', '$periode','$nbr_salaries','$num_imma','$jour_decale','$salaireR','$salaireP','$T_Ctr')";
  585.             // $stmt5 = $this->getDoctrine()->getConnection()->prepare($sql5);
  586.             // $stmt5->execute();
  588.             $tcnssB05 = new TCnssB05();
  589.             $tcnssB05->setCnssb00($tcnssB00);
  590.             $tcnssB05->setTypeEnreg('B05');
  591.             $tcnssB05->setNumAffilie($num_affilie);
  592.             $tcnssB05->setPeriode($periode);
  593.             $tcnssB05->setNbrSalaries($nbr_salaries);
  594.             $tcnssB05->setTNumImma($num_imma);
  595.             $tcnssB05->setTJoursDeclares($jour_decale);
  596.             $tcnssB05->setTSalaireReel($salaireR);
  597.             $tcnssB05->setTSalairePlaf($salaireP);
  598.             $tcnssB05->setTCtr($T_Ctr);
  599.             
  600.             $this->em->persist($tcnssB05);
  602.     
  603.             $T_nbr_salaries=$T_nbr_salaries+$nbr_salaries;
  604.             $T_jour_decale=$T_jour_decale+$jour_decale;
  605.             $T_num_imma=$T_num_imma+$num_imma;
  606.             $T_salaireR=$T_salaireR+$salaireR;
  607.             $T_salaireP=$T_salaireP+$salaireP;
  608.             $T_Ctr6=$T_Ctr6+$T_Ctr;
  610.             // $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) 
  611.             // VALUES ('B06','$num_affilie', '$periode','$T_nbr_salaries','$T_num_imma','$T_jour_decale','$T_salaireR','$T_salaireP','$T_Ctr6')";
  612.             // $stmt6 = $this->getDoctrine()->getConnection()->prepare($sql6);
  613.             // $stmt6->execute();
  615.             $tcnssB06 = new TCnssB06();
  616.             $tcnssB06->setCnssb00($tcnssB00);
  617.             $tcnssB06->setTypeEnreg('B06');
  618.             $tcnssB06->setNumAffilie($num_affilie);
  619.             $tcnssB06->setPeriode($periode);
  620.             $tcnssB06->setNbrSalaries($T_nbr_salaries);
  621.             $tcnssB06->setTNumImma($T_num_imma);
  622.             $tcnssB06->setTJoursDeclares($T_jour_decale);
  623.             $tcnssB06->setTSalaireReel($T_salaireR);
  624.             $tcnssB06->setTSalairePlaf($T_salaireP);
  625.             $tcnssB06->setTCtr($T_Ctr6);
  626.             
  627.             $this->em->persist($tcnssB06);
  628.                 
  629.                 
  630.             
  631.         }
  632.         $this->em->flush();
  633.         return new JsonResponse('Bien enregistrer');
  634.    
  635.     }
  637.     #[Route('/app_reporting_cnss_export/{cnssa00}', name: 'app_reporting_cnss_export', options: ['expose' => true])]
  638.     public function app_reporting_cnss_export(TCnssA00 $cnssa00): Response
  639.     {
  640.         $myFile = 'DS_';
  641.         $myFinalData = '';
  642.         /* cnss_b00 */
  644.         $cnssB00 = $this->em->getRepository(TCnssB00::class)->findOneBy(['cnssa00' => $cnssa00]);
  645.         $table = TCnssB00::class;
  646.         $typeEnreg = $this->getFinalLength($cnssB00->getTypeEnreg(),$table, 'typeEnreg', 'AN');
  647.         $identiTransfert = $this->getFinalLength($cnssB00->getIdentiTransfert(), $table, 'identiTransfert', 'N');
  648.         $cat = $this->getFinalLength($cnssB00->getCat(), $table, 'cat', 'AN');
  649.         $filler = $this->getFinalLength($cnssB00->getFiller(), $table, 'filler', 'AN');
  651.         $myFinalData .= $typeEnreg . $identiTransfert . $cat . $filler.PHP_EOL;
  652.      
  653.         // echo $myFinalData;
  654.         // die;
  655.         /* cnss_b01 */
  656.         // dd($cnssB01Data);
  658.         $table = TCnssB01::class;
  659.         foreach ($cnssB00->getTCnssB01s() as $cnssB01) {
  660.             $typeEnreg = $this->getFinalLength($cnssB01->getTypeEnreg(),$table, 'typeEnreg', 'AN');
  661.             $numAffilie = $this->getFinalLength($cnssB01->getNumAffilie(),$table, 'numAffilie', 'N');
  662.             $periode = $this->getFinalLength($cnssB01->getPeriode(),$table, 'periode', 'N');
  663.             $raisonSociale = $this->getFinalLength($cnssB01->getRaisonSociale(),$table, 'raisonSociale', 'AN');
  664.             $activite = $this->getFinalLength($cnssB01->getActivite(),$table, 'activite', 'AN');
  665.             $adresse = $this->getFinalLength($cnssB01->getAdresse(),$table, 'adresse', 'AN');
  666.             $ville = $this->getFinalLength($cnssB01->getVille(),$table, 'ville', 'AN');
  667.             $codePostal = $this->getFinalLength($cnssB01->getCodePostal(),$table, 'codePostal', 'AN');
  668.             $codeAgence = $this->getFinalLength($cnssB01->getCodeAgence(),$table, 'codeAgence', 'N');
  669.             $dateEmission = $this->getFinalLength($cnssB01->getDateEmission(),$table, 'dateEmission', 'N');
  670.             $dateExig = $this->getFinalLength($cnssB01->getDateExig(),$table, 'dateExig', 'N');
  672.             $myFinalData .= $typeEnreg . $numAffilie . $periode . $raisonSociale . $activite . $adresse . $ville . $codePostal . $codeAgence . $dateEmission . $dateExig.PHP_EOL;
  673.             
  674.         }
  675.         $myFile .=$numAffilie.'_'.$cnssa00->getPeriode();
  677.         
  678.        
  679.         /* cnss_b02 */
  680.         // dd($cnssB02Data);
  681.         $table = TCnssB02::class;
  682.         // dd($table);
  683.         foreach ($cnssB00->getTCnssB02s() as $cnssB02) {
  684.             // dd($cnssB02->Num_Assure);
  685.             // if($cnssB02->getId() == 38) {
  686.                 
  687.             
  688.             // $idEmployer = $this->getFinalLength($cnssB02->ID_Employer,'cnss_b02', 'ID_Employer', 'AN');
  689.             $typeEnreg = $this->getFinalLength($cnssB02->getTypeEnreg(),$table, 'typeEnreg', 'AN');
  690.             $numAffilie = $this->getFinalLength($cnssB02->getNumAffilie(),$table, 'numAffilie', 'N');
  691.             $periode = $this->getFinalLength($cnssB02->getPeriode(),$table, 'periode', 'N');
  692.             $numAssure = $this->getFinalLength($cnssB02->getNumAssure(),$table, 'numAssure', 'N');
  693.             $nomPrenom = $this->getFinalLength($cnssB02->getNomPrenom(),$table, 'nomPrenom', 'AN');
  694.             $enfants = $this->getFinalLength($cnssB02->getEnfants(),$table, 'enfants', 'N');
  695.             $aFAPayer = $this->getFinalLength($cnssB02->getAFAPayer(),$table, 'afAPayer', 'N');
  696.             $aFADeduire = $this->getFinalLength($cnssB02->getAfADeduire(),$table, 'afADeduire', 'N');
  697.             $aFNetAPayer = $this->getFinalLength($cnssB02->getAfNetAPayer(),$table, 'afNetAPayer', 'N');
  698.             $aFAReverser = $this->getFinalLength($cnssB02->getAfAReverser(),$table, 'afAReverser', 'N');
  699.             $nJoursDeclares = $this->getFinalLength($cnssB02->getNJoursDeclares(),$table, 'nJoursDeclares', 'NN');
  700.             $nSalaireReel = $this->getFinalLength($cnssB02->getNSalaireReel(),$table, 'nSalaireReel', 'NN');
  701.             $nSalairePlaf = $this->getFinalLength($cnssB02->getNSalairePlaf(),$table, 'nSalairePlaf', 'NN');
  702.             $lSituation = $this->getFinalLength($cnssB02->getLSituation(),$table, 'lSituation', 'AN');
  703.             $sCtr = $this->getFinalLength($cnssB02->getSCtr(),$table, 'sCtr', 'NN');
  704.             $filler = $this->getFinalLength($cnssB02->getFiller(),$table, 'filler', 'AN');
  705.             $myFinalData .= $typeEnreg . $numAffilie . $periode . $numAssure . $nomPrenom . $enfants . $aFAPayer . $aFADeduire . $aFNetAPayer . $aFAReverser . $nJoursDeclares . $nSalaireReel . $nSalairePlaf . $lSituation . $sCtr . $filler.PHP_EOL;
  706.             // dump($myFinalData);
  707.             // }
  708.         }
  710.         /* cnss_b03 */
  711.         // dd($cnssB03Data);
  712.         $table = TCnssB03::class;
  713.         foreach ($cnssB00->getTCnssB03s() as $cnssB03) {
  714.             // dd($cnssB02->Num_Assure);
  715.             $typeEnreg = $this->getFinalLength($cnssB03->getTypeEnreg(),$table, 'typeEnreg', 'AN');
  716.             $numAffilie = $this->getFinalLength($cnssB03->getNumAffilie(),$table, 'numAffilie', 'N');
  717.             $periode = $this->getFinalLength($cnssB03->getPeriode(),$table, 'periode', 'N');
  718.             $nbrSalaries = $this->getFinalLength($cnssB03->getNbrSalaries(),$table, 'nbrSalaries', 'N');
  719.             $enfants = $this->getFinalLength($cnssB03->getTEnfants(),$table, 'tEnfants', 'N');
  720.             $aFAPayer = $this->getFinalLength($cnssB03->getTAfAPayer(),$table, 'tAfAPayer', 'N');
  721.             $aFADeduire = $this->getFinalLength($cnssB03->getTAfADeduire(),$table, 'tAfADeduire', 'N');
  722.             $aFNetAPayer = $this->getFinalLength($cnssB03->getTAfNetAPayer(),$table, 'tAfNetAPayer', 'N');
  723.             $tNumImma = $this->getFinalLength($cnssB03->getTNumImma(),$table, 'tNumImma', 'N');
  724.             $aFAReverser = $this->getFinalLength($cnssB03->getTAfAReverser(),$table, 'tAfAReverser', 'N');
  725.             $nJoursDeclares = $this->getFinalLength($cnssB03->getTJoursDeclares(),$table, 'tJoursDeclares', 'NN');
  726.             $nSalaireReel = $this->getFinalLength($cnssB03->getTSalaireReel(),$table, 'tSalaireReel', 'NN');
  727.             $nSalairePlaf = $this->getFinalLength($cnssB03->getTSalairePlaf(),$table, 'tSalairePlaf', 'NN');
  728.             $sCtr = $this->getFinalLength($cnssB03->getTCtr(),$table, 'tCtr', 'NN');
  729.             $filler = $this->getFinalLength($cnssB03->getFiller(),$table, 'filler', 'AN');
  731.             $myFinalData .= $typeEnreg . $numAffilie . $periode . $nbrSalaries . $enfants . $aFAPayer . $aFADeduire . $aFNetAPayer . $tNumImma . $aFAReverser . $nJoursDeclares . $nSalaireReel . $nSalairePlaf . $sCtr . $filler.PHP_EOL;
  732.            
  733.         }
  734.       
  735.         /* cnss_b04 */
  736.         $table = TCnssB04::class;
  737.         foreach ($cnssB00->getTCnssB04s() as $cnssB04) {
  738.             // dd($cnssB04->getNumAssure() == 0 ? ' ' : $cnssB04->getNumAssure());
  739.             $typeEnreg = $this->getFinalLength($cnssB04->getTypeEnreg(),$table, 'typeEnreg', 'AN');
  740.             $numAffilie = $this->getFinalLength($cnssB04->getNumAffilie(),$table, 'numAffilie', 'N');
  741.             $periode = $this->getFinalLength($cnssB04->getPeriode(),$table, 'periode', 'N');
  742.             $numAssure = $this->getFinalLength($cnssB04->getNumAssure() == 0 ? '' : $cnssB04->getNumAssure() ,$table, 'numAssure', 'AN');
  743.             // $numAssure = $this->getFinalLength(
  744.             //     $cnssB04->getNumAssure() == 0 ? ' ' : $cnssB04->getNumAssure(),
  745.             //     $table, 
  746.             //     'numAssure', 
  747.             //     $cnssB04->getNumAssure() == 0 ? 'AN' : 'N'
  748.             // );
  749.             $nomPrenom = $this->getFinalLength($cnssB04->getNomPrenom(),$table, 'nomPrenom', 'AN');
  750.             $numCIN = $this->getFinalLength($cnssB04->getNumCIN(),$table, 'numCin', 'AN');
  751.             $nbrJours = $this->getFinalLength($cnssB04->getNbrJours(),$table, 'nbrJours', 'NN');
  752.             $salReel = $this->getFinalLength($cnssB04->getSalReel(),$table, 'salReel', 'NN');
  753.             $salPlaf = $this->getFinalLength($cnssB04->getSalPlaf(),$table, 'salPlaf', 'NN');
  754.             $sCtr = $this->getFinalLength($cnssB04->getSCtr(),$table, 'sCtr', 'NN');
  755.             $filler = $this->getFinalLength($cnssB04->getFiller(),$table, 'filler', 'AN');
  757.             $myFinalData .= $typeEnreg . $numAffilie . $periode . $numAssure . $nomPrenom . $numCIN . $nbrJours . $salReel . $salPlaf . $sCtr . $filler.PHP_EOL;
  758.            
  759.         }
  760.        
  761.         
  762.         /* cnss_b05 */
  763.         $table = TCnssB05::class;
  765.         // dd($cnssB05Data);
  766.         foreach ($cnssB00->getTCnssB05s() as $cnssB05) {
  767.             // dd($cnssB02->Num_Assure);
  768.             $typeEnreg = $this->getFinalLength($cnssB05->getTypeEnreg(),$table, 'typeEnreg', 'AN');
  769.             $numAffilie = $this->getFinalLength($cnssB05->getNumAffilie(),$table, 'numAffilie', 'N');
  770.             $periode = $this->getFinalLength($cnssB05->getPeriode(),$table, 'periode', 'N');
  771.             $nbrSalaries = $this->getFinalLength($cnssB05->getNbrSalaries(),$table, 'nbrSalaries', 'NN');
  772.             $tNumImma = $this->getFinalLength($cnssB05->getTNumImma(),$table, 'tNumImma', 'NN');
  773.             $tJoursDeclares = $this->getFinalLength($cnssB05->getTJoursDeclares(),$table, 'tJoursDeclares', 'NN');
  774.             $salReel = $this->getFinalLength($cnssB05->getTSalaireReel(),$table, 'tSalaireReel', 'NN');
  775.             $salPlaf = $this->getFinalLength($cnssB05->getTSalairePlaf(),$table, 'tSalairePlaf', 'NN');
  776.             $sCtr = $this->getFinalLength($cnssB05->getTCtr(),$table, 'tCtr', 'NN');
  777.             $filler = $this->getFinalLength($cnssB05->getFiller(),$table, 'filler', 'AN');
  779.             $myFinalData .= $typeEnreg . $numAffilie . $periode . $nbrSalaries . $tNumImma . $tJoursDeclares . $salReel . $salPlaf . $sCtr . $filler.PHP_EOL;
  780.            
  781.         }
  783.         
  784.        
  785.         /* cnss_b06 */
  786.         // dd($cnssB06Data);
  787.         $table = TCnssB06::class;
  789.         foreach ($cnssB00->getTCnssB06s() as $cnssB06) {
  790.             // dd($cnssB02->Num_Assure);
  791.             $typeEnreg = $this->getFinalLength($cnssB06->getTypeEnreg(),$table, 'typeEnreg', 'AN');
  792.             $numAffilie = $this->getFinalLength($cnssB06->getNumAffilie(),$table, 'numAffilie', 'N');
  793.             $periode = $this->getFinalLength($cnssB06->getPeriode(),$table, 'periode', 'N');
  794.             $nbrSalaries = $this->getFinalLength($cnssB06->getNbrSalaries(),$table, 'nbrSalaries', 'NN');
  795.             $tNumImma = $this->getFinalLength($cnssB06->getTNumImma(),$table, 'tNumImma', 'NN');
  796.             $tJoursDeclares = $this->getFinalLength($cnssB06->getTJoursDeclares(),$table, 'tJoursDeclares', 'NN');
  797.             $salReel = $this->getFinalLength($cnssB06->getTSalaireReel(),$table, 'tSalaireReel', 'NN');
  798.             $salPlaf = $this->getFinalLength($cnssB06->getTSalairePlaf(),$table, 'tSalairePlaf', 'NN');
  799.             $sCtr = $this->getFinalLength($cnssB06->getTCtr(),$table, 'tCtr', 'NN');
  800.             $filler = $this->getFinalLength($cnssB06->getFiller(),$table, 'filler', 'AN');
  802.             $myFinalData .= $typeEnreg . $numAffilie . $periode . $nbrSalaries . $tNumImma . $tJoursDeclares . $salReel . $salPlaf . $sCtr . $filler.PHP_EOL;
  803.            
  804.         }
  806.         $tempFileName = tempnam(sys_get_temp_dir(), 'text_file');
  807.         file_put_contents($tempFileName, $myFinalData);
  809.         // Create a BinaryFileResponse
  810.         $response = new BinaryFileResponse($tempFileName);
  812.         // Set the response headers
  813.         $response->headers->set('Content-Type', 'text/plain');
  814.         $response->headers->set('Content-Disposition', $response->headers->makeDisposition(
  815.             ResponseHeaderBag::DISPOSITION_ATTACHMENT,
  816.             $myFile.'.txt'
  817.         ));
  819.         return $response;
  820.        
  821.     }
  824.     // public function getFinalLengthCimr($data,$long, $type) {
  825.         
  826.     //     $diffrenceCaracterType =  $long - (int)(strlen($data));
  828.     //     if($diffrenceCaracterType==0) {
  829.     //         return $data; 
  830.     //     }
  831.     //     else {
  832.     //         if($type == 'A') {
  833.     //             return  $data .str_repeat(' ', $diffrenceCaracterType);
  834.     //         } else if ($type == 'N') {
  835.     //             return  str_repeat('0', $diffrenceCaracterType).$data ;
  836.     //         }
  837.     //         else if ($type == 'AN') {
  838.     //              return  $data.str_repeat(' ', $diffrenceCaracterType) ;
  839.     //         }   
  840.     //     }
  841.     // }
  843.     public function getFinalLength($data,$table,$column, $type) {
  844.         $nom = trim($data);
  845.         if($column == 'nomPrenom' and $table=='App\Entity\TCnssB02') {
  846.             // dd(());
  847.             // $data = preg_split('/\s{2,}/', $nom); this old code because i found el kihel has more than one whitespace in her last name
  848.             $data = preg_split('/\s{3,}/', $nom);
  849.             if(count($data) < 2) {
  850.                 $data = preg_split('/\s+/', $nom);
  851.                 if (count($data) == 3) {
  852.                     $data = [$data[0].' ' . $data[1], $data[2]];
  853.                     //dd($data);
  854.                 }
  855.             }
  856.             $diffrence = 30 - (int)strlen($data[0]);
  857.             $nom = $data[0] . str_repeat(' ', $diffrence);
  858.             $diffrence = 30 - (int)strlen($data[1]);
  859.             $prenom = $data[1] . str_repeat(' ', $diffrence);
  860.             // dump(strip_tags($prenom));
  861.             // dd( $nom . $prenom);
  862.             return $nom . $prenom;
  863.         }
  864.         $numberOfLengthColumn = $this->getNumberOfLength($table, $column);
  865.         if((strlen($data)) == $numberOfLengthColumn) {
  866.             return $data;
  867.         } 
  868.         else {
  869.             $diffrenceCaracterType =  $numberOfLengthColumn - (int)(strlen($data));
  870.             if($type == 'AN') {
  871.                 return $data . str_repeat(' ', $diffrenceCaracterType);
  872.             } else if ($type == 'N') {
  873.                 return $data . str_repeat('0', $diffrenceCaracterType);
  874.             }
  875.             else if ($type == 'NN') {
  876.                // dd($data);
  877.                
  878.                 return  str_repeat('0', $diffrenceCaracterType).$data ;
  879.             }
  880.         }
  881.     }
  883.     public function getNumberOfLength($table, $column) {
  884.         // $em = $this->getDoctrine()->getManager()->getConnection();
  885.         // $sql = "SELECT CHARACTER_MAXIMUM_LENGTH FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = '$table' AND COLUMN_NAME = '$column'";
  886.         // $stmt  = $em->prepare($sql);
  887.         // $stmt->execute();
  888.         // // dd($stmt->fetch(PDO::FETCH_OBJ)->CHARACTER_MAXIMUM_LENGTH);
  889.         // return $stmt->fetch(PDO::FETCH_OBJ)->CHARACTER_MAXIMUM_LENGTH;
  891.         // $entityManager = $this->getDoctrine()->getManager();
  892.         $classMetadata = $this->em->getClassMetadata($table);
  893.         $length = $classMetadata->getFieldMapping($column)['length'];
  894.         return $length;
  895.     }
  896.     #[Route('/import', name: 'app_reporting_import_cnss', options: ['expose' => true])]
  897.     public function app_reporting_import_cnss(Request $request): Response
  898.     {
  899.         $reader = new Reader();
  900.         $spreadsheet = $reader->load($request->files->get('file'));
  901.         $worksheet = $spreadsheet->getActiveSheet();
  902.         $spreadSheetArys = $worksheet->toArray();
  904.         unset($spreadSheetArys[0]);
  906.         foreach ($spreadSheetArys as $key => $sheet) {
  907.             $employe = $this->em->getRepository(Pemploye::class)->findOneBy(['id' => $sheet[0], 'cin' => $sheet[1]]);
  908.             if(!$employe) {
  909.                 return new JsonResponse('Employe introuvable à la ligne '.($key + 1).' !', 500);                
  910.             }
  912.             if($sheet[5] != '') {
  913.                 $situation = $this->em->getRepository(PsituationFamiliale::class)->findOneBy(['Abreviation' => $sheet[5]]);
  914.     
  915.                 if(!$situation) {
  916.                     return new JsonResponse('Situation introuvable à la ligne '.($key + 1).' !', 500);                
  917.                 }
  919.                 $employe->setSituationFamilialeId($situation);
  920.             }
  921.             if($sheet[6] != '') {
  922.                 $employe->setNbrPrisEnCharge($sheet[6]);
  923.                 foreach ($employe->getContracts() as $key => $contract) {
  924.                     $contract->setPriseEnCharge($sheet[6]);
  925.                 }
  926.             }
  928.             if($sheet[4] != '') {
  929.                 foreach ($employe->getContracts() as $key => $contract) {
  930.                     $contract->setCnss(trim($sheet[4]));
  931.                 }
  932.             }
  933.             if($sheet['7'] != '') {
  934.                 foreach ($employe->getContracts() as $key => $contract) {
  935.                     $contract->setDateCnss(new \DateTime($sheet['7']));
  936.                 }
  937.             }
  938.         }
  939.         
  940.         $this->em->flush();
  942.         return new JsonResponse('Bien enregistre !');
  943.     }
  944. }