public static function getUserExternalSystemIds()
{
$columnName = ExternalSystemIdUtil::EXTERNAL_SYSTEM_ID_COLUMN_NAME;
RedBeanColumnTypeOptimizer::externalIdColumn(User::getTableName('User'), $columnName);
$sql = 'select ' . $columnName . ' from ' . User::getTableName('User');
return R::getCol($sql);
}
protected static function generateCampaignItems($campaign, $pageSize)
{
if ($pageSize == null) {
$pageSize = self::DEFAULT_CAMPAIGNITEMS_TO_CREATE_PAGE_SIZE;
}
$contacts = array();
$quote = DatabaseCompatibilityUtil::getQuote();
$marketingListMemberTableName = RedBeanModel::getTableName('MarketingListMember');
$campaignItemTableName = RedBeanModel::getTableName('CampaignItem');
$sql = "select {$quote}{$marketingListMemberTableName}{$quote}.{$quote}contact_id{$quote} from {$quote}{$marketingListMemberTableName}{$quote}";
// Not Coding Standard
$sql .= "left join {$quote}{$campaignItemTableName}{$quote} on ";
$sql .= "{$quote}{$campaignItemTableName}{$quote}.{$quote}contact_id{$quote} ";
$sql .= "= {$quote}{$marketingListMemberTableName}{$quote}.{$quote}contact_id{$quote}";
$sql .= "AND {$quote}{$campaignItemTableName}{$quote}.{$quote}campaign_id{$quote} = " . $campaign->id . " ";
$sql .= "where {$quote}{$marketingListMemberTableName}{$quote}.{$quote}marketinglist_id{$quote} = " . $campaign->marketingList->id;
$sql .= " and {$quote}{$campaignItemTableName}{$quote}.{$quote}id{$quote} is null limit " . $pageSize;
$ids = R::getCol($sql);
foreach ($ids as $contactId) {
$contacts[] = Contact::getById((int) $contactId);
}
if (!empty($contacts)) {
//todo: if the return value is false, then we might need to catch that since it didn't go well.
CampaignItem::registerCampaignItemsByCampaign($campaign, $contacts);
if (count($ids) < $pageSize) {
return true;
}
} else {
return true;
}
return false;
}
function index()
{
$view = new G2_TwigView('pages/index');
$test_a = R::findOne('audit');
$args = [];
if (!empty($_GET)) {
$wheres = [];
foreach ($_GET as $field => $value) {
if (isset($test_a->{$field}) && trim($value)) {
$wheres[] = "{$field} LIKE :{$field}";
$args[$field] = $value;
}
}
$where = implode(' AND ', $wheres);
} else {
$where = '';
}
$audits = Audit::deserialize(Mvc_Db::paginate_findAll('audit', 10, "{$where} ORDER BY id DESC", $args));
// Sort into entities
$sorted = [];
foreach ($audits as $audit) {
// Limit to 10 entries per entity
if (count($sorted[$audit->entity]) <= 10) {
$sorted[$audit->entity][] = $audit;
}
}
$view->set('sorted', $sorted);
$view->entities = R::getCol('SELECT DISTINCT entity FROM audit');
$view->set_entity = $_GET['entity'];
$view->page_count = Mvc_Db::get_last_total_pages();
$view->current = Mvc_Db::get_current_page();
unset($_GET['p']);
$view->current_url = PACKAGE_URL . '?' . http_build_query($_GET);
$view->render();
}
public static function getKeys(RedBean_OODBBean $bean, $typeName)
{
$fieldName = self::getLinkField($typeName);
$id = (int) $bean->{$fieldName};
$ids = R::getCol("select id from {$typeName} where " . $bean->getMeta("type") . "_id" . " = {$bean->id}");
return $ids;
}
/**
* Tests the various ways to fetch (select queries)
* data using adapter methods in the facade.
* Also tests the new R::getAssocRow() method,
* as requested in issue #324.
*/
public function testFetchTypes()
{
R::nuke();
$page = R::dispense('page');
$page->a = 'a';
$page->b = 'b';
R::store($page);
$page = R::dispense('page');
$page->a = 'c';
$page->b = 'd';
R::store($page);
$expect = '[{"id":"1","a":"a","b":"b"},{"id":"2","a":"c","b":"d"}]';
asrt(json_encode(R::getAll('SELECT * FROM page')), $expect);
$expect = '{"1":"a","2":"c"}';
asrt(json_encode(R::getAssoc('SELECT id, a FROM page')), $expect);
asrt(json_encode(R::getAssoc('SELECT id, a, b FROM page')), $expect);
$expect = '[{"id":"1","a":"a"},{"id":"2","a":"c"}]';
asrt(json_encode(R::getAssocRow('SELECT id, a FROM page')), $expect);
$expect = '[{"id":"1","a":"a","b":"b"},{"id":"2","a":"c","b":"d"}]';
asrt(json_encode(R::getAssocRow('SELECT id, a, b FROM page')), $expect);
$expect = '{"id":"1","a":"a","b":"b"}';
asrt(json_encode(R::getRow('SELECT * FROM page WHERE id = 1')), $expect);
$expect = '"a"';
asrt(json_encode(R::getCell('SELECT a FROM page WHERE id = 1')), $expect);
$expect = '"b"';
asrt(json_encode(R::getCell('SELECT b FROM page WHERE id = 1')), $expect);
$expect = '"c"';
asrt(json_encode(R::getCell('SELECT a FROM page WHERE id = 2')), $expect);
$expect = '["a","c"]';
asrt(json_encode(R::getCol('SELECT a FROM page')), $expect);
$expect = '["b","d"]';
asrt(json_encode(R::getCol('SELECT b FROM page')), $expect);
}
public static function getKeys(RedBean_OODBBean $bean, $typeName, $name = null)
{
$fieldName = self::getLinkField($typeName, $name);
$id = (int) $bean->{$fieldName};
$columnPrefix = self::resolveColumnPrefix($name);
$columnName = $columnPrefix . $bean->getMeta("type") . '_id';
$ids = R::getCol("select id from {$typeName} where " . $columnName . " = {$bean->id}");
return $ids;
}
public static function getTailDistinctEventsByEventName($eventName, User $user, $count)
{
assert('is_string($eventName)');
assert('is_int($count)');
$sql = "select id\n from ( select id, modelclassname, modelid, datetime from auditevent where _user_id = {$user->id}\n AND eventname = '{$eventName}' order by id desc ) auditevent\n group by concat(modelclassname, modelid) order by datetime desc limit {$count}";
$ids = R::getCol($sql);
$beans = R::batch('auditevent', $ids);
return self::makeModels($beans, __CLASS__);
}
/**
* Returns an array of table names from the database.
*/
public static function getAllTableNames()
{
assert('RedBeanDatabase::isSetup()');
if (RedBeanDatabase::getDatabaseType() == 'sqlite') {
return R::getCol('select name from sqlite_master where type = \'table\' order by name;');
} elseif (RedBeanDatabase::getDatabaseType() == 'pgsql') {
return R::getCol("\n select relname from pg_catalog.pg_class\n left join pg_catalog.pg_namespace n on n.oid = pg_catalog.pg_class.relnamespace\n where pg_catalog.pg_class.relkind in ('r', '') and\n n.nspname <> 'pg_catalog' and\n n.nspname <> 'information_schema' and\n n.nspname !~ '^pg_toast' and\n pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid)\n order by lower(relname);\n ");
} else {
return R::getCol('show tables;');
}
}
public static function getByLayoutIdAndUser($layoutId, $user)
{
assert('is_integer($layoutId) && $layoutId >= 1');
assert('$user instanceof User && $user->id > 0');
$sql = 'select dashboard.id id ' . 'from dashboard, ownedsecurableitem ' . 'where ownedsecurableitem.owner__user_id = ' . $user->id . ' and dashboard.ownedsecurableitem_id = ownedsecurableitem.id ' . ' and layoutid = ' . $layoutId . ' order by layoutId;';
$ids = R::getCol($sql);
assert('count($ids) <= 1');
if (count($ids) == 0) {
if ($layoutId == Dashboard::DEFAULT_USER_LAYOUT_ID) {
return Dashboard::setDefaultDashboardForUser($user);
}
throw new NotFoundException();
}
$bean = R::load(RedBeanModel::getTableName('Dashboard'), $ids[0]);
assert('$bean === false || $bean instanceof RedBean_OODBBean');
if ($bean === false) {
throw new NotFoundException();
}
return self::makeModel($bean);
}
protected static function generateCampaignItems($campaign, $pageSize)
{
if ($pageSize == null) {
$pageSize = self::DEFAULT_CAMPAIGNITEMS_TO_CREATE_PAGE_SIZE;
}
$contacts = array();
$quote = DatabaseCompatibilityUtil::getQuote();
$sql = "select {$quote}marketinglistmember{$quote}.{$quote}contact_id{$quote} from {$quote}marketinglistmember{$quote}\n left join {$quote}campaignitem{$quote} on {$quote}campaignitem{$quote}.{$quote}contact_id{$quote} " . "= {$quote}marketinglistmember{$quote}.{$quote}contact_id{$quote} " . "AND {$quote}campaignitem{$quote}.{$quote}campaign_id{$quote} = " . $campaign->id . " where {$quote}marketinglistmember{$quote}.{$quote}marketinglist_id{$quote} = " . $campaign->marketingList->id . " and {$quote}campaignitem{$quote}.{$quote}id{$quote} IS NULL limit " . $pageSize;
$ids = R::getCol($sql);
foreach ($ids as $contactId) {
$contacts[] = Contact::getById((int) $contactId);
}
if (!empty($contacts)) {
//todo: if the return value is false, then we might need to catch that since it didn't go well.
CampaignItem::registerCampaignItemsByCampaign($campaign, $contacts);
if (count($ids) < $pageSize) {
return true;
}
} else {
return true;
}
}
/**
* Given a table name, count, and offset get an array of beans.
* @param string $tableName
* @param integer $count
* @param integer $offset
* @return array of RedBean_OODBBean beans.
*/
public static function getSubset($tableName, $where = null, $count = null, $offset = null)
{
assert('is_string($tableName)');
assert('$offset === null || is_integer($offset) && $offset >= 0');
assert('$offset === null || is_integer($count) && $count >= 1');
$sql = 'select id from ' . $tableName;
if ($where != null) {
$sql .= ' where ' . $where;
}
if ($count !== null) {
$sql .= " limit {$count}";
}
if ($offset !== null) {
$sql .= " offset {$offset}";
}
$ids = R::getCol($sql);
return R::batch($tableName, $ids);
}
/**
* Returns an array containing the index names of all
* indexes on the specified table name.
*
* @param $tableNoQ table name without quotes or backticks
*
* @return array
*/
function getIndexes($tableNoQ)
{
$writer = R::getWriter();
if ($writer instanceof \RedBeanPHP\QueryWriter\MySQL || $writer instanceof \RedBeanPHP\QueryWriter\CUBRID) {
$indexes = array();
$list = R::getAll("SHOW INDEX FROM `{$tableNoQ}`");
foreach ($list as $listItem) {
$indexes[] = $listItem['Key_name'];
}
return $indexes;
}
if ($writer instanceof \RedBeanPHP\QueryWriter\SQLiteT) {
$indexes = array();
$list = R::getAll(" pragma index_list(`{$tableNoQ}`) ");
foreach ($list as $listItem) {
$indexes[] = $listItem['name'];
}
return $indexes;
}
if ($writer instanceof \RedBeanPHP\QueryWriter\PostgreSQL) {
return R::getCol(" SELECT indexname FROM pg_indexes WHERE tablename = '{$tableNoQ}' AND schemaname = 'public' ");
}
return array();
}
/**
* Conta as linhas totais do SELECT
* @return int
*/
private function countLines()
{
$sqlCount = "SELECT COUNT(*) AS QNT FROM ({$this->sql}) AS TABLE";
return (int) \R::getCol($sqlCount);
}
// broadcast_action.php
include "config.php";
$target_usertype = null;
$message = null;
if (isset($_POST["target_usertype"])) {
$target_usertype = $_POST["target_usertype"];
}
if (isset($_POST["message"])) {
$message = $_POST["message"];
}
if ($message == null || $message == "") {
store_in_session("message", "You can't send an empty message");
header("Location: ./broadcast.php");
} else {
$data = R::getCol("select max(thread_id)+1 as new_thread_id from messages");
$thread_id = $data[0];
if (is_null($thread_id)) {
$thread_id = 1;
}
$sql = "insert into messages (from_id, to_id, message_sent_datetime, message_text, thread_id) values(0, ?,?,?,?)";
$param = 1;
$sql1 = "select id from users where 1=?";
if ($target_usertype != -1) {
$sql1 = "select id from users where usertype=?";
$param = $target_usertype;
}
$data = R::getAll($sql1, array($param));
foreach ($data as $value) {
R::exec($sql, array($value["id"], date("Y-m-d h:i:s"), $message, $thread_id++));
}
/**
* Gets a range of model ids from the database of the named model type.
* @param $modelClassName
* @param $joinTablesAdapter null or instance of joinTablesAdapter.
* @param $offset The zero based index of the first model to be returned.
* @param $count The number of models to be returned.
* @param $where
* @param $orderBy - sql string. Example 'a desc' or 'a.b desc'. Currently only supports non-related attributes
* @param $modelClassName Pass only when getting it at runtime gets the wrong name.
* @return An array of models ids
*/
public static function getSubsetIds(RedBeanModelJoinTablesQueryAdapter $joinTablesAdapter = null, $offset = null, $count = null, $where = null, $orderBy = null, $modelClassName = null, $selectDistinct = false)
{
assert('$offset === null || is_integer($offset) && $offset >= 0');
assert('$count === null || is_integer($count) && $count >= 1');
assert('$where === null || is_string ($where) && $where != ""');
assert('$orderBy === null || is_string ($orderBy) && $orderBy != ""');
assert('$modelClassName === null || is_string($modelClassName) && $modelClassName != ""');
if ($modelClassName === null) {
$modelClassName = get_called_class();
}
if ($joinTablesAdapter == null) {
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter($modelClassName);
}
$tableName = self::getTableName($modelClassName);
$sql = static::makeSubsetOrCountSqlQuery($tableName, $joinTablesAdapter, $offset, $count, $where, $orderBy, false, $selectDistinct);
$ids = R::getCol($sql);
return $ids;
}
<?php
/*
Suggests matches between domains and accounts
TODO: fix f.eks. co.uk domæner. Vi antager at alt efter sidste . er tld.
*/
$config = (require 'config.php');
require 'includes/redbean/rb.php';
$dsn = 'mysql:host=' . $config->dbHost . ';dbname=' . $config->dbName;
R::setup($dsn, $config->dbUsername, $config->dbPassword);
$domains = R::getCol('select name from domain');
require 'includes/classes/class.sugarCrmConnector.php';
try {
$sugar = sugarCrmConnector::getInstance();
$sugar->connect($config->sugarLogin, $config->sugarPassword);
$accounts = array();
$results = $sugar->getEntryList('Accounts', "accounts.account_type = 'Customer'", 'name', 0, array('name'));
foreach ($results->entry_list as $result) {
#$accounts[] = (object) array( 'id' => $result->id, 'label' => $result->name_value_list[0]->value, 'value' => $result->name_value_list[0]->value );
$accounts[] = array('id' => $result->id, 'value' => $result->name_value_list[0]->value);
}
} catch (Exception $e) {
print_r($e);
}
find_suggestions($domains, $accounts);
function domainToAccount($domainname, $account, $account_id)
{
$owner = R::findOne('owner', 'account_id=?', array($account_id));
if ($owner === false) {
$owner = R::dispense("owner");
$owner->name = $account;
/**
* @depends testSetAttributesWithPostForCustomField
*/
public function testUpdateValueOnCustomFieldRows()
{
$values = array('A', 'B', 'C');
$customFieldData = CustomFieldData::getByName('updateItems');
$customFieldData->serializedData = serialize($values);
$this->assertTrue($customFieldData->save());
$id = $customFieldData->id;
$customField = new CustomField();
$customField->value = 'A';
$customField->data = $customFieldData;
$this->assertTrue($customField->save());
$customField = new CustomField();
$customField->value = 'B';
$customField->data = $customFieldData;
$this->assertTrue($customField->save());
$customField = new CustomField();
$customField->value = 'C';
$customField->data = $customFieldData;
$this->assertTrue($customField->save());
$customField = new CustomField();
$customField->value = 'C';
$customField->data = $customFieldData;
$this->assertTrue($customField->save());
$quote = DatabaseCompatibilityUtil::getQuote();
$customFieldTableName = RedBeanModel::getTableName('CustomField');
$baseCustomFieldTableName = RedBeanModel::getTableName('BaseCustomField');
$valueAttributeColumnName = 'value';
$dataAttributeColumnName = RedBeanModel::getForeignKeyName('CustomField', 'data');
$sql = "select {$quote}{$customFieldTableName}{$quote}.id from {$quote}{$customFieldTableName}{$quote} ";
$sql .= "left join {$quote}{$baseCustomFieldTableName}{$quote} on ";
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote}.id = ";
$sql .= "{$quote}{$customFieldTableName}{$quote}.basecustomfield_id ";
$sql .= "where {$quote}{$dataAttributeColumnName}{$quote} = {$id}";
$ids = R::getCol($sql);
$beans = R::batch($customFieldTableName, $ids);
$customFields = RedBeanModel::makeModels($beans, 'CustomField');
$this->assertEquals(4, count($customFields));
$sql = "select {$quote}{$customFieldTableName}{$quote}.id from {$quote}{$customFieldTableName}{$quote} ";
$sql .= "left join {$quote}{$baseCustomFieldTableName}{$quote} on ";
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote}.id = ";
$sql .= "{$quote}{$customFieldTableName}{$quote}.basecustomfield_id ";
$sql .= "where {$quote}{$dataAttributeColumnName}{$quote} = {$id} ";
$sql .= "and {$quote}{$valueAttributeColumnName}{$quote} = 'B'";
$this->assertEquals(1, count(R::getCol($sql)));
$sql = "select {$quote}{$customFieldTableName}{$quote}.id from {$quote}{$customFieldTableName}{$quote} ";
$sql .= "left join {$quote}{$baseCustomFieldTableName}{$quote} on ";
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote}.id = ";
$sql .= "{$quote}{$customFieldTableName}{$quote}.basecustomfield_id ";
$sql .= "where {$quote}{$dataAttributeColumnName}{$quote} = {$id} ";
$sql .= "and {$quote}{$valueAttributeColumnName}{$quote} = 'C'";
$this->assertEquals(2, count(R::getCol($sql)));
$sql = "select {$quote}{$customFieldTableName}{$quote}.id from {$quote}{$customFieldTableName}{$quote} ";
$sql .= "left join {$quote}{$baseCustomFieldTableName}{$quote} on ";
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote}.id = ";
$sql .= "{$quote}{$customFieldTableName}{$quote}.basecustomfield_id ";
$sql .= "where {$quote}{$dataAttributeColumnName}{$quote} = {$id} ";
$sql .= "and {$quote}{$valueAttributeColumnName}{$quote} = 'E'";
$this->assertEquals(0, count(R::getCol($sql)));
CustomField::updateValueByDataIdAndOldValueAndNewValue($id, 'C', 'E');
$sql = "select {$quote}{$customFieldTableName}{$quote}.id from {$quote}{$customFieldTableName}{$quote} ";
$sql .= "left join {$quote}{$baseCustomFieldTableName}{$quote} on ";
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote}.id = ";
$sql .= "{$quote}{$customFieldTableName}{$quote}.basecustomfield_id ";
$sql .= "where {$quote}{$dataAttributeColumnName}{$quote} = {$id} ";
$sql .= "and {$quote}{$valueAttributeColumnName}{$quote} = 'B'";
$this->assertEquals(1, count(R::getCol($sql)));
$sql = "select {$quote}{$customFieldTableName}{$quote}.id from {$quote}{$customFieldTableName}{$quote} ";
$sql .= "left join {$quote}{$baseCustomFieldTableName}{$quote} on ";
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote}.id = ";
$sql .= "{$quote}{$customFieldTableName}{$quote}.basecustomfield_id ";
$sql .= "where {$quote}{$dataAttributeColumnName}{$quote} = {$id} ";
$sql .= "and {$quote}{$valueAttributeColumnName}{$quote} = 'C'";
$this->assertEquals(0, count(R::getCol($sql)));
$sql = "select {$quote}{$customFieldTableName}{$quote}.id from {$quote}{$customFieldTableName}{$quote} ";
$sql .= "left join {$quote}{$baseCustomFieldTableName}{$quote} on ";
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote}.id = ";
$sql .= "{$quote}{$customFieldTableName}{$quote}.basecustomfield_id ";
$sql .= "where {$quote}{$dataAttributeColumnName}{$quote} = {$id} ";
$sql .= "and {$quote}{$valueAttributeColumnName}{$quote} = 'E'";
$this->assertEquals(2, count(R::getCol($sql)));
}
<?php
// all_messages.php
include_once "config.php";
$current_user = get_from_session("current_user");
if (is_null($current_user)) {
store_in_session("message", "You must login to access this page");
header("Location: index.php");
return;
}
$sql = "select count(*) from messages where to_id=? and is_read=0";
$count = R::getCol($sql, array($current_user["id"]));
$count = $count[0];
include_once "header.php";
?>
<style type="text/css">
@import "css/experiment.css";
</style>
<?php
include_once "menu.php";
?>
<div id="all_messages_container">
<div id="all_messages_header">
<h1>All messages</h1>
</div>
<?php
if ($count == 0) {
?>
/**
* Basic tests through Facade.
*
* @return void
*/
public function testBasicThroughFacade()
{
$toolbox = R::$toolbox;
$adapter = $toolbox->getDatabaseAdapter();
$writer = $toolbox->getWriter();
$redbean = $toolbox->getRedBean();
$pdo = $adapter->getDatabase();
$a = new RedBean_AssociationManager($toolbox);
asrt(R::$redbean instanceof RedBean_OODB, TRUE);
asrt(R::$toolbox instanceof RedBean_Toolbox, TRUE);
asrt(R::$adapter instanceof RedBean_Adapter, TRUE);
asrt(R::$writer instanceof RedBean_QueryWriter, TRUE);
$book = R::dispense("book");
asrt($book instanceof RedBean_OODBBean, TRUE);
$book->title = "a nice book";
$id = R::store($book);
asrt($id > 0, TRUE);
$book = R::load("book", (int) $id);
asrt($book->title, "a nice book");
$author = R::dispense("author");
$author->name = "me";
R::store($author);
$book9 = R::dispense("book");
$author9 = R::dispense("author");
$author9->name = "mr Nine";
$a9 = R::store($author9);
$book9->author_id = $a9;
$bk9 = R::store($book9);
$book9 = R::load("book", $bk9);
$author = R::load("author", $book9->author_id);
asrt($author->name, "mr Nine");
R::trash($author);
R::trash($book9);
pass();
$book2 = R::dispense("book");
$book2->title = "second";
R::store($book2);
R::associate($book, $book2);
asrt(count(R::related($book, "book")), 1);
$book3 = R::dispense("book");
$book3->title = "third";
R::store($book3);
R::associate($book, $book3);
asrt(count(R::related($book, "book")), 2);
asrt(count(R::find("book")), 3);
asrt(count(R::findAll("book")), 3);
asrt(count(R::findAll("book", " WHERE ROWNUM <= 2")), 2);
asrt(count(R::find("book", " id=id ")), 3);
asrt(count(R::find("book", " title LIKE ?", array("third"))), 1);
asrt(count(R::find("book", " title LIKE ?", array("%d%"))), 2);
// Now with new SQL Helper argument
asrt(count(R::find("book", R::$f->begin()->addSQL('title LIKE ? ')->put('third'))), 1);
asrt(count(R::find("book", R::$f->begin()->addSQL('title LIKE ? ')->put('%d%'))), 2);
asrt(count(R::find("book", R::$f->begin()->addSQL('title')->like(' ? ')->addSQL(' ORDER BY id ')->desc()->put('%d%'))), 2);
//Find without where clause
asrt(count(R::findAll('book', ' order by id')), 3);
R::unassociate($book, $book2);
asrt(count(R::related($book, "book")), 1);
R::trash($book3);
R::trash($book2);
asrt(count(R::related($book, "book")), 0);
asrt(count(R::getAll("SELECT * FROM book ")), 1);
asrt(count(R::getCol("SELECT title FROM book ")), 1);
asrt((int) R::getCell("SELECT 123 FROM DUAL "), 123);
$book = R::dispense("book");
$book->title = "not so original title";
$author = R::dispense("author");
$author->name = "Bobby";
R::store($book);
$aid = R::store($author);
R::associate($book, $author);
$author = R::findOne("author", " name = ? ", array("Bobby"));
$books = R::related($author, "book");
$book = reset($books);
testpack("Test Swap function in R-facade");
$book = R::dispense("book");
$book->title = "firstbook";
$book->rating = 2;
$id1 = R::store($book);
$book = R::dispense("book");
$book->title = "secondbook";
$book->rating = 3;
$id2 = R::store($book);
$book1 = R::load("book", $id1);
$book2 = R::load("book", $id2);
asrt($book1->rating, '2');
asrt($book2->rating, '3');
$books = R::batch("book", array($id1, $id2));
R::swap($books, "rating");
$book1 = R::load("book", $id1);
$book2 = R::load("book", $id2);
asrt($book1->rating, '3');
asrt($book2->rating, '2');
testpack("Test R::convertToBeans");
$SQL = "SELECT '1' as id, a.name AS name, b.title AS title, '123' as rating FROM author a LEFT JOIN book b ON b.id = ? WHERE a.id = ? ";
$rows = R::$adapter->get($SQL, array($id2, $aid));
$beans = R::convertToBeans("something", $rows);
$bean = reset($beans);
asrt($bean->getMeta("type"), "something");
asrt($bean->name, "Bobby");
asrt($bean->title, "secondbook");
asrt($bean->rating, "123");
testpack("Ext Assoc with facade and findRelated");
R::nuke();
$cd = R::dispense("cd");
$cd->title = "Midnight Jazzfest";
R::store($cd);
$track = R::dispense("track");
$track->title = "Night in Tunesia";
$track2 = R::dispense("track");
$track2->title = "Stompin at one o clock";
$track3 = R::dispense("track");
$track3->title = "Nightlife";
R::store($track);
R::store($track2);
R::store($track3);
// Assoc ext with json
R::associate($track, $cd, '{"order":1}');
pass();
// Width array
R::associate($track2, $cd, array("order" => 2));
pass();
R::associate($track3, $cd, '{"order":3}');
pass();
$tracks = R::related($cd, "track", " title LIKE ? ", array("Night%"));
asrt(count($tracks), 2);
$track = array_pop($tracks);
asrt(strpos($track->title, "Night") === 0, TRUE);
$track = array_pop($tracks);
asrt(strpos($track->title, "Night") === 0, TRUE);
$track = R::dispense("track");
$track->title = "test";
R::associate($track, $cd, "this column should be named extra");
asrt(R::getCell("SELECT count(*) FROM cd_track WHERE extra = 'this column should be named extra' "), "1");
$composer = R::dispense("performer");
$composer->name = "Miles Davis";
R::store($composer);
}
case 'all':
$last_run_time = '1970101.000000';
break;
default:
R::close();
exit('Invalid delete mode [' . $mode . ']\\n');
}
} catch (Exception $e) {
R::close();
exit('DB alarm get run time transation failed \\n' . $e->getMessage());
}
try {
if ($target == 'dc-view') {
$data_removed = R::getCol(' SELECT DISTINCT create_time AS last_rm_time ' . ' FROM dcom_dcview ' . ' WHERE create_time >= :create_time ', [':create_time' => $last_run_time]);
} else {
$data_removed = R::getCol(' SELECT DISTINCT create_time AS last_rm_time ' . ' FROM dcom_yidong ' . ' WHERE yidong_code LIKE "' . $target . '%" ' . ' AND create_time >= :create_time ', [':create_time' => $last_run_time]);
}
} catch (Exception $e) {
R::close();
exit('DB alarm get run time transation failed \\n' . $e->getMessage());
}
try {
R::begin();
if ($target == 'dc-view') {
$data_yidong = R::exec(' DELETE FROM dcom_dcview ' . ' WHERE create_time >= :create_time ', [':create_time' => $last_run_time]);
} else {
$data_yidong = R::exec(' DELETE FROM dcom_yidong ' . ' WHERE yidong_code LIKE "' . $target . '%" ' . ' AND create_time >= :create_time ', [':create_time' => $last_run_time]);
$data_guanlian = R::exec(' DELETE FROM dcom_yidongguanlian ' . ' WHERE yidong_code LIKE "' . $target . '%" ' . ' AND guanlianzhi >= :guanlianzhi ', [':guanlianzhi' => $last_run_time]);
}
} catch (Exception $e) {
R::rollback();
$book3 = R::dispense("book");
$book3->title = "third";
R::store($book3);
R::associate($book, $book3);
asrt(count(R::related($book, "book")), 2);
asrt(count(R::find("book")), 3);
asrt(count(R::find("book", "1")), 3);
asrt(count(R::find("book", " title LIKE ?", array("third"))), 1);
asrt(count(R::find("book", " title LIKE ?", array("%d%"))), 2);
R::unassociate($book, $book2);
asrt(count(R::related($book, "book")), 1);
R::trash($book3);
R::trash($book2);
asrt(count(R::related($book, "book")), 0);
asrt(count(R::getAll("SELECT * FROM book ")), 1);
asrt(count(R::getCol("SELECT title FROM book ")), 1);
asrt((int) R::getCell("SELECT 123 "), 123);
testpack("FUSE");
testpack("test FUSE for association removal");
$marker = false;
class Model_Book_Page extends RedBean_SimpleModel
{
public function update()
{
}
public function delete()
{
global $marker;
$marker = true;
}
}
public static function getBean_ids($table, $parentBean, $withParent = true)
{
$where = $withParent ? 'left_id >= ? AND right_id <= ?' : 'left_id > ? AND right_id < ?';
return R::getCol('SELECT id FROM ' . $table . ' WHERE ' . $where, array((int) $parentBean->left_id, (int) $parentBean->right_id));
}
public static function shortuuid()
{
$uuid = R::getCol('select UUID_SHORT()');
return $uuid[0];
}
}
});
go_home();
break;
case 'saveteam':
$team = R::load('team', get_param('id'))->import($_POST, 'name,description');
R::store($team);
go_home();
break;
}
//---------------------------------- VIEWS (GET) ----------------------------------
$template = StampTE::load('template.html');
switch ($cmd) {
case 'main':
$toolbar = $template->getToolbar();
$years = R::getCol('SELECT DISTINCT strftime("%Y",start) FROM period ORDER BY start ASC');
if (count($years) > 0) {
$yearSelector = $toolbar->getYearSelector();
foreach ($years as $year) {
$yearSelector->add($yearSelector->getYearOption()->setYear($year)->attr('selected', $currentYear == $year));
}
$toolbar->add($yearSelector);
}
$template->add($toolbar);
$sidebar = $template->getSidebar();
//Teams
foreach (R::find('team') as $team) {
$teamRow = $sidebar->getTeam();
$availableHrsTeam = 0;
$teamRow->setTeamDisplayName(cut($team->name, 20))->setLinkToTeamView("?c=selectteam&id={$team->id}")->setSelected($team->id == $currentTeam->id ? 'selected' : '');
$sidebar->add($teamRow);
/**
* Tries to find the value in the system. If found, returns true, otherwise false.
* @param string $value
* @return boolean
*/
protected function resolveFoundExternalSystemIdByValue($value)
{
assert('is_int($value) || is_string($value) || $value == null');
if ($value == null) {
return false;
}
$modelClassName = $this->attributeModelClassName;
$columnName = ExternalSystemIdUtil::EXTERNAL_SYSTEM_ID_COLUMN_NAME;
$sql = 'select id from ' . $modelClassName::getTableName($modelClassName) . ' where ' . $columnName . ' = \'' . $value . '\' limit 1';
$ids = R::getCol($sql);
assert('count($ids) <= 1');
if (count($ids) == 0) {
return false;
}
return true;
}
public static function userBeingRemovedFromRole(User $user, Role $role)
{
foreach (self::getMungableModelClassNames() as $modelClassName) {
$mungeTableName = self::getMungeTableName($modelClassName);
$userId = $user->id;
$sql = "select securableitem_id\n from ownedsecurableitem\n where owner__user_id = {$userId}";
$securableItemIds = R::getCol($sql);
self::bulkDecrementParentRolesCounts($mungeTableName, $securableItemIds, $role);
$sql = "select {$mungeTableName}.securableitem_id\n from {$mungeTableName}, _group__user\n where {$mungeTableName}.munge_id = concat('G', _group__user._group_id) and\n _group__user._user_id = {$userId}";
$securableItemIds = R::getCol($sql);
self::bulkDecrementParentRolesCounts($mungeTableName, $securableItemIds, $role);
/*
* This additional step I don't think is needed because the sql query above actually traps
* the upstream explicit securableItems because the lower level groups will already have a point for
* each of them.
What groups are the user part of and what groups are those groups children of recursively?
For any models that have that group explicity for read, subtract 1 point for the user's
upstream roles from the disconnected role.
*/
self::garbageCollect($mungeTableName);
}
}
/**
* @param $id
* @param bool $topLevel 是否只获取下一级
* @param bool $includeSelf 是否包含本身
* @return array
* @throws \Exception
*/
public function getChildrenIds($id = 0, $topLevel = false, $includeSelf = true)
{
$id = (int) $id;
if ($id < 1) {
$ids = \R::getCol(sprintf('select id from %s', static::$tableName));
return $ids;
}
if ($topLevel) {
$ids = \R::getCol(sprintf('select id from %s where %s = %d', static::$tableName, static::$parentId, $id));
} else {
$ids = \R::getCol(sprintf('select id from %s where path like ?', static::$tableName), ['%/' . $id . '/%']);
}
if ($includeSelf) {
$ids[] = $id;
}
return $ids;
}
/**
* Drops all the tables in the databaes.
*/
public static function dropAllTables()
{
$tableNames = R::getCol('show tables');
foreach ($tableNames as $tableName) {
R::exec("drop table {$tableName}");
}
assert('count(R::getCol("show tables")) == 0');
}
public function testConnectToDatabaseCreateSuperUserBuildDatabaseAndFreeze()
{
// This test cannot run as saltdev. It is therefore skipped on the server.
if ($this->temporaryDatabaseUsername == 'root') {
$this->assertTrue(DatabaseCompatibilityUtil::createDatabase('mysql', $this->temporaryDatabaseHostname, $this->temporaryDatabaseUsername, $this->temporaryDatabasePassword, $this->temporaryDatabasePort, $this->temporaryDatabaseName));
$this->assertTrue(DatabaseCompatibilityUtil::createDatabaseUser('mysql', $this->temporaryDatabaseHostname, $this->temporaryDatabaseUsername, $this->temporaryDatabasePassword, $this->temporaryDatabasePort, $this->temporaryDatabaseName, 'wacko', 'wacked'));
InstallUtil::connectToDatabase('mysql', $this->temporaryDatabaseHostname, 'wacky', $this->temporaryDatabaseUsername, $this->temporaryDatabasePassword, $this->temporaryDatabasePort);
Yii::app()->user->userModel = InstallUtil::createSuperUser('super', 'super');
$messageLogger = new MessageLogger();
InstallUtil::autoBuildDatabase($messageLogger);
$this->assertFalse($messageLogger->isErrorMessagePresent());
ReadPermissionsOptimizationUtil::rebuild();
InstallUtil::freezeDatabase();
$tableNames = R::getCol('show tables');
$this->assertEquals(array('_group', '_group__user', '_right', '_user', 'account', 'account_read', 'activity', 'activity_item', 'actual_permissions_cache', 'address', 'auditevent', 'contact', 'contact_opportunity', 'contact_read', 'contactstate', 'currency', 'currencyvalue', 'customfield', 'customfielddata', 'dashboard', 'email', 'filecontent', 'filemodel', 'globalmetadata', 'item', 'log', 'mashableactivity', 'meeting', 'meeting_read', 'namedsecurableitem', 'note', 'note_read', 'opportunity', 'opportunity_read', 'ownedcustomfield', 'ownedsecurableitem', 'permission', 'permitable', 'person', 'perusermetadata', 'policy', 'portlet', 'role', 'securableitem', 'task', 'task_read'), $tableNames);
}
}
