class ProductCountStruct < Struct.new(:product_id, :quantity, :product_code, :name, :unit_value, :unit_of_measure); end

class ProductCount < ActiveRecord::Base
  belongs_to :inventory_need
  belongs_to :product

  def self.between_dates(start_date, end_date, area)
    all_archived_product_counts = Array.new
    area_condition = area_condition(area)
    #first get all the archived product counts as archived_delivery_order_items
    all_archived_product_counts = ArchivedDeliveryOrderItem.find(:all,
        :select => "adoi.product_id, sum(quantity) as quantity, adoi.product_code, adoi.name, adoi.unit_value, adoi.unit_of_measure",
        :joins =>  "as adoi inner join archived_delivery_orders as ado on ado.id = adoi.archived_delivery_order_id
                    inner join customers as cu on cu.id = ado.customer_id
                    inner join delivery_locations as dl on cu.id = dl.customer_id",
        :conditions => ["ado.delivery_date BETWEEN ? AND ? #{area_condition}", start_date, end_date],
        :group => "adoi.product_id, adoi.product_code, adoi.name, adoi.unit_value, adoi.unit_of_measure")
   archived_product_counts_without_ids = Array.new
   archived_product_counts_with_ids = Array.new
   # now separate out the archived_delivery_orders where there were no product_ids
   for product_count in all_archived_product_counts
     if product_count.product.nil? || product_count.product_id = 0
       archived_product_counts_without_ids << product_count
     else
       archived_product_counts_with_ids << product_count
     end
   end
   # now find the delivery_order product counts though only for customers who are not inactive.
   # first get nonsubstituted delivery order items.
   nonsub_unarchived_product_counts = DeliveryOrderItem.find( :all,
        :select => "doi.product_id, sum(quantity) as quantity, doi.product_code, doi.name, doi.unit_value, uom.name as unit_of_measure",
        :joins =>  "as doi inner join delivery_orders as deo on deo.id = doi.delivery_order_id
                           inner join customers as cu on cu.id = deo.customer_id
                           inner join products as pr on pr.id  = doi.product_id
                           inner join unit_of_measures as uom on pr.unit_of_measure_id = uom.id
                           inner join delivery_locations as dl on cu.id = dl.customer_id",
        :conditions => ["deo.delivery_date BETWEEN ? AND ? AND cu.status <> ?
                         AND pr.out_of_stock = false AND pr.out_of_season = false #{area_condition}",
                           start_date, end_date, CustomerStatus::INACTIVE],
        :group => "doi.product_id, doi.product_code, doi.name, doi.unit_value, uom.name" )
   # add the delivery_order product_count to the archived product_counts with ids.
   # thankfully these are so similar that they can be grouped together easily
   archived_product_counts_with_ids += nonsub_unarchived_product_counts
   #now find the substituted delivery order items.
   sub_unarchived_product_counts = DeliveryOrderItem.find( :all,
        :select => "sub.id as product_id, sum(quantity) as quantity, sub.product_code, sub.name, sub.unit_value, uom.name as unit_of_measure",
        :joins =>  "as doi inner join delivery_orders as deo on deo.id = doi.delivery_order_id
                           inner join customers as cu on cu.id = deo.customer_id
                           inner join products as pr on pr.id  = doi.product_id
                           inner join products as sub on sub.id = pr.substitution_id
                           inner join unit_of_measures as uom on sub.unit_of_measure_id = uom.id
                           inner join delivery_locations as dl on cu.id = dl.customer_id",
        :conditions => ["deo.delivery_date BETWEEN ? AND ? AND cu.status <> ?
                         AND (pr.out_of_stock = true OR pr.out_of_season = true) #{area_condition}",
                         start_date, end_date, CustomerStatus::INACTIVE],
        :group => " sub.id, sub.product_code, sub.name, sub.unit_value, uom.name" )
   for substituted_item in sub_unarchived_product_counts
     substituted_item.name = substituted_item.name + " ** substitution"
     substituted_item.product_id = substituted_item.product_id + 10000
   end
   # now add in the substituted delivery order items.
   archived_product_counts_with_ids += sub_unarchived_product_counts
   #group the combined product_counts by product.id in order to get a sum of the quantities
   grouped_product_counts  = archived_product_counts_with_ids.group_by {|product_count| product_count.product_id}
   product_counts = Array.new
   for key in grouped_product_counts.keys
     product_count = ProductCountStruct.new
     product_count.product_id = grouped_product_counts[key].last.product_id
     # this is the key line that gets the sum of the quantities to get a total count
     product_count.quantity = grouped_product_counts[key].sum {|pc| pc.quantity}
     product_count.product_code = grouped_product_counts[key].last.product_code
     product_count.name = grouped_product_counts[key].last.name
     product_count.unit_value = grouped_product_counts[key].last.unit_value
     product_count.unit_of_measure = grouped_product_counts[key].last.unit_of_measure
     product_counts << product_count
   end
   # now we add the product_counts without ids back in to the fold
   product_counts += archived_product_counts_without_ids
   #again we group though now by name in order to add the missing id product_count
   #to the quantity sums.
   all_grouped_product_counts  = product_counts.group_by  do |product_count|
       string = product_count.name
       string += product_count.unit_value.to_s unless product_count.unit_value.nil?
       string += product_count.unit_of_measure unless product_count.unit_of_measure.nil?
       string
     end

   product_counts = Array.new
   for key in all_grouped_product_counts.keys
     product_count = ProductCountStruct.new
     product_count.product_id = all_grouped_product_counts[key].first.product_id
     product_count.quantity = all_grouped_product_counts[key].sum {|pc| pc.quantity}
     product_count.product_code = all_grouped_product_counts[key].first.product_code
     product_count.name = all_grouped_product_counts[key].first.name
     product_count.unit_value = all_grouped_product_counts[key].first.unit_value
     product_count.unit_of_measure = all_grouped_product_counts[key].first.unit_of_measure
     product_counts << product_count
   end
   #finally we do a sort by product_code and name.
   product_counts.sort_by {|product_count| [product_count.product_code, product_count.name]}
  end

  private

  def self.area_condition(area)
    case area
    when "All"
      return ""
    when "SanAntonio"
      return " AND dl.zip LIKE '782%'"
    when "Austin"
      return "AND dl.zip not LIKE '782%'"
    end
  end
end