Query 8889 Sent Emails

System Application, System.Email

src/System Application/App/Email/src/Email/Sent/SentEmails.Query.al289 lines, Copyright (c) Microsoft Corporation. MIT

// ------------------------------------------------------------------------------------------------// Copyright (c) Microsoft Corporation. All rights reserved.// Licensed under the MIT License. See License.txt in the project root for license information.// ------------------------------------------------------------------------------------------------namespace System.Email;using System.Security.AccessControl;/// <summary>/// Query to get all sent emails and their related records./// The query has an one to many relationship between email and the related records./// This file contains logic that is identical to the EmailOutbox.Query.al./// If changes are made to this file, make sure to update EmailOutbox to./// </summary>query 8889 "Sent Emails"{    Access = Internal;    QueryType = Normal;    OrderBy = ascending(Message_Id);    Permissions = tabledata "Sent Email" = r,                  tabledata "Email Related Record" = r;    elements    {        dataitem(SentEmail; "Sent Email")        {            /// <summary>            /// Email id            /// </summary>            column(Id; Id) { }            /// <summary>            /// Email message id            /// </summary>            column(Message_Id; "Message Id") { }            /// <summary>            /// User security id            /// </summary>            column(User_Security_Id; "User Security Id") { }            /// <summary>            /// Email account id            /// </summary>            filter(Account_Id; "Account Id") { }            /// <summary>            /// Date and time for when email was sent            /// </summary>            filter(Date_Time_Sent; "Date Time Sent") { }            dataitem(RelatedRecord; "Email Related Record")            {                DataItemLink = "Email Message Id" = SentEmail."Message Id";                SqlJoinType = LeftOuterJoin;                /// <summary>                /// Table id for related record                /// </summary>                column(Table_Id; "Table Id") { }                /// <summary>                /// System id for related record                /// </summary>                column(System_Id; "System Id") { }            }        }    }    procedure GetSentEmails(var SentEmails: Record "Sent Email" temporary)    var        SentEmailRecord: Record "Sent Email";    begin        if not SentEmails.IsEmpty() then            SentEmails.DeleteAll();        CopyFiltersFrom(SentEmails);        SentEmails.Reset();        if not Open() then            exit;        while Read() do            if SentEmailRecord.Get(Id) then begin                SentEmails.SetRange(Id, Id);                if SentEmails.IsEmpty() then begin                    SentEmails.TransferFields(SentEmailRecord);                    SentEmails.Insert();                end;                SentEmails.Reset();            end;    end;    procedure ReadUntilNextMessageId(CurrentMessageId: Guid) KeepReading: Boolean    begin        KeepReading := Read();        while KeepReading do begin            if CurrentMessageId <> Message_Id then                exit;            KeepReading := Read();        end;    end;    procedure InsertRecordInto(var SentEmails: Record "Sent Email" temporary)    var        SentEmailRecord: Record "Sent Email";    begin        if SentEmailRecord.Get(Id) then begin            SentEmails.TransferFields(SentEmailRecord);            if SentEmails.Insert() then;        end;    end;    procedure InsertRecordIfAnyRelatedRecords(var SentEmails: Record "Sent Email" temporary) KeepReading: Boolean    var        SentEmailRecord: Record "Sent Email";        RecordRef: RecordRef;        CurrentMessageId: Guid;    begin        KeepReading := true;        CurrentMessageId := Message_Id;        // First step is to process already loaded record        if SentEmailRecord.Get(Id) then            SentEmails.TransferFields(SentEmailRecord);        // Keep reading until Read is false or we get a new message id        // Stop as soon as we get a single permission on a related record        while KeepReading do begin            if CurrentMessageId <> Message_Id then                exit;            // Intentionally disregard relations to the Users table as every user has access to it            if (Table_Id <> 0) and (Table_Id <> Database::User) then begin                RecordRef.Open(Table_Id);                if RecordRef.ReadPermission() then begin                    RecordRef.Close();                    if SentEmails.Insert() then;                    exit(ReadUntilNextMessageId(CurrentMessageId));                end;                RecordRef.Close();            end;            KeepReading := Read();        end;    end;    procedure InsertRecordIfAllRelatedRecords(var SentEmails: Record "Sent Email" temporary) KeepReading: Boolean    var        SentEmailRecord: Record "Sent Email";        RecordRef: RecordRef;        CurrentMessageId: Guid;        HadRelatedRecord: Boolean;    begin        HadRelatedRecord := false;        KeepReading := true;        CurrentMessageId := Message_Id;        // First step is to process already loaded record        if SentEmailRecord.Get(Id) then            SentEmails.TransferFields(SentEmailRecord);        // Keep reading until Read is false or we get a new message id        while KeepReading do begin            if (CurrentMessageId <> Message_Id) and HadRelatedRecord then begin                if SentEmails.Insert() then;                exit;            end;            // Intentionally disregard relations to the Users table as every user has access to it            if (Table_Id <> 0) and (Table_Id <> Database::User) then begin                HadRelatedRecord := true;                RecordRef.Open(Table_Id);                if not RecordRef.ReadPermission() then begin                    RecordRef.Close();                    exit(ReadUntilNextMessageId(CurrentMessageId));                end;                RecordRef.Close();            end;            KeepReading := Read();        end;        // Post fence record.        // If Read returned false, then CurrentMessageId changed, but while ended        // and we therefore did not get the change to insert the record        if HadRelatedRecord then            if SentEmails.Insert() then;    end;    procedure GetSentEmailsIfAccessToAllRelatedRecords(var SentEmails: Record "Sent Email" temporary)    begin        GetSentEmailsIfAccessToAllRelatedRecords(SentEmails, false)    end;    procedure GetSentEmailsIfAccessToAllRelatedRecords(var SentEmails: Record "Sent Email" temporary; KeepRecords: Boolean)    var        KeepReading: Boolean;        UserSecId: Guid;    begin        UserSecId := UserSecurityId();        KeepReading := true;        if not KeepRecords then            if not SentEmails.IsEmpty() then                SentEmails.DeleteAll();        CopyFiltersFrom(SentEmails);        SentEmails.Reset();        //        // Each row returned in the SQL statement is read one at the time        // We need to make sure that all related records to the same message id is accessible to the user        //        if not Open() then            exit;        KeepReading := Read();        while KeepReading do begin            // If user is the owner of email we simply insert this sent email            // And read until next message id            while User_Security_Id = UserSecId do begin                InsertRecordInto(SentEmails);                KeepReading := ReadUntilNextMessageId(Message_Id);                if not KeepReading then                    exit;            end;            // Process message and insert            KeepReading := InsertRecordIfAllRelatedRecords(SentEmails);        end;    end;    procedure GetSentEmailsIfAccessToAnyRelatedRecords(var SentEmails: Record "Sent Email" temporary)    begin        GetSentEmailsIfAccessToAnyRelatedRecords(SentEmails, false)    end;    procedure GetSentEmailsIfAccessToAnyRelatedRecords(var SentEmails: Record "Sent Email" temporary; KeepRecords: Boolean)    var        KeepReading: Boolean;        UserSecId: Guid;    begin        UserSecId := UserSecurityId();        KeepReading := true;        if not KeepRecords then            if not SentEmails.IsEmpty() then                SentEmails.DeleteAll();        CopyFiltersFrom(SentEmails);        SentEmails.Reset();        //        // Each row returned in the SQL statement is read one at the time        // We need to make sure that any related records to the same message id is accessible to the user        //        if not Open() then            exit;        KeepReading := Read();        while KeepReading do begin            // If user is the owner of email we simply insert this sent email            // And read until next message id            while User_Security_Id = UserSecId do begin                InsertRecordInto(SentEmails);                KeepReading := ReadUntilNextMessageId(Message_Id);                if not KeepReading then                    exit;            end;            // Process message and insert            KeepReading := InsertRecordIfAnyRelatedRecords(SentEmails);        end;    end;    local procedure CopyFiltersFrom(var SentEmail: Record "Sent Email" temporary)    begin        // In certain localizations, the filters do not work with filter tokens.        // Fx. MX localization. GetFilter() output is "17/07/22 04:01:25.251 p. m...17/07/22 04:01:25.251 p. m."        // Which will fail with filter tokens as it will split into "17/07/22 04:01:25.251 p. m" and "".17/07/22 04:01:25.251 p. m."". The second is an invalid datetime.        if SentEmail.GetFilter("Date Time Sent") <> '' then            SetRange(Date_Time_Sent, SentEmail.GetRangeMin("Date Time Sent"), SentEmail.GetRangeMax("Date Time Sent"));        SetFilter(Account_Id, SentEmail.GetFilter("Account Id"));        SetFilter(User_Security_Id, SentEmail.GetFilter("User Security Id"));    end;}