CHANGING LEAD STATUS NOT APPEARING IN REPORTS

[dito]

Hi all,
on my vicibox8 i used non api for changing the lead status to 'SALE'

Code: Select all

https://1.1.1.1/vicidial/non_agent_api.php?source=test&user=dito&pass=dito&function=update_lead&search_location=LIST&search_method=PHONE_NUMBER&insert_if_not_found=Y&phone_number=#{CLIDNUM} &status=SALE

the api works and changes the lead status to SALE
doing lead search all is ok i see lead changed as 'SALE'

but on the other side this not change result on reporting like in the Real-Time Whiteboard Report
when agent qualify SALE via the astguiclient it goes ok on reports..
but on the vicidial_sales_viewer.php it can be displayed ... so may be reporting in some reports no checking same tables ..
and this case could the non agent api do this ?

[blackbird2306]

The non_agent_api function update_lead changes status in vicidial_list table. But some reports like Real-Time Whiteboard Report are searching in call log tables "vicidial_log" or "vicidial_closer_log". But there is in non_agent_api a function "update_log_entry", with this function you are able to change also the vicidial_log.

update_log_entry - updates the status of a vicidial_log or vicidial_closer_log entry

NOTE: api user for this function must have user_level set to 8 or higher and "modify leads" enabled

REQUIRED FIELDS-
source - description of what originated the API call (maximum 20 characters)
call_id - either the uniqueid or the 20-character ID of the call
group - either a campaign_id or an in-group group_id
status - the new status for the log record to be set to

[dito]

The non_agent_api function update_lead changes status in vicidial_list table. But some reports like Real-Time Whiteboard Report are searching in call log tables "vicidial_log" or "vicidial_closer_log". But there is in non_agent_api a function "update_log_entry", with this function you are able to change also the vicidial_log.

update_log_entry - updates the status of a vicidial_log or vicidial_closer_log entry

NOTE: api user for this function must have user_level set to 8 or higher and "modify leads" enabled

REQUIRED FIELDS-
source - description of what originated the API call (maximum 20 characters)
call_id - either the uniqueid or the 20-character ID of the call
group - either a campaign_id or an in-group group_id
status - the new status for the log record to be set to

thanks i will try the this and feed back.
non api save from hours of mysql tables exploration !

[blackbird2306]

Another interesting trick to know:
That required field for call_id is "either the uniqueid or the 20-character ID of the call". But the 20-character ID is something like "V2201745250000046636" where "46636" at the end is the lead_id. So it's easily to use the update_log_entry with only lead_id. The part with "V22017452500000" will be truncated so that only lead_id "46636" remains for sql query. The last call log entry with latest date will be changed ! call_id for lead_id "50111" would be then "V2201745250000050111"

[dito]

Another interesting trick to know:
That required field for call_id is "either the uniqueid or the 20-character ID of the call". But the 20-character ID is something like "V2201745250000046636" where "46636" at the end is the lead_id. So it's easily to use the update_log_entry with only lead_id. The part with "V22017452500000" will be truncated so that only lead_id "46636" remains for sql query. The last call log entry with latest date will be changed ! call_id for lead_id "50111" would be then "V2201745250000050111"

ah very nice kind of parsing of the vdad clid !

regret not moving to php when it appeared 25 years ago...
i was so trustful in desktop wares ..

[dito]

well... update lof entry search keyword is based on the call_id ...
it could be easier to put search methd like in the update_lead command .. searching with the phone_number is not possible
i have another inbound call pbx so the purpoce was when i recieve call on the other server
an url will be launched to update the lead status on the vicibox.
i think i will be forced to make a curl or mysql query command on agi for the inbound pbx server to execute an sql update on the vicidial_log table

[blackbird2306]

regret not moving to php when it appeared 25 years ago...
i was so trustful in desktop wares ..

It's never too late !

You are right actually in "update_log_entry" the call_id is a required field and only accepts uniqueid or call_id, but it's very easy to change this function for searching with a valid phone number. So let's try it and make changes in non_agent_api.php (revision 2906 from 2018-02-08):

Code: Select all

if ($function == 'update_log_entry')
{ ...
if (preg_match("/\./",$call_id))
                  {
                  if ($inbound_call > 0)
                     {$stmt="SELECT lead_id,status,closecallid from vicidial_closer_log where campaign_id='$group' and uniqueid='$call_id' order by closecallid desc limit 1;";}
                  else
                     {$stmt="SELECT lead_id,status,uniqueid from vicidial_log where campaign_id='$group' and uniqueid='$call_id';";}
                  }
// START PASTE NEW PART about line 7409 -------------------------
elseif (preg_match("/PHONE/",$call_id))
                  {
                  $phone = substr($call_id,5);
                  if ($inbound_call > 0)
                     {$stmt="SELECT lead_id,status,closecallid from vicidial_closer_log where campaign_id='$group' and phone_number='$phone' order by closecallid desc limit 1;";}
                  else
                     {$stmt="SELECT lead_id,status,uniqueid from vicidial_log where campaign_id='$group' and phone_number='$phone' order by call_date desc limit 1;";}
                  }
// END PASTE NEW PART ---------------------------                     
else
                  {
                  $lead_id = substr($call_id, -10);
                  $lead_id = ($lead_id + 0);


Now it's possible to search by phone_number this way (call_id starts with PHONE) :
http://server/vicidial/non_agent_api.php?source=test&user=dito&pass=dito&function=update_log_entry&group=SALESLINE&call_id=PHONE138373822&status=SALEX

[dito]

It's never too late !

43 years old i feel like a dino ..

thanks for the feed back ! waiting workers break to make an apache2 restart
i may update the webroot with the last svn update.. no database changes with the new updates ?

[blackbird2306]

What is your build and database schema version? You should always update database and run install.pl after updating svn.
Did you try my post with changes in non agent api ?

[dito]

What is your build and database schema version? You should always update database and run install.pl after updating svn.
Did you try my post with changes in non agent api ?

hey, hope you're having a good sunday, peacefully day to make some parsing reading into the code
I did the diff today in the non api and it's perfectly running! thanks !
i am using VERSION: 2.14-650a BUILD: 180111-1544
i did a trunk update svn checkout svn://svn.eflo.net:3690/agc_2-X/trunk
but those lines where not included so i've added them manually.
start to understand the php logic not so far from other coding logic
may be get used to code in separated files/classes mode to get more easy access after.
but the vici code is well commented !
thanks again

[dito]

hi, monday is like a monday !
the non-api working perfectly updating the vicidial_list and vicidial_log statuses to SALE
but the Real-Time Whiteboard Report is only displaying sales statused by the agents ..
big head ache i may have to search into the RTW to see the table he is searching in

[blackbird2306]

Yes, I know there is additionally another table called vicidial_agent_log. We have now 2 different tables vicidial_log and vicidial_agent_log, but the function "update_log_entry" in non agent api only updates the vicidial_log. And the Real-Time Whiteboard Report gathers data from vicidial_log and vicidial_agent_log depending on selected "Report Type". We also get this problem under control with changes in non_agent_api:

Code: Select all

if ($function == 'update_log_entry')
{ ...
else
   {
   $result = 'ERROR';
   $result_reason = "update_log_entry NO RECORDS FOUND";
   $data = "$user|$group|$call_id|$status";
   echo "$result: $result_reason: $data\n";
   api_log($link,$api_logging,$api_script,$user,$agent_user,$function,$value,$result,$result_reason,$source,$data);
   exit;
   }
if ($inbound_call > 0)
   {$stmt="UPDATE vicidial_closer_log SET status='$status' where campaign_id='$group' and closecallid='$uniqueid' order by closecallid desc limit 1;";}
//-----------NEW PART FOR CHANGING BOTH LOG TABLES
elseif (preg_match("/BOTHLOGS/",$source))
   {
   $stmt="UPDATE vicidial_log SET status='$status' where campaign_id='$group' and uniqueid='$uniqueid';";
   $stmt2="UPDATE vicidial_agent_log SET status='$status' where campaign_id='$group' and uniqueid='$uniqueid';";
   $rslt2=mysql_to_mysqli($stmt2, $link);
   }
//-------------END NEW PART FOR CHANGING BOTH LOG TABLES
else
   {$stmt="UPDATE vicidial_log SET status='$status' where campaign_id='$group' and uniqueid='$uniqueid';";}
$rslt=mysql_to_mysqli($stmt, $link);
$update_count = mysqli_affected_rows($link);


Now you have to set get parameter "source" to "BOTHLOGS" and status of both logs will be changed at same time (without source=BOTHLOGS only vicidial_log will be touched):
http://server/vicidial/non_agent_api.php?source=BOTHLOGS&user=dito&pass=dito&function=update_log_entry&group=SALESLINE&call_id=PHONE138373822&status=SALEX

[dito]

morning !
added $rslt=mysql_to_mysqli($stmt, $link); into your parse to execute the first query
i will try the BOTHLOG parse this afternoon and feed you back
actually only lanching the non-api request from the fop2 panel, further when i'll have more time i will try to make sth
more strucred i will share it with you. thanks

Code: Select all

               //-----------NEW PART FOR CHANGING BOTH LOG TABLES
               elseif (preg_match("/BOTHLOGS/",$source))
                  {
                  $stmt="UPDATE vicidial_log SET status='$status' where campaign_id='$group' and uniqueid='$uniqueid';";
                  $stmt2="UPDATE vicidial_agent_log SET status='$status' where campaign_id='$group' and uniqueid='$uniqueid';";
                  $rslt=mysql_to_mysqli($stmt, $link);
                  $rslt2=mysql_to_mysqli($stmt2, $link);
                  }
               //-------------END NEW PART FOR CHANGING BOTH LOG TABLES


[blackbird2306]

No don't add "$rslt=mysql_to_mysqli($stmt, $link);". This will be executed further down always independently from if clause, otherwise it will be executed twice and this is unnecessary load for system.

look here:

Code: Select all

//-------------END NEW PART FOR CHANGING BOTH LOG TABLES
else
   {                          $stmt="UPDATE vicidial_log SET status='$status' where campaign_id='$group' and uniqueid='$uniqueid';";                                  }
$rslt=mysql_to_mysqli($stmt, $link);


[dito]

OMG THOSE NEVER ENDING IMBRICATED IF !! lol sorry ! just saw it :p

No don't add "$rslt=mysql_to_mysqli($stmt, $link);". This will be executed further down always independently from if clause, otherwise it will be executed twice.

look here:

Code: Select all

//-------------END NEW PART FOR CHANGING BOTH LOG TABLES
else
   {                          $stmt="UPDATE vicidial_log SET status='$status' where campaign_id='$group' and uniqueid='$uniqueid';";                                  }
$rslt=mysql_to_mysqli($stmt, $link);


[dito]

PERFECT BRAVO BLACK BIRD
it's working perfectly ! when inbound call come the status is updated
thanks bro !

No don't add "$rslt=mysql_to_mysqli($stmt, $link);". This will be executed further down always independently from if clause, otherwise it will be executed twice and this is unnecessary load for system.

look here:

Code: Select all

//-------------END NEW PART FOR CHANGING BOTH LOG TABLES
else
   {                          $stmt="UPDATE vicidial_log SET status='$status' where campaign_id='$group' and uniqueid='$uniqueid';";                                  }
$rslt=mysql_to_mysqli($stmt, $link);