多sql结果集按列合并新结果报表实现方案
程序员文章站
2022-07-12 20:30:36
...
场景:某个报表一个sql出不来,需要多个sql的结果集合并加工
方案要点:按列抽取sql数据,再矩阵转置方式合成目标报表。
方案难点:
1,各sql结果行数不一致时,空列要按空集处理补数据加工。
2,实现跨表字断的表达式计算。
部分代码:
源码关注:
https://github.com/jamst/pre-report
方案要点:按列抽取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
上一篇: 怎么样吃到孩子手中的糖
下一篇: 怎么样吃到孩子手中的糖
推荐阅读