[Spce-user] Query for report

[Jonathan Scott]

Michael,
What you're trying to do is just honestly not going to get much support 
from the user lists, mainly because interacting directly with the SQL 
database is not recommended, and is suggested that networking is 
disabled on the SQL to increase the performance of other applications.

All of the information is surely there though, but like I said you're 
sort of on your own there. If you'd like to try and create a PHP 
frontend to another database that you populate with information provided 
from the Provisioning server and the CDR Export methods, this is highly 
suggested and more efficient. Infact, it makes more sense to start off 
this way and populate your Provisioning server with information from 
your customer database. :)

This is how it's done here at XATel, if you need more information on 
this perhaps we can talk off list because it's not directly related to 
anything that will help anyone else out here.

-- 
Jonathan Scott Knox
Systems Administrator
XATel Communication
7 Green Street, Biddeford ME. 04005
(207)391.1000 (Mon - Thur, 9AM - 6PM. Friday - 9AM - 5PM.)



On 10/17/2011 4:50 AM, Michael Molina wrote:
> Trying to create a query that will show the shopuser (as account 
> number), customer name, customer last name, customer street 
> address, city and zip code. It is important to fetch also the values 
> for the phone numbers associated with this account as customers can 
> have several phone numbers (subscribers) linked to them. I am using 
> the program Navicat Premium to connect to the mySQL server and hence 
> being able to create the query and from the query create the report. I 
> have made a query by using the tables "customers" field "contact_id" 
> and "contracts" field "id" from the database "billing", if you join 
> them and run the query you get the information required but I can not 
> find a way to fetch the DIDs associated with each account. I looked 
> everywhere, "provisioning", "kamailio" even accounting but that one 
> only shows the cdrs. I believed I could have gotten what we need in 
> the "billing" database table "voip_numbers" but to no avail. The idea 
> is perhaps to select for instance, a "shopuser" and get the CDRs for 
> that customer, weather he has one or ten phone numbers. Maybe for 
> billing purposes or traffic control even load control. It does not 
> matter, the point is being able to create a report that can be printed 
> and possibly select inbound and outbound traffic for a specific 
> customer. I would really love and appreciate some help in the matter. 
> Thank you all.
>
> Michael Molina
>
>
> _______________________________________________
> Spce-user mailing list
> Spce-user at lists.sipwise.com
> http://lists.sipwise.com/listinfo/spce-user


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sipwise.com/pipermail/spce-user_lists.sipwise.com/attachments/20111017/b93d0d44/attachment-0001.html>