Proclaim slideshow validation

validate_proclaim.py

import sqlite3
from pathlib import Path
from collections import defaultdict
import json
from lxml import etree


def decode_richtextXML(xml):
    # _richtextfield:Lyrics <Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I love You Lord" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="Oh Your mercy never fails me" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="All my days" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I’ve been held in Your hands" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="From the moment that I wake up" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="Until I lay my head" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I will sing of the goodness of God" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="All my life You have been faithful" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="All my life You have been so, so good" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="With every breath that I am able" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I will sing of the goodness of God" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I love Your voice" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="You have led me through the fire" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="In darkest night" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="You are close like no other" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I’ve known You as a father" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I’ve known You as a friend" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I have lived in the goodness of God" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="All my life You have been faithful" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="All my life You have been so, so good" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="With every breath that I am able" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I will sing of the goodness of God" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="Your goodness is running after" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="It’s running after me" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="Your goodness is running after" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="It’s running after me" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="With my life laid down" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I’m surrendered now" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I give You everything" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="Your goodness is running after" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="It’s running after me" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="Your goodness is running after" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="It’s running after me" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="Your goodness is running after" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="It’s running after me" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="With my life laid down" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I’m surrendered now" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I give You everything" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="Your goodness is running after" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="It’s running after me" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="All my life You have been faithful" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="All my life You have been so, so good" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="With every breath that I am able" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I will sing of the goodness of God" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0" /><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="All my life You have been faithful" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="All my life You have been so, so good" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="With every breath that I am able" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I will sing of the goodness of God" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0"><Run Text="I will sing of the goodness of God" /></Paragraph><Paragraph Language="en-US" Margin="0,0,0,0" />

    # parse that XML: each Paragraph Run Text is a line
    """"
    Decode the rich text XML from Proclaim into plain text.

    The basic XML format is:
    <Paragraph Language="en-US" Margin="0,0,0,0">
        <Run Text="I love You Lord" />
    </Paragraph>
    <Paragraph Language="en-US" Margin="0,0,0,0">
        <Run Text="Oh Your mercy never fails me" />
    </Paragraph>
    etc.

    Each Paragraph contains one or more Run elements, each with a Text attribute (and maybe formatting attributes?).
    """
    result = ''

    root = etree.fromstring('<Song>' + xml + '</Song>')
    for paragraph in root:
        runs = paragraph.findall('Run')
        for run in runs:
            result += run.attrib['Text'] + ' '
        result += '\n'
    return result


# TODO: put your path here.
proclaim_data = Path('~/Library/Application Support/Proclaim/Data/REPLACE_ME.XYZ/').expanduser()
presentations_db = proclaim_data / 'PresentationManager' / 'PresentationManager.db'

conn = sqlite3.connect(presentations_db)

# Find the most recent presentation
presentations = conn.execute(
    '''
    SELECT
        Presentations.PresentationId, Presentations.DateGiven, Presentations.Title
        FROM Presentations
        WHERE DateGiven > "2024-01-01"
        ORDER BY DateGiven DESC
    ;''')

most_recent_presentation = presentations.fetchone()
assert most_recent_presentation is not None, "No presentations found after 2024-01-01"

# Get all the items in that presentation
presentation_id = most_recent_presentation[0]
presentation_title = most_recent_presentation[2]
print(f"Validating presentation {presentation_title} ({presentation_id})")

service_items = conn.execute(
    '''
    SELECT
        Title, Content, ServiceItemKind
        FROM ServiceItems
        WHERE PresentationId = ?
    ;''', (presentation_id,)).fetchall()


def warn(item_name, message):
    print(f"***Warning***: {item_name}: {message}")


def split_into_sections(text):
    """Split the text into sections based on blank lines."""
    sections = ['']
    for line in text.strip().split('\n'):
        if line.strip():
            sections[-1] += line + '\n'
        else:
            sections.append('')
    return sections


def get_first_line(text):
    """Get the first non-empty line from the text."""
    return text.strip().split('\n')[0].strip()

for i, (title, content_json, item_kind) in enumerate(service_items):
    print(f"{item_kind}: {title}")
    content = json.loads(content_json)
    if item_kind == "SongLyrics":
        assert content.get('_richtextfield:Lyrics'), f"Missing _richtextfield:Lyrics in {title}"
        # check transitions
        transition_info = (content.get('UseCustomTransition'), content.get('CustomTransitionKind'), content.get('CustomTransitionDuration'))
        if transition_info != ('true', 'LyricScrolling', '0'):
            warn(title, f"Unexpected transition info: {transition_info}")
            
        # Find the translations output
        translations = [key for key in content if key.startswith("slideOutput") and key.endswith("RichTextXml")]
        if len(translations) != 1:
            warn(title, f"Expected one translation, found {len(translations)}")
        translation_key = translations[0]
        translation_content = content[translation_key]

        # Check that the number of \n\n-delimited sections is the same
        original_lyrics = decode_richtextXML(content['_richtextfield:Lyrics'])
        translation_lyrics = decode_richtextXML(translation_content)
        original_sections = split_into_sections(original_lyrics)
        translation_sections = split_into_sections(translation_lyrics)
        if len(original_sections) != len(translation_sections):
            warn(title, f"Number of sections in original ({len(original_sections)}) and translation ({len(translation_sections)}) do not match")
            # print the first line of each section, for debug
            for i, section in enumerate(original_sections):
                print(f"Original section {i}: {get_first_line(section)}")
            for i, section in enumerate(translation_sections):
                print(f"Translation section {i}: {get_first_line(section)}")


conn.close()

import sys
sys.exit(0)



for title, (date, content) in latest_content.items():
    f.write(decode_richtextXML(content['_richtextfield:Lyrics']))

    all_songs_joined.append(f"# {title}\n\n")
    all_songs_joined.append(f"Last done: {date}\n\n")
    all_songs_joined.append(decode_richtextXML(content['_richtextfield:Lyrics']))

    if False:
        for key, value in content.items():
            if key.startswith("slideOutput") and key.endswith("RichTextXml"):
                f.write(f"\n\n\n\n# Translation\n\n")
                f.write(decode_richtextXML(value))


with (output_path / 'all_lyrics.md').open('w') as f:
    f.write('\n'.join(all_songs_joined))