Inventory Query

[skyscorpio]

This is the best way for retail to skip all complicated steps when Products & Inventory data entry, hope this helps,

@JohnS

*** Setup And Usage Instructions ***

Requirements

• SambaPOS V4.1.37
• SQL Express Server 2008+
• WampServer 2.5 (Only used to run PHP script)
• Download attached file below

Installation Notes

• Always backup your Database files before using this tool
• This is designed for SQL Express databases only and for people who are familiar with using SQL and know how to back up their database files.
  ** If you do not understand any of these instructions then DO NOT USE THIS TOOL **
• I will take no responsibility for lost or damaged databases.
• Best performed on the same computer as the SQL Express Database

Setup

• Download and install WampServer 2.5 using default settings
• Unzip ‘SambaPOS4 Product Import V2.zip’ and place files from ‘www’ folder into c:\wamp\www

Using Importer Tool

• If starting with a blank database, you will need to Start a Work Period and have atleast one Menu defined
• Products will be added to the Menu List you specify under CreateMenu in the import.csv file.
• Edit load_products.php file in c:\wamp\www and set your specific details.

// SQL Express Server Details - Set to your details from your connection string
// example: data source = localhost\SAMBAPOS; user id=sa; password=sambapos; database=import
$myServer = “localhost\SAMBAPOS”;
$myUser = “sa”;
$myPass = “sambapos”;
$myDB = “import”;

// Timezone - PHP: List of Supported Timezones - Manual
date_default_timezone_set(‘Australia/Sydney’);

// Menu Defaults
// Colours can be by Name or by RGB Code (#AAFF00) - http://colorschemedesigner.com/
$DefaultCategoryButtonColour = “Orange”;
$DefaultMenuItemButtonColour = “Green”;

$CategoryButtonHeight = “45”;
$SubCategoryButtonHeight = “45”;
$MenuItemButonHeight = “45”;

$DefaultColumnCount = “2”;
$DefaultNumberPad = “1”; // 2-Large, 1-Small, 0-None

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

• Edit import.csv in c:\wamp\www and update with your products and save.
  Note: You cannot change the order of the Columns.

CSV Columns

• CreateMenu - Menu Name/N - Create a Menu Item Button for this Product. If you list the Menu Name here, the product is added to that menu

• CreateInventory - Y/N - Create an Inventory Item & Recipe for this Product and import stock levels

• GroupCode - Text - Product Group Code, Inventory Group Code and Menu Category

• SubGroupCode - Text - Menu Sub Category

• Barcode - Text - Product Barcode

• ProductName - Text - Product Name

• MenuName - Text - Menu Item Name - If blank Product Name is used

• InventoryName - Text - Inventory Item Name - If blank Product Name is used

• PortionName - Text - Product Price Portion Name - if blank Normal is used

• PortuonMultiplier - Number - Product Portion Price Multiplier - if blank 1 is used

• SellPrice - Number - Product Portion Price

• BaseUnit - Text - Inventory Base Unit - if blank Ea is used

• TransactionUnit - Text - Inventory Transaction Unit - if blank Ea is used

• TransactionMultiplier - Number - Inventory Transaction Multiplier - if blank 1 is used

• RecipeQty - Number - Inventory Item Quantity per Recipe - if blank 1 is used

• InventoryQty - Number - Starting Inventory Level

• CostPrice - Number - Inventory Item Cost Price

• Warehouse - Text - The Warehouse the Inventory Item is added to

• CategoryButtonColour - Text - Menu Category Button Colour - if blank Orange is used

• MenuItemButtonColour - Text - Menu Item Button Colour - if blank Green is used

• Open Web Browser and goto address ‘http://localhost/load_products.php’ This will import immediately and you should get a result like

Start : 23-07-2013 00:07:13
Connected successfully - MSSQL

Menu Category - OIL
MOTOREX TOP SPEED 4T 10W40 1 LITRE
MOTOREX TOP SPEED MC 4T 10W40 4 LITRE
MOTOREX FORMULA 4T 15W50 1 LITRE
MOTOREX CROSS POWER 2T 1 LITRE
MOTOREX CROSS POWER 4T 10W50 4 LITRE
Motorex Cross Power 4T 10w50 4Litre
MOTOREX 4 STROKE 10W/40 4 LITRE
Menu Category - AIR FILTER
MOTOREX AIR FILTER OIL 206 1 LITRE

Finish : 23-07-2013 00:07:13
Execution Time : 0

• Run SambaPOS4.
• Enjoy

Additional Tools
In ‘SambaPOS4 Product Import V2.zip’ there is a folder called ‘Clear SambaPOS DB’ which contains the following tools - Use at your own risk as they wipe data directly from your database.

DELETE V3 Prod Inv Recipe MenuItems.bat

• This clears all Products, Inventory Items, Recipes, Menu Categories & Items and all Inventory Transactions. It does NOT remove transactional data like sales
• Edit this file to make sure your SQL Express Server Name and Instance are correct

DELETE V3 All Transactions.bat

• This clears all transactional data leaving Products, Inventory, Recipes and Menus untouched.

SambaPOS4 Product Import V2.zip (5.5 KB)

---

EDIT (2015-04-15 : QMcKay): New version supporting SambaPOS 4.1.82

@QMcKay

Major modifications… tested with SambaPOS 4.1.82 and SQL Express 2014.