inventory_item.rb in toolhound-ruby-1.0.11

- old
+ new

@@ -36,11 +36,11 @@
           :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],
+        inventory_text:       [:var_description, :var_part_no, {varUserField1: "varGlRevenue"}, {varUserField2: "varGlCOGSCode"}, {varUserField3: "varPhaseCode"}],
         inventory_id:         ["intInventoryIdID", {varInventoryID: "inventory_idid"}]
       }
 
     end
 
@@ -64,24 +64,46 @@
 
     def for_inventory(inventory_id)
 
     end
 
-    def charges_for_entity(entity_id)
+    def charges_for_entity(entity_id, job_id=nil)
       joins = default_joins
       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}'
                   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 tblRentalCharge.intJobID, tblRentalCharge.intRentalChargeID
+                  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}'"
 
       selects = default_selects
+      selects[:job_text] = [:int_job_id, :int_job_text_id, :var_job_number, :var_job]
       selects[:rental_query] = [:dec_total_rent]
 
-      build_and_query(selects: selects, where: [], joins: joins, order: "tblInventoryText.varPartNo")
+      # (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