newys.pas

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

unit newys;

interface

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

type
  TForm2 = class(TForm)
    Button1: TButton;
    Timer1: TTimer;
    Animate1: TAnimate;
    ProgressBar1: TProgressBar;
    Button2: TButton;
    Label1: TLabel;
    Animate2: TAnimate;
    Label2: TLabel;
    Bevel1: TBevel;
    Database1: TDatabase;
    rb1: TStoredProc;
    Button3: TButton;
    Query1: TQuery;
    rb2: TStoredProc;
    rb3: TStoredProc;
    Query2: TQuery;
    sanname: TComboBox;
    phname: TComboBox;
    phtype: TComboBox;
    source: TQuery;
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Timer1Timer(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Button3Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form2: TForm2;

implementation

{$R *.DFM}
var
  nlntjtime,nlytjtime,nowdate,nsdate:string;

procedure getdate;
begin
with form2 do
  begin
  Query1.sql.clear;
  Query1.sql.add('select ntjbegtime,ytjbegtime,rbdate from syssetup');
  Query1.Prepare ;
  Query1.open;
  nsdate:=Query1.fieldbyname('rbdate').asstring;
  nlntjtime:=Query1.fieldbyname('ntjbegtime').asstring;
  nlytjtime:=Query1.fieldbyname('ytjbegtime').asstring;
  Query1.Active :=false;
  Query1.UnPrepare ;
  end;
end;

procedure execute;
var
  dqtime,jltime,room0,room1,acc,wel,rmno,s,s2:string;
  d:Tdatetime;
  Query2:Tquery;
  i,lsnum:integer;
begin
with form2 do
  begin
 //  if lsnum=0 then
//     exit;
  Label2.Caption :='系统状态：  正在复制。。。';
  label1.Caption :='从 [电话计费] 到 [桑拿管理] ';
  ProgressBar1.Position:=0;

  Query1.Active :=false;
  Query1.sql.clear;
  Query1.sql.text:='select phname from phbook where sanname='''+'jltime'+'''';
  Query1.open;
  jltime:=Query1.Fields[0].asstring;

  Query1.Active :=false;
  Query1.sql.clear;
  Query1.sql.text:='select roomno from room where status='''+'1'+''' or status='''+'4'+'''';
  Query1.open;
  room1:='';
  while not Query1.Eof do
  begin
    if trim(room1)<>'' then room1:=room1+',';
    room1:=room1+''''+Query1.Fields[0].asstring+'''';
    Query1.Next;
  end;
  Query1.Active :=false;
  Query1.sql.clear;
  Query1.sql.text:='select roomno from room where status='''+'0'+''' or status='''+'2'+''' or status='''+'3'+'''';
  Query1.open;
  room0:='';
  while not Query1.Eof do
  begin
    if trim(room0)<>'' then room0:=room0+',';
    room0:=room0+''''+Query1.Fields[0].asstring+'''';
    Query1.Next;
  end;
  Query1.Active :=false;
  Query1.sql.clear;

  dqtime:=formatdatetime('yyyy-mm-dd hh:mm',now);

  source.Active :=false;
  source.sql.clear;
  if  (trim(room1)<>'') then   // 住客房间
  begin
    if (trim(phname.Items[4])<>'') then
    begin
       if copy(dqtime,1,10)=copy(jltime,1,10) then
       begin
          source.sql.text:='select * from '+phname.Items[0]+' where '+phname.Items[1]+' in ('+room1+') and '+phname.Items[5]+'>=:pbegtime and '+phname.Items[5]+'<:pendtime';
       end
       else
       begin
          source.sql.text:='select * from '+phname.Items[0]+' where '+phname.Items[1]+' in ('+room1+') and ((';
          source.sql.Add(phname.Items[4]+'>:pbegdate and '+phname.Items[4]+'<:penddate) or ('+phname.Items[4]+'=:pbegdate and '+phname.Items[5]+'>=:pbegtime) or ('+phname.Items[4]+'=:penddate and '+phname.Items[5]+'<:pendtime))');
          if phtype.Items[4]='date' then
          begin
             source.ParamByName('pbegdate').asdate:=strtodate(copy(jltime,1,10));
             source.ParamByName('penddate').asdate:=strtodate(copy(dqtime,1,10));
          end
          else
          begin
             source.ParamByName('pbegdate').asstring:=copy(jltime,1,10);
             source.ParamByName('penddate').asstring:=copy(dqtime,1,10);
          end;
       end;
       if phtype.Items[5]='time' then
       begin
          source.ParamByName('pbegtime').astime:=strtotime(copy(jltime,12,5));
          source.ParamByName('pendtime').astime:=strtotime(copy(dqtime,12,5));
       end
       else
       begin
          source.ParamByName('pbegtime').asstring:=copy(jltime,12,5);
          source.ParamByName('pendtime').asstring:=copy(dqtime,12,5);
       end;
    end
    else
    begin
       source.sql.text:='select * from '+phname.Items[0]+' where '+phname.Items[1]+' in ('+room1+') and '+phname.Items[5]+'>=:pbegtime and '+phname.Items[5]+'>:pendtime';
       if phtype.Items[5]='datetime' then
       begin
          source.ParamByName('pbegtime').asdatetime:=strtodatetime(jltime);
          source.ParamByName('pendtime').asdatetime:=strtodatetime(dqtime);
       end
       else
       begin
          source.ParamByName('pbegtime').asstring:=jltime;
          source.ParamByName('pendtime').asstring:=dqtime;
       end;
    end;

    source.open;
    while not source.Eof do
    begin
       Query2.Active:=false;
       Query2.sql.Clear;
       Query2.sql.add('insert into phonedb values(');
       for i:=1 to 9 do
          if i<>4 then Query2.sql.add(':p'+sanname.Items[i]+',');
       Query2.sql.add(':p'+sanname.Items[10]+')');
       for i:=1 to 10 do
       begin
        if (i<>4) and (i<>5) then
        begin
           if phtype.Items[i]='string' then
              Query2.ParamByName('p'+sanname.Items[i]).asstring:=source.fieldbyname(phname.Items[i]).asstring;
           if (phtype.Items[i]='money') or (phtype.Items[i]='float') then
              Query2.ParamByName('p'+sanname.Items[i]).asfloat:=source.fieldbyname(phname.Items[i]).asfloat;
           if phtype.Items[i]='integer' then
              Query2.ParamByName('p'+sanname.Items[i]).asinteger:=source.fieldbyname(phname.Items[i]).asinteger;
        end
        else
        begin
            if (i=5) and (trim(phname.Items[4])='') then
            begin
              if (phtype.Items[i]='datetime') then
                 Query2.ParamByName('p'+sanname.Items[i]).asdatetime:=source.fieldbyname(phname.Items[i]).asdatetime;
              if (phtype.Items[i]='string') then
                 Query2.ParamByName('p'+sanname.Items[i]).asstring:=source.fieldbyname(phname.Items[i]).asstring;
            end;
            if (i=5) and (trim(phname.Items[4])<>'') then
            begin
              if (phtype.Items[i]='time') and (phtype.Items[4]='date') then
                 Query2.ParamByName('p'+sanname.Items[i]).asstring:=formatdatetime('yyyy-mm-dd',source.fieldbyname(phname.Items[4]).asdatetime)+' '+formatdatetime('hh:mm',source.fieldbyname(phname.Items[4]).asdatetime);
              if (phtype.Items[i]='time') and (phtype.Items[4]<>'date') then
                 Query2.ParamByName('p'+sanname.Items[i]).asstring:=source.fieldbyname(phname.Items[4]).asstring+' '+formatdatetime('hh:mm',source.fieldbyname(phname.Items[4]).asdatetime);
              if (phtype.Items[i]='string') and (phtype.Items[4]='date') then
                 Query2.ParamByName('p'+sanname.Items[i]).asstring:=formatdatetime('yyyy-mm-dd',source.fieldbyname(phname.Items[4]).asdatetime)+' '+source.fieldbyname(phname.Items[4]).asstring;
              if (phtype.Items[i]='string') and (phtype.Items[4]<>'date') then
                 Query2.ParamByName('p'+sanname.Items[i]).asstring:=source.fieldbyname(phname.Items[4]).asstring+' '+source.fieldbyname(phname.Items[4]).asstring;
            end;
        end;
       end;
//     showmessage(Query2.sql.Text);
       Query2.Prepare;
       Query2.ExecSQL ;
       Query2.Active :=false;
       Query2.sql.clear;
       if ProgressBar1.Position =100 then
          ProgressBar1.Position:=0
       else
          ProgressBar1.StepIt;
       source.Next;
    end;
    source.Active :=false;
    source.sql.clear;
  end;
  if  (trim(room1)<>'') then  // 非住客房间
  begin
    if trim(phname.Items[4])<>'' then
    begin
       if copy(dqtime,1,10)=copy(jltime,1,10) then
       begin
          source.sql.text:='select * from '+phname.Items[0]+' where '+phname.Items[1]+' in ('+room0+') and '+phname.Items[5]+'>=:pbegtime and '+phname.Items[5]+'<:pendtime';
       end
       else
       begin
          source.sql.text:='select * from '+phname.Items[0]+' where '+phname.Items[1]+' in ('+room0+') and ((';
          source.sql.Add(phname.Items[4]+'>:pbegdate and '+phname.Items[4]+'<:penddate) or ('+phname.Items[4]+'=:pbegdate and '+phname.Items[5]+'>=:pbegtime) or ('+phname.Items[4]+'=:penddate and '+phname.Items[5]+'<:pendtime))');
          if phtype.Items[4]='date' then
          begin
             source.ParamByName('pbegdate').asdate:=strtodate(copy(jltime,1,10));
             source.ParamByName('penddate').asdate:=strtodate(copy(dqtime,1,10));
          end
          else
          begin
             source.ParamByName('pbegdate').asstring:=copy(jltime,1,10);
             source.ParamByName('penddate').asstring:=copy(dqtime,1,10);
          end;
       end;
       if phtype.Items[5]='time' then
       begin
          source.ParamByName('pbegtime').astime:=strtotime(copy(jltime,12,5));
          source.ParamByName('pendtime').astime:=strtotime(copy(dqtime,12,5));
       end
       else
       begin
          source.ParamByName('pbegtime').asstring:=copy(jltime,12,5);
          source.ParamByName('pendtime').asstring:=copy(dqtime,12,5);
       end;
    end
    else
    begin
       source.sql.text:='select * from '+phname.Items[0]+' where '+phname.Items[1]+' in ('+room0+') and '+phname.Items[5]+'>=:pbegtime and '+phname.Items[5]+'>:pendtime';
       if phtype.Items[5]='datetime' then
       begin
          source.ParamByName('pbegtime').asdatetime:=strtodatetime(jltime);
          source.ParamByName('pendtime').asdatetime:=strtodatetime(dqtime);
       end
       else
       begin
          source.ParamByName('pbegtime').asstring:=jltime;
          source.ParamByName('pendtime').asstring:=dqtime;
       end;
    end;

    source.open;
    while not source.Eof do
    begin
       Query2.Active:=false;
       Query2.sql.Clear;
       Query2.sql.add('insert into qtphonedb values(');
       for i:=1 to 9 do
          if i<>4 then Query2.sql.add(':p'+sanname.Items[i]+',');
       Query2.sql.add(':p'+sanname.Items[10]+')');
       for i:=1 to 10 do
       begin
        if (i<>4) and (i<>5) then
        begin
           if phtype.Items[i]='string' then
              Query2.ParamByName('p'+sanname.Items[i]).asstring:=source.fieldbyname(phname.Items[i]).asstring;
           if (phtype.Items[i]='money') or (phtype.Items[i]='float') then
              Query2.ParamByName('p'+sanname.Items[i]).asfloat:=source.fieldbyname(phname.Items[i]).asfloat;
           if phtype.Items[i]='integer' then
              Query2.ParamByName('p'+sanname.Items[i]).asinteger:=source.fieldbyname(phname.Items[i]).asinteger;
        end
        else
        begin
            if (i=5) and (trim(phname.Items[4])='') then
            begin
              if (phtype.Items[i]='datetime') then
                 Query2.ParamByName('p'+sanname.Items[i]).asdatetime:=source.fieldbyname(phname.Items[i]).asdatetime;
              if (phtype.Items[i]='string') then
                 Query2.ParamByName('p'+sanname.Items[i]).asstring:=source.fieldbyname(phname.Items[i]).asstring;
            end;
            if (i=5) and (trim(phname.Items[4])<>'') then
            begin
              if (phtype.Items[i]='time') and (phtype.Items[4]='date') then
                 Query2.ParamByName('p'+sanname.Items[i]).asstring:=formatdatetime('yyyy-mm-dd',source.fieldbyname(phname.Items[4]).asdatetime)+' '+formatdatetime('hh:mm',source.fieldbyname(phname.Items[4]).asdatetime);
              if (phtype.Items[i]='time') and (phtype.Items[4]<>'date') then
                 Query2.ParamByName('p'+sanname.Items[i]).asstring:=source.fieldbyname(phname.Items[4]).asstring+' '+formatdatetime('hh:mm',source.fieldbyname(phname.Items[4]).asdatetime);
              if (phtype.Items[i]='string') and (phtype.Items[4]='date') then
                 Query2.ParamByName('p'+sanname.Items[i]).asstring:=formatdatetime('yyyy-mm-dd',source.fieldbyname(phname.Items[4]).asdatetime)+' '+source.fieldbyname(phname.Items[4]).asstring;
              if (phtype.Items[i]='string') and (phtype.Items[4]<>'date') then
                 Query2.ParamByName('p'+sanname.Items[i]).asstring:=source.fieldbyname(phname.Items[4]).asstring+' '+source.fieldbyname(phname.Items[4]).asstring;
            end;
        end;
       end;
//     showmessage(Query2.sql.Text);
       Query2.Prepare;
       Query2.ExecSQL ;
       Query2.Active :=false;
       if ProgressBar1.Position =100 then
          ProgressBar1.Position:=0
       else
          ProgressBar1.StepIt;
       source.Next;
    end;
    source.Active :=false;
    source.sql.clear;