2. PHPRunner General questions
  3. [SOLVED]  Customer, Regional and Administrative data listings
This topic is locked
[SOLVED]

 Customer, Regional and Administrative data listings

 		10/5/2011 3:32:55 PM
PHPRunner General questions
S
 Stucco author

Currently I am using the feature that allows users to only see their data. I am using it a little differently though. Each user has a customer_id, and that customer_id is what is recorded in the record. Users with the same customer can see each other's records. I now need to expand this and have a regional account that can see a configurable set of customer's records. For example, Cust A, B and C are administered by Regional Guy 1. Regional Guy 1 should have visibility of Cust A, B and C's records, but not Cust D.
I think the hardest part of this will be to make it configurable. Maybe I will have to create another view into the same tables.
S
 		Stucco author 10/9/2011

I added a table to the database to create a one to many relationship between users and customers. This allows the setting of any number of customers to be visible by each specific user, however I am having difficulty setting this restriction on the visibility. The query I need to set is like this, but it will not let me save it with the PHP variable.



SELECT

shipments.id,

shipments.origin,

shipments.destination,

shipments.customer_id,

FROM shipments

INNER JOIN users_customers ON shipments.customer_id = users_customers.customer_id

WHERE (shipments.status_id ="1") AND users_customers.user_id = $_SESSION["user_id"]

ORDER BY shipments.id


I set $_SESSION["user_id"] on successful logon, so I know it will have a proper value. I need to restrict this view similar to the built in setting discussed in the post above.
Thank you!!
S
 		stiven 10/10/2011

i had a problem with the where clause in the sql query design so this is what i did you may try it and see if it works for you... i also notice the parenthesis is closed before and you dont have quotes on the $_SESSION variable
WHERE (shipments.status_id ="1" AND users_customers.user_id = '".$_SESSION["user_id"]."') still im not sure if it will work it didn't for me..
on the beforeSQL query events page add this...


$strWhereClause = whereAdd($strWhereClause, "shipments.status_id ="1" AND users_customers.user_id = '".$_SESSION["user_id"]."');
Sergey Kornilov admin 10/10/2011

SQL Standard requires you to use single quotes to wrap text values.
Incorrect:

WHERE shipments.status_id ="1"
Correct:

shipments.status_id ='1'
Note, that only text values need to be wrapped. Numeric values do not require any wrappers.
S
 		Stucco author 10/16/2011

I was able to add the restriction in the List and Export page before sql query events. The code looks like this:

$strWhereClause = whereAdd($strWhereClause, "users_customers.user_id = " . $_SESSION["user_id"]);