#!/usr/bin/python2.7
# -*- encoding: UTF-8 -*-

# Imports
# --------
from pg import DB # module ubuntu : python-pygresql, import de la fonction DB
import os.path # Fonctionalités diverses du système de fichiers
import re # Expressions régulières
import sys # Paramètres et fonctions spécifiques au système

# Définition des variables à paramétrer
# --------------------------------------
aigledbname = 'vanoisedev_back'
aigledbhost = 'x.x.x.x'
aigledbport = xxxxxx
aigledbuser = 'xxxxxx'
aigledbpwd = 'xxxxxx

geotrekdbname = 'geotrekdb'
geotrekdbhost = 'x.x.x.x'
geotrekdbport = xxxxxx
geotrekdbuser = 'xxxxxx'
geotrekdbpwd = 'xxxxxx'

codeMur = 1
codeCalade = 2
codePasserelle = 3
codePIS = 4
codeCompteur = 5

def translateBool (str) :
	if str == "f" :
		return "non"
	elif str == "t" :
		return "oui"
	else :
		return ""

def testIfNull (value) :
	if value == None :
		return ""
	else :
		return value 

# ----------------------------------------------------------
# Insertions dans la table a_t_amenagement des données des tables :
# passerelles, PIS, murs_pierres_seches (vide), ecocompteur, calades (vide)
# ----------------------------------------------------------
dbAigle = DB(dbname=aigledbname, host=aigledbhost, port=aigledbport,user=aigledbuser,passwd=aigledbpwd)
dbGeotrek = DB(dbname=geotrekdbname, host=geotrekdbhost, port=geotrekdbport,user=geotrekdbuser,passwd=geotrekdbpwd)

qry = "SELECT max(id) as max FROM e_t_evenement"
result = dbGeotrek.query(qry).getresult()
max = result[0][0] + 1

#----- Table PIS
rows = dbAigle.query("SELECT nom_pis,date_part('year',date_installation) as annee,ST_AsText(geom) as geom FROM sentiers_signaletique.pis").dictresult()
	
for row in rows :
	qry = "INSERT INTO e_t_evenement (id,supprime,decallage,kind,geom) \
	VALUES (" + str(max) + ",false,0,'INFRASTRUCTURE',ST_GeomFromText('" + row["geom"]+ "',2154))"
	dbGeotrek.query(qry)

	qry = "INSERT INTO a_t_amenagement (structure,evenement,nom,description,type,annee_implantation) VALUES (1," + str(max) + ",'" + row["nom_pis"].replace("'","''") + "',''," + str(codePIS) +"," + str(row["annee"]) + ")"
	dbGeotrek.query(qry)

	max += 1

#----- Table PASSERELLES
rows = dbAigle.query("SELECT nom_passerelle,libelle_type_passerelle,remarques,date_installation,ST_AsText(geom) as geom, \
flux_animaux, flux_vehicules,longueur_mobile,poids_mobile,poids_element_mobile,hauteur_riviere,risque_crue,risque_avalanche,\
risque_neige,risque_erosion,risque_reptaion,risque_blocs,risque_autres \
FROM sentiers_signaletique.passerelles \
INNER JOIN sentiers_signaletique.dico_type_passerelle ON (passerelles.id_type_passerelle = dico_type_passerelle.id_type_passerelle)").dictresult()
	
for row in rows :
	qry = "INSERT INTO e_t_evenement (id,supprime,decallage,kind,geom) VALUES (" + str(max) + ",false,0,'INFRASTRUCTURE',ST_GeomFromText('" + row["geom"]+ "',2154))"
	dbGeotrek.query(qry)
	
	desc = "Type passerelle:" + row["libelle_type_passerelle"] + "<br>"
	desc += "Flux animaux:" + translateBool(row["flux_animaux"]) + "<br>"
	desc += "Flux véhicules:" + translateBool(row["flux_vehicules"]) + "<br>"
	desc += "Longueur mobile:" + str(row["longueur_mobile"]) + "<br>"
	desc += "Poids mobile:" + str(row["poids_mobile"]) + "<br>"
	desc += "Poids élément mobile:" + str(row["poids_element_mobile"]) + "<br>"
	desc += "Hauteur rivière:" + str(row["hauteur_riviere"]) + "<br>"
	desc += "Risque Crue:" + translateBool(row["risque_crue"]) + " avalanche:" + translateBool(row["risque_avalanche"])
	desc += " neige:" + translateBool(row["risque_neige"]) + " érosion:" + translateBool(row["risque_erosion"])
	desc += " reptation:" + translateBool(row["risque_reptaion"]) + " blocs:" + translateBool(row["risque_blocs"])
	desc += "<br>" + testIfNull(row["remarques"])
	
	annee = "null"
	if row["date_installation"] != None and re.search(r'.*([0-9]{4}).*',row["date_installation"]) != None:
		annee = re.sub(r'.*([0-9]{4}).*',r'\1',row["date_installation"])

	qry = "INSERT INTO a_t_amenagement (structure,evenement,nom,description,type,annee_implantation) \
	VALUES (1," + str(max) + ",'" + row["nom_passerelle"].replace("'","''") + "','" + desc.replace("'","''") + "'," + str(codePasserelle) + "," + str(annee) + ")"
	dbGeotrek.query(qry)
	
	max += 1

#----- Table ECOCOMPTEUR
rows = dbAigle.query("SELECT nom,id_pnv,ST_AsText(geom) as geom FROM sentiers_signaletique.ecocompteur").dictresult()
	
for row in rows :
	qry = "INSERT INTO e_t_evenement (id,supprime,decallage,kind,geom) VALUES (" + str(max) + ",false,0,'INFRASTRUCTURE',ST_GeomFromText('" + row["geom"]+ "',2154))"
	dbGeotrek.query(qry)
	
	qry = "INSERT INTO a_t_amenagement (structure,evenement,nom,description,type,c) \
	VALUES (1," + str(max) + ",'" + row["nom"].replace("'","''") + "','ID PNV:" + str(row["id_pnv"]) + "'," + str(codeCompteur) + ")"
	dbGeotrek.query(qry)
	max += 1

#----- Table CHANTIER
# A Faire

# Mise à jour de la séquence pour e_t_evenement
qry = "ALTER SEQUENCE geotrek.e_t_evenement_id_seq RESTART WITH " + str(max)
dbGeotrek.query(qry)

dbAigle.close()
dbGeotrek.close()