Symfony Profiler

<?php
/**
* Created by PhpStorm.
* User: POlleros
* Date: 12/11/2017
* Time: 11:04 AM
*/
namespace Globals\CoreBundle\Controller;
use Globals\CustomerManagementBundle\Entity\AutoDiary;
use Globals\CustomerManagementBundle\Form\AutoDiaryType;
use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
use Globals\CustomerManagementBundle\Entity\Customer;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\JsonResponse;
use Globals\CoreBundle\Helpers\DbUtilities;
use DateTime;
use Globals\ClaimManagementBundle\Entity\Claim;
/**
* @Route("/administration")
*/
class LicensesMaintenanceWidgetController extends Controller
{
/**
* @Route("/licenses/show")
*/
public function showLicenses()
{
return $this->render('@Core/Widget/widget_licenses_maintenance.html.twig', array(
'active_class' => 'diary'
));
}
# Load Datatables
/**
* @Route("/licenses/data")
*/
public function licensesWidgetData(Request $request)
{
$authChecker = $this->container->get('security.authorization_checker');
$user = $this->container->get('security.token_storage')->getToken()->getUser();
$dropdownTeamValue = $request->query->get("dropdownTeamValue");
$resourceId = $user->getId();
//echo "LOOK::: " . $dropdownTeamValue;
# Array of columns that will be sorted
$columnsArr = [
"user_name",
"licenseNum",
"stateCode",
"licenseExpires",
"daysExpired"
];
//If is SUPERADMIN
if ($authChecker->isGranted("ROLE_ADMIN") == true || $authChecker->isGranted("ROLE_EXAMINER") == true) {
//echo "SIUPER ADMIN";
$sql = "SELECT r.id, CONCAT(r.first_name, ' ', r.last_name) AS user_name,
l.licenseNum, s.stateCode,
l.licenseExpires,
datediff(l.licenseExpires, CURDATE()) AS daysExpired
FROM license l
INNER JOIN resource r
ON r.id = l.resource_id
INNER JOIN state s
ON l.state_id = s.id
WHERE r.is_active = 1 AND l.id IS NOT NULL
AND (licenseExpires <= DATE_ADD(CURDATE(), INTERVAL 60 DAY) OR l.licenseExpires <= CURDATE())
AND r.roles <> '[\"ROLE_REGISTERED\"]' ";
/*if($dropdownTeamValue != 0) {
$sql .= " AND c2.claim_team_id = $dropdownTeamValue";
}*/
# handle status being passed in from pie chart
$dataParams = array();
# specifiy group by
$group_by = " GROUP BY l.licenseNum";
}elseif ($authChecker->isGranted("ROLE_TEAM_LEADER") == true){ //If is TEAM LEADER
//echo "TEam Leader";
$sql = "SELECT r.id, CONCAT(r.first_name, ' ', r.last_name) AS user_name,
l.licenseNum, s.stateCode,
l.licenseExpires,
datediff(l.licenseExpires, CURDATE()) AS daysExpired
FROM claim_team_resource ctr2
LEFT JOIN claim c2
ON ctr2.claim_team_id = c2.claim_team_id AND ctr2.team_lead = 1
INNER JOIN claim_team_resource ctr1
ON ctr2.claim_team_id = ctr1.claim_team_id
INNER JOIN resource r
ON r.id = ctr1.resource_id
INNER JOIN license l
ON l.resource_id = r.id
INNER JOIN state s
ON l.state_id = s.id
WHERE r.is_active = 1 AND l.id IS NOT NULL
AND ctr1.is_active = 1 AND ctr2.resource_id = :resourceId
AND r.roles <> '[\"ROLE_REGISTERED\"]'
AND (licenseExpires <= DATE_ADD(CURDATE(), INTERVAL 60 DAY) OR l.licenseExpires <= CURDATE()) ";
if($dropdownTeamValue != 0) {
$sql .= " AND c2.claim_team_id = $dropdownTeamValue";
}
# handle status being passed in from pie chart
$dataParams = array('resourceId' => $resourceId);
# specifiy group by
$group_by = " GROUP BY l.licenseNum";
//$group_by = "";
}else{ //IF ADJUSTER
//echo "ADJUSTER";
$sql = "SELECT r.id, CONCAT(r.first_name, ' ', r.last_name) AS user_name,
l.licenseNum, s.stateCode,
l.licenseExpires,
datediff(l.licenseExpires, CURDATE()) AS daysExpired
FROM resource r
INNER JOIN license l
ON r.id = l.resource_id
INNER JOIN state s
ON l.state_id = s.id
WHERE r.is_active = 1 AND r.id = :resourceId
AND l.id IS NOT NULL AND (licenseExpires <= DATE_ADD(CURDATE(), INTERVAL 60 DAY) OR l.licenseExpires <= CURDATE())
AND r.roles <> '[\"ROLE_REGISTERED\"]' ";
# handle status being passed in from pie chart
$dataParams = array('resourceId' => $resourceId);
# specifiy group by
$group_by = "";
}
// echo "<pre> LICEN: ". $sql ."</pre>";
# build keyword filter, which is requested by datatables ajax when someone types into keyword field
$keyword_filter = " AND (l.licenseNum like :keywordLicenses OR s.stateCode like :keywordLicenses
OR r.first_name like :keywordLicenses OR r.last_name like :keywordLicenses) ";
// $keyword_filter = "";
//return new JsonResponse(["sql" => $sql, "data" => $dataParams]); die;
# call PaginatorService
$paginator = $this->container->get("resource.paginator_service");
$data = $paginator->getPaginationResults($request, $sql, $dataParams, "", $columnsArr, "search_keyword", $keyword_filter, "keywordLicenses", $group_by, "GET");
$resultset2 = $data['data'];
$resultset = array();
foreach ($resultset2 as $getItem) {
/*
* IF EXPIRED DAYS are less than 0 means they are Expired
*/
if($getItem['daysExpired'] < 0){
$getItem['daysExpired'] = 'Expired';
}
$getItem['licenseExpires'] = date("m/d/Y", strtotime($getItem['licenseExpires']));
$resultset[] = $getItem;
}
$data['data'] = $resultset;
return new JsonResponse($data);
}
/**
* @Route("/licenses/get_teams")
*/
public function getTeams(Request $request){
$user = $this->get('security.token_storage')->getToken()->getUser();
$em = $this->getDoctrine()->getManager();
$authChecker = $this->container->get('security.authorization_checker');
$resourceId = $user->getId();
/*if ($authChecker->isGranted("ROLE_ADMIN") == true) {
$sqlTeams = "SELECT DISTINCT ct.id, ct.name
FROM claim_team_resource ctr2
LEFT JOIN claim c2
ON ctr2.claim_team_id = c2.claim_team_id AND ctr2.team_lead = 1
LEFT JOIN claim_team_resource ctr1
ON ctr2.claim_team_id = ctr1.claim_team_id
LEFT JOIN resource r
ON r.id = ctr1.resource_id
LEFT JOIN license l
ON l.resource_id = r.id
LEFT JOIN state s
ON l.state_id = s.id
LEFT JOIN claim_team ct
ON ct.id = ctr2.claim_team_id
WHERE r.is_active = 1
AND l.id IS NOT NULL
-- AND ctr2.resource_id = :resourceId
AND (licenseExpires <= DATE_ADD(CURDATE(), INTERVAL 60 DAY) OR l.licenseExpires <= CURDATE())
GROUP BY l.licenseNum
";
}elseif ($authChecker->isGranted("ROLE_TEAM_LEADER") == true){ //If is TEAM LEADER
$sqlTeams = "SELECT DISTINCT ct.id, ct.name
FROM claim_team_resource ctr2
LEFT JOIN claim c2
ON ctr2.claim_team_id = c2.claim_team_id AND ctr2.team_lead = 1
LEFT JOIN claim_team_resource ctr1
ON ctr2.claim_team_id = ctr1.claim_team_id
LEFT JOIN resource r
ON r.id = ctr1.resource_id
LEFT JOIN license l
ON l.resource_id = r.id
LEFT JOIN state s
ON l.state_id = s.id
LEFT JOIN claim_team ct
ON ct.id = ctr2.claim_team_id
WHERE r.is_active = 1
AND l.id IS NOT NULL
AND ctr2.resource_id = $resourceId
AND (licenseExpires <= DATE_ADD(CURDATE(), INTERVAL 60 DAY) OR l.licenseExpires <= CURDATE())
GROUP BY l.licenseNum
";
}else{
$sqlTeams = "SELECT ct.id, ct.name
FROM claim c
LEFT JOIN customer cust on c.customer_id = cust.id
INNER JOIN state s on c.loss_state_id = s.id
LEFT JOIN claim_resource cr on cr.claim_id = c.id
LEFT JOIN claim_team ct ON ct.id = c.claim_team_id
LEFT JOIN resource r on cr.resource_id = r.id
LEFT JOIN claims_insured ci on ci.claim_id = c.id AND ci.is_primary = 1
LEFT JOIN insured i ON i.id = ci.insured_id
WHERE c.status_id > 5
AND r.id = $resourceId
GROUP BY ct.id";
}
$stmt = $em->getConnection()->prepare($sqlTeams);
$stmt->execute();
$data = $stmt->fetchAll();*/
$data = [
['id' => 11, 'name' => 'Cat Team']
];
return new JsonResponse($data);
}
}

src/Globals/CoreBundle/Controller/LicensesMaintenanceWidgetController.php line 45