--
-- Host: localhost    Database: squid
-- ------------------------------------------------------


--
-- Table structure for table `access_log`
--

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;

--
-- Table structure for table `column_names`
--

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;

--
-- Dumping data for table `column_names`
--

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>','');

--
-- Table structure for table `hosts`
--

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;

--
-- Table structure for table `http_codes`
--

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;

--
-- Table structure for table `http_methods`
--

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;

--
-- Table structure for table `mime_types`
--

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;

--
-- Table structure for table `rep`
--

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;

--
-- Dumping data for table `rep`
--

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');

--
-- Table structure for table `rep_cat`
--

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;

--
-- Dumping data for table `rep_cat`
--

INSERT INTO `rep_cat` VALUES (1,'user','Пользователи','Отчеты по авторизованным пользователям'),(2,'host','Хосты','Отчеты по клиентским подключениям'),(3,'site','Сайты','Отчеты по посещаемым сайтам');

--
-- Table structure for table `tmp_traffic`
--

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;

--
-- Table structure for table `users`
--

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;

--
-- Table structure for table `web_templates`
--

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;

--
-- Dumping data for table `web_templates`
--

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>');

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;


