/** * Constructs a query to fetch data for supanels and list views * * It constructs union queries for activities subpanel. * * @param Object $parentbean constructing queries for link attributes in this bean * @param string $order_by Optional, order by clause * @param string $sort_order Optional, sort order * @param string $where Optional, additional where clause * * Internal Function, do not overide. */ function get_union_related_list($parentbean, $order_by = "", $sort_order = '', $where = "", $row_offset = 0, $limit = -1, $max = -1, $show_deleted = 0, $subpanel_def) { $secondary_queries = array(); global $layout_edit_mode, $beanFiles, $beanList; if (isset($_SESSION['show_deleted'])) { $show_deleted = 1; } $final_query = ''; $final_query_rows = ''; $subpanel_list = array(); if ($subpanel_def->isCollection()) { $subpanel_def->load_sub_subpanels(); $subpanel_list = $subpanel_def->sub_subpanels; } else { $subpanel_list[] = $subpanel_def; } $first = true; //Breaking the building process into two loops. The first loop gets a list of all the sub-queries. //The second loop merges the queries and forces them to select the same number of columns //All columns in a sub-subpanel group must have the same aliases //If the subpanel is a datasource function, it can't be a collection so we just poll that function for the and return that foreach ($subpanel_list as $this_subpanel) { if ($this_subpanel->isDatasourceFunction() && empty($this_subpanel->_instance_properties['generate_select'])) { $shortcut_function_name = $this_subpanel->get_data_source_name(); $parameters = $this_subpanel->get_function_parameters(); if (!empty($parameters)) { //if the import file function is set, then import the file to call the custom function from if (is_array($parameters) && isset($parameters['import_function_file'])) { //this call may happen multiple times, so only require if function does not exist if (!function_exists($shortcut_function_name)) { require_once $parameters['import_function_file']; } //call function from required file $tmp_final_query = $shortcut_function_name($parameters); } else { //call function from parent bean $tmp_final_query = $parentbean->{$shortcut_function_name}($parameters); } } else { $tmp_final_query = $parentbean->{$shortcut_function_name}(); } if (!$first) { $final_query_rows .= ' UNION ALL ( ' . $parentbean->create_list_count_query($tmp_final_query, $parameters) . ' )'; $final_query .= ' UNION ALL ( ' . $tmp_final_query . ' )'; } else { $final_query_rows = '(' . $parentbean->create_list_count_query($tmp_final_query, $parameters) . ')'; $final_query = '(' . $tmp_final_query . ')'; $first = false; } } } //If final_query is still empty, its time to build the sub-queries if (empty($final_query)) { $subqueries = SugarBean::build_sub_queries_for_union($subpanel_list, $subpanel_def, $parentbean, $order_by); $all_fields = array(); foreach ($subqueries as $i => $subquery) { $query_fields = $GLOBALS['db']->helper->getSelectFieldsFromQuery($subquery['select']); foreach ($query_fields as $field => $select) { if (!in_array($field, $all_fields)) { $all_fields[] = $field; } } $subqueries[$i]['query_fields'] = $query_fields; } $first = true; //Now ensure the queries have the same set of fields in the same order. foreach ($subqueries as $subquery) { $subquery['select'] = "SELECT"; foreach ($all_fields as $field) { if (!isset($subquery['query_fields'][$field])) { $subquery['select'] .= " ' ' {$field},"; } else { $subquery['select'] .= " {$subquery['query_fields'][$field]},"; } } $subquery['select'] = substr($subquery['select'], 0, strlen($subquery['select']) - 1); //Put the query into the final_query $query = $subquery['select'] . " " . $subquery['from'] . " " . $subquery['where']; if (!$first) { $query = ' UNION ALL ( ' . $query . ' )'; $final_query_rows .= " UNION ALL "; } else { $query = '(' . $query . ')'; $first = false; } $query_array = $subquery['query_array']; $select_position = strpos($query_array['select'], "SELECT"); $distinct_position = strpos($query_array['select'], "DISTINCT"); if ($select_position !== false && $distinct_position != false) { $query_rows = "( " . substr_replace($query_array['select'], "SELECT count(", $select_position, 6) . ")" . $subquery['from_min'] . $query_array['join'] . $subquery['where'] . ' )'; } else { //resort to default behavior. $query_rows = "( SELECT count(*)" . $subquery['from_min'] . $query_array['join'] . $subquery['where'] . ' )'; } if (!empty($subquery['secondary_select'])) { $subquerystring = $subquery['secondary_select'] . $subquery['secondary_from'] . $query_array['join'] . $subquery['where']; if (!empty($subquery['secondary_where'])) { if (empty($subquery['where'])) { $subquerystring .= " WHERE " . $subquery['secondary_where']; } else { $subquerystring .= " AND " . $subquery['secondary_where']; } } $secondary_queries[] = $subquerystring; } $final_query .= $query; $final_query_rows .= $query_rows; } } if (!empty($order_by)) { $submodule = false; if (!$subpanel_def->isCollection()) { $submodulename = $subpanel_def->_instance_properties['module']; $submoduleclass = $beanList[$submodulename]; $submodule = new $submoduleclass(); } if (!empty($submodule) && !empty($submodule->table_name)) { $final_query .= " ORDER BY " . $parentbean->process_order_by($order_by, $submodule); } else { $final_query .= " ORDER BY " . $order_by . ' '; } if (!empty($sort_order)) { $final_query .= ' ' . $sort_order; } } if (isset($layout_edit_mode) && $layout_edit_mode) { $response = array(); if (!empty($submodule)) { $submodule->assign_display_fields($submodule->module_dir); $response['list'] = array($submodule); } else { $response['list'] = array(); } $response['parent_data'] = array(); $response['row_count'] = 1; $response['next_offset'] = 0; $response['previous_offset'] = 0; return $response; } return $parentbean->process_union_list_query($parentbean, $final_query, $row_offset, $limit, $max, '', $subpanel_def, $final_query_rows, $secondary_queries); }