From: gregarican <greg.kujawa@...>
Date: 2005-08-16T03:51:15+09:00
Subject: Ruby MAPI Connectivity

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:ADO:Provider=SQLOLEDB;Connect Timeout=5;Data
Source=sawmill_app;Initial Catalog=dcdev;Persist Security
Info=True;Trusted_Connection=Yes;')
	query=@dsn.prepare(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