&$item ) { $item['field_label'] = isset( $item['field_label'] ) ? sanitize_text_field( $item['field_label'] ) : ''; $item['field_id'] = isset( $item['field_id'] ) ? sanitize_text_field( $item['field_id'] ) : ''; $item['field_sample_text'] = isset( $item['field_sample_text'] ) ? sanitize_text_field( $item['field_sample_text'] ) : ''; $item['field_creator'] = isset( $item['field_creator'] ) ? sanitize_text_field( $item['field_creator'] ) : ''; $item['field_maxchar'] = isset( $item['field_maxchar'] ) ? sanitize_text_field( $item['field_maxchar'] ) : ''; $item['field_uniqueid'] = isset( $item['field_uniqueid'] ) ? sanitize_text_field( $item['field_uniqueid'] ) : ''; $item['app_width_figure'] = isset( $item['app_width_figure'] ) ? sanitize_text_field( $item['app_width_figure'] ) : ''; $item['field_dropdown_options'] = isset( $item['field_dropdown_options'] ) ? sanitize_text_field( $item['field_dropdown_options'] ) : ''; // Validate field_label length (DB column is VARCHAR 300) if ( mb_strlen( $item['field_label'] ) > 300 ) { wp_send_json_error( 'Field label for field #' . ( $idx + 1 ) . ' exceeds 300 characters (currently ' . mb_strlen( $item['field_label'] ) . '). Please shorten it.' ); } // Validate field_sample_text length (DB column is VARCHAR 500) if ( mb_strlen( $item['field_sample_text'] ) > 500 ) { wp_send_json_error( 'Sample text for field #' . ( $idx + 1 ) . ' exceeds 500 characters (currently ' . mb_strlen( $item['field_sample_text'] ) . '). Please shorten it.' ); } } unset( $item ); // break reference // Table name for field selector settings $table_name_settings = $wpdb->prefix . 'ai_app_onsite_field_selector_settings'; $max_field_option = $wpdb->get_var("SELECT MAX(field_position) FROM $table_name_settings WHERE app_id = 1"); // Iterate through form data and insert/update settings foreach ($form_data_array as $index => $formData) { // Check if settings already exist for this field $existing_settings = $wpdb->get_row($wpdb->prepare("SELECT * FROM $table_name_settings WHERE field_uniqueid = %s", $formData['field_uniqueid'])); // echo 'existing_settings = {{'.$formData['field_label'].'}}'; $field_label = $formData['field_label']; // Replace underscores with spaces $field_label = str_replace(' ', '_', $field_label); // Convert the first letter to lowercase $field_label = strtolower($field_label); // Enclose the key in curly braces $field_id = $formData['field_id']; $replaceField_id = str_replace(' ', '-', $field_id); // Convert the first letter to lowercase $formField_id = strtolower($replaceField_id); $field_tag = '{{' . $formField_id . '}}'; if (isset($formData['field_dropdown_options'])) { // Split the string into an array using '|' as a delimiter $field_dropdown_options = explode('|', $formData['field_dropdown_options']); } else { $field_dropdown_options = []; } // Serialize the array to store in the database $field_dropdown_options = serialize($field_dropdown_options); $field_position = isset($formData['field_order']) ? (int) $formData['field_order'] : (int) $index; // If settings already exist, update the row; otherwise, insert a new row if ($existing_settings) { // Prepare data for update $update_data = array( 'app_id' => 1, // Adjust as necessary 'field_uniqueid' => $formData['field_uniqueid'], 'field_id' => $formField_id, 'app_width' => $formData['app_width_figure'], 'field_type' => $formData['field_creator'], 'field_label' => $formData['field_label'], 'field_maxchar' => $formData['field_maxchar'], 'field_dropdown_options' => $field_dropdown_options, 'field_sample_text' => $formData['field_creator'] == 'dropdown' ? '' : $formData['field_sample_text'], 'field_require_field_status' => $formData['section_required'] ? 1 : 0, 'field_include_in_export_email' => $formData['include_in_email'] ? 1 : 0, 'field_position' => $field_position, 'field_tag' => $field_tag, // 'created_at' => current_time('mysql'), 'updated_at' => gmdate("Y-m-d H:i:s") ); if ($existing_settings->field_tag !== $field_tag) { // Update references in prompt editor table (only prompt and prompt_html columns) $this->update_prompt_references('prompt', $existing_settings->field_tag, $field_tag, 1); $this->update_prompt_references('prompt_html', $existing_settings->field_tag, $field_tag, 1); } // Update existing row $wpdb->update( $table_name_settings, $update_data, array('id' => $existing_settings->id), array('%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s', '%s'), array('%d') // where format ); } else { $field_position = isset($formData['field_order']) ? (int) $formData['field_order'] : ((int) $max_field_option + 1); // Prepare data for insertion $create_data = array( 'app_id' => 1, // Adjust as necessary 'field_uniqueid' => $formData['field_uniqueid'], 'field_id' => $formData['field_id'], 'app_width' => $formData['app_width_figure'], 'field_type' => $formData['field_creator'], 'field_label' => $formData['field_label'], 'field_maxchar' => $formData['field_maxchar'], 'field_dropdown_options' => $field_dropdown_options, 'field_sample_text' => $formData['field_creator'] == 'dropdown' ? '' : $formData['field_sample_text'], 'field_require_field_status' => $formData['section_required'] ? 1 : 0, 'field_include_in_export_email' => $formData['include_in_email'] ? 1 : 0, 'field_position' => $field_position, 'field_tag' => $field_tag, 'created_at' => gmdate("Y-m-d H:i:s"), 'updated_at' => gmdate("Y-m-d H:i:s") ); // Insert new row $wpdb->insert( $table_name_settings, $create_data, array('%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s', '%s', '%s') ); } // Check if the database operation was successful if ($wpdb->last_error) { wp_send_json_error('Error savingdf data: ' . $wpdb->last_error); } } // If reached here, data saved successfully aiapp_invalidate_frontend_cache( 'Form fields updated' ); wp_send_json_success('Settings saved successfully.'); } /** * Update references of an old field tag to a new one inside the prompt editor table. * * @param string $column The column name to update ('prompt' or 'prompt_html'). * @param string $current The current field tag value (e.g., '{{old-tag}}'). * @param string $expected The new field tag value (e.g., '{{new-tag}}'). * @param int $app_id The app ID for scoping the update. * @return int|false Number of rows affected, or false on error. */ public function update_prompt_references($column, $current, $expected, $app_id = 1) { global $wpdb; // Ensure the table name is accessible from the class $table_name_prompt_editor = $this->table_name_prompt_editor ?? $wpdb->prefix . 'ai_app_onsite_prompt_editor'; // Validate column name to prevent SQL injection $allowed_columns = ['prompt', 'prompt_html']; if (!in_array($column, $allowed_columns, true)) { return false; // invalid column } // Prepare safe LIKE pattern $like = '%' . $wpdb->esc_like($current) . '%'; // Build and execute the query $sql = $wpdb->prepare( "UPDATE {$table_name_prompt_editor} SET {$column} = REPLACE(COALESCE({$column}, ''), %s, %s) WHERE {$column} LIKE %s AND app_id = %d", $current, $expected, $like, (int) $app_id ); return $wpdb->query($sql); } public function ai_app_onsite_read_field_selector_callback() { global $wpdb; $table_name = $wpdb->prefix . 'ai_app_onsite_field_selector_settings'; $table_name_prompt_editor = $wpdb->prefix . 'ai_app_onsite_prompt_editor'; // Fetch data from the database $results = $wpdb->get_results("SELECT * FROM $table_name WHERE app_id = 1 ORDER BY field_position ASC", ARRAY_A); // HTML content string to store the generated HTML $html_content = ''; // Loop through each record and generate HTML content $ctr = 0; $app_width = $results[0]['app_width'] ?? '70'; foreach ($results as $record) { // Sync the field tag value in the database $field_id = strtolower(str_replace(' ', '-', trim($record['field_id']))); $expected = '{{' . $field_id . '}}'; $current = trim($record['field_tag']); if ($current !== $expected) { $wpdb->update( $table_name, ['field_tag' => $expected], ['id' => (int) $record['id']] ); // 2) Update references in prompt editor table (only prompt and prompt_html columns) $this->update_prompt_references('prompt', $current, $expected, 1); $this->update_prompt_references('prompt_html', $current, $expected, 1); } $html_content .= '
'; $html_content .= ' '; $html_content .= '

'; $html_content .= '

'; if (isset($record['field_type']) && $record['field_type'] == 'dropdown') { $field_dropdown_options = unserialize($record['field_dropdown_options']); $dropdown_options = implode('|', $field_dropdown_options); $html_content .= ''; }; $html_content .= '
'; $html_content .= '
'; $html_content .= '
'; $ctr++; } // --- Orphan tag repair: fix prompt references that got out of sync --- $table_name_prompt_editor = $wpdb->prefix . 'ai_app_onsite_prompt_editor'; if ( ! empty( $results ) ) { $prompt_row = $wpdb->get_row( "SELECT id, prompt, prompt_html FROM $table_name_prompt_editor WHERE app_id = 1 LIMIT 1", ARRAY_A ); if ( $prompt_row && ! empty( $prompt_row['prompt'] ) ) { $valid_tags = []; foreach ( $results as $r ) { $fid = strtolower( str_replace( ' ', '-', trim( $r['field_id'] ) ) ); $valid_tags[] = '{{' . $fid . '}}'; } preg_match_all( '/\{\{([^}]+)\}\}/', $prompt_row['prompt'], $matches ); $prompt_tags = array_unique( $matches[0] ?? [] ); $orphans = array_diff( $prompt_tags, $valid_tags ); $unreferenced = []; foreach ( $valid_tags as $idx => $vtag ) { if ( strpos( $prompt_row['prompt'], $vtag ) === false ) { $unreferenced[$idx] = $vtag; } } if ( ! empty( $orphans ) && ! empty( $unreferenced ) ) { $prompt_text = $prompt_row['prompt']; $prompt_html_text = $prompt_row['prompt_html'] ?? ''; $repaired = false; foreach ( $orphans as $orphan ) { $orphan_inner = trim( str_replace( [ '{{', '}}' ], '', $orphan ) ); $best_match = null; $best_score = 0; foreach ( $unreferenced as $uidx => $utag ) { $utag_inner = trim( str_replace( [ '{{', '}}' ], '', $utag ) ); similar_text( $orphan_inner, $utag_inner, $percent ); if ( $percent > $best_score ) { $best_score = $percent; $best_match = $utag; $best_idx = $uidx; } } if ( $best_match && $best_score >= 40 ) { $prompt_text = str_replace( $orphan, $best_match, $prompt_text ); if ( $prompt_html_text ) { $prompt_html_text = str_replace( $orphan, $best_match, $prompt_html_text ); } unset( $unreferenced[$best_idx] ); $repaired = true; } } if ( $repaired ) { $wpdb->update( $table_name_prompt_editor, [ 'prompt' => $prompt_text, 'prompt_html' => $prompt_html_text ], [ 'id' => (int) $prompt_row['id'] ] ); } } } } // Return the HTML content $arrayName = array('app_width' => $app_width, 'html_content' => $html_content, 'count' => $ctr); wp_send_json($arrayName); exit; } public function ai_app_onsite_delete_field_selector_callback() { global $wpdb; if ( ! isset($_POST['ai_app_onsite_field_selector_nonce_field_delete']) || ! wp_verify_nonce(sanitize_text_field(wp_unslash($_POST['ai_app_onsite_field_selector_nonce_field_delete'])), 'ai_app_onsite_delete_field_nonce') ) { wp_send_json_error('Nonce verification failed.for this'); return false; } // Check if the field_uniqueid parameter is set if (isset($_POST['field_uniqueid'])) { // Sanitize the input to prevent SQL injection $field_uniqueid = isset($_POST['field_uniqueid']) ? sanitize_text_field(wp_unslash($_POST['field_uniqueid'])) : ''; // Table name $table_name = $wpdb->prefix . 'ai_app_onsite_field_selector_settings'; // Delete the record from the database $wpdb->delete($table_name, array('field_uniqueid' => $field_uniqueid), array('%s')); // check error first if ($wpdb->last_error) { wp_send_json_error('Error saving data: ' . $wpdb->last_error); } else { // Return a success message aiapp_invalidate_frontend_cache( 'Form field deleted' ); wp_send_json_success('Record deleted successfully'); } } else { // Return an error message if field_uniqueid parameter is not set wp_send_json_error('Missing field_uniqueid parameter'); } // Make sure no extra content is echoed exit; } public function ai_app_onsite_drag_data_save_database_callback() { if ( ! isset($_POST['ai_app_onsite_fields_selector_order_nonce']) || ! wp_verify_nonce(sanitize_text_field(wp_unslash($_POST['ai_app_onsite_fields_selector_order_nonce'])), 'ai_app_onsite_fields_selector_order_nonce') ) { wp_send_json_error('Nonce verification failed.'); } global $wpdb; $table_name = $wpdb->prefix . 'ai_app_onsite_field_selector_settings'; // Decode the JSON data $form_data_raw = isset($_POST['form_data_array']) ? wp_unslash($_POST['form_data_array']) : ''; $form_data_array = json_decode($form_data_raw, true); // Iterate through the form data array foreach ($form_data_array as $key => $value) { $field_position = $value['fieldOrder']; // Uncomment the update query to actually update the data $wpdb->update( $table_name, // Table name array('field_position' => $field_position), // Data to update array('field_uniqueid' => $value['fieldUniqueId']), // Where clause array('%d'), // Data format (update_data format) array('%s') // Where format ); } // Send a success response wp_send_json_success('Data updated successfully.'); } } new AI_APP_ONSITE_Field_Selector_Handler(); // Instantiate the class }