Урок 9. Список категорий

На этом уроке мы познакомимся с объектом DatabaseTree, который отображает данные в виде дерева значений, что позволяет лучше воспринимать иерархическую структуру данных. Для этого создадим форму списка товарно-материальных ценностей (ТМЦ), которые будут разделяться на категории.

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

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

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

Новый список

Для начала создадим простенький список единиц измерений, который пригодится при создании списка ТМЦ.

База данных

Для списка единиц измерения создайте таблицу template.unit с колонками:

unit_id smallint NOT NULL - первичный ключ;
title character varying NOT NULL - наименование;
short_title character varying NOT NULL - сокращенное обозначение;
archive boolean NOT NULL DEFAULT false - признак архивной записи.

Создайте функцию template.unit_try_delete(smallint).

Форма списка и карточка редактирования

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

Для этого можете воспользоваться паттерном ArchiveList из архива:

Пример заполнение настроек паттерна для списка единиц измерения:

Редактор автоматически создаст формы списка (TemplateUnitList.xml) и карточки сущности (TemplateUnitEdit.xml), а также в серверный xml-файл добавит все необходимые запросы.

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

На главной форме в меню добавьте пункт Списки -> Единицы измерения..., по которому будет открываться новая форма TemplateUnitList.xml.

Дерево значений

Нам необходимо создать форму списка для создания и редактирования новой сущности - товарно-материальных ценностей.

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

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

База данных

Создадим в базе данных таблицы:

template.material_category - категории товарно-материальных ценностей;
template.material - товарно-материальные ценности.

CREATE SEQUENCE template.material_category_id_seq;

CREATE TABLE template.material_category
(
  material_category_id bigint NOT NULL DEFAULT nextval('template.material_category_id_seq'::regclass),
  parent_material_category_id bigint,
  title character varying NOT NULL,
  archive boolean NOT NULL DEFAULT false,
  CONSTRAINT pk_material_category_id PRIMARY KEY (material_category_id),
  CONSTRAINT fk_parent_material_category_id FOREIGN KEY (parent_material_category_id)
      REFERENCES template.material_category (material_category_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

Поле parent_material_category_id будем использовать для хранения идентификатора родительской категории.

CREATE SEQUENCE template.material_id_seq;

CREATE TABLE template.material
(
  material_id bigint NOT NULL DEFAULT nextval('template.material_id_seq'::regclass),
  material_category_id bigint NOT NULL,
  title character varying NOT NULL,
  unit_id bigint NOT NULL,
  unit_price numeric NOT NULL,
  archive boolean NOT NULL DEFAULT false,
  CONSTRAINT pk_material_id PRIMARY KEY (material_id),
  CONSTRAINT fk_material_category_id FOREIGN KEY (material_category_id)
      REFERENCES template.material_category (material_category_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_unit_id FOREIGN KEY (unit_id)
      REFERENCES template.unit (unit_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

Подготовка формы

Создайте пустую форму TemplateMaterialList.xml с заголовком "Товарно-материальные ценности".

В главном меню на стартовой форме (TemplateStart.xml) добавьте пункт меню Списки -> ТМЦ..., по которому будет открываться новая форма со списком товарно-материальных ценностей.

Перейдем в файл TemplateMaterialList.xml, где в ContentPanel добавим три панели:

MaterialCategoryPanel - левая панель, в которой будет описываться дерево категорий товарно-материальные ценностей. Ей можно задать ширину в 400 пикселей;
MaterialPanel - правая панель, в которой будет описываться таблица товарно-материальных ценностей. Эта панель будет занимать все оставшееся место;
MaterialSeparatePanel - разделитель между панелей.

Дерево значений

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

В панели MaterialCategoryPanel создайте:

Надпись "Категории" (MaterialCategoryLabel), которая будет выступать заголовком для дерева значений;
Дерево категорий (MaterialCategoryDatabaseTree) - объект типа DatabaseTree. Тэг <Items> оставьте пустым - его заполним позже;
Кнопки редактирования дерева категорий (добавить, редактировать и удалить).

Для проверки наличия выделенного узла дерева DatabaseTree будем использовать get-проперти SelectedItemId, которое будем проверять в условии типа IsNotNullCondition:

TemplateMaterialList.xml

<Condition Name="MaterialCategorySelectedCondition" Type="IsNotNullCondition" Assembly="Conditions">
  <Items>
    <Item>
      <Object Name="MaterialCategoryDatabaseTree">
        <Property Name="SelectedItemId" />
      </Object>
    </Item>
  </Items>
  <DataType Type="IntegerDataType" />
</Condition>

Для кнопок добавления и редактирования категории создадим команды:

TemplateMaterialList.xml

<Command Name="MaterialCategoryAddFormShowCommand" Type="FormShowCommand" Assembly="Commands">
  <Xml Type="Path">TemplateMaterialCategoryEdit.xml</Xml>
  <Show Type="None" />
  <Multiple Allow="True" />
  <Parameters>
    <Parameter Name="ParentMaterialCategoryId">
      <Object Name="MaterialCategoryDatabaseTree">
        <Property Name="SelectedItemId" />
      </Object>
    </Parameter>
  </Parameters>
</Command>

<Command Name="MaterialCategoryEditFormShowCommand" Type="FormShowCommand" Assembly="Commands">
  <Xml Type="Path">TemplateMaterialCategoryEdit.xml</Xml>
  <Show Type="None" />
  <Multiple Allow="True" />
  <Parameters>
    <Parameter Name="Edit">True</Parameter>
    <Parameter Name="MaterialCategoryId">
      <Object Name="MaterialCategoryDatabaseTree">
        <Property Name="SelectedItemId" />
      </Object>
    </Parameter>
  </Parameters>
</Command>

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

Для отслеживания двойного клика по узлу дерева используйте условие DoubleClickCondition. Создайте <Execution>, который будет отслеживать это условие.

Создайте самостоятельно команду MaterialCategoryDeleteMessageBoxCommand для кнопки удаления записи в дереве категорий.

Конструкции <Execution> на эти команды можете оставить пустыми.

Карточка редактирования узла дерева

Создадим форму для редактирования категории (TemplateMaterialCategoryEdit.xml), используя паттерн EntityForm:

Добавьте на форму объекты ParentMaterialCategoryLabel и ParentMaterialCategoryComboBox, которые будут описывать родительскую категорию. Тэги <ValueList> и <Value> для выпадающего списка оставьте пустыми - их заполним позже.

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

Перейдем в xml-файл серверной части (Template.xml) и создадим запрос для получения списка категорий:

Template.xml

<SqlQuery Name="ParentMaterialCategorySelectSqlQuery">
  <Text>
    WITH RECURSIVE tree_tmp (material_category_id, parent_material_category_id, title, path) AS (
      SELECT material_category_id, parent_material_category_id, title, ARRAY[material_category_id]::bigint[]
      FROM parent_mc
    
      UNION ALL
    
      SELECT MC.material_category_id, MC.parent_material_category_id, MC.title, T.path || ARRAY[MC.material_category_id]::bigint[]
      FROM
        mc_tmp MC
      JOIN tree_tmp T ON T.material_category_id = MC.parent_material_category_id
    ), mc_tmp AS (
      SELECT *
      FROM template.material_category
      WHERE material_category_id IS DISTINCT FROM {MaterialCategoryId} OR {WithChild}
    ), parent_mc AS (
      SELECT *
      FROM mc_tmp
      WHERE parent_material_category_id IS NULL
    )
    
    SELECT
      T.material_category_id AS "MaterialCategoryId",
      CASE WHEN array_length(T.path, 1) > 1 THEN COALESCE(repeat('—', (array_length(T.path, 1) - 1)), '') || ' ' ELSE '' END || T.title  AS "Title"
    FROM
      tree_tmp T
      ORDER BY T.path, T.material_category_id;
  </Text>
</SqlQuery>

Этот запрос будем использовать в двух местах: в карточке категории ТМЦ для выбора родительской категории и в карточке ТМЦ для выбора категории товарно-материальной ценности. Поэтому сразу добавим в него параметр {WithChild}, которым будем разделять место использования запроса: из карточки категории будем передавать false, а из карточки ТМЦ - true.

В запросе стоит обратить внимание на условие IS DISTINCT FROM, которое для значений не NULL работает так же, как оператор <>. Однако, если оба сравниваемых значения NULL, результат будет false, и только если одно из значений NULL, возвращается true. Следовательно, если параметр {MaterialCategoryId} будет иметь значение NULL, то условие вернет true.

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

В этом запросе используется вычисление рекурсивного запроса, с помощью указания служебного слова RECURSIVE. Подробнее про рекурсивные запросы можете почитать по ссылке.

Добавьте запрос ParentMaterialCategorySelectSqlQuery в разрешение MaterialCategoryViewSqlQueryPermission, которое создалось автоматически при выполнении паттерна EntityForm.

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

Template.xml

<SqlQuery Name="MaterialCategoryByIdSelectSqlQuery">
  <Text>
    SELECT
      MC.title AS "Title",
      MC.parent_material_category_id AS "ParentMaterialCategoryId"
    FROM
      template.material_category MC
    WHERE
      MC.material_category_id = {MaterialCategoryId};
  </Text>
</SqlQuery>

Скорректируйте самостоятельно запросы MaterialCategoryInsertSqlQuery и MaterialCategoryUpdateSqlQuery, чтобы они сохраняли значение ParentMaterialCategoryId, переданное с формы.

Вернемся в файл TemplateMaterialCategoryEdit.xml и скорректируем MaterialCategoryPrimaryGetDataConnection:

TemplateMaterialCategoryEdit.xml

<DataConnection Name="MaterialCategoryPrimaryGetDataConnection" Type="PrimaryGetDataConnection" Assembly="DataConnections">
  <ManualLoad>
    <Not>
      <Parameter Name="Edit" />
    </Not>
  </ManualLoad>
  <SqlQuery Name="MaterialCategoryByIdSelectSqlQuery" Type="Select">
    <Workflow Name="Template" />
    <Fields>
      <Field Name="Title" />
      <Field Name="ParentMaterialCategoryId" />
    </Fields>
    <Parameters>
      <Parameter NativeName="MaterialCategoryId">
        <Value>
          <Parameter Name="MaterialCategoryId" />
        </Value>
      </Parameter>
    </Parameters>
  </SqlQuery>
</DataConnection>

Обратите внимание, что у MaterialCategoryPrimaryGetDataConnection появился тэг <ManualLoad> - признак определяет, будет ли загрузка данных происходить вместе с загрузкой формы или только после ручного обновления соединения с данными. Значение тэга обратно значению параметра Edit. Когда форма открыта на редактирование (значение параметра Edit = True), значение тэга <ManualLoad> будет False - DataConnection будет обновляться вместе с загрузкой формы. А если форма будет открыта на создание, то значение тэга станет True, и DataConnection будет обновляться только по команде. Таким образом, мы можем исключать лишнее обращение к базе данных, если уверены, что там нет нужных данных.

Самостоятельно измените MaterialCategoryInsertSetDataConnection и MaterialCategoryUpdateSetDataConnection, добавив параметр ParentMaterialCategoryId, в который будет передаваться значение из ParentMaterialCategoryComboBox.

Создадим соединения с данными для загрузки списка родительских категорий:

TemplateMaterialCategoryEdit.xml

<DataConnection Name="ParentMaterialCategoryPrimaryGetDataConnection" Type="PrimaryGetDataConnection" Assembly="DataConnections">
  <SqlQuery Name="ParentMaterialCategorySelectSqlQuery" Type="Select">
    <Workflow Name="Template" />
    <Fields>
      <Field Name="MaterialCategoryId" />
      <Field Name="Title" />
    </Fields>
    <Parameters>
      <Parameter NativeName="MaterialCategoryId" RefreshQuery="False">
        <Value>
          <Parameter Name="MaterialCategoryId" />
        </Value>
      </Parameter>
      <Parameter NativeName="WithChild" RefreshQuery="False">
        <Value>False</Value>
      </Parameter>
    </Parameters>
  </SqlQuery>
</DataConnection>

Как вы помните, на форме TemplateMaterialList.xml в команде MaterialCategoryAddFormShowCommand на дочернюю форму передавался параметр ParentMaterialCategoryId, давайте создадим этот параметр:

TemplateMaterialCategoryEdit.xml

<Parameter Name="ParentMaterialCategoryId" />

Теперь можем доделать ParentMaterialCategoryComboBox, заполнив его тэги <ValueList> и <Value>:

TemplateMaterialCategoryEdit.xml

<MyObject Name="ParentMaterialCategoryComboBox" Type="ComboBox" Assembly="BaseControls">
  <Top>
    <Object Name="ParentMaterialCategoryLabel">
      <Property Name="Bottom" />
    </Object>
  </Top>
  <Left>
    <Object Name="ParentMaterialCategoryLabel">
      <Property Name="Left" />
    </Object>
  </Left>
  <Width>
    <Object Name="ParentMaterialCategoryLabel">
      <Property Name="Width" />
    </Object>
  </Width>
  <TabIndex>1</TabIndex>
  <NullValue Show="True" Title="[Не выбрано]" />
  <AutoCompleteMode>SmartSuggest</AutoCompleteMode>
  <ValueList>
    <DataConnection SourceDataConnection="ParentMaterialCategoryPrimaryGetDataConnection">
      <Fields>
        <Field Name="MaterialCategoryId" />
        <Field Name="Title" />
      </Fields>
    </DataConnection>
  </ValueList>
  <Value>
    <Switch>
      <Case>
        <When>
          <Parameter Name="Edit" />
        </When>
        <Then>
          <DataConnection SourceDataConnection="MaterialCategoryPrimaryGetDataConnection">
            <Fields>
              <Field Name="ParentMaterialCategoryId" />
            </Fields>
          </DataConnection>
        </Then>
      </Case>
      <Case>
        <Parameter Name="ParentMaterialCategoryId" />
      </Case>
    </Switch>
  </Value>
</MyObject>

Откройте приложение, проверьте загрузку форм и попробуйте создать несколько категорий, одна из которых не будет иметь родительской категории (например, категория "Бумага"), а остальные категории будут ссылаться на нее. Выпадающий список будет иметь вид:

Отлично! Теперь можем продолжить работать с деревом значений на форме списка.

Запрос для DatabaseTree

Перейдем в xml-файл серверной части (Template.xml) и добавим запрос для получения списка категорий (MaterialCategoryListSelectSqlQuery) и запрос для получения взаимосвязей элементов дерева (MaterialCategoryRelationSelectSqlQuery):

Template.xml

<SqlQuery Name="MaterialCategoryListSelectSqlQuery">
  <Text>
    SELECT
      MC.material_category_id AS "MaterialCategoryId",
      MC.title AS "Title"
    FROM
      template.material_category MC
    ORDER BY MC.title, MC.material_category_id;
  </Text>
</SqlQuery>

<SqlQuery Name="MaterialCategoryRelationSelectSqlQuery">
  <Text>
    SELECT
      material_category_id AS "MaterialCategoryId",
      parent_material_category_id AS "ParentMaterialCategoryId"
    FROM
      template.material_category;
  </Text>
</SqlQuery>

Не забудем добавить их в MaterialCategoryViewSqlQueryPermission.

Вернемся в файл формы списка ТМЦ (TemplateMaterialList.xml) и создадим загружающее соединение с данными:

TemplateMaterialList.xml

<DataConnection Name="MaterialCategoryPrimaryGetDataConnection" Type="PrimaryGetDataConnection" Assembly="DataConnections">
  <SqlQueries>
    <SqlQuery Name="MaterialCategoryListSelectSqlQuery" Type="Select">
      <Workflow Name="Template" />
      <Fields>
        <Field Name="MaterialCategoryId" />
        <Field Name="Title" />
      </Fields>
    </SqlQuery>
    <SqlQuery Name="MaterialCategoryRelationSelectSqlQuery" Type="Select">
      <Workflow Name="Template" />
      <Fields>
        <Field Name="MaterialCategoryId" />
        <Field Name="ParentMaterialCategoryId" />
      </Fields>
    </SqlQuery>
  </SqlQueries>
</DataConnection>

Сразу создайте команду DataConnectionRefreshCommand для его обновления.

Обязательный тэг <Items> объекта MaterialCategoryDatabaseTree ожидает соединение с данными с двумя таблицами:

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

Таким образом, в тэге <Items> пропишем следующее значение:

<DataConnection SourceDataConnection="MaterialCategoryPrimaryGetDataConnection">
  <SourceQuery Name="MaterialCategoryListSelectSqlQuery">
    <Fields>
      <Field Name="MaterialCategoryId" />
      <Field Name="Title" />
    </Fields>
  </SourceQuery>
  <SourceQuery Name="MaterialCategoryRelationSelectSqlQuery">
    <Fields>
      <Field Name="MaterialCategoryId" />
      <Field Name="ParentMaterialCategoryId" />
    </Fields>
  </SourceQuery>
</DataConnection>

Откроем форму списка ТМЦ и проверим, что у нас получилось.

Обновление дерева

Создадим команду, в которой через set-проперти SelectedItemId объекта DatabaseTree будем выделять нужный элемент в нашем дереве категорий:

TemplateMaterialList.xml

<Command Name="MaterialCategorySelectInTreeValueSetCommand" Type="ValueSetCommand" Assembly="Commands">
  <Object Name="MaterialCategoryDatabaseTree">
    <Property Name="SelectedItemId">
      <Input />
    </Property>
  </Object>
</Command>

Дополним <Execution> последовательностями команд:

TemplateMaterialList.xml

<Execution>
  <ConditionExpression>
    <Command Name="MaterialCategoryAddFormShowCommand" Parameter="Updated" />
  </ConditionExpression>
  <Commands>
    <Command Name="MaterialCategoryDataConnectionRefreshCommand" />
    <Command Name="MaterialCategorySelectInTreeValueSetCommand">
      <Command Name="MaterialCategoryAddFormShowCommand" Parameter="MaterialCategoryId" />
    </Command>
    <Command Name="UpdatedTrueValueSetCommand" />
  </Commands>
</Execution>

<Execution>
  <ConditionExpression>
    <Command Name="MaterialCategoryEditFormShowCommand" Parameter="Updated" />
  </ConditionExpression>
  <Commands>
    <Command Name="MaterialCategoryDataConnectionRefreshCommand" />
    <Command Name="MaterialCategorySelectInTreeValueSetCommand">
      <Command Name="MaterialCategoryEditFormShowCommand" Parameter="MaterialCategoryId" />
    </Command>
    <Command Name="UpdatedTrueValueSetCommand" />
  </Commands>
</Execution>

Вызов команды UpdatedTrueValueSetCommand позволит при закрытии формы списка ТМЦ уведомить родительскую форму о наличии изменений.

Запустите приложение и попробуйте добавить пару категорий, чтобы проверить работу <Execution>.

Удаление категории

Сложность удаления категории ТМЦ заключается в том, что мы не можем удалить категорию, если она содержит подкатегории и/или позиции ТМЦ. Для проверки этих условий создадим функции.

Первая будет на проверку наличия вложенных позиций ТМЦ:

CREATE OR REPLACE FUNCTION template.material_category_has_materials(in_material_category_id bigint)
  RETURNS boolean AS
$BODY$
BEGIN

  IF (EXISTS (SELECT * FROM template.material WHERE material_category_id = in_material_category_id))
  THEN
    RETURN TRUE;
  END IF;

  RETURN
    bool_or(template.material_category_has_materials(material_category_id))
    FROM template.material_category
    WHERE parent_material_category_id = in_material_category_id;
    
END;
$BODY$
  LANGUAGE plpgsql;

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

CREATE OR REPLACE FUNCTION template.material_category_try_delete(in_material_category_id bigint)
  RETURNS character varying AS
$BODY$
DECLARE
  _has_child_categories boolean = FALSE;
  _has_child_materials boolean = FALSE;
  _result character varying;
BEGIN

  IF (SELECT NOT EXISTS(SELECT * FROM template.material_category WHERE material_category_id = in_material_category_id))
  THEN
    RETURN NULL;
  END IF;

  IF (SELECT used FROM template.is_used('material_category', 'material_category_id', ARRAY[]::text[], ARRAY[]::text[], in_material_category_id))
  THEN
  
    _has_child_categories = EXISTS (SELECT * FROM template.material_category WHERE parent_material_category_id = in_material_category_id);
    
    _has_child_materials = template.material_category_has_materials(in_material_category_id);
    
    _result = 'Выбранная категория ТМЦ ' ||
      CASE
        WHEN _has_child_categories AND _has_child_materials
        THEN 'содержит вложенные категории и позиции ТМЦ.'
        WHEN _has_child_categories
        THEN 'содержит вложенные категории ТМЦ.'
        WHEN _has_child_materials
        THEN 'содержит вложенные позиции ТМЦ.'
        ELSE 'используется в программе.'
      END;
      
    RETURN _result;
    
  ELSE
  
    DELETE FROM template.material_category
    WHERE material_category_id = in_material_category_id;

    RETURN NULL;
    
  END IF;
  
END;
$BODY$
  LANGUAGE plpgsql;

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

Создадим функцию для каскадного удаления:

CREATE OR REPLACE FUNCTION template.material_category_delete_cascade(in_material_category_id bigint)
  RETURNS void AS
$BODY$
BEGIN
  IF (SELECT NOT EXISTS(SELECT * FROM template.material_category WHERE material_category_id = in_material_category_id)) THEN
    RETURN;
  END IF;

  PERFORM template.material_try_delete(array_agg(material_id))
  FROM template.material
  WHERE material_category_id = in_material_category_id;
  
  PERFORM template.material_category_delete_cascade(material_category_id)
  FROM template.material_category
  WHERE parent_material_category_id = in_material_category_id;

  IF (SELECT used FROM template.is_used('material_category', 'material_category_id', ARRAY[]::text[], ARRAY[]::text[], in_material_category_id))
  THEN
  
    UPDATE template.material_category
    SET archive = TRUE
    WHERE material_category_id = in_material_category_id AND NOT archive;
    
  ELSE
  
    DELETE FROM template.material_category
    WHERE material_category_id = in_material_category_id;
    
  END IF;
END;
$BODY$
  LANGUAGE plpgsql;

Создадим функцию template.material_try_delete(bigint[]):

CREATE OR REPLACE FUNCTION template.material_try_delete(in_material_id_array bigint[])
  RETURNS character varying AS
$BODY$
DECLARE
  _material_id bigint;
  _deleted_count integer := 0;
  _archived_count integer := 0;
  _archived_array varchar[];
BEGIN
  FOR i IN 1..COALESCE(array_length(in_material_id_array, 1), 0)
  LOOP
    _material_id = in_material_id_array[i];

    raise notice '_material_id: %', _material_id;

    IF (SELECT used FROM template.is_used('material', 'material_id', ARRAY[]::text[], ARRAY[]::text[], _material_id))
    THEN
    
      UPDATE template.material
      SET archive = True
      WHERE material_id = _material_id AND NOT archive;

      _archived_count = _archived_count + 1;
      _archived_array = _archived_array || (SELECT title FROM template.material WHERE material_id = _material_id);
      
    ELSE
    
      DELETE FROM template.material
      WHERE material_id = _material_id;

      _deleted_count = _deleted_count + 1;
      
    END IF;
   
  END LOOP;

  RETURN CASE WHEN _archived_count > 0 THEN 
    'Удалено ТМЦ: ' || _deleted_count || 
    E'.\rПеремещено в архив: ' || _archived_count || 
    E'.\r\rСписок ТМЦ, перемещенных в архив:\r' || array_to_string(array_sort(_archived_array), E',\r') ELSE NULL END;
END;
$BODY$
  LANGUAGE plpgsql;

Функция template.material_try_delete(bigint[]) возвращает сообщение о количестве удаленных и перемещенных в архив записей, а также наименования ТМЦ, отправленных в архив. Но функция template.material_category_delete_cascade(bigint), которая ее вызывает, никак не обрабатывает возвращаемое сообщение и не передает его в результат SQL-запроса, чтобы можно было получить сообщение на форме и отобразить его пользователю. Вы можете самостоятельно реализовать данную функциональность.

В функции template.material_try_delete(bigint[]) используется функция сортировки массива:

CREATE OR REPLACE FUNCTION public.array_sort(anyarray)
  RETURNS anyarray AS
$BODY$
  SELECT ARRAY(SELECT unnest($1) ORDER BY 1);
$BODY$
  LANGUAGE sql;

Перейдем в серверный xml-файл и создадим запросы на удаление категорий:

Template.xml

<SqlQuery Name="MaterialCategoryDeleteSqlQuery">
  <Text>
    SELECT template.material_category_try_delete({MaterialCategoryId}::bigint);
  </Text>
</SqlQuery>

<SqlQuery Name="MaterialCategoryDeleteCascadeSqlQuery">
  <Text>
    SELECT template.material_category_delete_cascade({MaterialCategoryId}::bigint);
  </Text>
</SqlQuery>

Добавьте их в MaterialCategoryEditSqlQueryPermission.

Вернемся на форму списка ТМЦ и категорий (TemplateMaterialList.xml) и создадим SetDataConnection:

TemplateMaterialList.xml

<DataConnection Name="MaterialCategoryDeleteSetDataConnection" Type="SetDataConnection" Assembly="DataConnections">
  <Workflow Name="Template" />
  <Parameters>
    <Parameter NativeName="MaterialCategoryId">
      <Value>
        <Object Name="MaterialCategoryDatabaseTree">
          <Property Name="SelectedItemId" />
        </Object>
      </Value>
    </Parameter>
  </Parameters>
  <SqlQueries>
    <SqlQuery Name="MaterialCategoryDeleteSqlQuery" Type="Delete" />
  </SqlQueries>
</DataConnection>

<DataConnection Name="MaterialCategoryDeleteCascadeSetDataConnection" Type="SetDataConnection" Assembly="DataConnections">
  <Workflow Name="Template" />
  <Parameters>
    <Parameter NativeName="MaterialCategoryId">
      <Value>
        <Object Name="MaterialCategoryDatabaseTree">
          <Property Name="SelectedItemId" />
        </Object>
      </Value>
    </Parameter>
  </Parameters>
  <SqlQueries>
    <SqlQuery Name="MaterialCategoryDeleteCascadeSqlQuery" Type="Delete" />
  </SqlQueries>
  <Refresh>
    <DataConnection Name="MaterialCategoryPrimaryGetDataConnection" />
    <DataConnection Name="MaterialPrimaryGetDataConnection" />
  </Refresh>
</DataConnection>

Добавим команды типа SaveCommand:

TemplateMaterialList.xml

<Command Name="MaterialCategoryDeleteSaveCommand" Type="SaveCommand" Assembly="Commands">
  <DataConnections>
    <DataConnection Name="MaterialCategoryDeleteSetDataConnection" />
  </DataConnections>
</Command>

<Command Name="MaterialCategoryDeleteCascadeSaveCommand" Type="SaveCommand" Assembly="Commands">
  <DataConnections>
    <DataConnection Name="MaterialCategoryDeleteCascadeSetDataConnection" />
  </DataConnections>
</Command>

Создадим условие IsNullCondition для проверки результата выполнения запроса на удаление категории:

TemplateMaterialList.xml

<Condition Name="MaterialCategoryDeleteCommandResultIsNullCondition" Type="IsNullCondition" Assembly="Conditions">
  <Items>
    <Item>
      <Command Name="MaterialCategoryDeleteSaveCommand" />
    </Item>
  </Items>
</Condition>

И создадим команду для отображения полученного от сервера сообщения:

TemplateMaterialList.xml

<Command Name="MaterialCategoryTryDeleteUsedMessageBoxCommand" Type="MessageBoxCommand" Assembly="Commands">
  <Caption>Удаление</Caption>
  <Text>
    <String>
      <Format>{0}\rВы можете попытаться удалить выбранную категорию вместе с вложенными элементами. В случае невозможности удаления записи будут отправлены в архив.\rПродолжить?</Format>
      <Items>
        <Item>
          <Command Name="MaterialCategoryDeleteSaveCommand" />
        </Item>
      </Items>
    </String>
  </Text>
  <Icon Type="Question" />
  <Buttons Type="YesNo" />
</Command>

Добавим Execution:

TemplateMaterialList.xml

<Execution>
  <ConditionExpression>
    <Command Name="MaterialCategoryDeleteMessageBoxCommand" Parameter="Yes" />
  </ConditionExpression>
  <Commands>
    <Command Name="MaterialCategoryDeleteSaveCommand" />
    <If>
      <When>
        <Condition Name="MaterialCategoryDeleteCommandResultIsNullCondition" />
      </When>
      <Then>
        <Command Name="MaterialCategoryDataConnectionRefreshCommand" />
        <Command Name="UpdatedTrueValueSetCommand" />
      </Then>
      <Else>
        <Command Name="MaterialCategoryTryDeleteUsedMessageBoxCommand" />
      </Else>
    </If>
  </Commands>
</Execution>

<Execution>
  <ConditionExpression>
    <Command Name="MaterialCategoryTryDeleteUsedMessageBoxCommand" Parameter="Yes" />
  </ConditionExpression>
  <Commands>
    <Command Name="MaterialCategoryDeleteCascadeSaveCommand" />
  </Commands>
</Execution>

Паттерн Table

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

На панель MaterialPanel добавим таблицу с кнопками редактирования. Для этого воспользуйтесь паттерном Table:

Выполним следующие настройки паттерна:

После применения паттерна перейдем в xml-файл серверной части и перенесем роль MaterialEditRole в группу GuestGroup, а также скорректируем текст запроса:

Template.xml

<SqlQuery Name="MaterialSelectSqlQuery">
  <Text>  
    SELECT
      M.material_id AS "MaterialId",
      M.material_category_id AS "MaterialCategoryId",
      M.title AS "Title",
      M.unit_id AS "UnitId",
      M.unit_price AS "UnitPrice"
    FROM
      template.material M
    ORDER BY M.title;
  </Text>
</SqlQuery>

Таким образом форма списка (TemplateMaterialList.xml) примет вид:

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

TemplateMaterialList.xml

<DataConnection Name="UnitPrimaryGetDataConnection" Type="PrimaryGetDataConnection" Assembly="DataConnections">
  <SqlQuery Name="UnitSelectSqlQuery" Type="Select">
    <Workflow Name="Template" />
    <Fields>
      <Field Name="UnitId" />
      <Field Name="ShortTitle" />
    </Fields>
  </SqlQuery>
</DataConnection>

Добавим в таблицу MaterialDatabaseTable колонки:

<Column Name="UnitId" Type="DatabaseTableColumnTextBox" Assembly="DatabaseTableColumnControls">
  <Visible>False</Visible>
</Column>
<Column Name="UnitShortTitle" Type="DatabaseTableColumnTextBox" Assembly="DatabaseTableColumnControls">
  <Title>Ед. измерения</Title>
  <Width>100</Width>
  <AutoSizeMode Value="None" />
  <Substitution SourceColumn="UnitId">
    <DataConnection SourceDataConnection="UnitPrimaryGetDataConnection">
      <Fields>
        <Field Name="UnitId"/>
        <Field Name="ShortTitle" />
      </Fields>
    </DataConnection>
  </Substitution>
</Column>
<Column Name="UnitPrice" Type="DatabaseTableColumnTextBox" Assembly="DatabaseTableColumnControls">
  <Title>Цена продажи</Title>
  <Width>100</Width>
  <AutoSizeMode Value="None" />
  <DataType Type="DecimalDataType" Format="N2" />
  <Alignment Value="MiddleRight" />
</Column>

Скорректируем команду MaterialAddFormShowCommand, добавив параметр MaterialCategoryId:

TemplateMaterialList.xml

<Command Name="MaterialAddFormShowCommand" Type="FormShowCommand" Assembly="Commands">
  <Xml Type="Path">TemplateMaterialEdit.xml</Xml>
  <Show Type="None" />
  <Parameters>
    <Parameter Name="MaterialCategoryId">
      <Object Name="MaterialCategoryDatabaseTree">
        <Property Name="SelectedItemId" />
      </Object>
    </Parameter>
  </Parameters>
</Command>

Карточка ТМЦ

Первым делом перейдем в серверный xml-файл и скорректируем запросы:

Template.xml

<SqlQuery Name="MaterialByIdSelectSqlQuery">
  <Text>
    SELECT
      M.material_category_id AS "MaterialCategoryId",
      M.title AS "Title",
      M.unit_id AS "UnitId",
      M.unit_price AS "UnitPrice"
    FROM
      template.material M
    WHERE
      M.material_id = {MaterialId};
  </Text>
</SqlQuery>

<SqlQuery Name="MaterialInsertSqlQuery">
  <Text>
    INSERT INTO template.material (
      title,
      material_category_id,
      unit_id,
      unit_price
    )
    VALUES (
      {Title},
      {MaterialCategoryId},
      {UnitId},
      {UnitPrice}
    )
    RETURNING material_id;
  </Text>
</SqlQuery>

<SqlQuery Name="MaterialUpdateSqlQuery">
  <Text>
    UPDATE template.material
    SET
      title = {Title},
      material_category_id = {MaterialCategoryId},
      unit_id = {UnitId},
      unit_price = {UnitPrice}
    WHERE
      material_id = {MaterialId};
  </Text>
</SqlQuery>

Редактор с помощью паттерна Table уже создал нужный файл формы (TemplateMaterialEdit.xml). Перейдем в него, чтобы создать объекты интерфейса.

Добавим в параметры формы параметр MaterialCategoryId.

Скорректируем соединение с данными MaterialPrimaryGetDataConnection, добавив необходимые поля:

TemplateMaterialEdit.xml

<DataConnection Name="MaterialPrimaryGetDataConnection" Type="PrimaryGetDataConnection" Assembly="DataConnections">
  <ManualLoad>
    <Not>
      <Parameter Name="Edit" />
    </Not>
  </ManualLoad>
  <SqlQuery Name="MaterialByIdSelectSqlQuery" Type="Select">
    <Workflow Name="Template" />
    <Fields>
      <Field Name="MaterialCategoryId" />
      <Field Name="Title" />
      <Field Name="UnitId" />
      <Field Name="UnitPrice" />
    </Fields>
    <Parameters>
      <Parameter NativeName="MaterialId" RefreshQuery="False">
        <Value>
          <Parameter Name="MaterialId" />
        </Value>
      </Parameter>
    </Parameters>
  </SqlQuery>
</DataConnection>

Создадим соединение с данными MaterialCategoryPrimaryGetDataConnection для получения списка категорий:

TemplateMaterialEdit.xml

<DataConnection Name="MaterialCategoryPrimaryGetDataConnection" Type="PrimaryGetDataConnection" Assembly="DataConnections">
  <SqlQuery Name="ParentMaterialCategorySelectSqlQuery" Type="Select">
    <Workflow Name="Template" />
    <Fields>
      <Field Name="MaterialCategoryId" />
      <Field Name="Title" />
    </Fields>
    <Parameters>
      <Parameter NativeName="MaterialCategoryId" RefreshQuery="False">
        <Value>
          <DataConnection SourceDataConnection="MaterialPrimaryGetDataConnection">
            <Fields>
              <Field Name="MaterialCategoryId" />
            </Fields>
          </DataConnection>
        </Value>
      </Parameter>
      <Parameter NativeName="WithChild" RefreshQuery="False">
        <Value>True</Value>
      </Parameter>
    </Parameters>
  </SqlQuery>
</DataConnection>

В нем используем запрос ParentMaterialCategorySelectSqlQuery, который писали для карточки редактирования категории ТМЦ.

Самостоятельно создайте все необходимые элементы формы, чтобы форма имела вид:

Если форма открыта на редактирование, то в поле "Категория" (MaterialCategoryComboBox) должна отображаться категория редактируемого ТМЦ, иначе должен подставляться идентификатор из параметра MaterialCategoryId.

Для создания выпадающего списка единиц измерений (UnitComboBox) используйте материал прошлого урока, где мы реализовывали логику редактирования выпадающего списка клиентов и режим выбора на форме списка клиентов. Также для UnitComboBox используйте соединение с данными:

TemplateMaterialEdit.xml

<DataConnection Name="UnitPrimaryGetDataConnection" Type="PrimaryGetDataConnection" Assembly="DataConnections">
  <SqlQuery Name="UnitSelectSqlQuery" Type="Select">
    <Workflow Name="Template" />
    <Fields>
      <Field Name="UnitId" />
      <Field Name="ShortTitle" />
      <Field Name="Archive" />
    </Fields>
    <Filter Type="Nested">
      <Or>
        <Filter>
          <Field NativeName="Archive" />
          <Value>False</Value>
          <DataType Type="BooleanDataType" />
        </Filter>
        <Filter>
          <Field NativeName="UnitId" />
          <Value>
            <DataConnection SourceDataConnection="MaterialPrimaryGetDataConnection">
              <Fields>
                <Field Name="UnitId" />
              </Fields>
            </DataConnection>
          </Value>
          <DataType Type="IntegerDataType" />
        </Filter>
      </Or>
    </Filter>
  </SqlQuery>
</DataConnection>

Скорректируйте MaterialInsertSetDataConnection и MaterialUpdateSetDataConnection.

Запустите приложение и попробуйте создать несколько позиций для списка товарно-материальный ценностей с разными категориями.

Как видите, независимо от того, какую категорию выбрали, в списке отображаются все записи.

Реализуйте с помощью SecondaryGetDataConnection фильтрацию данных для таблицы ТМЦ на основе выбранной в дереве DatabaseTree категории.

Самостоятельно

Как вы могли заметить, в таблицах template.material_category и template.material есть колонки archive, которые мы используем в функциях на удаление категорий и ТМЦ.

Ваша задача реализовать на форме списка ТМЦ фильтр архивных и актуальных записей и кнопки для работы с архивом категорий и ТМЦ.

Для этого вам понадобятся функции:

Функция для отправки в архив ветки категорий и ТМЦ, принадлежащих этим категориям

CREATE OR REPLACE FUNCTION template.material_category_archive_child(in_material_category_id bigint)
  RETURNS void AS
$BODY$
DECLARE
  _archive boolean;
  _material_category_id bigint;
BEGIN
  _archive = archive FROM template.material_category WHERE material_category_id = in_material_category_id;
  
  IF (NOT _archive) THEN RETURN; END IF;

  UPDATE template.material
  SET archive = _archive
  WHERE material_category_id = in_material_category_id;

  FOR _material_category_id IN (SELECT material_category_id FROM template.material_category WHERE parent_material_category_id = in_material_category_id)
  LOOP
  
    UPDATE template.material_category
    SET archive = _archive
    WHERE material_category_id = _material_category_id;

    PERFORM template.material_category_archive_child(_material_category_id);
    
  END LOOP;
  
END;
$BODY$
  LANGUAGE plpgsql;

Функция восстановления всех родительских категорий у выбранной категории

CREATE OR REPLACE FUNCTION template.material_category_unarchive_parent(in_material_category_id bigint)
  RETURNS void AS
$BODY$
DECLARE
  _parent_material_category_id bigint;
  _archive boolean;
BEGIN

  SELECT parent_material_category_id, archive
  INTO _parent_material_category_id, _archive
  FROM template.material_category
  WHERE material_category_id = in_material_category_id;
  
  IF (_archive) THEN RETURN; END IF;

  IF (_parent_material_category_id IS NOT NULL)
  THEN
  
    UPDATE template.material_category
    SET archive = _archive
    WHERE material_category_id = _parent_material_category_id;

    PERFORM template.material_category_unarchive_parent(_parent_material_category_id);
    
  END IF;
  
END;
$BODY$
  LANGUAGE plpgsql;

Последовательность запросов для архивации категорий ТМЦ:

Template.xml

<SqlQuery Name="MaterialCategoryArchiveSqlQuery">
  <Text>
    UPDATE template.material_category
    SET archive = {Archive}
    WHERE material_category_id = {MaterialCategoryId};

    SELECT template.material_category_archive_child({MaterialCategoryId})
    WHERE {Archive};
    
    SELECT template.material_category_unarchive_parent({MaterialCategoryId})
    WHERE NOT {Archive};
  </Text>
</SqlQuery>

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

Последовательность запросов для архивации ТМЦ:

Template.xml

<SqlQuery Name="MaterialArchiveSqlQuery">
  <Text>
    UPDATE template.material
    SET archive = {Archive}
    WHERE material_id = {MaterialId};

    UPDATE template.material_category MC
    SET archive = False
    FROM template.material M
    WHERE
      MC.material_category_id = M.material_category_id AND
      material_id = {MaterialId} AND
      NOT M.archive;

    SELECT template.material_category_unarchive_parent(M.material_category_id)
    FROM template.material M
    WHERE
      M.material_id = {MaterialId} AND
      NOT M.archive;
  </Text>
</SqlQuery>

При восстановлении ТМЦ из архива необходимо восстановить ее категорию и все родительские категории.

Запросы MaterialCategoryArchiveSqlQuery и MaterialArchiveSqlQuery используйте на соответствующих кнопках на форме списка ТМЦ.

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

MaterialCategoryListSelectSqlQuery

Template.xml

<SqlQuery Name="MaterialCategoryListSelectSqlQuery">
  <Text>
    SELECT
      MC.material_category_id AS "MaterialCategoryId",
      MC.title || CASE WHEN archive THEN ' (арх.)' ELSE '' END AS "Title",
      MC.title AS "OriginalTitle",
      MC.archive AS "Archive",
      MC2.material_category_id NOTNULL AS "ArchiveForFilter"
    FROM
      template.material_category MC
      LEFT JOIN
      (
        WITH RECURSIVE archive_tree (material_category_id, parent_material_category_id) AS(
          SELECT
            material_category_id,
            parent_material_category_id
          FROM
            template.material_category MC
          WHERE
            MC.archive AND NOT EXISTS (SELECT * FROM template.material_category MC2 WHERE MC2.parent_material_category_id = MC.material_category_id) OR
            EXISTS (SELECT * FROM template.material M WHERE M.archive AND M.material_category_id = MC.material_category_id)
    
          UNION
    
          SELECT
            MC.material_category_id,
            MC.parent_material_category_id
          FROM
            template.material_category MC
            JOIN archive_tree T ON (T.parent_material_category_id = MC.material_category_id)
        )
        SELECT DISTINCT material_category_id FROM archive_tree
      ) MC2 ON (MC2.material_category_id = MC.material_category_id)
    ORDER BY MC.title, MC.material_category_id;
  </Text>
</SqlQuery>

В соединение с данными MaterialCategoryPrimaryGetDataConnection для списка категорий добавим фильтр архивных записей:

MaterialCategoryPrimaryGetDataConnection

TemplateMaterialList.xml

<DataConnection Name="MaterialCategoryPrimaryGetDataConnection" Type="PrimaryGetDataConnection" Assembly="DataConnections">
  <SqlQueries>
    <SqlQuery Name="MaterialCategoryListSelectSqlQuery" Type="Select">
      <Workflow Name="Template" />
      <Fields>
        <Field Name="MaterialCategoryId" />
        <Field Name="Title" />
        <Field Name="Archive" />
        <Field Name="ArchiveForFilter" />
      </Fields>
      <Filter Type="Nested">
        <Or>
          <Filter FilterByNullValue="False">
            <Field NativeName="Archive" />
            <Value>
              <Object Name="ArchiveFilterComboBox" />
            </Value>
            <DataType Type="BooleanDataType" />
          </Filter>
          <Filter FilterByNullValue="False">
            <Field NativeName="ArchiveForFilter" />
            <Value>
              <Object Name="ArchiveFilterComboBox" />
            </Value>
            <DataType Type="BooleanDataType" />
          </Filter>
        </Or>
      </Filter>
    </SqlQuery>
    <SqlQuery Name="MaterialCategoryRelationSelectSqlQuery" Type="Select">
      <Workflow Name="Template" />
      <Fields>
        <Field Name="MaterialCategoryId" />
        <Field Name="ParentMaterialCategoryId" />
      </Fields>
    </SqlQuery>
  </SqlQueries>
</DataConnection>

Для дерева MaterialCategoryDatabaseTree используйте вложенный тэг <Sorted> со значением True, чтобы при изменении фильтра узлы дерева сохраняли сортировку.

Итоги

На уроке мы познакомились с объектом DatabaseTree, который отображает древовидную структуру данных. Также создали пару списков, которые будем использовать в следующем уроке для расширения возможностей карточки заказа.

Ответы

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

403KB

lesson9-answer.zip