#!/usr/bin/perl
###################################################################################
#                                                                                 #
#                   PerlDesk - Customer Help Desk Software                        #
#                                                                                 #
###################################################################################
#                                                                                 #
#     Author: John Bennett	                                                  #
#      Email: j.bennett@perldesk.com                                              #
#        Web: http://www.perldesk.com                                             #
#   Filename: kb.cgi                                                              #
#    Details: The main knowledge base file                                        #
#    Release: 1.5.5                                                               #
#                                                                                 #
###################################################################################
# Please direct bug reports,suggestions or feedback to the perldesk forums.       #
# www.perldesk.com/board                                                          #
#                                                                                 #
# PerlDesk is free for both commercial and non-commercial use providing that the  #
# copyright headers remain intact and the links remain on the html pages.         #
# Re-distribution of this script without prior consent is strictly prohibited.    # 
#                                                                                 #
###################################################################################
# Please see the README/INSTALL files if you have any problems with this software #                                                            
################################################################################### 
 use CGI qw(:standard);                                 
 use DBI();                                             
 use Digest::MD5 ;   

 require "/var/www/agenda/cgi-bin/desk/include/pd.cgi";
 require "/var/www/agenda/cgi-bin/desk/include/conf.pl";
 
  get_time();

 $dbh    =  DBI->connect("DBI:mysql:$dbname:$dbhost","$dbuser","$dbpass") or die_nice("Could not connect to the help desk database");
 %global =  get_vars();  
 $q      =  CGI->new();

eval {                                                  
    require "include/subs.cgi";                                    
};                                                      
if ($@) {                                               
    print "Content-type: text/plain\n\n";               
    print "Error including file(s): $@\n";                
    print "Please ensure that the file(s) exist and permissions are correct";
    exit;
}                                                       

   if (defined $q->param('lang')) { $language = $q->param('lang'); } 
   else { $language = $global{'language'}; }

eval 
 {
    require "include/lang/$language.inc"; 
    require "include/parse/user.cgi";
 };

if ($@) {                                               
    print "Content-type: text/plain\n\n";               
    print "PerlDesk Error: $@\n";                
    exit;
}   

  print "Content-type: text/html\n\n";

  if ((!$ENV{'QUERY_STRING'}) || ($ENV{'QUERY_STRING'} =~ /^lang/)) {

    $num       =  1;
	$statement = 'SELECT * FROM kb_entries ORDER BY id DESC LIMIT 5'; 
	$sth       =  $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped";
 	$sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped";
	while(my $ref = $sth->fetchrow_hashref()) 
     {	
		if ($ref->{'views'} > 50) { $pop = "<img src=\"$global{'imgbase'}/hot.gif\">"; } else { $pop = ' '; }
		if ($num eq "2")
           {
      			$bgcol   =  '#FFFFFF';
     			$num = 0;
     	   }
              else  
                   {
			           $bgcol   =   '#F0F0F0';
		           }

		$kb .= '<tr><td width="5%" bgcolor = "' . "$bgcol" . '"><div align="center"><img src="' . "$global{'imgbase'}" . '/article.gif"></div></td>
                   <td width="66%" bgcolor = "' . "$bgcol" . '"><font size="1" face="Verdana, Arial, Helvetica, sans-serif">' . "<a href=kb.cgi?view=$ref->{'id'}&lang=$language>$ref->{'subject'}</a>" . '</font></td>
                   <td width="29%" bgcolor = "' . "$bgcol" . '"><font size="1" face="Verdana, Arial, Helvetica, sans-serif">' . "$ref->{'category'}" . '</font></td></tr>';
     	$num++;
	}

	$statement = 'SELECT category FROM kb_cat'; 
	$sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped";
 	$sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped";
		while(my $ref = $sth->fetchrow_hashref())  
         {	
			$option .= "<option value=\"$ref->{'category'}\">$ref->{'category'}</option>";
	    	push @categories, $ref->{'category'};
	     }
	$sth->finish;

	$option .= "<option value=\"all\">$LANG{'searchall'}</option>";
    $line   .= '<table width="350" border="0" cellspacing="0" cellpadding="3" align="center">';

    @_ = @categories;

    while (my ($one,$two) = (shift @_, shift @_))  
      {     
        last unless defined $one;
        $line .= "<tr><td width=\"50%\"><font size=2 color=\"#000000\">\&#149\; <a href=\"kb.cgi?cat&query=$one&lang=$language\">$one</a></font></td><td width=\"50%\"><font size=2 color=\"#000000\">"; 

     	if (defined $two) 
           {
            	$line .="\&#149\; <a href=\"kb.cgi?cat&query=$two&lang=$language\">$two</a></font>";
           }

    	$line .= "</td></tr>";
        last unless defined $two;  
      }

   $line                  .=  '</table>';
   $template{'categories'} =   $line;
   $template{'category'}   =   $option;
   $template{'list'}       =   $kb;
   
   print parse("$global{'data'}/include/tpl/kb");
   exit;
 }


if ($ENV{'QUERY_STRING'} =~ /^view/) 
  {

    $id        = $q->param('view');
	$statement = 'SELECT * FROM kb_entries WHERE id = ' . "$id"; 
	$sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped";
 	$sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped";
		while(my $ref = $sth->fetchrow_hashref()) 
         {	
			$read         =  $ref->{'views'};
			$author       =  $ref->{'author'};
			$subject      =  $ref->{'subject'};
			$description  =  $ref->{'description'};
			$id           =  $ref->{'id'};
  	     }

	$ead       = $read;
	$ead++;
	$sth->finish;
	$statement = 'UPDATE kb_entries SET views  = "' . "$ead" . '" WHERE id = "' . "$id" . '";'; 
	$sth       =  $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped";
	$sth->execute();# or die print "Couldn't execute statement: $DBI::errstr; stopped";


   $template{'subject'} = $subject;
   $template{'author'}  = $author;
   $template{'article'} = $description;
   $template{'read'}    = $read;
   $template{'id'}      = $id;
   
   print parse("include/tpl/kb_article");
   $dbh->disconnect;
	exit;
}


if ($ENV{'QUERY_STRING'} =~ /^search/)	{
	
	my $select    =  $q->param('select');
	   $query     =  $q->param('query');
	   $field     =  $query;
	   $category  =  $q->param('select');

    $query = $q->param('query');
    $feld  = $q->param('field');
	$query =~  s/_/ /g;
    $feld  =   $field if !$feld;

	if ($category eq "all") 
         {
              $sth = $dbh->prepare( 'SELECT COUNT(*) FROM kb_entries WHERE description LIKE "%' . "$query" . '%" OR subject LIKE "%' . "$query" . '%"' ) or die DBI->errstr;
   	     }
      else {  $sth = $dbh->prepare( 'SELECT COUNT(*) FROM kb_entries WHERE category = "' . "$category" . '" AND description LIKE "%' . "$query" . '%" OR subject LIKE "%' . "$query" . '%"' ) or die DBI->errstr;  } 

	$sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped";
        $count = $sth->fetchrow_array();
   	$sth->finish;

	if ($ENV{'QUERY_STRING'} !~ /show/) { $pae = 0; }
	if (($count > 30) || ($ENV{'QUERY_STRING'} =~ /show/)) { $showbar = "1"; }
		if ($showbar) {
		if ($ENV{'QUERY_STRING'} =~ /show/) {
				my $string = "$ENV{'QUERY_STRING'}";
 			 	 @values = split(/\&/,$ENV{'QUERY_STRING'});
   		      	foreach $i (@values) { 
                if ($i =~/show/) {
                    ($action, $pae) = split(/=/,$i);
                }   }

	if (!$pae)	{		$prevlink = '&laquo; prev';   } 
    else   {
			$link     =  $query;
			$link     =~ s/ /_/g;
			$prevpage =  $pae-1;
			$prevlink =  "<a href=\"kb.cgi?search&query=$link&show=$prevpage&field=$feld\">" . '&laquo; prev' . "</a>";
	} }	
    else	{	$prevlink = '&laquo; prev';   }
	
    	$res = $pae+1;
		$total = (30*$res);
		if ($count > $total){
	     	        $nextpage = $pae+1;
	    	        $link = $query;
	     	        $link =~ s/\s/_/g;
	    	        $nextlink = "<a href=\"kb.cgi?search&query=$link&show=$nextpage&field=$feld\">" . 'next &raquo;' . "</a>";
		} else {
	    		$nextlink = 'next &raquo;';
		}
				$bar = '<table width="100%" border="0" cellspacing="0" cellpadding="3" align="center"><tr bgcolor="#E8E8E8"><td> 
      			<div align="left"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">' . "$prevlink" . '</font></div></td><td><div align="right"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">' . "$nextlink" . '</font></div></td></tr></table>';
		}

	$showp = $pae*30;
    $bar   = '' if !$bar;

	 $statement = 'SELECT * FROM kb_entries WHERE description LIKE "%' . "$query" . '%" OR subject LIKE "%' . "$query" . '%" ORDER BY id LIMIT ' . "$showp" . ',30'; 
	 $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped";
 		$sth->execute() or die print "Couldn't execute statement: $DBI::errstr; stopped";
		$number=0;
	$num = 1;
    $total = 0;
	while(my $ref = $sth->fetchrow_hashref()) {	

	if ($category ne "all")
             {
                next if $ref->{'category'} ne $category;
             }

    if ($num eq "2")  
            {
     			$bgcol   =  '#FFFFFF';
     			$num     =  0;
     		} 
             else 
               {
                 	$bgcol   =   '#F0F0F0';  
               }

	$num++;
    $total++;

		if ($ref->{'views'} > 50) { $pop = "<img src=\"$global{'imgbase'}/hot.gif\">"; } else { $pop = ' '; }

				$article .= '<tr><td width="4%"  bgcolor = "' . "$bgcol" . '"> 
    			<div align="center"><img src="' . "$global{'imgbase'}" . '/article.gif"></div></td>
    			<td width="60%" bgcolor = "' . "$bgcol" . '"><font size="1" face="Verdana, Arial, Helvetica, sans-serif">' . "<a href=kb.cgi?view=$ref->{'id'}&lang=$language>$ref->{'subject'}</a>" . '</font></td>
    			<td width="30%" bgcolor = "' . "$bgcol" . '"><font size="1" face="Verdana, Arial, Helvetica, sans-serif">' . "$ref->{'category'}" . '</font></td>
    			<td width="6%" bgcolor = "' . "$bgcol" . '"><font size="1" face="Verdana, Arial, Helvetica, sans-serif">' . "$pop" . '</font></td>
  				</tr>';
		}
		$sth->finish;

   $template{'results'} =  $article;
   $template{'bar'}     =  $bar;
   $template{'num'}     =  $total;
   
   my $output = parse("$global{'data'}/include/tpl/kb_results");

   print $output;

   $dbh->disconnect;
   exit;	
}

if ($ENV{'QUERY_STRING'} =~ /^cat/)	{


    $query  =  $q->param('query');
	$query  =~  s/\%20/ /g;

	if (!$feld) {
		$feld = $field;	
	} 
	$sth = $dbh->prepare( 'SELECT COUNT(*) FROM kb_entries WHERE category = ?' ) or die DBI->errstr;
	$sth->execute($query) or die print "Couldn't execute statement: $DBI::errstr; stopped";
	 $count = $sth->fetchrow_array();
	$sth->finish;

	if ($ENV{'QUERY_STRING'} !~ /show/) { $pae = 0; }
	if (($count > 30) || ($ENV{'QUERY_STRING'} =~ /show/)) { $showbar = "1"; }
		if ($showbar) 
		{
		if ($ENV{'QUERY_STRING'} =~ /show/) 
			{
				my $string = "$ENV{'QUERY_STRING'}";
 			 	 @values = split(/\&/,$ENV{'QUERY_STRING'});
   		      	foreach $i (@values) { 
                if ($i =~/show/) 
				{
                    ($action, $pae) = split(/=/,$i);
                }   }
	if ($pae eq "0") 		{
		$prevlink = '&laquo; prev';
		} else 		{
			$link = $query;
			$link =~ s/ /_/g;
			$prevpage = $pae-1;
			$prevlink = "<a href=\"kb.cgi?cat&query=$link&show=$prevpage\">" . '&laquo; prev' . "</a>";
		} }	 else 		{
		$prevlink = '&laquo; prev';
		}
		$res = $pae+1;
		$total = (30*$res);
		if ($count > $total){
			$nextpage = $pae+1;
			$link = $query;
			$link =~ s/\s/_/g;
			$nextlink = "<a href=\"kb.cgi?cat&query=$link&show=$nextpage\">" . 'next &raquo;' . "</a>";
		} else		 {
			$nextlink = 'next &raquo;';
		}
				$bar = '<table width="100%" border="0" cellspacing="0" cellpadding="3" align="center"><tr bgcolor="#E8E8E8"><td> 
      			<div align="left"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">' . "$prevlink" . '</font></div></td><td><div align="right"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">' . "$nextlink" . '</font></div></td></tr></table>';
		}
	$showp = $pae*30;

	 $statement = 'SELECT * FROM kb_entries WHERE category = ? ORDER BY id LIMIT ' . "$showp" . ',30'; 
	 $sth = $dbh->prepare($statement) or die print "Couldn't prepare statement: $DBI::errstr; stopped";
 		$sth->execute($query) or die print "Couldn't execute statement: $DBI::errstr; stopped";
		$number=0;
	$num = 1;
	while(my $ref = $sth->fetchrow_hashref()) 
		{	
     		if ($num eq "2") 
                {
         			$bgcol   =  '#FFFFFF';
        			$num     =  0;
        		} 
                  else {
			              $bgcol   =   '#F0F0F0';
		               }
		$num++;

		if ($ref->{'views'} > 50) { $pop = "<img src=\"$global{'imgbase'}/hot.gif\">"; } else { $pop = ' '; }

				$article .= '<tr><td width="4%"  bgcolor = "' . "$bgcol" . '"> 
    			<div align="center"><img src="' . "$global{'imgbase'}" . '/article.gif"></div></td>
    			<td width="60%" bgcolor = "' . "$bgcol" . '"><font size="1" face="Verdana, Arial, Helvetica, sans-serif">' . "<a href=kb.cgi?view=$ref->{'id'}&lang=$language>$ref->{'subject'}</a>" . '</font></td>
    			<td width="30%" bgcolor = "' . "$bgcol" . '"><font size="1" face="Verdana, Arial, Helvetica, sans-serif">' . "$ref->{'category'}" . '</font></td>
    			<td width="6%" bgcolor = "' . "$bgcol" . '"><font size="1" face="Verdana, Arial, Helvetica, sans-serif">' . "$pop" . '</font></td>
  				</tr>';
	
		}
		$sth->finish;

        if (!$bar) { $bar = ''; }

   $template{'bar'}     =  $bar;
   $template{'results'} =  $article;
   $template{'num'}     =  $count;
   
   my $output = parse("$global{'data'}/include/tpl/kb_results");

   print $output;

	$dbh->disconnect;
	exit;	
}