AI Insights [Job Work Register, Ledger, Salary]

CREATE TABLE dash.DashboardJobWork
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    jobno NVARCHAR(6) COLLATE DATABASE_DEFAULT,
    ChallanTypeName NVARCHAR(50) COLLATE DATABASE_DEFAULT,
    unitname NVARCHAR(20) COLLATE DATABASE_DEFAULT,
    unitcode NVARCHAR(2) COLLATE DATABASE_DEFAULT,
    AnnexureNo NVARCHAR(20) COLLATE DATABASE_DEFAULT,
    AnnexureDate DATETIME,
    InOutTypeName NVARCHAR(20) COLLATE DATABASE_DEFAULT,
    partyshortname NVARCHAR(50) COLLATE DATABASE_DEFAULT,
    partyname NVARCHAR(100) COLLATE DATABASE_DEFAULT,
    itemgroupname NVARCHAR(50) 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,
    itemstatename NVARCHAR(10) COLLATE DATABASE_DEFAULT,
    ActualWeight NUMERIC(14,3),
    ActualQty NUMERIC(14,3),
    Amount NUMERIC(14,2),
    aging INT,
    RemarkName NVARCHAR(100) COLLATE DATABASE_DEFAULT,
    EWayBillNo NVARCHAR(20) COLLATE DATABASE_DEFAULT,
    JobItemNotes NVARCHAR(500) COLLATE DATABASE_DEFAULT,
    JobItemSpecialNotes NVARCHAR(500) COLLATE DATABASE_DEFAULT,
    JobNotes NVARCHAR(500) COLLATE DATABASE_DEFAULT,
    Packets INT,
    ind NVARCHAR(1) COLLATE DATABASE_DEFAULT,
    partygroupname NVARCHAR(50) COLLATE DATABASE_DEFAULT,
    refno NVARCHAR(25) COLLATE DATABASE_DEFAULT,
    refdate DATETIME
);

CREATE TABLE dash.DashboardLedger
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    doctype NVARCHAR(1) COLLATE DATABASE_DEFAULT,
    docno NVARCHAR(6) COLLATE DATABASE_DEFAULT,
    docdate DATETIME,
    unitcode NVARCHAR(2) COLLATE DATABASE_DEFAULT,
    refdoc NVARCHAR(2) COLLATE DATABASE_DEFAULT,
    refno NVARCHAR(20) COLLATE DATABASE_DEFAULT,
    refdate DATETIME,
    notes NVARCHAR(1000) COLLATE DATABASE_DEFAULT,
    cheqdetails NVARCHAR(500) COLLATE DATABASE_DEFAULT,
    debitamount NUMERIC(15,2),
    creditamount NUMERIC(15,2),
    mainsubac NVARCHAR(13) COLLATE DATABASE_DEFAULT,
    mainsubacname NVARCHAR(200) COLLATE DATABASE_DEFAULT
);

CREATE TABLE dash.DashboardSalary
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeCode NVARCHAR(6) COLLATE DATABASE_DEFAULT,
    EmployeeName NVARCHAR(120) COLLATE DATABASE_DEFAULT,
    EmployeeCategoryCode NVARCHAR(3) COLLATE DATABASE_DEFAULT,
    EmployeeCategoryName NVARCHAR(50) COLLATE DATABASE_DEFAULT,
    EmpTypeCode NVARCHAR(3) COLLATE DATABASE_DEFAULT,
    EmpTypeName NVARCHAR(50) COLLATE DATABASE_DEFAULT,
    PayDate DATETIME,
    PayHeadCode NVARCHAR(3) COLLATE DATABASE_DEFAULT,
    PayHeadName NVARCHAR(50) COLLATE DATABASE_DEFAULT,
    Amount NUMERIC(10,2),
    PayType NVARCHAR(1) COLLATE DATABASE_DEFAULT
);

var jobWorkRegisterSystemPrompt = @"Table 9: DashboardJobWork
Use for queries about:
- Job work register and processing activities
- Inward and outward job work transactions
- Job work party-wise and item-wise analysis
- Job work aging and pending analysis
- Job work challan tracking and status
- Job work quantity and value summaries
- Job work performance and trends

Key columns include:
jobno, AnnexureDate, InOutTypeName, partyname, itemname, ActualWeight, ActualQty, Amount, aging

Important Notes:
- This table contains individual job work transaction records
- ActualWeight, ActualQty, Amount can be positive (inward) or negative (outward)
- aging represents days since annexure date for outward transactions
- AnnexureDate is the key date field for filtering

Filtering Rules:
- InOutTypeName = 'OUT' for sent out job work
- InOutTypeName = 'IN GRN' for received job work with GRN
- InOutTypeName = 'IN No GRN' for received job work without GRN
- itemstatename = 'Good' for good items
- itemstatename = 'Rejected' for rejected items
- Use AnnexureDate BETWEEN for date range filtering
- Filter by partyname, itemname, unitname for specific analysis

Aggregation Rules (create totals on the fly when requested):
- For job-wise totals: GROUP BY jobno with SUM(ActualWeight), SUM(ActualQty), SUM(Amount), MAX(aging)
- For party-wise summaries: GROUP BY partyname with SUM(ActualWeight), SUM(ActualQty), SUM(Amount)
- For item-wise summaries: GROUP BY itemname with SUM(ActualWeight), SUM(ActualQty), SUM(Amount)
- For monthly trends: GROUP BY YEAR(AnnexureDate), MONTH(AnnexureDate) with SUM(ActualWeight), SUM(ActualQty), SUM(Amount)
- For overall totals: no GROUP BY, just SUM(ActualWeight), SUM(ActualQty), SUM(Amount)
- Always format monetary results as CAST(SUM(Amount) AS DECIMAL(18,2))
- For aging analysis: AVG(aging) or categorize by aging ranges";


var ledgerSystemPrompt = @"Table 10: DashboardLedger
Use for queries about:
- General ledger transactions and entries
- Account-wise debit and credit analysis
- Financial transaction tracking
- Journal voucher and ledger analysis
- Account balance and movement analysis
- Trial balance and account statements
- Cash flow analysis
- Financial period comparisons

Key columns include:
doctype, docno, docdate, unitcode, refdoc, refno, refdate, notes, cheqdetails, debitamount, creditamount, mainsubac, mainsubacname

Important Notes:
- Each row represents a single transaction entry for an account
- debitamount and creditamount are mutually exclusive (one will be 0, the other will have the amount)
- mainsubac is the account code in format 'mainac-subac'
- mainsubacname includes the account code and account name
- Transactions are ordered by account, then by date, then by document type and number

Document Type (doctype) Values:
- 'A' = Opening Balance entries
- 'I' = Sales Invoice transactions
- 'B' = Purchase Bill transactions  
- 'D' = Debit/Credit Note transactions
- 'V' = Payment/Receipt Voucher transactions
- 'J' = Journal Voucher transactions

Reference Document (refdoc) Values:
- 'SI' = Sales Invoice
- 'PB' = Purchase Bill
- 'JV' = Journal Voucher
- 'DN' = Debit Note
- 'CN' = Credit Note
- 'RV' = Receipt Voucher
- 'PV' = Payment Voucher

Filtering Rules:
- Use doctype IN ('I','B','D','V','J') for transaction entries (exclude 'A' for opening balances)
- Use doctype = 'A' for opening balance entries only
- Filter by mainsubac or mainsubacname for specific accounts
- Use docdate BETWEEN for date range filtering
- Use unitcode for business unit filtering

Aggregation Rules:
- For account balance: SUM(debitamount) - SUM(creditamount) GROUP BY mainsubac
- For period totals: SUM(debitamount), SUM(creditamount) with date filtering
- For transaction counts: COUNT(*) with appropriate filters
- For account statements: ORDER BY mainsubac, docdate, doctype, docno
- Always format monetary results as CAST(SUM(amount) AS DECIMAL(18,2))

Common Query Patterns:
- Account-wise balance: SELECT mainsubac, mainsubacname, SUM(debitamount) - SUM(creditamount) AS balance FROM dash.DashboardLedger WHERE doctype != 'A' GROUP BY mainsubac, mainsubacname
- Transaction listing: SELECT * FROM dash.DashboardLedger WHERE mainsubac = 'account_code' AND docdate BETWEEN 'start' AND 'end' ORDER BY docdate, doctype, docno
- Period summary: SELECT SUM(debitamount) AS total_debit, SUM(creditamount) AS total_credit FROM dash.DashboardLedger WHERE docdate BETWEEN 'start' AND 'end' AND doctype != 'A'";


var salarySystemPrompt = @"Table 11: DashboardSalary
Use for queries about:
- Employee salary and payroll analysis
- Pay head-wise salary distribution
- Employee category and type-wise analysis
- Salary trends and comparisons
- Payroll cost analysis
- Monthly/period-wise salary summaries
- Employee-wise total earnings and deductions
- Gross salary and deduction breakdowns

Key columns include:
EmployeeCode, EmployeeName, EmployeeCategoryCode, EmployeeCategoryName, EmpTypeCode, EmpTypeName, PayDate, PayHeadCode, PayHeadName, Amount,PayType

Important Notes:
- PayDate represents the salary period monthly and PayDate is Always month end date
- Each row represents one pay component for one employee in one pay period

Filtering and Aggregation Rules:
- Group by EmployeeCategoryName for category-wise analysis
- Group by EmpTypeName for employee type analysis  
- Filter by PayHeadName for specific pay component analysis
- Use PayDate BETWEEN for date range filtering (financial year or calendar periods)
- For net salary per employee per period: SUM(Amount) GROUP BY EmployeeCode, PayDate (gross minus deductions)
- For monthly trends: GROUP BY FORMAT(PayDate, 'yyyy-MM') or YEAR(PayDate), MONTH(PayDate)
- For payhead analysis: GROUP BY PayHeadName with SUM(Amount)
- For addition payheads only: WHERE PayType = 'A'
- For deduction payheads only: WHERE PayType = 'D'
- Always format monetary results as CAST(SUM(...) AS DECIMAL(18,2))";



{jobWorkRegisterSystemPrompt}
{salarySystemPrompt}
{ledgerSystemPrompt}

// -------salary table report
var truncateSalaryTable = "TRUNCATE TABLE dash.DashboardSalary";

using var connection = _DapperContext.SetClientConnection(dbname);
connection.Execute(truncateSalaryTable);

var salaryQuery = @"WITH A (EmployeeCode, EmployeeName, EmployeeCategoryCode, EmployeeCategoryName,
    EmpTypeCode, EmpTypeName, PayDate, PayHeadCode, PayHeadName, Amount) AS
(
    SELECT
        E.employeecode AS EmployeeCode,
        CONCAT_WS(' ', E.firstname, E.lastname) AS EmployeeName,
        E.categorycode AS EmployeeCategoryCode,
        EC.categoryname AS EmployeeCategoryName,
        E.emptypecode AS EmpTypeCode,
        ET.emptypename AS EmpTypeName,
        S.paydate AS PayDate,
        S.payheadcode AS PayHeadCode,
        PH.headname AS PayHeadName,
        S.amount AS Amount
    FROM Salary S
    LEFT JOIN Employeemst E ON S.employeecode = E.employeecode
    LEFT JOIN Empcategorymst EC ON E.categorycode = EC.categorycode
    LEFT JOIN Emptypemst ET ON E.emptypecode = ET.emptypecode
    LEFT JOIN Payheadmst PH ON S.payheadcode = PH.headcode
    WHERE CONVERT(DATE, S.paydate) BETWEEN @fromdate AND @todate
)
INSERT INTO dash.DashboardSalary (EmployeeCode, EmployeeName, EmployeeCategoryCode, EmployeeCategoryName,
    EmpTypeCode, EmpTypeName, PayDate, PayHeadCode, PayHeadName, Amount)
SELECT EmployeeCode, EmployeeName, EmployeeCategoryCode, EmployeeCategoryName,
       EmpTypeCode, EmpTypeName, PayDate, PayHeadCode, PayHeadName, Amount FROM A";

connection.Execute(salaryQuery, new { fromdate = fyStartDate, todate = DateTime.Now.AddDays(-1).Date });

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,
        IJobWorkAnalysisReportRepository ijobworkanalysisreportrepository,
        IFinancialReportRepository ifinancialreportrepository,
        IPayrollRepository ipayrollrepository

    ) : 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 IJobWorkAnalysisReportRepository _IJobWorkAnalysisReportRepository = ijobworkanalysisreportrepository;
        private readonly IFinancialReportRepository _IFinancialReportRepository = ifinancialreportrepository;
        private readonly IPayrollRepository _IPayrollRepository = ipayrollrepository;


        public async Task Invoke()
        {
            foreach (var dbname in dbnamelist)
            {
                try
                {
                    var fyStartDate = await _IAIInsightsRepository.GetPreviousFyStartDate(dbname,"3");
                    var yearLabel = await _IUtilityMethodsRepository.GetActiveYearlabel();
                    var fyStart = fyStartDate; // Use the same fyStartDate for consistency

                    // 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"

                            };
                            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");

                    // -------job work register report
                    JobWorkAnalysisReport _JobWorkAnalysisReport = new()
                    {
                        FromDate = fyStartDate,
                        ToDate = DateTime.Now.AddDays(-1).Date,
                        UnitCode = "00",
                        ReportType = "AB", // A for In, B for Out
                        ReportFilter = "0",
                        OmitAnnexures = "N",
                        IsPendingJob = false
                    };

                    await _IJobWorkAnalysisReportRepository.JobInOutReport(_JobWorkAnalysisReport, dbname, "C");

                    // -------ledger report
                    string[] ledgerTypes = ["GL", "CL", "VL"];
                    _IFinancialReportRepository.TruncateDashboardLedger(dbname); // Truncate once outside the loop
                    foreach (var ledgerType in ledgerTypes)
                    {
                        FinancialReport _FinancialReport = new()
                        {
                            FromDate = fyStartDate,
                            ToDate = DateTime.Now.AddDays(-1).Date,
                            UnitCode = "00",
                            DivisionCode = "99",
                            ReportType = "A",
                            ReportLedgerType = ledgerType,
                            ReportFilter = "0",
                            GroupType = "B"
                        };

                        await _IFinancialReportRepository.FinancialReportData(_FinancialReport, dbname, yearLabel, fyStart, "C");
                    }

                    // Salary Table for AI Insights
                    await _IPayrollRepository.InsertSalaryDashboard(dbname, fyStartDate.Value.Date,DateTime.Now.AddDays(-1).Date);

                }
                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");
                }
            }
        }
    }
}

public async Task<List<JobWorkMovements>> JobInOutReport(JobWorkAnalysisReport _JobWorkAnalysisReport, string dbname, string ind)
{
    // ... existing query code ...

    // Add conditional job type logic
    var jobTypeCondition = ind == "C" ? "J.Jobtype IN ('1','2')" : "J.Jobtype = @jobtype";
    // Use this in the WHERE clause: WHERE CONVERT(DATE,J.Dated) BETWEEN @fromdate AND @todate AND {jobTypeCondition}

    var truncateJobWorkTable = "TRUNCATE TABLE dash.DashboardJobWork";

    var dashboardJobWorkInsert = @"INSERT INTO dash.DashboardJobWork (jobno, ChallanTypeName, unitnamecode, AnnexureNo, AnnexureDate, InOutTypeName, partyshortname, partyname, itemgroupname, itemid, itemshortname, itemname, brandname, itemstatename, ActualWeight, ActualQty, Amount, aging, RemarkName, EWayBillNo, JobItemNotes, JobItemSpecialNotes, JobNotes, Packets, ind, partygroupname, refno, refdate)
    SELECT jobno, ChallanTypeName, unitnamecode, AnnexureNo, AnnexureDate, InOutTypeName, partyshortname, partyname, itemgroupname, itemid, itemshortname, itemname, brandname, itemstatename, ActualWeight, ActualQty, Amount, aging, RemarkName, EWayBillNo, JobItemNotes, JobItemSpecialNotes, JobNotes, Packets, ind, partygroupname, refno, refdate FROM #jobworkTemp";

    using var connection = _DapperContext.SetClientConnection(dbname);

    if (ind == "C")
    {
        connection.Execute(truncateJobWorkTable);

        var fullQuery = $"{query.Replace("-- INTO_temp", "INTO #jobworkTemp")}
        {dashboardJobWorkInsert}";

        connection.Execute(fullQuery, new
        {
            FromDate = _JobWorkAnalysisReport.FromDate?.Date,
            ToDate = _JobWorkAnalysisReport.ToDate?.Date,
            jobType,
            _JobWorkAnalysisReport.UnitCode,
            _JobWorkAnalysisReport.PartyGroupCode,
            _JobWorkAnalysisReport.PartyId,
            _JobWorkAnalysisReport.ItemGroupCode,
            _JobWorkAnalysisReport.ItemId
        });
        return new List<JobWorkMovements>();
    }
    else
    {
        var data = await connection.QueryAsync<JobWorkMovements>(query, new
        {
            FromDate = _JobWorkAnalysisReport.FromDate?.Date,
            ToDate = _JobWorkAnalysisReport.ToDate?.Date,
            jobType,
            _JobWorkAnalysisReport.UnitCode,
            _JobWorkAnalysisReport.PartyGroupCode,
            _JobWorkAnalysisReport.PartyId,
            _JobWorkAnalysisReport.ItemGroupCode,
            _JobWorkAnalysisReport.ItemId
        });
        return data.ToList();
    }
}

void TruncateDashboardLedger(string dbname);

public void TruncateDashboardLedger(string dbname)
{
    var query = @"TRUNCATE TABLE dash.DashboardLedger";
    using var connection = _DapperContext.SetClientConnection(dbname);
    connection.Execute(query);
}

public async Task<Tuple<List<LedgerReport>, List<TrialBalanceReport>, List<TrialBalanceReport>, List<TrialBalanceReport>,
List<TrialBalanceReport>, ConfirmationOfBalance, List<ConfirmationOfBalance>>>
FinancialReportData(FinancialReport _FinancialReport, string dbname, string yearLabel, DateTime? fyStart, string ind = "")
{
    // ... existing code ...

    if (ind == "C")
    {
        var dashboardLedgerInsert = @"INSERT INTO dash.DashboardLedger (doctype, docno, docdate, unitcode, refdoc, refno, refdate, notes, cheqdetails, debitamount, creditamount, mainsubac, mainsubacname)
        SELECT doctype, docno, docdate, unitcode, refdoc, refno, refdate, notes, cheqdetails, debitamount, creditamount,
        CONCAT_WS('-', mainac, subac) AS mainsubac,
        CONCAT_WS('-', mainac, subac, ' ', COALESCE(IIF(A.achead = A1.acname, A.achead, CONCAT_WS(',', A.achead, A1.acname)), 'INVALID ACCOUNT NAME')) AS mainsubacname
        FROM #ledger L
        LEFT JOIN accounts A ON L.mainac = A.mainac
        LEFT JOIN accounts1 A1 ON L.mainac + L.subac = A1.mainac + A1.subac
        WHERE doctype NOT IN ('Y', 'Z')
        ORDER BY mainac, subac, docdate, doctype, docno, id";

        connection.Execute(dashboardLedgerInsert);
    }

    // ... return existing tuple ...
}