WebShop

From Egbert's Wiki

Introduction

These wiki pages are written during the creation of a real webshop. This production webshop will he hosted with an external webhoster Net Ground in NL. The development and testing webshop is hosted on a local server which is setup in such a way that it resembles the external website as close as possible. the database i.e. has the same name and the WebRoot is also the same (~/httpdocs). Still there are differences that will need to be taken in account; the website name is an example. Moving the website back and forth between development and production has shown some problems, mainly with the images widgets (iDEAL and POSTNL).

More detailed documentation is to be found in the Project Document Speldorado.

How to create a webshop based on WordPress and WooThemes

WordPress is a simple to install web/blog CMS. There are many plugins and themes available. WooThemes makes a lot of themes and several plugins. Some are free, some ar sold for profit but those are not very expensive. The support of the WooThemes developers is absolutely fantastic.

Install

Unpack the WordPress distro. Upload. Configure (needs database and user/password). Login. Now configure the site to your needs. Check Instellingen (Configuration) and all sub-pages.

Extra plugins

Install plugins:

  • Code Styling Localization (free)
  • WooCommerce (free)
  • WooCommerce Product CSV Import Suite (50 Dollar)
  • Pronamic iDEAL (39 Euro)

Install Theme

  • WooTique (free). See Woo Tutorials
  • Switched to Storefront since it is better maintained.
  • Other themes by WooThemes (purchase)

Customizing a Theme

It is highly advisable to make a child-theme from the original theme. See Customizing a Child Theme. This will safeguard the theme through updates. Updates are applied to the parent theme and all modifications in the child theme stay untouched.

Data sources

The main datasource is the "OFB" database on the MS Windows server physically located in the shop. Historically the website was uploaded form here. This should stay the same if possible. Uploading of data to the test/development server is possible with the same tools. Whatever is developed to work om the test/decelopment server, must also work om the production server. The limited access to the production server (no ssh, only plesk panel and ftp) implies that the same limitations must be simulated on the test/development server although full access is available there.

Another part of the data sources is a pool of images. There are about 9000 images of which 4000 match (by there name) to products in the database. There is no referencing field in the database with the name of the images. Matching takes place on product number and image name. To match as much images with product names as possible, product number and image name are converted to lowercase and whitespace etc. is replaced by dashes ("-"). The more images match the better. The ultimate goal is to have max. 7500 products on the site each with their image.

Pre-processing the images is a one-time job. It does not need to be done on the production server. Over time new images may be added to the upload directory, making them available to new products. Corrected imaged must be copied back to the MS Windows server in the shop. Keep in mind that diskspace on the production server is limited and not for free. The hosting contract allows initially for 250 MB but extending this is possible for a modest fee.

Backup/restore

Saving the complete shop is a matter of copying the whole WordPress tree to a temporary location (use WinSCP) and save all tables in the database (using phpmyadmin). To rebuild on another server copy the WordPress tree to the right place and load the tables in the database. Check/correct the wp-config.php file idf needed. When full access is available (the test/development environment with shop.vandenbussche.nl runs on local server), a simple shell scripts can do the backup too.

root@ubuntu:~# cat backup_shop
#!/bin/sh
mysqldump -u speldor1 -pXXXXXXXX db_speldo1 >./shop-backup/shop.sql
tar zcf ./shop-backup/shop.tgz httpdocs
echo "Done"

Scripting to fill the shop

All information about the products to be placed in the webshop is available in a MS Access database called the OFB database. Since this database is part of a third party managed shop system only read access is allowed. Luckily there is an extremely useful program available to convert MS Access to MySQL by Bullzip. this program was used before on the old website too. It is easy to configure. It can selectively convert MSaccess tables/fields to MySQL format and at the same time, upload them to a MySQL database or save them in a mysqldump file.

General flow

An attempt will be made to describe the flow from source to publication.

  • Convert as much images to a unique and unified name. This is an ongoing process and has no direct impact on the webshop. The more images the better...
  • Upload some tables from the shop MS Windows server to the test/development server and/or the production server using the Bullzip tool.
  • Make (temporary) lists of available images/products. Also make a list with products for which is no image yet. These are all flat text files produced by a shell script.
  • Upload (new) images that belong to products to be placed in the webshop. Uploading images which are never referenced is a waist of diskspace and should be prevented.
  • Flag the products in the (uploaded) product table that have a corresponding image. An unused field in the products table is used for this together with one of the temporary files.
  • Above process is slightly changed. Nowadays the images are stored in a special folder in the test site. ALL images are here. The remote site (but also the test site) will try to get the images from this location. The behaviour of WordPress/WooCommerce is such that copies are made to the upload directory with the right thumbnail sizes.
  • Create the CSV which will actually be used to fill the webshop database. This CSV has a standard name to reference the image. In addition to the normal selection ("in collection", "in location", "in web1"), the flag on "image available", is not taken into account anymore when the images are referenced by URL.
  • Import the CSV in WooCommerce using the CSV import/export plugin.

Scripts

To do the conversions needed in ed flow above, several php and shell scripts are used.

convert-speldo-images.php

This script sanitizes the images names and copies them to a directory.

root@ubuntu:~# cat convert-speldo-images.php
<?php

# Remove all images from destination directories.
# Copy images in the source directory with their new name to the destination directory.

$SOURCE = "speldorado-plaatjes/images-mix";
$DEST = "speldorado-plaatjes/images-clean";
$UPLOAD = "speldorado-plaatjes/images-upload";

echo "Alle plaatjes in " . $DEST . " worden verwijderd...\n";
foreach (scandir($DEST) as $item) {
    if ($item == '.' || $item == '..') continue;
    unlink($DEST.DIRECTORY_SEPARATOR.$item);
}

echo "Alle plaatjes in " . $UPLOAD . " worden verwijderd...\n";
foreach (scandir($UPLOAD) as $item) {
    if ($item == '.' || $item == '..') continue;
    unlink($UPLOAD.DIRECTORY_SEPARATOR.$item);
}

echo "Plaatjes worden van " . $SOURCE . " naar " . $DEST . " gekopieerd...\n";
foreach (scandir($SOURCE) as $item) {
    if ($item == '.' || $item == '..') continue;
    $my_file=strtolower(str_replace(array(" ","_"), "-", $item));
        if (!copy($SOURCE.DIRECTORY_SEPARATOR.$item, $DEST.DIRECTORY_SEPARATOR.$my_file)) {
        echo "Failed...\n";
    }
}
?>

There are more ways to do a bulk rename of file in a directory.

find -name "* *" -type f | rename 's/ /_/g'

or a small script:

#!/bin/sh
# lowerit
# convert all file names in the current directory to lower case
# only operates on plain files--does not change the name of directories
# will ask for verification before overwriting an existing file
for x in `ls`
do
if [ ! -f $x ]; then
continue
fi
lc=`echo $x  | tr '[A-Z]' '[a-z]'`
if [ $lc != $x ]; then
mv -i $x $lc
fi
done

images-correct-missing-notused.sh

This script is not used anymore since all sanitized images are made available for download in the special directory. This shell script makes initially 3 files of which 2 are nearly the same. Both contain all image names as derived from the product name in the database but one has also product ID and a short description. The third file contains all image names as found in the directory with all the sanitized image names. The lists must be sorted because they will be used in a "comm" shell command which needs sorted input. See the man page of "comm" if needed.

The list of all images referred from the database and the list of available images are compared and 3 new files are produced:

  • one file containing the images that are refereced from the database and are available in the directory with sanitized images
  • one file with missing images. (Image names from the database NOT found in the images directory. This means that the image might be there but the name is wrong OR the images just isn't there).
  • one file with the images not referenced. This file may contain hints to find errors image names.


Using the "join" commnad two other files are produced which contain not only the image name but also the product ID and a description found the product table.

  • The file with the "correct" images will be used to set the pre-mentioned flag in the database.
  • The other file is a more verbose version of the "missing" file produced earlier. Both are short-lists made out of the full lists of product images in the database.


Finally the images which match the name derived from the database, are copied to the upload directory. From here they can be uploaded to the production website.

root@ubuntu:~# cat images-correct-missing-notused.sh
#!/bin/bash

DEST="speldorado-plaatjes/images-clean"
UPLOAD="speldorado-plaatjes/images-upload"
COUNTER=0

# Make list of supposed image names from the database
# Make list of supposed image names, ID, description from the database
# Make list of (sanitized) image names found in the directory
php select-speldo-images-only.php | sort > images-in-db.out
php select-speldo-images-omschr.php | sort >images-in-db-long.out
ls -1 $DEST | sort > images-in-clean.out

# Make lists of missing, not-used (possibly wrong name) and correctly named images
comm -2 -3 images-in-db.out images-in-clean.out >images-missing.out
comm -1 -3 images-in-db.out images-in-clean.out >images-notused.out
comm -1 -2 images-in-db.out images-in-clean.out >images-correct.out

# Make short-lists with ID and description of missing and correctly named images
join images-missing.out images-in-db-long.out >images-missing-long.out
join images-correct.out images-in-db-long.out >images-correct-long.out

while read line
do COUNTER=$(($COUNTER+1));
  mv $DEST/$line $UPLOAD/$line;
done < "images-correct.out"
echo "$COUNTER plaatjes naar de upload directory verplaatst.";

update-speldo-correct.php

This script is not actively used anymore. Use for historical context. This script writes a "1" to an unused field in the products database for each product with a n accompaning image in the upload directory. This flag is used when the CSV is produced.

root@ubuntu:~# cat update-speldo-correct.php
<?php
$dbase = "roeland";
$tbl01 = "`tbl artikel`";
$tbl02 = "`tblArtVrdOpLokatie`";
$tbl03 = "`tbl artikel subgroep`";
$tbl04 = "`tblArtikelSSG`";

/* Open the database */
$db = mysql_connect("localhost","USER","PASSWORD") or die("Cannot connect: " . mysql_error());
mysql_select_db($dbase,$db);

/* Reset the (unused) artChk1 field */
$sql = "UPDATE $tbl01 SET artChk1=0";
$result = mysql_query($sql);

/* Loop thru list of correct records and set artChk1 to 1 */
$handle = @fopen("images-correct-long.out", "r");
if ($handle) {
    while (($buffer = fgets($handle, 4096)) !== false) {
        $parts = explode(" ",$buffer, 3);
        $sql = "UPDATE $tbl01 SET artChk1=1 WHERE artId=$parts[1]";
        $result = mysql_query($sql);
    }
    if (!feof($handle)) {
        echo "Error: unexpected fgets() fail\n";
    }
    fclose($handle);
}
mysql_close($db);
?>

convert-speldo-to-csv.php

This is the script that produces the CSV file needed for the WooCommerce CSV import plugin. All fields needed are retrieved from the MySQL shop database or are hardcoded fixed strings. The same image name sanitazion is done as before. In this way the images can be uploaded in advance. As soon as the CSV is imported, the referenced imaged will match those in the upload directory. Output is to STDOUT; Redirect to save to a file. This script is not maintained anymore. The outout may differ from the version in the plugin.

Windows

Quite a lot can be achieved also with the free XnView tool for Windows.

TODO

There should be an easy way to unload all products from the Woo database. The import/export CSV plugin can do the initial load and subsequent dates but there is no unload. Currently the following make-clean SQL can be used:

DROP TABLE IF EXISTS shop_posts_copy;
CREATE TABLE shop_posts_copy LIKE shop_posts;
INSERT INTO shop_posts_copy SELECT * FROM shop_posts WHERE post_type='product';
DELETE tp FROM shop_posts tp JOIN shop_posts_copy tpc ON (tp.post_parent=tpc.ID) WHERE tp.post_type='attachment';
DELETE tp FROM shop_posts tp WHERE tp.post_type = 'product';
DELETE pm FROM shop_postmeta pm LEFT JOIN shop_posts tp ON tp.ID = pm.post_id WHERE tp.ID IS NULL;
DELETE tr FROM shop_term_relationships tr INNER JOIN shop_term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id) WHERE tt.taxonomy != 'link_category' AND tr.object_id NOT IN ( SELECT ID FROM shop_posts );
UPDATE shop_term_taxonomy SET count=0 WHERE taxonomy='product_type' OR taxonomy='product_cat';
DROP TABLE IF EXISTS shop_posts_copy;

And much more...