c_hoteldata.pas

经典的酒店管理系统

{立方酒店管理系统 v1.0
 Copyright by CubicSoft
 program by ls.
 Date:20020301-20020601
 20020627 加入注释
}

unit C_HotelData;

interface

uses
  SysUtils, Classes, DB, DBTables, StdCtrls, C_Define, DBCtrls, Forms, DateUtils;

type
  THotelData = class(TDataModule)
    tblSysData: TTable;
    tblUserdata: TTable;
    qryUserData: TQuery;
    tblBMDM: TTable;
    tblCTDM: TTable;
    tblDQDM: TTable;
    tblFLDM: TTable;
    tblFXDM: TTable;
    tblGBDM: TTable;
    tblHCDM: TTable;
    tblKFDM: TTable;
    tblLCDM: TTable;
    tblMZDM: TTable;
    tblQZDM: TTable;
    tblXMDM: TTable;
    tblXYK: TTable;
    tblZJDM: TTable;
    tblKHDA: TTable;
    tblBqj: TTable;
    tblKfzt: TTable;
    tblKrxx: TTable;
    tblKryj: TTable;
    tblKrzd: TTable;
    tblEwf: TTable;
    BatchMove1: TBatchMove;
    tblLctj: TTable;
    tblLctjda: TTable;
    qrySysData: TQuery;
    tblCtdc: TTable;
    tblCtdcda: TTable;
    tblDcmx: TTable;
    tblDcmxda: TTable;
    tblYdxx: TTable;
    qryDhf: TQuery;
    tblXxdc: TTable;
    DatabaseUser: TDatabase;
    tblDhfOld: TTable;
    tblKrzdcx: TTable;
    tblYssj: TTable;
    procedure DataModuleCreate(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    function  CheckKfzt(const AKfbh: string) : string;
    function  GetBh(const AFieldName,APrevStr: string): string;
    function  FindBh(const ATableName,AFieldBh,AFieldMc,AMc: string): string;
    function  FindMc(const ATableName,AFieldBh,AFieldMc,ABh: string): string;
    function  FindUserBh(const ATableName,AFieldBh,AFieldMc,AMc: string): string;
    //function IsExists(const AValue,ATableName,AFieldName: string): Boolean;
    //function IsValidYd(AXfxm: TXFXM;ADate: TDateTime): Boolean;
    function  IsValidHcyd(const AHcbh,ASjdm: string;ADate: TDateTime): Boolean;
    function  IsValidCyyd(const ACtbh,ASjdm: string;ADate: TDateTime): Boolean;
    function  IsValidKfyd(const AKfbh,AYdbh: string;ADdrq,ALdrq: TDateTime): Boolean;
    procedure ListMc(AComboBox: TComboBox;const ATableName,AFieldMc: string);
    procedure ListDm(AComboBox: TComboBox;const ATableName,AFieldMc: string);
    procedure ListDbDm(ADBComboBox: TDBComboBox;const ATableName,AFieldMc: string);
    procedure UpdateBqj(const AKfbh: string;ABqj: Integer;ASjfj,AJjfj: Currency);
    procedure UpdateJjfj(const AKfbh: string;ABqj: Integer;AJjfj: Currency);
    function  SumJe(const ASqlStr: string): Currency;
    function  SumSysJe(const ASqlStr: string): Currency;
    function  GetKfxx(const AKfbh: string): TKFXX;
    function  GetKfxx1(const AKfbh: string): TKFXX;
    function  GetMaxZdhh(const AZdbh: string): Integer;
    procedure UpdateEwfDbf(const AJzbh,AKrxm: string;AEwf: Currency;AJzrq,AJzsj: TDateTime);
    procedure InitKfzt(const AKfbh: string);
    procedure InitKfztOK(const AKfbh: string);
    procedure ClearKfzt(const AKfbh: string);
    procedure UpdateKrxxJz(const AKrbh: string;ALdrq,ALdsj: TDateTime);
    procedure BakData(const ASqlStr,ATableName: string);
    procedure EmptyData(const ASqlStr: string);
    procedure SetKfzt(const AKfbh,AKfzt: string);
    function  GetKfbh(const AKrbh: string):string;
    function  GetZdbh(const AKrbh: string):string;
    procedure BakTable(S,D: TTable);
    function  GetTdxx(const AKrbh: string): TTdxx;
    function  IsTdtf(const AKrbh: string): Boolean;
    function  GetYjxx(const AYjbh: string): TYjxx;
    function  Ldcs(const AKrxm: string): Integer;
    function  IsHmd(const AKrxm,AZjhm: string): string;
    procedure YgkfAppend(const ACzyxm,AKfbh,AKrxm: string;ASjfj: Currency;ADdrq,ADdsj: TDateTime);
    function  GetDhf(const AKfbh: string;ADdrq,ADdsj: TDateTime): Currency;
    function  GetDhf1(const AKfbh: string;ADdrq,ADdsj: TDateTime): Currency;
    function  CheckYgqx(const AYgbh,ACzxm: string): Boolean;
    function  GetFirstRec(const ATableName,AFieldBh,AFieldMc: string): TRecInfo;
    procedure ListTdmc(AComboBox: TComboBox);
    function  GetXfxx(const AZdbh: string): TXfxx;
    function  SumCount(const ASqlStr: string): Integer;
    procedure ExecSql(const ASqlStr: string);
    procedure InsertYjk(const AShlx,ABz: string;ARmb,AXyk,AZp: Currency);
    procedure InsertKrzd(const AZdbh,AZdlb,AKrbh,AYjbh,AKfbh,AXmbh,AJzbz,AJsbz,ABmbh,ABz: string;AHh: Integer;AXfje,AYhje: Currency);
    procedure AddKrzdZdXfje(const AZdbh: string;AXfje: Currency);
    procedure BakDhf;
    procedure EmptyDhf;
    function GetDdts(const aKfbh: string): string;
    procedure GetYssj(var HH,NN,SS: Word);
    function FindValue(const aSqlStr: string): string;
    function FindCurrency(const aSqlStr: string): Currency;
    function GetMaxId(const aSqlStr: string):Integer;
  end;

var
  HotelData: THotelData;

implementation

{$R *.dfm}
function THotelData.GetMaxId(const aSqlStr: string): Integer;
begin
  with qrySysData do
  begin
    Close;
    SQL.Clear;
    SQL.Add(aSqlStr);
    Prepare;
    Open;
    if Fields[0].IsNull then Result := 1
    else Result := Fields[0].AsInteger+1;
    Close;
  end;

end;

function THotelData.FindCurrency(const aSqlStr: string): Currency;
begin
  with qrySysData do
  begin
    Close;
    SQL.Clear;
    SQL.Add(aSqlStr);
    Prepare;
    Open;
    if Fields[0].IsNull then Result := 0
    else Result := Fields[0].AsCurrency;
    Close;
  end;
end;

function THotelData.FindValue(const aSqlStr: string): string;
begin
  with qrySysData do
  begin
    Close;
    SQL.Clear;
    SQL.Add(aSqlStr);
    Prepare;
    Open;
    if Fields[0].IsNull then Result := ''
    else Result := Fields[0].AsString;
    Close;
  end;
end;

//edit by ls.2003.01.10
procedure THotelData.GetYssj(var HH,NN,SS: Word);
var
  aYssj: string;
begin
  tblYssj.Open;
  if tblYssj.IsEmpty then
  begin
    aYssj := '233000';
  end
  else
  begin
    tblYssj.Last;
    aYssj := tblYssj.FieldByName('D_YSSJ').AsString;
  end;
  HH := StrToInt(Copy(aYssj,1,2));
  NN := StrToInt(Copy(aYssj,3,2));
  SS := StrToInt(Copy(aYssj,5,2));
end;

function THotelData.GetDdts(const aKfbh: string): string;
var
  aKrbh: string;
  aDdrq: TDateTime;
  aTs : Integer;
begin
  with qryUserData do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select * from KFZT where D_KFBH="'+aKfbh+'"');
    Prepare;
    Open;
    aKrbh := FieldByName('D_KRBH').AsString;
  end;
  with qryUserData do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select * from KRXX where D_KRBH="'+aKrbh+'"');
    Prepare;
    Open;
    if not IsEmpty then
      aDdrq := FieldByName('D_DDRQ').AsDateTime
    else
      aDdrq := Date;
  end;
  aTs := DaysBetween(Date,aDdrq);
  if (aTs = 0)or(aTs = 1) then
    Result := ''
  else if (aTs > 26) then
    Result := 'Z'
  else
    Result := Chr(64+aTs);
end;

//备份电话费
procedure THotelData.EmptyDhf;
var
  s : string;
begin
  with qryDhf do
  begin
    if Active then Active := False;
    DatabaseName := JF_DIR;
    SQL.Clear;
    //edit by ls 2002.12.28
    s := 'delete from DHFDATA.DBF where (SDate<:DD) or ((SDate=:DD)and(STime<"23:00"))';
    SQL.Add(s);
    ParamByName('DD').AsDate := Date;
    Prepare;
    ExecSQL;
  end;
end;

//清空当前电话费
procedure THotelData.BakDhf;
var
  s : string;
begin
  with qryDhf do
  begin
    if Active then Active := False;
    DatabaseName := JF_DIR;
    SQL.Clear;
    s := 'select * from DHFDATA.DBF where (SDate<:DD) or ((SDate=:DD)and(STime<"23:00"))';
    SQL.Add(s);
    ParamByName('DD').AsDate := Date;
    Open;
  end;
  with tblDhfOld do
  begin
    if Active then Active := False;
    DatabaseName := JF_DIR;
    TableName := 'DHFOLD.DBF';
    Open;
  end;
  qryDhf.First;
  while not qryDhf.Eof do
  begin
    tblDhfOld.Append;
    tblDhfOld.FieldByName('ROOM').AsString := qryDhf.FieldByName('ROOM').AsString;
    tblDhfOld.FieldByName('SDATE').AsDateTime := qryDhf.FieldByName('SDATE').AsDateTime;
    tblDhfOld.FieldByName('STIME').AsString := qryDhf.FieldByName('STIME').AsString;
    tblDhfOld.FieldByName('NUMBER').AsString := qryDhf.FieldByName('NUMBER').AsString;
    tblDhfOld.FieldByName('LAST').AsFloat := qryDhf.FieldByName('LAST').AsFloat;
    tblDhfOld.FieldByName('PLACE').AsString := qryDhf.FieldByName('PLACE').AsString;
    tblDhfOld.FieldByName('UNIT').AsFloat := qryDhf.FieldByName('UNIT').AsFloat;
    tblDhfOld.FieldByName('FEE').AsFloat := qryDhf.FieldByName('FEE').AsFloat;
    tblDhfOld.FieldByName('SVC_FEE').AsFloat := qryDhf.FieldByName('SVC_FEE').AsFloat;
    tblDhfOld.FieldByName('ADDI_FEE').AsFloat := qryDhf.FieldByName('ADDI_FEE').AsFloat;
    tblDhfOld.FieldByName('TOTAL').AsFloat := qryDhf.FieldByName('TOTAL').AsFloat;
    tblDhfOld.FieldByName('FLTY').AsFloat := qryDhf.FieldByName('FLTY').AsFloat;
    tblDhfOld.Post;
    qryDhf.Next;
  end;
  tblDhfOld.Close;
  qryDhf.Close;
end;

procedure THotelData.AddKrzdZdXfje(const AZdbh: string;AXfje: Currency);
var
  s : string;
begin
  s := 'update KRZD set D_XFJE = D_XFJE+'+CurrToStr(AXfje)+' where (D_ZDBH="'+AZdbh+'")and(D_HH=0)';
  Execsql(s);
end;

procedure THotelData.InsertKrzd(const AZdbh,AZdlb,AKrbh,AYjbh,AKfbh,AXmbh,AJzbz,AJsbz,ABmbh,ABz: string;AHh: Integer;AXfje,AYhje: Currency);
var
  s : string;
begin
  s := 'insert into KRZD (D_ZDBH,D_ZDLB,D_KRBH,D_YJBH,D_KFBH,D_XMBH,D_JZBZ,D_JSBZ,D_BMBH,D_BZ,D_HH,D_XFJE,D_YHJE,D_XFRQ,D_XFSJ) values '+
       '("'+AZdbh+'","'+AZdlb+'","'+AKrbh+'","'+AYjbh+'","'+AKfbh+'","'+AXmbh+'","'+AJzbz+
       '","'+AJsbz+'","'+ABmbh+'","'+ABz+'",'+IntToStr(AHh)+','+CurrToStr(AXfje)+','+CurrToStr(AYhje)+
       ',"'+FormatDateTime('yyyy-mm-dd',Date)+'","'+FormatDateTime('yyyy-mm-dd hh:nn:ss',Now)+'")';
  Execsql(s);
end;

procedure THotelData.InsertYjk(const AShlx,ABz: string;ARmb,AXyk,AZp: Currency);
var
  s : string;
begin
  s := 'insert into YJK (D_RMB,D_XYK,D_ZP,D_CZYXM,D_YJRQ,D_YJSJ,D_YJLX,D_BZ) values '+
       '('+FormatFloat('0.00',ARmb)+','+FormatFloat('0.00',AXyk)+','+FormatFloat('0.00',AZp)+','+
       '"'+CZY.CzyXm+'","'+FormatDateTime('yyyy-mm-dd',Date)+'","'+FormatDateTime('yyyy-mm-dd hh:nn:ss',Now)+
       '","'+AShlx+'","'+ABz+'")';
  ExecSql(s);
end;

procedure THotelData.ExecSql(const ASqlStr: string);
begin
  with qryUserData do
  begin
    Close;
    SQL.Clear;
    SQL.Add(ASqlStr);
    Prepare;
    ExecSQL;
  end;
end;

function THotelData.SumCount(const ASqlStr: string): Integer;
begin
  with qryUserData do
  begin
    SQL.Clear;
    SQL.Add(ASqlStr);
    Open;
    if Fields[0].IsNull then Result := 0
    else Result := Fields[0].AsInteger;
    Close;
  end;
end;

function THotelData.GetXfxx(const AZdbh: string): TXfxx;
var
  s: string;
  AYjje: Currency;
begin
  Result.AZdbh := AZdbh;
  s := 'select sum(D_XFJE) from KRZD where (D_XMBH<>"'
      +XMBH_YJK
      +'")and(D_JZBZ<>"'+JZ_YX+'")and(D_HH<>0)and(D_ZDBH="'+AZdbh+'")';
  Result.AXfje := SumJe(s);
  {s := 'select sum(D_XFJE) from KRZD where (D_XMBH="'
      +XMBH_YJK
      +'")and(D_HH<>0)and(D_ZDBH="'+AZdbh+'")';
  AYjk := SumJe(s);}
  s := 'select sum(D_XFJE) from KRZD where (D_JZBZ="'
      +JZ_YX
      +'")and(D_HH<>0)and(D_ZDBH="'+AZdbh+'")';
  AYjje := SumJe(s);

  Result.AYjje := 0-AYjje;
end;

procedure THotelData.ListTdmc(AComboBox: TComboBox);
begin
  with tblKrxx do
  begin
    try
      try
        Open;
      except
        On E:Exception do
        begin
          ShowWarning('打开数据库出错，请检查网络连接是否正常！'
                      +#13#10
                      +'错误信息:'
                      +E.Message);
          raise;
        end;
      end;

      AComboBox.Items.Clear;

      Filter   := 'D_KRLX=''T''';
      Filtered := True;

      First;
      while not Eof do
      begin
        AComboBox.Items.Add(tblKrxx.FIeldbyName('D_KRXM').AsString);
        Next;
      end;
    finally
      Filter   := '';
      Filtered := False;
      Close;
    end;
  end;
end;

function THotelData.GetFirstRec(const ATableName,AFieldBh,AFieldMc: string): TRecInfo;
begin
  with tblSysData do
  begin
    try
      if Active then Active := False;
      TableName := ATableName;
      Open;
    except
      On E:Exception do
      begin
        ShowWarning('打开'+ATableName+'出错，请检查网络连接是否正常！'
                    +#13#10
                    +'错误信息:'
                    +E.Message);
        raise;
      end;
    end;
    if not IsEmpty then
    begin
      First;
      Result.ABh := FieldByName(AFieldBh).AsString;
      Result.AMc := FieldByName(AFieldMc).AsString;
    end
    else
    begin
      Result.ABh := '';
      Result.AMc := '';
    end;
    Close;
  end;
end;

//通过员工编号、操作项目 检查员工权限 具有权限返回True
function THotelData.CheckYgqx(const AYgbh,ACzxm: string): Boolean;
var
  s: string;
begin
  Result := False;

  if AYgbh=SYSTEM_BH then //如果是系统管理员，则永远返回True
  begin
    Result := True;
    Exit;
  end;

  with qrySysData do
  begin
    if Active then Active := False;

    s := 'select * from YGQX where D_YGBH="'+AYgbh+'"';
    SQL.Clear;
    SQL.Add(s);

    try
      Open;

      if Locate('D_CZXM',ACzxm,[]) then   //察看操作项目
        Result := FieldByName('D_QX').AsBoolean;
      if not Result then
        ShowWarning('对不起，你没有权限操作该模块！');

      Close;
    except
      On E:Exception do
      begin
        ShowWarning('打开YGQX.DB出错，请检查网络连接是否正常！'
                    +#13#10
                    +'错误信息:'
                    +E.Message);
        raise;
      end;
    end;

  end;
end;

//通过客人编号、到店日期、到店时间 获取电话费
function THotelData.GetDhf(const AKfbh: string;Addrq,Addsj: TDateTime): Currency;
var
  s: string;
  ARoom: string;
  ASj: string;
begin
  //连接旧的程序
  if Length(Akfbh)=3 then
  //if Copy(AKfbh,1,2) = '10' then
    //ARoom := '1' + Copy(AKfbh,3,3)
    ARoom := '1' + AKfbh
  else
    //ARoom := Copy(AKfbh,1,1) + Copy(AKfbh,3,3);
    ARoom := AKfbh;
  ASj := FormatDateTime('hh:nn',ADdsj);
  with qryDhf do
  begin
    if Active then Active := False;
    DatabaseName := JF_DIR;
    SQL.Clear;
    //edit by ls.20021030
    {s := 'select sum(TOTAl) from DHFDATA.DBF where (ROOM = "'
        +ARoom+'")and((SDate>:BD)or((SDate=:BD)and(STime>"'+ASj+'")))';}
    s := 'select sum(TOTAl) from DHFDATA.DBF where (ROOM = "'
        +ARoom+'")and((SDate>:BD)or((SDate=:BD)and(STime>"'+ASj+'")))';

    SQL.Add(s);
    ParamByName('BD').DataType := ftDate;