public function getTransQuery($champ)
{
$tanslation = $this->filter->getCorFilter();
if (array_key_exists($champ, $tanslation)) {
return $tanslation[$champ];
} else {
return $champ;
}
}
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 getNbConn(Application $app, $dmin, $dmax, $sbas_id, $list_coll_id)
{
$databox = $app->findDataboxById($sbas_id);
$conn = $databox->get_connection();
$datefilter = module_report_sqlfilter::constructDateFilter($dmin, $dmax);
$params = array_merge([':site_id' => $app['conf']->get(['main', 'key'])], $datefilter['params']);
$finalfilter = $datefilter['sql'] . ' AND ';
$finalfilter .= 'log_date.site = :site_id';
/*
$sql = "SELECT COUNT(DISTINCT(log_date.id)) as nb
FROM log as log_date FORCE INDEX (date_site)
INNER JOIN log_colls FORCE INDEX (couple) ON (log_date.id = log_colls.log_id)
WHERE " . $finalfilter;
*/
$sql = "SELECT COUNT(DISTINCT(log_date.id)) as nb\n" . " FROM log as log_date FORCE INDEX (date_site)\n" . " WHERE " . $finalfilter . "\n";
// 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);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$stmt->closeCursor();
return (int) $row['nb'];
}
public static function activiteAddedTopTenUser(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, 'log_docs.date');
$params = [];
$params = array_merge($params, $datefilter['params']);
/*
$sql = "
SELECT tt.usrid, tt.user, sum( 1 ) AS nb
FROM (
SELECT DISTINCT(log.id), log.usrid, log.user
FROM (log_docs AS log_date)
INNER JOIN log FORCE INDEX (date_site) ON (log_date.log_id = log.id)
INNER JOIN log_colls FORCE INDEX (couple) ON (log.id = log_colls.log_id)
WHERE " . $datefilter['sql'] . " AND log_date.action = 'add'" .
(('' !== $collfilter['sql']) ? " AND (" . $collfilter['sql'] . ")" : '')
. ") AS tt
GROUP BY tt.usrid
ORDER BY nb ASC ";
*/
$sql = "" . " SELECT tt.usrid, tt.user, sum( 1 ) AS nb\n" . " FROM (\n" . " SELECT DISTINCT(log.id), log.usrid, log.user\n" . " FROM (log_docs)\n" . " INNER JOIN log FORCE INDEX (date_site) ON (log_docs.log_id = log.id)\n" . " WHERE " . $datefilter['sql'] . " AND log_docs.action = 'add'" . ") AS tt\n" . " GROUP BY tt.usrid\n" . " ORDER BY nb ASC ";
// 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) {
$result[$row['usrid']]['lib'] = $row['user'];
$result[$row['usrid']]['nb'] = $row['nb'];
$result[$row['usrid']]['id'] = $row['usrid'];
}
return $result;
}
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 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;
}
public function testReportFilter()
{
$filter = $this->filter->getReportFilter();
$this->checkFilter($filter);
}
public static function getTopDl(Application $app, $dmin, $dmax, $sbas_id, $list_coll_id)
{
$databox = $app->findDataboxById((int) $sbas_id);
$conn = $databox->get_connection();
$params = [':site_id' => $app['conf']->get(['main', 'key'])];
$datefilter = module_report_sqlfilter::constructDateFilter($dmin, $dmax);
$params = array_merge($params, $datefilter['params']);
$finalfilter = "";
$array = ['preview' => [], 'document' => []];
$finalfilter .= $datefilter['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
';
*/
$sql = "SELECT tt.id, tt.name, SUM(1) AS nb\n" . " FROM (\n" . " SELECT DISTINCT(log.id) AS log_id, log_docs.record_id as id, subdef.name\n" . " FROM ( log )\n" . " INNER JOIN log_docs ON (log.id = log_docs.log_id)\n" . " INNER JOIN subdef ON (log_docs.record_id = subdef.record_id)\n" . " WHERE (" . $finalfilter . ")\n" . " AND ( log_docs.action = 'download' OR log_docs.action = 'mail' )\n" . " AND subdef.name = log_docs.final\n" . " ) AS tt\n" . " GROUP BY id, name\n";
// 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) {
$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;
}
