/**
* @desc get all download by base by day
* @param array $tab config for html table
* @return array
*/
public function getDownloadByBaseByDay($tab = false)
{
$this->title = $this->app->trans('report:: telechargements par jour');
$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 tt.record_id, tt.the_date AS ddate, tt.final, SUM(1) AS nb\n FROM (\n SELECT DISTINCT(log.id), log_docs.date AS the_date, log_docs.final, log_docs.record_id\n FROM (log_docs)\n INNER JOIN log FORCE INDEX (date_site) ON (log.id = log_docs.log_id)\n LEFT JOIN record ON (log_docs.record_id = record.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 ) AS tt\n GROUP BY tt.final, ddate\n ORDER BY tt.the_date DESC";
// 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();
$this->setChamp($rs);
$this->setDisplay($tab);
$total = ['tot_doc' => 0, 'tot_prev' => 0, 'tot_dl' => 0];
$i = -1;
$last_date = null;
foreach ($rs as $row) {
$date = $this->app['date-formatter']->getPrettyString(new DateTime($row['ddate']));
if ($date != $last_date) {
$i++;
$this->result[$i] = ['ddate' => $date, 'document' => 0, 'preview' => 0, 'total' => 0];
$last_date = $date;
}
if ($row['final'] == 'document') {
$this->result[$i]['document'] += $row['nb'];
$total['tot_doc'] += $row['nb'];
} else {
$this->result[$i]['preview'] += $row['nb'];
$total['tot_prev'] += $row['nb'];
}
$this->result[$i]['total'] += $row['nb'];
$total['tot_dl'] += $row['nb'];
}
$nb_row = $i + 1;
$sqlBuilder->setTotalrows($nb_row);
if ($sqlBuilder->getTotalRows() > 0) {
$this->result[$nb_row]['ddate'] = '<b>TOTAL</b>';
$this->result[$nb_row]['document'] = '<b>' . $total['tot_doc'] . '</b>';
$this->result[$nb_row]['preview'] = '<b>' . $total['tot_prev'] . '</b>';
$this->result[$nb_row]['total'] = '<b>' . $total['tot_dl'] . '</b>';
}
// no_file_put_contents("/tmp/report.txt", sprintf("%s (%s)\n%s\n\n", __FILE__, __LINE__, var_export($this->result, true)), FILE_APPEND);
foreach ($this->result as $k => $row) {
$_row = array();
foreach ((array) $tab as $k2 => $f) {
$_row[$k2] = array_key_exists($k2, $row) ? $row[$k2] : '';
}
$this->result[$k] = $_row;
}
// no_file_put_contents("/tmp/report.txt", sprintf("%s (%s)\n%s\n\n", __FILE__, __LINE__, var_export($this->result, true)), FILE_APPEND);
$this->calculatePages();
$this->setDisplayNav();
$this->setReport();
return $this->report;
}
/**
* @desc get all download by base by day
* @param array $tab config for html table
* @return array
*/
public function getDownloadByBaseByDay($tab = false)
{
$this->title = $this->app->trans('report:: telechargements par jour');
$sqlBuilder = new module_report_sql($this->app, $this);
$filter = $sqlBuilder->getFilters()->getReportFilter();
$params = array_merge([], $filter['params']);
$sql = "\n SELECT tt.record_id, DATE_FORMAT(tt.the_date, GET_FORMAT(DATE, 'INTERNAL')) AS ddate, tt.final, SUM(1) AS nb\n FROM (\n SELECT DISTINCT(log.id), log_docs.date AS the_date, log_docs.final, log_docs.record_id\n FROM (log_docs)\n INNER JOIN record ON (record.record_id = log_docs.record_id)\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 WHERE (" . $filter['sql'] . ")\n AND (log_docs.action = 'download' OR log_docs.action = 'mail')\n AND (log_docs.final = 'preview' OR log_docs.final = 'document')\n ) AS tt\n LEFT JOIN subdef AS s ON (s.record_id = tt.record_id)\n WHERE s.name = tt.final\n GROUP BY tt.final, ddate\n ORDER BY tt.the_date DESC";
$stmt = $sqlBuilder->getConnBas()->prepare($sql);
$stmt->execute($params);
$rs = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt->closeCursor();
$this->setChamp($rs);
$this->setDisplay($tab);
$total = ['tot_doc' => 0, 'tot_prev' => 0, 'tot_dl' => 0];
$i = -1;
$last_date = null;
foreach ($rs as $row) {
$date = $this->app['date-formatter']->getPrettyString(new DateTime($row['ddate']));
if ($date != $last_date) {
$i++;
$this->result[$i] = ['ddate' => $date, 'document' => 0, 'preview' => 0, 'total' => 0];
$last_date = $date;
}
if ($row['final'] == 'document') {
$this->result[$i]['document'] += $row['nb'];
$total['tot_doc'] += $row['nb'];
} else {
$this->result[$i]['preview'] += $row['nb'];
$total['tot_prev'] += $row['nb'];
}
$this->result[$i]['total'] += $row['nb'];
$total['tot_dl'] += $row['nb'];
}
$nb_row = $i + 1;
$sqlBuilder->setTotalrows($nb_row);
if ($sqlBuilder->getTotalRows() > 0) {
$this->result[$nb_row]['ddate'] = '<b>TOTAL</b>';
$this->result[$nb_row]['document'] = '<b>' . $total['tot_doc'] . '</b>';
$this->result[$nb_row]['preview'] = '<b>' . $total['tot_prev'] . '</b>';
$this->result[$nb_row]['total'] = '<b>' . $total['tot_dl'] . '</b>';
}
$this->calculatePages();
$this->setDisplayNav();
$this->setReport();
return $this->report;
}
