Разработка программного модуля начисления процентов

    Label13: TLabel;

    ButtonPrint: TButton;

    ButtonCalculate: TButton;

    Panel2: TPanel;

    DBNavigator1: TDBNavigator;

    ButtonRefreshTariffs: TButton;

    Panel3: TPanel;

    DBNavigator2: TDBNavigator;

    ButtonRefreshInvestors: TButton;

    GroupBox3: TGroupBox;

    StringGridCalc: TStringGrid;

    Panel4: TPanel;

    DBNavigator3: TDBNavigator;

    ButtonRefreshAccounts: TButton;

    ButtonAdd: TButton;

    ADOQueryADD: TADOQuery;

    procedure FormCreate(Sender: TObject);

    procedure ButtonRefreshTariffsClick(Sender: TObject);

    procedure ButtonRefreshInvestorsClick(Sender: TObject);

    procedure ButtonRefreshAccountsClick(Sender: TObject);

    procedure ButtonCalculateClick(Sender: TObject);

    procedure ComboBox1Select(Sender: TObject);

    procedure DateTimePicker1Change(Sender: TObject);

    procedure SpinEdit2Change(Sender: TObject);

    procedure DateTimePicker2Change(Sender: TObject);

    procedure GroupBox1MouseDown(Sender: TObject; Button: TMouseButton;

      Shift: TShiftState; X, Y: Integer);

    procedure GroupBox1MouseMove(Sender: TObject; Shift: TShiftState; X,

      Y: Integer);

    procedure SpinEdit1Change(Sender: TObject);

    procedure FloatSpinEdit1EdtChange(Sender: TObject);

    procedure ComboBox2Change(Sender: TObject);

    procedure FloatSpinEdit2EdtChange(Sender: TObject);

    procedure FloatSpinEdit3EdtChange(Sender: TObject);

    procedure StringGridDepDrawCell(Sender: TObject; ACol, ARow: Integer;

      Rect: TRect; State: TGridDrawState);

    procedure ButtonPrintClick(Sender: TObject);

    procedure CheckBoxCyrrencyClick(Sender: TObject);

    procedure CheckBoxTariffClick(Sender: TObject);

    procedure StringGridDrawCell(Sender: TObject; ACol, ARow: Integer;

      Rect: TRect; State: TGridDrawState);

    procedure RadioGroupCalculateClick(Sender: TObject);

    procedure ButtonAddClick(Sender: TObject);

  private

    { Private declarations }

  public

    { Public declarations }

  end;

type

  TAccounts=record

      Viplat : array[1..999] of Integer;

      AccountID : integer;

      Summa : real;

      Balance :real;

      Length : integer;

    end;

var

  MainForm: TMainForm;

  CONECT_STR :String;

  IDChet,FIO,Passport,INN,Reg, DateR,Inf,Val:string;

implementation

uses UReport, UnitRepDep;

{$R *.dfm}

procedure TMainForm.ButtonRefreshTariffsClick(Sender: TObject);

begin

ADOQTariffs.Active :=false;

ADOQTariffs.Active :=true;

end;

procedure TMainForm.ButtonRefreshInvestorsClick(Sender: TObject);

begin

ADOQInvestors.Active :=false;

ADOQInvestors.Active :=true;

end;

procedure TMainForm.ButtonRefreshAccountsClick(Sender: TObject);

begin

ADOQAccounts.Active :=false;

ADOQAccounts.Active :=true;

end;

procedure TMainForm.ButtonCalculateClick(Sender: TObject);

var

str,timeBegin,timeEnd,accountID,cyrrency,periodicity : string;

time1,time2: TDateTime;

i,countM:integer;

n:integer;

Balance,Summa,Rate, BalanceRef,RateRef,sum,Summa1:real;

Year, Month, Day :Word;

st0,st1:char;

begin

  For i:=0 to StringGrid.RowCount-1 do

StringGrid.Rows[i].Clear;

  if RadioGroupCalculate.ItemIndex = 0 then

  begin

     if  CheckBoxCyrrency.Checked = true  then

   begin

    if  DBLookupComboBoxCyrrency.Text = ''

     then

      cyrrency:= '1'

     else

      cyrrency := DBLookupComboBoxCyrrency.KeyValue

   end;

     StringGrid.Cells[0,0]:='№ Счёта';

     StringGrid.Cells[1,0]:='Дата открытия'#13'счёта';

     StringGrid.Cells[2,0]:='Дата закрытия'#13'счёта';

     StringGrid.Cells[3,0]:='Начальный'#13'остаток';

     StringGrid.Cells[4,0]:='Процентная'#13'ставка (%)';

     StringGrid.Cells[5,0]:='Начисление'#13'процентов';

     StringGrid.Cells[6,0]:='Конечный'#13'остаток';

     StringGrid.Cells[7,0]:='Описание';

   timeEnd := DateTimeToStr(DateTimePickerEnd.Date);

   for i:=0 to  length(timeEnd) do

   if timeEnd[i] = '.' then timeEnd[i]:='-';

    st0:=timeEnd[1];

    st1:=timeEnd[2];

    timeEnd[1]:=timeEnd[4];

    timeEnd[2]:=timeEnd[5];

    timeEnd[4]:=st0;

    timeEnd[5]:=st1;

  Delete(timeEnd, 11,10);

  timeEnd := '#' + timeEnd + '#';

  ADOQTime.SQL.Clear;

if CheckBoxTime.Checked = true then

  begin

  if CheckBoxCyrrency.Checked = true

  then

    str := 'SELECT AccountID,DataBegin,DataEnd,Summa,Balance,Tariffs.TariffName,Rate,Length,Periodicity,CurrencyID '+

  ' FROM Accounts inner join Tariffs on Accounts.TariffName=Tariffs.TariffName Where (DataBegin <='+timeEnd+')  and (CurrencyID='+cyrrency+')'

  else

   begin

  if CheckBoxTariff.Checked = true then

  str := 'SELECT AccountID,DataBegin,DataEnd,Summa,Balance,Tariffs.TariffName,Rate,Length,Periodicity,CurrencyID '+

  ' FROM Accounts inner join Tariffs on Accounts.TariffName=Tariffs.TariffName Where (DataBegin <='+timeEnd+' ) and (Tariffs.TariffName='''+DBLookupComboBoxTariff.Text+''')'

  else

  str := 'SELECT AccountID,DataBegin,DataEnd,Summa,Balance,Tariffs.TariffName,Rate,Length,Periodicity,CurrencyID '+

  ' FROM Accounts inner join Tariffs on Accounts.TariffName=Tariffs.TariffName Where DataBegin <='+timeEnd;

  end

  end

  else

   begin

    if CheckBoxCyrrency.Checked = true

  then

    str := 'SELECT AccountID,DataBegin,DataEnd,Summa,Balance,Tariffs.TariffName,Rate,Length,Periodicity,CurrencyID '+

  ' FROM Accounts inner join Tariffs on Accounts.TariffName=Tariffs.TariffName Where CurrencyID='+cyrrency

  else

   if CheckBoxTariff.Checked = true then

     str := 'SELECT AccountID,DataBegin,DataEnd,Summa,Balance,Tariffs.TariffName,Rate,Length,Periodicity,CurrencyID '+

  ' FROM Accounts inner join Tariffs on Accounts.TariffName=Tariffs.TariffName Where Tariffs.TariffName='''+DBLookupComboBoxTariff.Text+''''

   else

  str := 'SELECT AccountID,DataBegin,DataEnd,Summa,Balance,Tariffs.TariffName,Rate,Length,Periodicity,CurrencyID '+

  ' FROM Accounts inner join Tariffs on Accounts.TariffName=Tariffs.TariffName ';

   end ;

  ADOQTime.SQL.Add(str);

  ADOQTime.ExecSQL;

  ADOQTime.close;

  ADOQTime.open;

  if ADOQTime.RecordCount>=1  then

    StringGrid.RowCount:=ADOQTime.RecordCount+1

  else

   StringGrid.RowCount :=2;

  ADOTableNumDeposit.Active :=true;

  n:=0;

  ADOQTime.First;

  while not ADOQTime.Eof do

   begin

      Val := ' руб.';

    if ADOQTime.FieldByName('CurrencyID').AsString = '2'

     then Val := ' $';

     if ADOQTime.FieldByName('CurrencyID').AsString = '3'

      then Val := ' €';

    n:=n+1;

    StringGrid.Cells[0,n]:=ADOQTime.FieldByName('AccountID').AsString;

    StringGrid.Cells[1,n]:=ADOQTime.FieldByName('DataBegin').AsString;

    StringGrid.Cells[2,n]:=ADOQTime.FieldByName('DataEnd').AsString;

    StringGrid.Cells[3,n]:=ADOQTime.FieldByName('Summa').AsString+Val;

    StringGrid.Cells[4,n]:=ADOQTime.FieldByName('Rate').AsString+'%';

    StringGrid.Cells[5,n]:=ADOQTime.FieldByName('Periodicity').AsString;

    RateRef:=13;

    if (ADOQTime.FieldByName('CurrencyID').AsString = '2') or (ADOQTime.FieldByName('CurrencyID').AsString = '3')

     then

      RateRef:=9;

    if (ADOQTime.FieldByName('DataEnd').AsDateTime <DateTimePickerEnd.Date) or (CheckBoxTime.Checked = false)

     then

      begin

       countM:= ADOQTime.FieldByName('Length').AsInteger;

      // time1:=ADOQTime.FieldByName('DataBegin').AsDateTime;

      // time2:=ADOQTime.FieldByName('DataEnd').AsDateTime;

      // countD:=daysBetween(time2,time1);

       //edit1.Text :=IntToStr (countD)

      end

     else

      begin

        time1:=ADOQTime.FieldByName('DataBegin').AsDateTime;

        time2:=DateTimePickerEnd.Date;

        countM:= MonthsBetween(time1,time2);

         //countD:=daysBetween(time1,time2);

        // edit1.Text :=IntToStr (countD);

        //edit1.Text :=IntToStr (countM)

      end ;

   Summa :=ADOQTime.FieldByName('Summa').AsFloat;

   Rate := ADOQTime.FieldByName('Rate').AsFloat;

   if ADOQTime.FieldByName('Periodicity').AsString = 'ежемесячно'

    then

     begin

     // Balance := Summa *exp(Ln(1+(Rate*(365/12))/36500)*countM);

      if Rate>Rateref  then

       Balance := Summa *exp(Ln(1+(Rate*(365/12))/36500 - 0.35*((Rate*(365/12))/36500-(RateRef*(365/12))/36500))*countM)

      else

       Balance := Summa *exp(Ln(1+(Rate*(365/12))/36500)*countM);

     { balanceRef := Summa *exp(Ln(1+(RateRef*(365/12))/36500)*countM)-Summa;

      If   Balance-Summa> balanceRef then

        begin

          BalanceRef:= (Balance-Summa-balanceRef)*0.35;

          Balance :=Balance -BalanceRef;

        end }

     end;

    if ADOQTime.FieldByName('Periodicity').AsString = 'ежеквартально' then

     begin

                     if countM<3 then

                      begin

                       Balance := Summa+Summa * ((Rate * countM*(365/12)) / (36500));

                        balanceRef := Summa * ((RateRef * countM*(365/12)) / (36500));

                       If   Balance-Summa> balanceRef then

                        begin

                         BalanceRef:= (Balance-Summa-balanceRef)*0.35;

                         Balance :=Balance -BalanceRef;

                        end

                      end

                      else

                       begin

                        if Rate>RateRef then

                           Balance := Summa *exp(Ln(1+(Rate*3*(365/12))/36500 - 0.35*((Rate*3*(365/12))/36500-(RateRef*3*(365/12))/36500))*(countM div 3))

                        else

                           Balance := Summa *exp(Ln(1+(Rate*3*(365/12))/36500)*(countM div 3));

                        Summa1:=Summa;

                        Summa:=Balance;

                        Balance := Summa+Summa*((Rate*(countM mod 3)*(365/12))/(36500));

                       balanceRef := Summa * ((RateRef * (countM mod 3)*(365/12)) / (36500));

                       If   Balance-Summa> balanceRef then

                        begin

                         BalanceRef:= (Balance-Summa-balanceRef)*0.35;

                         Balance :=Balance -BalanceRef;

                        end;

                         Summa:=Summa1;

                       end

                    end ;

     if ADOQTime.FieldByName('Periodicity').AsString ='ежегодно' then begin

                     if countM<12 then

                      begin

                        Balance := Summa+Summa * ((Rate * countM*(365/12)) / (36500));

                        balanceRef := Summa * ((RateRef * countM*(365/12)) / (36500));

                       If   Balance-Summa> balanceRef then

                        begin

                         BalanceRef:= (Balance-Summa-balanceRef)*0.35;

                         Balance :=Balance -BalanceRef;

                        end

                      end

                      else

                       begin

                       if Rate>RateRef then

                         Balance := Summa *exp(Ln(1+(Rate)/100-0.35*((Rate)/100-(RateRef)/100))*(countM div 12))

                        else

                        Balance := Summa *exp(Ln(1+(Rate)/100)*(countM div 12));

                        Summa1:=Summa;

                        Summa:=Balance;

                        Balance := Balance+Balance*((Rate*(countM mod 12)*(365/12))/(36500));

                        balanceRef := Summa * ((RateRef * (countM mod 12)*(365/12)) / (36500));

                        If   Balance-Summa> balanceRef then

                        begin

                         BalanceRef:= (Balance-Summa-balanceRef)*0.35;

                         Balance :=Balance -BalanceRef;

                        end;

                         Summa:=Summa1;

                       end

                    end;

    if ADOQTime.FieldByName('Periodicity').AsString ='в конце срока'

     then

      begin

       Balance := Summa+Summa * ((Rate * countM*(365/12)) / (36500));

      balanceRef := Summa * ((RateRef * countM*(365/12)) / (36500));

      If   Balance-Summa> balanceRef then

        begin

          BalanceRef:= (Balance-Summa-balanceRef)*0.35;

          Balance :=Balance -BalanceRef;

        end

      end;

  Balance:=round(Balance*100)/100;

  StringGrid.Cells[6,n]:=FloatToStr(Balance)+Val;

  Summa:= Balance-Summa;

  Summa:= round((Summa)*100)/100;

  StringGrid.Cells[7,n]:='Начисленно ' + FloatToStr(Summa)+Val +' за '+IntToStr(countM)+' месяц(-a,-ев)';

  //x^y - это exp(y*ln(x))

  ADOQTime.Next

  end;

end;

  if RadioGroupCalculate.ItemIndex = 1 then

  begin

  accountID := DBLookupComboBoxNumDeposit.text;

   ADOQTime.SQL.Clear;

  str := 'SELECT AccountID,DataBegin,DataEnd,Summa,Balance,Rate,Length,Periodicity,CurrencyID'+

  ' FROM Accounts inner join Tariffs on Accounts.TariffName=Tariffs.TariffName Where AccountID ='+accountID;

  ADOQTime.SQL.Add(str);

  //edit1.Text := str;

  ADOQTime.ExecSQL;

  ADOQTime.close;

  ADOQTime.open;

     Val := ' руб.';

    if ADOQTime.FieldByName('CurrencyID').AsString = '2'

     then Val := ' $';

     if ADOQTime.FieldByName('CurrencyID').AsString = '3'

      then Val := ' €';

    Rate := ADOQTime.FieldByName('Rate').AsFloat;

    Summa :=ADOQTime.FieldByName('Summa').AsFloat;

    countM:= ADOQTime.FieldByName('Length').AsInteger;

    time1:=ADOQTime.FieldByName('DataBegin').AsDateTime;

    periodicity := ADOQTime.FieldByName('Periodicity').AsString;

    RateRef:=13;

    if (ADOQTime.FieldByName('CurrencyID').AsString = '2') or (ADOQTime.FieldByName('CurrencyID').AsString = '3')

     then

      RateRef:=9;

      StringGrid.RowCount := countM+2;

     StringGrid.Cells[0,0]:='№';

     StringGrid.Cells[1,0]:='Дата';

     StringGrid.Cells[2,0]:='Остаток на'#13'начало месяца';

     StringGrid.Cells[3,0]:='Начисленно'#13'процентов';

     StringGrid.Cells[4,0]:='Налогооблага-'#13'емый доход';

     StringGrid.Cells[5,0]:='Сумма'#13'налога';

     StringGrid.Cells[6,0]:='Чистая'#13'прибыль';

     StringGrid.Cells[7,0]:='Сумма'#13'сбережений';

     For n:=1 to  countM do

      begin

        BalanceRef:=0;

       StringGrid.Cells[0,n]:=intToStr(n);

       StringGrid.Cells[2,n]:=FloatToStr(Summa)+Val;;

       if  periodicity = 'ежемесячно' then

        begin

         Balance := (Summa * Rate * (365/12)) / (36500);

         BalanceRef := (Summa * RateRef * (365/12)) / (36500);

        end;

       if  periodicity = 'ежеквартально' then

         if n mod 3=0 then

           begin

            Balance := (Summa * Rate * 3*(365/12)) / (36500);

            BalanceRef := (Summa * RateRef * 3*(365/12)) / (36500);

           end;

       if  periodicity =  'ежегодно' then

         if n mod 12=0 then

           begin

            Balance := (Summa * Rate) / (100);

            BalanceRef := (Summa * RateRef) / (100);

           end ;

       Balance:=round(Balance*100)/100;

       BalanceRef:=round(BalanceRef*100)/100;

       BalanceRef :=Balance-BalanceRef;

       BalanceRef:=round(BalanceRef*100)/100;

       If  BalanceRef>0 then

         begin

          StringGrid.Cells[4,n]:=FloatToStr(BalanceRef);

           BalanceRef := BalanceRef*35/100;

           BalanceRef:=round(BalanceRef*100)/100;

           StringGrid.Cells[5,n]:=FloatToStr(BalanceRef);

         end

        else

          begin

           StringGrid.Cells[4,n]:='0';

           StringGrid.Cells[5,n]:='0';

          end;

       StringGrid.Cells[3,n]:=FloatToStr(Balance);

       if BalanceRef>0  then

        Balance:=Balance-BalanceRef;

       Balance:=round(Balance*100)/100;

       StringGrid.Cells[6,n]:=FloatToStr(Balance);

      if n=countM then

        begin

         if  periodicity = 'ежеквартально' then

          if countM mod 3<>0 then

           begin

             BalanceRef := (Summa*((Rate*(countM mod 3)*(365/12))/(36500)))-(Summa * ((RateRef * (countM mod 3)*(365/12)) / (36500)));

              StringGrid.Cells[3,n]:=FloatToStr(round(100*Summa*((Rate*(countM  mod 3)*(365/12))/(36500)))/100);

              BalanceRef:=round(BalanceRef*100)/100;

               if BalanceRef>0 then

               StringGrid.Cells[4,n]:=FloattoStr(BalanceRef)

               else

               StringGrid.Cells[4,n]:='0';

             if BalanceRef>0 then

               BalanceRef := BalanceRef*35/100

             else

               BalanceRef :=0;

                BalanceRef:=round(BalanceRef*100)/100;

              StringGrid.Cells[5,n]:=FloattoStr(BalanceRef);

              StringGrid.Cells[6,n]:=FloatToStr(StrToFloat(StringGrid.Cells[3,n])-BalanceRef);

             Summa := Summa+Summa*((Rate*(countM mod 3)*(365/12))/(36500))- BalanceRef;