Rapport de consommation: ajouter les immo au réel

[auf_coda_additions.git] / db / procedures / auf_p_intero_cumul_dep_rec.sql

USE [coda-prod]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[auf_p_intero_cumul_dep_rec]
    @projet varchar(6),
    @exercice int,
    @groupe varchar(10)
AS
SET NOCOUNT ON
DECLARE @periode int
DECLARE @like_el3 varchar(20)

SELECT @periode = 12

IF isnumeric(@projet) = 1 OR len(@projet) > 2
    SELECT @like_el3 = @projet+'%'
ELSE
    SELECT @like_el3 = '%'+@projet

CREATE TABLE #cumuls (
    el3  varchar(12),
    typecumul varchar(12) ,
    montant money null
)

-- DEPENSES BUDGET
INSERT INTO #cumuls
SELECT
    el3,
    'DEP-BUDGET' typecumul,
    SUM(full_value) mont
FROM oas_balance, oas_grplist imp
WHERE
    oas_balance.cmpcode = 'AUF'
    AND imp.cmpcode = 'AUF'
    AND yr = @exercice
    AND period BETWEEN 1 AND @periode
    AND curcode = 'EUR'
    AND repbasis = 5
    AND balcode ='BUDGET'
    AND (el1 LIKE '2%' OR el1 LIKE '6%' OR el1 LIKE '9%')
    -- on exclut les repartitions --
    AND el1 NOT LIKE '96%'
    AND el1 NOT LIKE '97%'
    AND el1 NOT LIKE '98%'
    AND el3 LIKE @like_el3
    AND el3 = imp.code
    AND imp.elmlevel = 3
    AND imp.grpcode LIKE @groupe
GROUP BY el3

-- DEPENSES REEL
INSERT INTO #cumuls
SELECT
    el3,
    'DEP-REEL' typecumul,
    SUM(full_value) mont
FROM oas_balance, oas_grplist imp
WHERE
    oas_balance.cmpcode = 'AUF'
    AND imp.cmpcode = 'AUF'
    AND yr = @exercice
    AND period BETWEEN 1 AND @periode
    AND curcode = 'EUR'
    AND repbasis = 5
    AND balcode ='REEL'
    AND (el1 LIKE '2%' OR el1 LIKE '6%' OR el1 LIKE '9%')
    -- on exclut les repartitions
    AND el1 NOT LIKE '96%'
    AND el1 NOT LIKE '97%'
    AND el1 NOT LIKE '98%'
    AND el3 LIKE @like_el3
    AND el3 = imp.code
    AND imp.elmlevel = 3
    AND imp.grpcode LIKE @groupe
GROUP BY el3

-- RECETTES BUDGET
INSERT INTO #cumuls
SELECT
    el3,
    'REC-BUDGET' typecumul,
    SUM(full_value) mont
FROM oas_balance, oas_grplist imp
WHERE
    oas_balance.cmpcode = 'AUF'
    AND imp.cmpcode = 'AUF'
    AND yr = @exercice
    AND period BETWEEN 1 AND @periode
    AND curcode = 'EUR'
    AND repbasis = 5
    AND balcode ='BUDGET'
    AND (el1 LIKE '1%' OR el1 LIKE '7%')
    AND el3 LIKE @like_el3
    AND el3 = imp.code
    AND imp.elmlevel = 3
    AND imp.grpcode LIKE @groupe
GROUP BY el3

-- RECETTES REEL
INSERT INTO #cumuls
SELECT
    el3,
    'REC-REEL' typecumul,
    SUM(full_value) mont
FROM oas_balance, oas_grplist imp
WHERE
    oas_balance.cmpcode = 'AUF'
    AND imp.cmpcode = 'AUF'
    AND yr = @exercice
    AND period BETWEEN 1 AND @periode
    AND curcode = 'EUR'
    AND repbasis = 5
    AND balcode ='REEL'
    AND (el1 LIKE '1%' OR el1 LIKE '7%')
    AND el3 LIKE @like_el3
    AND el3 = imp.code
    AND imp.elmlevel = 3
    AND imp.grpcode LIKE @groupe
GROUP BY el3

IF @exercice = YEAR(GETDATE())
BEGIN
    -- PRE-ENGAGEMENT --
    INSERT INTO #cumuls
    SELECT
        el3,
        'PRENGAGEMENT' typecumul,
        SUM(full_value) mont
    FROM oas_balance, oas_grplist imp
    WHERE
        oas_balance.cmpcode = 'AUF'
        AND imp.cmpcode = 'AUF'
        AND curcode = 'EUR'
        AND repbasis = 5
        AND balcode = 'PRENGAGEMENT'
        AND el1 NOT LIKE '4%'
        AND el1 NOT LIKE '96%'
        AND el1 NOT LIKE '97%'
        AND el1 NOT LIKE '98%'
        AND el3 LIKE @like_el3
        AND el3 = imp.code
        AND imp.elmlevel = 3
        AND imp.grpcode LIKE @groupe
    GROUP BY el3

    -- ENGAGEMENT --
    INSERT INTO #cumuls
    SELECT
        el3,
        'ENGAGEMENT' typecumul,
        SUM(full_value) mont
    FROM oas_balance, oas_grplist imp
    WHERE
        oas_balance.cmpcode = 'AUF'
        AND imp.cmpcode = 'AUF'
        AND curcode = 'EUR'
        AND repbasis = 5
        AND balcode = 'ENGAGEMENT'
        AND el1 NOT LIKE '4%'
        AND el1 NOT LIKE '96%'
        AND el1 NOT LIKE '97%'
        AND el1 NOT LIKE '98%'
        AND el3 LIKE @like_el3
        AND el3 = imp.code
        AND imp.elmlevel = 3
        AND imp.grpcode LIKE @groupe
    GROUP BY el3
END

SELECT
    cumul.el3 projetPoste,
    e.sname nomProjet,
    e.[name] nomPoste,
    SUM(ISNULL(budget_depenses.montant,0)) budget_depense,
    SUM(ISNULL(budget_recettes.montant,0)) budget_recette,
    SUM(ISNULL(reel_recettes.montant,0)) reel_recette,
    SUM(ISNULL(reel_depenses.montant,0)) reel_depense,
    SUM(ISNULL(engages.montant,0)) engage,
    SUM(ISNULL(preengagement.montant,0)) preengagement
FROM
    (
        SELECT DISTINCT el3
        FROM #cumuls
    ) cumul
    LEFT JOIN (
        SELECT el3, typecumul, montant
        FROM #cumuls
    ) budget_depenses
        ON cumul.el3 = budget_depenses.el3
        AND budget_depenses.typecumul = 'DEP-BUDGET'
    LEFT JOIN (
        SELECT el3, typecumul, montant
        FROM #cumuls
    ) budget_recettes
        ON cumul.el3 = budget_recettes.el3
        AND budget_recettes.typecumul = 'REC-BUDGET'
    LEFT JOIN (
        SELECT el3, typecumul, montant
        FROM #cumuls
    ) reel_recettes
        ON cumul.el3 = reel_recettes.el3
        AND reel_recettes.typecumul = 'REC-REEL'
    LEFT JOIN (
        SELECT el3, typecumul, montant
        FROM #cumuls
    ) reel_depenses
        ON cumul.el3 = reel_depenses.el3
        AND reel_depenses.typecumul = 'DEP-REEL'
    LEFT JOIN (
        SELECT el3, typecumul, montant
        FROM #cumuls
    ) engages
        ON cumul.el3 = engages.el3
        AND engages.typecumul = 'ENGAGEMENT'
    LEFT JOIN (
        SELECT el3, typecumul, montant
        FROM #cumuls
    ) preengagement
        ON cumul.el3 = preengagement.el3
        AND preengagement.typecumul = 'PRENGAGEMENT'
    LEFT JOIN  oas_element e
        ON cumul.el3 = e.code
        AND e.cmpcode = 'AUF'
        AND e.elmlevel = 3
WHERE
    (@exercice < 2014 AND LEN(cumul.el3) = 7 AND LEFT(cumul.el3,2) <> '95')
    OR (@exercice >= 2014 AND LEN(cumul.el3) = 8 AND LEFT(cumul.el3,1) <> 'P')
GROUP BY cumul.el3, e.sname, e.[name]
ORDER BY cumul.el3
GO