Symfony Profiler

<?php
namespace App\Controller\Admin;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Session\Session;
use Symfony\Component\Process\Process;
use App\DependencyInjection\ToolsUsers;
class ProductUploadController extends AbstractController {
use \App\Traits\Database;
use \App\Traits\DateUtils;
use \App\Traits\Rights;
public function index(Request $request) {
$session=new Session();
$translate=$this->container->get('site.translate')->get();
$conn=$this->getDoctrine()->getConnection();
if(!$session->get('admin')) return $this->redirect($this->generateUrl('admin_homepage'));
$is_admin=$this->checkAdmin($session, $conn);
if(!$is_admin) return $this->redirect($this->generateUrl('admin_homepage'));
return $this->render('Admin/ProductUpload/index.html.twig', array('is_admin'=>$is_admin, 'translate'=>$translate));
}
public function controlFile(Request $request, $id) {
$session=new Session();
$translate=$this->container->get('site.translate')->get();
$conn=$this->getDoctrine()->getConnection();
if(!$session->get('admin')) return $this->redirect($this->generateUrl('admin_homepage'));
$is_admin=$this->checkAdmin($session, $conn);
if(!$is_admin) return $this->redirect($this->generateUrl('admin_homepage'));
return $this->render('Admin/ProductUpload/controlFile.html.twig', array('id'=>$id, 'is_admin'=>$is_admin, 'translate'=>$translate));
}
public function controlFileList(Request $request, $id) {
$session=$this->get('session');
$translate=$this->container->get('site.translate')->get();
$conn=$this->getDoctrine()->getConnection();
if(!$this->checkConnected($session)) return $this->render('Admin/Default/not-connected-single.html.twig', array('translate'=>$translate));
$is_admin=$this->checkAdmin($session, $conn);
if(!$is_admin) return $this->render('Admin/Default/no-rights-single.html.twig', array('translate'=>$translate));
$where='';$page=1;$limit=10;
if(isset($_GET['page']) && is_numeric($_GET['page']) && $_GET['page']) $page=$_GET['page'];
if(isset($_GET['limit']) && is_numeric($_GET['limit']) && $_GET['limit']) $limit=$_GET['limit'];
$start=($page-1)*$limit;
$dateNow=(new \DateTime())->format('Y-m-d H:i:s');
$count=$this->fetchOne($conn, "SELECT COUNT(DISTINCT pud.ref)
FROM product_upload_data pud
INNER JOIN product_upload_file puf ON puf.id=pud.product_upload_file_id
WHERE puf.product_upload_id=$id $where");
$items=$this->fetchAll($conn, "SELECT pud.ref, 0 qty, MAX(pud.price) price
FROM product_upload_data pud
INNER JOIN product_upload_file puf ON puf.id=pud.product_upload_file_id
WHERE puf.product_upload_id=$id $where
GROUP BY pud.ref
ORDER BY pud.ref ASC
LIMIT $start,$limit");
foreach($items as $k=>$v) {
$qte=$this->fetch($conn, 'SELECT SUM(qty) qty FROM product_upload_data WHERE LOWER(ref)="'.strtolower($v['ref']).'" AND qty>0 AND product_upload_id='.$id);
if($qte) $items[$k]['qty']=$qte['qty'];
}
return $this->render('Admin/ProductUpload/controlFileList.html.twig', array('items'=>$items, 'count'=>$count, 'page'=>$page, 'limit'=>$limit, 'is_admin'=>$is_admin, 'translate'=>$translate));
}
public function controlFileExport(Request $request, $id) {
$session=$this->get('session');
$translate=$this->container->get('site.translate')->get();
$conn=$this->getDoctrine()->getConnection();
if(!$this->checkConnected($session)) return $this->render('Admin/Default/not-connected-single.html.twig', array('translate'=>$translate));
$is_admin=$this->checkAdmin($session, $conn);
if(!$is_admin) return $this->render('Admin/Default/no-rights-single.html.twig', array('translate'=>$translate));
$dateNow=(new \DateTime())->format('Y-m-d H:i:s');
$items=$this->fetchAll($conn, "SELECT LOWER(pud.ref) ref, 0 qty, MAX(pud.price) price
FROM product_upload_data pud
WHERE pud.product_upload_id=$id
AND pud.product_upload_file_id IN (SELECT puf.id FROM product_upload_file puf WHERE puf.status=2 AND puf.id=pud.product_upload_file_id)
GROUP BY pud.ref
ORDER BY pud.ref ASC");
$now = gmdate("D, d M Y H:i:s");
header("Expires: Tue, 03 Jul 2001 06:00:00 GMT");
header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate");
header("Last-Modified: {$now} GMT");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Disposition: attachment; filename="Controle-'.$id.'.csv";');
header("Content-Transfer-Encoding: binary");
$output = fopen("php://output", "w");
fputcsv($output, array('Ref', 'Qte', 'Prix'), ';');
foreach($items as $k=>$v) {
$qte=$this->fetch($conn, 'SELECT SUM(pud.qty) qty
FROM product_upload_data pud
WHERE pud.product_upload_id='.$id.' AND LOWER(pud.ref)="'.strtolower($v['ref']).'" AND pud.qty>0
AND pud.product_upload_file_id IN (SELECT puf.id FROM product_upload_file puf WHERE puf.status=2 AND puf.id=pud.product_upload_file_id)');
if($qte) $v['qty']=$qte['qty'];
fputcsv($output, $v, ';');
}
fclose($output);
die;
}
public function checkCSV(Request $request, $id, $company_id=0) {
$session=new Session();
$translate=$this->container->get('site.translate')->get();
$conn=$this->getDoctrine()->getConnection();
if(!$session->get('admin')) return $this->redirect($this->generateUrl('admin_homepage'));
$is_admin=$this->checkAdmin($session, $conn);
if(!$is_admin) return $this->redirect($this->generateUrl('admin_homepage'));
return $this->render('Admin/ProductUpload/checkCSV.html.twig', array('id'=>$id, 'company_id'=>$company_id, 'is_admin'=>$is_admin, 'translate'=>$translate));
}
public function checkCSVList(Request $request, $id, $company_id=0) {
$session=$this->get('session');
$translate=$this->container->get('site.translate')->get();
$conn=$this->getDoctrine()->getConnection();
if(!$this->checkConnected($session)) return $this->render('Admin/Default/not-connected-single.html.twig', array('translate'=>$translate));
$is_admin=$this->checkAdmin($session, $conn);
if(!$is_admin) return $this->render('Admin/Default/no-rights-single.html.twig', array('translate'=>$translate));
$where='';$page=1;$limit=10;
if(isset($_GET['page']) && is_numeric($_GET['page']) && $_GET['page']) $page=$_GET['page'];
if(isset($_GET['limit']) && is_numeric($_GET['limit']) && $_GET['limit']) $limit=$_GET['limit'];
if($company_id) $where.=' AND puf.company_id='.$company_id;
$start=($page-1)*$limit;
$dateNow=(new \DateTime())->format('Y-m-d H:i:s');
$count=$this->fetchOne($conn, "SELECT COUNT(pud.product_upload_file_id)
FROM product_upload_data pud
INNER JOIN product_upload_file puf ON puf.id=pud.product_upload_file_id
INNER JOIN company c ON c.id=puf.company_id
WHERE puf.product_upload_id=$id $where");
$items=$this->fetchAll($conn, "SELECT puf.*, pud.ref, pud.qty, pud.price, c.name company_name
FROM product_upload_data pud
INNER JOIN product_upload_file puf ON puf.id=pud.product_upload_file_id
INNER JOIN company c ON c.id=puf.company_id
WHERE puf.product_upload_id=$id $where
ORDER BY pud.ref ASC
LIMIT $start,$limit");
return $this->render('Admin/ProductUpload/checkCSVList.html.twig', array('items'=>$items, 'count'=>$count, 'page'=>$page, 'limit'=>$limit, 'is_admin'=>$is_admin, 'translate'=>$translate));
}
public function checkCSVExport(Request $request, $id, $company_id=0) {
$session=$this->get('session');
$translate=$this->container->get('site.translate')->get();
$conn=$this->getDoctrine()->getConnection();
if(!$this->checkConnected($session)) return $this->render('Admin/Default/not-connected-single.html.twig', array('translate'=>$translate));
$is_admin=$this->checkAdmin($session, $conn);
if(!$is_admin) return $this->render('Admin/Default/no-rights-single.html.twig', array('translate'=>$translate));
$where='';
if($company_id) $where.=' AND puf.company_id='.$company_id;
$dateNow=(new \DateTime())->format('Y-m-d H:i:s');
$items=$this->fetchAll($conn, "SELECT c.name company_name, pud.ref, pud.qty, pud.price
FROM product_upload_data pud
INNER JOIN product_upload_file puf ON puf.id=pud.product_upload_file_id
INNER JOIN company c ON c.id=puf.company_id
WHERE product_upload_id=$id $where
ORDER BY pud.ref ASC");
$now = gmdate("D, d M Y H:i:s");
header("Expires: Tue, 03 Jul 2001 06:00:00 GMT");
header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate");
header("Last-Modified: {$now} GMT");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
if(!$company_id) header('Content-Disposition: attachment; filename="'.$id.'.csv";');
else header('Content-Disposition: attachment; filename="'.$id.'-'.$company_id.'.csv";');
header("Content-Transfer-Encoding: binary");
$output = fopen("php://output", "w");
fputcsv($output, array('Libraire', 'Ref', 'Qte', 'Prix'), ';');
foreach($items as $v) {
fputcsv($output, $v, ';');
}
fclose($output);
die;
}
public function one(Request $request, $id) {
$session=$this->get('session');
$translate=$this->container->get('site.translate')->get();
$conn=$this->getDoctrine()->getConnection();
if(!$this->checkConnected($session)) return $this->render('Admin/Default/not-connected-single.html.twig', array('translate'=>$translate));
$is_admin=$this->checkAdmin($session, $conn);
if($is_admin!=2) return $this->render('Admin/Default/no-rights-single.html.twig', array('translate'=>$translate));
$item=$this->fetch($conn, "SELECT pu.*
FROM product_upload pu
WHERE pu.id=$id");
//$item['companies']=$this->fetchAll($conn, "SELECT * FROM company WHERE id IN (SELECT company_id FROM staff WHERE id=$id)");
return $this->render('Admin/ProductUpload/one.html.twig', array('item'=>$item, 'translate'=>$translate));
}
public function list(Request $request) {
$session=$this->get('session');
$translate=$this->container->get('site.translate')->get();
$conn=$this->getDoctrine()->getConnection();
if(!$this->checkConnected($session)) return $this->render('Admin/Default/not-connected-single.html.twig', array('translate'=>$translate));
$is_admin=$this->checkAdmin($session, $conn);
if(!$is_admin) return $this->render('Admin/Default/no-rights-single.html.twig', array('translate'=>$translate));
$where='';$page=1;$limit=10;
if(isset($_GET['status']) && is_numeric($_GET['status']) && $_GET['status']) $where.=' AND pu.status="'.$_GET['status'].'"';
if(isset($_GET['page']) && is_numeric($_GET['page']) && $_GET['page']) $page=$_GET['page'];
if(isset($_GET['limit']) && is_numeric($_GET['limit']) && $_GET['limit']) $limit=$_GET['limit'];
$start=($page-1)*$limit;
$dateNow=(new \DateTime())->format('Y-m-d H:i:s');
if($is_admin==2) {
$count=$this->fetchOne($conn, "SELECT COUNT(DISTINCT pu.id)
FROM product_upload pu
WHERE 1=1 $where");
$items=$this->fetchAll($conn, "SELECT pu.*
FROM product_upload pu
WHERE 1=1 $where
ORDER BY pu.creation_date DESC
LIMIT $start,$limit"); //pu.start_date DESC, pu.end_date DESC,
}
elseif($is_admin==1) {
$count=$this->fetchOne($conn, "SELECT COUNT(DISTINCT pu.id)
FROM product_upload pu
WHERE pu.start_date<='$dateNow' AND (pu.end_date IS NULL OR pu.end_date>='$dateNow') $where");
$items=$this->fetchAll($conn, "SELECT pu.*
FROM product_upload pu
WHERE pu.start_date<='$dateNow' AND (pu.end_date IS NULL OR pu.end_date>='$dateNow') $where
ORDER BY pu.creation_date DESC
LIMIT $start,$limit");//pu.start_date DESC, pu.end_date DESC
}
$companies=$this->getCompanies($session, $conn);
foreach($items as $k=>$v) {
foreach($companies as $k2=>$v2) {
$exists=$this->fetchOne($conn, 'SELECT count(id) FROM product_upload_file WHERE status=2 AND product_upload_id="'.$v['id'].'" AND company_id="'.$v2['id'].'"');
if($exists) {
$companies[$k2]['msg']=1;
$exists=$this->fetchOne($conn, 'SELECT count(id) FROM product_upload_file WHERE status=2 AND end_script_date IS NOT NULL AND product_upload_id="'.$v['id'].'" AND company_id="'.$v2['id'].'"');
if($exists) $companies[$k2]['msg']=2;
}
else $companies[$k2]['msg']=0;
}
if(!$v['end_date'] || (new \DateTime($v['end_date'])>(new \DateTime()))) $items[$k]['showCSV']=1;
else $items[$k]['showCSV']="0";
$items[$k]['companies']=$companies;
}
return $this->render('Admin/ProductUpload/list.html.twig', array('items'=>$items, 'count'=>$count, 'page'=>$page, 'limit'=>$limit, 'is_admin'=>$is_admin, 'translate'=>$translate));
}
public function add(Request $request) {
$session=$this->get('session');
$translate=$this->container->get('site.translate')->get();
$conn=$this->getDoctrine()->getConnection();
if(!$this->checkConnected($session)) return $this->render('Admin/Default/not-connected-single.html.twig', array('translate'=>$translate));
if($this->checkAdmin($session, $conn)!=2) return $this->render('Admin/Default/no-rights-single.html.twig', array('translate'=>$translate));
$genders=$this->fetchAll($conn, 'SELECT * FROM gender');
if(isset($_POST['ok'])) {
$errors=array();$message='';
$name=addslashes(trim($_POST['name']));
$start_date=$this->toDateHourSQL(trim($_POST['start_date']));
$end_date=$this->toDateHourSQL(trim($_POST['end_date']));
if(strlen($name)<2) $errors['name']='Veuillez entrer une désignation correcte (min. 2cars)';
if(!$start_date) $errors['start_date']='Veuillez entrer une date de début';
if(!$end_date) $errors['end_date']='Veuillez entrer une date de fin';
if(count($errors)>0 || $message) return new JsonResponse(array('s'=>0, 'e'=>$errors, 'm'=>$message));
try {
$dateNow=(new \DateTime())->format('Y-m-d H:i:s');
$this->query($conn, 'START TRANSACTION');
$this->query($conn, 'INSERT INTO product_upload (name, start_date, end_date, creation_date) VALUES
("'.$name.'", "'.$start_date.'", "'.$end_date.'", "'.$dateNow.'")');
$this->query($conn, 'COMMIT');
} catch(\Exception $e) {
$this->query($conn, 'ROLLBACK');
return new JsonResponse(array('s'=>0, 'e'=>$errors, 'm'=>'Erreur de transaction'));
}
return new JsonResponse(array('s'=>1));
}
return $this->render('Admin/ProductUpload/add.html.twig', array('genders'=>$genders, 'translate'=>$translate));
}
public function edit(Request $request, $id) {
$session=$this->get('session');
$translate=$this->container->get('site.translate')->get();
$conn=$this->getDoctrine()->getConnection();
if(!$this->checkConnected($session)) return $this->render('Admin/Default/not-connected-single.html.twig', array('translate'=>$translate));
if($this->checkAdmin($session, $conn)!=2) return $this->render('Admin/Default/no-rights-single.html.twig', array('translate'=>$translate));
$item=$this->fetch($conn, 'SELECT * FROM product_upload WHERE id="'.$id.'"');
if(isset($_POST['ok'])) {
$errors=array();$message='';
$name=addslashes(trim($_POST['name']));
$start_date=$this->toDateHourSQL(trim($_POST['start_date']));
$end_date=$this->toDateHourSQL(trim($_POST['end_date']));
if(strlen($name)<2) $errors['name']='Veuillez entrer une désignation correcte (min. 2cars)';
if(!$start_date) $errors['start_date']='Veuillez entrer une date de début';
if(!$end_date) $errors['end_date']='Veuillez entrer une date de fin';
if(count($errors)>0 || $message) return new JsonResponse(array('s'=>0, 'e'=>$errors, 'm'=>$message));
try {
$this->query($conn, 'START TRANSACTION');
$this->query($conn, 'UPDATE product_upload SET name="'.$name.'", start_date="'.$start_date.'", end_date="'.$end_date.'" WHERE id="'.$id.'"');
$this->query($conn, 'COMMIT');
} catch(\Exception $e) {
$this->query($conn, 'ROLLBACK');
return new JsonResponse(array('s'=>0, 'e'=>$errors, 'm'=>'Erreur de transaction'));
}
return new JsonResponse(array('s'=>1));
}
return $this->render('Admin/ProductUpload/edit.html.twig', array('item'=>$item, 'translate'=>$translate));
}
private function inCompany($id, $companies) {
foreach($companies as $v) {
if($v['id']==$id) return true;
}
return false;
}
public function uploadCSV(Request $request, $id) {
$session=$this->get('session');
$translate=$this->container->get('site.translate')->get();
$conn=$this->getDoctrine()->getConnection();
if(!$this->checkConnected($session)) return $this->render('Admin/Default/not-connected-single.html.twig', array('translate'=>$translate));
if(!$this->checkAdmin($session, $conn)) return $this->render('Admin/Default/no-rights-single.html.twig', array('translate'=>$translate));
$companies=$this->getCompanies($session, $conn);
if(count($companies)==0) return $this->render('Admin/Default/no-rights-single.html.twig', array('translate'=>$translate));
$item=$this->fetch($conn, 'SELECT * FROM product_upload WHERE id="'.$id.'"');
foreach($companies as $k2=>$v2) {
$exists=$this->fetchOne($conn, 'SELECT count(id) FROM product_upload_file WHERE status=2 AND product_upload_id="'.$item['id'].'" AND company_id="'.$v2['id'].'"');
if($exists) {
$companies[$k2]['msg']=1;
$exists=$this->fetchOne($conn, 'SELECT count(id) FROM product_upload_file WHERE status=2 AND end_script_date IS NOT NULL AND product_upload_id="'.$item['id'].'" AND company_id="'.$v2['id'].'"');
if($exists) $companies[$k2]['msg']=2;
}
else $companies[$k2]['msg']=0;
}
if(isset($_POST['ok'])) {
$errors=array();$message='';
$company_id=$_POST['company_id'];
$uploadCode=$_POST['uploadCode'];
if(!is_numeric($company_id) || !$company_id || !$this->inCompany($company_id, $companies)) $errors['company_id']='Vous n\'avez pas de droits suffisants pour importer ce fichier';
if(!is_numeric($uploadCode) || !$uploadCode) $message='Erreur';
if(count($errors)>0 || $message) return new JsonResponse(array('s'=>0, 'e'=>$errors, 'm'=>$message));
try {
$dateNow=(new \DateTime())->format('Y-m-d H:i:s');
$this->query($conn, 'START TRANSACTION');
$this->query($conn, 'UPDATE product_upload_file SET status=5 WHERE product_upload_id="'.$id.'" AND company_id="'.$company_id.'"');
$this->query($conn, 'UPDATE product_upload_file SET company_id="'.$company_id.'", status=2 WHERE id="'.$uploadCode.'" AND user_id="'.$session->get('admin').'"');
$this->query($conn, 'COMMIT');
exec('nohup php /var/www/stock.mylibrairie.ma/web/app/public/uploadCSV.php '.$uploadCode.' '.$session->get('admin').' > /dev/null 2>&1 &');
} catch(\Exception $e) {
$this->query($conn, 'ROLLBACK');
return new JsonResponse(array('s'=>0, 'e'=>$errors, 'm'=>'Erreur de transaction'));
}
return new JsonResponse(array('s'=>1));
}
return $this->render('Admin/ProductUpload/uploadCSV.html.twig', array('item'=>$item, 'user_id'=>$session->get('admin'), 'password'=>$session->get('admin'), 'companies'=>$companies, 'translate'=>$translate));
}
public function activate(Request $request, $id, $status) {
$session=$this->get('session');
$conn=$this->getDoctrine()->getConnection();
$translate=$this->container->get('site.translate')->get();
if(!$this->checkConnected($session)) return new JsonResponse(array('s'=>"0"));
if($this->checkAdmin($session, $conn)!=2) return new JsonResponse(array('s'=>"0"));
$users=$this->query($conn, 'UPDATE product_upload SET status="'.$status.'" WHERE id="'.$id.'"');
return new JsonResponse(array('s'=>1));
}
}
?>

src/Controller/Admin/ProductUploadController.php line 188