欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

多sql结果集按列合并新结果报表实现方案

程序员文章站 2022-07-12 20:30:36
...
场景:某个报表一个sql出不来,需要多个sql的结果集合并加工
方案要点:按列抽取sql数据,再矩阵转置方式合成目标报表。
方案难点:
  1,各sql结果行数不一致时,空列要按空集处理补数据加工。
  2,实现跨表字断的表达式计算。

部分代码:
 # 关联报表子报表
  def association
    @child_columns = []
    @child_reports = []
    @execute_child_reports = []
    @child_temporary_reports = @temporary_report.child_reports
    @child_temporary_reports.each do |child|
      @child_columns << child.columns.split(" ")
      child_reports = ExecuteReport.new(child.get_report_sql(params)).report 
      @execute_child_reports << child_reports
      @child_reports << Kaminari.paginate_array(child_reports, total_count: child_reports.size).page(params[:page]).per(20)
    end
  end

  # 合并报表子报表
  def composite
    # (默认内容最多的sql放在父亲sql,关键列放在左边)
    # 当多sql数据行不一致时,按照合并关键列补充空数据
    unless (@execute_child_reports.inject([]){|o,j| o<<j.size } << @execute_reports.size).uniq.size == 1
      # 查询合并关键列列数
      @composite_columns = @columns & @child_columns.first
      @new_execute_child_reports = ("[] " * @child_columns.size).split(" ").map{|_|eval(_)}
      @execute_reports.each_with_index do |parent_report,i|
        base_columns = parent_report[0..@composite_columns.size-1]
        @execute_child_reports.each_with_index do |child_report,j|
          compare_columns = child_report.map{|child| child[0..@composite_columns.size-1] }
          if compare_columns.include? base_columns
            child_report_index = compare_columns.index(base_columns)
            @new_execute_child_reports[j][i] = child_report.to_a[child_report_index]
          else
            create_child_columns = ("- " * @child_columns[j].size).split(" ") 
            create_child_columns[0..@composite_columns.size-1] = base_columns
            @new_execute_child_reports[j][i] = create_child_columns
          end
        end
      end
      # 重新赋值
      @execute_child_reports =  @new_execute_child_reports
    end

    @moder_columns = []
    @moder = []
    @sentence_expression = []
    arr = ('a'..'z').to_a
    composite_sentence = @temporary_report.composite_sentence.split("|")[0].split(",")
    composite_sentence_expression_str = @temporary_report.composite_sentence.split("|")[1].to_s
    composite_sentence_expression = composite_sentence_expression_str.split(",")
    composite_sentence.each do |_|
      mod = arr.index(_[0])
      moder = (mod == 0 ? @execute_reports : @execute_child_reports[mod-1])
      mod_columns_size = _[1].to_i
      moder_value = moder.map{|_|_[mod_columns_size]}

      # 表达式运算
      if composite_sentence_expression_str.present? && composite_sentence_expression_str.include?(_)
        moder_value_set = moder.map{|_|_[mod_columns_size].to_f}
        instance_variable_set("@#{_}_arr",moder_value_set)
        @sentence_expression << "@#{_}"
      end

      @moder << moder_value
      moder_column = (mod == 0 ? @columns[mod_columns_size] : @child_columns[mod-1][mod_columns_size])
      @moder_columns << moder_column
    end

    # 表达式运算
    composite_sentence_expression.each do |_|
      moder_column = _.split(":").first
      @expression = _.split(":").last
      moder_values = []
      @moder.first.size.times do |i|
        @sentence_expression.each do |ex|
          moder_value_set = instance_variable_get("#{ex}_arr")[i]
          instance_variable_set("#{ex}",moder_value_set)
        end
        moder_values << eval(@expression).to_f.round(2)
      end
      @moder << moder_values
      @moder_columns << moder_column
    end if composite_sentence_expression_str.present?

    # 矩阵行列倒置
    @reports = Matrix.columns(@moder).to_a
    @columns = @moder_columns
    # 合并报表子报表的下载xls
    if params[:xls]
      search_conditions = @temporary_report.search_conditions(params)
      send_data ExecuteReport.to_xlsx(@report_name,@columns,@reports,search_conditions), type: 'text/xls', filename: "#{Time.now}#{@report_name}.xls"
    else
      @reports = Kaminari.paginate_array(@reports, total_count: @reports.size).page(params[:page]).per(20) 
    end
  end


源码关注:
https://github.com/jamst/pre-report