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

#client ⇒ Object

Returns the value of attribute client.



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

def client
  @client
end

#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 



205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
 
# File 'lib/toolhound-ruby/base.rb', line 205

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
195
196
197
198
 
# 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[:raw]
            select = v.first[0]
          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 



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
265
266
267
268
269
270
271
 
# File 'lib/toolhound-ruby/base.rb', line 238

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 



332
333
334
335
 
# File 'lib/toolhound-ruby/base.rb', line 332

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

#build_group(groups) ⇒ Object 



234
235
236
 
# File 'lib/toolhound-ruby/base.rb', line 234

def build_group(groups)
  groups
end

#build_joins(joins) ⇒ Object 



201
202
203
 
# File 'lib/toolhound-ruby/base.rb', line 201

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 



401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
 
# File 'lib/toolhound-ruby/base.rb', line 401

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]
  group   = obj[:group] ? build_group(obj[:group]) : nil

  selects = "*" if selects.strip.length == 0

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

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

#build_update_attributes(attributes) ⇒ Object 



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

def build_update_attributes(attributes)
  arr = []
  case attributes.class.to_s
  when "String"
    arr << attributes
  when "Hash"
    attributes.each do |k, v|
      name = formmatted_column_name(k)

      if v.is_a? Hash
        allow_null  = v.delete :null
        value       = v.delete :value
        if value.nil? && allow_null == true
          arr << "#{name} = NULL"
        else
          arr << "#{name} = '#{v}'"
        end

      else
        arr << "#{name} = '#{v}'"
      end


    end
  when "Array"
    attributes.each do |v|
      arr += build_update_attributes(v)
    end
  end
  arr

end

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



348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
 
# File 'lib/toolhound-ruby/base.rb', line 348

def build_update_sql(options, query_options = {})
  table    = options[:table]  ? formatted_table_name(options[:table]) : table_name
  attributes  = build_update_attributes(options[:attributes])
  sql         = nil
  where       = build_where(options[:where])
  if attributes.length > 0
    attributes  = attributes.join(", ")

    sql = "UPDATE #{table} SET #{attributes} WHERE #{where}"
    puts sql
  end

  sql
#   update tblCountFinal set
		# 	intQuantity = isnull(intQuantity,0) + isnull(@p_Quantity,0),
		# 	intQOH = isnull(intQOH,0) + isnull(@p_QuantityOnHand,0)
		# where intCountFinalID = @CountFinalID
end

#build_where(wheres) ⇒ Object 



229
230
231
232
 
# File 'lib/toolhound-ruby/base.rb', line 229

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 



291
292
293
294
295
296
297
298
299
300
 
# File 'lib/toolhound-ruby/base.rb', line 291

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 



273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
 
# File 'lib/toolhound-ruby/base.rb', line 273

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 



456
457
458
459
460
461
462
463
464
 
# File 'lib/toolhound-ruby/base.rb', line 456

def parse_time(date)
  if date.is_a?(String)
    date = Time.parse(date)
  end

  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 



302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
 
# File 'lib/toolhound-ruby/base.rb', line 302

def query(query, options = {})
  data    = []
  begin
    results = connection.execute(query)
    opts = {cache_rows: false}.merge(options || {})

    results.each(opts) do |row|
      data << transform_attributes(row)
    end
    data
  rescue TinyTds::Error
    client.reset_connection
    retry
  end
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 



472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
 
# File 'lib/toolhound-ruby/base.rb', line 472

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 



431
432
433
434
435
436
437
438
439
440
441
442
 
# File 'lib/toolhound-ruby/base.rb', line 431

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 



466
467
468
469
470
 
# File 'lib/toolhound-ruby/base.rb', line 466

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

#transform_procedure_value(value) ⇒ Object 



452
453
454
 
# File 'lib/toolhound-ruby/base.rb', line 452

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

#transform_procedure_variables(variables) ⇒ Object 



444
445
446
447
448
449
450
 
# File 'lib/toolhound-ruby/base.rb', line 444

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

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



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

def update(options, query_options = {})
  sql = build_update_sql(options, query_options)

  debug = query_options[:debug]

  if sql && debug != true
    update_query(sql, query_options)
  end

end

#update_query(query, options) ⇒ Object 



320
321
322
323
324
325
326
327
328
329
330
 
# File 'lib/toolhound-ruby/base.rb', line 320

def update_query(query, options)
  begin
    result = connection.execute(query)
    result.do
  rescue TinyTds::Error
    client.reset_connection
    retry
  end
ensure
  finish_statement_handle(result)
end