class PurchaseDetail < CommLogistics::Base::Model::Detail
  include Comm::Module::Model::Logging
  belongs_to :purchase
  named_scope :child, lambda {|id| {:conditions => ["purchase_id = ?", id]}}
  named_scope :with_parent, :joins => "INNER JOIN `purchases` ON `purchase_details`.`purchase_id` = `purchases`.`id`"
  named_scope :with_product, lambda{ |id| {
    :conditions => ["`purchase_details`.`purchase_id` = ?", id],
    :joins => "LEFT OUTER JOIN `products` ON `products`.id = `purchase_details`.`product_id`"
  }}
  class << columns_hash['ubd']
    def type
      :string
    end
  end
  
  def self.summed_prices(product_id, start_date, end_date=nil)
    if end_date
      where_add = "target_date BETWEEN \'#{start_date}\' AND \'#{end_date}\' "
    else
      where_add = "target_date = \'#{start_date}\' "
    end
    where_add = where_add + ' GROUP BY product_id '
    pcd = self.find_by_sql(["SELECT IFNULL(SUM(quantity), 0) AS summed_quantity, 
                                    IFNULL(SUM(price * quantity), 0) AS summed_price
                             FROM purchase_details LEFT JOIN purchases
                             ON purchases.id = purchase_details.purchase_id
                             WHERE invalid_flag_code = #{MCODE_FLAG_OFF} AND 
                                   state_code = #{MCODE_STATUS2_COMP} AND
                                   inspect_status_code = #{MCODE_STATUS2_COMP} AND
                                   purchase_type_code != #{MCODE_PURCHASE_TYPE_STORING} AND
                                   product_id = ? AND " + where_add, product_id])
    logger.debug(["pd summed_prices", pcd.first.inspect].join("\n\t"))
    return pcd.first
  end
  
  def self.last_price(product_id, date)
    pcd = self.find_by_sql(["SELECT price 
                             FROM purchase_details AS pcd
                             LEFT JOIN purchases AS pc ON pc.id = pcd.purchase_id
                             WHERE pc.target_date <= ? AND 
                                   pcd.product_id = ? AND
                                   pc.invalid_flag_code = #{MCODE_FLAG_OFF} AND
                                   pc.state_code = #{MCODE_STATUS2_COMP} AND
                                   pc.purchase_type_code != #{MCODE_PURCHASE_TYPE_STORING} AND
                                   pcd.inspect_status_code = #{MCODE_STATUS2_COMP}
                             ORDER BY pc.target_date DESC, pc.updated_at DESC, pcd.id DESC
                             LIMIT 1", date, product_id])
    logger.debug(["pd last_price", pcd.first.inspect].join("\n\t"))
    return pcd.first
  end
end