ufrmdeposit.pas

小银行系统

    //取款金额
    if EdtOUT_10.Text='' then
       vOUT_10 :='0'
    else
       vOUT_10 :=EdtOUT_10.Text ;
    if EdtOUT_9.Text='' then
       vOUT_9 :='0'
    else
       vOUT_9 :=EdtOUT_9.Text ;
    if EdtOUT_8.Text='' then
       vOUT_8 :='0'
    else
       vOUT_8 :=EdtOUT_8.Text ;
    if EdtOUT_7.Text='' then
       vOUT_7 :='0'
    else
       vOUT_7 :=EdtOUT_7.Text ;
    if EdtOUT_6.Text='' then
       vOUT_6 :='0'
    else
       vOUT_6 :=EdtOUT_6.Text ;
    if EdtOUT_5.Text='' then
       vOUT_5 :='0'
    else
       vOUT_5 :=EdtOUT_5.Text ;
    if EdtOUT_4.Text='' then
       vOUT_4 :='0'
    else
       vOUT_4 :=EdtOUT_4.Text ;
    if EdtOUT_3.Text='' then
       vOUT_3 :='0'
    else
       vOUT_3 :=EdtOUT_3.Text ;
    if EdtOUT_2.Text='' then
       vOUT_2 :='0'
    else
       vOUT_2 :=EdtOUT_2.Text ;
    if EdtOUT_1.Text='' then
       vOUT_1 :='0'
    else
       vOUT_1 :=EdtOUT_1.Text ;

    sOUT_money :=vOUT_10 + vOUT_9 + vOUT_8 + vOUT_7 +vOUT_6 + vOUT_5 +
                vOUT_4 + vOUT_3 +'.'+ vOUT_2 + vOUT_1 ;
    vOUT_money := StrToFloat(Trim(sOUT_money));

    //原存取款金额
    if  (self.ClientDataSet1.State=dsEdit) then
    begin
      sqlstr:='select IN_money,OUT_money FROM Deposit where ACC_NO='''+EdtACC_NO.Text+
              ''' AND serial_no='''+ DBEdit2.Text +''' ';
      TempAdo := TAdodataSet.Create(nil);
      try
        TempAdo.Connection := DM.ADOConnection1 ;
        TempAdo.CommandText := Sqlstr;
        TempAdo.Prepared := True;
        TempAdo.Open;
        if NOT TempAdo.Eof then
        begin
           vOLD_IN_money :=TempAdo.fieldbyname('in_money').AsFloat ;
           vOLD_out_money :=TempAdo.fieldbyname('out_money').AsFloat ;
        end
        else
        begin
           vOLD_IN_money :=0 ;
           vOLD_out_money:=0 ;
        end ;
      finally
        TempAdo.Free;
      end;
    end ;

    if (self.ClientDataSet1.State=dsInsert) then
    begin
        vOLD_IN_money :=0 ;
        vOLD_out_money:=0 ;
    end ;

    //余额
    //sqlstr:='select Rem_money from Balance where ACC_NO='''+EdtACC_NO.Text+''' ';

    sqlstr:='select  REM_money FROM Deposit INNER JOIN '+
    '(SELECT Acc_no,MAX(serial_no) AS serial_no FROM Deposit where ACC_NO='''+EdtACC_NO.Text
    +''' AND serial_no<'''+DBEdit2.Text +''' GROUP BY Acc_no) A '+
    ' ON Deposit.Acc_no=A.Acc_no AND Deposit.serial_no=A.serial_no';
    TempAdo := TAdodataSet.Create(nil);
    try
      TempAdo.Connection := DM.ADOConnection1 ;
      TempAdo.CommandText := Sqlstr;
      TempAdo.Prepared := True;
      TempAdo.Open;
      if TempAdo.Eof then
         vREM_money :=0
      else
         vREM_money :=TempAdo.fieldbyname('Rem_money').AsFloat ;
    finally
      TempAdo.Free;
    end;

    vREM_money :=vREM_money + vIN_money - vOUT_money ;

    //if (vOUT_money>0) AND (vOUT_money > vREM_money ) then
    if (vOUT_money>0) AND (vRem_money<0) then
    begin
       MessageDlg('对不起，存款余额不足，不能取款！', mtInformation,[mbOk], 0);
       f_main.B_cancel.Click;
       exit ;
    end;

    //vREM_money :=vREM_money - vOLD_IN_money + vIN_money
    //             + vOLD_OUT_money - vOUT_money ;
    self.ClientDataSet1IN_money.Value  := vIN_money ;
    self.ClientDataSet1OUT_money.Value := vOUT_money ;
    self.ClientDataSet1REM_money.Value := vREM_money ;
    if CheckBox1.Checked then
    begin
       self.ClientDataSet1Check_mk.Value :='Y' ;
       self.ClientDataSet1Check_man.Value :=f_main.vuser_nm ; 
    end
    else
       self.ClientDataSet1Check_mk.Value :='' ;

    sqlstr:='select count(*) nn from Deposit where ACC_NO='''+EdtACC_NO.Text +
    ''' and Cdate='''+ MaskEdit1.Text+''' and serial_no='''+DBEdit2.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;

  end;

  try
    //余额为0进行消户
    if vDel AND (vREM_money =0) then
    begin
       if user_func.showans('余额为0，确定要消户吗？') = true then
       begin
          sqlstr:='UPDATE Account SET Cancel_YN=''Y'',Cancel_date='''+
                  MaskEdit1.Text +''' WHERE Acc_no='''+EdtACC_NO.Text +'''';
          sqlstr:='begin tran'#13+Sqlstr
              +'if @@error!=0 begin raiserror(''操作失败!'',16,1) rollback return end'#13
              +'commit tran';
          ADOQpub := TAdoQuery.Create(nil);
          try
             ADOQpub.Connection := DM.ADOConnection1 ;
             ADOQpub.Close ;
             ADOQpub.SQL.Clear ;
             ADOQpub.SQL.Add(Sqlstr);
             ADOQpub.ExecSQL ;
          finally
             ADOQpub.Free;
          end;
       end ;
    end ;

  if (self.ClientDataSet1.State=dsEdit) then
     modify := True
  else
     modify := false ;

  if vDel then modify := True ;

  //保存数据
  inherited;

  //修改存取款数据将自动更新每笔存取款的余额
  if modify then
  begin
     ADOStoredProc1.Close ;
     ADOStoredProc1.Parameters.ParamByName('@Acc_no').Value :=EdtACC_NO.Text ;
     ADOStoredProc1.Parameters.ParamByName('@Serial_no').Value :=DBEdit2.Text ;
     ADOStoredProc1.ExecProc ;
     BK:=self.ClientDataSet1.GetBookmark ;
     BitBtn_query.Click ;
     self.ClientDataSet1.GotoBookMark(BK);
  end ;

  except
    MessageDlg('资料更新失败', mtInformation,[mbOk], 0);
  end ;

  MaskEdit1.Visible :=false;
  DBEdit1.Visible :=true;
  vDel := False ;
end;

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

procedure TfrmDeposit.FormCreate(Sender: TObject);
begin
  inherited;
  frmFirst :=true;
  confirm :=false ;
  ADOAccount.Active :=True ;
  Memo1.Visible :=false ;
end;

procedure TfrmDeposit.FormActivate(Sender: TObject);
begin
  inherited;
  if frmFirst=true then
  begin
     EdtACC_NO.SetFocus ;
     frmFirst :=false;
     MaskEdit1.Visible :=false;
     DBEdit1.Visible :=true;
  end;
end;

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

end;

procedure TfrmDeposit.BitBtn_queryClick(Sender: TObject);
var sqlstr:string;
  vlen: integer ;
  TempAdo : TAdodataSet ;
begin
  inherited;
  if EdtACC_NO.Text =''   then
  begin
     showmessage('请输入储户帐号！');
     EdtACC_NO.SetFocus ;
     exit ;
  end ;
  sqlstr:='SELECT per_na,Cancel_YN,rep_loss_YN  FROM Account '+
     ' WHERE ACC_NO='''+ EdtACC_NO.Text +''' ';
  TempAdo := TAdodataSet.Create(nil);
  try
    TempAdo.Connection := DM.ADOConnection1 ;
    TempAdo.CommandText := Sqlstr;
    TempAdo.Prepared := True;
    TempAdo.Open;
    if not TempAdo.Eof then
    begin
       if TempAdo.FieldByName('Cancel_YN').AsString ='Y' then
       begin
           ShowMessage('此帐号已消户，请重新输入！');
           EdtACC_NO.SetFocus;
           exit;
       end;
       if TempAdo.FieldByName('rep_loss_YN').AsString ='Y' then
       begin
           ShowMessage('此帐号已挂失，请重新输入！');
           EdtACC_NO.SetFocus;
           exit;
       end;
    end ;
  finally
    TempAdo.Free ;
  end;

  if edtyymmdd.text ='    -  -  ' then
     vlen := 0
  else if trim(copy(edtyymmdd.text,9,2))='' then
     vlen := 7
  else
     vlen := 10 ;

  sqlstr :='';
  sqlstr :=sqlstr +' and Acc_no='''+edtAcc_no.Text +'''' ;
  if vlen =7 then
     sqlstr := sqlstr+' and LEFT(Cdate,7)='''+ COPY(Edtyymmdd.Text,1,7)+'''';
  if vlen =10 then
     sqlstr := sqlstr+' and Cdate='''+ Edtyymmdd.Text+'''' ;

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

  sqlstr:='SELECT * FROM Deposit '+ sqlstr+' ORDER  BY serial_no ';
  self.ClientDataSet1.Active :=false;
  self.ADODataSet1.CommandText:=sqlstr;
  self.ClientDataSet1.Active :=true;
  self.ClientDataSet1.Last ;

  self.Memo1.Visible :=false;
  self.DBGrid1.Visible :=true;

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

procedure TfrmDeposit.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  inherited;
  self.ADODataSet1.Free ;
  self.ClientDataSet1.Free ;
  self.DataSetProvider1.Free ;
  ADOAccount.Active :=false ;
  frmDeposit :=nil;
end;

procedure TfrmDeposit.MaskEdit1Exit(Sender: TObject);
var
  sqlstr,vnum,vp_date:string;
  nn:integer;
  TempAdo :TAdodataSet ;
begin
  inherited;
  if self.mlog  =mcancel then abort ;
  if f_main.ActiveControl =f_main.B_cancel then abort ;
  if not isdate(MaskEdit1.Text) then
  begin
     showmessage('日期输入错误，请重新输入!');
     MaskEdit1.SetFocus ;
     exit ;
  end ;

  //读取最后一个交易日期
  if (self.ClientDataSet1.State=dsInsert) then
  begin
    TempAdo := TAdodataSet.Create(nil);
    sqlstr := 'select max(Cdate) cdate from Deposit where ACC_NO='''+EdtACC_NO.Text+'''' ;
    try
      TempAdo.Connection := DM.ADOConnection1 ;
      TempAdo.CommandText := Sqlstr;
      TempAdo.Prepared := True;
      TempAdo.Open;
      if not TempAdo.eof then
         vp_date := TempAdo.fieldbyname('Cdate').AsString
      else
         vp_date :=f_main.vTodayDate ;
    finally
      TempAdo.Free;
    end;
    if MaskEdit1.Text < vp_date  then
    begin
       showmessage('日期输入错误，请重新输入!');
       //MaskEdit1.SetFocus ;
       f_main.B_cancel.Click ;
       exit ;
    end ;
  end ;

  DBEdit1.Text :=MaskEdit1.Text ;
  //产生流水号
  if (self.ClientDataSet1.State=dsInsert) then
  begin
    TempAdo := TAdodataSet.Create(nil);
    sqlstr := 'select max(serial_no) dno, CONVERT(int,substring(max(serial_no),9,2)) nn '
       +'from Deposit  where ACC_NO='''+EdtACC_NO.Text+''' AND Cdate='''+
        MaskEdit1.Text+'''' ;
    try
      TempAdo.Connection := DM.ADOConnection1 ;
      TempAdo.CommandText := Sqlstr;
      TempAdo.Prepared := True;
      TempAdo.Open;

      nn := TempAdo.fieldbyname('nn').Asinteger ;
      vnum := copy(MaskEdit1.Text,1,4)+copy(MaskEdit1.Text,6,2)
         +copy(MaskEdit1.Text,9,2)+right('00'+trim(IntToStr(nn+1)),2) ;

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

end;

procedure TfrmDeposit.EdtACC_NOChange(Sender: TObject);
var sqlstr:string;