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