public function GetRecommend()
{
$db = new DBHelper();
$sql = "select id,Title,Abstract,CommentCount,Readers,Likers,UpdateDate from mydairy where IsRecommend=1 limit 0,4";
$re = $db->get_all($sql);
return $re;
}
public function getBadWordNum()
{
$query = "SELECT * FROM " . BADWORD;
$dbHelper = new DBHelper();
$badword_num = $dbHelper->getNumOfRows($query);
return $badword_num;
}
function GetMyInfo()
{
$db = new DBHelper();
$sql = "SELECT * FROM `myinformation`";
$_reData = $db->get_all($sql);
return $_reData;
}
function LeaveMsg($msg)
{
$db = new DBHelper();
$db->insert("messageboard", $msg);
$msg["id"] = $db->insert_id();
return $msg;
}
public function getReportByReason($reason)
{
$query = "SELECT * FROM " . REPORT . " WHERE rep_reason LIKE '%" . $reason . "%' ORDER BY rep_reported_on DESC";
$dbHelper = new DBHelper();
$result = $dbHelper->executeQuery($query);
$reports_by_reason = $this->getReportList($result);
return $reports_reason;
}
public function getTreqBriefById($id)
{
$query = "SELECT\n r.treq_entry_id,\n l.lan_lang_name\n FROM \n " . TRANS_REQUEST . " AS r \n STRAIGHT_JOIN \n " . LANGUAGE . " AS l \n WHERE\n r.treq_target_lang_id = l.lan_language_id\n AND\n r.treq_id={$id}";
$dbHelper = new DBHelper();
$result = $dbHelper->executeQuery($query);
$treqGottenByEntryId = $this->getTreqBrief($result);
return $treqGottenByEntryId;
}
public function getAllUserTypes()
{
$dbHelper = new DBHelper();
$query = "SELECT * FROM " . USER_TYPE . " ORDER BY utp_usertype_id ASC";
$result = $dbHelper->executeQuery($query);
$Users = $this->getUserTypeList($result);
return $Users;
}
public function getLanguages()
{
$dbHelper = new DBHelper();
$query = "SELECT * FROM " . LANGUAGE . " order by lan_lang_name asc ";
$result = $dbHelper->executeQuery($query);
$Languages = $this->getListOfLang($result);
return $Languages;
}
public function getAuthenByName($authenName)
{
$dbHelper = new DBHelper();
//to escape the strings for inserting
$authen_name = $dbHelper->EscapeString($authenName);
$authen_name = strtoupper($authen_name);
$query = "SELECT * FROM " . AUTHEN . " WHERE UPPER(athn_stat_name) = " . "'" . $authen_name . "'";
$result = $dbHelper->executeQuery($query);
$authen = $this->getAuthen($result);
return $authen->getAuthenId();
}
public function Login($uname, $upwd)
{
$uname = $uname;
$upwd = sha1($upwd);
// returns all users with the names passed
//$query = "SELECT * FROM ".USER. " WHERE upper(usr_first_name) like " ."%JOHN%";
$query = "SELECT * FROM " . USER . " WHERE usr_login = '******' and usr_password='******'";
$dbHelper = new DBHelper();
$result = $dbHelper->executeSelect($query);
$Users = $this->getLogin($result);
return $Users;
}
public function AddProficient($ProfData)
{
$dbHelper = new DBHelper();
//All the data members are being passed through the escape string function
$userid = $dbHelper->EscapeString($ProfData->getUserId());
$langid = $dbHelper->EscapeString($ProfData->getLanguageId());
$prof = $dbHelper->EscapeString($ProfData->getProf());
$query_insert = "INSERT INTO " . LANGUAGE_PROF . " VALUES('', '{$userid}', '{$langid}','{$prof}')";
//echo $query_insert;
$result = $dbHelper->executeInsertQuery($query_insert);
//returns the last row inserted..
return $result;
}
public function setGroups($driver_id, $groupList)
{
DBHelper::run("DELETE FROM `drivers_groups` WHERE `driver_id` = " . $driver_id);
foreach ($groupList as $group_id) {
DBHelper::run("INSERT INTO `drivers_groups` SET `group_id` = " . $group_id . ", `driver_id` = " . $driver_id);
}
}
/**
* @brief create course
* @param type $public_code
* @param type $lang
* @param type $title
* @param string $description
* @param array $departments
* @param type $vis
* @param type $prof
* @param type $password
* @return boolean
*/
function create_course($public_code, $lang, $title, $description, $departments, $vis, $prof, $password = '') {
$code = strtoupper(new_code($departments[0]));
if (!create_course_dirs($code)) {
return false;
}
if (!$public_code) {
$public_code = $code;
}
$q = Database::get()->query("INSERT INTO course
SET code = ?s,
lang = ?s,
title = ?s,
keywords = '',
description = ?s,
visible = ?d,
prof_names = ?s,
public_code = ?s,
created = " . DBHelper::timeAfter() . ",
password = ?s,
glossary_expand = 0,
glossary_index = 1", $code, $lang, $title, $description, $vis, $prof, $public_code, $password);
if ($q) {
$course_id = $q->lastInsertID;
} else {
return false;
}
require_once 'include/lib/course.class.php';
$course = new Course();
$course->refresh($course_id, $departments);
return array($code, $course_id);
}
/**
* Create a new foreign key from $masterTableName to $detailTableName based
* on the $detailFieldName field
* @param type $detailTableName The detail table name
* @param type $detailFieldName The detail table's field name, which
* connects with the master table
* @param type $masterTableName The master table name
* @param type $defaultEntryResolver A numeric value or a function which
* returns a numeric value, in order to get the master id field value. This
* will be used as default for those entries of the detail table, who are
* orphaned (have a wrong reference). If this value is null, or if the
* function returns null, and the field does not accept null values, then
* the orphaned entries will be removed from the detail table and recycled
* to the recyclebin table.
*/
public static function create($detailTableName, $detailFieldName, $masterTableName, $defaultEntryResolver) {
$masterIDFieldName = DBHelper::primaryKeyOf($masterTableName);
if (DBHelper::foreignKeyExists($detailTableName, $detailFieldName, $masterTableName, $masterIDFieldName))
return;
$detailIDFieldName = DBHelper::primaryKeyOf($detailTableName);
$defaultEntryID = is_null($defaultEntryResolver) ? null :
(is_numeric($defaultEntryResolver) ? $defaultEntryResolver :
is_callable($defaultEntryResolver) ? $defaultEntryResolver() :
null);
$nullable = DBHelper::isColumnNullable($detailTableName, $detailFieldName);
$wrongIDs = Database::get()->queryArray("select `$detailTableName`.`$detailIDFieldName` as detailid from `$detailTableName`
left join `$masterTableName` on `$detailTableName`.`$detailFieldName` = `$masterTableName`.`$masterIDFieldName`
where `$detailTableName`.`$detailFieldName` is not null and `$masterTableName`.`$masterIDFieldName` is null");
if ($wrongIDs) {
foreach ($wrongIDs as $entry) {
if (is_null($defaultEntryID)) {
if ($nullable)
Database::get()->query("update `" . $detailTableName . "` set `" . $detailFieldName . "` = NULL where $detailIDFieldName = ?d"
, $entry->detailid);
else
Recycle::deleteObject($detailTableName, $entry->detailid, $detailIDFieldName);
} else {
Database::get()->query("update `" . $detailTableName . "` set `" . $detailFieldName . "` = ?d where $detailIDFieldName = ?d"
, $defaultEntryID, $entry->detailid);
}
}
}
DBHelper::createForeignKey($detailTableName, $detailFieldName, $masterTableName, $masterIDFieldName);
}
public static function getRandLessons($count)
{
$db = DBHelper::getConnection();
$result = $db->query("SELECT COUNT(*) max FROM lessons");
if (!empty($result)) {
$result->setFetchMode(PDO::FETCH_ASSOC);
$max = $result->fetch();
$max = $max["max"];
} else {
$max = 0;
}
if ($count > $max) {
$count = $max;
}
$ar_rang = array();
$ar_rand = range(1, $max);
shuffle($ar_rand);
$lessons = array();
for ($i = 0; $i < $count; $i++) {
$result = $db->query("SELECT * FROM lessons WHERE ID={$ar_rand[$i]}");
if (!empty($result)) {
$result->setFetchMode(PDO::FETCH_ASSOC);
$lessons[$i] = $result->fetch();
}
}
return $lessons;
}
public function setUserPrefs($userid, $changedprefs)
{
/*
* $changedprefs is a non-associative array with parameters in order,
* corresponding to $USERPREFS_COL
*/
// Convert $changedprefs to associative array
$changedprefs = self::convertToAssocArray(self::$USERPREFS_COL, $changedprefs);
$validprefs = array("show_boulder", "show_TR", "show_Lead", "show_project", "show_redpoint", "show_flash", "show_onsight", "minV", "maxV", "minTR", "maxTR", "minL", "maxL", "boulderGradingSystemID", "routeGradingSystemID");
$minValid = array("show_boulder" => 0, "show_TR" => 0, "show_Lead" => 0, "show_project" => 0, "show_redpoint" => 0, "show_flash" => 0, "show_onsight" => 0, "minV" => 0, "maxV" => 0, "minTR" => 0, "maxTR" => 0, "minL" => 0, "maxL" => 0, "boulderGradingSystemID" => 0, "routeGradingSystemID" => 0);
$maxValid = array("show_boulder" => 1, "show_TR" => 1, "show_Lead" => 1, "show_project" => 1, "show_redpoint" => 1, "show_flash" => 1, "show_onsight" => 1, "minV" => 22, "maxV" => 22, "minTR" => 30, "maxTR" => 30, "minL" => 30, "maxL" => 30, "boulderGradingSystemID" => 2, "routeGradingSystemID" => 9);
$prefsarevalid = true;
foreach ($changedprefs as $prefname => $prefvalue) {
//check if not numeric or not within valid range
if (!(is_numeric($prefvalue) && $prefvalue >= $minValid[$prefname] && $prefvalue <= $maxValid[$prefname])) {
$prefsarevalid = false;
throw new Exception("The following key-value is not valid: " . $prefname . "=>" . $prefvalue);
break;
//break out of foreach loop if not valid
}
}
if ($prefsarevalid) {
//prefs are valid, so write to database
$selectKeyValue = ["userid", $userid];
return DBHelper::performUpdateQuery($this->db, self::USERPREFS_TABLENAME, $changedprefs, $selectKeyValue);
} else {
return false;
}
}
public static function Instance()
{
if (self::$instance == null) {
self::$instance = new DBHelper();
}
return self::$instance;
}
public function filterLinks()
{
$filter = array("gift", "feedback", "login", "register", "kaidian", "sitemap");
$db = new DBHelper();
$db->selectDB("FoodOrder");
for ($i = 0, $len = count($this->links[0][0]); $i < $len; $i++) {
if ($this->links[0][2][$i] == "" || in_array($this->links[0][1][$i], $filter)) {
unset($this->links[0][0][$i]);
unset($this->links[0][1][$i]);
unset($this->links[0][2][$i]);
} else {
$this->links[0][1][$i] = "http://" . $this->links[0][1][$i] . ".ele.me/";
$sql = "insert into site (sitename,siteurl) values ('" . $this->links[0][2][$i] . "','" . $this->links[0][1][$i] . "')";
$db->query($sql);
}
}
}
public function execSQL($query, $multi = false)
{
$res = DBHelper::exec($query, $multi);
if ($res != null) {
$this->error++;
$this->printStepMsg($res, true);
}
return true;
}
public function updateUserData($userid, $propValArray)
{
/*
* Input: $userid = user ID
* $propValArray = array("prop1"=>val1, "prop2"=>val2), an array of properties to update in the userdata table
*/
$selectKeyValue = ["userid", $userid];
return DBHelper::performUpdateQuery($this->db, self::USERDATA_TABLENAME, $propValArray, $selectKeyValue);
}
private function __construct()
{
try {
self::$db = new PDO('mysql:host=' . DB_HOST . ';dbname=' . DB_NAME, DB_USER, DB_PASSWORD);
// self::$db = new PDO('mysql:host=localhost;dbname=FMC', 'root', '');
self::$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo 'blad polaczenia z baza danych';
}
}
public function deleteDatabaseTables()
{
global $wgDBtype;
$db =& wfGetDB(DB_MASTER);
$verbose = true;
DBHelper::reportProgress("Dropping materialization tables ...\n", $verbose);
$db->query('DROP TABLE' . ($wgDBtype == 'postgres' ? '' : ' IF EXISTS') . $this->smw_ws_materialization_hashes, 'WSStorageSQL::drop');
DBHelper::reportProgress(" ... dropped table {$this->smw_ws_materialization_hashes}.\n", $verbose);
DBHelper::reportProgress(" ... done!\n", $verbose);
}
function displayAllMainPageCategoryItems()
{
$category_ids = DBHelper::verticalSlice(Category::getCategories(), "id");
$html = "";
for ($i = 0; $i < count($category_ids); $i++) {
$html .= sprintf('<div data-tab="%d" class="category-items hidden">', $i + 1);
$html .= displayMainPageCategoryItems($category_ids[$i]);
$html .= '</div>';
}
return $html;
}
function test_4_null()
{
DB::query("UPDATE accounts SET password = NULL WHERE username=%s", 'Bart');
$all = DB::query("SELECT * FROM accounts ORDER BY id ASC");
$ages = DBHelper::verticalSlice($all, 'age', 'password');
$this->assert(count($ages) === 5);
$this->assert($ages[''] === '15');
$passwords = DBHelper::reIndex($all, 'password');
$this->assert(count($passwords) === 5);
$this->assert($passwords['']['username'] === 'Bart');
$this->assert($passwords['']['password'] === NULL);
}
public function __construct()
{
$this->istok = DBHelper::getIstok(Connection::getConnection());
$this->obj = Creator::getObject($this->istok["name"]);
$this->columneNames = $this->istok["colnames"];
$this->data = $this->istok["colvalues"];
//print_r($this->istok);
//$this->test();
//print_r($this->obj); echo PHP_EOL;
//print_r($this->columneNames);echo PHP_EOL;
//print_r($this->data);echo PHP_EOL;
}
public static function insertMessage($message)
{
$db = DBHelper::getConnection();
$subject = self::filterString($message["subject"]);
$name = self::filterString($message["name"]);
$email = self::filterString($message["email"]);
$text = self::filterString($message["text"]);
$data = array("subject" => $subject, "name" => $name, "email" => $email, "text" => $text);
$query = $db->prepare("INSERT INTO guestbook (subject, name, email, text) VALUES (:subject, :name, :email, :text)");
$query->execute($data);
return true;
}
public function dropTables()
{
$db =& wfGetDB(DB_MASTER);
$verbose = true;
DBHelper::reportProgress("Dropping query results cache tables ...\n", $verbose);
$tables = array('smw_qrc_cache');
foreach ($tables as $table) {
$name = $db->tableName($table);
$db->query('DROP TABLE' . ($wgDBtype == 'postgres' ? '' : ' IF EXISTS') . $name, 'SMWQRCSQLStore::drop');
DBHelper::reportProgress(" ... dropped table {$name}.\n", $verbose);
}
DBHelper::reportProgress(" ... done!\n", $verbose);
}
public function getReports()
{
if ($this->issue_reports == null) {
$arr = DBHelper::selectRows(TBL_REPORTS, REPORT_ISSUE . '=' . $this->issue_id, REPORT_CRASH_DATE . ' DESC', REPORT_PROJECTION, null, null, false);
$this->issue_reports = array();
if (!empty($arr)) {
foreach ($arr as $values) {
$this->issue_reports[] = Report::createFromArray($values);
}
}
}
return $this->issue_reports;
}
function retrieve_relationships($table, $values, $select_id)
{
$query = "SELECT {$select_id} FROM {$table} WHERE deleted = 0 ";
foreach ($values as $name => $value) {
$query .= " AND {$name} = '{$value}' ";
}
$query .= " ORDER BY {$select_id} ";
$result = $this->db->query($query, false, "Retrieving Relationship:" . $query);
$ids = array();
while ($row = $this->db->fetchByAssoc($result)) {
$ids[] = $row;
}
return $ids;
}
public function signIn($login, $hash)
{
$hash256 = hash('sha256', $hash . $login);
$result = DBHelper::getFirst("SELECT * FROM `users` WHERE `login` = '" . $login . "' AND `password` = '" . $hash256 . "'");
if ($result != NULL) {
if (session_id() == "") {
session_start();
}
$_SESSION['login'] = $login;
DBHelper::run("UPDATE `users` WHERE `login` = '" . $login . "' SET `last_activity` = " . time());
return "SUCCESS";
}
return "FAILURE";
}
