class CustomersProfitListsController < CommLogistics::Base::Controller::ListController
  def get_record_list
    @control_keys = 'customers_profit_lists'
    
    ss = CustomersProfitSearch.new
    ss.table_alias = {'sales' => 'a'}
    # sortテーブルの置き換えに使用
    join_mcls = []
    ars = ss.search(FLAG_ON, Sale, join_mcls, params)
    return ars
  end
  
  class CustomersProfitSearch < Comm::Tool::SqlSearch
    def get_columns_and_tables(tab, join_lists, params, str_vals)
      str_cols = ' * '
      (y,m,d) = params[:start_target_date].split(/-/)
      start_target_year = y + '-01-01'
      end_target_year = params[:end_target_date]
      #消費税も含む。売り上げ伝票がカテゴリごとに分別されていることが前提。seq_number=1のときだけ、消費税をカウントしている。
      str_tab = " FROM (SELECT IFNULL(m.total_quantity,0) AS monthly_quantity,
                               IFNULL(m.total_price,0) AS monthly_price,
                               IFNULL(m.total_invoice,0) AS monthly_invoice,
                               IFNULL(TRUNCATE((IFNULL(m.total_price,0)-IFNULL(m.total_invoice,0))/(IFNULL(m.total_price,0)) * 100 + 0.5,0),0) AS monthly_rate,
                               IFNULL(y.total_quantity,0) AS yearly_quantity,
                               IFNULL(y.total_price,0) AS yearly_price,
                               IFNULL(y.total_invoice,0) AS yearly_invoice,
                               IFNULL(TRUNCATE((IFNULL(y.total_price,0)-IFNULL(y.total_invoice,0))/(IFNULL(y.total_price,0)) * 100 + 0.5,0),0) AS yearly_rate,
                               c.customer_id,
                               c.customer_group_id 
       FROM (SELECT id AS customer_id, charge_customer_group_id AS customer_group_id, disp_name, e_name FROM master_app_production.customers WHERE invalid_flag_code=#{MCODE_FLAG_OFF}) AS c
       LEFT JOIN ( SELECT
            s.customer_id,
            s.charge_customer_group_id,
            SUM(sd.quantity) AS total_quantity,
            SUM(IFNULL(sd.quantity,1) * IF(s.duty_type_code=#{MCODE_DUTY_TYPE_CODE_ON} AND s.price_duty_type_code=#{MCODE_PRICE_DUTY_TYPE_INC}, TRUNCATE(sd.price*100/(100+s.duty_rate) + 0.5,0), sd.price) ) AS total_price,
            SUM(IF(ps.price_unique_code=#{MCODE_PRICE_UNIQUE_PRODUCT_SET}, ps.cost_price, p.cost_price) * IFNULL(sd.quantity,0)) AS total_invoice
         FROM sale_details AS sd 
            LEFT JOIN sales AS s ON sd.sale_id=s.id
            LEFT JOIN master_app_production.product_sets AS ps ON sd.product_set_id=ps.id
            LEFT JOIN master_app_production.products AS p ON sd.product_id=p.id
          WHERE s.invalid_flag_code=#{MCODE_FLAG_OFF}
            AND s.state_code=#{MCODE_STATUS2_COMP}
            AND s.target_date BETWEEN \'#{params[:start_target_date]}\' AND \'#{params[:end_target_date]}\'
            AND sd.product_category_id IN (#{params[:product_category_id]})
            AND (p.trade_type_code = #{MCODE_TRADE_TYPE_SALE} OR sd.product_id IS NULL)
          GROUP BY s.customer_id
        ) AS m ON c.customer_id=m.customer_id
        LEFT JOIN ( SELECT
            s.customer_id,
            s.charge_customer_group_id,
            SUM(sd.quantity) AS total_quantity,
            SUM(IFNULL(sd.quantity,1) * IF(s.duty_type_code=#{MCODE_DUTY_TYPE_CODE_ON} AND s.price_duty_type_code=#{MCODE_PRICE_DUTY_TYPE_INC}, TRUNCATE(sd.price*100/(100+s.duty_rate) + 0.5,0), sd.price) ) AS total_price,
            SUM(IF(ps.price_unique_code=#{MCODE_PRICE_UNIQUE_PRODUCT_SET}, ps.cost_price, p.cost_price) * IFNULL(sd.quantity,0)) AS total_invoice
          FROM sale_details AS sd 
            LEFT JOIN sales AS s ON sd.sale_id=s.id
            LEFT JOIN master_app_production.product_sets AS ps ON sd.product_set_id=ps.id
            LEFT JOIN master_app_production.products AS p ON sd.product_id=p.id
          WHERE s.invalid_flag_code=#{MCODE_FLAG_OFF}
            AND s.state_code=#{MCODE_STATUS2_COMP}
            AND s.target_date BETWEEN \'#{start_target_year}\' AND \'#{end_target_year}\'
            AND sd.product_category_id IN (#{params[:product_category_id]})
            AND (p.trade_type_code = #{MCODE_TRADE_TYPE_SALE} OR sd.product_id IS NULL)
          GROUP BY s.customer_id
        ) AS y ON c.customer_id=y.customer_id ) AS a"
      return str_cols, str_tab, str_vals
    end
    #空実装
    def set_target_date_to_where(tab, params, str_where)
    end
  end
end