#!/usr/bin/perl
use DBI;
#use strict;

            
# ------------------------------
# My custom phone access script!
# dvb 1999 December 29
# using mysql, and stuff
#


$| = 1;

my $mode_add = 1;
my $mode_modify = 2;
my $mode_lookup = 3;
my $mode_list = 5;

my $gCGIMode = $ENV{"SERVER_SOFTWARE"};	# nonempty if a cgi.

# Extract CGI Variables
# ---------------------
# Operates on $ENV stuff,
# and returns associative array of name/value pairs.
sub GetCGIVariables
	{
	my %cgiVariables;
	my @pairs;
	my ($name,$value,$pair);

	@pairs = split ('&',$ENV{"QUERY_STRING"});
	foreach $pair (@pairs)
		{
		($name,$value) = split('=',$pair);
		# Un-Webify plus signs and %-encoding
		$value =~ tr/+/ /;
		$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;

		$cgiVariables{$name} = $value;
		}
	return %cgiVariables;
	}

# Prompt for input
# ----------------
# Two arguments, prompt, and default value
sub prompt
	{
	my ($prompt, $default) = @_;
	my $x;

	$prompt .= ":" if $prompt =~ /.*[a-zA-Z]/;
	print "$prompt [$default] ";
	$x = <STDIN>;
	chop($x);
	$x = $default unless $x;
	$x = "" if $x eq " ";
	return $x;
	}

# Prompt for input block
# ----------------------
# Two associative arrays as arguments:
#   list2{"key"} -> prompt string for that value
#   list3 -> array of keys in desired order
#   list1{"key"} -> current values
# return a modified list1.
sub promptBlock
	{
	my $prompts = shift;
	my $values = shift;
	my $keys = shift;
	my %results;

	my $widestPrompt;
	my $i;
	my $key;
	my $length;
	my $prompt;
	my @keys;

	if(!$keys)
		{
		@keys = keys(%$values);
		$keys = \@keys;
		}

	foreach $key (@$keys)
		{
		$length = length($$prompts{$key});
		$widestPrompt = $length if $widestPrompt < $length;
		}

	foreach $key (@$keys)
		{
		$prompt = $$prompts{$key};
		$prompt = $key if !$prompt;
		$length = length($prompt);
		while($length++ < $widestPrompt)
			{
			$prompt = " ".$prompt;
			}
		$$values{$key} = prompt($prompt,$$values{$key});
		}
	}

sub insertRow
	{
	my $dbh = shift;
	my $rowRef = shift;
	my $doReplace = shift;
	my $rv;
	my $count;
	my $select;
	my $key;

	if($doReplace)
		{
		$select = "REPLACE";
		}
	else
		{
		$select = "INSERT";
		}
	$select .= qq# INTO people (#;
	$count = 0;
	foreach $key (keys(%$rowRef))
		{
		$select .= "," if $count++;
		$select .= $key;
		}

    if($doReplace)
    {
        $select .= ",changed";
        $select .= ",added";
    }
    else
    {
        $select .= ",changed";
        $select .= ",added";
    }



	$select .= qq#) VALUES (#;
	$count = 0;
	foreach $key (keys(%$rowRef))
		{
		$select .= "," if $count++;
		$select .= qq#"$$rowRef{$key}"#;
		}

    if($doReplace)
    {
        $select .= ",NOW(),people.added";
    }
    else
    {
        $select .= ",NOW(),NOW()";
    }

	$select .= qq#);#;

	$rv = $dbh->do($select);
	return $rv;
	}

sub setLength	# (aString,newLength)
	{
	my $s = shift;
	my $l = shift;

	my $ls = length($s);

	if($ls < $l)
		{
		while($ls < $l)
			{
			$s .= " ";
			$ls++;
			}
		}
	elsif ($ls > $l)
		{
		$s = substr($s,0,$l-3) . "...";;
		}

	return $s;
	}

# Show one name
# -------------
# fromat and display one name
# showonename(\row hash,extended)
sub showOneName
	{
	my $row = shift;
	my $extended = shift;
	my ($f_fn,$f_mn,$f_cn,$f_wn,$f_em,$f_no);

	$f_fn = $$row{"name"};
	$f_mn = $$row{"main_phone"};
	$f_cn = $$row{"cell_phone"};
	$f_wn = $$row{"work_phone"};
	$f_em = $$row{"email"};
	$f_no = $$row{"notes"};

	$f_cn = "c".$f_cn if $f_cn;
	$f_wn = "w".$f_wn if $f_wn;

	if($extended)
		{
		my ($a1,$a2);

		$f_fn = setLength($f_fn,45);
		$a1 = $$row{"addr_1"};
		$a2 = $$row{"addr_2"};
		$a1 = setLength($a1,40);
		$a2 = setLength($a2,40);
		$f_mn = "m".$f_mn if $f_mn;
		#$f_em = setLength($f_em,27);

		print $f_fn,$f_em,"\n";
		print "     ",$a1,$f_mn,"\n";
		print "     ",$a2,$f_cn,"\n";
		print "     ",setLength(" ",40),$f_wn,"\n";
		print "     ",$f_no,"\n" if $f_no;
		printBar();
		return;
		}

	$f_fn = setLength($f_fn,18);
	$f_cn = $f_wn if (length($f_cn) <= 1);

	$f_mn = setLength($f_mn,12);
	$f_cn = setLength($f_cn,13);
	$f_em = setLength($f_em,27);

	print "$f_fn$f_mn $f_cn $f_em\n";
	}

sub printBar
	{
	if($gCGIMode)
		{
		print "<hr>\n";
		}
	else
		{
		print <<EOF;
------------------------------------------------------------------------
EOF
		}
	}

# promptRow
# ---------
# Takes a hashref from DBI, and then prompts
# for new values in every field. returns the
# modified hash.
sub promptRow
	{
# experimental prompt block
	my $key;
	my $row = shift;
	my %prompts;
	my %values;
	my $ok = "No";
	my @fields = ("name",
			"main_phone","cell_phone","work_phone",
			"email","addr_1","addr_2","category","notes");

	$prompts{"name"} = "Name";
	$prompts{"main_phone"} = "Main phone";
	$prompts{"cell_phone"} = "Cell phone";
	$prompts{"work_phone"} = "Work phone";
	$prompts{"email"} = "Email";
	$prompts{"addr_1"} = "Address (line 1)";
	$prompts{"addr_2"} = "Address (line 2)";

	if($row)
		{
		foreach $key (@fields)
			{
			$values{$key} = $$row{$key};
			}
		}

	while(1)
		{
		promptBlock(\%prompts,\%values,\@fields);
		$ok = prompt("All correct? ",$ok);
		last if ($ok =~ /[yY].*/);
		}

	return %values;
	}

# Look up name
# ------------
# arguments (data_connection, exact, name )
# and returns the sth for the caller to operate on.
sub lookupName
	{
	my $dbh = shift;
	my $exact = shift;

	my $sth;
	my @row;
	my $key;
	my $value;
	my @name;
	my $foundCount;
	my $select;
	my $count;
	my $x;
	@name = @_;

	@name = (" ") if(!@name);

	$select = qq(SELECT * FROM people WHERE );
	$count = 0;
	foreach $x (@name)
		{
		$x =~ tr/\./ /;
		$select .= " OR " if $count;
		$count++;

		if($exact)
			{
			$select .= qq(name = "$x" OR email = "$x" );
			}
		else
			{
			$select .= qq(notes LIKE "%$x%" OR name LIKE "%$x%" OR email LIKE "%$x%" );
			}
		}
	$select .= "ORDER BY name;";

	$sth = $dbh->prepare($select);
	$sth->execute if $sth;
	return $sth;
	}

# LookupAndPrintNames
# -------------------
# args: extended, dbh, exact, nameslist
sub lookupAndPrintNames
	{
	my $sth;
	my $row;
	my $count;
	my $doExtended = shift;

	$sth = lookupName(@_);
	$count = 0;
	while($row = $sth->fetchrow_hashref)
		{
		$count++;
		showOneName($row,$doExtended);
		}
	return $count;
	}

# modifyNames
# -----------
# args: dbh, exact, nameslist
sub modifyNames
	{
	my ($dbh,$doExact,@names) = @_;
	my $sth;
	my $row;
	my $count;
	my %values;

	$sth = lookupName(@_);
	while($row = $sth->fetchrow_hashref)
		{
		@names = (@names,$$row{"name"});
		}

	printBar;
	print "About to modify ",scalar(@names)," name(s):\n";
	foreach $row (@names)
		{
		print "    $row\n";
		}
	if(scalar(@names) > 3)
		{
		print "That's too many.\n";
		printBar;
		return;
		}


	printBar;
	$count = 0;
	$sth = lookupName(@_);
	while($row = $sth->fetchrow_hashref)
		{
		$count++;
		%values = promptRow($row);
		if($$row{"name"} ne $values{"name"})
			{
			$dbh->do(qq#DELETE FROM people#
					. qq# WHERE name = "$$row{"name"}";#);
			insertRow($dbh,\%values,0);
			}
		else
			{
			insertRow($dbh,\%values,1);
			}
		showOneName(\%values);
		}
	return $count;
	}

#------------------------
# Main

my $g_dbh = DBI->connect("DBI:mysql:phones:mysql.omino.com","phones","phones");

if($gCGIMode)
	{
	# Do it as a web-action
	my %cgiVariables = GetCGIVariables();
	print <<EOF;
Content-type: text/html

<body bgcolor=#ffffff>
<hr>
<blockquote>
<pre>
EOF
		if($cgiVariables{"name"})
			{
			lookupAndPrintNames(0,$g_dbh,0,split(" ",$cgiVariables{"name"}) );
			}
		print <<EOF;
</pre>
</blockquote>
<hr>
Look Up Another Name...
<form action="phones.cgi">
Name: <input type="text" name="name"><br>
<input type="submit">
</form>
EOF
	}
else
	{
	# Do it as a command line action
	my ($arg,$argC);
	my @args;
	my @names;
	my $doExact;
	my $doExtended;

	$argC = scalar @ARGV;

	my $mode = $mode_lookup;

	if($argC)
		{
		for(my $i = 0; $i < $argC; $i++)
			{
			$arg = @ARGV[$i];
			if($arg =~ /$\-a/)
				{
				$mode = $mode_add;
				}
			elsif($arg =~ /$\-l/ or $arg eq "all")
				{
				$mode = $mode_list;
				}
			elsif($arg =~ /$\-x/)
				{
				$doExtended = 1;
				}
			elsif($arg =~ /$\-e/)
				{
				$doExact = 1;
				}
			elsif($arg =~ /$\-m/)
				{
				$mode = $mode_modify;
				}
			else
				{
				@names = (@names,$arg);
				}
			}

		if($mode == $mode_add)
			{
			# A new database entry!
			my %values;
			my $key;

			%values = promptRow();
			foreach $key (keys(%values))
				{
				print "$key: $values{$key} \n";
				}
			insertRow($g_dbh,\%values);
			}

		if($mode == $mode_modify)
			{
			modifyNames($g_dbh,$doExact,@names);
			}

		if($mode == $mode_lookup)
			{
			printBar;
			my $foundCount = lookupAndPrintNames($doExtended,$g_dbh,$doExact,@names);
			print "No matches for @names.\n" if !$foundCount;
			printBar if (!$doExtended);
			}
		elsif($mode == $mode_list)
			{
			printBar;
			lookupAndPrintNames($doExtended,$g_dbh,0,"");
			printBar if !$doExtended;
			}
		}
	else
		{
		print <<EOF;
usage: p [options] [name[ more names]]
          -m modify existing entries
          -a add an entry
          -e exact match
          -x extended report
EOF
		}
	}

$g_dbh->disconnect;