Урок 13. Самостоятельная

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

Если хотите начать практику с этого урока, то вам необходимо развернуть учебный проект по инструкции в статье Разворачивание проекта.

При разворачивании проекта используйте backup базы данных, который можете найти в архиве из раздела Ответы прошлого урока. Скопируйте папки Forms, Workflow и Patterns в папку с развернутым проектом, например, в папку D:\WT\Projects\Template\Projects\1. Template.

Инструкция по подключению шаблонов находится по ссылке.

Касса

Форма кассы должна открываться с главной формы через пункт меню Финансы -> Касса.

Форма кассы

Создайте форму кассы с глобальным фильтром по дате операции. По умолчанию должны отображаться операции за текущий месяц.

В запросе для кассы для параметров {DateStart} и {DateFinish} используйте функцию convert_date_filter(), которая приводит дату фильтра к дате со временем в часовой зоне сервера.

Таблица операций

Таблица кассовых операций должна содержать колонки:

RowNumber (№) - порядковый номер строки;
CashId - идентификатор кассовой операции из таблицы template.cash;
OrderId - идентификатор заказа из таблицы template.order. Актуально только для операций "Оплата заказа";
Date (Дата операции) - дата в формате "dd.MM.yyyy HH:mm";
AccountTitle (Счет) - наименование счета;
OperationTitle (Назначение платежа) - наименование назначения платежа;
Income (Приход) - сумма кассовой операции или оплаты заказа, если тип назначение платежа "Доход";
Expense (Расход) - сумма кассовой операции, если тип назначение платежа "Расход";
ClientTitle (Клиент) - имя клиента.

Редактирование таблицы кассовых операций

Операции системного назначения "Оплата заказа" можно создавать, редактировать и удалять только из карточки заказа.

При попытке открыть для редактирование операцию "Оплата заказа" должна открываться карточка заказа, в котором эта оплата была создана. При сохранении изменений в заказе и его оплатах данные на форме кассы должны обновляться. Так же необходимо прокидывать уведомление об изменении заказов на главную форму, чтобы обновлять список заказов.

Остальные операции редактируются в карточке кассовой операции.

При попытке удалить операцию "Оплата заказа" из кассы необходимо уведомлять пользователя о невозможности действия и предлагать перейти в заказ.

Удаление кассовой операции делать через поля deleted и date_deleted - это позволит не терять данные.

Чтобы текст на кнопках не наезжал на иконку, добавьте в начало текста несколько пробелов.

Для иконки используйте тэг <Image>, а для выравнивания - <ImageAlign>.

Результирующие поля

Под таблицей создайте результирующие поля (TextBox), которые будут привязаны к столбцам Income (Приход) и Expense (Расход).

Для задания левой координаты объекта TextBox используйте get-проперти ColumnLeft, которое вернет левую координату столбца таблицы относительно контейнера, в котором располагается таблица.

Если таблица имеет горизонтальную полосу прокрутки, то следует использовать get-проперти ColumnOffset, которое вернет левую координату столбца таблицы относительно контейнера, в котором располагается таблица, с учетом сдвига полосы прокрутки по горизонтали.

Для задания ширины объекта TextBox используйте get-проперти ColumnWidth, которое вернет ширину определенного столбца.

Высоту объектов TextBox задайте равной 35 и переведите их в многострочный режим, указав тэг <Multiline> со значением True. Если это не сделать, высота объекта TextBox останется стандартной.

Текст необходимо выровнять по правому краю.

Результирующие поля должны быть только для чтения и отображать две суммы. Первая сумма - сумма значений выделенных ячеек в столбце. Вторая сумма - сумма значений всех ячеек в столбце.

Пример тэга <Text> для результирующего поля

<Text>
  <String>
	<Format>{0} /
{1}</Format>
	<Items>
	  <Item>
		<DataTypeFormat Type="DecimalDataType" Format="N2">
		  <Object Name="CashDatabaseTable">
			<Property Name="SelectedCellsSumByColumnName">
			  <Parameters>
				<Parameter Name="ColumnName">Income</Parameter>
			  </Parameters>
			</Property>
		  </Object>
		</DataTypeFormat>
	  </Item>
	  <Item>
		<DataTypeFormat Type="DecimalDataType" Format="N2">
		  <Object Name="CashDatabaseTable">
			<Property Name="ColumnSum">
			  <Parameters>
				<Parameter Name="ColumnName">Income</Parameter>
			  </Parameters>
			</Property>
		  </Object>
		</DataTypeFormat>
	  </Item>
	</Items>
  </String>
</Text>

Для таблицы задайте свойство MultiSelect со значением True для атрибута Value. Это позволит пользователю выделять несколько строк.

Если пользователи могут менять видимость колонок в таблице DatabaseTable (данная возможность будет рассматриваться в уроке Пользовательские настройки), то видимость результирующих полей следует привязать к видимости соответствующих колонок через get-проперти ColumnVisible.

Выгрузка данных в Excel

Для кнопки "Экспорт в Excel..." используйте картинку из архива и команду типа ExportToExcelCommand.

Создайте xlsx-файл шаблона для экспорта в Excel кассы за выбранный период. При выгрузке данных у вас должен получаться документ вида:

Карточка кассовой операции

Создайте форму добавления и редактирования кассовой операции вида:

Для поля "Назначение платежа" (OperationComboBox) используйте SQL-запрос:

Template.xml

<SqlQuery Name="OperationCashSelectSqlQuery">
  <Text>
    SELECT
      operation_id AS "OperationId",
      title AS "Title",
      archive AS "Archive"
    FROM template.operation O
    WHERE
      id_title IS NULL AND
      NOT EXISTS (SELECT * FROM template.operation O2 WHERE O2.operation_category_id = O.operation_id)
    ORDER BY title, operation_id ASC;
  </Text>
</SqlQuery>

Запрос возвращает назначения платежей, которые не являются системными и не имеют дочерних назначений.

Дополнительно можете перевести форму списка назначений платежа в режим выбора. При этом для кнопки "Выбрать" на форме списка необходимо реализовать проверку, что выделенное назначение не имеет дочерних элементов и не является служебным или системным.

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

Отчет по бюджету

Форма отчета по бюджету должна открываться с главной формы (TemplateStart.xml) через пункт меню Отчеты-> Отчет по бюджету.

Основной отчет

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

Для получения данных для отчета используйте SQL-запрос, приведенный ниже.

BudgetReportSelectSqlQuery

Template.xml

<SqlQuery Name="BudgetReportSelectSqlQuery">
  <Text>
    WITH RECURSIVE _operation_tree (operation_id, operation_category_id, title, income, archive, path, child_array) AS (
      SELECT -2::integer, NULL::bigint, 'ДОХОД', True, False, ARRAY[-2]::integer[], template.operation_get_child(null, true)

      UNION

      SELECT -1::integer, NULL::bigint, 'РАСХОД', False, False, ARRAY[-1]::integer[], template.operation_get_child(null, false)

      UNION

      SELECT
        O.operation_id,
        T.operation_id,
        O.title,
        O.income,
        O.archive,
        T.path || O.operation_id,
        template.operation_get_child(O.operation_id)
      FROM
        template.operation O, _operation_tree T
      WHERE
        COALESCE(O.operation_category_id, CASE WHEN O.income THEN -2 ELSE -1 END) = T.operation_id
    ), _cash AS (
      SELECT
        Oper.operation_id,
        Oper.income,
        SUM(summ) AS summ
      FROM
        template.cash C
        JOIN template.operation Oper USING(operation_id)
        LEFT JOIN template.order_payment OP USING(cash_id)
        LEFT JOIN template.order O USING(order_id)
      WHERE
        NOT C.deleted AND
        NOT COALESCE(O.deleted, false) AND
        C.cash_date BETWEEN convert_date_filter({DateStart}::timestamp) AND convert_date_filter({DateFinish}::timestamp) + interval '1d - 1s'
      GROUP BY Oper.operation_id
    ), report_tmp AS (
      SELECT
        O.operation_id,
        O.operation_category_id,
        O.title AS operation_title,
        O.income,
        O.archive,
        O.path,
        COALESCE(C.summ, 0) summ
      FROM
        _operation_tree O
        LEFT JOIN LATERAL
        (
          SELECT SUM(summ) AS summ
          FROM _cash C
          WHERE C.operation_id = ANY(O.child_array)
        ) AS C ON True
    )
    SELECT
      true AS "Expand",
      operation_id AS "OperationId",
      operation_category_id AS "OperationCategoryId",
      operation_title AS "OperationTitle",
      summ AS "Summ",
      operation_id IN (-1, -2) AS "Total",
      income AS "Income",
      false AS "Profit"
    FROM report_tmp
    WHERE
      NOT archive OR
      summ != 0

    UNION ALL

    SELECT
      true AS "Expand",
      -3 AS "OperationId",
      NULL AS "OperationCategoryId",
      'Прибыль' AS "OperationTitle",
      SUM(summ * CASE WHEN income THEN 1 ELSE -1 END) AS "Summ",
      false AS "Total",
      NULL AS "Income",
      true AS "Profit"
    FROM report_tmp
    WHERE operation_id IN (-1, -2);
  </Text>
</SqlQuery>

Обратите внимание, что для параметров {DateStart} и {DateFinish} используется функция convert_date_filter(), которая приводит дату фильтра к дате со временем в часовой зоне сервера.

В запросе используется функция template.operation_get_child(), которая возвращает массив идентификаторов всех вложенных дочерних элементов выбранного назначения платежа:

CREATE OR REPLACE FUNCTION template.operation_get_child(
    in_operation_id bigint,
    in_income boolean DEFAULT true)
  RETURNS bigint[] AS
$BODY$
DECLARE
  _child_operation_id_array bigint[];
  _child_operation_id bigint;
  _child_operation_record record;
  _result bigint[];
BEGIN
  IF (in_operation_id IS NOT NULL)
  THEN
    _child_operation_id_array = (SELECT array_agg(operation_id ORDER BY operation_id) FROM template.operation WHERE operation_category_id = in_operation_id);

    IF (_child_operation_id_array IS NULL)
    THEN
      _result = ARRAY[in_operation_id];
    ELSE
      FOREACH _child_operation_id IN ARRAY _child_operation_id_array
        LOOP
          _result = _result || (SELECT template.operation_get_child(_child_operation_id));
        END LOOP;
    END IF;
  ELSE
    FOR _child_operation_record IN (SELECT operation_id FROM template.operation WHERE (income = in_income OR in_income IS NULL) AND operation_category_id IS NULL ORDER BY operation_id)
    LOOP
      _result = _result || (SELECT template.operation_get_child(_child_operation_record.operation_id));
    END LOOP;
  END IF;

  RETURN _result;
END;
$BODY$
  LANGUAGE plpgsql;

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

Для каждого отчета все необходимые запросы лучше группировать в отдельный SqlQueryPermission, для которого создавать отдельную Role. Это позволит в будущем гибче настраивать права доступа к отдельным отчетам.

Создайте xlsx-файл шаблона для экспорта в Excel отчета за выбранный период. При выгрузке данных у вас должен получаться документ вида:

В шаблоне должно быть три блока: "ДОХОД", "РАСХОД" и "Прибыль". Для получения нужных строк из таблицы DatabaseTable используйте get-проперти FilteredColumnValues.

Детализация отчета

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

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

Форма детализации отображает список кассовых операции выбранного назначения платежей за указанный период:

Если в основном отчете будет выбрана одна из служебных записей "ДОХОД", "РАСХОД" или "Прибыль", то в детализации будут отображаться кассовые операции на назначения платежей соответствующего типа: доход, расход или одновременно доход и расход.

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

Для получения данных для отчета используйте SQL-запрос, приведенный ниже.

BudgetReportDetailSelectSqlQuery

Template.xml

<SqlQuery Name="BudgetReportDetailSelectSqlQuery">
  <Text>
    WITH operation_tmp AS (
      SELECT template.operation_get_child({OperationId}, {Income}) AS operation_id_array
    )
    SELECT
      C.cash_id AS "CashId",
      O.order_id AS "OrderId",
      C.cash_date AS "Date",
      A.title AS "AccountTitle",
      Oper.title AS "OperationTitle",
      C.summ * (CASE WHEN NOT Oper.income AND {OperationId} IS NULL AND {Income} IS NULL THEN -1 ELSE 1 END)AS "Summ",
      Cl.title AS "ClientTitle"
    FROM
      operation_tmp, template.cash C
      JOIN template.account A USING(account_id)
      JOIN template.operation Oper USING(operation_id)
      LEFT JOIN template.order_payment OP USING(cash_id)
      LEFT JOIN template.order O USING(order_id)
      LEFT JOIN template.client Cl USING(client_id)
    WHERE
      NOT C.deleted AND
      NOT COALESCE(O.deleted, false) AND
      C.cash_date BETWEEN convert_date_filter({DateStart}::timestamp) AND convert_date_filter({DateFinish}::timestamp) + interval '1d - 1s' AND
      C.operation_id = ANY(operation_id_array)
    ORDER BY C.cash_date, C.cash_id;
  </Text>
</SqlQuery>

Из формы детализации отчета пользователь должен попадать в карточку кассовой операции или на форму заказа, если выбрана "Оплата заказа". Это позволит узнать детали операции или скорректировать ее данные. Если в заказе были сохранены изменения, то необходимо обновлять данные в детализации отчета, а так же прокидывать уведомление об изменения на форму основного отчета и на главную форму, чтобы обновлять данные на этих формах.

Создайте xlsx-файл шаблона для экспорта в Excel детализации отчета за выбранный период. При выгрузке данных у вас должен получаться документ вида:

Отлично! Теперь у нас в проекте есть финансовый блок.