/**
* Retrieve Custom Form Fields
* @param bool|int $incident_id The unique incident_id of the original report
* @param int $form_id The unique form_id. Uses default form (1), if none selected
* @param bool $field_names_only Whether or not to include just fields names, or field names + data
* @param bool $data_only Whether or not to include just data
* @param string $action If this is being used to grab fields for submit or view of data
*/
public static function get_custom_form_fields($incident_id = FALSE, $form_id = 1, $data_only = FALSE, $action = "submit")
{
$fields_array = array();
if (!$form_id) {
$form_id = 1;
}
// Validation
if (!Form_Model::is_valid_form($form_id)) {
return $fields_array;
}
// Database table prefix
$table_prefix = Kohana::config('database.default.table_prefix');
//NOTE will probably need to add a user_level variable for non-web based requests
$user_level = self::get_user_max_auth();
// Get the predicates for the public state
$public_state = $action == "view" ? '<=' . $user_level : ' <= ' . $user_level;
// Query to fetch the form fields and their responses
$sql = "SELECT ff.*, '' AS form_response FROM " . $table_prefix . "form_field ff WHERE 1=1 ";
// Check if the provided incident exists
if (Incident_Model::is_valid_incident($incident_id)) {
// Overwrite the previous query
$sql = "SELECT ff.*, fr.form_response " . "FROM " . $table_prefix . "form_field ff " . "RIGHT JOIN " . $table_prefix . "form_response fr ON (fr.form_field_id = ff.id) " . "WHERE fr.incident_id = " . $incident_id . " ";
}
$sql .= "AND ff.form_id = " . $form_id . " " . "AND ff.field_ispublic_visible " . $public_state . " " . "ORDER BY ff.field_position ASC";
// Execute the SQL to fetch the custom form fields
$form_fields = Database::instance()->query($sql);
foreach ($form_fields as $custom_formfield) {
if ($data_only) {
// Return Data Only
$fields_array[$custom_formfield->id] = $custom_formfield->form_response;
} else {
// Return Field Structure
$fields_array[$custom_formfield->id] = array('field_id' => $custom_formfield->id, 'field_name' => $custom_formfield->field_name, 'field_type' => $custom_formfield->field_type, 'field_default' => $custom_formfield->field_default, 'field_required' => $custom_formfield->field_required, 'field_maxlength' => $custom_formfield->field_maxlength, 'field_height' => $custom_formfield->field_height, 'field_width' => $custom_formfield->field_width, 'field_isdate' => $custom_formfield->field_isdate, 'field_ispublic_visible' => $custom_formfield->field_ispublic_visible, 'field_ispublic_submit' => $custom_formfield->field_ispublic_submit, 'field_response' => $custom_formfield->form_response);
}
}
// Garbage collection
unset($form_fields);
// Return
return $fields_array;
}
/**
* Retrieve Custom Form Fields
* @param bool|int $incident_id The unique incident_id of the original report
* @param int $form_id The unique form_id. If none selected, retrieve custom form fields from ALL custom forms
* @param bool $data_only Whether or not to include just data
* @param string $action If this is being used to grab fields for submit or view of data
*/
public static function get_custom_form_fields($incident_id = FALSE, $form_id = NULL, $data_only = FALSE, $action = "submit")
{
$fields_array = array();
// If we have a form id but its invalid, return empty
if (!empty($form_id) and !Form_Model::is_valid_form($form_id)) {
return $fields_array;
}
// Database table prefix
$table_prefix = Kohana::config('database.default.table_prefix');
// Get field we'll check permissions against
$ispublic_field = $action == "view" ? 'field_ispublic_visible' : 'field_ispublic_submit';
// NOTE will probably need to add a user_level variable for non-web based requests
$user_level = self::get_user_max_auth();
// Check if incident is valid
// Have to do this early since we can't build 2 ORM queries at once.
$valid_incident = Incident_Model::is_valid_incident($incident_id, FALSE);
// Check if the provided incident exists, then fill in the data
if ($valid_incident) {
$sql = "SELECT ff.*, fr.form_response\n\t\t\tFROM `{$table_prefix}form_field` ff\n\t\t\tLEFT JOIN `{$table_prefix}roles` r ON (r.id = {$ispublic_field})\n\t\t\tLEFT JOIN\n\t\t\t\t`{$table_prefix}form_response` fr ON (\n\t\t\t\t\tfr.form_field_id = ff.id AND\n\t\t\t\t\tfr.incident_id = :incident_id\n\t\t\t\t)\n\t\t\tWHERE (access_level <= :user_level OR access_level IS NULL) " . (!empty($form_id) ? "AND form_id = :form_id " : '') . "ORDER BY field_position ASC";
} else {
$sql = "SELECT ff.*\n\t\t\tFROM `{$table_prefix}form_field` ff\n\t\t\tLEFT JOIN `{$table_prefix}roles` r ON (r.id = {$ispublic_field})\n\t\t\tWHERE (access_level <= :user_level OR access_level IS NULL) " . (!empty($form_id) ? "AND form_id = :form_id " : '') . "ORDER BY field_position ASC";
}
$form_fields = Database::instance()->query($sql, array(':form_id' => $form_id, ':user_level' => $user_level, ':incident_id' => $incident_id));
foreach ($form_fields as $custom_formfield) {
if ($data_only) {
// Return Data Only
$fields_array[$custom_formfield->id] = isset($custom_formfield->form_response) ? $custom_formfield->form_response : '';
} else {
// Return Field Structure
// JP: added field description
$fields_array[$custom_formfield->id] = array('field_id' => $custom_formfield->id, 'form_id' => $custom_formfield->form_id, 'field_name' => $custom_formfield->field_name, 'field_description' => $custom_formfield->field_description, 'field_type' => $custom_formfield->field_type, 'field_default' => $custom_formfield->field_default, 'field_required' => $custom_formfield->field_required, 'field_maxlength' => $custom_formfield->field_maxlength, 'field_height' => $custom_formfield->field_height, 'field_width' => $custom_formfield->field_width, 'field_isdate' => $custom_formfield->field_isdate, 'field_ispublic_visible' => $custom_formfield->field_ispublic_visible, 'field_ispublic_submit' => $custom_formfield->field_ispublic_submit, 'field_response' => isset($custom_formfield->form_response) ? $custom_formfield->form_response : '');
}
}
// Garbage collection
unset($form_fields);
// Return
return $fields_array;
}
/**
* Retrieve Custom Form Fields
* @param bool|int $incident_id The unique incident_id of the original report
* @param int $form_id The unique form_id. If none selected, retrieve custom form fields from ALL custom forms
* @param bool $data_only Whether or not to include just data
* @param string $action If this is being used to grab fields for submit or view of data
*/
public static function get_custom_form_fields($incident_id = FALSE, $form_id = NULL, $data_only = FALSE, $action = "submit")
{
$fields_array = array();
if ($form_id != null and $form_id != '') {
// Validation
if (!Form_Model::is_valid_form($form_id)) {
return $fields_array;
}
}
// Database table prefix
$table_prefix = Kohana::config('database.default.table_prefix');
// Get field we'll check permissions against
$ispublic_field = $action == "view" ? 'field_ispublic_visible' : 'field_ispublic_submit';
// Query to fetch the form fields associated with the given form id
$sql = "SELECT ff.*, '' AS form_response FROM " . $table_prefix . "form_field ff LEFT JOIN roles r ON (r.id = {$ispublic_field}) WHERE 1=1 ";
if ($form_id != null and $form_id != '') {
$sql .= "AND ff.form_id = " . $form_id . " ";
}
// NOTE will probably need to add a user_level variable for non-web based requests
$user_level = self::get_user_max_auth();
// Check access_level
$sql .= 'AND (r.access_level <= ' . $user_level . ' OR r.access_level IS NULL)';
$sql .= " ORDER BY ff.field_position ASC";
// Execute the SQL to fetch the custom form fields
$form_fields = Database::instance()->query($sql);
foreach ($form_fields as $custom_formfield) {
if ($data_only) {
// Return Data Only
$fields_array[$custom_formfield->id] = $custom_formfield->form_response;
} else {
// Return Field Structure
$fields_array[$custom_formfield->id] = array('field_id' => $custom_formfield->id, 'field_name' => $custom_formfield->field_name, 'field_type' => $custom_formfield->field_type, 'field_default' => $custom_formfield->field_default, 'field_required' => $custom_formfield->field_required, 'field_maxlength' => $custom_formfield->field_maxlength, 'field_height' => $custom_formfield->field_height, 'field_width' => $custom_formfield->field_width, 'field_isdate' => $custom_formfield->field_isdate, 'field_ispublic_visible' => $custom_formfield->field_ispublic_visible, 'field_ispublic_submit' => $custom_formfield->field_ispublic_submit, 'field_response' => $custom_formfield->form_response);
}
}
// Garbage collection
unset($form_fields);
// Check if the provided incident exists, then fill in the data
if (Incident_Model::is_valid_incident($incident_id)) {
// Overwrite the previous query
$sql = "SELECT ff.*, fr.form_response " . "FROM " . $table_prefix . "form_field ff " . "RIGHT JOIN " . $table_prefix . "form_response fr ON (fr.form_field_id = ff.id) " . "LEFT JOIN roles r ON (r.id = {$ispublic_field})" . "WHERE fr.incident_id = " . $incident_id . " ";
if ($form_id != null and $form_id != '') {
$sql .= "AND ff.form_id = " . $form_id . " ";
}
$sql .= 'AND (r.access_level <= ' . $user_level . ' OR r.access_level IS NULL)';
$sql .= " ORDER BY ff.field_position ASC";
// Execute the SQL to fetch the custom form fields
$form_fields = Database::instance()->query($sql);
foreach ($form_fields as $custom_formfield) {
if ($data_only) {
// Return Data Only
$fields_array[$custom_formfield->id] = $custom_formfield->form_response;
} else {
// Return Field Structure
$fields_array[$custom_formfield->id] = array('field_id' => $custom_formfield->id, 'field_name' => $custom_formfield->field_name, 'field_type' => $custom_formfield->field_type, 'field_default' => $custom_formfield->field_default, 'field_required' => $custom_formfield->field_required, 'field_maxlength' => $custom_formfield->field_maxlength, 'field_height' => $custom_formfield->field_height, 'field_width' => $custom_formfield->field_width, 'field_isdate' => $custom_formfield->field_isdate, 'field_ispublic_visible' => $custom_formfield->field_ispublic_visible, 'field_ispublic_submit' => $custom_formfield->field_ispublic_submit, 'field_response' => $custom_formfield->form_response);
}
}
}
// Garbage collection
unset($form_fields);
// Return
return $fields_array;
}
/**
* Function to import a report form a row in the CSV file
* @param array $row
* @return bool
*/
function import_report($row)
{
// If the date is not in proper date format
if (!strtotime($row['INCIDENT DATE'])) {
$this->errors[] = Kohana::lang('import.incident_date') . ($this->rownumber + 1) . ': ' . $row['INCIDENT DATE'];
}
// If a value of Yes or No is NOT set for approval status for the imported row
if (isset($row["APPROVED"]) and !in_array(utf8::strtoupper($row["APPROVED"]), array('NO', 'YES'))) {
$this->errors[] = Kohana::lang('import.csv.approved') . ($this->rownumber + 1);
}
// If a value of Yes or No is NOT set for verified status for the imported row
if (isset($row["VERIFIED"]) and !in_array(utf8::strtoupper($row["VERIFIED"]), array('NO', 'YES'))) {
$this->errors[] = Kohana::lang('import.csv.verified') . ($this->rownumber + 1);
}
if (count($this->errors)) {
return false;
}
// STEP 1: SAVE LOCATION
if (isset($row['LOCATION'])) {
$location = new Location_Model();
$location->location_name = isset($row['LOCATION']) ? $row['LOCATION'] : '';
// For Geocoding purposes
$location_geocoded = map::geocode($location->location_name);
// If we have LATITUDE and LONGITUDE use those
if (isset($row['LATITUDE']) and isset($row['LONGITUDE'])) {
$location->latitude = isset($row['LATITUDE']) ? $row['LATITUDE'] : 0;
$location->longitude = isset($row['LONGITUDE']) ? $row['LONGITUDE'] : 0;
} else {
$location->latitude = $location_geocoded ? $location_geocoded['latitude'] : 0;
$location->longitude = $location_geocoded ? $location_geocoded['longitude'] : 0;
}
$location->country_id = $location_geocoded ? $location_geocoded['country_id'] : 0;
$location->location_date = $this->time;
$location->save();
$this->locations_added[] = $location->id;
}
// STEP 2: SAVE INCIDENT
$incident = new Incident_Model();
$incident->location_id = isset($row['LOCATION']) ? $location->id : 0;
$incident->user_id = 0;
$incident->form_id = (isset($row['FORM #']) and Form_Model::is_valid_form($row['FORM #'])) ? $row['FORM #'] : 1;
$incident->incident_title = $row['INCIDENT TITLE'];
$incident->incident_description = isset($row['DESCRIPTION']) ? $row['DESCRIPTION'] : '';
$incident->incident_date = date("Y-m-d H:i:s", strtotime($row['INCIDENT DATE']));
$incident->incident_dateadd = $this->time;
$incident->incident_active = (isset($row['APPROVED']) and utf8::strtoupper($row['APPROVED']) == 'YES') ? 1 : 0;
$incident->incident_verified = (isset($row['VERIFIED']) and utf8::strtoupper($row['VERIFIED']) == 'YES') ? 1 : 0;
$incident->save();
$this->incidents_added[] = $incident->id;
// STEP 3: Save Personal Information
if (isset($row['FIRST NAME']) or isset($row['LAST NAME']) or isset($row['EMAIL'])) {
$person = new Incident_Person_Model();
$person->incident_id = $incident->id;
$person->person_first = isset($row['FIRST NAME']) ? $row['FIRST NAME'] : '';
$person->person_last = isset($row['LAST NAME']) ? $row['LAST NAME'] : '';
$person->person_email = (isset($row['EMAIL']) and valid::email($row['EMAIL'])) ? $row['EMAIL'] : '';
$person->person_date = date("Y-m-d H:i:s", time());
// Make sure that you're not importing an empty record i.e at least one field has been recorded
// If all fields are empty i.e you have an empty record, don't save
if (!empty($person->person_first) or !empty($person->person_last) or !empty($person->person_email)) {
$person->save();
// Add to array of incident persons added
$this->incident_persons_added[] = $person->id;
}
}
// STEP 4: SAVE CATEGORIES
// If CATEGORY column exists
if (isset($row['CATEGORY'])) {
$categorynames = explode(',', trim($row['CATEGORY']));
// Trim whitespace from array values
$categorynames = array_map('trim', $categorynames);
// Get rid of duplicate category entries in a row
$categories = array_unique(array_map('strtolower', $categorynames));
// Add categories to incident
foreach ($categories as $categoryname) {
// Convert the first string character of the category name to Uppercase
$categoryname = utf8::ucfirst($categoryname);
// For purposes of adding an entry into the incident_category table
$incident_category = new Incident_Category_Model();
$incident_category->incident_id = $incident->id;
// If category name exists, add entry in incident_category table
if ($categoryname != '') {
// Check if the category exists (made sure to convert to uppercase for comparison)
if (!isset($this->existing_categories[utf8::strtoupper($categoryname)])) {
$this->notices[] = Kohana::lang('import.new_category') . $categoryname;
$category = new Category_Model();
$category->category_title = $categoryname;
// We'll just use black for now. Maybe something random?
$category->category_color = '000000';
// because all current categories are of type '5'
$category->category_visible = 1;
$category->category_description = $categoryname;
$category->category_position = count($this->existing_categories);
$category->save();
$this->categories_added[] = $category->id;
// Now category_id is known: This time, and for the rest of the import.
$this->existing_categories[utf8::strtoupper($categoryname)] = $category->id;
}
$incident_category->category_id = $this->existing_categories[utf8::strtoupper($categoryname)];
$incident_category->save();
$this->incident_categories_added[] = $incident_category->id;
}
}
}
// STEP 5: Save Custom form fields responses
// Check for form_id
$form_id = (isset($row['FORM #']) and Form_Model::is_valid_form($row['FORM #'])) ? $row['FORM #'] : 1;
// Get custom form fields for this particular form
$custom_titles = customforms::get_custom_form_fields('', $form_id, false);
// Do custom form fields exist on this deployment?
if (!empty($custom_titles)) {
foreach ($custom_titles as $field_name) {
// Check if the column exists in the CSV
$rowname = utf8::strtoupper($field_name['field_name']);
if (isset($row[$rowname . '-' . $form_id])) {
$response = $row[$rowname . '-' . $form_id];
// Grab field_id and field_type
$field_id = $field_name['field_id'];
$field_type = $field_name['field_type'];
// Initialize form response model
$form_response = new Form_Response_Model();
$form_response->incident_id = $incident->id;
$form_response->form_field_id = $field_id;
// If form response exists
if ($response != '') {
/* Handling case sensitivity issues with custom form field upload */
// Check if the field is a radio button, checkbox OR dropdown field
if ($field_type == '5' or $field_type == '6' or $field_type == '7') {
// Get field option values
$field_values = $field_name['field_default'];
// Split field options into individual values
$options = explode(",", $field_values);
// Since radio button and dropdown fields take single responses
if ($field_type == '5' or $field_type == '7') {
foreach ($options as $option) {
// Carry out a case insensitive comparison between individual field options and csv response
// If there's a match, store field option value from the db
if (strcasecmp($option, $response) == 0) {
$form_response->form_response = $option;
}
}
}
// For checkboxes, which accomodate multiple responses
if ($field_type == '6') {
// Split user responses into single values
$csvresponses = explode(",", $response);
$values = array();
foreach ($options as $option) {
foreach ($csvresponses as $csvresponse) {
// Carry out a case insensitive comparison between individual field options and csv response
// If there's a match
if (strcasecmp($option, $csvresponse) == 0) {
// Store field option value from the db
$values[] = $option;
}
}
}
// Concatenate checkbox values into a string, separated by a comma
$form_response->form_response = implode(",", $values);
}
} else {
$form_response->form_response = $response;
}
// If form_response is provided based on conditions set above, Save the form response
if ($form_response->form_response != '') {
$form_response->save();
// Add to array of field responses added
$this->incident_responses_added[] = $form_response->id;
}
}
}
}
}
return true;
}
/**
* JP: Edit & Save Advanced Form Field
*/
public function advanced_field_edit()
{
$this->template = "";
$this->auto_render = FALSE;
// setup and initialize form field names
$form = array('report_title_name' => '', 'description_name' => '', 'description_active' => '');
// copy the form as errors, so the errors will be stored with keys corresponding to the form field names
$errors = $form;
$advanced_edit_status = "";
$advanced_edit_response = "";
if ($_POST) {
// @todo Manually extract the data to be validated
$form_data = arr::extract($_POST, 'advanced_form_id', 'advanced_form_title', 'advanced_form_description', 'advanced_form_active', 'report_title_name', 'description_name', 'description_active');
// Form Model instance
$custom_form = Form_Model::is_valid_form($_POST['advanced_form_id']) ? ORM::factory('form', $_POST['advanced_form_id']) : new Form_Model();
// Validate the form data
if ($custom_form->validate(Validation::factory($form_data))) {
// Validation succeeded, proceed...
// Save the new or modified entries
// JP: The Report Title and Description fields are saved as null in the database if they match the default names or are empty.
if (strcmp(trim($form_data['report_title_name']), Kohana::lang('ui_main.reports_title')) === 0 or empty(trim($form_data['report_title_name']))) {
$custom_form->report_title_name = null;
} else {
$custom_form->report_title_name = trim($form_data['report_title_name']);
}
if (strcmp(trim($form_data['description_name']), Kohana::lang('ui_main.reports_description')) === 0 or empty(trim($form_data['description_name']))) {
$custom_form->description_name = null;
} else {
$custom_form->description_name = trim($form_data['description_name']);
}
$custom_form->description_active = $form_data['description_active'];
$custom_form->save();
$advanced_edit_status = "success";
$advanced_edit_response = rawurlencode(customforms::get_current_fields($custom_form->id, $this->user));
} else {
// Repopulate the form fields
$form = arr::overwrite($form, $form_data->as_array());
// Populate the error fields, if any
$errors = arr::overwrite($errors, $form_data->errors('form'));
// populate the response to this post request
$advanced_edit_status = "error";
$advanced_edit_response = "";
$advanced_edit_response .= "<ul>";
foreach ($errors as $error_item => $error_description) {
$advanced_edit_response .= !$error_description ? '' : "<li>" . $error_description . "</li>";
}
$advanced_edit_response .= "</ul>";
}
}
echo json_encode(array("status" => $advanced_edit_status, "response" => $advanced_edit_response));
}
