Vicidial Database Structure

From ViciWiki
Revision as of 03:44, 17 August 2013 by Bill (talk | contribs) (vicidial_agent_log)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

recording_log

Field Name Description Location
recording_id Primary Key for this table
channel asterisk channel when call was live vicidial/admin_modify_lead.php?lead_id=####
server_ip IP address of server user is connected to
extension could be callerid of the client or extension call was ultimately sent to
start_time start time of call
start_epoch start time of call in unix epoch format
end_time start time of call
end_epoch end time of call in unix epoch format
length_in_sec length of call in seconds
length_in_min length converted to minutes decimal format
filename without extension to allow use as audio playback in asterisk
location url to listen to recording note that this is modified via regex in some interfaces IF still located on the original server, it is modified to the URL provided during ftp transfer if already moved to the archive server
lead_id if linked to a lead
user agent's user id
vicidial_id could be vicidial_closer_log_id or uniqueid from asterisk



vicidial_agent_log

Field Name Description Location
agent_log_id Primary Key for this table
user User Name ID field vicidial/AST_agent_time_detail.php
server_ip IP address of server user is connected to vicidial/AST_usergroup_login_report.php
event_time Time logged for start of event or call
lead_id link to vicidial_list.lead_id
campaign_id Campaign user is logged into /vicidial/AST_agent_time_detail.php
pause_epoch start of agent pause time
pause_sec length in second of pause time vicidial/AST_agent_performance_detail.php
wait_epoch start of agent wait time
wait_sec total length in seconds of wait time vicidial/AST_agent_performance_detail.php
talk_epoch start of agent talk time
talk_sec total agent talk time per call vicidial/AST_agent_performance_detail.php
dispo_epoch start of disposition time
dispo_sec total amount of time to disposition a call vicidial/AST_agent_performance_detail.php
status Call dispositioned at the end of the call this can be changed by /vicidial/admin_modify_lead.php?lead_id=
user_group call group user is assigned to can be located at vicidial/admin.php?ADD=100000
comments Call comments inputted /vicidial/admin_search_lead.php
sub_status
dead_epoch start of dead time dead time is after lead has hung up but agent has not pressed "hangup call" on screen
dead_sec length of dead time in seconds vicidial/AST_agent_performance_detail.php
processed
uniqueid Identifier for phone call, generated by Asterisk at the onset of call

vicidial_closer_log

Field Name Description Location
closecallid closed call table Primary key
lead_id ID of Lead vicidial/admin_modify_lead.php?lead_id=
list_id ID of List vicidial/admin_modify_lead.php?lead_id=
campaign_id Alpha Numeric Campaign ID from Vicidial Campaigns
call_date Date and Time call was made
start_epoch Start of Call Time
end_epoch End of Call Time
length_in_sec Total Length of call in seconds vicidial/AST_agent_performance_detail.php
status what happpened to the call Samples: AFTHRS = did not match call times for an Ingroup or Call Menu; NANQUE = no agent was available
phone_code from lead record vicidial/admin_modify_lead.php?lead_id=
phone_number from lead record vicidial/admin_modify_lead.php?lead_id=
user User ID vicidial/AST_agent_time_detail.php
comments Explanation related to Status in this table More information related to status: for instance: "After Hours Drop" when status is "AFTHRS".
processed
queue_seconds Wait time before arriving at an agent
user_group user group of agent who handled this call record
xfercallid
term_reason Call Termination Reason Samples: AGENT; ABANDON; CALLER
uniqueid Unique ID assigned by asterisk
agent_only
queue_position

vicidial_list

Most of these fields are visible in /vicidial/admin_modify_lead.php?lead_id={LEAD_ID} and/or on the agent screen. If so, they are marked "admin_modify_lead" or "agent screen" (or "both").

Field Name Description Location
lead_id Unique ID for leads, incrementing admin_modify_lead
entry_date Date/time lead imported
modify_date Date/time of last modification Enforced by mysql code
status Disposition/Status (NEW, DROP) admin_modify_lead
user vicidial user admin_modify_lead
vendor_lead_code vendor id both
this field is used to link "multi-lead" alt phones together and is also often used to link to external databases such as CRMs
source_id used to link to external databases as either a unique record or a category
list_id List containing the lead_id Used to link the lead to a campaign.
gmt_offset_now Time Zone Calculated during import and again nightly with perl script
called_since_last_reset Has the lead been called Resetting this value to Zero allows the lead to be called again. Cannot be called if set to 1.
phone_code Dial Code (1 in US, or could be 44 or 01144 for UK) both
phone_number Phone Number both
title Title both
first_name First name both
middle_initial Middle Initial both
last_name Last name both
address1 Primary Street Address both
address2 Address 2 both
address3 Address 3 both
city City both
state State both
province Province if outside CUSA both
postal_code Postal(ZIP) Code both
country_code Country admin_modify_lead
used to assist in GMT calculation. Two character code. ie: US or CA
gender Gender both
date_of_birth Birthdate admin_modify_lead.php
alt_phone Second Phone Number both
email email both
security_phrase Labeled as "Show" in agent screen both
comments Comments both
called_count Number of times this lead has been called
last_local_call_time date and time of the last call
rank admin_modify_lead
owner Name of user that can dial lead if configured for owner dialing admin_modify_lead
entry_list_id original list id during import if using custom fields, this is the "home" list id where those fields are kept.
If the lead is moved between lists, this field must be populated to link to the field structure (and custom field record).

vicidial/admin_modify_lead.php

vicidial_log

Field Name Description Location
uniqueid unique id for record
lead_id Lead Identifier /vicidial/admin_search_lead.php
list_id List Name vicidial/admin.php?ADD=100
campaign_id Campaign Name vicidial/admin.php?ADD=10
call_date Call Date and Time /vicidial/user_stats.php?user=
start_epoch Start of Call
end_epoch End of Call
length_in_sec Length of Call in seconds /vicidial/user_stats.php?user=
status Dispositioned Status given at end of call Note this may no longer be the status for the lead, it may change.
phone_code Phone Dial Code (IE "1") /vicidial/admin_modify_lead.php?lead_id=
phone_number Phone number /vicidial/admin_modify_lead.php?lead_id=
user User Name or ID vicidial/AST_agent_performance_detail.php
comments MANUAL or AUTO (or NULL)
processed unused?
user_group User Group of agent on call vicidial/admin.php?ADD=100000
term_reason Who terminated the call, Caller or Agent 'CALLER','AGENT','QUEUETIMEOUT','ABANDON','AFTERHOURS','NONE'
alt_dial field name if called using alternate phone number field ie: alt_phone or address3