#!/usr/local/bin/perl -w
##############################################################################
# samology_google_referrals_view_entries (SGRVE)               Version 0.0.3 #
# Copyright 2004 Sam Thompson   sam@samology.com                             #
# Created 01/03/04              Last Modified 01/03/04                       #
# Available at:                 http://www.samology.com/                     #
##############################################################################
# COPYRIGHT NOTICE                                                           #
# Copyright 2004 Sam Thompson       All Rights Reserved.                     #
#                                                                            #
# SGRVE may be used and modified free of charge by anyone so long as         #
# this copyright notice and the comments above remain intact.  By using this #
# code you agree to indemnify Sam Thompson from any liability that           #  
# might arise from it's use.                                                 #  
#                                                                            #
# Selling the code for this program without prior written consent is         #
# expressly forbidden.                                                       #
#                                                                            #
# Obtain permission before redistributing this software.  In all cases,      #
# copyright and header must remain intact.                                   #
#                                                                            #
# P.S.; Format for this copyright notice blatantly stolen from Matt Wright   #
# of www.scriptarchive.com.                                                  #
#                                                                            #
# P.P.S.; This is Sam's first Perl script, corrections to blatant/glaring    #
# security and/or other errors will be gladly tolerated, though possibly     #
# ignored...                                                                 #
##############################################################################

#Output to be understood by browser;
print "Content-type: text/html\n\n";
use strict;
#Includes (CGI and database);
use CGI qw(-no_xhtml :standard);
use DBI;
use URI::Escape;

#Variable declaration;
my ($search_engine, @date, $currentdate, $sqlstatement, $sth, $returntext, $referral_text, $beginning, $ending, @row);

#Initialize connection to MySQL data store;
my $dbh = DBI->connect('dbi:mysql:xxx:xxx','xxx','xxx');

#First, inspect browser referral string (can be blocked by client, but normally available) for keywords;
if (index($ENV{'HTTP_REFERER'},".google.") > 0)
{
    #Google search
    $search_engine = "Google";
}
elsif (index($ENV{'HTTP_REFERER'},"aolsearch.") > 0)
{
    #AOL search
    $search_engine = "AOL";
}
elsif (index($ENV{'HTTP_REFERER'},"search.yahoo.") > 0)
{
    #Yahoo search
    $search_engine = "Yahoo";
}
elsif (index($ENV{'HTTP_REFERER'},"search.msn.") > 0)
{
    #MSN search
    $search_engine = "MSN";
}
elsif (index($ENV{'HTTP_REFERER'},"search.netscape.") > 0)
{
    #Netscape search
    $search_engine = "Netscape";
}

#If referral is from a recognized search engine, add data to table;
if (length($search_engine) > 0)
{
    #A decent method to retrieve MySQL compliant formatted date;
    @date = reverse(localtime);
    $date[3]+=1900;
    $date[4]++;
    $currentdate = $date[3].substr("00".$date[4],-2).substr("00".$date[5],-2);
    
    #Insert the new record;
    $sqlstatement = "INSERT INTO google_referrals (search_engine, referral_text, date_added) VALUES ('".$search_engine."', '".$ENV{'HTTP_REFERER'}."', $currentdate);";
    $sth = $dbh->prepare($sqlstatement);
    $sth->execute ||
        die "Could not execute SQL statement ... maybe invalid?";
}

#Then, if the displaygoogle environment variable is set, return results;
if (param('displaygoogle') eq "true")
{
    #Prep and execute SQL SELECT statement, adjust "LIMIT" parameter to show additional / fewer records;
    $sqlstatement="SELECT referral_text, DATE_FORMAT(date_added, '%c/%d'), search_engine FROM google_referrals ORDER BY record_no DESC LIMIT 10";
    $sth = $dbh->prepare($sqlstatement);
    $sth->execute || 
        die "Could not execute SQL statement ... maybe invalid?";
    
    #Loop resultset array, returning html formatted strings appropriate for browser display;
    while (@row=$sth->fetchrow_array)
     {
        #Format referral string portion;
        $referral_text = $row[0];
        
        #Different search engines pass the query text differently;
        if ($row[2] eq "Google")
        {
            $beginning = index($referral_text,"q=") + 2;
        }
        elsif ($row[2] eq "Yahoo")
        {
            $beginning = index($referral_text,"p=") + 2;
        }
        elsif ($row[2] eq "Netscape")
        {
            $beginning = index($referral_text,"query=") + 6;
        }
        elsif ($row[2] eq "AOL")
        {
            $beginning = index($referral_text,"query=") + 6;
        }
        else
        {
            #Blank - assume it was a Google query (shouldn't happen)?;
            $beginning = index($referral_text,"q=") + 2;
        }
        
        if (index($referral_text,"&",$beginning)!=-1)
        {
            #Switches found after search text, return substring;
            $ending = index($referral_text,"&",$beginning)-$beginning;
            $returntext = substr($referral_text,$beginning,$ending);
        }
        else
        {
            #No switches after search text, return entire end of string;
            $returntext = substr($referral_text,$beginning);     
        }
        
        #Translate escape characters (if any);
        $returntext =~ s/%([0-9A-Fa-f]{2})/chr(hex($1))/eg;
        
        #Remove "+" between search terms to make it more legible;
        $returntext =~ s/\+/\ /g;
        
        #Print the date first...
        print "<b>",$row[1],"</b> (".$row[2]."): ";
        #Now print formatted search text with a link to the referring query;
        print "<a href=\"", $referral_text, "\" target=\"_blank\">", "'", $returntext, "'</a><br>","\n";
     }
}