#!/usr/bin/perl
#
# Asterisk(tm) Calling Card Platform
#
# Copyright (C) 2004, Digium, Inc.
#
# Mark Spencer <markster@digium.com
#
# This program is Free Software and is distributed under the
# terms of the GNU General Public License version 2 or later
# at your option.
#
# This program can be called from the web browser or from 
# another page with options to generate a card automatically.
# ie. http://127.0.0.1/cgi-bin/astcc-admin/astcc-admin.cgi?md=Auto&pn=500&em=root@localhost&iax=y&sip=n
# would generate a card with 500 pennies on it and send an
# email containing the info to root@localhost.  It would 
# also add a user to the iaxfriends database.

use CGI qw/:standard/;
use DBI;
use POSIX qw(strftime);
use POSIX qw(ceil floor);

print header;
my $copyright = "Asterisk&trade; Calling Card Manager by Mark Spencer, &copy;2004 Digium, Inc.";
@modes = ( "Home", "Calc_Charge", "Brands", "Cards", "Trunks", "Routes", "Configure", "Users_Configure", "IAX_Friends", "SIP_Friends" );
@languages =  ("en", "de", "fr", "es");
@techs = ( "SIP", "IAX2", "Zap", "Local" );
@incs = ("6", "60");
@yesno = ("YES", "NO"); 
my $dbh;

sub timestamp() {
	my $now = strftime "%Y%m%d%H%M%S", gmtime;
	return $now;
}

sub load_config() {
	open(CFG, "</var/lib/astcc/astcc-config.conf");
	while(<CFG>) {
		chomp;
		my ($var, $val) = split(/\s*\=\s*/);
		$config{$var} = $val;
	}
	close(CFG);
}

sub users_load_config() {
	open(CFG, "</var/lib/astcc/users-astcc-config.conf");
	while(<CFG>) {
		chomp;
		my ($var, $val) = split(/\s*\=\s*/);
		$users_config{$var} = $val;
	}
	close(CFG);
}

sub users_connect_db() {
	my $users_dsn = "DBI:mysql:database=$users_config{'dbname'};host=$users_config{'dbhost'}";
	$users_dbh->disconnect if $users_dbh;
	$users_dbh = DBI->connect($users_dsn, $users_config{'dbuser'}, $users_config{'dbpass'});
}


sub connect_db() {
	my $dsn = "DBI:mysql:database=$config{'dbname'};host=$config{'dbhost'}";
	$dbh->disconnect if $dbh;
	$dbh = DBI->connect($dsn, $config{'dbuser'}, $config{'dbpass'});
}

sub users_create_db() {
	my $users_drh = DBI->install_driver("mysql");
	return -1 unless $users_drh;
	return -1 unless $users_drh->func('createdb', $users_config{'dbname'}, $users_config{'dbhost'}, $users_config{'dbuser'}, 
								$users_config{'dbpass'}, 'admin');
	users_connect_db();
	return -1 unless $users_dbh;
	return -1 unless $users_dbh->do("CREATE TABLE sipfriends (name CHAR(40) NOT NULL PRIMARY KEY, username CHAR(40), secret CHAR(40) NOT NULL, context char(40) NOT NULL, ipaddr char(20) NOT NULL, port INTEGER(6) NOT NULL default '0', regseconds INTEGER(11))");
	return -1 unless $users_dbh->do("CREATE TABLE iaxfriends (name CHAR(40) PRIMARY KEY, secret CHAR(40), context CHAR(40), ipaddr CHAR(20), port INTEGER(6), regseconds INTEGER(11) NOT NULL)");
	return 0;
}

sub create_db() {
	my $drh = DBI->install_driver("mysql");
	return -1 unless $drh;
	return -1 unless $drh->func('createdb', $config{'dbname'}, $config{'dbhost'}, $config{'dbuser'}, 
								$config{'dbpass'}, 'admin');
	connect_db();
	return -1 unless $dbh;
	return -1 unless $dbh->do("CREATE TABLE brands (name CHAR(40) PRIMARY KEY, language CHAR(10), publishednum CHAR(40), did char(40), markup INTEGER, inc INTEGER, fee INTEGER, days INTEGER)");
	return -1 unless $dbh->do("CREATE TABLE cards (number CHAR(20) PRIMARY KEY, language CHAR(10), facevalue INTEGER, used INTEGER NOT NULL, inc INTEGER NOT NULL, markup INTEGER NOT NULL, creation TIMESTAMP, firstuse TIMESTAMP, expiration TIMESTAMP, inuse INTEGER, brand CHAR(40), nextfee INTEGER, pin INTEGER)");
	return -1 unless $dbh->do("CREATE TABLE trunks (name CHAR(40) PRIMARY KEY, tech CHAR(10), path CHAR(40) NOT NULL)");
	return -1 unless $dbh->do("CREATE TABLE routes (pattern CHAR(40) PRIMARY KEY, comment CHAR(80), trunks CHAR(80), connectcost INTEGER NOT NULL, includedseconds INTEGER NOT NULL, cost INTEGER NOT NULL)");
	return -1 unless $dbh->do("CREATE TABLE cdrs (cardnum CHAR(40), callerid char(80), callednum CHAR(80), trunk CHAR(40), disposition CHAR(20), billseconds INTEGER, billcost INTEGER)");
	return 0;
}

sub update_db() {
		$dbh->do("ALTER TABLE cdrs ADD COLUMN callstart CHAR(24)");
      	$dbh->do("ALTER TABLE cards ADD COLUMN pin INTEGER");
      	$dbh->do("ALTER TABLE cards ADD COLUMN expiration TIMESTAMP");
      	$dbh->do("ALTER TABLE cards ADD COLUMN creation TIMESTAMP");
      	$dbh->do("ALTER TABLE cards ADD COLUMN firstuse TIMESTAMP");            
      	$dbh->do("ALTER TABLE cards ADD COLUMN nextfee INTEGER");
      	$dbh->do("ALTER TABLE cards ADD COLUMN brand CHAR(40)");
      	$dbh->do("ALTER TABLE cards ADD COLUMN pin INTEGER");
	return 0;
}

sub save_config() {
	my $tmp;
	open(CFG, ">/var/lib/astcc/astcc-config.conf");
	print CFG ";\n; Automatically created by astcc-admin.cgi.\n;\n";
	foreach $tmp (keys %config) {
		print CFG "$tmp = $config{$tmp}\n";
	}
	close(CFG);
}

sub users_save_config() {
	my $tmp;
	open(CFG, ">/var/lib/astcc/users-astcc-config.conf");
	print CFG ";\n; Automatically created by astcc-admin.cgi.\n;\n";
	foreach $tmp (keys %users_config) {
		print CFG "$tmp = $users_config{$tmp}\n";
	}
	close(CFG);
}

sub build_menu() {
	my ($selected) = @_;
	my $body;
	my $tmp;
	my $bgcolor, $fgcolor;
	$body = "<table bgcolor=#777777 cellpadding=4 width=100>\n";
	foreach $tmp (@modes) {
		if ($tmp eq $selected) {
			if (($tmp eq "Configure") && !$dbh) {
				$bgcolor = "#ff4400";
			} else {
				$bgcolor = "#ff8800";
			}
			$fgcolor = "#ffffff";
		} else {
			if (($tmp eq "Configure") && !$dbh) {
				$bgcolor = "#cc0000";
			} else {
				$bgcolor = "#ccccff";
			}
			$fgcolor = "#999999";
		}
		$body .= "<tr><td bgcolor=\"$bgcolor\">" .
			     "<font face=verdana,helvetica color=\"$fgcolor\">" .
				 "&nbsp;&nbsp;<a href=\"?mode=$tmp\">$tmp</a>" .
				 "</td></tr>\n";
	}
	$body .= "</table>\n";
	return $body;
}

sub build_home() {
	return "<table width=400><tr><td>Welcome to the Asterisk&trade; Calling Card Admin.  Please select " .
	"a function from the menu on the left.</td></tr></table>"
}

sub list_brands() {
	my $sth;
	my @brandlist;
	my $row;
	$sth = $dbh->prepare("SELECT name FROM brands");
	$sth->execute;
	while($row = $sth->fetchrow_hashref) {
		push @brandlist, $row->{name};
	}
	$sth->finish;
	if ($#brandlist > -1) {
		return popup_menu(-name => "brand", -values => \@brandlist);
	}
	return "";
}

sub list_cards() {
	my $sth;
	my @cardlist;
	my $row;
	$sth = $dbh->prepare("SELECT number FROM cards");
	$sth->execute;
	while($row = $sth->fetchrow_hashref) {
		push @cardlist, $row->{number};
	}
	$sth->finish;
	return @cardlist;
}

sub list_trunks() {
	my $sth;
	my @trunklist;
	my $row;
	my ($name, $def) = @_;
	$sth = $dbh->prepare("SELECT name FROM trunks");
	$sth->execute;
	while($row = $sth->fetchrow_hashref) {
		push @trunklist, $row->{name};
	}
	$sth->finish;
	if ($#trunklist > -1) {
		unshift(@trunklist, "<none>");
		return popup_menu(-name => "$name", -values => \@trunklist, -default => $def);
	}
	return "";
}

sub count_cards() {
	my ($clause) = @_;
	my $row;
	my $res;
	$sth = $dbh->prepare("SELECT COUNT(*) FROM cards $clause");
	$sth->execute;
	$row = $sth->fetchrow_hashref;
	$res = $row->{"COUNT(*)"};
	$sth->finish;
	return $res;
}

sub isunique() {
	my ($number) = @_;
	my $clause = "WHERE number = " . $dbh->quote($number);
	my $count = &count_cards($clause);
	return 1 if $count == "0";
	return 0;
}

sub findunique() {
	my $number;
	for (;;) {
		$number = int(rand() * 9000 + 1000) . int(rand() * 9000 + 1000) . int(rand() * 9000 + 1000) . int(rand() * 9000 + 1000) . int(rand() * 9000 + 1000);
		if ($config{'startingdigit'} ne "" && $config{'startingdigit'} ne "0") {
			$startingdigit = substr($number, 0, 1);	
			if ($startingdigit == $config{'startingdigit'}) {
				$number = substr($number, 0, $config{'cardlength'});			
				return $number if (&isunique($number));
			}
		} else	{
		$number = substr($number, 0, $config{'cardlength'});
		return $number if (&isunique($number));
		}
	}
}

sub addcard() {
	my ($number, $language, $seconds, $inc, $markup, $brand, $nextfee) = @_;
	my $pin = &findunique;
	my $pin = substr($pin, 0, $config{'pinlength'});
	my $tmp = "INSERT INTO cards (number,language,inc,facevalue,markup,used,brand,nextfee,pin) VALUES (" .
		$dbh->quote($number) . ", " . $dbh->quote($language) . ", " . $dbh->quote($inc) . ", " . $dbh->quote($seconds) .
		", " . $dbh->quote($markup) . ",0,". $dbh->quote($brand) ."," . $dbh->quote($nextfee) .
		", " . $dbh->quote($pin) . ")";
	$dbh->do($tmp) || print "$tmp failed";
}

sub generatecards() {
	my ($count, $pennies, $brand, $customnum) = @_;
	my $row;
	my $x;
	my $number;
	my $status = "";
	my ($language, $inc, $markup);
	# Retrieve brand specifications
	my $sth = $dbh->prepare("SELECT * FROM brands WHERE name = " . $dbh->quote($brand));
	$sth->execute;
	if ($config{'email'} eq "YES") {
		open (EMAIL, "| $config{mailprog} $config{emailadd} -s 'New Card(s) Created'") ||
			die "Error - could not write to $config{mailprog}\n";
		print EMAIL "You have added $count $brand card(s) in the amount of $pennies cents. \n\n"; 
	}
	if ($row = $sth->fetchrow_hashref) {
		($language, $inc, $markup, $nextfee) = ($row->{language}, $row->{inc}, $row->{markup}, $row->{days});
	}
	$sth->finish;
	# add cards -- face value is in 1/100 of a penny
	for ($x=0; $x < $count; $x++) {
		if ($customnum ne '') {
			$number = $customnum;
		} else {
			$number = &findunique;
		}
		&addcard($number, $language, $pennies * 100, $inc, $markup, $brand, $nextfee);
		if ($config{'email'} eq "YES") {
			print EMAIL "Number: $number Pin: $pin \n";
		}
		$status .= "Number: $number Pin: $pin<br>";
	}
	if ($config{'email'} eq "YES") {
		print EMAIL ".\n";
		close (EMAIL);
	}
	return $status;
}

sub users_generatecards() {
	my ($pennies, $users_email, $iax, $sip) = @_;
	my $brand = $users_config{brand};
#	my $pennies = $auto_pennies;
	my $count = 1;
	my $row;
	my $x;
	my $number;
	my $status = "";
	my ($language, $inc, $markup);
	# Retrieve brand specifications
	my $sth = $dbh->prepare("SELECT * FROM brands WHERE name = " . $dbh->quote($brand));
	$sth->execute;
	if ($users_config{'email'} eq "YES") {
		open (EMAIL_ADMIN, "| $users_config{mailprog} $users_config{emailadd} -s 'New Cards Created'") ||
			die "Error - could not write to $users_config{mailprog}\n";
		print EMAIL_ADMIN "You have added $count $brand cards in the amount of $auto_pennies cents. \n\n"; 
	}
	open (USERS_EMAIL, "| $users_config{mailprog} $users_email -s 'Your Account Has Been Added'") ||
			die "Error - could not write to $users_config{mailprog}\n";
	print USERS_EMAIL "Thank you for your patronage.  Your account has been added\n";
	if ($row = $sth->fetchrow_hashref) {
		($language, $inc, $markup) = ($row->{language}, $row->{inc}, $row->{markup});
	}
	$sth->finish;
	# add cards -- face value is in 1/100 of a penny
#	for ($x=0; $x < $count; $x++) {
		$number = &findunique;
		&addcard($number, $language, $pennies * 100, $inc, $markup);
		$secret = &findunique;
		if ($users_config{'email'} eq "YES") {
			print EMAIL_ADMIN "Account Number: $number \n";
			print EMAIL_ADMIN "Password: $secret \n";
			print EMAIL_ADMIN "Balance (Pennies): $pennies \n";
			print EMAIL_ADMIN "Pin: $pin \n";
		}
		print USERS_EMAIL "Account Number: $number \n";
		print USERS_EMAIL "Password: $secret \n";
		print USERS_EMAIL "Balance (cents): $pennies \n";
		print USERS_EMAIL "Pin: $pin \n";
		if ($sip eq "y" ) {
			&add_sip_user($number, $secret);
		}
		if ($iax eq "y" ) {	
			&add_iax_user($number, $secret);
		}	
#	}
	
	if ($users_config{'email'} eq "YES") {
		print EMAIL_ADMIN ".\n";
		close (EMAIL_ADMIN);
	}
	
	print USERS_EMAIL ".\n";
	close (USERS_EMAIL);
	$date = localtime();
	print "Your account: $number has been created.  ";
	print "Your password is $secret.  ";
	print "Your accounts has $pennies cents on it.  ";
	print "You will receive an email containing this ";
	print "in the next few minutes.";
}


sub getcard() {	my ($cardno) = @_;
	my $res;
	$sth = $dbh->prepare("SELECT * FROM cards WHERE number=" . $dbh->quote($cardno));
	$sth->execute;
	$res = $sth->fetchrow_hashref;
	$sth->finish;
	return $res;
}

sub build_cards() {
	my $total, $used;
	my $pennies, $count, $brand;
	return "Cannot edit cards until database is configured" unless $dbh;
	my $brands = list_brands();
	return "Please define at least one brand before creating cards" unless $brands;
	if (param('action') eq "Generate...") {
		$count = param('count');
		$pennies = param('pennies');
		$brand = param('brand');
		if ($count < 1) {
			$status = "Can't generate fewer than one card\n";
		} elsif ($pennies < 1) {
			$status = "Can't generate cards of fewer than one penny\n";
		} else {
			$status = &generatecards($count, $pennies, $brand);
		}
	}elsif (param('action') eq "Generate_Custom...") {
		my $count = 1;
		my $pennies = param('custom_pennies');
		my $brand = param('brand');
		my $cardnumber = param('customnum');
		if ($pennies < 1) {
			$status = "Can't generate cards of fewer than one penny\n";
		} else {
			$status = &generatecards($count, $pennies, $brand, $cardnumber);
		}		
	} elsif (param('action') eq "Drop...") {
		if ($dbh->do("DELETE FROM cards WHERE number = " . $dbh->quote(param('number')))) {
			$status = "Dropped card " . param('number') . "'.";
		} else {
 			$status = "Unable to drop card ". param('number') . "'.";
		}
	} elsif (param('action') eq "Refill...") {
		$cardinfo = &getcard(param('refillnum'));
		$number = $cardinfo->{number};
		$newfacevalue = $cardinfo->{facevalue} + (param('refillpennies') * 100);
		if ($dbh->do("UPDATE cards SET facevalue=" . $dbh->quote($newfacevalue) . " WHERE number=" . $dbh->quote($cardinfo->{number}))) { 
			$status = "Refilled card " . param('refillnum') . "'.";
		} else {
 			$status = "Unable to Refill card". param('refillnum') . "'.";
		}
	} elsif (param('action') eq "Reset...") {
		$number = param('resetnum');
		$newstatus = 0;
		if ($dbh->do("UPDATE cards SET inuse=" . $dbh->quote($newstatus) . " WHERE number=" . $dbh->quote($number))) { 
			$status = "Reset card " . param('resetnum') . "'.";
		} else {
 			$status = "Unable to Reset card ". param('resetnum') . "'.";
		}
	}
	$total = &count_cards();
	$used = &count_cards("WHERE firstuse > 0");
	$status = "<i>$status</i><br>" . "<font color=#777777>$total cards outstanding, $used of which have been used</font>";
	$body = "<table><tr><td>" . start_form . "$status</td></tr>";
	$body .= "<tr><td>Create&nbsp;&nbsp;" . hidden(-name => "mode", -value => "Cards") .
		textfield(-name => "count", -size => 8) . "&nbsp;" . $brands . "&nbsp;&nbsp;cards of&nbsp;&nbsp;" .
		textfield(-name => "pennies", -size => 8) . "&nbsp;&nbsp;pennies&nbsp;&nbsp;" .
		submit(-name => "action", -value => "Generate...") . "</td></tr>";
	$body .= "<tr><td>Create Custom Numbered " . hidden(-name => "mode", -value => "Cards") .
		textfield(-name => "customnum", -size => 20) . "&nbsp;" . $brands . "&nbsp;&nbsp;cards of&nbsp;&nbsp;" .
		textfield(-name => "custom_pennies", -size => 8) . "&nbsp;&nbsp;pennies&nbsp;&nbsp;" .
		submit(-name => "action", -value => "Generate_Custom...") . "</td></tr>";
	$body .= "<tr><td>Add Money to Card&nbsp;&nbsp;" . hidden(-name => "mode", -value => "Cards") .
		textfield(-name => "refillnum", -size => 20) . "&nbsp;&nbsp;In the Amount of &nbsp;&nbsp;" .
		textfield(-name => "refillpennies", -size => 8) . "&nbsp;&nbsp;pennies&nbsp;&nbsp;" .
		submit(-name => "action", -value => "Refill...") . "</td></tr>";	
	$body .= "<tr><td>Get information on card&nbsp;&nbsp;" . hidden(-name => "mode", -value => "Cards") .
		textfield(-name => "cardnum", -size => 20)  . "&nbsp;&nbsp;&nbsp;&nbsp;" .
		submit(-name => "action", -value => "Information...") . "</td></tr>";
	$body .= "<tr><td>Delete card&nbsp;&nbsp;" . hidden(-name => "mode", -value => "Cards") .
		textfield(-name => "number", -size => 20)  . "&nbsp;&nbsp;&nbsp;&nbsp;" .
		submit(-name => "action", -value => "Drop...") . "</td></tr>";	
	$body .= "<tr><td>Reset in use info on card&nbsp;&nbsp;" . hidden(-name => "mode", -value => "Cards") .
		textfield(-name => "resetnum", -size => 20)  . "&nbsp;&nbsp;&nbsp;&nbsp;" .
		submit(-name => "action", -value => "Reset...") . "</td></tr>";	
	$body .= "<tr><td>List Cards&nbsp;&nbsp;" . hidden(-name => "mode", -value => "Cards") .
		submit(-name => "action", -value => "List_Cards...") . "</td></tr>";
	$body .= "</table>";
	$cardnum = param('cardnum');
	if ((param('action') eq "Information...") && length(param('cardnum'))) {
		my $cardinfo;
		my $status;
		$cardinfo = &getcard(param('cardnum'));
		if ($cardinfo->{'number'}) {
			$status = "Card&nbsp;&nbsp;</i><b>" . param('cardnum') . "&nbsp;&nbsp;</b><i> has used&nbsp;&nbsp;" .
			"</i><b>$cardinfo->{used}</b></i>" . "&nbsp;&nbsp;of&nbsp;&nbsp;" .
			"</i><b>$cardinfo->{facevalue}</b></i>&nbsp;&nbsp;units\n";
		} else {
			$status = "No such card number '" . param('cardnum') . "' found!\n";
		}
		$body .= "<table>";
		$body .= "<tr bgcolor=ffffff><tdcolspan=4><i>" . $status . "</i></td></tr>\n";
		if ($cardinfo->{'number'}) {
			$body .= "<tr bgcolor=ccccff><td>Caller*ID</td><td>Called Number</td><td>Trunk</td><td>Disposition</td><td>Billable Seconds</td><td>Billed Cost</td></tr>\n";
			my $sth = $dbh->prepare("SELECT * FROM cdrs WHERE cardnum=" . $dbh->quote($cardinfo->{'number'}));
			my $res;
			my $count;
			my $callerid;
			$sth->execute;
			while(($res = $sth->fetchrow_hashref)) {
				$callerid = $res->{callerid};
				$callerid = "&lt;unknown&gt;" unless $callerid;
				$count++;
				if (!($count % 2)) {
					$color = "#ccffcc";
				} else {
					$color = "#ffffcc";
				}
				$body .= "<tr bgcolor=$color><td>$callerid</td><td>$res->{callednum}</td><td>$res->{trunk}</td>" .
					"<td>$res->{disposition}</td><td>$res->{billseconds}</td><td>$res->{billcost}</td></tr>";
			}
		}
		$body .= "</table>";
	} elsif (param('action') eq "List_Cards...") {
		my $cardinfo;
		my $status;
		@cardlist = &list_cards();
		$body .= "<table>";
		$body .= "<tr bgcolor=ffffff><tdcolspan=4><i>" . $status . "</i></td></tr>\n";
		$body .= "<tr bgcolor=ccccff><td>Card Number</td><td>Brand</td><td>Amount Used</td><td>Face Value</td><td>Markup</td><td>Creation</td><td>First Use</td><td>Expiration</td><td>In Use</td><td>Pin</td></tr>\n";
		foreach (@cardlist) {
			$cardnum = $_;
			$cardinfo = &getcard($cardnum);
			if ($cardinfo->{'number'}) {
				$count++;
					if (!($count % 2)) {
					$color = "#ccffcc";
				} else {
					$color = "#ffffcc";
				}

			$body .= "<tr bgcolor=$color><td>$cardnum</td><td>$cardinfo->{brand}</td><td>$cardinfo->{used}</td><td>$cardinfo->{facevalue}</td><td>$cardinfo->{markup}</td><td>$cardinfo->{creation}</td><td>$cardinfo->{firstuse}</td><td>$cardinfo->{expiration}</td><td>$cardinfo->{inuse}</td><td>$cardinfo->{pin}"; 
			$body .= "</td></tr>\n";
			}
		}
		$body .= "</table>";
	} 
	return $body;
}

sub build_brands() {
	my $sth;
	my $count;
	my $color;
	my $tmp;
	my $editing;
	my $creating;
	my $status = "&nbsp;";
	return "Cannot edit brands until database is configured" unless $dbh;
	#
	# Find what we're editing if anything
	#
	if (param("action") eq "Yes, drop it") {
		if ($dbh->do("DELETE FROM brands WHERE name = " . $dbh->quote(param('dropitem')))) {
			$status = "Dropped brand '" . param('dropitem') . "'.";
		} else {
			$status = "Unable to drop brand '" . param('dropitem') . "'.";
		}
	}
	for ($x=1;length(param("item$x"));$x++) {
		if (param("action$x") eq "Edit...") {
			$editing = param("item$x");
		} elsif (param("action$x") eq "Add...") {
			$editing = param("item$x");
			$creating = "yes";
			$status = "Creating new entry $x";
		} elsif (param("action$x") eq "Save...") {
			if ($dbh->do("UPDATE brands SET name=" . $dbh->quote(param("newname$x")) .
				" WHERE name=" . $dbh->quote(param("item$x")))) {
				$tmp = "REPLACE INTO brands (name,language,inc,publishednum,did,markup,days,fee) VALUES (" .
					$dbh->quote(param("newname$x")) . ", " . $dbh->quote(param("newlang$x")) .
					", " . $dbh->quote(param("newinc$x")) . 
					", " . $dbh->quote(param("newpub$x")) . ", " . $dbh->quote(param("newdid$x")) . 
					", " . $dbh->quote(param("newmarkup$x")) . 
					", " . $dbh->quote(param("newdays$x")) . 
					", " . $dbh->quote(param("newfees$x")) . 
					")";
				if ($dbh->do($tmp)) {
					$status = "Brand '" . param("newname$x") . "' has been updated";
				} else {
					$status = "Brand '" . param("newname$x") . "' FAILED to update ($tmp)!";
				}
			} else {
				$status = "Name is already in use!";
			}
		} elsif (param("action$x") eq "Create...") {
				$tmp = "INSERT INTO brands (name,language,inc,publishednum,did,markup,days,fee) VALUES (" .
					$dbh->quote(param("newname$x")) . ", " . $dbh->quote(param("newlang$x")) .
					", " . $dbh->quote(param("newinc$x")) .
					", " . $dbh->quote(param("newpub$x")) . ", " . $dbh->quote(param("newdid$x")) . 
					", " . $dbh->quote(param("newmarkup$x")) . 
					", " . $dbh->quote(param("newdays$x")) . 
					", " . $dbh->quote(param("newfees$x")) . 
					")";
				if ($dbh->do($tmp)) {
					$status = "Brand '" . param("newname$x") . "' has been created";
				} else {
					$status = "Brand '" . param("newname$x") . "' FAILED to create ($tmp)!";
				}
		} elsif (param("action$x") eq "Drop...") {
			$status = "Are you sure you want to delete the brand '" .
				param("item$x") . "'?<br>" . hidden(-name => "dropitem", -value => param("item$x")) .
				submit(-name => "action", -value => "Yes, drop it") . "&nbsp;&nbsp;&nbsp;" .
				submit(-name => "action", -value => "Cancel");
			$editing = "dropping something";
			
		}
	}
	$sth = $dbh->prepare('SELECT * FROM brands');
	$sth->execute || return "Something is wrong with the brands database\n";
	$body = "<table>" .
		"<tr bgcolor=ffffff><tdcolspan=7>" . start_form . "<i>$status</i></td></tr>\n" .
		"<tr bgcolor=ccccff><td>" . hidden(-name => 'mode', -value => 'Brands') . "Brand Name</td><td>Language</td><td>Published Number</td><td>DID</td><td>Inc</td><td>Service Fee (cents)</td><td>Service Fee Days</td><td colspan=2>Markup (in 1/100 of 1%)</td></tr>\n";
	while($row = $sth->fetchrow_hashref) {
		$count++;
		if (!($count % 2)) {
			$color = "#ccffcc";
		} else {
			$color = "#ffffcc";
		}
		Delete("newname$count");
		Delete("item$count");
		if ($editing eq $row->{name}) {
			$body .= "<tr bgcolor=$color><td>" . hidden(-name => "item$count", -value => $row->{name}) .
				 textfield(-name => "newname$count", -size=> 10, -default => $row->{name}) . "</td><td>" .
		         popup_menu(-name => "newlang$count", -values => \@languages, -default=>$row->{language}) . 
				 "</td><td>" . 
				 textfield(-name => "newpub$count", -size => 15, -default => $row->{publishednum}) . "</td><td>" .
				 textfield(-name => "newdid$count", -size => 10, -default => $row->{did}) . 
				 "</td><td>" . popup_menu(-name => "newinc$count", -values => \@incs, -default => $row->{inc}) . "</td><td>" .
				 textfield(-name => "newfees$count", -size => 2, -default => $row->{fee}) . "</td><td>" .
				 textfield(-name => "newdays$count", -size => 2, -default => $row->{days}) . "</td><td>" .
				 textfield(-name => "newmarkup$count", -size => 5, -default => $row->{markup}) . "</td>";
				 $body .= "<td bgcolor=white>" . submit(-name => "action$count", -value => 'Save...') .
				 submit(-name => "action$count", -value => 'Cancel...'); 
				$body .= "</td></tr>";
		} else {
			$body .= "<tr bgcolor=$color><td>" . hidden(-name => "item$count", -value => $row->{name}) .
				 "$row->{name}</td>" .
		         "<td>$row->{language}</td><td>$row->{publishednum}</td>" .
				 "<td>$row->{did}</td><td>$row->{inc}</td><td>$row->{fee}</td><td>$row->{days}</td><td>$row->{markup}</td>" .
				 "<td bgcolor=white>";
			if (!$editing) {
				$body .= submit(-name => "action$count", -value => 'Edit...') .
				 submit(-name => "action$count", -value => 'Drop...');
			} else {
				$body .= "&nbsp;";
			}
			$body .= "</td></tr>\n";
		}
	}
	$sth->finish;
	$count++;
	if ($creating) {
			$body .= "<tr bgcolor=$color><td>" . hidden(-name => "item$count", -value => $row->{name}) .
				 textfield(-name => "newname$count", -size=> 10) . "</td><td>" .
		         popup_menu(-name => "newlang$count", -values => \@languages) . 
				 "</td><td>" . 
				 textfield(-name => "newpub$count", -size => 15) . "</td><td>" .
				 textfield(-name => "newdid$count", -size => 10) . 
				 "</td><td>" . popup_menu(-name => "newinc$count", -values => \@incs) . "</td><td>" .
				 textfield(-name => "newfees$count", -size => 2) . "</td><td>" .
				 textfield(-name => "newdays$count", -size => 2) . "</td><td>" .
				 textfield(-name => "newmarkup$count", -size => 5) . "</td>";
				 $body .= "<td bgcolor=white>" . submit(-name => "action$count", -value => 'Create...') .
				 submit(-name => "action$count", -value => 'Cancel...');
				$body .= "</td></tr>";
	}
	if (!$editing) {
		$body .=
			"<tr bgcolor=ffffff><td colspan=7><hr></td></tr>\n";
		$body .=
			"<tr bgcolor=ffffff><td colspan=6>&nbsp;" . hidden(-name => "item$count", -value => "new") . 
			"</td><td align=right>" .
			submit(-name => "action$count", -value => "Add...") . "</td></tr>\n";
	}
	$body .= "</table>\n";
	return $body;
}

sub build_trunks() {
	my $sth;
	my $count;
	my $color;
	my $tmp;
	my $editing;
	my $creating;
	my $status = "&nbsp;";
	return "Cannot edit trunks until database is configured" unless $dbh;
	#
	# Find what we're editing if anything
	#
	if (param("action") eq "Yes, drop it") {
		if ($dbh->do("DELETE FROM trunks WHERE name = " . $dbh->quote(param('dropitem')))) {
			$status = "Dropped trunk '" . param('dropitem') . "'.";
		} else {
			$status = "Unable to drop trunk '" . param('dropitem') . "'.";
		}
	}
	for ($x=1;length(param("item$x"));$x++) {
		if (param("action$x") eq "Edit...") {
			$editing = param("item$x");
		} elsif (param("action$x") eq "Add...") {
			$editing = param("item$x");
			$creating = "yes";
			$status = "Creating new entry $x";
		} elsif (param("action$x") eq "Save...") {
			if ($dbh->do("UPDATE trunks SET name=" . $dbh->quote(param("newname$x")) .
				" WHERE name=" . $dbh->quote(param("item$x")))) {
				$tmp = "REPLACE INTO trunks (name,tech,path) VALUES (" .
					$dbh->quote(param("newname$x")) . ", " . $dbh->quote(param("newtech$x")) .
					", " . $dbh->quote(param("newpath$x")) . ")";
				if ($dbh->do($tmp)) {
					$status = "Trunk '" . param("newname$x") . "' has been updated";
				} else {
					$status = "Trunk '" . param("newname$x") . "' FAILED to update ($tmp)!";
				}
			} else {
				$status = "Name is already in use!";
			}
		} elsif (param("action$x") eq "Create...") {
				$tmp = "INSERT INTO trunks (name,tech,path) VALUES (" .
					$dbh->quote(param("newname$x")) . ", " . $dbh->quote(param("newtech$x")) .
					", " . $dbh->quote(param("newpath$x")) . ")";
				if ($dbh->do($tmp)) {
					$status = "Trunk '" . param("newname$x") . "' has been created";
				} else {
					$status = "Trunk '" . param("newname$x") . "' FAILED to create ($tmp)!";
				}
		} elsif (param("action$x") eq "Drop...") {
			$status = "Are you sure you want to delete the trunk '" .
				param("item$x") . "' and all routes that use it?<br>" . hidden(-name => "dropitem", -value => param("item$x")) .
				submit(-name => "action", -value => "Yes, drop it") . "&nbsp;&nbsp;&nbsp;" .
				submit(-name => "action", -value => "Cancel");
			$editing = "dropping something";
		}
	}
	$sth = $dbh->prepare('SELECT * FROM trunks');
	$sth->execute || return "Something is wrong with the trunks database\n";
	$body = "<table>" .
		"<tr bgcolor=ffffff><tdcolspan=4>" . start_form . "<i>$status</i></td></tr>\n" .
		"<tr bgcolor=ccccff><td>" . hidden(-name => 'mode', -value => 'Trunks') . "Trunk Name</td><td>Technology</td><td colspan=2>Peer/Trunk</td></tr>\n";
	while($row = $sth->fetchrow_hashref) {
		$count++;
		Delete("newname$count");
		Delete("item$count");
		if (!($count % 2)) {
			$color = "#ccffcc";
		} else {
			$color = "#ffffcc";
		}
		if ($editing eq $row->{name}) {
			$body .= "<tr bgcolor=$color><td>" . hidden(-name => "item$count", -value => $row->{name}) .
				 textfield(-name => "newname$count", -size=> 10, -default => $row->{name}) . "</td><td>" .
		         popup_menu(-name => "newtech$count", -values => \@techs, -default=>$row->{tech}) . 
				 "</td><td>" . 
				 textfield(-name => "newpath$count", -size => 15, -default => $row->{path}) . "</td>";
				 $body .= "<td bgcolor=white>" . submit(-name => "action$count", -value => 'Save...') .
				 submit(-name => "action$count", -value => 'Cancel...'); 
				$body .= "</td></tr>";
		} else {
			$body .= "<tr bgcolor=$color><td>" . hidden(-name => "item$count", -value => $row->{name}) .
				 "$row->{name}</td>" .
		         "<td>$row->{tech}</td><td>$row->{path}</td>" .
				 "<td bgcolor=white>";
			if (!$editing) {
				$body .= submit(-name => "action$count", -value => 'Edit...') .
				 submit(-name => "action$count", -value => 'Drop...');
			} else {
				$body .= "&nbsp;";
			}
			$body .= "</td></tr>\n";
		}
	}
	$sth->finish;
	$count++;
	if ($creating) {
			$body .= "<tr bgcolor=$color><td>" . hidden(-name => "item$count", -value => $row->{name}) .
				 textfield(-name => "newname$count", -size=> 10) . "</td><td>" .
		         popup_menu(-name => "newtech$count", -values => \@techs) . 
				 "</td><td>" . 
				 textfield(-name => "newpath$count", -size => 15) . "</td>";
				 $body .= "<td bgcolor=white>" . submit(-name => "action$count", -value => 'Create...') .
				 submit(-name => "action$count", -value => 'Cancel...');
				$body .= "</td></tr>";
	}
	if (!$editing) {
		$body .=
			"<tr bgcolor=ffffff><td colspan=4><hr></td></tr>\n";
		$body .=
			"<tr bgcolor=ffffff><td colspan=3>&nbsp;" . hidden(-name => "item$count", -value => "new") . 
			"</td><td align=right>" .
			submit(-name => "action$count", -value => "Add...") . "</td></tr>\n";
	}
	$body .= "</table>\n";
	return $body;
}

sub find_newtrunks() {
	my ($base) = @_;
	my $x;
	my $res = "";
	my (@trunks);
	for ($x=1;length(param("$base-${x}"));$x++) {
		push @trunks, param("$base-${x}") if param("$base-${x}") ne "<none>";
	}
	return join(':',@trunks);
}

sub build_routes() {
	my $sth;
	my $count;
	my $color;
	my $tmp;
	my $editing;
	my $creating;
	my $trunks;
	my $newtrunks;
	my $status = "&nbsp;";
	my (@oldtrunks);
	return "Cannot edit routes until database is configured" unless $dbh;
	my $trunks = list_trunks();
	return "Please define at least one trunk before creating routes" unless $trunks;
	#
	# Find what we're editing if anything
	#
	if (param("action") eq "Yes, drop it") {
		if ($dbh->do("DELETE FROM routes WHERE pattern = " . $dbh->quote(param('dropitem')))) {
			$status = "Dropped route '" . param('dropitem') . "'.";
		} else {
			$status = "Unable to drop route '" . param('dropitem') . "'.";
		}
	}
	for ($x=1;length(param("item$x"));$x++) {
		$newtrunks = &find_newtrunks("newtrunk$x");
		if (param("action$x") eq "Edit...") {
			$editing = param("item$x");
		} elsif (param("action$x") eq "Add...") {
			$editing = param("item$x");
			$creating = "yes";
			$status = "Creating new entry $x";
		} elsif (param("action$x") eq "Save...") {
			if ($dbh->do("UPDATE routes SET pattern=" . $dbh->quote(param("newpat$x")) .
				" WHERE pattern=" . $dbh->quote(param("item$x")))) {
				$tmp = "REPLACE INTO routes (pattern,comment,trunks,connectcost, includedseconds, cost) VALUES (" .
					$dbh->quote(param("newpat$x")) . ", " . $dbh->quote(param("newcomment$x")) . ", " . 
					$dbh->quote($newtrunks) .
					", " . $dbh->quote(param("newconnect$x")) . ", " . $dbh->quote(param("newincluded$x")) .
					", " . $dbh->quote(param("newcost$x")) . ")";
				if ($dbh->do($tmp)) {
					$status = "Pattern '" . param("newpat$x") . "' has been updated";
				} else {
					$status = "Pattern '" . param("newpat$x") . "' FAILED to update ($tmp)!";
				}
			} else {
				$status = "Pattern already exists!";
			}
		} elsif (param("action$x") eq "Create...") {
				$tmp = "INSERT INTO routes (pattern,comment,trunks,connectcost, includedseconds, cost) VALUES (" .
					$dbh->quote(param("newpat$x")) . ", " . $dbh->quote(param("newcomment$x")) .
					", " . $dbh->quote($newtrunks) .  
					", " . $dbh->quote(param("newconnect$x")) . ", " . $dbh->quote(param("newincluded$x")) .
					", " . $dbh->quote(param("newcost$x")) . ")";
				if ($dbh->do($tmp)) {
					$status = "Pattern '" . param("newpat$x") . "' has been created";
				} else {
					$status = "Pattern '" . param("newpat$x") . "' FAILED to create ($tmp)!";
				}
		} elsif (param("action$x") eq "Drop...") {
			$status = "Are you sure you want to delete the pattern '" .
				param("item$x") . "'?<br>" . hidden(-name => "dropitem", -value => param("item$x")) .
				submit(-name => "action", -value => "Yes, drop it") . "&nbsp;&nbsp;&nbsp;" .
				submit(-name => "action", -value => "Cancel");
			$editing = "dropping something";
		}
	}
	$sth = $dbh->prepare('SELECT * FROM routes');
	$sth->execute || return "Something is wrong with the routes database\n";
	$body = "<table>" .
		"<tr bgcolor=ffffff><td colspan=7>" . start_form . "<i>$status</i></td></tr>\n" .
		"<tr bgcolor=ffffff><td colspan=3><font size=-1><i>Pattern is a REGEX</i></font></td><td colspan=4>". "<font size=-1><i>All costs are in 1/100 of a penny</i></font></td></tr>\n" .
		"<tr bgcolor=ccccff><td>" . hidden(-name => 'mode', -value => 'Routes') . "Pattern</td><td>Comment</td><td>Trunks</td><td>Connect Fee</td><td>Inc. Seconds</td><td colspan=2>Cost per additional minute</td></tr>\n";
	while($row = $sth->fetchrow_hashref) {
		$count++;
		@oldtrunks = split(/:/, $row->{trunks});
		Delete("newpat$count");
		Delete("item$count");
		if (!($count % 2)) {
			$color = "#ccffcc";
		} else {
			$color = "#ffffcc";
		}
		if ($editing eq $row->{pattern}) {
			$body .= "<tr valign=top bgcolor=$color><td>" . hidden(-name => "item$count", -value => $row->{pattern}) .
				 textfield(-name => "newpat$count", -size=> 8, -default => $row->{pattern}) . "</td><td>" .
				 textfield(-name => "newcomment$count", -size=> 20, -default => $row->{comment}) . "</td><td>" .
		         "1" . &list_trunks("newtrunk$count-1", $oldtrunks[0]) . "<br>" . 
				 "2" . &list_trunks("newtrunk$count-2", $oldtrunks[1]) . "<br>" . 
				 "3" . &list_trunks("newtrunk$count-3", $oldtrunks[2]) . "<br>" . 
				 "4" . &list_trunks("newtrunk$count-4", $oldtrunks[3]) . "</td><td>" . 
				 textfield(-name => "newconnect$count", -size => 5, -default => $row->{connectcost}) . "</td><td>" .
				 textfield(-name => "newincluded$count", -size => 5, -default => $row->{includedseconds}) . "</td><td>" .
				 textfield(-name => "newcost$count", -size => 5, -default => $row->{cost}) . "</td>";
				 $body .= "<td bgcolor=white>" . submit(-name => "action$count", -value => 'Save...') .
				 submit(-name => "action$count", -value => 'Cancel...'); 
				$body .= "</td></tr>";
		} else {
			$body .= "<tr bgcolor=$color><td>" . hidden(-name => "item$count", -value => $row->{pattern}) .
				 "$row->{pattern}</td>" .
		         "<td>$row->{comment}</td><td>$row->{trunks}</td><td>$row->{connectcost}</td><td>$row->{includedseconds}</td><td>$row->{cost}</td>" .
				 "<td bgcolor=white>";
			if (!$editing) {
				$body .= submit(-name => "action$count", -value => 'Edit...') .
				 submit(-name => "action$count", -value => 'Drop...');
			} else {
				$body .= "&nbsp;";
			}
			$body .= "</td></tr>\n";
		}
	}
	$sth->finish;
	$count++;
	if ($creating) {
			$body .= "<tr valign=top bgcolor=$color><td>" . hidden(-name => "item$count", -value => $row->{name}) .
				 textfield(-name => "newpat$count", -size=> 8) . "</td><td>" .
				 textfield(-name => "newcomment$count", -size=> 20) . "</td><td>" .
		         "1" . &list_trunks("newtrunk$count-1") . "<br>" . 
				 "2" . &list_trunks("newtrunk$count-2") . "<br>" . 
				 "3" . &list_trunks("newtrunk$count-3") . "<br>" . 
				 "4" . &list_trunks("newtrunk$count-4") . "</td><td>" . 
				 textfield(-name => "newconnect$count", -size => 5, default => 0) . "</td><td>" .
				 textfield(-name => "newincluded$count", -size => 5, default => 0) . "</td><td>" .
				 textfield(-name => "newcost$count", -size => 5, -default => 0) . "</td>";
				 $body .= "<td bgcolor=white>" . submit(-name => "action$count", -value => 'Create...') .
				 submit(-name => "action$count", -value => 'Cancel...');
				$body .= "</td></tr>";
	}
	if (!$editing) {
		$body .=
			"<tr bgcolor=ffffff><td colspan=7><hr></td></tr>\n";
		$body .=
			"<tr bgcolor=ffffff><td colspan=6>&nbsp;" . hidden(-name => "item$count", -value => "new") . 
			"</td><td align=right>" .
			submit(-name => "action$count", -value => "Add...") . "</td></tr>\n";
	}
	$body .= "</table>\n";
	return $body;
}

sub build_configure() {
	my $body = start_form;
	my $activity = "<br>";
	my $action = param('action');
	if (($action eq "Save") || ($action eq "Create Database") || ($action eq "Update Database")) {
		$config{dbuser} = param('dbuser');
		$config{dbpass} = param('dbpass');
		$config{dbname} = param('dbname');
		$config{dbhost} = param('dbhost');
		$config{cardlength} = param('cardlength');		
		$config{startingdigit} = param('startingdigit');			
		$config{email} = param('email');
		$config{emailadd} = param('emailadd');
		$config{mailprog} = param('mailprog');
		$config{friendsdb} = param('friendsdb');
		$config{maintverbose} = param('maintverbose');
		$config{maintcdr} = param('maintcdr');
		$config{maintname} = param('maintname');
		$config{pinstatus} = param('pinstatus');
		$config{pinlength} = param('pinlength');		
		$config{debug} = param('debug');
		&save_config();
		$activity = "Configuration saved...";
		if ($action eq "Create Database") {
			if (!&create_db()) {i
				$activity = "Database created!";
			} else {
				$activity = "Database creation failed!";
			}
		} elsif ($action eq "Update Database") {
			if (!&update_db()) {i
				$activity = "Database Updated!";
			} else {
				$activity = "Database Update failed!";
			}
		}
		&connect_db;
	}
	$body .= hidden(-name => 'mode', -default => 'Configure');
	$body .= "<table><tr><td align=left colspan=2><font color=#4444aa>Database Configuration</font></td></tr>";
	$body .= "<tr><td colspan=2 align=center><i>$activity</i></td></tr>\n";
	$body .= "<tr><td>Host</td><td>" . textfield(-name => 'dbhost', -default => $config{dbhost}) . "</td></tr>";
	$body .= "<tr><td>Username</td><td>" . textfield(-name => 'dbuser', -default => $config{dbuser}) . "</td></tr>";
	$body .= "<tr><td>Password</td><td>" . textfield(-name => 'dbpass', -default => $config{dbpass}) . "</td></tr>";
	$body .= "<tr><td>Database</td><td>" . textfield(-name => 'dbname', -default => $config{dbname}) . "</td></tr>";
	$body .= "<tr><td>Card Length (4-20) </td><td>" . textfield(-name => 'cardlength', -default => $config{cardlength}) . "</td></tr>";	
	$body .= "<tr><td>Card Starting Number - </td></tr>";
	$body .= "<tr><td>Enter 0 if Not Required </td><td>" . textfield(-name => 'startingdigit', -default => $config{startingdigit}) . "</td></tr>";
	$body .= "<tr><td>Email New Card Info (YES/NO) </td><td>" . textfield(-name => 'email', -default => $config{email}) . "</td></tr>";
	$body .= "<tr><td>Admin Email</td><td>" . textfield(-name => 'emailadd', -default => $config{emailadd}) . "</td></tr>";
	$body .= "<tr><td>Email Program</td><td>" . textfield(-name => 'mailprog', -default => $config{mailprog}) . "</td></tr>";
	$body .= "<tr><td>Enable Iax/Sip Friends DB (YES/NO) </td><td>" . textfield(-name => 'friendsdb', -default => $config{friendsdb}) . "</td></tr>";
	$body .= "<tr><td>Verbose account maintainence (YES/NO) </td><td>" . textfield(-name => 'maintverbose', -default => $config{maintverbose}) . "</td></tr>";
	$body .= "<tr><td>Modify CDR to show maintainence fee (YES/NO) </td><td>" . textfield(-name => 'maintcdr', -default => $config{maintcdr}) . "</td></tr>";
	$body .= "<tr><td>CDR Text for maintainence fee</td><td>" . textfield(-name => 'maintname', -default => $config{maintname}) . "</td></tr>";
	$body .= "<tr><td>Pin Length  (4-20) </td><td>" . textfield(-name => 'pinlength', -default => $config{pinlength}) . "</td></tr>";
	$body .= "<tr><td>Require Pins (Yes/NO)</td><td>" . textfield(-name => 'pinstatus', -default => $config{pinstatus}) . "</td></tr>";
	$body .= "<tr><td>Debug Mode</td><td>" . popup_menu(-name => "debug", -values => \@yesno, -default=> $config{debug}) . "</td></tr>";
	$body .= "<tr><td align=left>&nbsp;" . submit(-name => 'action', -value => 'Save') . "</td><td align=right>" . submit( -name => 'action', -value => 'Create Database') . "&nbsp;</td></tr>";
	$body .= "<tr><td align=left>&nbsp;" . submit(-name => 'action', -value => 'Update Database') . "&nbsp;</td></tr>";
	$body .= "</table>";
	return $body;
}

sub build_user_configure() {
	my $body = start_form;
	my $activity = "<br>";
	my $action = param('action');
	if (($action eq "Save") || ($action eq "Create Database")) {
		$users_config{dbuser} = param('dbuser');
		$users_config{dbpass} = param('dbpass');
		$users_config{dbname} = param('dbname');
		$users_config{dbhost} = param('dbhost');
		$users_config{iax_context} = param('iax_context');		
		$users_config{sip_context} = param('sip_context');
		$users_config{iax_port} = param('iax_port');		
		$users_config{sip_port} = param('sip_port');
		$users_config{regseconds} = param('regseconds');			
		$users_config{brand} = param('brand');
		$users_config{emailadd} = param('emailadd');
		$users_config{mailprog} = param('mailprog');
		&users_save_config();
		$activity = "Configuration saved...";
		if ($action eq "Create Database") {
			if (!&users_create_db()) {i
				$activity = "Database created!";
			} else {
				$activity = "Database creation failed!";
			}
		}
		&users_connect_db;
	}
	$body .= hidden(-name => 'mode', -default => 'Users_Configure');
	$body .= "<table><tr><td align=left colspan=2><font color=#4444aa>Database Configuration</font></td></tr>";
	$body .= "<tr><td colspan=2 align=center><i>$activity</i></td></tr>\n";
	$body .= "<tr><td>Host</td><td>" . textfield(-name => 'dbhost', -default => $users_config{dbhost}) . "</td></tr>";
	$body .= "<tr><td>Username</td><td>" . textfield(-name => 'dbuser', -default => $users_config{dbuser}) . "</td></tr>";
	$body .= "<tr><td>Password</td><td>" . textfield(-name => 'dbpass', -default => $users_config{dbpass}) . "</td></tr>";
	$body .= "<tr><td>Database</td><td>" . textfield(-name => 'dbname', -default => $users_config{dbname}) . "</td></tr>";
	$body .= "<tr><td>Default SIP Context </td><td>" . textfield(-name => 'sip_context', -default => $users_config{sip_context}) . "</td></tr>";	
	$body .= "<tr><td>Default IAX Context </td><td>" . textfield(-name => 'iax_context', -default => $users_config{iax_context}) . "</td></tr>";
	$body .= "<tr><td>Default SIP Port </td><td>" . textfield(-name => 'sip_port', -default => $users_config{sip_port}) . "</td></tr>";	
	$body .= "<tr><td>Default IAX Port </td><td>" . textfield(-name => 'iax_port', -default => $users_config{iax_port}) . "</td></tr>";
	$body .= "<tr><td>Default Reg Seconds </td><td>" . textfield(-name => 'regseconds', -default => $users_config{regseconds}) . "</td></tr>";
	$body .= "<tr><td>Default Brand </td><td>" . textfield(-name => 'brand', -default => $users_config{brand}) . "</td></tr>";
	$body .= "<tr><td>Admin Email</td><td>" . textfield(-name => 'emailadd', -default => $users_config{emailadd}) . "</td></tr>";
	$body .= "<tr><td>Email Program</td><td>" . textfield(-name => 'mailprog', -default => $users_config{mailprog}) . "</td></tr>";
	$body .= "<tr><td align=left>&nbsp;" . submit(-name => 'action', -value => 'Save') . "</td><td align=right>" . submit( -name => 'action', -value => 'Create Database') . "&nbsp;</td></tr>";
	$body .= "</table>";
	return $body;
}

sub add_sip_user() {
	my ($name, $secret) = @_;
	my $username = $name;
	my $context = $users_config{sip_context};
	my $port = $users_config{sip_port};
	my $regseconds = $users_config{regseconds};
	my $brand = $users_config{brand};
	my $ipaddr = "dynamic";
	$tmp = "INSERT INTO sipfriends (name,username,secret,context,ipaddr,port,regseconds) VALUES (" .
					$users_dbh->quote($name) . ", " .$users_dbh->quote($username) . ", " .
					$users_dbh->quote($secret) . ", " .$users_dbh->quote($context) . ", " .
					$users_dbh->quote($ipaddr) . ", " . $users_dbh->quote($port) . ", " .
					$users_dbh->quote($regseconds).")";
	$users_dbh->do($tmp) || print "$tmp failed";
}

sub add_iax_user() {
	my ($name, $secret) = @_;
	my $context = $users_config{iax_context};
	my $port = $users_config{iax_port};
	my $regseconds = $users_config{regseconds};
	my $ipaddr = "dynamic";
	$tmp = "INSERT INTO iaxfriends (name,secret,context,ipaddr,port,regseconds) VALUES (" .
					$users_dbh->quote($name) . ", " .
					$users_dbh->quote($secret) . ", " .$users_dbh->quote($context) . ", " .
					$users_dbh->quote($ipaddr) . ", " . $users_dbh->quote($port) . ", " .
					$users_dbh->quote($regseconds).")";
	$users_dbh->do($tmp) || print "$tmp failed";
}

sub build_iax_friends() {
	my $sth;
	my $count;
	my $color;
	my $tmp;
	my $editing;
	my $creating;
	my $status = "&nbsp;";
	return "Cannot edit IAX users until database is configured" unless $users_dbh;
	#
	# Find what we're editing if anything
	#
	if (param("action") eq "Yes, drop it") {
		if ($users_dbh->do("DELETE FROM iaxfriends WHERE name = " . $users_dbh->quote(param('dropitem')))) {
			$status = "Dropped user '" . param('dropitem') . "'.";
		} else {
			$status = "Unable to drop user '" . param('dropitem') . "'.";
		}
	}
	for ($x=1;length(param("item$x"));$x++) {
		if (param("action$x") eq "Edit...") {
			$editing = param("item$x");
		} elsif (param("action$x") eq "Add...") {
			$editing = param("item$x");
			$creating = "yes";
			$status = "Creating new entry $x";
		} elsif (param("action$x") eq "Save...") {
			if ($users_dbh->do("UPDATE iaxfriends SET name=" . $users_dbh->quote(param("newname$x")) .
				" WHERE name=" . $users_dbh->quote(param("item$x")))) {
				$tmp = "REPLACE INTO iaxfriends (name,secret,context,ipaddr,port,regseconds) VALUES (" . $users_dbh->quote(param("newname$x")) . 
					", " . $users_dbh->quote(param("newsecret$x")) .
					", " . $users_dbh->quote(param("newcontext$x")) . 
					", " . $users_dbh->quote(param("newipaddr$x")) . 
					", " . $users_dbh->quote(param("newport$x")) . 
					", " . $users_dbh->quote(param("newregseconds$x")) . ")";
				if ($users_dbh->do($tmp)) {
					$status = "User '" . param("newname$x") . "' has been updated";
				} else {
					$status = "User '" . param("newname$x") . "' FAILED to update ($tmp)!";
				}
			} else {
				$status = "Name is already in use!";
			}
		} elsif (param("action$x") eq "Create...") {
				$tmp = "INSERT INTO iaxfriends (name,secret,context,ipaddr,port,regseconds) VALUES (" .
					$users_dbh->quote(param("newname$x")) . ", " . $users_dbh->quote(param("newsecret$x")) .
					", " . $users_dbh->quote(param("newcontext$x")) .
					", " . $users_dbh->quote(param("newipaddr$x")) . ", " . $users_dbh->quote(param("newport$x")) . 
					", " . $users_dbh->quote(param("newregseconds$x")) . ")";
				if ($users_dbh->do($tmp)) {
					$status = "User '" . param("newname$x") . "' has been created";
				} else {
					$status = "User '" . param("newname$x") . "' FAILED to create ($tmp)!";
				}
		} elsif (param("action$x") eq "Drop...") {
			$status = "Are you sure you want to delete the user '" .
				param("item$x") . "'?<br>" . hidden(-name => "dropitem", -value => param("item$x")) .
				submit(-name => "action", -value => "Yes, drop it") . "&nbsp;&nbsp;&nbsp;" .
				submit(-name => "action", -value => "Cancel");
			$editing = "dropping something";
			
		}
	}
	$sth = $users_dbh->prepare('SELECT * FROM iaxfriends');
	$sth->execute || return "Something is wrong with the iaxfriends database\n";
	$body = "<table>" .
		"<tr bgcolor=ffffff><tdcolspan=7>" . start_form . "<i>$status</i></td></tr>\n" .
		"<tr bgcolor=ccccff><td>" . hidden(-name => 'mode', -value => 'iaxfriends') . "User Name</td><td>Secret</td><td>Context</td><td>IP Address</td><td>Port</td><td>Reg Seconds</td></tr>\n";
	while($row = $sth->fetchrow_hashref) {
		$count++;
		if (!($count % 2)) {
			$color = "#ccffcc";
		} else {
			$color = "#ffffcc";
		}
		Delete("newname$count");
		Delete("item$count");
		if ($editing eq $row->{name}) {
			$body .= "<tr bgcolor=$color><td>" . hidden(-name => "item$count", -value => $row->{name}) .
				 textfield(-name => "newname$count", -size=> 10, -default => $row->{name}) . "</td><td>" .
		         	 textfield(-name => "newsecret$count", -size=> 10, -default=>$row->{secret}) . 
				 "</td><td>" . 
				 textfield(-name => "newcontext$count", -size => 15, -default => $row->{context}) . "</td><td>" .
				 textfield(-name => "newipaddr$count", -size => 10, -default => $row->{ipaddr}) . 
				 "</td><td>" . 
				 textfield(-name => "newport$count", -size => 10, -default => $row->{port}) . "</td><td>" .
				 textfield(-name => "newregseconds$count", -size => 5, -default => $row->{regseconds}) . "</td>";
				 $body .= "<td bgcolor=white>" . submit(-name => "action$count", -value => 'Save...') .
				 submit(-name => "action$count", -value => 'Cancel...'); 
				$body .= "</td></tr>";
		} else {
			$body .= "<tr bgcolor=$color><td>" . hidden(-name => "item$count", -value => $row->{name}) .
				 "$row->{name}</td>" .
		         "<td>$row->{secret}</td><td>$row->{context}</td>" .
				 "<td>$row->{ipaddr}</td><td>$row->{port}</td><td>$row->{regseconds}</td>" .
				 "<td bgcolor=white>";
			if (!$editing) {
				$body .= submit(-name => "action$count", -value => 'Edit...') .
				 submit(-name => "action$count", -value => 'Drop...');
			} else {
				$body .= "&nbsp;";
			}
			$body .= "</td></tr>\n";
		}
	}
	$sth->finish;
	$count++;
	if ($creating) {
			$body .= "<tr bgcolor=$color><td>" . hidden(-name => "item$count", -value => $row->{name}) .
				 textfield(-name => "newname$count", -size=> 10) . "</td><td>" .
		         	 textfield(-name => "newsecret$count", -size => 10) . "</td><td>" . 
				 textfield(-name => "newcontext$count", -size => 15) . "</td><td>" .
				 textfield(-name => "newipaddr$count", -size => 10) . "</td><td>" . 
				 textfield(-name => "newport$count", -size => 10) . "</td><td>" .
				 textfield(-name => "newregseconds$count", -size => 5) . "</td>";
				 $body .= "<td bgcolor=white>" . submit(-name => "action$count", -value => 'Create...') .
				 submit(-name => "action$count", -value => 'Cancel...');
				$body .= "</td></tr>";
	}
	if (!$editing) {
		$body .=
			"<tr bgcolor=ffffff><td colspan=7><hr></td></tr>\n";
		$body .=
			"<tr bgcolor=ffffff><td colspan=6>&nbsp;" . hidden(-name => "item$count", -value => "new") . 
			"</td><td align=right>" .
			submit(-name => "action$count", -value => "Add...") . "</td></tr>\n";
	}
	$body .= "</table>\n";
	return $body;
}

sub build_sip_friends() {
	my $sth;
	my $count;
	my $color;
	my $tmp;
	my $editing;
	my $creating;
	my $status = "&nbsp;";
	return "Cannot edit SIP users until database is configured" unless $users_dbh;
	#
	# Find what we're editing if anything
	#
	if (param("action") eq "Yes, drop it") {
		if ($users_dbh->do("DELETE FROM sipfriends WHERE name = " . $users_dbh->quote(param('dropitem')))) {
			$status = "Dropped user '" . param('dropitem') . "'.";
		} else {
			$status = "Unable to drop user '" . param('dropitem') . "'.";
		}
	}
	for ($x=1;length(param("item$x"));$x++) {
		if (param("action$x") eq "Edit...") {
			$editing = param("item$x");
		} elsif (param("action$x") eq "Add...") {
			$editing = param("item$x");
			$creating = "yes";
			$status = "Creating new entry $x";
		} elsif (param("action$x") eq "Save...") {
			if ($users_dbh->do("UPDATE sipfriends SET name=" . $users_dbh->quote(param("newname$x")) .
				" WHERE name=" . $users_dbh->quote(param("item$x")))) {
				$tmp = "REPLACE INTO sipfriends (name,username,secret,context,ipaddr,port,regseconds) VALUES (" . $users_dbh->quote(param("newname$x")) . 
					", " . $users_dbh->quote(param("newusername$x")) .
					", " . $users_dbh->quote(param("newsecret$x")) .
					", " . $users_dbh->quote(param("newcontext$x")) . 
					", " . $users_dbh->quote(param("newipaddr$x")) . 
					", " . $users_dbh->quote(param("newport$x")) . 
					", " . $users_dbh->quote(param("newregseconds$x")) . ")";
				if ($users_dbh->do($tmp)) {
					$status = "User '" . param("newname$x") . "' has been updated";
				} else {
					$status = "User '" . param("newname$x") . "' FAILED to update ($tmp)!";
				}
			} else {
				$status = "Name is already in use!";
			}
		} elsif (param("action$x") eq "Create...") {
				$tmp = "INSERT INTO sipfriends (name,username,secret,context,ipaddr,port,regseconds) VALUES (" .
					$users_dbh->quote(param("newname$x")) . 
					", " . $users_dbh->quote(param("newusername$x")) .
					", " . $users_dbh->quote(param("newsecret$x")) .
					", " . $users_dbh->quote(param("newcontext$x")) .
					", " . $users_dbh->quote(param("newipaddr$x")) . 
					", " . $users_dbh->quote(param("newport$x")) . 
					", " . $users_dbh->quote(param("newregseconds$x")) . ")";
				if ($users_dbh->do($tmp)) {
					$status = "User '" . param("newname$x") . "' has been created";
				} else {
					$status = "User '" . param("newname$x") . "' FAILED to create ($tmp)!";
				}
		} elsif (param("action$x") eq "Drop...") {
			$status = "Are you sure you want to delete the user '" .
				param("item$x") . "'?<br>" . hidden(-name => "dropitem", -value => param("item$x")) .
				submit(-name => "action", -value => "Yes, drop it") . "&nbsp;&nbsp;&nbsp;" .
				submit(-name => "action", -value => "Cancel");
			$editing = "dropping something";
			
		}
	}
	$sth = $users_dbh->prepare('SELECT * FROM sipfriends');
	$sth->execute || return "Something is wrong with the sipfriends database\n";
	$body = "<table>" .
		"<tr bgcolor=ffffff><tdcolspan=7>" . start_form . "<i>$status</i></td></tr>\n" .
		"<tr bgcolor=ccccff><td>" . hidden(-name => 'mode', -value => 'sipfriends') . "Name</td>
		<td>User Name</td><td>Secret</td><td>Context</td><td>IP Address</td><td>Port</td><td>Reg Seconds</td></tr>\n";
	while($row = $sth->fetchrow_hashref) {
		$count++;
		if (!($count % 2)) {
			$color = "#ccffcc";
		} else {
			$color = "#ffffcc";
		}
		Delete("newname$count");
		Delete("item$count");
		if ($editing eq $row->{name}) {
			$body .= "<tr bgcolor=$color><td>" . hidden(-name => "item$count", -value => $row->{name}) .
				 textfield(-name => "newname$count", -size=> 10, -default => $row->{name}) . "</td><td>" .
				 textfield(-name => "newusername$count", -size=> 10, -default => $row->{username}) . "</td><td>" .
		         	 textfield(-name => "newsecret$count", -size=> 10, -default=>$row->{secret}) . 
				 "</td><td>" . 
				 textfield(-name => "newcontext$count", -size => 15, -default => $row->{context}) . "</td><td>" .
				 textfield(-name => "newipaddr$count", -size => 10, -default => $row->{ipaddr}) . 
				 "</td><td>" . 
				 textfield(-name => "newport$count", -size => 10, -default => $row->{port}) . "</td><td>" .
				 textfield(-name => "newregseconds$count", -size => 5, -default => $row->{regseconds}) . "</td>";
				 $body .= "<td bgcolor=white>" . submit(-name => "action$count", -value => 'Save...') .
				 submit(-name => "action$count", -value => 'Cancel...'); 
				$body .= "</td></tr>";
		} else {
			$body .= "<tr bgcolor=$color><td>" . hidden(-name => "item$count", -value => $row->{name}) .
				 "$row->{name}</td>" .
		        	 "<td>$row->{username}</td><td>$row->{secret}</td><td>$row->{context}</td>" .
				 "<td>$row->{ipaddr}</td><td>$row->{port}</td><td>$row->{regseconds}</td>" .
				 "<td bgcolor=white>";
			if (!$editing) {
				$body .= submit(-name => "action$count", -value => 'Edit...') .
				 submit(-name => "action$count", -value => 'Drop...');
			} else {
				$body .= "&nbsp;";
			}
			$body .= "</td></tr>\n";
		}
	}
	$sth->finish;
	$count++;
	if ($creating) {
			$body .= "<tr bgcolor=$color><td>" . hidden(-name => "item$count", -value => $row->{name}) .
				 textfield(-name => "newname$count", -size=> 10) . "</td><td>" .
				 textfield(-name => "newusername$count", -size=> 10) . "</td><td>" .
		         	 textfield(-name => "newsecret$count", -size => 10) . "</td><td>" . 
				 textfield(-name => "newcontext$count", -size => 15) . "</td><td>" .
				 textfield(-name => "newipaddr$count", -size => 10) . "</td><td>" . 
				 textfield(-name => "newport$count", -size => 10) . "</td><td>" .
				 textfield(-name => "newregseconds$count", -size => 5) . "</td>";
				 $body .= "<td bgcolor=white>" . submit(-name => "action$count", -value => 'Create...') .
				 submit(-name => "action$count", -value => 'Cancel...');
				$body .= "</td></tr>";
	}
	if (!$editing) {
		$body .=
			"<tr bgcolor=ffffff><td colspan=7><hr></td></tr>\n";
		$body .=
			"<tr bgcolor=ffffff><td colspan=6>&nbsp;" . hidden(-name => "item$count", -value => "new") . 
			"</td><td align=right>" .
			submit(-name => "action$count", -value => "Add...") . "</td></tr>\n";
	}
	$body .= "</table>\n";
	return $body;
}

sub build_calc_charge() {
	my ($total, $used, $pennies, $count, $brand, $language, $inc, $markup);
	return "Cannot calculate charges until database is configured" unless $dbh;
	my $brands = &list_brands();
	return "Please define at least one brand before creating cards" unless $brands;
	if (param('action') eq "Calc_Rate...") {
		$phoneno = param('Number');
		$minutes = param('Minutes');
		$seconds = $minutes * 60;
		$brand = param('brand');

		# Retrieve brand specifications
		my $sth = $dbh->prepare("SELECT * FROM brands WHERE name = " . $dbh->quote($brand));
		$sth->execute;
		if ($row = $sth->fetchrow_hashref) {
			($language, $increment, $markup) = ($row->{language}, $row->{inc}, $row->{markup});
	 	}
		$sth->finish;
		$charge = &calccost($phoneno, $seconds,$increment,$markup);		
		eval { $charge = $charge / 10000 };
		$status = "Charge for $minutes to $phoneno \n";
		$status .= "will be $charge \n";
	}	
	$body = "<table><tr><td>" . start_form . "$status</td></tr>";
	$body .= "<tr><td>Brand:" . $brands .  " Phone Number&nbsp;&nbsp;" . hidden(-name => "mode", -value => "Cards") .
		textfield(-name => "Number", -size => 20) . "&nbsp;&nbsp;&nbsp;&nbsp;" .
		textfield(-name => "Minutes", -size => 4) . "&nbsp;&nbsp;Minutes&nbsp;&nbsp;" .
		submit(-name => "action", -value => "Calc_Rate...") . "</td></tr>";
	$body .= "</table>";
	return $body;
}


sub calccost() {
	my ($number, $answeredtime, $increment, $markup) = @_;
	my $numdata = &getphone($number);
	$adjcost = ($numdata->{cost} * (10000 + $markup)) / 10000;
	$adjconn = ($numdata->{connectcost} * (10000 + $markup)) / 10000;
    my $cost;
    my $adjtime = eval { $adjtime = int((($answeredtime - $numdata->{includedseconds}) + $increment - 1) / $increment) * $increment;
		return $adjtime };
	if ($adjtime < 0) {
		$adjtime = 0;
	}
    print STDERR "Adjusted time is $adjtime, cost is $adjcost with $adjconn fee\n";
    eval { $cost = int($adjcost * $adjtime / 60) };
    $cost += $adjconn;
    print STDERR "Total cost is $cost\n";
    return $cost;
}

sub getphone() {
	my ($number) = @_;
	my $sth = $dbh->prepare("SELECT * FROM routes WHERE " . $dbh->quote($number) . " RLIKE pattern ORDER BY LENGTH(pattern) DESC");
	$sth->execute;
	$res = $sth->fetchrow_hashref;
	$sth->finish;
	return $res;
}


sub build_body() {
	my ($mode) = @_;
	my $body;
	return &build_home() if ($mode eq "Home");
	return &build_cards() if ($mode eq "Cards");
	return &build_configure() if ($mode eq "Configure");
	return &build_brands() if ($mode eq "Brands");
	return &build_trunks() if ($mode eq "Trunks");
	return &build_routes() if ($mode eq "Routes");
	return &build_calc_charge() if ($mode eq "Calc_Charge");	
	if ($config{'friendsdb'} eq "YES") {	
		return &build_user_configure() if ($mode eq "Users_Configure");
		return &build_iax_friends() if ($mode eq "IAX_Friends");	
		return &build_sip_friends() if ($mode eq "SIP_Friends");
	} elsif ($config{'friendsdb'} ne "YES") {
		return "<h2>Not Configured!</h2>\n" if ($mode eq "Users_Configure");
		return "<h2>Not Configured!</h2>\n" if ($mode eq "IAX_Friends");	
		return "<h2>Not Configured!</h2>\n" if ($mode eq "SIP_Friends");
	}		
	return "<h2>Not yet implemented!</h2>\n";
}

my $body, $menu;
my $mode = param("mode");
my $pn = param("pn");
my $em = param("em");
my $md = param("md");
my $iax = param("iax");
my $sip = param("sip");

&load_config;
&connect_db;

if ($config{'friendsdb'} eq "YES") {
	&users_load_config;
	&users_connect_db;
}

$msg = "&nbsp;";
if ($md eq "Auto") {
	&users_generatecards($pn, $em, $iax, $sip);
} else {
	$mode = "Home" unless grep(/^$mode$/, @modes);
	$body = &build_body($mode);
	$menu = &build_menu($mode);
	$msg = "<i>Database unavailable -- please check configuration</i>" unless $dbh;

	print "<title>Asterisk&trade; Calling Card manager: $mode</title>\n";
	print "<body><table align=center width=800>\n";
	print "   <tr><td><img src=\"/_astcc/astcc.png\"></td><td align=center><font face=verdana,helvetica size=5>Asterisk&trade; Calling Card Admin: <b>$mode</b></font>" .
		"<br><font face=verdana,helvetica color=#444444>$msg</font></td></tr>";
	print "   <tr><td height=350 valign=top>$menu</td><td valign=top width=90% align=center><font face=verdana,helvetica>$body</font></td></tr>";
	print "<tr><td align=center colspan=2><hr><font face=verdana,helvetica size=-1>$copyright</font></td></tr>\n";
	print "</table></body>\n";
}