class SalesUsersListsController < CommLogistics::Base::Controller::ListController
  def get_record_list
    @disp_name_nil_str = 'その他'
    @control_keys = 'sales_users_lists'
    
    ss = SalesUsersSearch.new
    #ss.post_where_sentence = ' '
    #ss.date_table = 'b'
    #ss.non_table_columns = ['quantity', 'average_sales_price', 'total_sales_price']
    ss.table_alias = {'sale_details' => 't',
                      'sales' => 't'}
    # sortテーブルの置き換えに使用
    join_mcls = [{:tab => 'sales',
                  :cols => Sale.column_names},
                 {:tab => 'sales_areas',
                  :cols => SalesArea.column_names}]
    ars = ss.search(FLAG_ON, SaleDetail, join_mcls, params)
    return ars
  end
  
  class SalesUsersSearch < Comm::Tool::SqlSearch
    def get_columns_and_tables(tab, join_lists, params, str_vals)
      pci = params[:product_category_id]
      str_cols = ' * '
      str_tab =  " FROM ( SELECT 
                     b.sales_user_id,
                     b.user_position_id,
                     b.sales_area_id,
                     b.global_area_id,
                     SUM(IFNULL(quantity,0)) AS quantity,
                     SUM(IFNULL(quantity,1)*price)/SUM(IFNULL(quantity,0)) AS average_sales_price,
                     SUM(IFNULL(quantity,1)*price) AS total_sales_price
                   FROM sale_details AS a INNER JOIN (
                    sales AS b 
                   ) ON a.sale_id=b.id 
                   WHERE a.product_category_id IN (#{pci}) 
                     AND b.target_date BETWEEN \'#{params[:start_target_date]}\' AND \'#{params[:end_target_date]}\'
                     AND b.state_code=#{MCODE_STATUS2_COMP} 
                     AND b.invalid_flag_code=#{MCODE_FLAG_OFF}
                   GROUP BY b.user_position_id ) AS t "
      return str_cols, str_tab, str_vals
    end
    def set_target_date_to_where(tab, params, str_where)
    end
  end
end