Jump to content

PHP calculation


Go to solution Solved by mac_gyver,

Recommended Posts

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.

image.thumb.png.29764a73b7ab177fd8a9515b6459c6f2.png

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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 by mac_gyver
Link to comment
Share on other sites

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, ...

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Solution
Posted (edited)

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 by mac_gyver
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.