public static function prepareReportFilters($reportModule, $filtersJson, $translateFieldLabels, $fieldsToBeRemoved) { global $timedate; if (strlen($filtersJson) == 0) { return null; } $filtersArray = unserialize(base64_decode($filtersJson)); foreach ($filtersArray['data'] as &$currentFilter) { if (in_array($currentFilter['type'], array("date", "datetime", "datetimecombo", "timestamp")) && !in_array($currentFilter['operator'], array("last", "this", "these", "next", "not last", "not this", "not next"))) { if (!in_array($currentFilter['operator'], array("equals", "not equals", "before date", "after date", "between", "not between"))) { foreach ($currentFilter['parameters']['first'] as &$currentParameter) { if (!$timedate->check_matching_format($currentParameter, $timedate->get_date_format()) && $currentParameter != "") { $currentParameter = $timedate->swap_formats($currentParameter, $GLOBALS['timedate']->dbDayFormat, $timedate->get_date_format()); } } } if (count($currentFilter['parameters']['first']) > 0 && in_array($currentFilter['parameters']['first'][0], array("calendar"))) { foreach ($currentFilter['parameters']['second'] as &$currentParameter) { if (!$timedate->check_matching_format($currentParameter, $timedate->get_date_format()) && $currentParameter != "") { $currentParameter = $timedate->swap_formats($currentParameter, $GLOBALS['timedate']->dbDayFormat, $timedate->get_date_format()); } } if (in_array($currentFilter['operator'], array("between", "not between"))) { foreach ($currentFilter['parameters']['third'] as &$currentParameter) { if (!$timedate->check_matching_format($currentParameter, $timedate->get_date_format()) && $currentParameter != "") { $currentParameter = $timedate->swap_formats($currentParameter, $GLOBALS['timedate']->dbDayFormat, $timedate->get_date_format()); } } } } } if (in_array($currentFilter['enumOperator'], array('options', 'function'))) { $currentFilter['enumValues'] = asol_Report::getEnumValues($currentFilter['enumOperator'], $currentFilter['enumReference']); $currentFilter['enumLabels'] = asol_Report::getEnumLabels($currentFilter['enumOperator'], $currentFilter['enumReference']); } else { if ($currentFilter['enumOperator'] == 'asolFunction') { $currentFilter['enumLabels'] = $translateFieldLabels ? asol_Report::getAuditedLabels($reportModule, $fieldsToBeRemoved) : asol_Report::getAuditedFields($reportModule, $fieldsToBeRemoved); $currentFilter['enumValues'] = asol_Report::getAuditedFields($reportModule, $fieldsToBeRemoved); } } } $filtersJson = addslashes(json_encode($filtersArray)); return array('json' => $filtersJson, 'array' => $filtersArray); }
public static function getFilteringParams($filters, $extFilters, $report_module, $predefinedTemplates, $dashletId, $userDateFormat, $auditedReport) { global $current_user, $app_strings, $timedate, $beanList, $beanFiles; $filtersPanel = array(); $filtersHiddenInputs = ""; $dashletId = str_replace("-", "", $dashletId); foreach ($filters['data'] as &$currentFilter) { $filterField = $currentFilter['field']; $filterReference = $currentFilter['filterReference']; $filterType = $currentFilter['type']; $filterBehavior = $currentFilter['behavior']; $filterUserOptions = $currentFilter['userOptions']; $filterEnumOperator = $currentFilter['enumOperator']; $filterEnumReference = $currentFilter['enumReference']; $filterEnumTemplate = isset($currentFilter['templates']['enum']) ? $currentFilter['templates']['enum'] : null; $hasCustomEnum = !empty($filterUserOptions) || !empty($filterEnumTemplate); if ($hasCustomEnum) { //***********************// //***AlineaSol Premium***// //***********************// $filterUserOptionsResult = asol_ReportsUtils::managePremiumFeature("templatesReports", "reportFunctions.php", "getFilterTemplateUserOptions", array('filterEnumTemplate' => $filterEnumTemplate, 'predefinedEnumTemplates' => $predefinedTemplates['enum'])); $filterUserOptions = $filterUserOptionsResult !== false ? $filterUserOptionsResult : $filterUserOptions; //***********************// //***AlineaSol Premium***// //***********************// $customGeneratedDropdownValues = self::getCustomGeneratedDropdownValues($filterUserOptions); } //Update filter values with external filters if exists if (!empty($filterReference) && !empty($extFilters[$filterReference])) { $currentFilter['operator'] = $extFilters[$filterReference]["opp"] !== '' && $extFilters[$filterReference]["opp"] !== NULL ? $extFilters[$filterReference]["opp"] : $currentFilter['operator']; $currentFilter['parameters']['first'] = $extFilters[$filterReference]["param1"] !== '' && $extFilters[$filterReference]["param1"] !== NULL ? explode('${dollar}', $extFilters[$filterReference]["param1"]) : $currentFilter['parameters']['first']; $nextParams = explode('${comma}', $extFilters[$filterReference]["param2"]); $secondParam = count(explode('${dollar}', $nextParams[0])) > 1 ? explode('${dollar}', $nextParams[0]) : array($nextParams[0]); $thirdParam = count(explode('${dollar}', $nextParams[1])) > 1 ? explode('${dollar}', $nextParams[1]) : array($nextParams[1]); $currentFilter['parameters']['second'] = $nextParams[0] !== '' && $nextParams[0] !== NULL ? $secondParam : $filterSecondParameter; $currentFilter['parameters']['third'] = $nextParams[1] !== '' && $nextParams[1] !== NULL ? $thirdParam : $filterThirdParameter; } $filterOperator = $currentFilter['operator']; $filterFirstParameter = $currentFilter['parameters']['first']; $filterSecondParameter = $currentFilter['parameters']['second']; $filterThirdParameter = $currentFilter['parameters']['third']; if (in_array($filterBehavior, array("user_input", "visible"))) { if (substr($currentFilter['field'], -2) != 'id' && $filterType == 'char(36)') { $currentFilter['type'] = 'relate'; } switch ($filterType) { case "enum": case "radioenum": $selectedOpts = $currentFilter['parameters']['first']; //Get dropdown list field if (in_array($filterEnumOperator, array('options', 'function'))) { if (!$hasCustomEnum) { $opts = asol_Report::getEnumValues($filterEnumOperator, $filterEnumReference); $optsLabels = asol_Report::getEnumLabels($filterEnumOperator, $filterEnumReference); } else { $opts = $customGeneratedDropdownValues['opts']; $optsLabels = $customGeneratedDropdownValues['optsLabels']; } } if ($filterBehavior == "user_input") { if (in_array($currentFilter['operator'], array("like", "not like", "starts with", "ends with"))) { $theInput1 = !$hasCustomEnum ? "<input type='text' id='" . $filterReference . $dashletId . "_1' value='" . $selectedOpts[0] . "'>" : "<select id='" . $filterReference . "_1'>"; } else { $selectMultiple = in_array($currentFilter['operator'], array("one of", "not one of")) ? "multiple size=3" : ""; $theInput1 = "<select id='" . $filterReference . $dashletId . "_1' " . $selectMultiple . ">"; } if (!in_array($currentFilter['operator'], array("like", "not like", "starts with", "ends with")) || $hasCustomEnum) { foreach ($opts as $opt) { $theInput1 .= "<option value='" . $opt . "' " . (in_array($opt, $selectedOpts) ? "selected" : "") . " title='" . $optsLabels[$opt] . "'>" . $optsLabels[$opt] . "</option>"; } $theInput1 .= "</select>"; } } else { if ($filterBehavior == "visible") { $theInput1 = '<span>'; foreach ($opts as $opt) { if (in_array($opt, $selectedOpts)) { $theInput1 .= $optsLabels[$opt] . "<br>"; } } $theInput1 = substr($theInput1, 0, -4); $theInput1 .= '</span>'; } } $theInput2 = null; $theInput3 = null; break; case "multienum": $selectedOpts = $currentFilter['parameters']['first']; //Get dropdown list field if (in_array($filterEnumOperator, array('options', 'function'))) { if (!$hasCustomEnum) { $opts = asol_Report::getEnumValues($filterEnumOperator, $filterEnumReference); $optsLabels = asol_Report::getEnumLabels($filterEnumOperator, $filterEnumReference); } else { $opts = $customGeneratedDropdownValues['opts']; $optsLabels = $customGeneratedDropdownValues['optsLabels']; } } if ($filterBehavior == "user_input") { $theInput1 = "<select id='" . $filterReference . $dashletId . "_1' multiple size=3>"; foreach ($opts as $opt) { $theInput1 .= "<option value='" . $opt . "' " . (in_array($opt, $selectedOpts) ? "selected" : "") . " title='" . $optsLabels[$opt] . "'>" . $optsLabels[$opt] . "</option>"; } $theInput1 .= "</select>"; } else { if ($filterBehavior == "visible") { $theInput1 = '<span>'; foreach ($opts as $opt) { if (in_array($opt, $selectedOpts)) { $theInput1 .= $optsLabels[$opt] . "<br>"; } } $theInput1 = substr($theInput1, 0, -4); $theInput1 .= '</span>'; } } $theInput2 = null; $theInput3 = null; break; case "date": case "datetime": case "datetimecombo": case "timestamp": switch ($currentFilter['operator']) { case "equals": case "not equals": case "before date": case "after date": switch ($currentFilter['parameters']['first'][0]) { case "calendar": $date2 = $filterSecondParameter[0]; if ($date2 != "") { $date2 = $timedate->swap_formats($date2, $GLOBALS['timedate']->dbDayFormat, $userDateFormat); } if ($filterBehavior == "user_input") { $theInput1 = "<input type='hidden' id='" . $filterReference . $dashletId . "_1' value='" . $filterFirstParameter[0] . "' /><span>" . asol_ReportsUtils::translateReportsLabel('LBL_REPORT_CALENDAR') . "</span>"; $theInput2 = "<input type='text' id='" . $filterReference . $dashletId . "_2' class='calendarValue' value='" . $date2 . "' disabled='true' /><img border='0' class='calendarIcon' src='themes/default/images/jscalendar.gif' alt='Enter Date' id='" . $filterReference . $dashletId . "_trigger2'>"; $theInput2 .= "<script>Calendar.setup ({ inputField : '" . $filterReference . $dashletId . "_2' , daFormat : '" . $timedate->get_cal_date_format() . "', button : '" . $filterReference . $dashletId . "_trigger2' , singleClick : true, dateStr : '', step : 1, weekNumbers:false });</script>"; } else { if ($filterBehavior == "visible") { $theInput1 = "<span>" . asol_ReportsUtils::translateReportsLabel('LBL_REPORT_CALENDAR') . "</span>"; $theInput2 = "<span>" . $date2 . "</span>"; } } break; case "dayofweek": $selectedOpts = $filterSecondParameter; if (!$hasCustomEnum) { $dowEnumArray = self::getDOWEnumArrays(); $opts = $dowEnumArray["opts"]; $optsLabels = $dowEnumArray["optsLabels"]; } else { $opts = $customGeneratedDropdownValues['opts']; $optsLabels = $customGeneratedDropdownValues['optsLabels']; } if ($filterBehavior == "user_input") { $theInput1 = "<input type='hidden' id='" . $filterReference . $dashletId . "_1' value='" . $currentFilter['parameters']['first'][0] . "'><span>" . asol_ReportsUtils::translateReportsLabel('LBL_REPORT_DAYOFWEEK') . "</span>"; $theInput2 = "<select id='" . $filterReference . $dashletId . "_2' multiple size=3>"; foreach ($opts as $opt) { $theInput2 .= "<option value='" . $opt . "' " . (in_array($opt, $selectedOpts) ? "selected" : "") . " title='" . $optsLabels[$opt] . "'>" . $optsLabels[$opt] . "</option>"; } $theInput2 .= "</select>"; } else { if ($filterBehavior == "visible") { $theInput1 = "<span>" . asol_ReportsUtils::translateReportsLabel('LBL_REPORT_DAYOFWEEK') . "</span>"; $theInput2 = '<span>'; foreach ($opts as $opt) { if (in_array($opt, $selectedOpts)) { $theInput2 .= $optsLabels[$opt] . "<br>"; } } $theInput2 = substr($theInput2, 0, -4); $theInput2 .= '</span>'; } } break; case "weekofyear": if (!$hasCustomEnum) { $woyEnumArray = self::getWOYEnumArrays(); $opts = $woyEnumArray["opts"]; $optsLabels = $woyEnumArray["optsLabels"]; } else { $opts = $customGeneratedDropdownValues['opts']; $optsLabels = $customGeneratedDropdownValues['optsLabels']; } if ($filterBehavior == "user_input") { $theInput1 = "<input type='hidden' id='" . $filterReference . $dashletId . "_1' value='" . $currentFilter['parameters']['first'][0] . "'><span>" . asol_ReportsUtils::translateReportsLabel('LBL_REPORT_WEEKOFYEAR') . "</span>"; $theInput2 = "<select id='" . $filterReference . $dashletId . "_2'>"; foreach ($opts as $opt) { $theInput2 .= "<option value='" . $opt . "' " . ($opt == $filterSecondParameter[0] ? "selected" : "") . " title='" . $optsLabels[$opt] . "'>" . $optsLabels[$opt] . "</option>"; } $theInput2 .= "</select>"; } else { if ($filterBehavior == "visible") { $theInput1 = "<span>" . asol_ReportsUtils::translateReportsLabel('LBL_REPORT_WEEKOFYEAR') . "</span>"; $theInput2 = "<span>" . $filterSecondParameter[0] . "</span>"; } } break; case "monthofyear": $selectedOpts = $filterSecondParameter; if (!$hasCustomEnum) { $moyEnumArray = self::getMOYEnumArrays(); $opts = $moyEnumArray["opts"]; $optsLabels = $moyEnumArray["optsLabels"]; } else { $opts = $customGeneratedDropdownValues['opts']; $optsLabels = $customGeneratedDropdownValues['optsLabels']; } if ($filterBehavior == "user_input") { $theInput1 = "<input type='hidden' id='" . $filterReference . $dashletId . "_1' value='" . $currentFilter['parameters']['first'][0] . "'><span>" . asol_ReportsUtils::translateReportsLabel('LBL_REPORT_MONTHOFYEAR') . "</span>"; $theInput2 = "<select id='" . $filterReference . $dashletId . "_2' multiple size=3>"; foreach ($opts as $opt) { $theInput2 .= "<option value='" . $opt . "' " . (in_array($opt, $selectedOpts) ? "selected" : "") . " title='" . $optsLabels[$opt] . "'>" . $optsLabels[$opt] . "</option>"; } $theInput2 .= "</select>"; } else { if ($filterBehavior == "visible") { $theInput1 = "<span>" . asol_ReportsUtils::translateReportsLabel('LBL_REPORT_MONTHOFYEAR') . "</span>"; $theInput2 = '<span>'; foreach ($opts as $opt) { if (in_array($opt, $selectedOpts)) { $theInput2 .= $optsLabels[$opt] . "<br>"; } } $theInput2 = substr($theInput2, 0, -4); $theInput2 .= '</span>'; } } break; case "naturalquarterofyear": case "fiscalquarterofyear": $selectedOpts = $filterSecondParameter; if (!$hasCustomEnum) { $qoyEnumArray = self::getQOYEnumArrays(); $opts = $qoyEnumArray["opts"]; $optsLabels = $qoyEnumArray["optsLabels"]; } else { $opts = $customGeneratedDropdownValues['opts']; $optsLabels = $customGeneratedDropdownValues['optsLabels']; } $userInputLabel = $currentFilter['parameters']['first'][0] == "naturalquarterofyear" ? "LBL_REPORT_NATURALQUARTEROFYEAR" : "LBL_REPORT_FISCALQUARTEROFYEAR"; if ($filterBehavior == "user_input") { $theInput1 = "<input type='hidden' id='" . $filterReference . $dashletId . "_1' value='" . $currentFilter['parameters']['first'][0] . "'><span>" . asol_ReportsUtils::translateReportsLabel($userInputLabel) . "</span>"; $theInput2 = "<select id='" . $filterReference . $dashletId . "_2' multiple size=3>"; foreach ($opts as $opt) { $theInput2 .= "<option value='" . $opt . "' " . (in_array($opt, $selectedOpts) ? "selected" : "") . " title='" . $optsLabels[$opt] . "'>" . $optsLabels[$opt] . "</option>"; } $theInput2 .= "</select>"; } else { if ($filterBehavior == "visible") { $theInput1 = "<span>" . asol_ReportsUtils::translateReportsLabel($userInputLabel) . "</span>"; $theInput2 = '<span>'; foreach ($opts as $opt) { if (in_array($opt, $selectedOpts)) { $theInput2 .= $optsLabels[$opt] . "<br>"; } } $theInput2 = substr($theInput2, 0, -4); $theInput2 .= '</span>'; } } break; case "naturalyear": case "fiscalyear": $userInputLabel = $currentFilter['parameters']['first'][0] == "naturalyear" ? "LBL_REPORT_NATURALYEAR" : "LBL_REPORT_FISCALYEAR"; if (empty($filterSecondParameter)) { $filterSecondParameter = array(date("Y")); } if ($filterBehavior == "user_input") { $theInput1 = "<input type='hidden' id='" . $filterReference . $dashletId . "_1' value='" . $currentFilter['parameters']['first'][0] . "'><span>" . asol_ReportsUtils::translateReportsLabel($userInputLabel) . "</span>"; $theInput2 = "<input type='text' id='" . $filterReference . $dashletId . "_2' style='width:80px' value='" . $filterSecondParameter[0] . "'>"; } else { if ($filterBehavior == "visible") { $theInput1 = "<span>" . asol_ReportsUtils::translateReportsLabel($userInputLabel) . "</span>"; $theInput2 = "<span>" . $filterSecondParameter[0] . "</span>"; } } break; } $theInput3 = null; break; case "before date": case "after date": $date1 = $currentFilter['parameters']['first'][0]; if ($date1 != "") { $date1 = $timedate->swap_formats($date1, $GLOBALS['timedate']->dbDayFormat, $userDateFormat); } if ($filterBehavior == "user_input") { $theInput1 = "<input type='text' id='" . $filterReference . $dashletId . "_1' class='calendarValue' value='" . $date1 . "' disabled='true' /><img border='0' class='calendarIcon' src='themes/default/images/jscalendar.gif' alt='Enter Date' id='" . $filterReference . $dashletId . "_trigger1'>"; $theInput1 .= "<script>Calendar.setup ({ inputField : '" . $filterReference . $dashletId . "_1' , daFormat : '" . $timedate->get_cal_date_format() . "', button : '" . $filterReference . $dashletId . "_trigger1' , singleClick : true, dateStr : '', step : 1, weekNumbers:false });</script>"; } else { if ($filterBehavior == "visible") { $theInput1 = "<span>" . $date1 . "</span>"; } } $theInput2 = null; $theInput3 = null; break; case "between": case "not between": $input1 = $filterSecondParameter[0]; $input2 = $filterThirdParameter[0]; switch ($currentFilter['parameters']['first'][0]) { case "calendar": if (!$timedate->check_matching_format($input1, $userDateFormat) && $input1 != "") { $input1 = $timedate->swap_formats($input1, $GLOBALS['timedate']->dbDayFormat, $userDateFormat); } if (!$timedate->check_matching_format($input2, $userDateFormat) && $input1 != "") { $input2 = $timedate->swap_formats($input2, $GLOBALS['timedate']->dbDayFormat, $userDateFormat); } if ($filterBehavior == "user_input") { $theInput1 = "<input type='hidden' id='" . $filterReference . $dashletId . "_1' value='" . $currentFilter['parameters']['first'][0] . "' /><span>" . asol_ReportsUtils::translateReportsLabel('LBL_REPORT_CALENDAR') . "</span>"; $theInput2 = "<input type='text' id='" . $filterReference . $dashletId . "_2' class='calendarValue' value='" . $input1 . "' disabled='true' /><img border='0' class='calendarIcon' src='themes/default/images/jscalendar.gif' alt='Enter Date' id='" . $filterReference . $dashletId . "_trigger2'>"; $theInput2 .= "<script>Calendar.setup ({ inputField : '" . $filterReference . $dashletId . "_2' , daFormat : '" . $timedate->get_cal_date_format() . "', button : '" . $filterReference . $dashletId . "_trigger2' , singleClick : true, dateStr : '', step : 1, weekNumbers:false });</script>"; $theInput3 = "<input type='text' id='" . $filterReference . $dashletId . "_3' class='calendarValue' value='" . $input2 . "' disabled='true' /><img border='0' class='calendarIcon' src='themes/default/images/jscalendar.gif' alt='Enter Date' id='" . $filterReference . $dashletId . "_trigger3'>"; $theInput3 .= "<script>Calendar.setup ({ inputField : '" . $filterReference . $dashletId . "_3' , daFormat : '" . $timedate->get_cal_date_format() . "', button : '" . $filterReference . $dashletId . "_trigger3' , singleClick : true, dateStr : '', step : 1, weekNumbers:false });</script>"; } else { if ($filterBehavior == "visible") { $theInput1 = "<span>" . asol_ReportsUtils::translateReportsLabel('LBL_REPORT_CALENDAR') . "</span>"; $theInput2 = "<span>" . $input1 . "</span>"; $theInput3 = "<span>" . $input2 . "</span>"; } } break; case "weekofyear": if (!$hasCustomEnum) { $woyEnumArray = self::getWOYEnumArrays(); $opts = $woyEnumArray["opts"]; $optsLabels = $woyEnumArray["optsLabels"]; } else { $opts = $customGeneratedDropdownValues['opts']; $optsLabels = $customGeneratedDropdownValues['optsLabels']; } if ($filterBehavior == "user_input") { $theInput1 = "<input type='hidden' id='" . $filterReference . $dashletId . "_1' value='" . $currentFilter['parameters']['first'][0] . "'><span>" . asol_ReportsUtils::translateReportsLabel('LBL_REPORT_WEEKOFYEAR') . "</span>"; $theInput2 = "<select id='" . $filterReference . $dashletId . "_2'>"; foreach ($opts as $opt) { $theInput2 .= "<option value='" . $opt . "' " . ($opt == $input1 ? "selected" : "") . " title='" . $optsLabels[$opt] . "'>" . $optsLabels[$opt] . "</option>"; } $theInput2 .= "</select>"; $theInput3 = "<span style='display: block'>" . asol_ReportsUtils::translateReportsLabel('LBL_REPORT_AND') . "</span>"; $theInput3 .= "<select id='" . $filterReference . $dashletId . "_3'>"; foreach ($opts as $opt) { $theInput3 .= "<option value='" . $opt . "' " . ($opt == $input2 ? "selected" : "") . " title='" . $optsLabels[$opt] . "'>" . $optsLabels[$opt] . "</option>"; } $theInput3 .= "</select>"; } else { if ($filterBehavior == "visible") { $theInput1 = "<span>" . asol_ReportsUtils::translateReportsLabel('LBL_REPORT_WEEKOFYEAR') . "</span>"; $theInput2 = "<span>" . $input1 . "</span>"; $theInput3 = "<span style='display: block'>" . asol_ReportsUtils::translateReportsLabel('LBL_REPORT_AND') . "</span>"; $theInput3 .= "<span>" . $input2 . "</span>"; } } break; case "naturalyear": case "fiscalyear": $userInputLabel = $currentFilter['parameters']['first'][0] == "naturalyear" ? "LBL_REPORT_NATURALYEAR" : "LBL_REPORT_FISCALYEAR"; if (empty($input1)) { $input1 = date("Y"); } if (empty($input2)) { $input2 = date("Y"); } if ($filterBehavior == "user_input") { $theInput1 = "<input type='hidden' id='" . $filterReference . $dashletId . "_1' value='" . $currentFilter['parameters']['first'][0] . "'><span>" . asol_ReportsUtils::translateReportsLabel($userInputLabel) . "</span>"; $theInput2 = "<input type='text' id='" . $filterReference . $dashletId . "_2' style='width:80px' value='" . $input1 . "'>"; $theInput3 = "<span style='display: block'>" . asol_ReportsUtils::translateReportsLabel('LBL_REPORT_AND') . "</span>"; $theInput3 .= "<input type='text' id='" . $filterReference . $dashletId . "_3' style='width:80px' value='" . $input2 . "'>"; } else { if ($filterBehavior == "visible") { $theInput1 = "<span>" . asol_ReportsUtils::translateReportsLabel($userInputLabel) . "</span>"; $theInput2 = "<span>" . $input1 . "</span>"; $theInput3 = "<span style='display: block'>" . asol_ReportsUtils::translateReportsLabel('LBL_REPORT_AND') . "</span>"; $theInput3 .= "<span>" . $input2 . "</span>"; } } break; } break; case "last": case "not last": if (!$hasCustomEnum) { $doaEnumArray = self::getDateOperatorArrays(); $opts = $doaEnumArray["opts"]; $optsLabels = $doaEnumArray["optsLabels"]; } else { $opts = $customGeneratedDropdownValues['opts']; $optsLabels = $customGeneratedDropdownValues['optsLabels']; } if ($filterBehavior == "user_input") { $theInput1 = "<select id='" . $filterReference . $dashletId . "_1' onChange='if (this.selectedIndex >= 7) { document.getElementById(\"" . $filterReference . "_2\").style.display=\"none\"; } else { document.getElementById(\"" . $filterReference . "_2\").style.display=\"inline\"; } '>"; foreach ($opts as $opt) { $theInput1 .= "<option value='" . $opt . "' " . ($opt == $currentFilter['parameters']['first'][0] ? "selected" : "") . " title='" . $optsLabels[$opt] . "'>" . $optsLabels[$opt] . "</option>"; } $theInput1 .= "</select>"; } else { if ($filterBehavior == "visible") { $theInput1 = !empty($extFilters[$filterReference]["param1"]) ? '<span>' . $optsLabels[$extFilters[$filterReference]["param1"]] . '</span>' : '<span>' . $optsLabels[$currentFilter['parameters']['first'][0]] . '</span>'; } } switch ($currentFilter['parameters']['first'][0]) { case "day": case "week": case "month": case "Nquarter": case "Fquarter": case "Nyear": case "Fyear": if ($filterBehavior == "user_input") { $theInput2 = '<input id="' . $filterReference . $dashletId . '_2" type="text" value="' . $filterSecondParameter[0] . '">'; } else { if ($filterBehavior == "visible") { $theInput2 = '<span>' . $filterSecondParameter[0] . '</span>'; } } break; default: if ($filterBehavior == "user_input") { $theInput2 = '<input id="' . $filterReference . $dashletId . '_2" style="display: none;" type="text" value="' . $filterSecondParameter[0] . '">'; } else { if ($filterBehavior == "visible") { $theInput2 = '<span>' . $filterSecondParameter[0] . '</span>'; } } break; } $theInput3 = null; break; case "this": case "not this": if (!$hasCustomEnum) { $rdoaEnumArray = self::getReducedDateOperatorArrays(); $opts = $rdoaEnumArray["opts"]; $optsLabels = $rdoaEnumArray["optsLabels"]; } else { $opts = $customGeneratedDropdownValues['opts']; $optsLabels = $customGeneratedDropdownValues['optsLabels']; } if ($filterBehavior == "user_input") { $theInput1 = "<select id='" . $filterReference . $dashletId . "_1'>"; foreach ($opts as $opt) { $theInput1 .= "<option value='" . $opt . "' " . ($opt == $currentFilter['parameters']['first'][0] ? "selected" : "") . " title='" . $optsLabels[$opt] . "'>" . $optsLabels[$opt] . "</option>"; } $theInput1 .= "</select>"; } else { if ($filterBehavior == "visible") { $theInput1 = '<span>' . $optsLabels[$currentFilter['parameters']['first'][0]] . '</span>'; } } $theInput2 = null; $theInput3 = null; break; case "next": case "not next": case "these": if (!$hasCustomEnum) { $rdoaEnumArray = self::getReducedDateOperatorArrays(); $opts = $rdoaEnumArray["opts"]; $optsLabels = $rdoaEnumArray["optsLabels"]; } else { $opts = $customGeneratedDropdownValues['opts']; $optsLabels = $customGeneratedDropdownValues['optsLabels']; } if ($filterBehavior == "user_input") { $theInput1 = "<select id='" . $filterReference . $dashletId . "_1'>"; foreach ($opts as $opt) { $theInput1 .= "<option value='" . $opt . "' " . ($opt == $currentFilter['parameters']['first'][0] ? "selected" : "") . " title='" . $optsLabels[$opt] . "'>" . $optsLabels[$opt] . "</option>"; } $theInput1 .= "</select>"; } else { if ($filterBehavior == "visible") { $theInput1 = '<span>' . $optsLabels[$currentFilter['parameters']['first'][0]] . '</span>'; } } if ($filterBehavior == "user_input") { $theInput2 = '<input id="' . $filterReference . $dashletId . '_2" type="text" value="' . $filterSecondParameter[0] . '">'; } else { if ($filterBehavior == "visible") { $theInput2 = '<span>' . $filterSecondParameter[0] . '</span>'; } } $theInput3 = null; break; } break; case "bool": case "tinyint(1)": if (!empty($extFilters[$filterReference]["param1"])) { $currentFilter['parameters']['first'][0] = $extFilters[$filterReference]["param1"]; } if ($filterBehavior == "user_input") { $theInput1 = $currentFilter['parameters']['first'][0] == "true" ? "<select id='" . $filterReference . $dashletId . "_1' name='" . $filterReference . "_1'><option value='true' selected>" . asol_ReportsUtils::translateReportsLabel("LBL_REPORT_TRUE") . "</option><option value='false'>" . asol_ReportsUtils::translateReportsLabel("LBL_REPORT_FALSE") . "</option></select>" : "<select id='" . $filterReference . $dashletId . "_1'><option value='true'>" . asol_ReportsUtils::translateReportsLabel("LBL_REPORT_TRUE") . "</option><option value='false' selected>" . asol_ReportsUtils::translateReportsLabel("LBL_REPORT_FALSE") . "</option></select>"; } else { if ($filterBehavior == "visible") { $theInput1 = "<span>" . $currentFilter['parameters']['first'][0] . "</span>"; } } $theInput2 = null; $theInput3 = null; break; case "relate": if (!empty($extFilters[$filterReference]["param1"])) { $currentFilter['parameters']['first'][0] = $extFilters[$filterReference]["param1"]; } $tmpField = explode(".", $filterField); $relateField = count($tmpField) == 2 ? $tmpField[1] : $filterField; if ($auditedReport) { if ($filterField == 'parent_id') { $relateModule = $report_module; } else { if ($filterField == 'created_by') { $relateModule = "Users"; } } } else { $relateModule = asol_Report::getRelateFieldModule($report_module, $relateField); } if ($filterOperator === 'my items' && empty($currentFilter['parameters']['first'][0])) { $moduleFieldValue = $current_user->id; } else { $moduleFieldValue = $currentFilter['parameters']['first'][0]; } $relateId = "id"; $relateName = $relateModule == 'Users' ? "user_name" : "name"; $fieldInputId = $filterReference . $dashletId . "_1"; $fieldInputName = $filterReference . $dashletId . "_1_name"; //Create new ModuleObject and get Name field Value $moduleFieldName = BeanFactory::getBean($relateModule, $moduleFieldValue)->{$relateName}; //Create new ModuleObject and get Name field Value $popup_selector = "<input type='hidden' id='" . $filterReference . $dashletId . "_1" . "' value='" . $moduleFieldValue . "'><input readonly type='text' autocomplete='off' title='' value='" . $moduleFieldName . "' id='" . $filterReference . $dashletId . "_1_name" . "'>\r\n\t\t\t\t\t\t<button type='button' onclick=\"open_popup('" . $relateModule . "', 600, 400, '', true, false, {'call_back_function':'set_return','form_name':'criteria_form','field_to_name_array':{'" . $relateId . "':'" . $fieldInputId . "','" . $relateName . "':'" . $fieldInputName . "'}}, 'single', true);\" class='button' title='" . $app_strings['LBL_SELECT_BUTTON_LABEL'] . "'><img src='themes/default/images/id-ff-select.png'></button>\r\n\t\t\t\t\t\t<button type='button' onclick=\"document.getElementById('" . $filterReference . $dashletId . "_1_name').value =''; document.getElementById('" . $filterReference . $dashletId . "_1').value = ''\" value='Clear'><img src='themes/default/images/id-ff-clear.png'></button>"; if ($filterBehavior == "user_input") { $theInput1 = $popup_selector; } else { if ($filterBehavior == "visible") { $theInput1 = "<span>" . $moduleFieldName . "</span>"; } } $theInput2 = null; $theInput3 = null; break; default: if ($filterBehavior == "user_input") { $selectMultiple = in_array($currentFilter['operator'], array("one of", "not one of")) ? "multiple size=3" : ""; if (!$hasCustomEnum) { $theInput1 = '<input id="' . $filterReference . $dashletId . '_1" type="text" value="' . $currentFilter['parameters']['first'][0] . '">'; } else { $selectedOpts = $currentFilter['parameters']['first']; $opts = $customGeneratedDropdownValues['opts']; $optsLabels = $customGeneratedDropdownValues['optsLabels']; $theInput1 = '<select id="' . $filterReference . $dashletId . '_1" ' . $selectMultiple . '>'; foreach ($opts as $opt) { $theInput1 .= "<option value='" . $opt . "' " . (in_array($opt, $selectedOpts) ? "selected" : "") . " title='" . $optsLabels[$opt] . "'>" . $optsLabels[$opt] . "</option>"; } $theInput1 .= "</select>"; } } else { if ($filterBehavior == "visible") { if (!$hasCustomEnum) { $theInput1 = '<span>' . $currentFilter['parameters']['first'][0] . '</span>'; } else { $selectedOpts = $currentFilter['parameters']['first']; $opts = $customGeneratedDropdownValues['opts']; $optsLabels = $customGeneratedDropdownValues['optsLabels']; $theInput1 = '<span>'; foreach ($opts as $opt) { if (in_array($opt, $selectedOpts)) { $theInput1 .= $optsLabels[$opt] . "<br>"; } } $theInput1 = substr($theInput1, 0, -4); $theInput1 .= '</span>'; } } } $theInput2 = null; $theInput3 = null; break; } if ($filterBehavior == "user_input") { if ($theInput3 != null) { $filtersHiddenInputs .= $filterReference . '${dp}' . $currentFilter['operator'] . '${dp}3${pipe}'; } else { if ($theInput2 != null) { $filtersHiddenInputs .= $filterReference . '${dp}' . $currentFilter['operator'] . '${dp}2${pipe}'; } else { $filtersHiddenInputs .= $filterReference . '${dp}' . $currentFilter['operator'] . '${dp}1${pipe}'; } } } if ($filterBehavior == "visible") { $filterLabel = "LBL_REPORT_" . strtoupper(str_replace(" ", "_", $currentFilter['operator'])) . "_" . strtoupper(str_replace(" ", "_", $currentFilter['parameters']['first'][0])); $filterLabel .= !empty($filterSecondParameter[0]) ? "_" . $filterSecondParameter[0] : ""; if (empty($filterLabelValue)) { $filterLabel = "LBL_REPORT_" . strtoupper(str_replace(" ", "_", $currentFilter['operator'])); } } else { $filterLabel = "LBL_REPORT_" . strtoupper(str_replace(" ", "_", $currentFilter['operator'])); } $filterLabelValue = asol_ReportsUtils::translateReportsLabel($filterLabel); //***********************// //***AlineaSol Premium***// //***********************// $extraParams = array('multiLanguage' => $currentFilter['language']); $returnedPremiumAlias = asol_ReportsUtils::managePremiumFeature("multiLanguageReport", "reportFunctions.php", "getMultiLanguageLabel", $extraParams); $currentFilter['untranslatedAlias'] = $currentFilter['alias']; $currentFilter['alias'] = $returnedPremiumAlias !== false ? $returnedPremiumAlias : $currentFilter['alias']; //***********************// //***AlineaSol Premium***// //***********************// $filtersPanel[] = array("type" => $val, "label" => $currentFilter['alias'], "reference" => $filterReference, "opp" => $currentFilter['operator'], "input1" => $theInput1, "input2" => $theInput2, "input3" => $theInput3, "genLabel" => !empty($filterLabelValue) ? $filterLabelValue : $currentFilter['operator']); } } $filtersHiddenInputs = urlencode(substr($filtersHiddenInputs, 0, -7)); $filtersHiddenInputs = !empty($filtersHiddenInputs) ? $filtersHiddenInputs : false; return array("filterValues" => $filters, "filtersPanel" => $filtersPanel, "filtersHiddenInputs" => $filtersHiddenInputs); }
public static function getSqlSelectQuery(&$fieldValues, &$chartInfo, $predefinedTemplates, $alternativeDb, $reportTable, $hourOffset, $quarterMonth, $weekStart, $audited_report, $displayTotals, $displaySubtotals, $fieldsByRef, $filtersByRef, $aliasIndexTable = array(), $aliasSuffix = '') { //OBTENEMOS LA CAUSULA "SELECT" con sus ALIAS $sqlSelect = "SELECT "; $columns = array(); $types = array(); $hasGrouped = false; $hasTotals = false; $hasFunctionWithSQL = false; $groupSubTotalField = null; $groupSubTotalFieldAscSort = null; $sqlTotals = "SELECT "; $sqlTotalsC = "SELECT "; $totals = array(); //CREAR Y USAR SUBLISTA CON LOS CAMPOS QUE IRÁN EN EL RESULSET UNICAMENTE $resulsetFields = array(); $referenceAlias = array(); $processedChartFields = array(); foreach ($fieldValues['tables'][0]['data'] as $i => $currentValues) { if ($currentValues['function'] != "0" && !empty($currentValues['sql'])) { $hasFunctionWithSQL = true; } $hasGrouped = $hasGrouped || in_array($currentValues['grouping'], array('Grouped', 'Minute Grouped', 'Quarter Hour Grouped', 'Hour Grouped', 'Day Grouped', 'DoW Grouped', 'WoY Grouped', 'Month Grouped', 'Natural Quarter Grouped', 'Fiscal Quarter Grouped', 'Natural Year Grouped', 'Fiscal Year Grouped')); } foreach ($fieldValues['tables'][0]['data'] as $i => &$currentValues) { //***********************// //***AlineaSol Premium***// //***********************// $extraParams = array('currentSql' => $currentValues['sql'], 'fieldsByRef' => $fieldsByRef, 'suppressChars' => false); $currentSql = asol_ReportsUtils::managePremiumFeature("sqlWithReferences", "reportFunctions.php", "replaceSqlReferenceByValue", $extraParams); $currentValues['sql'] = $currentSql !== false ? $currentSql : $currentValues['sql']; //***********************// //***AlineaSol Premium***// //***********************// //***********************// //***AlineaSol Premium***// //***********************// $extraParams = array('multiLanguage' => $currentValues['language']); $returnedPremiumAlias = asol_ReportsUtils::managePremiumFeature("multiLanguageReport", "reportFunctions.php", "getMultiLanguageLabel", $extraParams); $currentValues['untranslatedAlias'] = trim($currentValues['alias']); $currentValues['alias'] = $returnedPremiumAlias !== false ? $returnedPremiumAlias : trim($currentValues['alias']); //***********************// //***AlineaSol Premium***// //***********************// $referenceAlias[$currentValues['fieldReference']] = $currentValues['alias']; $currentValues['enumLabels'] = in_array($currentValues['enumOperator'], array('options', 'function')) ? asol_Report::getEnumLabels($currentValues['enumOperator'], $currentValues['enumReference']) : array(); //***********************// //***AlineaSol Premium***// //***********************// $enumLabels = asol_ReportsUtils::managePremiumFeature("multiLanguageReport", "reportFunctions.php", "getFormatTypeEnumValues", array('enumLabels' => $currentValues['enumLabels'], 'extraFormat' => $currentValues['format']['extra'], 'template' => $currentValues['templates']['enum'], 'enumTemplates' => $predefinedTemplates['enum'])); $currentValues['enumLabels'] = $enumLabels !== false ? $enumLabels : $currentValues['enumLabels']; //***********************// //***AlineaSol Premium***// //***********************// //***********************// //***AlineaSol Premium***// //***********************// $currentPhp = asol_ReportsUtils::managePremiumFeature("predefinedTemplates", "reportFunctions.php", "getPhpTemplateValue", array('currentPHP' => $currentValues['php'], 'template' => $currentValues['templates']['php'], 'phpTemplates' => $predefinedTemplates['php'])); $currentValues['php'] = $currentPhp !== false ? $currentPhp : $currentValues['php']; //***********************// //***AlineaSol Premium***// //***********************// //***********************// //***AlineaSol Premium***// //***********************// if (isset($currentValues['format']['extra']['thresholds'])) { $currentThreshold = asol_ReportsUtils::managePremiumFeature("predefinedTemplates", "reportFunctions.php", "getThresholdTemplateValue", array('currentThreshold' => $currentValues['format']['extra']['thresholds'], 'template' => $currentValues['templates']['threshold'], 'thresholdTemplates' => $predefinedTemplates['threshold'])); $currentValues['format']['extra']['thresholds'] = $currentThreshold !== false ? $currentThreshold : $currentValues['format']['extra']['thresholds']; } //***********************// //***AlineaSol Premium***// //***********************// $sqlFunction = self::replaceReportsFieldVars($currentValues['sql'], $reportTable, $aliasIndexTable, $audited_report, $alternativeDb); if (!$audited_report) { $table = count(explode(".", $currentValues['field'])) == 1 ? $reportTable . $aliasSuffix . "." : ""; } else { $table = count(explode(".", $currentValues['field'])) == 1 ? $reportTable . "_audit." . $aliasSuffix : ""; } $fieldValues['tables'][0]['data'][$i]['chartOriginalFieldName'] = $currentValues['field']; $fieldValues['tables'][0]['data'][$i]['field'] = $table . $currentValues['field']; if ($currentValues['grouping'] !== '0') { switch ($currentValues['grouping']) { case "Detail": case "Grouped": $whereFunction = null; break; case "Minute Detail": case "Minute Grouped": if (in_array($currentValues['type'], array('datetime', 'datetimecombo')) && $hourOffset != 0) { $whereFunction = 'MINUTE(DATE_ADD(${this}, INTERVAL ' . $hourOffset * -1 . ' SECOND))'; } else { $whereFunction = 'MINUTE(${this})'; } break; case "Quarter Hour Detail": case "Quarter Hour Grouped": if (in_array($currentValues['type'], array('datetime', 'datetimecombo')) && $hourOffset != 0) { $whereFunction = 'TRUNCATE((MINUTE(DATE_ADD(${this}, INTERVAL ' . $hourOffset * -1 . ' SECOND))/15), 0)'; } else { $whereFunction = 'TRUNCATE((MINUTE(${this})/15), 0)'; } break; case "Hour Detail": case "Hour Grouped": if (in_array($currentValues['type'], array('datetime', 'datetimecombo')) && $hourOffset != 0) { $whereFunction = 'HOUR(DATE_ADD(${this}, INTERVAL ' . $hourOffset * -1 . ' SECOND))'; } else { $whereFunction = 'HOUR(${this})'; } break; case "Day Detail": case "Day Grouped": if (in_array($currentValues['type'], array('datetime', 'datetimecombo')) && $hourOffset != 0) { $whereFunction = 'DATE(DATE_ADD(${this}, INTERVAL ' . $hourOffset * -1 . ' SECOND))'; } else { $whereFunction = 'DATE(${this})'; } break; case "DoW Detail": case "DoW Grouped": if (in_array($currentValues['type'], array('datetime', 'datetimecombo')) && $hourOffset != 0) { $whereFunction = 'WEEKDAY(DATE_ADD(${this}, INTERVAL ' . $hourOffset * -1 . ' SECOND))'; } else { $whereFunction = 'WEEKDAY(${this})'; } break; case "WoY Detail": case "WoY Grouped": $weekStartsOn = $weekStart == '0' ? 2 : 7; if (in_array($currentValues['type'], array('datetime', 'datetimecombo')) && $hourOffset != 0) { $whereFunction = 'CONCAT(YEAR(DATE_ADD(${this}, INTERVAL ' . $hourOffset * -1 . ' SECOND)), WEEK(DATE_ADD(${this}, INTERVAL ' . $hourOffset * -1 . ' SECOND), ' . $weekStartsOn . '))'; } else { $whereFunction = 'CONCAT(YEAR(${this}), WEEK(${this}, ' . $weekStartsOn . '))'; } break; case "Month Detail": case "Month Grouped": if (in_array($currentValues['type'], array('datetime', 'datetimecombo')) && $hourOffset != 0) { $whereFunction = 'EXTRACT(YEAR_MONTH FROM DATE_ADD(${this}, INTERVAL ' . $hourOffset * -1 . ' SECOND))'; } else { $whereFunction = 'EXTRACT(YEAR_MONTH FROM ${this})'; } break; case "Natural Quarter Detail": case "Natural Quarter Grouped": if (in_array($currentValues['type'], array('datetime', 'datetimecombo')) && $hourOffset != 0) { $whereFunction = 'CONCAT(YEAR(DATE_ADD(${this}, INTERVAL ' . $hourOffset * -1 . ' SECOND)), QUARTER(DATE_ADD(${this}, INTERVAL ' . $hourOffset * -1 . ' SECOND)))'; } else { $whereFunction = 'CONCAT(YEAR(${this}), QUARTER(${this}))'; } break; case "Fiscal Quarter Detail": case "Fiscal Quarter Grouped": $quarterMonth = empty($quarterMonth) ? 0 : 12 - (intval($quarterMonth) - 1); if (in_array($currentValues['type'], array('datetime', 'datetimecombo')) && $hourOffset != 0) { $whereFunction = 'CONCAT(YEAR(DATE_SUB(DATE_ADD(${this}, INTERVAL ' . $hourOffset * -1 . ' SECOND), INTERVAL ' . (12 - $quarterMonth) . ' MONTH)), QUARTER(DATE_ADD(DATE_ADD(${this}, INTERVAL ' . $hourOffset * -1 . ' SECOND), INTERVAL ' . $quarterMonth . ' MONTH)))'; } else { $whereFunction = 'CONCAT(YEAR(DATE_SUB(${this}, INTERVAL ' . (12 - $quarterMonth) . ' MONTH)), QUARTER(DATE_ADD(${this}, INTERVAL ' . $quarterMonth . ' MONTH)))'; } break; case "Natural Year Detail": case "Natural Year Grouped": if (in_array($currentValues['type'], array('datetime', 'datetimecombo')) && $hourOffset != 0) { $whereFunction = 'YEAR(DATE_ADD(${this}, INTERVAL ' . $hourOffset * -1 . ' SECOND))'; } else { $whereFunction = 'YEAR(${this})'; } break; case "Fiscal Year Detail": case "Fiscal Year Grouped": $quarterMonth = empty($quarterMonth) ? 0 : 12 - (intval($quarterMonth) - 1); if (in_array($currentValues['type'], array('datetime', 'datetimecombo')) && $hourOffset != 0) { $whereFunction = '(YEAR(DATE_SUB(DATE_ADD(${this}, INTERVAL ' . $hourOffset * -1 . ' SECOND), INTERVAL ' . (12 - $quarterMonth) . ' MONTH)))'; } else { $whereFunction = '(YEAR(DATE_SUB(${this}, INTERVAL ' . (12 - $quarterMonth) . ' MONTH)))'; } break; } if ($whereFunction !== null) { self::updateMySqlFunctionField($whereFunction, null, $fieldValues['tables'][0]['data'][$i]['field']); } } //****************************************// //***Fields Without Aggreagted Function***// //****************************************// if (in_array($currentValues['function'], array("0", "undefined"))) { if (!empty($sqlFunction)) { //ASOL CALCULATED self::updateMySqlFunctionField($sqlFunction, null, $fieldValues['tables'][0]['data'][$i]['field']); if (in_array(strtolower($currentValues['visible']), array("yes", "html"))) { $sqlSelect .= $fieldValues['tables'][0]['data'][$i]['field'] . " AS '" . $currentValues['alias'] . "',"; } } else { if (in_array(strtolower($currentValues['visible']), array("yes", "html"))) { $sqlSelect .= $fieldValues['tables'][0]['data'][$i]['field'] . " AS '" . $currentValues['alias'] . "',"; } } if (in_array(strtolower($currentValues['visible']), array("yes", "html"))) { $columns[] = $currentValues['alias']; $types[] = !empty($currentValues['format']['type']) ? $currentValues['format']['type'] : (!empty($currentValues['type']) ? $currentValues['type'] : ""); $columnsU[] = $currentValues['untranslatedAlias']; $columnsO[] = $fieldValues['tables'][0]['data'][$i]['chartOriginalFieldName']; $columnsN[] = $fieldValues['tables'][0]['data'][$i]['notModifiedFieldName']; $columnsI[] = $currentValues['index']; $resulsetFields[] = $fieldValues['tables'][0]['data'][$i]; } //*************************************// //***Fields With Aggregated Function***// //*************************************// } else { if ($groupSubTotalField == null) { $groupSubTotalField = $currentValues['alias']; $groupSubTotalFieldAscSort = $currentValues['sortDirection']; } $fieldValues['tables'][0]['data'][$i]['field'] = $currentValues['function'] . "(" . $fieldValues['tables'][0]['data'][$i]['field'] . ")"; if (!empty($sqlFunction)) { //ASOL CALCULATED self::updateMySqlFunctionField($sqlFunction, null, $fieldValues['tables'][0]['data'][$i]['field']); if (in_array(strtolower($currentValues['visible']), array("yes", "html"))) { if ($hasGrouped && $displaySubtotals) { $sqlSelect .= $fieldValues['tables'][0]['data'][$i]['field'] . " AS '" . $currentValues['alias'] . "',"; } $sqlTotals .= $fieldValues['tables'][0]['data'][$i]['field'] . " AS '" . $currentValues['alias'] . "',"; } } else { if (in_array(strtolower($currentValues['visible']), array("yes", "html"))) { if ($hasGrouped && $displaySubtotals) { $sqlSelect .= $fieldValues['tables'][0]['data'][$i]['field'] . " AS '" . $currentValues['alias'] . "',"; } $sqlTotals .= $fieldValues['tables'][0]['data'][$i]['field'] . " AS '" . $currentValues['alias'] . "',"; } } if (in_array(strtolower($currentValues['visible']), array("yes", "html"))) { if ($hasGrouped && $displaySubtotals) { $columns[] = $currentValues['alias']; $types[] = !empty($currentValues['format']['type']) ? $currentValues['format']['type'] : (!empty($currentValues['type']) ? $currentValues['type'] : ""); $columnsU[] = $currentValues['untranslatedAlias']; $columnsO[] = $fieldValues['tables'][0]['data'][$i]['chartOriginalFieldName']; $columnsN[] = $fieldValues['tables'][0]['data'][$i]['notModifiedFieldName']; $columnsI[] = $currentValues['index']; } if (!$hasGrouped || $displaySubtotals) { $resulsetFields[] = $fieldValues['tables'][0]['data'][$i]; } if ($displayTotals) { $totals[] = $fieldValues['tables'][0]['data'][$i]; } $hasTotals = true; } } //Check if chart is displayable on Charts Div $availableCharts = array(); foreach ($chartInfo as $cKey => $cInfoData) { $fInfoData = $fieldValues['tables'][0]['data'][$i]; if ($audited_report) { $cInfoData['field'] = count(explode(".", $cInfoData['field'])) > 1 ? $cInfoData['field'] : $reportTable . "_audit." . $cInfoData['field']; } $isCurrentChart = $fInfoData['notModifiedFieldName'] == $cInfoData['field'] && $fInfoData['index'] == $cInfoData['index']; $isCurrentZChart = $fInfoData['notModifiedFieldName'] == $cInfoData['zAxis'] && $fInfoData['index'] == $cInfoData['zIndex']; if ($cInfoData['display'] == 'yes' && ($isCurrentChart || $isCurrentZChart)) { $availableCharts[] = array('displayable' => true, 'alias' => $cInfoData['label'] . ($isCurrentZChart ? '_z' : '')); } foreach ($cInfoData['subcharts'] as $subChartKey => $subChartValues) { $subChartData = $subChartValues['data']; if ($audited_report) { $subChartData['field'] = count(explode(".", $subChartData['field'])) > 1 ? $subChartData['field'] : $reportTable . "_audit." . $subChartData['field']; } $isCurrentChart = $fInfoData['notModifiedFieldName'] == $subChartData['field'] && $fInfoData['index'] == $subChartData['index']; $isCurrentZChart = $fInfoData['notModifiedFieldName'] == $subChartData['zAxis'] && $fInfoData['index'] == $subChartData['zIndex']; if ($subChartData['display'] == 'yes' && ($isCurrentChart || $isCurrentZChart)) { $availableCharts[] = array('displayable' => true, 'alias' => $cInfoData['label'] . '_' . $subChartKey . ($isCurrentZChart ? '_z' : '')); } } } foreach ($availableCharts as $availableChart) { if ($availableChart['displayable']) { if (in_array($availableChart['alias'], $processedChartFields)) { continue; } if (!empty($sqlFunction)) { //ASOL CALCULATED if (self::updateMySqlFunctionField($sqlFunction, $currentValues['function'] === '0' ? null : $currentValues['function'], $fieldValues['tables'][0]['data'][$i]['chartOriginalFieldName'])) { $sqlTotalsC .= $fieldValues['tables'][0]['data'][$i]['field'] . " AS '" . $availableChart['alias'] . "',"; } else { $sqlTotalsC .= $fieldValues['tables'][0]['data'][$i]['chartOriginalFieldName'] . " AS '" . $availableChart['alias'] . "',"; } } else { $sqlTotalsC .= $fieldValues['tables'][0]['data'][$i]['field'] . " AS '" . $availableChart['alias'] . "',"; } $processedChartFields[] = $availableChart['alias']; } } //Check if chart is displayable on Charts Div } if ($hasGrouped && $displaySubtotals && !$hasTotals) { $sqlSelect = $sqlSelect . " COUNT(*) AS 'TOTAL',"; $columns[] = 'TOTAL'; $types[] = 'int'; $columnsU[] = null; $columnsO[] = null; $columnsN[] = null; $columnsI[] = null; } $sqlSelect = substr($sqlSelect, 0, -1); $sqlTotals = substr($sqlTotals, 0, -1); $sqlTotalsC = substr($sqlTotalsC, 0, -1); $returnedArray = array("referenceAlias" => $referenceAlias, "columns" => $columns, "types" => $types, "columnsU" => $columnsU, "columnsO" => $columnsO, "columnsN" => $columnsN, "columnsI" => $columnsI, "totals" => $totals, "groupSubTotalField" => $groupSubTotalField, "groupSubTotalFieldAscSort" => $groupSubTotalFieldAscSort, "hasGrouped" => $hasGrouped, "hasFunctionWithSQL" => $hasFunctionWithSQL, "resultsetFields" => $resulsetFields, "querys" => array("Select" => $sqlSelect, "Totals" => $sqlTotals, "Charts" => $sqlTotalsC)); return $returnedArray; }