def getReferenceChildsAll(database, refid):
reflist = []
if not cleanDatabasename(database):
return []
database = diversitydatabase(database)
query = text('''with hierachy as (
select '%s' as DatabaseName, RefID as baseRefID, RefID, ParentRefID, 0 as level
from [%s].[dbo].[ReferenceTitle]
where RefID= :rpid
union all
select '%s' as DatabaseName, child.baseRefID, crefid.RefID, crefid.ParentRefID, child.level -1 as level
from hierachy as child
inner join [%s].[dbo].[ReferenceTitle] as crefid
on child.RefID = crefid.ParentRefID
)
select a.*
from hierachy a
order by baseRefID, level desc;''' %
(database, database, database, database))
current_app.logger.debug("Query %s with rpid=%s' " % (query, refid))
with get_db().connect() as conn:
treflist = conn.execute(query, rpid=refid)
if treflist != None:
reflist = R2L(treflist)
return reflist
def makeReferenceURI(database, id):
if isInt(id):
query = '''select concat([%s].[dbo].BaseURL() COLLATE Latin1_General_CI_AS , cast(%s as nvarchar) COLLATE Latin1_General_CI_AS) as ReferenceURI;''' % (
database, id)
current_app.logger.debug("Query %s " % (query))
with get_db().connect() as conn:
treflist = conn.execute(query)
if treflist != None:
reflist = treflist.fetchone()
return reflist['ReferenceURI']
return []
def findProjectsWithReference(database, referenceurl):
projectlists = []
if not cleanDatabasename(database):
return []
database=diversitydatabase(database)
query=''' select '%s' as DatabaseName, ProjectID from [%s].[dbo].[ProjectReference] where [ReferenceURI] = '%s'; ''' % (database, database, referenceurl)
current_app.logger.debug("Query %s with refuri = '%s'" % (query, referenceurl))
with get_db().connect() as conn:
plist = conn.execute(query, refuri=referenceurl)
if plist != None:
projectlists = R2L(plist)
return projectlists
def getProjectAgentRoles(database, projectid, agentname, agenturi):
agentrolelist = []
if not cleanDatabasename(database):
return []
query = text(f"select ProjectID, AgentName, AgentURI, AgentRole from [{database}].[dbo].[ProjectAgentRole] \
where ProjectID = :e1 and AgentName = :e2 and AgentURI = :e3")
current_app.logger.debug("Query %s " % (query))
with get_db().connect() as conn:
tagentrolelist = conn.execute(query, e1=projectid, e2=agentname, e3=agenturi)
if tagentrolelist != None:
agentrolelist=R2L(tagentrolelist)
return agentrolelist
def getProjectAgents(database, projectid):
agentlist=[]
if not cleanDatabasename(database):
return []
database=diversitydatabase(database)
query = ''' select ProjectID, AgentName, AgentURI, AgentRole, AgentSequence, Notes from [%s].[dbo].[ProjectAgent] \
where ProjectID=%s order by AgentSequence''' % (database, projectid)
current_app.logger.debug("Query %s " % (query))
with get_db().connect() as conn:
tagentlist = conn.execute(query)
if tagentlist != None:
agentlist=R2L(tagentlist)
return agentlist
def getProjectReferences(database, projectid):
referenceslist=[]
if not cleanDatabasename(database):
return []
database=diversitydatabase(database)
query = ''' select ProjectID, ReferenceTitle, ReferenceURI, ReferenceDetails, Notes, ReferenceType from [%s].[dbo].[ProjectReference] \
where ProjectID=%s''' % (database, projectid)
current_app.logger.debug("Query %s " % (query))
with get_db().connect() as conn:
tagentlist = conn.execute(query)
if tagentlist != None:
referenceslist=R2L(tagentlist)
return referenceslist
def getProjectLastChange(database, projectid):
mdate=''
if not cleanDatabasename(database):
return ''
database=diversitydatabase(database)
query=''' DECLARE @ProjectID int; DECLARE @lastmodification datetime = NULL; set @ProjectID=%s; EXECUTE [%s].[dbo].[procLastProjectModification] @ProjectID,@lastmodification OUTPUT; select @lastmodification;''' % (projectid,database)
current_app.logger.debug("Query %s " % (query))
with get_db().connect() as conn:
q = conn.execute(query)
if q:
v = q.fetchone()
current_app.logger.debug("Time %s " % (str(v)))
mdate = v[0].isoformat()
return mdate
def getReferenceRelations(database, refid):
reflist = []
if not cleanDatabasename(database):
return []
database = diversitydatabase(database)
query = ''' select '%s' as DatabaseName, RefID, Role, Sequence, Name
from [%s].[dbo].[ReferenceRelator] \
where RefID=%s ''' % (database, database, refid)
current_app.logger.debug("Query %s " % (query))
with get_db().connect() as conn:
treflist = conn.execute(query)
if treflist != None:
reflist = R2L(treflist)
return reflist
def getReferences(database):
reflist = []
if not cleanDatabasename(database):
return []
database = diversitydatabase(database)
query = ''' select '%s' as DatabaseName, RefID
from [%s].[dbo].[ReferenceTitle] \
''' % (database, database)
current_app.logger.debug("Query %s " % (query))
with get_db().connect() as conn:
treflist = conn.execute(query)
if treflist != None:
reflist = R2L(treflist)
return reflist
def getReferenceChilds(database, refid):
reflist = []
if not cleanDatabasename(database):
return []
database = diversitydatabase(database)
query = text(''' select '%s' as DatabaseName, RefID, ParentRefID \
from [%s].[dbo].[ReferenceTitle] \
where ParentRefID = :rpid ''' % (database, database))
current_app.logger.debug("Query %s with rpid=%s' " % (query, refid))
with get_db().connect() as conn:
treflist = conn.execute(query, rpid=refid)
if treflist != None:
reflist = R2L(treflist)
return reflist
def getAgentRelations(database, agentid):
agentlist = []
if not cleanDatabasename(database):
return []
database = diversitydatabase(database)
query = ''' select '%s' as DatabaseName, AgentID, RelatedAgentID, RelationType
from [%s].[dbo].[AgentRelation] \
where AgentID=%s ''' % (database, database, agentid)
current_app.logger.debug("Query %s " % (query))
with get_db().connect() as conn:
tagentlist = conn.execute(query)
if tagentlist != None:
agentlist = R2L(tagentlist)
return agentlist
def getAgents(database):
agentlist = []
if not cleanDatabasename(database):
return []
database = diversitydatabase(database)
query = ''' select '%s' as DatabaseName, AgentID
from [%s].[dbo].[Agent] where DataWithholdingReason is Null \
''' % (database, database)
current_app.logger.debug("Query %s " % (query))
with get_db().connect() as conn:
tagentlist = conn.execute(query)
if tagentlist != None:
agentlist = R2L(tagentlist)
return agentlist
def getProject(database, projectid):
projectlist=[]
if not cleanDatabasename(database):
return []
database=diversitydatabase(database)
query = ''' select a.ProjectID, a.ProjectParentID, a.Project, a.ProjectTitle, a.ProjectDescription, \
a.ProjectEditors, a.ProjectInstitution, a.ProjectNotes, a.ProjectCopyright, a.ProjectURL, \
a.ProjectSettings, a.ProjectRights, a.ProjectLicenseURI \
from [%s].dbo.[Project] a \
where a.ProjectID=%s''' % (database, projectid)
current_app.logger.debug("Query %s " % (query))
with get_db().connect() as conn:
tprojectlist = conn.execute(query)
if tprojectlist != None:
projectlist=R2L(tprojectlist)
return projectlist
def getProjectLicense(database, projectid):
licenselist = []
if not cleanDatabasename(database):
return []
database=diversitydatabase(database)
query = ''' select b.LicenseID, b.ProjectID, b.DisplayText as LicenseDisplayText, b.LicenseURI, b.LicenseType, b.LicenseHolder, \
b.LicenseHolderAgentURI, b.LicenseYear, b.Context as LicenseContext, \
b.IPR, b.IPRHolder, b.IPRHolderAgentURI, b.CopyrightStatement, b.CopyrightHolder, \
b.CopyrightHolderAgentUri, b.Notes as LicenseNotes \
from [%s].dbo.[ProjectLicense] b \
where b.ProjectID=%s''' % (database, projectid)
current_app.logger.debug("Query %s " % (query))
with get_db().connect() as conn:
tlicenselist = conn.execute(query)
if tlicenselist != None:
licenselist=R2L(tlicenselist)
return licenselist
def getReference(database, refid):
reflist = []
if not cleanDatabasename(database):
return []
database = diversitydatabase(database)
query = ''' select '%s' as DatabaseName, RefID, RefType, RefDescription_Cache, Title, DateYear, \
DateMonth, DateDay, DateSuppl, SourceTitle, SeriesTitle, Periodical, Volume, Issue, Pages, Publisher, \
PublPlace, Edition, ISSN_ISBN, Miscellaneous1, Miscellaneous2, Miscellaneous3, Weblinks, LinkToPDF, UserDef1, UserDef2, UserDef3, ParentRefID, \
Language, [%s].[dbo].RefAutoDescription_2(RefID) as fullref \
from [%s].[dbo].[ReferenceTitle] \
where RefID='%s' ''' % (database, database, database, refid)
current_app.logger.debug("Query %s " % (query))
with get_db().connect() as conn:
treflist = conn.execute(query)
if treflist != None:
reflist = R2L(treflist)
return reflist
def getAgent(database, agentid):
agentlist = []
if not cleanDatabasename(database):
return []
database = diversitydatabase(database)
query = ''' select '%s' as DatabaseName, AgentID, AgentParentID, AgentName, Version, AgentTitle, \
GivenName, InheritedName, \
Abbreviation, AgentType, AgentGender, Description, \
RevisionLevel
from [%s].[dbo].[Agent] \
where AgentID=%s and DataWithholdingReason is Null''' % (
database, database, agentid)
current_app.logger.debug("Query %s " % (query))
with get_db().connect() as conn:
tagentlist = conn.execute(query)
if tagentlist != None:
agentlist = R2L(tagentlist)
return agentlist