[Serusers] Accounting module mods with MySQL and SERWEB

[John N. French]

Some interesting modifications I have made to improve
acc process.

1)
--------------
Some changes made to acc.c to strip down the user
names for output and also to get missed calls logged
with callee rather than caller. It was showing missed
calls on the callers missed_calls.php which is
incorrect.  
Here are the extracts, I can post the full prog if
required:

/*
JF added following block to extract to user
*/
	str_copy(&to_user, &rq->to->body);
        if ((ul_get_user(&to_user) < 0) ||
!to_user.len) {
            LOG(L_ERR, "ERROR: acc_request: Error
while extracting to_username\n");
            return -1;
	}

/*
	str_copy(&user, &rq->first_line.u.request.uri);
JF modified this copy statement to create correct acc
username (from not to!)
*/
	str_copy(&user, &rq->from->body);


2)
---------------------
I modified the $q in accounting.php to the below to
include calls where BYE sent by callee as well as
caller (before it was not displaying calls when closed
by callee).  Also elimination of double counting of
calls due to 
multiple INVITEs being sent by UAC.  max(time) is
required as distinct rows are produced if the invites
are > 1sec apart.  "Group by" puts all the 
INVITEs together so that the latest one can be
selected with max(). Assume that latest invite is
correct for measuring call start time.  The max(time) 
column has been aliased to c2 so that it can be used
in the output $time=Substr($row->c2,0,16).
Here it is:
	$q="select distinct t1.sip_callid, max(t1.time) c2,
t1.sip_to,
sec_to_time(unix_timestamp(t2.time)-unix_timestamp(max(t1.time)))
as length ".
		"from ".$config->table_accounting." t1,
".$config->table_accounting." t2 ".
		"where (t1.user='".$auth->auth["uname"]."' and
t1.sip_method='INVITE') and
((t2.user='".$auth->auth["uname"]."' or
t2.sip_to='".$auth->auth["uname"]."') and
t2.sip_method='BYE') and ".
			"t1.sip_callid=t2.sip_callid group by t1.sip_callid
".
		"order by t1.time desc";


3) I modified the &q in missed_calls.php to the below
to include calls to aliases as well.  The query looks
in the missed_calls table for the sip_to = logged-in
user and also joins with aliases table to extract
calls to alias of contact. Otherwise missed_calls.php
only provided calls made directly to a user and
ignored calls to aliases.
Here it is:
	$q="select distinct t1.user, t1.sip_to, t1.time,
t1.sip_status, t1.sip_callid from
".$config->table_missed_calls." 

t1,".$config->table_aliases." t2 where
t1.sip_to='".$auth->auth["uname"]."' OR 

('sip:".$auth->auth["uname"]."@".$config->default_domain."'=t2.contact
AND t2.user=t1.sip_to) order by time desc";
echo $q;

4) 
-----------------
Any comments will be appreciated.


__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/