Hello
How can I let the system prompt the user how many hours the record (fill in the date) has accumulated?
Ancen
Hello
How can I let the system prompt the user how many hours the record (fill in the date) has accumulated?
Ancen
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.
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.
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
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;
}
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 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;
}
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
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;
}