Skip navigation
All Places > Developer > Blog > 2014 > April
2014
Harald Kuske

RESTful Web2Lead Form

Posted by Harald Kuske Employee Apr 24, 2014
If you create a Lead Form in Sugar, this Lead Form uses the entrypoint “WebToLeadCapture” with it’s full qualified web address

 

e.g. http://myCRMserver/index.php?entryPoint=WebToLeadCapture.

 

Using this generated form every visitor of your website is informed about the location of your CRM server. If you want to hide the address of the CRM server you should use a Lead Form which uses the web service interface of Sugar in a php script. So the address of your web server is not visible any more in the source code of your website.

 

The following article describes how to set up such a php page which uses the Lead Form generated in Sugar 7 with some extra code to enter leads via web service in Sugar.Step 1: Create a “normal” Lead Form in Sugar 7

 

Drag and drop the fields you want to see in the Lead Form to the two possible columns.

Now configure your form and select the related campaign.

Beautify the form in the html editor...

 

...and generate the html code.

Step 2: Change the generated html code to a php REST code

Copy the html code of the generated Lead Form into an editor and copy the following lines of code before the first line of the Lead Form code:

<?php

 

$rest = "http://myCRMserver/rest/v10"; // your crm server address

 

$user = "user"; // userid of the webservice user

 

$pass = "pass"; // and the password of user “user”

 

$step = $_REQUEST["step"];

 

if ($step=="") $step="1";

 

if ($step=="1") // show the Lead Form

 

{

 

?>

Now search the <form> line:

<form id="WebToLeadForm" action="http://myCRMserver/index.php?entryPoint=WebToLeadCapture" method="POST" name="WebToLeadForm">

and change it to the location of the new php lead form:

<form id="WebToLeadForm" action="http://myWebserver/myWebToLeadCapture.php?step=2" method="POST" name="WebToLeadForm">

http://myWebserver/myWebToLeadCapture.php” is only an example, you can store the new Lead Form in any location on your public web server.

Then add the following code to call the webservice interface at the end of the file:

<?php

 

}

 

   else // $step == "2" - generate the Lead

 

{

 

   $lead = $_REQUEST;

 

   $ignore = array("step","redirect_url","req_id"); // fields to be ignored

 

   foreach ($ignore as $key)

 

   {

 

      unset($lead[$key]); // ignore the field

 

   }

 

   $url = $rest . "/oauth2/token"; // login

 

   $oauth2_token_parameters = array(

 

      "grant_type" => "password",

 

      "client_id" => "sugar",

 

      "client_secret" => "",

 

      "username" => $user,

 

      "password" => $pass,

 

      "platform" => "base"

 

   );

 

   $oauth2_token_result = call($url, '', 'POST', $oauth2_token_parameters);

 

   if (empty($oauth2_token_result->error))

 

   {

 

      $url = $rest . "/Leads"; // enter Lead

 

      $post_lead_result = call($url, $oauth2_token_result->access_token, 'POST', $lead);

 

      $url = $rest . "/oauth2/logout"; // logout

 

      $oauth2_logout_result = call($url, $oauth2_token_result->access_token, 'POST');

 

      if (!empty($_REQUEST["redirect_url"])) // redirect

 

         header("Location: {$_REQUEST['redirect_url']}");

 

   }

 

   else

 

      print("<hr>THAT WAS NOT OK<hr>"); // login not successful

 

}

and the code for the function “call” which allows easy handling of RESTful webservices to Sugar:

/*******************************************************************************

 

* call

 

******************************************************************************/

 

/**

 

* Generic function to make cURL request.

 

* @param $url - The URL route to use.

 

* @param string $oauthtoken - The oauth token.

 

* @param string $type - GET, POST, PUT. Defaults to GET.

 

* @param array $parameters - Endpoint parameters.

 

* @return mixed

 

*/

 

function call($url, $oauthtoken='', $type='GET', $parameters=array())

 

{

 

   $type = strtoupper($type);

 

   $curl_request = curl_init($url);

 

   if ($type == 'POST')

 

   {

 

      curl_setopt($curl_request, CURLOPT_POST, 1);

 

   }

 

   elseif ($type == 'PUT')

 

   {

 

      curl_setopt($curl_request, CURLOPT_CUSTOMREQUEST, "PUT");

 

   }

 

   elseif ($type == 'DELETE')

 

   {

 

      curl_setopt($curl_request, CURLOPT_CUSTOMREQUEST, "DELETE");

 

   }

 

   curl_setopt($curl_request, CURLOPT_HTTP_VERSION, CURL_HTTP_VERSION_1_0);

 

   curl_setopt($curl_request, CURLOPT_HEADER, false);

 

   curl_setopt($curl_request, CURLOPT_SSL_VERIFYPEER, 0);

 

   curl_setopt($curl_request, CURLOPT_RETURNTRANSFER, 1);

 

   curl_setopt($curl_request, CURLOPT_FOLLOWLOCATION, 0);

 

   if (!empty($oauthtoken))

 

   {

 

      $token = array("oauth-token: {$oauthtoken}");

 

      curl_setopt($curl_request, CURLOPT_HTTPHEADER, $token);

 

   }

 

   if (!empty($parameters))

 

   {

 

      //encode the parameters as JSON

 

      $jsonEncodedData = json_encode($parameters);

 

      curl_setopt($curl_request, CURLOPT_POSTFIELDS, $jsonEncodedData);

 

   }

 

   $result = curl_exec($curl_request);

 

   curl_close($curl_request);

 

   //decode the response from JSON

 

   $response = json_decode($result);

 

   return $response;

 

}

 

?>

Step 3: Save the new Lead Form to your webserver location, e.g.http://myWebserver/myWebToLeadCapture.php or any location, as mentioned already above.

There are two additional direct links to your crm server hidden in two JavaScript includes. Check if you need these includes and remove them from the Lead Form or provide the needed JavaScipt functions in your web server environment.

Step 4: Test the new Lead Form

If you call your new Lead Form http://myWebserver/myWebToLeadCapture.php you get the following lead capture screen:



Fill the field values and hit the Submit Button, you will be redirected to the redirect_url.

In your Sugar System you will se the newly generated Lead:

Post originally written by Jim Bartek.

 

UPDATE October 3rd 2017

 

Removed references to compileSql() function which was deprecated and removed in Sugar 7.9.

 

You have found yourself in a bind, and you need to query the database directly. There is no other recourse than to write a query to get the data you need. This cookbook entry is going to give you some examples on how to use our new SugarQuery API instead of direct SQL.

 

1. What is SugarQuery?

 

SugarQuery is a SQL query builder for retrieving data directly from the database.  It is used extensively within the core of the application.  For instance, the FilterAPI uses it.

 

It uses a bean, the beans relationships, and visibility models to build a SQL query that can be used to retrieve data.

 

2. The Basics

 

SugarQuery has a very simple interface for building queries.

 

The basic methods you will need to create a query are:

  • select($fields) - accepts an array of fields you would like to select
  • from($bean) - validates the query against a SugarBean at generation
  • where() - creates an AND where object
  • orWhere() - creates an OR where Object
  • join($link) - accepts a link from the from bean to create the correct join(s)
  • orderBy($field, $direction) - accepts a field and a direction to sort upon
  • groupBy($field) - accepts a field to group by
  • having($condition) - accepts a condition (see below)

 

There is also conditions that can be used when building out your query.  This conditions can be used with the where and having.

 

To execute a query you call execute() on the object.  If you would like to see the sql it generated you can call $query->compile()->getSQL() and $query>compile()->getParameters().

 

The execute() method by default returns an array of the records selected. You may also choose to return the db result object execute('db') or return as json execute('json').

 

Lets try a simple example of selecting all Accounts that have an industry of 'Media'.

 

sugarquery-accounts.php

<?php
$query = new SugarQuery();
$query->select(array('id','name'));
$query->from(BeanFactory::getBean('Accounts'));
$query->where()->equals('industry','Media');
$results = $query->execute();

Now lets roll through some examples.

 

3. Joins

 

Let's now get all the contacts on these media accounts.

 

sugarquery-acconts-contacts.php

<?php
$query = new SugarQuery();
$query->from(BeanFactory::getBean('Accounts'));
$contacts = $query->join('contacts')->joinName();
$query->select(array("$contacts.full_name"));
$query->where()->equals('industry','Media');
$results = $query->execute();

 

4. N-Deep Joins

 

Lets say you need to get all media industry accounts, contacts, that are attached to closed opportunities.  Why?  Because you can!

 

sugarquery-n-deep-joins.php

<?php
$query = new SugarQuery();
$query->from(BeanFactory::getBean('Accounts'));
$contacts = $query->join('contacts')->joinName();
$opp = $query->join('opportunities', array('relatedJoin' => $contacts))->joinName();
$query->select(array("$contacts.full_name", "$opp.name"));
$query->where()->equals('industry','media');
$query->where()->equals("$opp.sales_stage", 'closed');
$results = $query->execute();

As you can see, you can prefix a field with the link name, and when the SQL is generated it will replace those with the system generated aliases.

 

5. But I want my own aliases!

 

So you want to alias things yourself?  Alright!  You can set aliases everywhere!

 

sugarquery-aliasing.php

<?php
$query = new SugarQuery();
$query->from(BeanFactory::getBean('Accounts'));
$query->join('contacts', array('alias' => 'industryContacts'));
$query->join('opportunities', array('relatedJoin' => 'industryContacts', 'alias' => 'contactsOpportunities'));
$query->select(array(array('industryAccounts.first_name', 'fname'), array('industryContacts.last_name', 'lname'), 'contactsOpportunities.name'));
$query->where()->equals('industryAccounts.industry','Media');
$query->where()->equals('contactsOpportunities.sales_stage', 'closed');
$results = $query->execute();


6. Order the madness

 

Now you have this data, but you want to sort it, make it make some sense.  Thats EASY!

 

sugarquery-orderby.php

<?php
$query = new SugarQuery();
$query->from(BeanFactory::getBean('Accounts'));
$query->join('contacts', array('alias' => 'industryContacts'));
$query->join('opportunities', array('relatedJoin' => 'industryContacts', 'alias' => 'contactsOpportunities'));
$query->select(array(array('industryContacts.first_name', 'fname'), array('industryContacts.last_name', 'lname'), 'contactsOpportunities.name'));
$query->where()->equals('industryAccounts.industry','Media');
$query->where()->equals('contactsOpportunities.sales_stage', 'closed');
$query->orderBy('industryContacts.name', 'DESC');
$results = $query->execute();

 

7. Groups!

 

Time to group by!

 

sugarquery-groupby.php

<?php
$query = new SugarQuery();
$query->from(BeanFactory::getBean('Accounts'));
$query->join('contacts', array('alias' => 'industryContacts'));
$query->join('opportunities', array('relatedJoin' => 'industryContacts', 'alias' => 'contactsOpportunities'));
$query->select()->setCountQuery();
$query->where()->equals('contactsOpportunities.sales_stage', 'closed');
$query->groupBy('accounts.industry');
$results = $query->execute();

 

8. Having!

 

For a having we need to setup a condition object.  To do this, instantiate the SugarQuery_Builder_Condition object and set the operator, field, and values.

 

sugarquery-having.php

<?php
$query = new SugarQuery();
$query->from(BeanFactory::getBean('Accounts'));
$query->join('contacts', array('alias' => 'industryContacts'));
$query->join('opportunities', array('relatedJoin' => 'industryContacts', 'alias' => 'contactsOpportunities'));
$query->select()->setCountQuery();
$query->where()->equals('contactsOpportunities.sales_stage', 'closed');
$havingCondition = new SugarQuery_Builder_Condition($query);
$havingCondition->setField('contactsOpportunities.amount')->setOperator('>')->setValues('1000');
$query->having($havingCondition);
$results = $query->execute();


Conclusion

 

SugarQuery is your one stop shop for getting your data out of the database.  In part two we can examine more advanced options.