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']])
def routetaxodel():
txt = ""
try:
taxoid = int(gvp('id'))
params = dict(id=taxoid)
UsedTaxon = database.GetAll(
"""select 1 from taxonomy t where id=(%s)
and (
exists(select 1 from taxonomy p where p.parent_id=t.id)
or exists(select 1 from obj_head where classif_id=t.id) )
""", [taxoid])
if len(UsedTaxon) > 0:
return appli.ErrorFormat(
"This Taxon is used locally, you cannot remove it")
database.ExecSQL("delete from taxonomy t where id=%s", [taxoid])
j = request_withinstanceinfo("/deltaxon/", params)
if j['msg'] != 'ok':
return appli.ErrorFormat("deltaxon Error :" + j['msg'])
txt = """<script> DoSync(); At2PopupClose(0); </script>"""
return txt
except Exception as e:
import traceback
tb_list = traceback.format_tb(e.__traceback__)
return appli.FormatError("Saving Error : {}\n{}", e,
"__BR__".join(tb_list[::-1]))
def CheckInstanceSecurity():
id_instance = gvp('id_instance', '')
if not id_instance.isdigit():
abort(jsonify(msg="invalid instance id format"))
Instance = database.GetAll(
"select sharedsecret from ecotaxainst WHERE id=%s", [int(id_instance)])
if len(Instance) != 1:
abort(jsonify(msg="invalid instance"))
if Instance[0]['sharedsecret'] != gvp('sharedsecret'):
time.sleep(random.random())
abort(jsonify(msg="invalid instance secret"))
g.ecotaxa_version = gvp('ecotaxa_version')
g.ecotaxa_version_int = 0
re_match = re.match(r"(\d+).(\d+).(\d+)", g.ecotaxa_version)
if re_match:
g.ecotaxa_version_int = int(re_match.group(1)) * 10000 + int(
re_match.group(2)) * 100 + int(re_match.group(3))
else:
abort(jsonify(msg='invalid version'))
g.MsgVersion = "ok"
if g.ecotaxa_version_int < 20000:
g.MsgVersion = "Current Ecotaxa version is 2.0.0 it's recommanded to upgrade your version (%s.%s.%s) ." % (
re_match.group(1), re_match.group(2), re_match.group(3))
if g.ecotaxa_version_int < 20000:
abort(jsonify(msg='Your Ecotaxa version is too old, you must upgrade'))
def routegettaxon():
sql = """select t.id,t.parent_id,t.name,t.taxotype,t.display_name,t.id_source,t.source_url,t.source_desc
,t.creator_email
,to_char(t.creation_datetime,'YYYY-MM-DD HH24:MI:SS') creation_datetime
,to_char(t.lastupdate_datetime,'YYYY-MM-DD HH24:MI:SS') lastupdate_datetime,t.id_instance,t.taxostatus,t.rename_to
from taxonomy t where 1=1 """
sqlparam = {}
filtertype = gvp('filtertype')
if filtertype == 'id':
sql += " and id=%(id)s "
sqlparam['id'] = int(gvp('id'))
elif filtertype == 'since':
sql += """ and lastupdate_datetime>=to_timestamp(%(startdate)s,'YYYY-MM-DD HH24:MI:SS')
"""
# and (taxostatus!='N' or id_instance=%(id_instance)s) ==> Désactivé dans un premier temps
sqlparam['startdate'] = gvp('startdate')
# sqlparam['id_instance']=gvp('id_instance')
else:
return jsonify(msg='filtertype required')
sql += " order by lastupdate_datetime "
res = database.GetAll(sql,
sqlparam,
cursor_factory=psycopg2.extras.RealDictCursor)
if len(res) == 0 and filtertype == 'id':
return jsonify(msg='no data found')
return jsonify(res)
def searchusers():
term=gvg("q")
if len(term)<2:
return "[]"
term=R"%"+term+R"%"
res = database.GetAll("SELECT id, name FROM users WHERE name ILIKE %s and active=true order by name limit 1000", (term,),debug=False)
return json.dumps([dict(id=r[0],text=r[1]) for r in res])
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 ajaxorganisationlist():
LstOrga = database.GetAll(
"select * from (select distinct organisation from users where organisation ilike %(term)s)q order by upper(organisation)",
({
'term': '%' + gvg('term', '') + '%'
}))
return json.dumps([o['organisation'] for o in LstOrga])
def searchannot(PrjId):
projid = str(int(PrjId))
res = database.GetAll("""Select id,name
from users
where id in ( SELECT distinct classif_who FROM obj_head WHERE projid ={0} ) order by name""".format(projid))
# if gvg("format",'J')=='J': # version JSon par defaut
# return json.dumps([dict(id=r[0],text=r[1]) for r in res])
return render_template('search/annot.html', samples=res)
def ajaxcoutrylist():
Lst = database.GetAll(
"select countryname from countrylist where countryname ilike %(term)s order by countryname",
({
'term': '%' + gvg('term', '') + '%'
}))
res = [{'id': o['countryname'], 'text': o['countryname']} for o in Lst]
res.append({'id': 'Other', 'text': 'Other'})
return json.dumps({"results": res})
def JinjaGetManagerList(sujet=""):
LstUsers=database.GetAll("""select distinct u.email,u.name,Lower(u.name)
FROM users_roles ur join users u on ur.user_id=u.id
where ur.role_id=2
and u.active=TRUE and email like '%@%'
order by Lower(u.name)""")
if sujet:
sujet="?"+urllib.parse.urlencode({"subject":sujet}).replace('+','%20')
return " ".join(["<li><a href='mailto:{1}{0}'>{2} ({1})</a></li> ".format(sujet,*r) for r in LstUsers ])
def GetFilteredSamples(Filter=None,
GetVisibleOnly=False,
ForceVerticalIfNotSpecified=False,
RequiredPartVisibility='N',
RequiredZooVisibility='N'):
sqlparam = {}
if Filter is None: # si filtre non spécifié on utilise GET
Filter = request.args
sqlvisible, sqljoin = GetSQLVisibility()
sql = "select s.psampleid,s.latitude,s.longitude,cast (" + sqlvisible + """ as varchar(2) ) as visibility,s.pprojid,pp.ptitle
from part_samples s
JOIN part_projects pp on s.pprojid=pp.pprojid
LEFT JOIN projects p on pp.projid=p.projid """
sql += sqljoin
sql += " where 1=1 "
if Filter.get("MapN", '') != "" and Filter.get(
"MapW", '') != "" and Filter.get("MapE", '') != "" and Filter.get(
"MapS", '') != "":
sql += " and s.latitude between %(MapS)s and %(MapN)s "
sqlparam['MapN'] = Filter.get("MapN")
sqlparam['MapS'] = Filter.get("MapS")
sqlparam['MapW'] = float(Filter.get("MapW"))
sqlparam['MapE'] = float(Filter.get("MapE"))
if sqlparam['MapW'] < sqlparam['MapE']:
sql += " and s.longitude between %(MapW)s and %(MapE)s "
else:
sql += " and (s.longitude between %(MapW)s and 180 or s.longitude between -180 and %(MapE)s )"
if Filter.get("filt_fromdate", '') != "":
sql += " and s.sampledate>= to_date(%(fromdate)s,'YYYY-MM-DD') "
sqlparam['fromdate'] = Filter.get("filt_fromdate")
if Filter.get("filt_todate", '') != "":
sql += " and s.sampledate< to_date(%(todate)s,'YYYY-MM-DD')+1 "
sqlparam['todate'] = Filter.get("filt_todate")
if Filter.get("filt_proj", '') != "":
sql += " and p.projid in (%s) " % (','.join(
[str(int(x)) for x in request.args.getlist("filt_proj")]))
if Filter.get("filt_uproj", '') != "":
sql += " and pp.pprojid in (%s) " % (','.join(
[str(int(x)) for x in request.args.getlist("filt_uproj")]))
if Filter.get("filt_instrum", '') != "":
sql += " and lower(pp.instrumtype)=lower(%(instrum)s) "
sqlparam['instrum'] = Filter.get("filt_instrum")
if Filter.get("filt_proftype", '') != "":
sql += " and organizedbydeepth = %s " % (True if Filter.get(
"filt_proftype", '' if ForceVerticalIfNotSpecified == False else
'V') == 'V' else False)
sql = """select s.*,case when substr(visibility,1,1)>='%s' and substr(visibility,2,1)>='%s' then true end as visible
from (%s ) s """ % (RequiredPartVisibility, RequiredZooVisibility,
sql)
if GetVisibleOnly:
sql = "select * from (" + sql + ") s where visible=true "
sql += """ order by s.psampleid """
return database.GetAll(sql, sqlparam)
def GlobalTaxoCompute():
# Sample Particule sans liens etabli avec Zoo qui sont liables
Samples=database.GetAll("""select ps.psampleid,pp.projid from samples
join part_samples ps on samples.orig_id=ps.profileid
join part_projects pp on ps.pprojid = pp.pprojid and samples.projid=pp.projid
where ps.sampleid is null""")
for S in Samples:
logging.info("Matching %s %s",S['psampleid'],ComputeZooMatch(S['psampleid'],S['projid']))
# sample ayant un objet qui à été classifié depuis le dernier calcul de l'histogramme
Samples=database.GetAll("""select psampleid, daterecalculhistotaxo
from part_samples ps
where ps.sampleid is not null
and (
exists (select 1 from obj_head where obj_head.sampleid=ps.sampleid and obj_head.classif_when>ps.daterecalculhistotaxo)
or ps.daterecalculhistotaxo is null
or exists (select 1 from part_histocat_lst hc where hc.psampleid=ps.psampleid and classif_id not in (select id from taxonomy))
)""")
for S in Samples:
ComputeZooHisto(S['psampleid'])
def RecomputePart(ProjectID, What, User, Email):
if 'C' in What:
if User is None or Email is None:
print("-u and -e options are required for CTD import")
quit(-1)
with app.app_context(
): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent
g.db = None
import appli.part.database as partdatabase
import appli.part.prj as prj
import appli.part.common_sample_import as common_import
Prj = partdatabase.part_projects.query.filter_by(
pprojid=ProjectID).first()
Samples = database.GetAll(
"select psampleid,profileid from part_samples where pprojid=(%s)",
[ProjectID])
for S in Samples:
print("Processing particle sample %s:%s" %
(S['psampleid'], S['profileid']))
if 'D' in What:
print("Det=",
prj.ComputeHistoDet(S['psampleid'], Prj.instrumtype))
if 'R' in What:
print("Red=",
prj.ComputeHistoRed(S['psampleid'], Prj.instrumtype))
if 'M' in What:
print("Match=", prj.ComputeZooMatch(S['psampleid'],
Prj.projid))
if 'C' in What:
print(
"CTD=", "Imported" if common_import.ImportCTD(
S['psampleid'], User, Email) else 'CTD No file')
Samples = database.GetAll(
"select psampleid,profileid,sampleid from part_samples where pprojid=(%s)",
[ProjectID])
for S in Samples:
if 'T' in What and S['sampleid']:
print(
"Zoo for particle sample %s:%s=" %
(S['psampleid'], S['profileid']),
prj.ComputeZooHisto(S['psampleid']))
def checktaxon(taxotype: str, name: str, parent='', updatetarget=''):
"""
Check if a Taxon name follows rules
:param taxotype: Taxon Type P or M
:param name: Taxon Name
:param parent: Parent ID
:param updatetarget: ID of the updated taxon, used to avoid duplicate error when you update yourself
:return: "ok" or Error message
"""
if len(name) < 3:
return "Name too short 3 characters min."
if parent:
if len(database.GetAll("select id from taxonomy where id=%s",
[parent])) != 1:
return "invalid parent, doesn't exists in database"
if taxotype == 'P': # Phylo
# if not re.match(r"^[A-Z][a-z+\-']{2,} ?[a-z+\-']*$", name):
if not re.match(r"^[A-Z][a-z+\-']{2,}( [a-z+\-']+)*$", name):
return "Must contains only letters and -+' and not more than 1 consecutive whitespace, Start with a Uppercase"
sql = "select count(*) from taxonomy where lower(name)=lower( (%s) ) "
if updatetarget and int(updatetarget) > 0:
sql += " and id!={}".format(int(updatetarget))
Nbr = int(database.GetAll(sql, [name])[0][0])
if Nbr != 0:
return "duplicate name"
elif taxotype == 'M': # Morpho
if not re.match(r"^[a-z+\-']{3,}( [a-z+\-']+)*$", name):
return "Must contains only letters and -+' and not more than 1 consecutive whitespace, be lowercase only"
if not parent:
return "You must specify a parent to check morpho type"
sql = "select count(*) from taxonomy where lower(name)=lower( (%s) ) and parent_id={}".format(
int(parent))
if updatetarget and int(updatetarget) > 0:
sql += " and id!={}".format(int(updatetarget))
Nbr = int(database.GetAll(sql, [name])[0][0])
if Nbr != 0:
return "duplicate child for this parent"
else:
return "Invalid taxotype"
return "ok"
def ComputeZooMatch(psampleid, projid):
if projid is not None:
ecosample = database.GetAll("""select samples.sampleid from samples
join part_samples ps on psampleid=%s
where samples.projid=%s and samples.orig_id=ps.profileid""",
( psampleid,int(projid)))
if len(ecosample) == 1:
database.ExecSQL("update part_samples set sampleid=%s where psampleid=%s",
(ecosample[0]['sampleid'], psampleid))
return " Matched"
else:
return " <span style='color: orange;'>No match found in Ecotaxa</span>"
else:
return " <span style='color: red;'>Ecotaxa sample matching impossible if Particle project not linked to an Ecotaxa project</span>"
def GenerateReducedParticleHistogram(psampleid):
"""
Génération de l'histogramme particulaire détaillé (45 classes) et réduit (15 classes) à partir de l'histogramme détaillé
:param psampleid:
:return:
"""
if database.GetAll(
"select count(*) from part_histopart_det where psampleid=" +
str(psampleid))[0][0] <= 0:
return "<span style='color: red;'>Reduced Histogram can't be computer without Detailed histogram</span>"
database.ExecSQL("delete from part_histopart_reduit where psampleid=" +
str(psampleid))
sql = """insert into part_histopart_reduit(psampleid, lineno, depth,datetime, watervolume
, class01, class02, class03, class04, class05, class06, class07, class08, class09, class10, class11, class12, class13, class14, class15
, biovol01, biovol02, biovol03, biovol04, biovol05, biovol06, biovol07, biovol08, biovol09, biovol10, biovol11, biovol12, biovol13, biovol14, biovol15
)
select psampleid, lineno, depth,datetime, watervolume,
coalesce(class01,0)+coalesce(class02,0)+coalesce(class03,0) as c1,
coalesce(class04,0)+coalesce(class05,0)+coalesce(class06,0) as c2,
coalesce(class07,0)+coalesce(class08,0)+coalesce(class09,0) as c3,
coalesce(class10,0)+coalesce(class11,0)+coalesce(class12,0) as c4,
coalesce(class13,0)+coalesce(class14,0)+coalesce(class15,0) as c5,
coalesce(class16,0)+coalesce(class17,0)+coalesce(class18,0) as c6,
coalesce(class19,0)+coalesce(class20,0)+coalesce(class21,0) as c7,
coalesce(class22,0)+coalesce(class23,0)+coalesce(class24,0) as c8,
coalesce(class25,0)+coalesce(class26,0)+coalesce(class27,0) as c9,
coalesce(class28,0)+coalesce(class29,0)+coalesce(class30,0) as c10,
coalesce(class31,0)+coalesce(class32,0)+coalesce(class33,0) as c11,
coalesce(class34,0)+coalesce(class35,0)+coalesce(class36,0) as c12,
coalesce(class37,0)+coalesce(class38,0)+coalesce(class39,0) as c13,
coalesce(class40,0)+coalesce(class41,0)+coalesce(class42,0) as c14,
coalesce(class43,0)+coalesce(class44,0)+coalesce(class45,0) as c15,
coalesce(biovol01,0)+coalesce(biovol02,0)+coalesce(biovol03,0) as bv1,
coalesce(biovol04,0)+coalesce(biovol05,0)+coalesce(biovol06,0) as bv2,
coalesce(biovol07,0)+coalesce(biovol08,0)+coalesce(biovol09,0) as bv3,
coalesce(biovol10,0)+coalesce(biovol11,0)+coalesce(biovol12,0) as bv4,
coalesce(biovol13,0)+coalesce(biovol14,0)+coalesce(biovol15,0) as bv5,
coalesce(biovol16,0)+coalesce(biovol17,0)+coalesce(biovol18,0) as bv6,
coalesce(biovol19,0)+coalesce(biovol20,0)+coalesce(biovol21,0) as bv7,
coalesce(biovol22,0)+coalesce(biovol23,0)+coalesce(biovol24,0) as bv8,
coalesce(biovol25,0)+coalesce(biovol26,0)+coalesce(biovol27,0) as bv9,
coalesce(biovol28,0)+coalesce(biovol29,0)+coalesce(biovol30,0) as bv10,
coalesce(biovol31,0)+coalesce(biovol32,0)+coalesce(biovol33,0) as bv11,
coalesce(biovol34,0)+coalesce(biovol35,0)+coalesce(biovol36,0) as bv12,
coalesce(biovol37,0)+coalesce(biovol38,0)+coalesce(biovol39,0) as bv13,
coalesce(biovol40,0)+coalesce(biovol41,0)+coalesce(biovol42,0) as bv14,
coalesce(biovol43,0)+coalesce(biovol44,0)+coalesce(biovol45,0) as bv15
from part_histopart_det where psampleid=""" + str(psampleid)
database.ExecSQL(sql)
return " reduced Histogram computed"
def searchsamples():
term=("%"+gvg("q")+"%").lower().replace('*','%')
projid=""
if gvg("projid")!="":
projid=str(int(gvg("projid")))
if gvg("projid[]")!="":
projid=",".join([str(int(x)) for x in request.args.getlist("projid[]")])
if projid=="":
return "[]"
res = database.GetAll("""SELECT sampleid, orig_id
FROM samples
WHERE projid in ({0}) and orig_id like %s order by orig_id limit 2000""".format(projid), (term,))
if gvg("format",'J')=='J': # version JSon par defaut
return json.dumps([dict(id=r[0],text=r[1]) for r in res])
return render_template('search/samples.html', samples=res)
def searchgettaxomapping():
Prj = database.Projects.query.filter_by(projid=int(gvg("projid"))).first()
classifsettings = DecodeEqualList(Prj.classifsettings)
PostTaxoMapping=classifsettings.get("posttaxomapping","")
res={'mapping':{},'taxo':{}}
if PostTaxoMapping!='':
res['mapping'] = {el[0].strip(): el[1].strip() for el in
[el.split(':') for el in PostTaxoMapping.split(',') if el != '']}
sql = """SELECT tf.id, tf.name||case when p1.name is not null and tf.name not like '%% %%' then ' ('||p1.name||')' else ' ' end as name
FROM taxonomy tf
left join taxonomy p1 on tf.parent_id=p1.id
WHERE tf.id = any (%s)
order by tf.name limit 2000"""
res['taxo'] = {x[0]:x[1] for x in database.GetAll(sql,([int(x) for x in res['mapping'].values()],))}
return json.dumps(res)
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 UpdateSunPos(ProjId):
"""
will update Sunpos field for object of the given project
if projid = * all project are updated
"""
from appli import CalcAstralDayTime
from astral import AstralError
with app.app_context(
): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent
g.db = None
param = []
sql = """select distinct objdate,objtime,round(cast(latitude as NUMERIC),4) latitude,round(cast(longitude as NUMERIC),4) longitude
from obj_head
where objdate is not null and objtime is not null and longitude is not null and latitude is not null
"""
if ProjId != '*':
sql += " and projid=%s "
param.append(ProjId)
Obj = database.GetAll(sql, param)
for o in Obj:
# l=Location()
# l.latitude=o['latitude']
# l.longitude = o['longitude']
# s=l.sun(date=o['objdate'],local=False)
# dt=datetime.datetime(o['objdate'].year,o['objdate'].month,o['objdate'].day,o['objtime'].hour,o['objtime'].minute,o['objtime'].second,tzinfo=pytz.UTC)
# if s['sunset'].time()>s['sunrise'].time() \
# and dt.time()>=s['sunset'].time(): Result='N'
# elif dt>=s['dusk']: Result='U'
# elif dt>=s['sunrise']: Result='D'
app.logger.info("Process %s %s %s %s", o['objdate'], o['objtime'],
o['latitude'], o['longitude'])
try:
Result = CalcAstralDayTime(o['objdate'], o['objtime'],
o['latitude'], o['longitude'])
sql = "update obj_head set sunpos=%s where objdate=%s and objtime=%s and round(cast(latitude as NUMERIC),4)=%s and round(cast(longitude as NUMERIC),4)=%s "
param = [
Result, o['objdate'], o['objtime'], o['latitude'],
o['longitude']
]
if ProjId != '*':
sql += " and projid=%s "
param.append(ProjId)
database.ExecSQL(sql, param)
app.logger.info(Result)
except AstralError as e:
app.logger.error("Astral error : %s", e)
def Partgetsamplepopover(psampleid):
sql = """select s.psampleid,s.profileid,p.ptitle,ep.title,p.cruise,p.ship ,p.projid,p.pprojid
,round(cast(s.latitude as NUMERIC),4) latitude,round(cast(s.longitude as NUMERIC),4) longitude
from part_samples s
LEFT JOIN part_projects p on s.pprojid=p.pprojid
left join projects ep on p.projid = ep.projid
where s.psampleid=%(psampleid)s
"""
data = database.GetAll(sql, {'psampleid': psampleid})[0]
txt = """ID : {psampleid}<br>
Profile ID : {profileid}<br>
Project : {ptitle} ({pprojid})<br>
Ship : {ship}<br>
Cruise : {cruise}<br>
Ecotaxa Project : {title} ({projid})<br>
Lat/Lon : {latitude}/{longitude}
""".format(**data)
return txt
def searchinstrumlist():
sql="select DISTINCT lower(instrument) from acquisitions where instrument is not null and instrument!='' "
if gvg("projid")!="":
sql += " and projid="+str(int(gvg("projid")))
res = database.GetAll(sql+" order by 1")
txt="List of available Intruments : <hr><ul id=InstrumList>"
for r in res:
txt +="\n<li>{0}</li>".format(r[0])
txt += """</ul>
<hr>
<button type="button" class="btn btn-default btn-" onclick="$('#PopupDetails').modal('hide');">Close</button>
<br><br>
<script>
$('#InstrumList li').click(function(){
$('#filt_instrum').val($(this).text());
$('#PopupDetails').modal('hide');
}).css('cursor','pointer');
</script>
"""
return txt
def QuestionProcessScreenSelectSource(self,Prj):
# Premier écran de configuration, choix du projet de base
PreviousTxt = self.GetFilterText()
d = DecodeEqualList(Prj.classifsettings)
TargetFeatures = set(DecodeEqualList(Prj.mappingobj).values())
PreviousLS=d.get("baseproject", "")
if PreviousLS != "":
BasePrj = GetAll("select projid,title from projects where projid in ({0})".format(PreviousLS))
if len(BasePrj):
PreviousTxt += """<a class='btn btn-primary' href='?{0}&src={1}'>
USE previous Learning Set : {2}</a><br><br>OR USE another project<br><br>""".format(
request.query_string.decode("utf-8"),PreviousLS, " + ".join(["#{0} - {1}".format(*r) for r in BasePrj]) )
from flask_login import current_user
sql = """select projid,title,round(coalesce(objcount,0)*coalesce(pctvalidated,0)/100) objvalid
,mappingobj,coalesce(cnn_network_id,'') cnn_network_id
from projects where 1=1 """
sqlparam=[]
if gvp('filt_title'):
sql += " and title ilike (%s) "
sqlparam.append(("%"+gvp('filt_title')+"%"))
if gvp('filt_instrum', '') != '':
sql += " and projid in (select distinct projid from acquisitions where instrument ilike '%%'||(%s) ||'%%' ) "
sqlparam.append(gvp('filt_instrum'))
sql += " order by title"
ProjList = database.GetAll(sql,sqlparam,doXSSEscape=True)
TblBody=""
for r in ProjList:
MatchingFeatures = len(set(DecodeEqualList(r['mappingobj']).values()) & TargetFeatures)
if MatchingFeatures<int(gvp("filt_featurenbr") if gvp("filt_featurenbr") else 10):
continue
TblBody += """<tr><td><input type='checkbox' class='selproj' data-prjid='{projid}'></td>
<td>#{projid} - {title}</td><td>{objvalid:0.0f}</td><td>{MatchingFeatures}</td><td>{cnn_network_id}</td>
</tr>""".format(MatchingFeatures=MatchingFeatures, **r)
return render_template('task/classifauto2_create_lstproj.html'
,url=request.query_string.decode('utf-8')
,TblBody=TblBody
,PreviousTxt=PreviousTxt)
def UpdateObjectFromForm(taxon):
taxon.parent_id = gvp('parent_id')
taxon.name = gvp('name')
taxon.taxotype = gvp('taxotype')
taxon.id_source = gvp('id_source')
taxon.source_url = gvp('source_url')
taxon.source_desc = gvp('source_desc')
taxon.creator_email = gvp('creator_email')
if taxon.taxostatus is None:
taxon.taxostatus = 'N'
elif taxon.taxostatus != 'N':
abort(jsonify(msg="Only non validated status can be updated"))
elif taxon.id_instance != int(gvp('id_instance')):
abort(jsonify(msg="Only instance author of a taxon can update it"))
if taxon.id is None:
taxon.creation_datetime = datetime.datetime.utcnow()
taxon.id_instance = int(gvp('id_instance'))
taxon.lastupdate_datetime = datetime.datetime.utcnow()
if gvp('rename_to') != '':
if len(
database.GetAll("select id from taxonomy where id=%s",
[int(gvp('rename_to'))])) != 1:
abort(jsonify(msg="invalid rename_to value"))
taxon.rename_to = gvp('rename_to') or None
def GenerateTaxonomyHistogram(psampleid):
"""
Génération de l'histogramme Taxonomique
:param psampleid:
:return:
"""
UvpSample = partdatabase.part_samples.query.filter_by(
psampleid=psampleid).first()
if UvpSample is None:
raise Exception("GenerateTaxonomyHistogram: Sample %d missing" %
psampleid)
Prj = partdatabase.part_projects.query.filter_by(
pprojid=UvpSample.pprojid).first()
if UvpSample.sampleid is None:
raise Exception(
"GenerateTaxonomyHistogram: Ecotaxa sampleid required in Sample %d "
% psampleid)
pixel = UvpSample.acq_pixel
EcoPrj = database.Projects.query.filter_by(projid=Prj.projid).first()
if EcoPrj is None:
raise Exception(
"GenerateTaxonomyHistogram: Ecotaxa project %d missing" %
Prj.projid)
objmap = DecodeEqualList(EcoPrj.mappingobj)
areacol = None
for k, v in objmap.items():
if v.lower() == 'area':
areacol = k
break
if areacol is None:
raise Exception(
"GenerateTaxonomyHistogram: esd attribute required in Ecotaxa project %d"
% Prj.projid)
# app.logger.info("Esd col is %s",areacol)
DepthOffset = UvpSample.acq_depthoffset
if DepthOffset is None:
DepthOffset = Prj.default_depthoffset
if DepthOffset is None:
DepthOffset = 0
# LstTaxo=database.GetAll("""select classif_id,floor((depth_min+{DepthOffset})/5) tranche,avg({areacol}) as avgarea,count(*) nbr
# from objects
# WHERE sampleid={sampleid} and classif_id is not NULL and depth_min is not NULL and {areacol} is not NULL and classif_qual='V'
# group by classif_id,floor((depth_min+{DepthOffset})/5)"""
# .format(sampleid=UvpSample.sampleid,areacol=areacol,DepthOffset=DepthOffset))
LstTaxoDet = database.GetAll(
"""select classif_id,floor((depth_min+{DepthOffset})/5) tranche,{areacol} areacol
from objects
WHERE sampleid={sampleid} and classif_id is not NULL and depth_min is not NULL and {areacol} is not NULL and classif_qual='V'
""".format(sampleid=UvpSample.sampleid,
areacol=areacol,
DepthOffset=DepthOffset))
LstTaxo = {}
for r in LstTaxoDet:
cle = "{}/{}".format(r['classif_id'], r['tranche'])
if cle not in LstTaxo:
LstTaxo[cle] = {
'nbr': 0,
'esdsum': 0,
'bvsum': 0,
'classif_id': r['classif_id'],
'tranche': r['tranche']
}
LstTaxo[cle]['nbr'] += 1
esd = 2 * math.sqrt(r['areacol'] * (pixel**2) / math.pi)
LstTaxo[cle]['esdsum'] += esd
biovolume = pow(esd / 2, 3) * 4 * math.pi / 3
LstTaxo[cle]['bvsum'] += biovolume
LstVol = database.GetAssoc(
"""select cast(round((depth-2.5)/5) as INT) tranche,watervolume from part_histopart_reduit where psampleid=%s"""
% psampleid)
# 0 Taxoid, tranche
# TblTaxo=np.empty([len(LstTaxo),4])
database.ExecSQL("delete from part_histocat_lst where psampleid=%s" %
psampleid)
database.ExecSQL("delete from part_histocat where psampleid=%s" %
psampleid)
sql = """insert into part_histocat(psampleid, classif_id, lineno, depth, watervolume, nbr, avgesd, totalbiovolume)
values({psampleid},{classif_id},{lineno},{depth},{watervolume},{nbr},{avgesd},{totalbiovolume})"""
for r in LstTaxo.values():
avgesd = r['esdsum'] / r['nbr']
biovolume = r['bvsum']
watervolume = 'NULL'
if r['tranche'] in LstVol:
watervolume = LstVol[r['tranche']]['watervolume']
database.ExecSQL(
sql.format(psampleid=psampleid,
classif_id=r['classif_id'],
lineno=r['tranche'],
depth=r['tranche'] * 5 + 2.5,
watervolume=watervolume,
nbr=r['nbr'],
avgesd=avgesd,
totalbiovolume=biovolume))
database.ExecSQL("""insert into part_histocat_lst(psampleid, classif_id)
select distinct psampleid,classif_id from part_histocat where psampleid=%s"""
% psampleid)
database.ExecSQL(
"""update part_samples set daterecalculhistotaxo=current_timestamp
where psampleid=%s""" % psampleid)
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 DoFullSync():
txt = ""
try:
UpdatableCols = [
'parent_id', 'name', 'taxotype', 'taxostatus', 'id_source',
'id_instance', 'rename_to', 'display_name', 'source_desc',
'source_url', 'creation_datetime', 'creator_email'
]
MaxUpdate = database.GetAll(
"select coalesce(max(lastupdate_datetime),to_timestamp('2000-01-01','YYYY-MM-DD')) lastupdate from taxonomy"
)
MaxUpdateDate = MaxUpdate[0]['lastupdate']
j = request_withinstanceinfo("/gettaxon/", {
'filtertype': 'since',
'startdate': MaxUpdateDate
})
if 'msg' in j:
return appli.ErrorFormat("Sync Error :" + j['msg'])
NbrRow = len(j)
NbrUpdate = NbrInsert = 0
txt += "Received {} rows<br>".format(NbrRow)
if (NbrRow > 0):
txt += "Taxo 0 = {}<br>".format(j[0])
for jtaxon in j:
taxon = database.Taxonomy.query.filter_by(
id=int(jtaxon['id'])).first()
lastupdate_datetime = datetime.datetime.strptime(
jtaxon['lastupdate_datetime'], '%Y-%m-%d %H:%M:%S')
if taxon:
if taxon.lastupdate_datetime == lastupdate_datetime:
continue #already up to date
NbrUpdate += 1
else:
if ntcv(jtaxon['rename_to']) != '':
continue # don't insert taxon that should be renamed
if ntcv(jtaxon['taxostatus']) == 'D':
continue # don't insert taxon that are deprecated and planned to be deleted
NbrInsert += 1
taxon = database.Taxonomy()
taxon.id = int(jtaxon['id'])
db.session.add(taxon)
for c in UpdatableCols:
setattr(taxon, c, jtaxon[c])
taxon.lastupdate_datetime = lastupdate_datetime
db.session.commit()
# Manage rename_to
sqlbase = "with taxorename as (select id,rename_to from taxonomy where rename_to is not null) "
sql = sqlbase + """select distinct projid from obj_head o join taxorename tr on o.classif_id=tr.id """
ProjetsToRecalc = database.GetAll(sql)
sql = sqlbase + """update obj_head o set classif_id=tr.rename_to
from taxorename tr where o.classif_id=tr.id """
NbrRenamedObjects = ExecSQL(sql)
sql = sqlbase + """update obj_head o set classif_auto_id=tr.rename_to
from taxorename tr where o.classif_auto_id=tr.id """
ExecSQL(sql)
sql = sqlbase + """update objectsclassifhisto o set classif_id=tr.rename_to
from taxorename tr where o.classif_id=tr.id """
ExecSQL(sql)
# on efface les taxon qui doivent être renomé car ils l'ont normalement été
sql = """delete from taxonomy where rename_to is not null """
ExecSQL(sql)
sql = """delete from taxonomy t where taxostatus='D'
and not exists(select 1 from projects_taxo_stat where id=t.id) """
ExecSQL(sql)
# il faut recalculer projects_taxo_stat et part_histocat,part_histocat_lst pour ceux qui referencaient un
# taxon renomé et donc disparu
if NbrRenamedObjects > 0:
# cron.RefreshTaxoStat() operation trés longue (env 5 minutes en prod, il faut être plus selectif)
# permet de recalculer projects_taxo_stat
for Projet in ProjetsToRecalc:
appli.project.main.RecalcProjectTaxoStat(Projet['projid'])
#recalcul part_histocat,part_histocat_lst
appli.part.prj.GlobalTaxoCompute()
flash(
"Received {} rows,Insertion : {} Update :{}".format(
NbrRow, NbrInsert, NbrUpdate), "success")
if gvp('updatestat') == 'Y':
msg = DoSyncStatUpdate()
flash("Taxon statistics update : " + msg,
"success" if msg == 'ok' else 'error')
# txt="<script>location.reload(true);</script>" # non car ça reprovoque le post de l'arrivée initiale
txt = "<script>window.location=window.location;</script>"
except:
msg = "Error while syncing {}".format(sys.exc_info())
app.logger.error(msg)
txt += appli.ErrorFormat(msg)
return txt
def ComputeDisplayName(TaxoList):
"""
Compute display_name column in database, for the list of provided id
:param TaxoList:
:return: None
"""
# sql="""with duplicate as (select name from taxonomy GROUP BY name HAVING count(*)>1),
# nt as (select t.id,case when t.name like '%% %%' or p.id is null then t.name
# when t.taxotype='M' and P.taxotype='M' then concat(p2.name||'>',p.name||'>',t.name)
# when t.name in (select name from duplicate) then concat(p.name||'>',t.name)
# else t.name end
# ||case when t.taxostatus='D' then ' (Deprecated)' else '' end
# newname
# from taxonomy t
# left JOIN taxonomy p on t.parent_id=p.id
# left JOIN taxonomy p2 on p.parent_id=p2.id
# where (t.id = any ( %(taxo)s ) or p.id = any ( %(taxo)s ) or p2.id = any ( %(taxo)s ))
# )
# update public.taxonomy t set display_name=newname,lastupdate_datetime=to_timestamp(%(ts)s,'YYYY-MM-DD HH24:MI:SS')
# from nt
# where nt.id=t.id and display_name IS DISTINCT FROM newname """
# # Pour chaque nom on cherche à determiner à quelle hauteur il n'y a plus de doublons
# # quand plus de 2 doublons ça peut conduire à une inflation car on va prendre le plus long pour tous
# # alors que par forcement necessaire ex : A<B , A<C<D , A<C<E A<B sera A<B<X inutilement rallongé
# sql="""
# with duplicate as (
# select t.name, count(distinct t.id) cid,
# count(distinct concat(t.name,'<'||p.name)) c2,
# count(distinct concat(t.name,'<'||p.name,'<'||p2.name)) c3,
# count(distinct concat(t.name,'<'||p.name,'<'||p2.name,'<'||p3.name)) c4
# from taxonomy t
# left JOIN taxonomy p on t.parent_id=p.id
# left JOIN taxonomy p2 on p.parent_id=p2.id
# left JOIN taxonomy p3 on p2.parent_id=p3.id
# group by t.name
# having count(distinct t.id)>1 )
# ,nt as (select t.id,case when d.name is null then t.name
# when cid=c2 then concat(t.name,'<'||p.name)
# when cid=c3 then concat(t.name,'<'||p.name,'<'||p2.name)
# else concat(t.name,'<'||p.name,'<'||p2.name,'<'||p3.name)
# end newname
# from taxonomy t
# left JOIN duplicate d on t.name=d.name
# left JOIN taxonomy p on t.parent_id=p.id
# left JOIN taxonomy p2 on p.parent_id=p2.id
# left JOIN taxonomy p3 on p2.parent_id=p3.id
# where (t.id = any ( %(taxo)s ) or p.id = any ( %(taxo)s ) or p2.id = any(%(taxo)s) or p3.id = any(%(taxo)s) )
# )
# update public.taxonomy t set display_name=newname
# from nt
# where nt.id=t.id
#
# """
# database.ExecSQL(sql,{'taxo':TaxoList,'ts':datetime.datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S')})
# on recalcule tous les doublons + ceux qui n'ont pas de noms + ceux ayant le même nom que ceux demandés dans leur lineage 3.
sql = """with duplicate as (select lower(name) as name from taxonomy GROUP BY lower(name) HAVING count(*)>1)
select t.id,t.name tname,p.name pname,p2.name p2name,p3.name p3name,t.display_name,t.taxostatus
from taxonomy t
left JOIN duplicate d on lower(t.name)=d.name
left JOIN taxonomy p on t.parent_id=p.id
left JOIN taxonomy p2 on p.parent_id=p2.id
left JOIN taxonomy p3 on p2.parent_id=p3.id
where d.name is not null or t.display_name is null
or lower(t.name) in (select lower(st.name)
from taxonomy st
left JOIN taxonomy sp on st.parent_id=sp.id
left JOIN taxonomy sp2 on sp.parent_id=sp2.id
left JOIN taxonomy sp3 on sp2.parent_id=sp3.id
where (st.id=any(%(taxo)s) or sp.id=any(%(taxo)s) or sp2.id=any(%(taxo)s) or sp3.id=any(%(taxo)s) )
)
"""
Duplicates = database.GetAll(sql, {'taxo': TaxoList},
cursor_factory=psycopg2.extras.RealDictCursor)
starttime = datetime.datetime.now()
DStats = {}
def AddToDefStat(clestat):
clestat = clestat.lower()
if clestat in DStats:
DStats[clestat] += 1
else:
DStats[clestat] = 1
for D in Duplicates:
cle = ntcv(D['tname'])
AddToDefStat(cle)
cle += '<' + ntcv(D['pname'])
AddToDefStat(cle)
cle += '<' + ntcv(D['p2name'])
AddToDefStat(cle)
cle += '<' + ntcv(D['p3name'])
AddToDefStat(cle)
for i, D in enumerate(Duplicates):
cle = ntcv(D['tname'])
if DStats[cle.lower()] == 1:
Duplicates[i]['newname'] = cle
else:
cle += '<' + ntcv(D['pname'])
if DStats[cle.lower()] == 1:
Duplicates[i]['newname'] = cle
else:
cle += '<' + ntcv(D['p2name'])
if DStats[cle.lower()] == 1:
Duplicates[i]['newname'] = cle
else:
cle += '<' + ntcv(D['p3name'])
Duplicates[i]['newname'] = cle
if D['taxostatus'] == 'D':
Duplicates[i]['newname'] += " (Deprecated)"
app.logger.debug("Compute time %s ",
(datetime.datetime.now() - starttime).total_seconds())
starttime = datetime.datetime.now()
UpdateParam = []
for D in Duplicates:
if D['display_name'] != D['newname']:
UpdateParam.append((int(D['id']), D['newname']))
if len(UpdateParam) > 0:
cur = g.db.cursor()
psycopg2.extras.execute_values(
cur,
"""UPDATE taxonomy SET display_name = data.pdisplay_name,lastupdate_datetime=to_timestamp('{}','YYYY-MM-DD HH24:MI:SS')
FROM (VALUES %s) AS data (pid, pdisplay_name)
WHERE id = data.pid""".format(
datetime.datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S')),
UpdateParam)
cur.connection.commit()
cur.close()
app.logger.debug("Update time %s for %d rows",
(datetime.datetime.now() - starttime).total_seconds(),
len(UpdateParam))
def Partstatsample():
samples = GetFilteredSamples()
if len(samples) == 0:
return "No Data selected"
sampleinclause = ",".join([str(x[0]) for x in samples])
data = {
'nbrsample': len(samples),
'nbrvisible': sum(1 for x in samples if x['visible'])
}
data['nbrnotvisible'] = data['nbrsample'] - data['nbrvisible']
sqlvisible, sqljoin = GetSQLVisibility()
data['partprojcount'] = database.GetAll(
"""SELECT pp.ptitle,count(*) nbr,pp.do_email,do_name,qpp.email,qpp.name,pp.instrumtype,pp.pprojid
,count(ps.sampleid ) nbrtaxo
,p.visible,visibility,uppowner.name uppowner_name,uppowner.email uppowner_email
from part_samples ps
--join part_projects pp on ps.pprojid=pp.pprojid
join (select pp.*,cast ({1} as varchar(2) ) as visibility
from part_projects pp
LEFT JOIN projects p on pp.projid = p.projid
{2}
) pp on ps.pprojid=pp.pprojid
left join ( select * from (
select u.email,u.name,pp.projid,rank() OVER (PARTITION BY pp.projid ORDER BY pp.id) rang
from projectspriv pp join users u on pp.member=u.id
where pp.privilege='Manage' and u.active=true ) q where rang=1
) qpp on qpp.projid=pp.projid
LEFT JOIN projects p on pp.projid = p.projid
LEFT JOIN users uppowner on pp.ownerid=uppowner.id
where ps.psampleid in ({0} )
group by pp.ptitle,pp.do_email,do_name,qpp.email,qpp.name,p.visible,pp.instrumtype,pp.pprojid,visibility,uppowner.name,uppowner.email
order by pp.ptitle""".format(sampleinclause, sqlvisible, sqljoin))
data['instrumcount'] = database.GetAll(
"""SELECT coalesce(pp.instrumtype,'not defined') instrum,count(*) nbr
from part_samples ps
join part_projects pp on ps.pprojid=pp.pprojid
where ps.psampleid in ({0} )
group by pp.instrumtype
order by pp.instrumtype""".format(sampleinclause))
data['taxoprojcount'] = database.GetAll(
"""SELECT coalesce(p.title,'not associated') title,p.projid,count(*) nbr
from part_samples ps
join part_projects pp on ps.pprojid=pp.pprojid
left join projects p on pp.projid=p.projid
where ps.psampleid in ({0} )
group by p.title,p.projid
order by p.title""".format(sampleinclause))
# data['taxostat']=database.GetAll("""select round(100*count(case when nbr=nbrval then 1 end)/count(*),1) pctval100pct
# ,round(100*count(case when nbrval>0 and nbr=nbrval then 1 end)/count(*),1) pctpartval
# ,round(100*sum(nbrval)/sum(nbr),1) as pctobjval
# from (SELECT ps.sampleid,count(*) nbr,count(case when classif_qual='V' then 1 end) nbrval
# from part_samples ps
# join obj_head oh on oh.sampleid=ps.sampleid
# where ps.psampleid in ({0})
# group by ps.sampleid )q
# having count(*)>0
# """.format(sampleinclause))
# if len(data['taxostat'])==0:
# data['taxostat'] ={}
# else:
# data['taxostat']={k: float(v) for k, v in data['taxostat'][0].items()}
TaxoStat = database.GetAll(
"""SELECT ps.sampleid,count(*) nbr,count(case when classif_qual='V' then 1 end) nbrval,ps.pprojid
from part_samples ps
join obj_head oh on oh.sampleid=ps.sampleid
where ps.psampleid in ({0})
group by ps.sampleid,ps.pprojid """.format(sampleinclause))
ResultTaxoStat = {
'nbrobj': 0,
'nbrobjval': 0,
'pctval100pct': 0,
'pctpartval': 0,
'pctobjval': 0
}
TaxoStatByProj = {}
for r in TaxoStat:
ResultTaxoStat['nbrobj'] += r['nbr']
ResultTaxoStat['nbrobjval'] += r['nbrval']
if r['pprojid'] not in TaxoStatByProj:
TaxoStatByProj[r['pprojid']] = {
'nbrobj': 0,
'nbrobjval': 0,
'pctobjval': 0
}
TaxoStatByProj[r['pprojid']]['nbrobj'] += r['nbr']
TaxoStatByProj[r['pprojid']]['nbrobjval'] += r['nbrval']
if r['nbr'] == r['nbrval']: ResultTaxoStat['pctval100pct'] += 1
if r['nbr'] > r['nbrval']: ResultTaxoStat['pctpartval'] += 1
if len(TaxoStat) > 0:
ResultTaxoStat['pctval100pct'] = round(
100 * ResultTaxoStat['pctval100pct'] / len(TaxoStat), 1)
ResultTaxoStat['pctpartval'] = round(
100 * ResultTaxoStat['pctpartval'] / len(TaxoStat), 1)
if ResultTaxoStat['nbrobj'] > 0:
ResultTaxoStat['pctobjval'] = round(
100 * ResultTaxoStat['nbrobjval'] / ResultTaxoStat['nbrobj'], 1)
data['taxostat'] = ResultTaxoStat
data['taxostatbyproj'] = {}
for k, r in TaxoStatByProj.items():
if r['nbrobj']:
data['taxostatbyproj'][k] = round(
100 * r['nbrobjval'] / r['nbrobj'], 1)
print(data['taxostatbyproj'])
data['depthhisto'] = database.GetAll(
"""SELECT coalesce(case when bottomdepth<500 then '0- 500' else trunc(bottomdepth,-3)||'-'||(trunc(bottomdepth,-3)+1000) end,'Not defined') slice
, count(*) nbr
from (select ps.psampleid,cast(coalesce(max(depth),ps.bottomdepth) as NUMERIC) bottomdepth
from part_samples ps
left join part_histopart_reduit phr on ps.psampleid = phr.psampleid
where ps.psampleid in ({0})
group by ps.psampleid) ps
group by slice order by slice""".format(sampleinclause))
data['taxolist'] = database.GetAll("""
select classif_id,t.name nom
,concat(t14.name||'>',t13.name||'>',t12.name||'>',t11.name||'>',t10.name||'>',t9.name||'>',t8.name||'>',t7.name||'>',
t6.name||'>',t5.name||'>',t4.name||'>',t3.name||'>',t2.name||'>',t1.name||'>',t.name) tree
from (SELECT distinct hl.classif_id
from part_samples ps
join part_histocat_lst hl on ps.psampleid = hl.psampleid
where ps.psampleid in ({0} ) ) cat
join taxonomy t on cat.classif_id=t.id
left join taxonomy t1 on t.parent_id=t1.id
left join taxonomy t2 on t1.parent_id=t2.id
left join taxonomy t3 on t2.parent_id=t3.id
left join taxonomy t4 on t3.parent_id=t4.id
left join taxonomy t5 on t4.parent_id=t5.id
left join taxonomy t6 on t5.parent_id=t6.id
left join taxonomy t7 on t6.parent_id=t7.id
left join taxonomy t8 on t7.parent_id=t8.id
left join taxonomy t9 on t8.parent_id=t9.id
left join taxonomy t10 on t9.parent_id=t10.id
left join taxonomy t11 on t10.parent_id=t11.id
left join taxonomy t12 on t11.parent_id=t12.id
left join taxonomy t13 on t12.parent_id=t13.id
left join taxonomy t14 on t13.parent_id=t14.id
order by tree""".format(sampleinclause))
return render_template('part/stats.html', data=data, raw=json.dumps(data))
