[asterisk-users] Rates Importer Tool

[A E [Gmail]]

On Mon, May 9, 2011 at 7:58 PM, Markus <universe at truemetal.org> wrote:

> Hi,
>
> > new to the list. Wondering if anyone has / knows of, a good rate importer
> > tool that can be used to standardize and normalize the ratesheets / rate
> > decks etc. obtained from various carriers so they can be analysed and
> > imported into a DB or be saved as a CSV or something?
>
> I'm using a2billing (http://www.a2billing.org), a free of charge and
> complete call shop web-based PHP application for Asterisk. Very buggy
> overall but I couldn't find anything better (which is free of charge) yet.
> Anyway, it gets the job done. I'm uploading the rate sheets to a Linux box
> respectively download them directly to the box and then use a shell script
> for each provider's rate sheet to properly order to fit into the a2billing
> format, a la:
>
> wget http://www.provider.com/rates/premium.csv
> cat premium.csv | grep \"1\",\"1\" > temp.csv
> cat temp.csv | cut -d "," -f 3 > tempcode.csv
> cat temp.csv | cut -d "," -f 1 > tempdest.csv
> cat temp.csv | cut -d "," -f 6 > temprate.csv
> paste -d "," tempcode.csv tempdest.csv temprate.csv temprate.csv | tail
> -n+2 | sed 's/^\"/\"00/g' > Provider.PREMIUM.$DATE.csv
> unix2dos Provider.PREMIUM.$DATE.csv
> scp -P 450 Provider.PREMIUM.$DATE.csv user at athome.dyndns.org:samba/_rates/
> rm temp.csv tempcode.csv tempdest.csv temprate.csv premium.csv
>
> This fetches and orders the rate sheet properly and uploads it to my home.
> Then I just log into a2billing and upload the rate sheet there, done with
> a few clicks. But you could also create a new ratecard directly in MySQL
> and store the rates there directly if you want to. a2b stores all rates in
> a MySQL DB. You can then choose least cost routing between different
> providers etc.
>
> Also, when a provider only supplies XLS instead of CSV, I use a script
> like the following, utilizing "xlhtml":
>
> xlhtml -csv -xp:0 Provider.xls | cut -d "," -f 1-3 > temp.csv
> cat temp.csv | cut -d "," -f 3 > temp2.csv
> paste -d "," temp.csv temp2.csv | tail -n+3 | sed 's/^/00/g' >
> Provider.PREMIUM.$DATE.csv
> unix2dos Provider.PREMIUM.$DATE.csv
> scp -P 450 Provider.PREMIUM.$DATE.csv user at athome.dyndns.org:samba/_rates/
> rm temp.csv
> rm temp2.csv
>
> Regards.
>
> Hello Markus,
thanks for sharing. I am looking into A2Billing myself at the moment. Don't
really need most of the functionality in it, but will check out its rates
import tool although I'm not sure it can handle rate updates but seems like
something to check out. Although like I'd said in my OP, this is mostly for
the business people to be able to visualize the rates and analyse them them
more than anything else and judging from the extra hacking involved in
getting these rates to be ready to be imported into A2Billing even seems too
complicated for the business people be able to do on their own, and I don't
want to have to sit and normalize it for them every time there's a rate
update. But will look more into this. Thanks again for putting up your
script and trying to help out :)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.digium.com/pipermail/asterisk-users/attachments/20110509/1ed03591/attachment-0001.htm>