SET ECHO ON;
SET SQL DIALECT 3;
SET NAMES WIN1251;

CONNECT 'localhost:billing' USER 'sysdba' PASSWORD 'masterkey';

SET TERM ^ ;

CREATE PROCEDURE FULL_TRAFFIC (PERIOD_BEGIN DATE,
PERIOD_END DATE)
RETURNS (INCOMING_ALLOWED BIGINT,
INCOMING_REJECTED BIGINT,
OUTGOING BIGINT)
AS 

begin
  select sum(bytes) from transfer_log
    where outgoing<>'ppp0' and incoming='ppp0' and prefix='allow'
      and log_date>=:period_begin and log_date<=:period_end
    into :incoming_allowed;
  select sum(bytes) from transfer_log
    where outgoing<>'ppp0' and incoming='ppp0' and prefix='drop'
      and log_date>=:period_begin and log_date<=:period_end
    into :incoming_rejected;
  select sum(bytes) from transfer_log
    where outgoing='ppp0' and incoming<>'ppp0'
      and log_date>=:period_begin and log_date<=:period_end
    into :outgoing;
  suspend;
end ^

CREATE PROCEDURE NAT_TRAFFIC_BY_HOSTS (PERIOD_BEGIN DATE,
PERIOD_END DATE)
RETURNS (HOST VARCHAR(15) CHARACTER SET NONE,
INCOMING_ALLOWED BIGINT,
INCOMING_REJECTED BIGINT,
OUTGOING BIGINT)
AS 

begin
  for select destination, sum(bytes) from transfer_log
    where outgoing='eth0' and incoming='ppp0' and prefix='allow'
    and log_date>=:period_begin and log_date<=:period_end
    group by destination
    into :host, :incoming_allowed
  do
  begin
    select sum(bytes) from transfer_log
      where outgoing='eth0' and incoming='ppp0' and prefix='drop'
      and log_date>=:period_begin and log_date<=:period_end and destination=:host
      into :incoming_rejected;
    select sum(bytes) from transfer_log
      where outgoing='ppp0' and incoming='eth0'
      and log_date>=:period_begin and log_date<=:period_end and source=:host
    into :outgoing;
    suspend;
  end
end ^

CREATE PROCEDURE PROXY_TRAFFIC_BY_HOSTS (PERIOD_BEGIN DATE,
PERIOD_END DATE)
RETURNS (HOST VARCHAR(15) CHARACTER SET NONE,
TOTAL BIGINT,
CACHED BIGINT)
AS 

begin
  for select client_address, sum(total_bytes), sum(cache_bytes) from proxy_log
    where log_date>=:period_begin and log_date<=:period_end
    group by client_address
    into :host, :total, :cached
  do
  begin
    suspend;
  end
end ^

CREATE PROCEDURE REJECTED_INNER_TRAFFIC (PERIOD_BEGIN DATE,
PERIOD_END DATE)
RETURNS (PROTOCOL SMALLINT,
SOURCE VARCHAR(15) CHARACTER SET NONE,
SOURCE_PORT INTEGER,
DESTINATION VARCHAR(15) CHARACTER SET NONE,
DESTINATION_PORT INTEGER,
BYTES BIGINT)
AS 

begin
  for select protocol, source, source_port, destination, destination_port,
    sum(bytes) from transfer_log
    where outgoing='eth0' and incoming='ppp0' and prefix='drop'
    group by protocol, source, source_port, destination, destination_port
    order by 6 desc
    into :protocol, :source, :source_port, :destination, :destination_port, :bytes
  do
  begin
    suspend;
  end
end ^

CREATE PROCEDURE REJECTED_OUTER_TRAFFIC (PERIOD_BEGIN DATE,
PERIOD_END DATE)
RETURNS (PROTOCOL SMALLINT,
SOURCE VARCHAR(15) CHARACTER SET NONE,
SOURCE_PORT INTEGER,
DESTINATION VARCHAR(15) CHARACTER SET NONE,
DESTINATION_PORT INTEGER,
BYTES BIGINT)
AS 

begin
  for select first 25 protocol, source, source_port, destination, destination_port,
    sum(bytes) from transfer_log
    where outgoing<>'ppp0' and incoming='ppp0' and prefix='drop'
    group by protocol, source, source_port, destination, destination_port
    order by 6 desc
    into :protocol, :source, :source_port, :destination, :destination_port, :bytes
  do
  begin
    suspend;
  end
end ^

SET TERM ; ^

COMMIT WORK ;

GRANT SELECT ON PROXY_LOG TO USER OPERATOR;
GRANT SELECT ON TRANSFER_LOG TO USER OPERATOR;
GRANT EXECUTE ON PROCEDURE FULL_TRAFFIC TO USER OPERATOR;
GRANT EXECUTE ON PROCEDURE NAT_TRAFFIC_BY_HOSTS TO USER OPERATOR;
GRANT EXECUTE ON PROCEDURE PROXY_TRAFFIC_BY_HOSTS TO USER OPERATOR;
GRANT EXECUTE ON PROCEDURE REJECTED_INNER_TRAFFIC TO USER OPERATOR;
GRANT EXECUTE ON PROCEDURE REJECTED_OUTER_TRAFFIC TO USER OPERATOR;

COMMIT WORK ;

CREATE TABLE TRANSLATION (LANGUAGE VARCHAR(10),
        OBJECT_TYPE VARCHAR(25),
        SYSTEM_NAME VARCHAR(256),
        TRANSLATED_NAME VARCHAR(256) CHARACTER SET WIN1251);

INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('RU', 'PROCEDURE', 'FULL_TRAFFIC', 'Общее потребление трафика');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('RU', 'COLUMN', 'INCOMING_ALLOWED', 'Входящий разрешенный');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('RU', 'COLUMN', 'INCOMING_REJECTED', 'Входящий запрещенный');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('RU', 'COLUMN', 'OUTGOING', 'Исходящий');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('EN', 'PROCEDURE', 'FULL_TRAFFIC', 'General traffic consumption');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('RU', 'COLUMN', 'SYSTEM_NAME', 'Системное имя');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('EN', 'COLUMN', 'INCOMING_REJECTED', 'Incoming rejected');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('EN', 'COLUMN', 'OUTGOING', 'Outgoing');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('RU', 'COLUMN', 'TRANSLATED_NAME', 'Описание');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('EN', 'COLUMN', 'SYSTEM_NAME', 'System name');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('EN', 'COLUMN', 'TRANSLATED_NAME', 'Description');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('EN', 'COLUMN', 'INCOMING_ALLOWED', 'Incomong allowed');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('EN', 'COLUMN', 'HOST', 'Host');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('RU', 'PROCEDURE', 'NAT_TRAFFIC_BY_HOSTS', 'Потребление NAT-трафика по хостам');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('EN', 'PROCEDURE', 'NAT_TRAFFIC_BY_HOSTS', 'NAT traffic consumption by hosts');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('RU', 'COLUMN', 'HOST', 'Хост');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('EN', 'PROCEDURE', 'PROXY_TRAFFIC_BY_HOSTS', 'Proxy traffic consumption by hosts');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('RU', 'PROCEDURE', 'PROXY_TRAFFIC_BY_HOSTS', 'Потребление proxy-трафика по хостам');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('EN', 'COLUMN', 'TOTAL', 'Total');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('EN', 'COLUMN', 'CACHED', 'Cached');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('RU', 'COLUMN', 'TOTAL', 'Общий');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('RU', 'COLUMN', 'CACHED', 'Из кэша');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('EN', 'PROCEDURE', 'REJECTED_OUTER_TRAFFIC', 'Rejected outer traffic');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('RU', 'PROCEDURE', 'REJECTED_OUTER_TRAFFIC', 'Запрещенный внешний трафик');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('EN', 'COLUMN', 'PROTOCOL', 'Protocol');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('EN', 'COLUMN', 'SOURCE', 'Source');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('EN', 'COLUMN', 'SOURCE_PORT', 'Source port');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('EN', 'COLUMN', 'DESTINATION', 'Destination');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('EN', 'COLUMN', 'DESTINATION_PORT', 'Destination port');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('EN', 'COLUMN', 'BYTES', 'Bytes');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('RU', 'COLUMN', 'PROTOCOL', 'Протокол');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('RU', 'COLUMN', 'SOURCE', 'Источник');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('RU', 'COLUMN', 'SOURCE_PORT', 'Порт источника');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('RU', 'COLUMN', 'DESTINATION', 'Назначение');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('RU', 'COLUMN', 'DESTINATION_PORT', 'Порт назначения');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('RU', 'COLUMN', 'BYTES', 'Количество байт');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('EN', 'PROCEDURE', 'REJECTED_INNER_TRAFFIC', 'Rejected inner traffic');
INSERT INTO TRANSLATION (LANGUAGE, OBJECT_TYPE, SYSTEM_NAME, TRANSLATED_NAME) VALUES ('RU', 'PROCEDURE', 'REJECTED_INNER_TRAFFIC', 'Запрещенный внутренний трафик');

COMMIT WORK;

GRANT SELECT ON TRANSLATION TO USER OPERATOR;

COMMIT WORK ;