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
| SELECT SQL_CALC_FOUND_ROWS DATE_FORMAT( actions.date_recontact, '%d/%m/%Y' ) AS date_recontact, lignes_etape.resultat, contacts.id_contact, contacts.nom, societes_specifiques.topage, societes_specifiques.score_societe, societes.id_societe, script_etapes.groupe_etape, s_est_deplace, operation_a_effectuer, SUBSTRING( actions.heure_recontact, 1, 5 ) AS heure_recontact, societes.nom, actions.id_action, actions.id_contact, contacts.fonction_autre
FROM actions
LEFT JOIN lignes_etape ON actions.resultat = lignes_etape.id_ligne_etape
INNER JOIN contacts ON actions.id_contact = contacts.id_contact
AND contacts.plus_dans_la_soc =0
INNER JOIN contacts_specifiques ON contacts_specifiques.id_contact = contacts.id_contact
INNER JOIN societes ON contacts.id_societe = societes.id_societe
INNER JOIN societes_specifiques ON societes_specifiques.id_societe = societes.id_societe
AND societes_specifiques.id_entite =2
INNER JOIN script_etapes ON actions.etape = script_etapes.id_script_etape
INNER JOIN societes_users ON societes_users.id_societe = societes.id_societe
AND societes_users.id_campagne =1
WHERE actions.enregistrement_ok =1
AND actions.sommeil =0###############
AND actions.id_contact
IN (
SELECT A3.id_contact
FROM actions AS A3
INNER JOIN script_etapes S1 ON A3.etape = S1.id_script_etape
WHERE S1.groupe_etape =3
AND A3.resultat =0
HAVING 0 = (
SELECT COUNT( * )
FROM actions A4
INNER JOIN script_etapes ON A4.etape = script_etapes.id_script_etape
WHERE script_etapes.groupe_etape =3
AND A4.id_contact = A3.id_contact
AND A3.id_action <> A3.id_action )
)
AND actions.id_action = (
SELECT MAX( id_action )
FROM actions AS A2
INNER JOIN script_etapes AS S3 ON A2.etape = S3.id_script_etape
WHERE A2.id_contact = actions.id_contact
AND S3.groupe_etape =1
) |
Partager