class StocksListsController < CommLogistics::Base::Controller::ListController
  def get_record_list
    @control_keys = 'stocks_lists'
    
    ss = StocksSearch.new
    #ss.post_where_sentence = ' GROUP BY a.product_id, a.ubd '
    #ss.date_table = 'b'
    ss.table_alias = {'stocks' => 't',
                      'products' => 't',
                      'warehouses' => 't',
                      'sales_areas' => 't'}
    # sortテーブルの置き換えに使用
    #join_mcls = []
    join_mcls = [{:tab => 'stocks',
                  :cols => Stock.column_names},
                 {:tab => 'products',
                  :cols => Product.column_names},
                 {:tab => 'warehouses',
                  :cols => Warehouse.column_names}]
    ars = ss.search(FLAG_ON, Stock, join_mcls, params)
    return ars
  end
  
  class StocksSearch < Comm::Tool::SqlSearch
    include CommLogistics::Modules::PartialSql
    def get_columns_and_tables(tab, join_lists, params, str_vals)
      require 'config/site_config'
      str_cols = ' * '
      diff_columns = 'warehouse_id, customer_id, supplier_id, product_id, lot_number, serial_number, location_number, ubd'
      left_join_f = ' a.warehouse_id=f.warehouse_id AND ((a.customer_id is NULL AND f.customer_id is NULL) OR a.customer_id=f.customer_id) AND a.supplier_id=f.supplier_id AND a.product_id=f.product_id AND a.lot_number=f.lot_number AND a.serial_number=f.serial_number AND a.location_number=f.location_number AND a.ubd=f.ubd '
      stock_diff_sql = make_stock_diff(params[:end_target_date], diff_columns)
      #未検品のものを抽出する
      if $SHOW_NON_INSPECT_STOCKS_LIST
        non_inspect_sql = "LEFT JOIN (" + make_non_inspect_quantity_from_stock_diff(stock_diff_sql, diff_columns) + ") AS g ON #{left_join_f.gsub('f.', 'g.')}"
        non_inspect_quantity = ",IFNULL(g.diff_quantity,0) AS non_inspect_quantity"
        non_inspect_where = " OR IFNULL(g.diff_quantity,0) !=0 "
      else
        non_inspect_sql = ""
        non_inspect_quantity = ""
        non_inspect_where = ""
      end

      str_tab =  " FROM ( SELECT 
                     a.id,
                     a.warehouse_id,
                     a.customer_id,
                     a.supplier_id,
                     c.user_position_id,
                     d.global_area_id,
                     c.sales_area_id,
                     a.product_category_id,
                     a.product_set_id,
                     a.product_id,
                     a.lot_number,
                     a.serial_number,
                     a.location_number,
                     a.ubd,
                     a.validity_period,
                     a.unit_code,
                     a.created_at,
                     a.updated_at,
                     IFNULL(b.shipment_validity_period,0) AS shipment_validity_period,
                     ADDDATE(a.ubd, INTERVAL #{($MONTH_VALIDITY_PERIOD ? ' -30 * ' : ' -1 * ')} IFNULL(b.shipment_validity_period,0) DAY ) AS shipment_limit_date,
                     IFNULL(a.existing_quantity,0) + IFNULL(f.diff_quantity,0) AS existing_quantity,
                     IFNULL(a.available_quantity,0) + IFNULL(f.diff_a_quantity,0) AS available_quantity,
                     IFNULL(a.short_shipped_quantity,0) + IFNULL(f.diff_s_quantity,0) AS short_shipped_quantity
                     #{non_inspect_quantity}
                   FROM stocks AS a 
                   LEFT JOIN master_app_production.products AS b ON a.product_id=b.id 
                   LEFT JOIN master_app_production.warehouses AS c ON a.warehouse_id=c.id
                   LEFT JOIN master_app_production.sales_areas AS d ON c.sales_area_id=d.id
                   LEFT JOIN 
                      #{stock_diff_sql}
                   AS f ON #{left_join_f} 
                   #{non_inspect_sql}
                 WHERE ((IFNULL(a.existing_quantity,0) + IFNULL(f.diff_quantity,0)) !=0 #{non_inspect_where}) #{params[:stock_condition].blank? ? '' : params[:stock_condition]}) 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