# EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';
module Toolhound

  # Class to parse GitHub repository owner and name from
  # URLs and to generate URLs
  class Transaction < Base


    # self.table_name   = :rental
    # self.primary_key  = :int_rental_id

    def default_selects
      # tblWorkOrder.varWorkOrderNo,
      # tblInventoryID.varInventoryID,
      {
        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 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 return_periods(options = {})
      options       = (options || {}).dup
      entity_id     = options.delete :entity_id
      damaged_only  = options.delete :damaged_only

      wheres = []

      wheres << {'transaction.int_entity_id' => entity_id} if 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'
               "
      if damaged_only
        joins << "INNER JOIN tblReturnStatus ON tblReturnStatus.intReturnStatusID = tblTransactionDetail.intReturnStatusID"
        wheres << "(tblReturnStatus.bolCreateWO = 1 OR tblReturnStatus.bolRetire = 1)"
      end

      build_and_query(joins: joins, selects: selects, where: wheres, from: 'tblTransactionDetail').first

    end

    def returns(options = {})
      options       = (options || {}).dup
      entity_id     = options.delete :entity_id
      from_date     = options.delete :from
      to_date       = options.delete :to
      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.present?
      wheres << "tblTransaction.dteTransactionDate BETWEEN '#{parse_time(from_date)}' AND '#{parse_time(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

    def remove(job_id)
      # procedure("Job_Delete", {job_id: job_id})
    end


  end
end