class StatisticController < ApplicationController
  before_filter :check_access, :set_locale  
  def export
  end
  def operation
  end
######### Statistic oper compare ###################  
    def get_oper_stat_days
    	source_array=ActiveDatum\
    		.group_by_date\
    		.where(:operation_id => params[:foid]).order('date ASC')
    	render :text=> source_array.to_json
    end
    def get_oper_stat_graph_by_date
    	graph_array=ActiveDatum.global_filter(session[:user_id]).joins(:operation)\
    	.group_by_date.perfomance_data('count').time_select('avg')\
    	.select(
    		'COUNT(DISTINCT(login_id)) as uniq_user',
    		'COUNT(login_id) as count',
    	).where(:operation_id => params[:foid])  
        if $active_db == "sqlserver"
            graph_array=graph_array.like_compare("'#{params[:string]}'",'concat(\'%,\',convert(date,end_time,104),\'%,\')',0)
        else
            graph_array=graph_array.like_compare("'#{params[:string]}'",'concat(\'%,\',end_time::date,\'%,\')',0) 
        end
        gt_table_export(params[:export],graph_array,params[:headers]) && return
    	render :text=> 	{:data => format_array_for_table(graph_array,params[:order],params[:page],params[:offset]), :count => graph_array.length}.to_json
    end
    
	######### Statistic per day ###################

    def get_day_stat_activity_by_login
    	graph_array=ActiveDatum.global_filter(session[:user_id]).joins(:operation).group_by_date.perfomance_data('count')\
    	.where(' ? = cast(end_time as date)',params[:date]).select(
    		'COUNT(DISTINCT(login_id)) as users',
    		'COUNT(DISTINCT(operation_id)) as opers',
    		'coalesce(SUM(duration),0) as duration',
    		'coalesce(SUM(sys_time),0) as sys_time',
    		'coalesce(SUM(user_time),0) as user_time',
            'COUNT(login_id) as count'
    	)
    	if params[:loginid].to_i > 0
    		graph_array=graph_array.where(:login_id => params[:loginid])
    	end
    	render :text=> graph_array.to_json
    end
    def get_day_stat_full_activity
    	source_array=ActiveDatum.global_filter(session[:user_id]).group_by_date
    	render :text=> source_array.to_json
    end
    
    ######### Statistic day compare ############
     def get_day_compare_for_graph
        red_data=ActiveDatum.global_filter(session[:user_id]).where(' ? = cast(end_time as date)',params[:red_date])\
           .time_part_group('hour','end_time').select(
              'COUNT(operation_id) as count'
           ).select_LoginID(params[:red_user]).select_FOID(params[:red_oper])
        blue_data=ActiveDatum.global_filter(session[:user_id]).where(' ? = cast(end_time as date)',params[:blue_date])\
           .time_part_group('hour','end_time').select(
              'COUNT(operation_id) as count'
           ).select_LoginID(params[:blue_user]).select_FOID(params[:blue_oper])
        render :text=> {:red => red_data, :blue => blue_data}.to_json 
     end
     def get_day_compare_for_table
        output_array=[]
        data_array=[]
        source_array=ActiveDatum.global_filter(session[:user_id])
        type=params[:type]
        if type == "operation_id"
           main_id='operation_id'
           select_elements="\"operation_types\".\"name\" as type_name,(CASE WHEN display_name IS NULL OR display_name = '' THEN \"operations\".\"name\" ELSE display_name END) as oper_name"
           group_elements="\"operation_types\".\"name\",(CASE WHEN display_name IS NULL OR display_name = '' THEN \"operations\".\"name\" ELSE display_name END)"
           order_type='oper_name'
        else
           main_id='login_id'
           select_elements="(CASE WHEN full_name IS NULL  OR full_name = '' THEN \"logins\".\"name\" ELSE full_name END) as login_name"
           group_elements="(CASE WHEN full_name IS NULL  OR full_name = '' THEN \"logins\".\"name\" ELSE full_name END)"
           order_type='login_name'
        end
        colors=["red","blue"]
        opers_blue=[]
        opers_red=[]
        colors.each do |item|
           oper=source_array.where('? = cast(end_time as date)',params["#{item}_date"])\
              .group(main_id,select_elements).select(main_id,select_elements).joins(:login).joins(:operation => :operation_type).order(%Q|COUNT("#{type}") #{params[:order].split(';')[1]}|)\
              .select_LoginID(params["#{item}_user"]).select_FOID(params["#{item}_oper"])
           if item == "red"
              opers_red=oper
           else
              opers_blue=oper
           end
        end   
        if params[:order].split(';')[0] == 'red_count'
           opers=(opers_red+opers_blue)
        elsif params[:order].split(';')[0] == 'blue_count'
           opers=(opers_blue+opers_red)
        elsif params[:order].split(';')[0] == 'oper_name'
           opers=source_array.where('? = cast(end_time as date) OR ? = cast(end_time as date)',params[:blue_date],params[:red_date])\
              .group(main_id,group_elements).select(main_id,select_elements).joins(:login).joins(:operation => :operation_type)\
              .select_LoginID(params[:red_user]).select_FOID(params[:red_oper]).select_LoginID(params[:blue_user]).select_FOID(params[:blue_oper])
           opers=opers.order(%Q|#{order_type} #{params[:order].split(';')[1]}|)
        end
        opers=opers.as_json.uniq
        red_array=[]
        blue_array=[]
        colors.each do |element|
           count=source_array.where(' ? = cast(end_time as date)',params["#{element}_date"])\
              .group(main_id).select(main_id,%Q|COUNT(#{type}) as count|).select_LoginID(params["#{element}_user"]).select_FOID(params["#{element}_oper"])
           if element == "red"
              red_array=count.as_json
           else 
              blue_array=count.as_json
           end
           puts '!!!!!!!!!'
           puts count
        end
        count={'red' => red_array, 'blue' => blue_array}
        #puts red_array
        puts count
        opers.each do |item|
           blue_count=0
           red_count=0
           colors.each do |element|
              i=0
              while i < count[element].length do 
                 if count[element][i][type] == item[type]
                    if element == "red"
                       red_count=count[element][i]["count"]
                    else
                       blue_count=count[element][i]["count"]
                    end 
                    i=count[element].length
                 end
                 i+=1
              end
           end   
           if red_count > 0 || blue_count > 0
           if type == "operation_id"
              data_array.push({:oper_name => "#{item["oper_name"]} (#{item["type_name"]})",:id => item["operation_id"],:red => red_count,:blue => blue_count})
           else
              data_array.push({:oper_name => item["login_name"],:id => item["login_id"],:red => red_count,:blue => blue_count})
           end
        end
     end
     page_offset=(params[:page].to_f-1)*params[:offset].to_f
     count_oper=data_array.length
     data_array=data_array[page_offset..(page_offset+params[:offset].to_f)]
     #data_array=data_array.offset(page_offset).take(params[:offset].to_f)
     output_array.push({:data => data_array, :count => count_oper})
     gt_table_export(params[:export],data_array,params[:headers]) && return
     render :text=>{:data => data_array, :count => count_oper}.to_json(:except => [ :created_at, :updated_at ])
     end
  	def get_day_compare_stat_for_day
        output_array=[]
        source_array=ActiveDatum.global_filter(session[:user_id])
        red_data=source_array.where(' ? = cast(end_time as date)',params[:red_date])\
           .select(
              'COUNT(operation_id) as count',
              'COUNT(DISTINCT(operation_id)) as uniq_oper',
              'COUNT(DISTINCT(login_id)) as users'
           ).select_LoginID(params[:red_user]).select_FOID(params[:red_oper])
        blue_data=source_array.where(' ? = cast(end_time as date)',params[:blue_date])\
           .select(
              'COUNT(operation_id) as count',
              'COUNT(DISTINCT(operation_id)) as uniq_oper',
              'COUNT(DISTINCT(login_id)) as users'
           ).select_LoginID(params[:blue_user]).select_FOID(params[:blue_oper])

        render :text=> {:red => red_data, :blue => blue_data}.to_json(:except => [ :created_at, :updated_at ]) 
     end
     ####################### Export form ###########################
 	def stat_export_main
 		head=[]
 		body=[]
        active_db=Rails.configuration.database_configuration[Rails.env]["adapter"]
 		source_array=ActiveDatum.global_filter(session[:user_id]).check_period(params[:start_time],params[:end_time])
 		data=source_array.joins(:operation => :operation_type).joins(:login => :office)
 		if params[:oper_string].mb_chars.length > 0
 			if  params[:oper_inverse].to_i == 1
 				data=data.like_compare("'#{params[:oper_string]}'",'concat(\'%,\',operation_id,\'%,\')',1)
 			else
 				data=data.like_compare("'#{params[:oper_string]}'",'concat(\'%,\',operation_id,\'%,\')',0)
 			end
			
 		end 
 		if params[:login_string].mb_chars.length > 0
 			if  params[:login_inverse].to_i == 1
 				data=data.like_compare("'#{params[:login_string]}'",'concat(\'%,\',login_id,\'%,\')',1)
 			else
 				data=data.like_compare("'#{params[:login_string]}'",'concat(\'%,\',login_id,\'%,\')',0)
 			end
 		end 
 		if params[:office_string].mb_chars.length > 0
 			if  params[:office_inverse].to_i == 1
 				data=data.like_compare("'#{params[:office_string]}'",'concat(\'%,\',office_id,\'%,\')',1)
 			else
 				data=data.like_compare("'#{params[:office_string]}'",'concat(\'%,\',office_id,\'%,\')',0)
 			end
			
 		end 
 		group_select=0
 		if params[:group_oper].to_i == 1
 			data=data.operation_name_group.group(:operation_id,'"operation_types"."name"')
 			data=data.operation_name('oper_name').select(
 				:operation_id,
 				'"operation_types"."name" as type_name',
 				'AVG(time_avg) as avg_duration',
 				'AVG(user_time_avg) as avg_user_time',
 				'AVG(sys_time_avg) as avg_sys_time'
 			)
 			#head.push('Название операции');body.push('oper_name')
 			#head.push('Тип операции');body.push('type_name')
 			#head.push('Среднее время операции (секунды)');body.push('avg_duration')
 			#head.push('Среднее пользоватлеьское время операции (секунды)');body.push('avg_user_time')
 			#head.push('Среднее системное время операции (секунды)');body.push('avg_sys_time')
 			group_select=1
			
 		end
 		if params[:group_user].to_i == 1
 			data=data.group("(CASE WHEN full_name IS NULL  OR full_name = '' THEN \"logins\".\"name\" ELSE full_name END)",:login_id)
 			data=data.select(:login_id).login_name('login_name')
 			data=data.group('"offices"."name"',:office_id)
 			data=data.select('"offices"."name" as office_name',:office_id)
 			#head.push('Сотрудник');body.push('login_name')
 			group_select=1
 		end 
 		if params[:group_office].to_i == 1
 			data=data.group('"offices"."name"',:office_id)
 			data=data.select('"offices"."name" as office_name',:office_id)
 			#head.push('Филиал');body.push('office_name')
 			group_select=1
 		end 
 		if params[:group_hour].to_i == 1
 			data=data.time_part_group('hour','end_time')
 			#head.push('Час');body.push('Hour')
 			group_select=1
 		end
 		if params[:group_day].to_i == 1
 			data=data.time_part_group('day','end_time')
 			group_select=1
 			#head.push('День');body.push('Day')
 		end 	
 		if group_select == 1
 			data=data.select(
 				'COUNT(operation_id) as count',
 				'SUM(duration) as duration',
 				'SUM(user_time) as user_time',
 				'SUM(sys_time) as sys_time'
 			)
 			#head.push('Количество');body.push('count')
 			#head.push('Полное время (секунды)');body.push('duration')
 			#head.push('Пользовательское время (секунды)');body.push('user_time')
 			#head.push('Систеное время (секунды)');body.push('sys_time')
 		else
 			data=data.select(
                '"operations"."name" as oper_name',
                '"operation_types"."name" as type_name',
                '"logins"."name" as login_name',
                '"offices"."name" as office_name',
                'duration',
                'user_time',
                'sys_time',
                'end_time',
                'start_time',
 				'time_avg as avg_duration',
 				'user_time_avg as avg_user_time',
 				'sys_time_avg as avg_sys_time'
            )
 			#head.push('Название операции');body.push('oper_name')
 			#head.push('Тип операции');body.push('type_name')
 			#head.push('Филиал');body.push('office_name')
 			#head.push('Сотрудник');body.push('login_name')
 			#head.push('Полное время (секунды)');body.push('duration')
 			#head.push('Пользовательское время (секунды)');body.push('user_time')
 			#head.push('Систеное время (секунды)');body.push('sys_time')
 			#head.push('Время начала операции');body.push('start_time')
 			#head.push('Время окончания операции');body.push('end_time')
 		end
        
        columns=JSON.parse(params[:columns_list])
        columns.each do |column|
            if column['show'] == 1 && column['select'] == 1
                head.push(column['description']);body.push(column['name'])
            end
        end
        @title=t('.report')
        @period="#{t('.period')} #{t('.from')} #{params[:start_time].split(' ')[0]} #{t('.to')} #{params[:end_time].split(' ')[0]}"
 		@header=head
 		@body_data=body
 		@array=data
 		render xlsx: 'main_export_form.xlsx', filename: "report.xlsx"
 		#render :text=> data.to_json(:except => [ :created_at, :updated_at ])
 	end
    
    
    ############################ Flextera Log ####################################
    
    def get_flextera_logs
        flex_type=params[:type].to_i
        if flex_type == 1
            result=FlexteraPageFlowError.all
        else
            result=FlexteraProcContError.all
        end
        
        @active_db=Rails.configuration.database_configuration[Rails.env]["adapter"]
        if @active_db == "sqlserver"
            result=result.where(['CONVERT (date,date,104) >= CONVERT (date,:start_var,104) and CONVERT (date,date,104) <= CONVERT (date,:end_var,104)', { start_var: params[:start_time], end_var: params[:end_time]}])
        else
            result=result.where(['date::date >= :start_var::timestamp and date::date <= :end_var::timestamp', { start_var: params[:start_time], end_var: params[:end_time], format: "DD.MM.YYYY"  }])
        end       
        gt_table_export(params[:export],result,params[:headers]) && return
    	render :text=> 	{:data => format_array_for_table(result,params[:order],params[:page],params[:offset]), :count => result.length}.to_json
    end
end
