calcul_ca_insert_calculca_jour_nonj.SQL

calcul_ca_insert_calculca_jour_nonj.SQL

SELECT * FROM f_drop_function('calcul_ca_insert_calculca_jour_nonj');
create function calcul_ca_insert_calculca_jour_nonj(v_dtdebut timestamp without time zone, v_dtfin timestamp without time zone, v_e_cdoper character varying, v_e_type character varying) returns void
LANGUAGE plpgsql
AS $$
begin

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,			ca_brut_HT_tot_jour,			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,			nbcartepay,			txencartpay,			nbcarteens,			nbcartegrat,			nbliseuse,			cadesign,			nbtcksscarte, nb_tickets_hors_salon)
			select
				v_e_cdoper,				rcj.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,
				(select pctobjca from resultat_ca_pct_obj_jour where cdsite = rcj.cdsite and dtvaleur = rcj.dtvaleur) AS pctobjca,
				(select pctobjlib from resultat_ca_pct_obj_jour where cdsite = rcj.cdsite and dtvaleur = rcj.dtvaleur) AS pctobjlib,
				(select caobjtot from resultat_ca_pct_obj_jour where cdsite = rcj.cdsite and dtvaleur = rcj.dtvaleur) AS ca_brut_HT_tot_jour,
				(select pctobjpap from resultat_ca_pct_obj_jour where cdsite = rcj.cdsite and dtvaleur = rcj.dtvaleur) AS pctobjpap,
				(select mtobjca from resultat_ca_mt_obj_mois 
												where cdsite = rcj.cdsite
												and anneemois = cast(coalesce(COALESCE(TO_CHAR(rcj.dtvaleur,'YYYY'),'') 
																	|| lpad(cast(EXTRACT(month FROM rcj.dtvaleur) as text),2,'0'),'') as varchar(6))) ,
				(select mtobjlib from resultat_ca_mt_obj_mois 
												where cdsite = rcj.cdsite
												and anneemois = cast(coalesce(COALESCE(TO_CHAR(rcj.dtvaleur,'YYYY'),'') 
																	|| lpad(cast(EXTRACT(month FROM rcj.dtvaleur) as text),2,'0'),'') as varchar(6))) ,
				(select mtobjpap from resultat_ca_mt_obj_mois 
												where cdsite = rcj.cdsite
												and anneemois = cast(coalesce(COALESCE(TO_CHAR(rcj.dtvaleur,'YYYY'),'') 
																	|| lpad(cast(EXTRACT(month FROM rcj.dtvaleur) as text),2,'0'),'') as varchar(6)) ) ,
				(select mtinteressementmax from resultat_ca_mt_obj_mois 
																		where cdsite = rcj.cdsite
																		and anneemois = cast(coalesce(COALESCE(TO_CHAR(rcj.dtvaleur,'YYYY'),'') 
																					|| lpad(cast(EXTRACT(month FROM rcj.dtvaleur) as text),2,'0'),'')as varchar(6) ) ) ,
				(select mtinteressementmaxlib from resultat_ca_mt_obj_mois 
																			where cdsite = rcj.cdsite
																		  and anneemois = cast(coalesce(COALESCE(TO_CHAR(rcj.dtvaleur,'YYYY'),'') 
																				|| lpad(cast(EXTRACT(month FROM rcj.dtvaleur) as text),2,'0'),'') as varchar(6))) ,
				(select mtinteressementmaxpap from resultat_ca_mt_obj_mois 
																			where cdsite = rcj.cdsite
																			and anneemois = cast(coalesce(COALESCE(TO_CHAR(rcj.dtvaleur,'YYYY'),'') 
																					|| lpad(cast(EXTRACT(month FROM rcj.dtvaleur) as text),2,'0'),'') as varchar(6))) ,
				nbrecpt_lib,				nbrecpt_pap,				nbret_lib,				nb_tickets_hors_presse,				nb_car_kdo,				ca_net_HT_kdo,				nb_entrees,
				(select objmaxlib from resultat_ca_pct_obj_jour where cdsite = rcj.cdsite and dtvaleur = rcj.dtvaleur) AS objmaxlib,
				(select objmaxpap from resultat_ca_pct_obj_jour where cdsite = rcj.cdsite and dtvaleur = rcj.dtvaleur) AS objmaxpap,
				nbcartepay,				txencartpay,				nbcarteens,				nbcartegrat,				nbliseuse,				cadesign,				nbtcksscarte,nb_tickets_hors_salon
			from resultat_ca_jour rcj
			where dtvaleur >= v_dtdebut 
			and dtvaleur <= v_dtfin;
	end if;
-- données sur N-1 
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,
			nbcartepay,			txencartpay,			nbcarteens,			nbcartegrat,			nbliseuse,			cadesign,			nbtcksscarte,nb_tickets_hors_salon)
			select
				v_E_cdoper,				rcj.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,
				null AS pctobjca,				null AS pctobjlib,				null AS pctobjpap,				null AS mtobjmois,				null AS mtobjlib,
				null AS mtobjpap,				null AS mtinteresmax,				null AS mtinteresmax_lib,				null AS mtinteresmax_pap,
				nbrecpt_lib,				nbrecpt_pap,				nbret_lib,				nb_tickets_hors_presse,				nb_car_kdo,				ca_net_HT_kdo,
				nb_entrees,				nbcartepay,				txencartpay,nbcarteens,				nbcartegrat,nbliseuse,cadesign,nbtcksscarte,nb_tickets_hors_salon
			from resultat_ca_jour rcj
			where dtvaleur >= v_dtdebut 
			and dtvaleur <= v_dtfin;
end if ; 	
end;
$$;