class StocksOwnsListsController < CommLogistics::Base::Controller::ListController
  def get_record_list
    @control_keys = 'stocks_owns_lists'
    ss = StocksOwnsSearch.new
    ss.table_alias = {'stocks' => 'b'}
    ars = ss.search(FLAG_ON, Stock, [], params)
    return ars
  end
  
  class StocksOwnsSearch < 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, supplier_id, product_id, ubd'
      diff_quantity = "SUM(IFNULL(out_quantity,0)) - SUM(IFNULL(in_quantity,0)) AS diff_quantity,
                       SUM(IFNULL(out_a_quantity,0)) - SUM(IFNULL(in_a_quantity,0)) AS diff_a_quantity"
      left_join_f = " a.warehouse_id=f.warehouse_id AND a.supplier_id=f.supplier_id AND a.product_id=f.product_id AND a.ubd=f.ubd "
      stock_diff_sql = make_stock_diff(params[:end_target_date], diff_columns + ',customer_id', diff_quantity, " WHERE diff.customer_id IS NULL ")
      #未検品のものを抽出する
      if $SHOW_NON_INSPECT_STOCKS_LIST
        non_inspect_sql = "LEFT JOIN (" + make_non_inspect_quantity_from_stock_diff(stock_diff_sql, diff_columns + ',customer_id') + ") 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.warehouse_id,
                                a.supplier_id,
                                a.product_category_id,
                                a.product_set_id,
                                a.product_id,
                                a.ubd,
                                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
                                #{non_inspect_quantity}
                         FROM (
                          SELECT #{diff_columns}, 
                          product_category_id,
                          product_set_id,
                          SUM(existing_quantity) AS existing_quantity, 
                          SUM(available_quantity) AS available_quantity FROM stocks as a
                          WHERE a.customer_id IS NULL 
                          GROUP BY a.product_id, a.ubd, a.warehouse_id, a.supplier_id
                         ) AS a 
                         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})
                    ) AS b "
      return str_cols, str_tab, str_vals
    end
    
    def set_target_date_to_where(tab, params, str_where)
    end
  end
end