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 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.19', ValueInt = 51019 WHERE SettingID = 'DBVersion'