function test_6_or()
{
$where = new WhereClause('or');
$where->add('username=%s', 'Bart');
$where->add('username=%s', 'Abe');
$result = DB::query("SELECT * FROM accounts WHERE %l", $where);
$this->assert(count($result) === 2);
}
$logs .= '<tr><td>' . date($_SESSION['settings']['date_format'] . " " . $_SESSION['settings']['time_format'], $reccord['date']) . '</td><td align=\\"left\\">' . $label[0] . '</td><td align=\\"center\\">' . $reccord['login'] . '</td></tr>';
}
echo '[{"tbody_logs": "' . $logs . '" , "log_pages" : "' . $pages . '"}]';
break;
/**
* CASE admin want to see COPIES logs
*/
/**
* CASE admin want to see COPIES logs
*/
case "admin_logs":
$logs = $sqlFilter = "";
$nbPages = 1;
$pages = '<table style=\'border-top:1px solid #969696;\'><tr><td>' . $LANG['pages'] . ' : </td>';
$where = new WhereClause('and');
$where->add('l.type=%s', "admin_action");
if (isset($_POST['filter']) && !empty($_POST['filter'])) {
$where->add('i.label=%ss', $_POST['filter']);
}
if (isset($_POST['filter_user']) && !empty($_POST['filter_user'])) {
$where->add('i.id_user=%ss', $_POST['filter_user']);
}
//get number of pages
DB::query("SELECT * FROM " . prefix_table("log_system") . " as l\n INNER JOIN " . prefix_table("users") . " as u ON (l.qui=u.id)\n WHERE %l", $where);
if ($data[0] != 0) {
$nbPages = ceil($data[0] / $nbElements);
for ($i = 1; $i <= $nbPages; $i++) {
$pages .= '<td onclick=\'displayLogs(\\"copy_logs\\", ' . $i . ', \'\')\'><span style=\'cursor:pointer;' . ($_POST['page'] == $i ? 'font-weight:bold;font-size:18px;\'>' . $i : '\'>' . $i) . '</span></td>';
}
}
$pages .= '</tr></table>';
/**
* 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']);
}
header("Content-type: text/html; charset==utf-8");
//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::$error_handler = 'db_error_handler';
$link = mysqli_connect($server, $user, $pass, $database, $port);
//Columns name
$aColumns = array('id', 'label', 'description', 'tags', 'id_tree', 'folder', 'login');
//init SQL variables
$sOrder = $sLimit = "";
$where = new WhereClause('and');
$where->add('id_tree IN %ls', $_SESSION['groupes_visibles']);
//limit search to the visible folders
//get list of personal folders
$array_pf = array();
$list_pf = "";
$rows = DB::query("SELECT id FROM " . $pre . "nested_tree WHERE personal_folder=%i AND NOT title = %s", 1, $_SESSION['user_id']);
foreach ($rows as $reccord) {
if (!in_array($reccord['id'], $array_pf)) {
//build an array of personal folders ids
array_push($array_pf, $reccord['id']);
//build also a string with those ids
if (empty($list_pf)) {
$list_pf = $reccord['id'];
} else {
$list_pf .= ',' . $reccord['id'];
}
public static function timeInInterval($field, $start, $end)
{
$wc = new WhereClause('and');
if (!empty($start)) {
//Assume goes off to neg infinity
$wc->add('%b > %l', $field, self::timeRelativeToSQL($start));
}
if (!empty($end)) {
//Assume goes off to pos infinity
$wc->add('%b < %l', $field, self::timeRelativeToSQL($end));
}
return $wc;
}
} else {
// case complete database
//get list of personal folders
$arrayPf = array();
$listPf = "";
if (!empty($row['id'])) {
$rows = DB::query("SELECT id FROM " . prefix_table("nested_tree") . " WHERE personal_folder = %i", "1");
foreach ($rows as $record) {
if (!in_array($record['id'], $arrayPf)) {
array_push($arrayPf, $record['id']);
}
}
}
// build WHERE condition
$where = new WhereClause('and');
$where->add('id_tree = %i', $idFolder);
$where->add('label = %s', $label);
if (!empty($arrayPf)) {
$where->add("id_tree NOT IN (" . implode(',', $arrayPf) . ")");
}
DB::query("SELECT label\n FROM " . prefix_table("items") . "\n WHERE %l", $where);
}
// count results
if (DB::count() > 0) {
$duplicate = true;
}
// send data
echo '[{"duplicate" : "' . $duplicate . '" , "error" : ""}]';
}
break;
}
// THis is the The Main Page SQL Only. see post.php, etc for other individual page routines
// Using meekrodb for connecting to mySQL server for better security.
// You can specify string, integer, and decimal parameters
// by using %s, %i, and %d placeholders in the query,
// and attaching the parameters as shown below.
// The library will run all needed safety checks,
// such as escaping strings and making sure integers are really integers
if (empty($db_Where)) {
$db_Where = '';
}
if ($db_Page == 'db_List') {
if ($db_Where) {
$arrWhere = explode(',', $db_Where);
$where = new WhereClause('and');
// create a WHERE statement of pieces joined by ANDs
$where->add($arrWhere[0], $arrWhere[1]);
//$results = DB::query("SELECT * FROM $db_Table WHERE %l ", $where->text());
$results = DB::query("SELECT * FROM {$db_Table} WHERE {$arrWhere['0']}", $arrWhere[1]);
} else {
if (!empty($db_OrderBy)) {
$results = DB::query("SELECT * FROM {$db_Table} ORDER BY {$db_OrderBy}");
} else {
$results = DB::query("SELECT * FROM {$db_Table}");
}
}
}
// SETTING DEFAULTS IF NOT SET IN CONFIG
//Lets get the FieldNames, FieldCount and RcordCount
$arrFieldNames = DB::columnList($db_Table);
$intFieldCount = count($arrFieldNames);
$intRecordCount = DB::count();
private function updateIs($is, $setstr)
{
if (empty($this->Questions) || count($this->Questions) == 0) {
return;
}
//$setstr is risky.
$where = new WhereClause('or');
foreach ($is as $i) {
$where->add("QID=%i", $this->Questions[$i][0]);
}
DB::query("UPDATE questions SET {$setstr} WHERE (%l) LIMIT %i", $where, count($this->Questions));
}