[Asterisk-Users] AGI - IVR - Time Clock

Wed Dec 31 12:24:21 MST 2003

I wanted to post the beginings of my latest IVR Project for an automated
Time Clock software.

The customer has over 300 Field Reps that call in everytime they arrive
on location and whey they leave that location.  This is handled by the
receptionist now and she logs in them and out of there Time Clock
Software.  Which takes up majority of her day.  The customer has
requested a automated way of handling these request.

AGI Script ------------

#!/usr/bin/perl

use Asterisk::AGI;
use DBI;

$AGI = new Asterisk::AGI;

my %input = $AGI->ReadParse();
my $callerid = $input{'callerid'};

############# Time Clock Questions ################

my $empid = $AGI->get_data('employee',-1,5); # Asks for Employee ID
                $AGI->stream_file(entered);
                $AGI->say_digits($empid);

        my $optemp = $AGI->get_data('correct',-1,1);  # Asks if what was
entered is correct otherwise ask question again

if ($optemp != 1) {
        employeeid ();
}

my $strid = $AGI->get_data('store',-1,5);  # Asks for Store ID
                $AGI->stream_file(entered);
                $AGI->say_digits($strid);

        my $optstr = $AGI->get_data('correct',-1,1);    # Asks if what
was entered is correct otherwise ask question again

if ($optstr != 1) {
        storeid ();
}

my $stat = $AGI->get_data('status',-1,1);   #Asks - Login or Logout

                if ($stat == 1) {
                        $AGI->stream_file(login);
                }else{
                        $AGI->stream_file(logout);
                }

        my $optstat = $AGI->get_data('correct',-1,1);     # Asks if what
was entered is correct otherwise ask question again

if ($optstat != 1) {
        status ();
}

############# Database Connection ###########################

my $dbh = DBI->connect("DBI:mysql:database=service;host=localhost",
                                                "username", "password",
                                                {'RaiseError' => 1});

$query = "INSERT INTO auto (Callerid, Date, Time, Empid, Strid, Status)
VALUES ('$callerid', sysdate(), sysdate(), '$empid', '$strid',
'$stat')";

$sth = $dbh->prepare($query);
$sth->execute();

$sth->finish();

$dbh->disconnect;

$AGI->stream_file(beep);

####### Sub Routines ############

sub employeeid {

        my $empid = $AGI->get_data('employee',-1,5);
                $AGI->stream_file(entered);
                $AGI->say_digits($empid);

        my $optemp = $AGI->get_data('correct',-1,1);

}

sub storeid {

        my $strid = $AGI->get_data('store',-1,5);
                $AGI->stream_file(entered);
                $AGI->say_digits($strid);

        my $optstr = $AGI->get_data('correct',-1,1);

}

sub status {

        my $stat = $AGI->get_data('status',-1,1);

                if ($stat == 1) {
                        $AGI->stream_file(login);
                }else{
                        $AGI->stream_file(logout);
                }

        my $optstat = $AGI->get_data('correct',-1,1);

------------------------------------------------------------------------
------

Page to view data that was entered........

There is 3 Tables involved...

1. Data that is entered by user
2. Employeed Name -> Employee ID
3. Store Named -> Store ID

------------------------------------------------------------------------
---------

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Service Express Time Clock</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<table width="75%" border="1">
  <tr>
    <td colspan="6"><center><b>Service Express Time
Clock</b></center></td>
  </tr>
  <tr>
    <td>Caller ID</td>
    <td>Store Name</td>
    <td>Employee Name</td>
    <td>Date</td>
    <td>Time</td>
    <td>Login / Logout</td>
  </tr>
<?
$hostName = "localhost";
$userName = "username";
$password = "password";
$dbName = "service";

// make connection to database
mysql_connect($hostName, $userName, $password) or die("Unable to connect
to host $hostName");

mysql_select_db($dbName) or die("Unable to select database $dbName");

//$query = "SELECT * from auto ORDER BY Date, Time";

$query = "select
auto.Callerid,store.name,emp.employee,auto.Date,auto.Time,auto.Status
          from auto,store,emp
          where auto.Strid=store.store_id and auto.Empid=emp.id
          ORDER BY Date, Time";

$result = mysql_query($query);

$number = mysql_numrows($result);

print "There are $number records in the Database:<p>";

for ($i=0; $i<$number; $i++) {
        $v_callid = mysql_result($result, $i, "CallerID");
        $v_store = mysql_result($result, $i, "name");
        $v_emp = mysql_result($result, $i, "employee");
        $v_date = mysql_result($result, $i, "Date");
        $v_time = mysql_result($result, $i, "Time");
        $v_stat = mysql_result($result, $i, "Status");

if ($v_stat == '1') {
                $v_stat_chgn = "Login";
        }else{
                $v_stat_chgn = "Logout";
        }

        // print "$v_callid, $v_date, $v_time, $v_empid, $v_strid,
$v_stat, $v_stat_chgn<br>";
        echo "<tr>";
                echo "<td>$v_callid</td>";
                echo "<td>$v_store</td>";
                echo "<td>$v_emp</td>";
                echo "<td>$v_date</td>";
                echo "<td>$v_time</td>";
                echo "<td>$v_stat_chgn</td>";
        echo "</tr>";
}

mysql_close();

?>
</table>
</body>
</html>

Again this is a basic script.  The next step is for the AGI script to
integrate with the Time Clock software so there is no interaction other
than the field reps when they call in to login or logout.

-gcc