Symfony Profiler

<?php
namespace Globals\ClaimManagementBundle\Controller;
use Globals\ResourceManagementBundle\Entity\Resource;
use Globals\ResourceManagementBundle\Entity\ResourceDashboard;
use Globals\ResourceManagementBundle\Service\CarrierUtils;
use Globals\ResourceManagementBundle\Service\SLogger;
use Globals\ResourceManagementBundle\Twig\UCFirstAll;
use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\HttpFoundation\Request;
use Globals\ClaimManagementBundle\Entity\Claim;
use Globals\ClaimManagementBundle\Entity\ClaimStatus;
/**
* @Route("/administration/manage")
*/
class ClaimIntakeFragmentsController extends Controller
{
private $user = null;
/**
* @var array
*/
private $loss_type_map = [
'AIR CONDITIONING VESSEL' => 100,
'AIRCRAFT-DAMAGE BY' => 123,
'All Other Physical Damage' => 100,
'BI' => 2,
'BOP-LOSS OF RENTS' => 100,
'BUILDING' => 95,
'BUILDING AND CONTENTS' => 95,
'Burglary/theft/robbery-money or securities' => 124,
'Business Income' => 100,
'Business Personal Property' => 100,
'Chubb Fire – Lithium Battery' => 6,
'COLLAPSE' => 104,
'CONSTRUCTION DEFECTS' => 96,
'CONTENTS' => 100,
'CPL - ANIMAL BITE' => 96,
'CPL - BODILY INJURY' => 96,
'CPL - PROPERTY DMG' => 95,
'CPL-CARE OR CUSTDY OR CTRL' => 95,
'CYBER CRIME' => 100,
'Cyber Liability' => 96,
'DAMAGE UNKNOWN' => 101,
'DENIAL' => 100,
'Denied/Do No Report' => 100,
'Diving Board Liab' => 96,
'EARTH MOVEMENT/ SETTLEMENT/ RESULTANT CRACKING' => 165,
'EARTHQUAKE' => 165,
'ELECTRICAL OR PWR SURGE' => 100,
'EMPLOYEE DISHONESTY' => 100,
'EQUIPMENT BREAKDOWN' => 163,
'EXPLOSION' => 126,
'EXTRA CONTRACTUAL' => 100,
'FALLING OBJECTS' => 161,
'FIRE' => 6,
'FIRE LEGAL LIABILITY' => 96,
'FLOOD' => 98,
'FREEZING - ICE' => 7,
'GLASS - HOMEOWNERS' => 113,
'GOLF CART LIABILITY' => 96,
'HAIL' => 147,
'HURRICANE' => 20,
'IDENTITY THEFT' => 100,
'LEAD LIABILITY ' =>100,
'Liability - All Other' => 96,
'LIGHTNING' => 12,
'LOSS OF WAGES' => 100,
'MED-PAY' => 100,
'MISC. - ALL RISK' => 100,
'MOLD' => 100,
'MONEY/CURRENCY/BANK NOTES/CKS' => 100,
'MYSTERIOUS DISAPPEAR' => 15,
'NO INJURIES' => 100,
'NOC' => 100,
'OTHER' => 100,
'OTHER STRUCTURES' => 100,
'PERS INJ PROT OR (PIP)' => 100,
'PERSONAL INJURY HO' => 100,
'PROPERTY' => 95,
'PROPERTY DAMAGE' => 95,
'Pypestream Other' => 100,
'RIOT OR CIVIL COMMOTION' =>100,
'Rodent/Vermin' => 117,
'Service Line Coverage' => 100,
'SEWER OR SEWAGE' => 134,
'SMOKE' => 128,
'STO - Burg, Theft & Rob - Money/Securities' => 15,
'STO - Burglary - Outside' => 15,
'STO - Burglary/theft/robbery – other' => 15,
'STO - Earthquake' => 165,
'STO - Fire and Lightning' => 6,
'STO - Flood' => 98,
'STO - Hurricane' => 20,
'STO - Misc - Property Damage' => 95,
'STO - Misc animal or insect' => 100,
'STO - Mysterious Disappearance' => 15,
'STO - Other' => 100,
'STO - Property - Mold, Mildew and Dry rot' => 100,
'STO - Property - Wind or Hail' => 53,
'STO - Property Damage - Outside' => 95,
'STO - Severe Storm' => 23,
'STO - Sprinkler leakage' => 102,
'STO - Vandalism' => 17,
'STO - Water Damage' => 102,
'STO - Water Damage - Broken Pipe' => 102,
'STO - Water from roof' => 102,
'STO - Wildfire' => 157,
'STO - Winter Storm' => 23,
'SUBSIDE OR SOIL MOVMENT' => 100,
'Theft - Burglary Or Robbery - From Auto' => 100,
'THEFT- OFF PREMISES' => 15,
'THEFT ON PREMISES' => 15,
'TORNADO' => 16,
'TREE ROOTS - DMGD BY' => 100,
'TRIP COLLISION' => 100,
'TX ONLY - Applicance-Water' => 102,
'VANDALISM OR MAL MISCH' => 124,
'VEHICLE - DAMAGED BY' => 123,
'Vehicle (Struck By Vehicle & Damaged)' => 123,
'VOLCANIC ERUPTION' => 117,
'W OR C - MED.EXPENSES' => 100,
'W OR C - TTL TEMP DISBL' => 100,
'W OR C - VOC. REHAB.' => 100,
'W OR C-PERMANENT DSBLTY' => 100,
'WATER - BACKUP' => 153,
'WATER - OTHER' => 102,
'WATER - WEATHER' => 102,
'WATERCRAFT LIABILITY' => 96,
'WATER-PIPE BREAK' => 102,
'WATER-SUMP OVERFLOW' => 102,
'WEIGHT ICE OR SNOW' => 143,
'WILDFIRE' => 157,
'WILDFIRE SMOKE' => 157,
'Wind - Catastrophe' => 138,
'WINDSTORM' => 138
];
// Comment here
/**
* @Route("/unassigned")
*/
public function unassignedViewFragment()
{
$em = $this->get('doctrine')->getManager();
$CarrierCheck = $this->container->get("app.carrier_check");
$envService = $this->get('core.get_environment');
$claimRepo = $em->getRepository("ClaimManagementBundle:Claim");
$adjusterRepo = $em->getRepository("ResourceManagementBundle:Resource");
$claimTeamRepo = $em->getRepository("ClaimManagementBundle:ClaimTeam");
$claimTeams = $claimTeamRepo->findBy(['enabled'=>true], array('name' => 'ASC'));
$sql = "SELECT id, resourceType
FROM resource_type";
/**
* Don't select exception resource types for specific auto assigner instances
*/
if ($envService->getIsCarrier()) {
# Hide "None", its a auto assigner feature
$sql .= " WHERE id != 99";
} else {
# ICM doesnt use these resource types
$sql .= " WHERE id < 90 AND id != 3";
}
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$resourceTypes = $stmt->fetchAll(\PDO::FETCH_ASSOC);
$assignmentParties = $em->getRepository("AssignmentBundle:AssignmentParty")->findAll();
return $this->render('ClaimManagementBundle:ClaimIntakeManagement/Fragments:unassigned.html.twig',[
'resourceTypes' => $resourceTypes,
'assignmentParties' => $assignmentParties,
'teams' => $claimTeams,
'companyCode' => $envService->getCompanyCode()
]);
}
// Comment here
/**
* @Route("/unassignedmap/{customer_id}/{team_id}", defaults={"customer_id" = null,"team_id" = null})
*/
public function unassignedViewMap(Request $request, $customer_id, $team_id)
{
$em = $this->get('doctrine')->getManager();
$CarrierCheck = $this->container->get("app.carrier_check");
$envService = $this->get('core.get_environment');
$claimRepo = $em->getRepository("ClaimManagementBundle:Claim");
$adjusterRepo = $em->getRepository("ResourceManagementBundle:Resource");
$claimTeamRepo = $em->getRepository("ClaimManagementBundle:ClaimTeam");
$Customers = $em->getRepository("CustomerManagementBundle:Customer")->findBy([], ['companyName' => 'ASC']);
$resultsetClaimTeam = array();
$adjusters = $adjusterRepo->findBy(['resourceType'=>array(2,3,4)]);
$claimTeams = $claimTeamRepo->findBy(['enabled'=>true], array('name' => 'ASC'));
$sql = "SELECT id, resourceType
FROM resource_type";
/**
* Don't select exception resource types for specific auto assigner instances
*/
if ($envService->getIsCarrier()) {
# Hide "None", its a auto assigner feature
$sql .= " WHERE id != 99";
} else {
# ICM doesnt use these resource types
$sql .= " WHERE id < 90 AND id != 3";
}
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$resourceTypes = $stmt->fetchAll(\PDO::FETCH_ASSOC);
$CarrierCheck = $this->container->get("app.carrier_check");
$isCarrier = $CarrierCheck->environmentIsCarrier();
$tokenStorage = $this->container->get("security.token_storage");
$User = $tokenStorage->getToken()->getUser();
$isTeamLeader = false;
$isAdjuster = false;
$isBranchAdmin = false;
$isBranchUser = false;
if ($this->isGranted("ROLE_ADJUSTER") === true && $this->isGranted("ROLE_TEAM_LEADER") === false) {
$isAdjuster = true;
} else if ($this->isGranted("ROLE_TEAM_LEADER") === true && ($this->isGranted("ROLE_ADMIN") === false && $this->isGranted("ROLE_EXAMINER") === false)) {
$isTeamLeader = true;
} else if ($this->isGranted("ROLE_BRANCH_ADMIN") === true && $this->isGranted("ROLE_ADJUSTER") === false) {
$isBranchAdmin = true;
} else if ($this->isGranted("ROLE_BRANCH_USER") === true && $this->isGranted("ROLE_BRANCH_ADMIN") === false) {
$isBranchUser = true;
}
# Build Query and PDO Parameters that will be passed to PaginatorService class
$dataParams = array();
$sqlUnassign = "SELECT c.id,
c.id as file_number,
c.claim_number as claim_number,
CONCAT(i.first_name, ' ', i.last_name) as insured_member,
CONCAT(ar.first_name,' ',ar.last_name) as assistive_resource,
i.address as address,
cust.company_name as company_name,
c.date_received as date_received,
c.loss_city as loss_city,
s.stateName as state,
c.loss_zip, DATE_FORMAT(c.loss_date, '%m/%d/%Y') as loss_date,
c.loss_latitude as loss_latitude,
c.loss_longitude as loss_longitude,
c.status_id as status_id,
c.date_received,
c.loss_description,
p1.description as loss_type
FROM claim c "
. (($isCarrier) ? "LEFT" : "INNER") . " JOIN customer cust on c.customer_id = cust.id
LEFT JOIN state s
on c.loss_state_id = s.id
LEFT JOIN perils p1
ON c.peril_id = p1.id
LEFT JOIN claims_insured ci
ON c.id = ci.claim_id AND ci.is_primary = 1
LEFT JOIN insured i
ON ci.insured_id = i.id " .
(($isAdjuster) ? "INNER" : "LEFT") . " JOIN assignments cr
ON c.id = cr.claim_id AND IFNULL(cr.next_assignment_id,0) = 0 AND cr.is_current_assignment = 1
LEFT JOIN resource r
ON cr.resource_id = r.id " .
(($isBranchAdmin) ? "INNER" : "LEFT") . " JOIN customer_resource cust_res
ON c.customer_id = cust_res.customer_id ";
if ($isTeamLeader) {
$sqlUnassign .= "INNER JOIN claim_team_resource ctr
ON c.claim_team_id = ctr.claim_team_id ";
}
/*
* 7/26/2019 Added assistive adjuster update
*
*/
$sqlUnassign .= "LEFT JOIN resource ar ON ar.id = c.assistive_resource_id ";
$sqlUnassign .= "WHERE c.loss_longitude<>'0' ";
// $sql .= "WHERE 1 = 1";
// if ($resourceId > 0) {
$sqlUnassign .= " AND c.status_id < 10 ";
// }
if($customer_id>0) {
$sqlUnassign .= "AND c.customer_id = :customer_id ";
$dataParams['customer_id'] = $customer_id;
}
$sqlUnassign .= " GROUP BY c.id ";
$stmt = $em->getConnection()->prepare($sqlUnassign);
$stmt->execute($dataParams);
$resultset = $stmt->fetchAll(\PDO::FETCH_ASSOC);
$i = 0;
$markerData = array();
foreach($resultset as $key=>$value){
$markerData[$i]['longitude'] = $value['loss_longitude'];
$markerData[$i]['latitude'] = $value['loss_latitude'];
$markerData[$i]['claimnumber'] = $value['file_number'];
if(!empty($value['loss_type']))
$markerData[$i]['loss_type'] = $value['loss_type'];
else
$markerData[$i]['loss_type'] = 'NA';
$date=date_create($value['date_received']);
$date_received = date_format($date,"m/d/Y");
$date1 = new \DateTime("now");
$date2 = new \DateTime(date_format($date,"m/d/Y H:i:s"));
$interval = $date1->diff($date2);
$daysDiff = $interval->days;
$hrsDiff = $interval->h;
$totalHrs = ($daysDiff*24 + $hrsDiff);
$markerData[$i]['date_received'] = $date_received;
$markerData[$i]['interval'] = $totalHrs;
// $markerData[$i]['days'] = $interval->days;
//$markerData[$i]['hr'] = $interval->h;
$markerData[$i]['type'] ='claim';
$markerData[$i]['id'] = $value['file_number'];
$markerData[$i]['customerName'] = $value['company_name'];
if($totalHrs>8)
$markerData[$i]['iconUrl'] = '/img/pink-dot.png';
else
$markerData[$i]['iconUrl'] = '/img/blue-dot.png';
$i++;
}
if($customer_id>0){
$dataParamsClaimTeam = array();
$dataParamsClaimTeam['customer_id'] = $customer_id;
$sqlClaimTeam = "SELECT name,id FROM claim_team ct";
$sqlClaimTeam .= " WHERE ct.customer_id = :customer_id and enabled=1 ORDER BY name";
$stmtClaimTeam = $em->getConnection()->prepare($sqlClaimTeam);
$stmtClaimTeam->execute($dataParamsClaimTeam);
$resultsetClaimTeam = $stmtClaimTeam->fetchAll(\PDO::FETCH_ASSOC);
$dataParamsClaimTeamAdj = array();
if($team_id>0) {
$dataParamsClaimTeamAdj['claim_team_id'] = $team_id;
$sqlClaimTeamAdj = "SELECT r.id,
CONCAT(r.first_name, ' ', r.last_name) as team_member_name,r.latitude,r.longitude,r.email,r.phone FROM resource r
INNER JOIN claim_team_resource ctr on r.id = ctr.resource_id
";
$sqlClaimTeamAdj .= "WHERE r.is_active=1 and ctr.claim_team_id = :claim_team_id ";
$stmtClaimTeamAdj = $em->getConnection()->prepare($sqlClaimTeamAdj);
$stmtClaimTeamAdj->execute($dataParamsClaimTeamAdj);
$resultsetClaimTeamAdj = $stmtClaimTeamAdj->fetchAll(\PDO::FETCH_ASSOC);
if (!empty($resultsetClaimTeamAdj)) {
foreach ($resultsetClaimTeamAdj as $key => $value) {
$markerData[$i]['longitude'] = $value['longitude'];
$markerData[$i]['latitude'] = $value['latitude'];
$markerData[$i]['phone'] = $value['phone'];
$sqlClaimCnt = "SELECT count(claim_id) as count_of_claims
FROM assignments cr
INNER JOIN claim c
ON cr.claim_id = c.id
AND IFNULL(next_assignment_id,0) = 0
AND is_current_assignment = 1
WHERE 1 = 1
AND c.status_id < 30
AND cr.resource_id = :resource_id";
$dataParamsClaimCnt = array();
$dataParamsClaimCnt['resource_id'] = $value['id'];
$stmtClaimCnt = $em->getConnection()->prepare($sqlClaimCnt);
$stmtClaimCnt->execute($dataParamsClaimCnt);
$numOfClaims = $stmtClaimCnt->fetchAll(\PDO::FETCH_ASSOC);
foreach ($numOfClaims as $numOfClaim){
$markerData[$i]['claim_assigned']= $numOfClaim['count_of_claims'];
}
$markerData[$i]['email'] = $value['email'];
$value['team_member_name'] = str_replace("'","",$value['team_member_name']);
$markerData[$i]['customerName'] = str_replace('"',"",$value['team_member_name']);
$markerData[$i]['type'] ='adjuster';
$markerData[$i]['iconUrl'] = '/img/icon.png';
$i++;
}
}
}
}
//dd($markerData);
// $markerData[0]['longitude'] = '-111.8265049';
// $markerData[0]['latitude'] = '34.8791806';
// $markerData[0]['claimnumber'] = '1111111323-746456-7857657';
// $markerData[0]['customerName'] = 'USAA CAT1';
// $markerData[0]['iconUrl'] = 'http://maps.google.com/mapfiles/ms/icons/pink-dot.png';
//$markerData[1]['longitude'] = '-111.7695277';
// $markerData[1]['latitude'] = '34.832149';
//$markerData[1]['claimnumber'] = '22222323-746456-7857657';
// $markerData[1]['customerName'] = 'USAA CAT2';
// $markerData[1]['iconUrl'] = 'http://maps.google.com/mapfiles/ms/icons/blue-dot.png';
return $this->render('ClaimManagementBundle:ClaimIntakeManagement/Fragments:unassigned_map.html.twig',[
'adjusters' => $adjusters,
'resourceTypes' => $resourceTypes,
"Customers" => $Customers,
'teams' => $claimTeams,'creation'=>true,
'customer_id'=>$customer_id,
'companyCode' => $envService->getCompanyCode(),'markerData'=>$markerData,'resultsetClaimTeam'=>$resultsetClaimTeam,'team_id'=>$team_id
]);
}
// Comment here
/**
* @Route("/sendtoxact/{claim_id}/{sendflg}", defaults={"claim_id" = null,"sendflg" = null})
*/
public function sendclaimxact(Request $request, $claim_id,$sendflg)
{
$em = $this->get('doctrine')->getManager();
$sentToXact = 'N';
$claimData = array();
$claimRepo = $em->getRepository("ClaimManagementBundle:Claim");
$claimNotFoundErr = '';
$claimSentXact = '';
if($claim_id!='') {
$claimData = $claimRepo->findOneBy(['id' => $claim_id]);
if (empty($claimData)) {
$claimData = $claimRepo->findOneBy(['claimNumber' => $claim_id]);
}
$claimNotFoundErr = 'N';
if (empty($claimData)) {
$claimNotFoundErr = 'Y';
}
if(!empty($claimData) && $claimData->getTransactionId()!=''){
$claimSentXact = 'Y';
}else{
$claimSentXact = 'N';
}
if($sendflg=='Y'){
$xact_service = $this->container->get('integration.xact.outbound');
$xact_data = array();
if($claimData->getCustomer()->getCarrierId()!='') {
$xact_data["carrier_id"] = $claimData->getCustomer()->getCarrierId();
$xact_data["customer_name"] = $claimData->getCustomer()->getCompanyName();
$xact_data["recipientsXNAddress"] = 'IAS_CLAIMS.HOME.WEB';
$sqlInsured = "SELECT
ci.id, CONCAT(i.first_name, ' ', i.last_name) AS insuredName,i.phone,i.email as insuredEmail, i.address, ci.is_primary, cit.insured_type, i.city, s.stateCode, i.zip_code
FROM claims_insured ci
INNER JOIN insured i
ON ci.insured_id = i.id
INNER JOIN claim_insured_types cit
ON ci.claim_insured_type_id = cit.id
LEFT JOIN state s
ON i.state_id = s.id
WHERE claim_id = '" . $claimData->getId() . "' limit 1";
# Search By Claim ID
$stmt = $em->getConnection()->prepare($sqlInsured);
$stmt->execute();
$insuredResults = $stmt->fetchAll(\PDO::FETCH_ASSOC);
//dd($insuredResults);
$xact_data["insured_name"] = $insuredResults[0]['insuredName'];
$xact_data["InsuredHomeNumber"] = preg_replace('/[^0-9]/', '',$insuredResults[0]['phone']);
$xact_data["address"] = $insuredResults[0]['address'];
$xact_data["prov"] = $insuredResults[0]['stateCode'];
$xact_data["postal"] = $insuredResults[0]['zip_code'];
$xact_data["city"] = $insuredResults[0]['city'];
$xact_data["loss_city"] = $claimData->getLossCity();
$xact_data["loss_street"] = $claimData->getLossStreet();
$xact_data["loss_state"] = $claimData->getLossState()->getStateCode();
$xact_data["loss_zip"] = $claimData->getLossZip();
$xact_data["dateOfLoss"] = $claimData->getLossDate()->format('Y-m-d');
$xact_data["policyNumber"] = $claimData->getPolicyNumber();
$xact_data["claimNumber"] = $claimData->getClaimNumber();
$xact_data["notes"] = $claimData->getSpecialNotes();
$xact_data["catCode"] = $claimData->getCatastropheCode();
$xact_data["ClaimLossDesc"] = substr($claimData->getLossDescription(),0,34);
$xact_data["policyStart"] = $claimData->getPolicyStartDate()->format('Y-m-d');
$xact_data["policyEnd"] = $claimData->getPolicyEndDate()->format('Y-m-d');
$xact_data["dateOfRecieve"] = date('Y-m-d');
$xact_data["loss_type"] = strtoupper($claimData->getPeril()->getDescription());
$coverageRepo = $em->getRepository("ClaimManagementBundle:Coverage");
$coverageData = $coverageRepo->findBy(['claim' => $claimData]);
$coveragesArray = array();
if (!empty($coverageData)) {
foreach ($coverageData as $coverageData) {
if ($coverageData->getName() == 'Dwelling') {
$coveragesArray[0]['name'] = 'Dwelling';
$coveragesArray[0]['id'] = 'COV1';
$coveragesArray[0]['type'] = 0;
$coveragesArray[0]['amt'] = floatval($coverageData->getAmount());
}
if ($coverageData->getName() == 'APS') {
$coveragesArray[1]['id'] = 'COV3';
$coveragesArray[1]['name'] = 'Aps';
$coveragesArray[1]['type'] = 1;
$coveragesArray[1]['amt'] = floatval($coverageData->getAmount());
}
if ($coverageData->getName() == 'Contents') {
$coveragesArray[2]['id'] = 'COV2';
$coveragesArray[2]['name'] = 'Contents';
$coveragesArray[2]['type'] = 2;
$coveragesArray[2]['amt'] = floatval($coverageData->getAmount());
}
if ($coverageData->getName() == 'ALE') {
$coveragesArray[3]['id'] = 'COV4';
$coveragesArray[3]['name'] = 'ALE';
$coveragesArray[3]['type'] = 0;
$coveragesArray[3]['amt'] = floatval($coverageData->getAmount());
}
}
}
if (!empty($coveragesArray)) {
$xact_data['coverages'] = $coveragesArray;
}
$resXect = $xact_service->exportCreateClaim($xact_data);
$claimData->setTransactionId($resXect['transactionId']);
if ($resXect['errorDescription'] != '') {
$claimData->setXactErrorMessage($resXect['errorDescription']);
$claimData->setXactErrorCode($resXect['errorCode']);
$sentToXact = 'E';
} else {
$claimData->setXactErrorMessage('Claim sent successfully');
$claimData->setXactErrorCode('No Error');
$sentToXact = 'Y';
}
$em->persist($claimData);
// dump($importable);
// actually flush into the db
$em->flush();
}else{
$sentToXact = 'C';
}
}
}
return $this->render('ClaimManagementBundle:ClaimIntakeManagement/Fragments:send_claim_xact.html.twig',['claim_id'=>$claim_id,'claimNotFoundErr'=>$claimNotFoundErr,'claimSentXact'=>$claimSentXact,'sentToXact'=>$sentToXact
]);
}
/**
* @Route("/all")
*/
public function viewAllClaimsFragment()
{
$em = $this->getDoctrine()->getManager();
$CarrierCheck = $this->container->get("app.carrier_check");
$isCarrier = $CarrierCheck->environmentIsCarrier();
$tokenStorage = $this->container->get("security.token_storage");
$User = $tokenStorage->getToken()->getUser();
if($User instanceof Resource) {
if(in_array(UCFirstAll::ROLE_REGISTERED, $User->getRoles())){
throw $this->createAccessDeniedException("You don't have access to this page.");
}
} else {
throw $this->createAccessDeniedException("You don't have access to this page.");
}
$isTeamLeader = false;
$isAdjuster = false;
$isBranchAdmin = false;
$isBranchUser = false;
if ($this->isGranted("ROLE_ADJUSTER") === true && $this->isGranted("ROLE_TEAM_LEADER") === false) {
$isAdjuster = true;
} else if ($this->isGranted("ROLE_TEAM_LEADER") === true && ($this->isGranted("ROLE_ADMIN") === false && $this->isGranted("ROLE_EXAMINER") === false)) {
$isTeamLeader = true;
} else if ($this->isGranted("ROLE_BRANCH_ADMIN") === true && $this->isGranted("ROLE_ADJUSTER") === false) {
$isBranchAdmin = true;
} else if ($this->isGranted("ROLE_BRANCH_USER") === true && $this->isGranted("ROLE_BRANCH_ADMIN") === false) {
$isBranchUser = true;
}
$recordsCount = 0;
$dataParams = array();
$sql = "SELECT COUNT(*) as count2
FROM claim c
LEFT JOIN customer cust on c.customer_id = cust.id
LEFT JOIN state s on c.loss_state_id = s.id
LEFT JOIN claims_insured ci
ON c.id = ci.claim_id
LEFT JOIN insured i
ON ci.insured_id = i.id ";
if ($isAdjuster) {
$sql .= "INNER JOIN assignments cr
ON c.id = cr.claim_id
AND IFNULL(cr.next_assignment_id,0) = 0
AND cr.is_current_assignment = 1 ";
} else if ($isTeamLeader) {
$sql .= "INNER JOIN claim_team_resource ctr
ON c.claim_team_id = ctr.claim_team_id ";
} else if ($isBranchAdmin) {
$sql .= "INNER JOIN customer_resource cust_res
ON c.customer_id = cust_res.customer_id ";
}
$sql .= " WHERE (c.status_id BETWEEN 6 AND 29 OR c.status_id = 98) ";
if ($isAdjuster) {
$sql .= "AND cr.resource_id = :adjuster_id ";
$dataParams['adjuster_id'] = $User->getId();
} else if ($isTeamLeader) {
$sql .= "AND ctr.resource_id = :team_leader_id AND ctr.team_lead = 1 ";
$dataParams['team_leader_id'] = $User->getId();
} else if ($isBranchAdmin) {
$sql .= "AND cust_res.resource_id = :branch_admin_id ";
$dataParams['branch_admin_id'] = $User->getId();
} else if ($isBranchUser) {
$sql .= "AND c.claim_rep_id = :branch_user_id ";
$dataParams['branch_user_id'] = $User->getId();
}
$sql .= "GROUP BY c.id ";
$recordsCount = 0;
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute($dataParams);
$resultset = $stmt->fetchAll(\PDO::FETCH_ASSOC);
$recordsCount = count($resultset);
$sqlStatus = "SELECT id, status_name,hex_color
FROM claim_status where show_on_report='Y' order by order_by";
$stmtSql = $em->getConnection()->prepare($sqlStatus);
$stmtSql->execute();
$statusShow = $stmtSql->fetchAll(\PDO::FETCH_ASSOC);
return $this->render('ClaimManagementBundle:ClaimIntakeManagement/Fragments:all-claims.html.twig', [
'newClaimCount' => $recordsCount,'statusShow'=>$statusShow
]);
}
/**
* @Route("/unassigned_data")
*/
public function unassignedDatatables(Request $request)
{
$em = $this->getDoctrine()->getManager();
//$resourceId = intval($request->query->get("resourceId"));
$CarrierCheck = $this->container->get("app.carrier_check");
$isCarrier = $CarrierCheck->environmentIsCarrier();
$tokenStorage = $this->container->get("security.token_storage");
$User = $tokenStorage->getToken()->getUser();
# Array of columns that will be sorted
if($isCarrier) {
$columnsArr = [
"id",
"id",
"claim_number",
"insured_member",
"assistive_resource",
"loss_city",
"state",
"loss_zip",
"loss_type",
"loss_date",
"date_received"
];
}else{
$columnsArr = [
"id",
"id",
"claim_number",
"file_number",
"company_name",
"insured_member",
"assistive_resource",
"loss_city",
"state",
"loss_zip",
"loss_type",
"loss_date",
"date_received"
];
}
$isTeamLeader = false;
$isAdjuster = false;
$isBranchAdmin = false;
$isBranchUser = false;
if ($this->isGranted("ROLE_ADJUSTER") === true && $this->isGranted("ROLE_TEAM_LEADER") === false) {
$isAdjuster = true;
} else if ($this->isGranted("ROLE_TEAM_LEADER") === true && ($this->isGranted("ROLE_ADMIN") === false && $this->isGranted("ROLE_EXAMINER") === false)) {
$isTeamLeader = true;
} else if ($this->isGranted("ROLE_BRANCH_ADMIN") === true && $this->isGranted("ROLE_ADJUSTER") === false) {
$isBranchAdmin = true;
} else if ($this->isGranted("ROLE_BRANCH_USER") === true && $this->isGranted("ROLE_BRANCH_ADMIN") === false) {
$isBranchUser = true;
}
# Build Query and PDO Parameters that will be passed to PaginatorService class
$dataParams = array();
$sql = "SELECT c.id,
c.id as file_number,
c.claim_number as claim_number,
CONCAT(i.first_name, ' ', i.last_name) as insured_member,
CONCAT(ar.first_name,' ',ar.last_name) as assistive_resource,
i.address as address,
cust.company_name as company_name,
c.date_received as date_received,
c.loss_city as loss_city,
s.stateName as state,
c.loss_zip, DATE_FORMAT(c.loss_date, '%m/%d/%Y') as loss_date,
-- c.assignment_notes as notes,
-- c.abridged_aa_notes as notes,
c.status_id as status_id,
c.date_received,
c.loss_description,
c.is_claim_category,
p1.description as loss_type
FROM claim c "
. (($isCarrier) ? "LEFT" : "INNER") . " JOIN customer cust on c.customer_id = cust.id
LEFT JOIN state s
on c.loss_state_id = s.id
LEFT JOIN perils p1
ON c.peril_id = p1.id
LEFT JOIN claims_insured ci
ON c.id = ci.claim_id AND ci.is_primary = 1
LEFT JOIN insured i
ON ci.insured_id = i.id " .
(($isAdjuster) ? "INNER" : "LEFT") . " JOIN assignments cr
ON c.id = cr.claim_id AND IFNULL(cr.next_assignment_id,0) = 0 AND cr.is_current_assignment = 1
LEFT JOIN resource r
ON cr.resource_id = r.id " .
(($isBranchAdmin) ? "INNER" : "LEFT") . " JOIN customer_resource cust_res
ON c.customer_id = cust_res.customer_id ";
if ($isTeamLeader) {
$sql .= "INNER JOIN claim_team_resource ctr
ON c.claim_team_id = ctr.claim_team_id ";
}
/*
* 7/26/2019 Added assistive adjuster update
*
*/
$sql .= "LEFT JOIN resource ar ON ar.id = c.assistive_resource_id ";
$sql .= "WHERE 1 = 1 ";
// $sql .= "WHERE 1 = 1";
// if ($resourceId > 0) {
$sql .= " AND c.status_id < 10 ";
// }
if ($isAdjuster) {
$sql .= "AND cr.resource_id = :adjuster_id ";
$dataParams['adjuster_id'] = $User->getId();
} else if ($isTeamLeader) {
$sql .= "AND ctr.resource_id = :team_leader_id AND ctr.team_lead = 1 ";
$dataParams['team_leader_id'] = $User->getId();
} else if ($isBranchAdmin) {
$sql .= "AND cust_res.resource_id = :branch_admin_id ";
$dataParams['branch_admin_id'] = $User->getId();
} else if ($isBranchUser) {
$sql .= "AND c.claim_rep_id = :branch_user_id ";
$dataParams['branch_user_id'] = $User->getId();
}
# build keyword filter, which is requested by datatables ajax when someone types into keyword field
$keyword_filter = " AND (c.claim_number like :keyword OR c.loss_city like :keyword OR c.loss_zip like :keyword or CONCAT(i.first_name, ' ', i.last_name) LIKE :keyword OR s.stateCode LIKE :keyword OR s.stateName LIKE :keyword OR c.id LIKE :keyword OR cust.company_name LIKE :keyword OR CONCAT(r.first_name, ' ', r.last_name) LIKE :keyword) ";
# specifiy group by
// $group_by = " GROUP BY c.id, c.claim_number";
$group_by = "GROUP BY c.id";
# call PaginatorService
$paginator = $this->container->get("resource.paginator_service");
/** @var \PDO $pdo */
$pdo = $this->getDoctrine()->getManager()->getConnection();
$retVal2 = $paginator->getPaginationResults($request, $sql, $dataParams, "c.id", $columnsArr, "search_keyword", $keyword_filter, "keyword", $group_by, "GET");
$retVal = $retVal2;
$retVal['data'] = array();
$i = 0;
foreach ($retVal2['data'] as $getItem) {
$status = $em->find("ClaimManagementBundle:ClaimStatus", $getItem['status_id']);
$getItem2 = $getItem;
$claim= $em->getRepository("ClaimManagementBundle:Claim")->find($getItem['id']);
if(!$isCarrier){
$claim= $em->getRepository("ClaimManagementBundle:Claim")->find($getItem['id']);
if(!empty($claim)){
$customerId=$claim->getCustomer()->getId();
$TpaCustomer = $em->getRepository("CompanyManagementBundle:TpaCustomer")->findBy(['customerId' => $customerId]);
//dd($customerId);
//dd($TpaCustomer);
if(!empty($TpaCustomer)){
$getItem2['isTpaCustomer'] ='Y';
}else{
$getItem2['isTpaCustomer'] ='N';
}
}
$Coverages = $em->getRepository("ClaimManagementBundle:Coverage")->findBy(['claim' => $claim]);
$getItem2['notes'] = "Loss Description: " .$getItem['loss_description'];
if(!empty($Coverages)){
foreach ($Coverages as $Coverage){
if($Coverage){
$getItem2['notes'] .= " ".$Coverage->getName().": $". number_format($Coverage->getAmount(),2);
}
}
}
//$getItem2['notes'] = str_replace("\n", " ", strip_tags(preg_replace("/<[\/]{0,1}br[ \/]{0,2}>/", "\n", $getItem['notes'])));
}else{
$getItem2['notes'] = str_replace("\n", " ", strip_tags(preg_replace("/<[\/]{0,1}br[ \/]{0,2}>/", "\n", $getItem['notes'])));
}
$getItem2['useTbca'] = 0;
$getItem2['subClaimHtml'] = '';
$getItem2['color'] = $status->getHexColor();
$retVal['data'][] = $getItem2;
$retVal['query'] = $sql;
}
return new JsonResponse($retVal);
}
/**
* @Route("/all_data")
*/
public function allDatatables(Request $request)
{
# Array of columns that will be sorted
$columnsArr = [
"id",
"claim_number",
"company_name",
"insured_member",
"address",
"loss_city",
"state",
"loss_zip",
"loss_date",
"date_received"
];
$resourceId = intval($request->query->get("resourceId"));
$CarrierCheck = $this->container->get("app.carrier_check");
$isCarrier = $CarrierCheck->environmentIsCarrier();
$tokenStorage = $this->container->get("security.token_storage");
$User = $tokenStorage->getToken()->getUser();
$isTeamLeader = false;
$isAdjuster = false;
$isBranchAdmin = false;
$isBranchUser = false;
if ($this->isGranted("ROLE_ADJUSTER") === true && $this->isGranted("ROLE_TEAM_LEADER") === false) {
$isAdjuster = true;
} else if ($this->isGranted("ROLE_TEAM_LEADER") === true && ($this->isGranted("ROLE_ADMIN") === false && $this->isGranted("ROLE_EXAMINER") === false)) {
$isTeamLeader = true;
} else if ($this->isGranted("ROLE_BRANCH_ADMIN") === true && $this->isGranted("ROLE_ADJUSTER") === false) {
$isBranchAdmin = true;
} else if ($this->isGranted("ROLE_BRANCH_USER") === true && $this->isGranted("ROLE_BRANCH_ADMIN") === false) {
$isBranchUser = true;
}
# Build Query and PDO Parameters that will be passed to PaginatorService class
$dataParams = array();
$sql = "SELECT c.status_id as status, c.id, c.claim_number, CONCAT(i.first_name, ' ', i.last_name) as insured_member, i.address as address,
-- i.phone, i.cell_phone, i.alternate_phone,
cust.company_name, c.date_received, c.loss_street, c.loss_city, s.stateName as state, c.loss_zip, c.loss_date, c.special_notes as notes,
p.description as peril, c.special_notes, c.date_received
FROM claim c
LEFT JOIN customer cust on c.customer_id = cust.id
LEFT JOIN state s on c.loss_state_id = s.id
LEFT JOIN perils p on c.peril_id = p.id
LEFT JOIN claims_insured ci
ON c.id = ci.claim_id
LEFT JOIN insured i
ON ci.insured_id = i.id "
. (($isAdjuster) ? "INNER" : "LEFT") . " JOIN assignments cr
ON c.id = cr.claim_id AND IFNULL(cr.next_assignment_id,0) = 0 AND cr.is_current_assignment = 1
LEFT JOIN resource r
ON cr.resource_id = r.id ";
if ($isTeamLeader) {
$sql .= "INNER JOIN claim_team_resource ctr
ON c.claim_team_id = ctr.claim_team_id ";
} else if ($isBranchAdmin) {
$sql .= "INNER JOIN customer_resource cust_res
ON c.customer_id = cust_res.customer_id ";
}
$sql .= " WHERE (c.status_id BETWEEN 6 AND 29 OR c.status_id = 98) ";
if ($isAdjuster) {
$sql .= "AND cr.resource_id = :adjuster_id ";
$dataParams['adjuster_id'] = $User->getId();
} else if ($isTeamLeader) {
$sql .= "AND ctr.resource_id = :team_leader_id AND ctr.team_lead = 1 ";
$dataParams['team_leader_id'] = $User->getId();
} else if ($isBranchAdmin) {
$sql .= "AND cust_res.resource_id = :branch_admin_id ";
$dataParams['branch_admin_id'] = $User->getId();
} else if ($isBranchUser) {
$sql .= "AND c.claim_rep_id = :branch_user_id ";
$dataParams['branch_user_id'] = $User->getId();
}
$searchedStatusId = $request->query->get('search_status');
if(!empty($searchedStatusId)){
$sql .= " AND (c.status_id in($searchedStatusId)) ";
}
$slog = new SLogger();
$slog->log("sql at 79: " . $sql);
# build keyword filter, which is requested by datatables ajax when someone types into keyword field
$keyword_filter = " AND (c.claim_number like :keyword OR c.loss_city like :keyword OR c.loss_zip like :keyword or CONCAT(i.first_name, ' ', i.last_name) LIKE :keyword OR s.stateCode LIKE :keyword OR s.stateName LIKE :keyword OR c.id LIKE :keyword OR cust.company_name LIKE :keyword OR CONCAT(r.first_name, ' ', r.last_name) LIKE :keyword) ";
# specifiy group by
// $group_by = " GROUP BY c.id, c.claim_number";
$group_by = "GROUP BY c.id";
# call PaginatorService
$paginator = $this->container->get("resource.paginator_service");
$retVal = $paginator->getPaginationResults($request, $sql, $dataParams, "c.date_received", $columnsArr, "search_keyword", $keyword_filter, "keyword", $group_by, "GET");
$retVal['data'] = $this->manipulateClaimData($retVal);
return new JsonResponse($retVal);
}
/**
* @Route("/index")
*/
public function viewMyClaims()
{
# FYI, if they have a role to view all claims, we need to do it here.
$em = $this->get('doctrine')->getManager();
$authChecker = $this->container->get('security.authorization_checker');
$user = $this->container->get('security.token_storage')->getToken()->getUser();
$role = $user->getRoles()[0];
/** @var Resource $user */
$this->user = $this->get('security.token_storage')->getToken()->getUser();
//Getting the settings of the User in an object
$getDashBoardSettingsOfUser = $this->getSettingsUserForDash();
if($getDashBoardSettingsOfUser) {
$dbLocalStorageSettings = json_decode($getDashBoardSettingsOfUser[0]['dashboard_settings']);
$dbLocalStoragePosition = json_decode($getDashBoardSettingsOfUser[0]['dashboard_position']);
}
/*
* WHEN THERE IS NO SETTINGS FOR THE USER WE PICK THE DEFAULT DASHBOARD SETTINGS
*/
else{
$dbLocalStorageSettings = "{\"widget\":[]}";
$dbLocalStoragePosition = "{\"grid\":[]}";
}
/*
* Don't show widget for the following
* Security Roles
*/
$notShow = false;
if($role == "ROLE_BRANCH_USER" || $role == "ROLE_BRANCH_ADMIN"){
$notShow = true;
}
$teamCap = 0;
/*
* Check if there are saved settings
* If is ICM we load their settings 1 - 20000
*/
if ($dbLocalStorageSettings == "{\"widget\":[]}") {
$dbLocalStorageSettings = '{"widget":[{"id":"wid-id-sitenotif","title":"Sitewide Notifications","hidden":"'
. (($authChecker->isGranted("ROLE_BRANCH_USER") === true) ? 0 : 1) . '","collapsed":0, '
. (($authChecker->isGranted("ROLE_BRANCH_USER") === true) ? "\"menu\": true" : "\"menu\": false") . '},
{"id":"wid-id-all-assigned","title":"All Assigned Claims","hidden":"'
. (($authChecker->isGranted("ROLE_BRANCH_USER") === true && $role == "ROLE_ADJUSTER") ? 0 : 1) . '","collapsed":0, '
. (($authChecker->isGranted("ROLE_BRANCH_USER") === true && ($role != "ROLE_ADJUSTER" && !$notShow)) ? "\"menu\": true" : "\"menu\": false") . '},
{"id":"wid-id-all-claims-widget","title":"All Claims Widget","hidden":"'
. (($authChecker->isGranted("ROLE_BRANCH_USER") === true && $role != "ROLE_ADJUSTER") ? 0 : 1) . '","collapsed":0, '
. (($authChecker->isGranted("ROLE_BRANCH_USER") === true && $role == "ROLE_ADJUSTER") ? "\"menu\": true" : "\"menu\": false") . '},
{"id":"wid-id-bookmarked-claims-widget","title":"Bookmarked Claims Widget","hidden":"'
. (($authChecker->isGranted("ROLE_BRANCH_USER") === true) ? 0 : 1) . '","collapsed":0, '
. (($authChecker->isGranted("ROLE_BRANCH_USER") === true) ? "\"menu\": true" : "\"menu\": false") . '},
{"id":"wid-id-claims","title":"Claims Diary","hidden":"'
. (($authChecker->isGranted("ROLE_BRANCH_USER") === true) ? 0 : 1) . '","collapsed":0, '
. (($authChecker->isGranted("ROLE_BRANCH_USER") === true) ? "\"menu\": true" : "\"menu\": false") . '},
{"id":"wid-id-status","title":"Status of Claims","hidden":"'
. (($authChecker->isGranted("ROLE_BRANCH_USER") === true) ? 0 : 1) . '","collapsed":0, '
. (($authChecker->isGranted("ROLE_BRANCH_USER") === true) ? "\"menu\": true" : "\"menu\": false") . '},
{"id":"wid-id-licenses-maintenance","title":"Licenses Maintenance | Expiring in less than 60 days ","hidden":"'
. (($authChecker->isGranted("ROLE_BRANCH_USER") === true) ? 0 : 1) . '","collapsed":0, '
. (($authChecker->isGranted("ROLE_BRANCH_USER") === true) ? "\"menu\": true" : "\"menu\": false") . '},
{"id":"wid-id-forecast","title":"Forecast","hidden":1,"collapsed":0,"menu":false},
{"id":"wid-id-toploss","title":"Top Perils","hidden":"'
. (($authChecker->isGranted("ROLE_TEAM_LEADER") === true) ? 0 : 1) . '","collapsed":0,'
. (($authChecker->isGranted("ROLE_TEAM_LEADER") === true) ? "\"menu\": true" : "\"menu\": false") . '},
{"id":"wid-id-openclaimspie","title":"Open Claims","hidden":1,"collapsed":0,"menu":false},
{"id":"wid-id-available-resources","title":"Resources","hidden":"'
. (($authChecker->isGranted("ROLE_TEAM_LEADER") === true && $teamCap == 0) ? 0 : 1) . '","collapsed":0, '
. (($authChecker->isGranted("ROLE_TEAM_LEADER") === true && $teamCap == 0) ? "\"menu\": true" : "\"menu\": false") . '},
{"id":"wid-id-avg-drive-time","title":"Average Drive Time","hidden":"'
. (($authChecker->isGranted("ROLE_TEAM_LEADER") === true) ? 0 : 1) . '","collapsed":0, '
. (($authChecker->isGranted("ROLE_TEAM_LEADER") === true) ? "\"menu\": true" : "\"menu\": false") . '},
{"id":"wid-id-time-in-progress","title":"Time In Progress","hidden":"'
. (($authChecker->isGranted("ROLE_TEAM_LEADER") === true) ? 0 : 1) . '","collapsed":0, '
. (($authChecker->isGranted("ROLE_TEAM_LEADER") === true) ? "\"menu\": true" : "\"menu\": false") . '},
{"id":"wid-id-geomap-us","title":"GeoMap US","hidden":"'
. (($authChecker->isGranted("ROLE_TEAM_LEADER") === true) ? 0 : 1) . '","collapsed":0, '
. (($authChecker->isGranted("ROLE_TEAM_LEADER") === true) ? "\"menu\": true" : "\"menu\": false") . '},
{"id":"wid-id-mytasks","title":"Task created by me","hidden":"'
. (($authChecker->isGranted("ROLE_TEAM_LEADER") === true) ? 0 : 1) . '","collapsed":0, '
. (($authChecker->isGranted("ROLE_TEAM_LEADER") === true) ? "\"menu\": true" : "\"menu\": false") . '},
{"id":"wid-id-tasks","title":"Message Forum","hidden":"'
. (($authChecker->isGranted("ROLE_TEAM_LEADER") === true) ? 0 : 1) . '","collapsed":0, '
. (($authChecker->isGranted("ROLE_TEAM_LEADER") === true) ? "\"menu\": true" : "\"menu\": false") . '},
{"id":"wid-id-claims-count","title":"Claims Count","hidden":'
. (($authChecker->isGranted("ROLE_ADMIN") === true || $authChecker->isGranted("ROLE_EXAMINER") === true) ? 0 : 1) .',"collapsed":0,"menu": '
. (($authChecker->isGranted("ROLE_ADMIN") === true || $authChecker->isGranted("ROLE_EXAMINER") === true) ? 1 : 0) .'},
{"id":"wid-id-claims-status-time-spent-avg","title":"Claims Status Time Spent Avg","hidden":'
. (($authChecker->isGranted("ROLE_ADMIN") === true || $authChecker->isGranted("ROLE_EXAMINER") === true) ? 0 : 1) .',"collapsed":0,"menu": '
. (($authChecker->isGranted("ROLE_ADMIN") === true || $authChecker->isGranted("ROLE_EXAMINER") === true) ? 1 : 0) .'}]}';
}
#Forced password change after 90 Days
if(!empty($user->getPasswordChangeDate())) {
$currentDate = date('Y-m-d H:i:s');
$passwordChangeDate = $user->getPasswordChangeDate()->format('Y-m-d H:i:s');
$passwordChangeDay = round((strtotime($currentDate) - strtotime($passwordChangeDate)) / (60 * 60 * 24));
}else{
$passwordChangeDay=0;
}
$systemParamService = $this->container->get('core.system_parameter_control');
$systemAdminUsers = $systemParamService->getAccessArray('Invoicing_access');
$bShowInvoiceAccessBox = false;
$user = $this->get('security.token_storage')->getToken()->getUser();
if (!empty($systemAdminUsers) && in_array($user->getId(),$systemAdminUsers)) {
$bShowInvoiceAccessBox = true;
}
return $this->render('ClaimManagementBundle:ClaimIntakeManagement/Fragments:my-claims.html.twig',[
'adjuster' => $this->user->getId(),
'local_storage_settings' => json_encode($dbLocalStorageSettings),
'local_storage_position' => json_encode($dbLocalStoragePosition),
'local_storage_sett_obj' => $dbLocalStorageSettings,
'passwordChangeDay' => $passwordChangeDay,
'bShowInvoiceAccessBox' => $bShowInvoiceAccessBox
]);
}
/**
* @Route("/claims/datatable")
*/
public function myClaimsDatatable(Request $request)
{
# Array of columns that will be sorted
$columnsArr = [
"id",
"claim_number",
"date_received",
"insured",
"adjuster",
"state",
"loss_zip",
"loss_date",
"customer",
"status",
"color",
"notes"
];
//$resourceId = $this->get('security.token_storage')->getToken()->getUser()->getId();
$authChecker = $this->container->get('security.authorization_checker');
$user = $this->container->get('security.token_storage')->getToken()->getUser();
$resourceId = $user->getId();
$status = $request->query->get("statusId");
$dropdownValue = intval($request->query->get("dropdownValue"));
$CarrierCheck = $this->container->get("app.carrier_check");
$isCarrier = $CarrierCheck->environmentIsCarrier();
# Build Query and PDO Parameters that will be passed to PaginatorService class
$dataParams = array();
if ($authChecker->isGranted("ROLE_TEAM_LEADER") === true) {
//echo "team Leader";
$sql = "SELECT c.id, c.status_id as status, c.claim_number, cust.company_name as customer,
c.date_received, c.loss_city, s.stateName as state, c.loss_zip,
c.loss_date, CONCAT(i.first_name, ' ', i.last_name) as insured,
CONCAT(r.first_name, ' ', r.last_name) as adjuster, '' as color, special_notes as notes
FROM claim_team_resource cto
INNER JOIN claim_team_resource cto2
ON cto.team_lead = 1 AND cto.claim_team_id = cto2.claim_team_id
INNER JOIN assignments cr
ON cto2.resource_id = cr.resource_id AND IFNULL(next_assignment_id,0) = 0
AND cr.is_current_assignment = 1
INNER JOIN claim c
ON cr.claim_id = c.id
INNER JOIN customer cust on c.customer_id = cust.id
INNER JOIN state s on c.loss_state_id = s.id
LEFT JOIN resource r on cto2.resource_id = r.id
LEFT JOIN claims_insured ci on ci.claim_id = c.id
LEFT JOIN insured i ON i.id = ci.insured_id
WHERE c.status_id IN (6,10,12,13,14,15,20,23,25,100,101,103) AND cr.resource_id = :resourceId";
$dataParams = array('resourceId' => $resourceId);
}else{
//echo "rest of them";
$sql = "SELECT c.id, c.status_id as status, c.claim_number, cust.company_name as customer,
c.date_received, c.loss_city, s.stateName as state, c.loss_zip,
c.loss_date, CONCAT(i.first_name, ' ', i.last_name) as insured,
CONCAT(r.first_name, ' ', r.last_name) as adjuster, '' as color, special_notes as notes
FROM claim c "
. (($isCarrier) ? "LEFT" : "INNER") . " JOIN customer cust on c.customer_id = cust.id
INNER JOIN state s on c.loss_state_id = s.id
LEFT JOIN assignments cr on cr.claim_id = c.id
AND IFNULL(next_assignment_id,0) = 0 AND cr.is_current_assignment = 1
LEFT JOIN resource r on cr.resource_id = r.id
LEFT JOIN claims_insured ci on ci.claim_id = c.id
LEFT JOIN insured i ON i.id = ci.insured_id
WHERE " . (($isCarrier) ? "1 = 1" : "c.status_id IN (6,10,12,13,14,15,20,23,25,100,101,103)") . "
AND cr.resource_id = :resourceId";
$dataParams = array('resourceId' => $resourceId);
}
if ($status) {
# Denied claims
if ($status == -1) {
$sql .= " AND settlement_id in (4,5,6)";
} else {
# all other claims
$sql .= " AND c.status_id != 30";
$sql .= " AND c.status_id = :status";
$dataParams["status"] = $status;
}
# if status, add to array
} else {
}
# handle dropdown being passed in from pie dropdown
// switch ($dropdownValue) {
// default :
// $sql .= " AND c.date_received BETWEEN DATE_SUB(curdate(), INTERVAL 7 DAY) AND curdate()";
// break;
// case 2 :
// $sql .= " AND c.date_received BETWEEN DATE_SUB(curdate(), INTERVAL 30 DAY) AND curdate()";
// break;
// }
//echo $sql;
// echo "<pre>". $sql . "</pre>";
# build keyword filter, which is requested by datatables ajax when someone types into keyword field
$keyword_filter = "AND (c.claim_number like :keyword) ";
# specifiy group by
$group_by = "GROUP BY c.id, c.claim_number";
//return new JsonResponse(["sql" => $sql, "data" => $dataParams]); die;
# call PaginatorService
$paginator = $this->container->get("resource.paginator_service");
$data = $paginator->getPaginationResults($request, $sql, $dataParams, "", $columnsArr, "", $keyword_filter, "keyword", $group_by, "GET");
# manipulate data before sending to front end
$data['data'] = $this->manipulateClaimData($data);
return new JsonResponse($data);
}
public function manipulateClaimData($data)
{
$em = $this->getDoctrine()->getManager();
$data = $data['data'];
$result = [];
foreach ($data as $index => $row) {
$status = $em->find("ClaimManagementBundle:ClaimStatus", $row['status']);
$row['color'] = $status->getHexColor();
// $row['color'] = Claim::getStatusColor($row['status']);
$row['date_received'] = date("m/d/Y", strtotime($row['date_received']));
$row['loss_date'] = date("m/d/Y", strtotime($row['loss_date']));
# store into another array
$result[$index] = $row;
}
return $result;
}
public function getSettingsUserForDash(){
//$user = $this->get('security.token_storage')->getToken()->getUsername();
$em = $this->getDoctrine()->getManager();
$user_id = $this->user->getId();
$sql = "SELECT dashboard_settings, dashboard_position FROM resource_dashboard WHERE resource_id = ?";
//echo $sql;
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute([$user_id]);
$results = $stmt->fetchAll();
//print_r($results);
return $results;
}
/**
* @Route("/claims/save_settings")
*/
public function saveSettingsUserForDash(Request $request){
$em = $this->getDoctrine()->getManager();
$settings_to_save = $request->request->get("currentLocalStorageSettings");
$position_to_save = $request->request->get("currentLocalStoragePosition");
$user = $this->get('security.token_storage')->getToken()->getUser();
$resource_dashboard = $em->getRepository(ResourceDashboard::class)->findOneBy(array("resource"=>$user));
//If there is no existence of the user in the dashboard_settings table it CREATES
if(!$resource_dashboard){
$resource_dashboard = new ResourceDashboard();
$resource_dashboard->setResource($user);
}
if($settings_to_save && $settings_to_save != "null") {
$resource_dashboard->setDashboard($settings_to_save);
}
if($position_to_save && $position_to_save != "null") {
$resource_dashboard->setDashboardPos($position_to_save);
}
// if (!$username) {
// throw $this->createNotFoundException(
// 'No product found for id '.$username
// );
// }
$em->persist($resource_dashboard);
$em->flush();
return new JsonResponse(array('success' => true));
}
/**
* @Route("/diary_reporting")
*/
public function getDiaryReportingTool()
{
# Entity Manager
$em = $this->getDoctrine()->getManager();
$authChecker = $this->container->get('security.authorization_checker');
$logged_in_user = $this->container->get('security.token_storage')->getToken()->getUser();
$logged_in_user_id = $logged_in_user->getId();
//TEAM LEAD SECTION
$resources = array();
if ($authChecker->isGranted("ROLE_EXAMINER") || $authChecker->isGranted("ROLE_ADMIN") || $authChecker->isGranted("ROLE_SUPER_ADMIN")){
//fetch all the ACTIVE resources
$res_sql = "SELECT DISTINCT r.id, CONCAT(r.first_name,' ',r.last_name) as name
FROM resource r
INNER JOIN diary_entry de
ON de.resource_id = r.id
WHERE 1 =1
AND r.is_active = :is_active
UNION ALL
SELECT DISTINCT r.id , CONCAT(r.first_name,' ',r.last_name) as name
FROM resource r
WHERE r.id = :logged_in_user_id
ORDER BY name" ;
$res_stmt = $em->getConnection()->prepare($res_sql);
$res_stmt->execute(["is_active" => 1,"logged_in_user_id" => $logged_in_user_id]);
$resources = $res_stmt->fetchAll();
}else if ($authChecker->isGranted("ROLE_TEAM_LEADER")) {
foreach ($logged_in_user->getClaimTeams() as $claimTeamResource){
if($claimTeamResource->getIsTeamLead()){
//fetch all the resources for the team where resource id team lead
$res_sql = "SELECT DISTINCT r.id, concat(r.first_name,' ',r.last_name) as name
FROM resource r
INNER JOIN claim_team_resource ctr
ON r.id = ctr.resource_id
INNER JOIN diary_entry de
ON de.resource_id = r.id
INNER JOIN claim c
ON c.id = de.claim_id
WHERE 1 = 1
AND r.is_active = :is_active
AND ctr.claim_team_id = :team_id
AND c.claim_team_id = :team_id
UNION ALL
SELECT DISTINCT r.id , CONCAT(r.first_name,' ',r.last_name) as name
FROM resource r
WHERE r.id = :logged_in_user_id
ORDER BY name" ;
$res_stmt = $em->getConnection()->prepare($res_sql);
$res_stmt->execute(["is_active" => 1,
"team_id" => $claimTeamResource->getClaimTeam()->getId(),
"logged_in_user_id" => $logged_in_user_id]);
$resources = $res_stmt->fetchAll();
}
}
} else if ($authChecker->isGranted("ROLE_ADJUSTER")){
//fetch all the ACTIVE resources
$res_sql = "SELECT DISTINCT r.id , CONCAT(r.first_name,' ',r.last_name) as name
FROM resource r
-- INNER JOIN diary_entry de
-- ON de.resource_id = r.id
WHERE r.id = :logged_in_user_id
ORDER BY name" ;
$res_stmt = $em->getConnection()->prepare($res_sql);
$res_stmt->execute(["logged_in_user_id" => $logged_in_user_id]);
$resources = $res_stmt->fetchAll();
}
return $this->render('ClaimManagementBundle:ClaimIntakeManagement/Fragments:diary-objects.html.twig',[
"resources" => $resources
]);
}
/**
* @Route("/diary_reporting_datatable")
*/
public function getDiaryReportingDatatable(Request $request)
{
# Entity Manager
$em = $this->getDoctrine()->getManager();
$authChecker = $this->container->get('security.authorization_checker');
$logged_in_user = $this->container->get('security.token_storage')->getToken()->getUser();
$logged_in_user_id = $logged_in_user->getId();
$dataParams = array();
$dataParams["resource_id"] = $request->query->get("resourceId");
$sql = "SELECT de.*,
c.claim_team_id AS claim_team_id,
c.id AS claim_id,
c.claim_number AS claim_num,
c.date_received as claim_received_date,
CONCAT(ins.first_name, ' ' , ins.last_name) as claim_insured,
st.stateCode AS claim_state,
c.loss_zip AS claim_zip,
CONCAT(adj.first_name, ' ' , adj.last_name) as adjuster,
cust.company_name AS customer,
p.description AS loss_type,
CONCAT(res.first_name, ' ' , res.last_name) as owner,
CONCAT(author.first_name, ' ' , author.last_name) as author
FROM diary_entry de
INNER JOIN claim c
ON c.id = de.claim_id
LEFT JOIN claims_insured ci
ON ci.claim_id = c.id AND ci.is_primary = 1
LEFT JOIN insured ins
ON ins.id = ci.insured_id
LEFT JOIN resource res
ON res.id = de.resource_id
LEFT JOIN resource author
ON author.id = de.author_id
LEFT JOIN state st
ON st.id = c.loss_state_id
LEFT JOIN assignments cr ON cr.claim_id = c.id AND cr.is_primary_adjuster = 1
AND IFNULL(cr.next_assignment_id,0) = 0 AND cr.is_current_assignment = 1
LEFT JOIN resource adj ON adj.id = cr.resource_id
LEFT JOIN customer cust ON c.customer_id = cust.id
LEFT JOIN perils p ON p.id = c.peril_id
WHERE de.resource_id = :resource_id
AND de.status = 0 ";
if($request->query->get("filter") == 1){
$sql .= "AND DATE_FORMAT(de.dueDate,'%Y-%m-%d') = :today";
$dataParams["today"] = date('Y-m-d');
if ($request->query->get("searchFilter")){
$searchFilter = $request->query->get("searchFilter");
$sql .= " AND c.claim_number like '". $searchFilter . "%'";
}
}else if($request->query->get("filter") == 2){
$sql .= "AND de.dueDate < :today";
$dataParams["today"] = date('Y-m-d');
if ($request->query->get("searchFilter")){
$searchFilter = $request->query->get("searchFilter");
$sql .= " AND c.claim_number like '". $searchFilter . "%'";
}
}else if($request->query->get("filter") == 3 ){
// echo "POLLO";
if($request->query->get("start_date") && $request->query->get("end_date")) {
$sql .= "AND DATE_FORMAT(de.dueDate,'%Y-%m-%d') BETWEEN :start_date AND :end_date";
$dataParams["start_date"] = date('Y-m-d', strtotime($request->query->get("start_date")));
$dataParams["end_date"] = date('Y-m-d', strtotime($request->query->get("end_date")));
// echo $dataParams["start_date"];
}
if ($request->query->get("searchFilter")){
$searchFilter = $request->query->get("searchFilter");
$sql .= " AND c.claim_number like '". $searchFilter . "%'";
}
}else{
if ($request->query->get("searchFilter")){
$searchFilter = $request->query->get("searchFilter");
$sql .= " AND c.claim_number like '". $searchFilter . "%'";
}
}
//echo $sql;
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute($dataParams);
$DiaryEntry = $stmt->fetchAll();
$diary_report = array();
foreach ($DiaryEntry as $diaryEntryRow){
if ($authChecker->isGranted("ROLE_TEAM_LEADER")
&& !$authChecker->isGranted("ROLE_ADMIN")
&& !$authChecker->isGranted("ROLE_EXAMINER")
&& $logged_in_user_id <> $dataParams["resource_id"] ) {
echo $diaryEntryRow['claim_team_id'] . " ";
$isTeamLead = false;
foreach ($logged_in_user->getClaimTeams() as $claimTeamResource) {
if ($claimTeamResource->getIsTeamLead()) {
if($claimTeamResource->getClaimTeam()->getId() == $diaryEntryRow['claim_team_id'])
{
$isTeamLead = true;
}
}
}
if(!$isTeamLead){
continue;
}
}
$diary_report_row['diary_id'] = $diaryEntryRow['id'];
$diary_report_row['claim_id'] = $diaryEntryRow['claim_id'];
$diary_report_row['claim_num'] = $diaryEntryRow['claim_num'];
$diary_report_row['due_date'] = $diaryEntryRow['dueDate'];
$diary_report_row['owner'] = $diaryEntryRow['owner'];
$diary_report_row['description'] = $diaryEntryRow['description'];
$diary_report_row['create_date'] = $diaryEntryRow['dateCreated'];
$diary_report_row['author'] = $diaryEntryRow['author'];
$diary_report_row['priority'] = $diaryEntryRow['priority'];
$diary_report_row['claim_received_date'] = $diaryEntryRow['claim_received_date'];
$diary_report_row['claim_insured'] = $diaryEntryRow['claim_insured'];
$diary_report_row['claim_state'] = $diaryEntryRow['claim_state'];
$diary_report_row['claim_zip'] = $diaryEntryRow['claim_zip'];
$diary_report_row['adjuster'] = $diaryEntryRow['adjuster'];
$diary_report_row['customer'] = $diaryEntryRow['customer'];
$diary_report_row['loss_type'] = $diaryEntryRow['loss_type'];
$diary_report[] = $diary_report_row;
}
return $this->render('ClaimManagementBundle:ClaimIntakeManagement/Fragments/widgets:diary-objects-widget.html.twig',[
"diary_report" =>$diary_report
]);
}
/**
* @Route("/searchfileorclaim")
*/
public function searchfileorclaimAction(Request $request)
{
$em = $this->getDoctrine()->getManager();
$term = $request->request->get("file_claim_id");
$response['error'] = false;
if (preg_match('/[\'^£$%&*()}{@#~?><>,|=_+¬]/', $term))
{
// one or more of the 'special characters' found in $string
$response['error'] = true;
return new JsonResponse($response);
}
if (preg_match('/^[0-9]+-[0-9]+$/i', $term)) {
// contains a dash
$claim= $em->getRepository("ClaimManagementBundle:Claim")->findOneBy(['claimNumber' => $term]);
if(empty($claim)){
$response['error'] = true;
}else{
$response['data']['id']= $claim->getId();
}
} else {
// does not contain dash
$claim= $em->getRepository("ClaimManagementBundle:Claim")->findOneBy(['claimNumber' => $term]);
if(empty($claim)){
$claim= $em->getRepository("ClaimManagementBundle:Claim")->find($term);
}
}
if(empty($claim)){
$response['error'] = true;
}else{
$response['data']['id']= $claim->getId();
}
return new JsonResponse($response);
}
}

src/Globals/ClaimManagementBundle/Controller/ClaimIntakeFragmentsController.php line 586