· 5 years ago · Feb 07, 2020, 03:34 PM
1SELECT
2(LEFT(a.cnpj, 10)) 'PARENT_COMPANY',
3IFNULL(a.company_name, CONCAT(a.first_name,' ',a.last_name)) 'COMPANY_NAME',
4SUBSTRING(a.email,INSTR(a.email,'@')) 'CUSTOMER_DOMAIN',
5COUNT(1) 'COUNT_ID',
6b.sales_num 'SELLER_NUMBERS',
7ST,
8SERVICE_TYPE_VALIDATION, #MAIS DE UM SERVICE_TYPE ----------------------------> PADRONIZAR SERVICE_TYPE
9SELLER_VALIDATION, #MAIS DE UM VENDEDOR --------------------------------------> PADRONIZAR VENDEDOR
10DELETE_PRO_VALIDATION, #CONTÉM RICARDO E NÃO É PRO ---------------------------> EXCLUIR PRO
11DELETE_RICARDO_VALIDATION, #NÃO CONTÉM RICARDO E É PRO ------------------------------> DELETAR RICARDO
12CUSTOMER_SERVICE_VALIDATION, #CONTÉM VENDEDOR E É CUSTOMER_SERVICE -----------> CORRIGIR SERVICE_TYPE
13ADD_CUSTOMER_SERVICE, #NÃO CONTÉM VENDEDOR E É COMMERCIAL --------------------> VOLTAR PARA RELACIONAMENTO
14CUSTOMER_TYPE_VALIDATION, #DOMÍNIO NÃO É PÚBLICO, TEM CNPJ E É PF -------------> ALTERAR PARA PJ
15CASE WHEN SERVICE_TYPE_VALIDATION = 'OK' AND SELLER_VALIDATION = 'OK' AND DELETE_PRO_VALIDATION = 'OK' AND DELETE_RICARDO_VALIDATION = 'OK' AND CUSTOMER_SERVICE_VALIDATION = 'OK' AND ADD_CUSTOMER_SERVICE = 'OK' AND CUSTOMER_TYPE_VALIDATION = 'OK' THEN 'OK' ELSE 'CHECK' END 'TOTAL'
16
17FROM customer a
18
19LEFT JOIN
20 (SELECT
21 LEFT(z.cnpj,10) 'idd',
22 IFNULL(z.company_name, CONCAT(z.first_name,' ',z.last_name)) 'COMPANY',
23 COUNT(DISTINCT(z.service_type)),
24 CASE WHEN COUNT(DISTINCT(z.service_type)) > 1 THEN 'ERROR' ELSE 'OK' END 'SERVICE_TYPE_VALIDATION'
25 FROM customer z
26 WHERE
27 LEFT(z.cnpj, 10) IS NOT NULL
28 AND SUBSTRING(z.email,INSTR(z.email,'@')) NOT IN ('@ICLOUD.COM','@GMAIL.COM','@HOTMAIL.COM','@OUTLOOK.COM','@YAHOO.COM.BR','@UOL.COM.BR','@PRINTI.COM.BR','@TERRA.COM.BR','@BOL.COM.BR','@YAHOO.COM','@LIVE.COM','@OUTLOOK.COM.BR','@GLOBO.COM', 'gmail.com')
29 AND z.sales_user_id IS NOT NULL
30 GROUP BY LEFT(z.cnpj,10)) sub1 ON sub1.idd = LEFT(a.cnpj, 10)
31
32LEFT JOIN
33 (SELECT
34 LEFT(cnpj, 10) 'id' ,
35 IFNULL(company_name, CONCAT(first_name,' ',last_name)) 'name_comp',
36 COUNT(DISTINCT(sales_user_id)) 'SALES_NUM',
37 sales_user_id 'SUD',
38 service_type 'ST',
39 CASE WHEN COUNT(DISTINCT(sales_user_id)) > 1 THEN 'ERROR' ELSE 'OK' END 'SELLER_VALIDATION',
40 CASE WHEN sales_user_id = 43 AND service_type <> 'PRINTI_PRO' AND sales_user_id IS NOT NULL THEN 'ERROR' ELSE 'OK' END 'DELETE_PRO_VALIDATION',
41 CASE WHEN sales_user_id <> 43 AND service_type = 'PRINTI_PRO' AND sales_user_id IS NOT NULL THEN 'ERROR' ELSE 'OK' END 'DELETE_RICARDO_VALIDATION',
42 CASE WHEN sales_user_id IS NOT NULL AND service_type = 'CUSTOMER_SERVICE' THEN 'ERROR' ELSE 'OK' END 'CUSTOMER_SERVICE_VALIDATION',
43 CASE WHEN sales_user_id IS NULL AND service_type = 'COMMERCIAL' THEN 'ERROR' ELSE 'OK' END 'ADD_CUSTOMER_SERVICE',
44 CASE WHEN email NOT IN ('@ICLOUD.COM','@GMAIL.COM','@HOTMAIL.COM','@OUTLOOK.COM','@YAHOO.COM.BR','@UOL.COM.BR','@PRINTI.COM.BR','@TERRA.COM.BR','@BOL.COM.BR','@YAHOO.COM','@LIVE.COM','@OUTLOOK.COM.BR','@GLOBO.COM', 'gmail.com') AND cnpj IS NOT NULL AND type = 'PF' THEN 'ERROR' ELSE 'OK' END AS 'CUSTOMER_TYPE_VALIDATION'
45 FROM customer
46 WHERE LEFT(cnpj, 10) != ''
47 AND sales_user_id IS NOT NULL
48 GROUP BY
49 LEFT(cnpj, 10),
50 IFNULL(company_name, CONCAT(first_name,' ',last_name))) b
51 ON LEFT(a.cnpj, 10) = b.id
52 AND IFNULL(a.company_name, CONCAT(a.first_name,' ',a.last_name)) = b.name_comp
53 WHERE LEFT(a.cnpj, 10) IS NOT NULL
54 AND SUBSTRING(a.email,INSTR(a.email,'@')) NOT IN ('@ICLOUD.COM','@GMAIL.COM','@HOTMAIL.COM','@OUTLOOK.COM','@YAHOO.COM.BR','@UOL.COM.BR','@PRINTI.COM.BR','@TERRA.COM.BR','@BOL.COM.BR','@YAHOO.COM','@LIVE.COM','@OUTLOOK.COM.BR','@GLOBO.COM', 'gmail.com')
55 AND a.sales_user_id IS NOT NULL
56 GROUP BY LEFT(a.cnpj, 10),
57 IFNULL(a.company_name, CONCAT(a.first_name,' ',a.last_name)),
58 SUBSTRING(a.email,INSTR(email,'@')),
59 b.sales_num
60
61HAVING COUNT(1) >1