function register_local_fans($page_id) { $data_return = array('has_local_fans' => false); $local_fans_last_date = get_local_fans_last_date($page_id); if (!empty($local_fans_last_date)) { //Get page's data $query = 'SELECT id_page, parent, fb_id, name, username, is_verified, likes, likes_grow_7, talking_about, category_id, sub_category_id FROM ' . DB_RESULTS_PREFIX . 'facebook_pages WHERE id_page = $10;'; $que_pages = db_query_table_results($query, array($page_id)); $page = mysql_fetch_assoc($que_pages); //Get local fans $query = 'SELECT * FROM ' . DB_FACEBOOK_PREFIX . 'page_local_fans_country WHERE id_page = $1 AND date = \'$2\';'; $que_local_fans = db_query($query, array($page_id, $local_fans_last_date)); if (mysql_num_rows($que_local_fans) > 0) { $data_return['has_local_fans'] = true; $data_return['date'] = $local_fans_last_date; $data_return['items'] = array(); //Controlamos que no se duplique $local_fans_exist_in_result = false; $query = 'SELECT id_page FROM ' . DB_RESULTS_PREFIX . 'facebook_pages_local_fans WHERE id_page = $10 LIMIT 1;'; $result = db_query_table_results($query, array($page_id)); if (mysql_num_rows($result) > 0) { $local_fans_exist_in_result = true; } while ($local_fans = mysql_fetch_assoc($que_local_fans)) { $data_return['items'][] = array('c' => $local_fans['id_country'], 'l' => $local_fans['likes']); if (!$local_fans_exist_in_result) { $country = get_country_data($local_fans['id_country']); $country_code = $country['code']; $country_name = $country['name']; $country_slug = $country['slug']; $likes = $local_fans['likes']; //Insert into result's table $query = "INSERT INTO " . DB_RESULTS_PREFIX . "facebook_pages_local_fans VALUES(NULL, \$10, \$11, \$12, '\$13', '\$14', \$15, \$16, \$17, \$18, \$19, \$20, '\$21', '\$22', '\$23', \$24, NOW());"; $values = array($page['id_page'], $page['parent'], $page['fb_id'], $page['username'], $page['name'], $page['is_verified'], $page['likes'], !empty($page['likes_grow_7']) ? $page['likes_grow_7'] : 'NULL', $page['talking_about'], !empty($page['category_id']) ? $page['category_id'] : 'NULL', !empty($page['sub_category_id']) ? $page['sub_category_id'] : 'NULL', $country_code, $country_name, $country_slug, $likes); $res = db_query_table_results($query, $values, 1); } } } } return $data_return; }
$country_name = $countries[$pages['id_country']]['name']; $country_slug = $countries[$pages['id_country']]['slug']; $likes = $pages['likes']; $query = "INSERT INTO " . DB_RESULTS_PREFIX . "facebook_pages_local_fans VALUES(NULL, \$10, \$11, \$12, '\$13', '\$14', \$15, \$16, \$17, \$18, \$19, \$20, '\$21', '\$22', '\$23', \$24, NOW());"; $values = array($id_page, $page_info['parent'], $page_info['fb_id'], $page_info['username'], $page_info['name'], $page_info['is_verified'], $page_info['likes'], !empty($page_info['likes_grow_7']) ? $page_info['likes_grow_7'] : 'NULL', $page_info['talking_about'], !empty($page_info['category_id']) ? $page_info['category_id'] : 'NULL', !empty($page_info['sub_category_id']) ? $page_info['sub_category_id'] : 'NULL', $country_code, $country_name, $country_slug, $likes); $res = db_query_table_results($query, $values, 1); } } //Update ranking local fans country $countries = array(); $query = 'SELECT id_country, code FROM ' . DB_FACEBOOK_PREFIX . 'country_3_1 WHERE code in(SELECT DISTINCT country_code FROM owloo_results.' . DB_RESULTS_PREFIX . 'facebook_pages WHERE country_code is not null) OR code in(SELECT DISTINCT first_country_code FROM owloo_results.' . DB_RESULTS_PREFIX . 'facebook_pages WHERE first_country_code is not null) ORDER BY 1;'; $que = db_query($query, array()); while ($country = mysql_fetch_assoc($que)) { $sql = "SELECT id_page, likes_local_fans \n FROM " . DB_RESULTS_PREFIX . "facebook_pages_local_fans\n WHERE country_code = '\$10' \n ORDER BY likes_local_fans DESC\n ;"; $que_pages = db_query_table_results($sql, array($country['code'])); $count = 1; while ($pages = mysql_fetch_assoc($que_pages)) { $query = "UPDATE " . DB_RESULTS_PREFIX . "facebook_pages SET first_local_fans_country_ranking = \$10 WHERE id_page = \$11 AND first_country_code = '\$12';"; $values = array($count, $pages['id_page'], $country['code']); $res = db_query_table_results($query, $values, 1); $query = "UPDATE " . DB_RESULTS_PREFIX . "facebook_pages SET country_ranking = \$10 WHERE id_page = \$11 AND country_code = '\$12';"; $values = array($count, $pages['id_page'], $country['code']); $res = db_query_table_results($query, $values, 1); $count++; } } error_log(' Facebook Page Local Fans (f): ' . date('d m Y H:i:s'));
return '11'; case 'Dic': return '12'; } } function returnDates($fromdate, $todate) { $fromdate = \DateTime::createFromFormat('Y-m-d', $fromdate); $todate = \DateTime::createFromFormat('Y-m-d', $todate); return new \DatePeriod($fromdate, new \DateInterval('P1D'), $todate->modify('+1 day')); } $query_country = 'SELECT id_country, audience_history FROM web_facebook_countries WHERE id_country < 247 ORDER BY 1;'; $que_country = db_query_table_results($query_country, array()); while ($row_country = mysql_fetch_assoc($que_country)) { $id_country = $row_country['id_country']; $audience_history = json_decode($row_country['audience_history'], true); $audience_history_data = explode(',', $audience_history['series_data']); $audience_history_axis = explode(',', str_replace("'", '', $audience_history['x_axis'])); $array_values = array(); $current_value = NULL; for ($i = 0, $limit = count($audience_history_data); $i < $limit; $i++) { $date = explode(' ', $audience_history_axis[$i]); $date[1] = getMesNumeric($date[1]); $array_values['20' . $date[2] . '-' . $date[1] . '-' . $date[0]] = $audience_history_data[$i]; } reset($array_values); $first_key = key($array_values); $date1 = $first_key;
$que_cat = db_query_table_results($query_cat, array()); while ($fila_cat = mysql_fetch_assoc($que_cat)) { $xml .= "\n<url>\n <loc>" . $domain . "/facebook-analytics/pages/country/" . $fila['slug'] . "/tag/" . $fila_cat['category'] . "</loc>\n <changefreq>daily</changefreq>\n</url>"; } } /***** TWITTER PROFILES *****/ //Perfiles del Twitter $query = "SELECT id, screen_name \n FROM " . DB_RESULTS_PREFIX . "twitter_profiles \n ORDER BY 1 ASC;\n "; $que = db_query_table_results($query, array()); while ($fila = mysql_fetch_assoc($que)) { $xml .= "\n<url>\n <loc>" . $domain . "/twitter-analytics/profiles/" . strtolower($fila['screen_name']) . "</loc>\n <changefreq>daily</changefreq>\n</url>"; } /***** INSTAGRAM PROFILES *****/ //Perfiles del Twitter $query = "SELECT id, username \n FROM " . DB_RESULTS_PREFIX . "instagram_profiles\n ORDER BY 1 ASC;\n "; $que = db_query_table_results($query, array()); while ($fila = mysql_fetch_assoc($que)) { $xml .= "\n<url>\n <loc>" . $domain . "/instagram-analytics/accounts/" . strtolower($fila['username']) . "</loc>\n <changefreq>daily</changefreq>\n</url>"; } //Perfiles del Twitter $query = "SELECT id_category, category \n FROM " . DB_RESULTS_PREFIX . "instagram_categories\n ORDER BY 1 ASC;\n "; $que = db_query_table_results($query, array()); while ($fila = mysql_fetch_assoc($que)) { $xml .= "\n<url>\n <loc>" . $domain . "/instagram-analytics/accounts/tag/" . strtolower($fila['category']) . "</loc>\n <changefreq>daily</changefreq>\n</url>"; } $xml .= "\n</urlset>"; $f = fopen(__DIR__ . '/' . $prefig['name'] . '.xml', 'w+'); fwrite($f, $xml); fclose($f); } echo '<p>Sitemap generado!</p>';
WHERE (followed_by_count, followed_by_grow_30) > ( SELECT followed_by_count, followed_by_grow_30 FROM ' . DB_RESULTS_PREFIX . 'instagram_profiles WHERE id = $10 LIMIT 1 );'; $que_pages = db_query_table_results($query, array($generate_new_profile_id)); while ($page = mysql_fetch_assoc($que_pages)) { $query = "UPDATE " . DB_RESULTS_PREFIX . "instagram_profiles SET general_ranking = \$10 WHERE id = \$11;"; $values = array($page['rank'], $generate_new_profile_id); $res = db_query_table_results($query, $values, 1); } //return the page data $query = 'SELECT * FROM ' . DB_RESULTS_PREFIX . 'instagram_profiles WHERE id = $10;'; $que_page = db_query_table_results($query, array($generate_new_profile_id)); if ($page = mysql_fetch_assoc($que_page)) { header('Content-Type: application/json'); $page['charts'] = json_decode($page['charts'], true); $page['most_mentions'] = json_decode($page['most_mentions'], true); $page['most_hashtags'] = json_decode($page['most_hashtags'], true); $page['last_post'] = json_decode($page['last_post'], true); $page['post_by_engagement_rate'] = json_decode($page['post_by_engagement_rate'], true); echo json_encode(array('profile_data' => $page)); die; } else { header('Content-Type: application/json'); echo json_encode(array('message_code' => 3, 'description' => 'Sorry, an error has occurred')); die; } }