SELECT a.id AS id,a.codeSociete AS codeSociete,a.codePrestataire AS codePrestataire,a.numeroFeuilleMaladie AS numeroFeuilleMaladie,
a.numeroDecompte AS numeroDecompte,a.idAdherent AS idAdherent,a.idBeneficiaire AS idBeneficiaire,a.numeroBeneficiaire AS numeroBeneficiaire,
a.numeroFacture AS numeroFacture,a.numeroSinistre AS numeroSinistre,a.valide AS valide,a.fraisReel AS fraisReel,a.fraisReelBase AS fraisReelBase,
a.prixManuel AS prixManuel,a.prixBase AS prixBase,a.montantTm AS montantTm,a.montantArembourser AS montantArembourser,a.montantRembourse AS montantRembourse,
a.fraisExclu AS fraisExclu,a.depassement AS depassement,a.fraisRetenu AS fraisRetenu,a.montantForce AS montantForce,
a.montantRedresse AS montantRedresse,a.montantApayerCalc AS montantApayerCalc,a.montantApayer AS montantApayer,a.montantPaye AS montantPaye,
a.tiersPayant AS tiersPayant,a.okDecompte AS okDecompte,a.regle AS regle,a.dateReglement AS dateReglement,a.montantFranchise AS montantFranchise,
a.dateSysteme AS dateSysteme,a.codeUtilisateur AS codeUtilisateur,a.userControle AS userControle,a.codeExercice AS codeExercice,a.codeMois AS codeMois,
a.observations AS observations,a.numeroBonSeance AS numeroBonSeance,a.numeroDemandeDecompte AS numeroDemandeDecompte,a.userDemandeDecompte AS userDemandeDecompte,
a.numeroLotDecompte AS numeroLotDecompte,YEAR(a.dateSysteme) AS exercice,a.idFactureSmart AS idFactureSmart,a.gc AS gc,IFNULL(pr.libelle,'') AS prestataire,
IFNULL(pr.codeTypePrestataire,'') AS codeTypePrestataire,b.idPolice AS idPolice,IFNULL(b.beneficiaire,'') AS beneficiaire,IFNULL(b.adherent,'') AS adherent,
IFNULL(b.souscripteur,'') AS souscripteur,IFNULL(b.emailAdherent,'') AS emailAdherent,IFNULL(b.numeroAdherent,'') AS numeroAdherent,
a.codePrestataire AS codePrestataireLivraison,a.codeTypeFacture AS codeTypeFacture,IFNULL(fm.codePrestataire,'') AS codePrestatairePrescription,
IFNULL(fm.numeroPrescription,'0') AS numeroPrescription,IFNULL(fm.numeroBonConsultation,'0') AS numeroBonConsultation,
IFNULL(fm.numeroBonOrdonnance,'0') AS numeroBonOrdonnance,IFNULL(fm.numeroBonHospitalisation,'0') AS numeroBonHospitalisation,
IFNULL(fm.hospitalisation,'0') AS hospitalisation,IFNULL(fm.numeroChambre,'') AS numeroChambre,IFNULL(fm.chirurgie,'0') AS chirurgie,
IFNULL(fm.numeroBonOptique,'0') AS numeroBonOptique,IFNULL(fm.numeroOptique,'0') AS numeroOptique,IFNULL(fm.numeroExamen,'0') AS numeroExamen,
IFNULL(fm.numeroBonExamen,'0') AS numeroBonExamen,IFNULL(fm.numeroPrescriptionKine,'0') AS numeroPrescriptionKine,IFNULL(fm.numeroBonKine,'0') AS numeroBonKine,
LEFT(a.dateSysteme,10) AS dateFacture,a.id AS idFacture,fn_numordre(DAYOFMONTH(LEFT(a.dateSysteme,10)),2) AS jourFacture
-- ,d.libelle AS garantieFacture,a.fraisAssure AS fraisAssure,af.libelle AS libelleAffection,af.libelleEng AS libelleAffectionEng,IFNULL(af.codeAffection,'0000') AS codeAffection
,IFNULL(af.codeTypeAffection,'00') AS codeTypeAffection 
FROM (((((p_facture a 
LEFT JOIN prestataire pr ON((pr.codePrestataire = CONVERT(a.codePrestataire USING utf8)))) 
LEFT JOIN vw_infos_contrat_assures b ON((b.idBeneficiaire = a.idBeneficiaire))) 
LEFT JOIN p_feuillemaladie fm ON((fm.numeroFeuilleMaladie = a.numeroFeuilleMaladie))) 
LEFT JOIN p_typefacturegarantie d ON((d.codeTypeFactureGarantie = CONCAT(a.codeTypeFacture,IFNULL(fm.hospitalisation,'0'))))) 
LEFT JOIN p_affection af ON(((af.codeSociete = a.codeSociete) AND (af.codeAffection = fm.codeAffection))))
WHERE a.idAdherent=5873