/**
 * Creates and returns a search for any form View, and any subset of its columns, returning results in
 * any column order and for any single page subset (or all pages). The final $search_columns parameter
 * was added most recently to fix bug #173. That parameter lets the caller differentiate between the
 * columns being returned ($columns param) and columns to be searched ($search_columns).
 *
 * @param integer $form_id the unique form ID
 * @param integer $view_id the unique View ID
 * @param mixed $results_per_page an integer, or "all".
 * @param integer $page_num The current page number - or empty string, if this function is returning all
 *              results in one page (e.g. printer friendly page).
 * @param string $order A string of form: "{db column}_{ASC|DESC}"
 * @param mixed $columns An array containing which database columns to search and return, or a string:
 *              "all" - which returns all columns in the form.
 * @param array $search_fields an optional hash with these keys:<br/>
 *                  search_field<br/>
 *                  search_date<br/>
 *                  search_keyword<br/>
 * @param array submission_ids - an optional array containing a list of submission IDs to return.
 *     This may seem counterintuitive to pass the results that it needs to return to the function that
 *     figures out WHICH results to return, but it's actually kinda handy: this function returns exactly
 *     the field information that's needed in the order that's needed.
 * @param array $submission_ids an optional array of submission IDs to return
 * @param array $search_columns an optional array determining which database columns should be included
 *     in the search. Note: this is different from the $columns parameter which just determines which
 *     database columns will be returned. If it's not defined, it's just set to $columns.
 *
 * @return array returns a hash with these keys:<br/>
 *                ["search_query"]       => an array of hashes, each index a search result row<br />
 *                ["search_num_results"] => the number of results in the search (not just the 10 or so
 *                                          that will appear in the current page, listed in the
 *                                          "search_query" key<br />
 *                ["view_num_results"]   => the total number of results in this View, regardless of the
 *                                          current search values.
 */
function ft_search_submissions($form_id, $view_id, $results_per_page, $page_num, $order, $columns_to_return, $search_fields = array(), $submission_ids = array(), $searchable_columns = array())
{
    global $g_table_prefix;
    // for backward compatibility
    if (empty($searchable_columns)) {
        $searchable_columns = $columns_to_return;
    }
    // determine the various SQL clauses for the searches
    $order_by = _ft_get_search_submissions_order_by_clause($form_id, $order);
    $limit_clause = _ft_get_limit_clause($page_num, $results_per_page);
    $select_clause = _ft_get_search_submissions_select_clause($columns_to_return);
    $filter_clause = _ft_get_search_submissions_view_filter_clause($view_id);
    $submission_id_clause = _ft_get_search_submissions_submission_id_clause($submission_ids);
    $search_where_clause = _ft_get_search_submissions_search_where_clause($form_id, $search_fields, $searchable_columns);
    // (1) our main search query that returns a PAGE of submission info
    $search_query = "\n      SELECT {$select_clause}\n      FROM   {$g_table_prefix}form_{$form_id}\n      WHERE  is_finalized = 'yes'\n             {$search_where_clause}\n             {$filter_clause}\n             {$submission_id_clause}\n      ORDER BY {$order_by}\n             {$limit_clause}\n  ";
    $search_result = mysql_query($search_query) or ft_handle_error("Failed query in <b>" . __FUNCTION__ . "</b>; Query: {$search_query}; Error: ", mysql_error());
    $search_result_rows = array();
    while ($row = mysql_fetch_assoc($search_result)) {
        $search_result_rows[] = $row;
    }
    // (2) find out how many results there are in this current search
    $search_results_count_query = mysql_query("\n      SELECT count(*) as c\n      FROM   {$g_table_prefix}form_{$form_id}\n      WHERE  is_finalized = 'yes'\n             {$search_where_clause}\n             {$filter_clause}\n             {$submission_id_clause}\n                 ") or ft_handle_error("Failed query in <b>" . __FUNCTION__ . "</b>: ", mysql_error());
    $search_num_results_info = mysql_fetch_assoc($search_results_count_query);
    $search_num_results = $search_num_results_info["c"];
    // (3) find out how many results should appear in the View, regardless of the current search criteria
    $view_results_count_query = mysql_query("\n      SELECT count(*) as c\n      FROM   {$g_table_prefix}form_{$form_id}\n      WHERE  is_finalized = 'yes'\n             {$filter_clause}\n                 ") or ft_handle_error("Failed query in <b>" . __FUNCTION__ . "</b>: ", mysql_error());
    $view_num_results_info = mysql_fetch_assoc($view_results_count_query);
    $view_num_results = $view_num_results_info["c"];
    $return_hash["search_rows"] = $search_result_rows;
    $return_hash["search_num_results"] = $search_num_results;
    $return_hash["view_num_results"] = $view_num_results;
    extract(ft_process_hook_calls("end", compact("form_id", "submission_id", "view_id", "results_per_page", "page_num", "order", "columns", "search_fields", "submission_ids", "return_hash"), array("return_hash")), EXTR_OVERWRITE);
    return $return_hash;
}
Exemple #2
0
/**
 * Retrieves all field information about a form, ordered by list_order. The 2nd and 3rd optional
 * parameters let you return a subset of the fields for a particular page. This function is purely
 * concerned with the raw fields themselves: not how they are arbitrarily grouped in a View. To
 * retrieve the grouped fields list for a View, use ft_get_view_fields().
 *
 * @param integer $form_id the unique form ID
 * @param array $custom_settings optional settings
 * @return array an array of hash information
 */
function ft_get_form_fields($form_id, $custom_params = array())
{
    global $g_table_prefix;
    $params = array("page" => isset($custom_params["page"]) ? $custom_params["page"] : 1, "num_fields_per_page" => isset($custom_params["num_fields_per_page"]) ? $custom_params["num_fields_per_page"] : "all", "include_field_type_info" => isset($custom_params["include_field_type_info"]) ? $custom_params["include_field_type_info"] : false, "include_field_settings" => isset($custom_params["include_field_settings"]) ? $custom_params["include_field_settings"] : false, "evaluate_dynamic_settings" => isset($custom_params["evaluate_dynamic_settings"]) ? $custom_params["evaluate_dynamic_settings"] : false, "field_ids" => isset($custom_params["field_ids"]) ? $custom_params["field_ids"] : "all");
    $limit_clause = _ft_get_limit_clause($params["page"], $params["num_fields_per_page"]);
    if ($params["include_field_type_info"]) {
        $query = mysql_query("\n      SELECT ff.*, ft.field_type_name, ft.is_file_field, ft.is_date_field\n      FROM   {$g_table_prefix}form_fields ff, {$g_table_prefix}field_types ft\n      WHERE  ff.form_id = {$form_id} AND\n             ff.field_type_id = ft.field_type_id\n      ORDER BY ff.list_order\n      {$limit_clause}\n    ");
    } else {
        $field_id_clause = "";
        if ($params["field_ids"] != "all") {
            $field_id_clause = "AND field_id IN (" . implode(",", $params["field_ids"]) . ")";
        }
        $query = mysql_query("\n      SELECT *\n      FROM   {$g_table_prefix}form_fields\n      WHERE  form_id = {$form_id}\n             {$field_id_clause}\n      ORDER BY list_order\n      {$limit_clause}\n    ");
    }
    $infohash = array();
    while ($row = mysql_fetch_assoc($query)) {
        if ($params["include_field_settings"]) {
            $row["settings"] = ft_get_form_field_settings($row["field_id"], $params["evaluate_dynamic_settings"]);
        }
        $infohash[] = $row;
    }
    extract(ft_process_hook_calls("end", compact("form_id", "infohash"), array("infohash")), EXTR_OVERWRITE);
    return $infohash;
}