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 %>

 

演示效果：

定義報表取數邏輯：

 

 

定義報表模板

 

 

定義報表執行

 

 

執行報表列印

 

 

 

 

列印結果: