#!/usr/bin/perl -w
#
#  Trouble Ticket Management & Routing IVR Application for Asterisk PBX v1.0.1
#  Copyright 2004, Jim Radford <jradford@npl.com>
#  http://www.jimradford.com/asterisk/
#
#  REQUIREMENTS:
#    Tested with PerlDesk v1.0.2 - Not sure if it will work with newever versions of perldesk
#    Asterisk::AGI Module available from: http://asterisk.gnuinter.net/
#    Obviously a Running Asterisk Server, tested with v1.0.2 as well as CVS-HEAD-12/22/04-20:45:05
#
#  SAMPLE EXTENSIONS ENTRY
#    exten => 2,1,agi,perldesk.agi,[$QUEUESUPPORT}
#
#  when it returns it will go to whatever you have configured in $AGI_OPTIONS{user_extension_context}
#  with a priority of 1 and an extension of what it finds in the perldesk database calls.ownedby, if 
#  call is not assigned it will default to $AGI_OPTIONS{default_queue}
#
#  this is more useful of an example of what can be done with AGI, as oposed to a drop-in installation
#  unless you are using perldesk for trouble ticketing.
#
#  LIVE DEMO
#  You can visit http://www.npl.com/cgi-perl/index.pl and sign up for a user account and enter a trouble ticket
#  And then call IAXTEL: 17006036024 Option 2 for Support, Option 2 for this application.
#  
#
#  TODO:
# 	Code Cleanup, better comments
#       Better Default Handling (done 1.0.1)
#	Maybe rewrite to support a more popular helpdesk application? (I'm open to suggestions)
#	performance enhancements, application should be seemless to pbx interface (done 1.0.1)

use Asterisk::AGI;
use DBI;
use strict;

###
### GLOBAL CONFIGURATION SETTINGS 
###
my %MYSQL = (
	hostname	=>	"localhost",
	username	=>	"username",
	password	=>	"password",
	database	=>	"support"
);

my %AGI_OPTIONS = (
	user_extension_context		=>	"user_extensions",
	allow_close_issue		=>	1,
	allow_open_closed_issue		=>	1,
	default_queue			=>	"support",
	default_fallthrough_context	=>	"support_menu",
	default_fallthrough_extension	=>	500,
	default_fallthrough_priority	=>	1
);



### enable debugging? send some "verbose" messages to the asterisk console if enabled. 
### to see messages you muse enable "agi debug" at the asterisk console
my $debug = 2;

###
### End of Configuration settings
###

my $db_hash_calls;
my $dbh = DBI->connect("dbi:mysql:$MYSQL{database}:$MYSQL{hostname}","$MYSQL{username}","$MYSQL{password}",{'RaiseError' => 1});
#
# If we cannot connect to the database above, we should do some sort of graceful/default action
# like send the user into a queue or direct to a voicemail
#

if(!$dbh) {
	debug("Could not connect to database");
	# send user to regular queue (default action)
	exit 1;
}

###
### Fire Phasers, we have action!
### 
my $AGI = new Asterisk::AGI;

### populate hash with parameters passed from asterisk
### ie: $input{dnis}
my %input = $AGI->ReadParse();
my $tsr		= 0;

if ($debug >= 2) {
$AGI->stream_file('pls-wait-connect-call');
  my $key;
  foreach $key (keys %input) {
    debug("$key = " . $input{$key},3);
  }
}


# get tsr from caller
my $result = &gettsr();
debug("Result from gettsr: $result");
debug("DB HASH: $db_hash_calls->{status}");

## if we got nothing back we should... default them to the support queue?
if($result == -1) {
	debug("could not find tsr or user bombed out")
	# default out to support queue
} else {
  	# should we verify here or should we already have it verified?
	debug("found tsr $result");
	# log call to perldesk database

	# Prompt: To Close this Technical Support Request Press 1 now, to be connected to the agent assigned to this request press 2 now
	# close it? or xfer to tech?
	if($AGI_OPTIONS{allow_close_issue}) {
		debug("allow_close_issue = true");
 		# is it already CLOSED? if so, skip all this crap.. or maybe allow a reopen? hmmmm
		debug("db_hash_calls->status = $db_hash_calls->{status}");
		if($db_hash_calls->{status} ne "CLOSED") {
		debug("db status is not closed");
			my $close_tsr = $AGI->get_data("jim-closeit", "10000", "1");
			if($close_tsr == 2) { 
				# user requested tsr closed
				&log_call("close tsr");
				debug("user requested to close tsr");
				&func_change_tsr("CLOSED");
				debug("user requested to close tsr");
				# closed successfully
				; We have successfully closed this TSR
				$AGI->stream_file('jim-tsr-closed');
				# TODO: Send User back to Support Menu
				exit(0);
				# tell user? then send user where?
			} else {
			# transfer caller to assigned agent
			# lookup tech
			# forward to tech if assigned, else let it default.
				if($db_hash_calls->{ownership} ne "Unowned") {
					debug("user requests to speak to tech found: $db_hash_calls->{ownership}");
					&log_call("callin");
					$AGI->stream_file('jim-connect');
					$AGI->set_context("$AGI_OPTIONS{user_extension_context}");
					$AGI->set_extension("$db_hash_calls->{ownership}");
					$AGI->set_priority("1");
					#$AGI->exec('Transfer',"$db_hash_calls->{ownership}");
					#$AGI->exec('Goto',"$db_hash_calls->{ownership},1");
					#hangup? 
					exit 0;
				}
			}
		}
	}
	# issue is closed, wanna re-open it?
	if($AGI_OPTIONS{allow_open_closed_issue} == 1) {
		debug("allow_open_closed_issue = true");
		debug("db_hash_calls->status = $db_hash_calls->{status}");
		if($db_hash_calls->{status} eq "CLOSED") {
		debug("db status is closed");
			my $reopen_tsr = $AGI->get_data("jim-reopen", "10000", "1");
			if($reopen_tsr == 1) { 
				# user requested tsr reoprened
				&log_call("reopen tsr");
				debug("user requested to reopen tsr");
				&func_change_tsr("OPEN");
				$AGI->stream_file('jim-tsr-reopened');
				# reopened successfully
				# tell user? then send user where?
				#####} else {
			# transfer caller to assigned agent
			# lookup tech
			# forward to tech if assigned, else let it default.
				debug("check for ownership to direct to tech $db_hash_calls->{ownership}");
				if($db_hash_calls->{ownership} ne "Unowned") {
					debug("user requests to speak to tech found: $db_hash_calls->{ownership}");
					$AGI->stream_file('jim-connect');
					$AGI->set_context("$AGI_OPTIONS{user_extension_context}");
					$AGI->set_extension("$db_hash_calls->{ownership}");
					$AGI->set_priority("1");
					#$AGI->exec('Transfer',"$db_hash_calls->{ownership}");
					#$AGI->exec('Goto',"$db_hash_calls->{ownership},1");
					#hangup? 
					exit 0;
				}
			}
		}
	}

}

###
### as a default we'll send the caller into the support queue
###
debug("made it to default fallthrough");
if($ARGV[0] > 0) {
	debug("$ARGV[0] queue members present!");
	# we have queue members present
	$AGI->exec('Queue',"$AGI_OPTIONS{default_queue}");
	exit(0);
} else {
	debug("no queue members");
	# no queue members, send them to some default
	$AGI->set_context("$AGI_OPTIONS{default_fallthrough_context}");
        $AGI->set_extension("$AGI_OPTIONS{default_fallthrough_extension}");
        $AGI->set_priority("$AGI_OPTIONS{default_fallthrough_priority}");
	exit(0);
}
exit(0);

#
# prompt user for tsr ID
# returns: database ID of call
#
sub gettsr {
my $try	= 1;
while($try < 3) {
	debug("ask for tsr");
	# PROMPT: Please enter your request ID number followed by the pound sign.
	$tsr = $AGI->get_data("jim-ask-requestid", "10000", "5");
	$AGI->stream_file('auth-thankyou');
	my $tsrid = &check_tsr($tsr);
	debug("tsrid $tsr returned from check_tsr is \"$tsrid\" try# $try");
	if($tsrid eq -1) {
		$AGI->stream_file('jim-unable-find-requestid');
		$try++;
	} else {
		# confirm here
		#if(&confirm_prompt($tsrid) eq 1) { return($tsrid); }
		if((&confirm_prompt($db_hash_calls->{id})) eq 1) { return(1); }
		else { return(-1); }
	}
}	
debug("out of tries");
return(-1);
}

###
### confirm_prompt - prompts user for yes or no answer, returns 1 on yes
###                  parameters: tsr ID
###
sub confirm_prompt {
	$AGI->stream_file('you-entered');
	$AGI->exec('SayDigits',$_[0]);
	my $result = $AGI->get_data("jim-correct", "10000", "1");
	if($result eq 1) {
		  debug("caller pressed 1 to confirm $_[0]");
		  return(1);
		} else {
	  return(0);
        }
}

###
### log_call - log call info to support database
###
sub log_call {
	debug("log_call sub called");
	my $query	= "INSERT INTO notes set owner=?,visible=?,action=?,call=?,author=?,time=NOW(),comment=?";
	my $sth		= $dbh->prepare($query);
	$sth->execute("0","1","NULL",$db_hash_calls->{id},$db_hash_calls->{username} . " via Tel","Test Record from log_call")
		|| debug_die("Could not exec sth INSERT $!");
		$sth->finish;
		return(1);
}

###
### func_close_tsr - close the tsr ticket
###             parameters: tsr id
###
sub func_change_tsr {
	my $query	= "UPDATE calls set status=?,closedby=? WHERE id=?";
	my $sth		= $dbh->prepare($query);
	$sth->execute("$_[0]",$db_hash_calls->{username},$db_hash_calls->{id})
		|| debug_die("Could not Execute close_tsr sth! $!");
	$sth->finish;
	return(1);
}

###
### check_tsr - check for existance of trouble ticket
###		paramaters: tsr id from user
###
sub check_tsr {
	my $query	= "SELECT * FROM calls WHERE id=? LIMIT 1";
        my $sth		= $dbh->prepare($query);
	$sth->execute("$_[0]")
		|| debug_die("Couldn't exec sth2!");
	# need to be graceful here also
	my $res = $sth->fetchrow_hashref;
	$db_hash_calls = $res;
	$sth->finish;
	if($res) { return($res->{priority}); } 
	else { return(-1); }
}

sub debug {
  my $string = shift;
  my $level = shift || 3;
  if ($debug) {
    $AGI->verbose("DEBUG: " . $string, $level);
  }
return(0);
} 
sub debug_die {
  my $string = shift;
  my $level = shift || 3;
  if ($debug) {
    $AGI->verbose("DEBUG DIE: " . $string, $level);
  }
exit(0);
}