def getprojectlastchange(database, id):
lists = []
if not cleanDatabasename(database):
return []
database = diversitydatabase(database)
mdate = getProjectLastChange(database, id)
return mdate
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 getTaxonNameListsForName(database, id):
namelist = []
if not cleanDatabasename(database):
return []
database = diversitydatabase(database)
namelist = getAllTaxonNameListForName(database, id)
return namelist
def getcommonname(database, nameid, commonname, languagecode, countrycode,
referencetitle):
cnamelist = []
if not cleanDatabasename(database):
return []
database = diversitydatabase(database)
namelist = getCommonName(database, nameid, commonname, languagecode,
countrycode, referencetitle)
return namelist
def getNameAllCommonNames(database, id):
if not cleanDatabasename(database):
return []
database = diversitydatabase(database)
#dbList = getDBs('DiversityTaxonNames')
#if not database in dbList:
#return None
lists = getTaxonNameAllCommonNames(database, id)
return lists
def getreferences(database):
referencelist = []
if not cleanDatabasename(database):
return []
database = diversitydatabase(database)
#dbList = getDBs('DiversityAgents')
#if not database in dbList:
#return None
referencelist = getReferences(database)
return referencelist
def getreferencerelation(database, id, role, sequence):
referencelist = []
if not cleanDatabasename(database):
return []
database = diversitydatabase(database)
#dbList = getDBs('DiversityAgents')
#if not database in dbList:
#return None
referencelist = getReferenceRelation(database, id, role, sequence)
return referencelist
def getListProject(database, id):
projecturi = None
if not cleanDatabasename(database):
return projecturi
database = diversitydatabase(database)
#dbList = getDBs('DiversityTaxonNames')
#if not database in dbList:
#return None
projecturi = getTaxonNameListsProjectUri(database, id)
return projecturi
def gettaxonhierarchy(database, projectid, nameid, ignore):
lists = []
if not cleanDatabasename(database):
return []
database = diversitydatabase(database)
#dbList = getDBs('DiversityTaxonNames')
#if not database in dbList:
#return None
lists = getTaxonHierarchy(database, projectid, nameid, ignore)
return lists
def getName(database, id):
namelist = []
if not cleanDatabasename(database):
return []
database = diversitydatabase(database)
# dbList = getDBs('DiversityTaxonNames')
# if not database in dbList:
# return None
namelist = getTaxonName(database, id)
return namelist
def getprojectlicense(database, id):
lists = []
if not cleanDatabasename(database):
return []
database = diversitydatabase(database)
# dbList = getDBs('DiversityProjects')
# if not database in dbList:
# return None
lists = getProjectLicense(database, id)
return lists
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 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 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 getprojectagentroles(database, id, agenthash):
lists = []
if not cleanDatabasename(database):
return []
database = diversitydatabase(database)
# dbList = getDBs('DiversityProjects')
# if not database in dbList:
# return None
agent = getProjectAgentByHash(database, id, agenthash)
for a in agent:
agentname = a['AgentName']
agenturl = a['AgentURI']
lists = getProjectAgentRoles(database, id, agentname, agenturl)
return lists
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 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 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 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 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
def gettaxonhierachyfull(database, projectid, nameid, ignore):
if not cleanDatabasename(database):
return []
database = diversitydatabase(database)
lists = getTaxonHierarchyFull(database, projectid, nameid, ignore)
return lists
