ufrmcancelaccount.pas

来自「小银行系统」· PAS 代码 · 共 460 行

unit UfrmCancelAccount;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, U_child1_1, Mask, StdCtrls, Buttons, ExtCtrls, DosMove,
  DBTables, DB, DBClient, Provider, ADODB, DBCtrls, Grids, DBGridEh,
  wwdblook;

type
  TfrmCancelAccount = class(TF_child1_1)
    PanTop: TPanel;
    Label3: TLabel;
    Label5: TLabel;
    Label11: TLabel;
    Label24: TLabel;
    Edtfac_nm: TEdit;
    Edtsec_nm: TEdit;
    BitBtn_query: TBitBtn;
    EdtPY: TEdit;
    BitBtn1: TBitBtn;
    Edtyymmdd: TMaskEdit;
    Label14: TLabel;
    Label8: TLabel;
    Label15: TLabel;
    DBEdit20: TDBEdit;
    ADOFactory: TADODataSet;
    ADOFactoryfac_no: TStringField;
    ADOFactoryfac_nm: TStringField;
    ADOsec: TADODataSet;
    ADOsecsec_no: TStringField;
    ADOsecsec_nm: TStringField;
    Label26: TLabel;
    MaskEdit1: TMaskEdit;
    wwDBLookupCombosec_no: TwwDBLookupCombo;
    Label4: TLabel;
    wwDBLookupComboFac_no: TwwDBLookupCombo;
    Label1: TLabel;
    DBEdit3: TDBEdit;
    Label2: TLabel;
    Label6: TLabel;
    DBEdit5: TDBEdit;
    Label7: TLabel;
    DBEdit7: TDBEdit;
    DBEdit8: TDBEdit;
    DBEdit9: TDBEdit;
    DBEdit10: TDBEdit;
    DBEdit4: TDBEdit;
    Label9: TLabel;
    EdtACC_NO: TEdit;
    Label10: TLabel;
    DBEdit6: TDBEdit;
    Label12: TLabel;
    DBEdit12: TDBEdit;
    Label23: TLabel;
    Edtpers: TEdit;
    EdtEndyymmdd: TMaskEdit;
    ClientDataSet1ACC_NO: TStringField;
    ClientDataSet1Per_na: TStringField;
    ClientDataSet1Per_no: TStringField;
    ClientDataSet1fac_no: TStringField;
    ClientDataSet1sec_no: TStringField;
    ClientDataSet1Bz_no: TStringField;
    ClientDataSet1Cre_date: TStringField;
    ClientDataSet1remark: TStringField;
    ClientDataSet1PY: TStringField;
    ClientDataSet1add_date: TStringField;
    ClientDataSet1add_user: TStringField;
    ClientDataSet1edit_date: TStringField;
    ClientDataSet1edit_user: TStringField;
    ClientDataSet1Cancel_YN: TStringField;
    ClientDataSet1cancel_date: TStringField;
    ClientDataSet1fac_nm: TStringField;
    ClientDataSet1sec_nm: TStringField;
    procedure BitBtn1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure BitBtn_queryClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure MaskEdit1Exit(Sender: TObject);
    procedure wwDBLookupCombosec_noExit(Sender: TObject);
    procedure wwDBLookupComboFac_noExit(Sender: TObject);
    procedure DBEdit1Exit(Sender: TObject);
    procedure MaskEdit1KeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
  private
    { Private declarations }
    vpers:integer;
  public
    { Public declarations }
    procedure DoInsert;override;
    procedure DoEdit;override;
    procedure DoGetData;override;
    procedure DoDelete;override;
    procedure DoSave;override;
    procedure DoCancel;override;
  end;

var
  frmCancelAccount: TfrmCancelAccount;
  frmFirst,confirm :boolean;

implementation
uses U_main,UDM,U_sele,user_func,fuct_server,UExcelTools;

{$R *.dfm}

procedure TfrmCancelAccount.DoInsert;
begin
  inherited;
  if confirm =false then
  begin
     f_main.B_cancel.Click ;
     exit ;
  end ;
  DBEdit1.SetFocus ;

  MaskEdit1.Text :=f_main.vTodayDate ;
  MaskEdit1.Visible :=true;
  DBEdit2.Visible :=false;

  self.ClientDataSet1.FieldByName('cancel_YN').AsString := 'Y' ;
  self.ClientDataSet1.FieldByName('cancel_date').AsString := f_main.vTodayDate ;
  self.ClientDataSet1.FieldByName('add_date').AsString :=f_main.vTodayDate ;
  self.ClientDataSet1.FieldByName('add_user').AsString :=f_main.vuser_no ;
end;

procedure TfrmCancelAccount.DoEdit;
begin
  inherited;
  if confirm =false then
  begin
     f_main.B_cancel.Click ;
     exit ;
  end ;
  MaskEdit1.Visible :=true;
  DBEdit2.Visible :=false;
  MaskEdit1.Text := self.ClientDataSet1.FieldByName('Cancel_date').AsString ;
  self.ClientDataSet1.FieldByName('edit_date').AsString :=f_main.vTodayDate ;
  self.ClientDataSet1.FieldByName('edit_user').AsString :=f_main.vuser_no ;

end;

procedure TfrmCancelAccount.DoGetData;
begin
  inherited;
end;

procedure TfrmCancelAccount.DoDelete;
begin
  inherited;
end;

procedure TfrmCancelAccount.DoCancel;
begin
  inherited;
  MaskEdit1.Visible :=false;
  DBEdit2.Visible :=true;

end;

//存档
procedure TfrmCancelAccount.DoSave;
var Sqlstr:string;
  TempAdo : TAdodataSet;
begin
  if (self.ClientDataSet1.State=dsInsert) OR
     (self.ClientDataSet1.State=dsEdit) then
  begin
    if (DBEdit1.Text ='') then
    begin
       showmessage('帐号不能为空，请重新输入!');
       dbEdit1.SetFocus ;
       exit ;
    end ;
  end;

  Sqlstr:='SELECT  A.rem_money from '+
  '(SELECT * FROM Deposit  where ACC_NO='''+DBEdit1.Text+''') AS A INNER JOIN '+
  '(SELECT ACC_NO,max(serial_no) AS serial_no FROM dbo.Deposit '+
  ' WHERE ACC_NO='''+DBEdit1.Text+''' GROUP BY ACC_NO) as B '+
  '  ON A.ACC_NO=B.ACC_NO  AND A.serial_no=B.serial_no ';
  TempAdo := TAdodataSet.Create(nil);
  try
    TempAdo.Connection := DM.ADOConnection1 ;
    TempAdo.CommandText := Sqlstr;
    TempAdo.Prepared := True;
    TempAdo.Open;
    if TempAdo.FieldByName('rem_money').AsFloat >0 then
    begin
        MessageDlg('对不起，该储户'+char(10)+'存款余额不为零，不准消户.'+char(10)+'请重新核对', mtInformation,[mbOk], 0);
        f_main.B_cancel.Click;
        exit ;
     end;
  finally
    TempAdo.Free;
  end;

  sqlstr:='select count(*) nn from Account where Cancel_YN=''Y'' and ACC_NO='''
   +DBEdit1.Text+'''' ;
  TempAdo := TAdodataSet.Create(nil);
  try
    TempAdo.Connection := DM.ADOConnection1 ;
    TempAdo.CommandText := Sqlstr;
    TempAdo.Prepared := True;
    TempAdo.Open;

  if (self.ClientDataSet1.State=dsInsert)  then
  begin
     if TempAdo.FieldByName('nn').AsInteger >0 then
     begin
        MessageDlg('对不起该资料'+char(10)+'在你存档前已经存在.', mtInformation,[mbOk], 0);
        f_main.B_cancel.Click;
        exit ;
     end;
  end;
  if (self.ClientDataSet1.State=dsEdit)  then
  begin
     if TempAdo.FieldByName('nn').AsInteger=0 then
     begin
        MessageDlg('对不起该资料'+char(10)+'在你存档前已经不存在.'+char(10)+'请重新读取资料', mtInformation,[mbOk], 0);
        f_main.B_cancel.Click;
        exit ;
     end;
  end;
  finally
    TempAdo.Free;
  end;

  inherited;
  if (self.ClientDataSet1.State=dsInsert) then
  begin
     vpers := vpers + 1 ;
     Edtpers.Text := inttostr(vpers) ;
  end ;
  MaskEdit1.Visible :=false;
  DBEdit2.Visible :=true;
end;

procedure TfrmCancelAccount.BitBtn1Click(Sender: TObject);
begin
  inherited;
  close ;
end;

procedure TfrmCancelAccount.FormCreate(Sender: TObject);
var sqlstr:string;
begin
  inherited;
  self.ADOFactory.Active :=true;
  self.ADOsec.Active :=true;
  frmFirst :=true;
  confirm :=false ;
  Edtyymmdd.Text := Copy(f_main.vTodayDate,1,7)+'-01' ;
  EdtEndyymmdd.Text :=f_main.vTodayDate ;
  sqlstr :='';
  sqlstr := sqlstr+' and A.Cre_date>='''+ Edtyymmdd.Text+'''' ;
  sqlstr := sqlstr+' and A.Cre_date<='''+ Edtendyymmdd.Text+'''' ;
  sqlstr := sqlstr+' and A.Cancel_YN=''Y'' ' ;

  sqlstr := copy(sqlstr,6,length(sqlstr)-5);
  if trim(sqlstr)<>'' then
     sqlstr := ' where '+sqlstr ;

  sqlstr:='SELECT A.*, B.fac_nm,C.sec_nm  FROM Account A '+
     ' LEFT  JOIN factory B ON A.fac_no = B.fac_no '+
     ' LEFT  JOIN Sec C ON A.sec_no = C.sec_no '+ sqlstr +
     ' ORDER  BY Acc_no ';
  self.ClientDataSet1.Active :=false;
  self.ADODataSet1.CommandText:=sqlstr;
  self.ClientDataSet1.Active :=true;
  self.Memo1.Visible :=false;
  self.DBGrid1.Visible :=true;
  vpers := self.ClientDataSet1.RecordCount ;
  Edtpers.Text := inttostr(vpers) ;

  MaskEdit1.Visible :=false;
  DBEdit2.Visible :=true;
  confirm :=true ;
end;

procedure TfrmCancelAccount.FormActivate(Sender: TObject);
begin
  inherited;
  if frmFirst=true then
  begin
     wwDBLookupComboFac_no.SetFocus ;
     frmFirst :=false;
     MaskEdit1.Visible :=false;
     DBEdit2.Visible :=true;
  end;
end;

procedure TfrmCancelAccount.BitBtn_queryClick(Sender: TObject);
var sqlstr:string;
begin
  inherited;
  sqlstr :='';
  if EdtACC_NO.Text <>''   then
     sqlstr :=sqlstr +' and A.Acc_no='''+edtAcc_no.Text +''''
  else
  begin
    if wwDBLookupComboFac_no.Text <>'' then
       sqlstr := sqlstr + ' and A.fac_no='''+ wwDBLookupComboFac_no.Text +'''';
    if wwDBLookupCombosec_no.Text <>'' then
       sqlstr := sqlstr + ' and A.sec_no='''+wwDBLookupCombosec_no.Text +'''';
    if (edtyymmdd.text <>'    -  -  ') then
        sqlstr := sqlstr+' and A.Cre_date>='''+ Edtyymmdd.Text+'''' ;
    if (Edtendyymmdd.Text<>'    -  -  ') then
       sqlstr := sqlstr+' and A.Cre_date<='''+ Edtendyymmdd.Text+'''' ;
    //if Edtper_no.Text <>'' then
    //   sqlstr := sqlstr+' and A.per_no = '''+ EdtPer_no.Text +'''' ;
    //if Edtper_na.Text <>'' then
    //   sqlstr := sqlstr+' and A.per_na like ''%'+ TRIM(Edtper_na.Text)+'%''' ;
    if EdtPY.Text <>'' then
       sqlstr := sqlstr+' and A.PY='''+ EdtPY.Text+ ''' ';
  end;
  sqlstr := sqlstr+' and A.Cancel_YN=''Y'' ' ;

  sqlstr := copy(sqlstr,6,length(sqlstr)-5);
  if trim(sqlstr)<>'' then
     sqlstr := ' where '+sqlstr ;

  sqlstr:='SELECT A.*, B.fac_nm,C.sec_nm  FROM Account A '+
     ' LEFT  JOIN factory B ON A.fac_no = B.fac_no '+
     ' LEFT  JOIN Sec C ON A.sec_no = C.sec_no '+ sqlstr +
     ' ORDER  BY Acc_no ';
  self.ClientDataSet1.Active :=false;
  self.ADODataSet1.CommandText:=sqlstr;
  self.ClientDataSet1.Active :=true;
  self.Memo1.Visible :=false;
  self.DBGrid1.Visible :=true;

  //Edtpers.Text := inttostr(self.ClientDataSet1.RecordCount) ;
  vpers := self.ClientDataSet1.RecordCount ;
  Edtpers.Text := inttostr(vpers) ;

  MaskEdit1.Visible :=false;
  DBEdit2.Visible :=true;
  confirm :=true ;
end;

procedure TfrmCancelAccount.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  inherited;
  self.ADOFactory.Free ;
  self.ADOsec.Free ;
  self.ADODataSet1.Free ;
  self.ClientDataSet1.Free ;
  self.DataSetProvider1.Free ;
  frmCancelAccount :=nil;
end;

procedure TfrmCancelAccount.MaskEdit1Exit(Sender: TObject);
begin
  inherited;
  if not isdate(MaskEdit1.Text) then
  begin
     showmessage('消户日期输入错误，请重新输入!');
     MaskEdit1.SetFocus ;
     exit ;
  end ;
  DBEdit2.Text :=MaskEdit1.Text ;
end;

procedure TfrmCancelAccount.wwDBLookupCombosec_noExit(Sender: TObject);
begin
  inherited;
  if  self.ADOsec.Locate('sec_no',wwDBLookupCombosec_no.Text,[])  then
      edtsec_nm.text :=self.ADOsec.fieldbyname('sec_nm').asstring;

end;

procedure TfrmCancelAccount.wwDBLookupComboFac_noExit(Sender: TObject);
begin
  inherited;
  if  self.ADOFactory.Locate('fac_no',wwDBLookupCombofac_no.Text,[])  then
      edtfac_nm.text :=self.ADOFactory.fieldbyname('fac_nm').asstring;

end;

procedure TfrmCancelAccount.DBEdit1Exit(Sender: TObject);
var
  TempAdo : TAdodataSet;
  sqlstr :string;
begin
  inherited;
  if (self.ClientDataSet1.State=dsInsert) OR
     (self.ClientDataSet1.State=dsEdit) then
  begin
    sqlstr:='select count(*) nn from Account where Cancel_YN=''Y'' and ACC_NO='''
    +DBEdit1.Text+'''' ;
    TempAdo := TAdodataSet.Create(nil);
    try
      TempAdo.Connection := DM.ADOConnection1 ;
      TempAdo.CommandText := Sqlstr;
      TempAdo.Prepared := True;
      TempAdo.Open;

      if (self.ClientDataSet1.State=dsInsert)  then
      begin
         if TempAdo.FieldByName('nn').AsInteger >0 then
         begin
            MessageDlg('对不起该资料'+char(10)+', 该储户已经消户.', mtInformation,[mbOk], 0);
            TempAdo.Free;
            DBEdit1.SetFocus ;
            exit ;
         end;
      end;
    finally
      TempAdo.Free;
    end ;

    // 读取开户资料
    sqlstr:='SELECT A.*, B.fac_nm,C.sec_nm  FROM Account A '+
     ' LEFT  JOIN factory B ON A.fac_no = B.fac_no '+
     ' LEFT  JOIN Sec C ON A.sec_no = C.sec_no '+
     ' WHERE A.Acc_no='''+DBEdit1.text +''' ' ;
    TempAdo := TAdodataSet.Create(nil);
    try
      TempAdo.Connection := DM.ADOConnection1 ;
      TempAdo.CommandText := Sqlstr;
      TempAdo.Prepared := True;
      TempAdo.Open;
      if not TempAdo.eof then
      begin
         DBEdit12.Text := TempAdo.fieldbyname('per_na').AsString ;
         DBEdit3.Text := TempAdo.fieldbyname('per_no').AsString ;
         DBEdit6.Text := TempAdo.fieldbyname('Bz_no').AsString ;
         DBEdit5.Text := TempAdo.fieldbyname('PY').AsString ;
         DBEdit4.Text := TempAdo.fieldbyname('Cre_date').AsString ;
         DBEdit7.Text := TempAdo.fieldbyname('fac_no').AsString ;
         DBEdit8.Text := TempAdo.fieldbyname('fac_nm').AsString ;
         DBEdit10.Text := TempAdo.fieldbyname('sec_no').AsString ;
         DBEdit9.Text := TempAdo.fieldbyname('sec_nm').AsString ;
         DBEdit20.Text := TempAdo.fieldbyname('remark').AsString ;
      end
      else
      begin
         showmessage('此用户不存在，请重新输入!');
         DBEdit1.SetFocus ;
         exit ;
      end ;
    finally
      TempAdo.Free;
    end ;
  end ;

end;

procedure TfrmCancelAccount.MaskEdit1KeyDown(Sender: TObject;
  var Key: Word; Shift: TShiftState);
begin
  inherited;
  if key=13 then f_main.B_update.SetFocus ;
end;

end.