2 if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
3 /*********************************************************************************
4 * SugarCRM Community Edition is a customer relationship management program developed by
5 * SugarCRM, Inc. Copyright (C) 2004-2013 SugarCRM Inc.
7 * This program is free software; you can redistribute it and/or modify it under
8 * the terms of the GNU Affero General Public License version 3 as published by the
9 * Free Software Foundation with the addition of the following permission added
10 * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
11 * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
12 * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
14 * This program is distributed in the hope that it will be useful, but WITHOUT
15 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
16 * FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
19 * You should have received a copy of the GNU Affero General Public License along with
20 * this program; if not, see http://www.gnu.org/licenses or write to the Free
21 * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
24 * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
25 * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
27 * The interactive user interfaces in modified source and object code versions
28 * of this program must display Appropriate Legal Notices, as required under
29 * Section 5 of the GNU Affero General Public License version 3.
31 * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
32 * these Appropriate Legal Notices must retain the display of the "Powered by
33 * SugarCRM" logo. If the display of the logo is not reasonably feasible for
34 * technical reasons, the Appropriate Legal Notices must display the words
35 * "Powered by SugarCRM".
36 ********************************************************************************/
43 $used_aliases = array();
46 class SugarWidgetReportField extends SugarWidgetField
49 * Layout manager reporter attribute
54 function SugarWidgetReportField(&$layout_manager) {
55 parent::SugarWidgetField($layout_manager);
56 $this->reporter = $this->layout_manager->getAttribute("reporter");
59 function getSubClass($layout_def)
61 if (! empty($layout_def['type']))
64 if ($layout_def['type'] == 'time') {
65 $layout_def['widget_class'] = 'Fielddate';
67 $layout_def['widget_class'] = 'Field'.$layout_def['type'];
69 return $this->layout_manager->getClassFromWidgetDef($layout_def);
76 function display($layout_def)
78 $obj = $this->getSubClass($layout_def);
80 $context = $this->layout_manager->getAttribute('context');//_ppd($context);
81 $func_name = 'display'.$context;
84 if ( ! empty($context) && method_exists($obj,$func_name))
86 return $obj->$func_name($layout_def);
89 return 'display not found:'.$func_name;
93 function _get_column_select_special($layout_def)
96 if ( ! empty($layout_def['table_alias']))
98 $alias = $layout_def['table_alias'];
101 if ($layout_def['name'] == 'weighted_sum' )
103 return sprintf("SUM(%s * %s * 0.01)", $this->reporter->db->convert("$alias.probability","IFNULL", array(0)),
104 $this->reporter->db->convert("$alias.amount_usdollar","IFNULL", array(0)));
106 if ($layout_def['name'] == 'weighted_amount' )
108 return sprintf("AVG(%s * %s * 0.01)", $this->reporter->db->convert("$alias.probability","IFNULL", array(0)),
109 $this->reporter->db->convert("$alias.amount_usdollar","IFNULL", array(0)));
113 function _get_column_select($layout_def)
116 if (!isset($reportAlias)) {
117 $reportAlias = array();
120 if ( ! empty($layout_def['table_alias'])) {
121 $alias = $layout_def['table_alias'].".".$layout_def['name'];
122 } else if (! empty($layout_def['name'])) {
123 $alias = $layout_def['name'];
128 if ( ! empty($layout_def['group_function']) )
130 if ($layout_def['name'] == 'weighted_sum' || $layout_def['name'] == 'weighted_amount')
132 $alias = $this->_get_column_select_special($layout_def);
133 $reportAlias[$alias] = $layout_def;
137 // Use IFNULL only if it's not AVG aggregate
138 // because it adds NULL rows to the count when it should not, thus getting wrong result
139 if ($layout_def['group_function'] != 'avg') {
140 $alias = $this->reporter->db->convert($alias, 'IFNULL', array(0));
143 // for a field with type='currency' conversion of values into a user-preferred currency
144 if ($layout_def['type'] == 'currency' && strpos($layout_def['name'], '_usdoll') === false) {
145 $currency = $this->reporter->currency_obj;
146 $currency_alias = isset($layout_def['currency_alias'])
147 ? $layout_def['currency_alias'] : $currency->table_name;
148 $query = $this->reporter->db->convert($currency_alias.".conversion_rate", "IFNULL", array(1));
149 // We need to use convert() for AVG because of Oracle
150 if ($layout_def['group_function'] != 'avg') {
151 $alias = "{$layout_def['group_function']}($alias/{$query})*{$currency->conversion_rate}";
153 $alias = $this->reporter->db->convert("$alias/$query", "AVG") . " * {$currency->conversion_rate}";
156 // We need to use convert() for AVG because of Oracle
157 if ($layout_def['group_function'] != 'avg') {
158 $alias = "{$layout_def['group_function']}($alias)";
160 $alias = $this->reporter->db->convert($alias, "AVG");
166 $reportAlias[$alias] = $layout_def;
170 function querySelect(&$layout_def)
172 return $this->_get_column_select($layout_def)." ".$this->_get_column_alias($layout_def)."\n";
175 function queryGroupBy($layout_def)
177 return $this->_get_column_select($layout_def)." \n";
181 function queryOrderBy($layout_def)
183 if(!empty($this->reporter->all_fields[$layout_def['column_key']])) $field_def = $this->reporter->all_fields[$layout_def['column_key']];
185 if (!empty($layout_def['group_function']))
187 $order_by = $this->_get_column_alias($layout_def);
189 elseif (!empty($field_def['sort_on']))
191 $order_by = $layout_def['table_alias'].".".$field_def['sort_on'];
192 if(!empty($field_def['sort_on2']))
193 $order_by .= ', ' . $layout_def['table_alias'].".".$field_def['sort_on2'];
196 $order_by = $this->_get_column_alias($layout_def)." \n";
199 if ( empty($layout_def['sort_dir']) || $layout_def['sort_dir'] == 'a')
201 return $order_by." ASC";
203 return $order_by." DESC";
208 function queryFilter($layout_def)
210 $method_name = "queryFilter".$layout_def['qualifier_name'];
211 return $this->$method_name($layout_def);
214 function displayHeaderCell($layout_def)
216 global $start_link_wrapper,$end_link_wrapper;
219 // don't show sort links if name isn't defined
220 $no_sort = $this->layout_manager->getAttribute('no_sort');
221 if(empty($layout_def['name']) || ! empty($no_sort) || ! empty($layout_def['no_sort']))
223 return $layout_def['label'];
229 if ( ! empty($layout_def['table_key']) && ! empty($layout_def['name']) ) {
230 if (! empty($layout_def['group_function']) && $layout_def['group_function'] == 'count') {
231 $sort_by = $layout_def['table_key'].":".'count';
233 $sort_by = $layout_def['table_key'].":".$layout_def['name'];
234 if ( ! empty($layout_def['column_function'])) {
235 $sort_by .= ':'.$layout_def['column_function'];
236 } else if ( ! empty($layout_def['group_function']) ) {
237 $sort_by .= ':'.$layout_def['group_function'];
241 return $this->displayHeaderCellPlain($layout_def);
244 $start = empty($start_link_wrapper) ? '': $start_link_wrapper;
245 $end = empty($end_link_wrapper) ? '': $end_link_wrapper;
247 // unable to retrieve the vardef here, exclude columns of type clob/text from being sortable
249 if(!in_array($layout_def['name'], array('description', 'account_description', 'lead_source_description', 'status_description', 'to_addrs', 'cc_addrs', 'bcc_addrs', 'work_log', 'objective', 'resolution'))) {
250 $header_cell = "<a class=\"listViewThLinkS1\" href=\"".$start.$sort_by.$end."\">";
251 $header_cell .= $this->displayHeaderCellPlain($layout_def);
252 $header_cell .= ListView::getArrowUpDownStart(isset($layout_def['sort']) ? $layout_def['sort'] : '');
253 $header_cell .= ListView::getArrowUpDownEnd(isset($layout_def['sort']) ? $layout_def['sort'] : '');
254 $header_cell .= "</a>";
258 return $this->displayHeaderCellPlain($layout_def);
261 function query($layout_def)
263 $obj = $this->getSubClass($layout_def);
265 $context = $this->layout_manager->getAttribute('context');
266 $func_name = 'query'.$context;
268 if ( ! empty($context) && method_exists($obj,$func_name))
270 return $obj->$func_name($layout_def);
277 function _get_column_alias($layout_def)
279 $alias_arr = array();
281 if (!empty($layout_def['table_key']) && $layout_def['table_key'] == 'self' && !empty($layout_def['name']) && $layout_def['name'] == 'id')
287 // this comment is being added to trigger the upgrade package
288 if ( ! empty($layout_def['group_function']) && $layout_def['group_function']=='count')
290 return $layout_def['table_alias'] . '__count';
293 if ( ! empty($layout_def['table_alias']))
295 array_push($alias_arr,$layout_def['table_alias']);
298 if ( ! empty($layout_def['group_function']) && $layout_def['group_function'] != 'weighted_amount' && $layout_def['group_function'] != 'weighted_sum')
300 array_push($alias_arr,$layout_def['group_function']);
301 } else if ( ! empty($layout_def['column_function']))
303 array_push($alias_arr,$layout_def['column_function']);
304 } else if ( ! empty($layout_def['qualifier']))
306 array_push($alias_arr,$layout_def['qualifier']);
309 if ( ! empty($layout_def['name']))
311 array_push($alias_arr,$layout_def['name']);
314 global $used_aliases, $alias_map;
316 $alias = strtolower(implode("_",$alias_arr));
318 $short_alias = $this->getTruncatedColumnAlias($alias);
320 if ( empty($used_aliases[$short_alias]))
322 $alias_map[$alias] = $short_alias;
323 $used_aliases[$short_alias] = 1;
325 } else if ( ! empty($alias_map[$alias]) )
327 return $alias_map[$alias];
329 $alias_map[$alias] = $short_alias.'_'.$used_aliases[$short_alias];
330 $used_aliases[$short_alias]++;
331 return $alias_map[$alias];
335 function queryFilterEmpty($layout_def)
337 $column = $this->_get_column_select($layout_def);
338 return "($column IS NULL OR $column = ".$this->reporter->db->emptyValue($layout_def['type']).")";
341 function queryFilterIs($layout_def)
343 return '( '.$this->_get_column_select($layout_def)."='".$GLOBALS['db']->quote($layout_def['input_name0'])."')\n";
346 function queryFilteris_not($layout_def)
348 return '( '.$this->_get_column_select($layout_def)."<>'".$GLOBALS['db']->quote($layout_def['input_name0'])."')\n";
351 function queryFilterNot_Empty($layout_def)
353 /** @var $db DBManager */
354 $db = $this->reporter->db;
355 $column = $this->_get_column_select($layout_def);
356 return "(coalesce(" . $db->convert($column, "length") . ",0) > 0)\n";