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 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 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 routesetstat():
CheckInstanceSecurity()
id_instance = int(gvp('id_instance'))
database.ExecSQL("delete from ecotaxainststat WHERE id_instance=%s" %
id_instance)
# sql="insert into ecotaxainststat(id_instance, id_taxon, nbr) VALUES ({},%s,%s)".format(id_instance)
sql = "insert into ecotaxainststat(id_instance, id_taxon, nbr) VALUES "
stat = json.loads(gvp('data'))
if len(stat) > 0: # to avoid error on empty DB
sql += ",".join("({},{},{})".format(id_instance, int(k), int(v))
for (k, v) in stat.items())
database.ExecSQL(sql)
database.ExecSQL(
"update ecotaxainst set laststatupdate_datetime=timezone('utc',now()),ecotaxa_version=%s WHERE id=%s",
[g.ecotaxa_version, id_instance])
PurgeDeprecatedTaxon()
return jsonify(msg='ok', msgversion=g.MsgVersion)
def dbcreate():
with app.app_context(
): # Création d'un contexte pour utiliser les fonction GetAll,ExecSQL qui mémorisent
g.db = None
db.create_all()
from flask_migrate import stamp
stamp(revision='head')
database.ExecSQL("""create view objects as select oh.*,ofi.*
from obj_head oh left join obj_field ofi on oh.objid=ofi.objfid""")
def delete_sample(psampleid):
RawHistoFile = Path(sample_import.GetPathForRawHistoFile(psampleid))
if RawHistoFile.exists():
RawHistoFile.unlink()
model = partdatabase.part_samples.query.filter_by(
psampleid=psampleid).first()
for t in ('part_histopart_reduit', 'part_histopart_det', 'part_histocat',
'part_histocat_lst', 'part_ctd'):
database.ExecSQL("delete from " + t + " where psampleid=" +
str(model.psampleid))
db.session.delete(model)
db.session.commit()
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 browseinstancedelpopup():
try:
instance_id = gvp('id')
instance = database.EcotaxaInst.query.filter_by(id=instance_id).first()
if instance is None:
raise Exception("Instance not found in Database")
database.ExecSQL("delete from ecotaxainststat where id_instance=%s",
[instance.id])
db.session.delete(instance)
db.session.commit()
return "<script>location.reload(true);</script>"
except Exception as e:
tb_list = traceback.format_tb(e.__traceback__)
return FormatError("Saving Error : {}\n{}", e,
"__BR__".join(tb_list[::-1]))
def ProcessLig():
nonlocal LigData,InsSQL,upcur,LigID,pca
# pour générer un fichier de test
# pca = PCA(n_components=50)
# pca.fit(np.array(LigData))
# pca_fn = model_dir/"feature_pca.jbl"
# joblib.dump(pca, pca_fn)
pcares=pca.transform(np.array(LigData))
SQLParam=[ {"cnn%02d"%(i+1):float(x) for i,x in enumerate(feat)} for feat in pcares ]
for i in range(len(SQLParam)):
SQLParam[i]['objcnnid']=LigID[i]
database.ExecSQL("delete from obj_cnn_features where objcnnid= any(%s)",(LigID,))
upcur.executemany(InsSQL,SQLParam)
upcur.connection.commit()
LigData = []
LigID = []
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 browsetaxosavepopup():
txt = ""
try:
# txt = json.dumps(request.form)
taxonid = int(gvp('id'))
if taxonid > 0:
taxon = database.Taxonomy.query.filter_by(id=taxonid).first()
if taxon is None:
raise Exception("Taxon not found in Database")
else:
raise Exception("Taxon missing")
taxon.source_url = gvp('source_url')
taxon.name = gvp('name')
taxon.parent_id = gvp('parent_id')
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')
taxon.taxostatus = gvp('taxostatus')
taxon.rename_to = gvp('rename_to') or None
db.session.commit()
ComputeDisplayName([taxonid])
database.ExecSQL(
"UPDATE public.taxonomy t SET lastupdate_datetime=to_timestamp(%s,'YYYY-MM-DD HH24:MI:SS') WHERE id=%s",
[
datetime.datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S'),
taxonid
])
# txt=FormatSuccess("POST = {}",txt)
txt = """<script>
$('#tbl').DataTable().draw();
At2PopupClose(0);
</script>"""
return txt
except Exception as e:
tb_list = traceback.format_tb(e.__traceback__)
return FormatError("Saving Error : {}\n{}", e,
"__BR__".join(tb_list[::-1]))
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 GenerateParticleHistogram(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:
"""
UvpSample = partdatabase.part_samples.query.filter_by(
psampleid=psampleid).first()
if UvpSample is None:
raise Exception("GenerateParticleHistogram: Sample %d missing" %
psampleid)
if not UvpSample.histobrutavailable:
raise Exception(
"GenerateParticleHistogram: Sample %d Particle Histogram can't be computed without Raw histogram"
% psampleid)
Prj = partdatabase.part_projects.query.filter_by(
pprojid=UvpSample.pprojid).first()
ServerRoot = Path(app.config['SERVERLOADAREA'])
DossierUVPPath = ServerRoot / Prj.rawfolder
FirstImage = UvpSample.firstimage
LastImage = UvpSample.lastimgused
if LastImage is None: # Si aucune determinée lors de la génération de l'histogramme brut, on prend celle spécifiée dans le sample.
LastImage = UvpSample.lastimg
if FirstImage is None or LastImage is None:
raise Exception(
"GenerateParticuleHistogram sample %s first or last image undefined %s-%s"
% (UvpSample.profileid, FirstImage, LastImage))
DetHistoFile = GetPathForRawHistoFile(psampleid)
logging.info(
"GenerateParticleHistogram processing raw histogram file %s" %
DetHistoFile)
Part = np.loadtxt(DetHistoFile, delimiter='\t', skiprows=1)
# format de raw 0:depth,1:imgcount,2:area,3:nbr,4:greylimit1,greylimit2,greylimit3
# 1 Ligne par mètre et area, ne contient les données entre fist et last
MinDepth = Part[:, 0].min()
# ajout d'attributs calculés pour chaque ligne du fichier.
PartCalc = np.empty(
[Part.shape[0],
3]) # col0 = tranche, Col1=ESD, Col2=Biovolume en µl=mm3
PartCalc[:,
0] = Part[:,
0] // 5 #calcul de la tranche 0 pour [0m..5m[,1 pour [5m..10m[
PartCalc[:, 1] = 2 * np.sqrt(
(pow(Part[:, 2], UvpSample.acq_exp) * UvpSample.acq_aa) / np.pi)
PartCalc[:, 2] = Part[:, 3] * pow(PartCalc[:, 1] / 2, 3) * 4 * math.pi / 3
LastTranche = PartCalc[:, 0].max()
# on récupere les 1ère ligne de chaque mètre afin de calculer le volume d'eau
FirstLigByDepth = Part[np.unique(Part[:, 0], return_index=True)[1]]
# on calcule le volume de chaque tranche (y compris celle qui n'existent pas en 0 et la profondeur maxi)
VolumeParTranche = np.bincount(
(FirstLigByDepth[:, 0] // 5).astype(int), FirstLigByDepth[:, 1]
) * UvpSample.acq_volimage # Bin par tranche de tranche 5m partant de 0m
MetreParTranche = np.bincount(
(FirstLigByDepth[:, 0] //
5).astype(int)) # Bin par tranche de tranche 5m partant de 0m
# On supprime les tranches vides, mais ça fait planter les graphes suivants
# VolumeParTranche=VolumeParTranche[np.nonzero(VolumeParTranche)]
# MetreParTranche = MetreParTranche[np.nonzero(VolumeParTranche)]
(PartByClassAndTranche, bins, binsdept) = np.histogram2d(
PartCalc[:, 1],
PartCalc[:, 0],
bins=(PartDetClassLimit, np.arange(0, VolumeParTranche.shape[0] + 1)),
weights=Part[:, 3])
(BioVolByClassAndTranche, bins, binsdept) = np.histogram2d(
PartCalc[:, 1],
PartCalc[:, 0],
bins=(PartDetClassLimit, np.arange(0, VolumeParTranche.shape[0] + 1)),
weights=PartCalc[:, 2])
with np.errstate(
divide='ignore', invalid='ignore'
): # masque les warning provoquées par les divisions par 0 des tranches vides.
BioVolByClassAndTranche /= VolumeParTranche
font = {'family': 'arial', 'weight': 'normal', 'size': 10}
plt.rc('font', **font)
plt.rcParams['lines.linewidth'] = 0.5
Fig = plt.figure(
figsize=(16, 12),
dpi=100,
)
# calcul volume par metre moyen de chaque tranche
ax = Fig.add_subplot(241)
# si une tranche n'as pas été entierement explorée la /5 est un calcul éronné
ax.plot(VolumeParTranche / MetreParTranche,
np.arange(len(VolumeParTranche)) * -5 - 2.5)
ax.set_xticks(GetTicks((VolumeParTranche / 5).max()))
ax.set_xlabel('Volume/M')
ax.set_ylabel('Depth(m)')
# Calcul Particle <=0.53
Filtre = np.argwhere(PartCalc[:, 1] <= 0.53)
ax = Fig.add_subplot(242)
(n, bins) = np.histogram(PartCalc[Filtre, 0],
np.arange(len(VolumeParTranche) + 1),
weights=Part[Filtre, 3])
n = n / VolumeParTranche
ax.plot(n, bins[:-1] * -5 - MinDepth - 2.5)
ax.set_xticks(GetTicks(n.max()))
ax.set_xlabel('Part 0.06-0.53 mm esd # l-1')
ax.set_ylabel('Depth(m)')
# Calcul Particle 0.53->1.06
Filtre = np.argwhere((PartCalc[:, 1] >= 0.53)
& (PartCalc[:, 1] <= 1.06))
ax = Fig.add_subplot(243)
(n, bins) = np.histogram(PartCalc[Filtre, 0],
np.arange(len(VolumeParTranche) + 1),
weights=Part[Filtre, 3])
n = n / VolumeParTranche
ax.plot(n, bins[:-1] * -5 - MinDepth - 2.5)
ax.set_xticks(GetTicks(n.max()))
ax.set_xlabel('Part 0.53-1.06 mm esd # l-1')
ax.set_ylabel('Depth(m)')
# Calcul Particle 1.06->2.66
Filtre = np.argwhere((PartCalc[:, 1] >= 1.06)
& (PartCalc[:, 1] <= 2.66))
ax = Fig.add_subplot(244)
(n, bins) = np.histogram(PartCalc[Filtre, 0],
np.arange(len(VolumeParTranche) + 1),
weights=Part[Filtre, 3])
n = n / VolumeParTranche
ax.plot(n, bins[:-1] * -5 - MinDepth - 2.5)
ax.set_xticks(GetTicks(n.max()))
ax.set_xlabel('Part 1.06-2.66 mm esd # l-1')
ax.set_ylabel('Depth(m)')
# Calcul Biovolume Particle >0.512-<=1.02 mm via histograme
n = np.sum(BioVolByClassAndTranche[28:30, :], axis=0)
ax = Fig.add_subplot(245)
ax.plot(n, np.arange(0, LastTranche + 1) * -5 - 2.5)
ax.set_xticks(GetTicks(n.max()))
ax.set_xlabel('Part >=0.512-<1.02 mm esd mm3 l-1 from det histo')
ax.set_ylabel('Depth(m)')
# Calcul Particle <=0.512 mm via histograme
n = np.sum(PartByClassAndTranche[0:28, :], axis=0)
ax = Fig.add_subplot(246)
n = n / VolumeParTranche
ax.plot(n, np.arange(0, LastTranche + 1) * -5 - 2.5)
ax.set_xticks(GetTicks(n.max()))
ax.set_xlabel('Part <0.512 mm esd # l-1 from det histo')
ax.set_ylabel('Depth(m)')
# Calcul Particle >0.512-<=1.02 mm via histograme
n = np.sum(PartByClassAndTranche[28:30, :], axis=0)
ax = Fig.add_subplot(247)
n = n / VolumeParTranche
ax.plot(n, np.arange(0, LastTranche + 1) * -5 - 2.5)
ax.set_xticks(GetTicks(n.max()))
ax.set_xlabel('Part >=0.512-<1.02 mm esd # l-1 from det histo')
ax.set_ylabel('Depth(m)')
# Calcul Particle >0.512-<=1.02 mm via histograme
n = np.sum(PartByClassAndTranche[30:34, :], axis=0)
ax = Fig.add_subplot(248)
n = n / VolumeParTranche
ax.plot(n, np.arange(0, LastTranche + 1) * -5 - 2.5)
ax.set_xticks(GetTicks(n.max()))
ax.set_xlabel('Part >=1.02-<2.58 mm esd # l-1 from det histo')
ax.set_ylabel('Depth(m)')
# Fig.savefig((DossierUVPPath / 'results' / ('ecotaxa_particle_' + UvpSample.profileid+'.png')).as_posix())
Fig.text(0.05,
0.99,
"Project : %s , Profile %s , Filename : %s" %
(Prj.ptitle, UvpSample.profileid, UvpSample.filename),
ha='left')
# Fig.suptitle("Project %s : Profile %s"%(Prj.ptitle,UvpSample.profileid), ha='center')
Fig.tight_layout(rect=(
0, 0, 1,
0.99)) # permet de laisser un peu de blanc en haut pour le titre
Fig.savefig(GetPathForImportGraph(psampleid, 'particle'))
Fig.clf()
database.ExecSQL("delete from part_histopart_det where psampleid=" +
str(psampleid))
sql = """insert into part_histopart_det(psampleid, lineno, depth, watervolume
, class01, class02, class03, class04, class05, class06, class07, class08, class09, class10, class11, class12, class13, class14
, class15, class16, class17, class18, class19, class20, class21, class22, class23, class24, class25, class26, class27, class28, class29
, class30, class31, class32, class33, class34, class35, class36, class37, class38, class39, class40, class41, class42, class43, class44, class45
, biovol01, biovol02, biovol03, biovol04, biovol05, biovol06, biovol07, biovol08, biovol09, biovol10, biovol11, biovol12, biovol13, biovol14
, biovol15, biovol16, biovol17, biovol18, biovol19, biovol20, biovol21, biovol22, biovol23, biovol24, biovol25, biovol26, biovol27, biovol28, biovol29
, biovol30, biovol31, biovol32, biovol33, biovol34, biovol35, biovol36, biovol37, biovol38, biovol39, biovol40, biovol41, biovol42, biovol43, biovol44, biovol45
)
values(%(psampleid)s,%(lineno)s,%(depth)s,%(watervolume)s
,%(class01)s,%(class02)s,%(class03)s,%(class04)s,%(class05)s,%(class06)s
,%(class07)s,%(class08)s,%(class09)s,%(class10)s,%(class11)s,%(class12)s,%(class13)s,%(class14)s,%(class15)s,%(class16)s,%(class17)s
,%(class18)s,%(class19)s,%(class20)s,%(class21)s,%(class22)s,%(class23)s,%(class24)s,%(class25)s,%(class26)s,%(class27)s,%(class28)s
,%(class29)s,%(class30)s,%(class31)s,%(class32)s,%(class33)s,%(class34)s,%(class35)s,%(class36)s,%(class37)s,%(class38)s,%(class39)s
,%(class40)s,%(class41)s,%(class42)s,%(class43)s,%(class44)s,%(class45)s
,%(biovol01)s,%(biovol02)s,%(biovol03)s,%(biovol04)s,%(biovol05)s,%(biovol06)s
,%(biovol07)s,%(biovol08)s,%(biovol09)s,%(biovol10)s,%(biovol11)s,%(biovol12)s,%(biovol13)s,%(biovol14)s,%(biovol15)s,%(biovol16)s,%(biovol17)s
,%(biovol18)s,%(biovol19)s,%(biovol20)s,%(biovol21)s,%(biovol22)s,%(biovol23)s,%(biovol24)s,%(biovol25)s,%(biovol26)s,%(biovol27)s,%(biovol28)s
,%(biovol29)s,%(biovol30)s,%(biovol31)s,%(biovol32)s,%(biovol33)s,%(biovol34)s,%(biovol35)s,%(biovol36)s,%(biovol37)s,%(biovol38)s,%(biovol39)s
,%(biovol40)s,%(biovol41)s,%(biovol42)s,%(biovol43)s,%(biovol44)s,%(biovol45)s
)"""
sqlparam = {'psampleid': psampleid}
for i, r in enumerate(VolumeParTranche):
sqlparam['lineno'] = i
sqlparam['depth'] = (i * 5 + 2.5)
if VolumeParTranche[
i] == 0: # On ne charge pas les lignes sans volume d'eau car ça signifie qu'l n'y a pas eu d'échantillon
continue
sqlparam['watervolume'] = VolumeParTranche[i]
for k in range(0, 45):
sqlparam['class%02d' % (k + 1)] = PartByClassAndTranche[k, i]
for k in range(0, 45):
sqlparam['biovol%02d' % (k + 1)] = BioVolByClassAndTranche[k, i]
database.ExecSQL(sql, sqlparam)
GenerateReducedParticleHistogram(psampleid)
def ImportCTD(psampleid, user_name, user_email):
"""
Importe les données CTD
:param psampleid:
:return:
"""
UvpSample = partdatabase.part_samples.query.filter_by(
psampleid=psampleid).first()
if UvpSample is None:
raise Exception("ImportCTD: Sample %d missing" % psampleid)
Prj = partdatabase.part_projects.query.filter_by(
pprojid=UvpSample.pprojid).first()
ServerRoot = Path(app.config['SERVERLOADAREA'])
DossierUVPPath = ServerRoot / Prj.rawfolder
CtdFile = DossierUVPPath / "ctd_data_cnv" / (UvpSample.profileid + ".ctd")
if not CtdFile.exists():
app.logger.info("CTD file %s missing", CtdFile.as_posix())
return False
app.logger.info("Import CTD file %s", CtdFile.as_posix())
with CtdFile.open('r', encoding='latin_1') as tsvfile:
Rdr = csv.reader(tsvfile, delimiter='\t')
HeadRow = Rdr.__next__()
# Analyser la ligne de titre et assigner à chaque ID l'attribut
# Construire la table d'association des attributs complémentaires.
ExtramesID = 0
Mapping = []
ExtraMapping = {}
for ic, c in enumerate(HeadRow):
clow = c.lower().strip()
if clow in CTDFixedCol:
Target = CTDFixedCol[clow]
else:
ExtramesID += 1
Target = 'extrames%02d' % ExtramesID
ExtraMapping['%02d' % ExtramesID] = c
if ExtramesID > 20:
raise Exception(
"ImportCTD: Too much CTD data, column %s skipped" % c)
Mapping.append(Target)
app.logger.info("Mapping = %s", Mapping)
database.ExecSQL("delete from part_ctd where psampleid=%s" % psampleid)
for i, r in enumerate(Rdr):
cl = partdatabase.part_ctd()
cl.psampleid = psampleid
cl.lineno = i
for i, c in enumerate(Mapping):
v = CleanValue(r[i])
if v != '':
if c == 'qc_flag':
setattr(cl, c, int(float(v)))
elif c == 'datetime':
setattr(
cl, c,
datetime.datetime(int(v[0:4]), int(v[4:6]),
int(v[6:8]), int(v[8:10]),
int(v[10:12]), int(v[12:14]),
int(v[14:17]) * 1000))
else:
setattr(cl, c, v)
db.session.add(cl)
db.session.commit()
UvpSample.ctd_desc = EncodeEqualList(ExtraMapping)
UvpSample.ctd_import_datetime = datetime.datetime.now()
UvpSample.ctd_import_name = user_name
UvpSample.ctd_import_email = user_email
db.session.commit()
return True
def SPStep1(self):
logging.info("Input Param = %s" % (self.param.__dict__))
logging.info("Start Step 1")
# self.param.IntraStep=0
if getattr(self.param, 'IntraStep', 0) == 0:
fichier = os.path.join(self.GetWorkingDir(), "uploaded.txt")
logging.info("Analyzing file %s" % (fichier))
with open(fichier, encoding='latin_1') as csvfile:
# lecture en mode dictionnaire basé sur la premiere ligne
rdr = csv.reader(
csvfile,
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:
logging.info("Inserted %s lines" % RowCount)
RowCount += 1
logging.info("count=%d" % RowCount)
self.param.IntraStep = 1
if self.param.IntraStep == 1:
# 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)"""
)
logging.info("%d Nodes already exists " % n)
# insertion des nouveaux noeud racines
n = ExecSQL(
"""INSERT INTO taxonomy (id, parent_id, name, id_source)
select nextval('seq_taxonomy'),NULL,t.name,t.idtaxo from temp_taxo t where idparent='-1' and idfinal is null and status='1'"""
)
logging.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'""")
logging.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)
select nextval('seq_taxonomy'),ttp.id,tt.name,tt.idtaxo
from temp_taxo tt join taxonomy ttp on tt.idparent=ttp.id_source
where tt.idfinal is null and status='1'""")
if n == 0:
logging.info("No more data to import")
break
else:
logging.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 """)
logging.info("Updated %d inserted Child Nodes" % n)
n = ExecSQL("""UPDATE taxonomy tf set name=tt.name
from temp_taxo tt where tf.id_source=tt.idtaxo
and tt.status='1' and tf.name!=tt.name""")
logging.info("Updated %d Nodes names" % n)
n = ExecSQL("""UPDATE taxonomy tfu set parent_id=sq.idfinal
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"""
)
logging.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:
logging.info("No more data to delete")
break
else:
logging.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:
logging.info("Can't Delete '%s' because it's used " % r[0])
self.task.taskstate = "Done"
self.UpdateProgress(100, "Processing done")
def GenerateParticleHistogram(psampleid):
"""
Génération de l'histogramme particulaire détaillé (45 classes) et réduit (15 classes) à partir du fichier ASC
:param psampleid:
:return:
"""
PartSample = partdatabase.part_samples.query.filter_by(
psampleid=psampleid).first()
if PartSample is None:
raise Exception("GenerateRawHistogram: Sample %d missing" % psampleid)
LISSTClass = BuildLISSTClass(PartSample.lisst_kernel)
Prj = partdatabase.part_projects.query.filter_by(
pprojid=PartSample.pprojid).first()
ServerRoot = Path(app.config['SERVERLOADAREA'])
DossierUVPPath = ServerRoot / Prj.rawfolder
Fichier = DossierUVPPath / "work" / (PartSample.filename + '.asc')
logging.info("Processing file " + Fichier.as_posix())
NbrLine = 0
with Fichier.open() as csvfile:
for L in csvfile:
NbrLine += 1
logging.info("Line count %d" % NbrLine)
# Col0=Nbr Data pour calculer la moyenne , 1->45 Biovol cumulé puis moyénné
HistoByTranche = np.zeros((1, 46))
with Fichier.open() as csvfile:
Rdr = csv.reader(csvfile, delimiter=' ')
for i, row in enumerate(Rdr):
Depth = float(row[36])
if PartSample.organizedbydeepth:
Tranche = Depth // 5
else:
Tranche = i // 50
Part = np.empty(32)
for k in range(0, 32):
Part[k] = float(row[k])
# Mappe les 32 classes et retourne les 45 classes
Part = MapClasses(Part, LISSTClass)
# Todo gérer l'horodatage des tranche en profil horizontal
if Tranche >= HistoByTranche.shape[0]:
NewArray = np.zeros((Tranche + 1, 46))
NewArray[0:HistoByTranche.shape[0], :] = HistoByTranche
HistoByTranche = NewArray
HistoByTranche[Tranche, 1:46] += Part
HistoByTranche[Tranche, 0] += 1
HistoByTranche[:, 1:46] /= HistoByTranche[:, 0, np.newaxis]
database.ExecSQL("delete from part_histopart_det where psampleid=" +
str(psampleid))
sql = """insert into part_histopart_det(psampleid, lineno, depth, watervolume
, biovol01, biovol02, biovol03, biovol04, biovol05, biovol06, biovol07, biovol08, biovol09, biovol10, biovol11, biovol12, biovol13, biovol14
, biovol15, biovol16, biovol17, biovol18, biovol19, biovol20, biovol21, biovol22, biovol23, biovol24, biovol25, biovol26, biovol27, biovol28, biovol29
, biovol30, biovol31, biovol32, biovol33, biovol34, biovol35, biovol36, biovol37, biovol38, biovol39, biovol40, biovol41, biovol42, biovol43, biovol44, biovol45)
values(%(psampleid)s,%(lineno)s,%(depth)s,%(watervolume)s,%(biovol01)s,%(biovol02)s,%(biovol03)s,%(biovol04)s,%(biovol05)s,%(biovol06)s
,%(biovol07)s,%(biovol08)s,%(biovol09)s,%(biovol10)s,%(biovol11)s,%(biovol12)s,%(biovol13)s,%(biovol14)s,%(biovol15)s,%(biovol16)s,%(biovol17)s
,%(biovol18)s,%(biovol19)s,%(biovol20)s,%(biovol21)s,%(biovol22)s,%(biovol23)s,%(biovol24)s,%(biovol25)s,%(biovol26)s,%(biovol27)s,%(biovol28)s
,%(biovol29)s,%(biovol30)s,%(biovol31)s,%(biovol32)s,%(biovol33)s,%(biovol34)s,%(biovol35)s,%(biovol36)s,%(biovol37)s,%(biovol38)s,%(biovol39)s
,%(biovol40)s,%(biovol41)s,%(biovol42)s,%(biovol43)s,%(biovol44)s,%(biovol45)s)"""
sqlparam = {'psampleid': psampleid}
for i, r in enumerate(HistoByTranche):
sqlparam['lineno'] = i
#Todo ajuste au type
sqlparam['depth'] = (i * 5 + 2.5)
sqlparam['watervolume'] = None
# sqlparam['watervolume'] = VolumeParTranche[i]
for k in range(0, 45):
sqlparam['biovol%02d' % (k + 1)] = r[k + 1]
database.ExecSQL(sql, sqlparam)
GenerateReducedParticleHistogram(psampleid)
def PrjManualClassif(PrjId):
request.form # Force la lecture des données POST sinon il y a une erreur 504
Changement=[]
Prj=database.Projects.query.filter_by(projid=PrjId).first()
if not Prj.CheckRight(1): # Level 0 = Read, 1 = Annotate, 2 = Admin
return '<span class="label label-danger">You cannot Annotate this project</span>'
changes={k[8:-1]:v for k,v in request.form.items() if k[0:7]=="changes"}
if len(changes)==0:
return '<span class="label label-warning">No pending change to update</span>'
sql="""select o.objid,o.classif_auto_id,o.classif_auto_when,o.classif_auto_score,o.classif_id,o.classif_qual,o.classif_when,o.classif_who
from obj_head o
where o.objid in ("""+",".join(changes.keys())+")"
prev={r['objid']:r for r in GetAll(sql,debug=False)}
sql="update obj_head set classif_id=%(classif_id)s,classif_qual=%(classif_qual)s,classif_who=%(classif_who)s,classif_when=now() where objid=%(objid)s "
# sqli="""INSERT INTO objectsclassifhisto (objid, classif_date, classif_type, classif_id, classif_qual, classif_who)
# VALUES (%(objid)s,%(classif_when)s,'M',%(classif_id)s,%(classif_qual)s,%(classif_who)s )"""
# Traitement global de l'historisation afin de réduire les commandes SQL + test qu'il n'y a pas de doublons
sqli="""INSERT INTO objectsclassifhisto(objid, classif_date, classif_type, classif_id, classif_qual, classif_who, classif_score)
SELECT objid, classif_when, 'M' classif_type, classif_id, classif_qual, classif_who, null classif_score
from obj_head oh
where objid= any(%s)
and classif_when is not null
and not exists(select 1 from objectsclassifhisto och where oh.objid=och.objid and oh.classif_when=och.classif_date )"""
try:
params=[[int(x) for x in changes.keys()] ]
ExecSQL(sqli, params, True)
except:
app.logger.warning("Unable to add historical information, non-blocking %s" % (sys.exc_info(),))
BatchParam=[]
for k,v in changes.items():
ki=int(k)
if v=="-1" or v=="" : # utilisé dans validate all
v=prev[ki]['classif_id']
if prev[ki]['classif_qual']!=gvp('qual') or prev[ki]['classif_who']!=current_user.id or prev[ki]['classif_id']!=int(v):
# il y a eu au moins un changement
params={'objid':k,'classif_id':v,'classif_who':current_user.id,'classif_qual':gvp('qual')}
BatchParam.append(params)
# ExecSQL(sql,params,False)
Changement.append({'prev_id':prev[ki]['classif_id'],'prev_qual':prev[ki]['classif_qual']
,'id':int(v),'qual':gvp('qual')})
# params={'objid':k,'classif_id':prev[ki]['classif_id'],'classif_who':prev[ki]['classif_who']
# ,'classif_qual':prev[ki]['classif_qual'],'classif_when':prev[ki]['classif_when']}
# try:
# if ntcv(params['classif_when']) !="" : # si pas de date, violation PK
# ExecSQL(sqli,params,True)
# except:
# app.logger.warning("Unable to add historical information, non-blocking %s"%(prev,))
if prev[ki]['classif_id']!=int(v): # il y a eu un changement de classif on maintient la liste des classifs MRU
with app.MRUClassif_lock:
tbl=app.MRUClassif.get(current_user.id,[])
for i,t in enumerate(tbl):
if t["id"]==int(v):
if i>0: # on met cet item au début pour gérer un MRU
tbl=[t]+tbl[0:i]+tbl[i+1:]
break
else: # si pas trouvé dans la liste des MRU on l'ajoute au début si on trouve bien son nom dans la taxo
Taxon=GetAll("""select tf.display_name as name
from taxonomy tf
left join taxonomy p1 on tf.parent_id=p1.id
where tf.id=%(id)s """,{"id":v})
if len(Taxon)==1:
Taxon=Taxon[0].get('name', "")
tbl.insert(0,{"id": int(v), "pr": 0, "text": Taxon})
if len(tbl)>10:
tbl=tbl[0:10]
app.MRUClassif[current_user.id]=tbl
if len(BatchParam)>0:
upcur = db.engine.raw_connection().cursor()
try:
upcur.executemany(sql, BatchParam)
upcur.connection.commit()
except:
upcur.close()
app.logger.warning("Unable to save changes %s" % (sys.exc_info(),))
return '<span class="label label-danger">Unable to save changes</span>'
upcur.close()
app.logger.info("Changement = %s",Changement)
# applique les changements dans projects_taxo_stat
Empty = {'n': 0, 'V': 0, 'P': 0, 'D': 0}
Changes = {}
for c in Changement:
if c['prev_id'] is None: c['prev_id'] = -1
if c['prev_id'] not in Changes: Changes[c['prev_id']] = Empty.copy()
if c['id'] is None: c['id'] = -1
if c['id'] not in Changes: Changes[c['id']] = Empty.copy()
Changes[c['prev_id']]['n'] -= 1
Changes[c['id']]['n'] += 1
if c['prev_qual'] in ('V', 'P', 'D'):
Changes[c['prev_id']][c['prev_qual']] -= 1
if c['qual'] in ('V', 'P', 'D'):
Changes[c['id']][c['qual']] += 1
LstIdInDB=[x[0] for x in database.GetAll("select id from projects_taxo_stat where projid=%s",[PrjId])]
for k,c in Changes.items():
if k not in LstIdInDB:
database.ExecSQL("insert into projects_taxo_stat(projid, id, nbr, nbr_v, nbr_d, nbr_p) values (%s,%s,0,0,0,0)",[PrjId,k])
sqlparam={'projid':PrjId,'id':k,'n':c['n'],'v':c['V'],'d':c['D'],'p':c['P']}
database.ExecSQL("""update projects_taxo_stat set
nbr=nbr+%(n)s, nbr_v=nbr_v+%(v)s, nbr_d=nbr_d+%(d)s, nbr_p=nbr_p+%(p)s
where projid=%(projid)s and id=%(id)s""",sqlparam)
return '<span class="label label-success">Database update Successfull</span>'
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 __init__(self):
database.ExecSQL("select 1") # initialize g.db
self.cur = g.db.cursor()
