<?xml version="1.0" encoding="utf-8"?>
<AlvaoApplication xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" ModelVersion="1">
  <Applications>
    <Application id="5">
      <Name>Security Incident Reporting To NÚKIB</Name>
      <UniqueId>3F2504E0-4F89-11D3-9A0C-0305E82C3301</UniqueId>
      <Version>2</Version>
      <AdvancedSettings>
        <Setting>
          <Name>AutomaticReportSecurityIncidents.NUKIB.EmailTemplate</Name>
          <Value>{
    "csy": {
        "subject": "Hlášení kybernetického bezpečnostního incidentu",
        "body": "Vážený týme NÚKIB,\nv příloze této zprávy zasíláme oznámení o kybernetickém bezpečnostním incidentu ve formátu XML, v souladu s platnou legislativou.\nV případě potřeby doplnění informací nás prosím neváhejte kontaktovat.\n\nS pozdravem,\n[$SecurityManagerName$]\nSecurity Manager\n[$OrganizationName$]\n[$PhoneAndEmail$]"
    },
    "enu": {
        "subject": "Cybersecurity Incident Report",
        "body": "Dear NÚKIB team,\nPlease find attached an XML report of a cybersecurity incident, submitted in accordance with applicable legislation.\nPlease do not hesitate to contact us if you require any additional information.\n\nBest regards,\n[$SecurityManagerName$]\nSecurity Manager\n[$OrganizationName$]\n[$PhoneAndEmail$]"
    }
}</Value>
        </Setting>
      </AdvancedSettings>
      <Scripts>
        <Script id="8">
          <Name>AutomaticReportingSecurityIncidentsNUKIB</Name>
          <Code>using System;
using System.Data;
using System.Linq;
using System.IO;
using System.Globalization;
using System.Collections.Generic;
using Microsoft.Data.SqlClient;
using Alvao.Global;
using Alvao.API.Common;
using Alvao.API.Common.Model;
using Alvao.API.Common.Model.Database;
using Alvao.API.SD.Model.XmlExport;
using Alvao.API.SD;
using Alvao.API.SD.Model;
using Alvao.Apps.API;
using Alvao.Context;
using Dapper;
using Rebex.Mail;
using System.Xml.Serialization;
using Newtonsoft.Json;

public class AutomaticReportingSecurityIncidentsNUKIB : ITicketAutoAction
{
    private string ApprovingStatusName = "ApprovingStatusName";
    private string ReportingStatusName = "ReportingStatusName";
    private string ResolvedStatusName = "ResolvedStatusName";
    private string StartApprovingTitle = "StartApprovingTitle";
    private string AutomaticReportingTitle = "AutomaticReportingTitle";
    private string IncidentReport = "IncidentReport";
    private string ApprovalNotInitiated = "ApprovalNotInitiated";
    private string IncidentNotReported = "IncidentNotReported";
    private string ApprovalNoCustomColumns = "ApprovalNoCustomColumns";
    private string MandatoryFields = "MandatoryFields";
    private string ReportingNoCustomColumns = "ReportingNoCustomColumns";
    private string NoApprovalStateData = "NoApprovalStateData";
    private string NoSenderData = "NoSenderData";
    private string XmlNotCreated = "XmlNotCreated";
    private string NoTemplate = "NoTemplate";
    private string NoSubject = "NoSubject";
    private string NoBody = "NoBody";
    private string IncidentReported = "IncidentReported";
    private string NoDataForXml = "NoDataForXml";
    private string NoApprover = "NoApprover";

    private CultureInfo cultureInfo = new CultureInfo(1029);
    private int localeId = 1029;

    private IDictionary&lt;int, IDictionary&lt;string, string&gt;&gt; localizations = new Dictionary&lt;int, IDictionary&lt;string, string&gt;&gt;();
    
    public string Name
    { 
        get =&gt; "AutomaticReportingSecurityIncidentsNUKIB";
        set { } 
    }

    public void OnTicketChanged(SqlConnection con, SqlTransaction trans, int ticketId, int personId, string properties)
    {
        if (IsActivated() &amp;&amp; !IsApprovingState(ticketId))
            DoAutomaticReportingState(con, trans, ticketId);
    }

    public void OnTicketCreated(SqlConnection con, SqlTransaction trans, int ticketId, int personId)
    {
        if (!IsActivated())
            return;
        IsApprovingState(ticketId);
    }

    private void CreateLocalizationDict()
    {
        localizations = new Dictionary&lt;int, IDictionary&lt;string, string&gt;&gt;
        {
            {
                1029, new Dictionary&lt;string, string&gt;
                {
                    {ApprovingStatusName, "Schválení bezpečnostním manažerem"},
                    {ReportingStatusName, "Hlášení NÚKIB"},
                    {ResolvedStatusName, "Vyřešeno"},
                    {StartApprovingTitle, "Zahájení schvalování"},
                    {AutomaticReportingTitle, "Hlášení incidentu"},
                    {IncidentReport, "Incident report"},
                    {ApprovalNotInitiated, "Proces schvalování hlášení bezpečnostních incidentů nebyl zahájen."},
                    {IncidentNotReported, "Bezpečnostní incident nebyl nahlášen NÚKIB."},
                    {ApprovalNoCustomColumns, "Nelze získat vlastní položky pro zahájení schvalování."},
                    {MandatoryFields, "Následující pole jsou povinná pro hlášení bezpečnostních incidentů a nebyla vyplněna"},
                    {ReportingNoCustomColumns, "Nelze získat vlastní položky pro hlášení incidentu."},
                    {NoApprovalStateData, "Nelze získat údaje o stavu schválení pro hlášení incidentu."},
                    {NoSenderData, "Nebylo možné získat informace o odesílateli pro nahlášení incidentu."},
                    {XmlNotCreated, "Nelze vytvořit XML report pro hlášení incidentu."},
                    {NoTemplate, "Nelze získat e-mailovou šablonu pro hlášení incidentu."},
                    {NoSubject, "Nelze získat šablonu předmětu e-mailu pro hlášení incidentu."},
                    {NoBody, "Nelze získat šablonu těla e-mailu pro hlášení incidentu."},
                    {IncidentReported, "Incident byl nahlášeni NÚKIB"},
                    {NoDataForXml, "Nelze získat data pro vytvoření zprávy XML pro hlášení incidentu."},
                    {NoApprover, "Schvalovací proces nelze zahájit, protože v procesu není nastaven jako schvalovatel bezpečnostní manažer."}
                }
            },
            {
                1033, new Dictionary&lt;string, string&gt;
                {
                    {ApprovingStatusName, "Approving by security manager"},
                    {ReportingStatusName, "Report to NÚKIB"},
                    {ResolvedStatusName, "Resolved"},
                    {StartApprovingTitle, "Start approving"},
                    {AutomaticReportingTitle, "Report incident"},
                    {IncidentReport, "Incident report"},
                    {ApprovalNotInitiated, "The approval process for reporting security incidents was not initiated."},
                    {IncidentNotReported, "The security incident was not reported to NÚKIB."},
                    {ApprovalNoCustomColumns, "Could not get custom columns for starting approval."},
                    {MandatoryFields, "The following fields are mandatory for reporting security incidents and have not been filled in"},
                    {ReportingNoCustomColumns, "Could not get custom columns for reporting incident."},
                    {NoApprovalStateData, "Could not get approval state data for reporting incident."},
                    {NoSenderData, "Could not get information about sender for reporting incident."},
                    {XmlNotCreated, "Could not create xml report for reporting incident."},
                    {NoTemplate, "Could not get email template for reporting incident."},
                    {NoSubject, "Could not get email subject template for reporting incident."},
                    {NoBody, "Could not get email body template for reporting incident."},
                    {IncidentReported, "Incident was reported to NÚKIB."},
                    {NoDataForXml, "Could not get data to create xml report for reporting incident."},
                    {NoApprover, "The approval process could not be started because the security manager approver is not set in the process."}
                }
            }
        };
    }

    private bool IsApprovingState(int ticketId)
    {
        localeId = GetSectionLocaleId(ticketId);
        cultureInfo = new CultureInfo(localeId);
        CreateLocalizationDict();
        
        var stateId = Alvao.API.SD.TicketState.GetCurrentStateByTicketId(ticketId).id;
        if (!IsTicketInApprovingIncidentState(ticketId, stateId))
            return false;

        if (!StartApprovingReportingSecurityIncident(ticketId, stateId))
            CreateLog(ticketId, localizations[localeId][StartApprovingTitle], localizations[localeId][ApprovalNotInitiated]);
        return true;
    }

    private bool IsActivated()
    {
        return Activation.IsModuleActivated(ModuleInfo.ModuleId.SDAdvancedWorkflow) &amp;&amp; Activation.IsModuleActivated(ModuleInfo.ModuleId.SDEnterpriseApi);
    }

    private void DoAutomaticReportingState(SqlConnection con, SqlTransaction trans, int ticketId)
    {
        var stateId = Alvao.API.SD.TicketState.GetCurrentStateByTicketId(ticketId).id;  
        if (!IsTicketInReportingIncidentState(ticketId, stateId))
            return;
        
        if (ReportSecurityIncident(ticketId))
            ChangeToNextStateAfterReport(con, trans, ticketId, stateId);
        else
            CreateLog(ticketId, localizations[localeId][IncidentReport], localizations[localeId][IncidentNotReported]);
    }

    private int GetSectionLocaleId(int ticketId)
    {
        var ticket = Ticket.GetById(ticketId);
        var section = Section.GetById(ticket.liHdTicketHdSectionId);
        var localeId = section.DefaultLanguageId;
        return localeId == 1029 ? 1029 : 1033;
    }

    private void CreateLog(int ticketId, string subject, string text)
    {
        Act.Create(ticketId, subject, new HtmlTextModel(text), Alvao.API.Common.Person.GetSystem(), null, tActKind.ActKind.Note, new ActCreateSettings());
    }

    private int GetProcessId(int ticketId)
    {
        var ticket = Ticket.GetById(ticketId);
        var section = Section.GetById(ticket.liHdTicketHdSectionId);
        return section.TicketTypeId;
    }

    private bool IsTicketInApprovingIncidentState(int ticketId, int stateId)
    {
        return IsTicketInSecurityProcessState(ticketId, stateId, localizations[localeId][ApprovingStatusName], "ReportSecurityIncidentToNukibApproval");
    }

    private bool IsTicketInReportingIncidentState(int ticketId, int stateId)
    {
        return IsTicketInSecurityProcessState(ticketId, stateId, localizations[localeId][ReportingStatusName], "ReportSecurityIncidentToNukibAutoSend");
    }

    private bool IsTicketInSecurityProcessState(int ticketId, int stateId, string stateName, string columnName)
    {
        var processId = GetProcessId(ticketId);
        var statesInProcess = Alvao.API.SD.TicketState.GetFromProcess(processId);
        var securityState = statesInProcess.FirstOrDefault(s =&gt; s._TicketState == stateName);
        return securityState != null &amp;&amp; securityState.id == stateId &amp;&amp; GetCustomColumnValueInProcess(columnName, stateId) == "1";
    }

    private bool StartApprovingReportingSecurityIncident(int ticketId, int stateId)
    {
        var allCustomColumns = Alvao.API.Common.CustomColumn.GetAllColumns("tHdTicketCust");
        if (allCustomColumns == null)
        {
            CreateLog(ticketId, localizations[localeId][StartApprovingTitle], localizations[localeId][ApprovalNoCustomColumns]);
            return false;
        }

        var missingFields = CheckMandatoryFields(allCustomColumns, ticketId);
        if (missingFields.Any())
        {
            CreateLog(ticketId, localizations[localeId][StartApprovingTitle], $"{localizations[localeId][MandatoryFields]}: {string.Join(", ", missingFields)}");
            return false;
        }

        return StartApproving(ticketId, stateId);
    }

    private bool ReportSecurityIncident(int ticketId)
    {
        var allCustomColumns = Alvao.API.Common.CustomColumn.GetAllColumns("tHdTicketCust");
        if (allCustomColumns == null)
        {
            CreateLog(ticketId, localizations[localeId][AutomaticReportingTitle], localizations[localeId][ReportingNoCustomColumns]);
            return false;
        }
        
        var processId = GetProcessId(ticketId);
        var approvalStateId = GetApprovalStateId(processId);
        if (approvalStateId == null)
        {
            CreateLog(ticketId, localizations[localeId][AutomaticReportingTitle], localizations[localeId][NoApprovalStateData]);
            return false;
        }

        var missingFields = CheckMandatoryFields(allCustomColumns, ticketId);
        if (missingFields.Any())
        {
            CreateLog(ticketId, localizations[localeId][AutomaticReportingTitle], $"{localizations[localeId][MandatoryFields]}: {string.Join(", ", missingFields)}");
            return false;
        }

        return SendReport(ticketId, (int)approvalStateId);
    }

    private bool SendReport(int ticketId, int approvalStateId)
    {
        using (var xmlStream = new MemoryStream())
        {
            var sender = GetReportSender(ticketId, cultureInfo);
            if (sender == null)
            {
                CreateLog(ticketId, localizations[localeId][AutomaticReportingTitle], localizations[localeId][NoSenderData]);
                return false;
            }
            if (!MakeXml(xmlStream, ticketId, approvalStateId))
            {
                CreateLog(ticketId, localizations[localeId][AutomaticReportingTitle], localizations[localeId][XmlNotCreated]);
                return false;
            }

            var emailTemplate = DbProperty.AutomaticReportSecurityIncidents_NUKIB_EmailTemplate;
            var templateModel = JsonConvert.DeserializeObject&lt;ReportIncidentsNukibEmailTemplateModel&gt;(emailTemplate);
            if (templateModel == null)
            {
                CreateLog(ticketId, localizations[localeId][AutomaticReportingTitle], localizations[localeId][NoTemplate]);
                return false;
            }
            var emailSubject = GetEmailSubject(templateModel);
            var emailBody = GetEmailBody(templateModel, approvalStateId);
            if (string.IsNullOrEmpty(emailSubject))
            {
                CreateLog(ticketId, localizations[localeId][AutomaticReportingTitle], localizations[localeId][NoSubject]);
                return false;
            }
            if (string.IsNullOrEmpty(emailBody))
            {
                CreateLog(ticketId, localizations[localeId][AutomaticReportingTitle], localizations[localeId][NoBody]);
                return false;
            }

            xmlStream.Position = 0;

            // create email act
            var document = new Alvao.API.Common.Model.Database.tDocument();
            document.sDocument = "report.xml";
            document.sDocumentContentType = "application/xml";
            var streamLenght = xmlStream.Length;
            document.oDocument = new byte[xmlStream.Length];
            xmlStream.Read(document.oDocument, 0, (int)streamLenght);
            Attachment attachment = new Attachment(document);
            var emailAttachments = new List&lt;Alvao.API.Common.Model.IAttachment&gt;();
            emailAttachments.Add(attachment);

            var emailTextModel = new HtmlTextModel($"&lt;html&gt;&lt;body&gt;&lt;p&gt;{localizations[localeId][IncidentReported]}&lt;/p&gt;&lt;br/&gt;&lt;p&gt;{emailBody.Replace("\n", "&lt;br&gt;")}&lt;/p&gt;&lt;/body&gt;&lt;/html&gt;", emailAttachments);
            var actSettings = new Alvao.API.SD.Model.ActCreateSettings() { ActTo = DbProperty.AutomaticReportSecurityIncidents_NUKIB_Email, SendEmail = false, SenderEmail = sender.sPerson, IgnoreRights = true };
            Alvao.API.SD.Act.Create(ticketId, localizations[localeId][IncidentReport], emailTextModel, sender, null, Alvao.API.Common.Model.Database.tActKind.ActKind.Email, actSettings);
            
            // send email
            xmlStream.Position = 0;
            Rebex.Mail.MailMessage mailMessage = new Rebex.Mail.MailMessage();
            mailMessage.From.Add(sender.sPersonEmail);
            mailMessage.To.Add(DbProperty.AutomaticReportSecurityIncidents_NUKIB_Email);
            mailMessage.Subject = emailSubject;
            mailMessage.BodyText = emailBody;
            mailMessage.Attachments.Add(new Rebex.Mail.Attachment(xmlStream, "report.xml"));
            Alvao.API.Common.Email.Queue(mailMessage);
            return true;
        }
    }

    private string GetEmailSubject(ReportIncidentsNukibEmailTemplateModel model)
    {
        if (localeId == 1033 &amp;&amp; model.Enu != null)
            return model.Enu.Subject;
        
        if (localeId == 1029 &amp;&amp; model.Csy != null)
            return model.Csy.Subject;
            
        return string.Empty;
    }

    private string GetEmailBody(ReportIncidentsNukibEmailTemplateModel model, int approvalStateId)
    {
        var resultText = GetEmailBodyTemplate(model);
        if (string.IsNullOrEmpty(resultText))
            return resultText;

        var approverId = GetApproverId(approvalStateId);
        if (approverId == null)
            return resultText;
        
        var approver = Alvao.API.Common.Person.GetById((int)approverId);

        resultText = resultText.Replace("[$SecurityManagerName$]", approver.sPerson);
        if (approver.liAccountId != null)
        {
            resultText = resultText.Replace("[$OrganizationName$]", Organization.GetById((int)approver.liAccountId).sAccount);
        }
        resultText = resultText.Replace("[$PhoneAndEmail$]", $"{approver.sPersonMobile} {approver.sPersonEmail}");
        return resultText;
    }

    private string GetEmailBodyTemplate(ReportIncidentsNukibEmailTemplateModel model)
    {
        if (localeId == 1033 &amp;&amp; model.Enu != null)
            return model.Enu.Body;
        
        if (localeId == 1029 &amp;&amp; model.Csy != null)
            return model.Csy.Body;
            
        return string.Empty;
    }

    private bool MakeXml(Stream outputStream, int ticketId, int approvalStateId)
    {
        XmlSerializer serializer = new XmlSerializer(typeof(TReport));
        var data = LoadReportData(ticketId, approvalStateId);
        if (data == null)
        {
            CreateLog(ticketId, localizations[localeId][AutomaticReportingTitle], localizations[localeId][NoDataForXml]);
            return false;
        }
        serializer.Serialize(outputStream, data);
        return true;
    }

    private TReport LoadReportData(int ticketId, int approvalStateId)
    {
        var allCustomColumns = Alvao.API.Common.CustomColumn.GetAllColumns("tHdTicketCust");
        if (allCustomColumns == null)
            return null;
        
        var protectionInformation = GetProtectionInformation(allCustomColumns, ticketId, cultureInfo);
        if (protectionInformation == null)
            return null;

        var category = GetCategory(allCustomColumns, ticketId, cultureInfo);
        if (category == null)
            return null;

        var typeOfIncident = GetTypeOfIncident(allCustomColumns, ticketId, cultureInfo);
        if (typeOfIncident == null)
            return null;

        var status = GetStatus(allCustomColumns, ticketId, cultureInfo);
        if (status == null)
            return null;

        var typeOfReport = GetTypeOfReport(allCustomColumns, ticketId, cultureInfo);
        if (typeOfReport == null)
            return null;

        var continuation = GetContinuation(allCustomColumns, ticketId, cultureInfo);
        if (continuation == null)
            return null;

        var discoveryDate = ParseColumnValueToDateTime(GetCustumColumnValueInTicket("ReportSecurityIncidentToNukibDateAndTimeIncidentWasDetected", ticketId));
        if (discoveryDate == null)
            return null;
        
        var systemDetailTarget = GetCustumColumnValueInTicket("ReportSecurityIncidentToNukibSystemDetailTarget", ticketId);
        if (string.IsNullOrEmpty(systemDetailTarget))
            return null;
        
        var targetHostFeatures = GetCustumColumnValueInTicket("ReportSecurityIncidentToNukibTargetHostFeatures", ticketId);
        if (string.IsNullOrEmpty(targetHostFeatures))
            return null;

        var incidentDescription = GetCustumColumnValueInTicket("ReportSecurityIncidentToNukibIncidentDescription", ticketId);
        if (string.IsNullOrEmpty(incidentDescription))
            return null;

        var numberOfAffectedUsers = GetCustumColumnValueInTicket("ReportSecurityIncidentToNukibTheNumberOfAffectedUsers", ticketId);
        if (string.IsNullOrEmpty(numberOfAffectedUsers))
            return null;

        var numberOfAffectedSystems = GetCustumColumnValueInTicket("ReportSecurityIncidentToNukibTheNumberOfAffectedSystems", ticketId);
        if (string.IsNullOrEmpty(numberOfAffectedSystems))
            return null;

        var sender = GetReportSender(ticketId, cultureInfo);
        if (sender == null)
            return null;

        var report = new TReport
        {
            DegreeProtetionInformation = new() { ProtectionLevel = (STDegreeProtectionInformation)protectionInformation },
            ContactInfo = new() { Authority = sender.sPerson, Email = sender.sPersonEmail, Phone = sender.sPersonMobile },
            IncidentDetail = new()
            {
                DiscoveryDate = (DateTime)discoveryDate,
                Category = (STCategory)category,
                TypeOfIncident = (STTypeOfIncident)typeOfIncident,
                Status = (STStatus)status,
                NumberOfAffectedSystems = numberOfAffectedSystems,
                NumberOfAffectedUsers = numberOfAffectedUsers,
                IncidentDescription = incidentDescription,
                TypeOfReport = (STTypeOfReport)typeOfReport,
            },
            SystemDetail =
            [
                new()
                {
                    Host = systemDetailTarget,
                    HostFeatures = targetHostFeatures,
                    Continuation = (STContinuation)continuation,
                }
            ]
        };

        var tlp = GetTlp(allCustomColumns, ticketId, cultureInfo);
        if (tlp.HasValue)
            report.DegreeProtetionInformation.TLP = tlp.Value;

        var classification = GetClassification(allCustomColumns, ticketId, cultureInfo);
        if (classification.HasValue)
            report.IncidentDetail.Classification = classification.Value;

        var occurrenceDate = ParseColumnValueToDateTime(GetCustumColumnValueInTicket("ReportSecurityIncidentToNukibDateAndTimeOfIncidentOccurrence", ticketId));
        if (occurrenceDate != null)
            report.IncidentDetail.IncidentOccurrenceDate = occurrenceDate;

        var acceptedMeasures = GetCustumColumnValueInTicket("ReportSecurityIncidentToNukibWhatMeasuresHaveBeenTaken", ticketId);
        if (!string.IsNullOrEmpty(acceptedMeasures))
            report.IncidentDetail.AcceptedMeasures = acceptedMeasures;

        var extentOfDamage = GetCustumColumnValueInTicket("ReportSecurityIncidentToNukibExtentOfDamage", ticketId);
        if (!string.IsNullOrEmpty(extentOfDamage))
            report.IncidentDetail.ExtentOfDamage = extentOfDamage;

        var incidentId = ParseColumnValueToUint(GetCustumColumnValueInTicket("ReportSecurityIncidentToNukibExistingIncidentId", ticketId));
        if (incidentId.HasValue)
            report.IncidentDetail.IncidentID = incidentId.Value;
        
        var targetPort = GetCustumColumnValueInTicket("ReportSecurityIncidentToNukibTargetPort", ticketId);
        if (!string.IsNullOrEmpty(targetPort))
            report.SystemDetail[0].Port = targetPort;
        
        var targetProtocol = GetCustumColumnValueInTicket("ReportSecurityIncidentToNukibTargetProtocol", ticketId);
        if (!string.IsNullOrEmpty(targetProtocol))
            report.SystemDetail[0].Protocol = targetProtocol;

        var targetOsAndVersion = GetCustumColumnValueInTicket("ReportSecurityIncidentToNukibTargetOsAndVersion", ticketId);
        if (!string.IsNullOrEmpty(targetOsAndVersion))
            report.SystemDetail[0].OSVersion = targetOsAndVersion;

        var location = GetCustumColumnValueInTicket("ReportSecurityIncidentToNukibTargetLocation", ticketId);
        if (!string.IsNullOrEmpty(location))
            report.SystemDetail[0].LocationInArchitecture = location;

        var sourceHost = GetCustumColumnValueInTicket("ReportSecurityIncidentToNukibSourceHostOrIp", ticketId);
        if (!string.IsNullOrEmpty(sourceHost))
        {
            if (report.SystemDetailSource.Length == 0)
                report.SystemDetailSource = report.SystemDetailSource.Append(new TSystemDetailSource()).ToArray();
            report.SystemDetailSource[0].Host = sourceHost;
        }

        var sourcePort = GetCustumColumnValueInTicket("ReportSecurityIncidentToNukibSourcePort", ticketId);
        if (!string.IsNullOrEmpty(sourcePort))
        {
            if (report.SystemDetailSource.Length == 0)
                report.SystemDetailSource = report.SystemDetailSource.Append(new TSystemDetailSource()).ToArray();
            report.SystemDetailSource[0].Port = sourcePort;
        }

        var sourceProtocol = GetCustumColumnValueInTicket("ReportSecurityIncidentToNukibSourceProtocol", ticketId);
        if (!string.IsNullOrEmpty(sourceProtocol))
        {
            if (report.SystemDetailSource.Length == 0)
                report.SystemDetailSource = report.SystemDetailSource.Append(new TSystemDetailSource()).ToArray();
            report.SystemDetailSource[0].Protocol = sourceProtocol;
        }

        var nukibIdentificator = GetCustomColumnValueInProcess("ReportSecurityIncidentToNukibIdentificator", approvalStateId);
        if (!string.IsNullOrEmpty(nukibIdentificator))
            report.ContactInfo.PersonalID = nukibIdentificator;

        return report;
    }

    private IEnumerable&lt;string&gt; CheckMandatoryFields(IEnumerable&lt;Alvao.API.Common.Model.Database.tColumn&gt; allCustomColumns, int ticketId)
    {
        IList&lt;string&gt; missingFields = [];

        var protectionInformation = GetProtectionInformation(allCustomColumns, ticketId, cultureInfo);
        if (protectionInformation == null)
            missingFields.Add("ReportSecurityIncidentToNukibInformationProtectionLevel");

        var category = GetCategory(allCustomColumns, ticketId, cultureInfo);
        if (category == null)
            missingFields.Add("ReportSecurityIncidentToNukibIncidentCategory");

        var typeOfIncident = GetTypeOfIncident(allCustomColumns, ticketId, cultureInfo);
        if (typeOfIncident == null)
            missingFields.Add("ReportSecurityIncidentToNukibIncidentType");

        var status = GetStatus(allCustomColumns, ticketId, cultureInfo);
        if (status == null)
            missingFields.Add("ReportSecurityIncidentToNukibStatus");

        var typeOfReport = GetTypeOfReport(allCustomColumns, ticketId, cultureInfo);
        if (typeOfReport == null)
            missingFields.Add("ReportSecurityIncidentToNukibReportOf");

        var continuation = GetContinuation(allCustomColumns, ticketId, cultureInfo);
        if (continuation == null)
            missingFields.Add("ReportSecurityIncidentToNukibContinuation");

        var discoveryDate = ParseColumnValueToDateTime(GetCustumColumnValueInTicket("ReportSecurityIncidentToNukibDateAndTimeIncidentWasDetected", ticketId));
        if (discoveryDate == null)
            missingFields.Add("ReportSecurityIncidentToNukibDateAndTimeIncidentWasDetected");

        var systemDetailTarget = GetCustumColumnValueInTicket("ReportSecurityIncidentToNukibSystemDetailTarget", ticketId);
        if (string.IsNullOrEmpty(systemDetailTarget))
            missingFields.Add("ReportSecurityIncidentToNukibSystemDetailTarget");

        var targetHostFeatures = GetCustumColumnValueInTicket("ReportSecurityIncidentToNukibTargetHostFeatures", ticketId);
        if (string.IsNullOrEmpty(targetHostFeatures))
            missingFields.Add("ReportSecurityIncidentToNukibTargetHostFeatures");

        var incidentDescription = GetCustumColumnValueInTicket("ReportSecurityIncidentToNukibIncidentDescription", ticketId);
        if (string.IsNullOrEmpty(incidentDescription))
            missingFields.Add("ReportSecurityIncidentToNukibIncidentDescription");

        var numberOfAffectedUsers = GetCustumColumnValueInTicket("ReportSecurityIncidentToNukibTheNumberOfAffectedUsers", ticketId);
        if (string.IsNullOrEmpty(numberOfAffectedUsers))
            missingFields.Add("ReportSecurityIncidentToNukibTheNumberOfAffectedUsers");

        var numberOfAffectedSystems = GetCustumColumnValueInTicket("ReportSecurityIncidentToNukibTheNumberOfAffectedSystems", ticketId);
        if (string.IsNullOrEmpty(numberOfAffectedSystems))
            missingFields.Add("ReportSecurityIncidentToNukibTheNumberOfAffectedSystems");

        var sender = GetReportSender(ticketId, cultureInfo);
        if (sender == null)
            missingFields.Add("ReportSecurityIncidentToNukibSender");

        return missingFields;
    }

    private bool StartApproving(int ticketId, int stateId)
    {
        var approverId = GetApproverId(stateId);
        if (approverId == null)
        {
            CreateLog(ticketId, localizations[localeId][StartApprovingTitle], localizations[localeId][NoApprover]);
            return false;
        }

        var processId = GetProcessId(ticketId);
        var statesInProcess = Alvao.API.SD.TicketState.GetFromProcess(processId);
        var approvingState = statesInProcess.FirstOrDefault(s =&gt; s._TicketState == localizations[localeId][ApprovingStatusName]);
        double expirationHours = 0;
        if (approvingState != null &amp;&amp; approvingState.ApprovalExpirationHours != null)
        {
            expirationHours = (double)approvingState.ApprovalExpirationHours;
        }

        Approval.EnterByPerson(ticketId, (int)approverId, expirationHours, null, new Alvao.API.Common.Model.HtmlTextModel(string.Empty));

        return true;
    }

    private void ChangeToNextStateAfterReport(SqlConnection con, SqlTransaction trans, int ticketId, int actualState)
    {
        // change only when next status is Resolved
        var processId = GetProcessId(ticketId);
        var previousState = GetApprovalStateId(processId);
        if (previousState == null)
            return;

        var securityManagerId = GetApproverId((int)previousState);
        if (securityManagerId != null &amp;&amp; IsNextStateResolvedState(con, trans, processId, actualState))
            Ticket.Resolve(ticketId, (int)securityManagerId, new Alvao.API.Common.Model.HtmlTextModel(string.Empty));
    }

    private bool IsNextStateResolvedState(SqlConnection con, SqlTransaction trans, int processId, int actualState)
    {
        var statesInProcess = Alvao.API.SD.TicketState.GetFromProcess(processId);
        var resolveState = statesInProcess.FirstOrDefault(s =&gt; s._TicketState == localizations[localeId][ResolvedStatusName]);
        if (resolveState == null)
            return false;

        var transit = con.QueryFirstOrDefault&lt;int&gt;(@"select 1 from TicketStateRelation where BeginTicketStateId = @actualState and EndTicketStateId = @resolveState",
            new { actualState = actualState, resolveState = resolveState.id }, trans);

        return transit == 1;
    }

    private int? GetApprovalStateId(int processId)
    {
        var statesInProcess = Alvao.API.SD.TicketState.GetFromProcess(processId);
        var approvingState = statesInProcess.FirstOrDefault(s =&gt; s._TicketState == localizations[localeId][ApprovingStatusName]);
        return approvingState == null ? 0 : approvingState.id;
    }

    private int? GetReportStateId(int processId)
    {
        var statesInProcess = Alvao.API.SD.TicketState.GetFromProcess(processId);
        var reportingState = statesInProcess.FirstOrDefault(s =&gt; s._TicketState == localizations[localeId][ReportingStatusName]);
        return reportingState == null ? 0 : reportingState.id;
    }

    private int? GetApproverId(int stateId)
    {
        var customColumnsForState = Alvao.API.Common.CustomColumn.GetAll("TicketStateCust", stateId);
        var approver = customColumnsForState.FirstOrDefault(c =&gt; c.ColumnName == "ReportSecurityIncidentToNukibApprover");
        if (approver == null || approver.Value == null || !int.TryParse(approver.Value.ToString(), out var approverId))
            return null;
        return approverId;
    }

    private string GetCustomColumnValueInProcess(string columnName, int stateId)
    {
        return GetCustomColumnValue("TicketStateCust", columnName, stateId);
    }

    private string GetCustumColumnValueInTicket(string columnName, int ticketId)
    {
        return GetCustomColumnValue("tHdTicketCust", columnName, ticketId);
    }

    private string GetCustomColumnValue(string tableName, string columnName, int entityId)
    {
        return Alvao.API.Common.CustomColumn.GetValue(tableName, columnName, entityId, cultureInfo, DbProperty.DefaultTimeZone) ?? string.Empty;
    }

    private string GetEnumCustomColumnExtra1Value(IEnumerable&lt;Alvao.API.Common.Model.Database.tColumn&gt; allCustomColumns, string columnName, int ticketId, CultureInfo cultureInfo)
    {
        var protectionColumnDef = allCustomColumns.FirstOrDefault(ac =&gt; ac.sColumn == columnName);
        if (protectionColumnDef == null)
            return string.Empty;
        var value = CustomColumn.GetValue("tHdTicketCust", columnName, ticketId, cultureInfo, DbProperty.DefaultTimeZone);
        var columns = CustomColumn.GetAll("tHdTicketCust", ticketId);
        var protectionColumn = columns.FirstOrDefault(c =&gt; c.ColumnName == columnName);
        if (protectionColumn == null)
            return string.Empty;
        var columnValue = CustomColumn.SuggestValues(protectionColumnDef.iColumnId, null, value, localeId);

        if (columnValue == null || protectionColumn.Value == null || !int.TryParse(protectionColumn.Value.ToString(), out var protectionColumnId))
            return string.Empty;

        var selectedValue = columnValue.FirstOrDefault(c =&gt; c.iColumnValueId == protectionColumnId);
        if (selectedValue == null)
            return string.Empty;
        return selectedValue.sExtra1;
    }

    private STDegreeProtectionInformation? GetProtectionInformation(IEnumerable&lt;Alvao.API.Common.Model.Database.tColumn&gt; allCustomColumns, int ticketId, CultureInfo cultureInfo)
    {
        var extraColumnValue = GetEnumCustomColumnExtra1Value(allCustomColumns, "ReportSecurityIncidentToNukibInformationProtectionLevel", ticketId, cultureInfo);

        if (extraColumnValue == STDegreeProtectionInformation.Personal.ToString())
            return STDegreeProtectionInformation.Personal;
        if (extraColumnValue == STDegreeProtectionInformation.Restricted.ToString())
            return STDegreeProtectionInformation.Restricted;
        if (extraColumnValue == STDegreeProtectionInformation.Unrestricted.ToString())
            return STDegreeProtectionInformation.Unrestricted;

        return null;
    }

    private STtlp? GetTlp(IEnumerable&lt;Alvao.API.Common.Model.Database.tColumn&gt; allCustomColumns, int ticketId, CultureInfo cultureInfo)
    {
        var extraColumnValue = GetEnumCustomColumnExtra1Value(allCustomColumns, "ReportSecurityIncidentToNukibSTtlp", ticketId, cultureInfo);

        if (extraColumnValue == STtlp.AMBER.ToString())
            return STtlp.AMBER;
        if (extraColumnValue == STtlp.GREEN.ToString())
            return STtlp.GREEN;
        if (extraColumnValue == STtlp.RED.ToString())
            return STtlp.RED;
        if (extraColumnValue == STtlp.WHITE.ToString())
            return STtlp.WHITE;

        return null;
    }

    private STCategory? GetCategory(IEnumerable&lt;Alvao.API.Common.Model.Database.tColumn&gt; allCustomColumns, int ticketId, CultureInfo cultureInfo)
    {
        var extraColumnValue = GetEnumCustomColumnExtra1Value(allCustomColumns, "ReportSecurityIncidentToNukibIncidentCategory", ticketId, cultureInfo);

        if (extraColumnValue == STCategory.I.ToString())
            return STCategory.I;
        if (extraColumnValue == STCategory.II.ToString())
            return STCategory.II;
        if (extraColumnValue == STCategory.III.ToString())
            return STCategory.III;

        return null;
    }

    private STTypeOfIncident? GetTypeOfIncident(IEnumerable&lt;Alvao.API.Common.Model.Database.tColumn&gt; allCustomColumns, int ticketId, CultureInfo cultureInfo)
    {
        var extraColumnValue = GetEnumCustomColumnExtra1Value(allCustomColumns, "ReportSecurityIncidentToNukibIncidentType", ticketId, cultureInfo);

        if (extraColumnValue == "CSI cyber attack")
            return STTypeOfIncident.CSIcyberattack;
        if (extraColumnValue == "CSI malicious code")
            return STTypeOfIncident.CSImaliciouscode;
        if (extraColumnValue == "CSI compromised")
            return STTypeOfIncident.CSIcompromised;
        if (extraColumnValue == "CSI violation")
            return STTypeOfIncident.CSIviolation;
        if (extraColumnValue == "CSI APT")
            return STTypeOfIncident.CSIAPT;
        if (extraColumnValue == "Other CSI")
            return STTypeOfIncident.OtherCSI;
        if (extraColumnValue == "CSI confidentiality")
            return STTypeOfIncident.CSIconfidentiality;
        if (extraColumnValue == "CSI integrity")
            return STTypeOfIncident.CSIintegrity;
        if (extraColumnValue == "CSI availability")
            return STTypeOfIncident.CSIavailability;
        if (extraColumnValue == "CSI combination")
            return STTypeOfIncident.CSIcombination;

        return null;
    }

    private STClassification? GetClassification(IEnumerable&lt;Alvao.API.Common.Model.Database.tColumn&gt; allCustomColumns, int ticketId, CultureInfo cultureInfo)
    {
        var extraColumnValue = GetEnumCustomColumnExtra1Value(allCustomColumns, "ReportSecurityIncidentToNukibSTClassification", ticketId, cultureInfo);

        if (extraColumnValue == "Abusive Content")
            return STClassification.AbusiveContent;
        if (extraColumnValue == "Malicious Code")
            return STClassification.MaliciousCode;
        if (extraColumnValue == "Information Gathering")
            return STClassification.InformationGathering;
        if (extraColumnValue == STClassification.Intrusions.ToString())
            return STClassification.Intrusions;
        if (extraColumnValue == "Intrusion Attempts")
            return STClassification.IntrusionsAttempts;
        if (extraColumnValue == STClassification.Availability.ToString())
            return STClassification.Availability;
        if (extraColumnValue == "Information Security")
            return STClassification.InformationSecurity;
        if (extraColumnValue == STClassification.Fraud.ToString())
            return STClassification.Fraud;
        if (extraColumnValue == STClassification.Other.ToString())
            return STClassification.Other;

        return null;
    }

    private STStatus? GetStatus(IEnumerable&lt;Alvao.API.Common.Model.Database.tColumn&gt; allCustomColumns, int ticketId, CultureInfo cultureInfo)
    {
        var extraColumnValue = GetEnumCustomColumnExtra1Value(allCustomColumns, "ReportSecurityIncidentToNukibStatus", ticketId, cultureInfo);

        if (extraColumnValue == STStatus.Ongoing.ToString())
            return STStatus.Ongoing;
        if (extraColumnValue == "Under control")
            return STStatus.Undercontrol;
        if (extraColumnValue == STStatus.Restored.ToString())
            return STStatus.Restored;
        if (extraColumnValue == STStatus.Unknown.ToString())
            return STStatus.Unknown;

        return null;
    }

    private STTypeOfReport? GetTypeOfReport(IEnumerable&lt;Alvao.API.Common.Model.Database.tColumn&gt; allCustomColumns, int ticketId, CultureInfo cultureInfo)
    {
        var extraColumnValue = GetEnumCustomColumnExtra1Value(allCustomColumns, "ReportSecurityIncidentToNukibReportOf", ticketId, cultureInfo);

        if (extraColumnValue == STTypeOfReport.Incident.ToString())
            return STTypeOfReport.Incident;
        if (extraColumnValue == STTypeOfReport.Event.ToString())
            return STTypeOfReport.Event;

        return null;
    }

    private STContinuation? GetContinuation(IEnumerable&lt;Alvao.API.Common.Model.Database.tColumn&gt; allCustomColumns, int ticketId, CultureInfo cultureInfo)
    {
        var extraColumnValue = GetEnumCustomColumnExtra1Value(allCustomColumns, "ReportSecurityIncidentToNukibContinuation", ticketId, cultureInfo);

        if (extraColumnValue == STContinuation.Initiation.ToString())
            return STContinuation.Initiation;
        if (extraColumnValue == STContinuation.Continuation.ToString())
            return STContinuation.Continuation;

        return null;
    }

    private DateTime ParseColumnValueToDateTime(string value)
    {
        DateTime.TryParse(value.ToString(), out var date);
        return date;
    }

    private uint? ParseColumnValueToUint(string value)
    {
        if (value == null)
            return null;
        if (uint.TryParse(value.ToString(), out var incidentId))
            return incidentId;
        return null;
    }

    private Alvao.API.Common.Model.Database.tPerson GetReportSender(int ticketId, CultureInfo cultureInfo)
    {
        var customColumnsForProcess = Alvao.API.Common.CustomColumn.GetAllColumns("TicketStateCust").ToList();
        if (customColumnsForProcess == null)
            return null;
        var processId = GetProcessId(ticketId);
        var reportingStateId = GetReportStateId(processId);
        if (reportingStateId == null)
            return null;
        var sender = GetCustomColumnValueInProcess("ReportSecurityIncidentToNukibSender", (int)reportingStateId);

        var senderColumn = customColumnsForProcess.FirstOrDefault(c =&gt; c.sColumn == "ReportSecurityIncidentToNukibSender");
        if (senderColumn == null)
            return null;

        var columnValue = CustomColumn.SuggestValues(senderColumn.iColumnId, null, sender, localeId);
        if (columnValue == null)
            return null;
        
        var senderPerson = columnValue.FirstOrDefault(c =&gt; c.mColumnValue == sender);
        if (senderPerson == null)
            return null;
        return Alvao.API.Common.Person.GetById(senderPerson.iColumnValueId);
    }
}

public class Attachment : Alvao.API.Common.Model.IAttachment
{
     private Alvao.API.Common.Model.Database.tDocument document;
     public Attachment(Alvao.API.Common.Model.Database.tDocument document)
     {
         this.document = document;
     }
     public string Name =&gt; document.sDocument;
     public Guid UniqueName { get; } = Guid.NewGuid();
     public int? DocumentId
     {
         get =&gt; document.iDocumentId == 0 ? null : (int?) document.iDocumentId;
         set =&gt; document.iDocumentId = value ?? 0;
     }
     public string ContentType =&gt; document.sDocumentContentType;
     public string Url =&gt; document.Url;
     public bool EmbeddedImage =&gt; document.EmbededImage;
     public Stream GetStream()
     {
         return new MemoryStream(document.oDocument);
     }
}</Code>
          <IsLibCode>false</IsLibCode>
          <Codesign>pK/jam1FG6bK0RcVt3G5KKTjZD6YXhkQq/jYaHPiui6n1YkfvtVPHrZxIgjRAvCH8CetUFO6TAgeT3Yz2aYyPeVPwxno25A5bGTBAbpiYh7Ro3zAhWxBptrD4R2//dbqbZRDpjt8C7UH1+sULPqIPs6eVb2P4K2IT0PHjHDOMha1wsEEZLiz6uQa76dLxRtr7TTCFIFlDaIj3KHsuOCYIUNDbYL/v8FBjj0asIgK2OCSnjtC8Y0jW0VQKTan3lQXNL9YEoaEK7K3dGOdFaOqVBvXz5oQIcfV15o3tRDcHJCW3FjTF/8Tv9GIlgI6ugX2nwgXo9jWAyIkJSTZJwi/RA==</Codesign>
        </Script>
      </Scripts>
    </Application>
  </Applications>
</AlvaoApplication>