Simplify CHS - [Renewal Scheduler]

Script

INSERT INTO TaskList(TaskCode, TaskName,TaskType) VALUES
('003','Society Document Renewals','S'),
('004','Investment Renewals','S'),
('005','Vehicle Parking Renewal','S'),
('006','Delete Scheduler Log','S')

--- This table will be used for log insertion > changed approched of mfg's CSV approch
CREATE TABLE SchedulerLog (
    id INT PRIMARY KEY IDENTITY(1,1),
    activityname NVARCHAR(75),
    activitydate DATETIME,
    status NVARCHAR(1),
    notes NVARCHAR(500)
);

Society Document Renewal Scheduler

-- Scheduler 
SELECT docno,docname,description,validupto 
FROM Evault
WHERE validupto <= DATEADD(MONTH, 1, CAST(GETDATE() AS DATE))
ORDER BY validupto DESC;

-- Export To Xl 
SELECT docno, docname, validupto FROM evault
ORDER BY validupto DESC


-- Use Utility 
SELECT email FROM EntityMst

Currently, Excel export is not available in eVault. We will implement the Excel export functionality in the eVault controller and invoke it from the scheduler, similar to the AR/AP process.
Add an Export to Excel option on the eVault index page.
Separate methods will be used for fetching Scheduler data and Excel (XL) data.
The Scheduler will call its repository method during initialization. The XL method will reuse this data, and if data is already available, it will skip calling the XL query from the repository.

A scheduler is required to send renewal reminder emails using our existing Email Scheduler module.
The user will schedule this job to run automatically on the 1st day of every month; no additional validation is needed.
When the scheduler runs, it must scan the Evault table and fetch all records where the Document Expiry Date (validupto) is less than or equal to the current date + 1 month.
The reminder email will be sent to the corresponding Entity Mst Email.

Investment Renewal Scheduler

-- Fetch investments maturing within the next 15 days OR already expired
SELECT I.invno AS investno,I.details AS investdetails,B.bankname AS investbankname,I.startdate,
I.enddate,I.principal AS principalamount,I.roi,I.matvalue AS maturityamount,I.notes,
N.name AS investorname,C.description AS investcodename FROM Investment I
LEFT JOIN investbankmst B ON I.bankcode = B.bankcode
LEFT JOIN investname N ON I.name = N.code
LEFT JOIN investcode C ON I.code = C.code
WHERE I.Enddate <= DATEADD(DAY, 15, CAST(GETDATE() AS DATE)) AND I.Status = 'L'
ORDER BY I.enddate

-- Use Utility
SELECT email FROM EntityMst;

A weekly scheduler is required to send investment maturity reminder emails using our existing Email Scheduler module.
The job will run every week and will scan the Investment table for records where the maturity date (Enddate) is less than or equal to current date + 15 days, or already expired.
For example, if scheduled on 10-Dec-2025, all investments with Enddate ≤ 25-Dec-2025 will be included in the email.
The reminder email will be sent to the corresponding Entity Mst Email.

Send Email for live investments whose End Date is within the next 15 days or already expired but still marked as live.
Implement this using the AR/AP approach.

Separate methods will be used for fetching Scheduler data and Excel (XL) data.
The Scheduler will call its repository method during initialization. The XL method will reuse this data, and if data is already available, it will skip calling the XL query from the repository.

Vehicle Parking Renewal Scheduler


SELECT PM.id, Name AS membername,PSM.Slotname AS ParkingSlotName,FromDate,ToDate, allotmentno, M.email
FROM ParkingAllotment PM
LEFT JOIN membermst M ON PM.memberid = M.memberid 
LEFT JOIN ParkingSlotMst PSM ON PM.SlotNo = PSM.SlotNo 
WHERE ToDate <= DATEADD(DAY, 15, CAST(GETDATE() AS DATE))



-- Fetch Member Emails for expiring parking allotments
GetMemberEmail
SELECT email FROM MemeberMst WHERE memberid = @memberid AND Email LIKE '%@%.%'

A weekly scheduler is required to send vehicle parking renewal reminder emails using our existing Email Scheduler module.
The scheduler scans the ParkingAllotment table weekly to identify parking expiry dates (ToDate) that fall within current date + 15 days or have already expired.
Example: If scheduled on 10-Dec-2025, all records with ToDate ≤ 25-Dec-2025 will be included.
The email notification will be sent to the Member whose parking allotment is expiring.

Test Data


-- Evault
INSERT INTO Evault (docno, docname, description, validupto)
VALUES
-- 1. Backdated (Already expired)
('000001', 'Fire NOC', 'Annual Fire NOC Renewal', '2024-12-15'),

-- 2. Upcoming (Expires within next 1 month)
('000002', 'Lift AMC', 'Lift Maintenance Contract', DATEADD(DAY, 20, GETDATE())),

-- 3. Not Expired (Expires after 1 month)
('000003', 'Property Tax', 'Property Tax Certificate', DATEADD(MONTH, 3, GETDATE()));




-- Investment

INSERT INTO Investment
(Invno, Details, BankCode, Startdate, Enddate, Principal, ROI, MatValue, Status, Notes, Name, Code)
VALUES
-- 1. Backdated
('000001', 'FD in HDFC Bank', 'HDF', '2023-01-01', '2024-12-10', 50000, 7.5, 53750, 'A', 'Expired FD', 'HDF', '001'),

-- 2. Upcoming (within 15 days)
('000002', 'FD in SBI Bank', 'SBI', '2024-05-01', DATEADD(DAY, 10, GETDATE()), 80000, 6.75, 84200, 'A', 'Expiring soon', 'SBI', '002'),

-- 3. Not Expired
('000003', 'FD in ICICI Bank', 'ICI', '2024-06-01', DATEADD(MONTH, 2, GETDATE()), 100000, 7.0, 107000, 'A', 'Active FD', 'ICI', '003');



-- ParkingAllotment

INSERT INTO ParkingAllotment (memberid, allotmentno, SlotNo, FromDate, ToDate)
VALUES
-- 1. Backdated
('000001', '000001', 'A-12', '2024-01-01', '2024-12-15'),

-- 2. Upcoming (within 15 days)
('000002', '000002', 'B-07', '2024-02-01', DATEADD(DAY, 10, GETDATE())),

-- 3. Not expired
('000003', '000003', 'C-03', '2024-03-01', DATEADD(MONTH, 2, GETDATE()));

Delete Scheduler Log

A new scheduler must be added to automatically clean old records from the SchedulerLog table. The purpose is to remove unnecessary logs older than 3 months from today’s date to maintain system performance and reduce database size.

Functionality

The scheduler will run periodically (Monthly — based on system default Needs to be confimred).
It will delete all entries from SchedulerLog where:

activitydate < current date - 1 months

No user input or validation is required.
This cleanup process helps prevent the SchedulerLog table from becoming too large over time.

SQL Query to Delete Logs Older Than 3 Months

DELETE FROM SchedulerLog
WHERE activitydate < DATEADD(MONTH, -3, CAST(GETDATE() AS DATE));

Log the Cleanup Activity Itself

INSERT INTO SchedulerLog (activityname, activitydate, status, notes)
VALUES
('Delete Scheduler Log Cleanup', GETDATE(), 'S', 'Old logs older than 3 months deleted successfully');

Points which needs to be taken care while development

We will have the try catch block and in catch we will send the email like below given expample of Billing Scheduler

var emailController = new EmailController(_IEmailRepository, _IUtilityMethodsRepository);
var _ReceiverEmail = new ReceiverEmail
{
    email1 = "support@erpcrystal.in", // System Admin Email
    emailsubject = $"Billing Scheduler Error for {dbName}",
    emailmessage = $"An error occurred in the Billing Scheduler 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, "C","N");

We will also use the new log logic inated of writing the log in csv we will have in the SchedulerLog table

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

-- Eg
//--new code
_TaskScheduler.ActivityName = "Billing Scheduler";

if (string.IsNullOrEmpty(stdInstructions))
{
    _TaskScheduler.ActivityDate = DateTime.Now;
    _TaskScheduler.Status = "F";
    _TaskScheduler.Notes = "No Email Found";

    _ITaskSchedulerRepository.InsertSchedulerLog(_TaskScheduler, dbname);
}

Other Script

ALTER TABLE TaskList ADD TaskType NVARCHAR(1);
 UPDATE TaskList SET TaskType = 'S';

Next round

queue log deletion - 48 hr before

CHS Under Development CHS Minutes - New