SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TEMPLATE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TEMPLATE](
	[TEMPLATEID] [int] IDENTITY(1,1) NOT NULL,
	[USERID] [int] NOT NULL,
	[FINGERID] [int] NOT NULL,
	[TEMPLATE] [image] NOT NULL,
	[TEMPLATE2] [image] NULL,
	[TEMPLATE3] [image] NULL,
	[BITMAPPICTURE] [image] NULL,
	[BITMAPPICTURE2] [image] NULL,
	[BITMAPPICTURE3] [image] NULL,
	[BITMAPPICTURE4] [image] NULL,
	[USETYPE] [smallint] NULL,
	[EMACHINENUM] [varchar](3) NULL,
	[TEMPLATE1] [image] NULL,
	[Flag] [smallint] NULL DEFAULT ((1)),
	[DivisionFP] [smallint] NULL DEFAULT ((0)),
	[TEMPLATE4] [image] NULL,
	[Length] [int] NULL DEFAULT ((0)),
	[SN] [varchar](25) NULL,
 CONSTRAINT [TEMPLATED] PRIMARY KEY CLUSTERED 
(
	[TEMPLATEID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BroadcastEventDone]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[BroadcastEventDone](
	[EventID] [int] NOT NULL,
	[UpdateComm] [varchar](50) NULL,
	[SN] [varchar](25) NULL,
	[DoneBySN] [varchar](25) NULL,
	[TargetSN] [varchar](25) NULL,
	[Result] [varchar](3) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SystemParameter]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SystemParameter](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[arg1] [varchar](30) NULL,
	[arg2] [varchar](30) NULL,
	[arg3] [varchar](30) NULL,
	[arg4] [varchar](30) NULL,
 CONSTRAINT [Id] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MachineStamp]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[MachineStamp](
	[DevSN] [varchar](20) NOT NULL,
	[ATTLOGStamp] [varchar](10) NULL CONSTRAINT [DF_MachineStamp_ATTLOGStamp]  DEFAULT ((0)),
	[OPERLOGStamp] [varchar](10) NULL CONSTRAINT [DF_MachineStamp_OPERLOGStamp]  DEFAULT ((0)),
	[ATTPHOTOStamp] [varchar](10) NULL CONSTRAINT [DF_MachineStamp_ATTPHOTOStamp]  DEFAULT ((0)),
	[SMSStamp] [varchar](10) NULL CONSTRAINT [DF_MachineStamp_SMSStamp]  DEFAULT ((0)),
	[USER_SMSStamp] [varchar](10) NULL CONSTRAINT [DF_MachineStamp_USER_SMSStamp]  DEFAULT ((0)),
	[USERINFOStamp] [varchar](10) NULL CONSTRAINT [DF_MachineStamp_USERINFOStamp]  DEFAULT ((0)),
	[FINGERTMPStamp] [varchar](10) NULL CONSTRAINT [DF_MachineStamp_FINGERTMPStamp]  DEFAULT ((0)),
 CONSTRAINT [PK_MachineStamp] PRIMARY KEY CLUSTERED 
(
	[DevSN] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USERINFO]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[USERINFO](
	[USERID] [int] IDENTITY(1,1) NOT NULL,
	[Badgenumber] [varchar](24) NOT NULL,
	[SSN] [varchar](20) NULL,
	[Name] [varchar](40) NULL,
	[Gender] [varchar](8) NULL,
	[TITLE] [varchar](20) NULL,
	[PAGER] [varchar](20) NULL,
	[BIRTHDAY] [datetime] NULL,
	[HIREDDAY] [datetime] NULL,
	[street] [varchar](80) NULL,
	[CITY] [varchar](2) NULL,
	[STATE] [varchar](2) NULL,
	[ZIP] [varchar](12) NULL,
	[OPHONE] [varchar](20) NULL,
	[FPHONE] [varchar](20) NULL,
	[VERIFICATIONMETHOD] [smallint] NULL,
	[DEFAULTDEPTID] [smallint] NULL DEFAULT ((1)),
	[SECURITYFLAGS] [smallint] NULL,
	[ATT] [smallint] NOT NULL DEFAULT ((1)),
	[INLATE] [smallint] NOT NULL DEFAULT ((1)),
	[OUTEARLY] [smallint] NOT NULL DEFAULT ((1)),
	[OVERTIME] [smallint] NOT NULL DEFAULT ((1)),
	[SEP] [smallint] NOT NULL DEFAULT ((1)),
	[HOLIDAY] [smallint] NOT NULL DEFAULT ((1)),
	[MINZU] [varchar](8) NULL,
	[PASSWORD] [varchar](20) NULL,
	[LUNCHDURATION] [smallint] NOT NULL DEFAULT ((1)),
	[MVERIFYPASS] [varchar](10) NULL,
	[PHOTO] [image] NULL,
	[Notes] [image] NULL,
	[privilege] [int] NULL DEFAULT ((0)),
	[InheritDeptSch] [smallint] NULL DEFAULT ((1)),
	[InheritDeptSchClass] [smallint] NULL DEFAULT ((1)),
	[AutoSchPlan] [smallint] NULL DEFAULT ((1)),
	[MinAutoSchInterval] [int] NULL DEFAULT ((24)),
	[RegisterOT] [smallint] NULL DEFAULT ((1)),
	[InheritDeptRule] [smallint] NULL DEFAULT ((1)),
	[EMPRIVILEGE] [smallint] NULL DEFAULT ((0)),
	[CardNo] [varchar](20) NULL,
	[SN] [varchar](25) NULL,
 CONSTRAINT [USERIDS] PRIMARY KEY CLUSTERED 
(
	[USERID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AdmsMachines]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AdmsMachines](
	[DevID] [int] IDENTITY(1,1) NOT NULL,
	[MachineName] [varchar](30) NULL,
	[SN] [varchar](25) NOT NULL,
	[IP] [varchar](20) NULL,
	[Stamp] [varchar](20) NULL DEFAULT ('0'),
	[OpStamp] [varchar](20) NULL DEFAULT ('0'),
	[PhotoStamp] [varchar](20) NULL DEFAULT ('0'),
	[ErrorDelay] [int] NOT NULL DEFAULT ((30)),
	[Delay] [int] NOT NULL DEFAULT ((15)),
	[TransTimes] [varchar](60) NULL,
	[TransInterval] [int] NOT NULL DEFAULT ((1)),
	[TransFlag] [varchar](20) NOT NULL DEFAULT ('1111101100'),
	[Realtime] [int] NOT NULL DEFAULT ((1)),
	[Enabled] [bit] NOT NULL CONSTRAINT [DF__AdmsMachi__Enabl__56E8E7AB]  DEFAULT ((1)),
	[Encrypt] [int] NULL DEFAULT ((0)),
	[ServerVer] [varchar](20) NULL,
	[SchClearAttLog] [int] NULL CONSTRAINT [DF_AdmsMachines_SchClearAttLog]  DEFAULT ((0)),
	[SchClearPhoto] [int] NULL CONSTRAINT [DF_AdmsMachines_SchClearPhoto]  DEFAULT ((0)),
 CONSTRAINT [DevID] PRIMARY KEY CLUSTERED 
(
	[DevID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CHECKINOUT]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CHECKINOUT](
	[USERID] [int] NOT NULL,
	[CHECKTIME] [datetime] NOT NULL DEFAULT (getdate()),
	[CHECKTYPE] [varchar](3) NULL DEFAULT ('I'),
	[VERIFYCODE] [int] NULL DEFAULT ((0)),
	[SENSORID] [varchar](5) NULL CONSTRAINT [DF_CHECKINOUT_SENSORID]  DEFAULT ('1'),
	[Memoinfo] [varchar](30) NULL,
	[WorkCode] [int] NULL DEFAULT ((0)),
	[sn] [varchar](20) NULL,
	[UserExtFmt] [smallint] NULL DEFAULT ((0)),
	[BadgenumberBackup] [varchar](24) NULL,
 CONSTRAINT [USERCHECKTIME] PRIMARY KEY CLUSTERED 
(
	[USERID] ASC,
	[CHECKTIME] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WorkSch]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[WorkSch](
	[SchID] [int] IDENTITY(1,1) NOT NULL,
	[SchType] [varchar](50) NULL,
	[PeriodType] [varchar](50) NOT NULL CONSTRAINT [DF_WorkSch_PeriodType]  DEFAULT ('EveryDay'),
	[PeriodTime] [varchar](50) NULL CONSTRAINT [DF_WorkSch_PeriodTime]  DEFAULT (''),
	[WorkTime] [varchar](50) NULL CONSTRAINT [DF_WorkSch_WorkTime]  DEFAULT (''),
	[ReDownload] [bit] NOT NULL CONSTRAINT [DF_WorkSch_ReDownload]  DEFAULT ((0)),
	[Enable] [bit] NOT NULL CONSTRAINT [DF_WorkSch_Enable]  DEFAULT ((0))
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BroadcastEvent]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[BroadcastEvent](
	[EventID] [int] IDENTITY(1,1) NOT NULL,
	[UpdateComm] [varchar](50) NULL,
	[Badgenumber] [varchar](24) NULL,
	[Name] [varchar](40) NULL,
	[MVERIFYPASS] [varchar](10) NULL,
	[privilege] [int] NULL CONSTRAINT [DF__Broadcast__privi__07020F21]  DEFAULT ((0)),
	[CardNo] [varchar](20) NULL,
	[FINGERID] [int] NULL,
	[TEMPLATE] [image] NULL,
	[Length] [int] NULL,
	[SN] [varchar](25) NOT NULL CONSTRAINT [DF__BroadcastEve__SN__07F6335A]  DEFAULT ('FFFFFFFFFFFFF'),
	[TargetSN] [varchar](25) NOT NULL CONSTRAINT [DF__Broadcast__Targe__08EA5793]  DEFAULT ('FFFFFFFFFFFFF'),
 CONSTRAINT [EventID] PRIMARY KEY CLUSTERED 
(
	[EventID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
-----------------------------------------------------------------------------
IF NOT EXISTS(SELECT TOP 1 * FROM SystemParameter)
BEGIN
INSERT INTO SystemParameter(arg1,arg2,arg3,arg4) VALUES('FFFF','FFFF','FFFF','FFFF')
END
GO

IF NOT EXISTS(SELECT TOP 1 * FROM WorkSch WHERE SchType='CLEAR LOG')
BEGIN
INSERT INTO WorkSch(SchType,PeriodType,PeriodTime,WorkTime,ReDownload,Enable) VALUES('CLEAR LOG','EveryDay','','','False','False')  
END
GO

IF NOT EXISTS(SELECT TOP 1 * FROM WorkSch WHERE SchType='CLEAR PHOTO')
BEGIN
INSERT INTO WorkSch(SchType,PeriodType,PeriodTime,WorkTime,ReDownload,Enable) VALUES('CLEAR PHOTO','EveryDay','','','False','False')  
END
GO