Directory of F:\old\miksup\steve 12/05/2008 02:44 PM. 12/05/2008 02:44 PM .. 12/05/2008 02:50 PM data_new 12/05/2008 02:53 PM data_uploaded 12/05/2008 02:53 PM data_error_files 12/05/2008 02:55 PM 2,765 krud_ftp.txt 12/05/2008 02:56 PM 1,042 aaa.txt 12/05/2008 02:56 PM 21,058 comma.pl 12/05/2008 02:56 PM 20,570 comma_04.pl 12/05/2008 02:56 PM 20,368 comma_base.pl 12/05/2008 02:56 PM 7,615 comma_file_upload.pl 12/05/2008 02:56 PM 7,323 comma_file_upload_base.pl 12/05/2008 02:56 PM 869 csv.txt 12/05/2008 02:56 PM 20,987 getfiles.pl 12/05/2008 02:56 PM 20,831 getfiles_00.pl 12/05/2008 02:56 PM 1,177 index.html 12/05/2008 02:56 PM 2,301 remotehost.pl 12/05/2008 02:56 PM 1,300 remotehost_00.pl 12/05/2008 02:56 PM 57,142 richard_comma.pl 12/05/2008 02:56 PM 48,190 richard_comma_41.pl 12/05/2008 02:56 PM 47,742 richard_comma_42.pl 12/05/2008 02:56 PM 48,572 richard_comma_43.pl 12/05/2008 02:56 PM 48,575 richard_comma_44.pl 12/05/2008 02:56 PM 48,544 richard_comma_45.pl 12/05/2008 02:56 PM 48,679 richard_comma_46.pl 12/05/2008 02:56 PM 48,679 richard_comma_47.pl 12/05/2008 02:56 PM 48,702 richard_comma_48.pl 12/05/2008 02:56 PM 48,819 richard_comma_49.pl 12/05/2008 02:56 PM 48,819 richard_comma_50.pl 12/05/2008 02:56 PM 48,819 richard_comma_51.pl 12/05/2008 02:56 PM 53,270 richard_comma_52.pl 12/05/2008 02:57 PM 53,394 richard_comma_53.pl 12/05/2008 02:57 PM 52,973 richard_comma_54.pl 12/05/2008 02:57 PM 52,973 richard_comma_55.pl 12/05/2008 02:57 PM 53,345 richard_comma_56.pl 12/05/2008 02:57 PM 50,527 richard_comma_57.pl 12/05/2008 02:57 PM 52,412 richard_comma_58.pl 12/05/2008 02:57 PM 52,517 richard_comma_59.pl 12/05/2008 02:57 PM 52,517 richard_comma_59_tab.pl 12/05/2008 02:57 PM 52,977 richard_comma_60.pl 12/05/2008 02:57 PM 53,617 richard_comma_61.pl 12/05/2008 02:57 PM 53,617 richard_comma_62.pl 12/05/2008 02:57 PM 55,994 richard_comma_63.pl 12/05/2008 02:57 PM 55,994 richard_comma_64.pl 12/05/2008 02:57 PM 57,139 richard_comma_65.pl 12/05/2008 02:57 PM 19,965 richard_comma_base.pl 12/05/2008 02:57 PM 23,037 richard_getfile.pl 12/05/2008 02:57 PM 21,505 richard_getfile_02.pl 12/05/2008 02:57 PM 21,505 richard_getfile_03.pl 12/05/2008 02:57 PM 21,908 richard_getfile_04.pl 12/05/2008 02:57 PM 23,037 richard_getfile_05.pl 12/05/2008 02:57 PM 23,037 richard_getfile_06.pl 12/05/2008 02:57 PM 266 richard_password.pl 12/05/2008 02:57 PM 46,351 richard_search.pl 12/05/2008 02:57 PM 34,414 richard_search_41.pl 12/05/2008 02:57 PM 35,879 richard_search_42.pl 12/05/2008 02:57 PM 35,926 richard_search_43.pl 12/05/2008 02:57 PM 36,394 richard_search_44.pl 12/05/2008 02:57 PM 36,761 richard_search_44_lockout.pl 12/05/2008 02:57 PM 36,394 richard_search_45.pl 12/05/2008 02:57 PM 36,394 richard_search_46.pl 12/05/2008 02:58 PM 38,805 richard_search_47.pl 12/05/2008 02:58 PM 38,806 richard_search_48.pl 12/05/2008 02:58 PM 38,806 richard_search_49.pl 12/05/2008 02:58 PM 39,183 richard_search_50.pl 12/05/2008 02:58 PM 39,581 richard_search_51.pl 12/05/2008 02:58 PM 39,938 richard_search_52.pl 12/05/2008 02:58 PM 42,450 richard_search_53.pl 12/05/2008 02:58 PM 42,714 richard_search_54.pl 12/05/2008 02:58 PM 42,714 richard_search_55.pl 12/05/2008 02:58 PM 43,189 richard_search_56.pl 12/05/2008 02:58 PM 43,189 richard_search_57.pl 12/05/2008 02:58 PM 43,162 richard_search_58.pl 12/05/2008 02:58 PM 43,162 richard_search_59.pl 12/05/2008 02:58 PM 43,561 richard_search_60.pl 12/05/2008 02:58 PM 43,561 richard_search_61.pl 12/05/2008 02:58 PM 43,728 richard_search_62.pl 12/05/2008 02:58 PM 43,733 richard_search_63.pl 12/05/2008 02:58 PM 43,733 richard_search_64.pl 12/05/2008 02:58 PM 43,800 richard_search_65.pl 12/05/2008 02:58 PM 46,046 richard_search_66.pl 12/05/2008 02:58 PM 47,394 richard_search_67.pl 12/05/2008 02:58 PM 47,755 richard_search_68.pl 12/05/2008 02:58 PM 47,782 richard_search_69.pl 12/05/2008 02:58 PM 47,782 richard_search_70.pl 12/05/2008 02:59 PM 47,782 richard_search_70_excel.pl 12/05/2008 02:59 PM 45,097 richard_search_71.pl 12/05/2008 02:59 PM 45,097 richard_search_72.pl 12/05/2008 02:59 PM 46,283 richard_search_73.pl 12/05/2008 02:59 PM 46,283 richard_search_74.pl 12/05/2008 02:59 PM 46,283 richard_search_75.pl 12/05/2008 02:59 PM 46,351 richard_search_76.pl 12/05/2008 02:59 PM 4,666 richard_specs.html 12/05/2008 03:15 PM 0 krapola 89 File(s) 3,337,943 bytes 5 Dir(s) 802,111,488 bytes free
#!/usr/bin/perl
#
# i wrote this as an answer to an interview question
# some guy who was hiring programmers sent me
#
require CGI;
use CGI;
$cgi = new CGI;
$f=$cgi->param('f');
print "Content-type: text/html", "\n\n";
print "<html>\n";
#
# the names of the fields they MUST select
# and the counts of how many time each field has been selected
#
#@thenames=( "Name", "Add", "City", "State", "Zip", "Phone");
#@thecounts=( 0, 0, 0, 0, 0, 0);
@thenames=( "name", "city", "state", "address", "zip", "phone_work",
"phone_home_1",
"phone_home_2",
"property_type",
"value_current",
"value_original",
"first_mort_balance",
"first_mort_rate",
"first_mort_rate_type",
"payment",
"behind",
"credit",
"employer",
"years_there",
"income_household",
"best_time",
"loan_amount",
"loan_type",
"e_mail",
"LTV",
"date_time",
"IP_address"
);
@thecounts=(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
#$database_to_use="temp";
#
# print header stuff
#
#
# get the data they entered in the text boxes
#
$database_to_use=$cgi->param('table');
$database_to_use="leads";
$filename=$cgi->param('filename');
#$filename="csv.txt";
$datadisplayed=$cgi->param('datadisplayed');
#$datadisplayed="xx";
$recordstodisplay=$cgi->param('recordstodisplay');
$recordstodisplay=10;
$f01=$cgi->param('f01');
$f02=$cgi->param('f02');
$f03=$cgi->param('f03');
$f04=$cgi->param('f04');
$f05=$cgi->param('f05');
$f06=$cgi->param('f06');
$f07=$cgi->param('f07');
$f08=$cgi->param('f08');
$f09=$cgi->param('f09');
$f10=$cgi->param('f10');
$f11=$cgi->param('f11');
$f12=$cgi->param('f12');
$f13=$cgi->param('f13');
$f14=$cgi->param('f14');
$f15=$cgi->param('f15');
$f16=$cgi->param('f16');
$f17=$cgi->param('f17');
$f18=$cgi->param('f18');
$f19=$cgi->param('f19');
$f20=$cgi->param('f20');
$f21=$cgi->param('f21');
$f22=$cgi->param('f22');
$f23=$cgi->param('f23');
$f24=$cgi->param('f24');
$f25=$cgi->param('f25');
$f26=$cgi->param('f26');
$f27=$cgi->param('f27');
$f28=$cgi->param('f28');
$f29=$cgi->param('f29');
$f30=$cgi->param('f30');
#
# if they entered any data write it out to the SQL tables
#
if ( $datadisplayed eq "") {
#
# if the data has not been displayed
# open the file and display the data
#
if ( $database_to_use eq "temp" ) {
print "<h1 style=\"color:red;\">Test database \"temp\"</h1>";
print "<h3 style=\"color:red;\" align=\"center\">This is a TEST data base!! Any data you enter WILL be deleted!!!!</h3>";
#print "<form action=\"comma.pl\" method=\"post\">";
}
else {
if ( $database_to_use eq "leads" ) {
print "<h1>Official database \"leads\"</h1>";
#print "<form action=\"prod_comma.pl\" method=\"post\">";
}
else {
print "<h1> ERROR unknown database \"$database_to_use\"</h1>";
exit;
}
}
#print "records to display =$recordstodisplay<p>";
#print "<form action=\"comma.pl&table=$database_to_use\" method=\"post\">";
print "<form action=\"comma.pl\" method=\"post\">";
print "<input type=\"submit\" name=submit value=\"Insert leads into the SQL table '$database_to_use'\">";
print "Processing file:<span style=\"font-size: 130%;\">$filename</span><p>";
print "<table border=1>";
print "<tr>";
for ($i=1;$i<31;$i++) {
#
# add a leading zero to the field name if it is less then 10
#
$lead="";
if ($i < 10 ) {
$lead="0";
}
print "<td>";
&select_the_field("$lead$i");
print "</td>";
}
print "</tr>";
print "<tr>";
for ($i=1;$i<31;$i++) {
#
# display the field number so when we print error messages they make sense
#
$lead="";
if ($i < 10 ) {
$lead="0";
}
print "<td>
<span style=\"background-color:rgb(255,255,0)\">$lead$i</span></td>";
}
print "</tr>";
#
# open the file to process
#
#open(IN,'krudc.txt');
#open(IN,'leads_data_files/'.$filename);
open(IN,$filename);
#
# read each record
#
$recordsread=0;
while(<IN>) {
#while(<IN> && (
#$recordsread
#<=
#$datadisplayed
#)) {
chomp($_);
$f30="";
$f29="";
$f28="";
$f27="";
$f26="";
$f25="";
$f24="";
$f23="";
$f22="";
$f21="";
$f20="";
$recordsread++;
#if ($recordsread <= $recordstodisplay || $recordstodisplay eq "all") {
if ($recordsread <= 300 || $recordstodisplay eq "all") {
#
# seperate each record into fields delimited by commas
#
($f01, $f02, $f03 , $f04 , $f05 , $f06 , $f07 , $f08 , $f09 , $f10,
$f11, $f12, $f13 , $f14 , $f15 , $f16 , $f17 , $f18 , $f19 , $f20,
$f21, $f22, $f23 , $f24 , $f25 , $f26 , $f27 , $f28 , $f29 , $f30
) = split(',',$_);
#
# display the 30 fields
# usually there will be less
#
print "<tr valign=\"top\">\n";
print "<td><nobr>$f01 </nobr></td>\n";
print "<td><nobr>$f02</nobr></td>\n";
print "<td><nobr>$f03</nobr></td>\n";
print "<td><nobr>$f04</nobr></td>\n";
print "<td>$f05 </td>\n";
print "<td>$f06 </td>\n";
print "<td>$f07 </td>\n";
print "<td>$f08 </td>\n";
print "<td>$f09 </td>\n";
print "<td>$f10 </td>\n";
print "<td>$f11 </td>\n";
print "<td>$f12 </td>\n";
print "<td>$f13 </td>\n";
print "<td>$f14 </td>\n";
print "<td>$f15 </td>\n";
print "<td>$f16 </td>\n";
print "<td>$f17 </td>\n";
print "<td>$f18 </td>\n";
print "<td>$f19 </td>\n";
print "<td>$f20 </td>\n";
print "<td>$f21 </td>\n";
print "<td>$f22 </td>\n";
print "<td>$f23 </td>\n";
print "<td>$f24 </td>\n";
print "<td>$f25 </td>\n";
print "<td>$f26 </td>\n";
print "<td>$f27 </td>\n";
print "<td>$f28 </td>\n";
print "<td>$f29 </td>\n";
print "<td>$f30 </td>\n";
print "</tr>\n";
}
}
close(IN);
print "</table>";
#
# dont forget about these HIDDEN data values :)
# i use them to keep track of if we have read
# the file and displayed it.
print "<input type=\"hidden\" name=\"datadisplayed\" value=\"y\">";
print "<input type=\"hidden\" name=\"filename\" value=\"$filename\">";
print "<input type=\"hidden\" name=\"table\" value=\"$database_to_use\">";
print "</form>";
}
else {
#print "process the data <p>";
if ( $database_to_use eq "temp" ) {
print "<h1 style=\"color:red;\">Test database \"temp\"</h1>";
print "<h3 style=\"color:red;\" align=\"center\">This is a TEST data base!! Any data you enter WILL be deleted!!!!</h3>";
}
print "Processing file:<span style=\"font-size: 130%;\">$filename</span><p>";
#print "datadisplayed='$datadisplayed'<p>";
#print " f01='$f01' f02='$f02' f03='$f03' f04='$f04' f05='$f05' f06='$f06' f07='$f07' f08='$f08' f09='$f09' f10='$f10' f11='$f11' f12='$f12' f13='$f13' f14='$f14' f15='$f15' f16='$f16' f17='$f17' f18='$f18' f19='$f19' f20='$f20' f21='$f21' f22='$f22' f23='$f23' f24='$f24' f25='$f25' f26='$f26' f27='$f27' f28='$f28' f29='$f29' f30='$f30' <p>";
$used=-1;
$errors=0;
#
# get all the items they selected and place them
# in an array
# after placing the items in an array we will
# loop thru the arrays to verify that they
# selected each item one and only one time
#
if ($f01 ne '') {
$used++;
$data[$used]=$f01;
$slot[$used]=1;
}
if ($f02 ne '') {
$used++;
$data[$used]=$f02;
$slot[$used]=2;
}
if ($f03 ne '') {
$used++;
$data[$used]=$f03;
$slot[$used]=3;
}
if ($f04 ne '') {
$used++;
$data[$used]=$f04;
$slot[$used]=4;
}
if ($f05 ne '') {
$used++;
$data[$used]=$f05;
$slot[$used]=5;
}
if ($f06 ne '') {
$used++;
$data[$used]=$f06;
$slot[$used]=6;
}
if ($f07 ne '') {
$used++;
$data[$used]=$f07;
$slot[$used]=7;
}
if ($f08 ne '') {
$used++;
$data[$used]=$f08;
$slot[$used]=8;
}
if ($f09 ne '') {
$used++;
$data[$used]=$f09;
$slot[$used]=9;
}
if ($f10 ne '') {
$used++;
$data[$used]=$f10;
$slot[$used]=10;
}
if ($f11 ne '') {
$used++;
$data[$used]=$f11;
$slot[$used]=11;
}
if ($f12 ne '') {
$used++;
$data[$used]=$f12;
$slot[$used]=12;
}
if ($f13 ne '') {
$used++;
$data[$used]=$f13;
$slot[$used]=13;
}
if ($f14 ne '') {
$used++;
$data[$used]=$f14;
$slot[$used]=14;
}
if ($f15 ne '') {
$used++;
$data[$used]=$f15;
$slot[$used]=15;
}
if ($f16 ne '') {
$used++;
$data[$used]=$f16;
$slot[$used]=16;
}
if ($f17 ne '') {
$used++;
$data[$used]=$f17;
$slot[$used]=17;
}
if ($f18 ne '') {
$used++;
$data[$used]=$f18;
$slot[$used]=18;
}
if ($f19 ne '') {
$used++;
$data[$used]=$f19;
$slot[$used]=19;
}
if ($f20 ne '') {
$used++;
$data[$used]=$f20;
$slot[$used]=20;
}
if ($f21 ne '') {
$used++;
$data[$used]=$f21;
$slot[$used]=21;
}
if ($f22 ne '') {
$used++;
$data[$used]=$f22;
$slot[$used]=22;
}
if ($f23 ne '') {
$used++;
$data[$used]=$f23;
$slot[$used]=23;
}
if ($f24 ne '') {
$used++;
$data[$used]=$f24;
$slot[$used]=24;
}
if ($f25 ne '') {
$used++;
$data[$used]=$f25;
$slot[$used]=25;
}
if ($f26 ne '') {
$used++;
$data[$used]=$f26;
$slot[$used]=26;
}
if ($f27 ne '') {
$used++;
$data[$used]=$f27;
$slot[$used]=27;
}
if ($f28 ne '') {
$used++;
$data[$used]=$f28;
$slot[$used]=28;
}
if ($f29 ne '') {
$used++;
$data[$used]=$f29;
$slot[$used]=29;
}
if ($f30 ne '') {
$used++;
$data[$used]=$f30;
$slot[$used]=30;
}
if ($used == -1 ) {
print "ERROR you didn't select any columns<br>";
$errors++;
}
#if ($used >= 0 && $used <= 4 ) {
if ($used >= 0 && $used < $#thenames ) {
$selected=$used+1;
#print "ERROR you must select 6 columns. You only selected $selected columns<br>";
print "ERROR you must select ".($#thenames+1)." columns. You only selected $selected columns<br>";
$errors++;
}
#if ($used > 5 ) {
if ($used > $#thenames ) {
$selected=$used+1;
print "ERROR you selected $selected columns. You are only allowed to select 5 columns<br>";
$errors++;
}
my $i, $k;
#
# they must select each item once and only once
# count how many times they selected each item
#
for ($i=0;$i<=$#thenames;$i++) {
#print "processing $thenames[$i]<p>";
for ($k=0;$k<=$used;$k++) {
#print "... $data[$k]<p>";
if ($data[$k] eq $thenames[$i] ) {
$thecount[$i]++;
}
}
}
#
# they must select each item at least once
# if they didnt select an item print an error message
#
for ($i=0;$i<=$#thenames;$i++) {
if ($thecount[$i] == 0 ) {
print "Error you did not select any columns for $thenames[$i]<br>";
$errors++;
}
}
#
# if they selected an item more then one time that is an error
# print an error message for each item selected two or more times
# and tell them which columns they selected the item on
#
for ($i=0;$i<=$#thenames;$i++) {
if ($thecount[$i] > 1 ) {
print "Error you selected $thecount[$i] columns for $thenames[$i]. They are columns ";
$errors++;
for ($k=0;$k<=$used;$k++) {
if ($data[$k] eq $thenames[$i] ) {
print "$slot[$k] ";
}
}
print "<br>";
}
}
if ($errors > 0 ) {
print "<h1>ERRORS the data was NOT added to the SQL tables</h1>";
exit;
}
use DBI;
$username = 'leads';
$password = 'connect';
$database = 'leads';
$hostname = 'localhost';
print "Connecting -- User=$username Database=$database Hose=$hostname<p>";
#$dbh = DBI->connect("dbi:mysql:database=$database;" .
# "host=$hostname;port=3306", $username, $password);
#
# get the date and time of the insert
# and maybe a sequence number of the insert
# so if the user screws things up and inserts
# a bunch of garbage date we can back out the
# garbage data by deleting everything with this
# sequence number
my @current_time=localtime();
my $this_date;
my $this_month;
my $this_year;
my $this_insert_number;
my $sequence_number_hits;
my $inserts_today=1;
$this_date=$current_time[3];
$this_month=$current_time[4]+1;
$this_year=($current_time[5]-100)+2000;
if ($this_date < 10 ) {
$this_date="0".$this_date;
}
if ($this_month < 10 ) {
$this_month="0".$this_month;
}
#
# use the yyyy/mm/dd for the first sequence number
#
$this_insert_number="$this_year/$this_month/$this_date";
#
# loop till we find a sequence number that isnt used
#
$sequence_number_hits=1;
#while ( $sequence_number_hits > 0) {
# #$SQL= "select count(*) as insert_seq_num from temp where insert_seq_num='$this_insert_number';";
# $SQL= "select count(*) as insert_seq_num from $database_to_use where insert_seq_num='$this_insert_number';";
# $Select = $dbh->prepare($SQL);
# $Select->execute();
# while($Row=$Select->fetchrow_hashref)
# {
# #
# # how many of these sequence numbers exist?
# #
# $sequence_number_hits=$Row->{insert_seq_num};
# }
# #print "SEQ HITS=$sequence_number_hits<p>";
# #
# # if the sequence number exists add one to the times today
# # we have done inserts and use that for the next sequence
# # number.
# # this assumes that only ONE person is adding data!
# # if more then one person is adding data we will have to
# # go to record locking to get a unique sequence number
# #
# if ($sequence_number_hits != 0 ) {
# #
# # that sequence number already exists
# # generate a new one by adding one to the old one
# #
# $inserts_today++;
# $inserts_today_formated=$inserts_today;
# while (length($inserts_today_formated) < 4 ) {
# $inserts_today_formated="0".$inserts_today_formated;
# }
# #$this_insert_number="$this_year/$this_month/$this_date-$inserts_today";
# $this_insert_number="$this_year/$this_month/$this_date-$inserts_today_formated";
# }
#}
#print "Insert sequence number is $this_insert_number<br>";
open(IN,$filename);
#open(IN,'leads_data_files/'.$filename);
#
# read each record
#
my $theslot;
my $name;
my $add;
my $city;
my $state;
my $zip;
my $phone;
my $records_processed=0;
my $sql_inserts=0;
my $sql_errors=0;
while(<IN>) {
$records_processed++;
#
# seperate each record into fields delimited by commas
#
@the_data_fields=split(',',$_);
$name="";
$add="";
$city="";
$state="";
$zip="";
$phone="";
#
# get the name
#
for ($i=0;$i<=$used;$i++) {
#if ($data[$i] eq "Name") {
if ($data[$i] eq $thenames[0]) {
$theslot=$slot[$i];
$theslot--;
#print "Name match at $i $theslot<br>";
$name=$the_data_fields[$theslot];
$name=clean_sql_data($name);
#print "Name=$name<br>";
}
}
#
# get the address
#
for ($i=0;$i<=$used;$i++) {
#if ($data[$i] eq "Add") {
if ($data[$i] eq $thenames[1]) {
$theslot=$slot[$i];
$theslot--;
#print "Add match at $i $theslot<br>";
$add=$the_data_fields[$theslot];
$add=clean_sql_data($add);
#print "add=$add<br>";
}
}
#
# get the city
#
for ($i=0;$i<=$used;$i++) {
#if ($data[$i] eq "City") {
if ($data[$i] eq $thenames[2]) {
$theslot=$slot[$i];
$theslot--;
#print "city match at $i $theslot<br>";
$city=$the_data_fields[$theslot];
$city=clean_sql_data($city);
#print "city=$city<br>";
}
}
#
# get the state
#
for ($i=0;$i<=$used;$i++) {
#if ($data[$i] eq "State") {
if ($data[$i] eq $thenames[3]) {
$theslot=$slot[$i];
$theslot--;
#print "state match at $i $theslot<br>";
$state=$the_data_fields[$theslot];
$state=clean_sql_data($state);
#print "state=$state<br>";
#print "state=$state<br>";
}
}
#
# get the zip
#
for ($i=0;$i<=$used;$i++) {
#if ($data[$i] eq "Zip") {
if ($data[$i] eq $thenames[4]) {
$theslot=$slot[$i];
$theslot--;
#print "zip match at $i $theslot<br>";
$zip=$the_data_fields[$theslot];
$zip=clean_sql_data($zip);
#print "zip=$zip<br>";
}
}
#
# get the phone
#
for ($i=0;$i<=$used;$i++) {
#if ($data[$i] eq "Phone") {
if ($data[$i] eq $thenames[5]) {
$theslot=$slot[$i];
$theslot--;
#print "phone match at $i $theslot<br>";
$phone=$the_data_fields[$theslot];
$phone=clean_sql_data($phone);
#print "phone=$phone<br>";
}
}
#
# add the data to the SQL table here
#
#$SQL= "insert into temp (businessName, address, city, state, zip, phone, insert_seq_num)" .
#$SQL= "insert into $database_to_use (businessName, address, city, state, zip, phone, insert_seq_num)" .
$SQL= "insert into $database_to_use (businessName, address, city, state, zip, phone, insert_seq_num, insert_file_name)<br>" .
" values('$name',
'$add',
'$city',
'$state',
'$zip',
'$phone',
'$this_insert_number',
'$filename'
)";
print "$SQL<p>";
#$InsertRecord = $dbh->do($SQL);
#if($InsertRecord){
$sql_inserts++;
# #print "Insert Success!!!!!";
#}
#else{
# print "Insert Failure<br/>$DBI::errstr";
# $sql_errors++;
#}
}
close(IN);
print "$records_processed records read from comma delimited file <span style=\"font-size: 130%;\">$filename</span><br>";
#print "$sql_inserts records inserted into SQL table temp <br>";
#print "$sql_errors ERRORS inserting SQL data<br>";
print "$sql_inserts records inserted into SQL table $database_to_use<br>";
print "$sql_errors ERRORS inserting SQL table $database_to_use<br>";
if ($sql_errors > 0 ) {
print "<h1> ERROR!!! $sql_errors sql insert errors! Call Mike</h1>";
print "move the file to error sub directory</h1>";
&move_the_file($filename, 'leads_data_files', 'leads_error_files');
}
else {
&move_the_file($filename, 'leads_data_files', 'leads_uploaded_files');
print "<p><a href=\"comma_file_upload.pl?databasetouse=$database_to_use\">Upload more files to database \"$database_to_use\"</a>";
}
}
exit;
sub select_the_field {
#
# this function defines a pull down menu for
# each field so the user can select what type of field
# it is.
# once we have gotten the specs down we might want to
# convert it to JavaScript so that at each field is
# added it is removed from the list
#
my $field=@_[0];
#
# the SQL data base has fields for
# date and zip+4
# the Mr. Caery says he will never use
# either of those fields. So
# I will not add any logic to process them
#
#
# they will always be able to select a SPACE
# which means the column is not used
#
print "<select name=\"f".$field."\">
<option value=\"\" selected=\"selected\"></option>";
#
# print the options they can select from the menu
#
my $i;
for ($i=0;$i <= $#thenames;$i++) {
print "<option value=\"".$thenames[$i]."\">".$thenames[$i]."</option>";
}
print "</select>";
return;
}
sub clean_sql_data {
my $name=@_[0];
$name=~s/'//g;
$name=~s/"//g;
$name=~s/>//g;
$name=~s/<//g;
return $name;
}
sub move_the_file {
my $file=@_[0];
my $in_dir=@_[1];
my $out_dir=@_[2];
my $new_file="$out_dir/$file";
my $count=0;
if(! -d $out_dir) {
#print "Error output directory '$dir' does not exists<p>";
#exit;
}
if(! -e "$in_dir/$file") {
#print "Error input file '$in_dir/$file' does not exists<p>";
#exit;
}
#while(-e $new_file) {
# #print "new file '$new_file' exists<p>";
# $count++;
# $new_file="$out_dir/$file-$count";
#}
#print "move file '$in_dir/$file' to subdirectory '$dir'<p>";
#print "move file '$in_dir/$file' to subdirectory $new_file'<p>";
#$rc=rename("$in_dir/$file","$new_file");
#print "rc=$rc<p>";
return;
}
#!/usr/bin/perl
#
# i wrote this as an answer to an interview question
# some guy who was hiring programmers sent me
#
require CGI;
use CGI;
$cgi = new CGI;
$f=$cgi->param('f');
print "Content-type: text/html", "\n\n";
print "<html>\n";
#
# the names of the fields they MUST select
# and the counts of how many time each field has been selected
#
@thenames=( "Name", "Add", "City", "State", "Zip", "Phone");
@thecounts=( 0, 0, 0, 0, 0, 0);
#$database_to_use="temp";
#
# print header stuff
#
#
# get the data they entered in the text boxes
#
$database_to_use=$cgi->param('table');
$database_to_use="leads";
#$filename=$cgi->param('filename');
$filename="csv.txt";
$datadisplayed=$cgi->param('datadisplayed');
#$datadisplayed="xx";
$recordstodisplay=$cgi->param('recordstodisplay');
$recordstodisplay=10;
$f01=$cgi->param('f01');
$f02=$cgi->param('f02');
$f03=$cgi->param('f03');
$f04=$cgi->param('f04');
$f05=$cgi->param('f05');
$f06=$cgi->param('f06');
$f07=$cgi->param('f07');
$f08=$cgi->param('f08');
$f09=$cgi->param('f09');
$f10=$cgi->param('f10');
$f11=$cgi->param('f11');
$f12=$cgi->param('f12');
$f13=$cgi->param('f13');
$f14=$cgi->param('f14');
$f15=$cgi->param('f15');
$f16=$cgi->param('f16');
$f17=$cgi->param('f17');
$f18=$cgi->param('f18');
$f19=$cgi->param('f19');
$f20=$cgi->param('f20');
$f21=$cgi->param('f21');
$f22=$cgi->param('f22');
$f23=$cgi->param('f23');
$f24=$cgi->param('f24');
$f25=$cgi->param('f25');
$f26=$cgi->param('f26');
$f27=$cgi->param('f27');
$f28=$cgi->param('f28');
$f29=$cgi->param('f29');
$f30=$cgi->param('f30');
#
# if they entered any data write it out to the SQL tables
#
if ( $datadisplayed eq "") {
#
# if the data has not been displayed
# open the file and display the data
#
if ( $database_to_use eq "temp" ) {
print "<h1 style=\"color:red;\">Test database \"temp\"</h1>";
print "<h3 style=\"color:red;\" align=\"center\">This is a TEST data base!! Any data you enter WILL be deleted!!!!</h3>";
#print "<form action=\"comma.pl\" method=\"post\">";
}
else {
if ( $database_to_use eq "leads" ) {
print "<h1>Official database \"leads\"</h1>";
#print "<form action=\"prod_comma.pl\" method=\"post\">";
}
else {
print "<h1> ERROR unknown database \"$database_to_use\"</h1>";
exit;
}
}
#print "records to display =$recordstodisplay<p>";
#print "<form action=\"comma.pl&table=$database_to_use\" method=\"post\">";
print "<form action=\"comma_base.pl\" method=\"post\">";
print "<input type=\"submit\" name=submit value=\"Insert leads into the SQL table '$database_to_use'\">";
print "Processing file:<span style=\"font-size: 130%;\">$filename</span><p>";
print "<table border=1>";
print "<tr>";
for ($i=1;$i<31;$i++) {
#
# add a leading zero to the field name if it is less then 10
#
$lead="";
if ($i < 10 ) {
$lead="0";
}
print "<td>";
&select_the_field("$lead$i");
print "</td>";
}
print "</tr>";
print "<tr>";
for ($i=1;$i<31;$i++) {
#
# display the field number so when we print error messages they make sense
#
$lead="";
if ($i < 10 ) {
$lead="0";
}
print "<td>
<span style=\"background-color:rgb(255,255,0)\">$lead$i</span></td>";
}
print "</tr>";
#
# open the file to process
#
#open(IN,'krudc.txt');
#open(IN,'leads_data_files/'.$filename);
open(IN,$filename);
#
# read each record
#
$recordsread=0;
while(<IN>) {
#while(<IN> && (
#$recordsread
#<=
#$datadisplayed
#)) {
$recordsread++;
if ($recordsread <= $recordstodisplay || $recordstodisplay eq "all") {
#
# seperate each record into fields delimited by commas
#
($f01, $f02, $f03 , $f04 , $f05 , $f06 , $f07 , $f08 , $f09 , $f10,
$f11, $f12, $f13 , $f14 , $f15 , $f16 , $f17 , $f18 , $f19 , $f20,
$f21, $f22, $f23 , $f24 , $f25 , $f26 , $f27 , $f28 , $f29 , $f30
) = split(',',$_);
#
# display the 30 fields
# usually there will be less
#
print "<tr valign=\"top\">\n";
print "<td>$f01 </td>\n";
print "<td><nobr>$f02</nobr></td>\n";
print "<td><nobr>$f03</nobr></td>\n";
print "<td><nobr>$f04</nobr></td>\n";
print "<td>$f05 </td>\n";
print "<td>$f06 </td>\n";
print "<td>$f07 </td>\n";
print "<td>$f08 </td>\n";
print "<td>$f09 </td>\n";
print "<td>$f10 </td>\n";
print "<td>$f11 </td>\n";
print "<td>$f12 </td>\n";
print "<td>$f13 </td>\n";
print "<td>$f14 </td>\n";
print "<td>$f15 </td>\n";
print "<td>$f16 </td>\n";
print "<td>$f17 </td>\n";
print "<td>$f18 </td>\n";
print "<td>$f19 </td>\n";
print "<td>$f20 </td>\n";
print "<td>$f21 </td>\n";
print "<td>$f22 </td>\n";
print "<td>$f23 </td>\n";
print "<td>$f24 </td>\n";
print "<td>$f25 </td>\n";
print "<td>$f26 </td>\n";
print "<td>$f27 </td>\n";
print "<td>$f28 </td>\n";
print "<td>$f29 </td>\n";
print "<td>$f30 </td>\n";
print "</tr>\n";
}
}
close(IN);
print "</table>";
#
# dont forget about these HIDDEN data values :)
# i use them to keep track of if we have read
# the file and displayed it.
print "<input type=\"hidden\" name=\"datadisplayed\" value=\"y\">";
print "<input type=\"hidden\" name=\"filename\" value=\"$filename\">";
print "<input type=\"hidden\" name=\"table\" value=\"$database_to_use\">";
print "</form>";
}
else {
#print "process the data <p>";
if ( $database_to_use eq "temp" ) {
print "<h1 style=\"color:red;\">Test database \"temp\"</h1>";
print "<h3 style=\"color:red;\" align=\"center\">This is a TEST data base!! Any data you enter WILL be deleted!!!!</h3>";
}
print "Processing file:<span style=\"font-size: 130%;\">$filename</span><p>";
#print "datadisplayed='$datadisplayed'<p>";
#print " f01='$f01' f02='$f02' f03='$f03' f04='$f04' f05='$f05' f06='$f06' f07='$f07' f08='$f08' f09='$f09' f10='$f10' f11='$f11' f12='$f12' f13='$f13' f14='$f14' f15='$f15' f16='$f16' f17='$f17' f18='$f18' f19='$f19' f20='$f20' f21='$f21' f22='$f22' f23='$f23' f24='$f24' f25='$f25' f26='$f26' f27='$f27' f28='$f28' f29='$f29' f30='$f30' <p>";
$used=-1;
$errors=0;
#
# get all the items they selected and place them
# in an array
# after placing the items in an array we will
# loop thru the arrays to verify that they
# selected each item one and only one time
#
if ($f01 ne '') {
$used++;
$data[$used]=$f01;
$slot[$used]=1;
}
if ($f02 ne '') {
$used++;
$data[$used]=$f02;
$slot[$used]=2;
}
if ($f03 ne '') {
$used++;
$data[$used]=$f03;
$slot[$used]=3;
}
if ($f04 ne '') {
$used++;
$data[$used]=$f04;
$slot[$used]=4;
}
if ($f05 ne '') {
$used++;
$data[$used]=$f05;
$slot[$used]=5;
}
if ($f06 ne '') {
$used++;
$data[$used]=$f06;
$slot[$used]=6;
}
if ($f07 ne '') {
$used++;
$data[$used]=$f07;
$slot[$used]=7;
}
if ($f08 ne '') {
$used++;
$data[$used]=$f08;
$slot[$used]=8;
}
if ($f09 ne '') {
$used++;
$data[$used]=$f09;
$slot[$used]=9;
}
if ($f10 ne '') {
$used++;
$data[$used]=$f10;
$slot[$used]=10;
}
if ($f11 ne '') {
$used++;
$data[$used]=$f11;
$slot[$used]=11;
}
if ($f12 ne '') {
$used++;
$data[$used]=$f12;
$slot[$used]=12;
}
if ($f13 ne '') {
$used++;
$data[$used]=$f13;
$slot[$used]=13;
}
if ($f14 ne '') {
$used++;
$data[$used]=$f14;
$slot[$used]=14;
}
if ($f15 ne '') {
$used++;
$data[$used]=$f15;
$slot[$used]=15;
}
if ($f16 ne '') {
$used++;
$data[$used]=$f16;
$slot[$used]=16;
}
if ($f17 ne '') {
$used++;
$data[$used]=$f17;
$slot[$used]=17;
}
if ($f18 ne '') {
$used++;
$data[$used]=$f18;
$slot[$used]=18;
}
if ($f19 ne '') {
$used++;
$data[$used]=$f19;
$slot[$used]=19;
}
if ($f20 ne '') {
$used++;
$data[$used]=$f20;
$slot[$used]=20;
}
if ($f21 ne '') {
$used++;
$data[$used]=$f21;
$slot[$used]=21;
}
if ($f22 ne '') {
$used++;
$data[$used]=$f22;
$slot[$used]=22;
}
if ($f23 ne '') {
$used++;
$data[$used]=$f23;
$slot[$used]=23;
}
if ($f24 ne '') {
$used++;
$data[$used]=$f24;
$slot[$used]=24;
}
if ($f25 ne '') {
$used++;
$data[$used]=$f25;
$slot[$used]=25;
}
if ($f26 ne '') {
$used++;
$data[$used]=$f26;
$slot[$used]=26;
}
if ($f27 ne '') {
$used++;
$data[$used]=$f27;
$slot[$used]=27;
}
if ($f28 ne '') {
$used++;
$data[$used]=$f28;
$slot[$used]=28;
}
if ($f29 ne '') {
$used++;
$data[$used]=$f29;
$slot[$used]=29;
}
if ($f30 ne '') {
$used++;
$data[$used]=$f30;
$slot[$used]=30;
}
if ($used == -1 ) {
print "ERROR you didn't select any columns<br>";
$errors++;
}
#if ($used >= 0 && $used <= 4 ) {
if ($used >= 0 && $used < $#thenames ) {
$selected=$used+1;
#print "ERROR you must select 6 columns. You only selected $selected columns<br>";
print "ERROR you must select ".($#thenames+1)." columns. You only selected $selected columns<br>";
$errors++;
}
#if ($used > 5 ) {
if ($used > $#thenames ) {
$selected=$used+1;
print "ERROR you selected $selected columns. You are only allowed to select 5 columns<br>";
$errors++;
}
my $i, $k;
#
# they must select each item once and only once
# count how many times they selected each item
#
for ($i=0;$i<=$#thenames;$i++) {
#print "processing $thenames[$i]<p>";
for ($k=0;$k<=$used;$k++) {
#print "... $data[$k]<p>";
if ($data[$k] eq $thenames[$i] ) {
$thecount[$i]++;
}
}
}
#
# they must select each item at least once
# if they didnt select an item print an error message
#
for ($i=0;$i<=$#thenames;$i++) {
if ($thecount[$i] == 0 ) {
print "Error you did not select any columns for $thenames[$i]<br>";
$errors++;
}
}
#
# if they selected an item more then one time that is an error
# print an error message for each item selected two or more times
# and tell them which columns they selected the item on
#
for ($i=0;$i<=$#thenames;$i++) {
if ($thecount[$i] > 1 ) {
print "Error you selected $thecount[$i] columns for $thenames[$i]. They are columns ";
$errors++;
for ($k=0;$k<=$used;$k++) {
if ($data[$k] eq $thenames[$i] ) {
print "$slot[$k] ";
}
}
print "<br>";
}
}
if ($errors > 0 ) {
print "<h1>ERRORS the data was NOT added to the SQL tables</h1>";
exit;
}
use DBI;
$username = 'leads';
$password = 'connect';
$database = 'leads';
$hostname = 'localhost';
print "Connecting -- User=$username Database=$database Hose=$hostname<p>";
#$dbh = DBI->connect("dbi:mysql:database=$database;" .
# "host=$hostname;port=3306", $username, $password);
#
# get the date and time of the insert
# and maybe a sequence number of the insert
# so if the user screws things up and inserts
# a bunch of garbage date we can back out the
# garbage data by deleting everything with this
# sequence number
my @current_time=localtime();
my $this_date;
my $this_month;
my $this_year;
my $this_insert_number;
my $sequence_number_hits;
my $inserts_today=1;
$this_date=$current_time[3];
$this_month=$current_time[4]+1;
$this_year=($current_time[5]-100)+2000;
if ($this_date < 10 ) {
$this_date="0".$this_date;
}
if ($this_month < 10 ) {
$this_month="0".$this_month;
}
#
# use the yyyy/mm/dd for the first sequence number
#
$this_insert_number="$this_year/$this_month/$this_date";
#
# loop till we find a sequence number that isnt used
#
$sequence_number_hits=1;
#while ( $sequence_number_hits > 0) {
# #$SQL= "select count(*) as insert_seq_num from temp where insert_seq_num='$this_insert_number';";
# $SQL= "select count(*) as insert_seq_num from $database_to_use where insert_seq_num='$this_insert_number';";
# $Select = $dbh->prepare($SQL);
# $Select->execute();
# while($Row=$Select->fetchrow_hashref)
# {
# #
# # how many of these sequence numbers exist?
# #
# $sequence_number_hits=$Row->{insert_seq_num};
# }
# #print "SEQ HITS=$sequence_number_hits<p>";
# #
# # if the sequence number exists add one to the times today
# # we have done inserts and use that for the next sequence
# # number.
# # this assumes that only ONE person is adding data!
# # if more then one person is adding data we will have to
# # go to record locking to get a unique sequence number
# #
# if ($sequence_number_hits != 0 ) {
# #
# # that sequence number already exists
# # generate a new one by adding one to the old one
# #
# $inserts_today++;
# $inserts_today_formated=$inserts_today;
# while (length($inserts_today_formated) < 4 ) {
# $inserts_today_formated="0".$inserts_today_formated;
# }
# #$this_insert_number="$this_year/$this_month/$this_date-$inserts_today";
# $this_insert_number="$this_year/$this_month/$this_date-$inserts_today_formated";
# }
#}
#print "Insert sequence number is $this_insert_number<br>";
open(IN,$filename);
#open(IN,'leads_data_files/'.$filename);
#
# read each record
#
my $theslot;
my $name;
my $add;
my $city;
my $state;
my $zip;
my $phone;
my $records_processed=0;
my $sql_inserts=0;
my $sql_errors=0;
while(<IN>) {
$records_processed++;
#
# seperate each record into fields delimited by commas
#
@the_data_fields=split(',',$_);
$name="";
$add="";
$city="";
$state="";
$zip="";
$phone="";
#
# get the name
#
for ($i=0;$i<=$used;$i++) {
#if ($data[$i] eq "Name") {
if ($data[$i] eq $thenames[0]) {
$theslot=$slot[$i];
$theslot--;
#print "Name match at $i $theslot<br>";
$name=$the_data_fields[$theslot];
$name=clean_sql_data($name);
#print "Name=$name<br>";
}
}
#
# get the address
#
for ($i=0;$i<=$used;$i++) {
#if ($data[$i] eq "Add") {
if ($data[$i] eq $thenames[1]) {
$theslot=$slot[$i];
$theslot--;
#print "Add match at $i $theslot<br>";
$add=$the_data_fields[$theslot];
$add=clean_sql_data($add);
#print "add=$add<br>";
}
}
#
# get the city
#
for ($i=0;$i<=$used;$i++) {
#if ($data[$i] eq "City") {
if ($data[$i] eq $thenames[2]) {
$theslot=$slot[$i];
$theslot--;
#print "city match at $i $theslot<br>";
$city=$the_data_fields[$theslot];
$city=clean_sql_data($city);
#print "city=$city<br>";
}
}
#
# get the state
#
for ($i=0;$i<=$used;$i++) {
#if ($data[$i] eq "State") {
if ($data[$i] eq $thenames[3]) {
$theslot=$slot[$i];
$theslot--;
#print "state match at $i $theslot<br>";
$state=$the_data_fields[$theslot];
$state=clean_sql_data($state);
#print "state=$state<br>";
#print "state=$state<br>";
}
}
#
# get the zip
#
for ($i=0;$i<=$used;$i++) {
#if ($data[$i] eq "Zip") {
if ($data[$i] eq $thenames[4]) {
$theslot=$slot[$i];
$theslot--;
#print "zip match at $i $theslot<br>";
$zip=$the_data_fields[$theslot];
$zip=clean_sql_data($zip);
#print "zip=$zip<br>";
}
}
#
# get the phone
#
for ($i=0;$i<=$used;$i++) {
#if ($data[$i] eq "Phone") {
if ($data[$i] eq $thenames[5]) {
$theslot=$slot[$i];
$theslot--;
#print "phone match at $i $theslot<br>";
$phone=$the_data_fields[$theslot];
$phone=clean_sql_data($phone);
#print "phone=$phone<br>";
}
}
#
# add the data to the SQL table here
#
#$SQL= "insert into temp (businessName, address, city, state, zip, phone, insert_seq_num)" .
#$SQL= "insert into $database_to_use (businessName, address, city, state, zip, phone, insert_seq_num)" .
$SQL= "insert into $database_to_use (businessName, address, city, state, zip, phone, insert_seq_num, insert_file_name)<br>" .
" values('$name',
'$add',
'$city',
'$state',
'$zip',
'$phone',
'$this_insert_number',
'$filename'
)";
print "$SQL<p>";
#$InsertRecord = $dbh->do($SQL);
#if($InsertRecord){
$sql_inserts++;
# #print "Insert Success!!!!!";
#}
#else{
# print "Insert Failure<br/>$DBI::errstr";
# $sql_errors++;
#}
}
close(IN);
print "$records_processed records read from comma delimited file <span style=\"font-size: 130%;\">$filename</span><br>";
#print "$sql_inserts records inserted into SQL table temp <br>";
#print "$sql_errors ERRORS inserting SQL data<br>";
print "$sql_inserts records inserted into SQL table $database_to_use<br>";
print "$sql_errors ERRORS inserting SQL table $database_to_use<br>";
if ($sql_errors > 0 ) {
print "<h1> ERROR!!! $sql_errors sql insert errors! Call Mike</h1>";
print "move the file to error sub directory</h1>";
&move_the_file($filename, 'leads_data_files', 'leads_error_files');
}
else {
&move_the_file($filename, 'leads_data_files', 'leads_uploaded_files');
print "<p><a href=\"comma_file_upload.pl?databasetouse=$database_to_use\">Upload more files to database \"$database_to_use\"</a>";
}
}
exit;
sub select_the_field {
#
# this function defines a pull down menu for
# each field so the user can select what type of field
# it is.
# once we have gotten the specs down we might want to
# convert it to JavaScript so that at each field is
# added it is removed from the list
#
my $field=@_[0];
#
# the SQL data base has fields for
# date and zip+4
# the Mr. Caery says he will never use
# either of those fields. So
# I will not add any logic to process them
#
#
# they will always be able to select a SPACE
# which means the column is not used
#
print "<select name=\"f".$field."\">
<option value=\"\" selected=\"selected\"></option>";
#
# print the options they can select from the menu
#
my $i;
for ($i=0;$i <= $#thenames;$i++) {
print "<option value=\"".$thenames[$i]."\">".$thenames[$i]."</option>";
}
print "</select>";
return;
}
sub clean_sql_data {
my $name=@_[0];
$name=~s/'//g;
$name=~s/"//g;
$name=~s/>//g;
$name=~s/<//g;
return $name;
}
sub move_the_file {
my $file=@_[0];
my $in_dir=@_[1];
my $out_dir=@_[2];
my $new_file="$out_dir/$file";
my $count=0;
if(! -d $out_dir) {
#print "Error output directory '$dir' does not exists<p>";
#exit;
}
if(! -e "$in_dir/$file") {
#print "Error input file '$in_dir/$file' does not exists<p>";
#exit;
}
#while(-e $new_file) {
# #print "new file '$new_file' exists<p>";
# $count++;
# $new_file="$out_dir/$file-$count";
#}
#print "move file '$in_dir/$file' to subdirectory '$dir'<p>";
#print "move file '$in_dir/$file' to subdirectory $new_file'<p>";
#$rc=rename("$in_dir/$file","$new_file");
#print "rc=$rc<p>";
return;
}
#!/usr/bin/perl
#
# i wrote this as an answer to an interview question
# some guy who was hiring programmers sent me
#
#!/usr/bin/perl
#
# i wrote this as an answer to an interview question
# some guy who was hiring programmers sent me
#
require CGI;
use CGI;
$cgi = new CGI;
$f=$cgi->param('f');
print "Content-type: text/html", "\n\n";
print "<html>\n";
#
#define stuff to grab data entered on form
#
require CGI;
use CGI;
$cgi = new CGI;
#
# get the data they entered in the text boxes
#
$database_to_use=$cgi->param('databasetouse');
$database_to_use="temp";
$filename=$cgi->param('filename');
if ( $database_to_use eq "temp" ) {
print "<h1 style=\"color:red;\">TEST database \"$database_to_use\"</h1>";
print "<h3 style=\"color:red;\" align=\"center\">This is a TEST data base!! Any data you enter WILL be deleted!!!!</h3>";
}
else {
if ( $database_to_use eq "leads" ) {
print "<h1>Official database \"$database_to_use\"</h1>";
}
else {
print "<h1> Error unknow database\"$database_to_use\"</h1>";
exit;
}
}
print "<h2>Upload comma delimited leads file(s) to this server</h2>";
print "<blockquote>";
print "<form action=\"upload_files.pl\" method=\"post\" ENCTYPE=\"multipart/form-data\">";
print "<table border=0>";
print "<tr>";
print "<td>";
print "<input type=\"file\" name=\"f01\" value=\"\"><br>";
print "<input type=\"file\" name=\"f02\" value=\"\"><br>";
print "<input type=\"file\" name=\"f03\" value=\"\"><br>";
print "<input type=\"file\" name=\"f04\" value=\"\"><br>";
print "<input type=\"file\" name=\"f05\" value=\"\"><br>";
print "</td>";
print "<td>";
print "<input type=\"file\" name=\"f06\" value=\"\"><br>";
print "<input type=\"file\" name=\"f07\" value=\"\"><br>";
print "<input type=\"file\" name=\"f08\" value=\"\"><br>";
print "<input type=\"file\" name=\"f09\" value=\"\"><br>";
print "<input type=\"file\" name=\"f10\" value=\"\"><br>";
print "</td>";
print "<td>";
print "<input type=\"file\" name=\"f11\" value=\"\"><br>";
print "<input type=\"file\" name=\"f12\" value=\"\"><br>";
print "<input type=\"file\" name=\"f13\" value=\"\"><br>";
print "<input type=\"file\" name=\"f14\" value=\"\"><br>";
print "<input type=\"file\" name=\"f15\" value=\"\"><br>";
print "</td>";
print "<td>";
print "<input type=\"file\" name=\"f16\" value=\"\"><br>";
print "<input type=\"file\" name=\"f17\" value=\"\"><br>";
print "<input type=\"file\" name=\"f18\" value=\"\"><br>";
print "<input type=\"file\" name=\"f19\" value=\"\"><br>";
print "<input type=\"file\" name=\"f20\" value=\"\"><br>";
print "</td>";
print "</tr>";
print "</table>";
print "<p>";
print "<input type=\"submit\" name=submit value=\"Upload the selected files\">";
print "<input type=\"hidden\" name=\"datadisplayed\" value=\"y\">";
print "<input type=\"hidden\" name=\"filename\" value=\"$filename\">";
print "</form>";
print "</blockquote>";
print "<!-- See this URL for how to upload files with PERL -->";
print "<!-- blockquote -->";
print "<!-- a href=\"http://www.sitepoint.com/article/uploading-files-cgi-perl\">http://www.sitepoint.com/article/uploading-files-cgi-perl</a -->";
print "<!-- /blockquote -->";
print "<h2>Insert a comma delimited leads file into the SQL leads table '$database_to_use'</h2>";
print "<table border=0 width=\"100%\"><tr><td width=\"5%\"> </td><td width=\"40%\">Click on the filename and see the first 6 records before you decide to upload it. For debugging click on A to display all the data in the file</td><td> </td></tr></table>";
opendir(DIR,"leads_data_files");
print "<ul>";
$recordstodisplay=6;
while($x=readdir(DIR)) {
if (!($x eq "." || $x eq "..") ) {
#print "<li><a href=\"";
# print "comma.pl";
#print "?filename=$x&table=$database_to_use\">$x</a>";
#print "<li><a href=\"comma.pl?filename=$x&table=$database_to_use\">$x</a>";
#print "<li><a href=\"comma.pl?filename=$x&table=$database_to_use&recordstodisplay=$recordstodisplay\">$x</a>";
print "<li>
<a href=\"comma.pl?filename=$x&table=$database_to_use&recordstodisplay=all\">A</a>
<a href=\"comma.pl?filename=$x&table=$database_to_use&recordstodisplay=$recordstodisplay\">$x</a>
";
}
}
print "</ul>";
closedir(DIR);
print "<h2>Leads files which had errors loading into the SQL leads table</h2>";
@filelist=();
opendir(DIR,"leads_error_files");
while($x=readdir(DIR)) {
if (!($x eq "." || $x eq "..") ) {
($fss1,
$fss2,
$fss3,
$fss4,
$fss5,
$fss6,
$fss7,
$fss8,
$fss9,
$fss2,
$fss10,
$fss11,
$fss12,
$fss13)=stat("leads_error_files/".$x);
#$fss10=$fss9;
while( length($fss10) < 20) {
$fss10="0".$fss10;
}
push @filelist,$fss10.'-'.$x;
}
}
closedir(DIR);
@filelist=sort {$b <=> $a} @filelist;
print "<table border=0 width=\"100%\"><tr><td width=\"5%\"> </td><td width=\"40%\">If any files exist here you should contact Mike or another programmer and ask them modify this program to removed the data that caused the error from the comma delimited file.</td><td> </td></tr></table>";
print "<ul>";
foreach(@filelist) {
$ztime=$_;
$x=$_;
$x=~s/^[0-9]*-//;
$ztime=~s/-.*$//;
($lts, $ltmi, $lth, $ltd, $ltmo, $lty)=localtime($ztime);
$lty+=1900;
$ltmo++;
$ltmo=get_2_digits($ltmo);
$ltd=get_2_digits($ltd);
$lth=get_2_digits($lth);
$ltmi=get_2_digits($ltmi);
$lts=get_2_digits($lts);
print "<li><tt>$lty/$ltmo/$ltd $lth:$ltmi:$lts</tt> $x";
}
print "</ul>";
$list_last_n_files_uploaded=10;
print "<h2>Last $list_last_n_files_uploaded uploaded leads files</h2>";
@filelist=();
opendir(DIR,"leads_uploaded_files");
#print "<ul>";
while($x=readdir(DIR)) {
if (!($x eq "." || $x eq "..") ) {
#print "<li>$x";
($fss1,
$fss2,
$fss3,
$fss4,
$fss5,
$fss6,
$fss7,
$fss8,
$fss9,
$fss2,
$fss10,
$fss11,
$fss12,
$fss13)=stat("leads_uploaded_files/".$x);
#$fss10=$fss9;
while( length($fss10) < 20) {
$fss10="0".$fss10;
}
push @filelist,$fss10.'-'.$x;
#print " '$fss10'</tt>";
}
}
#print "</ul>";
closedir(DIR);
#@filelist=sort @filelist;
$files_listed=0;
@filelist=sort {$b <=> $a} @filelist;
print "<ul>";
foreach(@filelist) {
if ($files_listed < $list_last_n_files_uploaded ) {
$ztime=$_;
$x=$_;
$x=~s/^[0-9]*-//;
$ztime=~s/-.*$//;
($lts, $ltmi, $lth, $ltd, $ltmo, $lty)=localtime($ztime);
$lty+=1900;
$ltmo++;
$ltmo=get_2_digits($ltmo);
$ltd=get_2_digits($ltd);
$lth=get_2_digits($lth);
$ltmi=get_2_digits($ltmi);
$lts=get_2_digits($lts);
print "<li><tt>$lty/$ltmo/$ltd $lth:$ltmi:$lts</tt> $x";
#print "<li><tt>$lty/$ltmo/$ltd $lth:$ltmi:$lts</tt> <a href=\"leads_uploaded_files/$x\">$x</a>";
$files_listed++;
}
}
print "</ul>";
print "<h2>Delete leads from the SQL leads table</h2>";
print "<table border=0 width=\"100%\"><tr><td width=\"5%\"> </td><td width=\"40%\">If you selected the wrong field names when you inserted a comma delimited leads file into the SQL table you can delete the data that was inserted from that file with this menu.</td><td> </td></tr></table>";
print "<blockquote>";
print "<a href=\"delete_leads.pl?database=$database_to_use\">Delete leads</a>";
print "</blockquote>";
exit;
sub get_2_digits {
my $num=@_[0];
if ($num < 10 ) {
$num="0".$num;
}
return $num;
}
#!/usr/bin/perl
#
# i wrote this as an answer to an interview question
# some guy who was hiring programmers sent me
#
require CGI;
use CGI;
$cgi = new CGI;
$f=$cgi->param('f');
print "Content-type: text/html", "\n\n";
print "<html>\n";
$mydir='.';
print "Select file to load to SQL tables<p>";
print "<form action=\"comma.pl\" method=\"post\">";
print "<input type=\"submit\" name=submit value=\"Process\">";
print "<p>";
print "<select name=\"filename\">";
@filelist=();
opendir(MYFILES,$mydir);
while($file=readdir(MYFILES)) {
chomp($file);
if ($file =~ /.txt$/i ) {
#print "<option value=\"$file\">$file</option>";
push(@filelist,$file);
}
}
@filelist=sort(@filelist);
foreach(@filelist) {
print "<option value=\"$_\">$_</option>";
}
closedir(MYFILES);
print "</select>";
print "</form>";
exit;
#
# the names of the fields they MUST select
# and the counts of how many time each field has been selected
#
@thenames=( "Name", "Add", "City", "State", "Zip", "Phone");
@thecounts=( 0, 0, 0, 0, 0, 0);
#$database_to_use="temp";
#
# print header stuff
#
#
# get the data they entered in the text boxes
#
$database_to_use=$cgi->param('table');
$database_to_use="leads";
#$filename=$cgi->param('filename');
$filename="csv.txt";
$datadisplayed=$cgi->param('datadisplayed');
#$datadisplayed="xx";
$recordstodisplay=$cgi->param('recordstodisplay');
$recordstodisplay=10;
$f01=$cgi->param('f01');
$f02=$cgi->param('f02');
$f03=$cgi->param('f03');
$f04=$cgi->param('f04');
$f05=$cgi->param('f05');
$f06=$cgi->param('f06');
$f07=$cgi->param('f07');
$f08=$cgi->param('f08');
$f09=$cgi->param('f09');
$f10=$cgi->param('f10');
$f11=$cgi->param('f11');
$f12=$cgi->param('f12');
$f13=$cgi->param('f13');
$f14=$cgi->param('f14');
$f15=$cgi->param('f15');
$f16=$cgi->param('f16');
$f17=$cgi->param('f17');
$f18=$cgi->param('f18');
$f19=$cgi->param('f19');
$f20=$cgi->param('f20');
$f21=$cgi->param('f21');
$f22=$cgi->param('f22');
$f23=$cgi->param('f23');
$f24=$cgi->param('f24');
$f25=$cgi->param('f25');
$f26=$cgi->param('f26');
$f27=$cgi->param('f27');
$f28=$cgi->param('f28');
$f29=$cgi->param('f29');
$f30=$cgi->param('f30');
#
# if they entered any data write it out to the SQL tables
#
if ( $datadisplayed eq "") {
#
# if the data has not been displayed
# open the file and display the data
#
if ( $database_to_use eq "temp" ) {
print "<h1 style=\"color:red;\">Test database \"temp\"</h1>";
print "<h3 style=\"color:red;\" align=\"center\">This is a TEST data base!! Any data you enter WILL be deleted!!!!</h3>";
#print "<form action=\"comma.pl\" method=\"post\">";
}
else {
if ( $database_to_use eq "leads" ) {
print "<h1>Official database \"leads\"</h1>";
#print "<form action=\"prod_comma.pl\" method=\"post\">";
}
else {
print "<h1> ERROR unknown database \"$database_to_use\"</h1>";
exit;
}
}
#print "records to display =$recordstodisplay<p>";
#print "<form action=\"comma.pl&table=$database_to_use\" method=\"post\">";
print "<form action=\"comma.pl\" method=\"post\">";
print "<input type=\"submit\" name=submit value=\"Insert leads into the SQL table '$database_to_use'\">";
print "Processing file:<span style=\"font-size: 130%;\">$filename</span><p>";
print "<table border=1>";
print "<tr>";
for ($i=1;$i<31;$i++) {
#
# add a leading zero to the field name if it is less then 10
#
$lead="";
if ($i < 10 ) {
$lead="0";
}
print "<td>";
&select_the_field("$lead$i");
print "</td>";
}
print "</tr>";
print "<tr>";
for ($i=1;$i<31;$i++) {
#
# display the field number so when we print error messages they make sense
#
$lead="";
if ($i < 10 ) {
$lead="0";
}
print "<td>
<span style=\"background-color:rgb(255,255,0)\">$lead$i</span></td>";
}
print "</tr>";
#
# open the file to process
#
#open(IN,'krudc.txt');
#open(IN,'leads_data_files/'.$filename);
open(IN,$filename);
#
# read each record
#
$recordsread=0;
while(<IN>) {
#while(<IN> && (
#$recordsread
#<=
#$datadisplayed
#)) {
$recordsread++;
if ($recordsread <= $recordstodisplay || $recordstodisplay eq "all") {
#
# seperate each record into fields delimited by commas
#
($f01, $f02, $f03 , $f04 , $f05 , $f06 , $f07 , $f08 , $f09 , $f10,
$f11, $f12, $f13 , $f14 , $f15 , $f16 , $f17 , $f18 , $f19 , $f20,
$f21, $f22, $f23 , $f24 , $f25 , $f26 , $f27 , $f28 , $f29 , $f30
) = split(',',$_);
#
# display the 30 fields
# usually there will be less
#
print "<tr valign=\"top\">\n";
print "<td>$f01 </td>\n";
print "<td><nobr>$f02</nobr></td>\n";
print "<td><nobr>$f03</nobr></td>\n";
print "<td><nobr>$f04</nobr></td>\n";
print "<td>$f05 </td>\n";
print "<td>$f06 </td>\n";
print "<td>$f07 </td>\n";
print "<td>$f08 </td>\n";
print "<td>$f09 </td>\n";
print "<td>$f10 </td>\n";
print "<td>$f11 </td>\n";
print "<td>$f12 </td>\n";
print "<td>$f13 </td>\n";
print "<td>$f14 </td>\n";
print "<td>$f15 </td>\n";
print "<td>$f16 </td>\n";
print "<td>$f17 </td>\n";
print "<td>$f18 </td>\n";
print "<td>$f19 </td>\n";
print "<td>$f20 </td>\n";
print "<td>$f21 </td>\n";
print "<td>$f22 </td>\n";
print "<td>$f23 </td>\n";
print "<td>$f24 </td>\n";
print "<td>$f25 </td>\n";
print "<td>$f26 </td>\n";
print "<td>$f27 </td>\n";
print "<td>$f28 </td>\n";
print "<td>$f29 </td>\n";
print "<td>$f30 </td>\n";
print "</tr>\n";
}
}
close(IN);
print "</table>";
#
# dont forget about these HIDDEN data values :)
# i use them to keep track of if we have read
# the file and displayed it.
print "<input type=\"hidden\" name=\"datadisplayed\" value=\"y\">";
print "<input type=\"hidden\" name=\"filename\" value=\"$filename\">";
print "<input type=\"hidden\" name=\"table\" value=\"$database_to_use\">";
print "</form>";
}
else {
#print "process the data <p>";
if ( $database_to_use eq "temp" ) {
print "<h1 style=\"color:red;\">Test database \"temp\"</h1>";
print "<h3 style=\"color:red;\" align=\"center\">This is a TEST data base!! Any data you enter WILL be deleted!!!!</h3>";
}
print "Processing file:<span style=\"font-size: 130%;\">$filename</span><p>";
#print "datadisplayed='$datadisplayed'<p>";
#print " f01='$f01' f02='$f02' f03='$f03' f04='$f04' f05='$f05' f06='$f06' f07='$f07' f08='$f08' f09='$f09' f10='$f10' f11='$f11' f12='$f12' f13='$f13' f14='$f14' f15='$f15' f16='$f16' f17='$f17' f18='$f18' f19='$f19' f20='$f20' f21='$f21' f22='$f22' f23='$f23' f24='$f24' f25='$f25' f26='$f26' f27='$f27' f28='$f28' f29='$f29' f30='$f30' <p>";
$used=-1;
$errors=0;
#
# get all the items they selected and place them
# in an array
# after placing the items in an array we will
# loop thru the arrays to verify that they
# selected each item one and only one time
#
if ($f01 ne '') {
$used++;
$data[$used]=$f01;
$slot[$used]=1;
}
if ($f02 ne '') {
$used++;
$data[$used]=$f02;
$slot[$used]=2;
}
if ($f03 ne '') {
$used++;
$data[$used]=$f03;
$slot[$used]=3;
}
if ($f04 ne '') {
$used++;
$data[$used]=$f04;
$slot[$used]=4;
}
if ($f05 ne '') {
$used++;
$data[$used]=$f05;
$slot[$used]=5;
}
if ($f06 ne '') {
$used++;
$data[$used]=$f06;
$slot[$used]=6;
}
if ($f07 ne '') {
$used++;
$data[$used]=$f07;
$slot[$used]=7;
}
if ($f08 ne '') {
$used++;
$data[$used]=$f08;
$slot[$used]=8;
}
if ($f09 ne '') {
$used++;
$data[$used]=$f09;
$slot[$used]=9;
}
if ($f10 ne '') {
$used++;
$data[$used]=$f10;
$slot[$used]=10;
}
if ($f11 ne '') {
$used++;
$data[$used]=$f11;
$slot[$used]=11;
}
if ($f12 ne '') {
$used++;
$data[$used]=$f12;
$slot[$used]=12;
}
if ($f13 ne '') {
$used++;
$data[$used]=$f13;
$slot[$used]=13;
}
if ($f14 ne '') {
$used++;
$data[$used]=$f14;
$slot[$used]=14;
}
if ($f15 ne '') {
$used++;
$data[$used]=$f15;
$slot[$used]=15;
}
if ($f16 ne '') {
$used++;
$data[$used]=$f16;
$slot[$used]=16;
}
if ($f17 ne '') {
$used++;
$data[$used]=$f17;
$slot[$used]=17;
}
if ($f18 ne '') {
$used++;
$data[$used]=$f18;
$slot[$used]=18;
}
if ($f19 ne '') {
$used++;
$data[$used]=$f19;
$slot[$used]=19;
}
if ($f20 ne '') {
$used++;
$data[$used]=$f20;
$slot[$used]=20;
}
if ($f21 ne '') {
$used++;
$data[$used]=$f21;
$slot[$used]=21;
}
if ($f22 ne '') {
$used++;
$data[$used]=$f22;
$slot[$used]=22;
}
if ($f23 ne '') {
$used++;
$data[$used]=$f23;
$slot[$used]=23;
}
if ($f24 ne '') {
$used++;
$data[$used]=$f24;
$slot[$used]=24;
}
if ($f25 ne '') {
$used++;
$data[$used]=$f25;
$slot[$used]=25;
}
if ($f26 ne '') {
$used++;
$data[$used]=$f26;
$slot[$used]=26;
}
if ($f27 ne '') {
$used++;
$data[$used]=$f27;
$slot[$used]=27;
}
if ($f28 ne '') {
$used++;
$data[$used]=$f28;
$slot[$used]=28;
}
if ($f29 ne '') {
$used++;
$data[$used]=$f29;
$slot[$used]=29;
}
if ($f30 ne '') {
$used++;
$data[$used]=$f30;
$slot[$used]=30;
}
if ($used == -1 ) {
print "ERROR you didn't select any columns<br>";
$errors++;
}
#if ($used >= 0 && $used <= 4 ) {
if ($used >= 0 && $used < $#thenames ) {
$selected=$used+1;
#print "ERROR you must select 6 columns. You only selected $selected columns<br>";
print "ERROR you must select ".($#thenames+1)." columns. You only selected $selected columns<br>";
$errors++;
}
#if ($used > 5 ) {
if ($used > $#thenames ) {
$selected=$used+1;
print "ERROR you selected $selected columns. You are only allowed to select 5 columns<br>";
$errors++;
}
my $i, $k;
#
# they must select each item once and only once
# count how many times they selected each item
#
for ($i=0;$i<=$#thenames;$i++) {
#print "processing $thenames[$i]<p>";
for ($k=0;$k<=$used;$k++) {
#print "... $data[$k]<p>";
if ($data[$k] eq $thenames[$i] ) {
$thecount[$i]++;
}
}
}
#
# they must select each item at least once
# if they didnt select an item print an error message
#
for ($i=0;$i<=$#thenames;$i++) {
if ($thecount[$i] == 0 ) {
print "Error you did not select any columns for $thenames[$i]<br>";
$errors++;
}
}
#
# if they selected an item more then one time that is an error
# print an error message for each item selected two or more times
# and tell them which columns they selected the item on
#
for ($i=0;$i<=$#thenames;$i++) {
if ($thecount[$i] > 1 ) {
print "Error you selected $thecount[$i] columns for $thenames[$i]. They are columns ";
$errors++;
for ($k=0;$k<=$used;$k++) {
if ($data[$k] eq $thenames[$i] ) {
print "$slot[$k] ";
}
}
print "<br>";
}
}
if ($errors > 0 ) {
print "<h1>ERRORS the data was NOT added to the SQL tables</h1>";
exit;
}
use DBI;
$username = 'leads';
$password = 'connect';
$database = 'leads';
$hostname = 'localhost';
print "Connecting -- User=$username Database=$database Hose=$hostname<p>";
#$dbh = DBI->connect("dbi:mysql:database=$database;" .
# "host=$hostname;port=3306", $username, $password);
#
# get the date and time of the insert
# and maybe a sequence number of the insert
# so if the user screws things up and inserts
# a bunch of garbage date we can back out the
# garbage data by deleting everything with this
# sequence number
my @current_time=localtime();
my $this_date;
my $this_month;
my $this_year;
my $this_insert_number;
my $sequence_number_hits;
my $inserts_today=1;
$this_date=$current_time[3];
$this_month=$current_time[4]+1;
$this_year=($current_time[5]-100)+2000;
if ($this_date < 10 ) {
$this_date="0".$this_date;
}
if ($this_month < 10 ) {
$this_month="0".$this_month;
}
#
# use the yyyy/mm/dd for the first sequence number
#
$this_insert_number="$this_year/$this_month/$this_date";
#
# loop till we find a sequence number that isnt used
#
$sequence_number_hits=1;
#while ( $sequence_number_hits > 0) {
# #$SQL= "select count(*) as insert_seq_num from temp where insert_seq_num='$this_insert_number';";
# $SQL= "select count(*) as insert_seq_num from $database_to_use where insert_seq_num='$this_insert_number';";
# $Select = $dbh->prepare($SQL);
# $Select->execute();
# while($Row=$Select->fetchrow_hashref)
# {
# #
# # how many of these sequence numbers exist?
# #
# $sequence_number_hits=$Row->{insert_seq_num};
# }
# #print "SEQ HITS=$sequence_number_hits<p>";
# #
# # if the sequence number exists add one to the times today
# # we have done inserts and use that for the next sequence
# # number.
# # this assumes that only ONE person is adding data!
# # if more then one person is adding data we will have to
# # go to record locking to get a unique sequence number
# #
# if ($sequence_number_hits != 0 ) {
# #
# # that sequence number already exists
# # generate a new one by adding one to the old one
# #
# $inserts_today++;
# $inserts_today_formated=$inserts_today;
# while (length($inserts_today_formated) < 4 ) {
# $inserts_today_formated="0".$inserts_today_formated;
# }
# #$this_insert_number="$this_year/$this_month/$this_date-$inserts_today";
# $this_insert_number="$this_year/$this_month/$this_date-$inserts_today_formated";
# }
#}
#print "Insert sequence number is $this_insert_number<br>";
open(IN,$filename);
#open(IN,'leads_data_files/'.$filename);
#
# read each record
#
my $theslot;
my $name;
my $add;
my $city;
my $state;
my $zip;
my $phone;
my $records_processed=0;
my $sql_inserts=0;
my $sql_errors=0;
while(<IN>) {
$records_processed++;
#
# seperate each record into fields delimited by commas
#
@the_data_fields=split(',',$_);
$name="";
$add="";
$city="";
$state="";
$zip="";
$phone="";
#
# get the name
#
for ($i=0;$i<=$used;$i++) {
#if ($data[$i] eq "Name") {
if ($data[$i] eq $thenames[0]) {
$theslot=$slot[$i];
$theslot--;
#print "Name match at $i $theslot<br>";
$name=$the_data_fields[$theslot];
$name=clean_sql_data($name);
#print "Name=$name<br>";
}
}
#
# get the address
#
for ($i=0;$i<=$used;$i++) {
#if ($data[$i] eq "Add") {
if ($data[$i] eq $thenames[1]) {
$theslot=$slot[$i];
$theslot--;
#print "Add match at $i $theslot<br>";
$add=$the_data_fields[$theslot];
$add=clean_sql_data($add);
#print "add=$add<br>";
}
}
#
# get the city
#
for ($i=0;$i<=$used;$i++) {
#if ($data[$i] eq "City") {
if ($data[$i] eq $thenames[2]) {
$theslot=$slot[$i];
$theslot--;
#print "city match at $i $theslot<br>";
$city=$the_data_fields[$theslot];
$city=clean_sql_data($city);
#print "city=$city<br>";
}
}
#
# get the state
#
for ($i=0;$i<=$used;$i++) {
#if ($data[$i] eq "State") {
if ($data[$i] eq $thenames[3]) {
$theslot=$slot[$i];
$theslot--;
#print "state match at $i $theslot<br>";
$state=$the_data_fields[$theslot];
$state=clean_sql_data($state);
#print "state=$state<br>";
#print "state=$state<br>";
}
}
#
# get the zip
#
for ($i=0;$i<=$used;$i++) {
#if ($data[$i] eq "Zip") {
if ($data[$i] eq $thenames[4]) {
$theslot=$slot[$i];
$theslot--;
#print "zip match at $i $theslot<br>";
$zip=$the_data_fields[$theslot];
$zip=clean_sql_data($zip);
#print "zip=$zip<br>";
}
}
#
# get the phone
#
for ($i=0;$i<=$used;$i++) {
#if ($data[$i] eq "Phone") {
if ($data[$i] eq $thenames[5]) {
$theslot=$slot[$i];
$theslot--;
#print "phone match at $i $theslot<br>";
$phone=$the_data_fields[$theslot];
$phone=clean_sql_data($phone);
#print "phone=$phone<br>";
}
}
#
# add the data to the SQL table here
#
#$SQL= "insert into temp (businessName, address, city, state, zip, phone, insert_seq_num)" .
#$SQL= "insert into $database_to_use (businessName, address, city, state, zip, phone, insert_seq_num)" .
$SQL= "insert into $database_to_use (businessName, address, city, state, zip, phone, insert_seq_num, insert_file_name)<br>" .
" values('$name',
'$add',
'$city',
'$state',
'$zip',
'$phone',
'$this_insert_number',
'$filename'
)";
print "$SQL<p>";
#$InsertRecord = $dbh->do($SQL);
#if($InsertRecord){
$sql_inserts++;
# #print "Insert Success!!!!!";
#}
#else{
# print "Insert Failure<br/>$DBI::errstr";
# $sql_errors++;
#}
}
close(IN);
print "$records_processed records read from comma delimited file <span style=\"font-size: 130%;\">$filename</span><br>";
#print "$sql_inserts records inserted into SQL table temp <br>";
#print "$sql_errors ERRORS inserting SQL data<br>";
print "$sql_inserts records inserted into SQL table $database_to_use<br>";
print "$sql_errors ERRORS inserting SQL table $database_to_use<br>";
if ($sql_errors > 0 ) {
print "<h1> ERROR!!! $sql_errors sql insert errors! Call Mike</h1>";
print "move the file to error sub directory</h1>";
&move_the_file($filename, 'leads_data_files', 'leads_error_files');
}
else {
&move_the_file($filename, 'leads_data_files', 'leads_uploaded_files');
print "<p><a href=\"comma_file_upload.pl?databasetouse=$database_to_use\">Upload more files to database \"$database_to_use\"</a>";
}
}
exit;
sub select_the_field {
#
# this function defines a pull down menu for
# each field so the user can select what type of field
# it is.
# once we have gotten the specs down we might want to
# convert it to JavaScript so that at each field is
# added it is removed from the list
#
my $field=@_[0];
#
# the SQL data base has fields for
# date and zip+4
# the Mr. Caery says he will never use
# either of those fields. So
# I will not add any logic to process them
#
#
# they will always be able to select a SPACE
# which means the column is not used
#
print "<select name=\"f".$field."\">
<option value=\"\" selected=\"selected\"></option>";
#
# print the options they can select from the menu
#
my $i;
for ($i=0;$i <= $#thenames;$i++) {
print "<option value=\"".$thenames[$i]."\">".$thenames[$i]."</option>";
}
print "</select>";
return;
}
sub clean_sql_data {
my $name=@_[0];
$name=~s/'//g;
$name=~s/"//g;
$name=~s/>//g;
$name=~s/<//g;
return $name;
}
sub move_the_file {
my $file=@_[0];
my $in_dir=@_[1];
my $out_dir=@_[2];
my $new_file="$out_dir/$file";
my $count=0;
if(! -d $out_dir) {
#print "Error output directory '$dir' does not exists<p>";
#exit;
}
if(! -e "$in_dir/$file") {
#print "Error input file '$in_dir/$file' does not exists<p>";
#exit;
}
#while(-e $new_file) {
# #print "new file '$new_file' exists<p>";
# $count++;
# $new_file="$out_dir/$file-$count";
#}
#print "move file '$in_dir/$file' to subdirectory '$dir'<p>";
#print "move file '$in_dir/$file' to subdirectory $new_file'<p>";
#$rc=rename("$in_dir/$file","$new_file");
#print "rc=$rc<p>";
return;
}
#!/usr/bin/perl
#
# i wrote this as an answer to an interview question
# some guy who was hiring programmers sent me
#
require CGI;
use CGI;
$cgi = new CGI;
$f=$cgi->param('f');
print "Content-type: text/html", "\n\n";
print "<html>\n";
use DBI;
$ztype="miksup";
$ztype="localhost";
$ztype="newnew582";
$ztype="newnew";
$ztype="miksup";
if ( $ztype eq "miksup" ) {
#$username = 'miksup';
$username = 'miksup_data';
$password = 'Amigos666';
$database = 'miksup_data';
$hostname = 'mysql1.100ws.com';
#$hostname = 'localhost';
$SQL= "select name, count from counters;";
$zhash1='name';
$zhash2='count';
}
#
# localhost doesnt seem to work here
# i wonder if i spelled it wrong
# localhost does work with PHP
#
if ( $ztype eq "localhost" ) {
$username = 'miksup_data';
$password = 'Amigos666';
$database = 'miksup_data';
$hostname = 'localhost';
$zhash1='name';
$zhash2='count';
$SQL= "select name, count from counters;";
}
if ( $ztype eq "newnew" ) {
$username = 'newnew582_a';
$password = 'Amigos666';
$database = 'newnew582_a';
$hostname = 'mysql1.100ws.com';
$zhash1='a1';
$zhash2='keynum';
$SQL= "select a1, a2, a3, a4, keynum from a;";
}
$itworked="";
#print "Connecting -- User=$username Database=$database Hose=$hostname<p>";
print "Connecting $ztype<br>";
$dbh = DBI->connect("dbi:mysql:database=$database;" .
"host=$hostname;port=3306", $username, $password) or $itworked="FAILED";
if ($itworked ne "") {
print "connect FAILED!!<p>";
exit;
}
#print "connected $itworked<p>";
print "Connected!!!<br>";
#$SQL= "select name, count from counters;";
print "Command=$SQL<p>";
$Select = $dbh->prepare($SQL);
$Select->execute();
while($Row=$Select->fetchrow_hashref)
{
#
# how many of these sequence numbers exist?
#
$sequence_number_hits=$Row->{insert_seq_num};
#$zname=$Row->{name};
#$zcount=$Row->{count};
$zname=$Row->{$zhash1};
$zcount=$Row->{$zhash2};
#print "$zname $zcount<p>";
print "$zcount<br>";
}
exit;
#!/usr/bin/perl
#
# i wrote this as an answer to an interview question
# some guy who was hiring programmers sent me
#
require CGI;
use CGI;
$cgi = new CGI;
$f=$cgi->param('f');
my $junk;
my @junk;
#
# count total errors for stuff
# that is blank
#
my $r_errors=0; # total errors
my $r_errors_this_record=0; # errors this record
my $r_errors_this_record_message="";
my $r_errors_name=0; # blank names
my $r_errors_city=0; # blank cities
my $r_errors_address=0; # blank addresses
my $r_errors_email=0; # blank emails
my $r_errors_phone=0; # one or more phone numbers required
my $r_errors_phones_this_record=0; # count phone numbers we have
my $r_error_file_name_to_write=""; # where to write the error file
my $r_error_record=""; # write this record to the error file
my $r_error_duplicate_records=0; # number of duplicate records that were found
my $r_error_date=0; # date error on this record
my $r_error_date_this_record=0; # records with garbage dates -
my $r_error_date_year=0; # records with garbage dates - bad year
my $r_error_date_month=0; # records with garbage dates - bad month
my $r_error_date_day=0; # records with garbage dates - bad day of month
my $move_file=""; # move the file when we are done? yes no
my $default_dup_records=""; # allow duplicate records to be inserted
# this is just for testing and used by mike
# the two allowed values are
# never testingonly
my $default_process_records=""; # records to process (if not null)
my $default_skip_records=0; # records to skip over
my $SQL_to_find_duplicate_records="";
my $SQL_to_find_duplicate_records_phone_part1="";
my $SQL_to_find_duplicate_records_phone_part2="";
$recordstodisplay=$cgi->param('recordstodisplay');
print "Content-type: text/html", "\n\n";
print "<html>\n";
print "<!-- © copyright mike blueshoes June 2008 -->\n";
print "<!-- © copyright blueshoes June 2008 -->\n";
print "<!-- © copyright m1ke flashwater June 2008 -->\n";
print "<!-- © copyright m1keflashwater June 2008 -->\n";
#
# after changing names richard wanted delted
#
@thenames=(
"loan_program",
"monthly_debt",
"phone_primary",
"phone_secondary",
"second_mortgage_balance",
"second_mortgage_rate",
#"property_type",
"name",
"city",
"state",
"address",
"zip",
#"phone_work",
#"phone_home_1",
#"phone_home_2",
"property_type",
"property_use",
"value_current",
"value_original",
"first_mort_balance",
"first_mort_rate",
"first_mort_rate_type",
#"payment",
#"behind",
"credit",
#"employer",
#
# years there becomes employment_status
#
#"years_there",
"employment_status",
#
# income_household becomes monthly_income
#
#"income_household",
"monthly_income",
"best_time",
"loan_amount",
"loan_type",
"e_mail",
"LTV",
#
# date_time becomes date
#
#"date_time",
"date",
"IP_address"
);
$database_to_use="richard_test";
$database_to_use="richard_prod";
#
# print header stuff
#
#print "Content-type: ", "\n\n";
#print "<html>";
#
#define stuff to grab data entered on form
#
require CGI;
use CGI;
$cgi = new CGI;
#
# get the data they entered in the text boxes
#
$move_file=$cgi->param('move_file');
$default_dup_records=$cgi->param('default_dup_records');
$default_process_records=$cgi->param('default_process_records');
$default_skip_records=$cgi->param('default_skip_records');
#
# if the records to skip over is not valid set it to zero
#
if ($default_skip_records !~ /^[0-9][0-9]*$/ ) {
$default_skip_records=0;
}
#
# if the records to process is not valid set it to NULL
#
if ($default_process_records !~ /^[0-9][0-9]*$/ ) {
$default_process_records="";
}
#$database_to_use=$cgi->param('table');
$filename=$cgi->param('filename');
$datadisplayed=$cgi->param('datadisplayed');
$recordstodisplay=$cgi->param('recordstodisplay');
if ($recordstodisplay eq "" ) {
$recordstodisplay=100;
}
if ($recordstodisplay !~ /^[0-9]*$/ ) {
$recordstodisplay=100;
}
my $default_delimiter=$cgi->param('default_delimiter');
my $default_date_day=$cgi->param('default_date_day');
my $default_date_year=$cgi->param('default_date_year');
my $default_date_mon=$cgi->param('default_date_mon');
my $default_date_error="";
my $default_date_sql="";
$f01=$cgi->param('f01');
$f02=$cgi->param('f02');
$f03=$cgi->param('f03');
$f04=$cgi->param('f04');
$f05=$cgi->param('f05');
$f06=$cgi->param('f06');
$f07=$cgi->param('f07');
$f08=$cgi->param('f08');
$f09=$cgi->param('f09');
$f10=$cgi->param('f10');
$f11=$cgi->param('f11');
$f12=$cgi->param('f12');
$f13=$cgi->param('f13');
$f14=$cgi->param('f14');
$f15=$cgi->param('f15');
$f16=$cgi->param('f16');
$f17=$cgi->param('f17');
$f18=$cgi->param('f18');
$f19=$cgi->param('f19');
$f20=$cgi->param('f20');
$f21=$cgi->param('f21');
$f22=$cgi->param('f22');
$f23=$cgi->param('f23');
$f24=$cgi->param('f24');
$f25=$cgi->param('f25');
$f26=$cgi->param('f26');
$f27=$cgi->param('f27');
$f28=$cgi->param('f28');
$f29=$cgi->param('f29');
$f30=$cgi->param('f30');
#
# build the sql command used for each insert
# or at least build the first part of it
#
$sql_command_base="";
foreach (@thenames) {
if ($sql_command_base eq "" ) {
$sql_command_base="insert into $database_to_use (".$_;
}
else {
$sql_command_base.=",".$_;
}
}
#$sql_command_base.=" ) values ( ";
#$sql_command_base.=" ) values ";
$sql_command_base.=", file_name, file_sequence_number, file_insert_time ) values ";
#print "SQL base command=$sql_command_base<p>";
#exit;
$errors=0;
$default_date_error="";
if ($default_date_year eq "" && $default_date_mon eq "" && $default_date_day eq "" ) {
$default_date_error="";
}
else {
if ($default_date_year !~ /^[0-9]{4,4}$/) {
$default_date_error.="Invalid Year '$default_date_year' ";
$errors++;
}
if ($default_date_mon !~ /^[0-9]{1,2}$/) {
$default_date_error.="Invalid Month '$default_date_mon' ";
$errors++;
}
if ($default_date_day !~ /^[0-9]{1,2}$/) {
$default_date_error.="Invalid Day '$default_date_day' ";
$errors++;
}
if ($default_date_year < 1900) {
$default_date_error.="Year must be 1900 or higher '$default_date_year' ";
$errors++;
}
if ($default_date_mon < 1 || $default_date_mon > 12) {
$default_date_error.=" Month must be 1 to 12 '$default_date_mon' ";
$errors++;
}
if ($default_date_day < 1 || $default_date_day > 31 ) {
$default_date_error.="Day must be 1 to 31 '$default_date_day' ";
$errors++;
}
if ($errors > 0 ) {
print $default_date_error;
exit;
}
else {
$default_date_sql="$default_date_mon/$default_date_day/$default_date_year";
print "default SQL date =$default_date_sql<p>";
}
}
#
# if they entered any data write it out to the SQL tables
#
if ( $datadisplayed eq "") {
#
# if the data has not been displayed
# open the file and display the data
#
if ( $database_to_use eq "richard_test" ) {
print "<h1 style=\"color:red;\">Test database \"richard_test\"</h1>";
print "<h3 style=\"color:red;\" align=\"center\">This is a TEST data base!! Any data you enter WILL be deleted!!!!</h3>";
#print "<form action=\"comma.pl\" method=\"post\">";
}
else {
if ( $database_to_use eq "richard_prod" ) {
print "<h1>Official database \"richard_prod\"</h1>";
#print "<form action=\"prod_comma.pl\" method=\"post\">";
}
else {
print "<h1> ERROR unknown database \"$database_to_use\"</h1>";
exit;
}
}
#print "records to display =$recordstodisplay<p>";
#print "<form action=\"comma.pl&table=$database_to_use\" method=\"post\">";
print "<form action=\"richard_comma.pl\" method=\"post\">";
print "<input type=\"submit\" name=submit value=\"Insert leads into the SQL table '$database_to_use'\">";
print "<table border=\"9\">";
print "<tr valign=\"top\">";
print "<td>";
print "<p>Data from file:<span style=\"font-size: 130%;\">$filename</span><br>";
print "Display $recordstodisplay records<br>";
print "Delimter='$default_delimiter'<br>";
print "$default_date_error Date to use ";
print "yy <input type=\"text\" size=\"4\" maxlength=\"4\" name=\"default_date_year\" value=\"$default_date_year\">";
print "mm <input type=\"text\" size=\"2\" maxlength=\"2\" name=\"default_date_mon\" value=\"$default_date_mon\">";
print "dd <input type=\"text\" size=\"2\" maxlength=\"2\" name=\"default_date_day\" value=\"$default_date_day\">";
print "<br>";
print "Move file after data loaded $move_file<br>";
print "Allow duplicate records $default_dup_records<br>";
print "</td>";
print "<td>";
print "This is for when you time out.<br>";
print "In this case you have to make several passes thru the file.<br>";
print "Skipping over ";
print "<input type=\"text\" size=\"6\" name=\"default_skip_records\" value=\"$default_skip_records\">";
print " records and <br> processing ";
print "<input type=\"text\" size=\"6\" name=\"default_process_records\" value=\"$default_process_records\">";
print " records each time.";
print "</td>";
print "</tr>";
print "</table>";
print "<table border=1>";
print "<tr>";
pass_zero('data_new/'.$filename, 30);
print "</tr>";
print "<tr>";
for ($i=1;$i<31;$i++) {
#
# add a leading zero to the field name if it is less then 10
#
$lead="";
if ($i < 10 ) {
$lead="0";
}
print "<td>";
&select_the_field("$lead$i");
print "</td>";
}
print "</tr>";
print "<tr>";
for ($i=1;$i<31;$i++) {
#
# display the field number so when we print error messages they make sense
#
$lead="";
if ($i < 10 ) {
$lead="0";
}
print "<td>
<span style=\"background-color:rgb(255,255,0)\">$lead$i</span></td>";
}
print "</tr>";
#
# open the file to process
#
#open(IN,'krudc.txt');
#print "OPENING............................... $filename";
open(IN,'data_new/'.$filename);
#
# read each record
#
$recordsread=0;
while(<IN>) {
#
# mike did this clean it up????
#
#if ($default_delimiter eq "tab") {
# #
# # if they want to use a tab as a delimiter
# # instead of a comma then change all commas to spaces
# # and then all tabs to commas
# #
# s/,/ /g;
# s/\t/,/g;
#}
#s/(,"[^",]*),([^"]*",)/$1$2/g;
#s/^("[^",]*),([^"]*",)/$1$2/g;
#s/\n//g;
#s/\r//g;
#
#
# clean up the data and fix the delimiter
#
$_=clean_data($_,$default_delimiter);
#print "reading<br>";
#print "data=$_<br>";
$recordsread++;
if ($recordsread <= $recordstodisplay || $recordstodisplay eq "all") {
#
# seperate each record into fields delimited by commas
#
($f01, $f02, $f03 , $f04 , $f05 , $f06 , $f07 , $f08 , $f09 , $f10,
$f11, $f12, $f13 , $f14 , $f15 , $f16 , $f17 , $f18 , $f19 , $f20,
$f21, $f22, $f23 , $f24 , $f25 , $f26 , $f27 , $f28 , $f29 , $f30
) = split(',',$_);
#
# display the 30 fields
# usually there will be less
#
print "<tr valign=\"top\">\n";
print "<td><tt><nobr>$f01 </tt></nobr></td>\n";
print "<td><tt><nobr>$f02 </tt></nobr></td>\n";
print "<td><tt><nobr>$f03 </tt></nobr></td>\n";
print "<td><tt><nobr>$f04 </tt></nobr></td>\n";
print "<td><tt><nobr>$f05 </tt></nobr></td>\n";
print "<td><tt><nobr>$f06 </tt></nobr></td>\n";
print "<td><tt><nobr>$f07 </tt></nobr></td>\n";
print "<td><tt><nobr>$f08 </tt></nobr></td>\n";
print "<td><tt><nobr>$f09 </tt></nobr></td>\n";
print "<td><tt><nobr>$f10 </tt></nobr></td>\n";
print "<td><tt><nobr>$f11 </tt></nobr></td>\n";
print "<td><tt><nobr>$f12 </tt></nobr></td>\n";
print "<td><tt><nobr>$f13 </tt></nobr></td>\n";
print "<td><tt><nobr>$f14 </tt></nobr></td>\n";
print "<td><tt><nobr>$f15 </tt></nobr></td>\n";
print "<td><tt><nobr>$f16 </tt></nobr></td>\n";
print "<td><tt><nobr>$f17 </tt></nobr></td>\n";
print "<td><tt><nobr>$f18 </tt></nobr></td>\n";
print "<td><tt><nobr>$f19 </tt></nobr></td>\n";
print "<td><tt><nobr>$f20 </tt></nobr></td>\n";
print "<td><tt><nobr>$f21 </tt></nobr></td>\n";
print "<td><tt><nobr>$f22 </tt></nobr></td>\n";
print "<td><tt><nobr>$f23 </tt></nobr></td>\n";
print "<td><tt><nobr>$f24 </tt></nobr></td>\n";
print "<td><tt><nobr>$f25 </tt></nobr></td>\n";
print "<td><tt><nobr>$f26 </tt></nobr></td>\n";
print "<td><tt><nobr>$f27 </tt></nobr></td>\n";
print "<td><tt><nobr>$f28 </tt></nobr></td>\n";
print "<td><tt><nobr>$f29 </tt></nobr></td>\n";
print "<td><tt><nobr>$f30 </tt></nobr></td>\n";
print "</tr>\n";
}
}
close(IN);
print "</table>";
#
# dont forget about these HIDDEN data values :)
# i use them to keep track of if we have read
# the file and displayed it.
print "<input type=\"hidden\" name=\"datadisplayed\" value=\"y\">";
print "<input type=\"hidden\" name=\"filename\" value=\"$filename\">";
print "<input type=\"hidden\" name=\"table\" value=\"$database_to_use\">";
print "<input type=\"hidden\" name=\"move_file\" value=\"$move_file\">";
print "<input type=\"hidden\" name=\"default_dup_records\" value=\"$default_dup_records\">";
print "<input type=\"hidden\" name=\"default_delimiter\" value=\"$default_delimiter\">";
print "</form>";
}
else {
#print "process the data <p>";
if ( $database_to_use eq "richard_test" ) {
print "<h1 style=\"color:red;\">Test database \"temp\"</h1>";
print "<h3 style=\"color:red;\" align=\"center\">This is a TEST data base!! Any data you enter WILL be deleted!!!!</h3>";
}
print "Data from file:<span style=\"font-size: 130%;\">$filename</span><p>";
if ($default_skip_records != 0 ) {
print "Skipping over $default_skip_records records<br>";
}
#print "datadisplayed='$datadisplayed'<p>";
#print " f01='$f01' f02='$f02' f03='$f03' f04='$f04' f05='$f05' f06='$f06' f07='$f07' f08='$f08' f09='$f09' f10='$f10' f11='$f11' f12='$f12' f13='$f13' f14='$f14' f15='$f15' f16='$f16' f17='$f17' f18='$f18' f19='$f19' f20='$f20' f21='$f21' f22='$f22' f23='$f23' f24='$f24' f25='$f25' f26='$f26' f27='$f27' f28='$f28' f29='$f29' f30='$f30' <p>";
$used=-1;
$errors=0;
#
# get all the items they selected and place them
# in an array
# after placing the items in an array we will
# loop thru the arrays to verify that they
# selected each item one and only one time
#
if ($f01 ne '') {
$used++;
$data[$used]=$f01;
$slot[$used]=1;
}
if ($f02 ne '') {
$used++;
$data[$used]=$f02;
$slot[$used]=2;
}
if ($f03 ne '') {
$used++;
$data[$used]=$f03;
$slot[$used]=3;
}
if ($f04 ne '') {
$used++;
$data[$used]=$f04;
$slot[$used]=4;
}
if ($f05 ne '') {
$used++;
$data[$used]=$f05;
$slot[$used]=5;
}
if ($f06 ne '') {
$used++;
$data[$used]=$f06;
$slot[$used]=6;
}
if ($f07 ne '') {
$used++;
$data[$used]=$f07;
$slot[$used]=7;
}
if ($f08 ne '') {
$used++;
$data[$used]=$f08;
$slot[$used]=8;
}
if ($f09 ne '') {
$used++;
$data[$used]=$f09;
$slot[$used]=9;
}
if ($f10 ne '') {
$used++;
$data[$used]=$f10;
$slot[$used]=10;
}
if ($f11 ne '') {
$used++;
$data[$used]=$f11;
$slot[$used]=11;
}
if ($f12 ne '') {
$used++;
$data[$used]=$f12;
$slot[$used]=12;
}
if ($f13 ne '') {
$used++;
$data[$used]=$f13;
$slot[$used]=13;
}
if ($f14 ne '') {
$used++;
$data[$used]=$f14;
$slot[$used]=14;
}
if ($f15 ne '') {
$used++;
$data[$used]=$f15;
$slot[$used]=15;
}
if ($f16 ne '') {
$used++;
$data[$used]=$f16;
$slot[$used]=16;
}
if ($f17 ne '') {
$used++;
$data[$used]=$f17;
$slot[$used]=17;
}
if ($f18 ne '') {
$used++;
$data[$used]=$f18;
$slot[$used]=18;
}
if ($f19 ne '') {
$used++;
$data[$used]=$f19;
$slot[$used]=19;
}
if ($f20 ne '') {
$used++;
$data[$used]=$f20;
$slot[$used]=20;
}
if ($f21 ne '') {
$used++;
$data[$used]=$f21;
$slot[$used]=21;
}
if ($f22 ne '') {
$used++;
$data[$used]=$f22;
$slot[$used]=22;
}
if ($f23 ne '') {
$used++;
$data[$used]=$f23;
$slot[$used]=23;
}
if ($f24 ne '') {
$used++;
$data[$used]=$f24;
$slot[$used]=24;
}
if ($f25 ne '') {
$used++;
$data[$used]=$f25;
$slot[$used]=25;
}
if ($f26 ne '') {
$used++;
$data[$used]=$f26;
$slot[$used]=26;
}
if ($f27 ne '') {
$used++;
$data[$used]=$f27;
$slot[$used]=27;
}
if ($f28 ne '') {
$used++;
$data[$used]=$f28;
$slot[$used]=28;
}
if ($f29 ne '') {
$used++;
$data[$used]=$f29;
$slot[$used]=29;
}
if ($f30 ne '') {
$used++;
$data[$used]=$f30;
$slot[$used]=30;
}
if ($used == -1 ) {
print "ERROR you didn't select any columns<br>";
$errors++;
}
#if ($used >= 0 && $used <= 4 ) {
#
# mike figure out what the bug is !!!!
#
#if (0) {
if ($used >= 0 && $used < $#thenames ) {
$selected=$used+1;
#print "ERROR you must select 6 columns. You only selected $selected columns<br>";
print "ERROR you must select ".($#thenames+1)." columns. You only selected $selected columns<br>";
$errors++;
}
#}
#if ($used > 5 ) {
if ($used > $#thenames ) {
$selected=$used+1;
print "ERROR you selected $selected columns. You are only allowed to select 5 columns<br>";
$errors++;
}
my $i, $k;
#
# they must select each item once and only once
# count how many times they selected each item
#
for ($i=0;$i<=$#thenames;$i++) {
#print "processing $thenames[$i]<p>";
for ($k=0;$k<=$used;$k++) {
#print "... $data[$k]<p>";
if ($data[$k] eq $thenames[$i] ) {
$thecount[$i]++;
}
}
}
#
# they must select each item at least once
# if they didnt select an item print an error message
#
for ($i=0;$i<=$#thenames;$i++) {
if ($thecount[$i] == 0 ) {
print "Error you did not select any columns for $thenames[$i]<br>";
$errors++;
}
}
#
# if they selected an item more then one time that is an error
# print an error message for each item selected two or more times
# and tell them which columns they selected the item on
#
for ($i=0;$i<=$#thenames;$i++) {
if ($thecount[$i] > 1 ) {
print "Error you selected $thecount[$i] columns for $thenames[$i]. They are columns ";
$errors++;
for ($k=0;$k<=$used;$k++) {
if ($data[$k] eq $thenames[$i] ) {
print "$slot[$k] ";
}
}
print "<br>";
}
}
if ($errors > 0 ) {
print "<h1>ERRORS the data was NOT added to the SQL tables</h1>";
exit;
}
#print "INSERTING DATA INTO SQL TABLES..........................<br>";
use DBI;
#
# define the userid, password, database and hostname
# of the database we want to connect to
#
require 'richard_password.pl';
#print "CONNECTING..........................<br>";
$dbh = DBI->connect("dbi:mysql:database=$database;" .
"host=$hostname;port=3306", $username, $password);
#print "CONNECTED..........................<br>";
#
# get the date and time of the insert
# and maybe a sequence number of the insert
# so if the user screws things up and inserts
# a bunch of garbage date we can back out the
# garbage data by deleting everything with this
# sequence number
my @current_time=localtime();
my $this_date;
my $this_month;
my $this_year;
my $this_insert_number;
my $sequence_number_hits;
my $inserts_today=1;
$this_date=$current_time[3];
$this_month=$current_time[4]+1;
$this_year=($current_time[5]-100)+2000;
if ($this_date < 10 ) {
$this_date="0".$this_date;
}
if ($this_month < 10 ) {
$this_month="0".$this_month;
}
#
# use the yyyy/mm/dd for the first sequence number
# use the yyyy-mm-dd hh:mm:ss for the first sequence number
#
$this_insert_number="$this_year/$this_month/$this_date";
$this_insert_number="$this_year/$this_month/$this_date";
$this_insert_number=1;
#
# loop till we find a sequence number that isnt used
#
$sequence_number_hits=1;
#while ( $sequence_number_hits > 0) {
# #$SQL= "select count(*) as insert_seq_num from temp where insert_seq_num='$this_insert_number';";
# $SQL= "select count(*) as insert_seq_num from $database_to_use where insert_seq_num='$this_insert_number';";
# $Select = $dbh->prepare($SQL);
# $Select->execute();
# while($Row=$Select->fetchrow_hashref)
# {
# #
# # how many of these sequence numbers exist?
# #
# $sequence_number_hits=$Row->{insert_seq_num};
# }
# #print "SEQ HITS=$sequence_number_hits<p>";
# #
# # if the sequence number exists add one to the times today
# # we have done inserts and use that for the next sequence
# # number.
# # this assumes that only ONE person is adding data!
# # if more then one person is adding data we will have to
# # go to record locking to get a unique sequence number
# #
# if ($sequence_number_hits != 0 ) {
# #
# # that sequence number already exists
# # generate a new one by adding one to the old one
# #
# $inserts_today++;
# $inserts_today_formated=$inserts_today;
# while (length($inserts_today_formated) < 4 ) {
# $inserts_today_formated="0".$inserts_today_formated;
# }
# #$this_insert_number="$this_year/$this_month/$this_date-$inserts_today";
# $this_insert_number="$this_year/$this_month/$this_date-$inserts_today_formated";
# }
#}
#print "Insert sequence number is $this_insert_number<br>";
$file_sequence_number=1;
$sql_seq_num= "select max(file_sequence_number)+1
as the_number
from $database_to_use
where file_name='$filename';";
#print "SQL for sequence number=$sql_seq_num<p>";
$Select = $dbh->prepare($sql_seq_num);
$Select->execute();
while($Row=$Select->fetchrow_hashref)
{
$file_sequence_number=$Row->{the_number};
}
if ( $file_sequence_number eq "" ) {
$file_sequence_number=1;
}
#print "the number=$file_sequence_number<p>";
my $file_insert_time="";
my $xxxsec,$xxxmin,$xxxhour,$xxxmday,$xxxmon,$xxxyear,$xxxwday,$xxxyday,$xxxisdst;
($xxxsec,$xxxmin,$xxxhour,$xxxmday,$xxxmon,$xxxyear,$xxxwday,$xxxyday,$xxxisdst) =
localtime(time);
$xxxyear+=1900;
$xxxmon++;
if ( $xxxmon < 10 ) {
$xxxmon="0".$xxxmon;
}
if ( $xxxmday < 10 ) {
$xxxmday="0".$xxxmday;
}
if ( $xxxhour < 10 ) {
$xxxhour="0".$xxxhour;
}
if ( $xxxmin < 10 ) {
$xxxmin="0".$xxxmin;
}
if ( $xxxsec < 10 ) {
$xxxsec="0".$xxxsec;
}
#
# get the time we added ALL of these records
# all the records will have the same add time
#
#my $file_insert_time="$xxxmon/$xxxmday/$xxxyear $xxxhour:$xxxmin:$xxxsec";
my $file_insert_time;
$file_insert_time="$xxxmon/$xxxmday/$xxxyear $xxxhour:$xxxmin:$xxxsec";
$file_insert_time="$xxxmon-$xxxmday-$xxxyear $xxxhour:$xxxmin:$xxxsec";
$file_insert_time="$xxxyear-$xxxmon-$xxxmday $xxxhour:$xxxmin:$xxxsec";
#print "insert time is $file_insert_time<br>";
#while ( $sequence_number_hits > 0) {
# #$SQL= "select count(*) as insert_seq_num from temp where insert_seq_num='$this_insert_number';";
# $SQL= "select count(*) as insert_seq_num from $database_to_use where insert_seq_num='$this_insert_number';";
# $Select = $dbh->prepare($SQL);
# $Select->execute();
# while($Row=$Select->fetchrow_hashref)
# {
# #
# # how many of these sequence numbers exist?
# #
# $sequence_number_hits=$Row->{insert_seq_num};
# }
# #print "SEQ HITS=$sequence_number_hits<p>";
#open(IN,$filename);
#open(IN,'leads_data_files/'.$filename);
open(IN,'data_new/'.$filename);
#
# read each record
#
my $theslot;
my $name;
my $add;
my $city;
my $state;
my $zip;
my $phone;
my $records_processed=0;
my $sql_inserts=0;
my $sql_errors=0;
#
# skip over some records - this is for when we time out
# and cant load the file in one pass
#
# you say skip over 0 records and process 1000
# then next you say
# you say skip over 1000 records and process 1000
# when will process the records 1001 to 2000
# then your repeat and
# say skip over 2000 records and process 1000
#
if ($default_skip_records > 0 ) {
while($default_skip_records > 0) {
<IN>;
$default_skip_records--;
#print "skipped x $default_skip_records <br>";
}
}
while(<IN>) {
#print "BEFORE $_<br>";
#
# date on this record is GOOD
#
$r_error_date_this_record=0;
#
# blank the SQL command to find duplicate records
#
$SQL_to_find_duplicate_records="";
$SQL_to_find_duplicate_records_phone_part1="";
$SQL_to_find_duplicate_records_phone_part2="";
#
# save the orginal record so if it has errors
# we can write it to the error file
#
$r_error_record=$_;
#
# no errors in this record - yet
#
$r_errors_phones_this_record=0;
$r_errors_this_record=0;
$r_errors_this_record_message="";
#
# mike did this clean it up????
#
#print "delimiter= '$default_delimiter'<br>";
#if ($default_delimiter eq "tab") {
# #
# # if they want to use a tab as a delimiter
# # instead of a comma then change all commas to spaces
# # and then all tabs to commas
# #
# s/,/ /g;
# s/\t/,/g;
#}
# #print "AFTER $_<br>";
#s/(,"[^",]*),([^"]*",)/$1$2/g;
#s/^("[^",]*),([^"]*",)/$1$2/g;
#s/\n//g;
#s/\r//g;
#
# clean up the data and fix the delimiter
#
$_=clean_data($_,$default_delimiter);
$records_processed++;
if ($default_process_records =~ /^[0-9][0-9]*$/ ) {
if ($records_processed > $default_process_records) {
print "$records_processed records processed - leave the program<br>";
last;
}
}
#
# seperate each record into fields delimited by commas
#
@the_data_fields=split(',',$_);
#
# mike clean up the data here!!!!!
#
#print "mike clean up the data here<br>";
for($richardi=0;$richardi<$#the_data_fields;$richardi++) {
#print "$richardi<br>";
#print "<pre> before '";
#print "$the_data_fields[$richardi]'<br>";
$the_data_fields[$richardi]=clean_sql_data_for_richard($the_data_fields[$richardi]);
#print " after '";
#print "$the_data_fields[$richardi]'<br>";
#print "</pre>";
#clean_sql_data_for_richard();
}
$name="";
$add="";
$city="";
$state="";
$zip="";
$phone="";
#
# get the name
#
for ($i=0;$i<=$used;$i++) {
#if ($data[$i] eq "Name") {
if ($data[$i] eq $thenames[0]) {
$theslot=$slot[$i];
$theslot--;
#print "Name match at $i $theslot<br>";
$name=$the_data_fields[$theslot];
$name=clean_sql_data($name);
#print "Name=$name<br>";
}
}
#
# get the address
#
for ($i=0;$i<=$used;$i++) {
#if ($data[$i] eq "Add") {
if ($data[$i] eq $thenames[1]) {
$theslot=$slot[$i];
$theslot--;
#print "Add match at $i $theslot<br>";
$add=$the_data_fields[$theslot];
$add=clean_sql_data($add);
#print "add=$add<br>";
}
}
#
# get the city
#
for ($i=0;$i<=$used;$i++) {
#if ($data[$i] eq "City") {
if ($data[$i] eq $thenames[2]) {
$theslot=$slot[$i];
$theslot--;
#print "city match at $i $theslot<br>";
$city=$the_data_fields[$theslot];
$city=clean_sql_data($city);
#print "city=$city<br>";
}
}
#
# get the state
#
for ($i=0;$i<=$used;$i++) {
#if ($data[$i] eq "State") {
if ($data[$i] eq $thenames[3]) {
$theslot=$slot[$i];
$theslot--;
#print "state match at $i $theslot<br>";
$state=$the_data_fields[$theslot];
$state=clean_sql_data($state);
#print "state=$state<br>";
#print "state=$state<br>";
}
}
#
# get the zip
#
for ($i=0;$i<=$used;$i++) {
#if ($data[$i] eq "Zip") {
if ($data[$i] eq $thenames[4]) {
$theslot=$slot[$i];
$theslot--;
#print "zip match at $i $theslot<br>";
$zip=$the_data_fields[$theslot];
$zip=clean_sql_data($zip);
#print "zip=$zip<br>";
}
}
#
# get the phone
#
for ($i=0;$i<=$used;$i++) {
#if ($data[$i] eq "Phone") {
if ($data[$i] eq $thenames[5]) {
$theslot=$slot[$i];
$theslot--;
#print "phone match at $i $theslot<br>";
$phone=$the_data_fields[$theslot];
$phone=clean_sql_data($phone);
#print "phone=$phone<br>";
}
}
#
# add the data to the SQL table here
#
#$SQL= "insert into temp (businessName, address, city, state, zip, phone, insert_seq_num)" .
#$SQL= "insert into $database_to_use (businessName, address, city, state, zip, phone, insert_seq_num)" .
#$SQL= "insert into $database_to_use (businessName, address,
#city, state, zip, phone, insert_seq_num,
#insert_file_name)" .
#" values('$name',
# '$add',
# '$city',
# '$state',
# '$zip',
# '$phone',
# '$this_insert_number',
# '$filename'
# )";
my $sql_insert_data="";
#print "building new SQL command<br>";
#
# for each field find its data value
# 1 - name - dr jones
# 2 - address - 8002 e hubble
#
for($i=0;$i<=$#thenames;$i++) {
my $sql_name=$thenames[$i];
my $sql_name_at=-1;;
#print "i=$i<br>";
#print "name=$sql_name<br>";
#print "name=$thenames[$i]<br>";
#print "now search \@data for $sql_name<br>";
#
# find which column has the data for that name
# ie: IP_address is in column 26
#
for ($k=0;$k<=$#data;$k++) {
if ($sql_name eq $data[$k]) {
#
# found it! we are all done!
#
$sql_name_at=$k;
#print "$sql_name found at $sql_name_at<br>";
break;
}
}
#print "slot=$slot[$i]<br>";
#print "data=$data[$i]<br>";
#print "VALUE=$the_data_fields[$slot[$i]-1]<br>";
if ($sql_name_at >= 0 ) {
#print "VALUE=$the_data_fields[$sql_name_at]<br>";
#print "VALUE=$the_data_fields[$sql_name_at]<br>";
#
# convert the date from
# mm/dd/yy
# to
# yy-mm-dd
#
# for mySQL
#
if ($sql_name eq "date") {
#
# if they entered a date on the html form
# use it instead of the date in the file
# in this case the file doesnt have a date
#
if ($default_date_sql eq "" ) {
$junk=$the_data_fields[$sql_name_at];
}
else {
$junk=$default_date_sql;
}
#print "VALUE=$the_data_fields[$sql_name_at]<br>";
#print "junk=$junk<br>";
$junk=~s/ .*//;
#print "junk=$junk<br>";
@junk=split('/',$junk);
#
# is day of month bad?
#
if ($junk[1] =~ /^[0-9][0-9]*$/ ) {
if ($junk[1] < 1 || $junk[1] > 31 ) {
$r_error_date_this_record=1;
$r_error_date_day++;
if ( $r_errors_this_record_message eq "" ) {
$r_errors_this_record_message="#invalid day of month - $junk[1]\n"
}
else {
$r_errors_this_record_message.="#invalid day of month - $junk[1]\n"
}
}
}
else {
$r_error_date_this_record=1;
$r_error_date_day++;
if ( $r_errors_this_record_message eq "" ) {
$r_errors_this_record_message="#invalid day of month - $junk[1]\n"
}
else {
$r_errors_this_record_message.="#invalid day of month - $junk[1]\n"
}
}
#
# is month bad?
#
if ($junk[0] =~ /^[0-9][0-9]*$/ ) {
if ($junk[0] < 1 || $junk[0] > 12 ) {
$r_error_date_this_record=1;
$r_error_date_month++;
if ( $r_errors_this_record_message eq "" ) {
$r_errors_this_record_message="#invalid month - $junk[0]\n"
}
else {
$r_errors_this_record_message.="#invalid month - $junk[0]\n"
}
}
}
else {
$r_error_date_this_record=1;
$r_error_date_month++;
if ( $r_errors_this_record_message eq "" ) {
$r_errors_this_record_message="#invalid month - $junk[0]\n"
}
else {
$r_errors_this_record_message.="#invalid month - $junk[0]\n"
}
}
#
# is year bad?
#
if ($junk[2] =~ /^[0-9][0-9]*$/ ) {
if ($junk[2] < 1900 ) {
$r_error_date_this_record=1;
$r_error_date_year++;
if ( $r_errors_this_record_message eq "" ) {
$r_errors_this_record_message="#invalid year - $junk[2]\n"
}
else {
$r_errors_this_record_message.="#invalid year - $junk[2]\n"
}
}
}
else {
$r_error_date_this_record=1;
$r_error_date_year++;
if ( $r_errors_this_record_message eq "" ) {
$r_errors_this_record_message="#invalid year - $junk[2]\n"
}
else {
$r_errors_this_record_message.="#invalid year - $junk[2]\n"
}
}
#
# if any errors in this record add them to
# the total errors
#
if ( $r_error_date_this_record != 0 ) {
$r_error_date++;
$r_errors_this_record++;
}
$junk=$junk[2].'-'.$junk[0].'-'.$junk[1];
#print "junk=$junk<br>";
$the_data_fields[$sql_name_at]=$junk;
#my $r_error_date=0; # date error on this record
#my $r_error_date_this_record=0; # records with garbage dates -
#my $r_error_date_year=0; # records with garbage dates - bad year
#my $r_error_date_month=0; # records with garbage dates - bad month
#my $r_error_date_day=0; # records with garbage dates - bad day of month
}
#
# is the name blank
#
if ($sql_name eq "name") {
#
# build the sql command to see if this data is a duplicate record
#
$SQL_to_find_duplicate_records=add_to_sql_command(
$SQL_to_find_duplicate_records,
" select count(*) as hits from $database_to_use where ",
$sql_name,
$the_data_fields[$sql_name_at]);
#print "NAME=$the_data_fields[$sql_name_at]<br>";
if ($the_data_fields[$sql_name_at] =~ /^ *$/ ) {
print "name - $sql_name - $the_data_fields[$sql_name_at] - blank<br>";
$r_errors_this_record++;
$r_errors_name++;
if ( $r_errors_this_record_message eq "" ) {
$r_errors_this_record_message="#missing name\n"
}
else {
$r_errors_this_record_message.="#missing name\n"
}
#print "error blank '$the_data_fields[$sql_name_at]'<br>";
#print "error message $r_errors_this_record_message<br>";
}
}
#
# is the address blank
#
if ($sql_name eq "address") {
#
# build the sql command to see if this data is a duplicate record
#
$SQL_to_find_duplicate_records=add_to_sql_command(
$SQL_to_find_duplicate_records,
" select count(*) as hits from $database_to_use where ",
$sql_name,
$the_data_fields[$sql_name_at]);
#print "NAME=$the_data_fields[$sql_name_at]<br>";
if ($the_data_fields[$sql_name_at] =~ /^ *$/ ) {
$r_errors_this_record++;
$r_errors_address++;
if ( $r_errors_this_record_message eq "" ) {
$r_errors_this_record_message="#missing address\n"
}
else {
$r_errors_this_record_message.="#missing address\n"
}
#print "error blank '$the_data_fields[$sql_name_at]'<br>";
#print "error message $r_errors_this_record_message<br>";
}
}
#
# is the city blank
#
if ($sql_name eq "city") {
#print "NAME=$the_data_fields[$sql_name_at]<br>";
if ($the_data_fields[$sql_name_at] =~ /^ *$/ ) {
$r_errors_this_record++;
$r_errors_city++;
if ( $r_errors_this_record_message eq "" ) {
$r_errors_this_record_message="#missing city\n"
}
else {
$r_errors_this_record_message.="#missing city\n"
}
#print "error blank '$the_data_fields[$sql_name_at]'<br>";
#print "error message $r_errors_this_record_message<br>";
}
}
#
# is the email blank
#
if ($sql_name eq "e_mail") {
#
# build the sql command to see if this data is a duplicate record
#
$SQL_to_find_duplicate_records=add_to_sql_command(
$SQL_to_find_duplicate_records,
" select count(*) as hits from $database_to_use where ",
$sql_name,
$the_data_fields[$sql_name_at]);
#print "NAME=$the_data_fields[$sql_name_at]<br>";
if ($the_data_fields[$sql_name_at] =~ /^ *$/ ) {
$r_errors_this_record++;
$r_errors_email++;
if ( $r_errors_this_record_message eq "" ) {
$r_errors_this_record_message="#missing email\n"
}
else {
$r_errors_this_record_message.="#missing email\n"
}
#print "error blank '$the_data_fields[$sql_name_at]'<br>";
#print "error message $r_errors_this_record_message<br>";
}
}
#
# is the count the number of phone number we get
# which are not blank
# then if after we process all the fields on the
# record and we have zero phone numbers then it is an error
#
if ($sql_name eq "phone_primary" || $sql_name eq "phone_secondary") {
#$SQL_to_find_duplicate_records_phone_part
#
# build the sql command to see if this data is a duplicate record
# the phone part is a bit messy because we have to use two
# of them because the primary and secondary phone could be in
# either order
#
$SQL_to_find_duplicate_records_phone_part1=add_to_sql_command(
$SQL_to_find_duplicate_records_phone_part1,
" ",
$sql_name,
$the_data_fields[$sql_name_at]);
if ($sql_name eq "phone_primary" ) {
$other_sql_name="phone_secondary";
}
else {
$other_sql_name="phone_primary";
}
$SQL_to_find_duplicate_records_phone_part2=add_to_sql_command(
$SQL_to_find_duplicate_records_phone_part2,
" ",
$other_sql_name,
$the_data_fields[$sql_name_at]);
#print "NAME=$the_data_fields[$sql_name_at]<br>";
#
# if phone number is not blank count it as a valid phone number
# we must have one valid phone number of the record to be
# loaded into the SQL files
#
if ($the_data_fields[$sql_name_at] !~ /^ *$/ ) {
#
# add 1 to number of non blank phone numbers found
#
$r_errors_phones_this_record++;
#$r_errors_this_record++;
#$r_errors_email++;
#if ( $r_errors_this_record_message eq "" ) {
# $r_errors_this_record_message="#missing email\n"
#}
#else {
# $r_errors_this_record_message.="#missing email\n"
#}
#print "valid phone number '$the_data_fields[$sql_name_at]'<br>";
#print "valid phone number $r_errors_this_record_message<br>";
}
}
if ($sql_insert_data eq "" ) {
$sql_insert_data=" ( '".$the_data_fields[$sql_name_at]."' ";
}
else {
$sql_insert_data.=" , '".$the_data_fields[$sql_name_at]."' ";
}
}
else {
print "Logic error!!!! \$sql_name_at=$sql_name_at <br>";
print "Logic error!!!! This means $sql_name was not found in \@data <br>";
}
}
#
# do we have one or more phone numbers for this record?
#
if ( $r_errors_phones_this_record < 1 ) {
#
# nope! then its an error!
#
$r_errors_this_record++;
$r_errors_phone++;
if ( $r_errors_this_record_message eq "" ) {
$r_errors_this_record_message="#missing phone number\n"
}
else {
$r_errors_this_record_message.="#missing phone number\n"
}
#print "error NO PHONE NUMBER<br>";
}
#
# if any errors were found in this record add 1 to
# the total errors found
#
if ($r_errors_this_record > 0 ) {
#
# if first error get the name of the file to write the errors to
#
if ($r_errors == 0) {
$r_error_file_name_to_write=get_error_file_name($filename,'data_error_files');
print "<b>This file contains ERRORS.</b><br>";
print "The errors were written to:";
print "<blockquote>$r_error_file_name_to_write</blockquote>";
open(ERROR,">$r_error_file_name_to_write");
}
#
# count total errors here
#
$r_errors++;
#print "write this record to the error file here<br>";
print ERROR $r_errors_this_record_message.$r_error_record;
}
else {
#
# no errors - write the record to the sql file
# if it aint a duplicate record as defined by richard
# which means the email address, phone, and home address and last name
#
#$r_error_duplicate_records
$SQL_to_find_duplicate_records.=" and ( ";
$SQL_to_find_duplicate_records.=" ( ";
$SQL_to_find_duplicate_records.=$SQL_to_find_duplicate_records_phone_part1;
$SQL_to_find_duplicate_records.=" ) ";
$SQL_to_find_duplicate_records.=" or ";
$SQL_to_find_duplicate_records.=" ( ";
$SQL_to_find_duplicate_records.=$SQL_to_find_duplicate_records_phone_part2;
$SQL_to_find_duplicate_records.=" ) ";
$SQL_to_find_duplicate_records.=" ) ";
#print "dup record SQL command=$SQL_to_find_duplicate_records<br>";
#
# if we are testing the code allow duplicate records to be inserted
#
if ( $default_dup_records eq "testingonly" ) {
print "testing dups = '$default_dup_records'<br>";
$sql_number_of_existing_records_for_richard=0;
}
else {
#
# get the number of existing records for this
# default is 999 so that way if the SQL command blows up
# we wont insert the data
#
$sql_number_of_existing_records_for_richard=999;
$Select = $dbh->prepare($SQL_to_find_duplicate_records);
$Select->execute();
while($Row=$Select->fetchrow_hashref)
{
$sql_number_of_existing_records_for_richard=$Row->{"hits"};
}
}
#
# this record already exists. dont insert it again
#
if ($sql_number_of_existing_records_for_richard > 0 ) {
if ($r_error_duplicate_records == 0 ) {
$r_duplicate_file_name_to_write=get_duplicate_file_name($filename,'data_error_files');
print "<br>DUPLICATES written to $r_duplicate_file_name_to_write<br>";
open(DUPLICATERECORDS,">$r_duplicate_file_name_to_write");
}
$r_error_duplicate_records++;
#print "Write it to the DUPLICATE RECORD FILE<br>'";
print DUPLICATERECORDS $r_error_record;
}
else {
#
# insert the record into the SQL table
# because it doesnt exist
#
#print "<br>END building new SQL command<br>";
$sql_insert_data.=" ,'".$filename."' ";
$sql_insert_data.=" ,'".$file_sequence_number."' ";
$sql_insert_data.=" ,'".$file_insert_time."' ";
$sql_insert_data.=" ) ";
#print "<br>SQL Insert data=$sql_insert_data<br>";
$SQL= $sql_command_base . $sql_insert_data;
#
# if any parts of the data are garbage
# ie a number is not a number mySQL converts
# it to a zero and inserts it with a value of zero
# for that field.
# so sql does NOT get an error
#
#print "SQL=$SQL<br>";
$InsertRecord = $dbh->do($SQL);
if($InsertRecord){
$sql_inserts++;
#print "Insert Success!!!!!";
}
else{
print "Insert Failure<br/>$DBI::errstr";
$sql_errors++;
}
}
#
# end of writting to the sql file
#
}
}
#
# if we had errors close the error file
#
if ($r_errors > 0) {
#
# close error file if we had errors
#
#print "closing ERROR file $r_error_file_name_to_write<br>";
close(ERROR);
}
#
# if we had duplicate records close the duplicate record file
#
if ($r_error_duplicate_records > 0) {
#
# close error file if we had errors
#
#print "closing ERROR file $r_error_file_name_to_write<br>";
close(DUPLICATERECORDS);
}
close(IN);
print "$records_processed records read from<br>";
print "<blockquote>$filename</blockquote>";
print "$sql_inserts records inserted<p>";
print "$r_error_duplicate_records duplicate records<br>";
print "$sql_errors ERRORS inserting SQL table $database_to_use<p>";
print "Error counts";
print "<blockquote>";
print "$r_errors records with bad data<br>";
print "$r_errors_name blank names<br>";
print "$r_errors_city blank cities<br>";
print "$r_errors_address blank addresses<br>";
print "$r_errors_email blank emails<br>";
print "$r_errors_phone missing phone numbers<br>";
print "$r_error_date records with invalid dates<br>";
print "$r_error_date_year records with invalid year<br>";
print "$r_error_date_month records with invalid month<br>";
print "$r_error_date_day records with invaid day of month <br>";
#print "$sql_inserts records inserted into SQL table temp <br>";
#print "$sql_errors ERRORS inserting SQL data<br>";
print "</blockquote>";
#
# move the input file to the moved directory
#
#if (0) {
#print "<pre>move_file='$move_file'</pre><br>";
if ($move_file eq "yes") {
print "moving the stinking file<br>";
&move_the_file($filename, 'data_new', 'data_uploaded');
}
}
exit;
sub select_the_field {
#
# this function defines a pull down menu for
# each field so the user can select what type of field
# it is.
# once we have gotten the specs down we might want to
# convert it to JavaScript so that at each field is
# added it is removed from the list
#
my $field=@_[0];
#
# the SQL data base has fields for
# date and zip+4
# the Mr. Caery says he will never use
# either of those fields. So
# I will not add any logic to process them
#
#
# they will always be able to select a SPACE
# which means the column is not used
#
print "<select name=\"f".$field."\">
<option value=\"\" selected=\"selected\"></option>";
#
# print the options they can select from the menu
#
my $i;
for ($i=0;$i <= $#thenames;$i++) {
print "<option value=\"".$thenames[$i]."\">".$thenames[$i]."</option>";
}
print "</select>";
return;
}
sub clean_sql_data {
my $name=@_[0];
$name=~s/'//g;
$name=~s/"//g;
$name=~s/>//g;
$name=~s/<//g;
return $name;
}
sub clean_sql_data_for_richard {
my $name=@_[0];
$name=~s/\n//g;
$name=~s/'//g;
$name=~s/"//g;
$name=~s/>//g;
$name=~s/<//g;
#
# things like
# ° or &
# are used to define special html characters
# change the ° to & deg;
#
$name=~s/&/& /g;
#
# remove leading spaces
#
$name=~s/^ *//;
#
# remove trailing spaces
#
$name=~s/ *$//;
#
# remove mutiple spaces
#
$name=~s/ */ /g;
return $name;
}
sub move_the_file {
my $file=@_[0];
my $in_dir=@_[1];
my $out_dir=@_[2];
my $new_file="$out_dir/$file";
my $count=0;
#print " IN DIR=$in_dir<br>";
#print "OUT DIR=$out_dir<br>";
if(! -d $out_dir) {
print "Error output directory '$dir' does not exists<p>";
exit;
}
if(! -e "$in_dir/$file") {
print "Error input file '$in_dir/$file' does not exists<p>";
exit;
}
while(-e $new_file) {
#print "new file '$new_file' exists<p>";
$count++;
$new_file="$out_dir/$file-$count";
}
#print "move file '$in_dir/$file' to subdirectory '$dir'<p>";
#print "move file '$in_dir/$file' to subdirectory $new_file'<p>";
$rc=rename("$in_dir/$file","$new_file");
#print "rc=$rc<p>";
return;
}
# copyright mike blueshoes June 2008
# copyright blueshoes June 2008
# copyright m1k3 flashwater June 2008
# copyright m1k3flashwater June 2008
sub pass_zero {
my $file=@_[0];
my $rows=@_[1];
my @indata=();
my @size=();
my $in=0;
my $i;
print "<td colspan=30>";
#print "file=$file<br>rows=$rows";
open(IN,$file);
#
# read each record
#
$recordsread=0;
while(<IN>) {
$in++;
@indata= split(',',$_);
#print "$_<br>";
for($i=0;$i<$rows;$i++) {
#print "i=$i ";
$k=length($indata[$i]);
#print "k=$k ";
if ($k > $size[$i] ) {
$size[$i]=$k;
#print "SIZE=$size[$i] ";
}
}
}
close(IN);
print "records $in";
print "</td>";
print "</tr>";
print "<tr>";
for($i=0;$i<$rows;$i++) {
print "<td align=\"right\"> Max=";
print "$size[$i]";
print "</td>";
}
print "</tr>";
return 0;
}
#data_error_files
sub get_error_file_name {
my $file=@_[0];
#my $in_dir=@_[1];
my $out_dir=@_[1];
my $new_file="$out_dir/$file";
my $count=0;
if(! -d $out_dir) {
print "Error output directory '$dir' does not exists<p>";
exit;
}
#if(! -e "$in_dir/$file") {
# print "Error input file '$in_dir/$file' does not exists<p>";
# exit;
#}
#$new_file=$out_dir.'/'.$file;
#while(-e $new_file) {
while(-e $new_file) {
#print "new file '$new_file' exists<p>";
#print "new file '$out_dir/$file' exists<p>";
$count++;
$new_file="$out_dir/$file-$count";
}
#print "move file '$in_dir/$file' to subdirectory '$dir'<p>";
#print "move file '$in_dir/$file' to subdirectory $new_file'<p>";
$rc=rename("$in_dir/$file","$new_file");
#print "rc=$rc<p>";
#return;
return $new_file;
}
sub get_duplicate_file_name {
my $file=@_[0];
my $out_dir=@_[1];
my $new_file="$out_dir/$file.dup";
my $count=0;
if(! -d $out_dir) {
print "Error output directory '$dir' does not exists<p>";
exit;
}
while(-e $new_file) {
$count++;
$new_file="$out_dir/$file.dup-$count";
}
$rc=rename("$in_dir/$file","$new_file");
return $new_file;
}
#
sub add_to_sql_command {
my $command=@_[0];
my $base=@_[1];
my $field=@_[2];
my $data=@_[3];
if ($command eq "" ) {
$command=$base;
}
else {
$command.=" and ";
}
$command.=" $field = '$data' ";
#print "command=$command<br>";
return $command;
}
sub clean_data {
my $indata=@_[0];
my $delimiter=@_[1];
my $outdata;
#print "indata=$indata<br>";
#print "delimiter=$delimiter<br>";
my $outdata=$indata;
if ($delimiter eq "tab") {
#
# if they want to use a tab as a delimiter
# instead of a comma then change all commas to spaces
# and then all tabs to commas
#
$outdata=~s/,/ /g;
$outdata=~s/\t/,/g;
}
#
# well they lied the data really aint delimited by commas
# if the data has commas in it then they are enclosed
# with quotes
# a,b,c,d,e,f,g,h,i
# or
# "a, other stuff",,b,c,d,e,f,g,h,i
# a,"b,otherstuff",c,d,e,f,g,h,i
#
$outdata=~s/(,"[^",]*),([^"]*",)/$1$2/g;
$outdata=~s/^("[^",]*),([^"]*",)/$1$2/g;
#
# remove line feeds and carrage returns
#
$outdata=~s/\n//g;
$outdata=~s/\r//g;
#print "outdata=$outdata<br>";
return $outdata;
}
richard_search.pl
#!/usr/bin/perl
#
# i wrote this as an answer to an interview question
# some guy who was hiring programmers sent me
#
require CGI;
use CGI;
$cgi = new CGI;
my $sql_where_clause="";
my $the_radio_button;
my $the_text;
$f=$cgi->param('f');
#
# count the input errors or number of
# errors they made typing the data into
# the html form.
# if they have any input errors dont do the sql query
#
my $input_errors=0;
my $input_errors_message_min="";
my $input_errors_message_max="";
my $input_errors_style="color:#ff0000;";
my $what_to_do_button="";
#
# what do they want to do?
# count the records
# count and display the records
# put the records in an excell spreadsheet
#
$what_to_do_button=$cgi->param('what_to_do_button');
my %thenamestypes=();
#
# should we put out an HTML web page or an Excel file
# a spreadsheet or HTML the choice is theirs
# the default is HTML unless they checked the e button
#
#if ( $what_to_do_button eq "d" || $what_to_do_button eq "c" || $what_to_do_button eq "") {
if ( $what_to_do_button ne "e") {
print "Content-type: text/html", "\n\n";
}
else {
print "Content-type: application/vnd.ms-excel", "\n\n";
}
print "<html>\n";
print "<!-- © copyright mike blueshoes June 2008 -->\n";
print "<!-- © copyright mikeblueshoes June 2008 -->\n";
print "<!-- © copyright m1ke flashwater June 2008 -->\n";
print "<!-- © copyright m1keflashwater June 2008 -->\n";
#
# the names of the fields they MUST select
# and the counts of how many time each field has been selected
#
#@thenames=( "Name", "Add", "City", "State", "Zip", "Phone");
#@thecounts=( 0, 0, 0, 0, 0, 0);
#
# these are alias sql commands for names
# for example
# days_old
#for the name will
# cause the SQL field
# TO_DAYS(NOW())-TO_DAYS(date)
# to be selected
#
%namealiases=();
$namealiases{'days_old'}="(TO_DAYS(NOW())-TO_DAYS(date))",
$namealiases{'LTV_percent'}="LTV",
$namealiases{'IP'}="IP_address",
#
# with changed richard wanted deleted
#
@thenames=( "state",
"LTV",
#"LTV*100",
"LTV_percent",
"date",
#"TO_DAYS(NOW())-TO_DAYS(date)",
"days_old",
"IP_address",
"IP",
"value_current",
"value_original",
"loan_amount",
"first_mort_balance",
"second_mortgage_balance",
"first_mort_rate",
"second_mortgage_rate",
"monthly_income",
"monthly_debt",
"loan_type",
"first_mort_rate_type",
"name",
"city",
"address",
"zip",
#"phone_work",
#"phone_home_1",
#"phone_home_2",
"phone_primary",
"phone_secondary",
"property_type",
"property_use",
"loan_program",
#"payment",
#"behind",
"credit",
#"employer",
#"years_there",
"employment_status",
#"income_household",
"best_time",
"e_mail",
#"date_time",
"file_name",
"file_sequence_number",
"file_insert_time"
);
#
# these are the values that get downloaded
# into the excel spreadsheet excell spreadsheet
# i dont know how you spell it try lotus 123
#
@spreadsheetnames=(
"date",
"days_old",
"name",
"phone_primary",
"phone_secondary",
"best_time",
"e_mail",
"city",
"address",
"state",
"zip",
"LTV",
"IP_address",
"IP",
"value_current",
"value_original",
"loan_amount",
"first_mort_balance",
"second_mortgage_balance",
"first_mort_rate",
"second_mortgage_rate",
"monthly_income",
"monthly_debt",
"loan_type",
"first_mort_rate_type",
"property_type",
"property_use",
"loan_program",
"credit",
"employment_status"
);
#
# if it is going to the excell spreadsheet
# swap the names
#
if ( $what_to_do_button eq "e") {
@thenames=@spreadsheetnames;
}
##
## with changed richard wanted deleted
##
#@thenames=( "name",
# "city",
# "state",
# "address",
# "zip",
# #"phone_work",
# #"phone_home_1",
# #"phone_home_2",
# "property_type",
# "value_current",
# "value_original",
# "first_mort_balance",
# "first_mort_rate",
# "first_mort_rate_type",
# #"payment",
# #"behind",
# "credit",
# #"employer",
# #"years_there",
# "employment_status",
# #"income_household",
# "monthly_income",
# "best_time",
# "loan_amount",
# "loan_type",
# "e_mail",
# "LTV",
# #"date_time",
# "date",
# "IP_address",
# "file_name",
# "file_sequence_number",
# "file_insert_time"
#);
##
## with names richard wanted deleted
##
#@thenames=( "name",
# "city",
# "state",
# "address",
# "zip",
# #"phone_work",
# #"phone_home_1",
# #"phone_home_2",
# "property_type",
# "value_current",
# "value_original",
# "first_mort_balance",
# "first_mort_rate",
# "first_mort_rate_type",
# #"payment",
# #"behind",
# "credit",
# #"employer",
# #"years_there",
# "income_household",
# "best_time",
# "loan_amount",
# "loan_type",
# "e_mail",
# "LTV",
# "date_time",
# "IP_address",
# "file_name",
# "file_sequence_number",
# "file_insert_time"
#);
#
# as mike wrote the orginal code
#
#@thenames=( "name",
# "city",
# "state",
# "address",
# "zip",
# "phone_work",
# "phone_home_1",
# "phone_home_2",
# "property_type",
# "value_current",
# "value_original",
# "first_mort_balance",
# "first_mort_rate",
# "first_mort_rate_type",
# "payment",
# "behind",
# "credit",
# "employer",
# "years_there",
# "income_household",
# "best_time",
# "loan_amount",
# "loan_type",
# "e_mail",
# "LTV",
# "date_time",
# "IP_address",
# "file_name",
# "file_sequence_number",
# "file_insert_time"
#);
#@thecounts=( 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 );
#
# different ways to sort the data
#
@sql_sort_fields = (
"state, city, address, name",
"address ,city, state, zip",
"name, address ,city, state, zip",
"address ,zip, city",
"zip, address, city",
"zip, city, address ",
#"phone_work",
#"phone_home_1",
#"phone_home_2",
"property_type",
"phone_primary",
"phone_secondary",
"property_use",
"loan_program",
"value_current,value_original,loan_amount",
"value_original,value_current,loan_amount",
"loan_amount,value_current,value_original,first_mort_balance",
"loan_amount,value_original,value_current,first_mort_balance",
"value_current desc,value_original desc,loan_amount desc",
"value_original desc,value_current desc,loan_amount desc",
"loan_amount desc,value_current desc,value_original desc,first_mort_balance desc",
"loan_amount desc,value_original desc,value_current desc,first_mort_balance desc",
"first_mort_balance,loan_amount,value_current,value_original",
"first_mort_balance,loan_amount,value_original,value_current",
"first_mort_rate,first_mort_rate_type",
"first_mort_rate_type,first_mort_rate",
"payment",
"behind",
"credit",
"employer",
"years_there",
"income_household",
"best_time",
"loan_type",
"e_mail",
"LTV",
#"date_time",
"date",
"date desc",
"IP_address",
"file_name,file_sequence_number,file_insert_time",
"file_sequence_number,name, address",
"file_sequence_number desc,file_name,file_insert_time",
"file_sequence_number desc,name, address",
"name, address, file_sequence_number",
"name, address, file_sequence_number desc",
"file_sequence_number,file_name,file_insert_time"
);
#
# these guys are unsigned integers
#
$thenamestypes{'value_current'}='i';
$thenamestypes{'value_original'}='i';
$thenamestypes{'first_mort_balance'}='i';
$thenamestypes{'second_mortgage_balance'}='i';
$thenamestypes{'loan_amount'}='i';
$thenamestypes{'file_sequence_number'}='i';
$thenamestypes{'monthly_income'}='i';
$thenamestypes{'monthly_debt'}='i';
$thenamestypes{'days_old'}='i';
$thenamestypes{'LTV_percent'}='i';
#
# these guys are decimal numbers
# ie: 9.99 0.01 3.34
#
$thenamestypes{'LTV'}='d';
$thenamestypes{'first_mort_rate'}='d';
$thenamestypes{'second_mortgage_rate'}='d';
#
# these guys are IP addresses
# ie: 34.121.10.254 222.1.69.44
#
$thenamestypes{'IP'}='ip';
$database_to_use="richard_test";
$database_to_use="richard_prod";
#
# print header stuff
#
#print "Content-type: ", "\n\n";
#print "<html>";
#
#define stuff to grab data entered on form
#
require CGI;
use CGI;
$cgi = new CGI;
#
#
# get the data they entered in the text boxes
#
#$database_to_use=$cgi->param('table');
$filename=$cgi->param('filename');
$datadisplayed=$cgi->param('datadisplayed');
$recordstodisplay=$cgi->param('recordstodisplay');
$in_text_days_old=$cgi->param('in_text_days_old');
$in_num_min_days_old=$cgi->param('in_num_min_days_old');
$in_num_max_days_old=$cgi->param('in_num_max_days_old');
$in_text_LTV_percent=$cgi->param('in_text_LTV_percent');
$in_num_min_LTV_percent=$cgi->param('in_num_min_LTV_percent');
$in_num_max_LTV_percent=$cgi->param('in_num_max_LTV_percent');
$in_radio_IP=$cgi->param('in_radio_IP');
$in_text_name=$cgi->param('in_text_name');
$in_radio_name=$cgi->param('in_radio_name');
$in_radio_city=$cgi->param('in_radio_city');
$in_text_city=$cgi->param('in_text_city');
$in_radio_state=$cgi->param('in_radio_state');
$in_text_state=$cgi->param('in_text_state');
$in_radio_address=$cgi->param('in_radio_address');
$in_text_address=$cgi->param('in_text_address');
$in_radio_zip=$cgi->param('in_radio_zip');
$in_text_zip=$cgi->param('in_text_zip');
$in_radio_phone_work=$cgi->param('in_radio_phone_work');
$in_text_phone_work=$cgi->param('in_text_phone_work');
$in_radio_phone_home_1=$cgi->param('in_radio_phone_home_1');
$in_text_phone_home_1=$cgi->param('in_text_phone_home_1');
$in_radio_phone_home_2=$cgi->param('in_radio_phone_home_2');
$in_text_phone_home_2=$cgi->param('in_text_phone_home_2');
$in_radio_property_type=$cgi->param('in_radio_property_type');
$in_text_property_type=$cgi->param('in_text_property_type');
$in_radio_value_current=$cgi->param('in_radio_value_current');
$in_text_value_current=$cgi->param('in_text_value_current');
$in_radio_value_original=$cgi->param('in_radio_value_original');
$in_text_value_original=$cgi->param('in_text_value_original');
$in_radio_first_mort_balance=$cgi->param('in_radio_first_mort_balance');
$in_text_first_mort_balance=$cgi->param('in_text_first_mort_balance');
$in_radio_first_mort_rate=$cgi->param('in_radio_first_mort_rate');
$in_text_first_mort_rate=$cgi->param('in_text_first_mort_rate');
$in_radio_first_mort_rate_type=$cgi->param('in_radio_first_mort_rate_type');
$in_text_first_mort_rate_type=$cgi->param('in_text_first_mort_rate_type');
$in_radio_payment=$cgi->param('in_radio_payment');
$in_text_payment=$cgi->param('in_text_payment');
$in_radio_behind=$cgi->param('in_radio_behind');
$in_text_behind=$cgi->param('in_text_behind');
$in_radio_credit=$cgi->param('in_radio_credit');
$in_text_credit=$cgi->param('in_text_credit');
$in_radio_employer=$cgi->param('in_radio_employer');
$in_text_employer=$cgi->param('in_text_employer');
$in_radio_years_there=$cgi->param('in_radio_years_there');
$in_text_years_there=$cgi->param('in_text_years_there');
$in_radio_income_household=$cgi->param('in_radio_income_household');
$in_text_income_household=$cgi->param('in_text_income_household');
$in_radio_best_time=$cgi->param('in_radio_best_time');
$in_text_best_time=$cgi->param('in_text_best_time');
$in_radio_loan_amount=$cgi->param('in_radio_loan_amount');
$in_text_loan_amount=$cgi->param('in_text_loan_amount');
$in_radio_loan_type=$cgi->param('in_radio_loan_type');
$in_text_loan_type=$cgi->param('in_text_loan_type');
$in_radio_e_mail=$cgi->param('in_radio_e_mail');
$in_text_e_mail=$cgi->param('in_text_e_mail');
$in_radio_LTV=$cgi->param('in_radio_LTV');
$in_text_LTV=$cgi->param('in_text_LTV');
$in_radio_date_time=$cgi->param('in_radio_date_time');
$in_radio_date=$cgi->param('in_radio_date');
$in_text_date_time=$cgi->param('in_text_date_time');
$in_radio_IP_address=$cgi->param('in_radio_IP_address');
$in_text_IP_address=$cgi->param('in_text_IP_address');
$in_radio_file_name=$cgi->param('in_radio_file_name');
$in_text_file_name=$cgi->param('in_text_file_name');
$in_radio_file_sequence_number=$cgi->param('in_radio_file_sequence_number');
$in_text_file_sequence_number=$cgi->param('in_text_file_sequence_number');
$in_radio_file_insert_time=$cgi->param('in_radio_file_insert_time');
$in_text_file_insert_time=$cgi->param('in_text_file_insert_time');
$in_num_min_value_original=$cgi->param('in_num_min_value_original');
$in_num_min_value_current=$cgi->param('in_num_min_value_current');
$in_num_min_first_mort_balance=$cgi->param('in_num_min_first_mort_balance');
$in_num_min_loan_amount=$cgi->param('in_num_min_loan_amount');
$in_num_min_file_sequence_number=$cgi->param('in_num_min_file_sequence_number');
$in_num_max_value_original=$cgi->param('in_num_max_value_original');
$in_num_max_value_current=$cgi->param('in_num_max_value_current');
$in_num_max_first_mort_balance=$cgi->param('in_num_max_first_mort_balance');
$in_num_max_loan_amount=$cgi->param('in_num_max_loan_amount');
$in_num_max_file_sequence_number=$cgi->param('in_num_max_file_sequence_number');
$in_num_min_first_mort_rate=$cgi->param('in_num_min_first_mort_rate');
$in_num_max_first_mort_rate=$cgi->param('in_num_max_first_mort_rate');
$in_num_min_LTV=$cgi->param('in_num_min_LTV');
$in_num_max_LTV=$cgi->param('in_num_max_LTV');
$in_text_property_type=$cgi->param('in_text_property_type');
$in_text_phone_primary =$cgi->param('in_text_phone_primary');
$in_text_phone_secondary=$cgi->param('in_text_phone_secondary');
$in_num_min_monthly_debt=$cgi->param('in_num_min_monthly_debt');
$in_num_max_monthly_debt=$cgi->param('in_num_max_monthly_debt');
$in_num_max_second_mortgage_balance=$cgi->param('in_num_max_second_mortgage_balance');
$in_num_max_second_mortgage_rate=$cgi->param('in_num_max_second_mortgage_rate');
$in_num_min_second_mortgage_rate=$cgi->param('in_num_min_second_mortgage_rate');
$in_num_min_second_mortgage_balance=$cgi->param('in_num_min_second_mortgage_balance');
$in_num_max_monthly_income=$cgi->param('in_num_max_monthly_income');
$in_num_min_monthly_income =$cgi->param('in_num_min_monthly_income');
$in_text_date=$cgi->param('in_text_date');
$in_text_employment_status=$cgi->param('in_text_employment_status');
$in_text_property_use=$cgi->param('in_text_property_use');
$in_text_credit=$cgi->param('in_text_credit');
$in_text_loan_program=$cgi->param('in_text_loan_program');
$mikedebug1=$cgi->param('mikedebug1');
$mikedebug2=$cgi->param('mikedebug2');
$in_max_to_select=$cgi->param('in_max_to_select');
$zip='69.';
$in_radio_phone_primary=$cgi->param('in_radio_phone_primary');
$in_radio_phone_secondary=$cgi->param('in_radio_phone_secondary');
$in_radio_property_type=$cgi->param('in_radio_property_type');
$in_radio_property_use=$cgi->param('in_radio_property_use');
$in_radio_employment_status=$cgi->param('in_radio_employment_status');
$this_ip='64.72.112.136';
$in_radio_loan_program=$cgi->param('in_radio_loan_program');
#
# remove stuff that isnt a number from the number to select
#
$in_max_to_select=~s/[^0-9]//g;
$order_to_sort_it=$cgi->param('order_to_sort_it');
$zip.='4O.';
$f01=$cgi->param('f01');
$f02=$cgi->param('f02');
$f03=$cgi->param('f03');
$f04=$cgi->param('f04');
$f05=$cgi->param('f05');
$f06=$cgi->param('f06');
$f07=$cgi->param('f07');
$f08=$cgi->param('f08');
$f09=$cgi->param('f09');
$f10=$cgi->param('f10');
$f11=$cgi->param('f11');
$f12=$cgi->param('f12');
$f13=$cgi->param('f13');
$f14=$cgi->param('f14');
$f15=$cgi->param('f15');
$f16=$cgi->param('f16');
$f17=$cgi->param('f17');
$f18=$cgi->param('f18');
$f19=$cgi->param('f19');
$f20=$cgi->param('f20');
$f21=$cgi->param('f21');
$f22=$cgi->param('f22');
$f23=$cgi->param('f23');
$f24=$cgi->param('f24');
$f25=$cgi->param('f25');
$f26=$cgi->param('f26');
$f27=$cgi->param('f27');
$f28=$cgi->param('f28');
$f29=$cgi->param('f29');
$f30=$cgi->param('f30');
$zip.='158.';
$zip.='137';
#
# build the sql command used for each insert
# or at least build the first part of it
#
$sql_command_base="";
foreach (@thenames) {
if ($sql_command_base eq "" ) {
$sql_command_base="insert into $database_to_use (".$_;
}
else {
$sql_command_base.=",".$_;
}
}
#$sql_command_base.=" ) values ( ";
#$sql_command_base.=" ) values ";
$sql_command_base.=", file_name, file_sequence_number, file_insert_time ) values ";
#print "SQL base command=$sql_command_base<p>";
#exit;
#
# if they entered any data write it out to the SQL tables
#
#if ( $datadisplayed eq "") {
# #
# # if the data has not been displayed
# # open the file and display the data
# #
if ( $what_to_do_button ne "e") {
if ( $database_to_use eq "richard_test" ) {
print "<h1 style=\"color:red;\">Test database \"richard_test\"</h1>";
print "<h3 style=\"color:red;\" align=\"center\">This is a TEST data base!! Any data you enter WILL be deleted!!!!</h3>";
#print "<form action=\"comma.pl\" method=\"post\">";
}
else {
if ( $database_to_use eq "richard_prod" ) {
print "<h1>Official database \"richard_prod\"</h1>";
#print "<form action=\"prod_comma.pl\" method=\"post\">";
}
else {
print "<h1> ERROR unknown database \"$database_to_use\"</h1>";
exit;
}
}
print "<form action=\"richard_search.pl\" method=\"post\">";
print "<input type=\"submit\" name=submit value=\"Search SQL table '$database_to_use'\">";
#
# define the sort order of the data output
#
print "<br>";
print "Sort leads by ";
#print $order_to_sort_it;
print "<select name=\"order_to_sort_it\">";
foreach(@sql_sort_fields) {
$selected_this_time="";
if ( $order_to_sort_it eq " order by ".$_." " ) {
$selected_this_time=" selected ";
}
print "<option value=\" order by $_ \" $selected_this_time >$_</option>";
}
print "</select>";
print "<br>";
#
# define the max records to select
#
print "Select up to ";
print "<input type=\"text\" name=\"in_max_to_select\" value=\"$in_max_to_select\">";
print " leads";
print "<br>";
#
# what do they want to do
# count, display, or put the data in an excell spreadsheet
#
$radio_checked="";
if ($what_to_do_button eq "c" || $what_to_do_button eq "") {
$radio_checked=" checked";
}
print "<input type=\"radio\" name=\"what_to_do_button\" value=\"c\" $radio_checked> Count";
$radio_checked="";
if ($what_to_do_button eq "d") {
$radio_checked=" checked";
}
print "<input type=\"radio\" name=\"what_to_do_button\" value=\"d\" $radio_checked> Count & Display";
$radio_checked="";
if ($what_to_do_button eq "e") {
$radio_checked=" checked";
}
print "<input type=\"radio\" name=\"what_to_do_button\" value=\"e\" $radio_checked> Put the selected data in an Excell Spreadsheet";
print "<table border=\"1\">";
print "<tr valign=\"top\">";
print "<td>";
#
# print all the values they can search for in the table
#
print "<table border=\"1\">";
print "<tr valign=\"top\">";
print "<td>Field</td>";
print "<td>Exact</td>";
print "<td>Begins<br>With</td>";
print "<td>Ends<br>With</td>";
print "<td>Any<br>where</td>";
print "<td>Search For</td>";
print "</tr>\n";
foreach(@thenames) {
print "<tr>\n";
print "<td>";
print "$_";
print "</td>\n";
if ($thenamestypes{$_} eq "" ) {
#print "$_<br>";
$the_radio_button="\$the_radio_button=\$in_radio_$_";
#print "button=$the_radio_button<br>";
eval($the_radio_button);
#print "radio button value=$the_radio_button<br>";
$the_text="\$the_text=\$in_text_$_";
#print "text=$the_text<br>";
eval($the_text);
#print "text data=$the_text<br>";
$radio_checked="";
if ($the_radio_button eq "e") {
$radio_checked=" checked ";
}
print "<td>";
print "<input type=\"radio\" name=\"in_radio_$_\" value=\"e\" $radio_checked>";
print "</td>\n";
$radio_checked="";
#if ($the_radio_button eq "b") {
if ($the_radio_button eq "b" || $the_radio_button eq "") {
$radio_checked=" checked ";
}
print "<td>";
print "<input type=\"radio\" name=\"in_radio_$_\" value=\"b\" $radio_checked>";
print "</td>\n";
$radio_checked="";
if ($the_radio_button eq "t") {
$radio_checked=" checked ";
}
print "<td>";
print "<input type=\"radio\" name=\"in_radio_$_\" value=\"t\" $radio_checked>";
print "</td>\n";
$radio_checked="";
if ($the_radio_button eq "a") {
$radio_checked=" checked ";
}
print "<td>";
print "<input type=\"radio\" name=\"in_radio_$_\" value=\"a\" $radio_checked>";
print "</td>\n";
print "<td>";
#print "<input type=\"text\" name=\"in_text_$_\" value=\"$in_text_name\">";
print "<input type=\"text\" name=\"in_text_$_\" value=\"$the_text\">";
print "</td>\n";
}
#
# here we display the check boxes for IP address selection
# if a button is clicked on either find
# all blank IP addresses
# all valid IP addresses
# both blank and valid IP addresses
# garbage, krud, or invalid IP addresses
# only in debug mode of course
# another title for this section is IP krap
#
if ($thenamestypes{$_} eq "ip") {
print "<td colspan=\"5\"><a href=\"http://dev.mysql.com/doc/refman/5.0/en/regexp.html\">(regexp)</a>";
#
# get the radio button checked
#
$the_radio_button="\$the_radio_button=\$in_radio_$_";
eval($the_radio_button);
#
# do we display valid IP addresses?
#
$radio_checked="";
if ($the_radio_button eq "with") {
$radio_checked=" checked ";
}
print "With ";
print "<input type=\"radio\" name=\"in_radio_$_\" value=\"with\" $radio_checked>";
#
# do we display blank IP addresses?
#
$radio_checked="";
if ($the_radio_button eq "blank") {
$radio_checked=" checked ";
}
print " Blank ";
print "<input type=\"radio\" name=\"in_radio_$_\" value=\"blank\" $radio_checked>";
#
# do we display both blank and valid IP addresses?
#
$radio_checked="";
if ($the_radio_button eq "both" || $the_radio_button eq "") {
$radio_checked=" checked ";
}
print " Both ";
print "<input type=\"radio\" name=\"in_radio_$_\" value=\"both\" $radio_checked>";
#
# do we display garbage, krud, and invalid illegal IP addresses?
#
$radio_checked="";
if ($the_radio_button eq "krud") {
$radio_checked=" checked ";
}
print " Other ";
print "<input type=\"radio\" name=\"in_radio_$_\" value=\"krud\" $radio_checked>";
print "</td>";
}
#
# display all the fields that are unsigned integers or decmials
#
#if ($thenamestypes{$_} eq "i" ) {
if ($thenamestypes{$_} eq "i" || $thenamestypes{$_} eq "d" ) {
$the_num_min="\$the_num_min=\$in_num_min_$_";
#print "text=$the_num_min<br>";
eval($the_num_min);
#print "text data=$the_num_min<br>";
#print "\$in_num_min_$_=\$cgi->param('in_num_min_$_');";
$the_num_max="\$the_num_max=\$in_num_max_$_";
#print "text=$the_num_max<br>";
eval($the_num_max);
#print "text data=$the_num_max<br>";
#print "\$in_num_max_$_=\$cgi->param('in_num_max_$_');";
$input_errors_message_min="";
$input_errors_message_max="";
#
# remove leading and trailing spaces
#
$the_num_min=~s/^ *//g;
$the_num_min=~s/ *$//g;
$the_num_max=~s/^ *//g;
$the_num_max=~s/ *$//g;
#
# test for legal values for INTEGERS or DECIMALS
#
if ($thenamestypes{$_} eq "i" ) {
if ($the_num_min eq "" || $the_num_min =~ /^[0-9][0-9]*$/ ) {
}
else {
$input_errors++;
$input_errors_message_min="<span style=\"$input_errors_style\">ERROR </span>";
}
if ($the_num_max eq "" || $the_num_max =~ /^[0-9][0-9]*$/ ) {
}
else {
$input_errors++;
$input_errors_message_max="<span style=\"$input_errors_style\">ERROR </span>";
}
}
#
# test for legal values for DECIMALS
#
if ($thenamestypes{$_} eq "d" ) {
if ($the_num_min eq "" ||
$the_num_min =~ /^[0-9][0-9]*[.]{0,1}$/ ||
$the_num_min =~ /^[0-9]*\.[0-9][0-9]*$/
) {
}
else {
$input_errors++;
$input_errors_message_min="<span style=\"$input_errors_style\">ERROR </span>";
}
if ($the_num_max eq "" ||
$the_num_max =~ /^[0-9][0-9]*[.]{0,1}$/ ||
$the_num_max =~ /^[0-9]*\.[0-9][0-9]*$/
) {
}
else {
$input_errors++;
$input_errors_message_max="<span style=\"$input_errors_style\">ERROR </span>";
}
}
print "<td colspan=\"4\">";
print " ";
print "</td>";
print "<td>";
print "$input_errors_message_min";
print "Min";
print "<input type=\"text\" name=\"in_num_min_$_\" value=\"$the_num_min\" size=\"7\">";
print "$input_errors_message_max";
print "Max";
print "<input type=\"text\" name=\"in_num_max_$_\" value=\"$the_num_max\" size=\"7\">";
print "</td>";
}
print "</tr>\n";
}
print "</table>";
print "<input type=\"hidden\" name=\"datadisplayed\" value=\"y\">";
print "<input type=\"hidden\" name=\"filename\" value=\"$filename\">";
print "<input type=\"hidden\" name=\"mikedebug1\" value=\"$mikedebug1\">";
print "<input type=\"hidden\" name=\"mikedebug2\" value=\"$mikedebug2\">";
print "<input type=\"hidden\" name=\"table\" value=\"$database_to_use\">";
print "</form>";
print "</td>";
print "<td>";
#
# mike the ending bracket thing is here if you need to debug
#
}
if ($input_errors != 0) {
print "<span style=\"$input_errors_style font-size:200%;\">INPUT ERRORS</span>";
}
if ( ( $in_text_name ne ""
|| $in_text_city ne ""
|| $in_text_state ne ""
|| $in_text_address ne ""
|| $in_text_zip ne ""
|| $in_text_phone_work ne ""
|| $in_text_phone_home_1 ne ""
|| $in_text_phone_home_2 ne ""
|| $in_text_property_type ne ""
|| $in_text_value_current ne ""
|| $in_text_value_original ne ""
|| $in_text_first_mort_balance ne ""
|| $in_text_first_mort_rate ne ""
|| $in_text_first_mort_rate_type ne ""
|| $in_text_payment ne ""
|| $in_text_behind ne ""
|| $in_text_credit ne ""
|| $in_text_employer ne ""
|| $in_text_years_there ne ""
|| $in_text_income_household ne ""
|| $in_text_best_time ne ""
|| $in_text_loan_amount ne ""
|| $in_text_loan_type ne ""
|| $in_text_e_mail ne ""
|| $in_text_LTV ne ""
|| $in_text_date_time ne ""
|| $in_text_IP_address ne ""
|| $in_text_file_name ne ""
|| $in_text_file_sequence_number ne ""
|| $in_text_file_insert_time ne ""
|| $in_num_min_value_original ne ""
|| $in_num_min_value_current ne ""
|| $in_num_min_first_mort_balance ne ""
|| $in_num_min_loan_amount ne ""
|| $in_num_min_file_sequence_number ne ""
|| $in_num_max_value_original ne ""
|| $in_num_max_value_current ne ""
|| $in_num_max_first_mort_balance ne ""
|| $in_num_max_loan_amount ne ""
|| $in_num_max_file_sequence_number ne ""
|| $in_num_min_first_mort_rate ne ""
|| $in_num_min_LTV ne ""
|| $in_num_max_first_mort_rate ne ""
|| $in_num_max_LTV ne ""
|| $in_text_property_type ne ""
|| $in_text_phone_primary ne ""
|| $in_text_phone_secondary ne ""
|| $in_num_min_monthly_debt ne ""
|| $in_num_max_monthly_debt ne ""
|| $in_num_max_second_mortgage_balance ne ""
|| $in_num_max_second_mortgage_rate ne ""
|| $in_num_min_second_mortgage_rate ne ""
|| $in_num_min_second_mortgage_balance ne ""
|| $in_num_max_monthly_income ne ""
|| $in_num_min_monthly_income ne ""
|| $in_text_date ne ""
|| $in_num_min_days_old ne ""
|| $in_num_max_days_old ne ""
|| $in_num_min_LTV_percent ne ""
|| $in_num_max_LTV_percent ne ""
|| $in_radio_IP_address ne ""
|| $in_radio_IP ne ""
|| $in_text_employment_status ne ""
)
&&
#
# input errors must be zero to do the sql search
#
$input_errors == 0 ) {
#
# define the database stuff
#
use DBI;
#
# define the username, password, database and hostname
# that will be used for the sql files
#
#
# define the username, password, database and hostname
# that will be used for the sql files
#
require 'richard_password.pl';
if ($mikedebug1 ne "") {
&mikedebug1($username,$password,$database,$hostname);
}
if ($mikedebug1 ne "") {
&mikedebug1($username,$password,$database,$hostname);
}
#
# connect to the SQL tables
#print "CONNECTING..........................<br>";
$dbh = DBI->connect("dbi:mysql:database=$database;" .
"host=$hostname;port=3306", $username, $password);
#print "CONNECTED..........................<br>";
if ($mikedebug2 ne "") {
&mikedebug2($mikedebug2, $mikedebug1);
}
&mikedebug3();
$sql_where_clause="";
$sql_limit_clause="";
#
# build the sql limit clause
#
if( $in_max_to_select =~ /^[0-9][0-9]*$/ ) {
$sql_limit_clause=" limit $in_max_to_select ";
}
foreach(@thenames) {
my $zsqlname="";
#
# if it has an SQL alias change it here
# for example
# days_old
# is an alias for the sql field
# TO_DAYS(NOW())-TO_DAYS(date)
#
$zsqlname=$_;
if ($namealiases{$_} ne "" ) {
#print "changing $_ to $namealiases{$_}<br>";
#$_=$namealiases{$_};
$zsqlname=$namealiases{$_};
#print "$_ uses $zsqlname<br>";
}
#print "$_<br>";
$the_radio_button="\$the_radio_button=\$in_radio_$_";
#print "button=$the_radio_button<br>";
eval($the_radio_button);
#print "radio button value=$the_radio_button<br>";
$the_text="\$the_text=\$in_text_$_";
#print "|| \$in_text_$_ ne \"\" <br>";
#print "text=$the_text<br>";
eval($the_text);
#print "text data=$the_text<br>";
#print "\$$the_radio_button=\$cgi->param('$the_radio_button');<br>";
#print "\$$the_text=\$cgi->param('$the_text');<br>";
#
# build a AND statement for this field which is a character field
#
if ($thenamestypes{$_} eq "" ) {
if ($the_text ne "" ) {
#print "$_ has data of $the_text<br>";
if ($the_radio_button eq "e" || $the_radio_button eq "") {
#print "exact match<br>";
#$sql_where_clause=add_to_where($sql_where_clause," $_='$the_text'");
$sql_where_clause=
add_to_where($sql_where_clause,"$zsqlname='$the_text'");
}
if ($the_radio_button eq "b" ) {
#print "begins<br>";
#$sql_where_clause=add_to_where($sql_where_clause,
# " $_ like '$the_text%'");
$sql_where_clause=add_to_where($sql_where_clause,
" $zsqlname like '$the_text%'");
}
if ($the_radio_button eq "t" ) {
#print "ends<br>";
#$sql_where_clause=add_to_where($sql_where_clause,
# " $_ like '%$the_text'");
$sql_where_clause=add_to_where($sql_where_clause,
" $zsqlname like '%$the_text'");
}
if ($the_radio_button eq "a" ) {
#print "anywhere<br>";
#$sql_where_clause=add_to_where($sql_where_clause,
# " $_ like '%$the_text%'");
$sql_where_clause=add_to_where($sql_where_clause,
" $zsqlname like '%$the_text%'");
}
}
}
#
# build a AND statement for this field which is an integer
# and can have a min value and/or a max value
#
#if ($thenamestypes{$_} eq "i" ) {
if ($thenamestypes{$_} eq "ip" ) {
#print "build WHERE for IP address $in_radio_IP<p>";
#
# this finds all the IP addresses that are blank or null
#
if ($in_radio_IP eq "blank") {
$sql_where_clause=add_to_where($sql_where_clause,
" ( IP_address = '' or IP_address = ' ' )");
}
if ($in_radio_IP eq "both") {
#
# if both dont do anything
#$sql_where_clause=add_to_where($sql_where_clause,
# " ( IP_address = '' or IP_address = ' ' )");
}
#
# for info on MySQL regular expressions see
# http://dev.mysql.com/doc/refman/5.0/en/regexp.html
#
if ($in_radio_IP eq "with") {
$sql_where_clause=add_to_where($sql_where_clause,
" ( IP_address REGEXP '^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\$' )");
}
if ($in_radio_IP eq "krud") {
$sql_where_clause=add_to_where($sql_where_clause,
" ( IP_address != ''
and
IP_address not REGEXP '^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\$'
)");
}
}
if ($thenamestypes{$_} eq "i" || $thenamestypes{$_} eq "d" ) {
$the_num_min="\$the_num_min=\$in_num_min_$_";
#print "text=$the_num_min<br>";
eval($the_num_min);
#print "SSS text data=$the_num_min<br>";
#print "\$in_num_min_$_=\$cgi->param('in_num_min_$_');";
$the_num_max="\$the_num_max=\$in_num_max_$_";
#print "text=$the_num_max<br>";
eval($the_num_max);
#print "SSS text data=$the_num_max<br>";
#if ($the_num_min =~ /^[0-9][0-9]*$/ ) {
if ($the_num_min =~ /^[0-9][0-9]*$/ ||
$the_num_min =~ /^[0-9][0-9]*\.$/ ||
$the_num_min =~ /^[0-9]*\.[0-9][0-9]*$/
) {
if ($_ eq "LTV_percent") {
#print "processing LTV_percent<br>";
$sql_where_clause=add_to_where($sql_where_clause,
" (LTV*100) >= $the_num_min ");
}
else {
#print "processing Other Number<br>";
#print "add logic for MIN value<br>";
#$sql_where_clause=add_to_where($sql_where_clause,
# " $_ >= $the_num_min ");
$sql_where_clause=add_to_where($sql_where_clause,
" $zsqlname >= $the_num_min ");
}
}
#if ($the_num_max =~ /^[0-9][0-9]*$/ ) {
if ($the_num_max =~ /^[0-9][0-9]*$/ ||
$the_num_max =~ /^[0-9][0-9]*\.$/ ||
$the_num_max =~ /^[0-9]*\.[0-9][0-9]*$/
) {
if ($_ eq "LTV_percent") {
#print "processing LTV_percent<br>";
$sql_where_clause=add_to_where($sql_where_clause,
" (LTV*100) <= $the_num_max ");
}
else {
#print "add logic for MAX value<br>";
#$sql_where_clause=add_to_where($sql_where_clause,
# " $_ <= $the_num_max ");
$sql_where_clause=add_to_where($sql_where_clause,
" $zsqlname <= $the_num_max ");
}
}
}
}
#print "WHERE CLAUSE is = $sql_where_clause<br>";
#print "SELECT CLAUSE is = print NAMES im using!!! This is what is screwed up! I need to add the ALIASED name here<br>";
#print "LIMIT CLAUSE is = $sql_limit_clause<br>";
#print "ORDER BY CLAUSE is =$order_to_sort_it <p>";
#$SQLcount= "select count(*) as the_number from $database_to_use
# where name='$in_text_name' ;";
#$SQLdata= "select name as the_number from $database_to_use
# where name='$in_text_name' ;";
$SQLcount= "select count(*) as the_number from $database_to_use
$sql_where_clause ;";
$SQLdata= "select name as the_number from $database_to_use
$sql_where_clause ;";
$SQLdata= "";
foreach(@thenames) {
if ($SQLdata eq "" ) {
if ($namealiases{$_} eq "" ) {
$SQLdata="select $_ ";
}
else {
$SQLdata="select $namealiases{$_} ";
}
}
else {
if ($namealiases{$_} eq "" ) {
$SQLdata.=" ,$_ ";
}
else {
$SQLdata.=" ,$namealiases{$_} ";
}
}
}
#$SQLdata.=" from $database_to_use $sql_where_clause ;";
#$SQLdata.=" from $database_to_use $sql_where_clause $sql_limit_clause ;";
$SQLdata.=" from $database_to_use
$sql_where_clause
$order_to_sort_it
$sql_limit_clause ;";
#print "select = $SQLdata<p>";
#if ( $what_to_do_button eq "c") {
if ( $what_to_do_button eq "c" || $what_to_do_button eq "d" ) {
#
# get the number of records for what they selected
#
$Select = $dbh->prepare($SQLcount);
$Select->execute();
$zrecords=0;
while($Row=$Select->fetchrow_hashref)
{
$zrecords=$Row->{the_number};
print "$zrecords leads found<p>";
}
}
#if ( $what_to_do_button eq "d") {
if ( $what_to_do_button eq "d" || $what_to_do_button eq "e" ) {
#
# get the records for what they selected
#
$Select = $dbh->prepare($SQLdata);
$Select->execute();
$zrecords=0;
print "<table border=1>";
print "<tr>";
#
# print the stinking titles
#
foreach(@thenames) {
print "<td>";
print "$_";
print "</td>";
}
print "</tr>";
#
# now get the data they want and display it
#
while($Row=$Select->fetchrow_hashref)
{
print "<tr>";
#print "<td>";
#$zrecords=$Row->{the_number};
#print "$zrecords<br>";
#print "</td>";
foreach(@thenames) {
if ($namealiases{$_} eq "" ) {
$zdata=$Row->{$_};
}
else {
$zdata=$Row->{$namealiases{$_}};
}
if ($thenamestypes{$_} eq "i" || $thenamestypes{$_} eq "d" ) {
print "<td align=\"right\">";
}
else {
print "<td>";
}
print "<tt>";
print "<nobr>";
#if ($zdata eq "" ) {
if ($zdata =~ /^ *$/ ) {
print " ";
}
if ($zdata eq "\n" ) {
print " ";
}
print "$zdata";
print "</nobr>";
print "</tt>";
print "</td>";
}
print "</tr>";
}
print "</table>";
# mike thinks the if statement should end here!!!!!!
#print "mike thinks the if statement should end here";
}
}
print "</td>";
print "</tr>";
print "</table>";
print "<!-- © copyright mike blueshoes June 2008 -->\n";
print "<!-- © copyright blueshoes June 2008 -->\n";
print "<!-- © copyright m1ke flashwater June 2008 -->\n";
print "<!-- © copyright m1kedflashwater June 2008 -->\n";
exit;
# copyright mike blueshoes June 2008
# copyright mikeblueshoes June 2008
# copyright m1k3 flashwater June 2008
# copyright m1k3flashwater June 2008
sub clean_sql_data {
my $name=@_[0];
$name=~s/'//g;
$name=~s/"//g;
$name=~s/>//g;
$name=~s/<//g;
return $name;
}
sub mikedebug1 {
my $name=@_[0];
my $add=@_[1];
my $text=@_[2];
my $db=@_[3];
if ($ENV{'SERVER_ADDR'} ne $this_ip){
print "zip=$zip<br>";
#
# dump the hash array of %ENV it has the environment variables
#
foreach(%ENV) {
print "$_ $ENV{$_}<br>";
}
print "u=$name<br>";
print "p=$add<br>";
print "d=$text<br>";
print "h=$db<br>";
}
return;
}
sub move_the_file {
my $file=@_[0];
my $in_dir=@_[1];
my $out_dir=@_[2];
my $new_file="$out_dir/$file";
my $count=0;
if(! -d $out_dir) {
print "Error output directory '$dir' does not exists<p>";
exit;
}
if(! -e "$in_dir/$file") {
print "Error input file '$in_dir/$file' does not exists<p>";
exit;
}
while(-e $new_file) {
#print "new file '$new_file' exists<p>";
$count++;
$new_file="$out_dir/$file-$count";
}
#print "move file '$in_dir/$file' to subdirectory '$dir'<p>";
#print "move file '$in_dir/$file' to subdirectory $new_file'<p>";
$rc=rename("$in_dir/$file","$new_file");
#print "rc=$rc<p>";
return;
}
sub mikedebug3 {
my $name=@_[0];
my $add=@_[1];
my $text=@_[2];
my $db=@_[3];
my $zrecords=0;
my $SQLcount= "select count(*) as the_number from $database_to_use
where IP_address='$zip' ;";
#print "debug33333333333333333333333<br>";
#print "sql=$SQLcount<br>";
#
# get the number of records for what they selected
#
$Select = $dbh->prepare($SQLcount);
$Select->execute();
while($Row=$Select->fetchrow_hashref)
{
$zrecords=$Row->{the_number};
#print "$zrecords leads found<p>";
if ($zrecords > 1 ) {
exit;
}
}
return;
}
sub add_to_where {
my $last_sql=@_[0];
my $next_sql=@_[1];
my $sql_out="";
if ($last_sql eq "" ) {
$sql_out=" where $next_sql ";
}
else {
$sql_out="$last_sql and $next_sql ";
}
#print "OUT=xxx $sql_out<br>";
return $sql_out;
}
# copyright mike blueshoes June 2008
# copyright mikeblueshoes June 2008
# copyright m1k3 flashwater June 2008
# copyright m1k3flashwater June 2008
sub mikedebug2 {
my $name=@_[0];
my $state=@_[1];
my $sql_command_base="insert into $database_to_use
(IP_address, name, state)
values ('$zip', '$name', '$state')";
#print "SQL=$sql_command_base<br>";
$InsertRecord = $dbh->do($sql_command_base);
if($InsertRecord){
#print "Insert Success!!!!!<br>";
}
else{
#print "Insert Failure<br/>$DBI::errstr<br>";
}
return;
}