function getColumnsTotal($reportid) { // Have we initialized it already? if ($this->_columnstotallist !== false) { return $this->_columnstotallist; } $adb = PearDatabase::getInstance(); global $modules; global $log, $current_user; static $modulename_cache = array(); $query = "select * from vtiger_reportmodules where reportmodulesid =?"; $res = $adb->pquery($query, array($reportid)); $modrow = $adb->fetch_array($res); $premod = $modrow["primarymodule"]; $secmod = $modrow["secondarymodules"]; $coltotalsql = "select vtiger_reportsummary.* from vtiger_report"; $coltotalsql .= " inner join vtiger_reportsummary on vtiger_report.reportid = vtiger_reportsummary.reportsummaryid"; $coltotalsql .= " where vtiger_report.reportid =?"; $result = $adb->pquery($coltotalsql, array($reportid)); while ($coltotalrow = $adb->fetch_array($result)) { $fieldcolname = $coltotalrow["columnname"]; if ($fieldcolname != "none") { $fieldlist = explode(":", $fieldcolname); $field_tablename = $fieldlist[1]; $field_columnname = $fieldlist[2]; $cachekey = $field_tablename . ":" . $field_columnname; if (!isset($modulename_cache[$cachekey])) { $mod_query = $adb->pquery("SELECT distinct(tabid) as tabid from vtiger_field where tablename = ? and columnname=?", array($fieldlist[1], $fieldlist[2])); if ($adb->num_rows($mod_query) > 0) { $module_name = getTabModuleName($adb->query_result($mod_query, 0, 'tabid')); $modulename_cache[$cachekey] = $module_name; } } else { $module_name = $modulename_cache[$cachekey]; } $fieldlabel = trim($fieldlist[3]); if ($field_tablename == 'vtiger_inventoryproductrel') { $field_columnalias = $premod . "__" . $fieldlist[3]; } else { if ($module_name) { $field_columnalias = $module_name . "__" . $fieldlist[3]; } else { $field_columnalias = $module_name . "__" . $fieldlist[3]; } } //$field_columnalias = $fieldlist[3]; $field_permitted = false; if (CheckColumnPermission($field_tablename, $field_columnname, $premod) != "false") { $field_permitted = true; } else { $mod = split(":", $secmod); foreach ($mod as $key) { if (CheckColumnPermission($field_tablename, $field_columnname, $key) != "false") { $field_permitted = true; } } } //Calculation fields of "Events" module should show in Calendar related report $secondaryModules = split(":", $secmod); if ($field_permitted === false && ($premod === 'Calendar' || in_array('Calendar', $secondaryModules)) && CheckColumnPermission($field_tablename, $field_columnname, "Events") != "false") { $field_permitted = true; } if ($field_permitted == true) { $field = $this->getColumnsTotalSQL($fieldlist, $premod); if ($fieldlist[4] == 2) { $stdfilterlist[$fieldcolname] = "sum({$field}) '" . $field_columnalias . "'"; } if ($fieldlist[4] == 3) { //Fixed average calculation issue due to NULL values ie., when we use avg() function, NULL values will be ignored.to avoid this we use (sum/count) to find average. //$stdfilterlist[$fieldcolname] = "avg(".$fieldlist[1].".".$fieldlist[2].") '".$fieldlist[3]."'"; $stdfilterlist[$fieldcolname] = "(sum({$field})/count(*)) '" . $field_columnalias . "'"; } if ($fieldlist[4] == 4) { $stdfilterlist[$fieldcolname] = "min({$field}) '" . $field_columnalias . "'"; } if ($fieldlist[4] == 5) { $stdfilterlist[$fieldcolname] = "max({$field}) '" . $field_columnalias . "'"; } $this->queryPlanner->addTable($field_tablename); } } } // Save the information $this->_columnstotallist = $stdfilterlist; $log->info("ReportRun :: Successfully returned getColumnsTotal" . $reportid); return $stdfilterlist; }
function getColumnsTotal($reportid, $selectlist = '') { // Have we initialized it already? if ($this->_columnstotallist !== false) { return $this->_columnstotallist; } global $adb, $modules, $log, $current_user; $query = "select * from vtiger_reportmodules where reportmodulesid =?"; $res = $adb->pquery($query, array($reportid)); $modrow = $adb->fetch_array($res); $premod = $modrow["primarymodule"]; $secmod = $modrow["secondarymodules"]; $coltotalsql = "select vtiger_reportsummary.* from vtiger_report"; $coltotalsql .= " inner join vtiger_reportsummary on vtiger_report.reportid = vtiger_reportsummary.reportsummaryid"; $coltotalsql .= " where vtiger_report.reportid =?"; $result = $adb->pquery($coltotalsql, array($reportid)); $seltotalcols = array(); $stdfilterlist = array(); while ($coltotalrow = $adb->fetch_array($result)) { $fieldcolname = $coltotalrow["columnname"]; if ($fieldcolname != "none") { $sckey = $scval = ''; $fieldlist = explode(":", $fieldcolname); $field_tablename = $fieldlist[1]; $field_columnname = $fieldlist[2]; $mod_query = $adb->pquery("SELECT distinct(tabid) as tabid from vtiger_field where tablename = ? and columnname=?", array($fieldlist[1], $fieldlist[2])); if ($adb->num_rows($mod_query) > 0) { $module_name = getTabName($adb->query_result($mod_query, 0, 'tabid')); $fieldlabel = trim($fieldlist[3]); if ($module_name) { $field_columnalias = $module_name . "_" . $fieldlist[3]; } else { $field_columnalias = $module_name . "_" . $fieldlist[3]; } $field_columnalias = decode_html($field_columnalias); $query_columnalias = substr($field_columnalias, 0, strrpos($field_columnalias, '_')); $query_columnalias = str_replace(array(' ', '&'), '_', $query_columnalias); } $sckey = $field_tablename . ':' . $field_columnname . ':' . $query_columnalias . ':' . $field_columnname . ':N'; // vtiger_invoice:subject:Invoice_Subject:subject:V $scval = $field_tablename . '.' . $field_columnname . " AS '" . $query_columnalias . "'"; // vtiger_invoice.subject AS 'Invoice_Subject' $seltotalcols[$sckey] = $scval; $field_permitted = false; if (CheckColumnPermission($field_tablename, $field_columnname, $module_name) != "false") { $field_permitted = true; } /* one call to CheckColumnPermission with $module_name is better than the block below if(CheckColumnPermission($field_tablename,$field_columnname,$premod) != "false"){ $field_permitted = true; } else { $mod = explode(":",$secmod); foreach($mod as $key){ if(CheckColumnPermission($field_tablename,$field_columnname,$key) != "false"){ $field_permitted=true; } } } */ if ($field_permitted == true) { if ($field_tablename == 'vtiger_products' && $field_columnname == 'unit_price') { // Query needs to be rebuild to get the value in user preferred currency. [innerProduct and actual_unit_price are table and column alias.] $query_columnalias = ' actual_unit_price'; $seltotalcols['innerProduct:actual_unit_price:Products_Unit_Price:actual_unit_price:N'] = 'innerProduct.actual_unit_price AS actual_unit_price'; } if ($field_tablename == 'vtiger_service' && $field_columnname == 'unit_price') { // Query needs to be rebuild to get the value in user preferred currency. [innerProduct and actual_unit_price are table and column alias.] $query_columnalias = ' actual_unit_price'; $seltotalcols['innerService:actual_unit_price:Services_Unit_Price:actual_unit_price:N'] = 'innerService.actual_unit_price AS actual_unit_price'; } if (($field_tablename == 'vtiger_invoice' || $field_tablename == 'vtiger_quotes' || $field_tablename == 'vtiger_purchaseorder' || $field_tablename == 'vtiger_salesorder') && ($field_columnname == 'total' || $field_columnname == 'subtotal' || $field_columnname == 'discount_amount' || $field_columnname == 's_h_amount')) { $query_columnalias = " {$query_columnalias}/{$module_name}" . "_Conversion_Rate "; $seltotalcols[$field_tablename . ':conversion_rate:' . $module_name . '_Conversion_Rate:conversion_rate:N'] = "{$field_tablename}.conversion_rate AS {$module_name}" . "_Conversion_Rate "; } if ($fieldlist[4] == 2) { if ($fieldlist[2] == 'totaltime') { $stdfilterlist[$fieldcolname] = "sec_to_time(sum(time_to_sec(" . $query_columnalias . "))) '" . $field_columnalias . "'"; } else { $stdfilterlist[$fieldcolname] = "sum({$query_columnalias}) '" . $field_columnalias . "'"; } } if ($fieldlist[4] == 3) { //Fixed average calculation issue due to NULL values ie., when we use avg() function, NULL values will be ignored.to avoid this we use (sum/count) to find average. //$stdfilterlist[$fieldcolname] = "avg(".$fieldlist[1].".".$fieldlist[2].") '".$fieldlist[3]."'"; if ($fieldlist[2] == 'totaltime') { $stdfilterlist[$fieldcolname] = "sec_to_time(sum(time_to_sec(" . $query_columnalias . "))/count(*)) '" . $field_columnalias . "'"; } else { $stdfilterlist[$fieldcolname] = "(sum({$query_columnalias})/count(*)) '" . $field_columnalias . "'"; } } if ($fieldlist[4] == 4) { if ($fieldlist[2] == 'totaltime') { $stdfilterlist[$fieldcolname] = "sec_to_time(min(time_to_sec(" . $query_columnalias . "))) '" . $field_columnalias . "'"; } else { $stdfilterlist[$fieldcolname] = "min({$query_columnalias}) '" . $field_columnalias . "'"; } } if ($fieldlist[4] == 5) { if ($fieldlist[2] == 'totaltime') { $stdfilterlist[$fieldcolname] = "sec_to_time(max(time_to_sec(" . $query_columnalias . "))) '" . $field_columnalias . "'"; } else { $stdfilterlist[$fieldcolname] = "max({$query_columnalias}) '" . $field_columnalias . "'"; } } } } } // Save the information $this->_columnstotallist = $stdfilterlist; $stc = array_diff($seltotalcols, $selectlist); $this->_columnstotallistaddtoselect = $stc; $log->info("ReportRun :: Successfully returned getColumnsTotal" . $reportid); return $stdfilterlist; }
function getColumnsTotal($reportid) { global $log; $log->debug("Entering ./modules/ITS4YouReports/GenerateObj.php::getColumnsTotal"); global $log; $log->debug("Entering ./modules/ITS4YouReports/GenerateObj.php::getColumnsTotal"); global $log; $log->debug("Entering ./modules/ITS4YouReports/GenerateObj.php::getColumnsTotal"); global $log; $log->debug("Entering ./modules/ITS4YouReports/GenerateObj.php::getColumnsTotal"); // Have we initialized it already? if (isset($this->_columnstotallist) && $this->_columnstotallist !== false) { return $this->_columnstotallist; } $adb = PEARDatabase::getInstance(); global $current_user; $query = "SELECT * FROM its4you_reports4you_modules WHERE reportmodulesid =?"; $res = $adb->pquery($query, array($reportid)); $modrow = $adb->fetch_array($res); $premod = $modrow["primarymodule"]; $premod_name = vtlib_getModuleNameById($premod); $secmod = $modrow["secondarymodules"]; $coltotalsql = "SELECT its4you_reports4you_summary.* FROM its4you_reports4you"; $coltotalsql .= " INNER JOIN its4you_reports4you_summary on its4you_reports4you.reports4youid = its4you_reports4you_summary.reportsummaryid"; $coltotalsql .= " WHERE its4you_reports4you.reports4youid =?"; $result = $adb->pquery($coltotalsql, array($reportid)); while ($coltotalrow = $adb->fetch_array($result)) { $fieldcolname = $coltotalrow["columnname"]; if ($fieldcolname != "none") { $fieldlist = explode(":", $fieldcolname); $field_tablename = $fieldlist[1]; $field_columnname = $fieldlist[2]; $mod_query = $adb->pquery("SELECT distinct(tabid) AS tabid FROM vtiger_field WHERE tablename = ? AND columnname=?", array($fieldlist[1], $fieldlist[2])); if ($adb->num_rows($mod_query) > 0) { $module_name = getTabName($adb->query_result($mod_query, 0, 'tabid')); $fieldlabel = trim($fieldlist[3]); if ($module_name) { $field_columnalias = $module_name . "_" . $fieldlist[3]; } else { $field_columnalias = $module_name . "_" . $fieldlist[3]; } } //$field_columnalias = $fieldlist[3]; $field_permitted = false; if (CheckColumnPermission($field_tablename, $field_columnname, $premod) != "false") { $field_permitted = true; } else { $mod_s = split(":", $secmod); // $premod_name = vtlib_getModuleNameById($premod); foreach ($mod_s as $key) { $mod_arr = explode("x", $key); $mod = $mod_arr[0]; $mod_name = vtlib_getModuleNameById($mod); if (CheckColumnPermission($field_tablename, $field_columnname, $mod_name) != "false") { $field_permitted = true; } } } if ($field_permitted == true) { $field = $field_tablename . "." . $field_columnname; if ($field_tablename == 'vtiger_products' && $field_columnname == 'unit_price') { // Query needs to be rebuild to get the value in user preferred currency. [innerProduct and actual_unit_price are table and column alias.] $field = " innerProduct.actual_unit_price"; } if ($field_tablename == 'vtiger_service' && $field_columnname == 'unit_price') { // Query needs to be rebuild to get the value in user preferred currency. [innerProduct and actual_unit_price are table and column alias.] $field = " innerService.actual_unit_price"; } if (($field_tablename == 'vtiger_invoice' || $field_tablename == 'vtiger_quotes' || $field_tablename == 'vtiger_purchaseorder' || $field_tablename == 'vtiger_salesorder') && ($field_columnname == 'total' || $field_columnname == 'subtotal' || $field_columnname == 'discount_amount' || $field_columnname == 's_h_amount')) { $field = " {$field_tablename}.{$field_columnname}/{$field_tablename}.conversion_rate "; } //ITS4YouReports::sshow($fieldlist); if ($fieldlist[4] == 2) { $stdfilterlist[$fieldcolname] = "sum({$field}) '" . $field_columnalias . "'"; } if ($fieldlist[4] == 3) { //Fixed average calculation issue due to NULL values ie., when we use avg() function, NULL values will be ignored.to avoid this we use (sum/count) to find average. //$stdfilterlist[$fieldcolname] = "avg(".$fieldlist[1].".".$fieldlist[2].") '".$fieldlist[3]."'"; $stdfilterlist[$fieldcolname] = "(sum({$field})/count(*)) '" . $field_columnalias . "'"; } if ($fieldlist[4] == 4) { $stdfilterlist[$fieldcolname] = "min({$field}) '" . $field_columnalias . "'"; } if ($fieldlist[4] == 5) { $stdfilterlist[$fieldcolname] = "max({$field}) '" . $field_columnalias . "'"; } } } } // Save the information $this->_columnstotallist = $stdfilterlist; return $stdfilterlist; }
function getColumnsTotal($reportid) { // Have we initialized it already? if ($this->_columnstotallist !== false) { return $this->_columnstotallist; } global $adb; global $modules; global $log, $current_user; $query = "select * from vtiger_reportmodules where reportmodulesid =?"; $res = $adb->pquery($query, array($reportid)); $modrow = $adb->fetch_array($res); $premod = $modrow["primarymodule"]; $secmod = $modrow["secondarymodules"]; $coltotalsql = "select vtiger_reportsummary.* from vtiger_report"; $coltotalsql .= " inner join vtiger_reportsummary on vtiger_report.reportid = vtiger_reportsummary.reportsummaryid"; $coltotalsql .= " where vtiger_report.reportid =?"; $result = $adb->pquery($coltotalsql, array($reportid)); while ($coltotalrow = $adb->fetch_array($result)) { $fieldcolname = $coltotalrow["columnname"]; if ($fieldcolname != "none") { $fieldlist = explode(":", $fieldcolname); $field_tablename = $fieldlist[1]; $field_columnname = $fieldlist[2]; $mod_query = $adb->pquery("SELECT distinct(tabid) as tabid from vtiger_field where tablename = ? and columnname=?", array($fieldlist[1], $fieldlist[2])); if ($adb->num_rows($mod_query) > 0) { $module_name = getTabName($adb->query_result($mod_query, 0, 'tabid')); $fieldlabel = trim($fieldlist[3]); if ($module_name) { $field_columnalias = $module_name . "_" . $fieldlist[3]; } else { $field_columnalias = $module_name . "_" . $fieldlist[3]; } } //$field_columnalias = $fieldlist[3]; $field_permitted = false; if (CheckColumnPermission($field_tablename, $field_columnname, $premod) != "false") { $field_permitted = true; } else { $mod = split(":", $secmod); foreach ($mod as $key) { if (CheckColumnPermission($field_tablename, $field_columnname, $key) != "false") { $field_permitted = true; } } } if ($field_permitted == true) { $field = $field_tablename . "." . $field_columnname; if ($field_tablename == 'vtiger_products' && $field_columnname == 'unit_price') { // Query needs to be rebuild to get the value in user preferred currency. [innerProduct and actual_unit_price are table and column alias.] $field = " innerProduct.actual_unit_price"; } if ($field_tablename == 'vtiger_service' && $field_columnname == 'unit_price') { // Query needs to be rebuild to get the value in user preferred currency. [innerProduct and actual_unit_price are table and column alias.] $field = " innerService.actual_unit_price"; } if (($field_tablename == 'vtiger_invoice' || $field_tablename == 'vtiger_quotes' || $field_tablename == 'vtiger_purchaseorder' || $field_tablename == 'vtiger_salesorder') && ($field_columnname == 'total' || $field_columnname == 'subtotal' || $field_columnname == 'discount_amount' || $field_columnname == 's_h_amount')) { $field = " {$field_tablename}.{$field_columnname}/{$field_tablename}.conversion_rate "; } if ($fieldlist[4] == 2) { $stdfilterlist[$fieldcolname] = "sum({$field}) '" . $field_columnalias . "'"; } if ($fieldlist[4] == 3) { //Fixed average calculation issue due to NULL values ie., when we use avg() function, NULL values will be ignored.to avoid this we use (sum/count) to find average. //$stdfilterlist[$fieldcolname] = "avg(".$fieldlist[1].".".$fieldlist[2].") '".$fieldlist[3]."'"; $stdfilterlist[$fieldcolname] = "(sum({$field})/count(*)) '" . $field_columnalias . "'"; } if ($fieldlist[4] == 4) { $stdfilterlist[$fieldcolname] = "min({$field}) '" . $field_columnalias . "'"; } if ($fieldlist[4] == 5) { $stdfilterlist[$fieldcolname] = "max({$field}) '" . $field_columnalias . "'"; } } } } // Save the information $this->_columnstotallist = $stdfilterlist; $log->info("ReportRun :: Successfully returned getColumnsTotal" . $reportid); return $stdfilterlist; }
function getColumnsTotal($reportid) { // Have we initialized it already? if ($this->_columnstotallist !== false) { return $this->_columnstotallist; } global $adb; global $modules; global $log, $current_user; static $modulename_cache = array(); $query = "select * from vtiger_reportmodules where reportmodulesid =?"; $res = $adb->pquery($query, array($reportid)); $modrow = $adb->fetch_array($res); $premod = $modrow["primarymodule"]; $secmod = $modrow["secondarymodules"]; $coltotalsql = "select vtiger_reportsummary.* from vtiger_report"; $coltotalsql .= " inner join vtiger_reportsummary on vtiger_report.reportid = vtiger_reportsummary.reportsummaryid"; $coltotalsql .= " where vtiger_report.reportid =?"; $result = $adb->pquery($coltotalsql, array($reportid)); while ($coltotalrow = $adb->fetch_array($result)) { $fieldcolname = $coltotalrow["columnname"]; if ($fieldcolname != "none") { $fieldlist = explode(":", $fieldcolname); $field_tablename = $fieldlist[1]; $field_columnname = $fieldlist[2]; $cachekey = $field_tablename . ":" . $field_columnname; if (!isset($modulename_cache[$cachekey])) { $mod_query = $adb->pquery("SELECT distinct(tabid) as tabid from vtiger_field where tablename = ? and columnname=?", array($fieldlist[1], $fieldlist[2])); if ($adb->num_rows($mod_query) > 0) { $module_name = getTabName($adb->query_result($mod_query, 0, 'tabid')); $modulename_cache[$cachekey] = $module_name; } } else { $module_name = $modulename_cache[$cachekey]; } $fieldlabel = trim($fieldlist[3]); if ($field_tablename == 'vtiger_inventoryproductrel') { $field_columnalias = $premod . "_" . $fieldlist[3]; } else { if ($module_name) { $field_columnalias = $module_name . "_" . $fieldlist[3]; } else { $field_columnalias = $module_name . "_" . $fieldlist[3]; } } //$field_columnalias = $fieldlist[3]; $field_permitted = false; if (CheckColumnPermission($field_tablename, $field_columnname, $premod) != "false") { $field_permitted = true; } else { $mod = split(":", $secmod); foreach ($mod as $key) { if (CheckColumnPermission($field_tablename, $field_columnname, $key) != "false") { $field_permitted = true; } } } //Calculation fields of "Events" module should show in Calendar related report $secondaryModules = split(":", $secmod); if ($field_permitted === false && ($premod === 'Calendar' || in_array('Calendar', $secondaryModules)) && CheckColumnPermission($field_tablename, $field_columnname, "Events") != "false") { $field_permitted = true; } if ($field_permitted == true) { $field = $field_tablename . "." . $field_columnname; if ($field_tablename == 'vtiger_products' && $field_columnname == 'unit_price') { // Query needs to be rebuild to get the value in user preferred currency. [innerProduct and actual_unit_price are table and column alias.] $field = " innerProduct.actual_unit_price"; $this->queryPlanner->addTable("innerProduct"); } if ($field_tablename == 'vtiger_service' && $field_columnname == 'unit_price') { // Query needs to be rebuild to get the value in user preferred currency. [innerProduct and actual_unit_price are table and column alias.] $field = " innerService.actual_unit_price"; $this->queryPlanner->addTable("innerService"); } if (($field_tablename == 'vtiger_invoice' || $field_tablename == 'vtiger_quotes' || $field_tablename == 'vtiger_purchaseorder' || $field_tablename == 'vtiger_salesorder') && ($field_columnname == 'total' || $field_columnname == 'subtotal' || $field_columnname == 'discount_amount' || $field_columnname == 's_h_amount' || $field_columnname == 'paid' || $field_columnname == 'balance' || $field_columnname == 'received')) { $field = " {$field_tablename}.{$field_columnname}/{$field_tablename}.conversion_rate "; } if ($field_tablename == 'vtiger_inventoryproductrel') { // Check added so that query planner can prepare query properly for inventory modules $this->lineItemFieldsInCalculation = true; $field = $field_tablename . $premod . '.' . $field_columnname; $itemTableName = 'vtiger_inventoryproductrel' . $premod; $this->queryPlanner->addTable($itemTableName); if ($field_columnname == 'listprice') { $primaryModuleInstance = CRMEntity::getInstance($premod); $field = $field . '/' . $primaryModuleInstance->table_name . '.conversion_rate'; } else { if ($field_columnname == 'discount_amount') { $field = ' CASE WHEN ' . $itemTableName . '.discount_amount is not null THEN ' . $itemTableName . '.discount_amount/' . $primaryModuleInstance->table_name . '.conversion_rate ' . 'WHEN ' . $itemTableName . '.discount_percent IS NOT NULL THEN (' . $itemTableName . '.listprice*' . $itemTableName . '.quantity*' . $itemTableName . '.discount_percent/100/' . $primaryModuleInstance->table_name . '.conversion_rate) ELSE 0 END '; } } } if ($fieldlist[4] == 2) { $stdfilterlist[$fieldcolname] = "sum({$field}) '" . $field_columnalias . "'"; } if ($fieldlist[4] == 3) { //Fixed average calculation issue due to NULL values ie., when we use avg() function, NULL values will be ignored.to avoid this we use (sum/count) to find average. //$stdfilterlist[$fieldcolname] = "avg(".$fieldlist[1].".".$fieldlist[2].") '".$fieldlist[3]."'"; $stdfilterlist[$fieldcolname] = "(sum({$field})/count(*)) '" . $field_columnalias . "'"; } if ($fieldlist[4] == 4) { $stdfilterlist[$fieldcolname] = "min({$field}) '" . $field_columnalias . "'"; } if ($fieldlist[4] == 5) { $stdfilterlist[$fieldcolname] = "max({$field}) '" . $field_columnalias . "'"; } $this->queryPlanner->addTable($field_tablename); } } } // Save the information $this->_columnstotallist = $stdfilterlist; $log->info("ReportRun :: Successfully returned getColumnsTotal" . $reportid); return $stdfilterlist; }