class StocksOthersListsController < CommLogistics::Base::Controller::ListController
  def get_record_list
    
    @control_keys = 'stocks_others_lists'
    
    ss = StocksOthersSearch.new
    ss.table_alias = {'stocks' => 't',
                      'warehouses' => 't',
                      'sales_areas' => 't'}
    ars = ss.search(FLAG_ON, Stock, [], params)
    return ars
  end
  
  class StocksOthersSearch < Comm::Tool::SqlSearch
    include CommLogistics::Modules::PartialSql
    def get_columns_and_tables(tab, join_lists, params, str_vals)
      w_ids = Warehouse.own_ids
      str_cols = ' * '
      diff_columns = 'warehouse_id, customer_id, supplier_id, product_id, ubd'
      diff_quantity = "SUM(IFNULL(out_quantity,0)) - SUM(IFNULL(in_quantity,0)) AS diff_quantity,
                       SUM(IFNULL(out_s_quantity,0)) - SUM(IFNULL(in_s_quantity,0)) AS diff_s_quantity"
      str_tab =  " FROM (SELECT a.warehouse_id,
                                a.customer_id,
                                a.supplier_id,
                                b.user_position_id,
                                e.global_area_id,
                                b.sales_area_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.short_shipped_quantity,0) + IFNULL(f.diff_s_quantity,0) AS short_shipped_quantity
                          FROM (
                            SELECT #{diff_columns}, 
                              product_category_id,
                              product_set_id,
                              SUM(existing_quantity) AS existing_quantity, 
                              SUM(short_shipped_quantity) AS short_shipped_quantity FROM stocks as a
                            WHERE a.customer_id IS NOT NULL 
                            GROUP BY a.product_id, a.ubd, a.warehouse_id, a.supplier_id, a.customer_id
                         ) AS a 
                          LEFT JOIN master_app_production.warehouses AS b ON a.warehouse_id=b.id
                          LEFT JOIN master_app_production.sales_areas AS e ON b.sales_area_id=e.id
                          LEFT JOIN
                            #{make_stock_diff(params[:end_target_date], diff_columns, diff_quantity, " WHERE diff.customer_id IS NOT NULL ")}
                          AS f ON a.warehouse_id=f.warehouse_id AND a.supplier_id=f.supplier_id AND a.customer_id=f.customer_id AND a.product_id=f.product_id AND a.ubd=f.ubd
                          WHERE (IFNULL(a.existing_quantity,0) + IFNULL(f.diff_quantity,0)) !=0
                           ) AS t"
      return str_cols, str_tab, str_vals
    end
    
    #空実装
    def set_target_date_to_where(tab, params, str_where)
    end
  end
end