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