| install/sql/incremental/upd_0081.sql | ●●●●● patch | view | raw | blame | history | |
| interface/web/sites/ftp_sites_stats.php | ●●●●● patch | view | raw | blame | history | |
| interface/web/sites/lib/lang/en_ftp_sites_stats_list.lng | ●●●●● patch | view | raw | blame | history | |
| interface/web/sites/lib/lang/fr_ftp_sites_stats_list.lng | ●●●●● patch | view | raw | blame | history | |
| interface/web/sites/list/ftp_sites_stats.list.php | ●●●●● patch | view | raw | blame | history | |
| interface/web/sites/templates/ftp_sites_stats_list.htm | ●●●●● patch | view | raw | blame | history | |
| server/lib/classes/cron.d/200-ftplogfiles.inc.php | ●●●●● patch | view | raw | blame | history |
install/sql/incremental/upd_0081.sql
New file @@ -0,0 +1 @@ CREATE TABLE `ftp_traffic` (`hostname` varchar(255) NOT NULL,`traffic_date` date NOT NULL,`in_bytes` bigint(32) unsigned NOT NULL,`out_bytes` bigint(32) unsigned NOT NULL, PRIMARY KEY (`hostname`,`traffic_date`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; interface/web/sites/ftp_sites_stats.php
New file @@ -0,0 +1,181 @@ <?php require_once '../../lib/config.inc.php'; require_once '../../lib/app.inc.php'; /****************************************** * Begin Form configuration ******************************************/ $list_def_file = "list/ftp_sites_stats.list.php"; /****************************************** * End Form configuration ******************************************/ //* Check permissions for module $app->auth->check_module_permissions('sites'); $app->uses('functions'); $app->load('listform_actions'); class list_action extends listform_actions { private $sum_this_month = 0; private $sum_this_year = 0; private $sum_last_month = 0; private $sum_last_year = 0; function prepareDataRow($rec) { global $app; $rec = $app->listform->decode($rec); //* Alternating datarow colors $this->DataRowColor = ($this->DataRowColor == '#FFFFFF') ? '#EEEEEE' : '#FFFFFF'; $rec['bgcolor'] = $this->DataRowColor; //* Set the statistics colums //** Traffic of the current month $tmp_year = date('Y'); $tmp_month = date('m'); $tmp_rec = $app->db->queryOneRecord("SELECT SUM(in_bytes) AS ftp_in, SUM(out_bytes) AS ftp_out FROM ftp_traffic WHERE hostname = ? AND YEAR(traffic_date) = ? AND MONTH(traffic_date) = ?", $rec['domain'], $tmp_year, $tmp_month); $rec['this_month_in'] = $app->functions->formatBytes($tmp_rec['ftp_in']); $rec['this_month_out'] = $app->functions->formatBytes($tmp_rec['ftp_out']); $this->sum_this_month += $tmp_rec['ftp_in']+$tmp_rec['ftp_out']; //** Traffic of the current year $tmp_rec = $app->db->queryOneRecord("SELECT SUM(in_bytes) AS ftp_in, SUM(out_bytes) AS ftp_out FROM ftp_traffic WHERE hostname = ? AND YEAR(traffic_date) = ?", $rec['domain'], $tmp_year); $rec['this_year_in'] = $app->functions->formatBytes($tmp_rec['ftp_in']); $rec['this_year_out'] = $app->functions->formatBytes($tmp_rec['ftp_out']); $this->sum_this_year += $tmp_rec['ftp_in']+$tmp_rec['ftp_out']; //** Traffic of the last month $tmp_year = date('Y', mktime(0, 0, 0, date("m")-1, date("d"), date("Y"))); $tmp_month = date('m', mktime(0, 0, 0, date("m")-1, date("d"), date("Y"))); $tmp_rec = $app->db->queryOneRecord("SELECT SUM(in_bytes) AS ftp_in, SUM(out_bytes) AS ftp_out FROM ftp_traffic WHERE hostname = ? AND YEAR(traffic_date) = ? AND MONTH(traffic_date) = ?", $rec['domain'], $tmp_year, $tmp_month); $rec['last_month_in'] = $app->functions->formatBytes($tmp_rec['ftp_in']); $rec['last_month_out'] = $app->functions->formatBytes($tmp_rec['ftp_out']); $this->sum_last_month += $tmp_rec['ftp_in']+$tmp_rec['ftp_out']; //** Traffic of the last year $tmp_year = date('Y', mktime(0, 0, 0, date("m"), date("d"), date("Y")-1)); $tmp_rec = $app->db->queryOneRecord("SELECT SUM(in_bytes) AS ftp_in, SUM(out_bytes) AS ftp_out FROM ftp_traffic WHERE hostname = ? AND YEAR(traffic_date) = ?", $rec['domain'], $tmp_year); $rec['last_year_in'] = $app->functions->formatBytes($tmp_rec['ftp_in']); $rec['last_year_out'] = $app->functions->formatBytes($tmp_rec['ftp_out']); $this->sum_last_year += $tmp_rec['ftp_in']+$tmp_rec['ftp_out']; //* The variable "id" contains always the index variable $rec['id'] = $rec[$this->idx_key]; return $rec; } function onShowEnd() { global $app; $app->tpl->setVar('sum_this_month', $app->functions->formatBytes($this->sum_this_month)); $app->tpl->setVar('sum_this_year', $app->functions->formatBytes($this->sum_this_year)); $app->tpl->setVar('sum_last_month', $app->functions->formatBytes($this->sum_last_month)); $app->tpl->setVar('sum_last_year', $app->functions->formatBytes($this->sum_last_year)); $app->tpl->setVar('sum_txt', $app->listform->lng('sum_txt')); $app->tpl_defaults(); $app->tpl->pparse(); } function getQueryString($no_limit = false) { global $app; $sql_where = ''; //* Generate the search sql if($app->listform->listDef['auth'] != 'no') { if($_SESSION['s']['user']['typ'] == "admin") { $sql_where = ''; } else { $sql_where = $app->tform->getAuthSQL('r', $app->listform->listDef['table']).' and'; //$sql_where = $app->tform->getAuthSQL('r').' and'; } } if($this->SQLExtWhere != '') { $sql_where .= ' '.$this->SQLExtWhere.' and'; } $sql_where = $app->listform->getSearchSQL($sql_where); if($app->listform->listDef['join_sql']) $sql_where .= ' AND '.$app->listform->listDef['join_sql']; $app->tpl->setVar($app->listform->searchValues); $order_by_sql = $this->SQLOrderBy; //* Generate SQL for paging $limit_sql = $app->listform->getPagingSQL($sql_where); $app->tpl->setVar('paging', $app->listform->pagingHTML); $extselect = ''; $join = ''; if(!empty($_SESSION['search'][$_SESSION['s']['module']['name'].$app->listform->listDef["name"].$app->listform->listDef['table']]['order'])){ $order = str_replace(' DESC', '', $_SESSION['search'][$_SESSION['s']['module']['name'].$app->listform->listDef["name"].$app->listform->listDef['table']]['order']); list($tmp_table, $order) = explode('.', $order); if($order == 'ftp_traffic_last_month'){ $tmp_year = date('Y', mktime(0, 0, 0, date("m")-1, date("d"), date("Y"))); $tmp_month = date('m', mktime(0, 0, 0, date("m")-1, date("d"), date("Y"))); $extselect .= ', SUM(ft.in_bytes+out_bytes) as calctraffic'; $join .= ' INNER JOIN ftp_traffic as ft ON '.$app->listform->listDef['table'].'.domain = ft.hostname '; $sql_where .= " AND YEAR(ft.traffic_date) = '$tmp_year' AND MONTH(ft.traffic_date) = '$tmp_month'"; $order_by_sql = str_replace($app->listform->listDef['table'].'.ftp_traffic_last_month', 'calctraffic', $order_by_sql); $order_by_sql = "GROUP BY domain ".$order_by_sql; } elseif($order == 'ftp_traffic_this_month'){ $tmp_year = date('Y'); $tmp_month = date('m'); $extselect .= ', SUM(ft.in_bytes+out_bytes) as calctraffic'; $join .= ' INNER JOIN ftp_traffic as ft ON '.$app->listform->listDef['table'].'.domain = ft.hostname '; $sql_where .= " AND YEAR(ft.traffic_date) = '$tmp_year' AND MONTH(ft.traffic_date) = '$tmp_month'"; $order_by_sql = str_replace($app->listform->listDef['table'].'.ftp_traffic_this_month', 'calctraffic', $order_by_sql); $order_by_sql = "GROUP BY domain ".$order_by_sql; } elseif($order == 'ftp_traffic_last_year'){ $tmp_year = date('Y', mktime(0, 0, 0, date("m")-1, date("d"), date("Y"))); $extselect .= ', SUM(ft.in_bytes+out_bytes) as calctraffic'; $join .= ' INNER JOIN ftp_traffic as ft ON '.$app->listform->listDef['table'].'.domain = ft.hostname '; $sql_where .= " AND YEAR(ft.traffic_date) = '$tmp_year'"; $order_by_sql = str_replace($app->listform->listDef['table'].'.ftp_traffic_last_year', 'calctraffic', $order_by_sql); $order_by_sql = "GROUP BY domain ".$order_by_sql; } elseif($order == 'ftp_traffic_this_year'){ $tmp_year = date('Y'); $extselect .= ', SUM(ft.in_bytes+out_bytes) as calctraffic'; $join .= ' INNER JOIN ftp_traffic as ft ON '.$app->listform->listDef['table'].'.domain = ft.hostname '; $sql_where .= " AND YEAR(ft.traffic_date) = '$tmp_year'"; $order_by_sql = str_replace($app->listform->listDef['table'].'.ftp_traffic_this_year', 'calctraffic', $order_by_sql); $order_by_sql = "GROUP BY domain ".$order_by_sql; } } if($this->SQLExtSelect != '') { if(substr($this->SQLExtSelect, 0, 1) != ',') $this->SQLExtSelect = ','.$this->SQLExtSelect; $extselect .= $this->SQLExtSelect; } $table_selects = array(); $table_selects[] = trim($app->listform->listDef['table']).'.*'; $app->listform->listDef['additional_tables'] = trim($app->listform->listDef['additional_tables']); if($app->listform->listDef['additional_tables'] != ''){ $additional_tables = explode(',', $app->listform->listDef['additional_tables']); foreach($additional_tables as $additional_table){ $table_selects[] = trim($additional_table).'.*'; } } $select = implode(', ', $table_selects); $sql = 'SELECT '.$select.$extselect.' FROM '.$app->listform->listDef['table'].($app->listform->listDef['additional_tables'] != ''? ','.$app->listform->listDef['additional_tables'] : '')."$join WHERE $sql_where $order_by_sql $limit_sql"; return $sql; } } $list = new list_action; $list->SQLExtWhere = "(web_domain.type = 'vhost' or web_domain.type = 'vhostsubdomain')"; $list->SQLOrderBy = 'ORDER BY web_domain.domain'; $list->onLoad(); ?> interface/web/sites/lib/lang/en_ftp_sites_stats_list.lng
New file @@ -0,0 +1,10 @@ <?php $wb["list_head_txt"] = 'FTP traffic'; $wb["domain_txt"] = 'Domain'; $wb["this_month_txt"] = 'This month'; $wb["last_month_txt"] = 'Last month'; $wb["this_year_txt"] = 'This year'; $wb["last_year_txt"] = 'Last year'; $wb["sum_txt"] = 'Sum (Download + Upload)'; $wb["in_out_txt"] = 'DL/UL'; ?> interface/web/sites/lib/lang/fr_ftp_sites_stats_list.lng
New file @@ -0,0 +1,10 @@ <?php $wb["list_head_txt"] = 'FTP trafic'; $wb["domain_txt"] = 'Domaine'; $wb["this_month_txt"] = 'Mois en cours'; $wb["last_month_txt"] = 'Mois précédent'; $wb["this_year_txt"] = 'Année en cours'; $wb["last_year_txt"] = 'Année précédente'; $wb["sum_txt"] = 'Total (Download + Upload)'; $wb["in_out_txt"] = 'DL/UL'; ?> interface/web/sites/list/ftp_sites_stats.list.php
New file @@ -0,0 +1,60 @@ <?php /* Datatypes: - INTEGER - DOUBLE - CURRENCY - VARCHAR - TEXT - DATE */ // Name of the list $liste["name"] = "ftp_sites_stats"; // Database table $liste["table"] = "web_domain"; // Index index field of the database table $liste["table_idx"] = "domain_id"; // Search Field Prefix $liste["search_prefix"] = "search_"; // Records per page $liste["records_per_page"] = "15"; // Script File of the list $liste["file"] = "web_sites_stats.php"; // Script file of the edit form $liste["edit_file"] = "web_domain_edit.php"; // Script File of the delete script $liste["delete_file"] = "web_domain_del.php"; // Paging Template $liste["paging_tpl"] = "templates/paging.tpl.htm"; // Enable auth $liste["auth"] = "yes"; /***************************************************** * Suchfelder *****************************************************/ $liste["item"][] = array( 'field' => "domain", 'datatype' => "VARCHAR", 'filters' => array( 0 => array( 'event' => 'SHOW', 'type' => 'IDNTOUTF8') ), 'formtype' => "TEXT", 'op' => "like", 'prefix' => "%", 'suffix' => "%", 'width' => "", 'value' => ""); ?> interface/web/sites/templates/ftp_sites_stats_list.htm
New file @@ -0,0 +1,60 @@ <div class='page-header'> <h1><tmpl_var name="list_head_txt"></h1> </div> <p><tmpl_var name="list_desc_txt"></p> <p class="fieldset-legend"><tmpl_var name="list_head_txt"></p> <div class="table-wrapper marginTop15"> <table class="table"> <thead class="dark form-group-sm"> <tr> <th data-column="domain"><tmpl_var name="domain_txt"></th> <th data-column="this_month"><tmpl_var name="this_month_txt"></th> <th data-column="last_month"><tmpl_var name="last_month_txt"></th> <th data-column="this_year"><tmpl_var name="this_year_txt"></th> <th data-column="last_year"><tmpl_var name="last_year_txt"></th> <th class="small-col text-right">{tmpl_var name='search_limit'}</th> </tr> <tr> <td><input class="form-control" type="text" name="search_domain" value="{tmpl_var name='search_domain'}" /></td> <td><tmpl_var name="in_out_txt"></td> <td><tmpl_var name="in_out_txt"></td> <td><tmpl_var name="in_out_txt"></td> <td><tmpl_var name="in_out_txt"></td> <td class="text-right"> <button type="button" class="btn btn-default formbutton-default formbutton-narrow" name="Filter" id="Filter" value="{tmpl_var name="filter_txt"}" data-submit-form="pageForm" data-form-action="sites/ftp_sites_stats.php"><span class="icon icon-filter"></span></button> </td> </tr> </thead> <tbody> <tmpl_loop name="records"> <tr> <td><a target="_blank" href="http://{tmpl_var name="domain"}/stats">{tmpl_var name="domain"}</a></td> <td><a href="#" data-load-content="sites/web_<tmpl_if name='type' op='==' value='vhostsubdomain'>vhost_sub</tmpl_if><tmpl_if name='type' op='==' value='vhostalias'>vhost_alias</tmpl_if>domain_edit.php?id={tmpl_var name='id'}">{tmpl_var name="this_month_in"}/{tmpl_var name="this_month_out"}</a></td> <td><a href="#" data-load-content="sites/web_<tmpl_if name='type' op='==' value='vhostsubdomain'>vhost_sub</tmpl_if><tmpl_if name='type' op='==' value='vhostalias'>vhost_alias</tmpl_if>domain_edit.php?id={tmpl_var name='id'}">{tmpl_var name="last_month_in"}/{tmpl_var name="last_month_out"}</a></td> <td><a href="#" data-load-content="sites/web_<tmpl_if name='type' op='==' value='vhostsubdomain'>vhost_sub</tmpl_if><tmpl_if name='type' op='==' value='vhostalias'>vhost_alias</tmpl_if>domain_edit.php?id={tmpl_var name='id'}">{tmpl_var name="this_year_in"}/{tmpl_var name="this_year_out"}</a></td> <td><a href="#" data-load-content="sites/web_<tmpl_if name='type' op='==' value='vhostsubdomain'>vhost_sub</tmpl_if><tmpl_if name='type' op='==' value='vhostalias'>vhost_alias</tmpl_if>domain_edit.php?id={tmpl_var name='id'}">{tmpl_var name="last_year_in"}/{tmpl_var name="last_year_out"}</a></td> <td class="text-right"></td> </tr> </tmpl_loop> <tmpl_unless name="records"> <tr class="tbl_row_noresults tbl_row_<tmpl_if name='__EVEN__'}even<tmpl_else>uneven</tmpl_if>"> <td colspan="6">{tmpl_var name='globalsearch_noresults_text_txt'}</td> </tr> </tmpl_unless> <tr> <td><a href="#" onclick="return false;" style="font-weight:bold;">{tmpl_var name="sum_txt"}</a></td> <td><a href="#" onclick="return false;" style="font-weight:bold;">{tmpl_var name="sum_this_month"}</a></td> <td><a href="#" onclick="return false;" style="font-weight:bold;">{tmpl_var name="sum_last_month"}</a></td> <td><a href="#" onclick="return false;" style="font-weight:bold;">{tmpl_var name="sum_this_year"}</a></td> <td><a href="#" onclick="return false;" style="font-weight:bold;">{tmpl_var name="sum_last_year"}</a></td> <td class="text-right"></td> </tr> </tbody> <tfoot> <tr> <td colspan="6"><tmpl_var name="paging"></td> </tr> </tfoot> </table> </div> server/lib/classes/cron.d/200-ftplogfiles.inc.php
New file @@ -0,0 +1,126 @@ <?php /* * Author Gody - Orm 2016 * You need to configure daily log rotation for pureftp (/etc/logorate.d/pure-ftpd-comon) * TODO: replace logrotate to ISPConfig log rotation */ class cronjob_ftplogfiles extends cronjob { // job schedule protected $_schedule = '0 0 * * *'; /* this function is optional if it contains no custom code */ public function onPrepare() { global $app; parent::onPrepare(); } /* this function is optional if it contains no custom code */ public function onBeforeRun() { global $app; return parent::onBeforeRun(); } public function onRunJob() { global $app, $conf; //###################################################################################################### // Make the ftp logfiles directories world readable to enable ftp access //###################################################################################################### if(is_dir('/var/log/pure-ftpd/')) exec('chmod +r /var/log/pure-ftpd/*'); //###################################################################################################### // Manage and compress ftp logfiles and create traffic statistics //###################################################################################################### $sql = "SELECT domain_id, domain, type, document_root, web_folder, parent_domain_id FROM web_domain WHERE (type = 'vhost' or type = 'vhostsubdomain' or type = 'vhostalias') AND server_id = ?"; $records = $app->db->queryAllRecords($sql, $conf['server_id']); function parse_ftp_log($line){ if(preg_match('/^\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3} - (.+) \[(\d+\/\w+\/\d+):.+\] "(PUT|GET) .+" \d+ (\d+)$/', $line, $matches) == false) return false; $date = date('Y-m-d', strtotime(str_replace('/', '-', $matches[2]))); // Correction date switch($matches[3]) { case 'PUT': $direction = 'in'; break; case 'GET': $direction = 'out'; break; } // Returned array return array('username' => $matches[1], 'date' => $date,'direction' => $direction, 'size' => $matches[4]); } function add_ftp_traffic(&$traffic_array, $parsed_line) { if(is_array($traffic_array[$parsed_line['date']]) && array_key_exists($parsed_line['domain'], $traffic_array[$parsed_line['date']])) { $traffic_array[$parsed_line['date']][$parsed_line['domain']][$parsed_line['direction']] += $parsed_line['size']; } else { $traffic_array[$parsed_line['date']][$parsed_line['domain']] = array('in' => 0, 'out' => 0 ); $traffic_array[$parsed_line['date']][$parsed_line['domain']][$parsed_line['direction']] = $parsed_line['size']; } } $fp = fopen('/var/log/pure-ftpd/transfer.log.1', 'r'); $ftp_traffic = array(); // cumule des stats journalière dans un tableau while($line = fgets($fp)) { $parsed_line = parse_ftp_log($line); $sql = "SELECT wd.domain FROM ftp_user AS fu INNER JOIN web_domain AS wd ON fu.parent_domain_id = wd.domain_id WHERE fu.username = ? "; $temp = $app->db->queryOneRecord($sql, $parsed_line['username'] ); $parsed_line['domain'] = $temp['domain']; add_ftp_traffic($ftp_traffic, $parsed_line); } fclose($fp); // Save du tableau en BD foreach($ftp_traffic as $traffic_date => $all_traffic) { foreach ( $all_traffic as $hostname =>$traffic) { $sql1 = "SELECT hostname FROM ftp_traffic WHERE hostname = ? AND traffic_date = ?"; $tmp = $app->dbmaster->queryOneRecord($sql1, $hostname , $traffic_date); if(is_array($tmp) && count($tmp) > 0) { $sql = "UPDATE ftp_traffic SET in_bytes=in_bytes+ ?, out_bytes=out_bytes+ ? WHERE hostname = ? AND traffic_date = ? "; } else { $sql = "INSERT INTO ftp_traffic (in_bytes, out_bytes, hostname, traffic_date ) VALUES ( ?, ?, ?, ? )"; } $resultat = $app->dbmaster->query($sql, $traffic['in'], $traffic['out'], $hostname, $traffic_date ); //if($resultat == 1){ //echo 'finished.'.PHP_EOL; // maybe you have a better solution ? } } } parent::onRunJob(); } /* this function is optional if it contains no custom code */ public function onAfterRun() { global $app; parent::onAfterRun(); } } ?>
