<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Auth;
use App\User;
use Session;
use Redirect;
use DB;
use Storage;
use File;
use Hash;
use Illuminate\Support\Facades\Input;
use Illuminate\Support\Facades\Mail;
use Maatwebsite\Excel\Facades\Excel;

class RsmController extends Controller
{
    public function dashboard(Request $request)
    {
        $download = $request->download;
        $selectMonth = $request->selectMonth;
        $designation = DB::table('staff_detail')->where('user_id', Auth::id())->first();
        if (Auth::check() && $designation->designation_id == '13') {
            $user_id = Auth::id();
            $dealer_ids = DB::table('users')->select('id', 'name', 'reporting_authority')->where(['role' => 2, 'status' => 1])->orderBy('id', 'DESC')->get();
            $dealers = array();
            $d_ids = array();
            foreach ($dealer_ids as $i => $j) {
                $report_ids = explode(",", $j->reporting_authority);
                if (in_array($user_id, $report_ids)) {
                    $dealers[] = $dealer_ids[$i];
                    $d_ids[] = $dealer_ids[$i]->id;
                }
            }
            if (!empty($selectMonth)) {
                $exp = explode('-', $selectMonth);
                $month = $exp[1];
                $year = $exp[0];
            } else {
                $currentMonthYear = explode('-', date('Y-m'));
                $month = $currentMonthYear[1];
                $year = $currentMonthYear[0];
            }
            $first_day = date('Y-m-01');
            $currentMonth = date('Y-m');
            $today = date('Y-m-d');
            $result = DB::table('jobs as j')
                ->select(DB::raw('SUM(j.customer_price) as customer_price,  SUM(j.actual_price) as actual_price, SUM(j.difference_price) as difference_price, SUM(j.hvt_total) as hvt_total, SUM(j.hvt_value) as hvt_value,SUM(j.treatment_total) as vas_total, SUM(j.customer_price) as vas_value, j.job_date, j.foc_options'))
                ->whereMonth('j.job_date', $month)
                ->whereYear('j.job_date', $year)
                ->where('j.delete_job', 1)
                ->whereIn('j.dealer_id', $d_ids)
                // ->where('j.foc_options',5)
                ->groupBy('j.job_date')
                ->get();
            $i = 0;
            foreach ($result as $key => $value) {
                $total = DB::table('jobs_by_date')->select(DB::raw('SUM(total_jobs) as total_jobs'))->where('job_added_date', $value->job_date)->first();
                if (!empty($total->total_jobs)) {
                    $result[$i]->total = $total->total_jobs;
                } else {
                    $result[$i]->total = 0;
                }
                $i++;
            }
            $total = DB::table('jobs_by_date')
                ->select(DB::raw('SUM(total_jobs) as mtd_total'))
                ->whereMonth('job_added_date', $month)
                ->whereYear('job_added_date', $year)
                ->whereIn('dealer_id', $d_ids)
                ->first();
            $result = json_decode(json_encode($result), true);
            if (!empty($selectMonth)) {
                $current = "'" . $selectMonth . "'";
                $currentM = $selectMonth;
            } else {
                $current = "'" . $currentMonth . "'";
                $currentM = $currentMonth;
            }

            return view('rsm.dashboard', [
                'dealers' => $dealers,
                'current' => $current,
                'currentM' => $currentM,
                'result' => $result,
                'total' => $total->mtd_total,
                'oldMonth' => @$selectMonth,
            ]);
        } else {
            return redirect('/admin/login');
        }
    }

    public function downloadDashboard(Request $request)
    {
        $user_id = Auth::id();
        $dealer_ids = DB::table('users')->select('id', 'name', 'reporting_authority')->where(['role' => 2, 'status' => 1])->orderBy('id', 'DESC')->get();
        $dealers = array();
        $d_ids = array();
        foreach ($dealer_ids as $i => $j) {
            $report_ids = explode(",", $j->reporting_authority);
            if (in_array($user_id, $report_ids)) {
                $dealers[] = $dealer_ids[$i];
                $d_ids[] = $dealer_ids[$i]->id;
            }
        }
        $selectMonth = $request->selectMonth1;
        if (!empty($selectMonth)) {
            $exp = explode('-', $selectMonth);
            $month = $exp[1];
            $year = $exp[0];
        } else {
            $currentMonthYear = explode('-', date('Y-m'));
            $month = $currentMonthYear[1];
            $year = $currentMonthYear[0];
        }
        $first_day = date('Y-m-01');
        $currentMonth = date('Y-m');
        $today = date('Y-m-d');
        $result = DB::table('jobs as j')
            ->select(DB::raw('SUM(j.customer_price) as customer_price,  SUM(j.actual_price) as actual_price, SUM(j.difference_price) as difference_price, SUM(j.hvt_total) as hvt_total, SUM(j.hvt_value) as hvt_value,SUM(j.treatment_total) as vas_total, SUM(j.customer_price) as vas_value, j.job_date, j.foc_options'))
            ->whereMonth('j.job_date', $month)
            ->whereYear('j.job_date', $year)
            ->where('j.delete_job', 1)
            // ->where('j.foc_options',5)
            ->whereIn('j.dealer_id', $d_ids)
            ->groupBy('j.job_date')
            ->get();
        $i = 0;
        foreach ($result as $key => $value) {
            $total = DB::table('jobs_by_date')->select(DB::raw('SUM(total_jobs) as total_jobs'))->where('job_added_date', $value->job_date)->first();
            if (!empty($total->total_jobs)) {
                $result[$i]->total = $total->total_jobs;
            } else {
                $result[$i]->total = 0;
            }
            $i++;
        }
        $total = DB::table('jobs_by_date')
            ->select(DB::raw('SUM(total_jobs) as mtd_total'))
            ->whereMonth('job_added_date', $month)
            ->whereYear('job_added_date', $year)
            ->first();
        $result = json_decode(json_encode($result), true);
        /************************************ Download Dashboard *************************/
        $vas_total = $vas_value = $hvt_total = $hvt_value = 0;
        foreach ($result as $value) {
            $vas_total = $vas_total + $value['vas_total'];
            // $vas_value = $vas_value + $value['vas_value'];
            $vas_value = $vas_value + $value['actual_price'];
            $hvt_total = $hvt_total + $value['hvt_total'];
            $hvt_value = $hvt_value + $value['hvt_value'];
        }
        $mtd_total = $total->mtd_total;
        return Excel::create('Dashboard_' . date("d/m/Y"), function ($excel) use ($mtd_total, $vas_total, $hvt_total, $vas_value, $hvt_value) {
            $excel->sheet('sheet', function ($sheet) use ($mtd_total, $vas_total, $hvt_total, $vas_value, $hvt_value) {
                $sheet->setBorder('B1:C10');
                $sheet->cells('B1:C1', function ($cells) {
                    $cells->setBackground('#FFFFFF');
                });
                $sheet->cells('B2:C2', function ($cells) {
                    $cells->setBackground('#00a65a');
                });
                $sheet->cells('B3:C3', function ($cells) {
                    $cells->setBackground('#dd4b39');
                });
                $sheet->cells('B4:C4', function ($cells) {
                    $cells->setBackground('#00a65a');
                });
                $sheet->cells('B5:C5', function ($cells) {
                    $cells->setBackground('#00a65a');
                });
                $sheet->cells('B6:C6', function ($cells) {
                    $cells->setBackground('#FFFF00');
                });
                $sheet->cells('B7:C7', function ($cells) {
                    $cells->setBackground('#dd4b39');
                });
                $sheet->cells('B8:C8', function ($cells) {
                    $cells->setBackground('#00a65a');
                });
                $sheet->cells('B9:C9', function ($cells) {
                    $cells->setBackground('#00a65a');
                });
                $sheet->cells('B10:C10', function ($cells) {
                    $cells->setBackground('#FFFF00');
                });
                $sheet->setCellValue('B1', 'Monthly Treatments till Date');
                $sheet->mergeCells("B1:C1");
                $sheet->setCellValue('B2', 'RO');
                $sheet->setCellValue('C2', $mtd_total);
                $sheet->setCellValue('B3', 'VAS');
                $sheet->mergeCells("B3:C3");
                $sheet->setCellValue('B4', 'No of Trmt');
                $sheet->setCellValue('C4', $vas_total);
                $sheet->setCellValue('B5', 'Amount');
                $sheet->setCellValue('C5', $vas_value);
                $sheet->setCellValue('B6', 'Value Per Treatment');
                $sheet->setCellValue('C6', vas_in_percentage(@$vas_value, @$vas_total));
                $sheet->setCellValue('B7', 'HVT');
                $sheet->mergeCells("B7:C7");
                $sheet->setCellValue('B8', 'No of Trmt');
                $sheet->setCellValue('C8', $hvt_total);
                $sheet->setCellValue('B9', 'Amount');
                $sheet->setCellValue('C9', $hvt_value);
                $sheet->setCellValue('B10', 'HVT %');
                $sheet->setCellValue('C10', hvt_in_percentage(@$hvt_value, @$vas_value));
            });
        })->export('xlsx');
    }

    public function dealer_management(Request $request)
    {
        $search = $request->search;
        $ASM = Auth::id();
        $dealers = User::where('dealer_id', $ASM)
            ->where(function ($query) use ($search) {
                if (!empty($search)) {
                    if (isset($search)) {
                        if (!empty(trim($search))) {
                            $query->orWhere('name', 'like', '%' . $search . '%');
                            $query->orWhere('email', 'like', '%' . $search . '%');
                            $query->orWhere('mobile_no', 'like', '%' . $search . '%');
                        }
                    }
                }
            })
            ->orderBy('name', 'ASC')->paginate(15);

        return view('rsm.dealers', [
            'dealers' => $dealers->appends(Input::except('page')),
        ]);
    }

    // view add new Dealer page
    public function addDealer()
    {
        $states = DB::table('states')->get();
        $grouplist = DB::table('groups')->get();
        $oemlist = DB::table('oems')->get();
        return view('rsm.addDealers', [
            'states' => $states,
            'grouplist' => $grouplist,
            'oemlist' => $oemlist,
        ]);
    }

    // save new Dealer
    public function insertDealer(Request $request)
    {
        $ASM = Auth::id();
        $post = $request->all();
        $this->validate(
            $request,
            [
                'name' => 'required',
                'address' => 'required|max:250',
                'state_id' => 'required',
                'city' => 'required',
                'district_id' => 'required',
                'mobile_no' => 'required|digits:10|unique:users,mobile_no',
                'email' => 'required|unique:users,email',
                //'group' => 'required',
                'OEM' => 'required',
            ],
            [
                'name.required' => 'Please enter name',
                'email.required' => 'Please enter email',
                'address.required' => 'Please enter address',
                'city.required' => 'Please enter city',
                'district_id.required' => 'Please select district',
                'state_id.required' => 'Please select state',
                'mobile_no.required' => 'Please enter mobile no.',
                // 'group.required' => 'Please enter group.',
                'OEM.required' => 'Please enter OEM.',
                'latitude' => ['regex:/^[-]?(([0-8]?[0-9])\.(\d+))|(90(\.0+)?)$^/'],
                'longitude' => ['regex:/^[-]?(([0-8]?[0-9])\.(\d+))|(90(\.0+)?)$^/'],
            ]
        );
        $data = array(
            'role' => 2,
            'name' => $post['name'],
            'email' => $post['email'],
            'address' => $post['address'],
            'longitude' => $post['longitude'],
            'latitude' => $post['latitude'],
            'mobile_no' => $post['mobile_no'],
            'state_id' => $post['state_id'],
            'district_id' => $post['district_id'],
            'city' => $post['city'],
            'group_id' => @$post['group'],
            'oem_id' => $post['OEM'],
            'dealer_id' => $ASM,
        );

        User::insert($data);

        Session::flash('success', 'Dealer added successfully!');
        return redirect('/rsm/dealer_management');
    }

    //Change Dealer status or delete
    public function statusDealer($status, $id)
    {
        if (@$status) {
            if ($status == "deactivate") {
                $udata['status'] = 0;
                User::where('id', $id)->update($udata);
                Session::flash('success', 'Dealer deactivated successfully!');
            } else if ($status == "activate") {
                $udata['status'] = 1;
                User::where('id', $id)->update($udata);
                Session::flash('success', 'Dealer activated successfully!');
            }
        } else {
            Session::flash('error', 'Something wrong!');
        }
        return redirect('/rsm/dealer_management');
    }

    // view edit Dealer page
    public function editDealer($id)
    {
        $result = User::where('role', 2)->find($id);
        $states = DB::table('states')->get();
        $grouplist = DB::table('groups')->get();
        $oemlist = DB::table('oems')->get();
        $districts = DB::table('districts')->where('state_id', $result->state_id)->get();
        if (!empty($result)) {
            return view('rsm.editDealers', [
                'result' => $result,
                'states' => $states,
                'districts' => $districts,
                'grouplist' => $grouplist,
                'oemlist' => $oemlist,
            ]);
        } else {
            Session::flash('error', 'No dealer found!');
            return redirect('/rsm/dealer_management');
        }
    }

    // update existing Dealer
    public function updateDealer(Request $request)
    {
        $ASM = Auth::id();
        $post = $request->all();
        $this->validate(
            $request,
            [
                'name' => 'required',
                'address' => 'required|max:250',
                'state_id' => 'required',
                'city' => 'required',
                'district_id' => 'required',
                'mobile_no' => 'required|digits:10|unique:users,mobile_no,' . $request->id,
                'email' => 'required|unique:users,email,' . $request->id,
                //'group' => 'required',
                'OEM' => 'required',
            ],
            [
                'name.required' => 'Please enter name',
                'email.required' => 'Please enter email',
                'address.required' => 'Please enter address',
                'city.required' => 'Please enter city',
                'district_id.required' => 'Please select district',
                'state_id.required' => 'Please select state',
                'mobile_no.required' => 'Please enter mobile no.',
                // 'group.required' => 'Please enter group.',
                'OEM.required' => 'Please enter OEM.',
                'latitude' => ['regex:/^[-]?(([0-8]?[0-9])\.(\d+))|(90(\.0+)?)$^/'],
                'longitude' => ['regex:/^[-]?(([0-8]?[0-9])\.(\d+))|(90(\.0+)?)$^/'],
            ]
        );
        $data = array(
            'role' => 2,
            'name' => $post['name'],
            'email' => $post['email'],
            'address' => $post['address'],
            'longitude' => $post['longitude'],
            'latitude' => $post['latitude'],
            'mobile_no' => $post['mobile_no'],
            'state_id' => $post['state_id'],
            'district_id' => $post['district_id'],
            'city' => $post['city'],
            'group_id' => @$post['group'],
            'oem_id' => $post['OEM'],
            'dealer_id' => $ASM,
        );
        User::where('id', $post['id'])->update($data);

        Session::flash('success', 'Dealer updated successfully!');
        return redirect('/rsm/dealer_management');
    }



    // view Staff listing
    public function staff_management(Request $request)
    {
        $user_id = Auth::id();
        $search = $request->search;
        $des = $request->designation_id;
        $dealer_id = $request->dealer_id;
        $firm_id = $request->firm_id;
        $office_id = $request->office_id;
        $status = $request->status;

        $designations = DB::table('designations')->get();
        $dep_des = DB::table('staff_detail')->first();

        $dealers = array();
        if (!empty($firm_id)) {
            $results = DB::table('users')->select('id', 'name', 'reporting_authority')->where(['role' => 2, 'status' => 1, 'firm_id' => $firm_id])->orderBy('id', 'DESC')->get();
            foreach ($results as $key => $value) {
                $reporting_ids = explode(",", $value->reporting_authority);
                if (in_array($user_id, $reporting_ids)) {
                    $dealers[] = $results[$key];
                }
            }
            $firms = DB::table('firms')->get();
        } else {
            $results = DB::table('users')->select('id', 'name', 'reporting_authority')->where(['role' => 2, 'status' => 1])->orderBy('id', 'DESC')->get();
            foreach ($results as $key => $value) {
                $reporting_ids = explode(",", $value->reporting_authority);
                if (in_array($user_id, $reporting_ids)) {
                    $dealers[] = $results[$key];
                }
            }
        }
        $firms = DB::table('firms')->get();
        $offices = DB::table('users')->select('id', 'name')->where(['role' => 6, 'status' => 1])->get();

        $result = User::join('staff_detail', 'users.id', '=', 'staff_detail.user_id')
            // ->join('emp_hierarchy', 'emp_hierarchy.user_id', '=', 'users.id')
            ->select('*', 'users.id as user_id')
            ->whereIn('role', [3, 4])
            ->where('reporting_authority', $user_id)
            ->where(function ($query) use ($des, $search, $firm_id, $dealer_id, $office_id, $status) {
                if (!empty($search)) {
                    if (isset($search)) {
                        if (!empty(trim($search))) {
                            $query->orWhere('name', 'like', '%' . $search . '%');
                            $query->orWhere('mobile_no', 'like', '%' . $search . '%');
                        }
                    }
                }
                if (!empty($firm_id)) {
                    if (isset($firm_id)) {
                        if (!empty(trim($firm_id))) {
                            $query->Where(['firm_id' => $firm_id, 'status' => 1]);
                        }
                    }
                }
                if (!empty($dealer_id)) {
                    if (isset($dealer_id)) {
                        if (!empty(trim($dealer_id))) {
                            $query->Where(['dealer_id' => $dealer_id, 'status' => 1]);
                        }
                    }
                }
                if (!empty($office_id)) {
                    if (isset($office_id)) {
                        if (!empty(trim($office_id))) {
                            $query->Where(['dealer_office' => $office_id, 'status' => 1]);
                        }
                    }
                }
                if (!empty($des)) {
                    if (isset($des)) {
                        if (!empty(trim($des))) {
                            $query->Where(['designation_id' => $des, 'status' => 1]);
                        }
                    }
                }
                if (!empty($status)) {
                    if ($status == 'activated') {
                        $query->Where('status', 1);
                    } elseif ($status == 'deactivated') {
                        $query->Where('status', 0);
                    }
                }
            })
            ->orderBy('name', 'ASC')->paginate(20);
        return view('rsm.staff', [
            'result' => $result->appends(Input::except('page')),
            'designations' => $designations,
            'des' => $des,
            'dealers' => $dealers,
            'dealer_id' => $dealer_id,
            'dep_des' => $dep_des,
            'firms' => $firms,
            'firm_id' => $firm_id,
            'offices' => $offices,
            'office_id' => $office_id,
            'status' => $status,
        ]);
    }

    public function downloadStaff(Request $request)
    {
        $user_id = Auth::id();
        $search = $request->search;
        $des = $request->designation_id;
        $dealer_id = $request->dealer_id;
        $firm_id = $request->firm_id;

        $designations = DB::table('designations')->get();
        $dep_des = DB::table('staff_detail')->first();
        $results = DB::table('users')->select('id', 'name', 'reporting_authority')->where(['role' => 2, 'status' => 1])->orderBy('id', 'DESC')->get();
        $dealers = array();
        foreach ($results as $key => $value) {
            $reporting_ids = explode(",", $value->reporting_authority);
            if (in_array($user_id, $reporting_ids)) {
                $dealers[] = $results[$key];
            }
        }
        $firms = DB::table('firms')->get();

        $result = User::join('staff_detail', 'users.id', '=', 'staff_detail.user_id')
            // ->join('emp_hierarchy', 'emp_hierarchy.user_id', '=', 'users.id')
            ->select('*', 'users.id as user_id')
            ->whereIn('role', [3, 4])
            ->where('reporting_authority', $user_id)
            ->where(function ($query) use ($des, $search, $firm_id, $dealer_id) {
                if (!empty($search)) {
                    if (isset($search)) {
                        if (!empty(trim($search))) {
                            $query->orWhere('name', 'like', '%' . $search . '%');
                            $query->orWhere('mobile_no', 'like', '%' . $search . '%');
                        }
                    }
                }
                if (!empty($firm_id)) {
                    if (isset($firm_id)) {
                        if (!empty(trim($firm_id))) {
                            $query->Where(['firm_id' => $firm_id, 'status' => 1]);
                        }
                    }
                }
                if (!empty($dealer_id)) {
                    if (isset($dealer_id)) {
                        if (!empty(trim($dealer_id))) {
                            $query->Where(['dealer_id' => $dealer_id, 'status' => 1]);
                        }
                    }
                }
                if (!empty($des)) {
                    if (isset($des)) {
                        if (!empty(trim($des))) {
                            $query->Where(['designation_id' => $des, 'status' => 1]);
                        }
                    }
                }
            })
            ->orderBy('name', 'ASC')->paginate(20);

        return Excel::create('Staff ' . date("d M,Y"), function ($excel) use ($result) {
            $excel->sheet('mySheet', function ($sheet) use ($result) {
                $sheet->setCellValue('A1', 'Firm');
                $sheet->setCellValue('B1', 'Emp. Code');
                $sheet->setCellValue('C1', 'Name');
                $sheet->setCellValue('D1', 'Mobile No.');
                $sheet->setCellValue('E1', 'Dealer Name');
                $sheet->setCellValue('F1', 'Reporting Authority');
                $sheet->setCellValue('G1', 'Department');
                $sheet->setCellValue('H1', 'Designation');
                $sheet->setCellValue('I1', 'Access Right');
                $sheet->setCellValue('J1', 'Date Of Joining');
                $sheet->setCellValue('K1', 'Date Of Leaving');
                $i = 2;
                $loop = 1;
                foreach ($result as $key => $value) {
                    if ($value->role == "5") {
                        $access_rights = "ASM";
                    } elseif ($value->role == "3") {
                        $access_rights = "All";
                    } else {
                        $access_rights = "Attendance Only";
                    }

                    if (!empty($value->doj)) {
                        $doj = date("Y-m-d", strtotime($value->doj));
                    } else {
                        $doj = '';
                    }
                    if (!empty($value->dol)) {
                        $dol = date("Y-m-d", strtotime($value->dol));
                    } else {
                        $dol = '';
                    }

                    $sheet->setCellValue('A' . $i, get_firm_short_code(@$value->firm_id));
                    $sheet->setCellValue('B' . $i, $value->emp_code);
                    $sheet->setCellValue('C' . $i, $value->name);
                    $sheet->setCellValue('D' . $i, $value->mobile_no);
                    $sheet->setCellValue('E' . $i, get_dealer_name(@$value->dealer_id));
                    $sheet->setCellValue('F' . $i, get_reporting_authority_name(@$value->reporting_authority));
                    $sheet->setCellValue('G' . $i, get_department_name($value->department_id));
                    $sheet->setCellValue('H' . $i, get_designation_name($value->designation_id));
                    $sheet->setCellValue('I' . $i, $access_rights);
                    $sheet->setCellValue('J' . $i, $doj);
                    $sheet->setCellValue('K' . $i, $dol);
                    $i++;
                    $loop++;
                }
            });
        })->download('csv');
    }


    public function getDealerPermission($user_id, $del_id)
    {
        // $reporting_authority = get_asm($del_id);
        $checkDealer = DB::table('users')->where(['id' => $user_id, 'dealer_id' => $del_id])->first();
        if (!empty($checkDealer)) {
            $html = '';
        } else {
            $html = 'You are changing dealer. Are you sure ?';
        }
        return response()->json(['html' => $html]);
    }

    public function getdealerauthority(Request $request)
    {
        $user_id = $request->user_id;
        $del_id = $request->del_id;
        $result = DB::table('users')
            ->where('id', $del_id)
            ->where('reporting_authority', '!=', $user_id)
            ->select('reporting_authority')
            ->first();
        if (!empty($result)) {
            $authorities = explode(",", @$result->reporting_authority);
            if (($key = array_search($user_id, $authorities)) !== false) {
                unset($authorities[$key]);
            }
            $authorities = json_decode(json_encode($authorities), true);
            if (!empty($authorities)) {
                $authority_id = Auth::id();
                $authority_name = get_name($authority_id);
                $authority_des_name = get_designation_by_userid($authority_id);
                $res = '<option value="">Select Reporting Authority</option>';
                // foreach ($authorities as $authority) {
                //     $authority_id = $authority;
                //     $authority_name = get_name($authority);
                //     $authority_des_name = get_designation_by_userid($authority);
                //     $res .= "<option value='$authority_id'>$authority_name - $authority_des_name</option>";
                // }
                $res .= "<option value='$authority_id'>$authority_name - $authority_des_name</option>";
            } else {
                $res = "<option value=''>No Reporting Authority found</option>";
            }
        } else {
            $res = "<option value=''>No Reporting Authority found</option>";
        }
        return $res;
    }

    public function getreportingpermission($user_id, $del_id, $del_authid)
    {
        // $reporting_authority = get_asm($del_id);
        $checkAuthority = DB::table('users')->where(['id' => $user_id, 'dealer_id' => $del_id, 'reporting_authority' => $del_authid])->first();
        if (!empty($checkAuthority)) {
            $html = '';
        } else {
            $html = 'You are changing Reporting Authority. Are you sure ?';
        }
        return response()->json(['html' => $html]);
    }

    public function getauthority($user_id, $authority_id)
    {
        $checkAuthority = DB::table('users')->where(['id' => $user_id, 'reporting_authority' => $authority_id])->first();
        if (!empty($checkAuthority)) {
            $html = '';
        } else {
            $html = 'You are changing reporting authority. Are you sure ?';
        }
        return response()->json(['html' => $html]);
    }
    //Change Staff status or delete
    public function statusStaff($status, $id)
    {
        if (@$status) {
            if ($status == "deactivate") {
                $udata['status'] = 0;
                $udata['updated_at'] = date('Y-m-d h:i:s');
                User::where('id', $id)->update($udata);
                Session::flash('success', 'Staff member deactivated successfully!');
            } else if ($status == "activate") {
                $udata['status'] = 1;
                $udata['updated_at'] = date('Y-m-d h:i:s');
                User::where('id', $id)->update($udata);
                Session::flash('success', 'Staff member activated successfully!');
            } else if ($status == "delete") {
                User::where('id', $id)->delete();
                Session::flash('success', 'Staff member deleted successfully!');
            }
        } else {
            Session::flash('error', 'Something wrong!');
        }
        return redirect('/rsm/staff_management');
    }

    // public function emp_hierarchy()
    // {
    //     $result = DB::table('staff_detail as sd')->join('users as u', 'u.id', '=', 'sd.user_id')->join('emp_hierarchy as eh', 'u.id', '=', 'eh.user_id')->where(['sd.designation_id'=>14, 'u.status'=>1])->paginate(20);
    //     return view('rsm.emp_hierarchy',[
    //         'result' => $result->appends(Input::except('page')),
    //     ]);
    // }

    public function getreportingauthority(Request $request)
    {
        $user_id = $request->user_id;
        $reportinglevel = $request->reportinglevel;
        $authority_id = $request->auth_id;
        $reporting_authorities = DB::table('users as u')
            ->join('staff_detail as sd', 'sd.user_id', '=', 'u.id')
            ->join('designations as d', 'sd.designation_id', '=', 'd.id')
            ->where('d.level', $reportinglevel)
            ->where('u.id', '!=', $user_id)
            ->select('u.id as uid', 'u.name as uname', 'sd.designation_id as des_id')
            ->get();
        $reporting_authorities = json_decode(json_encode($reporting_authorities), true);
        if (@$reporting_authorities) {
            $res = '<option value="">Select Reporting Authority</option>';
            foreach ($reporting_authorities as $authority) {
                $authority_id = $authority["uid"];
                $authority_name = $authority["uname"];
                $authority_des_name = get_designation_by_userid($authority["uid"]);
                $res .= "<option value='$authority_id'>$authority_name - $authority_des_name</option>";
            }
        } else {
            $res = "<option value=''>No Reporting Authority found</option>";
        }
        return $res;
    }

    public function editEmpHierarchy($id)
    {
        $user_id = Auth::id();
        $result = DB::table('emp_hierarchy as eh')->join('users as u', 'u.id', '=', 'eh.user_id')->select('*', 'eh.id as id', 'u.id as user_id')->where('eh.user_id', $id)->first();
        if (empty($result)) {
            Session::flash('error', 'Please add required information first.');
            return redirect()->back();
        } else {
            $dep_des = DB::table('staff_detail')->where('user_id', $id)->first();
            // $dealers = DB::table('users')->where(['role'=>2, 'reporting_authority'=>$user_id, 'status'=>1])->select('id as dealer_id','name as dealer_name')->orderBy('name','ASC')->get();
            $results = DB::table('users')->select('id as dealer_id', 'name as dealer_name', 'reporting_authority')->where(['role' => 2, 'status' => 1])->orderBy('id', 'DESC')->get();
            $dealers = array();
            foreach ($results as $key => $value) {
                $reporting_ids = explode(",", $value->reporting_authority);
                if (in_array($user_id, $reporting_ids)) {
                    $dealers[] = $results[$key];
                }
            }
            $dealer_authorities = DB::table('users')
                ->where('id', @$result->dealer_id)
                ->select('reporting_authority')
                ->get();
            // $dealer_authorities = explode(",",@$dealer_authorities[0]->reporting_authority);
            $dealer_authorities = $user_id;
            $levels = DB::table('designation_levels')->get();
            if (empty($dep_des)) {
                $reportinglevels = $levels;
            } else {
                $reportinglevels = DB::table('designation_levels')->where('id', '<', getlevelbydesignation($dep_des->designation_id))->get();
            }
            $reporting_authorities = DB::table('users as u')
                ->join('staff_detail as sd', 'sd.user_id', '=', 'u.id')
                ->join('designations as d', 'sd.designation_id', '=', 'd.id')
                ->where('d.level', getlevelbydesignation(get_designation($result->reporting_authority)))
                ->select('u.id as uid', 'u.name as uname', 'sd.designation_id as des_id')
                ->get();
            return view('rsm.editEmpHierarchy', [
                'result' => $result,
                'dealers' => $dealers,
                'dep_des' => $dep_des,
                'levels' => $levels,
                'reportinglevels' => $reportinglevels,
                'reporting_authorities' => $reporting_authorities,
                'dealer_authorities' => $dealer_authorities,
            ]);
        }
    }

    public function updateEmpHierarchy(Request $request)
    {
        $post = $request->all();

        if (!empty($post['del_id'])) {
            $this->validate(
                $request,
                [
                    'del_id' => 'required',
                    // 'fdate' => 'required',
                    // 'todate' => 'required',
                ],
                [
                    'del_id.required' => 'Please select dealer',
                    // 'fdate.required' => 'Please select date.',
                    // 'todate.required' => 'Please select.',
                ]
            );
        } elseif (!empty($post['authority'])) {
            $this->validate(
                $request,
                [
                    'reporting_level' => 'required',
                    // 'fdate' => 'required',
                    // 'todate' => 'required',
                ],
                [
                    'reporting_level.required' => 'Please select Reporting Level',
                    // 'fdate.required' => 'Please select date.',
                    // 'todate.required' => 'Please select.',
                ]
            );
        } else {
            $this->validate(
                $request,
                [
                    'office' => 'required',
                    // 'fdate' => 'required',
                    // 'todate' => 'required',
                ],
                [
                    'office.required' => 'Please select office',
                    // 'fdate.required' => 'Please select date.',
                    // 'todate.required' => 'Please select.',
                ]
            );
        }


        $checkUser = DB::table('emp_hierarchy')->where('id', $post['id'])->select('dealer_id', 'authority_id')->first();
        if (!empty($post['del_id'])) {
            $d_id = $post['del_id'];
            if (empty($checkUser)) {
                DB::table('emp_hierarchy')->insert(['user_id' => $post['user_id'], 'dealer_id' => $d_id, 'authority_id' => $post['dealer_authid'], 'from_date' => $post['fdate'], 'to_date' => $post['todate']]);
            } elseif ($post['del_id'] != $checkUser->dealer_id && $post['dealer_authid'] != $checkUser->authority_id) {
                DB::table('emp_hierarchy')->where('id', $post['id'])->update(['dealer_id' => $post['del_id'], 'from_date' => $post['fdate'], 'to_date' => $post['todate'], 'status' => 3, 'authority_id' => $post['dealer_authid']]);
            } elseif ($post['del_id'] != $checkUser->dealer_id && $post['dealer_authid'] == $checkUser->authority_id) {
                DB::table('emp_hierarchy')->where('id', $post['id'])->update(['dealer_id' => $post['del_id'], 'from_date' => $post['fdate'], 'to_date' => $post['todate'], 'status' => 2, 'authority_id' => $post['dealer_authid']]);
            } else {
                DB::table('emp_hierarchy')->where('id', $post['id'])->update(['dealer_id' => $post['del_id'], 'authority_id' => $post['dealer_authid'], 'from_date' => $post['fdate'], 'to_date' => $post['todate']]);
            }
            // DB::table('users')->where('id',$post['user_id'])->update(array('dealer_id'=>$post['del_id'],'reporting_authority'=>$post['dealer_authid']));
        } elseif (!empty($post['authority'])) {
            if (empty($checkUser)) {
                DB::table('emp_hierarchy')->insert(['user_id' => $post['user_id'], 'authority_id' => $post['authority'], 'from_date' => $post['fdate'], 'to_date' => $post['todate']]);
            } elseif (!empty($checkUser) && $post['authority'] != $checkUser->authority_id) {
                DB::table('emp_hierarchy')->where('id', $post['id'])->update(array('dealer_id' => null, 'authority_id' => $post['authority'], 'from_date' => $post['fdate'], 'to_date' => $post['todate'], 'status' => 3));
            } elseif (!empty($checkUser) && $post['authority'] == $checkUser->authority_id) {
                DB::table('emp_hierarchy')->where('id', $post['id'])->update(array('dealer_id' => null, 'authority_id' => $post['authority'], 'from_date' => $post['fdate'], 'to_date' => $post['todate']));
            }
            // DB::table('users')->where('id',$post['user_id'])->update(array('dealer_id'=>null,'reporting_authority'=>$post['authority']));
        } else {
            if (empty($checkUser)) {
                DB::table('emp_hierarchy')->insert(['user_id' => $post['user_id'], 'authority_id' => $post['office'], 'from_date' => $post['fdate'], 'to_date' => $post['todate']]);
            } elseif (!empty($checkUser) && $post['office'] != $checkUser->authority_id) {
                DB::table('emp_hierarchy')->where('id', $post['id'])->update(array('dealer_id' => null, 'authority_id' => $post['office'], 'from_date' => $post['fdate'], 'to_date' => $post['todate'], 'status' => 3));
            } elseif (!empty($checkUser) && $post['office'] == $checkUser->authority_id) {
                DB::table('emp_hierarchy')->where('id', $post['id'])->update(array('dealer_id' => null, 'authority_id' => $post['office'], 'from_date' => $post['fdate'], 'to_date' => $post['todate']));
            }
            // DB::table('users')->where('id',$post['user_id'])->update(array('dealer_id'=>null,'reporting_authority'=>$post['office']));
        }
        return redirect('/rsm/staff_management')->with('success', 'data updated succesfully');
    }


    public function statusEmpHierarchy($status, $id)
    {
        if (@$status) {
            if ($status == "deactivate") {
                $udata['status'] = 0;
                DB::table('emp_hierarchy')->where('id', $id)->update($udata);
                Session::flash('success', 'Member deactivated successfully!');
            } else if ($status == "activate") {
                $udata['status'] = 1;
                DB::table('emp_hierarchy')->where('id', $id)->update($udata);
                Session::flash('success', 'Member activated successfully!');
            }
        } else {
            Session::flash('error', 'Something wrong!');
        }
        return redirect('/rsm/emp_hierarchy');
    }

    // View targets list
    public function targets()
    {
        $user_id = Auth::id();
        $getDealers = DB::table('users')
            ->where(['role' => 2, 'status' => 1])
            ->select('id', 'reporting_authority')
            ->get();
        foreach ($getDealers as $key => $value) {
            $authorities = explode(",", $getDealers[$key]->reporting_authority);
            if (in_array($user_id, $authorities)) {
                $dealers[] = $getDealers[$key]->id;
            }
        }
        $result = DB::table('target')->whereIn('dealer_id', $dealers)->paginate(10);
        foreach ($result as $key => $value) {
            $done_treatments = DB::table('jobs')->where('dealer_id', $value->dealer_id)->whereMonth('date_added', date('m', strtotime($value->month)))->whereYear('date_added', date('Y', strtotime($value->month)))->sum('treatment_total');
            $done_treatments_price = DB::table('jobs')->where('dealer_id', $value->dealer_id)->whereMonth('date_added', date('m', strtotime($value->month)))->whereYear('date_added', date('Y', strtotime($value->month)))->sum('customer_price');
            $result[$key]->done_treatments = $done_treatments;
            $result[$key]->done_treatments_price = $done_treatments_price;
        }

        return view('rsm.targets', [
            'result' => $result,
        ]);
    }

    // View Target Listings 
    public function targetListing(Request $request, $dealer_id, $template_id, $target_id)
    {

        $templates = DB::table('treatment_templates')->get();
        $search = $request->month;
        $temp_id = $request->temp_id;

        $user_id = Auth::id();
        $getDealers = DB::table('users')
            ->where(['role' => 2, 'status' => 1])
            ->select('id', 'reporting_authority')
            ->get();
        foreach ($getDealers as $key => $value) {
            $authorities = explode(",", $getDealers[$key]->reporting_authority);
            if (in_array($user_id, $authorities)) {
                $dealers[] = $getDealers[$key]->id;
            }
        }

        $result = DB::table('target_treatments')
            ->join('target', 'target.id', '=', 'target_treatments.target_id')
            ->select('target_treatments.*', 'target.*', 'target_treatments.id as id')
            ->whereIn('target.dealer_id', $dealers);
        if (!empty($search) && empty($temp_id)) {
            $result = $result->where(['target.month' => $search, 'target.dealer_id' => $dealer_id]);
            $search = $request->month;
        } elseif (empty($search) && !empty($temp_id)) {
            $result = $result->where(['target.template_id' => $temp_id, 'target.dealer_id' => $dealer_id]);
            $temp_id = $request->temp_id;
        } elseif (!empty($search) && !empty($temp_id)) {
            $result = $result->where(['target.month' => $search, 'target.template_id' => $temp_id, 'target.dealer_id' => $dealer_id]);
            $search = $request->month;
            $temp_id = $request->temp_id;
        } else {
            $result = $result->where('target_treatments.target_id', $target_id);
        }
        $result = $result->paginate(20);

        foreach ($result as $key => $value) {
            $done = DB::table('jobs')->where('dealer_id', $value->dealer_id)->whereMonth('date_added', date('m', strtotime($value->month)))->whereYear('date_added', date('Y', strtotime($value->month)))->select('treatments')->get();
            foreach ($done as $k => $val) {
                $data = json_decode($val->treatments);
                foreach ($data as $k1 => $val1) {
                    if ($result[$key]->treatment_id == $val1->id) {
                        $result[$key]->countdone[] = $val1->id;
                    }
                }
            }
        }
        // dd($result);
        return view('rsm.targetListing', [
            'result' => $result,
            'dealer_id' => $dealer_id,
            'templates' => $templates,
            'template_id' => $template_id,
            'temp_id' => $temp_id,
            'search' => $search,
        ]);
    }

    // View Jobs listing
    public function jobs(Request $request)
    {
        $search = $request->search;
        $user_id = Auth::id();
        $getDealers = DB::table('users')
            ->where(['role' => 2, 'status' => 1])
            ->select('id', 'reporting_authority')
            ->get();
        foreach ($getDealers as $key => $value) {
            $authorities = explode(",", $getDealers[$key]->reporting_authority);
            if (in_array($user_id, $authorities)) {
                $dealers[] = $getDealers[$key]->id;
            }
        }
        $regn_no = $request->regn_no;
        if (@$request->job_type) {
            if ($request->job_type == 1) {
                $job_type = 1;
                $type = $request->job_type;
            } elseif ($request->job_type == 2) {
                $job_type = 2;
                $type = $request->job_type;
            } elseif ($request->job_type == 3) {
                $job_type = 3;
                $type = $request->job_type;
            } elseif ($request->job_type == 4) {
                $job_type = 4;
                $type = $request->job_type;
            } elseif ($request->job_type == 5) {
                $job_type = 5;
                $type = $request->job_type;
            }
        }

        $result = DB::table('jobs as j')
            ->whereIn('dealer_id', $dealers)
            ->select('j.*')
            ->where(function ($query) use ($search) {
                if (!empty($search)) {
                    if (isset($search)) {
                        if (!empty(trim($search))) {
                            $query->where('j.user_id', '=', $search);
                        }
                    }
                }
            })
            ->where(function ($query) use ($regn_no) {
                if (!empty($regn_no)) {
                    if (isset($regn_no)) {
                        if (!empty(trim($regn_no))) {
                            $query->where('j.regn_no', 'LIKE', '%' . $regn_no);
                        }
                    }
                }
            });
        if ($request->job_type == 1) {
            $result = $result->where(function ($query) use ($job_type) {
                $query->where('j.foc_options', 1);
            });
        } elseif ($request->job_type == 2) {
            $result = $result->where(function ($query) use ($job_type) {
                $query->where('j.foc_options', 2);
            });
        } elseif ($request->job_type == 3) {
            $result = $result->where(function ($query) use ($job_type) {
                $query->where('j.foc_options', 3);
            });
        } elseif ($request->job_type == 4) {
            $result = $result->where(function ($query) use ($job_type) {
                $query->where('j.foc_options', 4);
            });
        } elseif ($request->job_type == 5) {
            $result = $result->where(function ($query) use ($job_type) {
                $query->where('j.foc_options', 5);
            });
        }
        $result = $result->where(['delete_job' => 1])
            ->orderBy('j.job_date', 'DESC')
            ->paginate(10);

        $supervisors = DB::table('jobs_treatment as jt')
            ->join('jobs as j', 'j.id', '=', 'jt.job_id')
            ->join('users as u', 'u.id', '=', 'j.user_id')
            ->select('u.name', 'j.user_id as id')
            ->where(['jt.delete_job' => 1, 'j.user_id' => $user_id])
            ->groupBy('j.user_id')
            ->orderBy('u.name', 'ASC')
            ->get();

        if (request()->has('page')) {
            Session::put('job_url', url()->full());
        }
        return view('rsm.jobs', [
            'result' => $result->appends(Input::except('page')),
            'supervisors' => $supervisors,
            'dealers' => $dealers,
            'regn_no' => @$regn_no,
            'job_type' => @$type,
            'oldSupervisor' => @$search,
        ]);
    }

    // view add job page
    public function addJob()
    {
        $user_id = Auth::id();
        $getDealers = DB::table('users')
            ->where(['role' => 2, 'status' => 1])
            ->select('id', 'reporting_authority')
            ->get();
        foreach ($getDealers as $key => $value) {
            $authorities = explode(",", $getDealers[$key]->reporting_authority);
            if (in_array($user_id, $authorities)) {
                $dealers[] = $getDealers[$key]->id;
            }
        }
        return view('rsm.addJob', [
            'dealers' => $dealers,
        ]);
    }

    // save new job
    public function insertJob(Request $request)
    {
        $post = $request->all();
        $actual_price = 0;
        $difference_price = 0;
        $customer_price = 0;
        $dealer_price = 0;
        $incentive = 0;
        $this->validate(
            $request,
            [
                'dealer_id' => 'required',
                'treatment_id' => 'required',
                'advisor_id' => 'required',
                'model_id' => 'required',
                'job_date' => 'required|date',
                'job_card_no' => 'required',
                'bill_no' => 'required',
                'regn_no' => 'required',
            ],
            [
                'model_id.required' => 'Please select model',
                'advisor_id.required' => 'Please select advisor',
                'treatment_id.required' => 'Please select treatment',
                'dealer_id.required' => 'Please select dealer',
                'job_date.required' => 'Please select job date',
                'job_card_no.required' => 'Please enter job card no',
                'bill_no.required' => 'Please enter bill no',
                'regn_no.required' => 'Please enter registration no',
            ]
        );
        $treatment_id = array();
        $treatment_data = array();
        $i = $hvt_value = 0;
        foreach ($post['treatment_id'] as $value) {
            $data1 = DB::table('treatments')->where('id', $value)->first();
            $data1->job_type = $post['job_type'][$key];
            $data1->actualPrice = $post['actualPrice'][$key];
            $data1->difference = $post['difference'][$key];
            $treatment_data[] = $data1;
            if ($data1->job_type == '5') {
                $actual_price = $actual_price + $data1->actualPrice;
                $difference_price = $difference_price + $data1->difference;
            } else {
                $actual_price = 0;
                $difference_price = 0;
            }
            $customer_price = $customer_price + $data1->customer_price;
            $dealer_price = $dealer_price + $data1->dealer_price;
            $incentive = $incentive + $data1->incentive;
            if ($data1->treatment_type == 1) {
                $i++;
                // $hvt_value = $hvt_value + $data1->customer_price;
                if ($data1->job_type == '5') {
                    $hvt_value = $hvt_value + $data1->actualPrice;
                } else {
                    $hvt_value = 0;
                }
            }
            $treat_id['id'] = $data1->id;
            $treatment_id[] = $treat_id;
        }
        $job_card_no = $request->job_card_no . '-W';
        // $checkCardNo = DB::table('jobs')
        //      ->where('job_card_no',$request->job_card_no)
        //      ->where('dealer_id',$request->dealer_id)
        //                     ->where('delete_job',1)
        //      ->get();
        // if(!empty($request->bill_no)){
        //   $checkBillNo = DB::table('jobs')->where('delete_job',1)->where('bill_no',$request->bill_no)->get();
        // }else{
        //   $checkBillNo = array();
        // }
        // if(!empty($request->regn_no)){
        //    $checkRegnNo = DB::table('jobs')->where('delete_job',1)->where('regn_no',$request->regn_no)->where('job_date',$request->job_date)->get();
        // }else{
        //   $checkRegnNo = array();
        // }
        // $error = array();
        // if(count($checkCardNo) > 0)
        // {
        // $error[] = 'This Job card no. has already added.';
        // }
        // if(count($checkBillNo) > 0)
        // {
        // $error[] = 'This Bill no. has already added.';
        // }
        // if(count($checkRegnNo) > 0)
        // {
        // $error[] = 'This Regn no. has already added.';
        // }
        // if(count($error) > 0)
        // {                  
        //    Session::flash('addComErrmsg', $error);
        //    Session::flash('alert-class', 'alert-danger');
        //     return redirect('/rsm/addJob')->with('error',$error);
        // }else{
        $data = array(
            'user_id' => Auth::user()->id,
            'job_date' => $request->job_date,
            'job_card_no' => $job_card_no,
            'bill_no' => $request->bill_no,
            'regn_no' => trim($request->regn_no),
            'remarks' => $request->remarks,
            'dealer_id' => $request->dealer_id,
            'model_id' => $request->model_id,
            'advisor_id' => $request->advisor_id,
            'department_id' => getDealerDepartment($request->advisor_id),
            'remarks' => $request->remark,
            'treatments' => json_encode($treatment_data),
            'treatment_total' => count($request->treatment_id),
            'hvt_total' => $i,
            'hvt_value' => $hvt_value,
            'vas_total' => count($request->treatment_id),
            'vas_value' => $customer_price,
            'customer_price' => $customer_price,
            'actual_price' => $actual_price,
            'difference_price' => $difference_price,
            'dealer_price' => $dealer_price,
            'incentive' => $incentive,
            // 'foc_options' => $request->option,

        );
        // if (@$request->option) {
        //     $data['foc_options'] = $request->option;
        // }else{
        //    $data['foc_options'] = 5; 
        // } 
        $result = DB::table('jobs')->insert($data);
        $id = DB::getPdo()->lastInsertId();
        foreach ($treatment_id as $value) {
            $data = array(
                'job_id' => $id,
                'treatment_id' => $value['id'],
            );
            DB::table('jobs_treatment')->insert($data);
            // }
            Session::flash('success', 'Job added successfully!');
            return redirect('/rsm/jobs');
        }
    }

    // view edit job page
    public function editJob($id)
    {
        $user_id = Auth::id();
        $result = DB::table('jobs')->where('id', $id)->first();
        $selectTreatment = array();
        foreach (json_decode($result->treatments) as $value) {
            $select['id'] = $value->id;
            $select['treatment'] = $value->treatment;
            $select['customer_price'] = $value->customer_price;
            $select['dealer_price'] = $value->dealer_price;
            $select['incentive'] = $value->incentive;
            $select['job_type'] = @$value->job_type;
            $select['actualPrice'] = @$value->actualPrice;
            $select['difference'] = @$value->difference;
            $selectTreatment[] = $select;
        }
        $treatments = DB::table('treatments')->where('model_id', $result->model_id)->where('status', 1)->get();
        // $treatment=array();
        // foreach ($treatments as $value) {
        //     $tr['id']=$value->id;
        //     $tr['treatment']=$value->treatment;
        //     $treatment[]=$tr;
        // }
        //dd($treatments);
        $template = DB::table('dealer_templates')->where('dealer_id', $result->dealer_id)->first();
        if (!empty($template)) {
            $models = DB::table('treatments')
                ->select('model_id')
                ->where('temp_id', $template->template_id)
                ->groupBy('model_id')
                ->get();

            if (!empty($models)) {
                $model = array();
                foreach ($models as $val) {
                    $model[] = $val->model_id;
                }
                $result_models = DB::table('models')
                    ->select('id', 'model_name')
                    ->whereIn('id', $model)
                    ->get();
            } else {
                $result_models = "";
            }
        } else {
            $result_models = "";
        }

        $advisors = DB::table('advisors')->select('id', 'name')->where('dealer_id', $result->dealer_id)->where('status', 1)->get();

        $getDealers = DB::table('users')
            ->where(['role' => 2, 'status' => 1])
            ->select('id', 'reporting_authority')
            ->get();
        foreach ($getDealers as $key => $value) {
            $authorities = explode(",", $getDealers[$key]->reporting_authority);
            if (in_array($user_id, $authorities)) {
                $dealers[] = $getDealers[$key]->id;
            }
        }

        return view('rsm.editJob', [
            'result' => $result,
            'dealers' => $dealers,
            'treatments' => $treatments,
            'result_models' => $result_models,
            'advisors' => $advisors,
            'selectTreatment' => $selectTreatment,
        ]);
    }

    // update existing job
    public function updateJob(Request $request)
    {
        $post = $request->all();
        $job_id  =  $request->id;
        $actual_price = 0;
        $difference_price = 0;
        $customer_price = 0;
        $dealer_price = 0;
        $incentive = 0;
        $this->validate(
            $request,
            [
                'dealer_id' => 'required',
                'treatment_id' => 'required',
                'advisor_id' => 'required',
                'model_id' => 'required',
                'job_date' => 'required|date',
                'job_card_no' => 'required',
                'bill_no' => 'required',
                'regn_no' => 'required',
            ],
            [
                'model_id.required' => 'Please select model',
                'advisor_id.required' => 'Please select advisor',
                'treatment_id.required' => 'Please select treatment',
                'dealer_id.required' => 'Please select dealer',
                'job_date.required' => 'Please select job date',
                'job_card_no.required' => 'Please enter job card no',
                'bill_no.required' => 'Please enter bill no',
                'regn_no.required' => 'Please enter registration no',
            ]
        );
        $selectedTreatments = array();
        for ($i = 0; $i < count($post['treatment_id']); $i++) {
            $data['id'] = $post['treatment_id'][$i];
            $data['dealer_price'] = $post['dealer'][$i];
            $data['customer_price'] = $post['customer'][$i];
            $data['incentive'] = $post['incentive'][$i];
            $data['job_type'] = $post['job_type'][$i];
            $data['actualPrice'] = @$post['actualPrice'][$i];
            $data['difference'] = $post['difference'][$i];
            $selectedTreatments[] = $data;
        }
        $treatment_id = array();
        $treatment_data = array();
        $i = $hvt_value = 0;
        foreach ($selectedTreatments as $value) {
            $data1 = DB::table('treatments')->where('id', $value['id'])->first();
            if ($value['job_type'] == 5) {
                $actual_price = $actual_price + $value['actualPrice'];
                $difference_price = $difference_price + $value['difference'];
            } else {
                $actual_price = $actual_price + 0;
                $difference_price = $difference_price + 0;
            }
            $customer_price = $customer_price + $value['customer_price'];
            $dealer_price = $dealer_price + $value['dealer_price'];
            $incentive = $incentive + $value['incentive'];
            if ($data1->treatment_type == 1) {
                $i++;
                // $hvt_value = $hvt_value + $value['customer_price'];
                if ($value['job_type'] == 5) {
                    $hvt_value = $hvt_value + $value['actualPrice'];
                } else {
                    $hvt_value = 0;
                }
            }
            $treat_id['id'] = $value['id'];
            $treat_id['treatment'] = $data1->treatment;
            $treat_id['treatment_type'] = $data1->treatment_type;
            $treat_id['customer_price'] = $value['customer_price'];
            $treat_id['dealer_price'] = $value['dealer_price'];
            $treat_id['incentive'] = $value['incentive'];
            $treat_id['job_type'] = $value['job_type'];
            $treat_id['actualPrice'] = $value['actualPrice'];
            $treat_id['difference'] = $value['difference'];
            $treat_id['labour_code'] = $data1->labour_code;
            $treatment_data[] = $treat_id;
            $treatment_id[] = $treat_id['id'];
        }
        // $checkCardNo = DB::table('jobs')->where('job_card_no',$request->job_card_no)->where('dealer_id',$request->dealer_id)->where('id','!=',$job_id)->where('delete_job',1)->get();
        // if(!empty($request->bill_no)){
        //           $checkBillNo = DB::table('jobs')->where('delete_job',1)->where('bill_no',$request->bill_no)->where('id','!=',$job_id)->get();
        //         }else{
        //           $checkBillNo = array();
        //         }
        //         if(!empty($request->regn_no)){
        //            $checkRegnNo = DB::table('jobs')->where('delete_job',1)->where('regn_no',$request->regn_no)->where('id','!=',$job_id)->where('job_date',$request->job_date)->get();
        //         }else{
        //           $checkRegnNo = array();
        //         }
        // $error = array();
        // if(count($checkCardNo) > 0)
        // {
        // $error[] = 'This Job card no. has already added.';
        // }
        // if(count($checkBillNo)>0)
        // {
        // $error[] = 'This Bill no. has already added.';
        // }
        // if(count($checkRegnNo)>0)
        // {
        // $error[] = 'This Regn no. has already added.';
        // }
        // if(count($error) > 0)
        // {                  
        //          Session::flash('addComErrmsg', $error);
        //          Session::flash('alert-class', 'alert-danger');
        //        return back()->with('error',$error);
        // }else{
        $data = array(
            'job_date' => $request->job_date,
            'job_card_no' => $request->job_card_no,
            'bill_no' => $request->bill_no,
            'regn_no' => trim($request->regn_no),
            'remarks' => $request->remarks,
            'dealer_id' => $request->dealer_id,
            'model_id' => $request->model_id,
            'advisor_id' => $request->advisor_id,
            'department_id' => getDealerDepartment($request->advisor_id),
            'remarks' => $request->remark,
            'treatments' => json_encode($treatment_data),
            'treatment_total' => count($treatment_id),
            'hvt_total' => $i,
            'hvt_value' => $hvt_value,
            'vas_total' => count($treatment_id),
            'vas_value' => $customer_price,
            'customer_price' => $customer_price,
            'actual_price' => $actual_price,
            'difference_price' => $difference_price,
            'dealer_price' => $dealer_price,
            'incentive' => $incentive,
            'last_updated' => getCurrentTimestamp(),
            // 'foc_options' => $request->option,
        );
        $result = DB::table('jobs')->where('id', $job_id)->update($data);
        DB::table('jobs_treatment')->where('job_id', $job_id)->delete();
        foreach ($treatment_id as $value) {
            $data = array(
                'job_id' => $job_id,
                'treatment_id' => $value,
            );
            DB::table('jobs_treatment')->insert($data);
        }
        Session::flash('success', 'Job Updated successfully!');
        if (Session::has('job_url')) {
            return redirect(Session::get('job_url'));
        } else {
            return redirect('/rsm/jobs');
        }
    }

    //Job delete
    public function statusJob($status, $job_id)
    {
        if (@$status) {
            if ($status == "delete") {
                $udata['delete_job'] = 0;
                $udata['delete_at'] = getCurrentTimestamp();
                DB::table('jobs')->where('id', $job_id)->update($udata);
                DB::table('jobs_treatment')->where('job_id', $job_id)->update($udata);
                Session::flash('success', 'Job deleted successfully!');
                return redirect('/rsm/jobs');
            }
        } else {
            Session::flash('error', 'Something wrong!');
            return redirect('/rsm/jobs');
        }
    }

    // Delete multiple jobs
    public function deleteJobs(Request $request)
    {
        $post = $request->all();
        if (!empty($post['selectedId'])) {
            $ids = explode(',', $post['selectedId']);
            foreach ($ids as $key => $value) {
                $udata['delete_job'] = 0;
                $udata['delete_at'] = getCurrentTimestamp();
                DB::table('jobs')->where('id', $value)->update($udata);
                DB::table('jobs_treatment')->where('job_id', $value)->update($udata);
            }
            Session::flash('success', 'Jobs deleted successfully!');
            return redirect('/rsm/jobs');
        } else {
            Session::flash('error', 'Please select job!');
            return redirect('/rsm/jobs');
        }
    }

    public function jobsTreatmentList(Request $request)
    {
        $search = $request->search;
        $user_id = Auth::id();

        $getDealers = DB::table('users')
            ->where(['role' => 2, 'status' => 1])
            ->select('id', 'reporting_authority')
            ->get();
        foreach ($getDealers as $key => $value) {
            $authorities = explode(",", $getDealers[$key]->reporting_authority);
            if (in_array($user_id, $authorities)) {
                $dealers[] = $getDealers[$key]->id;
            }
        }
        if (@$request->job_type) {
            if ($request->job_type == 1) {
                $job_type = 1;
                $type = $request->job_type;
            } elseif ($request->job_type == 2) {
                $job_type = 2;
                $type = $request->job_type;
            } elseif ($request->job_type == 3) {
                $job_type = 3;
                $type = $request->job_type;
            } elseif ($request->job_type == 4) {
                $job_type = 4;
                $type = $request->job_type;
            } elseif ($request->job_type == 5) {
                $job_type = 5;
                $type = $request->job_type;
            }
        }

        $result = DB::table('jobs as j')
            ->select('j.*', 'j.id as job_id')
            ->where('j.delete_job', 1)
            ->whereIn('j.dealer_id', $dealers)
            ->orderBy('j.job_date', 'DESC')
            ->get();

        $jobs_treatments = array();
        foreach ($result as $k => $v) {
            foreach ($data = json_decode($v->treatments) as $key => $value) {
                $data[$key]->job_id = $v->job_id;
                $data[$key]->dealer_id = $v->dealer_id;
                $data[$key]->job_date = $v->job_date;
                $data[$key]->job_card_no = $v->job_card_no;
                $data[$key]->regn_no = $v->regn_no;
                $data[$key]->bill_no = $v->bill_no;
                $jobs_treatments[] = $value;
            }
        }

        $data = array();
        if (!empty($search) && !empty($request->job_type)) {
            foreach ($jobs_treatments as $key => $value) {
                if ($search == @$value->job_card_no && $request->job_type == @$value->job_type) {
                    $data[] = $value;
                } elseif ($search == @$value->bill_no && $request->job_type == @$value->job_type) {
                    $data[] = $value;
                } elseif ($search == @$value->regn_no && $request->job_type == @$value->job_type) {
                    $data[] = $value;
                }
            }
            $jobs_treatments = $data;
        } elseif (!empty($search) && empty($request->job_type)) {
            foreach ($jobs_treatments as $key => $value) {
                if ($search == @$value->job_card_no) {
                    $data[] = $value;
                } elseif ($search == @$value->bill_no) {
                    $data[] = $value;
                } elseif ($search == @$value->regn_no) {
                    $data[] = $value;
                }
            }
            $jobs_treatments = $data;
        } elseif (empty($search) && !empty($request->job_type)) {
            foreach ($jobs_treatments as $key => $value) {
                if ($request->job_type == @$value->job_type) {
                    $data[] = $value;
                }
            }
            $jobs_treatments = $data;
        }
        // $jobs_treatments = $this->paginate($jobs_treatments);
        if (request()->has('page')) {
            Session::put('job_url', url()->full());
        }
        return view('rsm.jobsTreatmentList', [
            'result' => $jobs_treatments,
            'dealers' => $dealers,
            'regn_no' => @$regn_no,
            'job_type' => @$type,
            'search' => @$search,
        ]);
    }

    // Add Service load from dashboard
    public function addServiceLoad(Request $request)
    {
        $post = $request->all();
        $data = array(
            'user_id' => Auth::user()->id,
            'dealer_id' => $post['dealer_id'],
            'total_jobs' => $post['total_jobs'],
            'job_added_date' => $post['service_date'],
        );
        DB::table('jobs_by_date')->insert($data);
        Session::flash('success', 'RO added successfully!');
        return redirect('/rsm/jobs');
    }

    // Attendance 
    public function attendance(Request $request)
    {
        $user_id = Auth::id();
        $search = $request->all();
        // $dealers = User::where('role',2)->select('id','name')->where('status',1)->orderBy('name','ASC')->get();
        $getDealers = DB::table('users')
            ->where(['role' => 2, 'status' => 1])
            ->select('id', 'reporting_authority')
            ->get();
        foreach ($getDealers as $key => $value) {
            $authorities = explode(",", $getDealers[$key]->reporting_authority);
            if (in_array($user_id, $authorities)) {
                $dealers[] = $getDealers[$key]->id;
            }
        }
        $getStaff = User::join('staff_detail', 'users.id', '=', 'staff_detail.user_id')
            // ->join('emp_hierarchy', 'emp_hierarchy.user_id', '=', 'users.id')
            ->select('*', 'users.id as user_id')
            ->whereIn('role', [3, 4])
            ->whereIn('dealer_id', $dealers)
            ->where('reporting_authority', $user_id)
            ->get();
        $staff = array();
        foreach ($getStaff as $k => $val) {
            $staff[] = $getStaff[$k]->user_id;
        }

        if (count($staff) > 0) {
            $attendance = DB::table('attendance')
                ->whereIn('user_id', $staff)
                ->where(function ($query) use ($search) {
                    if (!empty($search)) {
                        if (isset($search['dealer'])) {
                            if (!empty(trim($search['dealer']))) {
                                $query->where('dealer_id', '=', $search['dealer']);
                            }
                        }
                        if (isset($search['start_date']) && isset($search['end_date'])) {
                            if (!empty(trim($search['start_date']))) {
                                $query->whereDate('date', '>=', $search['start_date']);
                                $query->whereDate('date', '<=', $search['end_date']);
                            }
                        } elseif (isset($search['end_date'])) {
                            if (!empty(trim($search['end_date']))) {
                                $query->whereDate('date', '<=', $search['end_date']);
                            }
                        } elseif (isset($search['start_date'])) {
                            if (!empty(trim($search['start_date']))) {
                                $query->whereDate('date', '>=', $search['start_date']);
                            }
                        } else {
                            $query->whereDate('date', date("Y-m-d"));
                        }
                    } else {
                        $query->whereDate('date', date("Y-m-d"));
                    }
                })->orderBy('user_id', 'ASC')->orderBy('date', 'ASC')->get();
            if (@$search['download']) {
                return Excel::create('Attendance_' . date("d-M-Y"), function ($excel) use ($attendance, $search) {
                    $excel->sheet('sheet', function ($sheet) use ($attendance, $search) {
                        $result = array();
                        foreach ($attendance as $key => $value) {
                            $data['S. No.'] = $key + 1;
                            $data['User'] = get_name($value->user_id);
                            $data['Emp Code'] = get_emp_code($value->user_id);
                            $data['Dealer'] = get_name($value->dealer_id);
                            @$mydata1 =
                                month_hours_date($value->user_id, $search['start_date'], $search['end_date']);

                            $data['Total Hours This Month'] = @$mydata1['hours'] . ' hours ' . @$mydata1['minutes'] . ' minutes';

                            $result[] = $data;
                        }
                        $sheet->fromArray($result);
                    });
                })->export('xlsx');
            }
        } else {
            $attendance = [];
        }

        return view('rsm.attendance', [
            'result' => $attendance,
            'dealers' => $dealers,
            'dealer' => @$search['dealer'],
            'start_date' => @$search['start_date'],
            'end_date' => @$search['end_date'],
        ]);
    }

    // View Daily report by dealer and advisor
    public function dailyReport(Request $request)
    {
        $search = $request->all();
        $user_id = Auth::id();
        $today = date('Y-m-d');
        $first_day = date('Y-m-01');
        if (!empty($search['report_type'])) {
            $type = $search['report_type'];
        } else {
            $type = 'dealer';
        }
        if (!empty($search['dealer'])) {
            $dealer_ids = DB::table('users')->select('id', 'name', 'reporting_authority')->where(['role' => 2, 'status' => 1])->orderBy('id', 'DESC')->get();
            $dealers = array();
            $d_ids = array();
            foreach ($dealer_ids as $i => $j) {
                $report_ids = explode(",", $j->reporting_authority);
                if (in_array($user_id, $report_ids)) {
                    $dealers[] = $dealer_ids[$i];
                    $d_ids[] = $dealer_ids[$i]->id;
                }
            }
            $oems = User::where('status', 1)->where('id', $search['dealer'])->select('oem_id')->groupBy('oem_id')->get();
            $groups = User::where('status', 1)->where('id', $search['dealer'])->select('group_id')->groupBy('group_id')->get();
            $allAdvisors = DB::table('advisors')->select('*', 'id as advisor_id')->where('status', 1)->where('dealer_id', $search['dealer'])->orderBy('dealer_id', 'ASC')->get();
            $departments = DB::table('dealer_department')->where('status', 1)->get();
        } else {
            $dealer_ids = DB::table('users')->select('id', 'name', 'reporting_authority')->where(['role' => 2, 'status' => 1])->orderBy('id', 'DESC')->get();
            $dealers = array();
            $d_ids = array();
            foreach ($dealer_ids as $i => $j) {
                $report_ids = explode(",", $j->reporting_authority);
                if (in_array($user_id, $report_ids)) {
                    $dealers[] = $dealer_ids[$i];
                    $d_ids[] = $dealer_ids[$i]->id;
                }
            }
            $oems = DB::table('oems')->where('status', 1)->get();
            $groups = DB::table('groups')->where('status', 1)->get();
            $allAdvisors = DB::table('advisors')->select('*', 'id as advisor_id')->whereIn('dealer_id', $d_ids)->where('status', 1)->orderBy('dealer_id', 'ASC')->get();
            $departments = DB::table('dealer_department')->where('status', 1)->get();
        }
        /************************************ Dealer Wise Report Start *************************/
        $result = DB::table('jobs as j')
            ->select('j.*')
            ->where(function ($query) use ($search, $d_ids) {
                if (!empty($search)) {
                    // if(empty(trim($search['dealer']))){
                    //     $query->whereIn('j.dealer_id',$d_ids);
                    // }
                    if (isset($search['dealer'])) {
                        if (!empty(trim($search['dealer']))) {
                            $query->where('j.dealer_id', '=', $search['dealer']);
                        }
                    }
                    // if(isset($search['advisor'])){
                    //     if(!empty(trim($search['advisor']))){
                    //         $query->where('j.advisor_id','=',$search['advisor']);
                    //     }
                    // }
                    if (isset($search['department'])) {
                        if (!empty(trim($search['department']))) {
                            $query->where('j.department_id', '=', $search['department']);
                        }
                    }
                    if (isset($search['from']) && isset($search['to'])) {
                        if (!empty(trim($search['from']))) {
                            $query->whereDate('j.job_date', '>=', $search['from']);
                            $query->whereDate('j.job_date', '<=', $search['to']);
                        }
                    } elseif (isset($search['to'])) {
                        if (!empty(trim($search['to']))) {
                            $query->whereDate('job_date', '<=', $search['to']);
                        }
                    } elseif (isset($search['from'])) {
                        if (!empty(trim($search['from']))) {
                            $query->whereDate('j.job_date', '>=', $search['from']);
                        }
                    } elseif (isset($search['month1'])) {
                        if (!empty(trim($search['month1']))) {
                            $exp = explode('-', $search['month1']);
                            $query->whereMonth('j.job_date', $exp[1]);
                            $query->whereYear('j.job_date', $exp[0]);
                        }
                    } else {
                        $query->whereDate('j.job_date', '=', date("Y-m-d"));
                    }
                } else {
                    $query->whereDate('j.job_date', '=', date("Y-m-d"));
                }
            })
            ->where('delete_job', 1)
            ->whereIn('j.dealer_id', $d_ids)
            ->orderBy('j.job_date', 'ASC')
            ->get();
        $array = array();
        $result1 = array();
        $total_incentive = 0;
        foreach ($result as $key => $value) {
            $array['dealer_id'] = $value->dealer_id;
            $array['job_date'] = $value->job_date;
            $array['job_card_no'] = $value->job_card_no;
            $array['bill_no'] = $value->bill_no;
            $array['regn_no'] = $value->regn_no;
            $array['advisor_id'] = $value->advisor_id;
            $array['model_id'] = $value->model_id;
            $array['remarks'] = $value->remarks;
            // $array['foc_options'] = $value->foc_options;
            $total_incentive = $total_incentive + $value->incentive;
            $decoded = json_decode($value->treatments);

            foreach ($decoded as $val) {
                $array['labour_code'] = $val->labour_code;
                $array['job_type'] = @$val->job_type;
                $array['treatment_name'] = $val->treatment;
                $array['customer_price'] = $val->customer_price;
                $array['actual_price'] = @$val->actualPrice;
                $array['difference_price'] = @$val->difference;
                $array['dealer_price'] = @$val->dealer_price;
                $array['incentive'] = $val->incentive;
                $result1[] = $array;
            }
        }

        /************************************ Dealer Wise Report End *************************/

        /************************************ Advisor Wise Report Start *************************/
        $data = DB::table('jobs')
            ->select(DB::raw('group_concat(id) as job_id, SUM(customer_price) as vas_customer_price, SUM(actual_price) as vas_actual_price, SUM(difference_price) as vas_difference, SUM(hvt_value) as hvt_customer_price,SUM(hvt_value) as hvt_actual_price, SUM(incentive) as vas_incentive, advisor_id, job_date'))
            ->whereIn('dealer_id', $d_ids)
            ->where(function ($query) use ($search) {
                if (!empty($search)) {
                    if (isset($search['dealer'])) {
                        if (!empty(trim($search['dealer']))) {
                            $query->where('dealer_id', '=', $search['dealer']);
                        }
                    }
                    // if(isset($search['advisor'])){
                    //     if(!empty(trim($search['advisor']))){
                    //         $query->where('advisor_id','=',$search['advisor']);
                    //     }
                    // }
                    if (isset($search['department'])) {
                        if (!empty(trim($search['department']))) {
                            $query->where('department_id', '=', $search['department']);
                        }
                    }
                    if (isset($search['from']) && isset($search['to'])) {
                        if (!empty(trim($search['from']))) {
                            $query->whereDate('job_date', '>=', $search['from']);
                            $query->whereDate('job_date', '<=', $search['to']);
                        }
                    } elseif (isset($search['to'])) {
                        if (!empty(trim($search['to']))) {
                            $query->whereDate('job_date', '<=', $search['to']);
                        }
                    } elseif (isset($search['from'])) {
                        if (!empty(trim($search['from']))) {
                            $query->whereDate('job_date', '>=', $search['from']);
                        }
                    } elseif (isset($search['month1'])) {
                        if (!empty(trim($search['month1']))) {
                            $exp = explode('-', $search['month1']);
                            $query->whereMonth('job_date', $exp[1]);
                            $query->whereYear('job_date', $exp[0]);
                        }
                    } else {
                        $query->whereDate('job_date', '=', date("Y-m-d"));
                    }
                } else {
                    $query->whereDate('job_date', '=', date("Y-m-d"));
                }
            })
            ->where('delete_job', 1)
            ->groupBy('advisor_id')
            ->get();
        $advisors = array();
        $i = $mtd_total = 0;
        if (count($data) > 0) {
            foreach ($data as $value) {
                $hvt_incentive = 0;
                $decoded_jobs = explode(',', $value->job_id);
                foreach ($decoded_jobs as $key => $val) {
                    $customer_price = $incentive = 0;
                    $treat = DB::table('jobs')->select('treatments')->where('id', $val)->first();
                    $decoded_treatments = json_decode(@$treat->treatments);
                    if (!empty($decoded_treatments)) {
                        foreach ($decoded_treatments as $key => $val1) {
                            if (@$val1->job_type == 5) {
                                $customer_price = $customer_price + $val1->customer_price;
                                $incentive = $incentive + $val1->incentive;
                            } else {
                                $customer_price = $customer_price + 0;
                                $incentive = $incentive + 0;
                            }
                            if ($val1->treatment_type == 1) {
                                $hvt_incentive = $hvt_incentive + $val1->incentive;
                            }
                        }
                    }
                }
                $advisor['advisor_id'] = $value->advisor_id;
                // $advisor['vas_customer_price'] = $value->vas_customer_price;
                // $advisor['vas_incentive'] = $value->vas_incentive;
                $advisor['vas_customer_price'] = $customer_price;
                $advisor['vas_incentive'] = $incentive;
                $advisor['vas_actual_price'] = $value->vas_actual_price;
                $advisor['vas_difference'] = $value->vas_difference;
                $advisor['hvt_customer_price'] = $value->hvt_customer_price;
                $advisor['hvt_actual_price'] = $value->hvt_actual_price;
                $advisor['hvt_incentive'] = $hvt_incentive;
                $advisors[] = $advisor;

                @$total_service = DB::table('jobs_by_date')
                    ->select(DB::raw('SUM(total_jobs) as mtd_total'))
                    ->whereIn('dealer_id', $d_ids)
                    ->where(function ($query) use ($search, $first_day, $today, $value) {
                        if (!empty($search)) {
                            if (isset($search['dealer'])) {
                                if (!empty(trim($search['dealer']))) {
                                    $query->where('dealer_id', '=', $search['dealer']);
                                }
                            }
                            if (isset($search['from']) && isset($search['to'])) {
                                if (!empty(trim($search['from']))) {
                                    $query->whereDate('job_added_date', '>=', $search['from']);
                                    $query->whereDate('job_added_date', '<=', $search['to']);
                                }
                            } elseif (isset($search['to'])) {
                                if (!empty(trim($search['to']))) {
                                    $query->whereDate('job_added_date', '<=', $search['to']);
                                }
                            } elseif (isset($search['from'])) {
                                if (!empty(trim($search['from']))) {
                                    $query->whereDate('job_added_date', '>=', $search['from']);
                                }
                            } elseif (isset($search['month1'])) {
                                if (!empty(trim($search['month1']))) {
                                    $exp = explode('-', $search['month1']);
                                    $query->whereMonth('job_added_date', $exp[1]);
                                    $query->whereYear('job_added_date', $exp[0]);
                                }
                            }
                        } else {
                            $query->whereDate('job_added_date', '=', @$value->job_date);
                        }
                    })
                    ->first();

                //dd($total_service);

                @$total_jobs = DB::table('jobs')
                    ->select(DB::raw('SUM(vas_value) as mtd_vas_value,SUM(actual_price) as mtd_actual_value,SUM(vas_total) as mtd_vas_total, SUM(hvt_value) as mtd_hvt_value,SUM(hvt_total) as mtd_hvt_total'))
                    // ->where('foc_options',5)
                    ->whereIn('dealer_id', $d_ids)
                    ->where(function ($query) use ($search, $first_day, $today, $value) {
                        if (!empty($search)) {
                            if (isset($search['dealer'])) {
                                if (!empty(trim($search['dealer']))) {
                                    $query->where('dealer_id', '=', $search['dealer']);
                                }
                            }
                            // if(isset($search['advisor'])){
                            //     if(!empty(trim($search['advisor']))){
                            //         $query->where('advisor_id','=',$search['advisor']);
                            //     }
                            // }
                            if (isset($search['department'])) {
                                if (!empty(trim($search['department']))) {
                                    $query->where('department_id', '=', $search['department']);
                                }
                            }
                            if (isset($search['from']) && isset($search['to'])) {
                                if (!empty(trim($search['from']))) {
                                    $query->whereDate('job_date', '>=', $search['from']);
                                    $query->whereDate('job_date', '<=', $search['to']);
                                }
                            } elseif (isset($search['to'])) {
                                if (!empty(trim($search['to']))) {
                                    $query->whereDate('job_date', '<=', $search['to']);
                                }
                            } elseif (isset($search['from'])) {
                                if (!empty(trim($search['from']))) {
                                    $query->whereDate('job_date', '>=', $search['from']);
                                }
                            } elseif (isset($search['month1'])) {
                                if (!empty(trim($search['month1']))) {
                                    $exp = explode('-', $search['month1']);
                                    $query->whereMonth('job_date', $exp[1]);
                                    $query->whereYear('job_date', $exp[0]);
                                }
                            }
                        } else {
                            $query->whereDate('job_date', '=', @$value->job_date);
                        }
                    })
                    ->where('delete_job', 1)
                    ->first();
                $total_job_array = array(
                    'mtd_total' => @$total_service->mtd_total,
                    'mtd_vas_value' => @$total_jobs->mtd_vas_value,
                    'mtd_actual_value' => @$total_jobs->mtd_actual_value,
                    'mtd_vas_total' => @$total_jobs->mtd_vas_total,
                    'mtd_hvt_value' => @$total_jobs->mtd_hvt_value,
                    'mtd_hvt_total' => @$total_jobs->mtd_hvt_total,
                );
                $i++;
            }
        }

        /************************************ Advisor Wise Report End *************************/

        Session::put('oldReport', $type);
        return view('rsm.dailyReport', [
            'result' => $result1,
            'total_incentive' => $total_incentive,
            'advisors' => $advisors,
            'allAdvisors' => $allAdvisors,
            'oldAdvisor' => @$search['advisor'],
            'total_job_array' => @$total_job_array,
            'dealers' => $dealers,
            'oems' => $oems,
            'groups' => $groups,
            'oldFromDate' => @$search['from'],
            'oldToDate' => @$search['to'],
            'oldDealer' => @$search['dealer'],
            'oldSelectMonth' => @$search['month1'],
            'oldReport' => @$type,
            'oldOem' => @$search['oem'],
            'oldGroup' => @$search['group'],
            'departments' => $departments,
            'oldDepartment' => @$search['department'],
        ]);
    }


    // Download Report
    public function downloadReport(Request $request)
    {
        $user_id = Auth::id();
        $dealer_ids = DB::table('users')->select('id', 'name', 'reporting_authority')->where(['role' => 2, 'status' => 1])->orderBy('id', 'DESC')->get();
        $dealers = array();
        $d_ids = array();
        foreach ($dealer_ids as $i => $j) {
            $report_ids = explode(",", $j->reporting_authority);
            if (in_array($user_id, $report_ids)) {
                $dealers[] = $dealer_ids[$i];
                $d_ids[] = $dealer_ids[$i]->id;
            }
        }
        $search = $request->all();
        if ($search['report'] == 'dealer') {
            $result = DB::table('jobs as j')
                ->select('j.*')
                // ->where('foc_options',5)
                ->where(function ($query) use ($search, $d_ids) {
                    if (!empty($search)) {
                        // if(empty(trim($search['dealer1']))){
                        //     $query->whereIn('j.dealer_id',$d_ids);
                        // }
                        if (isset($search['dealer1'])) {
                            if (!empty(trim($search['dealer1']))) {
                                $query->where('j.dealer_id', '=', $search['dealer1']);
                            }
                        }
                        if (isset($search['department1'])) {
                            if (!empty(trim($search['department1']))) {
                                $query->where('j.department_id', '=', $search['department1']);
                            }
                        }
                        if (isset($search['from1']) && isset($search['to1'])) {
                            if (!empty(trim($search['from1'])) && !empty(trim($search['to1']))) {
                                $query->whereDate('j.job_date', '>=', $search['from1']);
                                $query->whereDate('j.job_date', '<=', $search['to1']);
                            }
                        } elseif (isset($search['to1'])) {
                            if (!empty(trim($search['to1']))) {
                                $query->whereDate('job_date', '<=', $search['to1']);
                            }
                        } elseif (isset($search['from1'])) {
                            if (!empty(trim($search['from1']))) {
                                $query->whereDate('j.job_date', '>=', $search['from1']);
                            }
                        } elseif (isset($search['month3'])) {
                            if (!empty(trim($search['month3']))) {
                                $exp = explode('-', $search['month3']);
                                $query->whereMonth('j.job_date', $exp[1]);
                                $query->whereYear('j.job_date', $exp[0]);
                            }
                        } else {
                            $query->whereDate('j.job_date', '=', date("Y-m-d"));
                        }
                    } else {
                        $query->whereDate('j.job_date', '=', date("Y-m-d"));
                    }
                })
                ->where('j.delete_job', 1)
                ->whereIn('j.dealer_id', $d_ids)
                ->orderBy('j.job_date', 'ASC')
                ->get();

            $array = array();
            $result1 = array();
            $customer_price = $actual_price = $difference_price = $dealer_price = $incentive = 0;
            return Excel::create('Dealer_' . date("d-M-Y"), function ($excel) use ($result, $customer_price, $dealer_price, $incentive, $actual_price, $difference_price) {
                $excel->sheet('sheet', function ($sheet) use ($result, $customer_price, $dealer_price, $incentive, $actual_price, $difference_price) {
                    foreach ($result as $key => $value) {
                        $decoded = json_decode($value->treatments);
                        foreach ($decoded as $val) {
                            if (@$val->job_type == 5) {
                                $customer_price = $customer_price + round($val->customer_price);
                                $dealer_price = $dealer_price + round($val->dealer_price);
                                $incentive = $incentive + round($val->incentive);
                                $actual_price = $actual_price + round(@$val->actualPrice);
                                $difference_price = $difference_price + round(@$val->difference);
                            }
                        }
                    }
                    $sheet->setBorder('P1:T2');
                    $sheet->cells('P1', function ($cells) {
                        $cells->setBackground('#FFFF00');
                    });
                    $sheet->cells('Q1', function ($cells) {
                        $cells->setBackground('#FFFF00');
                    });
                    $sheet->cells('R1', function ($cells) {
                        $cells->setBackground('#FFFF00');
                    });
                    $sheet->cells('S1', function ($cells) {
                        $cells->setBackground('#FFFF00');
                    });
                    $sheet->cells('T1', function ($cells) {
                        $cells->setBackground('#FFFF00');
                    });

                    $sheet->setCellValue('P1', 'Customer_Price');
                    $sheet->setCellValue('P2', $customer_price);
                    $sheet->setCellValue('Q1', 'Dealer_Price');
                    $sheet->setCellValue('Q2', $dealer_price);
                    $sheet->setCellValue('R1', 'Incentive');
                    $sheet->setCellValue('R2', $incentive);
                    $sheet->setCellValue('S1', 'Actual_Price');
                    $sheet->setCellValue('S2', $actual_price);
                    $sheet->setCellValue('T1', 'Difference');
                    $sheet->setCellValue('T2', $difference_price);
                    foreach ($result as $key => $value) {
                        $array['Job_Date'] = date("d-M-Y", strtotime($value->job_date));
                        $array['Job_Card_No'] = $value->job_card_no;
                        $array['Bill_No'] = $value->bill_no;
                        $array['Regn_No'] = $value->regn_no;
                        $array['Advisor'] = get_advisor_name($value->advisor_id);
                        $array['Model'] = get_model_name($value->model_id);
                        $decoded = json_decode($value->treatments);
                        foreach ($decoded as $val) {
                            $array['Labour_Code'] = $val->labour_code;
                            $array['Treatment'] = $val->treatment;
                            $array['Customer_Price'] = round($val->customer_price);
                            $array['Dealer_Price'] = round($val->dealer_price);
                            $array['Incentive'] = round($val->incentive);
                            $array['Actual_Price'] = round(@$val->actualPrice);
                            $array['Difference_Price'] = round(@$val->difference);
                            $array['Remark'] = $value->remarks;
                            $result1[] = $array;
                        }
                    }
                    $sheet->fromArray(@$result1);
                });
            })->export('xlsx');
            /************************************ Download Dealer Wise Report *************************/
        } elseif ($search['report'] == 'advisor') {
            /************************************ Download Advisor Wise Report ***********************/
            $data = DB::table('jobs')
                // ->where('foc_options',5)
                ->select(DB::raw('group_concat(id) as job_id, SUM(customer_price) as vas_customer_price, SUM(actual_price) as vas_actual_price, SUM(difference_price) as vas_difference, SUM(hvt_value) as hvt_customer_price,SUM(hvt_value) as hvt_actual_price,  SUM(incentive) as vas_incentive, advisor_id, job_date'))
                ->where(function ($query) use ($search) {
                    if (!empty($search)) {
                        if (isset($search['dealer2'])) {
                            if (!empty(trim($search['dealer2']))) {
                                $query->where('dealer_id', '=', $search['dealer2']);
                            }
                        }
                        if (isset($search['department2'])) {
                            if (!empty(trim($search['department2']))) {
                                $query->where('department_id', '=', $search['department2']);
                            }
                        }
                        if (isset($search['from2']) && isset($search['to2'])) {
                            if (!empty(trim($search['from2']))) {
                                $query->whereDate('job_date', '>=', $search['from2']);
                                $query->whereDate('job_date', '<=', $search['to2']);
                            }
                        } elseif (isset($search['to2'])) {
                            if (!empty(trim($search['to2']))) {
                                $query->whereDate('job_date', '<=', $search['to2']);
                            }
                        } elseif (isset($search['from2'])) {
                            if (!empty(trim($search['from2']))) {
                                $query->whereDate('job_date', '>=', $search['from2']);
                            }
                        } elseif (isset($search['month4'])) {
                            if (!empty(trim($search['month4']))) {
                                $exp = explode('-', $search['month4']);
                                $query->whereMonth('job_date', $exp[1]);
                                $query->whereYear('job_date', $exp[0]);
                            }
                        }
                    }
                })
                ->where('delete_job', 1)
                ->whereIn('dealer_id', $d_ids)
                ->groupBy('advisor_id')
                ->get();

            $advisors = array();
            $i = $mtd_total = 0;
            $total_job_array = array();
            if (count($data) > 0) {
                foreach ($data as $value) {
                    $hvt_incentive = 0;
                    $decoded_jobs = explode(',', $value->job_id);
                    foreach ($decoded_jobs as $key => $val) {
                        $customer_price = $incentive = 0;
                        $treat = DB::table('jobs')->select('treatments')->where('id', $val)->first();
                        $decoded_treatments = json_decode($treat->treatments);
                        foreach ($decoded_treatments as $key => $val1) {
                            if (@$val1->job_type == 5) {
                                $customer_price = $customer_price + $val1->customer_price;
                                $incentive = $incentive + $val1->incentive;
                            } else {
                                $customer_price = $customer_price + 0;
                                $incentive = $incentive + 0;
                            }
                            if ($val1->treatment_type == 1) {
                                $hvt_incentive = $hvt_incentive + $val1->incentive;
                            }
                        }
                    }
                    $advisor['advisor_id'] = $value->advisor_id;
                    // $advisor['vas_customer_price'] = round($value->vas_customer_price);
                    // $advisor['vas_incentive'] = round($value->vas_incentive);
                    $advisor['vas_customer_price'] = round($customer_price);
                    $advisor['vas_incentive'] = round($incentive);
                    $advisor['vas_actual_price'] = round($value->vas_actual_price);
                    $advisor['vas_difference'] = round($value->vas_difference);
                    $advisor['hvt_customer_price'] = round($value->hvt_customer_price);
                    $advisor['hvt_actual_price'] = round($value->hvt_actual_price);
                    $advisor['hvt_incentive'] = round($hvt_incentive);
                    $advisors[] = $advisor;
                    @$total_service = DB::table('jobs_by_date')
                        ->select(DB::raw('SUM(total_jobs) as mtd_total'))
                        ->whereIn('dealer_id', $d_ids)
                        ->where(function ($query) use ($search, $first_day, $today, $value) {
                            if (!empty($search)) {
                                if (isset($search['dealer2'])) {
                                    if (!empty(trim($search['dealer2']))) {
                                        $query->where('dealer_id', '=', $search['dealer2']);
                                    }
                                }
                                if (isset($search['from2']) && isset($search['to2'])) {
                                    if (!empty(trim($search['from2']))) {
                                        $query->whereDate('job_added_date', '>=', $search['from2']);
                                        $query->whereDate('job_added_date', '<=', $search['to2']);
                                    }
                                } elseif (isset($search['to2'])) {
                                    if (!empty(trim($search['to2']))) {
                                        $query->whereDate('job_added_date', '<=', $search['to2']);
                                    }
                                } elseif (isset($search['from2'])) {
                                    if (!empty(trim($search['from2']))) {
                                        $query->whereDate('job_added_date', '>=', $search['from2']);
                                    }
                                } elseif (isset($search['month4'])) {
                                    if (!empty(trim($search['month4']))) {
                                        $exp = explode('-', $search['month4']);
                                        $query->whereMonth('job_added_date', $exp[1]);
                                        $query->whereYear('job_added_date', $exp[0]);
                                    }
                                } else {
                                    //$query->whereDate('job_added_date','=',@$value->job_date);
                                }
                            } else {
                                $query->whereDate('job_added_date', '=', @$value->job_date);
                            }
                        })
                        ->first();
                    @$total_jobs = DB::table('jobs')
                        // ->where('foc_options',5)
                        ->whereIn('dealer_id', $d_ids)
                        ->select(DB::raw('SUM(vas_value) as mtd_vas_value,SUM(vas_total) as mtd_vas_total, SUM(hvt_value) as mtd_hvt_value,SUM(hvt_total) as mtd_hvt_total'))
                        ->where(function ($query) use ($search, $first_day, $today, $value) {
                            if (!empty($search)) {
                                if (isset($search['dealer2'])) {
                                    if (!empty(trim($search['dealer2']))) {
                                        $query->where('dealer_id', '=', $search['dealer2']);
                                    }
                                }
                                if (isset($search['department2'])) {
                                    if (!empty(trim($search['department2']))) {
                                        $query->where('department_id', '=', $search['department2']);
                                    }
                                }
                                if (isset($search['from2']) && isset($search['to2'])) {
                                    if (!empty(trim($search['from2']))) {
                                        $query->whereDate('job_date', '>=', $search['from2']);
                                        $query->whereDate('job_date', '<=', $search['to2']);
                                    }
                                } elseif (isset($search['to2'])) {
                                    if (!empty(trim($search['to2']))) {
                                        $query->whereDate('job_date', '<=', $search['to2']);
                                    }
                                } elseif (isset($search['from2'])) {
                                    if (!empty(trim($search['from2']))) {
                                        $query->whereDate('job_date', '>=', $search['from2']);
                                    }
                                } elseif (isset($search['month4'])) {
                                    if (!empty(trim($search['month4']))) {
                                        $exp = explode('-', $search['month4']);
                                        $query->whereMonth('job_date', $exp[1]);
                                        $query->whereYear('job_date', $exp[0]);
                                    }
                                } else {
                                    //$query->whereDate('job_date','=',@$value->job_date);
                                }
                            } else {
                                $query->whereDate('job_date', '=', @$value->job_date);
                            }
                        })
                        ->where('delete_job', 1)
                        ->first();
                    $total_job_array = array(
                        'mtd_total' => round(@$total_service->mtd_total),
                        'mtd_vas_value' => round(@$total_jobs->mtd_vas_value),
                        'mtd_actual_value' => round(@$total_jobs->mtd_actual_value),
                        'mtd_vas_total' => round(@$total_jobs->mtd_vas_total),
                        'mtd_hvt_value' => round(@$total_jobs->mtd_hvt_value),
                        'mtd_hvt_total' => round(@$total_jobs->mtd_hvt_total),
                    );
                    $i++;
                }
            }
            $finalAdvisor = array();
            $array = array();
            foreach ($advisors as $value) {
                $array['Advisor'] = get_advisor_name($value['advisor_id']);
                $array['Vas_Customer_Price'] = round($value['vas_customer_price']);
                $array['Vas_Actual_Price'] = round($value['vas_actual_price']);
                $array['Vas_incentive'] = round($value['vas_incentive']);
                $array['HVT_Customer_Price'] = round($value['hvt_customer_price']);
                $array['HVT_Actual_Price'] = round($value['hvt_actual_price']);
                $array['HVT_Incentive'] = round($value['hvt_incentive']);
                $finalAdvisor[] = $array;
            }
            return Excel::create('Advisor_' . date("d-M-Y"), function ($excel) use ($finalAdvisor, $total_job_array) {
                $excel->sheet('sheet', function ($sheet) use ($finalAdvisor, $total_job_array) {
                    $sheet->setBorder('H1:I10');
                    $sheet->setCellValue('H1', 'Monthly Treatments till Date');
                    $sheet->mergeCells("H1:I1");
                    $sheet->setCellValue('H2', 'RO');
                    $sheet->setCellValue('I2', @$total_job_array['mtd_total']);
                    $sheet->setCellValue('H3', 'VAS');
                    $sheet->mergeCells("H3:I3");
                    $sheet->setCellValue('H4', 'No of Trmt');
                    $sheet->setCellValue('I4', @$total_job_array['mtd_vas_total']);
                    $sheet->setCellValue('H5', 'Amount');
                    // $sheet->setCellValue('I5',$total_job_array['mtd_vas_value']);
                    $sheet->setCellValue('I5', @$total_job_array['mtd_actual_value']);
                    $sheet->setCellValue('H6', 'Value Per Treatment');
                    $sheet->setCellValue('I6', vas_in_percentage(@$total_job_array['mtd_actual_value'], @$total_job_array['mtd_vas_total']));
                    $sheet->setCellValue('H7', 'HVT');
                    $sheet->mergeCells("H7:I7");
                    $sheet->setCellValue('H8', 'No of Trmt');
                    $sheet->setCellValue('I8', $total_job_array['mtd_hvt_total']);
                    $sheet->setCellValue('H9', 'Amount');
                    $sheet->setCellValue('I9', $total_job_array['mtd_hvt_value']);
                    $sheet->setCellValue('H10', 'HVT %');
                    $sheet->setCellValue('I10', hvt_in_percentage(@$total_job_array['mtd_hvt_value'], @$total_job_array['mtd_vas_value']));
                    $sheet->fromArray(@$finalAdvisor);
                });
            })->export('xlsx');
        }
    }

    // View Mis report
    public function misReport(Request $request)
    {
        $search = $request->all();
        $user_id = Auth::id();
        $today = date('Y-m-d');
        $first_day = date('Y-m-01');

        $dealer_ids = DB::table('users')->select('id', 'name', 'reporting_authority')->where(['role' => 2, 'status' => 1])->orderBy('id', 'DESC')->get();
        $dealers = array();
        $d_ids = array();
        foreach ($dealer_ids as $i => $j) {
            $report_ids = explode(",", $j->reporting_authority);
            if (in_array($user_id, $report_ids)) {
                $dealers[] = $dealer_ids[$i];
                $d_ids[] = $dealer_ids[$i]->id;
            }
        }

        /************************************ MIS Report Start *************************/
        // $users = DB::table('users')
        //           ->select('id')
        //           ->where('users.role',2)
        //           ->orderBy('users.name','ASC')
        //           ->get();

        $oems = User::where('status', 1)->whereIn('id', $d_ids)->select('oem_id')->groupBy('oem_id')->get();

        $groups = User::where('status', 1)->whereIn('id', $d_ids)->select('group_id')->groupBy('group_id')->get();

        $mist = array();

        foreach ($dealers as $key => $value) {
            $mis = DB::table('jobs')
                // ->select(DB::raw('jobs.id as job_id,SUM(jobs.treatment_total) as mtd_total,SUM(jobs.customer_price) as customer_price,SUM(jobs.actual_price) as actual_price,SUM(jobs.hvt_total) as hvt_total, SUM(jobs.dealer_price) as dealer_price, SUM(jobs.incentive) as incentive,SUM(jobs.hvt_total) as mtd_hvt, SUM(jobs.hvt_value) as mtd_hvt_value,SUM(jobs.vas_total) as mtd_vas, SUM(jobs.vas_value) as mtd_vas_value, jobs.dealer_id, jobs.foc_options,jobs.treatments'))
                ->where(function ($query) use ($search, $first_day, $today) {
                    if (isset($search['from1']) && isset($search['to1'])) {
                        if (!empty(trim($search['from1']))) {
                            $query->whereDate('jobs.job_date', '>=', $search['from1']);
                            $query->whereDate('jobs.job_date', '<=', $search['to1']);
                        }
                    } elseif (isset($search['to1'])) {
                        if (!empty(trim($search['to1']))) {
                            $query->whereDate('jobs.job_date', '<=', $search['to1']);
                        }
                    } elseif (isset($search['from1'])) {
                        if (!empty(trim($search['from1']))) {

                            $query->whereDate('jobs.job_date', '>=', $search['from1']);
                        }
                    } elseif (!empty($search['month'])) {
                        $exp = explode('-', $search['month']);
                        $query->whereMonth('jobs.job_date', $exp[1]);
                        $query->whereYear('jobs.job_date', $exp[0]);
                    } else {
                        $query->whereDate('jobs.job_date', '>=', $first_day);
                        $query->whereDate('jobs.job_date', '<=', $today);
                    }
                })
                ->where('jobs.dealer_id', $value->id)
                ->where('jobs.delete_job', 1)
                // ->where('jobs.foc_options',5)
                // ->groupBy('jobs.dealer_id')
                ->get();
            $treatment_total = $hvt_incentive = $customer_price = $actual_price = $incentive = $hvt_total = $hvt_value = $vas_total = $vas_value = $dealer_price = 0;
            $array = array();
            if (count($mis) == 0) {
                $data = new \stdClass();
                $data->dealer_id = $value->id;
                $data->treatment_total = 0;
                $data->id = 0;
                $data->customer_price = 0;
                $data->actual_price = 0;
                $data->dealer_price = 0;
                $data->incentive = 0;
                $data->hvt_total = 0;
                $data->mtd_hvt = 0;
                $data->hvt_value = 0;
                $data->vas_total = 0;
                $data->vas_value = 0;
                $data->hvt_incentive = 0;
                $mis[] = $data;
            }
            foreach ($mis as $key1 => $value1) {
                $treatment_total += $value1->treatment_total;
                $incentive       += $value1->incentive;
                $actual_price    += (int)$value1->actual_price;
                $hvt_total       += $value1->hvt_total;
                $hvt_value       += $value1->hvt_value;
                $vas_total       += $value1->vas_total;
                $vas_value       += $value1->vas_value;
                $dealer_price    += $value1->dealer_price;

                if ($value1->id != 0) {
                    $decoded_jobs = explode(',', $value1->id);
                    foreach ($decoded_jobs as $key => $val) {
                        $treat = DB::table('jobs')->select('treatments')->where('id', $val)->first();
                        $decoded_treatments = json_decode(@$treat->treatments);
                        if (!empty($decoded_treatments)) {
                            foreach ($decoded_treatments as $key => $val1) {
                                if (@$val1->job_type == 5) {
                                    $customer_price = $customer_price + $val1->customer_price;
                                    $incentive = $incentive + $val1->incentive;
                                } else {
                                    $customer_price = $customer_price + 0;
                                    $incentive = $incentive + 0;
                                }
                                if ($val1->treatment_type == 1) {
                                    $hvt_incentive = $hvt_incentive + $val1->incentive;
                                }
                            }
                        }
                    }
                }
                $array['mtd_total'] = $treatment_total;
                $array['customer_price'] = $customer_price;
                $array['actual_price'] = $actual_price;
                $array['dealer_price'] = $dealer_price;
                $array['incentive'] = $incentive;
                $array['hvt_total'] = $hvt_total;
                $array['mtd_hvt'] = $hvt_total;
                $array['mtd_hvt_value'] = $hvt_value;
                $array['mtd_vas'] = $vas_total;
                $array['mtd_vas_value'] = $vas_value;
                $array['hvt_incentive'] = $hvt_incentive;
                $array['dealer_id'] = @$value1->dealer_id;
            }
            $mist[] = $array;
        }

        foreach ($mist as $key => $value2) {

            $total = DB::table('jobs_by_date')
                ->select(DB::raw('SUM(total_jobs) as total_jobs,dealer_id'))
                ->where('dealer_id', $value2['dealer_id'])
                ->where(function ($query) use ($search, $first_day, $today) {
                    if (isset($search['from1']) && isset($search['to1'])) {
                        if (!empty(trim($search['from1']))) {
                            $query->whereDate('job_added_date', '>=', $search['from1']);
                            $query->whereDate('job_added_date', '<=', $search['to1']);
                        }
                    } elseif (isset($search['to1'])) {
                        if (!empty(trim($search['to1']))) {
                            $query->whereDate('job_added_date', '<=', $search['to1']);
                        }
                    } elseif (isset($search['from1'])) {
                        if (!empty(trim($search['from1']))) {

                            $query->whereDate('job_added_date', '>=', $search['from1']);
                        }
                    } elseif (!empty($search['month'])) {
                        $exp = explode('-', $search['month']);
                        $query->whereMonth('job_added_date', $exp[1]);
                        $query->whereYear('job_added_date', $exp[0]);
                    } else {
                        $query->whereDate('job_added_date', '>=', $first_day);
                        $query->whereDate('job_added_date', '<=', $today);
                    }
                })
                ->groupBy('dealer_id')
                ->first();

            if (!empty($total->total_jobs)) {
                $mist[$key]['service_load'] = $total->total_jobs;
            } else {
                $mist[$key]['service_load'] = 0;
            }
        }

        /************************************ MIS Report End *************************/

        return view('rsm.misReport', [
            'mis' => $mist,
            'oems' => $oems,
            'groups' => $groups,
            'oldFromDate1' => @$search['from1'],
            'oldToDate1' => @$search['to1'],
            'oldMonth' => @$search['month'],
        ]);
    }

    // Download MIS
    public function downloadMIS(Request $request)
    {
        $search = $request->all();
        $today = date('Y-m-d');
        $first_day = date('Y-m-01');

        $user_id = Auth::id();
        $dealer_ids = DB::table('users')->select('id', 'name', 'reporting_authority')->where(['role' => 2, 'status' => 1])->orderBy('id', 'DESC')->get();
        $dealers = array();
        $d_ids = array();
        foreach ($dealer_ids as $i => $j) {
            $report_ids = explode(",", $j->reporting_authority);
            if (in_array($user_id, $report_ids)) {
                $dealers[] = $dealer_ids[$i];
                $d_ids[] = $dealer_ids[$i]->id;
            }
        }

        // $users = DB::table('users')
        // ->select('id')
        // ->where('users.role',2)
        // ->orderBy('users.name','ASC')
        // ->get();
        $mist = array();
        foreach ($dealers as $key => $value) {
            $mis = DB::table('jobs')
                // ->select(DB::raw('jobs.id as job_id,SUM(jobs.treatment_total) as mtd_total,SUM(jobs.customer_price) as customer_price,SUM(jobs.actual_price) as actual_price,SUM(jobs.hvt_total) as hvt_total, SUM(jobs.dealer_price) as dealer_price, SUM(jobs.incentive) as incentive,SUM(jobs.hvt_total) as mtd_hvt, SUM(jobs.hvt_value) as mtd_hvt_value,SUM(jobs.vas_total) as mtd_vas, SUM(jobs.vas_value) as mtd_vas_value, jobs.dealer_id, jobs.foc_options,jobs.treatments'))
                ->where(function ($query) use ($search, $first_day, $today) {
                    if (isset($search['from12']) && isset($search['to12'])) {
                        if (!empty(trim($search['from12']))) {
                            $query->whereDate('jobs.job_date', '>=', $search['from12']);
                            $query->whereDate('jobs.job_date', '<=', $search['to12']);
                        }
                    } elseif (isset($search['to12'])) {
                        if (!empty(trim($search['to12']))) {
                            $query->whereDate('jobs.job_date', '<=', $search['to12']);
                        }
                    } elseif (isset($search['from12'])) {
                        if (!empty(trim($search['from12']))) {
                            $query->whereDate('jobs.job_date', '>=', $search['from12']);
                        }
                    } elseif (!empty($search['selectMonth2'])) {
                        $exp = explode('-', $search['selectMonth2']);
                        $query->whereMonth('jobs.job_date', $exp[1]);
                        $query->whereYear('jobs.job_date', $exp[0]);
                    } else {
                        $query->whereDate('jobs.job_date', '>=', $first_day);
                        $query->whereDate('jobs.job_date', '<=', $today);
                    }
                })
                ->where('jobs.dealer_id', $value->id)
                ->where('jobs.delete_job', 1)
                // ->where('jobs.foc_options',5)
                // ->groupBy('jobs.dealer_id')
                ->get();
            $treatment_total = $hvt_incentive = $customer_price = $actual_price = $incentive = $hvt_total = $hvt_value = $vas_total = $vas_value = $dealer_price = 0;
            $array = array();
            if (count($mis) == 0) {
                $data = new \stdClass();
                $data->dealer_id = $value->id;
                $data->treatment_total = 0;
                $data->id = 0;
                $data->customer_price = 0;
                $data->actual_price = 0;
                $data->dealer_price = 0;
                $data->incentive = 0;
                $data->hvt_total = 0;
                $data->mtd_hvt = 0;
                $data->hvt_value = 0;
                $data->vas_total = 0;
                $data->vas_value = 0;
                $data->hvt_incentive = 0;
                $mis[] = $data;
            }
            foreach ($mis as $key1 => $value1) {
                $treatment_total += $value1->treatment_total;
                $incentive       += $value1->incentive;
                $actual_price    += (int)$value1->actual_price;
                $hvt_total       += $value1->hvt_total;
                $hvt_value       += $value1->hvt_value;
                $vas_total       += $value1->vas_total;
                $vas_value       += $value1->vas_value;
                $dealer_price    += $value1->dealer_price;

                if ($value1->id != 0) {
                    $decoded_jobs = explode(',', $value1->id);
                    foreach ($decoded_jobs as $key => $val) {
                        $treat = DB::table('jobs')->select('treatments')->where('id', $val)->first();
                        $decoded_treatments = json_decode(@$treat->treatments);
                        if (!empty($decoded_treatments)) {
                            foreach ($decoded_treatments as $key => $val1) {
                                if (@$val1->job_type == 5) {
                                    $customer_price = $customer_price + $val1->customer_price;
                                    $incentive = $incentive + $val1->incentive;
                                } else {
                                    $customer_price = $customer_price + 0;
                                    $incentive = $incentive + 0;
                                }
                                if ($val1->treatment_type == 1) {
                                    $hvt_incentive = $hvt_incentive + $val1->incentive;
                                }
                            }
                        }
                    }
                }
                $array['mtd_total'] = $treatment_total;
                $array['customer_price'] = $customer_price;
                $array['actual_price'] = $actual_price;
                $array['dealer_price'] = $dealer_price;
                $array['incentive'] = $incentive;
                $array['hvt_total'] = $hvt_total;
                $array['mtd_hvt'] = $hvt_total;
                $array['mtd_hvt_value'] = $hvt_value;
                $array['mtd_vas'] = $vas_total;
                $array['mtd_vas_value'] = $vas_value;
                $array['hvt_incentive'] = $hvt_incentive;
                $array['dealer_id'] = @$value1->dealer_id;
            }
            $mist[] = $array;
        }
        foreach ($mist as $key => $value) {
            $total = DB::table('jobs_by_date')
                ->select(DB::raw('SUM(total_jobs) as total_jobs,dealer_id'))
                ->where('dealer_id', $value['dealer_id'])
                ->where(function ($query) use ($search, $first_day, $today) {
                    if (isset($search['from12']) && isset($search['to12'])) {
                        if (!empty(trim($search['from12']))) {
                            $query->whereDate('job_added_date', '>=', $search['from12']);
                            $query->whereDate('job_added_date', '<=', $search['to12']);
                        }
                    } elseif (isset($search['to12'])) {
                        if (!empty(trim($search['to12']))) {
                            $query->whereDate('job_added_date', '<=', $search['to12']);
                        }
                    } elseif (isset($search['from12'])) {
                        if (!empty(trim($search['from12']))) {
                            $query->whereDate('job_added_date', '>=', $search['from12']);
                        }
                    } elseif (!empty($search['selectMonth2'])) {
                        $exp = explode('-', $search['selectMonth2']);
                        $query->whereMonth('job_added_date', $exp[1]);
                        $query->whereYear('job_added_date', $exp[0]);
                    } else {
                        $query->whereDate('job_added_date', '>=', $first_day);
                        $query->whereDate('job_added_date', '<=', $today);
                    }
                })
                ->groupBy('dealer_id')
                ->first();
            if (!empty($total->total_jobs)) {
                $mist[$key]['service_load'] = (int)$total->total_jobs;
            } else {
                $mist[$key]['service_load'] = 0;
            }
        }
        /********************************* Download MIS Report Start *******************************/
        return Excel::create('MIS_' . date("d-M-Y"), function ($excel) use ($mist) {
            $excel->sheet('sheet', function ($sheet) use ($mist) {
                $arr = array();
                $cp = $dp = $in = $hvt = $mtd_hvt = $service = 0;
                foreach ($mist as $val1) {
                    $cp = $cp + $val1['customer_price'];
                    $dp = $dp + $val1['dealer_price'];
                    $in = $in + $val1['incentive'];
                    $hvt = $hvt + $val1['hvt_total'];
                    $mtd_hvt = $mtd_hvt + $val1['mtd_hvt_value'];
                    $service = $service + $val1['service_load'];
                }
                $array['CDC'] = 'Business Total';
                $array['Cust_Bill'] = round($cp);
                $array['Vendor'] = round($dp);
                $array['Incentive'] = round($in);
                $array['MTD_HVT'] = round($hvt);
                $array['HVT_Value'] = round($mtd_hvt);
                $array['HVT_%'] = hvt_in_percentage($mtd_hvt, $cp);
                $array['Service_Load'] = round($service);
                $arr[] = $array;
                foreach ($mist as $val) {
                    $array['CDC'] = get_name($val['dealer_id']);
                    $array['Cust_Bill'] = round($val['customer_price']);
                    $array['Vendor'] = round($val['dealer_price']);
                    $array['Incentive'] = round($val['incentive']);
                    $array['MTD_HVT'] = round($val['hvt_total']);
                    $array['HVT_Value'] = round($val['mtd_hvt_value']);
                    $array['HVT_%'] = hvt_in_percentage($val['mtd_hvt_value'], $val['customer_price']);
                    $array['RO'] = $val['service_load'];
                    $arr[] = $array;
                }
                $count = count($arr) + 1;
                $sheet->setBorder('A3:H' . $count);
                $sheet->cells('A3:A' . $count, function ($cells) {
                    $cells->setBackground('#FFFF00');
                });
                $sheet->cells('B3:B' . $count, function ($cells) {
                    $cells->setBackground('#B6DDE8');
                });
                $sheet->cells('C3:C' . $count, function ($cells) {
                    $cells->setBackground('#F7FED0');
                });
                $sheet->cells('D3:D' . $count, function ($cells) {
                    $cells->setBackground('#FFFF00');
                });
                $sheet->cells('E3:E' . $count, function ($cells) {
                    $cells->setBackground('#F2DDDC');
                });
                $sheet->cells('F3:F' . $count, function ($cells) {
                    $cells->setBackground('#F2DDDC');
                });
                $sheet->cells('G3:G' . $count, function ($cells) {
                    $cells->setBackground('#FFFF00');
                });
                $sheet->cells('H3:H' . $count, function ($cells) {
                    $cells->setBackground('#B6DDE8');
                });
                $sheet->fromArray(@$arr);
            });
        })->export('xlsx');
        /********************************* Download MIS Report End *******************************/
    }


    // View DCF report
    public function dcfReport(Request $request)
    {
        $search = $request->all();
        $user_id = Auth::id();
        $today = date('Y-m-d');
        $first_day = date('Y-m-01');
        if (!empty($search['report_type'])) {
            $type = $search['report_type'];
        } else {
            $type = 'dealer';
        }

        /************************************ DCF Report Start *******************************/
        if (@$search['selectMonth']) {
            $monthYear = explode('-', $search['selectMonth']);
            $year = $monthYear[0];
            $month =  $monthYear[1];
            $model = array();
            $dealer_ids = DB::table('users')->select('id', 'name', 'reporting_authority')->where(['role' => 2, 'status' => 1])->orderBy('id', 'DESC')->get();
            $dealers = array();
            $d_ids = array();
            foreach ($dealer_ids as $i => $j) {
                $report_ids = explode(",", $j->reporting_authority);
                if (in_array($user_id, $report_ids)) {
                    $dealers[] = $dealer_ids[$i];
                    // $d_ids[]=$dealer_ids[$i]->id;
                }
            }
            if (!empty($dealers) && @count($dealers) > 0) {
                return Excel::create('DCF_' . date("d-M-Y"), function ($excel) use ($dealers, $search, $month, $year) {
                    foreach ($dealers as $dealerValue) {
                        $excel->sheet(get_name($dealerValue->id), function ($sheet) use ($dealerValue, $search, $month, $year) {
                            $getModels = DB::table('models as m')
                                ->select(DB::raw('group_concat(m.id) as model_id, m.model_size'))
                                // ->where('m.dealer_id',$dealerValue->id)
                                ->groupBy('m.model_size')
                                ->orderBy('m.model_size', 'ASC')
                                ->get();
                            foreach ($getModels as $value) {
                                $model_id = explode(',', $value->model_id);
                                $jobs = DB::table('jobs')
                                    ->select('id', 'model_id', 'treatments')
                                    ->where('dealer_id', $dealerValue->id)
                                    ->whereIn('model_id', $model_id)
                                    ->whereMonth('job_date', $month)
                                    ->whereYear('job_date', $year)
                                    ->where('delete_job', 1)
                                    // ->where('foc_options',5)
                                    ->get();

                                $getTreatments = DB::table('treatments')
                                    ->select('id', 'treatment', 'labour_code')
                                    // ->where('dealer_id',$dealerValue->id)
                                    ->whereIn('model_id', $model_id)
                                    ->orderBy('treatment', 'ASC')
                                    ->get();

                                $tcounts = array();
                                $i = 0;
                                foreach ($getTreatments as $key => $tvalue) {
                                    $tcounts[$i]['id'] = $tvalue->id;
                                    $tcounts[$i]['treatment'] = $tvalue->treatment;
                                    $tcounts[$i]['labour_code'] = $tvalue->labour_code;
                                    $tcounts[$i]['total'] = 0;
                                    $tcounts[$i]['customer_price'] = 0;
                                    foreach ($jobs as $jvalue) {
                                        if (@$jvalue->treatments) {
                                            $t = json_decode($jvalue->treatments);
                                            if (@$t) {
                                                foreach ($t as $jtvalue) {
                                                    if ($jtvalue->id == $tvalue->id) {
                                                        $tcounts[$i]['total']++;
                                                        $tcounts[$i]['customer_price'] = $tcounts[$i]['customer_price'] + $jtvalue->customer_price;
                                                    }
                                                }
                                            }
                                        }
                                    }
                                    $i++;
                                }
                                $final_treatments = array();
                                $tnames = array();
                                $j = 0;
                                foreach ($tcounts as $valuet) {
                                    if (in_array($valuet['treatment'], $tnames)) {
                                        $a = array_search($valuet['treatment'], $tnames);
                                        $final_treatments[$a]['total'] = $final_treatments[$a]['total'] + $valuet['total'];
                                        $final_treatments[$a]['customer_price'] = $final_treatments[$a]['customer_price'] + $valuet['customer_price'];
                                    } else {
                                        $tnames[$j] = $valuet['treatment'];
                                        $final_treatments[$j]['labour_code'] = $valuet['labour_code'];
                                        $final_treatments[$j]['treatment'] = $valuet['treatment'];
                                        $final_treatments[$j]['total'] = $valuet['total'];
                                        $final_treatments[$j]['customer_price'] = $valuet['customer_price'];
                                        $j++;
                                    }
                                }
                                $sheet->setCellValue('A1', 'Small');
                                $sheet->mergeCells("A1:D1");
                                $sheet->setCellValue('A2', 'Treatment');
                                $sheet->setCellValue('B2', 'Labour_Code');
                                $sheet->setCellValue('C2', 'Number');
                                $sheet->setCellValue('D2', 'Value');
                                $sheet->setCellValue('E1', 'Medium');
                                $sheet->mergeCells("E1:H1");
                                $sheet->setCellValue('E2', 'Treatment');
                                $sheet->setCellValue('F2', 'Labour_Code');
                                $sheet->setCellValue('G2', 'Number');
                                $sheet->setCellValue('H2', 'Value');
                                $sheet->setCellValue('I1', 'Large');
                                $sheet->mergeCells("I1:L1");
                                $sheet->setCellValue('I2', 'Treatment');
                                $sheet->setCellValue('J2', 'Labour_Code');
                                $sheet->setCellValue('K2', 'Number');
                                $sheet->setCellValue('L2', 'Value');
                                // $sheet->setCellValue('M1','Total');
                                // $sheet->mergeCells("M1:N2");
                                if ($value->model_size == 3) {
                                    $i = 3;
                                    $in = 'A';
                                    $no = 0;
                                    $val = 0;
                                    foreach ($final_treatments as  $value1) {
                                        $sheet->setCellValue($in . $i, $value1['treatment']);
                                        $in++;
                                        $sheet->setCellValue($in . $i, $value1['labour_code']);
                                        $in++;
                                        $sheet->setCellValue($in . $i, $value1['total']);
                                        $in++;
                                        $sheet->setCellValue($in . $i, $value1['customer_price']);
                                        $no = $no + $value1['total'];
                                        $val = $val + $value1['customer_price'];
                                        $i++;
                                        $in = 'A';
                                    }
                                    $sheet->setCellValue('A' . $i, 'Total');
                                    $sheet->setCellValue('C' . $i, $no);
                                    $sheet->setCellValue('D' . $i, $val);
                                }
                                if ($value->model_size == 2) {
                                    $i = 3;
                                    $in = 'E';
                                    $no = 0;
                                    $val = 0;
                                    foreach ($final_treatments as  $value2) {
                                        $sheet->setCellValue($in . $i, $value2['treatment']);
                                        $in++;
                                        $sheet->setCellValue($in . $i, $value2['labour_code']);
                                        $in++;
                                        $sheet->setCellValue($in . $i, $value2['total']);
                                        $in++;
                                        $sheet->setCellValue($in . $i, $value2['customer_price']);
                                        $no = $no + $value2['total'];
                                        $val = $val + $value2['customer_price'];
                                        $i++;
                                        $in = 'E';
                                    }
                                    $sheet->setCellValue('G' . $i, $no);
                                    $sheet->setCellValue('H' . $i, $val);
                                }
                                if ($value->model_size == 1) {
                                    $i = 3;
                                    $in = 'I';
                                    $no = 0;
                                    $val = 0;
                                    foreach ($final_treatments as $value3) {
                                        $sheet->setCellValue($in . $i, $value3['treatment']);
                                        $in++;
                                        $sheet->setCellValue($in . $i, $value3['labour_code']);
                                        $in++;
                                        $sheet->setCellValue($in . $i, $value3['total']);
                                        $in++;
                                        $sheet->setCellValue($in . $i, $value3['customer_price']);
                                        $no = $no + $value3['total'];
                                        $val = $val + $value3['customer_price'];
                                        $i++;
                                        $in = 'I';
                                    }
                                    $sheet->setCellValue('K' . $i, $no);
                                    $sheet->setCellValue('L' . $i, $val);
                                }
                            }
                        });
                    }
                })->export('xlsx');
            } else {
                Session::flash('error', 'Data Not Available.');
                return redirect()->back();
            }
        }

        /************************************ DCF Report End *******************************/


        Session::put('oldReport', $type);
        return view('rsm.dcf_report', [
            //'result' => $result1,
            //'total_incentive' => $total_incentive,
            //'advisors' => $advisors,
            'total_job_array' => @$total_job_array,
            // 'mis' => $mist,
            //'dealers' => $dealers,
            'oldFromDate' => @$search['from'],
            'oldToDate' => @$search['to'],
            'oldFromDate1' => @$search['from1'],
            'oldToDate1' => @$search['to1'],
            'oldDealer' => @$search['dealer'],
            'oldDealers' => @$search['dealers'],
            'oldMonth' => @$search['month'],
            'oldSelectMonth' => @$search['month1'],
            'oldReport' => @$type,
            'tabName' => @$search['tabName'],
        ]);
    }

    // view Consumption Report 
    public function consumptionReport(Request $request)
    {
        $search = $request->all();
        if (@$search['selectMonth']) {
            $month = explode('-', $search['selectMonth']);
        } else {
            $month = explode('-', date('Y-m'));
        }

        $user_id = Auth::id();
        $dealer_ids = DB::table('users')->select('id', 'name', 'reporting_authority')->where(['role' => 2, 'status' => 1])->orderBy('id', 'DESC')->get();
        $dealers = array();
        $d_ids = array();
        foreach ($dealer_ids as $i => $j) {
            $report_ids = explode(",", $j->reporting_authority);
            if (in_array($user_id, $report_ids)) {
                $dealers[] = $dealer_ids[$i];
                $d_ids[] = $dealer_ids[$i]->id;
            }
        }
        $result = DB::table('jobs')
            ->whereIn('dealer_id', $d_ids)
            ->where(function ($query) use ($search) {
                if (!empty($search)) {
                    if (isset($search['dealer'])) {
                        if (!empty(trim($search['dealer']))) {
                            $query->where('dealer_id', '=', $search['dealer']);
                        }
                    }
                }
            })
            ->whereMonth('job_date', $month[1])
            ->whereYear('job_date', $month[0])
            ->where('delete_job', 1)
            ->get();
        $duplicate = array();
        $treatment_data = array();
        if (!empty($result)) {
            foreach ($result as $key => $value) {
                $treatments = json_decode($value->treatments);
                $treatment_data[] = $treatments[0];
            }
            $duplicate = array_count_values(array_column(@$treatment_data, 'id'));
            $first_array = array();
            foreach ($duplicate as $k => $val) {
                $treatment_value = 0;
                foreach ($treatment_data as $tre_k => $tre_value) {

                    if ($k == $tre_value->id) {
                        $treatment_value = $treatment_value + $tre_value->customer_price;
                        $res['id'] = $tre_value->id;
                        $res['treatment'] = $tre_value->treatment;
                        $res['treatment_type'] = $tre_value->treatment_type;
                        $res['count'] = $val;
                    }
                    $res['total_price'] = $treatment_value;
                }
                $first_array[] = $res;
            }

            foreach ($first_array as $key => $value) {
                $products = DB::table('products_treatments as pt')
                    ->join('products as p', 'p.id', 'pt.pro_id')
                    ->select('pt.pro_id', 'pt.tre_id', 'pt.uom', 'p.name', DB::raw('SUM(pt.quantity) as quantity, SUM(pt.price) as price, count(pt.pro_id) as total_pro_id'))
                    ->where('pt.tre_id', $value['id'])
                    ->groupBy('pt.pro_id')
                    ->get();

                foreach ($products as $k => $val) {
                    $products[$k]->total_quantity = $val->quantity * $value['count'];
                    $products[$k]->total_price = $val->price * $value['count'];
                }
                $first_array[$key]['products'] = $products;
            }
            $treatments = array();
            foreach ($first_array as $key => $value) {
                $treatments[$key]['id'] = $value['id'];
                $treatments[$key]['treatment'] = $value['treatment'];
                $treatments[$key]['treatment_type'] = $value['treatment_type'];
                $treatments[$key]['count'] = $value['count'];
                $treatments[$key]['total_price'] = $value['total_price'];
                if (!empty($value['products'])) {
                    foreach ($value['products'] as $k => $val) {
                        $pro['pro_id'] = $val->pro_id;
                        $pro['tre_id'] = $val->tre_id;
                        $pro['uom'] = $val->uom;
                        $pro['name'] = $val->name;
                        $pro['quantity'] = $val->quantity;
                        $pro['price'] = $val->price;
                        $pro['total_pro_id'] = $val->total_pro_id;
                        $pro['total_quantitys'] = $val->total_quantity;
                        $pro['total_prices'] = $val->total_price;
                        $treatments[$key]['products'][$k] = $pro;
                    }
                }
                //$treatments[$key]['treatment'] = $value['treatment'];
            }
            //print_r($duplicate);
            // echo "<pre>";
            // print_r($treatments);
            // die;
            //  print_r($first_array);
            //$treatments = $first_array;
            //dd($treatments);
            $newarray = array();
            foreach ($first_array as $row) {
                foreach ($row['products'] as $key => $val) {
                    if (!isset($newarray[$val->pro_id])) {
                        $newarray[$val->pro_id] = $val;
                        $newarray[$val->pro_id]->count = 1;
                        continue;
                    }
                    $newarray[$val->pro_id]->total_quantity += $val->total_quantity;
                    $newarray[$val->pro_id]->total_price += $val->total_price;
                    $newarray[$val->pro_id]->count++;
                }
            }
        }
        //dd($first_array, $treatments);
        return view('rsm.consumptionReport', [
            'treatments' => @$treatments,
            'dealers' => $dealers,
            'oldDealer' => @$search['dealer'],
            'oldMonth' => @$search['selectMonth'],
            'products' => array_values(@$newarray),
        ]);
        // echo "<pre>";
        // print_r(array_values($newarray));
    }

    public function downloadPerformanceSheet(Request $request)
    {
        $search = $request->all();
        $current = date('Y-m');
        if (@$search['selectMonth1']) {
            $monthYear = explode('-', $search['selectMonth1']);
        } else {
            $monthYear = explode('-', $current);
        }
        $user_id = Auth::id();
        $dealer_ids = DB::table('users')->select('id', 'name', 'reporting_authority')->where(['role' => 2, 'status' => 1])->orderBy('id', 'DESC')->get();
        $dealers = array();
        $d_ids = array();
        foreach ($dealer_ids as $i => $j) {
            $report_ids = explode(",", $j->reporting_authority);
            if (in_array($user_id, $report_ids)) {
                $dealers[] = $dealer_ids[$i];
                $d_ids[] = $dealer_ids[$i]->id;
            }
        }
        $advisors = DB::table('jobs')
            ->select(DB::raw('group_concat(id) as job_id, SUM(customer_price) as customer_price, SUM(incentive) as incentive, SUM(dealer_price) as dealer_price,SUM(hvt_value) as hvt_value,SUM(hvt_total) as hvt_total, advisor_id,dealer_id, job_date'))
            ->whereIn('dealer_id', $d_ids)
            ->where(function ($query) use ($search) {
                if (!empty($search)) {
                    if (isset($search['dealer1'])) {
                        if (!empty(trim($search['dealer1']))) {
                            $query->where('dealer_id', '=', $search['dealer1']);
                        }
                    }
                }
            })
            ->whereMonth('job_date', '=', $monthYear[1])
            ->whereYear('job_date', '=', $monthYear[0])
            ->where('delete_job', 1)
            ->groupBy('advisor_id')
            ->get();
        if (@$search['dealer1']) {
            $sheetName = get_name($search['dealer1']) . '_';
        } else {
            $sheetName = 'All_Dealers_';
        }
        return Excel::create($sheetName . 'Performance_Report_' . date("M-Y"), function ($excel) use ($advisors) {
            $result = array();
            foreach ($advisors as $key => $value) {
                $arr['Dealer'] = get_name($value->dealer_id);
                $arr['Advisor'] = get_advisor_name($value->advisor_id);
                $arr['PAN_Card'] = get_pan_no($value->advisor_id);
                $arr['Customer_Price'] = round($value->customer_price);
                $arr['Incentive'] = round($value->incentive);
                $arr['HVT_Value'] = round($value->hvt_value);
                $arr['HVT_Number'] = (int)$value->hvt_total;
                $result[] = $arr;
            }
            //dd($result);
            $excel->sheet('sheet', function ($sheet) use ($result) {
                $sheet->fromArray($result);
            });
        })->export('xlsx');
    }

    // View performance report
    public function performance_reports(Request $request)
    {
        $search = $request->all();
        $current = date('Y-m');
        if (@$search['selectMonth']) {
            $monthYear = explode('-', $search['selectMonth']);
        } else {
            $monthYear = explode('-', $current);
        }
        $user_id = Auth::id();
        $dealer_ids = DB::table('users')->select('id', 'name', 'reporting_authority')->where(['role' => 2, 'status' => 1])->orderBy('id', 'DESC')->get();
        $dealers = array();
        $d_ids = array();
        foreach ($dealer_ids as $i => $j) {
            $report_ids = explode(",", $j->reporting_authority);
            if (in_array($user_id, $report_ids)) {
                $dealers[] = $dealer_ids[$i];
                $d_ids[] = $dealer_ids[$i]->id;
            }
        }
        // $dealers = User::where('role',2)->select('id','name')->orderBy('name','ASC')->get();
        $advisors = DB::table('jobs')
            ->select(DB::raw('group_concat(id) as job_id, SUM(customer_price) as customer_price, SUM(incentive) as incentive, SUM(dealer_price) as dealer_price,SUM(hvt_value) as hvt_value, advisor_id,dealer_id,job_date'))
            ->whereIn('dealer_id', $d_ids)
            ->where(function ($query) use ($search) {
                if (!empty($search)) {
                    if (isset($search['dealer'])) {
                        if (!empty(trim($search['dealer']))) {
                            $query->where('dealer_id', '=', $search['dealer']);
                        }
                    }
                }
            })
            ->whereMonth('job_date', '=', $monthYear[1])
            ->whereYear('job_date', '=', $monthYear[0])
            ->where('delete_job', 1)
            ->where('foc', 0)
            ->groupBy('advisor_id')
            ->get();
        //dd($advisors);
        return view('rsm.performance_reports', [
            'dealers' => $dealers,
            'advisors' => $advisors,
            'oldDealer' => @$search['dealer'],
            'oldMonth' => @$search['selectMonth'],
        ]);
    }

    // Download Particular Advisor's Performance Report
    public function downloadAdvisor($id, $dealer_id, $month)
    {
        $monthYear = explode('-', $month);
        $getModels = DB::table('models as m')
            ->select(DB::raw('group_concat(m.id) as model_id, m.model_size'))
            // ->where('m.dealer_id',$dealer_id)
            ->groupBy('m.model_size')
            ->orderBy('m.model_size', 'ASC')->get();
        return Excel::create(get_name($dealer_id) . '_(' . get_advisor_name($id) . ')_' . date("M-Y"), function ($excel) use ($getModels, $id, $dealer_id, $monthYear) {
            $excel->sheet('sheet', function ($sheet) use ($getModels, $id, $dealer_id, $monthYear) {
                foreach ($getModels as $value) {
                    $model_id = explode(',', $value->model_id);
                    // if($value->model_size == 1){
                    //     $sheetName = 'Large';
                    // }elseif($value->model_size == 2){
                    //     $sheetName = 'Medium';
                    // }elseif($value->model_size == 3){
                    //     $sheetName = 'Small';
                    // }
                    $advisors = DB::table('jobs')
                        ->select('model_id', 'treatments')
                        ->where('dealer_id', '=', $dealer_id)
                        ->whereIn('model_id', $model_id)
                        ->whereMonth('job_date', '=', $monthYear[1])
                        ->whereYear('job_date', '=', $monthYear[0])
                        ->where('advisor_id', $id)
                        ->where('delete_job', 1)
                        ->get();
                    //dd($advisors);
                    $getTreatments = DB::table('treatments')
                        ->select('id', 'treatment')
                        // ->where('dealer_id',$dealer_id)
                        ->whereIn('model_id', $model_id)
                        // ->where('treatment_type',1)
                        ->orderBy('treatment', 'ASC')
                        ->get();
                    $tcounts = array();
                    $i = 0;
                    foreach ($getTreatments as $key => $tvalue) {
                        $tcounts[$i]['id'] = $tvalue->id;
                        $tcounts[$i]['treatment'] = $tvalue->treatment;
                        $tcounts[$i]['total'] = 0;
                        $tcounts[$i]['customer_price'] = 0;
                        foreach ($advisors as $jvalue) {
                            if (@$jvalue->treatments) {
                                $t = json_decode($jvalue->treatments);
                                if (@$t) {
                                    foreach ($t as $jtvalue) {
                                        if ($jtvalue->id == $tvalue->id) {
                                            // if($jtvalue->treatment_type=='1'){
                                            $tcounts[$i]['total']++;
                                            $tcounts[$i]['customer_price'] = $tcounts[$i]['customer_price'] + $jtvalue->customer_price;
                                            // }
                                        }
                                    }
                                }
                            }
                        }
                        $i++;
                    }
                    $final_treatments = array();
                    $tnames = array();
                    $j = 0;
                    foreach ($tcounts as $valuet) {
                        if (in_array($valuet['treatment'], $tnames)) {
                            $a = array_search($valuet['treatment'], $tnames);
                            $final_treatments[$a]['total'] = $final_treatments[$a]['total'] + $valuet['total'];
                            $final_treatments[$a]['customer_price'] = $final_treatments[$a]['customer_price'] + $valuet['customer_price'];
                        } else {
                            $tnames[$j] = $valuet['treatment'];
                            $final_treatments[$j]['treatment'] = $valuet['treatment'];
                            $final_treatments[$j]['total'] = $valuet['total'];
                            $final_treatments[$j]['customer_price'] = $valuet['customer_price'];
                            $j++;
                        }
                    }
                    //dd($final_treatments);
                    $sheet->cells('A1:K2', function ($cells) {
                        $cells->setBackground('#FFFF00');
                    });
                    $sheet->setBorder('A1:K2');
                    $sheet->setCellValue('A1', 'Large');
                    $sheet->mergeCells("A1:C1");
                    $sheet->setCellValue('A2', 'Treatment');
                    $sheet->setCellValue('B2', 'Number');
                    $sheet->setCellValue('C2', 'Value');
                    $sheet->setCellValue('E1', 'Medium');
                    $sheet->mergeCells("E1:G1");
                    $sheet->setCellValue('E2', 'Treatment');
                    $sheet->setCellValue('F2', 'Number');
                    $sheet->setCellValue('G2', 'Value');
                    $sheet->setCellValue('I1', 'Small');
                    $sheet->mergeCells("I1:K1");
                    $sheet->setCellValue('I2', 'Treatment');
                    $sheet->setCellValue('J2', 'Number');
                    $sheet->setCellValue('K2', 'Value');
                    $sheet->setBorder('M2:N4');
                    $sheet->cells('M2:N4', function ($cells) {
                        $cells->setBackground('#FFFF00');
                    });
                    $sheet->setCellValue('M2', 'Advisor');
                    $sheet->setCellValue('N2', get_advisor_name($id));
                    $sheet->setCellValue('M3', 'Pan Card');
                    $sheet->setCellValue('N3', get_pan_no($id));
                    $sheet->setCellValue('M4', 'Dealer');
                    $sheet->setCellValue('N4', get_name($dealer_id));
                    foreach ($final_treatments as $value1) {
                        $tot = count($final_treatments) + 3;
                        if ($value->model_size == 1) {
                            $i = 3;
                            $model1_total = $model1_value = 0;
                            foreach ($final_treatments as  $value1) {
                                $sheet->setCellValue('A' . $i, $value1['treatment']);
                                $sheet->setCellValue('B' . $i, $value1['total']);
                                $sheet->setCellValue('C' . $i, $value1['customer_price']);
                                $i++;
                                $sheet->setCellValue('A28', 'Total');
                                $model1_total = $model1_total + $value1['total'];
                                $model1_value = $model1_value + $value1['customer_price'];
                            }
                        }
                        if ($value->model_size == 2) {
                            $i = 3;
                            $model2_total = $model2_value = 0;
                            foreach ($final_treatments as  $value2) {
                                $sheet->setCellValue('E' . $i, $value2['treatment']);
                                $sheet->setCellValue('F' . $i, $value2['total']);
                                $sheet->setCellValue('G' . $i, $value2['customer_price']);
                                $i++;
                                $model2_total = $model2_total + $value2['total'];
                                $model2_value = $model2_value + $value2['customer_price'];
                            }
                        }
                        if ($value->model_size == 3) {
                            $i = 3;
                            $model3_total = $model3_value = 0;
                            foreach ($final_treatments as $value3) {
                                $sheet->setCellValue('I' . $i, $value3['treatment']);
                                $sheet->setCellValue('J' . $i, $value3['total']);
                                $sheet->setCellValue('K' . $i, $value3['customer_price']);
                                $i++;
                                $model3_total = $model3_total + $value3['total'];
                                $model3_value = $model3_value + $value3['customer_price'];
                            }
                        }
                        $sheet->setCellValue('B28', @$model1_total);
                        $sheet->setCellValue('C28', @$model1_value);
                        $sheet->setCellValue('F28', @$model2_total);
                        $sheet->setCellValue('G28', @$model2_value);
                        $sheet->setCellValue('J28', @$model3_total);
                        $sheet->setCellValue('K28', @$model3_value);
                        $sheet->cells('A28:K28', function ($cells) {
                            $cells->setBackground('#FFFF00');
                        });
                        $sheet->setBorder('A28:K28');
                        //$final[]=$array;
                    }
                    //$sheet->fromArray($final);
                }
            });
        })->export('xlsx');
    }
    // Download All Advisor's Performance Report
    public function downloadAllAdvisor(Request $request)
    {
        if (!empty($request->selectMonth2)) {
            $month = $request->selectMonth2;
        } else {
            $month = date('Y-m');
        }
        $dealer_id = $request->dealer2;
        $monthYear = explode('-', $month);
        dd($monthYear);
        $advisors = DB::table('advisors')->where('dealer_id', $dealer_id)->get();
        //dd($advisors);
        $getModels = DB::table('models as m')
            ->select(DB::raw('group_concat(m.id) as model_id, m.model_size'))
            // ->where('m.dealer_id',$dealer_id)
            ->groupBy('m.model_size')
            ->orderBy('m.model_size', 'ASC')->get();
        return Excel::create(get_name($dealer_id) . '_' . date("M-Y"), function ($excel) use ($getModels, $dealer_id, $monthYear, $advisors) {
            foreach ($advisors as $advisor) {
                $excel->sheet($advisor->name, function ($sheet) use ($getModels, $dealer_id, $monthYear, $advisor) {
                    foreach ($getModels as $value) {
                        $model_id = explode(',', $value->model_id);
                        $advisors = DB::table('jobs')
                            ->select('model_id', 'treatments')
                            ->where('dealer_id', '=', $dealer_id)
                            ->whereIn('model_id', $model_id)
                            ->whereMonth('job_date', '=', $monthYear[1])
                            ->whereYear('job_date', '=', $monthYear[0])
                            ->where('advisor_id', $advisor->id)
                            ->where('delete_job', 1)
                            ->get();
                        //dd($advisors);
                        $getTreatments = DB::table('treatments')
                            ->select('id', 'treatment')
                            // ->where('dealer_id',$dealer_id)
                            ->whereIn('model_id', $model_id)
                            // ->where('treatment_type',1)
                            ->orderBy('treatment', 'ASC')
                            ->get();
                        $tcounts = array();
                        $i = 0;
                        foreach ($getTreatments as $key => $tvalue) {
                            $tcounts[$i]['id'] = $tvalue->id;
                            $tcounts[$i]['treatment'] = $tvalue->treatment;
                            $tcounts[$i]['total'] = 0;
                            $tcounts[$i]['customer_price'] = 0;
                            foreach ($advisors as $jvalue) {
                                if (@$jvalue->treatments) {
                                    $t = json_decode($jvalue->treatments);
                                    if (@$t) {
                                        foreach ($t as $jtvalue) {
                                            if ($jtvalue->id == $tvalue->id) {
                                                // if($jtvalue->treatment_type=='1'){
                                                $tcounts[$i]['total']++;
                                                $tcounts[$i]['customer_price'] = $tcounts[$i]['customer_price'] + $jtvalue->customer_price;
                                                // }
                                            }
                                        }
                                    }
                                }
                            }
                            $i++;
                        }
                        $final_treatments = array();
                        $tnames = array();
                        $j = 0;
                        foreach ($tcounts as $valuet) {
                            if (in_array($valuet['treatment'], $tnames)) {
                                $a = array_search($valuet['treatment'], $tnames);
                                $final_treatments[$a]['total'] = $final_treatments[$a]['total'] + $valuet['total'];
                                $final_treatments[$a]['customer_price'] = $final_treatments[$a]['customer_price'] + $valuet['customer_price'];
                            } else {
                                $tnames[$j] = $valuet['treatment'];
                                $final_treatments[$j]['treatment'] = $valuet['treatment'];
                                $final_treatments[$j]['total'] = $valuet['total'];
                                $final_treatments[$j]['customer_price'] = $valuet['customer_price'];
                                $j++;
                            }
                        }
                        //dd($final_treatments);
                        $sheet->cells('A1:K2', function ($cells) {
                            $cells->setBackground('#FFFF00');
                        });
                        $sheet->setBorder('A1:K2');
                        $sheet->setCellValue('A1', 'Large');
                        $sheet->mergeCells("A1:C1");
                        $sheet->setCellValue('A2', 'Treatment');
                        $sheet->setCellValue('B2', 'Number');
                        $sheet->setCellValue('C2', 'Value');
                        $sheet->setCellValue('E1', 'Medium');
                        $sheet->mergeCells("E1:G1");
                        $sheet->setCellValue('E2', 'Treatment');
                        $sheet->setCellValue('F2', 'Number');
                        $sheet->setCellValue('G2', 'Value');
                        $sheet->setCellValue('I1', 'Small');
                        $sheet->mergeCells("I1:K1");
                        $sheet->setCellValue('I2', 'Treatment');
                        $sheet->setCellValue('J2', 'Number');
                        $sheet->setCellValue('K2', 'Value');
                        $sheet->setBorder('M2:N4');
                        $sheet->cells('M2:N4', function ($cells) {
                            $cells->setBackground('#FFFF00');
                        });
                        $sheet->setCellValue('M2', 'Advisor');
                        $sheet->setCellValue('N2', get_advisor_name($advisor->id));
                        $sheet->setCellValue('M3', 'Pan Card');
                        $sheet->setCellValue('N3', get_pan_no($advisor->id));
                        $sheet->setCellValue('M4', 'Dealer');
                        $sheet->setCellValue('N4', get_name($dealer_id));
                        foreach ($final_treatments as $value1) {
                            $tot = count($final_treatments) + 3;
                            if ($value->model_size == 1) {
                                $i = 3;
                                $model1_total = $model1_value = 0;
                                foreach ($final_treatments as  $value1) {
                                    $sheet->setCellValue('A' . $i, $value1['treatment']);
                                    $sheet->setCellValue('B' . $i, $value1['total']);
                                    $sheet->setCellValue('C' . $i, $value1['customer_price']);
                                    $i++;
                                    $sheet->setCellValue('A28', 'Total');
                                    $model1_total = $model1_total + $value1['total'];
                                    $model1_value = $model1_value + $value1['customer_price'];
                                }
                            }
                            if ($value->model_size == 2) {
                                $i = 3;
                                $model2_total = $model2_value = 0;
                                foreach ($final_treatments as  $value2) {
                                    $sheet->setCellValue('E' . $i, $value2['treatment']);
                                    $sheet->setCellValue('F' . $i, $value2['total']);
                                    $sheet->setCellValue('G' . $i, $value2['customer_price']);
                                    $i++;
                                    $model2_total = $model2_total + $value2['total'];
                                    $model2_value = $model2_value + $value2['customer_price'];
                                }
                            }
                            if ($value->model_size == 3) {
                                $i = 3;
                                $model3_total = $model3_value = 0;
                                foreach ($final_treatments as $value3) {
                                    $sheet->setCellValue('I' . $i, $value3['treatment']);
                                    $sheet->setCellValue('J' . $i, $value3['total']);
                                    $sheet->setCellValue('K' . $i, $value3['customer_price']);
                                    $i++;
                                    $model3_total = $model3_total + $value3['total'];
                                    $model3_value = $model3_value + $value3['customer_price'];
                                }
                            }
                            $sheet->setCellValue('B28', @$model1_total);
                            $sheet->setCellValue('C28', @$model1_value);
                            $sheet->setCellValue('F28', @$model2_total);
                            $sheet->setCellValue('G28', @$model2_value);
                            $sheet->setCellValue('J28', @$model3_total);
                            $sheet->setCellValue('K28', @$model3_value);
                            $sheet->cells('A28:K28', function ($cells) {
                                $cells->setBackground('#FFFF00');
                            });
                            $sheet->setBorder('A28:K28');
                            //$final[]=$array;
                        }
                        //$sheet->fromArray($final);
                    }
                });
            }
        })->export('xlsx');
    }


    public function view_attendance($id)
    {
        $list = array();

        if (isset($_GET['selectMonth']) && !empty($_GET['selectMonth'])) {
            $month = date('m', strtotime($_GET['selectMonth']));
            $year = date('Y', strtotime($_GET['selectMonth']));;
            $day = 31;
        } else {
            $month = date('m');
            $year = date('Y');
            $day = date('d');
        }

        for ($d = 1; $d <= $day; $d++) {
            $time = mktime(12, 0, 0, $month, $d, $year);
            if (date('m', $time) == $month)
                $list[] = date('Y-m-d', $time);
        }

        foreach ($list as $key => $value) {

            $result = DB::table('attendance')->where('user_id', $id)->whereDate('date', $value)->get();
            if ($result->count() == 0) {
                $nulldata = new \stdClass();
                // $result->date = $list[$key];
                $nulldata->date = $list[$key];
                $result[] = $nulldata;
            }
            $data[] = $result;
        }

        if (isset($_GET['download']) && ($_GET['download'] == 1)) {
            return Excel::create('MonthlyAttendanceReport_' . date("d-M-Y"), function ($excel) use ($data, $id) {
                $excel->sheet('sheet', function ($sheet) use ($data, $id) {
                    $result = array();
                    $file = array();
                    $notmarked = 0;
                    $half = 0;
                    $full = 0;
                    $i = 2;
                    $three = 0;

                    foreach ($data as $key1 => $value1) {

                        $row = count($data[$key1]);
                        foreach ($data[$key1] as $key => $value) {
                            $file['Date'] = date('d M, Y', strtotime($data[$key1][0]->date));
                            if ($row > 1) {
                                if ($key == 0) {
                                    $end = $i + $row - 1;
                                    $sheet->mergeCells('A' . $i . ':' . 'A' . $end);
                                    $sheet->mergeCells('G' . $i . ':' . 'G' . $end);
                                    $sheet->cells('A' . $i, function ($cells) {
                                        $cells->setValignment('center');
                                    });
                                    $sheet->cells('G' . $i, function ($cells) {
                                        $cells->setValignment('center');
                                    });
                                }
                            }

                            if (isset($value->user_id)) {
                                $myid = $value->user_id;
                                $file['In Time'] = $value->in_time;
                                $file['Out Time'] = $value->out_time;
                                $file['User'] = get_name($value->user_id);
                                $file['Dealer'] = get_name($value->dealer_id);
                                $timeFirst = strtotime($value->in_time);
                                $timeSecond = strtotime($value->out_time);
                                if (!empty($timeFirst) && !empty($timeSecond)) {
                                    @$differenceInSeconds = secToHR($timeSecond - $timeFirst);
                                    $file['This Dealer Hours'] =  @$differenceInSeconds['hours'] . ' hours ' . @$differenceInSeconds['minutes'] . ' minutes';
                                } else {
                                    $notmarked += 1;
                                    $file['This Dealer Hours'] = 'Attendance Not Marked';
                                }


                                if (!empty($timeFirst) && !empty($timeSecond)) {
                                    @$data_total_hours = day_hours($value->user_id, $value->date);
                                    $file['Total Hours Today'] = @$data_total_hours['hours'] . ' hours ' .
                                        @$data_total_hours['minutes'] . ' minutes';

                                    if ((@$data_total_hours['hours'] < 6 && (@$data_total_hours['hours'] >= 3))) {
                                        $half += 1;
                                    } elseif (@$data_total_hours['hours'] > 6) {
                                        $full += 1;
                                    } elseif (@$data_total_hours['hours'] < 3) {
                                        $three += 1;
                                    }
                                } else {
                                    $file['Total Hours Today'] = 'Attendance Not Marked';
                                }

                                $range = 'A' . $i . ':' . 'G' . $i;
                                $sheet->setBorder($range);
                                $sheet->cells($range, function ($cells) {
                                    $cells->setBorder('thin', 'thin', 'thin', 'thin');
                                });


                                $i = $i + 1;
                            } else {
                                $range = 'A' . $i . ':' . 'G' . $i;
                                $sheet->setBorder($range);
                                $sheet->cells($range, function ($cells) {
                                    $cells->setBorder('thin', 'thin', 'thin', 'thin');
                                });

                                $sheet->cells('B' . $i . ':' . 'G' . $i, function ($cells) {
                                    $cells->setBackground('#dd4b39');
                                });

                                $sheet->mergeCells('B' . $i . ':' . 'G' . $i);
                                $sheet->cell('B' . $i . ':' . 'G' . $i, function ($cell) {
                                    $cell->setAlignment('center');
                                });
                                $i = $i + 1;

                                $file['In Time'] = 'Absent';
                                $file['Out Time'] = 'Absent';
                                $file['User'] = 'Absent';
                                $file['Dealer'] = 'Absent';
                                $file['This Dealer Hours'] = 'Absent';
                                $file['Total Hours Today'] = 'Absent';
                            }
                            $result[] = $file;
                        }
                    }

                    $sheet->setCellValue('A' . $i, 'Total Hours This Month :');
                    $sheet->mergeCells('A' . $i . ':' . 'E' . $i);
                    $sheet->setBorder('A' . $i . ':' . 'E' . $i);
                    $sheet->cell('A' . $i . ':' . 'E' . $i, function ($cell) {
                        $cell->setAlignment('center');
                        $cell->setBorder('thin', 'thin', 'thin', 'thin');
                    });

                    @$mydata1 = month_hours($myid);
                    $month_hours =  @$mydata1['hours'] . ' hours ' . @$mydata1['minutes'] . ' minutes';
                    $sheet->setCellValue('F' . $i, $month_hours);
                    $sheet->mergeCells('F' . $i . ':' . 'G' . $i);
                    $sheet->setBorder('F' . $i . ':' . 'G' . $i);
                    $sheet->cell('F' . $i . ':' . 'G' . $i, function ($cell) {
                        $cell->setAlignment('center');
                        $cell->setBorder('thin', 'thin', 'thin', 'thin');
                    });

                    $i1 = $i + 1;
                    $sheet->setCellValue('A' . $i1, 'Total Full Days');
                    $sheet->mergeCells('A' . $i1 . ':' . 'E' . $i1);
                    $sheet->setBorder('A' . $i1 . ':' . 'E' . $i1);
                    $sheet->cell('A' . $i1 . ':' . 'E' . $i1, function ($cell) {
                        $cell->setAlignment('center');
                        $cell->setBorder('thin', 'thin', 'thin', 'thin');
                    });

                    $sheet->setCellValue('F' . $i1, $full);
                    $sheet->mergeCells('F' . $i1 . ':' . 'G' . $i1);
                    $sheet->setBorder('F' . $i1 . ':' . 'G' . $i1);
                    $sheet->cell('F' . $i1 . ':' . 'G' . $i1, function ($cell) {
                        $cell->setAlignment('center');
                        $cell->setBorder('thin', 'thin', 'thin', 'thin');
                    });

                    $i2 = $i + 2;
                    $sheet->setCellValue('A' . $i2, 'Total Half Days :');
                    $sheet->mergeCells('A' . $i2 . ':' . 'E' . $i2);
                    $sheet->setBorder('A' . $i2 . ':' . 'E' . $i2);
                    $sheet->cell('A' . $i2 . ':' . 'E' . $i2, function ($cell) {
                        $cell->setAlignment('center');
                        $cell->setBorder('thin', 'thin', 'thin', 'thin');
                    });

                    $sheet->setCellValue('F' . $i2, $half);
                    $sheet->mergeCells('F' . $i2 . ':' . 'G' . $i2);
                    $sheet->setBorder('F' . $i2 . ':' . 'G' . $i2);
                    $sheet->cell('F' . $i2 . ':' . 'G' . $i2, function ($cell) {
                        $cell->setAlignment('center');
                        $cell->setBorder('thin', 'thin', 'thin', 'thin');
                    });

                    $i3 = $i + 3;
                    $sheet->setCellValue('A' . $i3, 'Working Hours Less Than 3 :');
                    $sheet->mergeCells('A' . $i3 . ':' . 'E' . $i3);
                    $sheet->setBorder('A' . $i3 . ':' . 'E' . $i3);
                    $sheet->cell('A' . $i3 . ':' . 'E' . $i3, function ($cell) {
                        $cell->setAlignment('center');
                        $cell->setBorder('thin', 'thin', 'thin', 'thin');
                    });

                    $sheet->setCellValue('F' . $i3, $three);
                    $sheet->mergeCells('F' . $i3 . ':' . 'G' . $i3);
                    $sheet->setBorder('F' . $i3 . ':' . 'G' . $i3);
                    $sheet->cell('F' . $i3 . ':' . 'G' . $i3, function ($cell) {
                        $cell->setAlignment('center');
                        $cell->setBorder('thin', 'thin', 'thin', 'thin');
                    });

                    $i4 = $i + 4;
                    $sheet->setCellValue('A' . $i4, 'Days Attendance Not Marked :');
                    $sheet->mergeCells('A' . $i4 . ':' . 'E' . $i4);
                    $sheet->setBorder('A' . $i4 . ':' . 'E' . $i4);
                    $sheet->cell('A' . $i4 . ':' . 'E' . $i4, function ($cell) {
                        $cell->setAlignment('center');
                        $cell->setBorder('thin', 'thin', 'thin', 'thin');
                    });


                    $sheet->setCellValue('F' . $i4, @$notmarked);
                    $sheet->mergeCells('F' . $i4 . ':' . 'G' . $i4);
                    $sheet->setBorder('F' . $i4 . ':' . 'G' . $i4);
                    $sheet->cell('F' . $i4 . ':' . 'G' . $i4, function ($cell) {
                        $cell->setAlignment('center');
                        $cell->setBorder('thin', 'thin', 'thin', 'thin');
                    });



                    $sheet->setCellValue('H' . 1, 'Name :');
                    $sheet->mergeCells('H' . 1 . ':' . 'J' . 1);
                    $sheet->setBorder('H' . 1 . ':' . 'J' . 1);
                    $sheet->cell('H' . 1 . ':' . 'J' . 1, function ($cell) {
                        $cell->setAlignment('center');
                        $cell->setBorder('thin', 'thin', 'thin', 'thin');
                    });

                    $sheet->setCellValue('K' . 1, get_name($id));
                    $sheet->mergeCells('K' . 1 . ':' . 'M' . 1);
                    $sheet->setBorder('K' . 1 . ':' . 'M' . 1);
                    $sheet->cell('K' . 1 . ':' . 'M' . 1, function ($cell) {
                        $cell->setAlignment('center');
                        $cell->setBorder('thin', 'thin', 'thin', 'thin');
                    });

                    $sheet->setCellValue('H' . 2, 'Employee Code : ');
                    $sheet->mergeCells('H' . 2 . ':' . 'J' . 2);
                    $sheet->setBorder('H' . 2 . ':' . 'J' . 2);
                    $sheet->cell('H' . 2 . ':' . 'J' . 2, function ($cell) {
                        $cell->setAlignment('center');
                        $cell->setBorder('thin', 'thin', 'thin', 'thin');
                    });

                    $sheet->setCellValue('K' . 2, get_emp_code($id));
                    $sheet->mergeCells('K' . 2 . ':' . 'M' . 2);
                    $sheet->setBorder('K' . 2 . ':' . 'M' . 2);
                    $sheet->cell('K' . 2 . ':' . 'M' . 2, function ($cell) {
                        $cell->setAlignment('center');
                        $cell->setBorder('thin', 'thin', 'thin', 'thin');
                    });

                    $sheet->setCellValue('H' . 3, 'Current Dealer : ');
                    $sheet->mergeCells('H' . 3 . ':' . 'J' . 3);
                    $sheet->setBorder('H' . 3 . ':' . 'J' . 3);
                    $sheet->cell('H' . 3 . ':' . 'J' . 3, function ($cell) {
                        $cell->setAlignment('center');
                        $cell->setBorder('thin', 'thin', 'thin', 'thin');
                    });

                    $sheet->setCellValue('K' . 3, get_dealer_name_by_id($id));
                    $sheet->mergeCells('K' . 3 . ':' . 'M' . 3);
                    $sheet->setBorder('K' . 3 . ':' . 'M' . 3);
                    $sheet->cell('K' . 3 . ':' . 'M' . 3, function ($cell) {
                        $cell->setAlignment('center');
                        $cell->setBorder('thin', 'thin', 'thin', 'thin');
                    });

                    $sheet->setCellValue('H' . 1, 'Name :');
                    $sheet->mergeCells('H' . 1 . ':' . 'J' . 1);
                    $sheet->setBorder('H' . 1 . ':' . 'J' . 1);
                    $sheet->cell('H' . 1 . ':' . 'J' . 1, function ($cell) {
                        $cell->setAlignment('center');
                        $cell->setBorder('thin', 'thin', 'thin', 'thin');
                    });

                    $sheet->setCellValue('K' . 1, get_name($id));
                    $sheet->mergeCells('K' . 1 . ':' . 'M' . 1);
                    $sheet->setBorder('K' . 1 . ':' . 'M' . 1);
                    $sheet->cell('K' . 1 . ':' . 'M' . 1, function ($cell) {
                        $cell->setAlignment('center');
                        $cell->setBorder('thin', 'thin', 'thin', 'thin');
                    });

                    $sheet->setCellValue('H' . 2, 'Employee Code : ');
                    $sheet->mergeCells('H' . 2 . ':' . 'J' . 2);
                    $sheet->setBorder('H' . 2 . ':' . 'J' . 2);
                    $sheet->cell('H' . 2 . ':' . 'J' . 2, function ($cell) {
                        $cell->setAlignment('center');
                        $cell->setBorder('thin', 'thin', 'thin', 'thin');
                    });

                    $sheet->setCellValue('K' . 2, get_emp_code($id));
                    $sheet->mergeCells('K' . 2 . ':' . 'M' . 2);
                    $sheet->setBorder('K' . 2 . ':' . 'M' . 2);
                    $sheet->cell('K' . 2 . ':' . 'M' . 2, function ($cell) {
                        $cell->setAlignment('center');
                        $cell->setBorder('thin', 'thin', 'thin', 'thin');
                    });

                    $sheet->setCellValue('H' . 3, 'Current Dealer : ');
                    $sheet->mergeCells('H' . 3 . ':' . 'J' . 3);
                    $sheet->setBorder('H' . 3 . ':' . 'J' . 3);
                    $sheet->cell('H' . 3 . ':' . 'J' . 3, function ($cell) {
                        $cell->setAlignment('center');
                        $cell->setBorder('thin', 'thin', 'thin', 'thin');
                    });

                    $sheet->setCellValue('K' . 3, get_dealer_name_by_id($id));
                    $sheet->mergeCells('K' . 3 . ':' . 'M' . 3);
                    $sheet->setBorder('K' . 3 . ':' . 'M' . 3);
                    $sheet->cell('K' . 3 . ':' . 'M' . 3, function ($cell) {
                        $cell->setAlignment('center');
                        $cell->setBorder('thin', 'thin', 'thin', 'thin');
                    });


                    $range = 'A1:G1';
                    $sheet->setBorder($range);
                    $sheet->cells($range, function ($cells) {
                        $cells->setBorder('thin', 'thin', 'thin', 'thin');
                        $cells->setFontWeight('bold');
                    });

                    $sheet->fromArray($result);
                });
            })->export('xlsx');
        }

        // $result = DB::table('attendance')->where('user_id', $id)->whereMonth('date', date('m'))->get();
        return view('admin/view_attendance')->with(['result' => $data, 'id' => $id]);
    }

    public function daily_attendance($dealer_id = null)
    {
        $total_present = 0;
        if (!empty($dealer_id)) {
            $employees =  DB::table('users')->where('dealer_id', $dealer_id)->orwhere('dealer_office', $dealer_id)->get();
            if (count($employees) == 0) {
                return back()->with('error', 'No Data Found');
            }
            foreach ($employees as $key => $value) {

                if (isset($_GET['selectDate']) && !empty($_GET['selectDate'])) {
                    $employees_present =  DB::table('attendance')->where('user_id', $value->id)->where('date', $_GET['selectDate'])->first();
                } else {
                    $employees_present =  DB::table('attendance')->where('user_id', $value->id)->where('date', date('Y-m-d'))->first();
                }

                if (!empty($employees_present)) {
                    $employees[$key]->present_status = "Present";
                } else {
                    $employees[$key]->present_status = "Absent";
                }
            }
            return view('rsm/dailyattendance')->with('employees', $employees);
        } else {

            // $getStaff = User::join('staff_detail', 'users.id', '=', 'staff_detail.user_id')
            //     // ->join('emp_hierarchy', 'emp_hierarchy.user_id', '=', 'users.id')
            //     ->select('*', 'users.id as user_id')
            //     ->whereIn('role', [3, 4])
            //     ->whereIn('dealer_id', $dealers)
            //     ->where('reporting_authority', $user_id)
            //     ->get();
            // $staff = array();
            // foreach ($getStaff as $k => $val) {
            //     $staff[] = $getStaff[$k]->user_id;
            // }

            // $result = DB::table('users')->whereIn('role', [3, 4])->where('dealer_id', '!=', null)->groupBy('dealer_id')->select('dealer_id')->get()->toArray();

            // $result1 = DB::table('users')->whereIn('role', [3, 4, 5])->where('dealer_office', '!=', null)->groupBy('dealer_office')->select('dealer_office')->get()->toArray();

            // $result = array_merge($result, $result1);
            $user_id = Auth::id();
            $getDealers = DB::table('users')
                ->where(['role' => 2, 'status' => 1])
                ->select('id', 'reporting_authority')
                ->get();
            foreach ($getDealers as $key => $value) {
                $authorities = explode(",", $getDealers[$key]->reporting_authority);
                if (in_array($user_id, $authorities)) {
                    $dealers[] = $getDealers[$key]->id;
                }
            }
            $result = $dealers;
            // dd($result);

            foreach ($result as $key => $value) {
                // if (isset($result[$key]->dealer_id)) {
                $employees =  DB::table('users')->whereIn('role', [3, 4])->where('dealer_id', $result[$key])->get();
                // } elseif (isset($result[$key]->dealer_office)) {
                //     // dd($result[$key]->dealer_office);
                //     $employees =  DB::table('users')->whereIn('role', [3, 4, 5])->where('dealer_office', $result[$key])->get();


                // if (!isset($result[$key]->dealer_id)) {
                //     $result[$key]->dealer_id = $result[$key]->dealer_office;
                // }

                foreach ($employees as $key1 => $value1) {
                    if (isset($_GET['selectDate']) && !empty($_GET['selectDate'])) {
                        $employees_present =  DB::table('attendance')->where('user_id', $employees[$key1]->id)->where('date', $_GET['selectDate'])->first();
                    } else {
                        $employees_present =  DB::table('attendance')->where('user_id', $employees[$key1]->id)->where('date', date('Y-m-d'))->first();
                    }

                    if (!empty($employees_present)) {
                        $total_present += 1;
                    }
                }
                $result[$key] = new \stdClass();
                $result[$key]->dealer_id = $value;
                $result[$key]->total = $employees;
                $result[$key]->present = $total_present;
                $total_present = 0;
            }
        }
        // dd($result);

        return view('rsm/dailyattendance')->with('result', $result);
    }
}