checkserverunit.pas
来自「很好地delphi书籍源码」· PAS 代码 · 共 525 行 · 第 1/2 页
PAS
525 行
SQL.Add('[UserName] [varchar](10) NOT NULL PRIMARY KEY,'); //客户标识号，主健
SQL.Add('[Password] [varchar](10) NOT NULL ,'); //客户姓名
SQL.Add('[Kind] [varchar](10) NOT NULL )'); //性别
ExecSQL;
end;
//------------创建客户信息表 tbGInfo------------------
with Query1 do
begin
SQL.Clear;
SQL.Add('CREATE TABLE tbGInfo (');//创建数据表。
SQL.Add('[GuestID] [int] NOT NULL PRIMARY KEY,'); //客户标识号，主健
SQL.Add('[GuestName] [char](8) NOT NULL ,'); //客户姓名
SQL.Add('[Sex] [char](4) NULL ,'); //性别
SQL.Add('[Age] [int] NULL ,');// 年龄
SQL.Add('[BornPlace] [char] (20) NULL,'); //出生地
SQL.Add('[CardName] [char] (8) NOT NULL,'); //证件类型
SQL.Add('[CardID] [char] (20) NOT NULL,'); //证件号码
SQL.Add('[Company] [char] (20) NULL,'); //公司
SQL.Add('[Duty] [char] (10) NULL,'); //职务
SQL.Add('[PlanDays] [int] NULL,'); //拟住天数
SQL.Add('[Foregift] [money] NULL,'); //押金
SQL.Add('[EnterDateTime] [smalldatetime] NOT NULL,'); //入住时间
SQL.Add('[LeaveDateTime] [smalldatetime] NOT NULL)'); //结算时间
SQL.Add('INSERT INTO tbGInfo VALUES');
SQL.Add('(-1,"现金","",0,"","","","","",0,0,"","")');
SQL.Add('INSERT INTO tbGInfo VALUES');
SQL.Add('(-2,"信用卡","",0,"","","","","",0,0,"","")');
SQL.Add('INSERT INTO tbGInfo VALUES');
SQL.Add('(-3,"支票","",0,"","","","","",0,0,"","")');
SQL.Add('INSERT INTO tbGInfo VALUES');
SQL.Add('(-4,"赊欠","",0,"","","","","",0,0,"","")');
ExecSQL;
end;
//------------创建客户客房消费表 tbGRoomFee------------
with Query1 do
begin
SQL.Clear;
SQL.Add('CREATE TABLE tbGRoomFee (');//创建数据表。
SQL.Add('[GuestID] [int] NOT NULL ,'); //客户标识号
SQL.Add('[GuestName] [char](12) NOT NULL ,'); //客户姓名
SQL.Add('[CardID] [char](20) NOT NULL,'); //证件号码
SQL.Add('[RoomID] [char](8) NOT NULL ,'); //房间号
SQL.Add('[BedID] [char](8) NOT NULL ,'); //床位号
SQL.Add('[BedPrice] [money] NOT NULL ,');//床位价格
SQL.Add('[BedDays] [decimal](4,1) NOT NULL ,');//床位使用天数
SQL.Add('[BedStartDateTime] [smalldatetime] NOT NULL,'); //床位启用入住时间
SQL.Add('[BedEndDateTime] [smalldatetime] NOT NULL)'); //床位停用时间
ExecSQL;
end;
//------------创建客户宾馆消费表 tbGHotelFee------------
with Query1 do
begin
SQL.Clear;
SQL.Add('CREATE TABLE tbGHotelFee (');//创建数据表。
SQL.Add('[GuestID] [int] NOT NULL ,'); //客户标识号
SQL.Add('[GuestName] [char](8) NOT NULL ,'); //客户姓名
SQL.Add('[CardID] [char] (20) NOT NULL,'); //证件号码
SQL.Add('[FeeOffice] [money] NULL,'); //商务费
SQL.Add('[FeeDinner] [money] NULL,'); // 餐费
SQL.Add('[FeePlay] [money] NULL,'); // 娱乐费
SQL.Add('[FeeShop] [money] NULL,'); //购物费
SQL.Add('[FeeOther] [money] NULL,'); //其它费用
SQL.Add('[PayDateTime] [smalldatetime] NOT NULL)'); //消费时间
SQL.Add('INSERT tbGHotelFee VALUES');
SQL.Add('(-1,"现金","",0,0,0,0,0,"")');
SQL.Add('INSERT tbGHotelFee VALUES');
SQL.Add('(-2,"信用卡","",0,0,0,0,0,"")');
SQL.Add('INSERT tbGHotelFee VALUES');
SQL.Add('(-3,"支票","",0,0,0,0,0,"")');
SQL.Add('INSERT tbGHotelFee VALUES');
SQL.Add('(-4,"赊欠","",0,0,0,0,0,"")');
ExecSQL;
end;
//------------创建宾馆客房客户收银表 tbHGuestInCome------------
with Query1 do
begin
SQL.Clear;
SQL.Add('CREATE TABLE tbHGuestInCome (');//创建数据表。
SQL.Add('[GuestID] [int] PRIMARY KEY NOT NULL ,'); //客户标识号，主健
SQL.Add('[GuestName] [char](8) NOT NULL ,'); //客户姓名
SQL.Add('[FeeBed] [money] NOT NULL ,');//床位费
SQL.Add('[FeeOffice] [money] NULL,'); //商务费
SQL.Add('[FeeDinner] [money] NULL,'); // 餐费
SQL.Add('[FeePlay] [money] NULL,'); // 娱乐费
SQL.Add('[FeeShop] [money] NULL,'); //购物费
SQL.Add('[FeeOther] [money] NULL,'); //其它费用
SQL.Add('[PayMode] [char](8) NULL,'); //支付方式
SQL.Add('[LeaveDateTime] [smalldatetime] NULL)'); //结算时间
SQL.Add('INSERT INTO tbHGuestInCome VALUES');
SQL.Add('(-1,"现金",0,0,0,0,0,0,"现金","")');
SQL.Add('INSERT INTO tbHGuestInCome VALUES');
SQL.Add('(-2,"信用卡",0,0,0,0,0,0,"信用卡","")');
SQL.Add('INSERT INTO tbHGuestInCome VALUES');
SQL.Add('(-3,"支票",0,0,0,0,0,0,"支票","")');
SQL.Add('INSERT INTO tbHGuestInCome VALUES');
SQL.Add('(-4,"赊欠",0,0,0,0,0,0,"赊欠","")');
ExecSQL;
end;
//------------创建房态信息表 tbHRoomState------------
with Query1 do
begin
SQL.Clear;
SQL.Add('CREATE TABLE tbHRoomState (');//创建数据表
SQL.Add('[RoomID] [char](10) NOT NULL ,'); //房间号
SQL.Add('[BedID] [char](8) NOT NULL ,');//床位号
SQL.Add('[RoomKind] [char](20) NOT NULL ,'); //房间类别
SQL.Add('[BedPrice] [int] Not NULL ,'); //床位价格
SQL.Add('[RoomState] [varchar](10) NOT NULL ,'); //房间状态
SQL.Add('[BedState] [varchar](10) NOT NULL,');//床位状态
SQL.Add('[RoomInfo] [varchar](30) NULL )');//客房条件描述
ExecSQL;
end;
//------------创建宾馆历史客户记录表 tbOldGInfo------------
with Query1 do
begin
SQL.Clear;
SQL.Add('CREATE TABLE tbOldGInfo (');//创建数据表。
SQL.Add('[GuestID] [int] NOT NULL ,'); //客户标识号，主健
SQL.Add('[GuestName] [char](8) NOT NULL ,'); //客户姓名
SQL.Add('[Sex] [char](4) NULL ,'); //性别
SQL.Add('[Age] [int] NULL ,');// 年龄
SQL.Add('[BornPlace] [char] (20) NULL,'); //出生地
SQL.Add('[CardName] [char] (8) NOT NULL,'); //证件类型
SQL.Add('[CardID] [char] (20) NOT NULL,'); //证件号码
SQL.Add('[Company] [char] (20) NULL,'); //公司
SQL.Add('[Duty] [char] (10) NULL,'); //职务
SQL.Add('[PlanDays] [int] NULL,'); //拟住天数
SQL.Add('[Foregift] [money] NULL,'); //押金
SQL.Add('[EnterDateTime] [smalldatetime] NOT NULL,'); //入住时间
SQL.Add('[LeaveDateTime] [smalldatetime] NOT NULL)'); //结算时间
ExecSQL;
end;
//------------创建宾馆客户订票记录表 tbBookTicket------------
with Query1 do
begin
SQL.Clear;
SQL.Add('CREATE TABLE tbBookTicket (');//创建数据表。
SQL.Add('[GuestID] [int] NOT NULL ,'); //客户标识号，主健
SQL.Add('[GuestName] [char](8) NOT NULL ,'); //客户姓名
SQL.Add('[TKind] [char](6) NULL ,'); //票的种类
SQL.Add('[TDate] [smalldatetime] NULL,'); //票的日期
SQL.Add('[BDate] [smalldatetime] NULL,'); //票的日期
SQL.Add('[Foregift] [money] NULL,'); //押金
SQL.Add('[DesTown] [char](20) NULL,'); //目的地
SQL.Add('[Note] [char](20) NULL)'); //备注
ExecSQL;
end;
//------------创建宾馆预订房间记录表 tbBookTicket------------
with Query1 do
begin
SQL.Clear;
SQL.Add('CREATE TABLE tbBookRoom (');//创建数据表。
SQL.Add('[GuestName] [char](8) NOT NULL ,'); //订票人姓名
SQL.Add('[CardName] [char] (8) NULL,'); //证件类型
SQL.Add('[CardID] [char] (20) NULL,'); //证件号码
SQL.Add('[RoomKind] [char](20) NOT NULL ,'); //房间类别
SQL.Add('[RoomID] [char](10) NOT NULL ,'); //房间号
SQL.Add('[BedID] [char](8) NOT NULL ,');//床位号
SQL.Add('[PlanDays] [char](8) NULL,'); //拟住天数
SQL.Add('[EnterDateTime] [smalldatetime] NULL)'); //入住时间
ExecSQL;
end;
//------------创建宾馆客房类别及价格表 tbHRoomPrice------------
with Query1 do
begin
SQL.Clear;
SQL.Add('CREATE TABLE tbHRoomPrice (');//创建数据表。
SQL.Add('[HRName] [char](20) PRIMARY KEY NOT NULL ,'); //客房类别
SQL.Add('[HRPrice] [int] NULL ) '); // 价格
SQL.Add('Insert tbHRoomPrice VALUES("普通三人房/床",40)');
SQL.Add('Insert tbHRoomPrice VALUES("标准双人房/床",80)');
SQL.Add('Insert tbHRoomPrice VALUES("标准双人房",160)');
SQL.Add('Insert tbHRoomPrice VALUES("情侣双人房",200)');
SQL.Add('Insert tbHRoomPrice VALUES("豪华套房",400)');
SQL.Add('Insert tbHRoomPrice VALUES("大会议厅",400)');
SQL.Add('Insert tbHRoomPrice VALUES("小会议厅",300)');
ExecSQL;
end;
end;
procedure TForm1.CreateView;
begin
//------------显示客户信息表tbGInfo视图------------------
with Query1 do
begin
SQL.Clear;
SQL.Add('CREATE VIEW tbGInfo_VIEW_1 AS');
SQL.Add('SELECT GuestID as 客户编号,GuestName as 客户姓名, Age as 年龄,');
SQL.Add('Sex as 性别, BornPlace as 出生地, CardName as 证件类型,');
SQL.Add('CardID as 证件号码, Company as 工作单位, Duty as 职务,');
SQL.Add('PlanDays as 预住天数, Foregift as 预付押金,');
SQL.Add('EnterDateTime as 入住时间,LeaveDateTime as 结算时间 From tbGInfo');
SQL.Add('WHERE GuestID>=0');
ExecSQL;
end;
//------------创建客户客房消费表 tbGRoomFee 所有记录视图------------------
with Query1 do
begin
SQL.Clear;
SQL.Add('CREATE VIEW tbGRoomFee_VIEW_1 AS');
SQL.Add('SELECT GuestID as 客户编号,GuestName as 客户姓名, ');
SQL.Add('CardID as 证件号码,RoomID as 房间号, BedID as 床位号,');
SQL.Add('BedStartDateTime as 床位启用时间,BedEndDateTime as 床位停用时间');
SQL.Add(' From tbGRoomFee');
ExecSQL;
end;
//------------创建宾馆客房客户收银表 tbHGuestInCome视图------------
with Query1 do
begin
SQL.Clear;
SQL.Add('CREATE VIEW tbHGuestInCome_VIEW_1 AS');
SQL.Add('SELECT 客户编号=GuestID,客户姓名=GuestName,');
SQL.Add('床位费=FeeBed,商务费=FeeOffice,餐费=FeeDinner ,');
SQL.Add('娱乐费=FeePlay, 购物费=FeeShop,其它费用=FeeOther,支付方式=PayMode,');
SQL.Add('结算时间=LeaveDateTime From tbHGuestInCome where GuestID>=0');
ExecSQL;
end;
//------------创建宾馆客房客户宾馆消费表tbGHotelFee视图------------
with Query1 do
begin
SQL.Clear;
SQL.Add('CREATE VIEW tbGHotelFee_VIEW_1 AS');
SQL.Add('select GuestID as 客户编号, GuestName as 客户姓名,CardID as 证件号码,');
SQL.Add('FeeOffice as 商务费, FeeDinner as 餐饮费,FeePlay as 娱乐费,');
SQL.Add('FeeShop as 购物费,FeeOther as 其它费用 from tbGHotelFee where GuestID>=0');
ExecSQL;
end;
//----------停用床位的存贮过程-----------
with Query1 do
begin
SQL.Clear;
SQL.Add('CREATE PROCEDURE Update_tbGRoomFee');
SQL.Add('(@ID int, @Room char (8), @Bed char (8), @Days decimal (4,1),@Prcie Money, @EndDT smalldatetime)');
SQL.Add('AS UPDATE tbGRoomFee');
SQL.Add('SET BedDays= @Days,BedPrice=@Prcie,BedEndDateTime = @EndDT');
SQL.Add('WHERE GuestID=@ID and RoomId=@Room and BedID=@Bed');
ExecSQL;
end;
//----------添加床位的存贮过程-----------
with Query1 do
begin
SQL.Clear;
SQL.Add('CREATE PROCEDURE Insert_tbGRoomFee');
SQL.Add('(@ID int, @Name char (12), @CardID char (20),');
SQL.Add('@Room char(8), @Bed char(8),');
SQL.Add('@Price money , @Days decimal (4,1),');
SQL.Add('@Start smalldatetime, @End smalldatetime)');
SQL.Add('AS INSERT INTO tbGRoomFee');
SQL.Add('( GuestID,GuestName,CardID,RoomID,');
SQL.Add('BedID,BedPrice,BedDays,BedStartDateTime,BedEndDateTime) Values');
SQL.Add('(@ID,@Name, @CardID, @Room,@Bed,@Price,@Days,@Start, @End)');
ExecSQL;
end;
end;
end.
⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?