# INNER JOIN tblInventoryID AS iid ON iid.intInventoryIdID = rc.intInventoryIDID
# INNER JOIN tblInventory AS iv ON iid.intInventoryID = iv.intInventoryID
# INNER JOIN tblInventoryText AS ivt ON iv.intInventoryID = ivt.intInventoryID
module Toolhound

  # Class to parse GitHub repository owner and name from
  # URLs and to generate URLs
  class InventoryItem < Base
    # attr_accessor :owner, :name, :id

    # self.table_name   = "tblInventoryItem"
    # self.primary_key  = "intInventoryItemID"

    #
    # 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
    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: "inventory_idid"}]
      }

    end

    def default_wheres
      [{bolActive: 1 }, {bolDeleted: 0}]
    end

    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

    def for_entity(entity_id)
      all(where: [{int_curr_location_id: entity_id}])
    end

    def for_inventory(inventory_id)

    end

    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]

      # (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
    end



  end
end