Skip to content

Instantly share code, notes, and snippets.

@Djourdain

Djourdain/calcul_ca.SQL

Created December 22, 2021 09:05
Show Gist options
  • Save Djourdain/d759393c57a7e85936592f612b316419 to your computer and use it in GitHub Desktop.
Save Djourdain/d759393c57a7e85936592f612b316419 to your computer and use it in GitHub Desktop.
Download ZIP
calcul_ca.SQL
Raw
calcul_ca.SQL
CREATE OR REPLACE FUNCTION calcul_ca(v_dtdebut TIMESTAMP WITHOUT TIME ZONE, v_dtfin TIMESTAMP WITHOUT TIME ZONE, v_nb_jour_a_enlever INTEGER,
v_e_typestat CHARACTER VARYING, v_e_cdoper CHARACTER VARYING, v_type_select INTEGER) RETURNS VOID
LANGUAGE plpgsql
AS
$$
DECLARE
v_dtdebut_n_1 TIMESTAMP;
v_dtfin_n_1 TIMESTAMP;
v_max_dtvaleur TIMESTAMP;
v_max_dtvaleur_n_1 TIMESTAMP;
v_cdsite VARCHAR(5);
v_dtvaleur TIMESTAMP;
v_nbtickets INTEGER;
v_nbtickets_hors_presse INTEGER;
v_nb_cca INTEGER;
v_qte INTEGER;
v_qte_lib INTEGER;
v_qte_pap INTEGER;
v_mtttc NUMERIC(19, 4);
v_mtrms NUMERIC(19, 4);
v_mttva NUMERIC(19, 4);
v_mtttc_lib NUMERIC(19, 4);
v_mtrms_lib NUMERIC(19, 4);
v_mttva_lib NUMERIC(19, 4);
v_mtttc_pap NUMERIC(19, 4);
v_mtrms_pap NUMERIC(19, 4);
v_mttva_pap NUMERIC(19, 4);
v_mtttc_cca NUMERIC(19, 4);
v_mtrms_cca NUMERIC(19, 4);
v_mttva_cca NUMERIC(19, 4);
v_ca_net_ht NUMERIC(19, 4);
v_ca_net_ht_lib NUMERIC(19, 4);
v_ca_net_ht_pap NUMERIC(19, 4);
v_panier NUMERIC(19, 4);
v_ca_net_ht_cca NUMERIC(19, 4);
v_ca_net_ttc NUMERIC(19, 4);
v_ca_net_ttc_lib NUMERIC(19, 4);
v_ca_net_ttc_pap NUMERIC(19, 4);
v_ca_brut_ttc NUMERIC(19, 4);
v_ca_brut_ht NUMERIC(19, 4);
v_ca_brut_ht_lib NUMERIC(19, 4);
v_ca_brut_ht_pap NUMERIC(19, 4);
v_nbcartesach INTEGER;
v_mtcartesach NUMERIC(19, 4);
v_nbcartesrenouv INTEGER;
v_mtcartesrenouv NUMERIC(19, 4);
v_nbabovigi INTEGER;
v_pctobjjour DECIMAL(8, 6);
v_pctobjjourlib DECIMAL(8, 6);
v_pctobjjourpap DECIMAL(8, 6);
v_mtobjmois NUMERIC(19, 4);
v_anneemois VARCHAR(6);
v_verrue INTEGER;
v_date29fevrier TIMESTAMP;
v_annee INTEGER;
v_nbrecpt_lib INTEGER;
v_nbrecpt_pap INTEGER;
v_nbret_lib INTEGER;
v_mtobjlib NUMERIC(19, 4);
v_mtobjpap NUMERIC(19, 4);
v_mtinteresmax NUMERIC(19, 4);
v_mtinteresmax_lib NUMERIC(19, 4);
v_mtinteresmax_pap NUMERIC(19, 4);
v_mtttc_kdo NUMERIC(19, 4);
v_mtrms_kdo NUMERIC(19, 4);
v_mttva_kdo NUMERIC(19, 4);
v_nb_kdo INTEGER;
v_ca_net_ht_kdo NUMERIC(19, 4);
v_nb_entrees INTEGER;
v_objmaxlib DECIMAL(20, 4);
v_objmaxpap DECIMAL(20, 4);
v_ca_brut_ht_tot_jour DECIMAL(20, 4);
v_nbcartepay INTEGER;
v_txencartpay DECIMAL(5, 2);
v_nbcarteens INTEGER;
v_nbcartegrat INTEGER;
v_nbliseuse INTEGER;
v_cadesign NUMERIC(19, 4);
v_nbtcksscarte INTEGER;
v_nbtcksscarte_ann INTEGER;
v_nbcartepay_ann INTEGER;
v_dteb_boucle TIMESTAMP;
v_dtfin_boucle TIMESTAMP;
v_dteb_boucle_n1 TIMESTAMP;
v_dtfin_boucle_n1 TIMESTAMP;
c_list_site CURSOR FOR
SELECT DISTINCT site.cdsite
FROM site
WHERE
coalesce(cdenvinfocai, 0) = 1;
BEGIN
DELETE
FROM tempdb.resultat_ca
WHERE
cdoper = v_e_cdoper;
DELETE
FROM tempdb.calcul_ca_n
WHERE
cdoper = v_e_cdoper;
DELETE
FROM tempdb.calcul_ca_n_1
WHERE
cdoper = v_e_cdoper;
-- On r?cup?re la date max dans resultat_ca_jour
-- pour voir s'il faut r?cup?rer les donn?es dans histo_ventes
v_verrue := 0;
v_max_dtvaleur := LOCALTIMESTAMP;
-- si date de fin < date du jour on ne recherche que des données historisée
IF v_dtfin < current_date
THEN
v_max_dtvaleur := v_dtfin;
ELSE
IF v_e_typestat <> 'H'
THEN
v_max_dtvaleur := cast(to_char(current_date, 'YYYYMMDD') || ' 23:59:59' AS TIMESTAMP) ;
END IF;
END IF;
IF v_nb_jour_a_enlever = 0
THEN
v_dtdebut_n_1 := v_dtdebut - INTERVAL '1 year';
v_dtfin_n_1 := v_dtfin - INTERVAL '1 year';
v_max_dtvaleur_n_1 := v_max_dtvaleur - INTERVAL '1 year';
ELSE
v_dtdebut_n_1 := get_date_annee_precedente(v_dtdebut::DATE);
v_dtfin_n_1 := get_date_annee_precedente(v_dtfin::DATE);
v_max_dtvaleur_n_1 := get_date_annee_precedente(v_max_dtvaleur::DATE);
END IF;
v_annee := EXTRACT(YEAR FROM v_dtdebut);
-- on traite les journée avt la date J
IF v_dtdebut < current_date
THEN -- on ne traite pas la date du jour
v_dtfin_boucle := v_max_dtvaleur;
v_dtfin_boucle_n1 := v_max_dtvaleur_n_1;
IF v_dtfin >= current_date
THEN
v_dtfin_boucle := v_max_dtvaleur + INTERVAL '-1 day';
v_dtfin_boucle_n1 := v_max_dtvaleur_n_1 + INTERVAL '-1 day';
END IF;
PERFORM calcul_ca_insert_calculca_jour_nonj(v_dtdebut, v_dtfin_boucle, v_e_cdoper, 'N');
PERFORM calcul_ca_insert_calculca_jour_nonj(v_dtdebut_n_1, v_dtfin_boucle_n1, v_e_cdoper, 'N1');
END IF;
-- traitement date du jour
IF v_dtfin >= current_date
THEN
-- donnees horaires
IF v_e_typestat = 'H'
THEN
v_dteb_boucle := current_date;
v_dtfin_boucle := v_max_dtvaleur;
v_dteb_boucle_n1 := date_trunc('day', v_max_dtvaleur_n_1);
v_dtfin_boucle_n1 := v_max_dtvaleur_n_1 + now()::time;
ELSE
v_dteb_boucle := current_date;
v_dtfin_boucle := v_max_dtvaleur + INTERVAL '1 day';
v_dteb_boucle_n1 := date_trunc('day', v_max_dtvaleur_n_1);
v_dtfin_boucle_n1 := v_max_dtvaleur_n_1 + INTERVAL '1 day';
END IF;
OPEN c_list_site;
FETCH c_list_site INTO v_cdsite;
WHILE (found)
LOOP
PERFORM calcul_ca_insert_calculc_jourj(v_cdsite, v_dteb_boucle, v_dtfin_boucle, v_e_cdoper, 'N', v_type_select);
PERFORM calcul_ca_insert_calculc_jourj(v_cdsite, v_dteb_boucle_n1, v_dtfin_boucle_n1, v_e_cdoper, 'N1', v_type_select);
FETCH c_list_site INTO v_cdsite;
END LOOP;
CLOSE c_list_site;
END IF; -- fin traitement date du jour
INSERT INTO
tempdb.resultat_ca(cdsite, jour, dtvaleur, jour_n_1, dtvaleur_n_1, ca_net_ht_n, ca_net_ht_n_1, panier_n, panier_n_1, nb_ticket_n,
nb_ticket_n_1, nb_chq_cad_n, nb_chq_cad_n_1, mt_net_ht_cca_n, ca_net_ht_cca_n_1, ca_net_ttc_n, ca_net_ttc_n_1, ca_brut_ttc_n,
ca_brut_ttc_n_1, ca_brut_ht_n, ca_brut_ht_n_1, cdoper, qte_n, qte_n_1, nbcartesach, nbcartesach_n_1, mtcartesach,
mtcartesach_n_1, nbcartesrenouv, nbcartesrenouv_n_1, mtcartesrenouv, mtcartesrenouv_n_1, nbvigiab, nbvigiab_n_1, mtobjjour,
mtinteresmax, ca_brut_ht_lib, ca_brut_ht_lib_n_1, mtobjjour_lib, mtinteresmax_lib, ca_brut_ht_pap, ca_brut_ht_pap_n_1,
mtobjjour_pap,
mtinteresmax_pap, qte_lib, qte_lib_n_1, qte_recpt_lib, qte_recpt_lib_n_1, qte_ret_lib, qte_ret_lib_n_1, qte_pap,
qte_pap_n_1, qte_recpt_pap, qte_recpt_pap_n_1, nb_tickets_hors_presse, nb_tickets_hors_presse_n_1, nb_car_kdo, nb_car_kdo_n_1,
ca_net_ht_kdo, ca_net_ht_kdo_n_1, nb_entrees, nb_entrees_n_1, nbcartepay, nbcartepay_n_1, txencartpay, txencartpay_n_1,
nbcarteens, nbcarteens_n_1, nbcartegrat, nbcartegrat_n_1, nbliseuse, nbliseuse_n_1, cadesign, cadesign_n_1,
nbtcksscarte, nbtcksscarte_n_1)
SELECT
ca_n.cdsite,
ca_n.jour AS jour,
ca_n.dtvaleur AS dtvaleur,
ca_n1.jour AS jour_n_1,
ca_n1.dtvaleur AS dtvaleur_n_1,
ca_n.ca_net_ht AS ca_net_ht_n,
ca_n1.ca_net_ht AS ca_net_ht_n_1,
ca_n.panier AS panier_n,
ca_n1.panier AS panier_n_1,
ca_n.nb_tickets AS nb_ticket_n,
ca_n1.nb_tickets AS nb_ticket_n_1,
ca_n.nb_chq_cad AS nb_chq_cad_n,
ca_n1.nb_chq_cad AS nb_chq_cad_n_1,
ca_n.ca_net_ht_cca AS mt_net_ht_cca_n,
ca_n1.ca_net_ht_cca AS ca_net_ht_cca_n_1,
ca_n.ca_net_ttc AS ca_net_ttc_n,
ca_n1.ca_net_ttc AS ca_net_ttc_n_1,
ca_n.ca_brut_ttc AS ca_brut_ttc_n,
ca_n1.ca_brut_ttc AS ca_brut_ttc_n_1,
ca_n.ca_brut_ht AS ca_brut_ht_n,
ca_n1.ca_brut_ht AS ca_brut_ht_n_1,
v_e_cdoper,
ca_n.qte AS qte_n,
ca_n1.qte AS qte_n_1,
ca_n.nbcartesach AS nb_cartes_ach,
ca_n1.nbcartesach AS nb_cartes_ach_n_1,
ca_n.mtcartesach AS mt_cartes_ach,
ca_n1.mtcartesach AS mt_cartes_ach_n_1,
ca_n.nbcartesrenouv AS nb_cartes_renouv,
ca_n1.nbcartesrenouv AS nb_cartes_renouv_n_1,
ca_n.mtcartesrenouv AS mt_cartes_renouv,
ca_n1.mtcartesrenouv AS mt_cartes_renouv_n_1,
ca_n.nbvigiab AS nbvigiab,
ca_n1.nbvigiab AS nbvigiab_n_1,
CAST(ca_n.ca_brut_ht_tot_jour AS NUMERIC(19, 4)) AS mtobjjour,
coalesce(ca_n.mtinteresmax, cast(0 AS NUMERIC(19, 4))) AS mtinteresmax,
ca_n.ca_brut_ht_lib AS ca_brut_ht_lib,
ca_n1.ca_brut_ht_lib AS ca_brut_ht_lib_n_1,
CAST(coalesce(coalesce(ca_n.pctobjlib, ca_n.pctobjca), 0) * coalesce(ca_n.mtobjlib, cast(0 AS NUMERIC(19, 4))) AS NUMERIC(19, 4)) AS mtobjjour_lib,
CAST(ca_n.objmaxlib AS NUMERIC(19, 4)) AS mtinteresmax_lib,
ca_n.ca_brut_ht_pap AS ca_brut_ht_pap,
ca_n1.ca_brut_ht_pap AS ca_brut_ht_pap_n_1,
CAST(coalesce(coalesce(ca_n.pctobjpap, ca_n.pctobjca), 0) * coalesce(ca_n.mtobjpap, cast(0 AS NUMERIC(19, 4))) AS NUMERIC(19, 4)) AS mtobjjour_pap,
CAST(ca_n.objmaxpap AS NUMERIC(19, 4)) AS mtinteresmax_pap,
ca_n.qte_lib AS qte_lib,
ca_n1.qte_lib AS qte_lib_n_1,
ca_n.nbrecpt_lib AS qte_recpt_lib,
ca_n1.nbrecpt_lib AS qte_recpt_lib_n_1,
ca_n.nbret_lib AS qte_ret_lib,
ca_n1.nbret_lib AS qte_ret_lib_n_1,
ca_n.qte_pap AS qte_pap,
ca_n1.qte_pap AS qte_pap_n_1,
ca_n.nbrecpt_pap AS qte_recpt_pap,
ca_n1.nbrecpt_pap AS qte_recpt_pap_n_1,
CASE
WHEN coalesce(ca_n.nb_tickets_hors_presse, 0) > 0
THEN ca_n.nb_tickets_hors_presse
ELSE 1
END AS nb_tickets_hors_presse,
ca_n1.nb_tickets_hors_presse AS nb_tickets_hors_presse_n_1,
ca_n.nb_car_kdo AS nb_car_kdo,
ca_n1.nb_car_kdo AS nb_car_kdo_n_1,
ca_n.ca_net_ht_kdo AS ca_net_ht_kdo,
ca_n1.ca_net_ht_kdo AS ca_net_ht_kdo_n_1,
ca_n.nb_entrees AS nb_entrees,
ca_n1.nb_entrees AS nb_entrees_n_1,
ca_n.nbcartepay AS nbcartepay,
ca_n1.nbcartepay AS nbcartepay_n_1,
ca_n.txencartpay AS txencartpay,
ca_n1.txencartpay AS txencartpay_n_1,
ca_n.nbcarteens AS nbcarteens,
ca_n1.nbcarteens AS nbcarteens_n_1,
ca_n.nbcartegrat AS nbcartegrat,
ca_n1.nbcartegrat AS nbcartegrat_n_1,
ca_n.nbliseuse AS nbliseuse,
ca_n1.nbliseuse AS nbliseuse_n_1,
ca_n.cadesign AS cadesign,
CAST(ca_n1.cadesign AS INTEGER) AS cadesign_n_1,
ca_n.nbtcksscarte AS nbtcksscarte,
ca_n1.nbtcksscarte AS nbtcksscarte_n_1
FROM
tempdb.calcul_ca_n ca_n
LEFT JOIN tempdb.calcul_ca_n_1 ca_n1
ON ca_n.cdsite = ca_n1.cdsite
AND ca_n.cdoper = ca_n1.cdoper
AND ((ca_n1.dtvaleur = get_date_annee_precedente(ca_n.dtvaleur::DATE) AND v_nb_jour_a_enlever > 0)
OR (ca_n1.dtvaleur = (ca_n.dtvaleur - INTERVAL '1 year')) AND v_nb_jour_a_enlever = 0)
WHERE
ca_n.cdoper = v_e_cdoper;
RAISE NOTICE 'avt delete';
DELETE
FROM tempdb.resultat_ca
WHERE
cdoper = v_e_cdoper
AND cdsite = 'JUN';
RAISE NOTICE 'avt update ';
UPDATE tempdb.resultat_ca
SET
mtinteresmax = coalesce(mtinteresmax_lib, cast(0 AS NUMERIC(19, 4))) + coalesce(mtinteresmax_pap, cast(0 AS NUMERIC(19, 4)))
WHERE
cdoper = v_e_cdoper;
UPDATE tempdb.resultat_ca
SET
mtobjjour = NULL
WHERE
mtobjjour = 0
AND cdoper = v_e_cdoper;
UPDATE tempdb.resultat_ca
SET
mtobjjour_lib = NULL
WHERE
mtobjjour_lib = 0
AND cdoper = v_e_cdoper;
UPDATE tempdb.resultat_ca
SET
mtobjjour_pap = NULL
WHERE
mtobjjour_pap = 0
AND cdoper = v_e_cdoper;
UPDATE tempdb.resultat_ca
SET
mtinteresmax_lib = NULL
WHERE
mtinteresmax_lib = 0
AND cdoper = v_e_cdoper;
UPDATE tempdb.resultat_ca
SET
mtinteresmax_pap = NULL
WHERE
mtinteresmax_pap = 0
AND cdoper = v_e_cdoper;
UPDATE tempdb.resultat_ca
SET
mtinteresmax = NULL
WHERE
mtinteresmax = 0
AND cdoper = v_e_cdoper;
END;
$$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment