class StocksUbdListsController < CommLogistics::Base::Controller::ListController
  #
  # UBD切迫本数リスト
  # 
  # 納品先    warehouse_id
  # 代理店   customer_id
  # 担当者   hospitals_warehouses.sales_area_id
  # エリア   hospitals_warehouses.user_position_id
  # 製品クラス   product_set_id
  # 製品モデル   product_id
  # 長さ    spec_1
  # 径   spec_2
  # 在庫数   existing_quantity
  # 売上数   other_sale_quantity
  # 最終売上日 last_sold_date  同一モデルのstatusが完のsaleの最新のtarget_date
  # １ヶ月以内 one start_target_dateから対象期間のubdの本数
  # 3ヶ月以内 three start_target_dateから対象期間のubdの本数
  # 6ヶ月以内 six start_target_dateから対象期間のubdの本数
  # 9ヶ月以内 nine  start_target_dateから対象期間のubdの本数
  # 12ヶ月以内  twelve  start_target_dateから対象期間のubdの本数
  # 13ヶ月以上  more_than_thirteen  start_target_dateから対象期間のubdの本数
  #
  #
  def get_record_list
    @control_keys = 'stocks_ubd_lists'
    
    ss = StocksUbdSearch.new
    ss.table_alias = {'stocks' => 's',
                      'warehouses' => 's'}
    ars = ss.search(FLAG_ON, Stock, [], params)
    return ars
  end
  
  class StocksUbdSearch < Comm::Tool::SqlSearch
    def get_columns_and_tables(tab, join_lists, params, str_vals)
      (y,m,d) = params[:end_target_date].split(/-/)
      in_1month = Date.new(y.to_i,m.to_i,1) >> 1
      in_3month = (in_1month >> 2)
      in_6month = (in_3month >> 3)
      in_9month = (in_6month >> 3)
      in_12month = (in_9month >> 3)

      str_cols = ' * '
      str_tab =  " FROM ( SELECT st.warehouse_id,
                                 st.customer_id,
                                 st.supplier_id,
                                 st.product_category_id,
                                 st.product_set_id,
                                 st.product_id,
                                 w.sales_area_id,
                                 w.user_position_id,
                                 IFNULL(sl.sales_quantity, 0) AS sales_quantity,
                                 sl.last_target_date AS last_target_date,
                                 in_1month_quantity, 
                                 in_3month_quantity, 
                                 in_6month_quantity, 
                                 in_9month_quantity, 
                                 in_12month_quantity,
                                 in_24month_quantity
                         FROM ( SELECT st.warehouse_id,
                                       st.customer_id,
                                       st.supplier_id,
                                       st.product_category_id,
                                       st.product_set_id,
                                       st.product_id, 
                                       SUM(IF(ubd < \'#{in_1month.to_s}\', existing_quantity, 0)) AS in_1month_quantity,
                                       SUM(IF(ubd < \'#{in_3month.to_s}\', existing_quantity, 0)) AS in_3month_quantity,
                                       SUM(IF(ubd < \'#{in_6month.to_s}\', existing_quantity, 0)) AS in_6month_quantity,
                                       SUM(IF(ubd < \'#{in_9month.to_s}\', existing_quantity, 0)) AS in_9month_quantity,
                                       SUM(IF(ubd < \'#{in_12month.to_s}\', existing_quantity, 0)) AS in_12month_quantity,
                                       SUM(IF(ubd >= \'#{in_12month.to_s}\', existing_quantity, 0)) AS in_24month_quantity
                                FROM stocks AS st LEFT JOIN master_app_production.product_sets AS ps ON st.product_set_id=ps.id 
                                WHERE ps.invalid_flag_code=#{MCODE_FLAG_OFF} 
                                GROUP BY warehouse_id,customer_id,st.supplier_id,product_id 
                              ) AS st
                         LEFT JOIN master_app_production.product_sets AS ps ON st.product_set_id=ps.id
                         LEFT JOIN master_app_production.warehouses AS w ON st.warehouse_id=w.id
                         LEFT JOIN ( SELECT s.warehouse_id,
                        s.customer_id,
                        s.supplier_id,
                        sd.product_id,
                        SUM(quantity) AS sales_quantity,
                        MAX(target_date) AS last_target_date
                               FROM sale_details AS sd LEFT JOIN sales AS s ON sd.sale_id=s.id 
                               WHERE target_date BETWEEN \'#{params[:start_target_date]}\' AND \'#{params[:start_target_date]}\'  AND s.invalid_flag_code=#{MCODE_FLAG_OFF} AND state_code=#{MCODE_STATUS2_COMP}
                               GROUP BY warehouse_id, customer_id, supplier_id, product_id
                  ) AS sl ON st.warehouse_id=sl.warehouse_id AND st.customer_id=sl.customer_id AND st.supplier_id=sl.supplier_id AND st.product_id=sl.product_id ) AS s"
      return str_cols, str_tab, str_vals
    end
    #空実装
    def set_target_date_to_where(tab, params, str_where)
    end
  end
end