Support A2Billing :

provided by Star2Billing S.L.

Support A2Billing :
It is currently Thu Mar 28, 2024 10:25 pm
Voice Broadcast System


All times are UTC




Post new topic Reply to topic  [ 1 post ] 
Author Message
 Post subject: Better Merge of tarrifs proposal.
PostPosted: Sat Sep 10, 2011 7:44 am 
Offline

Joined: Wed Sep 07, 2011 9:05 pm
Posts: 9
I start with 2 tariffplans :

-+--------------+--------------------------+-------------+
| id | idtariffplan | dialprefix | buyrate | buyrateinitblock | buyrateincrement | rateinitial | initblock | billingblock | connectcharge | disconnectcharge | stepchargea | chargea | timechargea | billingblocka | stepchargeb | chargeb | timechargeb | billingblockb | stepchargec | chargec | timechargec | billingblockc | startdate | stopdate | starttime | endtime | id_trunk | musiconhold | id_outbound_cidgroup | rounding_calltime | rounding_threshold | additional_block_charge | additional_block_charge_time | tag | disconnectcharge_after | is_merged | additional_grace | minimal_cost | announce_time_correction | destination |
+--------+--------------+------------+---------+------------------+------------------+-------------+-----------+--------------+---------------+------------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+---------------------+---------------------+-----------+---------+----------+-------------+----------------------+-------------------+--------------------+-------------------------+------------------------------+------+------------------------+-----------+------------------+--------------+--------------------------+-------------+
| 214135 | 10 | 1700555 | 0.10000 | 1 | 1 | 0.10000 | 1 | 1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 | 0 | 0 | 0.00000 | 0.00000 | 0 | 0 | 0 | 0 | 0 | 0 | 2011-09-10 06:54:05 | 2021-09-10 06:54:05 | 0 | 10079 | 6 | | -1 | 0 | 0 | 0.00000 | 0 | | 0 | 0 | 0 | 0.00000 | 1.000 | 1700555 |
| 214138 | 10 | 17005551 | 0.20000 | 1 | 1 | 0.20000 | 1 | 1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 | 0 | 0 | 0.00000 | 0.00000 | 0 | 0 | 0 | 0 | 0 | 0 | 2011-09-10 06:59:00 | 2021-09-10 06:59:00 | 0 | 10079 | 6 | | -1 | 0 | 0 | 0.00000 | 0 | NULL | 0 | 0 | 0 | 0.00000 | 1.000 | 17005551 |
| 214139 | 10 | 170055511 | 0.12000 | 1 | 1 | 0.12000 | 1 | 1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 | 0 | 0 | 0.00000 | 0.00000 | 0 | 0 | 0 | 0 | 0 | 0 | 2011-09-10 07:00:24 | 2021-09-10 07:00:24 | 0 | 10079 | 6 | | -1 | 0 | 0 | 0.00000 | 0 | NULL | 0 | 0 | 0 | 0.00000 | 1.000 | 170055511 |
+--------+--------------+------------+---------+------------------+------------------+-------------+-----------+--------------+---------------+------------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+---------------------+---------------------+-----------+---------+----------+-------------+----------------------+-------------------+--------------------+-------------------------+------------------------------+------+------------------------+-----------+------------------+--------------+--------------------------+-------------+
3 rows in set (0.00 sec)

mysql> select * from cc_ratecard where idtariffplan=11;
+--------+--------------+------------+---------+------------------+------------------+-------------+-----------+--------------+---------------+------------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+---------------------+---------------------+-----------+---------+----------+-------------+----------------------+-------------------+--------------------+-------------------------+------------------------------+------+------------------------+-----------+------------------+--------------+--------------------------+-------------+
| id | idtariffplan | dialprefix | buyrate | buyrateinitblock | buyrateincrement | rateinitial | initblock | billingblock | connectcharge | disconnectcharge | stepchargea | chargea | timechargea | billingblocka | stepchargeb | chargeb | timechargeb | billingblockb | stepchargec | chargec | timechargec | billingblockc | startdate | stopdate | starttime | endtime | id_trunk | musiconhold | id_outbound_cidgroup | rounding_calltime | rounding_threshold | additional_block_charge | additional_block_charge_time | tag | disconnectcharge_after | is_merged | additional_grace | minimal_cost | announce_time_correction | destination |
+--------+--------------+------------+---------+------------------+------------------+-------------+-----------+--------------+---------------+------------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+---------------------+---------------------+-----------+---------+----------+-------------+----------------------+-------------------+--------------------+-------------------------+------------------------------+------+------------------------+-----------+------------------+--------------+--------------------------+-------------+
| 214140 | 11 | 1700555 | 0.15000 | 1 | 1 | 0.15000 | 1 | 1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 | 0 | 0 | 0.00000 | 0.00000 | 0 | 0 | 0 | 0 | 0 | 0 | 2011-09-10 07:07:37 | 2021-09-10 07:07:37 | 0 | 10079 | 8 | | -1 | 0 | 0 | 0.00000 | 0 | NULL | 0 | 0 | 0 | 0.00000 | 1.000 | 1700555 |
| 214141 | 11 | 17005552 | 0.30000 | 1 | 1 | 0.30000 | 1 | 1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 | 0 | 0 | 0.00000 | 0.00000 | 0 | 0 | 0 | 0 | 0 | 0 | 2011-09-10 07:09:22 | 2021-09-10 07:09:22 | 0 | 10079 | 8 | | -1 | 0 | 0 | 0.00000 | 0 | NULL | 0 | 0 | 0 | 0.00000 | 1.000 | 17005552 |
| 214142 | 11 | 17005554 | 0.10000 | 1 | 1 | 0.10000 | 1 | 1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 | 0 | 0 | 0.00000 | 0.00000 | 0 | 0 | 0 | 0 | 0 | 0 | 2011-09-10 07:10:53 | 2021-09-10 07:10:53 | 0 | 10079 | 8 | | -1 | 0 | 0 | 0.00000 | 0 | NULL | 0 | 0 | 0 | 0.00000 | 1.000 | 17005554 |
+--------+--------------+------------+---------+------------------+------------------+-------------+-----------+--------------+---------------+------------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+---------------------+---------------------+-----------+---------+----------+-------------+----------------------+-------------------+--------------------+-------------------------+------------------------------+------+------------------------+-----------+------------------+--------------+--------------------------+-------------+

One is essentially a customer plan the other a carrier, given that not all fields can be merged by the built in tool (at the time of my writing) I decided that I had to merge the carrier (10) into the customer(11). To my dismay only 1 record was update"Ratecard is successfully merged. 1 Record(s)"

And the resulting customer record looked like this:
+--------+--------------+------------+---------+------------------+------------------+-------------+-----------+--------------+---------------+------------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+---------------------+---------------------+-----------+---------+----------+-------------+----------------------+-------------------+--------------------+-------------------------+------------------------------+------+------------------------+-----------+------------------+--------------+--------------------------+-------------+
| id | idtariffplan | dialprefix | buyrate | buyrateinitblock | buyrateincrement | rateinitial | initblock | billingblock | connectcharge | disconnectcharge | stepchargea | chargea | timechargea | billingblocka | stepchargeb | chargeb | timechargeb | billingblockb | stepchargec | chargec | timechargec | billingblockc | startdate | stopdate | starttime | endtime | id_trunk | musiconhold | id_outbound_cidgroup | rounding_calltime | rounding_threshold | additional_block_charge | additional_block_charge_time | tag | disconnectcharge_after | is_merged | additional_grace | minimal_cost | announce_time_correction | destination |
+--------+--------------+------------+---------+------------------+------------------+-------------+-----------+--------------+---------------+------------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+---------------------+---------------------+-----------+---------+----------+-------------+----------------------+-------------------+--------------------+-------------------------+------------------------------+------+------------------------+-----------+------------------+--------------+--------------------------+-------------+
| 214140 | 11 | 1700555 | 0.10000 | 1 | 1 | 0.15000 | 1 | 1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 | 0 | 0 | 0.00000 | 0.00000 | 0 | 0 | 0 | 0 | 0 | 0 | 2011-09-10 07:07:37 | 2021-09-10 07:07:37 | 0 | 10079 | 8 | | -1 | 0 | 0 | 0.00000 | 0 | NULL | 0 | 0 | 0 | 0.00000 | 1.000 | 1700555 |
| 214141 | 11 | 17005552 | 0.30000 | 1 | 1 | 0.30000 | 1 | 1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 | 0 | 0 | 0.00000 | 0.00000 | 0 | 0 | 0 | 0 | 0 | 0 | 2011-09-10 07:09:22 | 2021-09-10 07:09:22 | 0 | 10079 | 8 | | -1 | 0 | 0 | 0.00000 | 0 | NULL | 0 | 0 | 0 | 0.00000 | 1.000 | 17005552 |
| 214142 | 11 | 17005554 | 0.10000 | 1 | 1 | 0.10000 | 1 | 1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 | 0 | 0 | 0.00000 | 0.00000 | 0 | 0 | 0 | 0 | 0 | 0 | 2011-09-10 07:10:53 | 2021-09-10 07:10:53 | 0 | 10079 | 8 | | -1 | 0 | 0 | 0.00000 | 0 | NULL | 0 | 0 | 0 | 0.00000 | 1.000 | 17005554 |
+--------+--------------+------------+---------+------------------+------------------+-------------+-----------+--------------+---------------+------------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+---------------------+---------------------+-----------+---------+----------+-------------+----------------------+-------------------+--------------------+-------------------------+------------------------------+------+------------------------+-----------+------------------+--------------+--------------------------+-------------+
3 rows in set (0.00 sec)


It looks like only the exact match on dialprefix was even considered.

A BETTER merge might be forth coming... Check for updates here and the github.




PSST you can do this to see if a carrier is profitable for a given call for given customer, no merge required.

SELECT CUST.dialprefix AS 'CUST.dialprefix' , CUST.id AS 'CUST.id' , CUST.idtariffplan AS 'CUST.idtariffplan' , CUST.rateinitial AS 'CUST.rateinitial', CARR.dialprefix AS 'CARR.dialprefix' , CARR.id AS 'CARR.id', CARR.idtariffplan AS 'CARR.idtarrifplan' , CARR.rateinitial AS 'CARR.rateinitial' FROM cc_ratecard CUST JOIN cc_ratecard CARR WHERE CUST.idtariffplan = 11 AND CARR.idtariffplan=10 AND 17005551122 LIKE CONCAT ( CUST.dialprefix , '%' ) AND 17005551122 LIKE CONCAT ( CARR.dialprefix , '%' ) ORDER BY ( CUST.dialprefix ) DESC , LENGTH ( CARR.dialprefix ) DESC LIMIT 1;
+-----------------+---------+-------------------+------------------+-----------------+---------+-------------------+------------------+
| CUST.dialprefix | CUST.id | CUST.idtariffplan | CUST.rateinitial | CARR.dialprefix | CARR.id | CARR.idtarrifplan | CARR.rateinitial |
+-----------------+---------+-------------------+------------------+-----------------+---------+-------------------+------------------+
| 1700555 | 214140 | 11 | 0.15000 | 170055511 | 214139 | 10 | 0.12000 |
+-----------------+---------+-------------------+------------------+-----------------+---------+-------------------+------------------+
1 row in set (0.00 sec)


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 1 post ] 
VoIP Billing solution


All times are UTC


Who is online

Users browsing this forum: No registered users and 6 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