ufrmopenaccount.pas

小银行系统

unit UfrmOpenAccount;

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
  TfrmOpenAccount = class(TF_child1_1)
    PanTop: TPanel;
    Label3: TLabel;
    Label5: TLabel;
    Label11: TLabel;
    Label23: TLabel;
    Label24: TLabel;
    Edtfac_nm: TEdit;
    Edtsec_nm: TEdit;
    BitBtn_query: TBitBtn;
    Edtpers: TEdit;
    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;
    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;
    ClientDataSet1fac_nm: TStringField;
    ClientDataSet1sec_nm: TStringField;
    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;
    ClientDataSet1add_date: TStringField;
    ClientDataSet1add_user: TStringField;
    ClientDataSet1edit_date: TStringField;
    ClientDataSet1edit_user: TStringField;
    Edtendyymmdd: TMaskEdit;
    ClientDataSet1New_fac_no: TStringField;
    ClientDataSet1New_sec_no: TStringField;
    Label12: TLabel;
    DBEdit11: TDBEdit;
    DBEdit12: TDBEdit;
    Label13: TLabel;
    DBEdit13: TDBEdit;
    DBEdit14: TDBEdit;
    ClientDataSet1Cancel_YN: TStringField;
    ClientDataSet1cancel_date: TStringField;
    ClientDataSet1New_fac_nm: TStringField;
    ClientDataSet1New_sec_nm: TStringField;
    Label17: TLabel;
    StaticText1: TStaticText;
    StaticText2: TStaticText;
    StaticText3: TStaticText;
    StaticText4: TStaticText;
    StaticText5: TStaticText;
    StaticText6: TStaticText;
    StaticText7: TStaticText;
    StaticText8: TStaticText;
    StaticText9: TStaticText;
    StaticText10: TStaticText;
    EdtIN_10: TEdit;
    EdtIN_9: TEdit;
    EdtIN_8: TEdit;
    EdtIN_5: TEdit;
    EdtIN_7: TEdit;
    EdtIN_6: TEdit;
    EdtIN_2: TEdit;
    EdtIN_4: TEdit;
    EdtIN_3: TEdit;
    EdtIN_1: TEdit;
    Label16: TLabel;
    EdtPer_no: TEdit;
    EdtPer_na: TEdit;
    CheckBox1: TCheckBox;
    ClientDataSet1IN_10: TStringField;
    ClientDataSet1IN_9: TStringField;
    ClientDataSet1IN_8: TStringField;
    ClientDataSet1IN_7: TStringField;
    ClientDataSet1IN_6: TStringField;
    ClientDataSet1IN_5: TStringField;
    ClientDataSet1IN_4: TStringField;
    ClientDataSet1IN_3: TStringField;
    ClientDataSet1IN_2: TStringField;
    ClientDataSet1IN_1: TStringField;
    ClientDataSet1serial_no: TStringField;
    Label18: TLabel;
    DBEdit15: TDBEdit;
    Shape1: TShape;
    UpdateSQL2: TUpdateSQL;
    ClientDataSet1IN_money: TBCDField;
    ClientDataSet1Cdate: TStringField;
    ClientDataSet1rem_money: TBCDField;
    Label19: TLabel;
    DBEdit16: TDBEdit;
    Label20: TLabel;
    DBEdit17: TDBEdit;
    ClientDataSet1rep_loss_YN: TStringField;
    ClientDataSet1rep_loss_date: 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 DBEdit7Exit(Sender: TObject);
    procedure DBEdit10Exit(Sender: TObject);
    procedure DBEdit2Exit(Sender: TObject);
    procedure DBEdit11Exit(Sender: TObject);
    procedure DBEdit13Exit(Sender: TObject);
    procedure EdtPer_noExit(Sender: TObject);
    procedure ClientDataSet1CalcFields(DataSet: TDataSet);
    procedure ClientDataSet1AfterScroll(DataSet: TDataSet);
    procedure EdtIN_10Enter(Sender: TObject);
    procedure EdtIN_10KeyPress(Sender: TObject; var Key: Char);
    procedure EdtIN_10Exit(Sender: TObject);
    procedure DataSetProvider1BeforeUpdateRecord(Sender: TObject;
      SourceDS: TDataSet; DeltaDS: TCustomClientDataSet;
      UpdateKind: TUpdateKind; var Applied: Boolean);
    procedure MaskEdit1Enter(Sender: TObject);
    procedure EdtIN_1KeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
  private
    { Private declarations }
    vpers :integer ;
    procedure refresh1;
  public
    { Public declarations }
    procedure DoInsert;override;
    procedure DoEdit;override;
    procedure DoGetData;override;
    procedure DoDelete;override;
    procedure DoSave;override;
    procedure DoCancel;override;
  end;

var
  frmOpenAccount: TfrmOpenAccount;
  frmFirst,confirm,First_code :boolean;

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

{$R *.dfm}

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

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

  self.ClientDataSet1.FieldByName('Cancel_YN').AsString := 'N';
  self.ClientDataSet1.FieldByName('rep_loss_YN').AsString :='N' ;
  self.ClientDataSet1.FieldByName('Cre_date').AsString := f_main.vTodayDate ;
  self.ClientDataSet1.FieldByName('add_date').AsString :=f_main.vTodayDate ;
  self.ClientDataSet1.FieldByName('add_user').AsString :=f_main.vuser_no ;
  DBEdit11.Enabled :=false ;
  DBEdit13.Enabled :=false ;
  First_code :=true ;
  DBEdit1.Enabled :=false ;
  DBEdit2.SetFocus ;
end;

procedure TfrmOpenAccount.DoEdit;
begin
  inherited;
  if confirm =false then
  begin
     f_main.B_cancel.Click ;
     exit ;
  end ;
  MaskEdit1.Visible :=true;
  DBEdit4.Visible :=false;
  MaskEdit1.Text := self.ClientDataSet1.FieldByName('Cre_date').AsString ;
  self.ClientDataSet1.FieldByName('edit_date').AsString :=f_main.vTodayDate ;
  self.ClientDataSet1.FieldByName('edit_user').AsString :=f_main.vuser_no ;
  DBEdit11.Enabled :=true ;
  DBEdit13.Enabled :=true ;
end;

procedure TfrmOpenAccount.DoGetData;
begin
  inherited;
end;

procedure TfrmOpenAccount.DoDelete;
var sqlstr:string;
  TempAdo :TAdodataSet;
begin
    sqlstr:='select count(*) nn from Deposit where ACC_NO='''+EdtACC_NO.Text+'''';
    TempAdo := TAdodataSet.Create(nil);
    try
      TempAdo.Connection := DM.ADOConnection1 ;
      TempAdo.CommandText := Sqlstr;
      TempAdo.Prepared := True;
      TempAdo.Open;

      if TempAdo.FieldByName('nn').AsInteger >0 then
      begin
          MessageDlg('对不起该资料'+char(10)+'已发生存取款业务，不能删除！', mtInformation,[mbOk], 0);
          f_main.B_cancel.Click;
          exit ;
      end;
   finally
     TempAdo.Free ;
   end ;

  inherited;
end;

procedure TfrmOpenAccount.DoCancel;
begin
  inherited;
  MaskEdit1.Visible :=false;
  DBEdit4.Visible :=true;

end;

//存档
procedure TfrmOpenAccount.DoSave;
var Sqlstr,vnum:string;
  nn: integer;
  TempAdo : TAdodataSet;
  vIN_money :real;
  sIN_money :string ;
  vIN_10,vIN_9,vIN_8,vIN_7,vIN_6,vIN_5,vIN_4,vIN_3,vIN_2,vIN_1 :string ;
begin
  //产生帐号
  if (self.ClientDataSet1.State=dsInsert)  then
  begin
    TempAdo := TAdodataSet.Create(nil);
    sqlstr := 'select max(Acc_no) dno, CONVERT(int,substring(max(Acc_no),6,4)) nn '
       +'FROM Account where fac_no='''+DBEdit7.Text+
       ''' AND sec_no='''+DBEdit10.Text+'''' ;
    try
      TempAdo.Connection := DM.ADOConnection1 ;
      TempAdo.CommandText := Sqlstr;
      TempAdo.Prepared := True;
      TempAdo.Open;

      nn := TempAdo.fieldbyname('nn').Asinteger ;
      vnum := DBEdit7.Text + DBEdit10.Text+ Right('0000'+trim(IntToStr(nn+1)),4);

      self.ClientDataSet1.FieldByName('Acc_no').AsString:=vnum;
    finally
      TempAdo.Free;
    end;
  end ;

  sqlstr:='select count(*) nn from Account where 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;

  //存款金额
  if EdtIN_10.Text='' then
     vIN_10 :='0'
  else
     vIN_10 :=EdtIN_10.Text ;
  if EdtIN_9.Text='' then
     vIN_9 :='0'
  else
     vIN_9 :=EdtIN_9.Text ;
  if EdtIN_8.Text='' then
     vIN_8 :='0'
  else
     vIN_8 :=EdtIN_8.Text ;
  if EdtIN_7.Text='' then
     vIN_7 :='0'
  else
     vIN_7 :=EdtIN_7.Text ;
  if EdtIN_6.Text='' then
     vIN_6 :='0'
  else
     vIN_6 :=EdtIN_6.Text ;
  if EdtIN_5.Text='' then
     vIN_5 :='0'
  else
     vIN_5 :=EdtIN_5.Text ;
  if EdtIN_4.Text='' then
     vIN_4 :='0'
  else
     vIN_4 :=EdtIN_4.Text ;
  if EdtIN_3.Text='' then
     vIN_3 :='0'
  else
     vIN_3 :=EdtIN_3.Text ;
  if EdtIN_2.Text='' then
     vIN_2 :='0'
  else
     vIN_2 :=EdtIN_2.Text ;
  if EdtIN_1.Text='' then
     vIN_1 :='0'
  else
     vIN_1 :=EdtIN_1.Text ;

  sIN_money :=vIN_10 + vIN_9 + vIN_8 + vIN_7 +vIN_6 + vIN_5 +
              vIN_4 + vIN_3 +'.'+ vIN_2 + vIN_1 ;
  vIN_money := StrToFloat(Trim(sIN_money));

  if (self.ClientDataSet1.State=dsInsert) and (vIN_money=0) then
  begin
     showmessage('存款金额不能为0，请重新输入!');
     EdtIN_10.SetFocus ;
     exit ;
  end;

  if (self.ClientDataSet1.State=dsInsert) or
     (self.ClientDataSet1.State=dsEdit)  then
  begin
     self.ClientDataSet1Cdate.Value := MaskEdit1.Text ;
     self.ClientDataSet1IN_money.Value  := vIN_money ;
     self.ClientDataSet1REM_money.Value := vIN_money ;
  end ;

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

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

procedure TfrmOpenAccount.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,E.fac_nm New_fac_nm,F.sec_nm New_sec_nm,'+
   'G.Cdate, G.IN_money, G.rem_money '+
   ' 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 '+
   ' LEFT  JOIN factory  E  ON A.New_fac_no = E.fac_no '+
   ' LEFT  JOIN Sec F ON A.New_sec_no = F.sec_no '+
   ' LEFT  JOIN Deposit G ON A.Acc_no=G.Acc_no '+
   ' AND A.Cre_date=G.Cdate  AND A.serial_no=G.serial_no '+sqlstr +
   ' ORDER  BY A.Acc_no ';


  self.ClientDataSet1.Active :=false;
  self.ADODataSet1.CommandText:=sqlstr;
  self.ClientDataSet1.Active :=true;
  self.DBGrid1.Visible :=true;
  //Edtpers.Text := inttostr(self.ClientDataSet1.RecordCount) ;
  vpers := self.ClientDataSet1.RecordCount ;
  Edtpers.Text := inttostr(vpers) ;
  MaskEdit1.Visible :=false;
  DBEdit4.Visible :=true;
  confirm :=true ;

end;

procedure TfrmOpenAccount.FormActivate(Sender: TObject);
begin