function addToSharingTable() {
parent::addToSharingTable(); // Default processing
$oid = $this->getId() ;
if ( $member = $this->getSelfMember() ) {
$mid = $member->getId();
$sql = "
SELECT distinct(permission_group_id) as gid
FROM ".TABLE_PREFIX."contact_member_permissions
WHERE member_id = $mid
";
$rows = DB::executeAll($sql);
if (is_array($rows)) {
foreach ($rows as $row ) {
$values = array();
if ($gid = array_var($row, 'gid')) {
$values[] = "($oid, $gid)";
}
if (count($values) > 0) {
$values_str = implode(",", $values);
DB::execute("INSERT INTO ".TABLE_PREFIX."sharing_table (object_id, group_id) VALUES $values_str ON DUPLICATE KEY UPDATE object_id=object_id");
}
}
}
}
}
/**
*
* @param Contact $user
* @param array $permissions
* @param ContactPermissionGroup $group
*/
function afterUserPermissionChanged($user, $permissions, $group = null)
{
//get members ids
$membersIds = array();
if (is_null($group)) {
//get all members affected from $permission
foreach ($permissions as $permission) {
$memberId = $permission->m;
if (!in_array($memberId, $membersIds)) {
$membersIds[] = $memberId;
}
}
} else {
// dimension
$dimensions = Dimensions::findAll();
$contact_pg_ids = $group->getId();
//get all allowed members for the group
$allowed_members = array();
foreach ($dimensions as $dimension) {
$member_list = array();
if ($dimension->getDefinesPermissions()) {
$member_list = DB::executeAll("SELECT * FROM " . TABLE_PREFIX . "members WHERE dimension_id = " . $dimension->getId() . " ORDER BY id");
}
foreach ($member_list as $dim_member) {
if (ContactMemberPermissions::instance()->contactCanAccessMemberAll($contact_pg_ids, $dim_member['id'], $user, ACCESS_LEVEL_READ, false)) {
$allowed_members[] = $dim_member['id'];
}
}
}
$membersIds = $allowed_members;
}
foreach ($membersIds as $member_id) {
ContactMemberCaches::updateContactMemberCache($user, $member_id);
}
}
/**
* Return categories
*
* @param string $locale Locale key
* @return I18nLocaleValue
*/
function getCategories($locale)
{
trace(__FILE__, 'getCategories():begin');
// Prepare SQL
$set1 = "(SELECT DISTINCT `category_id` FROM " . $this->getTableName(true) . " WHERE `locale_id` = {$locale->getId()})";
//$sql = "SELECT * FROM " . I18nCategories::getTableName(true) . " WHERE `id` IN $set1";
$sql = "SELECT * FROM " . 'pp088_i18n_section' . " WHERE `id` IN {$set1}";
trace(__FILE__, 'find():' . $sql);
// Run!
$rows = DB::executeAll($sql);
// Empty?
if (!is_array($rows) || count($rows) < 1) {
trace(__FILE__, 'find():found 0');
return null;
}
// if
// If we have one load it, else loop and load many
trace(__FILE__, 'find():found ' . count($rows));
$objects = array();
foreach ($rows as $row) {
$object = $this->loadFromRow($row);
if (instance_of($object, $this->getItemClass())) {
$objects[] = $object;
}
// if
}
// foreach
return count($objects) ? $objects : null;
trace(__FILE__, 'getCategories():end');
}
function mail_update_7_8()
{
$sent_mails = MailContents::findAll(array('conditions' => "`state`=3 AND `has_attachments`=1"));
foreach ($sent_mails as $mail) {
if (!$mail instanceof MailContent) {
continue;
}
/* @var $mail MailContent */
$attachments = array();
MailUtilities::parseMail($mail->getContent(), $decoded, $parsedEmail, $warnings);
if (isset($parsedEmail['Attachments'])) {
$attachments = $parsedEmail['Attachments'];
} else {
if ($mail->getHasAttachments() && !in_array($parsedEmail['Type'], array('html', 'text', 'delivery-status')) && isset($parsedEmail['FileName'])) {
// if the email is the attachment
$attachments = array(array('Data' => $parsedEmail['Data'], 'Type' => $parsedEmail['Type'], 'FileName' => $parsedEmail['FileName']));
}
}
foreach ($attachments as $att) {
$file = ProjectFiles::getByFilename($att['FileName']);
/* @var $file ProjectFile */
if ($file instanceof ProjectFile) {
$file->setMailId($mail->getId());
$file->setMarkTimestamps(false);
// dont change updated_on date
$file->save();
$file->addToSharingTable();
}
}
}
DB::executeAll("UPDATE " . TABLE_PREFIX . "objects o INNER JOIN " . TABLE_PREFIX . "project_files f ON f.object_id=o.id\n\t\t\tSET o.updated_by_id=o.created_by_id, o.updated_on=o.created_on\n\t\t\tWHERE f.mail_id>0;");
}
function workspaces_update_8_9()
{
$tag_options = '{"defaultAjax":{"controller":"dashboard", "action": "main_dashboard"},"quickAdd":true,"showInPaths":true,"useLangs":true}';
DB::executeAll("UPDATE " . TABLE_PREFIX . "dimensions SET options='{$tag_options}' WHERE code='tags'");
$dot_options = '{"defaultAjax":{"controller":"dashboard", "action": "main_dashboard"}}';
DB::executeAll("\n\t\t\tUPDATE " . TABLE_PREFIX . "dimension_object_types SET options='{$dot_options}' WHERE \n\t\t\t object_type_id=(SELECT `id` FROM `" . TABLE_PREFIX . "object_types` WHERE `name`='tag') AND\n\t\t\t dimension_id=(SELECT `id` FROM `" . TABLE_PREFIX . "dimensions` WHERE `code`='tags')\n\t\t");
DB::executeAll("\n\t\t\tINSERT INTO " . TABLE_PREFIX . "dimension_object_type_contents (dimension_id,dimension_object_type_id,content_object_type_id,is_required,is_multiple) VALUES\n\t\t\t((SELECT id FROM " . TABLE_PREFIX . "dimensions WHERE code='workspaces'), (SELECT id FROM " . TABLE_PREFIX . "object_types WHERE name='workspace'), (SELECT id FROM " . TABLE_PREFIX . "object_types WHERE name='template_task' LIMIT 1),0,1),\n\t\t\t((SELECT id FROM " . TABLE_PREFIX . "dimensions WHERE code='tags'), (SELECT id FROM " . TABLE_PREFIX . "object_types WHERE name='tag'), (SELECT id FROM " . TABLE_PREFIX . "object_types WHERE name='template_task' LIMIT 1),0,1),\n\t\t\t((SELECT id FROM " . TABLE_PREFIX . "dimensions WHERE code='workspaces'), (SELECT id FROM " . TABLE_PREFIX . "object_types WHERE name='workspace'), (SELECT id FROM " . TABLE_PREFIX . "object_types WHERE name='template_milestone' LIMIT 1),0,1),\n\t\t\t((SELECT id FROM " . TABLE_PREFIX . "dimensions WHERE code='tags'), (SELECT id FROM " . TABLE_PREFIX . "object_types WHERE name='tag'), (SELECT id FROM " . TABLE_PREFIX . "object_types WHERE name='template_milestone' LIMIT 1),0,1)\n\t\t\tON DUPLICATE KEY UPDATE dimension_id=dimension_id;\n\t\t");
}
/**
* Get a list of pages for a project
*
* @param mixed $project
* @return
*/
function getPagesList(Project $project)
{
$sql = 'SELECT p.id, r.name FROM ' . Wiki::instance()->getTableName(true) . ' AS p, ' . Revisions::instance()->getTableName(true) . ' AS r WHERE p.project_id = ' . $project->getId() . ' AND p.id = r.page_id AND r.revision = p.revision AND p.project_sidebar = 0 ORDER BY 2';
$return = array();
foreach ((array) DB::executeAll($sql) as $page) {
$return[] = array('name' => $page['name'], 'view_url' => get_url('wiki', 'view', array('id' => $page['id'])));
}
return $return;
}
static function getOptionsFromCategory($category_name) {
$rows = DB::executeAll("SELECT `name` FROM `".TABLE_PREFIX."config_options` WHERE `category_name` = '$category_name'");
$result = array();
if (is_array($rows)) {
foreach ($rows as $row) {
$result[] = $row['name'];
}
}
return $result;
}
/**
* Returns an array of the custom property ids for a given object type
*
* @param $object_type
* @return array
*/
static function getCustomPropertyIdsByObjectType($object_type)
{
$rows = DB::executeAll("SELECT `id` FROM " . self::instance()->getTableName(true) . " WHERE `object_type` = '" . $object_type . "'");
$result = array();
if (is_array($rows) && count($rows) > 0) {
foreach ($rows as $row) {
$result[] = $row['id'];
}
}
return $result;
}
/**
* Return co type ids for custom property
*
* @param $cp_id
* @return string
*/
function getCoTypesIdsForCpCSV($cp_id) {
$ids ='';
$sql = "SELECT `co_type_id` FROM `".$this->getTableName()."` WHERE `cp_id` = $cp_id";
$rows = DB::executeAll($sql);
if (is_array($rows)) {
foreach ($rows as $r) {
$ids .= ($ids == '' ? '' : ',') . $r['co_type_id'];
}
}
return $ids;
} // getCustomPropertyValue
/**
* Return number of unique objects
*
* @param string $conditions
* @return integer
*/
function countUniqueObjects($conditions) {
$table_name = SearchableObjects::instance()->getTableName(true);
//$tags_table_name = Tags::instance()->getTableName();
$where = '';
if(trim($conditions <> '')) $where = "WHERE $conditions";
$sql = "SELECT count(distinct `rel_object_manager`, `rel_object_id`) AS `count` FROM $table_name $where";
$result = DB::executeAll($sql);
if (!is_array($result) || !count($result)) return 0;
return $result[0]['count'];
} // countUniqueObjects
static function getListableObjectTypeIds()
{
if (is_null(self::$listableObjectTypesIds)) {
$ids = array();
$sql = "\r\n\t\t\t\tSELECT DISTINCT(id) as id \r\n\t\t\t\tFROM " . TABLE_PREFIX . "object_types \r\n\t\t\t\tWHERE type IN ('content_object', 'dimension_object', 'comment') AND (\r\n\t\t\t\t\tplugin_id IS NULL OR \r\n\t\t\t\t\tplugin_id = 0 OR \r\n\t\t\t\t\tplugin_id IN ( \r\n\t\t\t\t\t\tSELECT id FROM " . TABLE_PREFIX . "plugins WHERE is_activated > 0 AND is_installed > 0 \r\n\t\t\t\t\t)\r\n\t\t\t\t)";
$rows = DB::executeAll($sql);
foreach ($rows as $row) {
$ids[array_var($row, 'id')] = array_var($row, 'id');
}
self::$listableObjectTypesIds = $ids;
}
return self::$listableObjectTypesIds;
}
function getPermissionGroupsAllowAll($permission_group_ids)
{
if (is_array($permission_group_ids)) {
$permission_group_ids = implode(",", $permission_group_ids);
}
$rows = DB::executeAll("SELECT permission_group_id FROM " . TABLE_PREFIX . "contact_dimension_permissions WHERE `dimension_id` = " . $this->getId() . " AND `permission_type` = " . DB::escape('allow all') . " AND `permission_group_id` in ({$permission_group_ids})");
$res = array();
if ($rows && is_array($rows)) {
foreach ($rows as $row) {
$res[] = $row['permission_group_id'];
}
}
return $res;
}
function getMaxUID($folder = null)
{
$maxUID = 0;
$sql = "SELECT `uid` FROM `" . MailContents::instance()->getTableName() . "` WHERE `account_id` = " . $this->getId();
if (!is_null($folder)) {
$sql .= " AND `imap_folder_name` = '{$folder}'";
}
$sql .= " AND id = (SELECT max(id) FROM `" . MailContents::instance()->getTableName() . "` WHERE `account_id` = " . $this->getId() . " AND `state` < 2)";
$rows = DB::executeAll($sql);
if (isset($rows)) {
$maxUID = $rows[0]['uid'];
}
return $maxUID;
}
static function getDependenciesForTaskOnlyPendingIds($task_id)
{
$ids = array();
// Build Main SQL
$sql = "\r\n\t\t\tSELECT `task_id` FROM `" . TABLE_PREFIX . "project_task_dependencies` AS ptd\r\n\t\t\tLEFT JOIN `" . TABLE_PREFIX . "project_tasks` AS e ON ptd.`task_id` = e.`object_id`\r\n\t\t\tWHERE `previous_task_id` = " . $task_id . " AND `e`.`completed_on` = " . DB::escape(EMPTY_DATETIME) . " \r\n\t\t\t\t\tAND 0 = (SELECT `trashed_by_id` FROM `" . TABLE_PREFIX . "objects` WHERE `id`=`previous_task_id`)\t\t\t\t\t\t\t\r\n\t \t\r\n\t\t";
// Execute query and build the resultset
$rows = DB::executeAll($sql);
if (count($rows)) {
foreach ($rows as $row) {
$ids[] = $row['task_id'];
}
}
return $ids;
}
/**
* Returns an array with the dimensions to reload foreach member type that belongs to this dimension
*/
function getDimensionsToReloadByObjectType($dimension_id)
{
$sql = "SELECT `associated_dimension_id` as dim_id, `object_type_id` as ot_id FROM `" . TABLE_PREFIX . "dimension_member_associations` WHERE `dimension_id` = {$dimension_id}\r\n\t\t\t\tUNION SELECT `dimension_id` as dim_id, `associated_object_type_id` as ot_id FROM `" . TABLE_PREFIX . "dimension_member_associations` WHERE `associated_dimension_id` = {$dimension_id}";
$rows = DB::executeAll($sql);
$result = array();
if (is_array($rows)) {
foreach ($rows as $row) {
if (!isset($result[$row['ot_id']])) {
$result[$row['ot_id']] = array();
}
$result[$row['ot_id']][] = $row['dim_id'];
}
}
return $result;
}
/**
* Returns array of initials
*
* @param void
* @return array
*/
function getInitials()
{
$sql = "SELECT DISTINCT UPPER(SUBSTRING(`display_name`,1,1)) AS 'initial' FROM `" . TABLE_PREFIX . "contacts` ORDER BY `display_name` ASC";
$rows = DB::executeAll($sql);
$initials = array();
foreach ($rows as $row) {
if (preg_match('/^\\d$/', $row['initial'])) {
if (!in_array('_', $initials)) {
$initials[] = '_';
}
// if
} else {
$initials[] = $row['initial'];
}
// if
}
// foreach
return $initials;
}
static function getContentObjectTypeIds($dimension_id, $dimension_object_type = null)
{
$type_ids = array();
$cond = "";
$key = $dimension_id . "_" . $dimension_object_type;
if (isset(self::$content_ot_cache[$key])) {
return self::$content_ot_cache[$key];
} else {
if ($dimension_object_type != null) {
$cond = ' AND `dimension_object_type_id` = ' . $dimension_object_type;
}
$types = DB::executeAll("SELECT content_object_type_id FROM " . TABLE_PREFIX . "dimension_object_type_contents WHERE `dimension_id` = " . $dimension_id . $cond);
foreach ($types as $type) {
$type_ids[] = $type['content_object_type_id'];
}
self::$content_ot_cache[$key] = array_unique($type_ids);
return self::$content_ot_cache[$key];
}
}
function canAccessObjectTypeinMembersPermissionGroups($permission_group_ids, $member_ids, $object_type_id, $can_write = false, $can_delete = false)
{
if (is_array($permission_group_ids)) {
$permission_group_ids = implode(",", $permission_group_ids);
}
if (is_array($member_ids)) {
$member_ids = implode(",", $member_ids);
}
$can_write_cond = $can_write ? " AND `can_write` = 1" : "";
$can_delete_cond = $can_delete ? " AND `can_delete` = 1" : "";
$sql = "SELECT permission_group_id FROM " . TABLE_PREFIX . "contact_member_permissions WHERE `member_id` IN (" . $member_ids . ") AND `object_type_id` = {$object_type_id} AND `permission_group_id` IN ( {$permission_group_ids} ) {$can_write_cond} {$can_delete_cond}";
$rows = DB::executeAll($sql);
$res = array();
if ($rows && is_array($rows)) {
foreach ($rows as $row) {
$res[] = $row['permission_group_id'];
}
}
return $res;
}
function getColumnValue($column_name, $default = null)
{
if ($this->manager()->columnExists($column_name)) {
return parent::getColumnValue($column_name, $default);
}
$column_exists = false;
$rows = DB::executeAll("DESCRIBE " . $this->manager()->getTableName());
foreach ($rows as $row) {
if ($row['Field'] == $column_name) {
$column_exists = true;
}
}
if ($column_exists) {
$res = DB::executeAll("SELECT {$column_name} FROM " . $this->manager()->getTableName() . " WHERE permission_group_id=" . $this->getPermissionGroupId());
if (count($res) > 0) {
return $res[0][$column_name];
}
}
return false;
}
/**
* Return all projects that this user is part of
*
* @access public
* @param User $user
* @param
* @return array
*/
function getProjectsByUser(User $user, $additional_conditions = null)
{
$projects_table = Projects::instance()->getTableName(true);
$project_users_table = ProjectUsers::instance()->getTableName(true);
$projects = array();
$sql = "SELECT {$projects_table}.* FROM {$projects_table}, {$project_users_table} WHERE ({$projects_table}.`id` = {$project_users_table}.`project_id` AND {$project_users_table}.`user_id` = " . DB::escape($user->getId()) . ')';
if (trim($additional_conditions) != '') {
$sql .= " AND ({$additional_conditions})";
}
// if
$sql .= " ORDER BY {$projects_table}.`name`";
$rows = DB::executeAll($sql);
if (is_array($rows)) {
foreach ($rows as $row) {
$projects[] = Projects::instance()->loadFromRow($row);
}
// foreach
}
// if
return count($projects) ? $projects : null;
}
/**
* Return all projects that this company is member of
*
* @access public
* @param Company $company
* @param string $additional_conditions Additional SQL conditions
* @return array
*/
static function getProjectsByCompany(Company $company, $additional_conditions = null)
{
if ($company->isOwner()) {
return Projects::getAll();
}
$projects_table = Projects::instance()->getTableName(true);
$project_companies_table = ProjectCompanies::instance()->getTableName(true);
$projects = array();
$sql = "SELECT {$projects_table}.* FROM {$projects_table}, {$project_companies_table} WHERE ({$projects_table}.`id` = {$project_companies_table}.`project_id` AND {$project_companies_table}.`company_id` = " . DB::escape($company->getId()) . ')';
if (trim($additional_conditions) != '') {
$sql .= " AND ({$additional_conditions})";
}
$rows = DB::executeAll($sql);
if (is_array($rows)) {
foreach ($rows as $row) {
$projects[] = Projects::instance()->loadFromRow($row);
}
// foreach
}
// if
return count($projects) ? $projects : null;
}
function delete($delete_db_record = true) {
$rows = DB::executeAll("SELECT count(`object_id`) as `c` FROM `".TABLE_PREFIX."mail_contents` WHERE `conversation_id` = " . DB::escape($this->getConversationId()));
if (is_array($rows) && count($rows) > 0) {
if ($rows[0]['c'] < 2) {
// if no other emails in conversation, delete conversation
DB::execute("DELETE FROM `".TABLE_PREFIX."mail_conversations` WHERE `id` = " . DB::escape($this->getCOnversationId()));
}
}
if ($delete_db_record) {
return parent::delete();
} else {
return $this->mark_as_deleted();
}
}
static function findByContext($options = array())
{
// Initialize method result
$result = new stdClass();
$result->total = 0;
$result->objects = array();
// Read arguments and Init Vars
$limit = array_var($options, 'limit');
$offset = array_var($options, 'offset');
$trashed = array_var($options, 'trashed');
$archived = array_var($options, 'archived');
$members = active_context_members(false);
$type_id = self::instance()->getObjectTypeId();
$uid = logged_user()->getId();
if ($limit > 0) {
$limit_sql = "LIMIT " . ($offset ? "{$offset}, " : "") . "{$limit}";
} else {
$limit_sql = '';
}
$member_conditions = count($members) > 0 ? "id IN (SELECT object_id FROM " . TABLE_PREFIX . "object_members WHERE member_id IN (" . implode(',', $members) . "))" : "true";
$trashed_conditions = "AND o.trashed_on " . ($trashed ? ">" : "=") . " 0";
$archived_conditions = "AND o.archived_on " . ($archived ? ">" : "=") . " 0";
$extra_conditions = array_var($options, 'extra_conditions', "");
// Build Main SQL
$template_sql = "\r\n\t \tSELECT <selection> FROM " . TABLE_PREFIX . "objects o\r\n\t \tINNER JOIN " . TABLE_PREFIX . "mail_contents m ON m.object_id = o.id\r\n\t \tWHERE \r\n\t \t\to.id IN ( \r\n\t \t\t\tSELECT object_id FROM " . TABLE_PREFIX . "sharing_table\r\n\t \t\t\tWHERE group_id IN (\r\n\t\t \t\t\tSELECT permission_group_id FROM " . TABLE_PREFIX . "contact_permission_groups WHERE contact_id = {$uid}\r\n\t\t\t\t\t)\r\n\t\t\t\t) \r\n\t\t\t\tAND {$member_conditions}\r\n\t\t\t\tAND o.object_type_id = {$type_id}\r\n\t\t\t\tAND m.is_deleted = 0 {$trashed_conditions} {$archived_conditions} {$extra_conditions}";
$count_sql = str_replace_first("<selection>", "COUNT(distinct(o.id)) as total", $template_sql);
$sql = str_replace_first("<selection>", "distinct(o.id)", $template_sql) . " {$limit_sql}";
// count all emails
$res = DB::execute($count_sql);
$result->total = array_var($res->fetchRow(), 'total');
if ($result->total == 0) {
return $result;
}
// Execute query and build the resultset
$rows = DB::executeAll($sql);
$mail_ids = array();
foreach ($rows as $row) {
$mail_ids[] = $row['id'];
}
$result->objects = MailContents::findAll(array("conditions" => "object_id IN (" . implode(",", $mail_ids) . ")", "order" => array_var($options, 'order')));
return $result;
}
/**
* Returns true if this user can see $company. Members of owener company and
* coworkers are visible without project check! Also, members of owner company
* can see all clients without any prior check!
*
* @param Company $company
* @return boolean
*/
function canSeeCompany(Company $company) {
if ($this->isMemberOfOwnerCompany()) {
return true;
} // if
if ($company->isOwner()) {
$this->visible_companies[$company->getId()] = true;
return true;
} // if
if (isset($this->visible_companies[$company->getId()])) {
return $this->visible_companies[$company->getId()];
} // if
if ($this->getCompanyId() == $company->getId()) {
$this->visible_companies[$company->getId()] = true;
return true;
} // if
// Lets company projects for company of this user and for $company and
// compare if we have projects where both companies work together
$projects_1 = DB::executeAll("SELECT `project_id` FROM " . ProjectCompanies::instance()->getTableName(true) . " WHERE `company_id` = ?", $this->getCompanyId());
$projects_2 = DB::executeAll("SELECT `project_id` FROM " . ProjectCompanies::instance()->getTableName(true) . " WHERE `company_id` = ?", $company->getId());
if (!is_array($projects_1) || !is_array($projects_2)) {
$this->visible_companies[$company->getId()] = false;
return false;
} // if
foreach ($projects_1 as $project_id) {
if (in_array($project_id, $projects_2)) {
$this->visible_companies[$company->getId()] = true;
return true;
} // if
} // foreach
$this->visible_companies[$company->getId()] = false;
return false;
} // canSeeCompany
/**
* Return tag names as array for project file id
*
* @access public
* @param int $fileId
* @return array
*/
function getTagNamesByFileId($fileId)
{
$rows = DB::executeAll('SELECT `tag` FROM ' . self::instance()->getTableName(true) . ' WHERE `rel_object_id` = ? AND `rel_object_manager` =\'ProjectFiles\' ORDER BY `tag`', $fileId);
if (!is_array($rows)) {
return null;
}
$tags = array();
foreach ($rows as $row) {
$tags[] = $row['tag'];
}
return $tags;
}
function countUserInboxUnreadEmails()
{
$tp = TABLE_PREFIX;
$uid = logged_user()->getId();
$sql = "SELECT count(*) `c` FROM `{$tp}mail_contents` `a`, `{$tp}read_objects` `b` WHERE `b`.`rel_object_manager` = 'MailContents' AND `b`.`rel_object_id` = `a`.`id` AND `b`.`user_id` = '{$uid}' AND `b`.`is_read` = '1' AND `a`.`trashed_on` = '0000-00-00 00:00:00' AND `a`.`is_deleted` = 0 AND `a`.`archived_by_id` = 0 AND (`a`.`state` = '0' OR `a`.`state` = '5') AND " . permissions_sql_for_listings(MailContents::instance(), ACCESS_LEVEL_READ, logged_user(), null, '`a`');
$rows = DB::executeAll($sql);
$read = $rows[0]['c'];
$sql = "SELECT count(*) `c` FROM `{$tp}mail_contents` `a` WHERE `a`.`trashed_on` = '0000-00-00 00:00:00' AND `a`.`is_deleted` = 0 AND `a`.`archived_by_id` = 0 AND (`a`.`state` = '0' OR `a`.`state` = '5') AND " . permissions_sql_for_listings(MailContents::instance(), ACCESS_LEVEL_READ, logged_user(), null, '`a`');
$rows = DB::executeAll($sql);
$all = $rows[0]['c'];
return $all - $read;
}
function get_companies_json() {
$data = array();
$check_permissions = array_var($_REQUEST, 'check_p');
$allow_none = array_var($_REQUEST, 'allow_none', true);
if (!$check_permissions) {
$comp_rows = DB::executeAll("SELECT c.object_id, c.first_name FROM ".TABLE_PREFIX."contacts c INNER JOIN ".TABLE_PREFIX."objects o ON o.id=c.object_id
WHERE c.is_company = 1 AND o.trashed_by_id = 0 AND o.archived_by_id = 0 ORDER BY c.first_name ASC");
} else {
$companies = Contacts::getVisibleCompanies(logged_user(), "`id` <> " . owner_company()->getId());
if (logged_user()->isMemberOfOwnerCompany() || owner_company()->canAddUser(logged_user())) {
// add the owner company
$companies = array_merge(array(owner_company()), $companies);
}
}
if ($allow_none) {
$data[] = array('id' => 0, 'name' => lang('none'));
}
if (isset($comp_rows)) {
foreach ($comp_rows as $row) {
$data[] = array('id' => $row['object_id'], 'name' => $row['first_name']);
}
} else if (isset($companies)) {
foreach ($companies as $company) {
$data[] = array('id' => $company->getId(), 'name' => $company->getObjectName());
}
}
$this->setAutoRender(false);
echo json_encode($data);
ajx_current("empty");
}
function delete()
{
if (!can_manage_dimension_members(logged_user())) {
flash_error(lang('no access permissions'));
ajx_current("empty");
return;
}
$member = Members::findById(get_id());
try {
DB::beginWork();
if (!$member->canBeDeleted($error_message)) {
throw new Exception($error_message);
}
$dim_id = $member->getDimensionId();
// Remove from shring table
SharingTables::instance()->delete(" \n\t\t\t\tobject_id IN (\n \t\t\t\t SELECT distinct(object_id) FROM " . TABLE_PREFIX . "object_members WHERE member_id = " . get_id() . " AND is_optimization = 0\n\t\t\t\t)\n\t\t\t");
$affectedObjectsRows = DB::executeAll("SELECT distinct(object_id) AS object_id FROM " . TABLE_PREFIX . "object_members where member_id = " . get_id() . " AND is_optimization = 0");
if (is_array($affectedObjectsRows) && count($affectedObjectsRows) > 0) {
foreach ($affectedObjectsRows as $row) {
$oid = $row['object_id'];
$object = Objects::findObject($row['object_id']);
// return an instance of Message, contact, etc.
/* @var $object ContentDataObject */
if ($object) {
if ($object instanceof ContentDataObject) {
$object->addToSharingTable();
}
}
}
}
$args = $member;
Hook::fire('delete_member', $args, $ret);
// ApplicationLogs::createLog($member, ApplicationLogs::ACTION_DELETE, false, true);
$ok = $member->delete();
if ($ok) {
evt_add("reload dimension tree", $dim_id);
}
DB::commit();
flash_success(lang('success delete member', $member->getName()));
if (get_id('start')) {
ajx_current("start");
} else {
if (get_id('dont_reload')) {
ajx_current("empty");
} else {
ajx_current("reload");
}
}
} catch (Exception $e) {
DB::rollback();
flash_error($e->getMessage());
ajx_current("empty");
}
}
static function addObjToSharingTable($oid, $tid, $obj_mem_ids)
{
$gids = array();
$table_prefix = defined('FORCED_TABLE_PREFIX') && FORCED_TABLE_PREFIX ? FORCED_TABLE_PREFIX : TABLE_PREFIX;
//1. clear sharing table for this object
SharingTables::delete("object_id={$oid}");
//2. get dimensions of this object's members that defines permissions
$res = DB::execute("SELECT d.id as did FROM " . $table_prefix . "dimensions d INNER JOIN " . $table_prefix . "members m on m.dimension_id=d.id\r\n\t\t\t\tWHERE m.id IN ( SELECT member_id FROM " . $table_prefix . "object_members WHERE object_id = {$oid} AND is_optimization = 0 ) AND d.defines_permissions = 1");
$dids_tmp = array();
while ($row = $res->fetchRow()) {
$dids_tmp[$row['did']] = $row['did'];
}
$res->free();
$dids = array_values($dids_tmp);
$dids_tmp = null;
$sql_from = "" . $table_prefix . "contact_member_permissions cmp\r\n\t\tLEFT JOIN " . $table_prefix . "members m ON m.id = cmp.member_id\r\n\t\tLEFT JOIN " . $table_prefix . "dimensions d ON d.id = m.dimension_id";
$member_where_conditions = "";
$dim_where_conditions = "";
// if users can add objects without classifying then check for permissions with member_id=0
if (config_option('let_users_create_objects_in_root')) {
$member_where_conditions = "member_id=0 OR ";
$dim_where_conditions = " OR d.id IS NULL";
}
$sql_where = "({$member_where_conditions} member_id IN ( SELECT member_id FROM " . $table_prefix . "object_members WHERE object_id = {$oid} AND is_optimization = 0)) AND cmp.object_type_id = {$tid}";
//3. If there are dimensions that defines permissions containing any of the object members
if (count($dids)) {
// 3.1 get permission groups with permissions over the object.
$sql_fields = "permission_group_id AS group_id";
$sql = "\r\n\t\t\t\tSELECT\r\n\t\t\t\t{$sql_fields}\r\n\t\t\t\tFROM\r\n\t\t\t\t{$sql_from}\r\n\t\t\t\tWHERE\r\n\t\t\t\t{$sql_where} AND (d.id IN (" . implode(',', $dids) . ") {$dim_where_conditions})\r\n\t\t\t";
$res = DB::execute($sql);
$gids_tmp = array();
while ($row = $res->fetchRow()) {
$gids_tmp[$row['group_id']] = $row['group_id'];
}
$res->free();
// allow all permission groups
$allow_all_rows = DB::executeAll("SELECT DISTINCT permission_group_id FROM " . $table_prefix . "contact_dimension_permissions cdp\r\n\t\t\t\t\tINNER JOIN " . $table_prefix . "members m on m.dimension_id=cdp.dimension_id\r\n\t\t\t\t\tWHERE cdp.permission_type='allow all' AND cdp.dimension_id IN (" . implode(',', $dids) . ");");
if (is_array($allow_all_rows)) {
foreach ($allow_all_rows as $row) {
$gids_tmp[$row['permission_group_id']] = $row['permission_group_id'];
}
}
$gids = array_values($gids_tmp);
$gids_tmp = null;
// check for mandatory dimensions
$enabled_dimensions_sql = "";
$enabled_dimensions_ids = implode(',', config_option('enabled_dimensions'));
if ($enabled_dimensions_ids != "") {
$enabled_dimensions_sql = "AND id IN ({$enabled_dimensions_ids})";
}
$mandatory_dim_ids = Dimensions::findAll(array('id' => true, 'conditions' => "`defines_permissions`=1 {$enabled_dimensions_sql} AND `permission_query_method`='" . DIMENSION_PERMISSION_QUERY_METHOD_MANDATORY . "'"));
if (count($gids) > 0 && count($mandatory_dim_ids) > 0) {
$sql = "SELECT om.member_id, m.dimension_id FROM " . $table_prefix . "object_members om\r\n\t\t\t\t\tINNER JOIN " . $table_prefix . "members m ON m.id=om.member_id INNER JOIN " . $table_prefix . "dimensions d ON d.id=m.dimension_id\r\n\t\t\t\t\tWHERE om.object_id = {$oid} AND om.is_optimization = 0 AND d.id IN (" . implode(",", $mandatory_dim_ids) . ")";
// Object members in mandatory dimensions
$object_member_ids_res = DB::executeAll($sql);
$mandatory_dim_members = array();
if (!is_null($object_member_ids_res)) {
foreach ($object_member_ids_res as $row) {
if (!isset($mandatory_dim_members[$row['dimension_id']])) {
$mandatory_dim_members[$row['dimension_id']] = array();
}
$mandatory_dim_members[$row['dimension_id']][] = $row['member_id'];
}
$mandatory_dim_allowed_pgs = array();
// Check foreach group that it has permissions over at least one member of each mandatory dimension
foreach ($mandatory_dim_members as $mdim_id => $mmember_ids) {
$sql = "SELECT pg.id FROM " . $table_prefix . "permission_groups pg\r\n\t\t\t\t\t\t\tINNER JOIN " . $table_prefix . "contact_dimension_permissions cdp ON cdp.permission_group_id=pg.id\r\n\t\t\t\t\t\t\tINNER JOIN " . $table_prefix . "contact_member_permissions cmp ON cmp.permission_group_id=pg.id\r\n\t\t\t\t\t\t\tWHERE cdp.dimension_id = '{$mdim_id}' AND (\r\n\t\t\t\t\t\t\tcdp.permission_type='allow all' OR cdp.permission_type='check' AND cmp.permission_group_id IN (" . implode(',', $gids) . ")\r\n\t\t\t\t\t\t\tAND cmp.member_id IN (" . implode(',', $mmember_ids) . ")\r\n\t\t\t\t\t\t)";
$permission_groups_res = DB::executeAll($sql);
$mandatory_dim_allowed_pgs[$mdim_id] = array();
if (!is_null($permission_groups_res)) {
foreach ($permission_groups_res as $row) {
if (!in_array($row['id'], $mandatory_dim_allowed_pgs[$mdim_id])) {
$mandatory_dim_allowed_pgs[$mdim_id][] = $row['id'];
}
}
}
}
if (isset($mandatory_dim_allowed_pgs) && count($mandatory_dim_allowed_pgs) > 0) {
$original_mandatory_dim_allowed_pgs = $mandatory_dim_allowed_pgs;
$allowed_gids = array_pop($mandatory_dim_allowed_pgs);
foreach ($mandatory_dim_allowed_pgs as $pg_array) {
$allowed_gids = array_intersect($allowed_gids, $pg_array);
}
// If an user has permissions in one dim using a group and in other dim using his personal permissions then add to sharing table its personal permission group
$pg_ids = array_unique(array_flat($original_mandatory_dim_allowed_pgs));
if (count($pg_ids) == 0) {
$pg_ids[0] = 0;
}
$contact_pgs = array();
$contact_pg_rows = DB::executeAll("SELECT * FROM " . TABLE_PREFIX . "contact_permission_groups WHERE permission_group_id IN (" . implode(',', $pg_ids) . ") ORDER BY permission_group_id");
if (is_array($contact_pg_rows)) {
foreach ($contact_pg_rows as $cpgr) {
if (!isset($contact_pgs[$cpgr['contact_id']])) {
$contact_pgs[$cpgr['contact_id']] = array();
}
$contact_pgs[$cpgr['contact_id']][] = $cpgr['permission_group_id'];
}
}
// each user must have at least one pg for every dimension
foreach ($contact_pgs as $contact_id => $permission_groups) {
$has_one = array_flip(array_keys($original_mandatory_dim_allowed_pgs));
foreach ($has_one as $k => &$v) {
$v = false;
}
foreach ($permission_groups as $pg_id) {
foreach ($original_mandatory_dim_allowed_pgs as $dim_id => $allowedpgs) {
if (in_array($pg_id, $allowedpgs)) {
$has_one[$dim_id] = true;
break;
}
}
}
// all dims must be true in this array to allow permissions
$has_permission = !in_array(false, $has_one);
if ($has_permission) {
$contact_row = DB::executeOne("SELECT permission_group_id FROM " . TABLE_PREFIX . "contacts where object_id = {$contact_id}");
if (is_array($contact_row) && $contact_row['permission_group_id'] > 0) {
$allowed_gids[] = $contact_row['permission_group_id'];
}
}
}
$gids = array_unique($allowed_gids, SORT_NUMERIC);
} else {
$gids = array();
}
}
}
} else {
if ($obj_mem_ids) {
// 3.2 No memeber dimensions defines permissions.
// No esta en ninguna dimension que defina permisos, El objecto esta en algun lado
// => En todas las dimensiones en la que está no definen permisos => Busco todos los grupos
$gids = PermissionGroups::instance()->findAll(array('id' => true, 'conditions' => "type != 'roles'"));
} else {
// if this object is an email and it is unclassified => add to sharing table the permission groups of the users that have permissions in the email's account
if (Plugins::instance()->isActivePlugin('mail')) {
$mail_ot = ObjectTypes::instance()->findByName('mail');
if ($mail_ot instanceof ObjectType && $tid == $mail_ot->getId()) {
$gids = array_flat(DB::executeAll("\r\n\t\t\t\t\t\t\tSELECT cpg.permission_group_id\r\n\t\t\t\t\t\t\tFROM " . TABLE_PREFIX . "contact_permission_groups cpg\r\n\t\t\t\t\t\t\tINNER JOIN " . TABLE_PREFIX . "contacts c ON c.permission_group_id=cpg.permission_group_id\r\n\t\t\t\t\t\t\tWHERE cpg.contact_id IN (\r\n\t\t\t\t\t\t\t SELECT mac.contact_id FROM " . TABLE_PREFIX . "mail_account_contacts mac WHERE mac.account_id = (SELECT mc.account_id FROM " . TABLE_PREFIX . "mail_contents mc WHERE mc.object_id={$oid})\r\n\t\t\t\t\t\t\t);\r\n\t\t\t\t\t\t"));
}
}
}
}
if (count($gids)) {
$stManager = SharingTables::instance();
$stManager->populateGroups($gids, $oid);
$gids = null;
}
}
static function getMilestonesInfo($mid)
{
if (self::$info_cache == null) {
self::$info_cache = array();
// completed
$rows = DB::executeAll("select count(object_id) as row_count, milestone_id from " . TABLE_PREFIX . "project_tasks use index (completed_on) where completed_on > '0000-00-00' group by milestone_id;");
if (is_array($rows)) {
foreach ($rows as $row) {
if (array_var($row, 'milestone_id') > 0) {
if (!isset(self::$info_cache[$row['milestone_id']])) {
self::$info_cache[$row['milestone_id']] = array();
}
self::$info_cache[$row['milestone_id']]['tc'] = array_var($row, 'row_count');
}
}
}
// all milestone tasks
$rows = DB::executeAll("select count(object_id) as row_count, milestone_id from " . TABLE_PREFIX . "project_tasks use index (milestone_id) group by milestone_id;");
if (is_array($rows)) {
foreach ($rows as $row) {
if (array_var($row, 'milestone_id') > 0) {
if (!isset(self::$info_cache[$row['milestone_id']])) {
self::$info_cache[$row['milestone_id']] = array();
}
self::$info_cache[$row['milestone_id']]['tnum'] = array_var($row, 'row_count');
}
}
}
}
return array_var(self::$info_cache, $mid);
}
