<?php

namespace App\Http\Controllers;
use Session;

use Illuminate\Http\Request;
use App\Models\Organization;
use App\Models\Building;
use App\Models\Floorplan;
use App\Models\FloorplanMachines;
use App\Models\SchoolYear;
use App\Models\MachineArchive;
use App\Models\ModelDetail;
use App\Models\MachineStatus;
use App\Models\Meter;

use DB;

class Device_SummariesController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
    	$year = 20;    
    	$org_id = 15;
    	$varName = 'year';
    	session([$varName => $year]); 
       
    
    
    	//Session::set('variableName', 455);
        return view('device_summaries.testPage');
       
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create()
    {
        //
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        //
    }

	/**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function show($id)
    {
    	
    	//$device_data = ['44', '25', '64'];
    return view('device_summaries.show')
        ->with('orgs', Organization::orderBy('org_name')->get()->where('client_status', 'A'))
        ->with('bldgs', Building::orderBy('bldg_name')->get())
        ->with('years', SchoolYear::orderBy('year_id', 'desc')->get());
    }

	public function show_toner_data($summary)
    {
     	$org_id = session('org_id');	
     	$year = session('year');
		$color = session('color');
		$yearTab = session('yearTab');
		
		$minYear = $year - 7;
    
    	/*Correct years are returned, but the $year variable is set to 19, so it looks like data is incorrect. Leave this as is.*/
    	$years = DB::table('school_years')
    	            ->select(DB::raw('school_year, year_id'))
    	            ->where('year_id', '>=', $minYear)->get();
    	
    	$serNumDate = DB::table($yearTab . '_machine_statuses AS mstat')
                       ->select(DB::raw('max(mstat.created_date) as dt, mstat.serial_number')) 
                       ->where('mstat.created_date', '>', $startingDateFormat)
                       ->where('mstat.created_date', '<', $endingDateFormat)
                       ->groupBy('mstat.serial_number');
                       //->get(); This changes the $serNumDate from a string so an error occurs
    
    
    	$toner_data = DB::table($yearTab . '_machine_statuses AS ms')
        				/*List only the cols needed instead of selecting all from all joined tables.*/
        				->select(DB::raw('orgs.org_name, fpm.proposed_serial_number, fpm.proposed_model_id,
							fpm.fpm_id, bldg.bldg_name, fpm.room_name, md.mach_make, md.model,
                            fpm.`ip_address`, `fpm`.`mac_address`, fpm.budgeted_black,
                            fpm.budgeted_color, ms.toner, ms.service_needed')) 
        
    				->joinSub($serNumDate, 'serNumDate', function($join){
            												$join->on('ms.serial_number', '=', 'serNumDate.serial_number');
                    										$join->on('ms.created_date', '=', 'serNumDate.dt');})
        
        			->join($yearTab . '_floorplan_machines as fpm', 'fpm.proposed_serial_number', '=', 'ms.serial_number')
        			->join($yearTab . '_floorplans as fp', 'fp.fp_id', '=', 'fpm.floorplans_id')
        			->join('model_details as md', 'md.model_id', '=', 'fpm.proposed_model_id')
        			->join($yearTab . '_buildings as bldg', 'bldg.bldg_id', '=', 'fp.buildings_id')
     				->join('organizations as orgs', 'orgs.org_id', '=', 'bldg.organizations_id')
    				
            		
                     ->whereIn('ms.serial_number', function($query) use ($yearTab, $org_id){                    
                        									$query->select('fpm.proposed_serial_number')
                                                                ->from($yearTab . '_floorplan_machines AS fpm')
                                                                ->join($yearTab . '_floorplans AS fp', 'fp.fp_id', '=', 'fpm.floorplans_id')
                                                                ->join($yearTab . '_buildings AS bldg', 'bldg.bldg_id', '=', 'fp.buildings_id')
                              									->where('bldg.organizations_id', '=', $org_id);
                              									
                        									})
        			->where('toner', '=', 'Y')->get();   
        
     	
		//dd($toner_data);
	
		/*To get the total of devides w/toner alert, count the records in the array(resultset)*/
		//$toner_alert_count = $toner_data->count();
     	//$service_needed_count = $service_data->count();
		//$contract_devices_count = $contract_devices->count();
    		                        
     	return view('device_summaries.show_toner_data', compact('toner_data', 'org_id', 'year', 'yearTab', 'years', 'color', 'toner_data', 'summary'))
			->with('floorplans', Floorplan::all())
			->with('bldgs', Building::orderBy('bldg_name')->get()->where('organizations_id', $org_id))
     		//->with('years', SchoolYear::orderBy('year_id')->get()->where('year_id', '>=', $minYear))        
			->with('orgs', Organization::orderBy('org_name')->get()->where('client_status', 'A'));
		
    }

	public function show_service_data($summary)
    {
     	$org_id = session('org_id');	
     	$year = session('year');
		$color = session('color');
		$yearTab = session('yearTab'); 
    
    
    	
    
    
    /*************BEGIN ADDED ON 2/17/2021*******************/
    
    $serNumDate = DB::table($yearTab . '_machine_statuses')
                       ->select(DB::raw('max(created_date) as dt, serial_number'))           
                       ->groupBy('serial_number');
    
    
    $service_needed = DB::table($yearTab . '_machine_statuses AS ms')
    				->joinSub($serNumDate, 'serNumDate', function($join){
            												$join->on('ms.serial_number', '=', 'serNumDate.serial_number');
                    										$join->on('ms.created_date', '=', 'serNumDate.dt');})    
    
     /*************END ADDED ON 2/17/2021*******************/
    
    
    
    
    				
     				->join($yearTab . '_floorplan_machines as fpm', 'fpm.proposed_serial_number', '=', 'ms.serial_number')
        			->join('floorplans as fp', 'fp.fp_id', '=', 'fpm.floorplans_id')
        			->join('model_details as mach', 'mach.model_id', '=', 'fpm.proposed_model_id')
        			->join('buildings as bldg', 'bldg.bldg_id', '=', 'fp.buildings_id')
     				->join('organizations as orgs', 'orgs.org_id', '=', 'bldg.organizations_id')
            		
        
        			->whereIn('ms.serial_number', function($query) use ($org_id, $year){                    
                        									$query->select('serial_number')
                                                                ->from('machine_archive')
                              									->where('organizations_id', '=', $org_id)
                              									->where('school_year', '=', $year);
                        									})
        			->where('service_needed', '=', '1')->get();
    
        
    	return view('device_summaries.show_service_needed', compact('org_id', 'year', 'color', 'yearTab', 'service_needed'))
			->with('floorplans', Floorplan::all())
			->with('bldgs', Building::orderBy('bldg_name')->get()->where('organizations_id', $org_id))
     		->with('years', SchoolYear::orderBy('year_id')->get())        
			->with('orgs', Organization::orderBy('org_name')->get()->where('client_status', 'A'));
    }

	
	public function show_contract_devices($summary)
    {
     	$org_id = session('org_id');	
     	$year = session('year');
		$color = session('color');
		$yearTab = session('yearTab');
		
		$minYear = $year - 7;
        
        
        
         /*These are string values for the starting and ending dates based on the value in the Timeline Date datepicker*/
        $startingDateInput = '';
        $startingDateFormat = '';
        $endingDateFormat = '';
        
        /*UNIX timestamps*/
        $startingDate = '';
        $endingDate = '';
        
        $dateTime = '';
        
        
        /*fiscal days*/
        $fiscal_days = 0;
        $org_fiscal_days = 0;
        
        if (!isset($_SESSION['session_date'])) {
              $dateTime = strtotime('-4 hours'); //subtract 4 hours to get eastern time zone.
              $_SESSION['session_date'] = strtotime('now');
              $_SESSION['session_fiscal_year'] = "";
        }
    	
    	$today = date_create(date('Y-m-d', $dateTime));
        
        $currentYear = 2019; //$today->format('Y');
    	
    	// Get the fiscal starting date (7/1/yyyy)
    	if ($today->format('m') <= 6) {
    		$startingDateInput = '07/01/'.($currentYear-1);
    	}
    	else {
    		$startingDateInput = '07/01/'.($currentYear);
    	}
    	
    	$startingDate = strtotime($startingDateInput);
    	$startingDateFormat = date('Y/m/d H:i:s', $startingDate);
        
        // Get the fiscal ending date (6/30/yyyy)
    	$endingDate = strtotime($startingDateInput);
    	$endingDate = $endingDate + (364*60*60*24);
    	$endingDateFormat = date('Y/m/d H:i:s', $endingDate);
    	
    	
    	/*Correct years are returned, but the $year variable is set to 19, so it looks like data is incorrect. Leave this as is.*/
    	$years = DB::table('school_years')
    	            ->select(DB::raw('school_year, year_id'))
    	            ->where('year_id', '>=', $minYear)->get();
    
    	
        $serNumDate = DB::table($yearTab . '_machine_statuses')
                       	->select(DB::raw('max(created_date) as dt, serial_number'))
                       	->where('created_date', '>', $startingDateFormat)
                        ->where('created_date', '<', $endingDateFormat)
                       	->groupBy('serial_number');
                       	
                       	
                       	
        /*Used as a subquery in several queries*/	
        $serNumDateMetersMax = DB::table($yearTab . '_meters')
                           ->select(DB::raw('max(created_date) as dt, serial_number'))
                           ->where('created_date', '>', $startingDateFormat)
                           ->where('created_date', '<', $endingDateFormat)
                           ->groupBy('serial_number');
        				
    
    	/*Desired query
    	SELECT * FROM Y2019_floorplan_machines fpm
		INNER JOIN floorplans fp ON fp.fp_id = fpm.floorplan_id
        INNER JOIN buildings bldg ON bldg.bldg_id = fp.building_id
		WHERE serial_number in (SELECT SerialNumber FROM Machine_Archive
   								WHERE org_id_ma = 15 AND school_year = 19)
		AND model_id != 790 
		AND save_name_id IS NOT NULL;
        
      Actual query
      	select * from `y2019_floorplan_machines` as `fpm`
        inner join `floorplans` as `fp` on `fp`.`id` = `fpm`.`floorplans_id`
        inner join `buildings` as `bldg` on `bldg`.`id` = `fp`.`buildings_id`
        where `fpm`.`serial_number` in (select `serial_number` from `machine_archive`
        								where `org_id` = 15 and `school_year` = 19)
        and `fpm`.`machines_id` != 790 
        nd `fpm`.`fyer_groups_id` is not null */	
     
     
     /*$serNumDateMtr = DB::table($yearTab . '_meters')
                       	->select(DB::raw('max(created_date) as dt, serial_number'))           
                       	->groupBy('serial_number');*/
     
    /*This works. org 1838 is missing 1 rec vs stardoc because the ser num is not in the test y2019_machine_statuses table */
    $contract_devices = DB::table($yearTab . '_floorplan_machines as fpm')
        //->leftJoin($yearTab . '_machine_statuses as ms', 'ms.serial_number', '=', 'fpm.proposed_serial_number')
        
        ->leftJoin($yearTab . '_machine_statuses as ms', function ($join) use($serNumDate, $startingDateFormat, $endingDateFormat){
                                                        $join->on('ms.serial_number', '=', 'fpm.proposed_serial_number')
                                                        ->joinSub($serNumDate, 'serNumDate', function($join){
                											$join->on('ms.serial_number', '=', 'serNumDate.serial_number');
                    										$join->on('ms.created_date', '=', 'serNumDate.dt');});
            
        })
        
                    
        
        
     
        /*->joinSub($serNumDate, 'serNumDate', function($join){
											$join->on('ms.serial_number', '=', 'serNumDate.serial_number');
    										$join->on('ms.created_date', '=', 'serNumDate.dt');})*/
     
     					->join($yearTab . '_floorplans as fp', 'fp.fp_id', '=', 'fpm.floorplans_id')
     					->join('model_details as mach', 'mach.model_id', '=', 'fpm.proposed_model_id')
     					->leftjoin($yearTab . '_buildings as bldg', 'bldg.bldg_id', '=', 'fp.buildings_id')
     					->join('organizations as orgs', 'orgs.org_id', '=', 'bldg.organizations_id')     					
    
    					->whereIn('fpm.floorplans_id', function($query)  use ($yearTab, $org_id){ /*Pass org_id to the inner query*/
            											$query->select('fp.fp_id')
                                                    	->from($yearTab . '_floorplans as fp')
                                                        ->join($yearTab . '_buildings as bldgs', 'bldgs.bldg_id', '=', 'fp.buildings_id')
                                                        ->whereIn('fp.buildings_id', function($queryF) use ($yearTab, $org_id){
                                                        							$queryF->select('bldg_id')
                                                                                    ->from($yearTab . '_buildings')                                                                                  
                                                    								->where('organizations_id', '=', $org_id);                                                                                                                
            																		});
                        								})
        				->where('fpm.proposed_model_id', '!=', 790)
        				->whereNotNull('fpm.fyer_groups_id')->get();//->toSql(); //->get()
     
     //dd($contract_devices);
    
    	return view('device_summaries.show_contract_devices', compact('org_id', 'year', 'color', 'yearTab', 'years', 'contract_devices'))
			->with('floorplans', Floorplan::all())
			->with('bldgs', Building::orderBy('bldg_name')->get()->where('organizations_id', $org_id))
     		//->with('years', SchoolYear::orderBy('year_id')->get())        
			->with('orgs', Organization::orderBy('org_name')->get()->where('client_status', 'A'));
   
   
    /*Actual query
     * select * from `y2019_floorplan_machines` as `fpm`
inner join `y2019_machine_statuses` as `ms` on `ms`.`serial_number` = `fpm`.`serial_number`
inner join (select max(created_date) as dt, serial_number
            from `y2019_machine_status`
            group by `serial_number`) as `serNumDate` on `ms`.`serial_number` = `serNumDate`.`serial_number`
            and `ms`.`created_date` = `serNumDate`.`dt`
inner join `floorplans` as `fp` on `fp`.`id` = `fpm`.`floorplans_id`
inner join `machines` as `mach` on `mach`.`id` = `fpm`.`machines_id`
left join `buildings` as `bldg` on `bldg`.`id` = `fp`.`buildings_id`
inner join `organizations` as `orgs` on `orgs`.`id` = `bldg`.`organizations_id`
where `fpm`.`floorplans_id` in (select `fp`.`id` from `floorplans` as `fp`
                                inner join `buildings` as `bldgs` on `bldgs`.`id` = `fp`.`buildings_id`
                                where `fp`.`buildings_id` in (select `id` from `buildings`
                                                              where `organizations_id` = 4))
and `fpm`.`machines_id` != 790 and `fpm`.`fyer_groups_id` is not null; */
    
    
    }



public function show_non_contract_devices($summary)
    {
     	$org_id = session('org_id');	
     	$year = session('year');
		$color = session('color');
		$yearTab = session('yearTab');
    
    	
        $serNumDate = DB::table($yearTab . '_machine_statuses')
                       	->select(DB::raw('max(created_date) as dt, serial_number'))           
                       	->groupBy('serial_number');
        				
    
    	/*Desired query
    	SELECT * FROM Y2019_floorplan_machines fpm
		INNER JOIN floorplans fp ON fp.fp_id = fpm.floorplan_id
        INNER JOIN buildings bldg ON bldg.bldg_id = fp.building_id
		WHERE serial_number in (SELECT SerialNumber FROM Machine_Archive
   								WHERE org_id_ma = 15 AND school_year = 19)
		AND model_id != 790 
		AND save_name_id IS NOT NULL;
        
      Actual query
      	select * from `y2019_floorplan_machines` as `fpm`
        inner join `floorplans` as `fp` on `fp`.`id` = `fpm`.`floorplans_id`
        inner join `buildings` as `bldg` on `bldg`.`id` = `fp`.`buildings_id`
        where `fpm`.`serial_number` in (select `serial_number` from `machine_archive`
        								where `org_id` = 15 and `school_year` = 19)
        and `fpm`.`machines_id` != 790 
        nd `fpm`.`fyer_groups_id` is not null */
        
     $non_contract_model_id = 790;
     
     $non_contract_devices = DB::table($yearTab . '_floorplan_machines as fpm')
                            ->join($yearTab . '_machine_statuses as ms', 'ms.serial_number', '=', 'fpm.proposed_serial_number')
                            ->joinSub($serNumDate, 'serNumDate', function($join){
            												$join->on('ms.serial_number', '=', 'serNumDate.serial_number');
                    										$join->on('ms.created_date', '=', 'serNumDate.dt');})
     
         					->join($yearTab . '_floorplans as fp', 'fp.fp_id', '=', 'fpm.floorplans_id')
         					->join($yearTab . '_model_details as mach', 'mach.model_id', '=', 'fpm.proposed_model_id')
         					->join($yearTab . '_buildings as bldg', 'bldg.bldg_id', '=', 'fp.buildings_id')
         					->join('organizations as orgs', 'orgs.org_id', '=', 'bldg.organizations_id')
         					
            				->whereIn('fpm.proposed_serial_number', function($query) use ($org_id, $year){
                											$query->select('serial_number')
                                                        	->from('machine_archive')
                                                        	->where('organizations_id', '=', $org_id)
                                                            ->where('school_year', '=',  $year);
                										})
            				->where('fpm.proposed_model_id', '!=', $non_contract_model_id)
            				->whereNull('fpm.fyer_groups_id')->get(); //toSql();
            				
        /*echo $non_contract_devices  . '<br>';
        echo $year . '<br>';
        echo $org_id . '<br>';
        echo $non_contract_model_id;*/
     
     
    
    	return view('device_summaries.show_non_contract_devices', compact('org_id', 'year', 'color', 'yearTab', 'non_contract_devices'))
			->with('floorplans', Floorplan::all())
			->with('bldgs', Building::orderBy('bldg_name')->get()->where('organizations_id', $org_id))
     		->with('years', SchoolYear::orderBy('year_id')->get())        
			->with('orgs', Organization::orderBy('org_name')->get()->where('client_status', 'A'));
    }
    
    
	public function show_reporting_devices($summary)
    {
     	$org_id = session('org_id');	
     	$year = session('year');
		$color = session('color');
		$yearTab = session('yearTab'); 
    
        $minYear = $year - 7;
        
        
         /*These are string values for the starting and ending dates based on the value in the Timeline Date datepicker*/
        $startingDateInput = '';
        $startingDateFormat = '';
        $endingDateFormat = '';
        
        /*UNIX timestamps*/
        $startingDate = '';
        $endingDate = '';
        
        $dateTime = '';
        
        
        /*fiscal days*/
        $fiscal_days = 0;
        $org_fiscal_days = 0;
        
        if (!isset($_SESSION['session_date'])) {
              $dateTime = strtotime('-4 hours'); //subtract 4 hours to get eastern time zone.
              $_SESSION['session_date'] = strtotime('now');
              $_SESSION['session_fiscal_year'] = "";
        }
    	
    	$today = date_create(date('Y-m-d', $dateTime));
        
        $currentYear = 2019; //$today->format('Y');
    	
    	// Get the fiscal starting date (7/1/yyyy)
    	if ($today->format('m') <= 6) {
    		$startingDateInput = '07/01/'.($currentYear-1);
    	}
    	else {
    		$startingDateInput = '07/01/'.($currentYear);
    	}
    	
    	$startingDate = strtotime($startingDateInput);
    	$startingDateFormat = date('Y/m/d H:i:s', $startingDate);
        
        // Get the fiscal ending date (6/30/yyyy)
    	$endingDate = strtotime($startingDateInput);
    	$endingDate = $endingDate + (364*60*60*24);
    	$endingDateFormat = date('Y/m/d H:i:s', $endingDate);
    	
    	$fiscal_days = ($endingDate - $startingDate) /60/60/24;
        
        // **Note: not reporting changed from 30 days to 15 days on 10/13/2013 per Skip
    	
        /*Create the notReportingDate fresh from the same value as the $_SESSION['session_date'] is created.
            Since the $_SESSION['session_date'] is a dateInterval object, it is a reference variable and
            will be modified when date_sub is used with a variable that it is assigned to. When I was using
            $notReportingDate = $_SESSION['session_date'];
            $notReportingDate = date_sub($notReportingDate, date_interval_create_from_date_string('15 day'));
            the $_SESSION['session_date'] was being changed to
            6/15/2019 to reflect the date_sub function that was done on notReportingDate.*/
        
        //$notReportingDate = strtotime($startingDateInput);
        //$notReportingDate = strtotime($endingDateInput);
        $notReportingDate = $endingDate - (15*60*60*24);
        $notReportingDateFormat = date('Y-m-d H:i:s', $notReportingDate);
    	
    	
    	
    	/*Correct years are returned, but the $year variable is set to 19, so it looks like data is incorrect. Leave this as is.*/
    	$years = DB::table('school_years')
    	            ->select(DB::raw('school_year, year_id'))
    	            ->where('year_id', '>=', $minYear)->get();
    
    
    	$serNumDate = DB::table($yearTab . '_machine_statuses')
                       	->select(DB::raw('max(created_date) as dt, serial_number'))
                       	->where('created_date', '>', $startingDateFormat)
                        ->where('created_date', '<=', $endingDateFormat)
                       	->groupBy('serial_number');
       	
    	$lastMeterRead = DB::table($yearTab . '_meters')
                       ->select(DB::raw('max(created_date) as mtr_dt, serial_number'))
                       ->where('created_date', '>', $startingDateFormat)
                       ->where('created_date', '<=', $endingDateFormat)
                       ->groupBy('serial_number');
    	
        				
        				
        $reporting_devices = DB::table($yearTab . '_floorplan_machines as fpm')
        				->join($yearTab . '_meters as m', function ($join) use($lastMeterRead){
                                                        $join->on('fpm.proposed_serial_number', '=', 'm.serial_number')
                                                        ->joinSub($lastMeterRead, 'lastMeterRead', function($query){
                											$query->on('m.serial_number', '=', 'lastMeterRead.serial_number');
                    										$query->on('m.created_date', '=', 'lastMeterRead.mtr_dt');});
        				})
        				
        				->join('model_details as md', 'md.model_id', '=', 'fpm.proposed_model_id')
        				
        				->join($yearTab . '_machine_statuses as ms', function ($join) use($serNumDate){
                                                        $join->on('ms.serial_number', '=', 'fpm.proposed_serial_number')
                                                        ->joinSub($serNumDate, 'serNumDate', function($join){
                											$join->on('ms.serial_number', '=', 'serNumDate.serial_number');
                    										$join->on('ms.created_date', '=', 'serNumDate.dt')
                    										->whereNotNull('ms.serial_number');});
        				})
        				
       					->join($yearTab . '_floorplans as fp', 'fp.fp_id', '=', 'fpm.floorplans_id')
        				->join($yearTab . '_buildings as bldgs', 'bldgs.bldg_id', '=', 'fp.buildings_id')
        				->join('organizations as orgs', 'orgs.org_id', '=', 'bldgs.organizations_id')
        				->select('orgs.org_name', 'fpm.*', 'bldgs.bldg_name', 'md.mach_make', 'md.model',
        				        'm.created_date', 'ms.toner', 'ms.service_needed', 'm.serial_number')
        				
        				
        				->where('bldgs.organizations_id', '=', $org_id)
        				->where('fpm.proposed_model_id', '!=', 790)
        				->whereNotNull('fpm.fyer_groups_id')
        				->where('m.created_date', '>', $notReportingDateFormat)->get();
          	    
    
    	return view('device_summaries.show_reporting_devices', compact('org_id', 'year', 'color', 'yearTab', 'reporting_devices'))
			->with('floorplans', Floorplan::all())
			->with('bldgs', Building::orderBy('bldg_name')->get()->where('organizations_id', $org_id))
     		->with('years', SchoolYear::orderBy('year_id')->get())        
			->with('orgs', Organization::orderBy('org_name')->get()->where('client_status', 'A'));
    }



	public function show_not_reporting_devices($summary)
    {
     	$org_id = session('org_id');	
     	$year = session('year');
		$color = session('color');
		$yearTab = session('yearTab'); 
    
        $minYear = $year - 7;
        
        
        /************************** BEGIN GET DATE VARIABLES **************************/
		/*This will be the date selected by the Select Timeline Date datepicker.
        For now, this is just a placeholder variable.*/
        $dateString = '';
        
        /*These are string values for the starting and ending dates based on the value in the Timeline Date datepicker*/
        $startingDateInput = '';
        $startingDateFormat = '';
        $endingDateFormat = '';
        
        /*UNIX timestamps*/
        $startingDate = '';
        $endingDate = '';
        
        $dateTime = '';
        
        
        /*fiscal days*/
        $fiscal_days = 0;
        $org_fiscal_days = 0;
        
        if (!isset($_SESSION['session_date'])) {
              $dateTime = strtotime('-4 hours'); //subtract 4 hours to get eastern time zone.
              $_SESSION['session_date'] = strtotime('now');
              $_SESSION['session_fiscal_year'] = "";
        }
        
        $today = date_create(date('Y-m-d', $dateTime));
        
        $currentYear = 2019; //$today->format('Y');
    
        
        // Get the fiscal starting date (7/1/yyyy)
    	if ($today->format('m') <= 6) {
    		$startingDateInput = '07/01/'.($currentYear-1);
    	}
    	else {
    		$startingDateInput = '07/01/'.($currentYear);
    	}
    	
    	$startingDate = strtotime($startingDateInput);
    	$startingDateFormat = date('Y-m-d H:i:s', $startingDate);
        
        // Get the fiscal ending date (6/30/yyyy)
    	$endingDate = strtotime($startingDateInput);
    	$endingDate = $endingDate + (364*60*60*24);
    	$endingDateFormat = date('Y-m-d H:i:s', $endingDate);
    	
    	$fiscal_days = ($endingDate - $startingDate) /60/60/24;
        
        // **Note: not reporting changed from 30 days to 15 days on 10/13/2013 per Skip
    	
        /*Create the notReportingDate fresh from the same value as the $_SESSION['session_date'] is created.
            Since the $_SESSION['session_date'] is a dateInterval object, it is a reference variable and
            will be modified when date_sub is used with a variable that it is assigned to. When I was using
            $notReportingDate = $_SESSION['session_date'];
            $notReportingDate = date_sub($notReportingDate, date_interval_create_from_date_string('15 day'));
            the $_SESSION['session_date'] was being changed to
            6/15/2019 to reflect the date_sub function that was done on notReportingDate.*/
        
        //$notReportingDate = strtotime($startingDateInput);
        //$notReportingDate = strtotime($endingDateInput);
        $notReportingDate = $endingDate - (15*60*60*24);
        $notReportingDateFormat = date('Y-m-d H:i:s', $notReportingDate);
        
        /************************** END GET DATE VARIABLES **************************/
        
        
        
    
    	/*Correct years are returned, but the $year variable is set to 19, so it looks like data is incorrect. Leave this as is.*/
    	$years = DB::table('school_years')
    	            ->select(DB::raw('school_year, year_id'))
    	            ->where('year_id', '>=', $minYear)->get();
    
    	$serNumDate = DB::table($yearTab . '_machine_statuses')
                       	->select(DB::raw('max(created_date) as dt, serial_number'))           
                       	->groupBy('serial_number');
                       	
        /*Used as a subquery in devices_reporting and not_reporting queries*/
    	$lastMeterRead = DB::table($yearTab . '_meters as mtr')
                       ->select(DB::raw('max(mtr.created_date) as dt, mtr.serial_number'))
                       ->where('mtr.created_date', '>', $startingDateFormat)
        				->where('mtr.created_date', '<=', $endingDateFormat)
                       ->groupBy('mtr.serial_number');
       	
    	/*$lastMeterRead = DB::table($yearTab . '_meters')
                       ->select(DB::raw('max(created_date) as mtr_dt, serial_number'))           
                       ->groupBy('serial_number');*/
    
    
    	$not_reporting = DB::table($yearTab . '_meters as m')// Carbon::now()->subDays(30)
        				->join($yearTab . '_floorplan_machines as fpm', 'fpm.proposed_serial_number', '=', 'm.serial_number')
        				->join('model_details as mach', 'mach.model_id', '=', 'fpm.proposed_model_id')
        				->join($yearTab . '_machine_statuses as ms', 'ms.serial_number', '=', 'fpm.proposed_serial_number')
        				->joinSub($serNumDate, 'serNumDate', function($join){
            												$join->on('ms.serial_number', '=', 'serNumDate.serial_number');
                    										$join->on('ms.created_date', '=', 'serNumDate.dt');})
        
        				->joinSub($lastMeterRead, 'lastMeterRead', function($join){
            												$join->on('m.serial_number', '=', 'lastMeterRead.serial_number');
                    										$join->on('m.created_date', '=', 'lastMeterRead.mtr_dt');})
       					->join('floorplans as fp', 'fp.fp_id', '=', 'fpm.floorplans_id')
        				->join('buildings as bldgs', 'bldgs.bldg_id', '=', 'fp.buildings_id')
        				->join('organizations as orgs', 'orgs.org_id', '=', 'bldgs.organizations_id')
        				->where('bldgs.organizations_id', '=', $org_id)
        				->where('fpm.proposed_model_id', '!=', 790)
        				->whereNotNull('fpm.fyer_groups_id')
        				->where('m.created_date', '<', '2019-06-15 00:00:00')->get();
    
          	    
    
    	return view('device_summaries.show_not_reporting_devices', compact('org_id', 'year', 'color', 'yearTab', 'years', 'not_reporting'))
			->with('floorplans', Floorplan::all())
			->with('bldgs', Building::orderBy('bldg_name')->get()->where('organizations_id', $org_id))
     		//->with('years', SchoolYear::orderBy('year_id')->get())        
			->with('orgs', Organization::orderBy('org_name')->get()->where('client_status', 'A'));
    }
    


	public function setSelectedSessionVariable($goToUrl, $varName, $thisVar){
		session(['varName' => 'year']);
    
    	//page_direct('gauges.show_by_org', 'thisVar');
		//print("<script>alert('hello');</script>");
	//print("<script>page_direct('" . $goToUrl. "', '" . $thisVar . "')</script");

	}

    
    

    /**
     * Show the form for editing the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function edit($id)
    {
        //
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, $id)
    {
        //
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function destroy($id)
    {
        //
    }
}