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

Open in your IDE?
  1. <?php
  3. namespace Globals\ClaimManagementBundle\Controller;
  5. use Globals\ResourceManagementBundle\Entity\Resource;
  6. use Globals\ResourceManagementBundle\Entity\ResourceDashboard;
  7. use Globals\ResourceManagementBundle\Service\CarrierUtils;
  8. use Globals\ResourceManagementBundle\Service\SLogger;
  9. use Globals\ResourceManagementBundle\Twig\UCFirstAll;
  10. use Symfony\Bundle\FrameworkBundle\Controller\Controller;
  11. use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
  12. use Symfony\Component\HttpFoundation\JsonResponse;
  13. use Symfony\Component\HttpFoundation\Response;
  14. use Symfony\Component\HttpFoundation\Request;
  15. use Globals\ClaimManagementBundle\Entity\Claim;
  16. use Globals\ClaimManagementBundle\Entity\ClaimStatus;
  20. /**
  21.  * @Route("/administration/manage")
  22.  */
  23. class ClaimIntakeFragmentsController extends Controller
  24. {
  25.     private $user = null;
  26.     /**
  27.      * @var array
  28.      */
  29.     private $loss_type_map = [
  30.         'AIR CONDITIONING VESSEL' =>    100,
  31.         'AIRCRAFT-DAMAGE BY' =>    123,
  32.         'All Other Physical Damage' =>    100,
  33.         'BI' =>    2,
  34.         'BOP-LOSS OF RENTS' =>    100,
  35.         'BUILDING' =>    95,
  36.         'BUILDING AND CONTENTS' =>    95,
  37.         'Burglary/theft/robbery-money or securities' =>    124,
  38.         'Business Income' =>    100,
  39.         'Business Personal Property' =>    100,
  40.         'Chubb Fire – Lithium Battery' =>    6,
  41.         'COLLAPSE' =>    104,
  42.         'CONSTRUCTION DEFECTS' =>    96,
  43.         'CONTENTS' =>    100,
  44.         'CPL - ANIMAL BITE' =>    96,
  45.         'CPL - BODILY INJURY' =>    96,
  46.         'CPL - PROPERTY DMG' =>    95,
  47.         'CPL-CARE OR CUSTDY OR CTRL' =>    95,
  48.         'CYBER CRIME' =>    100,
  49.         'Cyber Liability' =>    96,
  50.         'DAMAGE UNKNOWN' =>    101,
  51.         'DENIAL' =>    100,
  52.         'Denied/Do No Report' =>    100,
  53.         'Diving Board Liab' =>    96,
  54.         'EARTH MOVEMENT/ SETTLEMENT/ RESULTANT CRACKING' =>    165,
  55.         'EARTHQUAKE' =>    165,
  56.         'ELECTRICAL OR PWR SURGE' =>    100,
  57.         'EMPLOYEE DISHONESTY' =>    100,
  58.         'EQUIPMENT BREAKDOWN' =>    163,
  59.         'EXPLOSION' =>    126,
  60.         'EXTRA CONTRACTUAL' =>    100,
  61.         'FALLING OBJECTS' =>    161,
  62.         'FIRE' =>    6,
  63.         'FIRE LEGAL LIABILITY' =>    96,
  64.         'FLOOD' =>    98,
  65.         'FREEZING - ICE' =>    7,
  66.         'GLASS - HOMEOWNERS' =>    113,
  67.         'GOLF CART LIABILITY' =>    96,
  68.         'HAIL' =>    147,
  69.         'HURRICANE' =>    20,
  70.         'IDENTITY THEFT' =>    100,
  71.         'LEAD LIABILITY    ' =>100,
  72.         'Liability - All Other' =>    96,
  73.         'LIGHTNING' =>    12,
  74.         'LOSS OF WAGES' =>    100,
  75.         'MED-PAY' =>    100,
  76.         'MISC. - ALL RISK' =>    100,
  77.         'MOLD' =>    100,
  78.         'MONEY/CURRENCY/BANK NOTES/CKS' =>    100,
  79.         'MYSTERIOUS DISAPPEAR' =>    15,
  80.         'NO INJURIES' =>    100,
  81.         'NOC' =>    100,
  82.         'OTHER' =>    100,
  83.         'OTHER STRUCTURES' =>    100,
  84.         'PERS INJ PROT OR (PIP)' =>    100,
  85.         'PERSONAL INJURY HO' =>    100,
  86.         'PROPERTY' =>    95,
  87.         'PROPERTY DAMAGE' =>    95,
  88.         'Pypestream Other' =>    100,
  89.         'RIOT OR CIVIL COMMOTION' =>100,
  90.         'Rodent/Vermin' =>    117,
  91.         'Service Line Coverage' =>    100,
  92.         'SEWER  OR  SEWAGE' =>    134,
  93.         'SMOKE' =>    128,
  94.         'STO - Burg, Theft & Rob - Money/Securities' =>    15,
  95.         'STO - Burglary - Outside' =>    15,
  96.         'STO - Burglary/theft/robbery – other' =>    15,
  97.         'STO - Earthquake' =>    165,
  98.         'STO - Fire and Lightning' =>    6,
  99.         'STO - Flood' =>    98,
  100.         'STO - Hurricane' =>    20,
  101.         'STO - Misc - Property Damage' =>    95,
  102.         'STO - Misc animal or insect' =>    100,
  103.         'STO - Mysterious Disappearance' =>    15,
  104.         'STO - Other' =>    100,
  105.         'STO - Property - Mold, Mildew and Dry rot' =>    100,
  106.         'STO - Property - Wind or Hail' =>    53,
  107.         'STO - Property Damage - Outside' =>    95,
  108.         'STO - Severe Storm' =>    23,
  109.         'STO - Sprinkler leakage' =>    102,
  110.         'STO - Vandalism' =>    17,
  111.         'STO - Water Damage' =>    102,
  112.         'STO - Water Damage - Broken Pipe' =>    102,
  113.         'STO - Water from roof' =>    102,
  114.         'STO - Wildfire' =>    157,
  115.         'STO - Winter Storm' =>    23,
  116.         'SUBSIDE OR SOIL MOVMENT' =>    100,
  117.         'Theft - Burglary Or Robbery - From Auto' =>    100,
  118.         'THEFT- OFF PREMISES' =>    15,
  119.         'THEFT ON PREMISES' =>    15,
  120.         'TORNADO' =>    16,
  121.         'TREE ROOTS - DMGD BY' =>    100,
  122.         'TRIP COLLISION' =>    100,
  123.         'TX ONLY - Applicance-Water' =>    102,
  124.         'VANDALISM OR  MAL MISCH' =>    124,
  125.         'VEHICLE - DAMAGED BY' =>    123,
  126.         'Vehicle (Struck By Vehicle & Damaged)' =>    123,
  127.         'VOLCANIC ERUPTION' =>    117,
  128.         'W OR C - MED.EXPENSES' =>    100,
  129.         'W OR C - TTL TEMP DISBL' =>    100,
  130.         'W OR C - VOC. REHAB.' =>    100,
  131.         'W OR C-PERMANENT DSBLTY' =>    100,
  132.         'WATER - BACKUP' =>    153,
  133.         'WATER - OTHER' =>    102,
  134.         'WATER - WEATHER' =>    102,
  135.         'WATERCRAFT LIABILITY' =>    96,
  136.         'WATER-PIPE BREAK' =>    102,
  137.         'WATER-SUMP OVERFLOW' =>    102,
  138.         'WEIGHT ICE OR SNOW' =>    143,
  139.         'WILDFIRE' =>    157,
  140.         'WILDFIRE SMOKE' =>    157,
  141.         'Wind - Catastrophe' =>    138,
  142.         'WINDSTORM' =>    138
  144.     ];
  146.     // Comment here
  148.     /**
  149.      * @Route("/unassigned")
  150.      */
  151.     public function unassignedViewFragment()
  152.     {
  153.         $em = $this->get('doctrine')->getManager();
  154.         $CarrierCheck = $this->container->get("app.carrier_check");
  155.         $envService = $this->get('core.get_environment');
  157.         $tokenStorage = $this->container->get("security.token_storage");
  158.         $User = $tokenStorage->getToken()->getUser();
  159.         $isTlg = $User instanceof Resource && $User->getIsTlg();
  161.         $companyCode = $envService->getCompanyCode();
  163.         // If the user is a TLG user, we'll return an empty view or a message
  164.        // if ($isTlg) {
  165.           //  return $this->render('ClaimManagementBundle:ClaimIntakeManagement/Fragments:unassigned_empty.html.twig', [
  166.             //    'message' => 'Unassigned claims are not available for TLG users.',
  167.               //  'companyCode' => $companyCode
  168.            // ]);
  169.       //  }
  171.         $claimRepo = $em->getRepository("ClaimManagementBundle:Claim");
  172.         $adjusterRepo = $em->getRepository("ResourceManagementBundle:Resource");
  173.         $claimTeamRepo = $em->getRepository("ClaimManagementBundle:ClaimTeam");
  175.         $claimTeams = $claimTeamRepo->findBy(['enabled' => true], array('name' => 'ASC'));
  177.         $sql = "SELECT id, resourceType FROM resource_type";
  179.         if ($envService->getIsCarrier()) {
  180.             $sql .= " WHERE id != 99";
  181.         } else {
  182.             $sql .= " WHERE id < 90 AND id != 3";
  183.         }
  185.         $stmt = $em->getConnection()->prepare($sql);
  186.         $stmt->execute();
  188.         $resourceTypes = $stmt->fetchAll(\PDO::FETCH_ASSOC);
  190.         $assignmentParties = $em->getRepository("AssignmentBundle:AssignmentParty")->findAll();
  192.         return $this->render('ClaimManagementBundle:ClaimIntakeManagement/Fragments:unassigned.html.twig',[
  193.             'resourceTypes' => $resourceTypes,
  194.             'assignmentParties' => $assignmentParties,
  195.             'teams' => $claimTeams,
  196.             'companyCode' => $companyCode
  197.         ]);
  198.     }
  199.     // Comment here
  200.     /**
  201.      * @Route("/unassignedmap/{customer_id}/{team_id}", defaults={"customer_id" = null,"team_id" = null})
  202.      */
  204.     public function unassignedViewMap(Request $request, $customer_id, $team_id)
  205.     {
  206.         $em = $this->get('doctrine')->getManager();
  207.         $CarrierCheck = $this->container->get("app.carrier_check");
  208.         $envService = $this->get('core.get_environment');
  210.         $claimRepo = $em->getRepository("ClaimManagementBundle:Claim");
  211.         $adjusterRepo = $em->getRepository("ResourceManagementBundle:Resource");
  212.         $claimTeamRepo = $em->getRepository("ClaimManagementBundle:ClaimTeam");
  213.         $Customers = $em->getRepository("CustomerManagementBundle:Customer")->findBy([], ['companyName' => 'ASC']);
  214.         $resultsetClaimTeam = array();
  215.         $adjusters = $adjusterRepo->findBy(['resourceType'=>array(2,3,4)]);
  216.         $claimTeams = $claimTeamRepo->findBy(['enabled'=>true], array('name' => 'ASC'));
  218.         $sql = "SELECT id, resourceType
  219.               FROM resource_type";
  221.         /**
  222.          * Don't select exception resource types for specific auto assigner instances
  223.          */
  224.         if ($envService->getIsCarrier()) {
  225.             # Hide "None", its a auto assigner feature
  226.             $sql .= " WHERE id != 99";
  227.         } else {
  228.             # ICM doesnt use these resource types
  229.             $sql .= " WHERE id < 90 AND id != 3";
  230.         }
  232.         $stmt = $em->getConnection()->prepare($sql);
  233.         $stmt->execute();
  234.         $resourceTypes = $stmt->fetchAll(\PDO::FETCH_ASSOC);
  236.         $CarrierCheck = $this->container->get("app.carrier_check");
  237.         $isCarrier = $CarrierCheck->environmentIsCarrier();
  239.         $tokenStorage = $this->container->get("security.token_storage");
  240.         $User = $tokenStorage->getToken()->getUser();
  242.         $isTeamLeader = false;
  243.         $isAdjuster = false;
  244.         $isBranchAdmin = false;
  245.         $isBranchUser = false;
  246.         if ($this->isGranted("ROLE_ADJUSTER") === true && $this->isGranted("ROLE_TEAM_LEADER") === false) {
  247.             $isAdjuster = true;
  248.         } else if ($this->isGranted("ROLE_TEAM_LEADER") === true && ($this->isGranted("ROLE_ADMIN") === false && $this->isGranted("ROLE_EXAMINER") === false)) {
  249.             $isTeamLeader = true;
  250.         } else if ($this->isGranted("ROLE_BRANCH_ADMIN") === true && $this->isGranted("ROLE_ADJUSTER") === false) {
  251.             $isBranchAdmin = true;
  252.         } else if ($this->isGranted("ROLE_BRANCH_USER") === true && $this->isGranted("ROLE_BRANCH_ADMIN") === false) {
  253.             $isBranchUser = true;
  254.         }
  256.         # Build Query and PDO Parameters that will be passed to PaginatorService class
  257.         $dataParams = array();
  258.         $sqlUnassign = "SELECT c.id, 
  259.                 c.id as file_number, 
  260.                 c.claim_number as claim_number, 
  261.                   c.catastrophe_code as catastrophe_code,
  262.                 CONCAT(i.first_name, ' ', i.last_name) as insured_member, 
  263.                 CONCAT(ar.first_name,' ',ar.last_name) as assistive_resource,
  264.                 i.address as address, 
  265.                 cust.company_name as company_name,
  266.                 c.date_received as date_received, 
  267.                 c.loss_city as loss_city, 
  268.                 s.stateName as state, 
  269.                 c.loss_zip, DATE_FORMAT(c.loss_date, '%m/%d/%Y') as loss_date, 
  270.                  c.loss_latitude as loss_latitude,
  271.                  c.loss_longitude as loss_longitude, 
  272.                 
  273.                 c.status_id as status_id, 
  274.                 c.date_received, 
  275.                 c.loss_description,
  276.                 p1.description as loss_type
  277.                 FROM claim c "
  278.             . (($isCarrier) ? "LEFT" : "INNER") . " JOIN customer cust on c.customer_id = cust.id
  279.                 LEFT JOIN state s
  280.                 on c.loss_state_id = s.id
  281.                 LEFT JOIN perils p1
  282.                   ON c.peril_id = p1.id
  283.                 LEFT JOIN claims_insured ci
  284.                   ON c.id = ci.claim_id AND ci.is_primary = 1
  285.                 LEFT JOIN insured i
  286.                   ON ci.insured_id = i.id " .
  287.             (($isAdjuster) ? "INNER" : "LEFT") . " JOIN assignments cr 
  288.                     ON c.id = cr.claim_id AND IFNULL(cr.next_assignment_id,0) = 0 AND cr.is_current_assignment = 1
  289.                 LEFT JOIN resource r 
  290.                     ON cr.resource_id = r.id " .
  291.             (($isBranchAdmin) ? "INNER" : "LEFT") . " JOIN customer_resource cust_res 
  292.                     ON c.customer_id = cust_res.customer_id ";
  293.         if ($isTeamLeader) {
  294.             $sqlUnassign .= "INNER JOIN claim_team_resource ctr 
  295.                         ON c.claim_team_id = ctr.claim_team_id ";
  296.         }
  298.         /*
  299.          * 7/26/2019 Added assistive adjuster update
  300.          *
  301.          */
  302.         $sqlUnassign .= "LEFT JOIN resource ar ON ar.id = c.assistive_resource_id ";
  304.         $sqlUnassign .= "WHERE c.loss_longitude<>'0' ";
  305.         // $sql .= "WHERE 1 = 1";
  307.         // if ($resourceId > 0) {
  308.         $sqlUnassign .= " AND c.status_id < 10 ";
  309.         // }
  310.         if($customer_id>0) {
  311.             $sqlUnassign .= "AND c.customer_id = :customer_id ";
  312.             $dataParams['customer_id'] = $customer_id;
  313.         }
  314.         $sqlUnassign .= " GROUP BY c.id ";
  315.         $stmt = $em->getConnection()->prepare($sqlUnassign);
  316.         $stmt->execute($dataParams);
  317.         $resultset = $stmt->fetchAll(\PDO::FETCH_ASSOC);
  318.  
  319.         $i = 0;
  320.         $markerData = array();
  321.        foreach($resultset as $key=>$value){
  322.            $markerData[$i]['longitude'] = $value['loss_longitude'];
  323.            $markerData[$i]['latitude'] =  $value['loss_latitude'];
  324.            $markerData[$i]['claimnumber'] =  $value['file_number'];
  325.            if(!empty($value['loss_type']))
  326.                 $markerData[$i]['loss_type'] = $value['loss_type'];
  327.            else
  328.                $markerData[$i]['loss_type'] = 'NA';
  329.            $date=date_create($value['date_received']);
  330.            $date_received =  date_format($date,"m/d/Y");
  331.            $date1 = new \DateTime("now");
  332.            $date2 = new \DateTime(date_format($date,"m/d/Y H:i:s"));
  333.            $interval = $date1->diff($date2);
  334.            $daysDiff = $interval->days;
  335.            $hrsDiff = $interval->h;
  336.            $totalHrs = ($daysDiff*24 + $hrsDiff);
  337.            $markerData[$i]['date_received'] = $date_received;
  338.            $markerData[$i]['interval'] = $totalHrs;
  339.           // $markerData[$i]['days'] = $interval->days;
  340.            //$markerData[$i]['hr'] = $interval->h;
  341.            $markerData[$i]['type'] ='claim';
  342.            $markerData[$i]['id'] = $value['file_number'];
  343.            $markerData[$i]['customerName'] = $value['company_name'];
  344.            if($totalHrs>8)
  345.                 $markerData[$i]['iconUrl'] = '/img/pink-dot.png';
  346.            else
  347.                $markerData[$i]['iconUrl'] = '/img/blue-dot.png';
  348.            $i++;
  349.        }
  351.         if($customer_id>0){
  352.             $dataParamsClaimTeam  = array();
  353.             $dataParamsClaimTeam['customer_id'] = $customer_id;
  354.             $sqlClaimTeam  = "SELECT name,id FROM claim_team ct";
  356.             $sqlClaimTeam .= " WHERE  ct.customer_id = :customer_id and enabled=1 ORDER BY name";
  357.             $stmtClaimTeam  = $em->getConnection()->prepare($sqlClaimTeam);
  358.             $stmtClaimTeam->execute($dataParamsClaimTeam);
  360.             $resultsetClaimTeam  = $stmtClaimTeam->fetchAll(\PDO::FETCH_ASSOC);
  362.             $dataParamsClaimTeamAdj = array();
  363.             if($team_id>0) {
  365.                 $dataParamsClaimTeamAdj['claim_team_id'] = $team_id;
  366.                 $sqlClaimTeamAdj = "SELECT r.id, 
  367.                 CONCAT(r.first_name, ' ', r.last_name) as team_member_name,r.latitude,r.longitude,r.email,r.phone FROM resource r  
  368.                 INNER JOIN claim_team_resource ctr on r.id = ctr.resource_id 
  369.                
  370.                 ";
  372.                 $sqlClaimTeamAdj .= "WHERE  r.is_active=1 and ctr.claim_team_id = :claim_team_id ";
  373.                 $stmtClaimTeamAdj = $em->getConnection()->prepare($sqlClaimTeamAdj);
  374.                 $stmtClaimTeamAdj->execute($dataParamsClaimTeamAdj);
  375.                 $resultsetClaimTeamAdj = $stmtClaimTeamAdj->fetchAll(\PDO::FETCH_ASSOC);
  376.                 
  377.                 if (!empty($resultsetClaimTeamAdj)) {
  378.                     foreach ($resultsetClaimTeamAdj as $key => $value) {
  380.                           $markerData[$i]['longitude'] = $value['longitude'];
  381.                           $markerData[$i]['latitude'] =  $value['latitude'];
  384.                         $markerData[$i]['phone'] = $value['phone'];
  386.                         $sqlClaimCnt = "SELECT count(claim_id) as count_of_claims
  387.                             FROM assignments cr 
  388.                             INNER JOIN claim c 
  389.                             ON cr.claim_id = c.id
  390.                             AND IFNULL(next_assignment_id,0) = 0
  391.                             AND is_current_assignment = 1
  392.                             WHERE 1 = 1
  393.                             AND c.status_id < 30
  394.                             AND cr.resource_id = :resource_id";
  396.                         $dataParamsClaimCnt = array();
  397.                         $dataParamsClaimCnt['resource_id'] = $value['id'];
  399.                         $stmtClaimCnt = $em->getConnection()->prepare($sqlClaimCnt);
  401.                         $stmtClaimCnt->execute($dataParamsClaimCnt);
  403.                         $numOfClaims = $stmtClaimCnt->fetchAll(\PDO::FETCH_ASSOC);
  405.                         foreach ($numOfClaims as $numOfClaim){
  406.                             $markerData[$i]['claim_assigned']= $numOfClaim['count_of_claims'];
  407.                         }
  411.                         $markerData[$i]['email'] = $value['email'];
  412.                         $value['team_member_name'] = str_replace("'","",$value['team_member_name']);
  413.                         $markerData[$i]['customerName'] = str_replace('"',"",$value['team_member_name']);
  414.                         $markerData[$i]['type'] ='adjuster';
  415.                        $markerData[$i]['iconUrl'] = '/img/icon.png';
  421.                          $i++;
  422.                     }
  423.                 }
  424.             }
  426.         }
  427. //dd($markerData);
  428.        // $markerData[0]['longitude'] = '-111.8265049';
  429.        // $markerData[0]['latitude'] = '34.8791806';
  430.       //  $markerData[0]['claimnumber'] = '1111111323-746456-7857657';
  431.        // $markerData[0]['customerName'] = 'USAA CAT1';
  432.        // $markerData[0]['iconUrl'] = 'http://maps.google.com/mapfiles/ms/icons/pink-dot.png';
  434.         //$markerData[1]['longitude'] = '-111.7695277';
  435.        // $markerData[1]['latitude'] = '34.832149';
  436.         //$markerData[1]['claimnumber'] = '22222323-746456-7857657';
  437.        // $markerData[1]['customerName'] = 'USAA CAT2';
  438.        // $markerData[1]['iconUrl'] = 'http://maps.google.com/mapfiles/ms/icons/blue-dot.png';
  439.         return $this->render('ClaimManagementBundle:ClaimIntakeManagement/Fragments:unassigned_map.html.twig',[
  440.             'adjusters' => $adjusters,
  441.             'resourceTypes' => $resourceTypes,
  442.             "Customers" => $Customers,
  443.             'teams' => $claimTeams,'creation'=>true,
  444.             'customer_id'=>$customer_id,
  445.             'companyCode' => $envService->getCompanyCode(),'markerData'=>$markerData,'resultsetClaimTeam'=>$resultsetClaimTeam,'team_id'=>$team_id
  446.         ]);
  447.     }
  449.     // Comment here
  450.     /**
  451.      * @Route("/sendtoxact/{claim_id}/{sendflg}", defaults={"claim_id" = null,"sendflg" = null})
  452.      */
  454.     public function sendclaimxact(Request $request, $claim_id,$sendflg)
  455.     {
  456.         $em = $this->get('doctrine')->getManager();
  458.         $sentToXact = 'N';
  459.         $claimData = array();
  460.         $claimRepo = $em->getRepository("ClaimManagementBundle:Claim");
  461.         $claimNotFoundErr = '';
  462.         $claimSentXact  = '';
  463.         if($claim_id!='') {
  464.             $claimData = $claimRepo->findOneBy(['id' => $claim_id]);
  466.             if (empty($claimData)) {
  467.                 $claimData = $claimRepo->findOneBy(['claimNumber' => $claim_id]);
  469.             }
  470.             $claimNotFoundErr = 'N';
  471.             if (empty($claimData)) {
  472.                 $claimNotFoundErr = 'Y';
  473.             }
  474.             if(!empty($claimData) && $claimData->getTransactionId()!=''){
  475.                 $claimSentXact = 'Y';
  476.             }else{
  477.                 $claimSentXact = 'N';
  478.             }
  479.             if($sendflg=='Y'){
  480.                 $xact_service = $this->container->get('integration.xact.outbound');
  481.                 $xact_data = array();
  482.                 
  483.                 if($claimData->getCustomer()->getCarrierId()!='') {
  484.                     $xact_data["carrier_id"] = $claimData->getCustomer()->getCarrierId();
  485.                     $xact_data["customer_name"] = $claimData->getCustomer()->getCompanyName();
  486.                     $xact_data["recipientsXNAddress"] = 'IAS_CLAIMS.HOME.WEB';
  489.                     $sqlInsured = "SELECT
  490.                 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
  491.                 FROM claims_insured ci
  492.                 INNER JOIN insured i
  493.                   ON ci.insured_id = i.id
  494.                 INNER JOIN claim_insured_types cit
  495.                   ON ci.claim_insured_type_id = cit.id
  496.                 LEFT JOIN state s
  497.                   ON i.state_id = s.id
  498.                 WHERE claim_id = '" . $claimData->getId() . "' limit 1";
  500.                     # Search By Claim ID
  502.                     $stmt = $em->getConnection()->prepare($sqlInsured);
  503.                     $stmt->execute();
  504.                     $insuredResults = $stmt->fetchAll(\PDO::FETCH_ASSOC);
  505.                     //dd($insuredResults);
  506.                     $xact_data["insured_name"] = $insuredResults[0]['insuredName'];
  507.                     $xact_data["InsuredHomeNumber"] = preg_replace('/[^0-9]/', '',$insuredResults[0]['phone']);
  508.                     $xact_data["address"] = $insuredResults[0]['address'];
  509.                     $xact_data["prov"] = $insuredResults[0]['stateCode'];
  510.                     $xact_data["postal"] = $insuredResults[0]['zip_code'];
  511.                     $xact_data["city"] = $insuredResults[0]['city'];
  512.                     $xact_data["loss_city"] = $claimData->getLossCity();
  513.                     $xact_data["loss_street"] = $claimData->getLossStreet();
  514.                     $xact_data["loss_state"] = $claimData->getLossState()->getStateCode();
  515.                     $xact_data["loss_zip"] = $claimData->getLossZip();
  516.                     $xact_data["dateOfLoss"] = $claimData->getLossDate()->format('Y-m-d');
  517.                     $xact_data["policyNumber"] = $claimData->getPolicyNumber();
  518.                     $xact_data["claimNumber"] = $claimData->getClaimNumber();
  519.                     $xact_data["notes"] = $claimData->getSpecialNotes();
  520.                     $xact_data["catCode"] = $claimData->getCatastropheCode();
  521.                     $xact_data["ClaimLossDesc"] = substr($claimData->getLossDescription(),0,34);
  522.                     $xact_data["policyStart"] = $claimData->getPolicyStartDate()->format('Y-m-d');
  523.                     $xact_data["policyEnd"] = $claimData->getPolicyEndDate()->format('Y-m-d');
  524.                     $xact_data["dateOfRecieve"] = date('Y-m-d');
  525.                     $xact_data["loss_type"] = strtoupper($claimData->getPeril()->getDescription());
  526.                     $coverageRepo = $em->getRepository("ClaimManagementBundle:Coverage");
  528.                     $coverageData = $coverageRepo->findBy(['claim' => $claimData]);
  529.                     $coveragesArray = array();
  530.                     if (!empty($coverageData)) {
  531.                         foreach ($coverageData as $coverageData) {
  532.                             if ($coverageData->getName() == 'Dwelling') {
  533.                                 $coveragesArray[0]['name'] = 'Dwelling';
  534.                                 $coveragesArray[0]['id'] = 'COV1';
  535.                                 $coveragesArray[0]['type'] = 0;
  536.                                 $coveragesArray[0]['amt'] = floatval($coverageData->getAmount());
  537.                             }
  538.                             if ($coverageData->getName() == 'APS') {
  539.                                 $coveragesArray[1]['id'] = 'COV3';
  540.                                 $coveragesArray[1]['name'] = 'Aps';
  541.                                 $coveragesArray[1]['type'] = 1;
  542.                                 $coveragesArray[1]['amt'] = floatval($coverageData->getAmount());
  543.                             }
  544.                             if ($coverageData->getName() == 'Contents') {
  545.                                 $coveragesArray[2]['id'] = 'COV2';
  546.                                 $coveragesArray[2]['name'] = 'Contents';
  547.                                 $coveragesArray[2]['type'] = 2;
  548.                                 $coveragesArray[2]['amt'] = floatval($coverageData->getAmount());
  549.                             }
  550.                             if ($coverageData->getName() == 'ALE') {
  551.                                 $coveragesArray[3]['id'] = 'COV4';
  552.                                 $coveragesArray[3]['name'] = 'ALE';
  553.                                 $coveragesArray[3]['type'] = 0;
  554.                                 $coveragesArray[3]['amt'] = floatval($coverageData->getAmount());
  555.                             }
  556.                         }
  557.                     }
  558.                     if (!empty($coveragesArray)) {
  559.                         $xact_data['coverages'] = $coveragesArray;
  560.                     }
  561.                     $resXect = $xact_service->exportCreateClaim($xact_data);
  563.                     $claimData->setTransactionId($resXect['transactionId']);
  564.                     if ($resXect['errorDescription'] != '') {
  565.                         $claimData->setXactErrorMessage($resXect['errorDescription']);
  566.                         $claimData->setXactErrorCode($resXect['errorCode']);
  567.                         $sentToXact = 'E';
  568.                     } else {
  569.                         $claimData->setXactErrorMessage('Claim sent successfully');
  570.                         $claimData->setXactErrorCode('No Error');
  571.                         $sentToXact = 'Y';
  572.                     }
  573.                     $em->persist($claimData);
  575.                     // dump($importable);
  577.                     // actually flush into the db
  578.                     $em->flush();
  579.                 }else{
  580.                     $sentToXact = 'C';
  581.                 }
  583.             }
  584.         }
  585.         return $this->render('ClaimManagementBundle:ClaimIntakeManagement/Fragments:send_claim_xact.html.twig',['claim_id'=>$claim_id,'claimNotFoundErr'=>$claimNotFoundErr,'claimSentXact'=>$claimSentXact,'sentToXact'=>$sentToXact
  587.         ]);
  588.     }
  589.     /**
  590.      * @Route("/all")
  591.      */
  592.     public function viewAllClaimsFragment()
  593.     {
  594.         $em = $this->getDoctrine()->getManager();
  596.         $CarrierCheck = $this->container->get("app.carrier_check");
  597.         $isCarrier = $CarrierCheck->environmentIsCarrier();
  599.         $tokenStorage = $this->container->get("security.token_storage");
  600.         $User = $tokenStorage->getToken()->getUser();
  602.         if(!$User instanceof Resource) {
  603.             throw $this->createAccessDeniedException("You don't have access to this page.");
  604.         }
  606.         if(in_array(UCFirstAll::ROLE_REGISTERED, $User->getRoles())){
  607.             throw $this->createAccessDeniedException("You don't have access to this page.");
  608.         }
  610.         $isTlg = $User->getIsTlg();
  611.         $isTeamLeader = false;
  612.         $isAdjuster = false;
  613.         $isBranchAdmin = false;
  614.         $isBranchUser = false;
  615.         if ($this->isGranted("ROLE_ADJUSTER") === true && $this->isGranted("ROLE_TEAM_LEADER") === false) {
  616.             $isAdjuster = true;
  617.         } else if ($this->isGranted("ROLE_TEAM_LEADER") === true && ($this->isGranted("ROLE_ADMIN") === false && $this->isGranted("ROLE_EXAMINER") === false)) {
  618.             $isTeamLeader = true;
  619.         } else if ($this->isGranted("ROLE_BRANCH_ADMIN") === true && $this->isGranted("ROLE_ADJUSTER") === false) {
  620.             $isBranchAdmin = true;
  621.         } else if ($this->isGranted("ROLE_BRANCH_USER") === true && $this->isGranted("ROLE_BRANCH_ADMIN") === false) {
  622.             $isBranchUser = true;
  623.         }
  625.         $recordsCount = 0;
  626.         $dataParams = array();
  627.         $sql = "SELECT COUNT(*) as count2
  628.             FROM claim c 
  629.             LEFT JOIN customer cust on c.customer_id = cust.id
  630.             LEFT JOIN state s on c.loss_state_id = s.id
  631.             LEFT JOIN claims_insured ci ON c.id = ci.claim_id
  632.             LEFT JOIN insured i ON ci.insured_id = i.id ";
  634.         if ($isTlg) {
  635.             $sql .= "LEFT JOIN company comp ON cust.sub_company_id = comp.id ";
  636.         }
  638.         if ($isAdjuster) {
  639.             $sql .= "INNER JOIN assignments cr 
  640.                     ON c.id = cr.claim_id 
  641.                     AND IFNULL(cr.next_assignment_id,0) = 0
  642.                     AND cr.is_current_assignment = 1 ";
  643.         } else if ($isTeamLeader) {
  644.             $sql .= "INNER JOIN claim_team_resource ctr 
  645.                     ON c.claim_team_id = ctr.claim_team_id ";
  646.         } else if ($isBranchAdmin) {
  647.             $sql .= "INNER JOIN customer_resource cust_res 
  648.                     ON c.customer_id = cust_res.customer_id ";
  649.         }
  651.         $sql .= " WHERE (c.status_id BETWEEN 6 AND 29 OR c.status_id = 98) ";
  653.         if ($isTlg) {
  654.             $sql .= " AND comp.id = 19 ";
  655.         }
  657.         if ($isAdjuster) {
  658.             $sql .= "AND cr.resource_id = :adjuster_id ";
  659.             $dataParams['adjuster_id'] = $User->getId();
  660.         } else if ($isTeamLeader) {
  661.             $sql .= "AND ctr.resource_id = :team_leader_id AND ctr.team_lead = 1 ";
  662.             $dataParams['team_leader_id'] = $User->getId();
  663.         } else if ($isBranchAdmin) {
  664.             $sql .= "AND cust_res.resource_id = :branch_admin_id ";
  665.             $dataParams['branch_admin_id'] = $User->getId();
  666.         } else if ($isBranchUser) {
  667.             $sql .= "AND c.claim_rep_id = :branch_user_id ";
  668.             $dataParams['branch_user_id'] = $User->getId();
  669.         }
  671.         $stmt = $em->getConnection()->prepare($sql);
  672.         $stmt->execute($dataParams);
  673.         $result = $stmt->fetch(\PDO::FETCH_ASSOC);
  674.         $recordsCount = $result['count2'] ?? 0;
  676.         $sqlStatus = "SELECT id, status_name, hex_color
  677.                   FROM claim_status 
  678.                   WHERE show_on_report='Y' 
  679.                   ORDER BY order_by";
  681.         $stmtSql = $em->getConnection()->prepare($sqlStatus);
  682.         $stmtSql->execute();
  684.         $statusShow = $stmtSql->fetchAll(\PDO::FETCH_ASSOC);
  686.         return $this->render('ClaimManagementBundle:ClaimIntakeManagement/Fragments:all-claims.html.twig', [
  687.             'newClaimCount' => $recordsCount,
  688.             'statusShow' => $statusShow
  689.         ]);
  690.     }
  692.     /**
  693.      * @Route("/unassigned_data")
  694.      */
  695.     public function unassignedDatatables(Request $request)
  696.     {
  697.         $em = $this->getDoctrine()->getManager();
  699.         //$resourceId = intval($request->query->get("resourceId"));
  701.         $CarrierCheck = $this->container->get("app.carrier_check");
  702.         $isCarrier = $CarrierCheck->environmentIsCarrier();
  704.         $tokenStorage = $this->container->get("security.token_storage");
  705.         $User = $tokenStorage->getToken()->getUser();
  707.         # Array of columns that will be sorted
  708.         if($isCarrier) {
  709.             $columnsArr = [
  710.                 "id",
  711.                 "id",
  712.                 "claim_number",
  713.                 "insured_member",
  714.                 "catastrophe_code",
  715.                 "assistive_resource",
  716.                 "loss_city",
  717.                 "state",
  718.                 "loss_zip",
  719.                 "loss_type",
  720.                 "loss_date",
  721.                 "date_received"
  722.             ];
  723.         }else{
  724.             $columnsArr = [
  725.                 "id",
  726.                 "id",
  727.                 "claim_number",
  728.                 "file_number",
  729.                 "company_name",
  730.                 "insured_member",
  731.                 "catastrophe_code",
  732.                 "assistive_resource",
  733.                 "loss_city",
  734.                 "state",
  735.                 "loss_zip",
  736.                 "loss_type",
  737.                 "loss_date",
  738.                 "date_received"
  739.             ];
  740.         }
  742.         $isTeamLeader = false;
  743.         $isAdjuster = false;
  744.         $isBranchAdmin = false;
  745.         $isBranchUser = false;
  746.         if ($this->isGranted("ROLE_ADJUSTER") === true && $this->isGranted("ROLE_TEAM_LEADER") === false) {
  747.             $isAdjuster = true;
  748.         } else if ($this->isGranted("ROLE_TEAM_LEADER") === true && ($this->isGranted("ROLE_ADMIN") === false && $this->isGranted("ROLE_EXAMINER") === false)) {
  749.             $isTeamLeader = true;
  750.         } else if ($this->isGranted("ROLE_BRANCH_ADMIN") === true && $this->isGranted("ROLE_ADJUSTER") === false) {
  751.             $isBranchAdmin = true;
  752.         } else if ($this->isGranted("ROLE_BRANCH_USER") === true && $this->isGranted("ROLE_BRANCH_ADMIN") === false) {
  753.             $isBranchUser = true;
  754.         }
  756.         # Build Query and PDO Parameters that will be passed to PaginatorService class
  757.         $dataParams = array();
  758.         $sql = "SELECT c.id, 
  759.                 c.id as file_number, 
  760.                 c.claim_number as claim_number, 
  761.                   c.catastrophe_code as catastrophe_code,
  762.                 CONCAT(i.first_name, ' ', i.last_name) as insured_member, 
  763.                 CONCAT(ar.first_name,' ',ar.last_name) as assistive_resource,
  764.                 i.address as address, 
  765.                 cust.company_name as company_name,
  766.                 c.date_received as date_received, 
  767.                 c.loss_city as loss_city, 
  768.                 s.stateName as state, 
  769.                 c.loss_zip, DATE_FORMAT(c.loss_date, '%m/%d/%Y') as loss_date, 
  770.                -- c.assignment_notes as notes, 
  771.                 -- c.abridged_aa_notes as notes, 
  772.                 c.status_id as status_id, 
  773.                 c.date_received, 
  774.                 c.loss_description,
  775.                 c.is_claim_category,
  776.                 p1.description as loss_type
  777.                 FROM claim c "
  778.                 . (($isCarrier) ? "LEFT" : "INNER") . " JOIN customer cust on c.customer_id = cust.id
  779.                 LEFT JOIN state s
  780.                 on c.loss_state_id = s.id
  781.                 LEFT JOIN perils p1
  782.                   ON c.peril_id = p1.id
  783.                 LEFT JOIN claims_insured ci
  784.                   ON c.id = ci.claim_id AND ci.is_primary = 1
  785.                 LEFT JOIN insured i
  786.                   ON ci.insured_id = i.id " .
  787.             (($isAdjuster) ? "INNER" : "LEFT") . " JOIN assignments cr 
  788.                     ON c.id = cr.claim_id AND IFNULL(cr.next_assignment_id,0) = 0 AND cr.is_current_assignment = 1
  789.                 LEFT JOIN resource r 
  790.                     ON cr.resource_id = r.id " .
  791.             (($isBranchAdmin) ? "INNER" : "LEFT") . " JOIN customer_resource cust_res 
  792.                     ON c.customer_id = cust_res.customer_id ";
  793.         if ($isTeamLeader) {
  794.             $sql .= "INNER JOIN claim_team_resource ctr 
  795.                         ON c.claim_team_id = ctr.claim_team_id ";
  796.         }
  798.         $tokenStorage = $this->container->get("security.token_storage");
  799.         $User = $tokenStorage->getToken()->getUser();
  800.         $isTlg = $User instanceof Resource && $User->getIsTlg();
  804.         // If the user is a TLG user, we'll return an empty view or a message
  805.         if ($isTlg) {
  806.             $sql .= "LEFT JOIN company comp ON cust.sub_company_id = comp.id ";
  807.         }
  809.         /*
  810.          * 7/26/2019 Added assistive adjuster update
  811.          *
  812.          */
  813.         $sql .= "LEFT JOIN resource ar ON ar.id = c.assistive_resource_id ";
  815.         $sql .= "WHERE 1 = 1 ";
  816.         if ($isTlg) {
  817.             $sql .= " AND comp.id = 19 ";
  818.         }
  819.         // $sql .= "WHERE 1 = 1";
  821.         // if ($resourceId > 0) {
  822.         $sql .= " AND c.status_id < 10 ";
  823.         // }
  825.         if ($isAdjuster) {
  826.             $sql .= "AND cr.resource_id = :adjuster_id ";
  827.             $dataParams['adjuster_id'] = $User->getId();
  828.         } else if ($isTeamLeader) {
  830.             $sql .= "AND ctr.resource_id = :team_leader_id AND ctr.team_lead = 1 ";
  831.             $dataParams['team_leader_id'] = $User->getId();
  832.         } else if ($isBranchAdmin) {
  834.             $sql .= "AND cust_res.resource_id = :branch_admin_id ";
  835.             $dataParams['branch_admin_id'] = $User->getId();
  836.         } else if ($isBranchUser) {
  838.             $sql .= "AND c.claim_rep_id = :branch_user_id ";
  839.             $dataParams['branch_user_id'] = $User->getId();
  840.         }
  842.         # build keyword filter, which is requested by datatables ajax when someone types into keyword field
  843.         $keyword_filter = " AND (c.catastrophe_code LIKE :keyword OR 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) ";
  845.         # specifiy group by
  846.         // $group_by = " GROUP BY c.id, c.claim_number";
  847.         $group_by = "GROUP BY c.id";
  849.         # call PaginatorService
  850.         $paginator = $this->container->get("resource.paginator_service");
  852.         /** @var \PDO $pdo */
  853.         $pdo = $this->getDoctrine()->getManager()->getConnection();
  855.         $retVal2 = $paginator->getPaginationResults($request, $sql, $dataParams, "c.id", $columnsArr, "search_keyword", $keyword_filter, "keyword", $group_by, "GET");
  856.         $retVal = $retVal2;
  857.         $retVal['data'] = array();
  858.         $i = 0;
  859.         foreach ($retVal2['data'] as $getItem) {
  860.             $status = $em->find("ClaimManagementBundle:ClaimStatus", $getItem['status_id']);
  862.             $getItem2 = $getItem;
  864.             $claim= $em->getRepository("ClaimManagementBundle:Claim")->find($getItem['id']);
  866.             if(!$isCarrier){
  867.                 $claim= $em->getRepository("ClaimManagementBundle:Claim")->find($getItem['id']);
  868.                 if(!empty($claim)){
  869.                     $customerId=$claim->getCustomer()->getId();
  870.                     $TpaCustomer = $em->getRepository("CompanyManagementBundle:TpaCustomer")->findBy(['customerId' => $customerId]);
  871.                     //dd($customerId);
  872.                     //dd($TpaCustomer);
  873.                     if(!empty($TpaCustomer)){
  874.                         $getItem2['isTpaCustomer'] ='Y';
  875.                     }else{
  876.                         $getItem2['isTpaCustomer'] ='N';
  877.                     }
  878.                 }
  879.                 $Coverages = $em->getRepository("ClaimManagementBundle:Coverage")->findBy(['claim' => $claim]);
  881.                 $getItem2['notes'] = "<b>Loss Description: </b> " .$getItem['loss_description'];
  883.                 if(!empty($Coverages)){
  884.                     foreach ($Coverages as $Coverage){
  885.                         if($Coverage){
  886.                             $getItem2['notes'] .= " </br><b>".$Coverage->getName().": </b> $". number_format($Coverage->getAmount(),2);
  887.                         }
  888.                     }
  889.                 }
  891.                 //$getItem2['notes'] = str_replace("\n", "<br>", strip_tags(preg_replace("/<[\/]{0,1}br[ \/]{0,2}>/", "\n", $getItem['notes'])));
  892.             }else{
  893.                 $getItem2['notes'] = str_replace("\n", "<br>", strip_tags(preg_replace("/<[\/]{0,1}br[ \/]{0,2}>/", "\n", $getItem['notes'])));
  894.             }
  896.             $getItem2['useTbca'] = 0;
  897.             $getItem2['subClaimHtml'] = '';
  899.             $getItem2['color'] = $status->getHexColor();
  900.             $retVal['data'][] = $getItem2;
  901.             $retVal['query'] = $sql;
  902.             
  903.         }
  904.         return new JsonResponse($retVal);
  905.     }
  907.     /**
  908.      * @Route("/all_data")
  909.      */
  910.     public function allDatatables(Request $request)
  911.     {
  912.         # Array of columns that will be sorted
  913.         $columnsArr = [
  914.             "id",
  915.             "claim_number",
  916.             "company_name",
  917.             "insured_member",
  918.             "address",
  919.             "loss_city",
  920.             "state",
  921.             "loss_zip",
  922.             "loss_date",
  923.             "date_received"
  924.         ];
  925.         $resourceId = intval($request->query->get("resourceId"));
  927.         $CarrierCheck = $this->container->get("app.carrier_check");
  928.         $isCarrier = $CarrierCheck->environmentIsCarrier();
  930.         $tokenStorage = $this->container->get("security.token_storage");
  931.         $User = $tokenStorage->getToken()->getUser();
  933.         // Check if the user has is_tlg = 1
  934.         $isTlg = $User instanceof Resource && $User->getIsTlg();
  936.         $isTeamLeader = false;
  937.         $isAdjuster = false;
  938.         $isBranchAdmin = false;
  939.         $isBranchUser = false;
  940.         if ($this->isGranted("ROLE_ADJUSTER") === true && $this->isGranted("ROLE_TEAM_LEADER") === false) {
  941.             $isAdjuster = true;
  942.         } else if ($this->isGranted("ROLE_TEAM_LEADER") === true && ($this->isGranted("ROLE_ADMIN") === false && $this->isGranted("ROLE_EXAMINER") === false)) {
  943.             $isTeamLeader = true;
  944.         } else if ($this->isGranted("ROLE_BRANCH_ADMIN") === true && $this->isGranted("ROLE_ADJUSTER") === false) {
  945.             $isBranchAdmin = true;
  946.         } else if ($this->isGranted("ROLE_BRANCH_USER") === true && $this->isGranted("ROLE_BRANCH_ADMIN") === false) {
  947.             $isBranchUser = true;
  948.         }
  950.         # Build Query and PDO Parameters that will be passed to PaginatorService class
  951.         $dataParams = array();
  952.         $sql = "SELECT c.status_id as status, c.id, c.catastrophe_code, c.claim_number, CONCAT(i.first_name, ' ', i.last_name) as insured_member, i.address as address,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, 
  953.             p.description as peril, c.special_notes, c.date_received
  954.             FROM claim c 
  955.             LEFT JOIN customer cust on c.customer_id = cust.id
  956.             LEFT JOIN state s on c.loss_state_id = s.id
  957.             LEFT JOIN perils p on c.peril_id = p.id
  958.             LEFT JOIN claims_insured ci ON c.id = ci.claim_id
  959.             LEFT JOIN insured i ON ci.insured_id = i.id "
  960.             . (($isAdjuster) ? "INNER" : "LEFT") . " JOIN assignments cr 
  961.               ON c.id = cr.claim_id AND IFNULL(cr.next_assignment_id,0) = 0 AND cr.is_current_assignment = 1
  962.             LEFT JOIN resource r ON cr.resource_id = r.id ";
  964.         if ($isTlg) {
  965.             $sql .= "LEFT JOIN company comp ON cust.sub_company_id = comp.id ";
  966.         }
  968.         if ($isTeamLeader) {
  969.             $sql .= "INNER JOIN claim_team_resource ctr ON c.claim_team_id = ctr.claim_team_id ";
  970.         } else if ($isBranchAdmin) {
  971.             $sql .= "INNER JOIN customer_resource cust_res ON c.customer_id = cust_res.customer_id ";
  972.         }
  974.         $sql .= " WHERE (c.status_id BETWEEN 6 AND 29 OR c.status_id = 98) ";
  976.         if ($isTlg) {
  977.             $sql .= " AND comp.id = 19 ";
  978.         }
  980.         if ($isAdjuster) {
  981.             $sql .= "AND cr.resource_id = :adjuster_id ";
  982.             $dataParams['adjuster_id'] = $User->getId();
  983.         } else if ($isTeamLeader) {
  984.             $sql .= "AND ctr.resource_id = :team_leader_id AND ctr.team_lead = 1 ";
  985.             $dataParams['team_leader_id'] = $User->getId();
  986.         } else if ($isBranchAdmin) {
  987.             $sql .= "AND cust_res.resource_id = :branch_admin_id ";
  988.             $dataParams['branch_admin_id'] = $User->getId();
  989.         } else if ($isBranchUser) {
  990.             $sql .= "AND c.claim_rep_id = :branch_user_id ";
  991.             $dataParams['branch_user_id'] = $User->getId();
  992.         }
  994.         $searchedStatusId = $request->query->get('search_status');
  995.         if (!empty($searchedStatusId)) {
  996.             $sql .= " AND (c.status_id in($searchedStatusId)) ";
  997.         }
  999.         $slog = new SLogger();
  1000.         $slog->log("sql at 79: " . $sql);
  1002.         # build keyword filter, which is requested by datatables ajax when someone types into keyword field
  1003.         $keyword_filter = " AND (c.catastrophe_code like :keyword OR 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) ";
  1005.         # specify group by
  1006.         $group_by = "GROUP BY c.id";
  1008.         # call PaginatorService
  1009.         $paginator = $this->container->get("resource.paginator_service");
  1010.         $retVal = $paginator->getPaginationResults($request, $sql, $dataParams, "c.date_received", $columnsArr, "search_keyword", $keyword_filter, "keyword", $group_by, "GET");
  1012.         $retVal['data'] = $this->manipulateClaimData($retVal);
  1014.         return new JsonResponse($retVal);
  1015.     }
  1017.     /**
  1018.      * @Route("/index")
  1019.      */
  1020.     public function viewMyClaims()
  1021.     {
  1023.         # FYI, if they have a role to view all claims, we need to do it here.
  1024.         $em = $this->get('doctrine')->getManager();
  1025.         $authChecker = $this->container->get('security.authorization_checker');
  1027.         $user  = $this->container->get('security.token_storage')->getToken()->getUser();
  1028.         $role  = $user->getRoles()[0];
  1029.       
  1030.         /** @var Resource $user */
  1031.         $this->user = $this->get('security.token_storage')->getToken()->getUser();
  1033.         //Getting the settings of the User in an object
  1034.         $getDashBoardSettingsOfUser = $this->getSettingsUserForDash();
  1035.         if($getDashBoardSettingsOfUser) {
  1036.             $dbLocalStorageSettings = json_decode($getDashBoardSettingsOfUser[0]['dashboard_settings']);
  1037.             $dbLocalStoragePosition = json_decode($getDashBoardSettingsOfUser[0]['dashboard_position']);
  1038.         }
  1039.         /*
  1040.          * WHEN THERE IS NO SETTINGS FOR THE USER WE PICK THE DEFAULT DASHBOARD SETTINGS
  1041.          */
  1042.         else{
  1043.             $dbLocalStorageSettings = "{\"widget\":[]}";
  1044.             $dbLocalStoragePosition = "{\"grid\":[]}";
  1045.         }
  1047.         /*
  1048.          * Don't show widget for the following
  1049.          * Security Roles
  1050.          */
  1051.         $notShow = false;
  1052.         if($role == "ROLE_BRANCH_USER" || $role == "ROLE_BRANCH_ADMIN"){
  1053.             $notShow = true;
  1054.         }
  1055.         $teamCap = 0;
  1056.         /*
  1057.          *  Check if there are saved settings
  1058.          *  If is ICM we load their settings 1 - 20000
  1059.          */
  1060.         if ($dbLocalStorageSettings == "{\"widget\":[]}") {
  1061.             $dbLocalStorageSettings = '{"widget":[{"id":"wid-id-sitenotif","title":"Sitewide Notifications","hidden":"'
  1062.                     . (($authChecker->isGranted("ROLE_BRANCH_USER") === true) ? 0 : 1) . '","collapsed":0, '
  1063.                     . (($authChecker->isGranted("ROLE_BRANCH_USER") === true) ? "\"menu\": true" : "\"menu\": false") . '},                   
  1064.                 {"id":"wid-id-all-assigned","title":"All Assigned Claims","hidden":"'
  1065.                 . (($authChecker->isGranted("ROLE_BRANCH_USER") === true && $role == "ROLE_ADJUSTER") ? 0 : 1) . '","collapsed":0, '
  1066.                 . (($authChecker->isGranted("ROLE_BRANCH_USER") === true && ($role != "ROLE_ADJUSTER" && !$notShow)) ? "\"menu\": true" : "\"menu\": false") . '},                             
  1067.                 {"id":"wid-id-all-claims-widget","title":"All Claims Widget","hidden":"'
  1068.                 . (($authChecker->isGranted("ROLE_BRANCH_USER") === true && $role != "ROLE_ADJUSTER") ? 0 : 1) . '","collapsed":0, '
  1069.                 . (($authChecker->isGranted("ROLE_BRANCH_USER") === true && $role == "ROLE_ADJUSTER") ? "\"menu\": true" : "\"menu\": false") . '},
  1070.                 {"id":"wid-id-ica-ncc-claims-widget","title":"ICA/NCC Claims Widget","hidden":"'
  1071.                 . (($authChecker->isGranted("ROLE_BRANCH_USER") === true && $role != "ROLE_ADJUSTER") ? 0 : 1) . '","collapsed":0, '
  1072.                 . (($authChecker->isGranted("ROLE_BRANCH_USER") === true && $role == "ROLE_ADJUSTER") ? "\"menu\": true" : "\"menu\": false") . '},
  1073.                 {"id":"wid-id-resource-background-check-widget","title":"Resource Background Check Widget","hidden":"'
  1074.                 . (($authChecker->isGranted("ROLE_BRANCH_USER") === true && $role != "ROLE_ADJUSTER") ? 0 : 1) . '","collapsed":0, '
  1075.                 . (($authChecker->isGranted("ROLE_BRANCH_USER") === true && $role == "ROLE_ADJUSTER") ? "\"menu\": true" : "\"menu\": false") . '},
  1076.                 {"id":"wid-id-proctor-claims-widget","title":"Proctor Claims Widget","hidden":"'
  1077.                 . (($authChecker->isGranted("ROLE_BRANCH_USER") === true && $role != "ROLE_ADJUSTER") ? 0 : 1) . '","collapsed":0, '
  1078.                 . (($authChecker->isGranted("ROLE_BRANCH_USER") === true && $role == "ROLE_ADJUSTER") ? "\"menu\": true" : "\"menu\": false") . '},
  1079.                 {"id":"wid-id-bookmarked-claims-widget","title":"Bookmarked Claims Widget","hidden":"'
  1080.                 . (($authChecker->isGranted("ROLE_BRANCH_USER") === true) ? 0 : 1) . '","collapsed":0, '
  1081.                 . (($authChecker->isGranted("ROLE_BRANCH_USER") === true) ? "\"menu\": true" : "\"menu\": false") . '},
  1082.                 {"id":"wid-id-claims","title":"Claims Diary","hidden":"'
  1083.                     . (($authChecker->isGranted("ROLE_BRANCH_USER") === true) ? 0 : 1) . '","collapsed":0, '
  1084.                     . (($authChecker->isGranted("ROLE_BRANCH_USER") === true) ? "\"menu\": true" : "\"menu\": false") . '},
  1085.                 {"id":"wid-id-status","title":"Status of Claims","hidden":"'
  1086.                     . (($authChecker->isGranted("ROLE_BRANCH_USER") === true) ? 0 : 1) . '","collapsed":0, '
  1087.                     . (($authChecker->isGranted("ROLE_BRANCH_USER") === true) ? "\"menu\": true" : "\"menu\": false") . '},               
  1088.                 {"id":"wid-id-licenses-maintenance","title":"Licenses Maintenance  |  Expiring in less than 60 days  ","hidden":"'
  1089.                     . (($authChecker->isGranted("ROLE_BRANCH_USER") === true) ? 0 : 1) . '","collapsed":0, '
  1090.                     . (($authChecker->isGranted("ROLE_BRANCH_USER") === true) ? "\"menu\": true" : "\"menu\": false") . '},
  1091.                 {"id":"wid-id-forecast","title":"Forecast","hidden":1,"collapsed":0,"menu":false},
  1092.                 {"id":"wid-id-toploss","title":"Top Perils","hidden":"'
  1093.                     . (($authChecker->isGranted("ROLE_TEAM_LEADER") === true) ? 0 : 1) . '","collapsed":0,'
  1094.                     . (($authChecker->isGranted("ROLE_TEAM_LEADER") === true) ? "\"menu\": true" : "\"menu\": false") . '},  
  1095.                 {"id":"wid-id-openclaimspie","title":"Open Claims","hidden":1,"collapsed":0,"menu":false},                    
  1096.                 {"id":"wid-id-available-resources","title":"Resources","hidden":"'
  1097.                     . (($authChecker->isGranted("ROLE_TEAM_LEADER") === true && $teamCap == 0) ? 0 : 1) . '","collapsed":0, '
  1098.                     . (($authChecker->isGranted("ROLE_TEAM_LEADER") === true && $teamCap == 0) ? "\"menu\": true" : "\"menu\": false") . '},                
  1099.                 {"id":"wid-id-avg-drive-time","title":"Average Drive Time","hidden":"'
  1100.                     . (($authChecker->isGranted("ROLE_TEAM_LEADER") === true) ? 0 : 1) . '","collapsed":0, '
  1101.                     . (($authChecker->isGranted("ROLE_TEAM_LEADER") === true) ? "\"menu\": true" : "\"menu\": false") . '},                    
  1102.                 {"id":"wid-id-time-in-progress","title":"Time In Progress","hidden":"'
  1103.                     . (($authChecker->isGranted("ROLE_TEAM_LEADER") === true) ? 0 : 1) . '","collapsed":0, '
  1104.                     . (($authChecker->isGranted("ROLE_TEAM_LEADER") === true) ? "\"menu\": true" : "\"menu\": false") . '},  
  1105.                 {"id":"wid-id-geomap-us","title":"GeoMap US","hidden":"'
  1106.                     . (($authChecker->isGranted("ROLE_TEAM_LEADER") === true) ? 0 : 1) . '","collapsed":0, '
  1107.                     . (($authChecker->isGranted("ROLE_TEAM_LEADER") === true) ? "\"menu\": true" : "\"menu\": false") . '},
  1108.                 {"id":"wid-id-mytasks","title":"Task created by me","hidden":"'
  1109.                     . (($authChecker->isGranted("ROLE_TEAM_LEADER") === true) ? 0 : 1) . '","collapsed":0, '
  1110.                     . (($authChecker->isGranted("ROLE_TEAM_LEADER") === true) ? "\"menu\": true" : "\"menu\": false") . '},
  1111.                 {"id":"wid-id-tasks","title":"Message Forum","hidden":"'
  1112.                 . (($authChecker->isGranted("ROLE_TEAM_LEADER") === true) ? 0 : 1) . '","collapsed":0, '
  1113.                 . (($authChecker->isGranted("ROLE_TEAM_LEADER") === true) ? "\"menu\": true" : "\"menu\": false") . '},        
  1114.                 {"id":"wid-id-claims-count","title":"Claims Count","hidden":'
  1115.                     . (($authChecker->isGranted("ROLE_ADMIN") === true || $authChecker->isGranted("ROLE_EXAMINER") === true) ? 0 : 1) .',"collapsed":0,"menu": '
  1116.                     . (($authChecker->isGranted("ROLE_ADMIN") === true || $authChecker->isGranted("ROLE_EXAMINER") === true) ? 1 : 0) .'},
  1117.                 {"id":"wid-id-claims-status-time-spent-avg","title":"Claims Status Time Spent Avg","hidden":'
  1118.                     . (($authChecker->isGranted("ROLE_ADMIN") === true || $authChecker->isGranted("ROLE_EXAMINER") === true) ? 0 : 1) .',"collapsed":0,"menu": '
  1119.                     . (($authChecker->isGranted("ROLE_ADMIN") === true || $authChecker->isGranted("ROLE_EXAMINER") === true) ? 1 : 0) .'},
  1120.                 {"id":"wid-id-claims-frontier-appraisal-services","title":"Claims Frontier Appraisal Services Widget","hidden":"'
  1121.                 . (($authChecker->isGranted("ROLE_BRANCH_USER") === true && $role != "ROLE_ADJUSTER") ? 0 : 1) . '","collapsed":0, '
  1122.                 . (($authChecker->isGranted("ROLE_BRANCH_USER") === true && $role == "ROLE_ADJUSTER") ? "\"menu\": true" : "\"menu\": false") . '}]}';
  1123.         }
  1124.         #Forced password change after 90 Days
  1125.         if(!empty($user->getPasswordChangeDate())) {
  1126.             $currentDate = date('Y-m-d H:i:s');
  1127.             $passwordChangeDate = $user->getPasswordChangeDate()->format('Y-m-d H:i:s');
  1128.             $passwordChangeDay = round((strtotime($currentDate) - strtotime($passwordChangeDate)) / (60 * 60 * 24));
  1129.         }else{
  1130.             $passwordChangeDay=0;
  1131.         }
  1133.         $systemParamService = $this->container->get('core.system_parameter_control');
  1134.         $systemAdminUsers = $systemParamService->getAccessArray('Invoicing_access');
  1136.         $bShowInvoiceAccessBox = false;
  1137.         $user = $this->get('security.token_storage')->getToken()->getUser();
  1138.         if (!empty($systemAdminUsers) && in_array($user->getId(),$systemAdminUsers)) {
  1139.             $bShowInvoiceAccessBox = true;
  1140.         }
  1142.         $hoverReportsBox = false;
  1143.         $hoverReportApprovers = $systemParamService->getAccessArray('hover_report_approvers');
  1144.         $hoverReportUsers = $systemParamService->getAccessArray('hover_report_order');
  1145.         if ((!empty($hoverReportApprovers) && in_array($user->getId(), $hoverReportApprovers)) ||
  1146.             (!empty($hoverReportUsers) && in_array($user->getId(), $hoverReportUsers))) {
  1147.             $hoverReportsBox = true;
  1148.         }
  1149.         
  1150.         return $this->render('ClaimManagementBundle:ClaimIntakeManagement/Fragments:my-claims.html.twig',[
  1151.             'adjuster' => $this->user->getId(),
  1152.             'local_storage_settings' => json_encode($dbLocalStorageSettings),
  1153.             'local_storage_position' => json_encode($dbLocalStoragePosition),
  1154.             'local_storage_sett_obj' => $dbLocalStorageSettings,
  1155.             'passwordChangeDay' => $passwordChangeDay,
  1156.             'bShowInvoiceAccessBox' => $bShowInvoiceAccessBox,
  1157.             'hoverReportsBox' => $hoverReportsBox
  1158.         ]);
  1159.     }
  1161.     /**
  1162.      * @Route("/claims/datatable")
  1163.      */
  1164.     public function myClaimsDatatable(Request $request)
  1165.     {
  1166.         # Array of columns that will be sorted
  1167.         $columnsArr = [
  1168.             "id",
  1169.             "claim_number",
  1170.             "date_received",
  1171.             "insured",
  1172.             "adjuster",
  1173.             "state",
  1174.             "loss_zip",
  1175.             "loss_date",
  1176.             "customer",
  1177.             "status",
  1178.             "color",
  1179.             "notes"
  1180.         ];
  1181.         //$resourceId = $this->get('security.token_storage')->getToken()->getUser()->getId();
  1182.         $authChecker = $this->container->get('security.authorization_checker');
  1183.         $user  = $this->container->get('security.token_storage')->getToken()->getUser();
  1184.         $resourceId = $user->getId();
  1186.         $status = $request->query->get("statusId");
  1187.         $dropdownValue = intval($request->query->get("dropdownValue"));
  1189.         $CarrierCheck = $this->container->get("app.carrier_check");
  1190.         $isCarrier = $CarrierCheck->environmentIsCarrier();
  1192.         # Build Query and PDO Parameters that will be passed to PaginatorService class
  1193.         $dataParams = array();
  1195.         if ($authChecker->isGranted("ROLE_TEAM_LEADER") === true) {
  1196.             //echo "team Leader";
  1197.             $sql = "SELECT c.id, c.status_id as status, c.claim_number, cust.company_name as customer,
  1198.                       c.date_received, c.loss_city, s.stateName as state, c.loss_zip,
  1199.                       c.loss_date, CONCAT(i.first_name, ' ', i.last_name) as insured,
  1200.                       CONCAT(r.first_name, ' ', r.last_name) as adjuster, '' as color, special_notes as notes
  1201.                     FROM claim_team_resource cto
  1202.                       INNER JOIN claim_team_resource cto2 
  1203.                         ON cto.team_lead = 1 AND cto.claim_team_id = cto2.claim_team_id 
  1204.                       INNER JOIN assignments cr 
  1205.                         ON cto2.resource_id = cr.resource_id AND IFNULL(next_assignment_id,0) = 0
  1206.                       AND cr.is_current_assignment = 1
  1207.                       INNER JOIN claim c
  1208.                         ON cr.claim_id = c.id  
  1209.                       INNER JOIN customer cust on c.customer_id = cust.id
  1210.                       INNER JOIN state s on c.loss_state_id = s.id
  1211.                       LEFT JOIN resource r on cto2.resource_id = r.id
  1212.                       LEFT JOIN claims_insured ci on ci.claim_id = c.id
  1213.                       LEFT JOIN insured i ON i.id = ci.insured_id
  1214.                     WHERE c.status_id IN (6,10,12,13,14,15,20,23,25,100,101,103) AND cr.resource_id = :resourceId";
  1215.             $dataParams = array('resourceId' => $resourceId);
  1217.         }else{
  1218.             //echo "rest of them";
  1219.             $sql = "SELECT c.id, c.status_id as status, c.claim_number, cust.company_name as customer,
  1220.                     c.date_received, c.loss_city, s.stateName as state, c.loss_zip,
  1221.                     c.loss_date, CONCAT(i.first_name, ' ', i.last_name) as insured,
  1222.                     CONCAT(r.first_name, ' ', r.last_name) as adjuster, '' as color, special_notes as notes
  1223.                     FROM claim c "
  1224.                     . (($isCarrier) ? "LEFT" : "INNER") . " JOIN customer cust on c.customer_id = cust.id
  1225.                     INNER JOIN state s on c.loss_state_id = s.id
  1226.                     LEFT JOIN assignments cr on cr.claim_id = c.id
  1227.                     AND IFNULL(next_assignment_id,0) = 0 AND cr.is_current_assignment = 1
  1228.                     LEFT JOIN resource r on cr.resource_id = r.id
  1229.                     LEFT JOIN claims_insured ci on ci.claim_id = c.id 
  1230.                     LEFT JOIN insured i ON i.id = ci.insured_id 
  1231.                     WHERE " . (($isCarrier) ? "1 = 1" : "c.status_id IN (6,10,12,13,14,15,20,23,25,100,101,103)") . " 
  1232.                     AND cr.resource_id = :resourceId";
  1233.             $dataParams = array('resourceId' => $resourceId);
  1234.             }
  1236.         if ($status) {
  1237.             # Denied claims
  1238.             if ($status == -1) {
  1239.                 $sql .= " AND settlement_id in (4,5,6)";
  1240.             } else {
  1241.                 # all other claims
  1242.                 $sql .= " AND c.status_id != 30";
  1243.                 $sql .= " AND c.status_id = :status";
  1244.                 $dataParams["status"] =  $status;
  1245.             }
  1246.             # if status, add to array
  1248.         } else {
  1250.         }
  1252.         # handle dropdown being passed in from pie dropdown
  1253. //        switch ($dropdownValue) {
  1254. //            default :
  1255. //                $sql .= " AND c.date_received BETWEEN DATE_SUB(curdate(), INTERVAL 7 DAY) AND curdate()";
  1256. //                break;
  1257. //            case 2 :
  1258. //                $sql .= " AND c.date_received BETWEEN DATE_SUB(curdate(), INTERVAL 30 DAY) AND curdate()";
  1259. //                break;
  1260. //        }
  1262.         //echo $sql;
  1263. //                echo "<pre>". $sql . "</pre>";
  1265.         # build keyword filter, which is requested by datatables ajax when someone types into keyword field
  1266.         $keyword_filter = "AND (c.claim_number like :keyword) ";
  1268.         # specifiy group by
  1269.         $group_by = "GROUP BY c.id, c.claim_number";
  1271.         //return new JsonResponse(["sql" => $sql, "data" => $dataParams]); die;
  1273.         # call PaginatorService
  1274.         $paginator = $this->container->get("resource.paginator_service");
  1275.         $data = $paginator->getPaginationResults($request, $sql, $dataParams, "", $columnsArr, "", $keyword_filter, "keyword", $group_by, "GET");
  1277.         # manipulate data before sending to front end
  1278.         $data['data'] = $this->manipulateClaimData($data);
  1280.         return new JsonResponse($data);
  1281.     }
  1283.     public function manipulateClaimData($data)
  1284.     {
  1285.         $em = $this->getDoctrine()->getManager();
  1286.         $data = $data['data'];
  1288.         $result = [];
  1289.         foreach ($data as $index => $row) {
  1290.             $status = $em->find("ClaimManagementBundle:ClaimStatus", $row['status']);
  1292.             $row['color'] =  $status->getHexColor();
  1294.             // $row['color'] = Claim::getStatusColor($row['status']);
  1295.             $row['date_received'] = date("m/d/Y", strtotime($row['date_received']));
  1296.             $row['loss_date'] = date("m/d/Y", strtotime($row['loss_date']));
  1298.             # store into another array
  1299.             $result[$index] = $row;
  1300.         }
  1301.         return $result;
  1302.     }
  1304.     public function getSettingsUserForDash(){
  1306.         //$user = $this->get('security.token_storage')->getToken()->getUsername();
  1307.         $em = $this->getDoctrine()->getManager();
  1308.         $user_id = $this->user->getId();
  1310.         $sql = "SELECT dashboard_settings, dashboard_position FROM resource_dashboard WHERE resource_id = ?";
  1312.         //echo $sql;
  1313.         $stmt = $em->getConnection()->prepare($sql);
  1314.         $stmt->execute([$user_id]);
  1316.         $results = $stmt->fetchAll();
  1317.         //print_r($results);
  1319.         return $results;
  1320.     }
  1322.     /**
  1323.      * @Route("/claims/save_settings")
  1324.      */
  1325.     public function saveSettingsUserForDash(Request $request){
  1327.         $em = $this->getDoctrine()->getManager();
  1329.         $settings_to_save  = $request->request->get("currentLocalStorageSettings");
  1330.         $position_to_save  = $request->request->get("currentLocalStoragePosition");
  1331.         $user = $this->get('security.token_storage')->getToken()->getUser();
  1332.         $resource_dashboard = $em->getRepository(ResourceDashboard::class)->findOneBy(array("resource"=>$user));
  1334.         //If there is no existence of the user in the dashboard_settings table it CREATES
  1335.         if(!$resource_dashboard){
  1336.             $resource_dashboard = new ResourceDashboard();
  1337.             $resource_dashboard->setResource($user);
  1338.         }
  1339.         if($settings_to_save && $settings_to_save != "null") {
  1340.             $resource_dashboard->setDashboard($settings_to_save);
  1341.         }
  1342.         if($position_to_save && $position_to_save != "null") {
  1343.             $resource_dashboard->setDashboardPos($position_to_save);
  1344.         }
  1345. //        if (!$username) {
  1346. //            throw $this->createNotFoundException(
  1347. //                'No product found for id '.$username
  1348. //            );
  1349. //        }
  1351.         $em->persist($resource_dashboard);
  1352.         $em->flush();
  1354.         return new JsonResponse(array('success' => true));
  1355.     }
  1357.     /**
  1358.      * @Route("/diary_reporting")
  1359.      */
  1360.     public function getDiaryReportingTool()
  1361.     {
  1362.         # Entity Manager
  1363.         $em = $this->getDoctrine()->getManager();
  1364.         $authChecker = $this->container->get('security.authorization_checker');
  1365.         $logged_in_user  = $this->container->get('security.token_storage')->getToken()->getUser();
  1366.         $logged_in_user_id = $logged_in_user->getId();
  1368.         //TEAM LEAD SECTION
  1369.         $resources = array();
  1370.         if ($authChecker->isGranted("ROLE_EXAMINER") || $authChecker->isGranted("ROLE_ADMIN") || $authChecker->isGranted("ROLE_SUPER_ADMIN")){
  1371.             //fetch all the ACTIVE resources
  1373.             $res_sql =    "SELECT DISTINCT r.id, CONCAT(r.first_name,' ',r.last_name) as name
  1374.                            FROM resource r
  1375.                            INNER JOIN diary_entry de 
  1376.                            ON de.resource_id = r.id
  1377.                            WHERE 1 =1 
  1378.                            AND r.is_active = :is_active
  1379.                            UNION 
  1380.                            SELECT DISTINCT r.id , CONCAT(r.first_name,' ',r.last_name) as name 
  1381.                            FROM resource r 
  1382.                            WHERE r.id = :logged_in_user_id
  1383.                            ORDER BY name" ;
  1385.             $res_stmt = $em->getConnection()->prepare($res_sql);
  1386.             $res_stmt->execute(["is_active" => 1,"logged_in_user_id" => $logged_in_user_id]);
  1387.             $resources = $res_stmt->fetchAll();
  1389.         }else if ($authChecker->isGranted("ROLE_TEAM_LEADER")) {
  1390.             foreach ($logged_in_user->getClaimTeams() as $claimTeamResource){
  1391.                 if($claimTeamResource->getIsTeamLead()){
  1392.                     //fetch all the resources for the team where resource id team lead
  1393.                     $res_sql =    "SELECT DISTINCT r.id, concat(r.first_name,' ',r.last_name) as name 
  1394.                                     FROM resource r 
  1395.                                     INNER JOIN claim_team_resource ctr
  1396.                                     ON r.id = ctr.resource_id
  1397.                                     INNER JOIN diary_entry de 
  1398.                                     ON de.resource_id = r.id
  1399.                                     INNER JOIN claim c 
  1400.                                     ON c.id = de.claim_id
  1401.                                     WHERE 1 = 1 
  1402.                                     AND r.is_active = :is_active
  1403.                                     AND ctr.claim_team_id = :team_id
  1404.                                     AND c.claim_team_id = :team_id
  1405.                                     UNION
  1406.                                     SELECT DISTINCT r.id , CONCAT(r.first_name,' ',r.last_name) as name 
  1407.                                     FROM resource r
  1408.                                     WHERE r.id = :logged_in_user_id
  1409.                                     ORDER BY name" ;
  1411.                     $res_stmt = $em->getConnection()->prepare($res_sql);
  1412.                     $res_stmt->execute(["is_active" => 1,
  1413.                         "team_id" => $claimTeamResource->getClaimTeam()->getId(),
  1414.                         "logged_in_user_id" => $logged_in_user_id]);
  1415.                     $resources = $res_stmt->fetchAll();
  1418.                 }
  1419.             }
  1420.         } else if ($authChecker->isGranted("ROLE_ADJUSTER")){
  1421.             //fetch all the ACTIVE resources
  1422.             $res_sql = "SELECT DISTINCT r.id , CONCAT(r.first_name,' ',r.last_name) as name 
  1423.                         FROM resource r
  1424.                         -- INNER JOIN diary_entry de 
  1425.                         -- ON de.resource_id = r.id
  1426.                         WHERE r.id = :logged_in_user_id
  1427.                         ORDER BY name" ;
  1429.             $res_stmt = $em->getConnection()->prepare($res_sql);
  1430.             $res_stmt->execute(["logged_in_user_id" => $logged_in_user_id]);
  1431.             $resources = $res_stmt->fetchAll();
  1432.         }
  1434.         return $this->render('ClaimManagementBundle:ClaimIntakeManagement/Fragments:diary-objects.html.twig',[
  1435.             "resources" => $resources
  1436.         ]);
  1438.     }
  1441.     /**
  1442.      * @Route("/diary_reporting_datatable")
  1443.      */
  1444.     public function getDiaryReportingDatatable(Request $request)
  1445.     {
  1446.         # Entity Manager
  1447.         $em = $this->getDoctrine()->getManager();
  1448.         $authChecker = $this->container->get('security.authorization_checker');
  1449.         $logged_in_user  = $this->container->get('security.token_storage')->getToken()->getUser();
  1450.         $logged_in_user_id = $logged_in_user->getId();
  1452.         $dataParams = array();
  1453.         $dataParams["resource_id"] = $request->query->get("resourceId");
  1456.             $sql = "SELECT de.*, 
  1457.                 c.claim_team_id AS claim_team_id,
  1458.                 c.id AS claim_id,
  1459.                 c.claim_number AS claim_num,
  1460.                 c.date_received as claim_received_date,
  1461.                 CONCAT(ins.first_name, ' ' , ins.last_name) as claim_insured,
  1462.                 st.stateCode AS claim_state,
  1463.                 c.loss_zip AS claim_zip,
  1464.                 CONCAT(adj.first_name, ' ' , adj.last_name) as adjuster,
  1465.                 cust.company_name AS customer,
  1466.                 p.description AS loss_type,
  1467.                 CONCAT(res.first_name, ' ' , res.last_name) as owner,
  1468.                 CONCAT(author.first_name, ' ' , author.last_name) as author
  1469.                 FROM diary_entry de
  1470.                 INNER JOIN claim c 
  1471.                 ON c.id = de.claim_id
  1472.                 LEFT JOIN claims_insured ci 
  1473.                 ON ci.claim_id = c.id AND ci.is_primary = 1
  1474.                 LEFT JOIN insured ins 
  1475.                 ON ins.id = ci.insured_id    
  1476.                 LEFT JOIN resource res
  1477.                 ON res.id = de.resource_id
  1478.                 LEFT JOIN resource author
  1479.                 ON author.id = de.author_id
  1480.                 LEFT JOIN state st 
  1481.                 ON st.id = c.loss_state_id
  1482.                 LEFT JOIN assignments cr ON cr.claim_id = c.id AND cr.is_primary_adjuster = 1
  1483.                   AND IFNULL(cr.next_assignment_id,0) = 0 AND cr.is_current_assignment = 1
  1484.                 LEFT JOIN resource adj ON adj.id = cr.resource_id
  1485.                 LEFT JOIN customer cust ON c.customer_id = cust.id
  1486.                 LEFT JOIN perils p ON p.id = c.peril_id
  1487.                 WHERE de.resource_id = :resource_id 
  1488.                 AND de.status = 0 ";
  1490.         if($request->query->get("filter") == 1){
  1491.             $sql .= "AND DATE_FORMAT(de.dueDate,'%Y-%m-%d') = :today";
  1492.             $dataParams["today"] = date('Y-m-d');
  1493.             if ($request->query->get("searchFilter")){
  1494.                 $searchFilter = $request->query->get("searchFilter");
  1495.                 $sql .= " AND c.claim_number like '". $searchFilter . "%'";
  1496.             }
  1497.         }else if($request->query->get("filter") == 2){
  1498.             $sql .= "AND de.dueDate < :today";
  1499.              $dataParams["today"] = date('Y-m-d');
  1500.             if ($request->query->get("searchFilter")){
  1501.                 $searchFilter = $request->query->get("searchFilter");
  1502.                 $sql .= " AND c.claim_number like '". $searchFilter . "%'";
  1503.             }
  1504.         }else if($request->query->get("filter") == 3 ){
  1505. //            echo "POLLO";
  1506.             if($request->query->get("start_date") && $request->query->get("end_date")) {
  1507.                 $sql .= "AND DATE_FORMAT(de.dueDate,'%Y-%m-%d') BETWEEN :start_date AND :end_date";
  1508.                 $dataParams["start_date"] = date('Y-m-d', strtotime($request->query->get("start_date")));
  1509.                 $dataParams["end_date"] = date('Y-m-d', strtotime($request->query->get("end_date")));
  1510. //                echo $dataParams["start_date"];
  1511.             }
  1512.             if ($request->query->get("searchFilter")){
  1513.                 $searchFilter = $request->query->get("searchFilter");
  1514.                 $sql .= " AND c.claim_number like '". $searchFilter . "%'";
  1515.             }
  1516.         }else{
  1517.             if ($request->query->get("searchFilter")){
  1518.                 $searchFilter = $request->query->get("searchFilter");
  1519.                 $sql .= " AND c.claim_number like '". $searchFilter . "%'";
  1520.             }
  1521.         }
  1523.         //echo $sql;
  1525.         $stmt = $em->getConnection()->prepare($sql);
  1526.         $stmt->execute($dataParams);
  1527.         $DiaryEntry = $stmt->fetchAll();
  1529.         $diary_report = array();
  1530.         foreach ($DiaryEntry as $diaryEntryRow){
  1531.             if ($authChecker->isGranted("ROLE_TEAM_LEADER")
  1532.                 && !$authChecker->isGranted("ROLE_ADMIN")
  1533.                 && !$authChecker->isGranted("ROLE_EXAMINER")
  1534.                 && $logged_in_user_id <> $dataParams["resource_id"] ) {
  1536.                 echo $diaryEntryRow['claim_team_id'] . " ";
  1537.                 $isTeamLead = false;
  1539.                 foreach ($logged_in_user->getClaimTeams() as $claimTeamResource) {
  1540.                     if ($claimTeamResource->getIsTeamLead()) {
  1541.                         if($claimTeamResource->getClaimTeam()->getId() == $diaryEntryRow['claim_team_id'])
  1542.                         {
  1543.                             $isTeamLead = true;
  1544.                         }
  1545.                     }
  1546.                 }
  1548.                 if(!$isTeamLead){
  1549.                     continue;
  1550.                 }
  1551.             }
  1554.             $diary_report_row['diary_id'] = $diaryEntryRow['id'];
  1555.             $diary_report_row['claim_id'] = $diaryEntryRow['claim_id'];
  1556.             $diary_report_row['claim_num'] = $diaryEntryRow['claim_num'];
  1557.             $diary_report_row['due_date'] = $diaryEntryRow['dueDate'];
  1558.             $diary_report_row['owner'] = $diaryEntryRow['owner'];
  1559.             $diary_report_row['description'] = $diaryEntryRow['description'];
  1560.             $diary_report_row['create_date'] = $diaryEntryRow['dateCreated'];
  1561.             $diary_report_row['author'] = $diaryEntryRow['author'];
  1562.             $diary_report_row['priority'] = $diaryEntryRow['priority'];
  1563.             $diary_report_row['claim_received_date'] = $diaryEntryRow['claim_received_date'];
  1564.             $diary_report_row['claim_insured'] = $diaryEntryRow['claim_insured'];
  1565.             $diary_report_row['claim_state'] = $diaryEntryRow['claim_state'];
  1566.             $diary_report_row['claim_zip'] = $diaryEntryRow['claim_zip'];
  1567.             $diary_report_row['adjuster'] = $diaryEntryRow['adjuster'];
  1568.             $diary_report_row['customer'] = $diaryEntryRow['customer'];
  1569.             $diary_report_row['loss_type'] = $diaryEntryRow['loss_type'];
  1571.             $diary_report[] = $diary_report_row;
  1573.         }
  1575.         return $this->render('ClaimManagementBundle:ClaimIntakeManagement/Fragments/widgets:diary-objects-widget.html.twig',[
  1576.             "diary_report" =>$diary_report
  1577.         ]);
  1579.     }
  1580.     /**
  1581.      * @Route("/searchfileorclaim")
  1582.      */
  1583.     public function searchfileorclaimAction(Request $request)
  1584.     {
  1585.         $em = $this->getDoctrine()->getManager();
  1586.         $term = $request->request->get("file_claim_id");
  1587.         $response['error'] = false;
  1588.         if (preg_match('/[\'^£$%&*()}{@#~?><>,|=_+¬]/', $term))
  1589.         {
  1590.             // one or more of the 'special characters' found in $string
  1591.             $response['error'] = true;
  1592.             return new JsonResponse($response);
  1593.         }
  1594.         if (preg_match('/^[0-9]+-[0-9]+$/i', $term)) {
  1595.             // contains a dash
  1596.             $claim= $em->getRepository("ClaimManagementBundle:Claim")->findOneBy(['claimNumber' => $term]);
  1597.             if(empty($claim)){
  1598.                 $response['error'] = true;
  1599.             }else{
  1600.                 $response['data']['id']= $claim->getId();
  1601.             }
  1602.         } else {
  1603.             // does not contain dash
  1604.             $claim= $em->getRepository("ClaimManagementBundle:Claim")->findOneBy(['claimNumber' => $term]);
  1605.             if(empty($claim)){
  1606.                 $claim= $em->getRepository("ClaimManagementBundle:Claim")->find($term);
  1607.             }
  1608.         }
  1609.         if(empty($claim)){
  1610.             $response['error'] = true;
  1611.         }else{
  1612.             $response['data']['id']= $claim->getId();
  1613.         }
  1615.         return new JsonResponse($response);
  1616.     }
  1617. }