How to judge the accumulated hours of the day?

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.
Post Reply
ancenhung
User
Posts: 9

How to judge the accumulated hours of the day?

Post by ancenhung »

Hello

How can I let the system prompt the user how many hours the record (fill in the date) has accumulated?

Ancen


mobhar
User
Posts: 11660

Post by mobhar »

Your post is not clear enough. You should give a real example, so others could comprehend the situation straightforward.


ancenhung
User
Posts: 9

Post by ancenhung »

Feel sorry,

I'm currently using phpMaker 2023 version.
I have a table for employees to fill in the daily work log, the fields are as follows

CREATE TABLE `work_diary` (
   `w_id` int(11) NOT NULL,
   `w_date` date NOT NULL,
   `w_addressnumber` varchar(255) NOT NULL,
   `w_area` varchar(255) NOT NULL,
   `w_main` varchar(255) NOT NULL,
   `w_secondary` varchar(255) NOT NULL,
   `w_description` text DEFAULT NULL,
   `w_hours` float NOT NULL,
   `wc_founder` int(11) NOT NULL,
   `w_postdate` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

How can I use w_date (fill in the date) and w_addressnumber (employee number) and w_hours (how many hours the job took) to judge that when the employee chooses w_date, the system will prompt how many hours have been accumulated that day. It can be up to 12 hours a day, and cannot be longer than 12 hours, Otherwise the form cannot be submitted.


mobhar
User
Posts: 11660

Post by mobhar »

So, in that table, there is a possibility to insert multiple records for an employee, right?

If so, then you may simply use Row_Inserting server event in order to check how many hours an employee has already had w_hours value in database (sum of accumulation).

Then, you may subtract the value of 12 by that sum of accumulation, and then make sure the value of user input is lower than or equal to the result above.

If it is greater than it, then you may display the error message by using setFailureMessage method of table/page object, and then return false to cancel the inserting process.


ancenhung
User
Posts: 9

Post by ancenhung »

Yes, there could be multiple records for the day. The day cannot be less than 8 hours and cannot be greater than 12 hours.
For example, on May 25, 2023, a colleague whose employee number is 101 may have the following 5 records.
w_hours: 2,4,2,2,2 hours

I'm new to phpMaker and don't yet check how many hours the employee has accumulated in the database for 2023-05-25 from the Row_Inserting server event.
Also don't know where to find setFailureMessage.

I still need your guidance, thank you very much.

Ancen


mobhar
User
Posts: 11660

Post by mobhar »

You may use ExecuteScalar global function in order to check the sum of accumulation from w_hours field. For more info and example, please read Some Global Functions.

If you want to display the error message, you may simply insert this code into Row_Inserting server event:

$accumulated = ExecuteScalar("Your SQL SELECT to get accumulation goes here");
if (<yourcondition>) { // <-- adjust to your condition that will prevent the inserting record
    $this->setFailureMessage("Sorry, you are not allowed to save this record.");
    return false;
}

ancenhung
User
Posts: 9

Post by ancenhung »

Can you help me see if there is something wrong with the code?
The following 5 lines of error messages appear.

Notice: Undefined variable: post in C:\xampp\htdocs\toolmachine\models\WorkDiary.php on line 1775
Notice: Trying to access array offset on value of type null in C:\xampp\htdocs\toolmachine\models\WorkDiary.php on line 1775
Notice: Undefined variable: post in C:\xampp\htdocs\toolmachine\models\WorkDiary.php on line 1779
Notice: Trying to access array offset on value of type null in C:\xampp\htdocs\toolmachine\models\WorkDiary.php on line 1779
Notice: Trying to access array offset on value of type null in C:\xampp\htdocs\toolmachine\models\WorkDiary.php on line 1779

thank you

Ancen


 // Row Inserting event
    public function rowInserting($rsold, &$rsnew)
    {

        $addressnumber = CurrentUserUAddressnumber();
        $wdate = $post['w_date'];  //1775

        $accumulated = ExecuteScalar("SELECT SUM(w_hours) as whours FROM `work_diary` WHERE w_date = '$wdate' AND w_addressnumber = '$addressnumber' GROUP BY w_date");

        $whours = $post['w_hours']+$rsold["whours"];  //1779

        if ($whours >= 12) { // <-- adjust to your condition that will prevent the inserting record
        $this->setFailureMessage("Sorry, you are not allowed to save this record.");
        return false;
        }
    }

mobhar
User
Posts: 11660

Post by mobhar »

You may simply change $post to $rsnew, and try again.


ancenhung
User
Posts: 9

Post by ancenhung »

mobhar wrote:

You may simply change $post to $rsnew, and try again.

Thank you
The remaining line of error code is as follows

Notice: Trying to access array offset on value of type null in C:\xampp\htdocs\toolmachine\models\WorkDiary.php on line 1779


$addressnumber = CurrentUserUAddressnumber();
        $wdate = $rsnew['w_date'];

        $accumulated = ExecuteScalar("SELECT SUM(w_hours) as whours FROM `work_diary` WHERE w_date = '$wdate' AND w_addressnumber = '$addressnumber' GROUP BY w_date");

		$whours = $rsnew['w_hours']+$rsold["whours"];    //1779
        if ($whours >= 8) { // <-- adjust to your condition that will prevent the inserting record
        $this->setFailureMessage("Sorry, you are not allowed to save this record.");
        return false;
        }

mobhar
User
Posts: 11660

Post by mobhar »

If you did not use Copy to add record, then there is no $rsold yet in that event.

So, this code:
$whours = $rsnew['w_hours']+$rsold["whours"]; //1779

should be:
$whours = $rsnew['w_hours']; //1779


ancenhung
User
Posts: 9

Post by ancenhung »

I edited, but got "Insert canceled" message.

How can I retrieve the 'whours' value from the following SQL language when judging the condition?
This SQL syntax mainly finds out how many hours an employee has accumulated that day.

$accumulated = ExecuteScalar("SELECT SUM(w_hours) as whours FROM `work_diary` WHERE w_date = '$wdate' AND w_addressnumber = '$addressnumber' GROUP BY w_date");

Ancen


mobhar
User
Posts: 11660

Post by mobhar »

Make sure you did not remove return true; line in that Row_Inserting server event.


ancenhung
User
Posts: 9

Post by ancenhung »

The current code is as follows, and the materials can still be submitted smoothly after more than 12 hours.


// Row Inserting event
    public function rowInserting($rsold, &$rsnew)
    {

        $addressnumber = CurrentUserUAddressnumber();
        $wdate = $rsnew['w_date'];

        $accumulated = ExecuteScalar("SELECT SUM(w_hours) as whours FROM `work_diary` WHERE w_date = '$wdate' AND w_addressnumber = '$addressnumber' GROUP BY w_date");

		$whours = $rsnew['w_hours'];
        if ($whours >= 12) { // <-- adjust to your condition that will prevent the inserting record
        $this->setFailureMessage("Sorry, you are not allowed to save this record.");
        return false;
        }
        return true;
    }

mobhar
User
Posts: 11660

Post by mobhar »

You should involve the value from $accumulated variable in your condition.

This code:
if ($whours >= 12) {

should be:
if ($accumulated >= 12) {

If it still does not work, then you need to evaluate your SQL SELECT again.


ancenhung
User
Posts: 9

Post by ancenhung »

It's working, thank you very much.


Post Reply