数据库脚本.sql

广州收费标准自动化测试程序

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

CREATE TABLE [dbo].[Account] (
	[CardID] [char] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[UserNo] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
	[UserName] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[OperaterDate] [datetime] NOT NULL ,
	[OperatorType] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[O_FirDate] [datetime] NULL ,
	[O_EndDate] [datetime] NULL ,
	[N_FirDate] [datetime] NULL ,
	[N_EndDate] [datetime] NULL ,
	[O_TMoney] [money] NULL ,
	[O_SMoney] [money] NULL ,
	[N_TMoney] [money] NULL ,
	[N_SMoney] [money] NULL ,
	[ChargeMoney] [money] NULL ,
	[OperatorName] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AlarmTimeGroup] (
	[GroupNo] [int] NOT NULL ,
	[GroupName] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[StartTime] [char] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[EndTime] [char] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[Monday] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[Tuesday] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[Wednesday] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[Thursday] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[Friday] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[Saturday] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[Sunday] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[Holiday1] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[Holiday2] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[Holiday3] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[Holiday4] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[Holiday5] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[Holiday6] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[Holiday7] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[BaseInfo] (
	[PicServer] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[PicDB] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[PicUid] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[PicPwd] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[AbnorPwd] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CardInfo] (
	[CardID] [char] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[CardNo] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,
	[ICorID] [int] NULL ,
	[UserFlag] [bit] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CompanyName] (
	[CompanyName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[Address] [nvarchar] (256) COLLATE Chinese_PRC_CI_AS NULL ,
	[Fax] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Tel] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Web] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[Email] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[OneCardQ] [bit] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Dept] (
	[DeptNo] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[DeptName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[DeptFlag] [char] (31) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DoorInfo] (
	[MachNo] [int] NOT NULL ,
	[DoorNo] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[DoorPlace] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[DoorTier] [int] NULL ,
	[DoorKey] [int] NOT NULL ,
	[controlType] [int] NULL ,
	[DeptName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[DoorTimeGroup] [int] NULL ,
	[OpenDelay] [int] NULL ,
	[Computer] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DoorRight] (
	[CardID] [char] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[RightValue] [varchar] (8000) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]