Thanks for reporting.
Find the following line (dbops_readfile.php @ line 135):
$RecipeItemType[$r] = (($RecipeItemType[$r]!=0 && $RecipeItemType[$r]!=1) ? 0 : $RecipeItemType[$r]);
Replace it with this:
$RecipeItemType[$r] = ((($RecipeItemType[$r]!=0 && $RecipeItemType[$r]!=1) || $RecipeItemType[$r]=='') ? 0 : $RecipeItemType[$r]);
ZIP file has been updated with that fix as of 2017-05-25
Following and enjoying the discussion. What I noticed was the report image above - where did that come from? Fantastic - how do I find that report please or similar reports giving sales and gp margins etc? Do they exist or do they need to be written?
Not sure which Report you are referring to, but check this topic:
A very kind request for an upgrade to support latest version of SambaPOS, as I’d love to use it for retail config. When and if you get the time of course @QMcKay
Thank you very much for all the help you give here!! 
I am not aware of any issues between this and 5.1.62.
When I tried it said that there is a column missing? Or something like that and gave an error? I just used the older version of Samba with it and then, upgraded.
But if you say so then it must be okay, sorry to bother 
I have a trouble exporting products, it give me this error, i can read the products but i cannot export it.
Whic “version” are you using?
Post the content of the file dbops.php … it should look like this:
<?php
/********************************************************
SambaPOS 5 DB Operations Tool
********************************************************
v124d (2016-08-20) QMcKay
- FIX: issue with missing RecipeItemType
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 - 60
- 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
********************************************************
Developed and Tested using WAMPServer 2.5 32bit
http://www.wampserver.com/en/#download-wrapper
Component Versions used
SQL Express 2014 (v11) 64-bit
Apache 2.4.9
PHP 5.6.0 (QMcKay)
MYSQL 5.6.17 (not required)
********************************************************/
/*******************************************************
User Settings
********************************************************/
// the driver version depends on your SQL installation
// SQL Express 2012 is v10
// SQL Express 2014 is v11
// SQL Express 2016 is v12
//$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;
// folder containing DB Tools Export Files (TXT)
$myDBToolsFolder = "DBTfiles";
$myDBToolsFile = "dbtexport.txt";
// Timezone - http://php.net/manual/en/timezones.php
date_default_timezone_set('Canada/Saskatchewan');
// Menu Defaults
// Colours can be by Name or by Hex RGB code (#AAFF00) - http://colorschemedesigner.com/
$DefaultCategoryButtonColour = "Orange";
$DefaultMenuItemButtonColour = "Green";
$CategoryButtonHeight = "75";
$SubCategoryButtonHeight = "75";
$MenuItemButtonHeight = "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
//E_ALL & ~E_DEPRECATED & ~E_STRICT
error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED & ~E_STRICT);
ini_set("display_errors", 1);
// character set
// PHP's default character set is set to empty.
// http://php.net/default-charset
ini_set("default_charset","UTF-8");
// maximum amount of time allowed for script execution
ini_set("max_execution_time","120");
/*******************************************************
End of user settings
*******************************************************/
$DBOPStoolVersion = "124d (5.1.58-60)";
$DBOPScompatVersion = "5.1.58-60";
$DBOPSdbVersion = "124";
ini_set("auto_detect_line_endings", 1);
include ("dbops_functions.php");
//connection to MSSQL database
$dbhandle = odbc_connect("Driver=$driver;Server=$myServer;Database=$myDB;", $myUser, $myPass);
if (!$dbhandle) {
$msg1 = "Couldn't connect to SQL Server on: $myServer";
$msg2 = odbc_errormsg();
THROWERROR($msg1,$msg2,TRUE);
}
$query = "
SELECT Name, database_id, create_date
FROM sys.databases
WHERE Name like 'Samba%' or Name like 'import%'
ORDER BY Name
";
$qparms = array();
$dbNames = ExecQueryP($query,$qparms);
$dbCount = count($dbNames);
//close DB connection
if ($dbhandle) {
odbc_close($dbhandle);
}
// use chosen DB or default to $myDB
$db = $_GET["db"];
$db = ($db != '' ? $db : ($myDB != '' ? $myDB : 'SambaPOS5'));
$db_name = $db;
// get CSV File List
$myImportFolder = ($myImportFolder=="" ? "" : (substr($myImportFolder,-1)=="/" ? $myImportFolder : $myImportFolder."/"));
$fiCount = 0;
$fiList = array();
if ($fhandle = opendir(($myImportFolder=="" ? "." : $myImportFolder))) {
while (false !== ($fi = readdir($fhandle)))
{
if ($fi != "." && $fi != ".." && strtolower(substr($fi, strrpos($fi, '.') + 1)) == 'csv')
{
$fiCount++;
$fiList[$fiCount]["Name"] = $fi;
}
}
closedir($fhandle);
sort($fiList);
}
// use chosen CSV or default to $myImportFile
$csv = $_GET["csv"];
$csv = ($csv != '' ? $csv : ($myImportFile != '' ? $myImportFile : ''));
$myImportFile = $csv;
// get DBT File List
$myDBToolsFolder = ($myDBToolsFolder=="" ? "" : (substr($myDBToolsFolder,-1)=="/" ? $myDBToolsFolder : $myDBToolsFolder."/"));
$dbtCount = 0;
$dbtList = array();
if ($fhandle = opendir(($myDBToolsFolder=="" ? "." : $myDBToolsFolder))) {
while (false !== ($fi = readdir($fhandle)))
{
if ($fi != "." && $fi != ".." && strtolower(substr($fi, strrpos($fi, '.') + 1)) == 'txt')
{
$dbtCount++;
$dbtList[$dbtCount]["Name"] = $fi;
}
}
closedir($fhandle);
sort($dbtList);
}
// use chosen CSV or default to $myImportFile
$dbt = $_GET["dbt"];
$dbt = ($dbt != '' ? $dbt : ($myDBToolsFile != '' ? $myDBToolsFile : ''));
$myDBToolsFile = $dbt;
// Rock n Roll
$op = $_GET["op"];
if ($op!='readfile' && $op!='importproducts' && $op!='readproducts' && $op!='exportproducts' && $op!='deleteproducts' && $op!='deletetransactions' && $op!='readdbtoolsfile') {
$op='';
}
if ($op=='exportproducts') {
include ("dbops_$op.php");
}
if ($allowUploads) {
$filecount = count($_FILES['filesToUpload']['name']);
$uploadAttempt = (($filecount>0 && $_FILES['filesToUpload']['name'][0]=="") ? true : false);
//echo "FC:$filecount";
//echo "-".$_FILES['filesToUpload']['name'][0]."-";
if ($filecount>0 && $_FILES['filesToUpload']['name'][0]!="") {
//$destination = realpath(dirname(__FILE__));
$destination = __DIR__ . "/" . $myImportFolder;
//echo $destination;
for ($f = 0; $f < $filecount; $f++) {
$bakmade[$f] = 0;
$filetocopy[$f] = $_FILES['filesToUpload']['tmp_name'][$f];
$filename[$f] = $_FILES['filesToUpload']['name'][$f];
$filesize[$f] = $_FILES['filesToUpload']['size'][$f];
$filetyp[$f] = $_FILES['filesToUpload']['type'][$f];
$ferror[$f] = $_FILES['filesToUpload']['error'][$f];
//echo "fc:$filecount".$filename[$f].'<br />';
}
for ($f = 0; $f < $filecount; $f++) {
if (file_exists($destination . $filename[$f])) {
if (!file_exists($destination . 'bak/')) {
mkdir($destination . 'bak/');
}
copy($destination . $filename[$f], $destination . 'bak/' . $filename[$f]);
$bakmade[$f] = 1;
}
copy($filetocopy[$f], $destination . $filename[$f]);
}
}
}
echo '<!DOCTYPE html>'."\r\n";
echo '<html>'."\r\n";
echo '<head>'."\r\n";
echo '<title>SambaPOS 5 DBops v'.$DBOPStoolVersion.' DB:'.$DBOPSdbVersion.'</title>'."\r\n";
echo '<link rel="stylesheet" type="text/css" href="dbops.css" media="screen" />';
//if ($op=='exportproducts') {
// $rd = 'dbops.php?db='.$db_name.'&csv='.$myImportFile;
// echo '<meta http-equiv="refresh" content="10; url='.$rd.'">';
//}
?>
<SCRIPT language="Javascript" type="text/javascript">
function verifyop(op) {
var db = document.getElementById("DBname").value;
var csv = document.getElementById("CSVfile").value;
var dbt = document.getElementById("DBTfile").value;
var execop = confirm("Are you certain you want to "+op+" ?\r\nDB : "+db+"\r\nCSV : "+csv+"\r\nDBT : "+dbt);
if (execop==true) {
if (op != 'EXPORT Products') {
if (document.getElementById('op')) {
document.getElementById('op').innerHTML = op;
}
if (document.getElementById('output')) {
document.getElementById('output').innerHTML = '<div style="font-size:26px;font-weight:bold;padding:10px;background-color:#55FF55;text-align:center;margin:auto;">EXECUTING: '+op+'<br/><br/>... please wait ...<br/></div>';
}
if (document.getElementById('busy')) {
document.getElementById('busy').style.display = 'block';
}
setInterval(busy,1000);
}
op = op.toLowerCase();
op = op.replace(/ /g,"");
location.href='dbops.php?db='+db+'&csv='+csv+'&dbt='+dbt+'&op='+op;
}
}
function busy(op) {
if (document.getElementById('elapsed')) {
var content = Number(document.getElementById('elapsed').innerHTML);
content++;
document.getElementById('elapsed').innerHTML = content;
}
}
function switchDB() {
var db = document.getElementById("DBname").value;
var csv = document.getElementById("CSVfile").value;
location.href='dbops.php?db='+db+'&csv='+csv;
}
function switchFile() {
var db = document.getElementById("DBname").value;
var csv = document.getElementById("CSVfile").value;
var dbt = document.getElementById("DBTfile").value;
location.href='dbops.php?db='+db+'&csv='+csv+'&dbt='+dbt;;
}
function hideDIV(divID) {
var elem = document.getElementById(divID);
elem.style.display='none';
elem.style.visibility='hidden';
}
function showDIV(divID) {
var elem = document.getElementById(divID);
elem.style.display='block';
elem.style.visibility='visible';
}
</SCRIPT>
<?php
echo '</head>'."\r\n";
echo '<body>'."\r\n";
echo '<DIV class="area_flex">'."\r\n";
echo '<DIV class="op_main" style="background-color:#77BEFA;" onclick="verifyop('."'READ File'".');">'."\r\n";
echo '<b style="font-size:20px">READ CSV File</b><br />'."\r\n";
echo '<i>Reads CSV File rows and displays output, without importing.</i>'."\r\n";
echo '</DIV>'."\r\n";
echo '<DIV class="op_main" style="background-color:#BBBBFF;" onclick="verifyop('."'IMPORT Products'".');">'."\r\n";
echo '<b style="font-size:20px">IMPORT Products</b><br />'."\r\n";
echo '<i>Imports Products, Menu Items, Inventory from CSV file.</i>'."\r\n";
echo '</DIV>'."\r\n";
echo '<DIV class="op_main" style="background-color:#AAFFAA;" onclick="verifyop('."'READ Products'".');">'."\r\n";
echo '<b style="font-size:20px">READ Products</b><br />'."\r\n";
echo '<i>Reads Products, Menu Items, Inventory from Database.</i>'."\r\n";
echo '</DIV>'."\r\n";
echo '<DIV class="op_main" style="background-color:#FFAAAA;" onclick="verifyop('."'DELETE Products'".');">'."\r\n";
echo '<b style="font-size:20px">DELETE Products</b><br />'."\r\n";
echo '<i>DELETES Products, Menu Items, Inventory from Database</i>'."\r\n";
echo '</DIV>'."\r\n";
echo '<DIV class="op_main" style="background-color:#FF8888;" onclick="verifyop('."'DELETE Transactions'".');">'."\r\n";
echo '<b style="font-size:20px">DELETE Transactions</b><br />'."\r\n";
echo '<i>DELETES all Transactions (i.e. Sales) from Database.</i>'."\r\n";
echo '</DIV>'."\r\n";
echo '<DIV class="op_main" style="background-color:#FFBB55;" onclick="verifyop('."'READ DB Tools File'".');">'."\r\n";
echo '<b style="font-size:20px">READ DB Tools File</b><br />'."\r\n";
echo '<i>Reads DB Tools Export File and displays output.</i>'."\r\n";
echo '</DIV>'."\r\n";
echo '</DIV>'."\r\n";
echo '<DIV class="area_flex" style="margin-top:2px;">'."\r\n";
echo '<DIV style="margin:0px;padding:5px;">'."\r\n";
echo 'DB: ';
echo '<select name="DBname" id="DBname" size="1" title="Select the Database you want to affect." onChange="switchDB(this.value);">';
for ($dbi=1; $dbi<=$dbCount; $dbi++) {
echo '<option value="'.$dbNames[$dbi]["Name"].'"'.($dbNames[$dbi]["Name"]==$db_name ? ' selected' : '').'>'.$dbNames[$dbi]["Name"].'</option>';
}
echo '</select>';
echo ' <input type="button" value="Export Products" title="Export Products to CSV for the selected Database. You will be prompted to save the File locally." onclick="verifyop('."'EXPORT Products'".');">';
echo '</DIV>'."\r\n";
echo '<DIV style="align-self:flex-end;background-color:#AAAAAA;margin:0px;padding:5px;text-align:right;">'."\r\n";
echo "[SambaPOS 5 DBops v$DBOPStoolVersion DB:$DBOPSdbVersion]";
echo '</DIV>'."\r\n";
echo '</DIV>'."\r\n";
echo '<DIV style="display:flex;width:100%;margin-top:2px;">'."\r\n";
echo '<DIV style="padding:5px;">'."\r\n";
// DBT
echo '<span title="The Path on the Server where the DB Tools Export Files reside.">DBT'.($myDBToolsFolder!="" ? " ($myDBToolsFolder)" : " (script Path) ").': </span>';
echo '<select name="DBTfile" id="DBTfile" size="1" title="Select the DB Tools File you want to Read." onChange="switchFile(this.value);">';
for ($f=0; $f<$dbtCount; $f++) {
echo '<option value="'.$dbtList[$f]["Name"].'"'.($dbtList[$f]["Name"]==$dbt ? ' selected' : '').'>'.$dbtList[$f]["Name"].'</option>';
}
echo '</select>';
echo ' <input type="button" value="Refresh DB Tools list" title="Click HERE to refresh the DB Tools File list." onclick="verifyop('."'REFRESH DBT'".');">';
echo '<br />';
// CSV
echo '<span title="The Path on the Server where the CSV Files reside.">CSV'.($myImportFolder!="" ? " ($myImportFolder)" : " (script Path) ").': </span>';
echo '<select name="CSVfile" id="CSVfile" size="1" title="Select the CSV File you want to Read or Import into the selected Database." onChange="switchFile(this.value);">';
for ($f=0; $f<$fiCount; $f++) {
echo '<option value="'.$fiList[$f]["Name"].'"'.($fiList[$f]["Name"]==$csv ? ' selected' : '').'>'.$fiList[$f]["Name"].'</option>';
}
echo '</select>';
echo ' <input type="button" value="Refresh CSV list" title="Click HERE to refresh the CSV File list. You will need to do this after an EXPORT operation." onclick="verifyop('."'REFRESH CSV'".');">';
if ($allowUploads) {
//$filestoaccept = '.mp3, image/jpeg, image/gif, image/png, image/tiff, application/pdf';
$filestoaccept = '.csv, .CSV, .txt, .TXT';
// echo "\r\n".'<div style="margin-top:10px">';
$formAction = "dbops.php?db=$db_name&csv=$myImportFile";
echo '<form style="display:inline-block" id="myForm" id="myForm" method="POST" action="'.$formAction.'" enctype="multipart/form-data">';
echo ' | <input type="submit" value=" UPLOAD " title="Upload selected Files to the Server."> ';
echo '<input type="file" name="filesToUpload[]" id="filesToUpload" size="120" length="0" maxlength="2048" maxtime="610" multiple="" onChange="makeFileList();" title="File' . ($browserIE ? '' : '(s)') . ' to upload - click here to choose file' . ($browserIE ? '' : '(s)') . '" ACCEPT="' . $filestoaccept . '" /><br />' . "\r\n";
echo '<input type="hidden" name="filenamesave" value="' . $_FILES['filesToUpload']['name'] . '">' . "\r\n";
echo '</form>';
// echo "</div>\r\n";
}
echo '</DIV>'."\r\n";
echo '</DIV>'."\r\n";
echo '<hr />'."\r\n";
echo ($uploadAttempt ? "<br />Choose Files to upload first, then click UPLOAD." : "");
//connection to MSSQL database
$dbhandle = odbc_connect("Driver=$driver;Server=$myServer;Database=$db_name;", $myUser, $myPass);
if (!$dbhandle) {
$msg1 = "Couldn't connect to SQL Server on: $myServer Database:[$db_name]";
$msg2 = odbc_errormsg();
THROWERROR($msg1,$msg2,TRUE);
}
//echo 'Connected successfully - MSSQL<br /><br />';
//$query = "SELECT [Version], [AppliedOn] FROM [VersionInfo] WHERE [Version] IN (SELECT max([Version]) FROM [VersionInfo])";
$query = "SELECT [Version] FROM [VersionInfo] WHERE [Version] IN (SELECT max([Version]) FROM [VersionInfo])";
$qparms = array();
$qrydata = ExecQueryP($query,$qparms);
$DBversion = $qrydata[1]["Version"];
$DBdate = $qrydata[1]["AppliedOn"];
$DBmismatchWarning = '<div style="font-size:18px;font-weight:bold;color:#FF0000">';
$DBmismatchWarning.= "WARNING: Database Version Mismatch. USE AT YOUR OWN RISK!";
$DBmismatchWarning.= '<div style="font-size:18px;font-weight:bold;color:#000000">';
$DBmismatchWarning.= "This Tool may or may not work properly.";
$DBmismatchWarning.= "<br />This Tool designed to work on SambaPOS DB version <span style='color:#009900'>$DBOPSdbVersion</span>.";
$DBmismatchWarning.= "<br />Your SambaPOS DB version for database <span style='color:#0000FF'>[$db_name]</span> is: <span style='color:#FF0000'>$DBversion</span>";
$DBmismatchWarning.= "<br /><br />";
$DBmismatchWarning.= '</div>';
$DBmismatchWarning.= '</div>';
echo ($DBOPSdbVersion!=$DBversion ? $DBmismatchWarning : '');
if ($op != '') {
echo 'OP: ';
echo '<span id="op" style="font-weight:bold;">'.$op."</span>";
echo "<br /><br />\r\n";
}
echo '<div id="output">';
if ($op=='') {
goto theend;
}
// Todays Date/Time
$today = date("Y-m-d H:i:s");
$smtime = microtime(true);
list($s,$us) = explode('.',$smtime);
$start_time = date("H:i:s.").$us;
echo "Start : ".$start_time."<br />";
// *******************************************************
// READ CSV File
// *******************************************************
if ($op=='readfile') {
$showRead = true;
include ("dbops_$op.php");
}
// *******************************************************
// READ Products
// *******************************************************
if ($op=='readproducts') {
include ("dbops_$op.php");
}
// *******************************************************
// EXPORT Products
// *******************************************************
if ($op=='exportproducts') {
//include ("dbops_$op.php");
}
// *******************************************************
// DELETE Products
// *******************************************************
if ($op=='deleteproducts') {
include ("dbops_$op.php");
}
// *******************************************************
// DELETE Transactions
// *******************************************************
if ($op=='deletetransactions') {
include ("dbops_$op.php");
}
// *******************************************************
// IMPORT Products
// *******************************************************
if ($op=='importproducts') {
include ("dbops_$op.php");
}
// *******************************************************
// READ DB Tools File
// *******************************************************
if ($op=='readdbtoolsfile') {
$showRead = true;
include ("dbops_$op.php");
}
finish:
$emtime = microtime(true);
list($s,$us) = explode('.',$emtime);
$finish_time = date("H:i:s.").$us;
echo '<hr />';
echo "<br />Finish : ".$finish_time;
$time1 = strtotime($start_time);
$time2 = strtotime($finish_time);
//$diff = $time2 - $time1;
$diff = $emtime-$smtime;
//echo "<br />Execution Time : ".date('i:s.u', $diff);
echo "<br />Execution Time (s): ".$diff;
theend:
//close DB connection
if ($dbhandle) {
odbc_close($dbhandle);
}
echo "\r\n</div>\r\n";
if ($allowUploads) {
?>
<SCRIPT language="Javascript" type="text/javascript">
function chooseFile() {
var ipath = '<?php echo $myImportFolder.'/'; ?>';
if (document.getElementById("CSVfile")) {
var ifile = document.getElementById("CSVfile").value;
alert ("File:"+ipath+ifile);
}
}
function chooseDBTFile() {
var ipath = '<?php echo $myDBToolsFolder.'/'; ?>';
if (document.getElementById("DBTfile")) {
var ifile = document.getElementById("DBTfile").value;
alert ("File:"+ipath+ifile);
}
}
</SCRIPT>
<?php
}
echo '<div id="busy" style="display:none;font-size:26px;font-weight:bold;padding:10px;background-color:#55FF55;text-align:center;margin:auto;">';
echo 'Elapsed Seconds: <span id="elapsed">0</span>';
echo '</div>';
echo '</body>'."\r\n";
echo '</html>'."\r\n";
<?php
/********************************************************
SambaPOS 5 DB Operations Tool
********************************************************
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
********************************************************
Developed and Tested using WAMPServer 2.5 32bit
http://www.wampserver.com/en/#download-wrapper
Component Versions used
SQL Express 2014 (v11) 64-bit
Apache 2.4.9
PHP 5.6.0 (QMcKay)
MYSQL 5.6.17 (not required)
********************************************************/
/*******************************************************
User Settings
********************************************************/
// 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\SAMBAPOS";
$myUser = "sa";
$myPass = "samba";
$myDB = "SambaPOS5";
// 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');
// Menu Defaults
// Colours can be by Name or by Hex RGB code (#AAFF00) - http://colorschemedesigner.com/
$DefaultCategoryButtonColour = "Orange";
$DefaultMenuItemButtonColour = "Green";
$CategoryButtonHeight = "75";
$SubCategoryButtonHeight = "75";
$MenuItemButtonHeight = "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
//E_ALL & ~E_DEPRECATED & ~E_STRICT
error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED & ~E_STRICT);
ini_set("display_errors", 1);
// character set
// PHP's default character set is set to empty.
// http://php.net/default-charset
ini_set("default_charset","UTF-8");
// maximum amount of time allowed for script execution
ini_set("max_execution_time","120");
/*******************************************************
End of user settings
*******************************************************/
$DBOPStoolVersion = "124c (5.1.58-60)";
$DBOPScompatVersion = "5.1.58-60";
$DBOPSdbVersion = "124";
ini_set("auto_detect_line_endings", 1);
include ("dbops_functions.php");
//connection to MSSQL database
$dbhandle = odbc_connect("Driver=$driver;Server=$myServer;Database=$myDB;", $myUser, $myPass);
if (!$dbhandle) {
$msg1 = "Couldn't connect to SQL Server on: $myServer";
$msg2 = odbc_errormsg();
THROWERROR($msg1,$msg2,TRUE);
}
$query = "
SELECT Name, database_id, create_date
FROM sys.databases
WHERE Name like 'Samba%' or Name like 'import%'
ORDER BY Name
";
$qparms = array();
$dbNames = ExecQueryP($query,$qparms);
$dbCount = count($dbNames);
//close DB connection
if ($dbhandle) {
odbc_close($dbhandle);
}
// use chosen DB or default to $myDB
$db = $_GET["db"];
$db = ($db != '' ? $db : ($myDB != '' ? $myDB : 'SambaPOS5'));
$db_name = $db;
// get CSV File List
$myImportFolder = ($myImportFolder=="" ? "" : (substr($myImportFolder,-1)=="/" ? $myImportFolder : $myImportFolder."/"));
$fiCount = 0;
$fiList = array();
if ($fhandle = opendir(($myImportFolder=="" ? "." : $myImportFolder))) {
while (false !== ($fi = readdir($fhandle)))
{
if ($fi != "." && $fi != ".." && strtolower(substr($fi, strrpos($fi, '.') + 1)) == 'csv')
{
$fiCount++;
$fiList[$fiCount]["Name"] = $fi;
}
}
closedir($fhandle);
sort($fiList);
}
// use chosen CSV or default to $myImportFile
$csv = $_GET["csv"];
$csv = ($csv != '' ? $csv : ($myImportFile != '' ? $myImportFile : ''));
$myImportFile = $csv;
// Rock n Roll
$op = $_GET["op"];
if ($op!='readfile' && $op!='importproducts' && $op!='readproducts' && $op!='exportproducts' && $op!='deleteproducts' && $op!='deletetransactions') {
$op='';
}
if ($op=='exportproducts') {
include ("dbops_$op.php");
}
if ($allowUploads) {
$filecount = count($_FILES['filesToUpload']['name']);
$uploadAttempt = (($filecount>0 && $_FILES['filesToUpload']['name'][0]=="") ? true : false);
//echo "FC:$filecount";
//echo "-".$_FILES['filesToUpload']['name'][0]."-";
if ($filecount>0 && $_FILES['filesToUpload']['name'][0]!="") {
//$destination = realpath(dirname(__FILE__));
$destination = __DIR__ . "/" . $myImportFolder;
//echo $destination;
for ($f = 0; $f < $filecount; $f++) {
$bakmade[$f] = 0;
$filetocopy[$f] = $_FILES['filesToUpload']['tmp_name'][$f];
$filename[$f] = $_FILES['filesToUpload']['name'][$f];
$filesize[$f] = $_FILES['filesToUpload']['size'][$f];
$filetyp[$f] = $_FILES['filesToUpload']['type'][$f];
$ferror[$f] = $_FILES['filesToUpload']['error'][$f];
//echo "fc:$filecount".$filename[$f].'<br />';
}
for ($f = 0; $f < $filecount; $f++) {
if (file_exists($destination . $filename[$f])) {
if (!file_exists($destination . 'bak/')) {
mkdir($destination . 'bak/');
}
copy($destination . $filename[$f], $destination . 'bak/' . $filename[$f]);
$bakmade[$f] = 1;
}
copy($filetocopy[$f], $destination . $filename[$f]);
}
}
}
echo '<!DOCTYPE html>'."\r\n";
echo '<html>'."\r\n";
echo '<head>'."\r\n";
echo '<title>SambaPOS 5 DBops v'.$DBOPStoolVersion.' DB:'.$DBOPSdbVersion.'</title>'."\r\n";
echo '<link rel="stylesheet" type="text/css" href="dbops.css" media="screen" />';
//if ($op=='exportproducts') {
// $rd = 'dbops.php?db='.$db_name.'&csv='.$myImportFile;
// echo '<meta http-equiv="refresh" content="10; url='.$rd.'">';
//}
?>
<SCRIPT language="Javascript" type="text/javascript">
function verifyop(op) {
var db = document.getElementById("DBname").value;
var csv = document.getElementById("CSVfile").value;
var execop = confirm("Are you certain you want to "+op+" ?\r\nDB : "+db+"\r\nCSV : "+csv);
if (execop==true) {
if (document.getElementById('op')) {
document.getElementById('op').innerHTML = op;
}
if (document.getElementById('output')) {
document.getElementById('output').innerHTML = '<div style="font-size:26px;font-weight:bold;padding:10px;background-color:#55FF55;text-align:center;margin:auto;">EXECUTING: '+op+'<br/><br/>... please wait ...<br/></div>';
}
if (document.getElementById('busy')) {
document.getElementById('busy').style.display = 'block';
//document.getElementById('busy').innerHTML = 'Elapsed Seconds: <span id="elapsed">0</span>';
// document.getElementById('elapsed').innerHTML = '0';
}
setInterval(busy,1000);
op = op.toLowerCase();
op = op.replace(" ","");
location.href='dbops.php?db='+db+'&csv='+csv+'&op='+op;
}
}
function busy(op) {
if (document.getElementById('elapsed')) {
var content = Number(document.getElementById('elapsed').innerHTML);
content++;
document.getElementById('elapsed').innerHTML = content;
}
}
function switchDB() {
var db = document.getElementById("DBname").value;
var csv = document.getElementById("CSVfile").value;
location.href='dbops.php?db='+db+'&csv='+csv;
}
function switchFile() {
var db = document.getElementById("DBname").value;
var csv = document.getElementById("CSVfile").value;
location.href='dbops.php?db='+db+'&csv='+csv;
}
function hideDIV(divID) {
var elem = document.getElementById(divID);
elem.style.display='none';
elem.style.visibility='hidden';
}
function showDIV(divID) {
var elem = document.getElementById(divID);
elem.style.display='block';
elem.style.visibility='visible';
}
</SCRIPT>
<?php
echo '</head>'."\r\n";
echo '<body>'."\r\n";
echo '<DIV class="area_flex">'."\r\n";
echo '<DIV class="op_main" style="background-color:#77BEFA;" onclick="verifyop('."'READ File'".');">'."\r\n";
echo '<b style="font-size:20px">READ File</b><br />'."\r\n";
echo '<i>Reads CSV File rows and displays output, without importing.</i>'."\r\n";
echo '</DIV>'."\r\n";
echo '<DIV class="op_main" style="background-color:#BBBBFF;" onclick="verifyop('."'IMPORT Products'".');">'."\r\n";
echo '<b style="font-size:20px">IMPORT Products</b><br />'."\r\n";
echo '<i>Imports Products, Menu Items, Inventory from CSV file.</i>'."\r\n";
echo '</DIV>'."\r\n";
echo '<DIV class="op_main" style="background-color:#AAFFAA;" onclick="verifyop('."'READ Products'".');">'."\r\n";
echo '<b style="font-size:20px">READ Products</b><br />'."\r\n";
echo '<i>Reads Products, Menu Items, Inventory from Database.</i>'."\r\n";
echo '</DIV>'."\r\n";
echo '<DIV class="op_main" style="background-color:#FFAAAA;" onclick="verifyop('."'DELETE Products'".');">'."\r\n";
echo '<b style="font-size:20px">DELETE Products</b><br />'."\r\n";
echo '<i>DELETES Products, Menu Items, Inventory from Database</i>'."\r\n";
echo '</DIV>'."\r\n";
echo '<DIV class="op_main" style="background-color:#FF8888;" onclick="verifyop('."'DELETE Transactions'".');">'."\r\n";
echo '<b style="font-size:20px">DELETE Transactions</b><br />'."\r\n";
echo '<i>DELETES all Transactions (i.e. Sales) from Database.</i>'."\r\n";
echo '</DIV>'."\r\n";
echo '</DIV>'."\r\n";
echo '<DIV class="area_flex" style="margin-top:2px;">'."\r\n";
echo '<DIV style="margin:0px;padding:5px;">'."\r\n";
echo 'DB: ';
echo '<select name="DBname" id="DBname" size="1" title="Select the Database you want to affect." onChange="switchDB(this.value);">';
for ($dbi=1; $dbi<=$dbCount; $dbi++) {
echo '<option value="'.$dbNames[$dbi]["Name"].'"'.($dbNames[$dbi]["Name"]==$db_name ? ' selected' : '').'>'.$dbNames[$dbi]["Name"].'</option>';
}
echo '</select>';
echo ' <input type="button" value="Export Products" title="Export Products to CSV for the selected Database. You will be prompted to save the File locally." onclick="verifyop('."'EXPORT Products'".');">';
echo '</DIV>'."\r\n";
echo '<DIV style="align-self:flex-end;background-color:#AAAAAA;margin:0px;padding:5px;text-align:right;">'."\r\n";
echo "[SambaPOS 5 DBops v$DBOPStoolVersion DB:$DBOPSdbVersion]";
echo '</DIV>'."\r\n";
echo '</DIV>'."\r\n";
echo '<DIV style="display:flex;width:100%;margin-top:2px;">'."\r\n";
echo '<DIV style="padding:5px;">'."\r\n";
echo '<span title="The Path on the Server where the CSV Files reside.">CSV'.($myImportFolder!="" ? " ($myImportFolder)" : " (script Path) ").': </span>';
echo '<select name="CSVfile" id="CSVfile" size="1" title="Select the CSV File you want to Read or Import into the selected Database." onChange="switchFile(this.value);">';
for ($f=0; $f<$fiCount; $f++) {
echo '<option value="'.$fiList[$f]["Name"].'"'.($fiList[$f]["Name"]==$csv ? ' selected' : '').'>'.$fiList[$f]["Name"].'</option>';
}
echo '</select>';
echo ' <input type="button" value="Refresh CSV list" title="Click HERE to refresh the CSV File list. You will need to do this after an EXPORT operation." onclick="verifyop('."'REFRESH CSV'".');">';
if ($allowUploads) {
//$filestoaccept = '.mp3, image/jpeg, image/gif, image/png, image/tiff, application/pdf';
$filestoaccept = '.csv, .CSV';
// echo "\r\n".'<div style="margin-top:10px">';
$formAction = "dbops.php?db=$db_name&csv=$myImportFile";
echo '<form style="display:inline-block" id="myForm" id="myForm" method="POST" action="'.$formAction.'" enctype="multipart/form-data">';
echo ' | <input type="submit" value=" UPLOAD " title="Upload selected Files to the Server."> ';
echo '<input type="file" name="filesToUpload[]" id="filesToUpload" size="120" length="0" maxlength="2048" maxtime="610" multiple="" onChange="makeFileList();" title="File' . ($browserIE ? '' : '(s)') . ' to upload - click here to choose file' . ($browserIE ? '' : '(s)') . '" ACCEPT="' . $filestoaccept . '" /><br />' . "\r\n";
echo '<input type="hidden" name="filenamesave" value="' . $_FILES['filesToUpload']['name'] . '">' . "\r\n";
echo '</form>';
// echo "</div>\r\n";
}
echo '</DIV>'."\r\n";
echo '</DIV>'."\r\n";
echo '<hr />'."\r\n";
echo ($uploadAttempt ? "<br />Choose Files to upload first, then click UPLOAD." : "");
//connection to MSSQL database
$dbhandle = odbc_connect("Driver=$driver;Server=$myServer;Database=$db_name;", $myUser, $myPass);
if (!$dbhandle) {
$msg1 = "Couldn't connect to SQL Server on: $myServer Database:[$db_name]";
$msg2 = odbc_errormsg();
THROWERROR($msg1,$msg2,TRUE);
}
//echo 'Connected successfully - MSSQL<br /><br />';
//$query = "SELECT [Version], [AppliedOn] FROM [VersionInfo] WHERE [Version] IN (SELECT max([Version]) FROM [VersionInfo])";
$query = "SELECT [Version] FROM [VersionInfo] WHERE [Version] IN (SELECT max([Version]) FROM [VersionInfo])";
$qparms = array();
$qrydata = ExecQueryP($query,$qparms);
$DBversion = $qrydata[1]["Version"];
$DBdate = $qrydata[1]["AppliedOn"];
$DBmismatchWarning = '<div style="font-size:18px;font-weight:bold;color:#FF0000">';
$DBmismatchWarning.= "WARNING: Database Version Mismatch. USE AT YOUR OWN RISK!";
$DBmismatchWarning.= '<div style="font-size:18px;font-weight:bold;color:#000000">';
$DBmismatchWarning.= "This Tool may or may not work properly.";
$DBmismatchWarning.= "<br />This Tool designed to work on SambaPOS DB version <span style='color:#009900'>$DBOPSdbVersion</span>.";
$DBmismatchWarning.= "<br />Your SambaPOS DB version for database <span style='color:#0000FF'>[$db_name]</span> is: <span style='color:#FF0000'>$DBversion</span>";
$DBmismatchWarning.= '</div>';
$DBmismatchWarning.= '</div>';
echo ($DBOPSdbVersion!=$DBversion ? $DBmismatchWarning : '');
if ($op=='') {
goto theend;
}
echo 'OP: ';
echo '<span id="op" style="font-weight:bold;">'.$op."</span>";
echo "<br /><br />\r\n";
//echo '<span id="wait"></span>';
echo '<div id="output">';
// Todays Date/Time
$today = date("Y-m-d H:i:s");
$smtime = microtime(true);
list($s,$us) = explode('.',$smtime);
$start_time = date("H:i:s.").$us;
echo "Start : ".$start_time."<br />";
// *******************************************************
// READ CSV File
// *******************************************************
if ($op=='readfile') {
$showRead = true;
include ("dbops_$op.php");
}
// *******************************************************
// READ Products
// *******************************************************
if ($op=='readproducts') {
include ("dbops_$op.php");
}
// *******************************************************
// EXPORT Products
// *******************************************************
if ($op=='exportproducts') {
//include ("dbops_$op.php");
}
// *******************************************************
// DELETE Products
// *******************************************************
if ($op=='deleteproducts') {
include ("dbops_$op.php");
}
// *******************************************************
// DELETE Transactions
// *******************************************************
if ($op=='deletetransactions') {
include ("dbops_$op.php");
}
// *******************************************************
// IMPORT Products
// *******************************************************
if ($op=='importproducts') {
include ("dbops_$op.php");
}
finish:
$emtime = microtime(true);
list($s,$us) = explode('.',$emtime);
$finish_time = date("H:i:s.").$us;
echo '<hr />';
echo "<br />Finish : ".$finish_time;
$time1 = strtotime($start_time);
$time2 = strtotime($finish_time);
//$diff = $time2 - $time1;
$diff = $emtime-$smtime;
//echo "<br />Execution Time : ".date('i:s.u', $diff);
echo "<br />Execution Time (s): ".$diff;
theend:
//close DB connection
if ($dbhandle) {
odbc_close($dbhandle);
}
echo "\r\n</div>\r\n";
if ($allowUploads) {
?>
<SCRIPT language="Javascript" type="text/javascript">
function chooseFile() {
var ipath = '<?php echo $myImportFolder.'/'; ?>';
if (document.getElementById("CSVfile")) {
var ifile = document.getElementById("CSVfile").value;
alert ("File:"+ipath+ifile);
}
}
</SCRIPT>
<?php
}
echo '<div id="busy" style="display:none;font-size:26px;font-weight:bold;padding:10px;background-color:#55FF55;text-align:center;margin:auto;">';
echo 'Elapsed Seconds: <span id="elapsed">0</span>';
echo '</div>';
echo '</body>'."\r\n";
echo '</html>'."\r\n";
Looks like an older version.
v124c (2016-07-14) QMcKay One he has
v124d (2016-08-20) QMcKay Latest Version
Use the code that I provided above.
There is a small part missing in your version that is causing the problem.
It is in the verifyop(op) function … you are missing the parts outlined in red…
P.S. there might be more problems with your version of the file, so just copy the code I provided and paste it into your file, change the DB config information, and save it.
It’s old. Likely won’t work without modification.
is there anyone maintaining this great PHP script now ?
