МСУ,
К стате вопрос к тебе,
По скольку ты верно подметил скорость построения отчетных данных, это можно сказать одно из важных элементов в системе.
Так вот я придерживаюсь мнения, что лучше принибречь скоростью ввода данных, т.е. я делаю все нужные вычисления в процессе ввода и фиксирую их статично, за то получаю скорость на выходе, моя платформа заточена именно так.
В общем собсветнно вопрос, как ты к этому относишься т.е. я не расматриваю динамические вычисления в процессе выборки на сервере?
Если не понятно, что я имею ввиду могу перефразировать.

МСУSerzh KZТак вот я придерживаюсь мнения, что лучше принибречь скоростью ввода данных, т.е. я делаю все нужные вычисления в процессе ввода и фиксирую их статично, за то получаю скорость на выходе, моя платформа заточена именно так.
Скоростью пренебрегать никак нельзя, даже на твоих маленьких объёмах.

1. То, что ты там закешировал на клиенте, будет невалидно при смене фильтра.
2. Кешировать большие рекордсеты на клиенте не есть тру подход (мобильные клиенты, ограниченные возможности жесткого диска, локальные / доменные политики безопасности и т.д.).
3. Кешировать должен сервисный слой (апп сервер), кеш которого для клиентов должен быть черной коробкой.
4. Не нужно делать по сути клонов БД на клиентах, они для этого не предназначены.
5. Зачем изобретать велосипед, тем более на клиентах, если есть специальные серверные мощности (DB, DWH, OLAP, Report Server), которые на порядки проще и надежней масштабировать, нежели клиентские варианты.
И так далее...

Serzh KZВ общем собсветнно вопрос, как ты к этому относишься т.е. я не расматриваю динамические вычисления в процессе выборки на сервере?
Категорически и бесповоротно плохо. Клиент должен быть легким и глупым, требовать минимум ресурсов. Решать задачи с повышенной нагрузкой для клиентов, да и еще какие задачи (!) - OLAP, извини, это фатальная ошибка в архитектуре. Ты это и сам поймешь при более менее серьезных фактах для "кубов". Тем более, ты гоняешь вхолостую большие объемы по сети, поверь, даже миллион строк по сети - жуткое расточительство и полный хаос в архитектуре. Это задача исключительно серверная, с предрасчетом в разрезе всех измерений. Клиент обращается к DWH, которая выплевывает ему уже посчитанный срез из результирующего десятка строк. Всё.
Речь идет не о клиенте а сервере, у меня хоть два звена но все происходит в сиквеле, клиент просто отображает готовые даные.
В общем дома зарисую, что бы было понятно о чем я.

в общем вот, пример из двух таблиц, что это?

МСУSerzh KZв общем вот, пример из двух таблиц, что это?
Не знаю. А что это?

P.S. Ты вопрос мой понял, вообще? :)

На сколько я помню был вопрос, что я даю на вход пивотгриду, на стороне клиента, я ответил, что любую таблу. Я, что то пропустил?

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

iscrafm__правильно пишется "Агрегатные".
Извиняюсь за безграмотность.
Твое мнение, что это?
У меня просто, к сожалению, нет времени изучать существующие, и если меня будут тыкать на существующие технологи, буду очень признателен, просто, так сказать, вырисовываю, то что у меня сидит в голове.

ViPRosSerzh KZ,

налету агрегация - не фигня :)

А на лету где? при выборке или при вводе данных?

ViPRosты б хоть view что ль создавал
В смысле?
Я вроде бы уже писал не однократно, что все таблы на стороне пользователя у меня, это вьюшки в виде генерируемых процедур.
В общем вот табла на стороне клиента.

Это, то что в нутри


ALTER PROCEDURE [dbo].[tmpTM_SelectDataTable_RU_36_11](@Param XML = NULL)
WITH EXECUTE AS OWNER
AS
DECLARE @RoleList table(Role_ID int)
DECLARE @UserID int = dbo.TM_fuUserID()
,@Param72 NVARCHAR(2048) = (SELECT TOP 1 Value FROM ##tmpParameters WHERE SPID = @@SPID AND (Table_ID = 0 OR Table_ID = 36) AND ID = 72 ORDER BY Table_ID DESC)
,@Param76 NVARCHAR(2048) = (SELECT TOP 1 Value FROM ##tmpParameters WHERE SPID = @@SPID AND (Table_ID = 0 OR Table_ID = 36) AND ID = 76 ORDER BY Table_ID DESC)
,@Param77 NVARCHAR(2048) = (SELECT TOP 1 Value FROM ##tmpParameters WHERE SPID = @@SPID AND (Table_ID = 0 OR Table_ID = 36) AND ID = 77 ORDER BY Table_ID DESC)
INSERT INTO @RoleList SELECT Role_ID FROM SYS_RoleUserRight WHERE [CreateUser_ID] = @UserID AND TypeRight_ID IN (5,6) GROUP BY Role_ID
SELECT 0 AS [_Filter_],[RW_WagonDislocation].[ID] AS [_ID_],
[Wagon_ID].[Number] AS [C309],
[StationDestination_ID].[code] AS [C4904],
[StationDestination_ID].[Name_RU] AS [C310],
[StationOperation_ID].[code] AS [C4905],
[StationOperation_ID].[Name_RU] AS [C313],
[WagonOperation_ID].[code] AS [C4903],
[WagonOperation_ID].[Name_RU] AS [C311],
[RW_WagonDislocation].[DateOperation] AS [C312],
[RW_WagonDislocation].[Distance] AS [C394],
[DistanceMeasure_ID].[ShortName_RU] AS [C395],
[WagonStatus_ID].[code] AS [C1509],
[WagonStatus_ID].[Name_RU] AS [C4945]
FROM RW_WagonDislocation
INNER JOIN RW_Wagon AS [Wagon_ID] ON [Wagon_ID].[ID] = [RW_WagonDislocation].[Wagon_ID] AND ([Wagon_ID].Role_ID IN (SELECT Role_ID FROM @RoleList) OR [Wagon_ID].CreateUser_ID = @UserID)
INNER JOIN RW_Station AS [StationDestination_ID] ON [StationDestination_ID].[ID] = [RW_WagonDislocation].[StationDestination_ID] AND ([StationDestination_ID].Role_ID IN (SELECT Role_ID FROM @RoleList) OR [StationDestination_ID].CreateUser_ID = @UserID)
INNER JOIN RW_Station AS [StationOperation_ID] ON [StationOperation_ID].[ID] = [RW_WagonDislocation].[StationOperation_ID] AND ([StationOperation_ID].Role_ID IN (SELECT Role_ID FROM @RoleList) OR [StationOperation_ID].CreateUser_ID = @UserID)
INNER JOIN RW_WagonOperation AS [WagonOperation_ID] ON [WagonOperation_ID].[ID] = [RW_WagonDislocation].[WagonOperation_ID] AND ([WagonOperation_ID].Role_ID IN (SELECT Role_ID FROM @RoleList) OR [WagonOperation_ID].CreateUser_ID = @UserID)
INNER JOIN MN_Measure AS [DistanceMeasure_ID] ON [DistanceMeasure_ID].[ID] = [RW_WagonDislocation].[DistanceMeasure_ID] AND ([DistanceMeasure_ID].Role_ID IN (SELECT Role_ID FROM @RoleList) OR [DistanceMeasure_ID].CreateUser_ID = @UserID)
INNER JOIN RW_WagonStatus AS [WagonStatus_ID] ON [WagonStatus_ID].[ID] = [RW_WagonDislocation].[WagonStatus_ID] AND ([WagonStatus_ID].Role_ID IN (SELECT Role_ID FROM @RoleList) OR [WagonStatus_ID].CreateUser_ID = @UserID)
WHERE RW_WagonDislocation.TypeFlag_ID in (1,6)
AND [RW_WagonDislocation].DistanceMeasure_ID = '2'
AND [RW_WagonDislocation].DistanceMeasure_ID = '2'
AND (@Param72 = '1' OR (@Param72 = '2' AND RW_WagonDislocation.CreateUser_ID = dbo.TM_fuUserID()) OR (@Param72 = '3' AND RW_WagonDislocation.ModifyUser_ID = dbo.TM_fuUserID()) OR (@Param72 = '4' AND RW_WagonDislocation.TypeFlag_ID = 4))
AND (@Param76 = '' OR RW_WagonDislocation.CreateDate BETWEEN CONVERT(DATE,LEFT(@Param76,CHARINDEX(':',@Param76)-1),121) AND CONVERT(DATE,RIGHT(@Param76,CHARINDEX(':',@Param76)-1),121))
AND (@Param77 = '' OR RW_WagonDislocation.ModifyDate IS NULL OR RW_WagonDislocation.ModifyDate BETWEEN CONVERT(DATE,LEFT(@Param77,CHARINDEX(':',@Param77)-1),121) AND CONVERT(DATE,RIGHT(@Param77,CHARINDEX(':',@Param77)-1),121))
AND RW_WagonDislocation.ID != 0
AND (RW_WagonDislocation.Role_ID IN (SELECT Role_ID FROM SYS_RoleUserRight WHERE [CreateUser_ID] = @UserID AND TypeRight_ID = 5)
OR RW_WagonDislocation.CreateUser_ID = @UserID)

ViPRosSerzh KZ,

при вводе т будешь кучу таблиц блокировать

Исключено, вот как данные попадают в туже таблу которые выше.

ALTER PROCEDURE [dbo].[tmpTM_spAddDataXML_RU_36] (
@DOC xml
,@SubTableRow_ID int = NULL OUT
,@ERROR uniqueidentifier = NULL OUT
,@MessageTable_ID int = NULL OUT
,@MessageColumn_ID int = NULL OUT
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY
DECLARE @RoleTableID int
--,@ERROR uniqueidentifier
,
@Wagon_ nvarchar(255),@_Flag_Wagon_ INT,
@StationDestination_ nvarchar(255),@_Flag_StationDestination_ INT,
@WagonOperation_ nvarchar(255),@_Flag_WagonOperation_ INT,
@DateOperation_ nvarchar(255),@_Flag_DateOperation_ INT,
@StationOperation_ nvarchar(255),@_Flag_StationOperation_ INT,
@Distance int,@_Flag_Distance INT,
@DistanceMeasure_ nvarchar(255),@_Flag_DistanceMeasure_ INT,
@WagonStatus_ nvarchar(255),@_Flag_WagonStatus_ INT,
@WagonStatus_2 nvarchar(255),@_Flag_WagonStatus_2 INT,
@WagonOperation_2 nvarchar(255),@_Flag_WagonOperation_2 INT,
@StationDestination_2 nvarchar(255),@_Flag_StationDestination_2 INT,
@StationOperation_2 nvarchar(255),@_Flag_StationOperation_2 INT,
@Wagon_ID int,@_Flag_Wagon_ID INT,
@DistanceMeasure_ID int,@_Flag_DistanceMeasure_ID INT,
@WagonOperation_ID int,@_Flag_WagonOperation_ID INT,
@StationDestination_ID int,@_Flag_StationDestination_ID INT,
@StationOperation_ID int,@_Flag_StationOperation_ID INT,
@WagonStatus_ID int,@_Flag_WagonStatus_ID INT,
@CreateUser_ID int,@_Flag_CreateUser_ID INT,
@CreateDate datetime,@_Flag_CreateDate INT,
@TypeFlag_ID int,@_Flag_TypeFlag_ID INT,
@Role_ID int,@_Flag_Role_ID INT,
@ID int,@_Flag_ID INT,
@DateOperation datetime,@_Flag_DateOperation INT,
@WagonTransportation_ID int,@_Flag_WagonTransportation_ID INT,
@WagonDislocation_ID int,@_Flag_WagonDislocation_ID INT,
@ModifyUser_ID int,@_Flag_ModifyUser_ID INT,
@ModifyDate datetime,@_Flag_ModifyDate INT,
@Content xml
,@LogExchange_ID int
,@TypeOperation_ID int
,@SubDOC xml
,@Result tyLogResult
,@_CreateUser_ID_ int
,@_FileID_ nvarchar(255),@_Flag__FileID_ int
,@_CreateUser_ nvarchar(255),@_Flag__CreateUser_ int
,@_CreateDate_ nvarchar(255),@_Flag__CreateDate_ int
,@_Role_ nvarchar(255),@_Flag__Role_ int
,@_Flag_ nvarchar(255),@_Flag__Flag_ int
SELECT @_CreateUser_ID_ = dbo.TM_fuUserID(),@RoleTableID = 11
--Перебор не форматированных данных
SELECT @Content = T.c.query('.'),

@Wagon_ = T.c.value('(C309)[1]','nvarchar(255)'),@_Flag_Wagon_ = T.c.value('(C309/@flag)[1]','int'),
@StationDestination_ = T.c.value('(C310)[1]','nvarchar(255)'),@_Flag_StationDestination_ = T.c.value('(C310/@flag)[1]','int'),
@WagonOperation_ = T.c.value('(C311)[1]','nvarchar(255)'),@_Flag_WagonOperation_ = T.c.value('(C311/@flag)[1]','int'),
@DateOperation_ = T.c.value('(C312)[1]','nvarchar(255)'),@_Flag_DateOperation_ = T.c.value('(C312/@flag)[1]','int'),
@StationOperation_ = T.c.value('(C313)[1]','nvarchar(255)'),@_Flag_StationOperation_ = T.c.value('(C313/@flag)[1]','int'),
@Distance = T.c.value('(C394)[1]','int'),@_Flag_Distance = T.c.value('(C394/@flag)[1]','int'),
@DistanceMeasure_ = T.c.value('(C395)[1]','nvarchar(255)'),@_Flag_DistanceMeasure_ = T.c.value('(C395/@flag)[1]','int'),
@WagonStatus_ = T.c.value('(C4945)[1]','nvarchar(255)'),@_Flag_WagonStatus_ = T.c.value('(C4945/@flag)[1]','int'),
@ID = T.c.value('(_ID_)[1]','int')
,@_FileID_ = T.c.value('(_FileID_)[1]','int'),@_Flag__FileID_ = T.c.value('(_FileID_/@flag)[1]','int')
,@_CreateUser_ = T.c.value('(_CreateUser_)[1]','nvarchar(255)'),@_Flag__CreateUser_ = T.c.value('(_CreateUser_/@flag)[1]','int')
,@_CreateDate_ = T.c.value('(_CreateDate_)[1]','nvarchar(255)'),@_Flag__CreateDate_ = T.c.value('(_CreateDate_/@flag)[1]','int')
,@_Role_ = T.c.value('(_Role_)[1]','nvarchar(255)'),@_Flag__Role_ = T.c.value('(_Role_/@flag)[1]','int')
,@_Flag_ = T.c.value('(_Flag_)[1]','nvarchar(255)'),@_Flag__Flag_ = T.c.value('(_Flag_/@flag)[1]','int')
,@LogExchange_ID = T.c.value('(_LogExchange_ID_)[1]','int')
FROM @DOC.nodes('*/T36') T(c)
IF @Content IS NOT NULL
BEGIN
SET @ERROR = NULL
--Добавление записи в журнал обмена
IF @LogExchange_ID is NULL
BEGIN
INSERT INTO SYS_LogExchange (Object_ID,Content) Values (36,@Content)
SET @LogExchange_ID = @@IDENTITY
END ELSE UPDATE SYS_LogExchange SET [CreateUser_ID] = @_CreateUser_ID_,Content = @Content WHERE ID = @LogExchange_ID
--SET @TypeFlag_ID = 1
SET @TypeOperation_ID = NULL
--Проверка на удаление и обнавление поля в записи
IF @_Flag_Wagon_ IS NULL AND @_Flag_StationDestination_ IS NULL AND @_Flag_WagonOperation_ IS NULL AND @_Flag_DateOperation_ IS NULL AND @_Flag_StationOperation_ IS NULL AND @_Flag_Distance IS NULL AND @_Flag_DistanceMeasure_ IS NULL AND @_Flag_WagonStatus_ IS NULL
BEGIN
IF @Wagon_ IS NOT NULL SET @_Flag_Wagon_ = 1
IF @StationDestination_ IS NOT NULL SET @_Flag_StationDestination_ = 1
IF @WagonOperation_ IS NOT NULL SET @_Flag_WagonOperation_ = 1
IF @DateOperation_ IS NOT NULL SET @_Flag_DateOperation_ = 1
IF @StationOperation_ IS NOT NULL SET @_Flag_StationOperation_ = 1
IF @Distance IS NOT NULL SET @_Flag_Distance = 1
IF @DistanceMeasure_ IS NOT NULL SET @_Flag_DistanceMeasure_ = 1
IF @WagonStatus_ IS NOT NULL SET @_Flag_WagonStatus_ = 1
END
--Установка констант

SELECT @DistanceMeasure_ID = '2', @_Flag_DistanceMeasure_ID = 1
--Преоброзование данных до проверок

IF (@Wagon_ like '% %') SET @Wagon_ = REPLACE(@Wagon_,' ','')
IF (@DateOperation_ like '[0-3][0-9].[0-1][0-9]') SET @DateOperation_ = CAST(Year(GETDATE()) AS nvarchar(4)) + '-' + SUBSTRING(@DateOperation_ ,4,2) + '-' + SUBSTRING(@DateOperation_,1,2) + ' 00:00:00'
IF (@DateOperation_ like '[0-3][0-9].[0-1][0-9].[0-2][0-9]') SET @DateOperation_ = '20' + SUBSTRING(@DateOperation_ ,7,2) + '-' + SUBSTRING(@DateOperation_ ,4,2) + '-' + SUBSTRING(@DateOperation_,1,2) + ' 00:00:00'
IF (@DateOperation_ like '[0-3][0-9].[0-1][0-9].[1-2][0-9][0-9][0-9]') SET @DateOperation_ = SUBSTRING(@DateOperation_ ,7,4) + '-' + SUBSTRING(@DateOperation_ ,4,2) + '-' + SUBSTRING(@DateOperation_,1,2) + ' 00:00:00'
IF (@DateOperation_ like '%T%') SET @DateOperation_ = REPLACE(@DateOperation_,'T',' ')
IF (@DateOperation_ like '[0-3][0-9].[0-1][0-9] [0-9]:[0-5][0-9]%') SET @DateOperation_ = CAST(Year(GETDATE()) AS nvarchar(4)) + '-' + SUBSTRING(@DateOperation_ ,4,2) + '-' + SUBSTRING(@DateOperation_,1,2) + ' 0' + SUBSTRING(@DateOperation_,7,4)
IF (@DateOperation_ like '[0-3][0-9].[0-1][0-9] [0-2][0-9]:[0-5][0-9]%') SET @DateOperation_ = CAST(Year(GETDATE()) AS nvarchar(4)) + '-' + SUBSTRING(@DateOperation_ ,4,2) + '-' + SUBSTRING(@DateOperation_,1,2) + ' ' + SUBSTRING(@DateOperation_,7,5)
IF (@DateOperation_ like '[0-3][0-9].[0-1][0-9].[0-2][0-9] [0-9]:[0-5][0-9]%') SET @DateOperation_ = '20' + SUBSTRING(@DateOperation_ ,7,2) + '-' + SUBSTRING(@DateOperation_ ,4,2) + '-' + SUBSTRING(@DateOperation_,1,2) + ' 0' + SUBSTRING(@DateOperation_,10,4)
IF (@DateOperation_ like '[0-3][0-9].[0-1][0-9].[0-2][0-9] [0-2][0-9]:[0-5][0-9]%') SET @DateOperation_ = '20' + SUBSTRING(@DateOperation_ ,7,2) + '-' + SUBSTRING(@DateOperation_ ,4,2) + '-' + SUBSTRING(@DateOperation_,1,2) + ' ' + SUBSTRING(@DateOperation_,10,5)
IF (@DateOperation_ like '[0-3][0-9].[0-1][0-9].[0-2][0-9][0-2][0-9] [0-9]:[0-5][0-9]%') SET @DateOperation_ = SUBSTRING(@DateOperation_ ,7,4) + '-' + SUBSTRING(@DateOperation_ ,4,2) + '-' + SUBSTRING(@DateOperation_,1,2) + ' 0' + SUBSTRING(@DateOperation_,12,4)
IF (@DateOperation_ like '[0-3][0-9].[0-1][0-9].[0-2][0-9][0-2][0-9] [0-2][0-9]:[0-5][0-9]%') SET @DateOperation_ = SUBSTRING(@DateOperation_ ,7,4) + '-' + SUBSTRING(@DateOperation_ ,4,2) + '-' + SUBSTRING(@DateOperation_,1,2) + ' ' + SUBSTRING(@DateOperation_,12,5)
IF (@DateOperation_ like '[0-2][0-9][0-2][0-9]-[0-1][0-9]-[0-3][0-9] [0-9]:[0-5][0-9]%') SET @DateOperation_ = LEFT(@DateOperation_,10) + ' 0' + SUBSTRING(@DateOperation_,12,4)
IF (@DateOperation_ like '[0-2][0-9][0-2][0-9]-[0-1][0-9]-[0-3][0-9] [0-2][0-9]:[0-5][0-9]%') SET @DateOperation_ = LEFT(@DateOperation_,10) + ' ' + SUBSTRING(@DateOperation_,12,5)
IF (@StationDestination_ like '[0-9][0-9][0-9][0-9][0-9][0-9]') SET @StationDestination_2 = @StationDestination_
IF (@StationDestination_ like '[0-9][0-9][0-9][0-9][0-9]') SET @StationDestination_2 = '0' + @StationDestination_
IF (@StationDestination_ like '[0-9][0-9][0-9][0-9][0-9]%') SET @StationDestination_ = NULL
IF (@StationOperation_ like '[0-9][0-9][0-9][0-9][0-9][0-9]') SET @StationOperation_2 = @StationOperation_
IF (@StationOperation_ like '[0-9][0-9][0-9][0-9][0-9]') SET @StationOperation_2 = '0' + @StationOperation_
IF (@StationOperation_ like '[0-9][0-9][0-9][0-9][0-9]%') SET @StationOperation_ = NULL
IF (@WagonOperation_ like '[0-9][0-9]') SET @WagonOperation_2 = @WagonOperation_
IF (@WagonOperation_ like '[0-9]') SET @WagonOperation_2 = '0' + @WagonOperation_
IF (@WagonOperation_ like '[0-9]') SET @WagonOperation_ = NULL
IF (@WagonOperation_ like '[0-9][0-9]') SET @WagonOperation_ = NULL
IF (@WagonOperation_ is not null) SET @WagonOperation_ = LTRIM(RTRIM(@WagonOperation_))
IF (@WagonOperation_ is not null) SET @WagonOperation_ = REPLACE(@WagonOperation_,'A','А')
IF (@WagonOperation_ is not null) SET @WagonOperation_ = REPLACE(@WagonOperation_,'B','В')
IF (@WagonOperation_ is not null) SET @WagonOperation_ = REPLACE(@WagonOperation_,'C','С')
IF (@WagonOperation_ is not null) SET @WagonOperation_ = REPLACE(@WagonOperation_,'E','Е')
IF (@WagonOperation_ is not null) SET @WagonOperation_ = REPLACE(@WagonOperation_,'H','Н')
IF (@WagonOperation_ is not null) SET @WagonOperation_ = REPLACE(@WagonOperation_,'K','К')
IF (@WagonOperation_ is not null) SET @WagonOperation_ = REPLACE(@WagonOperation_,'M','М')
IF (@WagonOperation_ is not null) SET @WagonOperation_ = REPLACE(@WagonOperation_,'O','О')
IF (@WagonOperation_ is not null) SET @WagonOperation_ = REPLACE(@WagonOperation_,'P','Р')
IF (@WagonOperation_ is not null) SET @WagonOperation_ = REPLACE(@WagonOperation_,'X','Х')
IF (@WagonOperation_ is not null) SET @WagonOperation_ = REPLACE(@WagonOperation_,'Y','У')
IF (@DateOperation_ like '20[0-9][0-9][0-2][0-9][0-3][0-9][0-9][0-9][0-9][0-9]') SET @DateOperation_ = SUBSTRING(@DateOperation_ ,1,4) + '-' + SUBSTRING(@DateOperation_ ,5,2) + '-' + SUBSTRING(@DateOperation_,7,2) + ' ' + SUBSTRING(@DateOperation_,9,2) + ':' + SUBSTRING(@DateOperation_,11,2)
IF (@WagonStatus_ like '[0-1][0-1]') SET @WagonStatus_2 = @WagonStatus_
IF (@WagonStatus_ like '[0-1][0-1]') SET @WagonStatus_ = NULL
--Проверка данных
--Проверка последовательности заполнения полей
IF @ERROR IS NULL BEGIN
--Группа[0]
--Подгруппа[1]
IF @Wagon_ IS NOT NULL BEGIN
IF @DateOperation_ IS NULL SELECT @ERROR = '39723573-3DEF-4805-B8EE-58B80164D532',@MessageColumn_ID = 312
END ELSE SELECT @ERROR = '39723573-3DEF-4805-B8EE-58B80164D532',@MessageColumn_ID = 309
END
--Проверка содержимого полей
IF @ERROR IS NULL AND NOT (@DateOperation_ IS NULL OR @DateOperation_ like '[1-2][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]' OR @DateOperation_ like '[1-2][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9] [0-2][0-9]:[0-5][0-9]' OR @DateOperation_ like '[1-2][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9] [0-2][0-9]:[0-5][0-9]' OR @DateOperation_ like '[1-2][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9] [0-2][0-9]:[0-5][0-9]:[0-5][0-9]') SELECT @ERROR = '3B071706-88A1-4ED9-86C2-B3FC3194ECFB',@MessageColumn_ID = 312
IF @ERROR IS NULL AND NOT (@StationDestination_ID != 0) SELECT @ERROR = '20BCE651-57E1-414A-B89F-F18E860439CA',@MessageColumn_ID = 5998
IF @ERROR IS NULL AND NOT (@StationOperation_ID != 0) SELECT @ERROR = '931D4F62-C4DE-4E6C-B859-ECDCEE5D3323',@MessageColumn_ID = 5999
IF @ERROR IS NULL AND NOT (@Wagon_ like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') SELECT @ERROR = '3B517A6F-681D-418D-B2D1-EAE1FF957453',@MessageColumn_ID = 309
IF @ERROR IS NULL AND NOT (@WagonOperation_ID != 0) SELECT @ERROR = '3AEEA368-6C0D-41A0-821E-375ABF4A5657',@MessageColumn_ID = 5947
IF @ERROR is NULL
BEGIN
--Преоброзование данных
/*Пользователи*/
IF @_Flag__CreateUser_ = 3 SET @_Flag_CreateUser_ID = 3
IF @_Flag__CreateUser_ IN (1,2) SET @_Flag_CreateUser_ID = 2
IF @ERROR IS NULL
IF (
@_CreateUser_ IS NOT NULL OR
@CreateUser_ID IS NOT NULL
) AND (@_Flag_CreateUser_ID IN (1,2) OR @ID <= 0)
IF
EXISTS(SELECT *
FROM SYS_User [RW_WagonDislocation.CreateUser_ID]
WHERE [RW_WagonDislocation.CreateUser_ID].TypeFlag_ID in (1,4,6) AND
((@_CreateUser_ IS NULL) OR (CAST(@_CreateUser_ AS nvarchar(255))) = [RW_WagonDislocation.CreateUser_ID].[Name_RU]) AND
((@CreateUser_ID IS NULL) OR (CAST(@CreateUser_ID AS int)) = [RW_WagonDislocation.CreateUser_ID].[ID]))
BEGIN
IF
(SELECT COUNT(*)
FROM SYS_User [RW_WagonDislocation.CreateUser_ID]
WHERE [RW_WagonDislocation.CreateUser_ID].TypeFlag_ID in (1,4,6) AND
((@_CreateUser_ IS NULL) OR (CAST(@_CreateUser_ AS nvarchar(255))) = [RW_WagonDislocation.CreateUser_ID].[Name_RU]) AND
((@CreateUser_ID IS NULL) OR (CAST(@CreateUser_ID AS int)) = [RW_WagonDislocation.CreateUser_ID].[ID])) = 1
BEGIN
SELECT @CreateUser_ID = [RW_WagonDislocation.CreateUser_ID].[ID]
FROM SYS_User [RW_WagonDislocation.CreateUser_ID]
WHERE [RW_WagonDislocation.CreateUser_ID].TypeFlag_ID in (1,4,6) AND
((@_CreateUser_ IS NULL) OR (CAST(@_CreateUser_ AS nvarchar(255))) = [RW_WagonDislocation.CreateUser_ID].[Name_RU]) AND
((@CreateUser_ID IS NULL) OR (CAST(@CreateUser_ID AS int)) = [RW_WagonDislocation.CreateUser_ID].[ID])
END ELSE SELECT @Error = '9B952BE1-5A8E-4B01-95C4-6CEEAB0AF80D' /*--Существует более одной записи*/,@MessageTable_ID = 10
END ELSE
BEGIN
SET @SubDOC = '<ADD>
<T10>
' + CASE WHEN @_CreateUser_ IS NOT NULL THEN '<C82>' + CAST(@_CreateUser_ AS NVARCHAR(255)) + '</C82>' ELSE '' END + '
</T10>
</ADD>'
EXEC TM_spAddDataXML 10,@SubDOC,'RU',@SubTableRow_ID OUT,@ERROR OUT,@MessageTable_ID OUT,@MessageColumn_ID OUT
IF @SubTableRow_ID IS NULL
BEGIN
IF @Error IS NULL SET @Error = '31329A2E-D55A-45AC-AD04-3086BB3A900D' --Запись не найдена
IF @MessageTable_ID IS NULL SET @MessageTable_ID = 10
END ELSE SET @CreateUser_ID = @SubTableRow_ID
END ELSE SET @CreateUser_ID = ([dbo].[TM_fuUserID]())
/*Валют договора*/
IF @_Flag_DistanceMeasure_ = 3 SET @_Flag_DistanceMeasure_ID = 3
IF @_Flag_DistanceMeasure_ IN (1,2) SET @_Flag_DistanceMeasure_ID = 2
IF @ERROR IS NULL
IF (
@DistanceMeasure_ IS NOT NULL OR
@DistanceMeasure_ID IS NOT NULL
) AND (@_Flag_DistanceMeasure_ID IN (1,2) OR @ID <= 0)
IF
EXISTS(SELECT *
FROM MN_Measure [RW_WagonDislocation.DistanceMeasure_ID]
WHERE [RW_WagonDislocation.DistanceMeasure_ID].TypeFlag_ID in (1,4,6) AND
((@DistanceMeasure_ IS NULL) OR (CAST(@DistanceMeasure_ AS nvarchar(255))) = [RW_WagonDislocation.DistanceMeasure_ID].[ShortName_RU]) AND
((@DistanceMeasure_ID IS NULL) OR (CAST(@DistanceMeasure_ID AS int)) = [RW_WagonDislocation.DistanceMeasure_ID].[ID]))
BEGIN
IF
(SELECT COUNT(*)
FROM MN_Measure [RW_WagonDislocation.DistanceMeasure_ID]
WHERE [RW_WagonDislocation.DistanceMeasure_ID].TypeFlag_ID in (1,4,6) AND
((@DistanceMeasure_ IS NULL) OR (CAST(@DistanceMeasure_ AS nvarchar(255))) = [RW_WagonDislocation.DistanceMeasure_ID].[ShortName_RU]) AND
((@DistanceMeasure_ID IS NULL) OR (CAST(@DistanceMeasure_ID AS int)) = [RW_WagonDislocation.DistanceMeasure_ID].[ID])) = 1
BEGIN
SELECT @DistanceMeasure_ID = [RW_WagonDislocation.DistanceMeasure_ID].[ID]
FROM MN_Measure [RW_WagonDislocation.DistanceMeasure_ID]
WHERE [RW_WagonDislocation.DistanceMeasure_ID].TypeFlag_ID in (1,4,6) AND
((@DistanceMeasure_ IS NULL) OR (CAST(@DistanceMeasure_ AS nvarchar(255))) = [RW_WagonDislocation.DistanceMeasure_ID].[ShortName_RU]) AND
((@DistanceMeasure_ID IS NULL) OR (CAST(@DistanceMeasure_ID AS int)) = [RW_WagonDislocation.DistanceMeasure_ID].[ID])
END ELSE SELECT @Error = '9B952BE1-5A8E-4B01-95C4-6CEEAB0AF80D' /*--Существует более одной записи*/,@MessageTable_ID = 113
END ELSE
BEGIN
SET @SubDOC = '<ADD>
<T113>
' + CASE WHEN @DistanceMeasure_ IS NOT NULL THEN '<C1893>' + CAST(@DistanceMeasure_ AS NVARCHAR(255)) + '</C1893>' ELSE '' END + '
</T113>
</ADD>'
EXEC TM_spAddDataXML 113,@SubDOC,'RU',@SubTableRow_ID OUT,@ERROR OUT,@MessageTable_ID OUT,@MessageColumn_ID OUT
IF @SubTableRow_ID IS NULL
BEGIN
IF @Error IS NULL SET @Error = '31329A2E-D55A-45AC-AD04-3086BB3A900D' --Запись не найдена
IF @MessageTable_ID IS NULL SET @MessageTable_ID = 113
END ELSE SET @DistanceMeasure_ID = @SubTableRow_ID
END ELSE SET @DistanceMeasure_ID = ((0))
/*Роли*/
IF @_Flag__Role_ = 3 SET @_Flag_Role_ID = 3
IF @_Flag__Role_ IN (1,2) SET @_Flag_Role_ID = 2
IF @ERROR IS NULL
IF (
@_Role_ IS NOT NULL OR
@Role_ID IS NOT NULL
) AND (@_Flag_Role_ID IN (1,2) OR @ID <= 0)
IF
EXISTS(SELECT *
FROM SYS_Role [RW_WagonDislocation.Role_ID]
WHERE [RW_WagonDislocation.Role_ID].TypeFlag_ID in (1,4,6) AND
((@_Role_ IS NULL) OR (CAST(@_Role_ AS nvarchar(255))) = [RW_WagonDislocation.Role_ID].[Name_RU]) AND
((@Role_ID IS NULL) OR (CAST(@Role_ID AS int)) = [RW_WagonDislocation.Role_ID].[ID]))
BEGIN
IF
(SELECT COUNT(*)
FROM SYS_Role [RW_WagonDislocation.Role_ID]
WHERE [RW_WagonDislocation.Role_ID].TypeFlag_ID in (1,4,6) AND
((@_Role_ IS NULL) OR (CAST(@_Role_ AS nvarchar(255))) = [RW_WagonDislocation.Role_ID].[Name_RU]) AND
((@Role_ID IS NULL) OR (CAST(@Role_ID AS int)) = [RW_WagonDislocation.Role_ID].[ID])) = 1
BEGIN
SELECT @Role_ID = [RW_WagonDislocation.Role_ID].[ID]
FROM SYS_Role [RW_WagonDislocation.Role_ID]
WHERE [RW_WagonDislocation.Role_ID].TypeFlag_ID in (1,4,6) AND
((@_Role_ IS NULL) OR (CAST(@_Role_ AS nvarchar(255))) = [RW_WagonDislocation.Role_ID].[Name_RU]) AND
((@Role_ID IS NULL) OR (CAST(@Role_ID AS int)) = [RW_WagonDislocation.Role_ID].[ID])
END ELSE SELECT @Error = '9B952BE1-5A8E-4B01-95C4-6CEEAB0AF80D' /*--Существует более одной записи*/,@MessageTable_ID = 13
END ELSE
BEGIN
SET @SubDOC = '<ADD>
<T13>
' + CASE WHEN @_Role_ IS NOT NULL THEN '<C104>' + CAST(@_Role_ AS NVARCHAR(255)) + '</C104>' ELSE '' END + '
</T13>
</ADD>'
EXEC TM_spAddDataXML 13,@SubDOC,'RU',@SubTableRow_ID OUT,@ERROR OUT,@MessageTable_ID OUT,@MessageColumn_ID OUT
IF @SubTableRow_ID IS NULL
BEGIN
IF @Error IS NULL SET @Error = '31329A2E-D55A-45AC-AD04-3086BB3A900D' --Запись не найдена
IF @MessageTable_ID IS NULL SET @MessageTable_ID = 13
END ELSE SET @Role_ID = @SubTableRow_ID
END ELSE SET @Role_ID = ((1))
/*Станции*/
IF @_Flag_StationDestination_ = 3 AND @_Flag_StationDestination_2 = 3 SET @_Flag_StationDestination_ID = 3
IF @_Flag_StationDestination_ IN (1,2) OR @_Flag_StationDestination_2 IN (1,2) SET @_Flag_StationDestination_ID = 2
IF @ERROR IS NULL
IF (
@StationDestination_ IS NOT NULL OR
@StationDestination_2 IS NOT NULL OR
@StationDestination_ID IS NOT NULL
) AND (@_Flag_StationDestination_ID IN (1,2) OR @ID <= 0)
IF
EXISTS(SELECT *
FROM RW_Station [RW_WagonDislocation.StationDestination_ID]
WHERE [RW_WagonDislocation.StationDestination_ID].TypeFlag_ID in (1,4,6) AND
((@StationDestination_ IS NULL) OR (CAST(@StationDestination_ AS nvarchar(255))) = [RW_WagonDislocation.StationDestination_ID].[Name_RU]) AND
((@StationDestination_2 IS NULL) OR (CAST(@StationDestination_2 AS char(6))) = [RW_WagonDislocation.StationDestination_ID].[code]) AND
((@StationDestination_ID IS NULL) OR (CAST(@StationDestination_ID AS int)) = [RW_WagonDislocation.StationDestination_ID].[ID]))
BEGIN
IF
(SELECT COUNT(*)
FROM RW_Station [RW_WagonDislocation.StationDestination_ID]
WHERE [RW_WagonDislocation.StationDestination_ID].TypeFlag_ID in (1,4,6) AND
((@StationDestination_ IS NULL) OR (CAST(@StationDestination_ AS nvarchar(255))) = [RW_WagonDislocation.StationDestination_ID].[Name_RU]) AND
((@StationDestination_2 IS NULL) OR (CAST(@StationDestination_2 AS char(6))) = [RW_WagonDislocation.StationDestination_ID].[code]) AND
((@StationDestination_ID IS NULL) OR (CAST(@StationDestination_ID AS int)) = [RW_WagonDislocation.StationDestination_ID].[ID])) = 1
BEGIN
SELECT @StationDestination_ID = [RW_WagonDislocation.StationDestination_ID].[ID]
FROM RW_Station [RW_WagonDislocation.StationDestination_ID]
WHERE [RW_WagonDislocation.StationDestination_ID].TypeFlag_ID in (1,4,6) AND
((@StationDestination_ IS NULL) OR (CAST(@StationDestination_ AS nvarchar(255))) = [RW_WagonDislocation.StationDestination_ID].[Name_RU]) AND
((@StationDestination_2 IS NULL) OR (CAST(@StationDestination_2 AS char(6))) = [RW_WagonDislocation.StationDestination_ID].[code]) AND
((@StationDestination_ID IS NULL) OR (CAST(@StationDestination_ID AS int)) = [RW_WagonDislocation.StationDestination_ID].[ID])
END ELSE SELECT @Error = '9B952BE1-5A8E-4B01-95C4-6CEEAB0AF80D' /*--Существует более одной записи*/,@MessageTable_ID = 25
END ELSE
BEGIN
SET @SubDOC = '<ADD>
<T25>
' + CASE WHEN @StationDestination_2 IS NOT NULL THEN '<C203>' + CAST(@StationDestination_2 AS NVARCHAR(255)) + '</C203>' ELSE '' END + '
' + CASE WHEN @StationDestination_ IS NOT NULL THEN '<C206>' + CAST(@StationDestination_ AS NVARCHAR(255)) + '</C206>' ELSE '' END + '
</T25>
</ADD>'
EXEC TM_spAddDataXML 25,@SubDOC,'RU',@SubTableRow_ID OUT,@ERROR OUT,@MessageTable_ID OUT,@MessageColumn_ID OUT
IF @SubTableRow_ID IS NULL
BEGIN
IF @Error IS NULL SET @Error = '31329A2E-D55A-45AC-AD04-3086BB3A900D' --Запись не найдена
IF @MessageTable_ID IS NULL SET @MessageTable_ID = 25
END ELSE SET @StationDestination_ID = @SubTableRow_ID
END ELSE SET @StationDestination_ID = ((0))
/*Станции*/
IF @_Flag_StationOperation_ = 3 AND @_Flag_StationOperation_2 = 3 SET @_Flag_StationOperation_ID = 3
IF @_Flag_StationOperation_ IN (1,2) OR @_Flag_StationOperation_2 IN (1,2) SET @_Flag_StationOperation_ID = 2
IF @ERROR IS NULL
IF (
@StationOperation_ IS NOT NULL OR
@StationOperation_2 IS NOT NULL OR
@StationOperation_ID IS NOT NULL
) AND (@_Flag_StationOperation_ID IN (1,2) OR @ID <= 0)
IF
EXISTS(SELECT *
FROM RW_Station [RW_WagonDislocation.StationOperation_ID]
WHERE [RW_WagonDislocation.StationOperation_ID].TypeFlag_ID in (1,4,6) AND
((@StationOperation_ IS NULL) OR (CAST(@StationOperation_ AS nvarchar(255))) = [RW_WagonDislocation.StationOperation_ID].[Name_RU]) AND
((@StationOperation_2 IS NULL) OR (CAST(@StationOperation_2 AS char(6))) = [RW_WagonDislocation.StationOperation_ID].[code]) AND
((@StationOperation_ID IS NULL) OR (CAST(@StationOperation_ID AS int)) = [RW_WagonDislocation.StationOperation_ID].[ID]))
BEGIN
IF
(SELECT COUNT(*)
FROM RW_Station [RW_WagonDislocation.StationOperation_ID]
WHERE [RW_WagonDislocation.StationOperation_ID].TypeFlag_ID in (1,4,6) AND
((@StationOperation_ IS NULL) OR (CAST(@StationOperation_ AS nvarchar(255))) = [RW_WagonDislocation.StationOperation_ID].[Name_RU]) AND
((@StationOperation_2 IS NULL) OR (CAST(@StationOperation_2 AS char(6))) = [RW_WagonDislocation.StationOperation_ID].[code]) AND
((@StationOperation_ID IS NULL) OR (CAST(@StationOperation_ID AS int)) = [RW_WagonDislocation.StationOperation_ID].[ID])) = 1
BEGIN
SELECT @StationOperation_ID = [RW_WagonDislocation.StationOperation_ID].[ID]
FROM RW_Station [RW_WagonDislocation.StationOperation_ID]
WHERE [RW_WagonDislocation.StationOperation_ID].TypeFlag_ID in (1,4,6) AND
((@StationOperation_ IS NULL) OR (CAST(@StationOperation_ AS nvarchar(255))) = [RW_WagonDislocation.StationOperation_ID].[Name_RU]) AND
((@StationOperation_2 IS NULL) OR (CAST(@StationOperation_2 AS char(6))) = [RW_WagonDislocation.StationOperation_ID].[code]) AND
((@StationOperation_ID IS NULL) OR (CAST(@StationOperation_ID AS int)) = [RW_WagonDislocation.StationOperation_ID].[ID])
END ELSE SELECT @Error = '9B952BE1-5A8E-4B01-95C4-6CEEAB0AF80D' /*--Существует более одной записи*/,@MessageTable_ID = 25
END ELSE
BEGIN
SET @SubDOC = '<ADD>
<T25>
' + CASE WHEN @StationOperation_2 IS NOT NULL THEN '<C203>' + CAST(@StationOperation_2 AS NVARCHAR(255)) + '</C203>' ELSE '' END + '
' + CASE WHEN @StationOperation_ IS NOT NULL THEN '<C206>' + CAST(@StationOperation_ AS NVARCHAR(255)) + '</C206>' ELSE '' END + '
</T25>
</ADD>'
EXEC TM_spAddDataXML 25,@SubDOC,'RU',@SubTableRow_ID OUT,@ERROR OUT,@MessageTable_ID OUT,@MessageColumn_ID OUT
IF @SubTableRow_ID IS NULL
BEGIN
IF @Error IS NULL SET @Error = '31329A2E-D55A-45AC-AD04-3086BB3A900D' --Запись не найдена
IF @MessageTable_ID IS NULL SET @MessageTable_ID = 25
END ELSE SET @StationOperation_ID = @SubTableRow_ID
END ELSE SET @StationOperation_ID = ((0))
/*Флаги*/
IF @_Flag__Flag_ = 3 SET @_Flag_TypeFlag_ID = 3
IF @_Flag__Flag_ IN (1,2) SET @_Flag_TypeFlag_ID = 2
IF @ERROR IS NULL
IF (
@_Flag_ IS NOT NULL OR
@TypeFlag_ID IS NOT NULL
) AND (@_Flag_TypeFlag_ID IN (1,2) OR @ID <= 0)
IF
EXISTS(SELECT *
FROM SYS_TypeFlag [RW_WagonDislocation.TypeFlag_ID]
WHERE [RW_WagonDislocation.TypeFlag_ID].TypeFlag_ID in (1,4,6) AND
((@_Flag_ IS NULL) OR (CAST(@_Flag_ AS nvarchar(255))) = [RW_WagonDislocation.TypeFlag_ID].[Name_RU]) AND
((@TypeFlag_ID IS NULL) OR (CAST(@TypeFlag_ID AS int)) = [RW_WagonDislocation.TypeFlag_ID].[ID]))
BEGIN
IF
(SELECT COUNT(*)
FROM SYS_TypeFlag [RW_WagonDislocation.TypeFlag_ID]
WHERE [RW_WagonDislocation.TypeFlag_ID].TypeFlag_ID in (1,4,6) AND
((@_Flag_ IS NULL) OR (CAST(@_Flag_ AS nvarchar(255))) = [RW_WagonDislocation.TypeFlag_ID].[Name_RU]) AND
((@TypeFlag_ID IS NULL) OR (CAST(@TypeFlag_ID AS int)) = [RW_WagonDislocation.TypeFlag_ID].[ID])) = 1
BEGIN
SELECT @TypeFlag_ID = [RW_WagonDislocation.TypeFlag_ID].[ID]
FROM SYS_TypeFlag [RW_WagonDislocation.TypeFlag_ID]
WHERE [RW_WagonDislocation.TypeFlag_ID].TypeFlag_ID in (1,4,6) AND
((@_Flag_ IS NULL) OR (CAST(@_Flag_ AS nvarchar(255))) = [RW_WagonDislocation.TypeFlag_ID].[Name_RU]) AND
((@TypeFlag_ID IS NULL) OR (CAST(@TypeFlag_ID AS int)) = [RW_WagonDislocation.TypeFlag_ID].[ID])
END ELSE SELECT @Error = '9B952BE1-5A8E-4B01-95C4-6CEEAB0AF80D' /*--Существует более одной записи*/,@MessageTable_ID = 15
END ELSE
BEGIN
SET @SubDOC = '<ADD>
<T15>
' + CASE WHEN @_Flag_ IS NOT NULL THEN '<C119>' + CAST(@_Flag_ AS NVARCHAR(255)) + '</C119>' ELSE '' END + '
</T15>
</ADD>'
EXEC TM_spAddDataXML 15,@SubDOC,'RU',@SubTableRow_ID OUT,@ERROR OUT,@MessageTable_ID OUT,@MessageColumn_ID OUT
IF @SubTableRow_ID IS NULL
BEGIN
IF @Error IS NULL SET @Error = '31329A2E-D55A-45AC-AD04-3086BB3A900D' --Запись не найдена
IF @MessageTable_ID IS NULL SET @MessageTable_ID = 15
END ELSE SET @TypeFlag_ID = @SubTableRow_ID
END ELSE SET @TypeFlag_ID = ((1))
/*Вагоны*/
IF @_Flag_Wagon_ = 3 SET @_Flag_Wagon_ID = 3
IF @_Flag_Wagon_ IN (1,2) SET @_Flag_Wagon_ID = 2
IF @ERROR IS NULL
IF (
@Wagon_ IS NOT NULL OR
@Wagon_ID IS NOT NULL
) AND (@_Flag_Wagon_ID IN (1,2) OR @ID <= 0)
IF
EXISTS(SELECT *
FROM RW_Wagon [RW_WagonDislocation.Wagon_ID]
WHERE [RW_WagonDislocation.Wagon_ID].TypeFlag_ID in (1,4,6) AND
((@Wagon_ IS NULL) OR (CAST(@Wagon_ AS char(8))) = [RW_WagonDislocation.Wagon_ID].[Number]) AND
((@Wagon_ID IS NULL) OR (CAST(@Wagon_ID AS int)) = [RW_WagonDislocation.Wagon_ID].[ID]))
BEGIN
IF
(SELECT COUNT(*)
FROM RW_Wagon [RW_WagonDislocation.Wagon_ID]
WHERE [RW_WagonDislocation.Wagon_ID].TypeFlag_ID in (1,4,6) AND
((@Wagon_ IS NULL) OR (CAST(@Wagon_ AS char(8))) = [RW_WagonDislocation.Wagon_ID].[Number]) AND
((@Wagon_ID IS NULL) OR (CAST(@Wagon_ID AS int)) = [RW_WagonDislocation.Wagon_ID].[ID])) = 1
BEGIN
SELECT @Wagon_ID = [RW_WagonDislocation.Wagon_ID].[ID]
FROM RW_Wagon [RW_WagonDislocation.Wagon_ID]
WHERE [RW_WagonDislocation.Wagon_ID].TypeFlag_ID in (1,4,6) AND
((@Wagon_ IS NULL) OR (CAST(@Wagon_ AS char(8))) = [RW_WagonDislocation.Wagon_ID].[Number]) AND
((@Wagon_ID IS NULL) OR (CAST(@Wagon_ID AS int)) = [RW_WagonDislocation.Wagon_ID].[ID])
END ELSE SELECT @Error = '9B952BE1-5A8E-4B01-95C4-6CEEAB0AF80D' /*--Существует более одной записи*/,@MessageTable_ID = 21
END ELSE
BEGIN
SET @SubDOC = '<ADD>
<T21>
' + CASE WHEN @Wagon_ IS NOT NULL THEN '<C167>' + CAST(@Wagon_ AS NVARCHAR(255)) + '</C167>' ELSE '' END + '
</T21>
</ADD>'
EXEC TM_spAddDataXML 21,@SubDOC,'RU',@SubTableRow_ID OUT,@ERROR OUT,@MessageTable_ID OUT,@MessageColumn_ID OUT
IF @SubTableRow_ID IS NULL
BEGIN
IF @Error IS NULL SET @Error = '31329A2E-D55A-45AC-AD04-3086BB3A900D' --Запись не найдена
IF @MessageTable_ID IS NULL SET @MessageTable_ID = 21
END ELSE SET @Wagon_ID = @SubTableRow_ID
END ELSE SET @Wagon_ID = ((0))
/*Операции над вагоном*/
IF @_Flag_WagonOperation_ = 3 AND @_Flag_WagonOperation_2 = 3 SET @_Flag_WagonOperation_ID = 3
IF @_Flag_WagonOperation_ IN (1,2) OR @_Flag_WagonOperation_2 IN (1,2) SET @_Flag_WagonOperation_ID = 2
IF @ERROR IS NULL
IF (
@WagonOperation_ IS NOT NULL OR
@WagonOperation_2 IS NOT NULL OR
@WagonOperation_ID IS NOT NULL
) AND (@_Flag_WagonOperation_ID IN (1,2) OR @ID <= 0)
IF
EXISTS(SELECT *
FROM RW_WagonOperation [RW_WagonDislocation.WagonOperation_ID]
WHERE [RW_WagonDislocation.WagonOperation_ID].TypeFlag_ID in (1,4,6) AND
((@WagonOperation_ IS NULL) OR (CAST(@WagonOperation_ AS nvarchar(255))) = [RW_WagonDislocation.WagonOperation_ID].[Name_RU]) AND
((@WagonOperation_2 IS NULL) OR (CAST(@WagonOperation_2 AS char(2))) = [RW_WagonDislocation.WagonOperation_ID].[code]) AND
((@WagonOperation_ID IS NULL) OR (CAST(@WagonOperation_ID AS int)) = [RW_WagonDislocation.WagonOperation_ID].[ID]))
BEGIN
IF
(SELECT COUNT(*)
FROM RW_WagonOperation [RW_WagonDislocation.WagonOperation_ID]
WHERE [RW_WagonDislocation.WagonOperation_ID].TypeFlag_ID in (1,4,6) AND
((@WagonOperation_ IS NULL) OR (CAST(@WagonOperation_ AS nvarchar(255))) = [RW_WagonDislocation.WagonOperation_ID].[Name_RU]) AND
((@WagonOperation_2 IS NULL) OR (CAST(@WagonOperation_2 AS char(2))) = [RW_WagonDislocation.WagonOperation_ID].[code]) AND
((@WagonOperation_ID IS NULL) OR (CAST(@WagonOperation_ID AS int)) = [RW_WagonDislocation.WagonOperation_ID].[ID])) = 1
BEGIN
SELECT @WagonOperation_ID = [RW_WagonDislocation.WagonOperation_ID].[ID]
FROM RW_WagonOperation [RW_WagonDislocation.WagonOperation_ID]
WHERE [RW_WagonDislocation.WagonOperation_ID].TypeFlag_ID in (1,4,6) AND
((@WagonOperation_ IS NULL) OR (CAST(@WagonOperation_ AS nvarchar(255))) = [RW_WagonDislocation.WagonOperation_ID].[Name_RU]) AND
((@WagonOperation_2 IS NULL) OR (CAST(@WagonOperation_2 AS char(2))) = [RW_WagonDislocation.WagonOperation_ID].[code]) AND
((@WagonOperation_ID IS NULL) OR (CAST(@WagonOperation_ID AS int)) = [RW_WagonDislocation.WagonOperation_ID].[ID])
END ELSE SELECT @Error = '9B952BE1-5A8E-4B01-95C4-6CEEAB0AF80D' /*--Существует более одной записи*/,@MessageTable_ID = 26
END ELSE
BEGIN
SET @SubDOC = '<ADD>
<T26>
' + CASE WHEN @WagonOperation_2 IS NOT NULL THEN '<C213>' + CAST(@WagonOperation_2 AS NVARCHAR(255)) + '</C213>' ELSE '' END + '
' + CASE WHEN @WagonOperation_ IS NOT NULL THEN '<C215>' + CAST(@WagonOperation_ AS NVARCHAR(255)) + '</C215>' ELSE '' END + '
</T26>
</ADD>'
EXEC TM_spAddDataXML 26,@SubDOC,'RU',@SubTableRow_ID OUT,@ERROR OUT,@MessageTable_ID OUT,@MessageColumn_ID OUT
IF @SubTableRow_ID IS NULL
BEGIN
IF @Error IS NULL SET @Error = '31329A2E-D55A-45AC-AD04-3086BB3A900D' --Запись не найдена
IF @MessageTable_ID IS NULL SET @MessageTable_ID = 26
END ELSE SET @WagonOperation_ID = @SubTableRow_ID
END ELSE SET @WagonOperation_ID = ((0))
/*Статус вагона*/
IF @_Flag_WagonStatus_ = 3 AND @_Flag_WagonStatus_2 = 3 SET @_Flag_WagonStatus_ID = 3
IF @_Flag_WagonStatus_ IN (1,2) OR @_Flag_WagonStatus_2 IN (1,2) SET @_Flag_WagonStatus_ID = 2
IF @ERROR IS NULL
IF (
@WagonStatus_ IS NOT NULL OR
@WagonStatus_2 IS NOT NULL OR
@WagonStatus_ID IS NOT NULL
) AND (@_Flag_WagonStatus_ID IN (1,2) OR @ID <= 0)
IF
EXISTS(SELECT *
FROM RW_WagonStatus [RW_WagonDislocation.WagonStatus_ID]
WHERE [RW_WagonDislocation.WagonStatus_ID].TypeFlag_ID in (1,4,6) AND
((@WagonStatus_ IS NULL) OR (CAST(@WagonStatus_ AS nvarchar(255))) = [RW_WagonDislocation.WagonStatus_ID].[Name_RU]) AND
((@WagonStatus_2 IS NULL) OR (CAST(@WagonStatus_2 AS char(2))) = [RW_WagonDislocation.WagonStatus_ID].[code]) AND
((@WagonStatus_ID IS NULL) OR (CAST(@WagonStatus_ID AS int)) = [RW_WagonDislocation.WagonStatus_ID].[ID]))
BEGIN
IF
(SELECT COUNT(*)
FROM RW_WagonStatus [RW_WagonDislocation.WagonStatus_ID]
WHERE [RW_WagonDislocation.WagonStatus_ID].TypeFlag_ID in (1,4,6) AND
((@WagonStatus_ IS NULL) OR (CAST(@WagonStatus_ AS nvarchar(255))) = [RW_WagonDislocation.WagonStatus_ID].[Name_RU]) AND
((@WagonStatus_2 IS NULL) OR (CAST(@WagonStatus_2 AS char(2))) = [RW_WagonDislocation.WagonStatus_ID].[code]) AND
((@WagonStatus_ID IS NULL) OR (CAST(@WagonStatus_ID AS int)) = [RW_WagonDislocation.WagonStatus_ID].[ID])) = 1
BEGIN
SELECT @WagonStatus_ID = [RW_WagonDislocation.WagonStatus_ID].[ID]
FROM RW_WagonStatus [RW_WagonDislocation.WagonStatus_ID]
WHERE [RW_WagonDislocation.WagonStatus_ID].TypeFlag_ID in (1,4,6) AND
((@WagonStatus_ IS NULL) OR (CAST(@WagonStatus_ AS nvarchar(255))) = [RW_WagonDislocation.WagonStatus_ID].[Name_RU]) AND
((@WagonStatus_2 IS NULL) OR (CAST(@WagonStatus_2 AS char(2))) = [RW_WagonDislocation.WagonStatus_ID].[code]) AND
((@WagonStatus_ID IS NULL) OR (CAST(@WagonStatus_ID AS int)) = [RW_WagonDislocation.WagonStatus_ID].[ID])
END ELSE SELECT @Error = '9B952BE1-5A8E-4B01-95C4-6CEEAB0AF80D' /*--Существует более одной записи*/,@MessageTable_ID = 78
END ELSE
BEGIN
SET @SubDOC = '<ADD>
<T78>
' + CASE WHEN @WagonStatus_2 IS NOT NULL THEN '<C1515>' + CAST(@WagonStatus_2 AS NVARCHAR(255)) + '</C1515>' ELSE '' END + '
' + CASE WHEN @WagonStatus_ IS NOT NULL THEN '<C1517>' + CAST(@WagonStatus_ AS NVARCHAR(255)) + '</C1517>' ELSE '' END + '
</T78>
</ADD>'
EXEC TM_spAddDataXML 78,@SubDOC,'RU',@SubTableRow_ID OUT,@ERROR OUT,@MessageTable_ID OUT,@MessageColumn_ID OUT
IF @SubTableRow_ID IS NULL
BEGIN
IF @Error IS NULL SET @Error = '31329A2E-D55A-45AC-AD04-3086BB3A900D' --Запись не найдена
IF @MessageTable_ID IS NULL SET @MessageTable_ID = 78
END ELSE SET @WagonStatus_ID = @SubTableRow_ID
END ELSE SET @WagonStatus_ID = ((0))
IF @_CreateDate_ IS NOT NULL BEGIN SET @CreateDate = CONVERT(datetime,@_CreateDate_,102) END ELSE SET @CreateDate = (GETDATE())
IF @Wagon_ID IS NOT NULL BEGIN SET @Wagon_ID = CAST(@Wagon_ID AS int) END ELSE SET @Wagon_ID = ((0))
IF @StationDestination_ID IS NOT NULL BEGIN SET @StationDestination_ID = CAST(@StationDestination_ID AS int) END ELSE SET @StationDestination_ID = ((0))
IF @WagonOperation_ID IS NOT NULL BEGIN SET @WagonOperation_ID = CAST(@WagonOperation_ID AS int) END ELSE SET @WagonOperation_ID = ((0))
IF @DateOperation_ IS NOT NULL BEGIN SET @DateOperation = CONVERT(datetime,@DateOperation_,102) END ELSE SET @DateOperation = NULL
IF @StationOperation_ID IS NOT NULL BEGIN SET @StationOperation_ID = CAST(@StationOperation_ID AS int) END ELSE SET @StationOperation_ID = ((0))
IF @Distance IS NOT NULL BEGIN SET @Distance = CAST(@Distance AS int) END ELSE SET @Distance = NULL
IF @DistanceMeasure_ID IS NOT NULL BEGIN SET @DistanceMeasure_ID = CAST(@DistanceMeasure_ID AS int) END ELSE SET @DistanceMeasure_ID = ((0))
IF @WagonStatus_ID IS NOT NULL BEGIN SET @WagonStatus_ID = CAST(@WagonStatus_ID AS int) END ELSE SET @WagonStatus_ID = ((0))
IF @WagonTransportation_ID IS NULL SET @WagonTransportation_ID = ((0))
IF @WagonDislocation_ID IS NULL SET @WagonDislocation_ID = ((0))
IF @ModifyUser_ID IS NULL SET @ModifyUser_ID = ((0))

END
IF @ERROR is NULL
BEGIN
--Фильтр записей

--Проверка ID
IF @TypeOperation_ID is NULL
BEGIN
IF NOT EXISTS(SELECT * FROM RW_WagonDislocation WHERE ID = @ID)
BEGIN
SET @ID = NULL
--Поиск записи
SELECT @ID = ID
FROM RW_WagonDislocation
WHERE
(Wagon_ID = @Wagon_ID AND DateOperation = @DateOperation)
END
IF @ID is NULL
IF EXISTS(SELECT * FROM SYS_RoleUserRight WHERE [CreateUser_ID] = @_CreateUser_ID_ AND TypeRight_ID = 2 AND Role_ID = @RoleTableID)
BEGIN
--Преоброзование данных после проверок

IF (@Distance IS NULL) SET @Distance = (SELECT MAX(CASE sr.StationB_ID WHEN 0 THEN srm.DistanceB ELSE srm.DistanceA END) FROM RW_StationRouteMember srm JOIN RW_StationRoute sr ON sr.ID = srm.StationRoute_ID WHERE srm.Station_ID = @StationOperation_ID AND @StationDestination_ID IN (sr.StationA_ID,sr.StationB_ID))
IF (@WagonTransportation_ID = 0 AND EXISTS(SELECT * FROM RW_WagonTransportation WHERE Wagon_ID = @Wagon_ID AND DateDispatch < @DateOperation AND (DateArrival IS NULL OR DateArrival > @DateOperation))) SET @WagonTransportation_ID = (SELECT TOP 1 ID FROM RW_WagonTransportation WHERE Wagon_ID = @Wagon_ID AND DateDispatch < @DateOperation AND (DateArrival IS NULL OR DateArrival > @DateOperation) ORDER BY DateDispatch DESC)
--Проверка записи

--Добавление записи
IF @ERROR is NULL
BEGIN

INSERT INTO RW_WagonDislocation(
[Distance],
[Wagon_ID],
[DistanceMeasure_ID],
[WagonOperation_ID],
[StationDestination_ID],
[StationOperation_ID],
[WagonStatus_ID],
[CreateUser_ID],
[CreateDate],
[TypeFlag_ID],
[Role_ID],
[DateOperation],
[WagonTransportation_ID],
[WagonDislocation_ID],
[ModifyUser_ID],
[ModifyDate]) VALUES (
@Distance,
@Wagon_ID,
@DistanceMeasure_ID,
@WagonOperation_ID,
@StationDestination_ID,
@StationOperation_ID,
@WagonStatus_ID,
@CreateUser_ID,
@CreateDate,
@TypeFlag_ID,
@Role_ID,
@DateOperation,
@WagonTransportation_ID,
@WagonDislocation_ID,
@ModifyUser_ID,
@ModifyDate)
SET @ID = @@IDENTITY

IF (NOT EXISTS(SELECT * FROM RW_WagonStat WHERE Wagon_ID = @Wagon_ID))
BEGIN
INSERT RW_WagonStat (Wagon_ID,WagonDislocation_ID,WagonDislocationDateOperation) VALUES (@Wagon_ID,@ID,@DateOperation)
END
IF (EXISTS(SELECT * FROM RW_WagonStat WHERE Wagon_ID = @Wagon_ID AND (WagonDislocationDateOperation IS NULL OR WagonDislocationDateOperation < @DateOperation)))
BEGIN
UPDATE RW_WagonStat SET WagonDislocation_ID = @ID,WagonDislocationDateOperation = @DateOperation WHERE Wagon_ID = @Wagon_ID
END
IF (@StationDestination_ID = @StationOperation_ID AND EXISTS(SELECT * FROM RW_WagonTransportation WHERE Wagon_ID = @Wagon_ID AND DateArrival IS NULL AND DateDispatch < @DateOperation))
BEGIN
UPDATE RW_WagonTransportation SET DateArrival = @DateOperation WHERE Wagon_ID = @Wagon_ID AND DateArrival IS NULL AND DateDispatch < @DateOperation
END
IF (EXISTS(SELECT * FROM RW_WagonTransportation wt JOIN RW_StationRoute sr ON sr.ID = wt.StationRoute_ID AND sr.StationB_ID != @StationDestination_ID WHERE wt.Wagon_ID = @Wagon_ID AND wt.DateArrival IS NULL AND wt.DateDispatch < @DateOperation))
BEGIN
UPDATE RW_WagonTransportation SET DateArrival = @DateOperation FROM RW_WagonTransportation wt JOIN RW_StationRoute sr ON sr.ID = wt.StationRoute_ID AND sr.StationB_ID != @StationDestination_ID WHERE wt.Wagon_ID = @Wagon_ID AND wt.DateArrival IS NULL AND wt.DateDispatch < @DateOperation
END
IF (EXISTS(SELECT * FROM RW_ContainerStat cs JOIN RW_ContainerTransportation ct ON ct.ID = cs.ContainerTransportation_ID AND @WagonTransportation_ID != 0 AND WagonTransportation_ID = @WagonTransportation_ID WHERE (WagonDislocationDateOperation IS NULL OR WagonDislocationDateOperation < @DateOperation)))
BEGIN
UPDATE RW_ContainerStat SET WagonDislocation_ID = @ID,WagonDislocationDateOperation = @DateOperation FROM RW_ContainerStat cs JOIN RW_ContainerTransportation ct ON ct.ID = cs.ContainerTransportation_ID AND @WagonTransportation_ID != 0 AND WagonTransportation_ID = @WagonTransportation_ID WHERE (WagonDislocationDateOperation IS NULL OR WagonDislocationDateOperation < @DateOperation)
END
IF (NOT EXISTS(SELECT * FROM RW_ContainerDislocation cd JOIN RW_ContainerTransportation ct ON ct.ID = cd.ContainerTransportation_ID AND ct.WagonTransportation_ID = @WagonTransportation_ID WHERE WagonDislocation_ID = @ID))
BEGIN
INSERT RW_ContainerDislocation (Container_ID,ContainerStatus_ID,ContainerTransportation_ID,WagonDislocation_ID) SELECT Container_ID,ContainerStatus_ID,ID,@ID FROM RW_ContainerTransportation WHERE ID != 0 AND WagonTransportation_ID != 0 AND WagonTransportation_ID = @WagonTransportation_ID
END
SET @TypeOperation_ID = 1
END
END ELSE SET @ERROR = '594433DD-D4FC-42CF-ACAE-CFBD102D71DC'
ELSE
BEGIN
--Проверка дупликата

IF @TypeOperation_ID is NULL
BEGIN
--Заполнение переменных из записи
SELECT
@CreateUser_ID = CASE WHEN @_Flag_CreateUser_ID IN (1,2,3) THEN @CreateUser_ID ELSE [CreateUser_ID] END,
@CreateDate = CASE WHEN @_Flag_CreateDate IN (1,2,3) THEN @CreateDate ELSE [CreateDate] END,
@TypeFlag_ID = CASE WHEN @_Flag_TypeFlag_ID IN (1,2,3) THEN @TypeFlag_ID ELSE [TypeFlag_ID] END,
@Role_ID = CASE WHEN @_Flag_Role_ID IN (1,2,3) THEN @Role_ID ELSE [Role_ID] END,
@Wagon_ID = CASE WHEN @_Flag_Wagon_ID IN (1,2,3) THEN @Wagon_ID ELSE [Wagon_ID] END,
@StationDestination_ID = CASE WHEN @_Flag_StationDestination_ID IN (1,2,3) THEN @StationDestination_ID ELSE [StationDestination_ID] END,
@WagonOperation_ID = CASE WHEN @_Flag_WagonOperation_ID IN (1,2,3) THEN @WagonOperation_ID ELSE [WagonOperation_ID] END,
@DateOperation = CASE WHEN @_Flag_DateOperation IN (1,2,3) THEN @DateOperation ELSE [DateOperation] END,
@StationOperation_ID = CASE WHEN @_Flag_StationOperation_ID IN (1,2,3) THEN @StationOperation_ID ELSE [StationOperation_ID] END,
@Distance = CASE WHEN @_Flag_Distance IN (1,2,3) THEN @Distance ELSE [Distance] END,
@DistanceMeasure_ID = CASE WHEN @_Flag_DistanceMeasure_ID IN (1,2,3) THEN @DistanceMeasure_ID ELSE [DistanceMeasure_ID] END,
@WagonTransportation_ID = CASE WHEN @_Flag_WagonTransportation_ID IN (1,2,3) THEN @WagonTransportation_ID ELSE [WagonTransportation_ID] END,
@WagonStatus_ID = CASE WHEN @_Flag_WagonStatus_ID IN (1,2,3) THEN @WagonStatus_ID ELSE [WagonStatus_ID] END,
@WagonDislocation_ID = CASE WHEN @_Flag_WagonDislocation_ID IN (1,2,3) THEN @WagonDislocation_ID ELSE [WagonDislocation_ID] END,
@ModifyUser_ID = CASE WHEN @_Flag_ModifyUser_ID IN (1,2,3) THEN @ModifyUser_ID ELSE [ModifyUser_ID] END,
@ModifyDate = CASE WHEN @_Flag_ModifyDate IN (1,2,3) THEN @ModifyDate ELSE [ModifyDate] END
FROM RW_WagonDislocation WHERE ID = @ID
IF EXISTS(SELECT * FROM SYS_RoleUserRight WHERE [CreateUser_ID] = @_CreateUser_ID_ AND TypeRight_ID = 3 AND Role_ID = @Role_ID) OR @CreateUser_ID = @_CreateUser_ID_
BEGIN
--Преоброзование данных после проверок

IF (@Distance IS NULL) SET @Distance = (SELECT MAX(CASE sr.StationB_ID WHEN 0 THEN srm.DistanceB ELSE srm.DistanceA END) FROM RW_StationRouteMember srm JOIN RW_StationRoute sr ON sr.ID = srm.StationRoute_ID WHERE srm.Station_ID = @StationOperation_ID AND @StationDestination_ID IN (sr.StationA_ID,sr.StationB_ID))
IF (@WagonTransportation_ID = 0 AND EXISTS(SELECT * FROM RW_WagonTransportation WHERE Wagon_ID = @Wagon_ID AND DateDispatch < @DateOperation AND (DateArrival IS NULL OR DateArrival > @DateOperation))) SET @WagonTransportation_ID = (SELECT TOP 1 ID FROM RW_WagonTransportation WHERE Wagon_ID = @Wagon_ID AND DateDispatch < @DateOperation AND (DateArrival IS NULL OR DateArrival > @DateOperation) ORDER BY DateDispatch DESC)
--Проверка записи

--Обновление записи
IF @ERROR is NULL
BEGIN

IF @_Flag_TypeFlag_ID NOT IN(1,2,3) SET @TypeFlag_ID = 1
UPDATE RW_WagonDislocation SET
[Distance] = @Distance,
[Wagon_ID] = @Wagon_ID,
[DistanceMeasure_ID] = @DistanceMeasure_ID,
[WagonOperation_ID] = @WagonOperation_ID,
[StationDestination_ID] = @StationDestination_ID,
[StationOperation_ID] = @StationOperation_ID,
[WagonStatus_ID] = @WagonStatus_ID,
[CreateUser_ID] = @CreateUser_ID,
[CreateDate] = @CreateDate,
[TypeFlag_ID] = @TypeFlag_ID,
[Role_ID] = @Role_ID,
[DateOperation] = @DateOperation,
[WagonTransportation_ID] = @WagonTransportation_ID,
[WagonDislocation_ID] = @WagonDislocation_ID,
[ModifyUser_ID] = @ModifyUser_ID,
[ModifyDate] = @ModifyDate
WHERE ID = @ID

IF (NOT EXISTS(SELECT * FROM RW_WagonStat WHERE Wagon_ID = @Wagon_ID))
BEGIN
INSERT RW_WagonStat (Wagon_ID,WagonDislocation_ID,WagonDislocationDateOperation) VALUES (@Wagon_ID,@ID,@DateOperation)
END
IF (EXISTS(SELECT * FROM RW_WagonStat WHERE Wagon_ID = @Wagon_ID AND (WagonDislocationDateOperation IS NULL OR WagonDislocationDateOperation < @DateOperation)))
BEGIN
UPDATE RW_WagonStat SET WagonDislocation_ID = @ID,WagonDislocationDateOperation = @DateOperation WHERE Wagon_ID = @Wagon_ID
END
IF (@StationDestination_ID = @StationOperation_ID AND EXISTS(SELECT * FROM RW_WagonTransportation WHERE Wagon_ID = @Wagon_ID AND DateArrival IS NULL AND DateDispatch < @DateOperation))
BEGIN
UPDATE RW_WagonTransportation SET DateArrival = @DateOperation WHERE Wagon_ID = @Wagon_ID AND DateArrival IS NULL AND DateDispatch < @DateOperation
END
IF (EXISTS(SELECT * FROM RW_WagonTransportation wt JOIN RW_StationRoute sr ON sr.ID = wt.StationRoute_ID AND sr.StationB_ID != @StationDestination_ID WHERE wt.Wagon_ID = @Wagon_ID AND wt.DateArrival IS NULL AND wt.DateDispatch < @DateOperation))
BEGIN
UPDATE RW_WagonTransportation SET DateArrival = @DateOperation FROM RW_WagonTransportation wt JOIN RW_StationRoute sr ON sr.ID = wt.StationRoute_ID AND sr.StationB_ID != @StationDestination_ID WHERE wt.Wagon_ID = @Wagon_ID AND wt.DateArrival IS NULL AND wt.DateDispatch < @DateOperation
END
IF (EXISTS(SELECT * FROM RW_ContainerStat cs JOIN RW_ContainerTransportation ct ON ct.ID = cs.ContainerTransportation_ID AND @WagonTransportation_ID != 0 AND WagonTransportation_ID = @WagonTransportation_ID WHERE (WagonDislocationDateOperation IS NULL OR WagonDislocationDateOperation < @DateOperation)))
BEGIN
UPDATE RW_ContainerStat SET WagonDislocation_ID = @ID,WagonDislocationDateOperation = @DateOperation FROM RW_ContainerStat cs JOIN RW_ContainerTransportation ct ON ct.ID = cs.ContainerTransportation_ID AND @WagonTransportation_ID != 0 AND WagonTransportation_ID = @WagonTransportation_ID WHERE (WagonDislocationDateOperation IS NULL OR WagonDislocationDateOperation < @DateOperation)
END
IF (NOT EXISTS(SELECT * FROM RW_ContainerDislocation cd JOIN RW_ContainerTransportation ct ON ct.ID = cd.ContainerTransportation_ID AND ct.WagonTransportation_ID = @WagonTransportation_ID WHERE WagonDislocation_ID = @ID))
BEGIN
INSERT RW_ContainerDislocation (Container_ID,ContainerStatus_ID,ContainerTransportation_ID,WagonDislocation_ID) SELECT Container_ID,ContainerStatus_ID,ID,@ID FROM RW_ContainerTransportation WHERE ID != 0 AND WagonTransportation_ID != 0 AND WagonTransportation_ID = @WagonTransportation_ID
END
SET @TypeOperation_ID = 2
END
END ELSE SET @ERROR = 'C90F9F89-5962-4565-B9BB-BA9C176B5BC6'
END
END
END
END
IF @ERROR IS NOT NULL SET @TypeOperation_ID = 3
IF @_FileID_ IS NOT NULL
UPDATE SYS_LogExchange SET File_ID = @_FileID_,
CreateUser_ID = @_CreateUser_ID_,
TypeOperation_ID = @TypeOperation_ID,
Role_ID = @RoleTableID,
TypeFlag_ID = 1,
ObjectPK_ID = @ID,
ObjectMessage_ID = @ERROR,
MessageTable_ID = @MessageTable_ID,
MessageColumn_ID = @MessageColumn_ID
WHERE ID = @LogExchange_ID
ELSE
UPDATE SYS_LogExchange SET TypeOperation_ID = @TypeOperation_ID,
CreateUser_ID = @_CreateUser_ID_,
Role_ID = @RoleTableID,
TypeFlag_ID = 1,
ObjectPK_ID = @ID,
ObjectMessage_ID = @ERROR,
MessageTable_ID = @MessageTable_ID,
MessageColumn_ID = @MessageColumn_ID
WHERE ID = @LogExchange_ID
SET @SubTableRow_ID = @ID
END
END TRY
BEGIN CATCH
DECLARE @Content_Input xml,@Object_Name varchar(255),@LogEvent_ID int
SET @Object_Name = 'tmpTM_spAddDataXML_RU_36'
SET @Content_Input = '<Parametr><' + @Object_Name + ' Name="@IDTable" Value="36"/>' +
CASE WHEN @LogExchange_ID IS NOT NULL THEN '<' + @Object_Name + ' Name="@LogExchange_ID" Value="' + CAST(@LogExchange_ID AS varchar(max)) + '"/>' ELSE '' END
+ '</Parametr>'
EXEC TM_spLogEventAddError @Object_Name,@Content_Input,@LogEvent_ID out
SET @ERROR = '38fb8e21-6477-45ea-a3b5-dc5fef22e211'
UPDATE SYS_LogExchange SET TypeOperation_ID = @TypeOperation_ID,
CreateUser_ID = @_CreateUser_ID_,
LogEvent_ID = @LogEvent_ID,
TypeFlag_ID = 1,
ObjectPK_ID = @ID,
ObjectMessage_ID = @ERROR,
MessageTable_ID = @MessageTable_ID,
MessageColumn_ID = @MessageColumn_ID
WHERE ID = @LogExchange_ID
END CATCH