PHP calculation

[Senthilkumar]

Dear Team,

I want to calculate the engineer's average mark for every year. I am using the bellow code for calculating

$where_columns = ['Year' => 'Year', 'Branch' => 'Branch'];

$CurrentYear = date('Y');

$where_terms = [];

foreach ($where_columns as $col => $input) {
	if ($_POST[$input] == !'') {
		$where_terms[] = "$col= '$_POST[$input]' ";
	}
}

$where = '';
if (!empty($where_terms)) {
	$where = 'WHERE ' . implode(' AND ', $where_terms);
} else {
	$where = 'WHERE 1 ';
}
$query = "SELECT * FROM kra.mark $where";
$result = mysqli_query($conn, $query);
while ($row = mysqli_fetch_array($result)) {

	$Emp_Name = $row['Emp_Name'];
	$Emp_No = $row['Emp_No'];
	$Branch = $row['Branch'];
	$Year = $row['Year'];
	$Jan = $row["Jan"];
	$Feb = $row["Feb"];
	$Mar = $row["Mar"];
	$Apr = $row["Apr"];
	$May = $row["May"];
	$Jun = $row["Jun"];
	$Jul = $row["Jul"];
	$Aug = $row["Aug"];
	$Sep = $row["Sep"];
	$Oct = $row["Oct"];
	$Nov = $row["Nov"];
	$Dec = $row["Decm"];

	
	if($Jan == ""){
		$Jan1 = 0;
	}else{
		$Jan1 = $Jan;
	}

	if ($Feb == "") {
		$Feb1 = 0;
	} else {
		$Feb1 = $Feb;
	}

	if ($Mar == "") {
		$Mar1 = 0;
	} else {
		$Mar1 = $Mar;
	}

	if ($Apr == "") {
		$Apr1 = 0;
	} else {
		$Apr1 = $Apr;
	}

	if ($May == "") {
		$May1 = 0;
	} else {
		$May1 = $May;
	}

	if ($Jun == "") {
		$Jun1 = 0;
	} else {
		$Jun1 = $Jun;
	}

	if ($Jul == "") {
		$Jul1 = 0;
	} else {
		$Jul1 = $Jul;
	}

	if ($Aug == "") {
		$Aug1 = 0;
	} else {
		$Aug1 = $Aug;
	}

	if ($Sep == "") {
		$Sep1 = 0;
	} else {
		$Sep1 = $Sep;
	}

	if ($Oct == "") {
		$Oct1 = 0;
	} else {
		$Oct1 = $Oct;
	}

	if ($Nov == "") {
		$Nov1 = 0;
	} else {
		$Nov1 = $Nov;
	}

	if ($Dec == "") {
		$Dec1 = 0;
	} else {
		$Dec1 = $Dec;
	}

	if ($Year == $CurrentYear) {


		$currentMonth = date('M', strtotime(date('Y-m') . " -1 month"));

		if ($currentMonth == 'Dec') {
			$result2 = "Decm";
		} else {
			$result2 = $currentMonth;
		}


		if ($row[$result2] == '') {
			$previous_month = date('m', strtotime(date('Y-m') . " -2 month"));
		} else {
			$previous_month = date('m', strtotime(date('Y-m') . " -1 month"));
		}

		try {
			$TotalMark = round(($Jan1 + $Feb1 + $Mar1 + $Apr1 + $May1 + $Jun1 + $Jul1 + $Aug1 + $Sep1 + $Oct1 + $Nov1 + $Dec1) / $previous_month, 2);
		} catch (DivisionByZeroError $e) {
			$TotalMark = '0';
		}
		
	} else {

		try {
			$TotalMark = round(($Jan1 + $Feb1 + $Mar1 + $Apr1 + $May1 + $Jun1 + $Jul1 + $Aug1 + $Sep1 + $Oct1 + $Nov1 + $Dec1) / 12, 2);
		} catch (DivisionByZeroError $e) {
			$TotalMark = '0';
		}
		

	}
}

The engineer will submit the data every month and has one month time to submit the data. For example, March month data he can submit up to April 30th. After that, he cannot submit the March month data.

For the current year, I will check if the previous month's data is submitted or not. based on that I will sum all the values and divided it by the previous month.

For the previous year, I directly summed up all the values and divided them by 12.

My requirement is, that if any engineer is added in the middle of the year and starts updating the data, then the calculation should start from that particular month.

In the above picture, s.No: 228 has started updating from Jul 2023. The average should sum J(un+Jul_Aug+Sep+Oct+Nov+Dec / 6)

I don't know how to do this. Can anyone please help me with how to do this?

[Barand]

Stop storing spreadsheets in your database. Come back when you have normalized data.

[Senthilkumar]

Dear Mr.Barand,

I am just storing the data month-wise. Please refer the table dump with the below link.

https://drive.google.com/file/d/1kRszTxRE0GZZlQG4FIkvOQupYv-itK8e/view?usp=sharing

This is an old project. Please suggest me how to modify the table structure and how to calculate the average

[Barand]

How do you know that he started in July?

How do you know that Kuldeep Singh (#230) didn't leave at the end August and his average shouldn't be calculated over 8 months?

In other words, what is the rule for when to include blank values in the averages and when to ignore them?

[edit] BTW, there is no "branch" column the "mark" table

[Senthilkumar]

Dear Mr.Barand,

At the starting of this project I don't plan for these calculations. So I didn't store the details like when he is joined and when he is left.

The branch will be fetched from user table with inner join query using employee number

[Senthilkumar]

Dear Mr.Barand,

 

we can use the below query to display the name and branch from the user table.

$query = "SELECT a.*, b.Name as Name, b.Branch as Branch FROM kra.mark as a
inner join kra.users as b on b.Emp_No = a.Emp_No $where";

For your reference, I upload the user's table below link

https://drive.google.com/file/d/1Cj8uxhX08z98qdGOmcvZxeU4B2gH31qP/view?usp=sharing

I created a column Startmonth and updated it when it starts from. 

Once the user leaves, his account is deactivated and the Status becomes 0. If the status is 0 we can calculate up to the last updated month.

Kindly suggest me how to do this

 

[mac_gyver]

i hope that isn't real data with actual password hashes?

you need an end date, so that you can only include data that's between the start date and the end date.

[Senthilkumar]

Dear Mr.mac_gyvr,

I need to calculate the average every year (year-wise), not cumulative average. 

[mac_gyver]

that's not what @Barand and I are trying to get you to understand.

for the example he asked about - 

10 hours ago, Barand said:

How do you know that Kuldeep Singh (#230) didn't leave at the end August and his average shouldn't be calculated over 8 months?

In other words, what is the rule for when to include blank values in the averages and when to ignore them?

you need to know which if any of the empty/zero - sep, oct, nov, and dec values should be included in the sum/count to produce the average for the year in question.

if the end date for that user was 2023-08, you don't included any of those empty values in the average calculation. if the end date is a value greater than 2023-08 or is an empty/null/maximum-value, you would treat some or all of these empty values as zeros and include them in the average calculation for the year in question.

Edited April 27 by mac_gyver

[Senthilkumar]

Ok. I understood. I included the end month also in the user table. Please check the new dump on the below link

https://drive.google.com/file/d/1Hhcx8CX_EjdcEI-hoPAmVXbr6bLNTkLF/view?usp=sharing

[Barand]

Now you have all the data you need to do what you wanted to do. For each mark record,j ust include the values for those months that lie within the data range and job done.

[Senthilkumar]

Dear Mr.Barand,

Can you please guide me

[mac_gyver]

since your data isn't normalized, and is stored in month name columns in a single row, you will need to produce a year-month value, e.g. 2023-04, ... for each piece of data so that you can compare if it is BETWEEN the start and end dates OR you could normalize the data, and store each value in its own row in a table. the normalized data table would have columns for - id (autoincrement primary index), Emp_No, date, and amount.

i also recommend that you store the dates using a DATE datatype and use 01 as the day part - year-month-01, e.g. 2023-04-10, 2024-06-01, ...

[Barand]

Working with what you've unfortunately got now, when you read through the query results store each record in an array which has a subarray for month values.

Covert the start and dates to index values for this array (0 if the start date is earlier than Jan and 11 if the end date is after Dec. Just use that portion of the array to calculate totals and averages.

[mac_gyver]

here's some coding pointers -

1. don't copy variables to other variables for nothing. just use the original variable(s) that data is in.
2. don't write out code for every possible value. use a data-driven design and let the computer operate on each value for you.

here's what I came up with. in your initialization section -

// define the month number and names (columns)
$months = [1=>"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Decm"];
// note: the reason you needed to use decm is because dec is a reserved word. you can use dec if you surround it in back-tacks in any sql statement

inside the while(){...} loop -

	// array to hold the data for the current row
	$data = [];
	// loop over the month number and month names
	foreach($months as $mo=>$month)
	{
		// produce the year-month value
		$ym = $row['Year'] .'-'. str_pad($mo,2,'0',STR_PAD_LEFT);
		
		// test if the year-month is between the start and end dates
		if($ym >= $row['start'] && $ym <= $row['end']) // use whatever you have named the start and end date columns
		{
			// put the value into the data array
			// use zero for any empty value
			$data[] = $row[$month] == '' ? 0 : $row[$month];
		}
	}
	// at this point, $data contains the entries to use for the current row of fetched data
	
	// examine the result
	echo '<pre>'; print_r($data); echo '</pre>';
	
	// calculate the average
	$TotalMark = round((array_sum($data)) / count($data), 2);
	
	// examine the result
	echo $TotalMark;

note: you should use a value like 9999-12 for the end date for a person who is still employed

Edited April 27 by mac_gyver

[Senthilkumar]

Dear Mr.mac_gyver & Mr.Barand,

Thanks for your support. The above code is working properly.