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

场景：某个报表一个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

评论

1 楼 masuweng 2018-03-28