with('orgs', Organization::orderBy('org_name')->get()
->where('client_status', '=', 'A')
->where('organization_types_id', '!=', 7));
}
/**
* Display the specified data for the selected org with color defaulting to both
* and year, and timeline date defaulting to current year values.
* @param int $id
* @return \Illuminate\Http\Response
*/
public function show(Request $request, $org_id)
{
/*************************** BEGIN SPECIFIC TO SHOW FUNCTION ONLY **************************/
/*Set the default values for these session variables*/
$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');
$timeline_date = ('20' . $year . '-06-30');
/*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 FUNCTION ONLY **************************/
/*THIS IS JUST A FAKE NUMBER TO PUT SOME DATA INTO THE GAUGES*/
$fp_data = 99;
/************************** BEGIN GET DATE AND COUNT VARIABLES **************************/
$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();
$notReportingDateFormat = $gCalc->getNotReportingDateFormat();
$fiscal_days = $gCalc->getFiscalDays();
$last_sync_date = $gCalc->getLastSyncDate($yearTab, $org_id);
$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 AND COUNT VARIABLES **************************/
/*********************************** 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');
$volume_totals = DB::table($yearTab . '_meters as mtr')
->join($yearTab . '_floorplan_machines as fpm', 'fpm.proposed_serial_number', '=', 'mtr.serial_number')
->join($yearTab . '_floorplans as fp', 'fp.fp_id', '=', 'fpm.floorplans_id')
->join($yearTab . '_buildings as bldgs', 'bldgs.bldg_id', '=', 'fp.buildings_id')
->join($yearTab . '_organizations as org', 'bldgs.organizations_id', '=', 'org.org_id')
//->join('org_year_specific_data as org', 'bldgs.organizations_id', '=', 'org.org_id')
->joinSub($serNumDateMetersMax, 'serNumDateMetersMax', function($join){
$join->on('mtr.serial_number', '=', 'serNumDateMetersMax.serial_number');
$join->on('mtr.created_date', '=', 'serNumDateMetersMax.dt');
})
->select(DB::raw('fpm.budgeted_black as budget_blk, fpm.budgeted_color as budget_col,
fpm.commencement_black_meter, fpm.commencement_color_meter,
fpm.commencement_date as dev_commencement_date,
fpm.cpc_black, fpm.cpc_color, fpm.budgeted_black, fpm.budgeted_color,
mtr.black_meter as black_meter, mtr.color_meter as color_meter,
mtr.black_meter - fpm.commencement_black_meter as cons_blk,
mtr.color_meter - fpm.commencement_color_meter as cons_col,
mtr.black_meter * fpm.cpc_black as cons_blk_cost,
mtr.color_meter * fpm.cpc_color as cons_col_cost,
mtr.created_date, fpm.proposed_serial_number,
org.commencement_date as org_commencement_date,
bldgs.bldg_name'))
//->where('org.school_year', '=', $year)
->whereIn('fpm.proposed_serial_number', function($query) use($year, $yearTab, $org_id){
$query->select('fm.proposed_serial_number')
->from($yearTab . '_floorplan_machines as fm')
->join($yearTab . '_floorplans as fp', 'fp.fp_id', '=', 'fm.floorplans_id')
->join($yearTab . '_buildings as bldgs', 'bldgs.bldg_id', '=', 'fp.buildings_id')
->where('bldgs.organizations_id', '=', $org_id);
})->get();
//echo $volume_totals;
/*Variables to hold calculated data*/
$consumed_black = 0;
$consumed_color = 0;
$consumed_black_estimated = 0;
$consumed_black_estimated_cost = 0;
$consumed_color_estimated = 0;
$consumed_color_estimated_cost = 0;
/*I do not know if these are used, but since they are in stardoc, I am including them for now.*/
$consumed_black_cost = 0;
$consumed_color_cost = 0;
$budgeted_black_cost = 0;
$budgeted_color_cost = 0;
$budgeted_black = 0;
$budgeted_color = 0;
$org_commencement_date = '';
$device_commencement_date = '';
$org_fiscal_days = 0;
$cm_diff = 1;
$org_cm_diff = 1;
$last_meter_date = '';
$last_diff_date = '';
$last_diff = 0;
$projected_black = 0;
$projected_black_cost = 0;
$projected_color = 0;
$projected_color_cost = 0;
$projected_black_year = 0;
$projected_black_year_cost = 0;
$projected_color_year = 0;
$projected_color_year_cost = 0;
$over_under_budget_black = 0;
$over_under_budget_color = 0;
$over_under_budget_array = array();
$over_under_budget_bldg_array = array();
foreach($volume_totals as $row){
$org_commencement_date = strtotime($row->org_commencement_date);
$device_commencement_date = strtotime($row->dev_commencement_date);
/*Set the org commencment date*/
if ($row->org_commencement_date == NULL || $org_commencement_date < $startingDate) {
$org_commencement_date = $startingDate;
}
/*Set the commencement date for the device*/
if ($row->dev_commencement_date == NULL || $device_commencement_date < $org_commencement_date) {
$device_commencement_date = $org_commencement_date;
}
else{
$device_commencement_date = $row->dev_commencement_date;
}//end else
/*echo 'ending_date ' . $endingDate . '
';
echo 'device_commencement_date ' . $device_commencement_date . '
';
echo ($endingDate - $device_commencement_date) . '
';*/
/*Get the number of days between the device commencement date and the current date*/
$cm_diff = ($endingDate - $device_commencement_date);// /60/60/24;
/*Get the number of days between the org_commencement_date and the current date*/
$org_cm_diff = ($endingDate - $org_commencement_date) /60/60/24;
/*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;
}
/*Consumed black*/
if($row->commencement_black_meter == NULL){
$consumed_black += $row->black_meter;
$consumed_black_cost += $row->black_meter * $row->cpc_black;
}
else{
$consumed_black += ($row->black_meter - $row->commencement_black_meter);
$consumed_black_cost += ($row->black_meter - $row->commencement_black_meter) * $row->cpc_black;
}//end else
/*Consumed color*/
if($row->commencement_color_meter == NULL){
$consumed_color += $row->color_meter;
$consumed_color_cost = $row->color_meter * $row->cpc_color;
}
else{
$consumed_color += ($row->color_meter - $row->commencement_color_meter);
$consumed_color_cost = ($row->color_meter - $row->commencement_color_meter) * $row->cpc_color;
}//end else
/*Budgeted black and color*/
if($row->org_commencement_date == NULL || $org_commencement_date < $startingDate){
$budgeted_black += $row->budgeted_black;
$budgeted_color += $row->budgeted_color;
$budgeted_black_cost += ($row->budgeted_black * $row->cpc_black);
$budgeted_color_cost += ($row->budgeted_color * $row->cpc_color);
}
else{
$budgeted_black += ($row->budgeted_black / $fiscal_days) * $org_fiscal_days;
$budgeted_color += ($row->budgeted_color / $fiscal_days * $org_fiscal_days);
$budgeted_black_cost += ($row->budgeted_black / $fiscal_days) * $org_fiscal_days * $row->cpc_black;
$budgeted_color_cost += ($row->budgeted_color / $fiscal_days) * $org_fiscal_days * $row->cpc_color;
}//end else
// Get the number of days between the last meter read date and the commencement date
if ($row->created_date == NULL) {
$last_diff = 1;
}
else {
//$device_commencement_date = $device_commencement_date;
$last_meter_date = strtotime($row->created_date);
$last_diff_unix = ($last_meter_date - $device_commencement_date);
$last_diff = $last_diff_unix / 60/60/24;
if ($last_diff <= 0) {
$last_diff = 1;
}
}//end else
if ($cm_diff > 0) {
$consumed_black_estimated = $consumed_black / $cm_diff * $org_cm_diff;
$consumed_black_estimated_cost = $consumed_black_cost / $cm_diff * $org_cm_diff;
} else {
$consumed_black_estimated = $consumed_black;
$consumed_black_estimated_cost = $consumed_black_cost;
}
/*Computer projected amounts. Use commencement date if it exists.*/
if ($cm_diff > 0) {
if ($row->org_commencement_date == NULL || $org_commencement_date < $startingDate) {
$projected_black = $consumed_black / $last_diff * 345;
$projected_black_cost = $consumed_black_estimated_cost / $last_diff * 345;
$projected_color = $consumed_color / $last_diff * 345;
$projected_color_cost = $consumed_color_estimated_cost / $last_diff * 345;
$projected_black_year += $projected_black;
$projected_black_year_cost = $projected_black_cost * 1.035;
$projected_color_year = $projected_color;
$projected_color_year_cost = $projected_color_cost * 1.035;
/*$projected_source = "first";*/
}//end if
else {
$projected_black = $consumed_black / $cm_diff * min($org_fiscal_days, 345);
$projected_black_cost = $consumed_black_cost / $cm_diff * min($org_fiscal_days, 345);
$projected_color = $consumed_color / $cm_diff * min($org_fiscal_days, 345);
$projected_color_cost = $consumed_color_cost / $cm_diff * min($org_fiscal_days, 345);
$projected_black_year += ($consumed_black / $cm_diff * min($org_fiscal_days, 345));
$projected_black_year_cost = $consumed_black_cost / $cm_diff * min($org_fiscal_days, 345) * 1.035;
$projected_color_year = $consumed_color / $cm_diff * min($org_fiscal_days, 345);
$projected_color_year_cost = $consumed_color_cost / $cm_diff * min($org_fiscal_days, 345) * 1.035;
/*$projected_source = "second";*/
}
}//end else
/*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"; }
//If the device is not reporting then use the budgeted amounts for projected and next years black.
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 ($projected_black_year < ($budgeted_black / $org_fiscal_days * $fiscal_days)) {
$projected_black = $budgeted_black;
$projected_black_cost = $budgeted_black_cost;
$projected_black_year = $projected_black;
$projected_black_year_cost = $projected_black_cost * 1.035;
}
}
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 ($projected_color_year < ($budgeted_color / $org_fiscal_days * $fiscal_days)) {
$projected_color = $budgeted_color;
$projected_color_cost = $budgeted_color_cost;
$projected_color_year = $projected_color;
$projected_color_year_cost = $projected_color_cost * 1.035;
}
}
}
/******* BEGIN Add data to the $over_under_budget_bldg_arrays to display in the Black and Color gauge % *******/
/*SJH - 8/26/22 - Get the correct calculation for these*/
$over_under_budget_black = (round($projected_black_cost) - round($budgeted_black));
$over_under_budget_color = (round($projected_color_cost) - round($budgeted_color));
/*Add the over_under_budget values for the current building to the $over_under_budget_array, then add that array
to the $over_under_budget_bldg_array 2 dimensional array. These vals will be shown in the gauges for
each building for black and color.*/
$over_under_budget_array = array("black"=>$over_under_budget_black, "color"=>$over_under_budget_color);
echo $row->bldg_name;
$over_under_budget_bldg_array[$row->bldg_name] = $over_under_budget_array;
/******* END Add data to the $over_under_budget_bldg_arrays to display in the Black and Color gauge % *******/
dd($over_under_budget_bldg_array);
/*echo //'
today is ' . $today->format('y') . $today->format('m') . $today->format('d') .
'
timelineDate ' . session('timeline_date') .
'
endingDateFormat ' . $endingDateFormat .
'
org_commencement_date ' . $org_commencement_date . //date( 'Y-m-d H:i:s', $org_commencement_date) .
'
device_commencement_date ' . $device_commencement_date .
'
last_diff ' . $last_diff .
'
cm_diff ' . $cm_diff .
'
org_cm_diff ' . $org_cm_diff .
'
session_date ' . date('Y-m-d H:i:s', $_SESSION['session_date']) .
'
startingDateFormat ' . $startingDateFormat .
'
projected_black line 453 - - ' . $consumed_black / $cm_diff * min($org_fiscal_days, 345) .
'
starting_date ' . date( 'Y-m-d H:i:s', $startingDate) .
'
ending_date ' . date( 'Y-m-d H:i:s', $endingDate) .
'
ending_date UNIX ' . $endingDate .
'
consumed_black_device ' .($row->black_meter - $row->commencement_black_meter) .
'
consumed_black_sum / last_diff = ' . $consumed_black / $last_diff .
'
proposed_serial_number ' . $row->proposed_serial_number .
'
reporting ' . $reporting .
'
org_fiscal_days ' . $org_fiscal_days .
'
budgeted black ' . $budgeted_black .
'
fiscal_days (364) ' . $fiscal_days .
'
budgeted_black ' . $budgeted_black .
'
projected_black_year ' . $projected_black_year.
'
calc ' . $budgeted_black / $org_fiscal_days * $fiscal_days .
'
date_created ' . $row->created_date .
'
notReportingDate UNIX' . $notReportingDate .
'
notReportingDateFormat ' . $notReportingDateFormat . '
';*/
}//end foreach
/*********************************** END VOLUME_TOTALS ************************************/
return view('gauges.show', compact('fp_data', 'org_id', 'year', 'yearTab', 'timeline_date', 'color', 'toner_alert_count', 'service_needed_count',
'contract_devices_count', 'non_contract_devices_count', 'devices_reporting_count',
'not_reporting_count', 'last_sync_date', 'volume_totals', 'consumed_black', 'consumed_color',
'budgeted_black', 'budgeted_color', 'projected_black', 'projected_color', 'projected_black_year',
'startingDateFormat', 'endingDateFormat'))
->with('floorplans', Floorplan::all())
->with('bldgs', Building::orderBy('bldg_name')->get()->where('organizations_id', $org_id))
->with('years', SchoolYear::orderBy('year_id')->get())
->with('orgs', Organization::orderBy('org_name')->get()->where('client_status', 'A')->where('organization_types_id', '!=', 7));
}
/*Display the specified data for the selected org with color, year, and timeline date displaying the selected
values or defaults when at least 1 selection of these 3 was made.*/
public function show_by_org($org_id)
{
$year = session('year');
$color = 'both';
$yearTab = session('yearTab');
/*This is a string*/
$timeline_date = session('timeline_date');
if(null !== session('color')){
$color = session('color');
}
/*THIS IS JUST A FAKE NUMBER TO PUT SOME DATA INTO THE GAUGES*/
$fp_data = 99;
/************************** BEGIN GET DATE AND COUNT VARIABLES **************************/
/*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');
$gCalcOrg = new GaugesCalcs($timeline_date);
/*UNIX timestamps*/
$startingDate = $gCalcOrg->getStartingDate();
$endingDate = $gCalcOrg->getEndingDate();
/*These are string values for the starting and ending dates based on the value in the Timeline Date datepicker*/
$startingDateFormat = $gCalcOrg->getStartingDateFormat();
$endingDateFormat = $gCalcOrg->getEndingDateFormat();
$notReportingDate = $gCalcOrg->getNotReportingDate();//$endingDate - (15*60*60*24);
$notReportingDateFormat = $gCalcOrg->getNotReportingDateFormat();
$fiscal_days = $gCalcOrg->getFiscalDays();
$last_sync_date = $gCalcOrg->getLastSyncDate($yearTab, $org_id);
$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 AND COUNT VARIABLES **************************/
/*********************************** 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');
$volume_totals = DB::table($yearTab . '_meters as mtr')
->join($yearTab . '_floorplan_machines as fpm', 'fpm.proposed_serial_number', '=', 'mtr.serial_number')
->join($yearTab . '_floorplans as fp', 'fp.fp_id', '=', 'fpm.floorplans_id')
->join($yearTab . '_buildings as bldgs', 'bldgs.bldg_id', '=', 'fp.buildings_id')
//->join($yearTab . '_organizations as org', 'bldgs.organizations_id', '=', 'org.org_id')
->join('org_year_specific_data as org', 'bldgs.organizations_id', '=', 'org.org_id')
->joinSub($serNumDateMetersMax, 'serNumDateMetersMax', function($join){
$join->on('mtr.serial_number', '=', 'serNumDateMetersMax.serial_number');
$join->on('mtr.created_date', '=', 'serNumDateMetersMax.dt');
})
->select(DB::raw('fpm.budgeted_black as budget_blk, fpm.budgeted_color as budget_col,
fpm.commencement_black_meter, fpm.commencement_color_meter,
fpm.commencement_date as dev_commencement_date,
fpm.cpc_black, fpm.cpc_color, fpm.budgeted_black, fpm.budgeted_color,
mtr.black_meter as black_meter, mtr.color_meter as color_meter,
mtr.black_meter - fpm.commencement_black_meter as cons_blk,
mtr.color_meter - fpm.commencement_color_meter as cons_col,
mtr.black_meter * fpm.cpc_black as cons_blk_cost,
mtr.color_meter * fpm.cpc_color as cons_col_cost,
mtr.created_date, fpm.proposed_serial_number,
org.commencement_date as org_commencement_date'))
->whereIn('fpm.proposed_serial_number', function($query) use($year, $yearTab, $org_id){
$query->select('fm.proposed_serial_number')
->from($yearTab . '_floorplan_machines as fm')
->join($yearTab . '_floorplans as fp', 'fp.fp_id', '=', 'fm.floorplans_id')
->join($yearTab . '_buildings as bldgs', 'bldgs.bldg_id', '=', 'fp.buildings_id')
->where('bldgs.organizations_id', '=', $org_id);
})->get();
/*Variables to hold calculated data*/
$consumed_black = 0;
$consumed_color = 0;
$consumed_black_estimated = 0;
$consumed_black_estimated_cost = 0;
$consumed_color_estimated = 0;
$consumed_color_estimated_cost = 0;
/*I do not know if these are used, but since they are in stardoc, I am including them for now.*/
$consumed_black_cost = 0;
$consumed_color_cost = 0;
$budgeted_black_cost = 0;
$budgeted_color_cost = 0;
$budgeted_black = 0;
$budgeted_color = 0;
$org_commencement_date = '';
$device_commencement_date = '';
$org_fiscal_days = 0;
$cm_diff = 1;
$org_cm_diff = 1;
$last_meter_date = '';
$last_diff_date = '';
$last_diff = 0;
$projected_black = 0;
$projected_black_cost = 0;
$projected_color = 0;
$projected_color_cost = 0;
$projected_black_year = 0;
$projected_black_year_cost = 0;
$projected_color_year = 0;
$projected_color_year_cost = 0;
foreach($volume_totals as $row){
$org_commencement_date = strtotime($row->org_commencement_date);
$device_commencement_date = strtotime($row->dev_commencement_date);
/*Set the org commencment date*/
if ($row->org_commencement_date == NULL || $org_commencement_date < $startingDate) {
$org_commencement_date = $startingDate;
}
/*Set the commencement date for the device*/
if ($row->dev_commencement_date == NULL || $device_commencement_date < $org_commencement_date) {
$device_commencement_date = $org_commencement_date;
//$comm_date_used = "org_commencement_date_1";
}
else{
$device_commencement_date = $row->dev_commencement_date;
//$comm_date_used = "device_commencement_date";
}//end else
/*Get the number of days between the device commencement date and the current date*/
$cm_diff = ($endingDate - $device_commencement_date) /60/60/24;
/*Get the number of days between the org_commencement_date and the current date*/
$org_cm_diff = ($endingDate - $org_commencement_date) /60/60/24;
/*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;
}
/*Consumed black*/
if($row->commencement_black_meter == NULL){
$consumed_black += $row->black_meter;
$consumed_black_cost += $row->black_meter * $row->cpc_black;
}
else{
$consumed_black += ($row->black_meter - $row->commencement_black_meter);
$consumed_black_cost += ($row->black_meter - $row->commencement_black_meter) * $row->cpc_black;
}//end else
/*Consumed color*/
if($row->commencement_color_meter == NULL){
$consumed_color += $row->color_meter;
$consumed_color_cost = $row->color_meter * $row->cpc_color;
}
else{
$consumed_color += ($row->color_meter - $row->commencement_color_meter);
$consumed_color_cost = ($row->color_meter - $row->commencement_color_meter) * $row->cpc_color;
}//end else
/*Budgeted black and color*/
if($row->org_commencement_date == NULL || $org_commencement_date < $startingDate){
$budgeted_black += $row->budgeted_black;
$budgeted_color += $row->budgeted_color;
$budgeted_black_cost += ($row->budgeted_black * $row->cpc_black);
$budgeted_color_cost += ($row->budgeted_color * $row->cpc_color);
}
else{
$budgeted_black += ($row->budgeted_black / $fiscal_days) * $org_fiscal_days;
$budgeted_color += ($row->budgeted_color / $fiscal_days * $org_fiscal_days);
$budgeted_black_cost += ($row->budgeted_black / $fiscal_days) * $org_fiscal_days * $row->cpc_black;
$budgeted_color_cost += ($row->budgeted_color / $fiscal_days) * $org_fiscal_days * $row->cpc_color;
}//end else
// Get the number of days between the last meter read date and the commencement date
if ($row->created_date == NULL) {
$last_diff = 1;
}
else {
//$device_commencement_date = $device_commencement_date;
$last_meter_date = strtotime($row->created_date);
$last_diff_unix = ($last_meter_date - $device_commencement_date);
$last_diff = $last_diff_unix / 60/60/24;
if ($last_diff <= 0) {
$last_diff = 1;
}
}//end else
if ($cm_diff > 0) {
$consumed_black_estimated = $consumed_black / $cm_diff * $org_cm_diff;
$consumed_black_estimated_cost = $consumed_black_cost / $cm_diff * $org_cm_diff;
} else {
$consumed_black_estimated = $consumed_black;
$consumed_black_estimated_cost = $consumed_black_cost;
}
/*Computer projected amounts. Use commencement date if it exists.*/
if ($cm_diff > 0) {
if ($row->org_commencement_date == NULL || $org_commencement_date < $startingDate) {
$projected_black = $consumed_black / $last_diff * 345;
$projected_black_cost = $consumed_black_estimated_cost / $last_diff * 345;
$projected_color = $consumed_color / $last_diff * 345;
$projected_color_cost = $consumed_color_estimated_cost / $last_diff * 345;
$projected_black_year += $projected_black;
$projected_black_year_cost = $projected_black_cost * 1.035;
$projected_color_year = $projected_color;
$projected_color_year_cost = $projected_color_cost * 1.035;
/*$projected_source = "first";*/
}//end if
else {
$projected_black = $consumed_black / $cm_diff * min($org_fiscal_days, 345);
$projected_black_cost = $consumed_black_cost / $cm_diff * min($org_fiscal_days, 345);
$projected_color = $consumed_color / $cm_diff * min($org_fiscal_days, 345);
$projected_color_cost = $consumed_color_cost / $cm_diff * min($org_fiscal_days, 345);
$projected_black_year += ($consumed_black / $cm_diff * min($org_fiscal_days, 345));
$projected_black_year_cost = $consumed_black_cost / $cm_diff * min($org_fiscal_days, 345) * 1.035;
$projected_color_year = $consumed_color / $cm_diff * min($org_fiscal_days, 345);
$projected_color_year_cost = $consumed_color_cost / $cm_diff * min($org_fiscal_days, 345) * 1.035;
/*$projected_source = "second";*/
}
}//end else
/*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"; }
//If the device is not reporting then use the budgeted amounts for projected and next years black.
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 ($projected_black_year < ($budgeted_black / $org_fiscal_days * $fiscal_days)) {
$projected_black = $budgeted_black;
$projected_black_cost = $budgeted_black_cost;
$projected_black_year = $projected_black;
$projected_black_year_cost = $projected_black_cost * 1.035;
}
}
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 ($projected_color_year < ($budgeted_color / $org_fiscal_days * $fiscal_days)) {
$projected_color = $budgeted_color;
$projected_color_cost = $budgeted_color_cost;
$projected_color_year = $projected_color;
$projected_color_year_cost = $projected_color_cost * 1.035;
}
}
}
}//end foreach
/*********************************** END VOLUME_TOTALS ************************************/
//session_unset();
//session_destroy();
return view('gauges.show', compact('fp_data', 'org_id', 'year', 'yearTab', 'timeline_date', 'color', 'toner_alert_count', 'service_needed_count',
'contract_devices_count', 'non_contract_devices_count', 'devices_reporting_count',
'not_reporting_count', 'last_sync_date', 'volume_totals', 'consumed_black', 'consumed_color',
'budgeted_black', 'budgeted_color', 'projected_black', 'projected_color', 'projected_black_year',
'startingDateFormat', 'endingDateFormat'))
->with('floorplans', Floorplan::all())
->with('bldgs', Building::orderBy('bldg_name')->get()->where('organizations_id', $org_id))
->with('years', SchoolYear::orderBy('year_id')->get())
->with('orgs', Organization::orderBy('org_name')->where('organization_types_id', '!=', 7)->get()->where('client_status', 'A'));
}
public function show_by_year($year)
{
$org_id = session('org_id');
$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');
//$timeline_date = ('20' . $year . '-06-30');
$timeline_date = ('20' . $year . '-06-30 24:59:59');
/************************** BEGIN from show**************************/
/*Set the default values for these session variables*/
/*Take the last 2 digits of the current year, which happen to start at the 2 index.*/
if(null !== session('color')){
$color = session('color');
}
else{
$color = 'both';
}
$org_fiscal_days = 0;
$cm_diff = 1;
$org_cm_diff = 1;
session(['org_id' => $org_id]);
session(['year' => $year]);
session(['color' => $color]);
session(['yearTab' => $yearTab]);
session(['timeline_date' => $timeline_date]);
/*echo session('org_id') . '
';
echo session('year') . '
';
echo session('color') . '
';
echo session('yearTab') . '
';
echo session('timeline_date') . '
';*/
/*THIS IS JUST A FAKE NUMBER TO PUT SOME DATA INTO THE GAUGES*/
$fp_data = 99;
/************************** BEGIN GET DATE AND COUNT VARIABLES **************************/
$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();
$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);
$last_sync_date = $gCalc->getLastSyncDate($yearTab, $org_id);
/************************** END GET DATE AND COUNT VARIABLES **************************/
/*********************************** 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');
$volume_totals = DB::table($yearTab . '_meters as mtr')
->join($yearTab . '_floorplan_machines as fpm', 'fpm.proposed_serial_number', '=', 'mtr.serial_number')
->join($yearTab . '_floorplans as fp', 'fp.fp_id', '=', 'fpm.floorplans_id')
->join($yearTab . '_buildings as bldgs', 'bldgs.bldg_id', '=', 'fp.buildings_id')
//->join($yearTab . '_organizations as org', 'bldgs.organizations_id', '=', 'org.org_id')
->join('org_year_specific_data as org', 'bldgs.organizations_id', '=', 'org.org_id')
->joinSub($serNumDateMetersMax, 'serNumDateMetersMax', function($join){
$join->on('mtr.serial_number', '=', 'serNumDateMetersMax.serial_number');
$join->on('mtr.created_date', '=', 'serNumDateMetersMax.dt');
})
->select(DB::raw('fpm.budgeted_black as budget_blk, fpm.budgeted_color as budget_col,
fpm.commencement_black_meter, fpm.commencement_color_meter,
fpm.commencement_date as dev_commencement_date,
fpm.cpc_black, fpm.cpc_color, fpm.budgeted_black, fpm.budgeted_color,
mtr.black_meter as black_meter, mtr.color_meter as color_meter,
mtr.black_meter - fpm.commencement_black_meter as cons_blk,
mtr.color_meter - fpm.commencement_color_meter as cons_col,
mtr.black_meter * fpm.cpc_black as cons_blk_cost,
mtr.color_meter * fpm.cpc_color as cons_col_cost,
mtr.created_date, fpm.proposed_serial_number,
org.commencement_date as org_commencement_date'))
->whereIn('fpm.proposed_serial_number', function($query) use($year, $yearTab, $org_id){
$query->select('fm.proposed_serial_number')
->from($yearTab . '_floorplan_machines as fm')
->join($yearTab . '_floorplans as fp', 'fp.fp_id', '=', 'fm.floorplans_id')
->join($yearTab . '_buildings as bldgs', 'bldgs.bldg_id', '=', 'fp.buildings_id')
->where('bldgs.organizations_id', '=', $org_id);
})->get();
/*echo $volume_totals;
echo $device_commencement_date;
echo $endingDate;*/
/*Variables to hold calculated data*/
$consumed_black = 0;
$consumed_color = 0;
$consumed_black_estimated = 0;
$consumed_black_estimated_cost = 0;
$consumed_color_estimated = 0;
$consumed_color_estimated_cost = 0;
/*I do not know if these are used, but since they are in stardoc, I am including them for now.*/
$consumed_black_cost = 0;
$consumed_color_cost = 0;
$budgeted_black_cost = 0;
$budgeted_color_cost = 0;
$budgeted_black = 0;
$budgeted_color = 0;
$org_commencement_date = '';
$device_commencement_date = '';
$org_fiscal_days = 0;
$cm_diff = 1;
$org_cm_diff = 1;
$last_meter_date = '';
$last_diff_date = '';
$last_diff = 0;
$projected_black = 0;
$projected_black_cost = 0;
$projected_color = 0;
$projected_color_cost = 0;
$projected_black_year = 0;
$projected_black_year_cost = 0;
$projected_color_year = 0;
$projected_color_year_cost = 0;
foreach($volume_totals as $row){
$org_commencement_date = strtotime($row->org_commencement_date);
//$device_commencement_date = strtotime($row->dev_commencement_date);
/*Set the org commencment date*/
if ($row->org_commencement_date == NULL || $org_commencement_date < $startingDate) {
$org_commencement_date = $startingDate;
}
/*Set the commencement date for the device*/
if ($row->dev_commencement_date == NULL || $device_commencement_date < $org_commencement_date) {
$device_commencement_date = $org_commencement_date;
//$comm_date_used = "org_commencement_date_1";
}
else{
$device_commencement_date = strtotime($row->dev_commencement_date);
//$comm_date_used = "device_commencement_date";
}//end else
/*Get the number of days between the device commencement date and the current date*/
$cm_diff = (($endingDate - $device_commencement_date)/60/60/24);
/*Get the number of days between the org_commencement_date and the current date*/
$org_cm_diff = ($endingDate - $org_commencement_date) /60/60/24;
/*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;
}
/*Consumed black*/
if($row->commencement_black_meter == NULL){
$consumed_black += $row->black_meter;
$consumed_black_cost += $row->black_meter * $row->cpc_black;
}
else{
$consumed_black += ($row->black_meter - $row->commencement_black_meter);
$consumed_black_cost += ($row->black_meter - $row->commencement_black_meter) * $row->cpc_black;
}//end else
/*Consumed color*/
if($row->commencement_color_meter == NULL){
$consumed_color += $row->color_meter;
$consumed_color_cost = $row->color_meter * $row->cpc_color;
}
else{
$consumed_color += ($row->color_meter - $row->commencement_color_meter);
$consumed_color_cost = ($row->color_meter - $row->commencement_color_meter) * $row->cpc_color;
}//end else
/*Budgeted black and color*/
if($row->org_commencement_date == NULL || $org_commencement_date < $startingDate){
$budgeted_black += $row->budgeted_black;
$budgeted_color += $row->budgeted_color;
$budgeted_black_cost += ($row->budgeted_black * $row->cpc_black);
$budgeted_color_cost += ($row->budgeted_color * $row->cpc_color);
}
else{
$budgeted_black += ($row->budgeted_black / $fiscal_days) * $org_fiscal_days;
$budgeted_color += ($row->budgeted_color / $fiscal_days * $org_fiscal_days);
$budgeted_black_cost += ($row->budgeted_black / $fiscal_days) * $org_fiscal_days * $row->cpc_black;
$budgeted_color_cost += ($row->budgeted_color / $fiscal_days) * $org_fiscal_days * $row->cpc_color;
}//end else
// Get the number of days between the last meter read date and the commencement date
if ($row->created_date == NULL) {
$last_diff = 1;
}
else {
//$device_commencement_date = $device_commencement_date;
$last_meter_date = strtotime($row->created_date);
$last_diff_unix = ($last_meter_date - $device_commencement_date);
$last_diff = $last_diff_unix / 60/60/24;
if ($last_diff <= 0) {
$last_diff = 1;
}
}//end else
if ($cm_diff > 0) {
$consumed_black_estimated = $consumed_black / $cm_diff * $org_cm_diff;
$consumed_black_estimated_cost = $consumed_black_cost / $cm_diff * $org_cm_diff;
} else {
$consumed_black_estimated = $consumed_black;
$consumed_black_estimated_cost = $consumed_black_cost;
}
/*Computer projected amounts. Use commencement date if it exists.*/
if ($cm_diff > 0) {
if ($row->org_commencement_date == NULL || $org_commencement_date < $startingDate) {
$projected_black = $consumed_black / $last_diff * 345;
$projected_black_cost = $consumed_black_estimated_cost / $last_diff * 345;
$projected_color = $consumed_color / $last_diff * 345;
$projected_color_cost = $consumed_color_estimated_cost / $last_diff * 345;
$projected_black_year += $projected_black;
$projected_black_year_cost = $projected_black_cost * 1.035;
$projected_color_year = $projected_color;
$projected_color_year_cost = $projected_color_cost * 1.035;
/*$projected_source = "first";*/
}//end if
else {
$projected_black = $consumed_black / $cm_diff * min($org_fiscal_days, 345);
$projected_black_cost = $consumed_black_cost / $cm_diff * min($org_fiscal_days, 345);
$projected_color = $consumed_color / $cm_diff * min($org_fiscal_days, 345);
$projected_color_cost = $consumed_color_cost / $cm_diff * min($org_fiscal_days, 345);
$projected_black_year += ($consumed_black / $cm_diff * min($org_fiscal_days, 345));
$projected_black_year_cost = $consumed_black_cost / $cm_diff * min($org_fiscal_days, 345) * 1.035;
$projected_color_year = $consumed_color / $cm_diff * min($org_fiscal_days, 345);
$projected_color_year_cost = $consumed_color_cost / $cm_diff * min($org_fiscal_days, 345) * 1.035;
/*$projected_source = "second";*/
}
}//end else
/*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"; }
//If the device is not reporting then use the budgeted amounts for projected and next years black.
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 ($projected_black_year < ($budgeted_black / $org_fiscal_days * $fiscal_days)) {
$projected_black = $budgeted_black;
$projected_black_cost = $budgeted_black_cost;
$projected_black_year = $projected_black;
$projected_black_year_cost = $projected_black_cost * 1.035;
}
}
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 ($projected_color_year < ($budgeted_color / $org_fiscal_days * $fiscal_days)) {
$projected_color = $budgeted_color;
$projected_color_cost = $budgeted_color_cost;
$projected_color_year = $projected_color;
$projected_color_year_cost = $projected_color_cost * 1.035;
}
}
}
}//end foreach
/*********************************** END VOLUME_TOTALS ************************************/
//session_unset();
//session_destroy();
return view('gauges.show', compact('fp_data', 'org_id', 'year', 'yearTab', 'timeline_date', 'color', 'toner_alert_count', 'service_needed_count',
'contract_devices_count', 'non_contract_devices_count', 'devices_reporting_count',
'not_reporting_count', 'last_sync_date', 'volume_totals', 'consumed_black', 'consumed_color',
'budgeted_black', 'budgeted_color', 'projected_black', 'projected_color', 'projected_black_year',
'startingDateFormat', 'endingDateFormat'))
->with('floorplans', Floorplan::all())
->with('bldgs', Building::orderBy('bldg_name')->get()->where('organizations_id', $org_id))
->with('years', SchoolYear::orderBy('year_id')->get())
->with('orgs', Organization::orderBy('org_name')->where('organization_types_id', '!=', 7)->where('client_status', 'A')->get());
}
public function show_by_timeline_date($timeline_date)
{
$org_id = session('org_id');
$color = 'both';
/*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;
$org_fiscal_days = 0;
$cm_diff = 1;
$org_cm_diff = 1;
/************************** BEGIN from show**************************/
if(null !== session('color')){
$color = session('color');
}
session(['org_id' => $org_id]);
session(['year' => $year]);
session(['color' => $color]);
session(['yearTab' => $yearTab]);
session(['timeline_date' => $timeline_date]);
/*THIS IS JUST A FAKE NUMBER TO PUT SOME DATA INTO THE GAUGES*/
$fp_data = 99;
/************************** BEGIN GET DATE AND COUNT VARIABLES **************************/
$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);
$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 AND COUNT VARIABLES **************************/
/*********************************** 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');
$volume_totals = DB::table($yearTab . '_meters as mtr')
->join($yearTab . '_floorplan_machines as fpm', 'fpm.proposed_serial_number', '=', 'mtr.serial_number')
->join($yearTab . '_floorplans as fp', 'fp.fp_id', '=', 'fpm.floorplans_id')
->join($yearTab . '_buildings as bldgs', 'bldgs.bldg_id', '=', 'fp.buildings_id')
->join($yearTab . '_organizations as org', 'bldgs.organizations_id', '=', 'org.org_id')
->joinSub($serNumDateMetersMax, 'serNumDateMetersMax', function($join){
$join->on('mtr.serial_number', '=', 'serNumDateMetersMax.serial_number');
$join->on('mtr.created_date', '=', 'serNumDateMetersMax.dt');
})
->select(DB::raw('fpm.budgeted_black as budget_blk, fpm.budgeted_color as budget_col,
fpm.commencement_black_meter, fpm.commencement_color_meter,
fpm.commencement_date as dev_commencement_date,
fpm.cpc_black, fpm.cpc_color, fpm.budgeted_black, fpm.budgeted_color,
mtr.black_meter as black_meter, mtr.color_meter as color_meter,
mtr.black_meter - fpm.commencement_black_meter as cons_blk,
mtr.color_meter - fpm.commencement_color_meter as cons_col,
mtr.black_meter * fpm.cpc_black as cons_blk_cost,
mtr.color_meter * fpm.cpc_color as cons_col_cost,
mtr.created_date, fpm.proposed_serial_number,
org.commencement_date as org_commencement_date'))
->whereIn('fpm.proposed_serial_number', function($query) use($year, $yearTab, $org_id){
$query->select('fm.proposed_serial_number')
->from($yearTab . '_floorplan_machines as fm')
->join($yearTab . '_floorplans as fp', 'fp.fp_id', '=', 'fm.floorplans_id')
->join($yearTab . '_buildings as bldgs', 'bldgs.bldg_id', '=', 'fp.buildings_id')
->where('bldgs.organizations_id', '=', $org_id);
})->get();
/*Variables to hold calculated data*/
$consumed_black = 0;
$consumed_color = 0;
$consumed_black_estimated = 0;
$consumed_black_estimated_cost = 0;
$consumed_color_estimated = 0;
$consumed_color_estimated_cost = 0;
/*I do not know if these are used, but since they are in stardoc, I am including them for now.*/
$consumed_black_cost = 0;
$consumed_color_cost = 0;
$budgeted_black_cost = 0;
$budgeted_color_cost = 0;
$budgeted_black = 0;
$budgeted_color = 0;
$org_commencement_date = '';
$device_commencement_date = '';
$org_fiscal_days = 0;
$cm_diff = 1;
$org_cm_diff = 1;
$last_meter_date = '';
$last_diff_date = '';
$last_diff = 0;
$projected_black = 0;
$projected_black_cost = 0;
$projected_color = 0;
$projected_color_cost = 0;
$projected_black_year = 0;
$projected_black_year_cost = 0;
$projected_color_year = 0;
$projected_color_year_cost = 0;
foreach($volume_totals as $row){
$org_commencement_date = strtotime($row->org_commencement_date);
//$device_commencement_date = strtotime($row->dev_commencement_date);
/*Set the org commencment date*/
if ($row->org_commencement_date == NULL || $org_commencement_date < $startingDate) {
$org_commencement_date = $startingDate;
}
/*Set the commencement date for the device*/
if ($row->dev_commencement_date == NULL || $device_commencement_date < $org_commencement_date) {
$device_commencement_date = $org_commencement_date;
//$comm_date_used = "org_commencement_date_1";
}
else{
$device_commencement_date = strtotime($row->dev_commencement_date);
//$comm_date_used = "device_commencement_date";
}//end else
/*Get the number of days between the device commencement date and the current date*/
$cm_diff = ($endingDate - $device_commencement_date) /60/60/24;
/*Get the number of days between the org_commencement_date and the current date*/
$org_cm_diff = ($endingDate - $org_commencement_date) /60/60/24;
/*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;
}
/*Consumed black*/
if($row->commencement_black_meter == NULL){
$consumed_black += $row->black_meter;
$consumed_black_cost += $row->black_meter * $row->cpc_black;
}
else{
$consumed_black += ($row->black_meter - $row->commencement_black_meter);
$consumed_black_cost += ($row->black_meter - $row->commencement_black_meter) * $row->cpc_black;
}//end else
/*Consumed color*/
if($row->commencement_color_meter == NULL){
$consumed_color += $row->color_meter;
$consumed_color_cost = $row->color_meter * $row->cpc_color;
}
else{
$consumed_color += ($row->color_meter - $row->commencement_color_meter);
$consumed_color_cost = ($row->color_meter - $row->commencement_color_meter) * $row->cpc_color;
}//end else
/*Budgeted black and color*/
if($row->org_commencement_date == NULL || $org_commencement_date < $startingDate){
$budgeted_black += $row->budgeted_black;
$budgeted_color += $row->budgeted_color;
$budgeted_black_cost += ($row->budgeted_black * $row->cpc_black);
$budgeted_color_cost += ($row->budgeted_color * $row->cpc_color);
}
else{
$budgeted_black += ($row->budgeted_black / $fiscal_days) * $org_fiscal_days;
$budgeted_color += ($row->budgeted_color / $fiscal_days * $org_fiscal_days);
$budgeted_black_cost += ($row->budgeted_black / $fiscal_days) * $org_fiscal_days * $row->cpc_black;
$budgeted_color_cost += ($row->budgeted_color / $fiscal_days) * $org_fiscal_days * $row->cpc_color;
}//end else
// Get the number of days between the last meter read date and the commencement date
if ($row->created_date == NULL) {
$last_diff = 1;
}
else {
//$device_commencement_date = $device_commencement_date;
$last_meter_date = strtotime($row->created_date);
$last_diff_unix = ($last_meter_date - $device_commencement_date);
$last_diff = $last_diff_unix / 60/60/24;
if ($last_diff <= 0) {
$last_diff = 1;
}
}//end else
if ($cm_diff > 0) {
$consumed_black_estimated = $consumed_black / $cm_diff * $org_cm_diff;
$consumed_black_estimated_cost = $consumed_black_cost / $cm_diff * $org_cm_diff;
} else {
$consumed_black_estimated = $consumed_black;
$consumed_black_estimated_cost = $consumed_black_cost;
}
/*Computer projected amounts. Use commencement date if it exists.*/
if ($cm_diff > 0) {
if ($row->org_commencement_date == NULL || $org_commencement_date < $startingDate) {
$projected_black = $consumed_black / $last_diff * 345;
$projected_black_cost = $consumed_black_estimated_cost / $last_diff * 345;
$projected_color = $consumed_color / $last_diff * 345;
$projected_color_cost = $consumed_color_estimated_cost / $last_diff * 345;
$projected_black_year += $projected_black;
$projected_black_year_cost = $projected_black_cost * 1.035;
$projected_color_year = $projected_color;
$projected_color_year_cost = $projected_color_cost * 1.035;
/*$projected_source = "first";*/
}//end if
else {
$projected_black = $consumed_black / $cm_diff * min($org_fiscal_days, 345);
$projected_black_cost = $consumed_black_cost / $cm_diff * min($org_fiscal_days, 345);
$projected_color = $consumed_color / $cm_diff * min($org_fiscal_days, 345);
$projected_color_cost = $consumed_color_cost / $cm_diff * min($org_fiscal_days, 345);
$projected_black_year += ($consumed_black / $cm_diff * min($org_fiscal_days, 345));
$projected_black_year_cost = $consumed_black_cost / $cm_diff * min($org_fiscal_days, 345) * 1.035;
$projected_color_year = $consumed_color / $cm_diff * min($org_fiscal_days, 345);
$projected_color_year_cost = $consumed_color_cost / $cm_diff * min($org_fiscal_days, 345) * 1.035;
/*$projected_source = "second";*/
}
}//end else
/*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"; }
//If the device is not reporting then use the budgeted amounts for projected and next years black.
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 ($projected_black_year < ($budgeted_black / $org_fiscal_days * $fiscal_days)) {
$projected_black = $budgeted_black;
$projected_black_cost = $budgeted_black_cost;
$projected_black_year = $projected_black;
$projected_black_year_cost = $projected_black_cost * 1.035;
}
}
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 ($projected_color_year < ($budgeted_color / $org_fiscal_days * $fiscal_days)) {
$projected_color = $budgeted_color;
$projected_color_cost = $budgeted_color_cost;
$projected_color_year = $projected_color;
$projected_color_year_cost = $projected_color_cost * 1.035;
}
}
}
}//end foreach
/*********************************** END VOLUME_TOTALS ************************************/
session_unset();
//session_destroy();
return view('gauges.show', compact('fp_data', 'org_id', 'year', 'yearTab', 'timeline_date', 'color', 'toner_alert_count', 'service_needed_count',
'contract_devices_count', 'non_contract_devices_count', 'devices_reporting_count',
'not_reporting_count', 'last_sync_date', 'volume_totals', 'consumed_black', 'consumed_color',
'budgeted_black', 'budgeted_color', 'projected_black', 'projected_color', 'projected_black_year',
'startingDateFormat', 'endingDateFormat'))
->with('floorplans', Floorplan::all())
->with('bldgs', Building::orderBy('bldg_name')->get()->where('organizations_id', $org_id))
->with('years', SchoolYear::orderBy('year_id')->get())
->with('orgs', Organization::orderBy('org_name')->where('organization_types_id', '!=', 7)->where('client_status', 'A')->get());
}
public function show_by_color($color)
{
session(['color' => $color]);
$org_id = session('org_id');
$year = session('year');
$yearTab = session('yearTab');
$timeline_date = session('timeline_date');
/*THIS IS JUST A FAKE NUMBER TO PUT SOME DATA INTO THE GAUGES*/
$fp_data = 99;
/************************** BEGIN GET DATE VARIABLES **************************/
$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);
$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 **************************/
/*********************************** 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');
$volume_totals = DB::table($yearTab . '_meters as mtr')
->join($yearTab . '_floorplan_machines as fpm', 'fpm.proposed_serial_number', '=', 'mtr.serial_number')
->join($yearTab . '_floorplans as fp', 'fp.fp_id', '=', 'fpm.floorplans_id')
->join($yearTab . '_buildings as bldgs', 'bldgs.bldg_id', '=', 'fp.buildings_id')
->join($yearTab . '_organizations as org', 'bldgs.organizations_id', '=', 'org.org_id')
->joinSub($serNumDateMetersMax, 'serNumDateMetersMax', function($join){
$join->on('mtr.serial_number', '=', 'serNumDateMetersMax.serial_number');
$join->on('mtr.created_date', '=', 'serNumDateMetersMax.dt');
})
->select(DB::raw('fpm.budgeted_black as budget_blk, fpm.budgeted_color as budget_col,
fpm.commencement_black_meter, fpm.commencement_color_meter,
fpm.commencement_date as dev_commencement_date,
fpm.cpc_black, fpm.cpc_color, fpm.budgeted_black, fpm.budgeted_color,
mtr.black_meter as black_meter, mtr.color_meter as color_meter,
mtr.black_meter - fpm.commencement_black_meter as cons_blk,
mtr.color_meter - fpm.commencement_color_meter as cons_col,
mtr.black_meter * fpm.cpc_black as cons_blk_cost,
mtr.color_meter * fpm.cpc_color as cons_col_cost,
mtr.created_date, fpm.proposed_serial_number,
org.commencement_date as org_commencement_date'))
->whereIn('fpm.proposed_serial_number', function($query) use($year, $yearTab, $org_id){
$query->select('fm.proposed_serial_number')
->from($yearTab . '_floorplan_machines as fm')
->join($yearTab . '_floorplans as fp', 'fp.fp_id', '=', 'fm.floorplans_id')
->join($yearTab . '_buildings as bldgs', 'bldgs.bldg_id', '=', 'fp.buildings_id')
->where('bldgs.organizations_id', '=', $org_id);
})->get();
/*Variables to hold calculated data*/
$consumed_black = 0;
$consumed_color = 0;
$consumed_black_estimated = 0;
$consumed_black_estimated_cost = 0;
$consumed_color_estimated = 0;
$consumed_color_estimated_cost = 0;
/*I do not know if these are used, but since they are in stardoc, I am including them for now.*/
$consumed_black_cost = 0;
$consumed_color_cost = 0;
$budgeted_black_cost = 0;
$budgeted_color_cost = 0;
$budgeted_black = 0;
$budgeted_color = 0;
$org_commencement_date = '';
$device_commencement_date = '';
$org_fiscal_days = 0;
$cm_diff = 1;
$org_cm_diff = 1;
$last_meter_date = '';
$last_diff_date = '';
$last_diff = 0;
$projected_black = 0;
$projected_black_cost = 0;
$projected_color = 0;
$projected_color_cost = 0;
$projected_black_year = 0;
$projected_black_year_cost = 0;
$projected_color_year = 0;
$projected_color_year_cost = 0;
foreach($volume_totals as $row){
$org_commencement_date = strtotime($row->org_commencement_date);
//$device_commencement_date = strtotime($row->dev_commencement_date);
/*Set the org commencment date*/
if ($row->org_commencement_date == NULL || $org_commencement_date < $startingDate) {
$org_commencement_date = $startingDate;
}
/*Set the commencement date for the device*/
if ($row->dev_commencement_date == NULL || $device_commencement_date < $org_commencement_date) {
$device_commencement_date = $org_commencement_date;
//$comm_date_used = "org_commencement_date_1";
}
else{
$device_commencement_date = strtotime($row->dev_commencement_date);
//$comm_date_used = "device_commencement_date";
}//end else
/*Get the number of days between the device commencement date and the current date*/
$cm_diff = ($endingDate - $device_commencement_date) /60/60/24;
/*Get the number of days between the org_commencement_date and the current date*/
$org_cm_diff = ($endingDate - $org_commencement_date) /60/60/24;
/*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;
}
/*Consumed black*/
if($row->commencement_black_meter == NULL){
$consumed_black += $row->black_meter;
$consumed_black_cost += $row->black_meter * $row->cpc_black;
}
else{
$consumed_black += ($row->black_meter - $row->commencement_black_meter);
$consumed_black_cost += ($row->black_meter - $row->commencement_black_meter) * $row->cpc_black;
}//end else
/*Consumed color*/
if($row->commencement_color_meter == NULL){
$consumed_color += $row->color_meter;
$consumed_color_cost = $row->color_meter * $row->cpc_color;
}
else{
$consumed_color += ($row->color_meter - $row->commencement_color_meter);
$consumed_color_cost = ($row->color_meter - $row->commencement_color_meter) * $row->cpc_color;
}//end else
/*Budgeted black and color*/
if($row->org_commencement_date == NULL || $org_commencement_date < $startingDate){
$budgeted_black += $row->budgeted_black;
$budgeted_color += $row->budgeted_color;
$budgeted_black_cost += ($row->budgeted_black * $row->cpc_black);
$budgeted_color_cost += ($row->budgeted_color * $row->cpc_color);
}
else{
$budgeted_black += ($row->budgeted_black / $fiscal_days) * $org_fiscal_days;
$budgeted_color += ($row->budgeted_color / $fiscal_days * $org_fiscal_days);
$budgeted_black_cost += ($row->budgeted_black / $fiscal_days) * $org_fiscal_days * $row->cpc_black;
$budgeted_color_cost += ($row->budgeted_color / $fiscal_days) * $org_fiscal_days * $row->cpc_color;
}//end else
// Get the number of days between the last meter read date and the commencement date
if ($row->created_date == NULL) {
$last_diff = 1;
}
else {
//$device_commencement_date = $device_commencement_date;
$last_meter_date = strtotime($row->created_date);
$last_diff_unix = ($last_meter_date - $device_commencement_date);
$last_diff = $last_diff_unix / 60/60/24;
if ($last_diff <= 0) {
$last_diff = 1;
}
}//end else
if ($cm_diff > 0) {
$consumed_black_estimated = $consumed_black / $cm_diff * $org_cm_diff;
$consumed_black_estimated_cost = $consumed_black_cost / $cm_diff * $org_cm_diff;
} else {
$consumed_black_estimated = $consumed_black;
$consumed_black_estimated_cost = $consumed_black_cost;
}
/*Computer projected amounts. Use commencement date if it exists.*/
if ($cm_diff > 0) {
if ($row->org_commencement_date == NULL || $org_commencement_date < $startingDate) {
$projected_black = $consumed_black / $last_diff * 345;
$projected_black_cost = $consumed_black_estimated_cost / $last_diff * 345;
$projected_color = $consumed_color / $last_diff * 345;
$projected_color_cost = $consumed_color_estimated_cost / $last_diff * 345;
$projected_black_year += $projected_black;
$projected_black_year_cost = $projected_black_cost * 1.035;
$projected_color_year = $projected_color;
$projected_color_year_cost = $projected_color_cost * 1.035;
/*$projected_source = "first";*/
}//end if
else {
$projected_black = $consumed_black / $cm_diff * min($org_fiscal_days, 345);
$projected_black_cost = $consumed_black_cost / $cm_diff * min($org_fiscal_days, 345);
$projected_color = $consumed_color / $cm_diff * min($org_fiscal_days, 345);
$projected_color_cost = $consumed_color_cost / $cm_diff * min($org_fiscal_days, 345);
$projected_black_year += ($consumed_black / $cm_diff * min($org_fiscal_days, 345));
$projected_black_year_cost = $consumed_black_cost / $cm_diff * min($org_fiscal_days, 345) * 1.035;
$projected_color_year = $consumed_color / $cm_diff * min($org_fiscal_days, 345);
$projected_color_year_cost = $consumed_color_cost / $cm_diff * min($org_fiscal_days, 345) * 1.035;
/*$projected_source = "second";*/
}
}//end else
/*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"; }
//If the device is not reporting then use the budgeted amounts for projected and next years black.
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 ($projected_black_year < ($budgeted_black / $org_fiscal_days * $fiscal_days)) {
$projected_black = $budgeted_black;
$projected_black_cost = $budgeted_black_cost;
$projected_black_year = $projected_black;
$projected_black_year_cost = $projected_black_cost * 1.035;
}
}
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 ($projected_color_year < ($budgeted_color / $org_fiscal_days * $fiscal_days)) {
$projected_color = $budgeted_color;
$projected_color_cost = $budgeted_color_cost;
$projected_color_year = $projected_color;
$projected_color_year_cost = $projected_color_cost * 1.035;
}
}
}
}//end foreach
/*********************************** END VOLUME_TOTALS ************************************/
session_unset();
//session_destroy();
return view('gauges.show', compact('fp_data', 'org_id', 'year', 'yearTab', 'timeline_date', 'color', 'toner_alert_count', 'service_needed_count',
'contract_devices_count', 'non_contract_devices_count', 'devices_reporting_count',
'not_reporting_count', 'last_sync_date', 'volume_totals', 'consumed_black', 'consumed_color',
'budgeted_black', 'budgeted_color', 'projected_black', 'projected_color', 'projected_black_year',
'startingDateFormat', 'endingDateFormat'))
->with('floorplans', Floorplan::all())
->with('bldgs', Building::orderBy('bldg_name')->get()->where('organizations_id', $org_id))
->with('years', SchoolYear::orderBy('year_id')->get())
->with('orgs', Organization::orderBy('org_name')->where('organization_types_id', '!=', 7)->where('client_status', 'A')->get());
}
public function create(){}
public function store(Request $request){}
public function edit($id){}
public function update(Request $request, $id){}
public function destroy($id){}
}