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

Счётчик должен вести подробную информацию о посетителях: адрес откуда пришёл посетитель, адрес страницы, 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;

susp end;

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
susp end;
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;

susp end;
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
susp end;
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
susp end;
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 getcounter value() {
$sqlexpr=« select * from countdata»;
$sth = ibase_query($this->conn,$sqlexpr);
$counter value=$this->ibase_num_rows($sth);
return $counter value;
}
//
function insertdata($ip=«»,$urlfrom=«»,$urlcurr=«»,$host=«») {
//beru vid brausera, versiu brauzera, plat formu 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 = «
\n»;
$html_output .=« Всего загрузок:
\n
\n»;
$html_output .= « ».sprintf($this->okrugl,$this->number_zagruzk).«»;
$html_output .= «

\n»;
//vivogu col. unikalnih zagruzok

$html_output .= «
\n»;
$html_output .=« Всего уникальных загрузок:
\n
\n»;
$html_output .= « ».sprintf($this->okrugl,$this->number_un_zagruzk).«»;
$html_output .= «

\n»;
//vivogu col. zagruzok segodnia

$html_output .= «
\n»;
$html_output .=« Загрузок за день:
\n
\n»;
$html_output .= « ».sprintf($this->okrugl,$this->number_zagr_date).«»;
$html_output .= «

\n»;

//vivogu col. unikalnih zagruzok segodnia

$html_output .= «
\n»;
$html_output .=« Уникальных загрузок за день:
\n
\n»;
$html_output .= «

».sprintf($this->okrugl,$this->number_zagr_un_date).«
»;
$html_output .= «

\n»;

//vivogu datu ustanovki schetchika
$html_output .= «
\n»;
$html_output .=« Дата установки счётчика:
\n
\n»;
$str=substr($this->mindate,0,10);
$html_output .= « ».$str.«»;
$html_output .= «

\n»;

return $html_output;
}

}

?>

classdb.php3

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

var $de scriptor = 0; // database de scriptor
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) && ($row countrow)) {
if (!is_string($col)) {
reset($this->result);
for ($fno = 0; $fno 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) && (0 <= $col) && ($col countfield)) {
reset($this->result);
for ($fno = 0; $fno result);
list($key, $val) = each($this->result);
return $key;
} else return «»;
}
}

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

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

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

// prepares data to storing in blobs. used in query & execute functions
// returns query statement de scriptor
function execcode($code, $blobs=0) {
$statement = 0;
$this->freequery();
if ($this->de scriptor) {
$cmd = «$».«statement = ibase_query(».«$».«this->de scriptor, ».«$».«code»;
if (is_array($blobs) && 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 in formation about fields existing in current row
if ($this->countfield 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)) &&
($finfo[«length»] > 0) &&
($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 ext ends cconnection {
function cconnectionoci() {}

function open($database = «», $username = «system», $password = «manager») {
$this->close();
if (($database) && strlen($database))
$this->de scriptor = ocilogon($username, $password, $database);
else $this->de scriptor = ocilogon($username, $password);
return $this->de scriptor;
}

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

function query($code) {
$this->freequery();
if ($this->de scriptor) {
if ($code && ($statement = ociparse($this->de scriptor, $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->de scriptor) {
if ($code) {
if ($blob) $lob = ocinewde scriptor($this->de scriptor, oci_d_lob);
if ($statement = ociparse($this->de scriptor, $code)) {
if ($blob) ocibindbyname($statement, «:blob», &$lob, — 1, oci_b_clob);
ociexecute($statement, oci_default);
if ($lob) {
if ($lob->save($blob)) $res = 1;
ocifreede scriptor($lob);
} else $res = 1;
ocifreestatement($statement);
}
}
}
return $res;
}

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

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

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

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

config.php

<?

$dbname = «server:c:\database\counter.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, plat formu 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.«&urlfrom=».$urlfrom.«&urlcurr=».$urlcurr.«&host=».$host);

// readfile(«http://mysite.com.ua/mycounter/test.php?ip=».$ip.«&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

Вот что получилось
Работу интернет-счётчика Вы можете увидеть на нашем сайте http://www.rudjuk.kiev.ua/.

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

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

Лучшие ссылки:

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

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

Повторные ip-адреса:

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