Ruby Ruport实践—报表参数实现

此例子在 Ruby Ruport实践—简单报表系统 及 Ruby Ruport实践—中文PDF报表之PRAWN  的基础上进行完善，添加了对报表参数的设计及实现。

一、创建数据表report_parameters

create table report_parameters
(report_parameter_id integer not null auto_increment,
 report_execute_id integer not null,
 parameter_name varchar(240),
 parameter_value varchar(240),
 primary key "report_parameter_id")
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

二、修改controllers/ReportExecutionsController.rb

将report_parameters的基本操作添加到report_executions界面

class ReportExecutionsController < ApplicationController
  # GET /report_executions
  # GET /report_executions.xml
  Ruport::Formatter::Template.create(:simple) do |format|
        eval（"format.page = {:layout => :landscape}
         format.grouping = {:style => :separated}
         format.text = {:font_size => 20,:justification => :center}
         format.table = {:font_size => 10,:heading_font_size => 10,:maximum_width => 720,:width => 720}
         format.column = {:alignment => :center}
         format.heading = {:alignment => :center,:bold => true,:font_size=>10}")
  end
  def show_report_details
    report_execution = ReportExecution.find_by_execute_code(params[:report_execute_code])
    @report_execute_code = params[:report_execute_code]
    puts "======================显示报表参数======================="
    @report_params = ReportExecution.find_by_sql("SELECT * FROM report_parameters WHERE report_execute_id= #{report_execution.report_execute_id}")
    @report_params.each do |t|
      puts "#{t.parameter_name}: #{t.parameter_value}"
    end
    puts"========================================================"
    render :action => "execute"
  end

  def output_report
    puts "----------------------------Output Report Code: #{params[:execution_code]}"
    report_execution = ReportExecution.find_by_execute_code(params[:execution_code])
    report_definition = ReportDefinition.find(report_execution.report_definition_id)
    report_template = ReportTemplate.find(report_execution.report_template_id)
    puts "=============Output Parameters========================"
    puts "execute_code = #{params[:execution_code]}"
    puts "report_name = #{report_definition.report_name}"
    puts "template_name = #{report_template.template_name}, template_type= #{report_template.template_type}"
    puts "======================================================"
    outputFile = ReportOutputController.render(eval（":"+report_template.template_type.downcase),:sql=>report_definition.report_sql,
                                               :parameters=>params[:parameters],
                                               :outputContent=>report_template.template_content,
                                               :template=>:simple)
    send_data outputFile,:type => "application/"+report_template.template_type.downcase,:filename => "reportExecution."+report_template.template_type.downcase

  end
  def index
    @report_executions = ReportExecution.all
    respond_to do |format|
      format.html # index.html.erb
      format.xml  { render :xml => @report_executions }
    end
  end

  # GET /report_executions/1
  # GET /report_executions/1.xml
  def show
    @report_execution = ReportExecution.find(params[:id])
    @report_parameters = ReportParameter.find_by_sql("select * from report_parameters where report_execute_id = #{params[:id]}")

    respond_to do |format|
      format.html # show.html.erb
      format.xml  { render :xml => @report_execution }
    end
  end

  # GET /report_executions/new
  # GET /report_executions/new.xml
  def new
    @report_execution = ReportExecution.new
    respond_to do |format|
      format.html # new.html.erb
      format.xml  { render :xml => @report_execution }
    end
  end

  # GET /report_executions/1/edit
  def edit
    @report_execution = ReportExecution.find(params[:id])
    report_definition = ReportDefinition.find(@report_execution.report_definition_id)
    report_template = ReportTemplate.find(@report_execution.report_template_id)
    @latest_report_name = report_definition.report_name
    @latest_template_name = report_template.template_name
    @report_parameters = ReportParameter.find_by_sql("select * from report_parameters where report_execute_id = #{params[:id]}")
    if (@report_parameters)
      puts "has parameters #{@report_parameters.length}========================="
      @report_parameters.each do |f|
        puts "#{f.parameter_name}: #{f.parameter_value}"
      end
      puts "has parameters========================="
    end
  end

  def create_report_parameters
    flag=true
     if(params[:parameter_name])
          for i in 0..params[:parameter_name].length-1
            if(params[:parameter_name][i]!="")
             report_parameter = ReportParameter.new
             report_parameter.write_attribute("report_execute_id",@report_execution.report_execute_id)
             report_parameter.write_attribute("parameter_name",params[:parameter_name][i])
             report_parameter.write_attribute("parameter_value",params[:parameter_value][i])

             if !report_parameter.save
               flag=false
             end
            end
          end
     end
    return flag
  end
  # POST /report_executions
  # POST /report_executions.xml
  def create
    puts"---------------------Create"
    @report_execution = ReportExecution.new(params[:report_execution])
    #@report_parameter = ReportParameter.new(params[:report_parameter])
    puts "params[:parameter_name]: #{params[:parameter_name]}"
    puts "params[:parameter_value]: #{params[:parameter_value]}"
    respond_to do |format|
      if @report_execution.save

        if create_report_parameters 
           flash[:notice] = 'ReportExecution was successfully created.'
        end
        format.html { redirect_to(@report_execution) }
        format.xml  { render :xml => @report_execution, :status => :created, :location => @report_execution }
      else
        format.html { render :action => "new" }
        format.xml  { render :xml => @report_execution.errors, :status => :unprocessable_entity }
      end
    end
  end

  # PUT /report_executions/1
  # PUT /report_executions/1.xml
  def update
    @report_execution = ReportExecution.find(params[:id])
    puts "========================="
    puts "Length: #{params[:parameter_id]}"
    respond_to do |format|
      if @report_execution.update_attributes(params[:report_execution])
        flash[:notice] = 'ReportExecution was successfully updated.'

        if(params[:parameter_id])
          for i in 0..params[:parameter_id].length-1
            report_parameter = ReportParameter.find(params[:parameter_id][i])
            report_parameter.update_attribute("parameter_name",params[:parameter_name][i])
            report_parameter.update_attribute("parameter_value",params[:parameter_value][i])
          end
        end

        format.html { redirect_to(@report_execution) }
        format.xml  { head :ok }
      else
        format.html { render :action => "edit" }
        format.xml  { render :xml => @report_execution.errors, :status => :unprocessable_entity }
      end
    end
  end

  # DELETE /report_executions/1
  # DELETE /report_executions/1.xml
  def destroy
    @report_execution = ReportExecution.find(params[:id])
    @report_execution.destroy

    respond_to do |format|
      format.html { redirect_to(report_executions_url) }
      format.xml  { head :ok }
    end
  end
end

三、修改helpers/ReportExecutionsHelper.rb

添加界面下拉列表的应用

module ReportExecutionsHelper
   def report_definitions_control
      report_definitions=ReportDefinition.find_by_sql("select * from report_definitions")
      index=get_selected_definition(report_definitions,@latest_report_name)
      puts "Latest Report Name: #{@latest_report_name}, index = #{index}"
      return  content_tag('select',options_from_collection_for_select(report_definitions, 'report_definition_id', 'report_name',:selected=>index), :id => 'report_definition', :name => 'report_execution[report_definition_id]')
   end
   def report_templates_control
     report_templates=ReportTemplate.find_by_sql("select * from report_templates")
     index=get_selected_template(report_templates,@latest_template_name)
     puts "Latest Template Name: #{@latest_template_name}, index = #{index}"
     return  content_tag('select',options_from_collection_for_select(report_templates, 'report_template_id', 'template_name',:selected=>index), :id => 'report_template', :name => 'report_execution[report_template_id]')
   end
  def report_executions_control
    reportExecutions=ReportExecution.find_by_sql("select * from report_executions")
    return  content_tag('select',options_from_collection_for_select(reportExecutions, 'execute_code', 'execute_name'), :id => 'report_execution', :name => 'report_execute_code')
  end
  def get_selected_definition(report_definitions,latest_report_name)
    index=0
    for i in 0..report_definitions.length-1
      if(report_definitions[i].report_name == latest_report_name)
        index = i+1
        break
      end
    end
    return index
  end
  def get_selected_template(report_templates,latest_template_name)
    index=0
    for i in 0..report_templates.length-1
      if(report_templates[i].template_name == latest_template_name)
        index = i+1
        break
      end
    end
    return index
  end
end

四、修改ReportOutputController.rb

对取数逻辑SQL进行转换

class ReportOutputController< Ruport::Controller
  #Code here
  stage :data_sheet
  def setup
    #self.data = ReportExecution.report_table_by_sql(options[:sql])
    if(options[:parameters])
      puts "参数列表: #{options[:parameters]}"
      options[:sql] = transferSql(options[:sql],options[:parameters])
    end
    puts "执行SQL：#{options[:sql]}"
    self.data = Product.report_table_by_sql(options[:sql])
  end
  def transferSql(str,parameters)
    if(parameters[0]=="")
      parameters[0]="null"
    end
    puts "转换:#{str}, 替换参数#{parameters[0]}"
    if (str.include?"?")
      puts  "===========开始替换================"
      str = str.sub(//?/,parameters[0])
      puts "str (#{parameters[0]}) = #{str}"
      puts "===================================="
      parameters.delete_at(0)
      if (str.include?"?")
         puts "还存在可替换参数"
         transferSql(str,parameters)
      else
         return str
      end
    else
      return str
    end
  end
end
class Ruport::Formatter::PRAWN< Ruport::Formatter::PDF
    def pdf_writer
      unless @pdf_writer
        @pdf_writer = ::Prawn::Document.new
        @pdf_writer.font"#{::Prawn::BASEDIR}/data/fonts/simkai.ttf"
      end
      return @pdf_writer
    end
    def render_pdf
      output<<pdf_writer.render
    end
  end

 class Pdf<Ruport::Formatter::PRAWN
    renders :pdf, :for => ReportOutputController
    build :data_sheet  do
      eval（options[:outputContent])  
    end  
 end

 class ReportHtml < Ruport::Formatter::HTML
    renders :html, :for => ReportOutputController
    build :data_sheet do
      eval（options[:outputContent])      
    end
  end

  class ReportCsv < Ruport::Formatter::CSV
    renders :csv, :for => ReportOutputController
    build :data_sheet do
      eval（options[:outputContent])   
    end
  end

五、修改views/report_executions/index.html.erb

在末尾添加以下代码，实现下拉列表的应用

<h2>Ouput Report</h2>
<%form_tag({:action=>'show_report_details' }) do%>
  报表执行编码:<%=report_executions_control%>
<%= submit_tag "Detail"%>

六、修改views/report_executions/new.html.erb

<h1>New report_execution</h1>

<% form_for(@report_execution) do |f| %>
  <%= f.error_messages %>

  <p>
    <%= f.label :execute_code %>:
    <%= f.text_field :execute_code %>
  </p>
  <p>
    <%= f.label :execute_name %>:
    <%= f.text_field :execute_name %>
  </p>
  <p>
    <%= f.label :report_definition_id %>:
    <%=report_definitions_control%>
  </p>
  <p>
    <%= f.label :report_template_id %>:
    <%=report_templates_control %>
  </p>
  <%for i in 0..5%>
  <p>
    Parameter<%=i%>: <%=text_field_tag 'parameter_name[]'%>
    Value<%=i%>: <%=text_field_tag 'parameter_value[]'%>
  </p>
  <%end%>

 <p>
    <%= f.submit 'Create' %>
 </p>
<% end %>

<%= link_to 'Back', report_executions_path %>

七、修改views/report_executions/edit.html.erb

<h1>Editing report_execution</h1>

<% form_for(@report_execution) do |f| %>
  <%= f.error_messages %>

  <p>
    <%= f.label :execute_code %>:
    <%= f.text_field :execute_code %>
  </p>
  <p>
    <%= f.label :execute_name %>:
    <%= f.text_field :execute_name %>
  </p>
  <p>
    <%= f.label :report_definition_id %>:
    <%=report_definitions_control%>
  </p>
  <p>
    <%= f.label :report_template_id %>:
    <%=report_templates_control%>
  </p>
  <%if @report_parameters.length>0%>
  <h3>Report Parameters</h3><p/>
  <%for i in 0..@report_parameters.length-1%>
  <p>
    <%=hidden_field_tag 'parameter_id[]',@report_parameters[i].report_parameter_id%>
    Parameter<%=i%>: <%=text_field_tag 'parameter_name[]',@report_parameters[i].parameter_name%>
    Value<%=i%>: <%=text_field_tag 'parameter_value[]',@report_parameters[i].parameter_value%>
  </p>
  <%end%>
  <%end%>
  <p>
    <%= f.submit 'Update' %>
  </p>
<% end %>

<%= link_to 'Show', @report_execution %> |
<%= link_to 'Back', report_executions_path %>

八、修改views/report_executions/show.html.erb

<p>
  <b>Execute code:</b>
  <%=h @report_execution.execute_code %>
</p>

<p>
  <b>Execute name:</b>
  <%=h @report_execution.execute_name %>
</p>

<p>
  <b>Report definition:</b>
  <%=h @report_execution.report_definition_id %>
</p>

<p>
  <b>Report template:</b>
  <%=h @report_execution.report_template_id %>
</p>
<br/>
<h3>Parameters</h3>
<%if @report_parameters.length>0%>
  <table table frame=box>
    <tr><td>Parameter Name</td><td>Parameter Value</td></tr>
   <%@report_parameters.each do|report_parameter|%>
     <tr>
        <td width="150"><%=h report_parameter.parameter_name %></td>
        <td width="150"><%=h report_parameter.parameter_value %></td>
     </tr>
   <%end%>
  </table>
<%end%>

<%= link_to 'Edit', edit_report_execution_path(@report_execution) %> |
<%= link_to 'Back', report_executions_path %>

演示效果：

定义报表取数逻辑：

定义报表模板

定义报表执行

执行报表打印

打印结果: