joewiz/gaps-between-principals-appointments.xq

## gaps-between-principals-appointments.xq
xquery version "3.1";

declare namespace output = "http://www.w3.org/2010/xslt-xquery-serialization";

declare option output:method "html5";
declare option output:media-type "text/html";

let $title := "Gaps between appointments of Principal Officers"
let $min-gap := xs:dayTimeDuration("P30D")
let $positions := collection("/db/apps/pocom/positions-principals")/principal-position[not(id = ("ambassador-at-large", "career-ambassador"))]
return

<html>
    <head>
        <meta charset="utf-8"/>
        <meta http-equiv="X-UA-Compatible" content="IE=edge"/>
        <meta name="viewport" content="width=device-width, initial-scale=1"/>
        <!-- The above 3 meta tags *must* come first in the head; any other head content must come *after* these tags -->

        <title>{$title}</title>

        <!-- Latest compiled and minified CSS -->
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous"/>

        <!-- Optional theme -->
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" integrity="sha384-rHyoN1iRsVXV4nD0JutlnGaslCJuC7uwjduW9SVrLvRYooPp2bWYgmgJQIXwl/Sp" crossorigin="anonymous"/>

        <!-- Latest compiled and minified JavaScript -->
        <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"/>
    </head>

<body>
    <div class="container">
        <h1>{$title}</h1>
        <p>In Chiefs of Mission, we already include certain categories of "acting" chiefs at post; specifically, it includes all Chargés d’Affaires and Chargés d’Affaires ad interim who served (1) for a continuous period of 12 months or more, (2) at the establishment or ending of diplomatic relations with a country, or (3) at the closure or opening of an embassy. To aid in a project to gauge whether Principal Officers could be extended to list "acting" principals, this report lists all gaps of <em>30 days or greater</em> between appointments across the Principal Officers database, taking "termination of appointment" as the departure date, preferring "entry on duty" over "appointed" as the date of arrival. The positions are listed alphabetically, includes discontinued positions, and excludes the positions of Career Ambassador and Ambassador at Large, which are not held exclusively by a single person. In cases where a gap cannot be calculated, the information is indicated in the "Notes" column; the reason is because the departure or arrival date is missing or isn't a complete date.</p>
        {
            for $position in $positions
            let $position-id := $position/id
            let $position-label := $position/names/singular
            let $principals := $position//principal
            let $gaps :=
                for $principal at $n in $principals
                let $ended := ($principal/ended/date, "?")[. ne ""][1] cast as xs:string
                let $principal-name := collection("/db/apps/pocom/people")//id[. eq $principal/person-id]/../persName/(surname | forename | genName) => string-join(", ")
                let $next := $principals[$n + 1]
                let $next-started := ($next/started/date, $next/appointed/date, "?")[. ne ""][1] cast as xs:string
                let $next-name := collection("/db/apps/pocom/people")//id[. eq $next/person-id]/../persName/(surname | forename | genName) => string-join(", ")
                let $gap :=
                    if ($ended castable as xs:date and $next-started castable as xs:date) then
                        map {
                            "type": "normal",
                            "principal-id": $principal/id/string(),
                            "principal-name": $principal-name,
                            "ended": $ended cast as xs:date,
                            "next-id": $next/id/string(),
                            "next-name": $next-name,
                            "next-started": $next-started cast as xs:date,
                            "duration": $next-started cast as xs:date - $ended cast as xs:date
                        }
                    else
                        map {
                            "type": "problem",
                            "principal-id": $principal/id/string(),
                            "principal-name": $principal-name,
                            "ended": if ($ended castable as xs:date) then $ended cast as xs:date else $ended,
                            "next-id": $next/id/string(),
                            "next-name": $next-name,
                            "next-started": if ($next-started castable as xs:date) then $next-started cast as xs:date else $next-started
                        }
                where exists($next)
                return
                    if ($gap?type eq "normal") then
                        if ($gap?duration ge $min-gap) then
                            <tr class="normal">
                                <td>{$gap?principal-name}</td>
                                <td>{$gap?next-name}</td>
                                <td>{$gap?duration => days-from-duration() => format-number("#,###.##")} days</td>
                                <td>{$gap?ended => format-date("[MNn,*-3] [D], [Y0001]", "en", (), "US")}</td>
                                <td>{$gap?next-started => format-date("[MNn,*-3] [D], [Y0001]", "en", (), "US")}</td>
                                <td>-</td>
                            </tr>
                        else
                            ()
                    else
                        <tr class="problem">
                            <td>{$gap?principal-name}</td>
                            <td>{$gap?next-name}</td>
                            <td>-</td>
                            <td>{if ($gap?ended instance of xs:date) then $gap?ended => format-date("[MNn,*-3] [D], [Y0001]", "en", (), "US") else $gap?ended}</td>
                            <td>{if ($gap?next-started instance of xs:date) then $gap?next-started => format-date("[MNn,*-3] [D], [Y0001]", "en", (), "US") else $gap?next-started}</td>
                            <td>Problem calculating gap</td>
                        </tr>
            where exists($gaps)
            order by $position-id
            return
                <div style="page-break-after: always">
                    <h2>{normalize-space($position-label)}</h2>
                    <p>{count($gaps[@class eq "normal"])} gaps of ≥{days-from-duration($min-gap)} days. URL: https://history.state.gov/departmenthistory/people/principalofficers/{$position-id}.</p>
                    <table class="table table-bordered table-striped" style="page-break-inside: avoid">
                        <thead>
                            <tr>
                                <th scope="col">Departing officer</th>
                                <th scope="col">Incoming officer</th>
                                <th scope="col">Gap (≥30 days)</th>
                                <th scope="col">Departure date</th>
                                <th scope="col">Start date</th>
                                <th scope="col">Notes</th>
                            </tr>
                        </thead>
                        <tbody>
                            {
                                $gaps
                            }
                        </tbody>
                    </table>
                </div>
            }
        </div>
    </body>
</html>
	xquery version "3.1";

	declare namespace output = "http://www.w3.org/2010/xslt-xquery-serialization";

	declare option output:method "html5";
	declare option output:media-type "text/html";

	let $title := "Gaps between appointments of Principal Officers"
	let $min-gap := xs:dayTimeDuration("P30D")
	let $positions := collection("/db/apps/pocom/positions-principals")/principal-position[not(id = ("ambassador-at-large", "career-ambassador"))]
	return

	<html>
	<head>
	<meta charset="utf-8"/>
	<meta http-equiv="X-UA-Compatible" content="IE=edge"/>
	<meta name="viewport" content="width=device-width, initial-scale=1"/>
	<!-- The above 3 meta tags must come first in the head; any other head content must come after these tags -->

	<title>{$title}</title>

	<!-- Latest compiled and minified CSS -->
	<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous"/>

	<!-- Optional theme -->
	<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" integrity="sha384-rHyoN1iRsVXV4nD0JutlnGaslCJuC7uwjduW9SVrLvRYooPp2bWYgmgJQIXwl/Sp" crossorigin="anonymous"/>

	<!-- Latest compiled and minified JavaScript -->
	<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"/>
	</head>

	<body>
	<div class="container">
	<h1>{$title}</h1>
	<p>In Chiefs of Mission, we already include certain categories of "acting" chiefs at post; specifically, it includes all Chargés d’Affaires and Chargés d’Affaires ad interim who served (1) for a continuous period of 12 months or more, (2) at the establishment or ending of diplomatic relations with a country, or (3) at the closure or opening of an embassy. To aid in a project to gauge whether Principal Officers could be extended to list "acting" principals, this report lists all gaps of <em>30 days or greater</em> between appointments across the Principal Officers database, taking "termination of appointment" as the departure date, preferring "entry on duty" over "appointed" as the date of arrival. The positions are listed alphabetically, includes discontinued positions, and excludes the positions of Career Ambassador and Ambassador at Large, which are not held exclusively by a single person. In cases where a gap cannot be calculated, the information is indicated in the "Notes" column; the reason is because the departure or arrival date is missing or isn't a complete date.</p>
	{
	for $position in $positions
	let $position-id := $position/id
	let $position-label := $position/names/singular
	let $principals := $position//principal
	let $gaps :=
	for $principal at $n in $principals
	let $ended := ($principal/ended/date, "?")[. ne ""][1] cast as xs:string
	let $principal-name := collection("/db/apps/pocom/people")//id[. eq $principal/person-id]/../persName/(surname \| forename \| genName) => string-join(", ")
	let $next := $principals[$n + 1]
	let $next-started := ($next/started/date, $next/appointed/date, "?")[. ne ""][1] cast as xs:string
	let $next-name := collection("/db/apps/pocom/people")//id[. eq $next/person-id]/../persName/(surname \| forename \| genName) => string-join(", ")
	let $gap :=
	if ($ended castable as xs:date and $next-started castable as xs:date) then
	map {
	"type": "normal",
	"principal-id": $principal/id/string(),
	"principal-name": $principal-name,
	"ended": $ended cast as xs:date,
	"next-id": $next/id/string(),
	"next-name": $next-name,
	"next-started": $next-started cast as xs:date,
	"duration": $next-started cast as xs:date - $ended cast as xs:date
	}
	else
	map {
	"type": "problem",
	"principal-id": $principal/id/string(),
	"principal-name": $principal-name,
	"ended": if ($ended castable as xs:date) then $ended cast as xs:date else $ended,
	"next-id": $next/id/string(),
	"next-name": $next-name,
	"next-started": if ($next-started castable as xs:date) then $next-started cast as xs:date else $next-started
	}
	where exists($next)
	return
	if ($gap?type eq "normal") then
	if ($gap?duration ge $min-gap) then
	<tr class="normal">
	<td>{$gap?principal-name}</td>
	<td>{$gap?next-name}</td>
	<td>{$gap?duration => days-from-duration() => format-number("#,###.##")} days</td>
	<td>{$gap?ended => format-date("[MNn,*-3] [D], [Y0001]", "en", (), "US")}</td>
	<td>{$gap?next-started => format-date("[MNn,*-3] [D], [Y0001]", "en", (), "US")}</td>
	<td>-</td>
	</tr>
	else
	()
	else
	<tr class="problem">
	<td>{$gap?principal-name}</td>
	<td>{$gap?next-name}</td>
	<td>-</td>
	<td>{if ($gap?ended instance of xs:date) then $gap?ended => format-date("[MNn,*-3] [D], [Y0001]", "en", (), "US") else $gap?ended}</td>
	<td>{if ($gap?next-started instance of xs:date) then $gap?next-started => format-date("[MNn,*-3] [D], [Y0001]", "en", (), "US") else $gap?next-started}</td>
	<td>Problem calculating gap</td>
	</tr>
	where exists($gaps)
	order by $position-id
	return
	<div style="page-break-after: always">
	<h2>{normalize-space($position-label)}</h2>
	<p>{count($gaps[@class eq "normal"])} gaps of ≥{days-from-duration($min-gap)} days. URL: https://history.state.gov/departmenthistory/people/principalofficers/{$position-id}.</p>
	<table class="table table-bordered table-striped" style="page-break-inside: avoid">
	<thead>
	<tr>
	<th scope="col">Departing officer</th>
	<th scope="col">Incoming officer</th>
	<th scope="col">Gap (≥30 days)</th>
	<th scope="col">Departure date</th>
	<th scope="col">Start date</th>
	<th scope="col">Notes</th>
	</tr>
	</thead>
	<tbody>
	{
	$gaps
	}
	</tbody>
	</table>
	</div>
	}
	</div>
	</body>
	</html>