private static function clearTables(\Core\Database $db)
{
$clearScript = new \Helpers\TablesClearScript();
foreach ($clearScript as $statement) {
$db->exec($statement, array());
}
}
/**
* @param float $latitude
* @param float $longitude
* @param $radio
*/
public static function getRepublicas($latitude, $longitude, $radius, Database &$database)
{
$latitude = filter_var($latitude, FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION);
$longitude = filter_var($longitude, FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION);
$radius = filter_var($radius, FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION);
//Haversine formula
$query = $database->prepare('
SELECT *, (6371 * acos(
cos(radians( :latitude )) * cos(radians(latitude)) *
cos(radians(longitude) - radians( :longitude )) +
sin(radians( :latitude )) * sin(radians(latitude))
))
AS distance
FROM republicas
HAVING distance < :radius
ORDER BY distance
');
$query->bindParam(':latitude', $latitude);
$query->bindParam(':longitude', $longitude);
$query->bindParam(':radius', $radius);
$query->execute();
$json = array();
while ($item = $query->fetch(Database::FETCH_ASSOC)) {
$json[] = $item;
}
return json_encode($json);
}
/**
* Static method get
*
* @param array $group
* @return \helpers\database
*/
public static function get($group = null)
{
// Determining if exists or it's not empty, then use default group defined in config
$group = !$group ? array('type' => DB_TYPE, 'host' => DB_HOST, 'name' => DB_NAME, 'user' => DB_USER, 'pass' => DB_PASS, 'port' => DB_PORT) : $group;
// Group information
$type = $group['type'];
$host = $group['host'];
$name = $group['name'];
$user = $group['user'];
$pass = $group['pass'];
$port = $group['port'];
// ID for database based on the group information
$id = "{$type}.{$host}.{$port}.{$name}.{$user}.{$pass}";
// Checking if the same
if (isset(self::$instances[$id])) {
return self::$instances[$id];
}
try {
// I've run into problem where
// SET NAMES "UTF8" not working on some hostings.
// Specifiying charset in DSN fixes the charset problem perfectly!
$instance = new Database("{$type}:host={$host};port={$port};dbname={$name};charset=utf8", $user, $pass);
$instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Setting Database into $instances to avoid duplication
self::$instances[$id] = $instance;
return $instance;
} catch (PDOException $e) {
//in the event of an error record the error to ErrorLog.html
Logger::newMessage($e);
Logger::customErrorMsg();
}
}
public function getAll(\Core\Database $db)
{
$recs = $db->fetchAllAssoc("select * from rooms", array());
$result = array();
for ($i = 0; $i < count($recs); $i++) {
$result[] = new \Application\RoomItem($recs[$i]);
}
return $result;
}
protected function makeUpdateQuery($table_name, $fields_to_save, array $where_condition, \Core\Database $db)
{
$value_equation_list_imploded = $this->makeEquationString(',', $fields_to_save);
$value_list = $this->makeValueVarArray($fields_to_save);
$sql = "update {$table_name} set {$value_equation_list_imploded} where " . $this->makeEquationString(' and ', $where_condition);
$value_list = array_merge($value_list, $this->makeValueVarArray($where_condition));
//error_log("\nSQL:" . print_r($sql, true) . "\nvalues:" . print_r($value_list, true), 3, "my_errors.txt");
return $db->exec($sql, $value_list);
}
public static function getUserId($username, $password)
{
$result = Database::getInstance()->prepare("SELECT id FROM `users` where `username` = :username AND `password`=:password OR `email`=:username AND `password`=:password");
$result->execute(array(':username' => $username, ':password' => $password));
$row = $result->fetch(PDO::FETCH_ASSOC);
return $row['id'];
}
/**
* Get a skin by payer name
* @param $playerName String
* @return Skin
*/
public static function getSkinByPlayerName($playerName)
{
$stn = DB::sql("SELECT * FROM mc_skin WHERE player_name=?");
$stn->bindValue(1, $playerName, DB::PARAM_STR);
$stn->execute();
return $stn->fetchObject(__CLASS__);
}
public static function getByUserId($userid)
{
$stm = DB::sql("SELECT * FROM `orders` WHERE `uid`=? AND status = 0");
$stm->bindValue(1, $userid, DB::PARAM_INT);
$stm->execute();
return $stm->fetchAll(DB::FETCH_CLASS, __CLASS__);
}
/**
* Get use user count
* @return int
*/
public static function GetUseUserCount()
{
$statement = Database::prepare("SELECT count(*) FROM member WHERE lastConnTime > 0");
$statement->execute();
$count = $statement->fetch(\PDO::FETCH_NUM);
return $count[0] == null ? 0 : $count[0];
}
/**
* Create a new instance of the database helper.
*/
public function __construct()
{
/**
* connect to PDO here.
*/
$this->db = \Core\Database::get();
}
public static function queryUrl($url)
{
$stm = Database::sql('SELECT `id`, `alias`, `url`, `status`, `add_time`, `click_num` FROM `url_list` WHERE `url`=?');
$stm->bindValue(1, $url, Database::PARAM_STR);
$stm->execute();
return $stm->fetchObject(__CLASS__);
}
public static function getByTrade($trade)
{
$stm = DB::sql('SELECT id, trade, has_notify FROM trade WHERE trade=?');
$stm->bindValue(1, $trade);
$stm->execute();
return $stm->fetchObject(__CLASS__);
}
/**
* Create a new PageData object.
* @param string $tableName Target table name
* @param string $extras Such as where statement or order statement
* @param array $column Column names needs to be fetch
*/
public function __construct($tableName, $extras = '', $column = array('*'))
{
$columns = '`' . implode('`, `', $column) . '`';
$this->countQuery = Database::getInstance()->prepare("SELECT COUNT(*) FROM `{$tableName}` {$extras}");
$this->query = Database::getInstance()->prepare("SELECT {$columns} FROM `{$tableName}` {$extras} LIMIT :pageDataStart,:pageDataRPP");
if ($_GET['page']) {
$this->setPage($_GET['page']);
}
}
public static function init()
{
$stn = DB::getInstance()->prepare("SELECT k, v FROM options");
$stn->execute();
$opt = $stn->fetchAll(DB::FETCH_UNIQUE | DB::FETCH_COLUMN);
// $GLOBALS['OPTIONS'] = $opt;
self::$list = $opt;
return $opt;
}
public function save($mode = self::SAVE_AUTO)
{
$map = array();
$reflection = new ReflectionObject($this);
$reflectionProp = $reflection->getProperties(ReflectionProperty::IS_PROTECTED | ReflectionProperty::IS_PUBLIC);
foreach ($reflectionProp as $property) {
if (strpos($property->getDocComment(), '@ignore')) {
continue;
}
$propertyName = $property->getName();
if ($propertyName == 'primaryKey') {
continue;
}
if ($property->isProtected()) {
$property->setAccessible(true);
}
$propertyValue = $property->getValue($this);
$map[$propertyName] = $propertyValue;
}
$primaryKey = $this->getPrimaryKeyName($reflection);
$identifier = $map[$primaryKey];
unset($map[$primaryKey]);
$tableName = $this->getTableName($reflection);
if ($mode == self::SAVE_UPDATE || $identifier && $mode != self::SAVE_INSERT) {
$sql = "UPDATE `{$tableName}` SET ";
foreach ($map as $key => $value) {
$sql .= "`{$key}` = :{$key},";
}
$sql = rtrim($sql, ',');
$sql .= " WHERE {$primaryKey} = :id";
$statement = Database::getInstance()->prepare($sql);
$statement->bindValue(':id', $identifier);
foreach ($map as $key => $value) {
$statement->bindValue(":{$key}", $value);
}
} else {
$sql = "INSERT INTO `{$tableName}` SET ";
foreach ($map as $key => $value) {
$sql .= "`{$key}` = :{$key},";
}
$sql = rtrim($sql, ',');
$statement = Database::getInstance()->prepare($sql);
foreach ($map as $key => $value) {
$statement->bindValue(":{$key}", $value);
}
}
$statement->execute();
if (!$identifier) {
$insertId = Database::getInstance()->lastInsertId();
if ($insertId) {
$reflection->getProperty($primaryKey)->setValue($this, $insertId);
}
}
}
/**
* @param (int|string)[] $answers
*
* @return boolean
*/
public static function save(array $answers, Database &$database)
{
$options = array('dificuldade' => FILTER_SANITIZE_STRING, 'explicacao_dificuldade' => FILTER_SANITIZE_STRING, 'encontrou' => FILTER_SANITIZE_STRING, 'aluno_EACH' => FILTER_SANITIZE_STRING, 'indicaria' => FILTER_SANITIZE_STRING, 'referencia' => FILTER_SANITIZE_STRING, 'nota_design' => FILTER_SANITIZE_NUMBER_INT, 'nota_funcionalidades' => FILTER_SANITIZE_NUMBER_INT, 'nota_acessibilidade' => FILTER_SANITIZE_NUMBER_INT, 'nota_insercao_reps' => FILTER_SANITIZE_NUMBER_INT, 'info_adicional' => FILTER_SANITIZE_STRING);
$answers = filter_var_array($answers, $options);
$query = $database->prepare('
INSERT INTO feedback (
dificuldade, explicacao_dificuldade, encontrou, aluno_EACH,
indicaria, referencia, nota_design, nota_funcionalidades,
nota_acessibilidade, nota_insercao_reps, info_adicional
) VALUES (
:dificuldade, :explicacao_dificuldade, :encontrou, :aluno_EACH,
:indicaria, :referencia, :nota_design, :nota_funcionalidades,
:nota_acessibilidade, :nota_insercao_reps, :info_adicional
)
');
do {
$query->bindParam(':' . key($answers), current($answers));
} while (next($answers) !== false);
return $query->execute();
}
public function run()
{
// 清理一个月前的数据
$mon = time() - 2592000;
$stn = Database::sql('DELETE FROM `card` WHERE add_time<? AND status=0');
$stn->bindValue(1, $mon, Database::PARAM_INT);
$stn->execute();
$stn = Database::sql("DELETE FROM `invite` WHERE dateLine<? AND status=1");
$stn->bindValue(1, $mon, Database::PARAM_INT);
$stn->execute();
}
/**
* Migrate current database
* @param $dropTable bool drop the table
*/
public function execute($dropTable = false)
{
$this->database = Database::getInstance();
$modelDir = "Application/Model";
$file = opendir($modelDir);
// there is fileName
while (($fileName = readdir($file)) !== false) {
if (substr($fileName, -4) == ".php") {
$this->migrateTable($modelDir . "/" . $fileName, $dropTable);
}
}
}
public static function getById($id)
{
try {
$connection = Database::instance();
$sql = "SELECT * from usuarios WHERE id = ?";
$query = $connection->prepare($sql);
$query->bindParam(1, $id, \PDO::PARAM_INT);
$query->execute();
return $query->fetch();
} catch (\PDOException $e) {
print "Error!: " . $e->getMessage();
}
}
public static function getUserPassword($user_name, $password)
{
try {
$connection = Database::instance();
$sql = "SELECT * from usuarios WHERE nombre_usuario = ? AND clave_usuario = ?";
$query = $connection->prepare($sql);
$query->bindParam(2, $user_name, $password, \PDO::PARAM_INT);
$query->execute();
return $query->fetch();
} catch (\PDOException $e) {
print "Error!: " . $e->getMessage();
}
}
/**
* @static
* @param string $email
* @param string $password
* @param string $location URL you want to redirect user to
*
* @return boolean
*/
public static function login($email, $password, Database &$database)
{
$email = filter_var($email, FILTER_SANITIZE_EMAIL);
$validEmail = (bool) filter_var($email, FILTER_VALIDATE_EMAIL);
if (!$validEmail) {
return false;
}
$query = $database->prepare('
SELECT id, password, salt FROM users WHERE email = :email
');
$query->bindParam(':email', $email, Database::PARAM_STR);
$query->execute();
$success = false;
if ($query->rowCount() == 1) {
$result = $query->fetch(Database::FETCH_ASSOC);
$passwordHash = hash('sha512', $result['salt'] . $password);
$success = $result['password'] == $passwordHash;
if ($success) {
$_SESSION['user_id'] = $result['id'];
}
}
return $success;
}
public static function setNextRun($cronId, $step)
{
$inTransaction = DB::getInstance()->inTransaction();
if (!$inTransaction) {
DB::getInstance()->beginTransaction();
}
$st = DB::sql("UPDATE cron SET nextrun=? WHERE id=?");
$st->bindValue(1, $step, DB::PARAM_INT);
$st->bindValue(2, $cronId, DB::PARAM_STR);
$st->execute();
if (!$inTransaction) {
DB::getInstance()->commit();
}
}
public function destroy()
{
$inTransaction = DB::getInstance()->inTransaction();
if (!$inTransaction) {
DB::getInstance()->beginTransaction();
}
$st = DB::sql("UPDATE card SET status=0 WHERE card=:card");
// 失效卡
$st->bindValue(":card", $this->card, DB::PARAM_STR);
$flag = $st->execute();
if (!$inTransaction) {
DB::getInstance()->commit();
}
return $flag;
}
public static function emailFind($email)
{
$db = Database::connect();
//TODO: Change to prepared statement
if ($stmt = $db->query("SELECT id FROM Users WHERE email = '{$email}'")) {
if ($data = $stmt->fetch(\PDO::FETCH_ASSOC)) {
return new self($data["id"]);
} else {
//No user with that email
return false;
}
} else {
throw \Exception\DatabaseError::build($db);
}
}
/**
* Execute a PDO prepare with execute
*
* @param $query
* The query to execute
* @param array $params
* The query parameters
* @return An array with the results
*/
public static function execute($query, $params = [])
{
// Preparing the query with the database instance
$results = Database::getInstance()->prepare($query);
// Executing the query with the given parameters
$results->execute($params);
// Fetching the results
$results = $results->fetchAll(\PDO::FETCH_OBJ);
// Checking them
if (count($results) < 2) {
$results = current($results);
}
// Returning them
return $results;
}
function validate($username, $password)
{
$session = Node::getOne(array(Node::FIELD_COLLECTION => FRAMEWORK_COLLECTION_SESSION, 'username' => Database::escapeValue($username)));
$res = Session::validate($username, $password, $this->request()->fingerprint());
if (is_int($res)) {
switch ($res) {
case Session::ERR_MISMATCH:
throw new ServiceException('Username and password mismatch.', $res);
break;
case Session::ERR_EXISTS:
throw new ServiceException('Session already exists.', $res);
break;
}
}
return $res;
}
public function update()
{
$inTransaction = Database::inTransaction();
if (!$inTransaction) {
Database::beginTransaction();
}
$statement = Database::prepare("UPDATE user_power SET `uid`=:uid WHERE id=:id");
$statement->bindValue(':uid', $this->uid, \PDO::PARAM_INT);
$statement->bindValue(':id', $this->id, \PDO::PARAM_INT);
$statement->execute();
$this->uid = Database::lastInsertId();
$statement->execute();
if (!$inTransaction) {
Database::commit();
}
}
public function delete()
{
$db = Database::connect();
if ($stmt = $db->prepare("DELETE FROM {$this->table} WHERE id=:id LIMIT 1;")) {
$stmt->bindParam(":id", $this->id);
if ($stmt->execute()) {
foreach ($this->getPublicProperties() as $p) {
$this->{$p} = null;
}
return true;
} else {
throw \Exception\DatabaseError::build($stmt);
}
} else {
throw \Exception\DatabaseError::build($db);
}
}
public function run()
{
$resetDate = '1';
$date = date("d", time());
if ($date == $resetDate) {
$inTransaction = DB::getInstance()->inTransaction();
if (!$inTransaction) {
DB::getInstance()->beginTransaction();
}
$st = DB::sql("UPDATE member SET flow_up=0, flow_down=0 WHERE `enable`=1 AND `plan`!='Z'");
$st->execute();
if (!$inTransaction) {
DB::getInstance()->commit();
}
}
return false;
}
/**
*
* @return array ["userCount", "checkCount", "connCount"]
*/
public static function getAnaCount()
{
$data = array();
// user count
$selectSQL = "SELECT count(*) FROM member";
$statement = Database::prepare($selectSQL);
$statement->execute();
$userCount = $statement->fetch(\PDO::FETCH_NUM);
$data['userCount'] = $userCount[0];
// check user
$statement = Database::prepare("SELECT count(*) FROM member WHERE lastCheckinTime > " . date('Y-m-d 00:00:00', time()));
$statement->execute();
$checkCount = $statement->fetch(\PDO::FETCH_NUM);
$data['checkCount'] = $checkCount[0];
$statement = Database::prepare("SELECT count(*) FROM member WHERE lastConnTime > " . time() - 600);
$statement->execute();
$connCount = $statement->fetch(\PDO::FETCH_NUM);
$data['connCount'] = $connCount[0];
return $data;
}
