sugar_crm.module

gistfile1.php

<?php
// $Id$

/**
 * Implementation of hook_perm()
 */
function sugar_crm_perm() {
  //return array();
}

/**
 * Implementation of hook_menu()
 */ 
function sugar_crm_menu() {
	$items['exhibitor-prospect'] = array(
    'title' => 'Exhibitor Prospect',
	  'page callback' => 'drupal_get_form',
		'page arguments' => array('prospect_to_sugar_form'),
	  'access callback' => TRUE,
	  'type' => MENU_CALLBACK,
  );
  $items['sugar-soap'] = array(
    'title' => 'Sugar Soap Testing',
	  'page callback' => 'sugar_soap',
	  'access callback' => TRUE,
	  'type' => MENU_CALLBACK,
  );
  $items['sync-gsc'] = array(
    'title' => 'sugar sync for gsc',
	  'page callback' => 'sync_gsc_exhibitors',
	  'access callback' => TRUE,
	  'type' => MENU_CALLBACK,
  );
  $items['sync-guide'] = array(
    'title' => 'sugar sync for guide',
	  'page callback' => 'sync_guide_exhibitors',
	  'access callback' => TRUE,
	  'type' => MENU_CALLBACK,
  );
  $items['make-target-list'] = array(
    'title' => 'Target List Conversion for guide.truckingshow.com',
	  'page callback' => 'drupal_get_form',
	  'page arguments' => array('make_guide_target_list_form'),
	  'access callback' => TRUE,
	  'type' => MENU_CALLBACK,
  );
	$items['process-bounces'] = array(
	  'title' => 'Process Bounces',
		'access callback' => TRUE,
		'type' => MENU_CALLBACK,
		'page callback' => 'sugar_process_bounces',
	);
  
  return $items;
}

/**
 * Implementation of hook_nodeapi()
 */
function sugar_crm_nodeapi(&$node, $op, $a3 = NULL, $a4 = NULL) {
  $alias = drupal_get_path_alias($_GET['q']);  
  
  if ($op == 'view' && $node->nid == '10410') {
	$node->webform == '';
	$node->content['webform']['#value'] = '';
	$node->content['body']['#value'] = drupal_get_form('prospect_to_sugar_form');
  }  
}

/** Exhibitor prospect form to sugar **/
function prospect_to_sugar_form() {
  $d = get_important_dates();
  $year = $d['c']['year'];
  $form['#prefix'] = '<div class="node clearfix content-padder">';
  $form['#suffix'] = '</div>';
  $form['user'] = array(
    '#type' => 'hidden',
  '#value' => 'web_to_lead',
  );
  $form['assigned_user_name'] = array(
    '#type' => 'hidden',
  '#value' => 'admin',
  );
  
  
  $form['lead_source'] = array(
    '#type' => 'hidden',
  '#value' => 'Prospect Web Form',
  );
  $current_year = $year;
  $prev_year = (string)((int)$year-1);
  $returning_date = 'July 15, 2013';
  $new_date = 'September 15, 2013';
  $form['intro'] = array(
    '#markup' => '<p><strong>This form is for companies that wish to exhibit at MATS but did not exhibit in the previous year.</strong>  If you are a returning Exhibitor we will send the Prospectus to you on ' . $returning_date . ', no action is required on your part before then.</p><p>Once all returning exhibitor enlargement / relocation requests have been addressed, new exhibitor applications will be accommodated in the order they were received.. If you would like for us to mail the Exhibitor Prospectus and Application for Exhibit Space to you for the ' . $current_year . ' Mid-America Trucking Show, please fill out the following form and we will add you to our prospect database. Please note that the Prospectus and Application will not be sent until ' . $new_date . '. If you have any questions at all, please call us at 800.626.2370.</p>',
  );
  $form['account_name'] = array(
    '#type' => 'textfield',
    '#title' => t('Company Name'),
    '#required' => TRUE,
  );
  $form['first_name'] = array(
    '#type' => 'textfield',
    '#title' => t('First Name'),
    '#required' => TRUE,
  );
  $form['last_name'] = array(
    '#type' => 'textfield',
    '#title' => t('Last Name'),
    '#required' => TRUE,
  );
  $form['phone_work'] = array(
    '#type' => 'textfield',
    '#title' => t('Office Phone'),
  );
  
  $form['website'] = array(
    '#type' => 'textfield',
    '#title' => t('Web site'),
  );
  $form['email1'] = array(
    '#type' => 'textfield',
    '#title' => t('Email'),
    '#required' => TRUE,
  );
  $form['primary_address_street'] = array(
    '#type' => 'textfield',
    '#title' => t('Street Address'),
  );
  $form['primary_address_city'] = array(
    '#type' => 'textfield',
    '#title' => t('City'),
  );
  $form['primary_address_state'] = array(
    '#type' => 'textfield',
    '#title' => t('State'),
  );
  $form['primary_address_postalcode'] = array(
    '#type' => 'textfield',
    '#title' => t('Postal Code'),
  );
  $form['primary_address_country'] = array(
    '#type' => 'textfield',
    '#title' => t('Country'),
  );
  $form['desired_booth_size'] = array(
    '#type' => 'textfield',
	  '#title' => t('Desired booth size'),
  );
  $form['hear_about'] = array(
    '#type' => 'textfield',
	  '#title' => t('How did you hear about MATS'),
	  '#description' => t('(previous Exhibitor, web site, magazine, etc.)'),
  );
  $form['status_description'] = array(
    '#type' => 'hidden',
  );
  $form['submit'] = array(
    '#type' => 'submit',
	  '#value' => 'submit',
	  '#title' => t('Submit'),
  );
  
  return $form;
}

function prospect_to_sugar_form_validate(&$form, &$form_state) {
  $desc = $form_state['values']['hear_about'] == '' ? '' : $form_state['values']['hear_about']."\n";
  $desc = $form_state['values']['desired_booth_size'] == '' ? '' : $form_state['values']['desired_booth_size']."\n";
  $form_state['values']['status_description'] = $desc;
}

function prospect_to_sugar_form_submit($form, $form_state) {
  require_once('sites/all/libraries/nusoap/nusoap.php');
  $sc = new nusoapclient('http://path/to/soap.php', false);
  
  $auth_array = array(
    'user_auth' => array(
      'user_name' => 'admin',
	  'password' => 'password_here',
	),
  );
  $v = $form_state['values'];
  $response = $sc->call('login', $auth_array);
  $session_id = $response['id'];
  $user_guid = $sc->call('get_user_id',$session_id);
  $set_entry_params = array(
      'session' => $session_id,
      'module_name' => 'Leads',
      'name_value_list'=>array(
  	  array('name'=>'account_name','value'=>$v['account_name']),
  	  array('name'=>'status', 'value'=>'Pre-Qualified'),
  	  array('name'=>'lead_source', 'value'=>'Prospect Web Form'),
  	  array('name'=>'first_name','value'=> $v['first_name']),
  	  array('name'=>'last_name','value'=>$v['last_name']),
  	  array('name'=>'phone_work', 'value'=>$v['phone_work']),
  	  array('name'=>'primary_address_street','value'=>$v['primary_address_street']),
  	  array('name'=>'primary_address_city','value'=>$v['primary_address_city']),
  	  array('name'=>'primary_address_state','value'=>$v['primary_address_state'])
,  	  array('name'=>'primary_address_country','value'=>$v['primary_address_country']),
  	  array('name'=>'primary_address_postalcode','value'=>$v['primary_address_postalcode']),		
  	  array('name'=>'email1','value'=>$v['email1']),
  	  array('name'=>'assigned_user_id', 'value'=>$user_guid)
	  )
  );
  
  $result = $sc->call('set_entry',$set_entry_params);
  $to = '[email protected]';
  $lang = language_list();
  $lang = $lang['en'];
  $params = $v;
  drupal_mail('sugar_crm', 'web_form_lead', $to, $lang, $params, $from = '<Mid-America Trucking Show> [email protected]');
  $msg = "We have received your request and added you to our Prospect list.  If you'd like to know more about How To Exhibit, please review the steps below. ";
  $msg .= "If you would like to return to the form, please <a href=\"exhibitor-prospect\" title=\"Exhibitor Prospect Form\">click here</a>.";
  drupal_set_message($msg);
	drupal_goto('how-to-exhibit');
}

/**
 * Implementation of hook_mail()
 */
function sugar_crm_mail($key, &$message, $params) {
  switch ($key) {
    case 'web_form_lead':
	  $message['subject'] = 'Prospect Web Form Submission';
	  $body = '';
	  foreach ($params as $k => $v) {
	    $body .= $k . ' : ' . $v . "\n";
	  }
	  $message['body'][] = $body;
	break;
  }
}

function sugar_soap() {
  require_once('sites/all/libraries/nusoap/nusoap.php');
  $sc = new nusoapclient('http://path/to/soap.php', false);
  
  $auth_array = array(
    'user_auth' => array(
      'user_name' => 'admin',
	  'password' => 'password_here',
	),
  );
  
  $response = $sc->call('login', $auth_array);
  var_dump($response);
}

/** gsc.truckingshow.com 
----------------------------
----------------------------
--------------------------*/

function sync_gsc_exhibitors() {
  
	/*
	 1. Get list of all exhibitors from sugar
	 2. Select * from Exhibitors not in step 1
	 3. Connect to sugar and add	
	*/
	db_set_active('gsc_sugar');
	$leads_query = db_query("SELECT exhibitor_number_c FROM {leads_cstm} WHERE {leads_cstm}.id_c IN (SELECT id FROM {leads} WHERE {leads}.deleted = 0)");
	db_set_active();
	$current_leads = array();
	foreach ($leads_query as $result)
	{
		$current_leads[] = $result->exhibitor_number_c;
	}
  
  if (empty($current_leads)) {
    $current_leads = array(99999999999);
  }
//_dvd($current_leads);
	$leads_list = implode(',' , $current_leads);
//_dvd($leads_list);
	// list of all current exhibitor id's
	$year = get_current_year();
	db_set_active('exhibitors');
	$exhibitors_query = db_query("SELECT * FROM {Exhibitors} 
		WHERE ShowYear = :year
		AND Active = -1 
		AND ExNumber 
		NOT IN (:leads_list)", 
		array(':year' => $year, ':leads_list' => $current_leads)
	);
	$exhibitors = array();
	foreach ($exhibitors_query as $record)
	{
		$current_exhibitors[] = $record;
	}
//_dvd(count($current_exhibitors));  
sugar_gsc_insert_lead($current_exhibitors);
  
  //sugar_gsc_modify_lead($modify_in_sugar); 
}

function sugar_gsc_modify_lead($data) {
  // waiting on feedback from gsc before proceeding
}

function sugar_gsc_insert_lead($data) {
  require_once('sites/all/libraries/nusoap/nusoap.php');
  $sc = new nusoapclient('http://path/to/soap.php', false);
  $auth_array = array(
    'user_auth' => array(
    'user_name' => 'admin',
	  'password' => 'password_here',
	),
  );
//print count($data);
  foreach ($data as $k => $v) {
    $response = $sc->call('login', $auth_array);
	  $session_id = $response['id'];
    $user_guid = $sc->call('get_user_id',$session_id);
    $status = $v->Active == '-1' ? 'New' : 'Cancelled';
	  
	  $set_entry_params = array(
      'session' => $session_id,
      'module_name' => 'Leads',
      'name_value_list'=>array(
	    array('name'  =>  'account_name',               'value' => $v->Company),
		  array('name'  =>  'booth_number_c',             'value' => $v->Booth),
		  array('name'  =>  'sqft_c',                     'value' => $v->SqFt),
		  array('name'  =>  'product_description_c',      'value' => $v->Product),
	    array('name'  =>  'status',                     'value' => $status),
		  array('name'  =>  'company_c',                  'value' => $v->Company),
	    array('name'  =>  'first_name',                 'value' => is_null($v->AddlFirstName) ? $v->FirstName : $v->AddlFirstName),
	    array('name'  =>  'last_name',                  'value' => is_null($v->AddlLastName) ? $v->LastName : $v->AddlLastName),
	    array('name'  =>  'phone_work',                 'value' => is_null($v->AddlPhone) ? $v->Phone : $v->AddlPhone),
	    array('name'  =>  'phone_fax',                  'value' => is_null($v->AddlFax) ? $v->Fax : $v->AddlFax),
		  array('name'  =>  'primary_address_street',     'value' => is_null($v->AddlAddress) ? $v->Address : $v->AddlAddress),
		  array('name'  =>  'primary_address_city',       'value' => is_null($v->AddlCity) ? $v->City : $v->AddlCity),
		  array('name'  =>  'primary_address_state',      'value' => is_null($v->AddlState) ? $v->State : $v->AddlState),
		  array('name'  =>  'primary_address_country',    'value' => is_null($v->AddlCountry) ? $v->Country : $v->AddlCountry),
		  array('name'  =>  'primary_address_postalcode', 'value' => is_null($v->AddlZip) ? $v->Zip : $v->AddlZip),		
		  array('name'  =>  'email1',                     'value' => is_null($v->AddlEmail) ? $v->Email : $v->AddlEmail),
		  array('name'  =>  'web_site_c',                 'value' => $v->Url),
		  array('name'  =>  'tshow_username_c',           'value' => $v->WebUsername),
		  array('name'  =>  'tshow_password_c',           'value' => $v->WebPassword),
	    array('name'  =>  'show_directory_contact_c',   'value' => $v->FirstName . ' ' . $v->LastName . '<br>' . $v->Phone . '<br>' . $v->Email),
		  array('name'  =>  'exhibitor_number_c',         'value' => $v->ExNumber),
		  array('name'  =>  'modified_c',                 'value' => $v->Date),
	    array('name'  =>  'assigned_user_id',           'value' => $user_guid)
	  ),	  
  );
	  $result = $sc->call('set_entry',$set_entry_params);
//_dvd($result);
	} 
  
}


/** guide.truckingshow.com 
----------------------------
----------------------------
--------------------------*/

function sync_guide_exhibitors() {
  // get last modified date/time from leads
  $last_modified_sql = "SELECT modified_c FROM leads_cstm ORDER BY modified_c DESC";
  db_set_active('guide_sugar');
  $last_modified = db_result(db_query($last_modified_sql));
  
  db_set_active();
  // all exhibitors that are new or are modified
  $new_modifieds_sql = "SELECT * FROM {guideexhibitors}";
  
  $new_modifieds_result_set = db_query($new_modifieds_sql);
  while ($d = db_fetch_array($new_modifieds_result_set)) {
    $new_modifieds[] = $d;
	$modifieds_exhibitor_id[] = $d['ExNumber'];
  }
  $modifieds_exhibitor_id_string = implode(',',$modifieds_exhibitor_id);
  
  // exhibitor numbers of already existing
  $already_existing_sql = "SELECT exhibitor_number_c FROM {leads_cstm}";				
  db_set_active('guide_sugar');
  $already_existing_result_set = db_query($already_existing_sql, $modifieds_exhibitor_id_string);
  db_set_active();  
  while ($d = db_fetch_array($already_existing_result_set)) {
    // already existing = any exhibitor that has already been added
	if ($d['exhibitor_number_c'] != '') {
	  $already_existing[] = $d['exhibitor_number_c'];
	}
  }
  
  // for each that is not in the already existing, we need to add them
  foreach ($new_modifieds as $k => $v) {
    if (!in_array($v['ExNumber'], $already_existing)) {
	  $insert_into_sugar[] = $v;
	} else {
	  $modify_in_sugar[] = $v;
	}
  }
  
  // check each $insert_into_sugar and remove any that are already in accounts
  $sql = "SElECT exhibitor_number_c FROM {accounts_cstm} WHERE {accounts_cstm}.advertiser_group_c = '%s' OR {accounts_cstm}.advertiser_group_c = '%s'";
  
  db_set_active('guide_sugar');
  $results = db_query($sql, '2010_advertiser', 'Media Partner');
  db_set_active();
  while ($r = db_fetch_array($results)) {
    $previous_advertisers[] = $r['exhibitor_number_c'];
  }
  
  // get all exhibitors previous exhibitor number
  foreach ($insert_into_sugar as $k => $v) {
    $sql = "SELECT OldExNumber FROM {tblExhibitorLink} WHERE NewExNumber = %d";
    $results = db_query($sql, $v['ExNumber']);
    while ($r = db_fetch_array($results)) {
      $insert_into_sugar[$k]['old_number'] = $r['OldExNumber'];
    }  
  }
  
  foreach ($insert_into_sugar as $k => $v) {
    // if the old number is in previous advertisers, then we do not insert as a lead
	// if the key 'old_number' does not exist, then we insert into leads as this is a new exhibitor
    if ( !in_array ($v['old_number'], $previous_advertisers ) || empty($v['old_number']) ) {
	  $do_insert[] = $v;
	} else {
	  $is_advertiser[] = $v;
	}
  }
  
  sugar_guide_insert_lead($do_insert); 
  print "Inserted " . count($do_insert) . " leads into guide.truckingshow.com";
  //sugar_guide_modify_lead($modify_in_sugar); 
}

function sugar_guide_modify_lead($data) {
  // waiting on feedback from gsc before proceeding
}

function sugar_guide_insert_lead($data) {
  
  require_once('sites/all/libraries/nusoap/nusoap.php');
  $sc = new nusoapclient('http://path/to/soap.php', false);
  
  $auth_array = array(
    'user_auth' => array(
      'user_name' => 'admin',
	  'password' => md5('password_here'),
	),
  );
  foreach ($data as $k => $v) {
    
	$response = $sc->call('login', $auth_array);
    
	$session_id = $response['id'];
    $user_guid = $sc->call('get_user_id',$session_id);
    $status = $v['active'] == '-1' ? 'New' : 'Cancelled';
	$set_entry_params = array(
      'session' => $session_id,
      'module_name' => 'Leads',
      'name_value_list'=>array(
	    array('name'=>'account_name','value'=>$v['ltCompany']),
		array('name'=>'booth_number_c','value'=>$v['ExhibitSpace']),
		array('name'=>'sqft_c','value'=>$v['SqFt']),
		array('name'=>'product_description_c','value'=>$v['Product']),
	    array('name'=>'status', 'value'=>$status),
		array('name' => 'opportunity_c', 'value' => '^Directory Advertiser^'),
		array('name'=>'exhibitor_number_c','value'=>$v['ExNumber']),
		array('name'=>'modified_c','value'=>$v['modified']),
	    array('name'=>'assigned_user_id', 'value'=>'29ed38b7-4763-d6d5-7b6a-4cb601a76319'),
		array('name'=>'lead_source', 'value'=>'2011 Exhibitor')
	  )
	  
    );
	$result = $sc->call('set_entry',$set_entry_params);
	
	$lead_id = $result['id'];
	// enter contact
	$set_entry_params = '';
	$set_entry_params = array(
	  'session' => $session_id,
	  'module_name' => 'Contacts',
	  'name_value_list' => array(
	    array('name'=>'assigned_user_id', 'value'=>'29ed38b7-4763-d6d5-7b6a-4cb601a76319'),
		array('name' => 'first_name', 'value' => $v['ltFirstName']),
		array('name' => 'last_name', 'value' => $v['ltLastName']),
		array('name' => 'phone_work', 'value' => $v['ltPhone']),
		array('name' => 'phone_fax', 'value' => $v['ltFax']),
		array('name'=>'email1','value'=>$v['ltEmail']),
		array('name' => 'primary_address_street', 'value' => $v['ltAddress']),
		array('name' => 'primary_address_city', 'value' => $v['ltCity']),
		array('name' => 'primary_address_state', 'value' => $v['ltState']),
		array('name' => 'primary_address_postalcode', 'value' => $v['ltZipCode']),
		array('name' => 'primary_address_country', 'value' => $v['ltCountry']),
		array('name' => 'primary_address_city', 'value' => $v['ltCity']),
		array('name' => 'contact_company_name_c', 'value' => $v['ltCompany']),
		array('name' => 'contact_lead_company_name_c', 'value' => $v['ltCompany']),
		array('name' => 'contact_type_c', 'value' => 'contact_type_access_show_directory'),
	  )
	);
	$result = $sc->call('set_entry',$set_entry_params);
	$show_directory_contact_id = $result['id'];
	
	if ($v['rtCompany'] != '' || $v['rtCompany'] != NULL) {
	    $set_entry_params = '';
	    $set_entry_params = array(
	    'session' => $session_id,
	    'module_name' => 'Contacts',
	    'name_value_list' => array(
	      array('name'=>'assigned_user_id', 'value'=>'29ed38b7-4763-d6d5-7b6a-4cb601a76319'),
		  array('name' => 'first_name', 'value' => $v['rtFirstName']),
		  array('name' => 'last_name', 'value' => $v['rtLastName']),
		  array('name' => 'phone_work', 'value' => $v['rtPhone']),
		  array('name' => 'phone_fax', 'value' => $v['rtFax']),
		  array('name'=>'email1','value'=>$v['rtEmail']),
		  array('name' => 'primary_address_street', 'value' => $v['rtAddress']),
		  array('name' => 'primary_address_city', 'value' => $v['rtCity']),
		  array('name' => 'primary_address_state', 'value' => $v['rtState']),
		  array('name' => 'primary_address_postalcode', 'value' => $v['rtZipCode']),
		  array('name' => 'primary_address_country', 'value' => $v['rtCountry']),
		  array('name' => 'primary_address_city', 'value' => $v['rtCity']),
		  array('name' => 'contact_company_name_c', 'value' => $v['rtCompany']),
		  array('name' => 'contact_lead_company_name_c', 'value' => $v['ltCompany']),
		  array('name' => 'contact_type_c', 'value' => 'contact_type_access_admin'),
	    )
	  );
	  $result = $sc->call('set_entry',$set_entry_params);
	  $admin_contact_id = $result['id'];	
	}
	//set the relationships
	$sql = "INSERT INTO {leads_contacts_c} (id,deleted,leads_cont7104tsleads_ida,leads_contbb5dontacts_idb) VALUES ('%s',%d,'%s','%s')";
	db_set_active('guide_sugar');
	$run_qry = db_query($sql, create_guid(), 0, $lead_id, $show_directory_contact_id);
	db_set_active();
	if ($v['rtCompany'] != '' || $v['rtCompany'] != NULL) {
	  $sql = "INSERT INTO {leads_contacts_c} (id,deleted,leads_cont7104tsleads_ida,leads_contbb5dontacts_idb) VALUES ('%s',%d,'%s','%s')";
	  db_set_active('guide_sugar');
	  $run_qry = db_query($sql, create_guid(), 0, $lead_id, $admin_contact_id);
	  db_set_active();
	}
	
	
  } 
  print '<h1>Completed Sugar upload for Advertising</h1>';
}

function make_guide_target_list_form($form_state) {
  $form['record_id'] = array(
    '#type' => 'textfield',
	'#title' => t('Record ID'),
	'#description' => t('This is the record ID for the Lead or Account.  To retrieve the id, first navigate to the campaign, then click on the target list within the campaign.  You will see a url like this: <em>http://guide.truckingshow.com/index.php?module=ProspectLists&action=DetailView&record=<strong>b849538a-62c1-318c-b3ef-4ce15be03830</strong>&parent_module=Campaigns&parent_id=823de879-2a80-3e09-065b-4ce15b7ccd7d&return_module=Campaigns&return_id=823de879-2a80-3e09-065b-4ce15b7ccd7d&return_action=DetailView</em>.  Copy and paste the bold section (in between <em>record=</em> and <em>&amp;</em>) and put in the field above.'),
	'#required' => TRUE,
  );
  $form['account_type'] = array(
    '#type' => 'select',
	'#title' => t('Leads or Accounts?'),
	'#description' => t('Looking at the Target list page, the list of company contacts will either be under <strong>^Leads</strong> or <strong>^Accounts</strong>.  Select the one under which they appear'),
	'#options' => array('Accounts'=>'Accounts', 'Leads'=>'Leads'),
	'#required' => TRUE,
  );
  $form['submit'] = array(
      '#type' => 'submit',
	  '#prefix' => '<div style="float: left;margin-top:10px;">',
	  '#suffix' => '</div>',
	  '#value' => 'Submit',
	  '#title' => t('Submit'),
    );
  return $form;
}
function make_guide_target_list_form_submit($form, $form_state) {
  $list_id = $form_state['values']['record_id'];
  $list_type = $form_state['values']['account_type']; // Leads or Accounts
  
  
  if ($list_type == 'Accounts') {
    /** enter the prospect list id -----------------------------------------**/
	db_set_active('guide_sugar');
    $rel_results = db_query("SELECT related_id FROM {prospect_lists_prospects} WHERE prospect_list_id = '%s'", $list_id);
    db_set_active();
  
    /** get full list of related ids ------------------------------------------**/
    while ($rel_res = db_fetch_array($rel_results)) {
	  $rel_ids[] = $rel_res['related_id'];
	  $rel_ids_quote[] = '\''.$rel_res['related_id'].'\'';
	}
	$rel_string = implode(',',$rel_ids_quote);
	
    /** select statement if prospect list is made of accounts ----------------------------------------**/
	db_set_active('guide_sugar');
	$ac_sql = "SELECT * FROM {accounts_contacts},{accounts},{contacts} WHERE {accounts_contacts}.account_id IN (".$rel_string.") AND
  {accounts}.id = {accounts_contacts}.account_id AND {contacts}.id = {accounts_contacts}.contact_id";
    db_set_active('guide_sugar');
    $ac_results = db_query($ac_sql);    
    while ($ac_d = db_fetch_array($ac_results)) {
	  $ac_r[] = $ac_d;
    }
	
    db_set_active();
    /** go through each contact and add account name -- comment out after completion  ----------------------------------- **/
    db_set_active('guide_sugar');
    foreach ($ac_r as $k => $v) {
      db_query("UPDATE contacts_cstm SET contact_lead_company_name_c = '%s' WHERE id_c = '%s'", $v['name'], $v['contact_id']);
    }
    db_set_active();
      
      /** foreach account, need to go through and update the prospect list based on priority of contact **/
      
      foreach ($rel_ids as $t => $y) {
	    $sql = "SELECT * FROM {accounts_contacts},{contacts},{contacts_cstm} WHERE {accounts_contacts}.account_id = '%s' AND {contacts}.id = {accounts_contacts}.contact_id AND {contacts_cstm}.id_c = {accounts_contacts}.contact_id";
	    db_set_active('guide_sugar');
		$run_qry = db_query($sql, $y);
		db_set_active();
		while ($d = db_fetch_array($run_qry)) {
	      $result[$t][] = $d;
	    }
	    //pr($result, 'black');
	      $importance = array (
	  			'contact_type_agency', 
				'contact_type_general', 
				'contact_type_access_admin', 
				'contact_type_access_show_directory', 
				'contact_type_billing' 
				);
	      for ($i = 0; $i< count($importance); $i++) {
	        foreach ($result[$t] as $j => $l) {		  
		    //print $i . ' ::: ' . $v['contact_type_c'] . '<br>';
		      if ( $l['contact_type_c'] == $importance[$i] ) {
			    // found the one with highest importance
			    $custom_contact = $l['contact_type_c'];
			    $contact_id = $l['id_c'];
				//pr($contact_id, 'black');
			    $found = TRUE;
			    break 2;
		      }
		    }
	      }
	      $update = "UPDATE {prospect_lists_prospects} SET {prospect_lists_prospects}.related_id = '%s', {prospect_lists_prospects}.related_type = 'Contacts' WHERE {prospect_lists_prospects}.related_id = '%s'";
	      db_set_active('guide_sugar');
	      $run_query = db_query($update, $contact_id, $y);
	      db_set_active();
	  }
  }
  /**
  *
  * LEADS
  */
  if ($list_type == 'Leads') {
    
	// first get all related id
	db_set_active('guide_sugar');
	$rel_results = db_query("SELECT related_id FROM {prospect_lists_prospects} WHERE prospect_list_id = '%s'", $list_id);
	db_set_active();
	
	while ($rel_res = db_fetch_array($rel_results)) {
	  $rel_ids[] = $rel_res['related_id'];
	}
	
	
    db_set_active('guide_sugar');
    $sql = "SELECT * FROM {contacts}, {leads}, {leads_contacts_c} WHERE
  			{contacts}.id = {leads_contacts_c}.leads_contbb5dontacts_idb
			AND {leads}.id = {leads_contacts_c}.leads_cont7104tsleads_ida";
    $run_qry = db_query($sql);
    db_set_active();
    while ($d = db_fetch_array($run_qry)) {
      $r[] = $d;
    }
  
    foreach ($r as $k => $v) {
	  db_set_active('guide_sugar');
	  $sql = "UPDATE {contacts_cstm} SET contact_lead_company_name_c = '%s' WHERE id_c = '%s'";
	  $run_query = db_query($sql, $v['account_name'], $v['leads_contbb5dontacts_idb']);
	  
	  db_set_active();
    }
  
    foreach ($rel_ids as $t => $y) {
	  $sql = "SELECT * FROM {contacts},{leads_contacts_c},{contacts_cstm} WHERE {leads_contacts_c}.leads_cont7104tsleads_ida = '%s'
			AND contacts.id = {leads_contacts_c}.leads_contbb5dontacts_idb AND contacts.id = {contacts_cstm}.id_c";
	  db_set_active('guide_sugar');
	  $run_qry = db_query($sql, $y);
	  db_set_active();
	  while ($d = db_fetch_array($run_qry)) {
	    $result[$t][] = $d;
	  }
	  
	  $importance = array (
	  			'contact_type_agency', 
				'contact_type_general', 
				'contact_type_access_admin', 
				'contact_type_access_show_directory', 
				'contact_type_billing' 
				);
	  for ($i = 0; $i< count($importance); $i++) {
	    foreach ($result[$t] as $j => $l) {		  
		  //print $i . ' ::: ' . $v['contact_type_c'] . '<br>';
		  if ( $l['contact_type_c'] == $importance[$i] ) {
			// found the one with highest importance
			$custom_contact = $l['contact_type_c'];
			$contact_id = $l['id_c'];
			$found = TRUE;
			break 2;
		  }
		}
	  }
	  $sql = "UPDATE {prospect_lists_prospects} SET {prospect_lists_prospects}.related_id = '%s', {prospect_lists_prospects}.related_type = 'Contacts' WHERE {prospect_lists_prospects}.related_id = '%s'";
	  db_set_active('guide_sugar');
	  $run_query = db_query($sql, $contact_id, $y);
	  db_set_active();
	  
    }
  
  }
  drupal_set_message('If process was completed correctly, all '. $list_type .' should now be Contacts.  Simply go to Admin->Email Queue to force the email out once ready.');
    
}


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

function sugar_process_bounces() {
	$lib = libraries_get_path('PHPExcel');
	
	require_once $lib . '/Classes/PHPExcel.php';
	
	
  $new_bounces = drupal_get_path('module', 'sugar_crm') . '/Bounces';
	$completed_bounces = drupal_get_path('module', 'sugar_crm') . '/Bounces/Completed';
	
	$dir = opendir($new_bounces);
	$contents = scandir($new_bounces);
	
	$doc = new PHPExcel();
	$reader = new PHPExcel_Reader_Excel2007();
	$reader->setReadDataOnly(TRUE);
	
	foreach ($contents as $k => $v) {
		if ( ($v != '.') && ($v != '..') && (!(is_dir($new_bounces . '/' . $v)))) {
			$doc = $reader->load($new_bounces . '/' . $v);
			//_dvd($doc->getActiveSheetIndex());
			$rowIterator = $doc->getActiveSheet()->getRowIterator();
			foreach ($rowIterator as $row) {
			  $cellIterator = $row->getCellIterator();
				$rowIndex = $row->getRowIndex();
				foreach ($cellIterator as $cell) {
				  if ($rowIndex != 1) {
					  $bounces[] = $cell->getCalculatedValue();
				  }
				}				
			}
		}
		sugar_processbounces($bounces);
		// move the file to Completed
		rename($new_bounces . '/' . $v, $completed_bounces . '/' . $v);
	}
	
}

function sugar_processbounces($emails = array()) {
  foreach ($emails as $email) {
	  sugar_set_lead_non_qual($email);
	}
}

function sugar_set_lead_non_qual($email) {
	
	// get sugar lead info
	$sql = "SELECT ea.id, eabr.email_address_id, eabr.bean_id, l.status_description
          FROM email_addresses ea, email_addr_bean_rel eabr, leads l
          WHERE ea.id = eabr.email_address_id
					AND l.id = eabr.bean_id
          AND ea.email_address = :email";
	db_set_active('sugar');
	$lead = db_query($sql, array(':email' => $email))->fetchObject();
	db_set_active();
	if ($lead->bean_id != '') {
    require_once('sites/all/libraries/nusoap/nusoap.php');
    $sc = new nusoapclient('http://path/to/soap/soap.php', false);
  
    $auth_array = array(
      'user_auth' => array(
      'user_name' => 'admin',
	    'password' => 'password_here',
	    ),
    );
	  $status = $lead->status_description;
	  $status .= "\r\n" . date('n/j/y') . ': Email bounced, auto-changed to non-qualified';
    $v = $form_state['values'];
    $response = $sc->call('login', $auth_array);
    $session_id = $response['id'];
    $user_guid = $sc->call('get_user_id',$session_id);
    $set_entry_params = array(
      'session' => $session_id,
      'module_name' => 'Leads',
      'name_value_list'=>array(
	    array('name'=>'status', 'value'=>'Non-Qualified'),
		  array('name' => 'id', 'value' => $lead->bean_id),
		  array('name' => 'status_description', 'value' => $status),
	    )
    );
    $result = $sc->call('set_entry',$set_entry_params);
	}
}