Scheduler Changes

SchedulerLog Table Script

CREATE TABLE SchedulerLog (
    id INT PRIMARY KEY IDENTITY(1,1),
    activityname NVARCHAR(75),
    activitydate DATETIME,
    status NVARCHAR(1),
    notes NVARCHAR(500)
);
--- for task list 
INSERT INTO TaskList(TaskCode, TaskName) VALUES ('008','Delete Scheduler Log')

Changes in TaskSchedulerEmail

Need to add Method for InsertSchedulerLog

INSERT INTO schedulerlog(activity, activitydate, status, notes)
VALUES (@activity, @activitydate, @status, @notes)

Changes in branch

In ArEmailScheduler, IndentEmailScheduler, InvoiceEmailScheduler, SalesOrderEmailScheduler, VoucherPaymentEmailScheduler need to add try catch logic similar to VoucherreceiptScheduler. For Catch errors mail will be sent to admins
In ArEmailScheduler, IndentEmailScheduler, InvoiceEmailScheduler, SalesOrderEmailScheduler, VoucherPaymentEmailScheduler, VoucherReceiptEmailScheduler, need to comment the logic which includes LogMessage and insertion in a csv file
Instead of LogMessage we will call InsertSchedulerLog Method, for eg.
Directly pass class

//---old code
if (string.IsNullOrEmpty(stdInstructions))
{
    LogMessage(logFilePath, $"AR Email, {DateTime.Now}, Failed, No Standard Instruction found");
}

//--new code 
_TaskScheduler.ActivityName = "Ar Email";

if (string.IsNullOrEmpty(stdInstructions))
{
    _TaskScheduler.ActivityDate = DateTime.Now;
    _TaskScheduler.Status = "F";
    _TaskScheduler.Notes = "No Standard Instruction found";
        
    _ITaskSchedulerRepository.InsertSchedulerLog(_TaskScheduler, dbname);
}

Changes in ErpLogReportOption

Need to comment existing download logic for LogType B, we will call a method SchedulerLogReport similar to logType A for Scheduler Log also

Label Name	Property Name
Company Name	CompanyName
Report Name	Scheduler Log Report
Report Date	DateTime.Now
Date Range	FromDate To ToDate
Activity Name	ActivityName
Activity Date	ActivityDate
Status	StatusName
Notes	Notes

Changes in ErpLogReportRepository

Need to add SchedulerLogReport Method

SELECT activity, activitydate, 
IIF(status = 'F', 'Failed', 'Sucess') AS statusname, notes 
FROM schedulerlogreport 
ORDER BY activitydate DESC

Changes in ErpLogReportValidate

If Log type is B then we will allow to download reports only for 1 months so need to add validations for it

New DeleteSchedulerLog

Need to create a DeleteSchedulerLog

using Coravel.Invocable;
using ErpCrystal_MFG.Api.Repositories;
using ErpCrystal_MFG.Models;
using ErpCrystal_MFG.Api.Controllers;

namespace ErpCrystal_MFG.Api.TaskScheduler
{
    public class DeleteSchedulerLog(
        IEnumerable<string> dbnamelist,
        ITaskSchedulerRepository itaskschedulerrepository,
        IEmailRepository iemailrepository,
        IUtilityMethodsRepository iutilitymethodsrepository,
        ITwoFactorAuthRepository itwofactorauthrepository

    ) : IInvocable
    {
        private readonly IEnumerable<string> _DbNameList = dbnamelist;
        private readonly ITaskSchedulerRepository _ITaskSchedulerRepository = itaskschedulerrepository;
        private readonly IEmailRepository _IEmailRepository = iemailrepository;
        private readonly IUtilityMethodsRepository _IUtilityMethodsRepository = iutilitymethodsrepository;
        private readonly TaskSchedulerConfig _TaskScheduler = new();
        private readonly ITwoFactorAuthRepository _ITwoFactorAuthRepository = itwofactorauthrepository;


        public async Task Invoke()
        {
            foreach (var dbname in _DbNameList)
            {
                try
                {
                    await _ITaskSchedulerRepository.DeleteSchedulerLog(dbname);
                    var sysAllowJvTypeValue = _IUtilityMethodsRepository.GetSysParameterValue(dbname, "EnableQueueReports");
                    var isactiveValue = sysAllowJvTypeValue.IsActive;
                    
                    if (isactiveValue == "Y")
                    { 
                        await _ITaskSchedulerRepository.DeleteQueueLog(dbname);
                    }

                }
                catch (Exception ex)
                {
                    var adminEmail = _ITwoFactorAuthRepository.GetAdminEmail(dbname);
                    if (!string.IsNullOrEmpty(adminEmail))
                    {
                        var emailController = new EmailController(_IEmailRepository, _IUtilityMethodsRepository);
                        ReceiverEmail _ReceiverEmail = new()
                        {
                            email1 = adminEmail,
                            emailsubject = $"Delete Scheduler Log Error for {dbname}",
                            emailmessage = $"An error occurred in the Delete Scheduler Log for database '{dbname}'.<br><br><b>Exception:</b><br>{ex.Message}<br><br><b>Stack Trace:</b><br>{ex.StackTrace}"
                        };
                        await emailController.SendEmail(dbname, _ReceiverEmail, "S");
                    }
                        
                    _TaskScheduler.ActivityName = "Delete Scheduler Log";
                    _TaskScheduler.ActivityDate = DateTime.Now;
                    _TaskScheduler.Status = "F";
                    _TaskScheduler.Notes = $"Exception: {ex.Message}";
                    await _ITaskSchedulerRepository.InsertSchedulerLog(_TaskScheduler, dbname);
                }
            }
        }
    }
}

Changes in TaskSchedulerInitialize

In TaskSchedulerInitialize make the following changes

case "008": // Chart Insert Scheduler Task
    scheduler
    .ScheduleWithParams<DeleteSchedulerLog>(dbList) // Schedules ChartInsertSheduler.
    .Cron(cron)
    .PreventOverlapping($"{nameof(DeleteSchedulerLog)}_{cron}")
    .Zoned(TimeZoneInfo.Local);
break;

Changes in TaskSchedulerRepository

Need to add Method in TaskSchedulerRepository DeleteScheduleLog and DeleteQueueLog

DELETE FROM schedulerlog
WHERE CONVERT(DATE, activitydate) < DATEADD(MONTH, -3, GETDATE());

DELETE FROM Queuelog
        WHERE CONVERT(DATE, queuedate) < DATEADD(DAY, -2, GETDATE())

QR-Code Jobwork Sysparameter Changes