Работа с базой данных¶
Прямой доступ к БД может использоваться для обмена данными со сторонними системами, когда другие инструменты обмена неэффективны или неприменимы.
Пример обмена данными с Microsoft Dynamics NAV¶
В рамках управления проектами в "Первой Форме" автоматизировано согласование договоров и планирование платежей по ним, а также ежемесячное формирование смет по проектам. Бюджетирование и учет поступающих платежей исторически ведутся в Microsoft Dynamics NAV. Между "Первой Формой" и Microsoft Dynamics NAV настроен обмен данными по платежам и сметам. Поскольку в обмене участвуют категории с табличными ДП, для которых не поддерживается штатный импорт данных, обмен данными реализован средствами SQL с использованием промежуточных таблиц.
-
Пример односторонней синхронизации — получение данных о поступивших платежах из NAV.
-
Пример двусторонней синхронизации — выгрузка смет и загрузка данных по бюджетам, утвержденным для этих смет (в этом примере, помимо двустороннего обмена, рассматривается синхронизация ДП "Таблица").
Логирование при обмене данными на уровне SQL¶
Обмен данными между двумя системами на уровне БД выполняется с помощью запуска хранимых процедур SQL. Запуск хранимых процедур выполняется либо по триггеру (при наступлении определенного события), либо по расписанию. При работе по расписанию администраторам важно контролировать, когда состоялся последний запуск хранимой процедуры и сколько записей было обработано. Это поможет им выявить ошибки — если запуск процедуры по каким-то причинам не произошел или если количество записей, которое было обработано, существенно отличается от среднестатистического.
Для логирования можно использовать специальную таблицу -- журнал (лог) записей о событиях обмена, а также хранимую процедуру, которая будет формировать эти записи. По каждому вызову процедуры обмена записываются: название и ID процедуры обмена, время начала и окончания выполнения, количество добавленных и обновленных записей.
Пример процедуры записи в журнал:
ALTER PROCEDURE [dbo].[cm_int_spStatEvent]
@sp VARCHAR(100),
@event VARCHAR(10),
@insCount INT = NULL,
@updCount INT = NULL,
@extStat VARCHAR(MAX) = NULL,
@result VARCHAR(MAX) = NULL,
@setChagesDate INT = NULL, -- 0 — NOT SET, 1 — SET, NULL — AUTO
@printStat INT = 0
AS
BEGIN
SET NOCOUNT ON;
IF (SELECT COUNT(*) FROM cm_int_procedures WHERE [proc] = @sp) = 0 BEGIN
INSERT INTO cm_int_procedures ([proc]) VALUES (@sp);
END;
-- START
IF @event = 'start' BEGIN
UPDATE cm_int_procedures SET last_started = GETDATE() WHERE [proc] = @sp;
END;
-- FINISH
IF @event = 'finish' BEGIN
DECLARE @stat VARCHAR(100) = '', @changesDate DATETIME;
IF @insCount IS NOT NULL BEGIN
SET @stat+= 'inserted: ' + CAST(@insCount AS VARCHAR(10)) + '; ';
END;
IF @updCount IS NOT NULL BEGIN
SET @stat+= 'updated: ' + CAST(@updCount AS VARCHAR(10)) + '; ';
END;
IF (@setChagesDate = 1 OR (@setChagesDate IS NULL AND @insCount IS NOT NULL AND @updCount IS NOT NULL AND @insCount + @updCount > 0)) BEGIN
SET @changesDate = GETDATE();
END;
UPDATE cm_int_procedures SET last_finished = GETDATE(), last_result = COALESCE(@result, @stat), changes_date = COALESCE(@changesDate, changes_Date) WHERE [proc] = @sp;
END;
-- PRINT
IF @printStat = 1 BEGIN
SELECT COALESCE(@result, @stat);
END;
END
В каждой процедуре обмена запись в журнал вызывается дважды -- в начале и в конце.
Пример вызова процедуры записи в журнал:
ALTER PROCEDURE [dbo].[cm_int_syncCat_city]
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@procName VARCHAR(50),
@insertedCount INT = 0,
@updatedCount INT = 0;
SET @procName = OBJECT_NAME(@@PROCID);
EXEC cm_int_spStatEvent @sp = @procName, @event = 'start';
...
...
EXEC cm_int_spStatEvent @sp = @procName, @event = 'finish', @insCount = @insertedCount, @updCount = @updatedCount, @printStat = 1;
END;
Данные из таблицы — журнала обмена — могут выводиться на портал администратора, например, в портальном блоке "Таблица".
Получение данных о платежах¶
Рассмотрим пример одностороннего обмена данными — данные о поступивших платежах регистрируются в NAV и передаются в "Первую Форму", в категорию "Платежи" (каждый платеж записывается в отдельную задачу).
1. В БД "Первой Формы" создается промежуточная таблица _NAVISION_PAY_NAV.
| Поле | Тип | Описание |
|---|---|---|
| Row_ID | int | Номер записи в промежуточной таблице |
| NAV_ID | nvarchar(20) | Идентификатор платежа в NAV |
| 1F_ID | nvarchar(20) | Идентификатор платежа в "Первой Форме" (ID задачи в категории "Платежи") |
| Status | tinyint | Статус синхронизации: 1 — новая запись, 2 — действует, 3 — отредактировано в 1Форме, ожидает синхронизации, 4 — запись удалена, 5 — не синхронизировано (ошибка) |
| Contragent, Smeta, State_bud, Sum, Curr, Date_pay, Comment | — | Любые необходимые данные о платеже: Контрагент, Номер сметы, Статья бюджета, Сумма платежа, Валюта платежа, Дата платежа, Назначение платежа |
2. В категории "Платежи" настраивается смарт-расписание, которое с заданной периодичностью выполняет две хранимые процедуры: загрузку новых платежей и обновление данных об уже имеющихся платежах.
Пример процедуры для загрузки новых платежей:
ALTER PROCEDURE [dbo].[NewFromNAV_PAY]
@TaskID int = null,
@SubcatID int = null,
@ExtParamID int = null,
@Mode varchar(max) = null,
@UserID int=3, -- SystemRobot
@ErrorMessage varchar(1000) = null out
AS
BEGIN
DECLARE @Task varchar(8000) = '', -- текст задачи
@OrderedTime datetime=NULL, -- желаемая дата завершения задачи в формате год-месяц-день час:мин:сек
@Category int, -- ID категории
@Comment varchar(8000) = '', -- первый комментарий
@extParamStr varchar(8000) = '', -- строка ДП
@perfID int = NULL, -- назначенный исполнитель
@priorityID int = 1, -- приоритет задачи
@D3TaskID int = 0, -- ID задачи
@ParentID int = 0, -- ID родительской задачи
@UserNick varchar(20) = '', -- ник пользователя, ставящего задачу
@remind bit = 0, -- напоминать ли о задаче
@PropagateSubscribers bit = 1
DECLARE @Str varchar (max)
DECLARE @tid varchar (max)
DECLARE @ID int
DECLARE @NumberNav varchar (max)
DECLARE @ErrorMessageFan varchar (max)
DEClARE @res int
SET @Category = 32
DECLARE @cur cursor
-- отбор платежей, поступивших из NAV, которых еще нет в 1Форме
SET @cur = cursor scroll FOR
SELECT DISTINCT
a.NAV_ID,
'#n191#v' + ISNULL(a.NAV_ID,'') -- Наименование
+::'#n365#v' + ISNULL(a.NAV_ID,'') -- Номер платежа в Навижн
+::'#n71#v' + ISNULL(CAST(b1.TaskID AS varchar(max)),'') -- Контрагент
+::'#n345#v' + ISNULL(CAST(b.TaskID AS varchar(max)),'') -- Смета
+::'#n366#v' + ISNULL(CAST(b2.TaskID AS varchar(max)),'') -- Статья бюджета
+::'#n134#v' + ISNULL(CAST(CAST(a.[Sum] AS decimal(16,2)) AS varchar(max)),'') -- Сумма
+::'#n240#v'+ CASE
WHEN ISNULL(CAST(a.[Curr] AS varchar(max)),'')=''
THEN 'RUB'
ELSE CAST(a.[Curr] AS varchar(max))
END -- Код оригинальной валюты
+::'#n135#v' + ISNULL(CAST(a.[DATE_PAY] AS varchar(max)),'') -- Дата платежа
+::'#n137#v' + ISNULL(a.[Comment],'') -- Номер счета, дата
+::'#n364#v' + ISNULL(CAST(a.[Status] AS varchar(max)),'') -- Статус записи
+::'#n382#v' + ISNULL(CAST(a.Rowid AS varchar(8000)),'') -- Номер строки
+'::''
FROM [dbo].[_NAVISION_PAY_NAV] a -- промежуточная таблица для обмена данными о платежах
LEFT JOIN TasksInSubcat34Denormalized b -- категория "Сметы"
ON a.[Smeta] = b.extparam141value
LEFT JOIN TasksInSubcat18Denormalized b1 -- категория "Контрагенты"
ON a.[Contragent] = b1.extparam214value
LEFT JOIN TasksInSubcat95Denormalized b2 -- категория "Статьи бюджета"
ON a.[State_bud] = b2.extparam389value
WHERE a.NAV_ID NOT IN (select extparam365value from TasksInSubcat32Denormalized) -- ДП "Номер платежа в NAV" в категории "Платежи"
-- перебор полученных результатов
OPEN @cur
FETCH NEXT FROM @cur INTO @NumberNav, @tID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ErrorMessageFan = NULL
SET @ErrorMessage = NULL
SET @extParamStr=(select @tid)
-- создание новой задачи в 1Форме в категории "Платежи" по переданным данным
IF EXISTS (select name from sysobjects where name = 'tc_ws_newTask')
BEGIN
exec @res = tc_ws_newTask @UserID, @Task, @OrderedTime, @Category, @Comment, @extParamStr,
@perfID, @priorityID, @D3TaskID, @ParentID, @UserNick,
@remind, @PropagateSubscribers
END
-- если задача создалась без ошибки, в запись в промежуточной таблице записывается номер задачи в 1Форме, для дальнейшей синхронизации
IF (@res != '0' and @res != '')
BEGIN
UPDATE [dbo].[_NAVISION_PAY_NAV]
SET [Status] = 2, [1F_ID] = @res
WHERE NAV_ID = @NumberNav
END
-- если возникла ошибка при создании задачи, платеж в промежуточной таблице помечается как не синхронизированный
ELSE
BEGIN
UPDATE [dbo].[_NAVISION_PAY_NAV]
SET [Status] = 5
WHERE NAV_ID = @NumberNav
END
FETCH NEXT FROM @cur INTO @NumberNav, @tID
END
CLOSE @cur
DEALLOCATE @cur
END
Пример процедуры для обновления существующих платежей:
ALTER PROCEDURE [dbo].[UpdateFromNAV_PAY]
@TaskID int = null,
@SubcatID int = null,
@ExtParamID int = null,
@Mode varchar(max) = null,
@UserID int=3, -- SystemRobot
@ErrorMessage varchar(1000) = null out
AS
BEGIN
DECLARE
@Task varchar(8000) = '', -- текст задачи
@OrderedTime datetime=NULL, -- желаемая дата завершения задачи в формате год-месяц-день час:мин:сек
@Category int, -- ID категории
@Comment varchar(8000) = '', -- первый комментарий
@extParamStr varchar(8000) = '', -- строка ДП
@perfID int = NULL, -- назначенный исполнитель
@priorityID int = 1, -- приоритет задачи
@D3TaskID int = 0, -- ID задачки в Д3Таск
@ParentID int = 0, -- ID родительской задачи
@UserNick varchar(20) = '', -- ник пользователя, ставящего задачу
@remind bit = 0, -- напоминать ли о задаче
@PropagateSubscribers bit = 1,
@Str varchar (8000),
@tid int,
@id int,
@TaskTEMP int,
@CancelStateID int = 16
DECLARE @Temp table (t int, dp varchar(8000))
SET @Category=32
DECLARE @cur cursor
INSERT INTO @Temp
-- отбор платежей, поступивших из NAV, в статусе "Ожидает обновления"
SELECT DISTINCT
d.taskid,
'#n191#v' + ISNULL(a.NAV_ID,'') -- Наименование
+::'#n365#v' + ISNULL(a.NAV_ID,'') -- Номер платежа в Навижн
+::'#n71#v' + ISNULL(CAST(b1.TaskID AS varchar(8000)),'') -- Контрагент
+::'#n345#v' + ISNULL(CAST(b.TaskID AS varchar(8000)),'') -- Смета
+::'#n366#v' + ISNULL(CAST(b2.TaskID AS varchar(8000)),'') -- Статья бюджета
+::'#n134#v' + ISNULL(CAST(CAST(a.[Sum] AS Decimal(16,2)) as varchar(8000)),'') -- Сумма
+::'#n240#v' + CASE
WHEN ISNULL(a.[Curr],'') = ''
THEN 'RUB'
ELSE a.Curr
END -- Код оригинальной валюты
+::'#n135#v' + ISNULL(CAST(a.[DATA_PAY] as varchar(8000)),'') -- Дата платежа
+::'#n137#v' + ISNULL(a.[Comment],'') -- Номер счета, дата
+::'#n364#v' + ISNULL(CAST(a.[Status] AS varchar(8000)),'') -- Статус записи
+::'#n382#v' + ISNULL(CAST(a.Rowid AS varchar(8000)),'') -- Номер строки
+'::''
FROM [dbo].[_NAVISION_PAY_NAV] a -- промежуточная таблица для обмена данными о платежах
JOIN TasksInSubcat32Denormalized d -- категория "Платежи"
ON d.TaskID = a.[1F_ID]
LEFT JOIN TasksInSubcat34Denormalized b -- категория "Сметы"
ON a.[Smeta] = b.extparam141value and b.StateId not in (@CancelStateID)
LEFT JOIN TasksInSubcat18Denormalized b1 -- категория "Контрагенты"
ON a.[Contragent] = b1.extparam214value
LEFT JOIN TasksInSubcat95Denormalized b2 -- категория "Статьи бюджета"
ON a.[State_bud] = b2.extparam389value
WHERE a.[Status] IN (3,4) OR @Mode = 'All'
SET @cur = cursor scroll for
SELECT t,dp
FROM @Temp
-- перебор полученных результатов
OPEN @cur
FETCH NEXT FROM @cur INTO @tID, @extParamStr
WHILE @@FETCH_STATUS = 0
BEGIN
-- обновление задачи в 1Форме в категории "Платежи" по переданным данным
EXEC [dbo].[tc_ws_updateExtParamsInTasks] @tid, @extParamStr, @SessionUserID = 3
-- платеж в промежуточной таблице помечается как действующий
UPDATE b
SET b.[Status] = 2
FROM [dbo].[_NAVISION_PAY_NAV] b
JOIN TasksInSubcat32Denormalized a on a.TaskID = [1F_ID]
WHERE b.[1F_ID] = @tID AND b.[Status] = 3
FETCH NEXT FROM @cur INTO @tID, @extParamStr
END
CLOSE @cur
DEALLOCATE @cur
END
Обмен данными о сметах¶
Рассмотрим пример двустороннего обмена данными — сметы создаются и редактируются в "Первой Форме" и передаются в NAV; из NAV поступают данные по бюджетам, выделенным по сметам. Работа ведется в категории "Сметы", в каждой задаче есть табличный ДП "Финансы" с колонками "Статья затрат", "Сумма к оплате", "Оплачено", "Дата оплаты" (при необходимости в таблицу могут быть добавлены и другие колонки — например, ссылка на договор, ссылка на контрагента, номер платежа и пр.).
Для удобства синхронизация выполняется с помощью двух промежуточных таблиц: одна для задач, другая для табличного ДП "Финансы".
1. В БД "Первой Формы" создаются две промежуточные таблицы.
Промежуточная таблица для синхронизации задач _NAVISION_Smeta_NAV:
| Поле | Тип | Описание |
|---|---|---|
| Row_ID | int | Номер записи в промежуточной таблице |
| NAV_ID | nvarchar(20) | Идентификатор сметы в NAV |
| 1F_ID | nvarchar(20) | Идентификатор сметы в "Первой Форме" (ID задачи в категории "Сметы") |
| Status | int | Статус синхронизации: 1 — новая запись, 2 — отредактирован в NAV, ожидает синхронизации, 3 — действует, 4 — отредактирован в 1Форме, ожидает синхронизации, 5 — не синхронизировано (ошибка) |
| Period, Department | — | Любые необходимые данные о смете, например: Период, Подразделение |
Промежуточная таблица для синхронизации табличного ДП "Финансы" _NAVISION_Smeta_table_NAV:
| Поле | Тип | Описание |
|---|---|---|
| Row_ID | int | Номер записи в промежуточной таблице |
| NAV_ID | nvarchar(20) | Идентификатор сметы в NAV |
| 1F_ID | nvarchar(20) | Идентификатор сметы в "Первой Форме" (ID задачи в категории "Сметы") |
| Status | int | Статус синхронизации: 1 — новая запись, 2 — отредактирован в NAV, ожидает синхронизации, 3 — действует, 4 — отредактирован в 1Форме, ожидает синхронизации, 5 — не синхронизировано (ошибка) |
| State_bud | int | Статья бюджета |
| Sum | decimal(38,2) | Сумма к оплате |
| Sum_pay | decimal(38,2) | Оплачено |
| Date_pay | datetime | Дата оплаты |
2. В категории "Сметы" настраивается автоматизация на переходах:
-
на переходе "Отправить в NAV" выполняется отправка изменений в NAV,
-
на переходе "Внести изменения" изменения, внесенные в смету, отражаются в промежуточных таблицах.
Пример процедуры записи изменений по сметам в промежуточную таблицу:
ALTER PROCEDURE [dbo].[UpdateFrom1F_Smeta_NAV]
@TaskID int = null,
@SubcatID int = null,
@ExtParamID int = null,
@Mode varchar(max) = null,
@UserID int=3, -- SystemRobot
@ErrorMessage varchar(1000) = null out
AS
BEGIN
DECLARE @Task varchar(8000), -- текст задачи
@OrderedTime datetime = NULL, -- желаемая дата завершения задачи в формате год-месяц-день час:мин:сек
@Category int, -- ID категории
@Comment varchar(8000) = '', -- первый комментарий
@extParamStr varchar(8000) = '', -- строка ДП
@perfID int = NULL, -- назначенный исполнитель
@priorityID int = 1, -- приоритет задачи
@D3TaskID int = 0, -- ID задачи
@ParentID int = 0, -- ID родительской задачи
@UserNick varchar(20) = '', -- ник пользователя, ставящего задачу
@remind bit = 0, -- напоминать ли о задаче
@PropagateSubscribers bit = 1
DECLARE @Str varchar (max)
DECLARE @tid varchar (max)
DECLARE @res int
DECLARE @idRec int
SET @Category=34
UPDATE b
SET b.[Status] = 4,
b.[1F_ID] = a.TaskID,
b.[Department] = a.ExtParam361Value,
b.[Period] = a.ExtParam355Value
FROM [dbo].[_NAVISION_Smeta_NAV] b
JOIN TasksInSubcat34Denormalized a -- категория "Сметы"
ON a.TaskID = [1F_ID]
WHERE a.TaskID = @TaskID
END
Пример процедуры записи изменений ДП "Финансы" в промежуточную таблицу:
ALTER PROCEDURE [dbo].[UpdateFrom1F_Smeta_Table_NAV]
@TaskID int = null,
@SubcatID int = null,
@ExtParamID int = null,
@Mode varchar(max) = null,
@UserID int = 3, -- SystemRobot
@ErrorMessage varchar(1000) = null out
AS
BEGIN
DECLARE @Task varchar(8000), -- текст задачи
@OrderedTime datetime = NULL, -- желаемая дата завершения задачи в формате год-месяц-день час:мин:сек
@Category int, -- ID категории
@Comment varchar(8000) = '', -- первый комментарий
@extParamStr varchar(8000) = '', -- строка ДП
@perfID int = NULL, -- назначенный исполнитель
@priorityID int = 1, -- приоритет задачи
@D3TaskID int = 0, -- ID задачи
@ParentID int = 0, -- ID родительской задачи
@UserNick varchar(20) = '', -- ник пользователя, ставящего задачу
@remind bit = 0, -- напоминать ли о задаче
@PropagateSubscribers bit = 1
DECLARE @Str varchar (max)
DECLARE @tid varchar (max)
DECLARE @res int
DECLARE @idRec int
SET @Category=34
;WITH cte
AS
(
SELECT 4 as [Status],
a.TaskID as [1F_ID],
a.ExtParam361Value as [Department],
a.ExtParam355Value as [Period],
a.CreatedTime as [Start_date],
eptv1.Value AS [State_bud],
convert (datetime, eptv3.Value ,103) as [Date_pay],
sum(dbo.cm_correctEmptyStringToNumber(eptv4.Value)) as [Sum],
sum(dbo.cm_correctEmptyStringToNumber(eptv2.Value)) as [Sum_pay],
max(b.RowID) as RowID
FROM [dbo].[_NAVISION_Smeta_table_NAV] b
JOIN TasksInSubcat34Denormalized a -- категория "Сметы"
ON a.TaskID = [1F_ID]
LEFT JOIN ExtParamTableValues eptv1 -- колонка "Статья бюджета"
ON eptv1.TaskID = a.TaskID AND eptv.RowID = eptv1.RowID AND eptv1.ColumnID = 93
LEFT JOIN ExtParamTableValues eptv2 -- колонка "Сумма к оплате"
ON eptv2.TaskID = a.taskid AND eptv.RowID = eptv2.RowID AND eptv2.ColumnID = 108
LEFT JOIN ExtParamTableValues eptv3 -- колонка "Дата оплаты"
ON eptv3.TaskID = a.TaskID AND eptv.RowID = eptv3.RowID AND eptv3.ColumnID = 100
LEFT JOIN ExtParamTableValues eptv4 -- колонка "Оплачено"
ON eptv4.TaskID = a.TaskID AND eptv.RowID = eptv4.RowID AND eptv4.ColumnID = 36
WHERE a.TaskID = @TaskID AND b.RowID = eptv.RowID
GROUP BY a.TaskID, a.ExtParam361Value, a.ExtParam355Value, eptv1.Value
)
UPDATE b
SET
b.[Status] = c.[Status],
b.[1F_ID] = c.[1F_ID],
b.[Start_date] = c.[Start_date],
b.[State_bud] = c.[State_bud],
b.[Date_pay] = c.[Date_pay],
b.[Sum] = c.[Sum],
b.[Sum_pay] = c.[Sum_pay]
FROM [dbo].[_NAVISION_Smeta_table_NAV] b
JOIN cte c ON b.RowID = c.RowID
WHERE b.[1F_ID] = @TaskID
END
3. В категории "Сметы" настраивается смарт-расписание, которое с заданной периодичностью выполняет три хранимые процедуры: передачу в NAV смет и их табличных ДП "Финансы" и обновление данных из NAV.
Смарт-расписание для синхронизации смет
Пакет для отправки данных в NAV
Пакет для получения данных из NAV
Пример процедуры для передачи новых смет в NAV:
ALTER PROCEDURE [dbo].[NewFrom1F_Smeta_NAV]
@TaskID int = null,
@SubcatID int = null,
@ExtParamID int = null,
@Mode varchar(max) = null,
@UserID int = 3, -- SystemRobot
@ErrorMessage varchar(1000) = null out
AS
BEGIN
DECLARE @Task varchar(8000), -- текст задачи
@OrderedTime datetime = NULL, -- желаемая дата завершения задачи в формате год-месяц-день час:мин:сек
@Category int, -- ID категории
@Comment varchar(8000) = '', -- первый комментарий
@extParamStr varchar(8000) = '', -- строка ДП
@perfID int = NULL, -- назначенный исполнитель
@priorityID int = 1, -- приоритет задачи
@D3TaskID int = 0, -- ID задачи
@ParentID int = 0, -- ID родительской задачи
@UserNick varchar(20) = '', -- ник пользователя, ставящего задачу
@remind bit = 0, -- напоминать ли о задаче
@PropagateSubscribers bit = 1
DECLARE @Str varchar (max)
DECLARE @tid varchar (max)
DECLARE @res int
DECLARE @idRec int
IF EXISTS(SELECT TOP 1 [1F_ID] FROM [_NAVISION_Smeta_NAV] WHERE [1F_ID] = @TaskID)
RETURN
INSERT INTO [_NAVISION_Smeta_NAV]
SELECT a.ExtParam141Value [NAV_ID],
a.TaskID [1F_ID],
a.ExtParam361Value [Department],
a.ExtParam355Value [Period],
1 [Status]
FROM TasksInSubcat34Denormalized a -- категория "Сметы"
WHERE a.TaskID = @TaskID
END
Пример процедуры для передачи в NAV ДП "Финансы" из новых смет:
ALTER PROCEDURE [dbo].[NewFrom1F_Smeta_Table_NAV]
@TaskID int = null,
@SubcatID int = null,
@ExtParamID int = null,
@Mode varchar(max) = null,
@UserID int = 3, -- SystemRobot
@ErrorMessage varchar(1000) = null out
AS
BEGIN
DECLARE @Task varchar(8000), -- текст задачи
@OrderedTime datetime=NULL, -- желаемая дата завершения задачи в формате год-месяц-день час:мин:сек
@Category int, -- ID категории
@Comment varchar(8000) = '', -- первый комментарий
@extParamStr varchar(8000) = '', -- строка ДП
@perfID int = NULL, -- назначенный исполнитель
@priorityID int = 1, -- приоритет задачи
@D3TaskID int = 0, -- ID задачи
@ParentID int = 0, -- ID родительской задачи
@UserNick varchar(20) = '', -- ник пользователя, ставящего задачу
@remind bit = 0, -- напоминать ли о задаче
@PropagateSubscribers bit = 1
DECLARE @Str varchar (max)
DECLARE @tid varchar (max)
DECLARE @res int
DECLARE @idRec int
SET @Category=34
INSERT INTO [_NAVISION_Smeta_table_NAV] (
RowID,
[NAV_ID],
[1F_ID],
State_bud,
Date_pay,
Sum_pay,
[Sum],
[Status])
SELECT
MAX(eptv.[RowID]) AS [RowID],
a.ExtParam141Value AS [NAV_ID],
a.TaskID AS [1F_ID],
eptv1.Value AS [State_bud],
CONVERT(datetime, eptv3.Value ,103) AS [Date_pay],
SUM(dbo.cm_correctEmptyStringToNumber(eptv4.value)) AS [Sum_pay],
SUM(dbo.cm_correctEmptyStringToNumber(eptv2.value)) AS [Sum],
1 AS [Status]
FROM TasksInSubcat34Denormalized a
LEFT JOIN ExtParamTableValues eptv
ON eptv.TaskID = a.TaskID AND eptv.ColumnID = 97
LEFT JOIN ExtParamTableValues eptv1 -- колонка "Статья бюджета"
ON eptv1.TaskID = a.TaskID AND eptv.RowID = eptv1.RowID AND eptv1.ColumnID = 93
LEFT JOIN ExtParamTableValues eptv2 -- колонка "Сумма к оплате"
ON eptv2.TaskID = a.taskid AND eptv.RowID = eptv2.RowID AND eptv2.ColumnID = 108
LEFT JOIN ExtParamTableValues eptv3 -- колонка "Дата оплаты"
ON eptv3.TaskID = a.TaskID AND eptv.RowID = eptv3.RowID AND eptv3.ColumnID = 100
LEFT JOIN ExtParamTableValues eptv4 -- колонка "Оплачено"
ON eptv4.TaskID = a.TaskID AND eptv.RowID = eptv4.RowID AND eptv4.ColumnID = 36
WHERE a.TaskID = @TaskID
AND NOT EXISTS(
SELECT TOP 1 1
FROM [_NAVISION_Smeta_table_NAV]
WHERE @TaskID = [1F_ID] AND eptv.[RowID] = [RowID]
)
GROUP BY
a.ExtParam141Value,
a.TaskID,
eptv1.Value,
CONVERT(datetime, eptv3.Value ,103)
ORDER BY a.TaskID ASC
END
Пример процедуры для получения изменений из NAV:
ALTER PROCEDURE [dbo].[UpdateFromNAV_Smeta]
@TaskID int = null,
@SubcatID int = null,
@ExtParamID int = null,
@Mode varchar(max) = null,
@UserID int=3, -- SystemRobot
@ErrorMessage varchar(1000) = null out
AS
BEGIN
DECLARE @Task varchar(8000), -- текст задачи
@OrderedTime datetime = NULL, -- желаемая дата завершения задачи в формате год-месяц-день час:мин:сек
@Category int, -- ID категории
@Comment varchar(8000) = '', -- первый комментарий
@extParamStr varchar(8000) = '', -- строка ДП
@perfID int = NULL, -- назначенный исполнитель
@priorityID int = 1, -- приоритет задачи
@D3TaskID int = 0, -- ID задачи
@ParentID int = 0, -- ID родительской задачи
@UserNick varchar(20) = '', -- ник пользователя, ставящего задачу
@remind bit = 0, -- напоминать ли о задаче
@PropagateSubscribers bit = 1
DECLARE @Str varchar (max)
DECLARE @tid int
DECLARE @id int
DECLARE @TaskTEMP int
DECLARE @Temp table (t int, dp varchar(max))
SET @Category=34
DECLARE @cur cursor
INSERT INTO @Temp
SELECT d.TaskID,
'#n141#v'+ ISNULL(a.NAV_ID,'')
+::'#n361#v' + ISNULL(a.Department,'')
+::'#n355#v' + ISNULL(a.Period,'')
+::'#n68#v' + ISNULL(a.[Description],'')
+::'#n364#v' + ISNULL(CAST(a.[Status] as varchar(max)),'')+
'::'
FROM [dbo].[_NAVISION_Smeta_NAV] a
JOIN TasksInSubcat34Denormalized d -- категория "Сметы"
ON d.TaskID = a.[1F_ID]
WHERE a.[Status] IN (2,5)
SET @cur = cursor scroll for
SELECT t, dp FROM @Temp
OPEN @cur
FETCH NEXT FROM @cur INTO @tID, @extParamStr
WHILE @@FETCH_STATUS = 0
BEGIN
exec tc_ws_UpdateExtParamsInTasks
@tid,
@extParamStr,
@SessionUserID = 3
UPDATE b
SET b.[Status] = 3
FROM [dbo].[_NAVISION_Smeta_NAV] b
JOIN TasksInSubcat34Denormalized a ON a.TaskID=[1F_ID]
WHERE b.[1F_ID] = @tID and b.[Status] = 2
UPDATE b
SET b.[Status] = 3
FROM [dbo].[_NAVISION_Smeta_table_NAV] b
JOIN TasksInSubcat34Denormalized a ON a.TaskID=[1F_ID]
WHERE b.[1F_ID] = @tID AND b.[Status] = 2
FETCH NEXT FROM @cur INTO @tID, @extParamStr
END
CLOSE @cur
DEALLOCATE @cur
END