Coterminous appointments

2012-palm-01.xml

<!-- 
This is a sample data file for capturing coterminous appointments
Store in the /db/cms/apps/principals-chiefs/data/coterminous-appointments collection.
The rule for deriving the file's <id> (and file basename) is to join the following elements with a hyphen:
    YYYY: the year of the first appointment
    ABCD: the first four letters of the appointee's last name (unless fewer than 4 letters
    NN:   usually 01, but increment to 02, 03, etc. to prevent id collisions
In this example: 
    2012: all appointments started in 2012
    palm: from Palmer
    01:   the first appointment with the id prefix, 2012-palm
Pass the document's <id> to the process-appointments.xq query, and you'll get a listing
of all of the coterminous appointments for each appointment, in the form that would appear at:
    https://history.state.gov/departmenthistory/people/palmer-larry-leon
-->
<coterminous-appointments>
    <id>2012-palm-01</id>
    <person-id>palmer-larry-leon</person-id>
    <resident-at>
        <locale>Bridgetown</locale>
        <contemporary-territory-id>barbados</contemporary-territory-id>
    </resident-at>
    <chief-id>bb-2012-palm-01</chief-id>
    <chief-id>ag-2012-palm-01</chief-id>
    <chief-id>dm-2012-palm-01</chief-id>
    <chief-id>gd-2012-palm-01</chief-id>
    <chief-id>kn-2012-palm-01</chief-id>
    <chief-id>lc-2012-palm-01</chief-id>
    <chief-id>vc-2012-palm-01</chief-id>
</coterminous-appointments>

process-appointments.xq

xquery version "3.0";

(: Functions for processing "coterminous appointments" data files such as 2012-palm-01.xml.
   The key function is "summarize-all-coterminous-appointments()" (toward the end below).
   The other functions handle one appointment's worth of data at a time.
:)

declare function local:join-with-and($words as xs:string+) as xs:string {
    let $count := count($words)
    return
        if ($count = 1) then 
            $words
        else if ($count = 2) then
            string-join($words, ' and ')
        else
            concat(
                string-join(subsequence($words, 1, $count - 1), ', '),
                ', and ',
                $words[last()]
            )
};


declare function local:summarize-coterminous-appointments($id as xs:string) {
    local:summarize-other-coterminous-appointments($id, ())
};

declare function local:summarize-other-coterminous-appointments($id as xs:string, $excluded-id as xs:string?) {
    let $doc := collection('/db/cms/apps/principals-chiefs/data/coterminous-appointments')/coterminous-appointments[id = $id]
    let $appointments := collection('/db/cms/apps/principals-chiefs/data/missions-countries')//chief[id = $doc/chief-id[not(. = $excluded-id)]]
    let $all-countries := collection('/db/cms/apps/countries/data')/country
    let $countries := local:join-with-and($all-countries[id = $appointments/preceding::territory-id]/label)
    let $resident-at := $doc/resident-at/locale/string()
    let $complete-summary := concat(if ($excluded-id) then 'Also accredited to ' else 'Accredited to ', $countries, '; resident at ', $resident-at)
    return
        $complete-summary
};

declare function local:summarize-all-coterminous-appointments($id as xs:string) {
    let $doc := collection('/db/cms/apps/principals-chiefs/data/coterminous-appointments')/coterminous-appointments[id = $id]
    let $person := collection('/db/cms/apps/principals-chiefs/data/people')/person[id = $doc/person-id]
    let $person-name := string-join(($person/persName/surname, $person/persName/forename, $person/persName/genName), ', ')
    let $all-countries := collection('/db/cms/apps/countries/data')/country
    return
        <appointments count="{count($doc/chief-id)}">
            <id>{$id}</id>
            <person-id>{$person/id/string()}</person-id>
            <person>{$person-name}</person>
            {
            for $appointment-id at $n in $doc/chief-id
            let $appointment := collection('/db/cms/apps/principals-chiefs/data/missions-countries')//chief[id = $appointment-id]
            let $country := $all-countries[id = $appointment/preceding::territory-id]/label/string()
            return
                <appointment n="{$n}">
                    <country>{$country}</country>
                    <coterminous-appointments-summary>{local:summarize-other-coterminous-appointments($id, $appointment-id)}</coterminous-appointments-summary>
                </appointment>
            }
        </appointments>
};

let $id := '2012-palm-01'
return 
    local:summarize-all-coterminous-appointments($id)

process-appointments_results.xml

<!-- This is the result of calling the process-appointments.xq query on 2012-palm-01.xml data -->
<appointments count="7">
    <id>2012-palm-01</id>
    <person-id>palmer-larry-leon</person-id>
    <person>Palmer, Larry Leon</person>
    <appointment n="1">
        <country>Barbados</country>
        <coterminous-appointments-summary>Also accredited to Antigua and Barbuda, Dominica,
            Grenada, Saint Kitts and Nevis, Saint Lucia, and Saint Vincent and the Grenadines;
            resident at Bridgetown</coterminous-appointments-summary>
    </appointment>
    <appointment n="2">
        <country>Antigua and Barbuda</country>
        <coterminous-appointments-summary>Also accredited to Barbados, Dominica, Grenada, Saint
            Kitts and Nevis, Saint Lucia, and Saint Vincent and the Grenadines; resident at
            Bridgetown</coterminous-appointments-summary>
    </appointment>
    <appointment n="3">
        <country>Dominica</country>
        <coterminous-appointments-summary>Also accredited to Antigua and Barbuda, Barbados,
            Grenada, Saint Kitts and Nevis, Saint Lucia, and Saint Vincent and the Grenadines;
            resident at Bridgetown</coterminous-appointments-summary>
    </appointment>
    <appointment n="4">
        <country>Grenada</country>
        <coterminous-appointments-summary>Also accredited to Antigua and Barbuda, Barbados,
            Dominica, Saint Kitts and Nevis, Saint Lucia, and Saint Vincent and the Grenadines;
            resident at Bridgetown</coterminous-appointments-summary>
    </appointment>
    <appointment n="5">
        <country>Saint Kitts and Nevis</country>
        <coterminous-appointments-summary>Also accredited to Antigua and Barbuda, Barbados,
            Dominica, Grenada, Saint Lucia, and Saint Vincent and the Grenadines; resident at
            Bridgetown</coterminous-appointments-summary>
    </appointment>
    <appointment n="6">
        <country>Saint Lucia</country>
        <coterminous-appointments-summary>Also accredited to Antigua and Barbuda, Barbados,
            Dominica, Grenada, Saint Kitts and Nevis, and Saint Vincent and the Grenadines; resident
            at Bridgetown</coterminous-appointments-summary>
    </appointment>
    <appointment n="7">
        <country>Saint Vincent and the Grenadines</country>
        <coterminous-appointments-summary>Also accredited to Antigua and Barbuda, Barbados,
            Dominica, Grenada, Saint Kitts and Nevis, and Saint Lucia; resident at
            Bridgetown</coterminous-appointments-summary>
    </appointment>
</appointments>