Ruby on Rails实现点击按钮生成指定日期报表表格的方法求助

Hey there! Let's get your report table working smoothly. Your current setup is close, but we need to fix a few key things—like eliminating SQL injection risks, keeping everything on the same page, and ensuring the table only shows when a valid date is selected. Here's a step-by-step solution tailored for your Rails app:

1. Refactor Your Controller (Fix Queries & Centralize Logic) #

First, your original controller uses string concatenation for SQL queries, which is a critical security risk (SQL injection). We'll replace that with safe Rails query methods, and move all logic into the index action so you don't have to jump to a separate test page.

Update controllers/reports_controller.rb:

class ReportsController < ApplicationController
  def index
    @chosen_date = params[:report_date]
    if @chosen_date.present?
      begin
        # Parse the date and create a time range for the full day
        chosen_date = Date.parse(@chosen_date)
        time_range = chosen_date.beginning_of_day..chosen_date.end_of_day

        # Safe, efficient queries using Rails' built-in methods
        @incoming_messages = Message.where(inbound: true, created_at: time_range).count
        @total_chats = Message.where(created_at: time_range).distinct.count(:phone_number)
        @enrollment_by_andi = Enrollment.where(created_at: time_range, created_by: 'ANDI').count
        @enrollment_by_agent = Enrollment.where(created_at: time_range).where.not(created_by: 'ANDI').count
        @sent_by_andi = Message.where(created_by: 'ANDI', created_at: time_range).count
        @sent_by_agents = Message.where(inbound: false, created_at: time_range).where.not(created_by: 'ANDI').count
        @unread_messages = Message.where(created_at: time_range, is_read: false).distinct.count(:phone_number)
      rescue ArgumentError
        # Handle invalid date inputs gracefully
        flash[:alert] = "Oops, that's not a valid date. Please try again."
        @chosen_date = nil
      end
    end
  end
end

2. Update Your View to Show the Table Conditionally #

We'll modify the index.html.erb to only render the table when a valid date is selected, and keep the chosen date in the input field after submission for a better user experience.

Update views/reports/index.html.erb:

<h1>Reports</h1>

<!-- Display error alert if date is invalid -->
<% if flash[:alert].present? %>
  <div style="color: #dc3545; margin: 10px 0; padding: 8px; border: 1px solid #dc3545; border-radius: 4px;">
    <%= flash[:alert] %>
  </div>
<% end %>

<!-- Date selection form -->
<%= form_tag(reports_index_path, method: "post") do %>
  <%= label_tag(:report_date, "Choose a Date", style: "margin-right: 8px;") %>
  <%= date_field_tag(:report_date, @chosen_date, required: true, style: "padding: 4px;") %>
  <%= button_tag "Generate Report", style: "padding: 4px 12px; margin-left: 8px;" %>
<% end %>

<!-- Render report table only when a valid date is chosen -->
<% if @chosen_date.present? %>
  <h2 style="margin-top: 20px;">Report for <%= @chosen_date %></h2>
  <table border="1" cellpadding="8" cellspacing="0" style="margin-top: 10px; border-collapse: collapse;">
    <thead>
      <tr style="background-color: #f8f9fa;">
        <th>Incoming Messages</th>
        <th>Total Chats</th>
        <th>Enrollment By Andi</th>
        <th>Enrollment By Agent</th>
        <th>Sent By Andi</th>
        <th>Sent By Agents</th>
        <th>Unread Messages</th>
      </tr>
    </thead>
    <tbody>
      <tr>
        <td><%= @incoming_messages %></td>
        <td><%= @total_chats %></td>
        <td><%= @enrollment_by_andi %></td>
        <td><%= @enrollment_by_agent %></td>
        <td><%= @sent_by_andi %></td>
        <td><%= @sent_by_agents %></td>
        <td><%= @unread_messages %></td>
      </tr>
    </tbody>
  </table>
<% end %>

3. Adjust Routes to Accept POST Requests to index #

Make sure your routes allow both GET and POST requests for the reports index. Update config/routes.rb:

# Allow both GET and POST to the reports index
match '/reports', to: 'reports#index', via: [:get, :post]

# Alternatively, if using resource routing:
# resources :reports, only: [:index] do
#   collection do
#     post :index
#   end
# end

Key Improvements Breakdown #

• No SQL Injection: Using created_at: time_range instead of string concatenation ensures Rails safely escapes your query, protecting your database.
• Same-Page Experience: All logic stays in the index action, so users don't navigate away after submitting the form.
• Error Handling: Catches invalid date inputs and shows a friendly, styled alert.
• User-Friendly Touches: Retains the chosen date in the input field, adds basic styling to make the table and alerts easier to read.

Now when you select a date and click "Generate Report", the table will appear right below the form with all your calculated metrics!

内容的提问来源于stack exchange，提问作者iamzouz