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;
}
