--- /dev/null
+#!/usr/bin/perl
+
+#build 20191105
+
+use DBI; # DBI Perl!!!
+use URI;
+
+#=======================CONFIGURATION BEGIN============================
+
+#mysql default config
+$host = "localhost"; # host s DB
+$port = "3306"; # port DB
+$user = "squid"; # username k DB
+$pass = "ASxjC7Gftx8IvRfF"; # pasword k DB
+$db = "squid"; # name DB
+
+#==========================================================
+#Path to access.log. It could be full path, e.g. /var/log/squid/access.log
+#Put k access.log. Eto mozhet bit polnii put. Naprimer, /var/log/squid/access.log
+#Путь к файлу access.log(имя может другим). Это может быть и полный путь, например, /var/log/squid/access.log
+
+my $filetoparse="/var/log/squid/access.log";
+#==========================================================
+
+#How older data must be deleted. In example, older than 100 days from max date.
+#Period, starshe kotorogo dannie budut udaliatsia. Ukazivaetsia v dniah.
+#Период, старше которого данные будут удаляться. Указывается в днях.
+
+my $deleteperiod=120; #days
+#==========================================================
+#min bytes of traffic in one record to write into DB. By default - all data stored.
+
+my $minbytestoparse=-1; #bytes, default -1
+
+#=======================CONFIGURATION END==============================
+
+$count=0;
+$lastdate=0;
+$sqltext="";
+$sql_getlastdate="";
+
+$sqlbatch=100;
+$overlap=300;
+
+#datetime when parse started
+print $now=localtime;
+$startnow=time;
+
+$dbh = DBI->connect("DBI:mysql:$db:$host:$port",$user,$pass);
+
+$sth = $dbh->prepare("SELECT ifnull(unix_timestamp(max(access_date))-$overlap,0) from access_log");
+$sth->execute;
+@row=$sth->fetchrow_array;
+$lastdate=$row[0];
+
+if ($deleteperiod) {
+ $sth = $dbh->prepare("delete from access_log where access_date < date_sub(now(), interval $deleteperiod day)");
+ $sth->execute;
+}
+
+#open log file for reading
+open(IN, "<$filetoparse");
+
+$countlines=0;
+$countadded=0;
+
+print "\n";
+
+$sqlspool = 0;
+$sqltext = "";
+
+#loop for get strings from file one by one.
+while (my $line=<IN>) {
+
+ if(time > $seconds+1) {
+ $seconds=time;
+ $insertspeed=$countinsert;
+ }
+ $countinsert++;
+
+ @item = split " ", $line;
+
+ $bytes = $item[4];
+ $time = $item[0];
+ if (($bytes>0) && ($time>$lastdate)) {
+ $ms = $item[1];
+ $client_ip = $item[2];
+ $http_code = $item[3];
+ $method = $item[5];
+ $url = $item[6];
+ @matches = ($url=~ /(?:^[a-z][a-z0-9+\-.]*:\/\/)?(?:[a-z0-9\-._~%!$&'()*+,;=]+(?::.*)?@)?([a-z0-9\-._~%]+|\[[a-z0-9\-._~%!$&'()*+,;=:]+\])/i);
+ $host=$matches[0];
+ $user = $item[7];
+ $mime = $item[9];
+ if ($sqltext) {
+ $sqltext=$sqltext.",($time,$ms,'$client_ip','$http_code',$bytes,'$method','$host','$user','$mime')";
+ } else {
+ $sqltext="($time,$ms,'$client_ip','$http_code',$bytes,'$method','$host','$user','$mime')";
+ }
+ $sqlspool++;
+ if ($sqlspool > $sqlbatch) {
+ $sqltext = "insert into tmp_traffic(timestamp,process_time,client_host,http_code,bytes,http_method,server_host,username,mime_type) values".$sqltext;
+ $sth = $dbh->prepare($sqltext);
+ $sth->execute;
+ $sqlspool = 0;
+ $sqltext = "";
+ }
+ print "Completed: ".$countlines." ".$insertspeed." lines/sec\r";
+ $countlines++;
+ }
+
+}
+
+if ($sqltext) {
+ $sqltext = "insert into tmp_traffic(timestamp,process_time,client_host,http_code,bytes,http_method,server_host,username,mime_type) values".$sqltext;
+ $sth = $dbh->prepare($sqltext);
+ $sth->execute;
+}
+
+print "\n";
+close(IN);
+
+$sth = $dbh->prepare("CALL Process_Traffic();");
+$sth->execute;
+
+$rc = $dbh->disconnect;
--- /dev/null
+-- MySQL dump 10.13 Distrib 5.7.28, for Linux (x86_64)
+--
+-- Host: localhost Database: squid
+-- ------------------------------------------------------
+-- Server version 5.7.28-0ubuntu0.18.04.4
+
+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
+/*!40101 SET NAMES utf8 */;
+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
+/*!40103 SET TIME_ZONE='+00:00' */;
+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
+
+--
+-- Table structure for table `access_log`
+--
+
+DROP TABLE IF EXISTS `access_log`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `access_log` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `access_date` datetime NOT NULL,
+ `process_time` int(11) DEFAULT NULL,
+ `host_id` int(11) NOT NULL,
+ `code_id` int(11) NOT NULL,
+ `bytes` int(11) DEFAULT NULL,
+ `method_id` int(11) NOT NULL,
+ `site` varchar(1024) DEFAULT NULL,
+ `user_id` int(11) NOT NULL,
+ `mime_id` int(11) NOT NULL,
+ PRIMARY KEY (`id`),
+ KEY `KEY` (`access_date`,`host_id`,`bytes`,`user_id`)
+) ENGINE=InnoDB AUTO_INCREMENT=112422 DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `column_names`
+--
+
+DROP TABLE IF EXISTS `column_names`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `column_names` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `name` varchar(45) NOT NULL,
+ `alias` varchar(45) NOT NULL,
+ `template` varchar(1024) DEFAULT NULL,
+ `template_name` varchar(45) DEFAULT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Dumping data for table `column_names`
+--
+
+LOCK TABLES `column_names` WRITE;
+/*!40000 ALTER TABLE `column_names` DISABLE KEYS */;
+INSERT INTO `column_names` VALUES (1,'username','Логин',NULL,''),(2,'useralias','Пользователь',NULL,NULL),(3,'mb','Трафик, Мб',NULL,'column-right'),(4,'avg_kbits','Средняя скорость, Кбит/c',NULL,'column-right'),(5,'hostname','Адрес',NULL,NULL),(6,'hostalias','Устройство','',NULL),(7,'site','Сайт',NULL,NULL),(8,'requests','Количество обращений','',NULL),(9,'_user_id','Пользователь',NULL,NULL),(10,'_host_id','Хост','',NULL),(11,'hosts','Хосты','<td class=\"clickable-numeric\" onclick=\"ShowFilteredRep(\'by-host\',{user_id:$_user_id;});\">$hosts;</td>',''),(12,'users','Пользователи','<td class=\"clickable-numeric\" onclick=\"ShowFilteredRep(\'by-user\',{host_id:$_host_id;});\">$users;</td>',''),(13,'hosts_s','Хосты','<td class=\"clickable-numeric\" onclick=\"ShowFilteredRep(\'by-host\',{site:\'$site;\'});\">$hosts_s;</td>',''),(14,'users_s','Пользователи','<td class=\"clickable-numeric\" onclick=\"ShowFilteredRep(\'by-user\',{site:\'$site;\'});\">$users_s;</td>',''),(15,'sites_u','Сайты','<td class=\"clickable-numeric\" onclick=\"ShowFilteredRep(\'by-site\',{user_id:$_user_id;});\">$sites_u;</td>',NULL),(16,'sites_h','Сайты','<td class=\"clickable-numeric\" onclick=\"ShowFilteredRep(\'by-site\',{host_id:$_host_id;});\">$sites_h;</td>',NULL),(17,'username_hh','Логин','<td class=\"clickable-numeric\" onclick=\"ShowFilteredRep(\'by-hour\',{user_id:$_user_id;});\">$username_hh;</td>',''),(18,'ip','Хост-клиент',NULL,''),(19,'uri','Запрос',NULL,''),(20,'bytes','Байт передано',NULL,NULL),(21,'seconds','Активен, с','',''),(22,'host','Хост-клиент','<td>$host;($_ip;)</td>',''),(23,'user','Логин','',''),(24,'hour','Час',NULL,NULL),(25,'hostname_hh','Хост','<td class=\"clickable-numeric\" onclick=\"ShowFilteredRep(\'by-hour-hosts\',{host_id:$_host_id;});\">$hostname_hh;</td>','');
+/*!40000 ALTER TABLE `column_names` ENABLE KEYS */;
+UNLOCK TABLES;
+
+--
+-- Table structure for table `hosts`
+--
+
+DROP TABLE IF EXISTS `hosts`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `hosts` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `hostname` varchar(45) NOT NULL,
+ `alias` varchar(45) DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `hostname_UNIQUE` (`hostname`),
+ UNIQUE KEY `alias_UNIQUE` (`alias`)
+) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `http_codes`
+--
+
+DROP TABLE IF EXISTS `http_codes`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `http_codes` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `http_code` varchar(45) DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `http_code_UNIQUE` (`http_code`)
+) ENGINE=InnoDB AUTO_INCREMENT=58 DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `http_methods`
+--
+
+DROP TABLE IF EXISTS `http_methods`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `http_methods` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `method` varchar(45) DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `method_UNIQUE` (`method`)
+) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `mime_types`
+--
+
+DROP TABLE IF EXISTS `mime_types`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `mime_types` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `mime_type` varchar(45) NOT NULL,
+ `alias` varchar(45) DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `mime_type_UNIQUE` (`mime_type`),
+ UNIQUE KEY `alias_UNIQUE` (`alias`)
+) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `rep`
+--
+
+DROP TABLE IF EXISTS `rep`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `rep` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `cat_mnemo` varchar(45) NOT NULL,
+ `mnemo` varchar(16) DEFAULT NULL,
+ `name` varchar(45) DEFAULT NULL,
+ `description` varchar(1024) DEFAULT NULL,
+ `query` varchar(1024) DEFAULT NULL,
+ `has_total` varchar(45) DEFAULT NULL,
+ `graph_x` varchar(45) DEFAULT NULL,
+ `graph_series` varchar(45) DEFAULT NULL,
+ `graph_y` varchar(45) DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `name_UNIQUE` (`name`)
+) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Dumping data for table `rep`
+--
+
+LOCK TABLES `rep` WRITE;
+/*!40000 ALTER TABLE `rep` DISABLE KEYS */;
+INSERT INTO `rep` VALUES (1,'user','by-user','Трафик по пользователям','Распределение трафика по авторизованным пользователям','select 0 as _ordr,t.user_id as _user_id,u.username,u.alias as useralias,round(t.bytes/(1024*1024),2) mb,hosts,sites_u,round((t.bytes*8/t.seconds)/(1024)) avg_kbits from (select user_id,sum(bytes) bytes,sum(process_time)/1000 seconds,count(distinct host_id) hosts,count(distinct site) sites_u from access_log where (1=1) $FILTER; group by user_id) t left join users u on t.user_id=u.id union all select 1,null,\'ИТОГО:\',\'\',round(SUM(bytes)/(1024*1024),2) mb,\'\',\'\',round((SUM(bytes)*8/SUM(process_time/1000))/1024) avg_kbits from access_log where (1=1) $FILTER; order by _ordr,mb desc\n','1',NULL,NULL,NULL),(2,'host','by-host','Трафик по хостам','Распределение трафика по клиентским хостам','select 0 as _ordr,t.host_id as _host_id,h.hostname,h.alias as hostalias,round(t.bytes/(1024*1024),2) mb,sites_h,users,round((t.bytes*8/t.seconds)/(1024)) avg_kbits from (select host_id,sum(bytes) bytes,sum(process_time)/1000 seconds,count(distinct user_id) users, count(distinct site) sites_h from access_log where (1=1) $FILTER; group by host_id) t left join hosts h on t.host_id=h.id union all select 1,null,\'ИТОГО:\',\'\',round(SUM(bytes)/(1024*1024),2) mb,\'\',\'\',round((SUM(bytes)*8/SUM(process_time/1000))/1024) avg_kbits from access_log where (1=1) $FILTER; order by _ordr,mb desc','1',NULL,NULL,NULL),(3,'site','top-sites','Сайты с наибольшим трафиком','Сайты с нибольшим объемом трафика за интервал времени','select site,round(sum(bytes)/(1024*1024),2) mb,count(*) requests,count(distinct user_id) as users_s,count(distinct host_id) as hosts_s from access_log where (1=1) $FILTER; group by site order by mb desc limit 20 ','0',NULL,NULL,NULL),(4,'site','top-requests','Часто используемые сайты','Сайты с наибольшим количеством запросов','select site,round(sum(bytes)/(1024*1024),2) mb,count(*) requests,count(distinct user_id) as users_s,count(distinct host_id) as hosts_s from access_log where (1=1) $FILTER; group by site order by requests desc limit 20 ','0',NULL,NULL,NULL),(7,'site','by-site','Трафик по сайтам','Распределение трафика по сайтам','select 0 as _ordr,site,round(t.bytes/(1024*1024),2) mb,hosts as hosts_s,users as users_s,round((t.bytes*8/t.seconds)/(1024)) avg_kbits from (select site,sum(bytes) bytes,sum(process_time)/1000 seconds,count(distinct host_id) hosts,count(distinct user_id) users from access_log where (1=1) $FILTER; group by site) t union all select 1,\'ИТОГО:\',round(SUM(bytes)/(1024*1024),2) mb,\'\',\'\',round((SUM(bytes)*8/SUM(process_time/1000))/1024) avg_kbits from access_log where (1=1) $FILTER; order by _ordr,mb desc','1',NULL,NULL,NULL),(8,'user','big-files','Большие файлы','Кто скачивает большие файлы','select 0 as _ordr,t.user_id as _user_id,u.username,u.alias as useralias,site,round(t.bytes/(1024*1024),2) mb,round((t.bytes*8/t.seconds)/(1024)) avg_kbits from (select user_id,site,sum(bytes) bytes,sum(process_time/1000) seconds from access_log where bytes>10*1024*1024 $FILTER; group by user_id,site) t left join users u on t.user_id=u.id order by mb desc limit 20\n','0',NULL,NULL,NULL),(9,'user','by-hour','Трафик по времени суток','Распределеение трафика по пользователям в различные часы','select hour,t.user_id as _user_id,u.username as username_hh,u.alias as useralias,round(t.bytes/(1024*1024),2) mb,round((t.bytes*8/t.seconds)/(1024)) avg_kbits from (select user_id,date_format(access_date,\'%H\') hour,sum(bytes) bytes,sum(process_time)/1000 seconds,count(distinct site) sites from access_log where (1=1) $FILTER; group by user_id,date_format(access_date,\'%H\')) t left join users u on t.user_id=u.id order by hour,mb desc','','hour','useralias','mb'),(10,'host','by-hour-hosts','Трафик по хостам по времени cуток','Распределение трафика по хостам по времени суток','select hour,t.host_id as _host_id,h.hostname as hostname_hh,h.alias as hostalias,round(t.bytes/(1024*1024),2) mb,round((t.bytes*8/t.seconds)/(1024)) avg_kbits from (select host_id,date_format(access_date,\'%H\') hour,sum(bytes) bytes,sum(process_time)/1000 seconds,count(distinct site) sites from access_log where (1=1) $FILTER; group by host_id,date_format(access_date,\'%H\')) t left join hosts h on t.host_id=h.id order by hour,mb desc',NULL,'hour','hostalias','mb'),(11,'user','by-time','Трафик по времени','Распределеение трафика по пользователям в различные часы','select hour,t.user_id as _user_id,u.username as username_hh,u.alias as useralias,round(t.bytes/(1024*1024),2) mb,round((t.bytes*8/t.seconds)/(1024)) avg_kbits from (select user_id,date_format(access_date,\'%Y%m%d%H\') hour,sum(bytes) bytes,sum(process_time)/1000 seconds,count(distinct site) sites from access_log where (1=1) $FILTER; group by user_id,date_format(access_date,\'%Y%m%d%H\')) t left join users u on t.user_id=u.id order by hour,mb desc','','hour','useralias','mb'),(12,'host','by-time-hosts','Трафик по хостам по времени','Распределение трафика по хостам по времени суток','select hour,t.host_id as _host_id,h.hostname as hostname_hh,h.alias as hostalias,round(t.bytes/(1024*1024),2) mb,round((t.bytes*8/t.seconds)/(1024)) avg_kbits from (select host_id,date_format(access_date,\'%Y%m%d%H\') hour,sum(bytes) bytes,sum(process_time)/1000 seconds,count(distinct site) sites from access_log where (1=1) $FILTER; group by host_id,date_format(access_date,\'%Y%m%d%H\')) t left join hosts h on t.host_id=h.id order by hour,mb desc','','hour','hostalias','mb');
+/*!40000 ALTER TABLE `rep` ENABLE KEYS */;
+UNLOCK TABLES;
+
+--
+-- Table structure for table `rep_cat`
+--
+
+DROP TABLE IF EXISTS `rep_cat`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `rep_cat` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `mnemo` varchar(45) DEFAULT NULL,
+ `name` varchar(45) DEFAULT NULL,
+ `description` varchar(128) DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `name_UNIQUE` (`name`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Dumping data for table `rep_cat`
+--
+
+LOCK TABLES `rep_cat` WRITE;
+/*!40000 ALTER TABLE `rep_cat` DISABLE KEYS */;
+INSERT INTO `rep_cat` VALUES (1,'user','Пользователи','Отчеты по авторизованным пользователям'),(2,'host','Хосты','Отчеты по клиентским подключениям'),(3,'site','Сайты','Отчеты по посещаемым сайтам');
+/*!40000 ALTER TABLE `rep_cat` ENABLE KEYS */;
+UNLOCK TABLES;
+
+--
+-- Table structure for table `tmp_traffic`
+--
+
+DROP TABLE IF EXISTS `tmp_traffic`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `tmp_traffic` (
+ `timestamp` decimal(38,3) DEFAULT NULL,
+ `process_time` int(11) DEFAULT NULL,
+ `client_host` varchar(128) DEFAULT NULL,
+ `http_code` varchar(45) DEFAULT NULL,
+ `bytes` int(11) DEFAULT NULL,
+ `http_method` varchar(45) DEFAULT NULL,
+ `server_host` varchar(128) DEFAULT NULL,
+ `username` varchar(45) DEFAULT NULL,
+ `mime_type` varchar(45) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `users`
+--
+
+DROP TABLE IF EXISTS `users`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `users` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `username` varchar(45) NOT NULL,
+ `alias` varchar(45) DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `username_UNIQUE` (`username`),
+ UNIQUE KEY `alias_UNIQUE` (`alias`)
+) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `web_templates`
+--
+
+DROP TABLE IF EXISTS `web_templates`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `web_templates` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `mnemo` varchar(32) DEFAULT NULL,
+ `description` varchar(128) DEFAULT NULL,
+ `body` varchar(8192) DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `mnemo_UNIQUE` (`mnemo`)
+) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Dumping data for table `web_templates`
+--
+
+LOCK TABLES `web_templates` WRITE;
+/*!40000 ALTER TABLE `web_templates` DISABLE KEYS */;
+INSERT INTO `web_templates` VALUES (3,'menuitem','Вложенные пункты меню','<li class=\"menu-item\" onclick=\"ShowRep(\'$MNEMO;\');\">$NAME;</li>'),(4,'menugroup','Подменю','<div class=\"category\" id=\"$MNEMO;\" onclick=\"ShowHide(\'_li_$MNEMO;\');\">$NAME;</div><li id=\"_li_$MNEMO;\" class=\"slide\"><ul class=\"slide-menu\">$MENUITEM;</ul></li>'),(6,'menu','Верхний уровень меню','<ul class=\"nav\">$MENUGROUP;</ul>'),(9,'report-table','Общий шаблон таблицы','<table id=\"report\">$HEADER;$LINES;</table>'),(11,'report-table-total','Общий шаблон таблицы с итогом','<table id=\"report\">$HEADER;$LINES;$TOTAL;</table>'),(15,'column','Отображение столбца по умолчанию','<td>$VALUE;</td>'),(16,'header-column','Отображение заголовка по умолчанию','<th>$VALUE;</th>'),(17,'table-row','Строка таблицы','<tr>$DATA;</tr>'),(18,'header-row',NULL,'<tr>$DATA;</tr>'),(19,'total-row',NULL,'<tr class=\"total\">$DATA;</tr>'),(20,'column-right','','<td class=\"numeric\">$VALUE;</td>'),(21,'filter-display','','<span class=\"filter-display\">$NAME; : $VALUE;</span>'),(22,'clear-filter','','<button onclick=\"ClearFilter(\'$NAME;\')\">X</button>'),(23,'menuonline','','<div class=\"category\" onclick=\"Online();\">Онлайн</div>');
+/*!40000 ALTER TABLE `web_templates` ENABLE KEYS */;
+UNLOCK TABLES;
+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
+
+CREATE DEFINER=`admin`@`%` PROCEDURE `Process_Traffic`()
+BEGIN
+
+ insert into users(username,alias)
+ select username,username from (
+ select distinct username from tmp_traffic
+ left join users using(username)
+ where users.username is null) t;
+
+ insert into hosts(hostname,alias)
+ select client_host,client_host from (
+ select distinct client_host from tmp_traffic
+ left join hosts on hosts.hostname=tmp_traffic.client_host
+ where hosts.hostname is null) t;
+
+ insert into http_methods(method)
+ select distinct http_method from tmp_traffic
+ left join http_methods on http_methods.method=tmp_traffic.http_method
+ where http_methods.method is null;
+
+ insert into http_codes(http_code)
+ select distinct http_code from tmp_traffic
+ left join http_codes using(http_code)
+ where http_codes.http_code is null;
+
+ insert into mime_types(mime_type,alias)
+ select mime_type,mime_type from (
+ select distinct mime_type from tmp_traffic
+ left join mime_types using(mime_type)
+ where mime_types.mime_type is null) t;
+
+ insert into access_log(access_date,process_time,host_id,code_id,bytes,method_id,site,user_id,mime_id)
+ select t.access_date,t.process_time,t.host_id,t.code_id,t.bytes,t.method,t.server_host,t.user_id,t.mime_id
+ from
+ (
+ select from_unixtime(round(timestamp)) access_date,process_time,h.id host_id,c.id code_id,bytes,m.id method,t.server_host,u.id user_id,mt.id mime_id
+ from tmp_traffic t, users u,hosts h,http_methods m,http_codes c,mime_types mt
+ where
+ t.username=u.username and
+ t.client_host=h.hostname and
+ t.http_method=m.method and
+ t.http_code=c.http_code and
+ t.mime_type=mt.mime_type
+ ) t
+ left join access_log a
+ on t.access_date=a.access_date and t.host_id=a.host_id and t.code_id=a.code_id and t.bytes=a.bytes and t.method=a.method_id and t.server_host=a.site and t.user_id=a.user_id
+ where a.id is null;
+
+ delete from tmp_traffic;
+
+END;
+
+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
+
+-- Dump completed on 2019-12-13 15:39:51
--- /dev/null
+<?
+
+ require_once("config.php");
+
+ header("Access-Control-Allow-Orgin: *");
+ header("Access-Control-Allow-Methods: *");
+ header("Content-Type: application/json");
+
+ $requestUri = explode('/', trim($_SERVER['REQUEST_URI'],'/'));
+ $requestParams = $_REQUEST;
+
+ $method = $_SERVER['REQUEST_METHOD'];
+
+ function requestStatus($code) {
+ $status = array(
+ 200 => 'OK',
+ 404 => 'Not Found',
+ 405 => 'Method Not Allowed',
+ 500 => 'Internal Server Error',
+ );
+ return ($status[$code])?$status[$code]:$status[500];
+ }
+
+ function response($data, $status = 500) {
+ header("HTTP/1.1 " . $status . " " . requestStatus($status));
+ print json_encode($data);
+ }
+
+ function connect_db() {
+
+ global $db,$mysql_host,$mysql_port,$mysql_schema,$mysql_user,$mysql_pwd;
+
+ if (! ($db = new PDO("mysql:host=$mysql_host;port=$mysql_port;dbname=$mysql_schema",$mysql_user,$mysql_pwd,array( PDO::ATTR_PERSISTENT => false)))) {
+ die($err);
+ }
+ $db -> exec('SET CHARACTER SET utf8');
+
+ return $db;
+
+ }
+
+ function exec_query($sql) {
+
+ global $db;
+
+ $q = $db -> prepare( $sql );
+ $q -> bindParam(':s',$auth_token,PDO::PARAM_INT);
+ $q -> execute();
+
+ return $q -> fetchall(PDO::FETCH_ASSOC);
+
+ }
+
+ $api = $requestParams["method"];
+
+ $data = Array();
+ $code = 200;
+ switch ($api) {
+ case "get-base-config":
+
+ $data["site-header"] = $site_header;
+
+ connect_db();
+
+ $cats = exec_query("select mnemo,name,description from rep_cat");
+
+ foreach ($cats as $cat => $value) {
+
+ $cmnemo = $cats[$cat]["mnemo"];
+ $reps = exec_query("select mnemo,name,description,graph_x,graph_y,graph_series from rep where cat_mnemo='$cmnemo'");
+ $cats[$cat]["reps"] = $reps;
+
+ }
+
+ $data["cats"] = $cats;
+ $data["templates"] = exec_query("select mnemo,body from web_templates");
+ $data["columns"] = exec_query("select * from column_names");
+ $users = exec_query("select id,username as name,alias from users");
+ $hosts = exec_query("select id,hostname as name,alias from hosts");
+ $data["dictionaries"] = Array( "user_id" => $users, "host_id" => $hosts);
+
+ break;
+
+ case "report":
+
+ $mnemo = $requestParams["mnemo"];
+
+ connect_db();
+
+ $rec = exec_query("select has_total,query from rep where mnemo='$mnemo'")[0];
+ $sql = $rec["query"];
+ $data["has_total"] = $rec["has_total"];
+
+ $filter_str = "";
+ $filter = Array();
+
+ foreach ($requestParams as $name => $value) {
+
+ if (($name == "mnemo") || ($name == "method")) { continue; }
+ if ($name == "date_from") { $filter_str = $filter_str." and access_date>='$value'"; }
+ elseif ($name == "date_to") { $filter_str = $filter_str." and access_date<date_add('$value',interval 1 day)"; }
+ else { $filter_str = $filter_str." and $name = '$value'"; $filter[$name] = $value; }
+ }
+
+ if ($filter_str) {
+ $sql = preg_replace('/\$FILTER;/',$filter_str,$sql);
+ }
+
+ if ($filter) {
+ $data["filter"] = $filter;
+ }
+ $result = exec_query($sql);
+ if ($result) {
+ $data["dictionary"] = array_keys($result[0]);
+ $data["data"] = $result;
+ } else {
+ $data["sql"] = $sql;
+ }
+
+ break;
+
+ case "online":
+
+ if($squid_passwd != "") { $pwd.="cachemgr:$cachemgr_passwd@"; } else { $pwd = ""; }
+ $url = "http://".$pwd.$squid_host.":".$squid_port."/squid-internal-mgr/active_requests";
+
+ $ch = curl_init($url);
+
+ $options = array(
+ CURLOPT_RETURNTRANSFER => true, // return web page
+ CURLOPT_HEADER => false, // don't return headers
+ CURLOPT_FOLLOWLOCATION => false, // follow redirects
+ CURLOPT_MAXREDIRS => 0, // stop after 10 redirects
+ CURLOPT_ENCODING => "", // handle compressed
+ CURLOPT_USERAGENT => "web", // name of client
+ CURLOPT_AUTOREFERER => true, // set referrer on redirect
+ CURLOPT_CONNECTTIMEOUT => 3, // time-out on connect
+ CURLOPT_TIMEOUT => 5, // time-out on response
+ );
+
+ curl_setopt_array($ch, $options);
+
+ $reply = curl_exec($ch);
+ $retcode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
+
+ // 400 means not found, 200 means found.
+ curl_close($ch);
+
+ if($retcode == 200) {
+
+ $data["active"] = Array();
+
+ if(preg_match("/HTTP/1.0 200 OK/",$ptmp)){
+ $code = 500;
+ $data=Array("error" => "No connection to Squid");
+ } else {
+ preg_match_all("/username(.+)/",$reply,$user);
+ preg_match_all("/(peer|remote):(.+)/",$reply,$remote);
+ preg_match_all("/uri(.+)/",$reply,$uri);
+ preg_match_all("/out\.size(.+)/",$reply,$size);
+ preg_match_all("/\((.+)seconds/",$reply,$sec);
+ $sess = Array();
+ for ($i=0; $i< count($user[1]); $i++) {
+ $ip=trim($remote[2][$i]);
+ $reversedParts = explode(':', strrev($ip), 2);
+ $ip = strrev($reversedParts[1]);
+ $port = strrev($reversedParts[0]);
+ if (preg_match('/\[(.*)\]/',$ip,$matches)) {
+ $ip = $matches[1];
+ }
+ $host = gethostbyaddr($ip);
+ if (!$host) { $host = $ip; }
+ $username=trim($user[1][$i]);
+ $site=trim($uri[1][$i]);
+ $datasize=trim($size[1][$i]);
+ $seconds = trim($sec[1][$i]);
+ $record = Array("_user" => $username, "_ip" => $ip, "_port"=> $port, "host" => $host, "uri" => $site, "bytes" => $datasize, "seconds" => $seconds);
+ $sess[] = $record;
+ }
+ $data["data"] = $sess;
+ $data["dictionary"] = Array("_user","_ip","_port","host","uri","bytes","seconds");
+ }
+
+ }
+
+ break;
+
+ default:
+ $data["error"] = "Method not found";
+ $data["method"] = $api;
+ $code = 404;
+ }
+
+ response($data,$code);
+
+?>
\ No newline at end of file
--- /dev/null
+<!DOCTYPE html>
+<html lang="ru">
+<head>
+ <meta charset="utf-8">
+ <title>Squid reports</title>
+ <meta content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=0" name="viewport"/>
+ <link rel="stylesheet" href="squid.css">
+ <link rel="shortcut icon" href="squid.png">
+ <script src="squid.js"></script>
+ <script src="../js/Chart.bundle.min.js"></script>
+</head>
+<body onload="OnLoad();">
+<header>
+<div class="fill">
+<div class="container">
+<a id="logo" href="http://www.squid-cache.org/"><img src="squid.png" alt="Squid"></a>
+<a id="brand" href="/squid/"></a>
+</div>
+</div>
+</header>
+<div class="main">
+<div id="menu">
+</div>
+<div class="main-right">
+<div id="report-name"></div>
+<div id="report-dates">
+Отчет с: <input id="date-from" type="date" onchange="UpdateDates();">
+по: <input id="date-to" type="date" onchange="UpdateDates();">
+<span id="filter">
+Фильтр:
+</span>
+<button id="report-button" type="button" disabled onclick="Rerun();">Выполнить</button>
+</div>
+<div id="page-selector">
+<div class="selector" onclick="Display('report-body');">Статистика</div><div class="selector" onclick="Display('report-graph')">График</div>
+</div>
+<div class="report-block" id="report-body">Выберите отчет из списка слева...</div>
+<div class="report-block" id="report-graph"><canvas id="canvas"></canvas></div>
+</div>
+</div>
+</body>
+</html>
--- /dev/null
+body {
+ font-size:.8rem;
+ background-color:#eee
+}
+
+html, body {
+ height:100%;
+ margin:0;
+ padding:0
+}
+
+header, .main {
+ position:absolute;
+ width:100%
+}
+
+header {
+ position:fixed;
+ z-index:2000;
+ float:left;
+ height:5rem;
+ transition:box-shadow .2s;
+ box-shadow:0 2px 5px rgba(0,0,0,.26);
+ color:white;
+ background:deepskyblue
+}
+
+footer {
+ font-size:.8rem;
+ overflow:hidden;
+ text-align:right;
+ white-space:nowrap;
+ color:#aaa;
+ text-shadow:0 0 2px #bbb;
+ padding:1rem
+}
+
+footer > a {
+ text-decoration:none;
+ color:#aaa
+}
+
+.main {
+ position:relative;
+ top:5rem;
+ bottom:0;
+ overflow-y:auto;
+ height:calc(100% - 5rem)
+}
+
+#menu {
+ position:fixed;
+ top:5rem;
+ float:left;
+ overflow-x:auto;
+ width:calc(0% + 15rem);
+ height:calc(100% - 4rem);
+ background-color:white
+}
+
+.main-right {
+ float:right;
+ width:calc(100% - 15rem);
+ height:calc(100% - 1rem);
+ background-color:#eee
+}
+
+header > .fill > .container {
+ margin-top:.5rem;
+ -webkit-user-select:none;
+ -moz-user-select:none;
+ -ms-user-select:none;
+ user-select:none;
+ padding:.5rem 1rem 0
+}
+
+header > .fill > .container > #logo {
+ margin:0 3.5rem 0 1.5rem
+}
+
+header > .fill > .container > #logo > img {
+ width: 3rem;
+ margin-top:-.1rem
+}
+
+header > .fill > .container > #brand {
+ font-size:1.4rem;
+ position:absolute;
+ cursor:default;
+ vertical-align:text-bottom;
+ text-decoration:none;
+ color:white;
+ padding-top: 0.5rem;
+}
+
+.nav {
+ font-size: 1.15rem;
+ margin-top: 0.5rem;
+ padding: 0;
+}
+
+.slide {
+ list-style-type: none;
+ padding: 0;
+ user-select: none;
+ display: none;
+}
+
+.category {
+ padding: 0.4rem 1rem;
+ cursor: pointer;
+}
+
+.slide-menu {
+ padding: 0.4rem 2rem;
+ list-style-type: none;
+ cursor: pointer;
+ user-select: none;
+}
+
+.menu-item {
+ padding: 0.2rem;
+ list-style-type: none;
+ cursor: pointer;
+ user-select: none;
+}
+
+.menu-item:hover, .category:hover {
+ background-color: lightgray;
+}
+
+#report-name {
+ padding: 1rem;
+ font-size: 2rem;
+}
+
+.selector {
+ display: inline-block;
+ cursor: pointer;
+ font-size: 1rem;
+ margin: 0 0.5rem 0 0.5rem;
+ padding: 0.5rem 2rem 0.5rem 2rem;
+}
+
+.selector:hover {
+ background-color: lightgray;
+}
+
+.report-block, #report-dates, #filter, #page-selector {
+ margin: 0 1rem 0 1rem;
+ background-color: white;
+}
+
+.report-block {
+ min-height: 50%;
+ padding: 0.2rem 1rem 1rem 1rem;
+}
+
+#report-dates {
+ font-size: 1rem;
+ padding: 0.5rem 1rem 0.5rem 1rem;
+ display: none;
+}
+
+#report-button {
+ float: right;
+}
+
+#filter {
+ display: none;
+ margin: 0;
+ padding: 0 2rem 0 2rem;
+}
+
+.fllter-button {
+ padding-right: 2rem;
+}
+
+#report {
+ width: 100%;
+}
+
+#report th {
+ font-size: 1.4rem
+}
+
+#report tr {
+ font-size: 1rem
+}
+
+#report tr:nth-child(2n+1) {
+ background-color: lightgray;
+}
+
+#report tr:nth-child(2n) {
+ background-color: white;
+}
+
+#report-graph {
+ display: none;
+ height: calc(100% - 10rem);
+}
+
+.numeric, .clickable-numeric {
+ text-align: right;
+}
+
+.total td {
+ font-size: 1.2rem;
+ padding: 0.4rem 0 0.4rem 2rem;
+}
+
+.clickable, .clickable-numeric {
+ cursor: pointer;
+ text-decoration: underline;
+}
--- /dev/null
+urlbase="./api.php"
+
+refresh = 500
+online_history = 60
+
+graph_colors = [
+"salmon", "lightcoral", "crimson", "red", "darkred", "orangered",
+"gold", "orange", "yellow", "darkkhaki", "lime", "green", "greenyellow",
+"springgreen", "olive", "cyan", "turquoise", "teal", "lightskyblue",
+"dodgerblue", "royalblue", "blue", "navy", "violet", "fuchsia",
+"darkviolet", "purple", "deeppink", "gray", "darkslategray", "sandybrown",
+"goldenrod", "chocolate", "saddlebrown", "maroon", "rosybrown", "sienna",
+"brown", "dimgray", "mediumvioletred", "indigo", "orchid", "mediumpurple",
+"steelblue", "powderblue", "lightseagreen", "cadetblue", "aquamarine",
+"olivedrab", "chartreuse", "darkolivegreen", "tomato", "firebrick"
+]
+
+currentState=""
+
+templates = {}
+columns = {}
+
+cats = {}
+reps = {}
+
+data = []
+
+current_filter = {}
+
+dictionaries = {}
+
+current_rep = ''
+
+online_traffic = null
+online_connections = null
+online_hosts = []
+
+timer = null
+
+assigned_colors = []
+
+var d = new Date();
+var curr_day = d.getDate();
+var curr_month = d.getMonth() + 1;
+var curr_year = d.getFullYear();
+today = curr_year + "-" + curr_month + "-" + curr_day;
+
+date_from = today
+date_to = today
+
+function UrlParams(params) {
+
+ var out = [];
+
+ for (var key in params) {
+ if (params.hasOwnProperty(key)) {
+ out.push(key + '=' + encodeURIComponent(params[key]));
+ }
+ }
+
+ return out.join('&');
+
+}
+
+function Macro(template, values) {
+ return templates[template].replace(/\$(\w+)\;/g,function (s,name) {
+ return values[name]
+ })
+}
+
+function ColumnMacro(column, values, macro = "column"){
+ var columnrec = columns[column]
+ var templatestr
+ if (columnrec) {
+ if (columnrec.template_name) {
+ macro = columnrec.template_name
+ }
+ templatestr = columnrec.template
+ }
+ if (templatestr) {
+ return templatestr.replace(/\$(\w+)\;/g,function (s,name) {
+ return values[name]
+ })
+ } else {
+ return Macro(macro,{VALUE:values[column]})
+ }
+}
+
+function HeaderMacro(column,macro = "header-column"){
+ var columnrec = columns[column]
+ if (columnrec) {
+ var alias = columns[column].alias;
+ } else {
+ var alias = ''
+ }
+ if (!(alias)) { alias = column }
+ return Macro(macro,{VALUE:alias})
+}
+
+function GetApi(onfinish,method,params) {
+
+ var req = new XMLHttpRequest();
+
+ req.onreadystatechange = function () {
+ if (this.readyState != 4) return;
+ if (this.status != 200) {
+ setTimeout(OnLoad,30000);
+ return;
+ }
+ res = JSON.parse(this.responseText);
+ onfinish(res);
+ };
+
+ var url = urlbase+"?method="+method;
+
+ if (params) {
+
+ url = url + '&' + UrlParams(params)
+
+ }
+
+ req.open("GET", url, true);
+ req.withCredentials = true;
+ req.send();
+
+}
+
+function UpdatePageProps(props) {
+
+ var logo = document.getElementById("brand");
+ logo.innerText = props["site-header"];
+
+ cats = props["cats"]
+
+ for (var i in res["columns"]) {
+ columns[res["columns"][i]["name"]] = {alias:res["columns"][i]["alias"],template:res["columns"][i]["template"],template_name:res["columns"][i]["template_name"]}
+ }
+
+ for (var i in res["templates"]) {
+ var mnemo = res["templates"][i]["mnemo"]
+ var body = res["templates"][i]["body"]
+ templates[mnemo] = body
+ }
+
+ dictionaries = res["dictionaries"]
+
+ menuInnerHTML = ""
+ for (var cat in cats) {
+ category = cats[cat]
+ innerHTML = ""
+ for (rep in category["reps"]) {
+ report = category["reps"][rep]
+ reps[report["mnemo"]] = report
+ reptext = Macro("menuitem",{ MNEMO:report["mnemo"], NAME:report["name"], DESCR:report["description"] })
+ innerHTML = innerHTML + reptext
+ }
+ grouptxt = Macro("menugroup",{ MNEMO:category["mnemo"], NAME:category["name"], DESCR:category["description"], MENUITEM: innerHTML})
+ menuInnerHTML = menuInnerHTML + grouptxt
+ }
+ menuInnerHTML = Macro("menuonline",{}) + menuInnerHTML
+ menuHTML = Macro("menu", { MENUGROUP: menuInnerHTML} )
+ var Left = document.getElementById("menu");
+ Left.innerHTML = menuHTML
+
+ Online();
+
+}
+
+function SetDates() {
+ var inp = document.getElementById("date-from")
+ inp.value = date_from
+ inp.max = date_to
+
+ var inp = document.getElementById("date-to")
+ inp.value = date_to
+ inp.min = date_from
+
+ var inp = document.getElementById("report-button")
+ if (current_rep) {
+ inp.disabled = false
+ } else {
+ inp.disabled = true
+ }
+}
+
+function OnLoad() {
+
+ GetApi(UpdatePageProps,"get-base-config",null)
+ SetDates();
+
+}
+
+function ShowHide(id) {
+ var content = document.getElementById(id);
+ if (content.style.display === "block") {
+ content.style.display = "none";
+ } else {
+ content.style.display = "block";
+ }
+}
+
+function MergeTR(tr,th, macro = "column") {
+ var str = ""
+ for (i in th) {
+ if (!(th[i].startsWith('_'))) {
+ str = str + ColumnMacro(th[i],tr,macro)
+ }
+ }
+ return str
+}
+
+function MergeTH(th, macro = "header-column") {
+ var str = ""
+ for (i in th) {
+ if (!(th[i].startsWith('_'))) {
+ str = str + HeaderMacro(th[i])
+ }
+ }
+ return str
+}
+
+function ProduceRep(res) {
+
+ current_filter = res["filter"]
+ if (!current_filter) { current_filter = {} }
+ dictionary = res["dictionary"]
+
+ header_template = res["header"]
+ has_total = res["has_total"]
+
+ innerHTML = ""
+ data = res["data"]
+ ii=0
+
+ if (has_total == "1") {
+ total = data.pop()
+ }
+
+ for (i in data) {
+ row_data = data[i]
+ table_row = Macro("table-row",{DATA:MergeTR(row_data,dictionary)})
+ innerHTML = innerHTML + table_row
+ }
+
+ headerHTML = Macro("header-row",{DATA:MergeTH(dictionary)})
+
+ if (has_total == "1") {
+ totalHTML = Macro("total-row",{DATA:MergeTR(total,dictionary)})
+ reportHTML = Macro("report-table-total",{HEADER:headerHTML,LINES:innerHTML,TOTAL:totalHTML})
+ } else {
+ reportHTML = Macro("report-table",{HEADER:headerHTML,LINES:innerHTML})
+ }
+ var body = document.getElementById("report-body")
+ body.innerHTML = reportHTML;
+
+ if (reps[current_rep].graph_x) {
+ DisplayGraph(true)
+ var config = PrepareGraphDataset(data,reps[current_rep].graph_x,reps[current_rep].graph_y,reps[current_rep].graph_series)
+ config.options.responsive = true
+
+ config.options.scales = {
+ xAxes: [{
+ scaleLabel: {
+ display: true,
+ }
+ }],
+ yAxes: [{
+ stacked: true,
+ scaleLabel: {
+ display: true,
+ },
+ ticks: {
+ suggestedMin: 0, // minimum will be 0, unless there is a lower value.
+ }
+ }]
+ }
+ DrawGraph(config)
+ } else {
+ DisplayGraph(false)
+ }
+
+}
+
+function DrawGraph(config) {
+ var ctx = document.getElementById('canvas').getContext('2d')
+ var div = document.getElementById("report-graph");
+ canvas.width = div.style.width;
+ canvas.height = div.style.height;
+ window.Graph = new Chart(ctx, config);
+}
+
+function AssignColor(key) {
+ if (assigned_colors[key]) {
+ return assigned_colors[key]
+ }
+ var rand_color
+ if (graph_colors.length) {
+ var idx = Math.floor(Math.random() * graph_colors.length)
+ rand_color = graph_colors[idx]
+ graph_colors.splice(idx,1)
+ } else {
+ rand_color = "darkgray"
+ }
+ assigned_colors[key] = rand_color
+ return rand_color
+}
+
+function PrepareGraphDataset(data,graph_x,graph_y,graph_series) {
+
+ var xvals = []
+ var series = []
+
+ var values = []
+
+ for (i in data) {
+ rec = data[i]
+ for (key in rec) {
+ if (key == graph_x) {
+ if (!xvals.includes(Number(rec[key]))) {
+ xvals.push(Number(rec[key]))
+ }
+ }
+ if (key == graph_series) {
+ if (!series.includes(rec[key])) {
+ series.push(rec[key])
+ }
+ }
+ }
+ }
+
+ xvals.sort(function(a,b) { return a-b; })
+ series.sort()
+
+ for (var i in series) {
+ values[i] = {}
+ values[i].data = []
+ values[i].label = series[i]
+ values[i].fill = true
+ values[i].borderColor = AssignColor(values[i].label)
+ values[i].backgroundColor = AssignColor(values[i].label)
+ for (var j in xvals) {
+ values[i].data[j] = 0
+ }
+ }
+
+ for (var k in data) {
+ rec = data[k]
+ xval = null
+ yval = null
+ dataset = null
+ for (key in rec) {
+ if (key == graph_x) {
+ xval = Number(rec[key])
+ }
+ if (key == graph_y) {
+ yval = Number(rec[key])
+ }
+ if (key == graph_series) {
+ dataset = rec[key]
+ }
+ }
+ var j = xvals.indexOf(xval)
+ var i = series.indexOf(dataset)
+ values[i].data[j] = yval
+ }
+
+ return {
+ type: 'line',
+ data: {
+ labels: xvals,
+ datasets: values,
+ },
+ options: {
+ }
+ }
+
+
+}
+
+function AliasByName(dict,name) {
+ for (i in dict) {
+ if (dict[i].name == name) { return dict[i].alias }
+ }
+ return name
+}
+
+function AddTraffic(label,b) {
+ var rec
+ for (i in online_traffic) {
+ if (online_traffic[i].label == label) {
+ rec = online_traffic[i]
+ break
+ }
+ }
+ if (!rec) {
+ rec = { label: label, borderColor: AssignColor(label), backgroundColor: AssignColor(label), data: new Array(online_history).fill(0) }
+ online_traffic.push(rec)
+ }
+ rec.data[rec.data.length-1] += b
+}
+
+function ProduceOnline(res) {
+
+ if (!online_traffic) {
+ online_traffic = []
+ config = {
+ type: 'line',
+ data: {
+ labels: new Array(online_history).fill('*'),
+ datasets: online_traffic,
+ },
+ options: {
+ animation: {
+ duration: 0
+ },
+ responsive: true,
+ scales: {
+ xAxes: [{
+ scaleLabel: {
+ display: true,
+ }
+ }],
+ yAxes: [{
+ stacked: true,
+ ticks: {
+ suggestedMin: 0, // minimum will be 0, unless there is a lower value.
+ },
+ scaleLabel: {
+ display: true,
+ }
+ }]
+ }
+ }
+ }
+ DrawGraph(config)
+ }
+
+ for (i = online_traffic.length-1; i>=0; i--) {
+ online_traffic[i].data.shift()
+ online_traffic[i].data.push(0)
+ if (Math.max.apply(null,online_traffic[i].data) == 0) {
+ online_traffic.splice(i,1)
+ }
+ }
+
+ dictionary = res["dictionary"]
+
+ header_template = res["header"]
+
+ innerHTML = ""
+ data = res["data"]
+ ii=0
+
+ var new_online_connections = []
+
+ dictionary.unshift("useralias")
+ for (i in data) {
+ row_data = data[i]
+ user = row_data["_user"]
+ username = AliasByName(dictionaries['user_id'],user)
+ row_data["useralias"] = username
+
+ host = row_data["host"]
+ hostname = AliasByName(dictionaries['host_id'],host)
+ row_data["host"] = hostname
+
+ table_row = Macro("table-row",{DATA:MergeTR(row_data,dictionary)})
+ innerHTML = innerHTML + table_row
+
+ var bytes = Number(row_data['bytes'])
+
+ var idx = row_data["_ip"]+':'+row_data["_port"]
+ var last_bytes
+
+ if (online_connections) {
+ last_bytes = online_connections[idx]
+ if (!last_bytes) {
+ last_bytes = 0
+ }
+ } else {
+ last_bytes = bytes
+ }
+
+ new_online_connections[idx] = bytes
+
+ AddTraffic(username,bytes-last_bytes)
+ }
+
+ online_connections = new_online_connections
+
+ headerHTML = Macro("header-row",{DATA:MergeTH(dictionary)})
+
+ reportHTML = Macro("report-table",{HEADER:headerHTML,LINES:innerHTML})
+
+ var body = document.getElementById("report-body")
+ body.innerHTML = reportHTML;
+
+ timer = setTimeout(Online,refresh)
+
+ window.Graph.update()
+
+}
+
+function CancelRefresh() {
+ if (timer) {
+ clearTimeout(timer)
+ timer = null
+ }
+}
+
+function ShowRep(id) {
+ CancelRefresh()
+ current_filter = {}
+ var header = document.getElementById("report-name")
+ header.innerText = reps[id]["name"]
+ var body = document.getElementById("report-body")
+ body.innerText = "Отчет загружается..."
+ current_rep = id
+ UpdateDates()
+ Rerun()
+}
+
+function ShowFilteredRep(id,filter) {
+ CancelRefresh()
+ for (i in filter) {
+ current_filter[i] = filter[i]
+ }
+ var header = document.getElementById("report-name")
+ header.innerText = reps[id]["name"]
+ var body = document.getElementById("report-body")
+ body.innerText = "Отчет загружается..."
+ current_rep = id
+ UpdateDates()
+ Rerun()
+}
+
+function FilterSelect(name,dict,value) {
+ var str = '<select onchange="SetFilter(\''+name+'\',this);">'
+ for (key in dict) {
+ if (dict[key].id==value) {
+ str = str + '<option selected value="'+dict[key].id+'">'+dict[key].name+"</option>"
+ } else {
+ str = str + '<option value="'+dict[key].id+'">'+dict[key].name+"</option>"
+ }
+ }
+ str = str + "</select>" + Macro("clear-filter",{NAME:name})
+ return str
+}
+
+function Filter() {
+ var str = ""
+ for (key in current_filter) {
+ var dict = dictionaries[key]
+ if(dict) {
+ str = str + FilterSelect(key,dict,current_filter[key])
+ } else {
+ str = str + Macro("filter-display",{NAME:HeaderMacro(key),VALUE:current_filter[key]}) + Macro("clear-filter",{NAME:key})
+ }
+ }
+ return str
+}
+
+function RefreshFilterPane() {
+ var filter = document.getElementById("report-dates")
+ if (current_rep) {
+ filter.style.display = "block";
+ } else {
+ filter.style.display = "none";
+ }
+ var filter = document.getElementById("filter")
+ if (Object.keys(current_filter).length === 0) {
+ filter.style.display = "none";
+ filter.innerHTML = ''
+ } else {
+ filter.style.display = "inline-block";
+ filter.innerHTML = Filter()
+ }
+}
+
+function Rerun() {
+ online_traffic = null
+ var parameters = JSON.parse(JSON.stringify(current_filter))
+ parameters["mnemo"] = current_rep
+ parameters["date_from"] = date_from
+ parameters["date_to"] = date_to
+ GetApi(ProduceRep,"report",parameters);
+ var filter = document.getElementById("filter")
+ RefreshFilterPane();
+}
+
+function Online() {
+ timer = null
+ RefreshFilterPane()
+ var header = document.getElementById("report-name")
+ header.innerText = "Активные соединения"
+ GetApi(ProduceOnline,"online",{});
+ DisplayGraph(true)
+}
+
+function UpdateDates() {
+ var inp = document.getElementById("date-from")
+ date_from = inp.value
+ var inp = document.getElementById("date-to")
+ date_to = inp.value
+
+ SetDates();
+
+}
+
+function SetFilter(name,select) {
+ current_filter[name] = select.value;
+}
+
+function ClearFilter(name) {
+ delete current_filter[name];
+ RefreshFilterPane();
+}
+
+function DisplayGraph(on) {
+ var selector = document.getElementById("page-selector")
+ if (on) {
+ selector.style.display="block"
+ } else {
+ selector.style.display="none"
+ Display('report-body')
+ }
+}
+
+function Display(id) {
+ var elements = document.getElementsByClassName('report-block');
+ for (i=0; i<elements.length; i++) {
+ if (elements[i].id == id) {
+ elements[i].style.display = "block"
+ } else {
+ elements[i].style.display = "none"
+ }
+ }
+}
\ No newline at end of file
projects / squid-reports.git / commitdiff
