HI
I have a Rostering application running in MYSQL.
I have a number of stored procedures in MYSQL that I can call in PHPrunner and all works fine
One of these is a Custom 'APPROVE' button
This is where an authorised user selects a record from a list (tick select) Presses button and the button runs stored procedures to reset a number of filed values which Locks the record for editing
This is all working as I want.
What i want to do now is, using the same button to Approve Check some things before the record is approved. e.g. If staff A is entered for 7 hours Leave - check if they actually have that amount of leave and if Yes, Approve and Lock, If NOt echo Not Enough Leave and stop (do not approve)
I have added 3 fields to the Roster record
AL_Bal (taken from another table X ), AL_Taken in this roster (taken from another table X), A_Left (calculated by subtracting AL_Taken from AL_Bal)
I am also writing to Audit file.
When I run the code I always get the Else result e.g. "Staff member does not have enough Leave" even when they have a positive remainder
The following is my code ( Server)
if($value["ALLeft"] > 0) {
global $dal;
while ( $data = $button->getNextSelectedRecord() ) {
// set Processed field to 'True'
$sql = "Update tblrota1 set Processed='1' where RotaID=".$data["RotaID"];
CustomQuery($sql);
//}
//Now add absences to absence table
{
//CustomQuery("CALL addinsert_AnnualLeave");
CustomQuery("CALL commit_Absences_temptbl");
}
//Now set Approved to true
$sql1 = "Update tblrota1 set Approved='1' where RotaID=".$data["RotaID"];
CustomQuery($sql1);
//now send to audit table
//global $dal;
//while ( $data = $button->getNextSelectedRecord() ) {
//Send User ID to Audit table
$IP = $_SERVER["REMOTE_ADDR"];
$name = @$_SESSION["UserID"];
$recordid = $data["RotaID"];
$sqlinsert = "Insert rotas_audit (IP,User,datetime,action,description) values ('$IP','$name',Now(),'Approved','$recordid')";
CustomQuery($sqlinsert);
}
$result["txt"] = "Records have been approved for Pay and absences committed.";
}
ELSE
$result["txt"] = "Staff Member does not have enough Leave";
End
Any assistance greatly appreciated