#
#= case numbersコントローラー
# Authors:: Kazunori Shimizu
# Copyright:: Copyright (C) OSS K.K.  2013.
#--
# date        name                   note
# 2012.3.22   Kazunori Shimizu        新規作成
#-------------------------------------------------------------------------------
#++
class CaseNumbersController < Comm::BaseController::General
  #== コンストラクタ
  #-----------------------------------------------------------------#++
  def initialize
    @mcls = CaseNumber
  end
  
  def find_all
    ss = CaseNumberSearch.new
    ss.table_alias = {'case_numbers' => 't',
                      'sales' => 't'}
    return ss.search(FLAG_ON, Sale, [], params)
  end
  
  def count_all
    result = 0
    ret = Sale.connection.execute('SELECT FOUND_ROWS()')
    ret.each {|x| result = x.first }
    return result
  end
  
  class CaseNumberSearch < Comm::Tool::SqlSearch
    def get_columns_and_tables(tab, join_lists, params, str_vals)
      if params[:target_case].blank? 
        case_sql = " WHERE 1!=1"
      else
        case_sql = " WHERE a.target_case_id IN (#{params[:target_case]}) "
      end
      
      if params[:target_year].blank?
        year_ar = CaseNumber.find_by_sql("SELECT target_year FROM master_app_production.case_numbers GROUP BY target_year order by target_year DESC limit 1")
        unless year_ar.blank?
          params[:target_year]=year_ar[0].target_year
          params[:sales_target_year]=year_ar[0].target_year
        end
      end
      
      if params[:target_year].blank? || params[:target_year]=="すべて"
        year_sql = ""
      else
        year_sql = " AND a.target_year=#{params[:target_year]} "
      end
      
      if params[:sales_target_year].blank?
        sales_target = " AND 1!=1"
      else
        sales_target = " AND target_date BETWEEN '#{params[:sales_target_year]}-01-01' AND '#{params[:sales_target_year]}-12-31' "
      end
      
      if params[:product_set_id].blank?
        sales_product = " AND 1!=1 "
      else 
        sales_product = " AND product_set_id in (#{params[:product_set_id]}) "
      end
      
      str_cols = ' * '
      str_tab =  " FROM ( SELECT 
                            a.*,
                            b.user_position_id AS current_user_position_id,
                            b.sales_area_id AS current_sales_area_id,
                            c.global_area_id AS current_global_area_id,
                            d.customer_id AS current_customer_id,
                            IFNULL(e.sales_quantity,0) AS sales_quantity,
                            ROUND(IFNULL(e.sales_quantity,0) / IFNULL(a.case_quantity,0) * 100, 0) AS share_rate
                          FROM master_app_production.case_numbers AS a 
                          LEFT JOIN master_app_production.warehouses AS b ON a.warehouse_id=b.id
                          LEFT JOIN master_app_production.sales_areas AS c ON b.sales_area_id=c.id
                          LEFT JOIN (SELECT warehouse_id, customer_id FROM master_app_production.warehouses_customers GROUP BY warehouse_id) AS d ON a.warehouse_id=d.warehouse_id
                          LEFT JOIN ( 
                            SELECT 
                              warehouse_id, 
                              SUM(quantity) AS sales_quantity
                            FROM sale_details AS sd LEFT JOIN sales AS s ON sd.sale_id=s.id
                            WHERE state_code=#{MCODE_STATUS2_COMP} AND invalid_flag_code=#{MCODE_FLAG_OFF} #{sales_target} #{sales_product}
                            GROUP BY warehouse_id
                          ) AS e ON a.warehouse_id=e.warehouse_id
                          #{case_sql} #{year_sql}
                        ) AS t "
                          
      return str_cols, str_tab, str_vals
    end
  end
end