/**
* returns array of all users
* [userID] => 23103741
* [name] => admin
* [mail] => 0
* [active] => 0
*
* @param int $trash
* @param array $groups list of group ids the users must be a member of
* @return array
* @author th
*/
public function get_users($trash = 0, array $groups = null)
{
$p = $this->kga['server_prefix'];
$trash = MySQL::SQLValue($trash, MySQL::SQLVALUE_NUMBER);
if (empty($groups)) {
$query = "SELECT * FROM {$p}users\n WHERE trash = {$trash}\n ORDER BY name ;";
} else {
$query = "SELECT DISTINCT u.* FROM {$p}users AS u\n JOIN {$p}groups_users AS g_u USING(userID)\n WHERE g_u.groupID IN (" . implode($groups, ',') . ") AND\n trash = {$trash}\n ORDER BY name ;";
}
$this->conn->Query($query);
$rows = $this->conn->RowArray(0, MYSQLI_ASSOC);
$i = 0;
$arr = array();
$this->conn->MoveFirst();
while (!$this->conn->EndOfSeek()) {
$row = $this->conn->Row();
$arr[$i]['userID'] = $row->userID;
$arr[$i]['name'] = $row->name;
$arr[$i]['globalRoleID'] = $row->globalRoleID;
$arr[$i]['mail'] = $row->mail;
$arr[$i]['active'] = $row->active;
$arr[$i]['trash'] = $row->trash;
if ($row->password != '' && $row->password != '0') {
$arr[$i]['passwordSet'] = "yes";
} else {
$arr[$i]['passwordSet'] = "no";
}
$i++;
}
return $arr;
}
}
// --- Query and show the data --------------------------------------
// (Note: $db->Query also returns the result set)
if ($db->Query("SELECT * FROM Test")) {
echo $db->GetHTML();
} else {
echo "<p>Query Failed</p>";
}
// --- Getting the record count is easy -----------------------------
echo "\n<p>Record Count: " . $db->RowCount() . "</p>\n";
// --- Loop through the records -------------------------------------
while ($row = $db->Row()) {
echo $row->Color . " - " . $row->Age . "<br />\n";
}
// --- Loop through the records another way -------------------------
$db->MoveFirst();
while (!$db->EndOfSeek()) {
$row = $db->Row();
echo $row->Color . " - " . $row->Age . "<br />\n";
}
// --- Loop through the records with an index -----------------------
for ($index = 0; $index < $db->RowCount(); $index++) {
$row = $db->Row($index);
echo "Row " . $index . ":" . $row->Color . " - " . $row->Age . "<br />\n";
}
// --- We can even grab array data from the last result set ---------
$myArray = $db->RecordsArray();
// --- List all of the tables in the database -----------------------
$tables = $db->GetTables();
foreach ($tables as $table) {
echo $table . "<br />\n";
function backupDatabase($dbName, $backupStructure = true, $backupData = true, $backupFile = null)
{
$columnName = 'Tables_in_' . $dbName;
$this->done = false;
$this->output .= "-- SQL Dump File \n";
$this->output .= "-- Generation Time: " . date('M j, Y') . " at " . date('h:i:s A') . " \n\n";
$this->output .= "-- \n";
$this->output .= "-- Database: `{$dbName}` \n";
$this->output .= "-- \n\n";
$conn = new MySQL(true, DBNAME, DBHOST, DBUSER, DBPASS, "", true);
$strTables = 'SHOW TABLES';
$conn->Query($strTables);
$cntTables = $conn->RowCount();
if ($cntTables) {
$conn->MoveFirst();
while (!$conn->EndOfSeek()) {
$rsTables = $conn->Row();
if ($backupStructure) {
$this->dumpTableStructure($rsTables->{$columnName});
}
if ($backupData) {
$this->dumpTableData($rsTables->{$columnName});
}
}
} else {
$this->output .= "-- \n";
$this->output .= "-- No tables in {$dbName} \n";
$this->output .= "-- \n";
}
if (!is_null($backupFile)) {
$this->dumpToFile($backupFile);
}
$this->done = true;
}
$createDatabaseAllowed = false;
$result = $con->Query('SHOW GRANTS');
while ($row = $con->RowArray(null, MYSQLI_NUM)) {
if (strpos($row[0], 'SHOW DATABASES') !== false) {
$showDatabasesAllowed = true;
} elseif (strpos($row[0], 'CREATE,') !== false) {
$createDatabaseAllowed = true;
} elseif (strpos($row[0], 'ALL PRIVILEGES') !== false) {
$showDatabasesAllowed = true;
$createDatabaseAllowed = true;
}
}
$useDatabases = array();
if ($showDatabasesAllowed) {
try {
$con->MoveFirst();
$result = $con->Query('SHOW DATABASES');
while ($row = $con->RowArray(-2, MYSQLI_NUM)) {
if ($row[0] != 'information_schema' && $row[0] != 'mysql') {
$useDatabases[] = $row[0];
}
}
} catch (Exception $ex) {
// we cannot always detect if we can read read databases, see https://github.com/kimai/kimai/issues/492
// no need for error handling, user will see an input field for the database name
}
}
if (count($useDatabases) == 0) {
if ($lang == 'de') {
echo 'Keine Datenbank(en) vorhanden oder keine Berechtigung um Datenbanken aufzulisten. Name der zu verwendenden Datenbank:<br/>';
} else {
/**
* returns expenses for specific user as multidimensional array
* @TODO: needs comments
* @param integer $users ID of user in table users
* @param integer $start
* @param integer $end
* @param integer $filterCleared
* @return array
* @author th
* @author Alexander Bauer
*/
public function get_expenses($start, $end, $users = null, $customers = null, $projects = null, $reverse_order = false, $filter_refundable = -1, $filterCleared = null, $startRows = 0, $limitRows = 0, $countOnly = false)
{
$conn = $this->conn;
$kga = $this->kga;
// -1 for disabled, 0 for only not cleared entries
if (!is_numeric($filterCleared)) {
$filterCleared = -1;
if ($kga->getSettings()->isHideClearedEntries()) {
$filterCleared = 0;
}
}
$start = MySQL::SQLValue($start, MySQL::SQLVALUE_NUMBER);
$end = MySQL::SQLValue($end, MySQL::SQLVALUE_NUMBER);
$p = $kga['server_prefix'];
$whereClauses = $this->dbLayer->timeSheet_whereClausesFromFilters($users, $customers, $projects);
if (isset($kga['customer'])) {
$whereClauses[] = "{$p}projects.internal = 0";
}
if (!empty($start)) {
$whereClauses[] = "timestamp >= {$start}";
}
if (!empty($end)) {
$whereClauses[] = "timestamp <= {$end}";
}
if ($filterCleared > -1) {
$whereClauses[] = "cleared = {$filterCleared}";
}
switch ($filter_refundable) {
case 0:
$whereClauses[] = "refundable > 0";
break;
case 1:
$whereClauses[] = "refundable <= 0";
break;
case -1:
default:
// return all expenses - refundable and non refundable
}
if (!empty($limitRows)) {
$startRows = (int) $startRows;
$limit = "LIMIT {$startRows}, {$limitRows}";
} else {
$limit = "";
}
$select = "SELECT expenseID, timestamp, multiplier, value, projectID, designation, userID, projectID,\n \t\t\t\t\tcustomerName, customerID, projectName, comment, refundable,\n \t\t\t\t\tcommentType, userName, cleared";
$where = empty($whereClauses) ? '' : "WHERE " . implode(" AND ", $whereClauses);
$orderDirection = $reverse_order ? 'ASC' : 'DESC';
if ($countOnly) {
$select = "SELECT COUNT(*) AS total";
$limit = "";
}
$query = "{$select}\n \t\t\tFROM {$p}expenses\n\t \t\tJoin {$p}projects USING(projectID)\n\t \t\tJoin {$p}customers USING(customerID)\n\t \t\tJoin {$p}users USING(userID)\n\t \t\t{$where}\n\t \t\tORDER BY timestamp {$orderDirection} {$limit}";
$conn->Query($query);
// return only the number of rows, ignoring LIMIT
if ($countOnly) {
$this->conn->MoveFirst();
$row = $this->conn->Row();
return $row->total;
}
$i = 0;
$arr = array();
$conn->MoveFirst();
// toArray();
while (!$conn->EndOfSeek()) {
$row = $conn->Row();
$arr[$i] = (array) $row;
$i++;
}
return $arr;
}
