public function togglePost($iId, $iVisible)
{
$dbh = new MyDB();
$oDb = $dbh->getDB();
$dbh = $oDb->prepare("UPDATE `posts` SET `hidde` = ? WHERE `post_id` = ?");
$dbh->execute(array($iVisible, $iId));
}
function go_digital_panel()
{
$db = new MyDB();
if (!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully</br>";
}
$userName = $_COOKIE["username"];
$sql = <<<EOF
SELECT * FROM User WHERE name = '{$userName}';
EOF;
$ret = $db->query($sql);
if (!$ret) {
echo $db->lastErrorMsg();
} else {
if ($row = $ret->fetchArray(SQLITE3_ASSOC)) {
$name = $row["name"];
$gender = $row["gender"];
$height = $row["height"];
$weight = $row["weight"];
$date = date("Y-m-d");
setcookie("date", $date, null, "/");
$url = "http://www.kmoving.com/home.php?name={$name}&gender={$gender}&height={$height}&weight={$weight}";
header("Location: {$url}");
}
}
$db->close();
}
public static function isIn($username, $id)
{
$db = new MyDB();
$sql = "SELECT * FROM `JOIN` WHERE USERNAME = '******' AND ACTIVITY={$id}";
$res = $db->query($sql);
return $res;
}
function csvToJson($filename, $separator = ",")
{
//create the resulting array
$result = array("records" => array());
//check if the file handle is valid
//echo $filename;
if (($handle = fopen($filename, "r")) !== false) {
//"Contact","Company","Business Email","Business Phone","Direct Phone","Time Zone","Fax","Web","Source"
//check if the provided file has the right format
if (($data = fgetcsv($handle, 4096, $separator)) == false || ($data[0] != "Contact" || $data[1] != "Company" || $data[2] != "Business Email")) {
throw new InvalidImportFileFormatException(sprintf('The provided file (%s) has the wrong format!', $filename));
}
$db = new MyDB();
if (!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
//loop through your data
while (($data = fgetcsv($handle, 4096, $separator)) !== false) {
$TZ = $db->UpdateTimeZone($data[3]);
echo json_encode($TZ);
//store each line in the resulting array
$result['records'][] = array("Contact" => $data[0], "Business Email" => $data[2], "Business Phone" => $data[3], "Time Zone" => $TZ);
}
//close the filehandle
$db->close();
fclose($handle);
}
//return the json encoded result
//echo json_encode($result);
return;
}
public function getUserList()
{
$dbh = new MyDB();
$oDb = $dbh->getDB();
$dbh = $oDb->prepare('SELECT id, login, avatar, email, reg_date, u_status FROM user ORDER BY id');
$dbh->execute(array());
return $dbh->fetchAll();
}
function import_advice($address)
{
$reader = PHPExcel_IOFactory::createReader('Excel5');
$PHPExcel = $reader->load($address);
// 载入excel文件
$sheet = $PHPExcel->getSheet(0);
// 读取第一個工作表
$highestRow = $sheet->getHighestRow();
// 取得总行数
$highestColumm = $sheet->getHighestColumn();
// 取得总列数
$db = new MyDB();
if (!$db) {
echo $db->lastErrorMsg();
} else {
}
$title = "";
$content = "";
$toUserId = 0;
$authorId = 0;
for ($row = 1; $row <= $highestRow; $row++) {
//行数是以第1行开始
for ($column = 'A'; $column <= $highestColumm; $column++) {
//列数是以A列开始
if ($row != 1) {
switch ($column) {
case 'A':
$title = $sheet->getCell($column . $row)->getValue();
break;
case 'B':
$content = $sheet->getCell($column . $row)->getValue();
break;
case 'C':
$toUserId = $sheet->getCell($column . $row)->getValue();
break;
case 'D':
$authorId = $sheet->getCell($column . $row)->getValue();
break;
}
}
}
if ($row != 1) {
$sql = <<<EOF
INSERT INTO Advice (title, content, toUserId, authorId)
VALUES ('{$title}', '{$content}', '{$toUserId}', '{$authorId}');
EOF;
$ret = $db->exec($sql);
if (!$ret) {
echo $db->lastErrorMsg();
} else {
}
}
}
$db->close();
header("Location: http://www.kmoving.com/user/advice.php");
}
public function registerUser()
{
$error = '';
$username = trim($_POST['username']);
$email = trim($_POST['email']);
$pass = trim($_POST['passwordinput']);
if (empty($username)) {
$error .= '<li>Username</li>';
}
if (empty($email)) {
$error .= '<li>E-mail</li>';
}
if (empty($pass)) {
$error .= '<li>Password</li>';
}
if (strlen($pass) < 8) {
$error .= '<li>Пароль надто короткий</li>';
}
if (empty($error)) {
$dbh = new MyDB();
$oDb = $dbh->getDB();
$dbh = $oDb->prepare("SELECT `id` FROM `user` WHERE `login` = ? LIMIT 1");
$dbh->execute(array(mysql_real_escape_string(strip_tags($username))));
if ($dbh->fetchColumn()) {
$rez = "<h3> Користувач з таким імям вже існує </h3>";
} else {
//якщо помилки відсутні
$pass = md5($pass);
$pass = strrev($pass) . 'ZAQ!2wsx';
$reg_date = date("d-m-Y в H:i");
$dbh = new MyDB();
$oDb = $dbh->getDB();
$dbh = $oDb->prepare("INSERT INTO user(login, password, email, reg_date)VALUES(?, ?, ?, ?)");
$dbh->execute(array($username, $pass, $email, $reg_date));
$iUserId = $oDb->lastInsertId();
if ($iUserId > 0) {
$oMedia = new myMedia();
$avatar = $oMedia->imgUploader($iUserId);
$dbh = new MyDB();
$oDb = $dbh->getDB();
$dbh = $oDb->prepare("UPDATE `ithink_db`.`user` SET `avatar` = ? WHERE `user`.`id` = ?");
$dbh->execute(array($avatar, $iUserId));
$rez = "<h3>Реєстрація пройшла успішно</h3>";
$_SESSION['auth']['user']['username'] = $username;
$_SESSION['auth']['user']['avatar'] = $avatar;
$_SESSION['auth']['user']['user_id'] = $iUserId;
$_SESSION['auth']['user']['u_status'] = 1;
}
}
} else {
//вивід помилок на екран
$rez = "Усі поля не заповнені: <ul id='error-list'> {$error} </ul>";
}
return $rez;
}
function napoj_db()
{
$db = new MyDB();
if (!$db) {
echo $db->lastErrorMsg();
return false;
} else {
//echo "Opened database successfully<br>"; ///////////////////////////////////
return $db;
}
}
function user_register($userName, $userPassword, $checkbox)
{
$db = new MyDB();
if (!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully</br>";
}
$sql = <<<EOF
SELECT * FROM User WHERE name = '{$userName}';
EOF;
$ret = $db->query($sql);
if (!$ret) {
echo $db->lastErrorMsg();
} else {
if ($row = $ret->fetchArray(SQLITE3_ASSOC)) {
header("Location: http://www.kmoving.com/user/register.php?Error=userExist");
} else {
$sql = <<<EOF
INSERT INTO User (name, password, last, doctor, gender, height, weight, country, city, address)
VALUES ('{$userName}', '{$userPassword}', '{$userName}', '{$checkbox}', '--', '--', '--', '--', '--', '--');
EOF;
$ret = $db->exec($sql);
if (!$ret) {
echo $db->lastErrorMsg();
} else {
$db->close();
setcookie("username", $userName, null, "/");
header("Location: http://www.kmoving.com/server/user/user_details.php");
}
}
}
$db->close();
}
protected function login()
{
try {
if ($this->method == 'POST') {
$username = $this->request['username'];
$password = $this->request['password'];
if (isset($username) && isset($password)) {
if (isset($_SESSION['Token'])) {
return $_SESSION['Token'];
} else {
$user_authenticated = MyDB::getInstance()->authenticateUser($username, $password);
if ($user_authenticated) {
//$this->User->loadUser($username, $password);
$_SESSION['Token'] = uniqid();
return array('token' => $_SESSION['Token'], 'user_name' => $username);
} else {
throw new Exception('Invalid user credentials');
}
}
} else {
throw new Exception('Missing username or password');
}
} else {
throw new Exception('Wrong request type');
}
} catch (Exception $e) {
header('401 Not Authorized');
return $e->getMessage();
}
}
public function password($username)
{
echo 'password called';
// Return the password for the username
$res = MyDB::loginStudent($username);
return $res['pwHash'];
}
public static function getInstance()
{
if (!self::$instance instanceof self) {
self::$instance = new self();
}
return self::$instance;
}
function change_pwd($oldpwd, $newpwd, $newpwd_r)
{
if ($newpwd != $newpwd_r) {
return 1;
}
return MyDB::getInstance()->change_passwd($_SESSION['u_login'], $oldpwd, $newpwd) == TRUE ? 0 : 1;
}
function activity_refresh($id, $title, $target, $content)
{
$db = new MyDB();
if (!$db) {
echo $db->lastErrorMsg();
} else {
}
$sql = <<<EOF
UPDATE Activity SET title='{$title}',target='{$target}',content='{$content}' where id={$id};
EOF;
$ret = $db->exec($sql);
if (!$ret) {
echo $db->lastErrorMsg();
}
$db->close();
}
protected function get_token()
{
if ($this->method == 'POST') {
$username = $this->request['username'];
$password = $this->request['password'];
if (isset($username) && isset($password)) {
if (isset($_SESSION[$username])) {
return $_SESSION[$username];
} else {
$user_authenticated = MyDB::getInstance()->authenticateUser($username, $password);
if ($user_authenticated) {
//$this->User->loadUser($username, $password);
$_SESSION[$username] = uniqid();
return $_SESSION[$username];
} else {
return 'Invalid user credentials';
}
}
} else {
return 'Missing username/password';
}
} else {
return "Only accepts POST requests";
}
}
protected function connect_me()
{
$this->connection = $this->connect($this->host, $this->user, $this->password, $this->database);
self::$db = $this->connection;
if ($this->connect_error) {
die($this->connect_error);
}
}
function update_moves($active_time, $inactive_time, $calories, $wo_calories, $bg_calories, $bmr_day, $steps, $km)
{
$db = new MyDB();
if (!$db) {
echo $db->lastErrorMsg();
} else {
}
$userName = $_COOKIE['username'];
$date = $_COOKIE['date'];
$sql = <<<EOF
SELECT * FROM User,Moves WHERE User.name='{$userName}' and User.id=Moves.userId and Moves.createAt='{$date}';
EOF;
$ret = $db->query($sql);
if (!$ret) {
echo $db->lastErrorMsg();
} else {
if ($row = $ret->fetchArray(SQLITE3_ASSOC)) {
$userId = $row['userId'];
$sql = <<<EOF
UPDATE Moves
SET active='{$active_time}',free='{$inactive_time}',
total_calories='{$calories}',workouts_calories='{$wo_calories}',
static_calories='{$bg_calories}',daixie='{$bmr_day}',steps='{$steps}',distance='{$km}'
where userId={$userId} and createAt={$date};
EOF;
$ret = $db->exec($sql);
if (!$ret) {
echo $db->lastErrorMsg();
} else {
}
} else {
$userId = get_userId($userName);
$sql = <<<EOF
INSERT INTO Moves (userId, active, free, total_calories, workouts_calories, static_calories, daixie, steps, distance, createAt)
VALUES ('{$userId}', '{$active_time}', '{$inactive_time}', '{$calories}', '{$wo_calories}', '{$bg_calories}', '{$bmr_day}', '{$steps}', '{$km}' , '{$date}');
EOF;
$ret = $db->exec($sql);
if (!$ret) {
echo $db->lastErrorMsg();
} else {
}
}
}
$db->close();
}
function activity_delete($delete_ID)
{
$db = new MyDB();
if (!$db) {
echo $db->lastErrorMsg();
} else {
}
$sql = <<<EOF
DELETE from Activity where id = {$delete_ID};
EOF;
$ret = $db->exec($sql);
if (!$ret) {
echo $db->lastErrorMsg();
} else {
$sql = <<<EOF
DELETE from ActivityMember where activityId = {$delete_ID};
EOF;
$ret = $db->exec($sql);
if (!$ret) {
echo $db->lastErrorMsg();
} else {
$db->close();
header("Location: http://www.kmoving.com/user/groups/activity.php");
}
}
}
function add_member($activityId)
{
$db = new MyDB();
if (!$db) {
echo $db->lastErrorMsg();
} else {
}
$userId = get_userId();
$sql = <<<EOF
SELECT * FROM ActivityMember WHERE userId={$userId} and activityId={$activityId};
EOF;
$ret = $db->query($sql);
if ($row = $ret->fetchArray(SQLITE3_ASSOC)) {
$db->close();
header("Location: http://www.kmoving.com/user/groups/activity.php?msg=memberExist");
} else {
$data = $_COOKIE['date'];
$sql = <<<EOF
INSERT INTO ActivityMember (userId, activityId, createAt)
VALUES ('{$userId}', '{$activityId}', '{$data}');
EOF;
$ret = $db->exec($sql);
if (!$ret) {
echo $db->lastErrorMsg();
} else {
$db->close();
header("Location: http://www.kmoving.com/user/groups/activity.php");
}
}
}
function createPost()
{
$error = '';
$title = trim($_POST['post_title']);
$full_text = trim($_POST['post_text']);
if (empty($title)) {
$error .= '<li>title</li>';
}
if (empty($full_text)) {
$error .= '<li>full_text</li>';
}
if (empty($error)) {
$author_id = $_SESSION['auth']['user']['user_id'];
if (strlen($title) > 50) {
$title = substr($title, 0, 50);
}
if (strlen($full_text) > 250) {
$description = substr($full_text, 0, 247);
$description .= '...';
} else {
$description = $full_text;
}
$cre_date = mktime();
$dbh = new MyDB();
$oDb = $dbh->getDB();
$dbh = $oDb->prepare("SELECT post_id FROM posts WHERE title = ? LIMIT 1");
$dbh->execute(array($title));
$iRow = $dbh->fetchColumn();
//var_dump($iRow);die();
if ($iRow) {
$rez = "<h3> Така думка вже була створена </h3>";
} else {
$dbh = new MyDB();
$oDb = $dbh->getDB();
$dbh = $oDb->prepare("INSERT INTO posts (author_id, title, description, full_text, cre_date)VALUES(?,?,?,?,?) ");
$dbh->execute(array($author_id, $title, $description, $full_text, $cre_date));
if ($oDb->lastInsertId()) {
$rez = "<h3>Запис успішно добавлено</h3>";
}
}
} else {
$rez = "Усі поля не заповнені: <ul id='error-list'> {$error} </ul>";
}
return $rez;
}
function get_total_len()
{
$db = new MyDB();
if (!$db) {
echo $db->lastErrorMsg();
} else {
}
$sql = <<<EOF
SELECT count(*) AS length FROM Activity;
EOF;
$ret = $db->query($sql);
if ($row = $ret->fetchArray(SQLITE3_ASSOC)) {
$db->close();
return $row['length'];
}
$db->close();
return null;
}
public static function getActiveProjects($companyID)
{
$currentProjectIDs = MyDB::getCompanyProjects($companyID);
$currentProjects = array();
for ($i = 0; $i < count($currentProjectIDs); $i++) {
array_push($currentProjects, MyDB::getprojectByID($currentProjectIDs[$i]));
}
return $currentProjects;
}
protected function setUp()
{
try {
$dbparams = ezcTestSettings::getInstance()->db->dsn;
MyDB::setParams($dbparams);
$this->db = MyDB::create();
} catch (Exception $e) {
$this->markTestSkipped();
}
}
function set_user_settings($gender, $height, $weight, $birth, $country, $city, $address)
{
$db = new MyDB();
if (!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully</br>";
}
$userName = $_COOKIE["username"];
$sql = <<<EOF
UPDATE User set gender='{$gender}', height='{$height}', weight='{$weight}', birth='{$birth}',country='{$country}',city='{$city}',address='{$address}' where name='{$userName}';
EOF;
$ret = $db->exec($sql);
if (!$ret) {
echo $db->lastErrorMsg();
} else {
header("Location: http://www.kmoving.com/server/user/settings.php");
}
$db->close();
}
function getXmlNews()
{
$d = new DomDocument('1.0', 'utf-8');
$root_e = $d->createElement('news');
foreach (MyDB::getInstance()->getResults(MyDB::getQuery(SELECT_NEWS)) as $row) {
$item_e = $d->createElement('item', $row['obsah']);
$item_e->setAttribute('date', $row['datum']);
$root_e->appendChild($item_e);
}
$d->appendChild($root_e);
return $d->saveXML();
}
function addToDB($ip, $time, $session, $message)
{
$db = new MyDB();
if (!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
$sql = <<<EOF
INSERT INTO test (ip,time,session,message)
VALUES('{$ip}', '{$time}', '{$session}', '{$message}');
EOF;
$ret = $db->exec($sql);
if (!$ret) {
echo $db->lastErrorMsg();
} else {
echo "Records created successfully\n";
}
$db->close();
}
public static function getActiveProjects($userID)
{
$currentProjectIDs = MyDB::getStudentProjects($userID);
//print_r($currentProjectIDs);
if (is_array($currentProjectIDs)) {
$currentProjects = array();
for ($i = 0; $i < count($currentProjectIDs); $i++) {
array_push($currentProjects, MyDB::getprojectByID($currentProjectIDs[$i]));
}
return $currentProjects;
}
return -1;
}
function show_activity($last, $amount)
{
$db = new MyDB();
if (!$db) {
echo $db->lastErrorMsg();
} else {
}
$userName = $_COOKIE['username'];
$userId = get_userId($db, $userName);
$sql = <<<EOF
SELECT * FROM Advice WHERE toUserId={$userId} ORDER BY id DESC LIMIT '{$last}','{$amount}';
EOF;
$ret = $db->query($sql);
while ($row = $ret->fetchArray(SQLITE3_ASSOC)) {
$authorId = $row['authorId'];
$authorName = get_authorName($db, $authorId);
if ($authorName != null) {
$sayList[] = array('title' => "标题：" . $row['title'], 'content' => "建议内容： " . $row['content'], 'authorName' => "提出者： " . $authorName);
}
}
echo json_encode($sayList);
$db->close();
}
function process($a)
{
$username = 0;
$password = 0;
$out = new stdClass();
$post = json_decode(file_get_contents('php://input'), TRUE);
if (isset($post['username'])) {
$username = $post['username'];
}
if (isset($post['password'])) {
$password = $post['password'];
}
if ($username && $password) {
$db = new MyDB();
$sql = "SELECT * FROM users WHERE username=? AND password=?";
$res = $db->queryA($sql, array($username, $password));
if ($res && count($res)) {
$res = $res[0];
$_SESSION['directories_user'] = $res['username'];
$_SESSION['directories_role'] = $res['role'];
$_SESSION['directories_user_id'] = $res['id'];
//$_SESSION['directories_folder']=$res->folder;
$out->success = 'loggedin';
$out->result = 'Admin';
return $out;
}
$out->error = 'wrong';
if (isset($_GET['debug'])) {
$out->error = $db->errorInfo();
}
$out->message = 'Please check username and password';
return $out;
}
$out->error = 'data empty';
$out->message = 'Please fill the form';
return $out;
}
public function action_index()
{
$session = Session::instance();
//print_r(Company::checkIfApproved($session->get('userId')));
//print_r($session);
$projects = array();
$projects = MyDB::getCompletedProjects();
if ($session->get('userType') == 'student') {
$projects = User::getActiveProjects($session->get('userId'));
}
if ($session->get('userType') == 'company') {
$projects = Company::getActiveProjects($session->get('userId'));
}
$this->response->body(View::factory('header') . View::factory('welcome')->set('projects', $projects)->set('userType', $session->get('userType')));
}
