function prepareElementMetaData($frid, $fid, $linkfids, $ifPartsZero, $formFieldFilterMap)
{
// first convert any handles to element Handles, and/or get the element id if necessary...element id is necessary for creating the formfieldfiltermap, since that function was written the first time we tried to do this, when there were no element handles in the mix
if ($frid and !is_numeric($ifPartsZero)) {
$ifPartsZero = dealWithDeprecatedFrameworkHandles($ifPartsZero, $frid);
// will convert a framework handle if necessary
$element_id = formulize_getIdFromElementHandle($ifPartsZero);
} elseif (is_numeric($ifPartsZero)) {
// using a numeric element id
$element_id = $ifPartsZero;
$ifPartsZero = formulize_getElementHandleFromID($ifPartsZero);
} else {
// no framework, element handle being used...so we have to derive the element id
$element_id = formulize_getIDFromElementHandle($ifPartsZero);
}
// identify the form that the element is associated with and put it in the map
list($formFieldFilterMap, $mappedForm) = formulize_mapFormFieldFilter($element_id, $formFieldFilterMap);
/*print "map: <br>";
print_r($formFieldFilterMap);
print "<br>Mappedform: $mappedForm<br>";
print "<br>fid: $fid";*/
$elementPrefix = $mappedForm == $fid ? "main" : "f" . array_search($mappedForm, $linkfids);
// check if its encrypted or not, and setup the proper field reference
$queryElementMetaData = formulize_getElementMetaData($ifPartsZero, true);
// add ` ` around ifParts[0]...
$ifPartsZero = "`" . $ifPartsZero . "`";
if ($queryElementMetaData['ele_encrypt']) {
$queryElement = "AES_DECRYPT({$elementPrefix}." . $ifPartsZero . ", '" . getAESPassword() . "')";
} else {
$queryElement = "{$elementPrefix}." . $ifPartsZero;
}
// return in this order: $ifParts[0], $formFieldFilterMap, $mappedForm, $element_id, $elementPrefix, $queryElement
$to_return = array();
$to_return[] = $ifPartsZero;
$to_return[] = $formFieldFilterMap;
$to_return[] = $mappedForm;
$to_return[] = $element_id;
$to_return[] = $elementPrefix;
$to_return[] = $queryElement;
return $to_return;
}
function getEntryValues($entry, $formulize_mgr, $groups, $fid, $elements = "", $mid, $uid, $owner, $groupEntryWithUpdateRights)
{
if (!$fid) {
// fid is required
return "";
}
if (!is_numeric($entry) or !$entry) {
return "";
}
static $cachedEntryValues = array();
$serializedElements = serialize($elements);
if (!isset($cachedEntryValues[$fid][$entry][$serializedElements])) {
global $xoopsDB;
if (!$mid) {
$mid = getFormulizeModId();
}
if (!$uid) {
global $xoopsUser;
$uid = $xoopsUser ? $xoopsUser->getVar("uid") : 0;
// if there is no uid, then use the $xoopsUser uid if there is one, or zero for anons
}
if (!$owner) {
$owner = getEntryOwner($entry, $fid);
// if there is no owner, then get the owner for this entry in this form
}
// viewquery changed in light of 3.0 data structure changes...
//$viewquery = q("SELECT ele_caption, ele_value FROM " . $xoopsDB->prefix("formulize_form") . " WHERE id_req=$entry $element_query");
// NEED TO CHECK THE FORM FOR ENCRYPTED ELEMENTS, AND ADD THEM AFTER THE * WITH SPECIAL ALIASES. tHEN IN THE LOOP, LOOK FOR THE ALIASES, AND SKIP PROCESSING THOSE ELEMENTS NORMALLY, BUT IF WHEN PROCESSING A NORMAL ELEMENT, IT IS IN THE LIST OF ENCRYPTED ELEMENTS, THEN GET THE ALIASED, DECRYPTED VALUE INSTEAD OF THE NORMAL ONE
// NEED TO ADD RETRIEVING ENCRYPTED ELEMENT LIST FROM FORM OBJECT
$form_handler =& xoops_getmodulehandler('forms', 'formulize');
$formObject = $form_handler->get($fid);
$formHandles = $formObject->getVar('elementHandles');
$formCaptions = $formObject->getVar('elementCaptions');
$formEncryptedElements = $formObject->getVar('encryptedElements');
$encryptedSelect = "";
foreach ($formEncryptedElements as $thisEncryptedElement) {
$encryptedSelect .= ", AES_DECRYPT(`" . $thisEncryptedElement . "`, '" . getAESPassword() . "') as 'decrypted_value_for_" . $thisEncryptedElement . "'";
}
$viewquerydb = q("SELECT * {$encryptedSelect} FROM " . $xoopsDB->prefix("formulize_" . $formObject->getVar('form_handle')) . " WHERE entry_id={$entry}");
$viewquery = array();
// need to parse the result based on the elements requested and setup the viewquery array for use later on
$vqindexer = 0;
foreach ($viewquerydb[0] as $thisField => $thisValue) {
if (strstr($thisField, "decrypted_value_for_")) {
continue;
}
// don't process these values normally, instead, we just refer to them later to grab the decrypted value, if this iteration is over an encrypted element.
$includeElement = false;
if (is_array($elements)) {
if (in_array(array_search($thisField, $formHandles), $elements) and $thisValue !== "") {
$includeElement = true;
}
} elseif (!strstr($thisField, "creation_uid") and !strstr($thisField, "creation_datetime") and !strstr($thisField, "mod_uid") and !strstr($thisField, "mod_datetime") and !strstr($thisField, "entry_id") and $thisValue !== "") {
$includeElement = true;
}
if ($includeElement) {
$viewquery[$vqindexer]["ele_handle"] = $thisField;
$viewquery[$vqindexer]["ele_caption"] = $formCaptions[array_search($thisField, $formHandles)];
if (in_array($thisField, $formEncryptedElements)) {
$viewquery[$vqindexer]["ele_value"] = $viewquerydb[0]["decrypted_value_for_" . $thisField];
} else {
$viewquery[$vqindexer]["ele_value"] = $thisValue;
}
}
$vqindexer++;
}
// build query for display groups and disabled
foreach ($groups as $thisgroup) {
$gq .= " OR ele_display LIKE '%,{$thisgroup},%'";
//$dgq .= " AND ele_disabled NOT LIKE '%,$thisgroup,%'"; // not sure that this is necessary
}
// exclude private elements unless the user has view_private_elements permission, or update_entry permission on a one-entry-per group entry
$private_filter = "";
$gperm_handler =& xoops_gethandler('groupperm');
$view_private_elements = $gperm_handler->checkRight("view_private_elements", $fid, $groups, $mid);
if (!$view_private_elements and $uid != $owner and !$groupEntryWithUpdateRights) {
$private_filter = " AND ele_private=0";
}
$allowedquery = q("SELECT ele_caption, ele_disabled, ele_handle FROM " . $xoopsDB->prefix("formulize") . " WHERE id_form={$fid} AND (ele_display='1' {$gq}) {$private_filter}");
// AND (ele_disabled != 1 $dgq)"); // not sure that filtering for disabled elements is necessary
$allowedDisabledStatus = array();
$allowedhandles = array();
foreach ($allowedquery as $onecap) {
$allowedhandles[] = $onecap['ele_handle'];
$allowedDisabledStatus[$onecap['ele_handle']] = $onecap['ele_disabled'];
}
foreach ($viewquery as $vq) {
// check that this caption is an allowed caption before recording the value
if (in_array($vq["ele_handle"], $allowedhandles)) {
$prevEntry['handles'][] = $vq["ele_handle"];
$prevEntry['captions'][] = $vq["ele_caption"];
$prevEntry['values'][] = $vq["ele_value"];
$prevEntry['disabled'][] = $allowedDisabledStatus[$vq['ele_handle']];
}
}
$cachedEntryValues[$fid][$entry][$serializedElements] = $prevEntry;
}
return $cachedEntryValues[$fid][$entry][$serializedElements];
}
function toggleEncryption($elementHandle, $currentEncryptionStatus)
{
if (!($element = _getElementObject($elementHandle))) {
return false;
}
// 1. rename to the current field
// 2. create a new field to put this data in
// 3. run the encrypt/decrypt SQL to populate the new field
// 4. drop the old field
$form_handler = xoops_getmodulehandler('forms', 'formulize');
if (!$form_handler->updateField($element, $elementHandle, false, $elementHandle . "qaz")) {
// false is no datatype change, last param is the new name
return false;
}
if ($currentEncryptionStatus) {
$dataType = 'text';
$aesFunction = 'AES_DECRYPT';
} else {
$dataType = 'blob';
$aesFunction = 'AES_ENCRYPT';
}
global $xoopsDB;
$formObject = $form_handler->get($element->getVar('id_form'));
$type_with_default = "text" == $dataType ? "text" : "{$dataType} NULL default NULL";
$insertFieldSQL = "ALTER TABLE " . $xoopsDB->prefix("formulize_" . $formObject->getVar('form_handle')) . " ADD `{$elementHandle}` {$type_with_default}";
if (!($insertFieldRes = $xoopsDB->queryF($insertFieldSQL))) {
return false;
}
$toggleSQL = "UPDATE " . $xoopsDB->prefix("formulize_" . $formObject->getVar('form_handle')) . " SET `{$elementHandle}` = " . $aesFunction . "(" . $elementHandle . "qaz, '" . getAESPassword() . "')";
if (!($toggleSQLRes = $xoopsDB->queryF($toggleSQL))) {
return false;
}
$dropSQL = "ALTER TABLE " . $xoopsDB->prefix("formulize_" . $formObject->getVar('form_handle')) . " DROP " . $elementHandle . "qaz";
if (!($dropSQLRes = $xoopsDB->queryF($dropSQL))) {
return false;
}
return true;
}
function performCalcs($cols, $calcs, $blanks, $grouping, $frid, $fid)
{
// determine which fields have which calculations and exculsion options
// calculations that are simple, with the same exclusion options, can be done in the same query
// percentage distribution is not simple, nor is percentile calculation (part of averages), nor is mode (part of averages), but all others are simple and can be done in one query
global $xoopsDB;
$masterResults = array();
$masterResultsRaw = array();
$blankSettings = array();
$groupingSettings = array();
$groupingValues = array();
$baseQuery = $GLOBALS['formulize_queryForCalcs'];
$oneSideBaseQuery = $GLOBALS['formulize_queryForOneSideCalcs'];
if ($frid) {
$framework_handler =& xoops_getmodulehandler('frameworks', 'formulize');
$frameworkObject = $framework_handler->get($frid);
}
$form_handler = xoops_getmodulehandler('forms', 'formulize');
for ($i = 0; $i < count($cols); $i++) {
// convert to element handle from element id
list($handle, $fidAlias, $handleFid) = getCalcHandleAndFidAlias($cols[$i], $fid);
// returns ELEMENT handles for use in query
$handleFormObject = $form_handler->get($handleFid);
// get the exclude and grouping values for this column
$excludes = explode(",", $blanks[$i]);
$groupings = explode(",", $grouping[$i]);
// need to properly handle "other" values
// build the select statement
foreach (explode(",", $calcs[$i]) as $cid => $calc) {
// set the base query to use:
// if this calculation is being done on a field that is on the one side of a one to many relationship, then we need to use a special version of the baseQuery
if ($frid) {
if ($frameworkObject->whatSideIsHandleOn($cols[$i]) == "one") {
$thisBaseQuery = $oneSideBaseQuery;
} else {
$thisBaseQuery = $baseQuery;
}
} else {
$thisBaseQuery = $baseQuery;
}
// figure out if the field is encrypted, and setup the calcElement accordingly
$calcElementMetaData = formulize_getElementMetaData($handle, true);
if ($calcElementMetaData['ele_encrypt']) {
$calcElement = "AES_DECRYPT({$fidAlias}.`{$handle}`, '" . getAESPassword() . "')";
} else {
$calcElement = "{$fidAlias}.`{$handle}`";
}
// figure out the group by clause (grouping is expressed as element ids right now)
//$groupings[$cid] .= "!@^%*17461!@^%*9402";
$theseGroupings = explode("!@^%*", $groupings[$cid]);
$groupByClause = "";
$outerGroupingSelect = "";
$innerGroupingSelect = "";
$outerGroupingSelectAvgCount = "";
$innerGroupingSelectAvgCount = "";
$start = true;
$allGroupings = array();
foreach ($theseGroupings as $thisGrouping) {
if ($thisGrouping == "none" or $thisGrouping == "") {
continue;
}
list($ghandle, $galias) = getCalcHandleAndFidAlias($thisGrouping, $fid);
// need to add awareness of encryption in here
if ($start) {
$start = false;
} else {
$groupByClause .= ", ";
}
$allGroupings[] = "{$galias}{$ghandle}";
$groupByClause .= "{$galias}{$ghandle}";
if ($ghandle == "creation_uid" or $ghandle == "mod_uid") {
$innerGroupingSelect .= ", (SELECT CASE usertable.name WHEN '' THEN usertable.uname ELSE usertable.name END FROM " . DBPRE . "users as usertable WHERE usertable.uid = " . $galias . "." . $ghandle . ") as inner{$galias}{$ghandle}";
$innerGroupingSelectAvgCount .= ", (SELECT CASE usertable.name WHEN '' THEN usertable.uname ELSE usertable.name END FROM " . DBPRE . "users as usertable WHERE usertable.uid = " . $galias . "." . $ghandle . ") as inner{$galias}{$ghandle}";
} else {
$innerGroupingSelect .= ", {$galias}.`{$ghandle}` as inner{$galias}{$ghandle}";
$innerGroupingSelectAvgCount .= ", {$galias}.`{$ghandle}` as inner{$galias}{$ghandle}";
}
$outerGroupingSelect .= ", inner{$galias}{$ghandle} as {$galias}{$ghandle}";
$outerGroupingSelectAvgCount .= ", inner{$galias}{$ghandle} as {$galias}{$ghandle}";
}
// figure out what to ask for for this calculation
switch ($calc) {
case "sum":
$select = "SELECT sum(tempElement) as {$fidAlias}{$handle} {$outerGroupingSelect} FROM (SELECT distinct({$fidAlias}.`entry_id`), {$calcElement} as tempElement {$innerGroupingSelect}";
break;
case "min":
$select = "SELECT min(tempElement) as {$fidAlias}{$handle} {$outerGroupingSelect} FROM (SELECT distinct({$fidAlias}.`entry_id`), {$calcElement} as tempElement {$innerGroupingSelect}";
break;
case "max":
$select = "SELECT max(tempElement) as {$fidAlias}{$handle} {$outerGroupingSelect} FROM (SELECT distinct({$fidAlias}.`entry_id`), {$calcElement} as tempElement {$innerGroupingSelect}";
break;
case "count":
$select = "SELECT count(tempElement) as count{$fidAlias}{$handle}, count(distinct(tempElement)) as distinct{$fidAlias}{$handle} {$outerGroupingSelect} FROM (SELECT distinct({$fidAlias}.`entry_id`), {$calcElement} as tempElement {$innerGroupingSelect}";
break;
case "avg":
$select = "SELECT avg(tempElement) as avg{$fidAlias}{$handle}, std(tempElement) as std{$fidAlias}{$handle} {$outerGroupingSelect} FROM (SELECT distinct({$fidAlias}.`entry_id`), {$calcElement} as tempElement {$innerGroupingSelect}";
$selectAvgCount = "SELECT tempElement as {$fidAlias}{$handle}, count(tempElement) as avgcount{$fidAlias}{$handle} {$outerGroupingSelectAvgCount} FROM (SELECT distinct({$fidAlias}.`entry_id`), {$calcElement} as tempElement {$innerGroupingSelectAvgCount}";
break;
case "per":
$select = "SELECT tempElement as {$fidAlias}{$handle}, count(tempElement) as percount{$fidAlias}{$handle} {$outerGroupingSelect} FROM (SELECT distinct({$fidAlias}.`entry_id`), {$calcElement} as tempElement {$innerGroupingSelect}";
include_once XOOPS_ROOT_PATH . "/modules/formulize/include/extract.php";
// need a function here later on
break;
default:
break;
}
// figure out the special where clause conditions that need to be added for this calculation
list($allowedValues, $excludedValues) = calcParseBlanksSetting($excludes[$cid]);
$allowedWhere = "";
if (count($allowedValues) > 0) {
$start = true;
foreach ($allowedValues as $value) {
if ($start) {
$allowedWhere = " AND (";
$start = false;
} else {
$allowedWhere .= " OR ";
}
if ($value === "{BLANK}") {
$allowedWhere .= "({$calcElement}='' OR {$calcElement} IS NULL)";
} else {
$value = parseUserAndToday($value);
// translate {USER} and {TODAY} into literals
$allowedWhere .= "{$calcElement}=";
$allowedWhere .= (is_numeric($value) and $value != 0) ? $value : "'{$value}'";
}
}
if ($allowedWhere) {
$allowedWhere .= ")";
// replace any LEFT JOIN on this form in the query with an INNER JOIN, since there are now search criteria for this form
if ($handleFid == "xoopsusertable") {
$replacementTable = DBPRE . "users";
} else {
$replacementTable = DBPRE . "formulize_" . $handleFormObject->getVar('form_handle');
}
$thisBaseQuery = str_replace("LEFT JOIN " . $replacementTable . " AS", "INNER JOIN " . $replacementTable . " AS", $thisBaseQuery);
}
}
$excludedWhere = "";
if (count($excludedValues) > 0) {
$start = true;
foreach ($excludedValues as $value) {
if ($start) {
$excludedWhere = " AND (";
$start = false;
} else {
$excludedWhere .= " AND ";
}
if ($value === "{BLANK}") {
$excludedWhere .= "({$calcElement}!='' AND {$calcElement} IS NOT NULL)";
} else {
$value = parseUserAndToday($value);
// translate {USER} and {TODAY} into literals
$excludedWhere .= "{$calcElement}!=";
$excludedWhere .= (is_numeric($value) and $value != 0) ? $value : "'{$value}'";
}
}
if ($excludedWhere) {
$excludedWhere .= ")";
if ($handleFid == "xoopsusertable") {
$replacementTable = DBPRE . "users";
} else {
$replacementTable = DBPRE . "formulize_" . $handleFormObject->getVar('form_handle');
}
// replace any LEFT JOIN on this form in the query with an INNER JOIN, since there are now search criteria for this form
$thisBaseQuery = str_replace("LEFT JOIN " . $replacementTable . " AS", "INNER JOIN " . $replacementTable . " AS", $thisBaseQuery);
}
}
// setup group by clause and order by clause
$orderByClause = "";
$groupByClauseMode = "";
if ($groupByClause) {
if ($calc == "avg") {
$groupByClauseMode = " GROUP BY {$fidAlias}{$handle}, " . $groupByClause;
$groupByClause = " GROUP BY " . $groupByClause;
} elseif ($calc == "per") {
$orderByClause = " ORDER BY {$groupByClause}, percount{$fidAlias}{$handle} DESC";
$groupByClause = " GROUP BY {$fidAlias}{$handle}, " . $groupByClause;
} else {
$groupByClause = " GROUP BY " . $groupByClause;
}
} elseif ($calc == "avg") {
$groupByClauseMode = " GROUP BY {$fidAlias}{$handle}";
} elseif ($calc == "per") {
$groupByClause = " GROUP BY {$fidAlias}{$handle}";
$orderByClause = " ORDER BY percount{$fidAlias}{$handle} DESC";
}
// do the query
$calcResult = array();
$calcResultSQL = "{$select} {$thisBaseQuery} {$allowedWhere} {$excludedWhere}) as tempQuery {$groupByClause} {$orderByClause} ";
global $xoopsUser;
//if($xoopsUser->getVar('uid') == 1) {
// print "$calcResultSQL<br><br>";
//}*/
$calcResultRes = $xoopsDB->query($calcResultSQL);
while ($calcResultArray = $xoopsDB->fetchArray($calcResultRes)) {
$calcResult[] = $calcResultArray;
}
// package up the result into the results array that gets passed to the output function that dumps data to screen (suitable for templating at a later date)
$blankSettings[$cols[$i]][$calc] = $excludes[$cid];
$groupingSettings[$cols[$i]][$calc] = $groupings[$cid];
$groupingValues[$cols[$i]][$calc] = array();
// this is an array to store
if ($calc == "per") {
$groupCounts = array();
$indivCounts = array();
$perindexer = -1;
}
foreach ($calcResult as $calcId => $thisResult) {
// this needs to be moved inside or lower down in order to support two level grouping?
switch ($calc) {
case "sum":
foreach ($theseGroupings as $gid => $thisGrouping) {
if ($thisGrouping != "none" and $thisGrouping != "") {
list($ghandle, $galias) = getCalcHandleAndFidAlias($thisGrouping, $fid);
$groupingValues[$cols[$i]][$calc][$calcId][] = convertRawValuesToRealValues($thisResult["{$galias}{$ghandle}"], $ghandle, true);
}
}
$masterResultsRaw[$cols[$i]][$calc][$calcId]['sum'] = $thisResult["{$fidAlias}{$handle}"];
$masterResults[$cols[$i]][$calc][$calcId] = _formulize_DE_CALC_SUM . ": " . formulize_numberFormat($thisResult["{$fidAlias}{$handle}"], $handle);
break;
case "min":
foreach ($theseGroupings as $gid => $thisGrouping) {
if ($thisGrouping != "none" and $thisGrouping != "") {
list($ghandle, $galias) = getCalcHandleAndFidAlias($thisGrouping, $fid);
$groupingValues[$cols[$i]][$calc][$calcId][] = convertRawValuesToRealValues($thisResult["{$galias}{$ghandle}"], $handle, true);
}
}
$masterResults[$cols[$i]][$calc][$calcId] = _formulize_DE_CALC_MIN . ": " . formulize_numberFormat($thisResult["{$fidAlias}{$handle}"], $handle);
break;
case "max":
foreach ($theseGroupings as $gid => $thisGrouping) {
if ($thisGrouping != "none" and $thisGrouping != "") {
list($ghandle, $galias) = getCalcHandleAndFidAlias($thisGrouping, $fid);
$groupingValues[$cols[$i]][$calc][$calcId][] = convertRawValuesToRealValues($thisResult["{$galias}{$ghandle}"], $handle, true);
}
}
$masterResults[$cols[$i]][$calc][$calcId] = _formulize_DE_CALC_MAX . ": " . formulize_numberFormat($thisResult["{$fidAlias}{$handle}"], $handle);
break;
case "count":
foreach ($theseGroupings as $gid => $thisGrouping) {
if ($thisGrouping != "none" and $thisGrouping != "") {
list($ghandle, $galias) = getCalcHandleAndFidAlias($thisGrouping, $fid);
$groupingValues[$cols[$i]][$calc][$calcId][] = convertRawValuesToRealValues($thisResult["{$galias}{$ghandle}"], $ghandle, true);
}
}
$masterResultsRaw[$cols[$i]][$calc][$calcId]['count'] = $thisResult["count{$fidAlias}{$handle}"];
$masterResultsRaw[$cols[$i]][$calc][$calcId]['countunique'] = $thisResult["distinct{$fidAlias}{$handle}"];
$masterResults[$cols[$i]][$calc][$calcId] = _formulize_DE_CALC_NUMENTRIES . ": " . $thisResult["count{$fidAlias}{$handle}"] . "<br>" . _formulize_DE_CALC_NUMUNIQUE . ": " . $thisResult["distinct{$fidAlias}{$handle}"];
break;
case "avg":
foreach ($theseGroupings as $gid => $thisGrouping) {
if ($thisGrouping != "none" and $thisGrouping != "") {
list($ghandle, $galias) = getCalcHandleAndFidAlias($thisGrouping, $fid);
$groupingValues[$cols[$i]][$calc][$calcId][] = convertRawValuesToRealValues($thisResult["{$galias}{$ghandle}"], $ghandle, true);
}
}
$masterResults[$cols[$i]][$calc][$calcId] = _formulize_DE_CALC_MEAN . ": " . formulize_numberFormat($thisResult["avg{$fidAlias}{$handle}"], $handle) . "<br>" . _formulize_DE_CALC_STD . ": " . formulize_numberFormat($thisResult["std{$fidAlias}{$handle}"], $handle) . "<br><br>";
break;
case "per":
$groupingWhere = array();
$groupingValuesFound = array();
foreach ($theseGroupings as $gid => $thisGrouping) {
if ($thisGrouping != "none" and $thisGrouping != "") {
list($ghandle, $galias) = getCalcHandleAndFidAlias($thisGrouping, $fid);
//print $thisResult["$galias$ghandle"] . "<br>";
if ($thisResult["{$galias}{$ghandle}"] == "") {
$groupingWhere[] = "({$galias}.`{$ghandle}` = '" . $thisResult["{$galias}{$ghandle}"] . "' OR {$galias}.`{$ghandle}` IS NULL)";
$groupingValuesFound[] = _formulize_BLANK_KEYWORD;
} else {
$groupingWhere[] = "{$galias}.`{$ghandle}` = '" . $thisResult["{$galias}{$ghandle}"] . "'";
$groupingValuesFound[] = $thisResult["{$galias}{$ghandle}"];
}
}
}
if (count($groupingWhere) > 0) {
$groupingWhere = "AND (" . implode(" AND ", $groupingWhere) . ")";
} else {
$groupingWhere = "";
}
if (!isset($groupCounts[$groupingWhere])) {
// need to figure out the total count for this grouping setting
$perindexer++;
$groupingValues[$cols[$i]][$calc][$perindexer] = convertRawValuesToRealValues($groupingValuesFound, $ghandle, true);
$countSQL = "SELECT count({$fidAlias}.`{$handle}`) as count{$fidAlias}{$handle} {$thisBaseQuery} {$allowedWhere} {$excludedWhere} {$groupingWhere}";
//print "$countSQL<br>";
$countRes = $xoopsDB->query($countSQL);
$countArray = $xoopsDB->fetchArray($countRes);
$countValue = $countArray["count{$fidAlias}{$handle}"];
$indexerToUse = $perindexer;
$groupCounts[$groupingWhere]['countValue'] = $countValue;
$groupCounts[$groupingWhere]['indexerToUse'] = $indexerToUse;
$start = true;
} else {
$indexerToUse = $groupCounts[$groupingWhere]['indexerToUse'];
$countValue = $groupCounts[$groupingWhere]['countValue'];
}
// need to figure out the individual counts of the constituent parts of this result
if (strstr($thisResult["{$fidAlias}{$handle}"], "*=+*:")) {
$rawIndivValues = explode("*=+*:", $thisResult["{$fidAlias}{$handle}"]);
array_shift($rawIndivValues);
// current convention is to have the separator at the beginning of the string, so the exploded array will have a blank value at the beginning
} elseif ($linkedMetaData = formulize_isLinkedSelectBox($cols[$i])) {
// convert the pointers for the linked selectbox values, to their source values
$sourceMeta = explode("#*=:*", $linkedMetaData[2]);
$data_handler = new formulizeDataHandler($sourceMeta[0]);
$rawIndivValues = $data_handler->findAllValuesForEntries($sourceMeta[1], explode(",", trim($thisResult["{$fidAlias}{$handle}"], ",")));
// trip opening and closing commas and split by comma into an array
} else {
$rawIndivValues = array(0 => $thisResult["{$fidAlias}{$handle}"]);
}
foreach ($rawIndivValues as $thisIndivValue) {
$indivCounts[$cols[$i]][$calc][$indexerToUse][trans(calcValuePlusText($thisIndivValue, $handle, $cols[$i], $calc, $indexerToUse))] += $thisResult["percount{$fidAlias}{$handle}"];
// add this count to the total count for this particular item
$groupCounts[$groupingWhere]['responseCountValue'] += $thisResult["percount{$fidAlias}{$handle}"];
// add this count to the total count for all items
}
break;
}
}
if ($calc == "avg") {
// then do some extra stuff for the more complicated calculations
// work out the mode...
$modeCounts = array();
$modeQuery = "{$selectAvgCount} {$thisBaseQuery} {$allowedWhere} {$excludedWhere} ) as tempQuery {$groupByClauseMode} ORDER BY ";
if (count($allGroupings) > 0) {
$modeQuery .= implode(", ", $allGroupings) . ", ";
}
$modeQuery .= "avgcount{$fidAlias}{$handle} DESC";
//print "$modeQuery<br>";
$modeRes = $xoopsDB->query($modeQuery);
$foundModeValue = array();
$modeIndexer = 0;
while ($modeData = $xoopsDB->fetchArray($modeRes)) {
$foundValues = "";
$modeCountsTemp = array();
foreach ($theseGroupings as $gid => $thisGrouping) {
if ($thisGrouping != "none" and $thisGrouping != "") {
list($ghandle, $galias) = getCalcHandleAndFidAlias($thisGrouping, $fid);
$foundValues .= $modeData["{$galias}{$ghandle}"] . "xyz";
$modeCountsTemp[$modeData["{$galias}{$ghandle}"]] = "{$galias}.`{$ghandle}`";
}
}
if (!isset($foundModeValue[$foundValues])) {
// this is a new combination
$foundModeValue[$foundValues] = true;
if ($foundValues) {
$modeCounts[$modeIndexer] = $modeCountsTemp;
} else {
$modeCounts[$modeIndexer]['none'] = 'none';
}
$masterResults[$cols[$i]][$calc][$modeIndexer] .= "REPLACE WITH MEDIAN" . _formulize_DE_CALC_MODE . ": " . formulize_numberFormat($modeData["{$fidAlias}{$handle}"], $handle);
$modeIndexer++;
}
}
// work out the percentiles including median
// calculating percentiles logic based on formula described here: http://onlinestatbook.com/chapter1/percentiles.html
// modeGrouping is the value that we are grouping by, modeHandle is the field to look for that value in
foreach ($modeCounts as $thisGid => $thisModeGrouping) {
$groupingWhere = "";
foreach ($thisModeGrouping as $modeGrouping => $modeHandle) {
// first we need to get the full count for this group of results
// need to convert grouping values into the where clause for the percentile calculations
$groupingWhere .= $modeHandle === 'none' ? "" : " AND ({$modeHandle} = '{$modeGrouping}')";
}
$countSQL = "SELECT count({$fidAlias}.`{$handle}`) as count{$fidAlias}{$handle} {$thisBaseQuery} {$allowedWhere} {$excludedWhere} {$groupingWhere}";
//print "<br>$countSQL<br>";
$countRes = $xoopsDB->query($countSQL);
$countArray = $xoopsDB->fetchArray($countRes);
$countValue = $countArray["count{$fidAlias}{$handle}"];
$per25Limit = floor(($countValue + 1) / 4);
$per25Fraction = ($countValue + 1) / 4 - $per25Limit;
$per25Limit = $per25Limit - 1;
// since Limit statements interpret rank orders as starting from 0, must subtract 1
$per25Size = ($countValue + 1) % 4 == 0 ? 1 : 2;
$per75Limit = floor(($countValue + 1) * 3 / 4);
$per75Fraction = ($countValue + 1) * 3 / 4 - $per75Limit;
$per75Limit = $per75Limit - 1;
// since Limit statements interpret rank orders as starting from 0, must subtract 1
$per75Size = $per25Size;
$per50Limit = floor(($countValue + 1) / 2);
$per50Fraction = ($countValue + 1) / 2 - $per50Limit;
$per50Limit = $per50Limit - 1;
// since Limit statements interpret rank orders as starting from 0, must subtract 1
$per50Size = ($countValue + 1) % 2 == 0 ? 1 : 2;
$per25SQL = "SELECT {$fidAlias}.`{$handle}` as {$fidAlias}{$handle} {$thisBaseQuery} {$allowedWhere} {$excludedWhere} {$groupingWhere} ORDER BY {$fidAlias}{$handle} LIMIT {$per25Limit},{$per25Size}";
//print "$per25SQL<Br><Br>";
$per75SQL = "SELECT {$fidAlias}.`{$handle}` as {$fidAlias}{$handle} {$thisBaseQuery} {$allowedWhere} {$excludedWhere} {$groupingWhere} ORDER BY {$fidAlias}{$handle} LIMIT {$per75Limit},{$per75Size}";
//print "$per75SQL<Br><Br>";
$per50SQL = "SELECT {$fidAlias}.`{$handle}` as {$fidAlias}{$handle} {$thisBaseQuery} {$allowedWhere} {$excludedWhere} {$groupingWhere} ORDER BY {$fidAlias}{$handle} LIMIT {$per50Limit},{$per50Size}";
//print "$per50SQL<Br><Br>";
$per25Res = $xoopsDB->query($per25SQL);
$per75Res = $xoopsDB->query($per75SQL);
$per50Res = $xoopsDB->query($per50SQL);
$allPerResults = _formulize_DE_CALC_MEDIAN25 . ": ";
$per25Results = "";
$per75Results = "";
$per50Results = "";
$start = true;
$perPair = array();
while ($per25Array = $xoopsDB->fetchArray($per25Res)) {
$perPair[] = $per25Array["{$fidAlias}{$handle}"];
if (!$start) {
$per25Results .= ", ";
}
$start = false;
$per25Results .= formulize_numberFormat($per25Array["{$fidAlias}{$handle}"], $handle);
}
if (count($perPair) < 2) {
$allPerResults .= $per25Results;
} elseif ($perPair[0] != $perPair[1]) {
// we have multiple values at the median/percentile point, so figure out the weighted average
$allPerResults .= formulize_numberFormat($per25Fraction * ($perPair[1] - $perPair[0]) + $perPair[0], $handle, "", 2) . " ({$per25Results})";
} else {
// multiple, equal values at median/percentile point
$allPerResults .= formulize_numberFormat($perPair[0], $handle);
}
$allPerResults .= "<br>";
$allPerResults .= _formulize_DE_CALC_MEDIAN . ": ";
$start = true;
$perPair = array();
while ($per50Array = $xoopsDB->fetchArray($per50Res)) {
$perPair[] = $per50Array["{$fidAlias}{$handle}"];
if (!$start) {
$per50Results .= ", ";
}
$start = false;
$per50Results .= formulize_numberFormat($per50Array["{$fidAlias}{$handle}"], $handle);
}
if (count($perPair) < 2) {
$allPerResults .= $per50Results;
} elseif ($perPair[0] != $perPair[1]) {
// we have multiple values at the median/percentile point, so figure out the average
$allPerResults .= formulize_numberFormat($per50Fraction * ($perPair[1] - $perPair[0]) + $perPair[0], $handle, "", 2) . " ({$per50Results})";
} else {
// multiple, equal values at median/percentile point
$allPerResults .= formulize_numberFormat($perPair[0], $handle);
}
$allPerResults .= "<br>";
$allPerResults .= _formulize_DE_CALC_MEDIAN75 . ": ";
$start = true;
$perPair = array();
while ($per75Array = $xoopsDB->fetchArray($per75Res)) {
$perPair[] = $per75Array["{$fidAlias}{$handle}"];
if (!$start) {
$per75Results .= ", ";
}
$start = false;
$per75Results .= formulize_numberFormat($per75Array["{$fidAlias}{$handle}"], $handle);
}
if (count($perPair) < 2) {
$allPerResults .= $per75Results;
} elseif ($perPair[0] != $perPair[1]) {
// we have multiple values at the median/percential point, so figure out the average
$allPerResults .= formulize_numberFormat($per75Fraction * ($perPair[1] - $perPair[0]) + $perPair[0], $handle, "", 2) . " ({$per75Results})";
} else {
// multiple, equal values at median/percentile point
$allPerResults .= formulize_numberFormat($perPair[0], $handle);
}
$allPerResults .= "<br><br>";
//print $medianResults."<br><br>";
$masterResults[$cols[$i]][$calc][$thisGid] = str_replace("REPLACE WITH MEDIAN", $allPerResults, $masterResults[$cols[$i]][$calc][$thisGid]);
}
} elseif ($calc == "per") {
// output the percentage breakdowns, since we'll be done counting everything we need now
foreach ($groupCounts as $groupCountData) {
$start = true;
if ($groupCountData['countValue'] == $groupCountData['responseCountValue'] and $start) {
$typeout = "<table cellpadding=3>\n<tr><td style=\"vertical-align: top; padding-right: 1em;\"><u>" . _formulize_DE_PER_ITEM . "</u></td><td style=\"vertical-align: top; padding-right: 1em;\"><u>" . _formulize_DE_PER_COUNT . "</u></td><td style=\"vertical-align: top; padding-right: 1em; padding-right: 1em;\"><u>" . _formulize_DE_PER_PERCENT . "</u></td></tr>\n";
} else {
$typeout = "<table cellpadding=3>\n<tr><td style=\"vertical-align: top; padding-right: 1em;\"><u>" . _formulize_DE_PER_ITEM . "</u></td><td style=\"vertical-align: top; padding-right: 1em;\"><u>" . _formulize_DE_PER_COUNT . "</u></td><td style=\"vertical-align: top; padding-right: 1em; padding-right: 1em;\"><u>" . _formulize_DE_PER_PERCENTRESPONSES . "</u></td><td style=\"vertical-align: top; padding-right: 1em;\"><u>" . _formulize_DE_PER_PERCENTENTRIES . "</u></td></tr>\n";
}
// replace the indivText with a corresponding name, if we have any on file
$nameReplacementMap = array();
if (isset($GLOBALS['formulize_fullNameUserNameCalculationReplacementList'][$cols[$i]][$calc][$groupCountData['indexerToUse']]) and $start) {
global $xoopsDB;
$nameType = $GLOBALS['formulize_fullNameUserNameCalculationReplacementList'][$cols[$i]][$calc][$groupCountData['indexerToUse']]['nametype'];
$userIDs = $GLOBALS['formulize_fullNameUserNameCalculationReplacementList'][$cols[$i]][$calc][$groupCountData['indexerToUse']]['values'];
// get a list of all the names and uids that we're dealing with
$nameReplacementSQL = "SELECT {$nameType}, uid FROM " . $xoopsDB->prefix("users") . " WHERE uid IN (" . implode(", ", $userIDs) . ")";
$nameReplacementRes = $xoopsDB->query($nameReplacementSQL);
while ($nameReplacementArray = $xoopsDB->fetchArray($nameReplacementRes)) {
// map the uid and name values we found, so we can sub them in lower down when needed
$nameReplacementMap[$nameReplacementArray['uid']] = $nameReplacementArray[$nameType];
}
}
$start = false;
arsort($indivCounts[$cols[$i]][$calc][$groupCountData['indexerToUse']]);
foreach ($indivCounts[$cols[$i]][$calc][$groupCountData['indexerToUse']] as $indivText => $indivTotal) {
if (count($nameReplacementMap) > 0) {
$indivText = $nameReplacementMap[$indivText];
}
// swap in a name for this user, if applicable
if ($groupCountData['countValue'] == $groupCountData['responseCountValue']) {
$typeout .= "<tr><td style=\"vertical-align: top;\">{$indivText}</td><td style=\"vertical-align: top;\">{$indivTotal}</td><td style=\"vertical-align: top;\">" . round($indivTotal / $groupCountData['countValue'] * 100, 2) . "%</td></tr>\n";
} else {
$typeout .= "<tr><td style=\"vertical-align: top;\">{$indivText}</td><td style=\"vertical-align: top;\">{$indivTotal}</td><td style=\"vertical-align: top;\">" . round($indivTotal / $groupCountData['responseCountValue'] * 100, 2) . "%</td><td style=\"vertical-align: top;\">" . round($indivTotal / $groupCountData['countValue'] * 100, 2) . "%</td></tr>\n";
}
}
if ($groupCountData['countValue'] == $groupCountData['responseCountValue']) {
$typeout .= "<tr><td style=\"vertical-align: top;\"><hr>" . _formulize_DE_PER_TOTAL . "</td><td style=\"vertical-align: top;\"><hr>" . $groupCountData['countValue'] . "</td><td style=\"vertical-align: top;\"><hr>100%</td></tr>\n</table>\n";
} else {
$typeout .= "<tr><td style=\"vertical-align: top;\"><hr>" . _formulize_DE_PER_TOTAL . "</td><td style=\"vertical-align: top;\"><hr>" . $groupCountData['responseCountValue'] . " " . _formulize_DE_PER_TOTALRESPONSES . "<br>" . $groupCountData['countValue'] . " " . _formulize_DE_PER_TOTALENTRIES . "</td><td style=\"vertical-align: top;\"><hr>100%</td><td style=\"vertical-align: top;\"><hr>" . round($groupCountData['responseCountValue'] / $groupCountData['countValue'], 2) . " " . _formulize_DE_PER_RESPONSESPERENTRY . "</td></tr>\n</table>";
}
$masterResults[$cols[$i]][$calc][$groupCountData['indexerToUse']] = $typeout;
}
}
}
}
/*print "<br><br>";
print_r($masterResults);
print "<br><br>";
print_r($groupingValues);*/
$to_return[0] = $masterResults;
$to_return[1] = $blankSettings;
$to_return[2] = $groupingSettings;
$to_return[3] = $groupingValues;
$to_return[4] = $masterResultsRaw;
return $to_return;
}
