Parent User ID filter issue

PHPMaker General Discussion (PHPMaker)
1

Hi,In the Page_DataRendering event,
I am filtering by CurrentUserID with the following code.
But the Parent User to which Current User is connected cannot see this data.
I can’t find how to include CurrentParentUserID() in this filter - code.

$qtyprojects = ExecuteQuery("SELECT * FROM projects WHERE Status <= 2 AND EmployeeID =". CurrentUserID() .""); // number of project
$projectscount = $qtyprojects->rowCount();

I tried this way but it didn’t work.

$qtyprojects = ExecuteQuery("SELECT * FROM projects WHERE Status <= 2 AND EmployeeID =". CurrentUserID() ." AND EmployeeID =". CurrentParentUserID() .""); // number of project
2

How about this?

$projectscount = ExecuteScalar("SELECT COUNT(*) FROM projects WHERE Status <= 2 AND (EmployeeID =". CurrentUserID() ." OR EmployeeID =". CurrentParentUserID() .")");
3

Thank you for shortening the query.
I am getting an error like this.C:\inetpub\wwwroot\app\vendor\doctrine\dbal\src\Driver\API\MySQL\ExceptionConverter.php(86): An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘)’ at line 1

function Page_DataRendering(&$header)
{
// Page Data Rendering event

if (CurrentUserLevel() <= 1 ) {

$projectscount = ExecuteScalar("SELECT COUNT(*) FROM projects WHERE Status <= 2");

} else {

$projectscount = ExecuteScalar("SELECT COUNT(*) FROM projects WHERE Status <= 2 AND (EmployeeID =". CurrentUserID() ." OR EmployeeID =". CurrentParentUserID() .")");

$header = '<div class="card h-100 text-center text-white bg-primary">
            <div class="card-body mt-2">
              <h2 class="mb-0 fw-bold">'.$projectscount.'</h2>
              <p class="card-text h5">'.Language()->phrase("DshProjects").'</p>
            </div>
          </div>'
        }
}
4

Make sure CurrentUserID() and CurrentParentUserID() returns the valid value, otherwise, it will trigger error for your SQL.

5

I selected a Parent User Id in the Advances Security and I also selected the User Id field on top of the screen and for my table. But when I use CurrentParentUserID() in my table to automatically fill one of my table columns, nothing happens.

To check the value I added

echo "CurrentParentUserID: ".CurrentParentUserID();

to the Table_Load() function of my table.

To double check I also added echo "CurrentUserID: ".CurrentUserID();

Result: CurrentUserID() was filled with my UserId but CurrentParentUserID() is empty although it’s filled in the database.

What can I do to fix this?

6

I fixed it by using this code in Table-Specific → Common → Row_Inserting:

// Row Inserting event

function Row_Inserting(?array $oldRow, array &$newRow): ?bool

{

    // Enter your code here

    // To cancel, set return value to false

    // To skip for grid insert/update, set return value to null   

    $newRow["CreateDate"]  = CurrentDateTime();

    $newRow["CreatedBy"]   = CurrentUserID();

    $usergroupid = ExecuteScalar("SELECT UserGroupId FROM Users WHERE UserId =".CurrentUserID());

    $newRow["UserGroupId"] = $usergroupid;

    return true;

}