Symfony Profiler

<?php
namespace App\Controller;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\RedirectResponse;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Session\SessionInterface;
use \MongoDB\Client as MongoDBClient;
use App\Entity\ImporterCSV;
use App\Entity\ManebiCSV;
use App\Entity\IncassiSearch\IncassiSearchEngine;
use App\Entity\IncassiSearch\Criterion\OrderIDCriterion;
use App\Entity\IncassiSearch\Criterion\DateCriterion;
use App\Entity\IncassiSearch\Criterion\TypeCriterion;
use App\Entity\IncassiSearch\Criterion\OrderTypeCriterion;
use App\Entity\IncassiSearch\Criterion\ShopCriterion;
use App\Entity\IncassiSearch\Criterion\CreditNoteCriterion;
use App\Entity\IncassiSearch\Criterion\SourceCriterion;
use App\Entity\IncassiSearch\Criterion\IdImportazioneCriterion;
use App\Entity\Profis\ManebiProfisXML;
use App\Entity\FatturaElettronica\ManebiFatturaElettronica;
use SessionIdInterface;
/**
*
*/
class IncassiController extends AdminController
{
/**
*
*/
public function manage(Request $request, SessionInterface $session)
{
$this->init($session);
$this->conn = $this->getDoctrine()->getManager()->getConnection();
$filters = ['ids' => [], 'date-from' => '', 'date-to' => ''];
$filter_params = [];
$export = false;
if($request->getMethod() == 'POST')
{
$filter_params['filter'] = $request->request->has('filter') ? $request->request->get('filter') : NULL;
$filter_params['filter-tipo'] = $request->request->has('filter-tipo') ? $request->request->get('filter-tipo') : NULL;
$filter_params['filter-order-type'] = $request->request->has('filter-order-type') ? $request->request->get('filter-order-type') : NULL;
$filter_params['filter-date-from'] = $request->request->has('filter-date-from') ? $request->request->get('filter-date-from') : NULL;
$filter_params['filter-date-to'] = $request->request->has('filter-date-to') ? $request->request->get('filter-date-to') : NULL;
$filter_params['filter-orders-ids'] = $request->request->has('filter-orders-ids') ? $request->request->get('filter-orders-ids') : NULL;
$filter_params['filter-credit-notes-from'] = $request->request->has('filter-credit-notes-from') ? $request->request->get('filter-credit-notes-from') : NULL;
$filter_params['filter-credit-notes-to'] = $request->request->has('filter-credit-notes-to') ? $request->request->get('filter-credit-notes-to') : NULL;
$filter_params['filter-source'] = $request->request->has('filter-source') ? $request->request->get('filter-source') : NULL;
$filter_params['filter-id-log-imports'] = $request->request->has('filter-id-log-imports') ? $request->request->get('filter-id-log-imports') : NULL;
}else{
$filter_params['filter'] = $request->query->has('filter') ? $request->query->get('filter') : NULL;
$filter_params['filter-tipo'] = $request->query->has('filter-tipo') ? $request->query->get('filter-tipo') : NULL;
$filter_params['filter-order-type'] = $request->query->has('filter-order-type') ? $request->query->get('filter-order-type') : NULL;
$filter_params['filter-date-from'] = $request->query->has('filter-date-from') ? $request->query->get('filter-date-from') : NULL;
$filter_params['filter-date-to'] = $request->query->has('filter-date-to') ? $request->query->get('filter-date-to') : NULL;
$filter_params['filter-orders-ids'] = $request->query->has('filter-orders-ids') ? $request->query->get('filter-orders-ids') : NULL;
$filter_params['filter-credit-notes-from'] = $request->query->has('filter-credit-notes-from') ? $request->query->get('filter-credit-notes-from') : NULL;
$filter_params['filter-credit-notes-to'] = $request->query->has('filter-credit-notes-to') ? $request->query->get('filter-credit-notes-to') : NULL;
$filter_params['filter-source'] = $request->query->has('filter-source') ? $request->query->get('filter-source') : NULL;
$filter_params['filter-id-log-imports'] = $request->query->has('filter-id-log-imports') ? $request->query->get('filter-id-log-imports') : NULL;
}
if($filter_params['filter'])
{
$incassiSearchEngine = new IncassiSearchEngine($this->conn);
// Filter By Shop
if($this->session->has('id_shop'))
{
$this->id_shop = (int) $this->session->get('id_shop');
$shopIdCriterion = new ShopCriterion('shop-id');
$shopIdCriterion->setId($this->id_shop);
$incassiSearchEngine->addCriterion($shopIdCriterion);
}
// Filter by Date From
if($filter_params['filter-date-from'])
{
$dateFrom = $filter_params['filter-date-from'];
if($dateFrom != ''){
$incassiDateFromCriterion = new DateCriterion('date-from');
$incassiDateFromCriterion->greaterOrEqualTo($dateFrom);
$incassiSearchEngine->addCriterion($incassiDateFromCriterion);
}
}
if($filter_params['filter-date-to'])
{
$dateTo = $filter_params['filter-date-to'];
if($dateTo != ''){
$incassiDateToCriterion = new DateCriterion('date-to');
$incassiDateToCriterion->lessOrEqualTo($dateTo);
$incassiSearchEngine->addCriterion($incassiDateToCriterion);
}
}
if($filter_params['filter-tipo'])
{
$type = $filter_params['filter-tipo'];
if($type != ''){
$incassiTypeCriterion = new TypeCriterion('type');
$incassiTypeCriterion->setValue($type);
$incassiSearchEngine->addCriterion($incassiTypeCriterion);
}
}
if($filter_params['filter-order-type'])
{
$type = $filter_params['filter-order-type'];
if($type != ''){
$orderTypeCriterion = new OrderTypeCriterion('order-type');
$orderTypeCriterion->setValue($type);
$incassiSearchEngine->addCriterion($orderTypeCriterion);
}
}
// Filter by ids
if($filter_params['filter-orders-ids'])
{
$orderIdCriterion = new OrderIDCriterion('order-ids');
$_filtersIds = $filter_params['filter-orders-ids'];
if(!empty($_filtersIds))
{
foreach($_filtersIds as $tag)
{
$filters['order-ids'][] = $tag['tag'];
}
}
$orderIdCriterion->setIDs($filters['order-ids']);
$incassiSearchEngine->addCriterion($orderIdCriterion);
}
// Filter credit notes
if($filter_params['filter-credit-notes-from'])
{
$dateFrom = $filter_params['filter-credit-notes-from'];
if($dateFrom != '')
{
$creditNoteFromCriterion = new CreditNoteCriterion('credit-notes-from');
$creditNoteFromCriterion->greaterOrEqualTo($dateFrom);
$incassiSearchEngine->addCriterion($creditNoteFromCriterion);
}
}
if($filter_params['filter-credit-notes-to'])
{
$dateTo = $filter_params['filter-credit-notes-to'];
if($dateTo != '')
{
$creditNoteToCriterion = new CreditNoteCriterion('credit-notes-to');
$creditNoteToCriterion->lessOrEqualTo($dateTo);
$incassiSearchEngine->addCriterion($creditNoteToCriterion);
}
}
if($filter_params['filter-source'])
{
$source = $filter_params['filter-source'];
if($source != '')
{
$sourceCriterion = new SourceCriterion('source');
$sourceCriterion->setValue($source);
$incassiSearchEngine->addCriterion($sourceCriterion);
}
}
if($filter_params['filter-id-log-imports'])
{
$idImportazione = trim($filter_params['filter-id-log-imports']);
if($idImportazione != '')
{
$IdImportazioneCriterion = new IdImportazioneCriterion('id-log-imports');
$IdImportazioneCriterion->setValue($idImportazione);
$incassiSearchEngine->addCriterion($IdImportazioneCriterion);
}
}
if($incassiSearchEngine->search())
{
$results = $incassiSearchEngine->getResults();
if($request->request->has('exportFatturaElettronica') || $request->request->has('exportProfis') || $request->request->has('exportCSV') || $request->request->has('exportCSVDetails'))
{
foreach($results as $key => $res)
{
$stmt = $this->conn->prepare('SELECT * FROM ordini WHERE `Order` = "'.$res['Order'].'"');
if($res = $stmt->execute())
{
$results[$key]['OrderData'] = $stmt->fetchAll();
if(!empty($results[$key]['OrderData']))
{
$results[$key]['OrderData'] = $results[$key]['OrderData'][0];
}
}
}
if($request->request->has('exportProfis'))
{
$response = new Response($this->exportProfisXML('incassi-rimborsi', $results));
$response->headers->set('Content-Type', 'text/xml');
}
if($request->request->has('exportFatturaElettronica') || $request->request->has('exportCSV') || $request->request->has('exportCSVDetails'))
{
foreach($results as &$result)
{
$result['conversion_rate'] = $this->getConversionRate('EUR', 'GBP', $result['OrderData']['Created at']);
$result['tax_rate'] = $this->getTaxRateByCountry($result['OrderData']['Shipping Country']);
$result['Prodotti'] = $this->getProdottiByOrder($result['Order']);
$prodotti_nc = $this->getProdottiByNotaCredito($result);
$shipping_nc = $this->getShippingByNotaCredito($result);
$result = \App\Pdf\ManebiPdf::getPdfVariables($result, [
'prodotti_nc' => $prodotti_nc,
'shipping_nc' => $shipping_nc
],
$this->getDoctrine()->getManager()->getConnection()
);
}
if($request->request->has('exportFatturaElettronica'))
{
$response = new Response($this->exportFatturaElettronicaNotaCredito($type, $results));
$response->headers->set('Content-Type', 'application/octect-stream');
return $response;
}
if($request->request->has('exportCSV'))
$response = new Response($this->exportCSV('incassi-rimborsi', $results));
if($request->request->has('exportCSVDetails'))
$response = new Response($this->exportCSVDetails('incassi-rimborsi', $results));
}
return $response;
}
return new JsonResponse(['status' => 'OK', 'data' => $results]);
}else{
return new JsonResponse(['status' => 'ERROR', 'message' => 'Cannot search']);
}
}
$incassiSenzaOrdine = $this->getIncassiSenzaOrdine($this->session->get('id_shop'));
$ordiniSenzaIncassi = $this->getOrdiniSenzaIncassi($this->session->get('id_shop'));
$params = [];
$params['incassiSenzaOrdine'] = $incassiSenzaOrdine;
$params['shop'] = $this->getShop();
$params['id_shop'] = $this->session->get('id_shop');
return $this->render('incassi.html.twig', $params);
}
/**
* Export for PROFIS
*/
protected function exportProfisXML($type, $results)
{
$profis = new ManebiProfisXML();
$profis->setDB($this->getDoctrine()->getManager())
->setIdShop($this->session->get('id_shop'))
->setType($type)
->generate($results);
header('Content-Type: text/xml; charset=UTF-8');
header('Content-Description: Manebi - File Transfer XML Profis');
header('Content-Disposition: attachment; filename="MANEBI - INVOICES - '.date('Ymd_Hi').'.xml"');
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
return $profis->getXML(true);
}
/**
* Export CSV
*/
protected function exportCSV($type, $results)
{
$csv = new ManebiCSV();
$csv->setType($type)->setDB($this->getDoctrine()->getManager())->generateIncassi($results);
header('Content-Type: text/csv; charset=UTF-8');
header('Content-Description: Manebi - File Transfer CSV Profis');
header('Content-Disposition: attachment; filename="' . $this->getShop()->getName() . ' - ' . $type . ' - ' . date('Ymd_Hi') . '.csv"');
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
return $csv->getCSV(true);
}
/**
* Export CSV Details
*/
protected function exportCSVDetails($type, $results)
{
$csv = new ManebiCSV();
$csv->setType($type)->setDB($this->getDoctrine()->getManager())->generateIncassiDetails($results);
header('Content-Type: text/csv; charset=UTF-8');
header('Content-Description: Manebi - File Transfer CSV Profis');
header('Content-Disposition: attachment; filename="' . $this->getShop()->getName() . ' - ' . $type . ' DETAILS - ' . date('Ymd_Hi') . '.csv"');
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
return $csv->getCSV(true);
}
/**
*
*/
public function import(Request $request, ImporterCSV $importerCSV, SessionInterface $session)
{
$this->init($session);
$params = ['stats' => [], 'load_results' => []];
$importerCSV->setDb($this->getDoctrine()->getManager());
$importerCSV->setShopId($this->session->get('id_shop'));
if($request->getMethod() == 'POST')
{
$tipoIncasso = $request->request->get('tipo_incasso');
if(isset($_FILES['incassi-file-csv']) && !empty($_FILES['incassi-file-csv']))
{
if($_FILES['incassi-file-csv']['error'] == UPLOAD_ERR_OK)
{
$idImportazione = (int) $importerCSV->addImportazione('incassi', $_FILES['incassi-file-csv']['name']);
$result = $importerCSV->importIncassiInDb($tipoIncasso, $_FILES['incassi-file-csv']['tmp_name'], $idImportazione);
if($result)
{
$params['stats'] = $importerCSV->getImportStats('incassi', $tipoIncasso);
}
}
}
}
$params['shop'] = $this->getShop();
$params['id_shop'] = $this->session->get('id_shop');
$params['importazioni'] = $importerCSV->getImportazioni('incassi');
return $this->render('incassi.import.html.twig', $params);
}
public function getIncassiSenzaOrdine($id_shop, $count = false)
{
$conn = $this->getDoctrine()->getManager()->getConnection();
$statement = $conn->prepare('SELECT I.id, I.PaymentReference, I.Order, I.Date, I.Source, I.Type, I.`ChargeAmount`, I.`RefundAmount`,
(SELECT IF(((`Numero Fattura` = "") AND (`Corrispettivo` = "")), "NO", "") FROM `ordini` WHERE `Order` = I.`Order`) AS "Gestito"
FROM `incassi` I
LEFT JOIN `ordini` O ON (I.`Order` = O.`Order` AND O.`Shop` = ' . $id_shop . ' AND (`Numero Fattura` <> "" OR `Corrispettivo` <> ""))
WHERE 1 AND I.`Shop` = ' . $id_shop . ' AND O.Order IS NULL');
if($res = $statement->execute())
{
if($count)
{
return count($statement->fetchAll());
}
return $statement->fetchAll();
}
return NULL;
}
/**
*
*/
public function getOrdiniSenzaIncassi($count = false)
{
$conn = $this->getDoctrine()->getManager()->getConnection();
$statement = $conn->prepare('SELECT * FROM
(SELECT I.id, O.Order, O.`Numero Fattura`, O.`Corrispettivo`, O.`Payment Reference`, I.`PaymentReference`, I.Source, I.`ChargeAmount`, I.`RefundAmount`
FROM ordini O
JOIN incassi I ON (O.Order = I.`Order`)
WHERE I.`Order` <> ""
UNION
(
SELECT I.id, O.Order, O.`Numero Fattura`, O.`Corrispettivo`, O.`Payment Reference`, I.`PaymentReference`, I.Source, I.`ChargeAmount`, I.`RefundAmount`
FROM ordini O
JOIN incassi I ON (I.Order = "" AND (INSTR(O.`Payment Reference`, I.`PaymentReference`) > 0))
)) INCASSI
WHERE INCASSI.Order NOT IN (SELECT `Order` FROM ordini)
');
if($res = $statement->execute())
{
if($count)
{
return count($statement->fetchAll());
}
return $statement->fetchAll();
}
return NULL;
}
/**
* Export Fattura Elettronica per Nota Credito
*/
protected function exportFatturaElettronicaNotaCredito($type, $results)
{
$fatturaElettronica = new ManebiFatturaElettronica();
$fatturaElettronica->setDB($this->getDoctrine()->getManager())
->setIdShop($this->session->get('id_shop'))
->setType($type)
->generate($results);
die();
}
/**
*
*/
public function getIncassi($count = false)
{
$conn = $this->getDoctrine()->getManager()->getConnection();
$statement = $conn->prepare('SELECT * FROM
(SELECT I.id, O.Order, O.`Numero Fattura`, O.`Corrispettivo`, O.`Payment Reference`, I.`PaymentReference`, I.Source, I.`ChargeAmount`, I.`RefundAmount`
FROM ordini O
JOIN incassi I ON (O.Order = I.`Order`)
WHERE I.`Order` <> ""
UNION
(
SELECT I.id, O.Order, O.`Numero Fattura`, O.`Corrispettivo`, O.`Payment Reference`, I.`PaymentReference`, I.Source, I.`ChargeAmount`, I.`RefundAmount`
FROM ordini O
JOIN incassi I ON (I.Order = "" AND (INSTR(O.`Payment Reference`, I.`PaymentReference`) > 0))
)) INCASSI
WHERE INCASSI.Order NOT IN (SELECT `Order` FROM ordini)');
if($res = $statement->execute())
{
if($count)
{
return count($statement->fetchAll());
}
return $statement->fetchAll();
}
return NULL;
}
/**
*
*/
public function assignNumNotaCredito(Request $request, SessionInterface $session)
{
$this->init($session);
$year = date('Y');
$preview = $request->query->has('preview') && $request->query->get('preview') == 'true' ? true : false;
$confirm = $request->query->has('confirm') ? (int) $request->query->get('confirm') : 0;
$result = [];
if($preview)
{
if(!$request->query->has('filter-date-to'))
{
$result['status'] = 'ERROR';
$result['message'] = 'Devi specificare una data di fine nel filtro per identificare correttamente l\'anno delle Note Credito da analizzare';
return new JsonResponse($result);
}
$year = date('Y', strtotime($request->query->get('filter-date-to')));
//Recupera l'ultimo Numero Nota Credito Assegnato
$lastNotaCredito = $this->getLastNotaCredito($year, $this->session->get('id_shop'));
// Anteprima
if(!empty($lastNotaCredito))
{
$result['status'] = 'OK';
$result['lastNotaCredito'] = $lastNotaCredito;
$result['rimborsi'] = [];
$result['rimborsi'][] = $lastNotaCredito;
$lastDateNotaCredito = date('Y-m-d', strtotime($lastNotaCredito['Date']));
$lastNumNotaCredito = $lastNotaCredito['NotaCredito'];
$rimborsi = $this->getRimborsiSenzaNotaCredito($year, $this->session->get('id_shop'));
$N = $lastNumNotaCredito;
foreach($rimborsi as &$r)
{
$date = date('Y-m-d', strtotime($r['Date']));
// Se la data di rimborso è inferiore alla data dell'ultima nota credito, allora c'è un errore!
if($date < $lastDateNotaCredito)
{
$result['status'] = 'ERROR';
$result['message'] = 'Attenzione! L\'Ordine '.$r['Order'].' ha una data di rimborso "'.$date.'" superiore alla data dell\'ultima Nota di Credito '.$lastNumNotaCredito.' (Ordine '.$lastNotaCredito['Order'].')';
$result['rimborsi'][] = $r;
break;
}
if($r['Order'] == '')
{
$result['status'] = 'ERROR';
$result['message'] = 'Attenzione! Il rimborso '.strtoupper($r['Source']).' in data '.$r['Date'].' è associato ad un Ordine che non è stato importato (Payment Reference: '. $r['PaymentReference'] .') per favore importa prima di tutto questo ordine e potrai così assegnare una Nota Credito';
$result['rimborsi'][] = $r;
break;
}
// Controlla se l'ordine è ok
if($result['status'] != 'ERROR')
{
$N++;
$r['NotaCredito'] = $N;
$result['rimborsi'][] = $r;
}
}
}else{
$result['status'] = 'ERROR';
$result['message'] = 'Non esiste una Nota di Credito di partenza per l\'anno '.$year.'. Controllare i rimborsi';
}
}else{
//Modalità non anteprima per conferma numerazione
if($confirm)
{
$rimborsi = json_decode($request->getContent(), true);
if(!empty($rimborsi))
{
$conn = $this->getDoctrine()->getManager()->getConnection();
$result['NoteCredito'] = [];
foreach($rimborsi as $r)
{
$statement = $conn->prepare('UPDATE `incassi` SET `NotaCredito` = '. (int) $r['NotaCredito'].' WHERE `id` = '.(int) $r['id']);
if($statement->execute())
{
$result['status'] = 'OK';
$result['NoteCredito'][] = ['id' => $r['id'], 'NotaCredito' => $r['NotaCredito']];
}
}
if($result['status'] == 'OK')
{
$result['message'] = 'Operazione effettuata con successo. '.count($result['NoteCredito']).' Note Credito assegnate. Aggiorna la tabella per visualizzare la nuova numerazione.';
}
}
}else{
$result['status'] = 'WARNING';
$result['message'] = 'Nessuna conferma all\'operazione. Nulla da eseguire';
}
}
return new JsonResponse($result);
}
/**
* Elimina Incasso
*/
public function deleteIncasso(Request $request, $incassoId)
{
$result = [];
$conn = $this->getDoctrine()->getManager()->getConnection();
$statement = $conn->prepare('DELETE FROM incassi WHERE id = '.(int) $incassoId);
if($res = $statement->execute())
{
$result['status'] = 'OK';
}else{
$result['status'] = 'ERROR';
$result['message'] = 'Incasso non eliminato';
}
return new JsonResponse($result);
}
/**
*
*/
protected function getLastNotaCredito($year, $id_shop)
{
$conn = $this->getDoctrine()->getManager()->getConnection();
$sql = 'SELECT I.id, O.Order, I.`PaymentReference`, I.`Source`, I.`Type`, I.`Date`, I.`NotaCredito`,
IFNULL(O.`Numero Fattura`, "") as "Fattura",
IFNULL(O.`Corrispettivo`, "") as "Corrispettivo"
FROM `incassi` I
JOIN `ordini` O ON (O.`Order` = I.`Order`)
/* LEFT JOIN `ordini` O ON (CASE
-- WHEN I.Order <> "" THEN (I.Order = O.`Order`)
-- WHEN I.PaymentReference <> "" THEN (INSTR(O.`Payment Reference`, I.`PaymentReference`) > 0)
-- ELSE FALSE
-- END = TRUE)*/
WHERE 1 AND YEAR(I.Date) = '.(int) $year.' AND I.`Shop` = ' . $id_shop . ' AND I.Type = "refund"
AND O.`Numero Fattura` <> ""
AND IFNULL(I.`NotaCredito`,"") <> ""
ORDER BY I.Date DESC LIMIT 1';
$statement = $conn->prepare($sql);
if($res = $statement->execute())
{
return $statement->fetch();
}
return [];
}
/**
*
*/
protected function getRimborsiSenzaNotaCredito($year, $id_shop, $count = false)
{
$conn = $this->getDoctrine()->getManager()->getConnection();
$sql = 'SELECT I.id, O.Order, I.`PaymentReference`, I.`Source`, I.`Type`, I.`Date`, I.`NotaCredito`, I.`ChargeAmount`, I.`RefundAmount`,
IFNULL(O.`Numero Fattura`, "") as "Fattura",
IFNULL(O.`Corrispettivo`, "") as "Corrispettivo"
FROM `incassi` I
JOIN `ordini` O ON (O.`Order` = I.`Order`)
/* LEFT JOIN `ordini` O ON (CASE
-- WHEN I.Order <> "" THEN (I.Order = O.`Order`)
-- WHEN I.PaymentReference <> "" THEN (INSTR(O.`Payment Reference`, I.`PaymentReference`) > 0)
-- ELSE FALSE
-- END = TRUE)*/
WHERE 1 AND YEAR(I.Date) = '.(int) $year.' AND I.`Shop` = ' . $id_shop . ' AND I.Type = "refund"
AND IFNULL(O.`Numero Fattura`, "") <> ""
AND IFNULL(I.`NotaCredito`, "") = ""
ORDER BY I.Date ASC';
$statement = $conn->prepare($sql);
if($res = $statement->execute())
{
return $statement->fetchAll();
}
return [];
}
/**
*
*/
public function getConversionRate($fromCurr, $toCurr, $date)
{
$conn = $this->getDoctrine()->getManager()->getConnection();
$statement = $conn->prepare('SELECT rate FROM `conversion_rates` WHERE `currency_from` = "'.$fromCurr.'" AND `currency_to` = "'.$toCurr.'" AND `date` = DATE("'.$date.'")');
if($statement->execute())
{
return $statement->fetch();
}
return NULL;
}
/**
*
*/
public function getTaxRateByCountry($iso_code)
{
$conn = $this->getDoctrine()->getManager()->getConnection();
$statement = $conn->prepare('SELECT `rate` FROM `paesi` WHERE iso_code = "'.$iso_code.'" ');
$statement->execute();
return $statement->fetch();
}
/**
*
*/
public function getProdottiByOrder($OrderID)
{
$conn = $this->getDoctrine()->getManager()->getConnection();
$stmt = $conn->prepare('SELECT OP.*, C.HSCode, C.CountryOfOrigin
FROM ordini_prodotti OP
LEFT JOIN codici_doganali C ON (SUBSTR(OP.`SKU`,1,5) = C.`Codice`)
WHERE OP.`Order` = "'. $OrderID .'"');
if($stmt->execute())
{
$products = $stmt->fetchAll();
if(!empty($products))
{
foreach($products as $key => &$product)
{
$_code = substr($product['SKU'], 0, 5);
$stmt = $conn->prepare('SELECT `HSCode` FROM `codici_doganali` WHERE `Codice` = "'.$_code.'"');
$stmt->execute();
$result = $stmt->fetch();
$product['HSCode'] = $result ? $result['HSCode'] : '';
$_name = explode(' - ', $product['Name']);
if(isset($_name[1])) {
$product['Category'] = $_name[0];
$product['Articolo'] = $_name[1];
}else{
$product['Category'] = '';
$product['Articolo'] = $_name[0];
}
if(isset($_name[2]))
{
$product['CleanName'] = $_name[2];
}
if(isset($_name[3]))
{
$product['Size'] = (int) $_name[3];
}else {
$product['Size'] = '';
}
}
return $products;
}
}
return [];
}
/**
*
*/
public function getProdottiByNotaCredito($NotaCredito)
{
$conn = $this->getDoctrine()->getManager()->getConnection();
$stmt = $conn->prepare('SELECT NCP.SKU, NCP.QuantityRefund, NCP.`PriceRefund`, IFNULL(OP.Name, NCP.Name) as "Name", OP.Price, C.HSCode as "HSCode", C.CountryOfOrigin as "CountryOfOrigin"
FROM notecredito_prodotti NCP
LEFT JOIN ordini_prodotti OP ON (NCP.`Order` = OP.`Order` AND NCP.Shop = OP.Shop AND NCP.SKU = OP.SKU)
LEFT JOIN codici_doganali C ON (SUBSTR(OP.`SKU`,1,5) = C.`Codice`)
WHERE NCP.Shop = '.$NotaCredito['Shop'].' AND NCP.`Order` = "'.$NotaCredito['Order'].'" AND NCP.`NotaCredito` = "'.$NotaCredito['NotaCredito'].'"');
if($stmt->execute())
{
$products = $stmt->fetchAll();
if(!empty($products))
{
foreach($products as &$p)
{
$_name = explode(' - ', $p['Name']);
$p['Category'] = $_name[0];
$p['Articolo'] = isset($_name[1]) ? $_name[1] : '';
if(isset($_name[2]))
{
$p['CleanName'] = $_name[2];
}
if(isset($_name[3]))
{
$p['Size'] = (int) $_name[3];
}else {
$p['Size'] = '';
}
}
}
}
return $products;
}
/**
*
*/
public function getShippingByNotaCredito($NotaCredito)
{
$conn = $this->getDoctrine()->getManager()->getConnection();
$stmt = $conn->prepare('SELECT NCS.*
FROM notecredito_spedizioni NCS
WHERE NCS.Shop = '.$NotaCredito['Shop'].' AND NCS.`Order` = "'.$NotaCredito['Order'].'" AND NCS.`NotaCredito` = "'.$NotaCredito['NotaCredito'].'"');
if($stmt->execute())
{
$shipping = $stmt->fetch();
}
return $shipping;
}
/**
*
*/
public function import_note_credito_prodotti(Request $request, SessionInterface $session, ImporterCSV $importerCSV)
{
$this->init($session);
$params = [];
$params = ['result' => []];
$params['id_shop'] = $this->session->get('id_shop');
$importerCSV->setDb($this->getDoctrine()->getManager());
$importerCSV->setShopId($this->session->get('id_shop'));
if($request->getMethod() == 'POST')
{
$tipoIncasso = $request->request->get('tipo_incasso');
if(isset($_FILES['note-credito-prodotti-file-csv']) && !empty($_FILES['note-credito-prodotti-file-csv']))
{
if($_FILES['note-credito-prodotti-file-csv']['error'] == UPLOAD_ERR_OK)
{
$result = $importerCSV->importNoteCreditoProdottiInDb($_FILES['note-credito-prodotti-file-csv']['tmp_name']);
if($result){
$params['result'] = $result;
}
}
}
}
return $this->render('incassi.import.note_credito_prodotti.html.twig', $params);
}
/**
*
*/
public function deleteImportIncasso(Request $request, SessionInterface $session)
{
$this->init($session);
$conn = $this->getDoctrine()->getManager()->getConnection();
$params = ['result' => []];
$params['id_shop'] = $this->session->get('id_shop');
$id_log_imports = (int) ($request->request->has('id_log_imports') ? $request->request->get('id_log_imports') : NULL);
if($id_log_imports > 0)
{
$conn->prepare('START TRANSACTION')->execute();
$conn->prepare('DELETE FROM `incassi` WHERE `id_log_imports` = '.(int) $id_log_imports)->execute();
$conn->prepare('UPDATE `log_imports` SET `date_delete` = "' . date('Y-m-d H:i:s') . '" WHERE `id` = '.(int) $id_log_imports)->execute();
$conn->prepare('COMMIT')->execute();
return new JsonResponse(['status' => 'OK']);
}
return new JsonResponse(['status' => 'ERROR', 'message' => 'Importazione non valida']);
}
}

src/Controller/IncassiController.php line 38