Change ownership of record automatically

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
User avatar
shasta59
AppGini Super Hero
AppGini Super Hero
Posts: 231
Joined: 2013-01-08 19:40
Location: Calgary, Alberta, Canada

Change ownership of record automatically

Post by shasta59 » 2013-01-18 20:17

Here is one thing I am currently working on.

Preamble:
Supervisors enter reports based upon their evaluation of an member. This report needs to then be looked at by the individual. It is important the only one who can see reports are the individual who they are about and the supervisors who create the reports. The supervisors ability to view is easy - give their group permission to see all records. The other item involves changing ownership of the record to the person who was supervised.

Intent:
When the report is saved the user name is looked at and then an email is sent to this member advising them the report has been filed. What I am trying to get to happen is to have the ownership of the record changed to the member name who the report is about. The current system requires the admin to change ownership of the record.

Task:
I am writing code so when the report is filed and saved, the email is sent out and the program automatically changes the ownership of the record from the person who created it to the person who it is about. This is done using already saved information about the person who the report is concerning. This has to be a behind the scenes change so the supervisor does not have to do anything. (It was hard enough to get all of them to use the online system)

Is anyone else working on such a beast? Is there any interest in my posting my solution when done? Have it partly working but not fully yet. Changes the record ownership only about 15% of the time and not sure why (yet). But my code is still at the cheap and dirty stage right now.

Alan
Calgary, Alberta, Canada - Using Appgini 5.50 -

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: Change ownership of record automatically

Post by a.gneady » 2013-01-19 17:28

Alan, thank you a lot for all your contributions and your sharing all the tips. Here is a tip for starting this: you'll need to add your code to the tablename_insert hook ... the code would send the email using the standard PHP mail() function, and change the record owner by updating the "membership_userrecords" table. This table stores ownership info for all records of all tables. It contains the following fields that would help you find the concerned record: tableName, pkValue ... pkValue is the primary key value for the record .. in the hook function, this is stored in the variable $data['selectedID']. The query for changing ownership would look something like this:

Code: Select all

UPDATE membership_userrecords SET memberID='new_member' WHERE tableName='tablename' AND pkValue='{$data['selectedID']}'
Please let me know if you have any other questions or comments.
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

benzoD
Veteran Member
Posts: 69
Joined: 2013-01-31 21:16

Re: Change ownership of record automatically

Post by benzoD » 2013-02-06 17:33

When you get this worked out, it would be invaluable to me if you would post your method. Thanks so much for sharing your work!

User avatar
shasta59
AppGini Super Hero
AppGini Super Hero
Posts: 231
Joined: 2013-01-08 19:40
Location: Calgary, Alberta, Canada

Re: Change ownership of record automatically

Post by shasta59 » 2013-05-19 16:23

I now have this working but it took me a bit of thinking as to where to put the code. Ahmad suggested in the hook file but it would not work no matter what. The clue came when I read the following in the hook file:

Called after executing the insert query (but before executing the ownership insert query).

I could not get it to work in the hook file so said to myself, Ah ha, where is the ownership insert query found. And that led me to the tablename_dml.php file. The ownership insert is done after the hook files returns TRUE so my first efforts did not work as there was no record to change yet until the code in the dml file executed.

In this file look for the following:

if(function_exists('yourtablename_after_insert')){

Then after the following code (to make sure the record details are inserted into membership_userrecords)

sql("insert into membership_userrecords set tableName='yourtablename', pkValue='$recID', memberID='".getLoggedMemberID()."', dateAdded='".time()."', dateUpdated='".time()."', groupID='".getLoggedGroupID()."'", $eo);

Insert your own code. I did it this way so I can test some other things and have not cleaned it up yet but it works so have not made it pretty yet. I use the variable for a bit of other testing and checking so that is why I have some extra code in there. I suggest putting in a comment so you can find it easily in the future. I added a note as to what my code does.

$result2 = mysql_query("SELECT * FROM membership_users WHERE custom1 LIKE '%{$data[yourchoiceoffield]}%'") or die(mysql_error());
$row1 = mysql_fetch_array($result2);
$memid = $row1['memberID'];
$grpid = $row1['groupID'];
mysql_query("UPDATE membership_userrecords SET memberID = '$memid', groupID='$grpid' WHERE tableName=
'yourtablename' AND pkValue={$data['selectedID']}");

If anyone has any questions about the above just ask. I have tested this with over 300 records being added without one mistake so far.
Calgary, Alberta, Canada - Using Appgini 5.50 -

User avatar
shasta59
AppGini Super Hero
AppGini Super Hero
Posts: 231
Joined: 2013-01-08 19:40
Location: Calgary, Alberta, Canada

Re: Change ownership of record automatically

Post by shasta59 » 2013-05-19 16:29

One quick note.

In my situation I do not need to worry about a user which does not exist. But if you may have that situation you need to put in some code to make sure you do not create a membership_userrecords record with no groupID or memberID.

An if statement will take care of this. I will leave it up to you to figure this out. That way I do not take all the challenge out of it. Grin (If you are really stuck on the if statement then let me know.)

Alan
Calgary, Alberta, Canada - Using Appgini 5.50 -

bfell
Posts: 1
Joined: 2013-12-03 21:45

Re: Change ownership of record automatically

Post by bfell » 2013-12-04 05:02

Hi Everyone. Thanks for this thread. I believe it got me on the right track. I too would like to change the ownership of records so I can have users log in and view records made for them. I am running into a wall here. Giving you some fair warning I am not very good with php or sql.

I think my difference from the example above is that I have a lookup field selecting customers street (i.e. 123 Fake St) and instead of that field contain the street, it contains the id field for that table.

If this needs a new thread please move it or let me know and I can recreate it.


Background:
I have an app where I add services performed at customers houses and i want the customers to be able to log on and view all the services performed on their address only. So I went with trying to automatically change ownership after I save a service detail.

Tables and Fields:
I think 3 tables will be related to this.

Code: Select all

1.  Services
 - sid
 - street
 - date
 - time
 - etc...

Code: Select all

2.  Customers
 - firstName
 - lastName
 - street
 - city
 - state
 - etc...

Code: Select all

3.  membership_users
 - standard membership_users table from appgini
The Services table has a lookup field Services.street. That points to the Customers table at Customers.cid. So I have to have it take the Services.street field, match it to the Customers.cid field and then grab the Customers.street field and match that to the membership_users.custom2 field to find the username and group to add to the record.


What I've Done So Far:
So taking the example above I have created the following code and added it to the Services_dml.php file... that does not work.

Code: Select all

$result2 = mysql_query("SELECT * 
FROM `membership_users` 
WHERE membership_users.custom2 LIKE (SELECT Customers.street
                                                              FROM `Customers'
                                                              INNER JOIN `Services` 
                                                              ON Services.street = Customers.cid
                                                              WHERE Customers.cid = '%{$data['street']}%')") or die(mysql_error());
$row1 = mysql_fetch_array($result2);
$memid = $row1['memberID'];
$grpid = $row1['groupID'];
mysql_query("UPDATE membership_userrecords SET memberID = '$memid', groupID='$grpid' WHERE tableName='Services' AND pkValue={$data['selectedID']}");
Right now if I enter something in, it goes through but the owner and group is changed to a blank field. Has anyone done something like this or can anyone send me in the right direction?

I will also need to add an if statement because not all street addresses have corresponding street addresses in the membership_users table.

Hopefully I have explained this right but if you need clarification let me know.


Ionut Bocanet
Posts: 28
Joined: 2017-03-12 09:26
Contact:

Re: Change ownership of record automatically

Post by Ionut Bocanet » 2019-09-30 12:51

Dear Sirs,
I try to understand the code but i am blocked.
I attached the Detail View of my PDCA Database. Mostly it is an Action Log and the action that I create, I want to add an owner and automatically to be updated the "membership_userrecords".

I mention that already this "Action Owner" that I created it is already taking the data from the "Membership_user" as presented in the http://forums.appgini.com/phpbb/viewtop ... =854#p1850, that appgini is creating and hiding.

I just when I add an "Action Owner" to be updated the "membership_userrecords"

Can somebody give some hints.

Thank you in advance
Attachments
plan do check act action log.png
plan do check act action log.png (162.62 KiB) Viewed 73272 times
Best Regards,
Ionut Bocanet

Post Reply