newys.pas

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

    Query2.sql.Clear;
    Query2.Active :=false;
  end;
  Query2.SQL.text:='update phbook set phname='''+dqtime+''' where sanname='''+'jltime'+'''';
  Query2.Prepare;
  Query2.ExecSQL ;
  Query2.Active :=false;
  Label2.Caption :='系统状态：  正在查找。。。';
  label1.Caption :='';
  ProgressBar1.Position:=0;
  source.Active :=false;
end;
end;

procedure oldtonew(olddbf,newdbf,lsdate:string);
var
//  sshj,yshj:real;
  temp:string;
begin
with form2 do
  begin
     Query1.Active:=false;
     Query1.SQL.Clear;
     Query1.SQL.Text:='select max(rbdate) from '+newdbf;
     Query1.Prepare;
     Query1.Open;
     temp:=Query1.Fields[0].asstring;
     Query1.Close;
     Query1.Active:=false;
     Query1.SQL.Clear;
     Query1.SQL.Text:='select code,ssbyhj,ysbyhj from '+olddbf+' where rbdate in (select max(rbdate) from '+olddbf+')';
     Query1.Prepare;
     Query1.Open;
     while not Query1.Eof do
     begin
       Query2.Active:=false;
       Query2.SQL.Clear;
       Query2.SQL.Text:='insert into '+newdbf+' select '''+Query1.Fields[0].asstring+'''';
       Query2.SQL.add('code,sum(jznum),sum(ssxj),sum(wdxj),sum(sszp),sum(sscard),sum(ssgz),sum(ssmd),sum(ssother),sum(ssxiaoj),'+Query1.Fields[1].asstring+',');
       Query2.SQL.add('sum(ysnum),sum(ysxj),sum(yszp),sum(yscard),sum(ysgz),sum(ysmd),sum(ysother),sum(ysxiaoj),'+Query1.Fields[2].asstring+','''+lsdate+'''');
       Query2.SQL.add(' from '+olddbf+' where code='''+Query1.Fields[0].asstring+'''');
       Query2.Prepare;
       Query2.ExecSQL;
       Query1.Next;
     end;
     if trim(temp)<>'' then
     begin
        Query1.Close;
        Query1.Active:=false;
        Query1.SQL.Clear;
        Query1.SQL.Text:='select code,ssbyhj,ysbyhj from '+newdbf+' where rbdate='''+temp+'''';
        Query1.Prepare;
        Query1.Open;
        while not Query1.Eof do
        begin
           Query2.Active:=false;
           Query2.SQL.Clear;
           Query2.SQL.Text:='update '+newdbf+' set ssbyhj=ssbyhj+'+Query1.Fields[1].asstring+',ysbyhj=ysbyhj+'+Query1.Fields[2].asstring;
           Query2.SQL.add('where code='''+Query1.Fields[0].asstring+'''');
           Query2.Prepare;
           Query2.ExecSQL;
           Query2.sql.Clear;
//           Query2.UnPrepare;
           Query1.Next;
        end;
     end;
     Query1.sql.Clear;
//     Query1.UnPrepare;
     Query2.Active:=false;
     Query2.SQL.Clear;
     Query2.SQL.Text:='insert into '+'ls'+olddbf+' select * from '+olddbf;
     Query2.SQL.add('delete '+olddbf);
     Query2.Prepare;
     Query2.ExecSQL;
     Query2.sql.Clear;
//     Query2.UnPrepare;

  end;
end;

procedure czltj(value:string);
var
  rczl:real;
  czs:ttime;
  temp:string;
begin
with form2 do
  begin
    Query1.Active:=false;
    Query1.SQL.Clear;
    Query1.SQL.Text:='select RoomNo,status,zczl,nczl,yczl,rczl from room1';
    Query1.Prepare;
    Query1.Open;
    while not Query1.Eof do
    begin
       czs:=0;
       if (Query1.FieldByName('status').Asstring='1')  then
       begin
          Query2.Active:=false;
          Query2.SQL.Clear;
          Query2.SQL.Text:='select BegTime from nowin where roomno='''+Query1.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('23:59')
             else
                 czs:=czs+strtotime('23:59')-strtotime(formatdatetime('hh:mm',Query2.Fields[0].Asdatetime));
             Query2.Next;
          end;
       end;
       Query2.Active:=false;
       Query2.SQL.Clear;
       Query2.SQL.Text:='select BegTime,EndTime from lslsnowin where  roomno='''+Query1.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+strtotime(formatdatetime('hh:mm',Query2.Fields[1].Asdatetime))
         else
             czs:=czs+strtotime(formatdatetime('hh:mm',Query2.Fields[1].Asdatetime))-strtotime(formatdatetime('hh:mm',Query2.Fields[0].Asdatetime));
         Query2.Next;
       end;
       Query2.Active:=false;
       Query2.SQL.Clear;
       temp:=timetostr(czs);
       rczl:=strtofloat(copy(timetostr(czs),1,2))+strtofloat(copy(timetostr(czs),4,2))/60;
       temp:='';
       if Query1.FieldByName('yczl').Asfloat>=0 then
           temp:=temp+', yczl=(yczl+'+floattostr((rczl/24)*100+0.5)+')/2'
       else
           temp:=temp+', yczl='+floattostr((rczl/24)*100+0.5);
       if Query1.FieldByName('nczl').Asfloat>=0 then
          temp:=temp+', nczl=(nczl+'+floattostr((rczl/24)*100+0.5)+')/2'
       else
           temp:=temp+', nczl='+floattostr((rczl/24)*100+0.5);
       if Query1.FieldByName('zczl').Asfloat>=0 then
          temp:=temp+', zczl=(zczl+'+floattostr((rczl/24)*100+0.5)+')/2'
       else
           temp:=temp+', zczl='+floattostr((rczl/24)*100+0.5);
       Query2.Active:=false;
       Query2.SQL.Clear;
       Query2.SQL.Text:='update room1 set rczltjdate='''+nowdate+''', rczl='+floattostr(rczl*100/24+0.5)+temp+' where roomno='''+Query1.FieldByName('roomno').Asstring+'''';
       Query2.Prepare;
       Query2.ExecSQL;
       Query2.sql.Clear;
//       Query2.UnPrepare;
       Query1.next;
    end;

  end;
end;

procedure setdata(value:string);
var jbtime:string;
begin
with form2 do
  begin
  query1.sql.clear;
  query1.sql.add('update syssetup set rbdate=:pcurnsdate');
  query1.ParamByName ('pcurnsdate').asstring:=value;
  query1.Prepare ;
  query1.ExecSQL ;
  query1.Active :=false;
//  query1.UnPrepare ;
  query1.sql.clear;
  query1.sql.add('select max(GiveTime) from changebc ');
  query1.Prepare ;
  query1.Open;
  jbtime:=query1.Fields[0].asstring;
  query1.Active :=false;
//  query1.UnPrepare ;
  query1.sql.clear;
  query1.sql.add('delete lslsnowin where EndTime<='''+jbtime+'''');
  query1.sql.add('delete lslsjz where jztime<='''+jbtime+'''');
  query1.sql.add('delete lslscustom where Moditime<='''+jbtime+'''');
  query1.Prepare ;
  query1.ExecSQL ;
  query1.Active :=false;
//  query1.UnPrepare ;

  end;
end;



procedure TForm2.Button1Click(Sender: TObject);
begin
  button1.Enabled :=false;
  Animate2.Active :=true;
  Label2.Caption :='系统状态：  正在查找。。。';
  timer1.enabled:=true;
end;

procedure TForm2.FormCreate(Sender: TObject);
begin
  shortdateformat:='yyyy-mm-dd';
  Label2.Caption :='系统状态：  待机';
  getdate;
  button1.Enabled :=false;
  Animate2.Active :=true;
  Label2.Caption :='系统状态：  正在查找。。。';
  timer1.enabled:=true;
  phname.Items.Clear;
  phtype.Items.Clear;
  sanname.Items.Clear;
  Query1.Active :=false;
  Query1.sql.clear;
  Query1.sql.text:='select sanname,phname,phtype from phbook where sanname<>'''+'jltime'+'''';
  Query1.open;
  while not Query1.Eof do
  begin
     phtype.Items.Add(Query1.Fields[2].asstring);
     phname.Items.Add(Query1.Fields[1].asstring);
     sanname.Items.Add(Query1.Fields[0].asstring);
     Query1.Next;
  end;
  Query1.Active :=false;
  Query1.sql.clear;

end;

procedure TForm2.Button2Click(Sender: TObject);
begin
  close;
end;

procedure TForm2.Timer1Timer(Sender: TObject);
begin
  getdate;
  try
   execute;
  except
      Animate1.Active :=false;
      Label2.Caption :='系统状态：  正在查找。。。';
      label1.Caption :='';
      ProgressBar1.Position:=0;
 end;
  nowdate:=formatdatetime('yyyy-mm-dd',now-strtotime('23:59:59'));
  while nowdate=formatdatetime('yyyy-mm-dd',now) do nowdate:=formatdatetime('yyyy-mm-dd',now-strtotime('23:59:59'));
  if trim(nsdate)<>nowdate then
     begin
     Label2.Caption :='系统状态：  夜审';
     Animate1.Active :=true;
     if trim(nlytjtime)=copy(nowdate,9,2) then
          oldtonew('srrb','sryb',copy(nowdate,1,10));
     if trim(nlntjtime)=copy(nowdate,6,5) then
          oldtonew('sryb','srnb',copy(nowdate,1,10));
     query1.sql.clear;
     query1.sql.add('select distinct AccNo from nowin where begtime<='''+formatdatetime('yyyy-mm-dd',now)+' 00:00:00'+'''');
     query1.Prepare ;
     query1.Open;
     while not query1.Eof do
     begin
       rb3.Prepare ;
       rb3.ParamByName ('@itime').asstring:=formatdatetime('yyyy-mm-dd',now)+' 00:00:00';
       rb3.ParamByName ('@btime').asstring:=nowdate+' 00:00:00';
       rb3.ParamByName ('@iAccount').asstring:=query1.Fieldbyname('accno').asstring;
       rb3.Prepare ;
       rb3.ExecProc ;
       query1.Next;
     end;
     query1.Close;
     query1.sql.clear;
     query1.Active :=false;
//     query1.UnPrepare ;
     rb3.UnPrepare;
//     rb3.Active:=false;
     rb1.Prepare ;
     rb1.ParamByName ('@lsendtime').asstring:=formatdatetime('yyyy-mm-dd',now)+' 00:00:00';
     rb1.ParamByName ('@lsbegtime').asstring:=nowdate+' 00:00:00';
     rb1.Prepare ;
     rb1.ExecProc ;
     query1.sql.clear;
     query1.Active :=false;
     query1.sql.add('insert into sslj select code,0 from jkmoney where code not in (select code from sslj)');
     query1.Prepare ;
     query1.ExecSQL ;
     query1.sql.clear;
     query1.Active :=false;
//     query1.UnPrepare ;
//     rb1.Active:=false;
//     rb1.UnPrepare;
     rb2.Prepare ;
     rb2.ParamByName ('@lsendtime').asstring:=formatdatetime('yyyy-mm-dd',now)+' 00:00:00';
     rb2.ParamByName ('@lsbegtime').asstring:=nowdate+' 00:00:00';
     rb2.Prepare ;
     rb2.ExecProc ;
     query1.Active :=false;
     query1.sql.clear;
     query1.sql.add('update sslj set sslj.byhj=sslj.byhj+jkmoney.xiaoj+rblj.wd from sslj,rblj,jkmoney where jkmoney.code=sslj.code and rblj.code=sslj.code');
     query1.sql.add('insert into srrb select jkmoney.Code,jkmoney.num,jkmoney.xj,rblj.wd,jkmoney.zp,jkmoney.card,jkmoney.gz,jkmoney.md,jkmoney.other,jkmoney.xiaoj+rblj.wd,sslj.byhj,');
     query1.sql.add('rblj.num,rblj.xj,rblj.zp,rblj.card,rblj.gz,rblj.md,rblj.other,rblj.xiaoj,rblj.byhj,'''+nowdate+'''');
     query1.sql.add('from jkmoney,sslj,rblj where jkmoney.code=sslj.code and sslj.code=rblj.code');
     query1.Prepare ;
     query1.ExecSQL ;
     query1.sql.clear;
     query1.Active :=false;
//     query1.UnPrepare ;
     czltj(nowdate);
//     rb2.Active:=false;

     setdata(nowdate);
     Animate1.Active :=false;
     Label2.Caption :='系统状态：  正在查找。。。';
     end;
end;

procedure TForm2.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  Timer1.free;
  rb1.free;
end;

procedure TForm2.Button3Click(Sender: TObject);
begin
  Button1Click(Button1);
  form2.Visible:=false;
end;

end.