def system(self):
"""
Reference to `Doc` object for job's publishing control document
"""
if not hasattr(self, "_system"):
opts = dict(id=None, before=self.started)
if self.id:
query = Query("pub_proc", "pub_system")
query.where(query.Condition("id", self.id))
row = query.execute(self.cursor).fetchone()
if not row:
raise Exception("Job {} not found".format(self.id))
opts["id"] = row.pub_system
else:
name = self.__opts.get("system")
if name:
query = Query("document d", "d.id")
query.join("doc_type t", "t.id = d.doc_type")
query.where("t.name = 'PublishingSystem'")
query.where(query.Condition("d.title", name))
rows = query.execute(self.cursor).fetchall()
if len(rows) > 1:
raise Exception(f"multiple {name} docs")
if not rows:
message = "Publishing system {!r} not found"
raise Exception(message.format(name))
row = rows[0]
opts["id"] = row.id
opts["title"] = name
if opts["id"]:
self._system = Doc(self.session, **opts)
else:
self._system = None
return self._system
def _glossary_term_names(self):
"""
Find GlossaryTermName documents linked to a GlossaryTermConcept doc
Parameters:
ConceptID - required CDR ID for the master concept document
Return:
XML document node with the following structure:
ReportBody
ReportName
GlossaryTermName*
@ref
"""
concept_id = self.__opts.get("ConceptId")
if not concept_id:
raise Exception("Missing required 'ConceptId' parameter")
path = "/GlossaryTermName/GlossaryTermConcept/@cdr:ref"
doc = Doc(self.session, id=concept_id)
query = Query("document d", "d.id", "d.title")
query.join("query_term n", "n.doc_id = d.id")
query.where(query.Condition("n.path", path))
query.where(query.Condition("n.int_val", doc.id))
body = self.__start_body()
for id, title in query.execute(self.cursor).fetchall():
cdr_id = Doc.normalize_id(id)
etree.SubElement(body, "GlossaryTermName", ref=cdr_id).text = title
return body
def _genetics_syndromes(self):
"""
Find Term documents used to represent genetics syndromes
Parameters:
TitlePattern - optional string narrowing report to syndrome
names which match the pattern
Return:
XML document node with the following structure:
ReportBody
ReportName
ReportRow*
DocId
DocTitle
"""
pattern = self.__opts.get("TitlePattern")
query = Query("document d", "d.id", "d.title")
query.unique().order("d.title")
query.join("query_term t", "t.doc_id = d.id")
query.join("query_term s", "s.doc_id = t.doc_id",
"LEFT(s.node_loc, 8) = LEFT(t.node_loc, 8)")
query.where("t.path = '/Term/MenuInformation/MenuItem/MenuType'")
query.where("s.path = '/Term/MenuInformation/MenuItem/MenuStatus'")
query.where("s.value = 'Online'")
query.where("t.value = 'Genetics Professionals--GeneticSyndrome'")
if pattern:
query.where(query.Condition("d.title", pattern, "LIKE"))
body = self.__start_body()
for id, title in query.execute(self.cursor).fetchall():
wrapper = etree.SubElement(body, "ReportRow")
etree.SubElement(wrapper, "DocId").text = Doc.normalize_id(id)
etree.SubElement(wrapper, "DocTitle").text = title
return body
def _dated_actions(self):
"""
Find the to-do list for documents of a particular type
Parameters:
DocType - required string naming the document type for
which documents should be included
Return:
XML document node with the following structure:
ReportBody
ReportName
ReportRow*
DocId
DocTitle
"""
doctype = self.__opts.get("DocType")
if not doctype:
raise Exception("Missing required 'DocType' parameter")
path = "/{}/DatedAction/ActionDate".format(doctype)
query = Query("document d", "d.id", "d.title").unique()
query.join("query_term a", "a.doc_id = d.id")
query.where(query.Condition("a.path", path))
rows = query.execute(self.cursor).fetchall()
body = self.__start_body()
for id, title in rows:
wrapper = etree.SubElement(body, "ReportRow")
etree.SubElement(wrapper, "DocId").text = Doc.normalize_id(id)
etree.SubElement(wrapper, "DocTitle").text = title
result = Doc(self.session, id=id).filter("name:Dated Actions")
wrapper.append(result.result_tree.getroot())
return body
def _inactive_checked_out_documents(self):
"""
Report on locked documents which haven't seen any action recently
Parameters:
InactivityLength - required string for how long a document
can be active before showing up on the report
in the form YYYY-MM-DD
Return:
XML document node with the following structure:
ReportBody
ReportName
ReportRow*
DocId
DocType
CheckedOutTo
WhenCheckedOut
LastActivity
ActionType
ActionWhen
"""
deltas = self.__opts.get("InactivityLength")
if not deltas:
raise Exception("Missing required 'InactivityLength' parameter")
try:
years, months, days = [int(digits) for digits in deltas.split("-")]
except:
message = "InactivityLength parameter must be in YYYY-MM-DD format"
raise Exception(message)
delta = relativedelta(years=years, months=months, days=days)
cutoff = (datetime.now() - delta).replace(microsecond=0)
subquery = Query("audit_trail", "MAX(dt)")
subquery.where("document = a.document")
fields = "d.id", "t.name", "c.dt_out", "u.name", "w.name", "a.dt"
query = Query("document d", *fields)
query.join("doc_type t", "t.id = d.doc_type")
query.join("checkout c", "c.id = d.id")
query.join("open_usr u", "u.id = c.usr")
query.join("audit_trail a", "a.document = d.id")
query.join("action w", "w.id = a.action")
query.where("c.dt_in IS NULL")
query.where(query.Condition("c.dt_out", cutoff, "<"))
query.where(query.Condition("a.dt", cutoff, "<"))
query.where(query.Condition("a.dt", subquery))
rows = query.order("c.id").execute(self.cursor).fetchall()
body = self.__start_body()
for id, doctype, locked, locker, what, when in rows:
wrapper = etree.SubElement(body, "ReportRow")
etree.SubElement(wrapper, "DocId").text = Doc.normalize_id(id)
etree.SubElement(wrapper, "DocType").text = doctype
etree.SubElement(wrapper, "CheckedOutTo").text = locker
etree.SubElement(wrapper, "WhenCheckedOut").text = str(locked)
last_activity = etree.SubElement(wrapper, "LastActivity")
etree.SubElement(last_activity, "ActionType").text = what
etree.SubElement(last_activity, "ActionWhen").text = str(when)
return body
def lockers(self):
"""Get sequence of id/name pairs for users with locked documents."""
if not hasattr(self, "_lockers"):
fields = "COUNT(*) AS n", "u.id", "u.name", "u.fullname"
query = Query("usr u", *fields)
query.join("checkout c", "c.usr = u.id")
query.join("document d", "d.id = c.id")
query.group(*fields[1:])
query.where("c.dt_in IS NULL")
users = []
for row in query.execute(self.cursor):
name = row.fullname or row.name
display = f"{name} ({row.n} locks)"
users.append((name.lower(), row.id, display))
self._lockers = [(uid, label) for key, uid, label in sorted(users)]
return self._lockers
def query(self):
"""
Assemble the database query object for performing the search
"""
# Use cached `Query` object if we've already done this.
if hasattr(self, "_query"):
return self._query
# Create a new `Query` object and apply any doctype filters.
query = Query("document d", "d.id").order("d.title")
if self.doctypes:
query.join("doc_type t", "t.id = d.doc_type")
if len(self.doctypes) == 1:
query.where(query.Condition("t.name", self.doctypes[0]))
else:
query.where(query.Condition("t.name", self.doctypes, "IN"))
# Apply the conditions for each of the `Test` objects.
n = 1
for test in self.tests:
# If the `Test` object specifies a column use the `query_term`
# table.
if test.column:
alias = f"qt{n:d}"
n += 1
query.join("query_term " + alias, alias + ".doc_id = d.id")
query.where(query.Condition(alias + ".path", test.path))
column = f"{alias}.{test.column}"
# Otherwise, the test is looking for matching title strings.
else:
column = "d.title"
# Construct and add a new `Condition` object to the query.
query.where(query.Condition(column, test.value, test.operator))
# If the caller doesn't want all the matching documents, apply the
# limit requested.
if self.limit:
query.limit(self.limit)
# Cache and return the `Query` object.
self._query = query
return query
def _term_sets(self):
"""
Collect named sets of CDR terms
Parameters:
SetType - optional string restricting report to term sets
whose type name matches the pattern passed
(any wildcards in the pattern must be included,
the report code does not wrap the string in a
pair of wildcards); as of this writing, this
parameter is of limited use, as all of the
existing term sets have the set type of
"diagnosis macro"
Return:
XML document node with the following structure:
ReportBody
ReportName
TermSet*
Name
Members [string of CDR term IDs]
"""
query = Query("doc_version v", "v.id", "MAX(v.num) AS num")
query.join("query_term_pub t", "t.doc_id = v.id")
query.where("t.path = '/TermSet/TermSetType'")
query.where("v.publishable = 'Y'")
query.group("v.id")
pattern = self.__opts.get("SetType")
if pattern:
query.where(query.Condition("t.value", pattern, "LIKE"))
body = self.__start_body()
for id, num in query.execute(self.cursor).fetchall():
doc = Doc(self.session, id=id, version=num)
result = doc.filter("name:Get TermSet Name and Members")
body.append(result.result_tree.getroot())
return body
def build_tables(self):
"""Show the documents the user has locked."""
fields = "c.dt_out", "t.name", "d.id", "d.title"
query = Query("usr u", *fields).order(*fields[:3])
query.join("checkout c", "c.usr = u.id")
query.join("document d", "d.id = c.id")
query.join("doc_type t", "t.id = d.doc_type")
query.where("c.dt_in IS NULL")
query.where(query.Condition("u.id", self.user.id))
rows = []
for dt_out, doc_type, doc_id, title in query.execute(self.cursor):
doc_id = Reporter.Cell(doc_id, center=True)
rows.append([str(dt_out)[:19], doc_type, doc_id, title])
caption = f"Checked out by {self.user.fullname or self.user.name}"
return Reporter.Table(rows, caption=caption, columns=self.COLUMNS)
def _locked_documents(self):
"""
Report on documents locked by specified user
Parameters:
UserId - required string identifying a user account;
this is the value often referred to as the 'name'
rather than the integer primary key for the row
in the usr table
Return:
XML document node with the following structure:
ReportBody
ReportName
ReportRow*
DocId
DocType
WhenCheckedOut
"""
user_name = self.__opts.get("UserId")
if not user_name:
raise Exception("Missing required 'UserId' parameter")
query = Query("document d", "d.id", "t.name", "c.dt_out")
query.join("doc_type t", "t.id = d.doc_type")
query.join("checkout c", "c.id = d.id")
query.join("open_usr u", "u.id = c.usr")
query.where("c.dt_in IS NULL")
query.where(query.Condition("u.name", user_name))
rows = query.order("c.id").execute(self.cursor).fetchall()
body = self.__start_body()
for id, doctype, locked in rows:
wrapper = etree.SubElement(body, "ReportRow")
etree.SubElement(wrapper, "DocId").text = Doc.normalize_id(id)
etree.SubElement(wrapper, "DocType").text = doctype
etree.SubElement(wrapper, "WhenCheckedOut").text = str(locked)
return body
def _menu_term_tree(self):
"""
Report on the menu term hierarchy
Parameters:
MenuType - required string restricting report to a single menu type
Return:
XML document node with the following structure:
ReportBody
ReportName
MenuItem*
TermId
TermName
MenuType
MenuStatus
DisplayName?
ParentId?
SortString
"""
menutype = self.__opts.get("MenuType", "%")
operand = "LIKE" if "%" in menutype else "="
fields = ("n.doc_id AS id", "n.value AS name", "t.value AS type",
"s.value AS status", "d.value AS display",
"p.int_val AS parent", "k.value AS sort_key")
i_path = "/Term/MenuInformation/MenuItem"
n_path = "/Term/PreferredName"
t_path = "{}/MenuType".format(i_path)
s_path = "{}/MenuStatus".format(i_path)
d_path = "{}/DisplayName".format(i_path)
p_path = "{}/MenuParent/@cdr:ref".format(i_path)
k_path = "{}/@SortOrder".format(i_path)
query = Query("query_term n", *fields).unique().order("n.doc_id")
query.join("query_term t", "t.doc_id = n.doc_id")
query.join("query_term s", "s.doc_id = t.doc_id",
"LEFT(s.node_loc, 8) = LEFT(t.node_loc, 8)")
query.outer("query_term d", "d.doc_id = t.doc_id",
"d.path = '{}'".format(d_path),
"LEFT(d.node_loc, 8) = LEFT(t.node_loc, 8)")
query.outer("query_term p", "p.doc_id = t.doc_id",
"p.path = '{}'".format(p_path),
"LEFT(p.node_loc, 8) = LEFT(t.node_loc, 8)")
query.outer("query_term k", "k.doc_id = t.doc_id",
"k.path = '{}'".format(k_path),
"LEFT(k.node_loc, 8) = LEFT(t.node_loc, 8)")
query.where("n.path = '{}'".format(n_path))
query.where("t.path = '{}'".format(t_path))
query.where("s.path = '{}'".format(s_path))
query.where(query.Condition("t.value", menutype, operand))
query.where(query.Condition("s.value", "Offline", "<>"))
body = self.__start_body()
for row in query.execute(self.cursor).fetchall():
sort_key = row.sort_key if row.sort_key is not None else row.name
wrapper = etree.SubElement(body, "MenuItem")
etree.SubElement(wrapper, "TermId").text = str(row.id)
etree.SubElement(wrapper, "TermName").text = row.name
etree.SubElement(wrapper, "MenuType").text = row.type
etree.SubElement(wrapper, "MenuStatus").text = row.status
if row.display is not None:
etree.SubElement(wrapper, "DisplayName").text = row.display
if row.parent is not None:
etree.SubElement(wrapper, "ParentId").text = str(row.parent)
etree.SubElement(wrapper, "SortString").text = sort_key
return body