/** * @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; }