public static function getInstance($node)
{
if (!isset(self::$instance)) {
$class = __CLASS__;
self::$instance = new $class($node);
}
return self::$instance;
}
function processCountryCitySummary($db, $dbNames, $startTime, $endTime, $hoursElapsedSinceLastEvenYear, $urlIdPageIdMap)
{
$geoipProvider = CityIPSpatialDatabase::getInstance('geoip');
$countryWiseLoadTimes = $cityWiseLoadTimes = $ipCountryCityMap = $countryCodeCountryIdMap = $cityNameCityIdMap = array();
foreach ($urlIdPageIdMap as $urlId => $pageId) {
$sql = 'SELECT ip_address, load_time.done AS loadTime
FROM ' . $dbNames['main'] . '.main, ' . $dbNames['main'] . '.url, ' . $dbNames['main'] . '.url_static, ' . $dbNames['main'] . '.load_time
WHERE main.url_id = url.url_id
AND url.url_static_id = url_static.url_id
AND main.main_id = load_time.main_id
AND main.log_time >= :start_time AND main.log_time <= :end_time
AND url_static.url_id = :url_id';
$st = $db->prepare($sql);
$st->bindValue(':start_time', $startTime, PDO::PARAM_STR);
$st->bindValue(':end_time', $endTime, PDO::PARAM_STR);
$st->bindValue(':url_id', $urlId, PDO::PARAM_INT);
$st->execute();
while ($row = $st->fetch(PDO::FETCH_ASSOC)) {
$ipAddress = long2ip($row['ip_address']);
if (!isset($ipCountryCityMap[$ipAddress])) {
$geoipDetails = $geoipProvider->getLocationDetailsByIP($ipAddress);
$ipCountryCityMap[$ipAddress] = array('country_code' => $geoipDetails['country_code'], 'city_name' => $geoipDetails['city_name']);
}
$countryCode = $ipCountryCityMap[$ipAddress]['country_code'];
$cityName = $ipCountryCityMap[$ipAddress]['city_name'];
if (!isset($countryCodeCountryIdMap[$countryCode])) {
$sqlCountry = 'SELECT country_id FROM newmonk_common.country WHERE code = :code';
$stCountry = $db->prepare($sqlCountry);
$stCountry->bindValue(':code', $countryCode ? $countryCode : '', PDO::PARAM_STR);
$stCountry->execute();
$rowCountry = $stCountry->fetch(PDO::FETCH_ASSOC);
$countryCodeCountryIdMap[$countryCode] = $rowCountry['country_id'];
$stCountry->closeCursor();
}
$countryId = $countryCodeCountryIdMap[$countryCode];
if (!isset($cityNameCityIdMap[$countryId][$cityName])) {
$sqlCity = 'SELECT city_id
FROM newmonk_common.city
WHERE country_id = :country_id
AND name = :name';
$stCity = $db->prepare($sqlCity);
$stCity->bindValue(':country_id', $countryId, PDO::PARAM_INT);
$stCity->bindValue(':name', $cityName ? $cityName : '', PDO::PARAM_STR);
$stCity->execute();
$rowCity = $stCity->fetch(PDO::FETCH_ASSOC);
$cityNameCityIdMap[$countryId][$cityName] = $rowCity['city_id'];
$stCity->closeCursor();
}
$cityId = $cityNameCityIdMap[$countryId][$cityName];
$countryWiseLoadTimes[$urlId][$countryId][] = $row['loadTime'];
$cityWiseLoadTimes[$urlId][$cityId][] = $row['loadTime'];
}
$st->closeCursor();
}
$countryWisePageViewsAndLoadTimes = $cityWisePageViewsAndLoadTimes = array();
foreach ($urlIdPageIdMap as $urlId => $pageId) {
foreach ($countryWiseLoadTimes[$urlId] as $countryId => $countryWiseLoadTime) {
$countryWisePageViewsAndLoadTimes[$urlId][] = array('countryId' => $countryId, 'pageViews' => count($countryWiseLoadTime), 'loadTime' => round(array_sum($countryWiseLoadTime) / count($countryWiseLoadTime), 2));
}
foreach ($cityWiseLoadTimes[$urlId] as $cityId => $cityWiseLoadTime) {
$cityWisePageViewsAndLoadTimes[$urlId][] = array('cityId' => $cityId, 'pageViews' => count($cityWiseLoadTime), 'loadTime' => round(array_sum($cityWiseLoadTime) / count($cityWiseLoadTime), 2));
}
}
$numValuesToInsert = 0;
$valuesToInsert = array();
$queryPrefix = 'INSERT INTO ' . $dbNames['summary'] . '.country_summary(page_id, hours_elapsed_since_last_even_year, country_id, page_views, avg_load_time) VALUES';
foreach ($urlIdPageIdMap as $urlId => $pageId) {
foreach ($countryWisePageViewsAndLoadTimes[$urlId] as $countryWisePageViewsAndLoadTime) {
if ($numValuesToInsert >= 5000) {
DbUtil::multipleInsert($db, $queryPrefix, $valuesToInsert, 5);
$numValuesToInsert = 0;
$valuesToInsert = array();
}
++$numValuesToInsert;
$valuesToInsert[] = $pageId;
$valuesToInsert[] = $hoursElapsedSinceLastEvenYear;
$valuesToInsert[] = $countryWisePageViewsAndLoadTime['countryId'];
$valuesToInsert[] = $countryWisePageViewsAndLoadTime['pageViews'];
$valuesToInsert[] = $countryWisePageViewsAndLoadTime['loadTime'];
}
}
DbUtil::multipleInsert($db, $queryPrefix, $valuesToInsert, 5);
$numValuesToInsert = 0;
$valuesToInsert = array();
$queryPrefix = 'INSERT INTO ' . $dbNames['summary'] . '.city_summary(page_id, hours_elapsed_since_last_even_year, city_id, page_views, avg_load_time) VALUES';
foreach ($urlIdPageIdMap as $urlId => $pageId) {
foreach ($cityWisePageViewsAndLoadTimes[$urlId] as $cityWisePageViewsAndLoadTime) {
if ($numValuesToInsert >= 5000) {
DbUtil::multipleInsert($db, $queryPrefix, $valuesToInsert, 5);
$numValuesToInsert = 0;
$valuesToInsert = array();
}
++$numValuesToInsert;
$valuesToInsert[] = $pageId;
$valuesToInsert[] = $hoursElapsedSinceLastEvenYear;
$valuesToInsert[] = $cityWisePageViewsAndLoadTime['cityId'];
$valuesToInsert[] = $cityWisePageViewsAndLoadTime['pageViews'];
$valuesToInsert[] = $cityWisePageViewsAndLoadTime['loadTime'];
}
}
DbUtil::multipleInsert($db, $queryPrefix, $valuesToInsert, 5);
}