Class: SqlReporting

Inherits:
Reporting
  • Object
show all
Defined in:
lib/reporting/sql_reporting.rb

Overview

Subclass of Reporting class Offers a variety of helpers to manage reportings that are generated via pure SQL queries

Instance Attribute Summary collapse

Attributes inherited from Reporting

#group_by, #id, #limit, #offset, #order_by, #query, #select, #virtual_columns

Class Method Summary collapse

Instance Method Summary collapse

Methods inherited from Reporting

abstract_class, #add_required_columns, #add_virtual_column, #aggregate, #all_columns, column, column_defaults, #column_label, #columns, columns, columns_hash, connection, #data, #datasource_columns, #defaults, defaults, deserialize, filter, #filterable_by?, from_params, group_by_default, #groupable_columns, #initialize, primary_key, #required_columns, #required_columns_for, #save, #save!, select_default, #serialize, #to_param, #to_params

Constructor Details

This class inherits a constructor from Reporting

Instance Attribute Details

#columns_used ⇒ Object (readonly)

Container for columns used by any (select, group by, where) statement. Used by the joins method to retrieve the joins needed



10
11
12
 
# File 'lib/reporting/sql_reporting.rb', line 10

def columns_used
  @columns_used
end

Class Method Details

.add_sql_columns_for(clazz, options = {}) ⇒ Object 



185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
 
# File 'lib/reporting/sql_reporting.rb', line 185

def add_sql_columns_for(clazz, options = {})
  except = (options[:except] || []).map(&:to_sym)
  model_columns = model_columns(clazz)
  column_names = (options[:columns] || model_columns.keys) # can be used to define column order

  column_names.each do |col_name|
    next if except.include?(col_name)

    type = model_columns[col_name]
    groupable = [:string, :date, :boolean].include?(type)
    use_sql_sum = (type == :number) # SUM() to get correct data with grouping
    send(:column, col_name.to_sym, 
            {
              :type => type, 
              :sql => use_sql_sum ? {:column => "SUM(#{col_name})"} : true,
              :grouping => groupable })
  end
end

.model_columns(clazz) ⇒ Object 



204
205
206
207
208
209
210
211
212
213
214
215
216
217
 
# File 'lib/reporting/sql_reporting.rb', line 204

def model_columns(clazz)
  hsh={}
  clazz.columns_hash.each do |name, col|
    hsh[name.to_sym] = if col.number?
      :number
    elsif col.text?
      :string
    else
      # try to map 1:1
      col.type
    end
  end
  hsh
end

.table(name, options = {}) ⇒ Object

Defines a SQL table that is not the ‘main table’

Options

  • join Defines the SQL JOIN statement to join this table (mandatory)

  • depends Defines a table, this table depends on to join correclty (optional)



178
179
180
181
182
183
 
# File 'lib/reporting/sql_reporting.rb', line 178

def table(name, options = {})
  self.sql_tables ||= HashWithIndifferentAccess.new.freeze
  new_entry = HashWithIndifferentAccess.new({name => options}).freeze
  # frozen, to prevent modifications on class_attribute
  self.sql_tables = self.sql_tables.merge(new_entry).freeze
end

Instance Method Details

#is_sql_column?(column) ⇒ Boolean

Returns true if column is a SQL column

Returns:

  • (Boolean) 


91
92
93
 
# File 'lib/reporting/sql_reporting.rb', line 91

def is_sql_column?(column)
  datasource_columns.has_key?(column.to_s) && datasource_columns[column.to_s][:sql]
end

#map_columns(columns, mapping = {}, with_alias = false) ⇒ Object

TODO make protected?



43
44
45
46
47
48
49
50
51
52
53
54
55
 
# File 'lib/reporting/sql_reporting.rb', line 43

def map_columns(columns, mapping = {}, with_alias = false)
  mapped = []
  columns.each do |column|
    mapped << column if is_sql_column?(column)
  end
  mapped.collect do |column|
    if mapping.has_key?(column)
      with_alias ? "#{mapping[column]} #{column}" : mapping[column]
    else
      sql_column_name(column, :with_alias => with_alias) 
    end
  end
end

#mark_as_used(column) ⇒ Object

Marks a column as used so the connected joins will be included



15
16
17
 
# File 'lib/reporting/sql_reporting.rb', line 15

def mark_as_used(column)
  columns_used << column.to_sym
end

#mark_used_columns ⇒ Object

Marks all columns used in filters or in the select fields as used



166
167
168
 
# File 'lib/reporting/sql_reporting.rb', line 166

def mark_used_columns
  sql_bind_variables.each { |col, value| mark_as_used(col) unless value.nil? }
end

#sql_bind_variables ⇒ Object

Returns all key value pairs that will be bound to the query



142
143
144
145
146
147
148
149
150
151
152
 
# File 'lib/reporting/sql_reporting.rb', line 142

def sql_bind_variables
  bind_vars = {}
  self.class.datasource_filters.each do |column_name, options|
    # just filter sql columns and nil values
    if options[:sql] and not attributes[column_name].nil?
      bind_vars[column_name] = attributes[column_name]
    end
  end
  
  bind_vars.symbolize_keys
end

#sql_column_name(column, options = {}) ⇒ Object

Maps the column name using the :sql definitions of the columns

options

  • :with_alias Returns ‘mapped_name name’ instead of mapped_name



100
101
102
103
104
105
106
107
108
109
110
111
112
113
 
# File 'lib/reporting/sql_reporting.rb', line 100

def sql_column_name(column, options = {})
  column = column.to_s
  return false unless is_sql_column?(column)
  sql = datasource_columns[column][:sql]
  return column.to_s if sql == true

  parts = []
  parts << sql[:table] if sql[:table] && !sql[:column].is_a?(String)
  parts << (sql[:column] || column).to_s
  sql_name = parts.join('.')

  sql_name << " #{column}" if options[:with_alias]
  sql_name
end

#sql_columns ⇒ Object

Returns all datasource columns that correcpond to a SQL column



86
87
88
 
# File 'lib/reporting/sql_reporting.rb', line 86

def sql_columns
  datasource_columns.keys.delete_if { |c| !is_sql_column?(c) }.collect(&:to_sym)
end

#sql_condition_for(column_name, value) ⇒ Object

Returns the SQL condition for the given column, depending on the columns type



117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
 
# File 'lib/reporting/sql_reporting.rb', line 117

def sql_condition_for(column_name, value)
  column_name = column_name.to_s
  # use the sql column name to be sure the table name is included if an sql column is defined
  sql_name = is_sql_column?(column_name) ? sql_column_name(column_name) : column_name
  bind_vars = [ ]
  condition = ''
  type = self.class.datasource_filters[column_name][:type].to_sym
  case type
    when :boolean then
      bind_vars = value
      condition = "(#{sql_name} = ?" + (value == false ? " OR ISNULL(#{sql_name}))" : ')')
    else
      bind_vars = value
      # special handling for zero numbers = allow them to be null too
      if [ :number, :integer ].include?(type) and not value.kind_of?(Array)
        condition = "(#{sql_name} = ? #{(value.to_i.zero? ? "OR ISNULL(#{sql_name})" : "")})"
      else
        condition = value.kind_of?(Array) ? "(#{sql_name} IN(?))" : "(#{sql_name} = ?)"
      end
  end
  self.class.send(:sanitize_sql_array, [ condition, bind_vars ])
end

#sql_conditions ⇒ Object

Concat all where conditions for all bind variables



156
157
158
159
160
161
 
# File 'lib/reporting/sql_reporting.rb', line 156

def sql_conditions
  sql_bind_variables.collect do |column_name, value|
    mark_as_used(column_name)
    sql_condition_for(column_name, value)
  end.compact.join(' AND ')
end

#sql_group_by(*args) ⇒ Object

Returns the columns string for the group by clause def sql_group_by(additional_columns = [], mapping = {})



26
27
28
29
30
31
 
# File 'lib/reporting/sql_reporting.rb', line 26

def sql_group_by(*args)
  mapping = args.extract_options! || {}
  additional_columns = args.first || []
  result = (map_columns(group_by, mapping) << additional_columns).flatten.join(', ')
  result.empty? ? nil : result
end

#sql_joins(*columns) ⇒ Object

Returns the join statements which are needed for the given columns



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
 
# File 'lib/reporting/sql_reporting.rb', line 58

def sql_joins(*columns)
  if columns.empty?
    columns = columns_used + required_columns
    columns += where.keys if self.respond_to?(:where)
  end
  columns.uniq!
  columns.flatten!

  # get all tables needed
  tables = columns.inject([]) do |tables, c|
    sql = datasource_columns[c.to_s][:sql] if is_sql_column?(c)
    tables << sql[:table] if sql && sql.is_a?(Hash)
    tables
  end.compact.uniq

  # explode dependencies
  sql_joins = tables.collect do |table|
    result = [self.class.sql_tables[table]]
    while result.last.has_key?(:depends)
      result << self.class.sql_tables[result.last[:depends]]
    end
    result.reverse
  end.flatten.uniq

  sql_joins.collect { |t| t[:join] }.join(' ')
end

#sql_order_by(mapping = {}) ⇒ Object 



33
34
35
36
37
38
39
40
 
# File 'lib/reporting/sql_reporting.rb', line 33

def sql_order_by(mapping = {})
  return nil if order_by.nil?
  column    = order_by[0]
  direction = order_by[1]
  column    = mapping.has_key?(column) ? mapping[column] : sql_column_name(column)
  return nil unless column
  "#{column} #{direction.to_s.upcase}"
end

#sql_select(additional_columns = [], mapping = {}) ⇒ Object

Returns the columns string for the select clause



20
21
22
 
# File 'lib/reporting/sql_reporting.rb', line 20

def sql_select(additional_columns = [], mapping = {})
  (map_columns(required_columns, mapping, true) << additional_columns).flatten.join(', ')
end