-- This file is part of the it-crafts software and is subject to copyright protection.
-- (C) 2022..2025, All rights reserved by Steffen Anders, Elsteraue - Germany
-- Visit also https://www.it-crafts.de.
--
-- -------------------------------------------------------------------------------------------------
-- Blob-Felder zur Tabelle ut_TableSnipped hinzufuegen
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_TableSnipped' and sc.[name] = 'Data')
BEGIN
  ALTER TABLE [dbo].[ut_TableSnipped] ADD [Data] varbinary(max) DEFAULT NULL
END

-------------------------------------------------------------------------------------------------------
-- Tabellenausschnitt ut_TableSnippet bereitstellen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_CreateDataSnippet')
  DROP PROCEDURE [dbo].[itcproc_CreateDataSnippet]
GO
CREATE PROCEDURE [dbo].[itcproc_CreateDataSnippet] @mGuid varchar(40), @ObjectName varchar(50), @ObjectType varchar(10), @m_Where varchar(128), @m_OrderBy varchar(128) = 'RecId'
AS
  -- Spaltenbeschreibung abrufen und als XML-Daten bereitstellen
  DECLARE @m_XmlColumns varchar(max) = CONVERT(varchar(max), (
	  select c.name as [Name], t.name as [Typ], c.length AS [Len], c.xprec AS [Precis], c.xscale AS [Scale]
	    from syscolumns c inner join dbo.sysobjects o on c.id = o.id inner join dbo.systypes t on t.xtype = c.xtype
	    where o.xtype = @ObjectType and o.name = @ObjectName and c.name <> 'MandId' order by c.colid
   FOR XML RAW))
  --PRINT @m_XmlColumns
  --PRINT ''

  -- Feldliste fuer Datenabfrage erstellen
  DECLARE @m_FieldList varchar(max) = ''
  DECLARE @m_cname varchar(128)
  DECLARE @m_tname varchar(128)
  DECLARE C CURSOR LOCAL FOR
	  select c.name, t.name	
	    from syscolumns c inner join dbo.sysobjects o on c.id = o.id inner join dbo.systypes t on t.xtype = c.xtype
	    where o.xtype = @ObjectType and o.name = @ObjectName and c.name <> 'MandId' order by c.colid
  OPEN C
  FETCH NEXT FROM C INTO @m_cname, @m_tname
  WHILE @@FETCH_STATUS = 0 BEGIN
    IF @m_FieldList <> ''
	  SET @m_FieldList = @m_FieldList + ', '
    IF @m_tname = 'char'
	  SET @m_FieldList = @m_FieldList + 'dbo.itcfunc_CleanString(RTRIM(' + @m_cname + ')) AS ' + @m_cname
    ELSE IF @m_tname = 'varchar'
	  SET @m_FieldList = @m_FieldList + 'dbo.itcfunc_CleanString(' + @m_cname + ') AS ' + @m_cname
    ELSE IF @m_tname = 'float'
	  SET @m_FieldList = @m_FieldList + 'dbo.itcfunc_floatToString(' + @m_cname + ') AS ' + @m_cname
	ELSE
	  SET @m_FieldList = @m_FieldList + @m_cname
    FETCH NEXT FROM C INTO @m_cname, @m_tname
  END
  CLOSE C
  DEALLOCATE C
  --PRINT @m_FieldList
  --PRINT ''

  -- Datenabfrage erstellen
  DECLARE @m_Stmt varchar(max) = 'SELECT ' + @m_FieldList + ' FROM dbo.' + @ObjectName + ' WHERE ' + @m_Where + ' ORDER BY ' + @m_OrderBy
  DECLARE @m_StmtN nvarchar(max) = N'SELECT @m_XmlRows = CONVERT(varchar(max), (' + convert(nvarchar(max), @m_Stmt) + N' FOR XML RAW))'
  -- Datenabfrage ausfuehren
  DECLARE @m_XmlRows varchar(max)
  EXEC sp_executesql @m_StmtN, N'@m_XmlRows varchar(max) OUTPUT', @m_XmlRows = @m_XmlRows OUTPUT

  -- Daten abspeichern
  DECLARE @m_Data varchar(max) = null
  DECLARE @m_DataLen integer = 0
  IF NOT @m_XmlRows IS NULL BEGIN 
	SET @m_Data = '<Snippet ObjectName="' + @ObjectName + '"><Columns>' + @m_XmlColumns + '</Columns><Rows>' + @m_XmlRows + '</Rows></Snippet>'
	SET @m_DataLen = LEN(@m_Data)
  END
  BEGIN TRANSACTION
  DECLARE @m_RecId integer = (SELECT ISNULL(MAX(RecId),0) FROM [dbo].[ut_TableSnipped] WITH (TABLOCK, HOLDLOCK)) + 1
  INSERT [dbo].[ut_TableSnipped] (RecId, [SnippedId], [TableName], [CurrTime], [DataLen], [Data]) 
    VALUES(@m_RecId, @mGuid, @ObjectName, CONVERT(varchar(16),GETDATE(),120), @m_DataLen, COMPRESS(@m_Data))
  COMMIT TRANSACTION
GO

-------------------------------------------------------------------------------------------------------
-- Belegtransaktion beginnen (Sicherungssatz erstellen)
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'itcproc_CreateBelegSnippet')
  DROP PROCEDURE [dbo].[itcproc_CreateBelegSnippet]
GO
CREATE PROCEDURE [dbo].[itcproc_CreateBelegSnippet]	@m_PrjNr varchar(8), @m_BelArt varchar(2),	@m_BelNr varchar(8)
AS
  SET NOCOUNT ON
  DECLARE @mGuid varchar(40) = NEWID()
  DECLARE @m_WhereKopf varchar(128) = 'PRJNR = ''' + @m_PrjNr + ''' AND BELEGART = ''' + @m_BelArt + ''' AND BELEGNR = ''' + @m_BelNr + ''''
  DECLARE @m_Where varchar(128) = 'PrjNr = ''' + @m_PrjNr + ''' AND BelArt = ''' + @m_BelArt + ''' AND BelNr = ''' + @m_BelNr + ''''

  EXECUTE [dbo].[itcproc_CreateDataSnippet] @mGuid, 'Belege', 'V', @m_WhereKopf
  EXECUTE [dbo].[itcproc_CreateDataSnippet] @mGuid, 'BelKopfExt', 'V', @m_Where
  EXECUTE [dbo].[itcproc_CreateDataSnippet] @mGuid, 'BelAdresse', 'V', @m_Where
  EXECUTE [dbo].[itcproc_CreateDataSnippet] @mGuid, 'BelAufmasse', 'V', @m_Where
  EXECUTE [dbo].[itcproc_CreateDataSnippet] @mGuid, 'BelBauabschnitte', 'V', @m_Where
  EXECUTE [dbo].[itcproc_CreateDataSnippet] @mGuid, 'BelRaum', 'V', @m_Where
  EXECUTE [dbo].[itcproc_CreateDataSnippet] @mGuid, 'BelPos', 'V', @m_Where
  EXECUTE [dbo].[itcproc_CreateDataSnippet] @mGuid, 'BelPosDimension', 'V', @m_Where
  EXECUTE [dbo].[itcproc_CreateDataSnippet] @mGuid, 'BelNe', 'V', @m_Where
  EXECUTE [dbo].[itcproc_CreateDataSnippet] @mGuid, 'BelMassRef', 'V', @m_Where
  EXECUTE [dbo].[itcproc_CreateDataSnippet] @mGuid, 'BelMassPosRef', 'V', @m_Where
  EXECUTE [dbo].[itcproc_CreateDataSnippet] @mGuid, 'BelText', 'V', @m_Where
  EXECUTE [dbo].[itcproc_CreateDataSnippet] @mGuid, 'BelSummenSpeicher', 'V', @m_Where
  EXECUTE [dbo].[itcproc_CreateDataSnippet] @mGuid, 'BelSummenSpeicherRefs', 'V', @m_Where
  EXECUTE [dbo].[itcproc_CreateDataSnippet] @mGuid, 'KGrpSummenWerte', 'V', @m_Where
  EXECUTE [dbo].[itcproc_CreateDataSnippet] @mGuid, 'BelKalkGruppen', 'V', @m_Where
  EXECUTE [dbo].[itcproc_CreateDataSnippet] @mGuid, 'BelegMwstKennzeichen', 'V', @m_Where
  EXECUTE [dbo].[itcproc_CreateDataSnippet] @mGuid, 'BelegMwstSummen', 'V', @m_Where
  EXECUTE [dbo].[itcproc_CreateDataSnippet] @mGuid, 'BelegAnhang', 'V', @m_Where
	
  SELECT @mGuid AS SnippetId
GO

--SELECT * FROM 

--EXECUTE [dbo].[itcproc_CreateBelegSnippet] 'STOELZER', ' 8', '  983416'
-- 5E48B8F1-563C-42D7-AC30-9CD716ECADDA
--SELECT RecId, [TableName], [CurrTime], [DataLen], convert(varchar(max), DECOMPRESS([Data])) FROM [dbo].[ut_TableSnipped] 
--WHERE [SnippedId] = '70EE26B7-6902-4A71-BB65-D56111CEBB13' ORDER BY RecId


--'8F37E9E3-A093-4673-AA2C-C17E3F3BBBE7'
--EXECUTE [dbo].[itcproc_CreateBelegSnippet] ' TAGLOHN', ' 1', '20233254'

