/**
* @desc report the most consulted module by the users
* @param array $tab
* @return array
*/
public function buildTabModule($tab = false)
{
$this->initialize();
$sqlBuilder = new module_report_sql($this->app, $this);
$filter = $sqlBuilder->getFilters();
$this->title = $this->app->trans('report:: modules');
$x = [];
$tab_appli = [];
$this->total_pourcent = $this->setTotalPourcent();
if (is_null($this->total_pourcent)) {
return $this->report;
}
$report_filter = $filter->getReportFilter();
$params = array_merge([], $report_filter['params']);
$sql = '
SELECT tt.appli
FROM (
SELECT DISTINCT(log.id), appli
FROM log FORCE INDEX (date_site, appli)
INNER JOIN log_colls FORCE INDEX (couple) ON (log.id = log_colls.log_id)
WHERE ' . $report_filter['sql'] . ' AND appli != \'a:0:{}\'
) AS tt
GROUP BY tt.appli';
$this->initialize();
$stmt = $sqlBuilder->getConnBas()->prepare($sql);
$stmt->execute($params);
$rs = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt->closeCursor();
foreach ($rs as $row) {
$applis = false;
if (($applis = @unserialize($row['appli'])) !== false) {
array_push($x, phrasea::modulesName($this->app['translator'], $applis));
} else {
array_push($x, 'NULL');
}
}
foreach ($x as $key => $tab_value) {
if (is_array($tab_value)) {
foreach ($tab_value as $key2 => $value) {
if (!isset($tab_appli[$value])) {
$tab_appli[$value] = 0;
}
$tab_appli[$value]++;
}
}
}
$total = array_sum($tab_appli);
$this->setChamp($rs);
$this->setDisplay($tab);
foreach ($tab_appli as $appli => $nb) {
$pourcent = round($nb / $total * 100, 1);
foreach ($this->champ as $key => $value) {
$this->result[] = ['appli' => $appli, 'nb' => $nb, 'pourcent' => $pourcent . '%'];
}
$this->report['value'][] = $nb;
$this->report['legend'][] = $appli;
}
$this->total = sizeof($this->result);
$this->calculatePages();
$this->setDisplayNav();
$this->setReport();
return $this->report;
}
/**
* Get the detail of download by users
*
* @param array $tab config for the html table
* @param String $on
*
* @return array
*/
public function getDetailDownload($tab = false, $on = "")
{
empty($on) ? $on = "user" : "";
//by default always report on user
//set title
$this->title = $this->app->trans('report:: Detail des telechargements');
$this->setDateField('log_docs.date');
$sqlBuilder = new module_report_sql($this->app, $this);
$filter = $sqlBuilder->getFilters()->getReportFilter();
$params = array_merge([], $filter['params']);
$sql = "\n SELECT TRIM(" . $on . ") AS " . $on . ", SUM(1) AS nb, log_docs.final, log.usrid\n FROM log_docs\n INNER JOIN log FORCE INDEX (date_site) ON (log.id = log_docs.log_id)\n LEFT JOIN record ON (record.record_id = log_docs.record_id)\n WHERE (" . $filter['sql'] . ") AND !ISNULL(usrid)\n AND (log_docs.action = 'download' OR log_docs.action = 'mail')\n AND (log_docs.final = 'preview' OR log_docs.final = 'document')\n GROUP BY usrid";
// no_file_put_contents("/tmp/report.txt", sprintf("%s (%s)\n%s\n\n", __FILE__, __LINE__, $sql), FILE_APPEND);
$stmt = $sqlBuilder->getConnBas()->prepare($sql);
$stmt->execute($params);
$rs = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt->closeCursor();
$save_user = "";
$i = -1;
$total = ['nbdoc' => 0, 'nbprev' => 0];
$this->setChamp($rs);
$this->setDisplay($tab);
foreach ($rs as $row) {
$user = $row[$on];
if ($save_user != $user && !is_null($user) && !empty($user)) {
if ($i >= 0) {
if ($this->result[$i]['nbprev'] + $this->result[$i]['nbdoc'] == 0) {
unset($this->result[$i]);
}
}
$i++;
$this->result[$i]['nbprev'] = 0;
$this->result[$i]['nbdoc'] = 0;
}
//doc info
if ($row['final'] == 'document' && !is_null($user) && !is_null($row['usrid'])) {
$this->result[$i]['nbdoc'] = !is_null($row['nb']) ? $row['nb'] : 0;
$this->result[$i]['user'] = empty($row[$on]) ? "<i>" . $this->app->trans('report:: non-renseigne') . "</i>" : $row[$on];
$total['nbdoc'] += $this->result[$i]['nbdoc'];
$this->result[$i]['usrid'] = $row['usrid'];
}
//preview info
if ($row['final'] == 'preview' && !is_null($user) && !is_null($row['usrid'])) {
$this->result[$i]['nbprev'] += !is_null($row['nb']) ? $row['nb'] : 0;
$this->result[$i]['user'] = empty($row[$on]) ? "<i>" . $this->app->trans('report:: non-renseigne') . "</i>" : $row[$on];
$total['nbprev'] += !is_null($row['nb']) ? $row['nb'] : 0;
$this->result[$i]['usrid'] = $row['usrid'];
}
$save_user = $user;
}
unset($this->result[$i]);
$nb_row = $i + 1;
$this->total = $nb_row;
if ($this->total > 0) {
$this->result[$nb_row]['user'] = '******';
$this->result[$nb_row]['nbdoc'] = '<b>' . $total['nbdoc'] . '</b>';
$this->result[$nb_row]['nbprev'] = '<b>' . $total['nbprev'] . '</b>';
}
foreach ($this->result as $k => $row) {
$_row = array();
foreach ((array) $tab as $k2 => $f) {
$_row[$k2] = array_key_exists($k2, $row) ? $row[$k2] : '';
}
$_row['usrid'] = array_key_exists('usrid', $row) ? $row['usrid'] : '';
$this->result[$k] = $_row;
}
// no_file_put_contents("/tmp/report.txt", sprintf("%s (%s) %s\n\n", __FILE__, __LINE__, var_export($this->result, true)), FILE_APPEND);
$this->total = sizeof($this->result);
$this->calculatePages();
$this->setDisplayNav();
$this->setReport();
return $this->report;
}
/**
* @desc report the most consulted module by the users
* @param array $tab
* @return array
*/
public function buildTabModule($tab = false)
{
$this->initialize();
$sqlBuilder = new module_report_sql($this->app, $this);
$filter = $sqlBuilder->getFilters();
$this->title = $this->app->trans('report:: modules');
$x = [];
$tab_appli = [];
$this->total_pourcent = $this->setTotalPourcent();
if (is_null($this->total_pourcent)) {
return $this->report;
}
$report_filter = $filter->getReportFilter();
$params = array_merge([], $report_filter['params']);
$sql = '
SELECT appli
FROM log FORCE INDEX (date_site)
WHERE ' . $report_filter['sql'] . ' AND appli != \'a:0:{}\' AND !ISNULL(usrid)
GROUP BY appli';
// no_file_put_contents("/tmp/report.txt", sprintf("%s (%s)\n%s\n\n", __FILE__, __LINE__, $sql), FILE_APPEND);
$this->initialize();
$stmt = $sqlBuilder->getConnBas()->prepare($sql);
$stmt->execute($params);
$rs = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt->closeCursor();
foreach ($rs as $row) {
$applis = false;
if (($applis = @unserialize($row['appli'])) !== false) {
array_push($x, phrasea::modulesName($this->app['translator'], $applis));
} else {
array_push($x, 'NULL');
}
}
foreach ($x as $key => $tab_value) {
if (is_array($tab_value)) {
foreach ($tab_value as $key2 => $value) {
if (!isset($tab_appli[$value])) {
$tab_appli[$value] = 0;
}
$tab_appli[$value]++;
}
}
}
$total = array_sum($tab_appli);
$this->setChamp($rs);
$this->setDisplay($tab);
foreach ($tab_appli as $appli => $nb) {
$pourcent = round($nb / $total * 100, 1);
foreach ($this->champ as $key => $value) {
$this->result[] = ['appli' => $appli, 'nb' => $nb, 'pourcent' => $pourcent . '%'];
}
$this->report['value'][] = $nb;
$this->report['legend'][] = $appli;
}
$this->total = sizeof($this->result);
$this->calculatePages();
$this->setDisplayNav();
$this->setReport();
return $this->report;
}
/**
* Get the deail of download by users
*
* @param array $tab config for the html table
* @param String $on
*
* @return array
*/
public function getDetailDownload($tab = false, $on = "")
{
empty($on) ? $on = "user" : "";
//by default always report on user
//set title
$this->title = $this->app->trans('report:: Detail des telechargements');
$sqlBuilder = new module_report_sql($this->app, $this);
$filter = $sqlBuilder->getFilters()->getReportFilter();
$params = array_merge([], $filter['params']);
$sql = "\n SELECT tt.usrid, TRIM(" . $on . ") AS " . $on . ", tt.final, sum(1) AS nb, sum(size) AS poid\n FROM (\n SELECT DISTINCT(log.id), TRIM(" . $on . ") AS " . $on . ", log_docs.record_id, log_docs.final, log.usrid\n FROM log_docs\n INNER JOIN log FORCE INDEX (date_site) ON (log.id = log_docs.log_id)\n INNER JOIN log_colls FORCE INDEX (couple) ON (log.id = log_colls.log_id)\n INNER JOIN record ON (record.record_id = log_docs.record_id)\n WHERE (" . $filter['sql'] . ")\n AND (log_docs.action = 'download' OR log_docs.action = 'mail')\n ) AS tt\n LEFT JOIN subdef FORCE INDEX (unicite) ON (tt.record_id = subdef.record_id)\n WHERE subdef.name = tt.final\n GROUP BY " . $on . ", usrid\n ORDER BY nb DESC;";
$stmt = $sqlBuilder->getConnBas()->prepare($sql);
$stmt->execute($params);
$rs = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt->closeCursor();
$save_user = "";
$i = -1;
$total = ['nbdoc' => 0, 'poiddoc' => 0, 'nbprev' => 0, 'poidprev' => 0];
$this->setChamp($rs);
$this->setDisplay($tab);
foreach ($rs as $row) {
$user = $row[$on];
if ($save_user != $user && !is_null($user) && !empty($user)) {
if ($i >= 0) {
if ($this->result[$i]['nbprev'] + $this->result[$i]['nbdoc'] == 0 || $this->result[$i]['poiddoc'] + $this->result[$i]['poidprev'] == 0) {
unset($this->result[$i]);
}
if (isset($this->result[$i]['poiddoc']) && isset($this->result[$i]['poidprev'])) {
$this->result[$i]['poiddoc'] = p4string::format_octets($this->result[$i]['poiddoc']);
$this->result[$i]['poidprev'] = p4string::format_octets($this->result[$i]['poidprev']);
}
}
$i++;
$this->result[$i]['nbprev'] = 0;
$this->result[$i]['poidprev'] = 0;
$this->result[$i]['nbdoc'] = 0;
$this->result[$i]['poiddoc'] = 0;
}
//doc info
if ($row['final'] == 'document' && !is_null($user) && !is_null($row['usrid'])) {
$this->result[$i]['nbdoc'] = !is_null($row['nb']) ? $row['nb'] : 0;
$this->result[$i]['poiddoc'] = !is_null($row['poid']) ? $row['poid'] : 0;
$this->result[$i]['user'] = empty($row[$on]) ? "<i>" . $this->app->trans('report:: non-renseigne') . "</i>" : $row[$on];
$total['nbdoc'] += $this->result[$i]['nbdoc'];
$total['poiddoc'] += !is_null($row['poid']) ? $row['poid'] : 0;
$this->result[$i]['usrid'] = $row['usrid'];
}
//preview info
if (($row['final'] == 'preview' || $row['final'] == 'thumbnail') && !is_null($user) && !is_null($row['usrid'])) {
$this->result[$i]['nbprev'] += !is_null($row['nb']) ? $row['nb'] : 0;
$this->result[$i]['poidprev'] += !is_null($row['poid']) ? $row['poid'] : 0;
$this->result[$i]['user'] = empty($row[$on]) ? "<i>" . $this->app->trans('report:: non-renseigne') . "</i>" : $row[$on];
$total['nbprev'] += !is_null($row['nb']) ? $row['nb'] : 0;
$total['poidprev'] += !is_null($row['poid']) ? $row['poid'] : 0;
$this->result[$i]['usrid'] = $row['usrid'];
}
$save_user = $user;
}
unset($this->result[$i]);
$nb_row = $i + 1;
$this->total = $nb_row;
if ($this->total > 0) {
$this->result[$nb_row]['user'] = '******';
$this->result[$nb_row]['nbdoc'] = '<b>' . $total['nbdoc'] . '</b>';
$this->result[$nb_row]['poiddoc'] = '<b>' . p4string::format_octets($total['poiddoc']) . '</b>';
$this->result[$nb_row]['nbprev'] = '<b>' . $total['nbprev'] . '</b>';
$this->result[$nb_row]['poidprev'] = '<b>' . p4string::format_octets($total['poidprev']) . '</b>';
}
$this->total = sizeof($this->result);
$this->calculatePages();
$this->setDisplayNav();
$this->setReport();
return $this->report;
}
