<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Session;
use phpDocumentor\Reflection\DocBlock\Tags\See;
use App\Http\Traits\Helper;
use DataTables;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Crypt;
use App\Models\User;
use App\Models\MSite;
use App\Models\MDevice;
use App\Models\MCountPeople;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Csv;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

class CDashboard extends Controller
{    
    public function index()
    {   
        
        config(['database.default' => 'user_connection']);
        $user = Auth::user();
        $id_user = $user->id;
        $user = User::getUserById($id_user)->first();
        $id_level = $user->id_level;
        $str_list_site = $user->list_user;
        if ($id_level != 4 || $str_list_site == null)
        {
            $site = MSite::selectDataTables(0,"")->get();
            $device = MDevice::selectCameraOffv1(0,"");
        } else {
            $site_ids = explode(',',$str_list_site);
            $site = MSite::selectWhereIn($site_ids)->get();
            $device = MDevice::selectCameraOff(0,"",$site_ids)->get();
        }

        foreach($device as $item){
            if ($item->status == 0)
            {
                $item->status_msg = "Online";
            } else if ($item->status == 1)
            {
                $item->status_msg = "Offline";
            } else if ($item->status == 2)
            {
                $item->status_msg = "Offline";
            } else if ($item->status == 3)
            {
                $item->status_msg = "Offline";
            } else if ($item->status == 4)
            {
                $item->status_msg = "Offline";
            } else if ($item->status == 5)
            {
                $item->status_msg = "Online";
            } else if ($item->status == 6)
            {
                $item->status_msg = "Offline";
            } else {
                $item->status_msg = "Offline";
            }
            if($item->last_report != null){
                $last_report = strtotime($item->last_report);
                //strtotime
                $item->last_update = date('Y-m-d H:i',$last_report);
            } else {
                $item->last_update = "-";
            }
        }
        $i = 0;
        $all_id = [];
        $cek_invalid = 0;
        $cek_cracking = 0;
        $is_dev = config('constants.is_dev');
        $site_valid = [];
        
        foreach($site as $item){
            if($item->key_outlet != null && $item->key_outlet != ''){
                if($is_dev){
                    $key = config('constants.api_key_dev');
                    $check_key = $this->_decrypt_key($item->key_outlet,$key);
                    //dd($item->key_outlet);
                    $ket_rm_salt =substr($check_key, 0, -10);
                    $last10Characters = substr($check_key, -10);
                    if($ket_rm_salt == $item->site_code){
                        if($last10Characters == "develop123"){
                            $cek_duplicate = MSite::cekDuplicate($item->site_id,$item->site_code, $item->key_outlet);
                            if($cek_duplicate[0]->count == 0){
                                $item->status_msg = "Valid Key";
                                $item->status = 1;
                                $all_id[$i] = $item->site_id;
                                $i++;
                                array_push($site_valid,$item);
                            } else {
                                $item->status_msg = "Invalid Key";
                                $item->status = 2;
                                $cek_invalid = 1;
                                $cek_cracking = 1;
                            }
                        } else {
                            $item->status_msg = "Invalid Key";
                            $item->status = 2;
                            $cek_invalid = 1;
                        }
                    } else {
                        $item->status_msg = "Invalid Key";
                        $item->status = 2;
                        $cek_invalid = 1;
                    }
                } else {
                    $key = config('constants.api_key_prod');
                    $check_key = $this->_decrypt_key($item->key_outlet,$key);
                    //dd($check_key);
                    $ket_rm_salt =substr($check_key, 0, -10);
                    if($ket_rm_salt == $item->site_code){
                        $cek_duplicate = MSite::cekDuplicate($item->site_id,$item->site_code, $item->key_outlet);
                        if($cek_duplicate[0]->count == 0){
                            $item->status_msg = "Valid Key";
                            $item->status = 1;
                            $all_id[$i] = $item->site_id;
                            $i++;
                            array_push($site_valid,$item);
                        } else {
                            $item->status_msg = "Invalid Key";
                            $item->status = 2;
                            $cek_invalid = 1;
                            $cek_cracking = 1;
                        }
                    } else {
                        $item->status_msg = "Invalid Key";
                        $item->status = 2;
                        $cek_invalid = 1;
                    }
                }
            } else {
                $item->status_msg = "Invalid Key";
                $item->status = 2;
                $cek_invalid = 1;
            }
        }
        
        if($cek_cracking == 1){
            $site_valid = [];
            
        }
        $all_id_f = array_unique($all_id);
        $is_lite = config('constants.is_lite');
        $data_device = MDevice::selectBySite($all_id_f)->get();
        $camera_off_count = count($device);
        //dd($data_device);
        return view('dashboard.index')
            ->with('is_lite',$is_lite)
            ->with('camera_off_count',$camera_off_count)
            ->with('data_device',$data_device)
            ->with('all_device',$device)
            ->with('site_valid',$site_valid)
            ->with('cek_invalid',$cek_invalid)
            ->with('titlepage','Dashboard')
            ->with('title','Camera');
    }

    public function dashboardTotalData(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime(str_replace('/', '-', $request->date_end));
        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);
        $start_date = $date.' '.$request->from;
        $end_date = $date_end.' '.$request->to;
        $site_ids = $request->site;
        $device_ids = $request->device;
        $data = MCountPeople::SelectTotalDataDashboardV2($start_date,$end_date,$site_ids,$device_ids)->get();
        return response()->json(['message'=>'','code'=>1,'TotalData'=>$data]);
    }
    public function dashboardDailyLocation(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        //$originalDateEnd = strtotime(str_replace('/', '-', $request->date_end));
        $date = date('Y-m-d',$originalDate);
        //$date_end = date('Y-m-d',$originalDateEnd);
        $startDate = date('d/m/Y',$originalDate);
        $start_date = $date.' '.$request->from;
        $end_date = $date.' '.$request->to;
        $site_ids = $request->site;
        $device_ids = $request->device;
        $data = MCountPeople::SelectDailyLocationDashboardV2($start_date,$end_date,$site_ids,$device_ids)->get();
        return response()->json(['message'=>'','code'=>1,'DailyLocation'=>$data,'startDate'=>$startDate,'endDate'=>$startDate]);
    }
    public function dashboardWeeklyLocation(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime('-7 days',strtotime(str_replace('/', '-', $request->date)));
        $date = date('Y-m-d',$originalDateEnd);
        $date_end = date('Y-m-d',$originalDate);
        $startDate = date('d/m/Y',$originalDateEnd);
        $endDate = date('d/m/Y',$originalDate);
        $start_date = $date.' '.$request->from;
        $end_date = $date_end.' '.$request->to;
        $site_ids = $request->site;
        $device_ids = $request->device;
        $data = MCountPeople::SelectDailyLocationDashboardV2($start_date,$end_date,$site_ids,$device_ids)->get();
        return response()->json(['message'=>'','code'=>1,'WeeklyLocation'=>$data,'startDate'=>$startDate,'endDate'=>$endDate]);
    }
    public function dashboardMonthlyLocation(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime('-30 days',strtotime(str_replace('/', '-', $request->date)));
        $date = date('Y-m-d',$originalDateEnd);
        $date_end = date('Y-m-d',$originalDate);
        $startDate = date('d/m/Y',$originalDateEnd);
        $endDate = date('d/m/Y',$originalDate);
        $start_date = $date.' '.$request->from;
        $end_date = $date_end.' '.$request->to;
        $site_ids = $request->site;
        $device_ids = $request->device;
        $data = MCountPeople::SelectDailyLocationDashboardV2($start_date,$end_date,$site_ids,$device_ids)->get();
        return response()->json(['message'=>'','code'=>1,'MonthlyLocation'=>$data,'startDate'=>$startDate,'endDate'=>$endDate]);
    }
    public function dashboardYearlyLocation(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime('-1 year',strtotime(str_replace('/', '-', $request->date_end)));
        $date = date('Y-m-d',$originalDateEnd);
        $date_end = date('Y-m-d',$originalDate);
        $startDate = date('d/m/Y',$originalDateEnd);
        $endDate = date('d/m/Y',$originalDate);
        $start_date = $date.' '.$request->from;
        $end_date = $date_end.' '.$request->to;
        $site_ids = $request->site;
        $device_ids = $request->device;
        $data = MCountPeople::SelectDailyLocationDashboardV2($start_date,$end_date,$site_ids,$device_ids)->get();
        return response()->json(['message'=>'','code'=>1,'YearlyLocation'=>$data,'startDate'=>$startDate,'endDate'=>$endDate]);
    }

    public function dashboardDailyTrendCustom(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime(str_replace('/', '-', $request->date_end));
        
        $dc1 = date_create(str_replace('/', '-', $request->date));
        $dc2 = date_create(str_replace('/', '-', $request->date_end));
        $dateDiff = date_diff($dc1,$dc2);
        $yesterdayStart = strtotime('-'.$dateDiff->d.' days',strtotime(str_replace('/', '-', $request->date)));

        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);
        $date_yesterday = date('Y-m-d',$yesterdayStart);

        $startDate = date('d/m/Y',$originalDateEnd);
        $endDate = date('d/m/Y',$originalDate);

        $start_date = $date.' '.$request->from;
        $end_date = $date_end.' '.$request->to;
        $start_yesterday = $date_yesterday.' '.$request->from;
        $end_yesterday = $date.' '.$request->to;

        $site_ids = $request->site;
        $device_ids = $request->device;
        $data = MCountPeople::SelectMonthlyTrendV2($start_date,$end_date,$site_ids,$device_ids)->get();
        $YesterdayData = MCountPeople::SelectMonthlyTrendV2($start_yesterday,$end_yesterday,$site_ids,$device_ids)->get();
        return response()->json(['message'=>'','code'=>1,'DailyTrend'=>$data,'YesterdayTrend'=>$YesterdayData,'startDate'=>$startDate,'endDate'=>$endDate]);
    }

    public function dashboardDailyTrend(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime(str_replace('/', '-', $request->date_end));
        
        $yesterdayStart = strtotime('-1 days',strtotime(str_replace('/', '-', $request->date)));

        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);
        $date_yesterday = date('Y-m-d',$yesterdayStart);

        $startDate = date('d/m/Y',$originalDate);
        $endDate = date('d/m/Y',$yesterdayStart);

        $start_date = $date.' '.$request->from;
        $end_date = $date.' '.$request->to;
        $start_yesterday = $date_yesterday.' '.$request->from;
        $end_yesterday = $date_yesterday.' '.$request->to;

        $site_ids = $request->site;
        $device_ids = $request->device;
        $data = MCountPeople::SelectDailyTrendv3($start_date,$end_date,$site_ids,$device_ids)->get();
        $YesterdayData = MCountPeople::SelectDailyTrendv3($start_yesterday,$end_yesterday,$site_ids,$device_ids)->get();
        return response()->json(['message'=>'','code'=>1,'DailyTrend'=>$data,'YesterdayTrend'=>$YesterdayData,'startDate'=>$startDate,'endDate'=>$endDate]);
    }

    public function dashboardWeeklyTrendV2(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime(str_replace('/', '-', $request->date_end));
        
        $dc1 = date_create(str_replace('/', '-', $request->date));
        $dc2 = date_create(str_replace('/', '-', $request->date_end));
        $dateDiff = date_diff($dc1,$dc2);

        $yesterdayStart = strtotime('-'.$dateDiff->d.' days',strtotime(str_replace('/', '-', $request->date)));

        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);
        $date_yesterday = date('Y-m-d',$yesterdayStart);

        $startDate = date('d/m/Y',$originalDate);
        $endDate = date('d/m/Y',$originalDateEnd);

        $start_date = $date.' '.$request->from;
        $end_date = $date.' '.$request->to;
        $start_yesterday = $date_yesterday.' '.$request->from;
        $end_yesterday = $date_yesterday.' '.$request->to;

        $site_ids = $request->site;
        $device_ids = $request->device;
        $data = MCountPeople::SelectWeeklyTrendV3($start_date,$end_date,$site_ids,$device_ids)->get();
        $YesterdayData = MCountPeople::SelectWeeklyTrendV3($start_yesterday,$end_yesterday,$site_ids,$device_ids)->get();
        return response()->json(['message'=>'','code'=>1,'WeeklyTrend'=>$data,'LastWeeklyTrend'=>$YesterdayData,'startDate'=>$startDate,'endDate'=>$endDate]);
    }

    public function dashboardMonthlyTrendV2(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime(str_replace('/', '-', $request->date_end));
        
        $yesterdayStart = strtotime('-59 days',strtotime(str_replace('/', '-', $request->date)));
        $yesterdayEnd = strtotime('-29 days',strtotime(str_replace('/', '-', $request->date)));

        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);
        $date_yesterday = date('Y-m-d',$yesterdayStart);
        $end_yesterday = date('Y-m-d',$yesterdayEnd);

        $startDate = date('d/m/Y',$originalDate);
        $endDate = date('d/m/Y',$originalDateEnd);

        $start_date = $date.' '.$request->from;
        $end_date = $date_end.' '.$request->to;
        $start_yesterday = $date_yesterday.' '.$request->from;
        $end_yesterday = $date_yesterday.' '.$request->to;

        $site_ids = $request->site;
        $device_ids = $request->device;
        $data = MCountPeople::SelectMonthlyTrendV2($start_date,$end_date,$site_ids,$device_ids)->get();
        $YesterdayData = MCountPeople::SelectMonthlyTrendV2($start_yesterday,$end_yesterday,$site_ids,$device_ids)->get();
        return response()->json(['message'=>'','code'=>1,'MonthlyTrend'=>$data,'LastMonthlyTrend'=>$YesterdayData,'startDate'=>$startDate,'endDate'=>$endDate]);
    }

    public function dashboardYearlyTrendV2(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime(str_replace('/', '-', $request->date_end));
        
        $yesterdayStart = strtotime('-2 years',strtotime(str_replace('/', '-', $request->date)));
        $yesterdayEnd = strtotime('-1 year',strtotime(str_replace('/', '-', $request->date)));

        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);
        $date_yesterday = date('Y-m-d',$yesterdayStart);
        $end_yesterday = date('Y-m-d',$yesterdayEnd);

        $startDate = date('d/m/Y',$originalDate);
        $endDate = date('d/m/Y',$originalDateEnd);

        $start_date = $date.' '.$request->from;
        $end_date = $date_end.' '.$request->to;
        $start_yesterday = $date_yesterday.' '.$request->from;
        $end_yesterday = $date_yesterday.' '.$request->to;

        $site_ids = $request->site;
        $device_ids = $request->device;
        $data = MCountPeople::SelectYearlyTrendV2($start_date,$end_date,$site_ids,$device_ids)->get();
        $YesterdayData = MCountPeople::SelectYearlyTrendV2($start_yesterday,$end_yesterday,$site_ids,$device_ids)->get();
        return response()->json(['message'=>'','code'=>1,'YearlyTrend'=>$data,'LastYearlyTrend'=>$YesterdayData,'startDate'=>$startDate,'endDate'=>$endDate]);
    }

    public function peoplecountdata(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        //$originalDateEnd = strtotime(str_replace('/', '-', $request->date_end));
        $date = date('Y-m-d',$originalDate);
        //$date_end = date('Y-m-d',$originalDateEnd);
        $startDate = date('d/m/Y',$originalDate);
        $start_date = $date.' '.$request->from;
        $end_date = $date.' '.$request->to;
        $site_ids = $request->site;
        $device_ids = $request->device;
        $totalData = MCountPeople::SelectTotalDataDashboardV2($start_date,$end_date,$site_ids,$device_ids)->get();
        $data = MCountPeople::SelectPeopleCountDeviceV2($start_date,$end_date,$site_ids,$device_ids)->get();
        return response()->json(['message'=>'','code'=>1,'DailyLocation'=>$data,'TotalData'=>$totalData]);
    }

    public function tddaily(Request $request){
        config(['database.default' => 'user_connection']);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $yesterdayStart = strtotime('-1 days',strtotime(str_replace('/', '-', $request->date)));

        $date = date('Y-m-d',$originalDate);
        $date_yesterday = date('Y-m-d',$yesterdayStart);

        $startDate = date('d/m/Y',$originalDate);
        $yesterdayDate = date('d/m/Y',$yesterdayStart);

        $start_date = $date.' '.$request->from;
        $end_date = $date.' '.$request->to;
        $start_yesterday = $date_yesterday.' '.$request->from;
        $end_yesterday = $date_yesterday.' '.$request->to;

        $site_ids = $request->site;
        $device_ids = $request->device;

        $data = MCountPeople::SelectTDHourlyV2($start_date,$end_date,$site_ids,$device_ids)->get();
        $TotalData = MCountPeople::SelectTDHourlyTotalV2($start_date,$end_date,$site_ids,$device_ids)->get();
        $TotalYesterday = MCountPeople::SelectTDHourlyTotalV2($start_yesterday,$end_yesterday,$site_ids,$device_ids)->get();

        $i = 0;
        $arrName = [];
        $pivotTable = [];
        $pivotTableTotal = [];
        $pivotTableYesterday = [];

        foreach($TotalData as $row){
            $name = 'Total Daily';
            $inValue = $row->total_enter;
            $day = $row->hour.':00';
            if (!isset($pivotTableTotal[$name])) {
                $pivotTableTotal[$name] = array();
            }
            $pivotTableTotal[$name]['name/in/'.$day] = $inValue;
        }

        foreach($TotalYesterday as $row){
            $name = 'Total Yesterday';
            $inValue = $row->total_enter;
            $day = $row->hour.':00';
            if (!isset($pivotTableYesterday[$name])) {
                $pivotTableYesterday[$name] = array();
            }
            $pivotTableYesterday[$name]['name/in/'.$day] = $inValue;
        }
        
        foreach ($data as $row) {
            $arrName[$i] = $row->hour.':00';
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $day = $row->hour.':00';

            if (!isset($pivotTable[$name])) {
                $pivotTable[$name] = array();
            }
            $pivotTable[$name]['name/in/'.$day] = $inValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);

        $resultTotal = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTableTotal), $pivotTableTotal);

        $resultYesterday = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTableYesterday), $pivotTableYesterday);
        $removeDuplicate = array_unique($arrName);
        $uniqueNames = array_values($removeDuplicate);
        return response()->json(['message'=>'','code'=>1,'todayStringShow' => $startDate,'yesterdayStringShow' => $yesterdayDate
        ,'dataDaily' =>$result,'dataTotalDaily' =>$resultTotal,'dataTotalYesterday' =>$resultYesterday,'uniqueNames'=>$uniqueNames]);
    }

    public function tdweekly(Request $request){
        config(['database.default' => 'user_connection']);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime('-6 days',strtotime(str_replace('/', '-', $request->date)));
        $yesterdayStart = strtotime('-7 days',strtotime(str_replace('/', '-', $request->date)));
        $yesterdayEnd = strtotime('-13 days',strtotime(str_replace('/', '-', $request->date)));

        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);
        $date_yesterday = date('Y-m-d',$yesterdayStart);
        $end_yesterday = date('Y-m-d',$yesterdayEnd);

        $startDate = date('d/m/Y',$originalDate);
        $yesterdayDate = date('d/m/Y',$yesterdayEnd);

        $start_date = $date_end.' '.$request->from;
        $end_date = $date.' '.$request->to;
        $start_yesterday = $end_yesterday.' '.$request->from;
        $end_yesterday = $date_yesterday.' '.$request->to;

        $site_ids = $request->site;
        $device_ids = $request->device;

        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));

        $data = MCountPeople::SelectTDWeeklyV2($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $TotalData = MCountPeople::SelectTDWeeklyTotalV2($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $TotalYesterday = MCountPeople::SelectTDWeeklyTotalV2($start_yesterday,$end_yesterday,$site_ids,$device_ids,$start_time,$end_time)->get();

        $i = 0;
        $arrName = [];
        $pivotTable = [];
        $pivotTableTotal = [];
        $pivotTableYesterday = [];

        foreach($TotalData as $row){
            $name = 'Total Weekly';
            $inValue = $row->total_enter;
            $day = $row->day;
            if (!isset($pivotTableTotal[$name])) {
                $pivotTableTotal[$name] = array();
            }
            $pivotTableTotal[$name]['name/in/'.$day] = $inValue;
        }

        foreach($TotalYesterday as $row){
            $name = 'Total Last Week';
            $inValue = $row->total_enter;
            $day = $row->day;
            if (!isset($pivotTableYesterday[$name])) {
                $pivotTableYesterday[$name] = array();
            }
            $pivotTableYesterday[$name]['name/in/'.$day] = $inValue;
        }

        foreach ($data as $row) {
            $arrName[$i] = $row->day;
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $day = $row->day;

            if (!isset($pivotTable[$name])) {
                $pivotTable[$name] = array();
            }
            $pivotTable[$name]['name/in/'.$day] = $inValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);

        $resultTotal = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTableTotal), $pivotTableTotal);

        $resultYesterday = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTableYesterday), $pivotTableYesterday);
        $removeDuplicate = array_unique($arrName);
        $uniqueNames = array_values($removeDuplicate);
        return response()->json(['message'=>'','code'=>1,'todayStringShow' => $startDate,'yesterdayStringShow' => $yesterdayDate
        ,'dataDaily' =>$result,'dataTotalDaily' =>$resultTotal,'dataTotalYesterday' =>$resultYesterday,'uniqueNames'=>$uniqueNames]);
    }

    public function tdmonthly(Request $request){
        config(['database.default' => 'user_connection']);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime('-29 days',strtotime(str_replace('/', '-', $request->date)));
        $yesterdayStart = strtotime('-30 days',strtotime(str_replace('/', '-', $request->date)));
        $yesterdayEnd = strtotime('-59 days',strtotime(str_replace('/', '-', $request->date)));

        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);
        $date_yesterday = date('Y-m-d',$yesterdayStart);
        $end_yesterday = date('Y-m-d',$yesterdayEnd);

        $startDate = date('d/m/Y',$originalDate);
        $yesterdayDate = date('d/m/Y',$yesterdayEnd);

        $start_date = $date_end.' '.$request->from;
        $end_date = $date.' '.$request->to;
        $start_yesterday = $end_yesterday.' '.$request->from;
        $end_yesterday = $date_yesterday.' '.$request->to;

        $site_ids = $request->site;
        $device_ids = $request->device;

        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));

        $data = MCountPeople::SelectTDMonthlyV2($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $TotalData = MCountPeople::SelectTDMonthlyTotalV2($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $TotalYesterday = MCountPeople::SelectTDMonthlyTotalV2($start_yesterday,$end_yesterday,$site_ids,$device_ids,$start_time,$end_time)->get();

        $i = 0;
        $arrName = [];
        $pivotTable = [];
        $pivotTableTotal = [];
        $pivotTableYesterday = [];

        foreach($TotalData as $row){
            $name = 'Total Monthly';
            $inValue = $row->total_enter;
            $day = $row->day;
            if (!isset($pivotTableTotal[$name])) {
                $pivotTableTotal[$name] = array();
            }
            $pivotTableTotal[$name]['name/in/'.$day] = $inValue;
        }

        foreach($TotalYesterday as $row){
            $name = 'Total Last Month';
            $inValue = $row->total_enter;
            $day = $row->day;
            if (!isset($pivotTableYesterday[$name])) {
                $pivotTableYesterday[$name] = array();
            }
            $pivotTableYesterday[$name]['name/in/'.$day] = $inValue;
        }

        foreach ($data as $row) {
            $arrName[$i] = $row->day;
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $day = $row->day;

            if (!isset($pivotTable[$name])) {
                $pivotTable[$name] = array();
            }
            $pivotTable[$name]['name/in/'.$day] = $inValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);

        $resultTotal = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTableTotal), $pivotTableTotal);

        $resultYesterday = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTableYesterday), $pivotTableYesterday);
        $removeDuplicate = array_unique($arrName);
        $uniqueNames = array_values($removeDuplicate);
        return response()->json(['message'=>'','code'=>1,'todayStringShow' => $startDate,'yesterdayStringShow' => $yesterdayDate
        ,'dataDaily' =>$result,'dataTotalDaily' =>$resultTotal,'dataTotalYesterday' =>$resultYesterday,'uniqueNames'=>$uniqueNames]);
    }

    public function tdyearly(Request $request){
        config(['database.default' => 'user_connection']);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime('-1 year',strtotime(str_replace('/', '-', $request->date)));
        $yesterdayStart = strtotime('-1 year',strtotime(str_replace('/', '-', $request->date)));
        $yesterdayEnd = strtotime('-2 years',strtotime(str_replace('/', '-', $request->date)));

        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);
        $date_yesterday = date('Y-m-d',$yesterdayStart);
        $end_yesterday = date('Y-m-d',$yesterdayEnd);

        $startDate = date('d/m/Y',$originalDate);
        $yesterdayDate = date('d/m/Y',$yesterdayEnd);

        $start_date = $date_end.' '.$request->from;
        $end_date = $date.' '.$request->to;
        $start_yesterday = $end_yesterday.' '.$request->from;
        $end_yesterday = $date_yesterday.' '.$request->to;

        $site_ids = $request->site;
        $device_ids = $request->device;

        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));

        $data = MCountPeople::SelectTDYearlyV2($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $TotalData = MCountPeople::SelectTDYearlyTotalV2($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $TotalYesterday = MCountPeople::SelectTDYearlyTotalV2($start_yesterday,$end_yesterday,$site_ids,$device_ids,$start_time,$end_time)->get();

        $i = 0;
        $arrName = [];
        $pivotTable = [];
        $pivotTableTotal = [];
        $pivotTableYesterday = [];

        foreach($TotalData as $row){
            $name = 'Total Yearly';
            $inValue = $row->total_enter;
            $day = $row->month . " - " . $row->year;
            if (!isset($pivotTableTotal[$name])) {
                $pivotTableTotal[$name] = array();
            }
            $pivotTableTotal[$name]['name/in/'.$day] = $inValue;
        }

        foreach($TotalYesterday as $row){
            $name = 'Total Last Year';
            $inValue = $row->total_enter;
            $day = $row->month . " - " . $row->year;
            if (!isset($pivotTableYesterday[$name])) {
                $pivotTableYesterday[$name] = array();
            }
            $pivotTableYesterday[$name]['name/in/'.$day] = $inValue;
        }

        foreach ($data as $row) {
            $arrName[$i] = $row->month . " - " . $row->year;
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $day = $row->month . " - " . $row->year;

            if (!isset($pivotTable[$name])) {
                $pivotTable[$name] = array();
            }
            $pivotTable[$name]['name/in/'.$day] = $inValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);

        $resultTotal = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTableTotal), $pivotTableTotal);

        $resultYesterday = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTableYesterday), $pivotTableYesterday);
        $removeDuplicate = array_unique($arrName);
        $uniqueNames = array_values($removeDuplicate);
        return response()->json(['message'=>'','code'=>1,'todayStringShow' => $startDate,'yesterdayStringShow' => $yesterdayDate
        ,'dataDaily' =>$result,'dataTotalDaily' =>$resultTotal,'dataTotalYesterday' =>$resultYesterday,'uniqueNames'=>$uniqueNames]);
    }

    public function drhourly(Request $request){
        config(['database.default' => 'user_connection']);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime(str_replace('/', '-', $request->date_stop));
        
        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);

        $start_date = $date.' '.$request->from;
        $end_date = $date_end.' '.$request->to;

        $site_ids = $request->site;
        $device_ids = $request->device;

        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));
        $data = MCountPeople::SelectDRHourlyV3($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $i = 0;
        $arrName = [];
        $pivotTable = [];
        foreach ($data as $row) {
            $arrName[$i] = $row->site_name;
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $outValue = $row->total_exit;
            $hour = $row->hour;

            if (!isset($pivotTable[$hour])) {
                $pivotTable[$hour] = array();
            }
            $pivotTable[$hour]['name/in/'.$name] = $inValue;
            $pivotTable[$hour]['name/out/'.$name] = $outValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    $newKey = str_replace('name/out/', '', $key);
                    $nameOut[$newKey] = $value;
                }
            }
        
            return [
                'hour' => $storeName,
                'name_in' => $nameIn,
                'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);
        $uniqueNames = array_unique($arrName);
        return response()->json(['message'=>'','code'=>1,'dataHourly' => $result,'uniqueNames'=>$uniqueNames]);
    }

    public function drcustom(Request $request){
        config(['database.default' => 'user_connection']);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime(str_replace('/', '-', $request->date_stop));
        
        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);

        $start_date = $date.' '.$request->from;
        $end_date = $date_end.' '.$request->to;

        $site_ids = $request->site;
        $device_ids = $request->device;

        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));
        $data = MCountPeople::SelectDRCustomHV2($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        
        return response()->json(['message'=>'','code'=>1,'dataHourly' => $data]);
    }

    public function drdaily(Request $request){
        config(['database.default' => 'user_connection']);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime(str_replace('/', '-', $request->date_stop));
        
        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);

        $start_date = $date.' '.$request->from;
        $end_date = $date_end.' '.$request->to;

        $site_ids = $request->site;
        $device_ids = $request->device;

        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));
        $data = MCountPeople::SelectDRDailyV3($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $i = 0;
        $arrName = [];
        $pivotTable = [];
        foreach ($data as $row) {
            $arr_day = explode("-",$row->day);
            $arrName[$i] = $row->site_name;
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $outValue = $row->total_exit;
            $day = $arr_day[2] . "/" . $arr_day[1] . "/" . $arr_day[0];

            if (!isset($pivotTable[$day])) {
                $pivotTable[$day] = array();
            }
            $pivotTable[$day]['name/in/'.$name] = $inValue;
            $pivotTable[$day]['name/out/'.$name] = $outValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    $newKey = str_replace('name/out/', '', $key);
                    $nameOut[$newKey] = $value;
                }
            }
        
            return [
                'day' => $storeName,
                'name_in' => $nameIn,
                'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);
        $uniqueNames = array_unique($arrName);
        return response()->json(['message'=>'','code'=>1,'dataHourly' => $result,'uniqueNames'=>$uniqueNames]);
    }

    public function drweekly(Request $request){
        config(['database.default' => 'user_connection']);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime(str_replace('/', '-', $request->date_stop));
        
        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);

        $start_date = $date.' '.$request->from;
        $end_date = $date_end.' '.$request->to;

        $site_ids = $request->site;
        $device_ids = $request->device;

        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));
        $data = MCountPeople::SelectDRWeeklyV3($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $i = 0;
        $arrName = [];
        $pivotTable = [];
        foreach ($data as $row) {
            $arrName[$i] = $row->site_name;
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $outValue = $row->total_exit;
            $week = "Week " . $row->week . " - " . $row->month;

            if (!isset($pivotTable[$week])) {
                $pivotTable[$week] = array();
            }
            $pivotTable[$week]['name/in/'.$name] = $inValue;
            $pivotTable[$week]['name/out/'.$name] = $outValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    $newKey = str_replace('name/out/', '', $key);
                    $nameOut[$newKey] = $value;
                }
            }
        
            return [
                'week' => $storeName,
                'name_in' => $nameIn,
                'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);
        $uniqueNames = array_unique($arrName);
        return response()->json(['message'=>'','code'=>1,'dataHourly' => $result,'uniqueNames'=>$uniqueNames]);
    }

    public function drmonthly(Request $request){
        config(['database.default' => 'user_connection']);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime(str_replace('/', '-', $request->date_stop));
        
        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);

        $start_date = $date.' '.$request->from;
        $end_date = $date_end.' '.$request->to;

        $site_ids = $request->site;
        $device_ids = $request->device;

        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));
        $data = MCountPeople::SelectDRMonthlyV3($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $i = 0;
        $arrName = [];
        $pivotTable = [];
        foreach ($data as $row) {
            $arrName[$i] = $row->site_name;
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $outValue = $row->total_exit;
            $week = $row->month . " - " . $row->year;

            if (!isset($pivotTable[$week])) {
                $pivotTable[$week] = array();
            }
            $pivotTable[$week]['name/in/'.$name] = $inValue;
            $pivotTable[$week]['name/out/'.$name] = $outValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    $newKey = str_replace('name/out/', '', $key);
                    $nameOut[$newKey] = $value;
                }
            }
        
            return [
                'month' => $storeName,
                'name_in' => $nameIn,
                'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);
        $uniqueNames = array_unique($arrName);
        return response()->json(['message'=>'','code'=>1,'dataHourly' => $result,'uniqueNames'=>$uniqueNames]);
    }

    public function dryearly(Request $request){
        config(['database.default' => 'user_connection']);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime(str_replace('/', '-', $request->date_stop));
        
        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);

        $start_date = $date.' '.$request->from;
        $end_date = $date_end.' '.$request->to;

        $site_ids = $request->site;
        $device_ids = $request->device;

        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));
        $data = MCountPeople::SelectDRYearlyV3($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $i = 0;
        $arrName = [];
        $pivotTable = [];
        foreach ($data as $row) {
            $arrName[$i] = $row->site_name;
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $outValue = $row->total_exit;
            $week = $row->year . "";

            if (!isset($pivotTable[$week])) {
                $pivotTable[$week] = array();
            }
            $pivotTable[$week]['name/in/'.$name] = $inValue;
            $pivotTable[$week]['name/out/'.$name] = $outValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    $newKey = str_replace('name/out/', '', $key);
                    $nameOut[$newKey] = $value;
                }
            }
        
            return [
                'year' => $storeName,
                'name_in' => $nameIn,
                'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);
        $uniqueNames = array_unique($arrName);
        return response()->json(['message'=>'','code'=>1,'dataHourly' => $result,'uniqueNames'=>$uniqueNames]);
    }

    public function test_download(){
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Add some headers
        $sheet->setCellValue('A1', 'ID');
        $sheet->setCellValue('B1', 'Name');
        $sheet->setCellValue('C1', 'Email');
        $sheet->setCellValue('D1', 'Created At');

        // Fetch the data from the database
        //$users = \App\Models\User::all();
        $users = DB::table('users')->where('deleted',1)->get();
        // Start from the second row
        $row = 2;

        foreach ($users as $user) {
            $sheet->setCellValue('A' . $row, $user->id);
            $sheet->setCellValue('B' . $row, $user->name);
            $sheet->setCellValue('C' . $row, $user->email);
            $sheet->setCellValue('D' . $row, $user->created_at);
            $row++;
        }

        // Set the writer type (Xlsx or Csv)
        $writer = new Xlsx($spreadsheet);
        $fileName = 'users.xlsx';
        $tempFile = tempnam(sys_get_temp_dir(), $fileName);
        $writer->save($tempFile);

        // Return the file as a response
        return response()->download($tempFile, $fileName)->deleteFileAfterSend(true);
    }
    public function peoplecountxlsx(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        //$originalDateEnd = strtotime(str_replace('/', '-', $request->date_end));
        $date = date('Y-m-d',$originalDate);
        //$date_end = date('Y-m-d',$originalDateEnd);
        $startDate = date('d/m/Y',$originalDate);
        $start_date = $date.' '.$request->from;
        $end_date = $date.' '.$request->to;
        $site_id = $request->site;
        $device_id = $request->device;
        $tipe = $request->tipe;
        $site_ids = explode(",",$site_id);
        $device_ids = explode(",",$device_id);
        //$totalData = MCountPeople::SelectTotalDataDashboardV2($start_date,$end_date,$site_ids,$device_ids)->get();
        $data = MCountPeople::SelectPeopleCountDeviceV2($start_date,$end_date,$site_ids,$device_ids)->get();
        
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Add some headers
        $sheet->setCellValue('A1', 'Site');
        $sheet->setCellValue('B1', 'Sitename');
        $sheet->setCellValue('C1', 'Device ID');
        $sheet->setCellValue('D1', 'Device Name');
        if($tipe == 'all'){
            $sheet->setCellValue('E1', 'Total Enter');
            $sheet->setCellValue('F1', 'Total Exit');
        } else if($tipe == 1){
            $sheet->setCellValue('E1', 'Total Enter');
        } else if($tipe == 2){
            $sheet->setCellValue('E1', 'Total Exit');
        }
        $row = 2;
        // Set the writer type (Xlsx or Csv)
        foreach($data as $v){
            $sheet->setCellValue('A' . $row, $v->site_code);
            $sheet->setCellValue('B' . $row, $v->site_name);
            $sheet->setCellValue('C' . $row, $v->device_code);
            $sheet->setCellValue('D' . $row, $v->device_name);
            if($tipe == 'all'){
                $sheet->setCellValue('E' . $row, $v->total_enter);
                $sheet->setCellValue('F' . $row, $v->total_exit);
            } else if($tipe == 1){
                $sheet->setCellValue('E' . $row, $v->total_enter);
            } else if($tipe == 2){
                $sheet->setCellValue('E' . $row, $v->total_exit);
            }
            $row++;
        }
        $writer = new Xlsx($spreadsheet);
        $fileName = 'peoplecount_'.$date.'.xlsx';
        $tempFile = tempnam(sys_get_temp_dir(), $fileName);
        $writer->save($tempFile);

        // Return the file as a response
        return response()->download($tempFile, $fileName)->deleteFileAfterSend(true);
        //return response()->json(['message'=>'','code'=>1,'DailyLocation'=>$data,'TotalData'=>$totalData]);
    }

    public function peoplecountcsv(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        //$originalDateEnd = strtotime(str_replace('/', '-', $request->date_end));
        $date = date('Y-m-d',$originalDate);
        //$date_end = date('Y-m-d',$originalDateEnd);
        $startDate = date('d/m/Y',$originalDate);
        $start_date = $date.' '.$request->from;
        $end_date = $date.' '.$request->to;
        $site_id = $request->site;
        $device_id = $request->device;
        $tipe = $request->tipe;
        $site_ids = explode(",",$site_id);
        $device_ids = explode(",",$device_id);
        
        //$totalData = MCountPeople::SelectTotalDataDashboardV2($start_date,$end_date,$site_ids,$device_ids)->get();
        $data = MCountPeople::SelectPeopleCountDeviceV2($start_date,$end_date,$site_ids,$device_ids)->get();
        
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Add some headers
        $sheet->setCellValue('A1', 'Site');
        $sheet->setCellValue('B1', 'Sitename');
        $sheet->setCellValue('C1', 'Device ID');
        $sheet->setCellValue('D1', 'Device Name');
        if($tipe == 'all'){
            $sheet->setCellValue('E1', 'Total Enter');
            $sheet->setCellValue('F1', 'Total Exit');
        } else if($tipe == 1){
            $sheet->setCellValue('E1', 'Total Enter');
        } else if($tipe == 2){
            $sheet->setCellValue('E1', 'Total Exit');
        }
        $row = 2;
        // Set the writer type (Xlsx or Csv)
        foreach($data as $v){
            $sheet->setCellValue('A' . $row, $v->site_code);
            $sheet->setCellValue('B' . $row, $v->site_name);
            $sheet->setCellValue('C' . $row, $v->device_code);
            $sheet->setCellValue('D' . $row, $v->device_name);
            if($tipe == 'all'){
                $sheet->setCellValue('E' . $row, $v->total_enter);
                $sheet->setCellValue('F' . $row, $v->total_exit);
            } else if($tipe == 1){
                $sheet->setCellValue('E' . $row, $v->total_enter);
            } else if($tipe == 2){
                $sheet->setCellValue('E' . $row, $v->total_exit);
            }
            $row++;
        }
        $writer = new Csv($spreadsheet);
        $fileName = 'peoplecount_'.$date.'.csv';
        $tempFile = tempnam(sys_get_temp_dir(), $fileName);
        $writer->save($tempFile);

        // Return the file as a response
        return response()->download($tempFile, $fileName)->deleteFileAfterSend(true);
        //return response()->json(['message'=>'','code'=>1,'DailyLocation'=>$data,'TotalData'=>$totalData]);
    }

    public function tddxlsx(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $yesterdayStart = strtotime('-1 days',strtotime(str_replace('/', '-', $request->date)));
        //$originalDateEnd = strtotime(str_replace('/', '-', $request->date_end));
        $date = date('Y-m-d',$originalDate);
        $date_yesterday = date('Y-m-d',$yesterdayStart);
        //$date_end = date('Y-m-d',$originalDateEnd);
        $startDate = date('d/m/Y',$originalDate);
        
        $start_date = $date.' '.$request->from;
        $end_date = $date.' '.$request->to;

        $start_yesterday = $date_yesterday.' '.$request->from;
        $end_yesterday = $date_yesterday.' '.$request->to;

        $title_total = 'Total Today';
        $title_yesterday = 'Total Yesterday';


        $site_id = $request->site;
        $device_id = $request->device;
        $tipe = $request->tipe;
        $site_ids = explode(",",$site_id);
        $device_ids = explode(",",$device_id);
        $data = MCountPeople::SelectTDHourlyV2($start_date,$end_date,$site_ids,$device_ids)->get();
        $TotalData = MCountPeople::SelectTDHourlyTotalV2($start_date,$end_date,$site_ids,$device_ids)->get();
        $TotalYesterday = MCountPeople::SelectTDHourlyTotalV2($start_yesterday,$end_yesterday,$site_ids,$device_ids)->get();

        $i = 0;
        $arrName = [];
        $pivotTable = [];
        $pivotTableTotal = [];
        $pivotTableYesterday = [];

        foreach($TotalData as $row){
            $name = 'Total Daily';
            $inValue = $row->total_enter;
            $day = $row->hour.':00';
            if (!isset($pivotTableTotal[$name])) {
                $pivotTableTotal[$name] = array();
            }
            $pivotTableTotal[$name]['name/in/'.$day] = $inValue;
        }

        foreach($TotalYesterday as $row){
            $name = 'Total Yesterday';
            $inValue = $row->total_enter;
            $day = $row->hour.':00';
            if (!isset($pivotTableYesterday[$name])) {
                $pivotTableYesterday[$name] = array();
            }
            $pivotTableYesterday[$name]['name/in/'.$day] = $inValue;
        }

        foreach ($data as $row) {
            $arrName[$i] = $row->hour.':00';
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $day = $row->hour.':00';

            if (!isset($pivotTable[$name])) {
                $pivotTable[$name] = array();
            }
            $pivotTable[$name]['name/in/'.$day] = $inValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);

        $resultTotal = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTableTotal), $pivotTableTotal);

        $resultYesterday = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTableYesterday), $pivotTableYesterday);
        $uniqueNames = array_unique($arrName);
    
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Add some headers
        $sheet->setCellValue('A1', 'Location');

        $colIndex = 2;
        foreach ($uniqueNames as $name) {
            $sheet->setCellValueByColumnAndRow($colIndex, 1, $name);
            $colIndex++;
        }
        //dd($result);
        // Add data rows
        $rowIndex = 2;
        foreach ($result as $item) {
            //dd($item['name_in']);
            $sheet->setCellValue('A' . $rowIndex, $item['name']);

            $colIndex = 2;
            foreach ($uniqueNames as $name) {
                if (isset($item['name_in'][$name])) {
                    $sheet->setCellValueByColumnAndRow($colIndex, $rowIndex, $item['name_in'][$name]);
                } else {
                    $sheet->setCellValueByColumnAndRow($colIndex, $rowIndex, 0);
                }
                $colIndex++;
            }
            $rowIndex++;
        }

        // Add total row
        $totalRowIndex = $rowIndex;
        $sheet->setCellValue('A' . $totalRowIndex, $title_total);

        $colIndex = 2;
        foreach ($uniqueNames as $name) {
            if (isset($resultTotal[0]['name_in'][$name])) {
                $sheet->setCellValueByColumnAndRow($colIndex, $totalRowIndex, $resultTotal[0]['name_in'][$name]);
            } else {
                $sheet->setCellValueByColumnAndRow($colIndex, $totalRowIndex, 0);
            }
            $colIndex++;
        }

        // Add yesterday's total row
        $yesterdayRowIndex = $totalRowIndex + 1;
        $sheet->setCellValue('A' . $yesterdayRowIndex, $title_yesterday);

        $colIndex = 2;
        //dd($resultYesterday);
        foreach ($uniqueNames as $name) {
            if (isset($resultYesterday[0]['name_in'][$name])) {
                //dd($resultYesterday[0]['name_in'][$name]);
                $sheet->setCellValueByColumnAndRow($colIndex, $yesterdayRowIndex, $resultYesterday[0]['name_in'][$name]);
            } else {
                $sheet->setCellValueByColumnAndRow($colIndex, $yesterdayRowIndex, 0);
            }
            $colIndex++;
        }

        $writer = new Xlsx($spreadsheet);
        $fileName = 'table_degradation_daily_'.$date.'.xlsx';
        $tempFile = tempnam(sys_get_temp_dir(), $fileName);
        $writer->save($tempFile);

        // Return the file as a response
        return response()->download($tempFile, $fileName)->deleteFileAfterSend(true);
        //return response()->json(['message'=>'','code'=>1,'DailyLocation'=>$data,'TotalData'=>$totalData]);
    }

    public function tddcsv(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $yesterdayStart = strtotime('-1 days',strtotime(str_replace('/', '-', $request->date)));
        
        //$originalDateEnd = strtotime(str_replace('/', '-', $request->date_end));
        $date = date('Y-m-d',$originalDate);
        $date_yesterday = date('Y-m-d',$yesterdayStart);
        //$date_end = date('Y-m-d',$originalDateEnd);
        $startDate = date('d/m/Y',$originalDate);
        
        $start_date = $date.' '.$request->from;
        $end_date = $date.' '.$request->to;

        $start_yesterday = $date_yesterday.' '.$request->from;
        $end_yesterday = $date_yesterday.' '.$request->to;

        $title_total = 'Total Today';
        $title_yesterday = 'Total Yesterday';


        $site_id = $request->site;
        $device_id = $request->device;
        $tipe = $request->tipe;
        $site_ids = explode(",",$site_id);
        $device_ids = explode(",",$device_id);
        $data = MCountPeople::SelectTDHourlyV2($start_date,$end_date,$site_ids,$device_ids)->get();
        $TotalData = MCountPeople::SelectTDHourlyTotalV2($start_date,$end_date,$site_ids,$device_ids)->get();
        $TotalYesterday = MCountPeople::SelectTDHourlyTotalV2($start_yesterday,$end_yesterday,$site_ids,$device_ids)->get();

        $i = 0;
        $arrName = [];
        $pivotTable = [];
        $pivotTableTotal = [];
        $pivotTableYesterday = [];

        foreach($TotalData as $row){
            $name = 'Total Daily';
            $inValue = $row->total_enter;
            $day = $row->hour.':00';
            if (!isset($pivotTableTotal[$name])) {
                $pivotTableTotal[$name] = array();
            }
            $pivotTableTotal[$name]['name/in/'.$day] = $inValue;
        }

        foreach($TotalYesterday as $row){
            $name = 'Total Yesterday';
            $inValue = $row->total_enter;
            $day = $row->hour.':00';
            if (!isset($pivotTableYesterday[$name])) {
                $pivotTableYesterday[$name] = array();
            }
            $pivotTableYesterday[$name]['name/in/'.$day] = $inValue;
        }

        foreach ($data as $row) {
            $arrName[$i] = $row->hour.':00';
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $day = $row->hour.':00';

            if (!isset($pivotTable[$name])) {
                $pivotTable[$name] = array();
            }
            $pivotTable[$name]['name/in/'.$day] = $inValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);

        $resultTotal = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTableTotal), $pivotTableTotal);

        $resultYesterday = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTableYesterday), $pivotTableYesterday);
        $uniqueNames = array_unique($arrName);
    
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Add some headers
        $sheet->setCellValue('A1', 'Location');

        $colIndex = 2;
        foreach ($uniqueNames as $name) {
            $sheet->setCellValueByColumnAndRow($colIndex, 1, $name);
            $colIndex++;
        }
        //dd($result);
        // Add data rows
        $rowIndex = 2;
        foreach ($result as $item) {
            //dd($item['name_in']);
            $sheet->setCellValue('A' . $rowIndex, $item['name']);

            $colIndex = 2;
            foreach ($uniqueNames as $name) {
                if (isset($item['name_in'][$name])) {
                    $sheet->setCellValueByColumnAndRow($colIndex, $rowIndex, $item['name_in'][$name]);
                } else {
                    $sheet->setCellValueByColumnAndRow($colIndex, $rowIndex, 0);
                }
                $colIndex++;
            }
            $rowIndex++;
        }

        // Add total row
        $totalRowIndex = $rowIndex;
        $sheet->setCellValue('A' . $totalRowIndex, $title_total);

        $colIndex = 2;
        foreach ($uniqueNames as $name) {
            if (isset($resultTotal[0]['name_in'][$name])) {
                $sheet->setCellValueByColumnAndRow($colIndex, $totalRowIndex, $resultTotal[0]['name_in'][$name]);
            } else {
                $sheet->setCellValueByColumnAndRow($colIndex, $totalRowIndex, 0);
            }
            $colIndex++;
        }

        // Add yesterday's total row
        $yesterdayRowIndex = $totalRowIndex + 1;
        $sheet->setCellValue('A' . $yesterdayRowIndex, $title_yesterday);

        $colIndex = 2;
        //dd($resultYesterday);
        foreach ($uniqueNames as $name) {
            if (isset($resultYesterday[0]['name_in'][$name])) {
                //dd($resultYesterday[0]['name_in'][$name]);
                $sheet->setCellValueByColumnAndRow($colIndex, $yesterdayRowIndex, $resultYesterday[0]['name_in'][$name]);
            } else {
                $sheet->setCellValueByColumnAndRow($colIndex, $yesterdayRowIndex, 0);
            }
            $colIndex++;
        }

        $writer = new Csv($spreadsheet);
        $fileName = 'table_degradation_daily_'.$date.'.csv';
        $tempFile = tempnam(sys_get_temp_dir(), $fileName);
        $writer->save($tempFile);

        // Return the file as a response
        return response()->download($tempFile, $fileName)->deleteFileAfterSend(true);
        //return response()->json(['message'=>'','code'=>1,'DailyLocation'=>$data,'TotalData'=>$totalData]);
    }

    public function tdwxlsx(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime('-6 days',strtotime(str_replace('/', '-', $request->date)));
        $yesterdayStart = strtotime('-7 days',strtotime(str_replace('/', '-', $request->date)));
        $yesterdayEnd = strtotime('-13 days',strtotime(str_replace('/', '-', $request->date)));

        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);
        $date_yesterday = date('Y-m-d',$yesterdayStart);
        $end_yesterday = date('Y-m-d',$yesterdayEnd);

        $startDate = date('d/m/Y',$originalDate);
        $yesterdayDate = date('d/m/Y',$yesterdayEnd);

        $start_date = $date_end.' '.$request->from;
        $end_date = $date.' '.$request->to;
        $start_yesterday = $end_yesterday.' '.$request->from;
        $end_yesterday = $date_yesterday.' '.$request->to;

        $title_total = 'Total Weekly';
        $title_yesterday = 'Total Last Week';


        $site_id = $request->site;
        $device_id = $request->device;
        $tipe = $request->tipe;
        $site_ids = explode(",",$site_id);
        $device_ids = explode(",",$device_id);

        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));

        $data = MCountPeople::SelectTDWeeklyV2($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $TotalData = MCountPeople::SelectTDWeeklyTotalV2($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $TotalYesterday = MCountPeople::SelectTDWeeklyTotalV2($start_yesterday,$end_yesterday,$site_ids,$device_ids,$start_time,$end_time)->get();

        $i = 0;
        $arrName = [];
        $pivotTable = [];
        $pivotTableTotal = [];
        $pivotTableYesterday = [];

        foreach($TotalData as $row){
            $name = $title_total;
            $inValue = $row->total_enter;
            $day = $row->day;
            if (!isset($pivotTableTotal[$name])) {
                $pivotTableTotal[$name] = array();
            }
            $pivotTableTotal[$name]['name/in/'.$day] = $inValue;
        }

        foreach($TotalYesterday as $row){
            $name = $title_yesterday;
            $inValue = $row->total_enter;
            $day = $row->day;
            if (!isset($pivotTableYesterday[$name])) {
                $pivotTableYesterday[$name] = array();
            }
            $pivotTableYesterday[$name]['name/in/'.$day] = $inValue;
        }

        foreach ($data as $row) {
            $arrName[$i] = $row->day;
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $day = $row->day;

            if (!isset($pivotTable[$name])) {
                $pivotTable[$name] = array();
            }
            $pivotTable[$name]['name/in/'.$day] = $inValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);

        $resultTotal = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTableTotal), $pivotTableTotal);

        $resultYesterday = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTableYesterday), $pivotTableYesterday);
        $uniqueNames = array_unique($arrName);
    
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Add some headers
        $sheet->setCellValue('A1', 'Location');

        $colIndex = 2;
        foreach ($uniqueNames as $name) {
            $sheet->setCellValueByColumnAndRow($colIndex, 1, $name);
            $colIndex++;
        }
        //dd($result);
        // Add data rows
        $rowIndex = 2;
        foreach ($result as $item) {
            //dd($item['name_in']);
            $sheet->setCellValue('A' . $rowIndex, $item['name']);

            $colIndex = 2;
            foreach ($uniqueNames as $name) {
                if (isset($item['name_in'][$name])) {
                    $sheet->setCellValueByColumnAndRow($colIndex, $rowIndex, $item['name_in'][$name]);
                } else {
                    $sheet->setCellValueByColumnAndRow($colIndex, $rowIndex, 0);
                }
                $colIndex++;
            }
            $rowIndex++;
        }

        // Add total row
        $totalRowIndex = $rowIndex;
        $sheet->setCellValue('A' . $totalRowIndex, $title_total);

        $colIndex = 2;
        foreach ($uniqueNames as $name) {
            if (isset($resultTotal[0]['name_in'][$name])) {
                $sheet->setCellValueByColumnAndRow($colIndex, $totalRowIndex, $resultTotal[0]['name_in'][$name]);
            } else {
                $sheet->setCellValueByColumnAndRow($colIndex, $totalRowIndex, 0);
            }
            $colIndex++;
        }

        // Add yesterday's total row
        $yesterdayRowIndex = $totalRowIndex + 1;
        $sheet->setCellValue('A' . $yesterdayRowIndex, $title_yesterday);

        $colIndex = 2;
        //dd($resultYesterday);
        foreach ($uniqueNames as $name) {
            if (isset($resultYesterday[0]['name_in'][$name])) {
                //dd($resultYesterday[0]['name_in'][$name]);
                $sheet->setCellValueByColumnAndRow($colIndex, $yesterdayRowIndex, $resultYesterday[0]['name_in'][$name]);
            } else {
                $sheet->setCellValueByColumnAndRow($colIndex, $yesterdayRowIndex, 0);
            }
            $colIndex++;
        }

        $writer = new Xlsx($spreadsheet);
        $fileName = 'table_degradation_weekly_'.$date.'.xlsx';
        $tempFile = tempnam(sys_get_temp_dir(), $fileName);
        $writer->save($tempFile);

        // Return the file as a response
        return response()->download($tempFile, $fileName)->deleteFileAfterSend(true);
        //return response()->json(['message'=>'','code'=>1,'DailyLocation'=>$data,'TotalData'=>$totalData]);
    }

    public function tdwcsv(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime('-6 days',strtotime(str_replace('/', '-', $request->date)));
        $yesterdayStart = strtotime('-7 days',strtotime(str_replace('/', '-', $request->date)));
        $yesterdayEnd = strtotime('-13 days',strtotime(str_replace('/', '-', $request->date)));

        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);
        $date_yesterday = date('Y-m-d',$yesterdayStart);
        $end_yesterday = date('Y-m-d',$yesterdayEnd);

        $startDate = date('d/m/Y',$originalDate);
        $yesterdayDate = date('d/m/Y',$yesterdayEnd);

        $start_date = $date_end.' '.$request->from;
        $end_date = $date.' '.$request->to;
        $start_yesterday = $end_yesterday.' '.$request->from;
        $end_yesterday = $date_yesterday.' '.$request->to;

        $title_total = 'Total Weekly';
        $title_yesterday = 'Total Last Week';


        $site_id = $request->site;
        $device_id = $request->device;
        $tipe = $request->tipe;
        $site_ids = explode(",",$site_id);
        $device_ids = explode(",",$device_id);
        
        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));

        $data = MCountPeople::SelectTDWeeklyV2($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $TotalData = MCountPeople::SelectTDWeeklyTotalV2($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $TotalYesterday = MCountPeople::SelectTDWeeklyTotalV2($start_yesterday,$end_yesterday,$site_ids,$device_ids,$start_time,$end_time)->get();

        $i = 0;
        $arrName = [];
        $pivotTable = [];
        $pivotTableTotal = [];
        $pivotTableYesterday = [];

        foreach($TotalData as $row){
            $name = $title_total;
            $inValue = $row->total_enter;
            $day = $row->day;
            if (!isset($pivotTableTotal[$name])) {
                $pivotTableTotal[$name] = array();
            }
            $pivotTableTotal[$name]['name/in/'.$day] = $inValue;
        }

        foreach($TotalYesterday as $row){
            $name = $title_yesterday;
            $inValue = $row->total_enter;
            $day = $row->day;
            if (!isset($pivotTableYesterday[$name])) {
                $pivotTableYesterday[$name] = array();
            }
            $pivotTableYesterday[$name]['name/in/'.$day] = $inValue;
        }

        foreach ($data as $row) {
            $arrName[$i] = $row->day;
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $day = $row->day;

            if (!isset($pivotTable[$name])) {
                $pivotTable[$name] = array();
            }
            $pivotTable[$name]['name/in/'.$day] = $inValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);

        $resultTotal = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTableTotal), $pivotTableTotal);

        $resultYesterday = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTableYesterday), $pivotTableYesterday);
        $uniqueNames = array_unique($arrName);
    
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Add some headers
        $sheet->setCellValue('A1', 'Location');

        $colIndex = 2;
        foreach ($uniqueNames as $name) {
            $sheet->setCellValueByColumnAndRow($colIndex, 1, $name);
            $colIndex++;
        }
        //dd($result);
        // Add data rows
        $rowIndex = 2;
        foreach ($result as $item) {
            //dd($item['name_in']);
            $sheet->setCellValue('A' . $rowIndex, $item['name']);

            $colIndex = 2;
            foreach ($uniqueNames as $name) {
                if (isset($item['name_in'][$name])) {
                    $sheet->setCellValueByColumnAndRow($colIndex, $rowIndex, $item['name_in'][$name]);
                } else {
                    $sheet->setCellValueByColumnAndRow($colIndex, $rowIndex, 0);
                }
                $colIndex++;
            }
            $rowIndex++;
        }

        // Add total row
        $totalRowIndex = $rowIndex;
        $sheet->setCellValue('A' . $totalRowIndex, $title_total);

        $colIndex = 2;
        foreach ($uniqueNames as $name) {
            if (isset($resultTotal[0]['name_in'][$name])) {
                $sheet->setCellValueByColumnAndRow($colIndex, $totalRowIndex, $resultTotal[0]['name_in'][$name]);
            } else {
                $sheet->setCellValueByColumnAndRow($colIndex, $totalRowIndex, 0);
            }
            $colIndex++;
        }

        // Add yesterday's total row
        $yesterdayRowIndex = $totalRowIndex + 1;
        $sheet->setCellValue('A' . $yesterdayRowIndex, $title_yesterday);

        $colIndex = 2;
        //dd($resultYesterday);
        foreach ($uniqueNames as $name) {
            if (isset($resultYesterday[0]['name_in'][$name])) {
                //dd($resultYesterday[0]['name_in'][$name]);
                $sheet->setCellValueByColumnAndRow($colIndex, $yesterdayRowIndex, $resultYesterday[0]['name_in'][$name]);
            } else {
                $sheet->setCellValueByColumnAndRow($colIndex, $yesterdayRowIndex, 0);
            }
            $colIndex++;
        }

        $writer = new Csv($spreadsheet);
        $fileName = 'table_degradation_weekly_'.$date.'.csv';
        $tempFile = tempnam(sys_get_temp_dir(), $fileName);
        $writer->save($tempFile);

        // Return the file as a response
        return response()->download($tempFile, $fileName)->deleteFileAfterSend(true);
        //return response()->json(['message'=>'','code'=>1,'DailyLocation'=>$data,'TotalData'=>$totalData]);
    }

    public function tdmxlsx(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime('-29 days',strtotime(str_replace('/', '-', $request->date)));
        $yesterdayStart = strtotime('-30 days',strtotime(str_replace('/', '-', $request->date)));
        $yesterdayEnd = strtotime('-59 days',strtotime(str_replace('/', '-', $request->date)));

        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);
        $date_yesterday = date('Y-m-d',$yesterdayStart);
        $end_yesterday = date('Y-m-d',$yesterdayEnd);

        $startDate = date('d/m/Y',$originalDate);
        $yesterdayDate = date('d/m/Y',$yesterdayEnd);

        $start_date = $date_end.' '.$request->from;
        $end_date = $date.' '.$request->to;
        $start_yesterday = $end_yesterday.' '.$request->from;
        $end_yesterday = $date_yesterday.' '.$request->to;

        $title_total = 'Total Monthly';
        $title_yesterday = 'Total Last Month';


        $site_id = $request->site;
        $device_id = $request->device;
        $tipe = $request->tipe;
        $site_ids = explode(",",$site_id);
        $device_ids = explode(",",$device_id);

        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));

        $data = MCountPeople::SelectTDMonthlyV2($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $TotalData = MCountPeople::SelectTDMonthlyTotalV2($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $TotalYesterday = MCountPeople::SelectTDMonthlyTotalV2($start_yesterday,$end_yesterday,$site_ids,$device_ids,$start_time,$end_time)->get();

        $i = 0;
        $arrName = [];
        $pivotTable = [];
        $pivotTableTotal = [];
        $pivotTableYesterday = [];

        foreach($TotalData as $row){
            $name = $title_total;
            $inValue = $row->total_enter;
            $day = $row->day;
            if (!isset($pivotTableTotal[$name])) {
                $pivotTableTotal[$name] = array();
            }
            $pivotTableTotal[$name]['name/in/'.$day] = $inValue;
        }

        foreach($TotalYesterday as $row){
            $name = $title_yesterday;
            $inValue = $row->total_enter;
            $day = $row->day;
            if (!isset($pivotTableYesterday[$name])) {
                $pivotTableYesterday[$name] = array();
            }
            $pivotTableYesterday[$name]['name/in/'.$day] = $inValue;
        }

        foreach ($data as $row) {
            $arrName[$i] = $row->day;
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $day = $row->day;

            if (!isset($pivotTable[$name])) {
                $pivotTable[$name] = array();
            }
            $pivotTable[$name]['name/in/'.$day] = $inValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);

        $resultTotal = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTableTotal), $pivotTableTotal);

        $resultYesterday = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTableYesterday), $pivotTableYesterday);
        $uniqueNames = array_unique($arrName);
    
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Add some headers
        $sheet->setCellValue('A1', 'Location');

        $colIndex = 2;
        foreach ($uniqueNames as $name) {
            $sheet->setCellValueByColumnAndRow($colIndex, 1, $name);
            $colIndex++;
        }
        //dd($result);
        // Add data rows
        $rowIndex = 2;
        foreach ($result as $item) {
            //dd($item['name_in']);
            $sheet->setCellValue('A' . $rowIndex, $item['name']);

            $colIndex = 2;
            foreach ($uniqueNames as $name) {
                if (isset($item['name_in'][$name])) {
                    $sheet->setCellValueByColumnAndRow($colIndex, $rowIndex, $item['name_in'][$name]);
                } else {
                    $sheet->setCellValueByColumnAndRow($colIndex, $rowIndex, 0);
                }
                $colIndex++;
            }
            $rowIndex++;
        }

        // Add total row
        $totalRowIndex = $rowIndex;
        $sheet->setCellValue('A' . $totalRowIndex, $title_total);

        $colIndex = 2;
        foreach ($uniqueNames as $name) {
            if (isset($resultTotal[0]['name_in'][$name])) {
                $sheet->setCellValueByColumnAndRow($colIndex, $totalRowIndex, $resultTotal[0]['name_in'][$name]);
            } else {
                $sheet->setCellValueByColumnAndRow($colIndex, $totalRowIndex, 0);
            }
            $colIndex++;
        }

        // Add yesterday's total row
        $yesterdayRowIndex = $totalRowIndex + 1;
        $sheet->setCellValue('A' . $yesterdayRowIndex, $title_yesterday);

        $colIndex = 2;
        //dd($resultYesterday);
        foreach ($uniqueNames as $name) {
            if (isset($resultYesterday[0]['name_in'][$name])) {
                //dd($resultYesterday[0]['name_in'][$name]);
                $sheet->setCellValueByColumnAndRow($colIndex, $yesterdayRowIndex, $resultYesterday[0]['name_in'][$name]);
            } else {
                $sheet->setCellValueByColumnAndRow($colIndex, $yesterdayRowIndex, 0);
            }
            $colIndex++;
        }

        $writer = new Xlsx($spreadsheet);
        $fileName = 'table_degradation_monthly_'.$date.'.xlsx';
        $tempFile = tempnam(sys_get_temp_dir(), $fileName);
        $writer->save($tempFile);

        // Return the file as a response
        return response()->download($tempFile, $fileName)->deleteFileAfterSend(true);
        //return response()->json(['message'=>'','code'=>1,'DailyLocation'=>$data,'TotalData'=>$totalData]);
    }

    public function tdmcsv(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime('-29 days',strtotime(str_replace('/', '-', $request->date)));
        $yesterdayStart = strtotime('-30 days',strtotime(str_replace('/', '-', $request->date)));
        $yesterdayEnd = strtotime('-59 days',strtotime(str_replace('/', '-', $request->date)));

        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);
        $date_yesterday = date('Y-m-d',$yesterdayStart);
        $end_yesterday = date('Y-m-d',$yesterdayEnd);

        $startDate = date('d/m/Y',$originalDate);
        $yesterdayDate = date('d/m/Y',$yesterdayEnd);

        $start_date = $date_end.' '.$request->from;
        $end_date = $date.' '.$request->to;
        $start_yesterday = $end_yesterday.' '.$request->from;
        $end_yesterday = $date_yesterday.' '.$request->to;

        $title_total = 'Total Monthly';
        $title_yesterday = 'Total Last Month';


        $site_id = $request->site;
        $device_id = $request->device;
        $tipe = $request->tipe;
        $site_ids = explode(",",$site_id);
        $device_ids = explode(",",$device_id);
        
        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));

        $data = MCountPeople::SelectTDMonthlyV2($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $TotalData = MCountPeople::SelectTDMonthlyTotalV2($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $TotalYesterday = MCountPeople::SelectTDMonthlyTotalV2($start_yesterday,$end_yesterday,$site_ids,$device_ids,$start_time,$end_time)->get();

        $i = 0;
        $arrName = [];
        $pivotTable = [];
        $pivotTableTotal = [];
        $pivotTableYesterday = [];

        foreach($TotalData as $row){
            $name = $title_total;
            $inValue = $row->total_enter;
            $day = $row->day;
            if (!isset($pivotTableTotal[$name])) {
                $pivotTableTotal[$name] = array();
            }
            $pivotTableTotal[$name]['name/in/'.$day] = $inValue;
        }

        foreach($TotalYesterday as $row){
            $name = $title_yesterday;
            $inValue = $row->total_enter;
            $day = $row->day;
            if (!isset($pivotTableYesterday[$name])) {
                $pivotTableYesterday[$name] = array();
            }
            $pivotTableYesterday[$name]['name/in/'.$day] = $inValue;
        }

        foreach ($data as $row) {
            $arrName[$i] = $row->day;
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $day = $row->day;

            if (!isset($pivotTable[$name])) {
                $pivotTable[$name] = array();
            }
            $pivotTable[$name]['name/in/'.$day] = $inValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);

        $resultTotal = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTableTotal), $pivotTableTotal);

        $resultYesterday = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTableYesterday), $pivotTableYesterday);
        $uniqueNames = array_unique($arrName);
    
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Add some headers
        $sheet->setCellValue('A1', 'Location');

        $colIndex = 2;
        foreach ($uniqueNames as $name) {
            $sheet->setCellValueByColumnAndRow($colIndex, 1, $name);
            $colIndex++;
        }
        //dd($result);
        // Add data rows
        $rowIndex = 2;
        foreach ($result as $item) {
            //dd($item['name_in']);
            $sheet->setCellValue('A' . $rowIndex, $item['name']);

            $colIndex = 2;
            foreach ($uniqueNames as $name) {
                if (isset($item['name_in'][$name])) {
                    $sheet->setCellValueByColumnAndRow($colIndex, $rowIndex, $item['name_in'][$name]);
                } else {
                    $sheet->setCellValueByColumnAndRow($colIndex, $rowIndex, 0);
                }
                $colIndex++;
            }
            $rowIndex++;
        }

        // Add total row
        $totalRowIndex = $rowIndex;
        $sheet->setCellValue('A' . $totalRowIndex, $title_total);

        $colIndex = 2;
        foreach ($uniqueNames as $name) {
            if (isset($resultTotal[0]['name_in'][$name])) {
                $sheet->setCellValueByColumnAndRow($colIndex, $totalRowIndex, $resultTotal[0]['name_in'][$name]);
            } else {
                $sheet->setCellValueByColumnAndRow($colIndex, $totalRowIndex, 0);
            }
            $colIndex++;
        }

        // Add yesterday's total row
        $yesterdayRowIndex = $totalRowIndex + 1;
        $sheet->setCellValue('A' . $yesterdayRowIndex, $title_yesterday);

        $colIndex = 2;
        //dd($resultYesterday);
        foreach ($uniqueNames as $name) {
            if (isset($resultYesterday[0]['name_in'][$name])) {
                //dd($resultYesterday[0]['name_in'][$name]);
                $sheet->setCellValueByColumnAndRow($colIndex, $yesterdayRowIndex, $resultYesterday[0]['name_in'][$name]);
            } else {
                $sheet->setCellValueByColumnAndRow($colIndex, $yesterdayRowIndex, 0);
            }
            $colIndex++;
        }

        $writer = new Csv($spreadsheet);
        $fileName = 'table_degradation_monthly_'.$date.'.csv';
        $tempFile = tempnam(sys_get_temp_dir(), $fileName);
        $writer->save($tempFile);

        // Return the file as a response
        return response()->download($tempFile, $fileName)->deleteFileAfterSend(true);
        //return response()->json(['message'=>'','code'=>1,'DailyLocation'=>$data,'TotalData'=>$totalData]);
    }

    public function tdyxlsx(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime('-1 year',strtotime(str_replace('/', '-', $request->date)));
        $yesterdayStart = strtotime('-1 year',strtotime(str_replace('/', '-', $request->date)));
        $yesterdayEnd = strtotime('-2 years',strtotime(str_replace('/', '-', $request->date)));

        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);
        $date_yesterday = date('Y-m-d',$yesterdayStart);
        $end_yesterday = date('Y-m-d',$yesterdayEnd);

        $startDate = date('d/m/Y',$originalDate);
        $yesterdayDate = date('d/m/Y',$yesterdayEnd);

        $start_date = $date_end.' '.$request->from;
        $end_date = $date.' '.$request->to;
        $start_yesterday = $end_yesterday.' '.$request->from;
        $end_yesterday = $date_yesterday.' '.$request->to;

        $title_total = 'Total Yearly';
        $title_yesterday = 'Total Last Year';


        $site_id = $request->site;
        $device_id = $request->device;
        $tipe = $request->tipe;
        $site_ids = explode(",",$site_id);
        $device_ids = explode(",",$device_id);

        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));

        $data = MCountPeople::SelectTDYearlyV2($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $TotalData = MCountPeople::SelectTDYearlyTotalV2($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $TotalYesterday = MCountPeople::SelectTDYearlyTotalV2($start_yesterday,$end_yesterday,$site_ids,$device_ids,$start_time,$end_time)->get();

        $i = 0;
        $arrName = [];
        $pivotTable = [];
        $pivotTableTotal = [];
        $pivotTableYesterday = [];

        foreach($TotalData as $row){
            $name = $title_total;
            $inValue = $row->total_enter;
            $day = $row->month . " - " . $row->year;
            if (!isset($pivotTableTotal[$name])) {
                $pivotTableTotal[$name] = array();
            }
            $pivotTableTotal[$name]['name/in/'.$day] = $inValue;
        }

        foreach($TotalYesterday as $row){
            $name = $title_yesterday;
            $inValue = $row->total_enter;
            $day = $row->month . " - " . $row->year;
            if (!isset($pivotTableYesterday[$name])) {
                $pivotTableYesterday[$name] = array();
            }
            $pivotTableYesterday[$name]['name/in/'.$day] = $inValue;
        }

        foreach ($data as $row) {
            $arrName[$i] = $row->month . " - " . $row->year;
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $day = $row->month . " - " . $row->year;

            if (!isset($pivotTable[$name])) {
                $pivotTable[$name] = array();
            }
            $pivotTable[$name]['name/in/'.$day] = $inValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);

        $resultTotal = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTableTotal), $pivotTableTotal);

        $resultYesterday = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTableYesterday), $pivotTableYesterday);
        $uniqueNames = array_unique($arrName);
    
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Add some headers
        $sheet->setCellValue('A1', 'Location');

        $colIndex = 2;
        foreach ($uniqueNames as $name) {
            $sheet->setCellValueByColumnAndRow($colIndex, 1, $name);
            $colIndex++;
        }
        //dd($result);
        // Add data rows
        $rowIndex = 2;
        foreach ($result as $item) {
            //dd($item['name_in']);
            $sheet->setCellValue('A' . $rowIndex, $item['name']);

            $colIndex = 2;
            foreach ($uniqueNames as $name) {
                if (isset($item['name_in'][$name])) {
                    $sheet->setCellValueByColumnAndRow($colIndex, $rowIndex, $item['name_in'][$name]);
                } else {
                    $sheet->setCellValueByColumnAndRow($colIndex, $rowIndex, 0);
                }
                $colIndex++;
            }
            $rowIndex++;
        }

        // Add total row
        $totalRowIndex = $rowIndex;
        $sheet->setCellValue('A' . $totalRowIndex, $title_total);

        $colIndex = 2;
        foreach ($uniqueNames as $name) {
            if (isset($resultTotal[0]['name_in'][$name])) {
                $sheet->setCellValueByColumnAndRow($colIndex, $totalRowIndex, $resultTotal[0]['name_in'][$name]);
            } else {
                $sheet->setCellValueByColumnAndRow($colIndex, $totalRowIndex, 0);
            }
            $colIndex++;
        }

        // Add yesterday's total row
        $yesterdayRowIndex = $totalRowIndex + 1;
        $sheet->setCellValue('A' . $yesterdayRowIndex, $title_yesterday);

        $colIndex = 2;
        //dd($resultYesterday);
        foreach ($uniqueNames as $name) {
            if (isset($resultYesterday[0]['name_in'][$name])) {
                //dd($resultYesterday[0]['name_in'][$name]);
                $sheet->setCellValueByColumnAndRow($colIndex, $yesterdayRowIndex, $resultYesterday[0]['name_in'][$name]);
            } else {
                $sheet->setCellValueByColumnAndRow($colIndex, $yesterdayRowIndex, 0);
            }
            $colIndex++;
        }

        $writer = new Xlsx($spreadsheet);
        $fileName = 'table_degradation_yearly_'.$date.'.xlsx';
        $tempFile = tempnam(sys_get_temp_dir(), $fileName);
        $writer->save($tempFile);

        // Return the file as a response
        return response()->download($tempFile, $fileName)->deleteFileAfterSend(true);
        //return response()->json(['message'=>'','code'=>1,'DailyLocation'=>$data,'TotalData'=>$totalData]);
    }

    public function tdycsv(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime('-1 year',strtotime(str_replace('/', '-', $request->date)));
        $yesterdayStart = strtotime('-1 year',strtotime(str_replace('/', '-', $request->date)));
        $yesterdayEnd = strtotime('-2 years',strtotime(str_replace('/', '-', $request->date)));

        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);
        $date_yesterday = date('Y-m-d',$yesterdayStart);
        $end_yesterday = date('Y-m-d',$yesterdayEnd);

        $startDate = date('d/m/Y',$originalDate);
        $yesterdayDate = date('d/m/Y',$yesterdayEnd);

        $start_date = $date_end.' '.$request->from;
        $end_date = $date.' '.$request->to;
        $start_yesterday = $end_yesterday.' '.$request->from;
        $end_yesterday = $date_yesterday.' '.$request->to;

        $title_total = 'Total Yearly';
        $title_yesterday = 'Total Last Year';


        $site_id = $request->site;
        $device_id = $request->device;
        $tipe = $request->tipe;
        $site_ids = explode(",",$site_id);
        $device_ids = explode(",",$device_id);
        
        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));

        $data = MCountPeople::SelectTDYearlyV2($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $TotalData = MCountPeople::SelectTDYearlyTotalV2($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $TotalYesterday = MCountPeople::SelectTDYearlyTotalV2($start_yesterday,$end_yesterday,$site_ids,$device_ids,$start_time,$end_time)->get();

        $i = 0;
        $arrName = [];
        $pivotTable = [];
        $pivotTableTotal = [];
        $pivotTableYesterday = [];

        foreach($TotalData as $row){
            $name = $title_total;
            $inValue = $row->total_enter;
            $day = $row->month . " - " . $row->year;
            if (!isset($pivotTableTotal[$name])) {
                $pivotTableTotal[$name] = array();
            }
            $pivotTableTotal[$name]['name/in/'.$day] = $inValue;
        }

        foreach($TotalYesterday as $row){
            $name = $title_yesterday;
            $inValue = $row->total_enter;
            $day = $row->month . " - " . $row->year;
            if (!isset($pivotTableYesterday[$name])) {
                $pivotTableYesterday[$name] = array();
            }
            $pivotTableYesterday[$name]['name/in/'.$day] = $inValue;
        }

        foreach ($data as $row) {
            $arrName[$i] = $row->month . " - " . $row->year;
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $day = $row->month . " - " . $row->year;

            if (!isset($pivotTable[$name])) {
                $pivotTable[$name] = array();
            }
            $pivotTable[$name]['name/in/'.$day] = $inValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);

        $resultTotal = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTableTotal), $pivotTableTotal);

        $resultYesterday = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    //$newKey = str_replace('name/out/', '', $key);
                    //$nameOut[$newKey] = $value;
                }
            }
        
            return [
                'name' => $storeName,
                'name_in' => $nameIn,
                //'name_out' => $nameOut
            ];
        }, array_keys($pivotTableYesterday), $pivotTableYesterday);
        $uniqueNames = array_unique($arrName);
    
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Add some headers
        $sheet->setCellValue('A1', 'Location');

        $colIndex = 2;
        foreach ($uniqueNames as $name) {
            $sheet->setCellValueByColumnAndRow($colIndex, 1, $name);
            $colIndex++;
        }
        //dd($result);
        // Add data rows
        $rowIndex = 2;
        foreach ($result as $item) {
            //dd($item['name_in']);
            $sheet->setCellValue('A' . $rowIndex, $item['name']);

            $colIndex = 2;
            foreach ($uniqueNames as $name) {
                if (isset($item['name_in'][$name])) {
                    $sheet->setCellValueByColumnAndRow($colIndex, $rowIndex, $item['name_in'][$name]);
                } else {
                    $sheet->setCellValueByColumnAndRow($colIndex, $rowIndex, 0);
                }
                $colIndex++;
            }
            $rowIndex++;
        }

        // Add total row
        $totalRowIndex = $rowIndex;
        $sheet->setCellValue('A' . $totalRowIndex, $title_total);

        $colIndex = 2;
        foreach ($uniqueNames as $name) {
            if (isset($resultTotal[0]['name_in'][$name])) {
                $sheet->setCellValueByColumnAndRow($colIndex, $totalRowIndex, $resultTotal[0]['name_in'][$name]);
            } else {
                $sheet->setCellValueByColumnAndRow($colIndex, $totalRowIndex, 0);
            }
            $colIndex++;
        }

        // Add yesterday's total row
        $yesterdayRowIndex = $totalRowIndex + 1;
        $sheet->setCellValue('A' . $yesterdayRowIndex, $title_yesterday);

        $colIndex = 2;
        //dd($resultYesterday);
        foreach ($uniqueNames as $name) {
            if (isset($resultYesterday[0]['name_in'][$name])) {
                //dd($resultYesterday[0]['name_in'][$name]);
                $sheet->setCellValueByColumnAndRow($colIndex, $yesterdayRowIndex, $resultYesterday[0]['name_in'][$name]);
            } else {
                $sheet->setCellValueByColumnAndRow($colIndex, $yesterdayRowIndex, 0);
            }
            $colIndex++;
        }

        $writer = new Csv($spreadsheet);
        $fileName = 'table_degradation_yearly_'.$date.'.csv';
        $tempFile = tempnam(sys_get_temp_dir(), $fileName);
        $writer->save($tempFile);

        // Return the file as a response
        return response()->download($tempFile, $fileName)->deleteFileAfterSend(true);
        //return response()->json(['message'=>'','code'=>1,'DailyLocation'=>$data,'TotalData'=>$totalData]);
    }

    public function drhxlsx(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime(str_replace('/', '-', $request->date_stop));
        
        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);

        $start_date = $date.' '.$request->from;
        $end_date = $date_end.' '.$request->to;

        $site_id = $request->site;
        $device_id = $request->device;
        $tipe = $request->tipe;
        $site_ids = explode(",",$site_id);
        $device_ids = explode(",",$device_id);
        
        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));

        $data = MCountPeople::SelectDRHourlyV3($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $i = 0;
        $arrName = [];
        $pivotTable = [];
        foreach ($data as $row) {
            $arrName[$i] = $row->site_name;
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $outValue = $row->total_exit;
            $hour = $row->hour;

            if (!isset($pivotTable[$hour])) {
                $pivotTable[$hour] = array();
            }
            $pivotTable[$hour]['name/in/'.$name] = $inValue;
            $pivotTable[$hour]['name/out/'.$name] = $outValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    $newKey = str_replace('name/out/', '', $key);
                    $nameOut[$newKey] = $value;
                }
            }
        
            return [
                'hour' => $storeName,
                'name_in' => $nameIn,
                'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);
        $uniqueNames = array_unique($arrName);

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Define the headers for the Excel columns
        $headers = ['Time'];
        foreach ($uniqueNames as $name) {
            if ($tipe == "all") {
                $headers[] = $name . " / in";
                $headers[] = $name . " / out";
            } elseif ($tipe == "1") {
                $headers[] = $name . " / in";
            } elseif ($tipe == "2") {
                $headers[] = $name . " / out";
            }
        }

        // Set the headers in the first row
        $sheet->fromArray($headers, NULL, 'A1');

        // Fill in the rows
        $rowIndex = 2;
        foreach ($result as $item) {
            $row = [];
            $row[] = $item['hour'];
            //$row[] = $item['day'];

            foreach ($uniqueNames as $name) {
                if ($tipe == "all") {
                    $row[] = $item['name_in'][$name] ?? 0;
                    $row[] = $item['name_out'][$name] ?? 0;
                } elseif ($tipe == "1") {
                    $row[] = $item['name_in'][$name] ?? 0;
                } elseif ($tipe == "2") {
                    $row[] = $item['name_out'][$name] ?? 0;
                }
            }

            $sheet->fromArray($row, NULL, 'A' . $rowIndex);
            $rowIndex++;
        }
        
        $writer = new Xlsx($spreadsheet);
        $fileName = 'dashboard_range_hourly_'.$date.'.xlsx';
        $tempFile = tempnam(sys_get_temp_dir(), $fileName);
        $writer->save($tempFile);

        // Return the file as a response
        return response()->download($tempFile, $fileName)->deleteFileAfterSend(true);
    }
    public function drhcsv(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime(str_replace('/', '-', $request->date_stop));
        
        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);

        $start_date = $date.' '.$request->from;
        $end_date = $date_end.' '.$request->to;

        $site_id = $request->site;
        $device_id = $request->device;
        $tipe = $request->tipe;
        $site_ids = explode(",",$site_id);
        $device_ids = explode(",",$device_id);
        
        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));

        $data = MCountPeople::SelectDRHourlyV3($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $i = 0;
        $arrName = [];
        $pivotTable = [];
        foreach ($data as $row) {
            $arrName[$i] = $row->site_name;
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $outValue = $row->total_exit;
            $hour = $row->hour;

            if (!isset($pivotTable[$hour])) {
                $pivotTable[$hour] = array();
            }
            $pivotTable[$hour]['name/in/'.$name] = $inValue;
            $pivotTable[$hour]['name/out/'.$name] = $outValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    $newKey = str_replace('name/out/', '', $key);
                    $nameOut[$newKey] = $value;
                }
            }
        
            return [
                'hour' => $storeName,
                'name_in' => $nameIn,
                'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);
        $uniqueNames = array_unique($arrName);

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Define the headers for the Excel columns
        $headers = ['Time'];
        foreach ($uniqueNames as $name) {
            if ($tipe == "all") {
                $headers[] = $name . " / in";
                $headers[] = $name . " / out";
            } elseif ($tipe == "1") {
                $headers[] = $name . " / in";
            } elseif ($tipe == "2") {
                $headers[] = $name . " / out";
            }
        }

        // Set the headers in the first row
        $sheet->fromArray($headers, NULL, 'A1');

        // Fill in the rows
        $rowIndex = 2;
        foreach ($result as $item) {
            $row = [];
            $row[] = $item['hour'];
            //$row[] = $item['day'];

            foreach ($uniqueNames as $name) {
                if ($tipe == "all") {
                    $row[] = $item['name_in'][$name] ?? 0;
                    $row[] = $item['name_out'][$name] ?? 0;
                } elseif ($tipe == "1") {
                    $row[] = $item['name_in'][$name] ?? 0;
                } elseif ($tipe == "2") {
                    $row[] = $item['name_out'][$name] ?? 0;
                }
            }

            $sheet->fromArray($row, NULL, 'A' . $rowIndex);
            $rowIndex++;
        }
        
        $writer = new Csv($spreadsheet);
        $fileName = 'dashboard_range_hourly_'.$date.'.csv';
        $tempFile = tempnam(sys_get_temp_dir(), $fileName);
        $writer->save($tempFile);

        // Return the file as a response
        return response()->download($tempFile, $fileName)->deleteFileAfterSend(true);
    }

    public function drdxlsx(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime(str_replace('/', '-', $request->date_stop));
        
        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);

        $start_date = $date.' '.$request->from;
        $end_date = $date_end.' '.$request->to;

        $site_id = $request->site;
        $device_id = $request->device;
        $tipe = $request->tipe;
        $site_ids = explode(",",$site_id);
        $device_ids = explode(",",$device_id);
        
        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));

        $data = MCountPeople::SelectDRDailyV3($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $i = 0;
        $arrName = [];
        $pivotTable = [];
        foreach ($data as $row) {
            $arr_day = explode("-",$row->day);
            $arrName[$i] = $row->site_name;
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $outValue = $row->total_exit;
            $day = $arr_day[2] . "/" . $arr_day[1] . "/" . $arr_day[0];

            if (!isset($pivotTable[$day])) {
                $pivotTable[$day] = array();
            }
            $pivotTable[$day]['name/in/'.$name] = $inValue;
            $pivotTable[$day]['name/out/'.$name] = $outValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    $newKey = str_replace('name/out/', '', $key);
                    $nameOut[$newKey] = $value;
                }
            }
        
            return [
                'day' => $storeName,
                'name_in' => $nameIn,
                'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);
        $uniqueNames = array_unique($arrName);

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Define the headers for the Excel columns
        $headers = ['Day'];
        foreach ($uniqueNames as $name) {
            if ($tipe == "all") {
                $headers[] = $name . " / in";
                $headers[] = $name . " / out";
            } elseif ($tipe == "1") {
                $headers[] = $name . " / in";
            } elseif ($tipe == "2") {
                $headers[] = $name . " / out";
            }
        }

        // Set the headers in the first row
        $sheet->fromArray($headers, NULL, 'A1');

        // Fill in the rows
        $rowIndex = 2;
        foreach ($result as $item) {
            $row = [];
            $row[] = $item['day'];
            //$row[] = $item['day'];

            foreach ($uniqueNames as $name) {
                if ($tipe == "all") {
                    $row[] = $item['name_in'][$name] ?? 0;
                    $row[] = $item['name_out'][$name] ?? 0;
                } elseif ($tipe == "1") {
                    $row[] = $item['name_in'][$name] ?? 0;
                } elseif ($tipe == "2") {
                    $row[] = $item['name_out'][$name] ?? 0;
                }
            }

            $sheet->fromArray($row, NULL, 'A' . $rowIndex);
            $rowIndex++;
        }
        
        $writer = new Xlsx($spreadsheet);
        $fileName = 'dashboard_range_daily_'.$date.'.xlsx';
        $tempFile = tempnam(sys_get_temp_dir(), $fileName);
        $writer->save($tempFile);

        // Return the file as a response
        return response()->download($tempFile, $fileName)->deleteFileAfterSend(true);
    }
    public function drdcsv(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime(str_replace('/', '-', $request->date_stop));
        
        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);

        $start_date = $date.' '.$request->from;
        $end_date = $date_end.' '.$request->to;

        $site_id = $request->site;
        $device_id = $request->device;
        $tipe = $request->tipe;
        $site_ids = explode(",",$site_id);
        $device_ids = explode(",",$device_id);
        
        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));

        $data = MCountPeople::SelectDRDailyV3($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $i = 0;
        $arrName = [];
        $pivotTable = [];
        foreach ($data as $row) {
            $arr_day = explode("-",$row->day);
            $arrName[$i] = $row->site_name;
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $outValue = $row->total_exit;
            $day = $arr_day[2] . "/" . $arr_day[1] . "/" . $arr_day[0];

            if (!isset($pivotTable[$day])) {
                $pivotTable[$day] = array();
            }
            $pivotTable[$day]['name/in/'.$name] = $inValue;
            $pivotTable[$day]['name/out/'.$name] = $outValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    $newKey = str_replace('name/out/', '', $key);
                    $nameOut[$newKey] = $value;
                }
            }
        
            return [
                'day' => $storeName,
                'name_in' => $nameIn,
                'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);
        $uniqueNames = array_unique($arrName);

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Define the headers for the Excel columns
        $headers = ['Day'];
        foreach ($uniqueNames as $name) {
            if ($tipe == "all") {
                $headers[] = $name . " / in";
                $headers[] = $name . " / out";
            } elseif ($tipe == "1") {
                $headers[] = $name . " / in";
            } elseif ($tipe == "2") {
                $headers[] = $name . " / out";
            }
        }

        // Set the headers in the first row
        $sheet->fromArray($headers, NULL, 'A1');

        // Fill in the rows
        $rowIndex = 2;
        foreach ($result as $item) {
            $row = [];
            $row[] = $item['day'];
            //$row[] = $item['day'];

            foreach ($uniqueNames as $name) {
                if ($tipe == "all") {
                    $row[] = $item['name_in'][$name] ?? 0;
                    $row[] = $item['name_out'][$name] ?? 0;
                } elseif ($tipe == "1") {
                    $row[] = $item['name_in'][$name] ?? 0;
                } elseif ($tipe == "2") {
                    $row[] = $item['name_out'][$name] ?? 0;
                }
            }

            $sheet->fromArray($row, NULL, 'A' . $rowIndex);
            $rowIndex++;
        }
        
        $writer = new Csv($spreadsheet);
        $fileName = 'dashboard_range_daily_'.$date.'.csv';
        $tempFile = tempnam(sys_get_temp_dir(), $fileName);
        $writer->save($tempFile);

        // Return the file as a response
        return response()->download($tempFile, $fileName)->deleteFileAfterSend(true);
    }
    public function drwxlsx(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime(str_replace('/', '-', $request->date_stop));
        
        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);

        $start_date = $date.' '.$request->from;
        $end_date = $date_end.' '.$request->to;

        $site_id = $request->site;
        $device_id = $request->device;
        $tipe = $request->tipe;
        $site_ids = explode(",",$site_id);
        $device_ids = explode(",",$device_id);
        
        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));

        $data = MCountPeople::SelectDRWeeklyV3($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $i = 0;
        $arrName = [];
        $pivotTable = [];
        foreach ($data as $row) {
            $arrName[$i] = $row->site_name;
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $outValue = $row->total_exit;
            $week = "Week " . $row->week . " - " . $row->month;

            if (!isset($pivotTable[$week])) {
                $pivotTable[$week] = array();
            }
            $pivotTable[$week]['name/in/'.$name] = $inValue;
            $pivotTable[$week]['name/out/'.$name] = $outValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    $newKey = str_replace('name/out/', '', $key);
                    $nameOut[$newKey] = $value;
                }
            }
        
            return [
                'week' => $storeName,
                'name_in' => $nameIn,
                'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);
        $uniqueNames = array_unique($arrName);

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Define the headers for the Excel columns
        $headers = ['Week'];
        foreach ($uniqueNames as $name) {
            if ($tipe == "all") {
                $headers[] = $name . " / in";
                $headers[] = $name . " / out";
            } elseif ($tipe == "1") {
                $headers[] = $name . " / in";
            } elseif ($tipe == "2") {
                $headers[] = $name . " / out";
            }
        }

        // Set the headers in the first row
        $sheet->fromArray($headers, NULL, 'A1');

        // Fill in the rows
        $rowIndex = 2;
        foreach ($result as $item) {
            $row = [];
            $row[] = $item['week'];
            //$row[] = $item['day'];

            foreach ($uniqueNames as $name) {
                if ($tipe == "all") {
                    $row[] = $item['name_in'][$name] ?? 0;
                    $row[] = $item['name_out'][$name] ?? 0;
                } elseif ($tipe == "1") {
                    $row[] = $item['name_in'][$name] ?? 0;
                } elseif ($tipe == "2") {
                    $row[] = $item['name_out'][$name] ?? 0;
                }
            }

            $sheet->fromArray($row, NULL, 'A' . $rowIndex);
            $rowIndex++;
        }
        
        $writer = new Xlsx($spreadsheet);
        $fileName = 'dashboard_range_weekly_'.$date.'.xlsx';
        $tempFile = tempnam(sys_get_temp_dir(), $fileName);
        $writer->save($tempFile);

        // Return the file as a response
        return response()->download($tempFile, $fileName)->deleteFileAfterSend(true);
    }
    public function drwcsv(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime(str_replace('/', '-', $request->date_stop));
        
        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);

        $start_date = $date.' '.$request->from;
        $end_date = $date_end.' '.$request->to;

        $site_id = $request->site;
        $device_id = $request->device;
        $tipe = $request->tipe;
        $site_ids = explode(",",$site_id);
        $device_ids = explode(",",$device_id);
        
        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));

        $data = MCountPeople::SelectDRWeeklyV3($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $i = 0;
        $arrName = [];
        $pivotTable = [];
        foreach ($data as $row) {
            $arrName[$i] = $row->site_name;
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $outValue = $row->total_exit;
            $week = "Week " . $row->week . " - " . $row->month;

            if (!isset($pivotTable[$week])) {
                $pivotTable[$week] = array();
            }
            $pivotTable[$week]['name/in/'.$name] = $inValue;
            $pivotTable[$week]['name/out/'.$name] = $outValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    $newKey = str_replace('name/out/', '', $key);
                    $nameOut[$newKey] = $value;
                }
            }
        
            return [
                'week' => $storeName,
                'name_in' => $nameIn,
                'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);
        $uniqueNames = array_unique($arrName);

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Define the headers for the Excel columns
        $headers = ['Week'];
        foreach ($uniqueNames as $name) {
            if ($tipe == "all") {
                $headers[] = $name . " / in";
                $headers[] = $name . " / out";
            } elseif ($tipe == "1") {
                $headers[] = $name . " / in";
            } elseif ($tipe == "2") {
                $headers[] = $name . " / out";
            }
        }

        // Set the headers in the first row
        $sheet->fromArray($headers, NULL, 'A1');

        // Fill in the rows
        $rowIndex = 2;
        foreach ($result as $item) {
            $row = [];
            $row[] = $item['week'];
            //$row[] = $item['day'];

            foreach ($uniqueNames as $name) {
                if ($tipe == "all") {
                    $row[] = $item['name_in'][$name] ?? 0;
                    $row[] = $item['name_out'][$name] ?? 0;
                } elseif ($tipe == "1") {
                    $row[] = $item['name_in'][$name] ?? 0;
                } elseif ($tipe == "2") {
                    $row[] = $item['name_out'][$name] ?? 0;
                }
            }

            $sheet->fromArray($row, NULL, 'A' . $rowIndex);
            $rowIndex++;
        }
        
        $writer = new Csv($spreadsheet);
        $fileName = 'dashboard_range_weekly_'.$date.'.csv';
        $tempFile = tempnam(sys_get_temp_dir(), $fileName);
        $writer->save($tempFile);

        // Return the file as a response
        return response()->download($tempFile, $fileName)->deleteFileAfterSend(true);
    }

    public function drmxlsx(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime(str_replace('/', '-', $request->date_stop));
        
        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);

        $start_date = $date.' '.$request->from;
        $end_date = $date_end.' '.$request->to;

        $site_id = $request->site;
        $device_id = $request->device;
        $tipe = $request->tipe;
        $site_ids = explode(",",$site_id);
        $device_ids = explode(",",$device_id);
        
        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));

        $data = MCountPeople::SelectDRMonthlyV3($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $i = 0;
        $arrName = [];
        $pivotTable = [];
        foreach ($data as $row) {
            $arrName[$i] = $row->site_name;
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $outValue = $row->total_exit;
            $week = $row->month . " - " . $row->year;

            if (!isset($pivotTable[$week])) {
                $pivotTable[$week] = array();
            }
            $pivotTable[$week]['name/in/'.$name] = $inValue;
            $pivotTable[$week]['name/out/'.$name] = $outValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    $newKey = str_replace('name/out/', '', $key);
                    $nameOut[$newKey] = $value;
                }
            }
        
            return [
                'month' => $storeName,
                'name_in' => $nameIn,
                'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);
        $uniqueNames = array_unique($arrName);

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Define the headers for the Excel columns
        $headers = ['Month'];
        foreach ($uniqueNames as $name) {
            if ($tipe == "all") {
                $headers[] = $name . " / in";
                $headers[] = $name . " / out";
            } elseif ($tipe == "1") {
                $headers[] = $name . " / in";
            } elseif ($tipe == "2") {
                $headers[] = $name . " / out";
            }
        }

        // Set the headers in the first row
        $sheet->fromArray($headers, NULL, 'A1');

        // Fill in the rows
        $rowIndex = 2;
        foreach ($result as $item) {
            $row = [];
            $row[] = $item['month'];
            //$row[] = $item['day'];

            foreach ($uniqueNames as $name) {
                if ($tipe == "all") {
                    $row[] = $item['name_in'][$name] ?? 0;
                    $row[] = $item['name_out'][$name] ?? 0;
                } elseif ($tipe == "1") {
                    $row[] = $item['name_in'][$name] ?? 0;
                } elseif ($tipe == "2") {
                    $row[] = $item['name_out'][$name] ?? 0;
                }
            }

            $sheet->fromArray($row, NULL, 'A' . $rowIndex);
            $rowIndex++;
        }
        
        $writer = new Xlsx($spreadsheet);
        $fileName = 'dashboard_range_monthly_'.$date.'.xlsx';
        $tempFile = tempnam(sys_get_temp_dir(), $fileName);
        $writer->save($tempFile);

        // Return the file as a response
        return response()->download($tempFile, $fileName)->deleteFileAfterSend(true);
    }
    public function drmcsv(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime(str_replace('/', '-', $request->date_stop));
        
        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);

        $start_date = $date.' '.$request->from;
        $end_date = $date_end.' '.$request->to;

        $site_id = $request->site;
        $device_id = $request->device;
        $tipe = $request->tipe;
        $site_ids = explode(",",$site_id);
        $device_ids = explode(",",$device_id);
        
        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));

        $data = MCountPeople::SelectDRMonthlyV3($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $i = 0;
        $arrName = [];
        $pivotTable = [];
        foreach ($data as $row) {
            $arrName[$i] = $row->site_name;
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $outValue = $row->total_exit;
            $week = $row->month . " - " . $row->year;

            if (!isset($pivotTable[$week])) {
                $pivotTable[$week] = array();
            }
            $pivotTable[$week]['name/in/'.$name] = $inValue;
            $pivotTable[$week]['name/out/'.$name] = $outValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    $newKey = str_replace('name/out/', '', $key);
                    $nameOut[$newKey] = $value;
                }
            }
        
            return [
                'month' => $storeName,
                'name_in' => $nameIn,
                'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);
        $uniqueNames = array_unique($arrName);

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Define the headers for the Excel columns
        $headers = ['Month'];
        foreach ($uniqueNames as $name) {
            if ($tipe == "all") {
                $headers[] = $name . " / in";
                $headers[] = $name . " / out";
            } elseif ($tipe == "1") {
                $headers[] = $name . " / in";
            } elseif ($tipe == "2") {
                $headers[] = $name . " / out";
            }
        }

        // Set the headers in the first row
        $sheet->fromArray($headers, NULL, 'A1');

        // Fill in the rows
        $rowIndex = 2;
        foreach ($result as $item) {
            $row = [];
            $row[] = $item['month'];
            //$row[] = $item['day'];

            foreach ($uniqueNames as $name) {
                if ($tipe == "all") {
                    $row[] = $item['name_in'][$name] ?? 0;
                    $row[] = $item['name_out'][$name] ?? 0;
                } elseif ($tipe == "1") {
                    $row[] = $item['name_in'][$name] ?? 0;
                } elseif ($tipe == "2") {
                    $row[] = $item['name_out'][$name] ?? 0;
                }
            }

            $sheet->fromArray($row, NULL, 'A' . $rowIndex);
            $rowIndex++;
        }
        
        $writer = new Csv($spreadsheet);
        $fileName = 'dashboard_range_monthly_'.$date.'.csv';
        $tempFile = tempnam(sys_get_temp_dir(), $fileName);
        $writer->save($tempFile);

        // Return the file as a response
        return response()->download($tempFile, $fileName)->deleteFileAfterSend(true);
    }

    public function dryxlsx(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime(str_replace('/', '-', $request->date_stop));
        
        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);

        $start_date = $date.' '.$request->from;
        $end_date = $date_end.' '.$request->to;

        $site_id = $request->site;
        $device_id = $request->device;
        $tipe = $request->tipe;
        $site_ids = explode(",",$site_id);
        $device_ids = explode(",",$device_id);
        
        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));

        $data = MCountPeople::SelectDRYearlyV3($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $i = 0;
        $arrName = [];
        $pivotTable = [];
        foreach ($data as $row) {
            $arrName[$i] = $row->site_name;
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $outValue = $row->total_exit;
            $week = $row->year . "";

            if (!isset($pivotTable[$week])) {
                $pivotTable[$week] = array();
            }
            $pivotTable[$week]['name/in/'.$name] = $inValue;
            $pivotTable[$week]['name/out/'.$name] = $outValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    $newKey = str_replace('name/out/', '', $key);
                    $nameOut[$newKey] = $value;
                }
            }
        
            return [
                'year' => $storeName,
                'name_in' => $nameIn,
                'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);
        $uniqueNames = array_unique($arrName);

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Define the headers for the Excel columns
        $headers = ['Year'];
        foreach ($uniqueNames as $name) {
            if ($tipe == "all") {
                $headers[] = $name . " / in";
                $headers[] = $name . " / out";
            } elseif ($tipe == "1") {
                $headers[] = $name . " / in";
            } elseif ($tipe == "2") {
                $headers[] = $name . " / out";
            }
        }

        // Set the headers in the first row
        $sheet->fromArray($headers, NULL, 'A1');

        // Fill in the rows
        $rowIndex = 2;
        foreach ($result as $item) {
            $row = [];
            $row[] = $item['year'];
            //$row[] = $item['day'];

            foreach ($uniqueNames as $name) {
                if ($tipe == "all") {
                    $row[] = $item['name_in'][$name] ?? 0;
                    $row[] = $item['name_out'][$name] ?? 0;
                } elseif ($tipe == "1") {
                    $row[] = $item['name_in'][$name] ?? 0;
                } elseif ($tipe == "2") {
                    $row[] = $item['name_out'][$name] ?? 0;
                }
            }

            $sheet->fromArray($row, NULL, 'A' . $rowIndex);
            $rowIndex++;
        }
        
        $writer = new Xlsx($spreadsheet);
        $fileName = 'dashboard_range_yearly_'.$date.'.xlsx';
        $tempFile = tempnam(sys_get_temp_dir(), $fileName);
        $writer->save($tempFile);

        // Return the file as a response
        return response()->download($tempFile, $fileName)->deleteFileAfterSend(true);
    }
    public function drycsv(Request $request){
        config(['database.default' => 'user_connection']);
        //dd($request);
        $originalDate = strtotime(str_replace('/', '-', $request->date));
        $originalDateEnd = strtotime(str_replace('/', '-', $request->date_stop));
        
        $date = date('Y-m-d',$originalDate);
        $date_end = date('Y-m-d',$originalDateEnd);

        $start_date = $date.' '.$request->from;
        $end_date = $date_end.' '.$request->to;

        $site_id = $request->site;
        $device_id = $request->device;
        $tipe = $request->tipe;
        $site_ids = explode(",",$site_id);
        $device_ids = explode(",",$device_id);
        
        $start_time = intval(substr($request->from,0,2));
        $end_time = intval(substr($request->to,0,2));

        $data = MCountPeople::SelectDRYearlyV3($start_date,$end_date,$site_ids,$device_ids,$start_time,$end_time)->get();
        $i = 0;
        $arrName = [];
        $pivotTable = [];
        foreach ($data as $row) {
            $arrName[$i] = $row->site_name;
            $i++;
            $name = $row->site_name;
            $inValue = $row->total_enter;
            $outValue = $row->total_exit;
            $week = $row->year . "";

            if (!isset($pivotTable[$week])) {
                $pivotTable[$week] = array();
            }
            $pivotTable[$week]['name/in/'.$name] = $inValue;
            $pivotTable[$week]['name/out/'.$name] = $outValue;
        }
        $result = array_map(function ($storeName, $kvp) {
            $nameIn = [];
            //$nameOut = [];
        
            foreach ($kvp as $key => $value) {
                if (strpos($key, 'name/in/') === 0) {
                    $newKey = str_replace('name/in/', '', $key);
                    $nameIn[$newKey] = $value;
                } elseif (strpos($key, 'name/out/') === 0) {
                    $newKey = str_replace('name/out/', '', $key);
                    $nameOut[$newKey] = $value;
                }
            }
        
            return [
                'year' => $storeName,
                'name_in' => $nameIn,
                'name_out' => $nameOut
            ];
        }, array_keys($pivotTable), $pivotTable);
        $uniqueNames = array_unique($arrName);

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Define the headers for the Excel columns
        $headers = ['Year'];
        foreach ($uniqueNames as $name) {
            if ($tipe == "all") {
                $headers[] = $name . " / in";
                $headers[] = $name . " / out";
            } elseif ($tipe == "1") {
                $headers[] = $name . " / in";
            } elseif ($tipe == "2") {
                $headers[] = $name . " / out";
            }
        }

        // Set the headers in the first row
        $sheet->fromArray($headers, NULL, 'A1');

        // Fill in the rows
        $rowIndex = 2;
        foreach ($result as $item) {
            $row = [];
            $row[] = $item['year'];
            //$row[] = $item['day'];

            foreach ($uniqueNames as $name) {
                if ($tipe == "all") {
                    $row[] = $item['name_in'][$name] ?? 0;
                    $row[] = $item['name_out'][$name] ?? 0;
                } elseif ($tipe == "1") {
                    $row[] = $item['name_in'][$name] ?? 0;
                } elseif ($tipe == "2") {
                    $row[] = $item['name_out'][$name] ?? 0;
                }
            }

            $sheet->fromArray($row, NULL, 'A' . $rowIndex);
            $rowIndex++;
        }
        
        $writer = new Csv($spreadsheet);
        $fileName = 'dashboard_range_yearly_'.$date.'.csv';
        $tempFile = tempnam(sys_get_temp_dir(), $fileName);
        $writer->save($tempFile);

        // Return the file as a response
        return response()->download($tempFile, $fileName)->deleteFileAfterSend(true);
    }

    public function _decrypt_key($text,$key){
        //$encryptedData = base64_decode($text);
        $decryptedData = openssl_decrypt($text,'aes-128-ecb', $key,0,"");
        return $decryptedData;
    }

    public function create_pass(){
        $pass = 'M0d!C0unt2025';
        $encrypted = Crypt::encryptString($pass);       
        $decrypted = Crypt::decryptString($encrypted);
        print_r($encrypted.'<br><br>');
        print_r($decrypted.'<br><br>');
    }
}
