<?php

namespace App\Http\Controllers;

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 DB;
use App\Includes\GaugesCalcs;
use App\Includes\GaugesCounts;
use App\Includes\VolumeTotals;

class BuildingsCostController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        return view('buildings_cost.index')
        ->with('orgs', Organization::orderBy('org_name')->get()->where('client_status', 'A')
        ->where('organization_types_id', '!=', 7));
    }

    
    /**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function show_by_org($org_id)
    {/*explode the $org_id string into 2 strings, which are separated by __. */
        $vals_array = explode('__', $param_id);
        
        
        /*The first part of the string will be the type of id; org for org_id, year for year, timeline for timeline_date, color for color. */
        $param = $vals_array[0];
        
        
        $org_id = '';
        $year = '';
        $yearTab = '';
        $color = 'both';
        $timeline_date = '';
        
        /*If the param is org, assign the following values to these variables; org_id, year, timeline_date. */
        if($param == 'org'){
        
            /*The second part of the string will be the value of the param that will be used to assign the correct values to the criteria variables. */
            $org_id = $vals_array[1];
            
            if(null !== (session(['year']))){
    	        $year = session('year');
            }
        	else{
        	    /*Set the default values for this session variable*/
        	    //$year = date('y');
        	    $year = 19; //hardcoded for testing
        	}
        	
        	if(null !== (session(['color']))){
        	    $color = session('color');
        	}
        	else{
        	    $color = 'both';
        	}
    	
        	if(null !== (session(['timeline_date']))){
        	    $timeline_date = session('timeline_date');
        	}
        	else{
        	    /*The timeline date will default to 6-30 of the current year. When a year is selected from the
        	    school_year dropdown, this value will reset to 6-30 of the selected year.*/
        	    $timeline_date = date_create('20' . $year . '-06-30')->format('Y-m-d');
        	}
        }//end if $param == org
            
        
        elseif($param == 'year'){
            /*The second part of the string will be the value of the param that will be used to assign the correct values to the criteria variables. */
            $year = $vals_array[1];
            
            /*The timeline date will default to 6-30 of the current year. When a year is selected from the
        	    school_year dropdown, this value will reset to 6-30 of the selected year.*/
        	$timeline_date = date_create('20' . $year . '-06-30')->format('Y-m-d');
            $org_id = session('org_id');
            
        	if(null !== (session(['color']))){
        	    $color = session('color');
        	}
        	else{
        	    $color = 'both';
        	}
            
        }//end if $param == year
        
        
        elseif($param == 'timeline'){
            /*The second part of the string will be the value of the param that will be used to assign the correct values to the criteria variables. */
            $timeline_date = $vals_array[1];
            $org_id = session('org_id');
            
            /*Create a date object from the string date of the datepicker*/
        	/*The timeline date will default to 6-30 of the current year. When a year is selected from the
        	    school_year dropdown, this value will reset to 6-30 of the selected year.*/
        	$timeDate = date_create($timeline_date);
        	
        	/*Get the unix date equivalent to the $timeline_date from the datepicker. This will be
        	    used to compare the timeline_date to the fiscal year end*/
        	$timeDateUnix = strtotime($timeline_date);
        	
        	
        	/*Get the 2 digit year from the datepicker's $timeline_date*/
            $timeDateYear = $timeDate->format('y');
             
            $yearEndDateUnix = strtotime('06/30/20' . $timeDateYear);
            
            if($timeDateUnix <= $yearEndDateUnix){
                $year = $timeDateYear;
            }
            else{
                $year = $timeDateYear + 1;
            }
        
            /*Set the $yearTab to y20xx, with xx being the 2 digit year from the datepicker
                and depending on whether or not the timeline_date is earlier than the end of
                fiscal year date.*/
                $yearTab = 'y20' . $year;
                
            if(null !== (session(['color']))){
        	    $color = session('color');
        	}
        	else{
        	    $color = 'both';
        	}
            
        }
        
        
        /*************************** BEGIN SPECIFIC TO SHOW BY ORG FUNCTION ONLY **************************/
    	/*Set the default values for these session variables*/
    	
    	if(null !== (session(['year']))){
    	    $year = session('year');
    	}
    	else{
    	    //$year = date('y');
    	    $year = 19; //hardcoded for testing
    	}
    	    
    	
    	$color = 'both';
    	$yearTab = 'y20' . $year;
    	
    	/*The timeline date will default to 6-30 of the current year. When a year is selected from the
    	    school_year dropdown, this value will reset to 6-30 of the selected year.*/
    	$timeline_date = date_create('20' . $year . '-06-30')->format('Y-m-d');
    	
    	
    	/*Assign these values to session variables*/
    	session(['org_id' => $org_id]);
    	session(['year' => $year]);
    	session(['color' => $color]);
    	session(['yearTab' => $yearTab]);
    	session(['timeline_date' => $timeline_date]);
    	
    	/*************************** END SPECIFIC TO SHOW BY ORG FUNCTION ONLY **************************/
        
        
        
        /**************************** BEGIN GET DATE VARIABLES **************************/
        /*Used to have dynamic headings for the Projected and Next Years columns.*/
        $proj_year = $year + 1;
        $proj_year_next = $year + 2;
        
        $gCalc = new GaugesCalcs($timeline_date);
        
        /*UNIX timestamps*/
        $startingDate = $gCalc->getStartingDate();
        $endingDate = $gCalc->getEndingDate();
        
        /*These are string values for the starting and ending dates based on the value in the Timeline Date datepicker*/
        $startingDateFormat = $gCalc->getStartingDateFormat();
        $endingDateFormat = $gCalc->getEndingDateFormat();
        
        $notReportingDate = $gCalc->getNotReportingDate();//$endingDate - (15*60*60*24);
        $notReportingDateFormat = $gCalc->getNotReportingDateFormat(); //date('Y-m-d H:i:s', $notReportingDate);
         
        $fiscal_days = $gCalc->getFiscalDays();
        $last_sync_date = $gCalc->getLastSyncDate($yearTab, $org_id);
        $minYear = $gCalc->getMinYear(); //used to limit the number of past years in the dropdown
        
        $gCount = new GaugesCounts();
        $toner_alert_count = $gCount->getTonerLowCount($yearTab, $org_id, $startingDateFormat, $endingDateFormat);
        $service_needed_count = $gCount->getServiceNeededCount($yearTab, $org_id, $startingDateFormat, $endingDateFormat, $year);
        $contract_devices_count = $gCount->getContractDeviceCount($yearTab, $org_id);
        $non_contract_devices_count = $gCount->getNonContractDeviceCount($yearTab, $org_id);
        $devices_reporting_count = $gCount->getReportingDeviceCount($yearTab, $org_id, $startingDateFormat, $notReportingDateFormat);
        $not_reporting_count = $gCount->getNonReportingDeviceCount($yearTab, $org_id, $startingDateFormat, $notReportingDateFormat);
        
        /**************************** END GET DATE VARIABLES **************************/
        
        /*Get the list of years for the dropdown*/
    	$years = DB::table('school_years')
    	            ->select(DB::raw('school_year, year_id'))
    	            ->where('year_id', '>=', $minYear)->get();
    	            
    	            
    	            
    	/*********************************** BEGIN VOLUME_TOTALS ************************************/    
        /*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)
                           ->where('black_meter', '!=',  0)
                           ->groupBy('serial_number');
                           
        
        /************************** BEGIN ORG COMMENCEMENT AND FISCAL DAYS ******************************/
        $org_commencement_date = '';
        $org_fiscal_days = 1;
        $org_name = '';
        $display_meter_data;
        
        $org_commencement_dt = DB::table('org_year_specific_data as orgs')
                            ->join('organizations', 'organizations.org_id', 'orgs.org_id')
                            ->select(DB::raw('orgs.commencement_date as org_commencement_date, organizations.org_name, orgs.display_meter_data'))
                            ->where('orgs.school_year', '=', $year)
                            ->where('orgs.org_id', '=', $org_id)->get();
        
        foreach($org_commencement_dt as $row){
            $org_commencement_date = strtotime($row->org_commencement_date);
            $org_name = $row->org_name;
            $display_meter_data = $row->display_meter_data;
            
            /*Set the org commencment date*/  
        	if ($row->org_commencement_date == NULL || $org_commencement_date < $startingDate) {
        		$org_commencement_date = $startingDate;
        	}
        	
        	
        	/*Get the number of days between the organization commencement date and the ending fiscal date*/
    	    $org_fiscal_days = ($endingDate - $org_commencement_date) /60/60/24;
    	    
    	    if ($org_fiscal_days > 365) { 
        		$org_fiscal_days = 365; 
        	}//end if
        }//end foreach
        /************************** END ORG COMMENCEMENT AND FISCAL DAYS ******************************/
        
        $reporting = 'Y';
        $org_cm_diff = $org_fiscal_days;
        $last_meter_date = '';
        
        /*Variables to hold calculated data*/    				
        $consumed_black = 0;
        $consumed_color = 0;
        
        /*building totals*/
        $consumed_black_bldg = 0;
        $consumed_color_bldg = 0;
        
        $budgeted_black = 0;
        $budgeted_color = 0;
        
        /*building totals*/
        $budgeted_black_bldg = 0;
        $budgeted_color_bldg = 0;
        
        $projected_black = 0;
    	$projected_color = 0;
    	
        /*building totals*/
    	$projected_black_bldg = 0;
    	$projected_color_bldg = 0;
    	
    	$avg_black_per_student = 0;
    	$avg_black_per_student_bldg = 0;
    	
    	$avg_color_per_student = 0;
    	$avg_color_per_student_bldg = 0;
    	
    	
    	/*These will be the values for the Next years columns, which is really 2 years out.*/
    	$proj_blk_year_next = 0;
    	$proj_blk_year_next_bldg = 0;
    	$proj_blk_year_next_cost = 0;
    	$proj_col_year_next = 0;
    	$proj_col_year_next_bldg = 0;
    	$proj_col_year_next_cost = 0;
    	
    	
    	/*Variables to hold calculated data for the year total*/    				
        $consumed_black_year = 0;
        $consumed_color_year = 0;
        
        $budgeted_black_year = 0;
        $budgeted_color_year = 0;
        
        $projected_black_year = 0;
    	$projected_color_year = 0;
    	
    	$avg_black_per_student_year = 0;
    	$avg_color_per_student_year = 0;
    	
    	$proj_blk_year_next_tot = 0;
    	$proj_col_year_next_tot = 0;
    	
    	/*Used to calculate the avg per student (yearly projected / total students for the org) */ 
    	$total_students_per_org = 0;
    	
    	$over_under_budget_indiv = 0;
    	$over_under_budget = 0;
    	
        
        $consumed_black_estimated = 0;
        $consumed_black_estimated_cost = 0;
        $consumed_color_estimated = 0;
		$consumed_color_estimated_cost = 0;
        
        
        
        /*This will get the data for the first cell in the accordion child records*/
    	$mach_data = DB::table('model_details as mach');
        
    	
    	$volume_totals = DB::table($yearTab . '_meters as mtr')
                        ->join($yearTab . '_floorplan_machines as fpm', 'fpm.proposed_serial_number', '=', 'mtr.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 org', 'bldgs.organizations_id', '=', 'org.org_id')
        				
                        ->joinSub($serNumDateMetersMax, 'serNumDateMetersMax', function($join){
            												$join->on('mtr.serial_number', '=', 'serNumDateMetersMax.serial_number');
                    										$join->on('mtr.created_date', '=', 'serNumDateMetersMax.dt');
                        })
                        
                        ->joinSub($mach_data, 'mach_data', function($join){
                                    $join->on('fpm.proposed_model_id', '=', 'mach_data.model_id');
                        })
                        
                         
                         /*use cm_diff_orig to get the actual diff between endingDate and the device_commencement_date, then use cm_diff to further process. If
                         cm_diff_orig is < 0, cm_diff will be 0. If cm_diff_orig is 0 or greater, then cm_diff will be the actual value of cm_diff_orig*/
                                        
                        ->select(DB::raw('@device_commencement_date := (CASE WHEN fpm.commencement_date IS NULL THEN ' . $org_commencement_date . 
                                        ' WHEN UNIX_TIMESTAMP(fpm.commencement_date) < ' . $org_commencement_date . ' THEN ' . $org_commencement_date . 
                                        ' ELSE UNIX_TIMESTAMP(fpm.commencement_date) END) device_commencement_date,
                                        
                                        @c_blk := (CASE WHEN fpm.commencement_black_meter IS NULL THEN mtr.black_meter * cpc_black ELSE (mtr.black_meter - fpm.commencement_black_meter) * cpc_black END) c_blk,
                                        @c_col := (CASE WHEN fpm.commencement_color_meter IS NULL THEN mtr.color_meter * cpc_color ELSE (mtr.color_meter - fpm.commencement_color_meter) * cpc_color END) c_col,
                                        @last_diff_orig := (CASE WHEN mtr.created_date IS NULL THEN 1 ELSE (UNIX_TIMESTAMP(mtr.created_date) - @device_commencement_date) / 60/60/24 END) last_diff_orig,
                                        @last_diff := (CASE WHEN @last_diff_orig <= 0 THEN 1 ELSE @last_diff_orig END) last_diff,
                                        
                                        @cm_diff_orig := ((' . $endingDate . '- @device_commencement_date) /60/60/24) cm_diff_orig,
                                        @cm_diff := (CASE WHEN @cm_diff_orig < 0 THEN 0 ELSE @cm_diff_orig END) cm_diff,
                                        
                                        (CASE WHEN fpm.commencement_black_meter IS NULL THEN mtr.black_meter * cpc_black ELSE (mtr.black_meter - fpm.commencement_black_meter) * cpc_black END) consumed_black,
                                        (CASE WHEN fpm.commencement_color_meter IS NULL THEN mtr.color_meter * cpc_color ELSE (mtr.color_meter - fpm.commencement_color_meter) * cpc_color END) consumed_color,
                                        
                                        (CASE WHEN org.commencement_date IS NULL THEN fpm.budgeted_black
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN fpm.budgeted_black * cpc_black
                                        	ELSE (fpm.budgeted_black / ' . $fiscal_days . ') * ' . $org_fiscal_days . '  * cpc_black END) budgeted_black,
                                        	
                                        (CASE WHEN org.commencement_date IS NULL THEN fpm.budgeted_color
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN fpm.budgeted_color * cpc_color
                                        	ELSE (fpm.budgeted_color / ' . $fiscal_days . ') * ' . $org_fiscal_days . '  * cpc_color END) budgeted_color,
                                        	
                                        @projected_black := (CASE WHEN @cm_diff = 0 THEN 0 WHEN ' . $org_commencement_date . ' IS NULL THEN (@c_blk / @last_diff * 345)
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN (@c_blk / @last_diff * 345)
                                        	ELSE (@c_blk / @cm_diff * LEAST(' . $org_fiscal_days .', 345)) END) projected_black,
                                        	
                                        @projected_color := (CASE WHEN @cm_diff = 0 THEN 0 WHEN ' . $org_commencement_date . ' IS NULL THEN (@c_col / @last_diff * 345)
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN (@c_col / @last_diff * 345)
                                        	ELSE (@c_col / @cm_diff * LEAST(' . $org_fiscal_days .', 345)) END) projected_color,
                                        	
                                        (CASE WHEN @cm_diff = 0 THEN 0 WHEN ' . $org_commencement_date . ' IS NULL THEN @projected_black
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN @projected_black
                                        	ELSE (@c_blk / @cm_diff * LEAST(' . $org_fiscal_days .', 345)) END) proj_blk_year_next,
                                        	
                                        (CASE WHEN @cm_diff = 0 THEN 0 WHEN ' . $org_commencement_date . ' IS NULL THEN @projected_color
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN @projected_color
                                        	ELSE (@c_col / @cm_diff * LEAST(' . $org_fiscal_days .', 345)) END) proj_col_year_next,
                                        	
                                        bldgs.bldg_id, bldgs.bldg_name, bldgs.student_pop, mtr.created_date, fpm.closeout_date,
                                        fpm.proposed_serial_number, fpm.room_name, mach_make, model, is_color'))
                                        
                                        
                        ->whereIn('fpm.proposed_serial_number', function($query) use($year, $yearTab, $org_id){
                        									$query->select('fm.proposed_serial_number')
                                                            ->from($yearTab . '_floorplan_machines as fm')
                                                            ->join($yearTab . '_floorplans as fp', 'fp.fp_id', '=', 'fm.floorplans_id')
                                                            ->join($yearTab . '_buildings as bldgs', 'bldgs.bldg_id', '=', 'fp.buildings_id')
                                                            ->where('bldgs.organizations_id', '=', $org_id);
                        							})
                        							->orderBy('bldgs.bldg_name')
                        							->get();
                        							//echo $volume_totals;
                        							
                        							
                        							
            $first_bldg = 'Y';
            $bldg_name = '';
            $array_bldgs = array();
            
            foreach($volume_totals as $row){
                
                /* If this is the first building, assign the bldg_name to $bldg_name, then change value of $first_bldg to N */
                if($first_bldg == 'Y'){
                    $bldg_name = $row->bldg_name;
                    $first_bldg = 'N';
                    
                    /*Assign the student_pop for the first record of the first building here, then assign the
                        student_pop for the first record of each of the other buildings in the elseif block ($bldg_name != $row->bldg_name).
                        This is so we ONLY get the student_pop one time for each building. There are many device records for each building.*/
                    $total_students_per_org = $row->student_pop;
                }
                
                /* If the current building name = $bldg_name, then process fully*/
                if($bldg_name == $row->bldg_name){
                    
                    $cm_diff = $row->cm_diff;
                    
                    /*Process all rows for this building*/
                    $last_meter_date = strtotime($row->created_date);
                    
                    /*SJH - Note - the notReportingDate is just 15 days earlier than the last meter read date.*/
        	        if ( ($row->created_date == null) || ($last_meter_date < $notReportingDate) ) 
            			{ $reporting = "N"; } 
            		else 
            			{ $reporting = "Y"; }
            			
            			
            		/*Get the device closeout_date for use in setting the projected values for machines no longer in use*/
            		$closeout_date = $row->closeout_date;
            		$closeout_date_unix = strtotime($row->closeout_date);
                    
                    /*Get the consumed_black for each device. This will be used to calculate the projected_black*/
                    $consumed_black = $row->consumed_black;
                    $consumed_black_bldg += $row->consumed_black;
                    
                    /*Get the consumed_color for each device. This will be used to calculate the projected_black*/
                    $consumed_color = $row->consumed_color;
                    $consumed_color_bldg += $row->consumed_color;
                    
                    $budgeted_black = $row->budgeted_black;
                    $budgeted_black_bldg += $row->budgeted_black;
                    
                    $budgeted_color = $row->budgeted_color;
                    $budgeted_color_bldg += $row->budgeted_color;
                    
                    $projected_black = $row->projected_black;
                    $projected_black_bldg += $row->projected_black;
                    
                    $projected_color = $row->projected_color;
                    $projected_color_bldg += $row->projected_color;
                    
                    /*ORIGINAL values for the next year column, BEFORE any extra conditions are considered*/
                	$proj_blk_year_next = $row->proj_blk_year_next;
                	$proj_col_year_next = $row->proj_col_year_next;
                	
                    
                    /*Variables to hold the year totals*/    				
                    $consumed_black_year += $consumed_black;
                    $consumed_color_year += $consumed_color;
                    
                    $budgeted_black_year += $budgeted_black;
                    $budgeted_color_year += $budgeted_color;
                    
                    $projected_black_year += $projected_black;
                	$projected_color_year += $projected_color;
                	
                	$proj_blk_year_next_tot += $row->proj_blk_year_next;
    	            $proj_col_year_next_tot += $row->proj_col_year_next;
                	
                	
                	if($row->cm_diff > 0){
                	    $consumed_black_estimated =  $consumed_black / $cm_diff * $org_cm_diff;
                		//$consumed_black_estimated_cpc =  $consumed_black_cpc / $cm_diff * $org_cm_diff;
                		$consumed_color_estimated =  $consumed_color / $cm_diff * $org_cm_diff;
		                //$consumed_color_estimated_cpc =  $consumed_color_cpc / $cm_diff * $org_cm_diff;
                	}
                	
                	else {
                		$consumed_black_estimated =  $consumed_black;
                		//$consumed_black_estimated_cpc =  $consumed_black_cpc;
                		$consumed_color_estimated =  $consumed_color;
		                //$consumed_color_estimated_cpc =  $consumed_color_cpc;
                	}
                	
                	
                    /*Do further processing for projected values*/
                    if($reporting == 'N' && $org_fiscal_days > 0){
                        if($budgeted_black > 0){
                            
                            /*If the projected black for the year is < the budgeted black for the year then use the
                				budgeted black year amount instead of projected black.*/
                			if ($proj_blk_year_next < ($budgeted_black / $org_fiscal_days * $fiscal_days)) {
                				$projected_black = $budgeted_black;
                				$proj_blk_year_next = $projected_black;
                			
                				/*$projected_black_cpc = $budgeted_black_cpc;
                				$projected_black_year_cpc 	= $projected_black_cpc * 1.035;*/
                			}
                        }//end if $budgeted_black > 0
                        
                        if ( $budgeted_color > 0) {
                			/*If the projected color for the year is < the budgeted color for the year then use the
                				budgeted color year amount instead of projected color.*/
                			if ($proj_col_year_next < ($budgeted_color / $org_fiscal_days * $fiscal_days)) {
                				$projected_color = $budgeted_color;
                				$proj_col_year_next = $projected_color;
                				/*$projected_color_cpc = $budgeted_color_cpc;
                				$projected_color_year_cpc 	= $projected_color_cpc * 1.035;*/
                			}
                		}//end if $budgeted_color > 0
                    }
                    
                    /*If the closeout date for a device has passed then this device is no longer in use.  
                    	Set the projected amounts to match the consumed amounts because the device will not add to
                    	print total.  Set the projected next years volume to zero.*/
                    if($closeout_date != NULL && $closeout_date_unix < $endingDate){
                        $projected_black = $consumed_black_estimated;
                        $projected_color = $consumed_color_estimated;
                        
                        $proj_blk_year_next = 0;
                        $proj_col_year_next = 0;
                    }
                    
                    if($proj_blk_year_next < 0){
                        $proj_blk_year_next = 0;
                    }
                    
                    if($proj_col_year_next < 0){
                        $proj_col_year_next = 0;
                    }
                    
                    /*Assign the values here after all of the conditional processing has been done.*/
                    $proj_blk_year_next_bldg += $proj_blk_year_next;
                	$proj_col_year_next_bldg += $proj_col_year_next;
                	
                	$over_under_budget_indiv = ($projected_black + $projected_color) - ($budgeted_black + $budgeted_color);
                	$over_under_budget += $over_under_budget_indiv;
                    
                    
                    
                    if($row->student_pop > 0){
                        $avg_black_per_student = $projected_black / $row->student_pop;
                        $avg_black_per_student_bldg += $avg_black_per_student;
                        
                        $avg_color_per_student = $projected_color / $row->student_pop;
                        $avg_color_per_student_bldg += $avg_color_per_student;
                    }
                    
                    else{
                        $avg_black_per_student = 0;
                        $avg_color_per_student = 0;
                    }
                    
                    
                    
                    $row_array = array("consumed_black"=>$consumed_black_bldg, "budgeted_black"=>$budgeted_black_bldg,
                                        "projected_black"=>$projected_black_bldg, "avg_black_per_student"=>$avg_black_per_student_bldg, "next_year_black"=>$proj_blk_year_next_bldg,
                                        "consumed_color"=>$consumed_color_bldg, "budgeted_color"=>$budgeted_color_bldg, "projected_color"=>$projected_color_bldg,
                                        "avg_color_per_student"=>$avg_color_per_student_bldg, "next_year_color"=>$proj_col_year_next_bldg, "over_under_budget"=>$over_under_budget);
                                        
                    $array_bldgs[$bldg_name] = $row_array;
                }//end if($bldg_name == $row->bldg_name)
                
                /* If the current building name is different from $bldg_name, then this is a new building, so assign the new bldg_name to $bldg_name, then process fully */ 
                elseif($bldg_name != $row->bldg_name){
                    
                    $cm_diff = $row->cm_diff;
                    
                    /*Assign the current values to the array before starting with a new building*/
                    /*$row_array = array("consumed_black"=>$consumed_black_bldg, "budgeted_black"=>$budgeted_black_bldg,
                                        "projected_black"=>$projected_black_bldg, "avg_black_per_student"=>$avg_black_per_student_bldg,
                                        "next_year_black"=>$proj_blk_year_next,
                                        "consumed_color"=>$consumed_color_bldg, "budgeted_color"=>$budgeted_color_bldg, "projected_color"=>$projected_color_bldg,
                                        "avg_color_per_student"=>$avg_color_per_student_bldg, "next_year_color"=>$proj_col_year_next_bldg);
                                        
                    $array_bldgs[$bldg_name] = $row_array;*/
                   
                    
                    /*Assign the new building name to $bldg_name*/
                    $bldg_name = $row->bldg_name;
                    
                     /*Process all rows for this building*/
                    $last_meter_date = strtotime($row->created_date);
                    
                    /*SJH - Note - the notReportingDate is just 15 days earlier than the last meter read date.*/
        	        if ( ($row->created_date == null) || ($last_meter_date < $notReportingDate) ) 
            			{ $reporting = "N"; } 
            		else 
            			{ $reporting = "Y"; }
                    
                    /*Get the device closeout_date for use in setting the projected values for machines no longer in use*/
            		$closeout_date = $row->closeout_date;
            		$closeout_date_unix = strtotime($row->closeout_date);
                    
                    /*Get the consumed_black for each device. This will be used to calculate the projected_black*/
                    $consumed_black = $row->consumed_black;
                    $consumed_black_bldg = $row->consumed_black;
                    
                    /*Get the consumed_color for each device. This will be used to calculate the projected_black*/
                    $consumed_color = $row->consumed_color;
                    $consumed_color_bldg = $row->consumed_color;
                    
                    $budgeted_black = $row->budgeted_black;
                    $budgeted_black_bldg = $row->budgeted_black;
                    
                    $budgeted_color = $row->budgeted_color;
                    $budgeted_color_bldg = $row->budgeted_color;
                    
                    $projected_black = $row->projected_black;
                    $projected_black_bldg = $row->projected_black;
                    
                    $projected_color = $row->projected_color;
                    $projected_color_bldg = $row->projected_color;
                    
                	/*values for the next year column*/
                	$proj_blk_year_next = $row->proj_blk_year_next;
                	//$proj_blk_year_next_bldg = $row->proj_blk_year_next;
                	
                	$proj_col_year_next = $row->proj_col_year_next;
                	//$proj_col_year_next_bldg = $row->proj_col_year_next;
                    
                    /*Variables to hold the year totals*/    				
                    $consumed_black_year += $consumed_black;
                    $consumed_color_year += $consumed_color;
                    
                    $budgeted_black_year += $budgeted_black;
                    $budgeted_color_year += $budgeted_color;
                    
                    $projected_black_year += $projected_black;
                	$projected_color_year += $projected_color;
                	
                	$proj_blk_year_next_tot += $row->proj_blk_year_next;
    	            $proj_col_year_next_tot += $row->proj_col_year_next;
                	
                	
                	if($row->cm_diff > 0){
                	    $consumed_black_estimated =  $consumed_black / $cm_diff * $org_cm_diff;
                		//$consumed_black_estimated_cpc =  $consumed_black_cpc / $cm_diff * $org_cm_diff;
                		$consumed_color_estimated =  $consumed_color / $cm_diff * $org_cm_diff;
		                //$consumed_color_estimated_cpc =  $consumed_color_cpc / $cm_diff * $org_cm_diff;
                	}
                	
                	else {
                		$consumed_black_estimated =  $consumed_black;
                		//$consumed_black_estimated_cpc =  $consumed_black_cpc;
                		$consumed_color_estimated =  $consumed_color;
		                //$consumed_color_estimated_cpc =  $consumed_color_cpc;
                	}
                	
                	
                	/*Do further processing for projected values*/
                    if($reporting == 'N' && $org_fiscal_days > 0){
                        if($budgeted_black > 0){
                            /*If the projected black for the year is < the budgeted black for the year then use the
                				budgeted black year amount instead of projected black.*/
                			if ($proj_blk_year_next < ($budgeted_black / $org_fiscal_days * $fiscal_days)) {
                				$projected_black = $budgeted_black;
                				$proj_blk_year_next = $projected_black;
                				/*$projected_black_cpc = $budgeted_black_cpc;
                				$projected_black_year_cpc 	= $projected_black_cpc * 1.035;*/
                			}
                        }//end if $budgeted_black > 0
                        
                        if ( $budgeted_color > 0) {
                			/*If the projected color for the year is < the budgeted color for the year then use the
                				budgeted color year amount instead of projected color.*/
                			if ($proj_col_year_next < ($budgeted_color / $org_fiscal_days * $fiscal_days)) {
                				$projected_color = $budgeted_color;
                				$proj_col_year_next = $projected_color;
                				/*$projected_color_cpc = $budgeted_color_cpc;
                				$projected_color_year_cpc 	= $projected_color_cpc * 1.035;*/
                			}
                		}//end if $budgeted_color > 0
                    }
                    
                    /*If the closeout date for a device has passed then this device is no longer in use.  
                    	Set the projected amounts to match the consumed amounts because the device will not add to
                    	print total.  Set the projected next years volume to zero.*/
                    if($closeout_date != NULL && $closeout_date_unix < $endingDate){
                        $projected_black = $consumed_black_estimated;
                        $projected_color = $consumed_color_estimated;
                        
                        $proj_blk_year_next = 0;
                        $proj_col_year_next = 0;
                    }
                    
                    
                    if($proj_blk_year_next < 0){
                        $proj_blk_year_next = 0;
                    }
                    
                    if($proj_col_year_next < 0){
                        $proj_col_year_next = 0;
                    }
                    
                    /*Assign the values here after all of the conditional processing has been done.*/
                    $proj_blk_year_next_bldg = $proj_blk_year_next;
                	$proj_col_year_next_bldg = $proj_col_year_next;
                	
                	$over_under_budget_indiv = ($projected_black + $projected_color) - ($budgeted_black + $budgeted_color);
                	$over_under_budget = $over_under_budget_indiv;
                	
                    
                    if($row->student_pop > 0){
                        $avg_black_per_student = $projected_black / $row->student_pop;
                        $avg_black_per_student_bldg = $avg_black_per_student;
                        
                        $avg_color_per_student = $projected_color / $row->student_pop;
                        $avg_color_per_student_bldg = $avg_color_per_student;
                        
                        
                        /*Assign the student_pop for the first record of each building excluding the first building. The student_pop for
                        the first building was assigned in the if block ($first_bldg == 'Y'). This is so we ONLY get the student_pop one
                        time for each building. There are many device records for each building.*/
                        $total_students_per_org += $row->student_pop;
                    }
                    else{
                        $avg_black_per_student = 0;
                        $avg_color_per_student = 0;
                    }
                    
                     
                    /*Now assign the current row values to the array under the new bldg_name*/
                    $row_array = array("consumed_black"=>$consumed_black_bldg, "budgeted_black"=>$budgeted_black_bldg,
                                        "projected_black"=>$projected_black_bldg, "avg_black_per_student"=>$avg_black_per_student_bldg,
                                        "next_year_black"=>$proj_blk_year_next_bldg, "consumed_color"=>$consumed_color_bldg,
                                        "budgeted_color"=>$budgeted_color_bldg, "projected_color"=>$projected_color_bldg,
                                        "avg_color_per_student"=>$avg_color_per_student_bldg, "next_year_color"=>$proj_col_year_next_bldg,
                                        "over_under_budget"=>$over_under_budget);
                                        
                    $array_bldgs[$bldg_name]= $row_array;
                }//end if($bldg_name != $row->bldg_name)
                
                
        }//end foreach
        
        
        if($total_students_per_org > 0){
            $avg_black_per_student_year = $projected_black_year / $total_students_per_org;
        	$avg_color_per_student_year = $projected_color_year / $total_students_per_org;
        }
        
        $year_totals_array = array("consumed_black_year"=>$consumed_black_year, "budgeted_black_year"=>$budgeted_black_year,
                                    "projected_black_year"=>$projected_black_year, "avg_black_per_student_year"=>$avg_black_per_student_year,
                                    "next_years_black_year"=>$proj_blk_year_next_tot, "consumed_color_year"=>$consumed_color_year,
                                    "budgeted_color_year"=>$budgeted_color_year, "projected_color_year"=>$projected_color_year,
                                    "avg_color_per_student_year"=>$avg_color_per_student_year, "next_years_color_year"=>$proj_col_year_next_tot);
        
    	
    	return view('buildings_cost.index', compact('org_id', 'org_name', 'year', 'years', 'timeline_date', 'display_meter_data', 'toner_alert_count', 'service_needed_count',
                                                           'contract_devices_count', 'non_contract_devices_count', 'devices_reporting_count',
                                                           'not_reporting_count', 'last_sync_date', 'volume_totals', 'array_bldgs',
                                                           'year_totals_array', 'proj_year', 'proj_year_next'))
    	
			->with('orgs', Organization::orderBy('org_name')->get()
			->where('client_status', '=', 'A')
			->where('organization_types_id', '!=', 7));
    }
    
    
    public function show_by_year($year){
        /*************************** BEGIN SPECIFIC TO SHOW BY ORG FUNCTION ONLY **************************/
    	/*Set the default values for these session variables*/
    	
    	$yearTab = 'y20' . $year;
    	$org_id = session('org_id');
    	
    	/*The timeline date will default to 6-30 of the current year. When a year is selected from the
    	    school_year dropdown, this value will reset to 6-30 of the selected year.*/
    	$timeline_date = date_create('20' . $year . '-06-30')->format('Y-m-d');
    	
    	
    	/*Assign these values to session variables*/
    	session(['year' => $year]);
    	session(['yearTab' => $yearTab]);
    	session(['timeline_date' => $timeline_date]);
    	
    	/*************************** END SPECIFIC TO SHOW BY ORG FUNCTION ONLY **************************/
        
        
        
        /**************************** BEGIN GET DATE VARIABLES **************************/
        /*Used to have dynamic headings for the Projected and Next Years columns.*/
        $proj_year = $year + 1;
        $proj_year_next = $year + 2;
        
        $gCalc = new GaugesCalcs($timeline_date);
        
        /*UNIX timestamps*/
        $startingDate = $gCalc->getStartingDate();
        $endingDate = $gCalc->getEndingDate();
        
        /*These are string values for the starting and ending dates based on the value in the Timeline Date datepicker*/
        $startingDateFormat = $gCalc->getStartingDateFormat();
        $endingDateFormat = $gCalc->getEndingDateFormat();
        
        $notReportingDate = $gCalc->getNotReportingDate();//$endingDate - (15*60*60*24);
        $notReportingDateFormat = $gCalc->getNotReportingDateFormat(); //date('Y-m-d H:i:s', $notReportingDate);
         
        $fiscal_days = $gCalc->getFiscalDays();
        $last_sync_date = $gCalc->getLastSyncDate($yearTab, $org_id);
        $minYear = $gCalc->getMinYear(); //used to limit the number of past years in the dropdown
        
        $gCount = new GaugesCounts();
        $toner_alert_count = $gCount->getTonerLowCount($yearTab, $org_id, $startingDateFormat, $endingDateFormat);
        $service_needed_count = $gCount->getServiceNeededCount($yearTab, $org_id, $startingDateFormat, $endingDateFormat, $year);
        $contract_devices_count = $gCount->getContractDeviceCount($yearTab, $org_id);
        $non_contract_devices_count = $gCount->getNonContractDeviceCount($yearTab, $org_id);
        $devices_reporting_count = $gCount->getReportingDeviceCount($yearTab, $org_id, $startingDateFormat, $notReportingDateFormat);
        $not_reporting_count = $gCount->getNonReportingDeviceCount($yearTab, $org_id, $startingDateFormat, $notReportingDateFormat);
        
        /**************************** END GET DATE VARIABLES **************************/
        
        /*Get the list of years for the dropdown*/
    	$years = DB::table('school_years')
    	            ->select(DB::raw('school_year, year_id'))
    	            ->where('year_id', '>=', $minYear)->get();
    	            
    	            
    	            
    	/*********************************** BEGIN VOLUME_TOTALS ************************************/    
        /*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)
                           ->where('black_meter', '!=',  0)
                           ->groupBy('serial_number');
                           
        
        /************************** BEGIN ORG COMMENCEMENT AND FISCAL DAYS ******************************/
        $org_commencement_date = '';
        $org_fiscal_days = 1;
        $org_name = '';
        $display_meter_data;
        
        $org_commencement_dt = DB::table('org_year_specific_data as orgs')
                            ->join('organizations', 'organizations.org_id', 'orgs.org_id')
                            ->select(DB::raw('orgs.commencement_date as org_commencement_date, organizations.org_name, orgs.display_meter_data'))
                            ->where('orgs.school_year', '=', $year)
                            ->where('orgs.org_id', '=', $org_id)->get();
                            
        
        foreach($org_commencement_dt as $row){
            $org_commencement_date = strtotime($row->org_commencement_date);
            $org_name = $row->org_name;
            $display_meter_data = $row->display_meter_data;
            
            /*Set the org commencment date*/  
        	if ($row->org_commencement_date == NULL || $org_commencement_date < $startingDate) {
        		$org_commencement_date = $startingDate;
        	}
        	
        
        	/*Get the number of days between the organization commencement date and the ending fiscal date*/
    	    $org_fiscal_days = ($endingDate - $org_commencement_date) /60/60/24;
    	    
    	    if ($org_fiscal_days > 365) { 
        		$org_fiscal_days = 365; 
        	}//end if
        }//end foreach
        /************************** END ORG COMMENCEMENT AND FISCAL DAYS ******************************/
        
        $reporting = 'Y';
        $org_cm_diff = $org_fiscal_days;
        $last_meter_date = '';
        
        /*Variables to hold calculated data*/    				
        $consumed_black = 0;
        $consumed_color = 0;
        
        /*building totals*/
        $consumed_black_bldg = 0;
        $consumed_color_bldg = 0;
        
        $budgeted_black = 0;
        $budgeted_color = 0;
        
        /*building totals*/
        $budgeted_black_bldg = 0;
        $budgeted_color_bldg = 0;
        
        $projected_black = 0;
    	$projected_color = 0;
    	
        /*building totals*/
    	$projected_black_bldg = 0;
    	$projected_color_bldg = 0;
    	
    	$avg_black_per_student = 0;
    	$avg_black_per_student_bldg = 0;
    	
    	
    	$avg_color_per_student = 0;
    	$avg_color_per_student_bldg = 0;
    	
    	
    	/*These will be the values for the Next years columns, which is really 2 years out.*/
    	$proj_blk_year_next = 0;
    	$proj_blk_year_next_bldg = 0;
    	$proj_blk_year_next_cost = 0;
    	$proj_col_year_next = 0;
    	$proj_col_year_next_bldg = 0;
    	$proj_col_year_next_cost = 0;
    	
    	
    	/*Variables to hold calculated data for the year total*/    				
        $consumed_black_year = 0;
        $consumed_color_year = 0;
        
        $budgeted_black_year = 0;
        $budgeted_color_year = 0;
        
        $projected_black_year = 0;
    	$projected_color_year = 0;
    	
    	$avg_black_per_student_year = 0;
    	$avg_color_per_student_year = 0;
    	
    	$proj_blk_year_next_tot = 0;
    	$proj_col_year_next_tot = 0;
    	
    	/*Used to calculate the avg per student (yearly projected / total students for the org) */ 
    	$total_students_per_org = 0;
    	
    	$over_under_budget_indiv = 0;
    	$over_under_budget = 0;
    	
        
        $consumed_black_estimated = 0;
        $consumed_black_estimated_cost = 0;
        $consumed_color_estimated = 0;
		$consumed_color_estimated_cost = 0;
        
        /*This will get the data for the first cell in the accordion child records*/
        $mach_data = DB::table('model_details as mach');
        

    	$volume_totals = DB::table($yearTab . '_meters as mtr')
                        ->join($yearTab . '_floorplan_machines as fpm', 'fpm.proposed_serial_number', '=', 'mtr.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 org', 'bldgs.organizations_id', '=', 'org.org_id')
        				
                        ->joinSub($serNumDateMetersMax, 'serNumDateMetersMax', function($join){
            												$join->on('mtr.serial_number', '=', 'serNumDateMetersMax.serial_number');
                    										$join->on('mtr.created_date', '=', 'serNumDateMetersMax.dt');
                        })
                        
                        ->joinSub($mach_data, 'mach_data', function($join){
                                    $join->on('fpm.proposed_model_id', '=', 'mach_data.model_id');
                        })
                        
                         
                         /*use cm_diff_orig to get the actual diff between endingDate and the device_commencement_date, then use cm_diff to further process. If
                         cm_diff_orig is < 0, cm_diff will be 0. If cm_diff_orig is 0 or greater, then cm_diff will be the actual value of cm_diff_orig*/
                                        
                        ->select(DB::raw('@device_commencement_date := (CASE WHEN fpm.commencement_date IS NULL THEN ' . $org_commencement_date . 
                                        ' WHEN UNIX_TIMESTAMP(fpm.commencement_date) < ' . $org_commencement_date . ' THEN ' . $org_commencement_date . 
                                        ' ELSE UNIX_TIMESTAMP(fpm.commencement_date) END) device_commencement_date,
                                        
                                        @c_blk := (CASE WHEN fpm.commencement_black_meter IS NULL THEN mtr.black_meter * cpc_black ELSE (mtr.black_meter - fpm.commencement_black_meter) * cpc_black END) c_blk,
                                        @c_col := (CASE WHEN fpm.commencement_color_meter IS NULL THEN mtr.color_meter * cpc_color ELSE (mtr.color_meter - fpm.commencement_color_meter) * cpc_color END) c_col,
                                        @last_diff_orig := (CASE WHEN mtr.created_date IS NULL THEN 1 ELSE (UNIX_TIMESTAMP(mtr.created_date) - @device_commencement_date) / 60/60/24 END) last_diff_orig,
                                        @last_diff := (CASE WHEN @last_diff_orig <= 0 THEN 1 ELSE @last_diff_orig END) last_diff,
                                        
                                        @cm_diff_orig := ((' . $endingDate . '- @device_commencement_date) /60/60/24) cm_diff_orig,
                                        @cm_diff := (CASE WHEN @cm_diff_orig < 0 THEN 0 ELSE @cm_diff_orig END) cm_diff,
                                        
                                        (CASE WHEN fpm.commencement_black_meter IS NULL THEN mtr.black_meter * cpc_black ELSE (mtr.black_meter - fpm.commencement_black_meter) * cpc_black END) consumed_black,
                                        (CASE WHEN fpm.commencement_color_meter IS NULL THEN mtr.color_meter * cpc_color ELSE (mtr.color_meter - fpm.commencement_color_meter) * cpc_color END) consumed_color,
                                        
                                        (CASE WHEN org.commencement_date IS NULL THEN fpm.budgeted_black
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN fpm.budgeted_black * cpc_black
                                        	ELSE (fpm.budgeted_black / ' . $fiscal_days . ') * ' . $org_fiscal_days . '  * cpc_black END) budgeted_black,
                                        	
                                        (CASE WHEN org.commencement_date IS NULL THEN fpm.budgeted_color
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN fpm.budgeted_color * cpc_color
                                        	ELSE (fpm.budgeted_color / ' . $fiscal_days . ') * ' . $org_fiscal_days . '  * cpc_color END) budgeted_color,
                                        	
                                        @projected_black := (CASE WHEN @cm_diff = 0 THEN 0 WHEN ' . $org_commencement_date . ' IS NULL THEN (@c_blk / @last_diff * 345)
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN (@c_blk / @last_diff * 345)
                                        	ELSE (@c_blk / @cm_diff * LEAST(' . $org_fiscal_days .', 345)) END) projected_black,
                                        	
                                        @projected_color := (CASE WHEN @cm_diff = 0 THEN 0 WHEN ' . $org_commencement_date . ' IS NULL THEN (@c_col / @last_diff * 345)
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN (@c_col / @last_diff * 345)
                                        	ELSE (@c_col / @cm_diff * LEAST(' . $org_fiscal_days .', 345)) END) projected_color,
                                        	
                                        (CASE WHEN @cm_diff = 0 THEN 0 WHEN ' . $org_commencement_date . ' IS NULL THEN @projected_black
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN @projected_black
                                        	ELSE (@c_blk / @cm_diff * LEAST(' . $org_fiscal_days .', 345)) END) proj_blk_year_next,
                                        	
                                        (CASE WHEN @cm_diff = 0 THEN 0 WHEN ' . $org_commencement_date . ' IS NULL THEN @projected_color
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN @projected_color
                                        	ELSE (@c_col / @cm_diff * LEAST(' . $org_fiscal_days .', 345)) END) proj_col_year_next,
                                        	
                                        bldgs.bldg_id, bldgs.bldg_name, bldgs.student_pop, mtr.created_date, fpm.closeout_date,
                                        fpm.proposed_serial_number, fpm.room_name, mach_make, model, is_color'))
                                        
                                        
                        ->whereIn('fpm.proposed_serial_number', function($query) use($year, $yearTab, $org_id){
                        									$query->select('fm.proposed_serial_number')
                                                            ->from($yearTab . '_floorplan_machines as fm')
                                                            ->join($yearTab . '_floorplans as fp', 'fp.fp_id', '=', 'fm.floorplans_id')
                                                            ->join($yearTab . '_buildings as bldgs', 'bldgs.bldg_id', '=', 'fp.buildings_id')
                                                            ->where('bldgs.organizations_id', '=', $org_id);
                        							})
                        							->orderBy('bldgs.bldg_name')
                        							->get();
                        							
                        							
            $first_bldg = 'Y';
            $bldg_name = '';
            $array_bldgs = array();
            
            foreach($volume_totals as $row){
                
                /* If this is the first building, assign the bldg_name to $bldg_name, then change value of $first_bldg to N */
                if($first_bldg == 'Y'){
                    $bldg_name = $row->bldg_name;
                    $first_bldg = 'N';
                    
                    /*Assign the student_pop for the first record of the first building here, then assign the
                        student_pop for the first record of each of the other buildings in the elseif block ($bldg_name != $row->bldg_name).
                        This is so we ONLY get the student_pop one time for each building. There are many device records for each building.*/
                    $total_students_per_org = $row->student_pop;
                }
                
                /* If the current building name = $bldg_name, then process fully*/
                if($bldg_name == $row->bldg_name){
                    
                    $cm_diff = $row->cm_diff;
                    
                    /*Process all rows for this building*/
                    $last_meter_date = strtotime($row->created_date);
                    
                    /*SJH - Note - the notReportingDate is just 15 days earlier than the last meter read date.*/
        	        if ( ($row->created_date == null) || ($last_meter_date < $notReportingDate) ) 
            			{ $reporting = "N"; } 
            		else 
            			{ $reporting = "Y"; }
            			
            			
            		/*Get the device closeout_date for use in setting the projected values for machines no longer in use*/
            		$closeout_date = $row->closeout_date;
            		$closeout_date_unix = strtotime($row->closeout_date);
                    
                    /*Get the consumed_black for each device. This will be used to calculate the projected_black*/
                    $consumed_black = $row->consumed_black;
                    $consumed_black_bldg += $row->consumed_black;
                    
                    /*Get the consumed_color for each device. This will be used to calculate the projected_black*/
                    $consumed_color = $row->consumed_color;
                    $consumed_color_bldg += $row->consumed_color;
                    
                    $budgeted_black = $row->budgeted_black;
                    $budgeted_black_bldg += $row->budgeted_black;
                    
                    $budgeted_color = $row->budgeted_color;
                    $budgeted_color_bldg += $row->budgeted_color;
                    
                    $projected_black = $row->projected_black;
                    $projected_black_bldg += $row->projected_black;
                    
                    $projected_color = $row->projected_color;
                    $projected_color_bldg += $row->projected_color;
                    
                    /*ORIGINAL values for the next year column, BEFORE any extra conditions are considered*/
                	$proj_blk_year_next = $row->proj_blk_year_next;
                	$proj_col_year_next = $row->proj_col_year_next;
                	
                    
                    /*Variables to hold the year totals*/    				
                    $consumed_black_year += $consumed_black;
                    $consumed_color_year += $consumed_color;
                    
                    $budgeted_black_year += $budgeted_black;
                    $budgeted_color_year += $budgeted_color;
                    
                    $projected_black_year += $projected_black;
                	$projected_color_year += $projected_color;
                	
                	$proj_blk_year_next_tot += $row->proj_blk_year_next;
    	            $proj_col_year_next_tot += $row->proj_col_year_next;
                	
                	
                	if($row->cm_diff > 0){
                	    $consumed_black_estimated =  $consumed_black / $cm_diff * $org_cm_diff;
                		//$consumed_black_estimated_cpc =  $consumed_black_cpc / $cm_diff * $org_cm_diff;
                		$consumed_color_estimated =  $consumed_color / $cm_diff * $org_cm_diff;
		                //$consumed_color_estimated_cpc =  $consumed_color_cpc / $cm_diff * $org_cm_diff;
                	}
                	
                	else {
                		$consumed_black_estimated =  $consumed_black;
                		//$consumed_black_estimated_cpc =  $consumed_black_cpc;
                		$consumed_color_estimated =  $consumed_color;
		                //$consumed_color_estimated_cpc =  $consumed_color_cpc;
                	}
                	
                	
                    /*Do further processing for projected values*/
                    if($reporting == 'N' && $org_fiscal_days > 0){
                        if($budgeted_black > 0){
                            
                            /*If the projected black for the year is < the budgeted black for the year then use the
                				budgeted black year amount instead of projected black.*/
                			if ($proj_blk_year_next < ($budgeted_black / $org_fiscal_days * $fiscal_days)) {
                				$projected_black = $budgeted_black;
                				$proj_blk_year_next = $projected_black;
                			
                				/*$projected_black_cpc = $budgeted_black_cpc;
                				$projected_black_year_cpc 	= $projected_black_cpc * 1.035;*/
                			}
                        }//end if $budgeted_black > 0
                        
                        if ( $budgeted_color > 0) {
                			/*If the projected color for the year is < the budgeted color for the year then use the
                				budgeted color year amount instead of projected color.*/
                			if ($proj_col_year_next < ($budgeted_color / $org_fiscal_days * $fiscal_days)) {
                				$projected_color = $budgeted_color;
                				$proj_col_year_next = $projected_color;
                				/*$projected_color_cpc = $budgeted_color_cpc;
                				$projected_color_year_cpc 	= $projected_color_cpc * 1.035;*/
                			}
                		}//end if $budgeted_color > 0
                    }
                    
                    /*If the closeout date for a device has passed then this device is no longer in use.  
                    	Set the projected amounts to match the consumed amounts because the device will not add to
                    	print total.  Set the projected next years volume to zero.*/
                    if($closeout_date != NULL && $closeout_date_unix < $endingDate){
                        $projected_black = $consumed_black_estimated;
                        $projected_color = $consumed_color_estimated;
                        
                        $proj_blk_year_next = 0;
                        $proj_col_year_next = 0;
                    }
                    
                    if($proj_blk_year_next < 0){
                        $proj_blk_year_next = 0;
                    }
                    
                    if($proj_col_year_next < 0){
                        $proj_col_year_next = 0;
                    }
                    
                    /*Assign the values here after all of the conditional processing has been done.*/
                    $proj_blk_year_next_bldg += $proj_blk_year_next;
                	$proj_col_year_next_bldg += $proj_col_year_next;
                	
                	$over_under_budget_indiv = ($projected_black + $projected_color) - ($budgeted_black + $budgeted_color);
                	$over_under_budget += $over_under_budget_indiv;
                    
                    
                    
                    if($row->student_pop > 0){
                        $avg_black_per_student = $projected_black / $row->student_pop;
                        $avg_black_per_student_bldg += $avg_black_per_student;
                        
                        $avg_color_per_student = $projected_color / $row->student_pop;
                        $avg_color_per_student_bldg += $avg_color_per_student;
                    }
                    
                    else{
                        $avg_black_per_student = 0;
                        $avg_color_per_student = 0;
                    }
                    
                    
                    $row_array = array("consumed_black"=>$consumed_black_bldg, "budgeted_black"=>$budgeted_black_bldg,
                                        "projected_black"=>$projected_black_bldg, "avg_black_per_student"=>$avg_black_per_student_bldg, "next_year_black"=>$proj_blk_year_next_bldg,
                                        "consumed_color"=>$consumed_color_bldg, "budgeted_color"=>$budgeted_color_bldg, "projected_color"=>$projected_color_bldg,
                                        "avg_color_per_student"=>$avg_color_per_student_bldg, "next_year_color"=>$proj_col_year_next_bldg,
                                        "over_under_budget"=>$over_under_budget);
                                        
                    $array_bldgs[$bldg_name] = $row_array;
                }//end if($bldg_name == $row->bldg_name)
                
                /* If the current building name is different from $bldg_name, then this is a new building, so assign the new bldg_name to $bldg_name, then process fully */ 
                elseif($bldg_name != $row->bldg_name){
                    
                    $cm_diff = $row->cm_diff;
                    
                    /*Assign the current values to the array before starting with a new building
                    $row_array = array("consumed_black"=>$consumed_black_bldg, "budgeted_black"=>$budgeted_black_bldg,
                                        "projected_black"=>$projected_black_bldg, "avg_black_per_student"=>$avg_black_per_student_bldg,
                                        "next_year_black"=>$proj_blk_year_next,
                                        "consumed_color"=>$consumed_color_bldg, "budgeted_color"=>$budgeted_color_bldg, "projected_color"=>$projected_color_bldg,
                                        "avg_color_per_student"=>$avg_color_per_student_bldg, "next_year_color"=>$proj_col_year_next_bldg,
                                        "over_under_budget"=>$over_under_budget);*/
                                        
                    $array_bldgs[$bldg_name] = $row_array;
                   
                    
                    /*Assign the new building name to $bldg_name*/
                    $bldg_name = $row->bldg_name;
                    
                     /*Process all rows for this building*/
                    $last_meter_date = strtotime($row->created_date);
                    
                    /*SJH - Note - the notReportingDate is just 15 days earlier than the last meter read date.*/
        	        if ( ($row->created_date == null) || ($last_meter_date < $notReportingDate) ) 
            			{ $reporting = "N"; } 
            		else 
            			{ $reporting = "Y"; }
                    
                    /*Get the device closeout_date for use in setting the projected values for machines no longer in use*/
            		$closeout_date = $row->closeout_date;
            		$closeout_date_unix = strtotime($row->closeout_date);
                    
                    /*Get the consumed_black for each device. This will be used to calculate the projected_black*/
                    $consumed_black = $row->consumed_black;
                    $consumed_black_bldg = $row->consumed_black;
                    
                    /*Get the consumed_color for each device. This will be used to calculate the projected_black*/
                    $consumed_color = $row->consumed_color;
                    $consumed_color_bldg = $row->consumed_color;
                    
                    $budgeted_black = $row->budgeted_black;
                    $budgeted_black_bldg = $row->budgeted_black;
                    
                    $budgeted_color = $row->budgeted_color;
                    $budgeted_color_bldg = $row->budgeted_color;
                    
                    $projected_black = $row->projected_black;
                    $projected_black_bldg = $row->projected_black;
                    
                    $projected_color = $row->projected_color;
                    $projected_color_bldg = $row->projected_color;
                    
                	/*values for the next year column*/
                	$proj_blk_year_next = $row->proj_blk_year_next;
                	//$proj_blk_year_next_bldg = $row->proj_blk_year_next;
                	
                	$proj_col_year_next = $row->proj_col_year_next;
                	//$proj_col_year_next_bldg = $row->proj_col_year_next;
                    
                    /*Variables to hold the year totals*/    				
                    $consumed_black_year += $consumed_black;
                    $consumed_color_year += $consumed_color;
                    
                    $budgeted_black_year += $budgeted_black;
                    $budgeted_color_year += $budgeted_color;
                    
                    $projected_black_year += $projected_black;
                	$projected_color_year += $projected_color;
                	
                	$proj_blk_year_next_tot += $row->proj_blk_year_next;
    	            $proj_col_year_next_tot += $row->proj_col_year_next;
                	
                	
                	if($row->cm_diff > 0){
                	    $consumed_black_estimated =  $consumed_black / $cm_diff * $org_cm_diff;
                		//$consumed_black_estimated_cpc =  $consumed_black_cpc / $cm_diff * $org_cm_diff;
                		$consumed_color_estimated =  $consumed_color / $cm_diff * $org_cm_diff;
		                //$consumed_color_estimated_cpc =  $consumed_color_cpc / $cm_diff * $org_cm_diff;
                	}
                	
                	else {
                		$consumed_black_estimated =  $consumed_black;
                		//$consumed_black_estimated_cpc =  $consumed_black_cpc;
                		$consumed_color_estimated =  $consumed_color;
		                //$consumed_color_estimated_cpc =  $consumed_color_cpc;
                	}
                	
                	
                	/*Do further processing for projected values*/
                    if($reporting == 'N' && $org_fiscal_days > 0){
                        if($budgeted_black > 0){
                            /*If the projected black for the year is < the budgeted black for the year then use the
                				budgeted black year amount instead of projected black.*/
                			if ($proj_blk_year_next < ($budgeted_black / $org_fiscal_days * $fiscal_days)) {
                				$projected_black = $budgeted_black;
                				$proj_blk_year_next = $projected_black;
                				/*$projected_black_cpc = $budgeted_black_cpc;
                				$projected_black_year_cpc 	= $projected_black_cpc * 1.035;*/
                			}
                        }//end if $budgeted_black > 0
                        
                        if ( $budgeted_color > 0) {
                			/*If the projected color for the year is < the budgeted color for the year then use the
                				budgeted color year amount instead of projected color.*/
                			if ($proj_col_year_next < ($budgeted_color / $org_fiscal_days * $fiscal_days)) {
                				$projected_color = $budgeted_color;
                				$proj_col_year_next = $projected_color;
                				/*$projected_color_cpc = $budgeted_color_cpc;
                				$projected_color_year_cpc 	= $projected_color_cpc * 1.035;*/
                			}
                		}//end if $budgeted_color > 0
                    }
                    
                    /*If the closeout date for a device has passed then this device is no longer in use.  
                    	Set the projected amounts to match the consumed amounts because the device will not add to
                    	print total.  Set the projected next years volume to zero.*/
                    if($closeout_date != NULL && $closeout_date_unix < $endingDate){
                        $projected_black = $consumed_black_estimated;
                        $projected_color = $consumed_color_estimated;
                        
                        $proj_blk_year_next = 0;
                        $proj_col_year_next = 0;
                    }
                    
                    
                    if($proj_blk_year_next < 0){
                        $proj_blk_year_next = 0;
                    }
                    
                    if($proj_col_year_next < 0){
                        $proj_col_year_next = 0;
                    }
                    
                    /*Assign the values here after all of the conditional processing has been done.*/
                    $proj_blk_year_next_bldg = $proj_blk_year_next;
                	$proj_col_year_next_bldg = $proj_col_year_next;
                	
                	$over_under_budget_indiv =  ($projected_black + $projected_color) - ($budgeted_black + $budgeted_color);
                	$over_under_budget = $over_under_budget_indiv;
                	
                    
                    if($row->student_pop > 0){
                        $avg_black_per_student = $projected_black / $row->student_pop;
                        $avg_black_per_student_bldg = $avg_black_per_student;
                        
                        $avg_color_per_student = $projected_color / $row->student_pop;
                        $avg_color_per_student_bldg = $avg_color_per_student;
                        
                        
                        /*Assign the student_pop for the first record of each building excluding the first building. The student_pop for
                        the first building was assigned in the if block ($first_bldg == 'Y'). This is so we ONLY get the student_pop one
                        time for each building. There are many device records for each building.*/
                        $total_students_per_org += $row->student_pop;
                    }
                    else{
                        $avg_black_per_student = 0;
                        $avg_color_per_student = 0;
                    }
                    
                    
                    /*Now assign the current row values to the array under the new bldg_name*/
                    $row_array = array("consumed_black"=>$consumed_black_bldg, "budgeted_black"=>$budgeted_black_bldg,
                                        "projected_black"=>$projected_black_bldg, "avg_black_per_student"=>$avg_black_per_student_bldg,
                                        "next_year_black"=>$proj_blk_year_next_bldg, "consumed_color"=>$consumed_color_bldg,
                                        "budgeted_color"=>$budgeted_color_bldg, "projected_color"=>$projected_color_bldg,
                                        "avg_color_per_student"=>$avg_color_per_student_bldg, "next_year_color"=>$proj_col_year_next_bldg,
                                        "over_under_budget"=>$over_under_budget);
                                        
                    $array_bldgs[$bldg_name]= $row_array;
                }//end if($bldg_name != $row->bldg_name)
                
                
        }//end foreach
        
        
        if($total_students_per_org > 0){
            $avg_black_per_student_year = $projected_black_year / $total_students_per_org;
        	$avg_color_per_student_year = $projected_color_year / $total_students_per_org;
        }
        
        $year_totals_array = array("consumed_black_year"=>$consumed_black_year, "budgeted_black_year"=>$budgeted_black_year,
                                    "projected_black_year"=>$projected_black_year, "avg_black_per_student_year"=>$avg_black_per_student_year,
                                    "next_years_black_year"=>$proj_blk_year_next_tot, "consumed_color_year"=>$consumed_color_year,
                                    "budgeted_color_year"=>$budgeted_color_year, "projected_color_year"=>$projected_color_year,
                                    "avg_color_per_student_year"=>$avg_color_per_student_year, "next_years_color_year"=>$proj_col_year_next_tot);
        
    	
    	return view('buildings_cost.index', compact('org_id', 'org_name', 'year', 'years', 'timeline_date', 'display_meter_data', 'toner_alert_count', 'service_needed_count',
                                                           'contract_devices_count', 'non_contract_devices_count', 'devices_reporting_count',
                                                           'not_reporting_count', 'last_sync_date', 'volume_totals', 'array_bldgs',
                                                           'year_totals_array', 'proj_year', 'proj_year_next'))
    	
			->with('orgs', Organization::orderBy('org_name')->get()
			->where('client_status', '=', 'A')
			->where('organization_types_id', '!=', 7));
    }
    
    
    
    public function show_by_timeline_date($timeline_date){
        /*************************** BEGIN SPECIFIC TO SHOW BY ORG FUNCTION ONLY **************************/
    	/*Set the default values for these session variables*/
    	
    	$year = session('year');
    	$yearTab = 'y20' . $year;
    	$org_id = session('org_id');
    	
    	
    	/*Create a date object from the string date of the datepicker*/
    	/*The timeline date will default to 6-30 of the current year. When a year is selected from the
    	    school_year dropdown, this value will reset to 6-30 of the selected year.*/
    	$timeDate = date_create($timeline_date);
    	
    	
    	/*Get the unix date equivalent to the $timeline_date from the datepicker. This will be
    	    used to compare the timeline_date to the fiscal year end*/
    	$timeDateUnix = strtotime($timeline_date);
    	
    	
    	/*Get the 2 digit year from the datepicker's $timeline_date*/
        $timeDateYear = $timeDate->format('y');
         
        $yearEndDateUnix = strtotime('06/30/20' . $timeDateYear);
        
        if($timeDateUnix <= $yearEndDateUnix){
            $year = $timeDateYear;
        }
        else{
            $year = $timeDateYear + 1;
        }
        
        
        /*Set the $yearTab to y20xx, with xx being the 2 digit year from the datepicker
            and depending on whether or not the timeline_date is earlier than the end of
            fiscal year date.*/
            $yearTab = 'y20' . $year;
    	
    	
    	/*Assign these values to session variables*/
    	session(['year' => $year]);
    	session(['yearTab' => $yearTab]);
    	session(['timeline_date' => $timeline_date]);
    	
    	/*************************** END SPECIFIC TO SHOW BY ORG FUNCTION ONLY **************************/
        
        
        
        /**************************** BEGIN GET DATE VARIABLES **************************/
        /*Used to have dynamic headings for the Projected and Next Years columns.*/
        $proj_year = $year + 1;
        $proj_year_next = $year + 2;
        
        $gCalc = new GaugesCalcs($timeline_date);
        
        /*UNIX timestamps*/
        $startingDate = $gCalc->getStartingDate();
        $endingDate = $gCalc->getEndingDate();
        
        /*These are string values for the starting and ending dates based on the value in the Timeline Date datepicker*/
        $startingDateFormat = $gCalc->getStartingDateFormat();
        $endingDateFormat = $gCalc->getEndingDateFormat();
        
        $notReportingDate = $gCalc->getNotReportingDate();//$endingDate - (15*60*60*24);
        $notReportingDateFormat = $gCalc->getNotReportingDateFormat(); //date('Y-m-d H:i:s', $notReportingDate);
         
        $fiscal_days = $gCalc->getFiscalDays();
        $last_sync_date = $gCalc->getLastSyncDate($yearTab, $org_id);
        $minYear = $gCalc->getMinYear(); //used to limit the number of past years in the dropdown
        
        $gCount = new GaugesCounts();
        $toner_alert_count = $gCount->getTonerLowCount($yearTab, $org_id, $startingDateFormat, $endingDateFormat);
        $service_needed_count = $gCount->getServiceNeededCount($yearTab, $org_id, $startingDateFormat, $endingDateFormat, $year);
        $contract_devices_count = $gCount->getContractDeviceCount($yearTab, $org_id);
        $non_contract_devices_count = $gCount->getNonContractDeviceCount($yearTab, $org_id);
        $devices_reporting_count = $gCount->getReportingDeviceCount($yearTab, $org_id, $startingDateFormat, $notReportingDateFormat);
        $not_reporting_count = $gCount->getNonReportingDeviceCount($yearTab, $org_id, $startingDateFormat, $notReportingDateFormat);
        
        /**************************** END GET DATE VARIABLES **************************/
        
        /*Get the list of years for the dropdown*/
    	$years = DB::table('school_years')
    	            ->select(DB::raw('school_year, year_id'))
    	            ->where('year_id', '>=', $minYear)->get();
    	            
    	            
    	            
    	/*********************************** BEGIN VOLUME_TOTALS ************************************/    
        /*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)
                           ->where('black_meter', '!=',  0)
                           ->groupBy('serial_number');
                           
        
        /************************** BEGIN ORG COMMENCEMENT AND FISCAL DAYS ******************************/
        $org_commencement_date = '';
        $org_fiscal_days = 1;
        $org_name = '';
        $display_meter_data;
        
        $org_commencement_dt = DB::table('org_year_specific_data as orgs')
                            ->join('organizations', 'organizations.org_id', 'orgs.org_id')
                            ->select(DB::raw('orgs.commencement_date as org_commencement_date, organizations.org_name, orgs.display_meter_data'))
                            ->where('orgs.school_year', '=', $year)
                            ->where('orgs.org_id', '=', $org_id)->get();
        
        foreach($org_commencement_dt as $row){
            $org_commencement_date = strtotime($row->org_commencement_date);
            $org_name = $row->org_name;
            $display_meter_data = $row->display_meter_data;
            
            /*Set the org commencment date*/  
        	if ($row->org_commencement_date == NULL || $org_commencement_date < $startingDate) {
        		$org_commencement_date = $startingDate;
        	}
        	
        	
        	
        	/*Get the number of days between the organization commencement date and the ending fiscal date*/
    	    $org_fiscal_days = ($endingDate - $org_commencement_date) /60/60/24;
    	    
    	    if ($org_fiscal_days > 365) { 
        		$org_fiscal_days = 365; 
        	}//end if
        }//end foreach
        /************************** END ORG COMMENCEMENT AND FISCAL DAYS ******************************/
        
        $reporting = 'Y';
        $org_cm_diff = $org_fiscal_days;
        $last_meter_date = '';
        
        /*Variables to hold calculated data*/    				
        $consumed_black = 0;
        $consumed_color = 0;
        
        /*building totals*/
        $consumed_black_bldg = 0;
        $consumed_color_bldg = 0;
        
        $budgeted_black = 0;
        $budgeted_color = 0;
        
        /*building totals*/
        $budgeted_black_bldg = 0;
        $budgeted_color_bldg = 0;
        
        $projected_black = 0;
    	$projected_color = 0;
    	
        /*building totals*/
    	$projected_black_bldg = 0;
    	$projected_color_bldg = 0;
    	
    	$avg_black_per_student = 0;
    	$avg_black_per_student_bldg = 0;
    	
    	
    	$avg_color_per_student = 0;
    	$avg_color_per_student_bldg = 0;
    
    	
    	/*These will be the values for the Next years columns, which is really 2 years out.*/
    	$proj_blk_year_next = 0;
    	$proj_blk_year_next_bldg = 0;
    	$proj_blk_year_next_cost = 0;
    	$proj_col_year_next = 0;
    	$proj_col_year_next_bldg = 0;
    	$proj_col_year_next_cost = 0;
    	
    	
    	/*Variables to hold calculated data for the year total*/    				
        $consumed_black_year = 0;
        $consumed_color_year = 0;
        
        $budgeted_black_year = 0;
        $budgeted_color_year = 0;
        
        $projected_black_year = 0;
    	$projected_color_year = 0;
    	
    	$avg_black_per_student_year = 0;
    	$avg_color_per_student_year = 0;
    	
    	$proj_blk_year_next_tot = 0;
    	$proj_col_year_next_tot = 0;
    	
    	/*Used to calculate the avg per student (yearly projected / total students for the org) */ 
    	$total_students_per_org = 0;
    	
    	$over_under_budget_indiv = 0;
    	$over_under_budget = 0;
    	
        
        $consumed_black_estimated = 0;
        $consumed_black_estimated_cost = 0;
        $consumed_color_estimated = 0;
		$consumed_color_estimated_cost = 0;
		
		/*This will get the data for the first cell in the accordion child records*/
        $mach_data = DB::table('model_details as mach');
        
        
    	$volume_totals = DB::table($yearTab . '_meters as mtr')
                        ->join($yearTab . '_floorplan_machines as fpm', 'fpm.proposed_serial_number', '=', 'mtr.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 org', 'bldgs.organizations_id', '=', 'org.org_id')
        				
                        ->joinSub($serNumDateMetersMax, 'serNumDateMetersMax', function($join){
            												$join->on('mtr.serial_number', '=', 'serNumDateMetersMax.serial_number');
                    										$join->on('mtr.created_date', '=', 'serNumDateMetersMax.dt');
                        })
                        
                        ->joinSub($mach_data, 'mach_data', function($join){
                                    $join->on('fpm.proposed_model_id', '=', 'mach_data.model_id');
                        })
                        
                         
                         /*use cm_diff_orig to get the actual diff between endingDate and the device_commencement_date, then use cm_diff to further process. If
                         cm_diff_orig is < 0, cm_diff will be 0. If cm_diff_orig is 0 or greater, then cm_diff will be the actual value of cm_diff_orig*/
                                        
                        ->select(DB::raw('@device_commencement_date := (CASE WHEN fpm.commencement_date IS NULL THEN ' . $org_commencement_date . 
                                        ' WHEN UNIX_TIMESTAMP(fpm.commencement_date) < ' . $org_commencement_date . ' THEN ' . $org_commencement_date . 
                                        ' ELSE UNIX_TIMESTAMP(fpm.commencement_date) END) device_commencement_date,
                                        
                                        @c_blk := (CASE WHEN fpm.commencement_black_meter IS NULL THEN mtr.black_meter * cpc_black ELSE (mtr.black_meter - fpm.commencement_black_meter) * cpc_black END) c_blk,
                                        @c_col := (CASE WHEN fpm.commencement_color_meter IS NULL THEN mtr.color_meter * cpc_color ELSE (mtr.color_meter - fpm.commencement_color_meter) * cpc_color END) c_col,
                                        @last_diff_orig := (CASE WHEN mtr.created_date IS NULL THEN 1 ELSE (UNIX_TIMESTAMP(mtr.created_date) - @device_commencement_date) / 60/60/24 END) last_diff_orig,
                                        @last_diff := (CASE WHEN @last_diff_orig <= 0 THEN 1 ELSE @last_diff_orig END) last_diff,
                                        
                                        @cm_diff_orig := ((' . $endingDate . '- @device_commencement_date) /60/60/24) cm_diff_orig,
                                        @cm_diff := (CASE WHEN @cm_diff_orig < 0 THEN 0 ELSE @cm_diff_orig END) cm_diff,
                                        
                                        (CASE WHEN fpm.commencement_black_meter IS NULL THEN mtr.black_meter * cpc_black ELSE (mtr.black_meter - fpm.commencement_black_meter) * cpc_black END) consumed_black,
                                        (CASE WHEN fpm.commencement_color_meter IS NULL THEN mtr.color_meter * cpc_color ELSE (mtr.color_meter - fpm.commencement_color_meter) * cpc_color END) consumed_color,
                                        
                                        (CASE WHEN org.commencement_date IS NULL THEN fpm.budgeted_black
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN fpm.budgeted_black * cpc_black
                                        	ELSE (fpm.budgeted_black / ' . $fiscal_days . ') * ' . $org_fiscal_days . '  * cpc_black END) budgeted_black,
                                        	
                                        (CASE WHEN org.commencement_date IS NULL THEN fpm.budgeted_color
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN fpm.budgeted_color * cpc_color
                                        	ELSE (fpm.budgeted_color / ' . $fiscal_days . ') * ' . $org_fiscal_days . '  * cpc_color END) budgeted_color,
                                        	
                                        @projected_black := (CASE WHEN @cm_diff = 0 THEN 0 WHEN ' . $org_commencement_date . ' IS NULL THEN (@c_blk / @last_diff * 345)
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN (@c_blk / @last_diff * 345)
                                        	ELSE (@c_blk / @cm_diff * LEAST(' . $org_fiscal_days .', 345)) END) projected_black,
                                        	
                                        @projected_color := (CASE WHEN @cm_diff = 0 THEN 0 WHEN ' . $org_commencement_date . ' IS NULL THEN (@c_col / @last_diff * 345)
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN (@c_col / @last_diff * 345)
                                        	ELSE (@c_col / @cm_diff * LEAST(' . $org_fiscal_days .', 345)) END) projected_color,
                                        	
                                        (CASE WHEN @cm_diff = 0 THEN 0 WHEN ' . $org_commencement_date . ' IS NULL THEN @projected_black
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN @projected_black
                                        	ELSE (@c_blk / @cm_diff * LEAST(' . $org_fiscal_days .', 345)) END) proj_blk_year_next,
                                        	
                                        (CASE WHEN @cm_diff = 0 THEN 0 WHEN ' . $org_commencement_date . ' IS NULL THEN @projected_color
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN @projected_color
                                        	ELSE (@c_col / @cm_diff * LEAST(' . $org_fiscal_days .', 345)) END) proj_col_year_next,
                                        	
                                        bldgs.bldg_id, bldgs.bldg_name, bldgs.student_pop, mtr.created_date, fpm.closeout_date,
                                        fpm.proposed_serial_number, fpm.room_name, mach_make, model, is_color'))
                                        
                                        
                        ->whereIn('fpm.proposed_serial_number', function($query) use($year, $yearTab, $org_id){
                        									$query->select('fm.proposed_serial_number')
                                                            ->from($yearTab . '_floorplan_machines as fm')
                                                            ->join($yearTab . '_floorplans as fp', 'fp.fp_id', '=', 'fm.floorplans_id')
                                                            ->join($yearTab . '_buildings as bldgs', 'bldgs.bldg_id', '=', 'fp.buildings_id')
                                                            ->where('bldgs.organizations_id', '=', $org_id);
                        							})
                        							->orderBy('bldgs.bldg_name')
                        							->get();
                        							
                        							
            $first_bldg = 'Y';
            $bldg_name = '';
            $array_bldgs = array();
            
            foreach($volume_totals as $row){
                
                /* If this is the first building, assign the bldg_name to $bldg_name, then change value of $first_bldg to N */
                if($first_bldg == 'Y'){
                    $bldg_name = $row->bldg_name;
                    $first_bldg = 'N';
                    
                    /*Assign the student_pop for the first record of the first building here, then assign the
                        student_pop for the first record of each of the other buildings in the elseif block ($bldg_name != $row->bldg_name).
                        This is so we ONLY get the student_pop one time for each building. There are many device records for each building.*/
                    $total_students_per_org = $row->student_pop;
                }
                
                /* If the current building name = $bldg_name, then process fully*/
                if($bldg_name == $row->bldg_name){
                    
                    $cm_diff = $row->cm_diff;
                    
                    /*Process all rows for this building*/
                    $last_meter_date = strtotime($row->created_date);
                    
                    /*SJH - Note - the notReportingDate is just 15 days earlier than the last meter read date.*/
        	        if ( ($row->created_date == null) || ($last_meter_date < $notReportingDate) ) 
            			{ $reporting = "N"; } 
            		else 
            			{ $reporting = "Y"; }
            			
            			
            		/*Get the device closeout_date for use in setting the projected values for machines no longer in use*/
            		$closeout_date = $row->closeout_date;
            		$closeout_date_unix = strtotime($row->closeout_date);
                    
                    /*Get the consumed_black for each device. This will be used to calculate the projected_black*/
                    $consumed_black = $row->consumed_black;
                    $consumed_black_bldg += $row->consumed_black;
                    
                    /*Get the consumed_color for each device. This will be used to calculate the projected_black*/
                    $consumed_color = $row->consumed_color;
                    $consumed_color_bldg += $row->consumed_color;
                    
                    $budgeted_black = $row->budgeted_black;
                    $budgeted_black_bldg += $row->budgeted_black;
                    
                    $budgeted_color = $row->budgeted_color;
                    $budgeted_color_bldg += $row->budgeted_color;
                    
                    $projected_black = $row->projected_black;
                    $projected_black_bldg += $row->projected_black;
                    
                    $projected_color = $row->projected_color;
                    $projected_color_bldg += $row->projected_color;
                    
                    /*ORIGINAL values for the next year column, BEFORE any extra conditions are considered*/
                	$proj_blk_year_next = $row->proj_blk_year_next;
                	$proj_col_year_next = $row->proj_col_year_next;
                	
                    
                    /*Variables to hold the year totals*/    				
                    $consumed_black_year += $consumed_black;
                    $consumed_color_year += $consumed_color;
                    
                    $budgeted_black_year += $budgeted_black;
                    $budgeted_color_year += $budgeted_color;
                    
                    $projected_black_year += $projected_black;
                	$projected_color_year += $projected_color;
                	
                	$proj_blk_year_next_tot += $row->proj_blk_year_next;
    	            $proj_col_year_next_tot += $row->proj_col_year_next;
                	
                	
                	if($row->cm_diff > 0){
                	    $consumed_black_estimated =  $consumed_black / $cm_diff * $org_cm_diff;
                		//$consumed_black_estimated_cpc =  $consumed_black_cpc / $cm_diff * $org_cm_diff;
                		$consumed_color_estimated =  $consumed_color / $cm_diff * $org_cm_diff;
		                //$consumed_color_estimated_cpc =  $consumed_color_cpc / $cm_diff * $org_cm_diff;
                	}
                	
                	else {
                		$consumed_black_estimated =  $consumed_black;
                		//$consumed_black_estimated_cpc =  $consumed_black_cpc;
                		$consumed_color_estimated =  $consumed_color;
		                //$consumed_color_estimated_cpc =  $consumed_color_cpc;
                	}
                	
                	
                    /*Do further processing for projected values*/
                    if($reporting == 'N' && $org_fiscal_days > 0){
                        if($budgeted_black > 0){
                            
                            /*If the projected black for the year is < the budgeted black for the year then use the
                				budgeted black year amount instead of projected black.*/
                			if ($proj_blk_year_next < ($budgeted_black / $org_fiscal_days * $fiscal_days)) {
                				$projected_black = $budgeted_black;
                				$proj_blk_year_next = $projected_black;
                			
                				/*$projected_black_cpc = $budgeted_black_cpc;
                				$projected_black_year_cpc 	= $projected_black_cpc * 1.035;*/
                			}
                        }//end if $budgeted_black > 0
                        
                        if ( $budgeted_color > 0) {
                			/*If the projected color for the year is < the budgeted color for the year then use the
                				budgeted color year amount instead of projected color.*/
                			if ($proj_col_year_next < ($budgeted_color / $org_fiscal_days * $fiscal_days)) {
                				$projected_color = $budgeted_color;
                				$proj_col_year_next = $projected_color;
                				/*$projected_color_cpc = $budgeted_color_cpc;
                				$projected_color_year_cpc 	= $projected_color_cpc * 1.035;*/
                			}
                		}//end if $budgeted_color > 0
                    }
                    
                    /*If the closeout date for a device has passed then this device is no longer in use.  
                    	Set the projected amounts to match the consumed amounts because the device will not add to
                    	print total.  Set the projected next years volume to zero.*/
                    if($closeout_date != NULL && $closeout_date_unix < $endingDate){
                        $projected_black = $consumed_black_estimated;
                        $projected_color = $consumed_color_estimated;
                        
                        $proj_blk_year_next = 0;
                        $proj_col_year_next = 0;
                    }
                    
                    if($proj_blk_year_next < 0){
                        $proj_blk_year_next = 0;
                    }
                    
                    if($proj_col_year_next < 0){
                        $proj_col_year_next = 0;
                    }
                    
                    /*Assign the values here after all of the conditional processing has been done.*/
                    $proj_blk_year_next_bldg += $proj_blk_year_next;
                	$proj_col_year_next_bldg += $proj_col_year_next;
                	
                	$over_under_budget_indiv = ($projected_black + $projected_color) - ($budgeted_black + $budgeted_color);
                	$over_under_budget += $over_under_budget_indiv;
                    
                    
                    
                    if($row->student_pop > 0){
                        $avg_black_per_student = $projected_black / $row->student_pop;
                        $avg_black_per_student_bldg += $avg_black_per_student;
                        
                        $avg_color_per_student = $projected_color / $row->student_pop;
                        $avg_color_per_student_bldg += $avg_color_per_student;
                    }
                    
                    else{
                        $avg_black_per_student = 0;
                        $avg_color_per_student = 0;
                    }
                    
                    
                    $row_array = array("consumed_black"=>$consumed_black_bldg, "budgeted_black"=>$budgeted_black_bldg,
                                        "projected_black"=>$projected_black_bldg, "avg_black_per_student"=>$avg_black_per_student_bldg,
                                        "next_year_black"=>$proj_blk_year_next_bldg, "consumed_color"=>$consumed_color_bldg,
                                        "budgeted_color"=>$budgeted_color_bldg, "projected_color"=>$projected_color_bldg,
                                        "avg_color_per_student"=>$avg_color_per_student_bldg, "next_year_color"=>$proj_col_year_next_bldg,
                                        "over_under_budget"=>$over_under_budget);
                                        
                    $array_bldgs[$bldg_name] = $row_array;
                }//end if($bldg_name == $row->bldg_name)
                
                
                /* If the current building name is different from $bldg_name, then this is a new building, so assign the new bldg_name to $bldg_name, then process fully */ 
                elseif($bldg_name != $row->bldg_name){
                    
                    $cm_diff = $row->cm_diff;
                    
                    /*Assign the current values to the array before starting with a new building
                    $row_array = array("consumed_black"=>$consumed_black_bldg, "budgeted_black"=>$budgeted_black_bldg,
                                        "projected_black"=>$projected_black_bldg, "avg_black_per_student"=>$avg_black_per_student_bldg,
                                        "next_year_black"=>$proj_blk_year_next,
                                        "consumed_color"=>$consumed_color_bldg, "budgeted_color"=>$budgeted_color_bldg, "projected_color"=>$projected_color_bldg,
                                        "avg_color_per_student"=>$avg_color_per_student_bldg, "next_year_color"=>$proj_col_year_next_bldg);
                                        
                    $array_bldgs[$bldg_name] = $row_array;*/
                   
                    
                    /*Assign the new building name to $bldg_name*/
                    $bldg_name = $row->bldg_name;
                    
                     /*Process all rows for this building*/
                    $last_meter_date = strtotime($row->created_date);
                    
                    /*SJH - Note - the notReportingDate is just 15 days earlier than the last meter read date.*/
        	        if ( ($row->created_date == null) || ($last_meter_date < $notReportingDate) ) 
            			{ $reporting = "N"; } 
            		else 
            			{ $reporting = "Y"; }
                    
                    /*Get the device closeout_date for use in setting the projected values for machines no longer in use*/
            		$closeout_date = $row->closeout_date;
            		$closeout_date_unix = strtotime($row->closeout_date);
                    
                    /*Get the consumed_black for each device. This will be used to calculate the projected_black*/
                    $consumed_black = $row->consumed_black;
                    $consumed_black_bldg = $row->consumed_black;
                    
                    /*Get the consumed_color for each device. This will be used to calculate the projected_black*/
                    $consumed_color = $row->consumed_color;
                    $consumed_color_bldg = $row->consumed_color;
                    
                    $budgeted_black = $row->budgeted_black;
                    $budgeted_black_bldg = $row->budgeted_black;
                    
                    $budgeted_color = $row->budgeted_color;
                    $budgeted_color_bldg = $row->budgeted_color;
                    
                    $projected_black = $row->projected_black;
                    $projected_black_bldg = $row->projected_black;
                    
                    $projected_color = $row->projected_color;
                    $projected_color_bldg = $row->projected_color;
                    
                	/*values for the next year column*/
                	$proj_blk_year_next = $row->proj_blk_year_next;
                	//$proj_blk_year_next_bldg = $row->proj_blk_year_next;
                	
                	$proj_col_year_next = $row->proj_col_year_next;
                	//$proj_col_year_next_bldg = $row->proj_col_year_next;
                    
                    /*Variables to hold the year totals*/    				
                    $consumed_black_year += $consumed_black;
                    $consumed_color_year += $consumed_color;
                    
                    $budgeted_black_year += $budgeted_black;
                    $budgeted_color_year += $budgeted_color;
                    
                    $projected_black_year += $projected_black;
                	$projected_color_year += $projected_color;
                	
                	$proj_blk_year_next_tot += $row->proj_blk_year_next;
    	            $proj_col_year_next_tot += $row->proj_col_year_next;
                	
                	
                	if($row->cm_diff > 0){
                	    $consumed_black_estimated =  $consumed_black / $cm_diff * $org_cm_diff;
                		//$consumed_black_estimated_cpc =  $consumed_black_cpc / $cm_diff * $org_cm_diff;
                		$consumed_color_estimated =  $consumed_color / $cm_diff * $org_cm_diff;
		                //$consumed_color_estimated_cpc =  $consumed_color_cpc / $cm_diff * $org_cm_diff;
                	}
                	
                	else {
                		$consumed_black_estimated =  $consumed_black;
                		//$consumed_black_estimated_cpc =  $consumed_black_cpc;
                		$consumed_color_estimated =  $consumed_color;
		                //$consumed_color_estimated_cpc =  $consumed_color_cpc;
                	}
                	
                	
                	/*Do further processing for projected values*/
                    if($reporting == 'N' && $org_fiscal_days > 0){
                        if($budgeted_black > 0){
                            /*If the projected black for the year is < the budgeted black for the year then use the
                				budgeted black year amount instead of projected black.*/
                			if ($proj_blk_year_next < ($budgeted_black / $org_fiscal_days * $fiscal_days)) {
                				$projected_black = $budgeted_black;
                				$proj_blk_year_next = $projected_black;
                				/*$projected_black_cpc = $budgeted_black_cpc;
                				$projected_black_year_cpc 	= $projected_black_cpc * 1.035;*/
                			}
                        }//end if $budgeted_black > 0
                        
                        if ( $budgeted_color > 0) {
                			/*If the projected color for the year is < the budgeted color for the year then use the
                				budgeted color year amount instead of projected color.*/
                			if ($proj_col_year_next < ($budgeted_color / $org_fiscal_days * $fiscal_days)) {
                				$projected_color = $budgeted_color;
                				$proj_col_year_next = $projected_color;
                				/*$projected_color_cpc = $budgeted_color_cpc;
                				$projected_color_year_cpc 	= $projected_color_cpc * 1.035;*/
                			}
                		}//end if $budgeted_color > 0
                    }
                    
                    /*If the closeout date for a device has passed then this device is no longer in use.  
                    	Set the projected amounts to match the consumed amounts because the device will not add to
                    	print total.  Set the projected next years volume to zero.*/
                    if($closeout_date != NULL && $closeout_date_unix < $endingDate){
                        $projected_black = $consumed_black_estimated;
                        $projected_color = $consumed_color_estimated;
                        
                        $proj_blk_year_next = 0;
                        $proj_col_year_next = 0;
                    }
                    
                    
                    if($proj_blk_year_next < 0){
                        $proj_blk_year_next = 0;
                    }
                    
                    if($proj_col_year_next < 0){
                        $proj_col_year_next = 0;
                    }
                    
                    /*Assign the values here after all of the conditional processing has been done.*/
                    $proj_blk_year_next_bldg = $proj_blk_year_next;
                	$proj_col_year_next_bldg = $proj_col_year_next;
                	
                	$over_under_budget_indiv = ($projected_black + $projected_color) - ($budgeted_black + $budgeted_color);
                	$over_under_budget = $over_under_budget_indiv;
                	
                    
                    if($row->student_pop > 0){
                        $avg_black_per_student = $projected_black / $row->student_pop;
                        $avg_black_per_student_bldg = $avg_black_per_student;
                        
                        $avg_color_per_student = $projected_color / $row->student_pop;
                        $avg_color_per_student_bldg = $avg_color_per_student;
                        
                        
                        /*Assign the student_pop for the first record of each building excluding the first building. The student_pop for
                        the first building was assigned in the if block ($first_bldg == 'Y'). This is so we ONLY get the student_pop one
                        time for each building. There are many device records for each building.*/
                        $total_students_per_org += $row->student_pop;
                    }
                    else{
                        $avg_black_per_student = 0;
                        $avg_color_per_student = 0;
                    }
                    
                    
                    /*Now assign the current row values to the array under the new bldg_name*/
                    $row_array = array("consumed_black"=>$consumed_black_bldg, "budgeted_black"=>$budgeted_black_bldg,
                                        "projected_black"=>$projected_black_bldg, "avg_black_per_student"=>$avg_black_per_student_bldg,
                                        "next_year_black"=>$proj_blk_year_next_bldg, "consumed_color"=>$consumed_color_bldg,
                                        "budgeted_color"=>$budgeted_color_bldg, "projected_color"=>$projected_color_bldg,
                                        "avg_color_per_student"=>$avg_color_per_student_bldg, "next_year_color"=>$proj_col_year_next_bldg,
                                        "over_under_budget"=>$over_under_budget);
                                        
                    $array_bldgs[$bldg_name]= $row_array;
                }//end if($bldg_name != $row->bldg_name)
                
                
        }//end foreach
        
        
        if($total_students_per_org > 0){
            $avg_black_per_student_year = $projected_black_year / $total_students_per_org;
        	$avg_color_per_student_year = $projected_color_year / $total_students_per_org;
        }
        
        $year_totals_array = array("consumed_black_year"=>$consumed_black_year, "budgeted_black_year"=>$budgeted_black_year,
                                    "projected_black_year"=>$projected_black_year, "avg_black_per_student_year"=>$avg_black_per_student_year,
                                    "next_years_black_year"=>$proj_blk_year_next_tot, "consumed_color_year"=>$consumed_color_year,
                                    "budgeted_color_year"=>$budgeted_color_year, "projected_color_year"=>$projected_color_year,
                                    "avg_color_per_student_year"=>$avg_color_per_student_year, "next_years_color_year"=>$proj_col_year_next_tot);
                        							
        
    	
    	return view('buildings_cost.index', compact('org_id', 'org_name', 'year', 'years', 'timeline_date', 'display_meter_data', 'toner_alert_count', 'service_needed_count',
                                                           'contract_devices_count', 'non_contract_devices_count', 'devices_reporting_count',
                                                           'not_reporting_count', 'last_sync_date', 'volume_totals', 'array_bldgs',
                                                           'year_totals_array', 'proj_year', 'proj_year_next'))
    	
			->with('orgs', Organization::orderBy('org_name')->get()
			->where('client_status', '=', 'A')
			->where('organization_types_id', '!=', 7));
    }


    
    public function create(){}
    public function store(Request $request){}
    public function edit($id){}
    public function update(Request $request, $id){}
    public function destroy($id){}
}