Skip to content

Instantly share code, notes, and snippets.

@adamnoffie
Created August 31, 2009 16:49
Show Gist options
  • Save adamnoffie/178562 to your computer and use it in GitHub Desktop.
Save adamnoffie/178562 to your computer and use it in GitHub Desktop.
<?php
/**
* A temporary method of generating GUIDs of the correct format for our DB.
* @return String contianing a GUID in the format: aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee
*
* Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
* All Rights Reserved.
* Contributor(s): ______________________________________..
*/
function create_guid()
{
$microTime = microtime();
list($a_dec, $a_sec) = explode(" ", $microTime);
$dec_hex = dechex($a_dec* 1000000);
$sec_hex = dechex($a_sec);
ensure_length($dec_hex, 5);
ensure_length($sec_hex, 6);
$guid = "";
$guid .= $dec_hex;
$guid .= create_guid_section(3);
$guid .= '-';
$guid .= create_guid_section(4);
$guid .= '-';
$guid .= create_guid_section(4);
$guid .= '-';
$guid .= create_guid_section(4);
$guid .= '-';
$guid .= $sec_hex;
$guid .= create_guid_section(6);
return $guid;
}
function create_guid_section($characters)
{
$return = "";
for($i=0; $i<$characters; $i++)
{
$return .= dechex(mt_rand(0,15));
}
return $return;
}
function ensure_length(&$string, $length)
{
$strlen = strlen($string);
if($strlen < $length)
{
$string = str_pad($string,$length,"0");
}
else if($strlen > $length)
{
$string = substr($string, 0, $length);
}
}
function microtime_diff($a, $b) {
list($a_dec, $a_sec) = explode(" ", $a);
list($b_dec, $b_sec) = explode(" ", $b);
return $b_sec - $a_sec + $b_dec - $a_dec;
}
?>
<html>
<head>
<title>ProspectToLead - Campaign</title>
<style type="text/css">
body { font-family: Consolas, Monaco, Courier New, Courier }
.error { color: red; font-weight: bold }
.finished { color: green; font-size: larger }
</style>
</head>
<body style="width: 1000em">
<?php
if(!defined('sugarentry'))define('sugarentry', true);
require_once('create_guid.php');
// database
mysql_connect("localhost", "sugar_user_username", "sugar_password") or die(sterr(mysql_error()));
echo "connection to the server was successful!<br/>";
mysql_select_db("sugar_database") or die(sterr(mysql_error()));
echo "database was selected!<br/><br/>";
// you need to change these to match an existing campaign, and a user (probably yours)
$campaign_id = "314fe2d3-3004-e1d2-9b13-4a8ee361578e";
$user_id = "43188444-f11d-9b8f-46a5-460bd7368e24";
// Select Prospects from the campaign that have click-thrus and are not yet converted to leads, including number of hits
$qry = mysql_query("SELECT p.*,prospects_cstm.*, c.hits FROM campaign_log c
LEFT JOIN prospects p ON c.target_id = p.id
LEFT JOIN prospects_cstm ON p.id = prospects_cstm.id_c
WHERE c.campaign_id = '{$campaign_id}' AND c.target_type = 'Prospects'
AND c.activity_type = 'link' AND c.deleted=0
AND p.deleted=0 AND p.lead_id IS NULL") or die(sterr(mysql_error()));
echo "Prospects to Convert: " . mysql_num_rows($qry) . "<br /><br />";
// Process each Prospect
while($prospect = mysql_fetch_assoc($qry))
{
// pull email addresses for prospect
$qry_email = mysql_query("SELECT ea.*, ear.* FROM email_addresses ea
LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id
WHERE ear.bean_module = 'Prospects'
AND ear.bean_id = '{$prospect['id']}'
AND ear.deleted = 0
ORDER BY ear.reply_to_address, ear.primary_address DESC") or die(sterr(mysql_error()));
$lead_id = create_guid();
$now_str = date("Y-m-d H:i:s");
// Create LEAD from PROSPECT
$qry_lead_insert = mysql_query("INSERT INTO leads set id='{$lead_id}', date_entered='{$now_str}', date_modified='{$now_str}',
modified_user_id='{$user_id}', created_by='{$user_id}', deleted='0',
description=" . nov($prospect['description']) . ", assigned_user_id=null,
salutation=" . nov($prospect['salutation']) . ",
first_name=" . nov($prospect['first_name']) . ", last_name=" . nov($prospect['last_name']) . ",
title=" . nov($prospect['title']) . ",
department=" . nov($prospect['department']) . ", do_not_call='0',
phone_home=" . nov($prospect['phone_home']) . ",
phone_mobile=" . nov($prospect['phone_mobile']) . ",
phone_work=" . nov($prospect['phone_work']) . ",
phone_other=" . nov($prospect['phone_other']) . ",
phone_fax=" . nov($prospect['phone_fax']) . ",
primary_address_street=" . nov($prospect['primary_address_street']) . ",
primary_address_city=" . nov($prospect['primary_address_city']) . ",
primary_address_state=" . nov($prospect['primary_address_state']) . ",
primary_address_postalcode=" . nov($prospect['primary_address_postalcode']) . ",
primary_address_country=" . nov($prospect['primary_address_country']) . ",
alt_address_street=null, alt_address_city=null, alt_address_state=null, alt_address_postalcode=null, alt_address_country=null,
converted='0', refered_by=null, lead_source='Campaign', lead_source_description='{$prospect['hits']} ClickThru',
status='New', status_description=null,
account_name=" . nov($prospect['account_name']) . ",
contact_id=null, account_id=null, opportunity_id=null, campaign_id='{$campaign_id}';") or die(sterr(mysql_error()));
$website_c = nov($prospect['website_c']);
if ($website_c != "null") {
$qry_lead_cstm_insert = mysql_query("INSERT INTO leads_cstm set id_c='{$lead_id}', website_c={$website_c};") or die(sterr(mysql_error()));
}
while($email_address = mysql_fetch_assoc($qry_email))
{
// process each email address
$email_addr_bean_rel_id = create_guid();
$qry_email_addr_bean_rel_insert = mysql_query("INSERT INTO email_addr_bean_rel set id='{$email_addr_bean_rel_id}',
email_address_id='{$email_address['email_address_id']}', bean_id='{$lead_id}',
bean_module='Leads', primary_address='{$email_address['primary_address']}',
reply_to_address='{$email_address['reply_to_address']}',
date_created='{$now_str}', date_modified='{$now_str}', deleted='0'") or die(sterr(mysql_error()));
}
// Set the Lead_ID for the Prospect, so Prospect says it is "Converted to Lead" in the details
$qry_prospect_update = mysql_query("UPDATE prospects SET date_modified='{$now_str}', modified_user_id='{$user_id}', lead_id='{$lead_id}'
WHERE ID = '{$prospect['id']}'") or die(sterr(mysql_error()));
// get the target_tracker_key for this prospect
$qry_tracker_key = mysql_query("SELECT target_tracker_key FROM campaign_log
WHERE campaign_id='{$campaign_id}' AND target_type='Prospects' AND target_id='{$prospect['id']}' LIMIT 1") or die(sterr(mysql_error()));
$campaign_log_ttk = mysql_fetch_assoc($qry_tracker_key);
// Campaign Log Entry
$campaign_log_id = create_guid();
$qry_campaign_log_insert = mysql_query("INSERT INTO campaign_log SET id='{$campaign_log_id}', campaign_id='{$campaign_id}',
target_tracker_key='{$campaign_log_ttk['target_tracker_key']}', target_id='{$lead_id}', target_type='Leads', activity_type='lead',
activity_date='{$now_str}', related_id='{$prospect['id']}', related_type='Prospects', archived='0', hits=0, deleted='0', date_modified='{$now_str}'")
or die(sterr(mysql_error()));
echo "Converted {$prospect['last_name']},{$prospect['first_name']}...<br/>"; // next Prospect
}
echo "<br/><br/> <span class='finished'>Finished!</span>";
// utility functions
function nov($strval) {
if ($strval == "") return "null";
else return "'" . mysql_real_escape_string($strval) . "'";
}
function sterr($strval) {
return "<span class='error'>{$strval}</span>";
}
?>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment