1271 lines
44 KiB
PHP
1271 lines
44 KiB
PHP
<?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;
|
|
}
|
|
}
|
|
?>
|