Ejemplo n.º 1
0
 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;
 }
Ejemplo n.º 2
0
 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;
 }