<?php
// vim: ts=2 sw=2
include_once "utils.php";
class DBConn {
protected $db = NULL;
protected $history_db = NULL;
protected $dbpath = NULL;
// using 'var' to define a property equals define the property using 'public'
var $history_dbname = 'easyio.db';
public static function instance() {
static $inst = NULL;
if ($inst === NULL) {
$inst = new DBConn();
}
return $inst;
}
private function __construct() {
$db_file_created = true;
try {
$dbname = 'cpt-web.db';
$this->dbpath = build_file_path(cptBaseDir(), 'app', $dbname);
if (!file_exists($this->dbpath))
$db_file_created = false;
$this->db = new PDO("sqlite:{$this->dbpath}");
} catch (PDOException $e) {
print "Error: " . $e->getMessage() . "<br/>";
die();
}
if (file_exists($this->dbpath) && !$db_file_created)
chmod($this->dbpath, 0666);
}
public function __destruct() {
$this->db = NULL;
$this->history_db = NULL;
}
public function db() {
return $this->db;
}
public function history_db() {
if (!isset($this->history_db)) {
try {
$history_dbpath = $_SERVER['DOCUMENT_ROOT'] . '/sdcard/' . $this->history_dbname;
$this->history_db = new PDO("sqlite:{$history_dbpath}");
} catch (PDOException $e) {
print "Error: " . $e->getMessage() . "<br/>";
die();
}
}
return $this->history_db;
}
public function getColumnNames($dbh, $table) {
$col_names = array() ;
$stmt = $dbh->prepare("SELECT sql FROM sqlite_master WHERE tbl_name = ':table'") ;
$stmt->bindValue(':table', $table);
$stmt->execute() ;
$row = $stmt->fetch() ;
$sql = $row[0] ;
$r = preg_match("/\(\s*(\S+)[^,)]*/", $sql, $m, PREG_OFFSET_CAPTURE) ;
while ($r) {
array_push( $col_names, $m[1][0] ) ;
$r = preg_match("/,\s*(\S+)[^,)]*/", $sql, $m, PREG_OFFSET_CAPTURE, $m[0][1] + strlen($m[0][0]) ) ;
}
return $col_names;
}
};
class BaseModel {
public function create($attrs) {
$tblName = $this->tableName();
if (is_null($tblName))
return NULL;
if (!isset($attrs))
return NULL;
$db = DBConn::instance()->db();
$cols = implode(',', array_keys($attrs));
$vals = array();
foreach(array_keys($attrs) as $k) {
$vals[] = ":$k";
}
$vals = implode(',', $vals);
$command = "INSERT INTO {$tblName} ($cols) VALUES ($vals);";
$stmt = $db->prepare($command);
foreach($attrs as $k=>$v) {
$stmt->bindValue(":$k", $v);
}
if ($stmt->execute())
return $db->lastInsertId();
else
return false;
}
public function update($attrs, $selectors) {
$tblName = $this->tableName();
if (is_null($tblName))
return NULL;
if (!isset($attrs) || !isset($selectors))
return NULL;
$db = DBConn::instance()->db();
$assignments = array();
foreach(array_keys($attrs) as $k) {
$assignments[] = "$k=?";
}
$assignment = implode(',', $assignments);
$conditions = array();
foreach(array_keys($selectors) as $k) {
$conditions[] = "$k=?";
}
$condition = implode(' AND ', $conditions);
$command = "UPDATE {$tblName} SET {$assignment} WHERE {$condition}";
$stmt = $db->prepare($command);
$index = 1;
foreach(array_keys($attrs) as $k) {
$stmt->bindValue($index, $attrs[$k]);
++$index;
}
foreach(array_keys($selectors) as $k) {
$stmt->bindValue($index, $selectors[$k]);
++$index;
}
$stmt->execute();
return true;
}
public function read() {
}
public function delete() {
}
protected function tableName() {
return NULL;
}
protected function find_by($colname, $value) {
$tblName = $this->tableName();
if (is_null($tblName))
return NULL;
$db = DBConn::instance()->db();
$query = "SELECT * FROM {$tblName} WHERE {$colname} = ?";
$stmt = $db->prepare($query);
$stmt->bindValue(1, $value);
$stmt->execute();
return $stmt->fetchAll();
}
protected function find_by2($attrs) {
$tblName = $this->tableName();
if (is_null($tblName))
return NULL;
$conditions = array();
foreach(array_keys($attrs) as $k) {
$conditions[] = "$k=:$k";
}
$condition = implode(' AND ', $conditions);
$db = DBConn::instance()->db();
$query = "SELECT * FROM {$tblName} WHERE {$condition}";
$stmt = $db->prepare($query);
foreach(array_keys($attrs) as $k) {
$stmt->bindValue(":$k", $attrs[$k]);
}
$stmt->execute();
return $stmt->fetchAll();
}
};
class Permission extends BaseModel {
protected function tableName() {
return 'permissions';
}
public function permissions($user_id=null) {
$db = DBConn::instance()->db();
$stmt = null;
if (is_null($user_id)) {
$query = <<<SQL
SELECT u.id as user_id, u.name as name, u.home_page as home_page, u.dev_readable as dev_readable, u.dev_writable as dev_writable, p.path, p.readable, p.writable
FROM users AS u LEFT OUTER JOIN permissions AS p ON u.id = p.user_id
ORDER BY user_id DESC;
SQL;
$stmt = $db->prepare($query);
} else {
$query = <<<SQL
SELECT u.id as user_id, u.name as name, u.home_page as home_page, u.dev_readable as dev_readable, u.dev_writable as dev_writable, p.path, p.readable, p.writable
FROM users AS u LEFT OUTER JOIN permissions AS p ON u.id = ? and u.id = p.user_id;
SQL;
$stmt = $db->prepare($query);
$stmt->bindValue(1, $user_id);
}
$stmt->execute();
return $stmt->fetchAll();
}
public function delPermissionsByUserId($user_id) {
$db = DBConn::instance()->db();
$command = "DELETE FROM permissions WHERE user_id = :user_id";
$stmt = $db->prepare($command);
$stmt->bindValue(':user_id', $user_id);
$stmt->execute();
}
public function findByUserAndPath($user_id, $path) {
$db = DBConn::instance()->db();
$query = "SELECT * FROM {$this->tableName()} WHERE user_id = :user_id AND path = :path;";
$stmt = $db->prepare($query);
$stmt->bindValue(':user_id', $user_id);
$stmt->bindValue(':path', $path);
$stmt->execute();
return $stmt->fetchAll();
}
};
class User extends BaseModel {
protected function tableName() {
return 'users';
}
public function findById($id) {
$rows = parent::find_by('id', $id);
if (sizeof($rows) == 0)
return false;
$this->attrs = $rows[0];
return true;
}
public function find($name) {
$rows = parent::find_by('name', $name);
if (sizeof($rows) == 0)
return false;
$this->attrs = $rows[0];
return true;
}
public function attr($name) {
return $this->attrs[$name];
}
public function authenticate($password) {
$checksum = sha1($password . $this->attr('salt'))
return $checksum == $this->attr('checksum');
}
public function authenticate2($authHash, $authRand) {
$checksum = sha1($this->attr('checksum') . $authRand)
return $checksum == $authHash;
}
public function changePassword($password) {
$checksum = sha1($password . $this->attr('salt'))
return $this->changePassword2($checksum);
}
public function changePassword2($checksum) {
$result = false;
$result = $this->update(array('checksum' => $checksum), array('id' => $this->attr('id')));
if ($result && $this->attr("name") == "admin")
updatePhpAdminAuth($this->attr('salt'), $checksum);
return $result;
}
public function changeHomePage($home_page) {
if (function_exists('filter_var'))
$home_page = filter_var($home_page, FILTER_SANITIZE_URL);
if ($home_page == $this->attr('home_page'))
return;
return $this->update(array('home_page' => $home_page), array('id' => $this->attr('id')));
}
public function createAccount($name, $password, $utility_enabled, $system_enabled, $account_management_enabled, $password_change_enabled, $dashboard_enabled, $dashboard_as_landing_page) {
if ($this->find($name))
return false;
$salt = randStr();
$checksum = sha1($password . $salt)
return $this->createAccount2($name, $checksum, $salt, $utility_enabled, $system_enabled, $account_management_enabled, $password_change_enabled, $dashboard_enabled, $dashboard_as_landing_page);
}
public function createAccount2($name, $checksum, $salt, $utility_enabled, $system_enabled, $account_management_enabled, $password_change_enabled, $dashboard_enabled, $dashboard_as_landing_page) {
if ($this->find($name))
return false;
$vals = array(
'name' => $name,
'salt' => $salt,
'checksum' => $checksum,
'utility_enabled' => $utility_enabled,
'system_enabled' => $system_enabled,
'account_management_enabled' => $account_management_enabled,
'password_change_enabled' => $password_change_enabled,
'dashboard_enabled' => $dashboard_enabled,
'dashboard_as_landing_page' => $dashboard_as_landing_page);
if ($dashboard_enabled == 't')
$vals['dev_readable'] = 't';
$this->create($vals);
return true;
}
private function hashPassword($password, $salt) {
return sha1($password . $salt)
}
public function isAdmin() {
return $this->attr('is_admin') == 't' ;
}
public function isUtilityEnabled() {
return $this->isAdmin() || $this->attr('utility_enabled') == 't';
}
public function isSystemEnabled() {
return $this->isAdmin() || $this->attr('system_enabled') == 't';
}
public function isAccountManagementEnabled() {
return $this->isAdmin() || $this->attr('account_management_enabled') == 't';
}
public function isPasswordChangeEnabled() {
return $this->isAdmin() || $this->attr('password_change_enabled') == 't';
}
public function isDashboardEnabled() {
return $this->attr('dashboard_enabled') == 't';
}
public function isDashboardLanding() {
return $this->attr('dashboard_as_landing_page') == 't';
}
public function can($action, $grPath=null) {
if ($this->isAdmin())
return true;
if (is_null($grPath))
{
if ($action == 'read')
return strcasecmp($this->attr('dev_readable'), 't') == 0;
else if ($action == 'write')
return strcasecmp($this->attr('dev_writable'), 't') == 0;
else
return false;
}
$perm = new Permission();
$perm_datas = $perm->findByUserAndPath($this->attr('id'), $grPath);
if (count($perm_datas) == 0)
return true;
foreach($perm_datas as $data) {
if (strcasecmp($data['readable'], 'f') == 0 || strcasecmp($data['readable'], 'false') == 0)
return false;
if ($action == 'write' && (strcasecmp($data['writable'], 'f') == 0 || strcasecmp($data['writable'], 'false') == 0))
return false;
}
return true;
}
public function canDashboard($action, $dashboardId) {
if ($this->isAdmin())
return true;
$perm_datas = DashboardPermission::findByUserAndDashboard($this->attr('id'), $dashboardId);
if (count($perm_datas) == 0)
return false;
foreach($perm_datas as $data) {
if (!isset($data[$action]))
continue;
return strcasecmp($data[$action], 't') == 0;
}
return true;
}
public function updatePermissions($perms, $dev_readable='f', $dev_writable='f') {
$user_id = $this->attr("id");
$new_datas = array();
if (isset($perms))
{
foreach($perms as $perm) {
$attrs = array();
$attrs['user_id'] = $user_id;
$attrs['path'] = $perm['path'];
if (!isset($attrs['path'])) {
return "invalid path";
}
$readable = (strcasecmp($perm['readable'], 'true') == 0) || (strcasecmp($perm['readable'], 't') == 0);
$attrs['readable'] = $readable ? 't' : 'f';
$writable = (strcasecmp($perm['writable'], 'true') == 0) || (strcasecmp($perm['writable'], 't') == 0);
$attrs['writable'] = $writable ? 't' : 'f';
$new_datas[] = $attrs;
}
}
$db = DBConn::instance()->db();
$db->beginTransaction();
// update dev perms
$dev_perm_data = array('dev_readable' => $dev_readable, 'dev_writable' => $dev_writable);
$result = $this->update($dev_perm_data, array('id' => $this->attr('id')));
if (!$result)
{
$db->rollBack();
return "update user's dev permission failed";
}
// update gr files permission
$model = new Permission();
$model->delPermissionsByUserId($user_id);
//TODO: optimize below loop to use query statement
foreach($new_datas as $attrs)
$model->create($attrs);
$db->commit();
return '';
}
public function updateDashboardPermissions($perms) {
$user_id = $this->attr("id");
$new_datas = array();
foreach($perms as $perm) {
$attrs = array();
$attrs['user_id'] = $user_id;
$attrs['dashboard_id'] = $perm['dashboardId'];
$attrs['canManageDataSource'] = $perm['canManageDataSource'];
$attrs['canManageWidget'] = $perm['canManageWidget'];
$attrs['canViewDashboard'] = $perm['canViewDashboard'];
$attrs['canWriteDataSource'] = $perm['canWriteDataSource'];
if (!isset($attrs['dashboard_id'])) {
return L("invalid dashboard_id");
}
$new_datas[] = $attrs;
}
$db = DBConn::instance()->db();
$db->beginTransaction();
$model = new DashboardPermission();
$model->delPermissionsByUserId($user_id);
//TODO: optimize below loop to use query statement
foreach($new_datas as $attrs)
$model->create($attrs);
$db->commit();
return '';
}
public function grBlackList() {
$user_id = $this->attr("id");
$db = DBConn::instance()->db();
$query = <<<SQL
SELECT p.path as path
FROM users AS u JOIN permissions AS p ON u.id = ? and u.id = p.user_id and p.readable = 'f' and p.writable = 'f';
SQL;
$stmt = $db->prepare($query);
$stmt->bindValue(1, $user_id);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
}
public function updateAccount($values) {
$user_id = $this->attr("id");
$db = DBConn::instance()->db();
$db->beginTransaction();
if (array_key_exists('password', $values)) {
$this->changePassword($values['password']);
unset($values['password']);
}
if (array_key_exists('authHash', $values)) {
$this->changePassword2($values['authHash']);
unset($values['authHash']);
}
//when user is dashboard enabled, then it should also have 'dev_readable'
//perm, otherwise data polling will not work in dashboard
if (array_key_exists('dashboard_enabled', $values)) {
if ($values['dashboard_enabled'] == 't')
$values['dev_readable'] = 't';
}
unset($values['id']);
//FIXME: this may cause SQL injection issue, since the keys in $values can
//possibly provided by user
if (!empty($values))
$this->update($values, array('id' => $user_id));
$db->commit();
}
public function deleteAccount() {
$user_id = $this->attr("id");
$db = DBConn::instance()->db();
$db->beginTransaction();
$tblName = $this->tableName();
$command = "DELETE FROM {$tblName} WHERE id = :id ;";
$stmt = $db->prepare($command);
$stmt->bindValue(':id', $user_id);
$stmt->execute();
$perm = new Permission();
$perm->delPermissionsByUserId($user_id);
$dperm = new DashboardPermission();
$dperm->delPermissionsByUserId($user_id);
$db->commit();
}
public function accounts($user_id=null) {
$db = DBConn::instance()->db();
$stmt = null;
if (is_null($user_id)) {
$query = <<<SQL
SELECT id as user_id, name, utility_enabled, system_enabled, account_management_enabled, password_change_enabled, dashboard_enabled, dashboard_as_landing_page
FROM users ORDER BY user_id DESC;
SQL;
$stmt = $db->prepare($query);
} else {
$query = <<<SQL
SELECT id as user_id, name, utility_enabled, system_enabled, account_management_enabled, password_change_enabled, dashboard_enabled, dashboard_as_landing_page
FROM users WHERE id = ? ORDER BY user_id DESC;
SQL;
$stmt = $db->prepare($query);
$stmt->bindValue(1, $user_id);
}
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
protected $attrs = NULL;
};
function output_history_data($stmt, &$formatter, $download_file_name, $orig_columns=null, $bool_text_mappings=null, $dbh=null, $table=null, $dt_tz=null) {
$first = true;
$utc_tz = new DateTimeZone('UTC');
$formatter->writeHeader($download_file_name);
$dt_format = 'Y-m-d H:i:s';
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$bool_col_indexes = array();
while($row = $stmt->fetch()) {
//use '_dt' to overwrite 'dt' column
if (array_key_exists('dt', $row) && array_key_exists('_dt', $row))
{
$row['dt'] = $row['_dt'];
unset($row['_dt']);
}
//get headers row
if ($first) {
$first = false;
$formatter->writeRow(array_keys($row));
if (!is_null($bool_text_mappings)) {
$col_count = count(array_keys($row));
for($i=0; $i< $col_count; ++$i) {
$meta = $stmt->getColumnMeta($i);
if (!is_null($meta) && array_key_exists('sqlite:decl_type', $meta) && $meta['sqlite:decl_type'] == 'BOOLEAN') {
$bool_col_indexes[] = $i;
}
}
}
}
//convert dt to given timezone
if (isset($dt_tz) && $dt_tz->getName() != 'UTC' && array_key_exists('dt', $row)) {
$dt_in_utc = DateTime::createFromFormat($dt_format, $row['dt'], $utc_tz);
$row['dt'] = $dt_in_utc->setTimeZone($dt_tz)->format($dt_format);
}
$col_values = array_values($row);
if (!is_null($bool_text_mappings)) {
// convert bool to user given boolean text if there is any
for($i=0; $i<count($bool_col_indexes); ++$i) {
$bool_index = $bool_col_indexes[$i];
$bool_val = $col_values[$bool_index];
if (!array_key_exists($bool_val, $bool_text_mappings))
continue;
$bool_text = $bool_text_mappings[$bool_val];
if (!is_null($bool_text))
$col_values[$bool_index] = $bool_text;
}
}
$formatter->writeRow($col_values);
}
// error_log("### elapsed after fetch: " . $etime->elapsed());
//when there is no data, try to return row of columns
if ($formatter->isEmpty() && (!is_null($orig_columns))) {
if ($orig_columns != '*') {
$formatter->writeRow(explode(',', $orig_columns));
} else {
if (!is_null($dbh) && !is_null($table)) {
$col_names = DBConn::instance()->getColumnNames($dbh, $table);
$formatter->writeRow($col_names);
}
}
}
$formatter->writeFooter();
$formatter->done();
};
class Dashboard extends BaseModel {
protected $attrs = array();
protected function tableName() {
return 'dashboards';
}
public function findById($id) {
$rows = parent::find_by('id', $id);
if (sizeof($rows) == 0)
return false;
$this->attrs = $rows[0];
return true;
}
public function isNull() {
return !isset($this->attrs) || empty($this->attrs);
}
public function attr($name) {
if (isset($this->attrs[$name]))
return $this->attrs[$name];
else
return null;
}
public function toJson() {
return map2json($this->attrs);
}
public function createDashboard($name, $content) {
$vals = array(
'name' => $name,
'content' => $content,
'parent_id' => 1
);
return $this->create($vals);
}
public function saveDashboard($name=NULL, $content=NULL) {
$vals = array();
if ($name)
$vals['name'] = $name;
if ($content)
$vals['content'] = $content;
return $this->update($vals, array('id' => $this->attr('id')));
}
public function renameDashboard($id, $new_name) {
if (!$this->findById($id))
return false;
return $this->saveDashboard($new_name);
}
public static function updateLayout($id, $parent_id, $sibling_ids) {
$stmt = null;
$db = DBConn::instance()->db();
$db->beginTransaction();
$query = 'UPDATE dashboards SET parent_id=? WHERE id=?;';
$stmt = $db->prepare($query);
$stmt->bindValue(1, $parent_id);
$stmt->bindValue(2, $id);
$stmt->execute();
$query = 'UPDATE dashboards SET parent_id=?, order_val=? WHERE id=?;';
$stmt = $db->prepare($query);
foreach($sibling_ids as $index=>$sid) {
$stmt->bindValue(1, $parent_id);
$stmt->bindValue(2, $index+1);
$stmt->bindValue(3, $sid);
$stmt->execute();
}
return $db->commit();
}
public static function deleteDashboard($id_list) {
$db = DBConn::instance()->db();
$db->beginTransaction();
$command = "DELETE FROM dashboards WHERE id IN ( :ids ) ;";
$stmt = $db->prepare($command);
$stmt->bindValue(':ids', implode(",", $id_list));
if (!$stmt->execute())
{
$db->rollBack();
return false;
}
foreach($id_list as $dashboardId)
DashboardPermission::delPermissionsByDashboardId($dashboardId);
$db->commit();
return true;
}
public static function dashboardList() {
$db = DBConn::instance()->db();
$stmt = null;
$query = <<<SQL
SELECT id, name, parent_id FROM dashboards ORDER BY parent_id, order_val ASC;
SQL;
$stmt = $db->prepare($query);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
public static function indexDashboardByUser($user_id) {
$db = DBConn::instance()->db();
$query = <<<SQL
SELECT id FROM dashboards WHERE parent_id = 1
AND id NOT IN (SELECT dashboard_id FROM dashboard_permissions WHERE user_id = :user_id AND canViewDashboard = 'f') ORDER BY order_val, id ASC LIMIT 1;
SQL;
$stmt = $db->prepare($query);
$stmt->bindValue(':user_id', $user_id);
$stmt->execute();
$dashboardId = $stmt->fetchColumn();
$stmt->closeCursor();
$dashboard = new Dashboard();
if (isset($dashboardId))
$dashboard->findById($dashboardId);
return $dashboard;
}
};
class DashboardPermission extends BaseModel {
protected $attrs = array();
protected function tableName() {
return 'dashboard_permissions';
}
public function attr($name) {
return $this->attrs[$name];
}
public function permissions($user_id=null, $dashboard_id=null) {
$db = DBConn::instance()->db();
$stmt = null;
$query = <<<SQL
SELECT u.id as userId, u.name as userName, d.id as dashboardId, d.name as pageName, p.canManageDataSource, p.canManageWidget, p.canViewDashboard, p.canWriteDataSource
FROM users AS u JOIN dashboard_permissions AS p ON u.id = p.user_id JOIN dashboards as d ON d.id = p.dashboard_id
SQL;
$where_conditions = array();
if (!is_null($user_id))
$where_conditions[] = ' u.id = :user_id ';
if (!is_null($dashboard_id))
$where_conditions[] = ' d.id = :dashboard_id ';
if (!empty($where_conditions))
$query = $query . ' WHERE ' . implode(' AND ', $where_conditions);
$query = $query . ' ORDER BY userId DESC;';
$stmt = $db->prepare($query);
if (!is_null($user_id))
$stmt->bindValue(':user_id', $user_id);
if (!is_null($dashboard_id))
$stmt->bindValue(':dashboard_id', $dashboard_id);
$stmt->execute();
$perms = $stmt->fetchAll(PDO::FETCH_ASSOC);
$users = array();
$dashboards = array();
if (is_null($user_id))
$users = $db->query('SELECT id AS userId, name AS userName, role_id FROM users')->fetchAll();
else {
$user_stmt = $db->prepare("SELECT id AS userId, name AS userName, role_id FROM users WHERE id = ?");
$user_stmt->bindValue(1, $user_id);
$user_stmt->execute();
$users = $user_stmt->fetchAll();
}
if (is_null($dashboard_id))
$dashboards = $db->query('SELECT id AS dashboardId, name AS pageName FROM dashboards WHERE id != 1 ')->fetchAll();
else {
$dashboards_stmt = $db->prepare('SELECT id AS dashboardId, name AS pageName FROM dashboards WHERE id = ? ');
$dashboards_stmt->bindValue(1, $dashboard_id);
$dashboards_stmt->execute();
$dashboards = $dashboards_stmt->fetchAll();
}
$result = array();
foreach($users as $user) {
$userId = $user['userId'];
$userName = $user['userName'];
$roleId = intval($user['role_id']);
foreach($dashboards as $dashboard) {
$dashboardId = $dashboard['dashboardId'];
$pageName = $dashboard['pageName'];
$permExisted = false;
foreach($perms as $perm) {
if ($perm['userId'] == $userId && $perm['userName'] == $userName && $perm['pageName'] == $pageName)
{
$permExisted = true;
$row = array();
foreach($perm as $key => $val)
$row[$key] = $val;
$result[] = $row;
break;
}
}
if (!$permExisted) {
$canManageDataSource = $roleId == 1;
$canManageWidget = $roleId == 1 || $roleId == 2;
$canWriteDataSource = $roleId == 1 || $roleId == 2 || $roleId == 3;
$canViewDashboard = true;
$result[] = array(
'userId' => $userId,
'userName' => $userName,
'dashboardId' => $dashboardId,
'pageName' => $pageName,
'canManageDataSource' => ($canManageDataSource ? 't' : 'f'),
'canManageWidget' => ($canManageWidget ? 't' : 'f'),
'canWriteDataSource' => ($canWriteDataSource ? 't' : 'f'),
'canViewDashboard' => ($canViewDashboard ? 't' : 'f')
);
}
}
}
return $result;
}
public function delPermissionsByUserId($user_id) {
$db = DBConn::instance()->db();
$command = "DELETE FROM dashboard_permissions WHERE user_id = :user_id";
$stmt = $db->prepare($command);
$stmt->bindValue(':user_id', $user_id);
$stmt->execute();
}
public static function delPermissionsByDashboardId($dashboard_id) {
$db = DBConn::instance()->db();
$command = "DELETE FROM dashboard_permissions WHERE dashboard_id = :dashboard_id";
$stmt = $db->prepare($command);
$stmt->bindValue(':dashboard_id', $dashboard_id);
$stmt->execute();
}
public static function findByUserAndDashboard($user_id, $dashboard_id) {
$db = DBConn::instance()->db();
$query = "SELECT * FROM dashboard_permissions WHERE user_id = :user_id AND dashboard_id = :dashboard_id;";
$stmt = $db->prepare($query);
$stmt->bindValue(':user_id', $user_id);
$stmt->bindValue(':dashboard_id', $dashboard_id);
$stmt->execute();
return $stmt->fetchAll();
}
};
class AuthKeys extends BaseModel {
protected $attrs = array();
protected function tableName() {
return 'auth_keys';
}
public function attr($name) {
return $this->attrs[$name];
}
public function find($key) {
$rows = parent::find_by('key', $key);
if (sizeof($rows) == 0)
return false;
$this->attrs = $rows[0];
return true;
}
public function createKey($note, $expired_at) {
$key = randStr(16);
$vals = array(
'key' => $key,
'note' => $note,
'expired_at' => $expired_at);
$this->create($vals);
return $key;
}
public function updateKey($key, $note, $expired_at) {
return $this->update(array('note' => $note, 'expired_at' => $expired_at),
array('key' => $key));
}
public function deleteKey($key) {
$db = DBConn::instance()->db();
$command = "DELETE FROM auth_keys WHERE key = :key";
$stmt = $db->prepare($command);
$stmt->bindValue(':key', $key);
$stmt->execute();
}
public function keys() {
$db = DBConn::instance()->db();
$query = "SELECT id, key, note, expired_at FROM auth_keys ORDER BY created_at DESC;";
$stmt = $db->prepare($query);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
public function isExpired() {
$tz = new DateTimeZone('UTC');
$now = new DateTime('NOW', $tz);
$expired_at = DateTime::createFromFormat('Y-m-d H:i:s', $this->attr('expired_at'), $tz);
return $now >= $expired_at;
}
public static function isValid($key) {
$instance = new AuthKeys();
if (!$instance->find($key))
return false;
return $instance->isExpired();
}
};
class FailedAuthCounter extends BaseModel {
protected $attrs = array();
protected function tableName() {
return 'failed_auth_counter';
}
public function attr($name) {
return $this->attrs[$name];
}
public function getCounter($name, $clientIP) {
$rows = parent::find_by2(array('name'=> $name, 'client_ip' => $clientIP));
if (sizeof($rows) == 0)
return 0;
$this->attrs = $rows[0];
return intval($this->attrs['counter']);
}
public function incrCounter($name, $clientIP) {
if (is_null($name))
return false;
$tblName = $this->tableName();
$rows = parent::find_by2(array('name' => $name, 'client_ip' => $clientIP));
if (sizeof($rows) == 0) {
$this->create(array('name' => $name, 'client_ip' => $clientIP, 'counter' => 1, 'lockout_time' => 0));
} else {
$this->attrs = $rows[0];
$counter = intval($this->attrs['counter']);
$this->update(array('counter' => $counter+1),
array('name' => $name, 'client_ip' => $clientIP));
}
return true;
}
public function delRecord($name, $clientIP) {
$db = DBConn::instance()->db();
$tblName = $this->tableName();
$command = "DELETE FROM {$tblName} WHERE name= :name and client_ip= :client_ip";
$stmt = $db->prepare($command);
$stmt->bindValue(':name', $name);
$stmt->bindValue(':client_ip', $clientIP);
$stmt->execute();
}
public function getLockoutTime($name, $clientIP) {
$rows = parent::find_by2(array('name' => $name,
'client_ip' => $clientIP));
if (sizeof($rows) == 0)
return 0;
$this->attrs = $rows[0];
return intval($this->attrs['lockout_time']);
}
public function updateLockoutTime($name, $clientIP, $now) {
return $this->update(array('lockout_time' => $now),
array('name' => $name, 'client_ip' => $clientIP));
}
};
?>
