Exemplo n.º 1
0
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;
}
Exemplo n.º 2
0
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;
}