CREATE TABLE [dbo].[AccessLog](
[RcdID] [int] IDENTITY(1,1) NOT NULL,
[TerminalSerialNo] [char](20) NULL,
[CardID] [char](20) NULL,
[CustomerID] [char](16) NULL,
[LogDateTimeTerminal] [datetime] NULL,
[LogDateTerminal] [int] NULL,
[LogTimeTerminal] [int] NULL,
[LogDateTimeServer] [datetime] NULL,
[DateOfBirth] [datetime] NULL,
[TransactionType] [smallint] NOT NULL,
[ExpiryDate] [int] NULL,
[Description] [char](20) NULL,
[Quantity] [smallint] NULL,
[Amount] [decimal](17, 2) NULL,
[BalPrepaid] [decimal](17, 2) NULL,
[BonusAmount] [int] NULL,
[AccSpending] [decimal](17, 2) NULL,
[MemberInfo] [smallint] NULL,
[StaffID] [char](20) NULL,
[TerminalCode] [smallint] NULL,
[BranchCode] [smallint] NULL,
[CompanyCode] [smallint] NULL,
[TransactionID] [char](20) NULL,
[CardSN] [char](16) NULL,
[BalBonus] [int] NULL,
[BlacklistVersion] [datetime] NULL,
[TransactionCounter] [int] NULL,
[RefCode] [int] NULL,
[ProposedExpiryDate] [int] NULL,
[Age][int] NULL
) ON [PRIMARY];
AccessLog |
The transaction log which store all access log sent from Terminals |
RcdID |
An auto increment field. |
TerminalSerialNo |
The serial number of the Terminal where this log is from |
CardID |
The CardID of the card involved in this transaction 0-4294967295 |
CustomerID |
The CustomerID of the card involved in this transaction. 0-9999999999999999 |
LogDateTimeTerminal |
The time when the transaction is done on the Terminal based on Terminal local time. |
LogDateTerminal |
The integer representation of the date value of LogDateTimeTerminal. It is created for the convenience of query. Format: yyyymmdd |
LogTimeTerminal |
The integer representation of the time value of LogDateTimeTerminal. It is created for the convenience of query. Format: hhmmss |
LogDateTimeServer |
The time when the server receive this transaction |
DateOfBirth |
The date of birth of the member of the card involved in this transaction. |
TransactionType |
The code which identifies the transaction type. |
ExpiryDate |
The expiry date of the card involved in this transaction. If the expiry date is in last visit mode, the format is yyyymmdd. If the expiry date isn’t set in the card it will be zero. If the expiry date is fixed month mode the format is yyyymm99. |
Description |
The item code of the gift of product selected from iBonus Display |
Quantity |
The quantity of the redemption transaction 1-31 |
Amount |
The amount of money changed in this transaction 0-8388607.00 |
BalPrepaid |
The amount of money of the card after this transaction. |
BonusAmount |
The amount of bonus point changed in this transaction. 0-16777215 |
AccSpending |
The amount of accumulated spending in the card in this transaction. 0-2147483647.00 |
MemberInfo |
The flag which represent the user’s answer in the multiple choice question of iBonus Display |
StaffID |
The staffID of the staff who is responsible for this transaction. |
TerminalCode |
The Terminal Code of the Terminal |
BranchCode |
The Branch Code of the Terminal |
CompanyCode |
The Company Code of the Terminal |
TransactionID |
The transaction ID sent from POS (cashier). It is used for relaying the iBonus transaction with POS transaction. |
CardSN |
The serial number of the card involved in this transaction. |
BalBonus |
The Bonus point in the card after this transaction. |
BlacklistVersion |
The version of blacklist saved in iBonus Terminal when this transaction takes place. |
TransactionCounter |
The number which serializes the transaction of each member card. |
RefCode |
The reference code used to identify the renew, replace and repair transactions. |
ProposedExpiryDate |
The expiry date calculated by the Terminal during renew. |
Age |
The age of the customer/member derived from his or her birthday and LogDateTimeTerminal. It is set as 0 if birthday is not defined. |
CREATE TABLE [dbo].[Member](
[CardSN] [char](16) NOT NULL,
[CardID] [char](20) NULL,
[CustomerID] [char](16) NULL,
[MemberInfo] [smallint] NULL,
[DateOfBirth] [datetime] NULL,
[ExpiryDate] [int] NULL,
[BalBonus] [int] NULL,
[AccSpending] [decimal](17, 2) NULL,
[BalPrepaid] [decimal](17, 2) NULL,
[LastVisit] [datetime] NULL,
[Blacklisted] [bit] NULL,
[Replaced] [bit] NULL,
[CustomerIDDuplicated] [bit] NULL,
[TransactionCounter] [int] NULL,
[ProposedExpiryDate] [int] NULL,
CONSTRAINT [PK_Member] PRIMARY KEY CLUSTERED
([CardSN] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
Member |
The table which stores the detail of all member cards and brand new cards. |
CardID |
The CardID of the card. 0-4294967295 |
CustomerID |
The CustomerID of the card. 0-9999999999999999 |
DateOfBirth |
The date of birth of the member of the card involved in this transaction. |
ExpiryDate |
The expiry date of the card. If the expiry date is in last visit mode, the format is yyyymmdd. If the expiry date isn’t set in the card it will be zero. If the expiry date is fixed month mode the format is yyyymm99. |
BalPrepaid |
The amount of money of the card. |
AccSpending |
The amount of accumulated spending in the. 0-2147483647.00 |
MemberInfo |
The flag which represent the user’s answer in the multiple choice question of iBonus Display |
CompanyCode |
The Company Code of the Terminal |
TransactionID |
The number which serialize the transaction of the member card. |
CardSN |
The serial number of the card. |
BalBonus |
The Bonus point in the card. |
LastVisit |
The time when this card last did transaction. |
Blacklisted |
The flag stating if this card is blacklisted |
Replaced |
The flag stating if this card is replaced |
ProposedExpiryDate |
The new expiry date requested during renew. |
CREATE TABLE [dbo].[Staff](
[CardID] [char](20) NULL,
[CardSN] [char](16) NOT NULL,
[StaffName] [char](16) NULL,
[StaffInfo] [char](16) NULL,
[Blacklisted] [bit] NULL,
CONSTRAINT [PK_Staff] PRIMARY KEY CLUSTERED
([CardSN] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
Staff |
The table which stores the detail of all staff cards. |
CardID |
The CardID of the card. 0-4294967295 |
CardSN |
The serial number of the card. |
StaffName |
Not Used |
StaffInfo |
Not Used |
Blacklisted |
The flag stating if this card is blacklisted |
CREATE TABLE [dbo].[Terminals](
[SerialNo] [char](20) NOT NULL,
[StatusCode] [smallint] NULL,
[Warning] [smallint] NULL,
[TimeLastTransaction] [datetime] NULL,
[TimeLastVisit] [datetime] NULL,
[UsingBlacklistVer] [datetime] NULL,
[IPAddress] [char](15) NULL,
[BranchCode] [smallint] NULL,
[TerminalCode] [smallint] NULL,
[Description] [char](40) NULL,
CONSTRAINT [PK_Terminals] PRIMARY KEY CLUSTERED
([SerialNo] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
Terminals |
The table which stores the status, timing and info about every terminal. |
SerialNo |
The serial number of the Terminal |
StatusCode |
The status of the Terminal. active = 1 inactive = 2 |
Warning |
The warning which states if there is anything abnormal about the terminal Can be the OR of the followings: Ok = 0 Exceed last visit = 1 Exceed transaction log =2 Exceed linger request =4 Duplicated branch code and terminal Code = 8 Blacklist not update = 16 |
TimeLastTransaction |
The time when the Terminal sends back transaction. |
TimeLastVisit |
The time when the Terminal visit server. |
TerminalCode |
The Terminal Code of the Terminal |
BranchCode |
The Branch Code of the Terminal |
CompanyCode |
The Company Code of the Terminal |
IPAddress |
The IP address that the server recognize the Terminal as. |
UsingBlacklistVersion |
The blacklist version which is in the terminal. |
Description |
The text which describes the terminal. |
CREATE TABLE [dbo].[RenewRequests](
[TerminalSerialNo] [char](20) NOT NULL,
[RefCode] [int] NOT NULL,
[CardSN] [char](16) NOT NULL,
[Status] [smallint] NULL,
[TimeChgState] [datetime] NOT NULL,
[ProposedExpiryDate] [int] NULL,
[RecordExpiryDate] [datetime] NULL,
CONSTRAINT [PK_RenewRequests] PRIMARY KEY CLUSTERED
(
[TerminalSerialNo] ASC,
[RefCode] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
RenewRequests |
The table which stores the detail of all renew request. |
TerminalSerialNo |
The serial number of the request originating terminal |
RefCode |
The ref code the which refer to the request |
Status |
The approval status of the request |
TimeChgState |
The time when the status is last changed |
CardSN |
The serial number of the card |
ProposedExpiryDate |
The new expiry date generated in the renew request operation. |
RecordExpiryDate |
The expiry date of this record. |
CREATE TABLE [dbo].[ReplaceRequests](
[TerminalSerialNo] [char](20) NOT NULL,
[RefCode] [int] NOT NULL,
[CardSN] [char](16) NULL,
[Status] [smallint] NULL,
[TimeChgState] [datetime] NOT NULL,
[RequiredBlacklistVersion] [datetime] NULL,
[ExpiryDate] [int] NULL,
[CardID] [char](20) NULL,
[AccSpending] [decimal](17, 2) NULL,
[MemberInfo] [smallint] NULL,
[DateOfBirth] [datetime] NULL,
[BalBonus] [int] NULL,
[BalPrepaid] [decimal](17, 2) NULL,
[CustomerID] [char](16) NULL,
[TransactionCounter] [int] NULL,
[RecordExpiryDate] [datetime] NULL,
CONSTRAINT [PK_ReplaceRequests] PRIMARY KEY CLUSTERED
([TerminalSerialNo] ASC,
[RefCode] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
ReplaceRequests |
The table which stores the detail of all replace request. |
TerminalSerialNo |
The serial number of the request originating terminal |
RefCode |
The ref code the which refer to the request |
CardID |
The CardID of the card. 0-4294967295 |
Status |
The approval status of the request |
TimeChgState |
The time when the status is last changed |
RequiredBlacklistVersion |
The blacklist version which the terminal is required to have before this request can be approved. |
CustomerID |
The CustomerID of the card. 0-9999999999999999 |
DateOfBirth |
The date of birth of the member of the card involved in this transaction. |
ExpiryDate |
The expiry date of the card. If the expiry date is in last visit mode, the format is yyyymmdd. If the expiry date isn’t set in the card it will be zero. If the expiry date is fixed month mode the format is yyyymm99. |
BalPrepaid |
The amount of money of the card. |
AccSpending |
The amount of accumulated spending in the. 0-2147483647.00 |
MemberInfo |
The flag which represent the user’s answer in the multiple choice question of iBonus Display |
CompanyCode |
The Company Code of the Terminal |
TransactionID |
The transaction ID sent from POS (cashier). It is used for relaying the iBonus transaction with POS transaction. |
CardSN |
The serial number of the card. |
TransactionCounter |
The counter which serialize the member card transactions |
BalBonus |
The Bonus point in the card. |
RecordExpiryDate |
The expiry date of this record. |
CREATE TABLE [dbo].[RepairRequests](
[TerminalSerialNo] [char](20) NOT NULL,
[RefCode] [int] NOT NULL,
[CardSN] [char](16) NULL,
[Status] [smallint] NULL,
[TimeChgState] [datetime] NULL,
[ExpiryDate] [int] NULL,
[CardID] [char](20) NULL,
[AccSpending] [decimal](17, 2) NULL,
[MemberInfo] [smallint] NULL,
[DateOfBirth] [datetime] NULL,
[BalBonus] [int] NULL,
[BalPrepaid] [decimal](17, 2) NULL,
[CustomerID] [char](16) NULL,
[TransactionCounter] [int] NULL,
[RecordExpiryDate] [datetime] NULL,
CONSTRAINT [PK_RepairRequests] PRIMARY KEY CLUSTERED
([TerminalSerialNo] ASC,
[RefCode] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
RepairRequests |
The table which stores the detail of all repair request. |
TerminalSerialNo |
The serial number of the request originating terminal |
RefCode |
The ref code the which refer to the request |
CardID |
The CardID of the card. 0-4294967295 |
Status |
The approval status of the request |
TimeChgState |
The time when the status is last changed |
CustomerID |
The CustomerID of the card. 0-9999999999999999 |
DateOfBirth |
The date of birth of the member of the card involved in this transaction. |
ExpiryDate |
The expiry date of the card. If the expiry date is in last visit mode, the format is yyyymmdd. If the expiry date isn’t set in the card it will be zero. If the expiry date is fixed month mode the format is yyyymm99. |
BalPrepaid |
The amount of money of the card. |
AccSpending |
The amount of accumulated spending in the. 0-2147483647.00 |
MemberInfo |
The flag which represent the user’s answer in the multiple choice question of iBonus Display |
CompanyCode |
The Company Code of the Terminal |
CardSN |
The serial number of the card. |
TransactionCounter |
The counter which serialize the member card transactions |
BalBonus |
The Bonus point in the card. |
RecordExpiryDate |
The expiry date of this record. |