This topic is locked
[SOLVED]

 Problems with the filter panel

11/12/2020 6:19:09 AM
PHPRunner General questions
K
kohle author

I have the following problem:
The filter panel shows to much data.
I tried to solve in the

security part of phprunner, but only ends of phprunner

error messages or wrong data in the (left side) filter panel
to simplify :

I have 2 tables

  • user table with the fields : id_user, id_company, group
  • task table with the fields : id_task, id_user
    Depend on the the group, I want to show the data of the

    table "task" like this:
    group : "customer" , show and add only own user data

    group : "customer_admin" , show, edit,add data from the same id_company
    Query is :

    select task.id_case, task.id_user, user.id_company

    where

    task.id_user = user.id_user
    Depending on the group, I modify the where clause of the

    listpage. id_user, id_company and group I saved at login

    in SESSION vars.
    In listpage, beforeSQL...
    if($strWhereClause!="")

    {

    $strWhereClause = $strWhereClause . " and id_company = " . $_SESSION["JK_ID_COMPANY"];

    }

    else

    {

    $strWhereClause = " id_company= " . $_SESSION["JK_ID_COMPANY"];

    }

    if ($_SESSION["JK_GROUP"]=='customer')

    {

    $strWhereClause = $strWhereClause . " and t_case.id_user = " . $_SESSION["JK_ID_USER"];

    }
    This works fine. But I have problems with the filter panel depending of the security settings.
    Now 2 things I tried :

    1)

    Filter panel ok, edit ends with an phprunner error:

    Unknown column 'user.id_company' in 'where clause'
    With the "admin" group the error dont occur.
    In debug mode I see : WHERE ( t_user.id_company=2 ) and ( ( t_task.id_task=9 ) )

    But for the key column only id_task is defined.
    In the securtiy PERMISSION part:

    group "customer_admin" allowed to edit table task

    group "customer not allowed to edit
    In security ADVANCED part:

    table: task

    User can see and edit their own data only

    User/Main table ownerID : id_company
    2)

    Filter is wrong, edit works.
    The filter shows all users of the table user not only

    of the listpage where I changed the where clause

    to show only from a company.
    In security ADVANCED part:

    table: task

    User can see and edit other user data
    And it getting more weird when I change the

    security of the table "user" to

    User can see and edit their own data only
    The filter panel shows insteed of the username the ids

    of the the other users in the table.
    What I need is that I only see the users in the filter panel from the listpage I changed the where clause.

    Is there a way to change the where clause of the filter panel ?
    Thanks for helping

    rg

    J.

Sergey Kornilov admin 11/12/2020

To be honest, this post to too long to allow digesting but I can tell that BeforeSQL query event is a wrong choice for tasks like this.
Here is the recommended technique for situations like this:

https://xlinesoft.com/phprunner/docs/dynamic_sql_query.htm

K
kohle author 11/12/2020



To be honest, this post to too long to allow digesting but I can tell that BeforeSQL query event is a wrong choice for tasks like this.
Here is the recommended technique for situations like this:

https://xlinesoft.com/phprunner/docs/dynamic_sql_query.htm


Be honest,

the effect of your security system on lookups, filters and tables cant hardly be followed anymore.

Change the where here, change the where there .
Why CAN I change the where clause in SQLBefore event when it has no effect on the filters ?

I change to: show only customer emails on listpage, in the filter you show admin emails, other customers email. whats the idea of this ?
When I change the security to : only my data, edit and show

than I see on the filter panel, ids of the other data which not belong to me.

Like this : 3,4,5,kohle,1
One suggestion: But the security of the tables (permisson/advanced) on the table tabpage, not at the other site of the project.
rg

J.

Sergey Kornilov admin 11/13/2020

I have answered your question and explained what you need to use. Now you may or may not follow the advice, it is your decision.