Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Detecting Phone operator from phone number

 
Houssem Jlassi
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi everyBody,
I need to develop an application that calculate the fee of the VOIP in my entreprise.
I have : One table with the phone number and the duration of the call // and the second table with the prefix of the country and the call fee per minute.
So, I have already post this problem before, but I didn't get an aswser.

I have some idea that I want to share with you :

first of all, I can find the country from the phone number by making this procedure :
UPDATE TABLE
SET country =
CASE
WHEN left(Phonenumber,4) = 0093 THEN 'Afghanistan'
WHEN left(Phonenumber,5) = 00355 THEN 'Albania'
WHEN left(Phonenumber,5) = 00213 THEN 'Algeria'
WHEN left(Phonenumber,5) = 00376 THEN 'Andorra'
WHEN left(Phonenumber,5) = 00244 THEN 'Angola'
WHEN left(dst,3) = 007 THEN 'Russia
...
ELSE ''
END

Now I need to find also the operator : for exemple, 00216 ==> Tunisia // 002167 ==>Tunisia TELECOM // 002165 ==> Orange Tunisia ...

Some idea Please, I need that for my JOB...I'm blocked
 
Campbell Ritchie
Sheriff
Posts: 48445
56
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If Russia is 007, is there a 0071 and 00712?
But inside a country, there may be no correlation between the phone number and the operating company. In Britain, you can only identify the company from the phone number for mobiles, and even that changes as companies merge or trade network space. You may be attempting an impossible task.

By the way, which language is that case statement in? It doesn’t look like Java.
 
Henry Wong
author
Marshal
Pie
Posts: 20902
76
C++ Chrome Eclipse IDE Firefox Browser Java jQuery Linux VI Editor Windows
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

And doesn't this depend on the data in the database? Meaning how you do it will depend on how the data is organized. You already did a prefix check to find the country. Then I would assume, if you have the operator code in another table, you can remove the country code, and do a prefix check to find the operator (using the country to get to the correct table, or course).

Henry
 
Michael Krimgen
Ranch Hand
Posts: 35
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

Looks like you are using Oracle. So where exactly is the connection to Java?

However, you need to calculate the fees and have two tables, one with the the call details and another with the prefix + fee per minute. When you join the two tables on the prefix of the number,
you should be able to calculate the fee for each call. Then you can sum up all fees. Why ecatly do you need
to operator for that?
 
Campbell Ritchie
Sheriff
Posts: 48445
56
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Michael Krimgen wrote: . . . Looks like you are using Oracle. . . .
So was that PL/SQL?
 
Winston Gutkowski
Bartender
Pie
Posts: 10111
56
Eclipse IDE Hibernate Ubuntu
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Campbell Ritchie wrote:So was that PL/SQL?

Looks like it. It always amazes me that people who are so brilliant at figuring out complex data structures and relationships could come up with such a God-awful language, but there you go. It has to be said that SQL itself is no Page 3 model either.

Winston
 
Houssem Jlassi
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks everyBody for these amazing answer...
This code is a PL/SQL Block...All I need is to calculate the fee , but I can't make a join between the Two tables cause I didn't find yet how to determine the operator.
I should explain more my problem :

This PL/SQL procedure gonna make my work easier, cause if I find the country then I can limit the number of choice of the operator.
I need JAVA in my program cause I'm developin a dynamic web project to explore these data, to making report and chart (with iRport and jFreeChart)...

I made some bonus work with Stocked Procedure to make my JAVA CODE more easier, that's all.

Now, what I need to do is :
For every phone number, verify the country then verify the operator, and that's it ! than I can calculate the fee and make any other operation on the data (Search, reporting...).

As you can see, the lenght of the prefix is varaible, and this is the difficulty , cause if it was constant i can fix all this with the method STARTWITH of the String Class.
So I need to have the length of the prefix, than verify if the phone number start with this preifx ;else continue to the second number...when I finish with the first prefix , I need to verify with the next prefix...so the algorithm will look like this :

Variable prefix_length == prefix(i).lenght();
if (phone_number.startwith(prefix, prefix_lenght)) than fee = fee(i)
else
continue
...

I hope that the problem is more clear now...

Sorry cause my english is now awsome

Waiting for your ideas

Best & Regards





 
Ulf Dittmer
Rancher
Pie
Posts: 42967
73
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think you missed the main point: the task you set out to do is impossible in the general sense. It may have been possible in the early days of phone -there may once have been a 1:1 relationship between a block of phone numbers with a certain prefix and a certain carrier- but it's not any more. As people move from carrier to carrier, they take their phone numbers with them, removing the link between phone number and carrier.
 
Houssem Jlassi
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ulf Dittmer wrote:I think you missed the main point: the task you set out to do is impossible in the general sense. It may have been possible in the early days of phone -there may once have been a 1:1 relationship between a block of phone numbers with a certain prefix and a certain carrier- but it's not any more. As people move from carrier to carrier, they take their phone numbers with them, removing the link between phone number and carrier.


But What I need to do exist...for the VOIP operator who need to calculate the invoice of their client...They have the cost of the call, they have the call list..and then they give you the total...That's what I need to do...
 
Houssem Jlassi
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have an idea but I need your help :
What about using the Regular expression ?
for exemple : Russia : 0{0,3}7[0-9]* and Russia mobile : (0{0,3}7407[0-9]*|0{0,3}7810[0-9]*|0{0,3}79[0-9]*)
United Kingdom : 0{0,3}44[0-9]* and United Kingdom Mobile : (0{0,3}4475[0-9]*|0{0,3}4477[0-9]*|0{0,3}4478[0-9]*|0{0,3}4479[0-9]*|[0-9]*)

...
But here also I need your help to accomplish this job ...

Best & Regards
 
Ulf Dittmer
Rancher
Pie
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But What I need to do exist...for the VOIP operator who need to calculate the invoice of their client...

Right: they have APIs to hook directly into other phone networks. If you can arrange for that, your code can do the same. I wouldn't imagine that the carriers would let you, though.
 
Winston Gutkowski
Bartender
Pie
Posts: 10111
56
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Houssem Jlassi wrote:But What I need to do exist...for the VOIP operator who need to calculate the invoice of their client...They have the cost of the call, they have the call list..and then they give you the total...

Because they are party to information obtained directly from the supplier that they have a contract with. If they are willing to give it to you, it is highly likely to cost, unless required by a court. Or are you simply trying to model a cellnet charge algorithm?

Winston
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic