hahaha

gistfile1.rb

def self.search(employee_params, query_options_params, checked_fields)
    sql = ''
    date_of_employement_from = ''
    date_of_employement_to = ''
    date_of_birth_from = ''
    date_of_birth_to = ''
    employee_params.each do |key, value|

      if (key.start_with?('employee_') && is_checked(key, checked_fields))
        column = key.gsub('employee_','')
        if column=='position' || column == 'category' || column == 'department'
          query_field = 'name'
        elsif column == 'date_of_employement_from' || column == 'date_of_employement_to'
          query_field = 'created_at'
        elsif column == 'age_from' || column == 'age_to'
          query_field = 'date_of_birth'
        elsif column == 'country' || column == 'state' || column == 'lga'
          query_field = column+"_id"
        else
          query_field = column
        end

        if value.present? && (Employee.column_names.include?(query_field) || Department.column_names.include?(query_field) ||Position.column_names.include?(query_field) || Category.column_names.include?(column))
          option = query_options_params[key]
          if column == 'age_from'
            date_of_birth_from = (DateTime.now - value.to_i.years).to_time.strftime('%Y-%m-%d')
          elsif column == 'age_to'
            date_of_birth_to = (DateTime.now - value.to_i.years).to_time.strftime('%Y-%m-%d')
          elsif column == 'date_of_employement_from'
            date_of_employement_from = value.to_time.strftime('%Y-%m-%d')
          elsif column == 'date_of_employement_to'
            date_of_employement_to = value.to_time.strftime('%Y-%m-%d')
          end

          if sql.empty?
            if option == 'LIKE'
              if column == 'department'
                sql += "departments.#{query_field} LIKE '%#{value}%'"
              elsif column == 'position'
                sql += "positions.#{query_field} LIKE '%#{value}%'"
              elsif column == 'category'
                sql += "categories.#{query_field} LIKE '%#{value}%'"
              else
                sql += "#{query_field} LIKE '%#{value}%'"
              end
            elsif option == 'BEGIN WITH'
              if column == 'department'
                sql += "departments.#{query_field} LIKE '#{value}%'"
              elsif column == 'position'
                sql += "positions.#{query_field} LIKE '#{value}%'"
              elsif column == 'category'
                sql += "categories.#{query_field} LIKE '#{value}%'"
              else
                sql += "#{query_field} LIKE '#{value}%'"
              end
            elsif option == 'EQUAL'
              if value.kind_of?(Array)
                value = value.reject(&:empty?).to_s
                value = value.gsub("[","")
                value = value.gsub("]","")
                value = value.gsub("'","")
              end
              if  !value.empty?
                if column == 'department'
                  sql += "departments.id IN (#{value})"
                elsif column == 'position'
                  sql += "positions.id IN (#{value})"
                elsif column == 'category'
                  sql += "categories.id IN (#{value})"
                elsif column == 'country'
                  sql += "employees.country_id IN (#{value})"
                elsif column == 'state'
                  sql += "employees.state_id IN (#{value})"
                elsif column == 'lga'
                  sql += "employees.lga_id IN (#{value})"
                elsif column == 'gender'
                  sql += "gender IN (#{value})"
                elsif column == 'blood_group'
                  sql += "blood_group IN (#{value})"
                elsif column == 'religion'
                  sql += "religion IN (#{value})"
                elsif column == 'marital_status'
                  sql += "marital_status IN (#{value})"
                else
                  sql += "#{query_field} = '#{value}'"
                end
              end
            end

          else
            if option == 'LIKE'
              if column == 'department'
                sql += " AND departments.#{query_field} LIKE '%#{value}%'"
              elsif column == 'position'
                sql += " AND positions.#{query_field} LIKE '%#{value}%'"
              elsif column == 'category'
                sql += " AND categories.#{query_field} LIKE '%#{value}%'"
              else
                sql += " AND #{query_field} LIKE '%#{value}%'"
              end
            elsif option == 'BEGIN WITH'
              if column == 'department'
                sql += " AND departments.#{query_field} LIKE '#{value}%'"
              elsif column == 'position'
                sql += " AND positions.#{query_field} LIKE '#{value}%'"
              elsif column == 'category'
                sql += " AND categories.#{query_field} LIKE '#{value}%'"
              else
                sql += " AND #{query_field} LIKE '#{value}%'"
              end

            elsif option == 'EQUAL'
              if value.kind_of?(Array)
                value = value.reject(&:empty?).to_s
                value = value.gsub("[","")
                value = value.gsub("]","")
                value = value.gsub("'","")
              end
              if !value.empty?
                if column == 'department'
                  sql += " AND departments.id IN (#{value})"
                elsif column == 'position'
                  sql += " AND positions.id IN (#{value})"
                elsif column == 'category'
                  sql += " AND categories.id IN (#{value})"
                elsif column == 'country'
                  sql += " AND employees.country_id IN (#{value})"
                elsif column == 'state'
                  sql += " AND employees.state_id IN (#{value})"
                elsif column == 'lga'
                  sql += " AND employees.lga_id IN (#{value})"
                elsif column == 'gender'
                  sql += " AND gender IN (#{value})"
                elsif column == 'blood_group'
                  sql += " AND blood_group IN (#{value})"
                elsif column == 'religion'
                  sql += " AND religion IN (#{value})"
                elsif column == 'marital_status'
                  sql += " AND marital_status IN (#{value})"
                else
                  sql += " AND #{query_field} = '#{value}'"
                end
              end
            end
          end

        end
      end
    end

    if date_of_employement_from.present? && date_of_employement_to.present?  && sql.empty?
      if(date_of_employement_from == date_of_employement_to)
        sql += "employees.created_at = '#{date_of_employement_from}'"
      else
        sql += "employees.created_at BETWEEN '#{date_of_employement_from}' AND '#{date_of_employement_to}'"
      end
    elsif date_of_employement_from.present? && !date_of_employement_to.present? && sql.empty?
      sql += "employees.created_at >= '#{date_of_employement_from}'"
    elsif date_of_employement_to.present? && !date_of_employement_from.present? && sql.empty?
      sql += "employees.created_at <= '#{date_of_employement_to}'"

    elsif date_of_employement_from.present? && date_of_employement_to.present? && !sql.empty?
      if(date_of_employement_from == date_of_employement_to)
        sql += " AND employees.created_at = '#{date_of_employement_from}'"
      else
        sql += " AND employees.created_at BETWEEN '#{date_of_employement_from}' AND '#{date_of_employement_to}'"
      end
    elsif date_of_employement_from.present? && !date_of_employement_to.present? && !sql.empty?
      sql += " AND employees.created_at >= '#{date_of_employement_from}'"
    elsif date_of_employement_to.present? && !date_of_employement_from.present? && !sql.empty?
      sql += " AND employees.created_at <= '#{date_of_employement_to}'"
    end

    if date_of_birth_from.present? && date_of_birth_to.present? && sql.empty?
      if(date_of_birth_from == date_of_birth_to)
        sql += "employees.date_of_birth LIKE '#{date_of_birth_to.to_time.strftime('%Y')}-%'"
      else
        sql += "employees.date_of_birth BETWEEN '#{date_of_birth_to}' AND '#{date_of_birth_from}'"
      end
    elsif date_of_birth_from.present? && !date_of_birth_to.present? && sql.empty?
      sql += "employees.date_of_birth >= '#{date_of_birth_from}'"
    elsif date_of_birth_to.present? && !date_of_birth_from.present? && sql.empty?
      sql += "employees.date_of_birth <= '#{date_of_birth_to}'"
    elsif date_of_birth_from.present? && date_of_birth_to.present? && !sql.empty?
      if(date_of_birth_from == date_of_birth_to)
        sql += " AND employees.date_of_birth LIKE '#{date_of_birth_from.to_time.strftime('%Y')}-%'"
      else
        sql += " AND employees.date_of_birth BETWEEN '#{date_of_birth_from}' AND '#{date_of_birth_to}'"
      end
    elsif date_of_birth_from.present? && !date_of_birth_to.present? && !sql.empty?
      sql += " AND employees.date_of_birth >= '#{date_of_birth_from}'"
    elsif date_of_birth_to.present? && !date_of_birth_from.present? && !sql.empty?
      sql += " AND employees.date_of_birth <= '#{date_of_birth_to}'"
    end
    sql
  end