В данной статье рассказывается как создать интернет-счётчик для корпоративного портала.

Задача
Счётчик должен вести подробную информацию о посетителях: адрес откуда пришёл посетитель, адрес страницы, ip-адрес посетителя, дата, браузер пользователя,
операционная система.

По ip-адресу можно определить страну и регион откуда пришёл посетитель.

Кроме того мы должны иметь возможность просматривать статистику посещений по дням, количество новых посетителей (и их ip-адреса), количество уникальних
посетителей (и их ip-адреса), самые лучшие ссылки на на нашу интернет-страницу, самые популярные страницы и т.п..

Схема выполнения
Для начала, рассмотрим схему выполнения счётчика.

Пользователь из интернет (Пользователь1, Пользователь2, Пользователь3) заходит на нашу интернет-страницу, расположенную на сервере провайдера.
На интернет-странице выполняется код на php, который определяет ip-адрес пользователя, url откуда пришёл, url страницы, браузер и операционную систему
пользователя. Эти данные программа передаёт на корпоративный интернет-сервер.
На корпоративном интернет-сервере вызывается хранимая процедура interbase для передачи данных о пользователе и получении статистической информации для
интернет-страницы.
Статистически данные передаются в программу на Интернет-странице и выводятся для посетителя.
Со временем, администратор подключается к interbase-серверу с помощью клиенской программы (написанной на delphi) и обрабатывает информацию.

Примечание: Корпоративный сервер и interbase-сервер можно совместить, но тогда уменьшается уровень защиты информации. Так как зная пароль и имя пользователя
можно будет подключиться к базе данных из интернет.

Структура базы данных
Теперь создадим структуру базы данных.

Таблица project предназначена для хранения названия проекта. Таким образом, мы можем создавать сколько угодно счётчиков.

В таблице countdata накапливается информация о посетителях:

urlfrom – откуда пришёл посетитель,
urlcurr – какую страницу посетили,
datehint – дата (без времени) когда посетили,
fulldate – полная дата,
ipuser – ip-адрес посетителя,
browser – браузер,
systemuser – операционная система пользователя,
systemver – версия операционной системы,
compname – название компьютера посетителя.

В таблице iptable хранятся адреса уникальных ip-адресов.

Скрипт базы данных
Сгенерируем скрипт базы данных по данной структуре:

create table countdata (
countdataid integer not null,
projid integer,
urlfrom varchar(1024),
urlcurr varchar(1024),
datehint date,
fulldate date,
ipuser varchar(15),
browser varchar(255),
systemuser varchar(255),
systemver varchar(255)
);

create index if318countdata on countdata
(
projid
);

create index ak_ip on countdata
(
ipuser
);

create index ak_datehint on countdata
(
datehint
);

alter table countdata
add constraint pkcountdata primary key (countdataid);

create table iptable (
ipuser varchar(15) not null,
dateadd date
);

create index akdateadd on iptable
(
dateadd
);

alter table iptable
add constraint pkiptable primary key (ipuser);

create table project (
projid integer not null,
projname varchar(255),
comment blob sub_type 1
);

alter table project
add constraint pkproject primary key (projid);

alter table countdata
add constraint project_countdata
foreign key (projid)
references project;

create exception erwin_parent_insert_restrict "cannot insert parent table because child table exists.";
create exception erwin_parent_update_restrict "cannot update parent table because child table exists.";
create exception erwin_parent_delete_restrict "cannot delete parent table because child table exists.";
create exception erwin_child_insert_restrict "cannot insert child table because parent table does not exist.";
create exception erwin_child_update_restrict "cannot update child table because parent table does not exist.";
create exception erwin_child_delete_restrict "cannot delete child table because parent table does not exist.";

set term ^;

create trigger ti_countdata for countdata after insert as
declare variable numrows integer;
begin
select count(*)
from project
where
new.projid = project.projid into numrows;
if (
new.projid is not null and
numrows = 0
) then
begin
exception erwin_child_insert_restrict;
end

end ^

create trigger tu_countdata for countdata after update as
declare variable numrows integer;
begin
select count(*)
from project
where
new.projid = project.projid into numrows;
if (
new.projid is not null and
numrows = 0
) then
begin
exception erwin_child_update_restrict;
end

end ^

create trigger td_project for project after delete as
declare variable numrows integer;
begin
select count(*)
from countdata
where
countdata.projid = old.projid into numrows;
if (numrows > 0) then
begin
exception erwin_parent_delete_restrict;
end

end ^

create trigger tu_project for project after update as
declare variable numrows integer;
begin
if
(old.projid <> new.projid) then
begin
update countdata
set
countdata.projid = new.projid
where
countdata.projid = old.projid;
end

end ^

Триггера и хранимые процедуры
Уникальные ip-адреса

Уникальные ip-адреса будут фиксироваться автоматически базой данных. Для этого мы напишем пару триггеров.

Триггер вставки уникальных ip-адресов:

create trigger rti_countdata for countdata before insert position 0 as
declare variable cnt integer;
begin
new.countdataid=gen_id(gcountdataid,1);
new.datehint="today";
new.fulldate="now";

select count(*)
from iptable
where (ipuser=new.ipuser)
and(projid=new.projid)
into cnt;

if (cnt=0) then
insert into iptable (ipuser, projid)
values (new.ipuser, new.projid);
end
^

Триггер заполнения даты уникального ip-адреса:

create trigger rti_iptable for iptable before insert position 0 as
begin
new.dateadd="today"
end

Регистрация пользователя в базе данных

Теперь создадим хранимую процедуру для регистрации пользователя в базе данных и получения статистической информации о посещениях.

/***********************************************************************/
/* Процедура регистрации посетителя интернет странички */
/***********************************************************************/
create procedure proc_count_data
(projid integer,
urlfrom varchar(1024),
urlcurr varchar(1024),
ipuser varchar(15),
browser varchar(255),
systemuser varchar(255),
systemver varchar(255),
compname varchar(255)
)
returns (
mindate date, /* Дата установки счётчика */
number_zagruzk integer, /* Количество загрузок */
number_un_zagruzk integer, /* Количество уникальных загрузок */
number_zagr_date integer, /* Количество загрузок сегодня */
number_zagr_un_date integer) /* Количество уникальных загрузок */
as
begin

insert into countdata (projid, urlfrom, urlcurr, ipuser,
browser, systemuser, systemver, compname)
values (:projid, :urlfrom, :urlcurr, :ipuser,
:browser, :systemuser, :systemver, :compname);

/* Время установки счётчика */
select min(datehint)
from countdata
into :mindate;

if (mindate is null) then mindate="today";

/* Колчичество загрузок */
select count(*)
from countdata
into :number_zagruzk;

/* Количество уникальных загрузок */
select count(*)
from iptable
into :number_un_zagruzk;

/* Колчичество загрузок сегодня */
select count(*)
from countdata
where datehint="today"
into :number_zagr_date;

/* Количество уникальных загрузок сегодня */
select count(*)
from iptable
where dateadd="today"
into :number_zagr_un_date;

suspend;

end
^

Расшифруем передаваемые поля процедуре: 

projid – id проекта (счётчика),
urlfrom – откуда пришли,
urlcurr – куда пришли,
ipuser – ip-адрес пользователя,
browser - браузер,
systemuser – операционная система пользователя,
systemver – версия операционной системы пользователя,
compname – название компьютера. 

Процедура возвращает статистическую информацию: 

mindate - дата установки счётчика,
number_zagruzk integer – общее количество загрузок,
number_un_zagruzk integer – общее количество уникальных загрузок,
number_zagr_date integer - количество загрузок за сегодня,
number_zagr_un_date integer) - Количество уникальных загрузок за сегодня.

Получение списка повторных посетителей

Хранимая процедура для получения списка повторных посетителей

/***********************************************************************/
/* Процедура для извлечения повторных посетителей */
/***********************************************************************/
create procedure povtor_posetit
(projid integer,
dateot date,
datedo date
)
returns (
ipuser varchar(15)
)
as
begin
for select c.ipuser
from countdata c
where (c.datehint>=:dateot)
and(c.datehint< =:datedo)
and(c.projid>=:projid)
and(c.ipuser not in (select it.ipuser
from iptable it
where (it.dateadd>=:dateot)
and(it.dateadd< =:datedo)
and(it.projid=:projid)
))
group by c.ipuser
into :ipuser
do
begin
suspend;
end

end
^

Процедуре мы передаём диапазон за который хотим посмотреть статистику, а она возвращает ip-адреса пользователей, которые повторно посетили наш сайт.

Статистика посещений по дням

Хранимая процедура для расчёты статистики посещений по дням:

/***********************************************************************/
/* Статистика посещений по дням */
/***********************************************************************/
create procedure stat_day
(projid integer,
dateot date,
datedo date
)
returns (
date_posetit date, /* Дата посещения */
kol_posesch integer,/* Количество посещений */
kol_unikaln integer, /* Количество уникальных загрузок */
kol_povtorn_poset integer /* Количество повторных посетителей */
)
as
begin

for select datehint
from countdata
where (projid=:projid)
and(datehint>=:dateot)
and(datehint< =:datedo)
group by datehint
into :date_posetit
do
begin

/* Колчичество загрузок */
select count(*)
from countdata
where datehint=:date_posetit
into :kol_posesch;

/* Количество уникальных загрузок */
select count(*)
from iptable
where dateadd=:date_posetit
into :kol_unikaln;

select count(*)
from povtor_posetit(:projid, :date_posetit, :date_posetit)
into kol_povtorn_poset;

suspend;
end

end
^

Лучшие ссылки на сайт

Хранимая процедура для получения лучших ссылок на сайт

/***********************************************************************/
/* Лучшая ссылка на наш сайт */
/***********************************************************************/
create procedure best_link
(projid integer,
dateot date,
datedo date
)
returns (
urlfrom varchar(1024),
count_urls integer
)
as
begin
for select urlfrom, count(*) count_urls
from countdata
where (projid=:projid)
and(datehint>=:dateot)
and(datehint< =:datedo)
group by urlfrom
into :urlfrom, :count_urls
do
begin
suspend;
end
end
^

Лучшая интернет-страница

Хранимая процедура для получения статистики по посещаемости наших страниц:

/***********************************************************************/
/* Лучшая наша страничка */
/***********************************************************************/
create procedure best_page
(projid integer,
dateot date,
datedo date
)
returns (
urlcurr varchar(1024),
count_urls integer
)
as
begin
for select urlcurr, count(*) count_urls
from countdata
where (projid=:projid)
and(datehint>=:dateot)
and(datehint< =:datedo)
group by urlcurr
into :urlcurr, :count_urls
do
begin
suspend;
end
end
^

На нашем сервере (код на php)

Прежде всего, необходимо настроить apache на нашем сервере. Как его устанавливать и настраивать Вы можете во многочисленных статьях интернета.

Скрипт для подключения к нашей базе данных interbase, передачи информации о пользователе и получения статистической информации.

acounter.php

< ?php
/*include("config.php");*/
include "classdb.php3";
class acounter {
var $config = array();
var $conn;
var $dbname;
var $dbuser;
var $dbpass;
var $number_zagruzk;
var $number_un_zagruzk;
var $number_zagr_date;
var $number_zagr_un_date;
var $mindate;
var $okrugl; 

function acounter () {

/*Подключение к БД*/
include "config.php";
$this->okrugl = $okrugl;
$this->dbname = $dbname;
$this->dbuser = $dbuser;
$this->dbpass = $dbpass;
$this->conn=ibase_connect($this->dbname,$this->dbuser,$this->dbpass);

/* url to the digitset */
$this->config['img'] = "http://mysite.com.ua/mycounter/digits/";

/* url to the animated digitset */
$this->config['animated_img'] = "http://mysite.com.ua/mycounter/digits_ani/";

/* how many digits to show */
$this->config['pad'] = 6;

/* digit width and height */
$this->config['width'] = 16;
$this->config['height'] = 22;

/* timeout (minutes) */
$this->config['block_time'] = 15;
}

//Получает количество записей в таблице countdata(количество посещений)
function ibase_fetch_array($res) {
return get_object_vars(ibase_fetch_object($res));
}

function ibase_num_rows($query) {
$i=0;
while (ibase_fetch_row($query)){
$i++;}
return $i;
}

function getcountervalue() {
$sqlexpr="select * from countdata";
$sth = ibase_query($this->conn,$sqlexpr);
$countervalue=$this->ibase_num_rows($sth);
return $countervalue;
}
//
function insertdata($ip='',$urlfrom='',$urlcurr='',$host='') {
//beru vid brausera, versiu brauzera, platformu mashini;
$info=getenv("http_user_agent");
// $urlfrom=getenv("http_referer");
$gateway=getenv("gateway_interface");
$connect=getenv("server_protocol");
//beru ip-adres;
// $ip = getenv("remote_addr");

$db = new cconnectionibase();
$sqlexpr="select * from proc_count_data(1, '".$urlfrom."', '".$urlcurr."', '".$ip."', '', '".$info."', '','".$host."')";

$sth = ibase_query($this->conn,$sqlexpr);
$mas=$this->ibase_fetch_array($sth);
$this->number_zagruzk = $mas[number_zagruzk];
$this->number_un_zagruzk = $mas[number_un_zagruzk];
$this->number_zagr_date = $mas[number_zagr_date];
$this->number_zagr_un_date = $mas[number_zagr_un_date];
$this->mindate = $mas[mindate];
}

function create_output($ip='',$urlfrom='',$urlcurr='',$host='') {
$this->insertdata($ip,$urlfrom,$urlcurr,$host);
//vivogu col. zagruzok

$html_output = "< table cellpadding=\"0\" cellspacing=\"0\" border=\"0\">< tr>n";
$html_output .="< font size='1' color='#0000ff'>Всего загрузок:< /font>< /tr>n< tr align=\"center\">n";
$html_output .= "< font size='3' color='#800080'>< b>".sprintf($this->okrugl,$this->number_zagruzk)."< /b>< /font>";
$html_output .= "< /tr>< /table>n";
//vivogu col. unikalnih zagruzok

$html_output .= "< table cellpadding=\"0\" cellspacing=\"0\" border=\"0\">< tr>n";
$html_output .="< font size='1' color='#0000ff'>Всего уникальных загрузок:< /font>< /tr>n< tr align=\"center\">n";
$html_output .= "< font size='3' color='#800080'>< b>".sprintf($this->okrugl,$this->number_un_zagruzk)."< /b>< /font>";
$html_output .= "< /tr>< /table>n";
//vivogu col. zagruzok segodnia

$html_output .= "< table cellpadding=\"0\" cellspacing=\"0\" border=\"0\">< tr>n";
$html_output .="< font size='1' color='#0000ff'>Загрузок за день:< /font>< /tr>n< tr align=\"center\">n";
$html_output .= "< font size='3' color='#800080'>< b>".sprintf($this->okrugl,$this->number_zagr_date)."< /b>< /font>";
$html_output .= "< /tr>< /table>n";

//vivogu col. unikalnih zagruzok segodnia

$html_output .= "< table cellpadding=\"0\" cellspacing=\"0\" border=\"0\">< tr>n";
$html_output .="< font size='1' color='#0000ff'>Уникальных загрузок за день:< /font>< /tr>n< tr align=\"center\">n";
$html_output .= "< p align='center'>< font size='3' color='#800080'>< b>".sprintf($this->okrugl,$this->number_zagr_un_date)."< /b>< /font>< /p>";
$html_output .= "< /tr>< /table>n";

//vivogu datu ustanovki schetchika
$html_output .= "< table cellpadding=\"0\" cellspacing=\"0\" border=\"0\">< tr>n";
$html_output .="< font size='1' color='#0000ff'>Дата установки счётчика:< /font>< /tr>n< tr align=\"center\">n";
$str=substr($this->mindate,0,10);
$html_output .= "< font size='1' color='#800080'>".$str."< /font>";
$html_output .= "< /tr>< /table>n";

return $html_output;
}

}

?>

classdb.php3

< ?php
//---------------------------------------------------------------------------------------------
class cconnection {
var $err_logon = "can't connect to database %s!";

var $descriptor = 0; // database descriptor
var $result; // result array
var $countrow = 0; // number of records in result array
var $countfield = 0; // number of fields in result array

// clears result array. for internal use.
function freequery() {
unset($this->result);
$this->countrow = 0;
$this->countfield = 0;
}

// returns content of specified cell of result array or null if $col or $row is wrong.
// $col can to hold the field name or field index
function getdata($col, $row) {
if ((0 < = $row) &#038;& ($row <  $this->countrow)) {
if (!is_string($col)) {
reset($this->result);
for ($fno = 0; $fno < $col; $fno++) next($this->result);
$col = current($this->result);
return $col[$row];
} else return $this->result[strtoupper($col)][$row];
} else return null;
}

// returns field name by field index or empty string if $col is wrong
function getfieldname($col) {
if (is_integer($col) &#038;& (0 < = $col) &#038;& ($col <  $this->countfield)) {
reset($this->result);
for ($fno = 0; $fno < $col; $fno++) next($this->result);
list($key, $val) = each($this->result);
return $key;
} else return "";
}
}

//---------------------------------------------------------------------------------------------
class cconnectionibase extends cconnection {
// constructor. creates class.
function cconnectionibase() {}

// opens specified database.
// returns database descriptor.
function open($database, $username = "sysdba", $password = "masterkey", $charset="win1251") {
$this->close();
$this->descriptor = ibase_connect($database, $username, $password, $charset);
return $this->descriptor;
}

// closes current database connection
function close() {
if ($this->descriptor) {
$this->freequery();
ibase_close($this->descriptor);
$this->descriptor = 0;
}
}

// prepares data to storing in blobs. used in query &#038; execute functions
// returns query statement descriptor
function execcode($code, $blobs=0) {
$statement = 0;
$this->freequery();
if ($this->descriptor) {
$cmd = "$"."statement = ibase_query("."$"."this->descriptor, "."$"."code";
if (is_array($blobs) &#038;& count($blobs)) {
reset($blobs);
$fno = 0;
while (list($key, $val) = each($blobs)) {
$finfo[$fno] = array("blob_id" => ibase_blob_create(), "blob_str" => "");
if (is_string($val)) ibase_blob_add($finfo[$fno]["blob_id"], $val);
else ibase_blob_add($finfo[$fno]["blob_id"], "not supported yet, sorry");
$finfo[$fno]["blob_str"] = ibase_blob_close($finfo[$fno]["blob_id"]);
$cmd = $cmd.", $"."finfo[$fno][\"blob_str\"]";
$fno++;
}
}
$cmd = $cmd.");";
eval($cmd);
}
return $statement;
}

// executes select statement and fills result array by dataset contents
// returns number of records placed to result array
function query($code, $blobs=0) {
if ($statement = $this->execcode($code, $blobs)) {
while ($row = ibase_fetch_row($statement)) {
while(list($fno, $val) = each($row)) {
// getting information about fields existing in current row
if ($this->countfield < count($row)) $this->countfield = count($row);
$finfo = ibase_field_info($statement, $fno);
$fname = $finfo["alias"];
$ftype = $finfo["type"];
unset($finfo);

if (!strcmp($ftype, "blob")) {
// getting data from blob field
if (($finfo = ibase_blob_info($val)) &#038;&
($finfo["length"] > 0) &#038;&
($val = ibase_blob_open($val))) {
$blob = ibase_blob_get($val, $finfo["length"]);
ibase_blob_close($val);
} else $blob = "";
$this->result[$fname][$this->countrow] = $blob;
unset($blob);
} else {
// getting data from another field
if (isset($val)) {
if (is_string($val))
$this->result[$fname][$this->countrow] = trim($val);
else $this->result[$fname][$this->countrow] = $val;
} else $this->result[$fname][$this->countrow] = "";
}
}
$this->countrow++;
// cleaning temporary variables
unset($row);
unset($fno);
unset($val);
unset($finfo);
unset($fname);
unset($ftype);
}
ibase_free_result($statement);
unset($statement);
}
return $this->countrow;
}

// executes insert, delete or update statements. result array is empty.
// returns nonzero if all ok
function execute($code, $blobs=0) {
if ($statement = $this->execcode($code, $blobs)) {
@ibase_free_result($statement);
return 1;
}
return 0;
}

// commits current transaction
function commit() { ibase_commit(); }

// rollbacks current transaction
function rollback() { ibase_rollback(); }
}

//---------------------------------------------------------------------------------------------
class cconnectionoci extends cconnection {
function cconnectionoci() {}

function open($database = "", $username = "system", $password = "manager") {
$this->close();
if (($database) &#038;& strlen($database))
$this->descriptor = ocilogon($username, $password, $database);
else $this->descriptor = ocilogon($username, $password);
return $this->descriptor;
}

function close() {
if ($this->descriptor) {
$this->freequery();
ocilogoff($this->descriptor);
$this->descriptor = 0;
}
}

function query($code) {
$this->freequery();
if ($this->descriptor) {
if ($code &#038;& ($statement = ociparse($this->descriptor, $code))) {
ociexecute($statement, oci_default);
$this->countrow = ocifetchstatement($statement, $this->result);
$this->countfield = count($this->result);
ocifreestatement($statement);
}
}
return $this->countrow;
}

function execute($code, $blob=0) {
$res = 0;
$this->freequery();
if ($this->descriptor) {
if ($code) {
if ($blob) $lob = ocinewdescriptor($this->descriptor, oci_d_lob);
if ($statement = ociparse($this->descriptor, $code)) {
if ($blob) ocibindbyname($statement, ":blob", &#038;$lob, -1, oci_b_clob);
ociexecute($statement, oci_default);
if ($lob) {
if ($lob->save($blob)) $res = 1;
ocifreedescriptor($lob);
} else $res = 1;
ocifreestatement($statement);
}
}
}
return $res;
}

function commit() {
if ($this->descriptor) ocicommit($this->descriptor);
}

function rollback() {
if ($this->descriptor) ocirollback($this->descriptor);
}
}

//---------------------------------------------------------------------------------------------
?>

Файл для конфигурирования счётчика:

config.php

< ?

$dbname = "server:c:databasecounter.gdb";
$dbuser = "sysdba";
$dbpass = "masterkey";
$okrugl = "%07s";
?>

$dbname – название базы данных,
$dbuser- имя пользователя interbase,
$dbpass – пароль,
$okrugl – количество цифр в счётчики (например, 0000012).
Файл, который вызывается из интернет-страницы:

test.php

< ?php
include_once "acounter.php";
$ani_counter = new acounter();
echo $ani_counter->create_output($_get["ip"],$_get["urlfrom"],$_get["urlcurr"],$_get["host"]);

?>

На интернет-странице (код на php)
Эта часть находится на сервере провайдера (там, где находится наша интернет-страница).

Создаём скрипт для определения данных о пользователе:

counter.inc

< ?php

// phpinfo();
//beru vid brausera, versiu brauzera, platformu mashini;

$info=getenv("http_user_agent");
$urlfrom=getenv("http_referer");
$urlcurr=$_server["request_uri"];
$host=getenv("http_host");
//beru ip-adres;
$ip = getenv("remote_addr");
readfile('http://client70.ukrtelebud.com.ua/rudjukcounter/test.php?ip='.$ip.'&#038;urlfrom='.$urlfrom.'&#038;urlcurr='.$urlcurr.'&#038;host='.$host);

// readfile('http://mysite.com.ua/mycounter/test.php?ip='.$ip.'&#038;urlfrom='.$urlfrom);
// readfile('http://mysite.com.ua/mycounter/test.php') 

?>

На самой интернет-странице прописываем скрипт, который и будет запускать весь счётчик:

< ?php
include ("counter_inc.php")
?>

Примечание: Для того, чтобы счётчик работал корректно необходимо файлы интернет-страницы называть с расширением php, а не htm.

Клиентская часть
Саму обработку статистических данных удобнее всего сделать на delphi.

Реализацию на delphi я оставляю читателю, приведу лишь sql-запросы для получения необходимых данных.

sql-запросы

Получение подробной информации о посетителях:

select *
from countdata
where (projid=:projid)
and(datehint>=:dateot)
and(datehint< =:datedo)
order by countdataid

Статистика по дням:

select *
from stat_day(:projid, :dateot, :datedo) order by date_posetit

Лучшие ссылки на интернет-страницу:

select *
from best_link(:projid, :dateot, :datedo)
order by count_urls desc

Лучшие интернет-страницы:

select *
from best_page(:projid, :dateot, :datedo) order by count_urls desc

Уникальные ip-адреса:

select *
from iptable
where (projid=:projid)
and(dateadd>=:dateot)
and(dateadd< =:datedo)

ip-адреса повторных посетителей:

select *
from povtor_posetit(:projid, :dateot, :datedo) order by ipuser

Заключение
В программе есть ряд неточностей, а так же не определяются страны по ip-адресу. Эти задачи я оставляю за читателями.