def archive_bad_data(data, json_data):
# Insert rejected JSON into synchro_table (text format)
now = datetime.datetime.now()
objects = []
new_feature = {}
if json_data['input_type'] == 'fauna':
new_feature['table_name'] = settings.TABLE_FAILED_JSON_FAUNA
table_infos = settings.FAUNE_TABLE_INFOS
database_id = settings.DB_FAUNA
if json_data['input_type'] == 'mortality':
new_feature['table_name'] = settings.TABLE_FAILED_JSON_MORTALITY
table_infos = settings.FAUNE_TABLE_INFOS
database_id = settings.DB_FAUNA
if json_data['input_type'] == 'invertebrate':
new_feature['table_name'] = settings.TABLE_FAILED_JSON_INV
table_infos = settings.INV_TABLE_INFOS
database_id = settings.DB_INV
if json_data['input_type'] == 'flora':
new_feature['table_name'] = settings.TABLE_FAILED_JSON_FLORA
table_infos = settings.FLORA_TABLE_INFOS
database_id = settings.DB_FLORA
new_feature['date_import'] = "%d-%d-%d %d:%d:%d" % (now.year, now.month, now.day, now.hour, now.minute, now.second)
new_feature['json'] = data
objects.append(new_feature)
cursor = sync_db(objects, table_infos, database_id)
id_failed = cursor.fetchone()[0]
# Commit transaction
commit_transaction(database_id)
return id_failed
def import_data_fmi(json_data, data):
"""
Import data for fauna, mortality and invertebrate (fmi)
"""
response_content = {}
if json_data['input_type'] == 'fauna' or json_data['input_type'] == 'mortality':
table_infos = settings.FAUNE_TABLE_INFOS
table_sheet = settings.TABLE_FAUNA_SHEET
table_statement = settings.TABLE_FAUNA_STATEMENT
database_id = settings.DB_FAUNA
if json_data['input_type'] == 'invertebrate':
table_infos = settings.INV_TABLE_INFOS
table_sheet = settings.TABLE_INV_SHEET
table_statement = settings.TABLE_INV_STATEMENT
database_id = settings.DB_INV
d = EasyDict(json_data)
bad_id = False
# Check if ID are unique
count_string = "SELECT count(*) FROM %s WHERE %s='%s'" % (table_sheet, table_infos.get(table_sheet).get('id_col'), d.id)
cursor = query_db(count_string, database_id)
row = cursor.fetchone()
if row:
datarow = zip([column[0] for column in cursor.description], row)
val = datarow[0][1]
if val == 1:
bad_id = True
response_content.update({
'status_code': _("1"),
'status_message': _("Existing ID in database (%s) (%s)") % (table_sheet, d.id)
})
for taxon in d.taxons:
count_string = "SELECT count(*) FROM %s WHERE %s='%s'" % (table_statement, table_infos.get(table_statement).get('id_col'), taxon.id)
cursor = query_db(count_string, database_id)
row = cursor.fetchone()
if row:
datarow = zip([column[0] for column in cursor.description], row)
val = datarow[0][1]
if val == 1:
bad_id = True
response_content.update({
'status_code': _("1"),
'status_message': _("Existing ID in database (%s) (%s)") % (table_statement, taxon.id)
})
if not bad_id:
try:
objects = []
new_feature = {}
json_to_db = table_infos.get(table_sheet).get('json_to_db_columns')
# Insert into TABLE_SHEET
new_feature[table_infos.get(table_sheet).get('id_col')] = d.id
new_feature['table_name'] = table_sheet
date_obs = d.dateobs.split(" ")
new_feature[json_to_db.get('dateobs')] = date_obs[0]
if json_data['input_type'] == 'invertebrate':
new_feature[json_to_db.get('heure')] = date_obs[1].split(":")[0]
new_feature[json_to_db.get('environment')] = d.environment
new_feature[json_to_db.get('initial_input')] = d.initial_input
new_feature['supprime'] = 'False'
new_feature['id_organisme'] = settings.FAUNA_ID_ORGANISM
if json_data['input_type'] == 'fauna':
new_feature['id_protocole'] = settings.FAUNA_ID_PROTOCOL
new_feature['id_lot'] = settings.FAUNA_ID_LOT
if json_data['input_type'] == 'mortality':
new_feature['id_protocole'] = settings.MORTALITY_ID_PROTOCOL
new_feature['id_lot'] = settings.MORTALITY_ID_LOT
if json_data['input_type'] == 'invertebrate':
new_feature['id_protocole'] = settings.INV_ID_PROTOCOL
new_feature['id_lot'] = settings.INV_ID_LOT
# we need to transform into 2154
new_feature[json_to_db.get('geometry')] = "st_transform(ST_GeomFromText('POINT(%s %s)', 4326),2154)" % (d.geolocation.longitude, d.geolocation.latitude)
new_feature[json_to_db.get('accuracy')] = d.geolocation.accuracy
objects.append(new_feature)
cursor = sync_db(objects, table_infos, database_id)
# Insert into TABLE_STATEMENT
statement_ids = []
for taxon in d.taxons:
statement_ids.append(taxon.id)
objects = []
new_feature = {}
json_to_db = table_infos.get(table_statement).get('json_to_db_columns')
new_feature['table_name'] = table_statement
new_feature['supprime'] = 'False'
new_feature[table_infos.get(table_statement).get('id_col')] = taxon.id
new_feature[table_infos.get(table_sheet).get('id_col')] = d.id
new_feature[json_to_db.get('id')] = taxon.id_taxon
new_feature[json_to_db.get('name_entered')] = taxon.name_entered
if json_data['input_type'] == 'fauna':
new_feature[json_to_db.get('adult_male')] = taxon.counting.adult_male
new_feature[json_to_db.get('adult_female')] = taxon.counting.adult_female
new_feature[json_to_db.get('adult')] = taxon.counting.adult
new_feature[json_to_db.get('not_adult')] = taxon.counting.not_adult
new_feature[json_to_db.get('young')] = taxon.counting.young
new_feature[json_to_db.get('yearling')] = taxon.counting.yearling
new_feature[json_to_db.get('sex_age_unspecified')] = taxon.counting.sex_age_unspecified
new_feature[json_to_db.get('criterion')] = taxon.observation.criterion
if json_data['input_type'] == 'mortality':
new_feature[json_to_db.get('adult_male')] = taxon.mortality.adult_male
new_feature[json_to_db.get('adult_female')] = taxon.mortality.adult_female
new_feature[json_to_db.get('adult')] = taxon.mortality.adult
new_feature[json_to_db.get('not_adult')] = taxon.mortality.not_adult
new_feature[json_to_db.get('young')] = taxon.mortality.young
new_feature[json_to_db.get('yearling')] = taxon.mortality.yearling
new_feature[json_to_db.get('sex_age_unspecified')] = taxon.mortality.sex_age_unspecified
new_feature[json_to_db.get('sample')] = taxon.mortality.sample
new_feature[json_to_db.get('criterion')] = taxon.observation.criterion
if json_data['input_type'] == 'invertebrate':
new_feature[json_to_db.get('adult_male')] = taxon.counting.adult_male
new_feature[json_to_db.get('adult_female')] = taxon.counting.adult_female
new_feature[json_to_db.get('adult')] = taxon.counting.adult
new_feature[json_to_db.get('not_adult')] = taxon.counting.not_adult
new_feature[json_to_db.get('criterion')] = taxon.observation.criterion
new_feature[json_to_db.get('comment')] = taxon.comment
objects.append(new_feature)
cursor = sync_db(objects, table_infos, database_id)
# Insert into TABLE_SHEET_ROLE (multiple observers enable)
for observer in d.observers_id:
objects = []
new_feature = {}
if json_data['input_type'] == 'fauna' or json_data['input_type'] == 'mortality':
new_feature['table_name'] = settings.TABLE_FAUNA_SHEET_ROLE
new_feature['id_cf'] = d.id
if json_data['input_type'] == 'invertebrate':
new_feature['table_name'] = settings.TABLE_INV_SHEET_ROLE
new_feature['id_inv'] = d.id
new_feature['id_role'] = observer
objects.append(new_feature)
sync_db(objects, table_infos, database_id)
# Commit transaction
commit_transaction(database_id)
response_content.update({
'status_code': _("0"),
'status_message': "id_sheet: %s, ids_statements: %s" % (d.id, ','.join(map(str, statement_ids)))
})
except Exception, e:
# Insert rejected JSON into synchro_table (text format)
id_failed = archive_bad_data(data, json_data)
response_content.update({
'status_code': _("1"),
'status_message': _("Bad json or data (%d)") % id_failed
})
def import_data_flora(json_data, data):
"""
Import data for flora
"""
response_content = {}
table_infos = settings.FLORA_TABLE_INFOS
table_apresence = settings.TABLE_FLORA_T_APRESENCE
table_zprospection = settings.TABLE_FLORA_T_ZPROSPECTION
database_id = settings.DB_FLORA
d = EasyDict(json_data)
bad_id = False
# Check if ID are unique
count_string = "SELECT count(*) FROM %s WHERE %s='%s'" % (table_zprospection, table_infos.get(table_zprospection).get('id_col'), d.id)
cursor = query_db(count_string, database_id)
row = cursor.fetchone()
if row:
datarow = zip([column[0] for column in cursor.description], row)
val = datarow[0][1]
if val == 1:
bad_id = True
response_content.update({
'status_code': _("1"),
'status_message': _("Existing ID in database (%s) (%s)") % (table_zprospection, d.id)
})
for taxon in d.taxons:
for area in taxon.areas:
count_string = "SELECT count(*) FROM %s WHERE %s='%s'" % (table_apresence, table_infos.get(table_apresence).get('id_col'), area.id)
cursor = query_db(count_string, database_id)
row = cursor.fetchone()
if row:
datarow = zip([column[0] for column in cursor.description], row)
val = datarow[0][1]
if val == 1:
bad_id = True
response_content.update({
'status_code': _("1"),
'status_message': _("Existing ID in database (%s) (%s)") % (table_apresence, area.id)
})
# even the json offers a list, there's only one taxa
break
if not bad_id:
try:
objects = []
new_feature = {}
json_to_db = table_infos.get(table_zprospection).get('json_to_db_columns')
areas_ids = []
for taxon in d.taxons:
# Insert into ZPROSPECTION
new_feature[table_infos.get(table_zprospection).get('id_col')] = d.id
new_feature['table_name'] = table_zprospection
date_obs = d.dateobs.split(" ")
new_feature[json_to_db.get('dateobs')] = date_obs[0]
new_feature[json_to_db.get('initial_input')] = d.initial_input
new_feature['supprime'] = 'False'
new_feature[json_to_db.get('name_entered')] = taxon.name_entered
new_feature[json_to_db.get('id_taxon')] = taxon.id_taxon
new_feature['id_organisme'] = settings.FLORA_ID_ORGANISM
# ajout Gil
new_feature['id_protocole'] = settings.FLORA_ID_PROTOCOL
new_feature['id_lot'] = settings.FLORA_ID_LOT
# we need to transform geometry into 2154
string_geom = get_geometry_string_from_coords(taxon.prospecting_area.feature.geometry.coordinates, taxon.prospecting_area.feature.geometry.type)
new_feature[json_to_db.get('geometry')] = string_geom
objects.append(new_feature)
cursor = sync_db(objects, table_infos, database_id)
# Insert into APRESENCE
for area in taxon.areas:
areas_ids.append(area.id)
objects = []
new_feature = {}
json_to_db = table_infos.get(table_apresence).get('json_to_db_columns')
new_feature['table_name'] = table_apresence
new_feature['supprime'] = 'False'
new_feature[table_infos.get(table_apresence).get('id_col')] = area.id
new_feature[table_infos.get(table_zprospection).get('id_col')] = d.id
new_feature[json_to_db.get('id')] = area.id
new_feature[json_to_db.get('phenology')] = area.phenology
new_feature[json_to_db.get('computed_area')] = area.computed_area
if area.frequency.type == "estimation":
new_feature[json_to_db.get('frequenceap')] = area.frequency.value
new_feature[json_to_db.get('id_frequence_methodo_new')] = settings.FLORA_FREQUENCY_ESTIMATION
if area.frequency.type == "transect":
new_feature[json_to_db.get('frequenceap')] = area.frequency.value
new_feature[json_to_db.get('nb_transects_frequence')] = area.frequency.transects
new_feature[json_to_db.get('nb_points_frequence')] = area.frequency.transect_no # TODO check
new_feature[json_to_db.get('nb_contacts_frequence')] = area.frequency.transect_yes # TODO check
new_feature[json_to_db.get('longueur_pas')] = area.frequency.computed_recommended_step
new_feature[json_to_db.get('id_frequence_methodo_new')] = settings.FLORA_FREQUENCY_TRANSECT
string_geom = get_geometry_string_from_coords(area.feature.geometry.coordinates, area.feature.geometry.type)
new_feature[json_to_db.get('geometry')] = string_geom
if area.counting.type == "none":
new_feature[json_to_db.get('id_comptage_methodo')] = settings.FLORA_COUNTING_NONE
if area.counting.type == "exhaustive":
new_feature[json_to_db.get('total_steriles')] = area.counting.total_sterile
new_feature[json_to_db.get('total_fertiles')] = area.counting.total_fertile
new_feature[json_to_db.get('id_comptage_methodo')] = settings.FLORA_COUTING_EXHAUSTIVE
if area.counting.type == "sampling":
new_feature[json_to_db.get('total_steriles')] = area.counting.total_sterile
new_feature[json_to_db.get('total_fertiles')] = area.counting.total_fertile
new_feature[json_to_db.get('nb_placettes_comptage')] = area.counting.plots
new_feature[json_to_db.get('surface_placette_comptage')] = area.counting.plot_surface
new_feature[json_to_db.get('effectif_placettes_steriles')] = area.counting.sterile
new_feature[json_to_db.get('effectif_placettes_fertiles')] = area.counting.fertile
new_feature[json_to_db.get('id_comptage_methodo')] = settings.FLORA_COUTING_SAMPLING
new_feature[json_to_db.get('comment')] = area.comment
objects.append(new_feature)
cursor = sync_db(objects, table_infos, database_id)
# Physiognomies
for physiognomy in area.physiognomy:
objects = []
new_feature = {}
new_feature['table_name'] = settings.TABLE_FLORA_COR_AP_PHYSIONOMIE
new_feature['indexap'] = area.id
new_feature['id_physionomie'] = physiognomy
objects.append(new_feature)
sync_db(objects, table_infos, database_id)
# Disturbances
for disturbance in area.disturbances:
objects = []
new_feature = {}
new_feature['table_name'] = settings.TABLE_FLORA_COR_AP_PERTURB
new_feature['indexap'] = area.id
new_feature['codeper'] = disturbance
objects.append(new_feature)
sync_db(objects, table_infos, database_id)
break # even the json offers a list, there's only one taxa
# Insert into TABLE_SHEET_ROLE (multiple observers enable)
for observer in d.observers_id:
objects = []
new_feature = {}
new_feature['table_name'] = settings.TABLE_FLORA_COR_ZP_OBS
new_feature['indexzp'] = d.id
new_feature['codeobs'] = observer
objects.append(new_feature)
sync_db(objects, table_infos, database_id)
# Commit transaction
commit_transaction(database_id)
# Sync external DB
cmd = "%s%s" % (settings.SYNC_DB_CMD, d.id)
os.system(cmd)
response_content.update({
'status_code': _("0"),
'status_message': "id_prospection: %s, ids_areass: %s" % (d.id, ','.join(map(str, areas_ids)))
})
except Exception, e:
### Insert rejected JSON into synchro_table (text format)
id_failed = archive_bad_data(data, json_data)
response_content.update({
'status_code': _("1"),
'status_message': _("Bad json or data (%d)") % id_failed
})
