/**
* Return all companies that are on specific projects, determined by a CVS list of project ids.
*
* @access public
* @param string $projects_csv CSV list of projects
* @param string $additional_conditions Additional SQL conditions
* @param bool $include_owner Include the owner company
* @return array Array of Companies
*/
static function getCompaniesByProjects($projects_csv, $additional_conditions = null, $include_owner = true)
{
$companies = array();
$companies_table = self::instance()->getTableName(true);
$project_objects_table = WorkspaceObjects::instance()->getTableName(true);
// Restrict result only on owner company
$ownerCond = '';
if (!$include_owner) {
$owner_id = owner_company()->getId();
$ownerCond = "{$companies_table}.`client_of_id` = '{$owner_id}' AND ";
}
$wsCond = self::getWorkspaceString($projects_csv);
$conditions = $ownerCond != '' ? "{$ownerCond} AND {$wsCond}" : $wsCond;
if (trim($additional_conditions) != '') {
$conditions .= " AND ({$additional_conditions})";
}
return self::findAll(array('conditions' => $conditions, 'order' => '`name`'));
}
/**
* Returns a list of emails according to the requested parameters
*
* @param string $action
* @param string $tag
* @param array $attributes
* @param Project $project
* @return array
*/
private function getEmails($tag, $attributes, $project = null, $start = null, $limit = null, $order_by = 'sent_date', $dir = 'ASC', &$totalCount = 0)
{
// Return if no emails should be displayed
if (!isset($attributes["viewType"]) || $attributes["viewType"] != "all" && $attributes["viewType"] != "emails") {
return null;
}
$account = array_var($attributes, "accountId");
$classif_filter = array_var($attributes, 'classifType');
$read_filter = array_var($attributes, 'readType');
set_user_config_option('mails account filter', $account, logged_user()->getId());
set_user_config_option('mails classification filter', $classif_filter, logged_user()->getId());
set_user_config_option('mails read filter', $read_filter, logged_user()->getId());
$state = array_var($attributes, 'stateType');
list($objects, $pagination) = MailContents::getEmails($tag, $account, $state, $read_filter, $classif_filter, $project, $start, $limit, $order_by, $dir);
$totalCount = $pagination->getTotalItems();
//if standed in "All" check if all workspaces related to the email have been archived.. and if so, dont show them
if (active_project() == null) {
$aux = array();
foreach ($objects as $mail) {
$check = WorkspaceObjects::getWorkspacesByObject('MailContents', $mail->getId());
$archived = true;
foreach ($check as $wsobject) {
$ws = Projects::findById($wsobject->getId());
if ($ws->getCompletedById() != '0') {
continue;
}
$archived = false;
break;
}
if (!$archived || $check == null) {
$aux[] = $mail;
}
}
return $aux;
}
return $objects;
}
/**
* Enter description here...
* assumes manager has one field as PK
*
* @param DataManager $manager
* @param $access_level ACCESS_LEVEL_XX objects that defines which permission is being checked
* @param string $project_id string that will be compared to the project id while searching project_user table
* @param int $user_id user whose permissions are being checked
* @return unknown
*/
function permissions_sql_for_listings(DataManager $manager, $access_level, User $user, $project_id = '`project_id`', $table_alias = null)
{
if (!$manager instanceof DataManager) {
throw new Exception("Invalid manager '{$manager}' in permissions helper", -1);
return '';
}
$user_id = $user->getId();
$oup_tablename = ObjectUserPermissions::instance()->getTableName(true);
$wo_tablename = WorkspaceObjects::instance()->getTableName(true);
$users_table_name = Users::instance()->getTableName(true);
$pu_table_name = ProjectUsers::instance()->getTableName(true);
if ($user->isGuest() && $access_level == ACCESS_LEVEL_WRITE) {
return 'false';
}
if (isset($table_alias) && $table_alias && $table_alias != '') {
$object_table_name = $table_alias;
} else {
$object_table_name = $manager->getTableName();
}
if (!is_numeric($project_id)) {
$project_id = "{$object_table_name}.{$project_id}";
}
$object_id_field = $manager->getPkColumns();
$object_id = $object_table_name . '.' . $object_id_field;
$object_manager = get_class($manager);
$access_level_text = access_level_field_name($access_level);
$item_class = $manager->getItemClass();
$is_project_data_object = new $item_class() instanceof ProjectDataObject;
// permissions for contacts
if ($manager instanceof Contacts && can_manage_contacts($user)) {
return 'true';
}
if ($manager instanceof Companies && can_manage_contacts($user)) {
return 'true';
}
// permissions for file revisions
if ($manager instanceof ProjectFileRevisions) {
$pfTableName = "`" . TABLE_PREFIX . "project_files`";
return "{$object_table_name}.`file_id` IN (SELECT `id` FROM {$pfTableName} WHERE " . permissions_sql_for_listings(ProjectFiles::instance(), $access_level, $user) . ")";
}
// permissions for projects
if ($manager instanceof Projects) {
$pcTableName = "`" . TABLE_PREFIX . 'project_users`';
return "{$object_table_name}.`id` IN (SELECT `project_id` FROM {$pcTableName} `pc` WHERE `user_id` = {$user_id})";
}
// permissions for users
if ($manager instanceof Users) {
if (logged_user()->isMemberOfOwnerCompany()) {
return "true";
} else {
return "{$object_table_name}.`company_id` = " . owner_company()->getId() . " OR {$object_table_name}.`company_id` = " . logged_user()->getCompanyId();
}
}
$can_manage_object = manager_class_field_name($object_manager, $access_level);
// user is creator
$str = " ( `created_by_id` = {$user_id}) ";
// element belongs to personal project
/*if($is_project_data_object) // TODO: type of element belongs to a project
if (!in_array('project_id', $manager->getColumns())) {
$str .= "\n OR ( EXISTS(SELECT * FROM $users_table_name `xx_u`, $wo_tablename `xx_wo`
WHERE `xx_u`.`id` = $user_id
AND `xx_u`.`personal_project_id` = `xx_wo`.`workspace_id`
AND `xx_wo`.`object_id` = $object_id
AND `xx_wo`.`object_manager` = '$object_manager' )) ";
} else {
$str .= "\n OR ( $project_id = (SELECT `personal_project_id` FROM $users_table_name `xx_u` WHERE `xx_u`.`id` = $user_id)) ";
}
*/
// user or group has specific permissions over object
$group_ids = $user->getGroupsCSV();
$all_ids = '(' . $user_id . ($group_ids != '' ? ',' . $group_ids : '') . ')';
$str .= "\n OR ( EXISTS ( SELECT * FROM {$oup_tablename} `xx_oup` \n\t\t\t\tWHERE `xx_oup`.`rel_object_id` = {$object_id} \n\t\t\t\t\tAND `xx_oup`.`rel_object_manager` = '{$object_manager}' \n\t\t\t\t\tAND `xx_oup`.`user_id` IN {$all_ids} \n\t\t\t\t\tAND `xx_oup`.{$access_level_text} = true) )";
if ($is_project_data_object) {
// TODO: type of element belongs to a project
if (!in_array('project_id', $manager->getColumns())) {
$str .= "\n OR ( EXISTS ( SELECT * FROM {$pu_table_name} `xx_pu`, {$wo_tablename} `xx_wo` \n\t\t\t\tWHERE `xx_pu`.`user_id` IN {$all_ids} \n\t\t\t\t\tAND `xx_pu`.`project_id` = `xx_wo`.`workspace_id`\n\t\t\t\t\tAND `xx_wo`.`object_id` = {$object_id} \n\t\t\t\t\tAND `xx_wo`.`object_manager` = '{$object_manager}'\n\t\t\t\t\tAND `xx_pu`.{$can_manage_object} = true ) ) ";
} else {
$str .= "\n OR ( EXISTS ( SELECT * FROM {$pu_table_name} `xx_pu` \n\t\t\t\tWHERE `xx_pu`.`user_id` IN {$all_ids} \n\t\t\t\t\tAND `xx_pu`.`project_id` = {$project_id} \n\t\t\t\t\tAND `xx_pu`.{$can_manage_object} = true ) ) ";
}
}
// check account permissions in case of emails
if ($manager instanceof MailContents) {
$maccTableName = MailAccountUsers::instance()->getTableName(true);
$str .= "\n OR EXISTS(SELECT `id` FROM {$maccTableName} WHERE `account_id` = {$object_table_name}.`account_id` AND `user_id` = {$user_id})";
if (user_config_option('view deleted accounts emails', null, $user_id)) {
$str .= "\n OR ((SELECT count(*) FROM `" . TABLE_PREFIX . "mail_accounts` WHERE `id` = {$object_table_name}.`account_id`) = 0) AND `created_by_id` = {$user_id}";
}
}
$hookargs = array('manager' => $manager, 'access_level' => $access_level, 'user' => $user, 'project_id' => $project_id, 'table_alias' => $table_alias);
Hook::fire('permissions_sql', $hookargs, $str);
return ' (' . $str . ') ';
}
/**
* Return manager instance
*
* @access protected
* @param void
* @return WorkspaceObjects
*/
function manager()
{
if (!$this->manager instanceof WorkspaceObjects) {
$this->manager = WorkspaceObjects::instance();
}
return $this->manager;
}
/**
* Moves the tasks that do not comply with the following rule: Tasks of a milestone must belong to its workspace or any of its subworkspaces.
*
* @param Project $newWorkspace The new workspace
* @return unknown_type
*/
function move_inconsistent_tasks(Project $newWorkspace)
{
$oldWorkspace = $this->getProject();
$nwCSV = explode(',', $newWorkspace->getAllSubWorkspacesCSV(true));
$owCSV = explode(',', $oldWorkspace->getAllSubWorkspacesCSV(true));
$inconsistentWs = array();
foreach ($owCSV as $ow) {
$found = false;
foreach ($nwCSV as $nw) {
if ($ow == $nw) {
$found = true;
break;
}
}
if (!$found) {
$inconsistentWs[] = $ow;
}
}
if (count($inconsistentWs) > 0) {
try {
DB::execute('UPDATE ' . WorkspaceObjects::instance()->getTableName(true) . ' SET workspace_id = ' . $newWorkspace->getId() . ' WHERE object_manager = \'ProjectTasks\' and object_id in (SELECT id from ' . ProjectTasks::instance()->getTableName(true) . ' WHERE milestone_id = ' . $this->getId() . ') and workspace_id in (' . implode(',', $inconsistentWs) . ')');
} catch (Exception $e) {
throw $e;
}
// try
}
}
function clearWorkspaces()
{
return WorkspaceObjects::delete(array("`object_manager` = ? AND `object_id` = ?", $this->getObjectManagerName(), $this->getId()));
}
$dontshow = true;
break;
}
}
}
if ($dontshow) continue; // to prevent showing the linked objects two times
$linked_object_actions[] = array('action' => $act->getAction(), 'source' => $tmp_id, 'dest' => $act->getLogData());
$activity_data = $act->getActivityData();
$act_data = array('avatar' => $avatar_url, 'date' => $date, 'act_data' => $activity_data);
if ($act->getRelObjectManager() != 'Comments') {
$obj_wss = WorkspaceObjects::getWorkspacesByObject($act->getRelObjectManager(), $act->getRelObjectId());
} else {
$obj_wss = WorkspaceObjects::getWorkspacesByObject(get_class($object->getObject()->manager()), $object->getObject()->getId());
}
$object_ws = null;
$break = false;
foreach ($obj_wss as $obj_ws) {
if (in_array($obj_ws->getId(), $sub_wss_csv)) {
$object_ws = $obj_ws;
$break = true;
} else {
$parent = $obj_ws->getParentWorkspace();
while ($parent) {
if (in_array($parent, $sub_wss)) {
$object_ws = $parent;
$break = true;
break;
/**
* This function will return paginated result. Result is an array where first element is
* array of returned object and second populated pagination object that can be used for
* obtaining and rendering pagination data using various helpers.
*
* Items and pagination array vars are indexed with 0 for items and 1 for pagination
* because you can't use associative indexing with list() construct
*
* @access public
* @param array $arguments Query argumens (@see find()) Limit and offset are ignored!
* @param integer $items_per_page Number of items per page
* @param integer $current_page Current page number
* @return array
*/
function paginate($arguments = null, $items_per_page = 10, $current_page = 1)
{
if (isset($this) && instance_of($this, 'WorkspaceObjects')) {
return parent::paginate($arguments, $items_per_page, $current_page);
} else {
return WorkspaceObjects::instance()->paginate($arguments, $items_per_page, $current_page);
}
// if
}
/**
* Execute a report and return results
*
* @param $id
* @param $params
*
* @return array
*/
static function executeReport($id, $params, $order_by_col = '', $order_by_asc = true, $offset = 0, $limit = 50, $to_print = false)
{
$results = array();
$report = self::getReport($id);
if ($report instanceof Report) {
$conditionsFields = ReportConditions::getAllReportConditionsForFields($id);
$conditionsCp = ReportConditions::getAllReportConditionsForCustomProperties($id);
$table = '';
$object = null;
$controller = '';
$view = '';
eval('$managerInstance = ' . $report->getObjectType() . "::instance();");
if ($report->getObjectType() == 'Companies') {
$table = 'companies';
$controller = 'company';
$view = 'card';
$object = new Company();
} else {
if ($report->getObjectType() == 'Contacts') {
$table = 'contacts';
$controller = 'contact';
$view = 'card';
$object = new Contact();
} else {
if ($report->getObjectType() == 'MailContents') {
$table = 'mail_contents';
$controller = 'mail';
$view = 'view';
$object = new MailContent();
} else {
if ($report->getObjectType() == 'ProjectEvents') {
$table = 'project_events';
$controller = 'event';
$view = 'viewevent';
$object = new ProjectEvent();
} else {
if ($report->getObjectType() == 'ProjectFiles') {
$table = 'project_files';
$controller = 'files';
$view = 'file_details';
$object = new ProjectFile();
} else {
if ($report->getObjectType() == 'ProjectMilestones') {
$table = 'project_milestones';
$controller = 'milestone';
$view = 'view';
$object = new ProjectMilestone();
} else {
if ($report->getObjectType() == 'ProjectMessages') {
$table = 'project_messages';
$controller = 'message';
$view = 'view';
$object = new ProjectMessage();
} else {
if ($report->getObjectType() == 'ProjectTasks') {
$table = 'project_tasks';
$controller = 'task';
$view = 'view_task';
$object = new ProjectTask();
} else {
if ($report->getObjectType() == 'Users') {
$table = 'users';
$controller = 'user';
$view = 'card';
$object = new User();
} else {
if ($report->getObjectType() == 'ProjectWebpages') {
$table = 'project_webpages';
$controller = 'webpage';
$view = 'view';
$object = new ProjectWebpage();
} else {
if ($report->getObjectType() == 'Projects') {
$table = 'projects';
$controller = 'project';
$view = '';
$object = new Project();
}
}
}
}
}
}
}
}
}
}
}
$order_by = '';
if (is_object($params)) {
$params = get_object_vars($params);
}
$sql = 'SELECT id FROM ' . TABLE_PREFIX . $table . ' t WHERE ';
$manager = $report->getObjectType();
$allConditions = permissions_sql_for_listings(new $manager(), ACCESS_LEVEL_READ, logged_user(), 'project_id', 't');
if (count($conditionsFields) > 0) {
foreach ($conditionsFields as $condField) {
if ($condField->getFieldName() == 'workspace' || $condField->getFieldName() == 'tag') {
//if has a tag or workspace condition
if ($condField->getFieldName() == 'workspace') {
//if is a workspace condition:
$fiterUsingWorkspace = true;
if ($condField->getIsParametrizable() && isset($params['workspace'])) {
//if is parameter condition and is set the parameter
$ws_value = $params['workspace'];
} else {
//if is a fixed workspace value and is set
$val = $condField->getValue();
if (isset($val)) {
$ws_value = $val;
} else {
//if there is no workspace to filter with it doesnt filter at all.
$fiterUsingWorkspace = false;
}
}
$wsCondition = $condField->getCondition();
if ($fiterUsingWorkspace && $ws_value != 0) {
$parentWS = Projects::findById($ws_value);
if ($parentWS instanceof Project) {
$subWorkspaces = $parentWS->getSubWorkspaces();
foreach ($subWorkspaces as $subWS) {
$ws_value .= ',' . $subWS->getId();
}
}
$allConditions .= ' AND t.id ' . ($wsCondition == '=' ? 'IN' : 'NOT IN') . ' (SELECT object_id FROM ' . TABLE_PREFIX . 'workspace_objects WHERE object_manager = \'' . $manager . '\' AND workspace_id IN ( ' . $ws_value . '))';
}
}
if ($condField->getFieldName() == 'tag') {
//if is a tag condition:
$fiterUsingTag = true;
if ($condField->getIsParametrizable() && isset($params['tag'])) {
//if is parameter condition and is set the parameter
$tags_csv = $params['tag'];
$tags = explode(',', $tags_csv);
} else {
//if is a fixed tag value and is set
$tval = $condField->getValue();
if (isset($tval)) {
$tags = explode(',', $tval);
} else {
//if there is no tag to filter with it doesnt filter at all.
$fiterUsingTag = false;
}
}
$tagCondition = $condField->getCondition();
if ($fiterUsingTag && is_array($tags)) {
foreach ($tags as $tag_value) {
$tag_value = trim($tag_value);
if ($tag_value == '') {
continue;
}
$allConditions .= ' AND t.id ' . ($tagCondition == '=' ? 'IN' : 'NOT IN') . ' (SELECT rel_object_id FROM ' . TABLE_PREFIX . 'tags WHERE rel_object_manager = \'' . $manager . '\' AND tag = \'' . $tag_value . '\')';
}
}
}
} else {
$skip_condition = false;
$model = $report->getObjectType();
$model_instance = new $model();
$col_type = $model_instance->getColumnType($condField->getFieldName());
$allConditions .= ' AND ';
$dateFormat = 'm/d/Y';
if (isset($params[$condField->getId()])) {
$value = $params[$condField->getId()];
if ($col_type == DATA_TYPE_DATE || $col_type == DATA_TYPE_DATETIME) {
$dateFormat = user_config_option('date_format');
}
} else {
$value = $condField->getValue();
}
if ($value == '' && $condField->getIsParametrizable()) {
$skip_condition = true;
}
if (!$skip_condition) {
if ($condField->getCondition() == 'like' || $condField->getCondition() == 'not like') {
$value = '%' . $value . '%';
}
if ($col_type == DATA_TYPE_DATE || $col_type == DATA_TYPE_DATETIME) {
$dtValue = DateTimeValueLib::dateFromFormatAndString($dateFormat, $value);
$value = $dtValue->format('Y-m-d');
}
if ($condField->getCondition() != '%') {
if ($col_type == DATA_TYPE_INTEGER || $col_type == DATA_TYPE_FLOAT) {
$allConditions .= '`' . $condField->getFieldName() . '` ' . $condField->getCondition() . ' ' . mysql_real_escape_string($value);
} else {
if ($condField->getCondition() == '=' || $condField->getCondition() == '<=' || $condField->getCondition() == '>=') {
$equal = 'datediff(\'' . mysql_real_escape_string($value) . '\', `' . $condField->getFieldName() . '`)=0';
switch ($condField->getCondition()) {
case '=':
$allConditions .= $equal;
break;
case '<=':
case '>=':
$allConditions .= '(`' . $condField->getFieldName() . '` ' . $condField->getCondition() . ' \'' . mysql_real_escape_string($value) . '\'' . ' OR ' . $equal . ') ';
break;
}
} else {
$allConditions .= '`' . $condField->getFieldName() . '` ' . $condField->getCondition() . ' \'' . mysql_real_escape_string($value) . '\'';
}
}
} else {
$allConditions .= '`' . $condField->getFieldName() . '` like "%' . mysql_real_escape_string($value) . '"';
}
} else {
$allConditions .= ' true';
}
}
//else
}
//foreach
}
if (count($conditionsCp) > 0) {
foreach ($conditionsCp as $condCp) {
$cp = CustomProperties::getCustomProperty($condCp->getCustomPropertyId());
$skip_condition = false;
$dateFormat = 'm/d/Y';
if (isset($params[$condCp->getId() . "_" . $cp->getName()])) {
$value = $params[$condCp->getId() . "_" . $cp->getName()];
if ($cp->getType() == 'date') {
$dateFormat = user_config_option('date_format');
}
} else {
$value = $condCp->getValue();
}
if ($value == '' && $condCp->getIsParametrizable()) {
$skip_condition = true;
}
if (!$skip_condition) {
$allConditions .= ' AND ';
$allConditions .= 't.id IN ( SELECT object_id as id FROM ' . TABLE_PREFIX . 'custom_property_values cpv WHERE ';
$allConditions .= ' cpv.custom_property_id = ' . $condCp->getCustomPropertyId();
$fieldType = $object->getColumnType($condCp->getFieldName());
if ($condCp->getCondition() == 'like' || $condCp->getCondition() == 'not like') {
$value = '%' . $value . '%';
}
if ($cp->getType() == 'date') {
$dtValue = DateTimeValueLib::dateFromFormatAndString($dateFormat, $value);
$value = $dtValue->format('Y-m-d H:i:s');
}
if ($condCp->getCondition() != '%') {
if ($cp->getType() == 'numeric') {
$allConditions .= ' AND cpv.value ' . $condCp->getCondition() . ' ' . mysql_real_escape_string($value);
} else {
$allConditions .= ' AND cpv.value ' . $condCp->getCondition() . ' "' . mysql_real_escape_string($value) . '"';
}
} else {
$allConditions .= ' AND cpv.value like "%' . mysql_real_escape_string($value) . '"';
}
$allConditions .= ')';
}
}
}
if ($manager != 'Projects' && $manager != 'Users') {
$allConditions .= ' AND t.trashed_by_id = 0 ';
}
$sql .= $allConditions;
$rows = DB::executeAll($sql);
if (is_null($rows)) {
$rows = array();
}
$totalResults = count($rows);
$results['pagination'] = Reports::getReportPagination($id, $params, $order_by_col, $order_by_asc, $offset, $limit, $totalResults);
$selectCols = 'distinct(t.id) as "id"';
$titleCols = $managerInstance->getReportObjectTitleColumns();
$titleColAlias = array();
foreach ($titleCols as $num => $title) {
$selectCols .= ', t.' . $title . ' as "titleCol' . $num . '"';
$titleColAlias['titleCol' . $num] = $title;
}
$selectFROM = TABLE_PREFIX . $table . ' t ';
$selectWHERE = "WHERE {$allConditions}";
$order = $order_by_col != '' ? $order_by_col : $report->getOrderBy();
$order_asc = $order_by_col != '' ? $order_by_asc : $report->getIsOrderByAsc();
$allColumns = ReportColumns::getAllReportColumns($id);
$print_ws_idx = -1;
$print_tags_idx = -1;
if (is_array($allColumns) && count($allColumns) > 0) {
$first = true;
$openPar = '';
$index = 0;
foreach ($allColumns as $column) {
if ($column->getCustomPropertyId() == 0) {
$field = $column->getFieldName();
if ($managerInstance->columnExists($field)) {
$selectCols .= ', t.' . $field;
$results['columns'][] = lang('field ' . $report->getObjectType() . ' ' . $field);
$results['db_columns'][lang('field ' . $report->getObjectType() . ' ' . $field)] = $field;
$first = false;
} else {
if ($field === 'workspace') {
$print_ws_idx = $index;
} else {
if ($field === 'tag') {
$print_tags_idx = $index;
}
}
}
} else {
$colCp = $column->getCustomPropertyId();
$cp = CustomProperties::getCustomProperty($colCp);
if ($cp instanceof CustomProperty) {
$selectCols .= $cp->getIsMultipleValues() ? ', GROUP_CONCAT(DISTINCT cpv' . $colCp . '.value SEPARATOR ", ") as "' . $cp->getName() . '"' : ', cpv' . $colCp . '.value as "' . $cp->getName() . '"';
$results['columns'][] = $cp->getName();
$results['db_columns'][$cp->getName()] = $colCp;
$openPar .= '(';
$selectFROM .= ' LEFT OUTER JOIN ' . TABLE_PREFIX . 'custom_property_values cpv' . $colCp . ' ON (t.id = cpv' . $colCp . '.object_id AND cpv' . $colCp . '.custom_property_id = ' . $colCp . '))';
$first = false;
if ($order == $colCp) {
if ($cp->getType() == 'date') {
$order_by = 'ORDER BY STR_TO_DATE(cpv' . $colCp . '.value, "%Y-%m-%d %H:%i:%s") ' . ($order_asc ? 'asc' : 'desc');
} else {
$order_by = 'ORDER BY cpv' . $colCp . '.value ' . ($order_asc ? 'asc' : 'desc');
}
}
}
}
$index++;
}
}
if ($order_by == '') {
if (is_numeric($order)) {
$id = $order;
$openPar .= '(';
$selectFROM .= ' LEFT OUTER JOIN ' . TABLE_PREFIX . 'custom_property_values cpv' . $id . ' ON (t.id = cpv' . $id . '.object_id AND cpv' . $id . '.custom_property_id = ' . $id . '))';
$order_by = 'ORDER BY ' . $order;
} else {
if ($object->getColumnType($order) == 'date') {
$order_by = 'ORDER BY STR_TO_DATE(t.' . $order . ', "%Y-%m-%d %H:%i:%s") ' . ($order_asc ? 'asc' : 'desc');
} else {
$order_by = 'ORDER BY t.' . $order . ' ' . ($order_asc ? 'asc' : 'desc');
}
}
}
if ($to_print) {
$limit_str = '';
} else {
$limit_str = ' LIMIT ' . $offset . ',' . $limit;
}
$sql = 'SELECT ' . $selectCols . ' FROM (' . $openPar . $selectFROM . ') ' . $selectWHERE . ' GROUP BY id ' . $order_by . $limit_str;
$rows = DB::executeAll($sql);
if (is_null($rows)) {
$rows = array();
}
$rows = Reports::removeDuplicateRows($rows);
$reportObjTitleCols = array();
foreach ($rows as &$row) {
foreach ($row as $col => $value) {
if (isset($titleColAlias[$col])) {
$reportObjTitleCols[$titleColAlias[$col]] = $value;
}
}
$title = $managerInstance->getReportObjectTitle($reportObjTitleCols);
$iconame = strtolower($managerInstance->getItemClass());
$id = $row['id'];
unset($row['id']);
$row = array_slice($row, count($titleCols));
if (!$to_print) {
$row = array('link' => '<a class="link-ico ico-' . $iconame . '" title="' . clean($title) . '" target="new" href="' . get_url($controller, $view, array('id' => $id)) . '"> </a>') + $row;
}
foreach ($row as $col => &$value) {
if (in_array($col, $managerInstance->getExternalColumns())) {
$value = self::getExternalColumnValue($col, $value);
} else {
if ($col != 'link') {
$value = html_to_text(clean($value));
}
}
if (self::isReportColumnEmail($value)) {
if (logged_user()->hasMailAccounts()) {
$value = '<a class="internalLink" href="' . get_url('mail', 'add_mail', array('to' => clean($value))) . '">' . clean($value) . '</a></div>';
} else {
$value = '<a class="internalLink" target="_self" href="mailto:' . clean($value) . '">' . clean($value) . '</a></div>';
}
}
}
if ($print_tags_idx > -1) {
$row['tag'] = implode(", ", Tags::getTagNamesByObjectIds($id, $report->getObjectType()));
}
if ($print_ws_idx > -1) {
$row['workspace'] = "";
$workspaces = WorkspaceObjects::getWorkspacesByObject($report->getObjectType(), $id, logged_user()->getWorkspacesQuery());
foreach ($workspaces as $workspace) {
$row['workspace'] .= ($row['workspace'] == "" ? "" : ", ") . $workspace->getName();
}
}
// TODO: reorder columns
$row = str_replace('|', ',', $row);
}
// TODO: reorder column titles
if ($print_tags_idx > -1) {
$results['columns'][] = lang('tags');
}
if ($print_ws_idx > -1) {
$results['columns'][] = lang('workspaces');
}
if (!$to_print) {
if (is_array($results['columns'])) {
array_unshift($results['columns'], '');
} else {
$results['columns'] = array('');
}
}
$results['rows'] = $rows;
}
return $results;
}