Attribute VB_Name = "modReconcile"
Option Compare Database
Option Explicit

Public Function RunReconcile(Optional batchID As Long = -1) As Long
    Dim db As DAO.Database
    Dim rsTrans As DAO.Recordset
    Dim rsBank As DAO.Recordset
    Dim matchedCount As Long
    Dim effectiveBatchID As Long
    Dim seenRefNoCount As Long

    effectiveBatchID = IIf(batchID = -1, modUtil.gCurrentBatchID, batchID)
    Set db = CurrentDb()

    db.Execute "DELETE FROM tblReconcileResult WHERE TransID IN " & _
               "(SELECT TransID FROM tblTransactions WHERE ImportBatchID = " & effectiveBatchID & ")"

    Set rsTrans = db.OpenRecordset("SELECT * FROM tblTransactions WHERE ImportBatchID = " & effectiveBatchID)
    matchedCount = 0

    Do While Not rsTrans.EOF
        seenRefNoCount = DCount("*", "tblTransactions", _
            "RefNo = '" & Replace(rsTrans!RefNo, "'", "''") & "' AND ImportBatchID = " & effectiveBatchID)

        If seenRefNoCount > 1 Then
            InsertResult db, rsTrans!TransID, Null, "Duplicate", 0
        Else
            ' Exact match by RefNo
            Set rsBank = db.OpenRecordset( _
                "SELECT * FROM tblBankStatement WHERE BankRefNo = '" & Replace(rsTrans!RefNo, "'", "''") & "'")
            If Not rsBank.EOF Then
                InsertResult db, rsTrans!TransID, rsBank!StmtID, "Matched", rsTrans!Amount - rsBank!Amount
                matchedCount = matchedCount + 1
            Else
                rsBank.Close
                ' Fuzzy fallback: same Amount, TxnDate within +-1 day
                Set rsBank = db.OpenRecordset( _
                    "SELECT * FROM tblBankStatement WHERE Amount = " & modUtil.FormatNumberForSql(rsTrans!Amount) & _
                    " AND TxnDate BETWEEN " & modUtil.FormatDateForSql(rsTrans!TxnDate - 1) & _
                    " AND " & modUtil.FormatDateForSql(rsTrans!TxnDate + 1))
                If Not rsBank.EOF Then
                    InsertResult db, rsTrans!TransID, rsBank!StmtID, "Matched (Fuzzy)", rsTrans!Amount - rsBank!Amount
                    matchedCount = matchedCount + 1
                Else
                    InsertResult db, rsTrans!TransID, Null, "Unmatched", rsTrans!Amount
                End If
            End If
            rsBank.Close
        End If
        rsTrans.MoveNext
    Loop

    rsTrans.Close
    Set rsTrans = Nothing
    Set db = Nothing
    RunReconcile = matchedCount
End Function

Private Sub InsertResult(db As DAO.Database, transID As Variant, stmtID As Variant, status As String, variance As Currency)
    Dim sql As String
    sql = "INSERT INTO tblReconcileResult (TransID, StmtID, MatchStatus, Variance, ReconciledBy, ReconciledDate) VALUES (" & _
          IIf(IsNull(transID), "NULL", transID) & ", " & _
          IIf(IsNull(stmtID), "NULL", stmtID) & ", '" & status & "', " & modUtil.FormatNumberForSql(variance) & ", '" & _
          Environ("USERNAME") & "', Now())"
    db.Execute sql
End Sub