src/EventSubscriber/IASqlSubscriber.php line 122

Open in your IDE?
  1. <?php
  3. namespace App\EventSubscriber;
  5. use App\Entity\IASql;
  6. use Doctrine\ORM\EntityManagerInterface;
  7. use Doctrine\Persistence\ManagerRegistry;
  8. use EasyCorp\Bundle\EasyAdminBundle\Event\AfterEntityPersistedEvent;
  9. use EasyCorp\Bundle\EasyAdminBundle\Event\AfterEntityUpdatedEvent;
  10. use Doctrine\DBAL\Exception as DBALException;
  11. use PDO;
  12. use Symfony\Component\EventDispatcher\EventSubscriberInterface;
  13. use Symfony\Component\Security\Core\Authentication\Token\Storage\TokenStorageInterface;
  15. class IASqlSubscriber implements EventSubscriberInterface
  16. {
  17.     private $entityManager;
  18.     private ManagerRegistry $manager;
  19.     public function __construct(
  20.         ManagerRegistry $manager,
  21.         EntityManagerInterface $entityManager,
  22.         TokenStorageInterface $tokenStorage
  23.     ) {
  24.         $this->manager = $manager;
  25.         $this->entityManager = $entityManager;
  26.         $this->tokenStorage = $tokenStorage;
  27.     }
  29.     public static function getSubscribedEvents()
  30.     {
  31.         return [
  32.             AfterEntityPersistedEvent::class => ['afterAdd'],
  33.             AfterEntityUpdatedEvent::class => ['afterUpdate'],
  34.         ];
  35.     }
  38.     public function afterAdd(AfterEntityPersistedEvent $event)
  39.     {
  40.         $entity = $event->getEntityInstance();
  41.         if ($entity instanceof IASql) {
  42.             $keysSource = [];
  43.             $keysDestination = [];
  44.             $champsTable=[];
  45.             $champsTableD=[];
  46.             $sqlSource = strtolower($entity->getSourceSql());
  47.             if ($sqlSource != "") {
  48.                 $sqlSource = $this->cleanTextSql($sqlSource);
  49.                 $describe = $this->getTableColumnsFromQuery($sqlSource);
  50.                 $tableNames = $this->getTableNamesFromQuery($sqlSource);
  51.                 $resultats1 = $this->popup($sqlSource);
  52.                 if ($resultats1 != null) {
  53.                     foreach (array_slice($resultats1, 0, 10) as $resultat) {
  54.                         $keysSource = array_keys($resultat);
  55.                     }
  56.                     foreach ($keysSource as $column) {
  57.                         foreach ($tableNames as $tableName) {
  58.                             foreach ($describe as $key => $desc) {
  59.                                 if ($key == $tableName) {
  60.                                     if (in_array($column, $desc)) {
  61.                                         $champsTable[] = $column . ' : ' . $tableName;
  62.                                     }
  63.                                 }
  64.                             }
  65.                         }
  66.                     }
  67.                     $entity->setIaSqlChamps(implode("\n", $champsTable));
  68.                     $entity->setSourceChamps(implode("\n", $keysSource));
  69.                     $this->entityManager->persist($entity);
  70.                 }
  71.             } else {
  72.                 $entity->setSourceChamps("");
  73.                 $this->entityManager->persist($entity);
  74.             }
  75.             $sqlDestination = strtolower($entity->getDestinationSql());
  76.             if ($sqlDestination != "") {
  77.                 $sqlDestination = $this->cleanTextSql($sqlDestination);
  78.                 $describeD = $this->getTableColumnsFromQuery($sqlDestination);
  79.                 $tableNamesD = $this->getTableNamesFromQuery($sqlDestination);
  80.                 $resultats2 = $this->popup($sqlDestination);
  81.                 if ($resultats2 != null) {
  82.                     foreach (array_slice($resultats2, 0, 10) as $resultat) {
  83.                         $keysDestination = array_keys($resultat);
  84.                     }
  85.                     foreach ($keysDestination as $column) {
  86.                         foreach ($tableNamesD as $tableName) {
  87.                             foreach ($describeD as $key => $desc) {
  88.                                 if ($key == $tableName) {
  89.                                     if (in_array($column, $desc)) {
  90.                                         $champsTableD[] = $column . ' : ' . $tableName;
  91.                                     }
  92.                                 }
  93.                             }
  94.                         }
  95.                     }
  96.                     $entity->setIaSqlChampsD(implode("\n", $champsTableD));
  97.                     $entity->setDestinationChamps(implode("\n", $keysDestination));
  98.                     $this->entityManager->persist($entity);
  99.                 }
  100.             } else {
  101.                 $entity->setDestinationChamps("");
  102.                 $this->entityManager->persist($entity);
  103.             }
  105.             $this->entityManager->flush();
  107.             $count = 0;
  108.             $jeuxSql = $this->manager->getRepository(IASql::class)->findAll();
  109.             foreach ($jeuxSql as $jeuSql) {
  110.                 if (strtolower($jeuSql->getLabel()) == strtolower($entity->getLabel())) {
  111.                     $count++;
  112.                 }
  113.             }
  114.             if ($count > 1) {
  115.                 $this->entityManager->remove($entity);
  116.                 $this->entityManager->flush();
  117.             }
  118.         }
  119.     }
  122.     public function afterUpdate(AfterEntityUpdatedEvent $event)
  123.     {
  124.         $entity = $event->getEntityInstance();
  125.         if ($entity instanceof IASql) {
  126.             $keysSource = [];
  127.             $keysDestination = [];
  128.             $sqlSource = strtolower($entity->getSourceSql());
  129.             $champsTable = [];
  130.             $champsTableD = [];
  131.             if ($sqlSource != "") {
  132.                 $sqlSource = $this->cleanTextSql($sqlSource);
  133.                 $describe = $this->getTableColumnsFromQuery($sqlSource);
  134.                 $tableNames = $this->getTableNamesFromQuery($sqlSource);
  135.                 $resultats1 = $this->popup($sqlSource);
  136.                 if ($resultats1 != null) {
  137.                     foreach (array_slice($resultats1, 0, 10) as $resultat) {
  138.                         $keysSource = array_keys($resultat);
  139.                     }
  140.                     foreach ($keysSource as $column) {
  141.                         foreach ($tableNames as $tableName) {
  142.                             foreach ($describe as $key => $desc) {
  143.                                 if ($key == $tableName) {
  144.                                     if (in_array($column, $desc)) {
  145.                                         $champsTable[] = $column . ' : ' . $tableName;
  146.                                     }
  147.                                 }
  148.                             }
  149.                         }
  150.                     }
  151.                     $entity->setIaSqlChamps(implode("\n", $champsTable));
  152.                     $entity->setSourceChamps(implode("\n", $keysSource));
  153.                     $this->entityManager->persist($entity);
  154.                 } else {
  155.                     $entity->setSourceChamps("");
  156.                     $this->entityManager->persist($entity);
  157.                 }
  158.             } else {
  159.                 $entity->setSourceChamps("");
  160.                 $this->entityManager->persist($entity);
  161.             }
  162.             $sqlDestination = strtolower($entity->getDestinationSql());
  163.             if ($sqlDestination != "") {
  164.                 $sqlDestination = $this->cleanTextSql($sqlDestination);
  165.                 $describeD = $this->getTableColumnsFromQuery($sqlDestination);
  166.                 $tableNamesD = $this->getTableNamesFromQuery($sqlDestination);
  167.                 $resultats2 = $this->popup($sqlDestination);
  168.                 if ($resultats2 != null) {
  169.                     foreach (array_slice($resultats2, 0, 10) as $resultat) {
  170.                         $keysDestination = array_keys($resultat);
  171.                     }
  172.                     foreach ($keysDestination as $column) {
  173.                         foreach ($tableNamesD as $tableName) {
  174.                             foreach ($describeD as $key => $desc) {
  175.                                 if ($key == $tableName) {
  176.                                     if (in_array($column, $desc)) {
  177.                                         $champsTableD[] = $column . ' : ' . $tableName;
  178.                                     }
  179.                                 }
  180.                             }
  181.                         }
  182.                     }
  183.                     $entity->setIaSqlChampsD(implode("\n", $champsTableD));
  184.                     $entity->setDestinationChamps(implode("\n", $keysDestination));
  185.                     $this->entityManager->persist($entity);
  186.                 } else {
  187.                     $entity->setDestinationChamps("");
  188.                     $this->entityManager->persist($entity);
  189.                 }
  190.             } else {
  191.                 $entity->setDestinationChamps("");
  192.                 $this->entityManager->persist($entity);
  193.             }
  195.             $this->entityManager->flush();
  196.         }
  197.     }
  201.     public function popup($reqSource)
  202.     {
  203.         try {
  204.             $entityManager = $this->manager->getManager();
  205.             if ($reqSource != "") {
  206.                 $conn = $entityManager->getConnection();
  207.                 $sql = $reqSource; //."WHERE projet_id = ".$projet->getId();
  208.                 $stmt = $conn->prepare($sql);
  209.                 $resultSet = $stmt->executeQuery();
  210.                 return  $resultSet->fetchAllAssociative();
  211.             } else {
  212.                 return null;
  213.             }
  214.         } catch (DBALException $e) {
  215.             echo "<div class='alert alert-danger' role='alert'>Vous avez une erreur de syntaxe SQL !</p></div>";
  216.         }
  217.     }
  219.     function cleanTextSql($text)
  220.     {
  221.         $text = htmlspecialchars_decode($text);
  222.         $text = preg_replace('#<[^>]+>#', ' ', $text);
  223.         //$text = strip_tags($text);
  224.         $text = str_replace("’", '', $text);
  225.         $text = str_replace("`", '', $text);
  226.         $text = str_replace('/', 'slach', $text);
  227.         $text = str_replace('&nbsp;', '', $text);
  228.         //$text = str_replace(';', ' limit 10', $text);
  229.         $text = str_replace(';', '', $text);
  230.         $text = str_replace('< ', '<', $text);
  231.         $text = str_replace(' >', '>', $text);
  232.         $text = str_replace('»', 'mot', $text);
  233.         $text = str_replace('«', 'mot', $text);
  234.         $text = str_replace('#', '', $text);
  235.         $text = str_replace('œ', 'oe', $text);
  236.         $text = preg_replace('/\:/', 'mot', $text);
  237.         $text = str_replace('°', '', $text);
  238.         $text = str_replace(' – ', '', $text);
  239.         $utf8 = array(
  240.             '/[áàâãªä]/u'   =>   'a',
  241.             '/[ÁÀÂÃÄ]/u'    =>   'A',
  242.             '/[ÍÌÎÏ]/u'     =>   'I',
  243.             '/[íìîï]/u'     =>   'i',
  244.             '/[éèêë]/u'     =>   'e',
  245.             '/[ÉÈÊË]/u'     =>   'E',
  246.             '/[óòôõºö]/u'   =>   'o',
  247.             '/[ÓÒÔÕÖ]/u'    =>   'O',
  248.             '/[úùûü]/u'     =>   'u',
  249.             '/[ÚÙÛÜ]/u'     =>   'U',
  250.             '/ç/'           =>   'c',
  251.             '/Ç/'           =>   'C',
  252.             '/ñ/'           =>   'n',
  253.             '/Ñ/'           =>   'N',
  254.             //'/_/'           =>   '-', // UTF-8 hyphen to "normal" hyphen
  255.             '/[’‘‹›‚]/u'    =>   ' ', // Literally a single quote
  256.             '/[“”«»„]/u'    =>   'mot', // Double quote
  257.             '/ /'           =>   ' ', // nonbreaking space (equiv. to 0x160)
  258.         );
  259.         return preg_replace(array_keys($utf8), array_values($utf8), $text);
  260.     }
  266.     // SELECT `article`.*, `article_contenu`.*
  267.     //             FROM `article` 
  268.     //                 LEFT JOIN `article_contenu` ON `article_contenu`.`article_id` = `article`.`id`; 
  270.     //  SELECT 'article.*' , 
  271.     //                 'article_contenu.*' 
  272.     //                  FROM article
  273.     //                                 LEFT JOIN article_contenu ON 'article_contenu.article_id' = 'article.id';
  277.     function getTableNameFromQuery($query)
  278.     {
  279.         // Extract the table name from the FROM clause of the query
  280.         preg_match('/\bFROM\s+`?(\w+)`?\b/i', $query, $matches);
  281.         if (count($matches) > 1) {
  282.             return $matches[1];
  283.         }
  284.         return false;
  285.     }
  287.     function getTableNamesFromQuery($query)
  288.     {
  289.         try {
  290.             // $conn =   $this->manager->getManager()->getConnection();
  291.             // $params = $conn->getParams();
  292.             // dd($params);
  293.             // Extract the table names from the FROM clause and the JOIN clause of the query
  294.             preg_match_all('/\bFROM\s+`?(\w+)`?\b/i', $query, $matches1);
  295.             preg_match_all('/\bJOIN\s+`?(\w+)`?\b/i', $query, $matches2);
  296.             $tableNames = array_merge($matches1[1], $matches2[1]);
  297.             $tableNames = array_unique($tableNames);
  298.             return $tableNames;
  299.         } catch (DBALException $e) {
  300.             echo "<div class='alert alert-danger' role='alert'>Vous avez une erreur de syntaxe SQL !</p></div>";
  301.         }
  302.     }
  305.     function getTableColumnsFromQuery($query)
  306.     {
  307.         try {
  308.             // Extract the table names from the FROM clause and the JOIN clause of the query
  309.             preg_match_all('/\bFROM\s+`?(\w+)`?\b/i', $query, $matches1);
  310.             preg_match_all('/\bJOIN\s+`?(\w+)`?\b/i', $query, $matches2);
  311.             $tableNames = array_merge($matches1[1], $matches2[1]);
  312.             $tableNames = array_unique($tableNames);
  313.             $conn = $this->manager->getManager()->getConnection();
  314.             $tableColumns = array();
  315.             foreach ($tableNames as $tableName) {
  316.                 $stmt = $conn->prepare("DESCRIBE $tableName");
  317.                 $stmt->execute();
  318.                 $tableColumns[$tableName] = $stmt->executeQuery()->fetchAll(PDO::FETCH_COLUMN);
  319.             }
  321.             return $tableColumns;
  322.         } catch (DBALException $e) {
  323.             echo "<div class='alert alert-danger' role='alert'>Vous avez une erreur de syntaxe SQL !</p></div>";
  324.         }
  325.     }
  350.     // public function afterUpdate(AfterEntityUpdatedEvent $event)
  351.     // {
  352.     //     $entity = $event->getEntityInstance();
  353.     //     if ($entity instanceof IASql) {
  354.     //         $champsSource = "";
  355.     //         $champsDestination = "";
  356.     //         $sqlSource = strtolower($entity->getSourceSql());
  357.     //         $champs = trim(substr($sqlSource, 6, strpos($sqlSource, "from") - strlen("from") - 2));
  358.     //         if($champs == "*"){
  359.     //             $table_name=trim($this->get_string_between(trim($sqlSource),'from','where'));
  360.     //             $columnNames=$this->getColumnName($table_name);
  361.     //             foreach($columnNames as $columnName){
  362.     //                 $champs=(implode(",",$columnName));
  363.     //             }
  364.     //         }
  365.     //         //dd($champs);
  366.     //         $champs = explode(",", $champs);
  367.     //         $resultats1 = $this->popupSource($sqlSource);
  368.     //         if ($resultats1 != null) {
  369.     //             foreach ($resultats1 as  $resultat) {
  370.     //                 foreach ($champs as $champ) {
  371.     //                     if (array_key_exists(trim($champ), $resultat)) {
  372.     //                         foreach ($resultat as $value) {
  373.     //                             $champsSource = $champsSource . $value . ",";
  374.     //                         }
  375.     //                     }
  376.     //                 }
  377.     //             }
  378.     //             $entity->setSourceChamps($champsSource);
  379.     //         } else {
  380.     //             $entity->setSourceChamps("");
  381.     //         }
  383.     //         /*********Destination */
  384.     //         $sqlDestination = strtolower($entity->getDestinationSql());
  385.     //         $champs2 = trim(substr($sqlDestination, 6, strpos($sqlDestination, "from") - strlen("from") - 2));
  386.     //         if($champs2 == "*"){
  387.     //             $table_name=trim($this->get_string_between(trim( $sqlDestination),'from','where'));
  388.     //             $columnNames=$this->getColumnName($table_name);
  389.     //             foreach($columnNames as $columnName){
  390.     //                 $champs2=(implode(",",$columnName));
  391.     //             }
  392.     //         }
  393.     //         $champs2 = explode(",", $champs2);
  395.     //         $resultats2 = $this->popupDestination($sqlDestination);
  396.     //         if ($resultats2 != null) {
  397.     //             foreach ($resultats2 as  $resultat) {
  398.     //                 foreach ($champs2 as $champ) {
  399.     //                     if (array_key_exists(trim($champ), $resultat)) {
  400.     //                         foreach ($resultat as $value) {
  402.     //                             $champsDestination = $champsDestination . $value . ",";
  403.     //                         }
  404.     //                     }
  405.     //                 }
  406.     //             }
  407.     //             $entity->setDestinationChamps($champsDestination);;
  408.     //         } else {
  409.     //             $entity->setDestinationChamps("");
  410.     //         }
  411.     //         $this->entityManager->persist($entity);
  412.     //         $this->entityManager->flush();
  413.     //     }
  414.     // }
  415. }