Symfony Profiler

<?php
namespace App\Controller;
use App\Entity\Member;
use App\Entity\Opportunity;
use App\Entity\Procurement;
use App\Entity\Product;
use App\Entity\Slide;
use App\Entity\UpdateInfoRequest;
use App\Form\UpdateInfoRequestFormType;
use App\Repository\MemberRepository;
use App\Repository\OpportunityRepository;
use App\Repository\ProcurementRepository;
use App\Repository\ProductRepository;
use App\Repository\SlideRepository;
use App\Service\CommonService;
use App\Service\Helper;
use App\Service\ReportApiService;
use Doctrine\Common\Util\ClassUtils;
use Doctrine\DBAL\Exception;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\Persistence\Proxy;
use GuzzleHttp\Client;
use GuzzleHttp\Exception\GuzzleException;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\Routing\Annotation\Route;
use Symfony\Component\Serializer\Normalizer\ObjectNormalizer;
use Symfony\Component\Serializer\Serializer;
class IndexController extends BaseController
{
/**
* @Route("/", name="app_index")
*/
public function indexAction()
{
$select_year = !empty($_GET['year']) ? $_GET['year'] : '2024';
$years = ['2024', '2023', '2022', '2021', '2020'];
$user = $this->getUser();
/** @var SlideRepository $repo */
$repo = $this->doctrine->getRepository(Slide::class);
$qb = $repo->createQueryBuilder('s')
->andWhere('s.expirationDate > :expirationDate OR s.expirationDate IS NULL OR s.isDefault = 1')
->setParameter('expirationDate', new \DateTime())
->getQuery()
->getResult();
// /** @var ProcurementRepository $procurement_repo */
// $procurement_repo = $this->doctrine->getRepository(Procurement::class);
// $qb_procurement = $procurement_repo->createQueryBuilder('p')
// ->select('SUM(p.amount) as amount')
// ->join('App:Member', 'm', 'with', 'm.name = p.recipient')
// ->andWhere('p.spendYear = :year')->setParameter('year', '2022')
// ->andWhere('(p.type = \'Subscriber\' or p.type = \'Certified_Supplier\')');
// $procurement = $qb_procurement->getQuery()->getArrayResult();
$revenueData = ReportApiService::getReportData('revenue_by_industry?year='. $select_year);
//echo '<pre>'; print_r($revenueData); echo '</pre>'; exit();
$procurementData = ReportApiService::getReportData('procurement_by_industry?year='. $select_year);
$totalRevenue = 0;
$totalProcurement = 0;
$revenue = $revenueData ? $revenueData['data'] : [];
foreach ($revenue as $item) {
$totalRevenue += round($item['revenue'], 2);
}
$procurement = $procurementData ? $procurementData['data'] : [];
foreach ($procurement as $item) {
$totalProcurement += round($item['amount'], 2);
}
/** @var MemberRepository $member_repo */
$member_repo = $this->doctrine->getRepository(Member::class);
$qb_member = $member_repo->createQueryBuilder('m')
->select('SUM(m.revenue) as revenue')
->andWhere('(m.type = \'MBE\' or m.type = \'Subscriber\')');
//->andWhere('m.status = :status')
//->setParameter('status', 'Certified')
$revenue = $qb_member->getQuery()->getArrayResult();
/** @var OpportunityRepository $opp_repo */
$category = CommonService::$choices['industry_list'];
$opp_repo = $this->doctrine->getRepository(Opportunity::class);
$opportunities = $opp_repo->createQueryBuilder('o');
$opp_total = $opportunities->select('COUNT(o.id) AS total')->getQuery()->getOneOrNullResult();
$result = [];
$opportunities_count = 0;
foreach ($category as $item) {
$opportunities
->select('COUNT(o.id) AS total, o.industry')
->andWhere("o.industry LIKE :searchTerm")
->setParameter('searchTerm', "%\"{$item}\"%")
->andWhere('o.status = :status')
->setParameter('status', 'Active');
$qr = $opportunities->getQuery()->getResult();
$result[$item] = $qr[0]['total'] > 0 ? $qr[0]['total'] : 0;
if($result[$item] > 0){
$opportunities_count += $result[$item];
}
}
/** @var ProductRepository $pro_repo */
$pro_repo = $this->doctrine->getRepository(Product::class);
$bundle = $pro_repo->createQueryBuilder('p')
->andWhere('p.deleted = 0')
->andWhere('p.status = :status')
->setParameter('status', 'Active')
->getQuery()
->getResult();
return $this->render('index/index.twig', [
'images' => $qb,
'procurement' => $totalProcurement,
'revenue' => $totalRevenue,
'opportunities' => $opportunities_count,
'opportunities_total' => $opp_total ? $opp_total['total'] : 0,
'bundle' => $bundle,
'terms' => $user->getUserAcceptedTerms2() ? 1 : 0,
'years' => $years,
'select_year' => $select_year
]);
}
/**
* @Route("/chart-data/mbe-number-employees-data-chart", name="app_chart_mbe_employees_data")
*/
public function MBEsNumberFTimeEmployeesDataChart(Request $request)
{
$chartType = $request->get('chartType');
$sql = "
SELECT
COUNT(*) as c,
CASE
WHEN employees BETWEEN 0 AND 5 then '1-5'
WHEN employees BETWEEN 6 AND 10 then '6-10'
WHEN employees BETWEEN 11 AND 100 then '11-100'
WHEN employees BETWEEN 101 AND 500 then '101-500'
WHEN employees > 500 then '500+'
END AS numOfEmployees
FROM member
WHERE type = 'MBE' AND status = 'Certified'
GROUP BY numOfEmployees
ORDER BY employees
";
try {
$stmt = $this->em->getConnection()->prepare($sql);
$rows = $stmt->executeQuery()->fetchAllAssociative();
if ($chartType == 'echart') {
$data = [];
foreach ($rows as $row) {
if (empty($row['numOfEmployees'])) {
$row['numOfEmployees'] = 'Unclassified';
}
$data[] = array(
'name' => $row['numOfEmployees'], 'value' => intval($row['c'])
);
}
} else {
$data = [['Num Of Employees', 'Number']];
foreach ($rows as $row) {
if (empty($row['numOfEmployees'])) {
$row['numOfEmployees'] = 'Unclassified';
}
$data[] = [
$row['numOfEmployees'], intval($row['c'])
];
}
}
return $this->json([
'error' => 0,
'data' => $data,
]);
} catch (Exception $e) {
return $this->json([
'error' => 1,
'message' => $e->getMessage(),
]);
}
}
/**
* @Route("/chart-data/mbe-revenue-class-data-chart", name="app_chart_mbe_revenue_class_data")
*/
public function MBEsByClassOrRevenueDataChart(Request $request)
{
$chartType = $request->get('chartType');
$labels = [
'class_1' => 'Class 1 (under $1M)',
'class_2' => 'Class 2 ($1M - $10M)',
'class_3' => 'Class 3 ($10M - $50M)',
'class_4' => 'Class 4 ($50M - Above)',
];
$sql = "
SELECT
COUNT(*) as c,
revenue_class AS revenueClass
FROM member
WHERE type = 'MBE' AND status = 'Certified' AND revenue_class != '' AND revenue_class IS NOT NULL
GROUP BY revenueClass
ORDER BY revenueClass
";
try {
$stmt = $this->em->getConnection()->prepare($sql);
$rows = $stmt->executeQuery()->fetchAllAssociative();
if ($chartType == 'echart') {
$data = [];
foreach ($rows as $item) {
$label = (!empty($labels[$item['revenueClass']])) ? $labels[$item['revenueClass']] : $item['revenueClass'];
$data[] = [
'name' => $label, 'value' => intval($item['c'])
];
}
} else {
$data = [['Class', 'Number']];
foreach ($rows as $row) {
$label = (!empty($labels[$row['revenueClass']])) ? $labels[$row['revenueClass']] : $row['revenueClass'];
$data[] = [
$label, intval($row['c'])
];
}
}
return $this->json([
'error' => 0,
'data' => $data,
]);
} catch (Exception $e) {
return $this->json([
'error' => 1,
'message' => $e->getMessage(),
]);
}
}
/**
* @Route("/chart-data/mbe-year-bussiness-data-chart", name="app_chart_mbe_year_bussiness_data")
*/
public function MBEsYearsInBusinessDataChart(Request $request)
{
$chartType = $request->get('chartType');
$currentYear = date('Y');
$sql = "
SELECT
COUNT(*) as c,
CASE
WHEN ({$currentYear} - year_established) BETWEEN 0 AND 4 OR year_established IS NULL then '0-4 Years'
WHEN ({$currentYear} - year_established) BETWEEN 5 AND 10 then '5-10 Years'
WHEN ({$currentYear} - year_established) BETWEEN 11 AND 20 then '11-20 Years'
WHEN ({$currentYear} - year_established) BETWEEN 21 AND 20000 then '20+ Years'
END AS mbeYear
FROM member
WHERE type = 'MBE' AND status = 'Certified'
GROUP BY mbeYear
";
try {
$stmt = $this->em->getConnection()->prepare($sql);
$rows = $stmt->executeQuery()->fetchAllAssociative();
if ($chartType == 'echart') {
$data = [
'0-4 Years' => 0,
'5-10 Years' => 0,
'11-20 Years' => 0,
'20+ Years' => 0,
];
$colors = array(0 => '#73c0de', 1 => '#91cc75', 2 => '#fac858', 3 => '#ee6666');
foreach ($rows as $row) {
$data[$row['mbeYear']] = intval($row['c']);
}
$label = [];
$value = [];
$i = 0;
foreach ($data as $title => $c) {
$label[] = $title;
$value[] = [
'value' => intval($c),
'itemStyle' => array(
'color' => $colors[$i]
),
];
$i++;
}
return $this->json([
'error' => 0,
'label' => $label,
'data' => $value,
]);
} else {
$data = [
'Year' => 'Number',
'20+ Years' => 0,
'11-20 Years' => 0,
'5-10 Years' => 0,
'0-4 Years' => 0,
];
foreach ($rows as $row) {
$data[$row['mbeYear']] = intval($row['c']);
}
$chart = [];
foreach ($data as $label => $c) {
$chart[] = [$label, $c];
}
return $this->json([
'error' => 0,
'data' => $chart,
]);
}
} catch (Exception $e) {
return $this->json([
'error' => 1,
'message' => $e->getMessage(),
]);
}
}
/**
* @Route("/chart-data/mbe-ethnicity-data-chart", name="app_chart_mbe_ethnicity_data")
*/
public function MBEOwnersByEthnicityDateChart(Request $request)
{
$chartType = $request->get('chartType');
$sql = "
SELECT
COUNT(*) as c,
ethnicity
FROM member
WHERE type = 'MBE' AND status = 'Certified'
GROUP BY ethnicity
ORDER BY ethnicity
";
try {
$stmt = $this->em->getConnection()->prepare($sql);
$rows = $stmt->executeQuery()->fetchAllAssociative();
if ($chartType == 'echart') {
$data = [];
foreach ($rows as $row) {
if (empty($row['ethnicity'])) {
$row['ethnicity'] = 'Unclassified';
}
$data[] = [
'name' => $row['ethnicity'], 'value' => intval($row['c'])
];
}
} else {
$data = [['Ethnicity', 'Number']];
foreach ($rows as $row) {
if (empty($row['ethnicity'])) {
$row['ethnicity'] = 'Unclassified';
}
$data[] = [
$row['ethnicity'], intval($row['c'])
];
}
}
return $this->json([
'error' => 0,
'data' => $data,
]);
} catch (Exception $e) {
return $this->json([
'error' => 1,
'message' => $e->getMessage(),
]);
}
}
/**
* @Route("/chart-data/mbe-procurement-industry-data-chart", name="app_chart_mbe_procurement_industry_data")
*/
public function MBEProcurementByIndustryDataChart(Request $request)
{
$chartType = $request->get('chartType');
$selectedYear = $request->get('selectedYear');
$repo = $this->doctrine->getRepository(Procurement::class);
/** @var ProcurementRepository $repo */
$qb = $repo->createQueryBuilder('p')
->select('
m.industry,
SUM(p.amount) as amount
')
->join('App:Member', 'm', 'with', 'm.fei = p.fei AND (p.fei IS NOT NULL AND p.fei <> \'\')')
->andWhere('m.type = :MBE')
->setParameter('MBE', 'MBE')
->andWhere('p.spendYear = :spendYear')
->setParameter('spendYear', $selectedYear)
->groupBy('m.industry')
->orderBy('amount', 'desc');
$result = $qb->getQuery()->getResult();
if ($chartType == 'echart') {
$data = [];
foreach ($result as $item) {
if (empty($item['industry'])) {
$item['industry'] = 'Unclassified';
}
$data[] = array(
'name' => $item['industry'], 'value' => intval($item['amount'])
);
}
} else {
$data = [['Industry', 'Amount']];
foreach ($result as $item) {
if (empty($item['industry'])) {
$item['industry'] = 'Unclassified';
}
$data[] = [
$item['industry'], intval($item['amount'])
];
}
}
return $this->json([
'error' => 0,
'data' => $data,
]);
}
/**
* @Route("/chart-data/mbe-procurement-ethnicity-data-chart", name="app_chart_mbe_procurement_ethnicity_data")
*/
public function MBEProcurementByEthnicityDataChart(Request $request)
{
$chartType = $request->get('chartType');
$selectedYear = $request->get('selectedYear');
/** @var ProcurementRepository $repo */
$repo = $this->doctrine->getRepository(Procurement::class);
$qb = $repo->createQueryBuilder('p')
->select('
m.ethnicity,
SUM(p.amount) as amount
')
->join('App:Member', 'm', 'with', 'm.fei = p.fei AND (p.fei IS NOT NULL AND p.fei <> \'\')')
->andWhere('m.type = :MBE')
->setParameter('MBE', 'MBE')
->andWhere('p.spendYear = :spendYear')
->setParameter('spendYear', $selectedYear)
->groupBy('m.ethnicity')
->orderBy('m.ethnicity', 'asc');
$result = $qb->getQuery()->getResult();
if ($chartType == 'echart') {
$data = [];
foreach ($result as $row) {
if (empty($row['ethnicity'])) {
$row['ethnicity'] = 'Unclassified';
}
$data[] = array(
'name' => $row['ethnicity'], 'value' => intval($row['amount'])
);
}
} else {
$data = [['Ethnicity', 'Amount']];
foreach ($result as $row) {
if (empty($row['ethnicity'])) {
$row['ethnicity'] = 'Unclassified';
}
$data[] = [
$row['ethnicity'], intval($row['amount'])
];
}
}
return $this->json([
'error' => 0,
'data' => $data,
]);
}
/**
* @Route("/terms-of-use-disclaimer", name="app_terms_of_use_disclaimer")
*/
public function TermsOfUseDisclaimerFinal(Request $request)
{
$user = $this->getUser();
$user->setAcceptedTerms(true);
$user->setAcceptedTermsTime(new \DateTime('now'));
$this->doctrine->getManager()->persist($user);
$this->doctrine->getManager()->flush();
return $this->redirectToRoute('app_index');
}
/**
* @Route("/terms-of-use-disclaimer-2", name="app_terms_of_use_disclaimer_2")
*/
public function TermsOfUseDisclaimerFinal2(Request $request)
{
$user = $this->getUser();
$user->setUserAcceptedTerms2(true);
$user->setUserAcceptedTermsTime2(new \DateTime('now'));
$this->doctrine->getManager()->persist($user);
$this->doctrine->getManager()->flush();
return $this->redirectToRoute('app_index');
}
/**
* @Route("/update-info-request", name="app_update_info_request_new_2")
*/
public function updateCompanyInfoAction(Request $request, EntityManagerInterface $entityManager)
{
$member = '';
$user = $this->getUser();
if (!empty($user->getMember())){
$member = $user->getMember();
}
$updateRequest = new UpdateInfoRequest();
$updateRequest->setCompany($member);
$form = $this->createForm(UpdateInfoRequestFormType::class, $updateRequest);
$form->handleRequest($request);
if ($form->isSubmitted() && $form->isValid()) {
$data = $form->getData();
$updateRequest->setStatus('Synced');
foreach ($data as $key => $value) {
$updateRequest->set($key, $value);
}
if (!$this->isGranted('ROLE_ADMIN') && !$this->isGranted('ROLE_MANAGER')){
$updateRequest->setCompany($member);
}
$signatureData = $request->request->get('signature_pad_input');
$updateRequest->setCpoSignature($signatureData);
$this->doctrine->getManager()->persist($updateRequest);
$this->doctrine->getManager()->flush();
$this->sync_info_request($entityManager, $updateRequest);
$this->addFlash('success', 'Save successful');
return $this->redirectToRoute('app_update_info_request_new_2');
}
return $this->render('index/update_info_request.html.twig', [
'companyInfoForm' => $form->createView(),
]);
}
function sync_info_request($entityManager, UpdateInfoRequest $info){
if ($info instanceof Proxy) {
$info = $entityManager->find(ClassUtils::getRealClass(get_class($info)), $info->getId());
}
$normalizer = new ObjectNormalizer(null, null, null, null, null, null, [
'circular_reference_handler' => function ($object) {
return $object->getId(); // Return only the ID
}
]);
$serializer = new Serializer([$normalizer]);
$data = $serializer->normalize($info, null, ['groups' => 'member:read']);
$client = new Client(['timeout' => 1]);
try {
$client->request('POST', $_ENV['WEBHOOK_UPDATE_INFO_REQUEST'], ['form_params' => $data]);
} catch (GuzzleException | \Exception $e) {
Helper::fileLog($e->getMessage(), 'ERROR', 'MemberUpdate');
}
}
}

src/Controller/IndexController.php line 38