using Dapr; using FrameworkDAL.DTO.Address; using FrameworkDAL.DTO.AutoScheduler; using FrameworkDAL.DTO.Employee; using FrameworkDAL.DTO.EventType; using FrameworkDAL.DTO.MAction; using FrameworkDAL.DTO.Party; using FrameworkDAL.DTO.Port; using FrameworkDAL.DTO.Region; using FrameworkDAL.DTO.Scheduler; using FrameworkDAL.DTO.User; using FrameworkDAL.Query.AutoScheduler; using FrameworkDAL.Query.JobDefine; using GB5Shared.DTO.Framework.Criteria; using GB5Shared.DTO.Framework.Login; using GB5Shared.GB5Exception; using GB5Shared.GenerateAutoNumber; using GB5Shared.QueryExecutor; using Newtonsoft.Json; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading; using System.Threading.Tasks; namespace FrameworkDAL.CustomCode.AutoScheduler { public class AutoSchedulerDAL : IAutoSchedulerDAL { private readonly IQueryExecutor _QueryExecutor; public AutoSchedulerDAL(IQueryExecutor IQueryExecutor) { _QueryExecutor = IQueryExecutor; } public async Task GetAutoScheduler(AutoSchedulerDTO AutoSchedulerDTO, LoginDTO LoginDTO) { int SchedulerId = -1; try { if (AutoSchedulerDTO.MenuId == -1) throw new MissingMethodException("UNIB0172"); List AutoSchedulerResponseDTOList = new(); if (AutoSchedulerDTO.IsReportMenu == 0) { // --------------------------- // 1️⃣ FETCH JOB DEFINES // --------------------------- var JobDefineList = await _QueryExecutor.QueryAsync(LoginDTO, AutoSchedulerQB.GET_JOB_DEFINE_BY_MENU, new { MenuId = AutoSchedulerDTO.MenuId }); foreach (var JobDefine in JobDefineList) { if (JobDefine.BaseTenantId != LoginDTO.ClientId) continue; AutoSchedulerResponseDTO AutoSchedulerResponseDTO = new() { JobDefineId = JobDefine.JobDefineId, JobName = JobDefine.JobDefineJobName }; // --------------------------- // 2️⃣ FETCH SCHEDULER var Scheduler = (await _QueryExecutor.QueryAsync( LoginDTO, AutoSchedulerQB.GET_SCHEDULER_BY_ID, new { SchedulerId = JobDefine.JobDefineSchedulerId } )).FirstOrDefault(); if (Scheduler != null) { AutoSchedulerResponseDTO.SchedulerId = Scheduler.SchedulerId; AutoSchedulerResponseDTO.SchedulerName = Scheduler.SchedulerName; } // --------------------------- // 3️⃣ FETCH CRITERIA CONFIG // --------------------------- var CriteriaConfig = await _QueryExecutor.QuerySingleAsync( LoginDTO, AutoSchedulerQB.GET_CRITERIA_BY_ID, new { CriteriaConfigId = JobDefine.JobDefineCriteriaConfigId } ); if (CriteriaConfig != null) { AutoSchedulerResponseDTO.CriteriaConfigId = CriteriaConfig.CriteriaConfigId; AutoSchedulerResponseDTO.CriteriaConfigName = CriteriaConfig.CriteriaConfigName; } // --------------------------- // 4️⃣ FETCH MAction DETAILS // --------------------------- var MActions = await _QueryExecutor.QueryAsync( LoginDTO, AutoSchedulerQB.GET_MACTION_BY_JOBDEFINE, new { JobDefineId = JobDefine.JobDefineId } ); foreach (var MAction in MActions) { AutoSchedulerResponseDTO ActionResponse = new() { JobDefineId = JobDefine.JobDefineId, SchedulerId = Scheduler?.SchedulerId ?? SchedulerId, ActionId = MAction.MActionId, ActionName = MAction.MActionDescription, ReplyTo = MAction.MActionReplyTo, ToDeliveryType = MAction.MActionToDeliveryType, ToContentType = MAction.MActionToContentType, To = MAction.MActionTo, MailCCDeliveryType = MAction.MActionMailCCDeliveryType, MailCCContentType = MAction.MActionMailCCContentType, MailCC = MAction.MActionMailCC, MailBCCDeliveryType = MAction.MActionMailBCCDeliveryType, MailBCCContentType = MAction.MActionMailBCCContentType, MailBCC = MAction.MActionMailBCC, ExportFormat = MAction.MActionExportFormat, ReportType = MAction.MActionReportType, ActionType = MAction.MActionType, ActionForType = MAction.MActionActionForType, Type = MAction.MActionType, ReportId = MAction.ReportId, ReportFormatId = MAction.ReportFormatId, MailTemplateId = MAction.TemplateId, FileId = MAction.MailFileId, EventTypeId = MAction.EventTypeId, EventTypeName = MAction.EventTypeName, GroupByFields = MAction.MActionGroupByFields }; AutoSchedulerResponseDTOList.Add(ActionResponse); } } } else { if (AutoSchedulerDTO.MenuEntityId == -1) throw new MissingMethodException("UNIB0182"); // --------------------------- // 1️⃣ FETCH EVENT TYPES // --------------------------- var EventTypes = await _QueryExecutor.QueryAsync( LoginDTO, AutoSchedulerQB.GET_EVENT_TYPE_BY_ENTITY, new { EntityId = AutoSchedulerDTO.MenuEntityId } ); foreach (var EventType in EventTypes.DistinctBy(e => e.EventTypeId)) { // --------------------------- // 2️⃣ FETCH MACTIONS BY EVENT // --------------------------- var MActions = await _QueryExecutor.QueryAsync( LoginDTO, AutoSchedulerQB.GET_MACTION_BY_EVENT_TYPE, new { EventTypeId = EventType.EventTypeId } ); foreach (var MAction in MActions) { AutoSchedulerResponseDTO AutoSchedulerResponseDTO = new() { JobDefineId = -1, SchedulerId = SchedulerId, ActionId = MAction.MActionId, ActionName = MAction.MActionDescription, ReplyTo = MAction.MActionReplyTo, ToDeliveryType = MAction.MActionToDeliveryType, ToContentType = MAction.MActionToContentType, To = MAction.MActionTo, MailCCDeliveryType = MAction.MActionMailCCDeliveryType, MailCCContentType = MAction.MActionMailCCContentType, MailCC = MAction.MActionMailCC, MailBCCDeliveryType = MAction.MActionMailBCCDeliveryType, MailBCCContentType = MAction.MActionMailBCCContentType, MailBCC = MAction.MActionMailBCC, ExportFormat = MAction.MActionExportFormat, ReportType = MAction.MActionReportType, ActionType = MAction.MActionType, ActionForType = MAction.MActionActionForType, Type = MAction.MActionType, ReportId = MAction.ReportId, ReportFormatId = MAction.ReportFormatId, MailTemplateId = MAction.TemplateId, FileId = MAction.MailFileId, EventTypeId = MAction.EventTypeId, EventTypeName = MAction.EventTypeName, GroupByFields = MAction.MActionGroupByFields }; AutoSchedulerResponseDTOList.Add(AutoSchedulerResponseDTO); } } } string Json = JsonConvert.SerializeObject(AutoSchedulerResponseDTOList); return Json; } catch (Exception ex) { throw new Exception($"Error while fetching AutoScheduler data: {ex.Message}", ex); } } public async Task ContentFillingAsync(short deliveryType, string fieldValue, LoginDTO loginDTO) { if (string.IsNullOrWhiteSpace(fieldValue)) return string.Empty; try { var ids = fieldValue.Split(',', StringSplitOptions.RemoveEmptyEntries) .Select(id => Convert.ToInt32(id.Trim())) .ToList(); if (ids.Count == 0) return string.Empty; var names = new List(); foreach (var id in ids) { switch (deliveryType) { case 0: // Employee var employee = await _QueryExecutor.QuerySingleAsync( loginDTO, AutoSchedulerQB.GET_EMPLOYEE_BY_ID, new { EmployeeId = id }); if (employee != null) names.Add(employee.EmployeeName); break; case 1: // Role var role = await _QueryExecutor.QuerySingleAsync( loginDTO, AutoSchedulerQB.GET_ROLE_BY_ID, new { RoleId = id }); if (role != null) names.Add(role.RoleName); break; case 2: // Content case 3: // Others return fieldValue; case 5: // Party var party = await _QueryExecutor.QuerySingleAsync( loginDTO, AutoSchedulerQB.GET_PARTY_BY_ID, new { PartyId = id }); if (party != null) names.Add(party.PartyName); break; case 6: // User var user = await _QueryExecutor.QuerySingleAsync( loginDTO, AutoSchedulerQB.GET_USER_BY_ID, new { UserId = id }); if (user != null) names.Add(user.UserName); break; case 7: // Contact var contact = await _QueryExecutor.QuerySingleAsync( loginDTO, AutoSchedulerQB.GET_CONTACT_BY_ID, new { ContactId = id }); if (contact != null) names.Add(contact.ContactName); break; case 8: // UserGroup var userGroup = await _QueryExecutor.QuerySingleAsync( loginDTO, AutoSchedulerQB.GET_USERGROUP_BY_ID, new { UserGroupId = id }); if (userGroup != null) names.Add(userGroup.UserGroupName); break; default: break; } } return string.Join(", ", names); } catch (Exception ex) { throw new Exception($"Error in ContentFillingAsync for DeliveryType {deliveryType}: {ex.Message}", ex); } } public async Task<(string Message, int JobId)> SaveAutoScheduler(AutoSchedulerDTO AutoSchedulerDTO, LoginDTO LoginDTO) { if (AutoSchedulerDTO == null) throw new ArgumentNullException(nameof(AutoSchedulerDTO)); if (string.IsNullOrWhiteSpace(AutoSchedulerDTO.JobName)) throw new Exception("Job Name cannot be empty."); // Auto-numbers are allocated outside the transaction so the counter // always advances in auto-commit and is never rolled back on error. var jobId = AutoSchedulerDTO.JobDefineId == 0 ? await GetNextNumber("JOBDEFINE", LoginDTO) : AutoSchedulerDTO.JobDefineId; var actionId = AutoSchedulerDTO.ActionId == 0 ? await GetNextNumber("MACTION", LoginDTO) : AutoSchedulerDTO.ActionId; var eventTypeActionId = AutoSchedulerDTO.EventtypeActionId == 0 ? await GetNextNumber("EVENTTYPEACTION", LoginDTO) : AutoSchedulerDTO.EventtypeActionId; var eventTypeActionDetailId = await GetNextNumber("EVENTTYPEACTIONDETAIL", LoginDTO); var tx = await _QueryExecutor.BeginTransactionAsync(LoginDTO); try { // Insert JobDefine if new if (AutoSchedulerDTO.JobDefineId == 0) { jobId = await _QueryExecutor.QuerySingleAsync( LoginDTO, AutoSchedulerQB.INSERT_JOBDEFINE, new { JobId = jobId, JobName = AutoSchedulerDTO.JobName, SchedulerId = AutoSchedulerDTO.SchedulerId, WebServiceId = AutoSchedulerDTO.WebServiceId, CriteriaConfigId = AutoSchedulerDTO.CriteriaConfigId, CreatedById = LoginDTO.UserId, ModifiedById = LoginDTO.UserId, MenuId = AutoSchedulerDTO.MenuId, RunAsUserId = LoginDTO.UserId, RunAsRoleId = LoginDTO.RoleId, RunAsOUId = LoginDTO.WorkOUId, RunAsBranchId = LoginDTO.WorkPartyBranchId, RunAsWorkPartyId = LoginDTO.WorkPartyId, RunAsStoreId = LoginDTO.WorkStoreId, RunAsPeriodId = LoginDTO.WorkPeriodId }, tx); } // Insert MAction if new if (AutoSchedulerDTO.ActionId == 0) { await _QueryExecutor.ExecuteAsync(LoginDTO, AutoSchedulerQB.INSERT_MACTION, new { ActionId = actionId, Description = AutoSchedulerDTO.JobName, EventTypeId = AutoSchedulerDTO.EventTypeId, ActionType = AutoSchedulerDTO.ActionType, ReplyTo = AutoSchedulerDTO.ReplyTo, TemplateId = AutoSchedulerDTO.MailtemplateId, ReportFormatId = AutoSchedulerDTO.ReportFormatId, ReportId = AutoSchedulerDTO.ReportId, MenuId = AutoSchedulerDTO.MenuId, ActionForType = AutoSchedulerDTO.ActionForType, JobId = jobId, ToDeliveryType = AutoSchedulerDTO.ToDeliveryType, ToContentType = AutoSchedulerDTO.ToContentType, To = AutoSchedulerDTO.To ?? "", MailCCDeliveryType = AutoSchedulerDTO.MailCCDeliveryType, MailCCContentType = AutoSchedulerDTO.MailCCContentType, MailCC = AutoSchedulerDTO.MailCC ?? "", MailBCCDeliveryType = AutoSchedulerDTO.MailBCCDeliveryType, MailBCCContentType = AutoSchedulerDTO.MailBCCContentType, MailBCC = AutoSchedulerDTO.MailBCC ?? "", ExportFormat = AutoSchedulerDTO.ExportFormat, ReportType = AutoSchedulerDTO.ReportType, MailFileId = AutoSchedulerDTO.FileId, WebServiceId = AutoSchedulerDTO.WebServiceId, CriteriaConfigId = AutoSchedulerDTO.CriteriaConfigId, SortOrder = 9999, SourceType = LoginDTO.SourceType, Status = 1, Version = 1, CreatedById = LoginDTO.UserId, ModifiedById = LoginDTO.UserId, UriTemplateValue = AutoSchedulerDTO.UriTemplateValue ?? "", ReportViewId = AutoSchedulerDTO.ReportViewId, MailListId = -1, IsFileBased = 1, TenantId = LoginDTO.ClientId, Remarks = "" }, tx); } // Insert EventTypeAction if new if (AutoSchedulerDTO.EventtypeActionId == 0) { await _QueryExecutor.ExecuteAsync(LoginDTO, AutoSchedulerQB.INSERT_EVENTTYPEACTION, new { EventTypeActionId = eventTypeActionId, EventTypeId = AutoSchedulerDTO.EventTypeId, ReportId = AutoSchedulerDTO.ReportId, CreatedById = LoginDTO.UserId, ModifiedById = LoginDTO.UserId }, tx); await _QueryExecutor.ExecuteAsync(LoginDTO, AutoSchedulerQB.INSERT_EVENTTYPEACTIONDETAIL, new { DetailId = eventTypeActionDetailId, EventTypeActionId = eventTypeActionId, SlNo = 1, ActionId = actionId, Description = AutoSchedulerDTO.JobName, EvalCondition = AutoSchedulerDTO.EventTypeActionDetailEvalCondition }, tx); } // Update MACTION to link JobId await _QueryExecutor.ExecuteAsync(LoginDTO, AutoSchedulerQB.UPDATE_MACTION_JOBID, new { JobId = jobId, ActionId = actionId }, tx); await _QueryExecutor.CommitAsync(tx); return ("Record Saved Successfully.", jobId); } catch (Exception ex) { await _QueryExecutor.RollbackAsync(tx); throw new Exception($"SaveAutoScheduler failed: {ex.Message}", ex); } } private async Task GetNextNumber(string entityName, LoginDTO LoginDTO) { var next = await _QueryExecutor.QuerySingleAsync( LoginDTO, AutoSchedulerQB.GET_NEXT_NUMBER, new { EntityName = entityName } ); if (next == null) throw new Exception($"GetNextNumber returned null for {entityName}"); object? val = next.NextNumber ?? next.NEXTNUMBER ?? next.NextNumber; return Convert.ToInt32(val); } public async Task> GetJobDefineAsync(int jobId, LoginDTO login, CancellationToken ct) { return await _QueryExecutor.QueryAsync( login, JobDefineQB.GET_JOB_DEFINE, new { JobId = jobId, TenantId = login.ClientId }, cancellationToken: ct).ConfigureAwait(false); } public async Task UpdateAutoSchedulerAsync(AutoSchedulerDTO dto, LoginDTO login, CancellationToken ct) { var tx = await _QueryExecutor.BeginTransactionAsync(login); try { await _QueryExecutor.ExecuteAsync(login, AutoSchedulerQB.UPDATE_JOBDEFINE, new { JobId = dto.JobDefineId, JobName = dto.JobName, SchedulerId = dto.SchedulerId, WebServiceId = dto.WebServiceId, CriteriaConfigId = dto.CriteriaConfigId, ModifiedById = login.UserId }, tx, ct).ConfigureAwait(false); if (dto.ActionId != 0) { await _QueryExecutor.ExecuteAsync(login, AutoSchedulerQB.UPDATE_MACTION, new { dto.ActionId, Description = dto.JobName, dto.EventTypeId, dto.ActionType, dto.ReplyTo, TemplateId = dto.MailtemplateId, dto.ReportFormatId, dto.ReportId, JobId = dto.JobDefineId, dto.ToDeliveryType, dto.ToContentType, To = dto.To ?? string.Empty, dto.MailCCDeliveryType, dto.MailCCContentType, MailCC = dto.MailCC ?? string.Empty, dto.MailBCCDeliveryType, dto.MailBCCContentType, MailBCC = dto.MailBCC ?? string.Empty, dto.ExportFormat, dto.ReportType, MailFileId = dto.FileId, WebServiceId = dto.WebServiceId, CriteriaConfigId = dto.CriteriaConfigId, ModifiedById = login.UserId, UriTemplateValue = dto.UriTemplateValue ?? string.Empty, dto.ActionForType, Remarks = string.Empty, ReportViewId = dto.ReportViewId }, tx, ct).ConfigureAwait(false); } await _QueryExecutor.CommitAsync(tx); } catch { await _QueryExecutor.RollbackAsync(tx); throw; } } public async Task DeleteJobDefineAsync(int jobId, LoginDTO login, CancellationToken ct) { var tx = await _QueryExecutor.BeginTransactionAsync(login); try { await _QueryExecutor.ExecuteAsync(login, JobDefineQB.DELETE_JOB_ACTIONS, new { JobId = jobId }, tx, ct).ConfigureAwait(false); await _QueryExecutor.ExecuteAsync(login, JobDefineQB.DELETE_JOB_DEFINE, new { JobId = jobId, TenantId = login.ClientId }, tx, ct).ConfigureAwait(false); await _QueryExecutor.CommitAsync(tx); } catch { await _QueryExecutor.RollbackAsync(tx); throw; } } } }