Microsoft CRM as Time Logs for Consulting Business: Integration With Great Plains

This article is targeted to programmers, we are providing C# source code for CRM->GP integration. The code provided is not packaged product, but rather “solution”, which needs to be tailored and installed by GP eConnect C# programmers. What integration does:

1. If you log your timesheets as tasks in Microsoft CRM against contract line cases – these tasks will be integrated as GP Sales Order Processing Invoice lines with description cut to one hundred symbols – this is restriction of GP SOP line description

2. In GP sales invoices are created automatically by overnight integration when you close the case in CRM. If you close several cases for the same customer, let’s say QA, development, onsite consulting, all the cases and corresponding tasks will be combined in one invoice per customer

3. Additional possibilities in MS Dynamics CRM. If you log time against the cases, linked to contract lines, you can create very powerful consultant time tracking report and project status report.

4. Additional Comments. Microsoft Dynamics GP eConnect 10.0 SDK doesn’t have source code for Sales Invoice integration C# demo project. We worked out this project and its code is incorporated in the provided demo C# code, please look at the procedure: SerializeSalesOrderObject

Below you see the code listing:

using System;
using System.IO;
using System.Collections;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;
using System.Xml;
using System.Text;
using System.Xml.Serialization;
using Microsoft.Dynamics.GP.eConnect;
using Microsoft.Dynamics.GP.eConnect.Serialization;
using Microsoft.Dynamics.GP.eConnect.MiscRoutines;

namespace Integration
{
sealed public class OrderApp
{
public static string outerSelectString;// = "SELECT DISTINCT SQLSERVER, DATABASENAME FROM ALBANEWTIMELOG";
public static string innerSelectString;// = "SELECT AccountNumber,BillableTime,UnitPrice,LineTotalPrice,[Description],ItemNumber,SQLSERVER,DATABASENAME, IncidentId FROM ALBANEWTIMELOG WHERE DATABASENAME=";
public static string connectionString ;//= "Integrated Security = true;Data Source = CRMSERVER; Initial Catalog = CRMCUSTOMIZATION;";
public static DataTable TableLines; // the datasource of the datagrid called DataLines.

// Error handling variables
public static string sErrMsg;
public static int lErrCode;
public static int lRetCode;

static string sDatabaseName, sServerName, sUserName, sPassword;
static string description, itemnumber, accountNumber, sCustomerPO;
static char char39=(char)39; //to immitate ' symbol
static System.Data.SqlTypes.SqlGuid incidentId;


// ****************************************************************************
// This is the application entry point
// ****************************************************************************
public static void Main()
{

string sCustomerDocument;
string sSalesOrder;
string sXsdSchema;
string sConnectionString;


string NAME="";
string CONTACT="";
string ADDRESS1="";
string ADDRESS2="";
string CITY="";
string STATE="";
string ZIP="";

string currentCustomer = "";

sConnectionString = @"data source=ALBASERVER;initial catalog=ALBA;integrated security=SSPI;persist security info=False;packet size=4096";

StreamReader re = File.OpenText("C:\_IntegrationGP\Integration\Settings.txt");
connectionString = re.ReadLine();
outerSelectString=re.ReadLine();
innerSelectString=re.ReadLine();
sUserName = re.ReadLine();
sPassword = re.ReadLine();
String crmConnectionString = re.ReadLine();
String customerSelectString = re.ReadLine();
String timeLogSelectString = re.ReadLine();
re.Close();

SqlConnection outerConnection = new SqlConnection();
outerConnection.ConnectionString = connectionString;
outerConnection.Open();

SqlCommand outerCommand = new SqlCommand();
outerCommand.CommandType = System.Data.CommandType.Text;
outerCommand.CommandText = outerSelectString;
outerCommand.Connection = outerConnection;
SqlDataReader outerReader = outerCommand.ExecuteReader();

while(outerReader.Read())
{
sServerName="";
sDatabaseName="";
//reading databases-companie and their server names in timelog
sServerName=outerReader.GetSqlString(0).ToString();
sDatabaseName=outerReader.GetSqlString(1).ToString();

sServerName=sServerName.Trim();
sDatabaseName=sDatabaseName.Trim();

SqlConnection connection = new SqlConnection();
connection.ConnectionString = connectionString;
connection.Open();

SqlCommand command = new SqlCommand();
command.CommandType = System.Data.CommandType.Text;
command.CommandText = innerSelectString+char39+sDatabaseName+char39+" order by AccountNumber";
command.Connection = connection;
SqlDataReader reader = command.ExecuteReader();

while(reader.Read())
{
decimal billableTime, unitPrice, lineTotal;
double bTime, uPrice;

description="";
itemnumber="";
accountNumber="";

sCustomerPO="";

Console.WriteLine("Record:");
//reading from timelog
accountNumber=reader.GetSqlString(0).ToString();



billableTime=reader.GetSqlDecimal(1).Value;
unitPrice=reader.GetSqlDecimal(2).Value;
lineTotal=reader.GetSqlDecimal(3).Value;
description=reader.GetSqlString(4).ToString();
itemnumber=reader.GetSqlString(5).ToString();
incidentId=reader.GetSqlGuid(8);
sCustomerPO=reader.GetSqlString(9).ToString();
//add fields

bTime=(double)billableTime;
uPrice=(double)unitPrice;

Console.WriteLine(billableTime);
Console.WriteLine(billableTime.ToString());
Console.WriteLine(unitPrice.ToString());
Console.WriteLine(lineTotal.ToString());
Console.WriteLine(description.ToString());
Console.WriteLine(itemnumber.ToString ());
Console.WriteLine(sCustomerPO.ToString ());
Console.WriteLine();

try
{
if (String.Compare(currentCustomer, accountNumber) != 0)
{
using (eConnectMethods e = new eConnectMethods())
{
//-Update-Create Customer-------------------------------------------------------
//--------------------------------------------------------------------------------
try
{
//Read Customer info from CRM

SqlConnection crmConnection = new SqlConnection();
crmConnection.ConnectionString = crmConnectionString;
crmConnection.Open();

SqlCommand commandCustomer = new SqlCommand();
commandCustomer.CommandType = System.Data.CommandType.Text;
commandCustomer.CommandText = customerSelectString + char39 + accountNumber + char39 + " order by a.AccountNumber";
commandCustomer.Connection = crmConnection;
SqlDataReader readerCustomer = commandCustomer.ExecuteReader();
if (!readerCustomer.Read()) return;
readerCustomer.GetSqlString(0).ToString();
string sName = readerCustomer.GetSqlString(1).ToString();
if (String.Compare(sName, "Null") == 0) sName = "";
NAME = sName;
string sContact = readerCustomer.GetSqlString(4).ToString();
if (String.Compare(sContact, "Null") == 0) sContact = "";
CONTACT = sContact;
string sLine1 = readerCustomer.GetSqlString(5).ToString();
if (String.Compare(sLine1, "Null") == 0) sLine1 = "";
ADDRESS1 = sLine1;
string sLine2 = readerCustomer.GetSqlString(6).ToString();
if (String.Compare(sLine2, "Null") == 0) sLine2 = "";
ADDRESS2 = sLine2;
string sCity = readerCustomer.GetSqlString(7).ToString();
if (String.Compare(sCity, "Null") == 0) sCity = "";
CITY = sCity;
string sState = readerCustomer.GetSqlString(8).ToString();
if (String.Compare(sState, "Null") == 0) sState = "";
STATE = sState;
string sZip = readerCustomer.GetSqlString(9).ToString();
ZIP = sZip;
if (String.Compare(sZip, "Null") == 0) sZip = "";
string sCountry = readerCustomer.GetSqlString(10).ToString();
if (String.Compare(sCountry, "Null") == 0) sCountry = "";

readerCustomer.Close();
commandCustomer.Dispose();
crmConnection.Close();
crmConnection.Dispose();

// Create the customer data file
SerializeCustomerObject("Customer.xml", accountNumber, sName, sContact, sLine1, sLine2, "MAIN", sCity, sState, sZip, sCountry);

// Use an XML document to create a string representation of the customer
XmlDocument xmldoc = new XmlDocument();
xmldoc.Load("Customer.xml");
sCustomerDocument = xmldoc.OuterXml;

// Create an XML Document object for the schema
XmlDocument XsdDoc = new XmlDocument();

// Create a string representing the eConnect schema
sXsdSchema = XsdDoc.OuterXml;

// Pass in xsdSchema to validate against.
e.eConnect_EntryPoint(sConnectionString, EnumTypes.ConnectionStringType.SqlClient, sCustomerDocument, EnumTypes.SchemaValidationType.None, sXsdSchema);

}
// The eConnectException class will catch eConnect business logic errors.
// display the error message on the console
catch (eConnectException exc)
{
Console.Write(exc.ToString());
}

} // end of using statement
//End Update-Create Customer
//-----------------------------------------------------------
//Sales Invoice begins-----------------------
//--------------------------------------------

using (eConnectMethods e = new eConnectMethods())
{
try
{
GetSopNumber mySopNumber = new GetSopNumber();
string SOPNUMBE = mySopNumber.GetNextSopNumber(3, "INV", sConnectionString);

eConnectType eConnect = new eConnectType();

//Read Customer info from CRM

SqlConnection crmConnection = new SqlConnection();
crmConnection.ConnectionString = crmConnectionString;
crmConnection.Open();

SqlCommand commandSalesOrder = new SqlCommand();
commandSalesOrder.CommandType = System.Data.CommandType.Text;
commandSalesOrder.CommandText = timeLogSelectString + char39 + accountNumber + char39 + " order by a.AccountNumber";
commandSalesOrder.Connection = crmConnection;
SqlDataReader readerSalesOrder = commandSalesOrder.ExecuteReader();

taSopLineIvcInsert_ItemsTaSopLineIvcInsert[] items = new taSopLineIvcInsert_ItemsTaSopLineIvcInsert[0];
decimal SUBTOTAL = 0;
int iSO = 0;
while (readerSalesOrder.Read())
{
decimal hours = readerSalesOrder.GetSqlDecimal(0).Value;
string sDescription = readerSalesOrder.GetSqlString(1).ToString();
if (String.Compare(sDescription, "Null") == 0) sDescription = "miscellaneous";
decimal rate = readerSalesOrder.GetSqlDecimal(2).Value;

taSopLineIvcInsert_ItemsTaSopLineIvcInsert item = new taSopLineIvcInsert_ItemsTaSopLineIvcInsert();

hours = Decimal.Round(hours, 2);
rate = Decimal.Round(rate, 2);
if (rate < (decimal)0.01)
rate = (decimal)0.60;

item.SOPNUMBE = SOPNUMBE;
item.SOPTYPE = 3;
item.CUSTNMBR = accountNumber;
item.DOCID = "INV";
item.QUANTITY = hours;
item.ITEMNMBR = "SERVICE";
item.ITEMDESC = sDescription;
item.UNITPRCE = rate;
item.XTNDPRCE = hours * rate;
item.DOCDATE = System.DateTime.Today.Date.ToString();
item.NONINVEN = 1;

//items[iSO] = item;
SUBTOTAL += hours * rate;
iSO++;
if (items.Length < iSO) items = (taSopLineIvcInsert_ItemsTaSopLineIvcInsert[])ExpandArray((Array)items, item);
}
readerSalesOrder.Close();
commandSalesOrder.Dispose();
crmConnection.Close();
crmConnection.Dispose();

SOPTransactionType SopOrder = new SOPTransactionType();
XmlSerializer serializer = new XmlSerializer(eConnect.GetType());

SopOrder.taSopLineIvcInsert_Items = items;

taSopHdrIvcInsert header = new taSopHdrIvcInsert();

header.SOPTYPE = 3;
header.SOPNUMBE = SOPNUMBE;
header.DOCID = "INV";
header.BACHNUMB = "ECONNECT";
header.CUSTNMBR = accountNumber;
header.CUSTNAME = NAME;
//header.ShipToName = "WAREHOUSE";
header.ADDRESS1 = ADDRESS1;
header.CNTCPRSN = CONTACT;
// header.FAXNUMBR = "13125550150";
header.CITY = CITY;
header.STATE = STATE;
header.ZIPCODE = ZIP;
// header.COUNTRY = "USA";
header.SUBTOTAL = SUBTOTAL;
header.DOCAMNT = SUBTOTAL;
// header.USINGHEADERLEVELTAXES = 0;
// header.PYMTRMID = "Net 30";
header.DOCDATE = System.DateTime.Today.Date.ToString();

SopOrder.taSopHdrIvcInsert = header;

SOPTransactionType[] mySOPOrder = { SopOrder };
eConnect.SOPTransactionType = mySOPOrder;

FileStream fs = new FileStream("SalesOrder.xml", FileMode.Create);
XmlTextWriter writer = new XmlTextWriter(fs, new UTF8Encoding());
serializer.Serialize(writer, eConnect);
writer.Close();

XmlDocument xmldoc = new XmlDocument();
xmldoc.Load("SalesOrder.xml");
sSalesOrder = xmldoc.OuterXml;
XmlDocument XsdDoc = new XmlDocument();
sXsdSchema = XsdDoc.OuterXml;
e.eConnect_EntryPoint(sConnectionString, EnumTypes.ConnectionStringType.SqlClient, sSalesOrder, EnumTypes.SchemaValidationType.None, sXsdSchema);

}

catch (eConnectException exc)
{
Console.Write(exc.ToString());
}

}

//Sales Invoice ends-----------------------
//--------------------------------------------
}

{ //Marking case as imported
SqlConnection updateConnection = new SqlConnection();
updateConnection.ConnectionString = connectionString;
updateConnection.Open();

SqlCommand commandUpdate = new SqlCommand();
commandUpdate.CommandType = System.Data.CommandType.StoredProcedure;
commandUpdate.CommandText = "sp_UpdateImportedCase";
commandUpdate.Parameters.Add("@incidentId", SqlDbType.UniqueIdentifier);
commandUpdate.Parameters["@incidentId"].Value = incidentId;
commandUpdate.Connection = updateConnection;
commandUpdate.ExecuteNonQuery();
commandUpdate.Dispose();
updateConnection.Close();
updateConnection.Dispose();
}

}
catch { }
currentCustomer = accountNumber;
}
reader.Close();
command.Dispose();
connection.Close();
connection.Dispose();
}
}

public static void SerializeCustomerObject(string filename, string custnumbr, string name, string cntcprsn,string address1, string address2, string adrscode, string city, string state, string zip, string country)
{
try
{
// Instantiate an eConnectType schema object
eConnectType eConnect = new eConnectType();

// Instantiate a RMCustomerMasterType schema object
RMCustomerMasterType customertype = new RMCustomerMasterType();

// Instantiate a taUpdateCreateCustomerRcd XML node object
taUpdateCreateCustomerRcd customer = new taUpdateCreateCustomerRcd();

// Create an XML serializer object
XmlSerializer serializer = new XmlSerializer(eConnect.GetType());

// Populate elements of the taUpdateCreateCustomerRcd XML node object
customer.CUSTNMBR = custnumbr;
customer.CUSTNAME = name;
customer.CNTCPRSN = cntcprsn;
customer.ADDRESS1 = address1;
customer.ADDRESS2 = address2;
customer.ADRSCODE = adrscode;
customer.CITY = city;
customer.STATE = state;
customer.ZIPCODE = zip;
customer.COUNTRY = country;
customer.CUSTPRIORITY = 1;
customer.CUSTPRIORITYSpecified = true;

// Populate the RMCustomerMasterType schema with the taUpdateCreateCustomerRcd XML node
customertype.taUpdateCreateCustomerRcd = customer;
RMCustomerMasterType[] mySMCustomerMaster = { customertype };

// Populate the eConnectType object with the RMCustomerMasterType schema object
eConnect.RMCustomerMasterType = mySMCustomerMaster;

// Create objects to create file and write the customer XML to the file
FileStream fs = new FileStream(filename, FileMode.Create);
XmlTextWriter writer = new XmlTextWriter(fs, new UTF8Encoding());

// Serialize the eConnectType object to a file using the XmlTextWriter.
serializer.Serialize(writer, eConnect);
writer.Close();
}
// catch any errors that occur and display them to the console
catch (System.Exception ex)
{
Console.Write(ex.ToString());
}
}

//the following function is for demo only, not in use
public static void SerializeSalesOrderObject(string filename)
{
try
{
// Instantiate an eConnectType schema object
eConnectType eConnect = new eConnectType();

taSopLineIvcInsert_ItemsTaSopLineIvcInsert[] items = new taSopLineIvcInsert_ItemsTaSopLineIvcInsert[1];

taSopLineIvcInsert_ItemsTaSopLineIvcInsert item = new taSopLineIvcInsert_ItemsTaSopLineIvcInsert();

item.ADDRESS1 = "2345 Main St.";
item.CUSTNMBR = "CONTOSOL0001";
item.SOPNUMBE = "000001";
item.CITY = "Aurora";
item.SOPTYPE = 3;
item.DOCID = "STDINV";
item.QUANTITY = 2;
item.ITEMNMBR = "ACCS-CRD-12WH";
item.ITEMDESC = "Phone Cord - 12' White";
item.UNITPRCE = (decimal)10.95;
item.XTNDPRCE = (decimal)21.9;
item.LOCNCODE = "WAREHOUSE";
item.DOCDATE = "04/12/2017";// 'Today

items[0] = item;

SOPTransactionType SopOrder = new SOPTransactionType();
XmlSerializer serializer = new XmlSerializer(eConnect.GetType());

SopOrder.taSopLineIvcInsert_Items = items;

taSopHdrIvcInsert header = new taSopHdrIvcInsert();

header.SOPTYPE = 3;
header.SOPNUMBE = "000001";
header.DOCID = "STDINV";
header.BACHNUMB = "ECONNECT ";
header.TAXSCHID = "USASTCITY-6*";
header.FRTSCHID = "USASTCITY-6*";
header.MSCSCHID = "USASTCITY-6*";
header.LOCNCODE = "WAREHOUSE";
header.DOCDATE = "04/12/2017";
header.CUSTNMBR = "CONTOSOL0001";
header.CUSTNAME = "Contoso, Ltd.";
header.ShipToName = "WAREHOUSE";
header.ADDRESS1 = "2345 Main St.";
header.CNTCPRSN = "Joe Healy";
header.FAXNUMBR = "13125550150";
header.CITY = "Aurora";
header.STATE = "IL";
header.ZIPCODE = "65700";
header.COUNTRY = "USA";
header.SUBTOTAL = (decimal)21.9;
header.DOCAMNT = (decimal)21.9;
header.USINGHEADERLEVELTAXES = 0;
header.PYMTRMID = "Net 30";

SopOrder.taSopHdrIvcInsert = header;

// Populate the RMCustomerMasterType schema with the taUpdateCreateCustomerRcd XML node
//customertype.taUpdateCreateCustomerRcd = customer;
//RMCustomerMasterType[] mySMCustomerMaster = { customertype };

SOPTransactionType[] mySOPOrder = { SopOrder };
eConnect.SOPTransactionType = mySOPOrder;

// Populate the eConnectType object with the RMCustomerMasterType schema object
//eConnect.RMCustomerMasterType = mySMCustomerMaster;

// Create objects to create file and write the customer XML to the file
FileStream fs = new FileStream(filename, FileMode.Create);
XmlTextWriter writer = new XmlTextWriter(fs, new UTF8Encoding());

// Serialize the eConnectType object to a file using the XmlTextWriter.
serializer.Serialize(writer, eConnect);
writer.Close();

}
// catch any errors that occur and display them to the console
catch (System.Exception ex)
{
Console.Write(ex.ToString());
}
}
//this function allows us to increase Array length on the fly - we use it when we reading n=lines for Sales Invoice
public static Array ExpandArray(Array arr, object newElement)
{
int length = 1;
System.Type type = newElement.GetType();
if (arr != null)
{
length += arr.Length;
type = arr.GetType().GetElementType();
}
Array result = Array.CreateInstance(type, length);
if (arr != null)
{
arr.CopyTo(result, 0);
}
result.SetValue(newElement, result.Length - 1);
return result;
}
}
}