R/bd_2_xls_ch.R
In jprenaud-02/coopR: Fonctions R pour la Coop
#' @title Chargement des fichiers COOP depuis la base de donnees et export vers xls
#'
#' @description Cette fonction charge les tables au format pret pour l'utilisation des fonctions du package, directement a partir de la Base de donnees
#'
#' @param disp nom complet (lib_disp) des sites qui seront charges, il faut des guillemets et des simples quotes p ex au format "'SITEA', 'SITEB'"
#' @param nom, nom de l'utilisateur dans la base de donnees GIS Coop
#' @param mdp, mot de passe de l'utilisateur
#' @param date, date avec des guillemets par ex "241217" qui apparaitra dans le nom du fichier xls
#' @return un fichier xls
#'
#' @author Ingrid Seynave, A Calas, D Francois, JP Renaud
#' @references Protocole Coop chene... document d Ingrid et Claudine
#'
#' @seealso dico.ch, load_onf_ch, load_bdd_to_xls_ch
#' @examples
#' # A venir
#'
#' @keywords function
#'
#' @include coopR-package.R
#' @family coopR
#' @import DBI
#' @import RPostgreSQL
#' @import XLConnect
#' @import doBy
#' @import plyr
#' @export
# library(RPostgreSQL)
# library(xlsx)
# library(plyr)
# nom="renaud"; mdp ="jeanpierre"; disp = "'MONTRICHARD'"; date= as.character("999999")
# setwd("c:/Donnees")
bd_2_xls_ch <- function (nom="tonNom", mdp = "tonMdP", disp =NULL, date = "date"){
disp2 <- gsub("(^')|('$)", "", disp) # vire les simples quotes en début et en fin
# nom du fichier d'export
Fichier <- paste0(disp2,"_export_", date, ".xls")
# connexion ? la base de donn?es
drv <- dbDriver("PostgreSQL")
con <- postgresqlNewConnection(drv=drv,user=nom,password=mdp,host="postgres-national-priv-4.jouy.inra.fr",
dbname="coop", port=54443)
######## requ?tes
# feuille Placette
query <- paste("SELECT
public.departement.id_departement,
public.foret22.lib_foret,
public.dispositif22.lib_disp,
public.placette22.id_placette,
public.placette22.no_placette,
public.placette22.lib_placette,
public.traitement22.co_traitement,
public.organisme.id_organisme,
public.proprietaire22.code_proprietaire_id,
public.proprietaire22.nom_prop,
public.proprietaire22.comment_prop,
public.region_forestiere.lib_reg_forest,
public.placette22.essence_principale1_id,
public.placette22.essence_principale2_id,
public.placette22.surface,
public.placette22.surface_tot,
public.placette22.topographie_id,
public.placette22.altitude,
public.placette22.pente,
public.placette22.exposition,
public.placette22.ign,
public.placette22.equienne_id,
public.placette22.pure_id,
public.placette22.essence_secondaire1_id,
public.placette22.essence_secondaire2_id,
public.placette22.origine_id,
public.placette22.materiel_genetique,
public.placette22.nb_plants,
public.placette22.espacement,
public.placette22.annee_plant,
public.placette22.mois_plant_id,
public.placette22.age_plant,
public.placette22.an_cernes030,
public.placette22.mois_cernes030_id,
public.placette22.nb_cernes030,
public.placette22.an_cernes130,
public.placette22.mois_cernes130_id,
public.placette22.nb_cernes130,
public.placette22.age_installation,
public.placette22.largeur_cloiso,
public.placette22.entre_axe_cloiso,
public.dispositif22.commune_situation,
public.placette22.xauteur,
public.placette22.yauteur,
public.placette22.referentielxy_id,
public.placette22.meridienxy_id,
public.placette22.sourcexy_id,
public.placette22.cartographie_id,
public.placette22.an_deb_obs,
public.placette22.mois_deb_obs_id,
public.placette22.an_arret_mesure,
public.placette22.mois_arret_mesure_id,
public.placette22.cause_arret_mesure,
public.placette22.chgt_traitement_id,
public.placette22.existence_ss_unite_id,
public.placette22.observation,
public.placette22.x_liie,
public.placette22.y_liie
FROM
public.departement
INNER JOIN public.foret22 ON (public.departement.id_departement = public.foret22.departement_id)
INNER JOIN public.dispositif22 ON (public.foret22.id_foret = public.dispositif22.foret_id)
INNER JOIN public.placette22 ON (public.dispositif22.id_dispositif = public.placette22.dispositif_id)
INNER JOIN public.historique_proprietaire22 ON (public.historique_proprietaire22.dispositif_id = public.dispositif22.id_dispositif)
INNER JOIN public.proprietaire22 ON (public.proprietaire22.id_proprietaire = public.historique_proprietaire22.proprietaire_id)
INNER JOIN public.traitement22 ON (public.placette22.trait1_id = public.traitement22.id_traitement)
INNER JOIN public.organisme ON (public.dispositif22.organisme_id_old = public.organisme.id_organisme)
INNER JOIN public.region_forestiere ON (public.dispositif22.region_forestiere_id = public.region_forestiere.id_region_forestiere)
WHERE
dispositif22.lib_disp in (",disp,") order by id_placette", sep="")
placette <- dbSendQuery(con, query)
Placette <- fetch(placette, -1)
dbClearResult(placette)
# feuille Historique_traitement
query <- paste("SELECT
public.placette22.id_placette,
public.placette22.no_placette,
public.historique_traitement22.an_deb_application,
public.historique_traitement22.mois_deb_application_id,
public.traitement22.co_traitement,
public.historique_traitement22.observation,
public.historique_traitement22.id_historique_traitement
FROM
public.placette22
INNER JOIN public.historique_traitement22 ON (public.placette22.id_placette = public.historique_traitement22.placette_id)
INNER JOIN public.traitement22 ON (public.traitement22.id_traitement = public.historique_traitement22.trait1_id)
INNER JOIN public.dispositif22 ON (public.placette22.dispositif_id = public.dispositif22.id_dispositif)
WHERE
dispositif22.lib_disp in (",disp,")", sep="",
"ORDER BY id_placette, an_deb_application, mois_deb_application_id")
historique_traitement <- dbSendQuery(con, query)
Historique_traitement <- fetch(historique_traitement, -1)
dbClearResult(historique_traitement)
# feuille Intervention
query <- paste("SELECT
public.placette22.id_placette,
public.placette22.no_placette,
public.intervention22.an_intervention,
public.intervention22.mois_intervention_id,
public.intervention22.lib_intervention,
public.intervention22.observation,
public.intervention22.id_intervention
FROM
public.placette22
INNER JOIN public.dispositif22 ON (public.placette22.dispositif_id = public.dispositif22.id_dispositif)
INNER JOIN public.intervention22 ON (public.placette22.id_placette = public.intervention22.placette_id)
WHERE
dispositif22.lib_disp in (",disp,")", sep="",
"ORDER BY id_intervention")
intervention <- dbSendQuery(con, query)
Intervention <- fetch(intervention, -1)
dbClearResult(intervention)
# feuille Mesure
query <- paste("SELECT
public.placette22.id_placette,
public.placette22.no_placette,
public.mesure22.no_mesure,
public.mesure22.an_mesure,
public.mesure22.mois_mesure_id,
public.mesure22.guide,
public.mesure22.type_suivi_id,
public.mesure22.observation,
public.mesure22.an_vegetation,
public.mesure22.id_mesure
FROM
public.placette22
INNER JOIN public.mesure22 ON (public.placette22.id_placette = public.mesure22.placette_id)
INNER JOIN public.dispositif22 ON (public.placette22.dispositif_id = public.dispositif22.id_dispositif)
WHERE
dispositif22.lib_disp in (",disp,")", sep="",
"ORDER BY id_placette, no_mesure")
mesure <- dbSendQuery(con, query)
Mesure <- fetch(mesure, -1)
dbClearResult(mesure)
# feuille Numero_Arbre
query <- paste("SELECT
public.placette22.id_placette,
public.placette22.no_placette,
public.arbre22.no_arb_pl,
public.arbre22.no_arb_sp,
public.arbre22.no_arb_bd,
public.ss_placette22.lib_ss_placette,
public.bandelette22.lib_bandelette,
public.arbre22.observation,
public.arbre22.id_arbre
FROM
public.dispositif22
INNER JOIN public.placette22 ON (public.dispositif22.id_dispositif = public.placette22.dispositif_id)
INNER JOIN public.arbre22 ON (public.placette22.id_placette = public.arbre22.placette_id)
LEFT OUTER JOIN public.bandelette22 ON (public.arbre22.bandelette_id = public.bandelette22.id_bandelette)
LEFT OUTER JOIN public.ss_placette22 ON (public.arbre22.ss_placette_id = public.ss_placette22.id_ss_placette)
WHERE
dispositif22.lib_disp in (",disp,")", sep="",
"ORDER BY id_placette, no_arb_pl, no_arb_sp, no_arb_bd")
numero_arbre <- dbSendQuery(con, query)
Numero_arbre <- fetch(numero_arbre, -1)
dbClearResult(numero_arbre)
# feuille Arbre
query <- paste("SELECT
T.id_placette,
T.no_placette,
T.no_mesure,
T.no_arb_pl as no_arb_ech,
T.essence_id,
T.co_ech,
T.etat_san_id,
T.statut_av_id,
T.statut_ap_id,
NULL AS epv_av,
NULL AS epv_ap,
T.diam_050,
T.diam_130,
T.circonference130,
T.hauteur_ech,
T.ebv,
T.ht_pb_s,
T.ht_pb_g,
T.ht_pb_i,
T.designe_id,
T.eclairci_realise_id,
T.regarni_id,
G1.gourmands_02,
G2.gourmands_24,
BS1.branches_sequentielles_02,
BS2.branches_sequentielles_24,
BG1.branches_gourmandes_02,
BG2.branches_gourmandes_24,
BI1.branches_indeterminees_02,
BI2.branches_indeterminees_24,
T.observation,
T.ht_pb_v,
NULL as ht_pbette,
NULL as branches_gourmandes_0_4,
T.id_arbre,
T.id_mesure,
T.id_mesure_echantillon
FROM
(SELECT
public.placette22.id_placette,
public.placette22.no_placette,
public.mesure22.no_mesure,
public.arbre22.no_arb_pl,
public.arbre22.essence_id,
public.mesure_echantillon22.co_ech,
public.mesure_echantillon22.etat_san_id,
public.mesure_echantillon22.statut_av_id,
public.mesure_echantillon22.statut_ap_id,
public.mesure_echantillon22.diam_050,
public.mesure_echantillon22.diam_130,
public.mesure_echantillon22.circonference130,
public.mesure_echantillon22.hauteur_ech,
public.mesure_echantillon22.ebv,
public.mesure_echantillon22.ht_pb_s,
public.mesure_echantillon22.ht_pb_g,
public.mesure_echantillon22.ht_pb_i,
public.mesure_echantillon22.designe_id,
public.mesure_echantillon22.eclairci_realise_id,
public.mesure_echantillon22.regarni_id,
public.mesure_echantillon22.observation,
public.mesure_echantillon22.ht_pb_v,
public.arbre22.id_arbre,
public.mesure22.id_mesure,
public.mesure_echantillon22.id_mesure_echantillon
FROM
public.dispositif22
INNER JOIN public.placette22 ON (public.dispositif22.id_dispositif = public.placette22.dispositif_id)
INNER JOIN public.arbre22 ON (public.placette22.id_placette = public.arbre22.placette_id)
INNER JOIN public.mesure_echantillon22 ON (public.arbre22.id_arbre = public.mesure_echantillon22.arbre_id)
INNER JOIN public.mesure22 ON (public.mesure_echantillon22.mesure_id = public.mesure22.id_mesure) AND (public.placette22.id_placette = public.mesure22.placette_id)
WHERE
dispositif22.lib_disp in (",disp,")", sep="",") AS T
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon22.id_mesure_echantillon,
public.mesure_branchaison22.effectif as gourmands_02
FROM
public.mesure_echantillon22
INNER JOIN public.mesure_branchaison22 ON (public.mesure_echantillon22.id_mesure_echantillon = public.mesure_branchaison22.mesure_echantillon_id)
WHERE
public.mesure_branchaison22.ramification_id=4 AND public.mesure_branchaison22.ht_basse=0) AS G1
ON (T.id_mesure_echantillon=G1.id_mesure_echantillon)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon22.id_mesure_echantillon,
public.mesure_branchaison22.effectif as gourmands_24
FROM
public.mesure_echantillon22
INNER JOIN public.mesure_branchaison22 ON (public.mesure_echantillon22.id_mesure_echantillon = public.mesure_branchaison22.mesure_echantillon_id)
WHERE
public.mesure_branchaison22.ramification_id=4 AND public.mesure_branchaison22.ht_basse=2) G2
ON (T.id_mesure_echantillon=G2.id_mesure_echantillon)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon22.id_mesure_echantillon,
public.mesure_branchaison22.effectif as branches_sequentielles_02
FROM
public.mesure_echantillon22
INNER JOIN public.mesure_branchaison22 ON (public.mesure_echantillon22.id_mesure_echantillon = public.mesure_branchaison22.mesure_echantillon_id)
WHERE
public.mesure_branchaison22.ramification_id=1 AND public.mesure_branchaison22.ht_basse=0) AS BS1
ON (T.id_mesure_echantillon=BS1.id_mesure_echantillon)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon22.id_mesure_echantillon,
public.mesure_branchaison22.effectif as branches_sequentielles_24
FROM
public.mesure_echantillon22
INNER JOIN public.mesure_branchaison22 ON (public.mesure_echantillon22.id_mesure_echantillon = public.mesure_branchaison22.mesure_echantillon_id)
WHERE
public.mesure_branchaison22.ramification_id=1 AND public.mesure_branchaison22.ht_basse=2) AS BS2
ON (T.id_mesure_echantillon=BS2.id_mesure_echantillon)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon22.id_mesure_echantillon,
public.mesure_branchaison22.effectif as branches_gourmandes_02
FROM
public.mesure_echantillon22
INNER JOIN public.mesure_branchaison22 ON (public.mesure_echantillon22.id_mesure_echantillon = public.mesure_branchaison22.mesure_echantillon_id)
WHERE
public.mesure_branchaison22.ramification_id=2 AND public.mesure_branchaison22.ht_basse=0) AS BG1
ON (T.id_mesure_echantillon=BG1.id_mesure_echantillon)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon22.id_mesure_echantillon,
public.mesure_branchaison22.effectif as branches_gourmandes_24
FROM
public.mesure_echantillon22
INNER JOIN public.mesure_branchaison22 ON (public.mesure_echantillon22.id_mesure_echantillon = public.mesure_branchaison22.mesure_echantillon_id)
WHERE
public.mesure_branchaison22.ramification_id=2 AND public.mesure_branchaison22.ht_basse=2) AS BG2
ON (T.id_mesure_echantillon=BG2.id_mesure_echantillon)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon22.id_mesure_echantillon,
public.mesure_branchaison22.effectif as branches_indeterminees_02
FROM
public.mesure_echantillon22
INNER JOIN public.mesure_branchaison22 ON (public.mesure_echantillon22.id_mesure_echantillon = public.mesure_branchaison22.mesure_echantillon_id)
WHERE
public.mesure_branchaison22.ramification_id=3 AND public.mesure_branchaison22.ht_basse=0) AS BI1
ON (T.id_mesure_echantillon=BI1.id_mesure_echantillon)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon22.id_mesure_echantillon,
public.mesure_branchaison22.effectif as branches_indeterminees_24
FROM
public.mesure_echantillon22
INNER JOIN public.mesure_branchaison22 ON (public.mesure_echantillon22.id_mesure_echantillon = public.mesure_branchaison22.mesure_echantillon_id)
WHERE
public.mesure_branchaison22.ramification_id=3 AND public.mesure_branchaison22.ht_basse=2) AS BI2
ON (T.id_mesure_echantillon=BI2.id_mesure_echantillon)
ORDER BY id_placette, no_mesure, no_arb_ech")
arbre_pl <- dbSendQuery(con, query)
Arbre_pl <- fetch(arbre_pl, -1)
dbClearResult(arbre_pl)
query <- paste("SELECT
T.id_placette,
T.no_placette,
T.no_mesure,
T.no_arb_sp as no_arb_ech,
T.essence_id,
T.co_ech,
T.etat_san_id,
T.statut_av_id,
T.statut_ap_id,
NULL AS epv_av,
NULL AS epv_ap,
T.diam_050,
T.diam_130,
T.circonference130,
T.hauteur_ech,
T.ebv,
T.ht_pb_s,
T.ht_pb_g,
T.ht_pb_i,
T.designe_id,
T.eclairci_realise_id,
T.regarni_id,
G1.gourmands_02,
G2.gourmands_24,
BS1.branches_sequentielles_02,
BS2.branches_sequentielles_24,
BG1.branches_gourmandes_02,
BG2.branches_gourmandes_24,
BI1.branches_indeterminees_02,
BI2.branches_indeterminees_24,
T.observation,
T.ht_pb_v,
NULL as ht_pbette,
NULL as branches_gourmandes_0_4,
T.id_arbre,
T.id_mesure,
T.id_mesure_echantillon_sp
FROM
(SELECT
public.placette22.id_placette,
public.placette22.no_placette,
public.mesure22.no_mesure,
public.arbre22.no_arb_sp,
public.arbre22.essence_id,
public.mesure_echantillon_sp22.co_ech,
public.mesure_echantillon_sp22.etat_san_id,
public.mesure_echantillon_sp22.statut_av_id,
public.mesure_echantillon_sp22.statut_ap_id,
public.mesure_echantillon_sp22.diam_050,
public.mesure_echantillon_sp22.diam_130,
public.mesure_echantillon_sp22.circonference130,
public.mesure_echantillon_sp22.hauteur_ech,
public.mesure_echantillon_sp22.ebv,
public.mesure_echantillon_sp22.ht_pb_s,
public.mesure_echantillon_sp22.ht_pb_g,
public.mesure_echantillon_sp22.ht_pb_i,
public.mesure_echantillon_sp22.designe_id,
public.mesure_echantillon_sp22.eclairci_realise_id,
public.mesure_echantillon_sp22.regarni_id,
public.mesure_echantillon_sp22.observation,
public.mesure_echantillon_sp22.ht_pb_v,
public.arbre22.id_arbre,
public.mesure22.id_mesure,
public.mesure_echantillon_sp22.id_mesure_echantillon_sp
FROM
public.dispositif22
INNER JOIN public.placette22 ON (public.dispositif22.id_dispositif = public.placette22.dispositif_id)
INNER JOIN public.arbre22 ON (public.placette22.id_placette = public.arbre22.placette_id)
INNER JOIN public.mesure_echantillon_sp22 ON (public.arbre22.id_arbre = public.mesure_echantillon_sp22.arbre_id)
INNER JOIN public.mesure22 ON (public.mesure_echantillon_sp22.mesure_id = public.mesure22.id_mesure) AND (public.placette22.id_placette = public.mesure22.placette_id)
WHERE
dispositif22.lib_disp in (",disp,")", sep="",") AS T
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_sp22.id_mesure_echantillon_sp,
public.mesure_branchaison_sp22.effectif as gourmands_02
FROM
public.mesure_echantillon_sp22
INNER JOIN public.mesure_branchaison_sp22 ON (public.mesure_echantillon_sp22.id_mesure_echantillon_sp = public.mesure_branchaison_sp22.mesure_echantillon_sp_id)
WHERE
public.mesure_branchaison_sp22.ramification_id=4 AND public.mesure_branchaison_sp22.ht_basse=0) AS G1
ON (T.id_mesure_echantillon_sp=G1.id_mesure_echantillon_sp)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_sp22.id_mesure_echantillon_sp,
public.mesure_branchaison_sp22.effectif as gourmands_24
FROM
public.mesure_echantillon_sp22
INNER JOIN public.mesure_branchaison_sp22 ON (public.mesure_echantillon_sp22.id_mesure_echantillon_sp = public.mesure_branchaison_sp22.mesure_echantillon_sp_id)
WHERE
public.mesure_branchaison_sp22.ramification_id=4 AND public.mesure_branchaison_sp22.ht_basse=2) G2
ON (T.id_mesure_echantillon_sp=G2.id_mesure_echantillon_sp)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_sp22.id_mesure_echantillon_sp,
public.mesure_branchaison_sp22.effectif as branches_sequentielles_02
FROM
public.mesure_echantillon_sp22
INNER JOIN public.mesure_branchaison_sp22 ON (public.mesure_echantillon_sp22.id_mesure_echantillon_sp = public.mesure_branchaison_sp22.mesure_echantillon_sp_id)
WHERE
public.mesure_branchaison_sp22.ramification_id=1 AND public.mesure_branchaison_sp22.ht_basse=0) AS BS1
ON (T.id_mesure_echantillon_sp=BS1.id_mesure_echantillon_sp)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_sp22.id_mesure_echantillon_sp,
public.mesure_branchaison_sp22.effectif as branches_sequentielles_24
FROM
public.mesure_echantillon_sp22
INNER JOIN public.mesure_branchaison_sp22 ON (public.mesure_echantillon_sp22.id_mesure_echantillon_sp = public.mesure_branchaison_sp22.mesure_echantillon_sp_id)
WHERE
public.mesure_branchaison_sp22.ramification_id=1 AND public.mesure_branchaison_sp22.ht_basse=2) AS BS2
ON (T.id_mesure_echantillon_sp=BS2.id_mesure_echantillon_sp)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_sp22.id_mesure_echantillon_sp,
public.mesure_branchaison_sp22.effectif as branches_gourmandes_02
FROM
public.mesure_echantillon_sp22
INNER JOIN public.mesure_branchaison_sp22 ON (public.mesure_echantillon_sp22.id_mesure_echantillon_sp = public.mesure_branchaison_sp22.mesure_echantillon_sp_id)
WHERE
public.mesure_branchaison_sp22.ramification_id=2 AND public.mesure_branchaison_sp22.ht_basse=0) AS BG1
ON (T.id_mesure_echantillon_sp=BG1.id_mesure_echantillon_sp)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_sp22.id_mesure_echantillon_sp,
public.mesure_branchaison_sp22.effectif as branches_gourmandes_24
FROM
public.mesure_echantillon_sp22
INNER JOIN public.mesure_branchaison_sp22 ON (public.mesure_echantillon_sp22.id_mesure_echantillon_sp = public.mesure_branchaison_sp22.mesure_echantillon_sp_id)
WHERE
public.mesure_branchaison_sp22.ramification_id=2 AND public.mesure_branchaison_sp22.ht_basse=2) AS BG2
ON (T.id_mesure_echantillon_sp=BG2.id_mesure_echantillon_sp)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_sp22.id_mesure_echantillon_sp,
public.mesure_branchaison_sp22.effectif as branches_indeterminees_02
FROM
public.mesure_echantillon_sp22
INNER JOIN public.mesure_branchaison_sp22 ON (public.mesure_echantillon_sp22.id_mesure_echantillon_sp = public.mesure_branchaison_sp22.mesure_echantillon_sp_id)
WHERE
public.mesure_branchaison_sp22.ramification_id=3 AND public.mesure_branchaison_sp22.ht_basse=0) AS BI1
ON (T.id_mesure_echantillon_sp=BI1.id_mesure_echantillon_sp)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_sp22.id_mesure_echantillon_sp,
public.mesure_branchaison_sp22.effectif as branches_indeterminees_24
FROM
public.mesure_echantillon_sp22
INNER JOIN public.mesure_branchaison_sp22 ON (public.mesure_echantillon_sp22.id_mesure_echantillon_sp = public.mesure_branchaison_sp22.mesure_echantillon_sp_id)
WHERE
public.mesure_branchaison_sp22.ramification_id=3 AND public.mesure_branchaison_sp22.ht_basse=2) AS BI2
ON (T.id_mesure_echantillon_sp=BI2.id_mesure_echantillon_sp)
ORDER BY id_placette, no_mesure, no_arb_ech")
arbre_sp <- dbSendQuery(con, query)
#Arbre_sp <- fetch(arbre_sp, -1)
Arbre_sp <- dbFetch(arbre_sp, -1 )
#head(Arbre_sp)
query <- paste("SELECT
T.id_placette,
T.no_placette,
T.no_mesure,
T.no_arb_bd as no_arb_ech,
T.essence_id,
T.co_ech,
T.etat_san_id,
T.statut_av_id,
T.statut_ap_id,
NULL AS epv_av,
NULL AS epv_ap,
T.diam_050,
T.diam_130,
T.circonference130,
T.hauteur_ech,
T.ebv,
T.ht_pb_s,
T.ht_pb_g,
T.ht_pb_i,
T.designe_id,
T.eclairci_realise_id,
T.regarni_id,
G1.gourmands_02,
G2.gourmands_24,
BS1.branches_sequentielles_02,
BS2.branches_sequentielles_24,
BG1.branches_gourmandes_02,
BG2.branches_gourmandes_24,
BI1.branches_indeterminees_02,
BI2.branches_indeterminees_24,
T.observation,
T.ht_pb_v,
NULL as ht_pbette,
NULL as branches_gourmandes_0_4,
T.id_arbre,
T.id_mesure,
T.id_mesure_echantillon_bd
FROM
(SELECT
public.placette22.id_placette,
public.placette22.no_placette,
public.mesure22.no_mesure,
public.arbre22.no_arb_bd,
public.arbre22.essence_id,
public.mesure_echantillon_bd22.co_ech,
public.mesure_echantillon_bd22.etat_san_id,
public.mesure_echantillon_bd22.statut_av_id,
public.mesure_echantillon_bd22.statut_ap_id,
public.mesure_echantillon_bd22.diam_050,
public.mesure_echantillon_bd22.diam_130,
public.mesure_echantillon_bd22.circonference130,
public.mesure_echantillon_bd22.hauteur_ech,
public.mesure_echantillon_bd22.ebv,
public.mesure_echantillon_bd22.ht_pb_s,
public.mesure_echantillon_bd22.ht_pb_g,
public.mesure_echantillon_bd22.ht_pb_i,
public.mesure_echantillon_bd22.designe_id,
public.mesure_echantillon_bd22.eclairci_realise_id,
public.mesure_echantillon_bd22.regarni_id,
public.mesure_echantillon_bd22.observation,
public.mesure_echantillon_bd22.ht_pb_v,
public.arbre22.id_arbre,
public.mesure22.id_mesure,
public.mesure_echantillon_bd22.id_mesure_echantillon_bd
FROM
public.dispositif22
INNER JOIN public.placette22 ON (public.dispositif22.id_dispositif = public.placette22.dispositif_id)
INNER JOIN public.arbre22 ON (public.placette22.id_placette = public.arbre22.placette_id)
INNER JOIN public.mesure_echantillon_bd22 ON (public.arbre22.id_arbre = public.mesure_echantillon_bd22.arbre_id)
INNER JOIN public.mesure22 ON (public.mesure_echantillon_bd22.mesure_id = public.mesure22.id_mesure) AND (public.placette22.id_placette = public.mesure22.placette_id)
WHERE
dispositif22.lib_disp in (",disp,")", sep="",") AS T
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_bd22.id_mesure_echantillon_bd,
public.mesure_branchaison_bd22.effectif as gourmands_02
FROM
public.mesure_echantillon_bd22
INNER JOIN public.mesure_branchaison_bd22 ON (public.mesure_echantillon_bd22.id_mesure_echantillon_bd = public.mesure_branchaison_bd22.mesure_echantillon_bd_id)
WHERE
public.mesure_branchaison_bd22.ramification_id=4 AND public.mesure_branchaison_bd22.ht_basse=0) AS G1
ON (T.id_mesure_echantillon_bd=G1.id_mesure_echantillon_bd)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_bd22.id_mesure_echantillon_bd,
public.mesure_branchaison_bd22.effectif as gourmands_24
FROM
public.mesure_echantillon_bd22
INNER JOIN public.mesure_branchaison_bd22 ON (public.mesure_echantillon_bd22.id_mesure_echantillon_bd = public.mesure_branchaison_bd22.mesure_echantillon_bd_id)
WHERE
public.mesure_branchaison_bd22.ramification_id=4 AND public.mesure_branchaison_bd22.ht_basse=2) G2
ON (T.id_mesure_echantillon_bd=G2.id_mesure_echantillon_bd)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_bd22.id_mesure_echantillon_bd,
public.mesure_branchaison_bd22.effectif as branches_sequentielles_02
FROM
public.mesure_echantillon_bd22
INNER JOIN public.mesure_branchaison_bd22 ON (public.mesure_echantillon_bd22.id_mesure_echantillon_bd = public.mesure_branchaison_bd22.mesure_echantillon_bd_id)
WHERE
public.mesure_branchaison_bd22.ramification_id=1 AND public.mesure_branchaison_bd22.ht_basse=0) AS BS1
ON (T.id_mesure_echantillon_bd=BS1.id_mesure_echantillon_bd)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_bd22.id_mesure_echantillon_bd,
public.mesure_branchaison_bd22.effectif as branches_sequentielles_24
FROM
public.mesure_echantillon_bd22
INNER JOIN public.mesure_branchaison_bd22 ON (public.mesure_echantillon_bd22.id_mesure_echantillon_bd = public.mesure_branchaison_bd22.mesure_echantillon_bd_id)
WHERE
public.mesure_branchaison_bd22.ramification_id=1 AND public.mesure_branchaison_bd22.ht_basse=2) AS BS2
ON (T.id_mesure_echantillon_bd=BS2.id_mesure_echantillon_bd)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_bd22.id_mesure_echantillon_bd,
public.mesure_branchaison_bd22.effectif as branches_gourmandes_02
FROM
public.mesure_echantillon_bd22
INNER JOIN public.mesure_branchaison_bd22 ON (public.mesure_echantillon_bd22.id_mesure_echantillon_bd = public.mesure_branchaison_bd22.mesure_echantillon_bd_id)
WHERE
public.mesure_branchaison_bd22.ramification_id=2 AND public.mesure_branchaison_bd22.ht_basse=0) AS BG1
ON (T.id_mesure_echantillon_bd=BG1.id_mesure_echantillon_bd)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_bd22.id_mesure_echantillon_bd,
public.mesure_branchaison_bd22.effectif as branches_gourmandes_24
FROM
public.mesure_echantillon_bd22
INNER JOIN public.mesure_branchaison_bd22 ON (public.mesure_echantillon_bd22.id_mesure_echantillon_bd = public.mesure_branchaison_bd22.mesure_echantillon_bd_id)
WHERE
public.mesure_branchaison_bd22.ramification_id=2 AND public.mesure_branchaison_bd22.ht_basse=2) AS BG2
ON (T.id_mesure_echantillon_bd=BG2.id_mesure_echantillon_bd)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_bd22.id_mesure_echantillon_bd,
public.mesure_branchaison_bd22.effectif as branches_indeterminees_02
FROM
public.mesure_echantillon_bd22
INNER JOIN public.mesure_branchaison_bd22 ON (public.mesure_echantillon_bd22.id_mesure_echantillon_bd = public.mesure_branchaison_bd22.mesure_echantillon_bd_id)
WHERE
public.mesure_branchaison_bd22.ramification_id=3 AND public.mesure_branchaison_bd22.ht_basse=0) AS BI1
ON (T.id_mesure_echantillon_bd=BI1.id_mesure_echantillon_bd)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_bd22.id_mesure_echantillon_bd,
public.mesure_branchaison_bd22.effectif as branches_indeterminees_24
FROM
public.mesure_echantillon_bd22
INNER JOIN public.mesure_branchaison_bd22 ON (public.mesure_echantillon_bd22.id_mesure_echantillon_bd = public.mesure_branchaison_bd22.mesure_echantillon_bd_id)
WHERE
public.mesure_branchaison_bd22.ramification_id=3 AND public.mesure_branchaison_bd22.ht_basse=2) AS BI2
ON (T.id_mesure_echantillon_bd=BI2.id_mesure_echantillon_bd)
ORDER BY id_placette, no_mesure, no_arb_ech")
#arbre_bd <- dbSendQuery(con, query)
arbre_bd <- dbSendStatement(con, query)
Arbre_bd <- dbFetch(arbre_bd, -1)
dbClearResult(arbre_bd)
pos = grep("id_mesure_echantillon", names(Arbre_bd))
names(Arbre_bd)[pos] <- "id_mesure_echantillon"
pos = grep("id_mesure_echantillon", names(Arbre_sp))
names(Arbre_sp)[pos] <- "id_mesure_echantillon"
pos = grep("id_mesure_echantillon", names(Arbre_pl))
names(Arbre_pl)[pos] <- "id_mesure_echantillon"
Arbre <- rbind.fill(Arbre_pl, Arbre_sp, Arbre_bd)
Arbre <- Arbre[order(Arbre$id_placette, Arbre$no_mesure, Arbre$no_arb_ech),]
# feuille SS_unite_surface
query <- paste("SELECT
public.placette22.id_placette,
public.placette22.no_placette,
public.ss_placette22.lib_ss_placette AS no_ss_unite,
-2 AS no_mesure,
-2 AS av_ap_id,
-2 AS no_inventaire,
public.ss_placette22.longueur,
public.ss_placette22.largeur,
public.ss_placette22.surface,
public.ss_placette22.id_ss_placette as id_ss_unite
FROM
public.placette22
INNER JOIN public.ss_placette22 on (public.ss_placette22.placette_id = public.placette22.id_placette)
INNER JOIN public.dispositif22 ON (public.placette22.dispositif_id = public.dispositif22.id_dispositif)
WHERE
dispositif22.lib_disp in (",disp,")", sep="")
ss_unite_surface_sp <- dbSendQuery(con, query)
Ss_unite_surface_sp <- fetch(ss_unite_surface_sp, -1)
dbClearResult(ss_unite_surface_sp)
query <- paste("SELECT
public.placette22.id_placette,
public.placette22.no_placette,
public.bandelette22.lib_bandelette AS no_ss_unite,
public.mesure22.no_mesure,
public.bandelette_surface22.av_ap_id,
public.bandelette_surface22.no_inventaire,
public.bandelette_surface22.longueur,
public.bandelette_surface22.largeur,
public.bandelette_surface22.surface,
public.bandelette22.id_bandelette as id_ss_unite,
public.bandelette_surface22.id_bandelette_surface as id_ss_unite_surface
FROM
public.placette22
INNER JOIN public.bandelette22 on (public.bandelette22.placette_id = public.placette22.id_placette)
INNER JOIN public.mesure22 ON (public.placette22.id_placette = public.mesure22.placette_id)
INNER JOIN public.bandelette_surface22 ON (public.bandelette22.id_bandelette = public.bandelette_surface22.bandelette_id) AND (public.mesure22.id_mesure = public.bandelette_surface22.mesure_id)
INNER JOIN public.dispositif22 ON (public.placette22.dispositif_id = public.dispositif22.id_dispositif)
WHERE
dispositif22.lib_disp in (",disp,")", sep="",
"ORDER BY id_placette, no_mesure, av_ap_id, no_inventaire, no_ss_unite")
ss_unite_surface_bd <- dbSendQuery(con, query)
Ss_unite_surface_bd <- fetch(ss_unite_surface_bd, -1)
dbClearResult(ss_unite_surface_bd)
Ss_unite_surface <- rbind.fill(Ss_unite_surface_sp, Ss_unite_surface_bd)
# Feuille Inventaire_liste
query <- paste("SELECT
public.placette22.id_placette,
public.placette22.no_placette,
public.bandelette22.lib_bandelette,
public.mesure22.no_mesure,
public.bandelette_surface22.av_ap_id,
public.bandelette_surface22.no_inventaire,
public.mesure_inventaire22.circ_diam_id,
public.mesure_inventaire22.essence_id,
public.mesure_inventaire22.borne_inf,
public.mesure_inventaire22.borne_sup,
public.mesure_inventaire22.effectif,
public.mesure_inventaire22.id_mesure_inventaire
FROM
public.dispositif22
INNER JOIN public.placette22 ON (public.dispositif22.id_dispositif = public.placette22.dispositif_id)
INNER JOIN public.bandelette22 ON (public.placette22.id_placette = public.bandelette22.placette_id)
INNER JOIN public.bandelette_surface22 ON (public.bandelette22.id_bandelette = public.bandelette_surface22.bandelette_id)
INNER JOIN public.mesure22 ON (public.bandelette_surface22.mesure_id = public.mesure22.id_mesure)
AND (public.placette22.id_placette = public.mesure22.placette_id)
INNER JOIN public.mesure_inventaire22 ON (public.bandelette_surface22.id_bandelette_surface = public.mesure_inventaire22.bandelette_surface_id)
WHERE
dispositif22.lib_disp in (",disp,")", sep="",
"ORDER BY id_placette, no_mesure, av_ap_id, no_inventaire, lib_bandelette, essence_id, borne_inf")
inventaire_liste <- dbSendQuery(con, query)
Inventaire_liste <- fetch(inventaire_liste, -1)
dbClearResult(inventaire_liste)
######## cr?ation du fichier excel
#names(Placette)=c('id_departement','lib_foret','lib_disp','id_placette','no_placette','lib_placette','co_traitement','id_organisme','code_proprietaire_id',
# 'nom_prop','comment_prop','lib_reg_forest','essence_principale1_id','essence_principale2_id','surface','surface_tot','topographie_id',
# 'altitude','pente','exposition','ign','equienne_id','pure_id','essence_secondaire1_id','essence_secondaire2_id','origine_id',
# 'materiel_genetique','nb_plants','espacement','annee_plant','mois_plant_id','age_plant','an_cernes030','mois_cernes030_id',
# 'nb_cernes030','an_cernes130','mois_cernes130_id','nb_cernes130','age_installation','largeur_cloiso','entre_axe_cloiso',
# 'commune_situation','xauteur','yauteur','referentielxy_id','meridienxy_id','sourcexy_id','cartographie_id','an_deb_obs',
# 'mois_deb_obs_id','an_arret_mesure','mois_arret_mesure_id','cause_arret_mesure','chgt_traitement_id','existence_ss_unite_id',
# 'observation','x_liie','y_liie')
#names(Historique_traitement)=c('id_placette','no_placette','an_deb_application','mois_deb_application_id','co_traitement','observation','id_historique_traitement')
#names(Intervention)=c('placette_id','no_placette','an_intervention','mois_intervention_id','lib_intervention','observation','id_intervention')
#names(Mesure)=c('placette_id','no_placette','no_mesure','an_mesure','mois_mesure_id','guide','type_suivi_id','observation','an_vegetation','id_mesure')
#names(Arbre)=c('id_placette','no_placette','no_mesure','no_arb_ech','essence_id','co_ech','etat_san_id','statut_av_id','statut_ap_id','epv_av','epv_ap',
# 'diam_050','diam_130','circonference130','hauteur_ech','ebv','ht_pb_s','ht_pb_g','ht_pb_i','designe_id','eclairci_realise_id','regarni_id',
# 'gourmands_02','gourmands_24','branches_sequentielles_02','branches_sequentielles_24','branches_gourmandes_02','branches_gourmandes_24',
# 'branches_indeterminees_02','branches_indeterminees_24','observation','ht_pb_v','ht_pbette','branches_gourmandes_0_4',
# 'id_mesure_echantillon','id_mesure_echantillon_sp','id_mesure_echantillon_bd')
#names(Ss_unite_surface)=c('co_placette', 'no_placette', 'no_ss_unite', 'no_mesure', 'av_ap', 'no_inventaire','longueur', 'largeur', 'surface_ss_unite', 'id_ss_unite_surface')
#names(Inventaire_liste)=c('co_placette', 'no_placette', 'no_ss_unite', 'no_mesure', 'av_ap', 'no_inventaire','circ_diam', 'co_ess', 'borne_inf', 'borne_sup', 'effectif', 'id_inventaire_liste')
wb <- loadWorkbook(Fichier, create = TRUE)
createSheet(wb, name = "Placette")
createSheet(wb, name = "Historique_traitement")
createSheet(wb, name = "Intervention")
createSheet(wb, name = "Mesure")
createSheet(wb, name = "Ss_unite_surface")
createSheet(wb, name = "Inventaire_liste")
createSheet(wb, name = "Numero_arbre")
createSheet(wb, name = "Arbre")
writeWorksheet(wb, Placette , sheet = "Placette") # cree un feuille Placette
writeWorksheet(wb, Historique_traitement , sheet = "Historique_traitement")
writeWorksheet(wb, Intervention , sheet = "Intervention")
writeWorksheet(wb, Mesure , sheet = "Mesure")
writeWorksheet(wb, Ss_unite_surface , sheet = "Ss_unite_surface")
writeWorksheet(wb, Inventaire_liste , sheet = "Inventaire_liste")
writeWorksheet(wb, Numero_arbre , sheet = "Numero_arbre")
writeWorksheet(wb, Arbre , sheet = "Arbre")
saveWorkbook(wb)
## ancienne version à effacer si tout marche
#write.xlsx2(Placette, Fichier, sheetName="Placette", col.names=TRUE, row.names=FALSE, na="", qmethod = "double", dec=",")
# write.xlsx2(Historique_traitement, Fichier, sheetName="Historique_traitement", col.names=TRUE, row.names=FALSE, na="", qmethod = "double", dec=",", append=TRUE)
# write.xlsx2(Intervention, Fichier, sheetName="Intervention", col.names=TRUE, row.names=FALSE, na="", qmethod = "double", dec=",", append=TRUE)
# write.xlsx2(Mesure, Fichier, sheetName="Mesure", col.names=TRUE, row.names=FALSE, na="", qmethod = "double", dec=",", append=TRUE)
# write.xlsx2(Ss_unite_surface, Fichier, sheetName="Ss_unite_surface", col.names=TRUE, row.names=FALSE, na="", qmethod = "double", dec=",", append=TRUE)
# write.xlsx2(Inventaire_liste, Fichier, sheetName="Inventaire_liste", col.names=TRUE, row.names=FALSE, na="", qmethod = "double", dec=",", append=TRUE)
# write.xlsx2(Numero_arbre, Fichier, sheetName="Numero_arbre", col.names=TRUE, row.names=FALSE, na="", qmethod = "double", dec=",", append=TRUE)
# write.xlsx2(Arbre, Fichier, sheetName="Arbre", col.names=TRUE, row.names=FALSE, na="", qmethod = "double", dec=",", append=TRUE)
}
jprenaud-02/coopR documentation built on May 3, 2019, 7:06 p.m.
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
#' @title Chargement des fichiers COOP depuis la base de donnees et export vers xls
#'
#' @description Cette fonction charge les tables au format pret pour l'utilisation des fonctions du package, directement a partir de la Base de donnees
#'
#' @param disp nom complet (lib_disp) des sites qui seront charges, il faut des guillemets et des simples quotes p ex au format "'SITEA', 'SITEB'"
#' @param nom, nom de l'utilisateur dans la base de donnees GIS Coop
#' @param mdp, mot de passe de l'utilisateur
#' @param date, date avec des guillemets par ex "241217" qui apparaitra dans le nom du fichier xls
#' @return un fichier xls
#'
#' @author Ingrid Seynave, A Calas, D Francois, JP Renaud
#' @references Protocole Coop chene... document d Ingrid et Claudine
#'
#' @seealso dico.ch, load_onf_ch, load_bdd_to_xls_ch
#' @examples
#' # A venir
#'
#' @keywords function
#'
#' @include coopR-package.R
#' @family coopR
#' @import DBI
#' @import RPostgreSQL
#' @import XLConnect
#' @import doBy
#' @import plyr
#' @export
# library(RPostgreSQL)
# library(xlsx)
# library(plyr)
# nom="renaud"; mdp ="jeanpierre"; disp = "'MONTRICHARD'"; date= as.character("999999")
# setwd("c:/Donnees")
bd_2_xls_ch <- function (nom="tonNom", mdp = "tonMdP", disp =NULL, date = "date"){
disp2 <- gsub("(^')|('$)", "", disp) # vire les simples quotes en début et en fin
# nom du fichier d'export
Fichier <- paste0(disp2,"_export_", date, ".xls")
# connexion ? la base de donn?es
drv <- dbDriver("PostgreSQL")
con <- postgresqlNewConnection(drv=drv,user=nom,password=mdp,host="postgres-national-priv-4.jouy.inra.fr",
dbname="coop", port=54443)
######## requ?tes
# feuille Placette
query <- paste("SELECT
public.departement.id_departement,
public.foret22.lib_foret,
public.dispositif22.lib_disp,
public.placette22.id_placette,
public.placette22.no_placette,
public.placette22.lib_placette,
public.traitement22.co_traitement,
public.organisme.id_organisme,
public.proprietaire22.code_proprietaire_id,
public.proprietaire22.nom_prop,
public.proprietaire22.comment_prop,
public.region_forestiere.lib_reg_forest,
public.placette22.essence_principale1_id,
public.placette22.essence_principale2_id,
public.placette22.surface,
public.placette22.surface_tot,
public.placette22.topographie_id,
public.placette22.altitude,
public.placette22.pente,
public.placette22.exposition,
public.placette22.ign,
public.placette22.equienne_id,
public.placette22.pure_id,
public.placette22.essence_secondaire1_id,
public.placette22.essence_secondaire2_id,
public.placette22.origine_id,
public.placette22.materiel_genetique,
public.placette22.nb_plants,
public.placette22.espacement,
public.placette22.annee_plant,
public.placette22.mois_plant_id,
public.placette22.age_plant,
public.placette22.an_cernes030,
public.placette22.mois_cernes030_id,
public.placette22.nb_cernes030,
public.placette22.an_cernes130,
public.placette22.mois_cernes130_id,
public.placette22.nb_cernes130,
public.placette22.age_installation,
public.placette22.largeur_cloiso,
public.placette22.entre_axe_cloiso,
public.dispositif22.commune_situation,
public.placette22.xauteur,
public.placette22.yauteur,
public.placette22.referentielxy_id,
public.placette22.meridienxy_id,
public.placette22.sourcexy_id,
public.placette22.cartographie_id,
public.placette22.an_deb_obs,
public.placette22.mois_deb_obs_id,
public.placette22.an_arret_mesure,
public.placette22.mois_arret_mesure_id,
public.placette22.cause_arret_mesure,
public.placette22.chgt_traitement_id,
public.placette22.existence_ss_unite_id,
public.placette22.observation,
public.placette22.x_liie,
public.placette22.y_liie
FROM
public.departement
INNER JOIN public.foret22 ON (public.departement.id_departement = public.foret22.departement_id)
INNER JOIN public.dispositif22 ON (public.foret22.id_foret = public.dispositif22.foret_id)
INNER JOIN public.placette22 ON (public.dispositif22.id_dispositif = public.placette22.dispositif_id)
INNER JOIN public.historique_proprietaire22 ON (public.historique_proprietaire22.dispositif_id = public.dispositif22.id_dispositif)
INNER JOIN public.proprietaire22 ON (public.proprietaire22.id_proprietaire = public.historique_proprietaire22.proprietaire_id)
INNER JOIN public.traitement22 ON (public.placette22.trait1_id = public.traitement22.id_traitement)
INNER JOIN public.organisme ON (public.dispositif22.organisme_id_old = public.organisme.id_organisme)
INNER JOIN public.region_forestiere ON (public.dispositif22.region_forestiere_id = public.region_forestiere.id_region_forestiere)
WHERE
dispositif22.lib_disp in (",disp,") order by id_placette", sep="")
placette <- dbSendQuery(con, query)
Placette <- fetch(placette, -1)
dbClearResult(placette)
# feuille Historique_traitement
query <- paste("SELECT
public.placette22.id_placette,
public.placette22.no_placette,
public.historique_traitement22.an_deb_application,
public.historique_traitement22.mois_deb_application_id,
public.traitement22.co_traitement,
public.historique_traitement22.observation,
public.historique_traitement22.id_historique_traitement
FROM
public.placette22
INNER JOIN public.historique_traitement22 ON (public.placette22.id_placette = public.historique_traitement22.placette_id)
INNER JOIN public.traitement22 ON (public.traitement22.id_traitement = public.historique_traitement22.trait1_id)
INNER JOIN public.dispositif22 ON (public.placette22.dispositif_id = public.dispositif22.id_dispositif)
WHERE
dispositif22.lib_disp in (",disp,")", sep="",
"ORDER BY id_placette, an_deb_application, mois_deb_application_id")
historique_traitement <- dbSendQuery(con, query)
Historique_traitement <- fetch(historique_traitement, -1)
dbClearResult(historique_traitement)
# feuille Intervention
query <- paste("SELECT
public.placette22.id_placette,
public.placette22.no_placette,
public.intervention22.an_intervention,
public.intervention22.mois_intervention_id,
public.intervention22.lib_intervention,
public.intervention22.observation,
public.intervention22.id_intervention
FROM
public.placette22
INNER JOIN public.dispositif22 ON (public.placette22.dispositif_id = public.dispositif22.id_dispositif)
INNER JOIN public.intervention22 ON (public.placette22.id_placette = public.intervention22.placette_id)
WHERE
dispositif22.lib_disp in (",disp,")", sep="",
"ORDER BY id_intervention")
intervention <- dbSendQuery(con, query)
Intervention <- fetch(intervention, -1)
dbClearResult(intervention)
# feuille Mesure
query <- paste("SELECT
public.placette22.id_placette,
public.placette22.no_placette,
public.mesure22.no_mesure,
public.mesure22.an_mesure,
public.mesure22.mois_mesure_id,
public.mesure22.guide,
public.mesure22.type_suivi_id,
public.mesure22.observation,
public.mesure22.an_vegetation,
public.mesure22.id_mesure
FROM
public.placette22
INNER JOIN public.mesure22 ON (public.placette22.id_placette = public.mesure22.placette_id)
INNER JOIN public.dispositif22 ON (public.placette22.dispositif_id = public.dispositif22.id_dispositif)
WHERE
dispositif22.lib_disp in (",disp,")", sep="",
"ORDER BY id_placette, no_mesure")
mesure <- dbSendQuery(con, query)
Mesure <- fetch(mesure, -1)
dbClearResult(mesure)
# feuille Numero_Arbre
query <- paste("SELECT
public.placette22.id_placette,
public.placette22.no_placette,
public.arbre22.no_arb_pl,
public.arbre22.no_arb_sp,
public.arbre22.no_arb_bd,
public.ss_placette22.lib_ss_placette,
public.bandelette22.lib_bandelette,
public.arbre22.observation,
public.arbre22.id_arbre
FROM
public.dispositif22
INNER JOIN public.placette22 ON (public.dispositif22.id_dispositif = public.placette22.dispositif_id)
INNER JOIN public.arbre22 ON (public.placette22.id_placette = public.arbre22.placette_id)
LEFT OUTER JOIN public.bandelette22 ON (public.arbre22.bandelette_id = public.bandelette22.id_bandelette)
LEFT OUTER JOIN public.ss_placette22 ON (public.arbre22.ss_placette_id = public.ss_placette22.id_ss_placette)
WHERE
dispositif22.lib_disp in (",disp,")", sep="",
"ORDER BY id_placette, no_arb_pl, no_arb_sp, no_arb_bd")
numero_arbre <- dbSendQuery(con, query)
Numero_arbre <- fetch(numero_arbre, -1)
dbClearResult(numero_arbre)
# feuille Arbre
query <- paste("SELECT
T.id_placette,
T.no_placette,
T.no_mesure,
T.no_arb_pl as no_arb_ech,
T.essence_id,
T.co_ech,
T.etat_san_id,
T.statut_av_id,
T.statut_ap_id,
NULL AS epv_av,
NULL AS epv_ap,
T.diam_050,
T.diam_130,
T.circonference130,
T.hauteur_ech,
T.ebv,
T.ht_pb_s,
T.ht_pb_g,
T.ht_pb_i,
T.designe_id,
T.eclairci_realise_id,
T.regarni_id,
G1.gourmands_02,
G2.gourmands_24,
BS1.branches_sequentielles_02,
BS2.branches_sequentielles_24,
BG1.branches_gourmandes_02,
BG2.branches_gourmandes_24,
BI1.branches_indeterminees_02,
BI2.branches_indeterminees_24,
T.observation,
T.ht_pb_v,
NULL as ht_pbette,
NULL as branches_gourmandes_0_4,
T.id_arbre,
T.id_mesure,
T.id_mesure_echantillon
FROM
(SELECT
public.placette22.id_placette,
public.placette22.no_placette,
public.mesure22.no_mesure,
public.arbre22.no_arb_pl,
public.arbre22.essence_id,
public.mesure_echantillon22.co_ech,
public.mesure_echantillon22.etat_san_id,
public.mesure_echantillon22.statut_av_id,
public.mesure_echantillon22.statut_ap_id,
public.mesure_echantillon22.diam_050,
public.mesure_echantillon22.diam_130,
public.mesure_echantillon22.circonference130,
public.mesure_echantillon22.hauteur_ech,
public.mesure_echantillon22.ebv,
public.mesure_echantillon22.ht_pb_s,
public.mesure_echantillon22.ht_pb_g,
public.mesure_echantillon22.ht_pb_i,
public.mesure_echantillon22.designe_id,
public.mesure_echantillon22.eclairci_realise_id,
public.mesure_echantillon22.regarni_id,
public.mesure_echantillon22.observation,
public.mesure_echantillon22.ht_pb_v,
public.arbre22.id_arbre,
public.mesure22.id_mesure,
public.mesure_echantillon22.id_mesure_echantillon
FROM
public.dispositif22
INNER JOIN public.placette22 ON (public.dispositif22.id_dispositif = public.placette22.dispositif_id)
INNER JOIN public.arbre22 ON (public.placette22.id_placette = public.arbre22.placette_id)
INNER JOIN public.mesure_echantillon22 ON (public.arbre22.id_arbre = public.mesure_echantillon22.arbre_id)
INNER JOIN public.mesure22 ON (public.mesure_echantillon22.mesure_id = public.mesure22.id_mesure) AND (public.placette22.id_placette = public.mesure22.placette_id)
WHERE
dispositif22.lib_disp in (",disp,")", sep="",") AS T
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon22.id_mesure_echantillon,
public.mesure_branchaison22.effectif as gourmands_02
FROM
public.mesure_echantillon22
INNER JOIN public.mesure_branchaison22 ON (public.mesure_echantillon22.id_mesure_echantillon = public.mesure_branchaison22.mesure_echantillon_id)
WHERE
public.mesure_branchaison22.ramification_id=4 AND public.mesure_branchaison22.ht_basse=0) AS G1
ON (T.id_mesure_echantillon=G1.id_mesure_echantillon)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon22.id_mesure_echantillon,
public.mesure_branchaison22.effectif as gourmands_24
FROM
public.mesure_echantillon22
INNER JOIN public.mesure_branchaison22 ON (public.mesure_echantillon22.id_mesure_echantillon = public.mesure_branchaison22.mesure_echantillon_id)
WHERE
public.mesure_branchaison22.ramification_id=4 AND public.mesure_branchaison22.ht_basse=2) G2
ON (T.id_mesure_echantillon=G2.id_mesure_echantillon)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon22.id_mesure_echantillon,
public.mesure_branchaison22.effectif as branches_sequentielles_02
FROM
public.mesure_echantillon22
INNER JOIN public.mesure_branchaison22 ON (public.mesure_echantillon22.id_mesure_echantillon = public.mesure_branchaison22.mesure_echantillon_id)
WHERE
public.mesure_branchaison22.ramification_id=1 AND public.mesure_branchaison22.ht_basse=0) AS BS1
ON (T.id_mesure_echantillon=BS1.id_mesure_echantillon)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon22.id_mesure_echantillon,
public.mesure_branchaison22.effectif as branches_sequentielles_24
FROM
public.mesure_echantillon22
INNER JOIN public.mesure_branchaison22 ON (public.mesure_echantillon22.id_mesure_echantillon = public.mesure_branchaison22.mesure_echantillon_id)
WHERE
public.mesure_branchaison22.ramification_id=1 AND public.mesure_branchaison22.ht_basse=2) AS BS2
ON (T.id_mesure_echantillon=BS2.id_mesure_echantillon)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon22.id_mesure_echantillon,
public.mesure_branchaison22.effectif as branches_gourmandes_02
FROM
public.mesure_echantillon22
INNER JOIN public.mesure_branchaison22 ON (public.mesure_echantillon22.id_mesure_echantillon = public.mesure_branchaison22.mesure_echantillon_id)
WHERE
public.mesure_branchaison22.ramification_id=2 AND public.mesure_branchaison22.ht_basse=0) AS BG1
ON (T.id_mesure_echantillon=BG1.id_mesure_echantillon)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon22.id_mesure_echantillon,
public.mesure_branchaison22.effectif as branches_gourmandes_24
FROM
public.mesure_echantillon22
INNER JOIN public.mesure_branchaison22 ON (public.mesure_echantillon22.id_mesure_echantillon = public.mesure_branchaison22.mesure_echantillon_id)
WHERE
public.mesure_branchaison22.ramification_id=2 AND public.mesure_branchaison22.ht_basse=2) AS BG2
ON (T.id_mesure_echantillon=BG2.id_mesure_echantillon)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon22.id_mesure_echantillon,
public.mesure_branchaison22.effectif as branches_indeterminees_02
FROM
public.mesure_echantillon22
INNER JOIN public.mesure_branchaison22 ON (public.mesure_echantillon22.id_mesure_echantillon = public.mesure_branchaison22.mesure_echantillon_id)
WHERE
public.mesure_branchaison22.ramification_id=3 AND public.mesure_branchaison22.ht_basse=0) AS BI1
ON (T.id_mesure_echantillon=BI1.id_mesure_echantillon)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon22.id_mesure_echantillon,
public.mesure_branchaison22.effectif as branches_indeterminees_24
FROM
public.mesure_echantillon22
INNER JOIN public.mesure_branchaison22 ON (public.mesure_echantillon22.id_mesure_echantillon = public.mesure_branchaison22.mesure_echantillon_id)
WHERE
public.mesure_branchaison22.ramification_id=3 AND public.mesure_branchaison22.ht_basse=2) AS BI2
ON (T.id_mesure_echantillon=BI2.id_mesure_echantillon)
ORDER BY id_placette, no_mesure, no_arb_ech")
arbre_pl <- dbSendQuery(con, query)
Arbre_pl <- fetch(arbre_pl, -1)
dbClearResult(arbre_pl)
query <- paste("SELECT
T.id_placette,
T.no_placette,
T.no_mesure,
T.no_arb_sp as no_arb_ech,
T.essence_id,
T.co_ech,
T.etat_san_id,
T.statut_av_id,
T.statut_ap_id,
NULL AS epv_av,
NULL AS epv_ap,
T.diam_050,
T.diam_130,
T.circonference130,
T.hauteur_ech,
T.ebv,
T.ht_pb_s,
T.ht_pb_g,
T.ht_pb_i,
T.designe_id,
T.eclairci_realise_id,
T.regarni_id,
G1.gourmands_02,
G2.gourmands_24,
BS1.branches_sequentielles_02,
BS2.branches_sequentielles_24,
BG1.branches_gourmandes_02,
BG2.branches_gourmandes_24,
BI1.branches_indeterminees_02,
BI2.branches_indeterminees_24,
T.observation,
T.ht_pb_v,
NULL as ht_pbette,
NULL as branches_gourmandes_0_4,
T.id_arbre,
T.id_mesure,
T.id_mesure_echantillon_sp
FROM
(SELECT
public.placette22.id_placette,
public.placette22.no_placette,
public.mesure22.no_mesure,
public.arbre22.no_arb_sp,
public.arbre22.essence_id,
public.mesure_echantillon_sp22.co_ech,
public.mesure_echantillon_sp22.etat_san_id,
public.mesure_echantillon_sp22.statut_av_id,
public.mesure_echantillon_sp22.statut_ap_id,
public.mesure_echantillon_sp22.diam_050,
public.mesure_echantillon_sp22.diam_130,
public.mesure_echantillon_sp22.circonference130,
public.mesure_echantillon_sp22.hauteur_ech,
public.mesure_echantillon_sp22.ebv,
public.mesure_echantillon_sp22.ht_pb_s,
public.mesure_echantillon_sp22.ht_pb_g,
public.mesure_echantillon_sp22.ht_pb_i,
public.mesure_echantillon_sp22.designe_id,
public.mesure_echantillon_sp22.eclairci_realise_id,
public.mesure_echantillon_sp22.regarni_id,
public.mesure_echantillon_sp22.observation,
public.mesure_echantillon_sp22.ht_pb_v,
public.arbre22.id_arbre,
public.mesure22.id_mesure,
public.mesure_echantillon_sp22.id_mesure_echantillon_sp
FROM
public.dispositif22
INNER JOIN public.placette22 ON (public.dispositif22.id_dispositif = public.placette22.dispositif_id)
INNER JOIN public.arbre22 ON (public.placette22.id_placette = public.arbre22.placette_id)
INNER JOIN public.mesure_echantillon_sp22 ON (public.arbre22.id_arbre = public.mesure_echantillon_sp22.arbre_id)
INNER JOIN public.mesure22 ON (public.mesure_echantillon_sp22.mesure_id = public.mesure22.id_mesure) AND (public.placette22.id_placette = public.mesure22.placette_id)
WHERE
dispositif22.lib_disp in (",disp,")", sep="",") AS T
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_sp22.id_mesure_echantillon_sp,
public.mesure_branchaison_sp22.effectif as gourmands_02
FROM
public.mesure_echantillon_sp22
INNER JOIN public.mesure_branchaison_sp22 ON (public.mesure_echantillon_sp22.id_mesure_echantillon_sp = public.mesure_branchaison_sp22.mesure_echantillon_sp_id)
WHERE
public.mesure_branchaison_sp22.ramification_id=4 AND public.mesure_branchaison_sp22.ht_basse=0) AS G1
ON (T.id_mesure_echantillon_sp=G1.id_mesure_echantillon_sp)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_sp22.id_mesure_echantillon_sp,
public.mesure_branchaison_sp22.effectif as gourmands_24
FROM
public.mesure_echantillon_sp22
INNER JOIN public.mesure_branchaison_sp22 ON (public.mesure_echantillon_sp22.id_mesure_echantillon_sp = public.mesure_branchaison_sp22.mesure_echantillon_sp_id)
WHERE
public.mesure_branchaison_sp22.ramification_id=4 AND public.mesure_branchaison_sp22.ht_basse=2) G2
ON (T.id_mesure_echantillon_sp=G2.id_mesure_echantillon_sp)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_sp22.id_mesure_echantillon_sp,
public.mesure_branchaison_sp22.effectif as branches_sequentielles_02
FROM
public.mesure_echantillon_sp22
INNER JOIN public.mesure_branchaison_sp22 ON (public.mesure_echantillon_sp22.id_mesure_echantillon_sp = public.mesure_branchaison_sp22.mesure_echantillon_sp_id)
WHERE
public.mesure_branchaison_sp22.ramification_id=1 AND public.mesure_branchaison_sp22.ht_basse=0) AS BS1
ON (T.id_mesure_echantillon_sp=BS1.id_mesure_echantillon_sp)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_sp22.id_mesure_echantillon_sp,
public.mesure_branchaison_sp22.effectif as branches_sequentielles_24
FROM
public.mesure_echantillon_sp22
INNER JOIN public.mesure_branchaison_sp22 ON (public.mesure_echantillon_sp22.id_mesure_echantillon_sp = public.mesure_branchaison_sp22.mesure_echantillon_sp_id)
WHERE
public.mesure_branchaison_sp22.ramification_id=1 AND public.mesure_branchaison_sp22.ht_basse=2) AS BS2
ON (T.id_mesure_echantillon_sp=BS2.id_mesure_echantillon_sp)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_sp22.id_mesure_echantillon_sp,
public.mesure_branchaison_sp22.effectif as branches_gourmandes_02
FROM
public.mesure_echantillon_sp22
INNER JOIN public.mesure_branchaison_sp22 ON (public.mesure_echantillon_sp22.id_mesure_echantillon_sp = public.mesure_branchaison_sp22.mesure_echantillon_sp_id)
WHERE
public.mesure_branchaison_sp22.ramification_id=2 AND public.mesure_branchaison_sp22.ht_basse=0) AS BG1
ON (T.id_mesure_echantillon_sp=BG1.id_mesure_echantillon_sp)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_sp22.id_mesure_echantillon_sp,
public.mesure_branchaison_sp22.effectif as branches_gourmandes_24
FROM
public.mesure_echantillon_sp22
INNER JOIN public.mesure_branchaison_sp22 ON (public.mesure_echantillon_sp22.id_mesure_echantillon_sp = public.mesure_branchaison_sp22.mesure_echantillon_sp_id)
WHERE
public.mesure_branchaison_sp22.ramification_id=2 AND public.mesure_branchaison_sp22.ht_basse=2) AS BG2
ON (T.id_mesure_echantillon_sp=BG2.id_mesure_echantillon_sp)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_sp22.id_mesure_echantillon_sp,
public.mesure_branchaison_sp22.effectif as branches_indeterminees_02
FROM
public.mesure_echantillon_sp22
INNER JOIN public.mesure_branchaison_sp22 ON (public.mesure_echantillon_sp22.id_mesure_echantillon_sp = public.mesure_branchaison_sp22.mesure_echantillon_sp_id)
WHERE
public.mesure_branchaison_sp22.ramification_id=3 AND public.mesure_branchaison_sp22.ht_basse=0) AS BI1
ON (T.id_mesure_echantillon_sp=BI1.id_mesure_echantillon_sp)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_sp22.id_mesure_echantillon_sp,
public.mesure_branchaison_sp22.effectif as branches_indeterminees_24
FROM
public.mesure_echantillon_sp22
INNER JOIN public.mesure_branchaison_sp22 ON (public.mesure_echantillon_sp22.id_mesure_echantillon_sp = public.mesure_branchaison_sp22.mesure_echantillon_sp_id)
WHERE
public.mesure_branchaison_sp22.ramification_id=3 AND public.mesure_branchaison_sp22.ht_basse=2) AS BI2
ON (T.id_mesure_echantillon_sp=BI2.id_mesure_echantillon_sp)
ORDER BY id_placette, no_mesure, no_arb_ech")
arbre_sp <- dbSendQuery(con, query)
#Arbre_sp <- fetch(arbre_sp, -1)
Arbre_sp <- dbFetch(arbre_sp, -1 )
#head(Arbre_sp)
query <- paste("SELECT
T.id_placette,
T.no_placette,
T.no_mesure,
T.no_arb_bd as no_arb_ech,
T.essence_id,
T.co_ech,
T.etat_san_id,
T.statut_av_id,
T.statut_ap_id,
NULL AS epv_av,
NULL AS epv_ap,
T.diam_050,
T.diam_130,
T.circonference130,
T.hauteur_ech,
T.ebv,
T.ht_pb_s,
T.ht_pb_g,
T.ht_pb_i,
T.designe_id,
T.eclairci_realise_id,
T.regarni_id,
G1.gourmands_02,
G2.gourmands_24,
BS1.branches_sequentielles_02,
BS2.branches_sequentielles_24,
BG1.branches_gourmandes_02,
BG2.branches_gourmandes_24,
BI1.branches_indeterminees_02,
BI2.branches_indeterminees_24,
T.observation,
T.ht_pb_v,
NULL as ht_pbette,
NULL as branches_gourmandes_0_4,
T.id_arbre,
T.id_mesure,
T.id_mesure_echantillon_bd
FROM
(SELECT
public.placette22.id_placette,
public.placette22.no_placette,
public.mesure22.no_mesure,
public.arbre22.no_arb_bd,
public.arbre22.essence_id,
public.mesure_echantillon_bd22.co_ech,
public.mesure_echantillon_bd22.etat_san_id,
public.mesure_echantillon_bd22.statut_av_id,
public.mesure_echantillon_bd22.statut_ap_id,
public.mesure_echantillon_bd22.diam_050,
public.mesure_echantillon_bd22.diam_130,
public.mesure_echantillon_bd22.circonference130,
public.mesure_echantillon_bd22.hauteur_ech,
public.mesure_echantillon_bd22.ebv,
public.mesure_echantillon_bd22.ht_pb_s,
public.mesure_echantillon_bd22.ht_pb_g,
public.mesure_echantillon_bd22.ht_pb_i,
public.mesure_echantillon_bd22.designe_id,
public.mesure_echantillon_bd22.eclairci_realise_id,
public.mesure_echantillon_bd22.regarni_id,
public.mesure_echantillon_bd22.observation,
public.mesure_echantillon_bd22.ht_pb_v,
public.arbre22.id_arbre,
public.mesure22.id_mesure,
public.mesure_echantillon_bd22.id_mesure_echantillon_bd
FROM
public.dispositif22
INNER JOIN public.placette22 ON (public.dispositif22.id_dispositif = public.placette22.dispositif_id)
INNER JOIN public.arbre22 ON (public.placette22.id_placette = public.arbre22.placette_id)
INNER JOIN public.mesure_echantillon_bd22 ON (public.arbre22.id_arbre = public.mesure_echantillon_bd22.arbre_id)
INNER JOIN public.mesure22 ON (public.mesure_echantillon_bd22.mesure_id = public.mesure22.id_mesure) AND (public.placette22.id_placette = public.mesure22.placette_id)
WHERE
dispositif22.lib_disp in (",disp,")", sep="",") AS T
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_bd22.id_mesure_echantillon_bd,
public.mesure_branchaison_bd22.effectif as gourmands_02
FROM
public.mesure_echantillon_bd22
INNER JOIN public.mesure_branchaison_bd22 ON (public.mesure_echantillon_bd22.id_mesure_echantillon_bd = public.mesure_branchaison_bd22.mesure_echantillon_bd_id)
WHERE
public.mesure_branchaison_bd22.ramification_id=4 AND public.mesure_branchaison_bd22.ht_basse=0) AS G1
ON (T.id_mesure_echantillon_bd=G1.id_mesure_echantillon_bd)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_bd22.id_mesure_echantillon_bd,
public.mesure_branchaison_bd22.effectif as gourmands_24
FROM
public.mesure_echantillon_bd22
INNER JOIN public.mesure_branchaison_bd22 ON (public.mesure_echantillon_bd22.id_mesure_echantillon_bd = public.mesure_branchaison_bd22.mesure_echantillon_bd_id)
WHERE
public.mesure_branchaison_bd22.ramification_id=4 AND public.mesure_branchaison_bd22.ht_basse=2) G2
ON (T.id_mesure_echantillon_bd=G2.id_mesure_echantillon_bd)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_bd22.id_mesure_echantillon_bd,
public.mesure_branchaison_bd22.effectif as branches_sequentielles_02
FROM
public.mesure_echantillon_bd22
INNER JOIN public.mesure_branchaison_bd22 ON (public.mesure_echantillon_bd22.id_mesure_echantillon_bd = public.mesure_branchaison_bd22.mesure_echantillon_bd_id)
WHERE
public.mesure_branchaison_bd22.ramification_id=1 AND public.mesure_branchaison_bd22.ht_basse=0) AS BS1
ON (T.id_mesure_echantillon_bd=BS1.id_mesure_echantillon_bd)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_bd22.id_mesure_echantillon_bd,
public.mesure_branchaison_bd22.effectif as branches_sequentielles_24
FROM
public.mesure_echantillon_bd22
INNER JOIN public.mesure_branchaison_bd22 ON (public.mesure_echantillon_bd22.id_mesure_echantillon_bd = public.mesure_branchaison_bd22.mesure_echantillon_bd_id)
WHERE
public.mesure_branchaison_bd22.ramification_id=1 AND public.mesure_branchaison_bd22.ht_basse=2) AS BS2
ON (T.id_mesure_echantillon_bd=BS2.id_mesure_echantillon_bd)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_bd22.id_mesure_echantillon_bd,
public.mesure_branchaison_bd22.effectif as branches_gourmandes_02
FROM
public.mesure_echantillon_bd22
INNER JOIN public.mesure_branchaison_bd22 ON (public.mesure_echantillon_bd22.id_mesure_echantillon_bd = public.mesure_branchaison_bd22.mesure_echantillon_bd_id)
WHERE
public.mesure_branchaison_bd22.ramification_id=2 AND public.mesure_branchaison_bd22.ht_basse=0) AS BG1
ON (T.id_mesure_echantillon_bd=BG1.id_mesure_echantillon_bd)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_bd22.id_mesure_echantillon_bd,
public.mesure_branchaison_bd22.effectif as branches_gourmandes_24
FROM
public.mesure_echantillon_bd22
INNER JOIN public.mesure_branchaison_bd22 ON (public.mesure_echantillon_bd22.id_mesure_echantillon_bd = public.mesure_branchaison_bd22.mesure_echantillon_bd_id)
WHERE
public.mesure_branchaison_bd22.ramification_id=2 AND public.mesure_branchaison_bd22.ht_basse=2) AS BG2
ON (T.id_mesure_echantillon_bd=BG2.id_mesure_echantillon_bd)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_bd22.id_mesure_echantillon_bd,
public.mesure_branchaison_bd22.effectif as branches_indeterminees_02
FROM
public.mesure_echantillon_bd22
INNER JOIN public.mesure_branchaison_bd22 ON (public.mesure_echantillon_bd22.id_mesure_echantillon_bd = public.mesure_branchaison_bd22.mesure_echantillon_bd_id)
WHERE
public.mesure_branchaison_bd22.ramification_id=3 AND public.mesure_branchaison_bd22.ht_basse=0) AS BI1
ON (T.id_mesure_echantillon_bd=BI1.id_mesure_echantillon_bd)
LEFT OUTER JOIN
(SELECT DISTINCT
public.mesure_echantillon_bd22.id_mesure_echantillon_bd,
public.mesure_branchaison_bd22.effectif as branches_indeterminees_24
FROM
public.mesure_echantillon_bd22
INNER JOIN public.mesure_branchaison_bd22 ON (public.mesure_echantillon_bd22.id_mesure_echantillon_bd = public.mesure_branchaison_bd22.mesure_echantillon_bd_id)
WHERE
public.mesure_branchaison_bd22.ramification_id=3 AND public.mesure_branchaison_bd22.ht_basse=2) AS BI2
ON (T.id_mesure_echantillon_bd=BI2.id_mesure_echantillon_bd)
ORDER BY id_placette, no_mesure, no_arb_ech")
#arbre_bd <- dbSendQuery(con, query)
arbre_bd <- dbSendStatement(con, query)
Arbre_bd <- dbFetch(arbre_bd, -1)
dbClearResult(arbre_bd)
pos = grep("id_mesure_echantillon", names(Arbre_bd))
names(Arbre_bd)[pos] <- "id_mesure_echantillon"
pos = grep("id_mesure_echantillon", names(Arbre_sp))
names(Arbre_sp)[pos] <- "id_mesure_echantillon"
pos = grep("id_mesure_echantillon", names(Arbre_pl))
names(Arbre_pl)[pos] <- "id_mesure_echantillon"
Arbre <- rbind.fill(Arbre_pl, Arbre_sp, Arbre_bd)
Arbre <- Arbre[order(Arbre$id_placette, Arbre$no_mesure, Arbre$no_arb_ech),]
# feuille SS_unite_surface
query <- paste("SELECT
public.placette22.id_placette,
public.placette22.no_placette,
public.ss_placette22.lib_ss_placette AS no_ss_unite,
-2 AS no_mesure,
-2 AS av_ap_id,
-2 AS no_inventaire,
public.ss_placette22.longueur,
public.ss_placette22.largeur,
public.ss_placette22.surface,
public.ss_placette22.id_ss_placette as id_ss_unite
FROM
public.placette22
INNER JOIN public.ss_placette22 on (public.ss_placette22.placette_id = public.placette22.id_placette)
INNER JOIN public.dispositif22 ON (public.placette22.dispositif_id = public.dispositif22.id_dispositif)
WHERE
dispositif22.lib_disp in (",disp,")", sep="")
ss_unite_surface_sp <- dbSendQuery(con, query)
Ss_unite_surface_sp <- fetch(ss_unite_surface_sp, -1)
dbClearResult(ss_unite_surface_sp)
query <- paste("SELECT
public.placette22.id_placette,
public.placette22.no_placette,
public.bandelette22.lib_bandelette AS no_ss_unite,
public.mesure22.no_mesure,
public.bandelette_surface22.av_ap_id,
public.bandelette_surface22.no_inventaire,
public.bandelette_surface22.longueur,
public.bandelette_surface22.largeur,
public.bandelette_surface22.surface,
public.bandelette22.id_bandelette as id_ss_unite,
public.bandelette_surface22.id_bandelette_surface as id_ss_unite_surface
FROM
public.placette22
INNER JOIN public.bandelette22 on (public.bandelette22.placette_id = public.placette22.id_placette)
INNER JOIN public.mesure22 ON (public.placette22.id_placette = public.mesure22.placette_id)
INNER JOIN public.bandelette_surface22 ON (public.bandelette22.id_bandelette = public.bandelette_surface22.bandelette_id) AND (public.mesure22.id_mesure = public.bandelette_surface22.mesure_id)
INNER JOIN public.dispositif22 ON (public.placette22.dispositif_id = public.dispositif22.id_dispositif)
WHERE
dispositif22.lib_disp in (",disp,")", sep="",
"ORDER BY id_placette, no_mesure, av_ap_id, no_inventaire, no_ss_unite")
ss_unite_surface_bd <- dbSendQuery(con, query)
Ss_unite_surface_bd <- fetch(ss_unite_surface_bd, -1)
dbClearResult(ss_unite_surface_bd)
Ss_unite_surface <- rbind.fill(Ss_unite_surface_sp, Ss_unite_surface_bd)
# Feuille Inventaire_liste
query <- paste("SELECT
public.placette22.id_placette,
public.placette22.no_placette,
public.bandelette22.lib_bandelette,
public.mesure22.no_mesure,
public.bandelette_surface22.av_ap_id,
public.bandelette_surface22.no_inventaire,
public.mesure_inventaire22.circ_diam_id,
public.mesure_inventaire22.essence_id,
public.mesure_inventaire22.borne_inf,
public.mesure_inventaire22.borne_sup,
public.mesure_inventaire22.effectif,
public.mesure_inventaire22.id_mesure_inventaire
FROM
public.dispositif22
INNER JOIN public.placette22 ON (public.dispositif22.id_dispositif = public.placette22.dispositif_id)
INNER JOIN public.bandelette22 ON (public.placette22.id_placette = public.bandelette22.placette_id)
INNER JOIN public.bandelette_surface22 ON (public.bandelette22.id_bandelette = public.bandelette_surface22.bandelette_id)
INNER JOIN public.mesure22 ON (public.bandelette_surface22.mesure_id = public.mesure22.id_mesure)
AND (public.placette22.id_placette = public.mesure22.placette_id)
INNER JOIN public.mesure_inventaire22 ON (public.bandelette_surface22.id_bandelette_surface = public.mesure_inventaire22.bandelette_surface_id)
WHERE
dispositif22.lib_disp in (",disp,")", sep="",
"ORDER BY id_placette, no_mesure, av_ap_id, no_inventaire, lib_bandelette, essence_id, borne_inf")
inventaire_liste <- dbSendQuery(con, query)
Inventaire_liste <- fetch(inventaire_liste, -1)
dbClearResult(inventaire_liste)
######## cr?ation du fichier excel
#names(Placette)=c('id_departement','lib_foret','lib_disp','id_placette','no_placette','lib_placette','co_traitement','id_organisme','code_proprietaire_id',
# 'nom_prop','comment_prop','lib_reg_forest','essence_principale1_id','essence_principale2_id','surface','surface_tot','topographie_id',
# 'altitude','pente','exposition','ign','equienne_id','pure_id','essence_secondaire1_id','essence_secondaire2_id','origine_id',
# 'materiel_genetique','nb_plants','espacement','annee_plant','mois_plant_id','age_plant','an_cernes030','mois_cernes030_id',
# 'nb_cernes030','an_cernes130','mois_cernes130_id','nb_cernes130','age_installation','largeur_cloiso','entre_axe_cloiso',
# 'commune_situation','xauteur','yauteur','referentielxy_id','meridienxy_id','sourcexy_id','cartographie_id','an_deb_obs',
# 'mois_deb_obs_id','an_arret_mesure','mois_arret_mesure_id','cause_arret_mesure','chgt_traitement_id','existence_ss_unite_id',
# 'observation','x_liie','y_liie')
#names(Historique_traitement)=c('id_placette','no_placette','an_deb_application','mois_deb_application_id','co_traitement','observation','id_historique_traitement')
#names(Intervention)=c('placette_id','no_placette','an_intervention','mois_intervention_id','lib_intervention','observation','id_intervention')
#names(Mesure)=c('placette_id','no_placette','no_mesure','an_mesure','mois_mesure_id','guide','type_suivi_id','observation','an_vegetation','id_mesure')
#names(Arbre)=c('id_placette','no_placette','no_mesure','no_arb_ech','essence_id','co_ech','etat_san_id','statut_av_id','statut_ap_id','epv_av','epv_ap',
# 'diam_050','diam_130','circonference130','hauteur_ech','ebv','ht_pb_s','ht_pb_g','ht_pb_i','designe_id','eclairci_realise_id','regarni_id',
# 'gourmands_02','gourmands_24','branches_sequentielles_02','branches_sequentielles_24','branches_gourmandes_02','branches_gourmandes_24',
# 'branches_indeterminees_02','branches_indeterminees_24','observation','ht_pb_v','ht_pbette','branches_gourmandes_0_4',
# 'id_mesure_echantillon','id_mesure_echantillon_sp','id_mesure_echantillon_bd')
#names(Ss_unite_surface)=c('co_placette', 'no_placette', 'no_ss_unite', 'no_mesure', 'av_ap', 'no_inventaire','longueur', 'largeur', 'surface_ss_unite', 'id_ss_unite_surface')
#names(Inventaire_liste)=c('co_placette', 'no_placette', 'no_ss_unite', 'no_mesure', 'av_ap', 'no_inventaire','circ_diam', 'co_ess', 'borne_inf', 'borne_sup', 'effectif', 'id_inventaire_liste')
wb <- loadWorkbook(Fichier, create = TRUE)
createSheet(wb, name = "Placette")
createSheet(wb, name = "Historique_traitement")
createSheet(wb, name = "Intervention")
createSheet(wb, name = "Mesure")
createSheet(wb, name = "Ss_unite_surface")
createSheet(wb, name = "Inventaire_liste")
createSheet(wb, name = "Numero_arbre")
createSheet(wb, name = "Arbre")
writeWorksheet(wb, Placette , sheet = "Placette") # cree un feuille Placette
writeWorksheet(wb, Historique_traitement , sheet = "Historique_traitement")
writeWorksheet(wb, Intervention , sheet = "Intervention")
writeWorksheet(wb, Mesure , sheet = "Mesure")
writeWorksheet(wb, Ss_unite_surface , sheet = "Ss_unite_surface")
writeWorksheet(wb, Inventaire_liste , sheet = "Inventaire_liste")
writeWorksheet(wb, Numero_arbre , sheet = "Numero_arbre")
writeWorksheet(wb, Arbre , sheet = "Arbre")
saveWorkbook(wb)
## ancienne version à effacer si tout marche
#write.xlsx2(Placette, Fichier, sheetName="Placette", col.names=TRUE, row.names=FALSE, na="", qmethod = "double", dec=",")
# write.xlsx2(Historique_traitement, Fichier, sheetName="Historique_traitement", col.names=TRUE, row.names=FALSE, na="", qmethod = "double", dec=",", append=TRUE)
# write.xlsx2(Intervention, Fichier, sheetName="Intervention", col.names=TRUE, row.names=FALSE, na="", qmethod = "double", dec=",", append=TRUE)
# write.xlsx2(Mesure, Fichier, sheetName="Mesure", col.names=TRUE, row.names=FALSE, na="", qmethod = "double", dec=",", append=TRUE)
# write.xlsx2(Ss_unite_surface, Fichier, sheetName="Ss_unite_surface", col.names=TRUE, row.names=FALSE, na="", qmethod = "double", dec=",", append=TRUE)
# write.xlsx2(Inventaire_liste, Fichier, sheetName="Inventaire_liste", col.names=TRUE, row.names=FALSE, na="", qmethod = "double", dec=",", append=TRUE)
# write.xlsx2(Numero_arbre, Fichier, sheetName="Numero_arbre", col.names=TRUE, row.names=FALSE, na="", qmethod = "double", dec=",", append=TRUE)
# write.xlsx2(Arbre, Fichier, sheetName="Arbre", col.names=TRUE, row.names=FALSE, na="", qmethod = "double", dec=",", append=TRUE)
}
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
Embedding an R snippet on your website
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.