Importing Web-shop orders into EESys 

EESys includes some database processes to import data entered by customers on the web-shop into EESys so the orders can be picked in the warehouse and dispatched.
How to use these features and how they work is described here.

File format & location required from the Webshop

EESys imports data from "CSV" (Comma Separated Value) files. These are reasonably easy to create in the webshop. The file should be laid out with all the order details on each line separated by product. Each line of the CSV file should include the customer's order-number and their address and other details, plus information about one product purchase. A single order may consist of one or more lines.

The CSV File should be saved to the "Database\Stores\Imports" folder on the Server, make note of the name of the file.

EESys Internet Orders CSV File Example  

The above image shows a sample webshop CSV file.

1. Order numbers repeat, with multiple lines for 1 order. 
2. Note how the customer details also repeat on each line.

EESys Internet Orers CSV File Example: Products Section  

The above image shows further columns in the same CSV file.

1. Each row contains details of the customer's order for a different product, with the product's name, product-code and quantity ordered.

Actually importing Orders

"Import Internet Orders" Action in EESys  

To import the data, Click on the "Actions" button in EESys, and select "Import Internet Orders" (shown at 1., above)

Running the "Import Internet Orders" procedure  

When "Import Internet Orders" is clicked, a small screen will open in EESys, as shown above. Type the file-name for the import CSV ile into the "aFileName" field, as shown at 1., in the image. Then Click on the "OK" button (marked 2.).

The Import procedure will run

1. SalesInvoices will be created, one per Order-Number.
2. SalesInvoiceItems will be added and linked to each SalesInvoice, one per product ordered.
3. The SalesInvoices will be set to a status of "Picking", so they are then available to warehouse staff.
4. The CSV File will be renamed with a file extension "old" so that it is not accidentally imported twice.

Technical Details: The columns needed in the CSV file for the import process to work

The first step of the import process is for all the CSV data to be imported into a single data-table in the database which is exclusively used to hold the "raw" web-shop orders. Orixa then uses this raw data to pull through to the main system BusinessObject tables, SalesInvoices and SalesInvoiceItems. Once data has been imported, the "status" field in the WebShopOrders data-table is updated, so that items should not be imported twice.

If there are problems / issues, the contents of this table can be reviewed to see what may have gone wrong. For example, it is possible for items to be accidentally imported twice, or for rows of data to include invalid product-codes. In such instances it can be useful to open and review the "WebShopOrders" data-table to see what has gone wrong.

Also, if in future the web-shop is changed, this may impact on the operation of the importation process. Provided that the CSV file contains fields with the same names as the fields of the "WebShopOrders" data-table, the import process will still work. Therefore if the website is restructured try to ensure that the CSV file has the same fields as shown here, or review and update the SQL processes in EESys to change field-names to new names used by the webshop.

WebshopOrders SQL data-definition

CREATE TABLE "WebShopOrders" ( "ID" INTEGER DEFAULT UID(),
"order_id" VARCHAR(200) COLLATE "ANSI",
"order_date" VARCHAR(200) COLLATE "ANSI",
"status" VARCHAR(200) COLLATE "ANSI",
"shipping_total" VARCHAR(200) COLLATE "ANSI",
"order_total" VARCHAR(200) COLLATE "ANSI",
"order_key" VARCHAR(200) COLLATE "ANSI",
"payment_method" VARCHAR(200) COLLATE "ANSI",
"transaction_id" VARCHAR(200) COLLATE "ANSI",
"customer_ip_address" VARCHAR(200) COLLATE "ANSI",
"shipping_method" VARCHAR(200) COLLATE "ANSI",
"customer_id" VARCHAR(200) COLLATE "ANSI",
"billing_first_name" VARCHAR(200) COLLATE "ANSI",
"billing_last_name" VARCHAR(200) COLLATE "ANSI",
"billing_company" VARCHAR(200) COLLATE "ANSI",
"billing_email" VARCHAR(200) COLLATE "ANSI",
"billing_phone" VARCHAR(200) COLLATE "ANSI",
"billing_address_1" VARCHAR(200) COLLATE "ANSI",
"billing_address_2" VARCHAR(200) COLLATE "ANSI",
"billing_postcode" VARCHAR(200) COLLATE "ANSI",
"billing_city" VARCHAR(200) COLLATE "ANSI",
"billing_state" VARCHAR(200) COLLATE "ANSI",
"billing_country" VARCHAR(200) COLLATE "ANSI",
"shipping_first_name" VARCHAR(200) COLLATE "ANSI",
"shipping_last_name" VARCHAR(200) COLLATE "ANSI",
"shipping_company" VARCHAR(200) COLLATE "ANSI",
"shipping_phone" VARCHAR(200) COLLATE "ANSI",
"shipping_address_1" VARCHAR(200) COLLATE "ANSI",
"shipping_address_2" VARCHAR(200) COLLATE "ANSI",
"shipping_postcode" VARCHAR(200) COLLATE "ANSI",
"shipping_city" VARCHAR(200) COLLATE "ANSI",
"shipping_state" VARCHAR(200) COLLATE "ANSI",
"shipping_country" VARCHAR(200) COLLATE "ANSI",
"customer_note" VARCHAR(200) COLLATE "ANSI",
"item_product_id" VARCHAR(200) COLLATE "ANSI",
"item_name" VARCHAR(200) COLLATE "ANSI",
"item_sku" VARCHAR(200) COLLATE "ANSI",
"item_quantity" VARCHAR(200) COLLATE "ANSI",
"item_subtotal" VARCHAR(200) COLLATE "ANSI",
"item_subtotal_tax" VARCHAR(200) COLLATE "ANSI",
"item_total" VARCHAR(200) COLLATE "ANSI",
"item_total_tax" VARCHAR(200) COLLATE "ANSI",
"item_refunded" VARCHAR(200) COLLATE "ANSI",
"item_refunded_qty" VARCHAR(200) COLLATE "ANSI",
"Complete" BOOLEAN DEFAULT false NOT NULL,
"DateCreated" TIMESTAMP DEFAULT Current_Timestamp NOT NULL
)

Notes: The "ID", "Complete" and "DateCreated" fields in the above data-table are managed by Orixa. 
All other fields are imported from the "WebShop.CSV" file. This file MUST contain all of these fields, and must include HEADER-COLUMNS with the names of the fields which match these names exactly. 
The "Webshop.CSV" file can include as many other columns as you like, these extra columns will simply be ignored.