Полезные хранимые процедуры¶

ℹ️ Обратите внимание: данный раздел актуален для MSSQL. У PostgreSQL имеется своя специфика работы с хранимыми процедурами — как правило они используются для выполнения обновления данных. Для получения значений рекомендуется использовать пользовательские функции или представления

Вместе с платформой "Первая Форма" поставляются некоторые полезные хранимые функции, которые можно использовать при настройке системы.

Процедуры¶

ShowTasksFeed¶

Служит для получения списка задач. Поддерживает сортировки, группировки, пейджинг и различные фильтры Используется на фронте в следующих местах: -Лента задач oВсе типы (/api/tasks/feedsBySP) -Представления категории oГрид (/api-core/datasource/subcat/{subcatId}/data) oКанбан (/api/tasks/feedsBySP) oЛента задач (все типы) (/api/tasks/feedsBySP) -Чаты oВсе/Личные/Группы (/api/chats) oПодписка/Избранное (/api/tasks/feedsBySP) -Мои задачи oЗаказчик (/api-core/datasource/owner/data) oИсполнитель (/api-core/datasource/performer/data) oПодписка (/api-core/datasource/mytasks/data) oСогласованные вами (/api-core/datasource/Signatures/data) oОтклоненные вами (/api-core/datasource/Signatures/data) Вызовы ShowTasksFeed сохраняются в специализированную таблицу dba_util.ShowTasksFeed_Execlog,что позволяет анализировать, какой пользователь и в какое время инициировал данный запрос. Для детального изучения конкретного вызова необходимо по колонке Recid из таблицы логов вызвать функцию dba_util.ComposeShowTasksFeedSttFromLog. Если вывод результата запроса настроен корректно и не подвергается обрезке, результатом работы функции будет полная команда ShowTasksFeed со всеми исходными параметрами, что позволяет точно воспроизвести первоначальный запрос для диагностики или аудита. Аргументы Аргумент | Тип данных | Описание @UserID | int | Пользователь, получающий данные. Проверка прав и применение фильтров далее происходит в контексте данного пользователя @ResultRowsCount | int = 50 | Количество получаемых задач @PartitionRowsCount | int = null | Не используется @ResultSetOffset | int = 0 | Пропустить количество задач @MinTaskID | int = null | Начиная с указанного номера задачи @MaxTaskID | int = null | Заканчивая указанным номерм задачи @OpenOnlyF | bit = 0 | Только открытые задачи @ClosedOnlyF | bit = 0 | Только закрытые задачи @FavoritesOnlyF | bit = 0 | Только задачи, добавленные пользователем в избранное @TaskHelperOnlyF | bit = 0 | Только если пользователь в исполнителях @SubscriberOnlyF | bit = 0 | Только если пользователь в подписчиках @MyTasksOnlyF | bit = 0 | Только если пользователь — заказчик @AcceptantOnlyF | bit = 0 | Только если пользователь — акцептант. Определяет, должна ли выборка ограничиваться только теми задачами, где текущий пользователь или его принципал является акцептантом @AnyInvolvanceF | bit = 0 | Выбор задач, где пользователь задействован в любой возможной роли.

@SignatureStateIDs | dbo.id_tbl_type readonly | Только с подписями в заданных состояниях @OutLastCommentF | bit = 0 | Отдавать последний комментарий @LastCommentUnreadOnlyF | bit = 0 | Выводить вместо последнего комментария по времени отправки последний непрочитанный комментарий. Имеет смысл только при установленном флаге @OutLastCommentF @TaskCommentsStatF | bit = 0 | Включить в отдачу информацию о количестве комментариев, в адресатах которых есть текущий пользователь @IncludeReationsF | bit = 0 | Включить информацию по реакциям к последнему комментарию @TopCommentersCount | int = 0 | Включить информацию по активным комментаторам @ResultsetFormat | int = 0 | Режим отдачи результата

| | 0 — Стандартный полный (По умолчанию)

| | 1 — Краткий, только TaskID

@Aux_ReenterLevel | int = 0 | Не используется @EnforceDynamicF | bit = 0 | Не используется @DebugF | bit = 0 | Флаг отладочной печати и вывода @UseSearchYoficationF | bit = 0 | Флаг использования ёфикации. Управляет процессом нормализации букв "е" и "ё" в поисковом запросе. Если флаг установлен в 1, система будет считать буквы "е" и "ё" идентичными при поиске @TrigramSearchUsedF | bit = 0 | Флаг использования триграммного поиска. Разбивает слова на последовательности по 3 символа и ищет совпадения по ним Одновременное использование флагов MyTasksOnlyF, TaskHelperOnlyF, SubscriberOnlyF, AcceptantOnlyF и ExtParamUserInvolvanceF работает по принципу объединения (не пересечения). Это означает, что выборка будет содержать задачи, в которых пользователь задействован в любой из указанных ролей: является заказчиком (MyTasksOnlyF), исполнителем (TaskHelperOnlyF), подписчиком (SubscriberOnlyF), акцептантом (AcceptantOnlyF) или указан в дополнительном параметре "Выбор пользователя" (ExtParamUserInvolvanceF). crm_repGetParams:

Выделяет из xml-структуры отдельные параметры и представляет их в виде таблицы Входные параметры: o@XmlParam xml В xml-структуре могут быть ключи: Name, Value, ValueRaw, From, To Возвращает таблицу, каждая строка которой описывает один параметр из xml-структуры. Колонки таблицы: | Колонка | Описание | | --- | --- | | Name | Название параметра | | Value | Значение параметра | | From | Начальное значение (для периода) | | To | Конечное значение (для периода) |

tc_SubcategoryDenormalize:

Выполняет денормализацию категории Входные параметры: -@SubcatID int — можно не указывать, если заданы @TaskID или @IDs -@TaskID int --- указывать при обновлении одной задачи -@MinTaskID int, @MaxTaskID int — указывать при обновлении диапазона задач -@IDs id_tbl_type — (массив) указывать при обновлении нескольких задач -@Action varchar(16) = \'update\' — возможные значения: update, insert, merge Если не указан ни один из параметров @TaskID, @MinTaskID, @MaxTaskID и @IDs, то выполняется денормализация категории @SubcatID. Возвращает таблицу с денормализованной категорией или задачами tc_ws_UploadEpFile:

Выполняет загрузку файла в ДП Входные параметры: -@UserID int -@TaskID int -@ExtParamID int -@FileName varchar -@FileData nvarchar EnsureDenormExtParamIndexes:

Анализирует наличие индекса на ДП Входные параметры: -@ExtParamID int — ID ДП -@ThresholdTaskCount int — Пороговое количество задач, необязательный параметр Возвращает таблицу с полями SubcatID, SubcatDescription, ExtParamName, MakeMeHappyStatement, ExistingSuitableIndexName, ColMaxLength, AlreadyExistsF, KeyMaxLengthNotRestrictedF, created_ix.KeyMaxLenExceededF, maxlenstt_data.TaskCount, SubcatTaskTextMaxLength ,maxlenstt_data.FactMaxLength SubcatTickers_refresh:

Выполняет обновление тикеров Входные параметры: -@SubcatID int -@DebugF bit tc_DeleteTasks:

Удаляет задачи в системе Входной параметр: -@TaskIDs id_tbl_type FileStorageFilePermissions:

Вычисляет права пользователей к файлам Диска Входной параметр: -@UserID int AddUserToRecipientsOfTask:

Позволяет при подписке получить пользователю доступ не только к новым, но и ко всем ранее опубликованным постам сообщества или пользователя, созданными до момента подписки. Вызывается при подписке пользователя к задачам категории с типом "Группы соц. сети" Входные параметры: -@UserID int — ID текущего пользователя -@TaskID int — Номер задачи (сообщества), на которую пользователь подписался DiskSpeedBenchmark:

Тестирование производительности диска, путем измерения скорости записи и чтения файлов размером 200 МБ Предназначена для проведения теста производительности (бенчмарка) дисковой подсистемы в БД. Она измеряет скорость записи и чтения больших объемов данных, что позволяет оценить реальную эффективность работы хранилища. Процедура создает временную тестовую таблицу Test_PreUploadedOnPostTaskFiles в базе данных, если она не существует, и готовит тестовый блок данных объемом 200 МБ. Измеряется время, за которое сервер может записать этот 200 МБ блок данных в созданную таблицу. Результат в миллисекундах выводится в сообщениях SQL Server. После записи процедура считывает только что записанные данные и измеряет время, затраченное на эту операцию. Результат также выводится в сообщениях. По завершении теста временная таблица удаляется. Входные параметры: -@FileContent varbinary(max) -@StartDT datetime2 -@FileId int -@EndOfInsertDT datetime2 -@EndOfSelectDT datetime2

Функции, возвращающие скалярную величину¶

cm_ReplaceEverything¶

Заменяет в строке служебные символы Входные параметры: -@String nvarchar(max) Возвращает @String nvarchar(max) Заменяет в строке @String символы & < > « » " « » \ , \ на &, \<, >, " и перевод строки, \ на \, \ на \, \<s> на \<strike>, \<ol> и \<li> на буллет, \<ul> удаляется, \<span style="color:\' на \<font color=\', \<span style="background-color: удаляется

ℹ️ При использовании данной функции в SQL-запросе на большой выборке задач создается большая нагрузка на сервер. По возможности, перенесите операцию удаления тегов с сервера на клиент. Например, при использовании JS-вставок можно использовать замену:

myString.replace(/<[^>]*>?/gm, '')

cm_StripHTML:

Убирает из строки все теги (все, что находится между символами \< и >) Входные параметры: -@HTMLText varchar(max) Возвращает @String varchar(max)

ℹ️ При использовании данной функции в SQL-запросе на большой выборке задач создается большая нагрузка на сервер. По возможности, перенесите операцию удаления тегов с сервера на клиент. Например, при использовании JS-вставок можно использовать замену:

myString.replace(/<[^>]*>?/gm, '')

RussianMonthName:

Возвращает название месяца на русском языке Входные параметры: -@MonthNumber int Возвращает varchar(50) tc_AddWorkingDays, tc_AddWorkingHours, tc_AddWorkingMinutes, tc_AddWorkingMonths, tc_AddWorkingYears:

Прибавляет к дате указанное число рабочих дней (часов, минут, месяцев, лет) Входные параметры:

-@DateTime datetime, -@Days int Возвращает datetime tc_DiffWorkingDays, tc_DiffWorkingHours, tc_DiffWorkingMinutes:

Считает разницу между двумя датами в рабочих днях (часах, минутах) Входные параметры:

-@BeginDate datetime, -@EndDate datetime Возвращает int tc_fnForSmartFormatDateEx:

Преобразует исходную дату в строку указанного формата Входные параметры: -@Datetime datetime, -@FormatMask nvarchar(max), -@Locale varchar(32) = \'ru\' Возвращает nvarchar(max) -В параметре @Datetime передается исходная дата. -В параметре @FormatMask передается маска формата, к которому надо привести исходную дату. В маске можно использовать следующие элементы: | Маска | Описание | | --- | --- | | yyyy | Год, 4 цифры | | yy | Год, 2 цифры | | MONTH | Полное название месяца на языке локали, заглавными буквами | | Month | Полное название месяца на языке локали, с заглавной буквы | | month | Полное название месяца на языке локали, строчными буквами | | MON | Три первые буквы названия месяца на языке локали, заглавными буквами | | Mon | Три первые буквы названия месяца на языке локали, с заглавной буквы | | mon | Три первые буквы названия месяца на языке локали, строчными буквами | | mm | Номер месяца, 2 цифры (слева дополняется нулем) | | m | Номер месяца, 1 или 2 цифры (нулем не дополняется) | | dd | День, 2 цифры (слева дополняется нулем) | | d | День, 1 или 2 цифры (нулем не дополняется) | | hh | Часы, 2 цифры (слева дополняется нулем) | | mi | Минуты, 2 цифры (слева дополняется нулем) | | ss | Секунды, 2 цифры (слева дополняется нулем) | Пример: \'dd Month yyyy\'. -В формате @Locale передается локаль, а для русской локали — падеж. Возможные значения: | Локаль | Описание | | --- | --- | | 'en' | Английский | | 'ru' | Русский именительный падеж | | 'ru/case-r' | Русский родительный падеж | rss_RubPhrase:

Cумма в рублях прописью Входные параметры: -@Value money Возвращает строку с указанной суммой в рублях (прописью) и копейках (числом). Пример реализации:

DECLARE @Value money = (SELECT ExtParamXXXNativeValue FROM dbo.TasksInSubcatXXXDenormalized WHERE TaskId = @ContextID)
SELECT dbo.rss_RubPhrase(@Value)

rss_NumPhrase:

Целое число прописью с учетом мужского/женского рода Входные параметры: -@Num BIGINT -@IsMaleGender bit=1 Возвращает строку с указанным числом прописью fn_GetUnreadCommentsCount:

Расчет счетчика непрочитанных комментариев в чатах Входные параметры: -@UserID int -@ForChatF bit Возвращает строку с количеством непрочитанных комментариев fn_Tasks_ToSignCount:

Расчет счетчика подписей Входные параметры: - @UserID int -@SignatureStateID int Возвращает строку с количеством подписей, для которых требуется вынести решение по резолюции

Функции, возвращающие таблицу¶

crm_GetMultiValues¶

Раскладывает строку на значения и возвращает их в виде таблицы Входные параметры: -@String varchar(max) — содержит значения, разделенные символом @Delimiter (например, значения, перечисленные через запятую), -@Delimiter varchar(max) — содержит символ-разделитель (например, \',\') Возвращает таблицу с колонкой value

ℹ️ Вместо crm_GetMultiValues удобнее использовать fn_rep_params

fn_rep_params:

Выделяет из xml-структуры отдельные параметры и возвращает их в виде таблицы Входные параметры: -@XmlParam xml В xml-структуре могут быть ключи: Name, Value, ValueRaw, From, To Возвращает таблицу, каждая строка которой описывает один параметр из xml-структуры. Колонки таблицы: | Колонка | Описание | | --- | --- | | Name | Название параметра | | Value | Значение параметра | | ValueRaw | Значение параметра (raw) | | From | Начальное значение (для периода) | | To | Конечное значение (для периода) | fn_split_string:

Раскладывает строку на значения и возвращает их в виде таблицы Входные параметры: -@String varchar(max), -@Delimiter varchar(max) Возвращает таблицу с колонками id (порядковый номер) и value fn_TaskColor:

Добавляет цвет текста и фона полям с списке задач в категории в зависимости от условий Входные параметры: -@TaskID int, -@UserId int) При выводе списка задач в категории этот список автоматически объединяется (делается join) с результатом выполнения функции fn_TaskColor, и в итоговом результате отдельные поля и значения могут получать свои собственные атрибуты цвета для текста и фона. Стилизовать можно следующие поля: -Текст задачи -Статус -Срок -Заказчик -Исполнитель -Плановые трудозатраты -Фактические трудозатраты Функция поставляется вместе с платформой. Администратор должен вручную отредактировать функцию так, чтобы она возвращала нужные значения (например, добавить UNION ALL для определенной категории). [Пример](../

CREATE function [dbo].[fn_TaskColor]
(
   @TaskID           int null,
   @UserID           int null
)
returns table
as
return
  select
       '' StateTextColor,
       '' StateBackroundColor,
       '' TaskTextTextColor,
       '' TaskTextBackroundColor,
       '' DueDateTextColor,
       '' DueDateBackroundColor,
       '' OwnerTextColor ,
       '' OwnerBackroundColor,
       '' PerformerTextColor,
       '' PerformerBackroundColor,
       '' PlanTextColor,
       '' PlanBackroundColor,
       '' FactTextColor,
       '' FactBackroundColor
  where not exists ( select 1 from TasksInSubcat999Denormalized where TaskID = @TaskID ) -- это все остальные задачи в системе

  union all

  select
       '#ffffff' StateTextColor,       -- белый шрифт у поля Статус
       '#ff0000' StateBackroundColor,   -- красный фон у поля Статус
       '' TaskTextTextColor,
       '' TaskTextBackroundColor,
       '' DueDateTextColor,
       '' DueDateBackroundColor,
       '' OwnerTextColor ,
       '' OwnerBackroundColor,
       '' PerformerTextColor,
       '' PerformerBackroundColor,
       '' PlanTextColor,
       '' PlanBackroundColor,
       '' FactTextColor,
       '' FactBackroundColor

   -- условие ниже — это отбор задач из категории 999, которые "зависли" без движения
   -- более чем на 10 дней в статусе Выполняется (ID = 2)
  where exists (
      select
       1
      from
      TasksInSubcat999Denormalized
      where
      TaskID = @TaskID
      and StateID = 2
      and DATEDIFF ( Day, ModifiedDate, GETDATE () ) > 10
   )
```> ℹ️ Функция может использоваться для выделения критически важных значений в отдельных категориях. Не следует слишком "утяжелять" запрос, т.к. это будет замедлять вывод списка задач во всех категориях

fn\_UserTaskPermissions:

Определяет права доступа пользователя к задачам в указанной категории
Входные параметры:
-@UserID int,
-@SubcatID int
Возвращает строки таблицы Tasks с задачами из категории @SubcatID, к которым пользователь @UserID имеет доступ
tc\_GetUsersOrgUnitsRecurcive:

Определяет принадлежность пользователя к уровням орг.структуры
Входные параметры:
-@UserID int
Возвращает атрибуты Id, Name, ParentId, OrgStructureTypeId, IsActual, DoNotShowInOrgStructure, IsDirector  LinkedGroupId, GuidFrom1C, IsFuncGroup, AppointmentId, Description, LocalizedNameId из таблицы UserOrgStructureUnit, относящиеся к пользователю @UserID
fn\_UserDisplayName:

Получает отображаемое имя пользователя (DisplayName) в режиме представления имен сессионного пользователя
Входные параметры:
-   @UserID   int — ID пользователя, имя которого нужно получить
-   @SessionUserID int — ID пользователя, совершающего действие
```sql
ALTER function [dbo].[fn_UserDisplayName]
(
  @UserID   int, -- Id пользователя, имя которого нужно получить
  @SessionUserID int  -- Id пользователя, совершающего действие
)
returns table
as
return
(
  select
          unm.DisplayName
  from
          dbo.Users sess_user with(nolock)
          left join
          dbo.UserNameModes unm
              on unm.UserNameMode = sess_user.UserNameMode and
                 unm.LanguageID = sess_user.LanguageID and
                 unm.UserID = @UserID
  where
          sess_user.UserID = @SessionUserID
);

Возвращает отображаемое имя заданного пользователя для того же языка и формата, что и у текущего пользователя UserMenuItemTickes_refresh:

Расчет количества записей в кастомных индикаторах Входные параметры: -@UserID int -@EnableChatsF bit -@CustomTypesOnly bit -@DebugF bit