Attribute VB_Name = "MonlamJsonConverter_v2"
Option Compare Database

' V2: correct historical MonId 131 empty-slot coordinates and participant totals.
' Query Sex supplies Id.sex / Participant.Sex as M/F; L/D/N/P counts use sex.
' Stable Titel ID 10 maps to P (Lhopon); stable ID 11 maps to G.
' Import MonlamPdfAttachment.bas into Access as well: it embeds the JSON using
' native VBA and standard Windows streams, without PDFtk/pdfattach/Acrobat.
' Existing report, filenames, paths and historical JSON format are retained.
' Page/slot/exported SNo follow consecutive report positions, ignoring gaps in
' the query's database SNo. PDF page count must match Forms.Count at attachment.
' JSON-only attachment: the importer renders each PDF page to data.Form.w/h.
' After parsing an existing JSON file, call RepairMonlam131Forms(parsedForms).
Sub exportMonlamIDCardAsForms_v2()
On Error GoTo ExportFailed
Dim recFilterKey As String
Dim recSource As String
Dim recNo As Long
Dim A As Long
Dim b As Long
Dim recStart As Long
Dim recEnd As Long
Dim recFilter As String
Dim recStep As Integer
Dim recFileName As String
Dim recReport As String
Dim ImageMagick As Object
Dim recOutputDir As String
Dim recSno As Long
Dim recFileNamePrefix As String
Dim recFullFilename As String
Dim recIdPhotoFolder As String
Dim titelToLetter
Dim participantSex As String
'titelToLetter = Split("XVTKMMFMS")

'Set ImageMagick = CreateObject("ImageMagickObject.MagickImage.1")

recFilterKey = "SNo"
'recStep = 1000
recReport = "Monlam Form Report" ' specialy created report: photo in right size, margin 0, size as small as possible to speedup trim and conversion
recIdPhotoFolder = "\Forms"
recOutputDir = CurrentProject.Path & recIdPhotoFolder & "\"
recSource = "MonlamIDCard Query" 'Me.Form.RecordSource
recNo = DCount(recFilterKey, recSource)
If recNo = 0 Then
    MsgBox "MonlamIDCard Query contains no participants to export.", vbInformation, "Monlam export"
    Exit Sub
End If
recFileNamePrefix = "nam_0000_0131_"
recFileName = recFileNamePrefix

'create folder if not found
If Dir(recOutputDir, vbDirectory) = "" Then 'dir not found
    MkDir recOutputDir 'make dir
End If

SysCmd acSysCmdInitMeter, "Creating PDF......", 0
ExportMonlamReportPdf recReport, recOutputDir & recFileName & ".pdf"

''2018-12-22 don't convert pdf
'use imagemagic to convert pdf to image
'msgs = ImageMagick.Convert("-density", "200", recOutputDir & recFileName & ".pdf", "-scene", "1", recOutputDir & recFileName & "%04d.jpg")
'Debug.Print msgs
'run convertPdfToJpg.bat to convert pdf to image
'Shell "cmd /c d: && cd """ & recOutputDir & """ && """ & recOutputDir & "convertPdfToJpg.bat""", vbNormalFocus

Dim rs As DAO.Recordset
Set rs = CurrentDb().OpenRecordset("select * from [MonlamIDCard Query] order by SNo")
Dim Time, SNo, PNo, No, MonId_PNo, SourceNoFormNo, SourceNo, FormNo, MonId, SourceNoFormNo_SNo
Dim Forms As Dictionary
'Set Forms = New Dictionary
'Set rs = Form.RecordsetClone
If Not (rs.BOF And rs.EOF) Then
    rs.MoveFirst
    Time = Timer()
    Set Forms = New Dictionary 'set Forms as dic
    'Forms.RemoveAll
    SysCmd acSysCmdInitMeter, "MonlamIDCard", recNo
    SysCmd acSysCmdUpdateMeter, 0
    recSno = 0
    Do Until rs.EOF = True 'do until EOF
        DoEvents
        ' The report packs records ten per page; database SNo values can have gaps.
        ' SNo in exported JSON is the printed serial, not the database row number.
        recSno = recSno + 1
        SNo = recSno
        participantSex = MonlamSexCode(rs!Sex)
        If participantSex = "" Then Err.Raise 513, "MonlamJsonConverter_v2", "Sex must be M or F for participant SNo " & SNo & "."
        
        SysCmd acSysCmdUpdateMeter, SNo
        
        PNo = ((SNo - 1) \ 10) + 1
        No = ((SNo - 1) Mod 10) + 1
        MonId = 131
        MonId_PNo = Right("0000" & MonId, 4) & "_" & Right("0000" & PNo, 4)
        SourceNo = "nam_0000"
        FormNo = MonId_PNo
        SourceNoFormNo = SourceNo & "_" & FormNo
        SourceNoFormNo_SNo = SourceNoFormNo & "_" & Right("0000" & SNo, 4)
        
        'If FormNo = "0131_0100" Then
        '    Beep
        'End If
        If Forms.Exists(MonId_PNo) = False Or IsEmpty(Forms(MonId_PNo)) = True Then
            Set Forms(MonId_PNo) = New Dictionary
            'Form table fields
            Forms(MonId_PNo)("ID") = ""
            Forms(MonId_PNo)("SourceNoFormNo") = SourceNoFormNo
            Forms(MonId_PNo)("SourceNo") = SourceNo
            Forms(MonId_PNo)("FormNo") = FormNo
            Forms(MonId_PNo)("MonId") = MonId
            'Forms(MonId_PNo)("MonId_PNo") = MonId_PNo
            Forms(MonId_PNo)("PNo") = PNo
            
            Set Forms(MonId_PNo)("data") = New Dictionary
            Set Forms(MonId_PNo)("data")("Form") = JsonConverter.ParseJson("{""w"":1652,""h"":2338,""name"":""" & SourceNoFormNo & ".jpg""}")
            Set Forms(MonId_PNo)("data")("Ids") = New Dictionary
            Forms(MonId_PNo)("data")("Ids")("dummy") = "prevent conversion to array in firebase"
        End If
        
        If No = 1 Then
            Set Forms(MonId_PNo)("data")("Ids")(No) = New Dictionary
            Set Forms(MonId_PNo)("data")("Ids")(No)("Id") = JsonConverter.ParseJson("{""x"":""142.437"",""y"":""444.686"",""w"":""654.446"",""h"":""296.244"",""title"":""" & MonlamTitleLetter(rs!Titel) & """,""name"":""" & SourceNoFormNo & "_" & Right("0000" & No, 4) & ".jpg" & """}")
            Set Forms(MonId_PNo)("data")("Ids")(No)("Participant") = JsonConverter.ParseJson("{""SNo"":""" & SNo & """,""Name"":""" & rs!Name & """,""Age"":""" & rs!Age & """,""Fname"":""" & rs!Fname & """,""Titel"":" & rs!Titel & ",""RemarkNo"":" & rs!RemarkNo & "}")
            Set Forms(MonId_PNo)("data")("Ids")(No)("Photo") = JsonConverter.ParseJson("{""w"":186,""h"":243,""x"":169,""y"":461,""name"":""" & SourceNoFormNo_SNo & ".jpg""}")
        ElseIf No = 2 Then
            Set Forms(MonId_PNo)("data")("Ids")(No) = New Dictionary
            Set Forms(MonId_PNo)("data")("Ids")(No)("Id") = JsonConverter.ParseJson("{""x"":""142.437"",""y"":""742.463"",""w"":""654.446"",""h"":""296.244"",""title"":""" & MonlamTitleLetter(rs!Titel) & """,""name"":""" & SourceNoFormNo & "_" & Right("0000" & No, 4) & ".jpg" & """}")
            Set Forms(MonId_PNo)("data")("Ids")(No)("Participant") = JsonConverter.ParseJson("{""SNo"":""" & SNo & """,""Name"":""" & rs!Name & """,""Age"":""" & rs!Age & """,""Fname"":""" & rs!Fname & """,""Titel"":" & rs!Titel & ",""RemarkNo"":" & rs!RemarkNo & "}")
            Set Forms(MonId_PNo)("data")("Ids")(No)("Photo") = JsonConverter.ParseJson("{""w"":186,""h"":243,""x"":169,""y"":759,""name"":""" & SourceNoFormNo_SNo & ".jpg""}")
        ElseIf No = 3 Then
            Set Forms(MonId_PNo)("data")("Ids")(No) = New Dictionary
            Set Forms(MonId_PNo)("data")("Ids")(No)("Id") = JsonConverter.ParseJson("{""x"":""142.437"",""y"":""1039.729"",""w"":""654.446"",""h"":""296.244"",""title"":""" & MonlamTitleLetter(rs!Titel) & """,""name"":""" & SourceNoFormNo & "_" & Right("0000" & No, 4) & ".jpg" & """}")
            Set Forms(MonId_PNo)("data")("Ids")(No)("Participant") = JsonConverter.ParseJson("{""SNo"":""" & SNo & """,""Name"":""" & rs!Name & """,""Age"":""" & rs!Age & """,""Fname"":""" & rs!Fname & """,""Titel"":" & rs!Titel & ",""RemarkNo"":" & rs!RemarkNo & "}")
            Set Forms(MonId_PNo)("data")("Ids")(No)("Photo") = JsonConverter.ParseJson("{""w"":186,""h"":243,""x"":169,""y"":1059,""name"":""" & SourceNoFormNo_SNo & ".jpg""}")
        ElseIf No = 4 Then
            Set Forms(MonId_PNo)("data")("Ids")(No) = New Dictionary
            Set Forms(MonId_PNo)("data")("Ids")(No)("Id") = JsonConverter.ParseJson("{""x"":""142.437"",""y"":""1338.401"",""w"":""654.446"",""h"":""296.244"",""title"":""" & MonlamTitleLetter(rs!Titel) & """,""name"":""" & SourceNoFormNo & "_" & Right("0000" & No, 4) & ".jpg" & """}")
            Set Forms(MonId_PNo)("data")("Ids")(No)("Participant") = JsonConverter.ParseJson("{""SNo"":""" & SNo & """,""Name"":""" & rs!Name & """,""Age"":""" & rs!Age & """,""Fname"":""" & rs!Fname & """,""Titel"":" & rs!Titel & ",""RemarkNo"":" & rs!RemarkNo & "}")
            Set Forms(MonId_PNo)("data")("Ids")(No)("Photo") = JsonConverter.ParseJson("{""w"":186,""h"":243,""x"":169,""y"":1353,""name"":""" & SourceNoFormNo_SNo & ".jpg""}")
        ElseIf No = 5 Then
            Set Forms(MonId_PNo)("data")("Ids")(No) = New Dictionary
            Set Forms(MonId_PNo)("data")("Ids")(No)("Id") = JsonConverter.ParseJson("{""x"":""142.437"",""y"":""1634.517"",""w"":""654.446"",""h"":""296.244"",""title"":""" & MonlamTitleLetter(rs!Titel) & """,""name"":""" & SourceNoFormNo & "_" & Right("0000" & No, 4) & ".jpg" & """}")
            Set Forms(MonId_PNo)("data")("Ids")(No)("Participant") = JsonConverter.ParseJson("{""SNo"":""" & SNo & """,""Name"":""" & rs!Name & """,""Age"":""" & rs!Age & """,""Fname"":""" & rs!Fname & """,""Titel"":" & rs!Titel & ",""RemarkNo"":" & rs!RemarkNo & "}")
            Set Forms(MonId_PNo)("data")("Ids")(No)("Photo") = JsonConverter.ParseJson("{""w"":186,""h"":243,""x"":169,""y"":1649,""name"":""" & SourceNoFormNo_SNo & ".jpg""}")
        ElseIf No = 6 Then
            Set Forms(MonId_PNo)("data")("Ids")(No) = New Dictionary
            Set Forms(MonId_PNo)("data")("Ids")(No)("Id") = JsonConverter.ParseJson("{""x"":""796.883"",""y"":""444.686"",""w"":""654.446"",""h"":""296.244"",""title"":""" & MonlamTitleLetter(rs!Titel) & """,""name"":""" & SourceNoFormNo & "_" & Right("0000" & No, 4) & ".jpg" & """}")
            Set Forms(MonId_PNo)("data")("Ids")(No)("Participant") = JsonConverter.ParseJson("{""SNo"":""" & SNo & """,""Name"":""" & rs!Name & """,""Age"":""" & rs!Age & """,""Fname"":""" & rs!Fname & """,""Titel"":" & rs!Titel & ",""RemarkNo"":" & rs!RemarkNo & "}")
            Set Forms(MonId_PNo)("data")("Ids")(No)("Photo") = JsonConverter.ParseJson("{""w"":186,""h"":243,""x"":829,""y"":461,""name"":""" & SourceNoFormNo_SNo & ".jpg""}")
        ElseIf No = 7 Then
            Set Forms(MonId_PNo)("data")("Ids")(No) = New Dictionary
            Set Forms(MonId_PNo)("data")("Ids")(No)("Id") = JsonConverter.ParseJson("{""x"":""796.883"",""y"":""742.463"",""w"":""654.446"",""h"":""296.244"",""title"":""" & MonlamTitleLetter(rs!Titel) & """,""name"":""" & SourceNoFormNo & "_" & Right("0000" & No, 4) & ".jpg" & """}")
            Set Forms(MonId_PNo)("data")("Ids")(No)("Participant") = JsonConverter.ParseJson("{""SNo"":""" & SNo & """,""Name"":""" & rs!Name & """,""Age"":""" & rs!Age & """,""Fname"":""" & rs!Fname & """,""Titel"":" & rs!Titel & ",""RemarkNo"":" & rs!RemarkNo & "}")
            Set Forms(MonId_PNo)("data")("Ids")(No)("Photo") = JsonConverter.ParseJson("{""w"":186,""h"":243,""x"":829,""y"":759,""name"":""" & SourceNoFormNo_SNo & ".jpg""}")
        ElseIf No = 8 Then
            Set Forms(MonId_PNo)("data")("Ids")(No) = New Dictionary
            Set Forms(MonId_PNo)("data")("Ids")(No)("Id") = JsonConverter.ParseJson("{""x"":""796.883"",""y"":""1039.729"",""w"":""654.446"",""h"":""296.244"",""title"":""" & MonlamTitleLetter(rs!Titel) & """,""name"":""" & SourceNoFormNo & "_" & Right("0000" & No, 4) & ".jpg" & """}")
            Set Forms(MonId_PNo)("data")("Ids")(No)("Participant") = JsonConverter.ParseJson("{""SNo"":""" & SNo & """,""Name"":""" & rs!Name & """,""Age"":""" & rs!Age & """,""Fname"":""" & rs!Fname & """,""Titel"":" & rs!Titel & ",""RemarkNo"":" & rs!RemarkNo & "}")
            Set Forms(MonId_PNo)("data")("Ids")(No)("Photo") = JsonConverter.ParseJson("{""w"":186,""h"":243,""x"":829,""y"":1059,""name"":""" & SourceNoFormNo_SNo & ".jpg""}")
        ElseIf No = 9 Then
            Set Forms(MonId_PNo)("data")("Ids")(No) = New Dictionary
            Set Forms(MonId_PNo)("data")("Ids")(No)("Id") = JsonConverter.ParseJson("{""x"":""796.883"",""y"":""1338.401"",""w"":""654.446"",""h"":""296.244"",""title"":""" & MonlamTitleLetter(rs!Titel) & """,""name"":""" & SourceNoFormNo & "_" & Right("0000" & No, 4) & ".jpg" & """}")
            Set Forms(MonId_PNo)("data")("Ids")(No)("Participant") = JsonConverter.ParseJson("{""SNo"":""" & SNo & """,""Name"":""" & rs!Name & """,""Age"":""" & rs!Age & """,""Fname"":""" & rs!Fname & """,""Titel"":" & rs!Titel & ",""RemarkNo"":" & rs!RemarkNo & "}")
            Set Forms(MonId_PNo)("data")("Ids")(No)("Photo") = JsonConverter.ParseJson("{""w"":186,""h"":243,""x"":829,""y"":1353,""name"":""" & SourceNoFormNo_SNo & ".jpg""}")
        ElseIf No = 10 Then
            Set Forms(MonId_PNo)("data")("Ids")(No) = New Dictionary
            Set Forms(MonId_PNo)("data")("Ids")(No)("Id") = JsonConverter.ParseJson("{""x"":""796.883"",""y"":""1634.517"",""w"":""654.446"",""h"":""296.244"",""title"":""" & MonlamTitleLetter(rs!Titel) & """,""name"":""" & SourceNoFormNo & "_" & Right("0000" & No, 4) & ".jpg" & """}")
            Set Forms(MonId_PNo)("data")("Ids")(No)("Participant") = JsonConverter.ParseJson("{""SNo"":""" & SNo & """,""Name"":""" & rs!Name & """,""Age"":""" & rs!Age & """,""Fname"":""" & rs!Fname & """,""Titel"":" & rs!Titel & ",""RemarkNo"":" & rs!RemarkNo & "}")
            Set Forms(MonId_PNo)("data")("Ids")(No)("Photo") = JsonConverter.ParseJson("{""w"":186,""h"":243,""x"":829,""y"":1649,""name"":""" & SourceNoFormNo_SNo & ".jpg""}")
        End If
        
        Dim lastPNo As Long, lastNo As Long
        Forms(MonId_PNo)("data")("Ids")(No)("Id")("sex") = participantSex
        Forms(MonId_PNo)("data")("Ids")(No)("Participant")("Sex") = participantSex
        Dim pageEnded As Boolean
        lastPNo = PNo
        lastNo = No
        rs.MoveNext
        pageEnded = rs.EOF Or (recSno Mod 10 = 0)
        If pageEnded Then
            RecalculateMonlam131Page Forms(MonId_PNo)
            Forms(MonId_PNo)("stamp") = Format$(Now(), "yyyy/mm/dd h:mm:ss ampm")
            Set Forms(MonId_PNo)("timestamp") = JsonConverter.ParseJson("{"".sv"": ""timestamp""}")
        End If
        Debug.Print "Page: " & lastPNo & " No: " & lastNo & " Key: " & MonId_PNo ', Left(JsonConverter.ConvertToJson(Forms(MonId_PNo)), 250)
        'rs.MoveNext
    Loop
    SysCmd acSysCmdInitMeter, "Saving JSON", 4
    SysCmd acSysCmdUpdateMeter, 1
        
    'Debug.Print JsonConverter.ConvertToJson(Forms)
    Dim jsonStr As String
    'Dim firebasePath As String
    ValidateMonlamExportPages Forms
    jsonStr = JsonConverter.ConvertToJson(Forms)
    SysCmd acSysCmdUpdateMeter, 2
    'firebasePath = "Monlam Form Register"
    'writeToFirebase firebaseProject, firebaseAuth, firebasePath, "PATCH", jsonStr
    'Debug.Print "writeToFirebase", firebaseProject, firebaseAuth, firebasePath, "PATCH" ', JsonConverter.ConvertToJson(Forms)
    WriteMonlamJsonUtf8 recOutputDir & recFileName & ".json", jsonStr
    SysCmd acSysCmdInitMeter, "Attaching JSON to PDF", 1
    AttachMonlamJsonToPdf recOutputDir & recFileName & ".pdf", recOutputDir & recFileName & ".json", Forms.Count
    SysCmd acSysCmdUpdateMeter, 3
    Debug.Print Timer() - Time
End If

Dim cloudDir As String
cloudDir = "C:\Users\hp\CloudStation\"
FileCopy recOutputDir & recFileName & ".json", cloudDir & recFileName & ".json"
FileCopy recOutputDir & recFileName & ".pdf", cloudDir & recFileName & ".pdf"

SysCmd acSysCmdUpdateMeter, 4
SysCmd acSysCmdRemoveMeter
'open output directory
Shell "C:\WINDOWS\explorer.exe """ & recOutputDir & "", vbNormalFocus

Set Forms = Nothing
rs.Close
Set rs = Nothing
Exit Sub
ExportFailed:
    MsgBox "Export stopped: " & Err.Number & vbCrLf & Err.Description & vbCrLf & _
        "Source: " & Err.Source, vbExclamation, "Monlam export"
    SysCmd acSysCmdRemoveMeter
End Sub

Private Sub ExportMonlamReportPdf(ByVal reportName As String, ByVal pdfPath As String)
    Dim fso As Object, reportObject As Object, probe As Object
    Dim temporaryPath As String, backupPath As String, probePath As String
    Dim stage As String, openedHere As Boolean, originalMoved As Boolean
    Dim failureNumber As Long, failureDescription As String
    Set fso = CreateObject("Scripting.FileSystemObject")
    On Error GoTo ReportFailed
    stage = "checking the destination folder"
    If Not fso.FolderExists(fso.GetParentFolderName(pdfPath)) Then Err.Raise 513, "Monlam export", "Output folder does not exist."
    probePath = fso.BuildPath(fso.GetParentFolderName(pdfPath), fso.GetTempName)
    Set probe = fso.CreateTextFile(probePath, False)
    probe.Close
    fso.DeleteFile probePath
    probePath = ""
    temporaryPath = fso.BuildPath(fso.GetParentFolderName(pdfPath), "Monlam_" & fso.GetTempName & ".pdf")
    stage = "finding the Access report"
    Set reportObject = CurrentProject.AllReports(reportName)
    If Not reportObject.IsLoaded Then
        stage = "opening the report in Print Preview (Open/NoData events may cancel this)"
        DoCmd.OpenReport reportName, acViewPreview, , , acHidden
        openedHere = True
        If Not Reports(reportName).HasData Then Err.Raise 513, "Monlam export", "The report has no data."
    End If
    stage = "exporting the report to a new PDF filename"
    DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, temporaryPath, False, , , acExportQualityPrint
    If Not fso.FileExists(temporaryPath) Then Err.Raise 513, "Monlam export", "Access did not create the PDF."
    If fso.GetFile(temporaryPath).Size = 0 Then Err.Raise 513, "Monlam export", "Access created an empty PDF."
    If openedHere Then
        stage = "closing the temporary report preview"
        DoCmd.Close acReport, reportName, acSaveNo
        openedHere = False
    End If
    stage = "replacing the previous PDF (close any viewer using that file)"
    If fso.FileExists(pdfPath) Then
        backupPath = pdfPath & "." & fso.GetTempName & ".bak"
        fso.MoveFile pdfPath, backupPath
        originalMoved = True
    End If
    fso.MoveFile temporaryPath, pdfPath
    If originalMoved Then fso.DeleteFile backupPath
    Exit Sub
ReportFailed:
    failureNumber = Err.Number
    failureDescription = Err.Description
    If openedHere Then CloseMonlamReportForCleanup reportName
    If originalMoved Then
        If Not fso.FileExists(pdfPath) Then fso.MoveFile backupPath, pdfPath
    End If
    ' Retain any generated temporary PDF for diagnosis/recovery.
    Err.Raise failureNumber, "MonlamJsonConverter_v2.ExportMonlamReportPdf", _
        failureDescription & vbCrLf & "Stage: " & stage & vbCrLf & _
        "Report: " & reportName & vbCrLf & "Destination: " & pdfPath & vbCrLf & _
        "Temporary PDF: " & temporaryPath
End Sub

Private Sub CloseMonlamReportForCleanup(ByVal reportName As String)
    On Error GoTo CloseFailed
    DoCmd.Close acReport, reportName, acSaveNo
CloseFailed:
    ' Preserve the original export error even if a report Close event cancels.
End Sub

Private Sub WriteMonlamJsonUtf8(ByVal filePath As String, ByVal jsonText As String)
    Dim textStream As Object, binaryStream As Object
    Set textStream = CreateObject("ADODB.Stream")
    textStream.Type = 2 ' Text.
    textStream.Charset = "utf-8"
    textStream.Open
    textStream.WriteText jsonText
    textStream.Position = 0
    textStream.Type = 1 ' Binary; remove the UTF-8 BOM for the JSON importer.
    textStream.Position = 3
    Set binaryStream = CreateObject("ADODB.Stream")
    binaryStream.Type = 1
    binaryStream.Open
    textStream.CopyTo binaryStream
    binaryStream.SaveToFile filePath, 2 ' Overwrite this export's JSON file.
    binaryStream.Close
    textStream.Close
End Sub

' Stable database Titel IDs; P/G are appended IDs, not display positions.
Public Sub ValidateMonlamExportPages(ByVal registrationPages As Dictionary)
    Dim pageIndex As Long, slot As Long, count As Long, expectedSerial As Long
    Dim key As String, pageRecord As Dictionary, ids As Dictionary, entry As Dictionary
    Dim slotKey As Variant, gapSeen As Boolean
    If registrationPages.Count = 0 Then Err.Raise 513, "MonlamJsonConverter_v2", "There are no participant pages to export."
    For pageIndex = 1 To registrationPages.Count
        key = "0131_" & Right("0000" & pageIndex, 4)
        If Not registrationPages.Exists(key) Then Err.Raise 513, "MonlamJsonConverter_v2", "JSON page numbering has a gap at " & key & "."
        Set pageRecord = registrationPages(key)
        If CLng(pageRecord("PNo")) <> pageIndex Or CStr(pageRecord("FormNo")) <> key Then Err.Raise 513, "MonlamJsonConverter_v2", "JSON page identity does not match " & key & "."
        If CStr(pageRecord("SourceNoFormNo")) <> "nam_0000_" & key Then Err.Raise 513, "MonlamJsonConverter_v2", "Incorrect source/form identity for " & key & "."
        Set ids = pageRecord("data")("Ids")
        count = 0: gapSeen = False
        For slot = 1 To 10
            slotKey = CStr(slot)
            If Not ids.Exists(slotKey) Then
                If ids.Exists(slot) Then slotKey = slot
            End If
            If Not ids.Exists(slotKey) Then Err.Raise 513, "MonlamJsonConverter_v2", "Missing JSON slot on " & key & "."
            Set entry = ids(slotKey)
            If entry.Exists("Participant") Then
                If gapSeen Then Err.Raise 513, "MonlamJsonConverter_v2", "Participant positions have a gap on " & key & "."
                expectedSerial = (pageIndex - 1) * 10 + slot
                If CStr(entry("Participant")("SNo")) <> CStr(expectedSerial) Then Err.Raise 513, "MonlamJsonConverter_v2", "Participant serial does not match its printed position on " & key & "."
                count = count + 1
            Else
                gapSeen = True
            End If
        Next slot
        If pageIndex < registrationPages.Count And count <> 10 Then Err.Raise 513, "MonlamJsonConverter_v2", "An intermediate JSON page is not full: " & key & "."
        If count = 0 Or CLng(pageRecord("dataTotal")) <> count Then Err.Raise 513, "MonlamJsonConverter_v2", "Incorrect participant total on " & key & "."
    Next pageIndex
End Sub

Private Function MonlamTitleLetter(ByVal titleId As Variant) As String
    If IsNull(titleId) Or IsEmpty(titleId) Then
        MonlamTitleLetter = "X"
        Exit Function
    End If
    Select Case CLng(titleId)
        Case 0: MonlamTitleLetter = "X"
        Case 1: MonlamTitleLetter = "V"
        Case 2: MonlamTitleLetter = "T"
        Case 3: MonlamTitleLetter = "K"
        Case 4: MonlamTitleLetter = "L"
        Case 5: MonlamTitleLetter = "D"
        Case 6: MonlamTitleLetter = "A"
        Case 7: MonlamTitleLetter = "N"
        Case 8: MonlamTitleLetter = "S"
        Case 9: MonlamTitleLetter = "O"
        Case 10: MonlamTitleLetter = "P"
        Case 11: MonlamTitleLetter = "G"
        Case Else: Err.Raise 513, "MonlamJsonConverter_v2", "Unknown Titel ID: " & CStr(titleId)
    End Select
End Function

Private Function MonlamSexCode(ByVal value As Variant) As String
    MonlamSexCode = ""
    If IsNull(value) Or IsEmpty(value) Then Exit Function
    Select Case UCase(Trim(CStr(value)))
        Case "M", "MALE": MonlamSexCode = "M"
        Case "F", "FEMALE": MonlamSexCode = "F"
        Case "": ' Leave unavailable historical values blank.
        Case Else: Err.Raise 513, "MonlamJsonConverter_v2", "Unknown Sex value: " & CStr(value)
    End Select
End Function

' Repairs parsed historical JSON in memory. Returns the number of pages processed.
' Call before importing records; save explicitly if a repaired JSON file is wanted.
' This is for nam_0000 / MonId 131 / the 1652 x 2338 report layout ONLY.
' It must not be applied to current web-form JPEG coordinates.
Public Function RepairMonlam131Forms(ByVal registrationPages As Dictionary) As Long
    Dim pageKey As Variant
    Dim pageRecord As Dictionary
    RepairMonlam131Forms = 0
    ' Validate all pages before changing any of them.
    For Each pageKey In registrationPages.Keys
        Set pageRecord = registrationPages(pageKey)
        ValidateMonlam131Page pageRecord
    Next pageKey
    For Each pageKey In registrationPages.Keys
        Set pageRecord = registrationPages(pageKey)
        RecalculateMonlam131Page pageRecord
        RepairMonlam131Forms = RepairMonlam131Forms + 1
    Next pageKey
End Function

Private Sub ValidateMonlam131Page(ByVal pageRecord As Dictionary)
    Dim pageData As Dictionary, formImage As Dictionary
    If Not pageRecord.Exists("MonId") Or Not pageRecord.Exists("SourceNo") Or Not pageRecord.Exists("SourceNoFormNo") Or Not pageRecord.Exists("data") Then
        Err.Raise 513, "MonlamJsonConverter_v2", "Missing historical page metadata."
    End If
    If CStr(pageRecord("MonId")) <> "131" Or CStr(pageRecord("SourceNo")) <> "nam_0000" Then
        Err.Raise 513, "MonlamJsonConverter_v2", "Repair only supports the historical nam_0000 MonId 131 export."
    End If
    Set pageData = pageRecord("data")
    If Not pageData.Exists("Form") Or Not pageData.Exists("Ids") Then
        Err.Raise 513, "MonlamJsonConverter_v2", "Expected historical data.Form and data.Ids metadata."
    End If
    Set formImage = pageData("Form")
    If Not formImage.Exists("w") Or Not formImage.Exists("h") Then
        Err.Raise 513, "MonlamJsonConverter_v2", "Missing historical form dimensions."
    End If
    If CDbl(formImage("w")) <> 1652 Or CDbl(formImage("h")) <> 2338 Then
        Err.Raise 513, "MonlamJsonConverter_v2", "Repair requires the historical 1652 x 2338 report layout."
    End If
End Sub

Private Sub RecalculateMonlam131Page(ByVal pageRecord As Dictionary)
    Dim ids As Dictionary, entry As Dictionary, idRecord As Dictionary
    Dim slot As Long, slotKey As Variant, rowTops As Variant
    Dim title As String, sex As String, idSex As String
    Dim participant As Dictionary
    Dim dataX As Long, dataV As Long, dataT As Long, dataK As Long
    Dim dataM As Long, dataF As Long, dataS As Long, participantTotal As Long
    dataX = 0: dataV = 0: dataT = 0: dataK = 0
    dataM = 0: dataF = 0: dataS = 0: participantTotal = 0
    ValidateMonlam131Page pageRecord
    Set ids = pageRecord("data")("Ids")
    rowTops = Array("444.686", "742.463", "1039.729", "1338.401", "1634.517")
    For slot = 1 To 10
        ' JSON parsing produces string keys; the exporter uses numeric keys.
        slotKey = CStr(slot)
        If Not ids.Exists(slotKey) Then
            If ids.Exists(slot) Then slotKey = slot
        End If
        If Not ids.Exists(slotKey) Then Set ids(slotKey) = New Dictionary
        Set entry = ids(slotKey)
        If Not entry.Exists("Participant") Then
            If Not entry.Exists("Id") Then Set entry("Id") = New Dictionary
            Set idRecord = entry("Id")
            If slot <= 5 Then
                idRecord("x") = "142.437"
            Else
                idRecord("x") = "796.883"
            End If
            idRecord("y") = rowTops((slot - 1) Mod 5)
            idRecord("w") = "654.446"
            idRecord("h") = "296.244"
            idRecord("title") = "X"
            idRecord("sex") = ""
            idRecord("name") = pageRecord("SourceNoFormNo") & "_" & Right("0000" & slot, 4) & ".jpg"
        Else
            ' Count real records regardless of title, including O or an unknown title.
            participantTotal = participantTotal + 1
            Set participant = entry("Participant")
            Set idRecord = entry("Id")
            sex = "": idSex = ""
            If participant.Exists("Sex") Then sex = MonlamSexCode(participant("Sex"))
            If idRecord.Exists("sex") Then idSex = MonlamSexCode(idRecord("sex"))
            If sex <> "" And idSex <> "" And sex <> idSex Then
                Err.Raise 513, "MonlamJsonConverter_v2", "Conflicting Participant.Sex and Id.sex in slot " & slot & "."
            End If
            If sex = "" Then sex = idSex
            If sex = "" Then
                ' Historical inference only when neither field supplies sex.
                ' P has no historical default; explicit query sex is required for new exports.
                Select Case CStr(idRecord("title"))
                    Case "L", "D", "N": sex = "M"
                    Case "A": sex = "F"
                End Select
            End If
            participant("Sex") = sex
            idRecord("sex") = sex
        End If
        Set idRecord = entry("Id")
        title = CStr(idRecord("title"))
        Select Case title
            Case "X": dataX = dataX + 1
            Case "V": dataV = dataV + 1
            Case "T": dataT = dataT + 1
            Case "K": dataK = dataK + 1
            Case "L", "D", "N", "P"
                If idRecord("sex") = "M" Then
                    dataM = dataM + 1
                ElseIf idRecord("sex") = "F" Then
                    dataF = dataF + 1
                End If
            Case "A": dataF = dataF + 1
            Case "S": dataS = dataS + 1
        End Select
    Next slot
    pageRecord("dataX") = dataX
    pageRecord("dataV") = dataV
    pageRecord("dataT") = dataT
    pageRecord("dataK") = dataK
    pageRecord("dataM") = dataM
    pageRecord("dataF") = dataF
    pageRecord("dataS") = dataS
    pageRecord("dataTotal") = participantTotal
End Sub