Adding Daily Called Count to vicidial_list

[ajax1515]

I'm having a little trouble with something I'm trying to do. We want to be able to keep track of how many times a lead is called per day rather then simply over a life time. What I've done is add a column to vicidial_list called daily_called_count.

I then updated queries in all the scripts I could find that updated the called_count column... I've tested my query and I know it works: e.g. $stmt = "UPDATE vicidial_list set status='$stage',daily_called_count = (daily_called_count + ('$called_count'-called_count)), called_count='$called_count',user='$user' where lead_id='$lead_id';";

Problem is it doesn't seem to ever be happening... So I can only conclude that I must have missed an important script somewhere that does updates of called_count...

Here are the scripts I've modified the queries in:

Code: Select all

AST_VDauto_dial.pl
AST_VDauto_dial_FILL.pl
VICIDIAL_rebuild_list_statuses.pl
vcd_db_query.php

I also proposed simply adding a trigger like so:

Code: Select all

DELIMITER $$
CREATE TRIGGER `increment_daily_called_count` BEFORE UPDATE ON `vicidial_list`
   FOR EACH ROW begin
      if (NEW.called_count != OLD.called_count) then
         set NEW.daily_called_count = OLD.daily_called_count+(NEW.called_count-OLD.called_count);
      end if;
   end
$$
DELIMITER ;

but my boss is concerned that the trigger will add too much overhead.

So two questions:
1. What am I missing?
2. Is the trigger solution valid or is my boss correct in his concerns?

[mflorell]

We don't recommend using triggers, especially on the vicidial_list table.

As to why your changes aren't working, there could be several reasons for that. I can't really offer any suggestions without seeing the code.

[ajax1515]

Relavant code snippits:

This is AST_VDauto_dial.pl --- Starting at line : 1139

Code: Select all

...
if ( ($alt_dial =~ /ALT|ADDR3|X/) && ($DBIPautoaltdial[$user_CIPct] =~ /ALT|ADDR|X/) )
{
   if ( ($alt_dial =~ /ALT/) && ($DBIPautoaltdial[$user_CIPct] =~ /ALT/) )
   {
      $alt_phone =~ s/\D//gi;
      $phone_number = $alt_phone;
   }
   if ( ($alt_dial =~ /ADDR3/) && ($DBIPautoaltdial[$user_CIPct] =~ /ADDR3/) )
   {
      $address3 =~ s/\D//gi;
      $phone_number = $address3;
   }
   if  ( ($alt_dial =~ /X/) && ($DBIPautoaltdial[$user_CIPct] =~ /X/) )
   {
      if ($alt_dial =~ /LAST/)
      {
         $stmtA = "SELECT phone_code,phone_number FROM vicidial_list_alt_phones where lead_id='$lead_id' order by alt_phone_count desc limit 1;";
      }
      else
      {
         $Talt_dial = $alt_dial;
         $Talt_dial =~ s/\D//gi;
         $stmtA = "SELECT phone_code,phone_number FROM vicidial_list_alt_phones where lead_id='$lead_id' and alt_phone_count='$Talt_dial';";                              
      }
      $sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
      $sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
      $sthArows=$sthA->rows;
      if ($sthArows > 0)
      {
         @aryA = $sthA->fetchrow_array;
         $phone_code   =   $aryA[0];
         $phone_number =   $aryA[1];
         $phone_number =~ s/\D//gi;
      }
         $sthA->finish();
      }

   $stmtA = "UPDATE vicidial_list set called_since_last_reset='$CSLR',daily_called_count = (daily_called_count + ('$called_count'-called_count)),called_count='$called_count', user='VDAD',last_local_call_time='$LLCT_DATE' where lead_id='$lead_id'";
   }
   else
   {
      $stmtA = "UPDATE vicidial_list set called_since_last_reset='$CSLR',daily_called_count = (daily_called_count + ('$called_count'-called_count)), called_count='$called_count',user='VDAD',last_local_call_time='$LLCT_DATE' where lead_id='$lead_id'";
   }
$affected_rows = $dbhA->do($stmtA);
...


This is AST_VDauto_dial_FILL.pl starting at line 699

Code: Select all

...
if ( ($alt_dial =~ /ALT|ADDR3|X/) && ($DBIPautoaltdial[$user_CIPct] =~ /ALT|ADDR|X/) )
   {
   if ( ($alt_dial =~ /ALT/) && ($DBIPautoaltdial[$user_CIPct] =~ /ALT/) )
      {
      $alt_phone =~ s/\D//gi;
      $phone_number = $alt_phone;
      }
   if ( ($alt_dial =~ /ADDR3/) && ($DBIPautoaltdial[$user_CIPct] =~ /ADDR3/) )
      {
      $address3 =~ s/\D//gi;
      $phone_number = $address3;
      }
   if  ( ($alt_dial =~ /^X/) && ($DBIPautoaltdial[$user_CIPct] =~ /^X/) )
      {
      if ($alt_dial =~ /LAST/)
         {
         $stmtA = "SELECT phone_code,phone_number FROM vicidial_list_alt_phones where lead_id='$lead_id' order by alt_phone_count desc limit 1;";
         }
      else
         {
         $Talt_dial = $alt_dial;
         $Talt_dial =~ s/\D//gi;
         $stmtA = "SELECT phone_code,phone_number FROM vicidial_list_alt_phones where lead_id='$lead_id' and alt_phone_count='$Talt_dial';";                              
         }
      $sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
      $sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
      $sthArows=$sthA->rows;
      if ($sthArows > 0)
         {
         @aryA = $sthA->fetchrow_array;
         $phone_code   =   $aryA[0];
         $phone_number =   $aryA[1];
         $phone_number =~ s/\D//gi;
         }
      $sthA->finish();
      }

   $stmtA = "UPDATE vicidial_list set called_since_last_reset='$CSLR',daily_called_count = (daily_called_count + ('$called_count'-called_count)), called_count='$called_count',user='VDAD',last_local_call_time='$LLCT_DATE' where lead_id='$lead_id';";
   }
else
   {
   $stmtA = "UPDATE vicidial_list set called_since_last_reset='$CSLR',daily_called_count = (daily_called_count + ('$called_count'-called_count)), called_count='$called_count',user='VDAD',last_local_call_time='$LLCT_DATE' where lead_id='$lead_id';";
   }
...


And lastly vdc_db_query.php Starting at line 3642

Code: Select all

...
if ( ($owner_populate=='ENABLED') and ( (strlen($owner) < 1) or ($owner=='NULL') ) )
   {
   $ownerSQL = ",owner='$user'";
   $owner=$user;
   }
### flag the lead as called and change it's status to INCALL
$stmt = "UPDATE vicidial_list set status='INCALL', called_since_last_reset='$called_since_last_reset',daily_called_count = (daily_called_count + ('$called_count'-called_count)), called_count='$called_count',user='$user',last_local_call_time='$LLCT_DATE'$ownerSQL where lead_id='$lead_id';";
if ($DB) {echo "$stmt\n";}
$rslt=mysql_to_mysqli($stmt, $link);
...


The changes made were simply to the query statements.

[mflorell]

I usually try to keep math out of SQL statements, and in this case, the called_count is being updated in the same statement where you are using it's value, which is probably causing your problems.

[ajax1515]

Welp, I'm dumb.... just needed a server restart for my changes to take effect, all is working now.