class StocksArrivingsListsController < CommLogistics::Base::Controller::ListController
  #
  # 未入荷リスト
  # 
  # PO　Number               注文買取のorders.id
  # 発注日                   orders.target_date
  # 製品クラス               order_details.product_set_id
  # 製品モデル               order_details.product_id
  # 発注数 quantity          sum(order_details.quantity)
  # 仕入数 arrived_quantity  紐づいたpurchase_detailsをsum(quantity)
  # 未入荷数  arriving_quantity quantity - arrived_quantity
  #
  def get_record_list
    @control_keys = 'stocks_arrivings_lists'
    
    ss = StocksArrivingsSearch.new
    #ss.post_where_sentence = ' GROUP BY a.product_id '
    ss.table_alias = {'order_details' => 't'}
    # sortテーブルの置き換えに使用
    join_mcls = []
    ars = ss.search(FLAG_ON, OrderDetail, join_mcls, params)
    return ars
  end
  
  class StocksArrivingsSearch < Comm::Tool::SqlSearch
    def get_columns_and_tables(tab, join_lists, params, str_vals)
      str_cols = ' * '
                   
      str_tab =  " FROM (
                   SELECT 
                     po.order_id,
                     po.order_type_code,
                     po.supplier_id,
                     po.target_date,
                     po.product_category_id,
                     po.product_set_id,
                     po.product_id,
                     IFNULL(po.order_quantity, 0) AS quantity,   #発注数
                     IFNULL(pr.arrived_quantity, 0) AS arrived_quantity, #入荷数
                     IFNULL((po.order_quantity-pr.arrived_quantity), po.order_quantity) AS non_arrived_quantity, #未入荷数
                     IFNULL(pr.comp_quantity, 0) AS comp_quantity, #検品済数
                     IFNULL((pr.arrived_quantity - pr.comp_quantity), 0) AS non_comp_quantity, #入荷済未検品済数
                     IFNULL((po.order_quantity-pr.comp_quantity), po.order_quantity) AS non_arrived_non_comp_quantity #未入荷未検品数
                   FROM ( SELECT 
                            order_id, 
                            order_type_code,
                            supplier_id,
                            target_date,
                            product_category_id,
                            product_set_id,
                            product_id,
                            SUM(quantity) AS order_quantity
                          FROM order_details AS od LEFT JOIN orders AS o ON o.id=od.order_id 
                          WHERE order_trigger_code=#{MCODE_ORDER_TRIGGER_ORDER} 
                            AND order_type_code IN (#{MCODE_ORDER_TYPE_BUY}, #{MCODE_ORDER_TYPE_STORING}) 
                            #{params[:trans_condition].blank? ? '' : params[:trans_condition]}
                            AND invalid_flag_code=#{MCODE_FLAG_OFF} 
                            AND state_code=#{MCODE_STATUS2_COMP}
                            AND target_date BETWEEN \"#{params[:start_target_date]}\" and \"#{params[:end_target_date]}\"
                          GROUP BY order_id, product_id ) AS po 
                   LEFT JOIN ( SELECT
                            order_id,
                            product_id,
                            SUM(IF(inspect_status_code=#{MCODE_STATUS2_COMP}, quantity, 0 )) AS comp_quantity,
                            SUM(quantity) AS arrived_quantity
                          FROM purchase_details AS pd LEFT JOIN purchases AS p ON p.id=pd.purchase_id
                          WHERE order_id IS NOT NULL 
                            AND invalid_flag_code=#{MCODE_FLAG_OFF} 
                            #AND inspect_status_code=#{MCODE_STATUS2_COMP}
                            AND target_date BETWEEN \"#{params[:start_target_date]}\" and \"#{params[:target_date]}\"
                          GROUP BY order_id, product_id ) AS pr 
                   ON pr.order_id=po.order_id AND pr.product_id=po.product_id
                WHERE ( po.order_quantity!=pr.comp_quantity OR pr.arrived_quantity IS NULL)
                 ) AS t "
      return str_cols, str_tab, str_vals
    end
    
    #def set_additional_where(str_where, params)
    #  add_str_where(str_where, "  ")
    #end
    #から実装
    def set_target_date_to_where(tab, params, str_where)
    end
  end
end