require 'rmagick'
require 'fileutils'
require 'net/ftp'
require "mysql2"

class Statistic < ActiveRecord::Base
  include ThinkingSphinx::Scopes
  belongs_to :image
  belongs_to :user
  belongs_to :operation_label

  validates :image_id, :presence => true
  validates :user_id, :presence => true
  validates :operation_label_id, :presence => true

  before_create :send_message_to_ms if Server.itself? PIXADMIN_SERVER_NAME

  def send_message_to_ms
    if self.operation_label.label.in?(['BD','HD'])
      user= User.find(self.user_id)
      unless user.title.name == PIXWAYS_TITLE || user.title.server.name == PIXADMIN_SERVER_NAME || user.roles_mask.in?([1,8,16,32,128])
        img = Image.find(self.image_id)
        MessageToMs::PutMessageRec.sendMessageRecord("SEND_TO_PT", img.ms_image_id) unless img.nil?
      end
    end
  end

  def self.create_stat(u,i,op)
    self.create(user_id: u, image_id: i, operation_label_id: op)
  end

  def self.export_to_csv(stats, pixtech_author, provider_word, creator_word)
    export = ""
    if pixtech_author
      CSV(export, {col_sep: ";", quote_char: '"', force_quotes: true}) do |csv|
        csv << [
          I18n.t('statistics.date'),
          I18n.t('statistics.download'),
          I18n.t('statistics.title'),
          I18n.t('statistics.file_name'),
          I18n.t('statistics.picture_title'),
          I18n.t(creator_word).capitalize,
          I18n.t('statistics.description')
        ]
        stats.each do |s|
          a = s.user.id
          title_status = s.user.nil? ? [I18n.t(:deleted)] : [s.user.title.name]
          im_status = s.image.nil? ? [I18n.t(:deleted)] : [s.image.original_filename]
          v = [s.image.headline, s.image.creator, s.image.description]
          csv << [I18n.l(s.created_at), s.operation_label.label] + title_status + im_status + v
        end
      end
    else
      CSV(export, {col_sep: ";", quote_char: '"', force_quotes: true}) do |csv|
        csv << [
          I18n.t('statistics.date'),
          I18n.t('statistics.download'),
          I18n.t('statistics.title'),
          I18n.t('statistics.user'),
          I18n.t(provider_word, count: 1),
          I18n.t('statistics.file_name'),
          I18n.t('statistics.picture_title'),
          I18n.t(creator_word).capitalize,
          I18n.t('statistics.description')
        ]
        stats.each do |s|
          user_status = ( s.user.nil?) ? ['', I18n.t(:deleted) ] : [s.user.title.name, s.user.login]
          im_status = (s.image.nil?) ? ['', I18n.t(:deleted) ] : [s.image.provider.name, s.image.original_filename]
          v = [s.image.headline, s.image.creator, s.image.description]
          csv << [I18n.l(s.created_at), s.operation_label.label] + user_status + im_status + v
        end
      end
    end
    export
  end

  def self.export_synthesis_to_csv(stats, nb_prov)
    export = ""
    stats_synth = []
    stats.sort_by!{|s| [s.created_at.strftime("%Y/%m"),s.image.provider.name,s.user.title.name,s.operation_label.label]}
    stats.chunk{|s| [s.created_at.strftime("%Y/%m"),s.image.provider.name,s.user.title.name,s.operation_label.label]}.each{|n,l| stats_synth << {month:n[0],provider:n[1],title:n[2],label:n[3],nb:l.length}}

    months_list = []
    stats_synth.each{|s| months_list << s[:month] }
    months_list.uniq!

    header_1 = ['','']
    months_list.each{|m| header_1 << Date.strptime(m,'%Y/%m').strftime("%b %Y");header_1 << '';header_1 << ''}
    labels_list = OperationLabel.where(id:[1,2,3]).order(:id).pluck(:label)
    header_2 = [I18n.t(:provider, count: 1).capitalize, I18n.t(:title)]
    months_list.each{|m| labels_list.each {|l| header_2 << l}}
    header_2 << 'Total'

    stats_synth_per_titles = []
    stats_synth.chunk{|s| [ s[:month], s[:provider], s[:title] ]}.each do |n,l|
      stats_synth_per_titles_line = {month:n[0], provider:n[1], title:n[2]}
      labels_list.each do |ll|
        l.each do |ld|
          if ld[:label] == ll
            stats_synth_per_titles_line.merge!(ll.to_sym=>ld[:nb])
          end
        end
      end
      stats_synth_per_titles << stats_synth_per_titles_line
    end

    stats_synth_tot = []
    stats_synth_per_titles.sort_by!{|s| [s[:provider].downcase, s[:title].downcase, s[:month]]}
    stats_synth_per_titles.group_by{|s| [s[:provider], s[:title]]}.each do |n,l|
      stats_synth_tot_line = []
      stats_synth_tot_line << n[0] << n[1]
      total = 0
      months_list.each do |m|
        labels_list.each do |ll|
          nb = l.find {|ld| m == ld[:month] && ld[ll.to_sym]}
          if nb.nil?
            stats_synth_tot_line.push('')
          else
            stats_synth_tot_line.push(nb[ll.to_sym])
            total += nb[ll.to_sym].to_i
          end
        end
      end
      stats_synth_tot_line.push(total)
      stats_synth_tot << stats_synth_tot_line
    end

    CSV(export, {col_sep: ";"}) do |csv|
      unless nb_prov > 1
        header_1.shift
        header_2.shift
      end
      csv << header_1
      csv << header_2
      stats_synth_tot.each {|s| s.shift unless nb_prov > 1; csv << s }
    end

    export
  end

  def provider
    image.provider
  end

  def title_id
    user.title_id
  end

  def operation_label_name
    I18n.t(self.operation_label.label.to_sym)
  end

  # --- dates --->
  def localised_created_at
    I18n.l(created_at.in_time_zone.to_time)
  end

  def localised_updated_at
    I18n.l(updated_at.in_time_zone.to_time)
  end

  def self.afp_xls
    if Server.itself? VINGTMINUTES_SERVER_NAME
      month_range = (Date.today-1.month).at_beginning_of_month..(Date.today-1.month).at_end_of_month
      mois = I18n.t("date.month_names")[(Date.today-1.month).month]
      year = (Date.today-1.month).year
      users = User.where("title_id != (select id from titles where name='#{PIXWAYS_TITLE}')").collect{|u| u.id}
      prov_id = Provider.find_by_string_key("20min_Afp").id
      tabstats = []
      tstats = []
      tabstats = Statistic.joins(:image).where(user_id: users, images: {provider_id: prov_id}, statistics: {created_at: month_range}).order('statistics.created_at desc').uniq.pluck(:image_id)
      tabstats.each do |i|
        tstats << Statistic.where(image_id: i, created_at: month_range).first.id
      end
      stats = Statistic.joins(:image).where(statistics: {id: tstats}).order('statistics.created_at desc')
      nbs = stats.count

      Spreadsheet.client_encoding = 'UTF-8'
      xls_doc = Spreadsheet::Workbook.new

      sheet = xls_doc.create_worksheet(name: "20 Minutes Rapport")
      sheet[0,0] = "Date"
      sheet[0,1] = "Référence"
      sheet[0,2] = "Date Photo"
      sheet[0,3] = "Objet"
      sheet[0,4] = "Source"
      sheet[0,5] = "Photographe"
      stats.each_with_index do |s,i|
        sheet[i+1,0] = I18n.l(s.created_at)
        sheet[i+1,1] = s.image.original_filename
        sheet[i+1,2] = s.image.date_created
        sheet[i+1,3] = s.image.headline
        sheet[i+1,4] = s.image.source
        sheet[i+1,5] = s.image.creator
      end

      #Format
      bold = Spreadsheet::Format.new weight: :bold
      sheet.row(0).default_format = bold

      xls_doc.write 'tmp/stats_afp.xls'

      # Axlsx::Package.new do |p|
      #   p.workbook.add_worksheet(:name => "20 Minutes Rapport") do |sheet|
      #     sheet.add_row ["Date", "Référence", "Date Photo", "Objet", "Source", "Photographe"]
      #     sheet.column_widths nil, nil, nil, nil, nil, nil
      #     stats.each do |s|
      #       sheet.add_row [I18n.l(s.created_at), s.image.original_filename, s.image.date_created, s.image.headline, s.image.source, s.image.creator], height: 20
      #     end
      #   end
      #   p.serialize('afp.xlsx')
      # end

      UserMailer.send_afp_xls(nbs, mois, year).deliver_now
    end
  end

  def self.stats_editis
    if Server.itself? EDITIS_SERVER_NAME
      month_range = (Date.today-1.month).at_beginning_of_month..(Date.today-1.month).at_end_of_month
      mois = I18n.t("date.month_names")[(Date.today-1.month).month]
      users = User.where("title_id != (select id from titles where name='#{PIXWAYS_TITLE}')").pluck(:id)
      prov_id = Provider.where(name: EDITIS_LOCAL_BUTTONS).pluck(:id)
      prov_id.each do |prov|
        stats = Statistic.joins(:image).where(user_id: users, images: {provider_id: prov}, statistics: {created_at: month_range}).order('created_at desc')
        statsbd = stats.where(operation_label_id: 1).count
        statshd = stats.where(operation_label_id: 2).count
        statsdem = stats.where(operation_label_id: 3).count
        mel = ProviderContact.where(provider_id: prov, receive_stat: true).pluck(:email)
        UserMailer.send_stats_editis(stats, mel, mois, Provider.find(prov).name, statsbd, statshd, statsdem).deliver_now
      end
    end
  end

  def self.stats_downloads(reference_date = Date.today, string_key = '')
    if (Server.itself? PIXADMIN_SERVER_NAME) || (Server.itself? PP2_SERVER_NAME)
      reference_date = reference_date.instance_of?(String)? Date.parse(reference_date) : Date.parse(reference_date.strftime)
      reference_time = Time.parse(reference_date.strftime)
      time_range = reference_time.prev_week.beginning_of_week..reference_time.prev_week.end_of_week
      week_range = "du #{I18n.l((reference_date.prev_week).beginning_of_week)} au #{I18n.l((reference_date.prev_week).end_of_week)}"
      year_range = reference_time.years_ago(1).beginning_of_week..reference_time.prev_week.end_of_week.end_of_day
      prev_year_week_range = reference_time.years_ago(1).prev_week.beginning_of_week..reference_time.years_ago(1).prev_week.end_of_week
      prev_year_range = reference_time.years_ago(2).beginning_of_week..reference_time.years_ago(1).prev_week.end_of_week.end_of_day
      provs_bd_pixpalace = {}
      provs_hd_pixpalace = {}
      provs_dem_pixpalace = {}
      provs_bd_pixtech = {}
      provs_hd_pixtech = {}
      provs_dem_pixtech = {}
      total_provs_hd_pixpalace = 0
      total_provs_bd_pixpalace = 0
      total_provs_dem_pixpalace = 0
      total_provs_hd_pixtech = 0
      total_provs_bd_pixtech = 0
      total_provs_dem_pixtech = 0
      server_name = Server.find_by_is_self(true).name

      providers_all = string_key.blank? ? Provider.order(:pp_string_key, :name) : Provider.where(string_key: string_key)
      providers_all_id = providers_all.collect{|p| p.id}
      # providers_pixtech = providers_all.where.not(pixtech_id: nil).order(:pp_string_key, :name)
      # providers_pixpalace = providers_all.where(pixtech_id: nil).order(:pp_string_key, :name)
      servers_pixtech = Server.where(which_type: 'PIXTECH').pluck(:id)
      servers_pixpalace = Server.where.not(which_type: 'PIXTECH').pluck(:id)
      titles_pixtech = Title.where(server_id: servers_pixtech).select(:id,:name,:status).order(:name)
      titles_pixpalace = Title.where(server_id: servers_pixpalace).select(:id,:name,:status).order(:name)
      users_clients_all = User.where("title_id not in (select id from titles where name='#{PIXWAYS_TITLE}' or server_id in (select id from servers where name='#{PIXADMIN_SERVER_NAME}')) and roles_mask not in (1,8,16,32,128)")
      users_clients_pixtech = users_clients_all.where(title_id: titles_pixtech.pluck(:id)).pluck(:id)
      users_clients_pixpalace = users_clients_all.where(title_id: titles_pixpalace.pluck(:id)).pluck(:id)
      users_admin_all = User.where("title_id not in (select id from titles where name='#{PIXWAYS_TITLE}') and roles_mask in (1,8,16,32,128)")

      #Stats for each provider
      providers_all.each do |prov|
        for users_set in [users_clients_pixpalace,users_clients_pixtech] do
          stats = Statistic.joins(:image).where(user_id: users_set, images: {provider_id: prov.id}, statistics: {created_at: time_range}).order('created_at desc')
          #stats = Statistic.search with: {user_id: users_set, provider_id: prov.id, created_at: time_range}, order: 'created_at desc'
          statsbd = Statistic.search_count with: {user_id: users_set, provider_id: prov.id, created_at: time_range, operation_label_id: 1}
          statshd = Statistic.search_count with: {user_id: users_set, provider_id: prov.id, created_at: time_range, operation_label_id: 2}
          statsdem = Statistic.search_count with: {user_id: users_set, provider_id: prov.id, created_at: time_range, operation_label_id: 3}
          if users_set==users_clients_pixtech
            servername = 'PixTech'
            provs_hd_pixtech[prov.name] = statshd
            provs_bd_pixtech[prov.name] = statsbd
            provs_dem_pixtech[prov.name] = statsdem
            total_provs_hd_pixtech += statshd
            total_provs_bd_pixtech += statsbd
            total_provs_dem_pixtech += statsdem
          else
            servername = server_name == PP2_SERVER_NAME ? "PixPalace 2" : "PixPalace"
            provs_hd_pixpalace[prov.name] = statshd
            provs_bd_pixpalace[prov.name] = statsbd
            provs_dem_pixpalace[prov.name] = statsdem
            total_provs_hd_pixpalace += statshd
            total_provs_bd_pixpalace += statsbd
            total_provs_dem_pixpalace += statsdem
          end
          #mel = []
          mel = ProviderContact.where(provider_id: prov.id, receive_stat: true).collect{|pc| pc.email}
          unless ( (server_name == PP2_SERVER_NAME && stats.count == 0) || (!prov.pixtech.nil? && prov.provider_type_id==2 && Pixways.get_pixtech_param_value(prov.get_pixtech_name,'send_stats')=='false') || (prov.pixtech.nil? && users_set==users_clients_pixtech && stats.count == 0) || (!prov.pixtech.nil? && prov.provider_type_id==2 && users_set==users_clients_pixpalace && stats.count == 0) )
            #servername = providers_set==providers_pixtech ? 'PixTech' : server_name
            UserMailer.send_stats(stats, mel, prov.name, week_range, statsbd, statshd, statsdem, servername, !prov.pixtech.nil?, prov.get_pixtech_name).deliver_now
            sleep 10 unless prov == providers_all.last
          end
        end
      end

      if string_key.blank? #if individual mail have been asked for one specific provider then no need to send global mails

        #Stats per providers
        for users_set in [users_clients_pixpalace,users_clients_pixtech] do
          total_prov_bd_year = Statistic.search_count with: {user_id: users_set, provider_id: providers_all_id, created_at: year_range, operation_label_id: 1}
          total_prov_hd_year = Statistic.search_count with: {user_id: users_set, provider_id: providers_all_id, created_at: year_range, operation_label_id: 2}
          total_prov_dem_year = Statistic.search_count with: {user_id: users_set, provider_id: providers_all_id, created_at: year_range, operation_label_id: 3}
          total_prov_bd_prev_year = Statistic.search_count with: {user_id: users_set, provider_id: providers_all_id, created_at: prev_year_range, operation_label_id: 1}
          total_prov_hd_prev_year = Statistic.search_count with: {user_id: users_set, provider_id: providers_all_id, created_at: prev_year_range, operation_label_id: 2}
          total_prov_dem_prev_year = Statistic.search_count with: {user_id: users_set, provider_id: providers_all_id, created_at: prev_year_range, operation_label_id: 3}
          total_prov_bd_prev_year_week = Statistic.search_count with: {user_id: users_set, provider_id: providers_all_id, created_at: prev_year_week_range, operation_label_id: 1}
          total_prov_hd_prev_year_week = Statistic.search_count with: {user_id: users_set, provider_id: providers_all_id, created_at: prev_year_week_range, operation_label_id: 2}
          total_prov_dem_prev_year_week = Statistic.search_count with: {user_id: users_set, provider_id: providers_all_id, created_at: prev_year_week_range, operation_label_id: 3}
          if users_set==users_clients_pixtech
            servername = 'PixTech'
            provs_hd = provs_hd_pixtech
            provs_bd = provs_bd_pixtech
            provs_dem = provs_dem_pixtech
            total_provs_hd = total_provs_hd_pixtech
            total_provs_bd = total_provs_bd_pixtech
            total_provs_dem = total_provs_dem_pixtech
          else
            servername = server_name == PP2_SERVER_NAME ? "PixPalace 2" : "PixPalace"
            provs_hd = provs_hd_pixpalace
            provs_bd = provs_bd_pixpalace
            provs_dem = provs_dem_pixpalace
            total_provs_hd = total_provs_hd_pixpalace
            total_provs_bd = total_provs_bd_pixpalace
            total_provs_dem = total_provs_dem_pixpalace
          end
          UserMailer.pp_prov_stats(week_range, provs_hd, provs_bd, provs_dem, total_provs_hd, total_provs_bd, total_provs_dem, total_prov_hd_year, total_prov_bd_year, total_prov_dem_year, total_prov_hd_prev_year, total_prov_bd_prev_year, total_prov_dem_prev_year, total_prov_hd_prev_year_week, total_prov_bd_prev_year_week, total_prov_dem_prev_year_week, servername).deliver_now
          sleep 10
        end

        #Stats per titles
        for titles_set in [titles_pixtech, titles_pixpalace] do
          title_hd = {}
          title_bd = {}
          title_dem = {}
          titles_no_stat = []
          hide_hd = {}
          hide_bd = {}
          hide_dem = {}
          total_title_hd = 0
          total_title_bd = 0
          total_title_dem = 0
          total_hide_hd = 0
          total_hide_bd = 0
          total_hide_dem = 0
          titles_set.each do |title|
            title_users_clients = users_clients_all.where(title_id: title.id).collect{|u| u.id}
            unless title_users_clients.blank?
              t_statshd = Statistic.search_count with: {user_id: title_users_clients, created_at: time_range, operation_label_id: 2}
              t_statsbd = Statistic.search_count with: {user_id: title_users_clients, created_at: time_range, operation_label_id: 1}
              t_statsdem = Statistic.search_count with: {user_id: title_users_clients, created_at: time_range, operation_label_id: 3}
              if t_statshd == 0 && t_statsbd == 0 && t_statsdem == 0
                titles_no_stat << title.name if titles_set != titles_pixtech && title.status == 'active'
              else
                title_hd[title.name] = t_statshd
                title_bd[title.name] = t_statsbd
                title_dem[title.name] = t_statsdem
                total_title_hd += t_statshd
                total_title_bd += t_statsbd
                total_title_dem += t_statsdem
              end
            end

            #users = User.where('title_id = ? and roles_mask in (?,?,?,?)',title.id,32,16,8,1).collect{|u| u.id}
            title_users_admin = users_admin_all.where(title_id: title.id).collect{|u| u.id}
            unless title_users_admin.blank?
              t_hidehd = Statistic.search_count with: {user_id: title_users_admin, created_at: time_range, operation_label_id: 2}
              t_hidebd = Statistic.search_count with: {user_id: title_users_admin, created_at: time_range, operation_label_id: 1}
              t_hidedem = Statistic.search_count with: {user_id: title_users_admin, created_at: time_range, operation_label_id: 3}
              unless t_hidehd == 0 && t_hidebd == 0 && t_hidedem == 0
                hide_hd[title.name] = t_hidehd
                hide_bd[title.name] = t_hidebd
                hide_dem[title.name] = t_hidedem
                total_hide_hd += t_hidehd
                total_hide_bd += t_hidebd
                total_hide_dem += t_hidedem
              end
            end
          end
          servername = titles_set == titles_pixtech ? 'PixTech' : (server_name == PP2_SERVER_NAME ? "PixPalace 2" : "PixPalace")
          UserMailer.pp_title_stats(week_range, title_hd, title_bd, title_dem, total_title_hd, total_title_bd, total_title_dem, servername).deliver_now unless ((server_name == PP2_SERVER_NAME) && (servername == 'PixTech'))
          UserMailer.pp_title_no_stat(week_range, titles_no_stat, servername).deliver_now unless servername == 'PixTech'
          UserMailer.pp_hide_stats(week_range, hide_hd, hide_bd, hide_dem, total_hide_hd, total_hide_bd, total_hide_dem, servername).deliver_now unless ((server_name == PP2_SERVER_NAME) && (total_hide_dem == 0 || servername == 'PixTech'))
          sleep 10
        end

      end

      #Stats archive
      if server_name == PIXADMIN_SERVER_NAME
        Statistic.where(created_at: time_range).find_each do |s|
          ArchStat.create(user_id: s.user_id, image_id: s.image_id, created_at: s.created_at, updated_at: s.updated_at, operation_label_id: s.operation_label_id)
        end
      end

    end
  end

  def self.send_to_backup
    if Server.itself? VINGTMINUTES_SERVER_NAME
      yesterday = (Date.today.yesterday.beginning_of_day)..(Date.today.yesterday.end_of_day)
      ic = 0
      puts "**** send to backup #{yesterday}"
      config_db_ms = Rails.configuration.database_configuration['20minutes_backup']
      client = Mysql2::Client.new(:host => config_db_ms["host"],:database => config_db_ms["database"],:username => config_db_ms["username"], :password => config_db_ms["password"])

      Statistic.joins(:image).joins(:user).where(statistics: {created_at: yesterday}).select('images.ms_image_id as im_id, images.ms_id as ms_id, users.login as login, statistics.operation_label_id as opl, statistics.created_at as sdate, statistics.id20min as id20').each do |s|
        im_id = 0
        user_id = 0
        str20 = nil
        client.query("select id from images where ms_image_id='#{s.im_id}' and ms_id='#{s.ms_id}'").each do |row|
          im_id = row["id"]
          unless s.id20.blank?
            str20 = s.id20
            client.query("update images set id20min='#{str20}' where id=#{im_id}")
          end
        end
        if im_id == 0
          puts "image non trouvee : #{s.im_id} / #{s.ms_id}"
        else
          client.query("select id from users where login='#{s.login}'").each do |row|
            user_id = row['id']
          end
          if user_id == 0
            puts "utilisateur inconnu : #{s.login}"
          else
            client.query("INSERT INTO statistics(user_id, image_id, created_at, operation_label_id, id20min) VALUES (#{user_id.to_i}, #{im_id.to_i}, '#{s.sdate}', #{s.opl}, '#{str20}')")
            ic += 1
          end
        end
      end
      client.close
      puts "*** #{ic} stats envoyees"
    end
  end

  def self.send_to_pixtrak
    if Server.itself? PIXADMIN_SERVER_NAME
      yesterday = (Time.now.midnight - 1.day)..(Time.now.end_of_day - 1.day)
      op_label = ["","BD","HD"]
      ic = 0
      puts "**** send to Pixtrak #{yesterday}"
      config_db_ms = Rails.configuration.database_configuration['pixtrakk_print']
      client = Mysql2::Client.new(:host => config_db_ms["host"],:port => config_db_ms["port"],:database => config_db_ms["database"],:username => config_db_ms["username"], :password => config_db_ms["password"])

      Statistic.joins(image: :provider).joins(:user).where(statistics: {created_at: yesterday}, users: {roles_mask: 4}).select('images.ms_image_id as ms_id, images.original_filename as ori_fn, users.title_id as tid, providers.string_key as stringkey, statistics.operation_label_id as opl, statistics.created_at as sdate').each do |s|
        ori = s.ori_fn.gsub("'",'_').gsub(" ","_")
        row =  client.query("INSERT INTO images_downloads (ms_picture_id, original_filename, publication_id, string_key, operation_label, downloaded_date, created_at) VALUES (#{s.ms_id}, '#{ori}', #{s.tid}, '#{s.stringkey}', '#{op_label[s.opl]}', '#{s.sdate}', NOW())")
        ic += 1
      end
      client.close
      puts "*** #{ic} stats envoyees"
    end
  end

  def self.send_bd_to_pixtrak
    if Server.itself? PIXADMIN_SERVER_NAME
      threeday = (Time.now.midnight - 3.day)..(Time.now.end_of_day - 3.day)
      prov = []
      ic = 0
      puts "**** send to Pixtrak #{threeday}"
      Provider.where(in_pixtrakk: true).collect{|p| prov << p.id}
      Statistic.joins(image: :provider).joins(:user).where(statistics: {created_at: threeday}, images: {provider_id: prov}, users: {roles_mask: 4}).select('images.file_name as f_n, images.medium_location as med_loc').each do |s|
        file = "public#{s.med_loc}"
        ftp_config = YAML.load_file(File.join(Rails.root, 'config', 'ftp.yml'))
        ftp = Net::FTP.new(ftp_config[:ms][:host],ftp_config[:ms][:temp_access][:user], ftp_config[:ms][:temp_access][:passwd])
        ftp.passive = ftp_config[:ms][:passive]
        ftp.chdir('PIXTRAKKCRAWLER')
        ftp.putbinaryfile(file, s.f_n)
        ftp.close
        ic += 1
      end
      puts "*** #{ic} BD envoyes"
    end
  end

  def self.params_to_conditions(params, creator='')
    conditions = {}
    params.each do |k,v|
      conditions[k.to_sym] = Pixways::SearchesHelper.filter_keywords(v) if ( !v.blank? && Image.searchable_fields.include?(k.to_sym) )
    end
    conditions[:creator] = creator unless creator.blank?
    conditions
  end

  def self.params_to_with(params, provs, pays)
    with = {}

    authorized_providers = provs

    providers_list = params[:providers].nil? || params[:providers].count == 0 ? authorized_providers : params[:providers].keys.collect!{|e| e.to_i}
    with[:provider_id] = providers_list

    if params[:customers_only]
      with[:user_id] = User.where("roles_mask not in (1,8,16,32,128) and title_id not in (select id from titles where name='#{PIXWAYS_TITLE}' or server_id in (select id from servers where name='#{PIXADMIN_SERVER_NAME}'))").collect{|u| u.id}
    else
      with[:user_id] = User.all.collect{|u| u.id}
    end

    authorized_titles = []
    if pays == 0
      authorized_titles = Title.all.collect{|t| t.id}
    else
      authorized_titles = Title.where(country_id: pays).collect{|t| t.id}
    end
    with[:title_id] = params[:titles].nil? || params[:titles].count == 0 ? authorized_titles : authorized_titles & params[:titles].keys.collect!{|e| e.to_i}

    with[:updated_at] = Pixways::SearchesHelper.dates_to_range('',params[:search_time]) if params[:search_time]
    if params[:search_since] && params[:search_since] != 'all'
      with[:created_at] = Statistic.since_conditions(params[:search_since])
    end

    with[:created_at] = Pixways::SearchesHelper.dates_to_range(params[:reception_date_begin], params[:reception_date_end]) unless (params[:reception_date_begin].blank? && params[:reception_date_end].blank?)
    with[:operation_label_id] = params[:operation_label_id] if params[:operation_label_id] && params[:operation_label_id].to_i > 0
    with
  end

  private

  sphinx_scope(:by_original_filename) {
    {:order =>'original_filename ASC'}
  }

  sphinx_scope(:by_created_at) {
    {:order => 'created_at DESC'}
  }

  sphinx_scope(:by_operation_label_id) {
    {:order => 'operation_label_id ASC' }
  }

  sphinx_scope(:by_title_name) {
    {:order => 'title_name ASC' }
  }

  sphinx_scope(:by_user_login) {
    {:order => 'user_login ASC' }
  }

  sphinx_scope(:by_provider_name) {
    {:order => 'provider_name ASC' }
  }

  sphinx_scope(:by_creator) {
    {:order => 'creator ASC' }
  }

  sphinx_scope(:by_headline) {
    {:order => 'headline ASC' }
  }


  def self.since_conditions(since_txt)
    #to_i is automagicaly converting time, the only way to dispell magic I found is this
    current_time = Time.now
    current_time -= current_time.utc_offset
    upper_bound = 2145913199
    case since_txt
    when '10_minutes'
      (current_time - 10.minutes).to_i..upper_bound
    when '1_hour'
      (current_time - 1.hours).to_i..upper_bound
    when '1_day'
      (current_time - 1.day).to_i..upper_bound
    when '1_week'
      (current_time - 1.weeks).to_i..upper_bound
    when '1_month'
      (current_time - 1.months).to_i..upper_bound
    when '3_month'
      (current_time - 3.months).to_i..upper_bound
    when '1_year'
      (current_time - 1.year).to_i..upper_bound
    end
  end

end