/**
* Generates a huge array for the report
* @return array
*/
public function getReportingData()
{
$report = array();
$query = sprintf("\n SELECT\n fd.id AS id,\n fd.lang AS lang,\n fcr.category_id AS category_id,\n c.name as category_name,\n c.parent_id as parent_id,\n fd.sticky AS sticky,\n fd.thema AS question,\n fd.author AS original_author,\n fd.datum AS creation_date,\n fv.visits AS visits,\n u.display_name AS last_author\n FROM\n %sfaqdata fd\n LEFT JOIN\n %sfaqcategoryrelations fcr\n ON\n (fd.id = fcr.record_id AND fd.lang = fcr.record_lang)\n LEFT JOIN\n %sfaqvisits fv\n ON\n (fd.id = fv.id AND fd.lang = fv.lang)\n LEFT JOIN\n %sfaqchanges as fc\n ON\n (fd.id = fc.id AND fd.lang = fc.lang)\n LEFT JOIN\n %sfaquserdata as u\n ON\n (u.user_id = fc.usr)\n LEFT JOIN\n %sfaqcategories as c\n ON\n (c.id = fcr.category_id AND c.lang = fcr.record_lang)\n ORDER BY\n fd.id\n ASC", SQLPREFIX, SQLPREFIX, SQLPREFIX, SQLPREFIX, SQLPREFIX, SQLPREFIX);
$result = $this->db->query($query);
$lastId = 0;
while ($row = $this->db->fetch_object($result)) {
if ($row->id == $lastId) {
$report[$row->id]['faq_translations'] += 1;
} else {
$report[$row->id] = array('faq_id' => $row->id, 'faq_language' => $row->lang, 'category_id' => $row->category_id, 'category_parent' => $row->parent_id, 'category_name' => $row->category_name, 'faq_translations' => 0, 'faq_sticky' => $row->sticky, 'faq_question' => $row->question, 'faq_org_author' => $row->original_author, 'faq_creation' => PMF_Date::createIsoDate($row->creation_date), 'faq_visits' => $row->visits, 'faq_last_author' => $row->last_author);
}
$lastId = $row->id;
}
return $report;
}
/**
* @param array $queries
*/
public function dropTables(array $queries)
{
if ($this->_database instanceof PMF_DB_Driver) {
foreach ($queries as $query) {
$this->_database->query($query);
}
}
}
/**
* Get all the entries from the table faqvisits
*
* @return array
*/
function getAllData()
{
$data = array();
$query = sprintf("\n SELECT\n *\n FROM\n %sfaqvisits\n ORDER BY\n visits DESC", SQLPREFIX);
$result = $this->db->query($query);
while ($row = $this->db->fetch_object($result)) {
$data[] = array('id' => $row->id, 'lang' => $row->lang, 'visits' => $row->visits, 'last_visit' => $row->last_visit);
}
return $data;
}
/**
* Checks the number of entries of given login name
*
* @param string $login Loginname
* @param array $optionslData Optional data
* @return integer
*/
public function checkLogin($login, array $optionalData = null)
{
$check = sprintf("\n SELECT\n login\n FROM\n %sfaquserlogin\n WHERE\n login = '******'", SQLPREFIX, $this->db->escape_string($login));
$check = $this->db->query($check);
$error = $this->db->error();
if (strlen($error) > 0) {
$this->errors[] = $error;
return 0;
}
return $this->db->num_rows($check);
}
/**
* Checks the number of entries of given login name
*
* @param string $login Loginname
* @param array $optionalData Optional data
*
* @return integer
*/
public function checkLogin($login, array $optionalData = null)
{
$check = sprintf("\n SELECT\n login\n FROM\n %sfaquserlogin\n WHERE\n login = '******'", PMF_Db::getTablePrefix(), $this->db->escape($login));
$check = $this->db->query($check);
$error = $this->db->error();
if (strlen($error) > 0) {
$this->errors[] = $error;
return 0;
}
return $this->db->numRows($check);
}
/**
* Deletes the user-data entry for the given user-ID $user_id.
* Returns true on success, otherwise false.
*
* @param integer $user_id User ID
* @return bool
*/
public function delete($user_id)
{
$user_id = (int) $user_id;
if ($user_id <= 0 && $user_id != -1) {
return false;
}
$this->user_id = $user_id;
$delete = sprintf("\n DELETE FROM\n %sfaquserdata\n WHERE\n user_id = %d", SQLPREFIX, $this->user_id);
$res = $this->db->query($delete);
if (!$res) {
return false;
}
$this->data = array();
return true;
}
/**
* Returns an array of all users found in the database. By default, the
* anonymous User will not be returned. The returned array contains the
* user ID as key, the values are login name, account status, authentication
* source and the user creation date.
*
* @param boolean $withoutAnonymous Without anonymous?
* @return array
*/
public function getAllUserData($withoutAnonymous = true)
{
$select = sprintf("\n SELECT\n user_id, login, account_status, auth_source, member_since\n FROM\n %sfaquser\n %s\n ORDER BY\n login ASC", SQLPREFIX, $withoutAnonymous ? 'WHERE user_id <> -1' : '');
$res = $this->db->query($select);
if (!$res) {
return array();
}
$result = array();
while ($row = $this->db->fetch_assoc($res)) {
$result[$row['user_id']] = $row;
}
return $result;
}
/**
* Delete old captcha records.
*
* During normal use the <b>faqcaptcha</b> table would be empty, on average:
* each record is created when a captcha image is showed to the user
* and deleted upon a successful matching, so, on average, a record
* in this table is probably related to a spam attack.
*
* @param int $time The time (sec) to define a captcha code old and ready
* to be deleted (default: 1 week)
* @return void
*/
private function garbageCollector($time = 604800)
{
$delete = sprintf("\n DELETE FROM \n %sfaqcaptcha \n WHERE \n captcha_time < %d", SQLPREFIX, $_SERVER['REQUEST_TIME'] - $time);
$this->db->query($delete);
}
/**
* Prepares the search and executes it
*
* @param string $searchTerm Search term
*
* @return resource
*
* @throws PMF_Search_Exception
*/
public function search($searchTerm)
{
$query = sprintf("\n SELECT\n %s\n FROM \n %s %s %s\n WHERE\n %s = %d", $this->getResultColumns(), $this->getTable(), $this->getJoinedTable(), $this->getJoinedColumns(), $this->getMatchingColumns(), $searchTerm);
$this->resultSet = $this->dbHandle->query($query);
}
/**
* Returns an array with the user-IDs of all users found in
* the database. By default, the Anonymous User will not be returned.
*
* @param boolean $withoutAnonymous Without anonymous?
* @return array
*/
public function getAllUsers($withoutAnonymous = true)
{
$select = sprintf("\n SELECT\n user_id\n FROM\n %sfaquser\n %s\n ORDER BY\n login ASC", SQLPREFIX, $withoutAnonymous ? 'WHERE user_id <> -1' : '');
$res = $this->db->query($select);
if (!$res) {
return array();
}
$result = array();
while ($row = $this->db->fetch_assoc($res)) {
$result[] = $row['user_id'];
}
return $result;
}