public static function activiteTopTenSiteView(Application $app, $dmin, $dmax, $sbas_id, $list_coll_id)
{
$databox = $app->findDataboxById($sbas_id);
$conn = $databox->get_connection();
$result = [];
$datefilter = module_report_sqlfilter::constructDateFilter($dmin, $dmax);
$params = [];
$params = array_merge($params, $datefilter['params']);
/*
$sql = "
SELECT tt.referrer, SUM(1) AS nb_view
FROM (
SELECT DISTINCT(log_date.id), referrer
FROM (log_view)
INNER JOIN log AS log_date FORCE INDEX (date_site) ON (log_view.log_id = log_date.id)
INNER JOIN log_colls FORCE INDEX (couple) ON (log_date.id = log_colls.log_id)
WHERE " . $datefilter['sql'] . "" .
(('' !== $collfilter['sql']) ? " AND (" . $collfilter['sql'] . ")" : '')
. ") AS tt
GROUP BY referrer
ORDER BY nb_view DESC ";
*/
$sql = "SELECT tt.referrer, SUM(1) AS nb_view\n" . " FROM (\n" . " SELECT DISTINCT(log_date.id), referrer\n" . " FROM (log_view)\n" . " INNER JOIN log AS log_date FORCE INDEX (date_site) ON (log_view.log_id = log_date.id)\n" . " WHERE " . $datefilter['sql'] . ") AS tt\n" . " GROUP BY referrer\n" . " ORDER BY nb_view DESC ";
// no_file_put_contents("/tmp/report.txt", sprintf("%s (%s)\n%s\n\n", __FILE__, __LINE__, $sql), FILE_APPEND);
$stmt = $conn->prepare($sql);
$stmt->execute($params);
$rs = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt->closeCursor();
foreach ($rs as $row) {
$row['referrer'] != 'NO REFERRER' ? $host = parent::getHost($row['referrer']) : ($host = 'NO REFERRER');
if (!isset($result[$host]['nb'])) {
$result[$host]['nb'] = 0;
}
if (!isset($result[$host]['lib'])) {
$result[$host]['lib'] = $host;
}
$result[$host]['nb'] += (int) $row['nb_view'];
$result[$host]['id'] = "false";
}
return $result;
}
public static function activiteTopTenSiteView(Application $app, $dmin, $dmax, $sbas_id, $list_coll_id)
{
$databox = $app['phraseanet.appbox']->get_databox($sbas_id);
$conn = $databox->get_connection();
$result = [];
$datefilter = module_report_sqlfilter::constructDateFilter($dmin, $dmax);
$collfilter = module_report_sqlfilter::constructCollectionFilter($app, $list_coll_id);
$params = [];
$params = array_merge($params, $datefilter['params'], $collfilter['params']);
$sql = "\n SELECT tt.referrer, SUM(1) AS nb_view\n FROM (\n SELECT DISTINCT(log_date.id), referrer\n FROM (log_view)\n INNER JOIN log AS log_date FORCE INDEX (date_site) ON (log_view.log_id = log_date.id)\n INNER JOIN log_colls FORCE INDEX (couple) ON (log_date.id = log_colls.log_id)\n WHERE " . $datefilter['sql'] . "\n AND (" . $collfilter['sql'] . ")\n ) AS tt\n GROUP BY referrer\n ORDER BY nb_view DESC ";
$stmt = $conn->prepare($sql);
$stmt->execute($params);
$rs = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt->closeCursor();
foreach ($rs as $row) {
$row['referrer'] != 'NO REFERRER' ? $host = parent::getHost($row['referrer']) : ($host = 'NO REFERRER');
if (!isset($result[$host]['nb'])) {
$result[$host]['nb'] = 0;
}
if (!isset($result[$host]['lib'])) {
$result[$host]['lib'] = $host;
}
$result[$host]['nb'] += (int) $row['nb_view'];
$result[$host]['id'] = "false";
}
return $result;
}