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;
}
}
