#!/usr/bin/perl -T -w
#
# $Id: squid-access-from-mysql,v 1.1 2004/06/19 11:50:37 suter Exp $
# Copyright (C) 2004 Mark Suter <suter@humbug.org.au>
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.

use strict;
use DBI;
use CGI qw(-no_xhtml -nosticky -newstyle_urls);
use POSIX qw(strftime);

##
## Subs to make the parts of the search
##

sub where_user($) {
    $_ = $_[0];
    /^\s*$/ and return "";
    /^\s*(.+)\s*$/i or die "Unexpected error!\n";
    return 'user = "' . quotemeta($1) . '" and ';
}

sub where_ip($) {
    $_ = $_[0];
    /^\s*$/ and return "";
    /^\s*([0-9.]+)\s*$/i or die "Digits and periods only!\n";
    return 'ip = "' . quotemeta($1) . '" and ';
}

sub where_keyword_fulltext($) {
    $_ = $_[0];
    /^\s*$/ and return "";
    /^\s*(.+)\s*$/i or die "Unexpected error!\n";
    return 'match(uri) against ("' . quotemeta($1) . '") and ';
}

sub where_keyword_substr($) {
    $_ = $_[0];
    /^\s*$/ and return "";
    /^\s*(.+)\s*$/i or die "Unexpected error!\n";
    return '0 < locate("' . quotemeta($1) . '", uri) and ';
}

sub where_time($$) {
    $_ = $_[0] . " " . $_[1];
    /^\s*$/ and die "Date empty.\n";
    /^\s*(\d{4}-\d{1,2}-\d{1,2}) (\d{4}-\d{1,2}-\d{1,2})\s*$/ or die "Use YYYY-MM-DD.\n";
    return 'timestamp > "' . $1 . '" and timestamp < "' . $2 . '"';
}

sub where_order($) {
    $_ = $_[0];
    /^\s*$/ and die "Toggle empty!\n";
    /y/i and return 'order by user' ;
    return "";
}

sub where_limit($) {
    $_ = $_[0];
    /^\s*$/ and die "Limit empty.\n";
    /^\s*(\d+)\s*$/ or die "Limit not a number.\n";
    $1 == 0 and return "";
    return ' limit 0,' . $1;
}

##
## Plain text output?
##

sub text_output($) {
    $_ = $_[0];
    /^\s*$/ and die "Toggle empty!\n";
    /y/i and return 1;
    return 0;
}

##
## Main program
##

## Unbuffer STDOUT
$| = 1;

## Initialise CGI object
my $q = new CGI;

if ($q->param()) {

    ## Start either text/plain or text/html
    if (text_output $q->param('text')) {
	print $q->header(-type => 'text/plain', -expires => "+1d", -content_disposition => 'attachment; filename="results.txt"' );

    } else {
	print $q->header(-expires => "+1d"),
	    $q->start_html( -title => "Squid-Reports Search: Results",
		-author => 'suter@humbug.org.au', -style => '.hl { background-color: yellow; color: blue; }'),
	    $q->h1("Squid-Reports Search: Results"),
	    "<font size=\"-1\"><pre>\n";
    }

    ## Database stuff - plain text output only
    eval {

	## Connect to our database
	my $dbh = DBI->connect("DBI:mysql:database=squid", "squid", "secret", { RaiseError => 1, PrintError => 0, ShowErrorStatement => 1 });

	## Prepare our search
	my $query = "SELECT timestamp, user, ip, uri FROM logs WHERE ";
	$query .= where_user( $q->param('user') );
	$query .= where_ip( $q->param('ip') );
	$query .= where_keyword_fulltext( $q->param('keyword_fulltext') );
	$query .= where_keyword_substr( $q->param('keyword_substr') );
	$query .= where_time( $q->param('start'), $q->param('finish') );
	$query .= where_order( $q->param('sort') );
	$query .= where_limit( $q->param('limit') );
	my $sth = $dbh->prepare($query);

	## Perform our search
	$sth->execute();
	my @row = ();
	while (@row = $sth->fetchrow_array) {
	    print "@row\n";
	}
	print "\nEnd of the results\n";

	## Disconnect from the database.
	$dbh->disconnect();

    };
    print $@ if $@;

    ## Close off the text/html
    print "</pre></font>\n", $q->end_html, "\n" unless text_output( $q->param('text') );

} else {
    ## Print the search form
    print $q->header(-expires => "+0s"),
	$q->start_html( -title => "Squid-Reports Search: Data Entry", -author => 'suter@humbug.org.au'),
	$q->h1("Squid-Reports Search: Data Entry"),
	$q->startform(-method => "GET"),
	$q->table( { -summary=>"search form" }, $q->Tr([
	    $q->td([ "User: ", $q->textfield('user'), "Literal match" ]),
	    $q->td([ "IP address: ", $q->textfield('ip'), "Literal match" ]),
	    $q->td([ "Keyword: ", $q->textfield('keyword_substr'), "Literal match" ]),
	    $q->td([ "Keyword: ", $q->textfield('keyword_fulltext'), "MySQL fulltext match" ]),
	    $q->td([ "Start Date: ", $q->textfield('start',
		strftime("%Y-%m-%d", localtime(time-30*86400))), "YYYY-MM-DD" ]),
	    $q->td([ "Finish Date: ", $q->textfield('finish',
		strftime("%Y-%m-%d", localtime(time-86400))), "YYYY-MM-DD" ]),
	    $q->td([ "Sort by user: ", join(" ", $q->radio_group(-name => "sort",
		-values => ['Yes', 'No'], -default => 'No')), "Slower"]),
	    $q->td([ "Matches:", $q->textfield('limit', 100), "Use 0 for no limit" ]),
	    $q->td([ "Download results: ", join(" ", $q->radio_group(-name => "text",
		-values => ['Yes', 'No'], -default => 'No')), "Use this save output"]),
	])),
	$q->submit("Search"),
	$q->end_form,
	$q->end_html, "\n";
}