All voip solutions

[globals]
CALLFILENAME = ""
RT=60
DIAL_OPTIONS = wWtHL(3600000:61000:30000)

ASTETCDIR = /etc/asterisk
ASTMODDIR = /usr/lib/asterisk/modules
ASTVARLIBDIR = /var/lib/asterisk
ASTAGIDIR = /var/lib/asterisk/agi-bin
ASTSPOOLDIR = /var/spool/asterisk
ASTRUNDIR = /var/run/asterisk
ASTLOGDIR = /var/log/asterisk
DBHOST=127.0.0.1
DBUSER=a2billinguser
DBPASS=a2billing
DBNAME=mya2billing

[callpstn]
exten=>h,1,Set(callednum=${CDR(dst)})
exten=>h,2,Gotoif($["${CDR(disposition)}" ="ANSWERED"]?3:5)
exten =>h,3,Macro(updatecdr,${UNIQUEID},${CHANNEL},${CDR(accountcode)},${rateid},${ANSWEREDTIME},${chargeoption},${callednum})
exten =>h,4,Macro(updatecustomer,${CDR(accountcode)},${chargeoption},${sumcost})
exten=>h,5,Hangup()
exten =>_X.,1,Macro(accountinfo,${CDR(accountcode)})
exten =>_X.,n,Macro(rateinfo,${credit},${creditlimit},${tariffid},${EXTEN})
exten =>_X.,n,Set(maxseconds=${MATH(1000*${maxseconds})})
;; change SIPTRUNKTOPSTN to your termination IP
exten =>_X.,n,Dial(SIP/SIPTRUNKTOPSTN/${EXTEN}|60|wWHL(${maxseconds}))
exten =>_X.,n,Hangup

;dial as 00xxxxxxx
[a2billing]
exten =>_00X.,1,goto(callpstn,${EXTEN:2},1)
exten =>_00X.,2,Hangup

[macro-accountinfo]
exten =>s,1,MYSQL(Connect connid ${DBHOST} ${DBUSER} ${DBPASS} ${DBNAME})
exten =>s,2,MYSQL(query resultid ${connid} Call \accountinfo \("${ARG1}"))
exten =>s,3,MYSQL(Fetch fetchid ${resultid} credit \tariffid \creditlimit)
exten =>s,4,MYSQL(Clear ${resultid})
exten =>s,5,MYSQL(Disconnect ${connid})
exten =>s,6,NoOp(${tariffid})
exten =>s,7,Gotoif($["${tariffid}" =""]?noaccount,1:8)
exten=>s,8,NoOp(credit:${credit}|creditlimit:${creditlimit})
exten=>noaccount,1,Hangup

[macro-rateinfo]
exten =>s,1,MYSQL(Connect connid ${DBHOST} ${DBUSER} ${DBPASS} ${DBNAME})
exten =>s,2,MYSQL(query resultid ${connid} Call \rateinfo\("${credit}"\,"${creditlimit}"\,"${tariffid}"\,"${ARG4}"))
exten =>s,3,MYSQL(Fetch fetchid ${resultid} rateid \maxseconds \chargeoption)
exten =>s,4,MYSQL(Clear ${resultid})
exten =>s,5,MYSQL(Disconnect ${connid})
exten =>s,6,NoOp(rateid:${rateid}|maxseconds:${maxseconds}:chargeoption:${chargeoption})
exten =>s,7,Gotoif($["${rateid}" =""]?norate,1:8)
exten=>s,8,NoOp(rateid:${rateid}|maxseconds:${maxseconds}|chargeoption:${chargeoption})
exten=>norate,1,Hangup

[macro-updatecdr]
exten =>s,1,MYSQL(Connect connid ${DBHOST} ${DBUSER} ${DBPASS} ${DBNAME})
exten =>s,2,MYSQL(query resultid ${connid} Call \updatecdr\("${ARG1}"\,"${ARG2}"\,"${ARG3}"\,"${ARG4}"\,"${ARG5}"\,"${ARG6}"\,"${ARG7}"))
exten =>s,3,MYSQL(Fetch fetchid ${resultid} sumcost)
exten =>s,4,MYSQL(Clear ${resultid})
exten =>s,5,MYSQL(Disconnect ${connid})
exten =>s,6,NoOp(sumcost:${sumcost})
exten =>s,7,Gotoif($["${sumcost}" =""]?nocdr,1:8)
exten =>s,8,NoOp(sumcost:${sumcost})
exten=>nocdr,1,Hangup

[macro-updatecustomer]
exten =>s,1,MYSQL(Connect connid ${DBHOST} ${DBUSER} ${DBPASS} ${DBNAME})
exten =>s,2,MYSQL(query resultid ${connid} Call \updatecustomer\("${ARG1}"\,"${ARG2}"\,"${ARG3}"))
exten =>s,3,MYSQL(Fetch fetchid ${resultid} callcost \recharge \topup)
exten =>s,4,MYSQL(Clear ${resultid})
exten =>s,5,MYSQL(Disconnect ${connid})
exten =>s,6,NoOp(fetch ok)
exten =>s,7,Gotoif($["${callcost}" =""]?noupdate,1:8)
exten =>s,8,NoOp(callcost:${callcost}|newtopup:${topup}|recharge:${recharge})
exten=>noupdate,1,Hangup

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;STORE PROCEDURES:;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `accountinfo`(IN accountcode varchar(32))
begin
select credit,tariff,creditlimit from cc_card where username=accountcode and activated='t';
end |

CREATE PROCEDURE `rateinfo`(topup double,recharge double, IN tariffid int,IN callednum varchar(32))
sproc:begin

declare tempid,tempib,tempbb,tempco,sumseconds ,totalseconds,ntimes int;
declare tempdialprefix,tempdest varchar(32);
declare temprate,tempcc,tempdc,totalbalance double;

select C.id ,C.dialprefix,C.destination,C.rateinitial ,C.initblock ,C.billingblock ,C.connectcharge ,C.disconnectcharge ,A.chargeoption
into tempid,tempdialprefix,tempdest,temprate,tempib,tempbb,tempcc,tempdc ,tempco
from cc_tariffplan A,cc_tariffgroup_plan B,cc_ratecard C where A.id =C.idtariffplan and A.id = B.idtariffplan and B.idtariffgroup=tariffid and left(callednum,length(C.dialprefix)) =C.dialprefix order by length(C.dialprefix) desc limit 1;
if isnull(tempid) then

leave sproc;
end if ;
if temprate <=0 then
select tempid,7200,tempco;
leave sproc;
end if;
if tempbb =0 then
leave sproc;
end if;

if (tempco >0 ) then
set totalbalance = recharge;
else
set totalbalance = topup+recharge ;
end if;

set totalbalance =totalbalance - tempcc-tempdc;

if (totalbalance <=0) then
leave sproc;
end if ;

set totalseconds = (totalbalance *60)/temprate ;
if (totalseconds < tempib ) then
leave sproc ;
end if;
set sumseconds = tempib;
set totalseconds = totalseconds -tempib;
set ntimes = totalseconds DIV tempbb ;
set sumseconds = sumseconds + (ntimes*tempbb) ;
select tempid,sumseconds,tempco;

end |

CREATE PROCEDURE `updatecdr`(uniqueid varchar(32),chanid varchar(32),IN accountcode varchar(32),IN rateid int, IN duration int,IN chargeoption int,IN callednum varchar(32))
sproc: begin

declare tempid,tempib,tempbb,tempbuyib,tempbuybb,tempco,sumseconds ,totalseconds,ntimes ,temptrunk,bakduration int;
declare tempdialprefix,tempdest varchar(32);
declare temprate, tempbuyrate,tempcc,tempdc,totalbalance,sumbuycost,sumcost decimal(10,3);
select C.id ,C.dialprefix,C.destination,C.buyrate,C.buyrateinitblock,C.buyrateincrement,C.rateinitial ,C.initblock ,C.billingblock ,C.connectcharge ,C.disconnectcharge ,C.id_trunk
into tempid,tempdialprefix,tempdest,tempbuyrate,tempbuyib,tempbuybb,temprate,tempib,tempbb,tempcc,tempdc,temptrunk
from cc_ratecard C where C.id=rateid;
if isnull(tempid) then

leave sproc;
end if ;
if temprate <=0 then

leave sproc;
end if;

set bakduration = duration ;
if (bakduration < tempib) then
set bakduration =tempib;
end if ;
set totalseconds = bakduration ;
set sumseconds = tempib;
set totalseconds = totalseconds - tempib;
set ntimes = totalseconds/tempbb;
set sumseconds = sumseconds + (ntimes * tempbb) ;
if (sumseconds < duration) then
set sumseconds = sumseconds + tempbb;
end if;
set sumcost = (sumseconds*temprate)/60 ;
set sumcost = sumcost + tempcc +tempdc ;
set totalseconds = duration ;
set sumseconds = tempbuyib;
set totalseconds = totalseconds - tempbuyib;
set ntimes = totalseconds/tempbuybb;
set sumseconds = sumseconds + (ntimes * tempbuybb) ;
if (sumseconds < duration) then
set sumseconds = sumseconds + tempbuybb;
end if;
set sumbuycost = (sumseconds*tempbuyrate)/60 ;
set sumbuycost = sumbuycost + tempcc +tempdc ;

INSERT INTO cc_call (uniqueid,sessionid,username,starttime,sessiontime, calledstation, terminatecause, stoptime, calledrate, sessionbill, destination, id_tariffgroup, id_tariffplan, id_ratecard, id_trunk, src, sipiax,buyrate,buycost) VALUES (uniqueid, chanid, accountcode, CURRENT_TIMESTAMP - INTERVAL duration SECOND , duration, callednum, 'ANSWER', now(), temprate,sumcost, tempdest, '2', '1',rateid, '2',accountcode, '0',tempbuyrate,sumbuycost);
select sumcost;

end |

CREATE PROCEDURE `updatecustomer`(IN accountcode varchar(32), IN chargeoption int,IN cost decimal(10,2))
sproc:begin
declare topup ,recharge decimal(10,2) ;
declare tempnbused ,tempid int;
select id,nbused,credit,creditlimit into tempid,tempnbused,recharge,topup from cc_card where username=accountcode;
if isnull(tempid) then
leave sproc;
end if ;

set recharge = recharge -cost;
update cc_card set credit = recharge where id= tempid;
if (tempnbused =0) then
update cc_card set firstusedate=now(),nbused = nbused +1,lastuse=now() where id=tempid;
else
update cc_card set nbused = nbused +1,lastuse=now() where id=tempid;
end if ;
select cost,recharge,topup;
end |