class StocksCustomersListsController < CommLogistics::Base::Controller::ListController
  #
  # 棚卸原票
  #
  def get_record_list
    @control_keys = 'stocks_customers_lists'
    
    ss = StocksCustomersSearch.new
    ss.table_alias = {'stocks' => 't',
                      'customers' => 't'}
    join_mcls = [{:tab => 'customers',
                  :cols => Customer.column_names}]
    ars = ss.search(FLAG_ON, Stock, join_mcls, params)
    return ars
  end
  
  class StocksCustomersSearch < Comm::Tool::SqlSearch
    include CommLogistics::Modules::PartialSql
    def get_columns_and_tables(tab, join_lists, params, str_vals)
      str_cols = ' * '
      str_cols = ' * '
      diff_columns = 'customer_id, supplier_id'
      left_join_f = '((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 '
      
      str_tab =  " FROM ( SELECT 
                     IFNULL(a.customer_id, 0) AS id,
                     a.supplier_id,
                     a.customer_id,
                     c.charge_customer_group_id AS customer_group_id,
                     c.zip,
                     c.address_1,
                     c.address_2,
                     c.tel,
                     c.fax,
                     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
                   FROM (SELECT 
                            supplier_id, 
                            customer_id, 
                            SUM(existing_quantity) AS existing_quantity,
                            SUM(available_quantity) AS available_quantity,
                            SUM(short_shipped_quantity) AS short_shipped_quantity
                         FROM stocks WHERE supplier_id IN (#{params[:supplier_id]})
                         GROUP BY supplier_id, customer_id
                            ) AS a 
                   LEFT JOIN master_app_production.customers AS c ON a.customer_id=c.id
                   LEFT JOIN 
                      #{make_stock_diff(params[:end_target_date], diff_columns)}
                   AS f ON #{left_join_f} 
                 WHERE (IFNULL(a.existing_quantity,0) + IFNULL(f.diff_quantity,0)) !=0 AND a.supplier_id IN (#{params[:supplier_id]}) #{params[:stock_condition].blank? ? '' : params[:stock_condition]}) AS t "
      return str_cols, str_tab, str_vals
    end
    def set_target_date_to_where(tab, params, str_where)
    end
  end
end