ufrminterrest.pas

小银行系统

unit UfrmInterRest;

interface

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

type
  TfrmInterRest = class(TForm)
    PanTop: TPanel;
    Label5: TLabel;
    Label23: TLabel;
    Label24: TLabel;
    Label9: TLabel;
    BitBtn_query: TBitBtn;
    Edtper_na: TEdit;
    BitBtn1: TBitBtn;
    EdtStartyymm: TMaskEdit;
    EdtACC_NO: TEdit;
    BitBtn_Print: TBitBtn;
    EdtEndyymm: TMaskEdit;
    BitBtnCal: TBitBtn;
    Grid2: TDBGridEh;
    ScrollBox1: TScrollBox;
    Memo1: TMemo;
    Grid1: TDBGridEh;
    ADODataSet1: TADODataSet;
    DataSetProvider1: TDataSetProvider;
    ClientDataSet1: TClientDataSet;
    DataSource1: TDataSource;
    ADODataSet2: TADODataSet;
    DataSetProvider2: TDataSetProvider;
    ClientDataSet2: TClientDataSet;
    DataSource2: TDataSource;
    ClientDataSet1Start_yymm: TStringField;
    ClientDataSet1End_yymm: TStringField;
    ClientDataSet1Acc_no: TStringField;
    ClientDataSet1Interest_Money: TBCDField;
    ClientDataSet1Not_Mature: TStringField;
    ClientDataSet1Per_na: TStringField;
    ClientDataSet1Per_no: TStringField;
    ClientDataSet1fac_no: TStringField;
    ClientDataSet1sec_no: TStringField;
    ClientDataSet1fac_nm: TStringField;
    ClientDataSet1sec_nm: TStringField;
    ClientDataSet2INT_1: TStringField;
    ClientDataSet2INT_2: TStringField;
    ClientDataSet2INT_3: TStringField;
    ClientDataSet2INT_4: TStringField;
    ClientDataSet2INT_5: TStringField;
    ClientDataSet2INT_6: TStringField;
    ClientDataSet2INT_7: TStringField;
    ClientDataSet2INT_8: TStringField;
    ADOStoredProc1: TADOStoredProc;
    ClientDataSet2In_Money: TBCDField;
    ClientDataSet2Out_Money: TBCDField;
    ClientDataSet2Rem_Money: TBCDField;
    ClientDataSet2TotBase_Money: TBCDField;
    ClientDataSet2Interest_Money: TBCDField;
    ClientDataSet2Start_yymm: TStringField;
    ClientDataSet2End_yymm: TStringField;
    ClientDataSet2Acc_no: TStringField;
    ClientDataSet2Cdate: TStringField;
    ClientDataSet2serial_no: TStringField;
    ClientDataSet2Days: TIntegerField;
    ClientDataSet2Base_Money: TBCDField;
    ClientDataSet2IN_1: TStringField;
    ClientDataSet2IN_2: TStringField;
    ClientDataSet2IN_3: TStringField;
    ClientDataSet2IN_4: TStringField;
    ClientDataSet2IN_6: TStringField;
    ClientDataSet2IN_5: TStringField;
    ClientDataSet2IN_7: TStringField;
    ClientDataSet2IN_8: TStringField;
    ClientDataSet2OUT_1: TStringField;
    ClientDataSet2OUT_2: TStringField;
    ClientDataSet2OUT_3: TStringField;
    ClientDataSet2OUT_4: TStringField;
    ClientDataSet2OUT_5: TStringField;
    ClientDataSet2OUT_6: TStringField;
    ClientDataSet2OUT_7: TStringField;
    ClientDataSet2OUT_8: TStringField;
    ClientDataSet2R_1: TStringField;
    ClientDataSet2R_2: TStringField;
    ClientDataSet2R_3: TStringField;
    ClientDataSet2R_4: TStringField;
    ClientDataSet2R_5: TStringField;
    ClientDataSet2R_6: TStringField;
    ClientDataSet2R_7: TStringField;
    ClientDataSet2R_8: TStringField;
    DosMove1: TDosMove;
    Label1: TLabel;
    MaskEditLast_date: TMaskEdit;
    ClientDataSet1Pre_rest_Money: TBCDField;
    procedure BitBtn1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure EdtStartyymmExit(Sender: TObject);
    procedure BitBtnCalClick(Sender: TObject);
    procedure BitBtn_queryClick(Sender: TObject);
    procedure Grid1MouseUp(Sender: TObject; Button: TMouseButton;
      Shift: TShiftState; X, Y: Integer);
    procedure Grid1KeyUp(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure ClientDataSet2CalcFields(DataSet: TDataSet);
    procedure BitBtn_PrintClick(Sender: TObject);
  private
    { Private declarations }
    procedure refresh1;
  public
    { Public declarations }
  end;

var
  frmInterRest: TfrmInterRest;
  frmFirst,confirm :boolean;

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

{$R *.dfm}

procedure TfrmInterRest.BitBtn1Click(Sender: TObject);
begin
  close ;

end;

procedure TfrmInterRest.FormCreate(Sender: TObject);
begin
  frmFirst :=true;
  confirm :=false ;

end;

procedure TfrmInterRest.FormActivate(Sender: TObject);
begin
  if frmFirst=true then
  begin
     EdtStartyymm.SetFocus ;
     frmFirst :=false;
  end;
end;

procedure TfrmInterRest.EdtStartyymmExit(Sender: TObject);
begin
  if (copy(EdtStartyymm.Text,6,2)<>'01') and
     (copy(EdtStartyymm.Text,6,2)<>'04') and
     (copy(EdtStartyymm.Text,6,2)<>'07') and
     (copy(EdtStartyymm.Text,6,2)<>'10') then
  begin
     showmessage('请输入正确的利息年月！');
     EdtStartyymm.SetFocus ;
     exit ;
  end ;

  EdtEndyymm.Text:=COPY(DateToStr(StrToDate(EdtStartyymm.Text+'-01')+70),1,7);

end;

procedure TfrmInterRest.BitBtnCalClick(Sender: TObject);
begin
  Screen.Cursor:=crHourGlass;
  ADOStoredProc1.Active :=false ;
  ADOStoredProc1.Parameters.ParamByName('@start_yymm').Value :=EdtStartyymm.Text;
  ADOStoredProc1.Parameters.ParamByName('@end_yymm').Value :=EdtEndyymm.Text;
  ADOStoredProc1.Parameters.ParamByName('@Acc_no1').Value :=EdtACC_NO.Text;
  ADOStoredProc1.Parameters.ParamByName('@Acc_no2').Value :=EdtACC_NO.Text;
  ADOStoredProc1.Parameters.ParamByName('@Last_date').Value :=MaskEditLast_date.Text ;
  ADOStoredProc1.Active :=true ;
  Screen.Cursor := crArrow;
  
  BitBtn_query.Click ;
end;

procedure TfrmInterRest.BitBtn_queryClick(Sender: TObject);
var sqlstr:string;
begin
  IF (EdtStartyymm.Text ='    -  ') or
     (EdtEndyymm.Text ='    -  ') then
  begin
     showmessage('请输入利息年月！');
     EdtStartyymm.SetFocus ;
     exit ;
  end ;

  sqlstr :='';
  if EdtACC_NO.Text <>''   then
     sqlstr :=sqlstr +' and A.Acc_no='''+edtAcc_no.Text +'''' ;

  sqlstr :=sqlstr +' and A.Start_yymm='''+EdtStartyymm.Text +'''' ;
  sqlstr :=sqlstr +' and A.End_yymm='''+EdtEndyymm.Text +'''' ;
  sqlstr := copy(sqlstr,6,length(sqlstr)-5);
  if trim(sqlstr)<>'' then
     sqlstr := ' where '+sqlstr ;

  sqlstr :='SELECT A.*,B.Per_na,B.Per_no,B.fac_no,B.sec_no,C.fac_nm,D.sec_nm '+
  ' FROM InterestMaster  A '+
  ' INNER JOIN Account  B ON A.Acc_no = B.Acc_no '+
  ' INNER JOIN factory  C ON B.fac_no = C.fac_no '+
  ' INNER JOIN Sec  D ON B.sec_no = D.Sec_no '+ sqlstr+
  ' ORDER  BY  A.Acc_no ' ;

  self.ClientDataSet1.Active :=false;
  ADODataSet1.CommandText :=sqlstr;
  self.ClientDataSet1.Active :=true;
  
  self.refresh1 ;

end;

procedure TfrmInterRest.refresh1;
var sqlstr:string;
begin
   sqlstr:='SELECT * FROM InterestDetail where start_yymm='''+EdtStartyymm.Text+
   ''' and end_yymm='''+EdtEndyymm.Text +''' and acc_no='''+
   self.ClientDataSet1Acc_no.Value +''' ORDER BY Cdate, serial_no ';

   self.ClientDataSet2.Active :=false ;
   ADODataSet2.CommandText :=sqlstr;
   self.ClientDataSet2.Active :=true;
   
end;

procedure TfrmInterRest.Grid1MouseUp(Sender: TObject; Button: TMouseButton;
  Shift: TShiftState; X, Y: Integer);
begin
  self.refresh1 ;
end;

procedure TfrmInterRest.Grid1KeyUp(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if (key=40) or (key=38) then
     self.refresh1 ;

end;

procedure TfrmInterRest.ClientDataSet2CalcFields(DataSet: TDataSet);
var
  //小数点位置，小数点前位数，小数点后位数
  nPos,nLen,dLen:integer;
  sIN_money,nIN_money,sOUT_money,nOUT_money,srem_money,nrem_money,
  SInterest_money,nInterest_money :string;
begin
   inherited;
   ////////////////////////////////////////////////////////////////
   //存款部分
   //存款 角分元...
   if self.ClientDataSet2In_Money.AsFloat >0 then
   begin
       sIN_money := Trim(floattostr(self.ClientDataSet2IN_money.AsFloat));
       nPos :=Pos('.',sIN_money);
       if nPos>0 then
          dLen :=Length(Trim(COPY(sIN_money,nPos+1,2)))   //小数位数
       else
          dLen :=0 ;
       if nPos>0 then
          nIN_money := Trim(Copy(sIN_money,1,nPos-1))
       else
          nIN_money := sIN_money ;
       nIN_money :=nIN_money ;
       nLen :=Length(Trim(nIN_money)) ;  //整数位数


       //小数部分
       if dLen>0 then
          self.ClientDataSet2IN_2.Value := COPY(sIN_money,nPos+1,1)
       else
          if nLen>0 then
             self.ClientDataSet2IN_2.Value := '0' ;

       if dLen>1 then
          self.ClientDataSet2IN_1.Value := COPY(sIN_money,nPos+2,1)
       else
          if nLen>0 then
             self.ClientDataSet2IN_1.Value := '0' ;


       //整数部分
       if nLen >0 then
          self.ClientDataSet2IN_3.Value := COPY(nIN_money,nLen,1) ;
       if nLen >1 then
          self.ClientDataSet2IN_4.Value := COPY(nIN_money,nLen-1,1) ;
       if nLen >2 then
          self.ClientDataSet2IN_5.Value := COPY(nIN_money,nLen-2,1) ;
       if nLen >3 then
          self.ClientDataSet2IN_6.Value := COPY(nIN_money,nLen-3,1) ;
       if nLen >4 then
          self.ClientDataSet2IN_7.Value := COPY(nIN_money,nLen-4,1) ;
       if nLen >5 then
          self.ClientDataSet2IN_8.Value := COPY(nIN_money,nLen-5,1) ;
   end ;

   ////////////////////////////////////////////////////////////////
   //取款部分
   if self.ClientDataSet2OUT_money.AsFloat >0 then
   begin
       sOUT_money := Trim(floattostr(self.ClientDataSet2OUT_money.AsFloat));
       nPos :=Pos('.',sOUT_money);
       if nPos>0 then
          dLen :=Length(Trim(COPY(sOUT_money,nPos+1,2)))   //小数位数
       else
          dLen :=0 ;
       if nPos>0 then
          nOUT_money := Trim(Copy(sOUT_money,1,nPos-1))
       else
          nOUT_money := sOUT_money ;
       nLen :=Length(Trim(nOUT_money)) ;  //整数位数

       //小数部分
       if dLen>0 then
          self.ClientDataSet2OUT_2.Value := COPY(sOUT_money,nPos+1,1)
       else
          if nLen>0 then
             self.ClientDataSet2OUT_2.Value := '0' ;

       if dLen>1 then
          self.ClientDataSet2OUT_1.Value := COPY(sOUT_money,nPos+2,1)
       else
          if nLen>0 then
             self.ClientDataSet2OUT_1.Value := '0' ;

       //整数部分
       if nLen >0 then
          self.ClientDataSet2OUT_3.Value := COPY(nOUT_money,nLen,1) ;
       if nLen >1 then
          self.ClientDataSet2OUT_4.Value := COPY(nOUT_money,nLen-1,1) ;
       if nLen >2 then
          self.ClientDataSet2OUT_5.Value := COPY(nOUT_money,nLen-2,1) ;
       if nLen >3 then
          self.ClientDataSet2OUT_6.Value := COPY(nOUT_money,nLen-3,1) ;
       if nLen >4 then
          self.ClientDataSet2OUT_7.Value := COPY(nOUT_money,nLen-4,1) ;
       if nLen >5 then
          self.ClientDataSet2OUT_8.Value := COPY(nOUT_money,nLen-5,1) ;
   end ;

   /////////////////////////////////////////////////////////////////
   //余额部分
   //存款 角分元...
   if self.ClientDataSet2REM_money.AsFloat >0 then
   begin
       sREM_money := Trim(floattostr(self.ClientDataSet2REM_money.AsFloat));
       nPos :=Pos('.',sREM_money);
       if nPos>0 then
          dLen :=Length(Trim(COPY(sREM_money,nPos+1,2)))   //小数位数
       else
          dLen :=0 ;
       if nPos>0 then
          nRem_money := Trim(Copy(sRem_money,1,nPos-1))
       else
          nRem_money := sRem_money ;
       nLen :=Length(Trim(nRem_money)) ;  //整数位数

       //小数部分
       if dLen>0 then
          self.ClientDataSet2R_2.Value := COPY(sRem_money,nPos+1,1)
       else
          self.ClientDataSet2R_2.Value := '0' ;

       if dLen>1 then
          self.ClientDataSet2R_1.Value := COPY(sRem_money,nPos+2,1)
       else
          self.ClientDataSet2R_1.Value := '0' ;


       //整数部分
       if nLen >0 then
          self.ClientDataSet2R_3.Value := COPY(nRem_money,nLen,1) ;
       if nLen >1 then
          self.ClientDataSet2R_4.Value := COPY(nRem_money,nLen-1,1) ;
       if nLen >2 then
          self.ClientDataSet2R_5.Value := COPY(nRem_money,nLen-2,1) ;
       if nLen >3 then
          self.ClientDataSet2R_6.Value := COPY(nRem_money,nLen-3,1) ;
       if nLen >4 then
          self.ClientDataSet2R_7.Value := COPY(nRem_money,nLen-4,1) ;
       if nLen >5 then
          self.ClientDataSet2R_8.Value := COPY(nRem_money,nLen-5,1) ;
   end ;

   /////////////////////////////////////////////////////////////////
   //利息部分
   if self.ClientDataSet2Interest_Money.AsFloat >0 then
   begin
       sInterest_money := Trim(floattostr(self.ClientDataSet2Interest_money.AsFloat));
       nPos :=Pos('.',sInterest_money);
       if nPos>0 then
          dLen :=Length(Trim(COPY(sInterest_money,nPos+1,2)))   //小数位数
       else
          dLen :=0 ;
       if nPos>0 then
          nInterest_money := Trim(Copy(sInterest_money,1,nPos-1))
       else
          nInterest_money := sInterest_money ;
       nLen :=Length(Trim(nInterest_money)) ;  //整数位数

       //小数部分
       if dLen>0 then
          self.ClientDataSet2INT_2.Value := COPY(sInterest_money,nPos+1,1)
       else
          self.ClientDataSet2INT_2.Value := '0' ;

       if dLen>1 then
          self.ClientDataSet2INT_1.Value := COPY(sInterest_money,nPos+2,1)
       else
          self.ClientDataSet2INT_1.Value := '0' ;


       //整数部分
       if nLen >0 then
          self.ClientDataSet2INT_3.Value := COPY(nInterest_money,nLen,1) ;
       if nLen >1 then
          self.ClientDataSet2INT_4.Value := COPY(nInterest_money,nLen-1,1) ;
       if nLen >2 then
          self.ClientDataSet2INT_5.Value := COPY(nInterest_money,nLen-2,1) ;
       if nLen >3 then
          self.ClientDataSet2INT_6.Value := COPY(nInterest_money,nLen-3,1) ;
       if nLen >4 then
          self.ClientDataSet2INT_7.Value := COPY(nInterest_money,nLen-4,1) ;
       if nLen >5 then
          self.ClientDataSet2INT_8.Value := COPY(nInterest_money,nLen-5,1) ;
   end ;

end;

procedure TfrmInterRest.BitBtn_PrintClick(Sender: TObject);
var sqlstr,sTitle:string;
begin
  inherited;
  sqlstr:='';
  sTitle :=EdtStartyymm.Text +'至'+EdtEndyymm.Text +'利息表';
  //预览报表
  with TfrmReport.Create(nil) do
  begin
    //改变光标
    Screen.Cursor := crHourGlass;
    F1Report.ClearClipboard;
    Print_InterRest('利息表.vts',sTitle,sqlstr,ClientDataSet1);
    F1Report.ClearClipboard;
    Screen.Cursor := crArrow;
    F1Report.AllowSelections := false;
    ShowModal;
    free;
  end;    // with

end;

end.