checkserverunit.~pas

来自「很好地delphi书籍源码」· ~PAS 代码 · 共 525 行 · 第 1/2 页

    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.