/**
* For 2.4 upgrade: migrate Timed views statistics to the metrics table.
*/
function migrateTimedViewsUsageStatistics()
{
$metricsDao = DAORegistry::getDAO('MetricsDAO');
/* @var $metricsDao MetricsDAO */
$result =& $metricsDao->retrieve('SELECT * FROM timed_views_log');
if ($result->EOF) {
return true;
}
$loadId = '3.0.0-upgrade-timedViews';
$metricsDao->purgeLoadBatch($loadId);
$plugin = PluginRegistry::getPlugin('generic', 'usagestatsplugin');
$plugin->import('UsageStatsTemporaryRecordDAO');
$tempStatsDao = new UsageStatsTemporaryRecordDAO();
$tempStatsDao->deleteByLoadId($loadId);
import('plugins.generic.usageStats.GeoLocationTool');
$geoLocationTool = new GeoLocationTool();
while (!$result->EOF) {
$row = $result->GetRowAssoc(false);
list($countryId, $cityName, $region) = $geoLocationTool->getGeoLocation($row['ip_address']);
if ($row['galley_id']) {
$assocType = ASSOC_TYPE_GALLEY;
$assocId = $row['galley_id'];
} else {
$assocType = ASSOC_TYPE_SUBMISSION;
$assocId = $row['submission_id'];
}
$day = date('Ymd', strtotime($row['date']));
$tempStatsDao->insert($assocType, $assocId, $day, $countryId, $region, $cityName, null, $loadId);
$result->MoveNext();
}
// Articles.
$params = array(OJS_METRIC_TYPE_TIMED_VIEWS, $loadId, ASSOC_TYPE_SUBMISSION);
$tempStatsDao->update('INSERT INTO metrics (load_id, metric_type, assoc_type, assoc_id, day, country_id, region, city, submission_id, metric, context_id, issue_id)
SELECT tr.load_id, ?, tr.assoc_type, tr.assoc_id, tr.day, tr.country_id, tr.region, tr.city, tr.assoc_id, count(tr.metric), a.context_id, pa.issue_id
FROM usage_stats_temporary_records AS tr
LEFT JOIN submissions AS a ON a.submission_id = tr.assoc_id
LEFT JOIN published_submissions AS pa ON pa.submission_id = tr.assoc_id
WHERE tr.load_id = ? AND tr.assoc_type = ? AND a.context_id IS NOT NULL AND pa.issue_id IS NOT NULL
GROUP BY tr.assoc_type, tr.assoc_id, tr.day, tr.country_id, tr.region, tr.city, tr.file_type, tr.load_id', $params);
// Galleys.
$params = array(OJS_METRIC_TYPE_TIMED_VIEWS, $loadId, ASSOC_TYPE_GALLEY);
$tempStatsDao->update('INSERT INTO metrics (load_id, metric_type, assoc_type, assoc_id, day, country_id, region, city, submission_id, metric, context_id, issue_id)
SELECT tr.load_id, ?, tr.assoc_type, tr.assoc_id, tr.day, tr.country_id, tr.region, tr.city, ag.submission_id, count(tr.metric), a.context_id, pa.issue_id
FROM usage_stats_temporary_records AS tr
LEFT JOIN submission_galleys AS ag ON ag.galley_id = tr.assoc_id
LEFT JOIN submissions AS a ON a.submission_id = ag.submission_id
LEFT JOIN published_submissions AS pa ON pa.submission_id = ag.submission_id
WHERE tr.load_id = ? AND tr.assoc_type = ? AND a.context_id IS NOT NULL AND pa.issue_id IS NOT NULL
GROUP BY tr.assoc_type, tr.assoc_id, tr.day, tr.country_id, tr.region, tr.city, tr.file_type, tr.load_id', $params);
$tempStatsDao->deleteByLoadId($loadId);
// Remove the plugin settings.
$metricsDao->update('DELETE FROM plugin_settings WHERE plugin_name = ?', array('timedviewplugin'), false);
return true;
}
/**
* For 2.4 upgrade: migrate Timed views statistics to the metrics table.
*/
function migrateTimedViewsUsageStatistics()
{
$metricsDao =& DAORegistry::getDAO('MetricsDAO');
/* @var $metricsDao MetricsDAO */
$result =& $metricsDao->retrieve('SELECT * FROM timed_views_log');
if ($result->EOF) {
return true;
}
$loadId = '2.4.2-upgrade-timedViews';
$metricsDao->purgeLoadBatch($loadId);
$plugin =& PluginRegistry::getPlugin('generic', 'usagestatsplugin');
$plugin->import('UsageStatsTemporaryRecordDAO');
$tempStatsDao = new UsageStatsTemporaryRecordDAO();
$tempStatsDao->deleteByLoadId($loadId);
import('plugins.generic.usageStats.GeoLocationTool');
$geoLocationTool = new GeoLocationTool();
$articleGalleyDao =& DAORegistry::getDAO('ArticleGalleyDAO');
/* @var $articleGalleyDao ArticleGalleyDAO */
while (!$result->EOF) {
$row =& $result->GetRowAssoc(false);
$result->MoveNext();
list($countryId, $cityName, $region) = $geoLocationTool->getGeoLocation($row['ip_address']);
$fileType = null;
if ($row['galley_id']) {
// Get the file type.
$galley =& $articleGalleyDao->getGalley($row['galley_id']);
if (is_a($galley, 'ArticleGalley')) {
if ($galley->isHTMLGalley()) {
$fileType = STATISTICS_FILE_TYPE_HTML;
}
if ($galley->isPdfGalley()) {
$fileType = STATISTICS_FILE_TYPE_PDF;
}
if (!$fileType) {
$fileType = STATISTICS_FILE_TYPE_OTHER;
}
} else {
// No galley.
continue;
}
$assocType = ASSOC_TYPE_GALLEY;
$assocId = $row['galley_id'];
} else {
$assocType = ASSOC_TYPE_ARTICLE;
$assocId = $row['article_id'];
}
$recordTimestamp = strtotime($row['date']);
$day = date('Ymd', $recordTimestamp);
$tempStatsDao->insert($assocType, $assocId, $day, $recordTimestamp, $countryId, $region, $cityName, $fileType, $loadId);
}
switch (Config::getVar('database', 'driver')) {
case 'mysql':
default:
$monthSql = 'extract(YEAR_MONTH from tr.day)';
break;
case 'postgres':
$monthSql = 'to_char(to_date(to_char(tr.day, \'99999999\'), \'YYYYMMDD\'), \'YYYYMM\')';
break;
}
// Articles.
$params = array(OJS_METRIC_TYPE_TIMED_VIEWS, $loadId, ASSOC_TYPE_ARTICLE);
$tempStatsDao->update('INSERT INTO metrics (load_id, metric_type, assoc_type, assoc_id, day, month, country_id, region, city, submission_id, metric, context_id, issue_id)
SELECT tr.load_id, ?, tr.assoc_type, tr.assoc_id, tr.day, ' . $monthSql . ', tr.country_id, tr.region, tr.city, tr.assoc_id, COUNT(tr.metric), a.journal_id, pa.issue_id
FROM usage_stats_temporary_records AS tr
JOIN articles AS a ON a.article_id = tr.assoc_id
JOIN published_articles AS pa ON pa.article_id = tr.assoc_id
WHERE tr.load_id = ? AND tr.assoc_type = ?
GROUP BY tr.assoc_type, tr.assoc_id, tr.day, tr.country_id, tr.region, tr.city, tr.file_type, tr.load_id, a.journal_id, pa.issue_id', $params);
// Galleys.
$params = array(OJS_METRIC_TYPE_TIMED_VIEWS, $loadId, ASSOC_TYPE_GALLEY);
$tempStatsDao->update('INSERT INTO metrics (load_id, metric_type, assoc_type, assoc_id, day, month, country_id, region, city, submission_id, metric, context_id, issue_id, file_type)
SELECT tr.load_id, ?, tr.assoc_type, tr.assoc_id, tr.day, ' . $monthSql . ', tr.country_id, tr.region, tr.city, ag.article_id, COUNT(tr.metric), a.journal_id, pa.issue_id, tr.file_type
FROM usage_stats_temporary_records AS tr
JOIN article_galleys AS ag ON ag.galley_id = tr.assoc_id
JOIN articles AS a ON a.article_id = ag.article_id
JOIN published_articles AS pa ON pa.article_id = ag.article_id
WHERE tr.load_id = ? AND tr.assoc_type = ?
GROUP BY tr.assoc_type, tr.assoc_id, tr.day, tr.country_id, tr.region, tr.city, tr.file_type, tr.load_id, ag.article_id, a.journal_id, pa.issue_id', $params);
$tempStatsDao->deleteByLoadId($loadId);
// Remove the plugin settings.
$metricsDao->update('delete from plugin_settings where plugin_name = ?', array('timedviewplugin'), false);
return true;
}
