public function getPosts($f3)
{
$db = new \DB\SQL('mysql:host=' . $f3->get('db_host') . ';port=3306;dbname=' . $f3->get('db_server'), $f3->get('db_login'), $f3->get('db_password'));
$mapper = new \DB\SQL\Mapper($db, 'post_view');
if (!$f3->get('PARAMS.id')) {
$f3->set('usersPosts', $mapper->find(array(), array('order' => 'login')));
} else {
$f3->set('usersPosts', $mapper->find(array("user_id=?", $f3->get('PARAMS.id'))));
}
}
$response['status'] = 412;
$response['message'] = 'Invalid parameters provided';
echo json_encode($response);
return;
}
$db = db();
$users = new DB\SQL\Mapper($db, 'users');
$user = $users->findone(array('user_name = ?', $username));
if (empty($user)) {
$response['status'] = 412;
$response['message'] = 'Username doesnt exists';
echo json_encode($response);
return;
}
$chats = new DB\SQL\Mapper($db, 'chat');
$chat = $chats->find(["to_user = ? and status = 'recieved'", $username]);
$messages = [];
for ($i = 0; $i < count($chat); $i++) {
$from = $users->findone(['user_name = ?', $chat[$i]['from_user']]);
if (!empty($from)) {
$fromData = $from->cast();
unset($fromData['password']);
$messages[] = ['from' => $fromData, 'id' => $chat[$i]->id, 'message' => $chat[$i]->message, 'seen' => $chat[$i]->seen];
$chat[$i]->status = 'sent';
$chat[$i]->seen = date("Y-m-d H:i:s");
$chat[$i]->save();
}
}
$response['status'] = 200;
$response['message'] = 'Successfully recieved messages';
$response['chats'] = $messages;
/**
* Устанавливает массив с данными для генерации AJAX-полей
*/
protected function setAJAXFields()
{
$mapper = new \DB\SQL\Mapper($this->db, '_group');
$groups = [];
foreach ($mapper->find() as $g) {
$groups[$g->id] = $g->name;
}
$ajax_fields = array(array('name' => 'ref', 'type' => 'hidden', 'value' => $this->user_id), array('name' => 'group_id', 'table' => 'user', 'type' => 'select_list', 'placeHolder' => $this->fw->get('i18n.user.group.placeholder'), 'label' => $this->fw->get('i18n.user.group.label'), 'options' => $groups), array('name' => 'email', 'type' => 'text', 'placeHolder' => $this->fw->get('i18n.user.email.placeholder'), 'label' => $this->fw->get('i18n.user.email.label')));
$additional_fields = array();
$pofileMeta = \models\meta\Meta::getInstance()->getTableMeta('_user_profile');
foreach ($pofileMeta->getAdditionalFields() as $v) {
$type = $v['input_type'];
$additional_fields[] = array('name' => 'af_' . $v['name'], 'table' => 'content/_user_profile', 'type' => $type, 'placeHolder' => $v['comment'], 'label' => $v['title']);
}
$this->fw->set('ajax_fields', array_merge($ajax_fields, $additional_fields));
$this->setAjaxFieldsPost();
}
private function runTestSuite($db)
{
$schema = new \DB\SQL\Schema($db);
$schema->dropTable($this->tname);
// create table
$table = $schema->createTable($this->tname);
$table = $table->build();
$result = $schema->getTables();
$this->test->expect(in_array($this->tname, $result), $this->getTestDesc('create default table'));
unset($result);
$this->test->expect($table instanceof \DB\SQL\TableModifier, $this->getTestDesc('$table->build() returns TableModifier'));
// drop table
$table->drop();
$this->test->expect(in_array($this->tname, $schema->getTables()) == false, $this->getTestDesc('drop table'));
unset($table);
// create table with columns
$table = $schema->createTable($this->tname);
$table->addColumn('title')->type($schema::DT_VARCHAR128);
$table->addColumn('number')->type($schema::DT_INT4);
$table = $table->build();
$r1 = $schema->getTables();
$r2 = $table->getCols();
$this->test->expect(in_array($this->tname, $r1) && in_array('id', $r2) && in_array('title', $r2) && in_array('number', $r2), $this->getTestDesc('create new table with additional columns'));
unset($r1, $r2);
// testing all datatypes
foreach (array_keys($schema->dataTypes) as $index => $field) {
// testing column type
$table->addColumn('column_' . $index)->type($field);
$table->build();
$r1 = $table->getCols();
$this->test->expect(in_array('column_' . $index, $r1), $this->getTestDesc('adding column [' . $field . '], nullable'));
}
unset($r1);
// adding some testing data
$mapper = new \DB\SQL\Mapper($db, $this->tname);
$mapper->column_7 = 'hello world';
$mapper->save();
$mapper->reset();
$result = $mapper->findone(array('column_7 = ?', 'hello world'))->cast();
unset($mapper);
$this->test->expect($result['column_7'] == 'hello world', $this->getTestDesc('mapping dummy data'));
// default value text, not nullable
$table->addColumn('text_default_not_null')->type($schema::DT_VARCHAR128)->nullable(false)->defaults('foo bar');
$table->build();
$r1 = $table->getCols(true);
$this->test->expect(in_array('text_default_not_null', array_keys($r1)) && $r1['text_default_not_null']['default'] == 'foo bar' && $r1['text_default_not_null']['nullable'] == false, $this->getTestDesc('adding column [VARCHAR128], not nullable with default value'));
unset($r1);
// some testing dummy data
$mapper = new \DB\SQL\Mapper($db, $this->tname);
$mapper->column_7 = 'tanduay';
$mapper->save();
$mapper->reset();
$result = $mapper->findone(array('column_7 = ?', 'tanduay'))->cast();
$this->test->expect($result['column_7'] == 'tanduay' && $result['text_default_not_null'] == 'foo bar', $this->getTestDesc('mapping dummy data'));
unset($mapper, $result);
// default value numeric, not nullable
$table->addColumn('int_default_not_null')->type($schema::DT_INT4)->nullable(false)->defaults(123);
$table->build();
$r1 = $table->getCols(true);
$this->test->expect(in_array('int_default_not_null', array_keys($r1)) && $r1['int_default_not_null']['default'] == 123 && $r1['int_default_not_null']['nullable'] == false, $this->getTestDesc('adding column [INT4], not nullable with default value'));
unset($r1);
// adding testing data
$mapper = new \DB\SQL\Mapper($db, $this->tname);
$mapper->column_7 = 'test3';
$mapper->save();
$mapper->reset();
$r1 = $mapper->findone(array('column_7 = ?', 'test3'))->cast();
$this->test->expect($r1['column_7'] == 'test3' && $r1['int_default_not_null'] == 123, $this->getTestDesc('mapping dummy data'));
unset($mapper, $r1);
// default value text, nullable
$table->addColumn('text_default_nullable')->type($schema::DT_VARCHAR128)->defaults('foo bar');
$table->build();
$r1 = $table->getCols(true);
$this->test->expect(in_array('text_default_nullable', array_keys($r1)) && $r1['text_default_nullable']['default'] == 'foo bar', $this->getTestDesc('adding column [VARCHAR128], nullable with default value'));
unset($r1);
// adding some dummy data
$mapper = new \DB\SQL\Mapper($db, $this->tname);
$mapper->column_7 = 'test4';
$mapper->save();
$mapper->reset();
$mapper->column_7 = 'test5';
$mapper->text_default_nullable = null;
$mapper->save();
$mapper->reset();
$result = $mapper->find(array('column_7 = ? OR column_7 = ?', 'test4', 'test5'));
foreach ($result as &$r) {
$r = $r->cast();
}
$this->test->expect(array_key_exists(0, $result) && array_key_exists(1, $result) && $result[0]['column_7'] == 'test4' && $result[0]['text_default_nullable'] == 'foo bar' && $result[1]['column_7'] == 'test5' && $result[1]['text_default_nullable'] === null, $this->getTestDesc('mapping dummy data'));
unset($mapper, $result);
// default value numeric, nullable
$table->addColumn('int_default_nullable')->type($schema::DT_INT4)->defaults(123);
$table->build();
$r1 = $table->getCols(true);
$this->test->expect(in_array('int_default_nullable', array_keys($r1)) == true && $r1['int_default_nullable']['default'] == 123, $this->getTestDesc('adding column [INT4], nullable with default value'));
unset($r1);
// adding dummy data
$mapper = new \DB\SQL\Mapper($db, $this->tname);
$mapper->column_7 = 'test6';
$mapper->save();
$mapper->reset();
$mapper->column_7 = 'test7';
$mapper->int_default_nullable = null;
$mapper->save();
$mapper->reset();
$result = $mapper->find(array('column_7 = ? OR column_7 = ?', 'test6', 'test7'));
foreach ($result as &$r) {
$r = $r->cast();
}
$this->test->expect(array_key_exists(0, $result) && array_key_exists(1, $result) && $result[0]['column_7'] == 'test6' && $result[0]['int_default_nullable'] === 123 && $result[1]['column_7'] == 'test7' && $result[1]['int_default_nullable'] === null, $this->getTestDesc('mapping dummy data'));
unset($mapper, $result);
// current timestamp
$table->addColumn('stamp')->type($schema::DT_TIMESTAMP)->nullable(false)->defaults($schema::DF_CURRENT_TIMESTAMP);
$table->build();
$r1 = $table->getCols(true);
$this->test->expect(in_array('stamp', array_keys($r1)) && $r1['stamp']['default'] == $schema::DF_CURRENT_TIMESTAMP, $this->getTestDesc('adding column [TIMESTAMP], not nullable with current_timestamp default value'));
unset($r1);
// datetime nullable
$table->addColumn('datetime')->type_datetime()->nullable(true);
$table->build();
$r1 = $table->getCols(true);
$this->test->expect(in_array('datetime', array_keys($r1)) && $r1['datetime']['nullable'] == true, $this->getTestDesc('adding column [DATETIME], nullable, no default'));
unset($r1);
// adding dummy data
$mapper = new \DB\SQL\Mapper($db, $this->tname);
$mapper->column_7 = 'test_datetime';
$mapper->datetime = NULL;
$mapper->save();
$mapper->reset();
$result = $mapper->find(array('column_7 = ?', 'test_datetime'));
foreach ($result as &$r) {
$r = $r->cast();
}
$this->test->expect(array_key_exists(0, $result) && $result[0]['column_7'] == 'test_datetime' && $result[0]['datetime'] === null, $this->getTestDesc('mapping dummy data'));
unset($mapper, $result);
// rename column
$table->renameColumn('text_default_not_null', 'title123');
$table->build();
$r1 = $table->getCols();
$this->test->expect(in_array('title123', $r1) && !in_array('text_default_not_null', $r1), $this->getTestDesc('renaming column'));
unset($r1);
// adding dummy data
$mapper = new \DB\SQL\Mapper($db, $this->tname);
$mapper->title123 = 'test8';
$mapper->save();
$mapper->reset();
$result = $mapper->findone(array('title123 = ?', 'test8'));
$this->test->expect(!$result->dry(), $this->getTestDesc('mapping dummy data'));
$table->renameColumn('title123', 'text_default_not_null');
$table->build();
unset($result, $mapper);
// remove column
$table->dropColumn('column_1');
$table->build();
$r1 = $table->getCols();
$this->test->expect(!in_array('column_1', $r1), $this->getTestDesc('removing column'));
unset($r1);
// rename table
$schema->dropTable('test123');
$table->rename('test123');
$result = $schema->getTables();
$this->test->expect(in_array('test123', $result) && !in_array($this->tname, $result), $this->getTestDesc('renaming table'));
$table->rename($this->tname);
unset($result);
// check record count
$mapper = new \DB\SQL\Mapper($db, $this->tname);
$this->test->expect(count($mapper->find()) == 9, $this->getTestDesc('check record count'));
unset($mapper);
// adding composite primary keys
$table->addColumn('version')->type($schema::DT_INT4)->nullable(false)->defaults(1);
$table->primary(array('id', 'version'));
$table->build();
$r1 = $table->getCols(true);
$this->test->expect(!empty($r1) && isset($r1['version']) && $r1['id']['pkey'] == true && $r1['version']['pkey'] == true, $this->getTestDesc('adding composite primary-keys'));
unset($r1);
// check record count
$mapper = new \DB\SQL\Mapper($db, $this->tname);
$this->test->expect(count($mapper->find()) == 9, $this->getTestDesc('check record count'));
unset($mapper);
// drop table
$schema->dropTable($this->tname);
$this->test->expect(!in_array($this->tname, $schema->getTables()), $this->getTestDesc('drop table'));
// adding composite primary keys
$table = $schema->createTable($this->tname);
$table->addColumn('version')->type($schema::DT_INT4)->defaults(1)->nullable(false);
$table->primary(array('id', 'version'));
$table = $table->build();
$r1 = $table->getCols(true);
$this->test->expect(!empty($r1) && $r1['id']['pkey'] == true && $r1['version']['pkey'] == true, $this->getTestDesc('creating new table with composite key'));
$this->test->expect(!empty($r1) && $r1['version']['default'] == '1', $this->getTestDesc('default value on composite primary key'));
unset($r1);
// more fields to composite primary key table
$table->addColumn('title')->type($schema::DT_VARCHAR256);
$table->addColumn('title2')->type($schema::DT_TEXT);
$table->addColumn('title_notnull')->type($schema::DT_VARCHAR128)->nullable(false)->defaults("foo");
$table->build();
$r1 = $table->getCols(true);
$this->test->expect(array_key_exists('title', $r1) && array_key_exists('title_notnull', $r1) && $r1['id']['pkey'] == true && $r1['version']['pkey'] == true, $this->getTestDesc('adding more fields to composite pk table'));
unset($r1);
// testing primary keys with inserted data
$mapper = new \DB\SQL\Mapper($db, $this->tname);
$mapper->title = 'test1';
$mapper->save();
$mapper->reset();
$mapper->id = 1;
$mapper->title = 'nullable';
$mapper->version = 2;
$mapper->save();
$mapper->reset();
$mapper->title = 'test3';
$mapper->title2 = 'foobar';
$mapper->title_notnull = 'bar';
$mapper->save();
$result = array_map(array($mapper, 'cast'), $mapper->find());
$cpk_expected = array(0 => array('id' => 1, 'version' => 1, 'title' => 'test1', 'title2' => NULL, 'title_notnull' => 'foo'), 1 => array('id' => 1, 'version' => 2, 'title' => 'nullable', 'title2' => NULL, 'title_notnull' => 'foo'), 2 => array('id' => 2, 'version' => 1, 'title' => 'test3', 'title2' => 'foobar', 'title_notnull' => 'bar'));
foreach ($result as &$r) {
ksort($r);
}
foreach ($cpk_expected as &$r) {
ksort($r);
}
$this->test->expect(json_encode($result) == json_encode($cpk_expected), $this->getTestDesc('adding items with composite primary-keys'));
$schema->dropTable($this->tname);
// indexes
$table = $schema->createTable($this->tname);
$table->addColumn('rawtest', array('type' => $schema::DT_VARCHAR256, 'default' => 'foo'));
$table->addColumn('text')->type($schema::DT_TEXT);
$table->addColumn('foo')->type($schema::DT_VARCHAR128)->index();
$table = $table->build();
$r1 = $table->getCols(true);
$this->test->expect(isset($r1['rawtest']) && ($r1['rawtest']['default'] = 'foo'), $this->getTestDesc('adding column with options array'));
$indexes = $table->listIndex();
$this->test->expect(isset($indexes[$table->name . '___foo']), $this->getTestDesc('column index on table creation'));
$table->addColumn('bar')->type($schema::DT_VARCHAR128)->index(true);
$table->addColumn('baz')->type($schema::DT_VARCHAR128);
$table->addIndex(array('foo', 'baz'));
$table->build();
$indexes = $table->listIndex();
$this->test->expect(isset($indexes[$table->name . '___bar']), $this->getTestDesc('column index on table alteration'));
$this->test->expect(isset($indexes[$table->name . '___bar']) && $indexes[$table->name . '___bar']['unique'] == true, $this->getTestDesc('unique index'));
$this->test->expect(isset($indexes[$table->name . '___foo__baz']), $this->getTestDesc('index on combined columns'));
if ($this->current_engine == 'sqlite') {
$table->dropColumn('rawtest');
$table->build();
$indexes = $table->listIndex();
$this->test->expect(isset($indexes[$table->name . '___foo__baz']) && isset($indexes[$table->name . '___bar']) && $indexes[$table->name . '___bar']['unique'], $this->getTestDesc('preserve indexes after table rebuild'));
}
$table->dropIndex($table->name . '___bar');
$table->build();
$indexes = $table->listIndex();
$this->test->expect(!array_key_exists($table->name . '___bar', $indexes), $this->getTestDesc('drop index'));
// update column
$table->updateColumn('bar', $schema::DT_TEXT);
$table->build();
$r1 = $table->getCols(true);
$this->test->expect(array_key_exists('bar', $r1) && $r1['bar']['type'] == 'text', $this->getTestDesc('update column'));
// create table with text not nullable column
$table2 = $schema->createTable($this->tname . '_notnulltext');
$table2->addColumn('desc')->type($schema::DT_TEXT)->nullable(false);
$table2 = $table2->build();
$r1 = $schema->getTables();
$r2 = $table2->getCols(true);
$this->test->expect(in_array($this->tname . '_notnulltext', $r1) && array_key_exists('desc', $r2) && $r2['desc']['nullable'] == false, $this->getTestDesc('create new table with not nullable text column'));
$table2->drop();
}
function edit_food($f3, $params)
{
$category = new DB\SQL\Mapper($f3->get('DB'), 'CATEGORY');
$menu = new DB\SQL\Mapper($f3->get('DB'), 'MENU');
$meal = $f3->get('meal_orm');
$id = $f3->get('PARAMS.id');
$mealInst = $meal->load(array("id=?", $id));
if ($meal->dry()) {
$f3->reroute("/annos/luo");
}
$m_list = $menu->find();
$c_list = $category->find();
$a_list = Allergy::map_to_id($f3);
$f3->set("meal", $mealInst);
$f3->set("allergies", $a_list);
$f3->set("categories", $c_list);
$f3->set("menus", $m_list);
$f3->set("id", $id);
$f3->set("title", $mealInst->name);
$f3->set('content', 'food_form.htm');
echo Template::instance()->render('layout.htm');
}
public function saveStoryChanges(\DB\SQL\Mapper $current, array $post)
{
// Step one: save the plain data
$current->title = $post['story_title'];
$current->summary = str_replace("\n", "<br />", $post['story_summary']);
$current->storynotes = str_replace("\n", "<br />", $post['story_notes']);
$current->ratingid = $post['ratingid'];
$current->completed = $post['completed'];
$current->validated = $post['validated'];
$current->save();
// Step two: check for changes in relation tables
// Check tags:
$post['tags'] = explode(",", $post['tags']);
$tags = new \DB\SQL\Mapper($this->db, $this->prefix . 'stories_tags');
foreach ($tags->find(array('`sid` = ? AND `character` = ?', $current->sid, 0)) as $X) {
$temp = array_search($X['tid'], $post['tags']);
if ($temp === FALSE) {
// Excess relation, drop from table
$tags->erase(['lid=?', $X['lid']]);
} else {
unset($post['tags'][$temp]);
}
}
// Insert any tag IDs not already present
if (sizeof($post['tags']) > 0) {
foreach ($post['tags'] as $temp) {
// Add relation to table
$tags->reset();
$tags->sid = $current->sid;
$tags->tid = $temp;
$tags->character = 0;
$tags->save();
}
}
unset($tags);
// Check Characters:
$post['characters'] = explode(",", $post['characters']);
$characters = new \DB\SQL\Mapper($this->db, $this->prefix . 'stories_tags');
foreach ($characters->find(array('`sid` = ? AND `character` = ?', $current->sid, 1)) as $X) {
$temp = array_search($X['tid'], $post['characters']);
if ($temp === FALSE) {
// Excess relation, drop from table
$characters->erase(['lid=?', $X['lid']]);
} else {
unset($post['characters'][$temp]);
}
}
// Insert any character IDs not already present
if (sizeof($post['characters']) > 0) {
foreach ($post['characters'] as $temp) {
// Add relation to table
$characters->reset();
$characters->sid = $current->sid;
$characters->tid = $temp;
$characters->character = 1;
$characters->save();
}
}
unset($characters);
// Check Categories:
$post['category'] = explode(",", $post['category']);
$categories = new \DB\SQL\Mapper($this->db, $this->prefix . 'stories_categories');
foreach ($categories->find(array('`sid` = ?', $current->sid)) as $X) {
$temp = array_search($X['cid'], $post['category']);
if ($temp === FALSE) {
// Excess relation, drop from table
$categories->erase(['lid=?', $X['lid']]);
} else {
unset($post['category'][$temp]);
}
}
// Insert any character IDs not already present
if (sizeof($post['category']) > 0) {
foreach ($post['category'] as $temp) {
// Add relation to table
$categories->reset();
$categories->sid = $current->sid;
$categories->cid = $temp;
$categories->save();
}
}
unset($categories);
// Author and co-Author preparation:
$post['author'] = explode(",", $post['author']);
$post['coauthor'] = explode(",", $post['coauthor']);
// remove co-authors, that are already in the author field
$post['coauthor'] = array_diff($post['coauthor'], $post['author']);
// Check Authors:
$author = new \DB\SQL\Mapper($this->db, $this->prefix . 'stories_authors');
foreach ($author->find(array('`sid` = ? AND `ca` = ?', $current->sid, 0)) as $X) {
$temp = array_search($X['aid'], $post['author']);
if ($temp === FALSE) {
// Excess relation, drop from table
$author->erase(['lid=?', $X['lid']]);
} else {
unset($post['author'][$temp]);
}
}
// Insert any character IDs not already present
if (sizeof($post['author']) > 0) {
foreach ($post['author'] as $temp) {
// Add relation to table
$author->reset();
$author->sid = $current->sid;
$author->aid = $temp;
$author->ca = 0;
$author->save();
}
}
unset($author);
// Check co-Authors:
$coauthor = new \DB\SQL\Mapper($this->db, $this->prefix . 'stories_authors');
foreach ($coauthor->find(array('`sid` = ? AND `ca` = ?', $current->sid, 1)) as $X) {
$temp = array_search($X['aid'], $post['coauthor']);
if ($temp === FALSE) {
// Excess relation, drop from table
$coauthor->erase(['lid=?', $X['lid']]);
} else {
unset($post['coauthor'][$temp]);
}
}
// Insert any character IDs not already present
if (sizeof($post['coauthor']) > 0) {
foreach ($post['coauthor'] as $temp) {
// Add relation to table
$coauthor->reset();
$coauthor->sid = $current->sid;
$coauthor->aid = $temp;
$coauthor->ca = 1;
$coauthor->save();
}
}
unset($coauthor);
$this->rebuildStoryCache($current->sid);
return TRUE;
}
function show_all($f3)
{
$orm = new DB\SQL\Mapper($f3->get('DB'), 'INGREDIENT_OVERVIEW');
$page = $f3->get('PARAMS.page');
$record_count = $orm->count();
$result_number = 20;
$page_count = ceil($record_count / $result_number);
if (!isset($page) || $page <= 0) {
$f3->reroute("/ainesosa/listaa/sivu/1");
} else {
if ($page > $page_count) {
$f3->reroute("/ainesosa/listaa/sivu/" . $page_count);
}
}
$page--;
$result = $orm->find(array(), array('limit' => $result_number, 'offset' => $page * $result_number));
$allergy_arr = array();
foreach ($result as $ingredient) {
$id = $ingredient->id;
$string_allergies = $ingredient->allergies;
$allergy_arr[$id] = array_filter(explode(',', trim($string_allergies)));
}
$f3->set('allergies', Allergy::map_to_id($f3));
$f3->set('result', $result);
$f3->set('page_count', $page_count);
$f3->set('cur_page', $page + 1);
$f3->set('allergy_arr', $allergy_arr);
$f3->set("content", "ingredient_list.htm");
$f3->set("title", "Ainesosat");
echo Template::instance()->render("layout.htm");
}
