1 2 3 4 5 6 7 8 9 10
| SELECT E.idConvention, E.idFacture, E.idEcheance, E.datePaiement, E.montantEch, DATEDIFF(E.datePaiement, CURDATE()) AS retard, F.montantReel, DATE(F.dateCreation) AS dateCreation, DATEDIFF(CURDATE(), F.dateCreation) AS retardF, F.indexFacture, E.commentaire, E.convRecue1, E.convRecue, E.condPaiement, F.nomFichier AS facture, F.Contentieux, CO.indexConvention, CO.idAction, CO.nomFichier AS convention,
(SELECT nomClient FROM sg_client AS C WHERE E.idClient=C.idClient) AS Dest1,
(SELECT nomClient FROM sg_client AS C WHERE CO.idClient=C.idClient) AS entreprise,
(SELECT CONCAT_WS(' ',prenomAuditeur, nomAuditeur) FROM sg_auditeur AS A WHERE E.idAuditeur=A.idAuditeur) AS Dest2, CO.listeParticipants, CO.dateFin, RE.nomFichier AS fichRelance, RE.numRelance, RE.dateRelance, RE.nomUser, RE.typeEnvoi
FROM `sg_echeancier` AS E
JOIN sg_convention AS CO ON E.idConvention=CO.idConvention
LEFT JOIN sg_facture AS F ON F.idFacture=E.idFacture
LEFT JOIN sg_relances AS RE ON F.idFacture=RE.idFacture
WHERE (ISNULL(F.dateCreation) OR (F.factureAnnulee<>1 AND F.facturePayee<>1)) AND E.montantEch>=0 AND F.idFacture LIKE '$code%'
ORDER BY CO.idAction, CO.indexConvention, E.idEcheance |
Partager