#!/usr/bin/perl
# user_man

#ALTER TABLE support_replies ADD categoryid int

########################################################################
# COPYRIGHT NOTICE:
#
# Copyright 2007 FocalMedia.Net All Rights Reserved.
#
# Selling the code for this program without prior written consent 
# from FocalMedia.Net is expressly forbidden. You may not 
# redistribute this program in any shape or form.
# 
# This program is distributed "as is" and without warranty of any
# kind, either express or implied. In no event shall the liability 
# of FocalMedia.Net for any damages, losses and/or causes of action 
# exceed the total amount paid by the user for this software.
#
########################################################################

#use FindBin;
#use lib $FindBin::Bin;
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use DBI;
use mysdesk; 
use fmspm;

### WINDOWS USERS EDIT BELOW ##############################################
$cfile = "config.cgi";
#$cfile = "e:/full/server/path/to/config.cfg";
###########################################################################

&get_setup;

fmspm::check_spamb("search.cgi", "header", $data_dir);

$q = CGI->new;

$default_permissions = 0777;  ### DEFAULT PERMISSIONS THAT IS USED FOR DATA FILES IN DATA DIRECTORY

##################################################################################

print "Content-type: text/html\n\n";
#print "Admin Url check = $admin_url";
#exit;

if ($q->param('fct') eq "") {&start;}
if ($q->param('fct') eq "advanced") {&advanced;}

##################################################################################

sub advanced
{


if ($mysql_hostname eq ""){$dsn = "DBI:mysql:$db_name";}else{$dsn = "DBI:mysql:$db_name:$mysql_hostname:$mysql_port";}
$dbh = DBI->connect($dsn, $db_username, $db_password);
if ( !defined $dbh ) {die "Cannot connect to MySQL server: $DBI::errstr\n"; } 


$sql = "SELECT * FROM support_kbcats ORDER BY l1,l2";
$sth = $dbh->prepare($sql);
$sth->execute;
$serror = ""; $serror = $sth->errstr; if ($serror ne "") {die "SQL Syntax Error: $serror - From: $sql";} 

$rc = 0;
while ( @row = $sth->fetchrow() )
		{
		$cname = "C" . $rc;
		
		if ($row[3] ne "") { $row[3] = "/" . $row[3]; }
		
		$kb_tboxes = $kb_tboxes . qq[<input type="checkbox" name="$cname" value="$row[0]"> $row[2]$row[3]<BR>];
		
		$rc++;
		}
 
$sth->finish;
$dbh->disconnect;


$ct_main = mysdesk::get_file_contents("$data_dir/kb_asearch.html"); 

$ct_main =~ s/%%categories%%/$kb_tboxes/gi; 
$ct_main =~ s/!!search_script!!/$script_url\/search\.cgi/gi; 
$ct_main =~ s/!!rc!!/$rc/gi;

$ct_main  = mysdesk::insert_template_includes($ct_main);  
print $ct_main;

}





sub start
{

$gsettings = new_gsettings mysdesk;

$rc = $q->param('rc');
$matchtype = $q->param('matchtype');

### BUILD SQL QUERY FOR CATEGORIES & RECORD CATEGORIES SELECTED

	for ($ms = 0; $ms < $rc; $ms++) 
	{
	$cval = "C" . $ms;
	$the_kcid = $q->param($cval);
	
	$onpass = $onpass . "$cval=$the_kcid&";
	
	if ($the_kcid ne "")
		{
		$catsel = $catsel . " (A.kcid = '$the_kcid') OR ";
		}
	}
	
if ($catsel eq "") { $rc = ""; } 


$onpass = substr($onpass, 0, length($onpass) - 1);

$catsel = substr($catsel, 0, length($catsel) - 3);

$keywords0 = $q->param('keywords0'); $keywords0 =~ s/'/\\'/g; 
$keywords1 = $q->param('keywords1'); $keywords1 =~ s/'/\\'/g; 
$keywords2 = $q->param('keywords2'); $keywords2 =~ s/'/\\'/g; 
$keywords3 = $q->param('keywords3'); $keywords3 =~ s/'/\\'/g; 
$akeywords = $akeywords . $keywords0 . " " . $keywords1 . " " . $keywords2 . " " . $keywords3;

if (length($akeywords) > 50) { $akeywords = ""; }

$okeywords = "keywords0=$keywords0&keywords1=$keywords1&keywords2=$keywords2&keywords3=$keywords3";

### RUN SEARCH QUERY

if ($mysql_hostname eq ""){$dsn = "DBI:mysql:$db_name";}else{$dsn = "DBI:mysql:$db_name:$mysql_hostname:$mysql_port";}
$dbh = DBI->connect($dsn, $db_username, $db_password);
if ( !defined $dbh ) {die "Cannot connect to MySQL server: $DBI::errstr\n"; } 


if ($rc ne "") { $cat_s_sql = "AND ($catsel)"; } else { $cat_s_sql = ""; }


if (($q->param('matchtype') eq "relevance") or ($q->param('matchtype') eq "")) ### CONSTRUCT SQL SEARCH QUERY
	{
	
	$relform = "true";
	
	if (($q->param('cats') ne "") and ($q->param('cats') ne "all"))
		{
		$csq = $q->param('cats');
		$cat_s_sql = "AND ((B.kcid = '$csq') OR (B.kccid = '$csq'))";
		}
	
	
	$akeywords = mysdesk::remove_leading_spacing($akeywords);
	
	$sql = "SELECT A.kid,A.kcid,A.question,B.l1,B.l2, B.kcid,
															MATCH (A.question) AGAINST ('$akeywords') AS SCORE,
															A.title1, A.url1, A.title2, A.url2, A.title3, A.url3, A.title4, A.url4, A.title5, A.url5, A.title6, A.url6, A.title7, A.url7, A.title8, A.url8, A.title9, A.url9, A.title10, A.url10, A.comnr 
															FROM support_kb AS A,
																  support_kbcats AS B
															WHERE (MATCH (A.question) AGAINST ('$akeywords'))
															AND (A.kcid = B.kcid)
															$cat_s_sql
															ORDER BY score
																  ";
		
		#print "--> $sql <BR>";
		
		@allk = split (/ /, $akeywords);
		$c1 = 0;
			foreach $kitem (@allk)
					{
					if ($kitem ne "")
						{
						$unkeywords[$c1] = $kitem;
						$c1++;
						}
					}
		
	}
	elsif ($q->param('matchtype') eq "phrase") ### PHRASE MATCHES
	{

		if ($keywords0 ne ""){ $sql_where = $sql_where . " (question REGEXP '\[\[:<:\]\]$keywords0\[\[:>:\]\]') AND"; }
		if ($keywords1 ne ""){ $sql_where = $sql_where . " (question REGEXP '\[\[:<:\]\]$keywords1\[\[:>:\]\]') AND"; }
		if ($keywords2 ne ""){ $sql_where = $sql_where . " (question REGEXP '\[\[:<:\]\]$keywords2\[\[:>:\]\]') AND"; }
		if ($keywords3 ne ""){ $sql_where = $sql_where . " (question REGEXP '\[\[:<:\]\]$keywords3\[\[:>:\]\]') AND"; }
		
		$sql_where = substr($sql_where, 0, length($sql_where) - 3);

		$sql = "SELECT A.kid,A.kcid,A.question,B.l1,B.l2, B.kcid, A.title1, A.url1, A.title2, A.url2, A.title3, A.url3, A.title4, A.url4, A.title5, A.url5, A.title6, A.url6, A.title7, A.url7, A.title8, A.url8, A.title9, A.url9, A.title10, A.url10, A.comnr  
														FROM support_kb AS A, 
															  support_kbcats AS B 
														WHERE ($sql_where) AND (A.kcid = B.kcid) 
														$cat_s_sql";
		
			$c2 = 0;
			if ($keywords0 ne ""){ $unkeywords[$c2] = "$keywords0"; $c2++; }
			if ($keywords1 ne ""){ $unkeywords[$c2] = "$keywords1"; $c2++; }
			if ($keywords2 ne ""){ $unkeywords[$c2] = "$keywords2"; $c2++; }
			if ($keywords3 ne ""){ $unkeywords[$c2] = "$keywords3"; $c2++; }

			
		}
		else
		{
		
		$cc = 0;
		
			@allk = split (/ /, $akeywords);
			
				foreach $kitem (@allk)
					{
					#if (length($kitem) > 1)
					if ($kitem ne "")
						{
						$sql_awhere = $sql_awhere . " (question REGEXP '\[\[:<:\]\]$kitem\[\[:>:\]\]') AND"; 
						$unkeywords[$cc] = $kitem;
						$cc++;
						}
					 
					}
				
			$sql_awhere = substr($sql_awhere, 0, length($sql_awhere) - 3);
		
			$sql = "SELECT A.kid,A.kcid,A.question,B.l1,B.l2, B.kcid, A.title1, A.url1, A.title2, A.url2, A.title3, A.url3, A.title4, A.url4, A.title5, A.url5, A.title6, A.url6, A.title7, A.url7, A.title8, A.url8, A.title9, A.url9, A.title10, A.url10, A.comnr 
														FROM support_kb AS A, 
															  support_kbcats AS B 
														WHERE ($sql_awhere) AND (A.kcid = B.kcid)
														$cat_s_sql";
		
		
		} ### END OF SEARCH TYPE
	




$sth = $dbh->prepare($sql);
$sth->execute;
$serror = ""; $serror = $sth->errstr; if ($serror ne "") {die "SQL Syntax Error: $serror - From: $sql";} 
$rows = $sth->rows();


if ($rows < 1)
	{
	$no_match = mysdesk::get_file_contents("$data_dir/no_match.html");
	$no_match = mysdesk::insert_template_includes($no_match);  
	$keywords2find = "'$keywords0 $keywords1 $keywords2 $keywords3'";
	$no_match =~ s/%%keywords%%/$keywords2find/gi;
	print $no_match;
	exit;
	}


$ct_main = mysdesk::get_file_contents("$data_dir/kb_search.html"); 
$ct_main =~ s/%%matches%%/$rows/gi;


### PREPARE PAGES


$icnt = $rows;
$nr_searchres = $gsettings->{kb_search_nr};

$modp = ($icnt % $nr_searchres);
$pages = ($icnt - $modp) / $nr_searchres;
if ($modp != 0) {$pages++;}

$st = $q->param('st');
$nd = $q->param('nd');
if ($st eq ""){$st = 0;}
if ($nd eq ""){$nd = $nr_searchres;}

$main_nd = $nd;
$main_st = $st;

$ippc = 1;


### DISPLAY RESULTS

$kb_alsosee = mysdesk::get_file_contents("$data_dir/kb_salsosee.html"); 
$kb_alsosee2 =  mysdesk::get_file_contents("$data_dir/kb_salsosee2.html"); 
 

$ct_links = mysdesk::get_file_contents("$data_dir/kb_srhstyle.html"); 

while (my @row = $sth->fetchrow_array)
			{
			
 		if (($ippc > $st) and ($ippc <= $nd))
		 {
			
			$row[2] =~ s/</&lt;/g; $row[2] =~ s/>/&gt;/g; 
			$row[2] =~ s/\n/<BR>/gi;
			$row[2] = mysdesk::decode_tags($row[2]);
			
			foreach $itm (@unkeywords)
				{
				$uncrit = "<span style=\"background-color:" . $gsettings->{kb_sel_back_color} . ";color:" . $gsettings->{kb_sel_text_color} . ";\">$itm</span>";
				$row[2] =~ s/$itm/$uncrit/gi;
				}
			
			($question, $answer) = split (/---KBANSWER---/, $row[2]);
			
			if (($row[3] ne "") and ($row[4] ne ""))
				{
				$kbc = $row[3] . "/" . $row[4];
				}
				else
				{
				$kbc = $row[3];
				}
			
			#<span style="background-color:#FEDF07;color:#000000;"> </span>
			
			$tmp_links = $ct_links;
			$tmp_links =~ s/%%nr%%/$ippc/gi;
			$tmp_links =~ s/%%question%%/$question/gi;
			$tmp_links =~ s/%%answer%%/$answer/gi;
			$tmp_links =~ s/!!moreinfo!!/$script_url\/kb\.cgi?kid=$row[0]/gi;
			
			
			if ($row[4] eq "")
				{
				$catlink = qq[<a href="$script_url/kbcat.cgi?cid=$row[5]">$row[3]</a>];
				}
				else
				{
				$catlink = qq[<a href="$script_url/kbcat.cgi?cid=$row[5]&lv=2">$row[3]/$row[4]</a>];
				}
				
				
			$tmp_links =~ s/%%category%%/$catlink/gi;
			
			## ALSO SEE

					$alsosee = "";
					if ($relform eq "true") { $pval = 7; } else {$pval = 6; }
					for ($ms = 0; $ms < 10; $ms++)
						{
						if ($row[$ms+$pval] ne "")
							{
							$tmp_kb_alsosee = $kb_alsosee2;
							$tmp_kb_alsosee =~ s/%%alinktitle%%/$row[$ms+$pval]/gi;
							$ms++;
							$tmp_kb_alsosee =~ s/!!alink!!/$row[$ms+$pval]/gi;
							$alsosee = $alsosee . $tmp_kb_alsosee;
							}
						}
						
				
				$t_kb_alsosee = $kb_alsosee;
				$t_kb_alsosee =~ s/%%alsosee_links%%/$alsosee/gi;
				
				if ($alsosee ne "")
					{
					$tmp_links =~ s/%%alsosee%%/$t_kb_alsosee/gi;
					}
					else
					{
					$tmp_links =~ s/%%alsosee%%//gi;	
					}
				
				if ($row[26] eq "") { $row[26] = 0; }
				$tmp_links =~ s/%%cnr%%/$row[26]/gi;

			$kb_links = $kb_links . $tmp_links;
			
			} ### RESULTS PER PAGE
			
			$ippc++;
			
			}
	
$sth->finish;
$dbh->disconnect; 

$ct_main =~ s/%%searchresults%%/$kb_links/gi;


#### PAGES
$keywords =~ s/ /+/g;

  for ($ms = 0; $ms < $pages; $ms++) 
	{
	$pg = $ms + 1;
	if ($nd == ($pg * $nr_searchres))
		{
		$pgstring = $pgstring . " [$pg] ";
		}
	  	else
		{
		$st_a = ($pg * $nr_searchres) - $nr_searchres;
		$nd_a = ($pg * $nr_searchres);
		$pgstring = $pgstring . "<a href=\"$script_url/search.cgi?$okeywords&st=$st_a&nd=$nd_a&total=$rows&$onpass&rc=$rc&matchtype=$matchtype\">$pg</a> ";
		}
	}
	
$ct_main =~ s/%%pages%%/$pgstring/gi;
	
	#if ($pgstring ne "") { $pgstring = "Pages: " . $pgstring; }
	#$pages = "<BR><font face=\"MS Sans Serif\" size=\"1\"> $pgstring </font><BR>";


### PREV / NEXT PAGES

$spls = $modp;
if ($spls == 0){$spls++;}

 if ($nd <= ($rows - $spls))
 	{
	$st_a = $st + $nr_searchres;
	$nd_a = $nd + $nr_searchres;
	$nextt =   "<a href=\"$script_url/search.cgi?$okeywords&st=$st_a&nd=$nd_a&total=$rows&$onpass&rc=$rc&matchtype=$matchtype\">$gsettings->{kb_next_page}</a> ";
	}


 if ($st > 0)
	 	{
		$st_a = $st - $nr_searchres;
		$nd_a = $nd - $nr_searchres;
		$prev = "<a href=\"$script_url/search.cgi?$okeywords&st=$st_a&nd=$nd_a&total=$rows&$onpass&rc=$rc&matchtype=$matchtype\">$gsettings->{kb_prev_page}</a> ";
		}

if (($prev ne "") and ($nextt ne ""))
	{
	$spcer = " | ";
	}
	else
	{
	$spcer = " ";
	}

$prevnext = $prev . $spcer . $nextt;
$ct_main =~ s/%%prevnext%%/$prevnext/gi;

########

$ct_main =~ s/!!search_script!!/$script_url\/search\.cgi/gi;
$ct_main =~ s/!!advanced!!/$script_url\/search\.cgi\?fct=advanced/gi;

$keywords2find = "'$keywords0 $keywords1 $keywords2 $keywords3'";
$ct_main =~ s/%%keywords%%/$keywords2find/gi;


$ct_main  = mysdesk::insert_template_includes($ct_main);  

print $ct_main;

}



#### GET CONFIGURATION ########################################################

sub get_setup
{

$exists = (-e "config.cgi");
if ($exists > 0)
	{
	
	open (STP, "config.cgi");
		while (defined($line=<STP>))
			{
			if ($line =~ m/#/g)
				{
				$r = pos($line);
				$line = substr($line, 0, $r - 1);
				}
				
				$line =~ s/\n//g;
	
if ($line =~ /^DB_NAME/){$db_name = &get_setup_line($line, DB_NAME);}
if ($line =~ /^DB_USERNAME/){$db_username = &get_setup_line($line, DB_USERNAME);}
if ($line =~ /^DB_PASSWORD/){$db_password = &get_setup_line($line, DB_PASSWORD);}
if ($line =~ /^MYSQL_HOSTNAME/){$mysql_hostname = &get_setup_line($line, MYSQL_HOSTNAME);}
if ($line =~ /^MYSQL_PORT/){$mysql_port = &get_setup_line($line, MYSQL_PORT);}

if ($line =~ /^SCRIPT_URL/){$script_url = &get_setup_line($line, SCRIPT_URL);}
if ($line =~ /^ADMIN_URL/){$admin_url = &get_setup_line($line, ADMIN_URL);}
if ($line =~ /^WEB_URL/){$web_url = &get_setup_line($line, WEB_URL);}
if ($line =~ /^WEB_DIR/){$web_dir = &get_setup_line($line, WEB_DIR);}
if ($line =~ /^DATA_DIR/){$data_dir = &get_setup_line($line, DATA_DIR);}

if ($line =~ /^USERNAME/){$username = &get_setup_line($line, USERNAME);}
if ($line =~ /^PASSWORD/){$password = &get_setup_line($line, PASSWORD);}

if ($line =~ /^MAIL_METHOD/){$mail_method = &get_setup_line($line, MAIL_METHOD);}
if ($line =~ /^SENDMAIL/){$sendmail = &get_setup_line($line, SENDMAIL);}
if ($line =~ /^SMTP_SERVER/){$smtp_server = &get_setup_line($line, SMTP_SERVER);}

			}
	close (STP);
	
	}
}


sub get_setup_line
{
my ($setup_line, $setup_var) = @_;
$crit = "\"";
$setup_line =~ m/$crit/g;
$r1 = pos($setup_line);
$setup_line =~ m/$crit/g;
$r2 = pos($setup_line);
$setup_line = substr($setup_line, $r1, ($r2 - $r1 - 1));
$return_val = $setup_line;
return ($return_val);
}