#!/usr/bin/perl -Tw
# Use these commands to create the appropriate tables in MySQL
# If flags is 1 then this record is not included in the output voicemail file
#
#CREATE TABLE voicemail (
#  context varchar(20) NOT NULL default 'default',
#  mailbox varchar(20) NOT NULL default '',
#  password varchar(20) NOT NULL default '',
#  name varchar(50) NOT NULL default '',
#  email varchar(50) default '',
#  pager_email varchar(50) default '',
#  options varchar(50) default '',
#  descr text,
#  flags int(1) NOT NULL default '0',
#  PRIMARY KEY  (context,mailbox)
#);
#
#CREATE TABLE voicemailglobals (
#  variable char(20) NOT NULL default '',
#  value char(50) NOT NULL default '',
#  PRIMARY KEY  (variable,value)
#);

use DBI;
################### BEGIN OF CONFIGURATION ####################

# the name of the extensions table
$table_name = "voicemail";
# the name of the globals table
$global_table_name = "voicemailglobals";
# the path to the voicemail.conf file
# WARNING: this file will be substituted by the output of this program
$voicemail_conf = "/etc/asterisk/voicemail.conf";
# the name of the box the MySQL database is running on
$hostname = "localhost";
# the name of the database our tables are kept
$database = "asterisk";
# username to connect to the database
$username = "";
# password to connect to the database
$password = "";

################### END OF CONFIGURATION #######################

open EXTEN, ">$voicemail_conf" || die "Cannot create/overwrite extensions file: $voicemail_conf\n";

$dbh = DBI->connect("dbi:mysql:dbname=$database;host=$hostname", "$username", "$password");
$statement = "SELECT * from $global_table_name order by variable";
my $result = $dbh->selectall_arrayref($statement);
unless ($result) {
  # check for errors after every single database call
  print "dbh->selectall_arrayref($statement) failed!\n";
  print "DBI::err=[$DBI::err]\n";
  print "DBI::errstr=[$DBI::errstr]\n";
  exit;
}
my @resultSet = @{$result};
if ( $#resultSet > -1 ) {
        print EXTEN "[globals]\n";
        foreach $row (@{ $result }) {
                my @result = @{ $row };
                print EXTEN "$result[0] = $result[1]\n";
        }
        print EXTEN "\n";
}

$statement = "SELECT context from $table_name group by context";

$result = $dbh->selectall_arrayref($statement);
unless ($result) {
  # check for errors after every single database call
  print "dbh->selectall_arrayref($statement) failed!\n";
  print "DBI::err=[$DBI::err]\n";
  print "DBI::errstr=[$DBI::errstr]\n";
}

@resultSet = @{$result};
if ( $#resultSet == -1 ) {
  print "No voicemail defined in $table_name\n";
  exit;
}

foreach my $row ( @{ $result } ) {
        my $context = @{ $row }[0];
        print EXTEN "[$context]\n";
        $statement = "SELECT * from $table_name where context='$context' order by mailbox";
        my $result = $dbh->selectall_arrayref($statement);
        unless ($result) {
                # check for errors after every single database call
                print "dbh->selectall_arrayref($statement) failed!\n";
                print "DBI::err=[$DBI::err]\n";
                print "DBI::errstr=[$DBI::errstr]\n";
                exit;
        }

        my @resSet = @{$result};
        if ( $#resSet == -1 ) {
                print "no results\n";
                exit;
        }
        foreach my $row ( @{ $result } ) {
                my @result = @{ $row };
                if ($result[8] == 0) {
                        print EXTEN "$result[1] => $result[2],$result[3],$result[4],$result[5],$result[6]";
                        print EXTEN "\t";
                        print EXTEN "\t; $result[7]" if defined $result[7];
                        print EXTEN "\n";
                }
        }
        print EXTEN "\n";
}

exit 0;

