#!/usr/bin/perl

use DBI();
$filetypes{"pdf"}='pdf';
$filetypes{"ps"}='pdf';
$filetypes{"html"}='html';
$filetypes{"htm"}='html';
$filetypes{"doc"}='doc';
$filetypes{"xls"}='xls';
$filetypes{"txt"}='txt';
%monthlist = (Jan => "0", Feb => "1", Mar => "2", Apr => "3", May => "4", Jun => "5", Jul => "6", Aug => "7", Sep => "8", Oct => "9", Nov => "10", Dec => "11", 0 => "Jan", 1 => "Feb", 2 => "Mar", 3 => "Apr", 4 => "May", 5 => "Jun", 6 => "Jul", 7 => "Aug", 8 => "Sep", 9 => "Oct", 10 => "Nov", 11 => "Dec");
$tempfile="/tmp/temp.txt";
$localfile="local";
$list = '';
$list .= quotemeta chr for(32..126);

#________________________________________________________________________________________________________#


$ENV{ORACLE_HOME} = "/usr/local/oracle_home";
&log_print('Index Started...');
&open_A("select dm.filename as A,dm.id as B from doc_master dm, doc_contents dc where dm.id=dc.file_id and dc.inserted='0' order by dm.id desc");
&log_print("Search Completed, Processing Started");
while(my $ref = $sth->fetchrow_hashref()) {
	$filename=$ref->{"A"};
	$id=$ref->{"B"};
	&log_print("$filename, $id");
	&get_text("$filename","$id");
	if ($contents[0]) {
		&execute_B("update doc_contents set contents='$contents[0]',inserted='1' where file_id='$id'");
		for ($k=1;$k<@contents;$k++) {
			&execute_B("insert into doc_contents (file_id,contents,inserted) values ('$id','$contents[$k]','1')");
			print "read ".$id.", got: "."$contents[$k]"."\n";
		}
		my $templength = scalar(@contents);
		my $templength2 = 2000*$templength;

		&log_print("\n$id($filename)($type) - $templength2+ chars ($templength rows added)");
	}
	else {
		if ($type eq 'pdf') {
		&execute_B("update doc_contents set contents=' ',inserted='4' where file_id='$id'");
                &log_print("\n$id($filename)($type) - PDF ignored");
		}
		else {
		&execute_B("update doc_contents set contents=' ',inserted='2' where file_id='$id'");
		&log_print("\n$id($filename)($type) - no text to write to file");
		}
	}
}
#next line removes all of the non-matching files so that they don't build up
&execute_B("update doc_contents set contents=' ',inserted='3' where inserted='0'");
&log_print("Index Completed");
&close_A();

#________________________________________________________________________________________________________#

sub open_A {
	$ENV{ORACLE_HOME} = "/usr/local/oracle_home";
	$dbh = DBI->connect('DBI:Oracle:ifx2db','ifx1','ifx1');
	$statement = "$_[0]";
	$sth = $dbh->prepare($statement);
	$rv = $sth->execute;
}

#________________________________________________________________________________________________________#

sub close_A {
	$rc = $sth->finish;
	$rc = $dbh->disconnect;
}

#________________________________________________________________________________________________________#

sub execute_B {
	$rv2 = $dbh->do($_[$INDEX]);
}

#________________________________________________________________________________________________________#

sub log_print {
	$stamp = &stamp_to_date(time);
	$logstring = "[$stamp]: $_[0]";
	print "\n$logstring\n";
}

#________________________________________________________________________________________________________#

sub get_text {
	my @a = split /\./,"$_[0]";
	$type = $filetypes{"$a[1]"};
	unless ($type) {
		$type = 'null';
	}
	#my $target = "$_[1].$type";
	#scp from remote $_[0] to $target
	my $target=$_[0];
	my $return = '';
	if ($type eq 'pdf') {
		#system("pstotext $target $tempfile");
		exec("/usr/local/bin/pdftotext $target $tempfile");
		open(INFILE, "$tempfile");
		@text=<INFILE>;
		close(INFILE);
		$return=join("",@text);
		$return='';
	}
	elsif ($type eq 'html') {
		$return=`html2text $target`;
	}
	elsif ($type eq 'xls') {
		$return=`xls2csv $target`;
	}
	elsif ($type eq 'doc') {
		$return=`catdoc $target`;
	}
	elsif ($type eq 'txt') {
		open(INFILE, "$target`");
		@text=<INFILE>;
		close(INFILE);
		$return=join("",@text);
	}
	else {
		$return = '';
	}
	#$content=~s/[^!-~]/ /g;
	$return=~s/[^$list]/ /g;  #replace all control characters with a space.
	$return=~s/\'/\'\'/g;  #escape all single quotes since they'll mess up the SQL.
	$return=~s/\s+/ /g;  #replace all instance of multiple spaces with single spaces.
	@contents=();
	$flag=0;
	$m=0;
	$nextstring=$return;
	$lastspace=0;
	while (!$flag) {
		$lastspace = rindex($nextstring,' ',2000);  #this returns the last instance of a space before 2000 chars
		$contents[$m]=substr($nextstring,0,$lastspace);  #grabs the string until the last instance of a space before 2000 chars
		$nextstring=substr($nextstring,$lastspace+1);  #grabs the string from the last space to the end.
		if (length($nextstring)<2000) {
			$contents[$m+1]=$nextstring;
			$flag=1;
		}
		$m++;
	}

log_print("~~".$return."\n");
}

#________________________________________________________________________________________________________#

sub stamp_to_date {
	my @temp_time = localtime($_[0]);
	$hours = $temp_time[2];
	$m="AM";
	if ($hours>12) {
		$m="PM";
		$hours=$hours-12;
	}
	elsif ($hours==0) {
		$hours=12;
	}
	$minutes = $temp_time[1];
	if ($minutes<10) {
		$minutes="0$minutes";
	}
	$tempmonth = $monthlist{$temp_time[4]};
	$tempday = $temp_time[3];
	$tempyear = $temp_time[5]+1900;
	$export="$hours:$minutes $m, $tempmonth $tempday, $tempyear PST";
	return $export;
}

#________________________________________________________________________________________________________#

0;