<?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 DepartmentsVolumeController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        return view('departments_volume.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;
        
        /*department totals*/
        $consumed_black_dept = 0;
        $consumed_color_dept = 0;
        
        $budgeted_black = 0;
        $budgeted_color = 0;
        
        /*department totals*/
        $budgeted_black_dept = 0;
        $budgeted_color_dept = 0;
        
        $projected_black = 0;
    	$projected_black_cost = 0;
    	$projected_color = 0;
    	$projected_color_cost = 0;
    	
        /*department totals*/
    	$projected_black_dept = 0;
    	$projected_color_dept = 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_dept = 0;
    	$proj_blk_year_next_cost = 0;
    	$proj_col_year_next = 0;
    	$proj_col_year_next_dept = 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;
    
    	$proj_blk_year_next_tot = 0;
    	$proj_col_year_next_tot = 0;
    
        $consumed_black_estimated = 0;
        $consumed_color_estimated = 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 . '_departments as depts', 'depts.dept_id', '=', 'fpm.departments_id')
                        //->join($yearTab . '_organizations as org', 'depts.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 ELSE (mtr.black_meter - fpm.commencement_black_meter) END) c_blk,
                                        @c_col := (CASE WHEN fpm.commencement_color_meter IS NULL THEN mtr.color_meter ELSE (mtr.color_meter - fpm.commencement_color_meter) 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 ELSE (mtr.black_meter - fpm.commencement_black_meter) END) consumed_black,
                                        (CASE WHEN fpm.commencement_color_meter IS NULL THEN mtr.color_meter ELSE (mtr.color_meter - fpm.commencement_color_meter) END) consumed_color,
                                        
                                        (CASE WHEN ' . $org_commencement_date . ' IS NULL THEN fpm.budgeted_black
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN fpm.budgeted_black
                                        	ELSE (fpm.budgeted_black / ' . $fiscal_days . ') * ' . $org_fiscal_days . ' END) budgeted_black,
                                        	
                                        (CASE WHEN ' . $org_commencement_date . ' IS NULL THEN fpm.budgeted_color
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN fpm.budgeted_color
                                        	ELSE (fpm.budgeted_color / ' . $fiscal_days . ') * ' . $org_fiscal_days . ' 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,
                                        	
                                        depts.dept_id, depts.dept_name, 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 . '_departments as dept', 'dept.dept_id', '=', 'fm.departments_id')
                                                            ->where('dept.organizations_id', '=', $org_id);
                        							})
                        							->orderBy('depts.dept_name')
                        							->get();
                        							
                        							
                        							
                        							
            $first_dept = 'Y';
            $dept_name = '';
            $array_depts = array();
            $array_indiv_proj_vals = array();
            
            foreach($volume_totals as $row){
                
                /* If this is the first dept, assign the dept_name to $dept_name, then change value of $first_dept to N */
                if($first_dept == 'Y'){
                    $dept_name = $row->dept_name;
                    $first_dept = 'N';
                }//end if $first_dept == Y
                
                /* If the current department name = $dept_name, then process fully*/
                if($dept_name == $row->dept_name){
                    
                    $cm_diff = $row->cm_diff;
                    
                    /*Process all rows for this department*/
                    $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_dept += $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_dept += $row->consumed_color;
                    
                    $budgeted_black = $row->budgeted_black;
                    $budgeted_black_dept += $row->budgeted_black;
                    
                    $budgeted_color = $row->budgeted_color;
                    $budgeted_color_dept += $row->budgeted_color;
                    
                    $projected_black = $row->projected_black;
                    $projected_black_dept += $row->projected_black;
                    
                    $projected_color = $row->projected_color;
                    $projected_color_dept += $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_color_estimated =  $consumed_color / $cm_diff * $org_cm_diff;
                	}
                	
                	else {
                		$consumed_black_estimated =  $consumed_black;
                		$consumed_color_estimated =  $consumed_color;
                	}
                	
                	
                    /*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;
                			}
                        }//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;
                			}
                		}//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_dept += $proj_blk_year_next;
                	$proj_col_year_next_dept += $proj_col_year_next;
                	
                	/*Add the projected values for the current serial_number to the $proj_vals_array, then add that array
                	to the $array_indiv_proj_vals 2 dimensional array. These vals will be shown in the child records for
                	each department in the accordion.*/
                	
                	$proj_vals_array = array("projected_black"=>$projected_black, "projected_color"=>$projected_color,
                	    "proj_blk_year_next"=>$proj_blk_year_next, "proj_col_year_next"=>$proj_col_year_next);
                	$array_indiv_proj_vals[$row->proposed_serial_number] = $proj_vals_array;
                    
                    
                    $row_array = array("consumed_black"=>$consumed_black_dept, "budgeted_black"=>$budgeted_black_dept,
                                        "projected_black"=>$projected_black_dept, "next_year_black"=>$proj_blk_year_next_dept,
                                        "consumed_color"=>$consumed_color_dept, "budgeted_color"=>$budgeted_color_dept,
                                        "projected_color"=>$projected_color_dept, "next_year_color"=>$proj_col_year_next_dept);
                                        
                    $array_depts[$dept_name] = $row_array;
                }//end if($dept_name == $row->dept_name)
                
                /* If the current dept name is different from $dept_name, then this is a new dept, so assign the new dept_name to $dept_name, then process fully */ 
                elseif($dept_name != $row->dept_name){
                    
                    $cm_diff = $row->cm_diff;
                    
                    /*Assign the new dept name to $dept_name*/
                    $dept_name = $row->dept_name;
                    
                     /*Process all rows for this department*/
                    $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_dept = $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_dept = $row->consumed_color;
                    
                    $budgeted_black = $row->budgeted_black;
                    $budgeted_black_dept = $row->budgeted_black;
                    
                    $budgeted_color = $row->budgeted_color;
                    $budgeted_color_dept = $row->budgeted_color;
                    
                    $projected_black = $row->projected_black;
                    $projected_black_dept = $row->projected_black;
                    
                    $projected_color = $row->projected_color;
                    $projected_color_dept = $row->projected_color;
                    
                	/*values for the next year column*/
                	$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_color_estimated =  $consumed_color / $cm_diff * $org_cm_diff;
                	}
                	
                	else {
                		$consumed_black_estimated =  $consumed_black;
                		$consumed_color_estimated =  $consumed_color;
                	}
                	
                	
                	/*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;
                			}
                        }//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;
                			}
                		}//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_dept = $proj_blk_year_next;
                	$proj_col_year_next_dept = $proj_col_year_next;
                	
                	/*Add the projected values for the current serial_number to the $proj_vals_array, then add that array
                	to the $array_indiv_proj_vals 2 dimensional array. These vals will be shown in the child records for
                	each department in the accordion.*/
                	
                	$proj_vals_array = array("projected_black"=>$projected_black, "projected_color"=>$projected_color, "proj_blk_year_next"=>$proj_blk_year_next,
                	    "proj_col_year_next"=>$proj_col_year_next);
                	$array_indiv_proj_vals[$row->proposed_serial_number] = $proj_vals_array;
                    
                     
                    /*Now assign the current row values to the array under the new dept_name*/
                    $row_array = array("consumed_black"=>$consumed_black_dept, "budgeted_black"=>$budgeted_black_dept,
                                        "projected_black"=>$projected_black_dept, "next_year_black"=>$proj_blk_year_next_dept,
                                        "consumed_color"=>$consumed_color_dept, "budgeted_color"=>$budgeted_color_dept,
                                        "projected_color"=>$projected_color_dept, "next_year_color"=>$proj_col_year_next_dept);
                                        
                    $array_depts[$dept_name]= $row_array;
                }//end if($dept_name != $row->dept_name)
                
        }//end foreach
        
        
        $year_totals_array = array("consumed_black_year"=>$consumed_black_year, "budgeted_black_year"=>$budgeted_black_year,
                                    "projected_black_year"=>$projected_black_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, "next_years_color_year"=>$proj_col_year_next_tot);
                        							
        
    	
    	return view('departments_volume.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_depts',
                                                           'year_totals_array', 'array_indiv_proj_vals', '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 = 'Y';
        
         $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;
        
        /*department totals*/
        $consumed_black_bldg = 0;
        $consumed_color_bldg = 0;
        
        $budgeted_black = 0;
        $budgeted_color = 0;
        
        /*department totals*/
        $budgeted_black_bldg = 0;
        $budgeted_color_bldg = 0;
        
        $projected_black = 0;
    	$projected_black_cost = 0;
    	$projected_color = 0;
    	$projected_color_cost = 0;
    	
        /*department totals*/
    	$projected_black_bldg = 0;
    	$projected_color_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;
    	
    
    	$proj_blk_year_next_tot = 0;
    	$proj_col_year_next_tot = 0;
        
        $consumed_black_estimated = 0;
        $consumed_color_estimated = 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 . '_departments as depts', 'depts.dept_id', '=', 'fpm.departments_id')
                        //->join($yearTab . '_organizations as org', 'depts.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 ELSE (mtr.black_meter - fpm.commencement_black_meter) END) c_blk,
                                        @c_col := (CASE WHEN fpm.commencement_color_meter IS NULL THEN mtr.color_meter ELSE (mtr.color_meter - fpm.commencement_color_meter) 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 ELSE (mtr.black_meter - fpm.commencement_black_meter) END) consumed_black,
                                        (CASE WHEN fpm.commencement_color_meter IS NULL THEN mtr.color_meter ELSE (mtr.color_meter - fpm.commencement_color_meter) END) consumed_color,
                                        
                                        (CASE WHEN ' . $org_commencement_date . ' IS NULL THEN fpm.budgeted_black
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN fpm.budgeted_black
                                        	ELSE (fpm.budgeted_black / ' . $fiscal_days . ') * ' . $org_fiscal_days . ' END) budgeted_black,
                                        	
                                        (CASE WHEN ' . $org_commencement_date . ' IS NULL THEN fpm.budgeted_color
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN fpm.budgeted_color
                                        	ELSE (fpm.budgeted_color / ' . $fiscal_days . ') * ' . $org_fiscal_days . ' 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,
                                        	
                                        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 . '_departments as dept', 'dept.dept_id', '=', 'fm.departments_id')
                                                            ->where('depts.organizations_id', '=', $org_id);
                        							})
                        							->orderBy('depts.dept_name')
                        							->get();
                        							
            $first_bldg = 'Y';
            $bldg_name = '';
            $array_depts = array();
            $array_indiv_proj_vals = array();
            
            foreach($volume_totals as $row){
                
                /* If this is the first department, assign the dept_name to $dept_name, then change value of $first_dept to N */
                if($first_dept == 'Y'){
                    $dept_name = $row->dept_name;
                    $first_dept = 'N';
                }
                
                /* If the current dept name = $dept_name, then process fully*/
                if($dept_name == $row->dept_name){
                    
                    $cm_diff = $row->cm_diff;
                    
                    /*Process all rows for this department*/
                    $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_color_estimated =  $consumed_color / $cm_diff * $org_cm_diff;
                	}
                	
                	else {
                		$consumed_black_estimated =  $consumed_black;
                		$consumed_color_estimated =  $consumed_color;
                	}
                	
                	
                    /*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;
                			}
                        }//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;
                			}
                		}//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;
                	
                	/*Add the projected values for the current serial_number to the $proj_vals_array, then add that array
                	to the $array_indiv_proj_vals 2 dimensional array. These vals will be shown in the child records for
                	each dept in the accordion.*/
                	
                	$proj_vals_array = array("projected_black"=>$projected_black, "projected_color"=>$projected_color, "proj_blk_year_next"=>$proj_blk_year_next,
                	    "proj_col_year_next"=>$proj_col_year_next);
                	$array_indiv_proj_vals[$row->proposed_serial_number] = $proj_vals_array;
                    
                    
                    $row_array = array("consumed_black"=>$consumed_black_bldg, "budgeted_black"=>$budgeted_black_bldg,
                                        "projected_black"=>$projected_black_bldg, "next_year_black"=>$proj_blk_year_next_bldg,
                                        "consumed_color"=>$consumed_color_bldg, "budgeted_color"=>$budgeted_color_bldg,
                                        "projected_color"=>$projected_color_bldg, "next_year_color"=>$proj_col_year_next_bldg);
                                        
                    $array_depts[$dept_name] = $row_array;
                }//end if($dept_name == $row->dept_name)
                
                /* If the current dept name is different from $dept_name, then this is a new dept, so assign the new dept_name to $dept_name, then process fully */ 
                elseif($dept_name != $row->dept_name){
                    
                    $cm_diff = $row->cm_diff;
                   
                    /*Assign the new department name to $dept_name*/
                    $dept_name = $row->dept_name;
                    
                     /*Process all rows for this dept*/
                    $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_color_estimated =  $consumed_color / $cm_diff * $org_cm_diff;
                	}
                	
                	else {
                		$consumed_black_estimated =  $consumed_black;
                		$consumed_color_estimated =  $consumed_color;
                	}
                	
                	
                	/*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;
                			}
                        }//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;
                			}
                		}//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;
                	
                	/*Add the projected values for the current serial_number to the $proj_vals_array, then add that array
                	to the $array_indiv_proj_vals 2 dimensional array. These vals will be shown in the child records for
                	each department in the accordion.*/
                	
                	$proj_vals_array = array("projected_black"=>$projected_black, "projected_color"=>$projected_color, "proj_blk_year_next"=>$proj_blk_year_next,
                	    "proj_col_year_next"=>$proj_col_year_next);
                	$array_indiv_proj_vals[$row->proposed_serial_number] = $proj_vals_array;
                	
                    
                    /*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, "next_year_black"=>$proj_blk_year_next_bldg,
                                        "consumed_color"=>$consumed_color_bldg, "budgeted_color"=>$budgeted_color_bldg,
                                        "projected_color"=>$projected_color_bldg, "next_year_color"=>$proj_col_year_next_bldg);
                                        
                    $array_depts[$dept_name] = $row_array;
                }//end if
        }//end foreach
        
        
        
        $year_totals_array = array("consumed_black_year"=>$consumed_black_year, "budgeted_black_year"=>$budgeted_black_year,
                                    "projected_black_year"=>$projected_black_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, "next_years_color_year"=>$proj_col_year_next_tot);
                        							
        
    	
    	return view('departments_volume.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_depts',
                                                           'year_totals_array', 'array_indiv_proj_vals', '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;
        
        /*department totals*/
        $consumed_black_bldg = 0;
        $consumed_color_bldg = 0;
        
        $budgeted_black = 0;
        $budgeted_color = 0;
        
        /*department totals*/
        $budgeted_black_bldg = 0;
        $budgeted_color_bldg = 0;
        
        $projected_black = 0;
    	$projected_black_cost = 0;
    	$projected_color = 0;
    	$projected_color_cost = 0;
    	
        /*department totals*/
    	$projected_black_bldg = 0;
    	$projected_color_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;
    
    	$proj_blk_year_next_tot = 0;
    	$proj_col_year_next_tot = 0;
        
        $consumed_black_estimated = 0;
        $consumed_color_estimated = 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 . '_departments as depts', 'dept.dept_id', '=', 'fpm.departments_id')
                        //->join($yearTab . '_organizations as org', 'depts.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 ELSE (mtr.black_meter - fpm.commencement_black_meter) END) c_blk,
                                        @c_col := (CASE WHEN fpm.commencement_color_meter IS NULL THEN mtr.color_meter ELSE (mtr.color_meter - fpm.commencement_color_meter) 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 ELSE (mtr.black_meter - fpm.commencement_black_meter) END) consumed_black,
                                        (CASE WHEN fpm.commencement_color_meter IS NULL THEN mtr.color_meter ELSE (mtr.color_meter - fpm.commencement_color_meter) END) consumed_color,
                                        
                                        (CASE WHEN ' . $org_commencement_date . ' IS NULL THEN fpm.budgeted_black
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN fpm.budgeted_black
                                        	ELSE (fpm.budgeted_black / ' . $fiscal_days . ') * ' . $org_fiscal_days . ' END) budgeted_black,
                                        	
                                        (CASE WHEN ' . $org_commencement_date . ' IS NULL THEN fpm.budgeted_color
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN fpm.budgeted_color
                                        	ELSE (fpm.budgeted_color / ' . $fiscal_days . ') * ' . $org_fiscal_days . ' 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,
                                        	
                                        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 . '_departments as dept', 'dept.dept_id', '=', 'fm.departments_id')
                                                            ->where('depts.organizations_id', '=', $org_id);
                        							})
                        							->orderBy('depts.dept_name')
                        							->get();
                        							
            $first_dept = 'Y';
            $dept_name = '';
            $array_depts = array();
            $array_indiv_proj_vals = array();
            
            foreach($volume_totals as $row){
                
                /* If this is the first department, assign the dept_name to $dept_name, then change value of $first_dept to N */
                if($first_dept == 'Y'){
                    $dept_name = $row->dept_name;
                    $first_dept = 'N';
                }
                
                /* If the current dept name = $dept_name, then process fully*/
                if($dept_name == $row->dept_name){
                    
                    $cm_diff = $row->cm_diff;
                    
                    /*Process all rows for this department*/
                    $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_color_estimated =  $consumed_color / $cm_diff * $org_cm_diff;
                	}
                	
                	else {
                		$consumed_black_estimated =  $consumed_black;
                		$consumed_color_estimated =  $consumed_color;
                	}
                	
                	
                    /*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;
                			}
                        }//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;
                			}
                		}//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;
                	
                	/*Add the projected values for the current serial_number to the $proj_vals_array, then add that array
                	to the $array_indiv_proj_vals 2 dimensional array. These vals will be shown in the child records for
                	each department in the accordion.*/
                	
                	$proj_vals_array = array("projected_black"=>$projected_black, "projected_color"=>$projected_color, "proj_blk_year_next"=>$proj_blk_year_next,
                	    "proj_col_year_next"=>$proj_col_year_next);
                	$array_indiv_proj_vals[$row->proposed_serial_number] = $proj_vals_array;
                    
                    
                    $row_array = array("consumed_black"=>$consumed_black_bldg, "budgeted_black"=>$budgeted_black_bldg,
                                        "projected_black"=>$projected_black_bldg, "next_year_black"=>$proj_blk_year_next_bldg,
                                        "consumed_color"=>$consumed_color_bldg, "budgeted_color"=>$budgeted_color_bldg,
                                        "projected_color"=>$projected_color_bldg, "next_year_color"=>$proj_col_year_next_bldg);
                                        
                    $array_depts[$dept_name] = $row_array;
                }//end if
                
                /* If the current department name is different from $dept_name, then this is a new department, so assign the new dept_name to $dept_name, then process fully */ 
                elseif($dept_name != $row->dept_name){
                    
                    $cm_diff = $row->cm_diff;
                    
                    
                    /*Assign the new department name to $dept_name*/
                    $dept_name = $row->dept_name;
                    
                     /*Process all rows for this dept*/
                    $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_color_estimated =  $consumed_color / $cm_diff * $org_cm_diff;
                	}
                	
                	else {
                		$consumed_black_estimated =  $consumed_black;
                		$consumed_color_estimated =  $consumed_color;
                	}
                	
                	
                	/*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;
                			}
                        }//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;
                			}
                		}//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;
                	
                	/*Add the projected values for the current serial_number to the $proj_vals_array, then add that array
                	to the $array_indiv_proj_vals 2 dimensional array. These vals will be shown in the child records for
                	each department in the accordion.*/
                	
                	$proj_vals_array = array("projected_black"=>$projected_black, "projected_color"=>$projected_color, "proj_blk_year_next"=>$proj_blk_year_next,
                	    "proj_col_year_next"=>$proj_col_year_next);
                	$array_indiv_proj_vals[$row->proposed_serial_number] = $proj_vals_array;
                    
                    
                    /*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, "next_year_black"=>$proj_blk_year_next_bldg,
                                        "consumed_color"=>$consumed_color_bldg, "budgeted_color"=>$budgeted_color_bldg,
                                        "projected_color"=>$projected_color_bldg, "next_year_color"=>$proj_col_year_next_bldg);
                                        
                    $array_depts[$dept_name] = $row_array;
                }//end if
        }//end foreach
        
        
       
        $year_totals_array = array("consumed_black_year"=>$consumed_black_year, "budgeted_black_year"=>$budgeted_black_year,
                                    "projected_black_year"=>$projected_black_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, "next_years_color_year"=>$proj_col_year_next_tot);
                        							
        
    	
    	return view('departments_volume.index', compact('org_id', 'org_name', 'year', 'years', 'timeline_date', 'toner_alert_count', 'display_meter_data', 'service_needed_count',
                                                           'contract_devices_count', 'non_contract_devices_count', 'devices_reporting_count',
                                                           'not_reporting_count', 'last_sync_date', 'volume_totals', 'array_depts',
                                                           'year_totals_array', 'array_indiv_proj_vals', '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){}
}