Liste des documents actifs en fonction du type de documents, et/ou de la cible, et/ou du thème, et/ou du fournisseur, et/ou de la disponibilité, et/ou du poids unitaire, et/ou du stock, et/ou de la publication sur internet
SELECT
DOC.PRIMARY_KEY AS ID,
DOC.NAME AS NOM,
SUP.NAME AS FOURNISSEUR,
NAT.NAME AS NATURE,
THM.NAME AS THEME,
TARG.NAME AS CIBLE,
CASE
WHEN DOC.STOCK > DOC.DEFAULT_QTY THEN 'Disponible'
ELSE 'Non disponible'
END AS DISPONIBILITE,
WEIGHT AS POIDS,
DEFAULT_QTY AS QT_PAR_DEFAUT,
MAX_QTY AS QT_MAXIMUM,
STOCK AS STOCK,
CASE
WHEN ( DOC.DOC_ACTIVE = '1' AND DOC.DOC_PUBLISHED = '1' ) THEN 'Actif & Publié'
WHEN ( DOC.DOC_ACTIVE = '1' AND DOC.DOC_PUBLISHED = '0' ) THEN 'Actif'
ELSE 'Non actif'
END AS PUBLICATION,
DOC.DOC_COMMENTAIRE AS COMMENTAIRE
FROM
STATS_DAY_DOC DOC,
STATS_DAY_DOC_THEME DOC_THM,
STATS_COMMUN_THEME THM,
STATS_COMMUN_NATURE NAT,
STATS_COMMUN_TARGET TARG,
STATS_COMMUN_SUPPLIER SUP
WHERE
DOC.NATURE_ID = NAT.NATURE_ID
AND DOC.CIBLE_ID = TARG.TARGET_ID
AND DOC.SUPPLIER_ID = SUP.SUPPLIER_ID
AND DOC.DOC_ACTIVE = '1'
AND DOC_THM.DOC_ID = DOC.DOC_ID
AND DOC_THM.THEME_ID = THM.THEME_ID
AND NAT.NAME =
CASE
WHEN '##nature##' <> 'Aucun' THEN '##nature##'
ELSE NAT.NAME
END
AND TARG.NAME =
CASE
WHEN '##target##' <> 'Aucun' THEN '##target##'
ELSE TARG.NAME
END
AND THM.NAME =
CASE
WHEN '##theme##' <> 'Aucun' THEN '##theme##'
ELSE THM.NAME
END
AND TO_CHAR( WEIGHT ) =
CASE
WHEN '##weight##' IS NOT NULL THEN '##weight##'
ELSE TO_CHAR( WEIGHT )
END
AND TO_CHAR( STOCK ) =
CASE
WHEN '##stock##' IS NOT NULL THEN '##stock##'
ELSE TO_CHAR( STOCK )
END
AND DOC.DOC_PUBLISHED =
CASE
WHEN '##published##' = 'Actif' THEN 0
WHEN '##published##' = 'Actif & Publié' THEN 1
ELSE DOC.DOC_PUBLISHED
END
ORDER BY
THM.NAME, DOC.NAME
