Retrieving the records you have access

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
cesteban
Veteran Member
Posts: 52
Joined: 2018-04-18 09:56

Retrieving the records you have access

Post by cesteban » 2018-04-18 15:09

Hi,

I'm doing a query about a doctor's appointments with their patients, but when doing the query I get all the doctors' appointments for all the doctors.

I need my query to only get the records of the doctor I'm accessing

The query that I use and it works for me to obtain all the records is:
  • $result = sql("SELECT * FROM Citas WHERE date = '" . $date . "' ORDER BY time ", $e0);
I tried to use the function get_sql_from with the table 'Appointments' but I do not get the desired result, this is the query:
  • $result=sql("SELECT * FROM " .get_sql_from('Citas') . " WHERE date = '" . $date . "' ORDER BY time ", $e0);
or
  • $result=sqlValue("SELECT * FROM " .get_sql_from('Citas') . " WHERE date = '" . $date . "' ORDER BY time ");
Can someone help me to know how I can achieve it?

Regards :P

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Retrieving the records you have access

Post by pbottcher » 2018-04-18 19:24

Hi Cesteban,

you need to add the doctor to the where clause of your sql query.
sql("SELECT * FROM Citas WHERE date = '" . $date . "'and DOCTORFIELD = '". $doctor . "' ORDER BY time ", $e0);

assuming that DOCTORFIELD is the databasefield of you doctors and $doctor contains the doctor you want to retrieve the data for.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
cesteban
Veteran Member
Posts: 52
Joined: 2018-04-18 09:56

Re: Retrieving the records you have access

Post by cesteban » 2018-04-18 20:55

Thanks pböttcher,

I just need to recover the records to which my user has permissions, in this case each user is in a group, and you only have access to the records of your group (by each doctor)

If I get this, by bringing the records I will only get the ones that correspond to each doctor

regards

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Retrieving the records you have access

Post by pbottcher » 2018-04-19 07:04

Hi Cesteban,

where do you have the link in your db of who has access to which doctor? Also, if you refer to the user-group, are you using the appgini users/groups or have you a seperate table maintaining users/groups? Maybe you can share an example which make it easier to understand.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
cesteban
Veteran Member
Posts: 52
Joined: 2018-04-18 09:56

Re: Retrieving the records you have access

Post by cesteban » 2018-04-19 14:26

Hi Pböttche,

I'm using the user/group system.

All the time in my app, if you review the information as doctor, you have access only your patients, your medical consultations, your medical prescriptions, etc, but only the information for each doctor.

In this case when yoy consult the medical appointments calendar, each doctor can see all the appoinments for all doctors

May be I can make a query to select the records only for the doctor (maybe), but I know there are a AppGini function to do that (I think the get_sql_from), but it's not working to me

Thank you

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Retrieving the records you have access

Post by pbottcher » 2018-04-19 16:47

Hi Cesteban,

----
I tried to use the function get_sql_from with the table 'Appointments' but I do not get the desired result, this is the query:
$result=sql("SELECT * FROM " .get_sql_from('Citas') . " WHERE date = '" . $date . "' ORDER BY time ", $e0);
---
Are you refering to the correct table? In your sentense you mention the 'Appointments' table, but in the SQL you refer to the 'Citas' table.
Can you change the Citas to Appointments?
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
cesteban
Veteran Member
Posts: 52
Joined: 2018-04-18 09:56

Re: Retrieving the records you have access

Post by cesteban » 2018-04-19 19:06

Thank pböttcher,

I could solve it

When you use the function get_sql_from ('Citas'), it already includes the" WHERE" statment to bring only the records you have access to, as user.

You must add the additional ANDs

That's how my query is working

$result = sql ("SELECT paciente FROM" .get_sql_from ('Citas'). " AND Citas.date = '". $ currentDate. "'", $ e0);

Thanks for the support

Regards

Post Reply