/**
* Build sql for the query
* @param array $fields - fields to be displayed
* @return stdClass - query string with replacement values
*/
private function buildQuery(array $fields)
{
// Builds the query based on the filters in place, and the columns selected.
//
// parameters
// $fields - fields to be listed in the table, misc table only
// returns the a string - query for the database
$query = "select ";
// selection to be displayed
$sqlFields = "m." . implode(", m.", $this->db->qt($fields)) . ", u.UserId, ";
// calculate if the user is valid
$sqlFields .= "case when (";
foreach ($this->cfg->validUserFlags as $flag => $value) {
$flag = "u." . $this->db->qt($flag);
if ($value) {
$sqlFields .= "(1 - " . $flag . ") + ";
} else {
$sqlFields .= $flag . " + ";
}
}
$sqlFields = substr($sqlFields, 0, -2);
$sqlFields .= ") = 0 then u.UserAccess else 'Fault' end as UserStatus ";
// tables
$sqlTables = "from " . $this->cfg->userTable . " u ";
$sqlTables .= "left join " . $this->cfg->userMiscTable . " m on u." . $this->db->qt("UserId") . "= m." . $this->db->qt("UserId") . " ";
// where condition
$sqlwhereCond = "where ";
$where = array();
$whereF = array();
$sqlValues = array();
// user access filtering
if ($this->filter->userAccess != "all") {
$where[] = "u." . $this->db->qt("UserAccess") . " = ? ";
$sqlValues[] = $this->filter->userAccess;
}
// alphabetical filtering
if (strcmp($this->filter->alpha, 'all') !== 0) {
$where[] = $this->db->qt($this->filter->alphaField) . " like ? ";
$sqlValues[] = $this->filter->alpha . "%";
}
// get any filters specified
foreach ($this->filter->filterValues as $key => $value) {
if (trim($value) != "") {
$whereF[] = $this->db->qt($this->filter->filterFields[$key]) . " like ? ";
$sqlValues[] = "%" . $value . "%";
}
}
// if one or more filters have been specified, create a section with the appropriate logic
if (count($whereF)) {
$whereFilter = "(";
if (!$this->filter->filterOr) {
$whereFilter .= implode("and ", $whereF);
} else {
$whereFilter .= implode("or ", $whereF);
}
$whereFilter .= ") ";
$where[] = $whereFilter;
}
if ($this->filter->filterOnFlags) {
if ($this->filter->userDisabled != 2) {
$whereFl[] = "u." . $this->db->qt("UserDisabled") . " = ? ";
$sqlValues[] = $this->filter->userDisabled;
}
if ($this->filter->userPending != 2) {
$whereFl[] = "u." . $this->db->qt("UserPending") . " = ? ";
$sqlValues[] = $this->filter->userPending;
}
if ($this->filter->userAdminPending != 2) {
$whereFl[] = "u." . $this->db->qt("UserAdminPending") . " = ? ";
$sqlValues[] = $this->filter->userAdminPending;
}
if ($this->filter->creationFinished != 2) {
$whereFl[] = "u." . $this->db->qt("CreationFinished") . " = ? ";
$sqlValues[] = $this->filter->creationFinished;
}
if ($this->filter->userWaiting != 2) {
$whereFl[] = "u." . $this->db->qt("UserWaiting") . " = ? ";
$sqlValues[] = $this->filter->userWaiting;
}
if (!$this->filter->filterOr) {
foreach ($whereFl as $key => $value) {
$where[] = $value;
}
} else {
$where[] = '(' . implode('or ', $whereFl) . ') ';
}
}
if (count($where)) {
$sqlwhereCond .= implode("and ", $where);
} else {
$sqlwhereCond = " ";
}
// order list by the alpha field
$sqlOrder = "order by " . $this->db->qt($this->filter->alphaField);
$result = new \stdClass();
$result->sql = $query . $sqlFields . $sqlTables . $sqlwhereCond . $sqlOrder;
$result->values = $sqlValues;
return $result;
}
