Entries tagged mysql

Fix shipping providers for FBA orders in Plentymarkets

Posted on 27. Februar 2017 Comments

The popular ERP Plentymarkes let’s you send your orders via the Fulfillment by Amazon program to your customers. Amazon is just used as a logistics partner, the order doesn’t have to come via Amazon. It could e.g. be a Rakuten or eBay order. The problem that Plentymarkets didn’t address at all and is discussed in the private forums, is that Amazon chooses whatever shipping company they seem fit.

Every order has a standard shipping provider and with it comes a URL for tracking the package. Most of the vendors would implement an event procedure („Ereignisaktion“) that sends an email to the customer containing the order and the TrackingID as soon as it’s shipped. The TrackingIDs are directly imported from Amazon. Unfortunately Plentymarkets does not change the shipping provider in case it’s not the default one (which is often the case). So the customer gets wrong information.

In one of last last blogposts I introduced a PHP package to check which shipping provider a TrackingID belongs to by either scraping their website or using a regular expression and filtering for common patterns. In this blog post I want to explain how to use it to fix those orders. You will need a webserver with PHP and MySQL and a little knowledge of programming and Linux.

You can find some simplified code examples on GitHub soon.

  1. Take your FBA orders out of the event procedures that sends the email to the customers, filter e.g. via WarehouseID
  2. Instead, use an event procedure to mark those orders with a flag, e.g. a star (ID 2)
  3. Create a DynamicExport of the type OrderComplete. Filter for your Sent Status (usually 7) and WarehouseID of FBA orders. Include these fields:
    1. OrderID
    2. OrderPackageNo
    3. OrderParcelServiceID
    4. OrderParcelServicePresetID
    5. OrderLastUpdateTimestamp
  4. Implement the SOAP Call GetDynamicExport (will be replaced by REST via Plugin mid-2017)
  5. Write a Cronjob that calls the GetDynamicExport script with the FormatID of the DynamicExport from #2
  6. Create a database table called ‚pm_shipping_providers_check‚ with the fields from #2
  7. Write a script that imports the CSV you downloaded with the GetDynamicExport call into your MySQL database
  8. Create another DynamicExport, this time of the type Order. Include these fields:
    1. OrderID (Synchronisation)
    2. ParcelServiceID (Import)
    3. ParcelServicePresetID (Import)
    4. PackageNo (Import)
  9. Create a table with these fields called ‚pm_shipping_providers_correction
  10. Create a script, that uses the shipping-service-provider-check library to check the every PackageNo in ‚pm_shipping_providers_check‘ and – if the provider is different from the default one – writes it into ‚pm_shipping_providers_correction‘
  11. Implement the SOAP Call SetDynamicImport (see #3)
  12. Write a script that exports the table ‚pm_shipping_providers_correction‘ into a (semicolon seperated) CSV.
  13. Create a cronjob that uploads the file regularly
  14. Create an event procedure on the event PackageNo that sends out emails, but only for the previously flagged orders

Flattr this!

MySQL Workbench Error: line contains NULL byte

Posted on 21. April 2016 Comments

When importing CSV (or other) files into the database, scripts (especially PHP or C-related languages), will stop if there is – for whatever reason – a NULL byte in your file because it signals end of file/string, see Null bytes related issues.

So when importing a file like that with MySQL Workbench you will get this error:

line contains NULL byte

You can solve this by using the commandline tool tr (from coreutils):

tr < file-with-nulls -d '\000' > file-without-nulls

To check if there are any null bytes in your file, use the python IDE and type in:

open('filename.ext').read().index('\0')

Thanks to Pointy from Stackoverflow

Flattr this!

Collect currency exchange rates in a MySQL database with PHP and fixer.io API

Posted on 4. August 2015 Comments

If you work in a company that buys and sells goods in many different currencies, it might be a good idea, to use the latest exchange rates. Also, it might be useful, to store old exchange rates to clarify/verify old business decisions. If once a day is enough for you, fixer.io offers a free simple Rest API. A lot of the code at my work is written in PHP but I usually use the request library in JavaScript and Python, so I’m using it in this example too. A common PHP solution would be guzzle. But first, get composer (the PHP counterpart to npm or pip):

$ curl -sS https://getcomposer.org/installer | php

$ php composer.phar require rmccue/requests

The mysql_ commands are deprecated (and removed in PHP 7), use mysqli or PDO. Also you should use some sort of framework for the database access, like medoo or a proper ORM. This is just proof of concept.


$base = 'EUR';
$request = Requests::get('https://api.fixer.io/latest?base=' . $base, array('Accept' => 'application/json'));
if ($request->status_code == 200) {
$response = json_decode($request->body);
$GBP = $response->rates->GBP;
$CAD = $response->rates->CAD;
$USD = $response->rates->USD;
$NOK = $response->rates->NOK;
$CNY = $response->rates->CNY;
$rBase = mysql_real_escape_string($response->base);
$date = mysql_real_escape_string($response->date);
$currencies = mysql_real_escape_string("1.0, $USD, $GBP, $NOK, $CNY, $CAD");
$qry = "INSERT INTO `exchange_rates_fixerio`(date, base, eur, usd, gbp, nok, cny, cad) VALUES ('$date', '$rBase', $currencies);";
$insert = mysql_query($qry, $mysqlConnection) or print mysql_error();
}

I assume the database connection is defined earlier, there’s lot’s of documentation for that. Because we are from Europe, I chose Euro (EUR) as the base currency. Apart from the get() method, you need nothing else, to send a request. If the request returns an OK(200), the response is read and saved into different variables, e.g. for British Pounds, US Dollar, Canadian Dollar, Chinese Renminbi and Norwegian Krone. Just to make sure we have the right base, it’s also parsed. From there it’s only a simple INSERT INTO (as said before, use a framework for that)

The table could look like this:

CREATE TABLE `echange_rates_fixerio` (
`date` date NOT NULL,
`base` varchar(3) NOT NULL,
`eur` double NOT NULL,
`usd` double NOT NULL,
`gbp` double NOT NULL,
`nok` double NOT NULL,
`cny` double NOT NULL,
`cad` double NOT NULL
)

 

You can also find this code on GitHub.

Flattr this!

Automize selling at LaRedoute #2: Parse order files

Posted on 16. Dezember 2014 Comments

This blog post is part of the series Automize selling at LaRedoute.

  • Part 1: Get new orders
  • Part 2: Parse order files
  • Part 3: Upload response files
  • Part 4: update quantity and price feed

Update 2016: La Redoute is going to stop using CSV and moves everything to SOAP Webservices. Tutorials will follow


In part 1 the files containing orders are downloaded into a folder called OrdersFromLaRedoute. The next script is going to go through that folder, parse the file and insert it into a table.

These are the values the table must have because we’re just going to insert everything that’s in the CSV files.


$dbValues = ['MarketplaceID', 'OrderID', 'StorefrontOrderID', 'OrderDate', 'BuyerEmailAddress', 'BuyerName', 'BuyerPhoneNumber', 'OrderItemCode', 'ItemStatus', 'SKU', 'Title', 'Quantity', 'ItemPrice', 'ItemTax', 'ShippingCharge', 'ShippingTax','ItemFee', 'Currency', 'ShippingOption', 'PaymentInfo', 'ShippingAddressName', 'ShippingAddressFieldOne', 'ShippingAddressFieldTwo', 'ShippingAddressFieldThree', 'ShippingCity', 'ShippingStateOrRegion', 'ShippingPostalCode', 'ShippingCountryCode', 'ShippingPhoneNumber', 'BillingAddressName', 'BillingAddressFieldOne', 'BillingAddressFieldTwo', 'BillingAddressFieldThree', 'BillingCity', 'BillingStateOrRegion', 'BillingPostalCode', 'BillingCountryCode', 'BillingPhoneNumber'];

 

Therefore I created the table more or less like this:

CREATE TABLE `ORDERS-HISTORY` (
`MarketplaceID` int(11) DEFAULT NULL,
`OrderID` varchar(50) DEFAULT NULL,
`StorefrontOrderID` varchar(50) DEFAULT NULL,
`OrderDate` varchar(50) DEFAULT NULL,
`BuyerEmailAddress` varchar(50) DEFAULT NULL,
`BuyerName` varchar(50) DEFAULT NULL,
`BuyerPhoneNumber` varchar(50) DEFAULT NULL,
`OrderItemCode` varchar(50) NOT NULL DEFAULT '',
`ItemStatus` varchar(10) NOT NULL DEFAULT '',
`SKU` int(11) DEFAULT NULL,
`Title` varchar(50) DEFAULT NULL,
`Quantity` int(11) DEFAULT NULL,
`ItemPrice` decimal(8,2) DEFAULT NULL,
`ItemTax` decimal(8,2) DEFAULT NULL,
`ShippingCharge` decimal(8,2) DEFAULT NULL,
`ShippingTax` decimal(8,2) DEFAULT NULL,
`ItemFee` decimal(8,2) DEFAULT NULL,
`Currency` varchar(3) DEFAULT NULL,
`ShippingOption` varchar(10) DEFAULT NULL,
`PaymentInfo` varchar(50) DEFAULT NULL,
`ShippingAddressName` varchar(70) DEFAULT NULL,
`ShippingAddressFieldOne` varchar(70) DEFAULT NULL,
`ShippingAddressFieldTwo` varchar(70) DEFAULT NULL,
`ShippingAddressFieldThree` varchar(70) DEFAULT NULL,
`ShippingCity` varchar(70) DEFAULT NULL,
`ShippingStateOrRegion` varchar(70) DEFAULT NULL,
`ShippingPostalCode` int(11) DEFAULT NULL,
`ShippingCountryCode` varchar(3) DEFAULT NULL,
`ShippingPhoneNumber` varchar(50) DEFAULT NULL,
`BillingAddressName` varchar(70) DEFAULT NULL,
`BillingAddressFieldOne` varchar(70) DEFAULT NULL,
`BillingAddressFieldTwo` varchar(70) DEFAULT NULL,
`BillingAddressFieldThree` varchar(70) DEFAULT NULL,
`BillingCity` varchar(70) DEFAULT NULL,
`BillingStateOrRegion` varchar(70) DEFAULT NULL,
`BillingPostalCode` int(11) DEFAULT NULL,
`BillingCountryCode` varchar(3) DEFAULT NULL,
`BillingPhoneNumber` varchar(50) DEFAULT NULL,
PRIMARY KEY (`OrderID`,`OrderItemCode`,`ItemStatus`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

As you can see, the primary key consists of OrderID, OrderItemCode and ItemStatus. An OrderID is the unique identifier of one order, consisting of possibly many but at least one OrderItemCode. An OrderItemCode is representing an SKU + Quantity. For newly created orders, the ItemStatus will appear as „Created“. Once an item is accepted, LaRedoute will put a file into the ToSupplier folder with exactly the same OrderID, OrderItemCode but the ItemStatus „ToShip“. This will be important in Step 3.

Then, use a CSV library like league/csv and insert it with e.g. medoo.


$dirAsArray = scandir("OrdersFromLaRedoute");

foreach($dirAsArray as $file) {
// parse and INSERT
}

Flattr this!

Automize selling at LaRedoute #1: Get new orders

Posted on 16. Dezember 2014 Comments

This blog post is part of the series Automize selling at LaRedoute.


The french marketplace LaRedoute unfortunately doesn’t have a real API, but they do have ways to automize some processes. A lot of smaller marketplaces have this concept as well. You will get credentials for an SFTP server. On this server you will find the folders ToSupplier and FromSupplier, where the „supplier“ (aka you) can up- and download a range files documented by Merchantry in their blog. The processing of the uploaded files can take up to 6 hours, but is sometimes done in only a couple of minutes, so I’m going to assume the worst case of 6 hours in this post.

While programming a couple of scripts I found the following problems:

  • the server is incredibly slow sometimes (better at nights), so sometimes the connections just time out
  • sometimes the listing for the ToSupplier folder times out because there are too many files (according to support…huh?), so they have to be deleted regularly
  • not only the connection to LaRedoute but also the connection to my local MySQL server times out
  • I have to reserve a purchased item once I accepted it on LaRedoute immediately, because it could be sold elsewhere in the 6 hours LaRedoute might take to give me the shipping address

New orders can be found in the ToSupplier folder in tab seperated CSV files (but .txt ending) with the format OrdersYYYY-MM-DD-hh-mm-ss.txt.

Since PHP is the companies main language I will show a couple of scripts which automize downloading and processing those files. The code is of course simplified for better understanding. We’re using SFTP instead of FTP and I found using the phpseclib to be the most usable library.

I will propose the use of 2 Tables in the MySQL database: TEMP-FILENAMES and FILENAMES-HISTORY. Both have a the unique column filename. FILENAMES-HISTORY will contain the name of every file ever processed by the following script, TEMP-FILENAMES is a helper table that will be truncated after every run.

First, we need to establish a connection


$sftp = new Net_SFTP(SFTP_LAREDOUTE_HOST);
if (!$sftp-&gt;login(SFTP_LAREDOUTE_USER, SFTP_LAREDOUTE_PASS)) {
exit('Login Failed');
}

Then we change directory. This is a command that usually involves listing the directory changed into, but since this is not a graphical client, the real timeout might come on line below. $nlist will just be null if the listing fails, and I will assume it didn’t work if it takes more than 30 seconds.

$sftp->chdir('/ToSupplier');

$beforetime = time();
$nlist = $sftp->nlist();
$aftertime = time();
if(($aftertime-$beforetime) > 30 ) {
exit('Timeout while Listing directory');
}

The next piece of code is only executed if the listing worked. Every filename that includes the word „Order“ is now inserted into the temporary table:

foreach($nlist as $filename) {
if (strpos($filename, 'Order') !== false) {
$qry = "INSERT INTO `TEMP-FILENAMES`(`filename`) VALUES ('". $filename . "')";
$insert = mysql_query($qry,MYSQLCONNECTION) or print mysql_error();
}
}

You can look at the difference between the filenames in your HISTORY table and the possibly new ones in the temporary table.

$tmpCmpFilenames = array();
$qry = "SELECT `filename` FROM `TEMP-FILENAMES` WHERE `filename` NOT IN (SELECT `filename` FROM `FILENAMES-HISTORY`)";
$select = mysql_query($qry, MYSQLCONNECTION) or print mysql_error();
while ($row = mysql_fetch_assoc($select)) {
$tmpCmpFilenames[] = $row['filename-id'];
}

Now we have all the new files in the array $tmpCmpFilenames. The correct way would be make sure the downloaded files are correct with hashes. Instead we decided to misuse the filesize, since it’s a good indicator something didn’t work properly;) The files not downloaded correctly are deleted from the array. They will appear next time the script is run.

foreach($tmpCmpFilenames as $filename) {
$remotefilesize = $sftp->size($filename);
$sftp->get($filename, 'OrdersFromLaRedoute/' . $filename);
$localfilesize = filesize('OrdersFromLaRedoute/' . $filename);
if ($remotefilesize != $localfilesize) {
unset($tmpCmpFilenames[$filename]);
}
}

We can now insert the filenames into the HISTORY table.

foreach($tmpCmpFilenames as $filename) {
$qry = "INSERT INTO `FILENAMES-HISTORY`(`filename`) VALUES ('". $filename . "')";
$insert = mysql_query($qry, MYSQLCONNECTION) or print mysql_error();
}

Last but not least, the temporary table needs to be truncated for the next run.

$truncate=mysql_query("TRUNCATE TABLE `TEMP-FILENAMES`",MYSQLCONNECTION) or print mysql_error();

The next step is described in part 2 of this series.

Flattr this!

MySQL String Vergleiche in WHERE Klausel: Groß- und Kleinschreibung

Posted on 9. September 2014 Comments

MySQL unterscheidet standardmäßig nicht zwischen Groß- und Kleinschreibung, außer in speziellen Zeichensätzen. Bei z.B. UTF-8 muss man bei String Vergleichen in der WHERE Klausel deshalb immer das Schlüsselwort BINARY benutzen, wenn man Strings auf exakte Gleichheit überprüfen möchte. Dies kann z.B. praktisch sein beim Korrigieren von Rechtschreibfehlern oder der Groß- und Kleinschreibung von Usernamen. Ein SELECT Statement zum Prüfen von Ungleichheit sieht z.B. dann so aus:

SELECT feld FROM tabelle WHERE BINARY zeile1 <> zeile2;

Ist Groß- und Kleinschreibung irrelevant bietet sich immer an Vergleiche von Strings prinzipiell mit einem UPPER() zu versehen, um alle Buchstaben groß zu schreiben, vor allem, wenn man nicht sicher ist, welchen Zeichensatz man verwendet. Ein SELECT Statement würde dann so aussehen:

SELECT feld FROM tabelle WHERE UPPER(zeile1) <> UPPER(zeile2);

Flattr this!

MySQL Verbindungen in C++ mit mysqlpp

Posted on 6. November 2013 Comments

MySQL Server und Header installieren:

sudo apt-get install mysql-server  libmysqlclient-dev libmysql++-dev

In Eclipse C++-Projekt anlegen, Rechtsklick auf das Projekt:

Properties>C/C++-Build>Settings>Tool Settings>GCC C++ Compiler>Includes
Dort die Pfade /usr/include/mysql und /usr/include/mysql++ als Include paths eintragen.

cpp_mysql1

 

Weiter unten unter GCC C++ Linker>Libraries>Libraries „mysqlpp“ eintragen und unter Library search paths ebenfalls /usr/include/mysql und /usr/include/mysql++.

cpp_mysql2

 

Unter Miscellaneous dann noch bei Other objects die Datei /usr/lib/x86_64-linux-gnu/libmysqlclient.so angeben:

cpp_mysql3

 

Beispieldatei: mysqlpptest.cpp

 

Credits: C++ with a shot of MySQL on Ubuntu 10.10

Flattr this!

Owncloud News Reader Error after update to 5.0.11

Posted on 21. Oktober 2013 Comments

After updating to owncloud 5.0.11 I got the following error in my Owncloud News Reader:

Screenshot_2013-10-21-10-20-56

(For Google: A Toast saying „End of input at line 1 column 1“)

In my owncloud instance, I got an error in the administrator menu saying:

SQLSTATE[HY000]: General error: 1 no such column: feeds.articles_per_update at db.php#391

I updated the owncloud instance and app framework/news app but it still showed me that everything was uptodate, even when 5.0.12 was released. Via phpMyAdmin I could see that the feeds were still in the database but I couldn’t see them in the web app or Android app. So I just deleted the owncloud folder, downloaded the new .tar.bz2 from owncloud.org, removed the oc_news tables(do not delete e.g. the contacts or bookmarks!). Then I changed my username in oc_users, used my old username as my new username and reinstalled owncloud, this time version 5.0.12.  I could then delete the old (changed) username via the admin panel.

This is the third time I had to reinstall owncloud, because the update mechanism didn’t work properly… And yes, as advised, I did deactivate the news app and app framework for the update process 😉

I just had a look at the SQL-statements from my last backup and saw that articles_per_update is supposed to be a column in oc_news_feeds in the new version of the news app. Somehow, the owncloud update process didn’t update the news app or didn’t trigger a 3rd-party update process. Anyways, I guess this could also be solved by just adding this column manually:
`articles_per_update` bigint(20) NOT NULL DEFAULT '0'

Flattr this!

Simple MRBS Output from Database

Posted on 17. September 2013 Comments

I wrote a little PHP-script that extracts the most important and current data from MRBS’s MySQL database and just prints them in a simple HTML file. Its not pretty but good for bandwith and clarity. We use it to display information on an energy efficient e-ink screen in front of conference/computer rooms.

$date is set like this:

$date = date("Y-m-d");

The SELECT-statements go like this, where N is the ID of the room(in the database, not what you might call it)

SELECT name,description,from_unixtime(start_time),from_unixtime(end_time) FROM mrbs_entry WHERE from_unixtime(start_time) LIKE '%" . $date . "%' AND room_id = N;

I’m not gonna explain here how to establish a database connection in PHP, but you can have a  look at the whole sourcecode at github 😉

I then cut off the date with substr() because it’s always 10 characters in front of time->(YYYY-MM-DD) and printed it via echo. This is the simple version, there is a version with tables on github.

while($row=mysql_fetch_row($qry01)) {
echo "- <strong>Room</strong> 01 | <strong>Desc:</strong>" . $row[0]." - ".$row[1]." |  <strong>Time:</strong> ". substr($row[2],10) . " - " . substr($row[3],10) . " <br />";
}

update: I wrote another version that prints JSON objects which can be parsed on the other side. For easier parsing(e.g. if everything is in one String) I numbered them:

$jsonarray = array('begin' . $i => substr($row[2],11, 5), 'end' . $i => substr($row[3],11,5), 'name' . $i => $row['name'], 'description' . $i =>  $row['description']);

You’ll find the whole thing on github.

Flattr this!

Scuttle Bookmarks in Owncloud App

Posted on 21. Juni 2013 Comments

Ich habe ein kleines PHP-Skript geschrieben, was aus einer Scuttle Installation die Bookmarks ausliest und in die Owncloud Bookmark App einfügt.

In der jetzigen Version wird nur MySQL (noch kein SQLite) unterstützt und aus  einem mir nicht erklärlichen Grund funktionieren die Tags nicht. Vielleicht kommt ja jemand dahinter.

Den Quellcode findet man auf github.

Flattr this!