-- This file is part of the it-crafts software and is subject to copyright protection.
-- (C) 2023, All rights reserved by Steffen Anders, Elsteraue - Germany
-- Visit also https://www.it-crafts.de.
--
-- Ab SQL-Server 2016 sollte die Compression benutzt werden
IF CONVERT(integer,SERVERPROPERTY('ProductMajorVersion')) >= 13
  RAISERROR (N'%37.37s', -- Message text.
    16, -- Severity,
    1, -- State,
    N'Nicht unterstützte SQL-Server Version'); -- First argument supplies the string.
GO

--------------------------------------------------------------------------------------------------------
-- Blobinhalt eines Wartungsanhangs abrufen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_WartungsAnhang_readBlob')
  DROP PROCEDURE [dbo].[itcproc_WartungsAnhang_readBlob]
GO
CREATE PROCEDURE [dbo].[itcproc_WartungsAnhang_readBlob]
  @Parm_RecId varchar(15)
AS
  DECLARE @m_RecId  integer = dbo.itcfunc_integer(@Parm_RecId)
  DECLARE @m_BlobId integer 

  select @m_BlobId = ISNULL(fr.BlobId,0) from ut_WartungsAnhang wa left join ut_FileRef fr on fr.FileStamp = wa.Stamp where wa.RecId = @m_RecId
  If @m_BlobId <> 0
    select BlobData from ut_Blobs where BlobId = @m_BlobId and (NOT (BlobData IS NULL)) order by PartId
GO
-- EXECUTE [dbo].[itcproc_WartungsAnhang_readBlob] 1

--------------------------------------------------------------------------------------------------------
-- Wartungsanhänge auflisten
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_WartungsAnhang_list')
  DROP PROCEDURE [dbo].[itcproc_WartungsAnhang_list]
GO
CREATE PROCEDURE [dbo].[itcproc_WartungsAnhang_list]
  @Parm_AuftragId varchar(15)
AS
  DECLARE @m_AuftragId  integer = dbo.itcfunc_integer(@Parm_AuftragId)
  select wa.RecId, wa.AuftragId, wa.PosNr, wa.LfdNr, fr.BaseName, fr.Extension 
    , CONVERT(float,ISNULL((select sum(datalength(BlobData)) from ut_Blobs bl where bl.BlobId = fr.BlobId),0)) / (1024 * 1024) as BlobSize_MB
    from ut_WartungsAnhang wa 
    left join ut_FileRef fr on fr.FileStamp = wa.Stamp
	where AuftragId = @m_AuftragId
	order by wa.AuftragId, wa.PosNr, wa.LfdNr
GO

--------------------------------------------------------------------------------------------------------
-- Wartungsanhang hinzufuegen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P' and [name] = 'itcproc_WartungsAnhang_add')
  DROP PROCEDURE [dbo].[itcproc_WartungsAnhang_add]
GO
CREATE PROCEDURE [dbo].[itcproc_WartungsAnhang_add]
  @Param_AuftragId varchar(15)
 ,@Param_PosNr varchar(15)
 ,@Param_Stamp varchar(32)
AS
  DECLARE @m_AuftragId  integer = dbo.itcfunc_integer(@Param_AuftragId)
  DECLARE @m_PosNr integer = dbo.itcfunc_integer(@Param_PosNr)
  DECLARE @m_LfdNr integer
  DECLARE @m_ObjektId integer
  DECLARE @m_AnlagenId integer
  DECLARE @m_OrgName varchar(200)
  DECLARE @m_Extension varchar(20)

  If @Param_Stamp <> '' BEGIN
    IF 0 < (SELECT count(*) FROM ut_WartungsAnhang WHERE AuftragId = @m_AuftragId AND Stamp = @Param_Stamp) BEGIN
	  SELECT 'Ein Anhang mit gleicher Pruefsumme ist bereits vorhanden' AS error
	  RETURN
	END

    select @m_ObjektId = ObjektId, @m_AnlagenId = AnlagenId from ut_WartungsAuftraege where Ident = @m_AuftragId
    select @m_LfdNr = ISNULL(max(LfdNr),0) + 1 from ut_WartungsAnhang where AuftragId = @m_AuftragId and PosNr = @m_PosNr
    select @m_OrgName = BaseName, @m_Extension = Extension from ut_FileRef where FileStamp = @Param_Stamp

    If NOT @m_OrgName IS NULL BEGIN
      EXECUTE [dbo].[up_Append_WartungsAnhang] @m_AuftragId, @m_PosNr, @m_LfdNr, @m_ObjektId, @m_AnlagenId, @Param_Stamp, @m_OrgName, @m_Extension  
	  RETURN
    END
  END

  SELECT 0 AS RecId
GO
