dataclear.pas

一个典当行的业务管理系统程序

unit DataClear;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, Spin;

type
  TfrmDataClear = class(TForm)
    GroupBox1: TGroupBox;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label8: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    txtBeginYear: TSpinEdit;
    txtBeginMonth: TSpinEdit;
    txtBeginDay: TSpinEdit;
    txtEndYear: TSpinEdit;
    txtEndMonth: TSpinEdit;
    txtEndDay: TSpinEdit;
    cmdOk: TButton;
    cmdExit: TButton;
    staDataClear: TStaticText;
    procedure cmdExitClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure cmdOkClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmDataClear: TfrmDataClear;

implementation

uses dmPawn;

{$R *.DFM}

procedure TfrmDataClear.cmdExitClick(Sender: TObject);
begin
     close;
end;

procedure TfrmDataClear.FormShow(Sender: TObject);
var
   Year,Month,Day:word;
begin
     DecodeDate(Now,Year,Month,Day);
     txtBeginYear.Value:=Year;
     txtBeginMonth.Value:=Month;
     txtBeginDay.Value:=Day;
     txtEndYear.Value:=Year;
     txtEndMonth.Value:=Month;
     txtEndDay.Value:=Day;
end;

procedure TfrmDataClear.cmdOkClick(Sender: TObject);
var
   Sql:string;
   BeginDate,EndDate:TDateTime;
begin
     //要将这段时间内的已赎回的数据从数据库中删除分别在这6个数据表中：
     //PawnHead_Tbl,PawnDetail_Tbl,PawnMoney_Tbl,RansomMoney_Tbl,PDHistory_Tbl,PHHistory_Tbl
     //他们之间的纽带就是当票编号PawnTicketCode
     if messagedlg('是否确认清理？',mtConfirmation,[mbYes,mbNo],0)=mrNo then
     begin
          staDataClear.Caption:='清理数据过程被取消！';
          exit;   //退出清理过程
     end;
     //self.Cursor:=crHourGlass;
     BeginDate:=EncodeDate(txtBeginYear.Value,txtBeginMonth.Value,txtBeginDay.Value);
     EndDate:=EncodeDate(txtEndYear.Value,txtEndMonth.Value,txtEndDay.Value);
     Sql:= 'select * from PawnHead_Tbl ';
     Sql:=Sql+'where True and Status=''赎回'' ';
     Sql:=Sql+'and (CreateDate>=:BeginDate and CreateDate<=:EndDate) ';
     with dmPawnFrm do
     begin
          dqHeadUn.Close;
          dqHeadUn.SQL.Clear;
          dqHeadUn.SQL.Add(Sql);
          dqHeadUn.ParamByName('BeginDate').Asdatetime:=BeginDate;
          dqHeadUn.ParamByName('EndDate').AsDateTime:=EndDate;
          dqHeadUn.Open;
          if not dqHeadUn.IsEmpty then
          begin
               //----将细表PawnDetail_Tbl中有相同当票编号的记录都删除
               Sql:='select * from PawnDetail_Tbl as d ';
               Sql:=Sql+'where d.PawnTicketCode=:PawnCode ';
               dqHeadUn.First;   //依次取出赎回的当票编号
               while not dqHeadUn.Eof do
               begin
                    dqDelRansom.Close;
                    dqDelRansom.SQL.Clear;
                    dqDelRansom.SQL.Add(Sql);
                    dqDelRansom.ParamByName('PawnCode').asstring:=dqHeadUn.FieldByName('PawnTicketCode').Asstring;
                    dqDelRansom.Open;
                    if not dqDelRansom.IsEmpty then
                    begin
                         dqDelRansom.First;
                         while not dqDelRansom.Eof do
                         begin
                              dqDelRansom.Delete;
                         end;
                    end;
                    dqHeadUn.Next;    //再取下一条已赎回的当票编号
               end;                      //细表PawnDetail_Tbl中的已赎回记录已删除

               //----将历史细表PDHistory_tbl中有相同当票编号的记录都删除
               Sql:='select * from PDHistory_Tbl as d ';
               Sql:=Sql+'where d.PawnTicketCode=:PawnCode ';
               dqHeadUn.First;   //依次取出赎回的当票编号
               while not dqHeadUn.Eof do
               begin
                    dqDelRansom.Close;
                    dqDelRansom.SQL.Clear;
                    dqDelRansom.SQL.Add(Sql);
                    dqDelRansom.ParamByName('PawnCode').asstring:=dqHeadUn.FieldByName('PawnTicketCode').Asstring;
                    dqDelRansom.Open;
                    if not dqDelRansom.IsEmpty then
                    begin
                         dqDelRansom.First;
                         while not dqDelRansom.Eof do
                         begin
                              dqDelRansom.Delete;
                         end;
                    end;
                    dqHeadUn.Next;    //再取下一条已赎回的当票编号
               end;                      //历史细表PDHistory_Tbl中的已赎回记录已删除

               //----将历史头表PHHistory_Tbl中有相同编号的记录都删除
               Sql:='select * from PHHistory_Tbl as d ';
               Sql:=Sql+'where d.PawnTicketCode=:PawnCode ';
               dqHeadUn.First;   //依次取出赎回的当票编号
               while not dqHeadUn.Eof do
               begin
                    dqDelRansom.Close;
                    dqDelRansom.SQL.Clear;
                    dqDelRansom.SQL.Add(Sql);
                    dqDelRansom.ParamByName('PawnCode').asstring:=dqHeadUn.FieldByName('PawnTicketCode').Asstring;
                    dqDelRansom.Open;
                    if not dqDelRansom.IsEmpty then
                    begin
                         dqDelRansom.First;
                         while not dqDelRansom.Eof do
                         begin
                              dqDelRansom.Delete;
                         end;
                    end;
                    dqHeadUn.Next;    //再取下一条已赎回的当票编号
               end;                      //历史头表PHHistory_Tbl中的已赎回记录已删除

               //----将数据表PawnMoney_Tbl中已赎回的记录删除
               Sql:='select * from PawnMoney_Tbl as d ';
               Sql:=Sql+'where d.PawnTicketCode=:PawnCode ';
               dqHeadUn.First;   //依次取出赎回的当票编号
               while not dqHeadUn.Eof do
               begin
                    dqDelRansom.Close;
                    dqDelRansom.SQL.Clear;
                    dqDelRansom.SQL.Add(Sql);
                    dqDelRansom.ParamByName('PawnCode').asstring:=dqHeadUn.FieldByName('PawnTicketCode').Asstring;
                    dqDelRansom.Open;
                    if not dqDelRansom.IsEmpty then
                    begin
                         dqDelRansom.First;
                         while not dqDelRansom.Eof do
                         begin
                              dqDelRansom.Delete;
                         end;
                    end;
                    dqHeadUn.Next;    //再取下一条已赎回的当票编号
               end;                      //PawnMoney_Tbl表中的已赎回记录已删除

               //---将数据表RansomMoney_Tbl中已赎回的记录删除
               Sql:='select * from RansomMoney_Tbl as d ';
               Sql:=Sql+'where d.PawnTicketCode=:PawnCode ';
               dqHeadUn.First;   //依次取出赎回的当票编号
               while not dqHeadUn.Eof do
               begin
                    dqDelRansom.Close;
                    dqDelRansom.SQL.Clear;
                    dqDelRansom.SQL.Add(Sql);
                    dqDelRansom.ParamByName('PawnCode').asstring:=dqHeadUn.FieldByName('PawnTicketCode').Asstring;
                    dqDelRansom.Open;
                    if not dqDelRansom.IsEmpty then
                    begin
                         dqDelRansom.First;
                         while not dqDelRansom.Eof do
                         begin
                              dqDelRansom.Delete;
                         end;
                    end;
                    dqHeadUn.Next;    //再取下一条已赎回的当票编号
               end;                      //RansomMoney_Tbl表中的已赎回记录已删除

               //****最后删除头表PawnHead_Tbl中已赎回的所有记录
               dqHeadUn.First;
               tblClearHead.Open;
               tblClearHead.IndexFieldNames:='PawnTicketCode';
               while not dqHeadUn.Eof do
               begin
                    tblClearHead.SetKey;
                    tblClearHead.FieldByName('PawnTicketCode').asstring:=dqHeadUn.FieldByName('PawnTicketCode').Asstring;
                    if tblClearHead.GotoKey then
                    begin
                         tblClearHead.Delete;
                    end;
                    dqHeadUn.Next;
               end;
               tblClearHead.Close;
               staDataClear.Caption:='清理数据完毕，请继续使用！';
          end     //在头表PawnHead_Tbl找到此段时间内已赎回的记录
          else
          begin
               staDataClear.Caption:='这段时期无已赎回数据！';
               exit;
          end;
          //self.Cursor:=crArrow;
     end;
end;

end.