Support A2Billing :

provided by Star2Billing S.L.

Support A2Billing :
It is currently Fri Mar 29, 2024 8:59 am
Predictive Dialer


All times are UTC




Post new topic Reply to topic  [ 1 post ] 
Author Message
 Post subject: Custom SELECT to generate a CDR to use in Brazil
PostPosted: Tue Dec 14, 2010 12:45 pm 
Offline
User avatar

Joined: Sun Nov 07, 2010 3:11 pm
Posts: 3
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.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 1 post ] 
Auto Dialer Software


All times are UTC


Who is online

Users browsing this forum: No registered users and 8 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group