public function getContentItems()
{
$p =& $this->requestParams;
$folderTemplates = \CB\Config::get('folder_templates');
$p['fl'] = 'id,system,path,name,case,date,date_end,size,cid,oid,cdate,uid,udate,template_id,acl_count,cls,status,task_status,dstatus';
if (@$p['from'] == 'tree') {
$p['templates'] = $folderTemplates;
}
if (is_numeric($this->lastNode->id)) {
$p['pid'] = $pid;
}
$p['dstatus'] = 1;
$p['fq'] = $this->fq;
$s = new \CB\Search();
$rez = $s->query($p);
if (!empty($rez['data'])) {
for ($i = 0; $i < sizeof($rez['data']); $i++) {
$d =& $rez['data'][$i];
$res = DB\dbQuery('SELECT cfg
, (SELECT 1
FROM tree
WHERE pid = $1' . (@$p['from'] == 'tree' ? ' AND `template_id` IN (0' . implode(',', $folderTemplates) . ')' : '') . ' LIMIT 1) has_childs
FROM tree
WHERE id = $1', $d['id']) or die(DB\dbQueryError());
if ($r = $res->fetch_assoc()) {
$d['cfg'] = Util\toJSONArray($r['cfg']);
$d['has_childs'] = !empty($r['has_childs']);
}
$res->close();
}
}
return $rez;
}
/**
* create an object with specified params
* @param array $p object properties
* @return int created id
*/
public function create($p = false)
{
if ($p === false) {
$p = $this->data;
}
// check input params
if (!isset($p['target_id'])) {
throw new \Exception("No target id specified for shortcut creation", 1);
}
//check if target is also shortuc and replace with its target
if (Objects::getType($p['target_id']) == 'shortcut') {
$res = DB\dbQuery('SELECT target_id
FROM tree
WHERE id = $1', $p['target_id']) or die(DB\dbQueryError());
if ($r = $res->fetch_assoc()) {
$p['target_id'] = $r['target_id'];
}
$res->close();
}
$p['name'] = 'link to #' . $p['target_id'];
if (empty($p['template_id'])) {
$p['template_id'] = \CB\Config::get('default_shortcut_template');
}
$this->data = $p;
return parent::create($p);
}
/**
* delete a record by its id
* @param int $id
* @return boolean
*/
public static function delete($id)
{
static::validateParamTypes(array('id' => $id));
DB\dbQuery('DELETE from `' . static::$tableName . '` ' . 'WHERE id = $1', $id) or die(DB\dbQueryError());
$rez = DB\dbAffectedRows() > 0;
return $rez;
}
/**
* read objects data in bulk manner
* @param array $ids
* @return array
*/
public static function read($ids)
{
$rez = array();
$ids = Util\toNumericArray($ids);
if (!empty($ids)) {
$sql = 'SELECT t.*
,ti.pids
,ti.path
,ti.case_id
,ti.acl_count
,ti.security_set_id
,o.data
,o.sys_data
FROM tree t
JOIN tree_info ti
ON t.id = ti.id
LEFT JOIN objects o
ON t.id = o.id
WHERE t.id in (' . implode(',', $ids) . ')';
$res = DB\dbQuery($sql) or die(DB\dbQueryError());
while ($r = $res->fetch_assoc()) {
$r['data'] = Util\jsonDecode($r['data']);
$r['sys_data'] = Util\jsonDecode($r['sys_data']);
$rez[] = $r;
}
$res->close();
}
return $rez;
}
/**
* create system folders specified in created objects template config as system_folders property
* @param object $o
* @return void
*/
public function onNodeDbCreate($o)
{
if (!is_object($o)) {
return;
}
$template = $o->getTemplate();
if (empty($template)) {
return;
}
$templateData = $template->getData();
if (empty($templateData['cfg']['system_folders'])) {
return;
}
$folderIds = Util\toNumericArray($templateData['cfg']['system_folders']);
if (empty($folderIds)) {
return;
}
$p = array('sourceIds' => array(), 'targetId' => $o->getData()['id']);
$browserActionsClass = new Browser\Actions();
$res = DB\dbQuery('SELECT id
FROM tree
WHERE pid in (' . implode(',', $folderIds) . ')
AND dstatus = 0') or die(DB\dbQueryError());
while ($r = $res->fetch_assoc()) {
$p['sourceIds'][] = $r['id'];
}
$res->close();
// $browserActionsClass->copy($p);
$browserActionsClass->objectsClass = new \CB\Objects();
$browserActionsClass->doRecursiveAction('copy', $p['sourceIds'], $p['targetId']);
}
public static function checkTableExistance()
{
return DB\dbQuery('CREATE TABLE IF NOT EXISTS `guids`(
`id` bigint(20) unsigned NOT NULL auto_increment ,
`name` varchar(200) COLLATE utf8_general_ci NOT NULL ,
PRIMARY KEY (`id`) ,
UNIQUE KEY `guids_name`(`name`)
) ENGINE=InnoDB DEFAULT CHARSET=\'utf8\' COLLATE=\'utf8_general_ci\'', array()) or die(DB\dbQueryError());
}
public static function deleteByNodeId($nodeId, $userId = false)
{
if ($userId == false) {
$userId = \CB\User::getId();
}
DB\dbQuery('DELETE FROM ' . static::getTableName() . ' WHERE user_id = $1 AND node_id = $2', array($userId, $nodeId)) or die(DB\dbQueryError());
$rez = DB\dbAffectedRows() > 0;
return $rez;
}
/**
* delete core record form __casebox.cores table
* and drop database
* @param varchar $idOrName
* @return boolean
*/
public static function delete($idOrName)
{
$id = static::toId($idOrName);
$data = static::read($id);
$rez = parent::delete($id);
if ($rez) {
$dbName = Config::get('prefix') . '_' . $data['name'];
DB\dbQuery("DROP DATABASE `{$dbName}`") or die(DB\dbQueryError());
}
return $rez;
}
public function getName($id = false)
{
if ($id == false) {
$id = $this->id;
}
$rez = 'no name';
$res = DB\dbQuery('SELECT name FROM tree WHERE id = $1', $id) or die(DB\dbQueryError());
if ($r = $res->fetch_assoc()) {
$rez = $r['name'];
}
$res->close();
return $rez;
}
/**
* get oldest version ids after a given skipCount
* @param int $fileId
* @param varchar $md5
* @return array | false
*/
public static function getVersionByMD5($fileId, $md5)
{
$rez = array();
$res = DB\dbQuery('SELECT f.*
FROM files_versions f
JOIN files_content c ON f.content_id = c.id
AND c.md5 = $2
WHERE f.file_id = $1', array($fileId, $md5)) or die(DB\dbQueryError());
if ($r = $res->fetch_assoc()) {
$rez = $r;
}
return $rez;
}
/**
* update a record
* @param array $p array with properties
* @return array
*/
public static function getRecords($ids)
{
$rez = array();
$ids = Util\toNumericArray($ids);
$res = DB\dbQuery('SELECT *
FROM `' . static::getTableName() . '`
WHERE id in (' . implode(',', $ids) . ')') or die(DB\dbQueryError());
while ($r = $res->fetch_assoc()) {
$rez[] = $r;
}
$res->close();
return $rez;
}
/**
* copy a record
* @param int $id
* @return boolean
*/
public static function copy($sourceId, $targetId)
{
DB\dbQuery('INSERT INTO `objects`
(`id`
,`data`
,`sys_data`)
SELECT
$2
,`data`
,`sys_data`
FROM `objects`
WHERE id = $1', array($sourceId, $targetId)) or die(DB\dbQueryError());
return DB\dbAffectedRows() > 0;
}
/**
* read recods in bulk for given names
* @param array $names
* @return associative array ('name' => id)
*/
public static function readNames($names)
{
$rez = array();
$params = array();
for ($i = 1; $i <= sizeof($names); $i++) {
$params[] = '$' . $i;
}
$sql = 'SELECT id, name
FROM ' . static::getTableName() . '
WHERE name in (' . implode(',', $params) . ')';
$res = DB\dbQuery($sql, $names) or die(DB\dbQueryError());
while ($r = $res->fetch_assoc()) {
$rez[$r['name']] = $r['id'];
}
$res->close();
return $rez;
}
public static function readAll()
{
$rez = array();
$sql = 'SELECT *
FROM ' . static::getTableName() . '
ORDER BY pid';
$res = DB\dbQuery($sql . ', `order`');
//order by 'order' field also
//backward compatibility
if (empty($res)) {
$res = DB\dbQuery($sql) or die(DB\dbQueryError());
}
while ($r = $res->fetch_assoc()) {
$rez[] = $r;
}
$res->close();
return $rez;
}
/**
* get relative content paths for given file ids
* path is relative to casebox files directory
* @param array $ids
* @return array associative array (id => relative_content_path)
*/
public static function getContentPaths($ids)
{
$rez = array();
$ids = Util\toNumericArray($ids);
if (!empty($ids)) {
$sql = 'SELECT f.id, c.`path`, f.content_id
FROM files f
JOIN files_content c
ON f.content_id = c.id
WHERE f.id in (' . implode(',', $ids) . ')';
$res = DB\dbQuery($sql) or die(DB\dbQueryError());
while ($r = $res->fetch_assoc()) {
$rez[$r['id']] = $r['path'] . DIRECTORY_SEPARATOR . $r['content_id'];
}
$res->close();
}
return $rez;
}
/**
* add a record
* @param array $p associative array with table field values
* @return int created id
*/
public static function create($p)
{
parent::create($p);
//prepare params
$params = array(empty($p['object_id']) ? null : $p['object_id'], empty($p['object_pid']) ? null : $p['object_pid'], empty($p['user_id']) ? null : $p['user_id'], empty($p['action_type']) ? null : $p['action_type'], empty($p['data']) ? null : $p['data'], empty($p['data']) ? null : $p['data'], empty($p['activity_data_db']) ? null : $p['activity_data_db'], empty($p['activity_data_solr']) ? null : $p['activity_data_solr']);
//add database record
$sql = 'INSERT INTO `' . static::$tableName . '` (
`object_id`
,`object_pid`
,`user_id`
,`action_type`
,`data`
,`activity_data_db`
,`activity_data_solr`
) VALUES ($1, $2, $3, $4, $5, $6, $7)';
DB\dbQuery($sql, $params) or die(DB\dbQueryError());
$rez = DB\dbLastInsertId();
return $rez;
}
/**
* load all plugins from database
*
* @return void
*/
public function loadAll()
{
if (!empty($this->loaded)) {
return $this->items;
}
$this->items = array();
$res = DB\dbQuery('SELECT id
,name
,cfg
,`active`
,`order`
FROM ' . \CB\PREFIX . '_casebox.plugins
ORDER BY `order`') or die(DB\dbQueryError());
while ($r = $res->fetch_assoc()) {
$r['cfg'] = Util\toJSONArray($r['cfg']);
$this->items[$r['name']] = $r;
}
$res->close();
$this->loaded = true;
}
/**
* executing preimporting changes to target core
* @return void
*/
protected function prepare()
{
//update template types
DB\dbQuery("ALTER TABLE `templates`\n CHANGE `type` `type` ENUM('case','object','file','task','user','email','template','field','search','comment','shortcut','menu','config')\n CHARSET utf8 COLLATE utf8_general_ci NULL") or die(DB\dbQueryError());
// set templates template id in config
$ids = DM\Templates::getIdsByType('template');
$id = array_shift($ids);
BBM::$cfg['templatesTemplateId'] = $id;
// $this->cfg['templates']['Menu rule']['fields']['template_ids']['cfg']['templates'] = $id;
// $this->cfg['templates']['Menu rule']['fields']['menu']['cfg']['templates'] = $id;
// detect fields template id
$ids = DM\Templates::getIdsByType('field');
$id = array_shift($ids);
BBM::$cfg['fieldTemplateId'] = $id;
//detect folderTemplateId
$ids = Config::get('folder_templates');
if (!empty($ids)) {
BBM::$cfg['folderTemplateId'] = array_shift($ids);
}
//create "Config" folder under templates to store our config templates there
//and update BBM::$cfg['templatesFolderId'] to our folder id
$o = new \CB\Objects\Object();
$rootId = Browser::getRootFolderId();
$this->systemFolderId = Objects::getChildId($rootId, 'System');
$pid = Objects::getChildId($rootId, 'Templates');
if (empty($pid)) {
if (!empty($this->systemFolderId)) {
$pid = Objects::getChildId($this->systemFolderId, 'Templates');
}
}
$id = $o->create(array('id' => null, 'pid' => $pid, 'template_id' => BBM::$cfg['folderTemplateId'], 'name' => 'Config', 'data' => array('_title' => 'Config')));
BBM::$cfg['templatesFolderId'] = $id;
//create System/Config folder for transferring config options
$pid = Objects::getChildId($rootId, 'System');
if (empty($pid)) {
$pid = $o->create(array('id' => null, 'pid' => $rootId, 'template_id' => BBM::$cfg['folderTemplateId'], 'name' => 'System', 'data' => array('_title' => 'System')));
}
$this->cfg['configFolderId'] = $o->create(array('id' => null, 'pid' => $pid, 'template_id' => BBM::$cfg['folderTemplateId'], 'name' => 'Config', 'data' => array('_title' => 'Config')));
}
/**
* update create menus
* @return void
*/
protected function updateCreateMenus()
{
$rootId = \CB\Browser::getRootFolderId();
$pid = Objects::getChildId($rootId, 'System');
if (!empty($pid)) {
$pid = Objects::getChildId($pid, 'Menus');
if (!empty($pid)) {
$res = DB\dbQuery('SELECT id
FROM tree
WHERE pid = $1
AND dstatus = 0', $pid) or die(DB\dbQueryError());
if ($r = $res->fetch_assoc()) {
$o = Objects::getCachedObject($r['id']);
$d = $o->getData();
$d['data']['menu'] = $this->templateIds['Case'] . ',' . $this->templateIds['Contact'] . ',' . $this->templateIds['Organization'] . ',' . $d['data']['menu'];
$o->update($d);
}
$res->close();
}
}
}
WHERE `sys_data` LIKE \'%"subscribers"%\'') or die(DB\dbQueryError());
if ($r = $res->fetch_assoc()) {
echo "Total objects: " . $r['nr'] . "\n";
}
$res->close();
DB\startTransaction();
//iterate and upgrade each object
$i = 0;
$res = DB\dbQuery('SELECT id, sys_data
FROM objects
WHERE `sys_data` LIKE \'%"subscribers"%\'') or die(DB\dbQueryError());
while ($r = $res->fetch_assoc()) {
if ($i > 100) {
$i = 0;
echo '.';
}
$d = Util\toJSONArray($r['sys_data']);
if (empty($d['fu'])) {
$d['fu'] = array();
}
$su = $d['subscribers']['on'];
unset($d['subscribers']);
$d['fu'] = array_unique(Util\toNumericArray(array_merge($d['fu'], $su)));
DB\dbQuery('UPDATE objects
SET `sys_data` = $2
WHERE id = $1', array($r['id'], Util\jsonEncode($d))) or die(DB\dbQueryError());
$i++;
}
$res->close();
DB\commitTransaction();
echo "\nDone";
/**
* mark all notifications as read for given user
* @param int $userId
* @return void
*/
public static function markAllAsRead($userId)
{
//validate params
if (!is_numeric($userId)) {
trigger_error(L\get('ErroneousInputData'), E_USER_ERROR);
}
DB\dbQuery('UPDATE `' . static::$tableName . '`
SET `read` = 1
WHERE user_id = $1 AND `read` = 0', $userId) or die(DB\dbQueryError());
}
/**
* method to get users and groups display data in bulk manner (for rendering)
* @return array associative array: id => array(id, name, title, iconCls)
*/
public static function getDisplayData()
{
$rez = array();
$sql = 'SELECT id
,name
,trim( CONCAT(coalesce(first_name, \'\'), \' \', coalesce(last_name, \'\')) ) `title`
,CASE WHEN (`type` = 1) THEN \'icon-users\' ELSE CONCAT(\'icon-user-\', coalesce(sex, \'\') ) END `iconCls`
FROM users_groups';
$res = DB\dbQuery($sql) or die(DB\dbQueryError());
while ($r = $res->fetch_assoc()) {
$rez[$r['id']] = $r;
}
$res->close();
return $rez;
}
/**
* update create menus
* @return void
*/
protected function updateCreateMenus()
{
//add case template at the begining of default menu
DB\dbQuery('UPDATE menu
SET menu = CONCAT($1, menu)
WHERE node_ids IS NULL
AND node_template_ids IS NULL', $this->templateIds['Case'] . ',\'-\',' . $this->templateIds['Contact'] . ',' . $this->templateIds['Organization'] . ',\'-\',') or die(DB\dbQueryError());
}
/**
* function to set translations in Cache
*/
function initTranslations()
{
$translations = \CB\Cache::get('translations', []);
// if already defined translations then exit
if (!empty($translations)) {
return;
}
$languages = \CB\Config::get('languages');
// or : \CB\USER_LANGUAGE;
/* reading main translations table from casebox database*/
$res = DB\dbQuery('SELECT name, ' . implode(',', $languages) . '
FROM ' . \CB\PREFIX . '_casebox.translations
WHERE `type` < 2') or die(DB\dbQueryError());
// if ($rez = $res->fetch_all(MYSQLI_ASSOC)) {
// foreach ($rez as &$r) {
while ($r = $res->fetch_assoc()) {
reset($r);
$name = current($r);
while ($v = next($r)) {
$translations[key($r)][$name] = $v;
}
// }
}
$res->close();
/* reading specific translations of core */
$res = DB\dbQuery('SELECT *
FROM translations
WHERE `type` < 2') or die(DB\dbQueryError());
// if ($rez = $res->fetch_all(MYSQLI_ASSOC)) {
// foreach ($rez as &$r) {
while ($r = $res->fetch_assoc()) {
foreach ($languages as $l) {
if (!empty($r[$l])) {
$translations[$l][$r['name']] = $r[$l];
}
}
// }
}
$res->close();
\CB\Cache::set('translations', $translations);
}
/**
* updating modified nodes info into solr from tree)info table
*/
private function updateTreeInfo($p)
{
/* connect to solr service */
$this->connect();
/** @type int the last processed document id */
$lastId = 0;
/* prepeare $where condition for sql */
$where = 'ti.id > $1';
if (!empty($p['id'])) {
$ids = \CB\Util\toNumericArray($p['id']);
$where = 'ti.id in (0' . implode(',', $ids) . ')';
}
$sql = 'SELECT ti.id
,ti.pids
,ti.case_id
,ti.acl_count
,ti.security_set_id
,t.name `case`
FROM tree_info ti
LEFT JOIN tree t
ON ti.case_id = t.id
WHERE ' . $where . '
AND ti.updated = 1
ORDER BY ti.id
LIMIT 200';
$docs = true;
while (!empty($docs)) {
$docs = array();
$res = DB\dbQuery($sql, $lastId) or die(DB\dbQueryError());
while ($r = $res->fetch_assoc()) {
$lastId = $r['id'];
$r['update'] = true;
if (empty($r['pids'])) {
$r['pids'] = null;
$r['path'] = null;
} else {
$r['pids'] = explode(',', $r['pids']);
//exclude itself from pids
array_pop($r['pids']);
$r['path'] = implode('/', $r['pids']);
}
//encode special chars for string values
foreach ($r as $k => $v) {
if (is_string($v)) {
$r[$k] = htmlspecialchars($v, ENT_COMPAT);
}
}
$docs[$r['id']] = $r;
$this->updateCronLastActionTime(@$p['cron_id']);
}
$res->close();
if (!empty($docs)) {
$this->addDocuments($docs);
/* reset updated flag into database for processed documents info */
DB\dbQuery('UPDATE tree_info
SET updated = 0
WHERE id IN (' . implode(', ', array_keys($docs)) . ')') or die(DB\dbQueryError());
$this->updateCronLastActionTime(@$p['cron_id']);
$this->commit();
}
}
}
/**
* move an object to $pid or over $targetId
*
* we'll use the same principle as for copy
*
* @param int $pid if not specified then will be set to pid of targetId
* @param int $targetId
* @return int the id of moved object or false
*/
public function moveTo($pid = false, $targetId = false)
{
// check input params
if (!is_numeric($this->id) || !is_numeric($pid) && !is_numeric($targetId)) {
return false;
}
/* security check */
if (!\CB\Security::canRead($this->id)) {
return false;
}
/* end of security check */
//load current object from db into a variable to be passed to log and events
$this->oldObject = clone $this;
$this->oldObject->load($this->id);
if (is_numeric($targetId)) {
/* target security check */
if (!\CB\Security::canWrite($targetId)) {
return false;
}
/* end of target security check */
// marking overwriten object with dstatus = 3
DB\dbQuery('UPDATE tree
SET updated = 1
,dstatus = 3
,did = $2
WHERE id = $1', array($targetId, $_SESSION['user']['id'])) or die(DB\dbQueryError());
//get pid from target if not specified
$res = DB\dbQuery('SELECT pid FROM tree WHERE id = $1', $targetId) or die(DB\dbQueryError());
if ($r = $res->fetch_assoc()) {
$pid = $r['pid'];
}
$res->close();
} else {
/* pid security check */
if (!\CB\Security::canWrite($pid)) {
return false;
}
/* end of pid security check */
}
/* check again if we have pid set
It can be unset when not existent $targetId is specified
*/
if (!is_numeric($pid)) {
return false;
}
// moving the object to $pid
DB\dbQuery('UPDATE tree
SET updated = 1
,pid = $2
WHERE id = $1', array($this->id, $pid)) or die(DB\dbQueryError());
$this->moveCustomDataTo($pid);
// move childs from overwriten targetId (which has been marked with dstatus = 3)
// to newly copied object
if (is_numeric($targetId)) {
DB\dbQuery('UPDATE tree
SET updated = 1
,pid = $2
WHERE pid = $1 AND
dstatus = 0', array($targetId, $this->id)) or die(DB\dbQueryError());
}
$this->load();
$this->logAction('move', array('old' => $this->oldObject));
return $this->id;
}
/**
* mark all notifications as read for given user
* @param int $userId
* @return void
*/
public static function markAllAsRead($userId)
{
//validate params
\CB\raiseErrorIf(!is_numeric($userId), 'ErroneousInputData');
DB\dbQuery('UPDATE `' . static::getTableName() . '`
SET `read` = 1
WHERE user_id = $1 AND `read` = 0', $userId) or die(DB\dbQueryError());
}
/**
* get user owner id
* @param int $userId
* @param varchar $pass
* @return boolean
*/
public static function verifyPassword($userId, $pass)
{
$rez = false;
$res = DB\dbQuery('SELECT id
FROM users_groups
WHERE id = $1
AND `password`= md5($2)', array($userId, 'aero' . $pass)) or die(DB\dbQueryError());
if ($r = $res->fetch_assoc()) {
$rez = true;
}
$res->close();
return $rez;
}
/**
* get template type by its id
* @param int $templateId
* @return varchar
*/
public function getType($templateId)
{
if (!is_numeric($templateId)) {
return null;
}
// check if template has been loaded
if (!empty($this->templates[$templateId])) {
return $this->templates[$templateId]->getData()['type'];
}
$var_name = 'template_type' . $templateId;
if (!\CB\Cache::exist($var_name)) {
$res = DB\dbQuery('SELECT `type`
FROM templates
WHERE id = $1', $templateId) or die(DB\dbQueryError());
if ($r = $res->fetch_assoc()) {
\CB\Cache::set($var_name, $r['type']);
}
$res->close();
}
return \CB\Cache::get($var_name);
}
/**
* get name for given id or return same result if numeric
* @param varchar $idOrName
* @return int | null
*/
public static function toId($idOrName)
{
if (!is_numeric($idOrName)) {
$res = DB\dbQuery('SELECT id
FROM ' . static::getTableName() . ' WHERE name = $1', $idOrName) or die(DB\dbQueryError());
if ($r = $res->fetch_assoc()) {
$idOrName = $r['id'];
}
$res->close();
}
return $idOrName;
}