/**
* Tries to resolve the PK of a table.
*/
public function getPk($table)
{
$this->get();
if (!empty($this->data[$table])) {
return $this->data[$table];
} else {
$pk = null;
$db = Database::getObject();
// 1. Try to get PK info from column information
$db->query('SHOW COLUMNS FROM <p><table:noquote> WHERE `Key` = "PRI" ', compact("table"));
if ($db->numRows() > 0) {
$pk = $db->fetchOne();
}
// 2. Try to get auto_increment info from column information
if ($pk == null) {
$db->query('SHOW COLUMNS FROM <p><table:noquote> WHERE `Extra` = "auto_increment"', compact("table"));
if ($db->numRows() > 0) {
$pk = $db->fetchOne();
}
}
// 3. Use the first column as PK (important e.q. for views)
if ($pk == null) {
$db->query('SHOW COLUMNS FROM <p><table:noquote>', compact("table"));
if ($db->numRows() > 0) {
$pk = $db->fetchOne();
}
}
// Save and return
$this->data[$table] = $pk;
$this->export();
return $pk;
}
}
public function __construct()
{
$this->db = new FluentPDO(Database::getObject());
// FPDO debug mode
if ($this->debug == true) {
$this->fpdo->debug = function ($query) {
$data = array('query' => $query->getQuery(false), 'params' => implode(', ', $query->getParameters()), 'rowCount' => $query->getResult() ? $query->getResult()->rowCount() : 0);
App::debug($data);
};
}
// Entity name
if ($this->entity == null) {
$split = explode('\\', get_class($this));
$this->entity = strtolower(str_replace('Model', '', end($split)));
}
// Getting table fields name
$fields = $this->db->getPDO()->query("SHOW COLUMNS FROM " . $this->entity);
$row = $fields->fetchAll();
foreach ($row as $key => $field) {
$this->fields[$field['Field']] = '';
}
// Translation tool
if ($this->translation === null) {
$this->translation = new Translation(['bundle' => false, 'theme' => 'sybil', 'domain' => 'database']);
}
}
public function main()
{
$this->header();
$tpl = Response::getObject()->getTemplate('/Cms/admin/default');
$tpl->assign('mysql_version', Database::getObject()->version());
$tpl->output();
$this->footer();
}
protected function docs()
{
$db = Database::getObject();
$db->query("SELECT id, title, uri FROM <p>page ORDER BY title");
$tpl = Response::getObject()->appendTemplate("Cms/admin/docs");
$tpl->assign("data", $db->fetchAll());
$tpl->output();
}
public function load()
{
$this->data = array();
$db = Database::getObject();
$db->query("SELECT * FROM <p>fields ORDER BY priority");
while ($row = $db->fetchAssoc()) {
$this->data[$row['position']][] = $row;
$this->addField($row);
}
}
public function __destruct()
{
try {
Session::getObject()->update();
} catch (QueryException $e) {
Database::getObject()->getDebug()->add($e);
throw $e;
}
parent::__destruct();
}
private static function getByField($field, $data)
{
$user = null;
$db = Database::getObject();
$db->query("SELECT * FROM <p>user WHERE <field:noquote> = <data>", compact("field", "data"));
if ($db->numRows() == 1) {
$user = new User($db->fetchAssoc());
}
return $user;
}
protected function show()
{
$db = Database::getObject();
$tpl = Response::getObject()->appendTemplate("Airlines/admin/airports");
$country = Request::get('country', VAR_NONE, 'Schweiz');
$tpl->assign('country', $country);
$db->query("SELECT * FROM <p>airports " . iif(!empty($country), "WHERE land = <country>") . " ORDER BY land, stadt, flughafen", compact("country"));
$tpl->assign('data', $db->fetchAll());
$db->query("SELECT DISTINCT land FROM <p>airports ORDER BY land");
$tpl->assign('countries', $db->fetchAll(null, null, 'land'));
$tpl->output();
}
/**
* Escapces (database specific) chars in a string and removes null bytes.
*
* @param string Variable to check
* @return string Checked variable
**/
public static function saveDb($var)
{
if (is_array($var)) {
foreach ($var as $key => $value) {
$var[$key] = self::saveDb($value);
}
} else {
$var = self::removeNullByte($var);
$var = Database::getObject()->escapeString($var);
}
return $var;
}
public function login($email, $pw, $pwIsHashed = false)
{
if (!$pwIsHashed) {
$pw = Hash::generate($pw);
}
$db = Database::getObject();
$db->query("SELECT * FROM <p>user WHERE email = <email> AND pw = <pw> AND active = '1'", compact("email", "pw"));
if ($db->numRows() == 1) {
$my = $db->fetchAssoc();
$this->setCookie($email, $pw);
return new User($my);
} else {
return $this->loginAsGuest();
}
}
public function load()
{
$this->data = array('permissions' => array(), 'guest' => 0, 'titles' => array());
$db = Database::getObject();
$db->query("SELECT * FROM <p>group");
while ($row = $db->fetchAssoc()) {
$id = $row['id'];
unset($row['id']);
$this->data['titles'][$id] = $row['title'];
unset($row['title']);
if ($row['registered'] == 0) {
$this->data['guest'] = $id;
}
$this->data['permissions'][$id] = $row;
}
}
public function suggest()
{
$data = array();
$id = Request::get(1, VAR_INT);
$q = Request::get('q');
$q = SystemEnvironment::fromUtf8($q);
$db = Database::getObject();
$db->query("SELECT * FROM <p>fields WHERE id = <id:int>", compact("id"));
if ($db->numRows() == 1) {
$field = CustomField::constructObject($db->fetchAssoc());
if ($field instanceof CustomAutoCompleteTextField) {
$data = $field->getList($q);
}
}
Response::getObject()->sendHeader('Content-Type: text/plain; charset=' . Config::get('intl.charset'));
echo implode("\n", $data);
}
private function custom_pages()
{
$uri = Request::get(0, VAR_URI);
$db = Database::getObject();
$db->query("SELECT title, content FROM <p>page WHERE uri = <uri>", compact("uri"));
if ($db->numRows() != 1) {
$this->header();
$this->notFoundError();
$this->footer();
} else {
$data = $db->fetchAssoc();
$this->breadcrumb->add($data['title']);
$this->header();
echo $this->parse($data['content']);
$this->footer();
}
}
public function update()
{
$data = array('time' => time(), 'settings' => serialize($this->settings), 'sid' => $this->sid, 'uid' => $this->me->getId());
Database::getObject()->query("UPDATE <p>session SET visit = <time:int>, settings = <settings>, user_id = <uid:int> WHERE sid = <sid>", $data);
if ($data['uid'] > 0) {
Database::getObject()->query("UPDATE <p>user SET lastvisit = <time:int> WHERE id = <uid:int>", $data);
}
}
public static function checkPW($pw)
{
$db = Database::getObject();
$data = array('id' => Me::get()->getId(), 'pw' => Hash::generate($pw));
$db->query("SELECT id FROM <p>user WHERE id = <id:int> AND pw = <pw> AND active = '1' LIMIT 1", $data);
return $db->numRows() == 1;
}
public function add()
{
if ($this->edit(0)) {
$id = Database::getObject()->insertId();
return iif($id > 0, $id, 0);
} else {
return 0;
}
}
public static function getAverageFields(CustomDataPosition $pos, array $params = array())
{
$filter = new CustomDataFilter($pos);
$filter->field(null);
$fields = $pos->getFieldsForClassPath(self::$classPath);
foreach ($fields as $field) {
$fieldName = Sanitize::saveDb($field->getFieldName());
$filter->fieldCalculation($fieldName, "AVG({$fieldName})");
}
foreach ($params as $field => $value) {
$filter->condition($field, $value);
}
$result = $filter->execute();
if ($result) {
$data = new CustomData($pos);
$row = Database::getObject()->fetchAssoc($result);
if ($row) {
$data = new CustomData($pos);
$data->set($row, true, $fields);
return $data->getFields(array_keys($fields));
}
}
return array();
}
public function edit()
{
$id = Request::get(1, VAR_INT);
$isSent = Request::get(2, VAR_URI) == 'send';
$this->breadcrumb->add('Bearbeiten');
$this->header();
$db = Database::getObject();
$db->query("SELECT * FROM <p>fields WHERE id = <id:int>", compact("id"));
if ($db->numRows() == 0) {
CmsPage::error('Das Feld wurde leider nicht gefunden.');
$this->overview();
} else {
$field = CustomField::constructObject($db->fetchAssoc());
$_positions = $this->getPositions();
$positions = Core::constructObjectArray($_positions);
// Fill data array with the default (currently saved) data
$permissions = $field->getPermissions();
$data = array('name' => $field->getName(), 'description' => $field->getDescription(), 'priority' => $field->getPriority(), 'position' => $field->getPosition()->getClassPath(), 'type' => $field->getClassPath(), 'read' => $permissions['read'], 'write' => $permissions['write']);
foreach ($field->getParamsData() as $key => $value) {
$data[$key] = $value;
}
$error = array();
if ($isSent) {
// Base options for every field
$options = array_merge($this->getValidator(), array('position' => array(Validator::MESSAGE => 'Der Anzeigeort ist ungültig.', Validator::LIST_CS => $_positions)), $field->getValidationParams(false));
extract(Validator::checkRequest($options));
if (count($error) == 0) {
$this->injectDataToField($field, $data);
if ($field->update()) {
CmsPage::ok("Das Feld wurde erfolgreich aktualisiert.");
} else {
$error[] = 'Das Feld konnt leider nicht aktualisiert werden.';
}
}
if (count($error) > 0) {
CmsPage::error($error);
}
}
$tpl = Response::getObject()->appendTemplate("/Cms/admin/fields_edit");
$tpl->assign('field', $field, false);
$tpl->assign('positions', $positions, false);
$tpl->assign('data', $data);
$tpl->assign('baseUri', $this->getBaseURI());
$tpl->output();
}
$this->footer();
}
public function formatDataForDb($value)
{
if ($value !== null && $this->usePk()) {
list($table, $column) = explode('.', $this->params['source']);
$pkName = $this->getPkName();
$db = Database::getObject();
$db->query("SELECT <pkName:noquote> FROM <p><table:noquote> WHERE <column:noquote> = <value> LIMIT 1", compact("table", "column", "value", "pkName"));
if ($db->numRows() == 1) {
$value = $db->fetchOne();
}
}
return $value;
}
public function execute()
{
$vars = array('table' => $this->position->getDbTable());
$fields = $this->buildFields($vars);
$where = '';
if ($this->conditions != null) {
$where = 'WHERE ' . $this->buildWhere($this->conditions, $vars);
}
$join = '';
if (count($this->join) > 0) {
$join = $this->buildJoins($vars);
}
$order = '';
if (count($this->order) > 0) {
$order = 'ORDER BY ' . $this->buildOrder($vars);
}
$group = '';
if (count($this->group) > 0) {
$group = 'GROUP BY ' . $this->buildGroup($vars);
}
$limit = $this->buildLimit($vars);
return Database::getObject()->query("SELECT {$fields} FROM <p><table:noquote> {$join} {$where} {$group} {$order} {$limit}", $vars);
}
protected function members()
{
$db = Database::getObject();
$db->query("SELECT COUNT(*) FROM <p>user");
$pp = Config::get('pagination.admin');
$pg = new Pagination($pp, $db->fetchOne());
$pg->parsePage();
$pg->setUri(Uri::build('/Cms/admin/members'));
$offset = $pg->getOffset();
$db->query("SELECT * FROM <p>user ORDER BY surname, forename LIMIT <offset:int>, <pp:int>", compact("offset", "pp"));
$data = array();
while ($row = $db->fetchAssoc()) {
$row['group'] = UserUtils::getGroupName($row['group_id']);
$data[] = $row;
}
$tpl = Response::getObject()->appendTemplate("Cms/admin/members");
$tpl->assign("pages", $pg->build(), false);
$tpl->assign("data", $data);
$tpl->output();
}
// Load the class manager for autoload support
Core::loadClass('Core.Kernel.ClassManager');
// Register autoloader
spl_autoload_register('ClassManager::autoload');
// Store temporary entries (Registry like) - Namespace: temp
Config::setConfigHandler(new TempConfig(), 'temp');
// Load/Write entries from a native php array in file data/config.php - Namespace: base
Config::setConfigHandler(new PHPConfig(VISCACHA_CONFIG_FILE), 'base');
// Load/Write entries from a database table named config - Namespace: core
// Config::setConfigHandler(new DBConfig('config'), 'core');
// set the script start and cwd to temp config
Config::set('temp.benchmark.start', $scriptStart);
Config::set('temp.system.cwd', getcwd()); // see FileSystem::resetWorkdingDir() for more information
// Set up database connection
if (Config::get('base.database.enabled') == true) {
$db = Database::getObject(Config::get('base.database.driver'));
$db->connect(
Config::get('base.database.username'),
Config::get('base.database.password'),
Config::get('base.database.host'),
Config::get('base.database.port'),
Config::get('base.database.socket')
);
Core::storeObject($db, 'DB');
}
?>
public function remove()
{
$db = Database::getObject();
try {
$db->begin();
// Löschen in Felder-Tabelle
$db->query("DELETE FROM <p>fields WHERE id = <id:int>", array('id' => $this->id));
if ($this->getDbDataType() != null) {
// Spalte löschen aus Daten-Tabelle
$alter = array('table' => $this->position->getDbTable(), 'field' => $this->getFieldName());
$db->query("ALTER TABLE <p><table:noquote> DROP <field:noquote>", $alter);
// Default value?
}
$db->commit();
$this->invalidateCache();
return true;
} catch (QueryException $e) {
$db->rollback();
return false;
}
}
