How to limit user access to stock locations?

[esato06]

I'm sorry if this question has been asked before, I've searched through the forum and didn't find a definitive answer. I want to limit a user's ability to only see available stock of certain locations for example: I have three locations but I only want a user to be able to update stock quantities or view reports for two of these locations. I imagine the process would include adding an additional field to the "www_users" table defining which location a user has access to and linking it to the primary key "loccode" in the "locations" table. I feel comfortable updating the database with these options and updating the "www_users.php" page to reflect the changes. I am just unaware of how to filter the pages information from that point or if there is an easier way to perform this action.

Thank you for your time.

[phil]

It would need a lot of changes.
Every script that allows a selection of locations would also need to restrict the selection based on the new www_users parameter.
This may be a useful modification to submit if you were working on the latest scripts?

[esato06]

It would need a lot of changes.
Every script that allows a selection of locations would also need to restrict the selection based on the new www_users parameter.
This may be a useful modification to submit if you were working on the latest scripts?

Thanks for the reply! I am currently working with the 4.10.1 revision of the application and if I ever manage to get it working properly I will definitely submit my changes! I'd imagine you would need to add a table to the User Maintenance section with a boolean option of whether the user has access to that particular location similar to how module access is determined. Then substitute a MySQL query filtering location results that only match a users given permission to a location in every request for location listings.

[phil]

Well I think you could have a multi-select box in the user screen that allows you to select one or all locations that the user is allowed to see. These could be stored in a comma separated list in a new field in www_users say allowedlocations varchar(50) as the location code is only 2 or 3 characters long (can't recall off top of head).

Then when the user logs in this is exploded and stored in an $_SESSION array variable say $_SESSION['AllowedLocationsArray'] = explode($myrow['allowedlocations'],','); from includes/UserLogin.php then when we have <select "Location" > statements through-out the code (use grep to find them). Modify the SQL to use:

Code:

$SQL = "SELECT loccode, locationname FROM locations WHERE loccode IN (";
foreach ($_SESSION'AllowedLocationsArray'] as $LocCode) {
        $SQL .=  "'" . $LocCode . "',";
}
$SQL = mb_substr($SQL,0,mb_strlen($SQL)-1) . ")"; //to nobble the trailing "," and add the closing bracket

I am sure there would be a better way - but just my 2c

[PakRicard]

Hi:

I think it is a very interesting feature, but I think it is a better option to store this info in a separate table as there is a N:M relationship between tables www_users and locations. We'll need one script to maintain this relationship and add a JOIN on all SELECT statements using location table.

Probably Phil's approach is a bit faster to code, but having location codes concatenated in a string is less flexible than having them in a SQL table, for all the queries and improvements we can pull from this feature.

Also, there are some decisions to be made that affect all webERP behaviour. Some examples:
In SelectProduct.php "QOH" will show QOH for "all locations" or "your authorized locations". Same for QOO: should show QOO to be received at locations not authorized?

Also, my 2c. Now we have 6c :-)

[esato06]

That's an interesting point as well, keeping location access information in its own separate table will allow for more flexibility down the line. In concept I understand how both approaches would work and benefit the end-user, but I lack the coding ability to really put them into practice. I've started to implement a few of the suggestions from Phil and even though I haven't got the filtered location queries to work properly I wonder how the application will function after a new stock location is added and a user logs in before a permission is set since the array is dynamically generated vs the module array being a statically defined list. A separate table will solve this by having the ability to assign a default value to a field in the database every time a new record is created whereas in the first approach you will have to go into the user maintenance page to update the user permissions.

Looks like we are up to 7 cents, I'm not as good as you guys so I can only add 1...