public static function getNbConn(Application $app, $dmin, $dmax, $sbas_id, $list_coll_id)
{
$databox = $app['phraseanet.appbox']->get_databox($sbas_id);
$conn = $databox->get_connection();
$datefilter = module_report_sqlfilter::constructDateFilter($dmin, $dmax);
$collfilter = module_report_sqlfilter::constructCollectionFilter($app, $list_coll_id);
$params = array_merge([':site_id' => $app['conf']->get(['main', 'key'])], $datefilter['params'], $collfilter['params']);
$finalfilter = $datefilter['sql'] . ' AND ';
$finalfilter .= $collfilter['sql'] . ' AND ';
$finalfilter .= 'log_date.site = :site_id';
$sql = "SELECT COUNT(DISTINCT(log_date.id)) as nb\n FROM log as log_date FORCE INDEX (date_site)\n INNER JOIN log_colls FORCE INDEX (couple) ON (log_date.id = log_colls.log_id)\n WHERE " . $finalfilter;
$stmt = $conn->prepare($sql);
$stmt->execute($params);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$stmt->closeCursor();
return (int) $row['nb'];
}
public static function getTopDl(Application $app, $dmin, $dmax, $sbas_id, $list_coll_id)
{
$databox = $app['phraseanet.appbox']->get_databox((int) $sbas_id);
$conn = $databox->get_connection();
$params = [':site_id' => $app['conf']->get(['main', 'key'])];
$datefilter = module_report_sqlfilter::constructDateFilter($dmin, $dmax);
$collfilter = module_report_sqlfilter::constructCollectionFilter($app, $list_coll_id);
$params = array_merge($params, $datefilter['params'], $collfilter['params']);
$finalfilter = "";
$array = ['preview' => [], 'document' => []];
$finalfilter .= $datefilter['sql'] . ' AND ';
$finalfilter .= $collfilter['sql'] . ' AND ';
$finalfilter .= 'log.site = :site_id';
$sql = '
SELECT tt.id, tt.name, SUM(1) AS nb
FROM (
SELECT DISTINCT(log.id) AS log_id, log_date.record_id as id, subdef.name
FROM ( log )
INNER JOIN log_colls FORCE INDEX (couple) ON (log.id = log_colls.log_id)
INNER JOIN log_docs as log_date ON (log.id = log_date.log_id)
INNER JOIN subdef ON (log_date.record_id = subdef.record_id)
WHERE (
' . $finalfilter . '
)
AND ( log_date.action = \'download\'
OR log_date.action = \'mail\'
)
AND subdef.name = log_date.final
) AS tt
GROUP BY id, name
';
$stmt = $conn->prepare($sql);
$stmt->execute($params);
$rs = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt->closeCursor();
foreach ($rs as $row) {
$record = $databox->get_record($row['id']);
$k = $row['id'] . '_' . $sbas_id;
$orig_name = $record->get_original_name();
if ($row['name'] == 'document') {
$array[$row['name']][$k]['nb'] = (int) $row['nb'];
$array[$row['name']][$k]['lib'] = $orig_name;
$array[$row['name']][$k]['sbasid'] = $sbas_id;
$array[$row['name']][$k]['id'] = $row['id'];
} elseif ($row['name'] == "preview") {
$array[$row['name']][$k]['nb'] = (int) $row['nb'];
$array[$row['name']][$k]['lib'] = $orig_name;
$array[$row['name']][$k]['sbasid'] = $sbas_id;
$array[$row['name']][$k]['id'] = $row['id'];
}
}
return $array;
}
public static function activiteAddedTopTenUser(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.usrid, tt.user, sum( 1 ) AS nb\n FROM (\n SELECT DISTINCT(log.id), log.usrid, log.user\n FROM (log_docs AS log_date)\n INNER JOIN log FORCE INDEX (date_site) ON (log_date.log_id = log.id)\n INNER JOIN log_colls FORCE INDEX (couple) ON (log.id = log_colls.log_id)\n WHERE " . $datefilter['sql'] . " AND log_date.action = 'add'\n AND (" . $collfilter['sql'] . ")\n ) AS tt\n GROUP BY tt.usrid\n ORDER BY nb ASC ";
$stmt = $conn->prepare($sql);
$stmt->execute($params);
$rs = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt->closeCursor();
foreach ($rs as $row) {
$result[$row['usrid']]['lib'] = $row['user'];
$result[$row['usrid']]['nb'] = $row['nb'];
$result[$row['usrid']]['id'] = $row['usrid'];
}
return $result;
}
