lib/toolhound-ruby/inventory_item.rb in toolhound-ruby-1.0.22 vs lib/toolhound-ruby/inventory_item.rb in toolhound-ruby-1.0.23

@@ -64,59 +64,66 @@
 
     def for_inventory(inventory_id)
 
     end
 
-    def charges_for_entity(entity_id, job_id=nil)
-      joins = default_joins
+    def charges(options = {})
+      options = (options || {}).dup
+
+      job_id      = options[:job_id]
+      entity_id   = options[:entity_id]
+      order       = options[:order] || "tblInventoryText.varPartNo"
+
+
+      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 AND tblRental.intEntityID = '#{entity_id}'
+                  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
-                  WHERE rc.intEntityID = '#{entity_id}'
+                  #{charge_entity_query}
                   GROUP BY rc.intInventoryIDID
         ) AS tblLatestCharge ON tblLatestCharge.intInventoryIDID = tblInventoryID.intInventoryIdID"
+
       joins << "LEFT OUTER JOIN  (
                   SELECT tblRentalCharge.intJobID, tblRentalCharge.intRentalChargeID
                   FROM tblRentalCharge
-                  WHERE intEntityID = '#{entity_id}'
+                  #{charge_entity_query1}
                 ) AS tblJobCharge ON tblJobCharge.intRentalChargeID = tblLatestCharge.latest_id"
+
       joins << "LEFT OUTER JOIN tblJobText on tblJobText.intJobID = tblJobCharge.intJobID AND tblJobText.varLocaleID = '#{locale}'"
 
-      selects = default_selects
+      selects                 = default_selects
       selects[:job_text]      = [:int_job_id, :int_job_text_id, :var_job_number, :var_job]
       selects[:rental_query]  = [:dec_total_rent]
 
-      # (tblInventoryItem.intQOH=@p_QOH  OR @p_QOH  IS NULL)
       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: "tblInventoryText.varPartNo")
 
-      # SELECT tblInventoryItem.*, subquery1.total_rent FROM tblInventoryItem
-      # INNER JOIN tblInventoryID ON tblInventoryItem.intInventoryItemID = tblInventoryID.intInventoryItemID
-      # INNER JOIN tblInventoryItemText ON tblInventoryItemText.intInventoryItemID = tblInventoryItem.intInventoryItemID
-      # INNER JOIN tblInventoryType ON tblInventoryType.intInventoryTypeID = tblInventoryItem.intInventoryTypeID AND tblInventoryType.bolSerialized = 1
-      # INNER JOIN(
-      # 	SELECT tblRentalItem.intInventoryIDID, SUM(tblRentalItem.decTotalRent) AS total_rent
-      # 	FROM tblRentalItem
-      #   INNER JOIN tblRentalDetail ON tblRentalDetail.intRentalDetailID = tblRentalItem.intRentalDetailID
-      #   INNER JOIN tblRental ON tblRental.intRentalID = tblRentalDetail.intRentalID AND tblRental.intEntityID = 100002
-      #
-      #   GROUP BY tblRentalItem.intInventoryIDID
-      # ) AS subquery1 ON subquery1.intInventoryIDID = tblInventoryID.intInventoryIdID
+      build_and_query(selects: selects, where: wheres, joins: joins, order: order)
+
     end
 
 
 
   end