SHARE
TWEET

Untitled

a guest Jul 16th, 2019 67 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. try{
  2.             $agent = Auth::guard('agent')->user();
  3.             $data = [];
  4.             for($i = 1; $i <= 2; $i++) {
  5.                 if ($i == 1) {
  6.                     $request->region_code = $request->region_code1;
  7.                     $request->branch_code = $request->branch_code1;
  8.                 } else {
  9.                     $request->region_code = $request->region_code2;
  10.                     $request->branch_code = $request->branch_code2;
  11.                 }
  12.  
  13.                 $vehicleColor = Color::select('id','code','name')
  14.                     ->where('agent_code',$agent->code)
  15.                     ->whereNotNull('code');
  16.  
  17.                 $vehicleColor = $vehicleColor->get();
  18.                 $vehicleColorCode = $vehicleColor->pluck('code', 'id');
  19.                 $vehicleColorName = $vehicleColor->pluck('name', 'id');
  20.  
  21.                 $getVehicle = Vehicle::selectRaw('COUNT(vehicles.id) as total, color_id,vehicle_type_id, customer_id')
  22.                     ->where('agent_code',$agent->code)
  23.                     ->groupBy('vehicle_type_id')
  24.                     ->orderBy(\DB::Raw('COUNT(vehicles.id)'), 'DESC')
  25.                     ->limit(10);
  26.  
  27.                 if ($request->region_code || $request->branch_code) {
  28.                     $getBranchCodeIn = RegionBranchLib::regionBranchGet($request);
  29.                     $getVehicle->whereIn('branch_code', $getBranchCodeIn);
  30.                 }
  31.  
  32.                 if(isset($request->user_type) && $request->user_type == 'h2')
  33.                 {
  34.                     $getVehicle->whereIn('user_type',['ASSISTH2','AHASS SYSTEM']);
  35.                 }else{
  36.                     $getVehicle->whereNotIn('user_type',['ASSISTH2','AHASS SYSTEM']);
  37.                 }
  38.  
  39.                 if ($request->start_date)
  40.                     $getVehicle->where('vehicles.created_at','>=',date('Y-m-d 00:00:00',strtotime($request->start_date)));
  41.  
  42.                 if ($request->end_date)
  43.                     $getVehicle->where('vehicles.created_at','<=',date('Y-m-d 23:59:59',strtotime($request->end_date)));
  44.  
  45.                 if($request->vehicle_type_code || $request->vehicle_material_code || $request->vehicle_variant_code || $request->vehicle_segment_code || $request->vehicle_series_code)
  46.                 {
  47.                     $where = [];
  48.  
  49.                     if($request->vehicle_variant_code)
  50.                     {
  51.                         if(is_array($request->vehicle_variant_code))
  52.                         {
  53.                             $where[] = "vehicle_type.commercial_name IN ('" .implode("','",$request->vehicle_variant_code)."') ";
  54.                         }else{
  55.                             $where[] = "vehicle_type.commercial_name = '{$request->vehicle_variant_code}'";
  56.                         }
  57.                     }
  58.  
  59.                     if($request->vehicle_material_code)
  60.                     {
  61.                         if(is_array($request->vehicle_material_code))
  62.                         {
  63.                             $where[] = "vehicle_type.material_group_desc IN ('" .implode("','",$request->vehicle_material_code)."') ";
  64.                         }else{
  65.                             $where[] = "vehicle_type.material_group_desc = '{$request->vehicle_material_code}'";
  66.                         }
  67.                     }
  68.  
  69.                     if($request->vehicle_type_code)
  70.                     {
  71.                         if(is_array($request->vehicle_type_code))
  72.                         {
  73.                             $where[] = "vehicle_type.code IN ('" .implode("','",$request->vehicle_type_code)."') ";
  74.                         }else{
  75.                             $where[] = "vehicle_type.code = '{$request->vehicle_type_code}'";
  76.                         }
  77.                     }
  78.  
  79.                     if($request->vehicle_segment_code)
  80.                     {
  81.                         if(is_array($request->vehicle_segment_code))
  82.                         {
  83.                             $where[] = "vehicle_type.segment IN ('" .implode("','",$request->vehicle_segment_code)."') ";
  84.                         }else{
  85.                             $where[] = "vehicle_type.segment = '{$request->vehicle_segment_code}'";
  86.                         }
  87.                     }
  88.  
  89.                     if($request->vehicle_series_code)
  90.                     {
  91.                         if(is_array($request->vehicle_series_code))
  92.                         {
  93.                             $where[] = "vehicle_type.series IN ('" .implode("','",$request->vehicle_series_code)."') ";
  94.                         }else{
  95.                             $where[] = "vehicle_type.series = '{$request->vehicle_series_code}'";
  96.                         }
  97.                     }
  98.  
  99.                     $getVehicle->join(\DB::raw("(
  100.                         SELECT id,code,material_group_desc,commercial_name FROM vehicle_type
  101.                         WHERE agent_code = '{$agent->code}' AND ".implode("AND ",$where)."
  102.                     ) as v"),'vehicles.vehicle_type_id','v.id');
  103.                 }
  104.  
  105.                 if ($request->age_range && preg_match('/-/i',$request->age_range)) {
  106.                     $dataExplode = explode('-',$request->age_range);
  107.                     $dataTo = 2628000;
  108.                     $firstTotal = trim($dataExplode[0]) * $dataTo;
  109.                     $secondTotal = trim($dataExplode[1]) * $dataTo;
  110.  
  111.                     $getVehicle->join(\DB::raw("(
  112.                         SELECT
  113.                             object_id,
  114.                             IF(scan_log.aging_time IS NULL, TIMESTAMPDIFF(SECOND, scan_log.created_at, NOW()), scan_log.aging_time) AS aging_time
  115.                         FROM scan_log
  116.                         WHERE
  117.                             agent_code = '{$agent->code}'
  118.                             AND IF(scan_log.aging_time IS NULL, TIMESTAMPDIFF(SECOND, scan_log.created_at, NOW()), scan_log.aging_time) >= '{$firstTotal}'
  119.                             AND IF(scan_log.aging_time IS NULL, TIMESTAMPDIFF(SECOND, scan_log.created_at, NOW()), scan_log.aging_time) <= '{$secondTotal}'
  120.                         GROUP BY object_id
  121.                         ) as v"), 'vehicles.id', 'v.object_id');
  122.                 }
  123.  
  124.                 if($request->age_range && preg_match('/>/i',$request->age_range))
  125.                 {
  126.                     $ageRange = trim(str_replace('>', '', $request->age_range));
  127.                     $minTotal = $ageRange * 2628000;
  128.                     $getVehicle->join(\DB::raw("(
  129.                         SELECT
  130.                             object_id,
  131.                             IF(scan_log.aging_time IS NULL, TIMESTAMPDIFF(SECOND, scan_log.created_at, NOW()), scan_log.aging_time) AS aging_time
  132.                         FROM scan_log
  133.                         WHERE
  134.                             agent_code = '{$agent->code}'
  135.                             AND IF(scan_log.aging_time IS NULL, TIMESTAMPDIFF(SECOND, scan_log.created_at, NOW()), scan_log.aging_time) >= '{$minTotal}'
  136.                         GROUP BY object_id
  137.                         ) as v"), 'vehicles.id', 'v.object_id');
  138.                 }
  139.  
  140.                 $getVehicle = $getVehicle->get();
  141.                 $getVehicleTotal = $getVehicle->pluck('total', 'color_id')->toArray();
  142.                 arsort($getVehicleTotal);
  143.  
  144.                 foreach ($getVehicleTotal as $color_id => $total) {
  145.                     $vehicle_name = [];
  146.                     $vehicle_name[] = isset($vehicleColorCode[$color_id]) ? $vehicleColorCode[$color_id] : '';
  147.                     $vehicle_name[] = isset($vehicleColorName[$color_id]) ? $vehicleColorName[$color_id] : null;
  148.                     $data['data' . $i][] = [
  149.                         'branch_code' => isset($request->branch_code) && $request->branch_code ? $request->branch_code : '',
  150.                         'vehicle_type' => [
  151.                             'colour' => implode(' - ', array_filter($vehicle_name)),
  152.                             'total' => $total,
  153.                         ]
  154.                     ];
  155.                 }
  156.             }
  157.  
  158.             $response = [
  159.                 'data' => [(isset($data['data1']) ? $data['data1'] : []), (isset($data['data2']) ? $data['data2'] : [])]
  160.             ];
  161.  
  162.             return ResponseBase::success($response);
  163.  
  164.         } catch (\Exception $e) {
  165.             return ResponseBase::error(400,$e->getMessage());
  166.         }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top