Cleanup unused directives and add BadRequestException

Removed unnecessary using directives in `UpdateEmailTemplateCommandHandler.cs`. Added a new `BadRequestException` class in `BadRequestException.cs` to handle bad request scenarios with customizable error messages.

EnvelopeGenerator.Application/Contracts/SQLExecutor/IDocumentExecutor.cs

@@ -0,0 +1,18 @@
+using EnvelopeGenerator.Domain.Entities;
+
+namespace EnvelopeGenerator.Application.Contracts.SQLExecutor;
+
+/// <summary>
+/// 
+/// </summary>
+public interface IDocumentExecutor
+{
+    /// <summary>
+    /// 
+    /// </summary>
+    /// <param name="base64"></param>
+    /// <param name="envelope_uuid"></param>
+    /// <param name="cancellation"></param>
+    /// <returns></returns>
+    Task<EnvelopeDocument> CreateDocumentAsync(string base64, string envelope_uuid, CancellationToken cancellation = default);
+}

EnvelopeGenerator.Application/Contracts/SQLExecutor/IEnvelopeExecutor.cs

@@ -0,0 +1,21 @@
+using Dapper;
+using EnvelopeGenerator.Domain.Entities;
+
+namespace EnvelopeGenerator.Application.Contracts.SQLExecutor;
+
+/// <summary>
+/// 
+/// </summary>
+public interface IEnvelopeExecutor : ISQLExecutor
+{
+    /// <summary>
+    /// 
+    /// </summary>
+    /// <param name="userId"></param>
+    /// <param name="title"></param>
+    /// <param name="message"></param>
+    /// <param name="tfaEnabled"></param>
+    /// <param name="cancellation"></param>
+    /// <returns></returns>
+    Task<Envelope> CreateEnvelopeAsync(int userId, string title = "", string message = "", bool tfaEnabled = false, CancellationToken cancellation = default);
+}

EnvelopeGenerator.Application/Contracts/SQLExecutor/IEnvelopeReceiverExecutor.cs

@@ -0,0 +1,20 @@
+using EnvelopeGenerator.Domain.Entities;
+
+namespace EnvelopeGenerator.Application.Contracts.SQLExecutor;
+
+/// <summary>
+/// 
+/// </summary>
+public interface IEnvelopeReceiverExecutor
+{
+    /// <summary>
+    /// 
+    /// </summary>
+    /// <param name="envelope_uuid"></param>
+    /// <param name="emailAddress"></param>
+    /// <param name="salutation"></param>
+    /// <param name="phone"></param>
+    /// <param name="cancellation"></param>
+    /// <returns></returns>
+    Task<EnvelopeReceiver?> AddEnvelopeReceiverAsync(string envelope_uuid, string emailAddress, string? salutation = null, string? phone = null, CancellationToken cancellation = default);
+}

EnvelopeGenerator.Application/Contracts/SQLExecutor/IQuery.cs

@@ -0,0 +1,70 @@
+namespace EnvelopeGenerator.Application.Contracts.SQLExecutor;
+
+/// <summary>
+/// Provides methods for executing common queries on a given entity type.
+/// This interface abstracts away the direct usage of ORM libraries (such as Entity Framework) for querying data
+/// and provides asynchronous and synchronous operations for querying a collection or single entity.
+/// </summary>
+/// <typeparam name="TEntity">The type of the entity being queried.</typeparam>
+public interface IQuery<TEntity>
+{
+    /// <summary>
+    /// Asynchronously retrieves the first entity or a default value if no entity is found.
+    /// </summary>
+    /// <returns>A task that represents the asynchronous operation. The task result contains the entity or a default value.</returns>
+    public Task<TEntity?> FirstOrDefaultAsync();
+
+    /// <summary>
+    /// Asynchronously retrieves a single entity or a default value if no entity is found.
+    /// </summary>
+    /// <returns>A task that represents the asynchronous operation. The task result contains the entity or a default value.</returns>
+    public Task<TEntity?> SingleOrDefaultAsync();
+
+    /// <summary>
+    /// Asynchronously retrieves a list of entities.
+    /// </summary>
+    /// <returns>A task that represents the asynchronous operation. The task result contains the list of entities.</returns>
+    public Task<IEnumerable<TEntity>> ToListAsync();
+
+    /// <summary>
+    /// Asynchronously retrieves the first entity. Throws an exception if no entity is found.
+    /// </summary>
+    /// <returns>A task that represents the asynchronous operation. The task result contains the first entity.</returns>
+    public Task<TEntity> FirstAsync();
+
+    /// <summary>
+    /// Asynchronously retrieves a single entity. Throws an exception if no entity is found.
+    /// </summary>
+    /// <returns>A task that represents the asynchronous operation. The task result contains the single entity.</returns>
+    public Task<TEntity> SingleAsync();
+
+    /// <summary>
+    /// Synchronously retrieves the first entity or a default value if no entity is found.
+    /// </summary>
+    /// <returns>The first entity or a default value.</returns>
+    public TEntity? FirstOrDefault();
+
+    /// <summary>
+    /// Synchronously retrieves a single entity or a default value if no entity is found.
+    /// </summary>
+    /// <returns>The single entity or a default value.</returns>
+    public TEntity? SingleOrDefault();
+
+    /// <summary>
+    /// Synchronously retrieves a list of entities.
+    /// </summary>
+    /// <returns>The list of entities.</returns>
+    public IEnumerable<TEntity> ToList();
+
+    /// <summary>
+    /// Synchronously retrieves the first entity. Throws an exception if no entity is found.
+    /// </summary>
+    /// <returns>The first entity.</returns>
+    public TEntity First();
+
+    /// <summary>
+    /// Synchronously retrieves a single entity. Throws an exception if no entity is found.
+    /// </summary>
+    /// <returns>The single entity.</returns>
+    public TEntity Single();
+}

EnvelopeGenerator.Application/Contracts/SQLExecutor/ISQL.cs

@@ -0,0 +1,20 @@
+namespace EnvelopeGenerator.Application.Contracts.SQLExecutor;
+
+/// <summary>
+/// Represents a raw SQL query contract.
+/// </summary>
+public interface ISQL
+{
+    /// <summary>
+    /// Gets the raw SQL query string.
+    /// </summary>
+    string Raw { get; }
+}
+
+/// <summary>
+/// Represents a typed SQL query contract for a specific entity.
+/// </summary>
+/// <typeparam name="TEntity">The type of the entity associated with the SQL query.</typeparam>
+public interface ISQL<TEntity> : ISQL
+{
+}

EnvelopeGenerator.Application/Contracts/SQLExecutor/ISQLExecutor.cs

@@ -0,0 +1,27 @@
+namespace EnvelopeGenerator.Application.Contracts.SQLExecutor;
+
+/// <summary>
+/// Defines methods for executing raw SQL queries or custom SQL query classes and returning query executors for further operations.
+/// Provides abstraction for raw SQL execution as well as mapping the results to <typeparamref name="TEntity"/> objects.
+/// </summary>
+/// <typeparam name="TEntity">The entity type to which the SQL query results will be mapped.</typeparam>
+public interface ISQLExecutor<TEntity>: ISQLExecutor
+{
+    /// <summary>
+    /// Executes a raw SQL query and returns an <see cref="IQuery{TEntity}"/> for further querying operations on the result.
+    /// </summary>
+    /// <param name="sql">The raw SQL query to execute.</param>
+    /// <param name="cancellation">Optional cancellation token for the operation.</param>
+    /// <param name="parameters">Optional parameters for the SQL query.</param>
+    /// <returns>An <see cref="IQuery{TEntity}"/> instance for further query operations on the result.</returns>
+    IQuery<TEntity> Execute(string sql, CancellationToken cancellation = default, params object[] parameters);
+
+    /// <summary>
+    /// Executes a custom SQL query defined by a class that implements <see cref="ISQL{TEntity}"/> and returns an <see cref="IQuery{TEntity}"/> for further querying operations on the result.
+    /// </summary>
+    /// <typeparam name="TSQL">The type of the custom SQL query class implementing <see cref="ISQL{TEntity}"/>.</typeparam>
+    /// <param name="cancellation">Optional cancellation token for the operation.</param>
+    /// <param name="parameters">Optional parameters for the SQL query.</param>
+    /// <returns>An <see cref="IQuery{TEntity}"/> instance for further query operations on the result.</returns>
+    IQuery<TEntity> Execute<TSQL>(CancellationToken cancellation = default, params object[] parameters) where TSQL : ISQL<TEntity>;
+}

EnvelopeGenerator.Application/Contracts/SQLExecutor/ISQLExecutorBaseEntity.cs

@@ -0,0 +1,29 @@
+using Dapper;
+
+namespace EnvelopeGenerator.Application.Contracts.SQLExecutor;
+
+/// <summary>
+/// 
+/// </summary>
+public interface ISQLExecutor
+{
+    /// <summary>
+    /// Executes a raw SQL query and returns an <see cref="IQuery{TEntity}"/> for further querying operations on the result.
+    /// </summary>
+    /// <typeparam name="TEntity">The entity type to which the SQL query results will be mapped.</typeparam>
+    /// <param name="sql">The raw SQL query to execute.</param>
+    /// <param name="parameters">Parameters for the SQL query.</param>
+    /// <param name="cancellation">Optional cancellation token for the operation.</param>
+    /// <returns>An <see cref="IQuery{TEntity}"/> instance for further query operations on the result.</returns>
+    Task<IEnumerable<TEntity>> Execute<TEntity>(string sql, DynamicParameters parameters, CancellationToken cancellation = default);
+
+    /// <summary>
+    /// Executes a custom SQL query defined by a class that implements <see cref="ISQL{TEntity}"/> and returns an <see cref="IQuery{TEntity}"/> for further querying operations on the result.
+    /// </summary>
+    /// <typeparam name="TEntity">The entity type to which the SQL query results will be mapped.</typeparam>
+    /// <typeparam name="TSQL">The type of the custom SQL query class implementing <see cref="ISQL{TEntity}"/>.</typeparam>
+    /// <param name="parameters">Parameters for the SQL query.</param>
+    /// <param name="cancellation">Optional cancellation token for the operation.</param>
+    /// <returns>An <see cref="IQuery{TEntity}"/> instance for further query operations on the result.</returns>
+    Task<IEnumerable<TEntity>> Execute<TEntity, TSQL>(DynamicParameters parameters, CancellationToken cancellation = default) where TSQL : ISQL;
+}

EnvelopeGenerator.Application/Contracts/Services/IEnvelopeReceiverService.cs

@@ -3,6 +3,8 @@ using DigitalData.Core.Abstractions.Application;
 using DigitalData.Core.DTO;
 using EnvelopeGenerator.Application.DTOs.EnvelopeReceiver;
 using EnvelopeGenerator.Application.DTOs.Messaging;
+using EnvelopeGenerator.Application.Envelopes;
+using EnvelopeGenerator.Application.Receivers.Queries.Read;
 using EnvelopeGenerator.Domain.Entities;
 
 namespace EnvelopeGenerator.Application.Contracts.Services;
@@ -31,7 +33,7 @@ public interface IEnvelopeReceiverService : IBasicCRUDService<EnvelopeReceiverDt
 
     Task<DataResult<bool>> IsExisting(string envelopeReceiverId);
 
-    Task<DataResult<IEnumerable<EnvelopeReceiverDto>>> ReadByUsernameAsync(string username, int? min_status = null, int? max_status = null, params int[] ignore_statuses);
+    Task<DataResult<IEnumerable<EnvelopeReceiverDto>>> ReadByUsernameAsync(string username, int? min_status = null, int? max_status = null, EnvelopeQuery? envelopeQuery = null, ReadReceiverQuery? receiverQuery = null, params int[] ignore_statuses);
 
     Task<DataResult<string?>> ReadLastUsedReceiverNameByMail(string mail);
 

EnvelopeGenerator.Application/DTOs/EmailTemplateDto.cs

@@ -3,10 +3,30 @@ using Microsoft.AspNetCore.Mvc;
 
 namespace EnvelopeGenerator.Application.DTOs
 {
+    /// <summary>
+    /// 
+    /// </summary>
     [ApiExplorerSettings(IgnoreApi = true)]
-    public record EmailTemplateDto(
-        int Id,
-        string Name,
-        string Body,
-        string Subject) : IUnique<int>;
+    public record EmailTemplateDto : IUnique<int>
+    {
+        /// <summary>
+        /// 
+        /// </summary>
+        public int Id{ get; init; }
+
+        /// <summary>
+        /// 
+        /// </summary>
+        public required string Name { get; init; }
+
+        /// <summary>
+        /// 
+        /// </summary>
+        public required string Body { get; set; }
+
+        /// <summary>
+        /// 
+        /// </summary>
+        public required string Subject { get; set; }
+    };
 }

EnvelopeGenerator.Application/DTOs/EnvelopeDto.cs

@@ -66,6 +66,9 @@ namespace EnvelopeGenerator.Application.DTOs
 		public string? StatusTranslated { get; set; }
 
 		public string? ContractTypeTranslated { get; set; }
-		public IEnumerable<EnvelopeDocumentDto>? Documents { get; set; }
+
+        public byte[]? DocResult { get; init; }
+
+        public IEnumerable<EnvelopeDocumentDto>? Documents { get; set; }
 	}
 }

EnvelopeGenerator.Application/DTOs/Receiver/ReceiverReadDto.cs

@@ -1,6 +1,5 @@
 using DigitalData.Core.Abstractions;
 using DigitalData.Core.DTO;
-using DigitalData.EmailProfilerDispatcher.Abstraction.Attributes;
 using EnvelopeGenerator.Application.DTOs.EnvelopeReceiver;
 using Microsoft.AspNetCore.Mvc;
 using System.Text.Json.Serialization;

EnvelopeGenerator.Application/DependencyInjection.cs

@@ -7,6 +7,7 @@ using DigitalData.Core.Client;
 using QRCoder;
 using EnvelopeGenerator.Application.Contracts.Services;
 using System.Reflection;
+using EnvelopeGenerator.Application.EnvelopeReceivers.Commands.Create;
 
 namespace EnvelopeGenerator.Application;
 
@@ -58,6 +59,7 @@ public static class DependencyInjection
         services.AddMediatR(cfg =>
         {
             cfg.RegisterServicesFromAssembly(Assembly.GetExecutingAssembly());
+            cfg.RegisterServicesFromAssembly(typeof(CreateEnvelopeReceiverCommandHandler).Assembly);
         });
 
         return services;

EnvelopeGenerator.Application/Documents/Queries/Read/ReadDocumentResponse.cs

@@ -3,23 +3,8 @@
 /// <summary>
 /// Represents the response for reading a document.
 /// </summary>
-public class ReadDocumentResponse
+public class ReadDocumentResponse : ReadDocumentResponseBase
 {
-    /// <summary>
-    /// The unique identifier of the document.
-    /// </summary>
-    public int Guid { get; init; }
-
-    /// <summary>
-    /// The identifier of the associated envelope.
-    /// </summary>
-    public int EnvelopeId { get; init; }
-
-    /// <summary>
-    /// The date and time when the document was added.
-    /// </summary>
-    public DateTime AddedWhen { get; init; }
-
     /// <summary>
     /// The binary data of the document, if available.
     /// </summary>

EnvelopeGenerator.Application/Documents/Queries/Read/ReadDocumentResponseBase.cs

@@ -0,0 +1,22 @@
+namespace EnvelopeGenerator.Application.Documents.Queries.Read;
+
+/// <summary>
+/// Represents the response for reading a document.
+/// </summary>
+public class ReadDocumentResponseBase
+{
+    /// <summary>
+    /// The unique identifier of the document.
+    /// </summary>
+    public int Guid { get; init; }
+
+    /// <summary>
+    /// The identifier of the associated envelope.
+    /// </summary>
+    public int EnvelopeId { get; init; }
+
+    /// <summary>
+    /// The date and time when the document was added.
+    /// </summary>
+    public DateTime AddedWhen { get; init; }
+}

EnvelopeGenerator.Application/EmailTemplates/Commands/Reset/ResetEmailTemplateCommand.cs

@@ -1,4 +1,5 @@
 using EnvelopeGenerator.Common;
+using MediatR;
 
 namespace EnvelopeGenerator.Application.EmailTemplates.Commands.Reset;
 
@@ -6,8 +7,7 @@ namespace EnvelopeGenerator.Application.EmailTemplates.Commands.Reset;
 /// Ein Befehl zum Zurücksetzen einer E-Mail-Vorlage auf die Standardwerte.
 /// Erbt von <see cref="EmailTemplateQuery"/> und ermöglicht die Angabe einer optionalen ID und eines Typs der E-Mail-Vorlage.
 /// </summary>
-/// <param name="Id">Die optionale ID der E-Mail-Vorlage, die zurückgesetzt werden soll.</param>
-/// <param name="Type">Der Typ der E-Mail-Vorlage, z. B. <see cref="Constants.EmailTemplateType"/> (optional). Beispiele:
+/// Beispiele:
 /// 0 - DocumentReceived: Benachrichtigung über den Empfang eines Dokuments.
 /// 1 - DocumentSigned: Benachrichtigung über die Unterzeichnung eines Dokuments.
 /// 2 - DocumentDeleted: Benachrichtigung über das Löschen eines Dokuments.
@@ -19,4 +19,23 @@ namespace EnvelopeGenerator.Application.EmailTemplates.Commands.Reset;
 /// 8 - DocumentRejected_REC (Für den ablehnenden Empfänger): Mail an den ablehnenden Empfänger, wenn das Dokument abgelehnt wird.
 /// 9 - DocumentRejected_REC_2 (Für sonstige Empfänger): Mail an andere Empfänger (Brief), wenn das Dokument abgelehnt wird.
 /// </param>
-public record ResetEnvelopeTemplateCommand(int? Id, Constants.EmailTemplateType? Type) : EmailTemplateQuery(Id, Type);
+public record ResetEmailTemplateCommand : EmailTemplateQuery, IRequest
+{
+    /// <summary>
+    /// 
+    /// </summary>
+    /// <param name="orginal"></param>
+    public ResetEmailTemplateCommand(EmailTemplateQuery? orginal = null) : base(orginal ?? new())
+    {
+
+    }
+
+    /// <summary>
+    /// 
+    /// </summary>
+    /// <param name="Id">Die optionale ID der E-Mail-Vorlage, die zurückgesetzt werden soll.</param>
+    /// <param name="Type">Der Typ der E-Mail-Vorlage, z. B. <see cref="Constants.EmailTemplateType"/> (optional).
+    public ResetEmailTemplateCommand(int? Id = null, Constants.EmailTemplateType? Type = null) : base(Id, Type)
+    {
+    }
+};

EnvelopeGenerator.Application/EmailTemplates/Commands/Reset/ResetEmailTemplateCommandHandler.cs

@@ -0,0 +1,114 @@
+using DigitalData.Core.Abstractions.Infrastructure;
+using EnvelopeGenerator.Application.DTOs;
+using EnvelopeGenerator.Application.EmailTemplates.Queries.Read;
+using EnvelopeGenerator.Domain.Entities;
+using MediatR;
+
+namespace EnvelopeGenerator.Application.EmailTemplates.Commands.Reset;
+
+/// <summary>
+/// 
+/// </summary>
+public class ResetEmailTemplateCommandHandler : IRequestHandler<ResetEmailTemplateCommand>
+{
+    private readonly IRepository<EmailTemplate> _repository;
+
+    /// <summary>
+    /// 
+    /// </summary>
+    /// <param name="repository"></param>
+    public ResetEmailTemplateCommandHandler(IRepository<EmailTemplate> repository)
+    {
+        _repository = repository;
+    }
+
+    /// <summary>
+    /// 
+    /// </summary>
+    /// <param name="request"></param>
+    /// <param name="cancel"></param>
+    /// <returns></returns>
+    public async Task Handle(ResetEmailTemplateCommand request, CancellationToken cancel)
+    {
+        var temps = request.Id is not null
+            ? await _repository.ReadAllAsync<EmailTemplateDto>(t => t.Id == request.Id, cancel)
+            : request.Type is not null
+                ? await _repository.ReadAllAsync<EmailTemplateDto>(t => t.Name == request.Type.ToString(), cancel)
+                : await _repository.ReadAllAsync<EmailTemplateDto>(ct: cancel);
+
+        foreach (var temp in temps)
+        {
+            var def = Defaults.Where(t => t.Name == temp.Name).FirstOrDefault();
+            if(def is not null)
+                await _repository.UpdateAsync(def, t => t.Id == temp.Id, cancel);
+        }
+    }
+
+    /// <summary>
+    /// 
+    /// </summary>
+    public static readonly IEnumerable<EmailTemplateDto> Defaults = new List<EmailTemplateDto>()
+    {
+        new(){
+        Id = 1,
+        Name = "DocumentReceived",
+        Body = "Guten Tag [NAME_RECEIVER],<br />\r\n<br /><B><I>\r\n[NAME_SENDER]</I></B> hat Ihnen ein Dokument zum [SIGNATURE_TYPE] gesendet.<br />\r\n<br />\r\nÜber den folgenden Link können Sie das Dokument einsehen und elektronisch unterschreiben: <a href=\"[LINK_TO_DOCUMENT]\">[LINK_TO_DOCUMENT_TEXT]</a><br />\r\n<br />\r\n[MESSAGE]<br />\r\n<br />\r\nMit freundlichen Grüßen<br />\r\n<br />\r\n[NAME_PORTAL]",
+        Subject = "Dokument erhalten: '[DOCUMENT_TITLE]'"
+        },
+        new(){
+        Id = 2,
+        Name = "DocumentDeleted",
+        Body = "Guten Tag [NAME_RECEIVER],<br />\r\n<br /><B><I>\r\n[NAME_SENDER]</I></B> hat den Umschlag <B><I>'[DOCUMENT_TITLE]'</I></B> gelöscht/zurückgezogen.<br /><p>\rBegründung: <br /> <I>[REASON]</I> <p>\r\n<br />\r\nMit freundlichen Grüßen<br />\r\n<br />\r\n[NAME_PORTAL]",
+        Subject = "Umschlag zurückgezogen: '[DOCUMENT_TITLE]'"
+        },
+        new(){
+        Id = 3,
+        Name = "DocumentSigned",
+        Body = "Guten Tag [NAME_RECEIVER],<br />\r\n<br />\r\nhiermit bestätigen wir Ihnen die erfolgreiche Signatur für den Vorgang <B><I>'[DOCUMENT_TITLE]'</I></B>.<br />\r\nWenn alle Vertragspartner unterzeichnet haben, erhalten Sie ebenfalls per email ein unterschriebenes Exemplar mit dem Signierungszertifikat!\r\n<br />\r\nMit freundlichen Grüßen<br />\r\n<br />\r\n[NAME_PORTAL]",
+        Subject = "Dokument unterschrieben: '[DOCUMENT_TITLE]'"
+        },
+        new(){
+        Id = 4,
+        Name = "DocumentCompleted",
+        Body = "Guten Tag [NAME_RECEIVER],<br />\r\n<br />\r\nDer Signaturvorgang <B><I>'[DOCUMENT_TITLE]'</I></B> wurde erfolgreich abgeschlossen.<br />\r\n<br />\r\nSie erhalten das Dokument mit einem detaillierten Ergebnisbericht als Anhang zu dieser Email.<br />\r\n<br />\r\nMit freundlichen Grüßen<br />\r\n<br />\r\n[NAME_PORTAL]",
+        Subject = "Umschlag abgeschlossen: '[DOCUMENT_TITLE]'"
+        },
+        new(){
+        Id = 5,
+        Name = "DocumentAccessCodeReceived",
+        Body = "Guten Tag [NAME_RECEIVER],<br />\r\n<br /><B><I>\r\n[NAME_SENDER]</I></B> hat Ihnen ein Dokument zum [SIGNATURE_TYPE] gesendet. <br />\r\n<br />\r\nVerwenden Sie den folgenden Zugriffscode, um das Dokument einzusehen:<br />\r\n<br />\r\n[DOCUMENT_ACCESS_CODE]<br />\r\n<br />\r\nMit freundlichen Grüßen<br />\r\n<br />\r\n[NAME_PORTAL]",
+        Subject = "Zugriffscode für Dokument erhalten: '[DOCUMENT_TITLE]'"
+        },
+        new(){
+        Id = 6,
+        Name = "DocumentRejected_ADM",
+        Body = "Guten Tag [NAME_SENDER],<p><B><I>[NAME_RECEIVER]</I></B> hat den Umschlag <B><I>'[DOCUMENT_TITLE]'</I></B> mit folgendem Grund abgelehnt: <p>\r\n[REASON] \r\n<p>Der Umschlag wurde auf den Status Rejected gesetzt. <p> \r\nMit freundlichen Grüßen<br />\r\n<br />\r\n[NAME_PORTAL]",
+        Subject = "'[DOCUMENT_TITLE]' - Unterzeichnungsvorgang zurückgezogen"
+        },
+        new(){
+        Id = 9,
+        Name = "DocumentRejected_REC",
+        Body = "Guten Tag [NAME_RECEIVER],\r\n<p>Hiermit bestätigen wir Ihnen die Ablehnung des Unterzeichnungsvorganges <B><I>'[DOCUMENT_TITLE]'</I></B>!<p>Der Vertragsinhaber <B><I>[NAME_SENDER]</I></B> wurde über die Ablehnung informiert. <p> \r\nMit freundlichen Grüßen<br />\r\n<br />\r\n[NAME_PORTAL]",
+        Subject = "'[DOCUMENT_TITLE]' - Bestätigung Ablehnung"
+        },
+        new(){
+        Id = 10,
+        Name = "DocumentRejected_REC_2",
+        Body = "Guten Tag [NAME_RECEIVER],\r\n<p>Der Unterzeichnungsvorganges <B><I>'[DOCUMENT_TITLE]'</I></B> wurde durch einen anderen Vertragspartner abgelehnt! Ihre notwendige Unterzeichnung wurde verworfen.<p> Der Vertragsinhaber <B><I>[NAME_SENDER]</I></B> wird sich bei Bedarf mit Ihnen in Verbindung setzen. <p> \r\nMit freundlichen Grüßen<br />\r\n<br />\r\n[NAME_PORTAL]",
+        Subject = "'[DOCUMENT_TITLE]' - Unterzeichnungsvorgang abgelehnt."
+        },
+        new(){
+        Id = 11,
+        Name = "DocumentShared",
+        Body = "Guten Tag,<br />  <br /><B><I>  [NAME_RECEIVER]</I></B> hat Ihnen ein Dokument zum Ansehen gesendet.<br />  <br />  Über den folgenden Link können Sie das Dokument einsehen: <a href=\"[LINK_TO_DOCUMENT]\">[LINK_TO_DOCUMENT_TEXT]</a><br /> <br />  <br />  Mit freundlichen Grüßen<br />  <br />  [NAME_PORTAL]",
+        Subject = "Dokument geteilt: '[DOCUMENT_TITLE]'"
+        },
+        new(){
+        Id = 12,
+        Name = "TotpSecret",
+        Body = "Guten Tag,<br /> <br />Sie können auf Ihren Zwei-Faktor-Authentifizierungscode zugreifen, indem Sie den unten stehenden QR-Code mit einer beliebigen Authentifizierungs-App auf Ihrem Telefon scannen (Google Authenticator, Microsoft Authenticator usw.). Dieser Code ist bis zum [TFA_EXPIRATION] gültig.<br /> <br /> <img src=\"data:image/png;base64,[TFA_QR_CODE]\" style=\"width: 13rem; height: 13rem;\"><br /> <br />\r\n<br /> Mit freundlichen Grüßen<br /> <br /> [NAME_PORTAL]",
+        Subject = "2-Faktor-Verifizierung QR-Code"
+        }
+    };
+
+}

EnvelopeGenerator.Application/EmailTemplates/Commands/Update/UpdateEmailTemplateCommand.cs

@@ -1,4 +1,5 @@
-using System.Text.Json.Serialization;
+using MediatR;
+using System.Text.Json.Serialization;
 
 namespace EnvelopeGenerator.Application.EmailTemplates.Commands.Update;
 
@@ -12,11 +13,17 @@ namespace EnvelopeGenerator.Application.EmailTemplates.Commands.Update;
 /// <param name="Subject">
 /// (Optional) Der neue Betreff der E-Mail. Wenn null, bleibt der vorhandene Betreff unverändert.
 /// </param>
-public record UpdateEmailTemplateCommand(string? Body = null, string? Subject = null)
+public record UpdateEmailTemplateCommand(string? Body = null, string? Subject = null) : IRequest
 {
     /// <param>
     /// Die Abfrage, die die E-Mail-Vorlage darstellt, die aktualisiert werden soll.
     /// </param>
     [JsonIgnore]
     public EmailTemplateQuery? EmailTemplateQuery { get; set; }
+
+    /// <summary>
+    /// 
+    /// </summary>
+    [JsonIgnore]
+    public DateTime ChangedWhen { get; init; } = DateTime.Now;
 }

EnvelopeGenerator.Application/EmailTemplates/Commands/Update/UpdateEmailTemplateCommandHandler.cs

@@ -0,0 +1,63 @@
+using DigitalData.Core.Abstractions.Infrastructure;
+using EnvelopeGenerator.Application.DTOs;
+using EnvelopeGenerator.Application.Exceptions;
+using EnvelopeGenerator.Domain.Entities;
+using MediatR;
+
+namespace EnvelopeGenerator.Application.EmailTemplates.Commands.Update;
+
+/// <summary>
+/// 
+/// </summary>
+public class UpdateEmailTemplateCommandHandler : IRequestHandler<UpdateEmailTemplateCommand>
+{
+    private readonly IRepository<EmailTemplate> _repository;
+
+    /// <summary>
+    /// 
+    /// </summary>
+    /// <param name="repository"></param>
+    public UpdateEmailTemplateCommandHandler(IRepository<EmailTemplate> repository)
+    {
+        _repository = repository;
+    }
+
+    /// <summary>
+    /// 
+    /// </summary>
+    /// <param name="request"></param>
+    /// <param name="cancel"></param>
+    /// <returns></returns>
+    /// <exception cref="InvalidOperationException"></exception>
+    /// <exception cref="NotFoundException"></exception>
+    public async Task Handle(UpdateEmailTemplateCommand request, CancellationToken cancel)
+    {
+        EmailTemplateDto? temp;
+
+        if (request.EmailTemplateQuery?.Id is int id)
+        {
+            temp = await _repository.ReadOrDefaultAsync<EmailTemplateDto>(t => t.Id == id, single: false, cancel);
+        }
+        else if (request!.EmailTemplateQuery!.Type is Common.Constants.EmailTemplateType type)
+        {
+            temp = await _repository.ReadOrDefaultAsync<EmailTemplateDto>(t => t.Name == type.ToString(), single: false, cancel);
+        }
+        else
+        {
+            throw new InvalidOperationException("Both id and type is null. Id: " + request.EmailTemplateQuery.Id +". Type: " + request.EmailTemplateQuery.Type.ToString());
+        }
+
+        if (temp == null)
+        {
+            throw new NotFoundException();
+        }
+
+        if (request.Body is not null)
+            temp.Body = request.Body;
+
+        if (request.Subject is not null)
+            temp.Subject = request.Subject;
+
+        await _repository.UpdateAsync(temp, t => t.Id == temp.Id, cancel);
+    }
+}

EnvelopeGenerator.Application/EmailTemplates/Queries/Read/ReadEmailTemplateMappingProfile.cs

@@ -0,0 +1,23 @@
+using AutoMapper;
+using EnvelopeGenerator.Domain.Entities;
+using System;
+using System.Collections.Generic;
+using System.Linq;
+using System.Text;
+using System.Threading.Tasks;
+
+namespace EnvelopeGenerator.Application.EmailTemplates.Queries.Read;
+
+/// <summary>
+/// 
+/// </summary>
+public class ReadEmailTemplateMappingProfile : Profile
+{
+    /// <summary>
+    /// 
+    /// </summary>
+    public ReadEmailTemplateMappingProfile()
+    {
+        CreateMap<EmailTemplate, ReadEmailTemplateResponse>();
+    }
+}

EnvelopeGenerator.Application/EmailTemplates/Queries/Read/ReadEmailTemplateQuery.cs

@@ -1,10 +1,12 @@
-namespace EnvelopeGenerator.Application.EmailTemplates.Queries.Read;
+using MediatR;
+
+namespace EnvelopeGenerator.Application.EmailTemplates.Queries.Read;
 
 
 /// <summary>
 /// Stellt eine Abfrage dar, um eine E-Mail-Vorlage zu lesen.
 /// Diese Klasse erbt von <see cref="EmailTemplateQuery"/>.
 /// </summary>
-public record ReadEmailTemplateQuery : EmailTemplateQuery
+public record ReadEmailTemplateQuery : EmailTemplateQuery, IRequest<ReadEmailTemplateResponse?>
 {
 }

EnvelopeGenerator.Application/EmailTemplates/Queries/Read/ReadEmailTemplateQueryHandler.cs

@@ -0,0 +1,52 @@
+using AutoMapper;
+using EnvelopeGenerator.Application.Contracts.Repositories;
+using EnvelopeGenerator.Application.DTOs;
+using EnvelopeGenerator.Common;
+using MediatR;
+using Microsoft.AspNetCore.Http;
+using Microsoft.Extensions.Logging;
+
+namespace EnvelopeGenerator.Application.EmailTemplates.Queries.Read;
+
+/// <summary>
+/// 
+/// </summary>
+public class ReadEmailTemplateQueryHandler : IRequestHandler<ReadEmailTemplateQuery, ReadEmailTemplateResponse?>
+{
+    private readonly IMapper _mapper;
+
+    private readonly IEmailTemplateRepository _repository;
+
+    /// <summary>
+    /// Initialisiert eine neue Instanz der <see cref="EmailTemplateController"/>-Klasse.
+    /// </summary>
+    /// <param name="mapper">
+    /// <param name="repository">
+    /// Die AutoMapper-Instanz, die zum Zuordnen von Objekten verwendet wird.
+    /// </param>
+    public ReadEmailTemplateQueryHandler(IMapper mapper, IEmailTemplateRepository repository)
+    {
+        _mapper = mapper;
+        _repository = repository;
+    }
+
+    /// <summary>
+    /// 
+    /// </summary>
+    /// <param name="request"></param>
+    /// <param name="cancellationToken"></param>
+    /// <returns></returns>
+    /// <exception cref="InvalidOperationException"></exception>
+    public async Task<ReadEmailTemplateResponse?> Handle(ReadEmailTemplateQuery request, CancellationToken cancellationToken)
+    {
+        var temp = request.Id is int id
+            ? await _repository.ReadByIdAsync(id)
+            : request.Type is Constants.EmailTemplateType type
+                ? await _repository.ReadByNameAsync(type)
+                : throw new InvalidOperationException("Either a valid integer ID or a valid EmailTemplateType must be provided in the request.");
+
+        var res = _mapper.Map<ReadEmailTemplateResponse>(temp);
+
+        return res;
+    }
+}

EnvelopeGenerator.Application/EmailTemplates/Queries/Read/ReadEmailTemplateResponse.cs

@@ -3,18 +3,35 @@
 /// <summary>
 /// Stellt die Antwort für eine Abfrage von E-Mail-Vorlagen bereit.
 /// </summary>
-/// <param name="Id">Die eindeutige Kennung der E-Mail-Vorlage.</param>
-/// <param name="Type">Der Typ der E-Mail-Vorlage.</param>
-/// <param name="AddedWhen">Das Datum und die Uhrzeit, wann die Vorlage hinzugefügt wurde.</param>
-/// <param name="Body">Der Inhalt (Body) der E-Mail-Vorlage. Kann null sein.</param>
-/// <param name="Subject">Der Betreff der E-Mail-Vorlage. Kann null sein.</param>
-/// <param name="ChangedWhen">Das Datum und die Uhrzeit, wann die Vorlage zuletzt geändert wurde. Kann null sein.</param>
-public record ReadEmailTemplateResponse(
-    int Id,
-    int Type,
-    DateTime AddedWhen,
-    string? Body = null,
-    string? Subject = null,
-    DateTime? ChangedWhen = null)
+public class ReadEmailTemplateResponse
 {
+    /// <summary>
+    /// Die eindeutige Kennung der E-Mail-Vorlage.
+    /// </summary>
+    public int Id { get; set; }
+
+    /// <summary>
+    /// Name des Typs
+    /// </summary>
+    public required string Name { get; set; }
+
+    /// <summary>
+    /// Das Datum und die Uhrzeit, wann die Vorlage hinzugefügt wurde.
+    /// </summary>
+    public DateTime AddedWhen { get; set; }
+
+    /// <summary>
+    /// Der Inhalt (Body) der E-Mail-Vorlage. Kann null sein.
+    /// </summary>
+    public string? Body { get; set; }
+
+    /// <summary>
+    /// Der Betreff der E-Mail-Vorlage. Kann null sein.
+    /// </summary>
+    public string? Subject { get; set; }
+
+    /// <summary>
+    /// Das Datum und die Uhrzeit, wann die Vorlage zuletzt geändert wurde. Kann null sein.
+    /// </summary>
+    public DateTime? ChangedWhen { get; set; }
 }

EnvelopeGenerator.Application/EnvelopeGenerator.Application.csproj

@@ -13,13 +13,15 @@
 	</ItemGroup>
 
 	<ItemGroup>
-		<PackageReference Include="DigitalData.Core.Abstractions" Version="3.4.3" />
+		<PackageReference Include="Dapper" Version="2.1.66" />
+		<PackageReference Include="DigitalData.Core.Abstractions" Version="3.6.0" />
 		<PackageReference Include="DigitalData.Core.Application" Version="3.2.1" />
 		<PackageReference Include="DigitalData.Core.Client" Version="2.0.3" />
 		<PackageReference Include="DigitalData.Core.DTO" Version="2.0.1" />
 		<PackageReference Include="DigitalData.EmailProfilerDispatcher" Version="3.0.0" />
 		<PackageReference Include="MediatR" Version="12.5.0" />
 		<PackageReference Include="Microsoft.AspNetCore.Authentication.JwtBearer" Version="7.0.18" />
+		<PackageReference Include="Microsoft.Data.SqlClient" Version="6.0.2" />
 		<PackageReference Include="Microsoft.Extensions.DependencyInjection" Version="9.0.4" />
 		<PackageReference Include="Otp.NET" Version="1.4.0" />
 		<PackageReference Include="QRCoder" Version="1.6.0" />

EnvelopeGenerator.Application/EnvelopeReceivers/Commands/Create/CreateEnvelopeReceiverCommand.cs

@@ -1,4 +1,5 @@
-using MediatR;
+using EnvelopeGenerator.Application.Envelopes.Commands;
+using MediatR;
 using System.ComponentModel.DataAnnotations;
 
 namespace EnvelopeGenerator.Application.EnvelopeReceivers.Commands.Create;
@@ -17,43 +18,4 @@ public record CreateEnvelopeReceiverCommand(
     [Required] DocumentCreateCommand Document,
     [Required] IEnumerable<ReceiverGetOrCreateCommand> Receivers,
     bool TFAEnabled = false
-    ) : IRequest;
-
-#region DTOs
-/// <summary>
-/// Signaturposition auf einem Dokument.
-/// </summary>
-/// <param name="X">X-Position</param>
-/// <param name="Y">Y-Position</param>
-/// <param name="Page">Seite, auf der sie sich befindet</param>
-public record Signature([Required] int X, [Required] int Y, [Required] int Page);
-
-/// <summary>
-/// DTO für Empfänger, die erstellt oder abgerufen werden sollen.
-/// Wenn nicht, wird sie erstellt und mit einer Signatur versehen.
-/// </summary>
-/// <param name="Signatures">Unterschriften auf Dokumenten.</param>
-/// <param name="Salution">Der Name, mit dem der Empfänger angesprochen werden soll. Bei Null oder keinem Wert wird der zuletzt verwendete Name verwendet.</param>
-/// <param name="PhoneNumber">Sollte mit Vorwahl geschrieben werden</param>
-public record ReceiverGetOrCreateCommand([Required] IEnumerable<Signature> Signatures, string? Salution = null, string? PhoneNumber = null)
-{
-    private string _emailAddress = string.Empty;
-
-    /// <summary>
-    /// E-Mail-Adresse des Empfängers.
-    /// </summary>
-    [Required]
-    public required string EmailAddress { get => _emailAddress.ToLower(); init => _emailAddress = _emailAddress.ToLower(); }
-};
-
-/// <summary>
-/// DTO zum Erstellen eines Dokuments.
-/// </summary>
-/// <param name="DataAsByte">
-/// Die Dokumentdaten im Byte-Array-Format. Wird verwendet, wenn das Dokument als Roh-Binärdaten bereitgestellt wird.
-/// </param>
-/// <param name="DataAsBase64">
-/// Die Dokumentdaten im Base64-String-Format. Wird verwendet, wenn das Dokument als Base64-codierter String bereitgestellt wird.
-/// </param>
-public record DocumentCreateCommand(byte[]? DataAsByte = null, string? DataAsBase64 = null);
-#endregion
+    ) : CreateEnvelopeCommand(Title, Message, TFAEnabled), IRequest<CreateEnvelopeReceiverResponse?>;

EnvelopeGenerator.Application/EnvelopeReceivers/Commands/Create/CreateEnvelopeReceiverCommandHandler.cs

@@ -0,0 +1,67 @@
+using AutoMapper;
+using EnvelopeGenerator.Application.Contracts.SQLExecutor;
+using EnvelopeGenerator.Application.DTOs.Receiver;
+using EnvelopeGenerator.Domain.Entities;
+using MediatR;
+
+namespace EnvelopeGenerator.Application.EnvelopeReceivers.Commands.Create;
+
+/// <summary>
+/// Handles the creation of an envelope along with its associated document and recipients.
+/// This command processes the envelope data, including title, message, document content,
+/// recipient list, and optional two-factor authentication settings.
+/// </summary>
+public class CreateEnvelopeReceiverCommandHandler : IRequestHandler<CreateEnvelopeReceiverCommand, CreateEnvelopeReceiverResponse>
+{
+    private readonly IMapper _mapper;
+
+    private readonly IEnvelopeExecutor _envelopeExecutor;
+
+    private readonly IEnvelopeReceiverExecutor _erExecutor;
+
+    /// <summary>
+    /// 
+    /// </summary>
+    /// <param name="mapper"></param>
+    /// <param name="envelopeExecutor"></param>
+    /// <param name="erExecutor"></param>
+    public CreateEnvelopeReceiverCommandHandler(IMapper mapper, IEnvelopeExecutor envelopeExecutor, IEnvelopeReceiverExecutor erExecutor)
+    {
+        _mapper = mapper;
+        _envelopeExecutor = envelopeExecutor;
+        _erExecutor = erExecutor;
+    }
+
+    /// <summary>
+    /// Handles the execution of the <see cref="CreateEnvelopeReceiverCommand"/>.
+    /// Responsible for validating input data, creating or retrieving recipients, associating signatures,
+    /// and storing the envelope and document details.
+    /// </summary>
+    /// <param name="request">The command containing all necessary information to create an envelope.</param>
+    /// <param name="cancel">Token to observe while waiting for the task to complete.</param>
+    /// <returns>A task representing the asynchronous operation.</returns>
+    public async Task<CreateEnvelopeReceiverResponse> Handle(CreateEnvelopeReceiverCommand request, CancellationToken cancel)
+    {
+        int userId = request.UserId ?? throw new InvalidOperationException("UserId cannot be null when creating an envelope.");
+
+        var envelope = await _envelopeExecutor.CreateEnvelopeAsync(userId, request.Title, request.Message, request.TFAEnabled, cancel);
+
+        List<EnvelopeReceiver> sentRecipients = new();
+        List<ReceiverGetOrCreateCommand> unsentRecipients = new();
+
+        foreach (var receiver in request.Receivers)
+        {
+            var envelopeReceiver = await _erExecutor.AddEnvelopeReceiverAsync(envelope.Uuid, receiver.EmailAddress, receiver.Salution, receiver.PhoneNumber, cancel);
+
+            if (envelopeReceiver is null)
+                unsentRecipients.Add(receiver);
+            else
+                sentRecipients.Add(envelopeReceiver);
+        }
+
+        var res = _mapper.Map<CreateEnvelopeReceiverResponse>(envelope);
+        res.UnsentReceivers = unsentRecipients;
+        res.SentReceiver = _mapper.Map<IEnumerable<ReceiverReadDto>>(sentRecipients);
+        return res;
+    }
+}

EnvelopeGenerator.Application/EnvelopeReceivers/Commands/Create/CreateEnvelopeReceiverDtos.cs

@@ -0,0 +1,48 @@
+using System;
+using System.Collections.Generic;
+using System.ComponentModel.DataAnnotations;
+using System.Linq;
+using System.Text;
+using System.Threading.Tasks;
+
+namespace EnvelopeGenerator.Application.EnvelopeReceivers.Commands.Create;
+
+#region DTOs
+/// <summary>
+/// Signaturposition auf einem Dokument.
+/// </summary>
+/// <param name="X">X-Position</param>
+/// <param name="Y">Y-Position</param>
+/// <param name="Page">Seite, auf der sie sich befindet</param>
+public record Signature([Required] double X, [Required] double Y, [Required] int Page);
+
+/// <summary>
+/// DTO für Empfänger, die erstellt oder abgerufen werden sollen.
+/// Wenn nicht, wird sie erstellt und mit einer Signatur versehen.
+/// </summary>
+/// <param name="Signatures">Unterschriften auf Dokumenten.</param>
+/// <param name="Salution">Der Name, mit dem der Empfänger angesprochen werden soll. Bei Null oder keinem Wert wird der zuletzt verwendete Name verwendet.</param>
+/// <param name="PhoneNumber">Sollte mit Vorwahl geschrieben werden</param>
+public record ReceiverGetOrCreateCommand([Required] IEnumerable<Signature> Signatures, string? Salution = null, string? PhoneNumber = null)
+{
+    private string _emailAddress = string.Empty;
+
+    /// <summary>
+    /// E-Mail-Adresse des Empfängers.
+    /// </summary>
+    [Required]
+    public string EmailAddress { get => _emailAddress.ToLower(); init => _emailAddress = value.ToLower(); }
+};
+
+/// <summary>
+/// DTO zum Erstellen eines Dokuments.
+/// </summary>
+public record DocumentCreateCommand()
+{
+    /// <summary>
+    /// Die Dokumentdaten im Base64-String-Format. Wird verwendet, wenn das Dokument als Base64-codierter String bereitgestellt wird.
+    /// </summary>
+    [Required]
+    public required string DataAsBase64 { get; init; }
+};
+#endregion

EnvelopeGenerator.Application/EnvelopeReceivers/Commands/Create/CreateEnvelopeReceiverMappingProfile.cs

@@ -0,0 +1,21 @@
+using AutoMapper;
+using EnvelopeGenerator.Application.DTOs.Receiver;
+using EnvelopeGenerator.Application.Envelopes.Commands;
+using EnvelopeGenerator.Domain.Entities;
+
+namespace EnvelopeGenerator.Application.EnvelopeReceivers.Commands.Create;
+
+/// <summary>
+/// 
+/// </summary>
+public class CreateEnvelopeReceiverMappingProfile : Profile
+{
+    /// <summary>
+    /// 
+    /// </summary>
+    public CreateEnvelopeReceiverMappingProfile()
+    {
+        CreateMap<Envelope, CreateEnvelopeResponse>();
+        CreateMap<Receiver, ReceiverReadDto>();
+    }
+}

EnvelopeGenerator.Application/EnvelopeReceivers/Commands/Create/CreateEnvelopeReceiverResponse.cs

@@ -0,0 +1,39 @@
+using DigitalData.UserManager.Domain.Entities;
+using EnvelopeGenerator.Application.DTOs.Receiver;
+using EnvelopeGenerator.Application.Envelopes.Commands;
+
+namespace EnvelopeGenerator.Application.EnvelopeReceivers.Commands.Create;
+
+/// <summary>
+/// 
+/// </summary>
+public record CreateEnvelopeReceiverResponse : CreateEnvelopeResponse
+{
+    /// <summary>
+    /// 
+    /// </summary>
+    /// <param name="Id"></param>
+    /// <param name="UserId"></param>
+    /// <param name="Status"></param>
+    /// <param name="Uuid"></param>
+    /// <param name="Message"></param>
+    /// <param name="AddedWhen"></param>
+    /// <param name="ChangedWhen"></param>
+    /// <param name="Title"></param>
+    /// <param name="Language"></param>
+    /// <param name="TFAEnabled"></param>
+    /// <param name="User"></param>
+    public CreateEnvelopeReceiverResponse(int Id, int UserId, int Status, string Uuid, string? Message, DateTime AddedWhen, DateTime? ChangedWhen, string? Title, string Language, bool TFAEnabled, User User) : base(Id, UserId, Status, Uuid, Message, AddedWhen, ChangedWhen, Title, Language, TFAEnabled, User)
+    {
+    }
+
+    /// <summary>
+    /// 
+    /// </summary>
+    public IEnumerable<ReceiverReadDto> SentReceiver { get; set; } = new List<ReceiverReadDto>();
+
+    /// <summary>
+    /// 
+    /// </summary>
+    public IEnumerable<ReceiverGetOrCreateCommand> UnsentReceivers { get; set; } = new List<ReceiverGetOrCreateCommand>();
+}

EnvelopeGenerator.Application/Envelopes/Commands/CreateEnvelopeCommand.cs

@@ -0,0 +1,26 @@
+using MediatR;
+using Microsoft.AspNetCore.Mvc.ModelBinding;
+using System.ComponentModel.DataAnnotations;
+using System.Text.Json.Serialization;
+
+namespace EnvelopeGenerator.Application.Envelopes.Commands;
+
+/// <summary>
+/// Befehl zur Erstellung eines Umschlags.
+/// </summary>
+/// <param name="Title">Der Titel des Umschlags. Dies ist ein Pflichtfeld.</param>
+/// <param name="Message">Die Nachricht, die im Umschlag enthalten sein soll. Dies ist ein Pflichtfeld.</param>
+/// <param name="TFAEnabled">Gibt an, ob die Zwei-Faktor-Authentifizierung für den Umschlag aktiviert ist. Standardmäßig false.</param>
+public record CreateEnvelopeCommand(
+    [Required] string Title,
+    [Required] string Message,
+    bool TFAEnabled = false
+    ) : IRequest<CreateEnvelopeResponse?>
+{
+    /// <summary>
+    /// Id of receiver
+    /// </summary>
+    [JsonIgnore]
+    [BindNever]
+    public int? UserId { get; set; }
+};

EnvelopeGenerator.Application/Envelopes/Commands/CreateEnvelopeCommandHandler.cs

@@ -0,0 +1,41 @@
+using AutoMapper;
+using EnvelopeGenerator.Application.Contracts.SQLExecutor;
+using MediatR;
+
+namespace EnvelopeGenerator.Application.Envelopes.Commands;
+
+/// <summary>
+/// 
+/// </summary>
+public class CreateEnvelopeCommandHandler : IRequestHandler<CreateEnvelopeCommand, CreateEnvelopeResponse?>
+{
+    private readonly IEnvelopeExecutor _envelopeExecutor;
+
+    private readonly IMapper _mapper;
+
+    /// <summary>
+    /// 
+    /// </summary>
+    /// <param name="envelopeExecutor"></param>
+    /// <param name="mapper"></param>
+    public CreateEnvelopeCommandHandler(IEnvelopeExecutor envelopeExecutor, IMapper mapper)
+    {
+        _envelopeExecutor = envelopeExecutor;
+        _mapper = mapper;
+    }
+
+    /// <summary>
+    /// 
+    /// </summary>
+    /// <param name="request"></param>
+    /// <param name="cancellationToken"></param>
+    /// <returns></returns>
+    public async Task<CreateEnvelopeResponse?> Handle(CreateEnvelopeCommand request, CancellationToken cancellationToken)
+    {
+        int userId = request.UserId ?? throw new InvalidOperationException("UserId cannot be null when creating an envelope.");
+
+        var envelope = await _envelopeExecutor.CreateEnvelopeAsync(userId, request.Title, request.Message, request.TFAEnabled, cancellationToken);
+
+        return _mapper.Map<CreateEnvelopeResponse>(envelope);
+    }
+}

EnvelopeGenerator.Application/Envelopes/Commands/CreateEnvelopeMappingProfile.cs

@@ -0,0 +1,19 @@
+using AutoMapper;
+using EnvelopeGenerator.Application.EnvelopeReceivers.Commands.Create;
+using EnvelopeGenerator.Domain.Entities;
+
+namespace EnvelopeGenerator.Application.Envelopes.Commands;
+
+/// <summary>
+/// 
+/// </summary>
+public class CreateEnvelopeMappingProfile : Profile
+{
+    /// <summary>
+    /// 
+    /// </summary>
+    public CreateEnvelopeMappingProfile()
+    {
+        CreateMap<Envelope, CreateEnvelopeReceiverResponse>();
+    }
+}

EnvelopeGenerator.Application/Envelopes/Commands/CreateEnvelopeResponse.cs

@@ -0,0 +1,20 @@
+using EnvelopeGenerator.Application.Envelopes.Queries.Read;
+
+namespace EnvelopeGenerator.Application.Envelopes.Commands;
+
+/// <summary>
+/// 
+/// </summary>
+/// <param name="Id"><inheritdoc/></param>
+/// <param name="UserId"><inheritdoc/></param>
+/// <param name="Status"><inheritdoc/></param>
+/// <param name="Uuid"><inheritdoc/></param>
+/// <param name="Message"><inheritdoc/></param>
+/// <param name="AddedWhen"><inheritdoc/></param>
+/// <param name="ChangedWhen"><inheritdoc/></param>
+/// <param name="Title"><inheritdoc/></param>
+/// <param name="Language"><inheritdoc/></param>
+/// <param name="TFAEnabled"><inheritdoc/></param>
+/// <param name="User"><inheritdoc/></param>
+public record CreateEnvelopeResponse(int Id, int UserId, int Status, string Uuid, string? Message, DateTime AddedWhen, DateTime? ChangedWhen, string? Title, string Language, bool TFAEnabled, DigitalData.UserManager.Domain.Entities.User User) 
+    : ReadEnvelopeResponse(Id, UserId, Status, Uuid, Message, AddedWhen, ChangedWhen, Title, Language, TFAEnabled, User);

EnvelopeGenerator.Application/Exceptions/BadRequestException.cs

@@ -0,0 +1,22 @@
+namespace EnvelopeGenerator.Application.Exceptions;
+
+/// <summary>
+/// Represents an exception that is thrown when a bad request is encountered.
+/// </summary>
+public class BadRequestException : Exception
+{
+    /// <summary>
+    /// Initializes a new instance of the <see cref="BadRequestException"/> class.
+    /// </summary>
+    public BadRequestException()
+    {
+    }
+
+    /// <summary>
+    /// Initializes a new instance of the <see cref="BadRequestException"/> class with a specified error message.
+    /// </summary>
+    /// <param name="message">The message that describes the error.</param>
+    public BadRequestException(string? message) : base(message)
+    {
+    }
+}

EnvelopeGenerator.Application/Exceptions/NotFoundException.cs

@@ -0,0 +1,12 @@
+namespace EnvelopeGenerator.Application.Exceptions;
+
+public class NotFoundException : Exception
+{
+    public NotFoundException()
+    {
+    }
+
+    public NotFoundException(string? message) : base(message)
+    {
+    }
+}

EnvelopeGenerator.Application/Histories/Queries/Read/ReadHistoryQuery.cs

@@ -4,17 +4,14 @@ using EnvelopeGenerator.Common;
 
 namespace EnvelopeGenerator.Application.Histories.Queries.Read;
 
+//TODO: Add sender query
 /// <summary>
 /// Repräsentiert eine Abfrage für die Verlaufshistorie eines Umschlags.
 /// </summary>
 /// <param name="EnvelopeId">Die eindeutige Kennung des Umschlags.</param>
-/// <param name="Envelope">Die Abfrage, die den Umschlag beschreibt.</param>
-/// <param name="Receiver">Die Abfrage, die den Empfänger beschreibt.</param>
 /// <param name="Related">Abfrage, die angibt, worauf sich der Datensatz bezieht. Ob er sich auf den Empfänger, den Sender oder das System bezieht, wird durch 0, 1 bzw. 2 dargestellt.</param>
 /// <param name="OnlyLast">Abfrage zur Steuerung, ob nur der aktuelle Status oder der gesamte Datensatz zurückgegeben wird.</param>
 public record ReadHistoryQuery(
-    int EnvelopeId,
-    ReadEnvelopeQuery? Envelope = null,
-    ReadReceiverQuery? Receiver = null,
+    int? EnvelopeId = null,
     Constants.ReferenceType? Related = null,
     bool? OnlyLast = true);

EnvelopeGenerator.Application/SQL/DocumentCreateReadSQL.cs

@@ -0,0 +1,42 @@
+using Dapper;
+using EnvelopeGenerator.Application.Contracts.SQLExecutor;
+using EnvelopeGenerator.Domain.Entities;
+
+namespace EnvelopeGenerator.Application.SQL;
+
+/// <summary>
+/// 
+/// </summary>
+public class DocumentCreateReadSQL : ISQL<EnvelopeDocument>
+{
+    /// <summary>
+    /// Base64, OUT_UID
+    /// </summary>
+    public string Raw => @"
+        DECLARE @BYTE_DATA1 as VARBINARY(MAX)
+        SET @BYTE_DATA1 = @ByteData
+        DECLARE	@OUT_DOCID int
+
+        EXEC	[dbo].[PRSIG_API_ADD_DOC]
+		        {0},
+		        @BYTE_DATA1,
+		        @OUT_DOCID OUTPUT
+
+        SELECT TOP(1) *
+          FROM [dbo].[TBSIG_ENVELOPE_DOCUMENT]
+          WHERE [GUID] = @OUT_DOCID
+    ";
+
+    /// <summary>
+    /// 
+    /// </summary>
+    /// <param name="base64"></param>
+    /// <returns></returns>
+    public static DynamicParameters CreateParmas(string base64)
+    {
+        var parameters = new DynamicParameters();
+        byte[] byteData = Convert.FromBase64String(base64);
+        parameters.Add("ByteData", byteData, System.Data.DbType.Binary);
+        return parameters;
+    }
+}

EnvelopeGenerator.Application/SQL/EnvelopeCreateReadSQL.cs

@@ -0,0 +1,49 @@
+using Dapper;
+using EnvelopeGenerator.Application.Contracts.SQLExecutor;
+using EnvelopeGenerator.Domain.Entities;
+using System.Data;
+
+namespace EnvelopeGenerator.Application.SQL;
+
+/// <summary>
+/// 
+/// </summary>
+public class EnvelopeCreateReadSQL : ISQL<Envelope>
+{
+    /// <summary>
+    /// USER_ID, TITLE, TFAEnabled, MESSAGE
+    /// </summary>
+    public string Raw => @"
+        DECLARE @OUT_UID varchar(36);
+
+        EXEC [dbo].[PRSIG_API_CREATE_ENVELOPE]
+            {0},
+            {1},
+            {2},
+            {3},
+            @OUT_UID OUTPUT;
+
+        SELECT TOP(1) *
+        FROM [dbo].[TBSIG_ENVELOPE]
+        WHERE [ENVELOPE_UUID] = @OUT_UID;
+    ";
+
+    /// <summary>
+    /// 
+    /// </summary>
+    /// <param name="userId"></param>
+    /// <param name="title"></param>
+    /// <param name="message"></param>
+    /// <param name="tfaEnabled"></param>
+    /// <returns></returns>
+    public static DynamicParameters CreateParams(int userId, string title = "", string message = "", bool tfaEnabled = false)
+    {
+        var parameters = new DynamicParameters();
+        parameters.Add("@UserId", userId);
+        parameters.Add("@Title", title);
+        parameters.Add("@TfaEnabled", tfaEnabled ? 1 : 0);
+        parameters.Add("@Message", message);
+        parameters.Add("@OutUid", dbType: DbType.String, size: 36, direction: ParameterDirection.Output);
+        return parameters;
+    }
+}

EnvelopeGenerator.Application/SQL/EnvelopeReceiverAddReadSQL.cs

@@ -0,0 +1,47 @@
+using Dapper;
+using EnvelopeGenerator.Application.Contracts.SQLExecutor;
+using EnvelopeGenerator.Domain.Entities;
+
+namespace EnvelopeGenerator.Application.SQL;
+
+/// <summary>
+/// 
+/// </summary>
+public class EnvelopeReceiverAddReadSQL : ISQL<Envelope>
+{
+    /// <summary>
+    /// ENV_UID, EMAIL_ADRESS, SALUTATION, PHONE, 
+    /// </summary>
+    public string Raw => @"
+		DECLARE	@OUT_RECEIVER_ID int
+
+		EXEC	[dbo].[PRSIG_API_CREATE_RECEIVER]
+				{0},
+				{1},
+				{2},
+				{3},
+				@OUT_RECEIVER_ID OUTPUT
+
+        SELECT TOP(1) [ENVELOPE_ID] As EnvelopeId, [RECEIVER_ID] As ReceiverId
+        FROM [dbo].[TBSIG_ENVELOPE_RECEIVER]
+		WHERE [GUID] = @OUT_RECEIVER_ID;
+	";
+
+    /// <summary>
+    /// 
+    /// </summary>
+    /// <param name="envelope_uuid"></param>
+    /// <param name="emailAddress"></param>
+    /// <param name="salutation"></param>
+    /// <param name="phone"></param>
+    /// <returns></returns>
+    public static DynamicParameters CreateParameters(string envelope_uuid, string emailAddress, string? salutation = null, string? phone = null)
+    {
+        var parameters = new DynamicParameters();
+        parameters.Add("@ENV_UID", envelope_uuid);
+        parameters.Add("@EMAIL_ADRESS", emailAddress);
+        parameters.Add("@SALUTATION", salutation);
+        parameters.Add("@PHONE", phone);
+        return parameters;
+    }
+}

EnvelopeGenerator.Application/SQL/ParamsExtensions.cs

@@ -0,0 +1,30 @@
+using System.Globalization;
+
+namespace EnvelopeGenerator.Application.SQL;
+
+/// <summary>
+/// Extension method for converting objects to SQL parameter strings.
+/// </summary>
+public static class ParamsExtensions
+{
+    /// <summary>
+    /// Converts a .NET object to its corresponding SQL-safe parameter string.
+    /// </summary>
+    /// <param name="obj">The object to convert.</param>
+    /// <returns>A string representing the SQL parameter.</returns>
+    public static string ToSqlParam(this object? obj)
+    {
+        if (obj is null)
+            return "NULL";
+        else if (obj is string strVal)
+            return $"'{strVal}'";
+        else if (obj is bool boolVal)
+            return boolVal ? "1" : "0";
+        else if (obj is double doubleVal)
+            return $"'{doubleVal.ToString(CultureInfo.InvariantCulture)}'";
+        else if (obj is int intVal)
+            return intVal.ToString();
+        else
+            throw new NotSupportedException($"Type '{obj.GetType().FullName}' is not supported for SQL parameter conversion.");
+    }
+}

EnvelopeGenerator.Application/Services/EnvelopeReceiverService.cs

@@ -10,6 +10,8 @@ using Microsoft.Extensions.Logging;
 using EnvelopeGenerator.Extensions;
 using EnvelopeGenerator.Application.DTOs.Messaging;
 using EnvelopeGenerator.Application.Contracts.Services;
+using EnvelopeGenerator.Application.Envelopes;
+using EnvelopeGenerator.Application.Receivers.Queries.Read;
 
 namespace EnvelopeGenerator.Application.Services;
 
@@ -137,9 +139,25 @@ public class EnvelopeReceiverService : BasicCRUDService<IEnvelopeReceiverReposit
             : Result.Success(code);
 		}
 
-    public async Task<DataResult<IEnumerable<EnvelopeReceiverDto>>> ReadByUsernameAsync(string username, int? min_status = null, int? max_status = null, params int[] ignore_statuses)
+    public async Task<DataResult<IEnumerable<EnvelopeReceiverDto>>> ReadByUsernameAsync(string username, int? min_status = null, int? max_status = null, EnvelopeQuery? envelopeQuery = null, ReadReceiverQuery? receiverQuery = null, params int[] ignore_statuses)
     {
         var er_list = await _repository.ReadByUsernameAsync(username: username, min_status: min_status, max_status: max_status, ignore_statuses: ignore_statuses);
+        
+        if(envelopeQuery?.Id is int eId)
+            er_list = er_list.Where(er => er.EnvelopeId == eId);
+
+        if (envelopeQuery?.Uuid is string uuid)
+            er_list = er_list.Where(er => er.Envelope?.Uuid == uuid);
+
+        if (envelopeQuery?.Status is int status)
+            er_list = er_list.Where(er => er.Envelope?.Status == status);
+
+        if(receiverQuery?.Id is int id)
+            er_list = er_list.Where(er => er.Receiver?.Id == id);
+
+        if (receiverQuery?.Signature is string signature)
+            er_list = er_list.Where(er => er.Receiver?.Signature == signature);
+
         var dto_list = _mapper.Map<IEnumerable<EnvelopeReceiverDto>>(er_list);
         return Result.Success(dto_list);
     }

EnvelopeGenerator.BBTests/frmFinalizePDF.vb

@@ -4,6 +4,7 @@ Imports GdPicture14
 Imports Newtonsoft.Json.Linq
 Imports EnvelopeGenerator.Common.Jobs
 Imports System.IO
+Imports EnvelopeGenerator.Common.Jobs.FinalizeDocument
 
 Public Class frmFinalizePDF
     Private Const CONNECTIONSTRING = "Server=sDD-VMP04-SQL17\DD_DEVELOP01;Database=DD_ECM;User Id=sa;Password=+bk8oAbbQP1AzoHtvZUbd+Mbok2f8Fl4miEx1qssJ5yEaEWoQJ9prg4L14fURpPnqi1WMNs9fE4=;"
@@ -15,14 +16,14 @@ Public Class frmFinalizePDF
     Private Manager As AnnotationManager
     Private PDFBurner As FinalizeDocument.PDFBurner
     Private pGDPictureLicenseKey As String = "kG1Qf9PwmqgR8aDmIW2zI_ebj48RzqAJegRxcystEmkbTGQqfkNBdFOXIb6C_A00Ra8zZkrHdfjqzOPXK7kgkF2YDhvrqKfqh4WDug2vOt0qO31IommzkANSuLjZ4zmraoubyEVd25rE3veQ2h_j7tGIoH_LyIHmy24GaXsxdG0yCzIBMdiLbMMMDwcPY-809KeZ83Grv76OVhFvcbBWyYc251vou1N-kGg5_ZlHDgfWoY85gTLRxafjD3KS_i9ARW4BMiy36y8n7UP2jN8kGRnW_04ubpFtfjJqvtsrP_J9D0x7bqV8xtVtT5JI6dpKsVTiMgDCrIcoFSo5gCC1fw9oUopX4TDCkBQttO4-WHBlOeq9dG5Yb0otonVmJKaQA2tP6sMR-lZDs3ql_WI9t91yPWgpssrJUxSHDd27_LMTH_owJIqkF3NOJd9mYQuAv22oNKFYbH8e41pVKb8cT33Y9CgcQ_sy6YDA5PTuIRi67mjKge_nD9rd0IN213Ir9M_EFWqg9e4haWzIdHXQUo0md70kVhPX4UIH_BKJnxEEnFfoFRNMh77bB0N4jkcBEHPl-ghOERv8dOztf4vCnNpzzWvcLD2cqWIm6THy8XGGq9h4hp8aEreRleSMwv9QQAC7mjLwhQ1rBYkpUHlpTjhTLnMwHknl6HH0Z6zzmsgkRKVyfquv94Pd7QbQfZrRka0ss_48pf9p8hAywEn81Q=="
-    Private ReadOnly _ignoredLabels As New List(Of String) From {"Date", "Datum", "ZIP", "PLZ", "Place", "Ort", "Position", "Stellung"}
+    Private ReadOnly _pdfBurnerParams As New PDFBurnerParams()
 
     Private Sub frmFinalizePDF_Load(sender As Object, e As EventArgs) Handles MyBase.Load
         LogConfig = New LogConfig(LogConfig.PathType.CustomPath, Application.StartupPath)
         Database = New MSSQLServer(LogConfig, MSSQLServer.DecryptConnectionString(CONNECTIONSTRING))
 
 
-        PDFBurner = New FinalizeDocument.PDFBurner(LogConfig, pGDPictureLicenseKey, _ignoredLabels)
+        PDFBurner = New FinalizeDocument.PDFBurner(LogConfig, pGDPictureLicenseKey, _pdfBurnerParams)
 
         Viewer = New GdViewer()
         Manager = New AnnotationManager()

EnvelopeGenerator.Common/Constants.vb

@@ -21,7 +21,6 @@
         DocumentOpened = 2004
         DocumentSigned = 2005
         DocumentForwarded = 4001
-        SignatureConfirmed = 2006
         DocumentRejected = 2007
         EnvelopeShared = 2008
         EnvelopeViewed = 2009
@@ -33,6 +32,20 @@
         DocumentMod_Rotation = 4001
     End Enum
 
+    Public Class Status
+        Public Shared ReadOnly NonHist As IReadOnlyList(Of EnvelopeStatus) = New List(Of EnvelopeStatus) From {
+            EnvelopeStatus.Invalid,
+            EnvelopeStatus.EnvelopeSaved,
+            EnvelopeStatus.EnvelopeSent,
+            EnvelopeStatus.EnvelopePartlySigned
+        }
+
+        Public Shared ReadOnly RelatedToFormApp As IReadOnlyList(Of EnvelopeStatus) = New List(Of EnvelopeStatus) From {
+            EnvelopeStatus.EnvelopeCreated,
+            EnvelopeStatus.DocumentMod_Rotation
+        }
+    End Class
+
     'TODO: standardize in xwiki
     Public Enum ReferenceType
         Receiver = 0
@@ -130,7 +143,7 @@
     Public Const DATABASE = "DATABASE"
     Public Const LOGCONFIG = "LOGCONFIG"
     Public Const GDPICTURE = "GDPICTURE"
-    Public Const IGNORED_LABELS = "IgnoredLabels"
+    Public Const PDF_BURNER_PARAMS = "PDFBurnerParams"
 
     Public Const GREEN_300 = "#bbf7d0"
     Public Const RED_300 = "#fecaca"

EnvelopeGenerator.Common/Entities/Envelope.vb

@@ -25,6 +25,7 @@
     Public Property Message As String = My.Resources.Envelope.Please_read_and_sign_this_document
 
     Public Property AddedWhen As Date
+    Public Property ChangedWhen As Date
     Public Property User As New User()
 
     Public Property Documents As New List(Of EnvelopeDocument)

EnvelopeGenerator.Common/Entities/User.vb

@@ -8,6 +8,7 @@
 
     Public Property HasAccess As Boolean
     Public Property IsAdmin As Boolean
+    Public Property GhostModeActive As Boolean
 
     Public ReadOnly Property FullName() As String
         Get

EnvelopeGenerator.Common/EnvelopeGenerator.Common.vbproj

@@ -281,6 +281,7 @@
     <Compile Include="Entities\ElementStatus.vb" />
     <Compile Include="Entities\EmailData.vb" />
     <Compile Include="Entities\EmailTemplate.vb" />
+    <Compile Include="Jobs\FinalizeDocument\PDFBurnerParams.vb" />
     <Compile Include="Services\TemplateService.vb" />
     <Compile Include="Entities\Envelope.vb" />
     <Compile Include="Entities\EnvelopeDocument.vb" />

EnvelopeGenerator.Common/Jobs/FinalizeDocument/FinalizeDocumentJob.vb

@@ -74,8 +74,8 @@ Namespace Jobs
                 InitializeServices(oState)
 
                 Logger.Debug("Loading PDFBurner..")
-                Dim ignoredLabels As List(Of String) = pContext.MergedJobDataMap.Item(Constants.IGNORED_LABELS)
-                PDFBurner = New PDFBurner(LogConfig, oGdPictureKey, ignoredLabels)
+                Dim pdfBurnerParams As PDFBurnerParams = pContext.MergedJobDataMap.Item(Constants.PDF_BURNER_PARAMS)
+                PDFBurner = New PDFBurner(LogConfig, oGdPictureKey, pdfBurnerParams)
 
                 Logger.Debug("Loading PDFMerger..")
                 PDFMerger = New PDFMerger(LogConfig, oGdPictureKey)

EnvelopeGenerator.Common/Jobs/FinalizeDocument/PDFBurner.vb

@@ -16,9 +16,9 @@ Namespace Jobs.FinalizeDocument
         Private Const ANNOTATION_TYPE_IMAGE = "pspdfkit/image"
         Private Const ANNOTATION_TYPE_INK = "pspdfkit/ink"
         Private Const ANNOTATION_TYPE_WIDGET = "pspdfkit/widget"
-        Private Property _ignoredLabels As List(Of String)
+        Private Property _pdfBurnerParams As PDFBurnerParams
 
-        Public Sub New(pLogConfig As LogConfig, pGDPictureLicenseKey As String, ignoredLabels As List(Of String))
+        Public Sub New(pLogConfig As LogConfig, pGDPictureLicenseKey As String, pdfBurnerParams As PDFBurnerParams)
             MyBase.New(pLogConfig)
 
             LicenseManager = New LicenseManager()
@@ -26,7 +26,7 @@ Namespace Jobs.FinalizeDocument
 
             Manager = New AnnotationManager()
 
-            _ignoredLabels = ignoredLabels
+            _pdfBurnerParams = pdfBurnerParams
         End Sub
 
         Public Function BurnInstantJSONAnnotationsToPDF(pSourceBuffer As Byte(), pInstantJSONList As List(Of String)) As Byte()
@@ -68,7 +68,8 @@ Namespace Jobs.FinalizeDocument
         Private Function AddInstantJSONAnnotationToPDF(pInstantJSON As String) As Boolean
             Try
                 Dim oAnnotationData = JsonConvert.DeserializeObject(Of AnnotationData)(pInstantJSON)
-
+                oAnnotationData.annotations.Reverse()
+                Dim formFieldIndex = 0
                 For Each oAnnotation In oAnnotationData.annotations
                     Logger.Debug("Adding AnnotationID: " + oAnnotation.id)
                     Select Case oAnnotation.type
@@ -81,10 +82,12 @@ Namespace Jobs.FinalizeDocument
                         Case ANNOTATION_TYPE_WIDGET
                             'Add form field values
                             Dim formFieldValue = oAnnotationData.formFieldValues.FirstOrDefault(Function(fv) fv.name = oAnnotation.id)
-                            If formFieldValue IsNot Nothing AndAlso Not _ignoredLabels.Contains(formFieldValue.value) Then
-                                AddFormFieldValue(oAnnotation, formFieldValue)
+                            If formFieldValue IsNot Nothing AndAlso Not _pdfBurnerParams.IgnoredLabels.Contains(formFieldValue.value) Then
+                                AddFormFieldValue(oAnnotation, formFieldValue, formFieldIndex)
+                                formFieldIndex += 1
                             End If
                     End Select
+
                 Next
 
                 Return True
@@ -144,13 +147,13 @@ Namespace Jobs.FinalizeDocument
 
         End Function
 
-        Private Function AddFormFieldValue(pAnnotation As Annotation, formFieldValue As FormFieldValue) As Boolean
+        Private Function AddFormFieldValue(pAnnotation As Annotation, formFieldValue As FormFieldValue, index As Integer) As Boolean
             Try
                 ' Convert pixels to Inches
                 Dim oBounds = pAnnotation.bbox.Select(AddressOf ToInches).ToList()
 
                 Dim oX = oBounds.Item(0)
-                Dim oY = oBounds.Item(1)
+                Dim oY = oBounds.Item(1) + _pdfBurnerParams.YOffset * index + _pdfBurnerParams.TopMargin
                 Dim oWidth = oBounds.Item(2)
                 Dim oHeight = oBounds.Item(3)
 
@@ -159,9 +162,9 @@ Namespace Jobs.FinalizeDocument
                 Dim ant = Manager.AddTextAnnot(oX, oY, oWidth, oHeight, formFieldValue.value)
 
                 ' Set the font properties
-                ant.FontName = "Arial"
-                ant.FontSize = 8
-                ant.FontStyle = FontStyle.Italic
+                ant.FontName = _pdfBurnerParams.FontName
+                ant.FontSize = _pdfBurnerParams.FontSize
+                ant.FontStyle = _pdfBurnerParams.FontStyle
                 Manager.SaveAnnotationsToPage()
                 Return True
             Catch ex As Exception

EnvelopeGenerator.Common/Jobs/FinalizeDocument/PDFBurnerParams.vb

@@ -0,0 +1,16 @@
+Imports System.Drawing
+Namespace Jobs.FinalizeDocument
+    Public Class PDFBurnerParams
+        Public Property IgnoredLabels As New List(Of String) From {"Date", "Datum", "ZIP", "PLZ", "Place", "Ort", "Position", "Stellung"}
+
+        Public Property TopMargin As Double = 0.1
+
+        Public Property YOffset As Double = -0.3
+
+        Public Property FontName As String = "Arial"
+
+        Public Property FontSize As Integer = 8
+
+        Public Property FontStyle As FontStyle = FontStyle.Italic
+    End Class
+End Namespace

EnvelopeGenerator.Common/Models/EnvelopeModel.vb

@@ -35,6 +35,7 @@ Public Class EnvelopeModel
             .Language = pRow.ItemEx("LANGUAGE", "de-DE"),
             .Status = ObjectEx.ToEnum(Of Constants.EnvelopeStatus)(pRow.ItemEx("STATUS", Constants.EnvelopeStatus.EnvelopeCreated.ToString())),
             .AddedWhen = pRow.Item("ADDED_WHEN"),
+            .ChangedWhen = pRow.Item("CHANGED_WHEN"),
             .CertificationType = ObjectEx.ToEnum(Of Constants.CertificationType)(pRow.ItemEx("CERTIFICATION_TYPE", Constants.CertificationType.AdvancedElectronicSignature.ToString())),
             .User = New User(),
             .ExpiresWhen = pRow.ItemEx(Of Date)("EXPIRES_WHEN", Nothing),

EnvelopeGenerator.Common/Models/UserModel.vb

@@ -33,10 +33,10 @@ Public Class UserModel
             Dim oRow = oTable.Rows.Item(0)
             Dim oHasAccess = oRow.ItemEx("MODULE_ACCESS", False)
             Dim oIsAdmin = oRow.ItemEx("IS_ADMIN", False)
-
+            Dim oGhostmode = oRow.ItemEx("GHOST_MODE_OVERRIDE", False)
             pUser.HasAccess = oHasAccess
             pUser.IsAdmin = oIsAdmin
-
+            pUser.GhostModeActive = oGhostmode
             Return pUser
         Catch ex As Exception
             Logger.Error(ex)

EnvelopeGenerator.Common/My Project/AssemblyInfo.vb

@@ -31,5 +31,5 @@ Imports System.Runtime.InteropServices
 ' indem Sie "*" wie unten gezeigt eingeben:
 ' <Assembly: AssemblyVersion("1.0.*")>
 
-<Assembly: AssemblyVersion("2.4.3.0")>
-<Assembly: AssemblyFileVersion("2.4.3.0")>
+<Assembly: AssemblyVersion("2.4.4.0")>
+<Assembly: AssemblyFileVersion("2.4.4.0")>

EnvelopeGenerator.Common/Strings/Envelope.resx

@@ -220,6 +220,9 @@ Pattern: +491234567890</value>
   <data name="Missing Receivers" xml:space="preserve">
     <value>Fehlende Empfänger</value>
   </data>
+  <data name="ModificationOriginFile_FormFields" xml:space="preserve">
+    <value />
+  </data>
   <data name="New Envelope" xml:space="preserve">
     <value>Neuer Umschlag</value>
   </data>

EnvelopeGenerator.Common/Strings/Envelope1.Designer.vb

@@ -371,6 +371,15 @@ Namespace My.Resources
             End Get
         End Property
         
+        '''<summary>
+        '''  Sucht eine lokalisierte Zeichenfolge, die  ähnelt.
+        '''</summary>
+        Public Shared ReadOnly Property ModificationOriginFile_FormFields() As String
+            Get
+                Return ResourceManager.GetString("ModificationOriginFile_FormFields", resourceCulture)
+            End Get
+        End Property
+        
         '''<summary>
         '''  Sucht eine lokalisierte Zeichenfolge, die Neuer Umschlag ähnelt.
         '''</summary>

EnvelopeGenerator.Common/Strings/Model.Designer.vb

@@ -325,6 +325,15 @@ Namespace My.Resources
             End Get
         End Property
         
+        '''<summary>
+        '''  Sucht eine lokalisierte Zeichenfolge, die  ähnelt.
+        '''</summary>
+        Public Shared ReadOnly Property ModificationOriginFile_FormFields() As String
+            Get
+                Return ResourceManager.GetString("ModificationOriginFile_FormFields", resourceCulture)
+            End Get
+        End Property
+        
         '''<summary>
         '''  Sucht eine lokalisierte Zeichenfolge, die Nein ähnelt.
         '''</summary>

EnvelopeGenerator.Domain/Entities/EmailTemplate.cs

@@ -1,4 +1,5 @@
 using DigitalData.Core.Abstractions;
+using System.ComponentModel;
 using System.ComponentModel.DataAnnotations;
 using System.ComponentModel.DataAnnotations.Schema;
 
@@ -20,5 +21,13 @@ namespace EnvelopeGenerator.Domain.Entities
 
         [Column("SUBJECT", TypeName = "nvarchar(512)")]
         public string? Subject { get; set; }
+
+        [Required]
+        [Column("ADDED_WHEN", TypeName = "datetime")]
+        [DefaultValue("GETDATE()")]
+        public DateTime AddedWhen { get; set; }
+
+        [Column("CHANGED_WHEN", TypeName = "datetime")]
+        public DateTime? ChangedWhen { get; set; }
     }
 }

EnvelopeGenerator.Domain/Entities/Envelope.cs

@@ -28,7 +28,6 @@ namespace EnvelopeGenerator.Domain.Entities
         [Column("ENVELOPE_UUID", TypeName = "nvarchar(36)")]
         public required string Uuid { get; init; }
 
-        [Required]
         [Column("MESSAGE", TypeName = "nvarchar(max)")]
         public string? Message { get; set; }
 
@@ -52,7 +51,7 @@ namespace EnvelopeGenerator.Domain.Entities
         public int? ContractType { get; set; }
 
         [Column("LANGUAGE", TypeName = "nvarchar(5)")]
-        public required string Language { get; set; }
+        public string? Language { get; set; }
 
         [Column("SEND_REMINDER_EMAILS")]
         public bool? SendReminderEmails { get; set; }
@@ -87,6 +86,9 @@ namespace EnvelopeGenerator.Domain.Entities
         [Column("TFA_ENABLED", TypeName = "bit")]
         public bool TFAEnabled { get; set; }
 
+        [Column("DOC_RESULT", TypeName = "varbinary(max)")]
+        public byte[]? DocResult { get; init; }
+
         /// <summary>
         /// The sender of envelope
         /// </summary>

EnvelopeGenerator.Domain/EnvelopeGenerator.Domain.csproj

@@ -7,7 +7,7 @@
   </PropertyGroup>
 
   <ItemGroup>
-    <PackageReference Include="DigitalData.Core.Abstractions" Version="3.4.3" />
+    <PackageReference Include="DigitalData.Core.Abstractions" Version="3.6.0" />
     <PackageReference Include="DigitalData.EmailProfilerDispatcher.Abstraction" Version="3.0.0" />
     <PackageReference Include="UserManager.Domain" Version="3.0.2" />
   </ItemGroup>

EnvelopeGenerator.Extensions/EnvelopeGenerator.Extensions.csproj

@@ -13,6 +13,7 @@
 
   <ItemGroup>
     <PackageReference Include="HtmlSanitizer" Version="8.0.865" />
+    <PackageReference Include="Microsoft.Extensions.Caching.Memory" Version="9.0.4" />
     <PackageReference Include="Microsoft.Extensions.Localization.Abstractions" Version="7.0.19" />
     <PackageReference Include="Microsoft.Extensions.Logging" Version="8.0.0" />
     <PackageReference Include="Otp.NET" Version="1.4.0" />

EnvelopeGenerator.Extensions/MemoryCacheExtensions.cs

@@ -0,0 +1,16 @@
+using Microsoft.Extensions.Caching.Memory;
+
+namespace EnvelopeGenerator.Extensions;
+
+public static class MemoryCacheExtensions
+{
+    private static readonly Guid BaseId = Guid.NewGuid();
+
+    public static IDictionary<string, int> GetEnumAsDictionary<TEnum>(this IMemoryCache memoryCache)
+        where TEnum : Enum
+        => memoryCache.GetOrCreate(BaseId + typeof(TEnum).FullName, _ =>
+               Enum.GetValues(typeof(TEnum))
+                   .Cast<TEnum>()
+                   .ToDictionary(e => e.ToString(), e => Convert.ToInt32(e)))
+           ?? throw new InvalidOperationException($"Failed to cache or retrieve enum dictionary for type '{typeof(TEnum).FullName}'.");
+}

EnvelopeGenerator.Form/EnvelopeGenerator.Form.vbproj

@@ -416,6 +416,7 @@
     </EmbeddedResource>
     <EmbeddedResource Include="frmGhostMode.resx">
       <DependentUpon>frmGhostMode.vb</DependentUpon>
+      <SubType>Designer</SubType>
     </EmbeddedResource>
     <EmbeddedResource Include="frmMain.en.resx">
       <DependentUpon>frmMain.vb</DependentUpon>

EnvelopeGenerator.Form/Helper/FlattenFormFields.vb

@@ -18,6 +18,9 @@ Public Class FlattenFormFields
 
                 Dim newFilesPath As String = Path.Combine(oFolder, "InputFieldsFlattend_" & Path.GetFileName(pFilePath))
                 If gdpicturePdf.SaveToFile(newFilesPath) = GdPictureStatus.OK Then
+                    Dim oNameofFile = Path.GetFileName(newFilesPath)
+                    MsgBox("Your PDF-file contained form-fields!" & vbNewLine & "We needed to adapt the file and created an new version!" & vbNewLine &
+                        $"New filename: {oNameofFile}", MsgBoxStyle.Exclamation, "Information")
                     Return newFilesPath
                 End If
 

EnvelopeGenerator.Form/frmEnvelopeEditor.vb

@@ -200,7 +200,7 @@ Partial Public Class frmEnvelopeEditor
     Private Sub btnSave_ItemClick(sender As Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles btnSave.ItemClick
         Try
             If SaveEnvelopeWithOutValidation() = True Then
-                bsitm_info.Caption = "Data saved succeddfully " + Now.ToString
+                bsitm_info.Caption = "Data saved successfully " + Now.ToString
             Else
                 bsitm_info.Caption = "Exceprion - Error saving Data. Check LOG"
             End If

EnvelopeGenerator.Form/frmMain.Designer.vb

@@ -47,6 +47,7 @@ Partial Class frmMain
         Me.colStatus = New DevExpress.XtraGrid.Columns.GridColumn()
         Me.colTitle = New DevExpress.XtraGrid.Columns.GridColumn()
         Me.colAddedWhen = New DevExpress.XtraGrid.Columns.GridColumn()
+        Me.GridColumn2 = New DevExpress.XtraGrid.Columns.GridColumn()
         Me.RibbonControl = New DevExpress.XtraBars.Ribbon.RibbonControl()
         Me.btnCreateEnvelope = New DevExpress.XtraBars.BarButtonItem()
         Me.btnEditEnvelope = New DevExpress.XtraBars.BarButtonItem()
@@ -88,14 +89,16 @@ Partial Class frmMain
         Me.GridColumn4 = New DevExpress.XtraGrid.Columns.GridColumn()
         Me.GridColumn5 = New DevExpress.XtraGrid.Columns.GridColumn()
         Me.GridColumn7 = New DevExpress.XtraGrid.Columns.GridColumn()
+        Me.GridColumn1 = New DevExpress.XtraGrid.Columns.GridColumn()
         Me.XtraTabPageAdmin = New DevExpress.XtraTab.XtraTabPage()
         Me.SplitContainerControl2 = New DevExpress.XtraEditors.SplitContainerControl()
         Me.GridControlData = New DevExpress.XtraGrid.GridControl()
         Me.GridViewData = New DevExpress.XtraGrid.Views.Grid.GridView()
         Me.PanelControl1 = New DevExpress.XtraEditors.PanelControl()
         Me.GroupControl2 = New DevExpress.XtraEditors.GroupControl()
-        Me.GroupControl1 = New DevExpress.XtraEditors.GroupControl()
+        Me.btnEvvallUs_lastmonth = New DevExpress.XtraEditors.SimpleButton()
         Me.btnEvvallUs_thismonth = New DevExpress.XtraEditors.SimpleButton()
+        Me.GroupControl1 = New DevExpress.XtraEditors.GroupControl()
         Me.btnEnvelopes_All = New DevExpress.XtraEditors.SimpleButton()
         Me.btnEnvelopes_thisYear = New DevExpress.XtraEditors.SimpleButton()
         Me.btnEnvelopes_lastmonth = New DevExpress.XtraEditors.SimpleButton()
@@ -103,7 +106,6 @@ Partial Class frmMain
         Me.RefreshTimer = New System.Windows.Forms.Timer(Me.components)
         Me.SaveFileDialog1 = New System.Windows.Forms.SaveFileDialog()
         Me.XtraSaveFileDialog1 = New DevExpress.XtraEditors.XtraSaveFileDialog(Me.components)
-        Me.btnEvvallUs_lastmonth = New DevExpress.XtraEditors.SimpleButton()
         CType(Me.SplitContainerControl1, System.ComponentModel.ISupportInitialize).BeginInit()
         CType(Me.SplitContainerControl1.Panel1, System.ComponentModel.ISupportInitialize).BeginInit()
         Me.SplitContainerControl1.Panel1.SuspendLayout()
@@ -279,11 +281,12 @@ Partial Class frmMain
         '
         'ViewEnvelopes
         '
-        Me.ViewEnvelopes.Columns.AddRange(New DevExpress.XtraGrid.Columns.GridColumn() {Me.colEnvelopeId, Me.colContractType, Me.colStatus, Me.colTitle, Me.colAddedWhen})
+        Me.ViewEnvelopes.Columns.AddRange(New DevExpress.XtraGrid.Columns.GridColumn() {Me.colEnvelopeId, Me.colContractType, Me.colStatus, Me.colTitle, Me.colAddedWhen, Me.GridColumn2})
         Me.ViewEnvelopes.GridControl = Me.GridEnvelopes
         Me.ViewEnvelopes.Name = "ViewEnvelopes"
         Me.ViewEnvelopes.OptionsBehavior.Editable = False
         Me.ViewEnvelopes.OptionsBehavior.ReadOnly = True
+        Me.ViewEnvelopes.OptionsView.ShowAutoFilterRow = True
         Me.ViewEnvelopes.OptionsView.ShowIndicator = False
         '
         'colEnvelopeId
@@ -318,6 +321,14 @@ Partial Class frmMain
         Me.colAddedWhen.FieldName = "AddedWhen"
         Me.colAddedWhen.Name = "colAddedWhen"
         '
+        'GridColumn2
+        '
+        resources.ApplyResources(Me.GridColumn2, "GridColumn2")
+        Me.GridColumn2.DisplayFormat.FormatString = "G"
+        Me.GridColumn2.DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime
+        Me.GridColumn2.FieldName = "ChangedWhen"
+        Me.GridColumn2.Name = "GridColumn2"
+        '
         'RibbonControl
         '
         Me.RibbonControl.ExpandCollapseItem.Id = 0
@@ -637,11 +648,12 @@ Partial Class frmMain
         '
         'ViewCompleted
         '
-        Me.ViewCompleted.Columns.AddRange(New DevExpress.XtraGrid.Columns.GridColumn() {Me.GridColumn3, Me.GridColumn4, Me.GridColumn5, Me.GridColumn7})
+        Me.ViewCompleted.Columns.AddRange(New DevExpress.XtraGrid.Columns.GridColumn() {Me.GridColumn3, Me.GridColumn4, Me.GridColumn5, Me.GridColumn7, Me.GridColumn1})
         Me.ViewCompleted.GridControl = Me.GridCompleted
         Me.ViewCompleted.Name = "ViewCompleted"
         Me.ViewCompleted.OptionsBehavior.Editable = False
         Me.ViewCompleted.OptionsBehavior.ReadOnly = True
+        Me.ViewCompleted.OptionsView.ShowAutoFilterRow = True
         Me.ViewCompleted.OptionsView.ShowIndicator = False
         '
         'GridColumn3
@@ -665,9 +677,19 @@ Partial Class frmMain
         'GridColumn7
         '
         resources.ApplyResources(Me.GridColumn7, "GridColumn7")
+        Me.GridColumn7.DisplayFormat.FormatString = "G"
+        Me.GridColumn7.DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime
         Me.GridColumn7.FieldName = "AddedWhen"
         Me.GridColumn7.Name = "GridColumn7"
         '
+        'GridColumn1
+        '
+        resources.ApplyResources(Me.GridColumn1, "GridColumn1")
+        Me.GridColumn1.DisplayFormat.FormatString = "G"
+        Me.GridColumn1.DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime
+        Me.GridColumn1.FieldName = "ChangedWhen"
+        Me.GridColumn1.Name = "GridColumn1"
+        '
         'XtraTabPageAdmin
         '
         Me.XtraTabPageAdmin.Controls.Add(Me.SplitContainerControl2)
@@ -688,7 +710,7 @@ Partial Class frmMain
         'SplitContainerControl2.Panel2
         '
         resources.ApplyResources(Me.SplitContainerControl2.Panel2, "SplitContainerControl2.Panel2")
-        Me.SplitContainerControl2.SplitterPosition = 819
+        Me.SplitContainerControl2.SplitterPosition = 907
         '
         'GridControlData
         '
@@ -702,6 +724,7 @@ Partial Class frmMain
         '
         Me.GridViewData.GridControl = Me.GridControlData
         Me.GridViewData.Name = "GridViewData"
+        Me.GridViewData.OptionsView.ShowAutoFilterRow = True
         '
         'PanelControl1
         '
@@ -717,6 +740,20 @@ Partial Class frmMain
         resources.ApplyResources(Me.GroupControl2, "GroupControl2")
         Me.GroupControl2.Name = "GroupControl2"
         '
+        'btnEvvallUs_lastmonth
+        '
+        Me.btnEvvallUs_lastmonth.Appearance.BackColor = System.Drawing.Color.MediumPurple
+        Me.btnEvvallUs_lastmonth.Appearance.Options.UseBackColor = True
+        resources.ApplyResources(Me.btnEvvallUs_lastmonth, "btnEvvallUs_lastmonth")
+        Me.btnEvvallUs_lastmonth.Name = "btnEvvallUs_lastmonth"
+        '
+        'btnEvvallUs_thismonth
+        '
+        Me.btnEvvallUs_thismonth.Appearance.BackColor = System.Drawing.Color.MediumSlateBlue
+        Me.btnEvvallUs_thismonth.Appearance.Options.UseBackColor = True
+        resources.ApplyResources(Me.btnEvvallUs_thismonth, "btnEvvallUs_thismonth")
+        Me.btnEvvallUs_thismonth.Name = "btnEvvallUs_thismonth"
+        '
         'GroupControl1
         '
         Me.GroupControl1.Controls.Add(Me.btnEnvelopes_All)
@@ -726,13 +763,6 @@ Partial Class frmMain
         resources.ApplyResources(Me.GroupControl1, "GroupControl1")
         Me.GroupControl1.Name = "GroupControl1"
         '
-        'btnEvvallUs_thismonth
-        '
-        Me.btnEvvallUs_thismonth.Appearance.BackColor = System.Drawing.Color.MediumSlateBlue
-        Me.btnEvvallUs_thismonth.Appearance.Options.UseBackColor = True
-        resources.ApplyResources(Me.btnEvvallUs_thismonth, "btnEvvallUs_thismonth")
-        Me.btnEvvallUs_thismonth.Name = "btnEvvallUs_thismonth"
-        '
         'btnEnvelopes_All
         '
         Me.btnEnvelopes_All.Appearance.BackColor = System.Drawing.Color.MediumTurquoise
@@ -773,13 +803,6 @@ Partial Class frmMain
         '
         Me.XtraSaveFileDialog1.FileName = "XtraSaveFileDialog1"
         '
-        'btnEvvallUs_lastmonth
-        '
-        Me.btnEvvallUs_lastmonth.Appearance.BackColor = System.Drawing.Color.MediumPurple
-        Me.btnEvvallUs_lastmonth.Appearance.Options.UseBackColor = True
-        resources.ApplyResources(Me.btnEvvallUs_lastmonth, "btnEvvallUs_lastmonth")
-        Me.btnEvvallUs_lastmonth.Name = "btnEvvallUs_lastmonth"
-        '
         'frmMain
         '
         resources.ApplyResources(Me, "$this")
@@ -906,4 +929,6 @@ Partial Class frmMain
     Friend WithEvents BarStaticItemGhost As DevExpress.XtraBars.BarStaticItem
     Friend WithEvents btnEvvallUs_thismonth As DevExpress.XtraEditors.SimpleButton
     Friend WithEvents btnEvvallUs_lastmonth As DevExpress.XtraEditors.SimpleButton
+    Friend WithEvents GridColumn1 As DevExpress.XtraGrid.Columns.GridColumn
+    Friend WithEvents GridColumn2 As DevExpress.XtraGrid.Columns.GridColumn
 End Class

EnvelopeGenerator.Form/frmMain.resx

@@ -265,7 +265,7 @@
     <value>2</value>
   </data>
   <data name="colContractType.Width" type="System.Int32, mscorlib">
-    <value>120</value>
+    <value>112</value>
   </data>
   <data name="colStatus.Caption" xml:space="preserve">
     <value>Status</value>
@@ -277,7 +277,7 @@
     <value>1</value>
   </data>
   <data name="colStatus.Width" type="System.Int32, mscorlib">
-    <value>193</value>
+    <value>180</value>
   </data>
   <data name="colTitle.Caption" xml:space="preserve">
     <value>Titel</value>
@@ -289,7 +289,7 @@
     <value>0</value>
   </data>
   <data name="colTitle.Width" type="System.Int32, mscorlib">
-    <value>575</value>
+    <value>538</value>
   </data>
   <data name="colAddedWhen.Caption" xml:space="preserve">
     <value>Erstellt am</value>
@@ -301,7 +301,19 @@
     <value>3</value>
   </data>
   <data name="colAddedWhen.Width" type="System.Int32, mscorlib">
-    <value>196</value>
+    <value>130</value>
+  </data>
+  <data name="GridColumn2.Caption" xml:space="preserve">
+    <value>Zuletzt geändert am</value>
+  </data>
+  <data name="GridColumn2.Visible" type="System.Boolean, mscorlib">
+    <value>True</value>
+  </data>
+  <data name="GridColumn2.VisibleIndex" type="System.Int32, mscorlib">
+    <value>4</value>
+  </data>
+  <data name="GridColumn2.Width" type="System.Int32, mscorlib">
+    <value>130</value>
   </data>
   <data name="RibbonControl.ExpandCollapseItem.ImageOptions.ImageIndex" type="System.Int32, mscorlib">
     <value>0</value>
@@ -1052,7 +1064,7 @@
     <value>2</value>
   </data>
   <data name="GridColumn3.Width" type="System.Int32, mscorlib">
-    <value>120</value>
+    <value>100</value>
   </data>
   <data name="GridColumn4.Caption" xml:space="preserve">
     <value>Status</value>
@@ -1064,7 +1076,7 @@
     <value>1</value>
   </data>
   <data name="GridColumn4.Width" type="System.Int32, mscorlib">
-    <value>195</value>
+    <value>163</value>
   </data>
   <data name="GridColumn5.Caption" xml:space="preserve">
     <value>Titel</value>
@@ -1076,7 +1088,7 @@
     <value>0</value>
   </data>
   <data name="GridColumn5.Width" type="System.Int32, mscorlib">
-    <value>574</value>
+    <value>482</value>
   </data>
   <data name="GridColumn7.Caption" xml:space="preserve">
     <value>Erstellt am</value>
@@ -1088,7 +1100,19 @@
     <value>3</value>
   </data>
   <data name="GridColumn7.Width" type="System.Int32, mscorlib">
-    <value>195</value>
+    <value>120</value>
+  </data>
+  <data name="GridColumn1.Caption" xml:space="preserve">
+    <value>Zuletzt geändert am</value>
+  </data>
+  <data name="GridColumn1.Visible" type="System.Boolean, mscorlib">
+    <value>True</value>
+  </data>
+  <data name="GridColumn1.VisibleIndex" type="System.Int32, mscorlib">
+    <value>4</value>
+  </data>
+  <data name="GridColumn1.Width" type="System.Int32, mscorlib">
+    <value>120</value>
   </data>
   <data name="GridCompleted.Size" type="System.Drawing.Size, System.Drawing">
     <value>1088, 469</value>
@@ -1139,7 +1163,7 @@
     <value>0, 0</value>
   </data>
   <data name="GridControlData.Size" type="System.Drawing.Size, System.Drawing">
-    <value>819, 390</value>
+    <value>907, 390</value>
   </data>
   <data name="GridControlData.TabIndex" type="System.Int32, mscorlib">
     <value>1</value>
@@ -1262,7 +1286,7 @@
     <value>2</value>
   </data>
   <data name="GroupControl2.Text" xml:space="preserve">
-    <value>Umschläge alle User</value>
+    <value>Umschläge alle User (abrechnungsrelevant)</value>
   </data>
   <data name="&gt;&gt;GroupControl2.Name" xml:space="preserve">
     <value>GroupControl2</value>
@@ -1717,6 +1741,12 @@
   <data name="&gt;&gt;colAddedWhen.Type" xml:space="preserve">
     <value>DevExpress.XtraGrid.Columns.GridColumn, DevExpress.XtraGrid.v21.2, Version=21.2.4.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a</value>
   </data>
+  <data name="&gt;&gt;GridColumn2.Name" xml:space="preserve">
+    <value>GridColumn2</value>
+  </data>
+  <data name="&gt;&gt;GridColumn2.Type" xml:space="preserve">
+    <value>DevExpress.XtraGrid.Columns.GridColumn, DevExpress.XtraGrid.v21.2, Version=21.2.4.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a</value>
+  </data>
   <data name="&gt;&gt;btnCreateEnvelope.Name" xml:space="preserve">
     <value>btnCreateEnvelope</value>
   </data>
@@ -1939,6 +1969,12 @@
   <data name="&gt;&gt;GridColumn7.Type" xml:space="preserve">
     <value>DevExpress.XtraGrid.Columns.GridColumn, DevExpress.XtraGrid.v21.2, Version=21.2.4.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a</value>
   </data>
+  <data name="&gt;&gt;GridColumn1.Name" xml:space="preserve">
+    <value>GridColumn1</value>
+  </data>
+  <data name="&gt;&gt;GridColumn1.Type" xml:space="preserve">
+    <value>DevExpress.XtraGrid.Columns.GridColumn, DevExpress.XtraGrid.v21.2, Version=21.2.4.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a</value>
+  </data>
   <data name="&gt;&gt;GridViewData.Name" xml:space="preserve">
     <value>GridViewData</value>
   </data>

EnvelopeGenerator.Form/frmMain.vb

@@ -538,7 +538,7 @@ Public Class frmMain
         End If
         bbtnitmEB.Enabled = False
         RefreshTimer.Start()
-        If USER_GHOST_MODE_ACTIVE Then
+        If USER_GHOST_MODE_ACTIVE Or MYUSER.GhostModeActive Then
             frmGhostMode.ShowDialog()
             If USER_GHOST_MODE_USRNAME <> "" Then
                 MyUserModel = New UserModel(MyState)
@@ -546,7 +546,8 @@ Public Class frmMain
                 Dim oUser = MyUserModel.SelectUser()
                 If oUser IsNot Nothing Then
                     MyUserModel.CheckUserLogin(oUser)
-                    BarStaticItemGhost.Caption = $"GhostMode active: {USER_GHOST_MODE_USRNAME} - End signFLOW to quit mode"
+                    BarStaticItemGhost.Caption = $"GhostMode active: {USER_GHOST_MODE_USRNAME} - End signFLOW to quit ghost-mode"
+                    Me.Text = $"GhostMode active: {USER_GHOST_MODE_USRNAME} - End signFLOW to quit ghost - mode"
                     BarStaticItemGhost.Visibility = DevExpress.XtraBars.BarItemVisibility.Always
                     LoadEnvelopeData()
                 End If

EnvelopeGenerator.GeneratorAPI/Controllers/AuthController.cs

@@ -1,201 +1,150 @@
 using DigitalData.Core.Abstractions.Application;
 using DigitalData.UserManager.Application.Contracts;
-using DigitalData.UserManager.Application.DTOs.User;
 using Microsoft.AspNetCore.Authentication.Cookies;
 using Microsoft.AspNetCore.Authentication;
 using Microsoft.AspNetCore.Mvc;
-using System.Security.Claims;
-using DigitalData.UserManager.Application.DTOs.Auth;
 using Microsoft.AspNetCore.Authorization;
 using EnvelopeGenerator.GeneratorAPI.Models;
 
-namespace EnvelopeGenerator.GeneratorAPI.Controllers
+namespace EnvelopeGenerator.GeneratorAPI.Controllers;
+
+/// <summary>
+/// Controller verantwortlich für die Benutzer-Authentifizierung, einschließlich Anmelden, Abmelden und Überprüfung des Authentifizierungsstatus.
+/// </summary>
+[Route("api/[controller]")]
+[ApiController]
+public partial class AuthController : ControllerBase
 {
+    private readonly ILogger<AuthController> _logger;
+    private readonly IUserService _userService;
+    private readonly IDirectorySearchService _dirSearchService;
+
     /// <summary>
-    /// Controller verantwortlich für die Benutzer-Authentifizierung, einschließlich Anmelden, Abmelden und Überprüfung des Authentifizierungsstatus.
+    /// Initializes a new instance of the <see cref="AuthController"/> class.
     /// </summary>
-    [Route("api/[controller]")]
-    [ApiController]
-    public partial class AuthController : ControllerBase
+    /// <param name="logger">The logger instance.</param>
+    /// <param name="userService">The user service instance.</param>
+    /// <param name="dirSearchService">The directory search service instance.</param>
+    public AuthController(ILogger<AuthController> logger, IUserService userService, IDirectorySearchService dirSearchService)
     {
-        private readonly ILogger<AuthController> _logger;
-        private readonly IUserService _userService;
-        private readonly IDirectorySearchService _dirSearchService;
-
-        /// <summary>
-        /// Initializes a new instance of the <see cref="AuthController"/> class.
-        /// </summary>
-        /// <param name="logger">The logger instance.</param>
-        /// <param name="userService">The user service instance.</param>
-        /// <param name="dirSearchService">The directory search service instance.</param>
-        public AuthController(ILogger<AuthController> logger, IUserService userService, IDirectorySearchService dirSearchService)
-        {
-            _logger = logger;
-            _userService = userService;
-            _dirSearchService = dirSearchService;
-        }
-
-        /// <summary>
-        /// Authentifiziert einen Benutzer und generiert ein JWT-Token. Wenn 'cookie' wahr ist, wird das Token als HTTP-Only-Cookie zurückgegeben.
-        /// </summary>
-        /// <param name="login">Benutzeranmeldedaten (Benutzername und Passwort).</param>
-        /// <param name="cookie">Wenn wahr, wird das JWT-Token auch als HTTP-Only-Cookie gesendet.</param>
-        /// <returns>
-        /// Gibt eine HTTP 200 oder 401.
-        /// </returns>
-        /// <remarks>
-        /// Sample request:
-        ///
-        ///     POST /api/auth?cookie=true
-        ///     {
-        ///        "username": "MaxMustermann",
-        ///        "password": "Geheim123!"
-        ///     }
-        ///     
-        ///     POST /api/auth?cookie=true
-        ///     {
-        ///        "id": "1",
-        ///        "password": "Geheim123!"
-        ///     }
-        ///
-        /// </remarks>
-        /// <response code="200">Erfolgreiche Anmeldung. Gibt das JWT-Token im Antwortkörper oder als Cookie zurück, wenn 'cookie' wahr ist.</response>
-        /// <response code="401">Unbefugt. Ungültiger Benutzername oder Passwort.</response>
-        [ProducesResponseType(typeof(string), StatusCodes.Status200OK, "text/javascript")]
-        [ProducesResponseType(typeof(void), StatusCodes.Status401Unauthorized)]
-        [AllowAnonymous]
-        [HttpPost]
-        public async Task<IActionResult> Login([FromBody] Login login, [FromQuery] bool cookie = false)
-        {
-            try
-            {
-                bool isValid = _dirSearchService.ValidateCredentials(login.Username, login.Password);
-
-                if (!isValid)
-                    return Unauthorized();
-
-                //find the user
-                var uRes = await _userService.ReadByUsernameAsync(login.Username);
-                if (!uRes.IsSuccess || uRes.Data is null)
-                {
-                    return Forbid();
-                }
-
-                UserReadDto user = uRes.Data;
-
-                // Create claims
-                var claims = new List<Claim>
-                    {
-                        new (ClaimTypes.NameIdentifier, user.Id.ToString()),
-                        new (ClaimTypes.Name, user.Username),
-                        new (ClaimTypes.Surname, user.Name!),
-                        new (ClaimTypes.GivenName, user.Prename!),
-                        new (ClaimTypes.Email, user.Email!),
-                    };
-
-                // Create claimsIdentity
-                var claimsIdentity = new ClaimsIdentity(claims, CookieAuthenticationDefaults.AuthenticationScheme);
-
-                // Create authProperties
-                var authProperties = new AuthenticationProperties
-                {
-                    IsPersistent = true,
-                    AllowRefresh = true,
-                    ExpiresUtc = DateTime.Now.AddMinutes(180)
-                };
-
-                // Sign in
-                await HttpContext.SignInAsync(
-                    CookieAuthenticationDefaults.AuthenticationScheme,
-                    new ClaimsPrincipal(claimsIdentity),
-                    authProperties);
-
-                return Ok();
-            }
-            catch (Exception ex)
-            {
-                _logger.LogError(ex, "Unexpected error occurred.\n{ErrorMessage}", ex.Message);
-                return StatusCode(StatusCodes.Status500InternalServerError);
-            }
-        }
-
-        /// <summary>
-        /// Authentifiziert einen Benutzer und generiert ein JWT-Token. Das Token wird als HTTP-only-Cookie zurückgegeben.
-        /// </summary>
-        /// <param name="login">Benutzeranmeldedaten (Benutzername und Passwort).</param>
-        /// <returns>
-        /// Gibt eine HTTP 200 oder 401.
-        /// </returns>
-        /// <remarks>
-        /// Sample request:
-        ///
-        ///     POST /api/auth/form
-        ///     {
-        ///        "username": "MaxMustermann",
-        ///        "password": "Geheim123!"
-        ///     }
-        ///
-        /// </remarks>
-        /// <response code="200">Erfolgreiche Anmeldung. Gibt das JWT-Token im Antwortkörper oder als Cookie zurück, wenn 'cookie' wahr ist.</response>
-        /// <response code="401">Unbefugt. Ungültiger Benutzername oder Passwort.</response>
-        [ProducesResponseType(typeof(string), StatusCodes.Status200OK, "text/javascript")]
-        [ProducesResponseType(typeof(void), StatusCodes.Status401Unauthorized)]
-        [AllowAnonymous]
-        [HttpPost]
-        [Route("form")]
-        public async Task<IActionResult> Login([FromForm] Login login)
-        {
-            return await Login(login, true);
-        }
-
-        /// <summary>
-        /// Entfernt das Authentifizierungs-Cookie des Benutzers (AuthCookie)
-        /// </summary>
-        /// <returns>
-        /// Gibt eine HTTP 200 oder 401.
-        /// </returns>
-        /// <remarks>
-        /// Sample request:
-        ///
-        ///     POST /api/auth/logout
-        ///
-        /// </remarks>
-        /// <response code="200">Erfolgreich gelöscht, wenn der Benutzer ein berechtigtes Cookie hat.</response>
-        /// <response code="401">Wenn es kein zugelassenes Cookie gibt, wird „nicht zugelassen“ zurückgegeben.</response>
-        [ProducesResponseType(typeof(string), StatusCodes.Status200OK, "text/javascript")]
-        [ProducesResponseType(typeof(void), StatusCodes.Status401Unauthorized)]
-        [Authorize]
-        [HttpPost("logout")]
-        public async Task<IActionResult> Logout()
-        {
-            try
-            {
-                await HttpContext.SignOutAsync(CookieAuthenticationDefaults.AuthenticationScheme);
-                return Ok();
-            }
-            catch (Exception ex)
-            {
-                _logger.LogError(ex, "Unexpected error occurred.\n{ErrorMessage}", ex.Message);
-                return StatusCode(StatusCodes.Status500InternalServerError);
-            }
-        }
-
-        /// <summary>
-        /// Prüft, ob der Benutzer ein autorisiertes Token hat.
-        /// </summary>
-        /// <returns>Wenn ein autorisiertes Token vorhanden ist HTTP 200 asynchron 401</returns>
-        /// <remarks>
-        /// Sample request:
-        ///
-        ///     GET /api/auth
-        ///
-        /// </remarks>
-        /// <response code="200">Wenn es einen autorisierten Cookie gibt.</response>
-        /// <response code="401">Wenn kein Cookie vorhanden ist oder nicht autorisierte.</response>
-        [ProducesResponseType(typeof(string), StatusCodes.Status200OK, "text/javascript")]
-        [ProducesResponseType(typeof(void), StatusCodes.Status401Unauthorized)]
-        [Authorize]
-        [HttpGet]
-        public IActionResult IsAuthenticated() => Ok();
+        _logger = logger;
+        _userService = userService;
+        _dirSearchService = dirSearchService;
     }
+
+    /// <summary>
+    /// Authentifiziert einen Benutzer und generiert ein JWT-Token. Wenn 'cookie' wahr ist, wird das Token als HTTP-Only-Cookie zurückgegeben.
+    /// </summary>
+    /// <param name="login">Benutzeranmeldedaten (Benutzername und Passwort).</param>
+    /// <param name="cookie">Wenn wahr, wird das JWT-Token auch als HTTP-Only-Cookie gesendet.</param>
+    /// <returns>
+    /// Gibt eine HTTP 200 oder 401.
+    /// </returns>
+    /// <remarks>
+    /// Sample request:
+    ///
+    ///     POST /api/auth?cookie=true
+    ///     {
+    ///        "username": "MaxMustermann",
+    ///        "password": "Geheim123!"
+    ///     }
+    ///     
+    ///     POST /api/auth?cookie=true
+    ///     {
+    ///        "id": "1",
+    ///        "password": "Geheim123!"
+    ///     }
+    ///
+    /// </remarks>
+    /// <response code="200">Erfolgreiche Anmeldung. Gibt das JWT-Token im Antwortkörper oder als Cookie zurück, wenn 'cookie' wahr ist.</response>
+    /// <response code="401">Unbefugt. Ungültiger Benutzername oder Passwort.</response>
+    [ProducesResponseType(typeof(string), StatusCodes.Status200OK, "text/javascript")]
+    [ProducesResponseType(typeof(void), StatusCodes.Status401Unauthorized)]
+    [AllowAnonymous]
+    [HttpPost]
+    public Task<IActionResult> Login([FromBody] Login login, [FromQuery] bool cookie = false)
+    {
+        // added to configure open API (swagger and scalar)
+        throw new NotImplementedException();
+    }
+
+    /// <summary>
+    /// Authentifiziert einen Benutzer und generiert ein JWT-Token. Das Token wird als HTTP-only-Cookie zurückgegeben.
+    /// </summary>
+    /// <param name="login">Benutzeranmeldedaten (Benutzername und Passwort).</param>
+    /// <returns>
+    /// Gibt eine HTTP 200 oder 401.
+    /// </returns>
+    /// <remarks>
+    /// Sample request:
+    ///
+    ///     POST /api/auth/form
+    ///     {
+    ///        "username": "MaxMustermann",
+    ///        "password": "Geheim123!"
+    ///     }
+    ///
+    /// </remarks>
+    /// <response code="200">Erfolgreiche Anmeldung. Gibt das JWT-Token im Antwortkörper oder als Cookie zurück, wenn 'cookie' wahr ist.</response>
+    /// <response code="401">Unbefugt. Ungültiger Benutzername oder Passwort.</response>
+    [ProducesResponseType(typeof(string), StatusCodes.Status200OK, "text/javascript")]
+    [ProducesResponseType(typeof(void), StatusCodes.Status401Unauthorized)]
+    [AllowAnonymous]
+    [HttpPost]
+    [Route("form")]
+    public Task<IActionResult> Login([FromForm] Login login)
+    {
+        // added to configure open API (swagger and scalar)
+        throw new NotImplementedException();
+    }
+
+    /// <summary>
+    /// Entfernt das Authentifizierungs-Cookie des Benutzers (AuthCookie)
+    /// </summary>
+    /// <returns>
+    /// Gibt eine HTTP 200 oder 401.
+    /// </returns>
+    /// <remarks>
+    /// Sample request:
+    ///
+    ///     POST /api/auth/logout
+    ///
+    /// </remarks>
+    /// <response code="200">Erfolgreich gelöscht, wenn der Benutzer ein berechtigtes Cookie hat.</response>
+    /// <response code="401">Wenn es kein zugelassenes Cookie gibt, wird „nicht zugelassen“ zurückgegeben.</response>
+    [ProducesResponseType(typeof(string), StatusCodes.Status200OK, "text/javascript")]
+    [ProducesResponseType(typeof(void), StatusCodes.Status401Unauthorized)]
+    [Authorize]
+    [HttpPost("logout")]
+    public async Task<IActionResult> Logout()
+    {
+        try
+        {
+            await HttpContext.SignOutAsync(CookieAuthenticationDefaults.AuthenticationScheme);
+            return Ok();
+        }
+        catch (Exception ex)
+        {
+            _logger.LogError(ex, "Unexpected error occurred.\n{ErrorMessage}", ex.Message);
+            return StatusCode(StatusCodes.Status500InternalServerError);
+        }
+    }
+
+    /// <summary>
+    /// Prüft, ob der Benutzer ein autorisiertes Token hat.
+    /// </summary>
+    /// <returns>Wenn ein autorisiertes Token vorhanden ist HTTP 200 asynchron 401</returns>
+    /// <remarks>
+    /// Sample request:
+    ///
+    ///     GET /api/auth
+    ///
+    /// </remarks>
+    /// <response code="200">Wenn es einen autorisierten Cookie gibt.</response>
+    /// <response code="401">Wenn kein Cookie vorhanden ist oder nicht autorisierte.</response>
+    [ProducesResponseType(typeof(string), StatusCodes.Status200OK, "text/javascript")]
+    [ProducesResponseType(typeof(void), StatusCodes.Status401Unauthorized)]
+    [Authorize]
+    [HttpGet]
+    public IActionResult IsAuthenticated() => Ok();
 }

EnvelopeGenerator.GeneratorAPI/Controllers/ControllerExtensions.cs

@@ -3,22 +3,60 @@ using System.Security.Claims;
 
 namespace EnvelopeGenerator.GeneratorAPI.Controllers
 {
+    /// <summary>
+    /// Provides extension methods for extracting user information from a <see cref="ClaimsPrincipal"/>.
+    /// </summary>
     public static class ControllerExtensions
     {
-        public static int? GetId(this ClaimsPrincipal user)
-            => int.TryParse(user.FindFirst(ClaimTypes.NameIdentifier)?.Value, out int result)
+        /// <summary>
+        /// Attempts to retrieve the user's ID from the claims. Returns null if the ID is not found or invalid.
+        /// </summary>
+        /// <param name="user">The <see cref="ClaimsPrincipal"/> representing the user.</param>
+        /// <returns>The user's ID as an integer, or null if not found or invalid.</returns>
+        public static int? GetIdOrDefault(this ClaimsPrincipal user)
+            => int.TryParse(user.FindFirstValue(ClaimTypes.NameIdentifier) ?? user.FindFirstValue("sub"), out int result)
                 ? result : null;
-        
-        public static string? GetUsername(this ClaimsPrincipal user)
+
+        /// <summary>
+        /// Retrieves the user's ID from the claims. Throws an exception if the ID is missing or invalid.
+        /// </summary>
+        /// <param name="user">The <see cref="ClaimsPrincipal"/> representing the user.</param>
+        /// <returns>The user's ID as an integer.</returns>
+        /// <exception cref="InvalidOperationException">Thrown if the user ID claim is missing or invalid.</exception>
+        public static int GetId(this ClaimsPrincipal user)
+            => user.GetIdOrDefault()
+            ?? throw new InvalidOperationException("User ID claim is missing or invalid. This may indicate a misconfigured or forged JWT token.");
+
+        /// <summary>
+        /// Retrieves the username from the claims, if available.
+        /// </summary>
+        /// <param name="user">The <see cref="ClaimsPrincipal"/> representing the user.</param>
+        /// <returns>The username as a string, or null if not found.</returns>
+        public static string? GetUsernameOrDefault(this ClaimsPrincipal user)
             => user.FindFirst(ClaimTypes.Name)?.Value;
-        
-        public static string? GetName(this ClaimsPrincipal user)
+
+        /// <summary>
+        /// Retrieves the user's surname (last name) from the claims, if available.
+        /// </summary>
+        /// <param name="user">The <see cref="ClaimsPrincipal"/> representing the user.</param>
+        /// <returns>The surname as a string, or null if not found.</returns>
+        public static string? GetNameOrDefault(this ClaimsPrincipal user)
             => user.FindFirst(ClaimTypes.Surname)?.Value;
-        
-        public static string? GetPrename(this ClaimsPrincipal user)
+
+        /// <summary>
+        /// Retrieves the user's given name (first name) from the claims, if available.
+        /// </summary>
+        /// <param name="user">The <see cref="ClaimsPrincipal"/> representing the user.</param>
+        /// <returns>The given name as a string, or null if not found.</returns>
+        public static string? GetPrenameOrDefault(this ClaimsPrincipal user)
             => user.FindFirst(ClaimTypes.GivenName)?.Value;
-        
-        public static string? GetEmail(this ClaimsPrincipal user)
+
+        /// <summary>
+        /// Retrieves the user's email address from the claims, if available.
+        /// </summary>
+        /// <param name="user">The <see cref="ClaimsPrincipal"/> representing the user.</param>
+        /// <returns>The email address as a string, or null if not found.</returns>
+        public static string? GetEmailOrDefault(this ClaimsPrincipal user)
             => user.FindFirst(ClaimTypes.Email)?.Value;
     }
 }

EnvelopeGenerator.GeneratorAPI/Controllers/EmailTemplateController.cs

@@ -5,11 +5,17 @@ using EnvelopeGenerator.Application.EmailTemplates.Commands.Reset;
 using EnvelopeGenerator.Application.EmailTemplates.Queries.Read;
 using Microsoft.AspNetCore.Authorization;
 using Microsoft.AspNetCore.Mvc;
+using EnvelopeGenerator.Application.Contracts.Repositories;
+using EnvelopeGenerator.Application.DTOs;
+using MediatR;
+using System.Threading.Tasks;
+using DigitalData.UserManager.Application.Services;
+using EnvelopeGenerator.Application.Exceptions;
 
 namespace EnvelopeGenerator.GeneratorAPI.Controllers;
 
 /// <summary>
-/// Controller for managing email templates.
+/// Controller for managing temp templates.
 /// Steuerung zur Verwaltung von E-Mail-Vorlagen.
 /// </summary>
 [Route("api/[controller]")]
@@ -17,17 +23,27 @@ namespace EnvelopeGenerator.GeneratorAPI.Controllers;
 [Authorize]
 public class EmailTemplateController : ControllerBase
 {
+    private readonly ILogger<EmailTemplateController> _logger;
+
     private readonly IMapper _mapper;
 
+    private readonly IEmailTemplateRepository _repository;
+
+    private readonly IMediator _mediator;
+
     /// <summary>
     /// Initialisiert eine neue Instanz der <see cref="EmailTemplateController"/>-Klasse.
     /// </summary>
     /// <param name="mapper">
+    /// <param name="repository">
     /// Die AutoMapper-Instanz, die zum Zuordnen von Objekten verwendet wird.
     /// </param>
-    public EmailTemplateController(IMapper mapper)
+    public EmailTemplateController(IMapper mapper, IEmailTemplateRepository repository, ILogger<EmailTemplateController> logger, IMediator mediator)
     {
         _mapper = mapper;
+        _repository = repository;
+        _logger = logger;
+        _mediator = mediator;
     }
 
     /// <summary>
@@ -45,17 +61,33 @@ public class EmailTemplateController : ControllerBase
     /// <response code="401">Wenn der Benutzer nicht authentifiziert ist.</response>
     /// <response code="404">Wenn die gesuchte Abfrage nicht gefunden wird.</response>
     [HttpGet]
-    public IActionResult Get([FromQuery] ReadEmailTemplateQuery? emailTemplate = null)
+    public async Task<IActionResult> Get([FromQuery] ReadEmailTemplateQuery? emailTemplate = null)
     {
-        // Implementation logic here
-        return Ok(); // Placeholder for actual implementation
+        try
+        {
+            if (emailTemplate is null || (emailTemplate.Id is null && emailTemplate.Type is null))
+            {
+                var temps = await _repository.ReadAllAsync();
+                return Ok(_mapper.Map<IEnumerable<EmailTemplateDto>>(temps));
+            }
+            else
+            {
+                var temp = await _mediator.Send(emailTemplate);
+                return temp is null ? NotFound() : Ok(temp);
+            }
+        }
+        catch (Exception ex)
+        {
+            _logger.LogError(ex, "{Message}", ex.Message);
+            return StatusCode(StatusCodes.Status500InternalServerError);
+        }
     }
 
     /// <summary>
-    /// Updates an email template or resets it if no update command is provided.
+    /// Updates an temp template or resets it if no update command is provided.
     /// Aktualisiert eine E-Mail-Vorlage oder setzt sie zurück, wenn kein Aktualisierungsbefehl angegeben ist.
     /// </summary>
-    /// <param name="email">Die E-Mail-Vorlagenabfrage.</param>
+    /// <param name="temp">Die E-Mail-Vorlagenabfrage.</param>
     /// <param name="update">Der Aktualisierungsbefehl für die E-Mail-Vorlage.
     /// Wird auf Standardwert aktualisiert, wenn die Anfrage ohne http-Body gesendet wird.
     /// </param>
@@ -73,19 +105,35 @@ public class EmailTemplateController : ControllerBase
     /// <response code="401">Wenn der Benutzer nicht authentifiziert ist.</response>
     /// <response code="404">Wenn die gesuchte Abfrage nicht gefunden wird.</response>
     [HttpPut]
-    public IActionResult Update([FromQuery] EmailTemplateQuery email, [FromBody] UpdateEmailTemplateCommand? update = null)
+    public async Task<IActionResult> Update([FromQuery] EmailTemplateQuery? temp = null, [FromBody] UpdateEmailTemplateCommand? update = null)
     {
-        if (update is null)
+        try
         {
-            var reset = _mapper.Map<ResetEnvelopeTemplateCommand>(email);
-            // Logic for resetting the email template
-        }
-        else
-        {
-            update.EmailTemplateQuery = email;
-            // Logic for updating the email template
-        }
+            if (update is null)
+            {
+                await _mediator.Send(new ResetEmailTemplateCommand(temp));
+                return Ok();
+            }
+            else if(temp is null)
+            {
+                return BadRequest("No both id and type");
+            }
+            else
+            {
+                update.EmailTemplateQuery = temp;
+                await _mediator.Send(update);
+                return Ok();
+            }
 
-        return Ok(); // Placeholder for actual implementation
+        }
+        catch(NotFoundException)
+        {
+            return BadRequest();
+        }
+        catch (Exception ex)
+        {
+            _logger.LogError(ex, "An unexpected error occurred. {message}", ex.Message);
+            return new StatusCodeResult(StatusCodes.Status500InternalServerError);
+        }
     }
 }

EnvelopeGenerator.GeneratorAPI/Controllers/EnvelopeController.cs

@@ -1,8 +1,13 @@
 using DigitalData.Core.DTO;
 using EnvelopeGenerator.Application.Contracts.Services;
+using EnvelopeGenerator.Application.Envelopes.Commands;
 using EnvelopeGenerator.Application.Envelopes.Queries.Read;
+using MediatR;
 using Microsoft.AspNetCore.Authorization;
 using Microsoft.AspNetCore.Mvc;
+using Newtonsoft.Json;
+using System.ComponentModel;
+using System.ComponentModel.DataAnnotations.Schema;
 
 namespace EnvelopeGenerator.GeneratorAPI.Controllers;
 
@@ -27,16 +32,19 @@ public class EnvelopeController : ControllerBase
 {
     private readonly ILogger<EnvelopeController> _logger;
     private readonly IEnvelopeService _envelopeService;
+    private readonly IMediator _mediator;
 
     /// <summary>
     /// Erstellt eine neue Instanz des EnvelopeControllers.
     /// </summary>
     /// <param name="logger">Der Logger, der für das Protokollieren von Informationen verwendet wird.</param>
     /// <param name="envelopeService">Der Dienst, der für die Verarbeitung von Umschlägen zuständig ist.</param>
-    public EnvelopeController(ILogger<EnvelopeController> logger, IEnvelopeService envelopeService)
+    /// <param name="mediator"></param>
+    public EnvelopeController(ILogger<EnvelopeController> logger, IEnvelopeService envelopeService, IMediator mediator)
     {
         _logger = logger;
         _envelopeService = envelopeService;
+        _mediator = mediator;
     }
 
     /// <summary>
@@ -57,7 +65,19 @@ public class EnvelopeController : ControllerBase
         {
             if (User.GetId() is int intId)
                 return await _envelopeService.ReadByUserAsync(intId, min_status: envelope.Status, max_status: envelope.Status).ThenAsync(
-                    Success: Ok,
+                    Success: envelopes =>
+                    {
+                        if(envelope.Id is int id)
+                            envelopes = envelopes.Where(e => e.Id == id);
+
+                        if(envelope.Status is int status)
+                            envelopes = envelopes.Where(e => e.Status == status);
+
+                        if (envelope.Uuid is string uuid)
+                            envelopes = envelopes.Where(e => e.Uuid == uuid);
+
+                        return Ok(envelopes);
+                    },
                     Fail: IActionResult (msg, ntc) =>
                     {
                         _logger.LogNotice(ntc);
@@ -75,4 +95,87 @@ public class EnvelopeController : ControllerBase
             return StatusCode(StatusCodes.Status500InternalServerError);
         }
     }
+
+    /// <summary>
+    /// Ruft das Ergebnis eines Dokuments basierend auf der ID ab.
+    /// </summary>
+    /// <param name="id">Die eindeutige ID des Umschlags.</param>
+    /// <param name="view">Gibt an, ob das Dokument inline angezeigt werden soll (true) oder als Download bereitgestellt wird (false).</param>
+    /// <returns>Eine IActionResult-Instanz, die das Dokument oder einen Fehlerstatus enthält.</returns>
+    /// <response code="200">Das Dokument wurde erfolgreich abgerufen.</response>
+    /// <response code="404">Das Dokument wurde nicht gefunden oder ist nicht verfügbar.</response>
+    /// <response code="500">Ein unerwarteter Fehler ist aufgetreten.</response>
+    [HttpGet("doc-result")]
+    public async Task<IActionResult> GetDocResultAsync([FromQuery] int id, [FromQuery] bool view = false)
+    {
+        try
+        {
+            if (User.GetId() is int intId)
+                return await _envelopeService.ReadByUserAsync(intId).ThenAsync(
+                    Success: envelopes =>
+                    {
+                        var envelope = envelopes.Where(e => e.Id == id).FirstOrDefault();
+
+                        if (envelope is null)
+                            return NotFound("Envelope not available.");
+                        else if (envelope?.DocResult is null)
+                            return NotFound("The document has not been fully signed or the result has not yet been released.");
+                        else
+                        {
+                            if (view)
+                            {
+                                Response.Headers.Append("Content-Disposition", "inline; filename=\"" + envelope.Uuid + ".pdf\"");
+                                return File(envelope.DocResult, "application/pdf");
+                            }
+                            else
+                                return File(envelope.DocResult, "application/pdf", $"{envelope.Uuid}.pdf");
+                        }
+                    },
+                    Fail: IActionResult (msg, ntc) =>
+                    {
+                        _logger.LogNotice(ntc);
+                        return StatusCode(StatusCodes.Status500InternalServerError);
+                    });
+            else
+            {
+                _logger.LogError("Trotz erfolgreicher Autorisierung wurde die Benutzer-ID nicht als Ganzzahl erkannt. Dies könnte auf eine fehlerhafte Erstellung der Anspruchsliste zurückzuführen sein.");
+                return StatusCode(StatusCodes.Status500InternalServerError);
+            }
+        }
+        catch (Exception ex)
+        {
+            _logger.LogError(ex, "{Message}", ex.Message);
+            return StatusCode(StatusCodes.Status500InternalServerError);
+        }
+    }
+
+    /// <summary>
+    /// 
+    /// </summary>
+    /// <param name="envelope"></param>
+    /// <returns></returns>
+    [NonAction]
+    [Authorize]
+    [HttpPost]
+    public async Task<IActionResult> CreateAsync([FromQuery] CreateEnvelopeCommand envelope)
+    {
+        try
+        {
+            envelope.UserId = User.GetId();
+            var res = await _mediator.Send(envelope);
+            
+            if (res is null)
+            {
+                _logger.LogError("Failed to create envelope. Envelope details: {EnvelopeDetails}", JsonConvert.SerializeObject(envelope));
+                return StatusCode(StatusCodes.Status500InternalServerError);
+            }
+            else
+                return Ok(res);
+        }
+        catch (Exception ex)
+        {
+            _logger.LogError(ex, "{Message}", ex.Message);
+            return StatusCode(StatusCodes.Status500InternalServerError);
+        }
+    }
 }

EnvelopeGenerator.GeneratorAPI/Controllers/EnvelopeReceiverController.cs

@@ -1,11 +1,20 @@
-using DigitalData.Core.DTO;
+using AutoMapper;
+using DigitalData.Core.DTO;
 using EnvelopeGenerator.Application.Contracts.Services;
+using EnvelopeGenerator.Application.Contracts.SQLExecutor;
+using EnvelopeGenerator.Application.DTOs.Receiver;
 using EnvelopeGenerator.Application.EnvelopeReceivers.Commands.Create;
 using EnvelopeGenerator.Application.EnvelopeReceivers.Queries.Read;
 using EnvelopeGenerator.Application.Envelopes.Queries.ReceiverName;
+using EnvelopeGenerator.Application.SQL;
+using EnvelopeGenerator.Domain.Entities;
+using EnvelopeGenerator.GeneratorAPI.Models;
 using MediatR;
 using Microsoft.AspNetCore.Authorization;
 using Microsoft.AspNetCore.Mvc;
+using Microsoft.Data.SqlClient;
+using Microsoft.Extensions.Options;
+using System.Data;
 
 namespace EnvelopeGenerator.GeneratorAPI.Controllers;
 
@@ -26,17 +35,37 @@ public class EnvelopeReceiverController : ControllerBase
 
     private readonly IMediator _mediator;
 
+    private readonly IMapper _mapper;
+
+    private readonly IEnvelopeExecutor _envelopeExecutor;
+
+    private readonly IEnvelopeReceiverExecutor _erExecutor;
+
+    private readonly IDocumentExecutor _documentExecutor;
+
+    private readonly string _cnnStr;
+
     /// <summary>
     /// Konstruktor für den EnvelopeReceiverController.
     /// </summary>
     /// <param name="logger">Logger-Instanz zur Protokollierung von Informationen und Fehlern.</param>
     /// <param name="envelopeReceiverService">Service zur Verwaltung von Umschlagempfängern.</param>
     /// <param name="mediator">Mediator-Instanz zur Verarbeitung von Befehlen und Abfragen.</param>
-    public EnvelopeReceiverController(ILogger<EnvelopeReceiverController> logger, IEnvelopeReceiverService envelopeReceiverService, IMediator mediator)
+    /// <param name="mapper"></param>
+    /// <param name="envelopeExecutor"></param>
+    /// <param name="erExecutor"></param>
+    /// <param name="documentExecutor"></param>
+    /// <param name="csOpt"></param>
+    public EnvelopeReceiverController(ILogger<EnvelopeReceiverController> logger, IEnvelopeReceiverService envelopeReceiverService, IMediator mediator, IMapper mapper, IEnvelopeExecutor envelopeExecutor, IEnvelopeReceiverExecutor erExecutor, IDocumentExecutor documentExecutor, IOptions<ConnectionString> csOpt)
     {
         _logger = logger;
         _erService = envelopeReceiverService;
         _mediator = mediator;
+        _mapper = mapper;
+        _envelopeExecutor = envelopeExecutor;
+        _erExecutor = erExecutor;
+        _documentExecutor = documentExecutor;
+        _cnnStr = csOpt.Value.Value;
     }
 
     /// <summary>
@@ -57,16 +86,23 @@ public class EnvelopeReceiverController : ControllerBase
     {
         try
         {
-            var username = User.GetUsername();
+            var username = User.GetUsernameOrDefault();
 
             if (username is null)
             {
                 _logger.LogError(@"Envelope Receiver dto cannot be sent because username claim is null. Potential authentication and authorization error. The value of other claims are [id: {id}], [username: {username}], [name: {name}], [prename: {prename}], [email: {email}].",
-                    User.GetId(), User.GetUsername(), User.GetName(), User.GetPrename(), User.GetEmail());
+                    User.GetId(), User.GetUsernameOrDefault(), User.GetNameOrDefault(), User.GetPrenameOrDefault(), User.GetEmailOrDefault());
                 return StatusCode(StatusCodes.Status500InternalServerError);
             }
 
-            return await _erService.ReadByUsernameAsync(username: username).ThenAsync(
+            return await _erService.ReadByUsernameAsync(
+                username: username,
+                min_status: envelopeReceiver.Status?.Min,
+                max_status:envelopeReceiver.Status?.Max,
+                envelopeQuery: envelopeReceiver.Envelope,
+                receiverQuery: envelopeReceiver.Receiver,
+                ignore_statuses: envelopeReceiver.Status?.Ignore ?? Array.Empty<int>())
+                .ThenAsync(
                     Success: Ok,
                     Fail: IActionResult (msg, ntc) =>
                     {
@@ -120,8 +156,7 @@ public class EnvelopeReceiverController : ControllerBase
     /// <summary>
     /// Datenübertragungsobjekt mit Informationen zu Umschlägen, Empfängern und Unterschriften.
     /// </summary>
-    /// <param name="createEnvelopeQuery"></param>
-    /// <param name="cancellationToken">Token to cancel the operation</param>
+    /// <param name="request"></param>
     /// <returns>HTTP-Antwort</returns>
     /// <remarks>
     /// Sample request:
@@ -157,9 +192,141 @@ public class EnvelopeReceiverController : ControllerBase
     /// <response code="500">Es handelt sich um einen unerwarteten Fehler. Die Protokolle sollten überprüft werden.</response>
     [Authorize]
     [HttpPost]
-    public async Task<IActionResult> CreateAsync([FromBody] CreateEnvelopeReceiverCommand createEnvelopeQuery, CancellationToken cancellationToken)
+    public async Task<IActionResult> CreateAsync([FromBody] CreateEnvelopeReceiverCommand request)
     {
-        await _mediator.Send(createEnvelopeQuery, cancellationToken);
-        return Accepted();
+        try
+        {
+            CancellationToken cancel = default;
+            int userId = User.GetId();
+
+            #region Create Envelope
+            var envelope = await _envelopeExecutor.CreateEnvelopeAsync(userId, request.Title, request.Message, request.TFAEnabled, cancel);
+            #endregion
+
+            #region Add receivers
+            List<EnvelopeReceiver> sentReceivers = new();
+            List<ReceiverGetOrCreateCommand> unsentReceivers = new();
+
+            foreach (var receiver in request.Receivers)
+            {
+                var envelopeReceiver = await _erExecutor.AddEnvelopeReceiverAsync(envelope.Uuid, receiver.EmailAddress, receiver.Salution, receiver.PhoneNumber, cancel);
+
+                if (envelopeReceiver is null)
+                    unsentReceivers.Add(receiver);
+                else
+                    sentReceivers.Add(envelopeReceiver);
+            }
+
+            var res = _mapper.Map<CreateEnvelopeReceiverResponse>(envelope);
+            res.UnsentReceivers = unsentReceivers;
+            res.SentReceiver = _mapper.Map<List<ReceiverReadDto>>(sentReceivers.Select(er => er.Receiver));
+            #endregion
+
+            #region Add document
+            var document = await _documentExecutor.CreateDocumentAsync(request.Document.DataAsBase64, envelope.Uuid, cancel);
+
+            if(document is null)
+                return StatusCode(StatusCodes.Status500InternalServerError, "Document creation is failed.");
+            #endregion
+
+            #region Add document element
+            // @DOC_ID, @RECEIVER_ID, @POSITION_X, @POSITION_Y, @PAGE
+            string sql = @"
+                DECLARE @OUT_SUCCESS bit;
+
+                EXEC [dbo].[PRSIG_API_ADD_DOC_RECEIVER_ELEM]
+                    {0},
+                    {1},
+                    {2},
+                    {3},
+                    {4},
+                    @OUT_SUCCESS OUTPUT;
+
+                SELECT @OUT_SUCCESS as [@OUT_SUCCESS];";
+
+            foreach(var rcv in res.SentReceiver)
+                foreach(var sign in request.Receivers.Where(r => r.EmailAddress == rcv.EmailAddress).FirstOrDefault()?.Signatures ?? Array.Empty<Signature>())
+                {
+                    using (SqlConnection conn = new(_cnnStr))
+                    {
+                        conn.Open();
+
+                        var formattedSQL = string.Format(sql, document.Id.ToSqlParam(), rcv.Id.ToSqlParam(), sign.X.ToSqlParam(), sign.Y.ToSqlParam(), sign.Page.ToSqlParam());
+
+                        using SqlCommand cmd = new SqlCommand(formattedSQL, conn);
+                        cmd.CommandType = CommandType.Text;
+
+                        using SqlDataReader reader = cmd.ExecuteReader();
+                        if (reader.Read())
+                        {
+                            bool outSuccess = reader.GetBoolean(0);
+                        }
+                    }
+                }
+            #endregion
+
+            #region Create history
+            // ENV_UID, STATUS_ID, USER_ID,
+            string sql_hist = @"
+                USE [DD_ECM]
+
+                DECLARE @OUT_SUCCESS bit;
+
+                EXEC [dbo].[PRSIG_API_ADD_HISTORY_STATE]
+                    {0},
+                    {1},
+                    {2},
+                    @OUT_SUCCESS OUTPUT;
+
+                SELECT @OUT_SUCCESS as [@OUT_SUCCESS];";
+
+            using (SqlConnection conn = new(_cnnStr))
+            {
+                conn.Open();
+                var formattedSQL_hist = string.Format(sql_hist, envelope.Uuid.ToSqlParam(), 1003.ToSqlParam(), userId.ToSqlParam());
+                using (SqlCommand cmd = new SqlCommand(formattedSQL_hist, conn))
+                {
+                    cmd.CommandType = CommandType.Text;
+
+                    using (SqlDataReader reader = cmd.ExecuteReader())
+                    {
+                        if (reader.Read())
+                        {
+                            bool outSuccess = reader.GetBoolean(0);
+                        }
+                    }
+                }
+            }
+            #endregion
+
+            return Ok(res);
+        }
+        catch (Exception ex)
+        {
+            _logger.LogError(ex, "{Message}", ex.Message);
+            return StatusCode(StatusCodes.Status500InternalServerError);
+        }
     }
+
+    /// <summary>
+    /// 
+    /// </summary>
+    /// <param name="input"></param>
+    /// <returns></returns>
+    public static bool IsBase64String(string input)
+    {
+        if (string.IsNullOrWhiteSpace(input))
+            return false;
+
+        try
+        {
+            Convert.FromBase64String(input);
+            return true;
+        }
+        catch (FormatException)
+        {
+            return false;
+        }
+    }
+
 }

EnvelopeGenerator.GeneratorAPI/Controllers/HistoryController.cs

@@ -1,8 +1,10 @@
 using DigitalData.EmailProfilerDispatcher.Abstraction.Entities;
 using EnvelopeGenerator.Application.Contracts.Services;
 using EnvelopeGenerator.Application.Histories.Queries.Read;
+using EnvelopeGenerator.Extensions;
 using Microsoft.AspNetCore.Authorization;
 using Microsoft.AspNetCore.Mvc;
+using Microsoft.Extensions.Caching.Memory;
 using static EnvelopeGenerator.Common.Constants;
 
 
@@ -20,15 +22,18 @@ public class HistoryController : ControllerBase
 
     private readonly IEnvelopeHistoryService _service;
 
+    private readonly IMemoryCache _memoryCache;
+
     /// <summary>
     /// Konstruktor für den HistoryController.
     /// </summary>
     /// <param name="logger">Der Logger, der für das Protokollieren von Informationen verwendet wird.</param>
     /// <param name="service">Der Dienst, der für die Verarbeitung der Umschlaghistorie verantwortlich ist.</param>
-    public HistoryController(ILogger<HistoryController> logger, IEnvelopeHistoryService service)
+    public HistoryController(ILogger<HistoryController> logger, IEnvelopeHistoryService service, IMemoryCache memoryCache)
     {
         _logger = logger;
         _service = service;
+        _memoryCache = memoryCache;
     }
 
     /// <summary>
@@ -47,19 +52,9 @@ public class HistoryController : ControllerBase
     /// <response code="200"></response>
     [HttpGet("related")]
     [Authorize]
-    public IActionResult GetReferenceTypes()
+    public IActionResult GetReferenceTypes(ReferenceType? referenceType = null)
     {
-        // Enum zu Schlüssel-Wert-Paar
-        var referenceTypes = Enum.GetValues(typeof(ReferenceType))
-                                 .Cast<ReferenceType>()
-                                 .ToDictionary(rt =>
-                                 {
-                                     var key = rt.ToString();
-                                     var keyAsCamelCase = char.ToLower(key[0]) + key[1..];
-                                     return keyAsCamelCase;
-                                 }, rt => (int)rt);
-
-        return Ok(referenceTypes);
+        return referenceType is null ? Ok(_memoryCache.GetEnumAsDictionary<ReferenceType>()) : Ok(referenceType.ToString());
     }
 
     /// <summary>
@@ -91,7 +86,7 @@ public class HistoryController : ControllerBase
     /// 3004: MessageDeletionSent
     /// 3005: MessageCompletionSent
     /// </summary>
-    /// <param name="related">
+    /// <param name="status">
     /// Abfrageparameter, der angibt, auf welche Referenz sich der Status bezieht.
     /// 0 - Sender: Historische Datensätze, die sich auf den Status des Absenders beziehen. Sie haben Statuscodes, die mit 1* beginnen.
     /// 1 - Receiver: Historische Datensätze über den Status der Empfänger. Diese haben Statuscodes, die mit 2* beginnen.
@@ -102,19 +97,9 @@ public class HistoryController : ControllerBase
     /// <response code="200"></response>
     [HttpGet("status")]
     [Authorize]
-    public IActionResult GetEnvelopeStatus([FromQuery] ReferenceType? related = null)
+    public IActionResult GetEnvelopeStatus([FromQuery] EnvelopeStatus? status = null)
     {
-        // Enum zu Schlüssel-Wert-Paar
-        var referenceTypes = Enum.GetValues(typeof(EnvelopeStatus))
-                                 .Cast<EnvelopeStatus>()
-                                 .ToDictionary(rt =>
-                                 {
-                                     var key = rt.ToString();
-                                     var keyAsCamelCase = char.ToLower(key[0]) + key[1..];
-                                     return keyAsCamelCase;
-                                 }, rt => (int)rt);
-
-        return Ok(referenceTypes);
+        return status is null ? Ok(_memoryCache.GetEnumAsDictionary<EnvelopeStatus>()) : Ok(status.ToString());
     }
 
     /// <summary>
@@ -131,6 +116,8 @@ public class HistoryController : ControllerBase
     [Authorize]
     public async Task<IActionResult> GetAllAsync([FromQuery] ReadHistoryQuery history)
     {
+
+
         bool withReceiver = false;
         bool withSender = false;
 

EnvelopeGenerator.GeneratorAPI/Controllers/ReceiverController.cs

@@ -38,17 +38,24 @@ public class ReceiverController : CRUDControllerBaseWithErrorHandling<IReceiverS
     [HttpGet]
     public async Task<IActionResult> Get([FromQuery] ReadReceiverQuery receiver)
     {
-        if (receiver.EmailAddress is null && receiver.Signature is null)
+        if (receiver.Id is null && receiver.EmailAddress is null && receiver.Signature is null)
             return await base.GetAll();
 
         try
         {
+            if(receiver.Id is int id)
+                return await _service.ReadByIdAsync(id).ThenAsync(
+                    Success: Ok,
+                    Fail: IActionResult (msg, ntc) =>
+                    {
+                        return NotFound();
+                    });
+
             return await _service.ReadByAsync(emailAddress: receiver.EmailAddress, signature: receiver.Signature).ThenAsync(
                     Success: Ok,
                     Fail: IActionResult (msg, ntc) =>
                     {
-                        _logger.LogNotice(ntc);
-                        return StatusCode(StatusCodes.Status500InternalServerError);
+                        return NotFound();
                     });
         }
         catch (Exception ex)

EnvelopeGenerator.GeneratorAPI/EnvelopeGenerator.GeneratorAPI.csproj

@@ -10,21 +10,22 @@
     <Authors>Digital Data GmbH</Authors>
     <Company>Digital Data GmbH</Company>
     <Product>EnvelopeGenerator.GeneratorAPI</Product>
-    <Version>1.2.0</Version>
-    <FileVersion>1.2.0</FileVersion>
-    <AssemblyVersion>1.2.0</AssemblyVersion>
+    <Version>1.2.1</Version>
+    <FileVersion>1.2.1</FileVersion>
+    <AssemblyVersion>1.2.1</AssemblyVersion>
     <PackageOutputPath>Copyright © 2025 Digital Data GmbH. All rights reserved.</PackageOutputPath>
 	<DocumentationFile>bin\$(Configuration)\$(TargetFramework)\$(AssemblyName).xml</DocumentationFile>
   </PropertyGroup>
 
   <ItemGroup>
     <PackageReference Include="AspNetCore.Scalar" Version="1.1.8" />
+    <PackageReference Include="DigitalData.Auth.Client" Version="1.3.7" />
     <PackageReference Include="DigitalData.Core.API" Version="2.1.1" />
-    <PackageReference Include="Microsoft.AspNetCore.OpenApi" Version="9.0.3" />
+    <PackageReference Include="Microsoft.AspNetCore.OpenApi" Version="9.0.4" />
     <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="9.0.4" />
-    <PackageReference Include="Scalar.AspNetCore" Version="2.1.4" />
-    <PackageReference Include="Swashbuckle.AspNetCore" Version="8.1.0" />
-    <PackageReference Include="DigitalData.Core.Abstractions" Version="3.4.3" />
+    <PackageReference Include="Scalar.AspNetCore" Version="2.2.1" />
+    <PackageReference Include="Swashbuckle.AspNetCore" Version="8.1.1" />
+    <PackageReference Include="DigitalData.Core.Abstractions" Version="3.6.0" />
     <PackageReference Include="DigitalData.Core.Application" Version="3.2.1" />
     <PackageReference Include="DigitalData.Core.DTO" Version="2.0.1" />
     <PackageReference Include="DigitalData.EmailProfilerDispatcher.Abstraction" Version="3.0.0" />

EnvelopeGenerator.GeneratorAPI/Jenkinsfile

@@ -0,0 +1,10 @@
+pipeline {
+    agent any
+    stages {
+        stage('Build') {
+            steps {
+                sh 'dotnet build'
+            }
+        }
+    }
+}

EnvelopeGenerator.GeneratorAPI/Models/AuthTokenKeys.cs

@@ -0,0 +1,28 @@
+namespace EnvelopeGenerator.GeneratorAPI.Models;
+
+/// <summary>
+/// Represents the keys and default values used for authentication token handling 
+/// within the Envelope Generator API.
+/// </summary>
+public class AuthTokenKeys
+{
+    /// <summary>
+    /// Gets the name of the cookie used to store the authentication token.
+    /// </summary>
+    public string Cookie { get; init; } = "AuthToken";
+
+    /// <summary>
+    /// Gets the name of the query string parameter used to pass the authentication token.
+    /// </summary>
+    public string QueryString { get; init; } = "AuthToken";
+
+    /// <summary>
+    /// Gets the expected issuer value for the authentication token.
+    /// </summary>
+    public string Issuer { get; init; } = "auth.digitaldata.works";
+
+    /// <summary>
+    /// Gets the expected audience value for the authentication token.
+    /// </summary>
+    public string Audience { get; init; } = "sign-flow-gen.digitaldata.works";
+}

EnvelopeGenerator.GeneratorAPI/Models/ConnectionString.cs

@@ -0,0 +1,12 @@
+namespace EnvelopeGenerator.GeneratorAPI.Models;
+
+/// <summary>
+/// 
+/// </summary>
+public class ConnectionString
+{
+    /// <summary>
+    /// 
+    /// </summary>
+    public string Value { get; set; } = string.Empty;
+}

EnvelopeGenerator.GeneratorAPI/Models/Login.cs

@@ -6,8 +6,8 @@ namespace EnvelopeGenerator.GeneratorAPI.Models;
 /// Repräsentiert ein Login-Modell mit erforderlichem Passwort und optionaler ID und Benutzername.
 /// </summary>
 /// <param name="Password">Das erforderliche Passwort für das Login.</param>
-/// <param name="Id">Die optionale ID des Benutzers.</param>
+/// <param name="UserId">Die optionale ID des Benutzers.</param>
 /// <param name="Username">Der optionale Benutzername.</param>
-public record Login([Required] string Password, int? Id = null, string? Username = null)
+public record Login([Required] string Password, int? UserId = null, string? Username = null)
 {
 }

EnvelopeGenerator.GeneratorAPI/Program.cs

@@ -1,6 +1,5 @@
 using DigitalData.Core.API;
 using DigitalData.Core.Application;
-using DigitalData.UserManager.Application;
 using EnvelopeGenerator.Infrastructure;
 using Microsoft.AspNetCore.Authentication.Cookies;
 using Microsoft.AspNetCore.Localization;
@@ -10,11 +9,19 @@ using Scalar.AspNetCore;
 using Microsoft.OpenApi.Models;
 using DigitalData.UserManager.DependencyInjection;
 using EnvelopeGenerator.Application;
+using DigitalData.Auth.Client;
+using DigitalData.Core.Abstractions;
+using EnvelopeGenerator.GeneratorAPI.Models;
+using Microsoft.AspNetCore.Authentication.JwtBearer;
+using Microsoft.IdentityModel.Tokens;
+using DigitalData.Core.Abstractions.Security.Extensions;
 
 var builder = WebApplication.CreateBuilder(args);
 
 var config = builder.Configuration;
 
+var deferredProvider = new DeferredServiceProvider();
+
 builder.Services.AddControllers();
 
 //CORS Policy
@@ -83,8 +90,54 @@ builder.Services.AddSwaggerGen(options =>
 builder.Services.AddOpenApi();
 // DbContext
 var connStr = config.GetConnectionString("Default") ?? throw new InvalidOperationException("There is no default connection string in appsettings.json.");
+
+builder.Services.Configure<ConnectionString>(cs => cs.Value = connStr);
+
 builder.Services.AddDbContext<EGDbContext>(options => options.UseSqlServer(connStr));
 
+builder.Services.AddAuthHubClient(config.GetSection("AuthClientParams"));
+
+var authTokenKeys = config.GetOrDefault<AuthTokenKeys>();
+
+builder.Services.AddAuthentication(options =>
+{
+    options.DefaultAuthenticateScheme = JwtBearerDefaults.AuthenticationScheme;
+    options.DefaultChallengeScheme = JwtBearerDefaults.AuthenticationScheme;
+})
+    .AddJwtBearer(opt =>
+    {
+        opt.TokenValidationParameters = new TokenValidationParameters
+        {
+            ValidateIssuerSigningKey = true,
+            IssuerSigningKeyResolver = (token, securityToken, identifier, parameters) =>
+            {
+                var clientParams = deferredProvider.GetOptions<ClientParams>();
+                var publicKey = clientParams!.PublicKeys.Get(authTokenKeys.Issuer, authTokenKeys.Audience);
+                return new List<SecurityKey>() { publicKey.SecurityKey };
+            },
+            ValidateIssuer = true,
+            ValidIssuer = authTokenKeys.Issuer,
+            ValidateAudience = true,
+            ValidAudience = authTokenKeys.Audience,
+        };
+
+        opt.Events = new JwtBearerEvents
+        {
+            OnMessageReceived = context =>
+            {
+                // if there is no token read related cookie or query string
+                if (context.Token is null)   // if there is no token
+                {
+                    if (context.Request.Cookies.TryGetValue(authTokenKeys.Cookie, out var cookieToken) && cookieToken is not null)
+                        context.Token = cookieToken;
+                    else if (context.Request.Query.TryGetValue(authTokenKeys.QueryString, out var queryStrToken))
+                        context.Token = queryStrToken;
+                }
+                return Task.CompletedTask;
+            }
+        };
+    });
+
 // Authentication
 builder.Services.AddAuthentication(CookieAuthenticationDefaults.AuthenticationScheme)
 .AddCookie(options =>
@@ -109,11 +162,13 @@ builder.Services.AddCookieBasedLocalizer() ;
 
 // Envelope generator serives
 builder.Services
-    .AddEnvelopeGeneratorRepositories()
+    .AddEnvelopeGeneratorInfrastructureServices(sqlExecutorConfigureOptions: executor => executor.ConnectionString = connStr)
     .AddEnvelopeGeneratorServices(config);
 
 var app = builder.Build();
 
+deferredProvider.Factory = () => app.Services;
+
 app.MapOpenApi();
 
 // Configure the HTTP request pipeline.

EnvelopeGenerator.GeneratorAPI/Properties/launchSettings.json

@@ -22,7 +22,7 @@
     "https": {
       "commandName": "Project",
       "dotnetRunMessages": true,
-      "launchBrowser": true,
+      "launchBrowser": false,
       "launchUrl": "swagger",
       "applicationUrl": "https://localhost:7174;http://localhost:5131",
       "environmentVariables": {

EnvelopeGenerator.GeneratorAPI/appsettings.Development.json

@@ -4,5 +4,15 @@
       "Default": "Information",
       "Microsoft.AspNetCore": "Warning"
     }
+  },
+  "AuthClientParams": {
+    "Url": "https://localhost:7192/auth-hub",
+    "PublicKeys": [
+      {
+        "Issuer": "auth.digitaldata.works",
+        "Audience": "sign-flow-gen.digitaldata.works"
+      }
+    ],
+    "RetryDelay": "00:00:05"
   }
 }

EnvelopeGenerator.GeneratorAPI/appsettings.json

@@ -20,5 +20,227 @@
       "User": "(&(objectClass=user)(sAMAccountName=*))",
       "Group": "(&(objectClass=group)(samAccountName=*))"
     }
+  },
+  "AuthClientParams": {
+    "Url": "https://localhost:7192/auth-hub",
+    "PublicKeys": [
+      {
+        "Issuer": "auth.digitaldata.works",
+        "Audience": "sign-flow-gen.digitaldata.works"
+      }
+    ],
+    "RetryDelay": "00:00:05"
+  },
+  "AuthTokenKeys": {
+    "Cookie": "AuthToken",
+    "QueryString": "AuthToken",
+    "Issuer": "auth.digitaldata.works",
+    "Audience": "work-flow.digitaldata.works"
+  },
+  "PSPDFKitLicenseKey": "SXCtGGY9XA-31OGUXQK-r7c6AkdLGPm2ljuyDr1qu0kkhLvydg-Do-fxpNUF4Rq3fS_xAnZRNFRHbXpE6sQ2BMcCSVTcXVJO6tPviexjpiT-HnrDEySlUERJnnvh-tmeOWprxS6BySPnSILkmaVQtUfOIUS-cUbvvEYHTvQBKbSF8di4XHQFyfv49ihr51axm3NVV3AXwh2EiKL5C5XdqBZ4sQ4O7vXBjM2zvxdPxlxdcNYmiU83uAzw7B83O_jubPzya4CdUHh_YH7Nlp2gP56MeG1Sw2JhMtfG3Rj14Sg4ctaeL9p6AEWca5dDjJ2li5tFIV2fQSsw6A_cowLu0gtMm5i8IfJXeIcQbMC2-0wGv1oe9hZYJvFMdzhTM_FiejM0agemxt3lJyzuyP8zbBSOgp7Si6A85krLWPZptyZBTG7pp7IHboUHfPMxCXqi-zMsqewOJtQBE2mjntU-lPryKnssOpMPfswwQX7QSkJYV5EMqNmEhQX6mEkp2wcqFzMC7bJQew1aO4pOpvChUaMvb1vgRek0HxLag0nwQYX2YrYGh7F_xXJs-8HNwJe8H0-eW4x4faayCgM5rB5772CCCsD9ThZcvXFrjNHHLGJ8WuBUFm6LArvSfFQdii_7j-_sqHMpeKZt26NFgivj1A==",
+  "Content-Security-Policy": [ // The first format parameter {0} will be replaced by the nonce value.
+    "default-src 'self'",
+    "script-src 'self' 'nonce-{0}' 'unsafe-eval'",
+    "style-src 'self' 'unsafe-inline' https://fonts.googleapis.com:*",
+    "img-src 'self' data: https: blob:",
+    "font-src 'self' https://fonts.gstatic.com:*",
+    "connect-src 'self' https://nominatim.openstreetmap.org:* http://localhost:* https://localhost:* ws://localhost:* wss://localhost:* blob:",
+    "frame-src 'self'",
+    "media-src 'self'",
+    "object-src 'self'"
+  ],
+  "NLog": {
+    "throwConfigExceptions": true,
+    "variables": {
+      "logDirectory": "E:\\LogFiles\\Digital Data\\signFlow",
+      "logFileNamePrefix": "${shortdate}-ECM.EnvelopeGenerator.Web"
+    },
+    "targets": {
+      "infoLogs": {
+        "type": "File",
+        "fileName": "${logDirectory}\\${logFileNamePrefix}-Info.log",
+        "maxArchiveDays": 30
+      },
+      "errorLogs": {
+        "type": "File",
+        "fileName": "${logDirectory}\\${logFileNamePrefix}-Error.log",
+        "maxArchiveDays": 30
+      },
+      "criticalLogs": {
+        "type": "File",
+        "fileName": "${logDirectory}\\${logFileNamePrefix}-Critical.log",
+        "maxArchiveDays": 30
+      }
+    },
+    // Trace, Debug, Info, Warn, Error and *Fatal*
+    "rules": [
+      {
+        "logger": "*",
+        "minLevel": "Info",
+        "maxLevel": "Warn",
+        "writeTo": "infoLogs"
+      },
+      {
+        "logger": "*",
+        "level": "Error",
+        "writeTo": "errorLogs"
+      },
+      {
+        "logger": "*",
+        "level": "Fatal",
+        "writeTo": "criticalLogs"
+      }
+    ]
+  },
+  "ContactLink": {
+    "Label": "Kontakt",
+    "Href": "https://digitaldata.works/",
+    "HrefLang": "de",
+    "Target": "_blank",
+    "Title": "Digital Data GmbH"
+  },
+  /* Resx naming format is -> Resource.language.resx (eg: Resource.de_DE.resx). 
+     To add a new language, first you should write the required resx file.
+     first is the default culture name. */
+  "Cultures": [
+    {
+      "Language": "de-DE",
+      "FIClass": "fi-de"
+    },
+    {
+      "Language": "en-US",
+      "FIClass": "fi-us"
+    }
+  ],
+  "DisableMultiLanguage": false,
+  "Regexes": [
+    {
+      "Pattern": "/^\\p{L}+(?:([\\ \\-\\']|(\\.\\ ))\\p{L}+)*$/u",
+      "Name": "City",
+      "Platforms": [ ".NET" ]
+    },
+    {
+      "Pattern": "/^[a-zA-Z\\u0080-\\u024F]+(?:([\\ \\-\\']|(\\.\\ ))[a-zA-Z\\u0080-\\u024F]+)*$/",
+      "Name": "City",
+      "Platforms": [ "javascript" ]
+    }
+  ],
+  "CustomImages": {
+    "App": {
+      "Src": "/img/DD_signFLOW_LOGO.png",
+      "Classes": {
+        "Main": "signFlow-logo"
+      }
+    },
+    "Company": {
+      "Src": "/img/digital_data.svg",
+      "Classes": {
+        "Show": "dd-show-logo",
+        "Locked": "dd-locked-logo"
+      }
+    }
+  },
+  "DispatcherParams": {
+    "SendingProfile": 1,
+    "AddedWho": "DDEnvelopGenerator",
+    "ReminderTypeId": 202377,
+    "EmailAttmt1": ""
+  },
+  "MailParams": {
+    "Placeholders": {
+      "[NAME_PORTAL]": "signFlow",
+      "[SIGNATURE_TYPE]": "signieren",
+      "[REASON]": ""
+    }
+  },
+  "GtxMessagingParams": {
+    "Uri": "https://rest.gtx-messaging.net",
+    "Path": "smsc/sendsms/f566f7e5-bdf2-4a9a-bf52-ed88215a432e/json",
+    "Headers": {},
+    "QueryParams": {
+      "from": "signFlow"
+    }
+  },
+  "TFARegParams": {
+    "TimeLimit": "00:30:00"
+  },
+  "DbTriggerParams": {
+    "Envelope": [ "TBSIG_ENVELOPE_HISTORY_AFT_INS" ],
+    "EnvelopeHistory": [ "TBSIG_ENVELOPE_HISTORY_AFT_INS" ],
+    "EmailOut": [ "TBEMLP_EMAIL_OUT_AFT_INS", "TBEMLP_EMAIL_OUT_AFT_UPD" ],
+    "EnvelopeReceiverReadOnly": [ "TBSIG_ENVELOPE_RECEIVER_READ_ONLY_UPD" ],
+    "Receiver": [],
+    "EmailTemplate": [ "TBSIG_EMAIL_TEMPLATE_AFT_UPD" ]
+  },
+  "MainPageTitle": null,
+  "AnnotationParams": {
+    "Background": {
+      "Margin": 0.20,
+      "BackgroundColor": {
+        "R": 222,
+        "G": 220,
+        "B": 215
+      },
+      "BorderColor": {
+        "R": 204,
+        "G": 202,
+        "B": 198
+      },
+      "BorderStyle": "underline",
+      "BorderWidth": 4
+    },
+    "DefaultAnnotation": {
+      "Width": 1,
+      "Height": 0.5,
+      "MarginTop": 1
+    },
+    "Annotations": [
+      {
+        "Name": "Signature",
+        "MarginTop": 0
+      },
+      {
+        "Name": "PositionLabel",
+        "VerBoundAnnotName": "Signature",
+        "WidthRatio": 1.2,
+        "HeightRatio": 0.5,
+        "MarginTopRatio": 0.22
+      },
+      {
+        "Name": "Position",
+        "VerBoundAnnotName": "PositionLabel",
+        "WidthRatio": 1.2,
+        "HeightRatio": 0.5,
+        "MarginTopRatio": -0.05
+      },
+      {
+        "Name": "CityLabel",
+        "VerBoundAnnotName": "Position",
+        "WidthRatio": 1.2,
+        "HeightRatio": 0.5,
+        "MarginTopRatio": 0.05
+      },
+      {
+        "Name": "City",
+        "VerBoundAnnotName": "CityLabel",
+        "WidthRatio": 1.2,
+        "HeightRatio": 0.5,
+        "MarginTopRatio": -0.05
+      },
+      {
+        "Name": "DateLabel",
+        "VerBoundAnnotName": "City",
+        "WidthRatio": 1.55,
+        "HeightRatio": 0.5,
+        "MarginTopRatio": 0.05
+      },
+      {
+        "Name": "Date",
+        "VerBoundAnnotName": "DateLabel",
+        "WidthRatio": 1.55,
+        "HeightRatio": 0.5,
+        "MarginTopRatio": -0.1
+      }
+    ]
   }
 }

EnvelopeGenerator.Infrastructure/DependencyExtensions.cs

@@ -6,6 +6,13 @@ using Microsoft.EntityFrameworkCore;
 using DigitalData.Core.Infrastructure;
 using EnvelopeGenerator.Domain.Entities;
 using DigitalData.Core.Infrastructure.AutoMapper;
+using EnvelopeGenerator.Application.Contracts.SQLExecutor;
+using Microsoft.Extensions.Configuration;
+using EnvelopeGenerator.Infrastructure.Executor;
+using Dapper;
+using System.ComponentModel.DataAnnotations.Schema;
+using System.Reflection;
+using DigitalData.UserManager.Domain.Entities;
 
 namespace EnvelopeGenerator.Infrastructure;
 
@@ -25,7 +32,10 @@ public static class DIExtensions
     /// This method ensures that the repositories are registered as scoped services, meaning that a new instance of each repository 
     /// will be created per HTTP request (or per scope) within the dependency injection container.
     /// </remarks>
-    public static IServiceCollection AddEnvelopeGeneratorRepositories(this IServiceCollection services, Action<DbContextOptionsBuilder>? dbContextOptions = null)
+    public static IServiceCollection AddEnvelopeGeneratorInfrastructureServices(this IServiceCollection services,
+        Action<DbContextOptionsBuilder>? dbContextOptions = null,
+        IConfiguration? sqlExecutorConfiguration = null,
+        Action<SQLExecutorParams>? sqlExecutorConfigureOptions = null)
     {
         if(dbContextOptions is not null)
             services.AddDbContext<EGDbContext>(dbContextOptions);
@@ -59,6 +69,92 @@ public static class DIExtensions
         services.AddDbRepository<EGDbContext, UserReceiver>(context => context.UserReceivers).UseAutoMapper();
         services.AddDbRepository<EGDbContext, EnvelopeReceiverReadOnly>(context => context.EnvelopeReceiverReadOnlys).UseAutoMapper();
 
+        services.AddSQLExecutor<Envelope>();
+        services.AddSQLExecutor<Receiver>();
+        services.AddSQLExecutor<EnvelopeDocument>();
+        services.AddSQLExecutor<DocumentReceiverElement>();
+        services.AddSQLExecutor<DocumentStatus>();
+
+        SetDapperTypeMap<Envelope>();
+        SetDapperTypeMap<User>();
+        SetDapperTypeMap<Receiver>();
+        SetDapperTypeMap<EnvelopeDocument>();
+        SetDapperTypeMap<DocumentReceiverElement>();
+        SetDapperTypeMap<DocumentStatus>();
+
+        services.AddScoped<IEnvelopeExecutor, EnvelopeExecutor>();
+        services.AddScoped<IEnvelopeReceiverExecutor, EnvelopeReceiverExecutor>();
+        services.AddScoped<IDocumentExecutor, DocumentExecutor>();
+
+        if (sqlExecutorConfiguration is not null || sqlExecutorConfigureOptions is not null)
+            services.AddSQLExecutor(sqlExecutorConfiguration, sqlExecutorConfigureOptions);
+
+        return services;
+    }
+
+    public static IServiceCollection AddSQLExecutor(this IServiceCollection services, IConfiguration? configuration = null, Action<SQLExecutorParams>? configureOptions = null)
+    {
+        if(configuration is not null && configureOptions is not null)
+            throw new InvalidOperationException("Cannot use both 'configuration' and 'configureOptions'. Only one should be provided.");
+
+        if (configuration is not null)
+            services.Configure<SQLExecutorParams>(configuration);
+
+        if(configureOptions is not null)
+            services.Configure(configureOptions);
+
+        return services.AddSingleton<ISQLExecutor, SQLExecutor>();
+    }
+
+    private static void SetDapperTypeMap<TModel>()
+    {
+        Dapper.SqlMapper.SetTypeMap(typeof(TModel), new CustomPropertyTypeMap(
+            typeof(TModel),
+            (type, columnName) =>
+            {
+                return type.GetProperties().FirstOrDefault(prop =>
+                {
+                    var attr = prop.GetCustomAttribute<ColumnAttribute>();
+                    return attr != null && string.Equals(attr.Name, columnName, StringComparison.OrdinalIgnoreCase)
+                        || string.Equals(prop.Name, columnName, StringComparison.OrdinalIgnoreCase);
+                })!;
+            }
+        ));
+    }
+
+    public static IServiceCollection AddSQLExecutor<T>(this IServiceCollection services, IConfiguration? configuration = null, Action<SQLExecutorParams>? configureOptions = null) where T : class
+    {
+        if (configuration is not null && configureOptions is not null)
+            throw new InvalidOperationException("Cannot use both 'configuration' and 'configureOptions'. Only one should be provided.");
+
+        if (configuration is not null)
+            services.Configure<SQLExecutorParams>(configuration);
+
+        services.AddScoped<ISQLExecutor<T>, SQLExecutor<T>>();
+
+        var interfaceType = typeof(ISQL<>);
+        var targetGenericType = interfaceType.MakeGenericType(typeof(T));
+
+        var implementations = AppDomain.CurrentDomain.GetAssemblies()
+            .SelectMany(a =>
+            {
+                try { return a.GetTypes(); }
+                catch { return Array.Empty<Type>(); }
+            })
+            .Where(t =>
+                t is { IsClass: true, IsAbstract: false } &&
+                t.GetInterfaces().Any(i =>
+                    i.IsGenericType &&
+                    i.GetGenericTypeDefinition() == interfaceType &&
+                    i.GenericTypeArguments[0] == typeof(T)
+                )
+            );
+
+        foreach (var impl in implementations)
+        {
+            services.AddSingleton(impl);
+        }
+
         return services;
     }
 }

EnvelopeGenerator.Infrastructure/EnvelopeGenerator.Infrastructure.csproj

@@ -7,7 +7,8 @@
   </PropertyGroup>
 
   <ItemGroup>
-    <PackageReference Include="DigitalData.Core.Abstractions" Version="3.4.3" />
+    <PackageReference Include="Dapper" Version="2.1.66" />
+    <PackageReference Include="DigitalData.Core.Abstractions" Version="3.6.0" />
     <PackageReference Include="DigitalData.Core.Infrastructure" Version="2.0.4" />
     <PackageReference Include="DigitalData.Core.Infrastructure.AutoMapper" Version="1.0.2" />
     <PackageReference Include="DigitalData.EmailProfilerDispatcher" Version="3.0.0" />

EnvelopeGenerator.Infrastructure/Executor/DocumentExecutor.cs

@@ -0,0 +1,29 @@
+using Dapper;
+using EnvelopeGenerator.Application.Contracts.SQLExecutor;
+using EnvelopeGenerator.Application.SQL;
+using EnvelopeGenerator.Domain.Entities;
+using Microsoft.Data.SqlClient;
+using Microsoft.Extensions.DependencyInjection;
+using Microsoft.Extensions.Options;
+
+namespace EnvelopeGenerator.Infrastructure.Executor;
+
+public class DocumentExecutor : SQLExecutor, IDocumentExecutor
+{
+    public DocumentExecutor(IServiceProvider provider, IOptions<SQLExecutorParams> sqlExecutorParamsOptions) : base(provider, sqlExecutorParamsOptions)
+    {
+    }
+
+    public async Task<EnvelopeDocument> CreateDocumentAsync(string base64, string envelope_uuid, CancellationToken cancellation = default)
+    {
+        using var connection = new SqlConnection(Params.ConnectionString);
+        var sql = Provider.GetRequiredService<DocumentCreateReadSQL>();
+        var formattedSql = string.Format(sql.Raw, envelope_uuid.ToSqlParam());
+        var param = DocumentCreateReadSQL.CreateParmas(base64);
+        await connection.OpenAsync(cancellation);
+        var documents = await connection.QueryAsync<EnvelopeDocument>(formattedSql, param);
+        return documents.FirstOrDefault()
+            ?? throw new InvalidOperationException($"Document creation failed. Parameters:" +
+            $"base64={base64}, envelope_uuid='{envelope_uuid}'.");
+    }
+}

EnvelopeGenerator.Infrastructure/Executor/EnvelopeExecutor.cs

@@ -0,0 +1,37 @@
+using Dapper;
+using DigitalData.UserManager.Application.Contracts.Repositories;
+using EnvelopeGenerator.Application.Contracts.Repositories;
+using EnvelopeGenerator.Application.Contracts.SQLExecutor;
+using EnvelopeGenerator.Application.SQL;
+using EnvelopeGenerator.Domain.Entities;
+using Microsoft.Data.SqlClient;
+using Microsoft.Extensions.DependencyInjection;
+using Microsoft.Extensions.Options;
+
+namespace EnvelopeGenerator.Infrastructure.Executor;
+
+public class EnvelopeExecutor : SQLExecutor, IEnvelopeExecutor
+{
+    private readonly IUserRepository _userRepository;
+
+    public EnvelopeExecutor(IServiceProvider provider, IOptions<SQLExecutorParams> sqlExecutorParamsOptions, IUserRepository userRepository) : base(provider, sqlExecutorParamsOptions)
+    {
+        _userRepository = userRepository;
+    }
+
+    public async Task<Envelope> CreateEnvelopeAsync(int userId, string title = "", string message = "", bool tfaEnabled = false, CancellationToken cancellation = default)
+    {
+        using var connection = new SqlConnection(Params.ConnectionString);
+        var sql = Provider.GetRequiredService<EnvelopeCreateReadSQL>();
+        var formattedSql = string.Format(sql.Raw, userId.ToSqlParam(), title.ToSqlParam(), tfaEnabled.ToSqlParam(), message.ToSqlParam());
+        await connection.OpenAsync(cancellation);
+        var envelopes = await connection.QueryAsync<Envelope>(formattedSql);
+        var envelope = envelopes.FirstOrDefault()
+            ?? throw new InvalidOperationException($"Envelope creation failed. Parameters:" +
+            $"userId={userId}, title='{title}', message='{message}', tfaEnabled={tfaEnabled}."); ;
+
+        envelope.User = await _userRepository.ReadByIdAsync(envelope.UserId);
+
+        return envelope;
+    }
+}

EnvelopeGenerator.Infrastructure/Executor/EnvelopeReceiverExecutor.cs

@@ -0,0 +1,35 @@
+using Dapper;
+using EnvelopeGenerator.Application.Contracts.Repositories;
+using EnvelopeGenerator.Application.Contracts.SQLExecutor;
+using EnvelopeGenerator.Application.SQL;
+using EnvelopeGenerator.Domain.Entities;
+using Microsoft.Data.SqlClient;
+using Microsoft.Extensions.DependencyInjection;
+using Microsoft.Extensions.Options;
+
+namespace EnvelopeGenerator.Infrastructure.Executor;
+
+public class EnvelopeReceiverExecutor: SQLExecutor, IEnvelopeReceiverExecutor
+{
+    private readonly IEnvelopeReceiverRepository _erRepository;
+
+    public EnvelopeReceiverExecutor(IServiceProvider provider, IOptions<SQLExecutorParams> sqlExecutorParamsOptions, IEnvelopeReceiverRepository erRepository) : base(provider, sqlExecutorParamsOptions)
+    {
+        _erRepository = erRepository;
+    }
+
+    public async Task<EnvelopeReceiver?> AddEnvelopeReceiverAsync(string envelope_uuid, string emailAddress, string? salutation, string? phone = null, CancellationToken cancellation = default)
+    {
+        using var connection = new SqlConnection(Params.ConnectionString);
+        var sql = Provider.GetRequiredService<EnvelopeReceiverAddReadSQL>();
+        var formattedSql = string.Format(sql.Raw, envelope_uuid.ToSqlParam(), emailAddress.ToSqlParam(), salutation.ToSqlParam(), phone.ToSqlParam());
+        await connection.OpenAsync(cancellation);
+        var envelopeReceivers = await connection.QueryAsync(formattedSql);
+        var er = envelopeReceivers.FirstOrDefault();
+
+        if (er is null)
+            return null;
+
+        return await _erRepository.ReadByIdAsync(envelopeId: er.EnvelopeId, receiverId: er.ReceiverId);
+    }
+}

EnvelopeGenerator.Infrastructure/Executor/Query.cs

@@ -0,0 +1,41 @@
+using EnvelopeGenerator.Application.Contracts.SQLExecutor;
+using Microsoft.EntityFrameworkCore;
+
+namespace EnvelopeGenerator.Infrastructure.Executor;
+
+public sealed record Query<TEntity> : IQuery<TEntity>
+{
+    private readonly IQueryable<TEntity> _query;
+
+    internal Query(IQueryable<TEntity> queryable)
+    {
+        _query = queryable;
+    }
+
+    public TEntity First() => _query.First();
+
+    public Task<TEntity> FirstAsync() => _query.FirstAsync();
+
+    public TEntity? FirstOrDefault() => _query.FirstOrDefault();
+
+
+    public Task<TEntity?> FirstOrDefaultAsync() => _query.FirstOrDefaultAsync();
+
+
+    public TEntity Single() => _query.Single();
+
+
+    public Task<TEntity> SingleAsync() => _query.SingleAsync();
+
+
+    public TEntity? SingleOrDefault() => _query.SingleOrDefault();
+
+
+    public Task<TEntity?> SingleOrDefaultAsync() => _query.SingleOrDefaultAsync();
+
+
+    public IEnumerable<TEntity> ToList() => _query.ToList();
+
+
+    public async Task<IEnumerable<TEntity>> ToListAsync() => await _query.ToListAsync();
+}

EnvelopeGenerator.Infrastructure/Executor/QueryExtension.cs

@@ -0,0 +1,9 @@
+namespace EnvelopeGenerator.Infrastructure.Executor;
+
+public static class QueryExtension
+{
+    public static Query<TEntity> ToQuery<TEntity>(this IQueryable<TEntity> queryable) where TEntity : class
+    {
+        return new Query<TEntity>(queryable);
+    }
+}

EnvelopeGenerator.Infrastructure/Executor/SQLExecutor.cs

@@ -0,0 +1,33 @@
+using Dapper;
+using EnvelopeGenerator.Application.Contracts.SQLExecutor;
+using Microsoft.Data.SqlClient;
+using Microsoft.Extensions.DependencyInjection;
+using Microsoft.Extensions.Options;
+
+namespace EnvelopeGenerator.Infrastructure.Executor;
+
+public class SQLExecutor : ISQLExecutor
+{
+    protected readonly SQLExecutorParams Params;
+
+    protected readonly IServiceProvider Provider;
+
+    public SQLExecutor(IServiceProvider provider, IOptions<SQLExecutorParams> sqlExecutorParamsOptions)
+    {
+        Provider = provider;
+        Params = sqlExecutorParamsOptions.Value;
+    }
+
+    public async Task<IEnumerable<TEntity>> Execute<TEntity>(string sql, DynamicParameters parameters, CancellationToken cancellation = default)
+    {
+        using var connection = new SqlConnection(Params.ConnectionString);
+        await connection.OpenAsync(cancellation);
+        return await connection.QueryAsync<TEntity>(sql, parameters);
+    }
+
+    public Task<IEnumerable<TEntity>> Execute<TEntity, TSQL>(DynamicParameters parameters, CancellationToken cancellation = default) where TSQL : ISQL
+    {
+        var sql = Provider.GetRequiredService<TSQL>();
+        return Execute<TEntity>(sql.Raw, parameters, cancellation);
+    }
+}

EnvelopeGenerator.Infrastructure/Executor/SQLExecutorBaseEntity.cs

@@ -0,0 +1,31 @@
+using EnvelopeGenerator.Application.Contracts.SQLExecutor;
+using Microsoft.EntityFrameworkCore;
+using Microsoft.Extensions.DependencyInjection;
+using Microsoft.Extensions.Options;
+
+namespace EnvelopeGenerator.Infrastructure.Executor;
+
+public sealed class SQLExecutor<T> : SQLExecutor, ISQLExecutor<T> where T : class
+{
+    private readonly EGDbContext _context;
+
+    private readonly IServiceProvider _provider;
+
+    public SQLExecutor(EGDbContext context, IServiceProvider provider, IOptions<SQLExecutorParams> options) : base(provider, options)
+    {
+        _context = context;
+        _provider = provider;
+    }
+
+    public IQuery<T> Execute(string sql, CancellationToken cancellation = default, params object[] parameters)
+        => _context
+            .Set<T>()
+            .FromSqlRaw(sql, parameters)
+            .ToQuery();
+
+    public IQuery<T> Execute<TSQL>(CancellationToken cancellation = default, params object[] parameters) where TSQL : ISQL<T>
+    {
+        var sql = _provider.GetRequiredService<TSQL>();
+        return Execute(sql.Raw);
+    }
+}

EnvelopeGenerator.Infrastructure/Executor/SQLExecutorParams.cs

@@ -0,0 +1,6 @@
+namespace EnvelopeGenerator.Infrastructure.Executor;
+
+public class SQLExecutorParams
+{
+    public string? ConnectionString { get; set; }
+}

EnvelopeGenerator.Infrastructure/Repositories/EnvelopeHistoryRepository.cs

@@ -13,7 +13,7 @@ public class EnvelopeHistoryRepository : CRUDRepository<EnvelopeHistory, long, E
 
     private IQueryable<EnvelopeHistory> By(int? envelopeId = null, string? userReference = null, int? status = null, bool withSender = false, bool withReceiver = false)
     {
-        var query = _dbSet.AsQueryable();
+        var query = _dbSet.AsNoTracking();
 
         if (envelopeId is not null)
             query = query.Where(eh => eh.EnvelopeId == envelopeId);

EnvelopeGenerator.Infrastructure/Repositories/EnvlopeReceiverRepository.cs

@@ -48,9 +48,19 @@ public class EnvelopeReceiverRepository : CRUDRepository<EnvelopeReceiver, (int
 
     public async Task<int> CountAsync(string uuid, string signature) => await ReadWhere(uuid: uuid, signature: signature).CountAsync();
 
-    private IQueryable<EnvelopeReceiver> ReadById(int envelopeId, int receiverId, bool readOnly = true)
+    private IQueryable<EnvelopeReceiver> ReadById(int envelopeId, int receiverId, bool withEnvelope = true, bool withReceiver = true, bool readOnly = true)
     {
         var query = readOnly ? _dbSet.AsNoTracking() : _dbSet;
+        
+        if (withEnvelope)
+            query = query
+                .Include(er => er.Envelope).ThenInclude(e => e!.Documents!).ThenInclude(d => d.Elements!.Where(e => e.Receiver!.Id == receiverId))
+                .Include(er => er.Envelope).ThenInclude(e => e!.History)
+                .Include(er => er.Envelope).ThenInclude(e => e!.User);
+
+        if (withReceiver)
+            query = query.Include(er => er.Receiver);
+
         return query.Where(er => er.EnvelopeId == envelopeId && er.ReceiverId == receiverId);
     }
 

EnvelopeGenerator.Service/Config.vb

@@ -1,16 +1,13 @@
 Imports DigitalData.Modules.Config.ConfigAttributes
+Imports EnvelopeGenerator.Common.Jobs.FinalizeDocument
 
 Public Class Config
     <ConnectionString>
     Public Property ConnectionString As String = ""
 
     Public Property Debug As Boolean = False
+
     Public Property IntervalInMin As Integer = 1
 
-    Public Property IgnoredLabels As List(Of String) = New List(Of String) From {
-        "Date", "Datum",
-        "ZIP", "PLZ",
-        "Place", "Ort",
-        "Position", "Stellung"
-    }
+    Public Property PDFBurnerParams As PDFBurnerParams = New PDFBurnerParams()
 End Class

EnvelopeGenerator.Service/Scheduler.vb

@@ -3,6 +3,7 @@ Imports DigitalData.Modules.Base
 Imports DigitalData.Modules.Database
 Imports DigitalData.Modules.Logging
 Imports EnvelopeGenerator.Common.Jobs
+Imports EnvelopeGenerator.Common.Jobs.FinalizeDocument
 Imports Quartz
 
 Public Class Scheduler
@@ -11,15 +12,15 @@ Public Class Scheduler
     Private Scheduler As IScheduler
     Private ReadOnly ConnectionString As String
     Private ReadOnly LicenseKey As String
-    Private Property _ignoredLabels As List(Of String)
+    Private Property _pdfBurnerParams As PDFBurnerParams
 
     Private Const JobName = "CertificateDocumentJob"
 
-    Public Sub New(pLogConfig As LogConfig, pConnectionString As String, pLicenseKey As String, ignoredLabels As List(Of String))
+    Public Sub New(pLogConfig As LogConfig, pConnectionString As String, pLicenseKey As String, pdfBurnerParams As PDFBurnerParams)
         MyBase.New(pLogConfig)
         ConnectionString = pConnectionString
         LicenseKey = pLicenseKey
-        _ignoredLabels = ignoredLabels
+        _pdfBurnerParams = pdfBurnerParams
 
         Dim oLogProvider = New LogProvider(Logger)
         Logging.LogProvider.SetCurrentLogProvider(oLogProvider)
@@ -40,7 +41,7 @@ Public Class Scheduler
                 {Common.Constants.GDPICTURE, LicenseKey},
                 {Common.Constants.LOGCONFIG, LogConfig},
                 {Common.Constants.DATABASE, ConnectionString},
-                {Common.Constants.IGNORED_LABELS, _ignoredLabels}
+                {Common.Constants.PDF_BURNER_PARAMS, _pdfBurnerParams}
             }
 
             Logger.Debug("Initialized Job [{0}]", JobName)

EnvelopeGenerator.Service/Service.vb

@@ -55,7 +55,7 @@ Public Class Service
 
             Logger.Debug("Inititalize Quartz")
 
-            Scheduler = New Scheduler(LogConfig, Config.ConnectionString, oKey, Config.IgnoredLabels)
+            Scheduler = New Scheduler(LogConfig, Config.ConnectionString, oKey, Config.PDFBurnerParams)
             Await Scheduler.Start(Config.IntervalInMin)
 
             Logger.Info("Started [{0}] !", ServiceName)

EnvelopeGenerator.Terminal/CommandManager.cs

@@ -32,11 +32,27 @@ public class CommandManager
     [Subcommand]
     public IEnvelopeReceiverService EnvelopeReceiver => _envelopeReceiverService;
 
-    [Command]
-    public async Task ReadDocument(IConsole console, int? id = null, int? envelopeId = null)
+    [Command(ArgumentSeparatorStrategy = ArgumentSeparatorStrategy.EndOfOptions)]
+    public async Task ReadDocument(IConsole console,
+        [Option(Description = "ID of the document.")] int? id = null,
+        [Option(Description = "ID of the envelope containing the document.")] int? envelopeId = null,
+        [Option(Description = "Path to save the PDF")] bool save = false,
+        [Option(Description = "Directory to save the PDF")] string? dir = null,
+        [Option(Description = "Name of file to save the PDF")] string? fileName = null)
     {
         ReadDocumentQuery query = new(id, envelopeId);
         var document = await _mediator.Send(query);
-        console.WriteLine(JsonSerializer.Serialize(document, Options));
+        console.WriteLine(JsonSerializer.Serialize(save ?  document as ReadDocumentResponseBase : document, Options));
+
+        if (save)
+        {
+            dir ??= AppContext.BaseDirectory;
+            fileName ??= $"D{document?.Guid}E{document?.EnvelopeId}.pdf";
+
+            var path = Path.Combine(dir, fileName);
+            console.WriteLine("Save to " + path);
+
+            File.WriteAllBytes(path, document?.ByteData ?? Array.Empty<byte>());
+        }
     }
 }

EnvelopeGenerator.Terminal/DependencyInjection.cs

@@ -27,11 +27,11 @@ public static class DependencyInjection
         });
 
         // Add envelope generator services
-        services.AddEnvelopeGeneratorRepositories(options => options.UseSqlServer(connStr));
+        services.AddEnvelopeGeneratorInfrastructureServices(options => options.UseSqlServer(connStr));
 
         return services
             .AddSingleton<CommandManager>()
-            .AddEnvelopeGeneratorRepositories()
+            .AddEnvelopeGeneratorInfrastructureServices()
             .AddEnvelopeGeneratorServices(configuration)
             .AddSingleton(sp =>
             {

EnvelopeGenerator.Terminal/EnvelopeGenerator.Terminal.csproj

@@ -19,7 +19,7 @@
     <PackageReference Include="CommandDotNet" Version="7.0.5" />
     <PackageReference Include="CommandDotNet.IoC.MicrosoftDependencyInjection" Version="5.0.1" />
     <PackageReference Include="CommandDotNet.NameCasing" Version="4.0.2" />
-    <PackageReference Include="DigitalData.Core.Abstractions" Version="3.4.3" />
+    <PackageReference Include="DigitalData.Core.Abstractions" Version="3.6.0" />
     <PackageReference Include="DigitalData.Core.Application" Version="3.2.1" />
     <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="7.0.20" />
     <PackageReference Include="Microsoft.Extensions.Caching.SqlServer" Version="7.0.20" />

EnvelopeGenerator.Terminal/Program.cs

@@ -1,4 +1,5 @@
-using Microsoft.Extensions.Hosting;
+using Microsoft.Extensions.Configuration;
+using Microsoft.Extensions.Hosting;
 
 namespace EnvelopeGenerator.Terminal;
 
@@ -8,6 +9,10 @@ public class Program
     {
         var builder = Host.CreateApplicationBuilder(args);
 
+        builder.Configuration
+               .SetBasePath(AppContext.BaseDirectory)
+               .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true);
+
         var config = builder.Configuration;
 
         builder.Services.AddCommandManagerRunner(config);