Re: [asterisk-users] Rates Importer Tool
On Mon, May 9, 2011 at 7:58 PM, Markus 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 u...@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 u...@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 :) -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Rates Importer Tool
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 u...@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 u...@athome.dyndns.org:samba/_rates/ rm temp.csv rm temp2.csv Regards. -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Rates Importer Tool
On Mon, May 9, 2011 at 3:05 PM, Jason Aarons (AM) < jason.aar...@dimensiondata.com> wrote: > I know most billing software sell this as a monthly service. You get > cd-rom every month where they have collected the published tarrif tables > filed with the FCC. You load it on the software to analyze call costs. I’m > guessing this is a lot of labor hours/manual work thus they charge for > providing it. In particular I am thinking of InforTel for Windows. > That's interesting. Wasn't aware of such a thing...if these subscriptions ad/or software are reasonably priced then we might still be interested in having a look at it. What specific product of InforTel were you referring to? Thanks -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Rates Importer Tool
I know most billing software sell this as a monthly service. You get cd-rom every month where they have collected the published tarrif tables filed with the FCC. You load it on the software to analyze call costs. I'm guessing this is a lot of labor hours/manual work thus they charge for providing it. In particular I am thinking of InforTel for Windows. From: asterisk-users-boun...@lists.digium.com [mailto:asterisk-users-boun...@lists.digium.com] On Behalf Of A E [Gmail] Sent: Monday, May 09, 2011 2:29 PM To: Commercial and Business-Oriented Asterisk Discussion; Asterisk Users Mailing List - Non-Commercial Discussion Subject: [asterisk-users] Rates Importer Tool Hi All, 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? Thanks so much in advance aeg - Disclaimer: This e-mail communication and any attachments may contain confidential and privileged information and is for use by the designated addressee(s) named above only. If you are not the intended addressee, you are hereby notified that you have received this communication in error and that any use or reproduction of this email or its contents is strictly prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by replying to this message and deleting it from your computer. Thank you.-- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
[asterisk-users] Rates Importer Tool
Hi All, 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? Thanks so much in advance aeg -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
