/** * @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); }
/** * 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]; }
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(); } }
/** * * @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; }
/** * @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(); }
/** * @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 getSSPwd($userId) { $statement = Database::prepare("SELECT * FROM member WHERE uid=?"); $statement->bindValue(1, $userId, \PDO::PARAM_INT); $statement->execute(); $statement->setFetchMode(\PDO::FETCH_CLASS, '\\Model\\User'); return $statement->fetch(\PDO::FETCH_CLASS); }
public function updateInvite() { $inTransaction = Database::inTransaction(); if (!$inTransaction) { Database::beginTransaction(); } $statement = Database::prepare("UPDATE invite SET expiration=:expiration,\n\t\t\t`reguid`=:reguid, `regDateLine`=:regDateLine, `status`=:status, `inviteIp`=:inviteIp WHERE invite=:invite"); $statement->bindValue(':expiration', $this->expiration, \PDO::PARAM_INT); $statement->bindValue(':reguid', $this->reguid, \PDO::PARAM_INT); $statement->bindValue(':regDateLine', $this->regDateLine, \PDO::PARAM_INT); $statement->bindValue(':status', $this->status, \PDO::PARAM_INT); $statement->bindValue(':inviteIp', $this->inviteIp, \PDO::PARAM_STR); $statement->bindValue(':invite', $this->invite, \PDO::PARAM_STR); $statement->execute(); if (!$inTransaction) { Database::commit(); } }
public static function getUserCheckIn($uid) { $statement = Database::prepare("SELECT count(*) FROM member WHERE lastCheckinTime > " . date('Y-m-d 00:00:00', time()) . " AND uid=?"); $statement->bindValue(1, $uid, \PDO::PARAM_INT); $checkIn = $statement->fetch(\PDO::PARAM_INT)[0]; return $checkIn == null ? 0 : $checkIn; }
/** * Save new password * @param string $password New password */ public function savePassword($password) { $salt = substr(md5($this->id . $this->email . ENCRYPT_KEY), 8, 16); $this->password = substr(md5(md5($password) . $salt), 0, 30) . 'T' . self::ENCRYPT_TYPE_ENHANCE; $inTransaction = Database::inTransaction(); if (!$inTransaction) { Database::beginTransaction(); } $statement = Database::prepare("UPDATE member SET `password`=:pwd WHERE id=:userId"); $statement->bindValue(':pwd', $this->password, \PDO::PARAM_STR); $statement->bindValue(':userId', $this->id, \PDO::PARAM_INT); $statement->execute(); if (!$inTransaction) { Database::commit(); } }
/** * @param string $email * @param string $password * @param string $token * * @return boolean */ public static function createNewPassword($email, $password, $token, 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 salt FROM users INNER JOIN recovery_token ON users.email = recovery_token.email WHERE users.email = :email AND recovery_token.token = :token '); $query->bindParam(':email', $email, Database::PARAM_STR); $query->bindParam(':token', $token, Database::PARAM_STR); $query->execute(); $row = $query->fetch(Database::FETCH_ASSOC); $password = hash('sha512', $row['salt'] . $password); $query = $database->prepare(' UPDATE users SET password = :password WHERE email = :email '); $query->bindParam(':password', $password, Database::PARAM_STR); $query->bindParam(':email', $email, Database::PARAM_STR); $updated = $query->execute(); $query = $database->prepare(' DELETE FROM recovery_token WHERE email = :email '); $query->bindParam(':email', $email, Database::PARAM_STR); $query->execute(); return $updated; }
/** * 更新 node 信息 * @param $node */ public function UpdateNode() { $statement = Database::prepare("UPDATE node SET `name`=:name, `type`=:type,\n\t\t\t`server`=:server, `method`=:method, `info`=:info, `status`:=status, `order`=:order WHERE id=:id"); $statement->bindValue(':name', $this->name, \PDO::PARAM_STR); $statement->bindValue(':type', $this->type, \PDO::PARAM_INT); $statement->bindValue(':server', $this->server, \PDO::PARAM_STR); $statement->bindValue(':method', $this->method, \PDO::PARAM_STR); $statement->bindValue(':info', $this->info, \PDO::PARAM_STR); $statement->bindValue(':status', $this->status, \PDO::PARAM_STR); $statement->bindValue(':order', $this->order, \PDO::PARAM_INT); $statement->bindValue(':id', $this->id, \PDO::PARAM_INT); $statement->execute(); Database::commit(); }
/** * Update message */ public function update() { $inTransaction = Database::inTransaction(); if (!$inTransaction) { Database::beginTransaction(); } $statement = Database::prepare("UPDATE message SET `content`=:content, `pushTime`=:pushTime,\n\t\t\t`addTime`=:addTime, `pushUsers`=:pushUsers, `type`=:type, `pushEndTime`:=pushEndTime,\n\t\t\t `order`=:order WHERE id=:id"); $statement->bindValue(':content', $this->name, \PDO::PARAM_STR); $statement->bindValue(':pushTime', $this->type, \PDO::PARAM_INT); $statement->bindValue(':addTime', $this->server, \PDO::PARAM_INT); $statement->bindValue(':pushUsers', $this->method, \PDO::PARAM_STR); $statement->bindValue(':type', $this->info, \PDO::PARAM_INT); $statement->bindValue(':pushEndTime', $this->status, \PDO::PARAM_INT); $statement->bindValue(':order', $this->order, \PDO::PARAM_INT); $statement->bindValue(':id', $this->order, \PDO::PARAM_INT); $statement->execute(); if (!$inTransaction) { Database::commit(); } }