$agent = Auth::guard('agent')->user();

            $startDate = '';
            $endDate = '';

            // get dealer
            $locationCode = LocationCode::select('dealer_code', 'karesidenan')->where('agent_code',$agent->code);

            // get dealer
            $dealer = clone $locationCode;
            $locationDealerCode = $dealer->where('location_type','1')->get()->pluck('dealer_code');

            // get main dealer
            $mainDealer = clone $locationCode;
            $locationMainDealerCode = $mainDealer->where('location_type','2')->get()->pluck('dealer_code');


            $joinVehicleType = VehicleType::select('vehicle_type.id','category','segment','sub_series', 'type', 'code', 'commercial_name')
                ->where('agent_code',$agent->code)
                ->whereNotNull('category')
                ->whereNotNull('segment')
                ->whereNotNull('sub_series')
                ->whereNotNull('type');

            $joinVehicle = Vehicle::select('vehicles.id', 'vehicle_type_id', 'qrcode_code', 'vt.category', 'vt.segment', 'vt.sub_series', 'vt.type', 'vt.code', 'vt.commercial_name')
                ->where('agent_code', $agent->code);

            $joinVehicle->join(\DB::raw("(" . QueryLib::getSqlWithBindings($joinVehicleType) . ") as vt"), 'vt.id', 'vehicles.vehicle_type_id');

            // total vehicle
            $totalVehicle = ScanLog::selectRaw('COUNT(DISTINCT(scan_log.qrcode_code)) as total,  v.category, v.segment, v.sub_series, v.type, v.code, v.commercial_name')
                ->where('agent_code', $agent->code)->where('flag', ScanLog::OUT)
                ->whereIn('agent_from', $locationMainDealerCode)
                ->where('agent_to', $agent->merchant_code)
                ->groupBy('v.sub_series');

            // scan dealer spg
            $scanIn = ScanLog::selectRaw('COUNT(DISTINCT(scan_log.qrcode_code)) as total, sub_series')
                ->where('agent_code', $agent->code)->whereRaw("LOWER(activity_description) = 'spg'")
                ->where('branch_code', $agent->merchant_code)
                ->groupBy('v.sub_series');

            $scanLog = ScanLog::where('scan_log.agent_code',$agent->code)
                ->selectRaw('DISTINCT(scan_log.qrcode_code)')
                ->where('agent_to', $agent->merchant_code)
                ->whereRaw("LOWER(activity_description) = 'spg'");


            $claimWithoutSpg = VehicleClaim::selectRaw("COUNT(DISTINCT(vehicle_claim.qrcode_code)) as total, v.sub_series")
                ->where('vehicle_claim.branch_code', $agent->merchant_code)
                ->where('vehicle_claim.agent_code', $agent->code);

            $claimWithoutSpg->join(\DB::raw("(".QueryLib::getSqlWithBindings($joinVehicle).") as v"), 'vehicle_claim.vehicle_id', 'v.id')
                ->groupBy('v.sub_series');

            // join to vehicle
            $totalVehicle->join(\DB::raw("(".QueryLib::getSqlWithBindings($joinVehicle).") as v"), 'scan_log.qrcode_code', 'v.qrcode_code');
            $scanIn->join(\DB::raw("(".QueryLib::getSqlWithBindings($joinVehicle).") as v"), 'scan_log.object_id', 'v.id');

            // total sales
            $vehicleClaim = VehicleClaim::selectRaw('COUNT(DISTINCT(vehicle_claim.qrcode_code)) as total, v.category, v.segment, v.sub_series')
                ->where('agent_code', $agent->code)
                ->where('vehicle_claim.branch_code', $agent->merchant_code)
                ->groupBy('v.sub_series');

            // join sales with to vehicle
            $vehicleClaim->join(\DB::raw("(".QueryLib::getSqlWithBindings($joinVehicle).") as v"), 'vehicle_claim.vehicle_id', 'v.id');

            // set start_date and end_date by request month
            if ($request->month && $request->month >= 1){
                $startDate = DateLib::getStartDate($request->month);
                if ($request->month == 2) {
                    $endDate = DateLib::getEndDate(1);
                } else {
                    $endDate = date('Y-m-d 23:59:59');
                }
            }

            // set start_date and end_date by request date_start and date_end
            if($request->date_start)
                $startDate = substr($request->date_start,0,10) . ' 00:00:00';

            if($request->date_end)
                $endDate = substr($request->date_end,0,10) . ' 23:59:59';

            if ($startDate)
            {
                $scanInIncoming = clone  $scanIn;
                $claimWithoutSpgIncoming = clone $claimWithoutSpg;
                $claimIncoming = clone $vehicleClaim;

                $scanInIncoming->whereRaw("IF(saved_at IS NOT NULL, saved_at, created_at) <= '{$startDate}' ");
                $claimWithoutSpgIncoming->whereRaw("IF(vehicle_claim.invoice_date IS NOT NULL, vehicle_claim.invoice_date, vehicle_claim.created_at) <= '{$startDate}'");
                $claimIncoming->whereRaw("IF(invoice_date IS NOT NULL, invoice_date, created_at) <= '{$startDate}' ");

            }

            if ($endDate)
            {
                $scanIn->whereRaw("IF(saved_at IS NOT NULL, saved_at, created_at) <= '{$endDate}'");
                $claimWithoutSpg->whereRaw("IF(vehicle_claim.invoice_date IS NOT NULL, vehicle_claim.invoice_date, vehicle_claim.created_at) <= '{$endDate}'");
            }

            $claimWithoutSpg->leftJoin(\DB::raw("(".QueryLib::getSqlWithBindings($scanLog).") as s"), 's.qrcode_code', 'vehicle_claim.qrcode_code')->whereNull('v.qrcode_code');

            if ($startDate && $endDate)
            {
                $totalVehicle->whereRaw("IF(saved_at IS NOT NULL, saved_at, created_at) >= '{$startDate}' AND IF(saved_at IS NOT NULL, saved_at, created_at) <= '{$endDate}'");
                $vehicle = clone $totalVehicle;

                $vehicleClaim->whereRaw("IF(invoice_date IS NOT NULL, invoice_date, created_at) >= '{$startDate}' AND IF(invoice_date IS NOT NULL, invoice_date, created_at) <= '{$endDate}'");
            }

            // total vehicle
            $totalVehicleSubSeries = $totalVehicle->pluck('total', 'sub_series');

            // claim wihtout spg
            $claimWithoutSpgSubSeries = $claimWithoutSpg->pluck('total', 'sub_series');

            // scanIn
            $scanInSubSeries = $scanIn->pluck('total', 'sub_series');

            // claim without spg
            $claimWithoutSpgIncomingSubSeries = $claimWithoutSpgIncoming->pluck('total', 'sub_series');

            // scanin incoming
            $scanInIncomingSubSeries = $scanInIncoming->pluck('total', 'sub_series');

            // claim incoming
            $claimIncomingSubSeries = $claimIncoming->pluck('total', 'sub_series');

            // total sales
            $totalSalesSubSeries = $vehicleClaim->pluck('total', 'sub_series');

            $vehicle = $vehicle->get();
            $collectData = collect($vehicle);