function get_country_date_last_update()
{
$sql = 'SELECT date
FROM record_country
GROUP BY date
HAVING count(date) = ' . COUNTRY_TOTAL_COUNT . '
ORDER BY 1 DESC
LIMIT 1;
';
$res = mysql_query($sql) or die(mysql_error());
if ($fila = mysql_fetch_assoc($res)) {
return $fila['date'];
}
return NULL;
}
define('COUNTRY_DATE_LAST_UPDATE', get_country_date_last_update());
function get_city_total_count()
{
$sql = 'SELECT count(*) cantidad FROM facebook_city;';
$res = mysql_query($sql) or die(mysql_error());
if ($fila = mysql_fetch_assoc($res)) {
return $fila['cantidad'];
}
return NULL;
}
define('CITY_TOTAL_COUNT', get_city_total_count());
define('MAX_LIST_CITY_COUNT', 500);
function get_city_date_last_update($numSemanas = 1)
{
$sql = 'SELECT date
FROM record_city
function getCrecimiento($id_country, $column, $last_update, $days)
{
$sql = "SELECT total_\$4, (total_\$4 - (\n SELECT total_\$4 \n FROM " . DB_FACEBOOK_PREFIX . "record_country_3_1 \n WHERE date = (\n SELECT date \n FROM " . DB_FACEBOOK_PREFIX . "record_country_3_1 \n WHERE id_country = \$3\n AND total_\$4 IS NOT NULL\n AND DATE_SUB('\$1',INTERVAL \$2 DAY) <= date\n ORDER BY 1 ASC \n LIMIT 1\n ) \n AND id_country = \$3\n )) cambio \n FROM " . DB_FACEBOOK_PREFIX . "record_country_3_1\n WHERE id_country = \$3 \n AND date = '\$1';\n ";
$que = db_query($sql, array($last_update, $days, $id_country, $column));
if ($fila = mysql_fetch_assoc($que)) {
if (is_numeric($fila['cambio'])) {
return $fila['cambio'];
}
}
return 'NULL';
}
//Países
$countries = array();
$query = 'SELECT * FROM ' . DB_FACEBOOK_PREFIX . 'country_3_1 ORDER BY 1;';
$que = db_query($query, array());
$country_date_last_update = get_country_date_last_update(mysql_num_rows($que));
while ($country = mysql_fetch_assoc($que)) {
$id_country = $country['id_country'];
$code = $country['code'];
$name = $country['nombre'];
$name_en = $country['name'];
$abbreviation = !empty($country['abbreviation']) ? $country['abbreviation'] : 'NULL';
$slug = $country['slug'];
$idiom = !empty($country['idiom']) ? $country['idiom'] : 'NULL';
$id_continent = $country['id_continent'];
$supports_region = $country['supports_region'];
$supports_city = $country['supports_city'];
$audience = get_country_audience($id_country, $country_date_last_update);
$aux_audience_history = get_audience_history($id_country, $country_date_last_update, 380);
$audience_down_360 = $aux_audience_history['accumulate_down_360'];
unset($aux_audience_history['accumulate_down_360']);
