AI Insights [Inventory Ledger, Age Analysis]

CREATE TABLE dash.DashboardInventoryLedger 
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    DocType NVARCHAR(10) COLLATE DATABASE_DEFAULT,
    ItemClassName NVARCHAR(50) COLLATE DATABASE_DEFAULT,
    ItemGroupName NVARCHAR(50) COLLATE DATABASE_DEFAULT,
    ItemCategoryId NVARCHAR(3) COLLATE DATABASE_DEFAULT,
    ItemId NVARCHAR(6) COLLATE DATABASE_DEFAULT,
    ItemShortName NVARCHAR(100) COLLATE DATABASE_DEFAULT,
    ItemName NVARCHAR(150) COLLATE DATABASE_DEFAULT,
    BrandName NVARCHAR(50) COLLATE DATABASE_DEFAULT,
    StageName NVARCHAR(25) COLLATE DATABASE_DEFAULT,
    DocNo NVARCHAR(6) COLLATE DATABASE_DEFAULT,
    DocDate DATETIME,
    UomName NVARCHAR(5) COLLATE DATABASE_DEFAULT,
    OpeningQty NUMERIC(14,3),
    ReceiptQty NUMERIC(14,3),
    IssuedQty NUMERIC(14,3),
    BalanceQty NUMERIC(14,3),
    PartyShortName NVARCHAR(50) COLLATE DATABASE_DEFAULT,
    PartyName NVARCHAR(100) COLLATE DATABASE_DEFAULT,
    ItemRackInfo NVARCHAR(100) COLLATE DATABASE_DEFAULT,
    StoreName NVARCHAR(200) COLLATE DATABASE_DEFAULT,
    Description NVARCHAR(1000) COLLATE DATABASE_DEFAULT,
    ItemState NVARCHAR(1) COLLATE DATABASE_DEFAULT,
    unitcode NVARCHAR(2) COLLATE DATABASE_DEFAULT,
    unitname NVARCHAR(100) COLLATE DATABASE_DEFAULT,
    yearLabel NVARCHAR(7) COLLATE DATABASE_DEFAULT,
);


CREATE TABLE dash.DashboardInventoryAge
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    ItemId NVARCHAR(6) COLLATE DATABASE_DEFAULT,
    StageName NVARCHAR(25) COLLATE DATABASE_DEFAULT,
    ItemShortName NVARCHAR(100) COLLATE DATABASE_DEFAULT,
    ItemName NVARCHAR(150) COLLATE DATABASE_DEFAULT,
    ItemGroupName NVARCHAR(50) COLLATE DATABASE_DEFAULT,
    ItemSegmentName NVARCHAR(50) COLLATE DATABASE_DEFAULT,
    UomName NVARCHAR(5) COLLATE DATABASE_DEFAULT,
    QtyUpto15 NUMERIC(14,3),
    QtyUpto30 NUMERIC(14,3),
    QtyUpto60 NUMERIC(14,3),
    QtyUpto90 NUMERIC(14,3),
    QtyUpto180 NUMERIC(14,3),
    QtyUpto365 NUMERIC(14,3),
    QtyAbove365 NUMERIC(14,3),
    TotalQty NUMERIC(14,3),
    Rate NUMERIC(10,4),
    ValueUpto15 NUMERIC(14,2),
    ValueUpto30 NUMERIC(14,2),
    ValueUpto60 NUMERIC(14,2),
    ValueUpto90 NUMERIC(14,2),
    ValueUpto180 NUMERIC(14,2),
    ValueUpto365 NUMERIC(14,2),
    ValueAbove365 NUMERIC(14,2),
    TotalValue NUMERIC(14,2),
    ItemState NVARCHAR(1) COLLATE DATABASE_DEFAULT,
    yearLabel NVARCHAR(7) COLLATE DATABASE_DEFAULT,
    unitcode NVARCHAR(2) COLLATE DATABASE_DEFAULT,
    unitname NVARCHAR(100) COLLATE DATABASE_DEFAULT,
    itemcategory NVARCHAR(2) COLLATE DATABASE_DEFAULT
);

"Schema": "SCHEMA NAME: dash; TABLES: dash.DashboardGrn(Id INT IDENTITY PRIMARY KEY, grnno NVARCHAR(10), grndate DATE, UnitCodeName NVARCHAR(100), partyshortname NVARCHAR(250), partyname NVARCHAR(250), itemgroupname NVARCHAR(250), itemid NVARCHAR(10), itemshortname NVARCHAR(250), itemname NVARCHAR(250), pendingqty NUMERIC(15,3), indentrate NUMERIC(15,2), gstratename NVARCHAR(50), indentno NVARCHAR(10), indentdate DATE, challanno NVARCHAR(100), challandate DATE, grnnotes NVARCHAR(250), storename NVARCHAR(200), grnqty NUMERIC(15,3), partygroupname NVARCHAR(20), BillNo NVARCHAR(10), BillDate DATE, amount NUMERIC(15,2)), dash.DashboardSales(Id INT IDENTITY PRIMARY KEY, Itemid NVARCHAR(10), ItemName NVARCHAR(250), Partyid NVARCHAR(10), Partyname NVARCHAR(250), Docdate DATE, Amount NUMERIC(15,2), Unitnamecode NVARCHAR(100), Qty NUMERIC(15,3)), dash.DashboardArap(araptype NVARCHAR(10), partyshortname NVARCHAR(50), partyname NVARCHAR(150), doctype NVARCHAR(2), refdoc NVARCHAR(2), refno NVARCHAR(6), refdate DATETIME, docno NVARCHAR(6), docdate DATETIME, amount DECIMAL(18,2), netamount DECIMAL(18,2), gstamount DECIMAL(18,2), duedate DATETIME, aging INT, partyid INT, unitname NVARCHAR(100), creditdays INT, paymenttermname NVARCHAR(100), SalesmenName NVARCHAR(100), partyregionname NVARCHAR(100), collectionstatusname NVARCHAR(50), supplierbillno NVARCHAR(30), supplierbilldate DATETIME, isauth NVARCHAR(1), msmeno NVARCHAR(50), Gstr2bdate DATETIME, partybranchname NVARCHAR(100), fcnramount DECIMAL(18,2), notes NVARCHAR(255), controlaccode NVARCHAR(6), partygroupname NVARCHAR(100)), dash.DashboardPurchase(Id INT IDENTITY(1,1) PRIMARY KEY, UnitCode NVARCHAR(2), UnitName NVARCHAR(20), doctype NVARCHAR(5), docno NVARCHAR(6), docdate DATETIME, supplierbillno NVARCHAR(20), supplierbilldate DATETIME, itemid NVARCHAR(6), itemshortname NVARCHAR(100), itemname NVARCHAR(150), itemgroupname NVARCHAR(50), qty NUMERIC(13,3), gststatename NVARCHAR(30), rate NUMERIC(12,4), amount NUMERIC(15,2), partyshortname NVARCHAR(50), partyname NVARCHAR(75), partygroupname NVARCHAR(50), ceilingprice NUMERIC(12,4)), dash.DashboardOrder(Id INT IDENTITY(1,1) PRIMARY KEY, Ind NVARCHAR(1), OrderNo NVARCHAR(6), OrderSubNo NVARCHAR(6), OrderTypeName NVARCHAR(50), PoNo NVARCHAR(30), orders1id INT, OrderDate DATETIME, EffectiveDate DATETIME, RequiredByDate DATETIME, PlannedDate DATETIME, PoDate DATETIME, OrderStatusName NVARCHAR(50), PartyShortName NVARCHAR(50), PartyName NVARCHAR(100), PartyGroupName NVARCHAR(50), PartyCategoryName NVARCHAR(50), PartyRegionName NVARCHAR(50), DispatchLocation NVARCHAR(50), ItemId NVARCHAR(6), ItemShortName NVARCHAR(100), ItemName NVARCHAR(150), ItemGroupName NVARCHAR(50), ItemGroupType NVARCHAR(50), ItemSegmentName NVARCHAR(50), BrandName NVARCHAR(50), hsncode NVARCHAR(8), OrderQty NUMERIC(13,3), OrderRate NUMERIC(12,4), OrderAmount NUMERIC(15,2), UnitName NVARCHAR(50), GstRateName NVARCHAR(30), SalesmenName NVARCHAR(75), leadtime SMALLINT, aging INT, partyitemcode NVARCHAR(100), nonstditemname NVARCHAR(500), Currency NVARCHAR(3), PendingQty NUMERIC(13,3)), dash.DashboardIndent(Id INT IDENTITY(1,1) PRIMARY KEY, IndentNo NVARCHAR(6), IndentSubNo NVARCHAR(6), IndentTypeName NVARCHAR(30), IndentDate DATETIME, indent1id INT, UnitCode NVARCHAR(2), UnitName NVARCHAR(20), PartyGroupName NVARCHAR(50), PartyShortName NVARCHAR(50), PartyName NVARCHAR(100), BranchName NVARCHAR(50), ItemGroupName NVARCHAR(50), ItemId NVARCHAR(6), StageName NVARCHAR(25), ItemShortName NVARCHAR(100), ItemName NVARCHAR(150), BrandName NVARCHAR(50), Qty NUMERIC(13,3), Currency NVARCHAR(20), LoadFactor NUMERIC(10,4), GstRateName NVARCHAR(30), RequiredByDate DATETIME, ExpectedOnDate DATETIME, IndentStatusName NVARCHAR(50), PaymentTermName NVARCHAR(50), IsAuthName NVARCHAR(3), UomName NVARCHAR(5), Ind NVARCHAR(1), leadtime SMALLINT, aging INT, requestno NVARCHAR(20), expectedon DATETIME, PendingQty NUMERIC(13,3), standarduomname NVARCHAR(2), alternateuom NVARCHAR(2), alternateqty NUMERIC(13,3), rate NUMERIC(12,4), amount NUMERIC(15,2)), dash.DashboardJobWork(Id INT IDENTITY(1,1) PRIMARY KEY, jobno NVARCHAR(6), ChallanTypeName NVARCHAR(50), unitnamecode NVARCHAR(50), AnnexureNo NVARCHAR(20), AnnexureDate DATETIME, InOutTypeName NVARCHAR(20), partyshortname NVARCHAR(50), partyname NVARCHAR(100), itemgroupname NVARCHAR(50), itemid NVARCHAR(6), itemshortname NVARCHAR(50), ItemName NVARCHAR(150), brandname NVARCHAR(50), itemstatename NVARCHAR(10), ActualWeight NUMERIC(14,3), ActualQty NUMERIC(14,3), Amount NUMERIC(14,2), aging INT, RemarkName NVARCHAR(100), EWayBillNo NVARCHAR(20), JobItemNotes NVARCHAR(500), JobItemSpecialNotes NVARCHAR(500), JobNotes NVARCHAR(500), Packets INT, ind NVARCHAR(1), partygroupname NVARCHAR(50), refno NVARCHAR(25), refdate DATETIME), dash.DashboardLedger(Id INT IDENTITY(1,1) PRIMARY KEY, doctype NVARCHAR(1), docno NVARCHAR(6), docdate DATETIME, unitcode NVARCHAR(2), refdoc NVARCHAR(2), refno NVARCHAR(20), refdate DATETIME, notes NVARCHAR(1000), cheqdetails NVARCHAR(500), debitamount NUMERIC(15,2), creditamount NUMERIC(15,2), mainsubac NVARCHAR(12), mainsubacname NVARCHAR(200)), dash.DashboardSalary(Id INT IDENTITY(1,1) PRIMARY KEY, EmployeeCode NVARCHAR(6), EmployeeName NVARCHAR(120), EmployeeCategoryCode NVARCHAR(3), EmployeeCategoryName NVARCHAR(50), EmpTypeCode NVARCHAR(3), EmpTypeName NVARCHAR(50), PayDate DATETIME, PayHeadCode NVARCHAR(3), PayHeadName NVARCHAR(50), Amount NUMERIC(10,2), PayType NVARCHAR(1)), dash.DashboardInventoryAge(Id INT IDENTITY(1,1) PRIMARY KEY, ItemId NVARCHAR(6), StageName NVARCHAR(25), ItemShortName NVARCHAR(100), ItemName NVARCHAR(150), ItemGroupName NVARCHAR(50), ItemSegmentName NVARCHAR(50), UomName NVARCHAR(5), QtyUpto15 NUMERIC(14,3), QtyUpto30 NUMERIC(14,3), QtyUpto60 NUMERIC(14,3), QtyUpto90 NUMERIC(14,3), QtyUpto180 NUMERIC(14,3), QtyUpto365 NUMERIC(14,3), QtyAbove365 NUMERIC(14,3), TotalQty NUMERIC(14,3), Rate NUMERIC(10,4), ValueUpto15 NUMERIC(14,2), ValueUpto30 NUMERIC(14,2), ValueUpto60 NUMERIC(14,2), ValueUpto90 NUMERIC(14,2), ValueUpto180 NUMERIC(14,2), ValueUpto365 NUMERIC(14,2), ValueAbove365 NUMERIC(14,2), TotalValue NUMERIC(14,2), ItemState NVARCHAR(1), yearLabel NVARCHAR(7), unitcode NVARCHAR(2), UnitName NVARCHAR(100), itemcategory NVARCHAR(2)), dash.DashboardInventoryLedger(Id INT IDENTITY(1,1) PRIMARY KEY, DocType NVARCHAR(10), ItemClassName NVARCHAR(50), ItemGroupName NVARCHAR(50), ItemCategoryId NVARCHAR(3), ItemId NVARCHAR(6), ItemShortName NVARCHAR(100), ItemName NVARCHAR(150), BrandName NVARCHAR(50), StageName NVARCHAR(25), DocNo NVARCHAR(6), DocDate DATETIME, UomName NVARCHAR(5), OpeningQty NUMERIC(14,3), ReceiptQty NUMERIC(14,3), IssuedQty NUMERIC(14,3), BalanceQty NUMERIC(14,3), PartyShortName NVARCHAR(50), PartyName NVARCHAR(100), ItemRackInfo NVARCHAR(100), StoreName NVARCHAR(200), Description NVARCHAR(1000), ItemState NVARCHAR(1),unitcode NVARCHAR(2), UnitName NVARCHAR(100), yearLabel NVARCHAR(7))"

var inventoryLedgerSystemPrompt = @"Table 7: DashboardInventoryLedger
Use for queries about:
- Inventory ledger and stock movement transactions (transaction-level details)
- Item-wise stock balances, receipts, and issues over time
- Inventory valuation and stock tracking by item, stage, brand, and store
- Store-wise and stage-wise inventory analysis
- Good vs rejected item state comparisons
- Use bydefault ItemState = 'G' unless user explicitly mentions rejected items

Key columns include:
ItemClassName, ItemGroupName, ItemCategoryId, ItemId, ItemShortName,IssuedQty,ReceiptQty, PartyShortName, PartyName, StoreName, yearLabel

Filtering Rules:
- ItemState = 'G' for Good items
- ItemState = 'R' for Rejected items
- Use ItemState = 'G' for all total calculations unless user explicitly mentions rejected items.
- Use only latest year for all calculations unless user explicitly mentions any other year.
- Never show Opening or Balance qty columns to users
            

Error Handling and Edge Cases
When to Return 'INVALID':
When users ask for Total Opening/Balance Qty across all items or periods";

var inventoryAgingSystemPrompt = @"Table 8: DashboardInventoryAge  
Use for queries about:  
- Inventory aging analysis and stock age buckets  
- Slow-moving or dead stock identification  
- Inventory value distribution by aging periods  
- Item-wise aging reports and trends  
- Stock turnover and holding analysis  
- Summary aging reports aggregated by item group

Key columns include:  
ItemName, ItemGroupName, ItemSegmentName, TotalValue, ItemCategory, UnitName, QtyUpto15, QtyUpto30, QtyUpto60, QtyUpto90, QtyUpto180, QtyUpto365, QtyAbove365, ValueUpto15, ValueUpto30, ValueUpto60, ValueUpto90, ValueUpto180, ValueUpto365, ValueAbove365, TotalQty

Filtering Rules:  
- Filter by itemCategory = 'RM' for Raw Materials or itemCategory = 'FG' for Finished Goods.  
- Aging buckets represent days: QtyUpto15 (0-15 days), QtyUpto30 (16-30 days), etc.  
- For summary queries by item group, use LTRIM(RTRIM(ItemGroupName)) for exact matching  
- For summary queries, aggregate using SUM() on all Qty and Value columns  
- If no year is specified, use the latest yearlabel available in the data.

Additional Rule:  
- If the user does **not specify** whether they want aging based on value or quantity, treat value-based aging as the default.  
- For summary reports, include both quantity and value aggregations in the result.";

6. Filtering rules on name columns
- Always trim leading/trailing spaces when filtering on name columns (itemname, partyname, unitname, etc.)
- Always use LIKE Clause for naming columns to allow partial matches

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


namespace ErpCrystal_MFG.Api.TaskScheduler
{
    public class AIInsightsScheduler
    (
        IEnumerable<string> dbnamelist,
        ISalesAnalysisReportRepository isalesanalysisreportrepository,
        IGrnAnalysisReportRepository igrnanalysisreportrepository,
        IArApReportRepository iarapreportrepository,
        IUtilityMethodsRepository iutilitymethodsrepository,
        IAIInsightsRepository iaiinsightsrepository,
        IEmailRepository iemailrepository,
        IPurchaseAnalysisReportRepository ipurchaseanalysisreportrepository,
        ISalesOrderAnalysisReportRepository isalesorderanalysisreportrepository,
        IIndentAnalysisReportRepository iindentanalysisreportrepository,
        IInventoryAgingReportRepository iinventoryagingreportrepository,
        IInventoryLedgerReportRepository iinventoryledgerreportrepository

    ) : IInvocable
    {
        private readonly ISalesAnalysisReportRepository _ISalesAnalysisReportRepository = isalesanalysisreportrepository;
        private readonly IGrnAnalysisReportRepository _IGrnAnalysisReportRepository = igrnanalysisreportrepository;
        private readonly IArApReportRepository _IArApReportRepository = iarapreportrepository;
        private readonly IUtilityMethodsRepository _IUtilityMethodsRepository = iutilitymethodsrepository;
        private readonly IAIInsightsRepository _IAIInsightsRepository = iaiinsightsrepository;
        private readonly IEmailRepository _IEmailRepository = iemailrepository;
        private readonly IPurchaseAnalysisReportRepository _IPurchaseAnalysisReportRepository = ipurchaseanalysisreportrepository;
        private readonly ISalesOrderAnalysisReportRepository _ISalesOrderAnalysisReportRepository = isalesorderanalysisreportrepository;
        private readonly IIndentAnalysisReportRepository _IIndentAnalysisReportRepository = iindentanalysisreportrepository;
        private readonly IInventoryAgingReportRepository _IInventoryAgingReportRepository = iinventoryagingreportrepository;
        private readonly IInventoryLedgerReportRepository _IInventoryLedgerReportRepository = iinventoryledgerreportrepository;


        public async Task Invoke()
        {
            foreach (var dbname in dbnamelist)
            {
                try
                {
                    var fyStartDate = await _IAIInsightsRepository.GetPreviousFyStartDate(dbname,"3");

                    // Dashboard Sales
                    SalesAnalysisReport _SalesAnalysisReport = new()
                    {
                        FromDate = fyStartDate,
                        ToDate = DateTime.Now.AddDays(-1).Date,
                        UnitCode = "00",
                        ReportFilter = "0"
                    };

                    await _ISalesAnalysisReportRepository.SalesByProductReport(_SalesAnalysisReport, dbname, "C");


                    // Dashboard GRN
                    GrnAnalysisReport _GrnAnalysisReport = new()
                    {
                        FromDate = fyStartDate,
                        ConsiderBillDateUpto = DateTime.Now.AddDays(-1).Date,
                        ToDate = DateTime.Now.AddDays(-1).Date,
                        UnitCode = "00",
                        ReportFilter = "0",
                        ReportType = "B"
                    };
                    await _IGrnAnalysisReportRepository.GrnReport(_GrnAnalysisReport, dbname, "C");

                    var arAp = new string[] { "R", "P" };
                    _IArApReportRepository.TruncateDashboardArAp(dbname);
                    // Dashboard ARAP
                    foreach (var arApType in arAp)
                    {
                        var controlAcList = await _IArApReportRepository.ControlAcCombo(dbname, arApType);
                        foreach (var controlAc in controlAcList)
                        {

                            var _ArApReport = new ArApReport
                            {
                                AgeDocType = "R",
                                ArApType = arApType,
                                ControlAcCode = controlAc.ControlAcCode,
                                FromDate = Convert.ToDateTime("01-Jan-01"),
                                ToDate = DateTime.Now.Date,
                                ReportType = "A", // Summary report
                                ShowOnlyDue = "N",
                                ScanDocType = "R",
                                ShowOnlyMatched = "N",
                                ReportFilter = "0"

                            };
                            var yearLabel = await _IUtilityMethodsRepository.GetActiveYearlabel();
                            await _IArApReportRepository.ArApAnalysis(_ArApReport, dbname, yearLabel, "C");
                        }
                    }
                    // Dashboard Puchase By Product
                    PurchaseAnalysisReport _PurchaseAnalysisReport = new()
                    {
                        FromDate = fyStartDate,
                        ToDate = DateTime.Now.AddDays(-1).Date,
                        UnitCode = "00",
                        ReportFilter = "0"
                    };

                    await _IPurchaseAnalysisReportRepository.PurchaseByProductReport(_PurchaseAnalysisReport, dbname, "C");

                    // Dashboard Order Register
                    SalesOrderAnalysisReport _SalesOrderAnalysisReport = new()
                    {
                        FromDate = fyStartDate,
                        ToDate = DateTime.Now.AddDays(-1).Date,
                        UnitCode = "00",
                        ReportFilter = "0"
                    };

                    await _ISalesOrderAnalysisReportRepository.OrderRegisterReport(_SalesOrderAnalysisReport, dbname, "C");

                    // Indent Register Report
                    IndentAnalysisReport _IndentAnalysisReport = new()
                    {
                        FromDate = fyStartDate,
                        ToDate = DateTime.Now.AddDays(-1).Date,
                        UnitCode = "00",
                        ReportFilter = "0"
                    };

                    await _IIndentAnalysisReportRepository.IndentRegisterReport(_IndentAnalysisReport, dbname, "1", "C");

                                        // Inventory Ledger Report
                    _IInventoryLedgerReportRepository.TruncateDashboardInventoryLedger(dbname);
                    var activeUnits = await _IAIInsightsRepository.GetActiveUnitCodes(dbname);
                    string[] itemStates = ["G", "R"]; // Good and Rejected
                    var yearLabelList = await _IAIInsightsRepository.GetYearLabelList();

                    foreach (var unit in activeUnits)
                    {
                        foreach (var currentYearLabel in yearLabelList)
                        {
                            var fyDates = await _IUtilityMethodsRepository.GetFYDates(currentYearLabel);
                            var fystart = fyDates.fystart;
                            var fyend = fyDates.fyend;

                            var toDate = fyDates.fyend?.Date > DateTime.Now.Date ?
                            DateTime.Now.AddDays(-1).Date : fyDates.fyend?.Date;

                            foreach (var itemState in itemStates)
                            {
                                InventoryLedgerReport _InventoryLedgerReport = new()
                                {
                                    FromDate = fystart,
                                    ToDate = toDate,
                                    UnitCode = unit.UnitCode,
                                    UnitName = unit.UnitName,
                                    ReportFilter = "0",
                                    ReportType = "A",
                                    ItemState = itemState,
                                    StageCode = "AL",
                                    StoreCode = "99",
                                    SaveToTable = "N",
                                    ShowNegativeItem = "N",
                                    YearLabel = currentYearLabel
                                };

                                await _IInventoryLedgerReportRepository.InventoryLedgerReport(_InventoryLedgerReport, dbname, fyStartDate, "C");
                            }
                        }
                    }

                    _IInventoryAgingReportRepository.TruncateDashboardInventoryAge(dbname);

                    // Inventory Aging Report
                    string[] itemCategory = ["RM", "FG"];
                    foreach (var unit in activeUnits)
                    {
                        foreach (var currentYearLabel in yearLabelList)
                        {
                            var monthEndDate = await _IAIInsightsRepository.GetLatestMonthEndDate(currentYearLabel);
                            foreach (var inventoryCategories in itemCategory)
                            {
                                InventoryAgingReport _InventoryAgingReport = new()
                                {
                                    ReportType = "B",  // Inventory Aging Value
                                    InventoryType = "S",
                                    ItemCategory = inventoryCategories,
                                    UnitCode = unit.UnitCode,
                                    UnitName = unit.UnitName,
                                    MonthEndDate = monthEndDate,
                                    YearLabel = currentYearLabel,
                                };
                                await _IInventoryAgingReportRepository.InventoryAgingReport(_InventoryAgingReport, dbname, currentYearLabel, "C");
                            }
                        }
                    }

                }
                catch (Exception ex)
                {
                    var emailController = new EmailController(_IEmailRepository, _IUtilityMethodsRepository);
                    var _ReceiverEmail = new ReceiverEmail
                    {
                        email1 = "udit@erpcrystal.in", // System Admin Email
                        emailsubject = $"AI Insights Scheduler Error for {dbname}",
                        emailmessage = $"An error occurred in the AI Insights 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, "S");
                }
            }
        }
    }
}

var truncateChartTable = "TRUNCATE TABLE dash.DashboardInventoryLedger";//need to take outside like arap

var dashboardInsert = @"INSERT INTO dash.DashboardInventoryLedger ( DocType, ItemClassName, ItemGroupName,
ItemCategoryId, ItemId, ItemShortName, ItemName, BrandName, StageName, DocNo, DocDate, UomName, OpeningQty,
ReceiptQty, IssuedQty, BalanceQty, PartyShortName, PartyName, ItemRackInfo, StoreName, Description, ItemState, UnitCode, UnitName, yearlabel )
SELECT DocType, ItemClassName, ItemGroupName, ItemCategoryId, ItemId, ItemShortName, ItemName,
BrandName, StageName, DocNo, DocDate, UomName, OpeningQty, ReceiptQty, IssuedQty, BalanceQty,
PartyShortName, PartyName, ItemRackInfo, StoreName, Description, @itemState AS ItemState, @unitCode AS UnitCode, @unitName AS UnitName, @yearlabel
FROM #ledgerTemp WHERE Ind != 'Y'";
if (ind == "C") 
{ 
  connection.Execute(truncateChartTable); 
  var fullQuery = $@"{inventoryLedgerReportQuery.Replace("-- INTO_temp", "INTO #ledgerTemp")} {dashboardInsert}"; 
  connection.Execute(fullQuery, parameters);
}

 var truncateChartTable = "TRUNCATE TABLE dash.DashboardInventoryAge";//need to take outside like arap

var dashboardInsert = @"INSERT INTO dash.DashboardInventoryAge ( ItemId, StageName, ItemShortName,
ItemName, ItemGroupName, ItemSegmentName, UomName, QtyUpto15, QtyUpto30, QtyUpto60, QtyUpto90,
QtyUpto180, QtyUpto365, QtyAbove365, TotalQty, Rate, ValueUpto15, ValueUpto30, ValueUpto60,
ValueUpto90, ValueUpto180, ValueUpto365, ValueAbove365, TotalValue, ItemState,UnitCode, Unitname, yearlabel, itemcategory)
SELECT ItemId, StageName, ItemShortName, ItemName, ItemGroupName, ItemSegmentName, UomName,
QtyUpto15, QtyUpto30, QtyUpto60, QtyUpto90, QtyUpto180, QtyUpto365, QtyAbove365, TotalQty,
Rate, ValueUpto15, ValueUpto30, ValueUpto60, ValueUpto90, ValueUpto180, ValueUpto365,
ValueAbove365, TotalValue, @itemstate AS ItemState,@unitCode, @unitname, @yearlabel, @itemcategory
FROM #agingTemp";

if (ind == "C") 
{ 
  connection.Execute(truncateChartTable);
  var fullQuery = $@"{data1Query.Replace("-- INTO_temp", "INTO #agingTemp")} {dashboardInsert}"; 
  connection.Execute(fullQuery, new { itemstate = _InventoryAgingReport.ItemState, yearLabel, _InventoryAgingReport.UnitCode, MonthEndDate = _InventoryAgingReport.MonthEndDate?.Date, agingdate = computeAgeOn }); 
}