/**
* @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();
}
}
