init.sql

1. 发卡服务端：Server 项目文件：autojet.prj 2. 计费客户端：Client 项目文件：internet.prj Explorer.prj 先启动Exp

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

CREATE TABLE [dbo].[CARD] (
	[IcNo] [int] NOT NULL ,
	[IcType] [varchar] (10) NOT NULL ,
	[Gname] [varchar] (10) NULL ,
	[IdentityNo] [varchar] (18) NULL ,
	[Tele] [varchar] (18) NULL ,
	[Addr] [varchar] (40) NULL ,
	[Deposit] [smallmoney] NOT NULL ,
	[Payment] [smallmoney] NOT NULL ,
	[Gift] [smallmoney] NOT NULL ,
	[JSSS] [smallmoney] NOT NULL ,
	[ICdate] [smalldatetime] NOT NULL ,
	[Fxrq] [smalldatetime] NOT NULL ,
	[Gxrq] [smalldatetime] NULL ,
	[Zxrq] [smalldatetime] NULL ,
	[LDateTime] [smalldatetime] NULL ,
	[SYSJ] [smallmoney] NOT NULL ,
	[SYJE] [smallmoney] NOT NULL ,
	[Operator1] [varchar] (10) NULL ,
	[Operator2] [varchar] (10) NULL ,
	[Operator3] [varchar] (10) NULL 
)
GO

CREATE TABLE [dbo].[DelCard] (
	[CardNo] [int] NOT NULL ,
	[Deltime] [smalldatetime] NOT NULL 
)
GO

CREATE TABLE [dbo].[Grate] (
	[Gid] [char] (2) NOT NULL ,
	[StartTime] [char] (5) NOT NULL ,
	[PTK] [smallmoney] NULL ,
	[JK] [smallmoney] NULL ,
	[YK] [smallmoney] NULL ,
	[PTK1] [smallmoney] NULL ,
	[JK1] [smallmoney] NULL ,
	[YK1] [smallmoney] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[GRP] (
	[Edate] [smalldatetime] NOT NULL ,
	[YJ] [money] NOT NULL ,
	[CZ] [money] NOT NULL ,
	[ZS] [money] NOT NULL ,
	[YP] [money] NOT NULL ,
	[XY] [money] NOT NULL ,
	[QT] [money] NOT NULL ,
	[HT] [money] NOT NULL ,
	[YYJE] [money] NOT NULL 
)
GO

CREATE TABLE [dbo].[ItemID] (
	[ItemName] [varchar] (4) NOT NULL ,
	[ItemDesc] [varchar] (10) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[items] (
	[id] [varchar] (4) NOT NULL ,
	[iType] [varchar] (8) NOT NULL ,
	[iName] [varchar] (10) NOT NULL ,
	[price] [smallmoney] NOT NULL 
)
GO

CREATE TABLE [dbo].[MaxNum] (
	[CardNo] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Operator] (
	[Id] [varchar] (6) NOT NULL ,
	[GName] [varchar] (12) NOT NULL ,
	[Sex] [char] (2) NULL ,
	[Tel] [varchar] (20) NULL ,
	[PassWd] [varchar] (6) NULL ,
	[R_system] [char] (1) NULL ,
	[R_card] [char] (1) NULL ,
	[R_Report] [char] (1) NULL 
)
GO

CREATE TABLE [dbo].[PBill] (
	[Edate] [smalldatetime] NOT NULL ,
	[ICno] [int] NOT NULL ,
	[ItemName] [varchar] (4) NOT NULL ,
	[JF] [money] NOT NULL ,
	[DF] [money] NOT NULL ,
	[Flag] [char] (1) NOT NULL ,
	[Operator] [varchar] (10) NOT NULL ,
	[Remark] [varchar] (8) NULL ,
	[JBdate] [smalldatetime] NULL ,
	[JBoptor] [varchar] (10) NULL ,
	[JKdate] [smalldatetime] NULL ,
	[JKoptor] [varchar] (10) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Pdata] (
	[company] [varchar] (20) NOT NULL ,
	[SVname] [varchar] (10) NULL ,
	[SXRQ] [smallint] NOT NULL ,
	[YJ] [smallint] NOT NULL ,
	[Montime] [smallint] NOT NULL ,
	[SdTime] [smallint] NOT NULL ,
	[Imename] [varchar] (20) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RateName] (
	[Gid] [char] (2) NOT NULL ,
	[RName] [varchar] (8) NOT NULL 
)
GO

CREATE TABLE [dbo].[Things] (
	[TName] [varchar] (30) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Tigue] (
	[TermName] [varchar] (8) NOT NULL ,
	[ICNo] [int] NOT NULL ,
	[GState] [char] (4) NULL ,
	[login] [char] (11) NULL ,
	[logout] [char] (11) NULL 
)
GO

CREATE TABLE [dbo].[Users] (
	[TermName] [varchar] (8) NOT NULL ,
	[GId] [char] (2) NOT NULL ,
	[ComName] [char] (4) NOT NULL 
)
GO

CREATE TABLE [dbo].[XTCS] (
	[ID] [int] NOT NULL ,
	[FDATE] [char] (20) NULL ,
	[LDATE] [char] (20) NULL ,
	[SYSID] [char] (15) NULL ,
	[RECDEL] [int] NULL ,
	[NETBARNAME] [varchar] (20) NULL ,
	[ICPASS] [varchar] (6) NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CARD] WITH NOCHECK ADD 
	CONSTRAINT [PK_CARD] PRIMARY KEY  CLUSTERED 
	(
		[IcNo]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[DelCard] WITH NOCHECK ADD 
	CONSTRAINT [PK_DelCard] PRIMARY KEY  CLUSTERED 
	(
		[CardNo]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[GRP] WITH NOCHECK ADD 
	CONSTRAINT [PK_GRP] PRIMARY KEY  CLUSTERED 
	(
		[Edate]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[items] WITH NOCHECK ADD 
	CONSTRAINT [PK_items] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Operator] WITH NOCHECK ADD 
	CONSTRAINT [PK_Operator] PRIMARY KEY  CLUSTERED 
	(
		[Id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[RateName] WITH NOCHECK ADD 
	CONSTRAINT [PK_RateName] PRIMARY KEY  CLUSTERED 
	(
		[Gid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Tigue] WITH NOCHECK ADD 
	CONSTRAINT [PK_Tigue] PRIMARY KEY  CLUSTERED 
	(
		[TermName]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Users] WITH NOCHECK ADD 
	CONSTRAINT [PK_Users] PRIMARY KEY  CLUSTERED 
	(
		[TermName]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[PBill] WITH NOCHECK ADD 
	CONSTRAINT [DF_PBill_ICno] DEFAULT (0) FOR [ICno],
	CONSTRAINT [DF_PBill_JF] DEFAULT (0) FOR [JF],
	CONSTRAINT [DF_PBill_DF] DEFAULT (0) FOR [DF],
	CONSTRAINT [DF_PBill_Flag] DEFAULT (0) FOR [Flag]
GO

ALTER TABLE [dbo].[Pdata] WITH NOCHECK ADD 
	CONSTRAINT [DF_Pdata_company] DEFAULT ('AAA') FOR [company],
	CONSTRAINT [DF_Pdata_SXRQ] DEFAULT (365) FOR [SXRQ],
	CONSTRAINT [DF_Pdata_YJ] DEFAULT (10) FOR [YJ],
	CONSTRAINT [DF_Pdata_Montime] DEFAULT (1) FOR [Montime],
	CONSTRAINT [DF_Pdata_SdTime] DEFAULT (1) FOR [SdTime],
	CONSTRAINT [DF_Pdata_Imename] DEFAULT ('全拼输入法') FOR [Imename]
GO

ALTER TABLE [dbo].[Users] WITH NOCHECK ADD 
	CONSTRAINT [DF_Users_GId] DEFAULT ('01') FOR [GId],
	CONSTRAINT [DF_Users_ComName] DEFAULT ('COM1') FOR [ComName]
GO

SET QUOTED_IDENTIFIER  ON    SET ANSI_NULLS  OFF 
GO

CREATE PROCEDURE GETMAXNUM  @MaxVal int output AS

DECLARE @ISMAX BIT, @MAXNUM INT

IF NOT EXISTS(SELECT CARDNO FROM MAXNUM)  INSERT INTO MAXNUM (CARDNO) VALUES (0)

SELECT @ISMAX=0, @MAXNUM=99999

SELECT @ISMAX=1 FROM MAXNUM WHERE CARDNO>=99999

IF @ISMAX=0 BEGIN
  UPDATE MAXNUM SET CARDNO=CARDNO+1
  SELECT @MAXVAL=CARDNO FROM MAXNUM
END ELSE
BEGIN
  SELECT @MAXVAL=MIN(CARDNO) FROM DELCARD WHERE CARDNO<=@MAXNUM AND DELTIME IN (SELECT MIN(DELTIME) FROM DELCARD WHERE CARDNO<=@MAXNUM)
  DELETE DELCARD WHERE CARDNO=@MAXVAL
END
GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

insert into operator (id, gname, sex, passwd, r_system, r_card, r_report)
   values ('SYS', '系统管理员', '男', '123', '1', '1', '1')
GO

insert into pdata (company, SVname, SXRQ, YJ, Montime, SdTime, ImeName)
         Values ('AAA网吧', '', 365, 10, 1, 1, '全拼输入法')
GO

insert into grate (Gid, StartTime, PTK, JK, YK, PTK1, JK1, YK1)
        values('01', '01:00', 3, 2, 2.5, 0.05, 0.0333, 0.0417)
insert into grate (Gid, StartTime, PTK, JK, YK, PTK1, JK1, YK1)
        values('01', '08:00', 4, 3, 3.5, 0.0667, 0.05, 0.0583)
insert into grate (Gid, StartTime, PTK, JK, YK, PTK1, JK1, YK1)
        values('01', '20:00', 5, 3, 4, 0.0833, 0.05, 0.0667)
GO

insert into grate (Gid, StartTime, PTK, JK, YK, PTK1, JK1, YK1)
        values('02', '00:00', 6, 5, 5.5, 0.1, 0.0833, 0.0917)
GO

insert into RateName (Gid, Rname)
   values ('01', '普通费率')
insert into RateName (Gid, Rname)
   values ('02', '特别费率')
GO

insert into itemID (itemName, itemDesc)
   values('YJ','押金')
insert into itemID (itemName, itemDesc)
   values('CZ','充值')
insert into itemID (itemName, itemDesc)
   values('YP','饮品')
insert into itemID (itemName, itemDesc)
   values('XY','香烟')
insert into itemID (itemName, itemDesc)
   values('QT','其它')
insert into itemID (itemName, itemDesc)
   values('HT','退回')
insert into itemID (itemName, itemDesc)
   values('ZS','赠送')
GO