Up to [Platon] / phpWebLogAnalyzer / old

Changed string "Platon software development group"
to "Platon Software Development Group".

<?php

/*
 * phpWebLogAnalyzer - powerful weblog and analyzer
 *
 * phpWebLogAnalyzer.inc.php - main file of PHP API
 * __________________________________________________________
 *
 * Developed by Ondrej Jombik <nepto@pobox.sk>
 * Copyright (c) 2001 Platon Software Development Group
 * All rights reserved.
 *
 * See README file for more informations about this software.
 * See COPYING file for license informations.
 *
 * Download the latest version from
 * http://www.platon.sk/projects/phpWebLogAnalyzer/
 */

/*
 * 01/07/2001 - created
 * 18/07/2001 - added support for ident protocol (thanks to libident)
 * 04/08/2001 - completely rewritten with multiple tables handling
 * 20/09/2001 - code style improvements
 *               - import into CVS   
 * 02/10/2001 - added support for remote address resolving
 *            - new version of libident used
 *            - added support for proxy servers logging
 * 19/11/2001 - session_id column added
 *            - sessions implemented
 *            - various bugfixes and SQL improvements
 */

/*
 * Before doing anything else read config file.
 */
include("config.inc.php");

/*
 * Ident library
 */
include_once("libident/libident.php");

/*
 * phpWebLogAnalyzer global variabiles
 */
$phpWebLogAnalyzer_db_handle = 0;
unset($phpWebLogAnalyzer_db_handle);


/*
 * phpWebLogAnalyzer_open_db_connection()
 *
 * Opens database connection.
 */
function phpWebLogAnalyzer_open_db_connection() {
    global $phpWebLogAnalyzer_db_handle;
    global $phpWebLogAnalyzer_MYSQL_HOST;
    global $phpWebLogAnalyzer_MYSQL_PORT;
    global $phpWebLogAnalyzer_MYSQL_USERNAME;
    global $phpWebLogAnalyzer_MYSQL_PASSWORD;
    global $phpWebLogAnalyzer_MYSQL_DATABASE;

    $phpWebLogAnalyzer_db_handle = @mysql_connect(
            "$phpWebLogAnalyzer_MYSQL_HOST:$phpWebLogAnalyzer_MYSQL_PORT",
            $phpWebLogAnalyzer_MYSQL_USERNAME, 
            $phpWebLogAnalyzer_MYSQL_PASSWORD);

    if ($phpWebLogAnalyzer_db_handle == false)
        return false;

    if (@mysql_select_db($phpWebLogAnalyzer_MYSQL_DATABASE, 
                $phpWebLogAnalyzer_db_handle) == false)
        return false;

    return $phpWebLogAnalyzer_db_handle;
}


/*
 * phpWebLogAnalyzer_close_db_connection()
 *
 * Closes database connection.
 */
function phpWebLogAnalyzer_close_db_connection() {
    global $phpWebLogAnalyzer_db_handle;

    $phpWebLogAnalyzer_db_handle = @mysql_close($phpWebLogAnalyzer_db_handle);

    return $phpWebLogAnalyzer_db_handle; // true if OK, false if failed
}


/*
 * phpWebLogAnalyzer_check_db_handle()
 *
 * Check if database handle is still opened.
 */
function phpWebLogAnalyzer_check_db_handle() {
    global $phpWebLogAnalyzer_db_handle;

    if (! isset($phpWebLogAnalyzer_db_handle)) {
        phpWebLogAnalyzer_open_db_connection() 
            or phpWebLogAnalyzer_error("database server connection error");
    }
}


/*
 * phpWebLogAnalyzer_register_access()
 *
 * Register access. Insert one record to accesses table.
 * Also insert records into another tables if it is neccessary.
 */
function phpWebLogAnalyzer_register_access($value = 0) {
    global $phpWebLogAnalyzer_db_handle;
    global $phpWebLogAnalyzer_MYSQL_DATABASE;
    global $phpWebLogAnalyzer_LOG_TABLE;

    phpWebLogAnalyzer_check_db_handle();

    global $REQUEST_METHOD;
    global $QUERY_STRING;
    global $PATH_INFO; 
    global $PATH_TRANSLATED;
    global $CONTENT_TYPE; 
    global $CONTENT_LENGTH; 
    global $SCRIPT_NAME;
    global $SERVER_NAME;
    global $SERVER_PORT; 
    global $SERVER_SOFTWARE;
    global $SERVER_PROTOCOL;
    global $SERVER_ADMIN;
    global $SERVER_SIGNATURE;
    global $GATEWAY_INTERFACE;
    global $REMOTE_HOST; 
    global $REMOTE_ADDR;
    global $REMOTE_PORT;
    global $AUTH_TYPE; 
    global $REMOTE_USER; 
    global $REMOTE_IDENT; 
    global $DOCUMENT_ROOT;
    global $HTTP_ACCEPT;
    global $HTTP_ACCEPT_CHARSET;
    global $HTTP_ACCEPT_ENCODING;
    global $HTTP_ACCEPT_LANGUAGE;
    global $HTTP_CONNECTION;
    global $HTTP_HOST;
    global $HTTP_REFERER;
    global $HTTP_USER_AGENT;
    global $SCRIPT_FILENAME;
    global $REQUEST_URI;

    $ip_addr = '';
    $hostname = '';
    $proxy_ip_addr = '';
    $proxy_hostname = '';
    $ident_user = '';
    $ident_ostype = '';

    /*
     * IP address & proxy server getting.
     */

    if (getenv('HTTP_X_FORWARDED_FOR') != '') {  
        $ip_addr = getenv('HTTP_X_FORWARDED_FOR');
        $proxy_ip_addr = getenv('REMOTE_ADDR');
    }
    else {
        $ip_addr = getenv('REMOTE_ADDR');
    }

    /*
     * Host name resolving.
     */

    $hostname = @gethostbyaddr($ip_addr);
    if (! strcmp($hostname, $ip_addr))
        $hostname = '';

    if ($proxy_ip_addr) {
        $proxy_hostname = @gethostbyaddr($proxy_ip_addr);
        if (! strcmp($proxy_hostname, $proxy_ip_addr))
            $proxy_hostname = '';
    }

    /*
     * Ident protocol stuff
     */
    $ident = new Net_Ident($ip_addr, $REMOTE_PORT, $SERVER_PORT);

    if ($ident->status() == 1) {
        // print "Ident query failed: " . $ident->error() . ".<br>\n";
    } 
    else {
        $ident_user = $ident->user(); 
        $ident_ostype = $ident->osType(); 
    }

    $record = array ( // * - not in PHP manual

            'id' => 0, // 0 - auto_increment
            'value_id' => phpWebLogAnalyzer_get_id(
                'weblog_values', $value),
            'request_method_id' => phpWebLogAnalyzer_get_id(
                'weblog_request_methods', $REQUEST_METHOD),
            'query_string_id' => phpWebLogAnalyzer_get_id(
                'weblog_query_strings', $QUERY_STRING),
            'path_info_id' => phpWebLogAnalyzer_get_id(
                'weblog_path_infos', $PATH_INFO), // *
            'path_translated_id' => phpWebLogAnalyzer_get_id(
                'weblog_paths_translated', $PATH_TRANSLATED),
            'content_type_id' => phpWebLogAnalyzer_get_id(
                'weblog_content_types', $CONTENT_TYPE), // *
            'content_length' => $CONTENT_LENGTH, // * 
            'script_name_id' => phpWebLogAnalyzer_get_id(
                'weblog_script_names', $SCRIPT_NAME),
            'server_name_id' => phpWebLogAnalyzer_get_id(
                'weblog_server_names', $SERVER_NAME),
            'server_port' => $SERVER_PORT, // *
            'server_software_id' => phpWebLogAnalyzer_get_id(
                'weblog_server_softwares', $SERVER_SOFTWARE),
            'server_protocol_id' => phpWebLogAnalyzer_get_id(
                    'weblog_server_protocols', $SERVER_PROTOCOL),
            'server_admin_id' => phpWebLogAnalyzer_get_id(
                    'weblog_server_admins', $SERVER_ADMIN),
            'server_signature_id' => phpWebLogAnalyzer_get_id(
                    'weblog_server_signatures', $SERVER_SIGNATURE),
            'gateway_interface_id' => phpWebLogAnalyzer_get_id(
                    'weblog_gateway_interfaces', $GATEWAY_INTERFACE),
            'remote_host_id' => phpWebLogAnalyzer_get_id(
                    'weblog_remote_hosts', $hostname), // *
            'proxy_addr_id' => phpWebLogAnalyzer_get_id(
                    'weblog_remote_addrs', $proxy_ip_addr),
            'proxy_host_id' => phpWebLogAnalyzer_get_id(
                    'weblog_remote_hosts', $proxy_hostname), // *
            'remote_addr_id' => ($remote_addr_id = 
                    phpWebLogAnalyzer_get_id(
                        'weblog_remote_addrs', $ip_addr)),
            'remote_host_id' => phpWebLogAnalyzer_get_id(
                    'weblog_remote_hosts', $hostname), // *
            'remote_port' => $REMOTE_PORT,
            'auth_type_id' => phpWebLogAnalyzer_get_id(
                    'weblog_auth_types', $AUTH_TYPE), // *
            'remote_user_id' => phpWebLogAnalyzer_get_id(
                    'weblog_remote_users', $REMOTE_USER), // *
            'remote_ident_id' => phpWebLogAnalyzer_get_id(
                    'weblog_remote_idents', $REMOTE_IDENT), // * 
            'document_root_id' => phpWebLogAnalyzer_get_id(
                    'weblog_document_roots', $DOCUMENT_ROOT),
            'http_accept_id' => phpWebLogAnalyzer_get_id(
                    'weblog_http_accepts', $HTTP_ACCEPT),
            'http_accept_charset_id' => phpWebLogAnalyzer_get_id(
                    'weblog_http_accept_charsets', $HTTP_ACCEPT_CHARSET),
            'http_accept_encoding_id' => phpWebLogAnalyzer_get_id(
                    'weblog_http_accept_encodings', $HTTP_ACCEPT_ENCODING),
            'http_accept_language_id' => phpWebLogAnalyzer_get_id(
                    'weblog_http_accept_languages', $HTTP_ACCEPT_LANGUAGE),
            'http_connection_id' => phpWebLogAnalyzer_get_id(
                    'weblog_http_connections', $HTTP_CONNECTION),
            'http_host_id' => phpWebLogAnalyzer_get_id(
                    'weblog_http_hosts', $HTTP_HOST),
            'http_referer_id' => phpWebLogAnalyzer_get_id(
                    'weblog_http_referers', $HTTP_REFERER),
            'http_user_agent_id' => ($http_user_agent_id = 
                    phpWebLogAnalyzer_get_id(
                        'weblog_http_user_agents', $HTTP_USER_AGENT)),
            'script_filename_id' => phpWebLogAnalyzer_get_id(
                    'weblog_script_filenames', $SCRIPT_FILENAME),
            'request_uri_id' => phpWebLogAnalyzer_get_id(
                    'weblog_request_uris', $REQUEST_URI),
            'ident_username_id' => phpWebLogAnalyzer_get_id(
                    'weblog_ident_usernames', $ident_user), 
            'ident_ostype_id' => phpWebLogAnalyzer_get_id(
                    'weblog_ident_ostypes', $ident_ostype),
            'session_id' => phpWebLogAnalyzer_get_session_id(
                    $remote_addr_id, $http_user_agent_id)
                );


            $keys = '';
            $vals = '';
            $count = 0;

            foreach ($record as $key => $val) {
                $count++;
                $sep = ($count < count($record) ? ", " : "");

                $keys .= $key . $sep;
                $vals .= ($val ? "'$val'" : "NULL") . $sep;
            }

            $query = "insert into $phpWebLogAnalyzer_LOG_TABLE"
                ." ( datetime, $keys ) values ( sysdate(), $vals )";

            //print "$query<br>\n";

            $result = @mysql_query($query, $phpWebLogAnalyzer_db_handle) 
                or phpWebLogAnalyzer_db_error();

            return;
}

/*
 * phpWebLogAnalyzer_get_id()
 *
 * Return ID of $value in $table. 
 * If $value is not in table, record will be inserted.
 */
function phpWebLogAnalyzer_get_id($table, $value) {

    /* 
     * If you want to have 0 or "" in you database as valid values, 
     * uncomment this and comment the next line. Otherwise 0 and "" values 
     * will be counted as NULL.
     */

    // if (!isset($value) || !isset($table)) return 0;
    if (!$value || !$table)
        return 0;

    global $phpWebLogAnalyzer_db_handle;
    phpWebLogAnalyzer_check_db_handle();

    $query = "select id from $table"
        ." where value = substring('" . addslashes($value) . "', 1, 255)"
        ." limit 1";
    $result = @mysql_query($query, $phpWebLogAnalyzer_db_handle) 
        or phpWebLogAnalyzer_db_error();

    if ($row = @mysql_fetch_array($result))
        return $row[0];

    $query = "insert into $table (id, value)"
        ." values (0, '" . addslashes($value) . "')";
    $result = @mysql_query($query, $phpWebLogAnalyzer_db_handle) 
        or phpWebLogAnalyzer_db_error();

    $query = "select last_insert_id()";
    $result = @mysql_query($query, $phpWebLogAnalyzer_db_handle) 
        or phpWebLogAnalyzer_db_error();

    if ($row = @mysql_fetch_array($result))
        return $row[0];

    return 0;
}

/*
 * phpWebLogAnalyzer_get_session_id()
 *
 * Returns session_id according to $remote_addr_id and $http_user_agent_id
 * and session timeout defined in config.inc.php file.
 */
function phpWebLogAnalyzer_get_session_id(
        $remote_addr_id, $http_user_agent_id) {

    global $phpWebLogAnalyzer_SESSION_TIMEOUT;
    global $phpWebLogAnalyzer_db_handle;
    phpWebLogAnalyzer_check_db_handle();

    $query = "select"
        ." id, session_id,"
        ." unix_timestamp(now()) - unix_timestamp(datetime) as diff_sec"
        ." from weblog_accesses"
        ." where remote_addr_id = '$remote_addr_id'"
        ." and http_user_agent_id = '$http_user_agent_id'"
        ." order by datetime desc"
        ." limit 1";

    $result = @mysql_query($query, $phpWebLogAnalyzer_db_handle)
        or phpWebLogAnalyzer_db_error();

    if (($row = @mysql_fetch_array($result, MYSQL_ASSOC))
            && ($row[diff_sec] < $phpWebLogAnalyzer_SESSION_TIMEOUT)
            && ($row[session_id])) {

        return $row[session_id];
    }

    //echo "if: ". $row[diff_sec] . " &lt; " 
    //    . $phpWebLogAnalyzer_SESSION_TIMEOUT . "<br>";

    $query = "select max(session_id) + 1 as new_session_id"
        ." from weblog_accesses";

    $result = @mysql_query($query, $phpWebLogAnalyzer_db_handle)
        or phpWebLogAnalyzer_db_error();

    if (($row = @mysql_fetch_array($result, MYSQL_ASSOC))
            && ($row[new_session_id])) {

        return $row[new_session_id];
    }

    return 1;
}

/*
 * phpWebLogAnalyzer_get_sessions_count()
 *
 */
function phpWebLogAnalyzer_get_sessions_count($ar = '') {

    global $phpWebLogAnalyzer_db_handle;
    phpWebLogAnalyzer_check_db_handle();

    $list_tables = '';
    $list_where = '';

    if (is_array($ar)) {
        $names = array(
                'id' => array(
                    'column' => 'id_id'),
                'datetime' => array(
                    'column' => 'datetime_id'),
                'value' => array(
                    'column' => 'value_id',
                    'table' => 'weblog_values'),
                'request_method' => array(
                    'column' => 'request_method_id',
                    'table' => 'weblog_request_methods'),
                'query_string' => array(
                    'column' => 'query_string_id',
                    'table' => 'weblog_query_strings'),
                'path_info' => array(
                    'column' => 'path_info_id',
                    'table' => 'weblog_path_infos'),
                'path_translated' => array(
                    'column' => 'path_translated_id',
                    'table' => 'weblog_paths_translated'),
                'content_type' => array(
                    'column' => 'content_type_id',
                    'table' => 'weblog_content_types'),
                'content_length' => array(
                        'column' => 'content_length'),
                'script_name' => array(
                        'column' => 'script_name_id',
                        'table' => 'weblog_script_names'),
                'server_name' => array(
                        'column' => 'server_name_id',
                        'table' => 'weblog_server_names'),
                'server_port' => array(
                        'column' => 'server_port'),
                'server_software' => array(
                        'column' => 'server_software_id',
                        'table' => 'weblog_server_softwares'),
                'server_protocol' => array(
                        'column' => 'server_protocol_id',
                        'table' => 'weblog_server_protocols'),
                'gateway_interface' => array(
                        'column' => 'gateway_interface_id',
                        'table' => 'weblog_gateway_interfaces'),
                'proxy_addr' => array(
                        'column' => 'proxy_addr_id',
                        'table' => 'weblog_remote_addrs'),
                'proxy_host' => array(
                        'column' => 'proxy_host_id',
                        'table' => 'weblog_remote_hosts'),
                'remote_addr' => array(
                        'column' => 'remote_addr_id',
                        'table' => 'weblog_remote_addrs'),
                'remote_host' => array(
                        'column' => 'remote_host_id',
                        'table' => 'weblog_remote_hosts'),
                'remote_port' => array(
                        'column' => 'remote_port_id'),
                'auth_type' => array(
                        'column' => 'auth_type_id',
                        'table' => 'weblog_auth_types'),
                'remote_user' => array(
                        'column' => 'remote_user_id',
                        'table' => 'weblog_remote_users'),
                'remote_ident' => array(
                        'column' => 'remote_ident_id',
                        'table' => 'weblog_remote_idents'),
                'document_root' => array(
                        'column' => 'document_root_id',
                        'table' => 'weblog_document_roots'),
                'http_accept' => array(
                        'column' => 'http_accept_id',
                        'table' => 'weblog_http_accepts'),
                'http_accept_charset' => array(
                        'column' => 'http_accept_charset_id',
                        'table' => 'weblog_http_accept_charsets'),
                'http_accept_encoding' => array(
                        'column' => 'http_accept_encoding_id',
                        'table' => 'weblog_http_accept_encodings'),
                'http_accept_language' => array(
                        'column' => 'http_accept_language_id',
                        'table' => 'weblog_http_accept_languages'),
                'http_connection' => array(
                        'column' => 'http_connection_id',
                        'table' => 'weblog_http_connections'),
                'http_host' => array(
                        'column' => 'http_host_id',
                        'table' => 'weblog_http_hosts'),
                'http_referer' => array(
                        'column' => 'http_referer_id',
                        'table' => 'weblog_http_referers'),
                'http_user_agent' => array(
                        'column' => 'http_user_agent_id',
                        'table' => 'weblog_http_user_agents'),
                'script_filename' => array(
                        'column' => 'script_filename_id',
                        'table' => 'weblog_script_filenames'),
                'server_admin' => array(
                        'column' => 'server_admin_id',
                        'table' => 'weblog_server_admins'),
                'server_signature' => array(
                        'column' => 'server_signature_id',
                        'table' => 'weblog_server_signatures'),
                'request_uri' => array(
                        'column' => 'request_uri_id',
                        'table' => 'weblog_request_uris'),
                'ident_username' => array(
                        'column' => 'ident_username_id',
                        'table' => 'weblog_ident_usernames'),
                'ident_ostype' => array(
                        'column' => 'ident_ostype_id',
                        'table' => 'weblog_ident_ostypes'),
                'session' => array(
                        'column' => 'session_id',
                        'table' => 'weblog_sessions'),
                );

                global $phpWebLogAnalyzer_LOG_TABLE;

                $count = 0;

                foreach ($ar as $key => $val) {
                    $count++;

                    if (isset($names[$key][table])) {
                        $list_where .= "$phpWebLogAnalyzer_LOG_TABLE." 
                            . $names[$key][column] . " = " 
                            . $names[$key][table] . ".id and "
                            . $names[$key][table] . ".value like '$val'";

                        $list_tables .= $names[$key][table];

                        if ($count < count($ar))
                            $list_tables .= ', ';
                    }
                    else {
                        $list_where .= $names[$key] . " = '$val'";  
                    }

                    if ($count < count($ar))
                        $list_where .= ' and ';
                }
    }

    $query = "select"
        ." count(distinct session_id) as sessions_count"
        ." from weblog_accesses";

    if (strlen($list_tables) > 0)
        $query .= ", " . $list_tables;

    if (strlen($list_where) > 0)
        $query .= " where " . $list_where;

    //echo "<p>$query<hr>";

    $result = @mysql_query($query, $phpWebLogAnalyzer_db_handle)
        or phpWebLogAnalyzer_db_error();

    if ($row = @mysql_fetch_array($result, MYSQL_ASSOC))
        return $row[sessions_count];

    return false;
}

/*
 * phpWebLogAnalyzer_db_error()
 *
 * Database error handling function.
 */
function phpWebLogAnalyzer_db_error() {
    return phpWebLogAnalyzer_error("error during work with database server");
}


/*
 * phpWebLogAnalyzer_error()
 *
 * Error handling function.
 */
function phpWebLogAnalyzer_error($message = "Unknown error") {
    die ("<b>phpWebLogAnalyzer fatal failure</b>: " . $message 
            . "<br>Please contact webmaster or system administrator" 
            . " of this page with this problem.<br>");

    return false;
}

?>