Symfony Profiler

<?php
namespace App\Controller;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\Routing\Annotation\Route;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use App\Service\TrafficTracker;
use Symfony\Contracts\Translation\TranslatorInterface;
use App\Form\Usages\Importusages;
use App\Form\Usages\RequestAcronisReport;
use App\Form\Usages\RequestAzureBillingExport;
use App\Repository\TaskRepository;
use App\Service\Api\MicrosoftCsp\AzureBilledReconciliationService;
use App\Entity\Agreements;
use App\Entity\Apireports;
use App\Entity\Invoicearticle;
use App\Entity\Partner;
use App\Entity\Partnerlevelbyvendor;
use App\Entity\Product;
use App\Entity\License;
use App\Entity\Productbywholesaler;
use App\Entity\Taxes;
use App\Entity\Vendorprogramms;
use App\Repository\AgreementsRepository;
use App\Service\Api\AcronisClient;
use App\Service\CsvDataImporter;
use App\Service\UsageDataImporter;
use App\Service\GenerateInvoiceArticleDescriptionsService;
use App\Service\RegisterEvent;
use DateTime;
use Symfony\Component\HttpKernel\Exception\AccessDeniedHttpException;
class UsagesController extends AbstractController
{
/**
* @Route("/invoice/usages/indexWholesaler", name="invoice_usages_wholesaler")
*/
public function usagesWholesaler(Request $request, TrafficTracker $trafficTracker): Response
{
$trafficId = $trafficTracker->registerTraffic();
$user = $this->getUser();
if (!$user->getWholesaler()) {
throw new AccessDeniedHttpException();
}
/** @var AgreementsRepository */
$agreementRepository = $this->getDoctrine()->getRepository(Agreements::class);
$ia_qb1 = $agreementRepository->MyAgreementsQuery($user);
$ia_qb1->andWhere('a.wholesaler IS NOT NULL');
$ia_qb1->andWhere('a.active = TRUE');
$ia_qb1->innerJoin('\App\Entity\Vendorprogramms', 'vp', 'WITH', 'a.programm = vp.id');
$ia_qb1->andWhere('vp.vendorapi LIKE :acronis')->setParameter('acronis', '%' . 'acronis' . '%');
$acronisAgreement = $ia_qb1->setMaxResults(1)
->getQuery()
->getOneOrNullResult();
$ia_qb2 = $agreementRepository->MyAgreementsQuery($user);
$ia_qb2->andWhere('a.active = TRUE');
$ia_qb2->innerJoin('\App\Entity\Vendorprogramms', 'vp', 'WITH', 'a.programm = vp.id');
$ia_qb2->innerJoin('\App\Entity\Vendors', 'v', 'WITH', 'vp.vendor = v.id');
$ia_qb2->andWhere('v.name LIKE :SolarWinds')->setParameter('SolarWinds', '%' . 'N-able' . '%');
$solarwindsAgreement = $ia_qb2->setMaxResults(1)
->getQuery()
->getOneOrNullResult();
$ia_qb3 = $agreementRepository->MyAgreementsQuery($user);
$ia_qb3->andWhere('a.active = TRUE');
$ia_qb3->innerJoin('\App\Entity\Vendorprogramms', 'vp', 'WITH', 'a.programm = vp.id');
$ia_qb3->innerJoin('\App\Entity\Vendors', 'v', 'WITH', 'vp.vendor = v.id');
$ia_qb3->andWhere('v.name LIKE :Hornetsecurity')->setParameter('Hornetsecurity', '%' . 'Hornetsecurity' . '%');
$hornetAgreement = $ia_qb3->setMaxResults(1)
->getQuery()
->getOneOrNullResult();
$ia_qb4 = $agreementRepository->MyAgreementsQuery($user);
$ia_qb4->andWhere('a.active = TRUE');
$ia_qb4->andWhere('a.wholesaler IS NOT NULL');
$ia_qb4->innerJoin('\App\Entity\Vendorprogramms', 'vp', 'WITH', 'a.programm = vp.id');
$ia_qb4->andWhere('vp.vendorapi = :azureApi')->setParameter('azureApi', 'CSP');
$cspAgreement = $ia_qb4->setMaxResults(1)
->getQuery()
->getOneOrNullResult();
$listAgreements['Acronis'] = $acronisAgreement;
$listAgreements['Solarwinds'] = $solarwindsAgreement;
$listAgreements['Hornet'] = $hornetAgreement;
$listAgreements['Azure'] = $cspAgreement;
return $this->render('usages/index_wholesaler.html.twig', [
'agreements' => $listAgreements
]);
}
/**
* @Route("/invoice/usages/hornet", name="usages_hornet")
*/
public function hornet(Request $request, TrafficTracker $trafficTracker, TranslatorInterface $translator, UsageDataImporter $usageDataImporter, GenerateInvoiceArticleDescriptionsService $descriptionService, RegisterEvent $registerEvent): Response
{
$trafficId = $trafficTracker->registerTraffic();
$currentUser = $this->getUser();
$translator->setLocale($request->getLocale());
$session = $this->get('session');
$entityManager = $this->getDoctrine()->getManager();
$taxRepository = $this->getDoctrine()->getRepository(Taxes::class);
$form = $this->createForm(Importusages::class, null, array(
'translator' => $translator,
));
$mistakes = array();
if ($request->getMethod('post') == 'POST') {
$form->handleRequest($request);
$post = $form->getData();
// If form is valid
if ($form->isSubmitted() && $form->isValid()) {
$inputFileName = $form->get('submitFile')->getData()->getPathName();
$usages = $usageDataImporter->read($inputFileName);
$header = $usageDataImporter->getHeadersFromUsages($usages);
$data = $usageDataImporter->getDataFromUsages($usages);
$header_control = array(
'partner level 1',
'partner level 2',
'customer domain',
'creation date',
'MSF',
'ATP',
'MAS',
'MES',
'MCS',
'ASD',
'TPB',
'TPE',
'SAT1',
'TPEB',
'facturable'
);
if ($header !== $header_control) {
$session->getFlashBag()->add('danger', $translator->trans('app.Sales&Invoicing.ProcessingTools.WrongHeader'));
return $this->redirectToRoute('usages_hornet');
}
//Process usages
foreach ($data as $usage) {
$lastPosition = array_key_last($header);
//Get domain and check if exists in Katy
$dominio = $usage[2];
$facturable = $usage[$lastPosition] == 'SI' ? true : false;
$license = $entityManager->createQuery('SELECT l'
. ' FROM App\Entity\License l'
. ' WHERE l.cancellationrequestdate is null'
. ' AND l.cancellationvendordate is null'
. ' AND l.licenseid = :id')
->setParameter('id', $dominio)
->setMaxResults(1)
->getOneOrNullResult();
if ($license) {
$end = ($lastPosition - 1);
for ($i = 4; $i <= $end; $i++) {
$numberOfMailBoxes = $usage[$i];
$sku = $header[$i];
$product = $entityManager->createQuery('SELECT p'
. ' FROM App\Entity\Product p'
. ' WHERE p.sku like :sku')
->setParameter('sku', $sku)
->getOneOrNullResult();
$name = $license->getCustomer()->getCompany();
$wholesaler = $license->getCustomer()->getPartner()->getWholesaler();
$productByWholesaler = $entityManager->createQuery('SELECT pbw'
. ' FROM App\Entity\Productbywholesaler pbw'
. ' WHERE pbw.product = :product'
. ' AND pbw.wholesaler = :wholesaler')
->setParameter('product', $product)
->setParameter('wholesaler', $wholesaler)
->getOneOrNullResult();
if ($productByWholesaler && $numberOfMailBoxes > 0) {
$productRepository = $this->getDoctrine()->getRepository(\App\Entity\Product::class);
$pbwRepository = $this->getDoctrine()->getRepository(\App\Entity\Productbywholesaler::class);
$licenseRepository = $this->getDoctrine()->getRepository(\App\Entity\License::class);
$partnerRepository = $this->getDoctrine()->getRepository(\App\Entity\Partner::class);
$partnerLevel = $partnerRepository->getPartnerLevel($license->getCustomer()->getPartner(), $product->getProductline()->getVendor()->getId());
$discount = $productRepository->getDiscount($product, $wholesaler->getId(), $partnerLevel);
$price = $facturable == 'SI' ? $productByWholesaler->getPrice(False) : 0;
$priceConverted = $facturable == 'SI' ? $productByWholesaler->getPrice() : 0;
if ($productByWholesaler) {
$tax = $taxRepository->getTaxForPbw($productByWholesaler);
$taxValue = $tax ? ($price * $tax->getValue()) / 100 : 0;
$taxValueConverted = $tax ? ($priceConverted * $tax->getValue()) / 100 : 0;
} else {
$taxValue = 0;
$taxValueConverted = 0;
}
$description = $descriptionService->distributeVendorsDescriptions('Hornet', array(
'sku' => $sku,
'client' => $name,
'month' => $post['month'],
'year' => $post['year']
));
$total = ($price * $numberOfMailBoxes) * ((100 - $discount) / 100);
$totalPartner = number_format($total, 4, '.', '');
$totalConverted = ($priceConverted * $numberOfMailBoxes) * ((100 - $discount) / 100);
$totalConvertedPartner = number_format($totalConverted, 4, '.', '');
//Create invoiceline to partner
$newInvoiceArticletoPartner = new \App\Entity\Invoicearticle();
$newInvoiceArticletoPartner->setSale($licenseRepository->getActiveSale($license));
$newInvoiceArticletoPartner->setInvoiceTo('partner');
$newInvoiceArticletoPartner->setCustomer($license->getCustomer());
$newInvoiceArticletoPartner->setSku($sku);
$newInvoiceArticletoPartner->setDescription($description);
$newInvoiceArticletoPartner->setQuantity($numberOfMailBoxes);
$newInvoiceArticletoPartner->setPrice($price);
$newInvoiceArticletoPartner->setPriceConverted($priceConverted);
$newInvoiceArticletoPartner->setDiscount($discount);
$newInvoiceArticletoPartner->setImport($totalPartner);
$newInvoiceArticletoPartner->setImportConverted($totalConvertedPartner);
$newInvoiceArticletoPartner->setImportTax($totalPartner + ($taxValue * $totalPartner));
$newInvoiceArticletoPartner->setImportConvertedTax($totalConvertedPartner + ($taxValueConverted * $totalConvertedPartner));
$newInvoiceArticletoPartner->setExchangeRate($pbwRepository->getExchangeRate($productByWholesaler));
$newInvoiceArticletoPartner->setgeneratedDate(date_timestamp_get(date_create()));
$newInvoiceArticletoPartner->setPayperiod($product->getPayperiod());
$newInvoiceArticletoPartner->setProduct($product);
$newInvoiceArticletoPartner->setMonth($post['month']);
$newInvoiceArticletoPartner->setYear($post['year']);
$newInvoiceArticletoPartner->setPartner($license->getCustomer()->getPartner());
$entityManager->persist($newInvoiceArticletoPartner);
$registerEvent->addCreatedBy($newInvoiceArticletoPartner, $currentUser->getId(), false);
$importCustomer = $numberOfMailBoxes * $price;
$importConvertedCustomer = $numberOfMailBoxes * $priceConverted;
//Create invoiceline to customer
$newInvoiceArticletoCustomer = new \App\Entity\Invoicearticle();
$newInvoiceArticletoCustomer->setSale($licenseRepository->getActiveSale($license));
$newInvoiceArticletoCustomer->setInvoiceTo('customer');
$newInvoiceArticletoCustomer->setCustomer($license->getCustomer());
$newInvoiceArticletoCustomer->setSku($sku);
$newInvoiceArticletoCustomer->setDescription($description);
$newInvoiceArticletoCustomer->setQuantity($numberOfMailBoxes);
$newInvoiceArticletoCustomer->setPrice($price);
$newInvoiceArticletoCustomer->setPriceConverted($priceConverted);
$newInvoiceArticletoCustomer->setImport($importCustomer);
$newInvoiceArticletoCustomer->setImportConverted($importConvertedCustomer);
$newInvoiceArticletoCustomer->setImportTax($importCustomer + ($taxValue * $importCustomer));
$newInvoiceArticletoCustomer->setImportConvertedTax($importConvertedCustomer + ($taxValueConverted * $importConvertedCustomer));
$newInvoiceArticletoCustomer->setExchangeRate($pbwRepository->getExchangeRate($productByWholesaler));
$newInvoiceArticletoCustomer->setgeneratedDate(date_timestamp_get(date_create()));
$newInvoiceArticletoCustomer->setPayperiod($product->getPayperiod());
$newInvoiceArticletoCustomer->setDiscount(0); // El cliente no tiene descuento
$newInvoiceArticletoCustomer->setProduct($product);
$newInvoiceArticletoCustomer->setMonth($post['month']);
$newInvoiceArticletoCustomer->setYear($post['year']);
$newInvoiceArticletoCustomer->setPartner($license->getCustomer()->getPartner());
$entityManager->persist($newInvoiceArticletoCustomer);
$registerEvent->addCreatedBy($newInvoiceArticletoCustomer, $currentUser->getId(), false);
}
}
} else {
$mistakes[] = $dominio;
}
}
$entityManager->flush();
if (!$mistakes) {
$session->getFlashBag()->add('success', $translator->trans('app.Development.DataImport.DataImportDone'));
}
}
}
return $this->render('usages/hornet.html.twig', [
'form' => $form->createView(),
'mistakes' => $mistakes,
'rootInputDescription' => array()
]);
}
/**
* @Route("/invoice/usages/acronis/{agreement_id}", name="usages_acronis")
*/
public function acronisAction(Request $request, $agreement_id, AcronisClient $acronisClient, RegisterEvent $registerEvent, TrafficTracker $trafficTracker, TranslatorInterface $translator): Response
{
$trafficTracker->registerTraffic();
$session = $this->get('session');
/** @var EntityManager */
$entityManager = $this->getDoctrine()->getManager();
$user = $this->getUser();
/** @var Form $form */
$form = $this->createForm(RequestAcronisReport::class, null, array(
'translator' => $translator,
));
$form->handleRequest($request);
$post = $form->getData();
if ($form->isSubmitted()) {
if ($request->getMethod('post') == 'POST' && $form->isValid()) {
if (!$this->createAcronisReport($post['periodStart'], $post['periodEnd'], $agreement_id, $acronisClient, $registerEvent)) {
$session->getFlashBag()->add('danger', 'No se ha podido crear el informe.');
}
}
}
$reports = $entityManager->createQuery('SELECT ap'
. ' FROM App\Entity\Apireports ap '
. ' WHERE ap.vendor = 14'
. ' AND ap.wholesaler = :wholesaler')
->setParameter('wholesaler', $user->getWholesaler())
->getResult();
return $this->render('usages/acronis.html.twig', [
'reports' => $reports,
'form' => $form->createView(),
'agreement_id' => $agreement_id
]);
}
/**
* @Route("/invoice/usages/validateacronisreport/{id}", name="usages_validate_acronis_report", requirements={"id"="\d+"})
*/
public function validateacronisreportAction($id, TrafficTracker $trafficTracker, CsvDataImporter $csvDataImporter)
{
$trafficTracker->registerTraffic();
/** @var EntityManager */
$entityManager = $this->getDoctrine()->getManager();
$session = $this->get('session');
$errors[] = array('tenant_id', 'tenant_name', 'tenant_kind', 'error');
// find report to validate
$report = $entityManager->createQuery("SELECT ap FROM App\Entity\Apireports ap WHERE ap.id = :id")
->setParameter("id", $id)
->getOneOrNullResult();
if ($report) {
$tenants = $this->get_tenants_from_report($report, 'all', $csvDataImporter);
} else {
$session->getFlashBag()->add('warning', 'Informe de consumos no encontrado. No se ha podido validar el informe.');
return $this->redirect($this->generateUrl('usages_acronis'));
}
// Check if tenant exists in Katy
foreach ($tenants as $key => $value) {
$agreement = $entityManager->createQuery("SELECT a FROM App\Entity\Agreements a
JOIN a.programm p
WHERE p.id = 9
AND a.parameters like :parameters")
->setParameter("parameters", '{%"tenant_id": "' . $key . '"%}')
->setMaxResults(1)
->getOneOrNullResult();
$wholesaler = $agreement ? $agreement->getWholesaler() : NULL;
$partner = $agreement ? $agreement->getPartner() : NULL;
$customer = $agreement ? $agreement->getCustomer() : NULL;
if (!$customer && !$partner && !$wholesaler) {
array_push($errors, array($key, $value[0], $value[1], 'Not found.'));
} elseif ($customer) {
$license = $entityManager->createQuery("SELECT l
FROM App\Entity\License l
WHERE l.licenseid = :tenant_id")
->setParameter("tenant_id", $key)
->setMaxResults(1)
->getOneOrNullResult();
if (!$license) {
array_push($errors, array($key, $value[0], $value[1], 'License not found.'));
}
}
}
$this->array_to_csv_download($errors, 'Acronis_validation_errors_report_' . $id . '.csv');
die;
}
/**
* @Route("/invoice/usages/processacronisreport/{id}", name="usages_process_acronis_report", requirements={"id"="\d+"})
*/
public function processacronisreportAction($id, TrafficTracker $trafficTracker, RegisterEvent $registerEvent, CsvDataImporter $csvDataImporter, GenerateInvoiceArticleDescriptionsService $descriptionService)
{
$trafficTracker->registerTraffic();
/** @var EntityManager */
$entityManager = $this->getDoctrine()->getManager();
$vendor = $entityManager->createQuery("SELECT v FROM App\Entity\Vendors v WHERE v.id = 14")->getOneOrNullResult();
$session = $this->get('session');
// find report to process
$report = $entityManager->createQuery("SELECT ar FROM App\Entity\Apireports ar
WHERE ar.id = :id")
->setParameter("id", $id)
->getOneOrNullResult();
$user = $this->getUser();
$agreementWholesaler = $entityManager->createQuery("SELECT a FROM App\Entity\Agreements a
JOIN a.programm p
WHERE p.id = 9
AND a.wholesaler = :wholesaler")
->setParameter("wholesaler", $user->getWholesaler())
->getOneOrNullResult();
if (!$report) {
$session->getFlashBag()->add('warning', 'Informe de consumos no encontrado. No se han procesado consumos.');
return $this->redirect($this->generateUrl('usages_acronis'));
}
$this->calculate_acronis_partner_levels($report, $csvDataImporter);
$acronis_products = $entityManager->createQuery("SELECT p FROM App\Entity\Product p
JOIN p.productLine pl
WHERE pl.vendor = :vendor")
->setParameter("vendor", $vendor)
->getResult();
$acronis_skus = array();
foreach ($acronis_products as $p) {
$acronis_skus[] = $p->getProductVendorId();
}
// Process customer usages
$linescreated = 0;
$usages = $this->pre_pocess_report($report, 'usages', $csvDataImporter);
foreach ($usages as $u) {
if (in_array($u['name'], $acronis_skus)) {
$agreement = $entityManager->createQuery("SELECT a FROM App\Entity\Agreements a
JOIN a.programm p
WHERE p.id = 9
AND a.parameters like :parameters")
->setParameter("parameters", '{%"tenant_id": "' . $u['tenant.id'] . '"%}')
->setMaxResults(1)
->getOneOrNullResult();
$license = $entityManager->createQuery("SELECT l
FROM App\Entity\License l
WHERE l.licenseid = :tenant_id")
->setParameter("tenant_id", $u['tenant.id'])
->setMaxResults(1)
->getOneOrNullResult();
$customer = $agreement ? $agreement->getCustomer() : NULL;
if ($customer && $license) {
// Check unit
if ($u['measurement_unit'] == "bytes") {
//Convertir bytes en Gb
$bytes_production = intval($u['usage.effective.production']);
$usage_production = round($bytes_production / pow(1024, 3), 2);
$bytes_trial = intval($u['usage.effective.trial']);
$usage_trial = round($bytes_trial / pow(1024, 3), 2);
} elseif ($u['name'] === 'compute_points') {
// compute_points viene en segundos -> convertir a horas y redondear al alza.
$usage_production = ceil(($u['usage.effective.production'] / 3600));
$usage_trial = ceil(($u['usage.effective.trial'] / 3600));
} else {
$usage_production = $u['usage.effective.production'];
$usage_trial = $u['usage.effective.trial'];
}
//Create invoice article
$product = $this->getDoctrine()->getRepository(Product::class)->findOneBy(array('productvendorid' => $u['name']));
if ($usage_production > 0) {
$this->generate_invoice_article($usage_production, $customer, $product, $report, false, $descriptionService, $license);
$linescreated += 1;
}
if ($usage_trial > 0) {
$this->generate_invoice_article($usage_trial, $customer, $product, $report, true, $descriptionService, $license);
$linescreated += 1;
}
}
}
}
$today = new DateTime('now');
$report->setdateProcessed($today);
$entityManager->persist($report);
$entityManager->flush();
$registerEvent->addModifiedBy($report, $user->getId());
$session->getFlashBag()->add('success', 'Se han creado ' . $linescreated . ' lineas de factura');
return $this->redirect($this->generateUrl('usages_acronis', ['agreement_id' => $agreementWholesaler->getId()]));
}
/**
* @Route("/invoice/usages/downloadacronisreport/{id}/{agreement_id}", name="usages_download_acronis_report", requirements={"id"="\d+","agreement_id"="\d+"})
*/
public function downloadacronisreportAction($id, $agreement_id, AcronisClient $acronisClient, TrafficTracker $trafficTracker, TranslatorInterface $translator)
{
$trafficTracker->registerTraffic();
/** @var EntityManager */
$entityManager = $this->getDoctrine()->getManager();
$session = $this->get('session');
$report = $entityManager->createQuery("SELECT ar FROM App\Entity\Apireports ar
WHERE ar.id = :id")
->setParameter("id", $id)
->getOneOrNullResult();
$reportStored = $acronisClient->save_acronis_usage_report($report, $agreement_id);
if (is_null($reportStored['status'])) {
$session->getFlashBag()->add('danger', 'No se ha podido descargar el reporte');
} elseif ($reportStored['status'] == 'in_progress') {
$session->getFlashBag()->add('warning', $translator->trans('app.Sales&Invoicing.ProcessingTools.ProcessingMessageAcronis'));
} else {
$session->getFlashBag()->add('success', 'Guardado con éxito');
}
return $this->redirect($this->generateUrl('usages_acronis', array('agreement_id' => $agreement_id)));
}
/**
* @Route("/invoice/usages/katy_manual", name="usages_katy_manual")
*/
public function manualUsages(Request $request, TrafficTracker $trafficTracker)
{
$trafficTracker->registerTraffic();
$programmsRepository = $this->getDoctrine()->getRepository(Vendorprogramms::class);
$programms['malwarebytes'] = $programmsRepository->findOneBy(['vendorapi' => 'threatdown']);
return $this->render('usages/index_katy.html.twig', [
'programms' => $programms,
]);
}
// PRIVATE FUNCTIONS //
private function createAcronisReport($periodStart, $periodEnd, $agreement_id, AcronisClient $acronisClient, RegisterEvent $registerEvent)
{
/** @var EntityManager */
$entityManager = $this->getDoctrine()->getManager();
$vendor = $entityManager->createQuery("SELECT v FROM App\Entity\Vendors v WHERE v.id = 14")->getOneOrNullResult();
$currentUser = $this->getUser();
// Solicitar Report a Acronis
$newReport = $acronisClient->acronis_request_usage_report($periodStart, $periodEnd, $agreement_id);
// Check if report has been created
if (isset($newReport['id'])) {
//Persist report in data base
$report = new Apireports();
$report->setIdVendorReport($newReport['id']);
$report->setPeriodStart(new DateTime($newReport['parameters']['period']['start']));
$report->setPeriodEnd(new DateTime($newReport['parameters']['period']['end']));
$report->setGenerationDate(new DateTime($newReport['generation_date']));
$report->setReportType('acronis_usages');
$report->setVendor($vendor);
$report->setWholesaler($currentUser->getWholesaler());
$entityManager->persist($report);
$entityManager->flush();
$registerEvent->addCreatedBy($report, $currentUser->getId());
//Try retrieve report create
$acronisClient->acronis_usage_report($report, $agreement_id);
} else {
return false;
}
return $report;
}
private function array_to_csv_download($array, $filename = "export.csv", $delimiter = ";")
{
header('Content-Type: application/csv');
header('Content-Disposition: attachment; filename="' . $filename . '";');
$f = fopen('php://output', 'w');
foreach ($array as $line) {
fputcsv($f, $line, $delimiter);
}
fclose($f);
}
private function pre_pocess_report($report, $type, CsvDataImporter $csvDataImporter)
{
$wholesaler = $report->getWholesaler()->getId();
if ($type == 'usages') {
$tenant_types = array('customer');
} else if ($type == 'partners') {
$tenant_types = array('partner');
}
$raw_usages = $csvDataImporter->processCSV($this->getParameter('private_folder') . 'reports/' . $wholesaler . '/Acronis/' . $report->getFile() . '.csv');
$usages = array();
foreach ($raw_usages as $item) {
if (($item['usage.effective.production'] > 0 || $item['usage.effective.trial'] > 0) && (in_array($item['tenant.kind'], $tenant_types))) {
array_push($usages, $item);
}
}
return $usages;
}
private function get_tenants_from_report($report, $tenant_type, CsvDataImporter $csvDataImporter)
{
$wholesaler = $report->getWholesaler()->getId();
$raw_usages = $csvDataImporter->processCSV($this->getParameter('private_folder') . 'reports/' . $wholesaler . '/Acronis/' . $report->getFile() . '.csv');
if ($tenant_type == 'all') {
$tenant_types = array('partner', 'customer');
} else if ($tenant_type == 'partners') {
$tenant_types = array('partner');
} else if ($tenant_type == 'customers') {
$tenant_types = array('customer');
}
$tenants = array();
foreach ($raw_usages as $item) {
if (($item['usage.effective.production'] > 0 || $item['usage.effective.trial'] > 0) && (in_array($item['tenant.kind'], $tenant_types))) {
$tenants[$item['tenant.id']] = array($item['tenant.name'], $item['tenant.kind']);
}
}
return $tenants;
}
private function calculate_acronis_partner_levels($report, CsvDataImporter $csvDataImporter)
{
/** @var EntityManager */
$entityManager = $this->getDoctrine()->getManager();
//Antes de empezar el proceso poner todos los partnerlevels del fabricante Acronis a 1
$partners = $this->get_tenants_from_report($report, 'partners', $csvDataImporter);
$vendor = $entityManager->createQuery("SELECT v FROM App\Entity\Vendors v WHERE v.id = 14")->getOneOrNullResult();
foreach ($partners as $key => $value) {
$agreement = $entityManager->createQuery("SELECT a FROM App\Entity\Agreements a
JOIN a.programm p
WHERE p.id = 9
AND a.parameters like :parameters
AND a.cancellationdate is not null")
->setParameter("parameters", '{%"tenant_id": "' . $key . '"%}')
->getOneOrNullResult();
$partner = $agreement ? $agreement->getPartner() : false;
if ($partner) {
$plbv = $entityManager->createQuery("SELECT plbv FROM App\Entity\Partnerlevelbyvendor plbv
WHERE plbv.vendor = :vendor
AND plbv.partner = :partner")
->setParameter("partner", $partner)
->setParameteR("vendor", $vendor)
->getOneOrNullResult();
if (!is_null($plbv)) {
$plbv->setLevel(0);
$entityManager->persist($plbv);
$entityManager->flush();
}
}
}
$acronis_products = $entityManager->createQuery("SELECT p FROM App\Entity\Product p
JOIN p.productLine pl
WHERE pl.vendor = :vendor")
->setParameter("vendor", $vendor)
->getResult();
$acronis_skus = array();
foreach ($acronis_products as $p) {
$acronis_skus[] = $p->getSku();
}
//Calculate Partner Levels
$usages = $this->pre_pocess_report($report, 'partners', $csvDataImporter);
foreach ($usages as $u) {
if (in_array($u['name'], $acronis_skus) && $u['usage.effective.production']) {
$agreement = $entityManager->createQuery("SELECT a FROM App\Entity\Agreements a
JOIN a.programm p
WHERE p.id = 9
AND a.parameters like :parameters")
->setParameter("parameters", '{%"tenant_id": "' . $u['tenant.id'] . '"%}')
->getOneOrNullResult();
$partner = $agreement ? $agreement->getPartner() : NULL;
if ($partner) {
//Check if vendor has partnerLevelByVendor
$partnerLevelByVendor = $entityManager->createQuery("SELECT plbv FROM App\Entity\Partnerlevelbyvendor plbv
WHERE plbv.vendor = :vendor
AND plbv.partner = :partner")
->setParameter("vendor", $vendor)
->setParameter("partner", $partner)
->getOneOrNullResult();
//if not we create one for him.
if (!$partnerLevelByVendor) {
$partnerLevelByVendor = new Partnerlevelbyvendor();
$partnerLevelByVendor->setPartner($partner);
$partnerLevelByVendor->setVendor($vendor);
$partnerLevelByVendor->setLevel(1);
$entityManager->persist($partnerLevelByVendor);
$entityManager->flush();
}
// Check unit
if ($u['measurement_unit'] == "bytes") {
$bytes = intval($u['usage.effective.production']);
} else {
$bytes = 0;
}
// Set partnerLevel according to
if ($bytes < 2748779069439) {
if ($partnerLevelByVendor->getLevel() < 1) {
$partnerLevelByVendor->setLevel(1);
}
} else if ($bytes <= 16492674416639) {
if ($partnerLevelByVendor->getLevel() < 2) {
$partnerLevelByVendor->setLevel(2);
}
} else if ($bytes <= 32985348833279) {
if ($partnerLevelByVendor->getLevel() < 3) {
$partnerLevelByVendor->setLevel(3);
}
} else if ($bytes > 32985348833279) {
if ($partnerLevelByVendor->getLevel() < 4) {
$partnerLevelByVendor->setLevel(4);
}
}
$entityManager->persist($partnerLevelByVendor);
$entityManager->flush();
}
}
}
}
private function generate_invoice_article($usage, $customer, $product, $report, $trial = false, GenerateInvoiceArticleDescriptionsService $descriptionService, $license)
{
/** @var EntityManager */
$entityManager = $this->getDoctrine()->getManager();
/** @var PartnerRepository */
$partnerRepository = $this->getDoctrine()->getRepository(Partner::class);
/** @var ProductRepository */
$productRepository = $this->getDoctrine()->getRepository(Product::class);
/** @var ProductRepository */
$licenseRepository = $this->getDoctrine()->getRepository(License::class);
/** @var ProductbywholesalerRepository */
$productByWholesalerRepository = $this->getDoctrine()->getRepository(Productbywholesaler::class);
$taxRepository = $this->getDoctrine()->getRepository(Taxes::class);
//Find productByWholesaler and discount.
$productByWholesaler = $entityManager->createQuery("SELECT pbw FROM App\Entity\Productbywholesaler pbw
WHERE pbw.product = :product
AND pbw.wholesaler = :wholesaler")
->setParameter("product", $product)
->setParameter("wholesaler", $customer->getPartner()->getWholesaler())
->getOneOrNullResult();
$partnerLevel = $partnerRepository->getPartnerLevel($customer->getPartner(), $product->getProductline()->getVendor());
$discount = $productRepository->getDiscount($product, $customer->getPartner()->getWholesaler(), $partnerLevel);
$price = $trial ? 0 : $productByWholesalerRepository->getPrice($productByWholesaler, false);
$priceConverted = $trial ? 0 : $productByWholesalerRepository->getPrice($productByWholesaler);
if ($productByWholesaler) {
$tax = $taxRepository->getTaxForPbw($productByWholesaler);
$taxValue = $tax ? ($price * $tax->getValue()) / 100 : 0;
$taxValueConverted = $tax ? ($priceConverted * $tax->getValue()) / 100 : 0;
} else {
$taxValue = 0;
$taxValueConverted = 0;
}
$newInvoiceArticleToPartner = new Invoicearticle();
$newInvoiceArticleToPartner->setSale($licenseRepository->getActiveSale($license));
$newInvoiceArticleToPartner->setInvoiceTo('partner');
$newInvoiceArticleToPartner->setCustomer($customer);
$newInvoiceArticleToPartner->setPartner($customer->getPartner());
$newInvoiceArticleToPartner->setSku($product->getSku());
$description = $descriptionService->distributeVendorsDescriptions('Acronis', array(
'client' => $customer->getCompany(),
'vendor' => $product->getProductline()->getVendor()->getName(),
'product' => $product->getName(),
'period' => $report->getPeriodStart()->format('m-Y')
));
$newInvoiceArticleToPartner->setDescription($description);
$newInvoiceArticleToPartner->setQuantity($usage);
$newInvoiceArticleToPartner->setPrice($price);
$newInvoiceArticleToPartner->setPriceConverted($priceConverted);
$newInvoiceArticleToPartner->setDiscount($discount);
$importPartner = $usage * ($price * (100 - $discount) / 100);
$importConvertedPartner = $usage * ($priceConverted * (100 - $discount) / 100);
$newInvoiceArticleToPartner->setImport($importPartner);
$newInvoiceArticleToPartner->setImportConverted($importConvertedPartner);
$newInvoiceArticleToPartner->setImportTax($importPartner + ($taxValue * $usage));
$newInvoiceArticleToPartner->setImportConvertedTax($importConvertedPartner + ($taxValueConverted * $usage));
$newInvoiceArticleToPartner->setExchangeRate($productByWholesalerRepository->getExchangeRate($productByWholesaler) ? $productByWholesalerRepository->getExchangeRate($productByWholesaler) : null);
$newInvoiceArticleToPartner->setgeneratedDate(date_timestamp_get(date_create()));
$newInvoiceArticleToPartner->setPayperiod($product->getPayperiod());
$newInvoiceArticleToPartner->setProduct($product);
$newInvoiceArticleToPartner->setMonth($report->getperiodstart()->format('m'));
$newInvoiceArticleToPartner->setYear($report->getperiodstart()->format('Y'));
$entityManager->persist($newInvoiceArticleToPartner);
//Create invoice article to customer
$newInvoiceArticleToCustomer = new Invoicearticle();
$newInvoiceArticleToCustomer->setSale($licenseRepository->getActiveSale($license));
$newInvoiceArticleToCustomer->setInvoiceTo('customer');
$newInvoiceArticleToCustomer->setCustomer($customer);
$newInvoiceArticleToCustomer->setPartner($customer->getPartner());
$newInvoiceArticleToCustomer->setSku($product->getSku());
$description = $product->getProductline()->getVendor()->getName() . ' - ' . $product->getName() . ' ' . $report->getPeriodStart()->format('m-Y');
$newInvoiceArticleToCustomer->setDescription($description);
$newInvoiceArticleToCustomer->setQuantity($usage);
$newInvoiceArticleToCustomer->setPrice($price);
$newInvoiceArticleToCustomer->setPriceConverted($priceConverted);
$newInvoiceArticleToCustomer->setDiscount(0);
$importCustomer = $usage * $price;
$importConvertedCustomer = $usage * ($priceConverted * (100 - $discount) / 100);
$newInvoiceArticleToCustomer->setImport($importCustomer);
$newInvoiceArticleToCustomer->setImportConverted($importConvertedCustomer);
$newInvoiceArticleToCustomer->setImportTax($importCustomer + ($taxValue * $usage));
$newInvoiceArticleToCustomer->setImportConvertedTax($importConvertedCustomer + ($taxValueConverted * $usage));
$newInvoiceArticleToCustomer->setExchangeRate($productByWholesalerRepository->getExchangeRate($productByWholesaler) ? $productByWholesalerRepository->getExchangeRate($productByWholesaler) : null);
$newInvoiceArticleToCustomer->setgeneratedDate(date_timestamp_get(date_create()));
$newInvoiceArticleToCustomer->setPayperiod($product->getPayperiod());
$newInvoiceArticleToCustomer->setProduct($product);
$newInvoiceArticleToCustomer->setMonth($report->getperiodstart()->format('m'));
$newInvoiceArticleToCustomer->setYear($report->getperiodstart()->format('Y'));
$entityManager->persist($newInvoiceArticleToCustomer);
}
/**
* @Route("/invoice/usages/azure-billing/{agreement_id}", name="usages_azure_billing")
*/
public function azureBillingAction(Request $request, int $agreement_id, AzureBilledReconciliationService $reconciliationService, TrafficTracker $trafficTracker, TaskRepository $taskRepository, TranslatorInterface $translator): Response
{
$trafficTracker->registerTraffic();
$session = $this->get('session');
$em = $this->getDoctrine()->getManager();
$agreement = $em->getRepository(Agreements::class)->find($agreement_id);
if (!$agreement) {
throw $this->createNotFoundException('Agreement not found');
}
$form = $this->createForm(RequestAzureBillingExport::class);
$form->handleRequest($request);
if ($form->isSubmitted() && $form->isValid()) {
try {
$invoiceId = $form->getData()['invoiceId'];
$reconciliationService->requestExport($agreement, $invoiceId);
$em->flush();
$session->getFlashBag()->add('success', $translator->trans('app.General.SystemMessages.RequestSubmitted'));
} catch (\Exception $e) {
$session->getFlashBag()->add('danger', $translator->trans('app.General.SystemMessages.RequestNotSubmitted'));
}
return $this->redirectToRoute('usages_azure_billing', ['agreement_id' => $agreement_id]);
}
$tasks = $taskRepository->findMsBillingTasksByAgreementId($agreement_id);
return $this->render('usages/azure_billing.html.twig', [
'form' => $form->createView(),
'tasks' => $tasks,
'agreement_id' => $agreement_id,
]);
}
}

src/Controller/UsagesController.php line 753