#
#= SQL検索処理機能群
# Authors:: Sumiyo Yamamoto
# Copyright:: Copyright (C) OrbusNeich Medical K.K.  2010.
#--
# date        name                   note
# 2010.11.5   Kazunori Shimizu        新規作成
#-------------------------------------------------------------------------------
#++
module Comm
  module Tool
    #= SQL検索処理機能クラス
    # find_by_sqlによるSQL検索を制御する。
    #------------------------------------------------------------------------#++
    class SqlSearch
      require "config/site_config.rb"
      include Comm::Const::MasterCode
      include Comm::Const::DispName
      include App::Const::DispName
      include Comm::Const::MasterCode
      REGEX_ID = Regexp.new('(.+)_id$')
      REGEX_CODE = Regexp.new('(.+)_code$')
      REGEX_DISP_ADD_KEY = Regexp.new('(.+)disp_add_key\[([^\]]*)\]')
      MASTER_TABLES = Comm::BaseModel::Master.connection.tables
      #=== where句の後にそのまま使用
      attr_accessor :post_where_sentence
      #=== having句
      attr_accessor :having_sentence
      #=== フィルタのstart/end_target_dateの対象となるテーブル名(デフォルトのclsでない場合に使う)
      attr_accessor :date_table
      #=== テーブルのsqlでのエイリアスをHashで(カラム名のteble.column化などに使用される)
      attr_accessor :table_alias
      #=== テーブル名がないカラム(table.column化を避けたり)
      attr_accessor :non_table_columns
      #=== 集計している便宜上のカラム(where句ではなくhaving句がつかわれたりする)
      attr_accessor :calc_column
      #=== union文生成配列
      #[ {customer_id=>1, price=>1000}, {customer_id=2, price=>2000}]
      attr_accessor :union_array
      #=== 親テーブルカラム指定文字列
      attr_accessor :parent_table_columns
      #== SQL検索処理
      #-----------------------------------------------------------------#++
      def search(calc_rows_flag, cls, join_lists, params, find_flag = MFIND_A)
        join_lists = [] unless join_lists
        str_vals = []
        tab = cls.to_s.tableize
        
        #== カラムとテーブル
        str_col, str_tab, str_vals = get_columns_and_tables(tab, join_lists, params, str_vals)
        unless @calc_column.blank?
          @non_table_columns=[]
          @calc_column.each do |key, val|
            str_col << (',' + val + ' AS ' + key)
            @non_table_columns.push(key);
          end
        end
        #== 検索条件
        str_where, str_vals = get_where_sentence(tab, join_lists, params, str_vals, find_flag)
        #== order
        str_order, str_vals = get_order_sentence(tab, join_lists, params, str_vals)
        #== ページング（limit, offset）
        str_page, str_vals = get_limit_and_offset(tab, join_lists, params, str_vals)
        
        #== 実行
        str = 'SELECT'
        str << ' SQL_CALC_FOUND_ROWS ' if calc_rows_flag == FLAG_ON
        str << str_col
        str << str_tab
        str << str_where
        str << @post_where_sentence if @post_where_sentence
        str << @having_sentence if @having_sentence
        str << str_order
        str << str_page
        # プレースホルダを使用する要素がない場合に配列を渡すとエラーになる対策
        conditions = [str] + str_vals
        conditions = conditions.to_s if conditions.length == 1
        return cls.find_by_sql(conditions)
      end
      
      #== SELECT/FROMを作る
      def get_columns_and_tables(tab, join_lists, params, str_vals)
        str_col = @parent_table_columns || " #{tab}.* "
        str_tab = " FROM #{tab} "
        join_lists.each do |list|
          jtab = list[:tab]
          list[:cols].each do |jcol|
            # カラム編集
            str_col << ",#{jtab}.#{jcol}"
          end
          # テーブル編集
          str_tab << " JOIN #{jtab} ON #{tab}.id = #{jtab}.#{list[:join_id]}"
        end
        return str_col, str_tab, str_vals
      end
      
      #== WHEREを作る
      def get_where_sentence(tab, join_lists, params, str_vals, find_flag)
        str_where = ''
        # Filterオプション
        unless params[:filter].blank?
          tmp_str, tmp_vals = build_condition(params[:filter], tab, join_lists, 'AND', {:by_filter=>true})
          add_str_where(str_where, tmp_str)
          str_vals += tmp_vals
        end
        # Searchオプション
        unless params[:search].blank?
          tmp_str, tmp_vals = build_condition(params[:search], tab, join_lists, 'OR')
          unless tmp_str.blank?
            add_str_where(str_where, ' ( ' << tmp_str << ' ) ')
          end
          str_vals += tmp_vals
        end
        # invalid_flag_code
        if find_flag == MFIND_V
          date_table = @date_table ? @date_table : tab
          add_str_where(str_where, " #{date_table}.invalid_flag_code = ?")
          str_vals << MCODE_FLAG_OFF
        end
        set_target_date_to_where(tab, params, str_where)
        set_additional_where(str_where, params)
        return str_where, str_vals
      end
      #== WHEREの一番最後に設定される文(override用)
      def set_additional_where(str_where, params)
        true
      end
      #== WHEREのうち、target_dateを設定する
      def set_target_date_to_where(tab, params, str_where)
        date_table = @date_table ? @date_table : tab
        if params[:start_target_date]
          add_str_where(str_where, " #{date_table}.target_date >= \'#{params[:start_target_date]}\' ")
        end
        if params[:end_target_date]
          add_str_where(str_where, " \'#{params[:end_target_date]}\' >= #{date_table}.target_date")
        end
      end
      
      #== ORDER句を設定する
      def get_order_sentence(tab, join_lists, params, str_vals)
        str_order = ""
        if params[:sort]
          master_sort = nil
          cls = nil
          if REGEX_DISP_ADD_KEY =~ params[:sort]
            tmp = get_sql_col_name($1)
            master_sort = $2
          else
            tmp = get_sql_col_name(params[:sort])
            if defined?($SORT_MASTER_RECORD) 
              if $SORT_MASTER_RECORD.is_a?(String)
                master_sort = $SORT_MASTER_RECORD
              elsif $SORT_MASTER_RECORD.is_a?(Hash) && defined?($SORT_MASTER_RECORD[tmp])
                master_sort = $SORT_MASTER_RECORD[tmp]
              end
            end
          end
          if master_sort.is_a?(String)
            if REGEX_ID =~ tmp
              if cls = PARTICULAR_COL_INFO[tmp]
              # (_id)の前がテーブル名でない特殊なカラム名(アプリ固有)
              elsif cls = APP_PARTICULAR_COL_INFO[tmp]
              # (_id)の前がテーブル名であるカラム名
              else
                table_name = $1.pluralize
                #マスターでなければ返す
                if MASTER_TABLES.include?(table_name)
                  cls = table_name.classify
                end
              end
            elsif REGEX_CODE =~ tmp
              if cls = CODE_MASTER_TYPE_INFO[tmp]
              # code_type_id値取得（アプリ固有）
              elsif APP_CODE_MASTER_TYPE_INFO
                cls = APP_CODE_MASTER_TYPE_INFO[tmp]
              end
            end
          end
          
          field_name = add_tab_name(tab, tmp, join_lists)
          
          if cls.is_a?(String) && eval(cls).is_a?(Class)
            order = eval(cls).find(:all,:select => "id",:order => " #{master_sort} ASC").map(&:id)
            str_order << " ORDER BY FIELD(#{field_name}, #{order.join(',')} )"
          elsif cls.is_a?(Fixnum)
            order = CodeMaster.find(:all,:select => 'code_number',:conditions=>"code_type_id=#{cls}", :order => " #{master_sort} ASC ").map(&:code_number)
            str_order << " ORDER BY FIELD(#{field_name}, #{order.join(',')} )"
          else
            str_order << " ORDER BY #{field_name} "
          end
          
          sort_direction = ' ' + (params[:dir] || 'ASC').to_s.upcase
          str_order << sort_direction
        end
        return str_order, str_vals
      end
      
      #== LIMIT句を設定する
      def get_limit_and_offset(tab, join_lists, params, str_vals)
        str_page = ''
        if params[:show_all].blank?
          str_page << ' LIMIT ?,?'
          tmp = (params[:start] || 0)
          str_vals.push(tmp.to_i)
          tmp = (params[:limit] || 9223372036854775807)
          str_vals.push(tmp.to_i)
        end
        return str_page, str_vals
      end
      
      #== SQLカウント処理
      #-----------------------------------------------------------------#++
      def count(model=nil)
        model ||= ActiveRecord::Base
        result = 0
        ret = model.connection.execute('SELECT FOUND_ROWS()')
        ret.each {|x| result = x.first }
        return result
      end
      
      #== union文生成
      # * 特に本文中には使用せず、返すだけ
      # * 入力 (@union_arrayに) [{:customer_id=>1, :price=>1000}, {:customer_id=>2, :price=>2000}]
      # * 出力 select 1 as customer_id, 1000 as price union select 2, 2000
      # * 引数に配列を与えた場合は、その要素（だけ）をキーとして使用します
      #
      def generate_union(key_array=[])
        return '' if @union_array.empty?
        head_hash = @union_array.first
        # なければ先頭からキーを作る
        key_array = head_hash.keys if key_array.empty?
        sentence = []
        phrase = []
        
        key_array.each do |key|
          value = head_hash[key]
          if value && value.kind_of?(Hash) && value[:type]==:string
            r_value = value[:value] ? "\'" + value[:value].to_s + "\'" : "\"\""
          else
            r_value = value ? value : "\"\""
          end
          phrase << "#{r_value} AS #{key} "
        end
        sentence << 'SELECT ' + phrase.join(', ')
        
        # 2行目以降を取得してつなげる
        @union_array[1..-1].each do |h|
          phrase.clear
          key_array.each do |key|
            value = h[key]
            if value && value.kind_of?(Hash) && value[:type]==:string
              r_value = value[:value] ? "\'" + value[:value].to_s + "\'" : "\"\""
            else
              r_value = value ? value : "\"\""
            end
            phrase << r_value
          end
          sentence << 'UNION SELECT ' + phrase.join(', ')
        end
        return sentence.join(' ')
      end
      
    protected
      #== 検索条件文編集
      #-----------------------------------------------------------------#++
      def build_condition(request_lists, main_tab, join_lists, opt, opts={})
        result_str = ''
        result_vals = []
        having_sentence = ''
        
        # 設定値がないものは除去
        #request_lists.delete_if {|key, val| val.blank? }
        
        request_lists.each_value do |list|
          next if list.blank?
          # 検索条件のカラム名編集(col)
          col = get_sql_col_name(list[:field])
          col = add_tab_name(main_tab, col, join_lists)
          is_having = @calc_column.blank? ? false : @calc_column.include?(col)
          
          # 検索タイプ編集(type)
          #++ numeric, string, boolean, list, date, datetime  #--
          type = list[:data][:type].downcase
          
          # 検索値(vals)
          #vals = list[:data][:value].split(/','|[\s　]+/)
          vals = list[:data][:value].split(/\s*,\s*/)
          
          # 比較検索条件編集(comparison)
          #++ >, <, =, !=  #--
          comparison = get_sql_comparison(list[:data][:comparison], type)
          
          str = ''
          is_by_filter = opts.include?(:by_filter) ? true : false
          vals.each do |val|
            tmp_str, tmp_val = get_sql_string(type, col, val, comparison)
            
            if is_having
              if type == 'numeric' || type == 'list'
                tmp_having = tmp_str.sub(/\?/, tmp_val.to_s)
              elsif type == 'string'
                tmp_having = tmp_str.sub(/\?/, "\'"+tmp_val.to_s+"\'")
              else
                Rails.logger.debug("unknown having type: "+type.inspect)
                Rails.logger.debug("unknown col: "+col.inspect)
                Rails.logger.debug("unknown val: "+val.inspect)
                tmp_having = ''
              end
              Rails.logger.debug('having_sentence:'+having_sentence.inspect)
              if having_sentence == ''
                having_sentence = tmp_having
              else
                having_sentence += ' ' + opt + ' ' + tmp_having
              end
              next
            end
            
            if is_by_filter && val == "-2"
              tmp_str  = ' ' + col + ' is NULL OR ' + col + ' < ? '
              tmp_val = 0
            end
            
            unless tmp_str.empty?
              unless str.empty?
                str << ' ' + 'OR' + ' '
              end
              str << tmp_str
              result_vals << tmp_val
            end
          end
          
          unless str.empty?
            unless result_str.empty?
              result_str << ' '+ opt + ' '
            end
            result_str << '(' + str + ')'
          end
        end
        
        unless having_sentence == ''
          if @having_sentence
            @having_sentence += ' AND ' + having_sentence + ' '
          else
            @having_sentence = ' HAVING ' + having_sentence + ' '
          end
        end
        return result_str, result_vals
      end

      #== 比較条件リクエスト形式→SQL形式変換
      #-----------------------------------------------------------------#++
      def get_sql_comparison(comp_str, type)
        result = ''
        
        case comp_str
        when 'gt'
          result = '>='
        when 'lt'
          result = '<='
        when 'eq'
          result = '='
        when 'ne'
          result = '!='
        end
        
        return result
      end

      #== カラム名に所属テーブル名付加
      #-----------------------------------------------------------------#++
      def add_tab_name(main_tab, col, join_lists)
        table_name = main_tab
        
        if @non_table_columns && @non_table_columns.include?(col)
          #そのまま
          table_name = ''
        else
          # join情報リストにあれば、joinテーブル名を設定
          #-- リストになければ初期値(主テーブル名)のまま  #++
          join_lists.each do |list|
            if list[:cols].index(col)
              table_name = list[:tab]
              break
            end
          end
          
          # エイリアスがあれば置き換え
          unless @table_alias.blank?
            @table_alias.each do |key, val|
              table_name = table_name.sub(/^#{key}$/, val)
            end
          end
        end
        
        if table_name.length > 0
          colname = "#{table_name}.#{col}"
        else
          colname = col
        end
        return colname
      end
      
      #== カラム名リクエスト形式→SQL形式変換
      #   ex1. product[product_name]→product_name
      #   ex2. product[product_categories.type]→product_categories.type
      #-----------------------------------------------------------------#++
      def get_sql_col_name(col)
        result = ''
        
        if col.blank?
          return result
        end
        
        result = col.downcase.sub(/(\A[^\[]*)\[([^\]]*)\]/,'\2')
        
        return result
      end

      #== sql文編集
      #-----------------------------------------------------------------#++
      def get_sql_string(type, col, val, comparison)
        result_str = ''
        result_val = ''
        
        case type
        #-- 数値    #++
        when 'numeric'
          unless comparison.empty?
            result_str << (col + ' ' + comparison + ' ?')
            result_val =  val.to_i
          else
            #検索box
            result_str << (col + ' LIKE ?')
            result_val = "%" + val + "%"
          end
        #-- 文字列    #++
        when 'string'
          result_str << (col + ' LIKE ?')
          result_val = "%" + val + "%"
          #result_val = val
        #-- boolean    #++
        when 'boolean'
          val = val.downcase
          if (val == 'true') || (val == 'false')
            result_str << (col + ' = ' + val)
          end
        #-- リスト    #++
        when 'list'
          result_str << (col + ' = ?')
          if val.to_i.to_s == val
            result_val = val.to_i
          else
            result_val = val
          end
        when 'mlist'
          result_str << (" FIND_IN_SET( ? , #{col}) ")
          if val.to_i.to_s == val
            result_val = val.to_i
          else
            result_val = val
          end
        #-- 日付    #++
        # あいまい検索の%はviewでついてます
        when 'date'
          unless comparison.empty?
            result_str << (col + ' ' + comparison + ' ?')
            result_val = val.to_s
          else
            #検索box
            result_str << (col + ' LIKE ?')
            result_val = val.to_s
          end
          
        #-- 日付時間    #++
        # あいまい検索の%はviewでついてます
        when 'datetime'
          unless comparison.empty?
            result_str << (col + ' ' + comparison + ' ?')
            result_val = val.to_s
          else
            #検索box
            result_str << (col + ' LIKE ?')
            result_val = val.to_s
          end
        end
        return result_str, result_val
      end

      #== where文追加
      #-----------------------------------------------------------------#++
      def add_str_where(str_where, add_str)
        if add_str.blank?
          return str_where
        end
        
        if str_where.blank?
          str_where << " WHERE #{add_str}"
        else
          str_where << " AND (#{add_str})"
        end
        
        return str_where
      end
      
      #==条件からSQLを取得
      #_params_::条件param
      #_opt_:: OR/AND
      # 戻り値:: SQL文
      #-----------------------------------------------------------------#++
      def sanitize_sql_for_conditions(params, opt)
        str, vals = build_condition(params, '', [], opt)
        return ActiveRecord::Base.__send__(:sanitize_sql_array, [str] + vals)
      end
    end
  end
end