Create a new version of paste: f6aed3fd7

JavaScript: Can so...3 sec ago
UntitledC# | 3 sec ago
Untitled8 sec ago
Git - upstream + f...9 sec ago
FL Studio 1010 sec ago
Untitled15 sec ago
Organising JBehave...15 sec ago
UntitledC | 18 sec ago

New Paste

<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! ";
	mysql_select_db("sugar_database") or die(sterr(mysql_error()));
	echo "database was selected! ";
	
@@ // 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) . " ";
	
	// 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']}... "; // next Prospect
	}
	echo " Finished!";

// utility functions
	function nov($strval) { 
		if ($strval == "") return "null"; 
		else return "'" . mysql_real_escape_string($strval) . "'"; 
	}
	
	function sterr($strval) { 
		return "{$strval}"; 
	}
	
?>
</body>
</html>

Optional Paste Settings

Syntax Highlighting:

Paste Expiration:

Paste Exposure:

Paste Name / Title:

Hello Guest
Sign Up or Login

You are currently not logged in, this means you can not edit or delete anything you paste. Sign Up or Login

Pastebin.com Tools & Applications

Windows

Firefox

Chrome

iOS

WebOS

Android

Mac

Opera

Click.to

UNIX

WinPhone