Hello here is the code for wholesale accounts. It is not the best solution but it working for me fine. It works for prepaid and postpaid accounts. It uses databases of a2billing. Trunks , callplan ,rate card all from a2billing database. Also the rates are calculated according to the ones in simmilar manner to a2billing. Similar to a2billing.php failover trunks are working as well.
If any question or suggestion i will be glad.
[a2billing-text] exten => _X.,1,Set(Strheader=${SIP_HEADER(TO):5}) exten => _X.,2,Set(dst=${CUT(Strheader,@,1)}) exten => _X.,3,Set(strusername=0)
exten => _X.,4,MYSQL(Connect connid localhost XXX YYY ZZZ) exten => _X.,5,MYSQL(Query resultid ${connid} SELECT id,username,credit,tariff,inuse,lastuse,nbused,creditlimit,credit+IFNULL(creditlimit,0) as XXX FROM cc_card WHERE username="${CDR(accountcode)}" and credit + IFNULL(creditlimit,0)>0 and status="1" and CURRENT_TIMESTAMP < expirationdate) exten => _X.,6,MYSQL(Fetch fetchid ${resultid} strid strusername strcredit strtariff strinuse strlastuse strnbused strcreditlimit strXXX) exten => _X.,7,MYSQL(Clear ${resultid}) exten => _X.,8,GotoIf($[${strusername}=${CDR(accountcode)}]?10:53) ;;; first escape exten => _X.,9,GotoIf($[${StrXXX}>0]?10:53) ;;; first escape
exten => _X.,10,MYSQL(Query resultid ${connid} SELECT id,dialprefix,buyrate,rateinitial,initblock,id_trunk,idtariffplan,ratecard_id,tariffgroup_id from cc_callplan_lcr WHERE dialprefix = substring('${dst}',1,char_length(dialprefix)) and tariffgroup_id="${strtariff}" and (CURRENT_TIMESTAMP between startdate and stopdate) order by length(dialprefix) desc); exten => _X.,11,MYSQL(Fetch fetchid ${resultid} str_id str_dialprefix str_buyrate str_rateinitial str_initblock str_id_trunk str_idtariffplan str_ratecard_id str_tariffgroup_id) exten => _X.,12,MYSQL(Clear ${resultid})
exten => _X.,13,GotoIf($["${fetchid}"="0"]?53) ;second escape
exten => _X.,14,MYSQL(Query resultid ${connid} SELECT id,idtariffplan,dialprefix,buyrate,buyrateinitblock,buyrateincrement,rateinitial,initblock,billingblock,id_trunk,destination from cc_ratecard WHERE id="${str_id}" ); exten => _X.,15,MYSQL(Fetch fetchid ${resultid} str__id str__idtariffplan str__dialprefix str__buyrate str__buyrateinitblock str__buyrateincrement str__rateinitial str__initblock str__billingblock str__id_trunk str__destination ) exten => _X.,16,MYSQL(Clear ${resultid}) exten => _X.,17,GotoIf($["${fetchid}"="0"]?53) ;second escape
exten => _X.,18,MYSQL(Query resultid ${connid} SELECT id_trunk,trunkprefix,providertech,providerip,removeprefix,failover_trunk from cc_trunk WHERE id_trunk ="${str_id_trunk}" ); exten => _X.,19,MYSQL(Fetch fetchid ${resultid} trunk_id_trunk trunk_trunkprefix trunk_providertech trunk_providerip trunk_removeprefix trunk_failover_trunk) exten => _X.,20,MYSQL(Clear ${resultid})
exten => _X.,21,GotoIf($["${fetchid}"="0"]?53) ; third escape exten => _X.,22,Set(CALL_ATTEMPT=1)
exten => _X.,23,MYSQL(Query resultid ${connid} UPDATE cc_card SET inuse=inuse+1 WHERE username='${strusername}') exten => _X.,24,set(str_maxcall=${MATH(${strXXX}/${str_rateinitial})}) exten => _X.,25,Set(accountnumber=${strusername}) exten => _X.,26,Set(EXTEN1=${dst}) exten => _X.,27,Set(callrate=${str_rateinitial})
exten => _X.,28,GotoIf($[${ISNULL(${trunk_removeprefix})}]?32) exten => _X.,29,Set(len_trunk_pref_remove=${LEN(${trunk_removeprefix})}) exten => _X.,30,GotoIf($["${dst:0:${len_trunk_pref_remove}}" = "${trunk_removeprefix}"]?31:32) exten => _X.,31,Set(EXTEN1=${dst:${len_trunk_pref_remove}:${LEN(${dst})}}) exten => _X.,32,Dial(${trunk_providertech}/${trunk_trunkprefix}${EXTEN1}@${trunk_providerip},60,iL(${MATH(${strXXX}*600000)}:61000:30000))
exten => _X.,33,Noop(${DIALSTATUS}) exten => _X.,34,GotoIf($["${DIALSTATUS}" = "CONGESTION"]?38) exten => _X.,35,GotoIf($["${DIALSTATUS}" = "CHANUNAVAIL"]?38) exten => _X.,36,GotoIf($["${DIALSTATUS}" = "BUSY"]?54) exten => _X.,37,GotoIf($["${DIALSTATUS}" = "NOANSWER"]?53) exten => _X.,38,Set(CALL_ATTEMPT=$[${CALL_ATTEMPT} + 1]) exten => _X.,39,GotoIf($["${CALL_ATTEMPT}" > "4"]?53)
exten => _X.,40,MYSQL(Query resultid ${connid} SELECT id_trunk,trunkprefix,providertech,providerip,removeprefix,failover_trunk from cc_trunk WHERE id_trunk ="${trunk_failover_trunk}" ); exten => _X.,41,MYSQL(Fetch fetchid ${resultid} trunk_id_trunk trunk_trunkprefix trunk_providertech trunk_providerip trunk_removeprefix trunk_failover_trunk)
exten => _X.,42,GotoIf($["${fetchid}"="0"]?53) ; third escape exten => _X.,43,GotoIf($[${ISNULL(${trunk_removeprefix})}]?47) exten => _X.,44,Set(len_trunk_pref_remove=${LEN(${trunk_removeprefix})}) exten => _X.,45,GotoIf($["${dst:0:${len_trunk_pref_remove}}" = "${trunk_removeprefix}"]?46:47)
exten => _X.,46,Set(EXTEN1=${dst:${len_trunk_pref_remove}:${LEN(${dst})}}) exten => _X.,47,Dial(${trunk_providertech}/${trunk_trunkprefix}${EXTEN1}@${trunk_providerip},60,iL(${MATH(${strXXX}*600000)}:61000:30000))
exten => _X.,48,Noop(${DIALSTATUS}) exten => _X.,49,GotoIf($["${DIALSTATUS}" = "CONGESTION"]?38) exten => _X.,50,GotoIf($["${DIALSTATUS}" = "CHANUNAVAIL"]?38) exten => _X.,51,GotoIf($["${DIALSTATUS}" = "BUSY"]?54) exten => _X.,52,GotoIf($["${DIALSTATUS}" = "NOANSWER"]?53) exten => _X.,53,Hangup() exten => _X.,54,Hangup(41)
exten => h,1,GotoIf($[${strusername}=${CDR(accountcode)}]?2:31) exten => h,2,Set(StrSessiontime=${CDR(billsec)}) exten => h,3,Set(StrCalledstation=${dst}) exten => h,4,Set(StrDialstatus=${DIALSTATUS}) exten => h,5,Set(StrDialstatus_value=9) exten => h,6,Set(sellcost=0) exten => h,7,Set(buycost=0) exten => h,8,Set(StrChannel=${CDR(channel)}) exten => h,9,GotoIf($["${StrSessiontime}" = "0"]?h,12:10) exten => h,10,ExecIf($[${StrSessiontime}>${str__initblock}] ?set(sellcost=$[CEIL(${StrSessiontime}/${str__billingblock})*${str__billingblock}/60*${str__rateinitial}])) exten => h,11,ExecIf($[${StrSessiontime}<=${str__initblock}] ?set(sellcost=$[${str__initblock}/60*${str__rateinitial}])) exten => h,12,set(sellcost_all=${sellcost}) exten => h,13,GotoIf($["${StrSessiontime}" = "0"]?h,16:14) exten => h,14,ExecIf($[${StrSessiontime}>${str__buyrateinitblock}] ?set(buycost=$[CEIL(${StrSessiontime}/${str__buyrateincrement})*${str__buyrateincrement}/60*${str__buyrate}])) exten => h,15,ExecIf($[${StrSessiontime}<=${str__buyrateinitblock}] ?set(buycost=$[${str__buyrateinitblock}/60*${str__buyrate}])) exten => h,16,set(buycost_all=${buycost})
exten => h,17,ExecIf($[ "${StrDialstatus}" = "CHANUNAVAIL" ] ?Set(StrDialstatus_value=6)) exten => h,18,ExecIf($[ "${StrDialstatus}" = "CANCEL" ] ?Set(StrDialstatus_value=4)) exten => h,19,ExecIf($[ "${StrDialstatus}" = "BUSY" ] ?Set(StrDialstatus_value=2)) exten => h,20,ExecIf($[ "${StrDialstatus}" = "CONGESTION" ] ?Set(StrDialstatus_value=5)) exten => h,21,ExecIf($[ "${StrDialstatus}" = "ANSWER" ] ?Set(StrDialstatus_value=1)) exten => h,22,ExecIf($[ "${StrDialstatus}" = "NOANSWER" ] ?Set(StrDialstatus_value=3)) exten => h,23,ExecIf($[ "${StrDialstatus}" = "" ] ?Set(StrDialstatus_value=6))
exten => h,24,MYSQL(Query resultid ${connid} INSERT INTO cc_call (uniqueid, sessionid, card_id, nasipaddress, starttime, sessiontime, real_sessiontime, calledstation, terminatecauseid, stoptime, sessionbill, id_tariffgroup, id_tariffplan, id_ratecard, id_trunk, src, sipiax, buycost, id_card_package_offer, dnid, destination) VALUES ('${CDR(uniqueid)}','${CDR(channel)}','${strid}','',SUBDATE(CURRENT_TIMESTAMP, INTERVAL ${StrSessiontime} SECOND),'${StrSessiontime}','${StrSessiontime}','${StrCalledstation}','${StrDialstatus_value}',NOW(),'${sellcost_all}','${str_tariffgroup_id}','${str_idtariffplan}','${str_ratecard_id}','${trunk_id_trunk}','${CALLERID(num)}','0','${buycost_all}',NULL,'${CDR(dnid)}','${str_dialprefix}')) exten => h,25,MYSQL(Query resultid ${connid} UPDATE cc_trunk SET secondusedreal=secondusedreal+${StrSessiontime} WHERE id_trunk='${trunk_id_trunk}') exten => h,26,MYSQL(Query resultid ${connid} UPDATE cc_tariffplan SET secondusedreal=secondusedreal+${StrSessiontime} WHERE id='${str__idtariffplan}') exten => h,27,MYSQL(Query resultid ${connid} UPDATE cc_card SET inuse=inuse-1 WHERE username='${strusername}') exten => h,28,MYSQL(Query resultid ${connid} UPDATE cc_card SET credit=credit-${sellcost_all}, nbused=nbused+1,lastuse=NOW() WHERE username='${strusername}')
exten => h,29,NOOP(sellcost=${sellcost},buycost=${buycost})
exten => h,30,NOOP(finished) exten => h,31,MYSQL(Disconnect ${connid})
|