Thursday, September 2, 2010

Microsoft Dynamics CRM and PHP

I needed to use PHP for two things:

  • Given a lead's email address, retrieve the name, title, phone and email address of this lead's owner.
  • Insert a new lead into the system.

I spent a lot of time searching the web, and saw many posts of people being unsuccessful with using SoapClient, so I didn't even bother trying. The first order of business was to figure out how to login using IFD (Internet Facing Deployment.) I found this page, which explained how to authenticate. It didn't work, but eventually we figured out that our IFD was not configured properly. In the IFD configuration tool, you can't just leave the "local" subnets blank. At least one IP range must be added, even if you want everything to use IFD. We added 127.0.0.1-127.0.0.255 as the "local" range.

I wrote two classes to handle the CRM. These are in the mscrm.php file.

<?
// vim: filetype=php

class MSCRMServiceException extends Exception {}
class MSCRMLoginFailed extends MSCRMServiceException {}

class MSCRMService {
  protected $host, $org, $domain, $user, $password, $curl_handle, $ticket;

  function __construct($crm_host, $organization, $domain, $user, $password) {
    $this->host = $crm_host;
    $this->org = $organization;
    $this->domain = $domain;
    $this->user = $user;
    $this->password = $password;
    $this->init_curl();
    $this->login();
  }

  function __destruct() {
    curl_close($this->curl_handle);
  }

  public function domain_user() {
    return $this->domain . '\\' . $this->user;
  }

  protected function init_curl() {
    $this->curl_handle = curl_init();
    curl_setopt($this->curl_handle, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($this->curl_handle, CURLOPT_CONNECTTIMEOUT, 10);
    curl_setopt($this->curl_handle, CURLOPT_TIMEOUT, 30);
    curl_setopt($this->curl_handle, CURLOPT_HTTP_VERSION, CURL_HTTP_VERSION_1_1);
  }

  protected function login() {
    $request = '<?xml version="1.0" encoding="utf-8"?' . '>
      <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <soap:Body>
          <Execute xmlns="http://schemas.microsoft.com/crm/2007/CrmDiscoveryService">
            <Request xsi:type="RetrieveCrmTicketRequest">
              <OrganizationName>' . $this->org . '</OrganizationName>
              <UserId>' . $this->domain_user() . '</UserId>
              <Password>' . $this->password . '</Password>
            </Request>
          </Execute>
        </soap:Body>
      </soap:Envelope>';
    $headers = array(
      'Host: ' . $this->host,
      'Connection: Keep-Alive',
      'SOAPAction: "http://schemas.microsoft.com/crm/2007/CrmDiscoveryService/Execute"',
      'Content-type: text/xml; charset="utf-8"',
      'Content-length: ' . strlen($request)
    );
    curl_setopt($this->curl_handle, CURLOPT_URL, 'http://' . $this->host . '/MSCRMServices/2007/SPLA/CrmDiscoveryService.asmx');
    curl_setopt($this->curl_handle, CURLOPT_HTTPHEADER, $headers);
    curl_setopt($this->curl_handle, CURLOPT_POST, true);
    curl_setopt($this->curl_handle, CURLOPT_POSTFIELDS, $request);

    $response = curl_exec($this->curl_handle);
    $matches = array();
    if(preg_match('/<CrmTicket>([^<]*)<\/CrmTicket>/', $response, $matches)) {
      $this->ticket = $matches[1];
    } else {
      throw new MSCRMLoginFailed;
    }
  }

  public function xml_request($xml, $service, $action) {
    $request = '<?xml version="1.0" encoding="utf-8"?' . '>
      <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <soap:Header>
          <CrmAuthenticationToken xmlns="http://schemas.microsoft.com/crm/2007/WebServices">
            <AuthenticationType xmlns="http://schemas.microsoft.com/crm/2007/CoreTypes">2</AuthenticationType>
            <OrganizationName xmlns="http://schemas.microsoft.com/crm/2007/CoreTypes">' . $this->org . '</OrganizationName>
          </CrmAuthenticationToken>
        </soap:Header>
        <soap:Body>
          <' . $action . ' xmlns="http://schemas.microsoft.com/crm/2007/WebServices">
            ' . $xml . '
          </' . $action . '>
        </soap:Body>
      </soap:Envelope>';
    $headers = array(
      'Host: ' . $this->host,
      'Connection: Keep-Alive',
      'SOAPAction: "http://schemas.microsoft.com/crm/2007/WebServices/' . $action . '"',
      'Content-type: text/xml; charset="utf-8"',
      'Content-length: ' . strlen($request)
    );
    curl_setopt($this->curl_handle, CURLOPT_URL, 'http://' . $this->host . '/MSCrmServices/2007/' . $service . '.asmx ');
    curl_setopt($this->curl_handle, CURLOPT_HTTPHEADER, $headers);
    curl_setopt($this->curl_handle, CURLOPT_POST, true);
    curl_setopt($this->curl_handle, CURLOPT_POSTFIELDS, $request);
    // Ticket goes into a cookie.
    curl_setopt($this->curl_handle, CURLOPT_COOKIE, 'MSCRMSession=ticket=' . $this->ticket);
    $response = curl_exec($this->curl_handle);
    return $response;
  }

  // Retrieves given $attributes for given $entity where $condition_attribute equals $condition_string.
  public function retrieve_multiple($entity, $attributes, $condition_attribute, $condition_string) {
    $attributes_xml = "";
    foreach($attributes as $attribute) {
      $attributes_xml .= "<q1:Attribute>" . htmlspecialchars($attribute) . "</q1:Attribute>\n";
    }
    $xml = '
      <query xmlns:q1="http://schemas.microsoft.com/crm/2006/Query" xsi:type="q1:QueryExpression">
        <q1:EntityName>' . htmlspecialchars($entity) . '</q1:EntityName>
        <q1:ColumnSet xsi:type="q1:ColumnSet">
          <q1:Attributes>
            ' . $attributes_xml . '
          </q1:Attributes>
        </q1:ColumnSet>
        <q1:Distinct>false</q1:Distinct>
        <q1:Criteria>
          <q1:FilterOperator>And</q1:FilterOperator>
          <q1:Conditions>
            <q1:Condition>
              <q1:AttributeName>' . htmlspecialchars($condition_attribute) . '</q1:AttributeName>
              <q1:Operator>Equal</q1:Operator>
              <q1:Values>
                <q1:Value xsi:type="xsd:string">' . htmlspecialchars($condition_string) . '</q1:Value>
              </q1:Values>
            </q1:Condition>
          </q1:Conditions>
        </q1:Criteria>
      </query>
    ';
    $response_xml = $this->xml_request($xml, "CrmService", "RetrieveMultiple");
    $response = array();
    foreach($attributes as $attribute) {
      $matches = array();
      if(preg_match('/<q1:' . $attribute . '[^>]*>([^<]*)<\/q1:' . $attribute . '>/', $response_xml, $matches)) {
        $response[$attribute] = $matches[1];
      }
    }
    return $response;
  }

  // Create an entity.  $attributes should have attributes as keys and their values as values.
  public function create_entity($entity, $attributes) {
    $xml = "<entity xsi:type=\"$entity\">\n";
    foreach($attributes as $k => $v) {
      $xml .= "<$k>" . htmlspecialchars($v) . "</$k>\n";
    }
    $xml .= "</entity>\n";
    return $this->xml_request($xml, "CrmService", "Create");
  }

  // Usage: $options = retrieve_attribute("lead", "leadsourcecode")
  public function retrieve_attribute($entity, $attribute) {
    $xml = '
      <Request xsi:type="RetrieveAttributeRequest">
        <EntityLogicalName>' . $entity . '</EntityLogicalName>
        <LogicalName>' . $attribute . '</LogicalName>
      </Request>
    ';
    $xml = $this->xml_request($xml, "MetadataService", "Execute");
    // Get a list of <Option> tags.
    $matches = array();
    if(!preg_match_all('/<Option>(.*?)<\/Option>/', $xml, $matches)) {
      return null;
    }
    $options_xml = $matches[1];
    $options = array();
    foreach($options_xml as $option_xml) {
      $matches = array();
      if(!preg_match('/<Value[^>]*>([^<]*)<\/Value>/', $option_xml, $matches)) {
        continue;
      }
      $code = $matches[1];
      if(!preg_match('/<Label>([^<]*)<\/Label>/', $option_xml, $matches)) {
        continue;
      }
      $name = $matches[1];
      $options[] = array("code" => $code, "name" => $name);
    }
    return $options;
  }

  public function find_code_by_name($entity, $attribute, $name) {
    $codes = $this->retrieve_attribute($entity, $attribute);
    foreach($codes as $code) {
      if($code["name"] == $name) {
        return $code["code"];
      }
    }
    return null;
  }
}

class MSCRMLead {
  protected $service;

  function __construct($service) {
    $this->service = $service;
  }

  public function find_user($guid) {
    $retrieve_fields = array("firstname", "lastname", "title", "internalemailaddress", "address1_telephone1");
    return $this->service->retrieve_multiple("systemuser", $retrieve_fields, "systemuserid", $guid);
  }

  public function find_lead_by_email($email) {
    $retrieve_fields = array("leadsourcecode", "firstname", "lastname",
                             "jobtitle", "companyname", "industrycode", "websiteurl", "address1_telephone1", "emailaddress1",
                             "address1_city", "address1_stateorprovince", "address1_country", "ownerid");
    return $this->service->retrieve_multiple("lead", $retrieve_fields, "emailaddress1", $email);
  }

  public function find_owner_by_email($email) {
    $lead_info = $this->find_lead_by_email($email);
    if(!$lead_info["ownerid"]) {
      return null;
    }
    $owner = $this->find_user($lead_info["ownerid"]);
    return $owner;
  }

  public function find_leadsourcecode($leadsource) {
    return $this->service->find_code_by_name("lead", "leadsourcecode", $leadsource);
  }

  public function find_industrycode($industry) {
    return $this->service->find_code_by_name("lead", "industrycode", $industry);
  }

  public function create($lead_info) {
    return $this->service->create_entity("lead", $lead_info);
  }
}

?>

You can now retrieve a lead's owner information like this:

require_once("mscrm.php");

$mscrm_service = new MSCRMService($host, $organization, $domain, $user, $password);
$mscrm_lead = new MSCRMLead($mscrm_service);
$owner = $mscrm_lead->find_owner_by_email($lead_email);

Creating a new lead is a little more involved, since you need to look up codes for picklists, like leadsourcecode. You can do something like this:

require_once("mscrm.php");

$mscrm_service = new MSCRMService($host, $organization, $domain, $user, $password);
$mscrm_lead = new MSCRMLead($mscrm_service);

$lead_info = array();

$leadsourcecode = $mscrm_lead->find_leadsourcecode($lead_source);
if($leadsourcecode) {
  $lead_info["leadsourcecode"] = $leadsourcecode;
}

$lead_info["firstname"] = $firstname;
$lead_info["lastname"] = $lastname;
$lead_info["jobtitle"] = $jobtitle;
$lead_info["companyname"] = $companyname;

$industrycode = $mscrm_lead->find_industrycode($industry);
if($industrycode) {
  $lead_info["industrycode"] = $industrycode;
}

$lead_info["websiteurl"] = $websiteurl;
$lead_info["address1_telephone1"] = $phone;
$lead_info["emailaddress1"] = $email;
$lead_info["address1_city"] = $city;
$lead_info["address1_stateorprovince"] = $state;
$lead_info["address1_country"] = $country;

$mscrm_lead->create($lead_info);
Hopefully this will save someone days of headaches.

Wednesday, January 6, 2010

Transparent TCP proxy in ruby (jruby)

This is a transparent TCP proxy. I only tested it in jruby, but I see no reason why it wouldn't work in ruby as well. The proxy is multithreaded, it starts a new thread to handle every connection, up to a limit. The client and the server can talk at the same time, and IO.select is used to figure out who has data to send.
require "socket"

remote_host = "www.google.com"
remote_port = 80
listen_port = 5000
max_threads = 5
threads = []

puts "starting server"
server = TCPServer.new(nil, listen_port)
while true
  # Start a new thread for every client connection.
  puts "waiting for connections"
  threads << Thread.new(server.accept) do |client_socket|
    begin
      puts "#{Thread.current}: got a client connection"
      begin
        server_socket = TCPSocket.new(remote_host, remote_port)
      rescue Errno::ECONNREFUSED
        client_socket.close
        raise
      end
      puts "#{Thread.current}: connected to server at #{remote_host}:#{remote_port}"

      while true
        # Wait for data to be available on either socket.
        (ready_sockets, dummy, dummy) = IO.select([client_socket, server_socket])
        begin
          ready_sockets.each do |socket|
            data = socket.readpartial(4096)
            if socket == client_socket
              # Read from client, write to server.
              puts "#{Thread.current}: client->server #{data.inspect}"
              server_socket.write data
              server_socket.flush
            else
              # Read from server, write to client.
              puts "#{Thread.current}: server->client #{data.inspect}"
              client_socket.write data
              client_socket.flush
            end
          end
        rescue EOFError
          break
        end
      end
    rescue StandardError => e
      puts "Thread #{Thread.current} got exception #{e.inspect}"
    end
    puts "#{Thread.current}: closing the connections"
    client_socket.close rescue StandardError
    server_socket.close rescue StandardError
  end

  # Clean up the dead threads, and wait until we have available threads.
  puts "#{threads.size} threads running"
  threads = threads.select { |t| t.alive? ? true : (t.join; false) }
  while threads.size >= max_threads
    sleep 1
    threads = threads.select { |t| t.alive? ? true : (t.join; false) }
  end
end