class Graph::InventoryTurnoverEachAreaController < CommLogistics::Base::Controller::PortletController
  include CommLogistics::Modules::PartialSql
  ETC_AREA_ID = 13
  DATE_THRESHOLD = 90
  def index
    if params[:product_category_id].blank? || params[:product_set_id].blank?
      noDataToDisplay()
      return 
    end
    #自社倉庫
    wids = Warehouse.own_ids.join(',')
    #施設情報取得
    @warehouse_array, @warehouse_hash = Warehouse.find(:all, :select => "id, sales_area_id").index_hashfy([], "id")
    #エリア情報取得
    @area_array, @area_hash = SalesArea.valid.find(:all, :select => "id, disp_name", :order => "disp_sort").index_hashfy([], "id")
    #結果初期化
    @area_stocks = Array.new(@area_array.length, 0)
    @area_non_active_stocks = Array.new(@area_array.length, 0)
    @area_sales = Array.new(@area_array.length, 0)
    @area_turnover = Array.new(@area_array.length, 0)
    
    #貸出在庫を求める
    diff_columns = 'warehouse_id, product_id'
    diff_quantity = "SUM(IFNULL(out_quantity,0)) - SUM(IFNULL(in_quantity,0)) AS diff_quantity"
    target_conditions = " AND sd.product_category_id IN (#{params[:product_category_id]}) AND sd.product_set_id IN (#{params[:product_set_id]}) "
    group_conditions = " WHERE diff.warehouse_id NOT IN (#{wids}) "
    warehouse_stocks = Stock.find_by_sql(" SELECT 
            a.warehouse_id,
            a.product_set_id,
            a.product_id,
            p.spec1,
            IFNULL(a.existing_quantity,0) + IFNULL(f.diff_quantity,0) AS existing_quantity
          FROM (SELECT a.warehouse_id, a.product_set_id, a.product_id, SUM(a.existing_quantity) AS existing_quantity
                         FROM stocks AS a
                         WHERE a.warehouse_id NOT IN (#{wids}) 
                           AND a.product_category_id IN (#{params[:product_category_id]})
                           AND a.product_set_id IN (#{params[:product_set_id]})
                         GROUP BY a.warehouse_id, a.product_id
           ) AS a 
           LEFT JOIN master_app_production.products AS p ON a.product_id=p.id
           LEFT JOIN #{make_stock_diff(params[:end_target_date], diff_columns, diff_quantity, group_conditions, nil, target_conditions)}
              AS f ON a.warehouse_id=f.warehouse_id AND a.product_id=f.product_id
           WHERE IFNULL(a.existing_quantity,0) + IFNULL(f.diff_quantity,0) > 0
           " )
    search_date_start_date = (Date.parse(params[:end_target_date]) - DATE_THRESHOLD).to_s
    
#    non_active_stocks = Shipping.find_by_sql(" SELECT 
#            a.warehouse_id,
#            a.product_set_id,
#            a.spec1,
#            DATEDIFF(\"#{params[:end_target_date]}\", IFNULL(a.last_shipping_date,a.max_sale_date)) AS diff_active_date
#            #DATEDIFF(\"#{params[:end_target_date]}\", a.max_shipping_date) AS diff_shipping_date,
#            #DATEDIFF(\"#{params[:end_target_date]}\", b.max_sale_date) AS diff_sale_date
#          FROM ( SELECT 
#                    s.warehouse_id,
#                    sd.product_set_id,
#                    p.spec1,
#                    b.max_sale_date AS max_sale_date,
#                    MIN(IF(s.target_date>=b.max_sale_date OR b.max_sale_date IS NULL, s.target_date, NULL)) AS last_shipping_date
#                  FROM shipping_details as sd 
#                  LEFT JOIN shippings as s on sd.shipping_id=s.id 
#                  LEFT JOIN master_app_production.products as p on sd.product_id=p.id
#                  LEFT JOIN (SELECT 
#                    warehouse_id,
#                    sd.product_set_id,
#                    p.spec1,
#                    MAX(target_date) AS max_sale_date
#                  FROM sale_details as sd 
#                  LEFT JOIN sales as s on sd.sale_id=s.id 
#                  LEFT JOIN master_app_production.products as p on sd.product_id=p.id
#                            WHERE s.invalid_flag_code=#{MCODE_FLAG_OFF}
#                              AND state_code=#{MCODE_STATUS2_COMP}
#                              AND sd.product_category_id IN (#{params[:product_category_id]})
#                              AND sd.product_set_id IN (#{params[:product_set_id]})
#                              AND s.target_date <= \"#{params[:end_target_date]}\"
#                              #AND s.target_date >= \"#{search_date_start_date}\"
#                            GROUP BY warehouse_id, sd.product_set_id, spec1
#                  ) AS b ON s.warehouse_id=b.warehouse_id
#                          AND sd.product_set_id=b.product_set_id
#                          AND p.spec1=b.spec1
#                  WHERE s.invalid_flag_code=#{MCODE_FLAG_OFF}
#                    AND s.shipping_state_code=#{MCODE_SHIPPING_STATE_COMP}
#                    #AND s.shipping_type_code NOT IN (#{MCODE_SHIPPING_TYPE_EXCHANGE})
#                    AND sd.product_category_id IN (#{params[:product_category_id]})
#                    AND sd.product_set_id IN (#{params[:product_set_id]})
#                    AND s.target_date <= \"#{params[:end_target_date]}\"
#                    #AND s.target_date >= \"#{search_date_start_date}\"
#                  GROUP BY s.warehouse_id, sd.product_set_id, p.spec1
#          ) AS a 
#    ")
    #最終売り上げとってみる
    non_active_stocks = Sale.find_by_sql("SELECT 
                    warehouse_id,
                    sd.product_set_id,
                    p.spec1,
                    MAX(target_date) AS max_sale_date
                  FROM sale_details as sd 
                  LEFT JOIN sales as s on sd.sale_id=s.id 
                  LEFT JOIN master_app_production.products as p on sd.product_id=p.id
                            WHERE s.invalid_flag_code=#{MCODE_FLAG_OFF}
                              AND state_code=#{MCODE_STATUS2_COMP}
                              AND sd.product_category_id IN (#{params[:product_category_id]})
                              AND sd.product_set_id IN (#{params[:product_set_id]})
                              AND s.target_date <= \"#{params[:end_target_date]}\"
                            GROUP BY warehouse_id, sd.product_set_id, spec1
    ")
    
    non_active_hash = Hash.new
    non_active_stocks.each_with_index do |ar, i|
      non_active_hash.store(ar.warehouse_id.to_s + '-' + ar.product_set_id.to_s + '-' + ar.spec1.to_s, ar)
    end
    tmp_end_date = Date.parse(params[:end_target_date])
    #高速化のため、一度探した出荷日は持っておく。
    @shipping_hash = {}
    warehouse_stocks.each do |rec|
      warehouse_index = @warehouse_hash[rec.warehouse_id.to_i]
      area_id = @warehouse_array[warehouse_index]['sales_area_id']
      area_index = @area_hash[(area_id.blank? ? ETC_AREA_ID : area_id.to_i)]
      unless area_index.blank?
        @area_stocks[area_index] = @area_stocks[area_index] + rec.existing_quantity.to_i
        tmp_key = rec.warehouse_id.to_s + '-' + rec.product_set_id.to_s + '-' + rec.spec1.to_s
        non_stock_ar = non_active_hash[tmp_key]
  #      if !non_stock_ar || (non_stock_ar && (non_stock_ar.diff_active_date.to_i > DATE_THRESHOLD  || non_stock_ar.diff_active_date.blank?))
  #        @area_non_active_stocks[area_index] = @area_non_active_stocks[area_index] + rec.existing_quantity.to_i
  #      end
        unless non_stock_ar && (tmp_end_date - Date.parse(non_stock_ar.max_sale_date)) <= DATE_THRESHOLD
          unless @shipping_hash.has_key? tmp_key
            tmp_start_date = non_stock_ar ? " AND s.target_date >= \"#{non_stock_ar.max_sale_date.to_s}\" " : ""
            shipping_date = Shipping.find_by_sql("SELECT DATEDIFF(\"#{params[:end_target_date]}\",MIN(target_date)) AS min_shipping_diff, MIN(target_date) AS min_shipping_date
                                                    FROM shipping_details AS sd 
                                                    LEFT JOIN shippings AS s ON sd.shipping_id=s.id
                                                    LEFT JOIN master_app_production.products AS p ON sd.product_id=p.id
                                                  WHERE s.invalid_flag_code=#{MCODE_FLAG_OFF}
                                                    AND s.shipping_state_code=#{MCODE_SHIPPING_STATE_COMP}
                                                    AND s.warehouse_id=#{rec.warehouse_id}
                                                    AND sd.product_set_id=#{rec.product_set_id}
                                                    AND p.spec1=\"#{rec.spec1}\"
                                                    AND s.target_date <= \"#{params[:end_target_date]}\"
                                                    #{tmp_start_date}
            ").first
            @shipping_hash[tmp_key]=shipping_date
          else
            shipping_date = @shipping_hash[tmp_key]
          end
          unless shipping_date && !shipping_date.min_shipping_diff.blank? && shipping_date.min_shipping_diff.to_i <= DATE_THRESHOLD
            move_log = Shipping.find_by_sql("SELECT 'shippings' AS table_name, target_date
                                    FROM shipping_details AS sd 
                                    LEFT JOIN shippings AS s ON sd.shipping_id=s.id
                                    LEFT JOIN master_app_production.products AS p ON sd.product_id=p.id
                                  WHERE s.invalid_flag_code=#{MCODE_FLAG_OFF}
                                    AND s.shipping_state_code=#{MCODE_SHIPPING_STATE_COMP}
                                    AND s.warehouse_id=#{rec.warehouse_id}
                                    AND sd.product_set_id=#{rec.product_set_id}
                                    AND p.spec1=\"#{rec.spec1}\"
                                    AND s.target_date BETWEEN \"#{shipping_date.min_shipping_date}\" AND \"#{params[:end_target_date]}\"
                                  UNION (SELECT 'restorations' AS table_name, target_date
                                    FROM restoration_details AS sd
                                    LEFT JOIN restorations AS s ON sd.restoration_id=s.id
                                    LEFT JOIN master_app_production.products AS p ON sd.product_id=p.id
                                  WHERE s.invalid_flag_code=#{MCODE_FLAG_OFF}
                                    AND s.state_code=#{MCODE_STATUS2_COMP}
                                    AND s.warehouse_id=#{rec.warehouse_id}
                                    AND sd.product_set_id=#{rec.product_set_id}
                                    AND p.spec1=\"#{rec.spec1}\"
                                    AND s.target_date BETWEEN \"#{shipping_date.min_shipping_date}\" AND \"#{params[:end_target_date]}\"
                                  ) ORDER BY target_date, FIELD(table_name, \"restorations\",\"shippings\")
            ")
            tmp_rest_date = nil
            active_flag = false
            move_log.each do |ar|
              if ar.table_name=='restorations'
                tmp_rest_date = ar.target_date
              elsif ar.table_name=='shippings' && !tmp_rest_date.blank?
                if (ar.target_date - tmp_rest_date >= DATE_THRESHOLD) && (tmp_end_date - ar.target_date <= DATE_THRESHOLD)
                  active_flag = true
                  break
                end
                tmp_rest_date = nil
              end
            end
            unless active_flag
              @area_non_active_stocks[area_index] = @area_non_active_stocks[area_index] + rec.existing_quantity.to_i
            end
          end
        end

      end
    end
    #(y,m,d) = params[:end_target_date].split(/-/)
    #start_target_date = Date.new(y.to_i,m.to_i,1)
    #end_target_date = start_target_date.end_of_month
    
    sales = Sale.find_by_sql(" SELECT 
            w.sales_area_id, 
            SUM(quantity) AS total_quantity
          FROM sale_details AS sd 
          LEFT JOIN sales AS s ON s.id=sd.sale_id
          LEFT JOIN master_app_production.warehouses AS w ON s.warehouse_id=w.id
          WHERE s.target_date between \"#{params[:start_target_date]}\" AND \"#{params[:end_target_date]}\"
            AND s.invalid_flag_code=#{MCODE_FLAG_OFF}
            AND s.state_code=#{MCODE_STATUS2_COMP}
            AND sd.product_category_id IN (#{params[:product_category_id]})
            AND sd.product_set_id IN (#{params[:product_set_id]})
          GROUP BY w.sales_area_id
                          ")
    total_area_cnt = 0
    sales.each do |rec|
      area_index = @area_hash[(rec.sales_area_id.blank? ? ETC_AREA_ID : rec.sales_area_id.to_i)]
      unless area_index.blank?
        @area_sales[area_index] = rec.total_quantity.to_i
        @area_turnover[area_index] = (@area_sales[area_index]==0 ? nil : (@area_stocks[area_index].to_f / @area_sales[area_index].to_f).round(1))
        total_area_cnt = total_area_cnt + 1 if @area_stocks[area_index] > 0
      end
    end
    @threshold = @area_turnover.sort {|a, b| (-1) * (a <=> b) }[(total_area_cnt / 2).ceil]
    respond_to do |format|
      format.xml
      format.json
    end
  end
end