Campaign/Contacts Cycle + 1

[tj@estreet.com]

Hey All!
I have added a field to my contacts table named 'cycle' (int) with default of '0' intended to track number of times a given contact has been -- well ... contacted. I've managed to set this field up to massupdate from list view (text field), and to update from Contacts/EditView, but was hoping someone could offer a suggestion or two on how to have this field (contacts.cycle) updated for each contact when sent email -- especially from a campaign (+1) ??? I'm assuming this would reqire editing my EmailMan\EmailManDeliver.php, but I'm at a loss as to what said edits would look like??? Any input would be greatly appreciated.

Sugar OS 4.0.1f

[tj@estreet.com]

Surely SOMEBODY can explain why the following in EmailManDelivery.php won't update my "cycle" field in contacts table???

 if (isset($_REQUEST['send_all']) && $_REQUEST['send_all']== true) {
    $send_all= true;
    $update_cycle =" UPDATE contacts,emailman set contacts.cycle=contacts.cycle+1 where emailman.related_id = contacts.id and emailman.deleted !=1 ";        
} 
  

I know it's a valid sql statement. I ran the query in phpMyAdmin before the schedular ran and cleared out emailman and it worked just as expected. Further, the php looks okay in Zend development studio, but WHY won't it execute out of EmailManDelivery.php ??????? What am I overlooking????

[simple]

Hi tj

I'm not sure I understand your question but how/where do you execute the update_cycle sql statement?

[tj@estreet.com]

Thanks Pascal

Assuming the php/sql update syntax is correct, I'm not sure if it should be included in EmailManDelivery.php, or possibly even TrackDetailView.php, but in either case, I can't figure out where within eiter file to position it. I have tried moving it all over in both php files with no luck. I have even tried adding the following at the top of TrackDetaiView.html hoping it would execute onload.

 <?php
$cycle_query =" UPDATE contacts,emailman set contacts.cycle=contacts.cycle+1 where emailman.related_id = contacts.id and emailman.deleted !=1 "; 
$result = @cycle_query ($cycle_query);
if ($result) { $alerts='Contact Cycle Updated';
} else {$message = 'Cycle did not update' . mysql_error();}
?>  

To reiterate, the goal is to have my contacts.cycle field updated for each recipient of an email campaign, and it seemed to me that the EmailMan queue was the logical place to reference?

[simple]

Hello

I guess it should be "@mysql_query" instead of "@cycle_query"? Or did I miss something?

You could try using the Sugar db object. Maybe it helps?

  if (isset($_REQUEST['send_all']) && $_REQUEST['send_all']== true) {
    $send_all= true;
    $update_cycle =" UPDATE contacts,emailman set contacts.cycle=contacts.cycle+1 where emailman.related_id = contacts.id and emailman.deleted !=1 ";        
    
    $db = & PearDatabase::getInstance();
    $result = $db->query($update_cycle);
} 
  

[tj@estreet.com]

Yes Pascal,
You are absolutely correct. It should be "@mysql_query" instead of "@cycle_query". Still...

In TrackDetailView.html I tried:

 <?php  
$cycle_update =" UPDATE contacts,emailman set contacts.cycle=contacts.cycle+1 where emailman.related_id = contacts.id and emailman.deleted !=1 ";   
$result = @mysql_query ($cycle_update);  
if ($result) { $alerts='Contact Cycle Updated'; } 
else {$message = 'Contact Cycles WERE NOT UPDATED' . mysql_error();}  
?>  

and...

 <php 
$cycle_update =" UPDATE contacts,emailman set contacts.cycle=contacts.cycle+1 where emailman.related_id = contacts.id and emailman.deleted !=1 ";  
$result = mysql_query ($cycle_update) or die(mysql_error()); 
?> 
 

...and in EmailManDelivery.php I tried

 if (isset($_REQUEST['send_all']) && $_REQUEST['send_all']== true) { 
    $send_all= true; 
    $update_cycle =" UPDATE contacts,emailman set contacts.cycle=contacts.cycle+1 where emailman.related_id = contacts.id and emailman.deleted !=1 ";         
     
    $db = & PearDatabase::getInstance(); 
    $result = $db->query($update_cycle); 
} 
  

but it just won't update, and I know, having opened and run it in phpMyAdmin while contacts were still in emailman queue, that the following query works:

UPDATE contacts,emailman
SET contacts.cycle=contacts.cycle+1'
WHERE emailman.related_id = contacts.id'
AND emailman.deleted !=1

[tj@estreet.com]

For the sake of closure, here's my solution.

Added functionIn campaings.php

 function contact_cycle() {  
    $update_cycle =" update contacts,emailman set contacts.cycle=contacts.cycle+1 where emailman.related_id = contacts.id and emailman.deleted !=1 ";          
    $db = & PearDatabase::getInstance();  
    $result = $db->query($update_cycle);  
} 
  

and called function at end of TrackDetailView.php "contact_cycle();' and it works! Thanks Pascal for the input!!

[simple]

Great!

I did some testings too. I don't need this feature but I was curious.

I think the problem in your first try was that you put it in between

 if (isset($_REQUEST['send_all']) && $_REQUEST['send_all']== true) {  
...
} 
  

On about line 75 the following code is in the orignal file:

 $db = & PearDatabase::getInstance();
$emailman = new EmailMan();
 
$select_query =" SELECT *";
$select_query.=" FROM $emailman->table_name";
$select_query.=" WHERE send_date_time <= '". gmdate('Y-m-d H:i:s') . "'";
$select_query.=" AND (in_queue ='0' OR ( in_queue ='1' AND in_queue_date <= '" .gmdate('Y-m-d H:i:s', strtotime("-1 day"))."'))"; 
  

I've added a similar update statement bevor the select statement and it worked. (I don't have the cycle column, therefore I've used the description field)

 $emailman = new EmailMan();
 
// update before sending
$update_cycle = " UPDATE contacts, emailman set contacts.description='email sent 2' where emailman.related_id = contacts.id and emailman.deleted !=1 ";
$update_cycle .= " AND emailman.send_date_time <= ". db_convert("'".gmdate('Y-m-d H:i:s')."'" ,"datetime");
$update_query .=" AND (emailman.in_queue ='0' OR ( emailman.in_queue ='1' AND emailman.in_queue_date <= " .db_convert("'". gmdate('Y-m-d H:i:s', strtotime("-1 day")) ."'" ,"datetime")."))"; 
$result = $db->query($update_cycle);  
 
$select_query =" SELECT *"; 
  

Just as an info.

[tj@estreet.com]

VERY NICE!!!
Thank you Pascal!