u_data.pas

《delphi数据库系统开发实例导航》源代码

unit u_data;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Db, DBTables;

type
  TDM_main = class(TDataModule)
    Database: TDatabase;
    Q_login: TQuery;
    Q_room_valid: TQuery;
    DS_room_valid: TDataSource;
    Q_room_level: TQuery;
    Q_custom: TQuery;
    DS_custom: TDataSource;
    Q_id_type: TQuery;
    Q_add_custom: TQuery;
    Q_count_sheet: TQuery;
    Q_new_sheet: TQuery;
    T_add_items: TTable;
    DS_add_items: TDataSource;
    Q_custom2: TQuery;
    DS_custom2: TDataSource;
    Q_get_sheet: TQuery;
    DS_get_sheet: TDataSource;
    T_add_items2: TTable;
    DS_add_items2: TDataSource;
    Q_apply_sheet: TQuery;
    Q_worksheet: TQuery;
    DS_worksheet: TDataSource;
    T_items_list: TTable;
    DS_items_list: TDataSource;
    T_custom_info: TTable;
    DS_custom_info: TDataSource;
    Q_custom_list: TQuery;
    DS_custom_list: TDataSource;
    T_id_type: TTable;
    DS_id_type: TDataSource;
    T_items_info: TTable;
    DS_items_info: TDataSource;
    T_cost_list3: TTable;
    Q_worksheet3: TQuery;
    DS_worksheet3: TDataSource;
    DS_cost_list3: TDataSource;
    Q_worksheet2: TQuery;
    DS_worksheet2: TDataSource;
    T_room_list2: TTable;
    T_cost_list2: TTable;
    DS_room_list2: TDataSource;
    DS_cost_list2: TDataSource;
    Q_room_price: TQuery;
    Q_end_bill: TQuery;
  private
    { Private declarations }
  public
    function Login(user, passwd:String):String;
    procedure Logout;
    procedure GetRoomLevel(RoomLevel:TStrings);
    procedure SetRoomValid(StartTime, EndTime:TDateTime; RoomLevel:String);
    procedure GetIDType(IDType:TStrings);
    procedure AddCustom(ID, IDType, CustomName, Sex,
      ComeFrom, Addr, remark, IDCopyFileName:String);
    function GetCustomInfo(ID:String):Boolean;
    function NewWorkSheet:String;
    procedure SetAddItemsFilter(ID:String);
    procedure AddItems(WorkSheet, Custom:String; StartTime, EndTime:TDateTime);
    procedure DeleteItems;
    function GetCustomInfo2(ID:String):Boolean;
    procedure GetBookSheet(ID:String);
    procedure SetAddItemsFilter2(ID:String);
    procedure AddItems2(WorkSheet, Custom:String; StartTime, EndTime:TDateTime);
    procedure DeleteItems2;
    function ApplyBookSheet:String;
    procedure GetWorkSheet(Room:String);
    procedure OpenSearchInfo;
    procedure SetCustID(ID:String);
    procedure SetCustName(CustName:String);
    procedure SetCustFrom(CustFrom:String);
    procedure SetCustRoom(Room:String);
    procedure SetBill(Room:String);
    procedure SetCostList3(Room:String);
    procedure CalBill;
  end;

var
  DM_main: TDM_main;

implementation

uses Crypt, u_main;
{$R *.DFM}

function TDM_main.Login(user, passwd:String):String;
var
  Flag:Boolean;
begin
  with Database do
  begin
    Connected:=False;
    Params.Values['USER NAME']:='PMISPAS';
    Params.Values['PASSWORD']:='password';
    Connected:=True;
  end;
  passwd:=Copy(passwd+passwd, 1, 10);
  passwd:=Encrypt(passwd, 111);
  with Q_login do
  begin
    Close;
    Params.ParamValues['ID']:=user;
    Params.ParamValues['PASSWD']:=passwd;
    Open;
    Flag:=( not IsEmpty);
    if Flag then
      Login:=FieldValues['NAME']
    else
    begin
      Login:='';
      Application.MessageBox('请重新输入！', '登录失败', MB_OK);
    end;
    Close;
  end;
  with Database do
  begin
    Connected:=False;
    if Flag then
    begin
      Params.Values['USER NAME']:='PMISUSR';
      Params.Values['PASSWORD']:='password';
      Connected:=True;
    end;
  end;
end;

procedure TDM_main.Logout;
begin
  Database.Connected:=False;
end;

procedure TDM_main.GetRoomLevel(RoomLevel:TStrings);
begin
  RoomLevel.Clear;
  with Q_room_level do
  begin
    Open;
    First;
    while not Eof do
    begin
      RoomLevel.Add(FieldValues['DESCRIPT']);
      Next;
    end;
    Close;
  end;
end;

procedure TDM_main.GetIDType(IDType:TStrings);
begin
  IDType.Clear;
  with Q_id_type do
  begin
    Open;
    First;
    while not Eof do
    begin
      IDType.Add(FieldValues['DESCRIPT']);
      Next;
    end;
    Close;
  end;
end;

procedure TDM_main.SetRoomValid(StartTime, EndTime:TDateTime; RoomLevel:String);
begin
  with Q_room_valid do
  begin
    Close;
    Params.ParamValues['LEVEL']:=RoomLevel;
    Params.ParamValues['START_TIME']:=StartTime;
    Params.ParamValues['END_TIME']:=EndTime;
    Open;
  end;
end;

procedure TDM_main.AddCustom(ID, IDType, CustomName, Sex,
  ComeFrom, Addr, remark, IDCopyFileName:String);
var
  MemoryStream:TMemoryStream;
begin
  with Q_add_custom do
  begin
    Params.ParamValues['ID']:=ID;
    Params.ParamValues['ID_TYPE']:=IDType;
    Params.ParamValues['NAME']:=CustomName;
    Params.ParamValues['SEX']:=Sex;
    Params.ParamValues['COME_FROM']:=ComeFrom;
    Params.ParamValues['ADDR']:=Addr;
    Params.ParamValues['REMARK']:=Remark;
    MemoryStream:=TMemoryStream.Create;
    MemoryStream.LoadFromFile(IDCopyFileName);
    ParamByName('ID_COPY').SetBlobData(MemoryStream.Memory,
      MemoryStream.Size);
    ExecSQL;
    MemoryStream.Free;
  end;
end;

function TDM_main.GetCustomInfo(ID:String):Boolean;
begin
  with Q_custom do
  begin
    Close;
    Params.ParamValues['ID']:=ID;
    Open;
    GetCustomInfo:=(not IsEmpty);
  end;
end;

function TDM_main.NewWorkSheet:String;
var
  id:String;
  count:Integer;
begin
  with Q_count_sheet do
  begin
    Open;
    count:=FieldValues['COUNT'];
    Close;
  end;
  id:='000'+IntToStr(count);
  id:=Copy(id, length(id)-3, 4);
  id:=FormatDateTime('yymmdd',now)+id;
  NewWorkSheet:=id;
  with Q_new_sheet do
  begin
    Params.ParamValues['ID']:=id;
    Params.ParamValues['PERSON']:=F_main.i_user.Text;
    ExecSQL;
  end;
end;

procedure TDM_main.SetAddItemsFilter(ID:String);
begin
  with T_add_items do
  begin
    Filter:='WORKSHEET='''+ID+'''';
    Filtered:=True;
    Open;
  end;
end;

procedure TDM_main.AddItems(WorkSheet, Custom:String; StartTime, EndTime:TDateTime);
var
  Room:String;
begin
  Room:=Q_room_valid.FieldValues['ID'];
  T_add_items.AppendRecord([0,WorkSheet,Custom, Room, '0',StartTime,EndTime]);
  T_add_items.Refresh;
end;

procedure TDM_main.DeleteItems;
begin
  T_add_items.Delete;
  T_add_items.Refresh;
end;

function TDM_main.GetCustomInfo2(ID:String):Boolean;
begin
  with Q_custom2 do
  begin
    Close;
    Params.ParamValues['ID']:=ID;
    Open;
    GetCustomInfo2:=(not IsEmpty);
  end;
end;

procedure TDM_main.GetBookSheet(ID:String);
begin
  with Q_get_sheet do
  begin
    Close;
    Params.ParamValues['ID']:=ID;
    Open;
  end;
end;

procedure TDM_main.SetAddItemsFilter2(ID:String);
begin
  with T_add_items2 do
  begin
    Filter:='WORKSHEET='''+ID+'''';
    Filtered:=True;
    Open;
  end;
end;

procedure TDM_main.AddItems2(WorkSheet, Custom:String; StartTime, EndTime:TDateTime);
var
  Room:String;
begin
  Room:=Q_room_valid.FieldValues['ID'];
  T_add_items2.AppendRecord([0,WorkSheet,Custom, Room, '1',StartTime,EndTime]);
  T_add_items2.Refresh;
end;

procedure TDM_main.DeleteItems2;
begin
  T_add_items2.Delete;
  T_add_items2.Refresh;
end;

function TDM_main.ApplyBookSheet:String;
var
  sheet_id:String;
begin
  sheet_id:=Q_get_sheet.FieldValues['ID'];
  with Q_apply_sheet do
  begin
    Params.ParamValues['ID']:=sheet_id;
    ExecSQL;
  end;
  ApplyBookSheet:=sheet_id;
end;

procedure TDM_main.GetWorkSheet(Room:String);
begin
  with Q_worksheet do
  begin
    Close;
    Params.ParamValues['ROOM']:=Room;
    Open;
  end;
  T_items_list.Open;
end;

procedure TDM_main.OpenSearchInfo;
begin
  T_custom_info.Open;
  T_id_type.Open;
  T_items_info.Open;
end;

procedure TDM_main.SetCustID(ID:String);
begin
  with Q_custom_list do
  begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT ID');
    SQL.Add('FROM CUSTOM');
    SQL.Add('WHERE ID LIKE :ID');
    Params.ParamValues['ID']:=ID;
    Open;
  end;
end;

procedure TDM_main.SetCustName(CustName:String);
begin
  with Q_custom_list do
  begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT ID');
    SQL.Add('FROM CUSTOM');
    SQL.Add('WHERE NAME LIKE :NAME');
    Params.ParamValues['NAME']:=CustName;
    Open;
  end;
end;

procedure TDM_main.SetCustFrom(CustFrom:String);
begin
  with Q_custom_list do
  begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT ID');
    SQL.Add('FROM CUSTOM');
    SQL.Add('WHERE COME_FROM LIKE :FROM');
    Params.ParamValues['FROM']:=CustFrom;
    Open;
  end;
end;

procedure TDM_main.SetCustRoom(Room:String);
begin
  with Q_custom_list do
  begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT CUSTOM AS ID');
    SQL.Add('FROM SHEET_ITEMS');
    SQL.Add('WHERE ROOM=:ROOM');
    Params.ParamValues['ROOM']:=Room;
    Open;
  end;
end;

procedure TDM_main.SetCostList3(Room:String);
begin
  with Q_worksheet3 do
  begin
    Close;
    Params.ParamValues['ROOM']:=Room;
    Open;
  end;
  T_cost_list3.Open;
end;

procedure TDM_main.SetBill(Room:String);
begin
  with Q_worksheet2 do
  begin
    Close;
    Params.ParamValues['ROOM']:=Room;
    Open;
  end;
  T_room_list2.Open;
  T_cost_list2.Open;
end;

procedure TDM_main.CalBill;
var
  RoomCost:Integer;
  RoomPrice:Integer;
  RoomDay:Integer;
  TotalCost:Integer;
begin
  RoomCost:=0;
  with T_room_list2 do
  begin
    First;
    while not Eof do
    begin
      Q_room_price.Close;
      Q_room_price.Params.ParamValues['ROOM']:=FieldValues['ROOM'];
      Q_room_price.Open;
      RoomPrice:=Q_room_price['PRICE'];
      RoomDay:=Round(FieldValues['END_TIME']-FieldValues['START_TIME']);
      RoomCost:=RoomCost+RoomPrice*RoomDay;
      Next;
    end;
  end;
  TotalCost:=0;
  with T_cost_list2 do
  begin
    Append;
    FieldValues['COST']:=RoomCost;
    FieldValues['DESCRIPT']:='客房使用费用';
    Post;
    First;
    while not Eof do
    begin
      TotalCost:=TotalCost+FieldValues['COST'];
      Next;
    end;
  end;
  with Q_end_bill do
  begin
    Close;
    Prepare;
    Params.ParamValues['COST']:=TotalCost;
    ExecSQL;
  end;
end;

end.