Microsoft CRM as Time logs for Consulting Business: integration with Great Plains

Feb 6 06:57 2008 Andrew Karasev Print This Article

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,Guest Posting 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;

        }

      }  

}

Source: Free Guest Posting Articles from ArticlesFactory.com

About Article Author

Andrew Karasev
Andrew Karasev

Andrew Karasev, Pegas Planet http://www.pegasplanet.com, subdivision of Alba Spectrum Group: http://www.albaspectrum.com , providing media publishing services: articles, press releases, video and photo blogs distributions services in USA and internationally.  Taisia Karaseva is the author of Pegas Planet concept. You can submit your video article here: http://www.pegasplanet.com/SubmitNews/SubmitNews.aspx 

View More Articles