class CustomersUnrealizedListsController < CommLogistics::Base::Controller::ListController
  def initialize
    @pdf_cls = CustomersUnrealizedPdf
    @pdf_sort_column = 'customer_group_id'
  end
  
  def get_record_list
    @control_keys = 'customers_unrealized_lists'
    
    ss = CustomersUnrealizedSearch.new
    ss.table_alias = {'receivables' => 't'}
    # sortテーブルの置き換えに使用
    join_mcls = []
    ars = ss.search(FLAG_ON, Receivable, join_mcls, params)
    return ars
  end
  
  class CustomersUnrealizedSearch < Comm::Tool::SqlSearch
    def get_columns_and_tables(tab, join_lists, params, str_vals)
      str_cols = ' * '
      
      (y,m,d) = params[:end_target_date].split(/-/)
      this_month = Date.new(y.to_i,m.to_i,-1) #当月末 "2011-03-31"
      in_1month = (this_month << 1).end_of_month #1ヶ月前 "2011-02-28"
      in_2month = (this_month << 2).end_of_month #2ヶ月前 "2011-01-31"
      in_3month = (this_month << 3).end_of_month #3ヶ月前 "2010-12-31"
      
      str_tab = " FROM (SELECT 
                     r.customer_id,
                     r.customer_group_id,
                     r.payment_date,
                     r.cutoff_date,
                     r.total,
                     r.total_amount,
                     SUM(r.total) AS unrealized_receiving,
                     SUM(IF((r.payment_date > \"#{in_1month.to_s}\" AND r.payment_date <= \"#{this_month.to_s}\"), r.total,0)) AS this_receiving,
                     SUM(IF((r.payment_date > \"#{in_2month.to_s}\" AND r.payment_date <= \"#{in_1month.to_s}\"), r.total,0)) AS in_1month_receiving,
                     SUM(IF((r.payment_date > \"#{in_3month.to_s}\" AND r.payment_date <= \"#{in_2month.to_s}\"), r.total,0)) AS in_2month_receiving,
                     SUM(IF((r.payment_date <= \"#{in_3month.to_s}\"), r.total,0)) AS in_3month_receiving
                  FROM (SELECT 
                    r.customer_id,
                    r.customer_group_id,
                    r.cutoff_date,
                    r.payment_date,
                    r.total_price+r.total_duty+r.total_carry+r.total_carry_duty AS total,
                    v.total_amount
                  FROM receivables AS r LEFT JOIN 
                    (SELECT
                      customer_id, 
                      cutoff_date, 
                      SUM(total_amount) AS total_amount 
                    FROM receivings 
                    WHERE /*cutoff_date >= \'#{params[:start_target_date]}\' */ 
                      state_code=#{MCODE_STATUS2_COMP}
                      AND invalid_flag_code=#{MCODE_FLAG_OFF} 
                    GROUP BY customer_id, cutoff_date
                    ) AS v ON r.customer_id=v.customer_id AND r.cutoff_date=v.cutoff_date 
                  WHERE r.payment_date between \'#{params[:start_target_date]}\' AND \'#{params[:end_target_date]}\' 
                    AND ((v.total_amount IS NULL AND (r.total_price + r.total_duty+r.total_carry+r.total_carry_duty)!=0) OR v.total_amount!=(r.total_price + r.total_duty+r.total_carry+r.total_carry_duty)) 
                ) AS r GROUP BY r.customer_id
              ) AS t "
      return str_cols, str_tab, str_vals
    end
    #空実装
    def set_target_date_to_where(tab, params, str_where)
    end
  end
  
  # 未回収残高一覧
  class CustomersUnrealizedPdf < CommLogistics::Modules::Print::Controller::PdfList
    def initialize(params, mcls=nil)
      @pdf_basename = 'customer_unrealized_list'
      @total_columns = ['in_3month_receiving', 'in_2month_receiving', 'in_1month_receiving', 'unrealized_receiving', 'this_receiving']
      @total_title_column = 'customer_dn'
      super
    end
  end
end