Fetch the Account ID based on Primary Emailaddress in Sugar CRM

We have a website and when a record is inserted / updated we runs a script to update the sugar CRM Accounts module.

The Accounts module have a field website_id_c . When an insert occurs to DB we set the last insert ID to website_id_c of Sugar CRM and the remaining values to get inserted.

Insert script is something like below .

$name_value_field=array(
    array('name' => 'name','value' => $name),
    array('name' => 'primary_contactperson_c','value' => $ownername),
    array('name' => 'billing_address_street','value' => $address),
    array('name' => 'billing_address_postalcode','value' => $postalcode),
    array('name' => 'email1','value' => $email),
    array('name' => 'website_id_c','value' => $id_retrieved),
);
if(!empty($sessn_id))
{
    $result=$soap->set_entry("Accounts",$name_value_field); 

}

This uses the SOAP nusoap client to communicate with Sugar CRM cloud. Insertion works fine and update also.

$response = $soap->get_entry_list('Accounts', 
                array('id'), 
                "website_id_c='$websiteID'");
$account_id = $response['entry_list'][0]['id'];

But currently I want to retrieve the account_id to update the record on the basis on Email. I changed the fields with email . But I feel its stored in some other place and the primary address key is stored in the Accounts module. So first we want to fetch that id from the Email module, and then query the Accounts module on the basis of this Email address id to get the Account Values.

 $response = $soap->get_entry_list('Accounts', 
                array('id'), 
                "email1='email@example.com'");

Which is the module that stores the Email address ? I have not worked on Sugar before. So feel free to ask me if you didn't get me exactly.

Thanks

Solution

The email field and its relation to accounts is stored in two other tables, so you need to retrieve the accounts with a sub select.

$response = $soap->get_entry_list(
    $module = 'Accounts',
    $fields = array('id'),
    $query = "accounts.id IN (
        SELECT eabr.bean_id
        FROM email_addr_bean_rel eabr 
            JOIN email_addresses ea ON (ea.id = eabr.email_address_id)
        WHERE 
            eabr.bean_module = 'Accounts' 
            AND eabr.deleted = 0 
            AND ea.email_address = 'email@example.com'
    )",
    $orderBy = "accounts.name", 
    $offset = 0, 
    $max = 10
);

My soap clients get_entry_list method is as

get_entry_list($module, 
    $fields = array(), 
    $query='', 
    $order_by='', 
    $offset=0, 
    $limit = 1000)

Make the necessary changes accordingly .