Symfony Profiler

<?php
namespace App\Controller;
use App\Entity\EventLog;
use App\Entity\Invoice;
use App\Entity\InvoiceItem;
use App\Entity\Organization;
use App\Entity\Parameter;
use App\Entity\Product;
use App\Entity\Purchase;
use App\Entity\PurchaseItem;
use App\Entity\Salesman;
use App\Entity\StatusType;
use App\Entity\Preinvoice;
use App\Entity\User;
use App\Repository\ParameterRepository;
use DateInterval;
use DateTime;
use Doctrine\ORM\EntityManagerInterface;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\HttpFoundation\ResponseHeaderBag;
use Symfony\Component\HttpFoundation\StreamedResponse;
use Symfony\Component\Routing\Annotation\Route;
class DashController extends AbstractController
{
private $entityManager;
public function __construct( EntityManagerInterface $entityManager )
{
$this->entityManager = $entityManager;
}
/**
* @Route("/", name="app_dash")
*/
public function index(Request $request): Response
{
$this->denyAccessUnlessGranted('IS_AUTHENTICATED_FULLY');
$user = $this->getUser();
if( $user->isActive() == 0){
$this->addFlash(
'error',
'Su cuenta fue desactivada'
);
return $this->redirectToRoute('app_login');
}
if ($user->getIsSalesman()){
return $this->redirectToRoute('app_commissions');
}
return $this->redirectToRoute('app_start');
}
/**
* @Route("/start", name="app_start")
*/
public function start(Request $request): Response
{
$entityManager = $this->entityManager;
$countOrganization = $entityManager->getRepository(Organization::class)->findBy(['activeInd'=>1]);
$today = new DateTime();
$countPurchase = $entityManager->getRepository(Purchase::class)->findBy(['payInd'=>1,'activeInd'=>1]);
$countPendingPurchase = $entityManager->getRepository(Purchase::class)->findBy(['payInd'=>0,'activeInd'=>1]);
// Obtener el conteo de facturas pagadas con el statusType 4
$countPaidInvoices = $entityManager->getRepository(Invoice::class)->countPaidInvoices(4);
// Obtener el conteo de facturas pagadas con el statusType 2
$countPendingInvoices = $entityManager->getRepository(Invoice::class)->countPaidInvoices(2);
return $this->render('start.html.twig', [
'countPaidInvoicesPurchase' => count($countPurchase)+$countPaidInvoices,
'countPendingInvoicesPurchase'=>count($countPendingPurchase) +$countPendingInvoices,
'purchaseCount'=> count($countPurchase) + count($countPendingPurchase),
'invoiceCount'=>$countPaidInvoices + $countPendingInvoices,
'countOrganization'=>count($countOrganization),
'mountAnio' => $today->format('m-Y'),
'anio' => $today->format('Y')
]);
}
/**
* @Route("/purchases", name="app_purchases")
*/
public function purchases(Request $request): Response
{
$user = $this->getUser();
return $this->render('purchases.html.twig');
}
/**
* @Route("/sales", name="app_sales")
*/
public function sales(Request $request): Response
{
$user = $this->getUser();
return $this->render('sales.html.twig');
}
/**
* @Route("/orders", name="app_orders")
*/
public function orders(Request $request): Response
{
// $preinvoice = $this->getDoctrine()
// ->getRepository(Preinvoice::class)
// ->findAll();
$user = $this->getUser();
return $this->render('ordenes.html.twig');
}
/**
* @Route("/commissions", name="app_commissions")
*/
public function commissions(Request $request): Response
{
$user = $this->getUser();
return $this->render('commissions.html.twig');
}
/**
* @Route("/reporting", name="app_reporting")
*/
public function reporting(Request $request): Response
{
$entityManager = $this->getDoctrine()->getManager();
$salesmen = $entityManager->getRepository(Salesman::class)->findBy(['activeInd' => 1]);
$products = $entityManager->getRepository(Product::class)->findBy(['activeInd' => 1]);
$providers = $entityManager->getRepository(Organization::class)->findBy(['activeInd' => 1, 'organizationType' => 1]);
$clients = $entityManager->getRepository(Organization::class)->findBy(['activeInd' => 1, 'organizationType' => 2]);
$statusTypes = $entityManager->getRepository(StatusType::class)->findAll();
return $this->render('reporting.html.twig', [
'salesmen' => $salesmen,
'products' => $products,
'providers' => $providers,
'clients' => $clients,
'statusTypes'=>$statusTypes
]);
}
/**
* @Route("/reporting/data", name="app_reporting_data", methods={"POST"})
*/
public function getReportingData(Request $request): JsonResponse
{
$entityManager = $this->getDoctrine()->getManager();
$filters = json_decode($request->getContent(), true);
$invoices = $entityManager->getRepository(Invoice::class)->findByFilters($filters);
$purchases = $entityManager->getRepository(Purchase::class)->findByFilters($filters);
$data = $this->calculateData($invoices, $purchases, $entityManager);
return new JsonResponse($data);
}
private function calculateData($invoices, $purchases, $entityManager)
{
$salesTotal = 0;
$purchasesTotal = 0;
$commissionsTotal = 0;
$invoiceItemRepository = $entityManager->getRepository(InvoiceItem::class);
$numSalesInvoices = count($invoices);
$numPurchaseInvoices = count($purchases);
$numCommissionInvoices = 0;
$monthlySales = [];
$monthlyPurchases = [];
$monthlyCommissions = [];
$numSalesInvoicesPerMonth = [];
$numPurchaseInvoicesPerMonth = [];
$numCommissionInvoicesPerMonth = [];
$months = [];
$dailySales = [];
$dailyPurchases = [];
$dailyCommissions = [];
$numSalesInvoicesPerDay = [];
$numPurchaseInvoicesPerDay = [];
$numCommissionInvoicesPerDay = [];
$days = [];
foreach ($invoices as $invoice) {
$salesTotal += $invoice->getInvoiceAmount();
$month = $invoice->getInvoiceDate()->format('m-Y');
$day = $invoice->getInvoiceDate()->format('d-m-Y');
if (!in_array($month, $months)) {
$months[] = $month;
$monthlySales[$month] = 0;
$monthlyPurchases[$month] = 0;
$monthlyCommissions[$month] = 0;
$numSalesInvoicesPerMonth[$month] = 0;
$numPurchaseInvoicesPerMonth[$month] = 0;
$numCommissionInvoicesPerMonth[$month] = 0;
}
if (!in_array($day, $days)) {
$days[] = $day;
$dailySales[$day] = 0;
$dailyPurchases[$day] = 0;
$dailyCommissions[$day] = 0;
$numSalesInvoicesPerDay[$day] = 0;
$numPurchaseInvoicesPerDay[$day] = 0;
$numCommissionInvoicesPerDay[$day] = 0;
}
$monthlySales[$month] += $invoice->getInvoiceAmount();
$numSalesInvoicesPerMonth[$month]++;
$dailySales[$day] += $invoice->getInvoiceAmount();
$numSalesInvoicesPerDay[$day]++;
$invoiceItems = $invoiceItemRepository->findBy(['invoice' => $invoice->getId()]);
$hasCommission = false;
foreach ($invoiceItems as $item) {
$commission = $item->getCommissionAmount();
if ($commission !== null && $commission > 0) {
$hasCommission = true;
$commissionsTotal += $commission;
$monthlyCommissions[$month] += $commission;
$dailyCommissions[$day] += $commission;
}
}
if ($hasCommission) {
$numCommissionInvoices++;
$numCommissionInvoicesPerMonth[$month]++;
$numCommissionInvoicesPerDay[$day]++;
}
}
foreach ($purchases as $purchase) {
$purchasesTotal += $purchase->getPurchaseAmount();
$month = $purchase->getPurchaseDate()->format('m-Y');
$day = $purchase->getPurchaseDate()->format('d-m-Y');
if (!isset($monthlyPurchases[$month])) {
if (!in_array($month, $months)) {
$months[] = $month;
}
$monthlySales[$month] = 0;
$monthlyPurchases[$month] = 0;
$monthlyCommissions[$month] = 0;
$numSalesInvoicesPerMonth[$month] = 0;
$numPurchaseInvoicesPerMonth[$month] = 0;
$numCommissionInvoicesPerMonth[$month] = 0;
}
if (!isset($dailyPurchases[$day])) {
if (!in_array($day, $days)) {
$days[] = $day;
}
$dailySales[$day] = 0;
$dailyPurchases[$day] = 0;
$dailyCommissions[$day] = 0;
$numSalesInvoicesPerDay[$day] = 0;
$numPurchaseInvoicesPerDay[$day] = 0;
$numCommissionInvoicesPerDay[$day] = 0;
}
$monthlyPurchases[$month] += $purchase->getPurchaseAmount();
$numPurchaseInvoicesPerMonth[$month]++;
$dailyPurchases[$day] += $purchase->getPurchaseAmount();
$numPurchaseInvoicesPerDay[$day]++;
}
sort($months);
sort($days);
return [
'sales' => $salesTotal,
'purchases' => $purchasesTotal,
'commissions' => $commissionsTotal,
'numSalesInvoices' => $numSalesInvoices,
'numPurchaseInvoices' => $numPurchaseInvoices,
'numCommissionInvoices' => $numCommissionInvoices,
'months' => $months,
'monthlySales' => array_values($monthlySales),
'monthlyPurchases' => array_values($monthlyPurchases),
'monthlyCommissions' => array_values($monthlyCommissions),
'numSalesInvoicesPerMonth' => $numSalesInvoicesPerMonth,
'numPurchaseInvoicesPerMonth' => $numPurchaseInvoicesPerMonth,
'numCommissionInvoicesPerMonth' => $numCommissionInvoicesPerMonth,
'days' => $days,
'dailySales' => array_values($dailySales),
'dailyPurchases' => array_values($dailyPurchases),
'dailyCommissions' => array_values($dailyCommissions),
'numSalesInvoicesPerDay' => $numSalesInvoicesPerDay,
'numPurchaseInvoicesPerDay' => $numPurchaseInvoicesPerDay,
'numCommissionInvoicesPerDay' => $numCommissionInvoicesPerDay
];
}
/**
* @Route("/api/dollar/check-update", name="check_dollar_update", methods={"GET"})
*/
public function checkAndUpdateDollarPrice(ParameterRepository $repository): JsonResponse
{
$parameter = $repository->findOneBy(['parameterTxtVal'=>'Dolar']);
$today = new \DateTime();
if (!$parameter || !$parameter->getLastUpdated() || $parameter->getLastUpdated()->format('Y-m-d') != $today->format('Y-m-d')) {
// Llamar a la API externa y actualizar la base de datos
$apiUrl = 'https://mindicador.cl/api';
$data = json_decode(file_get_contents($apiUrl), true);
$newDollarPrice = $data['dolar']['valor'];
$newDollarPriceFormatted = number_format($newDollarPrice, 2, '.', '');
if ($parameter) {
$parameter->setParameterNumVal($newDollarPriceFormatted);
$parameter->setLastUpdated($today);
} else {
// Crear nueva instancia si no existe
$parameter = new Parameter();
$parameter->setParameterTxtVal('Dolar');
$parameter->setParameterNumVal($newDollarPriceFormatted);
$parameter->setLastUpdated($today);
$repository->add($parameter, true);
}
$this->getDoctrine()->getManager()->flush();
return new JsonResponse(['status' => 'updated', 'dollarPrice' => $newDollarPriceFormatted]);
}
return new JsonResponse(['status' => 'no update needed', 'dollarPrice' => $parameter->getParameterNumVal()]);
}
/**
* @Route("/api/iva/check-update", name="check_iva_update", methods={"GET"})
*/
public function checkAndUpdateIva(ParameterRepository $repository): JsonResponse
{
$parameter = $repository->findOneBy(['parameterTxtVal'=>'IVA']);
return new JsonResponse(['status' => 'no update needed', 'iva' => $parameter->getParameterNumVal()]);
}
/**
* @Route("/get-items-timeline", name="get_items_timeline", methods={"GET"})
*/
public function getItemsTimeline(): JsonResponse
{
$em = $this->entityManager;
$eventLog = $em->getRepository(EventLog::class)->eventLogToday();
$itemsTimeline= [];
foreach($eventLog as $item){
$itemsTimeline[] = [
'time' => $item->getCreatedAt()->format('H:i'),
'badgeClass' => $item->getTypeEventLog()->getCssColor(),
'content' => $item->getDescription()
];
}
// Función de comparación para ordenar por la hora en orden descendente
usort($itemsTimeline, function($a, $b) {
return strtotime($b['time']) - strtotime($a['time']);
});
return $this->json($itemsTimeline);
}
/**
* @Route("/ajax/get-data", name="ajax_get_data")
*/
public function getData(): JsonResponse
{
$em = $this->entityManager;
$salesmen = $em->getRepository(Salesman::class)->findBy(['activeInd' => 1]);
// Obtener la meta global desde la tabla Parameter
$parameter = $em->getRepository(Parameter::class)->findOneBy(['parameterTxtVal' => 'Meta Vendedores']);
$globalTarget = $parameter->getParameterNumVal();
$progressData = [];
foreach ($salesmen as $salesman) {
// Obtener el último día del mes actual
$lastDayOfMonth = new DateTime('last day of this month');
$targetDate = $lastDayOfMonth;
// Fecha actual
$today = new DateTime();
// Obtener la cantidad de ventas del vendedor para el mes actual
$startDate = new DateTime('first day of this month');
//$countSales = $em->getRepository(Invoice::class)->count(['salesman' => $salesman]);
$countSales = $em->getRepository(Invoice::class)->getTotalInvoiceAmountForCurrentMonth($salesman->getId());
// Calcular la diferencia en días entre la fecha actual y la fecha meta
$interval = $today->diff($targetDate);
$daysLeft = $interval->days;
// Calcular el porcentaje completado del mes actual
$totalDaysInMonth = (int)$startDate->format('t'); // Total de días en el mes actual
$percentage = (($totalDaysInMonth - $daysLeft) / $totalDaysInMonth) * 100;
// Calcular el porcentaje de ventas alcanzado en relación con la meta global
$salesPercentage = ($countSales / $globalTarget) * 100;
// Crear la barra de progreso
$progressData[] = [
'name' => $salesman->getSalesmanName(),
'percentage' => $percentage,
'daysLeft' => $daysLeft,
'salesCount' => $countSales,
'salesPercentage' => $salesPercentage, // Porcentaje de ventas alcanzado
'globalTarget' => $globalTarget, // Meta global
'status' => 'orange', // Aquí podrías calcular el color según el porcentaje
];
}
return new JsonResponse($progressData);
}
/**
* @Route("/certificationsChart", name="certifications_chart", methods={"GET"})
*/
public function certificationsChart(): JsonResponse
{
setlocale(LC_TIME, 'es_ES.UTF-8', 'Spanish_Spain.1252');
$months = [];
$today = new \DateTime(); // Fecha actual
for ($i = 5; $i >= 0; $i--) {
$date = clone $today; // Clonamos la fecha de hoy para no modificarla
$date->modify("-$i months"); // Restamos meses
$months[] = $date;
}
$salesData = [];
$purchaseData = [];
$salesAmountData = [];
$purchaseAmountData = [];
foreach ($months as $month) {
$startDate = $month->format('Y-m-01');
$endDate = $month->format('Y-m-t');
// Obtener ventas del mes
$sales = $this->entityManager->getRepository(Invoice::class)
->countInvoicesByMonth($startDate, $endDate);
// Obtener compras del mes
$purchases = $this->entityManager->getRepository(Purchase::class)->countPurchasesByMonth($startDate, $endDate);
$salesData[] = (int)$sales['invoiceCount'];
$purchaseData[] = (int)$purchases['purchaseCount'];
$salesAmountData[] = (float)$sales['totalAmount'];
$purchaseAmountData[] = (float)$purchases['totalAmount'];
}
$spanishMonths = array_map(function($month) {
return ucwords(strftime('%B', $month->getTimestamp())); // '%B' devuelve el nombre completo del mes
}, $months);
$data = [
'categories' => $spanishMonths,
'series' => [
['name' => 'Compras', 'data' => $purchaseData],
['name' => 'Ventas', 'data' => $salesData],
],
'amounts' => [
'sales' => $salesAmountData,
'purchases' => $purchaseAmountData,
]
];
return new JsonResponse($data);
}
/**
* @Route("/reporting/export/excel", name="app_reporting_export_excel", methods={"POST"})
*/
public function exportToExcel(Request $request, EntityManagerInterface $entityManager)
{
// Obtén los filtros desde la solicitud
$filters = json_decode($request->getContent(), true);
// Obtén los datos de ventas y compras
$invoices = $entityManager->getRepository(Invoice::class)->findByFilters($filters);
$purchases = $entityManager->getRepository(Purchase::class)->findByFilters($filters);
// Crea un nuevo archivo Excel
$spreadsheet = new Spreadsheet();
// Crear la hoja para Ventas
$sheetVentas = $spreadsheet->setActiveSheetIndex(0);
$sheetVentas->setTitle('Ventas');
$this->populateSheetWithData($sheetVentas, $invoices, 'Ventas', false, $entityManager);
// Crear la hoja para Compras
$sheetCompras = $spreadsheet->createSheet();
$sheetCompras->setTitle('Compras');
$this->populateSheetWithData($sheetCompras, $purchases, 'Compras', false, $entityManager);
// Crear la hoja para Comisiones
$sheetComisiones = $spreadsheet->createSheet();
$sheetComisiones->setTitle('Comisiones');
$this->populateSheetWithData($sheetComisiones, $invoices, 'Comisiones', true, $entityManager);
// Configura el archivo de salida
$writer = new Xlsx($spreadsheet);
$filename = 'Reporte_' . date('Y-m-d') . '.xlsx';
// Genera la respuesta para descargar el archivo
$response = new Response();
$response->headers->set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$response->headers->set('Content-Disposition', 'attachment;filename="' . $filename . '"');
$response->headers->set('Cache-Control', 'max-age=0');
ob_start();
$writer->save('php://output');
$excelOutput = ob_get_clean();
$response->setContent($excelOutput);
return $response;
}
/**
* Popula una hoja de Excel con los datos proporcionados.
*
* @param \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $sheet
* @param array $data
* @param string $type
* @param bool $isCommission
* @param EntityManagerInterface $entityManager
*/
private function populateSheetWithData($sheet, $data, $type, $isCommission, EntityManagerInterface $entityManager)
{
// Configurar encabezados de la hoja
$sheet->setCellValue('A1', 'N factura');
$sheet->setCellValue('B1', 'Fecha');
$sheet->setCellValue('C1', 'Monto Total');
$sheet->setCellValue('D1', 'Producto');
$sheet->setCellValue('E1', 'Cantidad');
$sheet->setCellValue('F1', 'Precio');
if ($type === 'Ventas' || $type === 'Comisiones') {
$sheet->setCellValue('G1', 'Utilidad Neta');
$sheet->setCellValue('H1', 'Margen Bruto (%)');
$sheet->setCellValue('I1', 'Comisión');
$sheet->setCellValue('J1', 'Vendedor');
}
$row = 2;
// Procesar los datos
foreach ($data as $item) {
if ($type === 'Ventas' || $type === 'Comisiones') {
$sheet->setCellValue('A' . $row, $item->getInvoiceHostnum());
$sheet->setCellValue('B' . $row, $item->getInvoiceDate()->format('d-m-Y'));
$sheet->setCellValue('C' . $row, $item->getInvoiceAmount());
$invoiceItems = $entityManager->getRepository(InvoiceItem::class)->findBy(['invoice' => $item->getId()]);
foreach ($invoiceItems as $invoiceItem) {
$sheet->setCellValue('D' . $row, $invoiceItem->getProduct()->getProductName());
$sheet->setCellValue('E' . $row, $invoiceItem->getQuantityProduct());
$sheet->setCellValue('F' . $row, $invoiceItem->getTotalAmount());
$sheet->setCellValue('G' . $row, $invoiceItem->getNetUtility());
$sheet->setCellValue('H' . $row, $invoiceItem->getGrossMarginPct() . '%');
$sheet->setCellValue('I' . $row, $invoiceItem->getCommissionAmount());
// Agregar vendedor si es ventas o comisiones
if ($item->getSalesman()) {
$sheet->setCellValue('J' . $row, $item->getSalesman()->getSalesmanName());
} else {
$sheet->setCellValue('J' . $row, 'Sin vendedor');
}
$row++;
}
} elseif ($type === 'Compras') {
$sheet->setCellValue('A' . $row, $item->getInvoicePurchaseNum());
$sheet->setCellValue('B' . $row, $item->getPurchaseDate()->format('d-m-Y'));
$sheet->setCellValue('C' . $row, $item->getPurchaseAmount());
$purchaseItems = $entityManager->getRepository(PurchaseItem::class)->findBy(['purchase' => $item->getId()]);
foreach ($purchaseItems as $purchaseItem) {
$sheet->setCellValue('D' . $row, $purchaseItem->getProduct()->getProductName());
$sheet->setCellValue('E' . $row, $purchaseItem->getQuantityProduct());
$sheet->setCellValue('F' . $row, $purchaseItem->getTotalAmount());
$row++;
}
}
}
// Estilo de las celdas (alineación a la derecha y bordes)
$sheet->getStyle('A1:J' . ($row - 1))->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
// Aplicar bordes
$styleArray = [
'borders' => [
'allBorders' => [
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
'color' => ['argb' => '000000'],
],
],
];
$sheet->getStyle('A1:J' . ($row - 1))->applyFromArray($styleArray);
// Aplicar formato de tabla
$sheet->setAutoFilter('A1:J1');
}
}

src/Controller/DashController.php line 45