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