Отчетность

Учет и контроль на производстве подразумевает отчетность: выборку данных с возможностью вывести её на экран и/или печать. Самым простым способом получение отчетности является заполнение табличного представления нужной информацией с последующей выгрузкой в MS Excel для форматирования и печати.

В My Visual Database для формирования простой отчетности будем использовать кнопки, настроенные для получения данных через SQL-запрос (1) и выгрузку данных в MS Excel (2), а также форму, которая будет содержать компоненты для фильтрации данных – параметры отчета (3).

Разберем подробно как работает отчет по операциям.

Отчет “Движение”

Выделив из общего потока движения материальных ценностей нужные нам операции, можно определить, что поступило на склад за определенный период, что ушло в производство, что было произведено, а что было продано и за какую сумму, вычислить прибыль от продажи. Цена отображается как цена прихода. Отображается движение по отдельным партиям материальных ценностей.

Данный отчет выводит операции по заданным параметрам:

• Тип операции
• От кого
• Кому
• Начало периода
• Окончание периода

Отображаемые данные:

• Тип операции
• От кого
• Кому
• Дата
• Артикул
• Наименование
• Цена (прихода)
• Количество
• Единица измерения
• Сумма прихода
• Сумма расхода
• Прибыль

Итоговые данные:

• Сумма прихода
• Сумма расхода
• Прибыль

Получение данных

Хотя кнопка TdbButton позволяет вводить текст SQL-запроса непосредственно в окно настройки кнопки, поле с SQL-запросом мы оставим пустым, а настроим только отображение названия колонок и укажем таблицу для отображения результата. Это связано с тем, что текст запроса мы будем строить с помощью скрипта, что позволит провести дополнительную оптимизацию, добавляя только те конструкции для фильтрации, которые нужны при выбранных параметрах отчета.

Для формирования текста запроса будет использован обработчик нажатия кнопки:

procedure frmOperRep_btnUpdate_OnClick (Sender: TObject; var Cancel: boolean);
var
  tmpSQL:string;
begin
  tmpSQL :=
    ' SELECT '+CR+//
    '   operTypeName, '+CR+//
    '   contrFromName, '+CR+//
    '   contrToName, '+CR+//
    '   StrFTime("%d.%m.%Y",docDate), '+CR+//
    '   itemCode, '+CR+//
    '   itemName, '+CR+//
    '   price, '+CR+//
    '   qty, '+CR+//
    '   unitCode, '+CR+//
    '   price*qty as DisAmount, '+CR+//
    '   amount, '+CR+//
    '   (amount - price*qty) as margin '+CR+//
    '  FROM '+CR+//
    ' ( SELECT '+CR+//
    '   docType.name as operTypeName, '+CR+//
    '   cFrom.name as contrFromName, '+CR+//
    '   cTo.name as contrToName, '+CR+//
    '   operDoc.docDate, '+CR+//
    '   item.code as itemCode, '+CR+//
    '   item.name as itemName, '+CR+//
    ' ( SELECT op.amount / op.qty '+CR+//
    '   FROM oper op '+CR+//
    '   LEFT JOIN operDoc od ON od.id = op.id_operDoc '+CR+//
    '   WHERE op.id_pitem = oper.id_pitem '+CR+//
    '   AND od.id_docType IN (1,6) '+CR+//
    '   ORDER BY od.docDate '+CR+//
    '   LIMIT 1 '+CR+//
    ' ) as price, '+CR+//
    '   oper.qty, '+CR+//
    '   unit.code as unitCode, '+CR+//
    '   oper.amount '+CR+//
    ' FROM oper '+CR+//
    ' LEFT JOIN operDoc ON operDoc.id = oper.id_operDoc '+CR+//
    ' LEFT JOIN docType ON docType.id = operDoc.id_docType '+CR+//
    ' LEFT JOIN contr cFrom ON cFrom.id = operDoc.id_contr '+CR+//
    ' LEFT JOIN contr cTo ON cTo.id = operDoc.id_contr1 '+CR+//
    ' LEFT JOIN item ON item.id = oper.id_pItem '+CR+//
    ' LEFT JOIN unit ON unit.id = item.id_unit '+
    ' WHERE (1 = 1) ';
    // если нужна фильтрация по типу документа, то добавляем её
    if frmOperRep.cmbOperType.dbItemID <> -1 then
    tmpSQL := tmpSQL +
      ' AND ( operDoc.id_docType = '+IntToStr(frmOperRep.cmbOperType.dbItemID)+' ) ';
    // если нужна фильтрация по отправителю, то добавляем её
    if frmOperRep.cmbContrFrom.dbItemID <> -1 then
    tmpSQL := tmpSQL +
      ' AND ( cFrom.id = '+IntToStr(frmOperRep.cmbContrFrom.dbItemID)+' ) ';
    // если нужна фильтрация по получателю, то добавляем её
    if frmOperRep.cmbContrTo.dbItemID <> -1 then
    tmpSQL := tmpSQL +
      ' AND ( cTo.id = '+IntToStr(frmOperRep.cmbContrTo.dbItemID)+' ) ';
    // если нужна фильтрация по периоду, то добавляем её
    if frmOperRep.dtpFrom.Checked then
    tmpSQL := tmpSQL +
      ' AND ( DATE( docDate ) >= DATE ( '+frmOperRep.dtpFrom.sqlDate+' ) ) ';
    if frmOperRep.dtpTo.Checked then
    tmpSQL := tmpSQL +
      ' AND ( DATE( docDate ) <= DATE ( '+frmOperRep.dtpTo.sqlDate+' ) ) ';
    // сортировка по датам
    tmpSQL := tmpSQL +
      ') as PData ORDER BY docDate ';
  frmOperRep.btnUpdate.dbSQL := tmpSQL;
end;
Code language: JavaScript (javascript)

Сам SQL-запрос достаточно сложный. Он включает подзапрос для определения цены закупки. Для расчет прибыли также используется подзапрос: во вложенном запросе вычисляется сумма покупки и сумма продажи, а во внешнем – сумма прибыли. Чтобы это было лучше видно, ниже я приведу структуру запроса на языке SQL, для случая, когда не один из фильтров не выбран.

SELECT
  operTypeName,
  contrFromName,
  contrToName,
  StrFTime("%d.%m.%Y",docDate),
  itemCode,
  itemName, 
  price, 
  qty, 
  unitCode, 
  price*qty as DisAmount, 
  amount, 
  (amount - price*qty) as margin 
FROM 
( SELECT 
    docType.name as operTypeName, 
    cFrom.name as contrFromName, 
    cTo.name as contrToName, 
    operDoc.docDate, 
    item.code as itemCode, 
    item.name as itemName, 
    ( SELECT op.amount / op.qty 
      FROM oper op
      LEFT JOIN operDoc od ON od.id = op.id_operDoc
      WHERE op.id_pitem = oper.id_pitem
      AND od.id_docType IN (1,6)
      ORDER BY od.docDate
      LIMIT 1 
    ) as price, 
    oper.qty, 
    unit.code as unitCode, 
    oper.amount 
  FROM oper 
  LEFT JOIN operDoc ON operDoc.id = oper.id_operDoc 
  LEFT JOIN docType ON docType.id = operDoc.id_docType 
  LEFT JOIN contr cFrom ON cFrom.id = operDoc.id_contr 
  LEFT JOIN contr cTo ON cTo.id = operDoc.id_contr1 
  LEFT JOIN item ON item.id = oper.id_pItem 
  LEFT JOIN unit ON unit.id = item.id_unit
  WHERE (1 = 1)
) as PData 
ORDER BY docDate
Code language: SQL (Structured Query Language) (sql)

В пятой строке использована функция форматирования даты StrFTime() , так как в My Visual Database нет возможности настройки типа отображения данных и все данные, возвращаемые SQL-запросом, интерпретируются как текст. Функция StrFTime() имеет два параметра: формат отображения и поле с данными. В данном случае формат включает дату (%d), месяц (%m) и год (%Y), которые разделены точкой. Такое представление принято в России, в других странах может быть принято другое представление даты, что необходимо учитывать при локализации приложения.

В строке 13 вычисляется прибыль как разница между стоимостью закупки и стоимостью продажи. Чтобы это вычисление было возможно, необходимо заранее вычислить закупочную цену, что и делается в подзапросе, который расположен в строках с 15 по 43.

Цена в свою очередь также вычисляется с помощью подзапроса, находящегося в строках 22-29. Для этого находится первый документ прихода у данной партии, из которого берётся цена – сумма закупки делится на количество.

Строки 34-39 содержат команды для присоединения дополнительных данных, используемых для отображения и фильтрации: дата и тип документа, названия получателя и отправителя, название материальной ценности и её единица измерения.

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

...
  WHERE (1 = 1)
  AND ( operDoc.id_docType = 1 )
  AND ( cFrom.id = 5 )
  AND ( cTo.id = 4 )
  AND ( DATE( docDate ) >= DATE ( "2022-09-01" ) ) ';
  AND ( DATE( docDate ) <= DATE ( "2022-09-30" ) ) ';
) as PData 
ORDER BY docDateCode language: SQL (Structured Query Language) (sql)

Форматирование данных

Чтобы финансовая информация выглядела в правильном формате необходимо программно откорректировать её отображение после её загрузки в компонент табличного отображения. Для этого используется обработчик onChange(), в котором настраивается отображение в денежном формате для колонок с ценой, количеством, суммой закупки, суммой продажи и прибылью.

procedure frmOperRep_tgrMain_OnChange (Sender: TObject);
// отображение данных по отчету
begin
  // установить денежный формат
  Grid_FinFormat( Sender, 6, False);
  Grid_FinFormat( Sender, 7, False);
  // выполнить вычисление итогов
  TdbStringGridEx(Sender).Columns[9].Footer.FormulaKind := fkSum;
  TdbStringGridEx(Sender).CalculateFooter;
  Grid_FinFormat( Sender, 9, True);

  TdbStringGridEx(Sender).Columns[10].Footer.FormulaKind := fkSum;
  TdbStringGridEx(Sender).CalculateFooter;
  Grid_FinFormat( Sender, 10, True);

  TdbStringGridEx(Sender).Columns[11].Footer.FormulaKind := fkSum;
  TdbStringGridEx(Sender).CalculateFooter;
  Grid_FinFormat( Sender, 11, True);
end;Code language: Delphi (delphi)

Процедура Grid_FinFormat() настраивает формат отображения для указанной колонки таблицы:

procedure Grid_FinFormat( Sender:TObject; AColNumber:integer; ASum:boolean = True);
// форматирование колонки таблицы/дерева в денежный формат
var
  tmpGrid:TdbStringGridEx;
begin
  tmpGrid := TdbStringGridEx(Sender);
  TNxNumberColumn(tmpGrid.Columns[AColNumber]).FormatMask := '#,##0.00';
  if ASum then

    TNxNumberColumn(tmpGrid.Columns[AColNumber]).Footer.FormatMask := '#,##0.00';
end;Code language: Delphi (delphi)

Экспорт данных для печати

Для вывода отчета в MS Excel достаточно настроить параметры (2) кнопки btnExcel (1) следующим образом: выбрать действие для кнопки “Открыть в Excel” (3), указать таблицу, из которой будут браться данные (4) и выбрать программу для загрузки данных (5). Не забудьте нажать кнопку “ОК” для сохранения настроек.

Вывод отчета возможен в программы MS Execel или Calc (OpenOffice).

Главное меню

В программу были добавлены три отчета:

• Остатки
• Остатки (партии)
• Движение

Чтобы добавить вызов форм отчетов в главное меню, мы модифицируем процедуру UserApp_InitForm():

procedure UserApp_InitForm;
// инициализация форм
var
  tmpItem:TMenuItem;
begin
  // сплиттеры
  Splitter_Create( frmItem.pgcDetail, frmItem.panMain, alBottom);
  Splitter_Create( frmOperDoc.pgcDetail, frmOperDoc.panMain, alBottom);
  // меню
  //
  tmpItem := Menu_Add('','Отчеты',nil,1, '-');
  Menu_Add('Show_frmBalance','Остатки',tmpItem);
  Menu_Add('Show_frmPBalance','Остатки (партии)',tmpItem);
  Menu_Add('Show_frmOperRep','Движение',tmpItem);
  //
  tmpItem := Menu_Add('','&Справочники',nil,1, '-');
  Menu_Add('Show_frmContr','&Контрагенты',tmpItem);
  Menu_Add('Show_frmItem','&Номенклатура',tmpItem);
  Menu_Add('Show_frmPItem','Партии',tmpItem);
  Menu_Add('Show_frmItemType','&Вид номенклатуры',tmpItem);
  Menu_Add('Show_frmUnit','&Единицы измерения ',tmpItem);
  Menu_Add('Show_frmDocType','Тип документов',tmpItem);
  //
  Menu_Add('Show_frmOperDoc','Журнал документов',frmMain.mniFile,0);
  Menu_Add('','-',frmMain.mniFile,1, '-');
  //
  Form_ShowOnWinControl( frmProductItem, frmItem.tshProductItem );
  Form_ShowOnWinControl( frmOper, frmOperDoc.tshOper );
end;
Code language: JavaScript (javascript)

В результате в главное меню добавятся необходимые пункты вызова отчетов:

Ссылки

• Программа “Производство”, версия 1.0 beta
• Исходные файлы проекта “Производство”