Export Call Report- SQL Query

[ahmad1710]

Hi, our call center is using Vicidial and we frequently use "Export_Call_Report". I needed SQL Query to fetch this data automatically for my Power Bi dashboard. I have attached my query, but it is resulting in fewer records, as compared to "Export-Call-Report.php". Any help will be appreciated.

Code: Select all

SELECT vl.call_date,vl.phone_number,vl.status,vl.user,vu.full_name,vl.campaign_id,vi.vendor_lead_code,vi.source_id,vi.list_id,vi.gmt_offset_now,
vi.phone_code,vi.phone_number,vi.title,vi.first_name,vi.middle_initial,vi.last_name,vi.address1,vi.address2,vi.address3,
vi.city,vi.state,vi.province,vi.postal_code,vi.country_code,vi.gender,vi.date_of_birth,vi.alt_phone,vi.email,vi.security_phrase,vi.comments,
vl.length_in_sec,vl.user_group,vl.queue_seconds,vi.rank,vi.owner,vi.lead_id,vl.closecallid,vi.entry_list_id,vl.uniqueid$export_fields_SQL from vicidial_users vu,vicidial_closer_log vl,
vicidial_list vi where vl.call_date
>= '$query_date 00:00:00' and vl.call_date <= '$end_date 23:59:59' and vu.user=vl.user and vi.lead_id=vl.lead_id $list_SQL $group_SQL $user_group_SQL $status_SQL
order by vl.call_date limit 100000;


[williamconley]

1) Welcome to the Party!

2) As you are obviously new here, I have some suggestions to help us all help you:

When you post, please post your entire configuration including (but not limited to) your installation method (7.X.X?) and vicidial version with build (VERSION: 2.X-XXXx ... BUILD: #####-####).

This IS a requirement for posting along with reading the stickies (at the top of each forum) and the manager's manual (available on EFLO.net, both free and paid versions)

You should also post: Asterisk version, telephony hardware (model number is helpful here), cluster information if you have one, and whether any other software is installed in the box. If your installation method is "manual/from scratch" you must post your operating system with version (and the .iso version from which you installed your original operating system) plus a link to the installation instructions you used. If your installation is "Hosted" list the site name of the host.

If this is a "Cloud" or "Virtual" server, please note the technology involved along with the version of that techology (ie: VMware Server Version 2.0.2). If it is not, merely stating the Motherboard model # and CPU would be helpful.

Similar to This:

Vicibox X.X from .iso | Vicidial X.X.X-XXX Build XXXXXX-XXXX | Asterisk X.X.X | Single Server | No Digium/Sangoma Hardware | No Extra Software After Installation | Intel DG35EC | Core2Quad Q6600

3) "... it is resulting in fewer records ..." OK: I'll byte. How many records does this result in? And how many were you expecting? Or is that a secret?

4) "$list_SQL $group_SQL $user_group_SQL $status_SQL" these are variables. This won't work in SQL. Which means you didn't actually show us the query you ran, but something else. Perhaps posting the actual query would help. However, removing these entirely would result in MORE records ... so did you run it without these? In which case, once again, you posted something technically other than the actual query you used while requesting technical support. Slightly counterintuitive.

5) "... limit 100000;" I'd suggest removing this, but I hav no idea if you were expecting 10 records and only got 5 or 2 million records and only got 100000.

6) Alternately, you could just POST your request to /vicidial/call_report_export.php just like the form does and not have to code at all to get your data. Your response will be in CSV instead of SQL, but it's not particularly difficult to parse CSV.

[ahmad1710]

Yes, it was my first time posting on this forum. Thanks for your help. Took your advice in point 6 and it works like charm.