- SELECT DISTINCT(p.pid), 'job'
- FROM profiles AS p
- INNER JOIN fusionax_activites AS f ON (f.ax_id = p.ax_id)
- INNER JOIN fusionax_entreprises AS fe ON (fe.Code_etab = f.Code_etab)
- WHERE (fe.Raison_sociale = '' AND NOT EXISTS (SELECT *
- FROM profile_job AS pj
- WHERE pj.pid = p.pid))
- OR (fe.Raison_sociale != '' AND EXISTS (SELECT *
- FROM profile_job AS pj
- WHERE pj.pid = p.pid))");
-// Then we retrieve jobs without entreprise name.
-XDB::rawExecute("INSERT INTO profile_job (id, pid, jobid, pub, description)
- SELECT 0, p.pid, NULL, IF(f.Annuaire = 1, 'ax', 'private'),
- IF(f.Raison_sociale,
- IF(f.Libelle_fonctio, CONCAT(f.Raison_sociale, ' ', f.Libelle_fonctio), f.Raison_sociale),
- f.Libelle_fonctio)
- FROM fusionax_activites AS f
- INNER JOIN profiles AS p ON (f.ax_id = p.ax_id)
- INNER JOIN fusionax_entreprises AS fe ON (fe.Code_etab = f.Code_etab AND fe.Raison_sociale = '')
- WHERE NOT EXISTS (SELECT *
- FROM profile_job AS pj
- WHERE pj.pid = p.pid)");
-// We insert complete jobs for profile already having jobs.
-XDB::rawExecute("INSERT INTO profile_job (id, pid, jobid, pub, description)
- SELECT MAX(pj.id) + 1, p.pid, pe.id, IF(f.Annuaire = 1, 'ax', 'private'),
- IF(f.Raison_sociale,
- IF(f.Libelle_fonctio, CONCAT(f.Raison_sociale, ' ', f.Libelle_fonctio), f.Raison_sociale),
- f.Libelle_fonctio)
- FROM fusionax_activites AS f
- INNER JOIN profiles AS p ON (f.ax_id = p.ax_id)
- INNER JOIN fusionax_entreprises AS fe ON (fe.Code_etab = f.Code_etab AND fe.Raison_sociale != '')
- INNER JOIN profile_job_enum AS pe ON (pe.AX_code = f.Code_etab)
- INNER JOIN profile_job AS pj ON (pj.pid = p.pid)
- GROUP BY p.pid");
-// Delete everything that has already been inserted.
-XDB::rawExecute("DELETE f
- FROM fusionax_activites AS f
- INNER JOIN profiles AS p ON (f.ax_id = p.ax_id)
- INNER JOIN fusionax_entreprises AS fe ON (fe.Code_etab = f.Code_etab)
- WHERE fe.Raison_sociale = ''
- OR (fe.Raison_sociale != '' AND EXISTS (SELECT *
- FROM profile_job AS pj
- WHERE pj.pid = p.pid))");
-// We finally add new complete jobs.