datadelete.pas

飞恒进销存(超市批发)管理系统V5.1（含源程序） 语言：Delphi 6/7 相关控件：FastReport 2.4以上, Ehlib 3.4以上 1.数据库为fhe2db_V51.da

unit DataDelete;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ExtCtrls, StdCtrls, Mask, DBCtrlsEh, Buttons, ComCtrls;

type
  TDeleteForm = class(TForm)
    Panel1: TPanel;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    cmdOK: TBitBtn;
    BitBtn2: TBitBtn;
    dtpStartDate: TDateTimePicker;
    dtpEndDate: TDateTimePicker;
    Bevel1: TBevel;
    CBIorder: TCheckBox;
    CBIinstore: TCheckBox;
    CBMIreturn: TCheckBox;
    CBIpay: TCheckBox;
    CBOOrder: TCheckBox;
    CBOoutstore: TCheckBox;
    CBOreturn: TCheckBox;
    CBOcharge: TCheckBox;
    CBDmove: TCheckBox;
    CBDcheck: TCheckBox;
    CBDInOut: TCheckBox;
    chkexpense: TCheckBox;
    chkINCOME: TCheckBox;
    cbDMATERIAL: TCheckBox;
    cbIBuild: TCheckBox;
    cbITear: TCheckBox;
    cbPos_Bill: TCheckBox;
    chbFlashDisk: TCheckBox;
    btnBack: TBitBtn;
    procedure FormShow(Sender: TObject);
    procedure cmdOKClick(Sender: TObject);
    procedure btnBackClick(Sender: TObject);
    procedure chbFlashDiskClick(Sender: TObject);
  private
    { Private declarations }
    date1,date2:string; //要执行的sql 时间段
    nbillid:Integer;  //备份数据时检查单号是否大于ninvoseq
    Function setsqltext(TableName:string):string;
    Function BakBaseTable(sTblName,sCnTblName,sId:string):string;
    Function BakTableIO(sTblName,sCnTblName:string):string;
    procedure StartBackUp;
  public
    { Public declarations }
    ATableName:array[0..16,0..1] of string;
  end;

var
  DeleteForm: TDeleteForm;

implementation

uses datamodule1, flash;

{$R *.dfm}

procedure TDeleteForm.FormShow(Sender: TObject);
var
  i:integer;
  s:string;
begin
  dtpStartDate.DateTime:=date();
  dtpEndDate.Date:=date();
  chbFlashDisk.Visible:=Not dataE2.bBakAccSet;

  //检测U盘是否在线，否则不可备份到U盘移动数据库
  s:='fhE2Bak_log.ldf';
  with dataE2 do
  begin
    if querytmp.active then queryTmp.close;
    queryTmp.sql.clear;
    queryTmp.sql.add('select * from msdb.dbo.fhAccount where accsetId=2');
    queryTmp.open;
    if not queryTmp.eof then
      s:=trim(querytmp.fieldbyname('sqlPath').asstring)+s
    else
      s:='I:\'+s;
    queryTmp.close;
  end;

  if FileExists(s) then
    chbFlashDisk.enabled:=true;

  for i:=0 to 16 do aTableName[i,0]:='';
  ATableName[0,0]:='I_Order';   ATableName[0,1]:='采购订单';
  ATableName[1,0]:='I_INSTORE'; ATableName[1,1]:='进货单';
  ATableName[2,0]:='I_RETURN';  ATableName[2,1]:='进货退货';
  ATableName[3,0]:='I_PAY';     ATableName[3,1]:='进货付款';
  ATableName[4,0]:='O_ORDER';   ATableName[4,1]:='销售订单';
  ATableName[5,0]:='O_OUTSTORE';ATableName[5,1]:='销售单';
  ATableName[6,0]:='O_RETURN';  ATableName[6,1]:='销售退货';
  ATableName[7,0]:='O_CHARGE';  ATableName[7,1]:='销售付款';
  ATableName[8,0]:='D_MOVE';    ATableName[8,1]:='转仓单';
  ATableName[9,0]:='D_CHECK';   ATableName[9,1]:='盘点单';
  ATableName[10,0]:='D_INOUT';  ATableName[10,1]:='其它出入库';
  ATableName[11,0]:='D_expense';ATableName[11,1]:='费用单';
  ATableName[12,0]:='D_INCOME'; ATableName[12,1]:='收入单';
  ATableName[13,0]:='D_MATERIAL';ATableName[13,1]:='配料单';
  ATableName[14,0]:='I_BUILD';  ATableName[14,1]:='组装单';
  ATableName[15,0]:='I_TEAR';   ATableName[15,1]:='拆卸单';
  ATableName[16,0]:='Pos_Bill'; ATableName[16,1]:='POS销售单';

  for i := 0 to ComponentCount-1 do
  begin
    if (Components[i] is TCheckBox) then
      TCheckBox(components[i]).Checked := false;
  end;
end;

Function TDeleteForm.BakTableIO(sTblName,sCnTblName:string):string;
var
  ssql:string;
begin
  result:='';
  with DataE2.adoCmd do
  try
    begin
      with DataE2.QueryTmp do
      begin
        Close;
        SQL.Clear;
        SQL.Add('select billid from fhe2bak.dbo.BillCode where TABLENAME=:TABLENAME');
        Parameters[0].Value:=sTblName;
        open;
        nbillid:=FieldByName('billid').AsInteger;
      end;

      FlashForm.lblFlash.Caption:='正在备份'+sCnTblName+'……';
      FlashForm.Repaint;
      //主表
      sSql:='delete from fhe2bak.dbo.'+sTblname;  //2005-11-18 +' select * from '+sTblname;
      sSql:=sSql+' where billId in (select billid from '+sTblName+' )';
      sSql:=sSql+' and billid>:billid';
      commandtext:=sSql;
      Parameters[0].Value:=nbillid;
      Execute;
      sSql:='insert into fhe2bak.dbo.'+sTblname +' select * from '+sTblname;
      sSql:=sSql+' where billId not in (select billid from fhe2bak.dbo.'+sTblName+' )';
      sSql:=sSql+' and billid>:billid';
      commandtext:=sSql;
      Parameters[0].Value:=nbillid;
      Execute;
      //明细表
      sSql:='delete from fhe2bak.dbo.'+sTblname;  // +'D select * from '+sTblname;
      //sSql:=sSql+'D where billId+itemNo in (select billId+itemNo from '+sTblName+'D )';
      sSql:=sSql+'D where billId in (select billId from '+sTblName+'D )';
      sSql:=sSql+' and billid>:billid';
      commandtext:=sSql;
      Parameters[0].Value:=nbillid;
      Execute;
      sSql:='insert into fhe2bak.dbo.'+sTblname +'D select * from '+sTblname;
      //sSql:=sSql+'D where billId+itemNo not in (select billid+ItemNO from fhe2bak.dbo.'+sTblName+'D )';
      sSql:=sSql+'D where billId not in (select billid from fhe2bak.dbo.'+sTblName+'D )';
      sSql:=sSql+' and billid>:billid';
      commandtext:=sSql;
      Parameters[0].Value:=nbillid;
      Execute;
    end;
    with DataE2.QueryTmp do
    begin
      Close;
      SQL.Clear;
      SQL.Add('select billid from BillCode where TABLENAME=:TABLENAME');
      Parameters[0].Value:=sTblName;
      open;
      nbillid:=FieldByName('billid').AsInteger;
      Close;
      SQL.Clear;
      SQL.Add('update fhe2bak.dbo.BillCode set Billid=:billid where TABLENAME=:TABLENAME');
      Parameters[0].Value:=nbillid;
      Parameters[1].Value:=sTblName;
      ExecSQL;
    end;
  except
    result:=sCnTblName;
  end;
end;

Function TDeleteForm.BakBaseTable(sTblName,sCnTblName,sId:string):string;
var
  ssql:string;
begin
  result:='';
  with DataE2.adoCmd do
  try
    begin
        FlashForm.lblFlash.Caption:='正在备份'+sCnTblName+'……';
        FlashForm.Repaint;
        sSql:='delete from FHE2Bak.dbo.'+sTblname;
        sSql:=sSql+' where '+sID +' in (select '+sid+' from '+sTblName+' )';
        commandtext:=sSql;
        Execute;

        sSql:='insert into fhe2bak.dbo.'+sTblname +' select * from '+sTblname;
        sSql:=sSql+' where '+sID +' not in (select '+sid+' from fhe2bak.dbo.'+sTblName+' )';
        commandtext:=sSql;
        Execute;
    end;
  except
    result:=','+sCnTblName;
  end;
end;

procedure TDeleteForm.cmdOKClick(Sender: TObject);
var
  i:integer;
  sqlstr,sErrInfo:string;
begin
  if Not dataE2.bBakAccSet then
    StartBackUp;

  sqlstr:='';
  date1:=formatdatetime('yyyy-mm-dd',dtpStartDate.Date);
  date2:=formatdatetime('yyyy-mm-dd',dtpEndDate.Date) ;
  for i := 0 to ComponentCount-1 do
  begin
    if (Components[i] is TCheckBox) then
      if TCheckBox(components[i]).Checked then
        sqlstr:=sqlstr+setsqltext(ATableName[TCheckBox(components[i]).tag,0]);
  end;
  if sqlstr<>'' then
  try
    with DataE2.adoCmd do
    begin
      // 开始删除值;
      commandtext:='';
      commandtext:=sqlstr;
      Execute;
    end;
    DataE2.InsEvent('删除数据','成功删除数据！'+'时间:从'+date1+'至'+date2);
    Application.MessageBox('成功删除数据！','提示',64);
  except
    DataE2.InsEvent('删除数据','删除数据失败！');
    Application.MessageBox('删除数据失败！','错误',64);
  end;
end;

Function TDeleteForm.setsqltext(TableName: string):string;
var
  sqlstr:string;
begin
 //每选中一个表就赋值一个sql语句给数组
  sqlstr:='';
  sqlstr:=sqlstr+' '+'delete from '+TableName+'D from '+TableName+' m ,'+TableName+'D d where m.BillId=d.billId and m.billdate between '''+date1+''' and '''+date2+'''';
  sqlstr:=sqlstr+' '+'delete from '+TableName+' where billdate between '''+date1+''' and '''+date2+'''';
  result:=sqlstr;
end;
{
        sSql:='delete from FHE2Back.dbo.ONHAND';
        close;
        SQL.Clear;
        SQL.Add(sSql);
        ExecSQL;
        sSql:='insert into ONHAND select * from fhe2.dbo.ONHAND';
        close;
        SQL.Clear;
        SQL.Add(sSql);
        ExecSQL;
}
procedure TDeleteForm.StartBackUp;
var
  i:integer;
  sqlstr,sErrInfo:string;
begin
  //------备份到可移动磁盘------
  if ( dataE2.hotelspe > 0)  and (DataE2.installdate > now) then  //security use
    if chbFlashDisk.Checked then
    try
      Application.ProcessMessages;

      FlashForm.Show;
      FlashForm.Update;

      sErrInfo:='';
      sErrInfo:=sErrInfo + BakBaseTable('area','地区','areaId');
      sErrInfo:=sErrInfo + BakBaseTable('goodstype2','货品类别2','type+code1+code2');
      sErrInfo:=sErrInfo + BakBaseTable('bank','银行','bankId');
      sErrInfo:=sErrInfo + BakBaseTable('client','客户','clientId');
      sErrInfo:=sErrInfo + BakBaseTable('department','部门','deptId');
      sErrInfo:=sErrInfo + BakBaseTable('employ','员工','employId');
      sErrInfo:=sErrInfo + BakBaseTable('goods','货品','goodsId');
      sErrInfo:=sErrInfo + BakBaseTable('goodstype','货品类别','type');
      sErrInfo:=sErrInfo + BakBaseTable('goodstype1','货品类别1','type+code1');
      sErrInfo:=sErrInfo + BakBaseTable('goodstype2','货品类别2','type+code1+code2');
      sErrInfo:=sErrInfo + BakBaseTable('ietype','支入支出类别','code');
      sErrInfo:=sErrInfo + BakBaseTable('IoType','其它出入库类别','typeId');
      sErrInfo:=sErrInfo + BakBaseTable('paytype','付款方式','paytypeid');
      sErrInfo:=sErrInfo + BakBaseTable('vendor','供应商','vendorId');
      sErrInfo:=sErrInfo + BakBaseTable('ONHAND','库存数量','STOREID+GOODSID');
      sErrInfo:=sErrInfo + BakBaseTable('Store','仓库','STOREID');
      sErrInfo:=sErrInfo + BakBaseTable('unitType','单位','unitId');

      for i := 0 to ComponentCount-1 do
        if (Components[i] is TCheckBox) then
           if TCheckBox(components[i]).Checked then
              sErrInfo:=sErrInfo + BakTableIO(ATableName[TCheckBox(components[i]).tag,0],ATableName[TCheckBox(components[i]).tag,1]);
      FlashForm.close;
      if sErrInfo='' then
        Application.MessageBox(pchar('备份成功！'),'提示',64);
    finally
      FlashForm.close;
      //备份有错
      if sErrInfo<>'' then
        Application.MessageBox(pchar('备份'+sErrInfo+'错误！'),'提示',64);
    end;
  //------结束备份 ------
end;

procedure TDeleteForm.btnBackClick(Sender: TObject);
begin
  StartBackUp;
end;

procedure TDeleteForm.chbFlashDiskClick(Sender: TObject);
begin
  btnBack.Visible:=chbFlashDisk.Checked;
end;

end.