class ActiveDatum < ActiveRecord::Base
    belongs_to :login
    belongs_to :operation
    belongs_to :operation_server
    

    
    @active_db=Rails.configuration.database_configuration[Rails.env]["adapter"]
    def self.check_period(start_time,end_time)
        if @active_db == "sqlserver"
            result=self.where(['CONVERT (date,end_time,104) >= CONVERT (date,:start_var,104) and CONVERT (date,end_time,104) <= CONVERT (date,:end_var,104)', { start_var: start_time, end_var: end_time}])
        else
            result=self.where(['end_time::date >= :start_var::timestamp and end_time::date <= :end_var::timestamp', { start_var: start_time, end_var: end_time, format: "DD.MM.YYYY"  }])
        end
        return result
    end
    def self.login_name(output_name)
	    result=self.select(
            "(CASE WHEN full_name IS NULL  OR full_name = '' THEN \"logins\".\"name\" ELSE full_name END) as #{output_name}"
        )
	    return result
    end
	def self.time_select(type)
		if type == 'avg'
			result=self.select(
					'AVG(duration) as duration',
					'AVG(user_time) as user_time',
					'AVG(sys_time) as sys_time'
			)
  	elsif type == 'all'
  			result=self.select(
  					'SUM(duration) as duration_sum',
  					'SUM(user_time) as user_time_sum',
  					'SUM(sys_time) as sys_time_sum'
  			)
		end
		return result
	end
	def self.perfomance_data(type)
		if type == 'count'
			result=self.select(
							'coalesce(SUM(CASE WHEN (user_time/user_time_avg) <= 1 THEN 1 ELSE 0 END),0) as green',
							'coalesce(SUM(CASE WHEN (user_time/user_time_avg) > 1 and (user_time/user_time_avg) <= kpi THEN 1 ELSE 0 END),0) as yellow',
							'coalesce(SUM(CASE WHEN (user_time/user_time_avg) > kpi THEN 1 ELSE 0 END),0) as red'
						)
		elsif type == 'type'
			result=self\
			.select(
				'AVG(CASE 	WHEN (user_time/user_time_avg) <= 1 THEN 1 
						WHEN (user_time/user_time_avg) > 1 and (user_time/user_time_avg) <= kpi THEN 2
						ELSE 3
				END) as status',
                
			)\
            .group(
				'CASE 	WHEN (user_time/user_time_avg) <= 1 THEN 1 
						WHEN (user_time/user_time_avg) > 1 and (user_time/user_time_avg) <= kpi THEN 2
						ELSE 3
				END'
            )
		end
		return result
	end
	def self.group_by_date
    if $active_db == "sqlserver"
	      result=self.group('cast(end_time as date)').select('cast(end_time as date) as date')
    else
  		  result=self.group('end_time::date').select('(end_time::date) as date')
    end
		return result
	end
    def self.time_part_group(type,field)
      if $active_db == "sqlserver"
        if type == "hour"
    			result=self.select("DATEPART(hh,#{field}) as hour").group("DATEPART(hh,#{field})")
        elsif type == "day week"
          result=self.select("DATEPART(dw,#{field}) as dow").group("DATEPART(dw,#{field})")
        elsif type == "day"
          result=self.select("DATEPART(dd,#{field}) as day").group("DATEPART(dd,#{field})")
        elsif type == "week"
          result=self.select("DATEPART(ww,#{field}) as week").group("DATEPART(ww,#{field})")
        elsif type == "month"
          result=self.select("DATEPART(mm,#{field}) as month").group("DATEPART(mm,#{field})")
        elsif type == "year"
          result=self.select("DATEPART(yy,#{field}) as year").group("DATEPART(yy,#{field})")  
        end
      else
        if type == "hour"
    			result=self.select("date_part('hour'::text,#{field}) as hour").group("date_part('hour'::text,#{field})")
        elsif type == "day week"
          result=self.select("EXTRACT(DOW FROM #{field}) as dow").group("EXTRACT(DOW FROM #{field})")
        elsif type == "day"
          result=self.select("date_part('day'::text,#{field})as day").group("date_part('day'::text,#{field})")
        elsif type == "week"
          result=self.select("date_part('week'::text,#{field}) as week").group("date_part('week'::text,#{field})")
        elsif type == "month"
          result=self.select("date_part('month'::text,#{field}) as month").group("date_part('month'::text,#{field})")
        elsif type == "year"
          result=self.select("date_part('year'::text,#{field}) as year").group("date_part('year'::text,#{field})")
        end
      end
      return result
    end
    def self.time_part_where(type,field,value,sign)
      if $active_db == "sqlserver"
        if type == "hour"
    			result=self.where("DATEPART(hh,#{field}) #{sign} #{value}")
        elsif type == "day week"
          result=self.where("DATEPART(dw,#{field}) #{sign} #{value}")
        elsif type == "day"
          result=self.where("DATEPART(dd,#{field}) #{sign} #{value}")
        elsif type == "week"
          result=self.where("DATEPART(ww,#{field}) #{sign} #{value}")
        elsif type == "month"
          result=self.where("DATEPART(mm,#{field}) #{sign} #{value}")
        elsif type == "year"
          result=self.where("DATEPART(yy,#{field}) #{sign} #{value}")  
        end
      else
        if type == "hour"
    			result=self.where("date_part('hour'::text,#{field})  #{sign} #{value}")
        elsif type == "day week"
          result=self.where("EXTRACT(DOW FROM #{field}) #{sign} #{value}")
        elsif type == "day"
          result=self.where("date_part('day'::text,#{field})  #{sign} #{value}")
        elsif type == "week"
          result=self.where("date_part('week'::text,#{field}) #{sign} #{value}")
        elsif type == "month"
          result=self.where("date_part('month'::text,#{field}) #{sign} #{value}")
        elsif type == "year"
          result=self.where("date_part('year'::text,#{field}) #{sign} #{value}")
        end
      end
      return result
    end
    def self.start_end_time()
      if $active_db == "sqlserver"
        result=self.select(	'CONVERT (varchar(25),start_time,126) as start_time','CONVERT (varchar(25),end_time,126) as end_time')
      else
        result=self.select(:start_time,:end_time)
      end
      return result
    end
    
    def self.like_compare(object,compared,invert)
      if $active_db == "sqlserver"
        if invert == 0;sign="LIKE";else;sign="NOT LIKE";end
        result=self.where("lower(#{object}) #{sign} lower(#{compared})")
      else
        if invert == 0;sign="=";else;sign="!=";end
        result=self.where("lower(#{object}) LIKE lower(#{compared}) #{sign} true")
      end
      return result   
    end
    def self.select_LoginID(id)
      if id.to_i > 0
        result=self.where(:login_id => id)
      else
        result=self.where('1=1')
      end  
      return result
    end 
    def self.select_FOID(id)
      if id.to_i > 0
        result=self.where(:operation_id => id)
      else
        result=self.where('1=1')
      end  
      return result
    end 
    def self.global_filter(id)
        @filter=GlobalFilter.where(:user_id => id).first
        @server_id=@filter.operation_server_id
        @source_id=@filter.operation_source_id
        @filter_id=@filter.custom_filter_id
        @result=self
        if @server_id == 0
            @result=@result.all
        else
            @result=@result.where(:operation_server_id => @server_id)
        end
        if @source_id == 0
            @result=@result.all
        else
            @result=@result.joins(:operation).where('"operations"."operation_source_id" = ?',@source_id)
        end
        if @filter_id == 0
            @result=@result.all
        else
            @result=@result\
            .where(:operation_id => OperationFilter.where(:custom_filter_id => @filter_id).select(:operation_id))\
            .where(:login_id => LoginFilter.where(:custom_filter_id => @filter_id).select(:login_id))
        end 
        return @result
    end
    
    
    
    
    
    def self.operation_name(output_name)
     result=self.select(
            "(CASE WHEN display_name IS NULL OR display_name = '' THEN \"operations\".\"name\" ELSE display_name END) as #{output_name}"
        )
     return result
    end
    def self.operation_name_group
        result=self.group(
               "(CASE WHEN display_name IS NULL OR display_name = '' THEN \"operations\".\"name\" ELSE display_name END)"
           )
        return result
    end
end
