adamnoffie

<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>