lib/toolhound-ruby/transaction.rb in toolhound-ruby-1.0.12 vs lib/toolhound-ruby/transaction.rb in toolhound-ruby-1.0.13

@@ -8,27 +8,88 @@
 
     # self.table_name   = :rental
     # self.primary_key  = :int_rental_id
 
     def default_selects
+      # tblWorkOrder.varWorkOrderNo,
+      # tblInventoryID.varInventoryID,
       {
-        # manufacturer:   [:int_manufacturer_id ],
-        # manufacturer_text: [:var_manufacturer, :var_phone, :var_fax, :var_url]
-        # rental_item_detail: [:var_status, :status_date, ],
-        # rental_detail: [:int_rental_id, :int_inventory_id, :int_quantity, :dec_daily, :dec_weekly, :dec_monthly, :dec_selling_price],
+        transaction_detail:     ['intInventoryIdID', :int_transaction_detail_id, :int_qty, :int_qty_return, :dec_unit_cost],
+        transaction:            [:int_transaction_id, :dte_transaction_date, :var_transaction_no, :int_location_id, :int_entity_id, :int_job_id],
+        transaction_type:       [:bol_return],
+        job_text:               [:var_job_number, :var_job],
+        return_status:          [:int_return_status_id],
+        return_status_text:     [:var_return_status],
+        inventory_status_text:  [:var_inventory_status],
+        entity:                 [{var_entity_id: "varJobNo"}],
+        entity_location_text:   [:var_location_name],
+
+        inventory_id:           [{var_inventory_id: "varInventoryIdNo"}],
+        inventory:              [:int_inventory_id, :int_category_id, :int_sub_category_id],
+        inventory_text:         [:var_description, :var_part_no, {varUserField1: "varGlRevenue"}, {varUserField2: "varGlCOGSCode"}, {varUserField3: "varPhaseCode"}],
+        inventory_item_text:    [:var_serial_number]
       }
     end
 
     def default_joins
       arr = []
-      # arr << "INNER JOIN tblManufacturerText ON (tblManufacturerText.intManufacturerID = tblManufacturer.intManufacturerID AND varLocaleID = '#{locale}')"
-      # arr << "INNER JOIN tblRentalDetail ON (tblRentalDetail.intRentalDetailID = tblRentalItem.intRentalDetailID)"
+      arr << "INNER JOIN tblTransaction ON tblTransaction.intTransactionID = tblTransactionDetail.intTransactionID"
+      arr << "INNER JOIN tblTransactionType ON tblTransactionType.intTransactionTypeID = tblTransactionDetail.intTransactionTypeID"
+      arr << "INNER JOIN tblReturnStatus ON tblReturnStatus.intReturnStatusID = tblTransactionDetail.intReturnStatusID"
+      arr << "INNER JOIN tblReturnStatusText ON tblReturnStatusText.intReturnStatusID = tblReturnStatus.intReturnStatusID AND tblReturnStatusText.varLocaleID = '#{locale}'"
+      arr << "INNER JOIN tblInventoryStatusText ON tblInventoryStatusText.intInventoryStatusID = tblTransactionType.intInventoryStatusID AND tblInventoryStatusText.varLocaleID = '#{locale}'"
+	    arr << "INNER JOIN tblInventoryID ON tblInventoryID.intInventoryIdID = tblTransactionDetail.intInventoryIdID"
+      arr << "INNER JOIN tblInventory ON tblInventory.intInventoryID = tblInventoryID.intInventoryID"
+      arr << "INNER JOIN tblInventoryText ON tblInventoryText.intInventoryID = tblInventory.intInventoryID AND tblInventoryText.varLocaleID = '#{locale}'"
+      arr << "LEFT OUTER JOIN tblInventoryItem ON tblInventoryItem.intInventoryItemID = tblInventoryID.intInventoryItemID"
+      arr << "LEFT OUTER JOIN tblInventoryItemText ON tblInventoryItemText.intInventoryItemID = tblInventoryItem.intInventoryItemID AND tblInventoryItemText.varLocaleID = '#{locale}'"
+      arr << "INNER JOIN tblEntity ON tblTransaction.intEntityID = tblEntity.intEntityID"
+      arr << "INNER JOIN tblLocation AS tblEntityLocation ON tblEntityLocation.intEntityID = tblEntity.intEntityID"
+      arr << "INNER JOIN tblLocationText AS tblEntityLocationText ON tblEntityLocationText.intLocationID = tblEntityLocation.intLocationID AND tblEntityLocationText.varLocaleID = '#{locale}'"
+      arr << "LEFT OUTER JOIN tblJobText ON tblJobText.intJobID = tblTransaction.intJobID AND tblJobText.varLocaleID = '#{locale}'"
 
+
+  # AND tblTransactionType.bolReturn = 'true'
       arr
     end
 
-    def returns
-      
+
+    def return_periods_for_entity(entity_id)
+      selects = {
+        transaction: [
+          {dte_transaction_date: {as: :min_date, agg: :min} },
+          {dte_transaction_date: {as: :max_date, agg: :max} }
+        ]
+      }
+      joins = []
+      joins << "INNER JOIN tblTransaction ON tblTransaction.intTransactionID = tblTransactionDetail.intTransactionID"
+      joins << "INNER JOIN tblTransactionType ON
+      	         tblTransactionType.intTransactionTypeID = tblTransactionDetail.intTransactionTypeID
+      	        AND tblTransactionType.bolReturn = 'true'
+               "
+
+
+      build_and_query(joins: joins, selects: selects, where: [{'transaction.int_entity_id' => entity_id}], from: 'tblTransactionDetail').first
+
+    end
+
+    def returns(options)
+      entity_id     = options.delete :entity_id
+      from_date     = options.delete :from_date
+      to_date       = options.delete :to_date
+      damaged_only  = options.delete :damaged_only
+
+      selects = default_selects
+      joins   = default_joins
+      wheres  = []
+      wheres << {"transaction_type.bol_return" => 'true'}
+
+      wheres << {"transaction.int_entity_id" => entity_id}                                  if entity_id
+      wheres << "tblTransaction.dteTransactionDate BETWEEN '#{from_date}' AND '#{to_date}'" if from_date && to_date
+      wheres << "(tblReturnStatus.bolCreateWO = 1 OR tblReturnStatus.bolRetire = 1)"        if damaged_only == true
+
+      build_and_query(joins: joins, selects: selects, where: wheres, from: 'tblTransactionDetail', order: "tblEntityLocationText.varLocationName")
+
     end
     # def insert(variables = {})
     #   vars = {created_user_id: 1, modified_user_id: 1, active: true, job: nil, created_date: nil, modified_date: nil}
     #   # procedure("Job_Insert", vars)
     # end