function test_4_negate_last_query()
{
$where = new WhereClause('and');
$where->add('password=%s', 'hello');
$subclause = $where->addClause('or');
$subclause->add('username!=%s', 'Bart');
$where->negateLast();
$result = DB::query("SELECT * FROM accounts WHERE %l", $where);
$this->assert(count($result) === 1);
$this->assert($result[0]['age'] === '15');
}
/**
* identifyUserRights()
*
* @return
*/
function identifyUserRights($groupesVisiblesUser, $groupesInterditsUser, $isAdmin, $idFonctions, $refresh)
{
global $server, $user, $pass, $database, $pre, $port, $encoding;
//load ClassLoader
require_once $_SESSION['settings']['cpassman_dir'] . '/sources/SplClassLoader.php';
//Connect to DB
require_once $_SESSION['settings']['cpassman_dir'] . '/includes/libraries/Database/Meekrodb/db.class.php';
DB::$host = $server;
DB::$user = $user;
DB::$password = $pass;
DB::$dbName = $database;
DB::$port = $port;
DB::$encoding = $encoding;
DB::$error_handler = 'db_error_handler';
$link = mysqli_connect($server, $user, $pass, $database, $port);
$link->set_charset($encoding);
//Build tree
$tree = new SplClassLoader('Tree\\NestedTree', $_SESSION['settings']['cpassman_dir'] . '/includes/libraries');
$tree->register();
$tree = new Tree\NestedTree\NestedTree(prefix_table("nested_tree"), 'id', 'parent_id', 'title');
// Check if user is ADMINISTRATOR
if ($isAdmin == 1) {
$groupesVisibles = array();
$_SESSION['groupes_visibles'] = array();
$_SESSION['groupes_interdits'] = array();
$_SESSION['personal_visible_groups'] = array();
$_SESSION['list_restricted_folders_for_items'] = array();
$_SESSION['groupes_visibles_list'] = "";
$rows = DB::query("SELECT id FROM " . prefix_table("nested_tree") . " WHERE personal_folder = %i", 0);
foreach ($rows as $record) {
array_push($groupesVisibles, $record['id']);
}
$_SESSION['groupes_visibles'] = $groupesVisibles;
$_SESSION['all_non_personal_folders'] = $groupesVisibles;
// Exclude all PF
$_SESSION['forbiden_pfs'] = array();
//$sql = "SELECT id FROM ".prefix_table("nested_tree")." WHERE personal_folder = 1";
$where = new WhereClause('and');
// create a WHERE statement of pieces joined by ANDs
$where->add('personal_folder=%i', 1);
if (isset($_SESSION['settings']['enable_pf_feature']) && $_SESSION['settings']['enable_pf_feature'] == 1) {
//$sql .= " AND title != '".$_SESSION['user_id']."'";
$where->add('title=%s', $_SESSION['user_id']);
$where->negateLast();
}
// Get ID of personal folder
$pf = DB::queryfirstrow("SELECT id FROM " . prefix_table("nested_tree") . " WHERE title = %s", $_SESSION['user_id']);
if (!empty($pf['id'])) {
if (!in_array($pf['id'], $_SESSION['groupes_visibles'])) {
array_push($_SESSION['groupes_visibles'], $pf['id']);
array_push($_SESSION['personal_visible_groups'], $pf['id']);
// get all descendants
$tree = new Tree\NestedTree\NestedTree(prefix_table("nested_tree"), 'id', 'parent_id', 'title', 'personal_folder');
$tree->rebuild();
$tst = $tree->getDescendants($pf['id']);
foreach ($tst as $t) {
array_push($_SESSION['groupes_visibles'], $t->id);
array_push($_SESSION['personal_visible_groups'], $t->id);
}
}
}
$_SESSION['groupes_visibles_list'] = implode(',', $_SESSION['groupes_visibles']);
$_SESSION['is_admin'] = $isAdmin;
// Check if admin has created Folders and Roles
DB::query("SELECT * FROM " . prefix_table("nested_tree") . "");
$_SESSION['nb_folders'] = DB::count();
DB::query("SELECT * FROM " . prefix_table("roles_title"));
$_SESSION['nb_roles'] = DB::count();
} else {
// init
$_SESSION['groupes_visibles'] = array();
$_SESSION['groupes_interdits'] = array();
$_SESSION['personal_visible_groups'] = array();
$_SESSION['read_only_folders'] = array();
$groupesVisibles = array();
$groupesInterdits = array();
$groupesInterditsUser = explode(';', trimElement($groupesInterditsUser, ";"));
if (!empty($groupesInterditsUser) && count($groupesInterditsUser) > 0) {
$groupesInterdits = $groupesInterditsUser;
}
$_SESSION['is_admin'] = $isAdmin;
$fonctionsAssociees = explode(';', trimElement($idFonctions, ";"));
$newListeGpVisibles = array();
$listeGpInterdits = array();
$listAllowedFolders = $listForbidenFolders = $listFoldersLimited = $listFoldersEditableByRole = $listRestrictedFoldersForItems = $listReadOnlyFolders = $listNoAccessFolders = array();
// rechercher tous les groupes visibles en fonction des roles de l'utilisateur
foreach ($fonctionsAssociees as $roleId) {
if (!empty($roleId)) {
// Get allowed folders for each Role
$rows = DB::query("SELECT folder_id FROM " . prefix_table("roles_values") . " WHERE role_id=%i", $roleId);
if (DB::count() > 0) {
$tmp = DB::queryfirstrow("SELECT allow_pw_change FROM " . prefix_table("roles_title") . " WHERE id = %i", $roleId);
foreach ($rows as $record) {
if (isset($record['folder_id']) && !in_array($record['folder_id'], $listAllowedFolders)) {
array_push($listAllowedFolders, $record['folder_id']);
//echo $record['folder_id'].";";
}
// Check if this group is allowed to modify any pw in allowed folders
if ($tmp['allow_pw_change'] == 1 && !in_array($record['folder_id'], $listFoldersEditableByRole)) {
array_push($listFoldersEditableByRole, $record['folder_id']);
}
}
// Check for the users roles if some specific rights exist on items
$rows = DB::query("SELECT i.id_tree, r.item_id\n FROM " . prefix_table("items") . " as i\n INNER JOIN " . prefix_table("restriction_to_roles") . " as r ON (r.item_id=i.id)\n WHERE r.role_id=%i\n ORDER BY i.id_tree ASC", $roleId);
$x = 0;
foreach ($rows as $record) {
if (isset($record['id_tree'])) {
$listFoldersLimited[$record['id_tree']][$x] = $record['item_id'];
$x++;
}
}
}
}
}
// Does this user is allowed to see other items
$x = 0;
$rows = DB::query("SELECT id, id_tree FROM " . prefix_table("items") . "\n WHERE restricted_to=%ss AND inactif=%s", $_SESSION['user_id'], '0');
foreach ($rows as $record) {
$listRestrictedFoldersForItems[$record['id_tree']][$x] = $record['id'];
$x++;
// array_push($listRestrictedFoldersForItems, $record['id_tree']);
}
// => Build final lists
// Clean arrays
$allowedFoldersTmp = array();
$listAllowedFolders = array_unique($listAllowedFolders);
// Add user allowed folders
$allowedFoldersTmp = array_unique(array_merge($listAllowedFolders, explode(';', trimElement($groupesVisiblesUser, ";"))));
// Exclude from allowed folders all the specific user forbidden folders
$allowedFolders = array();
foreach ($allowedFoldersTmp as $id) {
if (!in_array($id, $groupesInterditsUser) && !empty($id)) {
array_push($allowedFolders, $id);
}
}
// Clean array
$listAllowedFolders = array_filter(array_unique($allowedFolders));
// Exclude all PF
$_SESSION['forbiden_pfs'] = array();
$where = new WhereClause('and');
$where->add('personal_folder=%i', 1);
if (isset($_SESSION['settings']['enable_pf_feature']) && $_SESSION['settings']['enable_pf_feature'] == 1 && isset($_SESSION['personal_folder']) && $_SESSION['personal_folder'] == 1) {
$where->add('title=%s', $_SESSION['user_id']);
$where->negateLast();
}
$pfs = DB::query("SELECT id FROM " . prefix_table("nested_tree") . " WHERE %l", $where);
foreach ($pfs as $pfId) {
array_push($_SESSION['forbiden_pfs'], $pfId['id']);
}
// Get ID of personal folder
if (isset($_SESSION['settings']['enable_pf_feature']) && $_SESSION['settings']['enable_pf_feature'] == 1 && isset($_SESSION['personal_folder']) && $_SESSION['personal_folder'] == 1) {
$pf = DB::queryfirstrow("SELECT id FROM " . prefix_table("nested_tree") . " WHERE title = %s", $_SESSION['user_id']);
if (!empty($pf['id'])) {
if (!in_array($pf['id'], $listAllowedFolders)) {
// get all descendants
$ids = $tree->getDescendants($pf['id'], true);
foreach ($ids as $id) {
array_push($listAllowedFolders, $id->id);
array_push($_SESSION['personal_visible_groups'], $id->id);
}
}
}
}
// get list of readonly folders
// rule - if one folder is set as W in one of the Role, then User has access as W
foreach ($listAllowedFolders as $folderId) {
if (!in_array($folderId, $listReadOnlyFolders) || isset($pf) && $folderId != $pf['id']) {
DB::query("SELECT *\n FROM " . prefix_table("roles_values") . "\n WHERE folder_id = %i AND role_id IN %li AND type = %s", $folderId, $fonctionsAssociees, "W");
if (DB::count() == 0) {
array_push($listReadOnlyFolders, $folderId);
}
}
}
$_SESSION['all_non_personal_folders'] = $listAllowedFolders;
$_SESSION['groupes_visibles'] = $listAllowedFolders;
$_SESSION['groupes_visibles_list'] = implode(',', $listAllowedFolders);
$_SESSION['read_only_folders'] = $listReadOnlyFolders;
$_SESSION['list_folders_limited'] = $listFoldersLimited;
$_SESSION['list_folders_editable_by_role'] = $listFoldersEditableByRole;
$_SESSION['list_restricted_folders_for_items'] = $listRestrictedFoldersForItems;
// Folders and Roles numbers
DB::queryfirstrow("SELECT id FROM " . prefix_table("nested_tree") . "");
$_SESSION['nb_folders'] = DB::count();
DB::queryfirstrow("SELECT id FROM " . prefix_table("roles_title"));
$_SESSION['nb_roles'] = DB::count();
}
// update user's timestamp
DB::update(prefix_table('users'), array('timestamp' => time()), "id=%i", $_SESSION['user_id']);
}
