Call Notes View Associated With Call Record

All installation and configuration problems and questions

Moderators: gerski, enjay, williamconley, Op3r, Staydog, gardo, mflorell, MJCoate, mcargile, Kumba, Michael_N

Call Notes View Associated With Call Record

Postby mstopkey » Wed Nov 01, 2017 3:28 pm

Greetings all. I built a view in the database and a PHP app which shows the call notes for administrative purposes. Records can be searched and downloaded to build a new list or report to the client. My problem is associating the call notes with the call record to obtain the disposition etc. We have a client we receive inbound for and many call are from different employees at companies so therefore the same phone number and lead_id. We identify the record by entering the caller's name in the call notes. We have done this with custom archiving of the vicidial_list and vicidial_lists tables to be sure we keep records.
How can I add the call record to the view to obtain the call details such as disposition (Status), duration etc? Below is the view.

SELECT
`n`.`notesid` AS `notesid`,
`n`.`lead_id` AS `lead_id`,
`n`.`vicidial_id` AS `vicidial_id`,
`n`.`call_date` AS `call_date`,
`n`.`order_id` AS `order_id`,
`n`.`appointment_date` AS `appointment_date`,
`n`.`appointment_time` AS `appointment_time`,
`n`.`call_notes` AS `call_notes`,
`l`.`first_name` AS `first_name`,
`l`.`last_name` AS `last_name`,
`l`.`phone_number` AS `phone_number`,
`l`.`list_id` AS `list_id`,
`r`.`campaign_id` AS `campaign_id`
FROM
(
(
`vicidial_call_notes` `n`
JOIN `vicidial_list` `l` ON (
(
`n`.`lead_id` = `l`.`lead_id`
)
)
)
JOIN `vicidial_lists` `r` ON (
(
`l`.`list_id` = `r`.`list_id`
)
)
)
UNION
SELECT
`n`.`notesid` AS `notesid`,
`n`.`lead_id` AS `lead_id`,
`n`.`vicidial_id` AS `vicidial_id`,
`n`.`call_date` AS `call_date`,
`n`.`order_id` AS `order_id`,
`n`.`appointment_date` AS `appointment_date`,
`n`.`appointment_time` AS `appointment_time`,
`n`.`call_notes` AS `call_notes`,
`l`.`first_name` AS `first_name`,
`l`.`last_name` AS `last_name`,
`l`.`phone_number` AS `phone_number`,
`l`.`list_id` AS `list_id`,
`r`.`campaign_id` AS `campaign_id`
FROM
(
(
`vicidial_call_notes_archive` `n`
JOIN `vicidial_list_archive` `l` ON (
(
`n`.`lead_id` = `l`.`lead_id`
)
)
)
JOIN `vicidial_lists` `r` ON (
(
`l`.`list_id` = `r`.`list_id`
)
)
)
ViciBox 7.0.4 | Version 2.14-694a | SVN Trunk | 2803| Asterisk 11.25.1-vici |Build 181005-1738 | DB Schema 1561 | 7 64bit Telephony servers | 3 64bit Web servers | 1 64bit Database server | 1 64bit Archive server
mstopkey
 
Posts: 180
Joined: Sun Dec 28, 2008 1:01 pm

Return to Support

Who is online

Users browsing this forum: Bing [Bot] and 81 guests