вторник, 13 марта 2012 г.

Урок 32. “Уважаемые кроты, а не посчитать ли нам?”

Сегодня я решил поделиться одним из главных секретов, который поведал мне мой гуру однажды: "Хочешь сложный отчет получить  - сделай под него таблицу".

Счастье любого бухгалтера - правильно, без ошибок, посчитанная оборотная ведомость. Без лукавства можно утверждать, что "оборотка" - основной документ. Он служит и для составления Главной книги, и для поиска ошибок...

Это - довольно сложный отчет. Составляется он за период. Поэтому включает в себя для каждого счета развернутые (по дебету и кредиту) остатки на начало периода, обороты за период и развернутые остатки на конец периода. Средством первичного контроля правильности составления оборотной ведомости является присутствие в ней всех счетов и по парное совпадение итогов.

Коль скоро нам предстоит получить столь важный и сложный документ, мы должны иметь в базе данных "Расход", о создании которой я рассказал в уроке 18, одну вспомогательную таблицу "Oborot". Чем она существенно отличается от всех остальных? Она не присутствует в схеме данных, поскольку не имеет связей. А связей она не имеет, поскольку в ней нет ключей, в этой таблице не хранится полезная информация. Она нужна только для того, чтобы временно хранить результаты вычислений.

Структура ее проста:








И я надеюсь, не вызовет вопросов при ее создании.

Теперь вернемся к нашему проекту и добавим в него форму Oborot.pas (имя формы OborotFrm).

Все настройки формы мало чем отличаются от настроек формы main, кроме свойства

WindowState, которое нужно установить в wsMaximized, чтобы при создании формы она разворачивалась на весь экран (создаваемый отчет большой, хочется видеть как можно больше информации одновременно).

Не забудьте после добавления формы в проект удалить ее из автоматически создаваемых. Вместо этого в главной форме создайте обработчик пункта меню, предназначенного для создания Оборотной ведомости:



procedure TMainFrm.N3Click(Sender: TObject);      // Пункт меню "Оборотная ведомость"
begin
  Application.CreateForm(TOborotFrm, OborotFrm);  // Создание формы OborotFrm
  OborotFrm.ShowModal;                            // Вывод формы в модальном окне
  OborotFrm.Free;                                 // Освобождение памяти после окончания работы с формой
end;

Не забудьте в разделе implementation главной формы добавить ссылку на модуль формы Oborot.pas:

Uses
  MOs
, Oborot;


Теперь в коде добавленной формы пропишем сразу, чтобы не забыть:


Uses Main;


Это нужно для того, чтобы компонентам доступа к данным дать возможность использовать настройки компонента ADOConnection, расположенного на главной форме.

Традиционные обработчики при старте, активации, закрытии формы и некоторые другие:


procedure TOborotFrm.FormCreate(Sender: TObject);
begin


  // Стартовые значения для дат периода
  Date_N.Value:=Now();
  Date_K.Value:=Now()+1;


  // Подключение компонентов доступа к данным к базе данных
  ADOQuery1.Connection:=MainFrm.ADOConnection1;
  ADOQuery2.Connection:=MainFrm.ADOConnection1;
  ADOCommand1.Connection:=MainFrm.ADOConnection1;
  CDS.Connection:=MainFrm.ADOConnection1;


  // Назначение таблицы компоненту
  CDS.TableName:='Oborot';
end;


procedure TOborotFrm.FormActivate(Sender: TObject);
begin


  // Запрос на первичную очистку таблицы 
  ADOCommand1.CommandText:='DELETE Oborot.* FROM Oborot ';
  ADOCommand1.Execute;
end;


procedure TOborotFrm.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  Action:= caFree;
end;


procedure TOborotFrm.FormKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin


  // Обработка нажатия клавиши Escape - закрытие формы
  case Key of            // Start Case


    VK_ESCAPE:
      begin
        close;
      end;


    else


  End;                   // End case
end;


procedure TOborotFrm.MyOkButton1Click(Sender: TObject);
begin
  Close;
end;


А теперь - самое главное - собственно алгоритм вычисления оборотной ведомости, в основу которого положена описанная в уроке 16 формула:


procedure TOborotFrm.CalcOborots(Dn,Dk: TDateTime);
Var
    o: Currency;               // Свернутый остаток
    MyStr: String;             // Для хранения строки SQL запроса


begin


  // Очистка временной таблицы
  ADOCommand1.Execute;
  CDS.Active:=True;
  CDS.Requery();


  // Составление оборотной ведомости




  // Заполнение списка счетов  и стартовых остатков (для выбранной валюты)
  ADOQuery1.Active:=False;      // Деактивация запроса
  ADOQuery1.SQL.Clear;          // Очистка и затем - задание строки SQL
  ADOQuery1.SQL.Add('SELECT * FROM Accounts WHERE Val='+IntToStr(MySelect.MySel_IDVal));
  ADOQuery1.Active:=True;       // Включение запроса
  ADOQuery1.First;              // Переход на первую запись


  While not ADOQuery1.EOF do    // Перебор всех значений, пока не будет достигнут конец файла
  Begin
    CDS.Insert;                 // Добавление строки во вспомогательную таблицу, далее - заполнение полей
    CDS.FieldByName('IDAcc').Value:=ADOQuery1.FieldByName('ID').Value;  // ID счета
    CDS.FieldByName('NameAcc').Text:=ADOQuery1.FieldByName('Name').Text;// Название счета
    o:=ADOQuery1.FieldByName('Ost_D').Value-ADOQuery1.FieldByName('Ost_K').Value; // Вычисление свернутого остатка на момент старта программы
    If o>=0
    Then
    Begin
      CDS.FieldByName('OstD_N').Value:=o;
      CDS.FieldByName('OstK_N').Value:=0;      // Это - ноль
    End
    else
    Begin
      CDS.FieldByName('OstD_N').Value:=0;      // Это - ноль
      CDS.FieldByName('OstK_N').Value:=-o;
    End;


      // Вычисление остатков на начало по данным до даты Dn
    o:=CDS.FieldByName('OstD_N').Value-CDS.FieldByName('OstK_N').Value;


    ADOQuery2.Active:=False;
    ADOQuery2.SQL.Clear;
    MyStr:='SELECT Sum(Main.Summa) AS [MySum], IIf(IsNull([MySum]),0,[MySum]) AS MySumNotNull FROM Main WHERE (((Main.MyDate)<#';
    MyStr:=MyStr+FormatDateTime('mm/dd/yyyy',Dn);
    MyStr:=MyStr+'#) AND ((Main.D)=';
    MyStr:=MyStr+IntToStr(CDS.FieldByName('IDAcc').value);
    MyStr:=MyStr+'))';
    ADOQuery2.SQL.Add(MyStr);
    ADOQuery2.Active:=True;


    if ADOQuery2.RecordCount>0
    then
      o:=o+ADOQuery2.FieldByName('MySumNotNull').Value
    else
      o:=0;                                  // Это - ноль


    ADOQuery2.Active:=False;
    ADOQuery2.SQL.Clear;
    MyStr:='SELECT Sum(Main.Summa) AS [MySum], IIf(IsNull([MySum]),0,[MySum]) AS MySumNotNull FROM Main WHERE (((Main.MyDate)<#';
    MyStr:=MyStr+FormatDateTime('mm/dd/yyyy',Dn);
    MyStr:=MyStr+'#) AND ((Main.K)=';
    MyStr:=MyStr+IntToStr(CDS.FieldByName('IDAcc').value);
    MyStr:=MyStr+'))';
    ADOQuery2.SQL.Add(MyStr);
    ADOQuery2.Active:=True;


    if ADOQuery2.RecordCount>0
    then
      o:=o-ADOQuery2.FieldByName('MySumNotNull').Value
    else
      o:=0;                                // Это - ноль


    // Запись остатков на начало
    If o>=0
    Then
    Begin
      CDS.FieldByName('OstD_N').Value:=o;
      CDS.FieldByName('OstK_N').Value:=0;
    End
    else
    Begin
      CDS.FieldByName('OstD_N').Value:=0;
      CDS.FieldByName('OstK_N').Value:=-o;
    End;


    // Вычисление и запись оборотов
    ADOQuery2.Active:=False;
    ADOQuery2.SQL.Clear;
    MyStr:='SELECT Sum(Main.Summa) AS [MySum], IIf(IsNull([MySum]),0,[MySum]) AS MySumNotNull FROM Main ';
    MyStr:=MyStr+'WHERE (((Main.MyDate)>=#'+FormatDateTime('mm/dd/yyyy',Dn)+'#) ';
    MyStr:=MyStr+'AND ((Main.MyDate)<#'+FormatDateTime('mm/dd/yyyy',Dk)+'#) ';
    MyStr:=MyStr+'AND ((Main.D)=';
    MyStr:=MyStr+IntToStr(CDS.FieldByName('IDAcc').value);
    MyStr:=MyStr+'))';
    ADOQuery2.SQL.Add(MyStr);
    ADOQuery2.Active:=True;


    if ADOQuery2.RecordCount>0
    then
      o:=o+ADOQuery2.FieldByName('MySumNotNull').Value
    else
      o:=0;


    CDS.FieldByName('ObD').Value:=ADOQuery2.FieldByName('MySumNotNull').Value;


    ADOQuery2.Active:=False;
    ADOQuery2.SQL.Clear;
    MyStr:='SELECT Sum(Main.Summa) AS [MySum], IIf(IsNull([MySum]),0,[MySum]) AS MySumNotNull FROM Main ';
    MyStr:=MyStr+'WHERE (((Main.MyDate)>=#'+FormatDateTime('mm/dd/yyyy',Dn)+'#) ';
    MyStr:=MyStr+'AND ((Main.MyDate)<#'+FormatDateTime('mm/dd/yyyy',Dk)+'#) ';
    MyStr:=MyStr+'AND ((Main.K)=';
    MyStr:=MyStr+IntToStr(CDS.FieldByName('IDAcc').value);
    MyStr:=MyStr+'))';
    ADOQuery2.SQL.Add(MyStr);
    ADOQuery2.Active:=True;


    if ADOQuery2.RecordCount>0
    then
      o:=o-ADOQuery2.FieldByName('MySumNotNull').Value
    else
      o:=0;


    CDS.FieldByName('ObK').Value:=ADOQuery2.FieldByName('MySumNotNull').Value;


    // Вычисление и запись остатков на конец
    If o>=0
    Then
    Begin
      CDS.FieldByName('OstD_K').Value:=o;
      CDS.FieldByName('OstK_K').Value:=0;
    End
    else
    Begin
      CDS.FieldByName('OstD_K').Value:=0;
      CDS.FieldByName('OstK_K').Value:=-o;
    End;


    CDS.Post;
    ADOQuery1.Next;
  End;
  CDS.First;
end;


Я постарался дать подробные комментарии. Но, если у Вас возникнут вопросы, я жду их по адресу vs259@mail.ru.

Что остается? Остается только подложить вызов этой процедуры под кнопку:


procedure TOborotFrm.Button1Click(Sender: TObject);
begin
  CalcOborots(Date_N.Value,Date_K.Value);
end;



Но, смотреть на экране объемный документ не всегда удобно. Поэтому в следующих уроках я планирую рассказать, как экспортировать данные в Excel и как "прикрутить" к программе бумажный отчет.
















Комментариев нет:

Отправить комментарий

Примечание. Отправлять комментарии могут только участники этого блога.