1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
| ------------------------------- DETAIL DES COMMANDES ---------------------------------------------------------
----------suivi maj commandes----------------
--Informations sur les commandes clients
select
gr.nom "Groupe",
ad.ville_mix "ville",
upper(c.nom) "Nom",
t.opl "N° OPL",
co.no_etude_financeur "N° etude",
substring(cast(t.date_emission as character varying (50)),1,10) "Date commande"
from transaction t
inner join etablissement e on t.id_distributeur = e.id_etablissement
inner join adresse ad on e.id_adresse=ad.id_adresse
inner join entite ent on e.id_entite=ent.id_entite
inner join groupe gr on gr.id_groupe=ent.id_groupe
left join utilisateur uti on uti.id_utilisateur=t.vendeur
LEFT JOIN transaction_ligne TL1 ON TL1.id_transaction=t.id_transaction AND TL1.produit_eligible=FALSE
inner join client c on c.id_client = t.id_client
inner join parametre p2 on p2.id_parametre=c.civilite
inner join parametre para on para.id_parametre=c.profession
inner join transaction_ligne tl on tl.id_transaction = t.id_transaction and tl.produit_eligible=TRUE
inner join parametre p1 on p1.id_parametre=t.id_statut_opl
inner JOIN (select id_transaction, max(no_etude_financeur) "no_etude_financeur" from contrat group by id_transaction) as co ON co.id_transaction=t.id_transaction
inner JOIN contrat con ON co.id_transaction=con.id_transaction
inner join cat_evollis_categorie cec on cec.id_evollis_categorie=tl.id_cat_evollis_categorie
left join evenement ev1 on t.id_transaction=ev1.id_evt and ev1.id_liste_evenement=71 --commandes acceptation de financement et financées
left join evenement ev5 on t.id_transaction=ev5.id_evt and ev5.id_liste_evenement=82 --commandes financées
inner join evenement ev2 on t.id_transaction=ev2.id_evt and ev2.id_liste_evenement=66 --avoir la VR
where t.id_statut_opl in (364,365,367,603,354,617,366,356,357)
and T.test is not TRUE --enlever les tests
and T.doublon is not TRUE -- enlever les doublons
and T.id_transaction in (select id_evt from evenement where id_liste_evenement in (66)) --commandes qui ont un scoring
and T.date_emission > date_trunc('day',current_date-30)
group by ent.nom, e.nom, t.date_emission, co.no_etude_financeur, t.opl, t.id_transaction, c.nom, c.prenom, tl.designation_produit_dis, ev1.date_creation, p1.libelle_court, t.duree,
t.montant_total_ttc, t.montant_pack, (t.montant_pack*(t.duree-1)), ev2.commentaire, c.email_login ,ev5.date_creation, ad.cp, e.merchant_id,
cec.libelle,uti.nom, uti.prenom,c.date_naissance, para.libelle_long,c.revenus_mensuels,t.tarif_livraison_ttc,TL1.prix_ttc, tl.cout_garantie, con.no_contrat_financeur,gr.nom, ad.ville_mix, p2.libelle_court
order by t.date_emission; |
Partager