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 



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 



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

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 



309
310
311
312
 
# File 'lib/toolhound-ruby/base.rb', line 309

def build_and_query(options, query_options = {})
  sql = build_sql(options)
  results = query(sql, query_options)
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 



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

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 



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 



287
288
289
290
291
292
293
294
295
296
 
# File 'lib/toolhound-ruby/base.rb', line 287

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 



269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
 
# File 'lib/toolhound-ruby/base.rb', line 269

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 



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

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 



298
299
300
301
302
303
304
305
306
307
 
# File 'lib/toolhound-ruby/base.rb', line 298

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 



382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
 
# File 'lib/toolhound-ruby/base.rb', line 382

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 



341
342
343
344
345
346
347
348
349
350
351
352
 
# File 'lib/toolhound-ruby/base.rb', line 341

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 



376
377
378
379
380
 
# File 'lib/toolhound-ruby/base.rb', line 376

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

#transform_procedure_value(value) ⇒ Object 



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

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

#transform_procedure_variables(variables) ⇒ Object 



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

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