src/Controller/InvoiceController.php line 42

Open in your IDE?
  1. <?php
  3. namespace App\Controller;
  5. use Symfony\Component\HttpFoundation\Response;
  6. use Symfony\Component\HttpFoundation\JsonResponse;
  7. use Symfony\Component\HttpFoundation\RedirectResponse;
  8. use Symfony\Component\HttpFoundation\Request;
  9. use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
  10. use Symfony\Component\HttpFoundation\Session\SessionInterface;
  11. use \MongoDB\Client as MongoDBClient;
  12. use App\Entity\ImporterCSV;
  13. use App\Entity\ManebiCSV;
  15. use App\Entity\InvoiceSearch\InvoiceSearchEngine;
  16. use App\Entity\InvoiceSearch\Criterion\OrderIdCriterion;
  17. use App\Entity\InvoiceSearch\Criterion\InvoiceIdCriterion;
  18. use App\Entity\InvoiceSearch\Criterion\DateCriterion;
  19. use App\Entity\InvoiceSearch\Criterion\TypeCriterion;
  20. use App\Entity\InvoiceSearch\Criterion\ShopCriterion;
  21. use App\Entity\InvoiceSearch\Criterion\IdImportazioneCriterion;
  23. use App\Entity\Profis\ManebiProfisXML;
  24. use App\Entity\FatturaElettronica\ManebiFatturaElettronica;
  26. use App\Entity\ManebiStats;
  27. use App\Entity\OrderProduct;
  29. use Dompdf\Dompdf;
  31. /**
  32.  * 
  33.  */
  34. class InvoiceController extends AdminController
  35. {
  36.     public $importKey = 'Numero Fattura';
  37.     public $session = NULL;
  39.     /**
  40.      * 
  41.      */
  42.     public function manage(Request $request, ManebiStats $stats, SessionInterface $session)
  43.     {
  44.         $this->init($session);
  46.         if($request->attributes->get('_route') == 'admin_main'){
  47.             return new RedirectResponse($this->get('router')->generate('invoices_main'));
  48.         }
  50.         $filters = ['fatture-ids' => [], 'order-ids' => [], 'date-from' => '', 'date-to' => '', 'type' => ''];
  51.         $filter_params = [];
  52.         $export = false;
  54.         if($request->getMethod() == 'POST')
  55.         {
  56.             $filter_params['filter'] = $request->request->has('filter') ? $request->request->get('filter') : NULL;
  57.             $filter_params['filter-fatture-ids']  = $request->request->has('filter-fatture-ids') ? $request->request->get('filter-fatture-ids') : NULL;
  58.             $filter_params['filter-orders-ids']  = $request->request->has('filter-orders-ids') ? $request->request->get('filter-orders-ids') : NULL;
  59.             $filter_params['filter-date-from'] = $request->request->has('filter-date-from') ? $request->request->get('filter-date-from') : NULL;
  60.             $filter_params['filter-date-to'] = $request->request->has('filter-date-to') ? $request->request->get('filter-date-to') : NULL;
  61.             $filter_params['filter-type'] = $request->request->has('filter-type') ? $request->request->get('filter-type') : NULL;
  62.             $filter_params['filter-id-log-imports']  = $request->request->has('filter-id-log-imports') ? $request->request->get('filter-id-log-imports') : NULL;
  63.         }else{
  64.             $filter_params['filter'] = $request->query->has('filter') ? $request->query->get('filter') : NULL;
  65.             $filter_params['filter-fatture-ids']  = $request->query->has('filter-fatture-ids') ? $request->query->get('filter-fatture-ids') : NULL;
  66.             $filter_params['filter-orders-ids']  = $request->query->has('filter-orders-ids') ? $request->query->get('filter-orders-ids') : NULL;
  67.             $filter_params['filter-date-from'] = $request->query->has('filter-date-from') ? $request->query->get('filter-date-from') : NULL;
  68.             $filter_params['filter-date-to'] = $request->query->has('filter-date-to') ? $request->query->get('filter-date-to') : NULL;
  69.             $filter_params['filter-type'] = $request->query->has('filter-type') ? $request->query->get('filter-type') : NULL;
  70.             $filter_params['filter-id-log-imports']  = $request->query->has('filter-id-log-imports') ? $request->query->get('filter-id-log-imports') : NULL;
  71.         }
  73.         if($filter_params['filter'])
  74.         {
  75.             $invoiceSearchEngine = new InvoiceSearchEngine($this->getDoctrine()->getManager()->getConnection());
  77.             // Filter By Shop
  78.             if($this->session->has('id_shop'))
  79.             {
  80.                 $this->id_shop = (int) $this->session->get('id_shop');
  81.                 $shopIdCriterion = new ShopCriterion('shop-id');
  82.                 $shopIdCriterion->setId($this->id_shop);
  84.                 $invoiceSearchEngine->addCriterion($shopIdCriterion);
  85.             }
  87.             // Filter by ids
  88.             if($filter_params['filter-fatture-ids'])
  89.             {
  90.                 $invoiceIdCriterion = new InvoiceIDCriterion('invoice-ids');
  92.                 $_filtersIds = $filter_params['filter-fatture-ids'];
  93.                 
  94.                 if(!empty($_filtersIds))
  95.                 {
  96.                     foreach($_filtersIds as $tag)
  97.                     {
  98.                         $filters['fatture-ids'][] = $tag['tag'];
  99.                     }
  100.                 }
  102.                 $invoiceIdCriterion->setIDs($filters['fatture-ids']);
  103.                 $invoiceSearchEngine->addCriterion($invoiceIdCriterion);
  104.             }
  106.             // Filter by ids
  107.             if($filter_params['filter-orders-ids'])
  108.             {
  109.                 $orderIdCriterion = new OrderIDCriterion('order-ids');
  111.                 $_filtersIds = $filter_params['filter-orders-ids'];
  112.                 
  113.                 if(!empty($_filtersIds))
  114.                 {
  115.                     foreach($_filtersIds as $tag)
  116.                     {
  117.                         $filters['order-ids'][] = $tag['tag'];
  118.                     }
  119.                 }
  121.                 $orderIdCriterion->setIDs($filters['order-ids']);
  122.                 $invoiceSearchEngine->addCriterion($orderIdCriterion);
  123.             }
  125.             // Filter by Date From
  126.             if($filter_params['filter-date-from'])
  127.             {
  128.                 $dateFrom = $filter_params['filter-date-from'];
  130.                 if($dateFrom != ''){
  131.                     $invoiceDateFromCriterion = new DateCriterion('date-from');
  132.                     $invoiceDateFromCriterion->greaterOrEqualTo($dateFrom);
  133.                     $invoiceSearchEngine->addCriterion($invoiceDateFromCriterion);
  134.                 }
  135.                 
  136.             }
  138.             if($filter_params['filter-date-to'])
  139.             {
  140.                 $dateTo = $filter_params['filter-date-to'];
  142.                 if($dateTo != ''){
  143.                     $invoiceDateToCriterion = new DateCriterion('date-to');
  144.                     $invoiceDateToCriterion->lessOrEqualTo($dateTo);
  145.                     $invoiceSearchEngine->addCriterion($invoiceDateToCriterion);
  146.                 }
  147.             }
  149.             if($filter_params['filter-type'])
  150.             {
  151.                 $type = $filter_params['filter-type'];
  153.                 if($type != ''){
  154.                     $invoiceTypeCriterion = new TypeCriterion('type');
  155.                     $invoiceTypeCriterion->setValue($type);
  156.                     $invoiceSearchEngine->addCriterion($invoiceTypeCriterion);
  157.                 }
  158.             }
  160.             if($filter_params['filter-id-log-imports'])
  161.             {
  162.                 $idImportazione = trim($filter_params['filter-id-log-imports']);
  164.                 if($idImportazione != '')
  165.                 {
  166.                     $IdImportazioneCriterion = new IdImportazioneCriterion('id-log-imports');
  167.                     $IdImportazioneCriterion->setValue($idImportazione);
  168.                     $invoiceSearchEngine->addCriterion($IdImportazioneCriterion);
  169.                 }
  170.             }
  172.             if($invoiceSearchEngine->search())
  173.             {
  174.                 $results = $invoiceSearchEngine->getResults();
  176.                 foreach($results as &$result)
  177.                 {
  178.                     $result['Prodotti'] = $this->getProdottiByOrder($result['Order']);
  179.                 }
  181.                 if($request->request->has('exportProfis'))
  182.                 {
  183.                     $type = $type == 'invoices' ? 'fatture' : $type;
  184.                     
  185.                     $response = new Response($this->exportProfisXML($type, $results));
  186.                     $response->headers->set('Content-Type', 'text/xml');
  188.                     return $response;
  189.                 }
  191.                 if($request->request->has('exportFatturaElettronica'))
  192.                 {
  193.                     $type = $type == 'invoices' ? 'fatture' : $type;
  194.                     
  195.                     // Recupera i dettagli della Fattura/Nota credito
  196.                     foreach($results as &$result)
  197.                     {
  198.                         $result['conversion_rate'] = $this->getConversionRate('EUR', 'GBP', $result['Created at']);
  199.                         $result['tax_rate'] = $this->getTaxRateByCountry($result['Shipping Country']);
  201.                         $result = \App\Pdf\ManebiPdf::getPdfVariables($result, [], $this->getDoctrine()->getManager()->getConnection());
  202.                     }
  204.                     $response = new Response($this->exportFatturaElettronica($type, $results));
  205.                     $response->headers->set('Content-Type', 'application/octect-stream');
  207.                     return $response;
  208.                 }
  210.                 if($request->request->has('exportCSV') || $request->request->has('exportCSVDetails'))
  211.                 {   
  212.                     foreach($results as &$result)
  213.                     {
  214.                         $result['conversion_rate'] = $this->getConversionRate('EUR', 'GBP', $result['Created at']);
  215.                         $result['tax_rate'] = $this->getTaxRateByCountry($result['Shipping Country']);
  217.                         $result = \App\Pdf\ManebiPdf::getPdfVariables($result, [], $this->getDoctrine()->getManager()->getConnection());
  218.                     }
  220.                     if($request->request->has('exportCSV'))
  221.                     {
  222.                         $response = new Response($this->exportCSV($type, $results));
  223.                     }
  224.                         
  225.                     if($request->request->has('exportCSVDetails'))
  226.                     {
  227.                         $response = new Response($this->exportCSVDetails($type, $results));
  228.                     }
  230.                     return $response;
  231.                 }
  233.                 /*if($request->request->has('getPdf'))
  234.                 {
  235.                     $type = $type == 'invoices' ? 'fatture' : $type;
  236.                     
  237.                     $response = new Response($this->getPdf($type, $results));
  238.                     $response->headers->set('Content-Type', 'text/xml');
  240.                     return $response;
  241.                 }*/
  243.                 return new JsonResponse(['status' => 'OK', 'data' => $results]);
  244.             }else{
  245.                 return new JsonResponse(['status' => 'ERROR', 'message' => 'Cannot search']);
  246.             }
  247.         }
  249.         $params = [];
  250.         $params['ordiniSenzaIncassiERimborsi'] = $stats->getOrdiniSenzaIncassiERimborsi();
  251.         $params['shop'] = $this->getShop();
  252.         $params['id_shop'] = $this->session->get('id_shop');
  254.         return $this->render('invoices.html.twig', $params);
  255.     }
  257.     /**
  258.      * Get Invoice Data (Use Search Engine in order to search the target invoice)
  259.      */
  260.     public function getInvoice(Request $request, SessionInterface $session, string $invoiceId, $getPdf = false)
  261.     {
  262.         $this->init($session);
  264.         $invoiceId = '#'.$invoiceId;
  266.         $invoiceSearchEngine = new InvoiceSearchEngine($this->getDoctrine()->getManager()->getConnection());
  267.         $orderIdCriterion = new OrderIDCriterion('order-ids');
  268.         $orderIdCriterion->setIDs([$invoiceId]);
  269.         $invoiceSearchEngine->addCriterion($orderIdCriterion);
  271.         if($invoiceSearchEngine->search())
  272.         {
  273.             $results = $invoiceSearchEngine->getResults();
  274.         }
  276.         if($getPdf && !empty($results))
  277.         {
  278.             $result = $results[0];
  280.             $result['conversion_rate'] = $this->getConversionRate('EUR', 'GBP', $result['Created at']);
  281.             $result['tax_rate'] = $this->getTaxRateByCountry($result['Shipping Country']);
  283.             //Recupera i prodotti
  284.             $prodotti = $this->getProdottiByOrder($result['Order']);
  286.             $result['Prodotti'] = $prodotti;
  287.             
  288.             $result = \App\Pdf\ManebiPdf::getPdfVariables($result, [], $this->getDoctrine()->getManager()->getConnection());
  289.             //dump($result);
  290.             //die('test');
  291.             if($request->query->has('proforma_return'))
  292.             {
  293.                 $domPdf = $this->getPdf('proforma_return', $this->session->get('id_shop'), ['order' => $result, 'prodotti' => $prodotti]);
  294.             }else {
  295.                 $domPdf = $this->getPdf('fattura', $this->session->get('id_shop'), ['order' => $result, 'prodotti' => $prodotti]);
  296.             }
  297.             
  298.             return new Response($domPdf->output(), 200,
  299.                                         [
  300.                                             'Content-Type' => 'application/pdf',
  301.                                             'Content-Disposition' => 'attachment; filename="'.'MANEBI-'.(!$request->query->has('proforma_return') ? 'INVOICE' : 'RETURN').' ' . (!$request->query->has('proforma_return') ? $result['Numero Fattura'] : (str_replace('#','',$result['Order']) .'-'.$result['Shipping Country'])) . '.pdf'.'"'
  302.                                         ]);
  303.         }
  305.         return new JsonResponse(['status' => 'OK', 'data' => $results]);
  306.     }
  308.     /**
  309.      * Import Invoices
  310.      */
  311.     public function shopify_import(Request $request, ImporterCSV $importerCSV, SessionInterface $session)
  312.     {
  313.         $this->init($session);
  315.         $params = ['stats' => [], 'load_results' => []];
  317.         $importerCSV->setDB($this->getDoctrine()->getManager());
  318.         $importerCSV->setShopId($this->session->get('id_shop'));
  320.         if($request->getMethod() == 'POST')
  321.         {
  322.             if(isset($_FILES['file-shopify-csv']) && !empty($_FILES['file-shopify-csv']))
  323.             {
  324.                 if($_FILES['file-shopify-csv']['error'] == UPLOAD_ERR_OK)
  325.                 {
  326.                     $filename = date('Ymd_His').'-shopify';
  327.                     copy($_FILES['file-shopify-csv']['tmp_name'], dirname(__FILE__) . '/../../temp/import_ordini/'.$filename.'.csv');
  329.                     $result = $importerCSV->getFileFromShopify($_FILES['file-shopify-csv']['tmp_name']);
  331.                     $output = '';
  333.                     $csv = [];
  334.                     //var_dump(\reset($result['rows']));
  335.                     //$csv[] = array_keys((\reset($result['rows']))[0]);
  336.                     $outputHeader = [];
  337.                     $outputHeader[] = '"Ordini Letti",' . (count($result['processed_rows']) + count($result['orders_skipped']));
  338.                     $outputHeader[] = '"Ordini Processati",' . count($result['processed_rows']);
  339.                     $outputHeader[] = '"Ordini Esistenti",' . count($result['orders_skipped']) . ', '.implode(' - ', $result['orders_skipped']);
  340.                     $outputHeader[] = '"Ordini Antecedenti Ultimo Ordine",' . count($result['orders_old']) . ', '.implode(' - ', $result['orders_old']);
  341.                     $outputHeader[] = '"Ordini Aliquota diversa:",' . count($result['different_vat']) . ', '.implode(' - ', $result['different_vat']);
  343.                     $outputHeader[] = '';
  344.                     $outputHeader[] = '';
  346.                     foreach($result['processed_rows'] as $order)
  347.                     {
  348.                         foreach($order as $order_line)
  349.                         {
  350.                             $csv[] = $order_line; 
  351.                         }
  352.                     }
  353.                     
  354.                     if(count($result['processed_rows']) > 0)
  355.                     {
  356.                         $output = $this->array2csv($csv, ',', '"', '\\');
  357.                     }
  358.                     
  359.                     $output = implode("\n", $outputHeader) . $output;
  361.                     //file_put_contents('/tmp', $output, );
  362.                     file_put_contents(dirname(__FILE__) . '/../../temp/import_ordini/'.$filename.'-output.csv', $output);
  364.                     header('Content-Type: text/csv; charset=UTF-8');
  365.                     header('Content-Description: Manebi - File Transfer CSV Profis');
  366.                     header('Content-Disposition: attachment; filename="' . $this->getShop()->getName() . ' - Import from Shopify - ' . date('Ymd_Hi') . '.csv"');
  367.                     header('Content-Transfer-Encoding: binary');
  368.                     header('Expires: 0');
  369.                     header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
  370.                     
  371.                     die($output);
  372.                 }
  373.             }
  375.             if($request->query->get('loadInvoices') == 1)
  376.             {
  377.                 $params['load_results'] = $importerCSV->loadInvoices();
  379.                 // 2021-07-26 - Antonio@Tembo: reset Exports for imported file
  380.                 $params['reset_export_results'] = $importerCSV->resetExport();
  381.             }
  382.         }
  383.     }
  385.     /**
  386.      * Import Invoices
  387.      */
  388.     public function import(Request $request, ImporterCSV $importerCSV, SessionInterface $session)
  389.     {
  390.         $this->init($session);
  392.         $params = ['stats' => [], 'load_results' => []];
  394.         $importerCSV->setDB($this->getDoctrine()->getManager());
  395.         $importerCSV->setShopId($this->session->get('id_shop'));
  397.         if($request->getMethod() == 'POST')
  398.         {
  399.             if(isset($_FILES['file-csv']) && !empty($_FILES['file-csv']))
  400.             {
  401.                 if($_FILES['file-csv']['error'] == UPLOAD_ERR_OK)
  402.                 {
  403.                     $result = $importerCSV->importFileInDb($_FILES['file-csv']['tmp_name'], 'tmp_csv_ordini');
  405.                     if($result)
  406.                     {
  407.                         $params['stats'] = $importerCSV->getImportStats('ordini');
  408.                         $params['check_vat_errors'] = $this->checkVatErrors();
  409.                         $params['has_vat_errors'] = false;
  411.                         if(!empty($params['check_vat_errors'])) {
  412.                             foreach($params['check_vat_errors'] as $err)
  413.                             {
  414.                                 if($err['IsError'] <> "OK")
  415.                                 {
  416.                                     $params['has_vat_errors'] = true;
  417.                                     break;
  418.                                 }
  419.                             }
  420.                         }
  421.                     }
  422.                 }
  423.             }
  425.             if($request->query->get('loadInvoices') == 1)
  426.             {
  427.                 $params['load_results'] = $importerCSV->loadInvoices();
  429.                 // 2021-07-26 - Antonio@Tembo: reset Exports for imported file
  430.                 $params['reset_export_results'] = $importerCSV->resetExport();
  431.             }
  432.         }
  434.         $params['checkNumerazioneFatture'] = $this->checkNumerazioneFatture($this->session->get('id_shop'), date('Y'));
  435.         $params['shop'] = $this->getShop();
  436.         $params['id_shop'] = $this->session->get('id_shop');
  437.         $params['importazioni'] = $importerCSV->getImportazioni('invoices');
  439.         return $this->render('invoices.import.html.twig', $params);
  440.     }
  442.     /**
  443.      * 
  444.      */
  445.     public function delete(Request $request, SessionInterface $session, string $invoiceId) 
  446.     {
  447.         $this->init($session);
  448.         $conn = $this->getDoctrine()->getManager()->getConnection();
  450.         $idOrder = (int) $invoiceId;
  452.         if($idOrder == 0)
  453.         {
  454.             $id_log_imports = (int) ($request->request->has('id_log_imports') ? $request->request->get('id_log_imports') : NULL);
  456.             if($id_log_imports > 0)
  457.             {
  458.                 $conn->prepare('START TRANSACTION')->execute();
  459.                 $conn->prepare('DELETE FROM `ordini_prodotti` WHERE `Order` IN (SELECT `Order` FROM `ordini` WHERE `id_log_imports` = '.(int) $id_log_imports.')')->execute();
  460.                 $conn->prepare('DELETE FROM `ordini` WHERE `id_log_imports` = '.(int) $id_log_imports)->execute();
  461.                 $conn->prepare('UPDATE `log_imports` SET `date_delete` = "' . date('Y-m-d H:i:s') . '" WHERE `id` = '.(int) $id_log_imports)->execute();
  462.                 $conn->prepare('COMMIT')->execute();
  464.                 return new JsonResponse(['status' => 'OK']);
  465.             }
  467.             return new JsonResponse(['status' => 'ERROR', 'message' => 'Importazione non valida']);
  468.         }else
  469.         {
  470.             $Order = '#' . $idOrder;
  472.             $conn->prepare('START TRANSACTION')->execute();
  473.             
  474.             // Rimuovi i prodotti
  475.             $stmt = $conn->prepare('DELETE FROM `ordini_prodotti` WHERE `Order` = "' . $Order . '"');
  476.             $res = $stmt->execute();
  478.             // Rimuovi l'ordine
  479.             $stmt = $conn->prepare('DELETEFROM  `ordini` WHERE `Order` = "' . $Order . '"');
  480.             $res = $stmt->execute();
  482.             $conn->prepare('COMMIT')->execute();
  484.             if($res)
  485.             {
  486.                 return new JsonResponse(['status' => 'OK']);   
  487.             }
  488.         }
  489.         
  490.         return new JsonResponse(['status' => 'ERROR', 'message' => 'Ordine non valido']);
  491.     }
  493.     /**
  494.      * Export for PROFIS
  495.      */
  496.     protected function exportProfisXML($type, $results)
  497.     {
  498.         $profis = new ManebiProfisXML();
  499.         $profis->setDB($this->getDoctrine()->getManager())
  500.             ->setIdShop($this->session->get('id_shop'))
  501.             ->setType($type)
  502.             ->generate($results);
  503.         
  504.         header('Content-Type: text/xml; charset=UTF-8');
  505.         header('Content-Description: Manebi - File Transfer XML Profis');
  506.         header('Content-Disposition: attachment; filename="MANEBI - INVOICES - '.date('Ymd_Hi').'.xml"');
  507.         header('Content-Transfer-Encoding: binary');
  508.         header('Expires: 0');
  509.         header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
  511.         return $profis->getXML(true);
  512.     }
  514.     /**
  515.      * Export Fattura Elettronica
  516.      */
  517.     protected function exportFatturaElettronica($type, $results)
  518.     {
  519.         $fatturaElettronica = new ManebiFatturaElettronica();
  520.         $fatturaElettronica->setDB($this->getDoctrine()->getManager())
  521.             ->setIdShop($this->session->get('id_shop'))
  522.             ->setType($type)
  523.             ->generate($results);
  524.         
  525.         die();
  526.         header('Content-Type: application/octect-stream');
  527.         header('Content-Description: Manebi - File Transfer XML Profis');
  528.         header('Content-Disposition: attachment; filename="MANEBI - FATTURA ELETTRONICA - '.date('Ymd_Hi').'.xml"');
  529.         header('Content-Transfer-Encoding: binary');
  530.         header('Expires: 0');
  531.         header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
  533.         return $fatturaElettronica->getFiles(true);
  534.     }
  536.     /**
  537.      * Export for PROFIS
  538.      */
  539.     protected function exportCSV($type, $results)
  540.     {
  541.         $csv = new ManebiCSV();
  542.         $csv->setType($type)->generateOrders($results);
  543.         
  544.         header('Content-Type: text/csv; charset=UTF-8');
  545.         header('Content-Description: Manebi - File Transfer CSV Profis');
  546.         header('Content-Disposition: attachment; filename="' . $this->getShop()->getName() . ' - ' . $type . ' - ' . date('Ymd_Hi') . '.csv"');
  547.         header('Content-Transfer-Encoding: binary');
  548.         header('Expires: 0');
  549.         header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
  551.         return $csv->getCSV(true);
  552.     }
  554.      /**
  555.      * Export for PROFIS
  556.      */
  557.     protected function exportCSVDetails($type, $results)
  558.     {
  559.         $csv = new ManebiCSV();
  560.         $csv->setType($type)->generateOrdersDetails($results);
  561.         
  562.         header('Content-Type: text/csv; charset=UTF-8');
  563.         header('Content-Description: Manebi - File Transfer CSV Profis');
  564.         header('Content-Disposition: attachment; filename="' . $this->getShop()->getName() . ' - ' . $type . ' Details - ' . date('Ymd_Hi') . '.csv"');
  565.         header('Content-Transfer-Encoding: binary');
  566.         header('Expires: 0');
  567.         header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
  569.         return $csv->getCSV(true);
  570.     }
  572.     /**
  573.      * 
  574.      */
  575.     public function getProdottiByOrder($OrderID)
  576.     {
  577.         $conn = $this->getDoctrine()->getManager()->getConnection();
  578.         $stmt = $conn->prepare('SELECT * 
  579.                                 FROM ordini_prodotti WHERE `Order` = "'.$OrderID.'"');
  581.         if($stmt->execute())
  582.         {
  583.             $products = $stmt->fetchAll();
  585.             if(!empty($products))
  586.             {
  587.                 foreach($products as $key => &$product)
  588.                 { 
  589.                     $_code = substr($product['SKU'], 0, 5);
  590.                     $stmt = $conn->prepare('SELECT `HSCode`,`CountryOfOrigin`,`EU_ExtraEU`,`Weight` FROM `codici_doganali` WHERE  `Codice` = "'.$_code.'"');
  591.                     $stmt->execute();
  592.                     $result = $stmt->fetch();
  594.                     $product['HSCode'] = $result ? $result['HSCode'] : '';
  595.                     $product['CountryOfOrigin'] = $result ? $result['CountryOfOrigin'] : '';
  596.                     $product['EU_ExtraEU'] = $result ? $result['EU_ExtraEU'] : '';
  597.                     $product['Weight'] = $result ? (float) $result['Weight'] * (float) $product['Quantity'] : '';
  599.                     $_name = explode(' - ', $product['Name']);
  600.                     
  601.                     if(isset($_name[1])) {
  602.                         $product['Category'] = $_name[0];
  603.                         $product['Articolo'] = $_name[1];
  604.                     }else{
  605.                         $product['Category'] = '';
  606.                         $product['Articolo'] = $_name[0];
  607.                     }
  609.                     if(isset($_name[2]))
  610.                     {
  611.                         $product['CleanName'] = $_name[2];
  612.                     }
  614.                     if(isset($_name[3]))
  615.                     {
  616.                         $product['Size'] = (int) $_name[3];
  617.                     }else
  618.                     {
  619.                         $product['Size'] = '';
  620.                     }
  621.                 }
  623.                 return $products;
  624.             }
  625.         }
  627.         return [];
  628.     }
  630.     /**
  631.      * Genera il PDF di una singola fattura
  632.      */
  633.     protected function getPdf($type, $id_shop, $result)
  634.     {
  635.         $options = new \Dompdf\Options(); 
  636.         $options->setIsRemoteEnabled(true);
  638.         $dompdf = new \Dompdf\Dompdf($options);
  639.         $dompdf->setPaper('A4', 'vertical');
  641.         // Recupera il giusto template dalla data dell'ordine
  642.         $createdDate = substr($result['order']['Created at'], 0, 10);
  643.         
  644.         $templateVersion = '';
  645.         
  646.         if($createdDate >= '2025-03-12')
  647.         {
  648.             $templateVersion = '2025-03-12';
  649.         }elseif($createdDate >= '2025-02-01')
  650.         {
  651.             $templateVersion = '2025-02-01';
  652.         }
  654.         if($type == "proforma_return")
  655.             $htmlPDF = $this->renderView('pdf/'.$templateVersion.'/pdf_proforma_return_' . $id_shop . '.html.twig', $result);
  656.         else 
  657.             $htmlPDF = $this->renderView('pdf/'.$templateVersion.'/pdf_invoice_' . $id_shop . '.html.twig', $result);
  658.         
  659.         $dompdf->load_html($htmlPDF);
  660.         $dompdf->render();
  661.         
  662.         return $dompdf;
  663.     }
  665.     /**
  666.      * 
  667.      */
  668.     public function checkNumerazioneFatture($idShop, $anno)
  669.     {
  670.         $errors = [];
  672.         $conn = $this->getDoctrine()->getManager()->getConnection();
  673.         $stmt = $conn->prepare('SELECT `Order`, `Numero Fattura`, `Created at` 
  674.                                 FROM `ordini` WHERE `Shop` = ' . (int) $idShop . ' AND `Numero Fattura` <> "" AND YEAR(`Created at`) = '.$anno.' 
  675.                                 ORDER BY CONVERT(REPLACE(`Numero Fattura`,"B",""), UNSIGNED INT) ASC, DATE(`Created at`) ASC');
  676.         $stmt->execute();
  678.         $fatture = $stmt->fetchAll();
  680.         $_currentFattura = 0;
  681.         $_currentFatturaData = '';
  683.         foreach($fatture as $index => $f)
  684.         {
  685.             $fatturaNum = (int) str_replace('B', '', $f['Numero Fattura']);
  686.             
  687.             if($index == 0)
  688.             {
  689.                 $_currentOrder = $f['Order'];
  690.                 $_currentFattura = $fatturaNum;
  691.                 $_currentFatturaData = $f['Created at'];
  692.                 continue;
  693.             }
  695.             if($fatturaNum - $_currentFattura != 1)
  696.             {
  697.                 $errors = [
  698.                             'ultima_fattura_ok' => $_currentFattura, 
  699.                             'ultima_fattura_ok_data' => $_currentFatturaData, 
  700.                             'ultimo_ordine_ok' => $_currentOrder,
  702.                             'error_fattura' => $fatturaNum,
  703.                             'error_fattura_data' => $f['Created at'],
  704.                             'error_order' => $f['Order'],
  705.                         ];
  707.                 break;
  708.             }
  710.             $_currentOrder = $f['Order'];
  711.             $_currentFattura = $fatturaNum;
  712.             $_currentFatturaData = $f['Created at'];
  713.         }
  715.         return $errors;
  716.     }
  718.     /**
  719.      * 
  720.      */
  721.     public function getConversionRate($fromCurr, $toCurr, $date)
  722.     {
  723.         $conn = $this->getDoctrine()->getManager()->getConnection();
  724.         $statement = $conn->prepare('SELECT rate FROM `conversion_rates` WHERE `currency_from` = "'.$fromCurr.'" AND `currency_to` = "'.$toCurr.'" AND `date` = DATE("'.$date.'")');
  726.         if($statement->execute())
  727.         {
  728.             return $statement->fetch();
  729.         }
  731.         return NULL;
  732.     }
  734.     /**
  735.      * 
  736.      */
  737.     public function getTaxRateByCountry($iso_code)
  738.     {
  739.         $conn = $this->getDoctrine()->getManager()->getConnection();
  740.         $statement = $conn->prepare('SELECT `rate` FROM `paesi` WHERE iso_code = "'.$iso_code.'" ');
  741.         $statement->execute();
  743.         return $statement->fetch();
  744.     }
  746.     /**
  747.      * Controlla se ci sono errori nella VAT dei paesi europei,
  748.      * confrontando il Vat rate di Shopify con i rate che abbiamo noi.
  749.      */
  750.     public function checkVatErrors()
  751.     {
  752.         $conn = $this->getDoctrine()->getManager()->getConnection();
  753.         $statement = $conn->prepare('SELECT Y.`Order`, 
  754.         Y.`Country`, 
  755.         Y.`Shipping Country`, 
  756.         Y.`Shipping Province`, 
  757.         Y.`Shopify Country Rate`, 
  758.         Y.`Manebi Country Rate`, 
  759.         Y.`OrderVatRate`,
  760.         IFNULL(Y.`Province Tax Rate`,"") as "Province Tax Rate",
  761.         CASE
  762.             WHEN (Y.`OrderVatRate` > 0 AND Y.`Province Tax Rate` IS NULL) THEN "ERROR-PROVINCETAXRATE-NULL"
  763.           WHEN (Y.`OrderVatRate` IS NULL AND Y.`Province Tax Rate` > 0)  THEN "ERROR-ORDERVATRATE-NULL"
  764.            WHEN (Y.`OrderVatRate` <> Y.`Province Tax Rate`) THEN "ERROR-ORDERVATRATE-DIFF-PROVINCETAXRATE"
  765.          ELSE "OK"
  766.         END as "IsError"
  767.         FROM (SELECT `Order`, `Shipping Country`, `Shipping Province`, `Tax 1 Name`, 
  768.                             p.name as "Country", 
  769.                                                 REGEXP_SUBSTR(`Tax 1 Name`,"[0-9\.]+") as "Shopify Country Rate", 
  770.                                                 CONVERT(ROUND(p.rate,2),VARCHAR(10)) as "Manebi Country Rate",
  771.                                                 `OrderVatRate`,
  772.                                                 prov.rate as "Province Tax Rate"
  773.                                                 FROM tmp_csv_ordini X
  774.                                                 join paesi p ON X.`Shipping Country` = p.`iso_code`
  775.                                                 left join province prov ON (X.`Shipping Country` = prov.`iso_code_paese` AND X.`Shipping Province` = prov.iso_code) 
  776.                                                 ) Y
  777.         WHERE ROUND(Y.`Shopify Country Rate`,2) <> Y.`Manebi Country Rate`;');
  778.                                    
  779.         $statement->execute();
  781.         return $statement->fetchAll();
  782.     }
  783. }