<?php
namespace factory;

class Ranker
{
  static public function sites_to_confirm()
  {
    global $mdb;
    return $mdb -> select( 'pro_rr_sites', [ 'id', 'name' ], [ 'AND' => [ 'date_confirm[<]' => date( 'Y-m-d' ), 'need_confirm' => 1 ], 'ORDER' => [ 'name' => 'ASC' ] ] );
  }

  static public function get_domain_by_id( $site_id )
  {
    global $mdb;
    return $mdb -> get( 'pro_rr_sites', 'url', [ 'id' => $site_id ] );
  }

  public static function delete_site_comment_info( $site_id, $date )
  {
    global $mdb;

    return $mdb -> delete( 'pro_rr_sites_comments', [
                      'AND' => [
                        'site_id' => $site_id,
                        'date'    => $date
                      ]
                    ] );
  }

  public static function change_site_comment_info( $site_id, $date, $comment )
  {
    global $mdb;

    if ( $mdb -> count( 'pro_rr_sites_comments', [ 'AND' => [ 'site_id' => $site_id, 'date' => $date  ] ] ) )
    {
      return $mdb -> update( 'pro_rr_sites_comments', [
                      'comment' => $comment
                    ], [
                      'AND' => [
                        'site_id' => $site_id,
                        'date'    => $date
                      ]
                    ] );
    }
    else
    {
      $mdb -> insert( 'pro_rr_sites_comments', [
                      'comment' => $comment,
                      'site_id' => $site_id,
                      'date'    => $date
                    ] );
      return $mdb -> id();
    }
  }

  public static function uule($city)
  {
    $secretkey = array_merge(range('A','Z'), range('a','z'), range('0','9'), array('-', '_'));
    return trim('w+CAIQICI'.$secretkey[strlen($city)%count($secretkey)].base64_encode($city), '=');
  }

  public static function semstorm( $site_id )
  {
    global $mdb;
    return $mdb -> get( 'pro_rr_sites_semstorm', '*', [ 'site_id' => $site_id, 'ORDER' => [ 'date' => 'DESC' ] ] );
  }

  public static function majestic( $site_id )
  {
    global $mdb;
    return $mdb -> get( 'pro_rr_sites_majestic', '*', [ 'site_id' => $site_id, 'ORDER' => [ 'date' => 'DESC' ] ] );
  }

  public static function get_latitude( $localization )
  {
    switch ( $localization )
    {
      case 'Polska':
        return '51.9358377';
      break;
      case 'Rzeszow,Podkarpackie Voivodeship,Poland':
      default:
        return '50.0413200';
      break;
      case 'Częstochowa':
        return '50.817777777778';
      break;
      case 'Krosno':
        return '49.682222222222';
      break;
      case 'Jasło':
        return '49.747777777778';
      break;
      case 'Warszawa':
        return '52.232222222222';
      break;
      case 'Wyszków':
        return '52.593055555556';
      break;
      case 'Krakow,Lesser Poland Voivodeship,Poland':
        return '50.061388888889';
      break;
      case 'Wieliczka':
        return '49.986111111111';
      break;
      case 'Warsaw,Masovian Voivodeship,Poland';
        return '52.232222222222';
      break;
      case 'Zielona Gora,Lubusz Voivodeship,Poland';
        return '51.9354800';
      break;
    }
  }

  public static function get_longtitude( $localization )
  {
    switch ( $localization )
    {
      case 'Polska':
        return '16.8921118';
      break;
      case 'Rzeszow,Podkarpackie Voivodeship,Poland':
      default:
        return '21.9990100';
      break;
      case 'Częstochowa':
        return '19.138333333333';
      break;
      case 'Krosno':
        return '21.765833333333';
      break;
      case 'Jasło':
        return '21.471388888889';
      break;
      case 'Warszawa':
        return '21.008333333333';
      break;
      case 'Wyszków':
        return '21.459166666667';
      break;
      case 'Krakow,Lesser Poland Voivodeship,Poland':
        return '19.938333333333';
      break;
      case 'Wieliczka':
        return '20.061666666667';
      break;
      case 'Warsaw,Masovian Voivodeship,Poland';
        return '21.008333333333';
      break;
      case 'Zielona Gora,Lubusz Voivodeship,Poland';
        return '15.5064300';
      break;
    }
  }

  public static function site_confirm( $site_id )
  {
    global $mdb;
    return $mdb -> update( 'pro_rr_sites', [ 'date_confirm' => date( 'Y-m-d' ) ], [ 'id' => $site_id ] );
  }

  public static function site_monthly_profit( $site_id, $month, $year, $date_sale )
  {
    $site = \factory\Ranker::getSiteSummary( $site_id, $month, $year );

    if ( $site['date_end'] != null and date( 'Y-m' ) < date( 'Y-m-', strtotime( $date_sale ) ) )
      $summary = \S::number( $site['subscription'] / date( 't', strtotime( $site['date_end'] ) ) * date( 'j', strtotime( $site['date_end'] ) ) );
    else
      $summary = $site['subscription'];

    if ( is_array( $site['phrases'] ) ) foreach ( $site['phrases'] as $phrase )
    {
      $discount = '';

      if ( $site['discount'] )
        $discount = $site['discount'];

      if ( $phrase['discount'] )
        $discount = $phrase['discount'];

      if ( is_array( $phrase['prices'] ) )
      {
        if ( is_array( $phrase['prices'] ) )
        {
          foreach ( $phrase['prices'] as $price )
          {
            $price_c  = \S::number( $price['price'] - \S::number( $price['price'] * $discount / 100 ) );
            $summary  = $summary + $price_c * $price['count'];
          }
        }
      }
    }
    return $summary;
  }

  public static function site_name( $site_id )
  {
    global $mdb;
    return $mdb -> get( 'pro_rr_sites', 'name', [ 'id' => $site_id ] );
  }

  public static function last_login_client( $site_id )
  {
    global $mdb;

    $results = $mdb -> query( 'SELECT '
                                  . 'login, last_logged '
                                . 'FROM '
                                  . 'pro_rr_clients_sites AS prcs '
                                    . 'INNER JOIN pro_rr_clients AS prc ON prc.id = prcs.client_id  '
                                . 'WHERE '
                                  . 'site_id = ' . (int)$site_id ) -> fetchAll();
    if ( is_array( $results ) ) foreach ( $results as $row )
    {
      if ( $row['login'] == 'mind-vision' || $row['login'] == 'pietrynko.beata' )
        continue;

      if ( $out )
        $out .= ', ';

      $out .= $row['login'] . ' - <span class="';
        if ( date( 'Y-m-d' ) == date( 'Y-m-d', strtotime( $row['last_logged'] ) ) )
          $out .= ' text-danger';
      $out .= '" style="font-weight: 600;">' . date( 'Y-m-d H:i', strtotime( $row['last_logged'] ) ) . '</span>';
    }

    return $out;
  }

  public function createPdfReport( $sites, $report_form, $date_from, $date_to )
  {
    global $db;

    if ( is_array( $sites ) ) foreach ( $sites as $site )
    {
      $phrases = array();

      $query = $db -> prepare( 'SELECT * FROM pro_rr_phrases WHERE site_id = :site_id ORDER BY phrase ASC' );
      $query -> bindValue( ':site_id', $site, \PDO::PARAM_INT );
      $query -> execute();
      if ( $query -> rowCount() ) while ( $row = $query -> fetch() )
      {
        $positions  = array();
        $costs      = array();

        $query2 = $db -> prepare( 'SELECT * FROM pro_rr_phrases_positions WHERE phrase_id = :phrase_id AND date <= "' . $date_to . '" AND date >= "' . $date_from . '" GROUP BY date ORDER BY date ASC' );
        $query2 -> bindValue( ':phrase_id', $row['id'], \PDO::PARAM_INT );
        $query2 -> execute();
        if ( $query2 -> rowCount() ) while ( $row2 = $query2 -> fetch() )
        {
          $positions[$row2['date']]  = $row2['position'];
          $costs[] = $row2['position'];
        }
        $query2 -> closeCursor();

        $prices = self::getPhrasePrices( $row['id'] );
        for ( $i = 0; $i < count( $prices ); $i++ )
        {
          foreach ( $costs as $cost )
          {
            if ( $cost >= $prices[$i]['start'] && $cost <= $prices[$i]['end'] )
              $prices[$i]['count']++;
          }
        }

        $row['prices']    = $prices;
        $row['positions'] = $positions;
        $phrases[] = $row;
      }
      $query -> closeCursor();

      $query2 = $db -> prepare( 'SELECT url, discount FROM pro_rr_sites WHERE id = :id' );
      $query2 -> bindValue( ':id', $site, \PDO::PARAM_INT );
      $query2 -> execute();
      if ( $query2 -> rowCount() ) while ( $row2 = $query2 -> fetch() )
      {
        $page['url']      = $row2['url'];
        $page['discount'] = $row2['discount'];
      }
      $query2 -> closeCursor();

      $page['id']       = $page_id;
      $page['phrases']  = $phrases;
      $pages[]          = $page;
    }

    $tpl = new \Savant3;
    $tpl -> _pages        = $pages;
    $tpl -> _date_from    = $date_from;
    $tpl -> _date_to      = $date_to;
    $tpl -> _report_form  = $report_form;
    $out = $tpl -> fetch( 'ranker/reports-pdf' );

    define( "_MPDF_TEMP_PATH", 'temp/' );
    include( "resources/mpdf60/mpdf.php" );

    $link = 'temp_t/raport-pozycji-' . mktime() . '.pdf';

    $mpdf = new \mPDF( '', 'A4-L', '', '', 5, 5, 5, 5, 5, 5, 'L' );
    $mpdf -> AddPage('','','','','on');
    $mpdf -> WriteHTML( $out );
    $mpdf -> Output( $link, 'F' );
    \S::alert( 'Link do raportu: <a href="./?rw=download&file=' . $link . '" style="color: #000;">pobierz</a>' );
  }

  public function getSiteUrl( $id )
  {
    global $db;

    $query = $db -> prepare( 'SELECT url FROM pro_rr_sites WHERE id = :id' );
    $query -> bindValue( ':id' , $id , \PDO::PARAM_INT );
    $query -> execute();
    if ( $query -> rowCount() ) while ( $row = $query -> fetch() )
      return $row['url'];
    $query -> closeCursor();
    return false;
  }

  public function getClientsSitesSummary( $month, $year )
  {
    global $db;

    $client['login'] = '--- brak użytkownika ---';

    $query = $db -> prepare( 'SELECT id FROM pro_rr_sites WHERE id NOT IN ( SELECT site_id FROM pro_rr_clients_sites WHERE id != 24 ) AND name NOT LIKE \'* %\' ORDER BY name ASC' );
    $query -> bindValue( ':client_id', $row['id'], \PDO::PARAM_INT );
    $query -> execute();
    if ( $query -> rowCount() ) while ( $row = $query -> fetch() )
      $client['sites'][] =  self::getSiteSummary( $row['id'], $month, $year );
    $query -> closeCursor();

    $clients[] = $client;

    $query = $db -> prepare( 'SELECT COUNT( site_id ) AS sites_count, prc.id, login, type FROM pro_rr_clients AS prc, pro_rr_clients_sites AS prcs WHERE prc.id != 24 AND prc.id = prcs.client_id AND reseller_id IS NULL GROUP BY client_id ORDER BY sites_count DESC, login ASC' );
    $query -> execute();
    if ( $query -> rowCount() ) while ( $row = $query -> fetch() )
    {
      $sites = array();

      $query2 = $db -> prepare( 'SELECT site_id FROM pro_rr_clients_sites AS prcs, pro_rr_sites AS prs WHERE client_id = :client_id AND prs.id = prcs.site_id ORDER BY name ASC' );
      $query2 -> bindValue( ':client_id', $row['id'], \PDO::PARAM_INT );
      $query2 -> execute();
      if ( $query2 -> rowCount() ) while ( $row2 = $query2 -> fetch() )
        $sites[] =  self::getSiteSummary( $row2['site_id'], $month, $year );
      $query2 -> closeCursor();

      $row['sites'] = $sites;
      $clients[] = $row;
    }

    return $clients;
  }

  public function getSiteSummary( $id, $month, $year, $reseller_id = false, $user_login = '' )
  {
    global $db;

    $query = $db -> prepare( 'SELECT * FROM pro_rr_sites WHERE id = :id' );
    $query -> bindValue( ':id', $id, \PDO::PARAM_INT );
    $query -> execute();
    if ( $query -> rowCount() ) while ( $row = $query -> fetch() )
    {
      $row['phrases'] = self::getSitePhrases( $row['id'], $month, $year, false, $reseller_id, $user_login ) ;
      return $row;
    }
    $query -> closeCursor();
    return false;
  }

  public function deleteClient( $id )
  {
    global $db;

    $query = $db -> prepare( 'DELETE FROM pro_rr_clients_sites WHERE client_id = :client_id' );
    $query -> bindValue( ':client_id',  $id,  \PDO::PARAM_INT );
    $query -> execute();
    $query -> closeCursor();

    $query = $db -> prepare( 'DELETE FROM pro_rr_clients WHERE id = :id' );
    $query -> bindValue( ':id', $id, \PDO::PARAM_INT );
    $query -> execute();
    if ( $query -> rowCount() )
      return true;
    $query -> closeCursor();
    return false;
  }

  public function saveClient( $id, $login, $password, $sites, $enabled, $type )
  {
    global $db;

    if ( !$id || !$login )
      return false;

    $enabled == 'on' ? $enabled = 1 : $enabled = 0;

    $query = $db -> prepare( 'UPDATE pro_rr_clients SET login = :login, enabled = :enabled, type = :type WHERE id = :id' );
    $query -> bindValue( ':login',      $login,      \PDO::PARAM_STR );
    $query -> bindValue( ':enabled',    $enabled,    \PDO::PARAM_INT );
    $query -> bindValue( ':id',         $id,         \PDO::PARAM_INT );
    $query -> bindValue( ':type',       $type,       \PDO::PARAM_INT );
    $query -> execute();

    if ( $password )
    {
      $query = $db -> prepare( 'UPDATE pro_rr_clients SET password = :password WHERE id = :id' );
      $query -> bindValue( ':password',   $password, \PDO::PARAM_STR );
      $query -> bindValue( ':id',         $id,       \PDO::PARAM_INT );
      $query -> execute();
    }

    $query = $db -> prepare( 'DELETE FROM pro_rr_clients_sites WHERE client_id = :client_id' );
    $query -> bindValue( ':client_id', $id, \PDO::PARAM_INT );
    $query -> execute();
    $query -> closeCursor();

    if ( is_array( $sites ) )
    {
      $query = $db -> prepare( 'INSERT INTO pro_rr_clients_sites ( client_id, site_id ) VALUES ( :client_id, :site_id )' );
      foreach ( $sites as $site )
      {
        $query -> bindValue( ':client_id',  $id,    \PDO::PARAM_INT );
        $query -> bindValue( ':site_id',    $site,  \PDO::PARAM_INT );
        $query -> execute();
      }
      $query -> closeCursor();
    }
    return true;
  }

  public function addClient( $login, $password, $sites, $enabled, $type )
  {
    global $db;

    if ( !$login || !$password )
      return false;

    $enabled == 'on' ? $enabled = 1 : $enabled = 0;

    $query = $db -> prepare( 'INSERT INTO pro_rr_clients ( login, password, enabled, type ) VALUES ( :login, :password, :enabled, :type )' );
    $query -> bindValue( ':password',   $password,            \PDO::PARAM_STR );
    $query -> bindValue( ':login',      strtolower( $login ), \PDO::PARAM_STR );
    $query -> bindValue( ':enabled',    $enabled,             \PDO::PARAM_INT );
    $query -> bindValue( ':type',       $type,                \PDO::PARAM_INT );
    $query -> execute();

    $client_id = $db -> lastInsertId();

    if ( is_array( $sites ) )
    {
      $query = $db -> prepare( 'INSERT INTO pro_rr_clients_sites ( client_id, site_id ) VALUES ( :client_id, :site_id )' );
      foreach ( $sites as $site )
      {
        $query -> bindValue( ':client_id',  $client_id, \PDO::PARAM_INT );
        $query -> bindValue( ':site_id',    $site,      \PDO::PARAM_INT );
        $query -> execute();
      }
      $query -> closeCursor();
    }
    \S::set_session( 'check', $check );
    return true;
  }

  public function getClient( $id )
  {
    global $db;

    $query = $db -> prepare( 'SELECT * FROM pro_rr_clients WHERE id = :id' );
    $query -> bindValue( ':id', $id, \PDO::PARAM_INT );
    $query -> execute();
    if ( $query -> rowCount() ) while ( $row = $query -> fetch() )
    {
      $query2 = $db -> prepare( 'SELECT site_id FROM pro_rr_clients_sites WHERE client_id = :client_id' );
      $query2 -> bindValue( ':client_id', $id, \PDO::PARAM_INT );
      $query2 -> execute();
      if ( $query2 -> rowCount() ) while ( $row2 = $query2 -> fetch() )
        $sites[] = $row2['site_id'];
      $query2 -> closeCursor();

      $row['sites'] = $sites;
      $client = $row;
    }
    $query -> closeCursor();

    return $client;
  }

  public function day_profit()
  {
    global $mdb, $config;

    if ( $config['phrases']['admin_confirm'] )
    {
      $result = $mdb -> count( 'pro_rr_sites', [ 'AND' => [ 'date_confirm[<]' => date( 'Y-m-d' ), 'need_confirm' => 1 ] ] );
      if ( $result )
        return '<span class="text-danger">W trakcie sprawdzania. Zostało <b>' . $result . '</b> stron.</span>';
    }

    $phrases = array();

    $results = $mdb -> select( 'pro_rr_sites', [ 'id', 'discount' ], [ 'ORDER' => [ 'id' => 'ASC' ] ] );
    if ( is_array( $results ) ) foreach ( $results as $row )
    {
      $results2 = $mdb -> query( 'SELECT id, phrase, discount, date_end, date_start FROM pro_rr_phrases WHERE site_id = ' . $row['id'] . ' AND id IN ( SELECT DISTINCT(phrase_id) FROM pro_rr_phrases_prices )' ) -> fetchAll( \PDO::FETCH_ASSOC );
      if ( is_array( $results2 ) ) foreach ( $results2 as $row2 )
      {
        $row2['discount'] ? $discount = $row2['discount'] : $discount = $row['discount'];

        $prices     = array();
        $positions  = array();

        $results3 = $mdb -> select( 'pro_rr_phrases_positions', [ 'position', 'date' ], [ 'AND' => [ 'phrase_id' => $row2['id'], 'date' => date( 'Y-m-d' ) ] ] );
        if ( is_array( $results3 ) ) foreach ( $results3 as $row3 )
          $positions[] = $row3['position'];

        $results3 = $mdb -> select( 'pro_rr_phrases_prices', [ 'start', 'end', 'price' ], [ 'AND' => [ 'phrase_id' => $row2['id'], 'reseller_id' => null ] ] );
        if ( is_array( $results3 ) ) foreach ( $results3 as $row3 )
          $prices[] = $row3;

        if ( is_array( $positions ) ) foreach ( $positions as $position )
        {
          if ( is_array( $prices ) ) foreach ( $prices as $price )
          {
            if ( $position >= $price['start'] and $position <= $price['end'] )
              $summary += \S::number( $price['price'] - \S::number( $price['price'] * $discount / 100 ) );
          }
        }
      }
    }

    $day_profit = \S::number_display( $summary );

    return $day_profit;
  }

  public function getMonthProfit( $month, $year )
  {
    global $mdb, $config;

    if ( $config['phrases']['admin_confirm'] )
    {
      $result = $mdb -> count( 'pro_rr_sites', [ 'OR' => [ 'date_confirm[<]' => date( 'Y-m-d' ), 'need_confirm' => 0 ] ] );
      if ( $result )
        return '-';
    }

    $phrases = array();

    $results = $mdb -> select( 'pro_rr_sites', [ 'id', 'name', 'discount', 'subscription', 'date_start', 'date_end', 'subscription' ], [ 'ORDER' => [ 'id' => 'ASC' ] ] );
    if ( is_array( $results ) ) foreach ( $results as $row )
    {
      $results2 = $mdb -> query( 'SELECT id, phrase, discount, date_end, date_start FROM pro_rr_phrases WHERE site_id = ' . $row['id'] . ' AND id IN ( SELECT DISTINCT(phrase_id) FROM pro_rr_phrases_prices )' ) -> fetchAll( \PDO::FETCH_ASSOC );
      if ( is_array( $results2 ) ) foreach ( $results2 as $row2 )
      {
        $row2['discount'] ? $discount = $row2['discount'] : $discount = $row['discount'];

        $prices     = array();
        $positions  = array();

        $results3 = $mdb -> select( 'pro_rr_phrases_positions', [ 'position', 'date' ], [ 'AND' => [ 'phrase_id' => $row2['id'], 'date[~]' => $year . '-' . $month . '-__' ], 'GROUP' => 'date' ] );
        if ( is_array( $results3 ) ) foreach ( $results3 as $row3 )
          $positions[] = $row3['position'];

        $results3 = $mdb -> select( 'pro_rr_phrases_prices', [ 'start', 'end', 'price' ], [ 'AND' => [ 'phrase_id' => $row2['id'], 'reseller_id' => null ] ] );
        if ( is_array( $results3 ) ) foreach ( $results3 as $row3 )
          $prices[] = $row3;

        if ( is_array( $positions ) ) foreach ( $positions as $position )
        {
          if ( is_array( $prices ) ) foreach ( $prices as $price )
          {
            if ( $position >= $price['start'] and $position <= $price['end'] )
              $summary += \S::number( $price['price'] - \S::number( $price['price'] * $discount / 100 ) );
          }
        }
      }

      if ( $row['date_start'] and date( 'Y-m', strtotime( $row['date_start'] ) ) == date( 'Y-m', strtotime( $year . '-' . $month . '-01' ) ) )
      {
        $day_count  = date( 't', strtotime( $year . '-' . $month . '-01' ) );
        $day_cost   = \S::number( $row['subscription'] / $day_count );
        $summary    += \S::number( ( $day_count - date( 'd', strtotime( $row['date_start' ] ) ) + 1 ) * $day_cost );
      }
      else if ( $row['date_start'] and date( 'Y-m', strtotime( $row['date_start'] ) ) > date( 'Y-m', strtotime( $year . '-' . $month . '-01' ) ) )
      {

      }
      else
      {
        if ( $row['date_end'] )
        {
          if ( $row['date_end'] == date( 'Y-m-t', strtotime( '-1 months', strtotime( $year . '-' . $month . '-01' ) ) ) )
          {
            /* nie dodawaj abonamentu */
          }
          else if ( date( 'Y-m', strtotime( $row['date_end'] ) ) == date( 'Y-m', strtotime( $year . '-' . $month . '-01' ) ) )
          {
            if ( $row['date_end'] < date( 'Y-m-t', strtotime( $year . '-' . $month . '-01' ) ) )
            {
              $day_count  = date( 't', strtotime( $year . '-' . $month . '-01' ) );
              $day_cost   = \S::number( $row['subscription'] / $day_count );
              $summary    += \S::number( date( 'd', strtotime( $row['date_end'] ) ) * $day_cost );
            }
            else
            {
              $summary += $row['subscription'];
            }
          }
        }
        else
          $summary += $row['subscription'];
      }
    }

    $profit = \S::number_display( $summary );

    return $profit;
  }

  public function getPhrasePrices( $phrase_id, $reseller_id = '' )
  {
    global $db;

    if ( $reseller_id )
      $sql .= ' AND reseller_id = :reseller_id';
    else
      $sql .= ' AND reseller_id IS NULL';

    $query = $db -> prepare( 'SELECT * FROM pro_rr_phrases_prices WHERE phrase_id = :phrase_id ' . $sql . ' ORDER BY start ASC' );
    $query -> bindValue( ':phrase_id', $phrase_id, \PDO::PARAM_INT );
    if ( $reseller_id )
      $query -> bindValue( ':reseller_id', $reseller_id, \PDO::PARAM_INT );
    $query -> execute();
    if ( $query -> rowCount() ) while ( $row = $query -> fetch() )
      $prices[] = $row;
    $query -> closeCursor();

    return $prices;
  }

  public function changePhrasePosition( $phrase_id, $date, $position )
  {
    global $db;

    $query = $db -> prepare( 'SELECT id FROM pro_rr_phrases_positions WHERE phrase_id = :phrase_id AND date = :date' );
    $query -> bindValue( ':phrase_id',  $phrase_id, \PDO::PARAM_INT );
    $query -> bindValue( ':date',       $date,      \PDO::PARAM_STR );
    $query -> execute();
    if ( $query -> rowCount() ) while ( $row = $query -> fetch() )
    {
      $query2 = $db -> prepare( 'UPDATE pro_rr_phrases_positions SET position = :position WHERE id = :id' );
      $query2 -> bindValue( ':id',       $row['id'], \PDO::PARAM_INT );
      $query2 -> bindValue( ':position', $position,  \PDO::PARAM_INT );
      $query2 -> execute();
      $query2 -> closeCursor();
    }
    else
    {
      $query2 = $db -> prepare( 'INSERT INTO pro_rr_phrases_positions ( phrase_id, date, position ) VALUES ( :phrase_id, :date, :position )' );
      $query2 -> bindValue( ':phrase_id',   $phrase_id, \PDO::PARAM_INT );
      $query2 -> bindValue( ':date',        $date,      \PDO::PARAM_STR );
      $query2 -> bindValue( ':position',    $position,  \PDO::PARAM_INT );
      $query2 -> execute();
      $query2 -> closeCursor();
    }
    $query -> closeCursor();
  }

  public function refreshPhrase( $id )
  {
    global $db;

    $query = $db -> prepare( 'DELETE FROM pro_rr_phrases_positions WHERE phrase_id = :phrase_id AND date = "' . date( 'Y-m-d' ) . '"' );
    $query -> bindValue( ':phrase_id', $id, \PDO::PARAM_INT );
    $query -> execute();
    $query -> closeCursor();

    $query = $db -> prepare( 'UPDATE pro_rr_phrases SET last_checked = "2012-01-01", check_current = 0 WHERE id = :id' );
    $query -> bindValue( ':id', $id, \PDO::PARAM_INT );
    $query -> execute();
    $query -> closeCursor();

    return true;
  }

  static public function phrase_delete( $phrase_id )
  {
    global $mdb;

    $mdb -> delete( 'pro_rr_phrases_positions', [ 'phrase_id' => $phrase_id ] );
    $mdb -> delete( 'pro_rr_phrases_prices', [ 'phrase_id' => $phrase_id ] );
    $mdb -> delete( 'pro_rr_phrases_reseller', [ 'phrase_id' => $phrase_id ] );
    $mdb -> delete( 'pro_rr_phrases_reseller', [ 'phrase_id' => $phrase_id ] );

    return $mdb -> delete( 'pro_rr_phrases', [ 'id' => $phrase_id ] );
  }

  public function getPhrase( $id, $reseller_id = false )
  {
    global $db;

    $query = $db -> prepare( 'SELECT * FROM pro_rr_phrases WHERE id = :id' );
    $query -> bindValue( ':id', $id, \PDO::PARAM_INT );
    $query -> execute();
    if ( $query -> rowCount() ) while ( $row = $query -> fetch() )
    {
      if ( $reseller_id )
      {
        $query2 = $db -> prepare( 'SELECT discount FROM pro_rr_phrases_reseller WHERE phrase_id = :phrase_id AND reseller_id = :reseller_id' );
        $query2 -> bindValue( ':reseller_id', $reseller_id, \PDO::PARAM_INT );
        $query2 -> bindValue( ':phrase_id',   $id,          \PDO::PARAM_INT );
        $query2 -> execute();
        if ( $query2 -> rowCount() )
        {
          while ( $row2 = $query2 -> fetch() )
            $row['discount'] = $row2['discount'];
        }
        else
          $row['discount'] = 0;
        $query2 -> closeCursor();
      }
      return $row;
    }
    $query -> closeCursor();
    return false;
  }

  public static function site_comments( $site_id, $month, $year )
  {
    global $mdb;

    $date_prev    = date( 'Y-m-d', strtotime( '-1 day', strtotime( date( $year . '-' . $month ) . '-01' ) ) );

    $results = $mdb -> query( 'SELECT '
                                  . 'date, comment '
                                . 'FROM '
                                  . 'pro_rr_sites_comments '
                                . 'WHERE '
                                    . 'site_id = ' . (int)$site_id . ' '
                                  . 'AND '
                                    . '( date LIKE \'' . $year . '-' . $month . '-__\' OR date LIKE \'' . $date_prev . '\' ) '
                                . 'ORDER BY '
                                  . 'date ASC' ) -> fetchAll();
    if ( is_array( $results ) and !empty( $results ) ) foreach ( $results as $row )
      $out[ $row['date'] ] = $row['comment'];

    return $out;
  }

  public static function site_phrases_details( $site_id, $month, $year, $day = '', $reseller_id = '', $user_login = '' )
  {
    global $mdb, $user, $config;

    $phrases = array();

    if ( $user['type'] != 'admin' and $config['phrases']['admin_confirm'] )
    {
      $data = $mdb -> get( 'pro_rr_sites', [ 'date_confirm', 'need_confirm' ], [ 'id' => $site_id ] );
      if ( $data['need_confirm'] )
        $sql = ' AND date <= \'' . $data['date_confirm'] . '\' ';
      else
        $sql = ' AND date <= \'' . date( 'Y-m-d' ) . '\' ';
    }

    $results = $mdb -> query( 'SELECT '
                                . 'id, phrase, discount, date_end, date_start, site_id, localization, days_offset, last_checked '
                              . 'FROM '
                                . 'pro_rr_phrases '
                              . 'WHERE '
                                . 'site_id = ' . (int)$site_id . ' '
                              . 'ORDER BY '
                                . 'phrase ASC' ) -> fetchAll();
    if ( is_array( $results ) and !empty( $results ) ) foreach ( $results as $row )
    {
      $positions  = array();
      $prices     = array();
      $costs      = array();

      if ( $reseller_id )
      {
        $discount = 0;
        $discount = $mdb -> get( 'pro_rr_phrases_reseller', 'discount', [ 'AND' => [ 'phrase_id' => $row['id'], 'reseller_id' => $reseller_id ] ] );

        $row['discount'] = $discount;
      }

      $date_prev    = date( 'Y-m-d', strtotime( '-1 day', strtotime( date( $year . '-' . $month ) . '-01' ) ) );

      if ( $day )
        $results2 = $mdb -> query( 'SELECT '
                                      . 'position, url, map, date '
                                    . 'FROM '
                                      . 'pro_rr_phrases_positions '
                                    . 'WHERE '
                                        . 'phrase_id = ' . $row['id'] . ' '
                                      . 'AND '
                                        . 'date LIKE \'' . $year . '-' . $month . '-' . $day . '\' ' . $sql . ' '
                                    . 'GROUP BY '
                                      . 'date '
                                    . 'ORDER BY '
                                      . 'date ASC' ) -> fetchAll();
      else
        $results2 = $mdb -> query( 'SELECT '
                                        . 'position, url, map, date '
                                      . 'FROM '
                                        . 'pro_rr_phrases_positions '
                                      . 'WHERE '
                                          . 'phrase_id = ' . $row['id'] . ' '
                                        . 'AND '
                                          . '( date LIKE \'' . $year . '-' . $month . '-__\' OR date LIKE \'' . $date_prev . '\' ) ' . $sql . ' '
                                    . 'GROUP BY '
                                      . 'date '
                                    . 'ORDER BY '
                                      . 'date ASC' ) -> fetchAll();

      if ( is_array( $results2 ) and !empty( $results2 ) ) foreach ( $results2 as $row2 )
      {
        $positions[$row2['date']] = $row2;
        if ( $row2['date'] != $date_prev )
          $costs[] = $row2['position'];
      }

      $prices = self::getPhrasePrices( $row['id'], $reseller_id );

      for ( $i = 0; $i < count( $prices ); $i++ )
      {
        foreach ( $costs as $cost )
        {
          if ( $cost >= $prices[$i]['start'] && $cost <= $prices[$i]['end'] )
            $prices[$i]['count']++;
        }
      }

      $row['prices']    = $prices;
      $row['positions'] = $positions;

      $row['last_url']  = self::last_phrase_url( $row['id'] );

      $phrases[] = $row;
    }

    return $phrases;
  }

  public function getSitePhrases( $id, $month, $year, $day = '', $reseller_id = '', $user_login = '' )
  {
    global $db, $mdb, $user, $config;

    $phrases = array();

    if ( $user['type'] != 'admin' and $config['phrases']['admin_confirm'] )
    {
      $data = $mdb -> get( 'pro_rr_sites', [ 'date_confirm', 'need_confirm' ], [ 'id' => $site_id ] );
      if ( $data['need_confirm'] )
        $sql = ' AND date <= \'' . $data['date_confirm'] . '\' ';
      else
        $sql = ' AND date <= \'' . date( 'Y-m-d' ) . '\' ';
    }

    $query = $db -> prepare( 'SELECT '
                                . 'id, phrase, discount, date_end, date_start, site_id, localization '
                              . 'FROM '
                                . 'pro_rr_phrases '
                              . 'WHERE '
                                . 'site_id = :site_id '
                              . 'ORDER BY '
                                . 'phrase ASC' );
    $query -> bindValue( ':site_id', $id, \PDO::PARAM_INT );
    $query -> execute();
    if ( $query -> rowCount() ) while ( $row = $query -> fetch() )
    {
      $positions  = array();
      $prices     = array();
      $costs      = array();

      if ( $reseller_id )
      {
        $query2 = $db -> prepare( 'SELECT discount FROM pro_rr_phrases_reseller WHERE phrase_id = :phrase_id AND reseller_id = :reseller_id' );
        $query2 -> bindValue( ':reseller_id', $reseller_id, \PDO::PARAM_INT );
        $query2 -> bindValue( ':phrase_id',   $row['id'],   \PDO::PARAM_INT );
        $query2 -> execute();
        if ( $query2 -> rowCount() )
        {
          while ( $row2 = $query2 -> fetch() )
            $row['discount'] = $row2['discount'];
        }
        else
          $row['discount'] = 0;
        $query2 -> closeCursor();
      }

      $date_prev    = date( 'Y-m-d', strtotime( '-1 day', strtotime( date( $year . '-' . $month ) . '-01' ) ) );

      if ( $day )
        $query2 = $db -> prepare( 'SELECT position, url, map, date FROM pro_rr_phrases_positions WHERE phrase_id = :phrase_id AND date LIKE "' . $year . '-' . $month . '-' . $day . '" ' . $sql . ' GROUP BY date ORDER BY date ASC' );
      else
        $query2 = $db -> prepare( 'SELECT position, url, map, date FROM pro_rr_phrases_positions WHERE phrase_id = :phrase_id AND ( date LIKE "' . $year . '-' . $month . '-__" OR date LIKE "' . $date_prev . '" ) ' . $sql . ' GROUP BY date ORDER BY date ASC' );

      $query2 -> bindValue( ':phrase_id', $row['id'] , \PDO::PARAM_INT );
      $query2 -> execute();
      if ( $query2 -> rowCount() ) while ( $row2 = $query2 -> fetch() )
      {
        $positions[$row2['date']] = $row2;
        if ( $row2['date'] != $date_prev )
          $costs[] = $row2['position'];
      }
      $query2 -> closeCursor();

      $prices = self::getPhrasePrices( $row['id'], $reseller_id );

      for ( $i = 0; $i < count( $prices ); $i++ )
      {
        foreach ( $costs as $cost )
        {
          if ( $cost >= $prices[$i]['start'] && $cost <= $prices[$i]['end'] )
            $prices[$i]['count']++;
        }
      }

      $row['prices']    = $prices;
      $row['positions'] = $positions;

      $row['last_url']  = self::last_phrase_url( $row['id'] );

      $phrases[] = $row;
    }
    $query -> closeCursor();

    return $phrases;
  }

  public static function last_phrase_position( $phrase_id )
  {
    global $mdb;
    return $mdb -> get( 'pro_rr_phrases_positions', 'position', [ 'phrase_id' => $phrase_id, 'ORDER' => [ 'date' => 'DESC' ] ] );
  }

  public static function last_phrase_url( $phrase_id )
  {
    global $mdb;
    return $mdb -> get( 'pro_rr_phrases_positions', 'url', [ 'phrase_id' => $phrase_id, 'ORDER' => [ 'date' => 'DESC' ] ] );
  }

  public function phrase_costs_save( $phrase_id, $from, $to, $price )
  {
    global $db;

    $query = $db -> prepare( 'DELETE FROM pro_rr_phrases_prices WHERE phrase_id = :phrase_id' );
    $query -> bindValue( ':phrase_id', $phrase_id, \PDO::PARAM_INT );
    $query -> execute();
    $query -> closeCursor();

    if ( is_array( $price ) ) foreach ( $price as $key => $val )
    {
      if ( !empty( $from[$key] ) && !empty( $to[$key] ) && !empty( $val ) )
      {
        $query = $db -> prepare( 'INSERT INTO pro_rr_phrases_prices ( phrase_id, start, end, price ) VALUES ( :phrase_id, :start, :end, :price )' );
        $query -> bindValue( ':phrase_id',  $phrase_id,           \PDO::PARAM_INT );
        $query -> bindValue( ':start',      trim( $from[$key] ),  \PDO::PARAM_INT );
        $query -> bindValue( ':end',        trim( $to[$key] ),    \PDO::PARAM_INT );
        $query -> bindValue( ':price',      trim( $val ),         \PDO::PARAM_STR );
        $query -> execute();
        $query -> closeCursor();
      }
    }
    return true;
  }

  public function phrase_save( $phrase_id, $phrase, $discount, $localization, $date_start, $date_end, $site_id, $days_offset, $to_all )
  {
    global $mdb;

    if ( $discount == '0.00' || !$discount ) $discount = null;
    if ( !$date_end ) $date_end = null;
    if ( !$date_start ) $date_start = null;

    if ( $phrase_id ) {
      $result = $mdb -> update( 'pro_rr_phrases', [
                      'phrase'        => $phrase,
                      'localization'  => $localization,
                      'date_end'      => $date_end,
                      'date_start'    => $date_start,
                      'discount'      => $discount,
                      'site_id'       => $site_id,
                      'days_offset' => $days_offset ? $days_offset : null
                    ], [
                      'id' => $phrase_id
                    ] );
      if ( $to_all == 'on' ) {
        $mdb -> update( 'pro_rr_phrases', [
                  'days_offset' => $days_offset ? $days_offset : null
                ], [
                  'site_id' => $site_id
                ] );
      }
      return $result;
    } else {
      $phrases = explode( PHP_EOL, $phrase );
      if ( is_array( $phrases ) and !empty( $phrases ) ) foreach ( $phrases as $phrase )
        $mdb -> insert( 'pro_rr_phrases', [
                        'phrase'        => $phrase,
                        'localization'  => $localization,
                        'date_end'      => $date_end,
                        'date_start'    => $date_start,
                        'discount'      => $discount,
                        'site_id'       => $site_id,
                        'days_offset' => $days_offset ? $days_offset : null
                      ] );
      if ( $to_all == 'on' ) {
        $mdb -> update( 'pro_rr_phrases', [
                  'days_offset' => $days_offset ? $days_offset : null
                ], [
                  'site_id' => $site_id
                ] );
      }
      return true;
    }
    return true;
  }

  public function addPhrase( $site_id, $phrase, $discount, $localization, $date_start, $date_end )
  {
    global $db;

    if ( !$phrase || !$site_id )
      return false;

    if ( $discount == '0.00' || !$discount ) $discount = null;
    if ( !$date_end ) $date_end = null;
    if ( !$date_start ) $date_start = null;

    $phrases = preg_split( '/[\n\r]+/', $phrase );

    if ( is_array( $phrases ) ) foreach ( $phrases as $phrase )
    {
      $query = $db -> prepare( 'INSERT INTO pro_rr_phrases
                                  ( site_id, phrase, localization, date_start, date_end, discount )
                                VALUES
                                  ( :site_id, :phrase, :localization, :date_start, :date_end, :discount )' );
      $query -> bindValue( ':phrase',             $phrase,            \PDO::PARAM_STR );
      $query -> bindValue( ':site_id',            $site_id,           \PDO::PARAM_INT );
      $query -> bindValue( ':localization',       $localization,      \PDO::PARAM_STR );
      $query -> bindValue( ':date_start',         $date_start,        \PDO::PARAM_STR );
      $query -> bindValue( ':date_end',           $date_end,          \PDO::PARAM_STR );
      $query -> bindValue( ':discount',           $discount,          \PDO::PARAM_INT );
      $query -> execute();
      $query -> closeCursor();
    }

    return true;
  }

  static public function site_delete( $site_id )
  {
    global $mdb;

    $results = $mdb -> select( 'pro_rr_phrases', 'id', [ 'site_id' => $site_id ] );
    if ( is_array( $results ) and !empty( $results ) ) foreach ( $results as $row )
      self::phrase_delete( $row );

    $mdb -> delete( 'pro_rr_sites_majestic', [ 'site_id' => $site_id ] );
    $mdb -> delete( 'pro_rr_sites_semstorm', [ 'site_id' => $site_id ] );
    $mdb -> delete( 'pro_rr_sites_comments', [ 'site_id' => $site_id ] );
    $mdb -> delete( 'pro_rr_sites', [ 'id' => $site_id ] );

    return true;
  }

  public static function prev_site_id( $site_id, $delete = false )
  {
    global $mdb;

    if ( !$mdb -> count( 'pro_rr_sites', [ 'id' => $site_id ] ) )
      return self::first_site_id();

    $results = $mdb -> query( 'SELECT id FROM pro_rr_sites WHERE name < ( SELECT name FROM pro_rr_sites WHERE id = ' . $site_id . ' ) AND archive = 0 ORDER BY name DESC LIMIT 1' ) -> fetchAll();
    if ( is_array( $results ) and !empty( $results ) ) foreach ( $results as $row )
      return $row['id'];
    else
    {
      if ( $delete )
        return \factory\Ranker::next_site_id( $site_id );
      else
        return \factory\Ranker::last_site_id();
    }
    return false;
  }

  public function getPrevSiteId( $id )
  {
    global $db;

    $query = $db -> prepare( 'SELECT id FROM pro_rr_sites WHERE name < ( SELECT name FROM pro_rr_sites WHERE id = :id ) AND archive = 0 ORDER BY name DESC LIMIT 1' );
    $query -> bindValue( ':id', $id, \PDO::PARAM_INT );
    $query -> execute();
    if ( $query -> rowCount() ) while ( $row = $query -> fetch() )
      return $row['id'];
    else
    {
      $site = self::getSite( 0, 'DESC' );
      return $site['id'];
    }
    $query -> closeCursor();
    return false;
  }

  public static function last_site_id()
  {
    global $mdb;
    return $mdb -> get( 'pro_rr_sites', 'id', [ 'ORDER' => [ 'name' => 'DESC' ] ] );
  }

  public static function first_site_id()
  {
    global $mdb;
    return $mdb -> get( 'pro_rr_sites', 'id', [ 'ORDER' => [ 'name' => 'ASC' ] ] );
  }

  public static function next_site_id( $site_id )
  {
    global $mdb;

    if ( !$mdb -> count( 'pro_rr_sites', [ 'id' => $site_id ] ) )
      return self::first_site_id();

    $results = $mdb -> query( 'SELECT id FROM pro_rr_sites WHERE name > ( SELECT name FROM pro_rr_sites WHERE id = ' . $site_id . ' ) AND archive = 0 ORDER BY name ASC LIMIT 1' ) -> fetchAll();
    if ( is_array( $results ) and !empty( $results ) ) foreach ( $results as $row )
      return $row['id'];
    else
    {
      $site = self::getSite( self::first_site_id() );
      return $site['id'];
    }
    return false;
  }

  public function getNextSiteId( $id )
  {
    global $db;

    $query = $db -> prepare( 'SELECT id FROM pro_rr_sites WHERE name > ( SELECT name FROM pro_rr_sites WHERE id = :id ) AND archive = 0 ORDER BY name ASC LIMIT 1' );
    $query -> bindValue( ':id', $id, \PDO::PARAM_INT );
    $query -> execute();
    if ( $query -> rowCount() ) while ( $row = $query -> fetch() )
      return $row['id'];
    else
    {
      $site = self::getSite();
      return $site['id'];
    }
    $query -> closeCursor();
    return false;
  }

  public static function site_details( $site_id )
  {
    global $mdb;
    return $mdb -> get( 'pro_rr_sites', '*', [ 'id' => $site_id ] );
  }

  public static function getSite( $id, $sort = 'ASC', $force_id = false )
  {
    global $db;

    if ( $id || $force_id )
      $sql = 'WHERE id = :id';

    if ( !$site = \FileCache::fetch( "getaSite:$id:$sort:$force_id:" . date( 'Y-m-d' ) ) )
    {
      $query = $db -> prepare( 'SELECT * FROM pro_rr_sites ' . $sql . ' ORDER BY name ' . $sort . ' LIMIT 1' );
      if ( $id || $force_id )
        $query -> bindValue( ':id', $id, \PDO::PARAM_INT );
      $query -> execute();
      if ( $query -> rowCount() ) while ( $row = $query -> fetch() )
        $site = $row;
      $query -> closeCursor();

      \FileCache::store( "getSite:$id:$sort:$force_id:" . date( 'Y-m-d' ), $site );
    }

    return $site;
  }

  public static function sites_list()
  {
    global $mdb;
    return $mdb -> select( 'pro_rr_sites', '*', [ 'ORDER' => [ 'name' => 'ASC', 'url' => 'ASC' ] ] );
  }

  public function getSites()
  {
    global $db;

    $query = $db -> prepare( 'SELECT * FROM pro_rr_sites ORDER BY name ASC, url ASC' );
    $query -> execute();
    if ( $query -> rowCount() ) while ( $row = $query -> fetch() )
      $sites[] = $row;
    $query -> closeCursor();
    return $sites;
  }

  static public function site_save( $site_id, $name, $url, $discount, $comments, $subscription, $date_start, $date_end, $majestic, $semstorm, $need_confirm )
  {
    global $mdb;

    if ( !$url )
      return false;

    if ( $date_end == '0000-00-00' or empty( $date_end ) )
      $date_end = null;

    if ( !$site_id )
    {
      $mdb -> insert( 'pro_rr_sites', [
              'name'          => $name,
              'url'           => $url,
              'discount'      => $discount,
              'comments'      => $comments,
              'subscription'  => $subscription,
              'date_start'    => $date_start,
              'date_end'      => $date_end,
              'majestic'      => $majestic == 'on' ? 1 : 0,
              'semstorm' => $semstorm == 'on' ? 1 : 0,
              'need_confirm' => $need_confirm == 'on' ? 1 : 0
            ] );
      return $mdb -> id();
    }
    else
    {
      $mdb -> update( 'pro_rr_sites', [
              'url'           => $url,
              'discount'      => $discount,
              'name'          => $name,
              'comments'      => $comments,
              'subscription'  => $subscription,
              'date_start'    => $date_start,
              'date_end'      => $date_end,
              'majestic'      => $majestic == 'on' ? 1 : 0,
              'semstorm' => $semstorm == 'on' ? 1 : 0,
              'need_confirm' => $need_confirm == 'on' ? 1 : 0
            ], [
              'id' => $site_id
            ] );
      return $site_id;
    }
    return false;
  }

  public static function sites_clients()
  {
    global $mdb;

    $results = $mdb -> select( 'pro_rr_clients', [ 'id', 'login' ], [ 'ORDER' => [ 'login' => 'ASC' ] ] );
    if ( is_array( $results ) and !empty( $results ) ) foreach ( $results as $row )
    {
      unset( $sites );

      $results2 = $mdb -> select( 'pro_rr_clients_sites', [ 'site_id' ], [ 'client_id' => $row['id'] ] );
      if ( is_array( $results2 ) and !empty( $results2 ) ) foreach ( $results2 as $row2 )
        $sites[] = $row2['site_id'];

      $client['login']  = $row['login'];
      $client['sites']  = implode( ';', $sites );

      $sites_clients[] = $client;
    }
    return $sites_clients;
  }
}
?>