NTLNT.sql

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Agent]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Agent]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Code]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Code]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Company]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Company]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Customer]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Customer]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RepairRecord]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[RepairRecord]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Role]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Role]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SaleRecord]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SaleRecord]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Staff]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Staff]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Vehicle]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Vehicle]
GO

CREATE TABLE [dbo].[Agent] (
 [ID] [int] IDENTITY (2011, 1) NOT NULL ,
 [agenPassword] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [agenName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [agenAddr] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [agenPerson] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [agenPost] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [agenMobile] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [agenTel] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [agenRegUserID] [int] NULL ,
 [agenRegDate] [datetime] NULL ,
 [agenShortName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Code] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [codeID] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [codeName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [codeCategory] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Company] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [compName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [compAddr] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [compPost] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [compTel] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [compRegUserID] [int] NULL ,
 [compRegDate] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Customer] (
 [ID] [int] IDENTITY (1000, 1) NOT NULL ,
 [CustCard] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [CustSex] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [CustBirth] [datetime] NULL ,
 [CustName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [CustIndetity] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [CustPhone] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [CustTel] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [CustAddr] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
 [CustRegUserID] [int] NULL ,
 [CustRegDate] [datetime] NULL ,
 [CustUpdUserID] [int] NULL ,
 [CustUpdDate] [datetime] NULL ,
 [CustMemo] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RepairRecord] (
 [ID] [int] IDENTITY (10000, 1) NOT NULL ,
 [repaCustomerID] [int] NULL ,
 [repaSaleRecordID] [int] NULL ,
 [repaError] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
 [repaReason] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
 [repaStatus] [varchar] (1) COLLATE Chinese_PRC_CI_AS NULL ,
 [repaUserID] [int] NULL ,
 [repaQueryDate] [datetime] NULL ,
 [repaReceiveDate] [datetime] NULL ,
 [repaRegUserID] [int] NULL ,
 [repaRegDate] [datetime] NULL ,
 [repaUpdUserID] [int] NULL ,
 [repaUpdDate] [datetime] NULL ,
 [repaRepairID] [int] NULL ,
 [repaDelFlag] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Role] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [roleName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [roleCode] [varchar] (2056) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SaleRecord] (
 [ID] [int] IDENTITY (10000, 1) NOT NULL ,
 [saleCustomerID] [int] NULL ,
 [saleCompanyID] [int] NULL ,
 [saleAgentID] [int] NULL ,
 [saleVehID] [int] NULL ,
 [saleVehName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [saleVehModel] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [saleVehColor] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [saleEleNumber] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [saleFraNumber] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [saleRegNumber] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [saleBatNumber] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [saleBill] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [saleDate] [datetime] NULL ,
 [saleUserID] [int] NULL ,
 [saleMemo] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
 [salePrice] [decimal](18, 2) NULL ,
 [saleAmount] [decimal](18, 2) NULL ,
 [salePriceCaps] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [saleUpdUserID] [int] NULL ,
 [saleUpdDate] [datetime] NULL ,
 [saleUserName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [saleBarCode] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [saleSellerID] [int] NULL ,
 [saleDelFlag] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Staff] (
 [ID] [int] IDENTITY (7004, 1) NOT NULL ,
 [stafName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [stafPassWord] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [stafRoleID] [int] NULL ,
 [stafPhone] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [stafTel] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [stafAddr] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [stafDepartID] [int] NULL ,
 [stafProfessionID] [int] NULL ,
 [stafOnWork] [varchar] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [stafLoginCount] [int] NULL ,
 [stafLoginTime] [datetime] NULL ,
 [stafRegUser] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [stafRegDate] [datetime] NULL ,
 [stafMemo] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Vehicle] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [vehcName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [vehcModel] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [vehcColor] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [vehcRegUserID] [int] NULL ,
 [vehcRegDate] [datetime] NULL ,
 [vehcPrice] [decimal](18, 2) NULL
) ON [PRIMARY]
GO