/* -------------------------------------------- */
/* 2------------------------------------------- */
/* DB connection with AMDatabase, create Table  */
/* -------------------------------------------- */
/* -------------------------------------------- */

/* Access Control System (Local) History Access Information Table */
/* Number of Fields: 12 */

IF OBJECT_ID ('dbo.History') IS NOT NULL
	DROP TABLE dbo.History
GO

CREATE TABLE dbo.History
	(
	EventType                  VARCHAR     (100),                /* type               */
	EventDate                  VARCHAR     (100),                /* Date of access     */
	EventTime                  VARCHAR     (100),                /* Access time        */
	EventPlace                 VARCHAR     (100),                /* user               */
	EventName                  VARCHAR     (100),                /* Granted Approved   */
	EventUserID                VARCHAR     (100),                /* User number        */
	EventUserName              VARCHAR     (100),                /* username           */
	EventUserDept              VARCHAR     (100),                /* None               */
	EventDoorMode              VARCHAR     (100),                /* Door mode          */
	EventDoorState             VARCHAR     (100),                /* Door state Open/Close */
	EventFunKeyName            VARCHAR     (100),                /* Function KEY       */
	EventDesc                  VARCHAR     (100),                /*                    */
	)
GO

/* Access control system (local) Holidays Holiday information table */
/* Number of Fields: 3 */

IF OBJECT_ID ('dbo.Holidays') IS NOT NULL
	DROP TABLE dbo.Holidays
GO

CREATE TABLE dbo.Holidays
	(
	HolidayName                VARCHAR     (100),                /* Holiday Name           */
	HolidayDate                VARCHAR     (100),                /* Holiday date           */
	Used                       VARCHAR     (100),                /* Whether or not to apply*/
	)
GO

/* Access Management System (Local) Member User Information Table */
/* Number of Fields: 37 */

IF OBJECT_ID ('dbo.Member') IS NOT NULL
	DROP TABLE dbo.Member
GO

CREATE TABLE dbo.Member
	(	
	Used                       VARCHAR     (100),             /* Àû¿ëÀ¯¹«                */
	UserID                     VARCHAR     (100),             /* Employee number         */
	FirstName                  VARCHAR     (100),             /* First name              */
	MiddleName                 VARCHAR     (100),             /* middle name             */
	LastName                   VARCHAR     (100),             /* Last name               */
	UserClass                  VARCHAR     (100),             /* Rating                  */
	ID                         VARCHAR     (100),             /* ID                      */
	Password                   VARCHAR     (100),             /* password                */
	CardType                   VARCHAR     (100),             /* Card type               */
	CardAttribute              VARCHAR     (100),             /*                         */
	FacilityCode               VARCHAR     (100),             /* Pesility code           */
	CardID                     VARCHAR     (100),             /* card number             */
	CardID2                    VARCHAR     (100),             /* Card number 2           */
	TimeZoneNo1                VARCHAR     (100),             /* Access Time Range 1     */
	TimeZoneNo2                VARCHAR     (100),             /* Access Time Range 2     */
	CompanyID                  VARCHAR     (100),             /* Company code            */
	Department                 VARCHAR     (100),             /* Department name         */
	Position                   VARCHAR     (100),             /* Name of job title       */
	TelOffice                  VARCHAR     (100),             /* Office number           */
	EngagedDay                 VARCHAR     (100),             /*                         */
	LatestPromoted             VARCHAR     (100),             /*                         */
	RetiredDay                 VARCHAR     (100),             /*                         */
	EMail                      VARCHAR     (100),             /* Email address           */
	License1                   VARCHAR     (100),             /*                         */
	License2                   VARCHAR     (100),             /*                         */
	License3                   VARCHAR     (100),             /*                         */
	Sex                        VARCHAR     (100),             /* Gender classification   */
	Married                    VARCHAR     (100),             /* Marital Status          */
	ResidentRegNo              VARCHAR     (100),             /*                         */
	TelHome                    VARCHAR     (100),             /* Home phone number       */
	MobilePhone                VARCHAR     (100),             /* Cell Phone Number       */
	CarName                    VARCHAR     (100),             /* Car name                */
	Photo                      VARCHAR     (200),             /* Zip code                */
	ZipCode                    VARCHAR     (100),             /* address                 */
	Address                    VARCHAR     (200),             /*                         */
	UserMode                   VARCHAR     (100),             /*                         */
	TempId                     VARCHAR     (100),             /*                         */
	TempIdNumber               VARCHAR     (100),             /*                         */
	sValidity                  VARCHAR     (010),             /* Start of validity period */
	eValidity                  VARCHAR     (010),             /* End of validity period   */
	useFlag                    VARCHAR     (001),             /* 0:Guardian 311 1:Server  */
	dataFlag                   VARCHAR     (001),             /* N:New U:Correction D:Delete      */
	dbstate                    VARCHAR     (001),             /* maintain 1:data delete           */
	rsstate                    VARCHAR     (001),             /* 0:transmission 1:not transmitted */
	sendyn                     VARCHAR     (255),             /* Controller cnt 0:send 1:not send */
	barcnt                     INT         DEFAULT  0,        /* barcode print count     */
        dbdate                     CHAR        (010),             /* DB date                 */
        dbtime                     CHAR        (008),             /* DB time                 */
        srdate                     CHAR        (010),             /* send date               */
        srtime                     CHAR        (008),             /* send time               */
	fpyn1                      int               DEFAULT '0', /* 0:not finger 1:save finger */
	fpyn2                      int               DEFAULT '0',
	fpdata1                    varbinary   (MAX),
	fpdata2                    varbinary   (MAX),
	)
GO

/* Access control system (local) SetupSerial Serial communication information table */
/* Number of Fields: 3 */

IF OBJECT_ID ('dbo.SetupSerial') IS NOT NULL
	DROP TABLE dbo.SetupSerial
GO

CREATE TABLE dbo.SetupSerial
	(
	ControllerName             VARCHAR     (100),                /* Terminal name         */
	ID                         VARCHAR     (100),                /* Terminal ID           */
	Port                       VARCHAR     (100),                /* Communication port    */
	)
GO

/* Access control system (local) SetupTcpip Internet communication information table */
/* Number of Fields: 6 */

IF OBJECT_ID ('dbo.SetupTcpip') IS NOT NULL
	DROP TABLE dbo.SetupTcpip
GO

CREATE TABLE dbo.SetupTcpip
	(
	ControllerName             VARCHAR     (100),                /* Terminal name            */
	ID                         VARCHAR     (100),                /* Terminal ID              */
	Address                    VARCHAR     (100),                /* IP address               */
	Port                       VARCHAR     (100),                /* Port number              */
	Password                   VARCHAR     (100),                /* password                 */
	Fire                       VARCHAR     (100),                /* Fire brigade group number*/
	)
GO

/* Access control system (local) Timezone access time information table */
/* Number of Fields: 6 */

IF OBJECT_ID ('dbo.Timezone') IS NOT NULL
	DROP TABLE dbo.Timezone
GO

CREATE TABLE dbo.Timezone
	(
	TimeZoneNo                 VARCHAR     (100),                /* Access code             */
	TimeZoneName               VARCHAR     (100),                /* Time name               */
	HolSTime1                  VARCHAR     (100),                /* Holiday   start time1   */
	HolETime1                  VARCHAR     (100),                /* Holiday   end time1     */
	HolSTime2                  VARCHAR     (100),                /* Holiday   start time2   */
	HolETime2                  VARCHAR     (100),                /* Holiday   end time2     */
	MonSTime                   VARCHAR     (100),                /* Monday    Start Time    */
	MonETime                   VARCHAR     (100),                /* Monday    end time      */
	TueSTime                   VARCHAR     (100),                /* Tuesday   Start Time    */
	TueETime                   VARCHAR     (100),                /* Tuesday   end time      */
	WedSTime                   VARCHAR     (100),                /* Wednesday Start Time    */
	WedETime                   VARCHAR     (100),                /* Wednesday end time      */
	ThuSTime                   VARCHAR     (100),                /* Thursday  Start Time    */
	ThuETime                   VARCHAR     (100),                /* Thursday  end time      */
	FriSTime                   VARCHAR     (100),                /* Friday    Start Time    */
	FriETime                   VARCHAR     (100),                /* Friday    end time      */
	SatSTime                   VARCHAR     (100),                /* Saturday  Start Time    */
	SatETime                   VARCHAR     (100),                /* Saturday  end time      */
	)
GO

/* doorgroup information Field:3 */

IF OBJECT_ID ('dbo.doorgroup') IS NOT NULL
	DROP TABLE dbo.doorgroup
GO

CREATE TABLE dbo.doorgroup
	(
	dgcode                     char        (016),             /* doorgroup code          */
	dgname                     char        (120),             /* doorgroup name          */
	dgdata                     char        (255),             /* door no                 */
	)
GO

