/** * 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); }
/** * 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'); 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']); }
/** * 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; }
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; }
/** * 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; }
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()); }
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; }
/** * 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 (0' . implode(',', $ids) . ')'); while ($r = $res->fetch_assoc()) { $rez[] = $r; } $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)); if ($r = $res->fetch_assoc()) { $rez = $r; } 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; }
public function getName($id = false) { if ($id == false) { $id = $this->id; } $rez = 'no name'; $res = DB\dbQuery('SELECT name, iconCls FROM templates WHERE id = $1', $id); if ($r = $res->fetch_assoc()) { $rez = $r['name']; $this->iconCls = $r['iconCls']; } $res->close(); return $rez; }
/** * 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; }
/** * 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; }
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); } while ($r = $res->fetch_assoc()) { $rez[] = $r; } $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'))); }
/** * recursive objects moving or copying * @param int|array $objectIds source object ids * @param int $targetId target id * @return array processed ids */ public function doRecursiveAction($action, $objectIds, $targetId) { $rez = array(); if (!is_array($objectIds)) { $objectIds = Util\toNumericArray($objectIds); } if (empty($objectIds)) { return false; } foreach ($objectIds as $objectId) { $newId = null; // check if object with same name exist in target $existentTargetId = $this->overwriteCheck($objectId, $targetId); if ($existentTargetId == false) { // copy by creating a new object in target or just move switch ($action) { case 'copy': $newId = $this->objectsClass->copy($objectId, $targetId); break; case 'move': $newId = $this->objectsClass->move($objectId, $targetId); break; } } else { switch ($action) { case 'copy': $newId = $this->objectsClass->copy($objectId, $targetId, $existentTargetId); break; case 'move': $newId = $this->objectsClass->move($objectId, $targetId, $existentTargetId); break; } } // skip childs copy if object not copied/moved if (empty($newId)) { continue; } $rez[] = $newId; // skip childs moving if moved object is itself if ($newId == $objectId) { continue; } // select direct childs of the objects and make a recursive call with them $res = DB\dbQuery('SELECT t.id FROM tree t JOIN tree_info ti ON t.id = ti.id ' . $this->securitySetsFilter . ' WHERE t.pid = $1 AND t.dstatus = 0', $objectId); $childIds = array(); while ($r = $res->fetch_assoc()) { $childIds[] = $r['id']; } $res->close(); $this->doRecursiveAction($action, $childIds, $newId); } return $rez; }
/** * copy a record * @param int $id * @return boolean */ public static function copy($sourceId, $targetId) { $r = Tree::read($targetId); $pid = empty($r) ? null : $r['pid']; DB\dbQuery('INSERT INTO ' . static::getTableName() . ' (id, pid, `is_folder`, `type`, `name`, `l1`, `l2`, `l3`, `l4`, `order`, `visible`, `iconCls`, `default_field`, `cfg`, `title_template`, `info_template`) SELECT $2, $3, `is_folder`, `type`, `name`, `l1`, `l2`, `l3`, `l4`, `order`, `visible`, `iconCls`, `default_field`, `cfg`, `title_template`, `info_template` FROM ' . static::getTableName() . ' WHERE id = $1', array($sourceId, $targetId, $pid)); return DB\dbAffectedRows() > 0; }
/** * 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); }
/** * 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(); } } }
/** * 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()); }
/** * 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; }
/** * 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()); }