Hi guys,
First I would like to congratulate all the excellent work they are doing with A2B. The forum has been very helpful to solve some problems I encountered in my work with A2B.
And by the way, I need your help on a problem I'm facing. I'm using A2Billing V.1.8, work in Brazil, and because the particularities of the national telephone system CDR A2B standard does not meet our standard, so I had to do a SELECT to generate a new CDR as the standard I need.
However, I am having trouble with the "Card Numbers" that have more of a "SIP Account". For my SELECT, seeks to rise by "CallerID" to "SIP Account". Thus the origin of the SELECT returns incorrect, and duplicate records.
Code:
SELECT
cr.`lastname` AS contract,
DATE_FORMAT(cl.`starttime`, '%d-%c-%Y') AS callday,
DATE_FORMAT(cl.`starttime`, '%H:%i:%s') AS callhour,
cl.`real_sessiontime` AS calltime,
cl.`sessionbill` AS amount,
sb.`callerid` AS sourcenumber,
pr.`destination` AS destinationarea,
CASE WHEN LEFT(cl.`dnid`,2) = "00" THEN NULL ELSE "55" END AS countrycode,
CASE WHEN LEFT(cl.`dnid`,2) = "00" THEN NULL ELSE
CASE WHEN LENGTH(cl.`calledstation`) < 10 THEN LEFT(sb.`callerid`,2) ELSE MID(cl.`calledstation`,LENGTH(cl.`calledstation`) - 9,2) END
END AS regionprefix,
CASE WHEN LEFT(cl.`dnid`,2) = "00" THEN RIGHT(cl.`dnid`,LENGTH(cl.`dnid`) - 4) ELSE
CASE WHEN LENGTH(cl.`calledstation`) < 10 THEN cl.`calledstation` ELSE RIGHT(cl.`calledstation`,8) END
END AS destnumber,
CASE WHEN LEFT(trim(cl.`destination`),2) = LEFT((CASE WHEN LENGTH(trim(sb.`callerid`)) = 10 THEN CONCAT("55",trim(sb.`callerid`)) ELSE trim(sb.`callerid`) END),2) THEN
CASE WHEN LEFT(trim(cl.`destination`),3) = LEFT((CASE WHEN LENGTH(trim(sb.`callerid`)) = 10 THEN CONCAT("55",trim(sb.`callerid`)) ELSE trim(sb.`callerid`) END),3) THEN
CASE WHEN LEFT(trim(cl.`destination`),4) = LEFT((CASE WHEN LENGTH(trim(sb.`callerid`)) = 10 THEN CONCAT("55",trim(sb.`callerid`)) ELSE trim(sb.`callerid`) END),4) THEN
CASE WHEN mid(trim(cl.`destination`),5,1) < 6 THEN "VC1" ELSE "VC1M" END
ELSE
CASE WHEN mid(trim(cl.`destination`),5,1) < 6 THEN "VC2" ELSE "VC2M" END
END
ELSE
CASE WHEN mid(trim(cl.`destination`),5,1) < 6 THEN "VC3" ELSE "VC3M" END
END
ELSE
CASE WHEN LEFT(trim(trim(cl.`destination`)),2) <> "55" THEN "Internacional" ELSE NULL END
END AS calltype
FROM
cc_call cl
LEFT JOIN cc_card cr ON cl.`card_id` = cr.`id`
LEFT JOIN cc_sip_buddies sb ON cl.`card_id` = sb.`id_cc_card`
LEFT JOIN cc_ratecard rc ON cl.`id_ratecard` = rc.`id`
LEFT JOIN cc_prefix pr ON rc.`destination` = pr.`prefix`
LEFT JOIN cc_did did ON cl.`id_did` = did.`id`
WHERE
cl.`real_sessiontime` > 30
AND COALESCE(sb.`callerid`,"") <> ""
AND COALESCE(cl.`calledstation`,"") <> ""
ORDER BY
cr.`lastname`,
sb.`callerid`,
cl.`starttime`
;
I was looking at the file "call-log-customers.php" to try to identify ways of tying the "SIP Account" with the outgoing call, but could not find. (Do not know PHP very well).
Just like a tip in order to make this problem does not happen.
Thank you for everyone's help.
PS. Sorry for the english, I'm still learning.