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.

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?

Share on other sites

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

Share on other sites

Dear Mr.Barand,

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

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

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?

 BTW, there is no "branch" column the "mark" table

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

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";```

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

Â

Share on other sites

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.

Share on other sites

Dear Mr.mac_gyvr,

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

Share on other sites

Posted (edited)

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

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
Share on other sites

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

Share on other sites

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.

Dear Mr.Barand,

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

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.

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

// 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
Share on other sites

Dear Mr.mac_gyver & Mr.Barand,

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

Join the conversation

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

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.