Trixbox iCal Conduit

Sounds interesting. Have you released your work to the public? If so, where? I'll take a look at it.

William...

OK DDWyer,

I have been reading through these forums (& searching a TON online), and you're the only person I've found who actually has a solution for EXACTLY what I want to do. I really would like to see if you can provide further information regarding your solution. It is fully-functional on incoming & outgoing calls? What about calls that get transferred in to you from another extension? What about multiple people with different email addresses, extensions and/or DIDs?

I'm not sure why you haven't made more of the details public yet (or maybe you have and I just haven't seen it). But I'd really like this feature. I run a small I.T. service company, and we do 90% of our billing straight from events in our Outlook calendars. And truthfully, I'm not the best at logging my life away before the computer. For some time now, I've been pondering upon a package that would do exactly what you've accomplished.

In any rate, whether you haven't published the details in order to retain the efforts of your hard work, or because you're just too busy, or because you don't feel like it is complete enough, I would urge/beg you.. PLEASE consider contributing back to the asterisk/trixbox project, as it can truly benefit many others. Don't be the only person on the planet who enjoys such a great & useful feature :-)

The funny thing is that there seems to be nothing/no one else out there that has accomplished (or desired to accomplish) what you have. There are CRM integration packages for asterisk/trixbox, but I haven't seen anything that deals with it at such a basic and simple level as what you've done.

Personally, I don't really need the recorded call, although I think that's pretty cool. I would like to see if there is a way that the calendar item could include the name from the caller ID (or possibly even a reverse lookup from the Outlook/exchange contacts, although I'd be very happy with the first) to assist me in quickly filling in the details of the call in the calendar item. I'd think this should be pretty doable.

I'm not a programmer. I'm a sysadmin. But what resources I have, I'd happily make available to help bring this feature into the light.

If your reasoning in not publicizing the details further is because you don't want to give it away for free, I would definitely consider paying for a good solution. Although truthfully I think it would be great to make it a freely-available contribution back to the community. Either way though, I'm in.

Please reply to this and let me know the status, and what I can do to help this feature and also enjoy the functionality of your add-on.

Sincerely,
Doug Mortensen
(doug) (AT) (impalanetworks) (dot) (com)

i operate a service company , and ALL our work is received over the phone ..ie "ring ring ..can you come and fix my coolroom?" . at present we have develped a small application that i use in our business, and it records all incoming conversations on a particular DID , there is a custom context that uses the deadagi command to create an ICS file and email it to an email address when the incomming call is complete (ie the caller has hung up). I extended the application some time ago and it is now our primary company address book ,and trixbox does lookups on this address book to determine callerid name. the big thing that we added about 3 months ago was google maps integration , so when a customer calls - i can go to our application page, click on the customers name (which has been looked up in our address book) and a Google map will pop-up with the customers delivery address.

i will not relaes this application at this stage , because it is still a work in progress , we (my company) has employed a team of developers to modify and integrate phreebooks and our application , the outcome will be an accounting 'ERP' system that is fully integrated with your business telephone system , it will record and log all contact you have with your customers. if anybody would like to help develop this accounting system i am willing to share what i have done so far , but i wont publicly release our inhouse custom solution at this stage.

Scott,

How hard would it be to cause the iCal exported logs to include the duration (start and end times)? This way the calendar items could be even more accurate. Ultimately I would still like to see something that would automatically bring these iCal items straight into a calendars in Outlook (depending on the person associated with the SIP extension [may be easiest by using their email address]).

Also, how can I help with your project? I haven't seen any good documentation for your program and its features. I could help create that, and even set it up in a wiki. I can host it if necessary. Whatever I can do to lighten your load a little so that you can dedicate more time to development, I'd be happy to do.

Please let me know. You can reach me directly through email as well if you'd like to take this out of the forum. My email address is listed above in my first post. I look forward to hearing back from you soon!

Sincerely,
Doug

this script does a lot more than just email the phone call as a .ICS file (which is imported into outlook calender) . maybe if you just want the calender import you can look at how i do mine and modify it to suit your needs.

#!/usr/bin/php -q
<?php
include "phpagi.php";
include "mailer_class.php";
include "class_dal.php";
///Server URL where the call was recorded. The URL should match as per the dial plan.
///If the recording was done at "/record/" then the $server_url should be "/record/".
///Remember to add the last slash (/).
$server_url="http://10.60.33.141/admin/appointments/";
$record_path="/var/www/html/admin/appointments/";
///This is the absolute path where the ics file will be created temporarily and then deleted.
///Remember to add the last slash (/).
$ics_path="/var/www/html/admin/record/";

///URL of the page for changing caller name.
$changecid_url="http://10.60.33.141/admin/cid/cid.php";

$AGI = new AGI();
$AGI->verbose("executed");

$callerid=$AGI->request['agi_callerid'];
$AGI->verbose("callerid: ".$callerid);

$callername=$AGI->request['agi_calleridname'];
$AGI->verbose("Caller Name: ".$callername);

$duration = $argv[1];
$AGI->verbose("Duration :$duration");

$uid=$AGI->get_variable("UNIQUEID");
$AGI->verbose("UID: ".$uid['data']);

$AGI->verbose("Dialled Number :".$AGI->request['agi_dnid']);

if($AGI->request['agi_callerid']=="unknown")
{
$soundfile_name = "unknown-".$uid['data']; ///Sound Recording path for unknown caller
}
else
{
$soundfile_name = $AGI->request['agi_callerid']."-".$uid['data']; ///Sound Recording path
}
// $soundfile_name = $AGI->request['agi_callerid']."-".$uid['data']; ///Sound Recording path

///Converting wav file to mp3 file

shell_exec("/usr/local/bin/sox -r 8000 -s -w ".$record_path.$soundfile_name.".wav -r 128000 ".$record_path.$soundfile_name.".mp3 resample");

$sound = $server_url.$soundfile_name.".mp3";

$ics = $ics_path.$AGI->request['agi_callerid'].".ics"; ///ics file path for calendar event

$AGI->verbose("SOUND :".$sound);
$AGI->verbose('ICS :'. $ics);

$cdrst=$AGI->get_variable("CDR(start)");
$AGI->verbose("Call Start Time: ".$cdrst['data']);

$start = time($cdrst['data']);
$end = $start + $duration;
$AGI->verbose("Start timestamp : ".$start);
$AGI->verbose("End timestamp : ".$end);

///E-Mail Header Details
$From="PHONECALL";
$FromEmailID="phonecall@sra.net.au";
$ToEmailId="darren@sra.net.au";
$subject="$callername - $callerid - $duration seconds.";

if($duration != "" || $duration != null)
{
include("ics/class.iCal.inc.php");

///Message for the recorded file's URL

$message="\r\nWe have recorded the conversation CLICK this URL:";
$message.="\r\n$sound\n";
$message.="\r\n You can add or change the caller Id Name with the following URL:";
$message.="\r\n$changecid_url?callerid=$callerid";

$AGI->verbose("MESSAGE : $message");

$iCal = (object) new iCal('', 0, '');

///Calendar Event details
$organizer = (array) array('PHONECALL', 'phonecall@sra.net.au');
$categories = array('Telephone Call');

$iCal->addEvent( 'organizer', // Organizer
$start, // Start Time
$end, // End Time
'', // Location
0, // Transparancy (0 = OPAQUE | 1 = TRANSPARENT)
$categories, // Array with Strings
$subject.$message, // Description
$subject, // Title
1, // Class (0 = PRIVATE | 1 = PUBLIC | 2 = CONFIDENTIAL)
'', // Array (key = attendee name, value = e-mail, second value = role of the attendee [0 = CHAIR | 1 = REQ | 2 = OPT | 3 =NON])
5, // Priority = 0-9
'', // frequency: 0 = once, secoundly - yearly = 1-7
'', // recurrency end: ('' = forever | integer = number of times | timestring = explicit date)
'', // Interval for frequency (every 2,3,4 weeks...)
'', // Array with the number of the days the event accures (example: array(0,1,5) = Sunday, Monday, Friday
'', // Startday of the Week ( 0 = Sunday - 6 = Saturday)
'', // exeption dates: Array with timestamps of dates that should not be includes in the recurring event
0, // Sets the time in minutes an alarm appears before the event in the programm. no alarm if empty string or 0
1, // Status of the event (0 = TENTATIVE, 1 = CONFIRMED, 2 = CANCELLED)
'', // optional URL for that event
'en', // Language of the Strings
'' // Optional UID for this event
);

$handle = fopen($ics, 'w');
fputs($handle, $iCal->getOutput('ics'));
fclose($handle);

$obj_mail = new attach_mailer($From,$FromEmailID,$ToEmailId,'','',$subject,$message);
$obj_mail->create_attachment_part($ics,"inline");
$obj_mail->process_mail();
$email=$obj_mail->get_msg_str();
$AGI->verbose("email : ".$email);

}
else
{
///Message when call was not answered.
$message="Call not answered on : ".$cdrst['data'];

$AGI->verbose("call not answered");
$obj_mail = new attach_mailer($From,$FromEmailID,$ToEmailId,'','',$subject,$message);
$obj_mail->process_mail();
$email=$obj_mail->get_msg_str();
$AGI->verbose("email : ".$email);
}
unlink($ics);
unlink($record_path.$soundfile_name.".wav");
$AGI->verbose("----------------File deleted----------------");
add_data();

function add_data()
{

global $callername,$callerid,$sound,$start,$AGI;
$sql=new dal('localhost','root','appointment','passw0rd');
// $insert_phone="insert into phonebook(cidname,cidnumber) values('".$callername."','".$callerid."')";
// $sql-> sql_query($insert_phone);
$phonevalue_id=check_cid();
$query= "insert into call_details(call_date,phonebook_id,soundfile) values('".$start."','".$phonevalue_id."','".$sound."')";
$sql-> sql_query($query);
}

function check_cid()
{
global $callername,$callerid,$sound,$start,$AGI;
$sql=new dal('localhost','root','appointment','passw0rd');
$query="select count(*) as count from phonebook where cidnumber='".$callerid."'";
// $AGI->verbose($query);
$sql-> sql_query($query);
if($res=$sql->fetch_array())
{
$data=$res['count'];
}
if($data >=1)
{
$select_query="select phonebook_id from phonebook where cidnumber='".$callerid."'";
$sql-> sql_query($select_query);
// $AGI->verbose($select_query);
if($result=$sql->fetch_array())
{
// $AGI->verbose($result['phonebook_id']);
return ($result['phonebook_id']);
}

}
else
{
$insert_phone="insert into phonebook(phonebook_id,cidname,cidnumber) values('','".$callername."','".$callerid."')";
$sql-> sql_query($insert_phone);
$AGI->verbose($insert_phone);
$val = check_cid();
return $val;
}
}

?>

I'm calling my creation TrixiCal. (created for trixbox to create iCalendars ;-)

It's a bash script that runs as a cron job that definitely could use improvements in many ways. But for now, it really does make it easier for us to see our day from the phone call perspective in calendars in Outlook. We bill our time directly from Outlook, so any phone call that we want to bill, we just drag from the iCalendar to our own calendar, modify the subject to make it detailed & accurate, then with our BCM plugin, link it to a customer account and submit it as a time entry in Office Accounting.

I'd be interested in doing more with it if others had interest.

Here's my script:

#!/bin/bash

#echo "Running trixical make_icals.ics" at `date`

#1.) Create temp file
#====================================
rm /tmp/cdr_temp.csv
#mysql -u asteriskuser -p --execute="SELECT calldate, clid, src, dst, dcontext, channel, dstchannel, billsec, uniqueid INTO OUTFILE '/tmp/cdr_temp.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY \"\" LINES TERMINATED BY '\n' FROM cdr WHERE (DATE(calldate)=CURDATE()) AND (TIME(calldate)>=TIME(TIMESTAMPADD(HOUR,-4,NOW())))" asteriskcdrdb
#mysql -u asteriskuser -p --execute="SELECT calldate, clid, src, dst, dcontext, channel, dstchannel, billsec, uniqueid INTO OUTFILE '/tmp/cdr_temp.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY \"\" LINES TERMINATED BY '\n' FROM cdr" asteriskcdrdb
mysql -u asteriskuser --password=YourPasswordGoesHere --execute="SELECT calldate, clid, src, dst, dcontext, channel, dstchannel, lastapp, billsec, disposition, uniqueid INTO OUTFILE '/tmp/cdr_temp.csv' FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY \"\" LINES TERMINATED BY '\n' FROM cdr WHERE (DATE(calldate)=CURDATE()) AND (TIME(calldate)>=TIME(TIMESTAMPADD(HOUR,-4,NOW())))" asteriskcdrdb

#2.) Seperate date & time into 2 fields (required for array population)
#====================================
#sed 's/\([0-9]\{4\}\-[0-9]\{2\}\-[0-9]\{2\}\)\( \)/\1,/' < /tmp/cdr_temp.csv > /tmp/cdr_temp2.csv
sed 's/\([0-9]\{4\}\-[0-9]\{2\}\-[0-9]\{2\}\)\( \)/\1;/' < /tmp/cdr_temp.csv > /tmp/cdr_temp2.csv
cat /tmp/cdr_temp2.csv > /tmp/cdr_temp.csv
rm /tmp/cdr_temp2.csv

#echo "4.) Create/append iCalendar files"
#====================================
#Perform the following for each extension:

#Begin loop for each extension
for CurExtension in 701 702 703 801 802 803 600 899
do

#echo "a.) Extract all calls with current extension to extension.tmp"
#------------------------------------

#grep '[^0-9]$CurExtension'[^0-9] /tmp/cdr_temp.csv > /tmp/$CurExtension.tmp
grep [^0-9]$CurExtension[^0-9] /tmp/cdr_temp.csv > /tmp/$CurExtension.tmp
#grep '[^0-9]703'[^0-9] /tmp/cdr_temp.csv > /tmp/$CurExtension.tmp

#echo "b.) Load up array"
#------------------------------------
NumOfRecords=`grep -c . /tmp/$CurExtension.tmp`
unset StartDate
unset StartTime
unset CallerID
unset Source
unset Destination
unset DContext
unset Channel
unset DestChannel
unset CallType
unset Duration
unset Status
unset UniqueID

IFS=";"
StartDate=(`awk -F";" 'BEGIN {ORS = ";"} {print $1 }' /tmp/$CurExtension.tmp`)
StartTime=(`awk -F";" 'BEGIN {ORS = ";"} {print $2 }' /tmp/$CurExtension.tmp`)
CallerID=(`awk -F";" 'BEGIN {ORS = ";"} {print $3 }' /tmp/$CurExtension.tmp`)
Source=(`awk -F";" 'BEGIN {ORS = ";"} {print $4 }' /tmp/$CurExtension.tmp`)
Destination=(`awk -F";" 'BEGIN {ORS = ";"} {print $5 }' /tmp/$CurExtension.tmp`)
DContext=(`awk -F";" 'BEGIN {ORS = ";"} {print $6 }' /tmp/$CurExtension.tmp`)
Channel=(`awk -F";" 'BEGIN {ORS = ";"} {print $7 }' /tmp/$CurExtension.tmp`)
DestChannel=(`awk -F";" 'BEGIN {ORS = ";"} {print $8 }' /tmp/$CurExtension.tmp`)
CallType=(`awk -F";" 'BEGIN {ORS = ";"} {print $9 }' /tmp/$CurExtension.tmp`)
Duration=(`awk -F";" 'BEGIN {ORS = ";"} {print $10 }' /tmp/$CurExtension.tmp`)
Status=(`awk -F";" 'BEGIN {ORS = ";"} {print $11 }' /tmp/$CurExtension.tmp`)
UniqueID=(`awk -F";" 'BEGIN {ORS = ";"} {print $12 }' /tmp/$CurExtension.tmp`)

unset IFS

#echo "c.) Strip off last line from iCalendar (will be readded later)"
#------------------------------------
sed '$d' /var/www/html/icals/$CurExtension.ics > /var/www/html/icals/$CurExtension.tmp
cat /var/www/html/icals/$CurExtension.tmp > /var/www/html/icals/$CurExtension.ics

#echo "d.) Begin loop to finish formatting variables and output VEVENTS to iCalendar"
#------------------------------------

CurRecord=0
while [ $CurRecord -lt $NumOfRecords ]
do

#Check whether this phone call already exists in the iCal file before processing it
CurUniqueID=${UniqueID[$CurRecord]}
export CurUniqueID
export CurExtension
if [ "$CurUniqueID" != "`grep -o \$CurUniqueID /var/www/html/icals/\$CurExtension.ics`" ]
then

#echo "i.) Create $StartTimeStamp array variable & load it for each array element"
#....................................
#echo Creating StartTimeStamp
StartTimeStamp[$CurRecord]=`date -d ${StartDate[$CurRecord]}\ ${StartTime[$CurRecord]}`

#echo "ii.) Create $EndTimeStamp array variable & load it for each array element"
#....................................
#echo Creating EndTimeStamp
EndTimeStamp[$CurRecord]=`date --date="${StartTimeStamp[$CurRecord]} ${Duration[$CurRecord]} seconds"`

#echo "iii.) Create $DtStart and $DtEnd array variables & load them up for each array element"
#....................................
#echo Creating DtStart
DtStart[$CurRecord]=`date +%Y%m%dT%H%M%S --date="${StartTimeStamp[$CurRecord]}"`
#echo Creating DtEnd
DtEnd[$CurRecord]=`date +%Y%m%dT%H%M%S --date="${StartTimeStamp[$CurRecord]} ${Duration[$CurRecord]} seconds"`

#echo "iv.) Format $Summary"
#....................................
# In the future, I'll be adding some conditional statements to format the Summary based on whether it was incoming or outgoing and make it a bit cleaner
Summary[$CurRecord]="${CallType[$CurRecord]} Call between ${Source[$CurRecord]} ${CallerID[$CurRecord]} (${Channel[$CurRecord]}) AND ${Destination[$CurRecord]} (${DestChannel[$CurRecord]}) for ${Duration[$CurRecord]} seconds ${Status[$CurRecord]}"

#echo "v.) Create VEVENT for current array items"
#....................................
echo BEGIN:VEVENT >> /var/www/html/icals/$CurExtension.ics
echo DTSTART:${DtStart[$CurRecord]} >> /var/www/html/icals/$CurExtension.ics
echo DTEND:${DtEnd[$CurRecord]} >> /var/www/html/icals/$CurExtension.ics
echo SUMMARY:${Summary[$CurRecord]} >> /var/www/html/icals/$CurExtension.ics
echo CATEGORIES:Phone Call >> /var/www/html/icals/$CurExtension.ics
echo STATUS:CONFIRMED >> /var/www/html/icals/$CurExtension.ics
echo UID:${UniqueID[$CurRecord]} >> /var/www/html/icals/$CurExtension.ics
echo DTSTAMP:`date +%Y%m%dT%H%M%S` >> /var/www/html/icals/$CurExtension.ics
echo SEQUENCE:0 >> /var/www/html/icals/$CurExtension.ics
echo END:VEVENT >> /var/www/html/icals/$CurExtension.ics

#vi.) Increment counter & end while loop
#....................................

fi

CurRecord=$[$CurRecord+1]

done
#vii.) End VCalendar file
#....................................

echo END:VCALENDAR >> /var/www/html/icals/$CurExtension.ics

#d.) End loop for current extension

done