protected function _load()
{
$connection = DbConnection::getInstance()->getConnection();
$sth = $connection->query("SELECT * FROM {$this->_tableName}");
$sth->execute();
$this->_itemsData = $sth->fetchAll();
}
public function __construct($sql, DbConnection $DbConnection = null, $paginate = false)
{
$this->_sql = $sql;
if (is_null($DbConnection)) {
$this->_DbConnection = DbConnection::getInstance();
}
$this->_paginate = $paginate;
}
public function getBatchCount()
{
$pdo = DbConnection::getInstance()->getConnection($this->databaseName);
$sql = 'select count(*) as batchCount from mock_batches where institute_id = ' . $this->organizationId;
$prepped = $pdo->query($sql);
$row = $prepped->fetch(PDO::FETCH_ASSOC);
return $row["batchCount"];
}
function __construct($conn = NULL)
{
if (is_null($conn)) {
$this->_conn = DbConnection::getInstance();
} else {
$this->_conn = $conn;
}
$this->getTableField();
}
public function listAction()
{
$DbConnection = DbConnection::getInstance();
$sql = "SELECT * FROM categoria";
$categories = $DbConnection->getAll($sql);
$View = new ListView();
$View->setRows($categories);
$View->display();
}
/**
* Gets a {@link UserModel} from the unique user.name
* @param string $name
* @return UserModel
*/
public static function getByName($name)
{
$sql = "SELECT user_id\n FROM user\n WHERE name='" . mysql_escape_string($name) . "'\n LIMIT 1;";
$DbConnection = DbConnection::getInstance();
if (!($user_id = $DbConnection->getOne($sql))) {
return false;
}
return new UserModel($user_id);
}
/**
* Deletes a generic {@link Row}
*
* @param integer $id
* @param string $table_name
* @return True on success, false otherwise
*/
public static function deleteRow($id, $table_name)
{
$DbConnection = DbConnection::getInstance();
$Row = new Row($table_name, (int) $data["id_{$table_name}"], $DbConnection);
if ($Row->delete()) {
return false;
}
return true;
}
public static function saveAccess($data)
{
if ($data['allow'] == "true") {
$sql = "INSERT INTO role_acl (role_id, acl_id) VALUES(:role, :route)";
} else {
$sql = "DELETE FROM role_acl WHERE role_id= :role AND acl_id= :route";
}
unset($data['allow']);
$sth = DbConnection::getInstance()->getConnection()->prepare($sql);
$sth->execute($data);
}
public function ajax_contact_messages($startFrom)
{
$db = DbConnection::getInstance()->getPDO();
$sth = $db->prepare("SELECT name, email, message, date FROM messages ORDER BY date DESC LIMIT {$startFrom}, 5");
$sth->execute();
$messages = $sth->fetchAll(PDO::FETCH_ASSOC);
if (!$messages) {
throw new Exception('email not found');
}
return $messages;
}
public function transfer()
{
try {
$sth = DbConnection::getInstance()->getConnection()->prepare("CALL UserTransfer(?)");
$sth->execute(array($this->getId()));
$this->setPatientId(DbConnection::getInstance()->getConnection()->lastInsertId());
} catch (\Exception $e) {
echo 'TRANSFER REJECTED.';
die;
}
}
public function getUser($username, $password)
{
$db = DbConnection::getInstance()->getPDO();
$sth = $db->prepare('SELECT id, username FROM users WHERE username = :username AND password = :password');
$params = array('username' => $username, 'password' => (string) $password);
$sth->execute($params);
$data = $sth->fetch(PDO::FETCH_ASSOC);
if (!$data) {
throw new Exception('User not found');
}
return $data;
}
public function getById($id)
{
$db = DbConnection::getInstance()->getPDO();
$sth = $db->prepare('select b.title, b.description, b.price, a.name as author from books b join authors a on a.id = b.author_id where status = 1 and b.id = :book_id');
$params = array('book_id' => $id);
$sth->execute($params);
$book = $sth->fetch(PDO::FETCH_ASSOC);
if (!$book) {
throw new Exception('Book not found, sorry', 404);
}
return $book;
}
/**
* @param string $name
* @return UserModel
*/
public static function getUserByName($name)
{
$DbConnection = DbConnection::getInstance();
$Config = Config::getInstance();
$sql = "SELECT {$Config->user_table_id} FROM {$Config->user_table} WHERE name='{$name}' LIMIT 1";
if (!($user_id = $DbConnection->getOneValue($sql))) {
return false;
}
$User = new UserModel($Config->user_table, (int) $user_id);
$User->load();
return $User;
}
public function __construct($connection = null)
{
if ($connection instanceof \PDO) {
$this->db = $connection;
} else {
if ($connection != null) {
$this->db = DbConnection::getInstance()->getDBConnection($connection);
$this->connection = $connection;
} else {
$this->db = DbConnection::getInstance()->getDBConnection($this->connection);
}
}
}
/**
* Creates DataAccessObject Instance
* @param string $table_name
* @param string $id
* @return DataAccessObject
*/
public function __construct($tableName, $id)
{
if (!is_string($tableName)) {
throw new InvalidArgumentException("table_name isn't an string");
}
if (!is_integer($id)) {
throw new InvalidArgumentException("id isn't an integer");
}
$this->_DbConnection = DbConnection::getInstance();
$this->_tableName = $tableName;
$this->_id = $id;
$this->_idField = "{$tableName}_id";
}
/**
* @see IUserAuthentication::verifyAndUpdateCurrentUser()
*/
public function verifyAndUpdateCurrentUser(User $currentUser)
{
$db = DbConnection::getInstance();
$fromTable = $this->_website->getConfig('db_prefix') . '_user';
if (!isset($_SESSION[SESSION_PARAM_USERID]) || !$_SESSION[SESSION_PARAM_USERID]) {
// 'remember me' token
$rememberMe = CookieHelper::getCookieValue('user');
if ($rememberMe != null) {
$columns = 'id, passwort_salt, nick, email, lang';
$whereCondition = 'status = 1 AND tokenid = \'%s\'';
$result = $db->querySelect($columns, $fromTable, $whereCondition, $rememberMe);
$rememberedUser = $result->fetch_array();
$result->free();
if (isset($rememberedUser['id'])) {
$currentToken = SecurityUtil::generateSessionToken($rememberedUser['id'], $rememberedUser['passwort_salt']);
if ($currentToken === $rememberMe) {
$this->_login($rememberedUser, $db, $fromTable, $currentUser);
return;
} else {
CookieHelper::destroyCookie('user');
// invalid old token since most probably user agent changed
$columns = array('tokenid' => '');
$whereCondition = 'id = %d';
$parameter = $rememberedUser['id'];
$db->queryUpdate($columns, $fromTable, $whereCondition, $parameter);
}
} else {
CookieHelper::destroyCookie('user');
}
// user is neither in session nor with cookie logged on
} else {
return;
}
}
// get user data
$userid = isset($_SESSION[SESSION_PARAM_USERID]) ? $_SESSION[SESSION_PARAM_USERID] : 0;
if (!$userid) {
return;
}
$columns = 'id, nick, email, lang, premium_balance, picture';
$whereCondition = 'status = 1 AND id = %d';
$result = $db->querySelect($columns, $fromTable, $whereCondition, $userid);
if ($result->num_rows) {
$userdata = $result->fetch_array();
$this->_login($userdata, $db, $fromTable, $currentUser);
} else {
// user might got disabled in the meanwhile
$this->logoutUser($currentUser);
}
$result->free();
}
public static function save($userName, $userPhone, $userId, $userMessage, $products)
{
$db = DbConnection::getInstance()->getPDO();
$sql = 'INSERT INTO product_order (userName, userPhone, userId,userMessage, products) ' . 'VALUES (:userName, :userPhone, :userId,:userMessage, :products)';
$products = json_encode($products);
//сериализуем массив товаров заказа
$sth = $db->prepare($sql);
$sth->bindParam(':userName', $userName, PDO::PARAM_STR);
$sth->bindParam(':userPhone', $userPhone, PDO::PARAM_STR);
$sth->bindParam(':userId', $userId, PDO::PARAM_STR);
$sth->bindParam(':userMessage', $userMessage, PDO::PARAM_STR);
$sth->bindParam(':products', $products, PDO::PARAM_STR);
return $sth->execute();
}
public function changeAction()
{
$db = DbConnection::getInstance();
$rs = $db->query("SELECT * FROM tb_content");
foreach ($rs as $row) {
$title = trim($this->textToVN(strip_tags($row['title'])));
$short_body = trim($this->textToVN(strip_tags($row['short_body'])));
$long_body = trim($this->textToVN(strip_tags($row['long_body'])));
$sql = "UPDATE tb_content SET title = \"{$title}\",short_body = \"{$short_body}\",long_body = \"{$long_body}\" WHERE id = {$row['id']}";
$db->query($sql);
}
$rs = $db->query("SELECT * FROM tb_content");
$this->_view->rs = $rs;
$this->renderView('site/index/change');
// $sql = "UPDATE users SET first_name = '{$_POST['first_name']}',last_name = '{$_POST['last_name']}',email = '{$_POST['email']}',address = '{$_POST['address']}' WHERE user_id = {$id}";
// $db->query($sql);
}
public function editAction($id)
{
$db = DbConnection::getInstance();
$this->_view->title = 'Normal Edit Form';
$this->_view->link = base_url() . 'database/model/edit/' . $id;
$row = $db->query("SELECT * FROM users WHERE user_id = " . $id)->fetch();
if (empty($row)) {
redirect('database/model/index');
}
if (!empty($_POST)) {
$sql = "UPDATE users SET first_name = '{$_POST['first_name']}',last_name = '{$_POST['last_name']}',email = '{$_POST['email']}',address = '{$_POST['address']}' WHERE user_id = {$id}";
$db->query($sql);
redirect('database/model/index');
}
$data = array('first_name' => $row['first_name'], 'last_name' => $row['last_name'], 'email' => $row['email'], 'address' => $row['address']);
$this->_view->data = $data;
$this->renderView('database/model/_form');
}
/**
* @see IConverter::toDbValue()
*/
public function toDbValue($value)
{
$amount = (int) $value;
if (isset($_POST['user_id']) && $_POST['user_id']) {
// get current user budget
$db = DbConnection::getInstance();
$columns = 'premium_balance';
$fromTable = $this->_websoccer->getConfig('db_prefix') . '_user';
$whereCondition = 'id = %d';
$result = $db->querySelect($columns, $fromTable, $whereCondition, $_POST['user_id'], 1);
$user = $result->fetch_array();
$result->free();
// update budget in DB
$budget = $user['premium_balance'] + $amount;
$updatecolumns = array('premium_balance' => $budget);
$db->queryUpdate($updatecolumns, $fromTable, $whereCondition, $_POST['user_id']);
}
return $amount;
}
/**
* @see IConverter::toDbValue()
*/
public function toDbValue($value)
{
$amount = (int) $value;
if (isset($_POST['verein_id']) && $_POST['verein_id']) {
// get current team budget
$db = DbConnection::getInstance();
$columns = 'finanz_budget';
$fromTable = $this->_websoccer->getConfig('db_prefix') . '_verein';
$whereCondition = 'id = %d';
$result = $db->querySelect($columns, $fromTable, $whereCondition, $_POST['verein_id'], 1);
$team = $result->fetch_array();
$result->free();
// update budget in DB
$budget = $team['finanz_budget'] + $amount;
$updatecolumns = array('finanz_budget' => $budget);
$db->queryUpdate($updatecolumns, $fromTable, $whereCondition, $_POST['verein_id']);
}
return $amount;
}
public static function addUser($params)
{
$status = 'Success';
try {
$db = DbConnection::getInstance()->getPDO();
$sth = $db->prepare('INSERT INTO shooters VALUES (
null,
:nickname,
:first_name,
:last_name,
:email,
:password
)');
$sth->execute($params);
} catch (PDOException $e) {
$status = 'Fail: ' . $e->getMessage();
}
return $status;
}
public function __construct($table_name, $id, DbConnection $DbConnection = null)
{
if (!isset($DbConnection)) {
$DbConnection = DbConnection::getInstance();
}
if (!is_string($table_name)) {
throw new InvalidArgumentException("table_name isn't an string");
}
if (!is_integer($id)) {
throw new InvalidArgumentException("id isn't an integer");
}
if (get_class($DbConnection) !== 'DbConnection') {
throw new InvalidArgumentException("DbConnection isn't a DbConnection");
}
$this->table_name = $table_name;
$this->id = $id;
$this->id_field = "{$table_name}_id";
$this->DbConnection = $DbConnection;
}
/**
* @see IValidator::isValid()
*/
public function isValid()
{
$db = DbConnection::getInstance();
// any cup with same name (but different ID)?
$result = $db->querySelect('id', $this->_websoccer->getConfig('db_prefix') . '_cup', 'name = \'%s\'', $this->_value, 1);
$cups = $result->fetch_array();
$result->free();
if (isset($cups['id']) && (!isset($_POST['id']) || $_POST['id'] !== $cups['id'])) {
return FALSE;
}
// any match using the name for cup name?
$result = $db->querySelect('COUNT(*) AS hits', $this->_websoccer->getConfig('db_prefix') . '_spiel', 'pokalname = \'%s\'', $this->_value);
$matches = $result->fetch_array();
$result->free();
if ($matches['hits'] && !isset($_POST['id'])) {
return FALSE;
}
return TRUE;
}
/**
* @see IConverter::toDbValue()
*/
public function toDbValue($value)
{
if (isset($_POST['id']) && $_POST['id']) {
$db = DbConnection::getInstance();
$columns = 'passwort, passwort_salt';
$fromTable = $this->_websoccer->getConfig('db_prefix') . '_admin';
$whereCondition = 'id = %d';
$result = $db->querySelect($columns, $fromTable, $whereCondition, $_POST['id'], 1);
$admin = $result->fetch_array();
$result->free();
if (strlen($value)) {
$passwort = SecurityUtil::hashPassword($value, $admin['passwort_salt']);
} else {
$passwort = $admin['passwort'];
}
} else {
$passwort = SecurityUtil::hashPassword($value, '');
}
return $passwort;
}
/**
*
* @return unknown_type
* @todo $_POST shouldn't be used, check how this can be integrated with {@link Request}
*/
public function saveAction()
{
$View = new View('category/edit');
$View->assign('_title_', _('View Category'));
$id = isset($_POST['CatID']) ? (int) $_POST['CatID'] : 0;
$Category = new CategoryModel($id);
$Category->load();
$Category->Categoria = $_POST['Categoria'];
if (!$Category->save()) {
$_SESSION['_MESSAGE_'] = _('Couldn\'t save Category');
header('Location: ' . BASE_URL . '/category');
exit;
}
$_SESSION['_MESSAGE_'] = _('Saved');
if ($id == 0) {
$DbConnection = DbConnection::getInstance();
$id = $DbConnection->getLastId();
}
header('Location: ' . BASE_URL . "/category/view/?id={$id}");
}
/**
* Call all the necessary methods
*/
public function read($directory, $fileName, $parameters, $config = array(), $left = 0, $top = 0, $subreport = false)
{
//geting the bean
$xmlContent = FileUtils::readFile($directory . trim($fileName));
//Turn the jrxml to bean
$jasper = BeanUtils::xmlToBean($xmlContent, 'Jasper');
//Get the db stuff
$dbResult = null;
if ($jasper->queryString) {
$dbResult = DbConnection::getInstance()->listAll($jasper->queryString->content, $parameters);
}
$left += $jasper->leftMargin;
$top += $jasper->topMargin;
$jasperXml = new JasperXml();
$jasperXml->dbResult = $dbResult;
$jasperXml->config = $config;
$jasperXml->jasper = $jasper;
$jasperXml->parameters = $parameters;
$jasperXml->directory = $directory;
return $jasperXml->printJasper($left, $top, $subreport);
}
public function getBatches($options = array())
{
$data = array();
$pdo = DbConnection::getInstance()->getConnection($this->databaseName);
$like = '';
$params = array();
if (array_key_exists("code", $options)) {
$like = " AND code LIKE :code OR comments LIKE :comment";
$params[":code"] = "%{$options["code"]}%";
$params[":comment"] = "%{$options["code"]}%";
}
$countSql = 'select count(*) as batchCount from mock_batches where org_id = ' . $this->organizationId . $like;
//$prepped = $pdo->query($sql);
$prepped = $pdo->prepare($countSql);
$prepped->execute($params);
$row = $prepped->fetch(PDO::FETCH_ASSOC);
$data["count"] = $row["batchCount"];
$start = '';
if (array_key_exists("start", $options)) {
$start = "{$options["start"]},";
}
$rowLimit = 10;
if (array_key_exists("rows", $options)) {
$rowLimit = $options["rows"];
}
$limit = " LIMIT {$start}{$rowLimit}";
$sql = 'select * from mock_batches where org_id = ' . $this->organizationId . $like . $limit;
$prepped = $pdo->prepare($sql);
//foreach($params as $name=>$value){
// $prepped->bindParam($name, $value);
//}
$prepped->execute($params);
//$prepped->execute();
$batches = $this->getObjects($prepped);
$data["items"] = $batches;
return $data;
}
}
} catch (Exception $e) {
// write to log
try {
$log = new FileWriter('errorlog.txt');
$log->writeLine('Website Configuration Error: ' . $e->getMessage());
$log->close();
} catch (Exception $e) {
// ignore
}
header('HTTP/1.0 500 Error');
die;
}
// connect to DB
try {
$db = DbConnection::getInstance();
$db->connect($website->getConfig('db_host'), $website->getConfig('db_user'), $website->getConfig('db_passwort'), $website->getConfig('db_name'));
} catch (Exception $e) {
// write to log
try {
$log = new FileWriter('dberrorlog.txt');
$log->writeLine('DB Error: ' . $e->getMessage());
$log->close();
} catch (Exception $e) {
// ignore
}
die('<h1>Sorry, our data base is currently not available</h1><p>We are working on it.</p>');
}
// register own session handler
$handler = new DbSessionManager($db, $website);
session_set_save_handler(array($handler, 'open'), array($handler, 'close'), array($handler, 'read'), array($handler, 'write'), array($handler, 'destroy'), array($handler, 'gc'));
public function setAsLinked($field, $table_name, DbConnection $DbConnection = null, $table_id = '', $name_field = '', $condition = '')
{
if (!isset($DbConnection)) {
$DbConnection = DbConnection::getInstance();
}
if ($table_id == '') {
$table_id = "{$table_name}_id";
}
if ($name_field == '') {
$Config = Config::getInstance();
$name_field = $Config->name_field;
}
if ($condition == '') {
$condition = '1';
}
$sql = "SELECT {$table_id}, {$name_field}\n FROM {$table_name}\n WHERE {$condition}\n ORDER BY {$name_field}";
if (!($options = $DbConnection->getArrayPair($sql))) {
$options = array();
}
$this->setFieldProperty($field, 'type', 'select');
$this->unsetFieldInputParameter($field, 'maxlength');
if (count($options) <= 3) {
$this->setFieldProperty($field, 'type', 'radio');
}
$this->setFieldParameter($field, 'options', $options);
}