dqczlcx.pas

集成酒店桑拿食管管理的完整程序

unit dqczlcx;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Db, DBTables, StdCtrls, Grids, DBGrids, ComCtrls, ExtCtrls;

type
  Tdqczlcxform = class(TForm)
    TabControl1: TTabControl;
    DBGrid1: TDBGrid;
    Query1: TQuery;
    DataSource1: TDataSource;
    Query2: TQuery;
    Memo1: TMemo;
    Timer1: TTimer;
    Query3: TQuery;
    lc: TComboBox;
    procedure FormCreate(Sender: TObject);
    procedure TabControl1Change(Sender: TObject);
    procedure DBGrid1TitleClick(Column: TColumn);
    procedure Timer1Timer(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  dqczlcxform: Tdqczlcxform;

implementation
uses dataproc;
{$R *.DFM}

procedure czltj(value:string);
var
  czs:ttime;
begin
with dqczlcxform do
  begin
    Query3.Active:=false;
    Query3.SQL.Clear;
    Query3.SQL.Text:='select RoomNo,status,zczl,nczl,yczl,rczl from room1';
    Query3.Prepare;
    Query3.Open;
    while not Query3.Eof do
    begin
       czs:=0;
       if (Query3.FieldByName('status').Asstring='1')  then
       begin
          Query2.Active:=false;
          Query2.SQL.Clear;
          Query2.SQL.Text:='select BegTime from nowin where roomno='''+Query3.Fieldbyname('roomno').asstring+'''';
          Query2.Prepare;
          Query2.Open;
          while not Query2.Eof do
          begin
             if Query2.Fields[0].Asdatetime<strtodatetime(value+' 00:00') then
                 czs:=czs+strtotime(formatdatetime('hh:mm',now))
             else
                 czs:=czs+strtotime(formatdatetime('hh:mm',now-Query2.Fields[0].Asdatetime));
             Query2.Next;
          end;
       end;
       Query2.Active:=false;
       Query2.SQL.Clear;
       Query2.SQL.Text:='select BegTime,EndTime from lsnowin where  Handno='''+Query3.Fieldbyname('roomno').asstring+''' and EndTime>'''+value+' 00:00'+'''';
       Query2.Prepare;
       Query2.Open;
       while (not Query2.Eof) do
       begin
         if Query2.Fields[0].Asdatetime<strtodatetime(value+' 00:00') then
             czs:=czs+(Query2.Fields[1].Asdatetime-strtodatetime(value+' 00:00'))
         else
             czs:=czs+(Query2.Fields[1].Asdatetime-Query2.Fields[0].Asdatetime);
         Query2.Next;
       end;
//        showmessage(timetostr(czs)+','+Query3.Fieldbyname('roomno').asstring);
      ///////////fffffffffffzzzzzzzzzz
      { Query2.Active:=false;
       Query2.SQL.Clear;
       rczl:=strtofloat(copy(timetostr(czs),1,2))+strtofloat(copy(timetostr(czs),4,2))/60;
       Query2.Active:=false;
       Query2.SQL.Clear;
       Query2.SQL.Text:='update room1 set dqczl='+floattostr(rczl*100/24)+' where roomno='''+Query3.FieldByName('roomno').Asstring+'''';
       Query2.Prepare;
       Query2.ExecSQL;
       Query2.sql.Clear;
       Query2.UnPrepare;}
       Query3.next;
    end;

  end;
end;

procedure init(lz:integer;bx:string);
var tj:string;
begin
  with dqczlcxform do
   begin
     Memo1.Lines.Clear;
     Memo1.Lines.Add('   '+TabControl1.Tabs[lz]+'客房出租率统计表');
     Memo1.Lines.Add(' ');

     Query3.Active :=false;
     Query3.sql.clear;
     if lz=0 then
     begin
       tj:='';
       Query3.sql.text:='select Name,roomtype from roomtype ';
     end
     else
     begin
        tj:=' and REVERSE(substring(REVERSE(rtrim(room1.roomno)),3,2))='''+lc.Items[lz]+'''';
{       try
//        tj:=tj+' and room1.roomno<'''+lc.Items[lz+1]+'''';
       except
       end;}
        Query3.sql.text:='select Name,roomtype from roomtype';
     end;
     Query2.Active :=false;
     Query2.sql.clear;                                                       //, avg(dqczl)
     Query2.sql.text:='select DISTINCT avg(zczl),avg(nczl),avg(yczl),avg(rczl) from room1 where roomno<>'''' '+tj ;
     Query2.Open;
     if Query2.Fields[0].asstring<>'' then
     begin
        Memo1.Lines.Add('  总出租率:   '+Query2.Fields[0].asstring+' %');
        Memo1.Lines.Add('  年出租率:   '+Query2.Fields[1].asstring+' %');
        Memo1.Lines.Add('  月出租率:   '+Query2.Fields[2].asstring+' %');
        Memo1.Lines.Add('  昨日出租率: '+Query2.Fields[3].asstring+' %');
      //  Memo1.Lines.Add('  当前出租率: '+Query2.Fields[4].asstring+' %');
//            Memo1.Lines.Add(' ');
     end;
     Memo1.Lines.Add('');
     Memo1.Lines.Add('其中： ');
     Memo1.Lines.Add('');

     Query3.Open;
     while not Query3.Eof do
     begin
//,
         Query2.Active :=false;                                                          //, avg(dqczl)
         Query2.sql.clear;
         Query2.sql.text:='select DISTINCT avg(zczl),avg(nczl),avg(yczl),avg(rczl) from room1 where roomtype='''+Query3.Fieldbyname('roomtype').asstring+''''+tj ;
         Query2.Open;
         if Query2.Fields[0].asstring<>'' then
         begin
            Memo1.Lines.Add(Query3.Fieldbyname('name').asstring+':');
            Memo1.Lines.Add('      总出租率:   '+Query2.Fields[0].asstring+' %');
            Memo1.Lines.Add('      年出租率:   '+Query2.Fields[1].asstring+' %');
            Memo1.Lines.Add('      月出租率:   '+Query2.Fields[2].asstring+' %');
            Memo1.Lines.Add('      昨日出租率: '+Query2.Fields[3].asstring+' %');
          //  Memo1.Lines.Add('      当前出租率: '+Query2.Fields[4].asstring+' %');
//            Memo1.Lines.Add(' ');
         end;
         Query3.Next;
     end;
     Query1.Active :=false;                                                                                     // ,dqczl
     Query1.sql.clear;
     Query1.sql.text:='select roomno=room1.RoomNo,roomtype=Name,BedNum,Price,TurnTime,CSPrice,zczl,nczl,yczl,rczl from room1,roomtype where RoomType.RoomType=room1.RoomType '+tj+' order by '+bx ;
     Query1.Open;
     dbgrid1.Columns[0].width:=40;
     dbgrid1.Columns[1].width:=50;
     dbgrid1.Columns[2].width:=25;
     dbgrid1.Columns[3].width:=30;
     dbgrid1.Columns[4].width:=50;
     dbgrid1.Columns[5].width:=40;
     dbgrid1.Columns[6].width:=40;
     dbgrid1.Columns[7].width:=40;
     dbgrid1.Columns[8].width:=40;
     dbgrid1.Columns[9].width:=40;
     dbgrid1.Columns[10].width:=40;
     chinesegrid(dbgrid1);
     DBGrid1.fields[6].displaylabel:='总租率';
     DBGrid1.fields[7].displaylabel:='年租率';
     DBGrid1.fields[8].displaylabel:='月租率';
     DBGrid1.fields[9].displaylabel:='昨日率';
    // DBGrid1.fields[10].displaylabel:='当前率';
//     tstringfield(DBGrid1.Fields[6]).displayvalues:='是;否';

     Memo1.Visible:=true;
     DBGrid1.Visible:=true;

   end;
end;

procedure Tdqczlcxform.FormCreate(Sender: TObject);
var 
    i:integer;
begin
  Query1.Active :=false;
  Query1.sql.clear;
  Query1.sql.add('select DISTINCT REVERSE(substring(REVERSE(rtrim(roomno)),3,2)) from room1');
  Query1.Prepare ;
  Query1.open;
  TabControl1.Tabs.Clear;
  lc.Items.Clear;
  lc.Items.Add(' ');
  TabControl1.Tabs.Add('全部');
  for i:=1 to 30 do
  begin
    lc.Items.Add('');
    TabControl1.Tabs.Add('');
  end;
  while not Query1.eof do
  begin
     lc.Items[Query1.fields[0].asinteger]:=Query1.fields[0].asstring;
     TabControl1.Tabs[Query1.fields[0].asinteger]:=Query1.fields[0].asstring+'层';
     Query1.Next;
  end;
  Query1.Active :=false;
  Query1.sql.clear;
  for i:=0 to 29 do
   if trim(lc.Items[30-i])='' then
   begin
    lc.Items.Delete(30-i);
    TabControl1.Tabs.Delete(30-i);
  end;

//  init(0,'roomno');
  Timer1Timer(nil);

end;

procedure Tdqczlcxform.TabControl1Change(Sender: TObject);
begin
  init(TabControl1.TabIndex,'rczl');

end;

procedure Tdqczlcxform.DBGrid1TitleClick(Column: TColumn);
begin
  init(TabControl1.TabIndex,Column.DisplayName);

end;

procedure Tdqczlcxform.Timer1Timer(Sender: TObject);
begin
   dqczlcxform.Caption:='客房出租率           '+formatdatetime('yyyy',now)+'年'+formatdatetime('mm',now)+'月'+formatdatetime('dd',now)+'日';
   dqczlcxform.Caption:=dqczlcxform.Caption+' 截止到 '+formatdatetime('hh:mm',now)+' 的出租率';
   czltj(datetostr(now));
   init(TabControl1.TabIndex,'rczl');
end;

procedure Tdqczlcxform.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
 Timer1.Free;
 close;
end;

procedure Tdqczlcxform.FormKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if key=112 then
   begin
      if xt=1 then  help(16);
   end;

end;

end.