Ruby MAPI Connectivity

[gregarican]

For my most recent project I have been trying to connect disparate
applications together using XML-RPC. The reason why is because the
front end client is a Sharp Zaurus SL-5500 (which is a Linux-based
system) but the back end servers are Windows-based (in this case MS SQL
Server 2000 and MS Exchange 5.5). For anyone interested in doing so I'm
pasting the code I created to allow the Zaurus client to connect to a
Windows host using XML-RPC, which then in turn uses DBI and WIN32OLE to
query the MS SQL Server and Exchange Server.

The ultimate goal of all of this is to combine POS data with Outlook
contacts to create a custom CRM application that sales staff can use
out on the sales floor with Zaurus handhelds. Of course this is a crude
model, but it's a start The tough part for me was figuring out how
to talk to the Exchange Server. I tried LDAP, but that only got me as
far as the global Directory Services tree, which houses the Global
Address Book and that's about it. I needed to tunnel into the Public
Folders to get shared contacts.

Oh well, in any event here's the code:

-----------------------------
# crmServer.rb
#
# by Greg Kujawa
#
# this script services xmlrpc requests for CRM data provided by MAPI
and SQL data sources

require 'dbi'
require 'win32ole'
require 'xmlrpc/server'
require 'dotnet'

# assign the MAPI property tags
@cdoPR_DISPLAY_NAME = '3001001F'.hex
@cdoPR_COMPANY_NAME = '3A16001F'.hex
@cdoPR_SURNAME = '3A11001E'.hex
@cdoPR_MIDDLE_NAME = '3A44001F'.hex
@cdoPR_GIVEN_NAME = '3A06001F'.hex
@cdoPR_GENERATION = '3A05001E'.hex
@cdoPR_INITIALS = '3A0A001E'.hex
@cdoPR_DISPLAY_NAME_PREFIX = '3A45001E'.hex
@cdoPR_STREET_ADDRESS = '3A29001E'.hex
@cdoPR_LOCALITY = '3A27001E'.hex
@cdoPR_STATE_OR_PROVINCE = '3A28001E'.hex
@cdoPR_POSTAL_CODE = '3A2A001E'.hex
@cdoPR_BUSINESS_ADDRESS_COUNTRY = '3A26001E'.hex
@cdoPR_POSTAL_ADDRESS = '3A15001E'.hex


def getSQLRecordset(sqlStmt, custNum)
# method to query SQL database tables for customer records

# connect to the SQL data source
@dsn = DBI.connect('DBI:ADOrovider=SQLOLEDB;Connect Timeout=5;Data
Source=sawmill_app;Initial Catalog=dcdev;Persist Security
Info=True;Trusted_Connection=Yes;')
[email protected](sqlStmt)
query.execute(custNum)
resultSet = query.fetch.to_a
@dsn.disconnect
return resultSet
end

def getMAPIRecords(custName)
# method to traverse the Sales Contacts folder listed in the Public
Folders

# create the Exchange server session
@session = WIN32OLE.new('Mapi.Session')
@session.logon('Greg Kujawa')

@publicFolders = @session.InfoStores.Item(3)
@rootFolder= @publicFolders.RootFolder
@publicFoldersList = @rootFolder.Folders
@allPublicFolders = @publicFoldersList.Item(2)
@allPublicFoldersList = @allPublicFolders.Folders
@salesContacts = @allPublicFoldersList.Item(8)
@allContacts = @salesContacts.messages
@recordCount = @allContacts.count
@resultSet = Array.new
@recordCount.times do |i|
if @allContacts.Item(i+1).Fields.Item(@cdoPR_DISPLAY_NAME).value.to_s
== custName
@resultSet[0] =
@allContacts.Item(i+1).Fields.Item(@cdoPR_DISPLAY_NAME).value.to_s
@resultSet[1] =
@allContacts.Item(i+1).Fields.Item(@cdoPR_COMPANY_NAME).value.to_s
@resultSet[2] =
@allContacts.Item(i+1).Fields.Item(@cdoPR_SURNAME).value.to_s
@resultSet[3] =
@allContacts.Item(i+1).Fields.Item(@cdoPR_MIDDLE_NAME).value.to_s
@resultSet[4] =
@allContacts.Item(i+1).Fields.Item(@cdoPR_GIVEN_NAME).value.to_s
@resultSet[5] =
@allContacts.Item(i+1).Fields.Item(@cdoPR_STREET_ADDRESS).value.to_s
@resultSet[6] =
@allContacts.Item(i+1).Fields.Item(@cdoPR_LOCALITY).value.to_s
@resultSet[7] =
@allContacts.Item(i+1).Fields.Item(@cdoPR_STATE_OR_PROVINCE).value.to_s

@resultSet[8] =
@allContacts.Item(i+1).Fields.Item(@cdoPR_POSTAL_CODE).value.to_s
@session.logoff
return @resultSet
end
end
end

# create the xmlrpc server
s = XMLRPC::Server.new(port=8888, host="10.0.0.200")

# add a handler to return the contact provided the customer name passed
into the method call
s.add_handler("crm.getContactByName") do |n|
getMAPIRecords(n)
end

# add a handler to return the customer record provided the customer
number passed into the method call
s.add_handler("crm.getRecordByNum") do |n|
sqlStmt = "select fullName, street, city, state, zip, phone from
TblCustInfo where custNo = ?"
getSQLRecordset(sqlStmt, n)
end

# add a handler to return the customer history provided the customer
number passed into the method call
s.add_handler("crm.getHistoryByNum") do |n|
sqlStmt = "select eventType, stockNo, textDesc, salesPerson,
transDate, transType, qty, iPrice, tPrice from tblCustHist where custNo
= ?"
getSQLRecordset(sqlStmt, n)
end

s.serve

-----------------------------
# crmClient.rb
#
# by Greg Kujawa
#
# this script submits xmlrpc requests for CRM data

require "xmlrpc/client"

c = XMLRPC::Client.new("10.0.0.200", "/RPC2", 8888)
begin
# retrieve the first array of SQL records
results1 = Array.new
results1 = c.call("crm.getRecordByNum", 198268)
fullName1 = results1[0]
street1 = results1[1]
city1 = results1[2]
state1 = results1[3]
zip1 = results1[4]
phone1 = results1[5]

puts "Full Name: #{fullName1}\n"
puts "Street Address: #{street1}\n"
puts "City: #{city1}\n"
puts "State: #{state1}\n"
puts "ZIP: #{zip1}\n"
puts "Phone: #{phone1}\n"

# retrieve the second array of SQL records
results2 = Array.new
results2 = c.call("crm.getHistoryByNum", 198268)
eventType2 = results2[0]
stockNo2 = results2[1]
textDesc2 = results2[2]
salesPerson2 = results2[3]
transDate2 = results2[4]
transType2 = results2[5]
qty2 = results2[6]
iPrice2 = results2[7]
tPrice2 = results2[8]

puts "Event Type: #{eventType2}\n"
puts "Stock #: #{stockNo2}\n"
puts "Description: #{textDesc2}\n"
puts "Salesperson: #{salesPerson2}\n"
puts "Trans Date: #{transDate2}\n"
puts "Trans Type: #{transType2}\n"
puts "Qty: #{qty2}\n"
puts "Price Each: $#{iPrice2}\n"
puts "Total Price: $#{tPrice2}\n"

# retrieve the final array of Exchange Contact records
results3 = Array.new
results3 = c.call("crm.getContactByName", fullName1)
displayName3 = results3[0]
compName3 = results3[1]
lastName3 = results3[2]
middleName3 = results3[3]
firstName3 = results3[4]
street3 = results3[5]
city3 = results3[6]
state3 = results3[7]
zip3 = results3[8]

puts "Display Name: #{displayName3}\n"
puts "Company Name: #{compName3}\n"
puts "Last Name: #{lastName3}\n"
puts "Middle Name: #{middleName3}\n"
puts "First Name: #{firstName3}\n"
puts "Street Address: #{street3}\n"
puts "City: #{city3}\n"
puts "State: #{state3}\n"
puts "ZIP: #{zip3}\n"

rescue XMLRPC::FaultException => e
puts "Error:"
puts e.faultCode
puts e.faultString
end

 

[Florian Groß]

The ultimate goal of all of this is to combine POS data with Outlook
contacts to create a custom CRM application that sales staff can use
out on the sales floor with Zaurus handhelds. Of course this is a crude
model, but it's a start The tough part for me was figuring out how
to talk to the Exchange Server. I tried LDAP, but that only got me as
far as the global Directory Services tree, which houses the Global
Address Book and that's about it. I needed to tunnel into the Public
Folders to get shared contacts.

Certainly sounds like an interesting project. Make sure to mention it on
the Real world Ruby page when its finished!

@cdoPR_DISPLAY_NAME = '3001001F'.hex

Note that you can also do 0x3001001F which is slightly better.