class PrivateController < ApplicationController

  # Отдел
  $department = 'private'

  # Авторизация (файл: controllers/application.rb)
  before_filter :isAuthorization

  # Конструктор
  before_filter :construct

  def construct
  
    # Устанавливаем сессионную куку.
    cookies[:sys_dep] = { :value => $department }

    # Пункты меню
      $menu = 
        [
          {"url"=>"index", "title"=>"Сводная таблица"},
          {"url"=>"stats", "title"=>"Статистика"},
          {"url"=>"addReport", "title"=>"Добавить отчет"},
          {"url"=>"/works", "title"=>"Редактор работ"},
          {"url"=>"/measure", "title"=>"Редактор едениц измерения"}
        ]


      # Тип работ над объектом
      $category_info = [
            {"name" => "Подключение", 
             "price" => 550
            },
            {"name" => "Переподключение", 
             "price" => 350
            },
            {"name" => "Доп. работы", 
             "price" => 0
            }
          ]
      # Статусы
      $status = [{'name'=>'Выполнено'}, {"name"=>'Принято'}, {'name'=>'Отказано'}]

      # Сотрудники
      tmpObj = ApplicationHelper::UsersDB.new
      $employeesAll = tmpObj.getAllEmployees("*")
      $employeesGroup = tmpObj.getAllEmployees(session[:user_department])

      #$materials = ApplicationHelper::MaterialsDB.getMaterials


  end



  # ===========================================> START <======================================== #
	
	# СТРАНИЦЫ
	def index
		### PAGE INFO
		@page_title = 'Сводная таблица частных инсталляторов'
		@currPage = "index"



		# Получаем список сотрудников
    employeesNames = {}
    for val in $employeesAll
      employeesNames[val['empl_id']] = val['login']
    end
		

		# Получаем список всех работ
		tempWorks = WorksDoneDB.new
		allWorks = tempWorks.getAllWorks

		
		# ФИЛЬТРАЦИЯ
		time = Time.new
		sqlFIlterDepartment = "a.department='"+$department+"'"
		if(params[:month] && params[:month].to_i>0) then sqlFilterMonth = "AND MONTH(a.date)="+params['month'].to_s+"" else sqlFilterMonth = "AND MONTH(a.date)="+time.month.to_s+"" end
		if(params[:day] && params[:day].to_i>0) then sqlFilterDay = "AND DAY(a.date)="+params[:day].to_s else sqlFilterDay = '' end
		if(params[:year] && params[:year].to_i>0) then sqlFilterYear = "AND YEAR(a.date)="+params[:year].to_s else sqlFilterYear = '' end
		if(params[:empl] && params[:empl].to_i>0) then sqlFilterEmployee = "AND b.empl_id="+params[:empl].to_s else sqlFilterEmployee = '' end
		if(params[:worktype] && params[:worktype]!='') then sqlFilterWorktype = "AND a.category="+params[:worktype].to_s else sqlFilterWorktype = '' end
		
		
		# СОРТИРОВКА
		orderParams = ''
		if(params[:sdate]) 
			if(orderParams!='') then orderParams+=', ' end
			orderParams+='a.date '+params[:sdate]
		end
		if(params[:slogin]) 
			if(orderParams!='') then orderParams+=', ' end
			orderParams+='a.name '+params[:slogin]
		end
		if(params[:swtype]) 
			if(orderParams!='') then orderParams+=', ' end
			orderParams+='a.category '+params[:swtype]
		end
		if(params[:ssum]) 
			if(orderParams!='') then orderParams+=', ' end
			orderParams+='a.sum '+params[:ssum]
		end
		if(params[:sstatus]) 
			if(orderParams!='') then orderParams+=', ' end
			orderParams+='a.status '+params[:sstatus]
		end
		
		
		
		# Пoлучаем инфу
		allData = ReportsDB.find_by_sql("SELECT 
												a.*, 
												b.empl_id, 
												b.sum AS empl_sum, 
												c.work_id, 
												c.count AS work_count,
                        d.employee_name,
                        d.material_name,
                        d.count
												FROM reports AS a 
												LEFT JOIN done_employees AS b ON(b.report_id=a.id) 
												LEFT JOIN done_works AS c ON(c.report_id=a.id)
                        LEFT JOIN done_materials AS d ON(d.report_id=a.id)
												WHERE 
												"+sqlFIlterDepartment+"
												"+sqlFilterMonth+"
												"+sqlFilterDay+"
												"+sqlFilterYear+"
												"+sqlFilterEmployee+"
												"+sqlFilterWorktype+"
												ORDER BY a.id DESC
										")

		
		@result = {}
    
		for res in allData
			report_id = res['id'].to_i
				if(!@result[report_id])
					@result[report_id] = {}
				end
				
				@result[report_id]['name'] = res['name']
				@result[report_id]['date'] = res['date'].split('-').reverse.join(".")
				@result[report_id]['sum'] = res['sum'].to_i + $category_info[res['category'].to_i]['price'].to_i
				@result[report_id]['status'] = res['status']
				@result[report_id]['category'] = res['category']
				
				# ================================================================ Сохраняем сотрудников
				empl_name = employeesNames[res['empl_id'].to_i].to_s
				if(@result[report_id]['employees'])
					
					# Проверяем был ли уже сотрудник обработан для этого отчета
					check = false
					@result[report_id]['employees'].each {|empl| if(empl.has_value?(empl_name)) then check=true end }
					
					if(check==false) 
						@result[report_id]['employees'] += [{'name'=>empl_name, 'sum'=>res['empl_sum']}]
					end
				else
					@result[report_id]['employees'] = [{'name'=>employeesNames[res['empl_id'].to_i], 'sum'=>res['empl_sum']}]
				end

				# ================================================================ Сохраняем работы
				work_id = res['work_id'].to_i
				work_name = allWorks[work_id]['name'].to_s
				work_price = allWorks[work_id]['price'].to_s
				work_measure = allWorks[work_id]['measure'].to_s
				work_sum = res['work_count'].to_i*work_price.to_i
				if(@result[report_id]['works'])
					
					# Проверяем была ли уже работа обработана для этого отчета
					check = false
					@result[report_id]['works'].each {|wrk| if(wrk.has_value?(work_name)) then check=true end }
					
					if(check==false) 
						@result[report_id]['works'] += [{'name'=>work_name, 'count'=>res['work_count'], 'price'=>work_price, 'sum'=>work_sum, 'measure'=>work_measure}]
					end
				else
					@result[report_id]['works'] = [{'name'=>work_name, 'count'=>res['work_count'], 'price'=>work_price, 'sum'=>work_sum, 'measure'=>work_measure}]
				end

        # ================================================================ Сохраняем материалы
				if(@result[report_id]['materials']==nil)
          @result[report_id]['materials']=[{"employee"=>res['employee_name'], "name"=>res['material_name'], "count"=>res['count']}]
        else
          check = false
          @result[report_id]['materials'].each {|wrk| if(wrk.has_value?(res['material_name'])) then check=true end }
          if(check==false)
            @result[report_id]['materials']+=[{"employee"=>res['employee_name'], "name"=>res['material_name'], "count"=>res['count']}]
          end
        end


		end


	end

	def addReport
		# Page info
		@page_title = 'Добавить / Редактировать отчет'
		@currPage = "addReport"
		
		## INFO
		if(params[:id]) then @id = params[:id].to_i else @id = 0 end
		@name = ""
		@sum = ""
		@status = ""
		@category = 0
		@com_other_materials = ""
		@com_request_materials = ""
		@com_stay_work = ""
		@com_imperfection = ""
		@com_conflicts = ""

		## ADD INFO
		@work_cable = ReportsDB.find_by_sql("SELECT a.*, b.name AS measure_title FROM works AS a LEFT JOIN measures AS b ON(b.id=a.measure) WHERE a.section=0 ORDER BY a.sort ASC")
		@work_other = ReportsDB.find_by_sql("SELECT a.*, b.name AS measure_title FROM works AS a LEFT JOIN measures AS b ON(b.id=a.measure) WHERE a.section=1 ORDER BY a.sort ASC")

    # HTML шаблоны сотрудников
    tmp = ApplicationHelper::HTMLtemplates.new
    @HTMLemployees = tmp.getHtmlGroupEmployees($employeesGroup)
    @HTMLemployeesAll = tmp.getHtmlAllEmployeesWithGroups($employeesAll)
		
		#require "rexml/document"
		#tmp = XmlSimple.new
		#tmp.xml_in_string("http://intranet.rinet.net/local/calc/people_xml.php")
		#@empl_test = Xml.find(:first, :from=>"/local/calc/people_xml.php").people

		if(@id>0)
			@id = @id.to_s
			begin
				ReportsDB.find(@id)
			rescue	
				print 'Такого идентификатора - НЕТ.'
			else 	
				report = ReportsDB.find_by_sql("SELECT * FROM reports WHERE id="+@id+"")
				@name = report[0]['name']
				@date = report[0]['date']
				@sum = report[0]['sum']
				@status = report[0]['status']
				@category = report[0]['category']
				@com_other_materials = report[0]['com_other_materials']
				@com_request_materials = report[0]['com_request_materials']
				@com_stay_work = report[0]['com_stay_work']
				@com_imperfection = report[0]['com_imperfection']
				@com_conflicts = report[0]['com_conflicts']
				
				# Выполненные работы
				@works_done = [{}]
				works = WorksDoneDB.new
				@works_done = works.getWorks(@id)

				# Рабочая группа
				@employees_done = [{}]
				employees = EmployeesDoneDB.new
				@employees_done = employees.getEmployees(@id)

        # Потраченные материалы
        @materials = ""

			end
		end
	end


	def saveReport
		# Система отчетов
		
		@name = params[:title]
		@workDate = params[:date]
		
		@sum = params[:sum]
		@status = params[:status]
		@category = params[:category]
		com_other_materials = params[:com_other_materials]
		com_request_materials = params[:com_request_materials]
		com_stay_work = params[:com_stay_work]
		com_imperfection = params[:com_imperfection]
		com_conflicts = params[:com_conflicts]

		######################################################## Записываем в таблицу REPORTS
		if(params[:id].to_i>0)
			report_id = params[:id].to_i
			sv_report = ReportsDB.find(report_id)
		else
			report_id=0;
			sv_report = ReportsDB.new
		end
		# get
		sv_report.date = @workDate
		sv_report.name = @name
		sv_report.sum = @sum
		sv_report.status = @status
		sv_report.category = @category
		sv_report.department = $department
		sv_report.com_other_materials = com_other_materials
		sv_report.com_request_materials = com_request_materials
		sv_report.com_stay_work = com_stay_work
		sv_report.com_imperfection = com_imperfection
		sv_report.com_conflicts = com_conflicts
		
		
		# save
		sv_report.save
				
		####################################################### Получаем последний элемент или элемент редактирования
		if(report_id==0)
			last_element_db = ReportsDB.find_by_sql("SELECT id FROM reports ORDER BY id DESC LIMIT 1")
			report_id = last_element_db[0]['id']
      isMaterials_save = true
		else
			report_id = report_id
      isMaterials_save = false
		end

		
		######################################################## Сохраняем выполненные работы
		# get and save
		if(report_id.to_i>0)
			works = WorksDoneDB.new
			works.delWorks(report_id)
		end
		params.each {|key, value| 	
				key = "#{key}"
				value = "#{value}"
				
				if(key.index("work_") && value.to_i>0)
					work_id = key[5..-1]
					sv_work = WorksDoneDB.new
					sv_work.work_id = work_id
					sv_work.report_id = report_id
					sv_work.date_create_report = @workDate
					sv_work.count = value
					sv_work.department = $department
					sv_work.save	
				end
			}


		####################################################### Сохраняем потраченные материалы на склад (отписываем)
    if(isMaterials_save)
        params.each {|key, value|
            key = "#{key}"
            value = "#{value}"

            if(key.index("material_") && value.to_i>0)
              material_info = key[9..-1].split(/\|/)

              material_id = material_info[0]
              store_employee_name = material_info[1]
              store_employee_id = ApplicationHelper::MaterialsDB.getUserId(store_employee_name)
              material_name = ApplicationHelper::MaterialsDB.getMaterialName(material_id)

              # Сохраняем ЛОГ материала
              sv_materials = MaterialsDoneDB.new
              sv_materials.material_id = material_id
              sv_materials.material_name = material_name
              sv_materials.date = Time.now.to_s(:db)
              sv_materials.report_id = report_id
              sv_materials.date_create_report = @workDate
              sv_materials.count = value

              sv_materials.employee_id = store_employee_id
              sv_materials.employee_name = store_employee_name
              sv_materials.department = $department
              sv_materials.save

              # Сохраняем в базу склада
              # ApplicationHelper::MaterialsDB.saveMaterials(material_id, value, store_employee)
              






            end
          }
    end








		######################################################## Сохраняем рабочую группу
		# get and save
		if(report_id.to_i>0)
			employees = EmployeesDoneDB.new
			employees.delEmployees(report_id)
		end
		
		# GET
		allEmployees = params[:empl]
		allRoles = params[:role]
	
		i=0
		for emplId in allEmployees
			if(emplId.to_i>0)
				sv_empl = EmployeesDoneDB.new
				sv_empl.report_id = report_id
				sv_empl.empl_id = emplId
				# Определяем имя сотрудника
        for val in $employeesAll
          if(val['empl_id'].to_s == emplId.to_s)
              empl_name = val['login']
              break
          end
        end

  


        sv_empl.empl_name = empl_name
				sv_empl.role = allRoles[i]
				sv_empl.date_create_report = @workDate
				sv_empl.department = $department
				sv_empl.save	
				i+=1
			end
		end

		# Считаем сумму з/п и обновляем базу
		countSum = EmployeesDoneDB.new
		countSum.countEmployeeSum(report_id, @category)

		
		



		################################################### ФИНАЛЬНЫЕ ДЕЙСТВИЯ
		# редирект
		#link = url_for(:controller => "private", :action => "index")
		redirect_to :action => "index"

	end
	

	def delReport
		if(params[:id].to_i>0)
		 	id = params[:id].to_i
		else
			id = 0
		end
		
		if(id>0)
			begin
				ReportsDB.find(id)
			rescue	
				print 'Такого идентификатора - НЕТ.'
			else 	
				# delete report
				ReportsDB.delete(id)
				
				# find all done works and delete
				works = WorksDoneDB.new
				works.delWorks(id)

				# find all done materials and delete

				# find all employees and delete
				employees = EmployeesDoneDB.new
				employees.delEmployees(id)

				# redirect
				#link = url_for(:controller => "private", :action => "index")
				redirect_to :action => "index"
			end			
		end
	end
	
	
	# статистика по зарплате
	def stats
		# Page info
		@page_title = 'Статистика по з/п'
		@currPage = "stats"

		

		# ФИЛЬТРАЦИЯ
		time = Time.new
		sqlFIlterDepartment = "a.department='"+$department+"'"
		if(params[:month] && params[:month].to_i>0) then sqlFilterMonth = "AND MONTH(a.date_create_report)="+params['month'].to_s+"" else sqlFilterMonth = "AND MONTH(a.date_create_report)="+time.month.to_s+"" end
		if(params[:day] && params[:day].to_i>0) then sqlFilterDay = "AND DAY(a.date_create_report)="+params[:day].to_s else sqlFilterDay = '' end
		if(params[:year] && params[:year].to_i>0) then sqlFilterYear = "AND YEAR(a.date_create_report)="+params[:year].to_s else sqlFilterYear = '' end
		if(params[:empl] && params[:empl].to_i>0) then sqlFilterEmployee = "AND a.empl_id="+params[:empl].to_s else sqlFilterEmployee = '' end
		
		
		@employees_stats={}
		# ПОлучаем инфу
		allData = ReportsDB.find_by_sql("SELECT 
												a.empl_id, 
												a.empl_name, 
												a.role, 
												a.sum, 
												a.hand, 
												b.category
												FROM done_employees AS a LEFT JOIN reports AS b ON(a.report_id=b.id) 
												WHERE 
												"+sqlFIlterDepartment+"
												"+sqlFilterMonth+"
												"+sqlFilterDay+"
												"+sqlFilterYear+"
												"+sqlFilterEmployee+"
										")
		# Участвовал в переодкл и подключ
		conAndReconCount = 0
		
		
		
		for res in allData
			empl_id = res['empl_id'].to_s
			empl_name = res['empl_name'].to_s
			role = res['role'].to_i
			sum = res['sum'].to_i
			hand = res['hand'].to_f
			category = res['category'].to_i
			if(@employees_stats[empl_id])
				@employees_stats[empl_id]['sum']+=sum
				@employees_stats[empl_id]['hand']+=hand
				# Проверяем было ли это подкл или переподл
				if(category<2)
					# Участвовал в переодкл и подключ
					@employees_stats[empl_id]['conAndReconCount']+=1
					if(role==1)
						# Ответственный в подключ и переподкл
						@employees_stats[empl_id]['conAndReconCountRespond']+=1
					end
				end
				# Допработы
				if(category==2 && role==1)
					@employees_stats[empl_id]['addworkCountRespond']+=1
				end
			else
				@employees_stats[empl_id] = {}
				@employees_stats[empl_id]['name'] = empl_name
				@employees_stats[empl_id]['sum'] = sum
				@employees_stats[empl_id]['hand'] = hand
				@employees_stats[empl_id]['conAndReconCount']=0
				@employees_stats[empl_id]['conAndReconCountRespond']=0
				@employees_stats[empl_id]['addworkCountRespond']=0
				# Проверяем было ли это подкл или переподл
				if(category<2)
					# Участвовал в переодкл и подключ
					@employees_stats[empl_id]['conAndReconCount']+=1
					if(role==1)
						# Ответственный в подключ и переподкл
						@employees_stats[empl_id]['conAndReconCountRespond']+=1
					end
				end
				# Допработы
				if(category==2 && role==1)
					@employees_stats[empl_id]['addworkCountRespond']+=1
				end
			end
		end
	
	
	end

end





#######################################################################################
############################# Вспомагательные классы =================================>
#######################################################################################

class ReportsDB < ActiveRecord::Base
	self.table_name = "reports"
	def delReport(id)
		if(id.to_i>0)
			self.delete(id)	
		end
	end

end

class WorksDoneDB < ActiveRecord::Base
	self.table_name = "done_works"
	
	def getAllWorks
		works = [{}]
		result_works = WorksDoneDB.find_by_sql("SELECT a.*, b.name AS measure_name FROM works AS a LEFT JOIN measures AS b ON(a.measure=b.id)")
			for res_work in result_works
				work_id = res_work['id'].to_i
				work_name = res_work['name'].to_s
				work_price = res_work['price'].to_s
				work_measure = res_work['measure_name'].to_s
				works[work_id] = {'name'=>work_name, 'price'=>work_price, 'measure'=>work_measure}
			end
		return works
	end

	def getWorks(report_id)
		works_done = [{}]
		result_work_done = WorksDoneDB.find_by_sql("SELECT * FROM done_works WHERE report_id="+report_id.to_s+"")
			for res_work in result_work_done
				work_id = res_work['work_id'].to_i
				work_value = res_work['count'].to_i
				works_done[work_id] = work_value	
			end
		return 	works_done	
	end
	
	def delWorks(report_id)
		if(report_id.to_i>0)
			# if editing then remove all works and add 
			works_old_db = WorksDoneDB.find_by_sql("SELECT id FROM done_works WHERE report_id="+report_id.to_s+"")
			for val in works_old_db
				WorksDoneDB.delete(val['id'])	
			end
		end
	end
	
end

class MaterialsDoneDB < ActiveRecord::Base
	self.table_name = "done_materials"
end






class EmployeesDoneDB < ActiveRecord::Base
	self.table_name = "done_employees"

	def getEmployees(report_id)
		employees_done = [{}]
		result_employee_done = EmployeesDoneDB.find_by_sql("SELECT * FROM done_employees WHERE report_id="+report_id.to_s+" ORDER BY role ASC")
		for res_employee in result_employee_done
			if(res_employee['empl_id'] && res_employee['empl_id']>0 && res_employee['role'])
				# роли: 2 - мастер, 3 - помощник, 1 - ответственный
				if(res_employee['role'] == 3) then role_name = 'Помощник' end
				if(res_employee['role'] == 2) then role_name = 'Мастер' end
				if(res_employee['role'] == 1) then role_name = 'Ответственный' end
				employees_done += [ { 'id'=>res_employee['empl_id'], 'name'=>res_employee['empl_name'], 'role'=>res_employee['role'], 'role_name'=>role_name } ]		
			end
		end
		return employees_done
	end

	def delEmployees(report_id)
		if(report_id.to_i>0)
			# if editing then remove all works and add 
			empl_old_db = EmployeesDoneDB.find_by_sql("SELECT id FROM done_employees WHERE report_id="+report_id.to_s+"")
			for val in empl_old_db
				EmployeesDoneDB.delete(val['id'])	
			end
		end
	end

	
	def countEmployeeSum(report_id, workType)
		# Тариф за 1 час работы помощников, руб.
		helpPrice = 50
		
		totalSum = 0
		allWorkSum = 0
		
		workType = workType.to_i
		workPrice = $category_info[workType]['price']
		
		# Получаем сохранённых сотрудников
		result_employee_done = EmployeesDoneDB.find_by_sql("SELECT id, empl_id FROM done_employees WHERE report_id="+report_id.to_s+" ORDER BY role ASC")
		employeesCount = result_employee_done.length.to_i
		
		
		
		# Получаем выполненные работы
		result_work_done = WorksDoneDB.find_by_sql("SELECT a.count, b.price FROM done_works AS a LEFT JOIN works AS b ON(a.work_id=b.id) WHERE a.report_id="+report_id.to_s+"")
		
		# Рассчет
		for res in result_work_done
			allWorkSum += res['count'].to_i * res['price'].to_i
		end
		
		# Пoлная стоимость
		totalSum = allWorkSum.to_i + workPrice.to_i
		totalSum = totalSum.to_i
		

		for res in result_employee_done
			emplId = res['empl_id']
			db_id = res['id'].to_i
			
			# Добавляем проверки если нужны......
			sum = totalSum/employeesCount
			
			# Доля участия
			hand = 1.00/employeesCount
			
			# Обновляем таблицу
			empl = EmployeesDoneDB.find(db_id)
			empl.sum = sum
			empl.hand = hand			
			empl.save
			

		end
		


		## Обновляем отчет на общую сумму сделки
		report = ReportsDB.find(report_id)
		report.sum = totalSum
		report.save		


	end
	

end


#class Employees
#	def getEmployeesXml(department=0)
#			# Парсим XML с сотрудниками, сохраняем через запятую имя и ID, и возвращаем в локальную переменную контроллера
#			if(department>0)
#				result = "MoN-1123,noita-234,Eric-2245,kahlan-2348,timur-111"
##				result2 = "antok-1121,Barsik-23422,Dim-22345"
	#		end
	#		return result+"|"+result2
	#
	#end
	#def getEmployeesArray
	#		result = {}
	#		endResult = {}
	#
	#		# Удаляем символ
	#		tmpStr = $employees.sub(/\|/, ',')
	#		# Разбиваем на массивы
	#		tmpArr = tmpStr.split(',')
#
#
#			for res in tmpArr
#				num = res.sub(/^.*\-([0-9]*)$/, '\1').to_i
##				result[num] = name.capitalize
	#		end
	#
			#result = result.sort{|a,b| a[1]<=>b[1]}
			
			#for res in result.sort{|a,b| a[1]<=>b[1]}
			#	endResult[res[0].to_i] = res[1].to_s
			#end

	#		return result
	#end
#end






#class Xml < ActiveResource::Base
 #    self.site = "http://intranet.rinet.net"
     # site.user = 'store'
     # site.password = 'accessor'
#end
	

#class Person
	#self.site = "http://intranet.rinet.net/local/calc/people_xml.php"

#	def self.remote_report()
#		Remote::GetXml.find(:all, :from=>"/local/calc/people_xml.php")
#	end
#end