Notes

USE [PA_AdminDB]
GO
/****** Object: StoredProcedure [dbo].[ProcessImportedWO] Script Date: 16.02.2026 14:56:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ProcessImportedWO]
AS
BEGIN

DECLARE @json NVARCHAR(MAX);
DECLARE @ID INT;
DECLARE @WOName NVARCHAR(50);
DECLARE @WOStatus INT;
DECLARE @Product NVARCHAR(50);
DECLARE @PlannedQuantity INT;
DECLARE @PlannedStartDate NVARCHAR(50);
DECLARE @PlannedEndDate NVARCHAR(50);
DECLARE @RouteName NVARCHAR(50);
DECLARE @ProdLineName NVARCHAR(50);
DECLARE @RouteRevision INT;
DECLARE @cancelFlag BIT;

DECLARE @WO_DB_PlannedStartDate datetime;
DECLARE @WO_DB_PlannedEndDate datetime;
DECLARE @WO_DB_PlannedQuantity int;
DECLARE @WO_DB_Status int;

DECLARE import_cursor CURSOR FAST_FORWARD FOR
SELECT TOP 100 ID, WOName, WOStatus, Product, PlannedQuantity, PlannedStartDate, PlannedEndDate
FROM dbo.ERP_WO_Import WHERE CAST(PlannedStartDate AS DATE) > CAST(GETDATE() AS DATE);

OPEN import_cursor;
FETCH NEXT FROM import_cursor INTO @ID, @WOName, @WOStatus, @Product, @PlannedQuantity, @PlannedStartDate, @PlannedEndDate;

WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY

SET @cancelFlag = NULL;
SET @RouteName = NULL;
SET @ProdLineName = NULL;
SET @RouteRevision = NULL;
SET @WO_DB_PlannedStartDate = NULL;
SET @WO_DB_PlannedEndDate = NULL;
SET @WO_DB_PlannedQuantity = NULL;
SET @WO_DB_Status = NULL;

IF (@WOStatus = 30 OR @WOStatus = 91)
BEGIN
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Docasne omezeni na STATIC !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
IF EXISTS (SELECT 1 FROM [SOADB].[dbo].[Products_Base] WHERE Product_Family_Id = 6 AND LTRIM(RTRIM(Prod_Code)) = LTRIM(RTRIM(@Product)) COLLATE SQL_Latin1_General_CP1_CI_AS)
BEGIN

-- najit obednavku v systemu - pokud neni -> import, jinak porovnat
SELECT TOP 1
@WO_DB_PlannedStartDate = [PlannedStartDate],
@WO_DB_PlannedEndDate = [PlannedEndDate],
@WO_DB_PlannedQuantity = [PlannedQuantity],
@WO_DB_Status = [Status]
FROM [SOADB].[WorkOrder].[WorkOrders] WHERE LTRIM(RTRIM([Name])) = LTRIM(RTRIM(@WOName)) COLLATE SQL_Latin1_General_CP1_CI_AS

-- WO je v PA jiz ve stavu completed
IF (@WO_DB_Status IS NOT NULL AND @WO_DB_Status = 40)
BEGIN
INSERT INTO [PA_AdminDB].[dbo].[WOLog] VALUES (GetDate(), @WOName, 'Work Order already exists and is in complete state.', null);

FETCH NEXT FROM import_cursor INTO @ID, @WOName, @WOStatus, @Product, @PlannedQuantity, @PlannedStartDate, @PlannedEndDate;
CONTINUE;
END

-- WO je v PA jiz ve stavu canceled
IF (@WO_DB_Status IS NOT NULL AND @WO_DB_Status = 50)
BEGIN
INSERT INTO [PA_AdminDB].[dbo].[WOLog] VALUES (GetDate(), @WOName, 'Work Order already exists and is in canceled state.', null);

FETCH NEXT FROM import_cursor INTO @ID, @WOName, @WOStatus, @Product, @PlannedQuantity, @PlannedStartDate, @PlannedEndDate;
CONTINUE;
END

-- pro import se bere posledni revize
SELECT TOP 1
@RouteName = [name],
@ProdLineName = (SELECT TOP 1 [Pl_DESC] FROM [SOADB].[dbo].[Prod_Lines_Base] WHERE PL_Id = [productionLineId]),
@RouteRevision = [revision]
FROM [SOADB].[route].[Route]
WHERE [status] = 'Released' AND LTRIM(RTRIM([name])) = LTRIM(RTRIM(@Product)) COLLATE SQL_Latin1_General_CP1_CI_AS ORDER BY [revision] DESC;

IF (@RouteName IS NOT NULL)
BEGIN
-- STATUS CANCEL
IF (@WOStatus = 91)
BEGIN
IF EXISTS (SELECT 1 FROM [SOADB].[WorkOrder].[WorkOrders] WHERE LTRIM(RTRIM([Name])) = LTRIM(RTRIM(@WOName)) COLLATE SQL_Latin1_General_CP1_CI_AS)
SET @cancelFlag = 1;
END
ELSE
BEGIN
-- pokud je status PRINTED, zkontroluj data s jiz ulozenym WO
IF (CAST(@PlannedStartDate AS datetimeoffset) = @WO_DB_PlannedStartDate AND CAST(@PlannedEndDate AS datetimeoffset) = @WO_DB_PlannedEndDate AND @WO_DB_PlannedQuantity = @PlannedQuantity)
BEGIN
INSERT INTO [PA_AdminDB].[dbo].[WOLog] VALUES (GetDate(), @WOName, 'Work order already exists, no change', null);

FETCH NEXT FROM import_cursor INTO @ID, @WOName, @WOStatus, @Product, @PlannedQuantity, @PlannedStartDate, @PlannedEndDate;
CONTINUE;
END
END


SET @json = CONCAT(
N'{', CHAR(13), CHAR(10),
N'"schemaVersion": ', 8, N',', CHAR(13), CHAR(10),
N'"workOrderName": "' + @WOName + '"', N',', CHAR(13), CHAR(10),
N'"producedMaterialName": "' + @Product + '"', N',', CHAR(13), CHAR(10),
N'"plannedLineName": "' + @ProdLineName + '"', N',', CHAR(13), CHAR(10),
N'"priority": ', 0, N',', CHAR(13), CHAR(10),
CASE WHEN @PlannedStartDate IS NOT NULL THEN CONCAT(N'"plannedStartDate": "', @PlannedStartDate, N'",', CHAR(13), CHAR(10)) END,
CASE WHEN @PlannedEndDate IS NOT NULL THEN CONCAT(N'"plannedEndDate": "', @PlannedEndDate, N'",', CHAR(13), CHAR(10)) END,
N'"routeDefinitionName": "' + @RouteName + '"', N',', CHAR(13), CHAR(10),
N'"routeDefinitionRevision": ', @RouteRevision, N',', CHAR(13), CHAR(10),
N'"plannedQuantity": ', @PlannedQuantity, N',', CHAR(13), CHAR(10),
N'"status": ', CASE WHEN @cancelFlag IS NULL THEN 'null' ELSE '"cancelled"' END, N',', CHAR(13), CHAR(10),
N'"operationsGroup": {', CHAR(13), CHAR(10),
N' "route": {', CHAR(13), CHAR(10),
N' "billOfMaterials": [],', CHAR(13), CHAR(10),
N' "propertyValues": []', CHAR(13), CHAR(10),
N' },', CHAR(13), CHAR(10),
N' "operations": []', CHAR(13), CHAR(10),
N'},', CHAR(13), CHAR(10),
N'"materialLots": [', CHAR(13), CHAR(10),
N' {', CHAR(13), CHAR(10),
N' "plannedQuantity": ', @PlannedQuantity,
N',', CHAR(13), CHAR(10),
N' "lotIdentifier": ', '"1st series"',
CHAR(13), CHAR(10),
N' }', CHAR(13), CHAR(10),
N']', CHAR(13), CHAR(10),
N'}');

INSERT INTO [SOADB].[erp].Erp_integration_inbound_messages
([Inserted_Date], [Message_Type], [Media_Type], [Message], [Inserted_By])
VALUES
(GETUTCDATE(), 'workOrder', 'application/json', @json, 'import');

INSERT INTO [PA_AdminDB].[dbo].[WOLog] VALUES (GetDate(), @WOName, 'Imported', @json);
END
ELSE
BEGIN
INSERT INTO [PA_AdminDB].[dbo].[WOLog] VALUES (GetDate(), @WOName, 'Unknown route', null);
END
END
ELSE
BEGIN
INSERT INTO [PA_AdminDB].[dbo].[WOLog] VALUES (GetDate(), @WOName, 'Skipped - product not found', null);
END
END
ELSE
BEGIN
INSERT INTO [PA_AdminDB].[dbo].[WOLog] VALUES (GetDate(), @WOName, 'Skipped - unsupported status: ' + CAST(@WOStatus AS NVARCHAR(10)), null);
END

FETCH NEXT FROM import_cursor INTO @ID, @WOName, @WOStatus, @Product, @PlannedQuantity, @PlannedStartDate, @PlannedEndDate;

END TRY
BEGIN CATCH
INSERT INTO [PA_AdminDB].[dbo].[WOLog] VALUES (GetDate(), @WOName, ERROR_MESSAGE(), null);
FETCH NEXT FROM import_cursor INTO @ID, @WOName, @WOStatus, @Product, @PlannedQuantity, @PlannedStartDate, @PlannedEndDate;
END CATCH
END

CLOSE import_cursor;
DEALLOCATE import_cursor;

END