Created
August 31, 2009 16:49
-
-
Save adamnoffie/178562 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?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; | |
| } | |
| ?> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <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