def part_prj_vpgraph(PrjId,offset):
Prj=GetAll("""select pp.* from part_projects pp where pprojid=%s""",(PrjId,))
if len(Prj)!=1:
return PrintInCharte(ErrorFormat("Project doesn't exists"))
Prj=Prj[0]
if Prj['ownerid']!=current_user.id and not current_user.has_role(database.AdministratorLabel):
return PrintInCharte(ErrorFormat("Access Denied"))
g.headcenter="<h4>Particle Project %s : %s</h4><a href='/part/prj/%s'>Project home</a>"%(Prj['projid'],Prj['ptitle'],Prj['pprojid'],)
dbsample = database.GetAll("""select psampleid,filename from part_samples s where pprojid=%s
ORDER BY filename desc limit 50 OFFSET %s
""" % (PrjId,offset))
txt="""<style>
.idepth,.ipart {height: auto}
</style>
<button class='btn' onclick='dozoom(50);'>50%</button>
<button class='btn' onclick='dozoom(75);'>75%</button>
<button class='btn' onclick='dozoom(100);'>100%</button>
<script>
function dozoom(z) {
$('.idepth').css('width',(800*z/100).toFixed().toString()+"px");
$('.ipart').css('width',(1600*z/100).toFixed().toString()+"px");
}
</script>
"""
for s in dbsample:
txt+="""<p class='ghead'>Graph for {psampleid} - {filename} - </p>
<img src='/vault/{idepth}' class='idepth'> <img src='/vault/{ipart}' class='ipart'>
""".format(psampleid=s['psampleid'],filename=s['filename']
,idepth=uvp_sample_import.GetPathForImportGraph(s['psampleid'],'depth',True)
,ipart =uvp_sample_import.GetPathForImportGraph(s['psampleid'],'particle',True))
return PrintInCharte(txt)
def part_prj_main(PrjId):
# Prj = partdatabase.part_projects.query.filter_by(pprojid=PrjId).first()
Prj=GetAll("""select pp.*
,oldestsampledate+make_interval(0,public_visibility_deferral_month) visibility_date
,oldestsampledate+make_interval(0,public_partexport_deferral_month) partexport_date
,oldestsampledate+make_interval(0,public_zooexport_deferral_month) zooexport_date
from part_projects pp where pprojid=%s""",(PrjId,))
if len(Prj)!=1:
return PrintInCharte(ErrorFormat("Project doesn't exists"))
Prj=Prj[0]
if Prj['ownerid']!=current_user.id and not current_user.has_role(database.AdministratorLabel):
return PrintInCharte(ErrorFormat("Access Denied"))
g.headcenter="<h4>Particle Project %s : %s</h4><a href='/part/'>Particle Module Home</a>"%(Prj['projid'],Prj['ptitle'])
dbsample = database.GetAll("""select profileid,psampleid,filename,stationid,firstimage,lastimg,lastimgused,sampleid
,histobrutavailable,comment,daterecalculhistotaxo,ctd_import_datetime,sampledate,imp_descent_filtered_row,imp_removed_empty_slice
,(select count(*) from part_histopart_det where psampleid=s.psampleid) nbrlinedet
,(select count(*) from part_histopart_reduit where psampleid=s.psampleid) nbrlinereduit
,(select count(*) from part_histocat where psampleid=s.psampleid) nbrlinetaxo
,(select count(*) from part_ctd where psampleid=s.psampleid) nbrlinectd
from part_samples s
where pprojid=%s
ORDER BY filename desc
""" % (PrjId))
MinSampleDate=Prj['oldestsampledate']
VisibilityText=""
if MinSampleDate is not None :
VisibilityText +="""<br> Oldest sample date is {0:%Y-%m-%d}, Visibility date is {1}
, Particule export date is {2}, Zooplankton classification export date is {3}
""".format(MinSampleDate
, "Not Defined" if Prj['visibility_date'] is None else Prj['visibility_date'].strftime("%Y-%m-%d")
, "Not Defined" if Prj['partexport_date'] is None else Prj['partexport_date'].strftime("%Y-%m-%d")
, "Not Defined" if Prj['zooexport_date'] is None else Prj['zooexport_date'].strftime("%Y-%m-%d") )
return PrintInCharte(
render_template('part/prj_index.html', PrjId=PrjId, dbsample=dbsample, Prj=Prj,VisibilityText=VisibilityText))
def PrjEditPriv(PrjId):
Prj=database.Projects.query.filter_by(projid=PrjId).first()
g.headcenter="<h4><a href='/prj/{0}'>{1}</a></h4>".format(Prj.projid,XSSEscape(Prj.title))
if Prj is None:
flash("Project doesn't exists",'error')
return PrintInCharte("<a href=/prj/>Select another project</a>")
if not Prj.CheckRight(2): # Level 0 = Read, 1 = Annotate, 2 = Admin
flash('You cannot edit settings for this project','error')
return PrintInCharte("<a href=/prj/>Select another project</a>")
if gvp('save')=="Y":
# print(request.form)
for m in Prj.projmembers:
if gvp('priv_%s_delete'%m.id)=='Y':
db.session.delete(m)
elif gvp('priv_%s_member'%m.id)!='': # si pas delete c'est update
m.member=int(gvp('priv_%s_member'%m.id))
m.privilege=gvp('priv_%s_privilege'%m.id)
if gvp('priv_new_member')!='':
new=database.ProjectsPriv(member=int(gvp('priv_new_member')),privilege=gvp('priv_new_privilege'),projid=PrjId)
db.session.add(new)
try:
db.session.commit()
flash("Project settings Saved successfuly","success")
except Exception as E:
flash("Database exception : %s"%E,"error")
db.session.rollback()
g.users=GetAssoc2Col("select id,name from users order by lower(name)",dicttype=collections.OrderedDict)
return render_template('project/editprojectpriv.html',data=Prj)
def PrjEdit(PrjId):
g.useselect4 = True
Prj = database.Projects.query.filter_by(projid=PrjId).first()
g.headcenter = "<h4><a href='/prj/{0}'>{1}</a></h4>".format(
Prj.projid, XSSEscape(Prj.title))
if Prj is None:
flash("Project doesn't exists", 'error')
return PrintInCharte("<a href=/prj/>Select another project</a>")
if not Prj.CheckRight(2): # Level 0 = Read, 1 = Annotate, 2 = Admin
flash('You cannot edit settings for this project', 'error')
return PrintInCharte("<a href=/prj/>Select another project</a>")
if gvp('save') == "Y":
PreviousCNN = Prj.cnn_network_id
for f in request.form:
if f in dir(Prj):
setattr(Prj, f, gvp(f))
if PreviousCNN != Prj.cnn_network_id:
database.ExecSQL(
"delete from obj_cnn_features where objcnnid in (select objid from obj_head where projid=%s)",
[PrjId])
flash("SCN features erased", "success")
Prj.visible = True if gvp('visible') == 'Y' else False
# print(request.form)
for m in Prj.projmembers:
if gvp('priv_%s_delete' % m.id) == 'Y':
db.session.delete(m)
elif gvp('priv_%s_member' %
m.id) != '': # si pas delete c'est update
m.member = int(gvp('priv_%s_member' % m.id))
m.privilege = gvp('priv_%s_privilege' % m.id)
if gvp('priv_new_member') != '':
new = database.ProjectsPriv(member=int(gvp('priv_new_member')),
privilege=gvp('priv_new_privilege'),
projid=PrjId)
db.session.add(new)
try:
db.session.commit()
flash("Project settings Saved successfuly", "success")
except Exception as E:
flash("Database exception : %s" % E, "error")
db.session.rollback()
if Prj.initclassiflist is None:
lst = []
else:
lst = [int(x) for x in Prj.initclassiflist.split(",") if x.isdigit()]
g.predeftaxo = GetAll(
"""select t.id,t.display_name as name
from taxonomy t
left join taxonomy t2 on t.parent_id=t2.id
where t.id= any(%s) order by upper(t.display_name) """, (lst, ))
g.users = GetAssoc2Col("select id,name from users order by lower(name)",
dicttype=collections.OrderedDict)
g.maplist = [
'objtime', 'objdate', 'latitude', 'longitude', 'depth_min', 'depth_max'
] + sorted(DecodeEqualList(Prj.mappingobj).values())
g.scn = GetSCNNetworks()
return render_template('project/editproject.html', data=Prj)
def routetaxobrowse():
BackProjectBtn = ''
if gvp('updatestat') == 'Y':
# DoSyncStatUpdate()
DoFullSync()
if gvg('fromprj'):
BackProjectBtn = "<a href='/prj/{}' class='btn btn-default btn-primary'>{} Back to project</a> ".format(
int(gvg('fromprj')), FAIcon('arrow-left'))
if gvg('fromtask'):
BackProjectBtn = "<a href='/Task/Question/{}' class='btn btn-default btn-primary'>{} Back to importation task</a> ".format(
int(gvg('fromtask')), FAIcon('arrow-left'))
if not (current_user.has_role(database.AdministratorLabel)
or current_user.has_role(database.ProjectCreatorLabel)):
# /prj/653
txt = "You cannot create tanonomy category, you must request to your project manager (check project page)"
if gvg('fromprj'):
txt += "<br>" + BackProjectBtn
return PrintInCharte(FormatError(txt, DoNotEscape=True))
g.taxoserver_url = app.config.get('TAXOSERVER_URL')
if current_user.has_role(database.AdministratorLabel):
ExtraWehereClause = ""
else:
ExtraWehereClause = "and t.creator_email='{}'".format(
current_user.email)
lst = GetAll(
"""select t.id,t.parent_id,t.display_name as name,case t.taxotype when 'M' then 'Morpho' when 'P' then 'Phylo' else t.taxotype end taxotype,t.taxostatus,t.creator_email,t.id_source
,to_char(t.creation_datetime,'yyyy-mm-dd hh24:mi') creation_datetime,to_char(t.lastupdate_datetime,'yyyy-mm-dd hh24:mi') lastupdate_datetime,{}
from taxonomy t
{}
where t.id_instance ={} {}
order by case t.taxostatus when 'N' then 1 else 2 end,t.id
LIMIT 400
""".format(SQLTreeSelect, SQLTreeJoin,
app.config.get('TAXOSERVER_INSTANCE_ID'), ExtraWehereClause))
for lstitem in lst:
# lstitem['tree']=PackTreeTxt(lstitem['tree']) #evite les problèmes de safe
if lstitem['parent_id'] is None:
lstitem['parent_id'] = ""
# nbrtaxon=GetAll("select count(*) from taxonomy")[0][0]
# return render_template('browsetaxo.html',lst=lst,nbrtaxon=nbrtaxon)
return PrintInCharte(
render_template('taxonomy/browse.html',
lst=lst,
BackProjectBtn=BackProjectBtn))
def dbadmin_viewsizes():
g.headcenter = "Database objects size (public schema only)<br><a href=/admin/>Back to admin home</a>"
sql = """SELECT c.relname, c.relkind, CASE WHEN c.relkind='i' THEN c2.tablename ELSE c.relname END fromtable,pg_relation_size(('"' || c.relname || '"')::regclass)/(1024*1024) szMB
FROM
pg_namespace ns,
pg_class c LEFT OUTER JOIN
pg_indexes c2 ON c.relname = c2.indexname
WHERE c.relnamespace = ns.oid
AND ns.nspname = 'public'
AND c.relkind IN ('r' ,'i')
ORDER BY c.relkind DESC, pg_relation_size(('"' || c.relname || '"')::regclass) DESC
"""
res = GetAll(sql) #,debug=True
txt = """<table class='table table-bordered table-condensed table-hover' style="width:500px;">
<tr><th width=200>Object</td><th witdth=200>Table</td><th width=100>Size (Mb)</td></tr>"""
for r in res:
txt += """<tr><td>{0}</td>
<td>{2}</td>
<td>{3}</td>
</tr>""".format(*r)
txt += "</table>"
return PrintInCharte(txt)
def part_prj():
params={}
sql="""select pprojid,ptitle,up.ownerid,u.name,u.email,rawfolder,instrumtype,ep.title
,(select count(*) from part_samples where pprojid=up.pprojid) samplecount
from part_projects up
left JOIN projects ep on up.projid=ep.projid
LEFT JOIN users u on ownerid=u.id
"""
sql += " where 1=1 "
if not current_user.has_role(database.AdministratorLabel):
sql+=" and ownerid=%d"%(current_user.id,)
if gvg('filt_title','')!='':
sql +=" and ( up.ptitle ilike '%%'||%(title)s ||'%%' or to_char(up.pprojid,'999999') like '%%'||%(title)s or ep.title ilike '%%'||%(title)s ||'%%' or to_char(ep.projid,'999999') like '%%'||%(title)s) "
params['title']=gvg('filt_title')
if gvg('filt_instrum','')!='':
sql +=" and up.instrumtype ilike '%%'||%(filt_instrum)s ||'%%' "
params['filt_instrum']=gvg('filt_instrum')
sql+=" order by lower(ep.title),lower(ptitle)"
res = GetAll(sql,params) #,debug=True
# app.logger.info("res=%s",res)
CanCreate=False
if current_user.has_role(database.AdministratorLabel) or current_user.has_role(database.ProjectCreatorLabel):
CanCreate=True
g.headcenter = "<h4>Particle Projects management</h4><a href='/part/'>Particle Module Home</a>"
return PrintInCharte(
render_template('part/list.html', PrjList=res, CanCreate=CanCreate, AppManagerMailto=appli.GetAppManagerMailto()
, filt_title=gvg('filt_title'), filt_subset=gvg('filt_subset'), filt_instrum=gvg('filt_instrum')))
def QuestionProcess(self):
txt = "<h1>Test Task</h1>"
if self.task.taskstep == 0:
txt += "<h3>Task Creation</h3>"
if gvp('starttask') == "Y":
for k, v in self.param.__dict__.items():
setattr(self.param, k, gvp(k))
# Verifier la coherence des données
if (len(self.param.InData) < 5):
flash("Champ In Data trop court", "error")
else:
return self.StartTask(self.param)
return render_template('task/testcreate.html',
header=txt,
data=self.param)
if self.task.taskstep == 1:
txt += "<h3>Task Question 1</h3>"
if gvp('starttask') == "Y":
self.param.InData2 = gvp("InData2")
# Verifier la coherence des données
if (len(self.param.InData2) < 5):
flash("Champ In Data 2 trop court", "error")
else:
return self.StartTask(self.param, step=2)
return render_template('task/testquestion1.html',
header=txt,
data=self.param)
return PrintInCharte(txt)
def admin_edithomemessage():
g.headcenter = "Edit home page message<br><a href='/admin/'>Back to admin home</a>"
txt = "<script src='//cdn.ckeditor.com/4.6.0/full/ckeditor.js'></script>"
message = ""
NomFichier = 'appli/static/home/appmanagermsg.html'
if gvp("msg", None) is not None:
with open(NomFichier, 'w', encoding='utf-8', newline="\n") as f:
f.write(gvp("msg"))
if os.path.exists(NomFichier):
with open(NomFichier, 'r', encoding='utf-8') as f:
message = f.read()
txt += """<form action=? method=post>
Enter bellow the message you want display :
<button type=button class='btn btn-default btn-sm' onclick='EnableCK()'>Enable HTML Editor</button><br>
<textarea name=msg style='width:1000px;height:200px;'>{0}</textarea>
<br><button type=submit class='btn btn-primary'>Save</button>
</form>
<script>
function EnableCK() {{
CKEDITOR.replace( 'msg' );
}}
</script>
""".format(message)
return PrintInCharte(txt)
def part_sampleedit(psampleid):
model = partdatabase.part_samples.query.filter_by(
psampleid=psampleid).first()
form = UvpSampleForm(request.form, model)
if gvp('delete') == 'Y':
delete_sample(psampleid)
return redirect("/part/prj/" + str(model.pprojid))
if request.method == 'POST' and form.validate():
for k, v in form.data.items():
setattr(model, k, v)
db.session.commit()
if gvp('forcerecalc') == 'Y':
appli.part.prj.ComputeHistoDet(model.psampleid,
model.project.instrumtype)
appli.part.prj.ComputeHistoRed(model.psampleid,
model.project.instrumtype)
appli.part.prj.ComputeZooMatch(model.psampleid,
model.project.projid)
flash("Histograms have been recomputed", "success")
return redirect("/part/prj/" + str(model.pprojid))
return PrintInCharte(
render_template("part/sampleedit.html",
form=form,
prjid=model.pprojid,
psampleid=model.psampleid))
def dbadmin_console():
sql = gvp("sql")
if len(
request.form
) > 0 and request.referrer != request.url: # si post doit venir de cette page
return PrintInCharte("Invalid referer")
g.headcenter = "<font color=red style='font-size:18px;'>Warning : This screen must be used only by experts</font><br><a href=/admin/>Back to admin home</a>"
txt = "<form method=post>SQL : <textarea name=sql rows=15 cols=100>%s</textarea><br>" % escape(
sql)
txt += """<input type=submit class='btn btn-primary' name=doselect value='Execute Select'>
<input type=submit class='btn btn-primary' name=dodml value='Execute DML'>
Note : For DML ; can be used, but only the result of the last query displayed
</form>"""
if gvp("doselect"):
txt += "<br>Select Result :"
cur = db.engine.raw_connection().cursor()
try:
cur.execute(sql)
txt += "<table class='table table-condensed table-bordered'>"
for c in cur.description:
txt += "<td>%s</td>" % c[0]
for r in cur:
s = "<tr>"
for c in r:
s += "<td>%s</td>" % c
txt += s + "</tr>"
txt += "</table>"
except Exception as e:
txt += "<br>Error = %s" % e
cur.connection.rollback()
finally:
cur.close()
if gvp("dodml"):
txt += "<br>DML Result :"
cur = db.engine.raw_connection().cursor()
try:
cur.execute(sql)
txt += "%s rows impacted" % cur.rowcount
cur.connection.commit()
except Exception as e:
txt += "<br>Error = %s" % e
cur.connection.rollback()
finally:
cur.close()
return PrintInCharte(txt)
def part_prjcalc(PrjId):
Prj = partdatabase.part_projects.query.filter_by(pprojid=PrjId).first()
if Prj.ownerid!=current_user.id and not current_user.has_role(database.AdministratorLabel):
return PrintInCharte(ErrorFormat("Access Denied"))
txt=""
CheckedSampleList=[]
for f in request.form:
if f[0:2] == "s_" and request.form.get(f)=='Y':
CheckedSampleList.append(int(f[2:]))
app.logger.info("request.form=%s", request.form)
app.logger.info("CheckedSampleList=%s", CheckedSampleList)
app.logger.info("dohistodet=%s,domatchecotaxa=%s,dohistotaxo=%s", gvp('dohistodet'), gvp('domatchecotaxa'), gvp('dohistotaxo'))
dbsample = database.GetAll("""select profileid,psampleid,filename,sampleid,histobrutavailable
,(select count(*) from part_histopart_det where psampleid=s.psampleid) nbrlinedet
from part_samples s
where pprojid=%s and psampleid = any (%s)""" , (PrjId,CheckedSampleList))
for S in dbsample:
prefix="<br>{profileid} :".format(**S)
if gvp('delete') == 'Y':
sampleedit.delete_sample(S['psampleid'])
txt += prefix + " deleted"
continue
if gvp('dohistodet')=='Y':
txt += prefix + ComputeHistoDet(S['psampleid'], Prj.instrumtype)
if gvp('dohistored')=='Y':
txt += prefix + ComputeHistoRed(S['psampleid'], Prj.instrumtype)
if gvp('domatchecotaxa') == 'Y':
txt += prefix +ComputeZooMatch(S['psampleid'],Prj.projid)
if gvp('dohistotaxo') == 'Y':
txt += prefix + ComputeZooHisto(S['psampleid'])
# try:
# uvp_sample_import.GenerateTaxonomyHistogram(S['psampleid'])
# txt += prefix + " Taxonomy Histogram computed"
# except Exception as E:
# txt += prefix + " <span style='color: red;'>Taxonomy Histogram can't be computed : %s </span>"%(E)
if gvp('doctdimport') == 'Y':
if common_import.ImportCTD(S['psampleid'],current_user.name,current_user.email):
txt += prefix + " CTD imported"
else:
txt += prefix + " <span style='color: red;'>CTD No file</span>"
#
# txt+="CheckedSampleList=%s"%(CheckedSampleList)
# txt+="<br>dbsample = %s"%(dbsample)
txt += "<br><br><a href=/part/prj/%s class='btn btn-primary'><span class='glyphicon glyphicon-arrow-left'></span> Back to project samples list</a>"%PrjId
return PrintInCharte(txt)
def ShowCustomDetails(self):
txt = "<h3>Import Task details view</h3>"
txt = "<p><u>Used mapping, usable for next import</u></p>"
taxo = database.GetAssoc2Col(
"select id,name from taxonomy where id = any(%s)",
(list(set(self.param.TaxoFound.values())), ))
for k, v in self.param.TaxoFound.items():
if v in taxo:
txt += "{0}={1}<br>".format(k, taxo[v])
return PrintInCharte(txt)
def TaskMonitor(TaskID):
AddTaskSummaryForTemplate()
try:
task = LoadTask(TaskID)
ProjectID = getattr(task.param, 'ProjectId', None)
if ProjectID:
Prj = database.Projects.query.filter_by(projid=ProjectID).first()
g.headcenter = "<h4>Project : <a href='/prj/{0}'>{1}</a></h4>".format(
Prj.projid, XSSEscape(Prj.title))
return render_template('task/monitor.html', TaskID=task.task.id)
except:
return PrintInCharte(
"This task doesn't exists anymore, peraphs it was automaticaly purged"
)
def TaskClean(TaskID):
AddTaskSummaryForTemplate()
if gvg('thengotoproject') == 'Y':
task = LoadTask(TaskID)
ProjectID = getattr(task.param, 'ProjectId', None)
else:
ProjectID = ''
Msg = DoTaskClean(TaskID)
Msg += '<br><a href="/Task/listall"><span class="label label-info"> Back to Task List</span></a>'
if ProjectID:
Msg += """"<script>
window.location.href = "/prj/%s"
</script>""" % (ProjectID, )
return PrintInCharte(Msg)
def QuestionProcess(self):
g.headcenter = "<h3>TAXONOMY IMPORTATION</h3><a href='/admin'/>Back to admin home</a>"
txt = "<h1>Taxonomy Text File Importation Task</h1>"
errors = []
if self.task.taskstep == 0:
# txt+="<h3>Task Creation</h3>"
if not current_user.has_role(database.AdministratorLabel):
return PrintInCharte(
"ACCESS DENIED reserved to administrators")
if gvp('starttask') == "Y":
FileToSave = None
FileToSaveFileName = None
# Verifier la coherence des données
uploadfile = request.files.get("uploadfile")
if uploadfile is not None and uploadfile.filename != '': # import d'un fichier par HTTP
FileToSave = uploadfile # La copie est faite plus tard, car à ce moment là, le repertoire de la tache n'est pas encore créé
FileToSaveFileName = "uploaded.txt"
self.param.InData = "uploaded.txt"
else:
errors.append("txt file is missing")
if len(errors) > 0:
for e in errors:
flash(e, "error")
else:
return self.StartTask(
self.param,
FileToSave=FileToSave,
FileToSaveFileName=FileToSaveFileName)
else: # valeurs par default
pass
return render_template('task/taxoimport_create.html',
header=txt,
data=self.param,
ServerPath=gvp("ServerPath"),
TxtTaxoMap=gvp("TxtTaxoMap"))
return PrintInCharte(txt)
def dbadmin_viewtaxoerror():
g.headcenter="Database Taxonomy errors<br><a href=/admin/>Back to admin home</a>"
sql="""Select 'Missing parent' reason,t.id,t.parent_id,t.name,t.id_source
from taxonomy t where parent_id not in (select id from taxonomy);
"""
cur = db.engine.raw_connection().cursor()
try:
txt="<table class='table table-bordered table-condensed table-hover'>"
cur.execute(sql)
txt+="<tr><td>"+("</td><td>".join([x[0] for x in cur.description]))+"</td></tr>"
for r in cur:
txt+="<tr><td>"+("</td><td>".join([str(x) for x in r]))+"</td></tr>"
txt+="</table>"
finally:
cur.close()
return PrintInCharte(txt)
def dbadmin_merge2taxon():
if gvg("src","")=="" or gvg("dest","")=="":
txt="Select source Taxon (will be deleted after merge) :"
txt+="<br>Select Target Taxon :"
return render_template('search/merge2taxo.html')
TaxoSrc=database.Taxonomy.query.filter_by(id=int(gvg("src",""))).first()
TaxoDest=database.Taxonomy.query.filter_by(id=int(gvg("dest",""))).first()
N1=ExecSQL("update obj_head set classif_id=%(dest)s where classif_id=%(src)s",{"src":TaxoSrc.id,"dest":TaxoDest.id})
N2=ExecSQL("update obj_head set classif_auto_id=%(dest)s where classif_auto_id=%(src)s",{"src":TaxoSrc.id,"dest":TaxoDest.id})
N3=ExecSQL("update objectsclassifhisto set classif_id=%(dest)s where classif_id=%(src)s",{"src":TaxoSrc.id,"dest":TaxoDest.id})
N4=ExecSQL("update taxonomy set parent_id=%(dest)s where parent_id=%(src)s",{"src":TaxoSrc.id,"dest":TaxoDest.id})
N5=ExecSQL("delete from taxonomy where id=%(src)s",{"src":TaxoSrc.id,"dest":TaxoDest.id})
return PrintInCharte("""Merge of '%s' in '%s' done
<br>%d Objects Manuel classification updated
<br>%d Objects Automatic classification updated
<br>%d Objects classification historical updated
<br>%d Taxonomy child updated
<br>%d Taxonomy Node deleted
"""%(TaxoSrc.name,TaxoDest.name,N1,N2,N3,N4,N5))
def index():
txt = """<div style='margin:5px;'><div id="homeText"'>"""
#lecture du message de l'application manager
NomFichier = 'appli/static/home/appmanagermsg.html'
if os.path.exists(NomFichier):
with open(NomFichier, 'r', encoding='utf8') as f:
message = f.read()
if len(message) > 5:
txt += """
<div class="alert alert-warning alert-dismissable" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">×</span></button>
<p><strong>Message from the application manager</strong></p>{0}
</div>
""".format(message)
# Lecture de la partie Haute
NomFichier = 'appli/static/home/home.html'
if not os.path.exists(NomFichier):
NomFichier = 'appli/static/home/home-model.html'
with open(NomFichier, 'r', encoding='utf8') as f:
txt += f.read()
txt += """
</div>
<div class="row" id="homeSelectors" style="margin-top: 20px; margin-bottom: 20px;">
<div class="col-sm-4">
<a href="/explore/" class="btn btn-primary btn-lg btn-block">Explore images</a>
</div>
<div class="col-sm-4">
<a href="/prj/" class="btn btn-primary btn-lg btn-block">Contribute to a project</a>
</div>
<div class="col-sm-4">
<a href="/part/" class="btn btn-primary btn-lg btn-block">Particle module</a>
</div>
</div>
"""
NomFichier = 'appli/static/home/homebottom.html'
if not os.path.exists(NomFichier):
NomFichier = 'appli/static/home/homebottom-model.html'
txt += """<div class="row" id="homeLegal"><div class="col-sm-12">"""
with open(NomFichier, 'r', encoding='utf8') as f:
txt += f.read()
txt += """<br><a href='/privacy'>Privacy</a></div></div></div>"""
return PrintInCharte(txt)
def QuestionProcess(self):
txt = "<h1>Taxonomy Import Task</h1>"
if self.task.taskstep == 0:
txt += "<h3>Task Creation</h3>"
if gvp('starttask') == "Y":
for k, v in self.param.__dict__.items():
setattr(self.param, k, gvp(k))
if len(self.param.host) < 7:
flash("Host Field Too short", "error")
elif len(self.param.port) < 2:
flash("Port Field Too short", "error")
elif len(self.param.database) < 2:
flash("database Field Too short", "error")
else:
return self.StartTask(self.param)
return render_template('task/taxosynccreate.html',
header=txt,
data=self.param)
return PrintInCharte(txt)
def index():
txt = """<div style='margin:5px;'><div id="homeText"'>"""
#lecture du message de l'application manager
NomFichier = 'appli/static/home/appmanagermsg.html'
if os.path.exists(NomFichier):
with open(NomFichier, 'r', encoding='utf8') as f:
message = f.read()
if len(message) > 5:
txt += """
<div class="alert alert-warning alert-dismissable" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">×</span></button>
<p><strong>Message from the application manager</strong></p>{0}
</div>
""".format(message)
# Lecture de la partie Haute
NomFichier = 'appli/static/home/home.html'
if not os.path.exists(NomFichier):
NomFichier = 'appli/static/home/home-model.html'
with open(NomFichier, 'r', encoding='utf8') as f:
txt += f.read()
# txt+="""<br><a href='/privacy'>Privacy</a></div></div></div>"""
return PrintInCharte(txt)
def indexPart():
class FiltForm(Form):
filt_proj = SelectMultipleField(choices=[['', '']] + database.GetAll(
"SELECT projid,concat(title,' (',cast(projid AS VARCHAR),')') FROM projects where projid in (select projid from part_projects) ORDER BY lower(title)"
))
filt_uproj = SelectMultipleField(choices=[['', '']] + database.GetAll(
"SELECT pprojid,concat(ptitle,' (',cast(pprojid AS VARCHAR),')') FROM part_projects ORDER BY lower(ptitle)"
))
gpr = SelectMultipleField(
choices=[("cl%d" % i, "# l-1 %02d : " % i +
GetClassLimitTxt(PartRedClassLimit, i))
for i in range(1, 16)] +
[("bv%d" % i,
"BV %02d : " % i + GetClassLimitTxt(PartRedClassLimit, i))
for i in range(1, 16)])
gpd = SelectMultipleField(
choices=[("cl%d" % i, "# l-1 %02d : " % i +
GetClassLimitTxt(PartDetClassLimit, i))
for i in range(1, 46)] +
[("bv%d" % i,
"BV %02d : " % i + GetClassLimitTxt(PartDetClassLimit, i))
for i in range(1, 46)])
ctd = SelectMultipleField(
choices=sorted([(k, v) for v, k in CTDFixedCol.items()],
key=operator.itemgetter(1)))
filt_proftype = SelectField(
choices=[['', 'All'], ['V', 'Vertical'], ['H', 'Horizontal']])
filt_data = request.args
form = FiltForm(filt_data)
g.headcenter = """<h1 style='text-align: center;cursor: pointer;' >
<span onclick="$('#particleinfodiv').toggle()"><b>PARTICLE</b> module <span class='glyphicon glyphicon-info-sign'></span> </span>
<a href='/' style='font-size:medium;margin-left: 50px;'>Go to Ecotaxa</a></h2>"""
g.useselect4 = True
return PrintInCharte(
render_template('part/index.html',
form=form,
LocalGIS=app.config.get("LOCALGIS", False),
reqfields=request.args))
def TaskShow(TaskID):
AddTaskSummaryForTemplate()
try:
task = LoadTask(TaskID)
except:
return PrintInCharte(
"This task doesn't exists anymore, peraphs it was automaticaly purged"
)
txt = ""
if gvg('log') == "Y":
WorkingDir = task.GetWorkingDir()
# app.send_static_file(os.path.join(WorkingDir,"TaskLog.txt"))
return flask.send_from_directory(WorkingDir, "TaskLog.txt")
if gvg('CustomDetails') == "Y":
return task.ShowCustomDetails()
if "GetResultFile" in dir(task):
f = task.GetResultFile()
if f is None:
txt += "Error, final file not available"
else:
txt += "<a href='/Task/GetFile/%d/%s' class='btn btn-primary btn-sm ' role='button'>Get file %s</a>" % (
TaskID, f, f)
CustomDetailsAvail = "ShowCustomDetails" in dir(task)
try:
decodedsteperrors = json.loads(task.task.inputparam).get("steperrors")
except:
decodedsteperrors = ["Task Decoding Error"]
ProjectID = getattr(task.param, 'ProjectId', None)
if ProjectID:
Prj = database.Projects.query.filter_by(projid=ProjectID).first()
g.headcenter = "<h4>Project : <a href='/prj/{0}'>{1}</a></h4>".format(
Prj.projid, XSSEscape(Prj.title))
return render_template('task/show.html',
task=task.task,
steperror=decodedsteperrors,
CustomDetailsAvail=CustomDetailsAvail,
extratext=txt)
def QuestionProcess(self):
ServerRoot = Path(app.config['SERVERLOADAREA'])
txt = "<h1>Text File Importation Task</h1>"
errors = []
if self.task.taskstep == 0:
txt += "<h3>Task Creation</h3>"
Prj = database.Projects.query.filter_by(projid=gvg("p")).first()
g.prjtitle = Prj.title
g.prjprojid = Prj.projid
g.prjmanagermailto = Prj.GetFirstManagerMailto()
txt = ""
if Prj.CheckRight(2) == False:
return PrintInCharte("ACCESS DENIED for this project")
if gvp('starttask') == "Y":
FileToSave = None
FileToSaveFileName = None
self.param.ProjectId = gvg("p")
self.param.updateclassif = gvp("updateclassif")
TaxoMap = {}
for l in gvp('TxtTaxoMap').splitlines():
ls = l.split('=', 1)
if len(ls) != 2:
errors.append(
"Taxonomy Mapping : Invalid format for line %s" %
(l))
else:
TaxoMap[ls[0].strip().lower()] = ls[1].strip().lower()
# Verifier la coherence des données
uploadfile = request.files.get("uploadfile")
if uploadfile is not None and uploadfile.filename != '': # import d'un fichier par HTTP
FileToSave = uploadfile # La copie est faite plus tard, car à ce moment là, le repertoire de la tache n'est pas encore créé
FileToSaveFileName = "uploaded.zip"
self.param.InData = "uploaded.zip"
elif len(gvp("ServerPath")) < 2:
errors.append("Input Folder/File Too Short")
else:
sp = ServerRoot.joinpath(Path(gvp("ServerPath")))
if not sp.exists(): #verifie que le repertoire existe
errors.append("Input Folder/File Invalid")
else:
self.param.InData = sp.as_posix()
if len(errors) > 0:
for e in errors:
flash(e, "error")
else:
self.param.TaxoMap = TaxoMap # on stocke le dictionnaire et pas la chaine
return self.StartTask(
self.param,
FileToSave=FileToSave,
FileToSaveFileName=FileToSaveFileName)
else: # valeurs par default
self.param.ProjectId = gvg("p")
return render_template('task/importupdate_create.html',
header=txt,
data=self.param,
ServerPath=gvp("ServerPath"),
TxtTaxoMap=gvp("TxtTaxoMap"))
if self.task.taskstep == 1:
PrjId = self.param.ProjectId
Prj = database.Projects.query.filter_by(projid=PrjId).first()
g.prjtitle = Prj.title
g.appmanagermailto = GetAppManagerMailto()
# self.param.TaxoFound['agreia pratensis']=None #Pour TEST A EFFACER
NotFoundTaxo = [
k for k, v in self.param.TaxoFound.items() if v == None
]
NotFoundUsers = [
k for k, v in self.param.UserFound.items()
if v.get('id') == None
]
app.logger.info("Pending Taxo Not Found = %s", NotFoundTaxo)
app.logger.info("Pending Users Not Found = %s", NotFoundUsers)
if gvp('starttask') == "Y":
app.logger.info("Form Data = %s", request.form)
for i in range(1, 1 + len(NotFoundTaxo)):
orig = gvp(
"orig%d" % (i)
) #Le nom original est dans origXX et la nouvelle valeur dans taxolbXX
newvalue = gvp("taxolb%d" % (i))
if orig in NotFoundTaxo and newvalue != "":
t = database.Taxonomy.query.filter(
database.Taxonomy.id == int(newvalue)).first()
app.logger.info(orig + " associated to " + t.name)
self.param.TaxoFound[orig] = t.id
else:
errors.append(
"Taxonomy Manual Mapping : Invalid value '%s' for '%s'"
% (newvalue, orig))
for i in range(1, 1 + len(NotFoundUsers)):
orig = gvp(
"origuser%d" % (i)
) #Le nom original est dans origXX et la nouvelle valeur dans taxolbXX
newvalue = gvp("userlb%d" % (i))
if orig in NotFoundUsers and newvalue != "":
t = database.users.query.filter(
database.users.id == int(newvalue)).first()
app.logger.info("User " + orig + " associated to " +
t.name)
self.param.UserFound[orig]['id'] = t.id
else:
errors.append(
"User Manual Mapping : Invalid value '%s' for '%s'"
% (newvalue, orig))
app.logger.info("Final Taxofound = %s", self.param.TaxoFound)
self.UpdateParam() # On met à jour ce qui à été accepté
# Verifier la coherence des données
if len(errors) == 0:
return self.StartTask(self.param, step=2)
for e in errors:
flash(e, "error")
NotFoundTaxo = [
k for k, v in self.param.TaxoFound.items() if v == None
]
NotFoundUsers = [
k for k, v in self.param.UserFound.items()
if v.get('id') == None
]
return render_template('task/import_question1.html',
header=txt,
taxo=NotFoundTaxo,
users=NotFoundUsers,
task=self.task)
return PrintInCharte(txt)
def PrjMerge(PrjId):
Prj = database.Projects.query.filter_by(projid=PrjId).first()
if Prj is None:
flash("Project doesn't exists", 'error')
return PrintInCharte("<a href=/prj/>Select another project</a>")
if not Prj.CheckRight(2): # Level 0 = Read, 1 = Annotate, 2 = Admin
flash('You cannot edit settings for this project', 'error')
return PrintInCharte("<a href=/prj/>Select another project</a>")
g.headcenter = "<h4><a href='/prj/{0}'>{1}</a></h4>".format(
Prj.projid, XSSEscape(Prj.title))
txt = "<h3>Project Merge / Fusion </h3>"
if not gvg('src'):
txt += """<ul><li>You are allowed to merge projects that you are allowed to manage
<li>User privileges from both projects will be added
<li>This tool allow to merge two projects in a single projet (called Current project). The added project will then be automatically deleted. If object data are not consistent between both projects :
<ul><li>New data fields are added to the Current project
<li>The resulting project will thus contain partially documented datafields.
</ul><li>Note : Next screen will indicate compatibility issues (if exists) and allow you to Confirm the merging operation.
</ul>
"""
sql = "select p.projid,title,status,coalesce(objcount,0) objcount,coalesce(pctvalidated,0) pctvalidated,coalesce(pctclassified,0) pctclassified from projects p"
if not current_user.has_role(database.AdministratorLabel):
sql += " Join projectspriv pp on p.projid = pp.projid and pp.member=%d" % (
current_user.id, )
sql += " where p.projid!=%d order by title" % Prj.projid
res = GetAll(sql, doXSSEscape=True) #,debug=True
txt += """<table class='table table-bordered table-hover table-verycondensed'>
<tr><th width=120>ID</td><th>Title</td><th width=100>Status</th><th width=100>Nbr Obj</th>
<th width=100>% Validated</th><th width=100>% Classified</th></tr>"""
for r in res:
txt += """<tr><td><a class="btn btn-primary" href='/prj/merge/{activeproject}?src={projid}'>Select</a> {projid}</td>
<td>{title}</td>
<td>{status}</td>
<td>{objcount:0.0f}</td>
<td>{pctvalidated:0.2f}</td>
<td>{pctclassified:0.2f}</td>
</tr>""".format(activeproject=Prj.projid, **r)
txt += "</table>"
return PrintInCharte(txt)
PrjSrc = database.Projects.query.filter_by(projid=int(gvg('src'))).first()
if PrjSrc is None:
flash("Source project doesn't exists", 'error')
return PrintInCharte("<a href=/prj/>Select another project</a>")
if not PrjSrc.CheckRight(2): # Level 0 = Read, 1 = Annotate, 2 = Admin
flash('You cannot merge for this project', 'error')
return PrintInCharte("<a href=/prj/>Select another project</a>")
txt += """<h4>Source Project : {0} - {1} (This project will be destroyed)</h4>
""".format(PrjSrc.projid, XSSEscape(PrjSrc.title))
if not gvg('merge'): # Ici la src à été choisie et vérifiée
if PrjSrc.mappingobj != Prj.mappingobj:
flash("Object mapping differ With source project ", "warning")
if PrjSrc.mappingsample != Prj.mappingsample:
flash("Sample mapping differ With source project ", "warning")
if PrjSrc.mappingacq != Prj.mappingacq:
flash("Acquisition mapping differ With source project ", "warning")
if PrjSrc.mappingprocess != Prj.mappingprocess:
flash("Process mapping differ With source project ", "warning")
txt += FormatError(
""" <span class='glyphicon glyphicon-warning-sign'></span>
Warning project {1} - {2}<br>
Will be destroyed, its content will be transfered in the target project.<br>
This operation is irreversible</p>
<br><a class='btn btn-lg btn-warning' href='/prj/merge/{0}?src={1}&merge=Y'>Start Project Fusion</a>
""",
Prj.projid,
PrjSrc.projid,
XSSEscape(PrjSrc.title),
DoNotEscape=True)
return PrintInCharte(txt)
if gvg('merge') == 'Y':
ExecSQL("update acquisitions set projid={0} where projid={1}".format(
Prj.projid, PrjSrc.projid))
ExecSQL("update process set projid={0} where projid={1}".format(
Prj.projid, PrjSrc.projid))
ExecSQL("update samples set projid={0} where projid={1}".format(
Prj.projid, PrjSrc.projid))
ExecSQL("update obj_head set projid={0} where projid={1}".format(
Prj.projid, PrjSrc.projid))
ExecSQL("update part_projects set projid={0} where projid={1}".format(
Prj.projid, PrjSrc.projid))
# garde le privilege le plus elevé des 2 projets
ExecSQL("""UPDATE projectspriv ppdst
set privilege=case when 'Manage' in (ppsrc.privilege,ppdst.privilege) then 'Manage'
when 'Annotate' in (ppsrc.privilege,ppdst.privilege) then 'Annotate'
else 'View' end
from projectspriv ppsrc
where ppsrc.projid={1} and ppdst.projid={0} and ppsrc.member=ppdst.member"""
.format(Prj.projid, PrjSrc.projid),
debug=True)
# Transfere les privilege depuis le projet source
ExecSQL("""update projectspriv
set projid={0}
where projid={1} and member not in (select member from projectspriv where projid={0})"""
.format(Prj.projid, PrjSrc.projid))
# Efface ceux qui etait des 2 cotés
ExecSQL("delete from projectspriv where projid={0}".format(
PrjSrc.projid))
ExecSQL("delete from projects where projid={0}".format(PrjSrc.projid))
appli.project.main.RecalcProjectTaxoStat(Prj.projid)
appli.project.main.UpdateProjectStat(Prj.projid)
txt += "<div class='alert alert-success' role='alert'>Fusion Done successfully</div>"
txt += "<br><a class='btn btn-lg btn-primary' href='/prj/%s'>Back to target project</a>" % Prj.projid
return PrintInCharte(txt)
def doimporttext():
# test avec D:\temp\Downloads\taxoexport_20181228_101007.tsv
txt=""
uploadfile = request.files.get("uploadfile")
if uploadfile is None:
return PrintInCharte(FormatError("You must send a file"))
Desctxt="{0} on {1:%Y-%m-%d %H:%M:%S} ".format(uploadfile.filename,datetime.datetime.now())
app.logger.info('Load file {} by {}'.format(uploadfile.filename,current_user))
#creation d'un fichier temporaire qui s'efface automatiquement
tmpfile =tempfile.TemporaryFile(mode='w+b')
# app.logger.info('TMP file is {}'.format(tmpfile.name))
uploadfile.save(tmpfile) # on copie le contenu dedants
tmpfile.seek(0) # on se remet au debut
fichier= TextIOWrapper(tmpfile, encoding = 'latin_1', errors = 'replace') # conversion du format binaire au format texte
app.logger.info("Analyzing file %s" % (fichier))
# lecture en mode dictionnaire basé sur la premiere ligne
rdr = csv.reader(fichier, delimiter='\t', quotechar='"', )
# lecture la la ligne des titre
LType = rdr.__next__()
# Lecture du contenu du fichier
RowCount = 0
ExecSQL("truncate table temp_taxo")
sqlinsert = "INSERT INTO temp_taxo(idparent,idtaxo,name,status,typetaxo) values(%s,%s,%s,%s,%s)"
for lig in rdr:
if lig[0].strip() == '': # Ligne vide
continue
database.ExecSQL(sqlinsert, (
lig[0].strip(), lig[1].strip(), lig[2].replace('+', ' ').replace('_', ' ').strip(), lig[3].strip(),
lig[4].strip()))
if RowCount > 0 and RowCount % 1000 == 0:
app.logger.info("Inserted %s lines" % RowCount)
RowCount += 1
app.logger.info("count=%d" % RowCount)
# app.logger.info(str(r))
# if len(UpdatedTaxon)>0:
# ComputeDisplayName(UpdatedTaxon)
txt+="<p style='color: green'> %s taxon loaded </p> "%(RowCount)
# MAJ des IDFinal dans la table temp pour tout ce qui existe.
n = ExecSQL("""UPDATE temp_taxo tt set idfinal=tf.id
from taxonomy tf where tf.id_source=tt.idtaxo or (lower(tf.name)=lower(tt.name) and tf.id_source is null)""")
app.logger.info("%d Nodes already exists " % n)
TSVal="to_timestamp('{}','YYYY-MM-DD HH24:MI:SS')".format(datetime.datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S'))
TSUpdate="lastupdate_datetime="+TSVal
# insertion des nouveaux noeud racines
n = ExecSQL("""INSERT INTO taxonomy (id, parent_id, name, id_source,lastupdate_datetime,source_desc)
select nextval('seq_taxonomy'),NULL,t.name,t.idtaxo,{} ,%s
from temp_taxo t where idparent='-1' and idfinal is null and status='1'""".format(TSVal),["Created by "+Desctxt])
app.logger.info("Inserted %d Root Nodes" % n)
# MAJ de la table import existante
n = ExecSQL("""UPDATE temp_taxo tt set idfinal=tf.id
from taxonomy tf where tf.id_source=tt.idtaxo
and tt.idfinal is null and idparent='-1'""")
app.logger.info("Updated %d inserted Root Nodes" % n)
while True:
# insertion des nouveaux noeud enfants à partir des parents deja insérés
# n=ExecSQL("""INSERT INTO taxonomy (id, parent_id, name, id_source)
# select nextval('seq_taxonomy'),ttp.idfinal,tt.name,tt.idtaxo from temp_taxo tt join temp_taxo ttp on tt.idparent=ttp.idtaxo
# where tt.idfinal is null and ttp.idfinal is not null and status='1'""")
n = ExecSQL("""INSERT INTO taxonomy (id, parent_id, name, id_source,taxotype,lastupdate_datetime,source_desc)
select nextval('seq_taxonomy'),ttp.id,tt.name,tt.idtaxo,case when lower(tt.typetaxo)='taxa' then 'P' else 'M' end,{},%s
from temp_taxo tt join taxonomy ttp on tt.idparent=ttp.id_source
where tt.idfinal is null and status='1'""".format(TSVal),["Created by "+Desctxt])
if n == 0:
app.logger.info("No more data to import")
break
else:
app.logger.info("Inserted %d Child Nodes" % n)
# MAJ de la table import existante
n = ExecSQL("""UPDATE temp_taxo tt set idfinal=tf.id
from taxonomy tf where tf.id_source=tt.idtaxo
and tt.idfinal is null """)
app.logger.info("Updated %d inserted Child Nodes" % n)
n = ExecSQL("""UPDATE taxonomy tf set name=tt.name,{},taxotype=case when lower(tt.typetaxo)='taxa' then 'P' else 'M' end
,source_desc=%s
from temp_taxo tt where tf.id_source=tt.idtaxo
and tt.status='1' and (tf.name!=tt.name
or tf.taxotype!=case when lower(tt.typetaxo)='taxa' then 'P' else 'M' end )
""".format(TSUpdate),["Updated by "+Desctxt])
app.logger.info("Updated %d Nodes names" % n)
n = ExecSQL("""UPDATE taxonomy tfu set parent_id=sq.idfinal,{},source_desc=%s
from (select tf.id, ttp.idfinal from taxonomy tf
,temp_taxo tt LEFT JOIN temp_taxo ttp on tt.idparent=ttp.idtaxo where tf.id_source=tt.idtaxo
and tt.status='1' and coalesce(tf.parent_id,-1)!=coalesce(ttp.idfinal,-1)
and (ttp.idfinal is not null or tt.idparent='-1' )) sq where tfu.id=sq.id""".format(TSUpdate),["Updated by "+Desctxt])
app.logger.info("Updated %d Nodes Parents" % n)
# while True:
# n = ExecSQL("""delete from taxonomy t
# using temp_taxo tt
# where t.id=tt.idfinal and tt.status='0'
# and not exists (select 1 from taxonomy where parent_id=t.id )
# and not exists (select 1 from objects where classif_id=t.id or classif_auto_id=t.id)""")
# if n == 0:
# app.logger.info("No more data to delete")
# break
# else:
# app.logger.info("Deleted %d Nodes" % n)
# Lst = GetAll("""select t.name from taxonomy t,temp_taxo tt
# where t.id=tt.idfinal and tt.status='0'
# and (exists (select 1 from taxonomy where parent_id=t.id )
# or exists (select 1 from objects where classif_id=t.id or classif_auto_id=t.id))""")
# for r in Lst:
# app.logger.info("Can't Delete '%s' because it's used " % r[0])
txt+="<br><a href='/browsetaxo/' class='btn btn-primary'><i class='fas fa-arrow-left'></i> Back to taxonomy</a>"
LstId=[x['idfinal'] for x in GetAll("select idfinal from temp_taxo where idfinal is not null")]
ComputeDisplayName(LstId)
app.logger.info("Updated Display name" )
# if len(Errors):
# txt += "<p style='color: red'> %s errors <ul> " % (len(Errors))
# txt += "\n".join("<li>%s</li>"%x for x in Errors)
# txt += "</ul></p> "
g.bodydivmargin="10px"
return PrintInCharte(txt)
def doimportmassupdate():
# test avec D:\temp\Downloads\taxoexport_20181228_101007.tsv
txt = ""
uploadfile = request.files.get("fichier")
if uploadfile is None:
return PrintInCharte(FormatError("You must send a file"))
app.logger.info('Load file {} by {}'.format(uploadfile.filename,
current_user))
#creation d'un fichier temporaire qui s'efface automatiquement
tmpfile = tempfile.TemporaryFile(mode='w+b')
# app.logger.info('TMP file is {}'.format(tmpfile.name))
uploadfile.save(tmpfile) # on copie le contenu dedants
tmpfile.seek(0) # on se remet au debut
f = TextIOWrapper(
tmpfile, encoding='ascii',
errors='replace') # conversion du format binaire au format texte
rdr = csv.DictReader(
f,
delimiter='\t',
quotechar='"',
) # ouverture sous forme de reader dictionnaire
champs = rdr.fieldnames
app.logger.info("Loading file with this columns : %s" % str(champs))
if 'id' not in champs:
return PrintInCharte(FormatError("A column named 'id' is required"))
IdList = {x['id'] for x in database.GetAll("select id from taxonomy")}
InstanceList = {
x['id']
for x in database.GetAll("select id from ecotaxainst")
}
rowcount = 0
Errors = []
UpdatedTaxon = []
UpdatableCols = [
'parent_id', 'name', 'taxotype', 'taxostatus', 'id_source',
'source_url', 'source_desc', 'creator_email', 'creation_datetime',
'id_instance', 'rename_to'
]
# 'lastupdate_datetime', 'display_name',
for r in rdr:
rowcount += 1
id = int(r['id'])
taxon = database.Taxonomy.query.filter_by(id=id).first()
if taxon is None:
Errors.append("id {} does not exists in the database".format(id))
continue
valueschanged = False
SkipRow = False
for c in champs:
if c in UpdatableCols:
oldvalue = str(ntcv(getattr(taxon, c))).replace('\r', '')
newvalue = r[c].replace("\\n", "\n").strip()
if c in ('parent_id', 'rename_to') and newvalue != '':
if int(newvalue) not in IdList:
Errors.append(
"id {} : {} {} does not exists in the database".
format(id, c, newvalue))
SkipRow = True
continue
if c == 'taxotype':
if newvalue not in ('P', 'M'):
Errors.append("id {} : Invalid taxotype {} ".format(
id, newvalue))
SkipRow = True
continue
if c == 'taxostatus':
if newvalue not in database.TaxoStatus:
Errors.append("id {} : Invalid status {} ".format(
id, newvalue))
SkipRow = True
continue
if c == 'id_instance' and newvalue != '':
if int(newvalue) not in InstanceList:
Errors.append(
"id {} : {} is not a valid instance id".format(
id, newvalue))
SkipRow = True
continue
if oldvalue != newvalue:
valueschanged = True
setattr(taxon, c, newvalue)
app.logger.info("id {} : update {} to {}".format(
id, oldvalue, newvalue))
if SkipRow:
continue
if valueschanged:
# db.session.add(taxon)
UpdatedTaxon.append(id)
taxon.lastupdate_datetime = datetime.datetime.utcnow()
db.session.commit()
# app.logger.info(str(r))
if len(UpdatedTaxon) > 0:
ComputeDisplayName(UpdatedTaxon)
txt += "<p style='color: green'> %s taxon updated </p> " % (
len(UpdatedTaxon))
if len(Errors):
txt += "<p style='color: red'> %s errors <ul> " % (len(Errors))
txt += "\n".join("<li>%s</li>" % x for x in Errors)
txt += "</ul></p> "
txt += "<a href='/browsetaxo/' class='btn btn-primary'><i class='fas fa-arrow-left'></i> Back to Browse Taxonomy</a>"
g.bodydivmargin = "10px"
return PrintInCharte(txt)
def QuestionProcess(self):
ServerRoot = Path(app.config['SERVERLOADAREA'])
txt = "<h1>Particle ZooScan folder Importation Task</h1>"
errors = []
txt += "<h3>Task Creation</h3>"
Prj = partdatabase.part_projects.query.filter_by(
pprojid=gvg("p")).first()
if Prj is None:
return PrintInCharte(ErrorFormat("This project doesn't exists"))
if Prj.instrumtype not in LstInstrumType:
return PrintInCharte(
ErrorFormat("Instrument type '%s' not in list : %s" %
(Prj.instrumtype, ','.join(LstInstrumType))))
g.prjtitle = Prj.ptitle
g.prjprojid = Prj.pprojid
# g.prjowner=Prj.owneridrel.name
DossierUVPPath = ServerRoot / Prj.rawfolder
self.param.DossierUVP = DossierUVPPath.as_posix()
txt = ""
# TODO gestion sécurité
# if Prj.CheckRight(2)==False:
# return PrintInCharte("ACCESS DENIED for this project");
self.param.pprojid = gvg("p")
DirName = DossierUVPPath.name
m = re.search(R"([^_]+)_(.*)", DirName)
if m.lastindex != 2:
return PrintInCharte(
ErrorFormat("Le repertoire projet n'as pas un nom standard"))
else:
FichierHeader = DossierUVPPath / "meta" / (
m.group(1) + "_header_" + m.group(2) + ".txt")
if not FichierHeader.exists():
return PrintInCharte(
ErrorFormat("Le fichier header n'existe pas :"******"""select profileid,psampleid,filename,stationid,firstimage,lastimg,lastimgused,comment,histobrutavailable
,(select count(*) from part_histopart_det where psampleid=s.psampleid) nbrlinedet
,(select count(*) from part_histopart_reduit where psampleid=s.psampleid) nbrlinereduit
,(select count(*) from part_histocat where psampleid=s.psampleid) nbrlinetaxo
from part_samples s
where pprojid=%s""" % (self.param.pprojid))
# print("ouverture de " + FichierHeader)
with open(FichierHeader.as_posix()) as FichierHeaderHandler:
F = csv.DictReader(FichierHeaderHandler, delimiter=';')
for r in F:
r['psampleid'] = None
if r['profileid'] in dbsample:
r['psampleid'] = dbsample[
r['profileid']]['psampleid']
r['histobrutavailable'] = dbsample[
r['profileid']]['histobrutavailable']
r['nbrlinedet'] = dbsample[
r['profileid']]['nbrlinedet']
r['nbrlinereduit'] = dbsample[
r['profileid']]['nbrlinereduit']
r['nbrlinetaxo'] = dbsample[
r['profileid']]['nbrlinetaxo']
self.param.profilelistinheader.append(r)
# self.param.profilelistinheader[r['profileid']]=r
# Tri par 4eme colonne, profileid
self.param.profilelistinheader = sorted(
self.param.profilelistinheader,
key=lambda r: r['profileid'])
if gvp('starttask') == "Y":
self.param.ProcessOnlyMetadata = (gvp('onlymeta', 'N') == 'Y')
self.param.user_name = current_user.name
self.param.user_email = current_user.email
for f in request.form:
self.param.profiletoprocess[request.form.get(f)] = "Y"
if len(self.param.profiletoprocess) == 0:
errors.append("No sample to process selected")
if len(errors) > 0:
for e in errors:
flash(e, "error")
else:
return self.StartTask(self.param)
else: # valeurs par default
if len(self.param.profilelistinheader) == 0:
return PrintInCharte(
ErrorFormat("No sample available in file %s" %
(FichierHeader.as_posix())))
print("%s" % (self.param.profilelistinheader))
return render_template('task/uvpzooscanimport_create.html',
header=txt,
data=self.param,
ServerPath=gvp("ServerPath"),
TxtTaxoMap=gvp("TxtTaxoMap"))
def PrjConfusionMatrix(PrjId):
sql = """select lower(case when tr.name like '% %' then tr.name else concat(tr.name,'(',trp.name,')') end ) ClassifReel
,lower(case when tp.name like '% %' then tp.name else concat(tp.name,'(',tpp.name,')') end) ClassifPredict
from objects o
join taxonomy tp on tp.id=o.classif_auto_id
join taxonomy tr on tr.id=o.classif_id
left join taxonomy tpp on tp.parent_id=tpp.id
left join taxonomy trp on tr.parent_id=trp.id
where projid ={} and classif_qual='V'""".format(PrjId)
DBRes = np.array(GetAll(sql))
txtbacktoproject = "<a href='/prj/%d'>Back to project</a>" % PrjId
Prj = database.Projects.query.filter_by(projid=PrjId).first()
g.headcenter = "<h4><a href='/prj/{0}'>{1}</a></h4>".format(
Prj.projid, XSSEscape(Prj.title))
if len(DBRes) == 0:
flash("No validated objects with prediction", 'error')
return PrintInCharte(txtbacktoproject)
CatTrue = DBRes[:, 0]
CatPred = DBRes[:, 1]
CatAll = [x for x in set(CatPred) | set(CatTrue)]
CatAll.sort()
cm = metrics.confusion_matrix(y_pred=CatPred, y_true=CatTrue)
# Version Division par axe des Réel (somme horiz)
SommeH = cm.sum(axis=1)
SommeV = cm.sum(axis=0)
SommeVNoZero = cm.sum(axis=0)
SommeVNoZero[SommeVNoZero ==
0] = 999999 # pour eviter les division par zéro
SommeHNoZero = cm.sum(axis=1)
SommeHNoZero[SommeHNoZero ==
0] = 999999 # pour eviter les division par zéro
TotalObj = CatPred.shape[0]
D = np.diag(cm)
if gvg("astsv"):
t = [""] #case haut gauche vide
for c in CatAll:
t.append("\t%s" % c)
t.append("\tNb. true\t% true\tRecall")
for c, cml, s, recall in zip(CatAll, cm, SommeH,
100 * D / SommeHNoZero):
t.append("\n%s" % c)
for v in cml:
t.append("\t%s" % v)
t.append("\t%s\t%0.1f\t%0.1f" %
(s, 100 * s / TotalObj,
recall)) # Ajoute le total & Pct de la ligne
t.append("\nNb. predicted")
for s in SommeV:
t.append("\t%s" % (s)) # Ajoute le total de la Colonne
t.append("\n% of predicted")
for s in SommeV:
t.append("\t%0.1f" %
(100 * s / TotalObj)) # Ajoute le % de la Colonne
t.append("\nPrecision")
for s in 100 * D / SommeVNoZero:
t.append("\t%0.1f" % (s)) # Ajoute la precision
t.append("\n")
return Response("".join(t),
mimetype="text/tsv",
headers={
"Content-Disposition":
"attachment; filename=confusionmatrix_%s.tsv" %
Prj.projid
})
t = [
"""<style>
th {
vertical-align: bottom !important;
background-color: #ddd
}
.table > tbody > tr > th.rotate {
height: 140px;
white-space: nowrap;
}
.table > tbody > tr > th.row_header{
height: 140px;
white-space: nowrap;
vertical-align: top !important;
}
th.rotate > div {
transform:
rotate(270deg);
width: 15px;
}
th.row_header > div {
transform:
translate(0px, 200px)
rotate(270deg);
width: 15px;
}
.margin {
font-style: italic;
}
</style>
<h2>Confusion matrix - <a href='?astsv=Y' style='font-size:medium' class='btn btn-primary btn-xs'>TSV Export</a> </h2>
<p>This matrix is refreshed every time you access it. For more information on confusion statistics, please see the <a href='https://en.wikipedia.org/wiki/Precision_and_recall'>very well written Wikipedia page</a>.</p>
<table class='table table-bordered table-hover table-condensed' style='font-size:12px;'>
<tr>
<th> </th>
<th> </th>
<th class='column_header' colspan='1000'>Predicted category</th>
</tr>
<tr>
<th class='row_header' rowspan='1000'><div>True category</div></th>
<th> </th>
"""
]
# ligne titre des categorie
for c in CatAll:
t.append("<th class='rotate'><div>%s</div></th>" % c)
t.append(
"<th>Nb. true</th><th>% true</th><th><a href='https://en.wikipedia.org/wiki/Precision_and_recall#Recall' target='_blank'>Recall</a></th>"
)
for c, cml, s, recall in zip(CatAll, cm, SommeH, 100 * D / SommeHNoZero):
t.append("</tr><tr><th>%s</th>" % c)
for v in cml:
t.append("<td>%s</td>" % v)
t.append(
"<td class='margin'>%s</td><td class='margin'>%0.1f</td class='margin'><td>%0.1f</td>"
% (s, 100 * s / TotalObj,
recall)) # Ajoute le total & Pct de la ligne
t.append("</tr><tr><th>Nb. predicted</th>")
for s in SommeV:
t.append("<td class='margin'>%s</td>" %
(s)) # Ajoute le total de la Colonne
t.append("</tr><tr><th>% of predicted</th>")
for s in SommeV:
t.append("<td class='margin'>%0.1f</td>" %
(100 * s / TotalObj)) # Ajoute le % de la Colonne
t.append(
"</tr><tr><th><a href='https://en.wikipedia.org/wiki/Precision_and_recall#Precision' target='_blank' >Precision</a></th>"
)
for s in 100 * D / SommeVNoZero:
t.append("<td class='margin'>%0.1f</td>" % (s)) # Ajoute la precision
t.append("</tr></table>")
cm_normalized = cm.astype('float') / SommeHNoZero[:, np.newaxis]
FigSize = int(SommeHNoZero.shape[0] / 3)
if FigSize < 8: FigSize = 8 # 800x800 px
g.Fig = plt.figure(figsize=(FigSize, FigSize), dpi=100)
plot_confusion_matrix(cm_normalized, CatAll)
RamImage = io.BytesIO()
g.Fig.savefig(RamImage, dpi=100, format='png')
t.append(
"<h3>Confusion matrix divided by sum of lines</h3><p>The diagonal contains the <a href='https://en.wikipedia.org/wiki/Precision_and_recall#Recall' target='_blank'>recall</a> rate.</p><img src='data:image/png;base64,{}'/>"
.format(base64.encodebytes(RamImage.getvalue()).decode()))
# Version division par axe des prediction pas de div pas zero possible et permet de voir ce que c'est devenu (somme Vert.)
cm_normalized = cm.astype('float') / SommeVNoZero
# plt.figure(figsize=(8,8), dpi=100) # 800x800 px
g.Fig.clf()
plot_confusion_matrix(cm_normalized, CatAll)
RamImage = io.BytesIO()
g.Fig.savefig(RamImage, dpi=100, format='png')
t.append(
"<h3>Confusion matrix divided by sum of columns</h3><p>The diagonal contains the <a href='https://en.wikipedia.org/wiki/Precision_and_recall#Precision' target='_blank'>precision</a> rate.</p><img src='data:image/png;base64,{}'/>"
.format(base64.encodebytes(RamImage.getvalue()).decode()))
# t.append("<br>"+txtbacktoproject)
return PrintInCharte("\n".join(t))
def AutoCleanManual():
return PrintInCharte(AutoClean())