Lookup Util

#!/usr/bin/perl #* #* GnuDialer - Complete, free predictive dialer #* #* Complete, free predictive dialer for contact centers. #* #* Copyright (C) 2006, GnuDialer Project #* #* Richard Lyman #* #* This program is free software, distributed under the terms of #* the GNU General Public License. #* print "Content-type: text/html\n\n"; use CGI::Carp qw(fatalsToBrowser carpout); use CGI qw(:standard); use DBI; $drh = DBI->install_driver('mysql') or die("Unable to install driver"); $dbh = DBI->connect("DBI:mysql:dialer",'dialer','1234') or die("Unable to connect"); $testmode = 0; $testadd = 0; $usename = 1; $uselname = 0; $usefname = 0; ################### #my %param = map { $_, param($_) } param(); &fetchit; ################### $mycgi = $ENV{'SCRIPT_NAME'}; if ($FORM{'fs'}) { $fs = lc $FORM{'fs'}; $fstate = lc $FORM{'fstate'}; if ($fs) { $fstate = $fs; } } #print $fs; $referer = $ENV{'HTTP_REFER'}; $table = $FORM{'table'}; $leadid = $FORM{'leadid'}; $phone = $FORM{'phone'}; if ($usename) {$name = uc $FORM{'name'}; }; if ($usefname) {$fname = uc $FORM{'fname'}; }; if ($uselname) {$lname = uc $FORM{'lname'}; }; #$name =~ s/(\w+)/\u\L$1/g; ($sec,$min,$hour,$mday,$month,$year,$wday) = (localtime(time))[0,1,2,3,4,5,6]; $mydatetimestamp = sprintf("%4.4d%2.2d%2.2d%2.2d%2.2d%2.2d", $year+1900, $month+1, $mday, $hour, $min, $sec); if ($fstate eq 'errormsg') {&myerror; } elsif ($fstate eq 'lookup') {&base; &what_dbs; } elsif ($fstate eq 'edit') {&edit_record; } elsif ($fstate eq 'add') {&add_record; } elsif ($fstate eq 'update') {&update_record; &clear_params; &base;} elsif ($fstate eq 'env') {&env; &base;} else {&base; } $dbh->disconnect; ##################################################################################### ################################################################################################ sub base { print < Lookup Util EOT_MARKER # what_dbs(); print < EOT_MARKER } ####################################################################################### sub clear_params { $FORM{'leadid'} = ""; $FORM{'phone'} = ""; if ($usename) {$FORM{'name'} = ""; }; if ($usefname) {$FORM{'fname'} = ""; }; if ($uselname) {$FORM{'lname'} = ""; }; $FORM{'table'} = ""; $leadid = $FORM{'leadid'}; $phone = $FORM{'phone'}; if ($usename) {$name = uc $FORM{'name'}; }; if ($usefname) {$fname = uc $FORM{'fname'}; }; if ($uselname) {$lname = uc $FORM{'lname'}; }; $table = $FORM{'table'}; } ####################################################################################### sub edit_record { print < EOT_MARKER $mystring = "SELECT * FROM `" . $table . "` WHERE id=$leadid "; $sth = $dbh->prepare("$mystring"); $sth->execute or die("Unable to excute query"); $i=1; while (@data=$sth->fetchrow_array()) { foreach $col (@data) { if ($sth->{NAME}->[$i-1] ne "id" && $sth->{NAME}->[$i-1] ne "lastupdated" && $sth->{NAME}->[$i-1] ne "wdayl" && $sth->{NAME}->[$i-1] ne "wdayh" && $sth->{NAME}->[$i-1] ne "satl" && $sth->{NAME}->[$i-1] ne "sath" && $sth->{NAME}->[$i-1] ne "sunl" && $sth->{NAME}->[$i-1] ne "sunh" && $sth->{NAME}->[$i-1] ne "holl" && $sth->{NAME}->[$i-1] ne "holh" && $sth->{NAME}->[$i-1] ne "tzl" && $sth->{NAME}->[$i-1] ne "tzh") { if ($sth->{NAME}->[$i-1] eq "disposition" || $sth->{NAME}->[$i-1] eq "closerdispo") { print ""; } else { print "\n"; } } $i++; } } print <

" . $sth->{NAME}->[$i-1] . ":	\n"; print ""; print "
" . $sth->{NAME}->[$i-1] . ":	\n

EOT_MARKER } ####################################################################################### sub add_record { print < EOT_MARKER if ($usename) { print "" }; if ($usefname) { print "" }; if ($uselname) { print "" }; print < EOT_MARKER #$mystring = "INSERT INTO `" . $table . "` (id,phone) values (NULL,'$phone')"; $sth = $dbh->prepare("INSERT INTO `" . $table . "` (id,phone) values (NULL,'" . $phone . "')"); $sth->execute or die("Unable to excute query"); $mystring = "SELECT LAST_INSERT_ID() "; $sth = $dbh->prepare("$mystring"); $sth->execute or die("Unable to excute query"); $leadid = $sth->fetchrow_array(); print < Campaign: $tableLeadid: $leadid EOT_MARKER $mystring = "SELECT * FROM `" . $table . "` WHERE id=$leadid "; $sth = $dbh->prepare("$mystring"); $sth->execute or die("Unable to excute query"); $i=1; while (@data=$sth->fetchrow_array()) { foreach $col (@data) { if ($sth->{NAME}->[$i-1] ne "id" && $sth->{NAME}->[$i-1] ne "lastupdated" && $sth->{NAME}->[$i-1] ne "wdayl" && $sth->{NAME}->[$i-1] ne "wdayh" && $sth->{NAME}->[$i-1] ne "satl" && $sth->{NAME}->[$i-1] ne "sath" && $sth->{NAME}->[$i-1] ne "sunl" && $sth->{NAME}->[$i-1] ne "sunh" && $sth->{NAME}->[$i-1] ne "holl" && $sth->{NAME}->[$i-1] ne "holh" && $sth->{NAME}->[$i-1] ne "tzl" && $sth->{NAME}->[$i-1] ne "tzh") { if ($sth->{NAME}->[$i-1] eq "disposition" || $sth->{NAME}->[$i-1] eq "closerdispo") { print "

" . $sth->{NAME}->[$i-1] . ":

\n"; print ""; print ""; } else { print "

" . $sth->{NAME}->[$i-1] . ":

\n\n"; } } $i++; } } print <

EOT_MARKER } ####################################################################################### sub update_record { print < EOT_MARKER $updateDNC = 0; $mystring = "UPDATE `" . $table . "` SET "; foreach $key (sort keys %FORM) { if ($key ne "fs" && $key ne "table" && $key ne "leadid" && $key ne "lastupdated") { if ($key eq "disposition" && $FORM{$key} eq "8") { $updateDNC = 1; } $mystring = $mystring . $key . "='" . $FORM{$key} . "',"; if ($testmode) { print qq|\n|; } } } $mystring =~ s/\,$//; $mystring = $mystring . " WHERE id='" . $leadid . "'"; if ($testmode) { print "$mystring"; } $sth = $dbh->prepare("$mystring"); $sth->execute or die("Unable to excute query"); if ($updateDNC) { $mystring = "INSERT INTO `DNC` (`phone`) SELECT `phone` FROM `" . $table . "` WHERE id=" . $leadid . ""; $sth = $dbh->prepare("$mystring"); $sth->execute #or die("Unable to excute query"); } print <

$key:	$FORM{$key}
Record Updated!

EOT_MARKER } #################################################################################################### sub what_dbs { local($thetable) = @_; my $ret = 0; #$dbh = DBI->connect("DBI:mysql:dialer",'dialer','1234') or die("Unable to connect"); if ($thetable eq "") { my $sth = $dbh->prepare("SHOW TABLES"); $sth->execute or print "query: what_dbs failed"; while(@table = $sth->fetchrow_array) { if ($table[0] ne 'cdr' && $table[0] ne 'CDR' &&$table[0] ne 'dnc' && $table[0] ne 'DNC') { search_table($table[0]); } } $sth->finish; } else { search_table($thetable); } #$dbh->disconnect; return $ret; } #################################################################################################### sub select_dbs { local($thetable) = @_; my $ret = 0; #$dbh = DBI->connect("DBI:mysql:dialer",'dialer','1234') or die("Unable to connect"); my $sth = $dbh->prepare("SHOW TABLES"); $sth->execute or print "query: what_dbs failed"; print ""; $sth->finish; #$dbh->disconnect; return $ret; } ############################### sub search_table { local($thetable) = @_; # print " Table: $thetable "; if ($leadid ne "" || $phone ne "" || $name ne "" || $fname ne "" || $lname ne "") { $mystring = "SELECT * FROM `" . $thetable . "` WHERE 1 "; if ($leadid ne "") { $mystring = $mystring . " AND id='" . $leadid . "' "; } if ($phone ne "") { $mystring = $mystring . " AND phone LIKE '%" . $phone . "%' "; } if ($usename || $name ne "") { $mystring = $mystring . " AND name LIKE '%$name%'"; } if ($usefname || $fname ne "") { $mystring = $mystring . " AND fname LIKE '$fname%' "; } if ($uselname || $lname ne "") { $mystring = $mystring . " AND lname LIKE '%$lname%' "; } } else { $mystring = "SELECT * FROM `" . $thetable . "` WHERE phone = '9898989898' "; } $mystring = $mystring . " LIMIT 10"; $sth = $dbh->prepare("$mystring"); $sth->execute or die("Unable to excute query - " . $mystring . ""); print ""; $hit_count=0; while(@record = $sth->fetchrow_array) { $hit_count = $hit_count + 1; if ($hit_count eq 1) { print ""; print ""; print ""; if ($usename) {print ""; }; if ($usefname) {print ""; }; if ($uselname) {print ""; }; print ""; } print ""; print ""; print ""; if ($usename) {print ""; }; if ($usefname) {print ""; }; if ($uselname) {print ""; }; print ""; print ""; } if ($hit_count gt 0) { print "

$thetable
LeadID	Phone	Name	FName	Lname
$record[0]	$record[1]	$record[3]	$record[3]	$record[2]	"; print ""; print "Edit

"; } else { if ($testadd) { # add code to prompt to add a record print "

"; print "$thetable

"; print "Add New Record

"; } } $sth->finish; } ##################################### sub env { print "\n"; foreach $key (sort keys %ENV) { print qq|\n|; } print "

$key:	$ENV{$key}

\n"; } ############################################################################# sub passback { print "\n"; foreach $key (sort keys %FORM) { print qq|\n|; } print "

$key:	$FORM{$key}

\n"; } ########################## sub fetchit { if ($ENV{'REQUEST_METHOD'} eq 'GET') { @pairs = split(/&/, $ENV{'QUERY_STRING'}); } elsif ($ENV{'REQUEST_METHOD'} eq 'POST') { read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'}); @pairs = split(/&/, $buffer); } else { #&output("Error 112","Bad or Unknown Request Method"); print "Bad or Unknown Request Method"; } foreach $pair (@pairs) { local($name, $value) = split(/=/, $pair); $name =~ tr/+/ /; $name =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg; $value =~ tr/+/ /; $value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg; $value =~ s///g; $INPUT{$name} = $value; if (($name =~ /^r_/) && (!$value)) { push(@missing_fields,$name) } if (($name =~ /email/) && ($value) && (($value =~ /(@.*@)|(\.\.)|(@\.)|(\.@)|(^\.)/) || ($value !~ /^.+\@(\[?)[a-zA-Z0-9\-\.]+\.([a-zA-Z]{2,3}|[0-9]{1,3})(\]?)$/))) { push(@bad_emails,$name) } $FORM{$name} = $value; ### BEGIN DEBUG SECTION # print "Content-type: text/plain\n\n"; # print "In debug mode\n"; # print "$value = $name\n"; # exit; ### END DEBUG SECTION } } ########################## sub mail_user { open (MAIL, "|$mailprog -t") || print "Can't start mail program"; print MAIL "To: $email\n"; print MAIL "From: newuser\@dynx.net (DynX Services)\n"; print MAIL "Subject: New User Information\n\n"; print MAIL "-" x 75 . "\n\n"; print MAIL "Your request for a DynX Account was Successful. \n"; print MAIL "As soon as your request is processed, you will be \n"; print MAIL "notified by email that you can login. \n\n"; print MAIL "Username: $username \n"; print MAIL "Password: $password \n\n"; print MAIL "You should file this away somewhere safe! \n"; print MAIL "\n\n"; close (MAIL); } ########################## sub mail_dynx { open (MAIL, "|$mailprog -t") || print "Can't start mail program"; print MAIL "To: pchammer\@dynx.net\n"; print MAIL "From: newuser\@dynx.net (DynX Services)\n"; print MAIL "Subject: New User Information\n\n"; print MAIL "-" x 75 . "\n\n"; print MAIL "The following user has signed up for DynX Services. \n"; print MAIL "Username: $username \n"; print MAIL " Email: $email \n\n"; print MAIL "\n\n"; close (MAIL); } ########################## sub getdate { my $sth = $dbh->prepare("SELECT LEFT(NOW(),8)"); $sth->execute; ( $now ) = $sth->fetchrow(); $now =~ s/://g; $now =~ s/-//g; $now =~ s/\s//g; return $now; } ########################## sub getdatetime { my $sth = $dbh->prepare("SELECT NOW()"); $sth->execute; ( $now ) = $sth->fetchrow(); $now =~ s/://g; $now =~ s/-//g; $now =~ s/\s//g; return $now; } ######################### sub error { local($errornum, $error) = @_; print "

The Following Errors Were Encountered!"; print "
ERROR CODE: $errornum"; print "
ERROR TYPE: $error

"; print "

Press your browser's BACK button to Go back to the form and fix them. Thank you."; print "

$footer"; exit; } #*************************

LeadID:
Phone:
Name:
FName:
LName:
Table:	EOT_MARKER #
Table:

	Fill-in at least 1 box (Limit 10 per table shown)