Skip to content

Instantly share code, notes, and snippets.

@Djourdain

Djourdain/calcul_ca_insert_calculc_jourj.sql

Created December 22, 2021 09:09
Show Gist options
  • Save Djourdain/f06d060e0181672de21d38083919ad7b to your computer and use it in GitHub Desktop.
Save Djourdain/f06d060e0181672de21d38083919ad7b to your computer and use it in GitHub Desktop.
Download ZIP
calcul_ca_insert_calculc_jourj.sql
Raw
calcul_ca_insert_calculc_jourj.sql
SELECT f_drop_function('calcul_ca_insert_calculc_jourj');
CREATE FUNCTION calcul_ca_insert_calculc_jourj(
v_cdsite CHARACTER VARYING,
v_dtdeb TIMESTAMP WITHOUT TIME ZONE,
v_dtfin TIMESTAMP WITHOUT TIME ZONE,
v_e_cdoper CHARACTER VARYING,
v_e_type CHARACTER VARYING,
v_e_rech INTEGER)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
v_dtdebut_n_1 TIMESTAMP;
v_dtfin_n_1 TIMESTAMP;
v_max_dtvaleur_n_1 TIMESTAMP;
v_dtvaleur TIMESTAMP;
v_nbtickets INTEGER;
v_nbtickets_hors_presse INTEGER;
v_nbtickets_hors_salon 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;
BEGIN
v_ca_brut_HT_lib := CAST(0 AS NUMERIC(19,4));
v_ca_net_TTC_lib := CAST(0 AS NUMERIC(19,4));
v_ca_brut_HT_pap := CAST(0 AS NUMERIC(19,4));
v_ca_net_TTC_pap := CAST(0 AS NUMERIC(19,4));
v_ca_net_HT_lib := CAST(0 AS NUMERIC(19,4));
v_mtttc_lib := CAST(0 AS NUMERIC(19,4));
v_mtrms_lib := CAST(0 AS NUMERIC(19,4));
v_mttva_lib := CAST(0 AS NUMERIC(19,4));
v_ca_net_HT_pap := CAST(0 AS NUMERIC(19,4));
v_mtttc_pap := CAST(0 AS NUMERIC(19,4));
v_mtrms_pap := CAST(0 AS NUMERIC(19,4));
v_mttva_pap := CAST(0 AS NUMERIC(19,4));
v_nbtickets := 0;
v_nbtickets_hors_presse := 0;
v_nbtickets_hors_salon :=0;
v_panier := CAST(0 AS NUMERIC(19,4));
v_nb_entrees := 0;
v_mtrms := CAST(0 AS NUMERIC(19,4));
v_mtttc := CAST(0 AS NUMERIC(19,4));
v_mttva := CAST(0 AS NUMERIC(19,4));
v_ca_net_HT := CAST(0 AS NUMERIC(19,4));
v_qte_lib := 0;
v_qte_pap := 0;
v_qte := 0;
v_mtobjmois := CAST(0 AS NUMERIC(19,4));
v_mtobjlib := CAST(0 AS NUMERIC(19,4));
v_mtobjpap := CAST(0 AS NUMERIC(19,4));
v_mtinteresmax := CAST(0 AS NUMERIC(19,4));
v_mtinteresmax_lib := CAST(0 AS NUMERIC(19,4));
v_mtinteresmax_pap := CAST(0 AS NUMERIC(19,4));
v_anneemois := '';
v_pctobjjour := 0;
v_pctobjjourlib := 0;
v_pctobjjourpap := 0;
v_objmaxlib := 0;
v_objmaxpap := 0;
v_ca_brut_HT_tot_jour := 0;
-- 1.1) montants TOT (hors presse)
select
coalesce(sum((Case When hv.cdfam in('750','751','752','753','754','624') Then(mtttc -pmp) Else mtttc End)
*(case cdtypvente when '04' then 0 else cdcalculqtevendue end)),0),
coalesce(sum((mtrms*(case cdtypvente when '04' then 0 else cdcalculqtevendue end))),cast(0 as NUMERIC(19,4))),
coalesce(sum((mttva*(case cdtypvente when '04' then 0 else cdcalculqtevendue end))),cast(0 as NUMERIC(19,4))),
coalesce(sum((mtttc*(case when cdtypvente = '04' and SUBSTR(nummoypai,1,2) = '04' then cdcalculqtevendue
else 0 end))),cast(0 as NUMERIC(19,4))),
coalesce(sum((mtrms*(case when cdtypvente = '04' and SUBSTR(nummoypai,1,2) = '04' then cdcalculqtevendue
else 0 end))),cast(0 as NUMERIC(19,4))),
coalesce(sum((mttva*(case when cdtypvente = '04' and SUBSTR(nummoypai,1,2) = '04' then cdcalculqtevendue
else 0 end))),cast(0 as NUMERIC(19,4))),
coalesce(sum((mtttc*(case when cdtypvente = '04' and SUBSTR(nummoypai,1,2) = '24' then cdcalculqtevendue
else 0 end))),cast(0 as NUMERIC(19,4))),
coalesce(sum((mtrms*(case when cdtypvente = '04' and SUBSTR(nummoypai,1,2) = '24' then cdcalculqtevendue
else 0 end))),cast(0 as NUMERIC(19,4))),
coalesce(sum((mttva*(case when cdtypvente = '04' and SUBSTR(nummoypai,1,2) = '24' then cdcalculqtevendue
else 0 end))),cast(0 as NUMERIC(19,4))),
coalesce(sum(((Case When hv.cdfam in('750','751','752','753','754','624') Then(mtttc -pmp) Else mtttc End)
*(case cdtypvente when '04' then 0 else (case when f.cdtypfam = '1' then cdcalculqtevendue else 0 end) end))), cast(0 as NUMERIC(19,4))),
coalesce(sum((mtrms*(case cdtypvente when '04' then 0 else (case when f.cdtypfam = '1' then cdcalculqtevendue else 0 end) end))), cast(0 as NUMERIC(19,4))),
coalesce(sum((mttva*(case cdtypvente when '04' then 0 else (case when f.cdtypfam = '1' then cdcalculqtevendue else 0 end) end))), cast(0 as NUMERIC(19,4))),
coalesce(sum(((Case When hv.cdfam in('750','751','752','753','754','624') Then(mtttc -pmp) Else mtttc End)
*(case cdtypvente when '04' then 0 else (case when f.cdtypfam = '2' then cdcalculqtevendue else 0 end) end))), cast(0 as NUMERIC(19,4))),
coalesce(sum((mtrms*(case cdtypvente when '04' then 0 else (case when f.cdtypfam = '2' then cdcalculqtevendue else 0 end) end))), cast(0 as NUMERIC(19,4))),
coalesce(sum((mttva*(case cdtypvente when '04' then 0 else (case when f.cdtypfam = '2' then cdcalculqtevendue else 0 end) end))), cast(0 as NUMERIC(19,4))),
coalesce(sum(qtevendu * (case cdtypvente when '04' then 0 else (case when f.cdtypfam = '1' then cdcalculqtevendue else 0 end) end ) ), cast(0 as integer)),
coalesce(sum(qtevendu * (case cdtypvente when '04' then 0 else (case when f.cdtypfam = '2' then cdcalculqtevendue else 0 end) end ) ), cast(0 as integer)),
coalesce(sum(qtevendu * (case cdtypvente when '04' then 0 else cdcalculqtevendue end ) ), cast(0 as integer))
INTO v_mtttc,v_mtrms,v_mttva,
v_mtttc_cca,v_mtrms_cca,v_mttva_cca,
v_mtttc_kdo,v_mtrms_kdo,v_mttva_kdo,
v_mtttc_lib,v_mtrms_lib,v_mttva_lib,
v_mtttc_pap,v_mtrms_pap,v_mttva_pap,
v_qte_lib,v_qte_pap,v_qte
from histo_ventes hv
inner join type_mvt tm on tm.typmvt = hv.typmvt
left join famille f on f.cdfam = hv.cdfam
where hv.cdsite = v_cdsite
and hv.dtvaleur >= v_dtdeb
and hv.dtvaleur < v_dtfin
and hv.typmvt in('01','03','08','04','02','12')
and cdtypvente <> '06'
and coalesce(rtrim(hv.cdfam),'') not in('191','192','900')
and exists(select distinct 1 from site where cdsite = hv.cdsite);
v_ca_net_HT_lib := coalesce(v_mtttc_lib -v_mtrms_lib -v_mttva_lib,0);
v_ca_net_TTC_lib := coalesce(v_mtttc_lib -v_mtrms_lib,0);
v_ca_net_TTC_pap := coalesce(v_mtttc_pap -v_mtrms_pap,0);
v_ca_net_HT_pap := coalesce(v_mtttc_pap -v_mtrms_pap -v_mttva_pap,0);
-- 2) nombre de tickets
-- 2.1) nb de tickets, presse incluse
v_nbtickets := 0;
select count(distinct cdreforigmvt) INTO v_nbtickets
from histo_ventes hv
where hv.cdsite = v_cdsite
and hv.dtvaleur >= v_dtdeb
and hv.dtvaleur < v_dtfin
and typmvt in('01','08','03')
and cdtypvente not in('04','06')
and coalesce(rtrim(cdfam),'') <> '900';
select coalesce(v_nbtickets,0) - count(distinct cdreforigmvt)
INTO v_nbtickets
from histo_ventes hv
where hv.cdsite = v_cdsite
and hv.dtvaleur >= v_dtdeb
and hv.dtvaleur < v_dtfin
and typmvt in('04','02','12')
and cdtypvente not in('04','06')
and coalesce(rtrim(cdfam),'') <> '900';
v_nbtickets_hors_salon :=
COALESCE(v_nbtickets,0) - COALESCE((SELECT COUNT(distinct cdreforigmvt)
FROM histo_ventes hv
INNER JOIN caisse c
ON c.numero = LEFT(hv.cdreforigmvt,2)
AND hv.dtvaleur BETWEEN dtDebSalon AND dtFinSalon
WHERE hv.cdsite = v_cdsite
AND hv.dtvaleur >= v_dtdeb
AND hv.dtvaleur < v_dtfin
AND typmvt IN('04','02','12','01','08','03')
AND cdtypvente NOT IN('04','06')
AND COALESCE(RTRIM(cdfam),'') <> '900'),0);
RAISE NOTICE 'v_cdsite : % nb tickets : %, hS : %',v_cdsite, v_nbtickets, v_nbtickets_hors_salon;
-- 2.2) nb de tickets, hors presse
v_nbtickets_hors_presse := 0;
select count(distinct cdreforigmvt)
INTO v_nbtickets_hors_presse
from histo_ventes hv
where hv.cdsite = v_cdsite
and hv.dtvaleur >= v_dtdeb
and hv.dtvaleur < v_dtfin
and typmvt in('01','08','03')
and cdtypvente not in('04','06')
and coalesce(rtrim(cdfam),'') not in('191','192','900');
select coalesce(v_nbtickets_hors_presse,0) - count(distinct cdreforigmvt)
INTO v_nbtickets_hors_presse
from histo_ventes hv
where hv.cdsite = v_cdsite
and hv.dtvaleur >= v_dtdeb
and hv.dtvaleur < v_dtfin
and typmvt in('04','02','12')
and cdtypvente not in('04','06')
and coalesce(rtrim(cdfam),'') not in('191','192','900');
if v_e_rech = 1 then -- recherche complète
-- 3) nombre de cheques cadeau / cartes cadeau
-- 3.1) ch?ques cadeau
v_nb_cca := 0; -- n'existe plus
-- 3.2) cartes cadeau
v_nb_kdo := null;
select count(*)
INTO v_nb_kdo
from histo_ventes hv
where hv.cdsite = v_cdsite
and hv.dtvaleur >= v_dtdeb
and hv.dtvaleur < v_dtfin
and typmvt = '01'
and cdtypvente = '04'
and SUBSTR(nummoypai,1,2) = '24';
select coalesce(v_nb_kdo,0) - count(*)
INTO v_nb_kdo
from histo_ventes hv
where hv.cdsite = v_cdsite
and hv.dtvaleur >= v_dtdeb
and hv.dtvaleur < v_dtfin
and typmvt = '04'
and cdtypvente = '04'
and SUBSTR(nummoypai,1,2) = '24';
-- 5) nombre de cartes achet?es / renouvel?es et montants associ?s
v_nbcartesach := null;
v_mtcartesach := null;
v_nbcartesrenouv := null;
v_mtcartesrenouv := null;
-- 5.1) Carte payante.
v_nbcartepay := Null;
v_nbcartepay_ann := Null;
-- Nombre de carte payante emis.
select coalesce(sum(qtevendu * (case when hv.typmvt = '01' then 1
when hv.typmvt = '01' then -1
else 0
end )),0)
INTO v_nbcartepay
From histo_ventes hv
Where hv.cdsite = v_cdsite
and hv.dtvaleur >= v_dtdeb
and hv.dtvaleur < v_dtfin
and hv.typmvt in ('01','04')
and hv.cdtypvente = '03'
and coalesce(rtrim(hv.numcartereducach),'') <> ''
And coalesce(mtttc,cast(0 as NUMERIC(19,4))) > 0;
-- Nombre de ticket sans carte ou avec emission.
v_nbtcksscarte := 0;
v_nbtcksscarte_ann := 0;
select Count(Distinct(cdreforigmvt))
INTO v_nbtcksscarte
From histo_ventes hv
Where hv.cdsite = v_cdsite
and hv.dtvaleur >= v_dtdeb
and hv.dtvaleur < v_dtfin
and typmvt in('01','08')
and coalesce(rtrim(hv.numcarte),'') = '';
select Count(Distinct(cdreforigmvt))
INTO v_nbtcksscarte_ann
From histo_ventes hv
Where hv.cdsite = v_cdsite
and hv.dtvaleur >= v_dtdeb
and hv.dtvaleur < v_dtfin
and typmvt in('04')
and coalesce(rtrim(hv.numcarte),'') = '';
v_nbtcksscarte := coalesce(v_nbtcksscarte,0) -coalesce(v_nbtcksscarte_ann,0);
-- Taux d'encartage carte payante.
v_txencartpay := 0;
If(v_nbcartepay::bigint+v_nbtcksscarte::bigint) > 0 then
v_txencartpay := Round(CAST((v_nbcartepay::bigint*100.00)/(v_nbcartepay::bigint+v_nbtcksscarte::bigint) AS NUMERIC),2);
end if;
-- 5.2) Carte gratuite.
v_nbcartegrat := Null;
select coalesce(sum(qtevendu * (case when hv.typmvt = '01' then 1
when hv.typmvt = '01' then -1
else 0
end )),0)
INTO v_nbcartegrat
From histo_ventes hv
Where hv.cdsite = v_cdsite
and hv.dtvaleur >= v_dtdeb
and hv.dtvaleur < v_dtfin
and hv.typmvt in( '01','04')
and hv.cdtypvente = '03'
and coalesce(rtrim(hv.numcartereducach),'') <> ''
And (hv.numcartereducach Like '029%' OR hv.numcartereducach Like '027%')
And coalesce(mtttc,cast(0 as NUMERIC(19,4))) = 0
And Not Exists(Select 1 From carte_reduc Where nvnumcarte = hv.numcarte);
-- 5.3) Carte enseignante.
v_nbcarteens := Null;
select coalesce(sum(qtevendu * (case when hv.typmvt = '01' then 1
when hv.typmvt = '01' then -1
else 0
end )),0)
INTO v_nbcarteens
From histo_ventes hv
Where hv.cdsite = v_cdsite
and hv.dtvaleur >= v_dtdeb
and hv.dtvaleur < v_dtfin
and hv.typmvt in( '01','04')
and hv.cdtypvente = '03'
and coalesce(rtrim(hv.numcartereducach),'') <> ''
And hv.numcartereducach Like '028%';
-- 6) Liseuses & ca design.
v_cadesign := CAST(0 AS NUMERIC(19,4));
v_nbliseuse := 0;
-- 6.1) CA Design
select coalesce(sum((
(coalesce(mtttc,0::NUMERIC(19,4)) - coalesce(mtrms,0::NUMERIC(19,4)) - coalesce(mttva,0::NUMERIC(19,4)))
+ ((coalesce(mtttc,0::NUMERIC(19,4)) - coalesce(mtrms,0::NUMERIC(19,4)) - coalesce(mttva,0::NUMERIC(19,4)))
/ (coalesce(mtttc,cast(0 as NUMERIC(19,4)) - coalesce(mtrms,0::NUMERIC(19,4)) * coalesce(mtrms,0::NUMERIC(19,4))))))
* (case cdtypvente when '04' then 0
else(case typmvt
when '01' then 1
when '08' then 1
when '03' then 1
when '04' then -1
when '02' then -1
when '12' then -1
end)
end)),
0::NUMERIC(19,4))
INTO v_cadesign
from histo_ventes hv
where hv.cdsite = v_cdsite
and hv.dtvaleur >= v_dtdeb
and hv.dtvaleur < v_dtfin
and typmvt in('01','03','08','04','02','12')
and cdtypvente <> '06'
And cdfam In('745','746','747','748','749','623','625','646','641')
and exists(select distinct 1 from site where cdsite = hv.cdsite) ;
-- 6.2) Liseuse.
select coalesce(sum(qtevendu::bigint*(case typmvt when '01' then 1
when '08' then 1
when '03' then 1
when '12' then -1
when '04' then -1
when '02' then -1
end)),0)
INTO v_nbliseuse
from histo_ventes hv
where hv.cdsite = v_cdsite
and hv.dtvaleur >= v_dtdeb
and hv.dtvaleur < v_dtfin
and typmvt in('01','08','03','04','02','12')
and cdtypvente <> '06'
and cdfam in ('757', '640')
and exists(select distinct 1 from site where cdsite = hv.cdsite);
-- 7) Qt? r?ceptionn?e
-- 7.1) qt? recept LIB
v_nbrecpt_lib := 0;
v_nbrecpt_pap := 0;
select sum(ms.qtemvtsto * (case when (SUBSTR(ms.cdentorigmvt,1,1) = '7' and SUBSTR(ms.cdentorigmvt,7,1) = '7') then 1 else 0 end )),
sum(ms.qtemvtsto * (case when (SUBSTR(ms.cdentorigmvt,1,1) <> '7' or SUBSTR(ms.cdentorigmvt,7,1) <> '7') then 1 else 0 end ))
INTO v_nbrecpt_pap,v_nbrecpt_lib
from mvt_stock ms
where ms.cdsite = v_cdsite
and ms.dtvaleur >= v_dtdeb
and ms.dtvaleur < v_dtfin
and ms.cdmvtsto = '01';
-- 8) Qte retour LIB
v_nbret_lib := 0;
select sum(ms.qtemvtsto)
INTO v_nbret_lib
from mvt_stock ms, prepa_retour pr
where ms.cdsite = v_cdsite
and ms.dtvaleur >= v_dtdeb
and ms.dtvaleur < v_dtfin
and ms.cdmvtsto = '22'
and CAST(ms.cdreforigmvt AS INTEGER) = pr.numpreparet
and not (SUBSTR(pr.cdfour,1,1) = '7' and SUBSTR(pr.cdfour,length(pr.cdfour) -1+1) = '7');
end if; -- fin recherche complete
v_ca_net_HT := coalesce(v_mtttc -v_mtrms -v_mttva,0);
v_ca_net_TTC := coalesce(v_mtttc -v_mtrms,0);
v_ca_brut_HT := v_ca_net_HT;
--raise notice ', v_ca_net_HT % , v_ca_net_TTC % , v_mtrms %', v_ca_net_HT, v_ca_net_TTC,v_mtrms;
if v_ca_net_TTC > 0 then
v_ca_brut_HT := v_ca_net_HT + coalesce((v_ca_net_HT/coalesce(v_ca_net_TTC,cast(1 as NUMERIC(19,4))))*coalesce(v_mtrms,cast(0 as NUMERIC(19,4))),0);
end if;
v_ca_brut_HT_lib := v_ca_net_HT_lib;
if v_ca_net_HT_lib > 0 then
v_ca_brut_HT_lib := v_ca_net_HT_lib+coalesce((v_ca_net_HT_lib/coalesce(v_ca_net_TTC_lib,cast(1 as NUMERIC(19,4))))*coalesce(v_mtrms_lib,cast(0 as NUMERIC(19,4))),0);
end if;
v_ca_brut_HT_pap := v_ca_net_HT_pap;
IF v_ca_net_HT_pap > 0 then
v_ca_brut_HT_pap := v_ca_net_HT_pap+coalesce((v_ca_net_HT_pap/coalesce(v_ca_net_TTC_pap,cast(1 as NUMERIC(19,4))))*coalesce(v_mtrms_pap,cast(0 as NUMERIC(19,4))),0);
end if;
if v_nbtickets_hors_presse > 0 then
v_panier := coalesce(v_ca_net_HT/v_nbtickets_hors_presse,0);
end if;
v_ca_net_HT_kdo := coalesce(coalesce(v_mtttc_kdo,0) - coalesce(v_mtrms_kdo,0) - coalesce(v_mttva_kdo,0),0);
select sum(em.nbentrees)
INTO v_nb_entrees
from entrees_magasin em
where em.cdsite = v_cdsite
and em.dtdebut >= v_dtdeb
and em.dtfin < (case when date(v_dtfin) = date(current_date)
then current_date + interval '1 day'
else v_dtfin end);
v_nb_entrees := coalesce(v_nb_entrees,0);
v_anneemois := coalesce(COALESCE(TO_CHAR(v_dtfin,'YYYY'),'') || lpad(cast(EXTRACT(month FROM v_dtfin) as text),2,'0'),'');
select mtobjca, mtobjlib, mtobjpap, mtinteressementmax, mtinteressementmaxlib, mtinteressementmaxpap
INTO v_mtobjmois,v_mtobjlib,v_mtobjpap,v_mtinteresmax,v_mtinteresmax_lib,v_mtinteresmax_pap
from resultat_ca_mt_obj_mois
where cdsite = v_cdsite
and anneemois = v_anneemois;
select pctobjca, pctobjlib, pctobjpap, objmaxlib, objmaxpap, caobjtot
INTO v_pctobjjour,v_pctobjjourlib,v_pctobjjourpap,v_objmaxlib,v_objmaxpap,v_ca_brut_HT_tot_jour
from resultat_ca_pct_obj_jour
where cdsite = v_cdsite
and dtvaleur = v_dtdeb;
if v_e_type = 'N' then
insert INTO tempdb.calcul_ca_n(
cdoper, cdsite, dtvaleur, jour, ca_net_HT, panier, nb_tickets, nb_chq_cad, ca_net_HT_cca,
ca_net_TTC, ca_brut_TTC, ca_brut_HT, ca_brut_HT_lib, ca_brut_HT_pap, qte, qte_lib, qte_pap,
nbcartesach, mtcartesach, nbcartesrenouv, mtcartesrenouv, nbvigiab, pctobjca, pctobjlib, pctobjpap,
mtobjmois, mtobjlib, mtobjpap, mtinteresmax, mtinteresmax_lib , mtinteresmax_pap , nbrecpt_lib, nbrecpt_pap,
nbret_lib, nb_tickets_hors_presse, nb_car_kdo, ca_net_HT_kdo, nb_entrees, objmaxlib, objmaxpap, ca_brut_HT_tot_jour,
nbcartepay, txencartpay, nbcarteens, nbcartegrat, nbliseuse, cadesign, nbtcksscarte, nb_tickets_hors_salon)
values(
v_E_cdoper, v_cdsite, v_dtdeb, TO_CHAR(v_dtdeb, 'Day'),v_ca_net_HT,v_panier,v_nbtickets,coalesce(v_nb_cca, 0),coalesce(v_ca_net_HT_cca,0),
v_ca_net_TTC, v_mtttc, v_ca_brut_HT, v_ca_brut_HT_lib, v_ca_brut_HT_pap, v_qte,
v_qte_lib, v_qte_pap, v_nbcartesach, v_mtcartesach, v_nbcartesrenouv, v_mtcartesrenouv, v_nbabovigi,
v_pctobjjour, v_pctobjjourlib, v_pctobjjourpap, v_mtobjmois, v_mtobjlib, v_mtobjpap, v_mtinteresmax,
v_mtinteresmax_lib , v_mtinteresmax_pap , v_nbrecpt_lib, v_nbrecpt_pap, v_nbret_lib, v_nbtickets_hors_presse,
v_nb_kdo, v_ca_net_HT_kdo, v_nb_entrees, v_objmaxlib, v_objmaxpap, v_ca_brut_HT_tot_jour, v_nbcartepay,
v_txencartpay, v_nbcarteens, v_nbcartegrat, v_nbliseuse, v_cadesign, v_nbtcksscarte, v_nbtickets_hors_salon);
end if;
if v_e_type = 'N1' then
insert INTO tempdb.calcul_ca_n_1(
cdoper, cdsite, dtvaleur, jour, ca_net_HT, panier, nb_tickets, nb_chq_cad, ca_net_HT_cca,
ca_net_TTC, ca_brut_TTC, ca_brut_HT, ca_brut_HT_lib, ca_brut_HT_pap, qte, qte_lib, qte_pap,
nbcartesach, mtcartesach, nbcartesrenouv, mtcartesrenouv, nbvigiab, pctobjca, pctobjlib, pctobjpap,
mtobjmois, mtobjlib, mtobjpap, mtinteresmax, mtinteresmax_lib , mtinteresmax_pap , nbrecpt_lib, nbrecpt_pap,
nbret_lib, nb_tickets_hors_presse, nb_car_kdo, ca_net_HT_kdo, nb_entrees, objmaxlib, objmaxpap, ca_brut_HT_tot_jour,
nbcartepay, txencartpay, nbcarteens, nbcartegrat, nbliseuse, cadesign, nbtcksscarte, nb_tickets_hors_salon)
values(
v_E_cdoper, v_cdsite, v_dtdeb, TO_CHAR(v_dtdeb, 'Day'), v_ca_net_HT, v_panier, v_nbtickets, coalesce(v_nb_cca, 0),
v_ca_net_HT_cca, v_ca_net_TTC, v_mtttc, v_ca_brut_HT, v_ca_brut_HT_lib, v_ca_brut_HT_pap, v_qte,
v_qte_lib, v_qte_pap, v_nbcartesach, v_mtcartesach, v_nbcartesrenouv, v_mtcartesrenouv, v_nbabovigi,
v_pctobjjour, v_pctobjjourlib, v_pctobjjourpap, v_mtobjmois, v_mtobjlib, v_mtobjpap, v_mtinteresmax,
v_mtinteresmax_lib , v_mtinteresmax_pap , v_nbrecpt_lib, v_nbrecpt_pap, v_nbret_lib, v_nbtickets_hors_presse,
v_nb_kdo, v_ca_net_HT_kdo, v_nb_entrees, v_objmaxlib, v_objmaxpap, v_ca_brut_HT_tot_jour, v_nbcartepay,
v_txencartpay, v_nbcarteens, v_nbcartegrat, v_nbliseuse, v_cadesign, v_nbtcksscarte, v_nbtickets_hors_salon);
end if;
end;
$$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment