csmysql.sql

/*
Autor: John Llewelyn
Facebook: fb.com/johnwilliam.llewelyn
Twitter: @JWLLEWELYN
Updated on November 29, 2019
Installation for the first time external database for CafeSuite v3.70.1.
It requires as a database MySQL Server 5.7.28 (x64, 64-bit) Linux or Windows and the MySQL Connector/ODBC 5.3.14 (x86, 32-bit) for the communication between CafeStation and external database.
Please create a user on the MySQL server with privileges only for the cafesuite database.
After installing the database, please read the comments at the bottom of this script.
*/
CREATE DATABASE IF NOT EXISTS `cafesuite`;
USE `cafesuite`;

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

#
# TABLE STRUCTURE FOR TABLE 'AccountBill'
#
CREATE TABLE IF NOT EXISTS `AccountBill` (
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `UserName` VARCHAR(255) DEFAULT NULL,
  `Time` DATETIME DEFAULT NULL,
  `ProductName` VARCHAR(255) DEFAULT NULL,
  `ProductID` VARCHAR(255) DEFAULT NULL,
  `ProductUnit` VARCHAR(255) DEFAULT NULL,
  `ProductVAT` VARCHAR(255) DEFAULT NULL,
  `ProductType` VARCHAR(255) DEFAULT NULL,
  `Count` INTEGER DEFAULT '0',
  `Price` DECIMAL(19,4) DEFAULT '0.00000',
  `Operator` VARCHAR(255) DEFAULT NULL,
  `LocationId` INTEGER DEFAULT NULL,
  INDEX (`ID`),
  INDEX (`LocationId`),
  PRIMARY KEY (`ID`),
  INDEX (`ProductID`)
) ENGINE=myisam DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

#
# TABLE STRUCTURE FOR TABLE 'AccountHistory'
#
CREATE TABLE IF NOT EXISTS `AccountHistory` (
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `UserName` VARCHAR(255) DEFAULT NULL,
  `State` INTEGER DEFAULT '0',
  `Type` INTEGER DEFAULT '0',
  `Time` DATETIME DEFAULT NULL,
  `StartTime` DATETIME DEFAULT NULL,
  `Workstation` VARCHAR(255) DEFAULT NULL,
  `Period` INTEGER DEFAULT '0',
  `Charge` DECIMAL(19,4) DEFAULT '0.00000',
  `Price` DECIMAL(19,4) DEFAULT '0.00000',
  `Debt` DECIMAL(19,4) DEFAULT '0.00000',
  `Rates` VARCHAR(255) DEFAULT NULL,
  `Points` INTEGER DEFAULT '0',
  `Operator` VARCHAR(255) DEFAULT NULL,
  `AccountType` INTEGER DEFAULT '0',
  `Reset` INTEGER DEFAULT '0',
  `LocationId` INTEGER DEFAULT NULL,
  INDEX (`LocationId`),
  PRIMARY KEY (`ID`)
) ENGINE=myisam DEFAULT CHARSET=utf8 AUTO_INCREMENT=2;

#
# TABLE STRUCTURE FOR TABLE 'Accounts'
#
CREATE TABLE IF NOT EXISTS `Accounts` (
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `Username` VARCHAR(255) NOT NULL,
  `Barcode` VARCHAR(255) DEFAULT NULL,
  `SmartCard` LONGTEXT,
  `Password` VARCHAR(255) DEFAULT NULL,
  `AccountType` INTEGER DEFAULT '0',
  `AccountGroup` VARCHAR(255) DEFAULT NULL,
  `TotalTime` INTEGER DEFAULT '0',
  `UsedTime` INTEGER DEFAULT NULL,
  `LeftTime` INTEGER DEFAULT NULL,
  `SpentTime` INTEGER DEFAULT NULL,
  `BonusTime` INTEGER DEFAULT '0',
  `TotalCash` DECIMAL(19,4) DEFAULT '0.00000',
  `UsedCash` DECIMAL(19,4) DEFAULT '0.00000',
  `LeftCash` DECIMAL(19,4) DEFAULT NULL,
  `SpentCash` DECIMAL(19,4) DEFAULT NULL,
  `SetupFee` DECIMAL(19,4) DEFAULT NULL,
  `Debt` DECIMAL(19,4) DEFAULT '0.00000',
  `MaxDebt` DECIMAL(19,4) DEFAULT NULL,
  `Points` INTEGER DEFAULT '0',
  `TotalPoints` INTEGER DEFAULT '0',
  `Created` DATETIME DEFAULT NULL,
  `FirstUsed` DATETIME DEFAULT NULL,
  `LastUsed` DATETIME DEFAULT NULL,
  `ValidSince` INTEGER DEFAULT NULL,
  `ValidFromDate` DATETIME DEFAULT NULL,
  `ValidToDate` DATETIME DEFAULT NULL,
  `Expires` DATETIME DEFAULT NULL,
  `ValidFromTime` DATETIME DEFAULT NULL,
  `ValidToTime` DATETIME DEFAULT NULL,
  `ValidForDays` INTEGER DEFAULT NULL,
  `ValidForHours` INTEGER DEFAULT NULL,
  `ValidDays` TINYINT(3) UNSIGNED DEFAULT '0',
  `MaxUsagePeriod` INTEGER DEFAULT '1440',
  `MaxUsageInPeriod` INTEGER DEFAULT '24',
  `AllowChangePasswd` TINYINT(1) DEFAULT '0',
  `ForceChangePasswd` TINYINT(1) DEFAULT '0',
  `AllowLogin` TINYINT(1) DEFAULT '0',
  `NoPassword` TINYINT(1) DEFAULT '0',
  `UseWorkstationRates` TINYINT(1) DEFAULT '0',
  `AccumulateTime` TINYINT(1) DEFAULT '0',
  `Rates` VARCHAR(255) DEFAULT NULL,
  `FirstName` VARCHAR(255) DEFAULT NULL,
  `MiddleName` VARCHAR(50) DEFAULT NULL,
  `LastName` VARCHAR(255) DEFAULT NULL,
  `Title` VARCHAR(50) DEFAULT NULL,
  `Phone` VARCHAR(255) DEFAULT NULL,
  `Mobile` VARCHAR(255) DEFAULT NULL,
  `Fax` VARCHAR(255) DEFAULT NULL,
  `Address` LONGTEXT,
  `City` VARCHAR(50) DEFAULT NULL,
  `Country` VARCHAR(50) DEFAULT NULL,
  `Email` VARCHAR(255) DEFAULT NULL,
  `Gender` INTEGER DEFAULT '-1',
  `Birthday` DATETIME DEFAULT NULL,
  `Comment` LONGTEXT,
  `Description` LONGTEXT,
  `Custom1` VARCHAR(255) DEFAULT NULL,
  `Custom2` VARCHAR(255) DEFAULT NULL,
  `Custom3` VARCHAR(255) DEFAULT NULL,
  `Custom4` VARCHAR(255) DEFAULT NULL,
  `Custom5` VARCHAR(255) DEFAULT NULL,
  `Custom6` VARCHAR(255) DEFAULT NULL,
  `LoginMsg` LONGTEXT,
  `ProductsDiscount` INTEGER DEFAULT NULL,
  `ServicesDiscount` INTEGER DEFAULT NULL,
  `CollectPoints` TINYINT(1) DEFAULT '0',
  `LimitDebt` TINYINT(1) DEFAULT '0',
  `ShowLoginMsg` INTEGER DEFAULT NULL,
  `LocationId` INTEGER DEFAULT NULL,
  `Machine` VARCHAR(255) DEFAULT NULL,
  `AutoLimit` INTEGER DEFAULT NULL,
  `LimitPrintouts` TINYINT(1) DEFAULT '0',
  `MaxPrintouts` INTEGER DEFAULT NULL,
  `NoAutoDelete` TINYINT(1) DEFAULT '0',
  `Locked` TINYINT(1) DEFAULT '0',
  `Updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `DownloadedKB` INTEGER NOT NULL,
  `UploadedKB` INTEGER NOT NULL,
  `TotalTransfer` INTEGER NOT NULL,
  `UsedTransfer` INTEGER NOT NULL,
  INDEX (`Barcode`),
  INDEX (`LocationId`),
  PRIMARY KEY (`ID`),
  UNIQUE (`Username`)
) ENGINE=myisam DEFAULT CHARSET=utf8 AUTO_INCREMENT=768;

#
# TABLE STRUCTURE FOR TABLE 'Locations'
#

CREATE TABLE IF NOT EXISTS `Locations` (
  `LocationID` INTEGER NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(255) DEFAULT NULL,
  `Address` LONGTEXT,
  `Desc` LONGTEXT,
  `Currency` VARCHAR(3) DEFAULT NULL,
  PRIMARY KEY (`LocationID`)
) ENGINE=myisam DEFAULT CHARSET=utf8 AUTO_INCREMENT=4;

#
# TABLE STRUCTURE FOR TABLE 'Sales'
#
CREATE TABLE IF NOT EXISTS `Sales` (
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `OrderID` INTEGER DEFAULT NULL,
  `SessionID` INTEGER DEFAULT NULL,
  `ReceiptID` INTEGER DEFAULT NULL,
  `OrderTime` DATETIME DEFAULT NULL,
  `OrderState` INTEGER DEFAULT NULL,
  `OrderType` INTEGER DEFAULT NULL,
  `ProductName` VARCHAR(255) DEFAULT NULL,
  `ProductUnit` VARCHAR(50) DEFAULT NULL,
  `ProductID` VARCHAR(255) DEFAULT NULL,
  `NettoPrice` DECIMAL(19,4) DEFAULT NULL,
  `Count` INTEGER DEFAULT NULL,
  `Charge` DECIMAL(19,4) DEFAULT NULL,
  `MinutesCharge` INTEGER DEFAULT NULL,
  `Discount` INTEGER DEFAULT NULL,
  `Tax1` INTEGER DEFAULT NULL,
  `Tax2` INTEGER DEFAULT NULL,
  `Operator` VARCHAR(255) DEFAULT NULL,
  `ShiftID` INTEGER DEFAULT NULL,
  `Account` VARCHAR(255) DEFAULT NULL,
  `Workstation` VARCHAR(255) DEFAULT NULL,
  `Location` VARCHAR(255) DEFAULT NULL,
  `LocationId` INTEGER DEFAULT NULL,
  `Machine` VARCHAR(255) DEFAULT NULL,
  `Currency` VARCHAR(3) DEFAULT NULL,
  `Payment` INTEGER DEFAULT NULL,
  UNIQUE (`ID`),
  INDEX (`LocationId`),
  INDEX (`OrderID`),
  INDEX (`ReceiptID`),
  INDEX (`SessionID`),
  INDEX (`ShiftID`)
) ENGINE=myisam DEFAULT CHARSET=utf8 AUTO_INCREMENT=154;

/*
Adjust the regional configuration, decimal symbol "dot", symbol of thousands of "coma", without changing the country's location.
CafeStation settings, go to "Program" -> "Options" -> "Advanced" -> "External Database".
By default this is the access database, but it is necessary to adjust the access database path that we are going to migrate by default.
%PROGRAMDATA%\Cafesuite\Cafesuite.mdb
Note: If your installation was customized, you must place the path where the access database was installed.
We install MySQL Connector/ODBC and configure it.
We open as administrator: C:\WINDOWS\system32\odbcad32.exe
We create a unicode driver in the system DSN tab.
DSN Name: CafeSuite
user: myusername
password: ******
hostname: myhostname
In CafeStation we change the access database by MySQL.
Enter the credentials of the database, database, user and password.
We evaluate all the options.
we click on the buttons Export accounts to database, Export Sales to database and Import accounts from database.
*/