Class: Toolhound::Base

Inherits:
Object
  • Object
show all
Includes:
Util
Defined in:
lib/toolhound-ruby/base.rb

Constant Summary collapse

DB_TYPE_REGEX = 
/^(int|dec|var|bol|dte|bin)/.freeze
DATE_TIME_FORMAT = 
"%Y-%m-%d %H:%M:%S".freeze

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods included from Util

#acronym_regex, #acronyms, #camelize, #demodulize, #underscore

Constructor Details

#initialize(client, options = {}) ⇒ Base

def self.connection @connection ||= Toolhound.connection end



43
44
45
46
47
 
# File 'lib/toolhound-ruby/base.rb', line 43

def initialize(client, options = {})
  @client = client
  # @original = attrs
  # self.attributes = transform_attributes(attrs)
end

Instance Attribute Details

#connection ⇒ Object

Returns the value of attribute connection.



9
10
11
 
# File 'lib/toolhound-ruby/base.rb', line 9

def connection
  @connection
end

Class Method Details

.primary_key ⇒ Object 



15
16
17
 
# File 'lib/toolhound-ruby/base.rb', line 15

def self.primary_key
  @@primary_key[self.name]
end

.primary_key=(primary_key) ⇒ Object 



24
25
26
27
 
# File 'lib/toolhound-ruby/base.rb', line 24

def self.primary_key=(primary_key)
  @@primary_key ||= {}
  @@primary_key[self.name] = primary_key
end

.rename_attributes(hash) ⇒ Object 



30
31
32
33
 
# File 'lib/toolhound-ruby/base.rb', line 30

def self.rename_attributes(hash)
  hash = Hash[hash.map {|key, value| [key.to_s, value] }]
  @@rename_attributes = hash
end

.renamed_attributes ⇒ Object 



35
36
37
 
# File 'lib/toolhound-ruby/base.rb', line 35

def self.renamed_attributes
  @@rename_attributes ||= {}
end

.table_name ⇒ Object 



11
12
13
 
# File 'lib/toolhound-ruby/base.rb', line 11

def self.table_name
  @@table_name[self.name]
end

.table_name=(table_name) ⇒ Object 



19
20
21
22
 
# File 'lib/toolhound-ruby/base.rb', line 19

def self.table_name=(table_name)
  @@table_name ||= {}
  @@table_name[self.name] = table_name
end

Instance Method Details

#_build_joins(joins) ⇒ Object 



201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
 
# File 'lib/toolhound-ruby/base.rb', line 201

def _build_joins(joins)
  join_types = {
    inner: "INNER JOIN",
    left: "LEFT OUTER JOIN",
    left_outer: "LEFT OUTER JOIN",
    left_inner: "LEFT INNER JOIN",
    right: "RIGHT OUTER JOIN",
    right_outer: "RIGHT OUTER JOIN",
    right_inner: "RIGHT INNER JOIN",
  }

  # joins_query = joins.map do |join|
  #   type  = join_types[join[:type] || :inner]
  #   table = formatted_table_name(join[:table])
  #   on    = join[:on]
  #
  #   on_str = on ? "ON #{on}" : ""
  #   "#{type} #{table} #{on_str}"
  # end
  # joins_query.join(" ")
  joins
end

#_build_selects(selects) ⇒ Object 



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
 
# File 'lib/toolhound-ruby/base.rb', line 158

def _build_selects(selects)
  arr = []
  aggs = {
    count: "COUNT",
    max: "MAX",
    min: "MIN",
    average: "AVERAGE"
  }
  selects.each do |table, values|

    values.each do |v|
      if v.is_a? Hash

      end
      # select = "#{formatted_table_name(table)}."
      if v.is_a? Hash
        select = "#{formatted_table_name(table)}.#{formmatted_column_name(v.first[0])}"
        options = v.first[1]
        if options.is_a? Hash
          if options[:agg]
            select = "#{aggs[options[:agg]]}(#{select})"
          end
          if options[:as]
            select += " AS #{formmatted_column_name(options[:as])}"
          end
        else
          select += " AS #{formmatted_column_name(options)}"
        end
        #
      else
        select = "#{formatted_table_name(table)}.#{formmatted_column_name(v)}"
      end
      arr << select
    end
  end
  arr
end

#_build_where(wheres) ⇒ Object 



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
 
# File 'lib/toolhound-ruby/base.rb', line 230

def _build_where(wheres)
  arr = []
  case wheres.class.to_s
  when "String"
    arr << wheres
  when "Hash"
    wheres.each do |k, v|
      table, column = formatted_table_and_column(k)
      op = :eq
      if v.is_a? Hash
        op = v.delete :op
        v  = v.delete :value
      end


      arr << "#{table}.#{column} #{get_operator(op, v)}"
      # if v.is_a? Hash
      #   # key, value = v.first
      #   # op
      #   # arr <<
      #   v.each do |k1, v1|
      #     arr << "#{formatted_table_name(k)}.#{formmatted_column_name(k1)} = '#{v1}'"
      #   end
      # else
      #   arr << "#{formatted_table_name(table_name)}.#{formmatted_column_name(k)} = '#{v}'"
      # end
    end
  when "Array"
    wheres.each do |v|
      arr += _build_where(v)
    end
  end
  arr
end

#all(options = {}) ⇒ Object 



78
79
80
81
 
# File 'lib/toolhound-ruby/base.rb', line 78

def all(options = {})
  options = merge_options({selects: default_selects, joins: default_joins, where: default_wheres}, options)
  build_and_query options
end

#build_and_query(options, query_options = {}) ⇒ Object 



305
306
307
308
 
# File 'lib/toolhound-ruby/base.rb', line 305

def build_and_query(options, query_options = {})
  sql = build_sql(options)
  results = query(sql, query_options)
end

#build_joins(joins) ⇒ Object 



197
198
199
 
# File 'lib/toolhound-ruby/base.rb', line 197

def build_joins(joins)
  _build_joins(joins).join(" ")
end

#build_selects(selects) ⇒ Object 



154
155
156
 
# File 'lib/toolhound-ruby/base.rb', line 154

def build_selects(selects)
  _build_selects(selects).join(", ")
end

#build_sql(obj) ⇒ Object 



311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
 
# File 'lib/toolhound-ruby/base.rb', line 311

def build_sql(obj)
  limit   = obj[:limit]
  selects = obj[:selects] ? build_selects(obj[:selects]) : "*"
  joins   = obj[:joins] ? build_joins(obj[:joins]) : ""
  from    = obj[:from]  ? formatted_table_name(obj[:from]) : table_name
  where   = obj[:where] ? build_where(obj[:where]) : nil
  order   = obj[:order]

  limit_str   = limit ? "TOP(#{limit})" : ""
  where_str   = where.length > 0 ? "WHERE #{where}"    : ""
  order_str   = order ? "ORDER BY #{order}" : ""

  sql = "SELECT #{limit_str} #{selects} FROM #{from} #{joins} #{where_str} #{order_str}"
  puts sql
  sql
end

#build_where(wheres) ⇒ Object 



225
226
227
228
 
# File 'lib/toolhound-ruby/base.rb', line 225

def build_where(wheres)
  arr = _build_where(wheres)
  arr.join(" AND ")
end

#default_joins ⇒ Object 



74
75
76
 
# File 'lib/toolhound-ruby/base.rb', line 74

def default_joins
  []
end

#default_selects ⇒ Object 



71
72
73
 
# File 'lib/toolhound-ruby/base.rb', line 71

def default_selects
  {}
end

#default_wheres ⇒ Object 



67
68
69
 
# File 'lib/toolhound-ruby/base.rb', line 67

def default_wheres
  []
end

#find(id, options = {}) ⇒ Object 



83
84
85
86
87
88
89
90
 
# File 'lib/toolhound-ruby/base.rb', line 83

def find(id, options = {})
  # "tblInventory.bolIsActive = 1 AND tblInventory.bolDeleted = 0 AND tblInventory.intInventoryID = #{id}"
  # wheres = [] + default_wheres
  wheres = default_wheres + [{:"#{primary_key}" => id}]
  options = merge_options({limit: 1, selects: default_selects, joins: default_joins, where: wheres}, options)
  results = build_and_query options
  results.first
end

#formatted_table_and_column(key) ⇒ Object 



283
284
285
286
287
288
289
290
291
292
 
# File 'lib/toolhound-ruby/base.rb', line 283

def formatted_table_and_column(key)
  key = key.to_s
  first, second = key.split(".")
  if first && second
    [formatted_table_name(first), formmatted_column_name(second)]
  elsif(first)
    [table_name, formmatted_column_name(first)]
  end

end

#formatted_table_name(table) ⇒ Object 



141
142
143
144
145
146
147
 
# File 'lib/toolhound-ruby/base.rb', line 141

def formatted_table_name(table)
  table = table.to_s
  unless /^tbl/i =~ table
    table = "tbl#{camelize(table, true)}"
  end
  camelize(table, false)
end

#formmatted_column_name(column) ⇒ Object 



149
150
151
152
 
# File 'lib/toolhound-ruby/base.rb', line 149

def formmatted_column_name(column)
  column = column.to_s
  camelize(column, false)
end

#get_operator(op, value) ⇒ Object 



265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
 
# File 'lib/toolhound-ruby/base.rb', line 265

def get_operator(op, value)
  operators = {eq: "=", gt: ">", gte: ">=", lt: "<", lte: "<=", ne: "!=", in: "IN", nin: "NOT IN", like: "LIKE", between: "BETWEEN"}
  ops = operators.values
  operator = operators[op]
  unless operator
    operator = ops.include?(op) ? op : "="
  end

  if operator == "IN" || operator == "NOT IN"
    value = "(#{value})"
  elsif operator == "BETWEEN"
    value = "'#{value[0]}' AND '#{value[1]}'"
  else
    value = "'#{value}'"
  end
  "#{operator} #{value}"
end

#insert(table, variables) ⇒ Object 



107
108
109
110
 
# File 'lib/toolhound-ruby/base.rb', line 107

def insert(table, variables)


end

#locale ⇒ Object 



63
64
65
 
# File 'lib/toolhound-ruby/base.rb', line 63

def locale
  @locale ||= "EN-US"
end

#merge_options(defaults, options = {}) ⇒ Object 



112
113
114
115
116
117
118
119
120
121
 
# File 'lib/toolhound-ruby/base.rb', line 112

def merge_options(defaults, options = {})
  where   =  options.delete :where
  selects = options.delete :selects
  joins   = options.delete :joins
  defaults[:where]    = (defaults[:where] || []) + (where || [])
  defaults[:selects]  = defaults[:selects].merge(selects || {})
  defaults[:joins]    = defaults[:joins] + (joins || [])
  defaults.merge options

end

#parse_time(date) ⇒ Object 



362
363
364
365
366
 
# File 'lib/toolhound-ruby/base.rb', line 362

def parse_time(date)
  date = date.utc if date.respond_to?(:utc)

  date.strftime(DATE_TIME_FORMAT)
end

#primary_key ⇒ Object 



58
59
60
61
 
# File 'lib/toolhound-ruby/base.rb', line 58

def primary_key
  id = self.class.primary_key || "int#{demodulize(self.class.name)}ID"
  formmatted_column_name(id)
end

#procedure(procedure_name, variables = {}) ⇒ Object 



92
93
94
95
96
97
98
99
100
101
102
103
104
105
 
# File 'lib/toolhound-ruby/base.rb', line 92

def procedure(procedure_name, variables = {})
  # procedure_name = "Job_GetList"

  # EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';
  vars = transform_procedure_variables(variables)
  pairs = vars.map {|pair| pair.join(" = ")  }
  vars_query = pairs.join(", ")

  # EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';

  sql = "EXECUTE dbo.#{procedure_name} #{vars_query};"
  results = connection.execute(sql)
  # results = connection.execute(query)
end

#query(query, options = {}) ⇒ Object 



294
295
296
297
298
299
300
301
302
303
 
# File 'lib/toolhound-ruby/base.rb', line 294

def query(query, options = {})
  data    = []
  results = connection.execute(query)
  results.each(:cache_rows => false) do |row|
    data << transform_attributes(row)
  end
  data
ensure
  finish_statement_handle(results)
end

#table_name ⇒ Object 



54
55
56
57
 
# File 'lib/toolhound-ruby/base.rb', line 54

def table_name
  name = self.class.table_name || demodulize(self.class.name)
  formatted_table_name(name)
end

#transform_attribute_key(key) ⇒ Object 



374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
 
# File 'lib/toolhound-ruby/base.rb', line 374

def transform_attribute_key(key)
  renamed = self.class.renamed_attributes
  if renamed.include? key
    renamed[key].to_sym
  else
  # "varTransferReceiptPrefix"
    if DB_TYPE_REGEX =~ key
      word = key[3..key.length]
      underscore(word).to_sym
    else
      underscore(key).to_sym
    end
  end

end

#transform_attributes(attrs) ⇒ Object 



337
338
339
340
341
342
343
344
345
346
347
348
 
# File 'lib/toolhound-ruby/base.rb', line 337

def transform_attributes(attrs)
  hash = {}
  attrs.each do |k, v|
    key = transform_attribute_key(k)
    if hash.include? key
      hash[:"#{key}1"] = v
    else
      hash[key] = v
    end
  end
  hash
end

#transform_procedure_key(key) ⇒ Object 



368
369
370
371
372
 
# File 'lib/toolhound-ruby/base.rb', line 368

def transform_procedure_key(key)
  key = key.to_s
  key = camelize(key, true)
  "@p_#{key}"
end

#transform_procedure_value(value) ⇒ Object 



358
359
360
 
# File 'lib/toolhound-ruby/base.rb', line 358

def transform_procedure_value(value)
  "'#{value}'"
end

#transform_procedure_variables(variables) ⇒ Object 



350
351
352
353
354
355
356
 
# File 'lib/toolhound-ruby/base.rb', line 350

def transform_procedure_variables(variables)
  vars = []
  variables.each do |key, value|
    vars << [transform_procedure_key(key), transform_procedure_value(value)]
  end
  vars
end