# -*- coding: utf-8 -*-
# Filters added to this controller apply to all controllers in the application.
# Likewise, all the methods added will be available for all controllers.

class ApplicationController < ActionController::Base
  helper :all # include all helpers, all the time
  include AuthenticatedSystem
  before_filter :login_required unless (RAILS_ENV == 'development' || RAILS_ENV == 'test' || RAILS_ENV == 'culerity_development' )
 before_filter :set_current_user if  (RAILS_ENV == 'development' || RAILS_ENV == 'test'  || RAILS_ENV == 'culerity_development' )

  # See ActionController::RequestForgeryProtection for details
  # Uncomment the :secret if you're not using the cookie session store
  #
  # Чтобы заработали сессии
  protect_from_forgery :secret => 'ad9a20418525f0f5de1d3c515559f62c'
  #protect_from_forgery :except => :save
  protect_from_forgery :only => [:create, :update, :destroy]

  # See ActionController::Base for details
  # Uncomment this to filter the contents of submitted sensitive data parameters
  # from your application log (in this case, all fields with names like "password").
  # filter_parameter_logging :password


  def set_current_user
    Employee.current_user = Employee.find_by_nic 'annet17' #'nivanova' #'daptitsa'
    @current_user = Employee.current_user
    session[:user] = Employee.current_user.id
  end


  def store_id
    r = Report.find params[:sdelka_id]
    render(:text => r.store_id)

  end


  def from_finn
    r = Report.find params[:id]
    case r.department
      when 'soft'
      redirect_to edit_soft_path(r)
      when 'ams'
      redirect_to edit_am_path(r)
      when 'corp'
      redirect_to  edit_corp2_path(r)
      when 'private'
      redirect_to edit_private_path(r)
      when 'alp'
      redirect_to edit_alp_path(r)
      when 'construction'
      redirect_to edit_constr_work_path(r)
      when 'mod'
      redirect_to edit_mod2_path(r)
      when 'underground'
      redirect_to edit_underground_path(r)
      when 'reception'
      redirect_to receptions_path(r)
      when 'btul2'
      redirect_to btul2_path(r)
      when 'welder'
      redirect_to edit_welder_path(:id =>1, :date => r.date.to_date)
    end
  end

  def isAuthorization
    #      session[:user_department] = EmployeesDB.getUserDepId(session[:user])
    session[:user_department] = Employee.current_user.division_id
    session[:isAdmin] = (UserAccessWork.isAdmin(session[:user])) ? 'true' : ''
    access = UserAccessWork.getDataUserAccess(session[:user])
    
    cookies[:list_user_id] = { :value => ""+session[:user].to_s+"", :expires => 12.hours.from_now, :domain => 'wifikin.ru' }
    session[:isSuperUser] = access['superuser']
    session[:isRead] = access['read']
      session[:isWrite] = access['write']
      session[:isDelete] = access['delete']
      unless(session[:isAdmin]=='true' || session[:isSuperUser]=='true' || session[:isRead]=='true' || session[:isWrite])
         @errors = "В доступе отказано!"
         render(:partial => "shared/errors")
      end
  end


  #SSO - get remote token
  def get_token_user(sso_token)
        #user.sub!(' ','%20')
        SsoToken.find(:one, :from=>"/sdelka/payment_auth_confirm.php?s=#{sso_token}").sso_user
  end


  def userExit
    cookies.delete :sso_token
    cookies.delete :user
    cookies.delete :list_user_id
  end


  def isAccessRead
    if(session[:isAdmin]=='true' || session[:isSuperUser]=='true' || session[:isRead]=='true')
      yield
    else
      @errors = "В доступе отказано!"
      render(:partial => "shared/errors")
    end
  end

  def isAccessWrite
    if(session[:isAdmin]=='true' || session[:isSuperUser]=='true' || session[:isWrite]=='true')
      yield
    else
      @errors = "В доступе отказано!"
      render(:partial => "shared/errors")
    end
  end

  def isAccessDelete
    if(session[:isAdmin]=='true' || session[:isSuperUser]=='true' || session[:isDelete]=='true')
      yield
    else
      @errors = "В доступе отказано!"
      render(:partial => "shared/errors")
    end
  end

  def isAccessSuper
#    RAILS_DEFAULT_LOGGER.debug "@@@@@@@@@@@@@@@@@@@ #{session[:isSuperUser]=='true'}"
    if(session[:isAdmin]=='true' || session[:isSuperUser]=='true')
      yield
    else
      @errors = "В доступе отказано!"
      render(:partial => "shared/errors")
    end
  end

  def isAccessAdmin
    if(session[:isAdmin]=='true')
      yield
    else
      @errors = "В доступе отказано!"
      render(:partial => "shared/errors")
    end
  end



end

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


def session
  params.each { |key, value| session[""+key+""] = value }
  $session = session
end





class UserAccessWork < ActiveRecord::Base
    self.table_name = "access"
    def self.getUserId(user_name)

      result = UserAccessWork.find_by_sql("SELECT empl_id FROM access WHERE empl_name='"+user_name.to_s+"'")
      # Получаем из куки ID который пришёл из Интранета
      if(result!=nil && result[0]!=nil)
        return result[0]['empl_id'].to_s
      else
        return '0'
      end
    end

    def self.getSuperUserDepartment(empl_id)
        result = UserAccessWork.find_by_sql("SELECT department FROM access WHERE empl_id="+empl_id.to_s+" && access_superuser>0")
        if(result!=nil && result[0]!=nil)
          return result[0]['department'].to_s
      else
        return ''
      end
    end


    def self.getUserAccess(empl_id)
      if(empl_id!=nil && empl_id.to_i>0)
        result = UserAccessWork.find_by_sql("SELECT * FROM access WHERE empl_id="+empl_id.to_s+"")
        if(result.size>0) then return result[0] else return false end
      else
          return false
      end
    end

    # метод возвращает всех сотрудников для данной СО
    def self.getAllOtherUserAccess(dep_name)
      result = UserAccessWork.find_by_sql("SELECT * FROM access WHERE department='"+dep_name.to_s+"'")
      if(result.size>0) then return result else return false end

    end

    # метод для проверки есть ли записи по этой СО в базе аццес
    def self.isSetDepAccess(dep_name)
      result = UserAccessWork.find_by_sql("SELECT id FROM access WHERE department='"+dep_name.to_s+"'")
      if(result.size>0) then return true else return false end

    end


    # метод возвращает все права сотрудника
    def self.getDataUserAccess(empl_id=0)
      access = {}
      if(empl_id!=nil && empl_id.to_i>0 && $department!=nil)
          result = UserAccessWork.find_by_sql("SELECT * FROM access WHERE empl_id="+empl_id.to_s+" && department='"+$department.gsub(/[^a-z_]/, '')+"'")
            if(result && result[0]!=nil)
              if(result[0]['access_superuser']!=nil && result[0]['access_superuser'].to_s == '1') then access['superuser'] = 'true' end
              if(result[0]['access_read']!=nil && result[0]['access_read'].to_s == '1') then access['read'] = 'true' end
              if(result[0]['access_write']!=nil && result[0]['access_write'].to_s == '1') then access['write'] = 'true' end
              if(result[0]['access_delete']!=nil && result[0]['access_delete'].to_s == '1') then access['delete'] = 'true' end
            end
      else
        access['superuser'] = ''
        access['read'] = ''
        access['write'] = ''
        access['delete'] = ''
      end
      return access
    end

    def self.delEmployeeAccess(empl_id, department)
        if(empl_id!=nil && department!=nil)
            if(empl_id.to_i>0)
                  val = UserAccessWork.find_by_sql("SELECT id FROM access WHERE empl_id="+empl_id.to_s+" && department='"+department.to_s+"'")
                  if(val!=nil && val[0]!=nil && val[0]['id']!=nil && val[0]['id'].to_i>0) then row_id = val[0]['id'] else row_id = "0" end
                  if(row_id.to_i>0) then UserAccessWork.delete(row_id) end
            end
        end
    end


    def self.isAdmin(empl_id)
      if(empl_id.to_i>0)
        result = UserAccessWork.find_by_sql("SELECT id FROM access WHERE empl_id="+empl_id.to_s+" && access_admin>0")
        if(result.size>0) then return true else return false end
      else
        return false
      end
     # return true
    end

    def self.isSuperUser(empl_id, dep)
     if(empl_id!=nil && empl_id.to_i>0)
        result = UserAccessWork.find_by_sql("SELECT id FROM access WHERE empl_id="+empl_id.to_s+" && access_superuser>0 && department='"+dep.to_s+"'")
        if(result.size>0) then return true else return false end
     else
       return false
     end
    end

    def self.isRead(str_access)
     if(str_access['access_read'].to_i>0) then return true else return false end
    end

    def self.isWrite(str_access)
     if(str_access['access_write'].to_i>0) then return true else return false end
    end

    def self.isDelete(str_access)
     # if(str_access.to_s.scan("x").size>0) then return true else return false end
     if(str_access['access_delete'].to_i>0) then return true else return false end
    end
end








#### Работа с базой данных =====================================================

# Класс для работы с отчетами
class ReportsDB < ActiveRecord::Base
  self.table_name = "reports"
  def delReport(id)
    if(id.to_i>0)
      self.delete(id)
    end
  end

  def self.issetReport(logins, date, workType, employees, department, report_id)
    # логин(ы) (если массив то в строку)
    logins = logins.to_s
    # дата (если две, то первая)
    date = date.to_s
    # Тип работ
    workType = workType.to_s
    # Сотрудники (если массив то в строку)
    employees = employees.to_s
    # Отдел
    department = department.to_s
    require 'digest/md5'
    digest = Digest::MD5.hexdigest(logins+date+workType+employees+department)

    allData = ReportsDB.find_by_sql("SELECT * FROM reports WHERE check_hash='"+digest+"'")

    if(allData.size>0 && report_id.to_i<=0)
      return false
      #report_already_saved!

      # @errors = "Данный отчет уже сохранен."
      #render(:partial => "errors")
    else
      return digest
    end

  end

end



# Класс для работы со списком работ
class WorksDB < ActiveRecord::Base
   self.table_name = "works"
    def self.getWorks(department, category=false)
      addFilter = (category!=false) ? " && a.category LIKE '%"+category.to_s+"-%'" : ''
      works = [{}]
      result_works = self.find_by_sql("SELECT a.*, b.name AS measure_name FROM works AS a LEFT JOIN measures AS b ON(a.measure=b.id) WHERE a.show IS TRUE AND a.department='"+department.to_s+"'"+addFilter+"")
        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
          work_step_button = res_work['step_button'].to_s
          work_rate = res_work['rate'].to_s
          work_addrate = res_work['addrate'].to_s
          works[work_id] = {'id'=>work_id, 'name'=>work_name, 'price'=>work_price, 'measure'=>work_measure, 'step'=>work_step_button, 'rate'=>work_rate, 'addrate'=>work_addrate}
        end
      return works
    end

    def self.getSortWorks(department)
      works = [{}]
      result_works = self.find_by_sql("SELECT a.*, b.name AS measure_name FROM works AS a LEFT JOIN measures AS b ON(a.measure=b.id) WHERE a.department='"+department.to_s+"' ORDER BY a.sort ASC")
      a=0
        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
            work_step_button = res_work['step_button'].to_s
            works[a] = {'id'=>work_id, 'name'=>work_name, 'price'=>work_price, 'measure'=>work_measure, 'step'=>work_step_button}
        a+=1
        end
      return works
    end

    def self.delWork(work_id)
        works_old_db = self.find_by_sql("SELECT id FROM works WHERE id="+work_id.to_s+"")
        for val in works_old_db
          WorksDB.delete(val['id'])
        end
    end

    def self.getPrice(work_id)
        works_info = self.find_by_sql("SELECT price FROM works WHERE id="+work_id.to_s+"")
        return (works_info && works_info[0] && works_info[0]['price']) ? works_info[0]['price'] : false
    end

    def self.getRate(work_id)
        works_info = self.find_by_sql("SELECT rate FROM works WHERE id="+work_id.to_s+"")
        return (works_info && works_info[0] && works_info[0]['rate']) ? works_info[0]['rate'] : false
    end


end

# Класс для работы со списком выполненных работ (не из прайслиста)
class WorksDoneDB < ActiveRecord::Base
    self.table_name = "done_works"

    def self.getWorks(report_id)
      works_done = [{}]
      result_work_done = WorksDoneDB.find_by_sql("SELECT * FROM done_works WHERE report_id="+report_id.to_s+" && pricelist=0")
        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 self.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+" && pricelist=0")
        for val in works_old_db
          WorksDoneDB.delete(val['id'])
        end
      end
    end

    def self.getWorksTotalPrice(report_id)
      result_work_done = WorksDoneDB.find_by_sql("SELECT a.count, b.rate FROM done_works AS a LEFT JOIN works AS b ON(a.work_id=b.id) WHERE a.report_id="+report_id.to_s+" && a.pricelist=0")
      work_price = 0
      for res_work in result_work_done
          work_price += res_work['rate'].to_i * res_work['count'].to_i
        end
      return work_price
    end

    def self.getPricelistWorksTotalPrice(report_id)
      result_work_done = WorksDoneDB.find_by_sql("SELECT * FROM done_works WHERE report_id="+report_id.to_s+" && pricelist=1")
      work_price = 0
      for res_work in result_work_done
          work_id = res_work['work_id'].to_i
          work_price += PricelistDB.getWorkPrice(work_id).to_i * res_work['count'].to_i
        end
      return work_price
    end

    def self.getPricelistWorksTotalRate(report_id)
      result_work_done = WorksDoneDB.find_by_sql("SELECT * FROM done_works WHERE report_id="+report_id.to_s+" && pricelist=1")
      work_price = 0
      for res_work in result_work_done
          work_id = res_work['work_id'].to_i
          work_price += PricelistDB.getRate(work_id).to_i * res_work['count'].to_i
        end
      return work_price
    end


end


  # Класс для работы со списком выполненных работ (не из прайслиста)
class ObjectsDoneDB < ActiveRecord::Base
    self.table_name = "done_objects"

    def self.getObjects(report_id)
      result_done = ObjectsDoneDB.find_by_sql("SELECT * FROM done_objects WHERE report_id="+report_id.to_s+"")
      return (result_done && result_done.length>0) ? result_done : 'false'
    end

    def self.delObjects(report_id)
      if(report_id.to_i>0)
        # if editing then remove all works and add
        result_done = ObjectsDoneDB.find_by_sql("SELECT id FROM done_objects WHERE report_id="+report_id.to_s+"")
        for val in result_done
          ObjectsDoneDB.delete(val['id'])
        end
      end
    end

end


    # Класс для работы с категориями
class CategoryDB < ActiveRecord::Base
    self.table_name = "category"

  def self.add_new
    unless  CategoryDB.find_by_name 'Проблема с качеством'
      CategoryDB.create(:department => "ams", :show => 1, :sort => 600, :name => 'Проблема с качеством')
    end
    unless  CategoryDB.find_by_name 'Проблемы на узле'
      CategoryDB.create(:department => "ams", :show => 1, :sort => 700, :name => 'Проблемы на узле')
    end
  end

    def self.getCategory(department)
      result = []
      result_done = CategoryDB.find_by_sql("SELECT id, name, price, bonus FROM category WHERE department='"+department.to_s+"' ORDER BY sort ASC")
      for data in result_done
          result += [ { 'id'=>data['id'], 'name'=>data['name'], 'price'=>data['price'], 'bonus'=>data['bonus'] } ]
      end
      return result.compact
    end

    def self.getCategoryInfo(cat_id)
      result_done = CategoryDB.find_by_sql("SELECT id, name, price, bonus FROM category WHERE id='"+cat_id.to_s+"'")
      return (result_done && result_done.length>0) ? result_done[0] : 'false'
    end

end


  # Класс для работы с конфиг переменными
  class CfgDB < ActiveRecord::Base
    self.table_name = "cfg"

    def self.getCfgs(department)
      result = []
      result_done = self::find_by_sql("SELECT * FROM cfg WHERE department='"+department.to_s+"' ORDER BY id ASC")
      for data in result_done
          result += [ { 'id'=>data['id'], 'name'=>data['name'], 'value'=>data['val'], 'description'=>data['description'] } ]
      end
      return result.compact
    end


    def self.getCfgById(id)
      result_done = self::find_by_sql("SELECT * FROM cfg WHERE id='"+id.to_s+"'")
      return (result_done && result_done[0]) ? result_done[0] : 'false'
    end

    def self.getCfgValueById(id)
      result_done = self::find_by_sql("SELECT val FROM cfg WHERE id='"+id.to_s+"'")
      return (result_done && result_done[0] && result_done[0]['val']) ? result_done[0]['val'] : '0'
    end





  end


# Класс дя работы с сотрудниками из базы склада
class EmployeesDB < ActiveRecord::Base
   establish_connection :pricelist
   self.abstract_class = true
   self.table_name = "pl_people"

    def self.getUserId(empl_name)
      result = EmployeesDB.find_by_sql("SELECT id FROM pl_people WHERE login='"+empl_name.to_s+"'")
      return (result[0]!=nil && result[0]['id']!=nil) ? result[0]['id'] : 'false'
    end

    def self.getUserName(empl_id)
      result = EmployeesDB.find_by_sql("SELECT login FROM pl_people WHERE id='"+empl_id.to_s+"'")
      return (result[0]!=nil && result[0]['login']!=nil) ? result[0]['login'] : 'false'
    end

    def self.getUserDepId(empl_id)
      result = EmployeesDB.find_by_sql("SELECT division_id FROM pl_people WHERE id='"+empl_id.to_s+"'")
      return (result[0]!=nil && result[0]['division_id']!=nil) ? result[0]['division_id'] : 'false'
    end

    def self.getEmployeeInfo(empl_id)
      result = EmployeesDB.find_by_sql("SELECT * FROM pl_people WHERE id='"+empl_id.to_s+"'")
      if(result.size>0) then return result[0] else return false end
    end

    # Возвращает массив сотрудников, отделы можно передавать через запятую
    def self.getEmployees(departments, only_works=false)
      departments = departments.to_s
      where = []
      if(departments.to_s!='*')
        departments_array = departments.split(",")
        departments_array = MyArray.clearArray(departments_array)
        departments_where = "a.division_id="+departments_array.join(" || a.division_id=")
        where << "("+departments_where+")"     
      end
      where << "a.quitted IS FALSE" if only_works == 'true'
      where_cond = where.size > 0 ? ("WHERE "+where.join(' AND ')) : ""

      result = Employee.find_by_sql("SELECT a.id, a.division_id, a.nic, b.name AS division FROM people AS a LEFT JOIN divisions AS b ON(a.division_id=b.id) "+where_cond+" ORDER BY division_id ASC, nic ASC")
      return (result!=nil && result[0]!=nil) ? result : 'false'

    end

    def self.getEmployeesWithLeavePerson(person_ids = nil)
    if person_ids && !person_ids.blank?
    where = "WHERE (quitted IS NOT TRUE) OR a.id IN ("+person_ids.join(", ")+")"
    else
    where = ""
    end
    result = Employee.find_by_sql("SELECT a.id, a.division_id, a.nic, b.name AS division FROM people AS a LEFT JOIN divisions AS b ON(a.division_id=b.id) "+where.to_s+" ORDER BY division_id ASC, nic ASC")
    return (result!=nil && result[0]!=nil) ? result : 'false'
    end

    # Возвращаем email сотрудника
    def self.getEmail(empl_id)
      result = Employee.find_by_sql("SELECT email FROM people WHERE id='"+empl_id.to_s+"'LIMIT 1")
      return (result.size>0) ? result[0]['contact'] : false
    end

    # Возвращаем должность сотрудника
    def self.getPosition(empl_id)
      result = Employee.find_by_sql("SELECT position FROM people WHERE id='"+empl_id.to_s+"' LIMIT 1")
      case result.first
      when 'бригадир'
  ret = 863
      else
  ret = 'false'
      end
      return ret
    end

end


# Класс для работы с диспетчерами
class ManagersBD < ActiveRecord::Base
  self.table_name = "managers"

  def self.getManagers(department, month, year)
    managers = [{ 'id'=>"0", 'name'=>"0", 'date'=>"0" }]
    result_managers = ManagersBD.find_by_sql("SELECT * FROM managers WHERE department='"+department.to_s+"' && date LIKE '"+year.to_s+"-"+month.to_s+"-%' ORDER BY date ASC")


    for res_manager in result_managers
      if(res_manager && res_manager['empl_id'])
        date = res_manager['date']
        empl_id = res_manager['empl_id']
        empl_name = EmployeesDB::getUserName(empl_id)
        sum = res_manager['sum']

        managers+= [ { 'id'=>empl_id.to_s, 'name'=>empl_name.to_s, 'date'=>date.to_s, 'sum'=>sum.to_s } ]
      end
    end
    return managers
  end

  def self.isManager(department, date, empl_id)
    result_managers = ManagersBD.find_by_sql("SELECT * FROM managers WHERE department='"+department.to_s+"' && date = '"+date.to_s+"' && empl_id = '"+empl_id.to_s+"'")
    return (result_managers.size>0) ? true : false
  end

  def self.isSetManagers(department, date)
    result_managers = ManagersBD.find_by_sql("SELECT * FROM managers WHERE department='"+department.to_s+"' && date = '"+date.to_s+"'")
    return result_managers.size
  end

  def self.saveManagers(manager_id, date, department)
    if(manager_id>0 && date.scan(/^[0-9]{4}\-[0-9]{1,2}\-[0-9]{1,2}$/).size>0)
        date = self.formatDate(date)
        result_managers = ManagersBD.find_by_sql("SELECT id FROM managers WHERE date='"+date.to_s+"' && empl_id='"+manager_id.to_s+"' && department='"+department.to_s+"'")
        if(result_managers.size>0)
          return 'Диспетчер уже был добавлен'
        end
        # work Date
        date_tmp = date.split("-")
        workdate = Time.local(date_tmp[0], date_tmp[1], date_tmp[2])
        day_type = (workdate.wday == 6 || workdate.wday == 0) ? 'holidays' : 'workdays'

        sv_managers = ManagersBD.new
        sv_managers.date = date
        sv_managers.date_add = Time.now.to_s(:db)
        sv_managers.empl_id=manager_id
        sv_managers.empl_name=EmployeesDB::getUserName(manager_id)
        sv_managers.department = department
        sv_managers.day_type = day_type
        sv_managers.save
      return 'true'
    else
      return 'Ошибка...'
    end


  end

  def self.deleteManagers(manager_name, date, department)
    manager_id = EmployeesDB::getUserId(manager_name)
    if(manager_id.to_i>0 && date.scan(/^[0-9]{4}\-[0-9]{1,2}\-[0-9]{1,2}$/).size>0)
        date = self.formatDate(date)
        result_managers = ManagersBD.find_by_sql("SELECT id FROM managers WHERE date='"+date.to_s+"' && empl_id='"+manager_id.to_s+"' && department='"+department.to_s+"'")
        val = result_managers[0]
        ManagersBD.delete(val['id'])
        return 'true'
    else
      return manager_name
    end
  end

  def self.getSumReports(date, department)
    date_tmp = date.split("-")
    year  = date_tmp[0]
    month = date_tmp[1]
    day = date_tmp[2]

    result_managers = ReportsDB.find_by_sql("SELECT SUM(sum) AS allsum FROM reports WHERE department='"+department.to_s+"' && MONTH(date) = '"+month.to_s+"' && YEAR(date) = '"+year.to_s+"' && DAY(date) = '"+day.to_s+"'")
    return (result_managers && result_managers[0]!=nil && result_managers[0]['allsum']) ? result_managers[0]['allsum'].to_i : 0
  end

  def self.checkFirst(date, empl_id, department)
    date_tmp = date.split("-")
    year  = date_tmp[0]
    month = date_tmp[1]
    day = date_tmp[2]
    tmp = EmployeesDoneDB.find_by_sql("SELECT * FROM managers WHERE department='"+department.to_s+"' && MONTH(date) = '"+month.to_s+"' && YEAR(date) = '"+year.to_s+"' && DAY(date) = '"+day.to_s+"' ORDER BY id ASC")
    return (tmp[0]['empl_id'].to_s == empl_id.to_s) ? true : false
  end

  def self.countWorkDays(date)
    date_tmp = date.split("-")
    year  = date_tmp[0]
    month = date_tmp[1]

    daysInMonth = Time.days_in_month(month.to_i, year.to_i)

    i=1
    workdays = 0
    while i<=daysInMonth
      # work Date
      workdate = Time.local(year, month, i)
      if(workdate.wday!=6 && workdate.wday!=0) then workdays+=1 end
      i+=1
    end
    return workdays
  end

  def self.getSumAms(date, empl_id, day_type, department)
    # Если диспетчер - парвый?
    if(self.checkFirst(date, empl_id, department))
        date = self.formatDate(date)
        ## получаем сумму з/п за дату
        allsum = self.getSumReports(date, department)

        ### Считаем проценты
        percent = (day_type=='holidays') ? 30 : 20

        #### Считаем сумму по процентам
        sum = allsum*percent/100;


    else
       countWorkDays = self.countWorkDays(date)
       sum = 25000/countWorkDays.to_i
    end

      # Округление (2 знака после запятой)
      return sprintf("%.2f", sum).to_f
  end

  def self.getSumSoft(date, department)
        ## получаем сумму з/п за дату
        allsum = self.getSumReports(date, department)

        ### Считаем проценты
        percent = 30

        #### Считаем сумму по процентам
        sum = allsum*percent/100

      # Округление (2 знака после запятой)
      return sprintf("%.2f", sum).to_f
  end

  def self.formatDate(date)
    date = date.split("-")
      a=0
      for d in date
        if(d.to_s.length<=1)
          date[a]="0"+d
        end
        a+=1
      end
      date = date.join("-")
  end

end


# Класс дя работы с сотрудниками из базы отчетов
class EmployeesDoneDB < ActiveRecord::Base
  self.table_name = "done_employees"

  def self.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'])
        # роли: 2 - мастер, 3 - помощник, 1 - ответственный
        role_name = 'Мастер'
        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, 'time_work'=>res_employee['time_work'], 'time_addwork'=>res_employee['time_addwork'], 'time_stopwork'=>res_employee['time_stopwork'], 'ented_intime'=>res_employee['ented_intime'] } ]
      end
    end
    return employees_done
  end



  def self.getEmployeesPay(report_id)
    data = {}
    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'])
        data[res_employee['empl_id'].to_s] = res_employee['sum'].to_s
      end
    end
    return data
  end

  def self.isEnted(report_id, empl_id)
    result_employee_done = self.find_by_sql("SELECT id FROM done_employees WHERE report_id="+report_id.to_s+" && empl_id='"+empl_id.to_s+"'")
    return (result_employee_done && result_employee_done.size>0) ? 'true' : 'false'
  end


  def self.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 self.getDateEmployees(month, year, department)
      employees = []
      result = {}
      result_employee_done = EmployeesDoneDB.find_by_sql("SELECT * FROM done_employees WHERE department='"+department.to_s+"' && MONTH(date_create_report) = '"+month.to_s+"' && YEAR(date_create_report) = '"+year.to_s+"' && DAY(date_create_report) IN ( 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31 )")
      for val in result_employee_done
        employees +=[val['empl_id']]
      end
      if(employees.size>1)
        employees = employees.uniq
        for tmp in employees
          empl_id = tmp
          empl_name = EmployeesDB.getUserName(empl_id)
          result[empl_id] = empl_name
        end
      end
      return result
    end

    def self.getDateEmployee(empl_id, department, day, month, year)
      dayFilter = (day.to_i>0) ? "DAY(date_create_report) = '"+day.to_s+"'" : "DAY(date_create_report) IN ( 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31 )"
      result_employee_done = EmployeesDoneDB.find_by_sql("SELECT * FROM done_employees WHERE empl_id='"+empl_id.to_s+"' && department='"+department.to_s+"' && MONTH(date_create_report) = '"+month.to_s+"' && YEAR(date_create_report) = '"+year.to_s+"' && "+dayFilter+"")
      return result_employee_done
    end
end

# Класс для работы с новым складом
class AssetsDBNew < ActiveRecord::Base

  require 'remote'

  def self.getMaterials(empl_name)

    result =  Remote::Store.find(:all, :from => "/sdelka/person_balance/#{empl_name}.xml")

    materials = {}
    for data in result
  materials[material_id] = {}
  materials[material_id]['id'] = result.asset_id
  materials[material_id]['asset_id'] = result.asset_id
  materials[material_id]['name'] = result.name
  materials[material_id]['amount'] = result.count
    end
    return materials
  end


    def self.getUserId(empl_name)
      result = AssetsDB.find_by_sql("SELECT id FROM people WHERE nic='"+empl_name.to_s+"'")
      return (result[0]!=nil && result[0]['id']!=nil) ? result[0]['id'] : 'false'
    end

    def self.getUserName(empl_name)
      result = AssetsDB.find_by_sql("SELECT name FROM people WHERE id='"+empl_name.to_s+"'")
      return (result[0]!=nil && result[0]['name']!=nil) ? result[0]['name'] : 'false'
    end


    def self.getMaterialName(material_id)
      result = AssetsDB.find_by_sql("SELECT name FROM assets WHERE id='"+material_id.to_s+"'")
      return (result[0]!=nil && result[0]['name']!=nil) ? result[0]['name'] : 'false'
    end

    def self.getLoginRoots(lname='')
      if(lname!='')
        result = AssetsDB.find_by_sql("SELECT name FROM loginroots WHERE name LIKE '"+lname.to_s+"%' ORDER BY name ASC")
      end
      return (result && result[0]!=nil && result[0]['name']!=nil) ? result : 'false'
    end


end



# Класс для работы со складом
class AssetsDB < ActiveRecord::Base
  require 'remote'

  def self.getMaterials(empl_name)

    result =  Remote::Store.find(:all, :from => "/sdelka/person_balance/#{empl_name}.xml")

    materials = {}
    for data in result
      material_id = data.asset_id
      materials[material_id] = {}
      materials[material_id]['id'] = nil
      materials[material_id]['asset_id'] = data.asset_id
      materials[material_id]['name'] = data.asset.attributes["type"].to_s+" "+data.name
      materials[material_id]['amount'] = data.count
    end
    return materials
  end

    def self.getUserId(empl_name)
      begin
  result =  Remote::Store.find(:first, :from => "/sdelka/identify_person/#{empl_name}.xml")
      rescue ActiveResource::ResourceNotFound, ActiveResource::ResourceConflict, ActiveResource::ResourceInvalid
  result = 'false'
      end
      return (result != 'false' ) ? result.id : 'false'
    end

    def self.getMaterialName(material_id)
      begin
  result =  Remote::Store.find(:first, :from => "/sdelka/asset/#{material_id}.xml")
      rescue ActiveResource::ResourceNotFound, ActiveResource::ResourceConflict, ActiveResource::ResourceInvalid
  result = 'false'
      end
      return (result != 'false' ) ? result.name : 'false'
    end

    def self.getLoginRoots(lname='')
      result =  Remote::Store.find(:all, :from => "/sdelka/loginroots/#{lname}.xml")
      return (result && !result.empty?) ? result.map{|i| i.loginroot} : 'false'
    end

end


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

  def self.getMaterials(report_id)
    result = self.find_by_sql("SELECT * FROM done_materials WHERE report_id='"+report_id.to_s+"'")
    if(result.size>0)
    materials = {}
      for data in result
        employee_name = data['employee_name']
        serials = SerialsDoneDB.getSerials(report_id, data['material_id'])
        if(!materials[employee_name])
                materials[employee_name] = [{}]
                materials[employee_name] += [{'material_name'=>data['material_name'], 'count'=>data['count'], 'price'=>data['price'], 'serials'=>serials }]
        else
                materials[employee_name] += [{'material_name'=>data['material_name'], 'count'=>data['count'], 'price'=>data['price'], 'serials'=>serials}]
        end
      end
    else
      materials = false
    end

    return materials
  end


  def self.getMaterialInfo(report_id, material_id)
    result = self.find_by_sql("SELECT * FROM done_materials WHERE report_id='"+report_id.to_s+"' && material_id='"+material_id.to_s+"'")
    return result[0]
  end



end


class SerialsDoneDB < ActiveRecord::Base
  self.table_name = "done_serials"

  def self.getSerials(report_id, material_id)
    result = self.find_by_sql("SELECT * FROM done_serials WHERE report_id='"+report_id.to_s+"' && material_id='"+material_id.to_s+"'")
    return (result && result.size>0) ? result : 'false'
  end


end





# Класс дя работы с прайс листом
class PricelistDB < ActiveRecord::Base
   establish_connection :pricelist
   self.abstract_class = true
   self.table_name = "pl_items"



    def self.getPositions(division_id, report_id, clientType='P')
      result = PricelistDB.find_by_sql("SELECT a.*, b.division_id, c.name AS division_name, d.name AS section_name, d.ordered
                                          FROM pl_items AS a
                                          LEFT JOIN pl_item_divisions AS b ON(b.item_id=a.id)
                                          LEFT JOIN pl_divisions AS c ON(c.id=b.division_id)
                                          LEFT JOIN pl_sections AS d ON(d.id=a.section_id)
                                          WHERE a.section_id!='217' && a.client_type='"+clientType.to_s+"' && b.division_id='"+division_id.to_s+"' && YEAR(a.date_end)<2000 ORDER BY a.id ASC")



    positions = {}
    for data in  result
      section_id = data['section_id'].to_s
      section_name = data['section_name'].to_s
      position_id = data['id'].to_s
      if(!positions[section_name])
        positions[section_name] = {}
      end
      if(!positions[section_name][position_id])
        positions[section_name][position_id] = {}
      end

      positions[section_name][position_id]['name'] = data['name']
      positions[section_name][position_id]['price'] = data['price']
      positions[section_name][position_id]['rate'] = data['rate']
      positions[section_name][position_id]['client_type'] = data['client_type']
      positions[section_name][position_id]['section_id'] = data['section_id']
      positions[section_name][position_id]['section_name'] = data['section_name']
      positions[section_name][position_id]['ordered'] = data['ordered']

      if(!positions[section_name][position_id]['divisions'])
        positions[section_name][position_id]['divisions'] = [data['division_name']]
      else
        positions[section_name][position_id]['divisions'] += [data['division_name']]
      end

    end
    return positions
  end

  def self.getWorkPrice(work_id)
      result_work_done = PricelistDB.find_by_sql("SELECT price FROM pl_items WHERE id='"+work_id.to_s+"'")
      return  (result_work_done && result_work_done[0] && result_work_done[0]['price'].to_i>0) ? result_work_done[0]['price'] : 0
  end

  def self.getRate(work_id)
      result_work_done = PricelistDB.find_by_sql("SELECT rate FROM pl_items WHERE id='"+work_id.to_s+"'")
      return  (result_work_done && result_work_done[0] && result_work_done[0]['rate'].to_i>0) ? result_work_done[0]['rate'] : 0
  end

  def self.getWorks(division_id)
      result = PricelistDB.find_by_sql("SELECT a.*, b.division_id
                                          FROM pl_items AS a
                                          LEFT JOIN pl_item_divisions AS b ON(b.item_id=a.id)
                                          WHERE b.division_id='"+division_id.to_s+"' && YEAR(a.date_end)<2000 ORDER BY a.id ASC")

    positions = [{}]
    for data in  result
      position_id = data['id'].to_i
      position_name = data['name'].to_s
      position_price = data['price'].to_s
      position_rate = data['rate'].to_s

      positions[position_id] = {'id'=>position_id, 'name'=>position_name, 'price'=>position_price, 'step'=>'1', 'rate'=>position_rate}
    end
    return positions
  end



  def self.getItemId(asset_id)
    result = PricelistDB.find_by_sql("SELECT item_id FROM pl_item_assets WHERE asset_id='"+asset_id.to_s+"'")
    return (result && result[0]) ? result[0]['item_id'] : 'false'
  end


  def self.getPrice(material_id, clientType='P')
   # itemId = self.getItemId(material_id) # ID позиции !!!!! Странная штука
   # if itemId != 'false'
   #   assetInfo = self.getItem(itemId, clientType) # ИНФО позиции
   #   return (assetInfo!='false') ? assetInfo['price'] : ""
   # end
    ""
  end


  def self.getItem(itemId, clientType='P')
    result = PricelistDB.find_by_sql("SELECT * FROM pl_items WHERE id='"+itemId.to_s+"'")
    return (result && result[0]) ? result[0] : 'false'
  end


end

# Класс для работы со списком выполненных работ (из прайслиста)
class WorksPricelistDoneDB < ActiveRecord::Base
    self.table_name = "done_works"

    def self.getWorks(report_id)
      works_done = [{}]
      result_work_done = WorksDoneDB.find_by_sql("SELECT * FROM done_works WHERE report_id="+report_id.to_s+" && pricelist=1")
        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 self.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+" && pricelist=1")
        for val in works_old_db
          WorksDoneDB.delete(val['id'])
        end
      end
    end

end


  # Класс для работы с датами
  class MyDate < ActiveRecord::Base

    @@monthNames = {
                  '01'=>'Январь',
                  '1'=>'Январь',
                  '02'=>'Февраль',
                  '2'=>'Февраль',
                  '03'=>'Март',
                  '3'=>'Март',
                  '04'=>'Апрель',
                  '4'=>'Апрель',
                  '05'=>'Май',
                  '5'=>'Май',
                  '06'=>'Июнь',
                  '6'=>'Июнь',
                  '07'=>'Июль',
                  '7'=>'Июль',
                  '08'=>'Август',
                  '8'=>'Август',
                  '09'=>'Сентябрь',
                  '9'=>'Сентябрь',
                  '10'=>'Октябрь',
                  '11'=>'Ноябрь',
                  '12'=>'Декабрь'
                  }

    def self.format1(date)
      # 2009-11-30 17:40
      tmp = date.split(' ')
      dateNew = tmp[0].split('-').reverse
      return dateNew[0].to_s+" "+@@monthNames[dateNew[1].to_s].to_s
    end

  end



  class MyArray < ActiveRecord::Base
    # убирает из массива все nil и пустые элементы
    def self.clearArray(array)
      ar = [nil]
      i=1
      if(array.size>0)
        for data in array
          if(data!=nil && data!="" && data.to_i>0)
            ar[i] = data
            i+=1
          end
        end
        return ar.compact
      else
        return array.compact
      end
    end
  end



############################## Класс для организации проверок данных
class Check

  def self.intval(text)
    return (text) ? text.gsub(/[^0-9\.]/, '') : ''
  end

  def self.isText(text)
    return (text) ? text.gsub(/[^A-zА-я\.\-\,0-9\%\:\|\s\!\@]/, '').to_s : ''
  end

  def self.trim(text)
    return (text) ? text.strip : ''
  end


end


  ############################





















### Для справки работа с simpleXML + получение содержимого с удаленного адреса

#require 'net/http'
#require 'rubygems'
#require 'xmlsimple'

#url = 'http://api.search.yahoo.com/WebSearchService/V1/webSearch?appid=YahooDemo&query=madonna&results=2'
#xml_data = Net::HTTP.get_response(URI.parse(url)).body

#data = XmlSimple.xml_in(xml_data)

#data['Result'].each do |item|
#   item.sort.each do |k, v|
#      if ["Title", "Url"].include? k
#         print "#{v[0]}" if k=="Title"
#        print " => #{v[0]}\n" if k=="Url"
#     end
#  end
#end

########################################################################