select( 'pro_rr_sites', [ 'id', 'name' ], [ 'OR' => [ 'date_confirm' => date( 'Y-m-d' ), 'need_confirm' => 0 ] ] ); if ( is_array( $sites ) and !empty( $sites ) ) foreach ( $sites as $site ) { $phrases = $mdb -> select( 'pro_rr_phrases', [ 'id', 'phrase' ], [ 'AND' => [ 'filled_missing_positions' => 0, 'site_id' => $site['id'] ], 'ORDER' => [ 'phrase' => 'ASC' ], 'LIMIT' => 1 ] ); if ( is_array( $phrases ) and !empty( $phrases ) ) foreach ( $phrases as $phrase ) { $positions = $mdb -> select( 'pro_rr_phrases_positions', [ 'position', 'date' ], [ 'AND' => [ 'phrase_id' => $phrase['id'] ], 'ORDER' => [ 'date' => 'DESC' ], 'LIMIT' => 1 ] ); if ( is_array( $positions ) and !empty( $positions ) ) foreach ( $positions as $position ) { $previous_position = $mdb -> get( 'pro_rr_phrases_positions', [ 'date', 'position', 'url', 'map' ], [ 'AND' => [ 'phrase_id' => $phrase['id'], 'date[!]' => $position[ 'date' ] ], 'ORDER' => [ 'date' => 'DESC' ] ] ); if ( $previous_position['position'] != 0 and $position['position'] != 0 ) $average_position = round( ( $position['position'] + $previous_position['position'] ) / 2, 0 ); else if ( $previous_position['position'] == 0 and $position['position'] != 0 ) $average_position = $position['position']; else if ( $previous_position['position'] != 0 and $position['position'] == 0 ) $average_position = $previous_position['position']; else $average_position = 0; $begin = new DateTime( date( 'Y-m-d', strtotime( '+1 days', strtotime( $previous_position['date'] ) ) ) ); $end = new DateTime( date( 'Y-m-d', strtotime( '-1 days', strtotime( $position['date'] ) ) ) ); for ( $i = $begin; $i <= $end; $i -> modify( '+1 day' ) ) { if ( !$mdb -> count( 'pro_rr_phrases_positions', [ 'AND' => [ 'phrase_id' => $phrase['id'], 'date' => $i -> format( "Y-m-d" ) ] ] ) ) { $mdb -> insert( 'pro_rr_phrases_positions', [ 'position' => $average_position > 10 ? rand( $average_position - 1, $average_position + 1 ) : $average_position, 'phrase_id' => $phrase['id'], 'url' => $previous_position['url'], 'map' => $previous_position['map'], 'date' => $i -> format( "Y-m-d" ) ] ); } } $mdb -> update( 'pro_rr_phrases', [ 'filled_missing_positions' => 1 ], [ 'id' => $phrase['id'] ] ); } else { $mdb -> update( 'pro_rr_phrases', [ 'filled_missing_positions' => 1 ], [ 'id' => $phrase['id'] ] ); } return [ 'status' => 'ok', 'msg' => 'Uzupełniam brakujące pozycje: ' . $site['name'] . ' - ' . $phrase['phrase'] . '.' ]; } } return [ 'status' => 'empty' ]; } static public function archive_empty() { global $mdb; $results = $mdb -> query( 'SELECT * FROM archive_phrases_positions WHERE date < \'' . date( 'Y-m-d', strtotime( '-4 years', time() ) ) . '\' ORDER BY id ASC LIMIT 1' ) -> fetchAll(); if ( is_array( $results ) and !empty( $results ) ) { foreach ( $results as $row ) { $mdb -> delete( 'archive_phrases_positions', [ 'id' => $row['id'] ] ); return [ 'status' => 'ok', 'msg' => 'Usuwam stare pozycje fraz z archiwum | ID: ' . $row['id'] . ', data: ' . $row['date'] . ', URL: ' . $row['url'] . '' ]; } } else return [ 'status' => 'empty' ]; } public static function archive_positions() { global $mdb; $results = $mdb -> query( 'SELECT * FROM pro_rr_phrases_positions WHERE date LIKE \'' . date( 'Y-m', strtotime( '-2 years', time() ) ) . '%\' LIMIT 1' ) -> fetchAll(); if ( is_array( $results ) and !empty( $results ) ) { foreach ( $results as $row ) { $mdb -> insert( 'archive_phrases_positions', [ 'phrase_id' => $row['phrase_id'], 'position' => $row['position'], 'url' => $row['url'], 'map' => $row['map'], 'date' => $row['date'] ] ); $mdb -> delete( 'pro_rr_phrases_positions', [ 'id' => $row['id'] ] ); return [ 'status' => 'ok', 'msg' => 'Archiwizuję stare pozycje fraz | ID: ' . $row['id'] . ', data: ' . $row['date'] . '' ]; } } else return [ 'status' => 'empty' ]; } public static function check_proxy() { global $mdb, $config; $results = $mdb -> query( 'SELECT ' . 'id, proxy, errors ' . 'FROM ' . 'pro_proxy_servers ' . 'WHERE ' . '( DATE_ADD( last_checked, INTERVAL 1 DAY ) < NOW() OR last_checked IS NULL ) ' . 'ORDER BY ' . 'last_checked ASC ' . 'LIMIT 1' ) -> fetchAll(); if ( is_array( $results ) and !empty( $results ) ) foreach ( $results as $row ) { $result = \GoogleSite::checkProxyServerVersion( $row['proxy'] ); if ( $result['code'] == 404 or $result['result'] == '' and $result['code'] == 0 ) { $mdb -> update( 'pro_proxy_servers', [ 'errors' => $row['errors'] + 1, 'last_checked' => \S::getDate() ], [ 'id' => $row['id'] ] ); $mdb -> delete( 'pro_proxy_servers', [ 'errors[>=]' => 7 ] ); return [ 'status' => 'ok', 'msg' => 'Sprawdzam poprawność proxy serwerowego: ' . $row['proxy'] . ' - ' . floatval( $result ) ]; } $ip = $result['info']['primary_ip']; if ( !$result || $result != $config['proxy']['s-version'] ) $mdb -> update( 'pro_proxy_servers', [ 'last_checked' => \S::getDate(), 'errors' => 0, 'enabled' => 0, 'ip' => $ip ], [ 'id' => $row['id'] ] ); else $mdb -> update( 'pro_proxy_servers', [ 'last_checked' => \S::getDate(), 'errors' => 0, 'enabled' => 1, 'ip' => $ip ], [ 'id' => $row['id'] ] ); return [ 'status' => 'ok', 'msg' => 'Sprawdzam poprawność proxy serwerowego: ' . $row['proxy'] . ' - ' . floatval( $result ) ]; } return [ 'status' => 'empty' ]; } static public function get_phrases_positions_dfs3() { global $mdb; $row = $mdb -> get( 'pro_rr_phrases', '*', [ 'AND' => [ 'ds_ready' => 1, 'ds_id[!]' => null ] ] ); if ( $row ) { $client = new RestClient( 'https://api.dataforseo.com/', null, 'pyziak84@gmail.com', '0p4rYWDNoK63eUUw' ); $result = $client->get('/v3/serp/google/organic/task_get/advanced/' . $row['ds_id'] ); if ( $result['status_code'] == '20000' ) { $sites = $result['tasks'][0]['result'][0]['items']; foreach ( $sites as $site ) { if ( $site['type'] == 'organic' and \S::get_domain( $site['url'] ) == \S::get_domain( 'http://' . \factory\Ranker::get_domain_by_id( $row['site_id'] ) ) ) { $phrase_position = $site['rank_group']; $site_url = $site['url']; break; } } if ( $mdb -> count( 'pro_rr_phrases_positions', [ 'AND' => [ 'phrase_id' => $row['id'], 'date' => date( 'Y-m-d' ) ] ] ) ) { $mdb -> update( 'pro_rr_phrases_positions', [ 'position' => (int)$phrase_position, 'url' => $site_url, ], [ 'AND' => [ 'phrase_id' => $row['id'], 'date' => date( 'Y-m-d' ) ] ] ); } else { $mdb -> insert( 'pro_rr_phrases_positions', [ 'phrase_id' => $row['id'], 'position' => (int)$phrase_position, 'url' => $site_url, 'date' => date( 'Y-m-d' ) ] ); $last_id = $mdb -> get( 'phrase_positions_statistic', 'id', [ 'phrase_id' => $row['id'], 'ORDER' => [ 'date' => 'DESC' ] ] ); if ( $last_id ) $mdb -> query( 'DELETE FROM phrase_positions_statistic WHERE phrase_id = ' . $row['id'] . ' AND id NOT IN ( ' . $last_id . ' )' ); $mdb -> insert( 'phrase_positions_statistic', [ 'phrase_id' => $row['id'], 'position' => $site['rank_group'], 'date' => date( 'Y-m-d' ) ] ); } $phrase_position = $site['rank_group']; $mdb -> update( 'pro_rr_phrases', [ 'last_checked' => date( 'Y-m-d' ), 'filled_missing_positions' => 0, 'ds_id' => null, 'ds_ready' => 0 ], [ 'id' => $row['id'] ] ); return [ 'status' => 'ok', 'msg' => 'Pobieramy wyniki dla frazy: ' . $row['phrase'] . ' - ' . (int)$phrase_position . ' - (API 3.0)' ]; } return [ 'status' => 'ok', 'msg' => 'Pobieramy wyniki dla frazy: ' . $row['phrase'] . ' - BŁĄD - (API 3.0)' ]; } } static public function post_phrases_positions_dfs3() { global $mdb; $sql = 'SELECT * FROM ( ' . 'SELECT ' . 'prp.id, phrase, url, localization, last_checked, days_offset, prs.name ' . 'FROM ' . 'pro_rr_phrases AS prp, pro_rr_sites AS prs ' . 'WHERE ' . 'prs.id = prp.site_id ' . 'AND ' . 'last_checked != \'' . date( 'Y-m-d' ) . '\' ' . 'AND ' . '( prp.date_end >= \'' . date( 'Y-m-d' ) . '\' OR prp.date_end IS NULL ) ' . 'AND ' . '( prp.date_start <= \'' . date( 'Y-m-d' ) . '\' OR prp.date_start IS NULL ) ' . 'AND ' . '( prs.date_start <= \'' . date( 'Y-m-d' ) . '\' OR prs.date_start IS NULL ) ' . 'AND ' . '( prs.date_end >= \'' . date( 'Y-m-d' ) . '\' OR prs.date_end IS NULL ) ' . 'AND ' . 'ds_id IS NULL ' . ') AS q1 ' . 'WHERE ' . '( ' . 'days_offset IS NULL ' . 'OR ' . 'days_offset IS NOT NULL AND DATE( DATE_ADD( last_checked, INTERVAL +days_offset DAY ) ) <= CURRENT_DATE ' . ') OR last_checked = \'2012-01-01\' ' . 'ORDER BY ' . 'name ASC, phrase ASC ' . 'LIMIT 1'; $results = $mdb -> query( $sql ) -> fetchAll( \PDO::FETCH_ASSOC ); if ( is_array( $results ) and !empty( $results ) ) foreach ( $results as $row ) { $client = new RestClient( 'https://api.dataforseo.com/', null, 'pyziak84@gmail.com', '0p4rYWDNoK63eUUw' ); $last_position = $mdb -> get( 'pro_rr_phrases_positions', 'position', [ 'AND' => [ 'phrase_id' => $row['id'] ], 'ORDER' => [ 'date' => 'DESC' ] ] ); if ( $last_position <= 10 ) { $depth = 30; } elseif ( $last_position <= 30 ) { $depth = 50; } else { $depth = 100; } if ( $row['localization'] and (int)$row['localization'] ) { $post_array[0] = array( "language_code" => "pl", "location_code" => $row['localization'], "keyword" => mb_convert_encoding( $row['phrase'], "UTF-8" ), "priority" => 1, 'postback_data' => 'advanced', 'postback_url' => 'http://www.rank24.pl/dsf.php?d4s-id=$id', 'depth' => $depth ); } else { $post_array[0] = array( "language_code" => "pl", "location_code" => $row['localization'], "keyword" => mb_convert_encoding( $row['phrase'], "UTF-8" ), "priority" => 1, 'postback_data' => 'advanced', 'postback_url' => 'http://www.rank24.pl/dsf.php?d4s-id=$id', 'depth' => $depth ); } $task_post_result = $client -> post( '/v3/serp/google/organic/task_post', $post_array ); if ( $task_post_result['status_code'] == '20000' ) { $mdb -> update( 'pro_rr_phrases', [ 'ds_id' => $task_post_result['tasks'][0]['id'], 'ds_date' => date( 'Y-m-d' ) ], [ 'id' => $row['id'] ] ); return [ 'status' => 'ok', 'msg' => 'Wysyłam do sprawdzenia frazę: ' . $row['phrase'] . ' - ' . $row['url'] . ' - (API 3.0) - ' . $task_post_result['tasks'][0]['id'] ]; } } return [ 'status' => 'empty' ]; } public static function check_phrases_positions_dfs() { global $mdb; include 'autoload/RestClient.php'; $sql = 'SELECT * FROM ( ' . 'SELECT ' . 'prp.id, phrase, url, localization, last_checked, days_offset, prs.name ' . 'FROM ' . 'pro_rr_phrases AS prp, pro_rr_sites AS prs ' . 'WHERE ' . 'prs.id = prp.site_id ' . 'AND ' . 'last_checked != \'' . date( 'Y-m-d' ) . '\' ' . 'AND ' . '( prp.date_end >= \'' . date( 'Y-m-d' ) . '\' OR prp.date_end IS NULL ) ' . 'AND ' . '( prp.date_start <= \'' . date( 'Y-m-d' ) . '\' OR prp.date_start IS NULL ) ' . 'AND ' . '( prs.date_start <= \'' . date( 'Y-m-d' ) . '\' OR prs.date_start IS NULL ) ' . 'AND ' . '( prs.date_end >= \'' . date( 'Y-m-d' ) . '\' OR prs.date_end IS NULL ) ' . 'AND ' . 'ds_id IS NULL ' . ') AS q1 ' . 'WHERE ' . '( ' . 'days_offset IS NULL ' . 'OR ' . 'days_offset IS NOT NULL AND DATE( DATE_ADD( last_checked, INTERVAL +days_offset DAY ) ) <= CURRENT_DATE ' . ') OR last_checked = \'2012-01-01\' ' . 'ORDER BY ' . 'name ASC, phrase ASC ' . 'LIMIT 1'; $results = $mdb -> query( $sql ) -> fetchAll( \PDO::FETCH_ASSOC ); if ( is_array( $results ) and !empty( $results ) ) { foreach ( $results as $row ) { $client = new RestClient( 'https://api.dataforseo.com/', null, 'pyziak84@gmail.com', '0p4rYWDNoK63eUUw' ); if ( $row['localization'] and (int)$row['localization'] ) { $post_array[0] = array( "priority" => 1, "site" => \S::get_domain( 'https://' . $row['url'] ), "se_id" => 21, "loc_id" => $row['localization'], "key" => mb_convert_encoding( $row['phrase'], "UTF-8" ), "key_id" => null, 'se_id' => 3433, 'postback_url' => 'http://www.rank24.pl/dsf.php' ); } else { $post_array[0] = array( "priority" => 1, "site" => \S::get_domain( 'https://' . $row['url'] ), "se_id" => 21, "loc_name_canonical" => $row['localization'] ? $row['localization'] : 'Poland', "key" => mb_convert_encoding( $row['phrase'], "UTF-8" ), "key_id" => null, 'se_id' => 3433, 'postback_url' => 'http://www.rank24.pl/dsf.php' ); } $task_post_result = $client -> post( 'v2/rnk_tasks_post', array( 'data' => $post_array ) ); if ( $task_post_result['status'] == 'ok' ) { $mdb -> update( 'pro_rr_phrases', [ 'ds_id' => $task_post_result['results'][0]['task_id'], 'ds_date' => date( 'Y-m-d' ) ], [ 'id' => $row['id'] ] ); if ( $row['localization'] != '' and !is_int( $row['localization'] ) ) { $mdb -> update( 'pro_rr_phrases', [ 'localization' => $task_post_result['results'][0]['loc_id'] ], [ 'id' => $row['id'] ] ); } return [ 'status' => 'ok', 'msg' => 'Wysyłam do sprawdzenia frazę: ' . $row['phrase'] . ' - ' . $row['url'] . '.' ]; } } } return [ 'status' => 'empty' ]; } }