##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
[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.zipand place files from âdbopsâ folder intoc:\wamp\www -
You will need to edit the
dbops.phpfile inc:\wamp\wwwto 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.phpfile 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.csvinc:\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
********************************************************
and ask in a future update to add âPrice Definitionâ?
) 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.
. I guess many newcomers start with this scenarioâŠ