SambaPOS 5 Bulk Product and Inventory Importer

##Synopsis

This Tool is primarily used as a Bulk Product, Inventory, and Recipe importer based on data read from a CSV file. This is especially useful in Retail applications where there is a direct 1:1 relationship between Items you buy from a supplier and Products you sell.

As of this release, this Tool is compatible with the following SambaPOS versions:

5.1.58 - 5.1.60 (DB version 124) ::: dbops124c.zip (24.6 KB)


Originally developed by @JohnS for SambaPOS 3, updates to the Tool were developed by @QMcKay for compatibility with SambaPOS 4, and finally SambaPOS 5.

A few more “tools” are included for performing other DB operations, and there are 5 “OPS” or “operations” that you can choose from:

##Main Functions:

  • READ File - Reads CSV File and shows data without importing. Also contains CSV Header Column Help, and reveals information regarding invalid or missing data.

  • IMPORT Products - Imports Products, Menu Items, Inventory from CSV file.

  • READ Products - Reads Products, Menu Items, Inventory from Database.

  • DELETE Products - DELETES Products, Menu Items, Inventory from Database.

  • DELETE Transactions - DELETES all Transactions (i.e. Sales) from Database.

###Auxiliary Functions (smaller buttons on 2nd line):

  • DB - choose from list of known Databases

  • Export Products - creates a CSV file of Product Data for the selected Database

  • CSV - choose from list of known CSV files

  • UPLOAD - allows uploading of CSV files to the Server (helpful when using the Tool remotely)


##Screenshot of IMPORT operation:

##Screenshot of READ File operation:

##Screenshot of READ Products operation:


##Requirements

  • SQL Express Server 2008, 2012, or 2014

  • WampServer 2.5 (only used to run PHP script).

  • NOTE: If you already have a working PHP installation, you do not need to get or install WAMP.


##Installation Notes

  • Always backup your Database files before using this Tool.

  • The Tool is designed for SQL Express databases only and for people who are familiar with using SQL and know how to back up their database.

  • Best performed on the same computer as the SQL Express Database

:warning: [color=Red]WARNING[/color]: If you do not understand any of the instructions then DO NOT USE THIS TOOL


##Setup

  • Download and install WampServer 2.5 using default settings (not necessary if you already have a working PHP setup)

  • Unzip dbops124x.zip and place files from ‘dbops’ folder into c:\wamp\www

  • You will need to edit the dbops.php file in c:\wamp\www to set your specific details, such as DB connection parameters.

// the driver version depends on your SQL installation
// SQL Express 2012 is v10
// SQL Express 2014 is v11
//$driver = "{SQL Server Native Client 10.0}";
$driver = "{SQL Server Native Client 11.0}";

// SQL Express Server Details - Set to your details from your connection string, for example
// Data Source=localhost\SQLEXPRESS; User Id=sa; Password=sambapos; Database=import
$myServer = "localhost\SQLEXPRESS";
$myUser   = "sa";
$myPass   = "sambapos";
$myDB     = "import";

// file containing Product Data
$myImportFolder = "CSVfiles";
$myImportFile   = "sampleimport.csv";
$showRead = false;
$allowUploads = true;

// Timezone - http://php.net/manual/en/timezones.php 
date_default_timezone_set('Canada/Saskatchewan');
  • You can also modify the “defaults” for the way the Menu Categories and Menu Item Buttons will appear in SambaPOS. This is documented in the dbops.php file as well:
// Menu Defaults
// Colours can be by Name or by RGB code (#AAFF00) - http://colorschemedesigner.com/ 
$DefaultCategoryButtonColour = "Orange";
$DefaultMenuItemButtonColour = "Green";

$CategoryButtonHeight = "75";
$SubCategoryButtonHeight = "75";
$MenuItemButonHeight = "75";

$DefaultColumnCount = "0"; // 0-defaults to 3 columns
$DefaultNumberPad = "2"; // 2-Large, 1-Small, 0-None

$MenuButtonLength = 32; // Creates automatic Product Name wrapping on Menu Button

##Using the Tool

  • See the sampleimport.csv in c:\wamp\www\CSVfiles\ to get an idea how to format your data into a CSV file. NOTE: You may re-arrange the order of the Columns, however, Column Names are case-sensitive and should not be changed.

  • If starting with a blank database, the Tool will attempt to start a Workperiod and create a Menu definition. If the Tool is unable to do this, it will terminate. You can manually Start a Work Period and define at least one Menu, and try again.

  • to begin, open a Browser and navigate to: http://localhost/dbops.php


###CSV Column explanation

Update [text] (required:no) (Default: [blank] Insert the Product if it does not exist, else Skip the Product.)
U=Update by Product Name, UBC=Update by Barcode, A=Archive by Product Name, ABC=Archive by Barcode.

ProductGroupCode [text] (required:no) (Default: 'General' will be used.)
The Product GroupCode. This value will also be used as the Menu Category if that field is left blank.

ProductItemType [number] (required:no) (Default: 0)
0=SellProduct, 1=InventoryProduct. Classically, all Products are 'Sell Products', but you can also create pre-defined 'Inventory Products'.

ProductBarcode [text] (required:no) (Default: [blank])
Barcode for the Product.

ProductName [text] (required:yes) (Default: [REQUIRED])
The Name of the Product. This field is MANDATORY.

ProductTag [text] (required:no) (Default: [blank])
The Name of a Product Tag to sub-classify this Product.

ProductPortionName [text] (required:no) (Default: 'Normal' will be used.)
The Name of the Product Portion.

ProductPortionMultiplier [number] (required:no) (Default: 1)
Portion Multiplier.

ProductPrice [number] (required:no) (Default: 0.00)
The Product Price for the related Portion.

MenuName [text] (required:no) (Default: [blank])
The Name of the Menu where this Product will be placed. Leave this field blank if you do not want the Product assigned to a Menu.

MenuCategory [text] (required:no) (Default: GroupCode will be used.)
The Name of the Menu Category where this Product will be placed. You may leave this field blank if you want to use the GroupCode for the Menu Category Name.

SubMenu [text] (required:no) (Default: [blank])
The Name of a SubMenu within the Menu Category where the Menu Item button will appear.

MenuItemName [text] (required:no) (Default: ProductName will be used.)
The Name of the Product as you would like it to appear in the Menu.

MenuItemHeader [text] (required:no) (Default: ProductName/MenuItemName will be used.)
Allows for special formatting of the Menu Item button for this Product.

CategoryButtonColour [text] (required:no) (Default: 'Orange' will be used.)
The Color of the Menu Category button. Can be specified by Color Name or by Hex RGB code (#AAFF00).

MenuItemButtonColour [text] (required:no) (Default: 'Green' will be used.)
The Color of the Menu Item button. Can be specified by Color Name or by Hex RGB code (#AAFF00).

CreateInventory [text] (required:no) (Default: [blank] Inventory Item will not be created.)
Y=Create an Inventory Item for this Product.

InventoryName [text] (required:no) (Default: ProductName will be used.)
The Name of the Inventory Item or Inventory Product.

BaseUnit [text] (required:no) (Default: 'Ea' will be used.)
The Name of the Inventory Item Base Unit.

DefaultBaseUnitCost [number] (required:no) (Default: 0.00)
The default Cost for the Inventory Item Base Unit.

TransactionUnit [text] (required:no) (Default: 'Ea' will be used.)
The Name of the Inventory Item Transaction Unit.

TransactionUnitMultiplier [number] (required:no) (Default: 1)
Transaction Unit Multiplier. Specifies the quantity of Base Units in the Transaction Unit.

DefaultTransactionUnitCost [number] (required:no) (Default: 0.00)
The default Cost for the Inventory Item Transaction Unit.

WarehouseName [text] (required:no) (Default: 'Local Warehouse' will be used.)
The Name of the Warehouse where the Inventory Item resides.

WarehouseCode [text] (required:no) (Default: WarehouseName will be used.)
Used for Inventory Item Grouping.

RecipeName [text] (required:no) (Default: InventoryName/ProductName will be used.)
The Name of the Recipe for the Product/Portion.

RecipeItemOrderTag [text] (required:no) (Default: [blank])
Order Tag Mapping for the Recipe Item.

RecipeItemQty [number] (required:no) (Default: 1)
The quantity of Inventory Item or Inventory Product used in the Recipe.

RecipeItemUnit [number] (required:no) (Default: 1)
The (Base) Unit of Inventory Item or Inventory Product used in the Recipe.

RecipeItemType [number] (required:no) (Default: 0)
0=InventoryItem, 1=InventoryProduct. Recipes classically use Inventory Items, but they can also use pre-defined Inventory Products.

InventoryTransactionQty [number] (required:no) (Default: [blank])
The number of Transaction Units purchased, for the starting Stock.

InventoryTransactionCost [number] (required:no) (Default: DefaultTransactionUnitCost will be used if not 0, or (DefaultBaseUnitCost x InventoryTransactionQty) will be used.)
The Cost of the Purchase for this Inventory Item.

###Version History

********************************************************
 v124c (2016-07-14) QMcKay
  - FIX: issue with missing ProductItemType
  - FIX: MenuName is no longer required in CSV file (can be blank)
  - NEW: CSS file added for style - dbops.css
  - NEW: elapsed timer to indicate something is executing
  - NEW: parameter to set maximum execution time
 v124b (2016-05-26) QMcKay
  - NEW: parameter to set CSV File PATH
  - NEW: parameter to allow Upload of CSV File(s)
  - NOTE: several Header Columns have been renamed, and others have been added, however ...
  - NEW: function to READ CSV file and output some data without importing
     - contains CSV Header Column Help
     - shows valid/invalid/missing Columns
  - NEW: function to EXPORT DB Product Data to CSV file
     - Export prompts to save CSV data locally
  - NEW: choose Import CSV file by Browsing file system on Server
     - if no file is chosen, it defaults to $myImportFile parameter
  - NEW: choose Database by picking from list of available DBs on Server
     - works if $myUser has enough privelages to read 'master' DB
     - defaults to $myDB parameter
  - NEW: supports re-arranging CSV columns to whatever suits you best
     - NOTE: column Header Names are case-sensitive and must not be changed
  - NEW: supports multiple Price Definitions
     - create a Column Header in format of "PD:PriceDefinitionName/PriceTag"
     - example: PD:Happy Hour/HH
  - NEW: supports Product/Menu/Inventory Updates/Archiving via ProductName or ProductBarcode
     - previous versions would skip import of existing Products
     - if Product does not exist, it will be Inserted regardless of the following flags
     - to Update data of existing Products
        - to UPDATE, set the "Update" Column to "U" or "UBC":
           - U   : search and update by Product Name
           - UBC : search and update by Product BarCode
     - to Archive existing Products by re-assigning them to an "ARCHIVE Menu"
        - to ARCHIVE, set the "Update" Column to "A" or "ABC":
           - A   : search and archive by Product Name
           - ABC : search and archive by Product BarCode
 v124a (2016-05-18) QMcKay
  - first official version to support SambaPOS 5
  - supports changes in DB for SambaPOS 5.1.58 - 59
  - Import Products and Read Products modules output much more information
  - ExecQueryP function added to handle prepared SQL statements
  - ExecQuery function removed
  - changed all Queries to use Prepared Statements
     - allows for things like single-quotes or other special characters in the CSV data
  - changed versioning scheme of tool to be related to the SambaPOS DB version
     - indicates this tool only tested to work with same SambaPOS DB version
     - a warning is issued if there is a DB version mismatch
********************************************************
7 Likes

… reserved for updates …

Hey @QMcKay

Your work on such tools is outstanding and appreciated! Can I be very cheeky :innocent: and ask in a future update to add “Price Definition”?

It seems it would just be an extra column "Price Definition Name", blank is standard (nothing to do), “Take Away” would create extra records in [SAMBAPOS].[dbo].[MenuItemPriceDefinitions] for records with additional pricing for Takeaway and other definitions.
I guess it also means 2 exact records one column only changes on the subsequent record?

Also quick question with the version above, can you use the import to update existing pricing if the record already exists? I think currently “Batch Import” using YAML does not.

Hmm. I think it would be easier to process if the Header Line contained the Price Def Name/Tag so we could keep data to 1 row. Something like this:

In fact, it would be even better if we could move all of those columns to the end of the Row so that we know that anything beyond “SellPrice” is another Price Definition.


Not as-is. It skips existing Products. I can add an option for updating existing records.

So here is a question regarding that: Is the “update switch” Global? Or should we have a spec on each Row in the data? <-- add yet another column to the data, and only update the record if “Y”, else skip.

2 Likes

(You know the sad thing here is I cannot like a post more than once haha!)

PERFECT! - Such a clear and precise “take” on ideas.

Yes tricky, well I use the Customer Update Jscript routine than just Adds or Updates records - all fields.
In this case we really wish to update a new price only BUT who is to say we also wish to update the description?
Most users might desire to build integrations and hence require continuous updates so that may be our answer - anything in the file…

EDIT:
Umm seeing your update column above, maybe that column could morph into something like Update, Add, Delete, Archive…just saying leads to better design.

1 Like

Funny you should mention that, because I was seriously considering abandoning updates to the PHP script and move everything to JScript. There are Pros and Cons …

  • JScript is “internal” and requires no Webserver/PHP setup.

  • JScript could “freeze” SambaPOS until the import is finished, especially for large CSV files, unless it is invoked with Execute Script set to Run in Background.

  • JScript requires you have SambaPOS running to be able to use the “Import Function”, whereas PHP does not… PHP can be run either way, even remotely.

Thoughts? I really do not want to “maintain” two different methods of PHP and JScript.

Well this is one of my favourite topics so just tell me to shut up if required :grin:

I always have the focus of consumerism for @emre and SambaPOS (bigger, stronger) better for us so the “Average Joe” needs to be able to deploy SambaPOS - this is a plus.

I have bench tested this and VERY surprised how fast this is! Also with routines like “Application Start”, “Trigger”, and “File Monitor” we can load when these imports occurs. So feel this is neutral to positive.

The only issue I found was when I dropped 10 CVS files containing Customer Updates and Transaction Documents - all files were processed simultaneously by 1 workstation except the last file? Again, I have Application Start Process that will pick up any let over updates. Also these types of files are created one by one so I was trying to break it anyway…

Yes, this fact I spent a day figuring out Sukasems Loop - calling another Loop (:scream:) but now done works very well and if any workstation is started, data is picked up during Application Load (Blue Screen). File Monitor then does the rest throughout the day.

That is how I have managed without PHP.

The main issue is the requirement for more API’s to save SQL as again the average Joe may be offended by the requirement for SQL joins etc.

EDIT:
Oh Emre mentioned a new feature - ability to call a RULE(?) from a Script - could be a game changer as well.

I seem to remember something like that as well. Can you point me to that post?

Can’t wait!

1 Like

I tested this but it does not add the cost price. Everything else works fine. The ability to update existing product prices and cost prices would be a great feature.

1 Like

It isn’t designed to update the CostPrice. That column in the CSV data is used for an Inventory Transaction Purchase Document so you can set an initial stock level. Really, it should be renamed, but instead, I will add columns for:

DefaultBaseUnitCost
DefaultTransactionUnitCost

The next version of this Tool is shaping up really nice with a lot of enhancements, so keep the suggestions coming … so far, I am working on these changes:

 v124b (2016-05-20) QMcKay
  - Inventory Default Cost columns added:
     - DefaultBaseUnitCost
     - DefaultTransactionUnitCost
  - supports multiple Price Definitions
     - create a Column Header in format of "PD:PriceDefinitionName/PriceTag"
     - example: PD:Happy Hour/HH
  - supports data Updates
     - previous versions would skip import of existing Products
     - this version allows Updating data of existing Products  
     - set the "Update" Column to "U" for Products that you want to Update
  - supports re-arranging columns to whatever suits you best
     - NOTE: Column Header Names are case-sensitive and must not be changed
1 Like

Regarding the Data Update feature, I think we need to decide which portions of the data is subject to being updated.

We cannot update the Product Name, since we use that to search for the Product to be updated. Maybe we can mark the Product as something to be Deleted (D), or Archived (B), and have a new non-existing Product row in the data if we want to “Update the Product Name”. Still, I think this should be used with caution since it will affect Cost/Profit calculations since the Product is technically “new”.

If a Product is to be “Archived”, does that mean we simply remove it from the Menu, but leave the Product intact? I hope so, because …

I think “Deleting” a Product will be a PITA, and probably should not be done anyway for historical data integrity, so I will leave that option for a future Tool version.

Caution should also be used when updating Inventory Units (Base, Transaction, Additional Units, and their respective Parent Units and Multipliers.) since they will also affect Cost/Profit Calculations.

I think we can safely update things like:

Menu Item Name
Product Portion Prices
Product Price Definition Prices
Inventory Item Default Costs for any Unit
Recipe Inventory Item Quantity

We can also consider the possibility of setting the Product ItemType. Classically, there was only a single ItemType, but with recent Inventory enhancements, we now have the classic Product that you sell which is ItemType 0, and a new Inventory Product which is ItemType 1.

I know from my stock list if the product name is different it would be added as a new item. The stock list would have an item code or barcode. If item code or barcode is different it is always a new item also. I use the item code from the stock list in the barcode field in samba if the item has no barcode. In retail all items will have an item code or barcode. You could have script check if barcode is the same update and if different insert.

1 Like

Can you add barcodes to the list of things we can update?

It would be nice to be able to export all products and menus to the csv file for editing off premises.
The updated file could then be imported to overwrite existing product prices,barcodes etc.
It would be easier alternative to taking full database backup and restore the following day for staff with no MSSQL knowledge.

2 Likes

Yes absolutely agree and shows, speaking for myself, that I should take your guidance on UPDATES. I certainly do not have the experience in the “Inventory Side” of Samba to make a informed comment.

Question: Can the tool be used if SambaPOS MenuItems & Products have only be setup and you then wish to extend functionality into Inventory? I not suggesting that this should be a capability but just refer to a single case :blush:. I guess many newcomers start with this scenario…

Love how it is shaping up.

Exporting will be implemented in the next version! :wink:


Yes, by way of UPDATE switch. But it updates everything from Product to Menu to Inventory …

Coming very soon, lots of new functionality, just bug-testing …

v124b (2016-05-24) QMcKay
  - NEW parameter to set CSV File PATH
  - NEW parameter to allow Upload of CSV File(s)
  - NOTE: several Header Columns have been renamed, and others have been added, however ...
  - NEW function to READ CSV file and output some data without importing
     - contains CSV Header Column Help
     - shows valid/invalid/missing Columns
  - NEW function to EXPORT DB Product Data to CSV file
     - Export prompts to save CSV data locally
  - choose Import CSV file by Browsing file system on Server
     - if no file is chosen, it defaults to $myImportFile parameter
  - choose Database by picking from list of available DBs on Server
     - works if $myUser has enough privelages to read 'master' DB
     - defaults to $myDB parameter
  - supports re-arranging columns to whatever suits you best
     - NOTE: Column Header Names are case-sensitive and must not be changed
     - NOTE: some Column Header Names have been changed
  - supports multiple Price Definitions
     - create a Column Header in format of "PD:PriceDefinitionName/PriceTag"
     - example: PD:Happy Hour/HH
  - supports Product/Menu/Inventory Updates/Archiving via Product Name or Barcode
     - previous versions would skip import of existing Products
     - if Product does not exist, it will be Inserted regardless of the following flags
     - to Update data of existing Products
        - to UPDATE, set the "Update" Column to "U" or "UBC":
           - U   : search and update by Product Name
           - UBC : search and update by Product BarCode
     - to Archive existing Products by re-assigning them to an "ARCHIVE Menu"
        - to ARCHIVE, set the "Update" Column to "A" or "ABC":
           - A   : search and archive by Product Name
           - ABC : search and archive by Product BarCode
2 Likes

If update is set will it add the product if the product is not already there. The reason asking, I could get a new stock list with 10,000 items. The only thing on some items that would change is RRP Price and Cost Price. This stock list would also have new items also. Do you run the script twice? One for update and one new items.

Yes, if the Product does not already exist, it will be inserted, regardless of the Update column setting.

The only time this does not seem intuitive is if the Product does not exists and you have Update set to Archive (A or ABC). It ignores that setting as well, and inserts the Product into the Menu you have specified instead of placing the Product in the “Archive Menu”.

2 Likes

New version 124b has been uploaded. See original post in the topic to download.

:warning: NOTE: several Header column Names have been changed/updated.

Ensure your Headers are correct.

An easy way to check this is to use the “READ FILE” operation. It will scan your CSV file and report on missing or invalid Headers.

Let me know if there are any issues…

2 Likes

I am already making more updates to make it more compatible with non-Retail applications.

Initially, the Tool was developed for Retail Products, Menu Items, and Inventory. And it still work fairly well for that. But it is sequenced and checked in such a way that most things need to have a 1:1:1:1 mapping, as in:

Product.Portion : Menu Item : Inventory Item : Recipe

This works well for most cases, even if you specify multiple Portions, etc.

The place where it is falling apart right now is in cases where a Product is mapped to more than one Menu, Menu Category, and/or Menu Item Portion. I have already made changes to accommodate a few of those cases, but I still need to make some more changes to the way duplicates are detected in regard to Menu Items.

Right now, duplicates are detected solely on the Product.Portion Level, so it will skip inserting an Item at that point - that is, it won’t look as deep as the Menu Item. Instead, it should go on to check if the Menu Item for a Product is specifically mapped within more than one Menu or Menu Category. This could also help in Retail-based applications.

In any case, v124c will be coming out to accommodate these scenarios. I believe then it will then be an excellent Tool for both types of application, and I might even start using it myself!

In the mean-time, please let me know if there are any other features that you would like to see, or if you see any issues, because after the next version, I will shelf this project until it becomes incompatible with SambaPOS DB changes.