class Inventory < CommLogistics::Base::Model::LogisticsModel
  include Comm::Module::Model::Logging
  has_many :child_details,
           :class_name => 'InventoryDetail',
           :dependent => :delete_all
  def self.last_inventory_date
    ars = self.find_by_sql("SELECT DISTINCT(inventory_date) FROM inventories ORDER BY inventory_date DESC LIMIT 1")
    if ars.length == 0
      return nil
    end
    return ars.first.inventory_date
  end
  def self.inventory_dates
    ars = self.find_by_sql("SELECT DISTINCT(inventory_date) FROM inventories ORDER BY inventory_date DESC")
    if ars.length == 0
      return []
    end
    return ars.map{|ar| ar.inventory_date}
  end
  def self.collect_inventory_info(warehouse_id, customer_id, supplier_id)
    stocks_where_clause = "WHERE s.warehouse_id = #{warehouse_id} AND s.customer_id = #{customer_id} AND s.supplier_id = #{supplier_id} "
    self.collect_inventory_infos(stocks_where_clause)
  end
  def self.collect_initial_inventory_infos(own_warehouse_id_ary)
    stocks_where_clause = "WHERE s.existing_quantity > 0 AND s.warehouse_id NOT IN (#{own_warehouse_id_ary.join(",")}) "
    self.collect_inventory_infos(stocks_where_clause)
  end
  def self.collect_inventory_infos(stocks_where_clause)
    sql =  "SELECT w.customer_id, w.id AS warehouse_id, w.supplier_id, NULLIF(SUBSTR(MIN(CONCAT(LPAD(wc.id,11,'0'),LPAD(IFNULL(wc.shipment_customer_id,'0'),11,'0'))),12),0) AS shipment_customer_id, "
    sql << "NULLIF(SUBSTR(MIN(CONCAT(LPAD(wc.id,11,'0'),LPAD(IFNULL(wc.send_customer_id,'0'),11,'0'))),12),0) AS send_customer_id, w.user_position_id, w.sales_area_id, w.existing_quantity AS total_quantity "
    sql << "FROM (SELECT w.*, s.supplier_id, s.customer_id, s.existing_quantity "
    sql << "FROM (SELECT warehouse_id, supplier_id, customer_id, SUM(existing_quantity) AS existing_quantity FROM stocks as s "
    sql << stocks_where_clause
    sql << "GROUP BY warehouse_id, supplier_id, customer_id ) AS s "
    sql << "LEFT JOIN master_app_production.warehouses AS w ON w.id = s.warehouse_id) AS w "
    sql << "LEFT JOIN master_app_production.warehouses_customers AS wc ON w.id=wc.warehouse_id AND w.customer_id=wc.customer_id "
    sql << "WHERE w.invalid_flag_code=0 GROUP BY w.id, w.supplier_id, w.customer_id ORDER BY w.customer_id, w.id"
    parent_ars = self.find_by_sql(sql)
    return parent_ars
  end
  def self.collect_inventory_details_infos(warehouse_id, customer_id, supplier_id)
    sql =  "SELECT product_category_id, product_set_id, product_id, lot_number, serial_number, ubd, existing_quantity, short_shipped_quantity "
    sql << "FROM stocks WHERE warehouse_id = ? AND customer_id = ? AND supplier_id = ? AND existing_quantity > 0"
    child_ars = self.find_by_sql([sql, warehouse_id, customer_id, supplier_id])
    if child_ars.length == 0
      raise "No match record"
    end
    return child_ars
  end
end