CDmsd = new Dmsd();
$this->DmsConn = Propel::GetConnection('dms_main');
$this->Conn = Propel::GetConnection();
}
public function LoadNext($CustomerId=0)
{
$CustomerQuery = "";
$ToDoList = array();
if ( $CustomerId > 0 )
{
$CustomerQuery = "AND customer_id = {$CustomerId} ";
}
$Query = "SELECT * FROM dealerx_raw_data_batch_log "
. "WHERE mailing_id = 0 "
. "AND mailing_try_next_date < NOW() "
. "AND batch_date > '2020-11-01 00:00:00'"
. $CustomerQuery
. "LIMIT 1 ";
echo "Query = {$Query}\n";
$Rs = $this->DmsConn->executeQuery($Query);
while($Rs->next())
{
$ToDoList[] = $Rs->getRow();
}
if ( count($ToDoList) == 0)
return false;
foreach($ToDoList as $key => $ToDoRec)
{
echo "ToDoRec = " . print_r($ToDoRec, true) . "\n";
// Set the 'try_next' date in case something blows up.
$TryNextDate = date("Y-m-d H:i:s", time() + 3600);
$Query = "UPDATE dealerx_raw_data_batch_log "
. "SET mailing_try_next_date='{$TryNextDate}' "
. "WHERE dealerx_raw_data_batch_log_id = {$ToDoRec['dealerx_raw_data_batch_log_id']} "
. "LIMIT 1 ";
echo "Query = {$Query}\n";
$Rs = $this->DmsConn->executeQuery($Query);
$CustomerId = $ToDoRec['customer_id'];
$ClientId = array();
$Query = "SELECT client_id FROM customer WHERE customer_id = {$CustomerId} ";
$Rs = $this->Conn->executeQuery($Query);
while($Rs->next())
{
$ClientId = $Rs->get('client_id');
}
$OrderProductId = false;
$JobGroupId = 0;
$MailDate = '';
$MailingId = false;
$RecipientTypeId = 0;
$RecipientListId = 0;
if ( $ToDoRec['trigger_batch_id'] > 0 )
{
$OrderProductId = $this->GetOrderProduct($ToDoRec['customer_id'], $ToDoRec['trigger_batch_id']);
}
if ( $OrderProductId )
{
$Query = "SELECT us2_job_group.us2_job_group_id, us2_order_product.recipient_list_id, us2_order_product.recipient_type_id "
. "FROM us2_order_product, us2_job_group "
. "WHERE order_product_id = {$OrderProductId} "
. "AND us2_order_product.job_group_id > 0 "
. "AND us2_order_product.job_group_id = us2_job_group.us2_job_group_id ";
echo "Query = {$Query}\n";
$Rs = $this->Conn->executeQuery($Query);
while($Rs->next())
{
$JobGroupId = $Rs->get('us2_job_group_id');
$RecipientTypeId = $Rs->get('recipient_type_id');
$RecipientListId = $Rs->get('recipient_list_id');
}
} else {
echo "No OrderProductId detected.\n";
}
if ( $JobGroupId > 0 )
{
$Query = "SELECT mailing_id, mail_date "
. "FROM mailing "
. "WHERE us2_job_group_id = {$JobGroupId} "
. "AND work_order_number LIKE 'trm%' "
. "AND mail_date > '2020-10-01' "
. "LIMIT 1 ";
echo "Query = {$Query}\n";
$Rs = $this->Conn->executeQuery($Query);
while($Rs->next())
{
$MailDate = $Rs->get('mail_date');
$MailingId = $Rs->get('mailing_id');
}
}else {
echo "No JobGroupId detected.\n";
}
if ( $MailingId )
{
$Query = "UPDATE dealerx_raw_data_batch_log "
. "SET mailing_id = {$MailingId}, on_truck_date ='{$MailDate}', mailing_try_next_date='2029-12-31 23:59:59' "
. "WHERE dealerx_raw_data_batch_log_id = {$ToDoRec['dealerx_raw_data_batch_log_id']} "
. "LIMIT 1 ";
echo "Query = {$Query}\n";
$Rs = $this->DmsConn->executeQuery($Query);
// Get the names from the mailed list.
$this->PushToOpportunities($RecipientTypeId, $RecipientListId, $OrderProductId, $CustomerId, $ClientId, $MailDate);
}
}
}
public function PushToOpportunities($RecipientTypeId, $RecipientListId, $OrderProductId, $CustomerId, $ClientId, $OnTruckDate, $CommType='MAIL')
{
echo "PushToOpportunities(RecipientTypeId={$RecipientTypeId}, RecipientListId={$RecipientListId}, OrderProductId={$OrderProductId}, CustomerId={$CustomerId}, ClientId={$ClientId}, OnTruckDate={$OnTruckDate})\n";
$dms_master_conn = $this->CDmsd->GetDmsDbConn($CustomerId);
if ( $RecipientTypeId == 2 )
{
$TriggerBatchId = 0;
$TriggerBatchList = array();
$RecipientList = array();
$Query = "SELECT * FROM amms_list_data_detail "
. "WHERE amms_list_data_id = {$RecipientListId} "
. "AND duplicate_detected_yn = 'N' "
. "AND valid_yn = 'Y' ";
echo "Query = {$Query}\n";
$Rs = $this->Conn->executeQuery($Query);
while($Rs->next())
{
$Row = $Rs->getRow();
$NameInfo = unserialize($Row['name_info']);
echo "NameInfo = " . print_r($NameInfo, true) . "\n";
$Extra = unserialize($NameInfo->Extra);
echo "Extra = " . print_r($Extra, true) . "\n";
$TriggerBatchList['trigger_batch_list_id'] = $Extra['TRIGGER_BATCH_LIST_ID'];
$TriggerBatchId = $Extra['TRIGGER_BATCH_ID'];
$Email = '';
$Phone = '';
if (!empty($Extra['customer_email'])) {
$Email = $Extra['customer_email'];
} elseif (!empty($Extra['customer_email2'])) {
$Email = $Extra['customer_email2'];
}
if (!empty($Extra['customer_voice_phone'])) {
$Phone = $Extra['customer_voice_phone'];
} elseif (!empty($Extra['home_phone'])) {
$Phone = $Extra['home_phone'];
} elseif (!empty($Extra['mobile_phone'])) {
$Phone = $Extra['mobile_phone'];
}
$RecipientList[$Extra['TRIGGER_BATCH_LIST_ID']] = array('Prefix' => trim($NameInfo->Prefix),
'First' => trim($NameInfo->First),
'Middle' => trim($NameInfo->Middle),
'Last' => trim($NameInfo->Last),
'Suffix' => trim($NameInfo->Sufffix),
'Address' => trim($NameInfo->Address),
'Suite' => trim($NameInfo->Suite),
'City' => trim($NameInfo->City),
'State' => trim($NameInfo->State),
'Zip' => trim($NameInfo->Zip),
'Email' => $Email,
'Phone' => $Phone
);
foreach($Extra as $Key => $Value)
{
if ( strlen($key) > 0 && strlen($value) > 0 )
{
$RecipientList[$Extra['TRIGGER_BATCH_LIST_ID']][$key] = $Value;
}
}
}
$TrigNameId = 0;
// Get the trigger_name_id from the batch
$Query = "SELECT trigger_name_id "
. "FROM trigger_batch "
. "WHERE trigger_batch_id = {$TriggerBatchId}";
echo "Query = {$Query}\n";
$Rs = $dms_master_conn->executeQuery($Query);
while($Rs->next())
{
$TrigNameId = $Rs->get('trigger_name_id');
}
// Get the data from the actual batch records.
$Query = "SELECT * "
. "FROM trigger_batch_list "
. "WHERE trigger_batch_list_id IN (" . join(",", $TriggerBatchList) . ") ";
echo "Query = {$Query}\n";
$Rs = $dms_master_conn->executeQuery($Query);
while($Rs->next())
{
$Row = $Rs->getRow();
$TriggerBatchListId = $Row['trigger_batch_list_id'];
echo "TriggerBatchListId = {$TriggerBatchListId}\n";
$RecipientList[$TriggerBatchListId]['Email'] = $Row['email_address'];
$RecipientList[$TriggerBatchListId]['Phone'] = $Row['voice_phone'];
echo "RecipientList[{$TriggerBatchListId}] = " . print_r($RecipientList[$TriggerBatchListId], true) . "\n";
}
// die("Aborting\n");
$AccessGuid = strtoupper(uniqid(true));
$Query = "INSERT INTO trig_opportunity (`client_id`,`customer_id`,`communication_date`,`trigger_name_id`,`order_product_id`,`communication_type`,`list_json`,`access_guid`,`communications_json`) "
. "VALUES ({$ClientId}, {$CustomerId},'{$OnTruckDate}',{$TrigNameId},{$OrderProductId},'{$CommType}','" . addslashes(json_encode($RecipientList)) . "','{$AccessGuid}','[]') "
. "ON DUPLICATE KEY update communication_date = VALUES(communication_date), trigger_name_id=VALUES(trigger_name_id), communication_type=VALUES(communication_type), list_json=VALUES(list_json) ";
echo "Query = {$Query}\n";
$Rs = $this->Conn->executeQuery($Query);
}
}
public function GetOrderProduct($CustomerId, $TriggerBatchId)
{
echo "\nGetOrderProduct(CustomerId = {$CustomerId}, TriggerBatchId = {$TriggerBatchId}\n\n";
// Get the Mail Trigger Order Code from the trigger batch
$dms_master_conn = $this->CDmsd->GetDmsDbConn($CustomerId);
$MailOrderTriggerCode = "";
$OrderProductId = false;
$MailTriggerOrderCode = "";
$Query = "SELECT mail_trigger_order_code "
. "FROM trigger_batch "
. "WHERE trigger_batch_id = {$TriggerBatchId} ";
echo "Query = {$Query}\n";
$Rs = $dms_master_conn->executeQuery($Query);
while($Rs->next())
{
$MailTriggerOrderCode = $Rs->get('mail_trigger_order_code');
}
if ( $MailTriggerOrderCode != "" )
{
$Query = "SELECT us2_order_product_id "
. "FROM webservice_tracking, webservice_order "
. "WHERE webservice_tracking.tracking_number = '{$MailTriggerOrderCode}' "
. "AND webservice_tracking.webservice_tracking_id = webservice_order.webservice_tracking_id ";
echo "Query = {$Query}\n";
$Rs = $this->Conn->executeQuery($Query);
while($Rs->next())
{
$OrderProductId = $Rs->get('us2_order_product_id');
echo "Found OrderProductId = {$OrderProductId}\n";
}
}
return $OrderProductId;
}
public function SendNoticeEmailToDealer($TrigOpportunityId)
{
$Query = "SELECT * FROM trig_opportunity "
. "WHERE trig_opportunity_id = {$TrigOpportunityId} ";
$Rs = $this->Conn->executeQuery($Query);
while($Rs->next())
{
$OppRec = $Rs->getRow();
$CustomerId = $OppRec['customer_id'];
$MailList = json_decode($OppRec['list_json'], true);
}
echo "SendNoticeEmailToDealer($CustomerId)\n";
$ProfileAlertList = self::GetProfileInfo($CustomerId);
echo "ProfileAlertList = " . print_r($ProfileAlertList, true) . "\n";
$AlertList = array();
for($i = 1; $i < 3; $i++)
{
echo "i = {$i}\n";
if( strpos($ProfileAlertList['leadsniper_email' . $i], '@') )
{
if( strlen(trim($ProfileAlertList['leadsniper_name' . $i])) > 0 )
{
$AlertList[$ProfileAlertList['leadsniper_email' . $i]] = $ProfileAlertList['leadsniper_name' . $i];
}
}
}
echo "MailList = " . print_r($MailList, true) . "\n";
echo "======================\n";
echo "AlertList = " . print_r($AlertList, true) . "\n";
// Only contact if the are valid records with a mail or email address
$ActionList = array();
foreach($MailList as $key => $Rec)
{
if ( strlen($Rec['Email']) > 0 || strlen($Rec['Phone']) > 0 )
{
$ActionList[] = $Rec;
}
}
echo "ActionList = " . print_r($ActionList, true) . "\n";
die("Aborting\n");
if ( count($MailList) > 1 )
{
}
$Subject = "DOC Alert: Active Buyer Alert";
$SectionColor = "#4D1F78; color:#FFFF";
$BodyTemplate = "The following consumer has met the requirements to qualify matching VDPs similar to dealer make and model.
\n"
. "
\n"
. ""
//. "| VEHICLE |
\n"
//. "| %YEAR% %MAKE% %MODEL% |
\n"
. "| |
\n"
. "| CONSUMER |
\n"
. "| |
\n"
. "| First Name | %FIRST% |
\n"
. "| Last Name | %LAST% |
\n"
. "| Address | %ADDRESS% |
\n"
. "| City | %CITY% |
\n"
. "| State | %STATE% |
\n"
. "| Zip | %ZIP% |
\n"
. "| Phone | %PHONE% |
\n"
. "| Email | %EMAIL% |
\n"
. "
\n"
. "
"
. "
Kind regards,"
. "
D.O.C. Team\n";
foreach($MailList as $MailKey => $Rec)
{
echo "Rec = " . print_r($Rec, true) . "\n";
$Body = $BodyTemplate;
foreach($Rec as $RecKey => $RecValue)
{
echo "Rec[{$RecKey}] = {$RecValue}\n";
switch($RecKey)
{
case 'First':
$Body = str_replace("%FIRST%", $RecValue, $Body);
break;
case 'Last':
$Body = str_replace("%LAST%", $RecValue, $Body);
break;
case 'Address':
$Body = str_replace("%ADDRESS%", $RecValue, $Body);
break;
case 'City':
$Body = str_replace("%CITY%", $RecValue, $Body);
break;
case 'State':
$Body = str_replace("%STATE%", $RecValue, $Body);
break;
case 'Zip':
$Body = str_replace("%ZIP%", $RecValue, $Body);
break;
// case 'MAKE':
// $Body = str_replace("%MAKE%", $RecValue, $Body);
// break;
// case 'MODEL':
// $Body = str_replace("%MODEL%", $RecValue, $Body);
// break;
// case 'YEAR':
// $Body = str_replace("%YEAR%", $RecValue, $Body);
// break;
case 'Email':
$Body = str_replace("%EMAIL%", $RecValue, $Body);
break;
case 'Phone':
$Body = str_replace("%PHONE%", $RecValue, $Body);
break;
}
}
echo "Body = {$Body}\n";
// foreach($AlertList as $EmailAddress => $EmailName)
// {
// $this->SendAlert($EmailName, $EmailAddress, $Subject, $Body);
// }
// send a copy to Rick
$this->SendAlert('R Davis', 'rdavis@ammsusa.com', $Subject, $Body);
}
}
public function GetProfileInfo($CustomerId=0, $FieldList=array())
{
if ( count($FieldList) == 0 )
{
$FieldList = array('leadsniper_email1','leadsniper_email2','leadsniper_name1','leadsniper_name2');
}
$Profile = new CustomerProfile();
$DefaultProfileList = $Profile->GetCustomersDefaultProfile(array($CustomerId));
$ProfileId = $DefaultProfileList[$CustomerId];
if ( $ProfileId > 0 )
{
$ProfileData = $Profile->GetProfileData($ProfileId);
}
// Match the requested fields.
$Result = array();
foreach($FieldList as $key => $FieldName)
{
$Result[$FieldName] = '';
if ( array_key_exists($FieldName, $ProfileData))
{
$Result[$FieldName] = $ProfileData[$FieldName];
}
}
// echo "ProfileData = " . print_r($ProfileData, true) . "\n";
// echo "Result = " . print_r($Result, true) . "\n";
//die("Aborting\n");
return $Result;
}
/* ***********************************************************************************************************
* SendAlert()
* ***********************************************************************************************************/
public function SendAlert($EmailName, $EmailAddress, $Subject, $Body)
{
$Gmail = new SendGmail();
$Result = $Gmail->Send(array($EmailAddress => $EmailName), $Subject, "{$Body}");
// echo "Sending email \n";
// $email = new AlertEmail();
// $email->SetMailHost(369);
// $email->SendStatusEmail(array($EmailAddress => $EmailAddress),
// $Subject,
// "{$Body}",
// '',
// ''
// );
// $RecipientList = array('rdavis@americanmarketingusa.com' => 'Rick Davis',
// 'airborn3823r@gmail.com' => 'R Davis'
// );
// $Subject = "Testing the Gmail API";
// $Body = "Testing API
This is a new test message.";
}
}