· 7 years ago · Nov 02, 2018, 01:14 AM
1-- CREATE DATABASE `CafeSuite`;
2-- USE `CafeSuite`;
3SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
4
5CREATE TABLE IF NOT EXISTS `AccountBill` (
6 `ID` int(11) NOT NULL auto_increment,
7 `UserName` varchar(255) default NULL,
8 `Time` datetime default NULL,
9 `ProductName` varchar(255) default NULL,
10 `ProductID` varchar(255) default NULL,
11 `ProductUnit` varchar(255) default NULL,
12 `ProductVAT` varchar(255) default NULL,
13 `ProductType` varchar(255) default NULL,
14 `Count` decimal(19,4) default '0',
15 `Price` decimal(19,4) default '0.0000',
16 `Operator` varchar(255) default NULL,
17 `LocationId` int(11) default NULL,
18 PRIMARY KEY (`ID`),
19 KEY `ID` (`ID`),
20 KEY `ProductID` (`ProductID`)
21) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
22
23CREATE TABLE IF NOT EXISTS `AccountHistory` (
24 `ID` int(11) NOT NULL auto_increment,
25 `UserName` varchar(255) default NULL,
26 `State` int(11) default '0',
27 `Type` int(11) default '0',
28 `AccountType` int(11) default '0',
29 `Time` datetime default NULL,
30 `StartTime` datetime default NULL,
31 `Workstation` varchar(255) default NULL,
32 `Period` int(11) default '0',
33 `Charge` decimal(19,4) default '0.0000',
34 `Price` decimal(19,4) default '0.0000',
35 `Debt` decimal(19,4) default '0.0000',
36 `Points` int(11) default '0',
37 `Rates` varchar(255) default NULL,
38 `Operator` varchar(255) default NULL,
39 `Reset` tinyint(4) default '0',
40 `LocationId` int(11) default NULL,
41 PRIMARY KEY (`ID`)
42) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
43
44CREATE TABLE IF NOT EXISTS `Accounts` (
45 `ID` int(11) NOT NULL AUTO_INCREMENT,
46 `Deleted` tinyint(4) DEFAULT '0',
47 `Username` varchar(255) NOT NULL,
48 `Barcode` varchar(255) DEFAULT NULL,
49 `SmartCard` text,
50 `Password` varchar(255) DEFAULT NULL,
51 `AccountType` int(11) DEFAULT '0',
52 `AccountGroup` varchar(255) DEFAULT NULL,
53 `TotalTime` int(11) DEFAULT '0',
54 `UsedTime` int(11) DEFAULT NULL,
55 `LeftTime` int(11) DEFAULT NULL,
56 `SpentTime` int(11) DEFAULT NULL,
57 `BonusTime` int(11) DEFAULT '0',
58 `TotalCash` decimal(19,4) DEFAULT '0.0000',
59 `UsedCash` decimal(19,4) DEFAULT '0.0000',
60 `LeftCash` decimal(19,4) DEFAULT NULL,
61 `SpentCash` decimal(19,4) DEFAULT NULL,
62 `SetupFee` decimal(19,4) DEFAULT NULL,
63 `Debt` decimal(19,4) DEFAULT '0.0000',
64 `MaxDebt` decimal(19,4) DEFAULT NULL,
65 `Points` int(11) DEFAULT '0',
66 `TotalPoints` int(11) DEFAULT '0',
67 `Created` datetime DEFAULT NULL,
68 `FirstUsed` datetime DEFAULT NULL,
69 `LastUsed` datetime DEFAULT NULL,
70 `ValidSince` int(11) DEFAULT NULL,
71 `ValidFromDate` datetime DEFAULT NULL,
72 `ValidToDate` datetime DEFAULT NULL,
73 `Expires` datetime DEFAULT NULL,
74 `ValidFromTime` datetime DEFAULT NULL,
75 `ValidToTime` datetime DEFAULT NULL,
76 `ValidForDays` int(11) DEFAULT NULL,
77 `ValidForHours` int(11) DEFAULT NULL,
78 `ValidDays` tinyint(4) DEFAULT '0',
79 `MaxUsagePeriod` int(11) DEFAULT '1440',
80 `MaxUsageInPeriod` int(11) DEFAULT '24',
81 `AllowChangePasswd` tinyint(4) DEFAULT '0',
82 `ForceChangePasswd` tinyint(4) DEFAULT '0',
83 `AllowLogin` tinyint(4) DEFAULT '0',
84 `NoPassword` tinyint(4) DEFAULT '0',
85 `UseWorkstationRates` tinyint(4) DEFAULT '0',
86 `AccumulateTime` tinyint(4) DEFAULT '0',
87 `Rates` varchar(255) DEFAULT NULL,
88 `FirstName` varchar(255) DEFAULT NULL,
89 `MiddleName` varchar(50) DEFAULT NULL,
90 `LastName` varchar(255) DEFAULT NULL,
91 `Title` varchar(50) DEFAULT NULL,
92 `Phone` varchar(255) DEFAULT NULL,
93 `Mobile` varchar(255) DEFAULT NULL,
94 `Fax` varchar(255) DEFAULT NULL,
95 `Address` text,
96 `City` varchar(50) DEFAULT NULL,
97 `Country` varchar(50) DEFAULT NULL,
98 `Email` varchar(255) DEFAULT NULL,
99 `Gender` int(11) DEFAULT '-1',
100 `Birthday` datetime DEFAULT NULL,
101 `Comment` text,
102 `Description` text,
103 `Custom1` varchar(255) DEFAULT NULL,
104 `Custom2` varchar(255) DEFAULT NULL,
105 `Custom3` varchar(255) DEFAULT NULL,
106 `Custom4` varchar(255) DEFAULT NULL,
107 `Custom5` varchar(255) DEFAULT NULL,
108 `Custom6` varchar(255) DEFAULT NULL,
109 `LoginMsg` text,
110 `ProductsDiscount` int(11) DEFAULT NULL,
111 `ServicesDiscount` int(11) DEFAULT NULL,
112 `CollectPoints` tinyint(4) DEFAULT '0',
113 `LimitDebt` tinyint(4) DEFAULT '0',
114 `ShowLoginMsg` int(11) DEFAULT NULL,
115 `LocationId` int(11) DEFAULT NULL,
116 `Machine` varchar(255) DEFAULT NULL,
117 `AutoLimit` int(11) DEFAULT NULL,
118 `LimitPrintouts` tinyint(4) DEFAULT '0',
119 `MaxPrintouts` int(11) DEFAULT NULL,
120 `NoAutoDelete` tinyint(4) DEFAULT '0',
121 `Updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
122 `Locked` tinyint(4) NOT NULL,
123 `DownloadedKB` int(11) NOT NULL,
124 `UploadedKB` int(11) NOT NULL,
125 `TotalTransfer` double NOT NULL,
126 `UsedTransfer` double NOT NULL,
127 PRIMARY KEY (`ID`),
128 UNIQUE KEY `UserName` (`Username`),
129 KEY `Barcode` (`Barcode`),
130 KEY `LocationId` (`LocationId`)
131) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=768 ;
132
133CREATE TABLE IF NOT EXISTS `Locations` (
134 `LocationID` int(11) NOT NULL auto_increment,
135 `Name` varchar(255) default NULL,
136 `Address` text,
137 `Desc` text,
138 `Currency` varchar(3) default NULL,
139 PRIMARY KEY (`LocationID`)
140) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
141
142CREATE TABLE IF NOT EXISTS `Sales` (
143 `ID` int(11) NOT NULL auto_increment,
144 `OrderID` int(11) default NULL,
145 `SessionID` int(11) default NULL,
146 `ReceiptID` int(11) default NULL,
147 `OrderTime` datetime default NULL,
148 `OrderState` int(11) default NULL,
149 `OrderType` int(11) default NULL,
150 `ProductName` varchar(255) default NULL,
151 `ProductUnit` varchar(50) default NULL,
152 `ProductID` varchar(255) default NULL,
153 `NettoPrice` decimal(19,4) default NULL,
154 `Count` decimal(19,4) default NULL,
155 `Charge` decimal(19,4) default NULL,
156 `MinutesCharge` int(11) default NULL,
157 `Discount` int(11) default NULL,
158 `Tax1` int(11) default NULL,
159 `Tax2` int(11) default NULL,
160 `Operator` varchar(255) default NULL,
161 `ShiftID` int(11) default NULL,
162 `Account` varchar(255) default NULL,
163 `Workstation` varchar(255) default NULL,
164 `Location` varchar(255) default NULL,
165 `LocationId` int(11) default NULL,
166 `Machine` varchar(255) default NULL,
167 `Currency` varchar(3) default NULL,
168 `Payment` int(11) default NULL,
169 UNIQUE KEY `ID` (`ID`),
170 KEY `LocationId` (`LocationId`),
171 KEY `OrderID` (`OrderID`),
172 KEY `ReceiptID` (`ReceiptID`),
173 KEY `SessionID` (`SessionID`),
174 KEY `ShiftID` (`ShiftID`)
175) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=154 ;