예제 #1
0
 /**
  * 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)) . '">&nbsp;</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;
 }