class Toolhound::Base

Constants

DATE_TIME_FORMAT
DB_TYPE_REGEX

Attributes

client[RW]
connection[RW]

Public Class Methods

new(client, options = {}) click to toggle source

def self.connection

@connection ||= Toolhound.connection

end

# File lib/toolhound-ruby/base.rb, line 43
def initialize(client, options = {})
  @client = client
  # @original = attrs
  # self.attributes = transform_attributes(attrs)
end
primary_key() click to toggle source
# File lib/toolhound-ruby/base.rb, line 15
def self.primary_key
  @@primary_key[self.name]
end
primary_key=(primary_key) click to toggle source
# 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) click to toggle source
# 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() click to toggle source
# File lib/toolhound-ruby/base.rb, line 35
def self.renamed_attributes
  @@rename_attributes ||= {}
end
table_name() click to toggle source
# File lib/toolhound-ruby/base.rb, line 11
def self.table_name
  @@table_name[self.name]
end
table_name=(table_name) click to toggle source
# File lib/toolhound-ruby/base.rb, line 19
def self.table_name=(table_name)
  @@table_name ||= {}
  @@table_name[self.name] = table_name
end

Public Instance Methods

_build_joins(joins) click to toggle source
# 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) click to toggle source
# 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) click to toggle source
# 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 = {}) click to toggle source
# 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 = {}) click to toggle source
# File lib/toolhound-ruby/base.rb, line 344
def build_and_query(options, query_options = {})
  sql = build_sql(options)
  results = query(sql, query_options)
end
build_group(groups) click to toggle source
# File lib/toolhound-ruby/base.rb, line 234
def build_group(groups)
  groups
end
build_joins(joins) click to toggle source
# File lib/toolhound-ruby/base.rb, line 201
def build_joins(joins)
  _build_joins(joins).join(" ")
end
build_selects(selects) click to toggle source
# File lib/toolhound-ruby/base.rb, line 154
def build_selects(selects)
  _build_selects(selects).join(", ")
end
build_sql(obj) click to toggle source
# File lib/toolhound-ruby/base.rb, line 413
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) click to toggle source
# File lib/toolhound-ruby/base.rb, line 379
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} = '#{value}'"
        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 = {}) click to toggle source
# File lib/toolhound-ruby/base.rb, line 360
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) click to toggle source
# File lib/toolhound-ruby/base.rb, line 229
def build_where(wheres)
  arr = _build_where(wheres)
  arr.join(" AND ")
end
default_joins() click to toggle source
# File lib/toolhound-ruby/base.rb, line 74
def default_joins
  []
end
default_selects() click to toggle source
# File lib/toolhound-ruby/base.rb, line 71
def default_selects
  {}
end
default_wheres() click to toggle source
# File lib/toolhound-ruby/base.rb, line 67
def default_wheres
  []
end
find(id, options = {}) click to toggle source
# 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) click to toggle source
# 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) click to toggle source

def find(id)

# table_includes = ""
results = query "SELECT TOP(1) * FROM #{table_name} #{table_includes} WHERE #{primary_key} = #{id}"
results.first

end

def all(options = {})

results = query "SELECT * FROM #{table_name}"

end

# 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) click to toggle source
# 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) click to toggle source
# 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) click to toggle source
# File lib/toolhound-ruby/base.rb, line 107
def insert(table, variables)


end
locale() click to toggle source
# File lib/toolhound-ruby/base.rb, line 63
def locale
  @locale ||= "EN-US"
end
merge_options(defaults, options = {}) click to toggle source
# 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) click to toggle source
# File lib/toolhound-ruby/base.rb, line 468
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() click to toggle source
# 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 = {}) click to toggle source
# 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 = {}) click to toggle source
# File lib/toolhound-ruby/base.rb, line 302
def query(query, options = {})
  data    = []
  tries ||= 3
  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 => e
    if e.message =~ /^DBPROCESS/
      client.reset_connection
      retry unless (tries -= 1).zero?
    else
      # throw the error again
      raise
    end

  end
ensure
  finish_statement_handle(results)
end
table_name() click to toggle source
# 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) click to toggle source
# File lib/toolhound-ruby/base.rb, line 484
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) click to toggle source

def attributes=(hash)

@attributes = hash

end

def attributes

@attributes

end

# File lib/toolhound-ruby/base.rb, line 443
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) click to toggle source
# File lib/toolhound-ruby/base.rb, line 478
def transform_procedure_key(key)
  key = key.to_s
  key = camelize(key, true)
  "@p_#{key}"
end
transform_procedure_value(value) click to toggle source
# File lib/toolhound-ruby/base.rb, line 464
def transform_procedure_value(value)
  "'#{value}'"
end
transform_procedure_variables(variables) click to toggle source
# File lib/toolhound-ruby/base.rb, line 456
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 = {}) click to toggle source
# File lib/toolhound-ruby/base.rb, line 349
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) click to toggle source
# File lib/toolhound-ruby/base.rb, line 326
def update_query(query, options)
  tries ||= 3
  begin
    result = connection.execute(query)
    result.do
  rescue TinyTds::Error => e
    if e.message =~ /^DBPROCESS/
      client.reset_connection
      retry unless (tries -= 1).zero?
    else
      # throw the error again
      raise
    end
  end
ensure
  finish_statement_handle(result)
end

Protected Instance Methods

finish_statement_handle(handle) click to toggle source
# File lib/toolhound-ruby/base.rb, line 501
def finish_statement_handle(handle)
  handle.cancel if handle

  handle
end