Create automatic records with conditions for the user without the validity of the entry

Got something cool to share with AppGini users? Feel free to post it here!
Post Reply
ksabra
Posts: 8
Joined: 2020-05-26 11:58

Create automatic records with conditions for the user without the validity of the entry

Post by ksabra » 2020-08-02 13:05

Hi,
I am using appgini v 5.82

There is a table named (deposit_request)

In the table there are the following fields
id
delegate_name (lookupfield from employees table)& employees table Connected to users through view_membership_users table
order_status (optionlist New;; Under review;; Postponed;; Posting)
created_by (<%%creatorUsername%%>)
There is a group named (collectors) They can only modify or view the records they own

For reasons related to application analysis The record must be created automatically
So it will be executed in the hooks/ __global.php in function login_ok


function login_ok($memberInfo, &$args){
// First the following variables were created in function
$username = $memberInfo['username'];
$employee_id = sqlValue("select id from employees where username='{$username}'");
$cn='New';
$tbname='deposit_request';
$openreques_existsbefore = sqlValue("select count(1) from deposit_request where created_by='{$username}' and order_status='{$cn}'");
// If the user belongs to a group collectors.
if($memberInfo['group']=="collectors"){
// To create an automatic new record in the deposit_request table, there must be no records for the same user with the value of the order_status field equal to new.
if (!$openreques_existsbefore) {
sql("insert into deposit_request set delegate_name='{$employee_id}',order_status='{$cn}', created_by='{$username}'", $eo);
// To get LAST INSERT ID from inserted record
$last_id = sqlValue("select id from deposit_request where id = LAST_INSERT_ID() and created_by='{$username}' and order_status='{$cn}' ");
// To Set record owner
sql("insert into membership_userrecords set memberID='{$username}',tableName='{$tbname}', pkValue='{$last_id}'", $eo);
}else{
return false;
}
}
return '';
}

Now everything is fine and the record is created automatically with the condition after logging in without any problem

Users in the group collectors
After logging in
The app checks the user's previous records of the deposit_request table
If the condition is true in the order_status field
The app creates a new record automatically
If the condition is false in the order_status field
The app ignores the process

*Note if you want to implement the same method*
Now the user will see the record in the table and can work on it
But if the user changes the value of the order_status field and the value does not meet the condition in the variable $ cn
A new automatic record will not be created in the deposit_request table
Only after logging out of the app and logging in again
If you want to create a new automatic record in the deposit_request table
After the user changes the value of the order_status field, the value does not meet the condition in the variable $ cn
Without logging out of the app and then logging in again ,Additional procedures must be added in hooks
So it will be executed in the hooks/deposit_request.php in function after_update With a few simple changes
For example we can use set_record_owner function

*Now there is a question*
Is the LAST_INSERT_ID function return the first automatically generated record successfully inserted
For the same session ?

I am not sure of that I added two additional criteria in $last_id to avoid any errors until this is confirmed

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Create automatic records with conditions for the user without the validity of the entry

Post by onoehring » 2020-08-06 15:30

Hi,

please use the [ code ] ... [ /code ] to display your code. This would make your post(s) much easier to read.

I am not sure, If I understand your (very hidden ;-) ) question but as it says
The LAST_INSERT_ID() function returns the first automatically generated integer ( BIGINT UNSIGNED) successfully inserted for an AUTO_INCREMENT column.

here https://www.mysqltutorial.org/mysql-last_insert_id.aspx, I would expect the value is basically not only limited to the current session.
But, as you write something to the table and use last_insert directly after you should most often get the correct value. It is possible that another users loges in the at almost the exact same time, so that you would get the ID from that insert. Maybe this is what you mean by session. I am not sure if your second and third condition

Code: Select all

... and created_by='{$username}' and order_status='{$cn}' ");
will be applied though.
As an alternative you might use (SQL not tested)

Code: Select all

$last_id = sqlValue("select MAX(id) from deposit_request WHERE created_by='{$username}' and order_status='{$cn}' GROUP BY created_by ");
This should imho return the last inserted record with the second and third condition, i.e. the record you probably want.

Olaf

ksabra
Posts: 8
Joined: 2020-05-26 11:58

Re: Create automatic records with conditions for the user without the validity of the entry

Post by ksabra » 2020-08-06 18:10

onoehring wrote:
2020-08-06 15:30
Hi,

please use the [ code ] ... [ /code ] to display your code. This would make your post(s) much easier to read.

I am not sure, If I understand your (very hidden ;-) ) question but as it says
The LAST_INSERT_ID() function returns the first automatically generated integer ( BIGINT UNSIGNED) successfully inserted for an AUTO_INCREMENT column.

here https://www.mysqltutorial.org/mysql-last_insert_id.aspx, I would expect the value is basically not only limited to the current session.
But, as you write something to the table and use last_insert directly after you should most often get the correct value. It is possible that another users loges in the at almost the exact same time, so that you would get the ID from that insert. Maybe this is what you mean by session. I am not sure if your second and third condition

Code: Select all

... and created_by='{$username}' and order_status='{$cn}' ");
will be applied though.
As an alternative you might use (SQL not tested)

Code: Select all

$last_id = sqlValue("select MAX(id) from deposit_request WHERE created_by='{$username}' and order_status='{$cn}' GROUP BY created_by ");
This should imho return the last inserted record with the second and third condition, i.e. the record you probably want.

Olaf
thanks for the help
I mean
Returns the ID of the last inserted row from

Code: Select all

sql("insert into deposit_request set delegate_name='{$employee_id}',order_status='{$cn}', created_by='{$username}'", $eo);
Without using another query

Something similar to that

Code: Select all

INSERT INTO deposit_request set delegate_name='{$employee_id}',order_status='{$cn}', created_by='{$username}'INSERT INTO membership_userrecords set memberID='{$username}',tableName='{$tbname}', pkValue=LAST_INSERT_ID()

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Create automatic records with conditions for the user without the validity of the entry

Post by onoehring » 2020-08-07 06:58

Hi,

I think I do not understand the why and how of your request.
Why don't you want to use another query? In your original code you actually suggested using a query

Code: Select all

// To get LAST INSERT ID from inserted record
$last_id = sqlValue("select id from deposit_request where id = LAST_INSERT_ID() and created_by='{$username}' and order_status='{$cn}' ");
I suggested the use the MAX() option/function in combination with your WHERE criteria.

Olaf

Post Reply