Ticket de test pour l'application titre - Githubissues

TRUNCATE temp_tablename;
SELECT *
FROM temp_tablename;

INSERT INTO temp_tablename (tablename_id, firstname, lastname, email, phone, birthday_date)
SELECT 
  p.id_tablename, 
  p.firstname, 
  p.lastname, 
  p.email, 
  REPLACE(REPLACE(REPLACE(REPLACE(p.mobile, ' ', ''), '-', ''), '.', ''), '+33', '0'), 
  max(DATE(c1.created_at))
FROM tablename p
  inner join othertable c1
  on p.id_tablename = c1.id_tablename
WHERE
  c1.created_at BETWEEN '2014-01-01' and '2017-09-01'
  AND c1.status != 'cancelled'
  AND p.accept_email = 1
  AND c1.source IN ('asite.com', 'backoffice.com')
  AND p.email not like '%@marketplace.amazon.fr'
GROUP BY p.id_tablename
;

UPDATE temp_tablename
SET phone = IF((phone LIKE '06%' OR phone LIKE '07%') AND length(phone) = 10, phone, null),
firstname = IF(firstname = 'tmp', '', firstname),
lastname = IF(lastname = 'tmp', '', lastname)
;

SELECT CONCAT(p.firstname, ' ', p.lastname), p.email
FROM temp_tablename p
  LEFT JOIN othertable c
    ON p.tablename_id = c.id_tablename
    AND c.created_at > '2017-09-01'
WHERE c.id_othertable is null
GROUP BY p.tablename_id;

lossendae / test

Ticket de test pour l'application titre #1