[5/09/23 12:14:54]SELECT cou.idcourse as id, ifnull(soc.nom,'') as vsoc, ifnull(liv.abrege,'') as vliv, ifnull(cou.bon,'') as vbon, date_format(cou.datenl, '%d/%m/%Y') as vcoudt,cou.fk_etat,ec.libetat as vetat, SUBSTR(cou.hdebut, 1, 5) as vheuenl, ifnull(cou.exped,'') as vexped,   case when fk_type in (8,10) then ifnull(cou.AdresseEnlevTransf,'') else ifnull(cou.chantier1,'') end as vaddresse1, case when fk_type in (8,10) then ifnull(cou.AdresseLivTransf,'') else ifnull(cou.chantier2,'') end as vaddresse2,  case when fk_type in (8,10) then ifnull(cou.CPEnlevTransf,'') else ifnull(cou.CPChantier1,'') end as vzip1, case when fk_type in (8,10) then ifnull(cou.VilleEnlevTransf,'') else ifnull(cou.VilleChantier1,'') end as vville1, case when fk_type in (8,10) then ifnull(cou.CPLivTransf,'') else ifnull(cou.CPChantier2,'') end as vzip2, case when fk_type in (8,10) then ifnull(cou.VilleLivTransf,'') else ifnull(cou.VilleChantier2,'') end as vville2, ifnull(ROUND((ifnull((SELECT REPLACE(FORMAT(ifnull(SUM(total_ht),0),2), ',','') AS Total FROM llx_tr_courses_det WHERE fk_courses = cou.idcourse GROUP BY fk_courses),0)+cou.montant), 2),'') AS vtotalht, cap.libelle as vcapacite, ifnull(cou.service,'') as vservice, cou.ei as vei,ifnull(cou.non_facturer,'') as vnf,ifnull(cou.regleSousTraitant,'') as vrss, zn.libelle as vtypej, ifnull(ROUND(cou.punet, 2),'') as vpunet, cou.postec as vpostec, cou.postem as vpostem, '' as visreg, ifnull(cou.obsexp,'') as vobs,ifnull(cou.ref_commande,'') as vreffac, CONCAT(eb.mode, eb.etat) as ebetat, cou.sendstate, cou.fk_regular, cap.captyp, cou.picod,cou.fk_type,ct.libcoursetype as vtype,ifnull(cou.divers_address1,'') as vcontact, cou.scan, ifnull(ROUND(cou.punetliv, 2),'') as vpunetliv,ifnull(cou.prix_decharge, 0) as vdecharge1, cou.prix_decharge2 as vdecharge2,cou.prix_decharge3 as vdecharge3,cou.prix_cubage as vcubage,cou.nb_tour as vnbtour,ifnull(cou.obs,'') as voutils,ifnull(cou.faafacturer,0) as vfaafacturer,ifnull(f.ref,'') as vfacture, cou.qte as vqte, ifnull(ROUND(cou.montant,2),'') as vmontant , ifnull(truck.immat,'') as vimmat,ifnull(p.ref,'') as vpropocom,ifnull(soctr.ref_com,0) as ref_com,ifnull(cou.contact,'') as vcontactliv  FROM llx_tr_courses cou LEFT JOIN llx_societe soc ON soc.rowid = cou.fk_soc LEFT JOIN llx_tr_societe_trfields soctr ON cou.fk_soc = soctr.fk_societe LEFT JOIN llx_tr_capacite cap ON cap.idcapacite = cou.fk_capacite LEFT JOIN llx_tr_journee zn ON zn.rowid = cou.fk_zone LEFT JOIN llx_tr_livreur liv ON liv.rowid = cou.fk_livreur LEFT JOIN llx_tr_truck truck ON truck.rowid = cou.fk_truck LEFT JOIN llx_tr_ebon eb ON eb.fk_courses = cou.idcourse and eb.fk_livreur = cou.fk_livreur AND ifnull(eb.bInactif,0) = 0  LEFT JOIN llx_tr_coursetype ct ON ct.idcoursetype = cou.fk_type LEFT JOIN llx_tr_etatcourse ec ON ec.idetat = cou.fk_etat LEFT JOIN llx_propal p ON p.rowid = cou.fk_propal LEFT JOIN llx_facture f ON f.rowid = cou.pino WHERE (date_format(ifnull(cou.datenl,'0000-00-00'), '%Y-%m-%d') = '2023-09-05') AND cou.entity = 1 AND cou.fk_etat > 1  ORDER BY cou.datenl, cou.hdebut
[5/09/23 12:14:58]SELECT cou.idcourse as id, ifnull(soc.nom,'') as vsoc, ifnull(liv.abrege,'') as vliv, ifnull(cou.bon,'') as vbon, date_format(cou.datenl, '%d/%m/%Y') as vcoudt,cou.fk_etat,ec.libetat as vetat, SUBSTR(cou.hdebut, 1, 5) as vheuenl, ifnull(cou.exped,'') as vexped,   case when fk_type in (8,10) then ifnull(cou.AdresseEnlevTransf,'') else ifnull(cou.chantier1,'') end as vaddresse1, case when fk_type in (8,10) then ifnull(cou.AdresseLivTransf,'') else ifnull(cou.chantier2,'') end as vaddresse2,  case when fk_type in (8,10) then ifnull(cou.CPEnlevTransf,'') else ifnull(cou.CPChantier1,'') end as vzip1, case when fk_type in (8,10) then ifnull(cou.VilleEnlevTransf,'') else ifnull(cou.VilleChantier1,'') end as vville1, case when fk_type in (8,10) then ifnull(cou.CPLivTransf,'') else ifnull(cou.CPChantier2,'') end as vzip2, case when fk_type in (8,10) then ifnull(cou.VilleLivTransf,'') else ifnull(cou.VilleChantier2,'') end as vville2, ifnull(ROUND((ifnull((SELECT REPLACE(FORMAT(ifnull(SUM(total_ht),0),2), ',','') AS Total FROM llx_tr_courses_det WHERE fk_courses = cou.idcourse GROUP BY fk_courses),0)+cou.montant), 2),'') AS vtotalht, cap.libelle as vcapacite, ifnull(cou.service,'') as vservice, cou.ei as vei,ifnull(cou.non_facturer,'') as vnf,ifnull(cou.regleSousTraitant,'') as vrss, zn.libelle as vtypej, ifnull(ROUND(cou.punet, 2),'') as vpunet, cou.postec as vpostec, cou.postem as vpostem, '' as visreg, ifnull(cou.obsexp,'') as vobs,ifnull(cou.ref_commande,'') as vreffac, CONCAT(eb.mode, eb.etat) as ebetat, cou.sendstate, cou.fk_regular, cap.captyp, cou.picod,cou.fk_type,ct.libcoursetype as vtype,ifnull(cou.divers_address1,'') as vcontact, cou.scan, ifnull(ROUND(cou.punetliv, 2),'') as vpunetliv,ifnull(cou.prix_decharge, 0) as vdecharge1, cou.prix_decharge2 as vdecharge2,cou.prix_decharge3 as vdecharge3,cou.prix_cubage as vcubage,cou.nb_tour as vnbtour,ifnull(cou.obs,'') as voutils,ifnull(cou.faafacturer,0) as vfaafacturer,ifnull(f.ref,'') as vfacture, cou.qte as vqte, ifnull(ROUND(cou.montant,2),'') as vmontant , ifnull(truck.immat,'') as vimmat,ifnull(p.ref,'') as vpropocom,ifnull(soctr.ref_com,0) as ref_com,ifnull(cou.contact,'') as vcontactliv  FROM llx_tr_courses cou LEFT JOIN llx_societe soc ON soc.rowid = cou.fk_soc LEFT JOIN llx_tr_societe_trfields soctr ON cou.fk_soc = soctr.fk_societe LEFT JOIN llx_tr_capacite cap ON cap.idcapacite = cou.fk_capacite LEFT JOIN llx_tr_journee zn ON zn.rowid = cou.fk_zone LEFT JOIN llx_tr_livreur liv ON liv.rowid = cou.fk_livreur LEFT JOIN llx_tr_truck truck ON truck.rowid = cou.fk_truck LEFT JOIN llx_tr_ebon eb ON eb.fk_courses = cou.idcourse and eb.fk_livreur = cou.fk_livreur AND ifnull(eb.bInactif,0) = 0  LEFT JOIN llx_tr_coursetype ct ON ct.idcoursetype = cou.fk_type LEFT JOIN llx_tr_etatcourse ec ON ec.idetat = cou.fk_etat LEFT JOIN llx_propal p ON p.rowid = cou.fk_propal LEFT JOIN llx_facture f ON f.rowid = cou.pino WHERE (date_format(ifnull(cou.datenl,'0000-00-00'), '%Y') = '2023') AND cou.entity = 1 AND cou.fk_etat > 1  ORDER BY cou.datenl, cou.hdebut