filter values accessed from other tables

[kevinwen]

I have 2 tables with additional user table, and want to show a drop-down list of table A in table B based on the user role. For example:
We have 2 roles: USER and MANAGER. Statuses table contains the following statuses: Initial, Request, Ready, Validate, Approved. These statuses are used in the process table. When a user is MANAGER, he can see any of the statuses in the drop down list. However, the Approved status will dissappear in the drop-down list when the user is USER.

I tried to create a __sql__ delegate method under statuses table, but don't know what to do. Does anybody know how to do it?

[shannah]

In this case I think the best thing to do is to define the valuelist in the delegate class rather than the valuelists.ini file:

Code: Select all

function valuelist__statuses(){
    if ( getRole() == 'MANAGER' ) return array('Initial'=>'Initial, 'Request'=>'Request', ...);
    else return array('Request'=>'Request', ...);
}


[kevinwen]

I tried it but doesn't work. can you tell me exactly how it work? Where should I put this function? Should I remove the definition in valuelist.ini? Thanks.

[shannah]

It would be easier for you to show me your code so I can correct it.

[kevinwen]

OK. Currently I have 2 tables: processes table and statuses table under app_root/Tables. When a user edits the processes, there should be a drop-down list of statuses shown in the processes edit page. Here's what I did:

in Tables/processes/processes.php:
class tables_processes {
function getPermissions(&$record){
$auth =& Dataface_AuthenticationTool::getInstance();
$user =& $auth->getLoggedInUser();
if (!isset($user)) return Dataface_PermissionsTool::NO_ACCESS();
$role = $user->val('user_role');
if ($role == "GUEST") return Dataface_PermissionsTool::getRolePermissions("READ ONLY");
if ($role == "MANAGER") return Dataface_PermissionsTool::getRolePermissions("DELETE");
if ($role == "USER") return Dataface_PermissionsTool::getRolePermissions("EDIT");
if ($role == "NOACCESS") return Dataface_PermissionsTool::NO_ACCESS();
}
}


in Tables/processes/fields.ini:

__sql__ = "select * from processes"

[process_id]
widget:label = "ID#"
widget:type = static
order = 1

[status_id]
widget:label = "Status"
widget:type = select
vocabulary = StatusName
order = 2

in Tables/processes/valuelist.ini:

[StatusName]
__sql__ = "select status_id, status_name from statuses where status_id <> 9 order by status_name"

in Tables/statuses/fields.ini:

__sql__ = "select * from statuses"

[status_id]
widget:type = hidden
visibility:browse = hidden

[status_name]
widget:label = "Status"

[last_updated]
widget:label = "Last Updated"
widget:type = hidden
visibility:list = hidden
visibility:browse = hidden



Now as you showed me, I put valuelist_statuses() in Tables/processes/processes.php:

function valuelist__statuses(){
$auth =& Dataface_AuthenticationTool::getInstance();
$user =& $auth->getLoggedInUser();
//If the user is null then nobody is logged in... no access. This will force a login prompt.
if (!isset($user)) return array();
$role = $user->val('user_role');

//For debugging purpose, I'm using $statuses = array('1' => 'abc', '2' => 'qqq');
$statuses = array( 1 => 'Initail', 2 => 'Request', 3 => 'Ready', 4 => 'Approved'); // or we can use df_query("select status_id, status_name from statuses");
if ($role != "MANAGER")
unset($statuses[4]);
return $statuses;
}

I really appreciate your time on me.

[kevinwen]

Hi, Steve

Does the codes above make sense to you?

[shannah]

Your using the valuelist StatusName, which is defined in your valuelists.ini file. If you want to use the valuelist that you defined in your delegate class you need to reference that one instead (or rename it to status name).

e.g.
either change your vocabulary in fields.ini to "Vocabulary=statuses"
or
change the name of your valuelist function to "valuelist__StatusName"
(and remove the valuelists definition from your valuelists.ini file to avoid possibility of conflict).

-Steve

[kevinwen]

It works now! Thank you so much. Now I have problem getting the valuelist using df_query():

Code: Select all

function valuelist__statuses(){
$auth =& Dataface_AuthenticationTool::getInstance();
$user =& $auth->getLoggedInUser();
//If the user is null then nobody is logged in... no access. This will force a login prompt.
if (!isset($user)) return array();
$role = $user->val('user_role');

$sql = "select status_id, status_name from statuses order by status_name";
if ($role != "MANAGER")
$sql = "select status_id, status_name from statuses where status_name != 'Approved' order by status_name";
$result = df_query($sql);
return $result;
}

It actually return a resource id (Resource id #112) instead of something like array('1' => 'Initial', '2' => 'Request', '3' => 'Request'). What function does return the array like this?

[shannah]

df_query is just a drop-in replacement for mysql_query. If you want to build an array, the best thing to do is loop through it.

e.g.

Code: Select all

$res = df_query($sql);
if ( !$res ) throw new Exception(mysql_error(df_db());
$out = array();
while ( $row = mysql_fetch_assoc($res) ){
     $out[$row[0]] = $row[1];
}

return $out;


Or some such thing.

On a side note, you can get df_query() to return an array of results, but each result would be an associative array that probably wouldn't fit the bill for a valuelist. The syntax for that is

Code: Select all

df_query($sql, null, true);


The operative parameter is the boolean 'true' in the third parameter telling df_query() to return the results as an array rather than a resource. Returning as an array also allows the query cache to be used.

-Steve

[kevinwen]

You're right. It doesn't return a valuelist. So is there any way to return a valuelist if I provide a sql query?

[shannah]

All a valuelist is is an array where the keys are the IDs and the values are the DISPLAY values.
So here is a function that would do such a thing:

Code: Select all

function sql2Valuelist($sql){
   $res = mysql_query($sql, df_db());
    if ( !$res ) throw new Exception(mysql_error(df_db()));
    $out = array();
    while ( $row = mysql_fetch_row($res) ) $out[$row[0]] = $row[1];
    @mysql_free_result($res);
    return $out;
}


[kevinwen]

That's perfect.