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