class Toolhound::InventoryItem

Class to parse GitHub repository owner and name from URLs and to generate URLs

Public Instance Methods

charges(options = {}) click to toggle source
# File lib/toolhound-ruby/inventory_item.rb, line 109
def charges(options = {})
  options = (options || {}).dup

  job_id      = options[:job_id]
  entity_id   = options[:entity_id]
  order       = options[:order] || "tblInventoryText.varPartNo"
  # 249

  joins                 = default_joins
  entity_query          = ""
  charge_entity_query   = ""
  charge_entity_query1  = ""
  if entity_id
    entity_query          = "AND tblRental.intEntityID = '#{entity_id}'"
    charge_entity_query   = "WHERE rc.intEntityID = '#{entity_id}'"
    charge_entity_query1  = "WHERE intEntityID = '#{entity_id}'"
  end

  joins << "INNER JOIN tblInventoryType ON tblInventoryType.intInventoryTypeID = tblInventoryItem.intInventoryTypeID AND tblInventoryType.bolSerialized = 1"
  joins << "INNER JOIN(
                SELECT tblRentalItem.intInventoryIDID, SUM(tblRentalItem.decTotalRent) AS decTotalRent
              FROM tblRentalItem
              INNER JOIN tblRentalDetail ON tblRentalDetail.intRentalDetailID = tblRentalItem.intRentalDetailID
              INNER JOIN tblRental ON tblRental.intRentalID = tblRentalDetail.intRentalID #{entity_query}
              GROUP BY tblRentalItem.intInventoryIDID
   ) AS tblRentalQuery ON tblRentalQuery.intInventoryIDID = tblInventoryID.intInventoryIdID"

  joins << "LEFT OUTER JOIN (
              SELECT rc.intInventoryIDID, MAX(rc.intRentalChargeID) AS latest_id
              FROM tblRentalCharge as rc
              #{charge_entity_query}
              GROUP BY rc.intInventoryIDID
    ) AS tblLatestCharge ON tblLatestCharge.intInventoryIDID = tblInventoryID.intInventoryIdID"

  joins << "LEFT OUTER JOIN  (
              SELECT intJobID, intRentalChargeID, intRentalItemID
              FROM tblRentalCharge
              #{charge_entity_query1}
            ) AS tblJobCharge ON tblJobCharge.intRentalChargeID = tblLatestCharge.latest_id"
  joins << "LEFT OUTER JOIN tblRentalItem ON tblRentalItem.intRentalItemID = tblJobCharge.intRentalItemID"
  joins << "LEFT OUTER JOIN tblRentalDetail ON tblRentalDetail.intRentalDetailID = tblRentalItem.intRentalDetailID"
  joins << "LEFT OUTER JOIN tblRental ON tblRental.intRentalID = tblRentalDetail.intRentalID"
  joins << "LEFT OUTER JOIN tblTransaction ON tblTransaction.intRentalID = tblRental.intRentalID"

  joins << "LEFT OUTER JOIN tblJobText on tblJobText.intJobID = tblJobCharge.intJobID AND tblJobText.varLocaleID = '#{locale}'"

  selects                 = default_selects
  selects[:transaction]   = [{var_work_order: :var_work_order_no}, :var_transaction_no]
  selects[:job_text]      = [:int_job_id, :int_job_text_id, :var_job_number, :var_job]
  selects[:rental_query]  = [:dec_total_rent]

  wheres = []
  if job_id
    if job_id == :null
      wheres << "(tblJobText.intJobID IS NULL)"
    else
      wheres << "(tblJobText.intJobID = #{job_id})"
    end
  end

  build_and_query(selects: selects, where: wheres, joins: joins, order: order)

end
count(options = {}) click to toggle source
# File lib/toolhound-ruby/inventory_item.rb, line 69
def count(options = {})

  entity_id = options[:entity_id]

  selects = {
    job_text: [:int_job_id],
    inventory_item: [{ int_inventory_item_id: {as: :tool_count, agg: :count} }] #[{"count(*)" => {raw: true, as: :tools_assigned}}]
  }
  joins = []
  joins << "INNER JOIN tblInventory ON (tblInventory.intInventoryID = tblInventoryItem.intInventoryID AND tblInventory.bolDeleted = 0)"
  joins << "INNER JOIN tblInventoryID ON tblInventoryID.intInventoryItemID = tblInventoryItem.intInventoryItemID"
  joins << "INNER JOIN tblInventoryType ON tblInventoryType.intInventoryTypeID = tblInventoryItem.intInventoryTypeID AND tblInventoryType.bolSerialized = 1"
  joins << "INNER JOIN(
    SELECT tblRentalItem.intInventoryIDID FROM tblRentalItem
    INNER JOIN tblRentalDetail ON tblRentalDetail.intRentalDetailID = tblRentalItem.intRentalDetailID
    INNER JOIN tblRental ON tblRental.intRentalID = tblRentalDetail.intRentalID AND tblRental.intEntityID = '#{entity_id}'
    GROUP BY tblRentalItem.intInventoryIDID
    ) AS tblRentalQuery ON tblRentalQuery.intInventoryIDID = tblInventoryID.intInventoryIdID"

  joins << "LEFT OUTER JOIN (
                    SELECT rc.intInventoryIDID, MAX(rc.intRentalChargeID) AS latest_id
                    FROM tblRentalCharge as rc
                    WHERE rc.intEntityID = '#{entity_id}'
                    GROUP BY rc.intInventoryIDID
          ) AS tblLatestCharge ON tblLatestCharge.intInventoryIDID = tblInventoryID.intInventoryIdID"

  joins << "LEFT OUTER JOIN  (
              SELECT intJobID, intRentalChargeID, intRentalItemID
              FROM tblRentalCharge
              WHERE intEntityID = '#{entity_id}'
            ) AS tblJobCharge ON tblJobCharge.intRentalChargeID = tblLatestCharge.latest_id"

  joins << "LEFT OUTER JOIN tblJobText on tblJobText.intJobID = tblJobCharge.intJobID AND tblJobText.varLocaleID = '#{locale}'"

  build_and_query(selects: selects, joins: joins, group: "tblJobText.intJobID")

  # GROUP BY tblJobText.intJobID

end
default_joins() click to toggle source
# File lib/toolhound-ruby/inventory_item.rb, line 51
def default_joins
  arr = []
  arr << "INNER JOIN tblInventory ON (tblInventory.intInventoryID = tblInventoryItem.intInventoryID AND tblInventory.bolDeleted = 0)"
  arr << "INNER JOIN tblInventoryText       ON (tblInventoryText.intInventoryID = tblInventory.intInventoryID and tblInventoryText.varLocaleID ='#{locale}')"
  arr << "INNER JOIN tblInventoryItemText ON (tblInventoryItem.intInventoryItemID = tblInventoryItemText.intInventoryItemID and tblInventoryItemText.varLocaleID = '#{locale}')"
  arr << "INNER JOIN tblInventoryID ON tblInventoryID.intInventoryItemID = tblInventoryItem.intInventoryItemID"

  arr
end
default_selects() click to toggle source

def default_selects

selects = {
  inventory: ["intInventoryID", "intCategoryID", "intSubCategoryID", "intManufacturerID", "intVendorID", "dteCreatedDate", "dteModifiedDate"],
  inventory_text: ["varPartNo", "varDescription", "txtNotes", {varUserField1: "varGlRevenue"}, {varUserField2: "varGlCOGSCode"}, {varUserField3: "varPhaseCode"}],
  unit_of_measure_text: ["varUnitOfMeasure"],
  category: ["varCategory"],
  sub_category: ["varCategory"]
}

end def default_joins

arr = []
arr << "INNER JOIN tblInventoryType ON tblInventory.intInventoryTypeID = tblInventoryType.intInventoryTypeID"
arr << "INNER JOIN tblInventoryText ON (tblInventoryText.intInventoryID = tblInventory.intInventoryID AND tblInventoryText.varLocaleID = '#{locale}')"
arr << "LEFT OUTER JOIN tblUnitOfMeasureText ON (tblUnitOfMeasureText.intUofMID = tblInventory.intUofMID )"
arr << "LEFT OUTER JOIN tblCategoryText AS tblCategory ON (tblCategory.intCategoryID = tblInventory.intCategoryID AND tblCategory.varLocaleID = '#{locale}')"
arr << "LEFT OUTER JOIN tblCategoryText AS tblSubCategory ON (tblSubCategory.intCategoryID = tblInventory.intSubCategoryID AND tblSubCategory.varLocaleID = '#{locale}')"

end

# File lib/toolhound-ruby/inventory_item.rb, line 32
def default_selects
  # decCost = purchase amount
  {
    inventory_item:       [
      :int_inventory_item_id, "intQOH", :int_inventory_id, :int_inventory_type_id, :dec_cost, :dte_created_date, :dte_modified_date,
      {int_curr_location_id: :entity_id}
    ],
    inventory_item_text:  [:var_serial_number, :var_user_field1, :var_user_field2, :var_user_field3, :var_bin],
    inventory:            [:int_category_id, :int_sub_category_id],
    inventory_text:       [:var_description, :var_part_no, {varUserField1: "varGlRevenue"}, {varUserField2: "varGlCOGSCode"}, {varUserField3: "varPhaseCode"}],
    inventory_id:         ["intInventoryIdID", {varInventoryID: :varInventoryIdNo}]
  }

end
default_wheres() click to toggle source
# File lib/toolhound-ruby/inventory_item.rb, line 47
def default_wheres
  [{bolActive: 1 }, {bolDeleted: 0}]
end
for_entity(entity_id) click to toggle source
# File lib/toolhound-ruby/inventory_item.rb, line 61
def for_entity(entity_id)
  all(where: [{int_curr_location_id: entity_id}])
end
for_inventory(inventory_id) click to toggle source
# File lib/toolhound-ruby/inventory_item.rb, line 65
def for_inventory(inventory_id)

end
reassign(options) click to toggle source
# File lib/toolhound-ruby/inventory_item.rb, line 173
def reassign(options)
  options         = (options || {}).dup
  entity_id       = options[:entity_id]
  inventory_id_id = options[:inventory_id_id]
  job_id          = options[:job_id]

  query_opts      = {}
  query_opts[:debug] = options.delete(:debug) if options[:debug]

  raise ArgumentError.new(:entity_id)       unless entity_id
  raise ArgumentError.new(:inventory_id_id) unless inventory_id_id
  # raise ArgumentError.new(:job_id)          unless job_id

  unless job_id.nil?
    sql = build_sql(from: "job", where: [{"job.int_job_id" => job_id}], limit: 1)
    result = query(sql, first: true)

    raise ArgumentError.new(:no_job) if result.length == 0
  end

  sql = "SELECT * FROM tblRentalCharge WHERE intInventoryIDID = '#{inventory_id_id}' AND intEntityID = '#{entity_id}' "
  result = query(sql, first: true)

  raise ArgumentError.new(:no_charge) if result.length == 0

  id = result.first[:rental_charge_id]
  update({attributes: {
    int_job_id: {value: job_id, null: true}
  }, table: "rental_charge", where: [{"rental_charge.int_rental_charge_id" => id}]}, query_opts)

end