class CustomersReceivablesListsController < AccountingListController
  def initialize
    @pdf_cls = CustomersReceivables
    @pdf_sort_column = 'customer_group_id'
  end
  
  def get_record_list
    @control_keys = 'customers_receivables_lists'
    
    pre_params = {:start_receiving_date => params[:start_target_date],
                  :end_receiving_date => params[:end_target_date],
                  :show_all => true}
    
    pre_ss = CustomersPreReceivablesSearch.new
    pre_ars = pre_ss.search(FLAG_ON, CustomerLedger, [], pre_params)
    results, customers = pre_ars.index_hashfy([], 'customer_id')
    
    #手形による入金を取得する
    bill_ss = CustomersBillReceivingsSearch.new
    bill_ars = bill_ss.search(FLAG_ON, ReceivingDetail, [], pre_params)
    
    ##ここからデータをはめ込んでいく
    start_date = Date.parse(params[:start_target_date])
    end_date = Date.parse(params[:end_target_date])
    bill_ars.each do |ar|
      index = customers[ar.customer_id.to_i]
      bill_date = skip_holiday(ar.bill_date)
      if Date.parse(ar.target_date) < start_date &&  bill_date >= start_date then #前月末未決済
        results[index]['prev_undraft'] = results[index]['prev_undraft'].to_i + ar.amount.to_i
      end
      if bill_date >= start_date && bill_date <= end_date then #当月決裁手形
        results[index]['this_draft'] = results[index]['this_draft'].to_i + ar.amount.to_i
      end
    end
    
    results.each_with_index do |record,i|
      #当月未決済(this_undraft)=前月未決済(prev_undraft)+当月受取手形(this_indraft)-当月決済手形(this_draft)
      record['this_undraft']=record['prev_undraft'].to_i + record['this_indraft'].to_i - record['this_draft'].to_i
      #総債権残高(total_receivable)=当月未決済(this_undraft)+当月売掛残高(this_receivable)
      record['total_receivable']=record['this_receivable'].to_i + record['this_undraft']
      #すべて0の場合はレコードから削除
      if record['prev_undraft'].to_i==0 && record['this_indraft'].to_i==0 && record['this_draft'].to_i==0 && record['this_undraft'].to_i==0 && record['this_receivable'].to_i==0 && record['total_receivable'].to_i==0 then
        results[i] = nil
      end
    end
    results.compact!
    
    #ソートや検索などunion文で実行する
    ss = CustomersReceivablesSearch.new
    ss.table_alias = {'customer_ledgers' => 'r'}
    ss.union_array = results
    ars = ss.search(FLAG_ON, CustomerLedger, [], params)
    
    return ars
  end
  
  class CustomersPreReceivablesSearch < Comm::Tool::SqlSearch
    def get_columns_and_tables(tab, join_lists, params, str_vals)
      str_cols = " c.customer_id,
                   c.customer_group_id,
                   0 AS prev_undraft,
                   IFNULL(IF(l.target_date BETWEEN \"#{params[:start_receiving_date]}\" AND \"#{params[:end_receiving_date]}\",l.bill,0),0) AS this_indraft,
                   0 AS this_draft,
                   0 AS this_undraft,
                   IFNULL(l.total,0) AS this_receivable,
                   IFNULL(l.total,0) AS total_receivable "
                   
      str_tab = " FROM (SELECT 
                      charge_customer_id AS customer_id,
                      charge_customer_group_id AS customer_group_id
                    FROM master_app_production.customers 
                    WHERE charge_customer_id IS NOT NULL AND invalid_flag_code=#{MCODE_FLAG_OFF}
                    GROUP BY charge_customer_id
                  ) AS c LEFT JOIN (
                  SELECT * FROM ( SELECT
                        customer_id,
                        bill,
                        total,
                        target_date
                      FROM customer_ledgers
                      WHERE target_date <= \"#{params[:start_receiving_date]}\"
                      ORDER BY target_date DESC
                    ) AS tmp_l GROUP BY customer_id
                  ) AS l ON c.customer_id=l.customer_id "
      return str_cols, str_tab, str_vals
    end
  end
  
  class CustomersBillReceivingsSearch < Comm::Tool::SqlSearch
    def get_columns_and_tables(tab, join_lists, params, str_vals)
      str_cols = ' r.customer_id,
                   r.target_date,
                   rd.bill_date,
                   rd.amount '
      str_tab = " FROM receiving_details AS rd LEFT JOIN receivings AS r ON rd.receiving_id=r.id
                    WHERE r.target_date <= \"#{params[:end_receiving_date]}\"
                      AND rd.bill_date >= \"#{params[:start_receiving_date]}\"
                      AND rd.credit_type_code=#{MCODE_CREDIT_TYPE_BILL}
                      AND r.state_code=#{MCODE_STATUS2_COMP}
                      AND r.invalid_flag_code=#{MCODE_FLAG_OFF} "
      return str_cols, str_tab, str_vals
    end
  end
  
  class CustomersReceivablesSearch < Comm::Tool::SqlSearch
    def get_columns_and_tables(tab, join_lists, params, str_vals)
      str_cols = ' * '
      str_tab = " FROM (#{generate_union()}) AS r "
      return str_cols, str_tab, str_vals
    end
    #空実装
    def set_target_date_to_where(tab, params, str_where)
    end
  end
  
  # 債権残高一覧
  class CustomersReceivables  < CommLogistics::Modules::Print::Controller::PdfList
    def initialize(params, mcls=nil)
      @pdf_basename = 'customer_receivables_list'
      @total_columns = ['prev_undraft', 'this_indraft', 'this_draft', 'this_undraft', 
                        'this_receivable', 'total_receivable']
      @total_title_column = 'customer_dn'
      super
    end
  end
end