DROP PROCEDURE IF EXISTS dbo.sx_pf_GET_FlexList; GO /* GET Operation for receiving a context sensitiv List API deliviers List Values fitting to the sended context Parameters Procedure must be editend to fit user needs and recreated after each API Update When updating this procedure, you must reset the rights for it Gerd Tautenhahn for Saxess Software GmbH Last modified: 10/2022 for OCT 5.8 Test call DECLARE @RC AS NVARCHAR(255) --should return Data and 200 EXEC @RC = dbo.sx_pf_GET_FlexList 'SQL','1','1','1','1','','','','' PRINT @RC Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'PROCEDURE', 'sx_pf_GET_FlexList',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'PROCEDURE', 'sx_pf_GET_FlexList','PARAMETER',NULL) */ CREATE PROCEDURE dbo.sx_pf_GET_FlexList @Username NVARCHAR(255) ,@FactoryID NVARCHAR(255) ,@ProductLineID NVARCHAR(255) ,@ProductID NVARCHAR(255) ,@ValueSeriesID NVARCHAR(255) ,@TimeID BIGINT ,@GlobalattributeNumber NVARCHAR(255) ,@PageType NVARCHAR(255) -- PDT (ProductDataTable), PLP (ProductLinePage) ,@SearchString NVARCHAR(255) -- any Substring from searched list values AS BEGIN SET NOCOUNT ON; DECLARE @GlobalattributeAlias NVARCHAR(255) = N''; DECLARE @SearchDoneFlag INT = 0; DECLARE @ProductKey BIGINT = 0; DECLARE @DummyTable TABLE (Dummy NVARCHAR(255) NOT NULL) -- only for the samples to compile DECLARE @DummyInt INT -- Logging Block DECLARE @TransactUsername NVARCHAR(255) = N''; DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID); DECLARE @EffectedRows INT = 0; -- SET during Execution DECLARE @ResultCode INT = 501; -- SET during Execution DECLARE @TimestampCall DATETIME = GETUTCDATE(); DECLARE @Comment NVARCHAR(2000) = N''; -- SET during Execution DECLARE @ParameterString NVARCHAR(MAX) = N'''' + ISNULL(@Username, N'NULL') + N''',''' + ISNULL(@FactoryID, N'NULL') + N''',''' + ISNULL(@ProductLineID, N'NULL') + N''',''' + ISNULL(@ProductID, N'NULL') + N''',''' + ISNULL(@ValueSeriesID, N'NULL') + N''',' + CAST(ISNULL(@TimeID, N'NULL') AS NVARCHAR(255)) + N',''' + ISNULL(@GlobalattributeNumber, N'NULL') + N''',''' + ISNULL(@PageType, N'NULL') + N''',''' + ISNULL(@SearchString, N'NULL') + N''''; DECLARE @Template NVARCHAR(255) = N''; -- STEP 0.1 - NULL Protection IF @Username IS NULL SET @Username = N''; IF @FactoryID IS NULL SET @FactoryID = N''; IF @ProductLineID IS NULL SET @ProductLineID = N''; IF @ProductID IS NULL SET @ProductID = N''; IF @ValueSeriesID IS NULL SET @ValueSeriesID = N''; IF @TimeID IS NULL SET @TimeID = 0; IF @GlobalattributeNumber IS NULL SET @GlobalattributeNumber = ''; IF @PageType IS NULL SET @PageType = N''; -- add wildcards to SeachString SET @SearchString = '%' + ISNULL(@SearchString, '') + '%'; BEGIN TRY BEGIN TRANSACTION sx_pf_GET_FlexList; -- Protect input parameters SET @Username = dbo.sx_pf_pProtectString (@Username); SET @FactoryID = dbo.sx_pf_pProtectID (@FactoryID); SET @ProductLineID = dbo.sx_pf_pProtectID (@ProductLineID); SET @ProductID = dbo.sx_pf_pProtectID (@ProductID); SET @ValueSeriesID = dbo.sx_pf_pProtectID (@ValueSeriesID); -- Determine transaction user SELECT @TransactUsername = dbo.sx_pf_Determine_TransactionUsername (@Username); IF @TransactUsername = N'403' BEGIN SET @ResultCode = 403; RAISERROR('Transaction user don`t exists', 16, 10); END; -- Get template SELECT @Template = COALESCE(Template,'') FROM planning.tdProducts WHERE FactoryID = @FactoryID AND ProductLineID = @ProductLineID AND ProductID = @ProductID -- Preparing of useful context informations -- **************************************************************************************************************************************************** -- Try to determine ProductKey IF @ProductID <> N'' AND @ProductLineID <> N'' AND @FactoryID <> N'' BEGIN SELECT @ProductKey = ProductKey FROM planning.tdProducts WHERE FactoryID = @FactoryID AND ProductLineID = @ProductLineID AND ProductID = @ProductID END -- Determine AliasName of Globalattribute, to define actions for Globalattributes by name IF @GlobalattributeNumber <> '' BEGIN IF OBJECT_ID('tempdb..#GANames') IS NOT NULL DROP TABLE #GANames; CREATE TABLE #GANames ( RowKey BIGINT IDENTITY (1,1) NOT NULL ,FactoryID NVARCHAR (255) COLLATE DATABASE_DEFAULT NOT NULL ,ProductLineID NVARCHAR (255) COLLATE DATABASE_DEFAULT NOT NULL ,Globalattribute NVARCHAR (255) COLLATE DATABASE_DEFAULT NOT NULL ,GlobalattributeNumber INT NOT NULL ,Aliasname NVARCHAR (255) COLLATE DATABASE_DEFAULT NOT NULL ) INSERT INTO #GANames SELECT FactoryID ,ProductLineID ,Globalattribute ,COALESCE(TRY_CAST(RIGHT(Globalattribute,2) AS INT), TRY_CAST(RIGHT(Globalattribute,1) AS INT)) AS GlobalattributeNumber ,Aliasname FROM planning.tdProductLines UNPIVOT ( Aliasname FOR Globalattribute IN ( GlobalattributeAlias1, GlobalattributeAlias2, GlobalattributeAlias3, GlobalattributeAlias4, GlobalattributeAlias5 ,GlobalattributeAlias6, GlobalattributeAlias7, GlobalattributeAlias8, GlobalattributeAlias9, GlobalattributeAlias10 ,GlobalattributeAlias11, GlobalattributeAlias12, GlobalattributeAlias13, GlobalattributeAlias14, GlobalattributeAlias15 ,GlobalattributeAlias16, GlobalattributeAlias17, GlobalattributeAlias18, GlobalattributeAlias19, GlobalattributeAlias20 ,GlobalattributeAlias21, GlobalattributeAlias22, GlobalattributeAlias23, GlobalattributeAlias24, GlobalattributeAlias25 ) ) unpiv WHERE FactoryID = @FactoryID AND ProductLineID = @ProductLineID; SELECT @GlobalattributeAlias = Aliasname FROM #GANames WHERE GlobalattributeNumber = @GlobalattributeNumber; END /* DEFINITION OF THE FLEX LISTS ***************************************************************************************************************************************************************** First Column must be "Hint" an commits the display hint to the frontend Second Column must have identifier character, this value is sended back to the selected cell Column Names must have an Alias (AS Displayname) with ONLY ONE "_", after which Orientation (L=Left, R= Right, C = Center) DataType (M=Money, S=String, I=Int) ColumnWidth (Integer Number) is coded as Frontend Parameter, e.g. Name_LS50 means the column ist labeled Name, has left orientation, is formated as sting an 50px width */ -- Sample for creating a List for all ValueSeries with a specific ID IF @ValueSeriesID = N'SKTO' BEGIN SELECT 'Bitte Sachkonto auswählen, im Eingabefeld kann per Zeichenfolge gefiltert werden.' AS Hint ,'1000' AS KontenID_LS30 ,'Anlagevermögen' FROM @DummyTable; /* WHERE KontenID LIKE @SearchString OR KontenName LIKE @SearchString ORDER BY KontenID */ SET @SearchDoneFlag = 1; END; -- Sample for creating a List for all ValueSeries with a specific ID in a specific Template IF @ValueSeriesID = N'SKTO' AND EXISTS ( SELECT Template FROM planning.tdProducts WHERE ProductKey = @ProductKey AND Template = N'Invest_VM' ) BEGIN SELECT 'Bitte Sachkonto auswählen, im Eingabefeld kann per Zeichenfolge gefiltert werden.' AS Hint ,'1000' AS KontenID_LS30 ,'Anlagevermögen' FROM @DummyTable /* WHERE KontenID LIKE @SearchString OR KontenName LIKE @SearchString ORDER BY KontenID */ SET @SearchDoneFlag = 1; END; -- Sample for Seach over parameter Product IF @ValueSeriesID = N'SKTO' BEGIN SELECT DISTINCT 'Bitte Sachkonto auswählen, im Eingabefeld kann per Zeichenfolge gefiltert werden.' AS Hint ,fV.ValueText AS KontenID_LS40 ,fV2.ValueText AS KontenName_LS180 FROM planning.tfValues fV -- Kontoname aus anderer Spalte LEFT JOIN ( SELECT TimeID ,ValueText FROM planning.tfValues WHERE FactoryID = 'ZP' AND ProductLineID = '1' AND ProductID = '1' AND ValueSeriesID = 'KTO_Name' ) fV2 ON fV.TimeID = fV2.TimeID WHERE fV.FactoryID = 'ZP' AND fV.ProductLineID = '1' AND fV.ProductID = '1' AND fV.ValueSeriesID = 'KTO_ID' AND (fV.ValueText LIKE @SearchString OR fV2.ValueText LIKE @SearchString) /* WHERE KontenID LIKE @SearchString OR KontenName LIKE @SearchString ORDER BY KontenID */ SET @SearchDoneFlag = 1; END -- Samples for other context IF @GlobalattributeAlias = N'SKTO' -- for this Globalattribute OR LEFT(@ValueSeriesID, 3) = N'KST' -- for all ValueSeries starting with this letters BEGIN SET @DummyInt = 1 END -- Sample Fallback -- delete in productive use to change fallback to Productive Fallback -- ************************************************************************************************************************************** IF @SearchDoneFlag = 0 BEGIN DECLARE @Cities AS TABLE([Name] NVARCHAR (255) NOT NULL); INSERT INTO @Cities VALUES (N'Aachen'), (N'Augsburg'), (N'Bergisch Gladbach'), (N'Berlin'), (N'Bielefeld'), (N'Bochum'), (N'Bonn'), (N'Bottrop'), (N'Braunschweig'), (N'Bremen'), (N'Bremerhaven'), (N'Chemnitz'), (N'Darmstadt'), (N'Dortmund'), (N'Dresden'), (N'Duisburg'), (N'Düsseldorf'), (N'Erfurt'), (N'Erlangen'), (N'Essen'), (N'Frankfurt am Main'), (N'Freiburg im Breisgau'), (N'Fürth'), (N'Gelsenkirchen'), (N'Göttingen'), (N'Hagen'), (N'Halle (Saale)'), (N'Hamburg'), (N'Hamm'), (N'Hannover'), (N'Heidelberg'), (N'Heilbronn'), (N'Herne'), (N'Ingolstadt'), (N'Jena'), (N'Karlsruhe'), (N'Kassel'), (N'Kiel'), (N'Koblenz'), (N'Köln'), (N'Krefeld'), (N'Leipzig'), (N'Leverkusen'), (N'Lübeck'), (N'Ludwigshafen am Rhein'), (N'Magdeburg'), (N'Mainz'), (N'Mannheim'), (N'Moers'), (N'Mönchengladbach'), (N'Mülheim an der Ruhr'), (N'München'), (N'Münster'), (N'Neuss'), (N'Nürnberg'), (N'Oberhausen'), (N'Offenbach am Main'), (N'Oldenburg'), (N'Osnabrück'), (N'Paderborn'), (N'Pforzheim'), (N'Potsdam'), (N'Recklinghausen'), (N'Regensburg'), (N'Remscheid'), (N'Reutlingen'), (N'Rostock'), (N'Saarbrücken'), (N'Siegen'), (N'Solingen'), (N'Stuttgart'), (N'Trier'), (N'Ulm'), (N'Wiesbaden'), (N'Wolfsburg'), (N'Wuppertal'), (N'Würzburg'); SELECT 'List of Cities, you can search with substring.' AS Hint ,UPPER(LEFT([Name], 4)) AS CityCode_LS50 ,[Name] AS CityName_CS200 FROM @Cities WHERE [Name] LIKE @SearchString ORDER BY [Name]; SET @SearchDoneFlag = 1 END -- Productive Fallback - if no flex search matched the query -- ************************************************************************************** IF @SearchDoneFlag = 0 BEGIN SELECT 'For this Seach Context, we sadly must tell to have no fitting content to offer. ' AS Hint ,'Nothing' AS Content_LS50 ,'is all I have to offer for you.' AS Sorry_LS200 END SET @EffectedRows += @@ROWCOUNT; SET @ResultCode = 200; COMMIT TRANSACTION sx_pf_GET_FlexList; END TRY BEGIN CATCH DECLARE @Error_state INT = ERROR_STATE(); SET @Comment = ERROR_MESSAGE(); ROLLBACK TRANSACTION sx_pf_GET_FlexList; IF @Error_state <> 10 BEGIN SET @ResultCode = 500; PRINT 'Rollback due to not executable command.'; END ELSE IF @ResultCode IS NULL OR @ResultCode/100 = 2 BEGIN SET @ResultCode = 500; END; END CATCH EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @EffectedRows, @ResultCode, @TimestampCall, @Comment; RETURN @ResultCode; END GO ALTER AUTHORIZATION ON OBJECT ::dbo.sx_pf_GET_FlexList TO OCTService GO -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'dbo' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'sx_pf_GET_FlexList' -- enter procedure name ,@SX_Owner NVARCHAR(255) = N'OCT.core' -- enter owner name of the procedure from list (OCT.core, OCT.modules, Custom) ,@SX_Module NVARCHAR(255) = N'CORE' -- enter module name as free text (CORE,FIN, DEBKRED, HR, ...) ,@SX_ShipmentFlag INT = 1 -- 0 = Demo object - out of shipment process -- STANDARD OBJECTS -- 1 = shiped from saxess standard without modification -- 2 = shiped from saxess standard modified FOR customer from saxess -- 3 = shiped from saxess standard modified FOR customer from partner -- 4 = shiped from saxess standard modified FROM customer themself for own needs -- CUSTOM OBJECTS -- 10 = shiped from saxess as customer specific object -- 11 = shiped from partner as customer specific object -- 12 = shiped from customer as own specific object ,@SX_UserHint NVARCHAR(2000) = N'' -- optional, fill if Procedure shall be offerend for end user (e.g. for Pivot / Datagrid usage) -- KEEP this default constants ************************************************************************* DECLARE @name NVARCHAR(255) = N'MS_Description' ,@level0type NVARCHAR(255) = N'SCHEMA' ,@level1type NVARCHAR(255) = N'PROCEDURE' ,@level2type NVARCHAR(255) = N'PARAMETER' ,@level2name NVARCHAR(255) = N'' ,@value NVARCHAR(1000) = N''; SET @value = @SX_Owner; EXEC sys.sp_addextendedproperty N'SX_Owner' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_Module; EXEC sys.sp_addextendedproperty N'SX_Module' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_ShipmentFlag; EXEC sys.sp_addextendedproperty N'SX_ShipmentFlag' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_UserHint; EXEC sys.sp_addextendedproperty N'SX_UserHint' ,@value,@level0type,@level0name,@level1type,@level1name; -- SET documententation ************************************************************************* -- SET Procedure documentation SET @value = N'GET Operation for receiving a context sensitiv List API deliviers List Values fitting to the sended context Parameters Procedure must be editend to fit user needs and recreated after each API Update When updating this procedure, you must reset the rights for it'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- SET parameter documentation SET @level2name = N'@Username'; SET @value = N'Username'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@FactoryID'; SET @value = N'FactoryID'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ProductLineID'; SET @value = N'ProductLineID'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ProductID'; SET @value = N'ProductID'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ValueSeriesID'; SET @value = N'ValueSeriesID'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@TimeID'; SET @value = N'TimeID'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalattributeNumber'; SET @value = N'GlobalattributeNumber'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@PageType'; SET @value = N'PageType'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@SearchString'; SET @value = N'SearchString'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS dbo.sx_pf_GET_Info_Monitoring; GO /* GET Operation for monitoring Properties Gerd Tautenhahn for Saxess Software GmbH Last modified: 10/2022 for OCT 5.9 Test call DECLARE @RC NVARCHAR(255); EXEC @RC = dbo.sx_pf_GET_Info_Monitoring 'SQL'; PRINT @RC; Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'PROCEDURE', 'sx_pf_GET_Info_Monitoring',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'PROCEDURE', 'sx_pf_GET_Info_Monitoring','PARAMETER',NULL) */ CREATE PROCEDURE dbo.sx_pf_GET_Info_Monitoring @Username NVARCHAR(255) WITH EXECUTE AS 'dbo' -- as OCTS owned procedure needs access to system table AS BEGIN SET NOCOUNT ON; DECLARE @TransactUsername NVARCHAR(255) = N''; DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID); DECLARE @ParameterString NVARCHAR(MAX) = N'''' + ISNULL(@Username, N'NULL') DECLARE @EffectedRows INT = 0; -- SET during Execution DECLARE @ResultCode INT = 501; -- SET during Execution DECLARE @TimestampCall DATETIME = GETUTCDATE(); DECLARE @Comment NVARCHAR(2000) = N''; -- SET during Execution -- STEP 0.1 - NULL Protection IF @Username IS NULL SET @Username = N''; BEGIN TRY BEGIN TRANSACTION sx_pf_GET_Info_Monitoring; -- STEP 0.2 - Protect input parameters SET @Username = dbo.sx_pf_pProtectString (@Username); -- STEP 1.1 - Determine transaction user SELECT @TransactUsername = dbo.sx_pf_Determine_TransactionUsername (@Username); IF @TransactUsername = N'403' BEGIN SET @ResultCode = 403; RAISERROR('Transaction user don`t exists', 16, 10); END; -- STEP 2 - Check rights EXEC @ResultCode = sx_pf_pGET_ClusterPropertiesReadRight @TransactUsername; IF @ResultCode <> 200 BEGIN RAISERROR('Invalid rights', 16, 10); END; -- STEP 3 - Select Values IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp; CREATE TABLE #temp ( Property NVARCHAR (255) COLLATE DATABASE_DEFAULT NOT NULL ,[Value] NVARCHAR (255) COLLATE DATABASE_DEFAULT NOT NULL ); INSERT INTO #temp -- Language SELECT 'Cluster_C03' AS Property, ValueText FROM planning.tgCluster WHERE PropertyID = 'C03' UNION ALL -- Clustername SELECT 'Cluster_C01' AS Property, ValueText FROM planning.tgCluster WHERE PropertyID = 'C01' UNION ALL -- API Version SELECT 'Cluster_C04' AS Property, ValueText FROM planning.tgCluster WHERE PropertyID = 'C04' UNION ALL -- FactoryCount SELECT 'FactoryCount' AS Property, CAST(COUNT(*) AS NVARCHAR) FROM planning.tdFactories UNION ALL -- ProductLineCount SELECT 'ProductLineCount' AS Property, CAST(COUNT(*) AS NVARCHAR) FROM planning.tdProductLines UNION ALL -- ProductCount SELECT 'ProductCount' AS Property, CAST(COUNT(*) AS NVARCHAR) FROM planning.tdProducts UNION ALL -- API Error 500 Count SELECT 'API_Error500' AS Property, CAST(COUNT(*) AS NVARCHAR) FROM system.tAPI_Log WHERE ReturnCode = 500 AND ProcessCode = '' UNION ALL -- API Error 404 Count SELECT 'API_Error404' AS Property, CAST(COUNT(*) AS NVARCHAR) FROM system.tAPI_Log WHERE ReturnCode = 404 AND ProcessCode = ''; SELECT * FROM #temp; SET @EffectedRows = @@ROWCOUNT; COMMIT TRANSACTION sx_pf_GET_Info_Monitoring; END TRY BEGIN CATCH DECLARE @Error_state INT = ERROR_STATE(); SET @Comment = ERROR_MESSAGE(); ROLLBACK TRANSACTION sx_pf_GET_Info_Monitoring; IF @Error_state <> 10 BEGIN SET @ResultCode = 500; PRINT 'Rollback due to not executable command.'; END; END CATCH EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @EffectedRows, @ResultCode, @TimestampCall, @Comment; RETURN @ResultCode; END GO ALTER AUTHORIZATION ON OBJECT ::dbo.sx_pf_GET_Info_Monitoring TO OCTService; GO -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'dbo' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'sx_pf_GET_Info_Monitoring' -- enter procedure name ,@SX_Owner NVARCHAR(255) = N'OCT.core' -- enter owner name of the procedure from list (OCT.core, OCT.modules, Custom) ,@SX_Module NVARCHAR(255) = N'CORE' -- enter module name as free text (CORE,FIN, DEBKRED, HR, ...) ,@SX_ShipmentFlag INT = 1 -- 0 = Demo object - out of shipment process -- STANDARD OBJECTS -- 1 = shiped from saxess standard without modification -- 2 = shiped from saxess standard modified FOR customer from saxess -- 3 = shiped from saxess standard modified FOR customer from partner -- 4 = shiped from saxess standard modified FROM customer themself for own needs -- CUSTOM OBJECTS -- 10 = shiped from saxess as customer specific object -- 11 = shiped from partner as customer specific object -- 12 = shiped from customer as own specific object ,@SX_UserHint NVARCHAR(2000) = N'' -- optional, fill if Procedure shall be offerend for end user (e.g. for Pivot / Datagrid usage) -- KEEP this default constants ************************************************************************* DECLARE @name NVARCHAR(255) = N'MS_Description' ,@level0type NVARCHAR(255) = N'SCHEMA' ,@level1type NVARCHAR(255) = N'PROCEDURE' ,@level2type NVARCHAR(255) = N'PARAMETER' ,@level2name NVARCHAR(255) = N'' ,@value NVARCHAR(1000) = N''; SET @value = @SX_Owner; EXEC sys.sp_addextendedproperty N'SX_Owner' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_Module; EXEC sys.sp_addextendedproperty N'SX_Module' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_ShipmentFlag; EXEC sys.sp_addextendedproperty N'SX_ShipmentFlag' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_UserHint; EXEC sys.sp_addextendedproperty N'SX_UserHint' ,@value,@level0type,@level0name,@level1type,@level1name; -- SET documententation ************************************************************************* -- SET Procedure documentation SET @value = N'GET Operation for monitoring Properties'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- SET parameter documentation SET @level2name = N'@Username'; SET @value = N'Username'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS dbo.sx_pf_POST_ProductDataTableValues; GO /* POST Operation for all values of the ProductDataTable The method can be switched between incremental POST and full POST Gerd Tautenhahn for Saxess Software GmbH Last modified: 12/2022 for OCT 5.8 Concept: - all Values are deliverd as Textarray - this requires a big split operation - the Text array must also deliver deleted values if it does a incrementel transmisson (deleted Values are delivered with empty ValueInt, ValueText, ValueFormula) - Values may be sended with '<#NV>' to keep existing values @ValuesInBracketsCommaSeparated format is: (ValueSeriesID,TimeID,Formula,Value,ValueText,ValueComment),(ValueSeriesIDTimeID,Formula,Value,ValueText,ValueComment) The whole string is single quoted and every parameter inside except the TimeID is single quoted if you check this in SQL Server Profiler or Table sx_pf_APILog, every String must have two inverted commas, the whole Parameter must have Single inverted comma this must show profiler: '[(''ValueSeriesID'',TimeID,''Formula'',''ValueInt'',''ValueText'',''ValueComment'')],[(''ValueSeriesID'',TimeID,''Formula'',''ValueInt'',''ValueText'',''ValueComment'')]' Informal: In further releases the )],[( delimiter was planend be replaced with CHAR(29) - which is no longer likely, as we will use JSON But CHAR(29) is at the moment used at internal temporary delimiter Test call 1. Empty FactoryID / ProductLineID / ProductID / ValueSeriesID / TimeID => 404 Not Found 2. TransactionUsername not determined for User => 403 Forbidden 3. Username does not have write access to ProductLine => 401 Unauthorized 4. Non exists Factory / ProductLine / Product keys => 404 Not Found 5. If ValueSeriesID not exists in planning.tdValueSeries for this ProductKey => 403 Forbidden 6. Product updated => 200 OK Test case 1 - Incremental Sending *************************************************************** 1. One Value for an existing Value must replace them 2. One Value for an not exiting Value must create it 3. If all Parameter are posted empty, the value is deleted 4. If one or more of the parameter are sended with <#NV> there Value is replaced with the existing value (or nothing if it exists not) 5. An ZeroEmpty Value in old or sended values is deleted DECLARE @RC INT DECLARE @Username NVARCHAR (255) = 'SQL' DECLARE @ProductID NVARCHAR (255) = '1' DECLARE @ProductLineID NVARCHAR (255) = 'U' DECLARE @FactoryID NVARCHAR (255) = 'ZT' DECLARE @IsIncrementalValuesFlag INT = 1 -- 3 Values with comments - NEVER place a linebreak in the sample array ! -- DECLARE @ValuesInBracketsCommaSeparated nvarchar(max) = '[(''K1'',20170115,''=IF(H25=4,3,2.1)'',''6'',''HaseTextValue'',''HaseComment'')],[(''K2'',20170215,''=IF(H25=4,3,2)'',''555'',''HaseTextValue'',''HaseComment2'')],[(''E1'',20170515,'''','''',''Text'',''Comment'')]' -- Delete the three values by sending them empty -- DECLARE @ValuesInBracketsCommaSeparated nvarchar(max) = '[(''K1'',20170115,'''','''','''',''HaseComment'')],[(''K2'',20170215,'''','''','''',''HaseComment2'')],[(''E1'',20170515,'''','''','''','''')]' -- Send an <#NV> Value for an existing numeric and an Text Value and delete one -- DECLARE @ValuesInBracketsCommaSeparated nvarchar(max) = '[(''K1'',20170115,'''',''<#NV>'','''',''HaseComment'')],[(''K2'',20170215,'''','''','''',''HaseComment2'')],[(''E1'',20170515,'''','''',''<#NV>'','''')]' EXECUTE @RC = dbo.sx_pf_POST_ProductDataTableValues @Username ,@ProductID ,@ProductLineID ,@FactoryID ,@IsIncrementalValuesFlag ,@ValuesInBracketsCommaSeparated; PRINT @RC; Test case 2 - fill all Values of an Product ************************************************ DECLARE @RC INT DECLARE @Username NVARCHAR(255) = 'SQL' DECLARE @ProductID NVARCHAR(255) = '1' DECLARE @ProductLineID NVARCHAR(255) = 'U' DECLARE @FactoryID NVARCHAR(255) = 'ZT' DECLARE @IsIncrementalValuesFlag INT = 0 DECLARE @ValuesInBracketsCommaSeparated nvarchar(max) = '' DECLARE @start_time DATETIME -- Generate fictive values for all TimeIDs of Products SELECT @ValuesInBracketsCommaSeparated = @ValuesInBracketsCommaSeparated + '[(''' + dVS.ValueSeriesID +''',' + CAST(TimeID AS nvarchar(255)) +',''=A2'',''1000'',''Hase | Maus'',''Komment|ar'')],' FROM planning.tdValueSeries dVS LEFT JOIN planning.tdTime dT ON dVS.ProductKey = dT.ProductKey WHERE dVS.FactoryID = @FactoryID AND dVS.ProductLineID = @ProductLineID AND dVS.ProductID = @ProductID SET @ValuesInBracketsCommaSeparated = LEFT(@ValuesInBracketsCommaSeparated, LEN(@ValuesInBracketsCommaSeparated) -1) PRINT @ValuesInBracketsCommaSeparated SET @start_time = GETUTCDATE() EXECUTE @RC = dbo.sx_pf_POST_ProductDataTableValues @Username ,@ProductID ,@ProductLineID ,@FactoryID ,@IsIncrementalValuesFlag ,@ValuesInBracketsCommaSeparated; PRINT @RC; SELECT RTRIM(CAST(DATEDIFF(MS, @start_time, GETUTCDATE()) AS CHAR(10))) AS 'TimeTaken' -- Testcase 3 - delete all values DECLARE @RC INT DECLARE @Username NVARCHAR (255) = 'SQL' DECLARE @ProductID NVARCHAR (255) = '1' DECLARE @ProductLineID NVARCHAR (255) = 'U' DECLARE @FactoryID NVARCHAR (255) = 'ZT' DECLARE @IsIncrementalValuesFlag INT = 0 DECLARE @ValuesInBracketsCommaSeparated NVARCHAR (255) = '' EXECUTE @RC = dbo.sx_pf_POST_ProductDataTableValues @Username ,@ProductID ,@ProductLineID ,@FactoryID ,@IsIncrementalValuesFlag ,@ValuesInBracketsCommaSeparated; PRINT @RC; Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'PROCEDURE', 'sx_pf_POST_ProductDataTableValues',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'PROCEDURE', 'sx_pf_POST_ProductDataTableValues','PARAMETER',NULL) */ CREATE PROCEDURE dbo.sx_pf_POST_ProductDataTableValues @Username NVARCHAR(255), @ProductID NVARCHAR(255), @ProductLineID NVARCHAR(255), @FactoryID NVARCHAR(255), @IsIncrementalValuesFlag INT, @ValuesInBracketsCommaSeparated NVARCHAR(MAX) /* */ AS BEGIN SET NOCOUNT ON; DECLARE @TransactUsername NVARCHAR(255) = N''; DECLARE @FactoryKey INT = 0; DECLARE @ProductLineKey INT = 0; DECLARE @ProductKey INT = 0; DECLARE @errorvalue NVARCHAR(255); DECLARE @errormessage NVARCHAR(2000); DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID); DECLARE @ParameterString NVARCHAR(MAX) = N'''' + ISNULL(@Username , N'NULL') + N''',''' + ISNULL(@ProductID , N'NULL') + N''',''' + ISNULL(@ProductLineID , N'NULL') + N''',''' + ISNULL(@FactoryID , N'NULL') + N''',' + ISNULL(CAST(@IsIncrementalValuesFlag AS NVARCHAR(255)) , N'NULL') + N',''' + ISNULL(REPLACE(@ValuesInBracketsCommaSeparated, N'''', N'''''') , N'NULL') + N''''; -- WHY the replace - as there are double inverted comma's ? DECLARE @EffectedRows INT = 0; DECLARE @ResultCode INT = 501; DECLARE @TimestampCall DATETIME = GETUTCDATE(); DECLARE @Comment NVARCHAR(2000) = N''; -- SET Debug Flag for debugging and disable Transcation DECLARE @DebugFlag INT = 0 /* --Use for debugging SELECT * FROM ##Debug_ValuesInBracketsReceived SELECT * FROM ##Debug_ValueMergeTableFilled ORDER BY ValueSeriesID,TimeID SELECT * FROM ##Debug_ValueMergeTableBeforeTransmissions ORDER BY ValueSeriesID,TimeID */ -- STEP 0.1 - NULL Protection IF @Username IS NULL SET @Username = N''; IF @ProductID IS NULL SET @ProductID = N''; IF @ProductLineID IS NULL SET @ProductLineID = N''; IF @FactoryID IS NULL SET @FactoryID = N''; IF @IsIncrementalValuesFlag IS NULL SET @IsIncrementalValuesFlag = 0; IF @ValuesInBracketsCommaSeparated IS NULL SET @ValuesInBracketsCommaSeparated = N''; -- To delete Values in NOT Incremental Transmission, an empty @ValuesInBracketsCommaSeparated Parameter is sended IF @ValuesInBracketsCommaSeparated = '' AND @IsIncrementalValuesFlag = 0 BEGIN SET @ValuesInBracketsCommaSeparated = '[(''DeleteFlagID'',99999999,'''',''0'','''','''')]' --special ID with prevents from rejection from errorhandler END BEGIN TRY BEGIN TRANSACTION POST_PDT; -- STEP 0.2 - Protect input parameters SET @FactoryID = dbo.sx_pf_pProtectID (@FactoryID); SET @ProductID = dbo.sx_pf_pProtectID (@ProductID); SET @ProductLineID = dbo.sx_pf_pProtectID (@ProductLineID); IF @FactoryID = N'' OR @ProductLineID = N'' OR @ProductID = N'' BEGIN SET @ResultCode = 404; RAISERROR('Empty input parameters, one or more IDs are empty', 16, 10); END; -- STEP 1.1 - Determine transaction user SELECT @TransactUsername = dbo.sx_pf_Determine_TransactionUsername (@Username); IF @TransactUsername = N'403' BEGIN SET @ResultCode = 403; RAISERROR('Transaction user don`t exists', 16, 10); END; -- STEP 1.2 - Determine keys SELECT @FactoryKey = FactoryKey FROM planning.tdFactories WHERE FactoryID = @FactoryID; SELECT @ProductLineKey = ProductLineKey FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey AND ProductLineID = @ProductLineID; SELECT @ProductKey = ProductKey FROM planning.tdProducts WHERE ProductLineKey = @ProductLineKey AND ProductID = @ProductID; IF @FactoryKey = 0 OR @ProductLineKey = 0 OR @ProductKey = 0 BEGIN SET @ResultCode = 404; RAISERROR('One or more IDs dont exist (No Keys found)', 16, 10); END; -- STEP 2 - Check rights EXEC @ResultCode = sx_pf_pGET_ProductLineWriteRight @TransactUsername, @FactoryID, @ProductLineID; IF @ResultCode <> 200 BEGIN SET @errormessage = 'Invalid rights - no write Rights for ' + @TransactUsername + '.'; RAISERROR(@errormessage, 16, 10); END -- STEP 3.0 - Store Timeline, only TimeID, as Timeline is not changed during Posting IF OBJECT_ID('tempdb..#Timeline') IS NOT NULL DROP TABLE #Timeline; CREATE TABLE #Timeline ( TimeID NVARCHAR(10) COLLATE DATABASE_DEFAULT NOT NULL UNIQUE CLUSTERED ); INSERT INTO #Timeline SELECT TimeID FROM planning.tdTime WHERE ProductKey = @ProductKey; -- STEP 3.1 - ValueMergeTable Merge the current values of the product into the ValueMergeTable IF OBJECT_ID('tempdb..#ValueMergeTable') IS NOT NULL DROP TABLE #ValueMergeTable; CREATE TABLE #ValueMergeTable ( ValueSeriesKey BIGINT NOT NULL ,ValueSeriesID NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,TimeID NVARCHAR(10) COLLATE DATABASE_DEFAULT NOT NULL ,ValueFormula NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ,ValueInt NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,ValueText NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ,ValueComment NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ,[IsNumeric] INT NOT NULL ,IsFormula INT NOT NULL ,ProcessCode NVARCHAR(20) COLLATE DATABASE_DEFAULT NOT NULL ,DocuCode NVARCHAR(20) COLLATE DATABASE_DEFAULT NOT NULL ,ValueSeriesID_TimeID NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,AllowZero INT NOT NULL ); -- First Type "Old Values" is loaded in ValueMergeTable INSERT INTO #ValueMergeTable SELECT 0 AS ValueSeriesKey -- No Values from dValueSeries, this Informations are later added when old and sended Values are together ,ValueSeriesID ,v.TimeID ,ValueFormula ,ValueInt ,ValueText ,ValueComment ,0 AS IsNumeric ,0 AS IsFormula ,N'OLD VALUES' AS ProcessCode ,N'NONE' AS DocuCode ,ValueSeriesID + CAST(v.TimeID AS NVARCHAR(10)) AS ValueSeries_TimeID ,0 AS AllowZero FROM planning.tfValues v INNER JOIN #Timeline tl ON v.TimeID = tl.TimeID WHERE ProductKey = @ProductKey; -- Store Header, which is dValueSeries Information which will added to old and sended Values IF OBJECT_ID('tempdb..#Header') IS NOT NULL DROP TABLE #Header; CREATE TABLE #Header ( ValueSeriesKey BIGINT NOT NULL ,ValueSeriesID NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,[IsNumeric] INT NOT NULL ,IsFormula INT NOT NULL ,AllowZero INT NOT NULL ,UNIQUE CLUSTERED (ValueSeriesID, ValueSeriesKey, [IsNumeric], IsFormula) ); INSERT INTO #Header SELECT ValueSeriesKey ,ValueSeriesID ,[IsNumeric] ,IIF(ValueSource IN (N'XLS',N'XLS-Strict'), 1, 0) AS IsFormula ,AllowZero FROM planning.tdValueSeries WHERE ProductKey = @ProductKey; -- STEP 3.3 - Check for count of delimiter in Array DECLARE @OpenDelimetersCount INT = 0; DECLARE @DelimetersIndex INT = CHARINDEX(N'[(', @ValuesInBracketsCommaSeparated); WHILE @DelimetersIndex > 0 BEGIN SET @OpenDelimetersCount += 1; SET @DelimetersIndex = CHARINDEX(N'[(', @ValuesInBracketsCommaSeparated, @DelimetersIndex + 1); END; IF @OpenDelimetersCount > 32765 BEGIN SET @ResultCode = 403; RAISERROR('ValueArray to big (More then 32750 Values need special API Update.)', 16, 10); END; IF @OpenDelimetersCount = 0 BEGIN SET @ResultCode = 404; RAISERROR('No delimiters found in @ValuesInBracketsCommaSeparated', 16, 10); END; SET @DelimetersIndex = CHARINDEX(N')]', @ValuesInBracketsCommaSeparated); WHILE (@DelimetersIndex > 0) BEGIN SET @OpenDelimetersCount -= 1; SET @DelimetersIndex = CHARINDEX(N')]', @ValuesInBracketsCommaSeparated, @DelimetersIndex + 1); END; IF @OpenDelimetersCount <> 0 BEGIN SET @ResultCode = 404; RAISERROR('Invalid count of delimiter in @ValuesInBracketsCommaSeparated', 16, 10); END; -- STEP 3.4 - Splitt the Array with the new values for the Product to a temporary table ####################################################################################################################### -- cut leading/following brackets and ' from ValueSeriesID SET @ValuesInBracketsCommaSeparated = LEFT(@ValuesInBracketsCommaSeparated, LEN(@ValuesInBracketsCommaSeparated)-2); SET @ValuesInBracketsCommaSeparated = RIGHT(@ValuesInBracketsCommaSeparated, LEN(@ValuesInBracketsCommaSeparated)-2); DROP TABLE IF EXISTS #Values1; CREATE TABLE #Values1 ( txtValuesPipe NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ); DROP TABLE IF EXISTS #Values2; CREATE TABLE #Values2 ( ValueSeriesID NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,txtValuesPipe NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ); DROP TABLE IF EXISTS #Values3; CREATE TABLE #Values3 ( ValueSeriesID NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,TimeID NVARCHAR(10) COLLATE DATABASE_DEFAULT NOT NULL ,txtValuesPipe NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ); DROP TABLE IF EXISTS #Values4; CREATE TABLE #Values4 ( ValueSeriesID NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,TimeID NVARCHAR(10) COLLATE DATABASE_DEFAULT NOT NULL ,ValueFormula NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ,txtValuesPipe NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ); DROP TABLE IF EXISTS #Values5; CREATE TABLE #Values5 ( ValueSeriesID NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,TimeID NVARCHAR(10) COLLATE DATABASE_DEFAULT NOT NULL ,ValueFormula NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ,ValueInt NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,txtValuesPipe NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ); DROP TABLE IF EXISTS #Values6; CREATE TABLE #Values6 ( ValueSeriesID NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,TimeID NVARCHAR(10) COLLATE DATABASE_DEFAULT NOT NULL ,ValueFormula NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ,ValueInt NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,ValueText NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ,ValueComment NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ); DECLARE @spr NCHAR (1) = CHAR(29) -- Remove existing CHAR(29) = not visible character from string SET @ValuesInBracketsCommaSeparated = REPLACE(@ValuesInBracketsCommaSeparated,@spr,N''); --PRINT @ValuesInBracketsCommaSeparated -- Split String into Rows - all Values except the TimeID are single quoted after that an separated by @spr INSERT INTO #Values1 SELECT REPLACE(REPLACE(txtValues,N',''', @spr + ''''), N''',', '''' + @spr) FROM dbo.sx_pf_PivotStringIntoTable(@ValuesInBracketsCommaSeparated, N')],[('); -- Split string into two Columns (separate first column ValueSeriesID) INSERT INTO #Values2 SELECT LEFT(txtValuesPipe, CHARINDEX(@spr, txtValuesPipe) - 1) ,RIGHT(txtValuesPipe, LEN(txtValuesPipe) - CHARINDEX(@spr, txtValuesPipe)) FROM #Values1; -- Split last columns once more (separate TimeID) INSERT INTO #Values3 SELECT ValueSeriesID ,LEFT(txtValuesPipe, CHARINDEX(@spr, txtValuesPipe) - 1) ,RIGHT(txtValuesPipe, LEN(txtValuesPipe) - CHARINDEX(@spr, txtValuesPipe)) FROM #Values2; -- Delete Implicit sended periods (dont exit in current Product) DELETE FROM #Values3 WHERE TimeID NOT IN (SELECT CAST(TimeID AS NVARCHAR(10)) FROM #Timeline); -- Split last columns once more (separate ValueFormula) INSERT INTO #Values4 SELECT ValueSeriesID ,TimeID ,LEFT(txtValuesPipe, CHARINDEX(@spr, txtValuesPipe) - 1) ,RIGHT(txtValuesPipe, LEN(txtValuesPipe) - CHARINDEX(@spr, txtValuesPipe)) FROM #Values3; -- Split last columns once more (separate ValueInt) INSERT INTO #Values5 SELECT ValueSeriesID ,TimeID ,ValueFormula ,LEFT(txtValuesPipe, CHARINDEX(@spr, txtValuesPipe) - 1) ,RIGHT(txtValuesPipe, LEN(txtValuesPipe) - CHARINDEX(@spr, txtValuesPipe)) FROM #Values4; -- Split last columns once more (Separate ValueText and ValueComment) INSERT INTO #Values6 SELECT ValueSeriesID ,TimeID ,ValueFormula ,ValueInt ,LEFT(txtValuesPipe, CHARINDEX(@spr, txtValuesPipe) - 1) ,RIGHT(txtValuesPipe, LEN(txtValuesPipe) - CHARINDEX(@spr, txtValuesPipe)) FROM #Values5; -- Replace Quotes from all strings (all except TimeID) UPDATE #Values6 SET ValueSeriesID = RIGHT(LEFT(ValueSeriesID, (LEN(ValueSeriesID)-1)), LEN(ValueSeriesID)-2) ,ValueFormula = RIGHT(LEFT(ValueFormula, (LEN(ValueFormula)-1)), LEN(ValueFormula)-2) ,ValueInt = RIGHT(LEFT(ValueInt, (LEN(ValueInt)-1)), LEN(ValueInt)-2) ,ValueText = RIGHT(LEFT(ValueText, (LEN(ValueText)-1)), LEN(ValueText)-2) ,ValueComment = RIGHT(LEFT(ValueComment, (LEN(ValueComment)-1)), LEN(ValueComment)-2); -- Replace Linebreaks in ValueText, but keep them in ValueComment UPDATE #Values6 SET ValueText =REPLACE(REPLACE(ValueText,CHAR(13),''),CHAR(10),''); IF @DebugFlag = 1 BEGIN IF OBJECT_ID('tempdb..##Debug_Values6') IS NOT NULL DROP TABLE ##Debug_Values6; SELECT * INTO ##Debug_Values6 FROM #Values6; END; -- STEP 3.5 - UNION both temporary tables ####################################################################################################################### -- Second Typ "Sended Values" is loaded in ValueMergeTable INSERT INTO #ValueMergeTable SELECT 0 AS ValueSeriesKey ,ValueSeriesID ,TimeID ,ValueFormula ,ValueInt ,ValueText ,ValueComment ,0 AS [IsNumeric] ,0 AS IsFormula ,N'SENDED VALUES' ,N'NONE' ,ValueSeriesID+TimeID ,0 AS AllowZero FROM #Values6; -- Add the header informations to the values UPDATE VMT SET VMT.ValueSeriesKey = H.ValueSeriesKey ,VMT.[IsNumeric] = H.[IsNumeric] ,VMT.IsFormula = H.IsFormula ,VMT.AllowZero = H.AllowZero FROM #ValueMergeTable VMT INNER JOIN #Header H ON VMT.ValueSeriesID = H.ValueSeriesID; -- check for not existing ValueSeriesIDs which may have been sended IF EXISTS(SELECT TOP (1) ValueSeriesID FROM #ValueMergeTable WHERE ValueSeriesKey = 0) BEGIN SELECT TOP(1) @errorvalue = ValueSeriesID FROM #ValueMergeTable WHERE ValueSeriesKey = 0 ORDER BY TimeID; IF @errorvalue = 'DeleteFlagID' BEGIN DELETE FROM #ValueMergeTable END ELSE BEGIN SET @errormessage = 'One or more sended ValueSeriesIDs were not found, e.g. '+ @errorvalue +'.' SET @ResultCode = 403; RAISERROR(@errormessage, 16, 10); END END; -- check if more than one value is sended for a ValueSeries/TimeID combination IF EXISTS( SELECT COUNT(TimeID) FROM #ValueMergeTable WHERE ProcessCode = 'SENDED VALUES' GROUP BY ValueSeriesID_TimeID HAVING COUNT(TimeID) > 1 ) BEGIN SET @ResultCode = 403; RAISERROR('Douplicate Values found in ValueArray', 16, 10); END; -- STEP 3.6 - Prepare the ValueMergeTable ####################################################################################################################### -- FLAG OLD Values the user want to delete in Case of Numeric Series without formula -- Value 0 maybe sended as '000' UPDATE #ValueMergeTable SET ProcessCode = N'OLD VALUES TO DELETE' WHERE ProcessCode = N'OLD VALUES' AND [IsNumeric] = 1 AND IsFormula = 0 AND ValueSeriesID_TimeID IN ( SELECT ValueSeriesID_TimeID FROM #ValueMergeTable WHERE ProcessCode = N'SENDED VALUES' AND (TRY_CAST(ValueInt AS BIGINT) = 0 OR ValueInt = N'') ); -- FLAG OLD Values the user want to delete in Case of Numeric Series with formula UPDATE #ValueMergeTable SET ProcessCode = N'OLD VALUES TO DELETE' WHERE ProcessCode = N'OLD VALUES' AND [IsNumeric] = 1 AND IsFormula = 1 AND ValueSeriesID_TimeID IN ( SELECT ValueSeriesID_TimeID FROM #ValueMergeTable WHERE ProcessCode = N'SENDED VALUES' AND ValueFormula = N'' AND (TRY_CAST(ValueInt AS BIGINT) = 0 OR ValueInt = N'') ); -- FLAG OLD Values the user want to delete in Case of NON-Numeric Series without formula UPDATE #ValueMergeTable SET ProcessCode = N'OLD VALUES TO DELETE' WHERE ProcessCode = N'OLD VALUES' AND [IsNumeric] = 0 AND IsFormula = 0 AND ValueSeriesID_TimeID IN ( SELECT ValueSeriesID_TimeID FROM #ValueMergeTable WHERE ProcessCode = N'SENDED VALUES' AND ValueText = N'' ); -- FLAG OLD Values the user want to delete in Case of NON-Numeric Series with formula UPDATE #ValueMergeTable SET ProcessCode = N'OLD VALUES TO DELETE' WHERE ProcessCode = N'OLD VALUES' AND [IsNumeric] = 0 AND IsFormula = 1 AND ValueSeriesID_TimeID IN ( SELECT ValueSeriesID_TimeID FROM #ValueMergeTable WHERE ProcessCode = N'SENDED VALUES' AND ValueText = N'' AND ValueFormula = N'' ); -- Debugging Output IF @DebugFlag = 1 BEGIN IF OBJECT_ID('tempdb..##Debug_ValueMergeTableFilled') IS NOT NULL DROP TABLE ##Debug_ValueMergeTableFilled; SELECT * INTO ##Debug_ValueMergeTableFilled FROM #ValueMergeTable; END; -- STEP 3.7 - Handle VMT ####################################################################################################################### -- Documentation of changes in log table /* -- NEW is a Value which is not contained in existing Values UPDATE #ValueMergeTable SET DocuCode = N'NEW VALUE' WHERE ProcessCode = N'SENDED VALUES' AND DocuCode = N'NONE' AND ValueSeriesID + N'_' + TimeID NOT IN ( SELECT ValueSeriesID + N'_' + TimeID FROM #ValueMergeTable WHERE ProcessCode = N'OLD VALUES' ); -- CHANGED is a Value which is contained in existing Values UPDATE #ValueMergeTable SET DocuCode = N'CHANGED VALUE' WHERE ProcessCode = N'SENDED VALUES' AND DocuCode = N'NONE' AND ValueSeriesID + N'_' + TimeID IN ( SELECT ValueSeriesID + N'_' + TimeID FROM #ValueMergeTable WHERE ProcessCode = N'OLD VALUES' ); INSERT INTO sx_pf_AuditLog VALUES SELECT * FROM #ValueMergeTable WHERE ValueSeriesID + '_' + CAST(TimeID AS NVARCHAR(10)) IN ( SELECT ValueSeriesID + '_' + CAST(TimeID AS NVARCHAR(10)) FROM #ValueMergeTable WHERE ProcessCode = 'SENDED VALUES' AND DocuCode != 'NONE' ) */ -- Handle <#NV> Parameters - Replace them with the old Values (if exist !) UPDATE VMT SET VMT.ValueInt = OV.ValueInt FROM #ValueMergeTable VMT , (SELECT ValueInt, ValueSeriesID, TimeID FROM #ValueMergeTable WHERE ProcessCode IN (N'OLD VALUES', N'OLD VALUES TO DELETE')) OV WHERE VMT.ProcessCode = N'SENDED VALUES' AND VMT.ValueInt LIKE N'<#NV>' AND VMT.ValueSeriesID = OV.ValueSeriesID AND VMT.TimeID = OV.TimeID; UPDATE VMT SET VMT.ValueText = OV.ValueText FROM #ValueMergeTable VMT , (SELECT ValueText, ValueSeriesID, TimeID FROM #ValueMergeTable WHERE ProcessCode IN (N'OLD VALUES', N'OLD VALUES TO DELETE')) OV WHERE VMT.ProcessCode = N'SENDED VALUES' AND VMT.ValueText = N'<#NV>' AND VMT.ValueSeriesID = OV.ValueSeriesID AND VMT.TimeID = OV.TimeID; UPDATE VMT SET VMT.ValueFormula = OV.ValueFormula FROM #ValueMergeTable VMT , (SELECT ValueFormula, ValueSeriesID, TimeID FROM #ValueMergeTable WHERE ProcessCode IN (N'OLD VALUES', N'OLD VALUES TO DELETE')) OV WHERE VMT.ProcessCode = N'SENDED VALUES' AND VMT.ValueFormula = N'<#NV>' AND VMT.ValueSeriesID = OV.ValueSeriesID AND VMT.TimeID = OV.TimeID; UPDATE VMT SET VMT.ValueComment = OV.ValueComment FROM #ValueMergeTable VMT , (SELECT ValueComment, ValueSeriesID, TimeID FROM #ValueMergeTable WHERE ProcessCode IN (N'OLD VALUES', N'OLD VALUES TO DELETE')) OV WHERE VMT.ProcessCode = N'SENDED VALUES' AND VMT.ValueComment = N'<#NV>' AND VMT.ValueSeriesID = OV.ValueSeriesID AND VMT.TimeID = OV.TimeID; -- Handle <#NV> Parameter, where no old Values existed UPDATE #ValueMergeTable SET ValueInt = N'0' WHERE ValueInt = N'<#NV>' OR [IsNumeric] = 0; UPDATE #ValueMergeTable SET ValueText = N'' WHERE ValueText = N'<#NV>' OR [IsNumeric] = 1; UPDATE #ValueMergeTable SET ValueFormula = N'' WHERE ValueFormula = N'<#NV>' OR IsFormula = 0; UPDATE #ValueMergeTable SET ValueComment = N'' WHERE ValueComment = N'<#NV>'; -- Debugging Output IF @DebugFlag = 1 BEGIN IF OBJECT_ID('tempdb..##Debug_ValueMergeTableBeforeTransmissions') IS NOT NULL DROP TABLE ##Debug_ValueMergeTableBeforeTransmissions; SELECT * INTO ##Debug_ValueMergeTableBeforeTransmissions FROM #ValueMergeTable; END; -- STEP 3.8 - Save data IF @IsIncrementalValuesFlag <> 1 BEGIN -- CASE FULL transmission ######################################### -- Delete all Values DELETE FROM planning.tfValues WHERE ProductKey = @ProductKey; SET @EffectedRows += @@ROWCOUNT; -- DELETE Useless NullEmpty Values in ALL Values -- einfache Nullwerte DELETE FROM #ValueMergeTable WHERE [IsNumeric] = 1 AND IsFormula = 0 AND COALESCE(TRY_CAST(ValueInt AS BIGINT),0) = 0 AND (RIGHT(ValueSeriesID,2) <> '_0' AND AllowZero = 0); -- Numerische Formelzellen ohne Formel mit Wert 0 DELETE FROM #ValueMergeTable WHERE [IsNumeric] = 1 AND IsFormula = 1 AND ValueFormula = '' AND COALESCE(TRY_CAST(ValueInt AS BIGINT),0) = 0 AND (RIGHT(ValueSeriesID,2) <> '_0' AND AllowZero = 0); -- Gelöschte Werte in numerischen Zellen DELETE FROM #ValueMergeTable WHERE [IsNumeric] = 1 AND IsFormula = 0 AND ValueInt = N''; -- einfach Leertexte DELETE FROM #ValueMergeTable WHERE [IsNumeric] = 0 AND IsFormula = 0 AND ValueText = N''; -- Nichtnumerische Formelzellen ohne Formel mit Leertext DELETE FROM #ValueMergeTable WHERE [IsNumeric] = 0 AND IsFormula = 1 AND ValueFormula = '' AND ValueText = N''; -- Insert all Values INSERT INTO planning.tfValues SELECT ValueSeriesKey ,@ProductKey ,@ProductLineKey ,@FactoryKey ,@ProductID ,@ProductLineID ,@FactoryID ,ValueSeriesID ,TimeID ,ValueFormula ,ValueInt ,ValueText ,ValueComment FROM #ValueMergeTable WHERE ProcessCode IN (N'SENDED VALUES'); SET @EffectedRows += @@ROWCOUNT; END ELSE BEGIN -- CASE INCREMENTAL Transmission - it also leads to a full delete and insert !! ######################################### -- DELETE Values, which are sended to delete in OLD VALUES DELETE FROM #ValueMergeTable WHERE ProcessCode = N'OLD VALUES TO DELETE'; -- DELETE sended non-formula values DELETE FROM #ValueMergeTable WHERE ProcessCode = N'SENDED VALUES' AND [IsNumeric] = 1 AND IsFormula = 0 AND COALESCE(TRY_CAST(ValueInt AS BIGINT),0) = 0 AND (RIGHT(ValueSeriesID,2) <> '_0' AND AllowZero = 0); -- send Null Values for ValueSeries *_0; --empty values must delete 0 in _0 Series DELETE FROM #ValueMergeTable WHERE ProcessCode = N'SENDED VALUES' AND [IsNumeric] = 1 AND IsFormula = 0 AND ValueInt = '' AND (RIGHT(ValueSeriesID,2) = '_0' AND AllowZero = 0); -- delete deleted formulas DELETE FROM #ValueMergeTable WHERE ProcessCode = N'SENDED VALUES' AND [IsNumeric] = 1 AND IsFormula = 1 AND COALESCE(TRY_CAST(ValueInt AS BIGINT),0) = 0 AND ValueFormula = N'' AND (RIGHT(ValueSeriesID,2) <> '_0' AND AllowZero = 0); -- send Null Values for ValueSeries *_0; -- Delete DELETE FROM #ValueMergeTable WHERE ProcessCode = N'SENDED VALUES' AND [IsNumeric] = 0 AND IsFormula = 0 AND ValueText = N'' DELETE FROM #ValueMergeTable WHERE ProcessCode = N'SENDED VALUES' AND [IsNumeric] = 0 AND IsFormula = 1 AND ValueText = N'' AND ValueFormula = N''; DELETE FROM #ValueMergeTable WHERE ProcessCode = N'SENDED VALUES' AND [IsNumeric] = 1 AND IsFormula = 0 AND ValueInt = N''; -- DELETE Useless NullEmpty Values in OLD Values DELETE FROM #ValueMergeTable WHERE ProcessCode = N'OLD VALUES' AND [IsNumeric] = 1 AND IsFormula = 0 AND COALESCE(TRY_CAST(ValueInt AS BIGINT),0) = 0 AND (RIGHT(ValueSeriesID,2) <> '_0' AND AllowZero = 0); -- send Null Values for ValueSeries *_0 DELETE FROM #ValueMergeTable WHERE ProcessCode = N'OLD VALUES' AND [IsNumeric] = 0 AND IsFormula = 0 AND ValueText = N''; -- DELETE all Values DELETE FROM planning.tfValues WHERE ProductKey = @ProductKey; SET @EffectedRows += @@ROWCOUNT; -- Delete OLD VALUES which are new sended DELETE FROM #ValueMergeTable WHERE ProcessCode = N'OLD VALUES' AND ValueSeriesID_TimeID IN ( SELECT ValueSeriesID_TimeID FROM #ValueMergeTable WHERE ProcessCode = N'SENDED VALUES' ); -- Insert all Values INSERT INTO planning.tfValues SELECT ValueSeriesKey ,@ProductKey ,@ProductLineKey ,@FactoryKey ,@ProductID ,@ProductLineID ,@FactoryID ,ValueSeriesID ,TimeID ,ValueFormula ,CAST(ValueInt AS BIGINT) ,ValueText ,ValueComment FROM #ValueMergeTable WHERE ProcessCode IN (N'SENDED VALUES', N'OLD VALUES'); SET @EffectedRows += @@ROWCOUNT; END; --- materialize Product, if it is in ZT-PARAM IF @FactoryID = 'ZT' AND @ProductLineID LIKE 'PARAM%' BEGIN EXEC control.spParamTables @ProductID END COMMIT TRANSACTION POST_PDT; END TRY BEGIN CATCH DECLARE @Error_state INT = ERROR_STATE(); SET @Comment = ERROR_MESSAGE(); ROLLBACK TRANSACTION POST_PDT; IF @Error_state <> 10 BEGIN SET @ResultCode = 500; PRINT 'Rollback due to not executable command.'; END ELSE IF @ResultCode IS NULL OR @ResultCode/100 = 2 BEGIN SET @ResultCode = 500; END; END CATCH EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @EffectedRows, @ResultCode, @TimestampCall, @Comment; RETURN @ResultCode; END GO ALTER AUTHORIZATION ON OBJECT ::dbo.sx_pf_POST_ProductDataTableValues TO OCTService; GO -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'dbo' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'sx_pf_POST_ProductDataTableValues' -- enter procedure name ,@SX_Owner NVARCHAR(255) = N'OCT.core' -- enter owner name of the procedure from list (OCT.core, OCT.modules, Custom) ,@SX_Module NVARCHAR(255) = N'CORE' -- enter module name as free text (CORE,FIN, DEBKRED, HR, ...) ,@SX_ShipmentFlag INT = 1 -- 0 = Demo object - out of shipment process -- STANDARD OBJECTS -- 1 = shiped from saxess standard without modification -- 2 = shiped from saxess standard modified FOR customer from saxess -- 3 = shiped from saxess standard modified FOR customer from partner -- 4 = shiped from saxess standard modified FROM customer themself for own needs -- CUSTOM OBJECTS -- 10 = shiped from saxess as customer specific object -- 11 = shiped from partner as customer specific object -- 12 = shiped from customer as own specific object ,@SX_UserHint NVARCHAR(2000) = N'' -- optional, fill if Procedure shall be offerend for end user (e.g. for Pivot / Datagrid usage) -- KEEP this default constants ************************************************************************* DECLARE @name NVARCHAR(255) = N'MS_Description' ,@level0type NVARCHAR(255) = N'SCHEMA' ,@level1type NVARCHAR(255) = N'PROCEDURE' ,@level2type NVARCHAR(255) = N'PARAMETER' ,@level2name NVARCHAR(255) = N'' ,@value NVARCHAR(1000) = N''; SET @value = @SX_Owner; EXEC sys.sp_addextendedproperty N'SX_Owner' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_Module; EXEC sys.sp_addextendedproperty N'SX_Module' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_ShipmentFlag; EXEC sys.sp_addextendedproperty N'SX_ShipmentFlag' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_UserHint; EXEC sys.sp_addextendedproperty N'SX_UserHint' ,@value,@level0type,@level0name,@level1type,@level1name; -- SET documententation ************************************************************************* -- SET Procedure documentation SET @value = N'POST Operation for all values of the ProductDataTable The method can be switched between incremental POST and full POST'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; SET @level2name = N'@Username'; SET @value = N'Username'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ProductID'; SET @value = N'ProductID'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ProductLineID'; SET @value = N'ProductLineID'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@FactoryID'; SET @value = N'FactoryID'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@IsIncrementalValuesFlag'; SET @value = N'IsIncrementalValuesFlag'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ValuesInBracketsCommaSeparated'; SET @value = N'ValuesInBracketsCommaSeparated'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS planning.spEXPORT_Tab; GO /* Procedure to create an OCT Importscript for an selected Tab Saxess Software GmbH Last modified: 09/2023 for OCT 5.10 Testcall Procedure DECLARE @RC INT; EXEC @RC = planning.spEXPORT_Tab @Username = 'SQL' ,@FactoryID = 'ZT' ,@ProductLineID = '' ,@ProductID = '' ,@TabID = 'SF' PRINT @RC Testing in SSMS is only limited possible - Tabs which contains images result in very large strings which can handled only in bcp -> see planning.spExport_Setting for more informations Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'planning', 'PROCEDURE', 'spEXPORT_Tab',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'planning', 'PROCEDURE', 'spEXPORT_Tab','PARAMETER',NULL) */ CREATE PROCEDURE planning.spEXPORT_Tab @Username NVARCHAR(255) ,@FactoryID NVARCHAR(50) ,@ProductLineID NVARCHAR(50) ,@ProductID NVARCHAR(50) ,@TabID NVARCHAR(50) WITH EXECUTE AS 'dbo' -- as the Procedure must access the system schem AS BEGIN -- Standard declaration for logging DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID) -- SET PARAMETER LOG STRING -inside in single quotes (N''','''), Numbers without strings inside without quotes (N','), end list with '''' in case of string or '' in case of number on last position ,@ParameterString NVARCHAR(MAX) = N'''' + ISNULL(@Username ,N'NULL') + N''',' + ISNULL(@FactoryID ,N'NULL') + N''',' + ISNULL(@ProductLineID ,N'NULL') + N''',' + ISNULL(@ProductID ,N'NULL') + N''',' + ISNULL(@TabID ,N'NULL') + N'''' ,@EffectedRows INT = 0 ,@ResultCode INT = 501 ,@TimestampCall DATETIME = GETUTCDATE() ,@Comment NVARCHAR(2000) = N'' ,@TransactUsername NVARCHAR(255) = N'' ,@StartTime DATETIME2 = SysUTCDateTime(); -- Procedure specific declarations DECLARE @SourceClusterName NVARCHAR(255) ,@SourceClusterAPI NVARCHAR(255); -- NULL Protection for all Input parameters IF @Username IS NULL SET @Username = N''; IF @FactoryID IS NULL SET @FactoryID = N''; IF @TabID IS NULL SET @TabID = N''; -- START TRY *********************************************************************************** BEGIN TRY -- check transaction user existence SELECT @TransactUsername = dbo.sx_pf_Determine_TransactionUsername (@Username); IF @TransactUsername = N'403' BEGIN SET @ResultCode = 403; RAISERROR('Transaction user don`t exists', 16, 10); END; -- Create a temporary table to store the collection of export commands DROP TABLE IF EXISTS #tExport; CREATE TABLE #tExport ( RowKey BIGINT IDENTITY (1,1) ,MainOrderNumber INT NOT NULL ,SubOrderNumber INT NOT NULL ,Command NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ,CONSTRAINT PK_tExport PRIMARY KEY CLUSTERED (RowKey) ); -- Determine Metadata SELECT @SourceClusterName = ValueText FROM system.tSettings WHERE SettingID = 'Clustername'; -- May be empty if not defined SET @SourceClusterName = CONCAT(DB_Name(),' ',@SourceClusterName); SELECT @SourceClusterAPI = ValueText FROM system.tSettings WHERE SettingID = 'DBVersion'; -- MetaHeader INSERT INTO #tExport VALUES (0, 1, N'-- {') ,(0, 2, N'-- "Type": "Tab",') ,(0, 3, CONCAT(N'-- "SourceClusterName": "', @SourceClusterName,'",')) ,(0, 4, CONCAT(N'-- "SourceClusterAPI": "',@SourceClusterAPI,'",')) ,(0, 5, CONCAT(N'-- "SourceFactoryID": "',@FactoryID,'",')) ,(0, 5, CONCAT(N'-- "SourceProductLineID": "',@ProductLineID,'",')) ,(0, 5, CONCAT(N'-- "SourceProductID": "',@ProductID,'",')) ,(0, 6, CONCAT(N'-- "SourceTabID": "',@TabID,'"')) ,(0, 7, N'-- }') INSERT INTO #tExport VALUES (10, 1, N'--CONFIG: Adjust variable values manually to fit your needs !') ,(10, 2, N'DECLARE @Username NVARCHAR(255) = ''SQL''') ,(10, 3, N'DECLARE @FactoryID NVARCHAR(255) = ''' + @FactoryID + N'''') ,(10, 3, N'DECLARE @ProductLineID NVARCHAR(255) = ''' + @ProductLineID + N'''') ,(10, 3, N'DECLARE @ProductID NVARCHAR(255) = ''' + @ProductID + N'''') ,(10, 3, N'DECLARE @TabID NVARCHAR(255) = ''' + @TabID + N'''') ,(10, 4, N'--This Tab will be deleted during import, if it exists. You should be sure !'); -- Try_Delete existing Tab INSERT INTO #tExport SELECT 900 AS MainOrderNumber ,1000 AS SubOrderNumber ,CONCAT( 'EXEC planning.spDELETE_Tab ' ,'''SQL''' ,',@FactoryID' ,',@ProductLineID' ,',@ProductID' ,',@TabID' ,';' ) AS Command FROM planning.tTabs WHERE FactoryID = @FactoryID AND ProductLineID = @ProductLineID AND ProductID = @ProductID AND TabID = @TabID; -- Create the Tab as new Tab INSERT INTO #tExport SELECT 1000 AS MainOrderNumber ,1000 AS SubOrderNumber ,CONCAT( 'EXEC planning.spPOST_Tab ' ,'''SQL''' ,',@FactoryID' ,',@ProductLineID' ,',@ProductID' ,',@TabID' ,',' ,Orderindex ,'' ,',''' ,Tabname ,'''' ,',''' ,TabHint ,'''' ,',''' ,TabText ,'''' ,',''' ,PresentationCODE ,'''' ,',''' ,Datasource ,'''' ,',''' ,IsVisibleBOOL ,'''' ,',''''' -- LayoutJSON compatibility ,',''' ,ParameterJSON ,'''' ) AS Command FROM planning.tTabs WHERE FactoryID = @FactoryID AND ProductLineID = @ProductLineID AND ProductID = @ProductID AND TabID = @TabID; -- Create tab layouts INSERT INTO #tExport SELECT 1100 AS MainOrderNumber ,1000 AS SubOrderNumber ,CONCAT( 'EXEC planning.spPOST_TabLayout ' , '''SQL''' , ',@FactoryID' , ',@ProductLineID' , ',@TabID' , ',''', LayoutID, '''' , ',''', dbo.sx_pf_pMaskSQL(LayoutName), '''' , ',''', dbo.sx_pf_pMaskSQL(LayoutJSON), '''' , ',''', OrderIndex, '''' , ',''', LayoutOwner, '''' ) AS Command FROM planning.tTabLayouts WHERE FactoryID = @FactoryID AND ProductLineID = @ProductLineID AND TabID = @TabID; -- final GO INSERT INTO #tExport VALUES (9999, 1, N'GO'); -- Data Transaction PRINT CONCAT(N'Output of the import script',N' (started after ', DateDiff(millisecond, @StartTime, SysUTCDateTime()),N'ms)'); SELECT Command FROM #tExport ORDER BY MainOrderNumber ,SubOrderNumber SET @ResultCode = 200; PRINT CONCAT(N'Transaktion abgeschlossen',N' (after ', DateDiff(millisecond, @StartTime, SysUTCDateTime()),N'ms)') END TRY -- START CATCH *********************************************************************************** BEGIN CATCH DECLARE @Error_state INT = ERROR_STATE(); SET @Comment = ERROR_MESSAGE(); IF @Error_state <> 10 BEGIN SET @ResultCode = 500; PRINT 'Rollback due to not executable command.'; END ELSE IF @ResultCode IS NULL OR @ResultCode/100 = 2 BEGIN SET @ResultCode = 500; END; END CATCH EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @EffectedRows, @ResultCode, @TimestampCall, @Comment; RETURN @ResultCode; END GO -- GRANT Rights (only need if end user need direct database access to this object) -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'planning' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'spEXPORT_Tab' -- enter procedure name ,@SX_Owner NVARCHAR(255) = N'OCT.core' -- enter owner name of the procedure from list (OCT.core, OCT.modules, Custom) ,@SX_Module NVARCHAR(255) = N'CORE' -- enter module name as free text (CORE,FIN, DEBKRED, HR, ...) ,@SX_ShipmentFlag INT = 1 -- 0 = Demo object - out of shipment process -- STANDARD OBJECTS -- 1 = shiped from saxess standard without modification -- 2 = shiped from saxess standard modified FOR customer from saxess -- 3 = shiped from saxess standard modified FOR customer from partner -- 4 = shiped from saxess standard modified FROM customer themself for own needs -- CUSTOM OBJECTS -- 10 = shiped from saxess as customer specific object -- 11 = shiped from partner as customer specific object -- 12 = shiped from customer as own specific object ,@SX_UserHint NVARCHAR(2000) = N'' -- optional, fill if Procedure shall be offerend for end user (e.g. for Pivot / Datagrid usage) -- KEEP this default constants ************************************************************************* DECLARE @name NVARCHAR(255) = N'MS_Description' ,@level0type NVARCHAR(255) = N'SCHEMA' ,@level1type NVARCHAR(255) = N'PROCEDURE' ,@level2type NVARCHAR(255) = N'PARAMETER' ,@level2name NVARCHAR(255) = N'' ,@value NVARCHAR(1000) = N''; SET @value = @SX_Owner; EXEC sys.sp_addextendedproperty N'SX_Owner' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_Module; EXEC sys.sp_addextendedproperty N'SX_Module' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_ShipmentFlag; EXEC sys.sp_addextendedproperty N'SX_ShipmentFlag' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_UserHint; EXEC sys.sp_addextendedproperty N'SX_UserHint' ,@value,@level0type,@level0name,@level1type,@level1name; -- SET documententation ************************************************************************* -- SET Procedure documentation SET @value = N'Procedure to create an SQL Script, which imports a Tab in OCT.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- optional SET parameter documentation (only for Core / Standardmodules) SET @level2name = N'@TabID'; SET @value = N'ID of the Tab to be exported.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS planning.spPOST_TabLayout; GO /* Procedure to POST a layout for a tab Saxess Software GmbH Last modified: 09/2023 for OCT 5.10 Testcall DECLARE @RC INT; DECLARE @OutputID NVARCHAR(255); EXEC @RC = planning.spPOST_TabLayout @Username = 'SQL' ,@FactoryID = 'ZT' ,@ProductLineID = '' ,@TabID = 'SF' ,@LayoutID = '*' ,@LayoutName = 'Test Layout nummer 2' ,@LayoutJSON = '{}' ,@OrderIndex = 2 ,@LayoutOwner = '' ,@OutputID = @OutputID OUTPUT; SELECT @RC; SELECT @OutputID; SELECT * FROM planning.tTabLayouts ORDER BY RowKey DESC; Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'planning', 'PROCEDURE', 'spPOST_TabLayout',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'planning', 'PROCEDURE', 'spPOST_TabLayout','PARAMETER',NULL) */ CREATE PROCEDURE planning.spPOST_TabLayout @Username NVARCHAR(255) , @FactoryID NVARCHAR(255) = '' , @ProductLineID NVARCHAR(255) = '' , @TabID NVARCHAR(255) , @LayoutID NVARCHAR(255) , @LayoutName NVARCHAR(255) , @LayoutJSON NVARCHAR(MAX) , @OrderIndex INT , @LayoutOwner NVARCHAR(255) , @OutputID NVARCHAR(255) = NULL OUTPUT AS BEGIN BEGIN TRY BEGIN TRANSACTION planning_spPOST_TabLayout; -- Logging DECLARE @TimestampCall DATETIME = GETUTCDATE(); DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID); DECLARE @AffectedRows INT = 0; DECLARE @ResultCode INT = 501; DECLARE @Comment NVARCHAR(4000) = N''; DECLARE @ParameterString NVARCHAR(MAX) = N''; DECLARE @TransactUsername NVARCHAR(255) = N''; EXEC system.spGET_ParameterString @ParameterString OUTPUT, 9, @Username, @FactoryID, @ProductLineID, @TabID, @LayoutID, @LayoutName, @LayoutJSON, @OrderIndex, @LayoutOwner; EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username; -- Parameter protection SET @Username = COALESCE(@Username, N''); SET @FactoryID = dbo.sx_pf_pProtectID(COALESCE(@FactoryID, N'')); SET @ProductLineID = dbo.sx_pf_pProtectID(COALESCE(@ProductLineID, N'')); SET @TabID = dbo.sx_pf_pProtectID(COALESCE(@TabID, N'')); SET @LayoutID = dbo.sx_pf_pProtectID(COALESCE(@LayoutID, N'')); SET @LayoutName = COALESCE(@LayoutName, N''); SET @LayoutJSON = COALESCE(@LayoutJSON, N''); SET @OrderIndex = COALESCE(@OrderIndex, 999); SET @LayoutOwner = COALESCE(@LayoutOwner, N''); -- Variables DECLARE @SettingUserEditPublicLayouts BIT; DECLARE @SettingPrivateLayouts BIT; DECLARE @SettingMultipleLayouts BIT; DECLARE @IsUserAdministrator BIT; DECLARE @IsPublicLayout BIT = CASE WHEN @LayoutOwner = N'' THEN 1 ELSE 0 END; -- Get setting: regular users can edit public layouts? SELECT @SettingUserEditPublicLayouts = COALESCE(ValueInt, 0) FROM system.fGET_Setting('TabPreferencesUserEditPublicLayouts'); -- Get setting: are private layouts allowed in general? SELECT @SettingPrivateLayouts = COALESCE(ValueInt, 1) FROM system.fGET_Setting('TabPreferencesPrivateLayouts'); -- Get setting: are multiple layouts allowed in general? SELECT @SettingMultipleLayouts = COALESCE(ValueInt, 1) FROM system.fGET_Setting('TabPreferencesMultipleLayouts'); -- Is user administrator? SELECT @IsUserAdministrator = IsAdministratorFlag FROM system.trUser WHERE Username = @Username; -- Right rules: -- Administrators are allowed to create and edit all layouts -- Regular users can only create/edit private layouts -- Regular users can create/edit public layouts if the setting "UserEditPublicLayouts" = 1 but they need write rights on the location of the tab -- Regular users can only create/edit private layouts IF @IsUserAdministrator = 0 AND @SettingUserEditPublicLayouts = 0 AND @IsPublicLayout = 1 BEGIN RAISERROR('Insufficient rights to save this layout.', 16, 10); END -- Are private layouts allowed? IF @IsPublicLayout = 0 AND @SettingPrivateLayouts = 0 BEGIN RAISERROR('Private layouts are not allowed.', 16, 10); END -- Regular users can create/edit public layouts but need to have write rights IF @IsUserAdministrator = 0 AND @SettingUserEditPublicLayouts = 1 AND @IsPublicLayout = 1 BEGIN -- Cluster level IF @FactoryID = '' BEGIN EXEC @ResultCode = sx_pf_pGET_ClusterWriteRight @TransactUsername; IF @ResultCode <> 200 RAISERROR('Insufficient rights to save this layout.', 16, 10); END ELSE BEGIN -- Factory level IF @ProductLineID = '' BEGIN EXEC @ResultCode = sx_pf_pGET_FactoryWriteRight @TransactUsername, @FactoryID; IF @ResultCode <> 200 RAISERROR('Insufficient rights to save this layout.', 16, 10); END ELSE BEGIN -- ProductLine level EXEC @ResultCode = sx_pf_pGET_ProductLineWriteRight @TransactUsername, @FactoryID, @ProductLineID; IF @ResultCode <> 200 RAISERROR('Insufficient rights to save this layout.', 16, 10); END END END -- New layout? IF @LayoutID = '*' OR NOT EXISTS(SELECT 1 FROM planning.tTabLayouts WHERE FactoryID = @FactoryID AND ProductLineID = @ProductLineID AND TabID = @TabID AND LayoutID = @LayoutID) BEGIN IF NOT EXISTS(SELECT 1 FROM planning.tTabs WHERE FactoryID = @FactoryID AND ProductLineID = @ProductLineID AND TabID = @TabID) RAISERROR('Tab doesn''t exist.', 16, 10); -- Get next free LayoutID SELECT @LayoutID = COALESCE(MAX(CAST(LayoutID AS INT)), 0) + 1 FROM planning.tTabLayouts WHERE FactoryID = @FactoryID AND ProductLineID = @ProductLineID AND TabID = @TabID; -- Are multiple layouts allowed? IF @LayoutID > 1 AND @SettingMultipleLayouts = 0 BEGIN RAISERROR('Can''t create another layout because multiple layouts are not allowed.', 16, 10); END -- Insert new layout INSERT INTO planning.tTabLayouts (FactoryID, ProductLineID, TabID, LayoutID, LayoutName, LayoutJSON, OrderIndex, LayoutOwner) VALUES (@FactoryID, @ProductLineID, @TabID, @LayoutID, @LayoutName, @LayoutJSON, @OrderIndex, @LayoutOwner); END ELSE BEGIN -- Update existing layout UPDATE planning.tTabLayouts SET LayoutName = @LayoutName , LayoutJSON = @LayoutJSON , OrderIndex = @OrderIndex , LayoutOwner = @LayoutOwner WHERE FactoryID = @FactoryID AND ProductLineID = @ProductLineID AND TabID = @TabID AND LayoutID = @LayoutID; END SET @AffectedRows = @@ROWCOUNT; SET @OutputID = @LayoutID; -- Rearrange OrderIndex WITH Layouts AS ( SELECT * , ROW_NUMBER() OVER (PARTITION BY LayoutOwner ORDER BY OrderIndex) AS NewOrderIndex FROM planning.tTabLayouts WHERE FactoryID = @FactoryID AND ProductLineID = @ProductLineID AND TabID = @TabID ) UPDATE Layouts SET OrderIndex = NewOrderIndex; COMMIT TRANSACTION planning_spPOST_TabLayout; SET @ResultCode = 200 END TRY BEGIN CATCH ROLLBACK TRANSACTION planning_spPOST_TabLayout; SET @ResultCode = 500; SET @Comment = ERROR_MESSAGE(); END CATCH; EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @AffectedRows, @ResultCode, @TimestampCall, @Comment; IF @ResultCode >= 500 RAISERROR(@Comment, 16, 10); RETURN @ResultCode; END; GO -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'planning' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'spPOST_TabLayout' -- enter procedure name ,@SX_Owner NVARCHAR(255) = N'OCT.core' -- enter owner name of the procedure from list (OCT.core, OCT.modules, Custom) ,@SX_Module NVARCHAR(255) = N'CORE' -- enter module name as free text (CORE,FIN, DEBKRED, HR, ...) ,@SX_ShipmentFlag INT = 1 -- 0 = Demo object - out of shipment process -- STANDARD OBJECTS -- 1 = shiped from saxess standard without modification -- 2 = shiped from saxess standard modified FOR customer from saxess -- 3 = shiped from saxess standard modified FOR customer from partner -- 4 = shiped from saxess standard modified FROM customer themself for own needs -- CUSTOM OBJECTS -- 10 = shiped from saxess as customer specific object -- 11 = shiped from partner as customer specific object -- 12 = shiped from customer as own specific object ,@SX_UserHint NVARCHAR(2000) = N'' -- optional, fill if Procedure shall be offerend for end user (e.g. for Pivot / Datagrid usage) -- KEEP this default constants ************************************************************************* DECLARE @name NVARCHAR(255) = N'MS_Description' ,@level0type NVARCHAR(255) = N'SCHEMA' ,@level1type NVARCHAR(255) = N'PROCEDURE' ,@level2type NVARCHAR(255) = N'PARAMETER' ,@level2name NVARCHAR(255) = N'' ,@value NVARCHAR(1000) = N''; SET @value = @SX_Owner; EXEC sys.sp_addextendedproperty N'SX_Owner' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_Module; EXEC sys.sp_addextendedproperty N'SX_Module' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_ShipmentFlag; EXEC sys.sp_addextendedproperty N'SX_ShipmentFlag' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_UserHint; EXEC sys.sp_addextendedproperty N'SX_UserHint' ,@value,@level0type,@level0name,@level1type,@level1name; -- SET documententation ************************************************************************* -- SET Procedure documentation SET @value = N'Procedure for POST Bulkload'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- SET parameter documentation SET @level2name = N'@Username'; SET @value = N'Username'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@FactoryID'; SET @value = N'optional FactoryID, skip / NULL / empty string if request is for Cluster level'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ProductLineID'; SET @value = N'optional ProductLineID, skip / NULL / empty string if request is for Cluster or Factory level'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@TabID'; SET @value = N'TabID which is requested'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@LayoutID'; SET @value = N'ID of the layout which is updated. If LayoutID is * then a new layout is created.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@LayoutName'; SET @value = N'The name of the layout.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@LayoutJSON'; SET @value = N'The JSON definition of the layout.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@OrderIndex'; SET @value = N'Index to help with ordering the layouts. Needs to have unique values for all combinations of FactoryID, ProductLineID, TabID & LayoutOwner.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@LayoutOwner'; SET @value = N'Username if it is a private layout. Empty if it is a public layout.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS system.spGET_ConnectorTree; GO /* Toni August for Saxess Software GmbH Last modified: 12/2022 for OCT 5.9 Testcall Procedure DECLARE @RC INT; EXEC @RC = system.spGET_ConnectorTree @Username = 'SQL' PRINT @RC Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spGET_ConnectorTree',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spGET_ConnectorTree','PARAMETER',NULL) */ CREATE PROCEDURE system.spGET_ConnectorTree ( @Username NVARCHAR(255) ) AS BEGIN -- Standard declaration for logging DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID) -- SET PARAMETER LOG STRING -inside in single quotes (N''','''), Numbers without strings inside without quotes (N','), end list with '''' in case of string or '' in case of number on last position ,@ParameterString NVARCHAR(MAX) = N'''' + ISNULL(@Username ,N'NULL') + N'''' ,@EffectedRows INT = 0 ,@ResultCode INT = 501 ,@TimestampCall DATETIME = GETUTCDATE() ,@Comment NVARCHAR(2000) = N'' ,@TransactUsername NVARCHAR(255) = N'' ,@StartTime DATETIME2 = SysUTCDateTime(); -- NULL Protection for all Input parameters IF @Username IS NULL SET @Username = N''; BEGIN TRY -- check transaction user existence SELECT @TransactUsername = dbo.sx_pf_Determine_TransactionUsername (@Username); IF @TransactUsername = N'403' BEGIN SET @ResultCode = 403; RAISERROR('Transaction user doesn''t exist', 16, 10); END; -- Temp table to store all integration tables of all modules DROP TABLE IF EXISTS #ModuleTables; SELECT SUBSTRING(t.name, 2, CHARINDEX('_', t.name) - 2) COLLATE DATABASE_DEFAULT AS ModuleID, SUBSTRING(t.name, CHARINDEX('_', t.name) + 1, 255) COLLATE DATABASE_DEFAULT AS IntegrationTable INTO #ModuleTables FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE s.name = 'integration' AND t.name LIKE 't%[_]%'; -- Add GetCompanies to all modules as default INSERT INTO #ModuleTables SELECT DISTINCT ModuleID, 'GetCompanies' FROM #ModuleTables; -- Data output SELECT SourceSystemID, SourceSystemType, ModuleID, ConnectorDescription, ConnectorShortDescription, ConnectorVersion, QueryName, Manufacturer FROM ( SELECT c.SourceSystemID, c.SourceSystemType, c.ModuleID, c.ConnectorDescription, c.ConnectorShortDescription, c.ConnectorVersion, mt.IntegrationTable AS QueryName, CASE WHEN mt.IntegrationTable = 'GetCompanies' THEN 0 ELSE 1 END IsGetCompanies, c.Manufacturer FROM system.tConnectors AS c CROSS APPLY #ModuleTables AS mt LEFT JOIN system.tConnectorQueries AS ct ON ct.ConnectorKey = c.ConnectorKey AND ct.QueryName = mt.IntegrationTable WHERE c.ModuleID = mt.ModuleID UNION SELECT c.SourceSystemID, c.SourceSystemType, c.ModuleID, c.ConnectorDescription, c.ConnectorShortDescription, c.ConnectorVersion, ct.QueryName AS QueryName, CASE WHEN ct.QueryName = 'GetCompanies' THEN 0 ELSE 1 END IsGetCompanies, c.Manufacturer FROM system.tConnectors AS c LEFT JOIN system.tConnectorQueries AS ct ON ct.ConnectorKey = c.ConnectorKey WHERE ct.ConnectorKey IS NOT NULL ) Queries ORDER BY SourceSystemID, ModuleID, SourceSystemType, IsGetCompanies, QueryName; SET @EffectedRows = @@ROWCOUNT; SET @ResultCode = 200; END TRY -- START CATCH *********************************************************************************** BEGIN CATCH DECLARE @Error_state INT = ERROR_STATE(); SET @Comment = ERROR_MESSAGE(); IF @Error_state <> 10 BEGIN SET @ResultCode = 500; PRINT 'Rollback due to not executable command.'; END ELSE IF @ResultCode IS NULL OR @ResultCode/100 = 2 BEGIN SET @ResultCode = 500; END; END CATCH EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @EffectedRows, @ResultCode, @TimestampCall, @Comment; RETURN @ResultCode; END GO -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'system' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'spGET_ConnectorTree' -- enter procedure name ,@SX_Owner NVARCHAR(255) = N'OCT.core' -- enter owner name of the procedure from list (OCT.core, OCT.modules, Custom) ,@SX_Module NVARCHAR(255) = N'CORE' -- enter module name as free text (CORE,FIN, DEBKRED, HR, ...) ,@SX_ShipmentFlag INT = 1 -- 0 = Demo object - out of shipment process -- STANDARD OBJECTS -- 1 = shiped from saxess standard without modification -- 2 = shiped from saxess standard modified FOR customer from saxess -- 3 = shiped from saxess standard modified FOR customer from partner -- 4 = shiped from saxess standard modified FROM customer themself for own needs -- CUSTOM OBJECTS -- 10 = shiped from saxess as customer specific object -- 11 = shiped from partner as customer specific object -- 12 = shiped from customer as own specific object ,@SX_UserHint NVARCHAR(2000) = N'' -- optional, fill if Procedure shall be offerend for end user (e.g. for Pivot / Datagrid usage) -- KEEP this default constants ************************************************************************* DECLARE @name NVARCHAR(255) = N'MS_Description' ,@level0type NVARCHAR(255) = N'SCHEMA' ,@level1type NVARCHAR(255) = N'PROCEDURE' ,@level2type NVARCHAR(255) = N'PARAMETER' ,@level2name NVARCHAR(255) = N'' ,@value NVARCHAR(1000) = N''; SET @value = @SX_Owner; EXEC sys.sp_addextendedproperty N'SX_Owner' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_Module; EXEC sys.sp_addextendedproperty N'SX_Module' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_ShipmentFlag; EXEC sys.sp_addextendedproperty N'SX_ShipmentFlag' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_UserHint; EXEC sys.sp_addextendedproperty N'SX_UserHint' ,@value,@level0type,@level0name,@level1type,@level1name; -- SET documententation ************************************************************************* -- SET Procedure documentation SET @value = N'Procedure to get one or all Connectors.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; GO DROP PROCEDURE IF EXISTS system.spPOST_Content; GO /* Procedure to POST content information Saxess Software GmbH Last modified: 10/2023 for OCT 5.10 Testcall DECLARE @RC INT; EXEC @RC = system.spPOST_Content @Username = 'SQL', @ModuleID = 'FIN', @ContentID = 'result.tFIN_Konten', @ContentType = 'module', @TechnicalType = 'table', @Description = 'Result Tabelle für Konten', @OrderIndex = 1, @ContentSQL = 'CREATE TABLE result.tFIN_Konten...', @ContentUninstallSQL = 'DROP TABLE IF EXISTS result.tFIN_Konten', @VersionComment = 'Initial', @IsInstalled = 0, @RenameContentID = NULL, @CopyModuleID = NULL, @CopyContentID = NULL SELECT @RC; Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'agent', 'PROCEDURE', 'pPostBulkload',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'agent', 'PROCEDURE', 'pPostBulkload','PARAMETER',NULL) */ CREATE PROCEDURE system.spPOST_Content @Username NVARCHAR(255) , @ModuleID NVARCHAR(255) , @ContentID NVARCHAR(255) , @ContentType NVARCHAR(50) = NULL , @TechnicalType NVARCHAR(50) = NULL , @Description NVARCHAR(4000) = NULL , @OrderIndex INT = NULL , @ContentSQL NVARCHAR(MAX) = NULL , @ContentUninstallSQL NVARCHAR(MAX) = NULL , @VersionComment NVARCHAR(255) = NULL , @IsInstalled BIT = NULL AS BEGIN BEGIN TRY -- Logging DECLARE @TimestampCall DATETIME = GETUTCDATE(); DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID); DECLARE @AffectedRows INT = 0; DECLARE @ResultCode INT = 501; DECLARE @Comment NVARCHAR(4000) = N''; DECLARE @ParameterString NVARCHAR(MAX) = N''; DECLARE @TransactUsername NVARCHAR(255) = N''; EXEC system.spGET_ParameterString @ParameterString OUTPUT, 11, @Username, @ModuleID, @ContentID, @ContentType, @TechnicalType, @Description, @OrderIndex, @ContentSQL, @ContentUninstallSQL, @VersionComment, @IsInstalled; EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username; -- Variables DECLARE @ContentKey INT; DECLARE @CurrentOrderIndex INT; DECLARE @CurrentContentVersion INT; DECLARE @CurrentContentSQL NVARCHAR(MAX); DECLARE @CurrentContentUninstallSQL NVARCHAR(MAX); -- Input parameter handling SET @ModuleID = COALESCE(dbo.sx_pf_pProtectID(@ModuleID), N''); SET @ContentID = COALESCE(@ContentID, N''); IF COALESCE(@ModuleID, N'') = N'' RAISERROR('No ModuleID provided', 11, 1); IF COALESCE(@ContentID, N'') = N'' RAISERROR('No ContentID provided', 11, 1); -- Update OrderIndex IF @OrderIndex IS NOT NULL BEGIN SELECT @CurrentOrderIndex = OrderIndex FROM system.tContents WHERE ModuleID = @ModuleID AND ContentID = @ContentID; IF @CurrentOrderIndex > @OrderIndex OR @CurrentOrderIndex IS NULL BEGIN UPDATE system.tContents SET OrderIndex = OrderIndex + 1 WHERE ModuleID = @ModuleID AND OrderIndex >= @OrderIndex; END IF @CurrentOrderIndex < @OrderIndex BEGIN UPDATE system.tContents SET OrderIndex = OrderIndex - 1 WHERE ModuleID = @ModuleID AND OrderIndex <= @OrderIndex; END SET @AffectedRows += @@ROWCOUNT; END -- Update master data UPDATE system.tContents SET ContentType = COALESCE(@ContentType, ContentType) , TechnicalType = COALESCE(@TechnicalType, TechnicalType) , Description = COALESCE(@Description, Description) , OrderIndex = COALESCE(@OrderIndex, OrderIndex) WHERE ModuleID = @ModuleID AND ContentID = @ContentID; SET @AffectedRows += @@ROWCOUNT; -- Insert new content element IF @AffectedRows = 0 BEGIN INSERT INTO system.tContents ( ModuleKey , ModuleID , ContentID , ContentType , TechnicalType , Description , OrderIndex ) VALUES ( (SELECT ModuleKey FROM system.tModules WHERE ModuleID = @ModuleID) , @ModuleID , @ContentID , COALESCE(@ContentType, N'module') , COALESCE(@TechnicalType, N'query') , COALESCE(@Description, N'') , (SELECT COALESCE(@OrderIndex, MAX(OrderIndex) + 1, 1) FROM system.tContents WHERE ModuleID = @ModuleID) ); SET @ContentKey = @@IDENTITY; SET @AffectedRows += @@ROWCOUNT; -- Create content version if any value is provided IF @ContentSQL IS NOT NULL BEGIN INSERT INTO system.tContentVersions ( ContentKey , ModuleID , ContentID , Version , ContentSQL , ContentUninstallSQL , CreatedBy , CreatedDate , VersionComment , IsInstalled ) VALUES ( @ContentKey , @ModuleID , @ContentID , 1 , COALESCE(@ContentSQL, '') , COALESCE(@ContentUninstallSQL, '') , @TransactUsername , GETUTCDATE() , COALESCE(@VersionComment, 'Initial') , 0 ); SET @AffectedRows += @@ROWCOUNT; END END ELSE BEGIN SELECT @ContentKey = ContentKey FROM system.tContents WHERE ModuleID = @ModuleID AND ContentID = @ContentID; -- Get current content information SELECT TOP 1 @CurrentContentVersion = Version , @CurrentContentSQL = ContentSQL , @CurrentContentUninstallSQL = ContentUninstallSQL FROM system.tContentVersions WHERE ModuleID = @ModuleID AND ContentID = @ContentID ORDER BY Version DESC; -- Create new content version if value or uninstallvalue has changed IF (COALESCE(@ContentSQL, '') <> COALESCE(@CurrentContentSQL, '') OR COALESCE(@ContentUninstallSQL, '') <> COALESCE(@CurrentContentUninstallSQL, '')) AND @ContentSQL IS NOT NULL BEGIN INSERT INTO system.tContentVersions ( ContentKey , ModuleID , ContentID , Version , ContentSQL , ContentUninstallSQL , CreatedBy , CreatedDate , VersionComment , IsInstalled ) VALUES ( @ContentKey , @ModuleID , @ContentID , COALESCE(@CurrentContentVersion, 0) + 1 , COALESCE(@ContentSQL, '') , COALESCE(@ContentUninstallSQL, '') , @TransactUsername , GETUTCDATE() , COALESCE(@VersionComment, 'Initial') , 0 ); SET @AffectedRows += @@ROWCOUNT; END END; -- Fill gaps in OrderIndex WITH Contents AS ( SELECT * , ROW_NUMBER() OVER (PARTITION BY ModuleKey ORDER BY OrderIndex) AS NewOrderIndex FROM system.tContents ) UPDATE Contents SET OrderIndex = NewOrderIndex; -- Set content installed status IF @IsInstalled IS NOT NULL BEGIN UPDATE system.tContentVersions SET IsInstalled = 0 WHERE ContentID = @ContentID; IF @IsInstalled = 1 BEGIN UPDATE system.tContentVersions SET IsInstalled = 1 WHERE ModuleID = @ModuleID AND ContentID = @ContentID AND Version = ( SELECT MAX(Version) FROM system.tContentVersions WHERE ModuleID = @ModuleID AND ContentID = @ContentID ); END END SET @ResultCode = 200 END TRY BEGIN CATCH SET @ResultCode = 500; SET @Comment = ERROR_MESSAGE(); END CATCH; EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @AffectedRows, @ResultCode, @TimestampCall, @Comment; IF @ResultCode >= 500 RAISERROR(@Comment, 16, 10); RETURN @ResultCode; END; GO -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'system' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'spPOST_Content' -- enter procedure name ,@SX_Owner NVARCHAR(255) = N'OCT.core' -- enter owner name of the procedure from list (OCT.core, OCT.modules, Custom) ,@SX_Module NVARCHAR(255) = N'CORE' -- enter module name as free text (CORE,FIN, DEBKRED, HR, ...) ,@SX_ShipmentFlag INT = 1 -- 0 = Demo object - out of shipment process -- STANDARD OBJECTS -- 1 = shiped from saxess standard without modification -- 2 = shiped from saxess standard modified FOR customer from saxess -- 3 = shiped from saxess standard modified FOR customer from partner -- 4 = shiped from saxess standard modified FROM customer themself for own needs -- CUSTOM OBJECTS -- 10 = shiped from saxess as customer specific object -- 11 = shiped from partner as customer specific object -- 12 = shiped from customer as own specific object ,@SX_UserHint NVARCHAR(2000) = N'' -- optional, fill if Procedure shall be offerend for end user (e.g. for Pivot / Datagrid usage) -- KEEP this default constants ************************************************************************* DECLARE @name NVARCHAR(255) = N'MS_Description' ,@level0type NVARCHAR(255) = N'SCHEMA' ,@level1type NVARCHAR(255) = N'PROCEDURE' ,@level2type NVARCHAR(255) = N'PARAMETER' ,@level2name NVARCHAR(255) = N'' ,@value NVARCHAR(1000) = N''; SET @value = @SX_Owner; EXEC sys.sp_addextendedproperty N'SX_Owner' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_Module; EXEC sys.sp_addextendedproperty N'SX_Module' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_ShipmentFlag; EXEC sys.sp_addextendedproperty N'SX_ShipmentFlag' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_UserHint; EXEC sys.sp_addextendedproperty N'SX_UserHint' ,@value,@level0type,@level0name,@level1type,@level1name; -- SET documententation ************************************************************************* -- SET Procedure documentation SET @value = N'Procedure to GET the module tree'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- SET parameter documentation SET @level2name = N'@Username'; SET @value = N'Username'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ModuleID'; SET @value = N'ID of the module that should be saved.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ContentID'; SET @value = N'ID of the content that should be saved.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ContentType'; SET @value = N'OCT related type of the content (module, pipeline, list, format, factory, productline, product, tab, tabledata).'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@TechnicalType'; SET @value = N'Technical type of the content (table, view, storedprocedure, query).'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@Description'; SET @value = N'Details about the content.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ContentSQL'; SET @value = N'The SQL query that is executed when installing the content element.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ContentUninstallSQL'; SET @value = N'The SQL query that is executed when uninstalling the content element.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@VersionComment'; SET @value = N'A comment for a newly created version of the content element.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@IsInstalled'; SET @value = N'Flag if the content element is currently installed.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO UPDATE system.tSettings SET ValueText = '5.10.20', ValueInt = 51020 WHERE SettingID = 'DBVersion'