Платформа WT
Практика
Синтаксис
База знаний

Урок 7. Фильтры

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

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

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

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

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

Теория

Платформа Workflow Technology поддерживает четыре типа фильтров:

  • Глобальные фильтры;

  • Локальные фильтры;

  • Фильтр в колонке таблицы;

  • Табличный фильтр.

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

Четвертый тип фильтров - табличный фильтр - встроен в объект DatabaseTable.

Далее подробнее рассмотрим каждый тип фильтров.

Глобальные фильтры

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

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

Локальные фильтры

Локальные фильтры работают на форме с полученными данными без повторного обращения к серверу. Для этого используется вложенный тэг <Filter> у PrimaryGetDataConnection, SecondaryGetDataConnection, ConvertDataConnection и TreeGetDataConnection.

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

Локальный фильтр мы уже применяли в ClientSecondaryGetDataConnection в карточке заказа (TemplateOrderEdit.xml):

<DataConnection Name="ClientSecondaryGetDataConnection" Type="SecondaryGetDataConnection" Assembly="DataConnections">
  <SourceDataConnection Name="ClientPrimaryGetDataConnection" />
  <Filter>
    <Field NativeName="ClientId" />
    <Value>
      <Object Name="ClientComboBox" />
    </Value>
    <DataType Type="IntegerDataType" />
  </Filter>
</DataConnection>

Для выбранного клиента получали данные о его городе и телефоне, которые выводили в отдельные поля, а ClientPrimaryGetDataConnection использовали для выпадающего списка.

Фильтры в колонке таблицы

Этот вид фильтров привязан к колонке таблицы DatabaseTable и обрабатывается через ее вложенный тэг <Filter>.

С таким фильтром мы тоже уже работали, когда для списков клиентов и городов реализовывали работу с архивом, и нам нужно было фильтровать архивные и актуальные записи:

<Column Name="Archive" Type="DatabaseTableColumnCheckBox" Assembly="DatabaseTableColumnControls">
  <Visible>False</Visible>
  <Filter FilterNullValue="False">
    <Object Name="ArchiveFilterComboBox" />
  </Filter>
</Column>

Табличный фильтр

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

Чтобы запретить пользователю фильтровать таблицу через контекстное меню, необходимо в описании объекта DatabaseTable добавить вложенный тэг <AllowFilterColumns> со значением False.

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

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

Можем либо дополнить это выражение новыми условиями, либо сбросить фильтр и построить полностью новое выражение.

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

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

Этот момент важно понимать! Если мы редактируем таблицу DatabaseTable через ее set-проперти AddRow, AddRows, UpdateRow, UpdateRows и DeleteRowsByIndices, то изменение глобальных и локальных фильтров приведет к потере несохраненных пользовательских данных.

Практика

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

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

  • Сегодня (Today);

  • Текущая неделя (CurrentWeek);

  • Две недели (Fortnight) - текущая и предыдущая недели;

  • Период (Period) - произвольный период. По умолчанию будет текущий месяц.

Фильтры на форме

Панель фильтров

Подготовим форму под фильтры.

Увеличим ширину формы, задав в атрибуте Width тэга <Form> значение 900:

TemplateStart.xml
<Form Name="TemplateStartForm" Width="900" Height="400" BackColor="ThemeBackgroundColor" StartPosition="CenterScreen" FontStyle="ThemeFontStyle" ForeColor="ThemeForeColor" ValidationType="Flat" FlatColor="ThemeFlatColor" FlatWidth="2" StatusBar="True" MaximizeBox="True" FormBorderStyle="Sizable">

Добавим в панель ContentPanel три панели:

  • FilterPanel будем использовать как контейнер для наших фильтров;

  • DataPanel будет контейнером для таблицы заказов и кнопок редактирования;

  • FilterSeparatePanel необходима для отделения FilterPanel от DataPanel.

Скопируйте код и вставьте его перед описанием таблицы OrderDatabaseTable.

TemplateStart.xml
<MyObject Name="FilterPanel" Type="Panel" Assembly="BaseControls">
  <Top>0</Top>
  <Left>0</Left>
  <Height>
    <Object Name="ContentPanel">
      <Property Name="Height" />
    </Object>
  </Height>
  <Width>200</Width>
  <BackColor>ThemeBackgroundColor</BackColor>
</MyObject>

<MyObject Name="FilterSeparatePanel" Type="Panel" Assembly="BaseControls">
  <Top>5</Top>
  <Left>
    <Object Name="FilterPanel">
      <Property Name="Right" />
    </Object>
  </Left>
  <Height>
    <Calculate>
      <Expression>{0} - 10</Expression>
      <Items>
        <Item>
          <Object Name="ContentPanel">
            <Property Name="Height" />
          </Object>
        </Item>
      </Items>
    </Calculate>
  </Height>
  <Width>1</Width>
  <BackColor>ThemeSeparateBackgroundColor</BackColor>
</MyObject>

<MyObject Name="DataPanel" Type="Panel" Assembly="BaseControls">
  <Top>0</Top>
  <Left>
    <Object Name="FilterSeparatePanel">
      <Property Name="Right" />
    </Object>
  </Left>
  <Height>
    <Object Name="FilterPanel">
      <Property Name="Height" />
    </Object>
  </Height>
  <Width>
    <Calculate>
      <Expression>{0} - {1}</Expression>
      <Items>
        <Item>
          <Object Name="ContentPanel">
            <Property Name="Width" />
          </Object>
        </Item>
        <Item>
          <Object Name="DataPanel">
            <Property Name="Left" />
          </Object>
        </Item>
      </Items>
    </Calculate>
  </Width>
  <BackColor>ThemeBackgroundColor</BackColor>
</MyObject>

Теперь необходимо в контейнер DataPanel перенести описание таблицы и кнопки редактирования. После чего скорректируем расчет значений для высоты и ширины таблицы OrderDatabaseTable, заменив в тэгах <Height> и <Width> ContentPanel на DataPanel.

В результате у нас должна получиться форма следующего вида:

Фильтры

Теперь необходимо создать сами фильтры, которых по заданию должно быть четыре:

  • Сегодня (Today);

  • Текущая неделя (CurrentWeek);

  • Две недели (Fortnight) - текущая и предыдущая недели;

  • Период (Period) - произвольный период. По умолчанию будет текущий месяц.

В качестве фильтров будем использовать кнопки (Button).

Давайте добавим необходимые элементы на форму.

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

TemplateStart.xml
<!-- Стили фильтров -->
<Color Name="ActiveFilterColor" Red="228" Green="237" Blue="248" Alpha="255" />
<Color Name="InactiveFilterColor" Red="255" Green="255" Blue="255" Alpha="255" />

Создадим переменную Variable, в которой будем хранить ключ активного фильтра. По умолчанию будет активен фильтр "Сегодня". Описание переменной можно разместить в любом удобном месте в пределах тэга <MyObjects>. Например, внутри контейнера FilterPanel.

TemplateStart.xml
<MyObject Name="ActiveFilterVariable" Type="Variable" Assembly="SimpleControls" ChangeForm="False">
  <Value>Today</Value>
</MyObject>

Создадим команду ValueSetCommand, с помощью которой будем сохранять ключ активного фильтра в переменной ActiveFilterVariable:

TemplateStart.xml
<Command Name="ActiveFilterValueSetCommand" Type="ValueSetCommand" Assembly="Commands">
  <Object Name="ActiveFilterVariable">
    <Input />
  </Object>
</Command>

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

TemplateStart.xml
<Condition Name="FilterTodayIsActiveEqualCondition" Type="EqualCondition" Assembly="Conditions">
  <Items>
    <Item>
      <Object Name="ActiveFilterVariable" />
    </Item>
    <Item>Today</Item>
  </Items>
</Condition>

<Condition Name="FilterCurrentWeekIsActiveEqualCondition" Type="EqualCondition" Assembly="Conditions">
  <Items>
    <Item>
      <Object Name="ActiveFilterVariable" />
    </Item>
    <Item>CurrentWeek</Item>
  </Items>
</Condition>

<Condition Name="FilterFortnightIsActiveEqualCondition" Type="EqualCondition" Assembly="Conditions">
  <Items>
    <Item>
      <Object Name="ActiveFilterVariable" />
    </Item>
    <Item>Fortnight</Item>
  </Items>
</Condition>

<Condition Name="FilterPeriodIsActiveEqualCondition" Type="EqualCondition" Assembly="Conditions">
  <Items>
    <Item>
      <Object Name="ActiveFilterVariable" />
    </Item>
    <Item>Period</Item>
  </Items>
</Condition>

Создадим первую кнопку для фильтра "Сегодня":

TemplateStart.xml
<MyObject Name="TodayButton" Type="Button" Assembly="BaseControls">
  <Top>5</Top>
  <Left>10</Left>
  <Height>30</Height>
  <Width>
    <Calculate>
      <Expression>{0} - {1}*2</Expression>
      <Items>
        <Item>
          <Object Name="FilterPanel">
            <Property Name="Width" />
          </Object>
        </Item>
        <Item>
          <Object Name="TodayButton">
            <Property Name="Left" />
          </Object>
        </Item>
      </Items>
    </Calculate>
  </Width>  
  <TextAlign>MiddleCenter</TextAlign>
  <FontStyle>ButtonFontStyle</FontStyle>
  <BackColor>
    <Switch>
      <Case>
        <When>
          <Condition Name="FilterTodayIsActiveEqualCondition" />
        </When>
        <Then>ActiveFilterColor</Then>
      </Case>
      <Case>InactiveFilterColor</Case>
    </Switch>
  </BackColor>
  <FlatStyle>Flat</FlatStyle>
  <FlatBorderSize>1</FlatBorderSize>
  <FlatBorderColor>
    <Switch>
      <Case>
        <When>
          <Condition Name="FilterTodayIsActiveEqualCondition" />
        </When>
        <Then>ActiveFilterColor</Then>
      </Case>
      <Case>ButtonFlatBorderColor</Case>
    </Switch>
  </FlatBorderColor>
  <FlatMouseDownBackColor>ButtonFlatMouseDownBackColor</FlatMouseDownBackColor>
  <FlatMouseOverBackColor>ButtonFlatMouseOverBackColor</FlatMouseOverBackColor>
  <Text>Сегодня</Text>
  <Commands>
    <Command Name="ActiveFilterValueSetCommand">Today</Command>
  </Commands>
</MyObject>

Проверяя условие FilterTodayIsActiveEqualCondition, мы задаем нужные значения для цвета фона (BackColor) и цвета рамки (FlatBorderColor) кнопки. Таким образом мы будем подсвечивать кнопку активного фильтра. А в тэге <Commands> через команду ActiveFilterValueSetCommand обновляем ключ активного фильтра.

Самостоятельно создайте оставшиеся три кнопки, делая между ними отступ в 5 пикселей и заменяя "Сегодня" на "Текущая неделя", "Две недели" и "Период", а Today на CurrentWeek, Fortnight и Period.

Запустите проект и проверьте отображение кнопок будущих фильтров.

При выборе фильтра с типом "Период" должны отображаться поля с датами начала периода (StartDateDateTimePicker) и окончания периода (EndDateDateTimePicker) и кнопка (ApplyButton), по которой будут применяться выбранные даты и обновляться DC.

Скопируем код ниже и добавим его в FilterPanel вслед за описанием кнопки PeriodButton.

TemplateStart.xml
<MyObject Name="StartDateLabel" Type="Label" Assembly="BaseControls">
  <Top>
    <Calculate>
      <Expression>{0} + 10</Expression>
      <Items>
        <Item>
          <Object Name="PeriodButton">
            <Property Name="Bottom" />
          </Object>
        </Item>
      </Items>
    </Calculate>
  </Top>
  <Left>
    <Object Name="TodayButton">
      <Property Name="Left" />
    </Object>
  </Left>
  <Width>
    <Calculate>
      <Expression>{0} - {1}*2</Expression>
      <Items>
        <Item>
          <Object Name="FilterPanel">
            <Property Name="Width" />
          </Object>
        </Item>
        <Item>
          <Object Name="StartDateLabel">
            <Property Name="Left" />
          </Object>
        </Item>
      </Items>
    </Calculate>
  </Width>
  <Height>20</Height>
  <TextAlign>MiddleLeft</TextAlign>
  <FontStyle>LabelFontStyle</FontStyle>
  <ForeColor>LabelForeColor</ForeColor>
  <Text>Дата начала</Text>
  <Visible>
    <Condition Name="FilterPeriodIsActiveEqualCondition" />
  </Visible>
</MyObject>
	
<MyObject Name="StartDateDateTimePicker" Type="DateTimePicker" Assembly="BaseControls" ChangeForm="False">
  <Top>
    <Object Name="StartDateLabel">
      <Property Name="Bottom" />
    </Object>
  </Top>
  <Left>
    <Object Name="StartDateLabel">
      <Property Name="Left" />
    </Object>
  </Left>
  <Width>
    <Object Name="StartDateLabel">
      <Property Name="Width" />
    </Object>
  </Width>
  <ShowCalendar>True</ShowCalendar>
  <NullValue Show="False" />
  <Value>
    <DateTime Variable="FirstDayOfCurrMonth" />
  </Value>
  <Visible>
    <Condition Name="FilterPeriodIsActiveEqualCondition" />
  </Visible>
</MyObject>
	
<MyObject Name="EndDateLabel" Type="Label" Assembly="BaseControls">
  <Top>
    <Calculate>
      <Expression>{0} + 5</Expression>
      <Items>
        <Item>
          <Object Name="StartDateDateTimePicker">
            <Property Name="Bottom" />
          </Object>
        </Item>
      </Items>
    </Calculate>
  </Top>
  <Left>
    <Object Name="StartDateLabel">
      <Property Name="Left" />
    </Object>
  </Left>
  <Width>
    <Object Name="StartDateLabel">
      <Property Name="Width" />
    </Object>
  </Width>
  <Height>20</Height>
  <TextAlign>MiddleLeft</TextAlign>
  <FontStyle>LabelFontStyle</FontStyle>
  <ForeColor>LabelForeColor</ForeColor>
  <Text>Дата окончания</Text>
  <Visible>
    <Condition Name="FilterPeriodIsActiveEqualCondition" />
  </Visible>
</MyObject>
	
<MyObject Name="EndDateDateTimePicker" Type="DateTimePicker" Assembly="BaseControls" ChangeForm="False">
  <Top>
    <Object Name="EndDateLabel">
      <Property Name="Bottom" />
    </Object>
  </Top>
  <Left>
    <Object Name="StartDateLabel">
      <Property Name="Left" />
    </Object>
  </Left>
  <Width>
    <Object Name="StartDateLabel">
      <Property Name="Width" />
    </Object>
  </Width>
  <ShowCalendar>True</ShowCalendar>
  <NullValue Show="False" />
  <Value>
    <DateTime Variable="LastDayOfCurrMonth" />
  </Value>
  <Visible>
    <Condition Name="FilterPeriodIsActiveEqualCondition" />
  </Visible>
</MyObject>
	
<MyObject Name="ApplyButton" Type="Button" Assembly="BaseControls">
  <Top>
    <Calculate>
      <Expression>{0} + 5</Expression>
      <Items>
        <Item>
          <Object Name="EndDateDateTimePicker">
            <Property Name="Bottom" />
          </Object>
        </Item>
      </Items>
    </Calculate>
  </Top>
  <Left>
    <Object Name="TodayButton">
      <Property Name="Left" />
    </Object>
  </Left>
  <Width>
    <Object Name="StartDateLabel">
      <Property Name="Width" />
    </Object>
  </Width>
  <Height>30</Height>
  <TextAlign>MiddleCenter</TextAlign>
  <FontStyle>ButtonFontStyle</FontStyle>
  <ImageAlign>MiddleLeft</ImageAlign>
  <FlatStyle>Flat</FlatStyle>
  <FlatBorderSize>1</FlatBorderSize>
  <FlatBorderColor>ButtonFlatBorderColor</FlatBorderColor>
  <FlatMouseDownBackColor>ButtonFlatMouseDownBackColor</FlatMouseDownBackColor>
  <FlatMouseOverBackColor>ButtonFlatMouseOverBackColor</FlatMouseOverBackColor>
  <Text>Применить</Text>
  <Commands> </Commands>
  <Visible>
    <Condition Name="FilterPeriodIsActiveEqualCondition" />
  </Visible>
</MyObject>

Для задания значений по умолчанию для объектов StartDateDateTimePicker и EndDateDateTimePicker используем константы даты/времени (DateTime) FirstDayOfCurrMonth и LastDayOfCurrMonth соответственно.

Видимость новых объектов определяется условием FilterPeriodIsActiveEqualCondition.

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

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

Передача фильтров в запрос

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

TemplateStart.xml
<DataConnection Name="OrderPrimaryGetDataConnection" Type="PrimaryGetDataConnection" Assembly="DataConnections">
  <SqlQuery Name="OrderSelectSqlQuery" Type="Select">
    <Workflow Name="Template" />
    <Fields>
      <Field Name="OrderId" />
      <Field Name="ClientId" />
      <Field Name="CityId" />
      <Field Name="OrderNumber" />
      <Field Name="OrderDate" />
    </Fields>
    <Parameters>
      <Parameter NativeName="Filter" RefreshQuery="False">
        <Value>
          <Object Name="ActiveFilterVariable" />
        </Value>
      </Parameter>
      <Parameter NativeName="StartDate" RefreshQuery="False">
        <Value>
          <Object Name="StartDateDateTimePicker" />
        </Value>
      </Parameter>
      <Parameter NativeName="EndDate" RefreshQuery="False">
        <Value>
          <Object Name="EndDateDateTimePicker" />
        </Value>
      </Parameter>
    </Parameters>
  </SqlQuery>
</DataConnection>

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

Если атрибут RefreshQuery отсутствует или имеет значение True, то соединение с данными будет обновлять запрос каждый раз, когда изменяется значение параметра. Если атрибут отсутствует у нескольких параметров, то запрос будет выполняться отдельно при обновлении каждого параметра.

Если атрибут RefreshQuery имеет значение False, то соединение с данными будет обновлять запрос только по команде DataConnectionRefreshCommand.

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

Так как у нас все три параметра имеют атрибут RefreshQuery со значением False, то OrderPrimaryGetDataConnection будет получать данные с сервера при загрузке формы и при выполнении команды DataConnectionRefreshCommand.

На кнопки "Сегодня", "Текущая неделя" и "Две недели" добавим вызов команды OrderDataConnectionRefreshCommand. Она должна вызываться после команды ActiveFilterValueSetCommand. А для фильтра "Период" добавим эту команду на кнопку "Применить".

Фильтры в sql-запросе

Перейдем в файл описания работы серверной части приложения (Template.xml) и скорректируем запрос OrderSelectSqlQuery следующим образом:

Template.xml
<SqlQuery Name="OrderSelectSqlQuery">
  <Text>  
    WITH _filter AS (
      SELECT
        CASE
          WHEN {Filter} = 'Today'
          THEN CURRENT_DATE::timestamp
          WHEN {Filter} = 'CurrentWeek'
          THEN date_trunc('week', CURRENT_DATE::timestamp)
          WHEN {Filter} = 'Fortnight'
          THEN date_trunc('week', CURRENT_DATE::timestamp) - interval '7d'
          WHEN {Filter} = 'Period'
          THEN {StartDate}::timestamp
        END AS start_date,
    
        CASE
          WHEN {Filter} = 'Today'
          THEN CURRENT_DATE + interval '1d - 1s'
          WHEN {Filter} = 'CurrentWeek'
          THEN date_trunc('week', CURRENT_DATE::timestamp) + interval '7d - 1s'
          WHEN {Filter} = 'Fortnight'
          THEN date_trunc('week', CURRENT_DATE::timestamp) + interval '7d - 1s'
          WHEN {Filter} = 'Period'
          THEN {EndDate}::timestamp + interval '1d - 1s'
        END AS end_date
    )
    
    SELECT
      O.order_id AS "OrderId",
      O.client_id AS "ClientId",
      C.city_id AS "CityId",
      O.order_number AS "OrderNumber",
      O.order_date AS "OrderDate"
    FROM
      template.order O
      LEFT JOIN template.client C USING(client_id)
      LEFT JOIN _filter ON true
    WHERE
      NOT O.deleted AND
      O.order_date BETWEEN _filter.start_date AND _filter.end_date
    ORDER BY O.order_number ASC;
  </Text>
</SqlQuery>

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

В запросе с помощью предложения WITH мы создаем общее табличное выражение (Common Table Expressions, CTE) _filter, в котором в зависимости от выбранного фильтра формируем пару дат.

Для фильтра Today период фильтрации будет ограничен началом и окончанием текущего дня. Для даты начала периода используем простое выражение: CURRENT_DATE::timestamp. Сама по себе функция CURRENT_DATE возвращает текущую дату без времени (тип date), а явное приведение ее к типу timestamp добавляет к дате время 00:00:00. Для получения даты окончания периода к текущей дате прибавляем один день и отнимаем одну секунду, что добавляет к дате время 23:59:59.

Для фильтров CurrentWeek и Fortnight мы используем функцию даты/времени: date_trunc('week', CURRENT_DATE::timestamp). Данная функция отсекает компоненты даты до заданной точности. В нашем случае текущая дата будет приведена к первому дню текущей недели и будет иметь время 00:00:00. Остальные даты фильтров мы получаем из этой даты добавлением или вычитанием интервалов с учетом длительности периодов фильтрации.

Для расчета периода фильтрации для фильтра Period используем даты, переданные с формы и приведенные к типу timestamp.

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

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

Временные зоны и фильтры по дате

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

Рассмотрим пример.

В качестве временной зоны сервера используется значение Etc/GMT (значение по умолчанию). У пользователя в настройках стоит временная зона Europe/Moscow (значение по умолчанию).

Создадим первый заказ и в качестве даты заказа укажем значение 2021-08-30 02:00:00 (UTC+3). При отправке запроса на сервер клиентское приложение приведет дату со временем к UTC и отправит значение 2021-08-29 23:00:00. Сервер, получив запрос, переведет эту дату в свою временную зону Etc/GMT (UTC+0) и запишет в таблицу значение 2021-08-29 23:00:00.

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

Далее создадим второй заказ с датой 2021-08-30 13:00:00 (UTC+3), который сохранится в базу данных с датой 2021-08-30 10:00:00 (UTC+0).

Если захотим получить все заказы за 30 августа 2021 года, то CTE _filter сформирует даты: 2021-08-30 00:00:00 и 2021-08-30 23:59:59. И основной запрос вернет из таблицы только второй заказ с датой 2021-08-30 10:00:00, так как первый заказ с датой 2021-08-29 23:00:00 не попадет в период фильтрации.

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

Правила передачи времени между сервером и клиентом

Дата со временем (в формате даты) между клиентом и сервером всегда передается в UTC. Когда дату со временем передаем с формы на сервер, платформа переводит время из временной зоны клиента в UTC, а сервер полученную дату переводит из UTC в свою временную зону.

Аналогичное преобразование происходит и в обратную сторону. Сервер получает дату со временем из база данных, переводит ее в UTC и передает клиенту. Клиент, получив дату со временем, переводит ее из UTC в свою временную зону.

Если между клиентом и сервером передается только дата (без времени) или только время, то значение отправляется без перевода в UTC.

Напомню, что в базе данных мы используем тип timestamp without time zone, который не хранит информацию о временной зоне. PostgreSQL предполагает, что все временные данные принадлежат одной временной зоне, и не производит никаких преобразований. Таким образом, мы должны сами вручную приводить даты фильтров к временной зоне сервера.

Для решения данной проблемы в базе данных в схеме public есть функция convert_date_filter(), которая переводит произвольные дату и время из временной зоны пользователя во временную зону сервера.

CREATE OR REPLACE FUNCTION public.convert_date_filter(timestamp without time zone)
  RETURNS timestamp without time zone AS
$BODY$
  --используется при работе с фильтрами (когда с формы приходит дата БЕЗ времени)
  --преобразует дату с формы ко времени сервера
  --также можно использовать для преобразования любой даты (если она считается в часовом поясе клиента) ко времени сервера
DECLARE
  _time_zone_name text;
BEGIN
  
  _time_zone_name = time_zone_info.name
                    FROM
                      "user" pu
                      LEFT JOIN time_zone_info USING (time_zone_info_id)
                    WHERE
                      pu.user_id = current_setting('ws.user_id')::smallint;

  RETURN $1 at time zone _time_zone_name at time zone current_setting('ws.server_time_zone');
END;
$BODY$
  LANGUAGE plpgsql;

Первым делом из таблицы public.time_zone_info мы получаем имя временной зоны пользователя, которое сохраняем в переменную _time_zone_name. Идентификатор пользователя хранится в параметре конфигурации ws.user_id, значение которого получаем с помощью функции current_setting(). Как задаются параметры конфигурации, рассмотрим в следующем разделе.

Имя временной зоны сервера получаем из параметра конфигурации ws.server_time_zone.

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

$1 at time zone _time_zone_name at time zone current_setting('ws.server_time_zone');

где $1 - обращение к аргументу функции по его порядковому номеру.

Первое использование конструкции at time zone означает, что дата указана во временной зоне пользователя (переменная _time_zone_name). Второе использование конструкции at time zone переводит дату и время во временную зону сервера.

Итак, теперь мы можем скорректировать проверку дат заказов, добавив вызов функции convert_date_filter(), чтобы переводить даты фильтра в часовую зону сервера. Итоговый код запроса будет иметь вид:

<SqlQuery Name="OrderSelectSqlQuery">
  <Text>  
    WITH _filter AS (
      SELECT
        CASE
          WHEN {Filter} = 'Today'
          THEN CURRENT_DATE::timestamp
          WHEN {Filter} = 'CurrentWeek'
          THEN date_trunc('week', CURRENT_DATE::timestamp)
          WHEN {Filter} = 'Fortnight'
          THEN date_trunc('week', CURRENT_DATE::timestamp) - interval '7d'
          WHEN {Filter} = 'Period'
          THEN {StartDate}::timestamp
        END AS start_date,
    
        CASE
          WHEN {Filter} = 'Today'
          THEN CURRENT_DATE + interval '1d - 1s'
          WHEN {Filter} = 'CurrentWeek'
          THEN date_trunc('week', CURRENT_DATE::timestamp) + interval '7d - 1s'
          WHEN {Filter} = 'Fortnight'
          THEN date_trunc('week', CURRENT_DATE::timestamp) + interval '7d - 1s'
          WHEN {Filter} = 'Period'
          THEN {EndDate}::timestamp + interval '1d - 1s'
        END AS end_date
    )
    
    SELECT
      O.order_id AS "OrderId",
      O.client_id AS "ClientId",
      C.city_id AS "CityId",
      O.order_number AS "OrderNumber",
      O.order_date AS "OrderDate"
    FROM
      template.order O
      LEFT JOIN template.client C USING(client_id)
      LEFT JOIN _filter ON true
    WHERE
      NOT O.deleted AND
      O.order_date BETWEEN convert_date_filter(_filter.start_date)
                       AND convert_date_filter(_filter.end_date)
    ORDER BY O.order_number ASC;
  </Text>
</SqlQuery>

Функцию public.convert_date_filter() следует использовать в запросах с фильтрами по дате, когда с формы приходит только дата, а для данных в таблице в базе данных используется тип timestamp without time zone.

Параметры конфигурации

Если мы, заменив в запросе OrderSelectSqlQuery параметры {Filter}, {StartDate} и {EndDate} на конкретные значения, попытаемся выполнить его в программе для управления СУБД PostgreSQL (например, pgAdmin III), то мы словим следующую ошибку:

Ошибка говорит нам о том, что в рамках текущей транзакции не существует параметра ws.user_id. Чтобы задать новое значение параметров, необходимо воспользоваться функцией set_config().

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

SELECT
  set_config('ws.user_id', (2)::varchar, False),
  set_config('ws.public_user_id', (2)::varchar, False),
  set_config('ws.server_time_zone', ('Etc/GMT')::varchar, False);

Текст этого запроса вы могли видеть в журнале событий Windows в описании ошибки при выполнении запроса на стороне сервера:

Там же есть еще один системный запрос, который выполняется каждый раз, когда сервер обращается к базе:

SET time zone 'Etc/GMT';

С помощью SET time zone устанавливается временная зона для сервера PostgreSQL только для текущего сеанса. В качестве значения используется временная зона сервера.

Не забывайте про эти запросы, когда тестируете свои запросы в программе СУБД PostgreSQL.

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

Для удаления заказов мы используем колонку deleted, в которой помечаем удаленные заказы. Реализуйте самостоятельно форму отчета по удаленным заказам с фильтром по дате удаления (колонка date_deleted типа timestamp without time zone).

В главном меню создайте пункт Журналы -> Список удаленных заказов..., по которому будет открываться форма со списком.

Форма списка удаленных заказов должна иметь вид:

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

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

Итоги

На этом уроке мы рассмотрели виды фильтров. Глобальные фильтры, значения которых отправляются на сервер и используются в запросах для выборки данных из базы. Локальные фильтры, значения которых используются на форме для фильтрации данных в DataConnection. И два вида табличных фильтров: в колонке таблице DatabaseTable и настраиваемый фильтр, который вызывается через контекстное меню в заголовке таблицы.

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

Если в таблице DatabaseTable были совершены изменения через set-проперти AddRow, AddRows, UpdateRow, UpdateRows и DeleteRowsByIndices, то при изменении глобальных и локальных фильтров изменения будут утеряны. Табличные фильтры не изменяют источник данных для таблицы, а формируют строки для отображения.

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

При реализации фильтров рассмотрели правила работы с временными зонами.

Ответы

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

Last updated