Microsoft Dynamics CRM and PrestaShop

Hello. In this post, I demonstrate my method of synchronising product stock levels from Microsoft Dynamics CRM with a PrestaShop E-commerce store. 

The problem

I will call the client simply Company X for security reasons. Company X is using Microsoft Dynamic CRM for all its major administration processes. Real time stock information of the products in the warehouse is also stored in the CRM.

Company X is running an outdated e-commerce store which is a candidate for replacement. The biggest concern is that the old website does not have any connection to the CRM therefore stock levels need to be synchronized manually.

The task

Company X asked me to build the new store based on any platform and CMS that is easy enough to learn and operate by anyone in the business. The website need to have an auto-synchronize feature that is updating all stock levels from the CRM. Easy enough huh?

The blueprint of the solution

  • Creating the store using PrestaShop
  • Creating a windows application that downloads the relevant records from the CRM and uploads them remotely to a "middleware" mysql database. (I will explain later why do I think this is the best way.)
  • Finding all relevant fields in PrestaShop's database and update them accordingly.

 

Creating the new website

I would say this part was far the easiest. I installed the latest stable version of PrestaShop and transferred all products, categories and attributes from the old website. I defined the unique reference field in the CRM as to be the link that I can use to match the products. So when copying the products, I used this field as the Reference attribute in PrestaShop.

Some product combinations contain multiple CRM entities. For those I used a space to separate them because some Unique References contain all sorts of special characters.

Creating the windows app

This app does the following:

  • Connect to both CRM and a regular MySQL database on the company hosting server.
  • Retrieve only the fields from CRM that has any meaning in PrestaShop (Unique ID - > Reference, Name -> Name, Quantity -> Quantity)
  • Insert or update the data in the MySQL database (I named it CRMdata and will call it Middleware in the rest of this post)

This was the trickiest part for me because I have never tried to develop anything in .NET before. I am quite familiar with a bunch of programming languages but .NET was somehow always outside of my interest. (Btw.: I could not have used an ASP.NET framework for the website simply because the company already have a linux hosting account and a few other websites using PHP.

So I went to work to find out how to access Dynamics CRM from a .NET application. They both belong to Microsoft so it should be relatively simple I thought. I dedicated a day to find a way to create this app based on forums, stack overflow, youtube and the official documentation. The code is embedded here, I'd like you to take advantage of it if you can for your project.

The idea is that this app is run by a dedicated person whenever needed.

No setup required, so a single button is easy enough to use. The reason behind this approach: to establish the connection with CRM takes quite a bit of a time. If I even find the way how to retrieve data through the CRM rest endpoint from a PHP app, doing it every time when the stock changes takes very long and so it is clearly not a good solution. The data copying takes about 5 minutes alltogether.

The Windows App Interface

The Windows App Interface

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

using MySql.Data.MySqlClient;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Query;
using System.ServiceModel.Description;

namespace CrmCloner
{
public partial class Form1 : Form
{

int counter = 1;

MySqlConnection conn;

string connString;

public Form1()
{
InitializeComponent();
IOrganizationService service = GetCRMservice();
}

public IOrganizationService GetCRMservice()
{
string UserName = "*** Your Username ***";
string Password = "*** Your Password ***";
IOrganizationService service;
ClientCredentials credentials = new ClientCredentials();
credentials.UserName.UserName = UserName;
credentials.UserName.Password = Password;
string SoapOrgServiceUri = "*** Your Dynamic Crm Soap Endpoint ***";
Uri serviceUri = new Uri(SoapOrgServiceUri);
using (OrganizationServiceProxy proxy = new OrganizationServiceProxy(serviceUri, null, credentials, null))
{
service = (IOrganizationService)proxy;
}
//proxy.EnableProxyTypes();
return service;
}


private void GetAllActiveAccounts()
{

/* Connect to mysql database */

connString = "SERVER= *** Your hosting account IP ***;PORT=3306;DATABASE=*** Your database ***;UID=*** Your MySQL username ***;PASSWORD=*** Your MySQL password ***;";
try
{
conn = new MySqlConnection();
conn.ConnectionString = connString;
conn.Open();
label1.Text = "MySql connection succeeded.";
MessageBox.Show("MySql connection succeeded.");
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
label1.Text = "Something went wrong.";
}

string queryEmpty = "TRUNCATE TABLE crmdata;";
MySqlCommand emtyCommand = new MySqlCommand(queryEmpty, conn);
emtyCommand.ExecuteNonQuery();

/* Connect to crm */

IOrganizationService service = GetCRMservice();
QueryExpression querycrm = new QueryExpression("product");
querycrm.ColumnSet.AllColumns = true;

querycrm.Criteria.AddCondition("statuscode", ConditionOperator.Equal, 1);
EntityCollection retrieved = service.RetrieveMultiple(querycrm);
const int servicePageSize = 5000;
int pageNumber = 1;
counter = 0;
string pagingCookie = string.Empty;
const int pageSize = servicePageSize;
int totalRecordsCount = 0;
EntityCollection allRetrieved = retrieved;

do
{
querycrm.PageInfo = new PagingInfo()
{
PageNumber = 1,
Count = pageSize
};

if (pageNumber != 1)
{
querycrm.PageInfo.PageNumber = pageNumber;
querycrm.PageInfo.PagingCookie = pagingCookie;
}



retrieved = service.RetrieveMultiple(querycrm);
int progressBarControl = 0;
int totalRecords = retrieved.Entities.Count;
int incrementValue = totalRecords / 100;

string quantityInStock;
progressBar1.Value = 0;

foreach (Entity entity in retrieved.Entities)
{
counter++;
progressBarControl++;
if (progressBarControl == incrementValue)
{
progressBar1.Increment(1);
progressBarControl = 0;
}

string row;
row = entity.Attributes["*** Attribute you need to use ***"].ToString();
row += entity.Attributes["*** Attribute you need to use ***"] = entity.Attributes["*** Attribute you need to use ***"].ToString();
if (entity.Contains("*** Attribute you need to use ***"))
{
row += entity.Attributes["*** Attribute you need to use ***"].ToString();
quantityInStock = entity.Attributes["*** Attribute you need to use ***"].ToString();
}
else
{
quantityInStock = "0";
}
string productName;
productName = entity.Attributes["*** Attribute you need to use ***"].ToString();
productName = productName.Replace("'", "$");

string productId = entity.Attributes["*** Attribute you need to use ***"].ToString();
productId = productId.Replace("'", "$");
productId = productId.Replace(" ", "#");


string querydb = "INSERT INTO `synchroniser`.`crmdata` (`name`, `product_id`, `quantity`) VALUES ('" + productName + "', '" + productId + "', '" + quantityInStock + "');";
// Create Command
MySqlCommand cmd = new MySqlCommand(querydb, conn);
// Create Data reader and execute command
cmd.ExecuteNonQuery();
Console.WriteLine(row);
}

if (retrieved.MoreRecords)
{
pageNumber++;
pagingCookie = retrieved.PagingCookie;
}

try
{
if (retrieved.Entities.Count > 0)
{
totalRecordsCount += retrieved.Entities.Count;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadLine();
}

} while (retrieved.MoreRecords);

Console.WriteLine("Total records: " + totalRecordsCount);

MessageBox.Show("All Done! " + totalRecordsCount + " entities copied.");
label1.Text = "All Done! " + totalRecordsCount + " entities copied.";
try
{
conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}

private void button1_Click(object sender, EventArgs e)
{
timer1.Start();
label2.Text = "Started. Please wait...";
GetAllActiveAccounts();
}

private void label2_Click(object sender, EventArgs e)
{

}

private void progressBar1_Click(object sender, EventArgs e)
{

}

private void timer1_Tick(object sender, EventArgs e)
{

}
}
}

Using the middleware MySQL database

This database has only the most important columns. (Each product record in the CRM has 122 attributes) The product_id is the Unique Reference in the CRM. The name is there only for debugging purposes. (you will see it later)

The job is to find the relevant products and combinations in PrestaShop's database and update the stock levels to the ones you see on the right.

The middleware database

The middleware database

Updating PrestaShop

From here it is fairly simple. Since PrestaShop is an open source project, thorough documentation is available for it. (along with an active developer community)

After doing my research, I summarized the steps like this:

Prestashop table structure

Prestashop table structure

 

In case you can't read my handwriting (no worries you are not the only one), let me explain it step by step. I used php and mysqli to establish the connection between the 2 databases and run simple queries to do the following steps:

  • Get reference: Take the product reference from the middle ware database.
  • Go to the ps_product_attribute table and use the reference to identify the product in PrestaShop. To do this, get the id_product and id_product fields.
  • Go to ps_stock_available table and update the appropriate fields.

When a product is a combination of 2 CRM entries, only as much product can be sold as we have in stock of both. So if there is 250 in stock of shoestrings but only 50 shoes, we cannot sell any shoe without strings therefore the total available stock of this particular product has to be 50.

The code simply outputs a table to see the result. :

Checking stock levels in PrestaShop

This last step is to check if it has all worked. As you see on the image below, the stock levels have now been successfully updated.

Adding new products

To enable this feature on all new products to be listed in the future, there is only one thing to bear in mind. This is to use the same product reference in Dynamics CRM and PrestaShop. This way it will work perfectly. No further setup needed.

Summary

This unusual problem was quite fun to solve. I hope it helps you with your similar projects. 🙂 If you have any question, please feel free to leave your comment. If you have a similar setup, like this solution and you are looking for a partner to implement it, drop us an email. Thank you.

Write a Reply or Comment

Your email address will not be published. Required fields are marked *