/* Update-Schritte 5.10.21: 1. LastEditDate in system.tPipelineSchedules entfernen */ GO ALTER TABLE system.tPipelineSchedules DROP COLUMN LastEditDate; GO DROP PROCEDURE IF EXISTS dbo.sx_pf_EXPORT_Factory; GO /* Saxess Software GmbH Last modified: 01/2024 for OCT 5.10 EXPORT operation of a single Factory, including - its properties in gtables - its global attributes - its ProductLine - its products - it values - its dependend lists, which have to be merged in the existing lists during import - in two styles, one for import as sql script, one for Import over the GUI Import The Parameter @Output for SQL Server exists only for backwards compatibilty - its no longer used Test Call direct (for small Factories) DECLARE @RC INT; EXEC @RC = dbo.sx_pf_EXPORT_Factory @Username = N'SQL' ,@FactoryID = N'ZT'; PRINT @RC; Test Call over BCP Results must be stored in table, if column width is to big for SSMS or SQLCMD 0a. Activate Stepnumber for output in Line 624 around and execute prodecedure 0b. Its difficult to send commands with german Umlaute over cmd, thats why name the database without Umlaute 1. Execute this to store the results in the table DROP TABLE IF EXISTS dbo.tmpout_Export; CREATE TABLE dbo.tmpout_Export ( StepNumber INT, --enable Stepnumber inside for testing Command NVARCHAR(MAX) ); INSERT INTO dbo.tmpout_Export EXEC dbo.sx_pf_EXPORT_Factory N'SQL',N'ZT',1; SELECT * FROM dbo.tmpout_Export ORDER BY StepNumber; 2. Copy this table Content in a file over a .bat file with this command bcp.exe "SELECT Command FROM PlanningFactoryCars.dbo.tmpout_Export ORDER BY StepNumber" queryout import.sql -T -S localhost -n -e error.log -c -C65001 SELECT TOP 10 * FROM system.tAPI_Log; Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'PROCEDURE', 'sx_pf_EXPORT_Factory',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'PROCEDURE', 'sx_pf_EXPORT_Factory','PARAMETER',NULL) */ CREATE PROCEDURE dbo.sx_pf_EXPORT_Factory @Username NVARCHAR(255), @FactoryID NVARCHAR(255), @OutputForSQLServer INT = 1 AS BEGIN SET NOCOUNT ON; DECLARE @TransactUsername NVARCHAR(255) ,@ProcedureName NVARCHAR (255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID) ,@ParameterString NVARCHAR (MAX) = N'''' + ISNULL(@Username, N'NULL') + N''',''' + ISNULL(@FactoryID, N'NULL') + N''',''' + ISNULL(CAST(@OutputForSQLServer AS NVARCHAR(255)), N'NULL') + N'''' ,@EffectedRows INTEGER = 0 ,@ResultCode INTEGER = 501 ,@TimestampCall DATETIME = GETUTCDATE() ,@Comment NVARCHAR (2000) = N''; -- STEP 0.1 - NULL Protection IF @Username IS NULL SET @Username = N''; IF @FactoryID IS NULL SET @FactoryID = N''; IF @OutputForSQLServer IS NULL SET @OutputForSQLServer = 1; BEGIN TRY DECLARE @FactoryKey BIGINT = 0; -- STEP 0.2 - Protect input parameters SET @Username = dbo.sx_pf_pProtectString(@Username); SET @FactoryID = dbo.sx_pf_pProtectID(@FactoryID); IF @FactoryID = N'' BEGIN SET @ResultCode = 404; RAISERROR(N'Empty input parameters', 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(N'Transaction user don`t exists', 16, 10); END; -- STEP 1.2 - Determine keys SELECT @FactoryKey = FactoryKey FROM planning.tdFactories WHERE FactoryID = @FactoryID; IF @FactoryKey = 0 BEGIN SET @ResultCode = 404; RAISERROR(N'Keys don`t exists', 16, 10); END; -- STEP 2 - Check rights EXEC @ResultCode = dbo.sx_pf_pGET_FactoryReadRight @TransactUsername, @FactoryID; IF @ResultCode <> 200 BEGIN SET @ResultCode = 401; RAISERROR(N'Invalid rights', 16, 10); END; -- STEP 3.1 - Determine dependend objects -- Determine dependend Lists and store in temporary table DECLARE @DependendListIDs AS TABLE (ListID NVARCHAR(255)); ;WITH lists AS ( -- MERGE dependend Lists, used in ProductDataTable SELECT ValueListID FROM planning.tdValueSeries WHERE FactoryKey = @FactoryKey -- MERGE dependend Lists, used in Globalattributes UNION ALL SELECT GlobalAttributeSource1 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey UNION ALL SELECT GlobalAttributeSource2 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey UNION ALL SELECT GlobalAttributeSource3 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey UNION ALL SELECT GlobalAttributeSource4 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey UNION ALL SELECT GlobalAttributeSource5 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey UNION ALL SELECT GlobalAttributeSource6 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey UNION ALL SELECT GlobalAttributeSource7 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey UNION ALL SELECT GlobalAttributeSource8 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey UNION ALL SELECT GlobalAttributeSource9 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey UNION ALL SELECT GlobalAttributeSource10 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey UNION ALL SELECT GlobalAttributeSource11 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey UNION ALL SELECT GlobalAttributeSource12 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey UNION ALL SELECT GlobalAttributeSource13 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey UNION ALL SELECT GlobalAttributeSource14 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey UNION ALL SELECT GlobalAttributeSource15 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey UNION ALL SELECT GlobalAttributeSource16 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey UNION ALL SELECT GlobalAttributeSource17 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey UNION ALL SELECT GlobalAttributeSource18 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey UNION ALL SELECT GlobalAttributeSource19 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey UNION ALL SELECT GlobalAttributeSource20 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey UNION ALL SELECT GlobalAttributeSource21 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey UNION ALL SELECT GlobalAttributeSource22 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey UNION ALL SELECT GlobalAttributeSource23 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey UNION ALL SELECT GlobalAttributeSource24 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey UNION ALL SELECT GlobalAttributeSource25 FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey ) INSERT INTO @DependendListIDs (ListID) SELECT DISTINCT ValueListID FROM lists; -- Declare temporary Table for export results DECLARE @ExportTable AS TABLE ( StepNumber INT NOT NULL ,RowNumber INT NOT NULL ,Command NVARCHAR(MAX)); INSERT INTO @ExportTable VALUES (0,1, N'--CONFIG: Adjust variable names manually to fit your needs !') ,(0,1, N'DECLARE @Username NVARCHAR(255) = ''SQL''') ,(0,1, N'DECLARE @FactoryID NVARCHAR(255) = ''' + @FactoryID + N'''') ,(0,1, N'--This FactoryID will be deleted during import, if it exists. You should be sure !'); -- DELETE FactoryID if exists INSERT INTO @ExportTable VALUES (1000,1, N'EXEC dbo.sx_pf_DELETE_Factory @Username, @FactoryID;'); -- POST Factory PRINT N'Starting Export Factory'; INSERT INTO @ExportTable SELECT 1001,1, N'EXEC dbo.sx_pf_POST_Factory @Username, @FactoryID,N''' + dbo.sx_pf_pMaskSQL(NameShort) + N''',N''' + dbo.sx_pf_pMaskSQL(NameLong) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentUser) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentDev) + N''',N''' + dbo.sx_pf_pMaskSQL(ResponsiblePerson) + N''',N''' + dbo.sx_pf_pMaskSQL(ImageName) + N''';' FROM planning.tdFactories dF WHERE dF.FactoryKey = @FactoryKey -- POST ProductLines PRINT N'Starting Export Produclines'; INSERT INTO @ExportTable SELECT 1002,1, N'EXEC dbo.sx_pf_POST_ProductLine @Username,''' + ProductLineID + N''', @FactoryID,N''' + dbo.sx_pf_pMaskSQL(NameShort) + N''',N''' + dbo.sx_pf_pMaskSQL(NameLong) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentUser) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentDev) + N''',N''' + dbo.sx_pf_pMaskSQL(ResponsiblePerson) + N''',N''' + dbo.sx_pf_pMaskSQL(ImageName) + N''',N''' + dbo.sx_pf_pMaskSQL(DefaultTemplate) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource1) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias1) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource2) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias2) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource3) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias3) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource4) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias4) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource5) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias5) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource6) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias6) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource7) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias7) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource8) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias8) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource9) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias9) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource10) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias10) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource11) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias11) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource12) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias12) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource13) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias13) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource14) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias14) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource15) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias15) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource16) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias16) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource17) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias17) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource18) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias18) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource19) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias19) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource20) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias20) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource21) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias21) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource22) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias22) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource23) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias23) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource24) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias24) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource25) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias25) + N''';' FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey; -- POST Products PRINT N'Starting Export Products' INSERT INTO @ExportTable SELECT 2000,1, N'EXEC dbo.sx_pf_POST_ProductEmpty @Username,N''' + ProductID + N''',N''' + ProductLineID + N''',@FactoryID,N''' + TimeType + N''',N''' + dbo.sx_pf_pMaskSQL(NameShort) + N''',N''' + dbo.sx_pf_pMaskSQL(NameLong) + N''',N''' + dbo.sx_pf_pMaskSQL( calc.sfUnifyLinebreaks( CommentUser ) ) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentDev) + N''',N''' + dbo.sx_pf_pMaskSQL(ResponsiblePerson) + N''',N''' + dbo.sx_pf_pMaskSQL(ImageName) + N''',N''' + dbo.sx_pf_pMaskSQL([Status]) + N''',N''' + dbo.sx_pf_pMaskSQL(Template) + N''',N''' + TemplateVersion + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute1) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute2) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute3) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute4) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute5) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute6) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute7) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute8) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute9) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute10) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute11) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute12) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute13) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute14) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute15) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute16) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute17) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute18) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute19) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute20) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute21) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute22) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute23) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute24) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute25) + N''';' FROM planning.tdProducts WHERE FactoryKey = @FactoryKey; -- POST Statements --> statements are not exported -- POST all ValueSeries as NewValueSeries PRINT N'Starting Export ValueSeries'; INSERT INTO @ExportTable SELECT 3000 ,ROW_NUMBER()OVER (ORDER BY FactoryID,ProductLineID,ProductID,ValueSeriesNo) AS RowNumber ,N'EXEC dbo.sx_pf_POST_ValueSerie @Username,N''' + ProductID + N''',N''' + ProductLineID + N''',@FactoryID,N''' + ValueSeriesID + N''',N''' + CAST(ValueSeriesNo AS NVARCHAR (255)) + N''',N''' + dbo.sx_pf_pMaskSQL(NameShort) + N''',N''' + dbo.sx_pf_pMaskSQL(NameLong) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentUser) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentDev) + N''',N''' + dbo.sx_pf_pMaskSQL(ImageName) + N''',N''' + CAST([IsNumeric] AS NVARCHAR (255)) + N''',N''' + CAST(VisibilityLevel AS NVARCHAR (255)) + N''',N''' + ValueSource + N''',N''' + ValueListID + N''',N''' + ValueFormatID + N''',N''' + Unit + N''',N''' + CAST(Scale AS NVARCHAR (255)) + N''',N''' + Effect + N''',N''' + EffectParameter + N''',N''' + CAST(AllowZero AS NVARCHAR(255)) + N''';' FROM planning.tdValueSeries WHERE FactoryKey = @FactoryKey ORDER BY FactoryID ,ProductLineID ,ProductID ,ValueSeriesNo; -- POST dTime PRINT N'Starting Export Time'; ;WITH tms AS ( SELECT dP.ProductID,dP.ProductLineID ,(SELECT N'[(' + CAST(t.TimeID AS NVARCHAR(10)) + N',0,0,''''' + FormatID + ''''')],' -- with DeleteFlag, AddValue,FormatID FROM planning.tdTime t WHERE t.ProductKey = dP.ProductKey ORDER BY t.TimeID FOR XML PATH(N''), TYPE).value(N'.[1]', N'NVARCHAR(MAX)') AS vals FROM planning.tdProducts dP WHERE dP.FactoryKey = @FactoryKey ) INSERT INTO @ExportTable SELECT 4000,1, N'EXEC dbo.sx_pf_POST_Timeline @Username, N''' + ProductID + N''',''' + ProductLineID + N''', @FactoryID,1,N''' + LEFT(vals, LEN(vals) - 1) + N''';' FROM tms WHERE NOT vals IS NULL; -- POST ProductDataTableValues PRINT N'Starting Export PDT' ;WITH tms AS ( SELECT dP.ProductID,dP.ProductLineID ,(SELECT N'[(''''' + ValueSeriesID + N''''',' + CAST(TimeID AS NVARCHAR(255)) + N',''''' + ValueFormula + N''''',''''' + CAST(ValueInt AS NVARCHAR(255)) + N''''',''''' + dbo.sx_pf_pMaskSQL(ValueText) + N''''',''''' + dbo.sx_pf_pMaskSQL(calc.sfUnifyLinebreaks(ValueComment)) + N''''')],' FROM planning.tfValues t WHERE t.ProductKey = dP.ProductKey FOR XML PATH(N''), TYPE).value(N'.[1]', N'NVARCHAR(MAX)') AS vals FROM planning.tdProducts dP WHERE dP.FactoryKey = @FactoryKey ) INSERT INTO @ExportTable SELECT 5000,1, N'EXEC dbo.sx_pf_POST_ProductDataTableValues @Username,N''' + ProductID + N''',''' + ProductLineID + N''', @FactoryID,0,N''' + LEFT(vals, LEN(vals) - 1) + N''';' FROM tms WHERE NOT vals IS NULL; -- POST Factory Properties PRINT N'Starting Export FactoryPoperties'; INSERT INTO @ExportTable SELECT 6000,1, N'EXEC dbo.sx_pf_POST_FactoryProperty @Username, @FactoryID,N''' + PropertyID + N''',N''' + dbo.sx_pf_pMaskSQL(PropertyName) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentUser) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentDev) + N''',N''' + Unit + N''',N''' + dbo.sx_pf_pMaskSQL(ValueText) + N''',N''' + CAST(ValueInt AS NVARCHAR(255)) + N''',N''' + CAST(Scale AS NVARCHAR(255)) + N''',N''' + CAST(IsROSystemProperty AS NVARCHAR(255)) + N''',N''' + FormatID + N''';' FROM planning.tgFactories WHERE FactoryKey = @FactoryKey; -- POST ProductLine Properties PRINT N'Starting Export Productline Properties'; INSERT INTO @ExportTable SELECT 6001,1, N'EXEC dbo.sx_pf_POST_ProductLineProperty @Username,N''' + ProductLineID + N''',@FactoryID,N''' + PropertyID + N''',N''' + dbo.sx_pf_pMaskSQL(PropertyName) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentUser) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentDev) + N''',N''' + Unit + N''',N''' + dbo.sx_pf_pMaskSQL(ValueText) + N''',N''' + CAST(ValueInt AS NVARCHAR(255)) + N''',N''' + CAST(Scale AS NVARCHAR(255)) + N''',N''' + CAST(IsROSystemProperty AS NVARCHAR(255)) + N''',N''' + FormatID + N''';' FROM planning.tgProductLines WHERE FactoryKey = @FactoryKey; -- POST ProductLine Preferences PRINT N'Starting Export Productline Preferences'; INSERT INTO @ExportTable SELECT 6001,1, N'EXEC dbo.sx_pf_POST_Preference @Username,N''' + Username + N''',N''' + ProductLineID + N''', @FactoryID,N''' + SettingName + N''',N''' + CAST(ValueInt AS NVARCHAR(255)) + N''',N''' + dbo.sx_pf_pMaskSQL(ValueText) + N''';' FROM planning.trPreferences WHERE FactoryID = @FactoryID; -- POST Product Properties PRINT N'Starting Export Product Properties'; INSERT INTO @ExportTable SELECT 6002,1, N'EXEC dbo.sx_pf_POST_ProductProperty @Username,N''' + ProductID + N''',N''' + ProductLineID + N''',@FactoryID,N''' + PropertyID + N''',N''' + dbo.sx_pf_pMaskSQL(PropertyName) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentUser) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentDev) + N''',N''' + Unit + N''',N''' + dbo.sx_pf_pMaskSQL(ValueText) + N''',N''' + CAST(ValueInt AS NVARCHAR(255)) + N''',N''' + CAST(Scale AS NVARCHAR(255)) + N''',N''' + CAST(IsROSystemProperty AS NVARCHAR(255)) + N''',N''' + FormatID + N''';' FROM planning.tgProducts WHERE FactoryKey = @FactoryKey; -- POST Tabs PRINT N'Starting Export Tabs' INSERT INTO @ExportTable SELECT 6003 ,Row_Number() OVER (ORDER BY FactoryID, ProductLineID, ProductID, Orderindex) AS Row ,N'EXEC planning.spPOST_Tab @Username, @FactoryID, ''' + ProductLineID + N''',''' + ProductID + N''',''' + TabID + N''', ' + CAST(Orderindex AS NVARCHAR(255)) + N',N''' + dbo.sx_pf_pMaskSQL(TabName) + N''',N''' + dbo.sx_pf_pMaskSQL(TabHint) + N''',N''' + dbo.sx_pf_pMaskSQL(TabText) + N''',N''' + dbo.sx_pf_pMaskSQL(PresentationCODE) + N''',N''' + dbo.sx_pf_pMaskSQL(DataSource) + N''',N''' + CAST(IsVisibleBOOL AS NVARCHAR(255)) + N''',N''' + '' + N''',N''' -- LayoutJSON compatibility + dbo.sx_pf_pMaskSQL(ParameterJSON) + N'''; ' FROM planning.tTabs WHERE FactoryKey = @FactoryKey -- POST TabLayouts PRINT N'Starting Export TabLayouts' INSERT INTO @ExportTable SELECT 6004 ,Row_Number() OVER (ORDER BY FactoryID, ProductLineID, TabID, LayoutOwner, OrderIndex) AS Row ,N'EXEC planning.spPOST_TabLayout @Username, @FactoryID, ''' + ProductLineID + N''',''' + TabID + N''',''' + LayoutID + N''',''' + dbo.sx_pf_pMaskSQL(LayoutName) + N''',''' + dbo.sx_pf_pMaskSQL(LayoutJSON) + N''',' + CAST(Orderindex AS NVARCHAR(255)) + N',''' + LayoutOwner + N'''; ' FROM planning.tTabLayouts WHERE FactoryID = @FactoryID; -- Create the lists PRINT N'Starting Export Lists' INSERT INTO @ExportTable SELECT 7001,1, N'EXEC dbo.sx_pf_POST_List @Username,N''' + ListID + N''',N''' + dbo.sx_pf_pMaskSQL(NameShort) + N''',N''' + dbo.sx_pf_pMaskSQL(NameLong) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentUser) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentDev) + N''',N''' + Datentyp + N''',N''' + Source + N''',N''' + SourceFormula + N''',N''' + FormatID + N''';' FROM planning.thLists WHERE ListID IN (SELECT ListID COLLATE DATABASE_DEFAULT FROM @DependendListIDs); -- Create List Values PRINT N'Starting Export List Values'; INSERT INTO @ExportTable SELECT 7002,1, N'EXEC dbo.sx_pf_POST_ListValue @Username,0,N''' + ListID + N''',' + CAST(ValueInt AS NVARCHAR(255)) + N',' + CAST(Scale AS NVARCHAR(255)) + N',N''' + dbo.sx_pf_pMaskSQL(ValueText) + N''',N''' + dbo.sx_pf_pMaskSQL(ValueComment) + N''',N''' + FormatID + N''';' FROM planning.thListValues WHERE ListID IN (SELECT ListID COLLATE DATABASE_DEFAULT FROM @DependendListIDs); -- Create Formats PRINT N'Starting Export Formats'; ;WITH DependendFormats AS ( -- used in depended lists from global attributes SELECT FormatID FROM planning.thListValues WHERE ListID IN (SELECT ListID COLLATE DATABASE_DEFAULT FROM @DependendListIDs) UNION ALL -- used to format ValueSeries SELECT DISTINCT ValueFormatID FROM planning.tdValueSeries WHERE FactoryKey = @FactoryKey UNION ALL -- used to format Ranges SELECT DISTINCT FormatID FROM planning.tgProducts WHERE FactoryKey = @FactoryKey AND PropertyID LIKE N'PDTRange_%' ) INSERT INTO @ExportTable SELECT 7003,1, N'EXEC dbo.sx_pf_POST_Format @Username,N''' + FormatID + N''',N''' + BackgroundColor + N''',N''' + FontColor + N''',N''' + ValueFormat + N''',N''' + CAST(Bold AS NVARCHAR(255)) + N''',N''' + CAST(Italic AS NVARCHAR(255)) + N''',N''' + AlignmentHorizontal + N''',N''' + AlignmentVertical + N''',N''' + CAST(TextUnderlined AS NVARCHAR(255)) + N''',N''' + CAST(BorderToNext AS NVARCHAR(255)) + N''',N''' + CAST(BorderToPrevious AS NVARCHAR(255)) + N''';' FROM planning.thFormats WHERE FormatID IN (SELECT DISTINCT FormatID FROM DependendFormats); PRINT N'Finishing Script'; INSERT INTO @ExportTable VALUES (8000,1, N'GO'); -- Final Value Output ################################################################################################################### PRINT N'Starting Output'; SELECT --StepNumber, --activate only for bcp test output Command FROM @ExportTable ORDER BY StepNumber ,RowNumber; SET @EffectedRows = @@ROWCOUNT; SET @ResultCode = 200; END TRY BEGIN CATCH DECLARE @Error_state INT = ERROR_STATE(); SET @Comment = ERROR_MESSAGE(); IF @Error_state <> 10 BEGIN SET @ResultCode = 500; PRINT N'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_EXPORT_Factory TO OCTService; GO -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'dbo' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'sx_pf_EXPORT_Factory'-- 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'EXPORT operation of a single Factory, including, properties in gtables, global attributes, ProductLine, products, values, dependend lists which have to be merged in the existing lists during import in two styles. One for import as sql script, one for Import over the GUI Import'; 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'@OutputForSQLServer'; SET @value = N'OutputForSQLServer'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS dbo.sx_pf_EXPORT_ProductLine; GO /* EXPORT operation of a single productline, including - its properties in gtables - its global attributes - its products - it values - its dependend lists, which have to be merged in the existing lists during import - in two styles, one for import as sql script, one for Import over the GUI Import Saxess Software GmbH Last modified: 01/2024 for OCT 5.10 Testcall Procedure direct DECLARE @RC INT; EXEC @RC = dbo.sx_pf_EXPORT_ProductLine @Username = N'SQL' ,@FactoryID = N'ZT' ,@ProductLineID = N'1'; PRINT @RC; Testcall over BCP Results must be stored in table, if column width is to big for SSMS or SQLCMD 0a. Activate Stepnumber for output in Line 753 around and execute prodecedure 0b. Its difficult to send commands with german Umlaute over cmd, thats why name the database without Umlaute 1. Execute this to store the results in the table IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'tmpout_Export') AND type in (N'U')) DROP TABLE tmpout_Export GO CREATE TABLE tmpout_Export ( StepNumber INT, Command NVARCHAR(MAX) ) INSERT INTO tmpout_Export EXEC dbo.sx_pf_EXPORT_ProductLine 'SQL','ZT','U',1 2. Copy this table Content in a file over a .bat file with this command bcp.exe "SELECT Command FROM PlanningFactoryCars.tmpout_Export ORDER BY StepNumber" queryout import.sql -T -S localhost -n -e error.log -c -C65001 Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'PROCEDURE', 'sx_pf_EXPORT_ProductLine',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'PROCEDURE', 'sx_pf_EXPORT_ProductLine','PARAMETER',NULL) */ CREATE PROCEDURE dbo.sx_pf_EXPORT_ProductLine @Username NVARCHAR(255), @FactoryID NVARCHAR(255), @ProductLineID NVARCHAR(255), @OutputForSQLServer INT = 1 AS BEGIN SET NOCOUNT ON; DECLARE @TransactUsername AS NVARCHAR(255) ,@ProcedureName AS NVARCHAR (255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID) ,@ParameterString AS NVARCHAR (MAX) = N'''' + ISNULL(@Username , N'NULL') + N''',''' + ISNULL(@FactoryID , N'NULL') + N''',''' + ISNULL(@ProductLineID , N'NULL') + N''',''' + ISNULL(CAST(@OutputForSQLServer AS NVARCHAR(255)), N'NULL') + N'''' ,@EffectedRows AS INTEGER = 0 ,@ResultCode AS INTEGER = 501 ,@TimestampCall AS DATETIME = GETUTCDATE() ,@Comment AS NVARCHAR (2000) = 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''; BEGIN TRY DECLARE @ProductLineKey BIGINT = 0; -- Protect input parameters SET @Username = dbo.sx_pf_pProtectString(@Username); SET @FactoryID = dbo.sx_pf_pProtectID(@FactoryID); SET @ProductLineID = dbo.sx_pf_pProtectID(@ProductLineID); IF @FactoryID = N'' OR @ProductLineID = N'' BEGIN SET @ResultCode = 404; RAISERROR(N'Empty input parameters', 16, 10); END; -- Determine transaction user SELECT @TransactUsername = dbo.sx_pf_Determine_TransactionUsername (@Username); IF @TransactUsername = N'403' BEGIN SET @ResultCode = 403; RAISERROR(N'Transaction user don`t exists', 16, 10); END; -- Determine keys SELECT @ProductLineKey = ProductLineKey FROM planning.tdProductLines WHERE FactoryID = @FactoryID AND ProductLineID = @ProductLineID; IF @ProductLineKey = 0 BEGIN SET @ResultCode = 404; RAISERROR(N'Keys don`t exists', 16, 10); END; -- Check rights EXEC @ResultCode = dbo.sx_pf_pGET_ProductLineReadRight @TransactUsername, @FactoryID, @ProductLineID; IF @ResultCode <> 200 BEGIN SET @ResultCode = 401; RAISERROR(N'Invalid rights', 16, 10); END; -- Determine dependend objects -- Determine dependend Lists and store in temporary table DECLARE @DependendListIDs AS TABLE (ListID NVARCHAR(255)); ;WITH lists AS ( -- MERGE dependend Lists, used in ProductDataTable SELECT ValueListID FROM planning.tdValueSeries WHERE ProductLineKey = @ProductLineKey -- MERGE dependend Lists, used in Globalattributes UNION ALL SELECT GlobalAttributeSource1 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey UNION ALL SELECT GlobalAttributeSource2 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey UNION ALL SELECT GlobalAttributeSource3 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey UNION ALL SELECT GlobalAttributeSource4 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey UNION ALL SELECT GlobalAttributeSource5 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey UNION ALL SELECT GlobalAttributeSource6 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey UNION ALL SELECT GlobalAttributeSource7 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey UNION ALL SELECT GlobalAttributeSource8 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey UNION ALL SELECT GlobalAttributeSource9 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey UNION ALL SELECT GlobalAttributeSource10 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey UNION ALL SELECT GlobalAttributeSource11 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey UNION ALL SELECT GlobalAttributeSource12 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey UNION ALL SELECT GlobalAttributeSource13 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey UNION ALL SELECT GlobalAttributeSource14 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey UNION ALL SELECT GlobalAttributeSource15 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey UNION ALL SELECT GlobalAttributeSource16 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey UNION ALL SELECT GlobalAttributeSource17 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey UNION ALL SELECT GlobalAttributeSource18 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey UNION ALL SELECT GlobalAttributeSource19 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey UNION ALL SELECT GlobalAttributeSource20 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey UNION ALL SELECT GlobalAttributeSource21 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey UNION ALL SELECT GlobalAttributeSource22 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey UNION ALL SELECT GlobalAttributeSource23 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey UNION ALL SELECT GlobalAttributeSource24 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey UNION ALL SELECT GlobalAttributeSource25 FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey ) INSERT INTO @DependendListIDs (ListID) SELECT DISTINCT ValueListID FROM lists; -- Declare temporary Table for export results DECLARE @ExportTable AS TABLE ( StepNumber INT NOT NULL ,RowNumber INT NOT NULL ,Command NVARCHAR(MAX) ); INSERT INTO @ExportTable VALUES (0, 1, N'--CONFIG: Adjust variable names manually to fit your needs !') ,(0, 1, N'DECLARE @Username NVARCHAR(255) = ''SQL''') ,(0, 1, N'DECLARE @FactoryID NVARCHAR(255) = ''' + @FactoryID + N'''') ,(0, 1, N'DECLARE @ProductLineID NVARCHAR(255) = ''' + @ProductLineID + N'''') ,(0, 1, N'--This ProductLineID will be deleted during import, if it exists. You should be sure !'); -- DELETE ProductLine if exists INSERT INTO @ExportTable VALUES (1000,1, N'EXEC dbo.sx_pf_DELETE_ProductLine @Username, @FactoryID, @ProductLineID;'); -- POST ProductLine INSERT INTO @ExportTable SELECT 1001,1, N'EXEC dbo.sx_pf_POST_ProductLine @Username, @ProductLineID, @FactoryID,N''' + dbo.sx_pf_pMaskSQL(NameShort) + N''',N''' + dbo.sx_pf_pMaskSQL(NameLong) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentUser) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentDev) + N''',N''' + dbo.sx_pf_pMaskSQL(ResponsiblePerson) + N''',N''' + dbo.sx_pf_pMaskSQL(ImageName) + N''',N''' + dbo.sx_pf_pMaskSQL(DefaultTemplate) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource1) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias1) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource2) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias2) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource3) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias3) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource4) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias4) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource5) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias5) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource6) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias6) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource7) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias7) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource8) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias8) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource9) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias9) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource10) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias10) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource11) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias11) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource12) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias12) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource13) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias13) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource14) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias14) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource15) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias15) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource16) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias16) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource17) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias17) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource18) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias18) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource19) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias19) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource20) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias20) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource21) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias21) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource22) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias22) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource23) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias23) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource24) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias24) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeSource25) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttributeAlias25) + N''';' FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey; -- POST Products INSERT INTO @ExportTable SELECT 2000,1, N'EXEC dbo.sx_pf_POST_ProductEmpty @Username,''' + ProductID + N''', @ProductLineID, @FactoryID,N''' + TimeType + N''',N''' + dbo.sx_pf_pMaskSQL(NameShort) + N''',N''' + dbo.sx_pf_pMaskSQL(NameLong) + N''',N''' + dbo.sx_pf_pMaskSQL( calc.sfUnifyLinebreaks ( CommentUser ) ) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentDev) + N''',N''' + dbo.sx_pf_pMaskSQL(ResponsiblePerson) + N''',N''' + dbo.sx_pf_pMaskSQL(ImageName) + N''',N''' + dbo.sx_pf_pMaskSQL([Status]) + N''',N''' + dbo.sx_pf_pMaskSQL(Template) + N''',N''' + dbo.sx_pf_pMaskSQL(TemplateVersion) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute1) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute2) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute3) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute4) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute5) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute6) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute7) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute8) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute9) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute10) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute11) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute12) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute13) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute14) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute15) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute16) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute17) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute18) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute19) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute20) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute21) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute22) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute23) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute24) + N''',N''' + dbo.sx_pf_pMaskSQL(GlobalAttribute25) + N''';' FROM planning.tdProducts WHERE ProductLineKey = @ProductLineKey; -- POST Statements --> statements are not exported -- POST all ValueSeries as NewValueSeries INSERT INTO @ExportTable SELECT 3000 AS Step , ROW_NUMBER()OVER (ORDER BY ProductLineID,ProductID,ValueSeriesNo) AS RowNumber , N'EXEC dbo.sx_pf_POST_ValueSerie @Username,N''' + ProductID + N''',@ProductLineID, @FactoryID,N''' + ValueSeriesID + N''',N''' + CAST(ValueSeriesNo AS NVARCHAR (255)) + N''',N''' + dbo.sx_pf_pMaskSQL(NameShort) + N''',N''' + dbo.sx_pf_pMaskSQL(NameLong) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentUser) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentDev) + N''',N''' + dbo.sx_pf_pMaskSQL(ImageName) + N''',N''' + CAST([IsNumeric] AS NVARCHAR (255)) + N''',N''' + CAST(VisibilityLevel AS NVARCHAR (255)) + N''',N''' + ValueSource + N''',N''' + ValueListID + N''',N''' + ValueFormatID + N''',N''' + Unit + N''',N''' + CAST(Scale AS NVARCHAR (255)) + N''',N''' + Effect + N''',N''' + EffectParameter + N''',N''' + CAST(AllowZero AS NVARCHAR (255)) + N''';' FROM planning.tdValueSeries WHERE ProductLineKey = @ProductLineKey ORDER BY ProductLineID ,ProductID ,ValueSeriesNo; -- POST dTime ;WITH tms AS ( SELECT dP.ProductID ,(SELECT N'[(' + CAST(t.TimeID AS NVARCHAR(10)) + N',0,0,'''''+ FormatID + N''''')],' -- with DeleteFlag, AddValue,FormatID FROM planning.tdTime t WHERE t.ProductKey = dP.ProductKey ORDER BY t.TimeID FOR XML PATH(''), TYPE).value(N'.[1]', N'NVARCHAR(MAX)') AS vals FROM planning.tdProducts dP WHERE dP.ProductLineKey = @ProductLineKey ) INSERT INTO @ExportTable SELECT 4000,1, N'EXEC dbo.sx_pf_POST_Timeline @Username, N''' + ProductID + N''', @ProductLineID, @FactoryID,1,N''' + LEFT(vals, LEN(vals) - 1) + N''';' FROM tms WHERE NOT vals IS NULL; -- POST ProductDataTableValues - no N in Array needed ;WITH tms AS ( SELECT dP.ProductID ,(SELECT N'[(''''' + ValueSeriesID + N''''',' + CAST(TimeID AS NVARCHAR(255)) + N',''''' + ValueFormula + N''''',''''' + CAST(ValueInt AS NVARCHAR(255)) + N''''',''''' + dbo.sx_pf_pMaskSQL(ValueText) + N''''',''''' + dbo.sx_pf_pMaskSQL(calc.sfUnifyLinebreaks(ValueComment)) + N''''')],' FROM planning.tfValues t WHERE t.ProductKey = dP.ProductKey FOR XML PATH(''), TYPE).value(N'.[1]', N'NVARCHAR(MAX)') AS vals FROM planning.tdProducts dP WHERE dP.ProductLineKey = @ProductLineKey ) INSERT INTO @ExportTable SELECT 5000,1, N'EXEC dbo.sx_pf_POST_ProductDataTableValues @Username,N''' + ProductID + N''', @ProductLineID, @FactoryID,0,N''' + LEFT(vals, LEN(vals) - 1) + N''';' FROM tms WHERE NOT vals IS NULL; -- POST ProductLine Properties INSERT INTO @ExportTable SELECT 6000,1, N'EXEC dbo.sx_pf_POST_ProductLineProperty @Username, @ProductLineID, @FactoryID,N''' + PropertyID + N''',N''' + dbo.sx_pf_pMaskSQL(PropertyName) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentUser) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentDev) + N''',N''' + Unit + N''',N''' + dbo.sx_pf_pMaskSQL(ValueText) + N''',N''' + CAST(ValueInt AS NVARCHAR(255)) + N''',N''' + CAST(Scale AS NVARCHAR(255)) + N''',N''' + CAST(IsROSystemProperty AS NVARCHAR(255)) + N''',N''' + FormatID + N''';' FROM planning.tgProductLines WHERE ProductLineKey = @ProductLineKey; -- POST ProductLine Preferences INSERT INTO @ExportTable SELECT 6001,1, N'EXEC dbo.sx_pf_POST_Preference @Username,N''' + UserName + N''',@ProductLineID, @FactoryID,N''' + SettingName + N''',N''' + CAST(ValueInt AS NVARCHAR(255)) + N''',N''' + dbo.sx_pf_pMaskSQL(ValueText) + N''';' FROM planning.trPreferences WHERE FactoryID = @FactoryID AND ProductLineID = @ProductLineID; -- POST Tabs INSERT INTO @ExportTable SELECT 6003 ,Row_Number() OVER (ORDER BY ProductLineID, ProductID, Orderindex) AS Row ,N'EXEC planning.spPOST_Tab @Username, @FactoryID, @ProductLineID, N''' + ProductID + N''',''' + TabID + N''', ' + CAST(Orderindex AS NVARCHAR(255)) + N',N''' + dbo.sx_pf_pMaskSQL(TabName) + N''',N''' + dbo.sx_pf_pMaskSQL(TabHint) + N''',N''' + dbo.sx_pf_pMaskSQL(TabText) + N''',N''' + dbo.sx_pf_pMaskSQL(PresentationCODE) + N''',N''' + dbo.sx_pf_pMaskSQL(DataSource) + N''',N''' + CAST(IsVisibleBOOL AS NVARCHAR(255)) + N''',N''' + '' + N''',N''' -- LayoutJSON compatibility + dbo.sx_pf_pMaskSQL(ParameterJSON) + N'''; ' FROM planning.tTabs WHERE ProductLineKey = @ProductLineKey; -- POST TabLayouts INSERT INTO @ExportTable SELECT 6004 ,Row_Number() OVER (ORDER BY FactoryID, ProductLineID, TabID, LayoutOwner, OrderIndex) AS Row ,N'EXEC planning.spPOST_TabLayout @Username, @FactoryID, @ProductLineID, N''' + TabID + N''',''' + LayoutID + N''',''' + dbo.sx_pf_pMaskSQL(LayoutName) + N''',''' + dbo.sx_pf_pMaskSQL(LayoutJSON) + N''',' + CAST(Orderindex AS NVARCHAR(255)) + N',''' + LayoutOwner + N'''; ' FROM planning.tTabLayouts WHERE FactoryID = @FactoryID AND ProductLineID = @ProductLineID; -- POST Product Properties INSERT INTO @ExportTable SELECT 6001,1, N'EXEC dbo.sx_pf_POST_ProductProperty @Username,N''' + ProductID + N''',@ProductLineID, @FactoryID,N''' + PropertyID + N''',N''' + dbo.sx_pf_pMaskSQL(PropertyName) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentUser) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentDev) + N''',N''' + Unit + N''',N''' + dbo.sx_pf_pMaskSQL(ValueText) + N''',N''' + CAST(ValueInt AS NVARCHAR(255)) + N''',N''' + CAST(Scale AS NVARCHAR(255)) + N''',N''' + CAST(IsROSystemProperty AS NVARCHAR(255)) + N''',N''' + FormatID + N''';' FROM planning.tgProducts WHERE ProductLineKey = @ProductLineKey; -- Create the lists INSERT INTO @ExportTable SELECT 7001,1, N'EXEC dbo.sx_pf_POST_List @Username,N''' + ListID + N''',N''' + dbo.sx_pf_pMaskSQL(NameShort) + N''',N''' + dbo.sx_pf_pMaskSQL(NameLong) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentUser) + N''',N''' + dbo.sx_pf_pMaskSQL(CommentDev) + N''',N''' + Datentyp + N''',N''' + Source + N''',N''' + SourceFormula + N''',N''' + FormatID + N''';' FROM planning.thLists WHERE ListID IN (SELECT ListID COLLATE DATABASE_DEFAULT FROM @DependendListIDs); -- Create List Values INSERT INTO @ExportTable SELECT 7002,1, N'EXEC dbo.sx_pf_POST_ListValue @Username,0,N''' + ListID + N''',' + CAST(ValueInt AS NVARCHAR(255)) + N',' + CAST(Scale AS NVARCHAR(255)) + N',N''' + dbo.sx_pf_pMaskSQL(ValueText) + N''',N''' + dbo.sx_pf_pMaskSQL(ValueComment) + N''',N''' + FormatID + N''';' FROM planning.thListValues WHERE ListID IN (SELECT ListID COLLATE DATABASE_DEFAULT FROM @DependendListIDs); -- Create Formats ;WITH DependendFormats AS ( -- used in depended lists from global attributes SELECT FormatID FROM planning.thListValues WHERE ListID IN (SELECT ListID COLLATE DATABASE_DEFAULT FROM @DependendListIDs) UNION ALL -- used to format ValueSeries SELECT DISTINCT ValueFormatID FROM planning.tdValueSeries WHERE ProductLineKey = @ProductLineKey UNION ALL -- used to format Ranges SELECT DISTINCT FormatID FROM planning.tgProducts WHERE ProductLineKey = @ProductLineKey AND PropertyID LIKE N'PDTRange_%' ) INSERT INTO @ExportTable SELECT 7003,1, N'EXEC dbo.sx_pf_POST_Format @Username,N''' + FormatID + N''',N''' + BackgroundColor + N''',N''' + FontColor + N''',N''' + ValueFormat + N''',N''' + CAST(Bold AS NVARCHAR(255)) + N''',N''' + CAST(Italic AS NVARCHAR(255)) + N''',N''' + AlignmentHorizontal + N''',N''' + AlignmentVertical + N''',N''' + CAST(TextUnderlined AS NVARCHAR(255)) + N''',N''' + CAST(BorderToNext AS NVARCHAR(255)) + N''',N''' + CAST(BorderToPrevious AS NVARCHAR(255)) + N''';' FROM planning.thFormats WHERE FormatID IN (SELECT DISTINCT FormatID FROM DependendFormats); INSERT INTO @ExportTable VALUES (8000,1, N'GO'); -- Final Value Output ################################################################################################################### SELECT --StepNumber, --activate only for bcp test output Command FROM @ExportTable ORDER BY StepNumber,RowNumber; SET @EffectedRows = @@ROWCOUNT; SET @ResultCode = 200; END TRY 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 ALTER AUTHORIZATION ON OBJECT ::dbo.sx_pf_EXPORT_ProductLine TO OCTService; GO -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'dbo' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'sx_pf_EXPORT_ProductLine'-- 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'EXPORT operation of a single productline, including,properties in gtables, global attributes, products, values, dependend lists, which have to be merged in the existing lists during import in two styles: one for import as sql script, one for Import over the GUI Import'; 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'@OutputForSQLServer'; SET @value = N'OutputForSQLServer - no longer in use.'; 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_Format; GO /* Procedure to create an OCT Importscript for an format Gerd Tautenhahn for Saxess Software GmbH Last modified: 01/2023 for OCT 5.9 Testcall Procedure DECLARE @RC INT; EXEC @RC = planning.spEXPORT_Format @Username = 'SQL' ,@FormatID = 'EingabeDatum'; PRINT @RC; Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'planning', 'PROCEDURE', 'spEXPORT_Format',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'planning', 'PROCEDURE', 'spEXPORT_Format','PARAMETER',NULL) */ CREATE PROCEDURE planning.spEXPORT_Format @Username NVARCHAR(255) ,@FormatID NVARCHAR(50) WITH EXECUTE AS 'dbo' -- as the Procedure must access the system schema 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(@FormatID ,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 @FormatID IS NULL SET @FormatID = 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 ,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": "Format",') ,(0, 3, CONCAT(N'-- "SourceClusterName": "', @SourceClusterName,'",')) ,(0, 4, CONCAT(N'-- "SourceClusterAPI": "',@SourceClusterAPI,'",')) ,(0, 6, CONCAT(N'-- "SourceFormatID": "',@FormatID,'"')) ,(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 @FormatID NVARCHAR(255) = ''' + @FormatID + N'''') ,(10, 4, N'--This Format will be deleted during import, if it exists. You should be sure !'); -- Try_Delete existing Format INSERT INTO #tExport SELECT 900 AS MainOrderNumber ,1000 AS SubOrderNumber ,CONCAT( 'EXEC dbo.sx_pf_DELETE_Format ' ,'''SQL''' ,',@FormatID' ,';' ) AS Command FROM planning.thFormats WHERE FormatID = @FormatID; -- Create the Format as new Format INSERT INTO #tExport SELECT 1000 AS MainOrderNumber ,1000 AS SubOrderNumber ,CONCAT( 'EXEC dbo.sx_pf_POST_Format ' ,'''SQL''' ,',@FormatID' ,',''' ,BackgroundColor ,'''' ,',''' ,FontColor ,'''' ,',''' ,ValueFormat ,'''' ,',''' ,Bold ,'''' ,',''' ,Italic ,'''' ,',''' ,AlignmentHorizontal ,'''' ,',''' ,AlignmentVertical ,'''' ,',''' ,TextUnderlined ,'''' ,',''' ,BorderToNext ,'''' ,',''' ,BorderToPrevious ,'''' ,';' ) AS Command FROM planning.thFormats WHERE FormatID = @FormatID; -- 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 -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'planning' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'spEXPORT_Format' -- 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 Format in OCT.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- optional SET parameter documentation SET @level2name = N'@FormatID'; SET @value = N'ID of the Format to be exported.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS planning.spEXPORT_List; GO /* Procedure to create an OCT Importscript for a list with its values Gerd Tautenhahn for Saxess Software GmbH Last modified: 01/2023 for OCT 5.9 Testcall Procedure DECLARE @RC INT; EXEC @RC = planning.spEXPORT_List @Username = 'SQL' ,@ListID = 'suKontenfunktion'; PRINT @RC; Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'planning', 'PROCEDURE', 'spEXPORT_List',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'planning', 'PROCEDURE', 'spEXPORT_List','PARAMETER',NULL) */ CREATE PROCEDURE planning.spEXPORT_List @Username NVARCHAR(255) ,@ListID NVARCHAR(50) WITH EXECUTE AS 'dbo' -- as the Procedure must access the system schema 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(@ListID ,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 @ListID IS NULL SET @ListID = 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 ,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": "List",') ,(0, 3, CONCAT(N'-- "SourceClusterName": "', @SourceClusterName,'",')) ,(0, 4, CONCAT(N'-- "SourceClusterAPI": "',@SourceClusterAPI,'",')) ,(0, 6, CONCAT(N'-- "SourceListID": "',@ListID,'"')) ,(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 @ListID NVARCHAR(255) = ''' + @ListID + N'''') ,(10, 4, N'--This List will be deleted during import, if it exists. You should be sure !'); -- Try_Delete existing Pipeline INSERT INTO #tExport SELECT 900 AS MainOrderNumber ,1000 AS SubOrderNumber ,CONCAT( 'EXEC dbo.sx_pf_DELETE_List ' ,'''SQL''' ,',@ListID' ,';' ) AS Command FROM planning.thLists WHERE ListID = @ListID; -- Create the List as new List INSERT INTO #tExport SELECT 1000 AS MainOrderNumber ,1000 AS SubOrderNumber ,CONCAT( 'EXEC dbo.sx_pf_POST_List ' ,'''SQL''' ,',@ListID' ,',''' ,NameShort ,'''' ,',''' ,NameLong ,'''' ,',''' ,CommentDev ,'''' ,',''' ,CommentUser ,'''' ,',''' ,Datentyp ,'''' ,',''' ,Source ,'''' ,',''' ,SourceFormula ,'''' ,',''' ,FormatID ,'''' ,';' ) AS Command FROM planning.thLists WHERE ListID = @ListID; -- List Values INSERT INTO #tExport SELECT 1000 AS MainOrderNumber ,2000 AS SubOrderNumber ,CONCAT( 'EXEC dbo.sx_pf_POST_ListValue ' ,'''SQL''' ,',' ,0 ,'' ,',@ListID' ,',' ,ValueInt ,'' ,',' ,Scale ,'' ,',''' ,ValueText ,'''' ,',''' ,ValueComment ,'''' ,',''' ,FormatID ,'''' ,';' ) AS Command FROM planning.thListValues WHERE ListID = @ListID; -- 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_List' -- 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 List in OCT.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- optional SET parameter documentation (only for Core / Standardmodules) SET @level2name = N'@ListID'; SET @value = N'ID of the List to be exported.'; 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 ,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.spGET_TabLayouts; GO /* Procedure to POST a layout for a tab Saxess Software GmbH Last modified: 08/2024 for OCT 5.10 Testcall DECLARE @RC INT; EXEC @RC = planning.spGET_TabLayouts @Username = 'SQL' ,@FactoryID = '' ,@ProductLineID = '' ,@TabID = 'Test'; SELECT @RC; Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'planning', 'PROCEDURE', 'spGET_TabLayouts',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'planning', 'PROCEDURE', 'spGET_TabLayouts','PARAMETER',NULL) */ CREATE PROCEDURE planning.spGET_TabLayouts @Username NVARCHAR(255) , @FactoryID NVARCHAR(255) = '' , @ProductLineID NVARCHAR(255) = '' , @TabID NVARCHAR(255) , @ReturnAllLayouts BIT = 0 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, 4, @Username, @FactoryID, @ProductLineID, @TabID, @ReturnAllLayouts; EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username; -- Output SELECT LayoutID , LayoutName , LayoutJSON , OrderIndex , IIF(LayoutOwner = '', 1, 0) AS IsPublicLayout , LayoutOwner FROM planning.tTabLayouts WHERE FactoryID = @FactoryID AND ProductLineID = @ProductLineID AND TabID = @TabID AND (COALESCE(NULLIF(LayoutOwner, ''), @Username) = @Username OR @ReturnAllLayouts = 1) ORDER BY IsPublicLayout ASC , OrderIndex ASC; SET @AffectedRows = @@ROWCOUNT; 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'planning' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'spGET_TabLayouts' -- 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; GO DROP PROCEDURE IF EXISTS planning.spGET_Tree; GO /* Saxess Software GmbH Last modified: 10/2023 for OCT 5.9 Testcall Procedure DECLARE @RC INT; EXEC @RC = planning.spGET_Tree @Username = 'SQL' ,@Searchstring = '' PRINT @RC Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'planning', 'PROCEDURE', 'spGET_Tree',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'planning', 'PROCEDURE', 'spGET_Tree','PARAMETER',NULL) */ CREATE PROCEDURE planning.spGET_Tree ( @Username NVARCHAR(255) ,@Searchstring 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''',''' + ISNULL(@Searchstring ,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''; IF @Searchstring IS NULL SET @Searchstring = N''; SET @Searchstring = CONCAT('%',@Searchstring,'%'); -- START TRY *********************************************************************************** BEGIN TRY PRINT CONCAT(N'Determine Righs of this user',N' (started after ', DateDiff(millisecond, @StartTime, SysUTCDateTime()),N'ms)'); -- 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; -- get ProductLine rights for this user DROP TABLE IF EXISTS #ProductLineRights; CREATE TABLE #ProductLineRights ( RowKey BIGINT IDENTITY (1,1) ,FactoryID NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,ProductLineID NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,[Right] NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,ReadCommentMandatory INT NOT NULL ,WriteCommentMandatory INT NOT NULL ,PRIMARY KEY CLUSTERED (RowKey) ); INSERT INTO #ProductLineRights SELECT FactoryID ,ProductLineID ,[Right] ,ReadCommentMandatory ,WriteCommentMandatory FROM system.trUserRights WHERE UserName = @TransactUsername AND FactoryID <> ''; -- get Factory rights for this user (separat table for simple join later) DROP TABLE IF EXISTS #FactoryRights; CREATE TABLE #FactoryRights ( RowKey BIGINT IDENTITY (1,1) ,FactoryID NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,[Right] NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,PRIMARY KEY CLUSTERED (RowKey) ); INSERT INTO #FactoryRights SELECT FactoryID ,[Right] FROM system.trUserRights WHERE UserName = @TransactUsername AND ProductLineID = ''; -- Data Transaction PRINT CONCAT(N'Ausgabe Tree',N' (started after ', DateDiff(millisecond, @StartTime, SysUTCDateTime()),N'ms)'); SELECT dF.FactoryID AS FactoryID ,dF.NameShort AS FactoryNameShort ,dF.ImageName AS FactoryImageName ,RF.[Right] AS FactoryRight ,COALESCE(dPL.ProductLineID ,'') AS ProductLineID ,COALESCE(dPL.NameShort ,'') AS ProductLineNameShort ,COALESCE(dPL.ImageName ,'') AS ProductLineImageName ,COALESCE(RPL.[Right] ,'') AS ProductLineRight ,COALESCE(dP.ProductID ,'') AS ProductID ,COALESCE(dP.NameShort ,'') AS ProductNameShort ,COALESCE(dP.ImageName ,'') AS ProductImageName ,COALESCE(dP.Template ,'') AS ProductTemplate ,COALESCE(RPL.[Right] ,'') AS ProductRight --same as PL, but will become use later ,COALESCE(RPL.ReadCommentMandatory ,0) AS ReadCommentMandatory ,COALESCE(RPL.WriteCommentMandatory ,0) AS WriteCommentMandatory FROM planning.tdFactories dF LEFT JOIN planning.tdProductLines dPL ON dPL.FactoryKey = dF.FactoryKey LEFT JOIN planning.tdProducts dP ON dP.ProductLineKey = dPL.ProductLineKey -- Rights on FactoryLevel INNER JOIN #FactoryRights RF ON dF.FactoryID = RF.FactoryID -- Rights on ProductLineLevel INNER JOIN #ProductLineRights RPL ON dF.FactoryID = RPL.FactoryID AND (dPL.ProductLineID = RPL.ProductLineID OR dPL.ProductLineID IS NULL) -- maybe there is no ProductLine under this Factory, then the Factory must not be filtered. -- Filtering due to search string WHERE ( dF.FactoryID LIKE @Searchstring OR dF.NameShort LIKE @Searchstring OR dPL.ProductLineID LIKE @Searchstring OR dPL.NameShort LIKE @Searchstring OR dP.ProductID LIKE @Searchstring OR dP.NameShort LIKE @Searchstring ) OR @Searchstring = '' ORDER BY -- primary sort numeric, fallback to sort alphabetic dF.CommentDev ,ISNULL(TRY_CAST(dF.FactoryID AS INT),99999999) ,dF.FactoryID ,dPL.CommentDev ,ISNULL(TRY_CAST(dPL.ProductLineID AS INT),99999999) ,dPL.ProductLineID ,dP.CommentDev ,ISNULL(TRY_CAST(dP.ProductID AS INT),99999999) ,dP.ProductID; 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 -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'planning' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'spGET_Tree' -- 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'Returns the Tree of all planning object - Factories/ProductLines/Products'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- optional SET parameter documentation (only for Core / Standardmodules) SET @level2name = N'@Searchstring'; SET @value = N'String to filter the output values - will be handled as %Searchstring% search.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS planning.spPOST_Tab; GO /* Procedure to POST a Tab for - a cluster - a factory - a productline - a product Saxess Software GmbH Last modified: 08/2024 for OCT 5.10 Testcall Procedure DECLARE @RC INT; EXEC @RC = planning.spPOST_Tab @Username = 'W10\admin' ,@FactoryID = '' ,@ProductLineID = '' ,@ProductID = '' ,@TabID = 'Td5dd' ,@OrderIndex = '3' ,@TabName = 'Name' ,@TabHint = 'Hint' ,@TabText = 'Text' ,@PresentationCODE = 'Pivot' ,@Datasource = 'fdafds' ,@IsVisibleBOOL = '1' ,@LayoutJSON = 'dafds' ,@ParameterJSON = 'fdafd'; PRINT @RC SELECT * FROM planning.tTabs ORDER BY OrderIndex; TRUNCATE TABLE planning.tTabs; SELECT TOP 10 * FROM system.tAPI_Log; Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'planning', 'PROCEDURE', 'spPOST_Tab',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'planning', 'PROCEDURE', 'spPOST_Tab','PARAMETER',NULL) */ CREATE PROCEDURE planning.spPOST_Tab ( @Username NVARCHAR(255) ,@FactoryID NVARCHAR(255) = '' ,@ProductLineID NVARCHAR(255) = '' ,@ProductID NVARCHAR(255) = '' ,@TabID NVARCHAR(255) ,@OrderIndex INT ,@TabName NVARCHAR(255) ,@TabHint NVARCHAR(2000) ,@TabText NVARCHAR(2000) ,@PresentationCODE NVARCHAR(255) ,@Datasource NVARCHAR(2000) ,@IsVisibleBOOL INT ,@LayoutJSON NVARCHAR(MAX) = NULL ,@ParameterJSON NVARCHAR(MAX) ) AS BEGIN SET NOCOUNT ON; -- Standard declaration for logging DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID) ,@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''',' + CAST(ISNULL(@OrderIndex ,N'NULL') AS NVARCHAR(255)) + N',''' + ISNULL(@TabName ,N'NULL') + N''',''' + ISNULL(@TabHint ,N'NULL') + N''',''' + ISNULL(@TabText ,N'NULL') + N''',''' + ISNULL(@PresentationCODE ,N'NULL') + N''',''' + ISNULL(@Datasource ,N'NULL') + N''',' + CAST(ISNULL(@IsVisibleBOOL ,N'NULL') AS NVARCHAR(255)) + N',''' + ISNULL(@LayoutJSON ,N'NULL') + N''',''' + ISNULL(@ParameterJSON ,N'NULL') + N'''' ,@EffectedRows INT = 0 ,@ResultCode INT = 501 ,@TimestampCall DATETIME = GETUTCDATE() ,@Comment NVARCHAR(2000) = N'' ,@TransactUsername NVARCHAR(255) = N'' -- Procedure specific declarations DECLARE @FactoryKey INT = 0 ,@ProductLineKey INT = 0 ,@ProductKey INT = 0 ,@RowKey BIGINT = 0 ,@OldOrderIndex INT = 0; -- NULL Protection for all Input parameters 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 @TabID IS NULL SET @TabID = N''; IF @OrderIndex IS NULL SET @OrderIndex = 0 ; IF @TabName IS NULL SET @TabName = N''; IF @TabHint IS NULL SET @TabHint = N''; IF @TabText IS NULL SET @TabText = N''; IF @PresentationCODE IS NULL SET @PresentationCODE = N''; IF @Datasource IS NULL SET @Datasource = N''; IF @IsVisibleBOOL IS NULL SET @IsVisibleBOOL = 0 ; IF @LayoutJSON IS NULL SET @LayoutJSON = N''; IF @ParameterJSON IS NULL SET @ParameterJSON = N''; -- dont use negative index values IF @OrderIndex < 0 SET @OrderIndex = 1; -- protect bool IF @IsVisibleBOOL <> 1 SET @IsVisibleBOOL = 0; -- Content Protection for specific input parameters (e.g.IDs) SET @FactoryID = dbo.sx_pf_pProtectID (@FactoryID); SET @ProductLineID = dbo.sx_pf_pProtectID (@ProductLineID); SET @ProductID = dbo.sx_pf_pProtectID (@ProductID); -- START TRANSACTION *********************************************************************************** BEGIN TRY BEGIN TRANSACTION spPOST_Tab; -- 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; -- determine Keys for requested IDs - empty IDs keep Key -1 IF @FactoryID = '' SET @FactoryKey = -1; IF @ProductLineID = '' SET @ProductLineKey = -1; IF @ProductID = '' SET @ProductKey = -1; SELECT @FactoryKey = FactoryKey FROM planning.tdFactories WHERE FactoryID = @FactoryID AND @FactoryKey <> -1; SELECT @ProductLineKey = ProductLineKey FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey AND ProductLineID = @ProductLineID AND @ProductLineKey <> -1; SELECT @ProductKey = ProductKey FROM planning.tdProducts WHERE ProductLineKey = @ProductLineKey AND ProductID = @ProductID AND @ProductKey <> -1; -- error if and ID dont exists IF @FactoryKey = 0 OR @ProductLineKey = 0 OR @ProductKey = 0 BEGIN SET @ResultCode = 404; RAISERROR(N'Not existing FID / PID / PLID - Keys don`t exists', 16, 10); END; -- error if ID chains has gaps IF @FactoryKey = -1 AND (@ProductLineKey <> -1 OR @ProductKey <> -1) OR @ProductLineKey = -1 AND @ProductKey <> -1 BEGIN SET @ResultCode = 404; RAISERROR('ID combination can have only gaps at the end - Keys don`t exists', 16, 10); END; -- check user rights for cluster level IF @FactoryKey = -1 BEGIN EXEC @ResultCode = sx_pf_pGET_ClusterWriteRight @TransactUsername; IF @ResultCode <> 200 BEGIN RAISERROR('Invalid rights for Cluster write.', 16, 10); END END -- check user rights for Factory level IF @FactoryKey <> -1 AND @ProductLineKey = -1 BEGIN EXEC @ResultCode = sx_pf_pGET_FactoryWriteRight @TransactUsername,@FactoryID; IF @ResultCode <> 200 BEGIN RAISERROR('Invalid rights for Factory write.', 16, 10); END END -- check user rights for ProductLine / Product level IF @FactoryKey <> -1 AND @ProductLineKey <> -1 BEGIN EXEC @ResultCode = sx_pf_pGET_ProductLineWriteRight @TransactUsername,@FactoryID,@ProductLineID; IF @ResultCode <> 200 BEGIN RAISERROR('Invalid rights for ProductLine / Product write.', 16, 10); END END SET @ResultCode = 501 -- reset -- determine if posted Tab already exists SELECT @Rowkey = RowKey ,@OldOrderIndex = OrderIndex FROM planning.tTabs WHERE FactoryKey = @FactoryKey AND ProductLineKey = @ProductLineKey AND ProductKey = @ProductKey AND TabID = @TabID -- data operation starts *********************************************************************************** -- delete if exits, to determine Resultcode IF @RowKey > 0 BEGIN -- delete tab layouts if tab type was changed IF @PresentationCODE <> (SELECT PresentationCODE FROM planning.tTabs WHERE RowKey = @RowKey) BEGIN DELETE FROM planning.tTabLayouts WHERE FactoryID = @FactoryID AND ProductLineID = @ProductLineID AND TabID = @TabID; END DELETE FROM planning.tTabs WHERE RowKey = @RowKey; SET @Resultcode = 200 END -- System Tab with TabID 0 can only change visibility IF @TabID = '0' BEGIN INSERT INTO planning.tTabs VALUES ( @FactoryKey ,@ProductLineKey ,@ProductKey ,@FactoryID ,@ProductLineID ,@ProductID ,@TabID ,0 ,@TabName ,N'' ,N'' ,N'System' ,N'' ,@IsVisibleBOOL ,N'' ) END ELSE BEGIN -- Insert new Tab INSERT INTO planning.tTabs VALUES ( @FactoryKey ,@ProductLineKey ,@ProductKey ,@FactoryID ,@ProductLineID ,@ProductID ,@TabID ,@OrderIndex ,@TabName ,@TabHint ,@TabText ,@PresentationCODE ,@Datasource ,@IsVisibleBOOL ,@ParameterJSON ) -- Update other numbers if Number is changed or new IF @OldOrderIndex <> @OrderIndex BEGIN -- case if OrderIndex is bigger now and existed before - decrement all in move window IF @OldOrderIndex < @OrderIndex AND @RowKey <> 0 BEGIN UPDATE planning.tTabs SET OrderIndex = OrderIndex - 1 WHERE OrderIndex >= @OldOrderIndex AND OrderIndex <= @OrderIndex AND FactoryKey = @FactoryKey AND ProductLineKey = @ProductLineKey AND ProductKey = @ProductKey AND TabID <> @TabID END -- case if OrderIndex is smaller now and existed before - increment all in move window IF @OldOrderIndex > @OrderIndex AND @RowKey <> 0 BEGIN UPDATE planning.tTabs SET OrderIndex = OrderIndex + 1 WHERE OrderIndex <= @OldOrderIndex AND OrderIndex >= @OrderIndex AND FactoryKey = @FactoryKey AND ProductLineKey = @ProductLineKey AND ProductKey = @ProductKey AND TabID <> @TabID END -- case new Tab - increment all following IF @RowKey = 0 BEGIN UPDATE planning.tTabs SET OrderIndex = OrderIndex + 1 WHERE OrderIndex >= @OrderIndex AND FactoryKey = @FactoryKey AND ProductLineKey = @ProductLineKey AND ProductKey = @ProductKey AND TabID <> @TabID END -- eliminate all Gaps UPDATE planning.tTabs SET OrderIndex = Rownumbers.Number FROM planning.tTabs tT INNER JOIN ( SELECT ROW_NUMBER() OVER (ORDER BY OrderIndex) AS Number ,TabID FROM planning.tTabs WHERE FactoryKey = @FactoryKey AND ProductLineKey = @ProductLineKey AND ProductKey = @ProductKey AND TabID <> '0' ) AS Rownumbers ON tT.TabID = Rownumbers.TabID AND tT.FactoryKey = @FactoryKey AND tT.ProductLineKey = @ProductLineKey AND tT.ProductKey = @ProductKey END -- save TabLayout IF @LayoutJSON <> '' BEGIN EXEC planning.spPOST_TabLayout @Username = @Username, @FactoryID = @FactoryID, @ProductLineID = @ProductLineID, @TabID = @TabID, @LayoutID = '*', @LayoutName = 'Default', @LayoutJSON = @LayoutJSON, @OrderIndex = 1, @LayoutOwner = '' END END IF @ResultCode <> 200 SET @ResultCode = 201; COMMIT TRANSACTION spPOST_Tab; END TRY -- START CATCH *********************************************************************************** BEGIN CATCH DECLARE @Error_state INT = ERROR_STATE(); SET @Comment = ERROR_MESSAGE(); ROLLBACK TRANSACTION spPOST_Tab; 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 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'planning' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'spPOST_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 = not shiped from saxess -- 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 -- 10 = shiped from saxess as customer specific object -- 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 documententation ************************************************************************* -- SET Procedure documentation SET @value = N'Procedure to POST a Tab. The Tab with ID 0 is the special system tab, but does not get special handling in databae.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- SET parameter documentation SET @level2name = N'@Username'; SET @value = N'Name of the user this action is requested for.'; 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 POST 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 POST is for Cluster or Factory level'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ProductID'; SET @value = N'optional ProductD, skip / NULL / empty string if POST is for Cluster, Factory or ProductLine level'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@TabID'; SET @value = N'Business Identifier for the Tab.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@OrderIndex'; SET @value = N'Position in the list of Tabs. Any Number > 0 can be passed in, tabs reorder themself.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@TabName'; SET @value = N'Name of the Tab, used as Label.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@TabHint'; SET @value = N'Hint on the Tab, displayed as Tooltip.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@TabText'; SET @value = N'Text for the headerarea inside the tab.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@PresentationCODE'; SET @value = N'CODE word for the type of presentation, e.g PIVOT, DATAGRID, HTML_URL, HTML_STATIC.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@Datasource'; SET @value = N'Datasource fitting to PresentationCODE - name of the stored procedure or URL.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@IsVisibleBool'; SET @value = N'1 for visible, 0 for not visible'; 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'@ParameterJSON'; SET @value = N'Parameter definition (List and default value) as JSON String'; 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 AND @SettingMultipleLayouts = 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 IF @LayoutID = '*' BEGIN SELECT @LayoutID = COALESCE(MAX(CAST(LayoutID AS INT)), 0) + 1 FROM planning.tTabLayouts WHERE FactoryID = @FactoryID AND ProductLineID = @ProductLineID AND TabID = @TabID; END -- 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 result.spDataEntryNumericValues; GO /* Procedure to deliver all numeric values from data entry products to the enduser Expectes an optional parameter "Year" with one or multiple Years Gerd Tautenhahn for Saxess Software GmbH Last modified: 10/2022 for OCT 5.8 Testcall DECLARE @RC int DECLARE @Username NVARCHAR(255) = 'SQL' DECLARE @FactoryID NVARCHAR(255) = '' DECLARE @ProductLineID NVARCHAR(255) = '' DECLARE @ParameterJSON NVARCHAR(MAX) = N'{"Year":[2020]}' EXECUTE @RC = result.spDataEntryNumericValues @Username ,@FactoryID ,@ProductLineID ,@ParameterJSON Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'result', 'PROCEDURE', 'spDataEntryNumericValues',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'result', 'PROCEDURE', 'spDataEntryNumericValues','PARAMETER',NULL) */ CREATE PROCEDURE result.spDataEntryNumericValues ( @Username NVARCHAR(255) ,@FactoryID NVARCHAR(255) = N'' ,@ProductLineID NVARCHAR(255) = N'' ,@ParameterJSON NVARCHAR(MAX) = N'' ) AS BEGIN SET NOCOUNT ON; DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID) ,@ParameterString NVARCHAR(MAX) = N'''' + ISNULL(@Username ,N'NULL') + N''',''' + ISNULL(@FactoryID ,N'NULL') + N''',''' + ISNULL(@ProductLineID ,N'NULL') + N''',''' + ISNULL(@ParameterJSON ,N'NULL') + N'''' ,@EffectedRows INT = 0 ,@ResultCode INT = 501 ,@TimestampCall DATETIME = GETUTCDATE() ,@Comment NVARCHAR(2000) = N'' ,@TransactUsername NVARCHAR(255) = N''; -- NULL Protection for all Input parameters IF @Username IS NULL SET @Username = N''; IF @FactoryID IS NULL SET @FactoryID = N''; IF @ProductLineID IS NULL SET @ProductLineID = N''; IF @ParameterJSON IS NULL SET @ParameterJSON = N''; -- Content Protection SET @FactoryID = dbo.sx_pf_pProtectID (@FactoryID); SET @ProductLineID = dbo.sx_pf_pProtectID (@ProductLineID); DECLARE @years AS TABLE (Year INT NOT NULL); DECLARE @hasYears BIT = 0; -- START TRANSACTION *********************************************************************************** BEGIN TRY -- Test for valid JSON SET @hasYears = ISJSON(@ParameterJSON); IF @ParameterJSON <> N'' AND @hasYears = 0 BEGIN SET @ResultCode = 403; RAISERROR('Exit due to invalid JSON', 16, 10); END; -- 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; -- determine Years IF @hasYears = 1 BEGIN INSERT INTO @years SELECT DISTINCT value FROM OPENJSON(@ParameterJSON,'$.Year') WHERE value >= 0 AND value <= 9999; END; -- Helper Table for Userrights DROP TABLE IF EXISTS #tRights; CREATE TABLE #tRights ( RowKey BIGINT IDENTITY (1,1) ,FactoryID NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,ProductLineID NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,PRIMARY KEY CLUSTERED (RowKey) ); INSERT INTO #tRights SELECT FactoryID ,ProductLineID FROM system.trUserRights WHERE Username = @TransactUsername AND [Right] IN (N'Write', N'Read') AND ProductLineID <> N'' AND FactoryID <> N'' AND ( -- no Filter Paramter given (@FactoryID = N'' AND @ProductLineID = N'') OR ( -- FactoryID Filter given FactoryID = @FactoryID -- optional also ProductLine Filter given AND (@ProductLineID = N'' OR ProductLineID = @ProductLineID) ) ); SELECT fV.FactoryID ,fV.ProductLineID ,fV.ProductID ,fV.ValueSeriesID ,fV.TimeID / 10000 AS [Year] ,fV.TimeID % 10000 / 100 AS [Month] ,fV.TimeID % 100 AS [Day] ,TRY_CAST(CAST(fV.TimeID AS NVARCHAR) AS DATE) AS [Date] ,TRY_CAST(fV.ValueInt AS Money) / ISNULL(dVS.Scale, 1) AS [Value] ,fV.FactoryID + N' ' + dF.NameShort AS FactoryIDName ,dF.NameShort AS FactoryName ,dF.ResponsiblePerson AS FResponsiblePerson ,fV.ProductLineID + N' ' + dPL.NameShort AS ProductLineIDName ,dPL.NameShort AS ProductLineName ,dPL.ResponsiblePerson AS PLResponsiblePerson ,fV.ProductID + N' ' + dP.NameShort AS ProductIDName ,dP.NameShort AS ProductName ,dP.ResponsiblePerson AS PResponsiblePerson ,fV.ValueSeriesID + N' ' + dVS.NameShort AS ValueSeriesIDName ,dVS.NameShort ,dVS.ValueSeriesNo ,dVS.VisibilityLevel ,dVS.ValueSource ,dVS.Unit ,dVS.Effect ,fV.ValueComment ,dP.[Status] ,dP.GlobalAttribute1 ,dP.GlobalAttribute2 ,dP.GlobalAttribute3 ,dP.GlobalAttribute4 ,dP.GlobalAttribute5 ,dP.GlobalAttribute6 ,dP.GlobalAttribute7 ,dP.GlobalAttribute8 ,dP.GlobalAttribute9 ,dP.GlobalAttribute10 ,dP.GlobalAttribute11 ,dP.GlobalAttribute12 ,dP.GlobalAttribute13 ,dP.GlobalAttribute14 ,dP.GlobalAttribute15 ,dP.GlobalAttribute16 ,dP.GlobalAttribute17 ,dP.GlobalAttribute18 ,dP.GlobalAttribute19 ,dP.GlobalAttribute20 ,dP.GlobalAttribute21 ,dP.GlobalAttribute22 ,dP.GlobalAttribute23 ,dP.GlobalAttribute24 ,dP.GlobalAttribute25 FROM planning.tfValues fV LEFT JOIN planning.tdProducts dP ON fV.ProductKey = dP.ProductKey LEFT JOIN planning.tdProductLines dPL ON fV.ProductLineKey = dPL.ProductLineKey LEFT JOIN planning.tdFactories dF ON fV.FactoryKey = dF.FactoryKey -- Right Filter INNER JOIN #tRights tR ON fV.FactoryID = tR.FactoryID AND fV.ProductLineID = tR.ProductLineID -- to filter on numeric ValueSeries INNER JOIN planning.tdValueSeries dVS ON fV.ValueSeriesKey = dVS.ValueSeriesKey AND dVS.[IsNumeric] = 1 WHERE fV.ValueInt <> 0 -- Year Filter AND (@hasYears = 0 OR fV.TimeID / 10000 IN (SELECT Year FROM @years)) 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 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'result' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'spDataEntryNumericValues'-- 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'All numeric values from data entry products' -- 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 deliver all numeric values of data entry products to the enduser.' ; 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'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ProductLineID'; SET @value = N'optional: ProductLineID'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ParameterJSON'; SET @value = N'optional: The Parameter JSON expect the "Year" parameter.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS result.spDataEntryProfit; GO /* Procedure to deliver analytic profit data from data entry products to the enduser Expectes an optional parameter "Year" with one or multiple Years Gerd Tautenhahn for Saxess Software GmbH Last modified: 10/2022 for OCT 5.8 Testcall DECLARE @RC int DECLARE @Username NVARCHAR(255) = 'SQL' DECLARE @FactoryID NVARCHAR(255) = '' DECLARE @ProductLineID NVARCHAR(255) = '' DECLARE @ParameterJSON NVARCHAR(MAX) = N'{"Year":[2020]}' EXECUTE @RC = result.spDataEntryProfit @Username ,@FactoryID ,@ProductLineID ,@ParameterJSON Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'result', 'PROCEDURE', 'spDataEntryProfit',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'result', 'PROCEDURE', 'spDataEntryProfit','PARAMETER',NULL) */ CREATE PROCEDURE result.spDataEntryProfit ( @Username NVARCHAR(255) ,@FactoryID NVARCHAR(255) = N'' ,@ProductLineID NVARCHAR(255) = N'' ,@ParameterJSON NVARCHAR(MAX) = N'' ) AS BEGIN SET NOCOUNT ON; DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID) ,@ParameterString NVARCHAR(MAX) = N'''' + ISNULL(@Username ,N'NULL') + N''',''' + ISNULL(@FactoryID ,N'NULL') + N''',''' + ISNULL(@ProductLineID ,N'NULL') + N''',''' + ISNULL(@ParameterJSON ,N'NULL') + N'''' ,@EffectedRows INT = 0 ,@ResultCode INT = 501 ,@TimestampCall DATETIME = GETUTCDATE() ,@Comment NVARCHAR(2000) = N'' ,@TransactUsername NVARCHAR(255) = N''; -- NULL Protection for all Input parameters IF @Username IS NULL SET @Username = N''; IF @FactoryID IS NULL SET @FactoryID = N''; IF @ProductLineID IS NULL SET @ProductLineID = N''; IF @ParameterJSON IS NULL SET @ParameterJSON = N''; -- Content Protection SET @FactoryID = dbo.sx_pf_pProtectID (@FactoryID); SET @ProductLineID = dbo.sx_pf_pProtectID (@ProductLineID); DECLARE @years AS TABLE (Year INT NOT NULL); DECLARE @hasYears BIT = 0; -- START TRANSACTION *********************************************************************************** BEGIN TRY -- Test for valid JSON SET @hasYears = ISJSON(@ParameterJSON); IF @ParameterJSON <> N'' AND @hasYears = 0 BEGIN SET @ResultCode = 403; RAISERROR('Exit due to invalid JSON', 16, 10); END; -- 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; -- determine Years IF @hasYears = 1 BEGIN INSERT INTO @years SELECT DISTINCT value FROM OPENJSON(@ParameterJSON,'$.Year') WHERE value >= 0 AND value <= 9999; END; -- Helper Table for Userrights DROP TABLE IF EXISTS #tRights; CREATE TABLE #tRights ( RowKey BIGINT IDENTITY (1,1) ,FactoryID NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,ProductLineID NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,PRIMARY KEY CLUSTERED (RowKey) ); INSERT INTO #tRights SELECT FactoryID ,ProductLineID FROM system.trUserRights WHERE Username = @TransactUsername AND [Right] IN (N'Write', N'Read') AND ProductLineID <> N'' AND FactoryID <> N'' AND ( -- no Filter Paramter given (@FactoryID = N'' AND @ProductLineID = N'') OR ( -- FactoryID Filter given FactoryID = @FactoryID -- optional also ProductLine Filter given AND (@ProductLineID = N'' OR ProductLineID = @ProductLineID) ) ); SELECT fV.FactoryID ,fV.ProductLineID ,fV.ProductID ,fV.ValueSeriesID ,fV.TimeID / 10000 AS [Year] ,fV.TimeID % 10000 / 100 AS [Month] ,fV.TimeID % 100 AS [Day] ,TRY_CAST(CAST(fV.TimeID AS NVARCHAR) AS DATE) AS [Date] ,TRY_CAST(fV.ValueInt AS Money) / ISNULL(dVS.Scale, 1) * gVE.ProfitLossEffect AS [Value] ,fV.FactoryID + N' ' + dF.NameShort AS FactoryIDName ,dF.NameShort AS FactoryName ,dF.ResponsiblePerson AS FResponsiblePerson ,fV.ProductLineID + N' ' + dPL.NameShort AS ProductLineIDName ,dPL.NameShort AS ProductLineName ,dPL.ResponsiblePerson AS PLResponsiblePerson ,fV.ProductID + N' ' + dP.NameShort AS ProductIDName ,dP.NameShort AS ProductName ,dP.ResponsiblePerson AS PResponsiblePerson ,fV.ValueSeriesID + N' ' + dVS.NameShort AS ValueSeriesIDName ,dVS.NameShort ,dVS.ValueSeriesNo ,dVS.VisibilityLevel ,dVS.ValueSource ,dVS.Unit ,dVS.Effect ,fV.ValueComment ,dP.[Status] ,dP.GlobalAttribute1 ,dP.GlobalAttribute2 ,dP.GlobalAttribute3 ,dP.GlobalAttribute4 ,dP.GlobalAttribute5 ,dP.GlobalAttribute6 ,dP.GlobalAttribute7 ,dP.GlobalAttribute8 ,dP.GlobalAttribute9 ,dP.GlobalAttribute10 ,dP.GlobalAttribute11 ,dP.GlobalAttribute12 ,dP.GlobalAttribute13 ,dP.GlobalAttribute14 ,dP.GlobalAttribute15 ,dP.GlobalAttribute16 ,dP.GlobalAttribute17 ,dP.GlobalAttribute18 ,dP.GlobalAttribute19 ,dP.GlobalAttribute20 ,dP.GlobalAttribute21 ,dP.GlobalAttribute22 ,dP.GlobalAttribute23 ,dP.GlobalAttribute24 ,dP.GlobalAttribute25 FROM planning.tfValues fV LEFT JOIN planning.tdProducts dP ON fV.ProductKey = dP.ProductKey LEFT JOIN planning.tdProductLines dPL ON fV.ProductLineKey = dPL.ProductLineKey LEFT JOIN planning.tdFactories dF ON fV.FactoryKey = dF.FactoryKey -- Right Filter INNER JOIN #tRights tR ON fV.FactoryID = tR.FactoryID AND fV.ProductLineID = tR.ProductLineID -- to filter on numeric ValueSeries INNER JOIN planning.tdValueSeries dVS ON fV.ValueSeriesKey = dVS.ValueSeriesKey AND dVS.[IsNumeric] = 1 -- to filter on Profit / Loss INNER JOIN planning.tgValueEffects gVE ON dVS.Effect = gVE.EffectID AND gVE.ProfitLossEffect <> 0 WHERE fV.ValueInt <> 0 -- Year Filter AND (@hasYears = 0 OR fV.TimeID / 10000 IN (SELECT Year FROM @years)) 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 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'result' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'spDataEntryProfit'-- 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'Analytic profit data from data entry products' -- 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 deliver analytic data of data entry products to the enduser.' ; 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'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ProductLineID'; SET @value = N'optional: ProductLineID'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ParameterJSON'; SET @value = N'optional: The Parameter JSON expect the "Year" parameter.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS system.spCOPY_Pipeline; GO /* Procedure to copy a Pipeline - including its steps and schedules Saxess Software GmbH Last modified: 09/2024 for OCT 5.10 Testcall Procedure DECLARE @RC INT ,@ReturnID NVARCHAR(50); EXEC @RC = system.spCOPY_Pipeline @Username = 'SQL' ,@SourcePipelineID = 'P1' ,@TargetPipelineID = '*' ,@OutputID = @ReturnID OUTPUT; PRINT CONCAT('ReturnCode: ', @RC, ' OutputID: ', @ReturnID); SELECT * FROM system.tPipelines; SELECT * FROM system.tPipelineSteps; SELECT * FROM system.tPipelineSchedules; Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spCOPY_Pipeline',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spCOPY_Pipeline','PARAMETER',NULL) */ CREATE PROCEDURE system.spCOPY_Pipeline ( @Username NVARCHAR(255) ,@SourcePipelineID NVARCHAR(50) ,@TargetPipelineID NVARCHAR(50) ,@OutputID NVARCHAR(50) = NULL OUTPUT ) AS BEGIN -- Standard declaration for logging DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID) ,@ParameterString NVARCHAR(MAX) = N'''' --set Strings 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 + ISNULL(@Username ,N'NULL') + N''',' + ISNULL(@SourcePipelineID ,N'NULL') + N',''' + ISNULL(@TargetPipelineID ,N'NULL') + N'''' ,@EffectedRows INT = 0 ,@ResultCode INT = 501 ,@TimestampCall DATETIME = GETUTCDATE() ,@Comment NVARCHAR(2000) = N'' ,@TransactUsername NVARCHAR(255) = N'' ,@StartTime DATETIME2 = SysUTCDateTime() ,@SourcePipelineKey BIGINT = 0 ,@TargetPipelineKey BIGINT = 0 ,@LastPipelineNum NVARCHAR(50) = 0 ,@TargetOrderOfDisplay INT = 0; -- NULL Protection for all Input parameters IF @Username IS NULL SET @Username = N''; IF @SourcePipelineID IS NULL SET @SourcePipelineID = N''; IF @TargetPipelineID IS NULL SET @TargetPipelineID = N''; -- START TRANSACTION *********************************************************************************** BEGIN TRY BEGIN TRANSACTION spCOPY_Pipeline -- 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; -- check existence of IDs IF NOT EXISTS ( SELECT PipelineID FROM system.tPipelines WHERE PipelineID = @SourcePipelineID ) BEGIN SET @ResultCode = 404; RAISERROR('Source Pipeline don`t exits', 16, 10); END SELECT @SourcePipelineKey = PipelineKey FROM system.tPipelines WHERE PipelineID = @SourcePipelineID; IF EXISTS ( SELECT PipelineID FROM system.tPipelines WHERE PipelineID = @TargetPipelineID ) BEGIN SET @ResultCode = 403; RAISERROR('Target Pipeline already exits', 16, 10); END -- determine new ID if * is sended IF @TargetPipelineID = '*' BEGIN SELECT TOP 1 @LastPipelineNum = TRY_CAST(RIGHT(PipelineID,LEN(PipelineID)-1) AS INT ) FROM system.tPipelines WHERE PipelineID LIKE 'P%' AND LEN(PipelineID) > 1 AND TRY_CAST(RIGHT(PipelineID,LEN(PipelineID)-1) AS INT) IS NOT NULL ORDER BY TRY_CAST(RIGHT(PipelineID,LEN(PipelineID)-1) AS INT) DESC IF @LastPipelineNum IS NULL SET @LastPipelineNum = 0; SET @TargetPipelineID = 'P' + CAST(@LastPipelineNum +1 AS NVARCHAR(50)); END -- determine TargetOrderOfDisplay SELECT TOP 1 @TargetOrderOfDisplay = OrderOfDisplay + 1 FROM system.tPipelines ORDER BY OrderOfDisplay DESC; -- Copy the pipeline itself INSERT INTO system.tPipelines SELECT @TargetPipelineID ,PipelineName ,PipelineDescription ,PipelineDescriptionIconColor ,@TargetOrderOfDisplay ,EmailNotificationCODE FROM system.tPipelines WHERE PipelineID = @SourcePipelineID; SET @TargetPipelineKey = SCOPE_IDENTITY(); -- Copy the steps of the Pipeline INSERT INTO system.tPipelineSteps SELECT @TargetPipelineKey ,StepID ,StepName ,StepDescription ,OrderOfExecution ,StepDataSourceID ,StepCompanyIDsJSON ,StepDetailsJSON ,TimeExecutionLimit ,Active ,StartNextStepOnError FROM system.tPipelineSteps WHERE PipelineKey = @SourcePipelineKey; -- Copy the schedules of the Pipeline INSERT INTO system.tPipelineSchedules SELECT @TargetPipelineKey ,ScheduleID ,ScheduleName ,ScheduleDescription ,ScheduleDetailsJSON ,Active FROM system.tPipelineSchedules WHERE PipelineKey = @SourcePipelineKey; SET @ResultCode = 201; SET @OutputID = @TargetPipelineID; COMMIT TRANSACTION spCOPY_Pipeline END TRY -- START CATCH *********************************************************************************** BEGIN CATCH DECLARE @Error_state INT = ERROR_STATE(); SET @Comment = ERROR_MESSAGE(); ROLLBACK TRANSACTION spCOPY_Pipeline 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 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'spCOPY_Pipeline'-- 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 copy a Pipeline - including its steps and schedules'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- optional SET parameter documentation (only for Core / Standardmodules) SET @level2name = N'@SourcePipelineID'; SET @value = N'ID of the Pipeline which will be copied.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@TargetPipelineID'; SET @value = N'ID for the pipeline which will be created as copy of the SourcePipelineID, this Pipeline must not exists at the moment.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS system.spEXPORT_Connector; GO /* Procedure to create an OCT Importscript for a selected connector - used for export / import of connectors - exports the connector with its queries Toni August for Saxess Software GmbH Last modified: 12/2022 for OCT 5.9 Testcall Procedure DECLARE @RC INT; EXEC @RC = system.spEXPORT_Connector @Username = 'SQL' ,@SourceSystemID = 'NAVISION' ,@SourceSystemType = 'MSSQL' ,@ModuleID = 'FIN' ,@ExportType = 'JSON' PRINT @RC Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spEXPORT_Connector',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spEXPORT_Connector','PARAMETER',NULL) */ CREATE PROCEDURE system.spEXPORT_Connector ( @Username NVARCHAR(255) ,@SourceSystemID NVARCHAR(50) ,@SourceSystemType NVARCHAR(50) ,@ModuleID NVARCHAR(50) ,@ExportType NVARCHAR(50) = 'SQL' ) 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(CAST(@SourceSystemID AS NVARCHAR(50)) ,N'NULL') + N''',' + ISNULL(CAST(@SourceSystemType AS NVARCHAR(50)) ,N'NULL') + N''',' + ISNULL(CAST(@ModuleID AS NVARCHAR(50)) ,N'NULL') + N'''' ,@EffectedRows INT = 0 ,@ResultCode INT = 501 ,@TimestampCall DATETIME = GETUTCDATE() ,@Comment NVARCHAR(2000) = N'' ,@TransactUsername NVARCHAR(255) = N'' ,@StartTime DATETIME2 = SysUTCDateTime() ,@SourceClusterName NVARCHAR(255) = N'' ,@SourceClusterAPI NVARCHAR(255) = N''; -- NULL Protection for all Input parameters IF @Username IS NULL SET @Username = N''; IF @SourceSystemID IS NULL SET @SourceSystemID = N''; IF @SourceSystemType IS NULL SET @SourceSystemType = N''; IF @ModuleID IS NULL SET @ModuleID = 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; IF @ExportType = 'SQL' BEGIN -- 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 ,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": "Connector",') ,(0, 3, CONCAT(N'-- "SourceClusterName": "', @SourceClusterName,'",')) ,(0, 4, CONCAT(N'-- "SourceClusterAPI": "',@SourceClusterAPI,'",')) ,(0, 5, CONCAT(N'-- "SourceSystemID": "',@SourceSystemID,'",')) ,(0, 6, CONCAT(N'-- "SourceSystemType": "',@SourceSystemType,'",')) ,(0, 7, CONCAT(N'-- "ModuleID": "',@ModuleID,'"')) ,(0, 8, 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 @SourceSystemID NVARCHAR(255) = ''' + @SourceSystemID+ N'''') ,(10, 4, N'DECLARE @SourceSystemType NVARCHAR(255) = ''' + @SourceSystemType+ N'''') ,(10, 5, N'DECLARE @ModuleID NVARCHAR(255) = ''' + @ModuleID+ N'''') ,(10, 6, N'--This Connector will be deleted during import, if it exists. You should be sure !') -- Try_Delete existing connector INSERT INTO #tExport SELECT 900 AS MainOrderNumber ,1000 AS SubOrderNumber ,CONCAT( 'EXEC system.spDELETE_Connector ' ,'@Username' ,',@SourceSystemID' ,',@SourceSystemType' ,',@ModuleID' ,';' ) AS Command FROM system.tConnectors WHERE SourceSystemID = @SourceSystemID AND SourceSystemType = @SourceSystemType AND ModuleID = @ModuleID; -- Create the connector as new connector INSERT INTO #tExport SELECT 1000 AS MainOrderNumber ,1000 AS SubOrderNumber ,CONCAT( 'EXEC system.spPOST_Connector ' ,'@Username' ,',@SourceSystemID' ,',@SourceSystemType' ,',@ModuleID' ,',''' ,dbo.sx_pf_pMaskSQL(ConnectorDescription) ,'''' ,',''' ,dbo.sx_pf_pMaskSQL(ConnectorShortDescription) ,'''' ,',''' ,dbo.sx_pf_pMaskSQL(ConnectorDescriptionURL) ,'''' ,',''' ,dbo.sx_pf_pMaskSQL(ConnectorVersion),'''' ,',''' ,dbo.sx_pf_pMaskSQL(Manufacturer),'''' ) AS Command FROM system.tConnectors WHERE SourceSystemID = @SourceSystemID AND SourceSystemType = @SourceSystemType AND ModuleID = @ModuleID; -- Create the Steps INSERT INTO #tExport SELECT 2000 AS MainOrderNumber ,1000 AS SubOrderNumber ,CONCAT( 'EXEC system.spPOST_ConnectorQuery ' ,'@Username' ,',@SourceSystemID' ,',@SourceSystemType' ,',@ModuleID' ,',''' ,cq.QueryName ,'''' ,',''' ,dbo.sx_pf_pMaskSQL(cq.QuerySQL) ,'''' ,',''' ,dbo.sx_pf_pMaskSQL(cq.QueryVersionComment) ,'''' ) AS Command FROM system.tConnectorQueries AS cq INNER JOIN system.tConnectors AS c ON c.ConnectorKey = cq.ConnectorKey LEFT JOIN system.tConnectorQueries AS cqMax ON cq.ConnectorKey = cqMax.ConnectorKey AND cq.QueryName = cqMax.QueryName AND cq.QueryVersion < cqMax.QueryVersion WHERE c.SourceSystemID = @SourceSystemID AND c.SourceSystemType = @SourceSystemType AND c.ModuleID = @ModuleID AND cqMax.QueryVersion IS NULL; -- final GO INSERT INTO #tExport VALUES (9999, 1, N'GO'); -- Data Transaction SELECT Command FROM #tExport ORDER BY MainOrderNumber ,SubOrderNumber END IF @ExportType = 'JSON' BEGIN SELECT ( SELECT f1.SourceSystemID, f1.SourceSystemType, f1.ModuleID, f1.ConnectorDescription AS ConnectorDescription, f1.ConnectorShortDescription AS ConnectorShortDescription, f1.ConnectorDescriptionURL AS ConnectorDescriptionURL, f1.ConnectorVersion AS ConnectorVersion, f1.Manufacturer AS Manufacturer, ( SELECT f2.QueryName, f2.QuerySQL AS Query, f2.QueryVersion AS QueryVersion FROM system.tConnectorQueries AS f2 WHERE f2.ConnectorKey = f1.ConnectorKey AND f2.QueryVersion = ( SELECT MAX(f3.QueryVersion) FROM system.tConnectorQueries AS f3 WHERE f3.ConnectorKey = f2.ConnectorKey AND f3.QueryName = f2.QueryName ) ORDER BY CASE WHEN f2.QueryName = 'GetCompanies' THEN 1 ELSE 2 END ASC, f2.QueryName ASC FOR JSON PATH ) AS 'ConnectorQueries' FROM system.tConnectors AS f1 WHERE f1.SourceSystemID = @SourceSystemID AND f1.SourceSystemType = @SourceSystemType AND f1.ModuleID = @ModuleID FOR JSON PATH, ROOT ('Connector') ) AS Command END 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'spEXPORT_Connector' -- 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 connector with all steps in OCT.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- optional SET parameter documentation (only for Core / Standardmodules) SET @level2name = N'@SourceSystemID'; SET @value = N'SourceSystemID - ID of the source ERP system.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@SourceSystemType'; SET @value = N'SourceSystemType - type of connection to the source ERP system (MSSQL, ORACLE, ODBC).'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ModuleID'; SET @value = N'ModuleID - ID of the module.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS system.spEXPORT_Icon; GO /* Procedure to create an OCT Importscript for an selected Icon Gerd Tautenhahn for Saxess Software GmbH Last modified: 05/2023 for OCT 5.9 Testcall Procedure DECLARE @RC INT; EXEC @RC = system.spEXPORT_Icon @Username = N'SQL' ,@IconName = N'GuV'; PRINT @RC; Testing in SSMS is only limited possible - Icons with complex images resulting in very long text -> see system.spExport_Setting for more informations Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spEXPORT_Icon',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spEXPORT_Icon','PARAMETER',NULL) */ CREATE PROCEDURE system.spEXPORT_Icon @Username NVARCHAR(255) ,@IconName NVARCHAR(50) 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(@IconName ,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 @IconName IS NULL SET @IconName = 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 Iconle 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 ,PRIMARY KEY CLUSTERED (RowKey) ); -- Determine Metadata SELECT @SourceClusterName = ValueText FROM system.tSettings WHERE SettingID = N'Clustername'; -- May be empty if not defined SET @SourceClusterName = CONCAT(DB_Name(),N' ',@SourceClusterName); SELECT @SourceClusterAPI = ValueText FROM system.tSettings WHERE SettingID = N'DBVersion'; -- MetaHeader INSERT INTO #tExport VALUES (0, 1, N'-- {') ,(0, 2, N'-- "Type": "Icon",') ,(0, 3, CONCAT(N'-- "SourceClusterName": "', @SourceClusterName,'",')) ,(0, 4, CONCAT(N'-- "SourceClusterAPI": "',@SourceClusterAPI,'",')) ,(0, 5, CONCAT(N'-- "SourceFactoryID": "','','",')) ,(0, 5, CONCAT(N'-- "SourceProductLineID": "','','",')) ,(0, 5, CONCAT(N'-- "SourceProductID": "','','",')) ,(0, 6, CONCAT(N'-- "SourceIconName": "',@IconName,'"')) ,(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 @IconName NVARCHAR(255) = ''' + @IconName + N'''') ,(10, 4, N'--This Icon will be deleted during import, if it exists. You should be sure !'); -- Try_Delete existing Icon INSERT INTO #tExport SELECT 900 AS MainOrderNumber ,1000 AS SubOrderNumber ,CONCAT( 'EXEC system.spDELETE_Icon ' ,'''SQL''' ,',@IconName' ,';' ) AS Command -- Create the Icon as new Icon INSERT INTO #tExport SELECT 1000 AS MainOrderNumber ,1000 AS SubOrderNumber ,CONCAT( 'EXEC system.spPOST_Icon ' ,'''SQL''' ,',@IconName' ,',''' ,FileExtension ,'''' ,',0x' ,CONVERT(NVARCHAR(MAX),FileBody,2) ,';' ) AS Command FROM system.tIcons WHERE IconName = @IconName; -- final GO INSERT INTO #tExport VALUES (9999, 1, N'GO'); -- Data Transaction SELECT Command FROM #tExport ORDER BY MainOrderNumber ,SubOrderNumber 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 execuIconle 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 daIconase access to this object) -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'system' -- enter schema name of the Iconle ,@level1name NVARCHAR(255) = N'spEXPORT_Icon' -- 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 Icon in OCT.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- optional SET parameter documentation (only for Core / Standardmodules) SET @level2name = N'@IconName'; SET @value = N'Name of the Icon to be exported.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS system.spEXPORT_Pipeline; GO /* Procedure to create an OCT Importscript for an selected Pipeline - used for export / import of pipelines - exports the pipeline with its steps an schedules - NO global parameters used by the pipeline are exported (would be possible, but complex JSON operation) - conditions and consequences - a new Pipeline with the next free ID is created - the user must after the import or before - create a datasource(s) with type fitting to the used steps - download / activate modules needed by the steps - select companies in the datasource an in the steps - create global parameters used in the steps Gerd Tautenhahn for Saxess Software GmbH Last modified: 11/2022 for OCT 5.9 Testcall Procedure DECLARE @RC INT; EXEC @RC = system.spEXPORT_Pipeline @Username = 'SQL' ,@PipelineID = 'P4' PRINT @RC Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spEXPORT_Pipeline',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spEXPORT_Pipeline','PARAMETER',NULL) */ CREATE PROCEDURE system.spEXPORT_Pipeline ( @Username NVARCHAR(255) ,@PipelineID NVARCHAR(50) ) 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(CAST(@PipelineID AS NVARCHAR(255)) ,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 @PipelineID IS NULL SET @PipelineID = 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 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 ,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": "Pipeline",') ,(0, 3, CONCAT(N'-- "SourceClusterName": "', @SourceClusterName,'",')) ,(0, 4, CONCAT(N'-- "SourceClusterAPI": "',@SourceClusterAPI,'",')) ,(0, 5, CONCAT(N'-- "SourcePipelineID": "',@PipelineID,'"')) ,(0, 6, 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 @PipelineID NVARCHAR(255) = ''' + @PipelineID + N'''') ,(10, 4, N'--This PipelneID will be deleted during import, if it exists. You should be sure !') ,(10, 5, N'DECLARE @ReturnedPipelineID NVARCHAR (50);'); -- Try_Delete existing Pipeline INSERT INTO #tExport SELECT 900 AS MainOrderNumber ,1000 AS SubOrderNumber ,CONCAT( 'EXEC system.spDELETE_Pipeline ' ,'''SQL''' ,',@PipelineID' ,';' ) AS Command FROM system.tPipelines WHERE PipelineID = @PipelineID; -- Create the Pipeline as new Pipeline and catch the ID INSERT INTO #tExport SELECT 1000 AS MainOrderNumber ,1000 AS SubOrderNumber ,CONCAT( 'EXEC system.spPOST_Pipeline ' ,'''SQL''' ,',@PipelineID' ,',''' ,dbo.sx_pf_pMaskSQL(PipelineName) ,'''' ,',''' ,dbo.sx_pf_pMaskSQL(PipelineDescription),'''' ,',''' ,PipelineDescriptionIconColor ,'''' ,',' ,'NULL' ---NULL leads to positon at the end ,',''' ,EmailNotificationCODE , '''' ,', @OutputID = @ReturnedPipelineID OUTPUT;' ) AS Command FROM system.tPipelines WHERE PipelineID = @PipelineID; -- Create the Steps INSERT INTO #tExport SELECT 2000 AS MainOrderNumber ,1000 AS SubOrderNumber ,CONCAT( 'EXEC system.spPOST_PipelineStep ' ,'''SQL''' ,',' ,'@ReturnedPipelineID' ,',''*''' ,',''' ,StepName ,'''' ,',''' ,dbo.sx_pf_pMaskSQL(StepDescription) ,'''' ,',' ,OrderOfExecution ,',''' ,StepDataSourceID , '''' ,',''' ,StepCompanyIDsJSON , '''' ,',''' ,dbo.sx_pf_pMaskSQL(StepDetailsJSON) , '''' --inverted commas inside sql statements etc. are handled by the pMaskSQL function ,',' ,TimeExecutionLimit ,',' ,Active ,',''''' -- keep old ModuleID parameter for compatibility reasons ,',' ,StartNextStepOnError ,';' ) AS Command FROM system.tPipelineSteps tPS INNER JOIN system.tPipelines tP ON tPS.PipelineKey = tP.PipelineKey WHERE tP.PipelineID = @PipelineID ORDER BY OrderOfExecution; -- Create the Schedulers INSERT INTO #tExport SELECT 3000 AS MainOrderNumber ,1000 AS SubOrderNumber ,CONCAT( 'EXEC system.spPOST_PipelineSchedule ' ,'''SQL''' ,',' ,'@ReturnedPipelineID' ,',''*''' ,',''' ,ScheduleName ,'''' ,',''' ,dbo.sx_pf_pMaskSQL(ScheduleDescription),'''' ,',''' ,dbo.sx_pf_pMaskSQL(ScheduleDetailsJSON),'''' ,',' ,Active ,';' ) AS Command FROM system.tPipelineSchedules tPS INNER JOIN system.tPipelines tP ON tPS.PipelineKey = tP.PipelineKey WHERE tP.PipelineID = @PipelineID; -- final GO INSERT INTO #tExport VALUES (9999, 1, N'GO'); -- Data Transaction SELECT Command FROM #tExport ORDER BY MainOrderNumber ,SubOrderNumber 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'spEXPORT_Pipeline' -- 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 pipeline with all steps in OCT.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- optional SET parameter documentation (only for Core / Standardmodules) SET @level2name = N'@PipelineID'; SET @value = N'ID of the Pipeline to be exported.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS system.spEXPORT_Setting; GO /* Procedure to create an OCT Importscript for an setting which is saved in system.tSettings mainly used for - ClusterStartTab - DataFlowTab - Global parameters Gerd Tautenhahn for Saxess Software GmbH Last modified: 08/2022 for OCT 5.8 Testcall Procedure DECLARE @RC INT; EXEC @RC = system.spEXPORT_Setting @Username = 'SQL' ,@SettingID = 'ClusterStart_Html_Page' -- ClusterStart_Html_Page, Integration_Html_Page, PipelineGlobalParameters PRINT @RC Sourcetable SELECT * FROM system.tSettings Manual Export / Import for large Settings - long settings (more than 64.000 Charakters = mostly encoded images) are not displayed in SSMS and can't be read over sqlcmd - you must use bcp 1. write the export to an table DROP TABLE IF EXISTS tmpout_Export; GO CREATE TABLE tmpout_Export ( Command NVARCHAR(MAX) ); INSERT INTO tmpout_Export EXEC system.spEXPORT_Setting 'SQL','ClusterStart_Html_Page'; SELECT * FROM dbo.tmpout_Export; 2. EXECUTE this command over CMD or copy it into a bat file (Go to the drive you want to have the output to with D:, and to the fitting folder with cd ...) bcp.exe "SELECT Command FROM tmpout_Export" queryout import.sql -d AD00283_Plenovia_Dev -U octadmin -P [password] -S octproviderdev.database.windows.net -e error.log -c -C65001 3. even you cant read it in SSMS, you can import it over SSMS as long a the file is smaller than 250 MB (Output of SSMS is character limited, Input for SSMS if file size limited) Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spEXPORT_Setting',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spEXPORT_Setting','PARAMETER',NULL) */ CREATE PROCEDURE system.spEXPORT_Setting ( @Username NVARCHAR(255) ,@SettingID NVARCHAR(50) ) 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(@SettingID ,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 @SettingID IS NULL SET @SettingID = 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 ,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": "Setting",') ,(0, 3, CONCAT(N'-- "SourceClusterName": "', @SourceClusterName,'",')) ,(0, 4, CONCAT(N'-- "SourceClusterAPI": "',@SourceClusterAPI,'"')) ,(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 @SettingID NVARCHAR(255) = ''' + @SettingID + N'''') ,(10, 4, N'--This Tab will be deleted during import, if it exists. You should be sure !') -- Create the Setting as new Setting INSERT INTO #tExport SELECT 1000 AS MainOrderNumber ,1000 AS SubOrderNumber ,CONCAT( 'EXEC system.pPOST_Setting ' ,'''SQL''' ,',@SettingID' ,',''' ,DataTypeCODE ,'''' ,',''' ,SettingDescription ,'''' ,',''' ,ValueText ,'''' ,',''' ,ValueDateTime ,'''' ,',' ,ValueInt ,'' ,',' ,ValueIntScale ,'' ,',''' ,ValueJSON ,'''' ,',''' ,Category ,'''' ,',' ,ReadOnlySystemPropertyBool ,'' ) AS Command FROM system.tSettings WHERE SettingID = @SettingID; -- final GO INSERT INTO #tExport VALUES (9999, 1, N'GO'); -- Data Transaction SELECT Command FROM #tExport ORDER BY MainOrderNumber ,SubOrderNumber 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'spEXPORT_Setting' -- 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 an Setting in OCT.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- optional SET parameter documentation (only for Core / Standardmodules) SET @level2name = N'@SettingID'; SET @value = N'ID of the Setting to be exported.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS system.spGET_DBInfo; GO /* Procedure to get basic informations about one Database. Informations can be requested - from OCTService for an User - from OCTService for himself (passing SQL as Clientusername means check the Current SQL User) TODO: - resolve Rolemembership when assigned over Security Groups - check AccessRight (implicit sysadmin) over AzureAD SQL Server Administrator Group Membership Infolevel - 0 = information it clientuser can access this cluster or not - 1 = all the Service needs to display a tile - access - tileimage - number of users - database version - 2 = full Informations - user role - database size Gerd Tautenhahn for Saxess Software GmbH Last modified: 02/2023 for OCT 5.9 Testcall Procedure -- Service for an user DECLARE @RC INT; EXEC @RC = system.spGET_DBInfo @Username = 'SQL' ,@ClientUser = 'gerd.tautenhahn@saxess-software.com' ,@InfoLevel = 2; PRINT @RC; -- Service for Service DECLARE @RC INT; EXEC @RC = system.spGET_DBInfo @Username = 'SQL' ,@ClientUser = 'SQL' ,@InfoLevel = 2; PRINT @RC; SELECT TOP 3 * FROM system.tAPI_Log; Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spGET_DBInfo',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spGET_DBInfo','PARAMETER',NULL) */ CREATE PROCEDURE system.spGET_DBInfo @Username NVARCHAR(255) ,@ClientUser NVARCHAR(255) ,@InfoLevel INT = 1 AS BEGIN -- SET NOCOUNT ON; -- activate this, if you read the resultset with an external source -- 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(@ClientUser ,N'NULL') + N'''' ,@EffectedRows INT = 0 ,@ResultCode INT = 501 ,@TimestampCall DATETIME = GETUTCDATE() ,@Comment NVARCHAR(2000) = N'' ,@TransactUsername NVARCHAR(255) = N'' ,@StartTime DATETIME2 = SysUTCDateTime() ,@IsOCTService BIT = 0; -- NULL Protection for all mandatory Input parameters IF @Username IS NULL SET @Username = N''; IF @ClientUser IS NULL SET @ClientUser = N''; -- The string SQL means the octservice asks for himself IF @ClientUser = 'SQL' SELECT @ClientUser = CURRENT_USER; -- !! Clientuser will never exist in OCTUsermanagement -- !! Clientuser may be 'dbo' or an AzureActive Directory Identity -- passing SQL as Clientusername means check the Current SQL User IF EXISTS ( SELECT DP1.[name] FROM sys.database_role_members DRM RIGHT OUTER JOIN sys.database_principals DP1 ON DRM.role_principal_id = DP1.principal_id LEFT OUTER JOIN sys.database_principals DP2 ON DRM.member_principal_id = DP2.principal_id WHERE DP1.[type] = 'R' AND DP2.[name] = @ClientUser AND DP1.[name] IN ('OCTService','db_owner') ) SET @IsOCTService = 1; -- is OCTService or can act like OCTService due to db_owner status DECLARE @ClientUserAccess_Flag INT = 0; -- START TRY *********************************************************************************** BEGIN TRY ---- check transaction user existence SELECT @TransactUsername = dbo.sx_pf_Determine_TransactionUsername (@Username); IF @TransactUsername = N'403' BEGIN -- nothing, to enable Footprint Protection PRINT 'Unknown user - eliminate footprint' -- SET @ResultCode = 403; -- RAISERROR('Transaction user don`t exists', 16, 10); END; -- Caching table DROP TABLE IF EXISTS #tInfoCache; CREATE TABLE #tInfoCache ( RowKey BIGINT IDENTITY (1,1) ,PropertyID NVARCHAR(50) COLLATE DATABASE_DEFAULT NOT NULL ,PropertyName NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,InfoLevel INT NOT NULL ,ValueInt INT NOT NULL ,ValueMoney MONEY NOT NULL ,ValueText NVARCHAR(4000) COLLATE DATABASE_DEFAULT NOT NULL ,ValueDatetime DATETIME NULL ,ValueBinary VARBINARY(MAX) NULL --max 8000 Byte to avoid Varbinary(MAX) ,PRIMARY KEY CLUSTERED (RowKey) ); -- INFO LEVEL 0 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ -- check AccessRight for ClientUser IF @IsOCTService = 1 BEGIN SET @ClientUserAccess_Flag = 1; END ELSE BEGIN SELECT @ClientUserAccess_Flag = COUNT(UserKey) FROM system.trUser WHERE Username = @ClientUser AND Status = 'Active'; END -- check if database has public user IF @ClientUserAccess_Flag = 0 AND EXISTS(SELECT 1 FROM system.trUser WHERE Username = 'public' AND Status = 'Active') BEGIN SET @ClientUserAccess_Flag = 1 END INSERT INTO #tInfoCache SELECT 'CU01' AS PropertyID ,'Flag for ClientUser is an active User' AS PropertyName ,0 AS InfoLevel ,@ClientUserAccess_Flag AS ValueInt ,0 AS ValueMoney ,'' AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary; -- Footprint protection IF @ClientUserAccess_Flag = 0 BEGIN SET @ParameterString = REPLACE (@ParameterString,@ClientUser,''); SET @Username = ''; END -- INFO LEVEL 1 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ -- Only if ClientUser has Access, further Informations are delivered (valid for all Info Levels) IF @ClientUserAccess_Flag = 1 BEGIN -- Clustername INSERT INTO #tInfoCache SELECT 'C01' AS PropertyID ,'ClusterName' AS PropertyName ,1 AS InfoLevel ,0 AS ValueInt ,0 AS ValueMoney ,'' AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary; UPDATE #tInfoCache SET ValueText = S.ValueText FROM system.tSettings S WHERE S.SettingID = 'ClusterName' AND #tInfoCache.PropertyID = 'C01'; -- Clusterdescription INSERT INTO #tInfoCache SELECT 'C02' AS PropertyID ,'ClusterDescription' AS PropertyName ,1 AS InfoLevel ,0 AS ValueInt ,0 AS ValueMoney ,'' AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary; UPDATE #tInfoCache SET ValueText = S.ValueText FROM system.tSettings S WHERE S.SettingID = 'ClusterDescription' AND #tInfoCache.PropertyID = 'C02'; -- Clusterversion INSERT INTO #tInfoCache SELECT 'C03' AS PropertyID ,'OCT.core Database Version' AS PropertyName ,1 AS InfoLevel ,0 AS ValueInt ,0 AS ValueMoney ,ValueText AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary FROM system.tSettings WHERE SettingID = 'DBVersion'; -- Cluster_Tile_Logo INSERT INTO #tInfoCache SELECT 'C04' AS PropertyID ,'Cluster_Tile_Logo' AS PropertyName ,1 AS InfoLevel ,0 AS ValueInt ,0 AS ValueMoney ,'' AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary; UPDATE #tInfoCache SET ValueBinary = FileBody FROM planning.tFiles WHERE FileID = 'Cluster_Tile_Logo' AND #tInfoCache.PropertyID = 'C04'; -- Maintenance Mode -> this property exists only after first saving of database properties INSERT INTO #tInfoCache SELECT 'C05' AS PropertyID ,'Cluster is in Maintenance Mode Flag' AS PropertyName ,1 AS InfoLevel ,0 AS ValueInt ,0 AS ValueMoney ,'' AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary; UPDATE #tInfoCache SET ValueInt = S.ValueInt FROM system.tSettings S WHERE S.SettingID = 'MaintenanceMode' AND #tInfoCache.PropertyID = 'C05'; -- Scheduler Owner -> this property exists only after first saving of database properties INSERT INTO #tInfoCache SELECT 'C06' AS PropertyID ,'IntegrationPreferencesSchedulerOwnerServer' AS PropertyName ,1 AS InfoLevel ,0 AS ValueInt ,0 AS ValueMoney ,'' AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary; UPDATE #tInfoCache SET ValueText = S.ValueText FROM system.tSettings S WHERE S.SettingID = 'IntegrationPreferencesSchedulerOwnerServer' AND #tInfoCache.PropertyID = 'C06'; -- Usercount for Licence information INSERT INTO #tInfoCache SELECT 'C10' AS PropertyID ,'Number of active Users' AS PropertyName ,1 AS InfoLevel ,COUNT(UserKey) AS ValueInt ,0 AS ValueMoney ,'' AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary FROM system.trUser WHERE Status = 'Active'; -- Information, if the active User is an OCT Clusteradmin INSERT INTO #tInfoCache SELECT 'CU01' AS PropertyID ,'User is OCT Administrator' AS PropertyName ,1 AS InfoLevel ,COUNT(UserKey) AS ValueInt ,0 AS ValueMoney ,'' AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary FROM system.trUser WHERE Username = @ClientUser AND IsAdministratorFlag = 1; END -- INFO LEVEL 2 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ -- Informations for higher Info Levels IF @ClientUserAccess_Flag = 1 AND @InfoLevel >= 2 BEGIN -- Databaseroles of the Clientuser -- Works only if the Clientuser is not the dbo -- returns no Serverroles -- returns no roles if the dabaserole is inherited from a serverrole (e.g. somebody has db_owner role due to sysadmin login etc.) INSERT INTO #tInfoCache SELECT 'CU10' AS PropertyID ,'Database Roles of the Clientuser' AS PropertyName ,2 AS InfoLevel ,0 AS ValueInt ,0 AS ValueMoney ,COALESCE( STUFF( -- Stuffs all names in one list ( SELECT DP1.[name] +', ' FROM sys.database_role_members DRM RIGHT OUTER JOIN sys.database_principals DP1 ON DRM.role_principal_id = DP1.principal_id LEFT OUTER JOIN sys.database_principals DP2 ON DRM.member_principal_id = DP2.principal_id WHERE DP1.[type] = 'R' AND DP2.[name] = @ClientUser FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,0,'' ) , 'No direct assigned Databaseroles (Serveroles,dbo status, groupmemberships or admin status over AAD may be given !)' ) AS ValueText --Databaserolename ,NULL AS ValueDatetime ,NULL AS ValueBinary; -- dbo determination - its different on Azure and on-prem IF CONVERT(NVARCHAR(255),SERVERPROPERTY ('Edition')) LIKE '%Azure%' BEGIN INSERT INTO #tInfoCache SELECT 'DB01' AS PropertyID ,'Name or sid of dbo' AS PropertyName ,2 AS InfoLevel ,0 AS ValueInt ,0 AS ValueMoney ,CONVERT(NVARCHAR(255),sid,2) AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary FROM sys.sysusers where name = 'dbo' END ELSE -- on prem BEGIN INSERT INTO #tInfoCache SELECT 'DB01' AS PropertyID ,'Name of dbo' AS PropertyName ,2 AS InfoLevel ,0 AS ValueInt ,0 AS ValueMoney ,SUSER_SNAME(owner_sid) AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary FROM sys.databases WHERE name = DB_NAME(); END -- Database Rows Size INSERT INTO #tInfoCache SELECT 'DB02' AS PropertyID ,'Database Size (Rows) in GB' AS PropertyName ,2 AS InfoLevel ,0 AS ValueInt ,SUM(CAST(size AS MONEY) * 8/1024/1024) AS ValueMoney ,'' AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary FROM sys.database_files WHERE type = 0; -- Database Log Size INSERT INTO #tInfoCache SELECT 'DB03' AS PropertyID ,'Database Size (Log) in GB' AS PropertyName ,2 AS InfoLevel ,0 AS ValueInt ,SUM(CAST(size AS MONEY) * 8/1024/1024) AS ValueMoney ,'' AS ValueText ,NULL AS ValueDatetime ,NULL AS ValueBinary FROM sys.database_files WHERE type = 1; -- Options to To: -- Last Database Read Access -- Last Database Write Access -- Number of scheduled Pipelines END -- OUTPUT SELECT * FROM #tInfoCache WHERE InfoLevel <= @InfoLevel ORDER BY InfoLevel, PropertyID; 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_DBInfo' -- 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 -- 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 documententation ************************************************************************* -- SET Procedure documentation SET @value = N'Procedure to get a collection of database informations. Pass the Clientusername SQL to request the rights of the service.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- optional SET parameter documentation (only for Core / Standardmodules) SET @level2name = N'@ClientUser'; SET @value = N'Not logged if its not a clustermember to protect from footprinting access scans.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@InfoLevel'; SET @value = N'Control the amount of informations deliverd. 1 = Basic Info, 2 = extended Info.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS system.spGET_PipelineSchedules; GO /* Procedure to read one or all schedules of a pipeline or of the whole database Saxess Software GmbH Last modified: 09/2024 for OCT 5.10 Testcall Procedure DECLARE @RC INT; EXEC @RC = system.spGET_PipelineSchedules @Username = 'SQL' ,@PipelineID = '' ,@ScheduleID = '' PRINT @RC Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spGET_PipelineSchedules',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spGET_PipelineSchedules','PARAMETER',NULL) */ CREATE PROCEDURE system.spGET_PipelineSchedules ( @Username NVARCHAR(255) ,@PipelineID NVARCHAR(50) = '' ,@ScheduleID NVARCHAR(50) = '' ) 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(@PipelineID ,N'NULL') + N''',''' + ISNULL(@ScheduleID ,N'NULL') + N'''' ,@EffectedRows INT = 0 ,@ResultCode INT = 501 ,@TimestampCall DATETIME = GETUTCDATE() ,@Comment NVARCHAR(2000) = N'' ,@TransactUsername NVARCHAR(255) = N'' ,@StartTime DATETIME2 = SysUTCDateTime() ,@PipelineKey BIGINT; -- NULL Protection for all Input parameters IF @Username IS NULL SET @Username = N''; IF @PipelineID IS NULL SET @PipelineID = N''; IF @ScheduleID IS NULL SET @ScheduleID = 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; -- check existence of IDs IF @PipelineID <> '' AND NOT EXISTS ( SELECT PipelineID FROM system.tPipelines WHERE PipelineID = @PipelineID ) BEGIN SET @ResultCode = 404; RAISERROR('Source Pipeline don`t exits', 16, 10); END SELECT @PipelineKey = PipelineKey FROM system.tPipelines WHERE PipelineID = @PipelineID; IF @ScheduleID <> '' AND NOT EXISTS ( SELECT ScheduleID FROM system.tPipelineSchedules WHERE PipelineKey = @PipelineKey AND ScheduleID = @ScheduleID ) BEGIN SET @ResultCode = 404; RAISERROR('Schedule don`t exits', 16, 10); END -- Data Transaction SELECT P.PipelineID ,PS.ScheduleID ,PS.ScheduleName ,PS.ScheduleDescription ,PS.ScheduleDetailsJSON ,PS.Active FROM system.tPipelineSchedules PS LEFT JOIN system.tPipelines P ON PS.PipelineKey = P.PipelineKey WHERE (PS.PipelineKey = @PipelineKey OR @PipelineID = '') AND (PS.ScheduleID = @ScheduleID OR @ScheduleID = '') ORDER BY P.PipelineID ,PS.ScheduleID; 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_PipelineSchedules' -- 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 returns one or all schedules of a given pipeline.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- optional SET parameter documentation (only for Core / Standardmodules) SET @level2name = N'@PipelineID'; SET @value = N'ID of the Pipeline, which schedules are to read - use '' to read all schedules of this database.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ScheduleID'; SET @value = N'ID of the schedule to read - use '' to read all schedules of this pipeline.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS system.spGET_UserRightInformation; GO /* Procedure to get right informations for an User Gerd Tautenhahn for Saxess Software GmbH Last modified: 10/2022 for OCT 5.8 Testcall Procedure DECLARE @RC INT; EXEC @RC = system.spGET_UserRightInformation @Username = 'SQL' ,@UsernameRequestedUser = 'W10\admin' ,@RightAreaCODE = '' ,@FactoryID = '' ,@ProductLineID = '' ,@PipelineID = '' ,@FullStructureBOOL = 0 PRINT @RC Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spGET_UserRightInformation',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spGET_UserRightInformation','PARAMETER',NULL) */ CREATE PROCEDURE system.spGET_UserRightInformation ( @Username NVARCHAR(255) ,@UsernameRequestedUser NVARCHAR(255) ,@RightAreaCODE NVARCHAR(50) = '' --DATAENTRY, DATAFLOWS ,@FactoryID NVARCHAR(50) = '' ,@ProductLineID NVARCHAR(50) = '' ,@PipelineID NVARCHAR(50) = '' ,@FullStructureBOOL BIT = 0 ) AS BEGIN -- SET NOCOUNT ON; -- activate this, if you read the resultset with an external source -- 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(@UsernameRequestedUser ,N'NULL') + N''',''' + ISNULL(@RightAreaCODE ,N'NULL') + N''',''' + ISNULL(@FactoryID ,N'NULL') + N''',''' + ISNULL(@ProductLineID ,N'NULL') + N''',''' + ISNULL(@PipelineID ,N'NULL') + N''',' + ISNULL(CAST(@FullStructureBOOL AS NCHAR(1)) ,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 @FactoryKey INT = 0 -- ,@ProductLineKey INT = 0 -- ,@ProductKey INT = 0; -- NULL Protection for all Input parameters IF @Username IS NULL SET @Username = N''; IF @UsernameRequestedUser IS NULL SET @UsernameRequestedUser = N''; IF @RightAreaCODE IS NULL SET @RightAreaCODE = N''; IF @FactoryID IS NULL SET @FactoryID = N''; IF @ProductLineID IS NULL SET @ProductLineID = N''; IF @PipelineID IS NULL SET @PipelineID = N''; IF @FullStructureBOOL IS NULL SET @FullStructureBOOL = 0; -- 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; -- if RequestedUser doesn't exist we can use fallback to public user IF NOT EXISTS(SELECT 1 FROM system.trUser WHERE Username = @UsernameRequestedUser AND Status = 'Active') AND EXISTS(SELECT 1 FROM system.trUser WHERE Username = 'public' AND Status = 'Active') BEGIN SET @UsernameRequestedUser = 'public' END DROP TABLE IF EXISTS #tRightInformation; CREATE TABLE #tRightInformation ( RowKey BIGINT IDENTITY (1,1) ,RightAreaCode NVARCHAR(50) COLLATE DATABASE_DEFAULT NOT NULL ,FactoryID NVARCHAR(50) COLLATE DATABASE_DEFAULT NOT NULL ,ProductLineID NVARCHAR(50) COLLATE DATABASE_DEFAULT NOT NULL ,PipelineID NVARCHAR(50) COLLATE DATABASE_DEFAULT NOT NULL ,UserRight NVARCHAR(50) COLLATE DATABASE_DEFAULT NOT NULL ,ReadCommentMandatory BIT NOT NULL ,WriteCommentMandatory BIT NOT NULL ,RightDescription NVARCHAR(2000) COLLATE DATABASE_DEFAULT NOT NULL ,PRIMARY KEY CLUSTERED (RowKey) ); -- Rights for DataEntry IF @RightAreaCode = 'DATAENTRY' OR @RightAreaCode = '' BEGIN -- For full structure requested IF @FullStructureBOOL = 1 BEGIN INSERT INTO #tRightInformation SELECT 'DATAENTRY' AS RightAreaCode ,FactoryID ,ProductLineID ,'' AS PipelineID ,[Right] AS UserRight ,ReadCommentMandatory ,WriteCommentMandatory ,'' AS RightDescription FROM system.trUserRights WHERE Username = @UsernameRequestedUser AND (FactoryID = @FactoryID OR @FactoryID='' ) AND (ProductLineID = @ProductLineID OR @ProductLineID = ''); END -- For single line IF @FullStructureBOOL = 0 BEGIN INSERT INTO #tRightInformation SELECT 'DATAENTRY' AS RightAreaCode ,FactoryID ,ProductLineID ,'' AS PipelineID ,[Right] AS UserRight ,ReadCommentMandatory ,WriteCommentMandatory ,'' AS RightDescription FROM system.trUserRights WHERE Username = @UsernameRequestedUser AND (FactoryID = @FactoryID) AND (ProductLineID = @ProductLineID); END END -- Rights for DataFlows IF @RightAreaCode = 'DATAFLOWS' OR @RightAreaCode = '' BEGIN -- One Query for full structure and single object INSERT INTO #tRightInformation SELECT 'DATAFLOWS' AS RightAreaCode ,'' AS FactoryID ,'' AS ProductLineID ,PipelineID AS PipelineID ,'EXECUTE' AS UserRight ,0 AS ReadCommentMandatory ,0 AS WriteCommentMandatory ,'temporary' AS RightDescription FROM system.tPipelines WHERE -- Username = @UsernameRequestedUser -- AND (PipelineID = @PipelineID OR @PipelineID='') END -- OUTPUT SELECT * FROM #tRightInformation; 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_UserRightInformation' -- 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 right informations for an User - returns the Right as String - Read, Write, EXECUTE,DESIGN'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- optional SET parameter documentation (only for Core / Standardmodules) SET @level2name = N'@Username'; SET @value = N'Standardparameter, executing User - use "SQL"'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@UsernameRequestedUser'; SET @value = N'Name of the user for which rights are requested'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@RightAreaCODE'; SET @value = N'CODE to specify the Rightarea, use DATAFLOWS or DATAENTRY, keep empty to get all'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@FactoryID'; SET @value = N'FactoryID in case of requsting Rights for DATAENTRY, keep empty to get all'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ProductLineID'; SET @value = N'ProductLineID in case of requsting Rights for DATAENTRY, keep empty to get all'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name SET @level2name = N'@PipelineID'; SET @value = N'PipelineID in case of requsting Rights for DATAFLOWS, keep empty to get all'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name SET @level2name = N'@FullStructureBOOL'; SET @value = N'1 or 0 to flag if the full structure under the node (downwards) is included into the output'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name GO DROP PROCEDURE IF EXISTS system.spPOST_PipelineSchedule; GO /* Gerd Tautenhahn for Saxess Software GmbH Last modified: 09/2024 for OCT 5.10 Testcall Procedure DECLARE @RC INT ,@ReturnedScheduleID NVARCHAR(50); EXEC @RC = system.spPOST_PipelineSchedule @Username = 'SQL' ,@PipelineID = 'P1' ,@ScheduleID = '*' ,@ScheduleName = 'Hallo' ,@ScheduleDescription = 'Desc' ,@ScheduleDetailsJSON = '{}' ,@Active = 1 ,@OutputID = @ReturnedScheduleID OUTPUT; PRINT @RC PRINT 'ScheduleID OUT: ' + @ReturnedScheduleID; SELECT * FROM system.tPipelineSchedules; SELECT * FROM system.tPipelines; Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spPOST_PipelineSchedule',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spPOST_PipelineSchedule','PARAMETER',NULL) */ CREATE PROCEDURE system.spPOST_PipelineSchedule ( @Username NVARCHAR(255) ,@PipelineID NVARCHAR(50) ,@ScheduleID NVARCHAR(50) ,@ScheduleName NVARCHAR(255) = NULL ,@ScheduleDescription NVARCHAR(4000) = NULL ,@ScheduleDetailsJSON NVARCHAR(MAX) = NULL ,@Active BIT = NULL ,@OutputID NVARCHAR(50) = NULL OUTPUT ) AS BEGIN -- Standard declaration for logging DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID) ,@ParameterString NVARCHAR(MAX) = N'''' --set Strings 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 + ISNULL(@Username ,N'NULL') + N''',''' + ISNULL(@PipelineID ,N'NULL') + N''',''' + ISNULL(@ScheduleID ,N'NULL') + N''',''' + ISNULL(@ScheduleName ,N'NULL') + N''',''' + ISNULL(@ScheduleDescription ,N'NULL') + N''',''' + ISNULL(@ScheduleDetailsJSON ,N'NULL') + N''',' + ISNULL(CAST(@Active AS NCHAR(1)) ,N'NULL') + N'' ,@EffectedRows INT = 0 ,@ResultCode INT = 501 ,@TimestampCall DATETIME = GETUTCDATE() ,@Comment NVARCHAR(2000) = N'' ,@TransactUsername NVARCHAR(255) = N'' ,@StartTime DATETIME2 = SysUTCDateTime() ,@PipelineKey BIGINT = 0 ,@RowCount INT = 0 ,@LastSchedule INT = 0; -- NULL Protection for all Input parameters IF @Username IS NULL SET @Username = N''; IF @ScheduleID IS NULL SET @ScheduleID = N''; IF @PipelineID IS NULL SET @PipelineID = N''; -- Content Protection for specific input parameters (e.g.IDs) SET @ScheduleID = dbo.sx_pf_pProtectID (@ScheduleID); -- START TRANSACTION *********************************************************************************** BEGIN TRY BEGIN TRANSACTION spPOST_PipelineSchedule -- 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; -- Check Existence of Pipeline IF NOT EXISTS ( SELECT PipelineID FROM system.tPipelines WHERE PipelineID = @PipelineID ) BEGIN SET @ResultCode = 404; RAISERROR('Pipeline don`t exits', 16, 10); END SELECT @PipelineKey = PipelineKey FROM system.tPipelines WHERE PipelineID = @PipelineID; -- Cache DROP TABLE IF EXISTS #CurrentSchedule; CREATE TABLE #CurrentSchedule ( ScheduleKey BIGINT NOT NULL ,ScheduleID NVARCHAR(50) COLLATE DATABASE_DEFAULT NOT NULL ,ScheduleName NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,ScheduleDescription NVARCHAR(4000) COLLATE DATABASE_DEFAULT NOT NULL ,ScheduleDetailsJSON NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ,Active BIT NOT NULL ) INSERT INTO #CurrentSchedule SELECT ScheduleKey ,ScheduleID ,ScheduleName ,ScheduleDescription ,ScheduleDetailsJSON ,Active FROM system.tPipelineSchedules WHERE PipelineKey = @PipelineKey AND ScheduleID = @ScheduleID -- Data transaction SELECT @RowCount = Count (*) FROM #CurrentSchedule; IF @RowCount = 0 BEGIN --- determine next free ID if * is sended as ID, AutoID is generated as S1..Sn per Pipeline IF @ScheduleID = '*' BEGIN SELECT TOP 1 @LastSchedule = TRY_CAST(RIGHT(ScheduleID,LEN(ScheduleID)-1) AS INT ) FROM system.tPipelineSchedules WHERE ScheduleID LIKE 'J%' AND LEN(ScheduleID) > 1 AND PipelineKey = @PipelineKey AND TRY_CAST(RIGHT(ScheduleID,LEN(ScheduleID)-1) AS INT) IS NOT NULL ORDER BY TRY_CAST(RIGHT(ScheduleID,LEN(ScheduleID)-1) AS INT) DESC IF @LastSchedule IS NULL SET @LastSchedule = 0; SET @ScheduleID = 'J' + CAST(@LastSchedule + 1 AS NVARCHAR(50)); END -- INSERT with default values if NULL INSERT INTO system.tPipelineSchedules VALUES ( @PipelineKey ,@ScheduleID ,ISNULL(@ScheduleName,'') ,ISNULL(@ScheduleDescription,'') ,ISNULL(@ScheduleDetailsJSON,'') ,ISNULL(@Active,1) ) SET @EffectedRows = @@ROWCOUNT; SET @ResultCode = 201; END ELSE BEGIN -- UPDATE as Insert / delete not possible due to primary key constraints UPDATE tPS SET ScheduleName = ISNULL(@ScheduleName ,cS.ScheduleName) ,ScheduleDescription = ISNULL(@ScheduleDescription ,cS.ScheduleDescription) ,ScheduleDetailsJSON = ISNULL(@ScheduleDetailsJSON ,cS.ScheduleDetailsJSON) ,Active = ISNULL(@Active ,cS.Active) FROM system.tPipelineSchedules tPS LEFT JOIN #CurrentSchedule cS ON tPS.ScheduleKey = cS.ScheduleKey WHERE tPS.ScheduleKey = cS.ScheduleKey; SET @ResultCode = 200; SET @EffectedRows = @@ROWCOUNT; END -- Output SET @OutputID = @ScheduleID; COMMIT TRANSACTION spPOST_PipelineSchedule END TRY -- START CATCH *********************************************************************************** BEGIN CATCH DECLARE @Error_state INT = ERROR_STATE(); SET @Comment = ERROR_MESSAGE(); ROLLBACK TRANSACTION spPOST_PipelineSchedule 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 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'spPOST_PipelineSchedule' -- 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 call demo values.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- optional SET parameter documentation (only for Core / Standardmodules) SET @level2name = N'@PipelineID'; SET @value = N'ID of the Pipeline which contains this schedule.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ScheduleID'; SET @value = N'ID of the Schedule - POST * to create a new autogenerated ID.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ScheduleName'; SET @value = N'optional - name for the schedule.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ScheduleDescription'; SET @value = N'optional - description for the schedule.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ScheduleDetailsJSON'; SET @value = N'optional - definition of the schedule.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@Active'; SET @value = N'optional - Flag to set schedule to active / inactive.'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO UPDATE system.tSettings SET ValueText = '5.10.21', ValueInt = 51021 WHERE SettingID = 'DBVersion'