/**
* Retrieves everything about a form submission. It contains a lot of meta-information about the field,
* from the form_fields and view_tabs. If the optional view_id parameter is included, only the fields
* in the View are returned (AND all system fields, if they're not included).
*
* @param integer $form_id the unique form ID
* @param integer $submission_id the unique submission ID
* @param integer $view_id an optional view ID parameter
* @return array Returns an array of hashes. Each index is a separate form field and its value is
* a hash of information about it, such as value, field type, field size, etc.
*/
function ft_get_submission($form_id, $submission_id, $view_id = "")
{
global $g_table_prefix;
$return_arr = array();
$form_fields = ft_get_form_fields($form_id);
$submission = ft_get_submission_info($form_id, $submission_id);
$view_fields = !empty($view_id) ? ft_get_view_fields($view_id) : array();
if (empty($submission)) {
return array();
}
$view_field_ids = array();
foreach ($view_fields as $view_field) {
$view_field_ids[] = $view_field["field_id"];
}
// for each field, combine the meta form info (like field size, type, data type etc) from $form_fields
// with the info about the submission itself. Also, if there's a View specified, filter out any fields
// that aren't used in the View
foreach ($form_fields as $field_info) {
$field_id = $field_info["field_id"];
// if we're looking at this submission through a View,
if (!empty($view_id) && !in_array($field_id, $view_field_ids)) {
continue;
}
// if the submission contains contents for this field, add it
if (array_key_exists($field_info['col_name'], $submission)) {
$field_info["content"] = $submission[$field_info['col_name']];
}
// if a view ID is specified, return the view-specific field info as well
if (!empty($view_id)) {
$field_view_info = ft_get_view_field($view_id, $field_id);
if (!empty($field_view_info)) {
foreach ($field_view_info as $key => $value) {
$field_info[$key] = $value;
}
}
}
$return_arr[] = $field_info;
}
// finally, if a View is specified, ensure that the order in which the submission fields are returned
// is determined by the View. [NOT efficient!]
if (!empty($view_id)) {
$ordered_return_arr = array();
foreach ($view_fields as $view_field_info) {
$field_id = $view_field_info["field_id"];
foreach ($return_arr as $field_info) {
if ($field_info["field_id"] == $field_id) {
$ordered_return_arr[] = $field_info;
break;
}
}
}
$return_arr = $ordered_return_arr;
}
extract(ft_process_hook_calls("end", compact("form_id", "submission_id", "view_id", "return_arr"), array("return_arr")), EXTR_OVERWRITE);
return $return_arr;
}
/**
* Return all fields in a View. If this is being on the edit submission page, the second optional
* parameter is used to limit the results to ONLY those groups on the appropriate tab.
*
* @param integer $view_id
* @param integer $tab_number
* @param integer $form_id - this is optional. If this and the next $submission_id param is defined,
* details about the actual form submission is returned as well
* @param integer $submission_id
* @return array
*/
function ft_get_grouped_view_fields($view_id, $tab_number = "", $form_id = "", $submission_id = "")
{
global $g_table_prefix;
$tab_clause = !empty($tab_number) ? "AND custom_data = {$tab_number}" : "";
$group_query = mysql_query("\r\n SELECT *\r\n FROM {$g_table_prefix}list_groups\r\n WHERE group_type = 'view_fields_{$view_id}'\r\n {$tab_clause}\r\n ORDER BY list_order\r\n ");
if (!empty($submission_id)) {
$submission_info = ft_get_submission_info($form_id, $submission_id);
}
$grouped_info = array();
while ($group_info = mysql_fetch_assoc($group_query)) {
$group_id = $group_info["group_id"];
$field_query = mysql_query("\r\n SELECT *, vf.list_order as list_order, vf.is_new_sort_group as view_field_is_new_sort_group\r\n FROM {$g_table_prefix}view_fields vf, {$g_table_prefix}form_fields ff\r\n WHERE group_id = {$group_id} AND\r\n vf.field_id = ff.field_id\r\n ORDER BY vf.list_order\r\n ");
$fields_info = array();
$field_ids = array();
while ($row = mysql_fetch_assoc($field_query)) {
$field_ids[] = $row["field_id"];
$fields_info[] = $row;
}
// for efficiency reasons, we just do a single query to find all validation rules for the all relevant fields
if (!empty($field_ids)) {
$field_ids_str = implode(",", $field_ids);
$validation_query = mysql_query("\r\n SELECT *\r\n FROM {$g_table_prefix}field_validation fv, {$g_table_prefix}field_type_validation_rules ftvr\r\n WHERE fv.field_id IN ({$field_ids_str}) AND\r\n fv.rule_id = ftvr.rule_id\r\n ");
$rules_by_field_id = array();
while ($rule_info = mysql_fetch_assoc($validation_query)) {
$field_id = $rule_info["field_id"];
if (!array_key_exists($field_id, $rules_by_field_id)) {
$rules_by_field_id[$field_id]["is_required"] = false;
$rules_by_field_id[$field_id]["rules"] = array();
}
$rules_by_field_id[$field_id]["rules"][] = $rule_info;
if ($rule_info["rsv_rule"] == "required" || $rule_info["rsv_rule"] == "function" && $rule_info["custom_function_required"] == "yes") {
$rules_by_field_id[$field_id]["is_required"] = true;
}
}
}
// now merge the original field info with the new validation rules. "required" is a special validation rule: that's
// used to determine whether or not an asterix should appear next to the field. As such, we pass along a
// custom "is_required" key
$updated_field_info = array();
foreach ($fields_info as $field_info) {
$curr_field_id = $field_info["field_id"];
$field_info["validation"] = array_key_exists($curr_field_id, $rules_by_field_id) ? $rules_by_field_id[$curr_field_id]["rules"] : array();
$field_info["is_required"] = array_key_exists($curr_field_id, $rules_by_field_id) ? $rules_by_field_id[$curr_field_id]["is_required"] : false;
$updated_field_info[] = $field_info;
}
$fields_info = $updated_field_info;
// now, if the submission ID is set it returns an additional submission_value key
if (!empty($field_ids)) {
// do a single query to get a list of ALL settings for any of the field IDs we're dealing with
$field_id_str = implode(",", $field_ids);
$field_settings_query = mysql_query("\r\n SELECT *\r\n FROM {$g_table_prefix}field_settings\r\n WHERE field_id IN ({$field_id_str})\r\n ");
$field_settings = array();
while ($row = mysql_fetch_assoc($field_settings_query)) {
$field_id = $row["field_id"];
if (!array_key_exists($field_id, $field_settings)) {
$field_settings[$field_id] = array();
}
$field_settings[$field_id][] = array($row["setting_id"] => $row["setting_value"]);
}
// now append the submission info to the field info that we already have stored
$updated_fields_info = array();
foreach ($fields_info as $curr_field_info) {
$curr_col_name = $curr_field_info["col_name"];
$curr_field_id = $curr_field_info["field_id"];
$curr_field_info["field_settings"] = array_key_exists($curr_field_id, $field_settings) ? $field_settings[$curr_field_id] : array();
if (!empty($submission_id)) {
$curr_field_info["submission_value"] = $submission_info[$curr_col_name];
}
$updated_fields_info[] = $curr_field_info;
}
$fields_info = $updated_fields_info;
}
$grouped_info[] = array("group" => $group_info, "fields" => $fields_info);
}
return $grouped_info;
}
/**
* Completely removes a form from the database. This includes deleting all form fields, emails, Views,
* View fields, View tabs, View filters, client-form, client-view and public omit list (form & View),
* and anything else !
*
* It also includes an optional parameter to remove all files that were uploaded through file fields in the
* form; defaulted to FALSE.
*
* @param integer $form_id the unique form ID
* @param boolean $remove_associated_files A boolean indicating whether or not all files that were
* uploaded via file fields in this form should be removed as well.
*/
function ft_delete_form($form_id, $remove_associated_files = false)
{
global $g_table_prefix;
extract(ft_process_hook_calls("start", compact("form_id"), array()), EXTR_OVERWRITE);
$form_fields = ft_get_form_fields($form_id, array("include_field_type_info" => true));
$success = true;
$message = "";
$file_delete_problems = array();
if ($remove_associated_files) {
$submission_id_query = mysql_query("SELECT submission_id FROM {$g_table_prefix}form_{$form_id}");
$file_fields_to_delete = array();
while ($row = mysql_fetch_assoc($submission_id_query)) {
$submission_id = $row["submission_id"];
foreach ($form_fields as $form_field_info) {
if ($form_field_info["is_file_field"] == "no") {
continue;
}
// I really don't like this... what should be done is do a SINGLE query after this loop is complete
// to return a map of field_id to values. That would then update $file_fields_to_delete
// with a fraction of the cost
$submission_info = ft_get_submission_info($form_id, $submission_id);
$filename = $submission_info[$form_field_info["col_name"]];
// if no filename was stored, it was empty - just continue
if (empty($filename)) {
continue;
}
$file_fields_to_delete[] = array("submission_id" => $submission_id, "field_id" => $form_field_info["field_id"], "field_type_id" => $field_type_id, "filename" => $filename);
}
}
if (!empty($file_fields_to_delete)) {
list($success, $file_delete_problems) = ft_delete_submission_files($form_id, $file_fields_to_delete, "ft_delete_form");
}
}
// remove the table
$query = "DROP TABLE IF EXISTS {$g_table_prefix}form_{$form_id}";
mysql_query($query) or ft_handle_error("Failed query in <b>" . __FUNCTION__ . "</b>, line " . __LINE__ . ": <i>{$query}</i>", mysql_error());
// remove any reference to the form in form_fields
mysql_query("DELETE FROM {$g_table_prefix}form_fields WHERE form_id = {$form_id}") or ft_handle_error("Failed query in <b>" . __FUNCTION__ . "</b>, line " . __LINE__ . ": <i>{$query}</i>", mysql_error());
// remove any reference to the form in forms table
mysql_query("DELETE FROM {$g_table_prefix}forms WHERE form_id = {$form_id}");
mysql_query("DELETE FROM {$g_table_prefix}client_forms WHERE form_id = {$form_id}");
mysql_query("DELETE FROM {$g_table_prefix}form_export_templates WHERE form_id = {$form_id}");
mysql_query("DELETE FROM {$g_table_prefix}form_email_fields WHERE form_id = {$form_id}");
mysql_query("DELETE FROM {$g_table_prefix}public_form_omit_list WHERE form_id = {$form_id}");
mysql_query("DELETE FROM {$g_table_prefix}multi_page_form_urls WHERE form_id = {$form_id}");
mysql_query("DELETE FROM {$g_table_prefix}list_groups WHERE group_type = 'form_{$form_id}_view_group'");
// delete all email templates for the form
$email_templates = ft_get_email_template_list($form_id);
foreach ($email_templates as $email_template_info) {
ft_delete_email_template($email_template_info["email_id"]);
}
// delete all form Views
$views_result = mysql_query("SELECT view_id FROM {$g_table_prefix}views WHERE form_id = {$form_id}");
while ($info = mysql_fetch_assoc($views_result)) {
ft_delete_view($info["view_id"]);
}
// remove any field settings
foreach ($form_fields as $field_info) {
$field_id = $field_info["field_id"];
mysql_query("DELETE FROM {$g_table_prefix}field_settings WHERE field_id = {$field_id}");
}
// as with many things in the script, potentially we need to return a vast range of information from this last function. But
// we'l limit
if (!$success) {
$message = $file_delete_problems;
}
return array($success, $message);
}
/**
* Uploads a file for a particular form submission field. This is called AFTER the submission has already been
* added to the database so there's an available, valid submission ID. It uploads the file to the appropriate
* folder then updates the database record.
*
* Since any submission file field can only ever store a single file at once, this function automatically deletes
* the old file in the event of the new file being successfully uploaded.
*
* @param integer $form_id the unique form ID
* @param integer $submission_id a unique submission ID
* @param array $file_field_info
* @return array returns array with indexes:<br/>
* [0]: true/false (success / failure)<br/>
* [1]: message string<br/>
* [2]: If success, the filename of the uploaded file
*/
function ft_file_upload_submission_file($form_id, $submission_id, $file_field_info)
{
global $g_table_prefix, $g_filename_char_whitelist, $LANG;
// get the column name and upload folder for this field
$field_id = $file_field_info["field_id"];
$col_name = $file_field_info["field_info"]["col_name"];
// if the column name wasn't found, the $field_id passed in was invalid. Somethin' aint right...
if (empty($col_name)) {
return array(false, $LANG["notify_submission_no_field_id"]);
}
// clean up the filename according to the whitelist chars
$field_name = $file_field_info["field_info"]["field_name"];
$fileinfo = $_FILES[$field_name];
$filename_parts = explode(".", $fileinfo["name"]);
$extension = $filename_parts[count($filename_parts) - 1];
array_pop($filename_parts);
$filename_without_extension = implode(".", $filename_parts);
$valid_chars = preg_quote($g_filename_char_whitelist);
$filename_without_ext_clean = preg_replace("/[^{$valid_chars}]/", "", $filename_without_extension);
// unlikely, but...!
if (empty($filename_without_ext_clean)) {
$filename_without_ext_clean = "file";
}
$filename = $filename_without_ext_clean . "." . $extension;
$tmp_filename = $fileinfo["tmp_name"];
$filesize = $fileinfo["size"];
// always in BYTES
$filesize_kb = $filesize / 1000;
// pull a couple of values out of the field's settings (these are custom to the field)
$file_upload_max_size = $file_field_info["settings"]["max_file_size"];
$file_upload_dir = $file_field_info["settings"]["folder_path"];
$permitted_file_types = $file_field_info["settings"]["permitted_file_types"];
// check file size
if ($filesize_kb > $file_upload_max_size) {
$placeholders = array("FILESIZE" => round($filesize_kb, 1), "MAXFILESIZE" => $file_upload_max_size);
$error = ft_eval_smarty_string($LANG["notify_file_too_large"], $placeholders);
return array(false, $error);
}
// check upload folder is valid and writable
if (!is_dir($file_upload_dir) || !is_writable($file_upload_dir)) {
return array(false, $LANG["notify_invalid_field_upload_folder"]);
}
// check file extension is valid. Note: this is "dumb" - it just tests for the file extension string, not
// the actual file type based on it's header info [this is done because I want to allow users to permit
// uploading of any file types, and I can't know about all header types]
$is_valid_extension = true;
if (!empty($permitted_file_types)) {
$is_valid_extension = false;
$raw_extensions = explode(",", $permitted_file_types);
foreach ($raw_extensions as $ext) {
// remove whitespace and periods
$clean_extension = str_replace(".", "", trim($ext));
if (preg_match("/{$clean_extension}\$/i", $filename)) {
$is_valid_extension = true;
}
}
}
// all checks out!
if ($is_valid_extension) {
// find out if there was already a file uploaded in this field. We make a note of this so that
// in case the new file upload is successful, we automatically delete the old file
$submission_info = ft_get_submission_info($form_id, $submission_id);
$old_filename = !empty($submission_info[$col_name]) ? $submission_info[$col_name] : "";
// check for duplicate filenames and get a unique name
$unique_filename = ft_get_unique_filename($file_upload_dir, $filename);
// copy file to uploads folder and remove temporary file
if (@rename($tmp_filename, "{$file_upload_dir}/{$unique_filename}")) {
@chmod("{$file_upload_dir}/{$unique_filename}", 0777);
// update the database
$query = "\n UPDATE {$g_table_prefix}form_{$form_id}\n SET {$col_name} = '{$unique_filename}'\n WHERE submission_id = {$submission_id}\n ";
$result = mysql_query($query);
if ($result) {
// if there was a file previously uploaded in this field, delete it!
if (!empty($old_filename)) {
@unlink("{$file_upload_dir}/{$old_filename}");
}
return array(true, $LANG["notify_file_uploaded"], $unique_filename);
} else {
return array(false, $LANG["notify_file_not_uploaded"]);
}
} else {
return array(false, $LANG["notify_file_not_uploaded"]);
}
} else {
return array(false, $LANG["notify_unsupported_file_extension"]);
}
}
