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