Utilities to batch create a large number of extensions?

Ok... i wrote a quick Perl script that does almost everything... the only thing I couldn't figure out is how to get CallWaiting to enable. So after the script is done, you have to go into each extension, enable CallWaiting and submit. Still, it's better than doing everything by hand.

You need to have some understanding of Perl to use this. But it's pretty easy. If you know PHP, you can figure it out.

If you want a file, just message me and I'll send it to you.

Tim


#!/usr/bin/perl -U
# Written by Tim Schreyack, December 2007

# Requirements:
# DBI and DBD::mysql perl modules
# You can install them in perl by running: perl -MCPAN -e 'install DBI' and perl -MCPAN -e 'install DBD::mysql' from the command line
# You may have to manually install DBD::mysql. Download from CPAN: http://search.cpan.org/~capttofu/DBD-mysql-4.005/lib/DBD/mysql.pm
#
# This script is designed to be run as CGI.
# Put it in /var/html/www/cgi-bin and make sure it has read/execute permissions for everyone (ie. chmod 755 batchaddextension.pl).
#
# The CSV file must contain the following comma separated values in each row, in this exact order:
# MAC Address, User Name , Extension, DID, Password, Timezone (Eastern, Central, Mountain, Pacific)
#
# You must change the database username and password on line 48 so the script can login to your database.

$InputPath = "/var/ftp/"; #Location of Polycom Config files
$Voicemail = "/etc/asterisk/voicemail.conf"; #Name of voicemail.conf file including full path

use CGI;
use DBI;

#Get the CSV file from the browser
my $cgi = new CGI;
my $file = $cgi->param('file');

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

#Read through the file and process each row
while (<$file>)
{
#Do some error checking before processing that row
($macadd,$name,$exten,$did,$password,$timezone) = split ',', $_;
print "Creating: $macadd : $name : $exten";
if (length($macadd) != 12) {
print " MAC Address not 12 digits long. Skipping.
";
}
elsif (length($name) < 1) {
print " Name is blank. Skipping.
";
}
elsif (length($exten) < 4) {
print " Extension is not at least 4 digits. Skipping.
";
}
elsif (length($did) < 10) {
print " DID is not at least 10 digits. Skipping.
";
}
else {
# If we passed the error checks continue, otherwise skip to the next one
#
# Create FreePBX Extension
$drh = DBI->install_driver('mysql');

$db = $drh->connect(asterisk, datbaseuser, password) #Connect to the database. Replace databaseuser and password with whatever is appropriate for you.
or die "Can't connect to database: asterisk\nError: " . DBI->errstr;

$ucname = uc($name);
$vmailbox = join ('@', $exten, 'default');
$outid = "\"" . $ucname . "\"" . "<" . $did . ">";

$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','account','$exten','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','accountcode','','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','allow','','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','callerid','device <$exten>','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','callgroup','','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','canreinvite','yes','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','context','from-internal','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','disallow','','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','dtmfmode','rcf2833','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','host','dynamic','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','mailbox','$vmailbox','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','nat','yes','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','pickupgroup','','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','port','5060','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','qualify','yes','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','record_in','Adhoc','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','record_out','Adhoc','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','secret','$FORM{formPassword}','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','type','friend','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','dial','SIP/$exten','0')");
$db->do("DELETE FROM users WHERE extension = '$exten'"); #Just in case the extension already was in the table
$db->do("INSERT INTO users (extension,password,name,voicemail,ringtimer,noanswer,recording,outboundcid,directdid,didalert,sipname,faxexten,faxemail,answer,wait,privacyman) VALUES ('$exten','','$ucname','default','0','','out=Adhoc|in=Adhoc','$outid','','','$did','disabled','','0','0','0')");
$db->do("DELETE FROM devices WHERE id = '$exten'"); #Just in case the extension already was in the table
$db->do("INSERT INTO devices (id,tech,dial,devicetype,user,description,emergency_cid) VALUES ('$exten','sip','SIP/$exten','fixed','$exten','$ucname','')");
$db->do("UPDATE admin SET value = 'true' WHERE variable = 'need_reload'"); #Cause the Apply Changes button to appear in FreePBX
$db->disconnect;

# Now create voicemail box
$vmailnotexist = 1;
open(FILEHANDLE, $Voicemail) or die "Error opening $Voicemail: $!";
while (<>)
{
if (/(.*) =>/){ #if the voicemail box already exists, skip it
$vmailnotexist = 0;
break;
}
}
close FILEHANDLE;

if ($vmailnotexist) {
$timezone = "tz=" . lc($timezone . "|");
open(FILEHANDLE, ">>$Voicemail") or die "Error opening $Voicemail: $!";
print FILEHANDLE $exten," => ",$exten,",",$ucname,",,,",$timezone,"attach=no|saycid=yes|envelope=yes|delete=no\n";
close FILEHANDLE;
}
}
print " Success!
";
}
print "

Note: You must now go into FreePBX, edit each extension, change CallWaiting to Enabled, click Submit, and then Apply Changes.