src/Controller/ClientListController.php line 3242

Open in your IDE?
  1. <?php
  2. /**
  3.  * Created by Mediterranean Develup Solutions
  4.  * User: jorge.defreitas@develup.solutions
  5.  * Date: 04/07/2017
  6.  * Time: 17:02
  7.  */
  9. namespace App\Controller;
  11. use App\Entity\Cities;
  12. use App\Entity\Client;
  13. use App\Entity\Country;
  14. use App\Entity\Group;
  15. use App\Entity\Regions;
  16. use App\Entity\User;
  17. use App\Helper\ClientHelper;
  18. use App\MDS\CommercialBundle\Entity\CommercialTracking;
  19. use PhpOffice\PhpSpreadsheet\Spreadsheet;
  20. use Symfony\Component\Routing\Annotation\Route;
  21. use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
  22. use Symfony\Component\HttpFoundation\JsonResponse;
  23. use Symfony\Component\HttpFoundation\Request;
  24. use PhpOffice\PhpSpreadsheet\Style\Alignment;
  25. use PhpOffice\PhpSpreadsheet\Style\Fill;
  26. use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
  27. use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
  28. use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
  29. use Symfony\Component\HttpFoundation\ResponseHeaderBag;
  30. use Symfony\Component\HttpFoundation\StreamedResponse;
  31. use Symfony\Contracts\Translation\TranslatorInterface;
  33. class ClientListController extends AbstractController
  34. {
  35.     private $translator;
  37.     public function __construct(TranslatorInterface $translator) {
  38.         $this->translator = $translator;
  39.     }
  40.     
  41.     /**
  42.      * @Route("/client", name="client_list")
  43.      */
  44.     public function ClienteListAction(Request $request)
  45.     {
  46. //        $data = $this->primtListClientAction($request);
  48.         $em = $this->getDoctrine()->getManager();
  50.         /* POR GRUPOS EXISTENTES */
  51. //        $dql = 'SELECT cl, gr.title, c.country, p.region, r.city
  52. //                FROM App\Entity\Client cl
  53. //                INNER JOIN App\Entity\Group gr WITH gr.id = cl.groupId
  54. //                INNER JOIN App:Country c WITH c.id = cl.country
  55. //                INNER JOIN App:Regions p WITH p.id = cl.province
  56. //                INNER JOIN App:Cities r WITH r.id = cl.population';
  59.         /* ALL LO QUE HAY  */
  60.         $dql = 'SELECT cl,  c.country, p.name, re.region, r.city
  61.                 FROM App\Entity\Client cl
  62.                 INNER JOIN App:Country c WITH c.id = cl.country
  63.                 INNER JOIN App:Regions re WITH re.id = cl.region
  64.                 INNER JOIN App:Provinces p WITH p.id = cl.province
  65.                 INNER JOIN App:Cities r WITH r.id = cl.population';
  66. //                WHERE p.dateProposedAt >= :desde AND p.dateProposedAt <= :hasta
  67. //                ORDER BY p.dateProposedAt DESC';
  69.         $query = $em->createQuery($dql);
  70.         $clients = $query->getResult();
  72.         foreach($clients as $client){
  73.             $group = $em->getRepository(Group::class)->findOneById($client[0]->getGroupId());
  75.             $parameters = array(
  76.                 'clientId' => $client[0]->getId()
  77.             );
  78.             $dqlc = 'SELECT cc,  u.name, u.lastname
  79.                 FROM App\Entity\ClientContact cc
  80.                 INNER JOIN App\Entity\User u WITH u.id = cc.assignedAgent
  81.                 WHERE cc.clientId = :clientId ';
  82.             $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  83.             $contacts = $queryc->getResult();
  85.             $data_clients[] = array(
  86.                 'client' => $client,
  87.                 'group' => $group,
  88.                 'contacts' => $contacts,
  89.             );
  90.         }
  92.         return $this->render('client/clients-general-list.html.twig',  array( 'datosclientes' => $data_clients, ) );
  93.     }
  95.     /**
  96.      * @Route("/clientsearch", name="client_search")
  97.      */
  98.     public function ClientSearchAction(Request $request)
  99.     {
  100.         $em = $this->getDoctrine()->getManager();
  101.         $dql = 'SELECT cl
  102.                 FROM App\MDS\CommercialBundle\Entity\CommercialTracking cl
  103.                 GROUP BY cl.createdId';
  105.         $query = $em->createQuery($dql);
  106.         $comtrack = $query->getResult();
  108.         $agents = array();
  109.         foreach ($comtrack as $item) {
  110.             $userCreator = $em->getRepository(User::class)->findOneById($item->getCreatedId());
  111.             if (!empty($userCreator)){ $agents[$userCreator->getName() . ' '. $userCreator->getLastName()] = $userCreator; }
  112.         }
  114.         ksort($agents);
  115.         $background = 'assets/images/bg-10.jpg';
  117.         return $this->render('client/clients-search.html.twig', array(
  118.                 'background' => $background,
  119.                 'agents' => $agents,
  120.             )
  121.         );
  122.     }
  124.     /**
  125.      * @Route("/clientsearchzipcode", name="client_search_zipcode")
  126.      */
  127.     public function ClientSearchZipCodeAction(Request $request)
  128.     {
  129.         $em = $this->getDoctrine()->getManager();
  130.         $dato = $_POST['name'];
  131.         $standar = $_POST['criterio'];
  132.         $background = 'assets/images/bg-10.jpg';
  134.         switch ($standar){
  135.             case 'company':
  136.                 $parameters = array();
  137.                 $dqlc = "SELECT cc
  138.                 FROM App\Entity\Client cc
  139.                 WHERE cc.name LIKE '%".$dato."%'  OR cc.title LIKE '%".$dato."%' ";
  141.                 $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  142.                 $competencyClients = $queryc->getResult();
  144.                 $clientList = array();
  145.                 foreach($competencyClients as $client){
  146.                     $picture = $client->getPicture();
  147.                     if(!empty($picture) or $picture !== null){ $picture = $client->getPicture(); } else { $picture = 'assets/images/placeholder.jpg'; }
  149.                     //Ordenamos desde el tracking as reciente al mas antiguo
  150.                     $parameters = array( 'clientId' => $client->getId() );
  151.                     $dqlc = 'SELECT ct
  152.                              FROM CommercialBundle:CommercialTracking ct
  153.                              WHERE ct.clientId = :clientId
  154.                              ORDER BY ct.dateAt DESC';
  156.                     $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  157.                     $allClientTrackings = $queryc->getResult();
  158.                     $newTableData = array();
  159.                     $cityClient = $em->getRepository(Cities::class)->findOneById($client->getPopulation());
  160.                     if (!empty($cityClient)){ $cityClient = $cityClient->getCity(); }
  161.                     foreach ($allClientTrackings as $track) {
  162.                         $contactClientInfo = $em->getRepository('App\Entity\ClientContact')->findOneById($track->getContactId());
  163.                         if (!empty($contactClientInfo)){
  164.                             // allPhones tendrá los telefonos y moviles
  165.                             $allPhones = !empty($contactClientInfo->getPhone()) ? $contactClientInfo->getPhone() : null;
  166.                             $allMob = !empty($contactClientInfo->getMobile()) ? $contactClientInfo->getMobile() : null;
  167.                             $allPhones = (!empty($allPhones) and !empty($allMob)) ? $allPhones.' - '.$allMob : (!empty($allPhones) ? $allPhones : $allMob);
  169.                             $arrayContactClientInfo = array(
  170.                                 'fullName' => $contactClientInfo->getName().' '.$contactClientInfo->getLastName(),
  171.                                 'email' => $contactClientInfo->getEmail(),
  172.                                 'allPhones' => $allPhones,
  173.                             );
  174.                         } else {
  175.                             $arrayContactClientInfo = array(
  176.                                 'fullName' => '',
  177.                                 'email' => '',
  178.                                 'allPhones' => '',
  179.                             );
  180.                         }
  182.                         $xlsObservations = $track->getMessage();
  184.                         if(!empty($xlsObservations)){
  185.                             $xlsObservations = str_replace('</p><p>',PHP_EOL,$xlsObservations);
  186.                             $xlsObservations = str_replace('<p>','',$xlsObservations);
  187.                             $xlsObservations = str_replace('</p>','',$xlsObservations);
  188.                             $xlsObservations = str_replace('&nbsp;',' ',$xlsObservations);
  189.                             $xlsObservations = str_replace('<br>',PHP_EOL,$xlsObservations);
  190.                         }
  192.                         $newTableData[] = array(
  193.                             'id' => $client->getId(),
  194.                             'name' => $client->getName(),
  195.                             'title' => $client->getTitle(),
  196.                             'address' => $client->getAddress(),
  197.                             'addressNumber' => $client->getAddressNumber(),
  198.                             'cityClient' => $cityClient,
  199.                             'zipCode' => $client->getZipCode(),
  200.                             'volume' => $track->getVolume(),
  201.                             'payment' => $track->getPayment(),
  202.                             'situation' => $track->getSituation(),
  203.                             'competency' => $client->getCompetency(),
  204.                             'picture' => $picture,
  205.                             'contact' => $arrayContactClientInfo['fullName'],
  206.                             'email' => $arrayContactClientInfo['email'],
  207.                             'allPhones' => $arrayContactClientInfo['allPhones'],
  208.                             'trackMessage' => $xlsObservations,
  209.                             'trackStatus' => $track->getStatus(),
  210.                             'trackDateAt' => $track->getDateAt(),
  211.                         );
  212.                     }
  214.                     // Si no hay comercial tracking se debe dejar vacio
  215.                     if (empty($newTableData)){
  216.                         $newTableData[] = array(
  217.                             'id' => $client->getId(),
  218.                             'name' => $client->getName(),
  219.                             'title' => $client->getTitle(),
  220.                             'address' => $client->getAddress(),
  221.                             'addressNumber' => $client->getAddressNumber(),
  222.                             'cityClient' => $cityClient,
  223.                             'zipCode' => $client->getZipCode(),
  224.                             'volume' => '',
  225.                             'payment' => '',
  226.                             'situation' => '',
  227.                             'competency' => $client->getCompetency(),
  228.                             'picture' => $picture,
  229.                             'contact' => '',
  230.                             'email' => '',
  231.                             'allPhones' => '',
  232.                             'trackMessage' => '',
  233.                             'trackStatus' => '',
  234.                             'trackDateAt' => '',
  235.                         );
  236.                     }
  237.                     $clientList[] = $newTableData;
  238.                 }
  239.             break;
  241.             case 'zipCode':
  242.                 $parameters = array( 'zipCode' => $dato );
  243.                 $dqlc = 'SELECT cc
  244.                 FROM App\Entity\Client cc
  245.                 WHERE cc.zipCode = :zipCode ';
  247.                 $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  248.                 $competencyClients = $queryc->getResult();
  250.                 $clientList = array();
  251.                 foreach($competencyClients as $client){
  252.                     $picture = $client->getPicture();
  253.                     if(!empty($picture) or $picture !== null){ $picture = $client->getPicture(); } else { $picture = 'assets/images/placeholder.jpg'; }
  255.                     //Ordenamos desde el tracking as reciente al mas antiguo
  256.                     $parameters = array( 'clientId' => $client->getId() );
  257.                     $dqlc = 'SELECT ct
  258.                              FROM CommercialBundle:CommercialTracking ct
  259.                              WHERE ct.clientId = :clientId
  260.                              ORDER BY ct.dateAt DESC';
  262.                     $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  263.                     $allClientTrackings = $queryc->getResult();
  264.                     $newTableData = array();
  265.                     $cityClient = $em->getRepository(Cities::class)->findOneById($client->getPopulation());
  266.                     if (!empty($cityClient)){ $cityClient = $cityClient->getCity(); }
  267.                     foreach ($allClientTrackings as $track) {
  268.                         $contactClientInfo = $em->getRepository('App\Entity\ClientContact')->findOneById($track->getContactId());
  269.                         if (!empty($contactClientInfo)){
  270.                             // allPhones tendrá los telefonos y moviles
  271.                             $allPhones = !empty($contactClientInfo->getPhone()) ? $contactClientInfo->getPhone() : null;
  272.                             $allMob = !empty($contactClientInfo->getMobile()) ? $contactClientInfo->getMobile() : null;
  273.                             $allPhones = (!empty($allPhones) and !empty($allMob)) ? $allPhones.' - '.$allMob : (!empty($allPhones) ? $allPhones : $allMob);
  275.                             $arrayContactClientInfo = array(
  276.                                 'fullName' => $contactClientInfo->getName().' '.$contactClientInfo->getLastName(),
  277.                                 'email' => $contactClientInfo->getEmail(),
  278.                                 'allPhones' => $allPhones,
  279.                             );
  280.                         } else {
  281.                             $arrayContactClientInfo = array(
  282.                                 'fullName' => '',
  283.                                 'email' => '',
  284.                                 'allPhones' => '',
  285.                             );
  286.                         }
  288.                         $xlsObservations = $track->getMessage();
  290.                         if(!empty($xlsObservations)){
  291.                             $xlsObservations = str_replace('</p><p>',PHP_EOL,$xlsObservations);
  292.                             $xlsObservations = str_replace('<p>','',$xlsObservations);
  293.                             $xlsObservations = str_replace('</p>','',$xlsObservations);
  294.                             $xlsObservations = str_replace('&nbsp;',' ',$xlsObservations);
  295.                             $xlsObservations = str_replace('<br>',PHP_EOL,$xlsObservations);
  296.                         }
  298.                         $newTableData[] = array(
  299.                             'id' => $client->getId(),
  300.                             'name' => $client->getName(),
  301.                             'title' => $client->getTitle(),
  302.                             'address' => $client->getAddress(),
  303.                             'addressNumber' => $client->getAddressNumber(),
  304.                             'cityClient' => $cityClient,
  305.                             'zipCode' => $client->getZipCode(),
  306.                             'volume' => $track->getVolume(),
  307.                             'payment' => $track->getPayment(),
  308.                             'situation' => $track->getSituation(),
  309.                             'competency' => $client->getCompetency(),
  310.                             'picture' => $picture,
  311.                             'contact' => $arrayContactClientInfo['fullName'],
  312.                             'email' => $arrayContactClientInfo['email'],
  313.                             'allPhones' => $arrayContactClientInfo['allPhones'],
  314.                             'trackMessage' => $xlsObservations,
  315.                             'trackStatus' => $track->getStatus(),
  316.                             'trackDateAt' => $track->getDateAt(),
  317.                         );
  318.                     }
  320.                     // Si no hay comercial tracking se debe dejar vacio
  321.                     if (empty($newTableData)){
  322.                         $newTableData[] = array(
  323.                             'id' => $client->getId(),
  324.                             'name' => $client->getName(),
  325.                             'title' => $client->getTitle(),
  326.                             'address' => $client->getAddress(),
  327.                             'addressNumber' => $client->getAddressNumber(),
  328.                             'cityClient' => $cityClient,
  329.                             'zipCode' => $client->getZipCode(),
  330.                             'volume' => '',
  331.                             'payment' => '',
  332.                             'situation' => '',
  333.                             'competency' => $client->getCompetency(),
  334.                             'picture' => $picture,
  335.                             'contact' => '',
  336.                             'email' => '',
  337.                             'allPhones' => '',
  338.                             'trackMessage' => '',
  339.                             'trackStatus' => '',
  340.                             'trackDateAt' => '',
  341.                         );
  342.                     }
  343.                     $clientList[] = $newTableData;
  344.                 }
  346. //                $parameters = array( 'zipCode' => $dato );
  347. //                $dqlc = 'SELECT cc
  348. //                FROM App\Entity\Client cc
  349. //                WHERE cc.zipCode = :zipCode ';
  350. //
  351. //                $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  352. //                $zipCodeClients = $queryc->getResult();
  353. //
  354. //                $clientList = array();
  355. //
  356. //                foreach($zipCodeClients as $client){
  357. //
  358. //                    $picture = $client->getPicture();
  359. //                    if(!empty($picture) or $picture !== null){
  360. //                        $picture = $client->getPicture();
  361. //                    }else{
  362. //                        $picture = 'assets/images/placeholder.jpg';
  363. //                    }
  364. //                    $contacts = array();
  365. //                    $contactsInfo = $em->getRepository('App\Entity\ClientContact')->findByClientId($client->getId());
  366. //
  367. //                    foreach ($contactsInfo as $contact){
  368. //                        $contacts[] = array(
  369. //                            'name' => $contact->getName().' '.$contact->getLastName(),
  370. //                        );
  371. //                    }
  372. //                    $clientList[] = array(
  373. //                        'id' => $client->getId(),
  374. //                        'name' => $client->getName(),
  375. //                        'title' => $client->getTitle(),
  376. //                        'competency' => $client->getCompetency(),
  377. //                        'picture' => $picture,
  378. //                        'contact' => $contacts,
  379. //                        'filter' => 'Código Postal',
  380. //                        'data' => $dato,
  381. //                    );
  382. //                }
  384.             break;
  386.             case 'direction':
  387.                 $parameters = array();
  388.                 //                ---DIRECCIÓN---
  389.                 $dqlc = "SELECT cc
  390.                 FROM App\Entity\Client cc
  391.                 WHERE cc.address LIKE '%".$dato."%' ";
  392.                 $queryc = $em->createQuery($dqlc);
  393.                 $directionsPart1 = $queryc->getResult();
  395. //                ---DIRECCIÓN NÚMERO---
  396.                 $dqlc = "SELECT cc
  397.                 FROM App\Entity\Client cc
  398.                 WHERE cc.addressNumber LIKE '%".$dato."%' ";
  399.                 $queryc = $em->createQuery($dqlc);
  400.                 $directionsPart2 = $queryc->getResult();
  402.                 $competencyClients = array_merge($directionsPart1, $directionsPart2);
  403.                 $clientList = array();
  404.                 foreach($competencyClients as $client){
  405.                     $picture = $client->getPicture();
  406.                     if(!empty($picture) or $picture !== null){ $picture = $client->getPicture(); } else { $picture = 'assets/images/placeholder.jpg'; }
  408.                     //Ordenamos desde el tracking as reciente al mas antiguo
  409.                     $parameters = array( 'clientId' => $client->getId() );
  410.                     $dqlc = 'SELECT ct
  411.                              FROM CommercialBundle:CommercialTracking ct
  412.                              WHERE ct.clientId = :clientId
  413.                              ORDER BY ct.dateAt DESC';
  415.                     $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  416.                     $allClientTrackings = $queryc->getResult();
  417.                     $newTableData = array();
  418.                     $cityClient = $em->getRepository(Cities::class)->findOneById($client->getPopulation());
  419.                     if (!empty($cityClient)){ $cityClient = $cityClient->getCity(); }
  420.                     foreach ($allClientTrackings as $track) {
  421.                         $contactClientInfo = $em->getRepository('App\Entity\ClientContact')->findOneById($track->getContactId());
  422.                         if (!empty($contactClientInfo)){
  423.                             // allPhones tendrá los telefonos y moviles
  424.                             $allPhones = !empty($contactClientInfo->getPhone()) ? $contactClientInfo->getPhone() : null;
  425.                             $allMob = !empty($contactClientInfo->getMobile()) ? $contactClientInfo->getMobile() : null;
  426.                             $allPhones = (!empty($allPhones) and !empty($allMob)) ? $allPhones.' - '.$allMob : (!empty($allPhones) ? $allPhones : $allMob);
  428.                             $arrayContactClientInfo = array(
  429.                                 'fullName' => $contactClientInfo->getName().' '.$contactClientInfo->getLastName(),
  430.                                 'email' => $contactClientInfo->getEmail(),
  431.                                 'allPhones' => $allPhones,
  432.                             );
  433.                         } else {
  434.                             $arrayContactClientInfo = array(
  435.                                 'fullName' => '',
  436.                                 'email' => '',
  437.                                 'allPhones' => '',
  438.                             );
  439.                         }
  441.                         $xlsObservations = $track->getMessage();
  443.                         if(!empty($xlsObservations)){
  444.                             $xlsObservations = str_replace('</p><p>',PHP_EOL,$xlsObservations);
  445.                             $xlsObservations = str_replace('<p>','',$xlsObservations);
  446.                             $xlsObservations = str_replace('</p>','',$xlsObservations);
  447.                             $xlsObservations = str_replace('&nbsp;',' ',$xlsObservations);
  448.                             $xlsObservations = str_replace('<br>',PHP_EOL,$xlsObservations);
  449.                         }
  451.                         $newTableData[] = array(
  452.                             'id' => $client->getId(),
  453.                             'name' => $client->getName(),
  454.                             'title' => $client->getTitle(),
  455.                             'address' => $client->getAddress(),
  456.                             'addressNumber' => $client->getAddressNumber(),
  457.                             'cityClient' => $cityClient,
  458.                             'zipCode' => $client->getZipCode(),
  459.                             'volume' => $track->getVolume(),
  460.                             'payment' => $track->getPayment(),
  461.                             'situation' => $track->getSituation(),
  462.                             'competency' => $client->getCompetency(),
  463.                             'picture' => $picture,
  464.                             'contact' => $arrayContactClientInfo['fullName'],
  465.                             'email' => $arrayContactClientInfo['email'],
  466.                             'allPhones' => $arrayContactClientInfo['allPhones'],
  467.                             'trackMessage' => $xlsObservations,
  468.                             'trackStatus' => $track->getStatus(),
  469.                             'trackDateAt' => $track->getDateAt(),
  470.                         );
  471.                     }
  473.                     // Si no hay comercial tracking se debe dejar vacio
  474.                     if (empty($newTableData)){
  475.                         $newTableData[] = array(
  476.                             'id' => $client->getId(),
  477.                             'name' => $client->getName(),
  478.                             'title' => $client->getTitle(),
  479.                             'address' => $client->getAddress(),
  480.                             'addressNumber' => $client->getAddressNumber(),
  481.                             'cityClient' => $cityClient,
  482.                             'zipCode' => $client->getZipCode(),
  483.                             'volume' => '',
  484.                             'payment' => '',
  485.                             'situation' => '',
  486.                             'competency' => $client->getCompetency(),
  487.                             'picture' => $picture,
  488.                             'contact' => '',
  489.                             'email' => '',
  490.                             'allPhones' => '',
  491.                             'trackMessage' => '',
  492.                             'trackStatus' => '',
  493.                             'trackDateAt' => '',
  494.                         );
  495.                     }
  496.                     $clientList[] = $newTableData;
  497.                 }
  498.                 break;
  500.             case 'contact':
  502.                 $arrayDatos = explode(" ", $dato);
  503.                 $parameters = array();
  504.                 $clientsContacts = array();
  505.                 foreach ($arrayDatos as $item){
  506.                     if (strlen($item) > 3) {    // Descartar "la" "de" "del"
  507.                         $dqlc = "SELECT cc
  508.                             FROM App\Entity\ClientContact cc
  509.                             WHERE cc.name LIKE '%" . $item . "%' OR  cc.lastName LIKE '%" . $item . "%'";
  511.                         $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  512.                         $clientsContacts = array_merge($clientsContacts, $queryc->getResult());
  513.                     }
  514.                 }
  516.                 $arrayClients = array();
  517.                 $competencyClients = array();
  518.                 foreach ($clientsContacts as $item){ $arrayClients[$item->getClientId()] = $item->getClientId(); }
  519.                 foreach ($arrayClients as $item){
  520.                     $cli = $em->getRepository(Client::class)->findOneById($item);
  521.                     $competencyClients[] = $cli;
  522.                 }
  524.                 $clientList = array();
  525.                 foreach($competencyClients as $client){
  526.                     $picture = $client->getPicture();
  527.                     if(!empty($picture) or $picture !== null){ $picture = $client->getPicture(); } else { $picture = 'assets/images/placeholder.jpg'; }
  529.                     //Ordenamos desde el tracking as reciente al mas antiguo
  530.                     $parameters = array( 'clientId' => $client->getId() );
  531.                     $dqlc = 'SELECT ct
  532.                              FROM CommercialBundle:CommercialTracking ct
  533.                              WHERE ct.clientId = :clientId
  534.                              ORDER BY ct.dateAt DESC';
  536.                     $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  537.                     $allClientTrackings = $queryc->getResult();
  538.                     $newTableData = array();
  539.                     $cityClient = $em->getRepository(Cities::class)->findOneById($client->getPopulation());
  540.                     if (!empty($cityClient)){ $cityClient = $cityClient->getCity(); }
  541.                     foreach ($allClientTrackings as $track) {
  542.                         $contactClientInfo = $em->getRepository('App\Entity\ClientContact')->findOneById($track->getContactId());
  543.                         if (!empty($contactClientInfo)){
  544.                             // allPhones tendrá los telefonos y moviles
  545.                             $allPhones = !empty($contactClientInfo->getPhone()) ? $contactClientInfo->getPhone() : null;
  546.                             $allMob = !empty($contactClientInfo->getMobile()) ? $contactClientInfo->getMobile() : null;
  547.                             $allPhones = (!empty($allPhones) and !empty($allMob)) ? $allPhones.' - '.$allMob : (!empty($allPhones) ? $allPhones : $allMob);
  549.                             $arrayContactClientInfo = array(
  550.                                 'fullName' => $contactClientInfo->getName().' '.$contactClientInfo->getLastName(),
  551.                                 'email' => $contactClientInfo->getEmail(),
  552.                                 'allPhones' => $allPhones,
  553.                             );
  554.                         } else {
  555.                             $arrayContactClientInfo = array(
  556.                                 'fullName' => '',
  557.                                 'email' => '',
  558.                                 'allPhones' => '',
  559.                             );
  560.                         }
  562.                         $xlsObservations = $track->getMessage();
  564.                         if(!empty($xlsObservations)){
  565.                             $xlsObservations = str_replace('</p><p>',PHP_EOL,$xlsObservations);
  566.                             $xlsObservations = str_replace('<p>','',$xlsObservations);
  567.                             $xlsObservations = str_replace('</p>','',$xlsObservations);
  568.                             $xlsObservations = str_replace('&nbsp;',' ',$xlsObservations);
  569.                             $xlsObservations = str_replace('<br>',PHP_EOL,$xlsObservations);
  570.                         }
  572.                         $newTableData[] = array(
  573.                             'id' => $client->getId(),
  574.                             'name' => $client->getName(),
  575.                             'title' => $client->getTitle(),
  576.                             'address' => $client->getAddress(),
  577.                             'addressNumber' => $client->getAddressNumber(),
  578.                             'cityClient' => $cityClient,
  579.                             'zipCode' => $client->getZipCode(),
  580.                             'volume' => $track->getVolume(),
  581.                             'payment' => $track->getPayment(),
  582.                             'situation' => $track->getSituation(),
  583.                             'competency' => $client->getCompetency(),
  584.                             'picture' => $picture,
  585.                             'contact' => $arrayContactClientInfo['fullName'],
  586.                             'email' => $arrayContactClientInfo['email'],
  587.                             'allPhones' => $arrayContactClientInfo['allPhones'],
  588.                             'trackMessage' => $xlsObservations,
  589.                             'trackStatus' => $track->getStatus(),
  590.                             'trackDateAt' => $track->getDateAt(),
  591.                         );
  592.                     }
  594.                     // Si no hay comercial tracking se debe dejar vacio
  595.                     if (empty($newTableData)){
  596.                         $newTableData[] = array(
  597.                             'id' => $client->getId(),
  598.                             'name' => $client->getName(),
  599.                             'title' => $client->getTitle(),
  600.                             'address' => $client->getAddress(),
  601.                             'addressNumber' => $client->getAddressNumber(),
  602.                             'cityClient' => $cityClient,
  603.                             'zipCode' => $client->getZipCode(),
  604.                             'volume' => '',
  605.                             'payment' => '',
  606.                             'situation' => '',
  607.                             'competency' => $client->getCompetency(),
  608.                             'picture' => $picture,
  609.                             'contact' => '',
  610.                             'email' => '',
  611.                             'allPhones' => '',
  612.                             'trackMessage' => '',
  613.                             'trackStatus' => '',
  614.                             'trackDateAt' => '',
  615.                         );
  616.                     }
  617.                     $clientList[] = $newTableData;
  618.                 }
  619.                 break;
  621.             case 'competencies':
  622.                 $parameters = array();
  624.                 $dqlc = "SELECT cc
  625.                 FROM App\Entity\Client cc
  626.                 WHERE cc.competency LIKE '%".$dato."%' ";
  628.                 $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  629.                 $competencyClients = $queryc->getResult();
  631.                 $clientList = array();
  632.                 foreach($competencyClients as $client){
  633.                     $picture = $client->getPicture();
  634.                     if(!empty($picture) or $picture !== null){ $picture = $client->getPicture(); } else { $picture = 'assets/images/placeholder.jpg'; }
  636.                     //Ordenamos desde el tracking as reciente al mas antiguo
  637.                     $parameters = array( 'clientId' => $client->getId() );
  638.                     $dqlc = 'SELECT ct
  639.                              FROM CommercialBundle:CommercialTracking ct
  640.                              WHERE ct.clientId = :clientId
  641.                              ORDER BY ct.dateAt DESC';
  643.                     $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  644.                     $allClientTrackings = $queryc->getResult();
  645.                     $newTableData = array();
  646.                     $cityClient = $em->getRepository(Cities::class)->findOneById($client->getPopulation());
  647.                     if (!empty($cityClient)){ $cityClient = $cityClient->getCity(); }
  648.                     foreach ($allClientTrackings as $track) {
  649.                         $contactClientInfo = $em->getRepository('App\Entity\ClientContact')->findOneById($track->getContactId());
  650.                         if (!empty($contactClientInfo)){
  651.                             // allPhones tendrá los telefonos y moviles
  652.                             $allPhones = !empty($contactClientInfo->getPhone()) ? $contactClientInfo->getPhone() : null;
  653.                             $allMob = !empty($contactClientInfo->getMobile()) ? $contactClientInfo->getMobile() : null;
  654.                             $allPhones = (!empty($allPhones) and !empty($allMob)) ? $allPhones.' - '.$allMob : (!empty($allPhones) ? $allPhones : $allMob);
  656.                             $arrayContactClientInfo = array(
  657.                                 'fullName' => $contactClientInfo->getName().' '.$contactClientInfo->getLastName(),
  658.                                 'email' => $contactClientInfo->getEmail(),
  659.                                 'allPhones' => $allPhones,
  660.                             );
  661.                         } else {
  662.                             $arrayContactClientInfo = array(
  663.                                 'fullName' => '',
  664.                                 'email' => '',
  665.                                 'allPhones' => '',
  666.                             );
  667.                         }
  669.                         $xlsObservations = $track->getMessage();
  671.                         if(!empty($xlsObservations)){
  672.                             $xlsObservations = str_replace('</p><p>',PHP_EOL,$xlsObservations);
  673.                             $xlsObservations = str_replace('<p>','',$xlsObservations);
  674.                             $xlsObservations = str_replace('</p>','',$xlsObservations);
  675.                             $xlsObservations = str_replace('&nbsp;',' ',$xlsObservations);
  676.                             $xlsObservations = str_replace('<br>',PHP_EOL,$xlsObservations);
  677.                         }
  679.                         $newTableData[] = array(
  680.                             'id' => $client->getId(),
  681.                             'name' => $client->getName(),
  682.                             'title' => $client->getTitle(),
  683.                             'address' => $client->getAddress(),
  684.                             'addressNumber' => $client->getAddressNumber(),
  685.                             'cityClient' => $cityClient,
  686.                             'zipCode' => $client->getZipCode(),
  687.                             'volume' => $track->getVolume(),
  688.                             'payment' => $track->getPayment(),
  689.                             'situation' => $track->getSituation(),
  690.                             'competency' => $client->getCompetency(),
  691.                             'picture' => $picture,
  692.                             'contact' => $arrayContactClientInfo['fullName'],
  693.                             'email' => $arrayContactClientInfo['email'],
  694.                             'allPhones' => $arrayContactClientInfo['allPhones'],
  695.                             'trackMessage' => $xlsObservations,
  696.                             'trackStatus' => $track->getStatus(),
  697.                             'trackDateAt' => $track->getDateAt(),
  698.                         );
  699.                     }
  701.                     // Si no hay comercial tracking se debe dejar vacio
  702.                     if (empty($newTableData)){
  703.                         $newTableData[] = array(
  704.                             'id' => $client->getId(),
  705.                             'name' => $client->getName(),
  706.                             'title' => $client->getTitle(),
  707.                             'address' => $client->getAddress(),
  708.                             'addressNumber' => $client->getAddressNumber(),
  709.                             'cityClient' => $cityClient,
  710.                             'zipCode' => $client->getZipCode(),
  711.                             'volume' => '',
  712.                             'payment' => '',
  713.                             'situation' => '',
  714.                             'competency' => $client->getCompetency(),
  715.                             'picture' => $picture,
  716.                             'contact' => '',
  717.                             'email' => '',
  718.                             'allPhones' => '',
  719.                             'trackMessage' => '',
  720.                             'trackStatus' => '',
  721.                             'trackDateAt' => '',
  722.                         );
  723.                     }
  724.                     $clientList[] = $newTableData;
  725.                 }
  727.                 break;
  729.             case 'email':
  730.                 $parameters = array();
  732.                 $dqlc = "SELECT cc
  733.                 FROM App\Entity\ClientContact cc
  734.                 WHERE cc.email LIKE '%".$dato."%' ";
  735.                 $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  736.                 $arrayCompetencyClients = $queryc->getResult();
  738.                 $arrayClients = array();
  739.                 $competencyClients = array();
  740.                 foreach ($arrayCompetencyClients as $item){ $arrayClients[$item->getClientId()] = $item->getClientId(); }
  741.                 foreach ($arrayClients as $item){
  742.                     $cli = $em->getRepository(Client::class)->findOneById($item);
  743.                     if (!empty($cli)) { $competencyClients[] = $cli; }
  744.                 }
  746.                 $dqlc = "SELECT cc
  747.                 FROM App\Entity\Client cc
  748.                 WHERE cc.email LIKE '%".$dato."%' ";
  749.                 $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  750.                 $competencyClients = array_merge($competencyClients, $queryc->getResult());
  752.                 $clientList = array();
  753.                 foreach($competencyClients as $client){
  754.                     $picture = $client->getPicture();
  755.                     if(!empty($picture) or $picture !== null){ $picture = $client->getPicture(); } else { $picture = 'assets/images/placeholder.jpg'; }
  757.                     //Ordenamos desde el tracking as reciente al mas antiguo
  758.                     $parameters = array( 'clientId' => $client->getId() );
  759.                     $dqlc = 'SELECT ct
  760.                              FROM CommercialBundle:CommercialTracking ct
  761.                              WHERE ct.clientId = :clientId
  762.                              ORDER BY ct.dateAt DESC';
  764.                     $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  765.                     $allClientTrackings = $queryc->getResult();
  766.                     $newTableData = array();
  767.                     $cityClient = $em->getRepository(Cities::class)->findOneById($client->getPopulation());
  768.                     if (!empty($cityClient)){ $cityClient = $cityClient->getCity(); }
  769.                     foreach ($allClientTrackings as $track) {
  770.                         $contactClientInfo = $em->getRepository('App\Entity\ClientContact')->findOneById($track->getContactId());
  771.                         if (!empty($contactClientInfo)){
  772.                             // allPhones tendrá los telefonos y moviles
  773.                             $allPhones = !empty($contactClientInfo->getPhone()) ? $contactClientInfo->getPhone() : null;
  774.                             $allMob = !empty($contactClientInfo->getMobile()) ? $contactClientInfo->getMobile() : null;
  775.                             $allPhones = (!empty($allPhones) and !empty($allMob)) ? $allPhones.' - '.$allMob : (!empty($allPhones) ? $allPhones : $allMob);
  777.                             $arrayContactClientInfo = array(
  778.                                 'fullName' => $contactClientInfo->getName().' '.$contactClientInfo->getLastName(),
  779.                                 'email' => $contactClientInfo->getEmail(),
  780.                                 'allPhones' => $allPhones,
  781.                             );
  782.                         } else {
  783.                             $arrayContactClientInfo = array(
  784.                                 'fullName' => '',
  785.                                 'email' => '',
  786.                                 'allPhones' => '',
  787.                             );
  788.                         }
  790.                         $xlsObservations = $track->getMessage();
  792.                         if(!empty($xlsObservations)){
  793.                             $xlsObservations = str_replace('</p><p>',PHP_EOL,$xlsObservations);
  794.                             $xlsObservations = str_replace('<p>','',$xlsObservations);
  795.                             $xlsObservations = str_replace('</p>','',$xlsObservations);
  796.                             $xlsObservations = str_replace('&nbsp;',' ',$xlsObservations);
  797.                             $xlsObservations = str_replace('<br>',PHP_EOL,$xlsObservations);
  798.                         }
  800.                         $newTableData[] = array(
  801.                             'id' => $client->getId(),
  802.                             'name' => $client->getName(),
  803.                             'title' => $client->getTitle(),
  804.                             'address' => $client->getAddress(),
  805.                             'addressNumber' => $client->getAddressNumber(),
  806.                             'cityClient' => $cityClient,
  807.                             'zipCode' => $client->getZipCode(),
  808.                             'volume' => $track->getVolume(),
  809.                             'payment' => $track->getPayment(),
  810.                             'situation' => $track->getSituation(),
  811.                             'competency' => $client->getCompetency(),
  812.                             'picture' => $picture,
  813.                             'contact' => $arrayContactClientInfo['fullName'],
  814.                             'email' => $arrayContactClientInfo['email'],
  815.                             'allPhones' => $arrayContactClientInfo['allPhones'],
  816.                             'trackMessage' => $xlsObservations,
  817.                             'trackStatus' => $track->getStatus(),
  818.                             'trackDateAt' => $track->getDateAt(),
  819.                         );
  820.                     }
  822.                     // Si no hay comercial tracking se debe dejar vacio
  823.                     if (empty($newTableData)){
  824.                         $newTableData[] = array(
  825.                             'id' => $client->getId(),
  826.                             'name' => $client->getName(),
  827.                             'title' => $client->getTitle(),
  828.                             'address' => $client->getAddress(),
  829.                             'addressNumber' => $client->getAddressNumber(),
  830.                             'cityClient' => $cityClient,
  831.                             'zipCode' => $client->getZipCode(),
  832.                             'volume' => '',
  833.                             'payment' => '',
  834.                             'situation' => '',
  835.                             'competency' => $client->getCompetency(),
  836.                             'picture' => $picture,
  837.                             'contact' => '',
  838.                             'email' => '',
  839.                             'allPhones' => '',
  840.                             'trackMessage' => '',
  841.                             'trackStatus' => '',
  842.                             'trackDateAt' => '',
  843.                         );
  844.                     }
  845.                     $clientList[] = $newTableData;
  846.                 }
  847.                 break;
  849.             case 'phone':
  850.                 $parameters = array();
  852.                 $dqlc = "SELECT cc
  853.                 FROM App\Entity\ClientContact cc
  854.                 WHERE cc.phone LIKE '%".$dato."%' OR cc.mobile LIKE '%".$dato."%' ";
  855.                 $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  856.                 $arrayCompetencyClients = $queryc->getResult();
  858.                 $arrayClients = array();
  859.                 $competencyClients = array();
  860.                 foreach ($arrayCompetencyClients as $item){ $arrayClients[$item->getClientId()] = $item->getClientId(); }
  861.                 foreach ($arrayClients as $item){
  862.                     $cli = $em->getRepository(Client::class)->findOneById($item);
  863.                     if (!empty($cli)) { $competencyClients[] = $cli; }
  864.                 }
  866.                 $dqlc = "SELECT cc
  867.                 FROM App\Entity\Client cc
  868.                 WHERE cc.telephone LIKE '%".$dato."%' ";
  869.                 $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  870.                 $competencyClients = array_merge($competencyClients, $queryc->getResult());
  872.                 $clientList = array();
  873.                 foreach($competencyClients as $client){
  874.                     $picture = $client->getPicture();
  875.                     if(!empty($picture) or $picture !== null){ $picture = $client->getPicture(); } else { $picture = 'assets/images/placeholder.jpg'; }
  877.                     //Ordenamos desde el tracking as reciente al mas antiguo
  878.                     $parameters = array( 'clientId' => $client->getId() );
  879.                     $dqlc = 'SELECT ct
  880.                              FROM CommercialBundle:CommercialTracking ct
  881.                              WHERE ct.clientId = :clientId
  882.                              ORDER BY ct.dateAt DESC';
  884.                     $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  885.                     $allClientTrackings = $queryc->getResult();
  886.                     $newTableData = array();
  887.                     $cityClient = $em->getRepository(Cities::class)->findOneById($client->getPopulation());
  888.                     if (!empty($cityClient)){ $cityClient = $cityClient->getCity(); }
  889.                     foreach ($allClientTrackings as $track) {
  890.                         $contactClientInfo = $em->getRepository('App\Entity\ClientContact')->findOneById($track->getContactId());
  891.                         if (!empty($contactClientInfo)){
  892.                             // allPhones tendrá los telefonos y moviles
  893.                             $allPhones = !empty($contactClientInfo->getPhone()) ? $contactClientInfo->getPhone() : null;
  894.                             $allMob = !empty($contactClientInfo->getMobile()) ? $contactClientInfo->getMobile() : null;
  895.                             $allPhones = (!empty($allPhones) and !empty($allMob)) ? $allPhones.' - '.$allMob : (!empty($allPhones) ? $allPhones : $allMob);
  897.                             $arrayContactClientInfo = array(
  898.                                 'fullName' => $contactClientInfo->getName().' '.$contactClientInfo->getLastName(),
  899.                                 'email' => $contactClientInfo->getEmail(),
  900.                                 'allPhones' => $allPhones,
  901.                             );
  902.                         } else {
  903.                             $arrayContactClientInfo = array(
  904.                                 'fullName' => '',
  905.                                 'email' => '',
  906.                                 'allPhones' => '',
  907.                             );
  908.                         }
  910.                         $xlsObservations = $track->getMessage();
  912.                         if(!empty($xlsObservations)){
  913.                             $xlsObservations = str_replace('</p><p>',PHP_EOL,$xlsObservations);
  914.                             $xlsObservations = str_replace('<p>','',$xlsObservations);
  915.                             $xlsObservations = str_replace('</p>','',$xlsObservations);
  916.                             $xlsObservations = str_replace('&nbsp;',' ',$xlsObservations);
  917.                             $xlsObservations = str_replace('<br>',PHP_EOL,$xlsObservations);
  918.                         }
  920.                         $newTableData[] = array(
  921.                             'id' => $client->getId(),
  922.                             'name' => $client->getName(),
  923.                             'title' => $client->getTitle(),
  924.                             'address' => $client->getAddress(),
  925.                             'addressNumber' => $client->getAddressNumber(),
  926.                             'cityClient' => $cityClient,
  927.                             'zipCode' => $client->getZipCode(),
  928.                             'volume' => $track->getVolume(),
  929.                             'payment' => $track->getPayment(),
  930.                             'situation' => $track->getSituation(),
  931.                             'competency' => $client->getCompetency(),
  932.                             'picture' => $picture,
  933.                             'contact' => $arrayContactClientInfo['fullName'],
  934.                             'email' => $arrayContactClientInfo['email'],
  935.                             'allPhones' => $arrayContactClientInfo['allPhones'],
  936.                             'trackMessage' => $xlsObservations,
  937.                             'trackStatus' => $track->getStatus(),
  938.                             'trackDateAt' => $track->getDateAt(),
  939.                         );
  940.                     }
  942.                     // Si no hay comercial tracking se debe dejar vacio
  943.                     if (empty($newTableData)){
  944.                         $newTableData[] = array(
  945.                             'id' => $client->getId(),
  946.                             'name' => $client->getName(),
  947.                             'title' => $client->getTitle(),
  948.                             'address' => $client->getAddress(),
  949.                             'addressNumber' => $client->getAddressNumber(),
  950.                             'cityClient' => $cityClient,
  951.                             'zipCode' => $client->getZipCode(),
  952.                             'volume' => '',
  953.                             'payment' => '',
  954.                             'situation' => '',
  955.                             'competency' => $client->getCompetency(),
  956.                             'picture' => $picture,
  957.                             'contact' => '',
  958.                             'email' => '',
  959.                             'allPhones' => '',
  960.                             'trackMessage' => '',
  961.                             'trackStatus' => '',
  962.                             'trackDateAt' => '',
  963.                         );
  964.                     }
  965.                     $clientList[] = $newTableData;
  966.                 }
  967.                 break;
  969.             case 'volumeless':
  970.                 $allComTrack = $em->getRepository(CommercialTracking::class)->findAll();
  971.                 $arrayComTrack = array();
  973.                 foreach ($allComTrack as $item){
  974.                     // 200.000; 1050.000 es numero
  975.                     // 2.000.000; 200 000; 200,000; 90000 €; Desconocido; Null, no es un numero
  977.                     $datoVol = str_replace('.','',$item->getVolume());      // Los .
  978.                     $datoVol = str_replace(' ','',$datoVol);                // Los ' '
  979.                     $datoVol = str_replace(',','',$datoVol);                // Los ' '
  980.                     $datoVol = str_replace('€','',$datoVol);                // Los €
  981.                     $datoVol = str_replace('$','',$datoVol);                // Los $
  983.                     // Se busca hasta el volumen indicado. Victor Muñoz
  984.                     if (is_numeric($datoVol)){ if ($datoVol <= $dato) { $arrayComTrack[] = $item; } }
  985.                 }
  987.                 $arrayClients = array();
  988.                 $competencyClients = array();
  989.                 foreach ($arrayComTrack as $item){ $arrayClients[$item->getClientId()] = $item->getClientId(); }
  990.                 foreach ($arrayClients as $item){
  991.                     $cli = $em->getRepository(Client::class)->findOneById($item);
  992.                     if (!empty($cli)) { $competencyClients[] = $cli; }
  993.                 }
  995.                 $clientList = array();
  996.                 foreach($competencyClients as $client){
  997.                     $picture = $client->getPicture();
  998.                     if(!empty($picture) or $picture !== null){ $picture = $client->getPicture(); } else { $picture = 'assets/images/placeholder.jpg'; }
  1000.                     //Ordenamos desde el tracking as reciente al mas antiguo
  1001.                     $parameters = array( 'clientId' => $client->getId() );
  1002.                     $dqlc = 'SELECT ct
  1003.                              FROM CommercialBundle:CommercialTracking ct
  1004.                              WHERE ct.clientId = :clientId
  1005.                              ORDER BY ct.dateAt DESC';
  1007.                     $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  1008.                     $allClientTrackings = $queryc->getResult();
  1009.                     $newTableData = array();
  1010.                     $cityClient = $em->getRepository(Cities::class)->findOneById($client->getPopulation());
  1011.                     if (!empty($cityClient)){ $cityClient = $cityClient->getCity(); }
  1012.                     foreach ($allClientTrackings as $track) {
  1013.                         $contactClientInfo = $em->getRepository('App\Entity\ClientContact')->findOneById($track->getContactId());
  1014.                         if (!empty($contactClientInfo)){
  1015.                             // allPhones tendrá los telefonos y moviles
  1016.                             $allPhones = !empty($contactClientInfo->getPhone()) ? $contactClientInfo->getPhone() : null;
  1017.                             $allMob = !empty($contactClientInfo->getMobile()) ? $contactClientInfo->getMobile() : null;
  1018.                             $allPhones = (!empty($allPhones) and !empty($allMob)) ? $allPhones.' - '.$allMob : (!empty($allPhones) ? $allPhones : $allMob);
  1020.                             $arrayContactClientInfo = array(
  1021.                                 'fullName' => $contactClientInfo->getName().' '.$contactClientInfo->getLastName(),
  1022.                                 'email' => $contactClientInfo->getEmail(),
  1023.                                 'allPhones' => $allPhones,
  1024.                             );
  1025.                         } else {
  1026.                             $arrayContactClientInfo = array(
  1027.                                 'fullName' => '',
  1028.                                 'email' => '',
  1029.                                 'allPhones' => '',
  1030.                             );
  1031.                         }
  1033.                         $xlsObservations = $track->getMessage();
  1035.                         if(!empty($xlsObservations)){
  1036.                             $xlsObservations = str_replace('</p><p>',PHP_EOL,$xlsObservations);
  1037.                             $xlsObservations = str_replace('<p>','',$xlsObservations);
  1038.                             $xlsObservations = str_replace('</p>','',$xlsObservations);
  1039.                             $xlsObservations = str_replace('&nbsp;',' ',$xlsObservations);
  1040.                             $xlsObservations = str_replace('<br>',PHP_EOL,$xlsObservations);
  1041.                         }
  1043.                         $newTableData[] = array(
  1044.                             'id' => $client->getId(),
  1045.                             'name' => $client->getName(),
  1046.                             'title' => $client->getTitle(),
  1047.                             'address' => $client->getAddress(),
  1048.                             'addressNumber' => $client->getAddressNumber(),
  1049.                             'cityClient' => $cityClient,
  1050.                             'zipCode' => $client->getZipCode(),
  1051.                             'volume' => $track->getVolume(),
  1052.                             'payment' => $track->getPayment(),
  1053.                             'situation' => $track->getSituation(),
  1054.                             'competency' => $client->getCompetency(),
  1055.                             'picture' => $picture,
  1056.                             'contact' => $arrayContactClientInfo['fullName'],
  1057.                             'email' => $arrayContactClientInfo['email'],
  1058.                             'allPhones' => $arrayContactClientInfo['allPhones'],
  1059.                             'trackMessage' => $xlsObservations,
  1060.                             'trackStatus' => $track->getStatus(),
  1061.                             'trackDateAt' => $track->getDateAt(),
  1062.                         );
  1063.                     }
  1065.                     // Si no hay comercial tracking se debe dejar vacio
  1066.                     if (empty($newTableData)){
  1067.                         $newTableData[] = array(
  1068.                             'id' => $client->getId(),
  1069.                             'name' => $client->getName(),
  1070.                             'title' => $client->getTitle(),
  1071.                             'address' => $client->getAddress(),
  1072.                             'addressNumber' => $client->getAddressNumber(),
  1073.                             'cityClient' => $cityClient,
  1074.                             'zipCode' => $client->getZipCode(),
  1075.                             'volume' => '',
  1076.                             'payment' => '',
  1077.                             'situation' => '',
  1078.                             'competency' => $client->getCompetency(),
  1079.                             'picture' => $picture,
  1080.                             'contact' => '',
  1081.                             'email' => '',
  1082.                             'allPhones' => '',
  1083.                             'trackMessage' => '',
  1084.                             'trackStatus' => '',
  1085.                             'trackDateAt' => '',
  1086.                         );
  1087.                     }
  1088.                     $clientList[] = $newTableData;
  1089.                 }
  1090.                 break;
  1092.             case 'volumegreat':
  1093.                 $allComTrack = $em->getRepository(CommercialTracking::class)->findAll();
  1094.                 $arrayComTrack = array();
  1096.                 foreach ($allComTrack as $item){
  1097.                     // 200.000; 1050.000 es numero
  1098.                     // 2.000.000; 200 000; 200,000; 90000 €; Desconocido; Null, no es un numero
  1100.                     $datoVol = str_replace('.','',$item->getVolume());      // Los .
  1101.                     $datoVol = str_replace(' ','',$datoVol);                // Los ' '
  1102.                     $datoVol = str_replace(',','',$datoVol);                // Los ' '
  1103.                     $datoVol = str_replace('€','',$datoVol);                // Los €
  1104.                     $datoVol = str_replace('$','',$datoVol);                // Los $
  1106.                     // Se busca hasta el volumen indicado. Victor Muñoz
  1107.                     if (is_numeric($datoVol)){ if ($datoVol >= $dato) { $arrayComTrack[] = $item; } }
  1108.                 }
  1110.                 $arrayClients = array();
  1111.                 $competencyClients = array();
  1112.                 foreach ($arrayComTrack as $item){ $arrayClients[$item->getClientId()] = $item->getClientId(); }
  1113.                 foreach ($arrayClients as $item){
  1114.                     $cli = $em->getRepository(Client::class)->findOneById($item);
  1115.                     if (!empty($cli)) { $competencyClients[] = $cli; }
  1116.                 }
  1118.                 $clientList = array();
  1119.                 foreach($competencyClients as $client){
  1120.                     $picture = $client->getPicture();
  1121.                     if(!empty($picture) or $picture !== null){ $picture = $client->getPicture(); } else { $picture = 'assets/images/placeholder.jpg'; }
  1123.                     //Ordenamos desde el tracking as reciente al mas antiguo
  1124.                     $parameters = array( 'clientId' => $client->getId() );
  1125.                     $dqlc = 'SELECT ct
  1126.                              FROM CommercialBundle:CommercialTracking ct
  1127.                              WHERE ct.clientId = :clientId
  1128.                              ORDER BY ct.dateAt DESC';
  1130.                     $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  1131.                     $allClientTrackings = $queryc->getResult();
  1132.                     $newTableData = array();
  1133.                     $cityClient = $em->getRepository(Cities::class)->findOneById($client->getPopulation());
  1134.                     if (!empty($cityClient)){ $cityClient = $cityClient->getCity(); }
  1135.                     foreach ($allClientTrackings as $track) {
  1136.                         $contactClientInfo = $em->getRepository('App\Entity\ClientContact')->findOneById($track->getContactId());
  1137.                         if (!empty($contactClientInfo)){
  1138.                             // allPhones tendrá los telefonos y moviles
  1139.                             $allPhones = !empty($contactClientInfo->getPhone()) ? $contactClientInfo->getPhone() : null;
  1140.                             $allMob = !empty($contactClientInfo->getMobile()) ? $contactClientInfo->getMobile() : null;
  1141.                             $allPhones = (!empty($allPhones) and !empty($allMob)) ? $allPhones.' - '.$allMob : (!empty($allPhones) ? $allPhones : $allMob);
  1143.                             $arrayContactClientInfo = array(
  1144.                                 'fullName' => $contactClientInfo->getName().' '.$contactClientInfo->getLastName(),
  1145.                                 'email' => $contactClientInfo->getEmail(),
  1146.                                 'allPhones' => $allPhones,
  1147.                             );
  1148.                         } else {
  1149.                             $arrayContactClientInfo = array(
  1150.                                 'fullName' => '',
  1151.                                 'email' => '',
  1152.                                 'allPhones' => '',
  1153.                             );
  1154.                         }
  1156.                         $xlsObservations = $track->getMessage();
  1158.                         if(!empty($xlsObservations)){
  1159.                             $xlsObservations = str_replace('</p><p>',PHP_EOL,$xlsObservations);
  1160.                             $xlsObservations = str_replace('<p>','',$xlsObservations);
  1161.                             $xlsObservations = str_replace('</p>','',$xlsObservations);
  1162.                             $xlsObservations = str_replace('&nbsp;',' ',$xlsObservations);
  1163.                             $xlsObservations = str_replace('<br>',PHP_EOL,$xlsObservations);
  1164.                         }
  1166.                         $newTableData[] = array(
  1167.                             'id' => $client->getId(),
  1168.                             'name' => $client->getName(),
  1169.                             'title' => $client->getTitle(),
  1170.                             'address' => $client->getAddress(),
  1171.                             'addressNumber' => $client->getAddressNumber(),
  1172.                             'cityClient' => $cityClient,
  1173.                             'zipCode' => $client->getZipCode(),
  1174.                             'volume' => $track->getVolume(),
  1175.                             'payment' => $track->getPayment(),
  1176.                             'situation' => $track->getSituation(),
  1177.                             'competency' => $client->getCompetency(),
  1178.                             'picture' => $picture,
  1179.                             'contact' => $arrayContactClientInfo['fullName'],
  1180.                             'email' => $arrayContactClientInfo['email'],
  1181.                             'allPhones' => $arrayContactClientInfo['allPhones'],
  1182.                             'trackMessage' => $xlsObservations,
  1183.                             'trackStatus' => $track->getStatus(),
  1184.                             'trackDateAt' => $track->getDateAt(),
  1185.                         );
  1186.                     }
  1188.                     // Si no hay comercial tracking se debe dejar vacio
  1189.                     if (empty($newTableData)){
  1190.                         $newTableData[] = array(
  1191.                             'id' => $client->getId(),
  1192.                             'name' => $client->getName(),
  1193.                             'title' => $client->getTitle(),
  1194.                             'address' => $client->getAddress(),
  1195.                             'addressNumber' => $client->getAddressNumber(),
  1196.                             'cityClient' => $cityClient,
  1197.                             'zipCode' => $client->getZipCode(),
  1198.                             'volume' => '',
  1199.                             'payment' => '',
  1200.                             'situation' => '',
  1201.                             'competency' => $client->getCompetency(),
  1202.                             'picture' => $picture,
  1203.                             'contact' => '',
  1204.                             'email' => '',
  1205.                             'allPhones' => '',
  1206.                             'trackMessage' => '',
  1207.                             'trackStatus' => '',
  1208.                             'trackDateAt' => '',
  1209.                         );
  1210.                     }
  1211.                     $clientList[] = $newTableData;
  1212.                 }
  1213.                 break;
  1215.             case 'volumeblone':
  1216.                 // Bloque de 0 a 50K
  1217.                 $allComTrack = $em->getRepository(CommercialTracking::class)->findAll();
  1218.                 $arrayComTrack = array();
  1220.                 foreach ($allComTrack as $item){
  1221.                     // 200.000; 1050.000 es numero
  1222.                     // 2.000.000; 200 000; 200,000; 90000 €; Desconocido; Null, no es un numero
  1224.                     $datoVol = str_replace('.','',$item->getVolume());      // Los .
  1225.                     $datoVol = str_replace(' ','',$datoVol);                // Los ' '
  1226.                     $datoVol = str_replace(',','',$datoVol);                // Los ' '
  1227.                     $datoVol = str_replace('€','',$datoVol);                // Los €
  1228.                     $datoVol = str_replace('$','',$datoVol);                // Los $
  1230.                     // Se busca por bloque. Daniel Guerrero
  1231.                     if (is_numeric($datoVol)){ if (($datoVol >= 0) and ($datoVol <= 50000)) { $arrayComTrack[] = $item; } }
  1232.                 }
  1234.                 $arrayClients = array();
  1235.                 $competencyClients = array();
  1236.                 foreach ($arrayComTrack as $item){ $arrayClients[$item->getClientId()] = $item->getClientId(); }
  1237.                 foreach ($arrayClients as $item){
  1238.                     $cli = $em->getRepository(Client::class)->findOneById($item);
  1239.                     if (!empty($cli)) { $competencyClients[] = $cli; }
  1240.                 }
  1242.                 $clientList = array();
  1243.                 foreach($competencyClients as $client){
  1244.                     $picture = $client->getPicture();
  1245.                     if(!empty($picture) or $picture !== null){ $picture = $client->getPicture(); } else { $picture = 'assets/images/placeholder.jpg'; }
  1247.                     //Ordenamos desde el tracking as reciente al mas antiguo
  1248.                     $parameters = array( 'clientId' => $client->getId() );
  1249.                     $dqlc = 'SELECT ct
  1250.                              FROM CommercialBundle:CommercialTracking ct
  1251.                              WHERE ct.clientId = :clientId
  1252.                              ORDER BY ct.dateAt DESC';
  1254.                     $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  1255.                     $allClientTrackings = $queryc->getResult();
  1256.                     $newTableData = array();
  1257.                     $cityClient = $em->getRepository(Cities::class)->findOneById($client->getPopulation());
  1258.                     if (!empty($cityClient)){ $cityClient = $cityClient->getCity(); }
  1259.                     foreach ($allClientTrackings as $track) {
  1260.                         $contactClientInfo = $em->getRepository('App\Entity\ClientContact')->findOneById($track->getContactId());
  1261.                         if (!empty($contactClientInfo)){
  1262.                             // allPhones tendrá los telefonos y moviles
  1263.                             $allPhones = !empty($contactClientInfo->getPhone()) ? $contactClientInfo->getPhone() : null;
  1264.                             $allMob = !empty($contactClientInfo->getMobile()) ? $contactClientInfo->getMobile() : null;
  1265.                             $allPhones = (!empty($allPhones) and !empty($allMob)) ? $allPhones.' - '.$allMob : (!empty($allPhones) ? $allPhones : $allMob);
  1267.                             $arrayContactClientInfo = array(
  1268.                                 'fullName' => $contactClientInfo->getName().' '.$contactClientInfo->getLastName(),
  1269.                                 'email' => $contactClientInfo->getEmail(),
  1270.                                 'allPhones' => $allPhones,
  1271.                             );
  1272.                         } else {
  1273.                             $arrayContactClientInfo = array(
  1274.                                 'fullName' => '',
  1275.                                 'email' => '',
  1276.                                 'allPhones' => '',
  1277.                             );
  1278.                         }
  1280.                         $xlsObservations = $track->getMessage();
  1282.                         if(!empty($xlsObservations)){
  1283.                             $xlsObservations = str_replace('</p><p>',PHP_EOL,$xlsObservations);
  1284.                             $xlsObservations = str_replace('<p>','',$xlsObservations);
  1285.                             $xlsObservations = str_replace('</p>','',$xlsObservations);
  1286.                             $xlsObservations = str_replace('&nbsp;',' ',$xlsObservations);
  1287.                             $xlsObservations = str_replace('<br>',PHP_EOL,$xlsObservations);
  1288.                         }
  1290.                         $newTableData[] = array(
  1291.                             'id' => $client->getId(),
  1292.                             'name' => $client->getName(),
  1293.                             'title' => $client->getTitle(),
  1294.                             'address' => $client->getAddress(),
  1295.                             'addressNumber' => $client->getAddressNumber(),
  1296.                             'cityClient' => $cityClient,
  1297.                             'zipCode' => $client->getZipCode(),
  1298.                             'volume' => $track->getVolume(),
  1299.                             'payment' => $track->getPayment(),
  1300.                             'situation' => $track->getSituation(),
  1301.                             'competency' => $client->getCompetency(),
  1302.                             'picture' => $picture,
  1303.                             'contact' => $arrayContactClientInfo['fullName'],
  1304.                             'email' => $arrayContactClientInfo['email'],
  1305.                             'allPhones' => $arrayContactClientInfo['allPhones'],
  1306.                             'trackMessage' => $xlsObservations,
  1307.                             'trackStatus' => $track->getStatus(),
  1308.                             'trackDateAt' => $track->getDateAt(),
  1309.                         );
  1310.                     }
  1312.                     // Si no hay comercial tracking se debe dejar vacio
  1313.                     if (empty($newTableData)){
  1314.                         $newTableData[] = array(
  1315.                             'id' => $client->getId(),
  1316.                             'name' => $client->getName(),
  1317.                             'title' => $client->getTitle(),
  1318.                             'address' => $client->getAddress(),
  1319.                             'addressNumber' => $client->getAddressNumber(),
  1320.                             'cityClient' => $cityClient,
  1321.                             'zipCode' => $client->getZipCode(),
  1322.                             'volume' => '',
  1323.                             'payment' => '',
  1324.                             'situation' => '',
  1325.                             'competency' => $client->getCompetency(),
  1326.                             'picture' => $picture,
  1327.                             'contact' => '',
  1328.                             'email' => '',
  1329.                             'allPhones' => '',
  1330.                             'trackMessage' => '',
  1331.                             'trackStatus' => '',
  1332.                             'trackDateAt' => '',
  1333.                         );
  1334.                     }
  1335.                     $clientList[] = $newTableData;
  1336.                 }
  1337.                 break;
  1339.             case 'volumebltwo':
  1340.                 // Bloque de 50K a 200K
  1341.                 $allComTrack = $em->getRepository(CommercialTracking::class)->findAll();
  1342.                 $arrayComTrack = array();
  1344.                 foreach ($allComTrack as $item){
  1345.                     // 200.000; 1050.000 es numero
  1346.                     // 2.000.000; 200 000; 200,000; 90000 €; Desconocido; Null, no es un numero
  1348.                     $datoVol = str_replace('.','',$item->getVolume());      // Los .
  1349.                     $datoVol = str_replace(' ','',$datoVol);                // Los ' '
  1350.                     $datoVol = str_replace(',','',$datoVol);                // Los ' '
  1351.                     $datoVol = str_replace('€','',$datoVol);                // Los €
  1352.                     $datoVol = str_replace('$','',$datoVol);                // Los $
  1354.                     // Se busca por bloque. Daniel Guerrero
  1355.                     if (is_numeric($datoVol)){ if (($datoVol >= 50000) and ($datoVol <= 200000)) { $arrayComTrack[] = $item; } }
  1356.                 }
  1358.                 $arrayClients = array();
  1359.                 $competencyClients = array();
  1360.                 foreach ($arrayComTrack as $item){ $arrayClients[$item->getClientId()] = $item->getClientId(); }
  1361.                 foreach ($arrayClients as $item){
  1362.                     $cli = $em->getRepository(Client::class)->findOneById($item);
  1363.                     if (!empty($cli)) { $competencyClients[] = $cli; }
  1364.                 }
  1366.                 $clientList = array();
  1367.                 foreach($competencyClients as $client){
  1368.                     $picture = $client->getPicture();
  1369.                     if(!empty($picture) or $picture !== null){ $picture = $client->getPicture(); } else { $picture = 'assets/images/placeholder.jpg'; }
  1371.                     //Ordenamos desde el tracking as reciente al mas antiguo
  1372.                     $parameters = array( 'clientId' => $client->getId() );
  1373.                     $dqlc = 'SELECT ct
  1374.                              FROM CommercialBundle:CommercialTracking ct
  1375.                              WHERE ct.clientId = :clientId
  1376.                              ORDER BY ct.dateAt DESC';
  1378.                     $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  1379.                     $allClientTrackings = $queryc->getResult();
  1380.                     $newTableData = array();
  1381.                     $cityClient = $em->getRepository(Cities::class)->findOneById($client->getPopulation());
  1382.                     if (!empty($cityClient)){ $cityClient = $cityClient->getCity(); }
  1383.                     foreach ($allClientTrackings as $track) {
  1384.                         $contactClientInfo = $em->getRepository('App\Entity\ClientContact')->findOneById($track->getContactId());
  1385.                         if (!empty($contactClientInfo)){
  1386.                             // allPhones tendrá los telefonos y moviles
  1387.                             $allPhones = !empty($contactClientInfo->getPhone()) ? $contactClientInfo->getPhone() : null;
  1388.                             $allMob = !empty($contactClientInfo->getMobile()) ? $contactClientInfo->getMobile() : null;
  1389.                             $allPhones = (!empty($allPhones) and !empty($allMob)) ? $allPhones.' - '.$allMob : (!empty($allPhones) ? $allPhones : $allMob);
  1391.                             $arrayContactClientInfo = array(
  1392.                                 'fullName' => $contactClientInfo->getName().' '.$contactClientInfo->getLastName(),
  1393.                                 'email' => $contactClientInfo->getEmail(),
  1394.                                 'allPhones' => $allPhones,
  1395.                             );
  1396.                         } else {
  1397.                             $arrayContactClientInfo = array(
  1398.                                 'fullName' => '',
  1399.                                 'email' => '',
  1400.                                 'allPhones' => '',
  1401.                             );
  1402.                         }
  1404.                         $xlsObservations = $track->getMessage();
  1406.                         if(!empty($xlsObservations)){
  1407.                             $xlsObservations = str_replace('</p><p>',PHP_EOL,$xlsObservations);
  1408.                             $xlsObservations = str_replace('<p>','',$xlsObservations);
  1409.                             $xlsObservations = str_replace('</p>','',$xlsObservations);
  1410.                             $xlsObservations = str_replace('&nbsp;',' ',$xlsObservations);
  1411.                             $xlsObservations = str_replace('<br>',PHP_EOL,$xlsObservations);
  1412.                         }
  1414.                         $newTableData[] = array(
  1415.                             'id' => $client->getId(),
  1416.                             'name' => $client->getName(),
  1417.                             'title' => $client->getTitle(),
  1418.                             'address' => $client->getAddress(),
  1419.                             'addressNumber' => $client->getAddressNumber(),
  1420.                             'cityClient' => $cityClient,
  1421.                             'zipCode' => $client->getZipCode(),
  1422.                             'volume' => $track->getVolume(),
  1423.                             'payment' => $track->getPayment(),
  1424.                             'situation' => $track->getSituation(),
  1425.                             'competency' => $client->getCompetency(),
  1426.                             'picture' => $picture,
  1427.                             'contact' => $arrayContactClientInfo['fullName'],
  1428.                             'email' => $arrayContactClientInfo['email'],
  1429.                             'allPhones' => $arrayContactClientInfo['allPhones'],
  1430.                             'trackMessage' => $xlsObservations,
  1431.                             'trackStatus' => $track->getStatus(),
  1432.                             'trackDateAt' => $track->getDateAt(),
  1433.                         );
  1434.                     }
  1436.                     // Si no hay comercial tracking se debe dejar vacio
  1437.                     if (empty($newTableData)){
  1438.                         $newTableData[] = array(
  1439.                             'id' => $client->getId(),
  1440.                             'name' => $client->getName(),
  1441.                             'title' => $client->getTitle(),
  1442.                             'address' => $client->getAddress(),
  1443.                             'addressNumber' => $client->getAddressNumber(),
  1444.                             'cityClient' => $cityClient,
  1445.                             'zipCode' => $client->getZipCode(),
  1446.                             'volume' => '',
  1447.                             'payment' => '',
  1448.                             'situation' => '',
  1449.                             'competency' => $client->getCompetency(),
  1450.                             'picture' => $picture,
  1451.                             'contact' => '',
  1452.                             'email' => '',
  1453.                             'allPhones' => '',
  1454.                             'trackMessage' => '',
  1455.                             'trackStatus' => '',
  1456.                             'trackDateAt' => '',
  1457.                         );
  1458.                     }
  1459.                     $clientList[] = $newTableData;
  1460.                 }
  1461.                 break;
  1463.             case 'volumeblthr':
  1464.                 // Bloque de 200K a 500K
  1465.                 $allComTrack = $em->getRepository(CommercialTracking::class)->findAll();
  1466.                 $arrayComTrack = array();
  1468.                 foreach ($allComTrack as $item){
  1469.                     // 200.000; 1050.000 es numero
  1470.                     // 2.000.000; 200 000; 200,000; 90000 €; Desconocido; Null, no es un numero
  1472.                     $datoVol = str_replace('.','',$item->getVolume());      // Los .
  1473.                     $datoVol = str_replace(' ','',$datoVol);                // Los ' '
  1474.                     $datoVol = str_replace(',','',$datoVol);                // Los ' '
  1475.                     $datoVol = str_replace('€','',$datoVol);                // Los €
  1476.                     $datoVol = str_replace('$','',$datoVol);                // Los $
  1478.                     // Se busca por bloque. Daniel Guerrero
  1479.                     if (is_numeric($datoVol)){ if (($datoVol >= 200000) and ($datoVol <= 500000)) { $arrayComTrack[] = $item; } }
  1480.                 }
  1482.                 $arrayClients = array();
  1483.                 $competencyClients = array();
  1484.                 foreach ($arrayComTrack as $item){ $arrayClients[$item->getClientId()] = $item->getClientId(); }
  1485.                 foreach ($arrayClients as $item){
  1486.                     $cli = $em->getRepository(Client::class)->findOneById($item);
  1487.                     if (!empty($cli)) { $competencyClients[] = $cli; }
  1488.                 }
  1490.                 $clientList = array();
  1491.                 foreach($competencyClients as $client){
  1492.                     $picture = $client->getPicture();
  1493.                     if(!empty($picture) or $picture !== null){ $picture = $client->getPicture(); } else { $picture = 'assets/images/placeholder.jpg'; }
  1495.                     //Ordenamos desde el tracking as reciente al mas antiguo
  1496.                     $parameters = array( 'clientId' => $client->getId() );
  1497.                     $dqlc = 'SELECT ct
  1498.                              FROM CommercialBundle:CommercialTracking ct
  1499.                              WHERE ct.clientId = :clientId
  1500.                              ORDER BY ct.dateAt DESC';
  1502.                     $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  1503.                     $allClientTrackings = $queryc->getResult();
  1504.                     $newTableData = array();
  1505.                     $cityClient = $em->getRepository(Cities::class)->findOneById($client->getPopulation());
  1506.                     if (!empty($cityClient)){ $cityClient = $cityClient->getCity(); }
  1507.                     foreach ($allClientTrackings as $track) {
  1508.                         $contactClientInfo = $em->getRepository('App\Entity\ClientContact')->findOneById($track->getContactId());
  1509.                         if (!empty($contactClientInfo)){
  1510.                             // allPhones tendrá los telefonos y moviles
  1511.                             $allPhones = !empty($contactClientInfo->getPhone()) ? $contactClientInfo->getPhone() : null;
  1512.                             $allMob = !empty($contactClientInfo->getMobile()) ? $contactClientInfo->getMobile() : null;
  1513.                             $allPhones = (!empty($allPhones) and !empty($allMob)) ? $allPhones.' - '.$allMob : (!empty($allPhones) ? $allPhones : $allMob);
  1515.                             $arrayContactClientInfo = array(
  1516.                                 'fullName' => $contactClientInfo->getName().' '.$contactClientInfo->getLastName(),
  1517.                                 'email' => $contactClientInfo->getEmail(),
  1518.                                 'allPhones' => $allPhones,
  1519.                             );
  1520.                         } else {
  1521.                             $arrayContactClientInfo = array(
  1522.                                 'fullName' => '',
  1523.                                 'email' => '',
  1524.                                 'allPhones' => '',
  1525.                             );
  1526.                         }
  1528.                         $xlsObservations = $track->getMessage();
  1530.                         if(!empty($xlsObservations)){
  1531.                             $xlsObservations = str_replace('</p><p>',PHP_EOL,$xlsObservations);
  1532.                             $xlsObservations = str_replace('<p>','',$xlsObservations);
  1533.                             $xlsObservations = str_replace('</p>','',$xlsObservations);
  1534.                             $xlsObservations = str_replace('&nbsp;',' ',$xlsObservations);
  1535.                             $xlsObservations = str_replace('<br>',PHP_EOL,$xlsObservations);
  1536.                         }
  1538.                         $newTableData[] = array(
  1539.                             'id' => $client->getId(),
  1540.                             'name' => $client->getName(),
  1541.                             'title' => $client->getTitle(),
  1542.                             'address' => $client->getAddress(),
  1543.                             'addressNumber' => $client->getAddressNumber(),
  1544.                             'cityClient' => $cityClient,
  1545.                             'zipCode' => $client->getZipCode(),
  1546.                             'volume' => $track->getVolume(),
  1547.                             'payment' => $track->getPayment(),
  1548.                             'situation' => $track->getSituation(),
  1549.                             'competency' => $client->getCompetency(),
  1550.                             'picture' => $picture,
  1551.                             'contact' => $arrayContactClientInfo['fullName'],
  1552.                             'email' => $arrayContactClientInfo['email'],
  1553.                             'allPhones' => $arrayContactClientInfo['allPhones'],
  1554.                             'trackMessage' => $xlsObservations,
  1555.                             'trackStatus' => $track->getStatus(),
  1556.                             'trackDateAt' => $track->getDateAt(),
  1557.                         );
  1558.                     }
  1560.                     // Si no hay comercial tracking se debe dejar vacio
  1561.                     if (empty($newTableData)){
  1562.                         $newTableData[] = array(
  1563.                             'id' => $client->getId(),
  1564.                             'name' => $client->getName(),
  1565.                             'title' => $client->getTitle(),
  1566.                             'address' => $client->getAddress(),
  1567.                             'addressNumber' => $client->getAddressNumber(),
  1568.                             'cityClient' => $cityClient,
  1569.                             'zipCode' => $client->getZipCode(),
  1570.                             'volume' => '',
  1571.                             'payment' => '',
  1572.                             'situation' => '',
  1573.                             'competency' => $client->getCompetency(),
  1574.                             'picture' => $picture,
  1575.                             'contact' => '',
  1576.                             'email' => '',
  1577.                             'allPhones' => '',
  1578.                             'trackMessage' => '',
  1579.                             'trackStatus' => '',
  1580.                             'trackDateAt' => '',
  1581.                         );
  1582.                     }
  1583.                     $clientList[] = $newTableData;
  1584.                 }
  1585.                 break;
  1587.             case 'volumeblfour':
  1588.                 // Bloque de 500K en adelante
  1589.                 $allComTrack = $em->getRepository(CommercialTracking::class)->findAll();
  1590.                 $arrayComTrack = array();
  1592.                 foreach ($allComTrack as $item){
  1593.                     // 200.000; 1050.000 es numero
  1594.                     // 2.000.000; 200 000; 200,000; 90000 €; Desconocido; Null, no es un numero
  1596.                     $datoVol = str_replace('.','',$item->getVolume());      // Los .
  1597.                     $datoVol = str_replace(' ','',$datoVol);                // Los ' '
  1598.                     $datoVol = str_replace(',','',$datoVol);                // Los ' '
  1599.                     $datoVol = str_replace('€','',$datoVol);                // Los €
  1600.                     $datoVol = str_replace('$','',$datoVol);                // Los $
  1602.                     // Se busca por bloque. Daniel Guerrero
  1603.                     if (is_numeric($datoVol)){ if ($datoVol >= 500000) { $arrayComTrack[] = $item; } }
  1604.                 }
  1606.                 $arrayClients = array();
  1607.                 $competencyClients = array();
  1608.                 foreach ($arrayComTrack as $item){ $arrayClients[$item->getClientId()] = $item->getClientId(); }
  1609.                 foreach ($arrayClients as $item){
  1610.                     $cli = $em->getRepository(Client::class)->findOneById($item);
  1611.                     if (!empty($cli)) { $competencyClients[] = $cli; }
  1612.                 }
  1614.                 $clientList = array();
  1615.                 foreach($competencyClients as $client){
  1616.                     $picture = $client->getPicture();
  1617.                     if(!empty($picture) or $picture !== null){ $picture = $client->getPicture(); } else { $picture = 'assets/images/placeholder.jpg'; }
  1619.                     //Ordenamos desde el tracking as reciente al mas antiguo
  1620.                     $parameters = array( 'clientId' => $client->getId() );
  1621.                     $dqlc = 'SELECT ct
  1622.                              FROM CommercialBundle:CommercialTracking ct
  1623.                              WHERE ct.clientId = :clientId
  1624.                              ORDER BY ct.dateAt DESC';
  1626.                     $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  1627.                     $allClientTrackings = $queryc->getResult();
  1628.                     $newTableData = array();
  1629.                     $cityClient = $em->getRepository(Cities::class)->findOneById($client->getPopulation());
  1630.                     if (!empty($cityClient)){ $cityClient = $cityClient->getCity(); }
  1631.                     foreach ($allClientTrackings as $track) {
  1632.                         $contactClientInfo = $em->getRepository('App\Entity\ClientContact')->findOneById($track->getContactId());
  1633.                         if (!empty($contactClientInfo)){
  1634.                             // allPhones tendrá los telefonos y moviles
  1635.                             $allPhones = !empty($contactClientInfo->getPhone()) ? $contactClientInfo->getPhone() : null;
  1636.                             $allMob = !empty($contactClientInfo->getMobile()) ? $contactClientInfo->getMobile() : null;
  1637.                             $allPhones = (!empty($allPhones) and !empty($allMob)) ? $allPhones.' - '.$allMob : (!empty($allPhones) ? $allPhones : $allMob);
  1639.                             $arrayContactClientInfo = array(
  1640.                                 'fullName' => $contactClientInfo->getName().' '.$contactClientInfo->getLastName(),
  1641.                                 'email' => $contactClientInfo->getEmail(),
  1642.                                 'allPhones' => $allPhones,
  1643.                             );
  1644.                         } else {
  1645.                             $arrayContactClientInfo = array(
  1646.                                 'fullName' => '',
  1647.                                 'email' => '',
  1648.                                 'allPhones' => '',
  1649.                             );
  1650.                         }
  1652.                         $xlsObservations = $track->getMessage();
  1654.                         if(!empty($xlsObservations)){
  1655.                             $xlsObservations = str_replace('</p><p>',PHP_EOL,$xlsObservations);
  1656.                             $xlsObservations = str_replace('<p>','',$xlsObservations);
  1657.                             $xlsObservations = str_replace('</p>','',$xlsObservations);
  1658.                             $xlsObservations = str_replace('&nbsp;',' ',$xlsObservations);
  1659.                             $xlsObservations = str_replace('<br>',PHP_EOL,$xlsObservations);
  1660.                         }
  1662.                         $newTableData[] = array(
  1663.                             'id' => $client->getId(),
  1664.                             'name' => $client->getName(),
  1665.                             'title' => $client->getTitle(),
  1666.                             'address' => $client->getAddress(),
  1667.                             'addressNumber' => $client->getAddressNumber(),
  1668.                             'cityClient' => $cityClient,
  1669.                             'zipCode' => $client->getZipCode(),
  1670.                             'volume' => $track->getVolume(),
  1671.                             'payment' => $track->getPayment(),
  1672.                             'situation' => $track->getSituation(),
  1673.                             'competency' => $client->getCompetency(),
  1674.                             'picture' => $picture,
  1675.                             'contact' => $arrayContactClientInfo['fullName'],
  1676.                             'email' => $arrayContactClientInfo['email'],
  1677.                             'allPhones' => $arrayContactClientInfo['allPhones'],
  1678.                             'trackMessage' => $xlsObservations,
  1679.                             'trackStatus' => $track->getStatus(),
  1680.                             'trackDateAt' => $track->getDateAt(),
  1681.                         );
  1682.                     }
  1684.                     // Si no hay comercial tracking se debe dejar vacio
  1685.                     if (empty($newTableData)){
  1686.                         $newTableData[] = array(
  1687.                             'id' => $client->getId(),
  1688.                             'name' => $client->getName(),
  1689.                             'title' => $client->getTitle(),
  1690.                             'address' => $client->getAddress(),
  1691.                             'addressNumber' => $client->getAddressNumber(),
  1692.                             'cityClient' => $cityClient,
  1693.                             'zipCode' => $client->getZipCode(),
  1694.                             'volume' => '',
  1695.                             'payment' => '',
  1696.                             'situation' => '',
  1697.                             'competency' => $client->getCompetency(),
  1698.                             'picture' => $picture,
  1699.                             'contact' => '',
  1700.                             'email' => '',
  1701.                             'allPhones' => '',
  1702.                             'trackMessage' => '',
  1703.                             'trackStatus' => '',
  1704.                             'trackDateAt' => '',
  1705.                         );
  1706.                     }
  1707.                     $clientList[] = $newTableData;
  1708.                 }
  1709.                 break;
  1711.             case 'agent':
  1712.                 $dato = $_POST['agentes'];
  1714.                 $parameters = array( 'userid' => $dato );
  1715.                 $dqlc = 'SELECT ct
  1716.                          FROM CommercialBundle:CommercialTracking ct
  1717.                          WHERE ct.createdId = :userid OR ct.updatedId = :userid';
  1718.                 $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  1719.                 $arrayComTrack = $queryc->getResult();
  1720.                 $arrayClients = array();
  1721.                 $competencyClients = array();
  1722.                 foreach ($arrayComTrack as $item){ $arrayClients[$item->getClientId()] = $item->getClientId(); }
  1723.                 foreach ($arrayClients as $item){
  1724.                     $cli = $em->getRepository(Client::class)->findOneById($item);
  1725.                     if (!empty($cli)) { $competencyClients[] = $cli; }
  1726.                 }
  1728.                 $clientList = array();
  1729.                 foreach($competencyClients as $client){
  1730.                     $picture = $client->getPicture();
  1731.                     if(!empty($picture) or $picture !== null){ $picture = $client->getPicture(); } else { $picture = 'assets/images/placeholder.jpg'; }
  1733.                     //Ordenamos desde el tracking as reciente al mas antiguo
  1734.                     $parameters = array( 'clientId' => $client->getId() );
  1735.                     $dqlc = 'SELECT ct
  1736.                              FROM CommercialBundle:CommercialTracking ct
  1737.                              WHERE ct.clientId = :clientId
  1738.                              ORDER BY ct.dateAt DESC';
  1740.                     $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  1741.                     $allClientTrackings = $queryc->getResult();
  1742.                     $newTableData = array();
  1743.                     $cityClient = $em->getRepository(Cities::class)->findOneById($client->getPopulation());
  1744.                     if (!empty($cityClient)){ $cityClient = $cityClient->getCity(); }
  1745.                     foreach ($allClientTrackings as $track) {
  1746.                         $contactClientInfo = $em->getRepository('App\Entity\ClientContact')->findOneById($track->getContactId());
  1747.                         if (!empty($contactClientInfo)){
  1748.                             // allPhones tendrá los telefonos y moviles
  1749.                             $allPhones = !empty($contactClientInfo->getPhone()) ? $contactClientInfo->getPhone() : null;
  1750.                             $allMob = !empty($contactClientInfo->getMobile()) ? $contactClientInfo->getMobile() : null;
  1751.                             $allPhones = (!empty($allPhones) and !empty($allMob)) ? $allPhones.' - '.$allMob : (!empty($allPhones) ? $allPhones : $allMob);
  1753.                             $arrayContactClientInfo = array(
  1754.                                 'fullName' => $contactClientInfo->getName().' '.$contactClientInfo->getLastName(),
  1755.                                 'email' => $contactClientInfo->getEmail(),
  1756.                                 'allPhones' => $allPhones,
  1757.                             );
  1758.                         } else {
  1759.                             $arrayContactClientInfo = array(
  1760.                                 'fullName' => '',
  1761.                                 'email' => '',
  1762.                                 'allPhones' => '',
  1763.                             );
  1764.                         }
  1766.                         $xlsObservations = $track->getMessage();
  1768.                         if(!empty($xlsObservations)){
  1769.                             $xlsObservations = str_replace('</p><p>',PHP_EOL,$xlsObservations);
  1770.                             $xlsObservations = str_replace('<p>','',$xlsObservations);
  1771.                             $xlsObservations = str_replace('</p>','',$xlsObservations);
  1772.                             $xlsObservations = str_replace('&nbsp;',' ',$xlsObservations);
  1773.                             $xlsObservations = str_replace('<br>',PHP_EOL,$xlsObservations);
  1774.                         }
  1776.                         $newTableData[] = array(
  1777.                             'id' => $client->getId(),
  1778.                             'name' => $client->getName(),
  1779.                             'title' => $client->getTitle(),
  1780.                             'address' => $client->getAddress(),
  1781.                             'addressNumber' => $client->getAddressNumber(),
  1782.                             'cityClient' => $cityClient,
  1783.                             'zipCode' => $client->getZipCode(),
  1784.                             'volume' => $track->getVolume(),
  1785.                             'payment' => $track->getPayment(),
  1786.                             'situation' => $track->getSituation(),
  1787.                             'competency' => $client->getCompetency(),
  1788.                             'picture' => $picture,
  1789.                             'contact' => $arrayContactClientInfo['fullName'],
  1790.                             'email' => $arrayContactClientInfo['email'],
  1791.                             'allPhones' => $arrayContactClientInfo['allPhones'],
  1792.                             'trackMessage' => $xlsObservations,
  1793.                             'trackStatus' => $track->getStatus(),
  1794.                             'trackDateAt' => $track->getDateAt(),
  1795.                         );
  1796.                     }
  1798.                     // Si no hay comercial tracking se debe dejar vacio
  1799.                     if (empty($newTableData)){
  1800.                         $newTableData[] = array(
  1801.                             'id' => $client->getId(),
  1802.                             'name' => $client->getName(),
  1803.                             'title' => $client->getTitle(),
  1804.                             'address' => $client->getAddress(),
  1805.                             'addressNumber' => $client->getAddressNumber(),
  1806.                             'cityClient' => $cityClient,
  1807.                             'zipCode' => $client->getZipCode(),
  1808.                             'volume' => '',
  1809.                             'payment' => '',
  1810.                             'situation' => '',
  1811.                             'competency' => $client->getCompetency(),
  1812.                             'picture' => $picture,
  1813.                             'contact' => '',
  1814.                             'email' => '',
  1815.                             'allPhones' => '',
  1816.                             'trackMessage' => '',
  1817.                             'trackStatus' => '',
  1818.                             'trackDateAt' => '',
  1819.                         );
  1820.                     }
  1821.                     $clientList[] = $newTableData;
  1822.                 }
  1829.                 break;
  1831.             case 'pay':
  1832.                 $dato = $_POST['pago'];
  1834.                 $parameters = array( 'pay' => $dato );
  1835.                 $dqlc = 'SELECT ct
  1836.                          FROM CommercialBundle:CommercialTracking ct
  1837.                          WHERE ct.payment = :pay';
  1838.                 $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  1839.                 $arrayComTrack = $queryc->getResult();
  1840.                 $arrayClients = array();
  1841.                 $competencyClients = array();
  1842.                 foreach ($arrayComTrack as $item){ $arrayClients[$item->getClientId()] = $item->getClientId(); }
  1843.                 foreach ($arrayClients as $item){
  1844.                     $cli = $em->getRepository(Client::class)->findOneById($item);
  1845.                     if (!empty($cli)) { $competencyClients[] = $cli; }
  1846.                 }
  1848.                 $clientList = array();
  1849.                 foreach($competencyClients as $client){
  1850.                     $picture = $client->getPicture();
  1851.                     if(!empty($picture) or $picture !== null){ $picture = $client->getPicture(); } else { $picture = 'assets/images/placeholder.jpg'; }
  1853.                     //Ordenamos desde el tracking as reciente al mas antiguo
  1854.                     $parameters = array( 'clientId' => $client->getId() );
  1855.                     $dqlc = 'SELECT ct
  1856.                              FROM CommercialBundle:CommercialTracking ct
  1857.                              WHERE ct.clientId = :clientId
  1858.                              ORDER BY ct.dateAt DESC';
  1860.                     $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  1861.                     $allClientTrackings = $queryc->getResult();
  1862.                     $newTableData = array();
  1863.                     $cityClient = $em->getRepository(Cities::class)->findOneById($client->getPopulation());
  1864.                     if (!empty($cityClient)){ $cityClient = $cityClient->getCity(); }
  1865.                     foreach ($allClientTrackings as $track) {
  1866.                         $contactClientInfo = $em->getRepository('App\Entity\ClientContact')->findOneById($track->getContactId());
  1867.                         if (!empty($contactClientInfo)){
  1868.                             // allPhones tendrá los telefonos y moviles
  1869.                             $allPhones = !empty($contactClientInfo->getPhone()) ? $contactClientInfo->getPhone() : null;
  1870.                             $allMob = !empty($contactClientInfo->getMobile()) ? $contactClientInfo->getMobile() : null;
  1871.                             $allPhones = (!empty($allPhones) and !empty($allMob)) ? $allPhones.' - '.$allMob : (!empty($allPhones) ? $allPhones : $allMob);
  1873.                             $arrayContactClientInfo = array(
  1874.                                 'fullName' => $contactClientInfo->getName().' '.$contactClientInfo->getLastName(),
  1875.                                 'email' => $contactClientInfo->getEmail(),
  1876.                                 'allPhones' => $allPhones,
  1877.                             );
  1878.                         } else {
  1879.                             $arrayContactClientInfo = array(
  1880.                                 'fullName' => '',
  1881.                                 'email' => '',
  1882.                                 'allPhones' => '',
  1883.                             );
  1884.                         }
  1886.                         $xlsObservations = $track->getMessage();
  1888.                         if(!empty($xlsObservations)){
  1889.                             $xlsObservations = str_replace('</p><p>',PHP_EOL,$xlsObservations);
  1890.                             $xlsObservations = str_replace('<p>','',$xlsObservations);
  1891.                             $xlsObservations = str_replace('</p>','',$xlsObservations);
  1892.                             $xlsObservations = str_replace('&nbsp;',' ',$xlsObservations);
  1893.                             $xlsObservations = str_replace('<br>',PHP_EOL,$xlsObservations);
  1894.                         }
  1896.                         $newTableData[] = array(
  1897.                             'id' => $client->getId(),
  1898.                             'name' => $client->getName(),
  1899.                             'title' => $client->getTitle(),
  1900.                             'address' => $client->getAddress(),
  1901.                             'addressNumber' => $client->getAddressNumber(),
  1902.                             'cityClient' => $cityClient,
  1903.                             'zipCode' => $client->getZipCode(),
  1904.                             'volume' => $track->getVolume(),
  1905.                             'payment' => $track->getPayment(),
  1906.                             'situation' => $track->getSituation(),
  1907.                             'competency' => $client->getCompetency(),
  1908.                             'picture' => $picture,
  1909.                             'contact' => $arrayContactClientInfo['fullName'],
  1910.                             'email' => $arrayContactClientInfo['email'],
  1911.                             'allPhones' => $arrayContactClientInfo['allPhones'],
  1912.                             'trackMessage' => $xlsObservations,
  1913.                             'trackStatus' => $track->getStatus(),
  1914.                             'trackDateAt' => $track->getDateAt(),
  1915.                         );
  1916.                     }
  1918.                     // Si no hay comercial tracking se debe dejar vacio
  1919.                     if (empty($newTableData)){
  1920.                         $newTableData[] = array(
  1921.                             'id' => $client->getId(),
  1922.                             'name' => $client->getName(),
  1923.                             'title' => $client->getTitle(),
  1924.                             'address' => $client->getAddress(),
  1925.                             'addressNumber' => $client->getAddressNumber(),
  1926.                             'cityClient' => $cityClient,
  1927.                             'zipCode' => $client->getZipCode(),
  1928.                             'volume' => '',
  1929.                             'payment' => '',
  1930.                             'situation' => '',
  1931.                             'competency' => $client->getCompetency(),
  1932.                             'picture' => $picture,
  1933.                             'contact' => '',
  1934.                             'email' => '',
  1935.                             'allPhones' => '',
  1936.                             'trackMessage' => '',
  1937.                             'trackStatus' => '',
  1938.                             'trackDateAt' => '',
  1939.                         );
  1940.                     }
  1941.                     $clientList[] = $newTableData;
  1942.                 }
  1949.                 break;
  1951.             case 'situation':
  1952.                 $dato = $_POST['situacion'];
  1954.                 $parameters = array( 'situation' => $dato );
  1955.                 $dqlc = 'SELECT ct
  1956.                          FROM CommercialBundle:CommercialTracking ct
  1957.                          WHERE ct.situation = :situation';
  1958.                 $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  1959.                 $arrayComTrack = $queryc->getResult();
  1960.                 $arrayClients = array();
  1961.                 $competencyClients = array();
  1962.                 foreach ($arrayComTrack as $item){ $arrayClients[$item->getClientId()] = $item->getClientId(); }
  1963.                 foreach ($arrayClients as $item){
  1964.                     $cli = $em->getRepository(Client::class)->findOneById($item);
  1965.                     if (!empty($cli)) { $competencyClients[] = $cli; }
  1966.                 }
  1968.                 $clientList = array();
  1969.                 foreach($competencyClients as $client){
  1970.                     $picture = $client->getPicture();
  1971.                     if(!empty($picture) or $picture !== null){ $picture = $client->getPicture(); } else { $picture = 'assets/images/placeholder.jpg'; }
  1973.                     //Ordenamos desde el tracking as reciente al mas antiguo
  1974.                     $parameters = array( 'clientId' => $client->getId() );
  1975.                     $dqlc = 'SELECT ct
  1976.                              FROM CommercialBundle:CommercialTracking ct
  1977.                              WHERE ct.clientId = :clientId
  1978.                              ORDER BY ct.dateAt DESC';
  1980.                     $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  1981.                     $allClientTrackings = $queryc->getResult();
  1982.                     $newTableData = array();
  1983.                     $cityClient = $em->getRepository(Cities::class)->findOneById($client->getPopulation());
  1984.                     if (!empty($cityClient)){ $cityClient = $cityClient->getCity(); }
  1985.                     foreach ($allClientTrackings as $track) {
  1986.                         $contactClientInfo = $em->getRepository('App\Entity\ClientContact')->findOneById($track->getContactId());
  1987.                         if (!empty($contactClientInfo)){
  1988.                             // allPhones tendrá los telefonos y moviles
  1989.                             $allPhones = !empty($contactClientInfo->getPhone()) ? $contactClientInfo->getPhone() : null;
  1990.                             $allMob = !empty($contactClientInfo->getMobile()) ? $contactClientInfo->getMobile() : null;
  1991.                             $allPhones = (!empty($allPhones) and !empty($allMob)) ? $allPhones.' - '.$allMob : (!empty($allPhones) ? $allPhones : $allMob);
  1993.                             $arrayContactClientInfo = array(
  1994.                                 'fullName' => $contactClientInfo->getName().' '.$contactClientInfo->getLastName(),
  1995.                                 'email' => $contactClientInfo->getEmail(),
  1996.                                 'allPhones' => $allPhones,
  1997.                             );
  1998.                         } else {
  1999.                             $arrayContactClientInfo = array(
  2000.                                 'fullName' => '',
  2001.                                 'email' => '',
  2002.                                 'allPhones' => '',
  2003.                             );
  2004.                         }
  2006.                         $xlsObservations = $track->getMessage();
  2008.                         if(!empty($xlsObservations)){
  2009.                             $xlsObservations = str_replace('</p><p>',PHP_EOL,$xlsObservations);
  2010.                             $xlsObservations = str_replace('<p>','',$xlsObservations);
  2011.                             $xlsObservations = str_replace('</p>','',$xlsObservations);
  2012.                             $xlsObservations = str_replace('&nbsp;',' ',$xlsObservations);
  2013.                             $xlsObservations = str_replace('<br>',PHP_EOL,$xlsObservations);
  2014.                         }
  2016.                         $newTableData[] = array(
  2017.                             'id' => $client->getId(),
  2018.                             'name' => $client->getName(),
  2019.                             'title' => $client->getTitle(),
  2020.                             'address' => $client->getAddress(),
  2021.                             'addressNumber' => $client->getAddressNumber(),
  2022.                             'cityClient' => $cityClient,
  2023.                             'zipCode' => $client->getZipCode(),
  2024.                             'volume' => $track->getVolume(),
  2025.                             'payment' => $track->getPayment(),
  2026.                             'situation' => $track->getSituation(),
  2027.                             'competency' => $client->getCompetency(),
  2028.                             'picture' => $picture,
  2029.                             'contact' => $arrayContactClientInfo['fullName'],
  2030.                             'email' => $arrayContactClientInfo['email'],
  2031.                             'allPhones' => $arrayContactClientInfo['allPhones'],
  2032.                             'trackMessage' => $xlsObservations,
  2033.                             'trackStatus' => $track->getStatus(),
  2034.                             'trackDateAt' => $track->getDateAt(),
  2035.                         );
  2036.                     }
  2038.                     // Si no hay comercial tracking se debe dejar vacio
  2039.                     if (empty($newTableData)){
  2040.                         $newTableData[] = array(
  2041.                             'id' => $client->getId(),
  2042.                             'name' => $client->getName(),
  2043.                             'title' => $client->getTitle(),
  2044.                             'address' => $client->getAddress(),
  2045.                             'addressNumber' => $client->getAddressNumber(),
  2046.                             'cityClient' => $cityClient,
  2047.                             'zipCode' => $client->getZipCode(),
  2048.                             'volume' => '',
  2049.                             'payment' => '',
  2050.                             'situation' => '',
  2051.                             'competency' => $client->getCompetency(),
  2052.                             'picture' => $picture,
  2053.                             'contact' => '',
  2054.                             'email' => '',
  2055.                             'allPhones' => '',
  2056.                             'trackMessage' => '',
  2057.                             'trackStatus' => '',
  2058.                             'trackDateAt' => '',
  2059.                         );
  2060.                     }
  2061.                     $clientList[] = $newTableData;
  2062.                 }
  2069.                 break;
  2071.             case 'competency':
  2072.                 $parameters = array();
  2073.                 $dqlc = "SELECT cc
  2074.                 FROM App\Entity\Client cc
  2075.                 WHERE cc.competency LIKE '%".$dato."%' ";
  2077.                 $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  2078.                 $competencyClients = $queryc->getResult();
  2080.                 $clientList = array();
  2081.                 foreach($competencyClients as $client){
  2082.                     $picture = $client->getPicture();
  2083.                     if(!empty($picture) or $picture !== null){ $picture = $client->getPicture(); } else { $picture = 'assets/images/placeholder.jpg'; }
  2085.                     //Ordenamos desde el tracking as reciente al mas antiguo
  2086.                     $parameters = array( 'clientId' => $client->getId() );
  2087.                     $dqlc = 'SELECT ct
  2088.                              FROM CommercialBundle:CommercialTracking ct
  2089.                              WHERE ct.clientId = :clientId
  2090.                              ORDER BY ct.dateAt DESC';
  2092.                     $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  2093.                     $allClientTrackings = $queryc->getResult();
  2094.                     $newTableData = array();
  2095.                     $cityClient = $em->getRepository(Cities::class)->findOneById($client->getPopulation());
  2096.                     if (!empty($cityClient)){ $cityClient = $cityClient->getCity(); }
  2097.                     foreach ($allClientTrackings as $track) {
  2098.                         $contactClientInfo = $em->getRepository('App\Entity\ClientContact')->findOneById($track->getContactId());
  2099.                         if (!empty($contactClientInfo)){
  2100.                             // allPhones tendrá los telefonos y moviles
  2101.                             $allPhones = !empty($contactClientInfo->getPhone()) ? $contactClientInfo->getPhone() : null;
  2102.                             $allMob = !empty($contactClientInfo->getMobile()) ? $contactClientInfo->getMobile() : null;
  2103.                             $allPhones = (!empty($allPhones) and !empty($allMob)) ? $allPhones.' - '.$allMob : (!empty($allPhones) ? $allPhones : $allMob);
  2105.                             $arrayContactClientInfo = array(
  2106.                                 'fullName' => $contactClientInfo->getName().' '.$contactClientInfo->getLastName(),
  2107.                                 'email' => $contactClientInfo->getEmail(),
  2108.                                 'allPhones' => $allPhones,
  2109.                             );
  2110.                         } else {
  2111.                             $arrayContactClientInfo = array(
  2112.                                 'fullName' => '',
  2113.                                 'email' => '',
  2114.                                 'allPhones' => '',
  2115.                             );
  2116.                         }
  2118.                         $xlsObservations = $track->getMessage();
  2120.                         if(!empty($xlsObservations)){
  2121.                             $xlsObservations = str_replace('</p><p>',PHP_EOL,$xlsObservations);
  2122.                             $xlsObservations = str_replace('<p>','',$xlsObservations);
  2123.                             $xlsObservations = str_replace('</p>','',$xlsObservations);
  2124.                             $xlsObservations = str_replace('&nbsp;',' ',$xlsObservations);
  2125.                             $xlsObservations = str_replace('<br>',PHP_EOL,$xlsObservations);
  2126.                         }
  2128.                         $newTableData[] = array(
  2129.                             'id' => $client->getId(),
  2130.                             'name' => $client->getName(),
  2131.                             'title' => $client->getTitle(),
  2132.                             'address' => $client->getAddress(),
  2133.                             'addressNumber' => $client->getAddressNumber(),
  2134.                             'cityClient' => $cityClient,
  2135.                             'zipCode' => $client->getZipCode(),
  2136.                             'volume' => $track->getVolume(),
  2137.                             'payment' => $track->getPayment(),
  2138.                             'situation' => $track->getSituation(),
  2139.                             'competency' => $client->getCompetency(),
  2140.                             'picture' => $picture,
  2141.                             'contact' => $arrayContactClientInfo['fullName'],
  2142.                             'email' => $arrayContactClientInfo['email'],
  2143.                             'allPhones' => $arrayContactClientInfo['allPhones'],
  2144.                             'trackMessage' => $xlsObservations,
  2145.                             'trackStatus' => $track->getStatus(),
  2146.                             'trackDateAt' => $track->getDateAt(),
  2147.                         );
  2148.                     }
  2150.                     // Si no hay comercial tracking se debe dejar vacio
  2151.                     if (empty($newTableData)){
  2152.                         $newTableData[] = array(
  2153.                             'id' => $client->getId(),
  2154.                             'name' => $client->getName(),
  2155.                             'title' => $client->getTitle(),
  2156.                             'address' => $client->getAddress(),
  2157.                             'addressNumber' => $client->getAddressNumber(),
  2158.                             'cityClient' => $cityClient,
  2159.                             'zipCode' => $client->getZipCode(),
  2160.                             'volume' => '',
  2161.                             'payment' => '',
  2162.                             'situation' => '',
  2163.                             'competency' => $client->getCompetency(),
  2164.                             'picture' => $picture,
  2165.                             'contact' => '',
  2166.                             'email' => '',
  2167.                             'allPhones' => '',
  2168.                             'trackMessage' => '',
  2169.                             'trackStatus' => '',
  2170.                             'trackDateAt' => '',
  2171.                         );
  2172.                     }
  2173.                     $clientList[] = $newTableData;
  2174.                 }
  2176. //                $parameters = array( 'zipCode' => $dato );
  2177. //                $dqlc = 'SELECT cc
  2178. //                FROM App\Entity\Client cc
  2179. //                WHERE cc.zipCode = :zipCode ';
  2180. //
  2181. //                $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  2182. //                $zipCodeClients = $queryc->getResult();
  2183. //
  2184. //                $clientList = array();
  2185. //
  2186. //                foreach($zipCodeClients as $client){
  2187. //
  2188. //                    $picture = $client->getPicture();
  2189. //                    if(!empty($picture) or $picture !== null){
  2190. //                        $picture = $client->getPicture();
  2191. //                    }else{
  2192. //                        $picture = 'assets/images/placeholder.jpg';
  2193. //                    }
  2194. //                    $contacts = array();
  2195. //                    $contactsInfo = $em->getRepository('App\Entity\ClientContact')->findByClientId($client->getId());
  2196. //
  2197. //                    foreach ($contactsInfo as $contact){
  2198. //                        $contacts[] = array(
  2199. //                            'name' => $contact->getName().' '.$contact->getLastName(),
  2200. //                        );
  2201. //                    }
  2202. //                    $clientList[] = array(
  2203. //                        'id' => $client->getId(),
  2204. //                        'name' => $client->getName(),
  2205. //                        'title' => $client->getTitle(),
  2206. //                        'competency' => $client->getCompetency(),
  2207. //                        'picture' => $picture,
  2208. //                        'contact' => $contacts,
  2209. //                        'filter' => 'Código Postal',
  2210. //                        'data' => $dato,
  2211. //                    );
  2212. //                }
  2214.                 break;
  2216.             case 'zone':
  2217.                 $parameters = array();
  2218.                 $dqlc = "SELECT cc
  2219.                         FROM App\Entity\Cities cc
  2220.                         WHERE cc.city LIKE '%" . $dato . "%'";
  2221.                 $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  2222.                 $arrayCities = $queryc->getResult();
  2224.                 $competencyClients = array();
  2225.                 if(!empty($arrayCities)){
  2226.                     foreach ($arrayCities as $item){
  2227.                         $cli = $em->getRepository(Client::class)->findByPopulation($item->getId());
  2228.                         if (!empty($cli)){ foreach ($cli as $elem) { $competencyClients[] = $elem; } }
  2229.                     }
  2230.                 }
  2232.                 $clientList = array();
  2233.                 foreach($competencyClients as $client){
  2234.                     $picture = $client->getPicture();
  2235.                     if(!empty($picture) or $picture !== null){ $picture = $client->getPicture(); } else { $picture = 'assets/images/placeholder.jpg'; }
  2237.                     //Ordenamos desde el tracking as reciente al mas antiguo
  2238.                     $parameters = array( 'clientId' => $client->getId() );
  2239.                     $dqlc = 'SELECT ct
  2240.                              FROM CommercialBundle:CommercialTracking ct
  2241.                              WHERE ct.clientId = :clientId
  2242.                              ORDER BY ct.dateAt DESC';
  2244.                     $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  2245.                     $allClientTrackings = $queryc->getResult();
  2246.                     $newTableData = array();
  2247.                     $cityClient = $em->getRepository(Cities::class)->findOneById($client->getPopulation());
  2248.                     if (!empty($cityClient)){ $cityClient = $cityClient->getCity(); }
  2249.                     foreach ($allClientTrackings as $track) {
  2250.                         $contactClientInfo = $em->getRepository('App\Entity\ClientContact')->findOneById($track->getContactId());
  2251.                         if (!empty($contactClientInfo)){
  2252.                             // allPhones tendrá los telefonos y moviles
  2253.                             $allPhones = !empty($contactClientInfo->getPhone()) ? $contactClientInfo->getPhone() : null;
  2254.                             $allMob = !empty($contactClientInfo->getMobile()) ? $contactClientInfo->getMobile() : null;
  2255.                             $allPhones = (!empty($allPhones) and !empty($allMob)) ? $allPhones.' - '.$allMob : (!empty($allPhones) ? $allPhones : $allMob);
  2257.                             $arrayContactClientInfo = array(
  2258.                                 'fullName' => $contactClientInfo->getName().' '.$contactClientInfo->getLastName(),
  2259.                                 'email' => $contactClientInfo->getEmail(),
  2260.                                 'allPhones' => $allPhones,
  2261.                             );
  2262.                         } else {
  2263.                             $arrayContactClientInfo = array(
  2264.                                 'fullName' => '',
  2265.                                 'email' => '',
  2266.                                 'allPhones' => '',
  2267.                             );
  2268.                         }
  2270.                         $xlsObservations = $track->getMessage();
  2272.                         if(!empty($xlsObservations)){
  2273.                             $xlsObservations = str_replace('</p><p>',PHP_EOL,$xlsObservations);
  2274.                             $xlsObservations = str_replace('<p>','',$xlsObservations);
  2275.                             $xlsObservations = str_replace('</p>','',$xlsObservations);
  2276.                             $xlsObservations = str_replace('&nbsp;',' ',$xlsObservations);
  2277.                             $xlsObservations = str_replace('<br>',PHP_EOL,$xlsObservations);
  2278.                         }
  2280.                         $newTableData[] = array(
  2281.                             'id' => $client->getId(),
  2282.                             'name' => $client->getName(),
  2283.                             'title' => $client->getTitle(),
  2284.                             'address' => $client->getAddress(),
  2285.                             'addressNumber' => $client->getAddressNumber(),
  2286.                             'cityClient' => $cityClient,
  2287.                             'zipCode' => $client->getZipCode(),
  2288.                             'volume' => $track->getVolume(),
  2289.                             'payment' => $track->getPayment(),
  2290.                             'situation' => $track->getSituation(),
  2291.                             'competency' => $client->getCompetency(),
  2292.                             'picture' => $picture,
  2293.                             'contact' => $arrayContactClientInfo['fullName'],
  2294.                             'email' => $arrayContactClientInfo['email'],
  2295.                             'allPhones' => $arrayContactClientInfo['allPhones'],
  2296.                             'trackMessage' => $xlsObservations,
  2297.                             'trackStatus' => $track->getStatus(),
  2298.                             'trackDateAt' => $track->getDateAt(),
  2299.                         );
  2300.                     }
  2302.                     // Si no hay comercial tracking se debe dejar vacio
  2303.                     if (empty($newTableData)){
  2304.                         $newTableData[] = array(
  2305.                             'id' => $client->getId(),
  2306.                             'name' => $client->getName(),
  2307.                             'title' => $client->getTitle(),
  2308.                             'address' => $client->getAddress(),
  2309.                             'addressNumber' => $client->getAddressNumber(),
  2310.                             'cityClient' => $cityClient,
  2311.                             'zipCode' => $client->getZipCode(),
  2312.                             'volume' => '',
  2313.                             'payment' => '',
  2314.                             'situation' => '',
  2315.                             'competency' => $client->getCompetency(),
  2316.                             'picture' => $picture,
  2317.                             'contact' => '',
  2318.                             'email' => '',
  2319.                             'allPhones' => '',
  2320.                             'trackMessage' => '',
  2321.                             'trackStatus' => '',
  2322.                             'trackDateAt' => '',
  2323.                         );
  2324.                     }
  2325.                     $clientList[] = $newTableData;
  2326.                 }
  2327.                 break;
  2329.             case 'datetracking':
  2330.                 $dato = new \DateTime($_POST['fechaseguimiento']);
  2331.                 $startDate = $dato->setTime(0, 0, 0);
  2332.                 $endDate = clone $startDate;
  2333.                 $endDate = $endDate->modify('+1 day');
  2335.                 $parameters = array( 'startDate' => $startDate, 'endDate' => $endDate );
  2336.                 $dqlc = 'SELECT ct
  2337.                          FROM CommercialBundle:CommercialTracking ct
  2338.                          WHERE ct.createdAt >= :startDate AND ct.createdAt < :endDate';
  2339.                 $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  2340.                 $arrayComTrack = $queryc->getResult();
  2342.                 $arrayClients = array();
  2343.                 $competencyClients = array();
  2344.                 foreach ($arrayComTrack as $item){ $arrayClients[$item->getClientId()] = $item->getClientId(); }
  2345.                 foreach ($arrayClients as $item){
  2346.                     $cli = $em->getRepository(Client::class)->findOneById($item);
  2347.                     if (!empty($cli)) { $competencyClients[] = $cli; }
  2348.                 }
  2350.                 $clientList = array();
  2351.                 foreach($competencyClients as $client){
  2352.                     $picture = $client->getPicture();
  2353.                     if(!empty($picture) or $picture !== null){ $picture = $client->getPicture(); } else { $picture = 'assets/images/placeholder.jpg'; }
  2355.                     //Ordenamos desde el tracking as reciente al mas antiguo
  2356.                     $parameters = array( 'clientId' => $client->getId() );
  2357.                     $dqlc = 'SELECT ct
  2358.                              FROM CommercialBundle:CommercialTracking ct
  2359.                              WHERE ct.clientId = :clientId
  2360.                              ORDER BY ct.dateAt DESC';
  2362.                     $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  2363.                     $allClientTrackings = $queryc->getResult();
  2364.                     $newTableData = array();
  2365.                     $cityClient = $em->getRepository(Cities::class)->findOneById($client->getPopulation());
  2366.                     if (!empty($cityClient)){ $cityClient = $cityClient->getCity(); }
  2367.                     foreach ($allClientTrackings as $track) {
  2368.                         $contactClientInfo = $em->getRepository('App\Entity\ClientContact')->findOneById($track->getContactId());
  2369.                         if (!empty($contactClientInfo)){
  2370.                             // allPhones tendrá los telefonos y moviles
  2371.                             $allPhones = !empty($contactClientInfo->getPhone()) ? $contactClientInfo->getPhone() : null;
  2372.                             $allMob = !empty($contactClientInfo->getMobile()) ? $contactClientInfo->getMobile() : null;
  2373.                             $allPhones = (!empty($allPhones) and !empty($allMob)) ? $allPhones.' - '.$allMob : (!empty($allPhones) ? $allPhones : $allMob);
  2375.                             $arrayContactClientInfo = array(
  2376.                                 'fullName' => $contactClientInfo->getName().' '.$contactClientInfo->getLastName(),
  2377.                                 'email' => $contactClientInfo->getEmail(),
  2378.                                 'allPhones' => $allPhones,
  2379.                             );
  2380.                         } else {
  2381.                             $arrayContactClientInfo = array(
  2382.                                 'fullName' => '',
  2383.                                 'email' => '',
  2384.                                 'allPhones' => '',
  2385.                             );
  2386.                         }
  2388.                         $xlsObservations = $track->getMessage();
  2390.                         if(!empty($xlsObservations)){
  2391.                             $xlsObservations = str_replace('</p><p>',PHP_EOL,$xlsObservations);
  2392.                             $xlsObservations = str_replace('<p>','',$xlsObservations);
  2393.                             $xlsObservations = str_replace('</p>','',$xlsObservations);
  2394.                             $xlsObservations = str_replace('&nbsp;',' ',$xlsObservations);
  2395.                             $xlsObservations = str_replace('<br>',PHP_EOL,$xlsObservations);
  2396.                         }
  2398.                         $newTableData[] = array(
  2399.                             'id' => $client->getId(),
  2400.                             'name' => $client->getName(),
  2401.                             'title' => $client->getTitle(),
  2402.                             'address' => $client->getAddress(),
  2403.                             'addressNumber' => $client->getAddressNumber(),
  2404.                             'cityClient' => $cityClient,
  2405.                             'zipCode' => $client->getZipCode(),
  2406.                             'volume' => $track->getVolume(),
  2407.                             'payment' => $track->getPayment(),
  2408.                             'situation' => $track->getSituation(),
  2409.                             'competency' => $client->getCompetency(),
  2410.                             'picture' => $picture,
  2411.                             'contact' => $arrayContactClientInfo['fullName'],
  2412.                             'email' => $arrayContactClientInfo['email'],
  2413.                             'allPhones' => $arrayContactClientInfo['allPhones'],
  2414.                             'trackMessage' => $xlsObservations,
  2415.                             'trackStatus' => $track->getStatus(),
  2416.                             'trackDateAt' => $track->getDateAt(),
  2417.                         );
  2418.                     }
  2420.                     // Si no hay comercial tracking se debe dejar vacio
  2421.                     if (empty($newTableData)){
  2422.                         $newTableData[] = array(
  2423.                             'id' => $client->getId(),
  2424.                             'name' => $client->getName(),
  2425.                             'title' => $client->getTitle(),
  2426.                             'address' => $client->getAddress(),
  2427.                             'addressNumber' => $client->getAddressNumber(),
  2428.                             'cityClient' => $cityClient,
  2429.                             'zipCode' => $client->getZipCode(),
  2430.                             'volume' => '',
  2431.                             'payment' => '',
  2432.                             'situation' => '',
  2433.                             'competency' => $client->getCompetency(),
  2434.                             'picture' => $picture,
  2435.                             'contact' => '',
  2436.                             'email' => '',
  2437.                             'allPhones' => '',
  2438.                             'trackMessage' => '',
  2439.                             'trackStatus' => '',
  2440.                             'trackDateAt' => '',
  2441.                         );
  2442.                     }
  2443.                     $clientList[] = $newTableData;
  2444.                 }
  2451.                 break;
  2455.             default: break;
  2457.         };
  2459.         $clientCount = count($clientList);
  2461.         $dql = 'SELECT cl
  2462.                 FROM App\MDS\CommercialBundle\Entity\CommercialTracking cl
  2463.                 GROUP BY cl.createdId';
  2465.         $query = $em->createQuery($dql);
  2466.         $comtrack = $query->getResult();
  2468.         $agents = array();
  2469.         foreach ($comtrack as $item) {
  2470.             $userCreator = $em->getRepository(User::class)->findOneById($item->getCreatedId());
  2471.             if (!empty($userCreator)){ $agents[$userCreator->getName() . ' '. $userCreator->getLastName()] = $userCreator; }
  2472.         }
  2473.         ksort($agents);
  2475.         return $this->render('client/clients-find.html.twig',
  2477.             array(
  2478.                 'clients' => $clientList,
  2479.                 'clientsCount' => $clientCount,
  2480.                 'background' => $background,
  2481.                 'searchCriteria' => $standar,
  2482.                 'dato' => $dato,
  2483.                 'agents' => $agents,
  2484.             )
  2485.         );
  2486.     }
  2488.     /**
  2489.      * @Route("/xlsclientsearchcompetencies/", name="client_search_competencies_xls_empty")
  2490.      * Una busqueda con dato empty da 404, se crea esta ruta para solucionarlo
  2491.      */
  2492.     public function XlsEmptyClientSearchCompetenciesAction(Request $request){
  2493.         return $this->redirectToRoute('client_search_competencies_xls', array( 'dato' => 'zzz666zzz' ));
  2494.     }
  2496.     /**
  2497.      * @Route("/xlsclientsearchcompetencies/{dato}", name="client_search_competencies_xls")
  2498.      */
  2499.     public function XlsClientSearchCompetenciesAction($dato, Request $request){
  2501.         $em = $this->getDoctrine()->getManager();
  2502.         $parameters = array();
  2504.         $dqlc = "SELECT cc
  2505.                 FROM App\Entity\Client cc
  2506.                 WHERE cc.competency LIKE '%".$dato."%' ";
  2508.         if ($dato == 'zzz666zzz'){ $dqlc = 'SELECT cc FROM App\Entity\Client cc'; }
  2510.         $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  2511.         $competencyClients = $queryc->getResult();
  2513.         $clientList = array();
  2514.         foreach($competencyClients as $client){
  2516.             $picture = $client->getPicture();
  2517.             if(!empty($picture) or $picture !== null){
  2518.                 $picture = $client->getPicture();
  2519.             }else{
  2520.                 $picture = 'assets/images/placeholder.jpg';
  2521.             }
  2523.             //Ordenamos desde el tracking as reciente al mas antiguo
  2524.             $parameters = array( 'clientId' => $client->getId() );
  2525.             $dqlc = 'SELECT ct
  2526.                      FROM CommercialBundle:CommercialTracking ct
  2527.                      WHERE ct.clientId = :clientId
  2528.                      ORDER BY ct.dateAt DESC';
  2530.             $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  2531.             $allClientTrackings = $queryc->getResult();
  2533.             // Por indicaciones de Victor Muñoz solo mostraremos el comercial tracking mas reciente cuando la busqueda es sobre todos los clientes asi se evita error por desbordamiento de memoria
  2534.             if ($dato == 'zzz666zzz') { if (!empty($allClientTrackings)) { $allClientTrackings = array('0' => reset($allClientTrackings)); } }
  2536.             $newTableData = array();
  2537.             $cityClient = $em->getRepository(Cities::class)->findOneById($client->getPopulation());
  2538.             if (!empty($cityClient)){ $cityClient = $cityClient->getCity(); }
  2540.             foreach ($allClientTrackings as $track) {
  2542.                 $contactClientInfo = $em->getRepository('App\Entity\ClientContact')->findOneById($track->getContactId());
  2544.                 if (!empty($contactClientInfo)){
  2545.                     // allPhones tendrá los telefonos y moviles
  2546.                     $allPhones = !empty($contactClientInfo->getPhone()) ? $contactClientInfo->getPhone() : null;
  2547.                     $allMob = !empty($contactClientInfo->getMobile()) ? $contactClientInfo->getMobile() : null;
  2548.                     $allPhones = (!empty($allPhones) and !empty($allMob)) ? $allPhones.' - '.$allMob : (!empty($allPhones) ? $allPhones : $allMob);
  2550.                     $arrayContactClientInfo = array(
  2551.                         'fullName' => $contactClientInfo->getName().' '.$contactClientInfo->getLastName(),
  2552.                         'email' => $contactClientInfo->getEmail(),
  2553.                         'allPhones' => $allPhones,
  2554.                     );
  2555.                 } else {
  2556.                     $arrayContactClientInfo = array(
  2557.                         'fullName' => '',
  2558.                         'email' => '',
  2559.                         'allPhones' => '',
  2560.                     );
  2561.                 }
  2563.                 $xlsObservations = $track->getMessage();
  2565.                 if(!empty($xlsObservations)){
  2566.                     $xlsObservations = str_replace('</p><p>',PHP_EOL.' ',$xlsObservations);
  2567.                     $xlsObservations = str_replace('<p>','',$xlsObservations);
  2568.                     $xlsObservations = str_replace('</p>',' ',$xlsObservations);
  2569.                     $xlsObservations = str_replace('&nbsp;',' ',$xlsObservations);
  2570.                     $xlsObservations = str_replace('<br>',PHP_EOL.' ',$xlsObservations);
  2571.                 }
  2573.                 $newTableData[] = array(
  2574.                     'id' => $client->getId(),
  2575.                     'name' => $client->getName(),
  2576.                     'title' => $client->getTitle(),
  2577.                     'address' => $client->getAddress(),
  2578.                     'addressNumber' => $client->getAddressNumber(),
  2579.                     'cityClient' => $cityClient,
  2580.                     'zipCode' => $client->getZipCode(),
  2581.                     'volume' => $track->getVolume(),
  2582.                     'payment' => $track->getPayment(),
  2583.                     'situation' => $track->getSituation(),
  2584.                     'competency' => $client->getCompetency(),
  2585.                     'picture' => $picture,
  2586.                     'contact' => $arrayContactClientInfo['fullName'],
  2587.                     'email' => $arrayContactClientInfo['email'],
  2588.                     'allPhones' => $arrayContactClientInfo['allPhones'],
  2589.                     'trackMessage' => $xlsObservations,
  2590.                     'trackStatus' => $track->getStatus(),
  2591.                     'trackDateAt' => $track->getDateAt(),
  2592.                 );
  2593.             }
  2595.             // Si no hay comercial tracking se debe dejar vacio
  2596.             if (empty($newTableData)){
  2597.                 $newTableData[] = array(
  2598.                     'id' => $client->getId(),
  2599.                     'name' => $client->getName(),
  2600.                     'title' => $client->getTitle(),
  2601.                     'address' => $client->getAddress(),
  2602.                     'addressNumber' => $client->getAddressNumber(),
  2603.                     'cityClient' => $cityClient,
  2604.                     'zipCode' => $client->getZipCode(),
  2605.                     'volume' => '',
  2606.                     'payment' => '',
  2607.                     'situation' => '',
  2608.                     'competency' => $client->getCompetency(),
  2609.                     'picture' => $picture,
  2610.                     'contact' => '',
  2611.                     'email' => '',
  2612.                     'allPhones' => '',
  2613.                     'trackMessage' => '',
  2614.                     'trackStatus' => '',
  2615.                     'trackDateAt' => '',
  2616.                 );
  2617.             }
  2619.             $clientList[] = $newTableData;
  2620.         }
  2622.         if ($dato == 'zzz666zzz'){ $dato = ''; }
  2624.         // Solicita el servicio de excel
  2625.         $phpExcelObject = new Spreadsheet();
  2627.         $phpExcelObject->getProperties()->setCreator("InOut Travel")
  2628.             ->setLastModifiedBy("InOut Travel")
  2629.             ->setTitle("InOut Travel")
  2630.             ->setSubject("InOut Travel")
  2631.             ->setDescription("InOut Travel, generado usando clases de PHP")
  2632.             ->setKeywords("office 2005 openxml php")
  2633.             ->setCategory("Competencias");
  2634.         $fill = new Fill();
  2635.         $numberFormat = new NumberFormat();
  2636.         $alignment = new Alignment();
  2637.         $estilo = array(
  2638.             'alignment' => array(
  2639.                 'horizontal' => $alignment::HORIZONTAL_RIGHT,
  2640.             )
  2641.         );
  2643.         $i = 8;
  2644.         $indiceInicial = 9;
  2645.         $indiceFinal = 9;
  2647.         $activesheet = $phpExcelObject->getActiveSheet();
  2648.         $drawingobject = new Drawing();
  2649.         $drawingobject->setName('Image name');
  2650.         $drawingobject->setDescription('Image description');
  2652.         $drawingobject->setPath(getcwd().'/assets/images/logo/logo2022.png');
  2653.         $drawingobject->setHeight(60);
  2654.         $drawingobject->setOffsetY(20);
  2655.         $drawingobject->setCoordinates('B1');
  2656.         $drawingobject->setWorksheet($activesheet);
  2658.         $phpExcelObject
  2659.             ->getActiveSheet()
  2660.             ->getStyle('B6:AH6')
  2661.             ->getFill()
  2662.             ->setFillType($fill::FILL_SOLID)
  2663.             ->getStartColor()
  2664.             ->setRGB('FFA420');
  2666.         $phpExcelObject->setActiveSheetIndex(0)
  2667.             ->setCellValue('C6','Competencias con "'.$dato.'"')
  2668.         ;
  2670.         $phpExcelObject
  2671.             ->getActiveSheet()
  2672.             ->getStyle('B'.$i.':AH'.$i)
  2673.             ->getFill()
  2674.             ->setFillType($fill::FILL_SOLID)
  2675.             ->getStartColor()
  2676.             ->setRGB('9c9c9c');
  2678.         $phpExcelObject->setActiveSheetIndex(0)
  2679.             ->setCellValue('B'.$i, 'Fecha')//
  2680.             ->setCellValue('D'.$i, 'Empresa')
  2681.             ->setCellValue('F'.$i, 'Contacto')
  2682.             ->setCellValue('H'.$i, 'Correo Electrónico')//
  2683.             ->setCellValue('K'.$i, 'Teléfono')
  2684.             ->setCellValue('M'.$i, 'Dirección')
  2685.             ->setCellValue('P'.$i, 'Número')
  2686.             ->setCellValue('Q'.$i, 'Zona')
  2687.             ->setCellValue('S'.$i, 'Código Postal')
  2688.             ->setCellValue('T'.$i, 'Agencia')//
  2689.             ->setCellValue('V'.$i, 'Volumen')
  2690.             ->setCellValue('W'.$i, 'Pago')
  2691.             ->setCellValue('Y'.$i, 'Situación')
  2692. //            ->setCellValue('X'.$i, 'Prioridad')
  2693.             ->setCellValue('AA'.$i, 'Observaciones')
  2694.         ;
  2696.     foreach ($clientList as $elem) {
  2697.         foreach ($elem as $item) {
  2699.             $xlsDate = empty($item['trackDateAt']) ? '' : $item['trackDateAt']->format('d/m/Y');
  2701.             $phpExcelObject->setActiveSheetIndex(0)
  2702.                 ->setCellValue('B' . ($i + 1), $xlsDate)
  2703.                 ->setCellValue('D' . ($i + 1), $item['name'])
  2704.                 ->setCellValue('F' . ($i + 1), $item['contact'])
  2705.                 ->setCellValue('H' . ($i + 1), $item['email'])
  2706.                 ->setCellValue('K' . ($i + 1), $item['allPhones'])
  2707.                 ->setCellValue('M' . ($i + 1), $item['address'])
  2708.                 ->setCellValue('P' . ($i + 1), $item['addressNumber'])
  2709.                 ->setCellValue('Q' . ($i + 1), $item['cityClient'])
  2710.                 ->setCellValue('S' . ($i + 1), $item['zipCode'])
  2711.                 ->setCellValue('T' . ($i + 1), $item['competency'])
  2712.                 ->setCellValue('V' . ($i + 1), $item['volume'])
  2713.                 ->setCellValue('W' . ($i + 1), $item['payment'])
  2714.                 ->setCellValue('Y' . ($i + 1), $item['situation'])
  2715. //                ->setCellValue('W'.($i+1), $item['priority'])
  2716.                 ->setCellValue('AA' . ($i + 1), $item['trackMessage']);
  2718.             $i++;
  2719.             $indiceFinal++;
  2720.         }
  2721.     }
  2723.         $indiceFinal--;
  2724.         $indicesResta = $indiceFinal - $indiceInicial + 1;
  2727.         $phpExcelObject->getActiveSheet()->setTitle('Competencias');
  2728.         // Define el indice de página al número 1, para abrir esa página al abrir el archivo
  2729.         $phpExcelObject->setActiveSheetIndex(0);
  2731.         // Crea el writer
  2732.         $writer = new Xlsx($phpExcelObject);
  2733.         // Envia la respuesta del controlador
  2734.         $response = new StreamedResponse(
  2735.             function () use ($writer) {
  2736.                 $writer->save('php://output');
  2737.             }
  2738.         );
  2739.         // Agrega los headers requeridos
  2740.         $dispositionHeader = $response->headers->makeDisposition(
  2741.             ResponseHeaderBag::DISPOSITION_ATTACHMENT,
  2742.             'InOut_Competencias'.'.xlsx'
  2743.         );
  2745.         $response->headers->set('Content-Type', 'application/vnd.ms-excel; charset=utf-8');
  2746.         $response->headers->set('Pragma', 'public');
  2747.         $response->headers->set('Cache-Control', 'maxage=1');
  2748.         $response->headers->set('Content-Disposition', $dispositionHeader);
  2750.         return $response;
  2751.     }
  2753.     /**
  2754.      * @Route("/clientprofile/{id}", name="client_profile_id")
  2755.      */
  2756.     public function ClientProfileAction(Request $request, $id)
  2757.     {
  2759.         $em = $this->getDoctrine()->getManager();
  2760.         $clientInfo = $em->getRepository(Client::class)->findOneById($id);
  2761.         $clientContact = $em->getRepository('App\Entity\ClientContact')->findByClientId($id);
  2762.         $placeholder = 'assets/images/placeholder.jpg';
  2763.         d($clientInfo);
  2764.         return $this->render('client/clients-profile.html.twig',
  2766.             array(
  2767.                 'client' => $clientInfo,
  2768.                 'contacts' => $clientContact,
  2769.                 'placeholder' => $placeholder,
  2770.             )
  2771.         );
  2773.     }
  2775.     /* Lista Mis Client*/
  2776.     /**
  2777.      * @Route("/client/myclients", name="client_list_MyClients")
  2778.      */
  2779.     public function MyClienteListAction(Request $request)
  2780.     {
  2781.         $em = $this->getDoctrine()->getManager();
  2783.         /* Obtengo usuario logueado */
  2784.         $user_logueado = $this->get('security.token_storage')->getToken()->getUser();
  2785.         $user_id = $user_logueado->getId();
  2787.         $parametersA = array(
  2788.             'assignedAgent' => $user_id
  2789.         );
  2790.         /* ALL LO QUE HAY  */
  2791.         $dql = 'SELECT cl, c.country, p.region, r.city, cc.id, pr.name as province
  2792.                 FROM App\Entity\Client cl
  2793.                 INNER JOIN App:Country c WITH c.id = cl.country
  2794.                 INNER JOIN App:Regions p WITH p.id = cl.region
  2795.                 INNER JOIN App:Provinces pr WITH pr.id = cl.province
  2796.                 INNER JOIN App:Cities r WITH r.id = cl.population
  2797.                 INNER JOIN App\Entity\ClientContact cc WITH  cc.clientId = cl.id
  2798.                 WHERE cc.assignedAgent = :assignedAgent ';
  2799. //                ORDER BY p.dateProposedAt DESC';
  2801.         $query = $em->createQuery($dql)->setParameters($parametersA);;
  2802.         $clients = $query->getResult();
  2804.         // Ve todos los clientes, si es un administrador o un usuario especial
  2805.         if (($user_logueado->getRole() == "ROLE_ADMIN") or ($user_id == 133)){
  2806.             $parametersA = array();
  2808.             $dql = 'SELECT cl, c.country, p.region, r.city, cc.id, pr.name as province
  2809.                 FROM App\Entity\Client cl
  2810.                 INNER JOIN App:Country c WITH c.id = cl.country
  2811.                 INNER JOIN App:Regions p WITH p.id = cl.region
  2812.                 INNER JOIN App:Provinces pr WITH pr.id = cl.province
  2813.                 INNER JOIN App:Cities r WITH r.id = cl.population
  2814.                 INNER JOIN App\Entity\ClientContact cc WITH  cc.clientId = cl.id
  2815.                 WHERE cc.assignedAgent IS NOT NULL ';
  2817.             $query = $em->createQuery($dql)->setParameters($parametersA);;
  2818.             $clients = $query->getResult();
  2819.         }
  2821.         $data_clients = array();
  2823.         foreach($clients as $client){
  2825.             $group = $em->getRepository(Group::class)->findOneById($client[0]->getGroupId());
  2827.             $parameters = array(
  2828.                 'clientId' => $client[0]->getId()
  2829.             );
  2830.             $dqlc = 'SELECT cc,  u.name, u.lastname
  2831.                 FROM App\Entity\ClientContact cc
  2832.                 INNER JOIN App\Entity\User u WITH u.id = cc.assignedAgent
  2833.                 WHERE cc.clientId = :clientId ';
  2834.             $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  2835.             $contacts = $queryc->getResult();
  2837.             $data_clients[] = array(
  2838.                 'client' => $client,
  2839.                 'group' => $group,
  2840.                 'contacts' => $contacts,
  2841.             );
  2842.         }
  2844.         return $this->render('client/clients-general-list.html.twig', array( 'datosclientes' => $data_clients, )
  2845.         );
  2847.     }
  2848.     /* Lista Yes Client*/
  2849.     /**
  2850.      * @Route("/client/yesclient", name="client_list_YesClient")
  2851.      */
  2852.     public function YesClienteListAction(Request $request)
  2853.     {
  2854. //        $data = $this->primtListClientAction($request);
  2856.         $em = $this->getDoctrine()->getManager();
  2858.         $parametersA = array(
  2859.             'action' => 'Client'
  2860.         );
  2861.         /* ALL LO QUE HAY  */
  2862.         $dql = 'SELECT cl,  c.country, p.region, r.city, pr.name as province
  2863.                 FROM App\Entity\Client cl
  2864.                 INNER JOIN App:Country c WITH c.id = cl.country
  2865.                 INNER JOIN App:Regions p WITH p.id = cl.region
  2866.                 INNER JOIN App:Provinces pr WITH pr.id = cl.province
  2867.                 INNER JOIN App:Cities r WITH r.id = cl.population
  2868.                 WHERE cl.action = :action ';
  2869. //                ORDER BY p.dateProposedAt DESC';
  2871.         $query = $em->createQuery($dql)->setParameters($parametersA);;
  2872.         $clients = $query->getResult();
  2873.         $data_clients= array();
  2874.         foreach($clients as $client){
  2876.             $group = $em->getRepository(Group::class)->findOneById($client[0]->getGroupId());
  2878.             $parameters = array(
  2879.                 'clientId' => $client[0]->getId()
  2880.             );
  2881.             $dqlc = 'SELECT cc,  u.name, u.lastname
  2882.                 FROM App\Entity\ClientContact cc
  2883.                 INNER JOIN App\Entity\User u WITH u.id = cc.assignedAgent
  2884.                 WHERE cc.clientId = :clientId ';
  2885.             $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  2886.             $contacts = $queryc->getResult();
  2888.             $data_clients[] = array(
  2889.                 'client' => $client,
  2890.                 'group' => $group,
  2891.                 'contacts' => $contacts,
  2892.             );
  2894.         }
  2896.         return $this->render('client/clients-general-list.html.twig',
  2897.             array(
  2898.                 'datosclientes' => $data_clients,
  2899.             )
  2900.         );
  2902.     }
  2903.     /* Lista Not Client*/
  2904.     /**
  2905.      * @Route("/client/notclient", name="client_list_NoClient")
  2906.      */
  2907.     public function NotClienteListAction(Request $request)
  2908.     {
  2909. //        $data = $this->primtListClientAction($request);
  2911.         $em = $this->getDoctrine()->getManager();
  2913.         $parametersA = array(
  2914.             'action' => 'Not Client'
  2915.         );
  2916.         /* ALL LO QUE HAY  */
  2917.         $dql = 'SELECT cl,  c.country, re.region, p.name, r.city
  2918.                 FROM App\Entity\Client cl
  2919.                 INNER JOIN App:Country c WITH c.id = cl.country
  2920.                 INNER JOIN App:Regions re WITH re.id = cl.region
  2921.                 INNER JOIN App:Provinces p WITH p.id = cl.province
  2922.                 INNER JOIN App:Cities r WITH r.id = cl.population
  2923.                 WHERE cl.action = :action ';
  2924. //                ORDER BY p.dateProposedAt DESC';
  2926.         $query = $em->createQuery($dql)->setParameters($parametersA);;
  2927.         $clients = $query->getResult();
  2928.         $data_clients= array();     //Gustavo Ayala: agregué esta línea para incializar el array ya que si no hay clientes, no entraba en el for each y daba error return $this->render('client/clients-general-list.html.twig', array('datosclientes' => $data_clients,) por variable no definida
  2929.         foreach($clients as $client){
  2931.             $group = $em->getRepository(Group::class)->findOneById($client[0]->getGroupId());
  2933.             $parameters = array(
  2934.                 'clientId' => $client[0]->getId()
  2935.             );
  2936.             $dqlc = 'SELECT cc,  u.name, u.lastname
  2937.                 FROM App\Entity\ClientContact cc
  2938.                 INNER JOIN App\Entity\User u WITH u.id = cc.assignedAgent
  2939.                 WHERE cc.clientId = :clientId ';
  2940.             $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  2941.             $contacts = $queryc->getResult();
  2943.             $data_clients[] = array(
  2944.                 'client' => $client,
  2945.                 'group' => $group,
  2946.                 'contacts' => $contacts,
  2947.             );
  2950.         }
  2952.         return $this->render('client/clients-general-list.html.twig',
  2953.             array(
  2954.                 'datosclientes' => $data_clients,
  2955.             )
  2956.         );
  2958.     }
  2960.     /**
  2961.      * @Route("/client/map", name="client_map")
  2962.      */
  2963.     public function ClienteMapAction(Request $request)
  2964.     {
  2965. //        $data = $this->primtListClientAction($request);
  2967.         $em = $this->getDoctrine()->getManager();
  2968.         $clients = $em->getRepository(Client::class)->findAll();
  2970.         foreach($clients as $client){
  2971.             $contact = $em->getRepository('App\Entity\ClientContact')->findByClientId($client->getId());
  2972.             $group = $em->getRepository(Group::class)->findOneById($client->getGroupId());
  2974.             $data[] = array(
  2975.                 'client' => $client,
  2976.                 'group' => $group,
  2977.                 'contact' => $contact,
  2978.             );
  2979.         }
  2981.         return $this->render('client/clients-general-hierarchy.html.twig',
  2982.             array(
  2983.                 'datosclientes' => $data,
  2984.             )
  2985.         );
  2987.     }
  2989.     public function primtListClientAction(Request $request)
  2990.     {
  2991.         $em = $this->getDoctrine()->getManager();
  2992.         $clients = $em->getRepository(Client::class)->findAll();
  2994.         $data_clients = array();
  2995.         $datos_agent = array();
  2997.         foreach($clients as $client){
  2999.             $idclient = $client->getId();
  3000.             $idcountry = $client->getCountry();
  3001.             $contacts = $em->getRepository('App\Entity\ClientContact')->findByClientId($idclient);
  3003.             /*
  3004.              * Si tiene contactos el cliente:
  3005.              *
  3006.              * */
  3007.             if (!empty($contacts)){
  3009.                 foreach($contacts as $contact){
  3011.                     $agentid = $contact->getAssignedAgent();
  3013.                     $datos_contacto[$idclient][] = array(
  3014.                         'cid'             => $contact->getId(),
  3015.                         'cposition'       => $contact->getPosition(),
  3016.                         'cdepartment'     => $contact->getDepartment(),
  3017.                         'cname'           => $contact->getName(),
  3018.                         'clastname'       => $contact->getLastName(),
  3019.                         'cemail'          => $contact->getEmail(),
  3020.                         'cphone'          => $contact->getPhone(),
  3021.                         'cmobile'         => $contact->getMobile(),
  3022.                         'cbirthday'       => $contact->getBirthday(),
  3023.                         'cagentlastname'  => $contact->getAssignedAgent(),
  3024.                     );
  3025.                 }
  3027.                 $user = $em->getRepository(User::class)->findOneById($agentid);
  3028.                 $datos_agent = array(
  3029.                     'aid'        => $user->getId(),
  3030.                     'aname'      => $user->getName(),
  3031.                     'alastname'  => $user->getLastname(),
  3032.                 );
  3035.             } else {
  3037.                 $datos_contacto[$idclient][] = array(
  3038.                     'cid'             => '0',
  3039.                     'cposition'       => $this->translator->trans('Empty'),
  3040.                     'cdepartment'     => '',
  3041.                     'cname'           => $this->translator->trans('Client Non-contact'),
  3042.                     'clastname'       => '',
  3043.                     'cemail'          => '',
  3044.                     'cphone'          => '',
  3045.                     'cmobile'         => '',
  3046.                     'cbirthday'       => '',
  3047.                     'cagentlastname'  => '',
  3048.                 );
  3049.             }
  3051.             if ($client->getGroupId() != 0){
  3052.                 $grupo = $em->getRepository(Group::class)->findOneById($client->getGroupId());
  3053.                 $datos_grupo = array(
  3054.                     'gid'    => $grupo->getId(),
  3055.                     'gtitle' => $grupo->getTitle(),
  3056.                     'gdesc'  => $grupo->getDescription(),
  3057.                 );
  3058.             }else{
  3059.                 $datos_grupo = array(
  3060.                     'gid'    => '',
  3061.                     'gtitle' => '',
  3062.                     'gdesc'  => '',
  3063.                 );
  3064.             }
  3068.             $country= $em->getRepository(Country::class)->findOneById($idcountry);
  3069.             $datos_country= array(
  3070.                 'pid'        => $country->getId(),
  3071.                 'iso'        => $country->getCode(),
  3072.                 'pname'      => $country->getCountry(),
  3073.             );
  3074.             $region= $em->getRepository(Regions::class)->findOneById($client->getProvince());
  3075.             $datos_region= $region->getRegion();
  3077.             $city= $em->getRepository(Cities::class)->findOneById($client->getPopulation());
  3078.             $datos_city= $city->getCity();
  3080.             $data_clients[] = array(
  3081.                 'id'           => $client->getId(),
  3082.                 'picture'      => $client->getPicture(),
  3083.                 'name'         => $client->getName(),
  3084.                 'title'        => $client->getTitle(),
  3085.                 'address'      => $client->getAddress(),
  3086.                 'addressNumber'      => $client->getAddressNumber(),
  3087.                 'googlemap'    => str_replace(" ", "+", $client->getAddress()),
  3088.                 'zipcode'      => $client->getZipCode(),
  3089.                 'population'   => $datos_city,
  3090.                 'province'     => $datos_region,
  3091.                 'telephone'    => $client->getTelephone(),
  3092.                 'fax'          => $client->getFax(),
  3093.                 'email'        => $client->getEmail(),
  3094.                 'codegds'      => $client->getGds(),
  3095.                 'iddocument'   => $client->getIdDocument(),
  3096.                 'observation'  => $client->getObservation(),
  3097.                 'competency'   => $client->getCompetency(),
  3099.                 'contactos'  => $datos_contacto[$idclient],
  3100.                 'grupo'  => $datos_grupo,
  3101.                 'agent'  => $datos_agent,
  3102.                 'country'  => $datos_country,
  3103.             );
  3104.         }
  3106.         return $data_clients;
  3108.         //d($data_clients);
  3110. //        return $this->render('client/list-clientgroup-list.html.twig',
  3111. //            array(
  3112. //                'datosclientes' => $data_clients,
  3113. //            )
  3114. //        );
  3116.     }
  3118.     /**
  3119.      * @Route("/client/searchForNameOrTitle/",  name="client_searchForNameOrTitle")
  3120.      * @param Request $request
  3121.      * @return JsonResponse
  3122.      */
  3123.     public function searchForNameOrTitleAction(Request $request) {
  3124.         $term = $request->query->get('q');
  3125.         $em = $this->getDoctrine()->getManager();
  3126.         $clients = [
  3127.             "items" => array()
  3128.         ]
  3129.         ;
  3130.         $helper = new ClientHelper($em);
  3131.         $clientsCollection = $helper->searchForNameOrTitle($term);
  3132.         /**
  3133.          * @var Client  $client
  3134.          */
  3135.         foreach($clientsCollection as $client) {
  3136. //            $baseURL = $request->getScheme() . '://' . $request->getHttpHost() . $request->getBasePath() . "/";
  3137. //            $picture = (is_null($client->getPicture()))
  3138. //                        ? ""
  3139. //                        : $baseURL . $client->getPicture()
  3140. //            ;
  3141.             $items = [
  3142.                     "id" => $client->getId(),
  3143.                     "full_name" => $client->getName(),
  3144.                     "description" => $client->getTitle(),
  3145. //                    "img" => $picture,
  3147.             ]
  3148.             ;
  3149.             $clients["items"][] = $items;
  3150.         }
  3151.         $response = new JsonResponse($clients, 200 );
  3152.         return $response;
  3153.     }
  3155.     /**
  3156.      * @Route("/clientlistsimple/{ind}", defaults={"ind" = 0}, name="client_list_simple")
  3157.      */
  3158.     public function ClientListSimpleAction($ind, Request $request)
  3159.     {
  3160.         $em = $this->getDoctrine()->getManager();
  3162.         /* ALL LO QUE HAY  */
  3163.         $dql = 'SELECT cl,  c.country, p.name, re.region, r.city
  3164.                 FROM App\Entity\Client cl
  3165.                 INNER JOIN App:Country c WITH c.id = cl.country
  3166.                 INNER JOIN App:Regions re WITH re.id = cl.region
  3167.                 INNER JOIN App:Provinces p WITH p.id = cl.province
  3168.                 INNER JOIN App:Cities r WITH r.id = cl.population
  3169.                 ORDER BY cl.name ASC ';
  3171.         $query = $em->createQuery($dql);
  3172.         $clientsBigArray = $query->getResult();
  3174.         $ind = $ind * 250;
  3175.         $clients = array_slice($clientsBigArray, $ind, 250);
  3177.         // Generando el arreglo de la botonera para los saltos de pagina
  3178.         $indStart = 0;
  3179.         $indEndArray = sizeof($clientsBigArray) - 1;
  3180.         $arrayBoton = array();
  3182.         for($indStart = 0; $indStart <= $indEndArray; $indStart = $indStart + 250){
  3184.             if($indStart + 250 > $indEndArray){
  3185.                 //Ya tienes el ultimo elemento
  3186.                 $indEnd = $indEndArray;
  3187.             } else {
  3188.                 $indEnd = $indStart + 249;
  3189.             }
  3191.             //Buscamos las letras y compañias de inicio y fin
  3193.             $companyInicio = $clientsBigArray[$indStart][0]->getName();
  3194.             $letraInicio = substr($companyInicio, 0,1);
  3195.             $companyFin = $clientsBigArray[$indEnd][0]->getName();
  3196.             $letraFin = substr($companyFin, 0,1);
  3198.             $elem = array(
  3199.                 'indInicio' => $indStart,
  3200.                 'indFin' => $indEnd,
  3201.                 'letraInicio' => $letraInicio,
  3202.                 'companyInicio' => $companyInicio,
  3203.                 'letraFin' => $letraFin,
  3204.                 'companyFin' => $companyFin,
  3205.             );
  3206.             $arrayBoton[] = $elem;
  3207.         }
  3209.         $data_clients = array();
  3211.         foreach($clients as $client){
  3212.             $group = $em->getRepository(Group::class)->findOneById($client[0]->getGroupId());
  3213.             $parameters = array(
  3214.                 'clientId' => $client[0]->getId()
  3215.             );
  3216.             $dqlc = 'SELECT cc,  u.name, u.lastname
  3217.                 FROM App\Entity\ClientContact cc
  3218.                 INNER JOIN App\Entity\User u WITH u.id = cc.assignedAgent
  3219.                 WHERE cc.clientId = :clientId ';
  3220.             $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  3221.             $contacts = $queryc->getResult();
  3223.             $data_clients[] = array(
  3224.                 'client' => $client,
  3225.                 'group' => $group,
  3226.                 'contacts' => $contacts,
  3227.             );
  3228.         }
  3229.         return $this->render('client/clients-general-list-simple.html.twig',
  3231.             array(
  3232.                 'datosclientes' => $data_clients,
  3233.                 'arrayBoton' => $arrayBoton,
  3234.             )
  3235.         );
  3236.     }
  3239.     /**
  3240.      * @Route("/clientlistsimpleletters/{ind}", name="client_list_simple_letters")
  3241.      */
  3242.     public function clientListSimpleLettersAction($ind, Request $request)
  3243.     {
  3244.         $em = $this->getDoctrine()->getManager();
  3246.         $data_clients = array();
  3248.         if (true){
  3249.             // Creacion de la Botonera
  3250.             $arrayBoton = array();
  3252.             $elem = array('letra' => '0'); $arrayBoton['0'] = $elem;
  3253.             $elem = array('letra' => 'A'); $arrayBoton['A'] = $elem;
  3254.             $elem = array('letra' => 'B'); $arrayBoton['B'] = $elem;
  3255.             $elem = array('letra' => 'C'); $arrayBoton['C'] = $elem;
  3256.             $elem = array('letra' => 'D'); $arrayBoton['D'] = $elem;
  3257.             $elem = array('letra' => 'E'); $arrayBoton['E'] = $elem;
  3258.             $elem = array('letra' => 'F'); $arrayBoton['F'] = $elem;
  3259.             $elem = array('letra' => 'G'); $arrayBoton['G'] = $elem;
  3260.             $elem = array('letra' => 'H'); $arrayBoton['H'] = $elem;
  3261.             $elem = array('letra' => 'I'); $arrayBoton['I'] = $elem;
  3262.             $elem = array('letra' => 'J'); $arrayBoton['J'] = $elem;
  3263.             $elem = array('letra' => 'K'); $arrayBoton['K'] = $elem;
  3264.             $elem = array('letra' => 'L'); $arrayBoton['L'] = $elem;
  3265.             $elem = array('letra' => 'M'); $arrayBoton['M'] = $elem;
  3266.             $elem = array('letra' => 'N'); $arrayBoton['N'] = $elem;
  3267.             $elem = array('letra' => 'Ñ'); $arrayBoton['Ñ'] = $elem;
  3268.             $elem = array('letra' => 'O'); $arrayBoton['O'] = $elem;
  3269.             $elem = array('letra' => 'P'); $arrayBoton['P'] = $elem;
  3270.             $elem = array('letra' => 'Q'); $arrayBoton['Q'] = $elem;
  3271.             $elem = array('letra' => 'R'); $arrayBoton['R'] = $elem;
  3272.             $elem = array('letra' => 'S'); $arrayBoton['S'] = $elem;
  3273.             $elem = array('letra' => 'T'); $arrayBoton['T'] = $elem;
  3274.             $elem = array('letra' => 'U'); $arrayBoton['U'] = $elem;
  3275.             $elem = array('letra' => 'V'); $arrayBoton['V'] = $elem;
  3276.             $elem = array('letra' => 'W'); $arrayBoton['W'] = $elem;
  3277.             $elem = array('letra' => 'X'); $arrayBoton['X'] = $elem;
  3278.             $elem = array('letra' => 'Y'); $arrayBoton['Y'] = $elem;
  3279.             $elem = array('letra' => 'Z'); $arrayBoton['Z'] = $elem;
  3280.         }
  3282.         if ($ind == '0'){
  3283.             // Caso base, viene vacio se mostraran los que no empiezan por letra
  3285.             $arrayClientsZero = $em->getRepository(Client::class)->findAll();
  3286.             $arrayClients = array();
  3287.             // Creamos un arreglo con los indices
  3288.             foreach ($arrayClientsZero as $item){
  3289.                 $arrayClients[$item->getId()] = $item;
  3290.             }
  3292.             //Eliminamos del arreglo los elelmentos que se deban listar en A,B,C,...,Y,Z
  3293.             if (true){
  3294.                 $indice = 'a';
  3295.                 $parameters = array(
  3296.                     'namelist' => $indice.'%'
  3297.                 );
  3299.                 $dql = 'SELECT i
  3300.                 FROM App\Entity\Client i
  3301.                 WHERE i.name LIKE :namelist
  3302.                 ORDER BY i.name ASC';
  3303.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3305.                 $clients = $query->getResult();
  3307.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3308.                 foreach ($clients as $item){
  3309.                     if (array_key_exists($item->getId(),$arrayClients)){
  3310.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3311.                         unset($arrayClients[$item->getId()]);
  3312.                     }
  3313.                 }
  3314.             }
  3316.             if (true){
  3317.                 $indice = 'b';
  3318.                 $parameters = array(
  3319.                     'namelist' => $indice.'%'
  3320.                 );
  3322.                 $dql = 'SELECT i
  3323.                 FROM App\Entity\Client i
  3324.                 WHERE i.name LIKE :namelist
  3325.                 ORDER BY i.name ASC';
  3326.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3328.                 $clients = $query->getResult();
  3330.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3331.                 foreach ($clients as $item){
  3332.                     if (array_key_exists($item->getId(),$arrayClients)){
  3333.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3334.                         unset($arrayClients[$item->getId()]);
  3335.                     }
  3336.                 }
  3337.             }
  3339.             if (true){
  3340.                 $indice = 'c';
  3341.                 $parameters = array(
  3342.                     'namelist' => $indice.'%'
  3343.                 );
  3345.                 $dql = 'SELECT i
  3346.                 FROM App\Entity\Client i
  3347.                 WHERE i.name LIKE :namelist
  3348.                 ORDER BY i.name ASC';
  3349.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3351.                 $clients = $query->getResult();
  3353.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3354.                 foreach ($clients as $item){
  3355.                     if (array_key_exists($item->getId(),$arrayClients)){
  3356.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3357.                         unset($arrayClients[$item->getId()]);
  3358.                     }
  3359.                 }
  3360.             }
  3362.             if (true){
  3363.                 $indice = 'd';
  3364.                 $parameters = array(
  3365.                     'namelist' => $indice.'%'
  3366.                 );
  3368.                 $dql = 'SELECT i
  3369.                 FROM App\Entity\Client i
  3370.                 WHERE i.name LIKE :namelist
  3371.                 ORDER BY i.name ASC';
  3372.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3374.                 $clients = $query->getResult();
  3376.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3377.                 foreach ($clients as $item){
  3378.                     if (array_key_exists($item->getId(),$arrayClients)){
  3379.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3380.                         unset($arrayClients[$item->getId()]);
  3381.                     }
  3382.                 }
  3383.             }
  3385.             if (true){
  3386.                 $indice = 'e';
  3387.                 $parameters = array(
  3388.                     'namelist' => $indice.'%'
  3389.                 );
  3391.                 $dql = 'SELECT i
  3392.                 FROM App\Entity\Client i
  3393.                 WHERE i.name LIKE :namelist
  3394.                 ORDER BY i.name ASC';
  3395.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3397.                 $clients = $query->getResult();
  3399.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3400.                 foreach ($clients as $item){
  3401.                     if (array_key_exists($item->getId(),$arrayClients)){
  3402.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3403.                         unset($arrayClients[$item->getId()]);
  3404.                     }
  3405.                 }
  3406.             }
  3408.             if (true){
  3409.                 $indice = 'f';
  3410.                 $parameters = array(
  3411.                     'namelist' => $indice.'%'
  3412.                 );
  3414.                 $dql = 'SELECT i
  3415.                 FROM App\Entity\Client i
  3416.                 WHERE i.name LIKE :namelist
  3417.                 ORDER BY i.name ASC';
  3418.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3420.                 $clients = $query->getResult();
  3422.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3423.                 foreach ($clients as $item){
  3424.                     if (array_key_exists($item->getId(),$arrayClients)){
  3425.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3426.                         unset($arrayClients[$item->getId()]);
  3427.                     }
  3428.                 }
  3429.             }
  3431.             if (true){
  3432.                 $indice = 'g';
  3433.                 $parameters = array(
  3434.                     'namelist' => $indice.'%'
  3435.                 );
  3437.                 $dql = 'SELECT i
  3438.                 FROM App\Entity\Client i
  3439.                 WHERE i.name LIKE :namelist
  3440.                 ORDER BY i.name ASC';
  3441.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3443.                 $clients = $query->getResult();
  3445.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3446.                 foreach ($clients as $item){
  3447.                     if (array_key_exists($item->getId(),$arrayClients)){
  3448.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3449.                         unset($arrayClients[$item->getId()]);
  3450.                     }
  3451.                 }
  3452.             }
  3454.             if (true){
  3455.                 $indice = 'h';
  3456.                 $parameters = array(
  3457.                     'namelist' => $indice.'%'
  3458.                 );
  3460.                 $dql = 'SELECT i
  3461.                 FROM App\Entity\Client i
  3462.                 WHERE i.name LIKE :namelist
  3463.                 ORDER BY i.name ASC';
  3464.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3466.                 $clients = $query->getResult();
  3468.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3469.                 foreach ($clients as $item){
  3470.                     if (array_key_exists($item->getId(),$arrayClients)){
  3471.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3472.                         unset($arrayClients[$item->getId()]);
  3473.                     }
  3474.                 }
  3475.             }
  3477.             if (true){
  3478.                 $indice = 'i';
  3479.                 $parameters = array(
  3480.                     'namelist' => $indice.'%'
  3481.                 );
  3483.                 $dql = 'SELECT i
  3484.                 FROM App\Entity\Client i
  3485.                 WHERE i.name LIKE :namelist
  3486.                 ORDER BY i.name ASC';
  3487.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3489.                 $clients = $query->getResult();
  3491.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3492.                 foreach ($clients as $item){
  3493.                     if (array_key_exists($item->getId(),$arrayClients)){
  3494.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3495.                         unset($arrayClients[$item->getId()]);
  3496.                     }
  3497.                 }
  3498.             }
  3500.             if (true){
  3501.                 $indice = 'j';
  3502.                 $parameters = array(
  3503.                     'namelist' => $indice.'%'
  3504.                 );
  3506.                 $dql = 'SELECT i
  3507.                 FROM App\Entity\Client i
  3508.                 WHERE i.name LIKE :namelist
  3509.                 ORDER BY i.name ASC';
  3510.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3512.                 $clients = $query->getResult();
  3514.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3515.                 foreach ($clients as $item){
  3516.                     if (array_key_exists($item->getId(),$arrayClients)){
  3517.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3518.                         unset($arrayClients[$item->getId()]);
  3519.                     }
  3520.                 }
  3521.             }
  3523.             if (true){
  3524.                 $indice = 'k';
  3525.                 $parameters = array(
  3526.                     'namelist' => $indice.'%'
  3527.                 );
  3529.                 $dql = 'SELECT i
  3530.                 FROM App\Entity\Client i
  3531.                 WHERE i.name LIKE :namelist
  3532.                 ORDER BY i.name ASC';
  3533.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3535.                 $clients = $query->getResult();
  3537.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3538.                 foreach ($clients as $item){
  3539.                     if (array_key_exists($item->getId(),$arrayClients)){
  3540.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3541.                         unset($arrayClients[$item->getId()]);
  3542.                     }
  3543.                 }
  3544.             }
  3546.             if (true){
  3547.                 $indice = 'l';
  3548.                 $parameters = array(
  3549.                     'namelist' => $indice.'%'
  3550.                 );
  3552.                 $dql = 'SELECT i
  3553.                 FROM App\Entity\Client i
  3554.                 WHERE i.name LIKE :namelist
  3555.                 ORDER BY i.name ASC';
  3556.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3558.                 $clients = $query->getResult();
  3560.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3561.                 foreach ($clients as $item){
  3562.                     if (array_key_exists($item->getId(),$arrayClients)){
  3563.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3564.                         unset($arrayClients[$item->getId()]);
  3565.                     }
  3566.                 }
  3567.             }
  3569.             if (true){
  3570.                 $indice = 'm';
  3571.                 $parameters = array(
  3572.                     'namelist' => $indice.'%'
  3573.                 );
  3575.                 $dql = 'SELECT i
  3576.                 FROM App\Entity\Client i
  3577.                 WHERE i.name LIKE :namelist
  3578.                 ORDER BY i.name ASC';
  3579.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3581.                 $clients = $query->getResult();
  3583.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3584.                 foreach ($clients as $item){
  3585.                     if (array_key_exists($item->getId(),$arrayClients)){
  3586.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3587.                         unset($arrayClients[$item->getId()]);
  3588.                     }
  3589.                 }
  3590.             }
  3592.             if (true){
  3593.                 $indice = 'n';
  3594.                 $parameters = array(
  3595.                     'namelist' => $indice.'%'
  3596.                 );
  3598.                 $dql = 'SELECT i
  3599.                 FROM App\Entity\Client i
  3600.                 WHERE i.name LIKE :namelist
  3601.                 ORDER BY i.name ASC';
  3602.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3604.                 $clients = $query->getResult();
  3606.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3607.                 foreach ($clients as $item){
  3608.                     if (array_key_exists($item->getId(),$arrayClients)){
  3609.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3610.                         unset($arrayClients[$item->getId()]);
  3611.                     }
  3612.                 }
  3613.             }
  3615.             if (true){
  3616.                 $indice = 'ñ';
  3617.                 $parameters = array(
  3618.                     'namelist' => $indice.'%'
  3619.                 );
  3621.                 $dql = 'SELECT i
  3622.                 FROM App\Entity\Client i
  3623.                 WHERE i.name LIKE :namelist
  3624.                 ORDER BY i.name ASC';
  3625.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3627.                 $clients = $query->getResult();
  3629.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3630.                 foreach ($clients as $item){
  3631.                     if (array_key_exists($item->getId(),$arrayClients)){
  3632.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3633.                         unset($arrayClients[$item->getId()]);
  3634.                     }
  3635.                 }
  3636.             }
  3638.             if (true){
  3639.                 $indice = 'o';
  3640.                 $parameters = array(
  3641.                     'namelist' => $indice.'%'
  3642.                 );
  3644.                 $dql = 'SELECT i
  3645.                 FROM App\Entity\Client i
  3646.                 WHERE i.name LIKE :namelist
  3647.                 ORDER BY i.name ASC';
  3648.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3650.                 $clients = $query->getResult();
  3652.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3653.                 foreach ($clients as $item){
  3654.                     if (array_key_exists($item->getId(),$arrayClients)){
  3655.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3656.                         unset($arrayClients[$item->getId()]);
  3657.                     }
  3658.                 }
  3659.             }
  3661.             if (true){
  3662.                 $indice = 'p';
  3663.                 $parameters = array(
  3664.                     'namelist' => $indice.'%'
  3665.                 );
  3667.                 $dql = 'SELECT i
  3668.                 FROM App\Entity\Client i
  3669.                 WHERE i.name LIKE :namelist
  3670.                 ORDER BY i.name ASC';
  3671.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3673.                 $clients = $query->getResult();
  3675.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3676.                 foreach ($clients as $item){
  3677.                     if (array_key_exists($item->getId(),$arrayClients)){
  3678.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3679.                         unset($arrayClients[$item->getId()]);
  3680.                     }
  3681.                 }
  3682.             }
  3684.             if (true){
  3685.                 $indice = 'q';
  3686.                 $parameters = array(
  3687.                     'namelist' => $indice.'%'
  3688.                 );
  3690.                 $dql = 'SELECT i
  3691.                 FROM App\Entity\Client i
  3692.                 WHERE i.name LIKE :namelist
  3693.                 ORDER BY i.name ASC';
  3694.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3696.                 $clients = $query->getResult();
  3698.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3699.                 foreach ($clients as $item){
  3700.                     if (array_key_exists($item->getId(),$arrayClients)){
  3701.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3702.                         unset($arrayClients[$item->getId()]);
  3703.                     }
  3704.                 }
  3705.             }
  3707.             if (true){
  3708.                 $indice = 'r';
  3709.                 $parameters = array(
  3710.                     'namelist' => $indice.'%'
  3711.                 );
  3713.                 $dql = 'SELECT i
  3714.                 FROM App\Entity\Client i
  3715.                 WHERE i.name LIKE :namelist
  3716.                 ORDER BY i.name ASC';
  3717.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3719.                 $clients = $query->getResult();
  3721.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3722.                 foreach ($clients as $item){
  3723.                     if (array_key_exists($item->getId(),$arrayClients)){
  3724.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3725.                         unset($arrayClients[$item->getId()]);
  3726.                     }
  3727.                 }
  3728.             }
  3730.             if (true){
  3731.                 $indice = 's';
  3732.                 $parameters = array(
  3733.                     'namelist' => $indice.'%'
  3734.                 );
  3736.                 $dql = 'SELECT i
  3737.                 FROM App\Entity\Client i
  3738.                 WHERE i.name LIKE :namelist
  3739.                 ORDER BY i.name ASC';
  3740.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3742.                 $clients = $query->getResult();
  3744.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3745.                 foreach ($clients as $item){
  3746.                     if (array_key_exists($item->getId(),$arrayClients)){
  3747.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3748.                         unset($arrayClients[$item->getId()]);
  3749.                     }
  3750.                 }
  3751.             }
  3753.             if (true){
  3754.                 $indice = 't';
  3755.                 $parameters = array(
  3756.                     'namelist' => $indice.'%'
  3757.                 );
  3759.                 $dql = 'SELECT i
  3760.                 FROM App\Entity\Client i
  3761.                 WHERE i.name LIKE :namelist
  3762.                 ORDER BY i.name ASC';
  3763.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3765.                 $clients = $query->getResult();
  3767.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3768.                 foreach ($clients as $item){
  3769.                     if (array_key_exists($item->getId(),$arrayClients)){
  3770.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3771.                         unset($arrayClients[$item->getId()]);
  3772.                     }
  3773.                 }
  3774.             }
  3776.             if (true){
  3777.                 $indice = 'u';
  3778.                 $parameters = array(
  3779.                     'namelist' => $indice.'%'
  3780.                 );
  3782.                 $dql = 'SELECT i
  3783.                 FROM App\Entity\Client i
  3784.                 WHERE i.name LIKE :namelist
  3785.                 ORDER BY i.name ASC';
  3786.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3788.                 $clients = $query->getResult();
  3790.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3791.                 foreach ($clients as $item){
  3792.                     if (array_key_exists($item->getId(),$arrayClients)){
  3793.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3794.                         unset($arrayClients[$item->getId()]);
  3795.                     }
  3796.                 }
  3797.             }
  3799.             if (true){
  3800.                 $indice = 'v';
  3801.                 $parameters = array(
  3802.                     'namelist' => $indice.'%'
  3803.                 );
  3805.                 $dql = 'SELECT i
  3806.                 FROM App\Entity\Client i
  3807.                 WHERE i.name LIKE :namelist
  3808.                 ORDER BY i.name ASC';
  3809.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3811.                 $clients = $query->getResult();
  3813.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3814.                 foreach ($clients as $item){
  3815.                     if (array_key_exists($item->getId(),$arrayClients)){
  3816.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3817.                         unset($arrayClients[$item->getId()]);
  3818.                     }
  3819.                 }
  3820.             }
  3822.             if (true){
  3823.                 $indice = 'w';
  3824.                 $parameters = array(
  3825.                     'namelist' => $indice.'%'
  3826.                 );
  3828.                 $dql = 'SELECT i
  3829.                 FROM App\Entity\Client i
  3830.                 WHERE i.name LIKE :namelist
  3831.                 ORDER BY i.name ASC';
  3832.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3834.                 $clients = $query->getResult();
  3836.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3837.                 foreach ($clients as $item){
  3838.                     if (array_key_exists($item->getId(),$arrayClients)){
  3839.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3840.                         unset($arrayClients[$item->getId()]);
  3841.                     }
  3842.                 }
  3843.             }
  3845.             if (true){
  3846.                 $indice = 'x';
  3847.                 $parameters = array(
  3848.                     'namelist' => $indice.'%'
  3849.                 );
  3851.                 $dql = 'SELECT i
  3852.                 FROM App\Entity\Client i
  3853.                 WHERE i.name LIKE :namelist
  3854.                 ORDER BY i.name ASC';
  3855.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3857.                 $clients = $query->getResult();
  3859.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3860.                 foreach ($clients as $item){
  3861.                     if (array_key_exists($item->getId(),$arrayClients)){
  3862.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3863.                         unset($arrayClients[$item->getId()]);
  3864.                     }
  3865.                 }
  3866.             }
  3868.             if (true){
  3869.                 $indice = 'y';
  3870.                 $parameters = array(
  3871.                     'namelist' => $indice.'%'
  3872.                 );
  3874.                 $dql = 'SELECT i
  3875.                 FROM App\Entity\Client i
  3876.                 WHERE i.name LIKE :namelist
  3877.                 ORDER BY i.name ASC';
  3878.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3880.                 $clients = $query->getResult();
  3882.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3883.                 foreach ($clients as $item){
  3884.                     if (array_key_exists($item->getId(),$arrayClients)){
  3885.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3886.                         unset($arrayClients[$item->getId()]);
  3887.                     }
  3888.                 }
  3889.             }
  3891.             if (true){
  3892.                 $indice = 'z';
  3893.                 $parameters = array(
  3894.                     'namelist' => $indice.'%'
  3895.                 );
  3897.                 $dql = 'SELECT i
  3898.                 FROM App\Entity\Client i
  3899.                 WHERE i.name LIKE :namelist
  3900.                 ORDER BY i.name ASC';
  3901.                 $query = $em->createQuery($dql)->setParameters($parameters);
  3903.                 $clients = $query->getResult();
  3905.                 // Buscamos los elementos de esta letra y los removemos del arreglo
  3906.                 foreach ($clients as $item){
  3907.                     if (array_key_exists($item->getId(),$arrayClients)){
  3908.                         // Si el elemento se encuentra en el arreglo lo quitamos por que pertenece al listado de la letra
  3909.                         unset($arrayClients[$item->getId()]);
  3910.                     }
  3911.                 }
  3912.             }
  3914.             $clients = $arrayClients;
  3916.         } else {
  3918.             $parameters = array(
  3919.                 'namelist' => $ind.'%'
  3920.             );
  3922.             $dql = 'SELECT i
  3923.                 FROM App\Entity\Client i
  3924.                 WHERE i.name LIKE :namelist
  3925.                 ORDER BY i.name ASC';
  3926.             $query = $em->createQuery($dql)->setParameters($parameters);
  3928.             $clients = $query->getResult();
  3929.         }
  3931.         foreach($clients as $client){
  3932.             $group = $em->getRepository(Group::class)->findOneById($client->getGroupId());
  3933.             $parameters = array(
  3934.                 'clientId' => $client->getId()
  3935.             );
  3936.             $dqlc = 'SELECT cc,  u.name, u.lastname
  3937.                 FROM App\Entity\ClientContact cc
  3938.                 INNER JOIN App\Entity\User u WITH u.id = cc.assignedAgent
  3939.                 WHERE cc.clientId = :clientId ';
  3940.             $queryc = $em->createQuery($dqlc)->setParameters($parameters);
  3941.             $contacts = $queryc->getResult();
  3943.             $data_clients[] = array(
  3944.                 'client' => array( 'id' =>$client->getId(), 'name' => $client->getName(), 'title' => $client->getTitle()),
  3945.                 'group' => $group,
  3946.                 'contacts' => $contacts,
  3947.             );
  3948.         }
  3951.         return $this->render('client/clients-general-list-letters.html.twig',
  3953.             array(
  3954.                 'datosclientes' => $data_clients,
  3955.                 'arrayBoton' => $arrayBoton,
  3956.             )
  3957.         );
  3958.     }
  3962. }