class CustomersCashflowListsController < AccountingListController
  require 'config/site_config'
  include CommLogistics::Modules::ReceivableUtil
  include App::Const::SystemCode
  
  def initialize
    @multiple_id_cols = {'cutoff_day_code'=>true}
  end
  
  def init_results(customers)
    @results = []
    @indexes = {}
    customers.each_with_index do |ar, index|
      @results.push({'customer_id'=>ar.charge_customer_id,'customer_group_id'=>ar.charge_customer_group_id.to_i,'cutoff_day_code'=> "'#{ar.cutoff_day_code}'",
                     'before_1month_receiving'=>0, 'in_1month_receiving'=>0,'in_2month_receiving'=>0,'in_3month_receiving'=>0,'in_4month_receiving'=>0,'after_4month_receiving'=>0,'total'=>0,
                     'tmp_site'=>0, 'tmp_receiving'=>0
                     })
      @indexes.store(ar.charge_customer_id.to_i, index)
    end
  end
  #回収日が正しいかどうか調べる関数。
  def debug_date(ar)
    #デバッグ用-----
    group_total = Receivable.group_total(ar.customer_group_id.to_i, ar.cutoff_date)
    debug_payments = CustomerGroupPayment.customer_group_is(ar.customer_group_id.to_i).all
    debug_payment  = get_match_payment(debug_payments, group_total)
    debug_payment_date, debug_encachement_date = get_receivable_dates(debug_payment, ar.cutoff_date)
    logger.debug('>>>>>>LOG  group_total=>' + group_total.to_s + ':customer_id=>' + (ar.customer_id||'').to_s + ':group=>' + ar.customer_group_id.to_s + ':cutoff=>' + ar.cutoff_date.to_s + ':enc_date=>' + ar.encachement_date.to_s + ':right_date=>' + debug_encachement_date.to_s) if ar.customer_group_id.to_i==119
  end
  def set_record(ar, record, tmp_date)
    #未回収分
    #debug_date(ar)
    #if ar.customer_group_id==85
    #  require 'pp'
    #  p '----------------------------------'
    #  p 'r_id=>' + ar.id.to_s
    #  p 'date=>' + tmp_date.to_s
    #  p 'customer_id=>' + ar.customer_id.to_s
    #  p 'total=>' + ar.total.to_s
    #  p 'total_amount=>' + ar.total_amount.to_s
    #end
    
    if tmp_date <= @in_1month
      if ar.total.to_i != ar.total_amount.to_i
        record['before_1month_receiving'] += (ar.total.to_i-ar.total_amount.to_i)
        record['total'] += (ar.total.to_i-ar.total_amount.to_i)
        site = 0
      end
    else
      if tmp_date > @in_1month && tmp_date <= @in_2month then
        record['in_1month_receiving'] += ar.total.to_i
        site = 1
      elsif tmp_date > @in_2month && tmp_date <= @in_3month then
        record['in_2month_receiving'] += ar.total.to_i
        site = 2
      elsif tmp_date > @in_3month && tmp_date <= @in_4month then
        record['in_3month_receiving'] += ar.total.to_i
        site = 3
      elsif tmp_date > @in_4month && tmp_date <= @in_5month then
        record['in_4month_receiving'] += ar.total.to_i
        site = 4
      else 
        record['after_4month_receiving'] += ar.total.to_i
        site = 5
      end
      record['total'] += ar.total.to_i
    end
    return site
  end
  def get_worst_encachement_date(ar)
#    payments = CustomerGroupPayment.customer_group_is(ar.customer_group_id).all
#    payment  = get_match_payment(payments)
#    worst_payment_date, worst_encachement_date = get_receivable_dates(payment, ar.cutoff_date)
      payments = Customer.get_payments(ar.customer_id)
      payment = get_match_payment(payments)
      worst_payment_date, worst_encachement_date = get_receivable_dates(payment, ar.cutoff_date)
    return worst_encachement_date
  end
  def get_record_list
    @control_keys = 'customers_cashflow_lists'
    #init_results(CustomerGroup.set(MFIND_V).find(:all, :conditions=>['cutoff_date_code > ?', 0], :select=>['id AS customer_group_id, cutoff_date_code']))
    init_results(Customer.find(:all, :group=>"charge_customer_id", :conditions=>["charge_customer_id > 0 AND cutoff_type_code > 0 AND invalid_flag_code=?",MCODE_FLAG_OFF], :select => "charge_customer_id,charge_customer_group_id,cutoff_day_code"))
    #売掛金テーブルベースで1ヶ月間の売上を取得する
    pre_params = {:start_date => params[:start_target_date],
                  :end_date => params[:end_target_date],
                  :show_all => true}
    pre_ss = CustomersPreCashflowSearch.new
    pre_ars = pre_ss.search(FLAG_ON, Receivable, [], pre_params)
    
    (y,m,d) = params[:end_target_date].split(/-/)
    @in_1month = Date.new(y.to_i,m.to_i,-1)      #1ヶ月以内 "2011-03-31"
    @in_2month = (@in_1month >> 1).end_of_month  #2ヶ月前 "2011-04-30"
    @in_3month = (@in_1month >> 2).end_of_month  #3ヶ月前 "2011-05-31"
    @in_4month = (@in_1month >> 3).end_of_month  #4ヶ月前 "2010-06-30"
    @in_5month = (@in_1month >> 4).end_of_month  #5ヶ月前 "2010-07-31"
    
    pre_ars.each do |ar|
      index = @indexes[ar.customer_id.to_i]
      unless index.blank? 
        record = @results[index]
        #入金がされていたらそちらの日付を設定する。
        tmp_date = ar.encachement_date
        #相殺と、振込は、target_dateをみて、手形は、bill_dateをみる。
        #従って手形と、相殺、あるいは、手形と振込が混ざった、入金伝票があると、group byしているので、おそらく、detailのidが若い方がとられる。
        unless ar.credit_type_code.blank?
          if ar.credit_type_code.to_i == MCODE_CREDIT_TYPE_CASH || ar.credit_type_code.to_i == MCODE_CREDIT_TYPE_OFFSET then
            tmp_date = Date.parse(ar.target_date) unless ar.target_date.blank? 
          elsif ar.credit_type_code.to_i == MCODE_CREDIT_TYPE_BILL then
            tmp_date = Date.parse(ar.bill_date) unless ar.bill_date.blank?
          end
        end
        if tmp_date.is_a? Date 
          tmp_date = skip_holiday(tmp_date)
          set_record(ar, record, tmp_date)
        end
      end
    end
    #最後の対象月の売上（20日締めなどは翌月の売り掛けになるので、末締め以外のデータはここで集計する）
    last_params = {:start_date => @in_1month.beginning_of_month.to_s,
                   :end_date => params[:end_target_date],
                   :receivable_end_date => @in_2month.to_s,
                   :show_all => true}
    last_ss = CustomersLastMonthSalesSearch.new
    last_ars = last_ss.search(FLAG_ON, Receivable, [], last_params)
    last_ars.each do |ar|
      index = @indexes[ar.customer_id.to_i]
      unless index.blank? 
        record = @results[index]
        tmp_date = ar.encachement_date
        if ar.cutoff_date > Date.today
          worst_date = get_worst_encachement_date(ar)
          if tmp_date != worst_date ##ここは最悪のサイトを持ってくる。
            tmp_date = worst_date if $CASHFLOW_WORST_SITE ##ここに最悪のサイトを持ってくる
            record['tmp_receiving'] = ar.total.to_i
          end
        end
        if tmp_date.is_a? Date 
          tmp_date = skip_holiday(tmp_date)
          tmp_site = set_record(ar, record, tmp_date)
          record['tmp_site'] = tmp_site if record['tmp_receiving'] > 0 #暫定サイトの場合は、そのサイトを保存する
        end
      end
    end
    ss = CustomersCashflowSearch.new
    ss.table_alias = {'receivables' => 'r'}
    ss.union_array = @results
    ars = ss.search(FLAG_ON, Receivable, [], params)
    return ars
  end
  
  class CustomersPreCashflowSearch < Comm::Tool::SqlSearch
    def get_columns_and_tables(tab, join_lists, params, str_vals)
      str_cols = ' r.id,
                   r.customer_id,
                   r.customer_group_id,
                   r.cutoff_date,
                   r.payment_date,
                   r.encachement_date,
                   r.total,
                   v.total_amount,
                   v.target_date,
                   v.bill_date,
                   v.credit_type_code '
      str_tab = " FROM (SELECT 
                      r.id,
                      r.customer_id,
                      r.customer_group_id,
                      r.cutoff_date,
                      r.payment_date,
                      r.encachement_date,
                      r.total_price+r.total_duty+r.total_carry+r.total_carry_duty AS total
                    FROM receivables AS r
                    WHERE r.cutoff_date BETWEEN \'#{params[:start_date]}\' AND \'#{params[:end_date]}\'
                  ) AS r LEFT JOIN (SELECT
                      target_date,
                      customer_id,
                      customer_group_id, 
                      cutoff_date, 
                      SUM(total_amount) AS total_amount, 
                      rvd.bill_date,
                      rvd.credit_type_code
                    FROM receivings AS rv 
                    LEFT JOIN (select receiving_id, bill_date, credit_type_code FROM receiving_details WHERE credit_type_code !=#{MCODE_CREDIT_TYPE_FEE} GROUP BY receiving_id) AS rvd ON rvd.receiving_id=rv.id
                    WHERE cutoff_date BETWEEN \'#{params[:start_date]}\' AND \'#{params[:end_date]}\' 
                      AND state_code=#{MCODE_STATUS2_COMP}
                      AND invalid_flag_code=#{MCODE_FLAG_OFF} 
                    GROUP BY rv.customer_id, cutoff_date
                  ) AS v ON r.customer_id=v.customer_id AND r.cutoff_date=v.cutoff_date "
      return str_cols, str_tab, str_vals
      #2011-10-03相殺も、入金日付で置き換える。ため、下記の内容を上記に変更した。
      #LEFT JOIN (select receiving_id, bill_date, credit_type_code FROM receiving_details WHERE credit_type_code IN (#{MCODE_CREDIT_TYPE_BILL},#{MCODE_CREDIT_TYPE_CASH}) GROUP BY receiving_id) AS rvd ON rvd.receiving_id=rv.id
    end
  end
  #末締め以外で、たとえば20日締めだったら、20日〜末日までの売上ぶんを勘定に入れる。
  class CustomersLastMonthSalesSearch < Comm::Tool::SqlSearch
    def get_columns_and_tables(tab, join_lists, params, str_vals)
      #str_cols = ' r.customer_id,
      #             r.customer_group_id,
      #             r.cutoff_date,
      #             r.payment_date,
      #             r.encachement_date,
      #             SUM(s.total_price + s.total_duty) AS total,
      #             0 AS total_amount '
      str_cols = ' r.customer_id,
                   r.customer_group_id,
                   r.cutoff_date,
                   r.payment_date,
                   r.encachement_date,
                   SUM(IFNULL(s.total_price,0) + IFNULL(s.total_duty,0)+IFNULL(c.total_carry,0)+IFNULL(c.total_carry_duty,0)) AS total,
                   0 AS total_amount '
      #str_tab = " FROM receivable_details AS rd 
      #            LEFT JOIN receivables AS r ON rd.receivable_id=r.id
      #            LEFT JOIN sales AS s ON rd.trade_id=s.id
      #            WHERE r.cutoff_date > \'#{params[:end_date]}\' AND r.cutoff_date < \'#{params[:receivable_end_date]}\'
      #                AND s.target_date BETWEEN \'#{params[:start_date]}\' AND \'#{params[:end_date]}\'
      #            GROUP BY r.customer_group_id"
      str_tab = " FROM receivable_details AS rd 
                  LEFT JOIN receivables AS r ON rd.receivable_id=r.id
                  LEFT JOIN (SELECT * FROM sales WHERE target_date BETWEEN \'#{params[:start_date]}\' AND \'#{params[:end_date]}\') AS s ON rd.trade_id=s.id
                  LEFT JOIN (SELECT * FROM receivable_carry_remains WHERE target_date BETWEEN \'#{params[:start_date]}\' AND \'#{params[:end_date]}\') AS c ON rd.carry_id=c.id
                  WHERE r.cutoff_date > \'#{params[:end_date]}\' AND r.cutoff_date < \'#{params[:receivable_end_date]}\'
                      AND (s.id IS NOT NULL OR c.id IS NOT NULL)
                  GROUP BY r.customer_id"
      return str_cols, str_tab, str_vals
    end
  end
  
  class CustomersCashflowSearch < 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
end