function loadOldReport($id, $fid, $view_groupscope) { /* need to create the following for passing back... $reportscope - ,1,31,45, list of group ids $_POST['oldcols'] - 234,56,781 list of ele_ids for visible columns (handles for a framework, but an old report will never be for a framework) $_POST['asearch'] - flat array of search elements, separator: --> /,%^&2 <--, possible elements: [field]ele_id[/field], ==, !=, <, >, <=, >=, LIKE, NOT, NOT LIKE, AND, OR, ( and ) $_POST['calc_cols'] - 234/56/781 - list of ele_ids, or can include uid, proxyid, mod_date, creation_date $_POST['calc_calcs'] - sum,avg,min,max,count,per/...next column $_POST['calc_blanks'] - all,noblanks,onlyblanks/...next column $_POST['calc_grouping'] - none,uid,proxyid,mod_date,creation_date,orlistofele_ids/...next column $_POST['sort'] - ele_id for form, handle for framework $_POST['order'] - SORT_ASC, SORT_DESC */ global $xoopsDB; $s = "&*=%4#"; // get all data from DB $data = q("SELECT report_ispublished, report_scope, report_fields, report_search_typeArray, report_search_textArray, report_andorArray, report_calc_typeArray, report_sort_orderArray, report_ascdscArray, report_globalandor FROM " . $xoopsDB->prefix("formulize_reports") . " WHERE report_id={$id} AND report_id_form={$fid}"); // reportscope $scope = explode($s, $data[0]['report_scope']); if ($scope[0] == "") { if ($view_groupscope) { $found_scope = "group"; } else { $found_scope = "mine"; } } else { foreach ($scope as $thisscope) { if (substr($thisscope, 0, 1) == "g") { $found_scope .= "," . substr($thisscope, 1); } else { // the case of only userscopes, need to set the scope to the groups that the user is a member of $user_scope[] = $thisscope; // save and include as an advanced search property looking for the user id if (!$membership_handler) { $membership_handler =& xoops_gethandler('membership'); } unset($uidGroups); unset($groupString); $uidGroups = $membership_handler->getGroupsByUser($thisscope); $uidGroups = array_unique($uidGroups); // remove registered users from the $uidGroups -- registered users is equivalent to "all groups" since everyone is a member of it foreach ($uidGroups as $key => $thisgroup) { if ($thisgroup == 2) { unset($uidGroups[$key]); } } $groupString = implode(",", $uidGroups); $found_scope .= "," . $groupString; } } $found_scope .= ","; } $to_return[0] = $found_scope; // oldcols $tempcols = explode($s, $data[0]['report_fields']); // This conversion now performed as part of DB query // foreach($tempcols as $col) { // $cols[] = str_replace("`", "'", $col); // } $ids = convertHeadersToIds($cols, $fid); $to_return[1] = implode(",", $ids); // asearch - complicated! $s2 = "/,%^&2"; $gao = $data[0]['report_globalandor']; if ($gao == "and") { $gao = "AND"; } if ($gao == "or") { $gao = "OR"; } $terms = explode($s, $data[0]['report_search_textArray']); $tempops = explode($s, $data[0]['report_search_typeArray']); foreach ($tempops as $thisop) { switch ($thisop) { case "equals": $ops[] = "=="; break; case "not": $ops[] = "!="; break; case "like": $ops[] = "LIKE"; break; case "notlike": $ops[] = "NOT LIKE"; break; case "greaterthan": $ops[] = ">"; break; case "greaterthanequal": $ops[] = ">="; break; case "lessthan": $ops[] = "<"; break; case "lessthanequal": $ops[] = "<="; break; } } $laos = explode($s, $data[0]['report_andorArray']); $start = 1; // for each found column, we should create: // ($field $op $term1 $localandor $field $op $term2....) for ($i = 0; $i < count($ids); $i++) { if ($terms[$i]) { if (!$start) { $asearch .= $s2 . $gao . $s2; } $start = 0; $asearch .= "("; unset($allterms); $allterms = explode(",", $terms[$i]); $start2 = 1; foreach ($allterms as $thisterm) { if (!$start2) { if ($laos[$i] == "and") { $lao = "AND"; } if ($laos[$i] == "or") { $lao = "OR"; } $asearch .= $s2 . $lao; } $start2 = 0; $asearch .= $s2 . "[field]" . $ids[$i] . "[/field]"; $asearch .= $s2 . $ops[$i]; $termtouse = str_replace("[,]", ",", $thisterm); $asearch .= $s2 . $termtouse; } $asearch .= $s2 . ")"; } } // add in any user_scope found... if (count($user_scope) > 0) { if ($asearch) { $asearch .= $s2 . "AND" . $s2 . "(" . $s2; $needtoclose = 1; } $start = 1; foreach ($user_scope as $user) { if (!$start) { $asearch .= $s2 . "OR" . $s2; } $start = 0; $name = convertUids($user, "uid"); $asearch .= "[field]uid[/field]" . $s2 . "==" . $s2 . $name; } if ($needtoclose) { $asearch .= $s2 . ")"; } } $to_return[2] = $asearch; // calcs - special separator, and then the standard separator within each column (since multiple calcs can be requested) $oldcalcs = explode("!@+*+6-", $data[0]['report_calc_typeArray']); unset($cols); for ($i = 0; $i < count($ids); $i++) { if ($oldcalcs[$i]) { $cols[] = $ids[$i]; unset($localcalcs); $thesecalcs = explode($s, $oldcalcs[$i]); foreach ($thesecalcs as $acalc) { if (strstr($acalc, "selected")) { if (strstr($acalc, "sum")) { $localcalcs[] = "sum"; } if (strstr($acalc, "average")) { $localcalcs[] = "avg"; } if (strstr($acalc, "min")) { $localcalcs[] = "min"; } if (strstr($acalc, "max")) { $localcalcs[] = "max"; } if (strstr($acalc, "count")) { $localcalcs[] = "count"; } if (strstr($acalc, "percent")) { $localcalcs[] = "per"; } } } $foundcalcs = implode(",", $localcalcs); $calcs[] = $foundcalcs; unset($theseblanks); unset($thesegrouping); for ($x = 0; $x < count($localcalcs); $x++) { $theseblanks[] = "all"; $thesegrouping[] = "none"; } $tempblanks = implode(",", $theseblanks); $blanks[] = $tempblanks; $tempgrouping = implode(",", $thesegrouping); $grouping[] = $tempgrouping; } } $to_return[3] = implode("/", $cols); $to_return[4] = implode("/", $calcs); $to_return[5] = implode("/", $blanks); $to_return[6] = implode("/", $grouping); // sort and order $sorts = explode($s, $data[0]['report_sort_orderArray']); $orders = explode($s, $data[0]['report_ascdscArray']); for ($i = 0; $i < count($ids); $i++) { if ($sorts[$i] == 1) { $to_return[7] = $ids[$i]; if ($orders[$i] == "ASC") { $to_return[8] = "SORT_ASC"; } else { $to_return[8] = "SORT_DESC"; } break; } } if (!$to_return[7]) { $to_return[7] = ""; } if (!$to_return[8]) { $to_return[8] = ""; } // hide list, hide calcs // if ispub includes a 3 then hide list, show calcs if (strstr($data[0]['report_ispublished'], "3")) { $to_return[9] = 1; $to_return[10] = 0; } elseif ($to_return[3]) { $to_return[9] = 1; $to_return[10] = 0; } else { $to_return[9] = 0; $to_return[10] = 1; } // lock controls // if ispub includes a 2 or a 3, then lock controls if (strstr($data[0]['report_ispublished'], "3") or strstr($data[0]['report_ispublished'], "2")) { $to_return[11] = 1; } else { $to_return[11] = 0; } return $to_return; }
function getHeaderList($fid, $needids = false, $convertIdsToElementHandles = false) { global $xoopsDB; $headerlist = array(); $hlq = "SELECT headerlist FROM " . $xoopsDB->prefix("formulize_id") . " WHERE id_form='{$fid}'"; if ($result = $xoopsDB->query($hlq)) { while ($row = $xoopsDB->fetchRow($result)) { // check to see if there is actually any real data specified in this string, make sure it's not all separators. if ($somethingLeft = str_replace("*=+*:", "", $row[0])) { $headerlist = explode("*=+*:", $row[0]); array_shift($headerlist); } } // if the headerlist is using the new ID based system if (is_numeric($headerlist[0]) or isMetaDataField($headerlist[0])) { // if we want actual text headers, convert ids to text if (!$needids) { $start = 1; $metaHeaderlist = array(); foreach ($headerlist as $headerid => $thisheaderid) { if ($thisheaderid == "entry_id") { $metaHeaderlist[] = _formulize_ENTRY_ID; unset($headerlist[$headerid]); continue; } if ($thisheaderid == "uid" or $thisheaderid == "creation_uid") { $metaHeaderlist[] = _formulize_DE_CALC_CREATOR; unset($headerlist[$headerid]); continue; } if ($thisheaderid == "proxyid" or $thisheaderid == "mod_uid") { $metaHeaderlist[] = _formulize_DE_CALC_MODIFIER; unset($headerlist[$headerid]); continue; } if ($thisheaderid == "creation_date" or $thisheaderid == "creation_datetime") { $metaHeaderlist[] = _formulize_DE_CALC_CREATEDATE; unset($headerlist[$headerid]); continue; } if ($thisheaderid == "mod_date" or $thisheaderid == "mod_datetime") { $metaHeaderlist[] = _formulize_DE_CALC_MODDATE; unset($headerlist[$headerid]); continue; } if ($thisheaderid == "creator_email") { $metaHeaderlist[] = _formulize_DE_CALC_CREATOR_EMAIL; unset($headerlist[$headerid]); continue; } if ($start) { $where_clause = "ele_id='{$thisheaderid}'"; $start = 0; } else { $where_clause .= " OR ele_id='{$thisheaderid}'"; } } if ($where_clause) { $captionq = "SELECT ele_caption, ele_colhead FROM " . $xoopsDB->prefix("formulize") . " WHERE {$where_clause} AND (ele_type != \"ib\" AND ele_type != \"areamodif\" AND ele_type != \"subform\" AND ele_type != \"grid\") ORDER BY ele_order"; if ($rescaptionq = $xoopsDB->query($captionq)) { unset($headerlist); $headerlist = $metaHeaderlist; while ($row = $xoopsDB->fetchArray($rescaptionq)) { if ($row['ele_colhead'] != "") { $headerlist[] = $row['ele_colhead']; } else { $headerlist[] = $row['ele_caption']; } } } else { exit("Error returning the default list of captions."); } } } else { // if getting ids, need to convert old metadata values to new ones foreach ($headerlist as $headerListIndex => $thisheaderid) { if ($thisheaderid == "uid") { $headerlist[$headerListIndex] = "creation_uid"; } elseif ($thisheaderid == "proxyid") { $headerlist[$headerListIndex] = "mod_uid"; } elseif ($thisheaderid == "creation_date") { $headerlist[$headerListIndex] = "creation_datetime"; } elseif ($thisheaderid == "mod_date") { $headerlist[$headerListIndex] = $thisheaderid == "mod_datetime"; } } } } else { // not using new ID based system, so convert to ids if needids is true if ($needids) { $tempheaderlist = $headerlist; unset($headerlist); $headerlist = convertHeadersToIds($tempheaderlist, $fid); } } } if (count($headerlist) == 0) { // if no header fields specified, then // gather required fields for this form $reqfq = "SELECT ele_caption, ele_colhead, ele_id FROM " . $xoopsDB->prefix("formulize") . " WHERE ele_req=1 AND id_form='{$fid}' AND (ele_type != \"ib\" AND ele_type != \"areamodif\" AND ele_type != \"subform\" AND ele_type != \"grid\") ORDER BY ele_order ASC LIMIT 3"; if ($result = $xoopsDB->query($reqfq)) { while ($row = $xoopsDB->fetchArray($result)) { if ($needids) { $headerlist[] = $row['ele_id']; } else { if ($row['ele_colhead'] != "") { $headerlist[] = $row['ele_colhead']; } else { $headerlist[] = $row['ele_caption']; } } } } } if (count($headerlist) == 0) { // IF there are no required fields THEN ... go with first three fields $firstfq = "SELECT ele_caption, ele_colhead, ele_id FROM " . $xoopsDB->prefix("formulize") . " WHERE id_form='{$fid}' AND (ele_type != \"ib\" AND ele_type != \"areamodif\" AND ele_type != \"subform\" AND ele_type != \"grid\") ORDER BY ele_order ASC LIMIT 3"; if ($result = $xoopsDB->query($firstfq)) { while ($row = $xoopsDB->fetchArray($result)) { if ($needids) { $headerlist[] = $row['ele_id']; } else { if ($row['ele_colhead'] != "") { $headerlist[] = $row['ele_colhead']; } else { $headerlist[] = $row['ele_caption']; } } } } } if ($needids and $convertIdsToElementHandles) { $savedMetaHeaders = array(); foreach ($headerlist as $thisheaderkey => $thisheaderid) { // remove non numeric headers and save them if (!is_numeric($thisheaderid)) { $savedMetaHeaders[] = $thisheaderid; unset($headerlist[$thisheaderkey]); } } // if there are any numeric headers, then get the handles if (count($headerlist) > 0) { $sql = "SELECT ele_handle FROM " . $xoopsDB->prefix("formulize") . " WHERE ele_id IN (" . implode(",", $headerlist) . ") ORDER BY ele_order"; if ($res = $xoopsDB->query($sql)) { $headerlist = array(); while ($array = $xoopsDB->fetchArray($res)) { $headerlist[] = $array['ele_handle']; } $headerlist = array_merge($savedMetaHeaders, $headerlist); // add the non numeric headers back in to the front } else { print "Error: could not convert Element IDs to Handles when retrieving the header list. SQL error: " . $xoopsDB->error() . "<br>"; } } else { // no numeric headers, so just return the non numeric ones $headerlist = $savedMetaHeaders; } } return $headerlist; }