2.10SQL Statements for Creating Tables

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.