Microsoft,CRM,Time,logs,for,Co computer Microsoft CRM as Time logs for Consulting Business: integrat

Gone are those times when the companies and the organisations didn't need a hi-tech system to handle them. Owing to the considerable increase in the business sector and thus, an enormous increase in the complexity of the organisational struc ----------------------------------------------------------Permission is granted for the below article to forward,reprint, distribute, use for ezine, newsletter, website,offer as free bonus or part of a product for sale as longas no changes a

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 description2.       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 customer3.       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: SerializeSalesOrderObjectBelow 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;        }      }   }