lib/toolhound-ruby/inventory_item.rb in toolhound-ruby-1.0.32 vs lib/toolhound-ruby/inventory_item.rb in toolhound-ruby-1.0.33

@@ -64,18 +64,58 @@
 
     def for_inventory(inventory_id)
 
     end
 
+    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
+
     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
@@ -128,9 +168,37 @@
 
       build_and_query(selects: selects, where: wheres, joins: joins, order: order)
 
     end
 
+    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
+
+      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
+
+
+      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: job_id
+      }, table: "rental_charge", where: [{"rental_charge.int_rental_charge_id" => id}]}, query_opts)
+
+    end
   end
 end