I need a quick mysql solution for the following (please review table structures, see attached file):
There are several records in table "cdr" (around 100 new rows in every minute).
In every 5 minutes I need to determine the costs per call.
See the first row in cdr:
9256749931 | 10.0.0.1 | 12010320313 | [url removed, login to view]
it means 9256749931 called 12010320313, caller's source ip was 10.0.0.1, the destination gateway was [url removed, login to view]
Here are the steps to make it working:
select dst_username and dst_domain from the cdr table, and then look for the grp_id in gw table by IP.
Select the grp_id from the gw table and search for lcr based on the grp_id and the first 7 chars of the dst_username(remember, dst_username was selected form cdrs), the first 7 chars will be the prefix in lcr table.
Then select that prefix and search for destinations in the rates table (so lcr.prefix=[url removed, login to view] to search for).
select durationRate,durationRateIn,increment and minduration from the rates, and then do the following:
there are increment and minduration, so values should be rounded.
if the duration in cdrs table contains 13, then that should rounded to the next 6, so 13 will become 18 (remember, 6 seconds increments).
If the duration in cdrs are below 6, then it should be rounded to 6.
If the increment and min_duration is 1 and 1 in rates table, then it shouldn't be rounded to the next 6.
so there will be duration_6 and minduration_6 columns, so you will need to update that an don't touch the original duration.
you will need to update price_in, and price_out column in the cdrs table:
price_in will be [url removed, login to view]*[url removed, login to view][rounded to next 6 if there is increment 6 in the rates table for the prefix] / 10000
price_out will be [url removed, login to view]*[url removed, login to view][rounded to next 6 if there is increment 6 in the rates table for the prefix] / 10000
note: my comments are between [ and ]
if you done with a row in cdrs table, then update processed=1 in the specific row in cdrs table, make sure that won't be processed again.
I'll need a script for this, it will run from command line by a cronjob (every 5 mins), so you should add new indexes if needed, there can be 100mill rows in the cdrs table, so table scans should be avoided.
I'll need the solution within 2 hours from now, payment will be SL escrow after selecting the right provider for this project.
If you have any questions, please don't hesitate to ask.
Thanks and happy bidding :)