Учет и контроль на производстве подразумевает отчетность: выборку данных с возможностью вывести её на экран и/или печать. Самым простым способом получение отчетности является заполнение табличного представления нужной информацией с последующей выгрузкой в 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 docDate
Code 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
- Исходные файлы проекта “Производство”