Symfony Profiler

<?php
/**
* Created by Mediterranean Develup Solutions
* User: jorge.defreitas@develup.solutions
* Date: 19/01/2018
* Time: 11:27
*/
namespace App\MDS\EventsBundle\Controller;
use App\Entity\Client;
use App\Entity\ProposalsStatistics;
use App\Entity\Supplier;
use App\Entity\User;
use App\MDS\EventsBundle\Entity\Proposal;
use App\MDS\EventsBundle\Entity\ProposalBenefitUnder;
use App\MDS\EventsBundle\Entity\ProposalDiscount;
use App\MDS\EventsBundle\Entity\ProposalFee;
use App\MDS\EventsBundle\Entity\ProposalFeeInvoice;
use App\MDS\EventsBundle\Entity\ProposalInvoice;
use App\MDS\EventsBundle\Entity\ProposalPaymentsClient;
use App\MDS\EventsBundle\Entity\ProposalSupplierServicesCosts;
use App\MDS\EventsBundle\Entity\ProposalSupplierServicesCostsClient;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\Routing\Annotation\Route;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\ResponseHeaderBag;
use Symfony\Contracts\Translation\TranslatorInterface;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Symfony\Component\HttpFoundation\StreamedResponse;
class ProposalSummaryPricesController extends AbstractController
{
private $translator;
public function __construct(TranslatorInterface $translator) {
$this->translator = $translator;
}
/*
* ATENCION LAS RAIZ DE @Route POR DEFECTO DEL BUNDLE: EventsBundle ES /events/, NO PONERLO EN LA @Route("")
*
* */
/**
* @Route("/proposal/summary/prices/{id}", name="proposal_summary_price")
*/
public function SummaryPriceAction($id, Request $request)
{
$em = $this->getDoctrine()->getManager();
$proposal = $em->getRepository(Proposal::class)->findOneById($id);
$client = $em->getRepository(Client::class)->findOneById($proposal->getClientId());
$services_supplier = $em->getRepository(ProposalSupplierServicesCosts::class)->findBy( array( 'proposalId' => $id, ) );
$supplier_control = array();
foreach($services_supplier as $servicessupplier){
// if (!is_null($servicessupplier->getsupplierId()) && $servicessupplier->getsupplierId() != '0'){
if (!is_null($servicessupplier->getsupplierId())){
// if (!is_null($servicessupplier->getsupplierId())){
$supplier_control[$servicessupplier->getSupplierId()] = $servicessupplier->getSupplierId();
}
}
$total_neto = 0;
$total_iva = 0;
$totales_gene_neto_client ="0";
$totales_gene_con_iva_client ="0";
$totales_gen_neto_costs ="0";
$totales_gen_masiva_costa ="0";
$totatles_beneficio_moneda = "0";
$totatles_beneficio_porcentaje = "0";
$totales_neto_client = "0";
$iva_client = '0';
$data_supplier = array();
$data_serviceCat = array();
foreach ($supplier_control as $suppliercontrol){
$parameters = array(
'proposalId' => $id,
'supplierId' => $suppliercontrol,
// 'serviceCatId' => '0',
'serviceCatName' => 'PAYMENTS',
);
$dql = 'SELECT cs
FROM EventsBundle:ProposalSupplierServicesCosts cs
WHERE cs.proposalId = :proposalId AND cs.supplierId = :supplierId AND cs.serviceCatName != :serviceCatName';
$query = $em->createQuery($dql)->setParameters($parameters);
$costs_supplier = $query->getResult();
// pagos
$costs_supplier_pays = $em->getRepository(ProposalSupplierServicesCosts::class)->findBy(
array(
'proposalId' => $id,
'supplierId' => $suppliercontrol,
'serviceCatId' => '0',
'serviceCatName' => 'PAYMENTS',
)
);
$pagos_realizados ="0";
foreach ($costs_supplier_pays as $costssupplierpays){
$pagos_realizados += abs($costssupplierpays->getTotalServices());
}
$data_costs = array();
$totales_data = array();
$totales_neto_client ='0';
$totales_iva_client ='0';
$totales_con_iva_client ='0';
$totales_neto_costs ='0';
$totales_masiva_costa ='0';
$beneficio_moneda = "0";
$numerocuenta="1";
foreach ($costs_supplier as $costssupplier){
$serviceIdProposal = explode(",", $costssupplier->getServicesId());
$costs_client = $em->getRepository(ProposalSupplierServicesCostsClient::class)->findBy(
array(
'proposalId' => $id,
'serviceIdProposal' => $serviceIdProposal,
)
);
$total_neto_client ='0';
$total_iva_client ='0';
$total_con_iva_client ='0';
foreach ($costs_client as $costsclient){
$idcat = $costsclient->getServiceCatId();
$price = ($costsclient->getPrice() == "")? 0 : $costsclient->getPrice();
$units = ($costsclient->getUnits() == "")? 0 : $costsclient->getUnits();
$pax = ($costsclient->getPax() == "")? 0 : $costsclient->getPax();
$opcommission = $costsclient->getOpCommission();
$commission = $costsclient->getCommission();
$opover = $costsclient->getOpOver();
$over = $costsclient->getOver();
$opiva = $costsclient->getOpIva();
$iva_client = $costsclient->getIva();
$dateInAt = $costsclient->getDateInAt();
$dateOutAt = $costsclient->getDateOutAt();
if (!is_null($dateInAt) and !is_null($dateOutAt)){
$days = $dateInAt->diff($dateOutAt);
$day = $days->days;
}else{
$day = "0";
}
$calculos = $this->CalculoTotalesservices($price, $units, $pax, $day, $opcommission, $commission, $opover, $over, $opiva, $iva_client, $idcat);
$total_neto_client += $calculos['total_neto'];
$total_iva_client += $calculos['sub_total_iva'];
$total_con_iva_client += $calculos['total'];
// $totales_paying += $calculos['total_paying'];
// $totales_pago += $calculos['total_pago'];
// $totales_benefit_moneda += $calculos['benefit_moneda'];
$totales_neto_client += $calculos['total_neto'];
$totales_iva_client += $calculos['sub_total_iva'];
$totales_con_iva_client += $calculos['total'];
$totales_gene_neto_client += $calculos['total_neto'];
$totales_gene_con_iva_client += $calculos['total'];
// /* base imponible por servicios */
if (empty($base_imponible_service[$idcat])){
$base_imponible_service[$idcat]= 0;
}
$base_imponible_service[$idcat] += $calculos['total_neto'];
$data_serviceCat[$idcat] = array(
'idcat' => $idcat,
'total_neto' => $base_imponible_service[$idcat],
);
}
if (empty($costssupplier->getCommission())){ $commission = "0"; } else { $commission = $costssupplier->getCommission(); }
if (empty($costssupplier->getTotalServices())){ $cost_total = "0"; } else { $cost_total = $costssupplier->getTotalServices(); }
// $totales_neto = $costssupplier->getTotalServices() / ($costssupplier->getCommission() /100 + 1);
$totales_neto = (float)$cost_total * ( ( 100 - (float)$commission ) / 100);
$ivaServices = empty($costssupplier->getIvaServices())? 0 : $costssupplier->getIvaServices();
if ($costssupplier->getOpIva() == "0"){
// Si es menos(-) es Entre(/)
$totales_neto = $totales_neto / ($ivaServices / 100 + 1);
}
$total_neto += $totales_neto;
$iva = $totales_neto * ($ivaServices / 100);
$totales_iva = $totales_neto * ($ivaServices / 100 + 1);
$total_iva += $totales_iva;
$totales_neto_costs += $totales_neto;
$totales_masiva_costa += $totales_iva;
$totales_gen_neto_costs += $totales_neto;
$totales_gen_masiva_costa += $totales_iva;
/* Obtener y agregar el nombre del proveedor */
if (!is_null($costssupplier->getSupplierIdExpenses()) and $costssupplier->getSupplierIdExpenses() != 0 ){
$supplierExp = $em->getRepository(Supplier::class)->findOneById($costssupplier->getSupplierIdExpenses());
if(!empty($supplierExp)){
$costssupplier->setSupplierIdExpenses($supplierExp->getCompany());
}else{
$costssupplier->setSupplierIdExpenses('');
}
}else{
$costssupplier->setSupplierIdExpenses('');
}
$data_costs[] = array(
'data' => $costssupplier,
'total_neto_client' => $total_neto_client,
'iva_client' => $iva_client,
'total_iva_client' => $total_con_iva_client,
'total_neto' => $totales_neto,
'iva' => $ivaServices,
'total_iva' => $totales_iva
);
}
$beneficio_moneda = $totales_neto_client - ($totales_neto_costs);
if ($totales_neto_client == "0"){
$beneficio_porsentaje = "0";
}else{
$beneficio_porsentaje = $beneficio_moneda / $totales_neto_client * 100;
}
$totatles_beneficio_moneda += $beneficio_moneda;
$totatles_beneficio_porcentaje += $beneficio_porsentaje;
$numerocuenta += 1;
$Totales_pagos_realizados = $totales_masiva_costa - $pagos_realizados;
$totales_data = array(
'totalesSu_neto_client' => $totales_neto_client,
'totalesSu_masiva_client' => $totales_con_iva_client,
'totalesSu_neto_cost' => $totales_neto_costs,
'totalesSu_masiva_cost' => $totales_masiva_costa,
'pagos' => $pagos_realizados,
'totales_pagos' => $Totales_pagos_realizados,
'beneficio' => $beneficio_moneda,
'beneficio_por' => $beneficio_porsentaje,
);
if($suppliercontrol == '0'){
$data_supplier[$suppliercontrol] = array(
'master' => 'supplier',
'supplier' => 'Internal Expenses',
'data' => $data_costs,
'totales' => $totales_data
);
}else{
$suppliers = $em->getRepository(Supplier::class)->findById($suppliercontrol);
foreach ($suppliers as $supplier){
$data_supplier[$suppliercontrol] = array(
'master' => 'supplier',
'supplier' => $supplier->getName(),
'data' => $data_costs,
'totales' => $totales_data
);
}
}
}
$data_serviceOut = array(
'idcat' => null,
'total_neto' => $totales_neto_client,
);
array_push ( $data_serviceCat, $data_serviceOut);
$consulta_discount = $em->getRepository(ProposalDiscount::class)->findByProposalId($id);
$cuantosdiscount = count($consulta_discount);
$totales_generales="";
$descuento =array();
if (!empty($consulta_discount)){
$quitar_en_todos=0;
$datos_total_neto_suma=0;
foreach($data_serviceCat as $dataserviceat) {
$name ="";
$amount = "";
$type_des = "";
$iva_discount="";
$datos_idcat = $dataserviceat['idcat'];
$datos_total_neto = $dataserviceat['total_neto'];
$totales_neto_des = $datos_total_neto;
$datosiva_neto_des = $datos_total_neto;
$calculo_net_negativo="";
$total_con_iva_desc="";
$contarveces = 1;
foreach($consulta_discount as $consultadiscount) {
if($dataserviceat['idcat'] == null){
if($contarveces > 1 ){
$totales_neto_des = $datos_total_neto_suma;
$datosiva_neto_des = $datos_total_neto_suma;
$datos_total_neto = $datos_total_neto_suma;
}else{
if($cuantosdiscount > 1){
$totales_neto_des = $dataserviceat['total_neto'] - $quitar_en_todos;
$datosiva_neto_des = $dataserviceat['total_neto'] - $quitar_en_todos;
$datos_total_neto = $dataserviceat['total_neto'] - $quitar_en_todos;
}
}
$contarveces += 1;
}
if ($dataserviceat['idcat'] == $consultadiscount->getServiceId()){
$iva = $consultadiscount->getIva() / 100;
if ($consultadiscount->getType()=="0"){
$por = $consultadiscount->getAmount() / 100;
// $datosiva_neto_des = $datosiva_neto_des / ($por + 1) ;
$datosiva_neto_des = $datosiva_neto_des - ($datosiva_neto_des * $por) ;
$name = $consultadiscount->getName();
$type_des = $consultadiscount->getType();
$amount = $consultadiscount->getAmount();
}
if ($consultadiscount->getType()=="1"){
$datosiva_neto_des = $datosiva_neto_des - $consultadiscount->getAmount();
$name = $consultadiscount->getName();
$type_des = $consultadiscount->getType();
$amount = $consultadiscount->getAmount();
}
// resultados en negativo
$calculo_net_negativo = $datosiva_neto_des - $datos_total_neto;
$total_desc = "0";
// resultados para calculos
$calculo_net = $datos_total_neto - $datosiva_neto_des;
if($consultadiscount->getOpIva() =='0'){
$iva_discount = $calculo_net - ($calculo_net / ($iva + 1));
$calculo_net_negativo = $calculo_net_negativo + $iva_discount;
$totales_neto_des = $totales_neto_des + $iva_discount;
$total_desc = $iva_discount;
}
if($consultadiscount->getOpIva() =='1'){
$iva_discount = $calculo_net * $iva;
$total_desc = $iva_discount;
}
if($type_des == 0){ $type_des = "%"; }
if($type_des == 1){ $type_des = "€"; }
// if($dataserviceat['idcat'] == null){
$datos_total_neto_suma = $datos_total_neto- abs($calculo_net_negativo);
// }
$quitar_en_todos = $quitar_en_todos + abs($calculo_net_negativo);
// $datosiva_neto_des = $datosiva_neto_des + $calculo_net_negativo;
$total_con_iva_desc = $calculo_net_negativo - $iva_discount;
$totales_gene_neto_client = $totales_gene_neto_client - abs($calculo_net_negativo);
$totales_gene_con_iva_client = $totales_gene_con_iva_client - abs($total_con_iva_desc);
$descuento[] = array(
'name' => $name,
'type' => $type_des,
'amount' => $amount,
'iva' => $consultadiscount->getIva(),
'iva_amount' => $iva_discount,
'totales_neto_des' => $calculo_net_negativo,
'total' => $total_con_iva_desc,
);
}
}
}
$totatles_beneficio_moneda_dis = ($totales_gene_neto_client - $totales_gen_neto_costs) ;
if($totales_gene_neto_client == "0"){
$totatles_beneficio_porcentaje_general_dis = "0";
}else{
$totatles_beneficio_porcentaje_general_dis = ($totatles_beneficio_moneda_dis / $totales_gene_neto_client) * 100;
}
$totales_generales = array(
'totales_gen_neto_client' => $totales_gene_neto_client,
'totales_gen_masiva_client' => $totales_gene_con_iva_client,
'totales_gen_neto_cost' => $totales_gen_neto_costs,
'totales_gen_masiva_cost' => $totales_gen_masiva_costa,
'totatles_gen_beneficio_moneda' => $totatles_beneficio_moneda_dis,
'totatles_gen_beneficio_por' => $totatles_beneficio_porcentaje_general_dis,
);
}else{
if($totales_gene_neto_client == "0"){
$totatles_beneficio_porcentaje_general = "0";
}else{
$totatles_beneficio_porcentaje_general = ($totatles_beneficio_moneda / $totales_gene_neto_client) * 100;
}
$totales_generales = array(
'totales_gen_neto_client' => $totales_gene_neto_client,
'totales_gen_masiva_client' => $totales_gene_con_iva_client,
'totales_gen_neto_cost' => $totales_gen_neto_costs,
'totales_gen_masiva_cost' => $totales_gen_masiva_costa,
'totatles_gen_beneficio_moneda' => $totatles_beneficio_moneda,
'totatles_gen_beneficio_por' => $totatles_beneficio_porcentaje_general,
);
}
$payments = $em->getRepository(ProposalPaymentsClient::class)->findByProposalId($id);
$amount_pay =0;
foreach($payments as $payment) { $amount_pay = $amount_pay + $payment->getAmount(); }
if ($totales_generales['totatles_gen_beneficio_por'] < 9){
// Los expedientes con beneficio inferior al 9% se deben registrar en la tabla ProposalBenefitUnder para el control de administracion
$proposalBenUnder = $em->getRepository(ProposalBenefitUnder::class)->findOneByProposalId($id);
$em->clear();
$user_logueado = $this->get('security.token_storage')->getToken()->getUser();
$user_id = $user_logueado->getId();
$hoy = new \DateTime("now", NULL);
if (empty($proposalBenUnder)){
$prpBenUnd = new ProposalBenefitUnder();
$prpBenUnd->setProposalId($id);
$prpBenUnd->setBenefitCurrency((string)round($totales_generales['totatles_gen_beneficio_moneda'],2));
$prpBenUnd->setBenefitPercentage((string)round($totales_generales['totatles_gen_beneficio_por'],2));
$prpBenUnd->setCreatedId($user_id);
$prpBenUnd->setUpdateId($user_id);
$prpBenUnd->setCreatedAt($hoy);
$prpBenUnd->setUpdatedAt($hoy);
try {
$em->persist($prpBenUnd);
$em->flush();
} catch (\Exception $e) {
$event = 'Advertencia ' . $e->getMessage();
/* Para el usuario */
$errorMessage = $this->translator->trans($event);
$this->addFlash('mensajereservationerror', $errorMessage);
}
} else {
// Ya hay un registro, se procede a actualizar
// Por problemas en la cache se vuelve a realizar la misma busqueda, para eliminar el elemento de la tabla e insertar su version actualizada
$proposalBenUnder = $em->getRepository(ProposalBenefitUnder::class)->findOneByProposalId($id);
try {
$em->remove($proposalBenUnder);
$em->flush();
} catch (\Exception $e) {
$event = 'Advertencia ' . $e->getMessage();
/* Para el usuario */
$errorMessage = $this->translator->trans($event);
$this->addFlash('mensajereservationerror', $errorMessage);
}
$proposalBenUnder->setBenefitCurrency((string)round($totales_generales['totatles_gen_beneficio_moneda'],2));
$proposalBenUnder->setBenefitPercentage((string)round($totales_generales['totatles_gen_beneficio_por'],2));
$proposalBenUnder->setUpdateId($user_id);
$proposalBenUnder->setUpdatedAt($hoy);
try {
$em->persist($proposalBenUnder);
$em->flush();
} catch (\Exception $e) {
$event = 'Advertencia ' . $e->getMessage();
/* Para el usuario */
$errorMessage = $this->translator->trans($event);
$this->addFlash('mensajereservationerror', $errorMessage);
}
}
} else {
// No es un expediente de bajo beneficio, si esta en la tabla de bajo beneficio se debe eliminar
$em->clear();
$proposalBenUnder = $em->getRepository(ProposalBenefitUnder::class)->findOneByProposalId($id);
if (!empty($proposalBenUnder)){
try {
$em->remove($proposalBenUnder);
$em->flush();
} catch (\Exception $e) {
$event = 'Advertencia ' . $e->getMessage();
/* Para el usuario */
$errorMessage = $this->translator->trans($event);
$this->addFlash('mensajereservationerror', $errorMessage);
}
}
}
// ************************************************ FEE ************************************************************
$fees = $em->getRepository(ProposalFee::class)->findByProposalId($id);
$feesInvoice = $em->getRepository(ProposalFeeInvoice::class)->findByProposalId($id);
if (!empty($feesInvoice)) {
// Fee en factura
// Solo se haran los calculos si el Fee va a la factura, en caso contrario es un fee oculto y ya estará cargado en los servicios
if ($feesInvoice[0]->getToinvoice() == true) {
foreach ($feesInvoice as $feeInv) {
if ($feeInv->getType() == 0) {
// Tenemos un porcentaje en el Fee de la factura, debe calcularse
$feeNet = ($totales_generales['totales_gen_neto_client'] * $feeInv->getAmount()) / 100;
} else {
$feeNet = $feeInv->getAmount();
}
$feeTotal = $feeNet * 1.21; //El fee siempre será al 21%, Esteban Rincón
if ($feeInv->getToInvoice() == true) {
$totales_generales['totales_gen_neto_client'] += $feeNet;
$totales_generales['totales_gen_masiva_client'] += $feeTotal;
$totales_generales['totatles_gen_beneficio_moneda'] += $feeNet;
// $totales_generales['totatles_gen_beneficio_por'] = ($totales_generales['totatles_gen_beneficio_moneda'] / $totales_generales['totales_gen_neto_client'])* 100;
$totales_generales['totatles_gen_beneficio_por'] = $totales_generales['totales_gen_neto_client'] == 0 ? 0 : ($totales_generales['totatles_gen_beneficio_moneda'] / $totales_generales['totales_gen_neto_client'])* 100;
}
}
}
} else {
// Fee en proforma
if (!empty($fees)) {
// Solo se haran los calculos si el Fee va a la factura, en caso contrario es un fee oculto y ya estará cargado en los servicios
if ($fees[0]->getToinvoice() == true) {
foreach ($fees as $fee) {
if ($fee->getType() == 0) {
// Tenemos un porcentaje, debe calcularse
$feeNet = ($totales_generales['totales_gen_neto_client'] * $fee->getAmount()) / 100;
} else {
$feeNet = $fee->getAmount();
}
$feeTotal = $feeNet * 1.21; //El fee siempre será al 21%, Esteban Rincón
if ($fee->getToInvoice() == true) {
$totales_generales['totales_gen_neto_client'] += $feeNet;
$totales_generales['totales_gen_masiva_client'] += $feeTotal;
$totales_generales['totatles_gen_beneficio_moneda'] += $feeNet;
// $totales_generales['totatles_gen_beneficio_por'] = ($totales_generales['totatles_gen_beneficio_moneda'] / $totales_generales['totales_gen_neto_client'])* 100;
$totales_generales['totatles_gen_beneficio_por'] = $totales_generales['totales_gen_neto_client'] == 0 ? 0 : ($totales_generales['totatles_gen_beneficio_moneda'] / $totales_generales['totales_gen_neto_client'])* 100;
}
}
}
}
}
// ************************************************ FEE ************************************************************
$toXls = $request->request->get('boolToXls');
if (!empty($toXls)){
$phpExcelObject = new Spreadsheet();
// Solicita el servicio de excel
// $phpExcelObject = $this->get('phpexcel')->createPHPExcelObject();
$phpExcelObject->getProperties()->setCreator("InOut Travel & Events")
->setLastModifiedBy("InOut Travel & Events")
->setTitle("InOut Travel & Events")
->setSubject("InOut Travel & Events")
->setDescription("InOut Travel & Events, generado usando clases de PHP")
->setKeywords("office 2005 openxml php")
->setCategory("Archivo de ejemplo");
$fill = new Fill();
$numberFormat = new NumberFormat();
$alignment = new Alignment();
$i = 6;
$indiceInicial = 7;
$indiceFinal = 7;
// $activesheet = $phpExcelObject->getActiveSheet();
// $drawingobject = $this->get('phpexcel')->createPHPExcelWorksheetDrawing();
// $drawingobject->setName('Image name');
// $drawingobject->setDescription('Image description');
// $drawingobject->setPath(getcwd().'/assets/images/logo/logo2022.png');
// $drawingobject->setHeight(60);
// $drawingobject->setOffsetY(20);
// $drawingobject->setCoordinates('Q1');
// $drawingobject->setWorksheet($activesheet);
$phpExcelObject
->getActiveSheet()
->getStyle('B3:O3')
->getFill()
->setFillType($fill::FILL_SOLID)
->getStartColor()
->setRGB('188bab');
$phpExcelObject->setActiveSheetIndex(0)
->setCellValue('C3','Resumen económico del expediente: '.$id)
;
$phpExcelObject->setActiveSheetIndex(0)
->setCellValue('B'.$i, 'Cliente: ')
->setCellValue('C'.$i, $client->getName())
->setCellValue('B'.($i+1), 'CIF')
->setCellValue('C'.($i+1), $client->getIdDocument())
;
$data = $data_supplier;
$i = $i + 4;
foreach ($data as $item) {
$backRed = 'B'.$i.':C'.$i;
$phpExcelObject
->getActiveSheet()
->getStyle($backRed)
->getFill()
->setFillType($fill::FILL_SOLID)
->getStartColor()
->setRGB('f70000');
$phpExcelObject->setActiveSheetIndex(0) ->setCellValue('B' . $i, $item['supplier']) ;
$i += 2;
$phpExcelObject->setActiveSheetIndex(0) ->setCellValue('B' . $i, 'Tipo de Servicios') ;
$backGreenLight = 'D'.$i.':I'.($i+1);
$phpExcelObject
->getActiveSheet()
->getStyle($backGreenLight)
->getFill()
->setFillType($fill::FILL_SOLID)
->getStartColor()
->setRGB('78bf8f');
$phpExcelObject->setActiveSheetIndex(0) ->setCellValue('F' . $i, 'Confirmado Cliente') ;
$phpExcelObject->setActiveSheetIndex(0) ->setCellValue('D' . ($i + 1), 'Total Negocio') ;
$phpExcelObject->setActiveSheetIndex(0) ->setCellValue('F' . ($i + 1), 'Iva del Cliente') ;
$phpExcelObject->setActiveSheetIndex(0) ->setCellValue('H' . ($i + 1), 'Total del Cliente') ;
$backYellow = 'K'.$i.':O'.($i+1);
$phpExcelObject
->getActiveSheet()
->getStyle($backYellow)
->getFill()
->setFillType($fill::FILL_SOLID)
->getStartColor()
->setRGB('e3c636');
$phpExcelObject->setActiveSheetIndex(0) ->setCellValue('M' . $i, 'Coste Proveedor') ;
$phpExcelObject->setActiveSheetIndex(0) ->setCellValue('K' . ($i + 1), 'Total Neto') ;
$phpExcelObject->setActiveSheetIndex(0) ->setCellValue('M' . ($i + 1), 'Iva') ;
$phpExcelObject->setActiveSheetIndex(0) ->setCellValue('O' . ($i + 1), 'Total') ;
$i += 3;
foreach ($item['data'] as $elem) {
// Tipo de servicio
$phpExcelObject->setActiveSheetIndex(0)
->setCellValue('B' . $i, $elem['data']->getServiceCatName())
->setCellValue('D' . $i, $elem['total_neto_client'])
->setCellValue('F' . $i, $elem['iva_client'])
->setCellValue('H' . $i, $elem['total_iva_client'])
->setCellValue('K' . $i, number_format((float)$elem['total_neto'], 2, ',', ''))
->setCellValue('M' . $i, $elem['data']->getIvaServices())
->setCellValue('O' . $i, number_format((float)$elem['total_iva'], 2, ',', ''))
;
$i++;
}
$i ++;
$backBlue = 'B'.$i.':O'.$i;
$phpExcelObject
->getActiveSheet()
->getStyle($backBlue)
->getFill()
->setFillType($fill::FILL_SOLID)
->getStartColor()
->setRGB('14a6a8');
$phpExcelObject->setActiveSheetIndex(0) ->setCellValue('B' .$i, 'Totales') ;
$phpExcelObject->setActiveSheetIndex(0) ->setCellValue('D' .$i, $item['totales']['totalesSu_neto_client']) ;
$phpExcelObject->setActiveSheetIndex(0) ->setCellValue('H' .$i, $item['totales']['totalesSu_masiva_client']) ;
$phpExcelObject->setActiveSheetIndex(0) ->setCellValue('K' .$i, $item['totales']['totalesSu_neto_cost']) ;
$phpExcelObject->setActiveSheetIndex(0) ->setCellValue('O' .$i, $item['totales']['totalesSu_masiva_cost']) ;
$i++;
$backBluePay = 'K'.$i.':O'.($i+2);
$phpExcelObject
->getActiveSheet()
->getStyle($backBluePay)
->getFill()
->setFillType($fill::FILL_SOLID)
->getStartColor()
->setRGB('14a6a8');
$i++;
$phpExcelObject->setActiveSheetIndex(0) ->setCellValue('K' .$i, 'Pagos Anticipados') ;
$phpExcelObject->setActiveSheetIndex(0) ->setCellValue('O' .$i, $item['totales']['pagos']) ;
$phpExcelObject->setActiveSheetIndex(0) ->setCellValue('K' .($i+1), 'Pagos Pendientes') ;
$phpExcelObject->setActiveSheetIndex(0) ->setCellValue('O' .($i+1), $item['totales']['totales_pagos']) ;
$i += 3;
$backGreenBenef = 'H'.$i.':K'.($i+1);
$phpExcelObject
->getActiveSheet()
->getStyle($backGreenBenef)
->getFill()
->setFillType($fill::FILL_SOLID)
->getStartColor()
->setRGB('1fc254');
$phpExcelObject->setActiveSheetIndex(0) ->setCellValue('I' . $i, 'Beneficio: '. number_format((float)$item['totales']['beneficio'], 2, ',', '') .' €') ;
$phpExcelObject->setActiveSheetIndex(0) ->setCellValue('I' . ($i + 1), 'Beneficio (%): '.number_format((float)$item['totales']['beneficio_por'], 2, ',', '').' %') ;
$i += 5;
}
$backRedClient = 'D'.$i.':H'.$i;
$phpExcelObject
->getActiveSheet()
->getStyle($backRedClient)
->getFill()
->setFillType($fill::FILL_SOLID)
->getStartColor()
->setRGB('ff0303');
$phpExcelObject->setActiveSheetIndex(0)
->setCellValue('D' . $i, number_format((float)$totales_generales['totales_gen_neto_client'], 2, ',', ''))
->setCellValue('H' . $i, number_format((float)$totales_generales['totales_gen_masiva_client'], 2, ',', ''))
;
$backRedSupplier = 'K'.$i.':O'.$i;
$phpExcelObject
->getActiveSheet()
->getStyle($backRedSupplier)
->getFill()
->setFillType($fill::FILL_SOLID)
->getStartColor()
->setRGB('ff0303');
$phpExcelObject->setActiveSheetIndex(0)
->setCellValue('K' . $i, number_format((float)$totales_generales['totales_gen_neto_cost'], 2, ',', ''))
->setCellValue('O' . $i, number_format((float)$totales_generales['totales_gen_masiva_cost'], 2, ',', ''))
;
$i += 2;
$backRedBen = 'H'.$i.':K'.($i+1);
$phpExcelObject
->getActiveSheet()
->getStyle($backRedBen)
->getFill()
->setFillType($fill::FILL_SOLID)
->getStartColor()
->setRGB('ff0303');
$phpExcelObject->setActiveSheetIndex(0)
->setCellValue('I' . $i, 'Beneficio: '. number_format((float)$totales_generales['totatles_gen_beneficio_moneda'], 2, ',', '').' €')
->setCellValue('I' . ($i+1), 'Beneficio (%): '. number_format((float)$totales_generales['totatles_gen_beneficio_por'], 2, ',', ''). ' %')
;
$i += 4;
$backGrayPay = 'B'.$i.':O'.($i+1);
$phpExcelObject
->getActiveSheet()
->getStyle($backGrayPay)
->getFill()
->setFillType($fill::FILL_SOLID)
->getStartColor()
->setRGB('c4c4c4');
$phpExcelObject->setActiveSheetIndex(0)
->setCellValue('C' . $i, 'Cantidad')
->setCellValue('G' . $i, 'Fecha de Pago')
->setCellValue('L' . $i, 'Forma de Pago')
;
$i+=2;
foreach ($payments as $item){
$backGrayPay = 'B'.$i.':O'.$i;
$phpExcelObject
->getActiveSheet()
->getStyle($backGrayPay)
->getFill()
->setFillType($fill::FILL_SOLID)
->getStartColor()
->setRGB('c4c4c4');
$phpExcelObject->setActiveSheetIndex(0)
->setCellValue('C' . $i, $item->getAmount())
->setCellValue('G' . $i, $item->getDatePayAt()->format('d/m/Y'))
->setCellValue('L' . $i, $item->getWaytopay())
;
$i++;
}
$phpExcelObject->getActiveSheet()->setTitle('Estadísticas por sala');
// Define el indice de página al número 1, para abrir esa página al abrir el archivo
$phpExcelObject->setActiveSheetIndex(0);
// Creamos pagina de Datos en bruto para Esteban Rincon
// Add new sheet
$objWorkSheet = $phpExcelObject->createSheet(1); //Setting index when creating
$phpExcelObject->setActiveSheetIndex(1);
$phpExcelObject->getActiveSheet()->setTitle('Datos en bruto');
$x = 1;
$phpExcelObject->setActiveSheetIndex(1)
->setCellValue('A'.$x, 'PROVEEDOR')
->setCellValue('B'.$x, 'BENEFICIO (€)')
->setCellValue('C'.$x, 'BENEFICIO (%)')
->setCellValue('D'.$x, 'SUMA TOTAL NETO CLIENTE')
->setCellValue('E'.$x, 'SUMA TOTAL CLIENTE')
->setCellValue('F'.$x, 'SUMA TOTAL NETO PROVEEDOR')
->setCellValue('G'.$x, 'SUMA TOTAL PROVEEDOR')
->setCellValue('H'.$x, 'PAGOS ANTICIPADOS')
->setCellValue('I'.$x, 'PAGOS PENDIENTES')
->setCellValue('J'.$x, 'SERVICIO')
->setCellValue('K'.$x, 'CLIENTE NEGOCIO')
->setCellValue('L'.$x, 'IVA DEL CLIENTE')
->setCellValue('M'.$x, 'TOTAL CLIENTE')
->setCellValue('N'.$x, 'PROVEEDOR TOTAL NETO')
->setCellValue('O'.$x, 'PROVEEDOR IVA')
->setCellValue('P'.$x, 'TOTAL PROVEEDOR')
;
$i = $x + 1;
foreach ($data as $item) {
$phpExcelObject->setActiveSheetIndex(1) ->setCellValue('A' . $i, $item['supplier']) ;
$phpExcelObject->setActiveSheetIndex(1)
->setCellValue('B' . $i, number_format((float)$item['totales']['beneficio'], 2, ',', ''))
->setCellValue('C' . $i, number_format((float)$item['totales']['beneficio_por'], 2, ',', ''))
;
$phpExcelObject->setActiveSheetIndex(1) ->setCellValue('D' .$i, $item['totales']['totalesSu_neto_client']) ;
$phpExcelObject->setActiveSheetIndex(1) ->setCellValue('E' .$i, $item['totales']['totalesSu_masiva_client']) ;
$phpExcelObject->setActiveSheetIndex(1) ->setCellValue('F' .$i, $item['totales']['totalesSu_neto_cost']) ;
$phpExcelObject->setActiveSheetIndex(1) ->setCellValue('G' .$i, $item['totales']['totalesSu_masiva_cost']) ;
$phpExcelObject->setActiveSheetIndex(1) ->setCellValue('H' .$i, $item['totales']['pagos']) ;
$phpExcelObject->setActiveSheetIndex(1) ->setCellValue('I' .$i, $item['totales']['totales_pagos']) ;
foreach ($item['data'] as $elem) {
// Tipo de servicio
$phpExcelObject->setActiveSheetIndex(1)
->setCellValue('J' . $i, $elem['data']->getServiceCatName())
->setCellValue('K' . $i, $elem['total_neto_client'])
->setCellValue('L' . $i, $elem['iva_client'])
->setCellValue('M' . $i, $elem['total_iva_client'])
->setCellValue('N' . $i, number_format((float)$elem['total_neto'], 2, ',', ''))
->setCellValue('O' . $i, $elem['data']->getIvaServices())
->setCellValue('P' . $i, number_format((float)$elem['total_iva'], 2, ',', ''))
;
$i++;
}
}
$i += 3;
$phpExcelObject->setActiveSheetIndex(1)
->setCellValue('A' . $i, 'CANTIDAD')
->setCellValue('B' . $i, 'FECHA DE PAGO')
->setCellValue('C' . $i, 'FORMA DE PAGO')
;
$i++;
foreach ($payments as $item){
$phpExcelObject->setActiveSheetIndex(1)
->setCellValue('A' . $i, $item->getAmount())
->setCellValue('B' . $i, $item->getDatePayAt()->format('d/m/Y'))
->setCellValue('C' . $i, $item->getWaytopay())
;
$i++;
}
$phpExcelObject->setActiveSheetIndex(0);
// Crea el writer
// $writer = $this->get('phpexcel')->createWriter($phpExcelObject, 'Excel2007');
$writer = new Xlsx($phpExcelObject);
// Envia la respuesta del controlador
// $response = $this->get('phpexcel')->createStreamedResponse($writer);
$response = new StreamedResponse( function () use ($writer) { $writer->save('php://output'); } );
// Agrega los headers requeridos
$dispositionHeader = $response->headers->makeDisposition(
ResponseHeaderBag::DISPOSITION_ATTACHMENT,
'InOut_Travel_Events'.'.xlsx'
);
$response->headers->set('Content-Type', 'application/vnd.ms-excel; charset=utf-8');
$response->headers->set('Pragma', 'public');
$response->headers->set('Cache-Control', 'maxage=1');
$response->headers->set('Content-Disposition', $dispositionHeader);
$phpExcelObject->getActiveSheet()->setTitle('InOut Travel & Events');
// Define el indice de página al número 1, para abrir esa página al abrir el archivo
$phpExcelObject->setActiveSheetIndex(0);
// Crea el writer
// $writer = $this->get('phpexcel')->createWriter($phpExcelObject, 'Excel2007');
$writer = new Xlsx($phpExcelObject);
// Envia la respuesta del controlador
// $response = $this->get('phpexcel')->createStreamedResponse($writer);
$response = new StreamedResponse( function () use ($writer) { $writer->save('php://output'); } );
// Agrega los headers requeridos
$dispositionHeader = $response->headers->makeDisposition(
ResponseHeaderBag::DISPOSITION_ATTACHMENT,
'InOut Travel and Events.xlsx'
);
$response->headers->set('Content-Type', 'application/vnd.ms-excel; charset=utf-8');
$response->headers->set('Pragma', 'public');
$response->headers->set('Cache-Control', 'maxage=1');
$response->headers->set('Content-Disposition', $dispositionHeader);
return $response;
}
// ******************************** Statistics ********************************
$agentNewStats = $em->getRepository(User::class)->findOneById($proposal->getAgentId());
$ivaNewStats = $totales_generales['totales_gen_masiva_client'] - $totales_generales['totales_gen_neto_client'];
$stats = $em->getRepository(ProposalsStatistics::class)->findOneByProposalId($id);
if ($proposal->getStatus() == 'Invoiced'){
$invoiceNewStats = $em->getRepository(ProposalInvoice::class)->findOneByProposalId($id);
if (empty($stats)){
$stats = new ProposalsStatistics();
$stats->setProposalId($id);
$stats->setProposalName($proposal->getName());
$stats->setProposalStatus($proposal->getStatus());
$stats->setApproximateBilling($proposal->getApproximateBilling());
$stats->setInvoice($invoiceNewStats->getNumber());
$stats->setAgentFullName($agentNewStats->getName() . ' ' . $agentNewStats->getLastName());
$stats->setAgentId($agentNewStats->getId());
$stats->setClientName($client->getName());
$stats->setClientId($client->getId());
$stats->setProposalDateAt($proposal->getDateEventStarAt());
$stats->setProposalNet($totales_generales['totales_gen_neto_client']);
$stats->setProposalBenefit($totales_generales['totatles_gen_beneficio_moneda']);
$stats->setProposalBenefitPerc($totales_generales['totatles_gen_beneficio_por']);
$stats->setProposalIva($ivaNewStats);
$stats->setProposalTotal($totales_generales['totales_gen_masiva_client']);
$stats->setInvoiceDate($invoiceNewStats->getDateAt());
$em->persist($stats);
$em->flush();
} else {
if ($stats->getProposalStatus() !== 'Invoiced'){
// Estaba en confirmado y paso a facturado
$stats->setProposalId($id);
$stats->setProposalName($proposal->getName());
$stats->setProposalStatus($proposal->getStatus());
$stats->setInvoice($invoiceNewStats->getNumber());
$stats->setAgentFullName($agentNewStats->getName() . ' ' . $agentNewStats->getLastName());
$stats->setAgentId($agentNewStats->getId());
$stats->setClientName($client->getName());
$stats->setClientId($client->getId());
$stats->setProposalDateAt($proposal->getDateEventStarAt());
$stats->setProposalNet($totales_generales['totales_gen_neto_client']);
$stats->setProposalBenefit($totales_generales['totatles_gen_beneficio_moneda']);
$stats->setProposalBenefitPerc($totales_generales['totatles_gen_beneficio_por']);
$stats->setProposalIva($ivaNewStats);
$stats->setProposalTotal($totales_generales['totales_gen_masiva_client']);
$stats->setInvoiceDate($invoiceNewStats->getDateAt());
$em->persist($stats);
$em->flush();
}
}
} else {
// La parte para los confirmados
if ($proposal->getStatus() == 'Confirmed'){
if (empty($stats)){ $stats = new ProposalsStatistics(); }
$stats->setProposalId($id);
$stats->setProposalName($proposal->getName());
$stats->setProposalStatus($proposal->getStatus());
$stats->setApproximateBilling($proposal->getApproximateBilling());
$stats->setInvoice('NF');
$stats->setAgentFullName($agentNewStats->getName() . ' ' . $agentNewStats->getLastName());
$stats->setAgentId($agentNewStats->getId());
$stats->setClientName($client->getName());
$stats->setClientId($client->getId());
$stats->setProposalDateAt($proposal->getDateEventStarAt());
$stats->setProposalNet($totales_generales['totales_gen_neto_client']);
$stats->setProposalBenefit($totales_generales['totatles_gen_beneficio_moneda']);
$stats->setProposalBenefitPerc($totales_generales['totatles_gen_beneficio_por']);
$stats->setProposalIva($ivaNewStats);
$stats->setProposalTotal($totales_generales['totales_gen_masiva_client']);
$stats->setInvoiceDate(null);
$em->persist($stats);
$em->flush();
}
}
// ******************************** Statistics ********************************
return $this->render('MDS/EventsBundle/summary/summary-price-proposal.html.twig',
array(
'id' => $id,
'token' => $proposal->getAccessKey(),
'mcp' => $proposal->getMcp(),
'client' => $client,
'data' => $data_supplier,
'general' => $totales_generales,
'payments' => $payments,
'discounts' => $descuento,
));
}
public function CalculoTotalesservices($price, $units, $pax, $days, $opcommission, $commission, $opover, $over, $opiva, $iva, $idcat)
{
$price = trim($price);
$over = trim($over);
$price = str_replace(",", ".", $price);
$over = str_replace(",", ".", $over);
if (empty($commission)){$commission ="0";}
$iva = (float)$iva / 100;
if($opiva =='0'){
$price = $price / ($iva + 1);
}
//// $commission = $commission / 100;
// $commission = (100 - $commission) / 100;
// if($opcommission=='0'){
// $price = $price * $commission;
// }
if($opcommission=='0'){
$commission = (100 - $commission) / 100;
$price = $price * $commission;
}
if($opcommission=='1'){
$commission = ($commission / 100) + 1;
}
//Units
if(empty($units) or $units =="0"){
$units = "1";
}
// Calculo Especial por categoria
//Alojamientos
if($idcat =="1"){
$informativo_days = "si";
$pax = "1";
}
//Transporte
elseif($idcat =="13"){
$informativo_days = "no";
$pax = "1";
}
//Guia
elseif($idcat =="8"){
$informativo_days = "no";
$pax = "1";
}
//Lounge
elseif($idcat =="10"){
$informativo_days = "no";
$pax = "1";
}else{
$informativo_days = "no";
}
//Pax
if(empty($pax) or $pax =="0"){
$pax = "1";
}
//Over
if(empty($over)){
$over = "0";
}
if($informativo_days =="no"){
//Days
if(empty($days) or $days =="0"){
$days = "1";
}else{
$days = $days + 1 ;
}
}
$total_items = $price * $units * $pax * $days;
$total_over = $over * $units * $pax * $days;
// if($opover=='0'){
//// $total_neto = $total_items * ($commission + 1) - $total_over ;
// $total_neto = $total_items / $commission - $total_over;
// }
// if($opover=='1'){
//// $total_neto = $total_items * ($commission + 1) + $total_over;
// $total_neto = $total_items / $commission + $total_over;
// }
if($opcommission=='0'){
$total_neto = $total_items / $commission;
}
if($opcommission=='1'){
$total_neto = $total_items * $commission;
}
if($opover=='0'){
$total_neto = $total_neto - $total_over ;
}
if($opover=='1'){
$total_neto = $total_neto + $total_over;
}
$total_paying = $total_items * ($iva + 1);
$total_comision ="0";
$total_pago = $total_items;
$total_iva = $total_items * $iva;
$sub_total_iva = $total_neto * $iva;
$total = $total_neto + $sub_total_iva;
$benefit_moneda = $total_neto - $total_items;
$data_calculos = array(
'total_days' => $days,
'sub_total' => $total_items,
'sub_total_iva' => $sub_total_iva,
'total_neto' => $total_neto,
'total_iva' => $total_iva,
'total_paying' => $total_paying,
'total_pago' => $total_pago,
'total_comision' => $total_comision,
'total' => $total,
'benefit_moneda' => $benefit_moneda
);
return $data_calculos;
}
/**
* @Route("/proposal/lowbenefitlist", name="proposal_lowbenefitlist")
*/
public function ListLowBenefitAction(Request $request)
{
$em = $this->getDoctrine()->getManager();
$proposal = $em->getRepository(ProposalBenefitUnder::class)->findAll();
$proposalBenefitUnder = array();
foreach ($proposal as $prop){
$proposalData = $em->getRepository(Proposal::class)->findOneById($prop->getProposalId());
$agentId = $proposalData->getAgentId();
$agent = '';
if (!empty($agentId)) {
$agent = ($em->getRepository(User::class)->findOneById($agentId))->getName() . ' ' . ($em->getRepository(User::class)->findOneById($agentId))->getLastName();
}
$proposalBenefitUnder[] = array(
'proposalId' => $prop->getProposalId(),
'proposalName' => $proposalData->getName(),
'proposalAgent' => $agent,
'benefitCurrency' => $prop->getBenefitCurrency(),
'benefitPercentage' => $prop->getBenefitPercentage(),
);
}
//d($proposalBenefitUnder);exit();
return $this->render('MDS/EventsBundle/summary/low-benefit-proposal-list.html.twig',
array(
'proposalBenefitUnder' => $proposalBenefitUnder,
'id' => 0,
'token' => '',
'mcp' => '',
'unassigned' => '',
));
}
}

src/MDS/EventsBundle/Controller/ProposalSummaryPricesController.php line 53