class CustomersLedgersListsController < CommLogistics::Base::Controller::ListController
  def initialize
    @mcls = CustomerLedger
    @pdf_cls = CustomerLedgersPdf
    @pdf_sort_column = 'customer_id'
    @multiple_id_cols = {'cutoff_day_code'=>true}
  end
  
  def get_record_list
    @control_keys = 'customers_ledgers_lists'
    
    ss = CustomersLedgersSearch.new
    #ss.post_where_sentence = ' GROUP BY a.customer_id '
    #ss.date_table = 'a'
    #ss.non_table_columns = ['calc_black_price', 'calc_red_price', 'calc_adjust_price', 'calc_total_price', 'calc_total_duty', 'calc_cash', 'calc_bill', 'calc_offset', 'calc_fee', 'calc_total_credit', 'calc_total_carry', 'calc_pre_total']
    ss.table_alias = {'customer_ledgers' => 't',
                      'customers' => 't'}
    # sortテーブルの置き換えに使用
    #join_mcls = [{:tab => 'sales',
    #              :cols => Sale.column_names},
    #             {:tab => 'sales_areas',
    #              :cols => SalesArea.column_names}]
    join_mcls = [{:tab => 'customers',
                  :cols => Customer.column_names}]
    ars = ss.search(FLAG_ON, CustomerLedger, join_mcls, params)
    return ars
  end
  
  class CustomersLedgersSearch < Comm::Tool::SqlSearch
    def get_columns_and_tables(tab, join_lists, params, str_vals)
      str_cols = " * "
      str_tab =  " FROM ( SELECT 
                    a.id,
                    a.customer_id,
                    a.customer_group_id,
                    a.cutoff_day_code,
                    a.target_date,
                    a.total,
                    a.print_sheet_date,
                    a.created_at,
                    a.updated_at,
                    IF(a.target_date=\'#{params[:start_target_date]}\', a.black_price , 0) AS calc_black_price,
                    IF(a.target_date=\'#{params[:start_target_date]}\', a.red_price , 0) AS calc_red_price,
                    IF(a.target_date=\'#{params[:start_target_date]}\', a.adjust_price , 0) AS calc_adjust_price,
                    IF(a.target_date=\'#{params[:start_target_date]}\', a.total_price , 0) AS calc_total_price,
                    IF(a.target_date=\'#{params[:start_target_date]}\', a.total_duty , 0) AS calc_total_duty,
                    IF(a.target_date=\'#{params[:start_target_date]}\', a.cash , 0) AS calc_cash,
                    IF(a.target_date=\'#{params[:start_target_date]}\', a.bill , 0) AS calc_bill,
                    IF(a.target_date=\'#{params[:start_target_date]}\', a.offset , 0) AS calc_offset,
                    IF(a.target_date=\'#{params[:start_target_date]}\', a.fee , 0) AS calc_fee,
                    IF(a.target_date=\'#{params[:start_target_date]}\', a.total_credit , 0) AS calc_total_credit,
                    IF(a.target_date=\'#{params[:start_target_date]}\', a.total_carry , 0) AS calc_total_carry,
                    IF(a.target_date=\'#{params[:start_target_date]}\', a.total_carry_duty , 0) AS calc_total_carry_duty,
                    IF(a.target_date=\'#{params[:start_target_date]}\', a.pre_total , a.total) AS calc_pre_total,
                     c.kana_name AS kana_name
             FROM ( SELECT * 
              FROM (
                    SELECT * FROM customer_ledgers WHERE target_date <= \'#{params[:end_target_date]}\' ORDER BY target_date DESC
                   ) AS tmp_a 
             GROUP BY tmp_a.customer_id) AS a
             LEFT JOIN master_app_production.customers AS c ON a.customer_id=c.id 
             WHERE a.target_date=\'#{params[:start_target_date]}\' OR a.total != 0
           ) AS t "
      return str_cols, str_tab, str_vals
    end
    
    #空実装
    def set_target_date_to_where(tab, params, str_where)
    end
  end
  
  #得意先元帳一覧
  class CustomerLedgersPdf < CommLogistics::Modules::Print::Controller::PdfList
    def initialize(params, mcls=nil)
      @pdf_basename = 'customer_ledgers'
      @total_columns = ['calc_black_price','calc_red_price','calc_adjust_price','calc_total_price',
                        'calc_total_duty', 'calc_cash', 'calc_bill', 'calc_offset',
                        'calc_fee', 'calc_total_credit', 'calc_total_carry', 'calc_total_carry_duty', 'calc_pre_total', 'total']
      @total_title_column = 'customer_dn'
      @multiple_id_cols = {'cutoff_day_code'=>true}
      super
    end
  end
end