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()