Ruby Ruport實踐—報表參數實現

此例子在 Ruby Ruport實踐—簡單報表系統 及 Ruby Ruport實踐—中文PDF報表之PRAWN  的基礎上進行完善，添加了對報表參數的設計及實現。

 

一、建立資料表report_parameters

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

 

二、修改controllers/ReportExecutionsController.rb

將report_parameters的基本操作添加到report_executions介面

class ReportExecutionsController < ApplicationController<br /> # GET /report_executions<br /> # GET /report_executions.xml<br /> Ruport::Formatter::Template.create(:simple) do |format|<br /> eval("format.page = {:layout => :landscape}<br /> format.grouping = {:style => :separated}<br /> format.text = {:font_size => 20,:justification => :center}<br /> format.table = {:font_size => 10,:heading_font_size => 10,:maximum_width => 720,:width => 720}<br /> format.column = {:alignment => :center}<br /> format.heading = {:alignment => :center,:bold => true,:font_size=>10}")<br /> end<br /> def show_report_details<br /> report_execution = ReportExecution.find_by_execute_code(params[:report_execute_code])<br /> @report_execute_code = params[:report_execute_code]<br /> puts "======================顯示報表參數======================="<br /> @report_params = ReportExecution.find_by_sql("SELECT * FROM report_parameters WHERE report_execute_id= #{report_execution.report_execute_id}")<br /> @report_params.each do |t|<br /> puts "#{t.parameter_name}: #{t.parameter_value}"<br /> end<br /> puts"========================================================"<br /> render :action => "execute"<br /> end</p><p> def output_report<br /> puts "----------------------------Output Report Code: #{params[:execution_code]}"<br /> report_execution = ReportExecution.find_by_execute_code(params[:execution_code])<br /> report_definition = ReportDefinition.find(report_execution.report_definition_id)<br /> report_template = ReportTemplate.find(report_execution.report_template_id)<br /> puts "=============Output Parameters========================"<br /> puts "execute_code = #{params[:execution_code]}"<br /> puts "report_name = #{report_definition.report_name}"<br /> puts "template_name = #{report_template.template_name}, template_type= #{report_template.template_type}"<br /> puts "======================================================"<br /> outputFile = ReportOutputController.render(eval(":"+report_template.template_type.downcase),:sql=>report_definition.report_sql,<br /> :parameters=>params[:parameters],<br /> :outputContent=>report_template.template_content,<br /> :template=>:simple)<br /> send_data outputFile,:type => "application/"+report_template.template_type.downcase,:filename => "reportExecution."+report_template.template_type.downcase</p><p> end<br /> def index<br /> @report_executions = ReportExecution.all<br /> respond_to do |format|<br /> format.html # index.html.erb<br /> format.xml { render :xml => @report_executions }<br /> end<br /> end</p><p> # GET /report_executions/1<br /> # GET /report_executions/1.xml<br /> def show<br /> @report_execution = ReportExecution.find(params[:id])<br /> @report_parameters = ReportParameter.find_by_sql("select * from report_parameters where report_execute_id = #{params[:id]}")</p><p> respond_to do |format|<br /> format.html # show.html.erb<br /> format.xml { render :xml => @report_execution }<br /> end<br /> end</p><p> # GET /report_executions/new<br /> # GET /report_executions/new.xml<br /> def new<br /> @report_execution = ReportExecution.new<br /> respond_to do |format|<br /> format.html # new.html.erb<br /> format.xml { render :xml => @report_execution }<br /> end<br /> end</p><p> # GET /report_executions/1/edit<br /> def edit<br /> @report_execution = ReportExecution.find(params[:id])<br /> report_definition = ReportDefinition.find(@report_execution.report_definition_id)<br /> report_template = ReportTemplate.find(@report_execution.report_template_id)<br /> @latest_report_name = report_definition.report_name<br /> @latest_template_name = report_template.template_name<br /> @report_parameters = ReportParameter.find_by_sql("select * from report_parameters where report_execute_id = #{params[:id]}")<br /> if (@report_parameters)<br /> puts "has parameters #{@report_parameters.length}========================="<br /> @report_parameters.each do |f|<br /> puts "#{f.parameter_name}: #{f.parameter_value}"<br /> end<br /> puts "has parameters========================="<br /> end<br /> end</p><p> def create_report_parameters<br /> flag=true<br /> if(params[:parameter_name])<br /> for i in 0..params[:parameter_name].length-1<br /> if(params[:parameter_name][i]!="")<br /> report_parameter = ReportParameter.new<br /> report_parameter.write_attribute("report_execute_id",@report_execution.report_execute_id)<br /> report_parameter.write_attribute("parameter_name",params[:parameter_name][i])<br /> report_parameter.write_attribute("parameter_value",params[:parameter_value][i])</p><p> if !report_parameter.save<br /> flag=false<br /> end<br /> end<br /> end<br /> end<br /> return flag<br /> end<br /> # POST /report_executions<br /> # POST /report_executions.xml<br /> def create<br /> puts"---------------------Create"<br /> @report_execution = ReportExecution.new(params[:report_execution])<br /> #@report_parameter = ReportParameter.new(params[:report_parameter])<br /> puts "params[:parameter_name]: #{params[:parameter_name]}"<br /> puts "params[:parameter_value]: #{params[:parameter_value]}"<br /> respond_to do |format|<br /> if @report_execution.save</p><p> if create_report_parameters<br /> flash[:notice] = 'ReportExecution was successfully created.'<br /> end<br /> format.html { redirect_to(@report_execution) }<br /> format.xml { render :xml => @report_execution, :status => :created, :location => @report_execution }<br /> else<br /> format.html { render :action => "new" }<br /> format.xml { render :xml => @report_execution.errors, :status => :unprocessable_entity }<br /> end<br /> end<br /> end</p><p> # PUT /report_executions/1<br /> # PUT /report_executions/1.xml<br /> def update<br /> @report_execution = ReportExecution.find(params[:id])<br /> puts "========================="<br /> puts "Length: #{params[:parameter_id]}"<br /> respond_to do |format|<br /> if @report_execution.update_attributes(params[:report_execution])<br /> flash[:notice] = 'ReportExecution was successfully updated.'</p><p> if(params[:parameter_id])<br /> for i in 0..params[:parameter_id].length-1<br /> report_parameter = ReportParameter.find(params[:parameter_id][i])<br /> report_parameter.update_attribute("parameter_name",params[:parameter_name][i])<br /> report_parameter.update_attribute("parameter_value",params[:parameter_value][i])<br /> end<br /> end</p><p> format.html { redirect_to(@report_execution) }<br /> format.xml { head :ok }<br /> else<br /> format.html { render :action => "edit" }<br /> format.xml { render :xml => @report_execution.errors, :status => :unprocessable_entity }<br /> end<br /> end<br /> end</p><p> # DELETE /report_executions/1<br /> # DELETE /report_executions/1.xml<br /> def destroy<br /> @report_execution = ReportExecution.find(params[:id])<br /> @report_execution.destroy</p><p> respond_to do |format|<br /> format.html { redirect_to(report_executions_url) }<br /> format.xml { head :ok }<br /> end<br /> end<br />end<br />

 

三、修改helpers/ReportExecutionsHelper.rb

添加介面下拉式清單的應用

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

 

四、修改ReportOutputController.rb

對取數邏輯SQL進行轉換

class ReportOutputController< Ruport::Controller<br /> #Code here<br /> stage :data_sheet<br /> def setup<br /> #self.data = ReportExecution.report_table_by_sql(options[:sql])<br /> if(options[:parameters])<br /> puts "參數列表: #{options[:parameters]}"<br /> options[:sql] = transferSql(options[:sql],options[:parameters])<br /> end<br /> puts "執行SQL：#{options[:sql]}"<br /> self.data = Product.report_table_by_sql(options[:sql])<br /> end<br /> def transferSql(str,parameters)<br /> if(parameters[0]=="")<br /> parameters[0]="null"<br /> end<br /> puts "轉換:#{str}, 替換參數#{parameters[0]}"<br /> if (str.include?"?")<br /> puts "===========開始替換================"<br /> str = str.sub(//?/,parameters[0])<br /> puts "str (#{parameters[0]}) = #{str}"<br /> puts "===================================="<br /> parameters.delete_at(0)<br /> if (str.include?"?")<br /> puts "還存在可替換參數"<br /> transferSql(str,parameters)<br /> else<br /> return str<br /> end<br /> else<br /> return str<br /> end<br /> end<br />end<br />class Ruport::Formatter::PRAWN< Ruport::Formatter::PDF<br /> def pdf_writer<br /> unless @pdf_writer<br /> @pdf_writer = ::Prawn::Document.new<br /> @pdf_writer.font"#{::Prawn::BASEDIR}/data/fonts/simkai.ttf"<br /> end<br /> return @pdf_writer<br /> end<br /> def render_pdf<br /> output<<pdf_writer.render<br /> end<br /> end</p><p> class Pdf<Ruport::Formatter::PRAWN<br /> renders :pdf, :for => ReportOutputController<br /> build :data_sheet do<br /> eval(options[:outputContent])<br /> end<br /> end</p><p> class ReportHtml < Ruport::Formatter::HTML<br /> renders :html, :for => ReportOutputController<br /> build :data_sheet do<br /> eval(options[:outputContent])<br /> end<br /> end</p><p> class ReportCsv < Ruport::Formatter::CSV<br /> renders :csv, :for => ReportOutputController<br /> build :data_sheet do<br /> eval(options[:outputContent])<br /> end<br /> end

 

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

在末尾添加以下代碼，實現下拉式清單的應用

<h2>Ouput Report</h2><br /><%form_tag({:action=>'show_report_details' }) do%><br /> 報表執行編碼:<%=report_executions_control%><br /><%= submit_tag "Detail"%>

 

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

<h1>New report_execution</h1></p><p><% form_for(@report_execution) do |f| %><br /> <%= f.error_messages %></p><p> <p><br /> <%= f.label :execute_code %>:<br /> <%= f.text_field :execute_code %><br /> </p><br /> <p><br /> <%= f.label :execute_name %>:<br /> <%= f.text_field :execute_name %><br /> </p><br /> <p><br /> <%= f.label :report_definition_id %>:<br /> <%=report_definitions_control%><br /> </p><br /> <p><br /> <%= f.label :report_template_id %>:<br /> <%=report_templates_control %><br /> </p><br /> <%for i in 0..5%><br /> <p><br /> Parameter<%=i%>: <%=text_field_tag 'parameter_name[]'%><br /> Value<%=i%>: <%=text_field_tag 'parameter_value[]'%><br /> </p><br /> <%end%></p><p> <p><br /> <%= f.submit 'Create' %><br /> </p><br /><% end %></p><p><%= link_to 'Back', report_executions_path %>

 

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

<h1>Editing report_execution</h1></p><p><% form_for(@report_execution) do |f| %><br /> <%= f.error_messages %></p><p> <p><br /> <%= f.label :execute_code %>:<br /> <%= f.text_field :execute_code %><br /> </p><br /> <p><br /> <%= f.label :execute_name %>:<br /> <%= f.text_field :execute_name %><br /> </p><br /> <p><br /> <%= f.label :report_definition_id %>:<br /> <%=report_definitions_control%><br /> </p><br /> <p><br /> <%= f.label :report_template_id %>:<br /> <%=report_templates_control%><br /> </p><br /> <%if @report_parameters.length>0%><br /> <h3>Report Parameters</h3><p/><br /> <%for i in 0..@report_parameters.length-1%><br /> <p><br /> <%=hidden_field_tag 'parameter_id[]',@report_parameters[i].report_parameter_id%><br /> Parameter<%=i%>: <%=text_field_tag 'parameter_name[]',@report_parameters[i].parameter_name%><br /> Value<%=i%>: <%=text_field_tag 'parameter_value[]',@report_parameters[i].parameter_value%><br /> </p><br /> <%end%><br /> <%end%><br /> <p><br /> <%= f.submit 'Update' %><br /> </p><br /><% end %></p><p><%= link_to 'Show', @report_execution %> |<br /><%= link_to 'Back', report_executions_path %>

 

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

<p><br /> <b>Execute code:</b><br /> <%=h @report_execution.execute_code %><br /></p></p><p><p><br /> <b>Execute name:</b><br /> <%=h @report_execution.execute_name %><br /></p></p><p><p><br /> <b>Report definition:</b><br /> <%=h @report_execution.report_definition_id %><br /></p></p><p><p><br /> <b>Report template:</b><br /> <%=h @report_execution.report_template_id %><br /></p><br /><br/><br /><h3>Parameters</h3><br /><%if @report_parameters.length>0%><br /> <table table frame=box><br /> <tr><td>Parameter Name</td><td>Parameter Value</td></tr><br /> <%@report_parameters.each do|report_parameter|%><br /> <tr><br /> <td width="150"><%=h report_parameter.parameter_name %></td><br /> <td width="150"><%=h report_parameter.parameter_value %></td><br /> </tr><br /> <%end%><br /> </table><br /><%end%></p><p><%= link_to 'Edit', edit_report_execution_path(@report_execution) %> |<br /><%= link_to 'Back', report_executions_path %>

 

示範效果：

定義報表取數邏輯：

 

 

定義報表範本

 

 

定義報表執行

 

 

執行報表列印

 

 

 

 

列印結果: