/* 
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 * Author:  max
 * Created: 17 déc. 2019
 */

---------------
CREATE OR REPLACE VIEW public.v_transdecdeshchim AS
 SELECT t_itk.iditk,
    t_itkdeshchim.iditkdeshchim,
    t_itkdeshchim.decadedeshchim,
        CASE
            WHEN t_itkdeshchim.decadedeshchim < t_itk.decadesemis THEN t_itkdeshchim.decadedeshchim + 36
            ELSE t_itkdeshchim.decadedeshchim
        END AS decadedeshchimtrans
   FROM t_itk
     JOIN t_itkdeshchim ON t_itk.iditk = t_itkdeshchim.iditk;

ALTER TABLE public.v_transdecdeshchim
    OWNER TO oderaroot;

---------------
CREATE OR REPLACE VIEW public.v_transdecinterculture AS
 SELECT t_itk.iditk,
    t_itktravsuperf.iditktravsuperf,
    t_itktravsuperf.decadetravsuperf,
        CASE
            WHEN t_itktravsuperf.decadetravsuperf < t_itk.decadesemis THEN t_itktravsuperf.decadetravsuperf + 36
            ELSE t_itktravsuperf.decadetravsuperf
        END AS decadeinterculturetrans
   FROM t_itk
     JOIN t_itktravsuperf ON t_itk.iditk = t_itktravsuperf.iditk;

ALTER TABLE public.v_transdecinterculture
    OWNER TO oderaroot;

---------------
CREATE OR REPLACE VIEW public.v_transdeclabour AS
 SELECT t_itk.iditk,
    t_itk.decadesemis,
    t_itk.decadelabour,
        CASE
            WHEN t_itk.decadelabour <= 0 THEN NULL::integer
            WHEN t_itk.decadelabour < t_itk.decadesemis THEN t_itk.decadelabour + 36
            ELSE t_itk.decadelabour
        END AS decadelabourtrans
   FROM t_itk;

ALTER TABLE public.v_transdeclabour
    OWNER TO oderaroot;

---------------

CREATE OR REPLACE VIEW public.v_deshmeca_intervprec AS
 SELECT t_itkdeshmeca.iditk,
    t_itkdeshmeca.iditkdeshmeca,
    t_itkdeshmeca.decadedeshmeca,
    t_itkdeshmeca.idoutildeshmeca,
    t_itkdeshmeca.idoutil,
    v_decadeintervprec.decade_intervprec
   FROM t_itkdeshmeca
     LEFT JOIN v_decadeintervprec ON v_decadeintervprec.iditk = t_itkdeshmeca.iditk AND v_decadeintervprec.decade = t_itkdeshmeca.decadedeshmeca;

ALTER TABLE public.v_deshmeca_intervprec
    OWNER TO oderaroot;

---------------

CREATE OR REPLACE VIEW public.v_desherbage_all AS
 SELECT t_itk.iditk,
    desh.iditkdeshmeca,
    desh.decade,
    desh.decade_transposee,
    desh.idoutil,
    t_itk.decadesemis
   FROM ( SELECT v_transdecdeshmeca.iditk,
            v_transdecdeshmeca.iditkdeshmeca,
            v_transdecdeshmeca.decadedeshmeca AS decade,
            v_transdecdeshmeca.decadedeshmecatrans AS decade_transposee,
            v_transdecdeshmeca.idoutildeshmeca AS idoutil
           FROM v_transdecdeshmeca
        UNION
         SELECT v_transdecdeshchim.iditk,
            NULL::integer AS iditkdeshmeca,
            v_transdecdeshchim.decadedeshchim AS decade,
            v_transdecdeshchim.decadedeshchimtrans AS decade_transposee,
            'c'::character varying AS idoutil
           FROM v_transdecdeshchim
        UNION
         SELECT v_transdecinterculture.iditk,
            NULL::integer AS iditkdeshmeca,
            v_transdecinterculture.decadetravsuperf AS decade,
            v_transdecinterculture.decadeinterculturetrans AS decade_transposee,
            'i'::character varying AS idoutil
           FROM v_transdecinterculture
        UNION
         SELECT v_transdeclabour.iditk,
            NULL::integer AS iditkdeshmeca,
            v_transdeclabour.decadelabour AS decade,
            v_transdeclabour.decadelabourtrans AS decade_transposee,
            'l'::character varying AS idoutil
           FROM v_transdeclabour
        UNION
         SELECT t_itk_1.iditk,
            NULL::integer AS iditkdeshmeca,
            t_itk_1.decadesemis AS decade,
            t_itk_1.decadesemis AS decade_transposee,
            's'::character varying AS idoutil
           FROM t_itk t_itk_1) desh
     LEFT JOIN t_itk ON t_itk.iditk = desh.iditk
  ORDER BY t_itk.iditk, desh.decade_transposee;

ALTER TABLE public.v_desherbage_all
    OWNER TO oderaroot;

----------------