MIS Intergration

[Christopher Yates]

I have been working with XMPIE for some time and now we are looking to integrate uStore with our MIS solution, but the only way to do this is to use a web service and insert the information into the SQL database of the MIS system.  Does anyone have an example I could look at to see the easiest way to send the XML document to a web service and pull the information from the XML document and insert it into an SQL database?

Any help would be greatly appreciated as one of our customers wants us to track all order either through uStore or Sales and give them a report at the end of the month.

Thanks,

Chris

[couch]

Out of the box, uStore has two XML templates for exporting order details to external systems. It is possible to duplicate and modify the templates which are made with a SQL stored procedure, so good SQL knowledge is required.

The XML can be sent to a web service, or folder which is watched for incoming orders to add to the external system. 

It is very easy to create a trigger to export the XML to a folder and see what is available, and then adjust the template, and when ready change the trigger to send to your web service.

Triggers are created in uStore admin -> Presets -> Trigger setup.

Create a new trigger, and set:

Event: Order submission in customer application

Filter: order details filter

Select the store or stores that you want to export order data for

Set the location to save the file

Select the template (default will be either XML or cXML until you create your own custom templates)

[Yale Schachter]

Hi Chris,

As Couch mentioned in his reply, once you set up the trigger you can export the XML file. The trick then is to what fields in the XMPie XML file match the fields in the SQL database in the MIS solution.

I have a couple of VBScripts that I wrote, that are scheduled to run every few "clock units" using a free tool (called Splinterware Scheduler) that looks for any XML files to process. In the first case, I convert the XML file to a CSV file with specific headers to match the needs of a a program called Transaction Pro. Transaction Pro uses the CSV and in real time updates a QuickBooks database. 

In the 2nd script it takes the XML data and extracts certain fields and builds a SOAP call to a web based tracking software.

So a lot depends on what web service or real time update capability is available for the MIS system.

Let me know if I can be of any help getting your customer's request going.

Yale

[Christopher Yates]

Yale,

  Do you have an example of the SOAP Call you are using.  This is where I am getting stuck.

Chris

[Christopher Yates]

I know how to trigger the XML file my problem is getting the Web Service to ingest the XML file so that I can use the information within the triggered XML file.  I wondered if someone had an example of what they used so I could get an idea of what I needed to do in the webservice to pull the information from the XML file that is sent to the Web Service.

[couch]

[WebMethod]

        public void Execute(XmlDocument orderXml)

        {

            //extract the encrypted order ID from the xml file

            XmlNode node = orderXml.SelectSingleNode("//OrderXml/Order");

            string orderNumber = node.Attributes["DisplayOrderId"].Value;

            //this can also be done this way

            //XmlNode node = orderXml.SelectSingleNode("//OrderXml/Order/@DisplayOrderId");

            //XmlAttribute attr = (XmlAttribute)node;

            //string orderNumber = attr.Value;

            //do something with the data

            try

            {

                //insert the orderNumber and other fields into the MIS system using SQL or some other web service call... 

            }

            catch 

{ 

   // report or log any error messages

}

        }

[Christopher Yates]

Couch,

  Thank you for the help.  When I get the full WebService Written I will attach it hear for others to use. 

Chris

[Mark Angeli]

Chris, just wondering if you've made any progress with this.

We're in the process of trying integrate xmpie with the MIS system (based in NAV) that we're implementing and would love to chat with you.

Mark

[Christopher Yates]

Mark,

  I have not been able to finish working on this project as I got pulled away to work on multiple CrossMedia Campaigns that we took on to help out some local universities.  I'm going to start working on it again this week and once I have it up and working I will posted my results here for everyone, since I know we need documentation somewhere on how to do this for all XMPIE users.

Thanks,

Chris Yates

--
You received this message because you are subscribed to a topic in the Google Groups "XMPie Interest Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/xmpie-users/gmDPavg9rHc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to xmpie-users+unsubscribe@googlegroups.com.
To post to this group, send email to xmpie...@googlegroups.com.
Visit this group at https://groups.google.com/group/xmpie-users.
For more options, visit https://groups.google.com/d/optout.

[Mark Angeli]

Thanks Chris!

To unsubscribe from this group and all its topics, send an email to xmpie-users...@googlegroups.com.

[Christopher Yates]

Mark,

  Here is my code that I have developed so far.  Hope this gives everyone out there an idea of how this works.  I'm working on the rest of it over the next week and will post the final outcome once I have it complete so anyone else needing the code can use it.  I will be changing this so that it calls a stored procedure instead of doing the insert directly from the Web Service.  Hope this helps.  Chris

---------------------------------------------------------------------------

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Services;

using System.Data.SqlClient;

using System.Data;

using System.Xml;

using System.Diagnostics;

using System.Web.Services.Protocols;

using System.Diagnostics.Eventing;

namespace MSIintegration

{

    /// <summary>

    /// Summary description for Service1

    /// </summary>

    [WebService(Namespace = "http://tempuri.org/")]

    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

    [System.ComponentModel.ToolboxItem(false)]

    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 

    [System.Web.Script.Services.ScriptService]

    public class Service1 : System.Web.Services.WebService

    {

        System.Diagnostics.EventLog eventlog = new System.Diagnostics.EventLog();

        [WebMethod]

        public void Execute(XmlDocument orderXml)

        {

            //extract the encrypted order ID from the xml file

            XmlNode node = orderXml.SelectSingleNode("//OrderXml/Order");

            string orderNumber = node.Attributes["DisplayOrderId"].Value;

            XmlNode nentrydate = orderXml.SelectSingleNode("//OrderXml/Order/OrderProducts/OrderProduct");

            string NickName = nentrydate.Attributes["Nickname"].Value;

            XmlNode Des = orderXml.SelectSingleNode("//OrderXml/Order/OrderProducts/OrderProduct/Product");

            string Desc = Des["Name"].InnerText;

            XmlNode tp = orderXml.SelectSingleNode("//OrderXml/Order/Prices");

            string totalprice = tp["TotalPrice"].InnerText;

            XmlNode qty = orderXml.SelectSingleNode("//OrderXml/Order/OrderProducts/OrderProduct/Quantities");

            string quantity = qty["TotalUnits"].InnerText;

            

            //Hard Code

            var sides = 1;

            var width = 3.5;

            var length = 2;

            

            try

            {

                                

                SqlConnection con = new SqlConnection(@"Data Source=192.168.0.6;Initial Catalog=Sample;User ID=********;Password=*****************");

                con.Open();

                // SqlCommand cmd = new SqlCommand("insert into sample(OrderID,Price) values (@name,@father)", con);

                SqlCommand cmd = con.CreateCommand();

                cmd.CommandType = CommandType.Text;

                cmd.CommandText = "insert into Sample(OrderID,Price,Description,TotalPrice,Qty,Width,Length,Sides) values(@name,@father,@Description,@TotalPrice,@Qty,@Width,@Length,@Sides)";

                cmd.Parameters.AddWithValue(@"name", orderNumber);

                cmd.Parameters.AddWithValue(@"father", NickName);

                cmd.Parameters.AddWithValue(@"Description", Desc);

                cmd.Parameters.AddWithValue(@"TotalPrice", totalprice);

                cmd.Parameters.AddWithValue(@"Qty", quantity);

                cmd.Parameters.AddWithValue(@"Width", width);

                cmd.Parameters.AddWithValue(@"Length", length);

                cmd.Parameters.AddWithValue(@"Sides", sides);

                cmd.ExecuteNonQuery();

                con.Close();

            }

            catch (Exception e)

            {

                string error = e.ToString();

                //Set source name

                eventlog.Source = "MIS Integration";

                //Create an Event ID

                int eventID = 8;

                //eventlog write entry

                eventlog.WriteEntry(error, System.Diagnostics.EventLogEntryType.Error, eventID);

                //close log

                eventlog.Close();

            }

        }

        

    }

}