Γέφυρα e-shop με erp – Διασύνδεση erp με e-shop
The 2nd part of the erp to e-shop integration is the reverse process, e-shop to erp bridge, compared to 1st part. But it is more tricky as it cannot be succeeded via rest api. In order to achieve an e-shop to update erp values at tables I used webhooks that are a common feature at CMSs. In wp I enabled webhooks and pointed them to an url. E-shop to erp connection.
What does the php code accomplish – E-shop to erp connection
- When customers add something into their cart or update their profile or make an order an event is triggered
- The software catches the data regarding that event
- It creates database table if it doesn’t already exist with field names and types based on the data
- The table gets populated with the data
COMPLETE GUIDE
The information provided here applies to WC e-shops but different CMS’s have similar functions to interact with external software like this, using events to send data.
- First thing to be done is decision if the software and database will be online or local. The online option means that a cloud like database or erp is easier to interact with an e-shop without having to open ports and do firewall settings changes. But if its desired the database to be stored locally for example if I have a mysql based erp in my local server or pc running for my business then its easy to do it and I have a guide for that to. For web based database the php scripts should be placed into a new folder at the root of the site. I create a folder then I place the software, each php there and I name it as I want. The name of the url that gives access to the php scripts is the url which will be used at the next step. For instance if the php name is order.php and I have put it into a folder named receive then the url for integration is “my site name/receive/order.php”. The php has some lines of code that prevent web access if its not a post type from the e-shop.
- Interaction of the e-shop with external world is done via webhooks. First thing to do is enable webhooks at the cms dashboard. I’ ve done it at wordpress and particurarly woocommerce settings. I can add as many as I want based on the data I prefer to be sent to the external database. Delivery URL must be the one where the php software that I provide lies. Its the one described at the previous step.
Webhooks
e-shop to erp connection
The webhooks send data to the url specified by the administrator of the application into json format. In order to see what is the form of json data sent by the webhook I have made a simple php code that sends an email to my email. I’ve temporarily set the webhook receiving url at the webhook settings when creating it, as url/message.php in order to receive the message. So then I could easily make the desired php code in order to decode the json.
- message.php
<?php
$msg = "message";
if($_SERVER['REQUEST_METHOD']=="POST")
{
$data = file_get_contents('php://input');
$msg .= strval($data);
}
mail("yourmail@.com","message",strval($msg));
?>- The site where the php software is placed must be database enabled that way meaning that there is a mysql database under the domain and access to phpadmin.
- I go to phpadmin into my site and create a database. The name and credentials of it will be needed at a later step of the procedure.
- Now I can place the php scripts provided as follows into the receive folder after I copy the code, paste it into a document processor like ms word or notepad and save the file as “all files” and name of file with .php extension.
- config.php This is used for the connection to the database. If the database is not under the same domain of that where the php’s are placed then I replace “localhost” with the external ip of my database provided by the hosting provider. (That applies also to local databases at local servers or pcs if I externalize my ip). I Copy paste the code into ms word or notepad and save as all files config.php.
<?php
//Configuration for database connection
if ( $_SERVER['REQUEST_METHOD']!="POST" && realpath(__FILE__) == realpath( $_SERVER['SCRIPT_FILENAME'] ) ) {
header( 'HTTP/1.0 403 Forbidden', TRUE, 403 );
die( header( 'location: /error.php' ) );
}
$host = "localhost"; // if database is at same domain then its localhost
$username = " "; // username of your database
$password = " "; // password of your database
$dbname = " "; // database name
$dsn = "mysql:host=$host;dbname=$dbname";
$options = array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
);
?>- order.php This is the webhook receiving url. (url/order.php) that makes the integration. I placed this url/order.php at the settings of the webhook when I created it. This php creates a table “order_webhook” if it doesn’t exist from a previous webhook data sent. Then it populates the table with the data sent from the new order placed in your e-shop. The php code dives into a 3 layer table after it has decoded the original webhook and arranges the data into the table after it assigns appropriate field names on the table fields based on the keys of the original data sent. The function arranging the decoded data is a transformed recursion method with additions in order to produce the right field names. This is one of the php files that need to be placed into the receive folder. I can do the same with the rest webhooks (add to cart webhook, new customer webhook, customer update webhook) provided I change the name of the table created and updated into the following code. The code should be copy pasted into ms word or notepad and saved as all files order.php. The following php file should be placed at the same folder “receive” with the other two php files “config.php” and “error.php”.
<html>
<head>
<title>PHP Test</title>
</head>
<body>
<?php
/* at the top restricts access to only post data from e-shop */
if ( $_SERVER['REQUEST_METHOD']!="POST" && realpath(__FILE__) == realpath( $_SERVER['SCRIPT_FILENAME'] ) ) {
/* To which header to send */
header( 'HTTP/1.0 403 Forbidden', TRUE, 403 );
/* choose the page to redirect */
die( header( 'location: /error.php' ) );
}
if($_SERVER['REQUEST_METHOD']=="POST")
{
$data = file_get_contents('php://input');
$msg .= strval($data);
}
require "config.php";
try {
$connection = new PDO($dsn, $username, $password, $options);
//Alex
function funct($array) {
$line="_";
global $key1;
global $key2;
global $key3;
if (!is_array($array)) {
return FALSE;
}
$result = array();
foreach ($array as $key => $value) {
if (is_array($value)) {
$key1=$key;
foreach ($value as $key => $value1) {
if (is_array($value1)) {
foreach($value1 as $key => $value2){if (is_array($value2)) {
$result = array_merge($result, funct($value2));
}
}
$result = array_merge($result, funct($value1));
}
}
$result = array_merge($result, funct($value));
}
else {
if (is_null($key1)){
$result[$key] = $value;
}
else{
$key=$key1.$line.$key;
$result[$key] = $value;}
}
}
return $result;
}
//Alex
$data1=json_decode($data, true);
$data2 = funct($data1);
print_r($data2);
//Alex
$createtable = "CREATE TABLE IF NOT EXISTS order_webhook";
$createtable .= "(";
$createtable .= "f_id INT NOT NULL AUTO_INCREMENT,";
foreach($data2 as $dataKey => $dataValues)
{
$getDataType = gettype($dataValues);
if($getDataType == 'integer')
{
$createtable .= "".$dataKey." int(11) DEFAULT NULL, ";
}
elseif($getDataType == 'double')
{
$createtable .= "".$dataKey." float DEFAULT NULL, ";
}
elseif($getDataType == 'boolean')
{
$createtable .= "".$dataKey." tinyint(2) DEFAULT NULL, ";
}
else
{
$createtable .= "".$dataKey." varchar(140) DEFAULT NULL, ";
}
}
$createtable .= "PRIMARY KEY ( f_id )";
$createtable .= ")";
$createtable .= "COLLATE='utf8mb4_unicode_ci'ENGINE=InnoDB";
$statement = $connection->prepare($createtable);
$statement ->execute();
$sql = sprintf(
"INSERT INTO %s (%s) values (%s)",
"order_webhook",
implode(", ", array_keys($data2)),
":" . implode(", :", array_keys($data2))
);
$statement1 = $connection->prepare($sql);
$statement1 ->execute($data2);
}catch(PDOException $error) {
echo $createTableStatement . "<br>" . $error->getMessage();
}
?>
</body>
</html>
After implementing all 3 php’s (config.php, order.php and error.php), when a customer places a new order into the e-shop this results into the following:
Database of erp (Web based) before order placement (there is no order_webhook table)
After the order placement the table “order_webhook” has been created
The field names of the table are automatically named based on the data send by the order
The fields that are at second layer array get automatically a complex name (“billing_first_name”, “billing_last_name”) and not a key with a numerical value as the simple recursion method would produce.
- error.php Where it’s redirected if an error occurs. The code should be copy pasted into ms word or notepad and saved as all files error.php.
<html>
<head>
<title>PHP Test</title>
</head>
<body>
<?php
echo "error";
?>
</body>
</html>
Γέφυρα e-shop με erp – Διασύνδεση erp με e-shop
These php’s were named exactly as mentioned. Then placed into receive folder. This can be established in a local mysql database also.
Feel free to comment or contact me. I am available to provide you with any further information should it be required.
