Module: ModelMixins::TableBuilderClassMethods

Defined in:
lib/model_mixins/table_builder_class_methods.rb

Instance Method Summary collapse

Instance Method Details

#check_non_existing_colum_order_by(settings, params) ⇒ Object 



424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
 
# File 'lib/model_mixins/table_builder_class_methods.rb', line 424

def check_non_existing_colum_order_by(settings, params)
  order_by_params = params[:order_by].dup.gsub(/___unknown___\./, "") #some cleaning job
  order_by_params = order_by_params.gsub(/___sql_expression___\./, "") #some cleaning job

  order_by_arr = order_by_params.split(",")
  order_by_arr.each_with_index do |one_order_by, index|
    if one_order_by.match(/^.*?non_existing_column___.*$/i)
      identifier_and_direction = one_order_by.split("non_existing_column___").second
      identifier = identifier_and_direction.split(" ").first
      order_by_direction = identifier_and_direction.split(" ").second
      settings[:columns].each do |col|
        if !col[:select_as].blank? && !col[:format_method].blank? && col[:format_method] == identifier
          order_by_arr[index] = col[:order_by].gsub(",", " #{order_by_direction} ,") + " #{order_by_direction}"
        else
          ""
        end
      end
    else
      ""
    end
  end
  params[:real_order_by] = order_by_arr*","
end

#filter(object, settings, params, per_page = 10, total_count = nil) ⇒ Object 



266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
 
# File 'lib/model_mixins/table_builder_class_methods.rb', line 266

def filter(object, settings, params, per_page = 10, total_count = nil)
  order_by = params[:real_order_by]

  cond_str = ""
  cond_hash = {}
  having_cond_str = ""
  having_cond_hash = {}


  if !params.blank? && params['find']
    params['find'].each_pair do |i, v|
      i = i.gsub(/___unknown___\./, "") #some cleaning job
      unless v.blank?
        if i.match(/^.*?non_existing_column___.*$/i)
          identifier = i.split("non_existing_column___").second
          settings[:columns].each do |col|
            if !col[:select_as].blank? && !col[:format_method].blank? && col[:format_method] == identifier
              cond_str += " AND " unless cond_str.blank?
              cond_str += "( "
              sub_cond = ""
              col[:select].split(",").each do |sub_cond_col|
                sub_cond += " OR " unless sub_cond.blank?
                non_existing_column_i = col[:select_as] + "." + sub_cond_col.gsub(" ", "")
                cond_id = "find_#{non_existing_column_i.gsub(/\./, '_')}"
                sub_cond += "#{non_existing_column_i} LIKE :#{cond_id}" #OR guest_email LIKE :find"
                cond_hash.merge!({cond_id.to_sym => "%#{v}%"})
              end
              cond_str += sub_cond + " )"
            else
              ""
            end
          end
        else
          if i.match(/^.*?___sql_expression___.*$/i)
            i = i.gsub(/___sql_expression___\./, "") #some cleaning job

            having_cond_str += " AND " unless having_cond_str.blank?
            cond_id = "find_#{i.gsub(/\./, '_')}"
            having_cond_str += "#{i} LIKE :#{cond_id}" #OR guest_email LIKE :find"
            having_cond_hash.merge!({cond_id.to_sym => "%#{v}%"})
          else
            cond_str += " AND " unless cond_str.blank?
            cond_id = "find_#{i.gsub(/\./, '_')}"
            cond_str += "#{i} LIKE :#{cond_id}" #OR guest_email LIKE :find"
            cond_hash.merge!({cond_id.to_sym => "%#{v}%"})
          end
        end
      end
    end
  end


  # ToDo ladas add having condition to others
  if !params.blank? && params['multichoice']
    params['multichoice'].each_pair do |i, v|
      i = i.gsub(/___unknown___\./, "") #some cleaning job
      unless v.blank?
        cond_str += " AND " unless cond_str.blank?
        cond_id = "multichoice_#{i.gsub(/\./, '_')}"

        cond_str += "#{i} IN (:#{cond_id})" #OR guest_email LIKE :find"
        cond_hash.merge!({cond_id.to_sym => v})
      end
    end
  end

  if !params.blank? && (params['date_from'] || params['number_from'])
    from_hash = params['date_from']
    if from_hash.blank?
      from_hash = params['number_from']
    else
      from_hash.merge!(params['number_from']) unless params['number_from'].blank?
    end
    from_hash.each_pair do |i, v|
      i = i.gsub(/___unknown___\./, "") #some cleaning job
      unless v.blank?
        if i.match(/^.*?___sql_expression___.*$/i)
          i = i.gsub(/___sql_expression___\./, "") #some cleaning job
          having_cond_str += " AND " unless having_cond_str.blank?
          cond_id = "date_from_#{i.gsub(/\./, '_')}"
          having_cond_str += "#{i} >= :#{cond_id}" #OR guest_email LIKE :find"
          having_cond_hash.merge!({cond_id.to_sym => "#{v}"})
        else
          cond_str += " AND " unless cond_str.blank?
          cond_id = "date_from_#{i.gsub(/\./, '_')}"
          cond_str += "#{i} >= :#{cond_id}" #OR guest_email LIKE :find"
          cond_hash.merge!({cond_id.to_sym => "#{v}"})
        end
      end
    end
  end

  if !params.blank? && (params['date_to'] || params['number_to'])
    to_hash = params['date_to']
    if to_hash.blank?
      to_hash = params['number_to']
    else
      to_hash.merge!(params['number_to']) unless params['number_to'].blank?
    end
    to_hash.each_pair do |i, v|
      i = i.gsub(/___unknown___\./, "") #some cleaning job
      unless v.blank?
        if i.match(/^.*?___sql_expression___.*$/i)
          i = i.gsub(/___sql_expression___\./, "") #some cleaning job
          having_cond_str += " AND " unless having_cond_str.blank?
          cond_id = "date_to_#{i.gsub(/\./, '_')}"
          having_cond_str += "#{i} <= :#{cond_id}" #OR guest_email LIKE :find"
          having_cond_hash.merge!({cond_id.to_sym => "#{v}"})
        else
          cond_str += " AND " unless cond_str.blank?
          cond_id = "date_to_#{i.gsub(/\./, '_')}"
          cond_str += "#{i} <= :#{cond_id}" #OR guest_email LIKE :find"
          cond_hash.merge!({cond_id.to_sym => "#{v}"})
        end
      end
    end
  end

  ret = where(cond_str, cond_hash).order(order_by)
  ret = ret.having(having_cond_str, having_cond_hash) unless having_cond_str.blank?
  #items = self.joins("LEFT OUTER JOIN intranet_text_pages ON resource_id = intranet_text_pages.id").where(cond_str, cond_hash).paginate(:page => params[:page], :per_page => per_page).order(order_by).selection(settings)
  #if params[:page].to_i > items.total_pages && items.total_pages > 0
  #  params[:page] = 1
  #  items = self.where(cond_str, cond_hash).paginate(:page => params[:page], :per_page => per_page).order(order_by).selection(settings)
  #end
  #items

  # if there are additional joins i will add them
  settings[:columns].each do |col|
    col[:table_primary_key] = "id" if col[:table_primary_key].blank?
    if !col[:join_on].blank?
      join_on_select = col[:select] + ", #{col[:table_primary_key]}" # adding primary key so it can be used in on condition
      ret= ret.joins("LEFT OUTER JOIN (SELECT #{join_on_select} FROM #{col[:table]}) #{col[:select_as]} ON #{col[:select_as]}.#{col[:table_primary_key]}=#{col[:join_on]}")
    end
  end

  if per_page && per_page > 0
    # only when i need pagination
    if total_count.blank?
      # if I call this more times, I can pass the total count and not count it multiple times

      # fuck will paginate, if there are agregated queries that I use for condition, will_paginage will delete it
      # i am counting rows on my own (as sugested in will paginete gem, when the query got more complex)
      if object.respond_to?(:klass)
        mysql_count = object.klass.find_by_sql("SELECT COUNT(*) AS count_all FROM (" + ret.selection(settings).to_sql + ") count")
      else
        mysql_count = object.find_by_sql("SELECT COUNT(*) AS count_all FROM (" + ret.selection(settings).to_sql + ") count")
      end
      ret = ret.paginate(:page => params[:page], :per_page => per_page, :total_entries => mysql_count.first[:count_all])
    else
      ret = ret.paginate(:page => params[:page], :per_page => per_page, :total_entries => total_count)
    end
  end

  ret
end

#prepare_settings(logged_user, object, settings, params, per_page = 10) ⇒ Object 



3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
 
# File 'lib/model_mixins/table_builder_class_methods.rb', line 3

def prepare_settings(logged_user, object, settings, params, per_page = 10)
  params[:page] = 1 if params[:page].blank?
  params[:order_by] = settings[:default][:order_by] + " " + settings[:default][:order_by_direction] if params[:order_by].blank? && !settings[:default][:order_by].blank? && !settings[:default][:order_by_direction].blank?

  params[:order_by] = settings[:default][:order] if params[:order_by].blank? && !settings[:default][:order].blank?

  # there are allowed per_pages in the settings[:per_page]
  unless settings[:per_page].blank?
    unless params["per_page_chosen"].blank?
      params[:per_page] = settings[:per_page].include?(params["per_page_chosen"].to_i) ? params["per_page_chosen"].to_i : settings[:per_page].first
    end
  end

  if params[:per_page].blank?
    #default per page from Class variable set in initializer
    params[:per_page] = ModelMixins::TableBuilderClassMethods::PER_PAGE if defined?(ModelMixins::TableBuilderClassMethods::PER_PAGE)

    #or with higher priority from table definition
    params[:per_page] = settings[:default][:per_page] if params[:per_page].blank? && !settings[:default][:per_page].blank?
  end
  params[:per_page] = per_page if params[:per_page].blank?
  per_page = params[:per_page]


  # method below can change this if there were some virtual non exixtent columns
  params[:real_order_by] = params[:order_by]
  check_non_existing_colum_order_by(settings, params)

  not_selected_items = object.filter(object, settings, params, per_page)
  items = not_selected_items.selection(settings)
  if params[:page].to_i > items.total_pages && items.total_pages > 0
    params[:page] = 1
    not_selected_items = object.filter(object, settings, params, per_page)
    items = not_selected_items.selection(settings)
  end

  # the array of items, Will be filled with column method values, formatting values, etc.
  all_items = items.all # maybe can be done more optimal

  if settings[:template].blank?
    # initialize another_global_formats,another_formats and column_methods
    another_global_formats = []
    another_formats = []
    column_methods = []
    settings[:columns].each do |col|
      if !col[:summarize_page].blank? && col[:summarize_page]
        # mysql SUM of the collumn on the page
        # passing all_items.total_entries because I don't want it to count again
        col[:summarize_page_value] = sumarize(object, col, object.filter(object, settings, params, per_page, all_items.total_entries).selection(settings))
      end

      if !col[:summarize_all].blank? && col[:summarize_all]
        # mysql SUM of the collumn off all data
        col[:summarize_all_value] = sumarize(object, col, object.filter(object, settings, params, false).selection(settings))
      end


      unless col[:global_format_method].blank?
        # ToDo dodelat moznost predani parametru do formatovaci metody
        col[:name] = col[:name].blank? ? "non_existing_column___" + col[:global_format_method] : col[:name]
        another_global_format = {:global_format_method => col[:global_format_method],
                                 :name => col[:name],
                                 :table => col[:table]}
        another_global_formats << another_global_format
      end
      unless col[:format_method].blank?
        col[:name] = col[:name].blank? ? "non_existing_column___" + col[:format_method] : col[:name]
        another_format = {:format_method => col[:format_method],
                          :name => col[:name],
                          :table => col[:table]}
        another_formats << another_format
      end
      unless col[:column_method].blank?
        column_methods << {:column_method => col[:column_method],
                           :name => col[:name],
                           :table => col[:table],
                           :column_class => col[:column_class],
                           :column_params => col[:column_params]
        }
      end
    end


    # same as template_items below, loads objects so column method are better to use
    # todo think about, but I dont need object, because it's making the same query twice, I just need class and with one outer join it return filtered data, and i include includes to it
    #template_items = object.joins("RIGHT OUTER JOIN (" + not_selected_items.select(settings[:row][:id] + " AS row_id").to_sql + ") temp_template_query ON #{settings[:row][:id]} = temp_template_query.row_id")
    # the AREL with items
    if object.respond_to?(:klass)
      template_items = object.klass.joins("RIGHT OUTER JOIN (" + items.uniq.to_sql + ") temp_template_query ON #{settings[:row][:id]} = temp_template_query.row_id")
    else
      template_items = object.joins("RIGHT OUTER JOIN (" + items.uniq.to_sql + ") temp_template_query ON #{settings[:row][:id]} = temp_template_query.row_id")
    end

    template_items = template_items.includes(settings[:includes])

    #template_items.all
    # todo dat do knowledge base, kdyz chci aby fungoval include nesmim volat all
    #template_items.each {|t| t.meeting_registrations.each {|x| puts x.inspect}}

    # calling column methods
    another_columns = {}
    unless column_methods.blank?
      column_method_settings = {:params => params}
      column_methods.each do |column_method|
        column_method_settings[:column_params] = column_method[:column_params]
        #all items == array of array items
        #template items == AREL
        if column_method[:column_class].blank?
          if object.respond_to?(:klass)
            another_columns[column_method[:column_method]] = object.klass.send(column_method[:column_method], logged_user, all_items, template_items, column_method_settings)
          else
            another_columns[column_method[:column_method]] = object.send(column_method[:column_method], logged_user, all_items, template_items, column_method_settings)
          end
        else
          column_method[:column_class] = column_method[:column_class].constantize if column_method[:column_class].kind_of?(String)
          another_columns[column_method[:column_method]] = column_method[:column_class].send(column_method[:column_method], logged_user, all_items, template_items, column_method_settings)
        end
      end
    end

    # updating items by another_global_formats,another_formats, column_methods, summary_methods
    if another_global_formats.blank? && another_formats.blank? && column_methods.blank? &&
        items_array = items
    else
      items_array = []
      all_items.each do |i|
        attrs = i.attributes
        another_global_formats.each do |another_global_format|
          # todo udelat moznost predani dalsich parametru
          case another_global_format[:table]
            when "___sql_expression___"
              col_name = "#{another_global_format[:name]}"
            else
              col_name = "#{another_global_format[:table]}_#{another_global_format[:name]}"
          end
          attrs.merge!({col_name => i.send(another_global_format[:global_format_method].to_sym, attrs[col_name])})
        end
        another_formats.each do |another_format|
          case another_format[:table]
            when "___sql_expression___"
              col_name = "#{another_format[:name]}"
            else
              col_name = "#{another_format[:table]}_#{another_format[:name]}"
          end

          attrs.merge!({col_name => i.send(another_format[:format_method].to_sym, attrs[col_name])})
        end
        column_methods.each do |column_method|
          another_column_row = "-"
          another_column_row = another_columns[column_method[:column_method]][attrs['row_id']] if !another_columns.blank? && !another_columns[column_method[:column_method]].blank? && !another_columns[column_method[:column_method]][attrs['row_id']].blank?
          attrs.merge!({"#{column_method[:table]}_#{column_method[:name]}" => another_column_row})
        end

        items_array << attrs
      end
    end

    settings.merge!({:data => items_array})
  else
    #template_items = object.joins("RIGHT OUTER JOIN (" + not_selected_items.uniq.select(settings[:row][:id] + " AS row_id").to_sql + ") temp_template_query ON #{settings[:row][:id]} = temp_template_query.row_id")
    if object.respond_to?(:klass)
      template_items = object.klass.joins("RIGHT OUTER JOIN (" + items.uniq.to_sql + ") temp_template_query ON #{settings[:row][:id]} = temp_template_query.row_id")
    else
      template_items = object.joins("RIGHT OUTER JOIN (" + items.uniq.to_sql + ") temp_template_query ON #{settings[:row][:id]} = temp_template_query.row_id")
    end

    template_items = template_items.includes(settings[:includes])
    settings.merge!({:data => template_items})
  end

  # summary_methods 
  unless settings[:summaries].blank?
    summaries = settings[:summaries]
    summaries.each do |summary|
      summary_settings = {:params => params}
      summary_settings[:summary_params] = summary[:summary_params]

      if summary[:summary_class].blank?
        if object.respond_to?(:klass)
          val = object.klass.send(summary[:summary_method], logged_user, all_items, template_items, object, summary_settings)
        else
          val = object.send(summary[:summary_method], logged_user, all_items, template_items, object, summary_settings)
        end
      else
        summary_class = summary[:summary_class].constantize if summary[:summary_class].kind_of?(String)
        val = summary_class.send(summary[:summary_method], logged_user, all_items, template_items, object, summary_settings)
      end
      if val.kind_of?(Hash)
        summary[:value] = val[:value]
        summary[:class] = val[:class]
      else
        summary[:value] = val
      end
    end

  end


  settings.merge!({:data_paginate => items})
  settings.merge!({:params => params})
  settings
end

#selection(settings) ⇒ Object 



225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
 
# File 'lib/model_mixins/table_builder_class_methods.rb', line 225

def selection(settings)
  select_string = ""
  settings[:columns].each do |col|
    col[:table] = "___unknown___" if col[:table].blank?
    col[:table] = "___sql_expression___" unless col[:sql_expression].blank?

    if col[:column_method].blank? && col[:row_method].blank? && !col[:name].blank?
      if col[:sql_expression].blank?
        # I am selection col[:name]
        select_string += ", " unless select_string.blank?
        select_string += "#{col[:table]}.#{col[:name]} AS #{col[:table]}_#{col[:name]}"
      else
        # Iam selecting col[:expression] and col[:name] is alias
        # the expression can be sql expression
        select_string += ", " unless select_string.blank?
        select_string += "#{col[:sql_expression]} AS #{col[:name]}"
      end
    end

    # for select more data in combination with filter_method (etc full_name of user))
    if !col[:table].blank? && !col[:select].blank? && !col[:select_as].blank?
      col[:select_as] = col[:table] if col[:select_as].blank?

      col[:select].split(",").each do |one_select|
        one_select.gsub!(" ", "")
        select_string += ", " unless select_string.blank?
        select_string += "#{col[:select_as]}.#{one_select} AS #{col[:select_as]}_#{one_select}"
      end
    end

    # for (agregated data)
    #if (!col[:select_agregated].blank?
  end

  select_string += ", " unless select_string.blank?
  select_string += "#{settings[:row][:id]} AS row_id "


  select(select_string)
end

#sumarize(object, col, items) ⇒ Object 



206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
 
# File 'lib/model_mixins/table_builder_class_methods.rb', line 206

def sumarize(object, col, items)
  #method for sumarizing values in column

  if col[:sql_expression].blank?
    col_name = "#{col[:table]}_#{col[:name]}"
  else
    col_name = col[:name]
  end

  if object.respond_to?(:klass)
    mysql_count = object.klass.find_by_sql("SELECT SUM(#{col_name}) AS sum_column FROM (" + items.to_sql + ") count")
  else
    mysql_count = object.find_by_sql("SELECT SUM(#{col_name}) AS sum_column FROM (" + items.to_sql + ") count")
  end

  #count = items.sum(col[:name])
  count = mysql_count.first[:sum_column]
end