SELECT t.tn AS 't#', t.title AS '问题', t.create_time '创建时间', q.name AS '角色', concat(cu1.first_name,' ', cu1.last_name) AS '用户', ty.name AS '类型',
concat(u1.first_name,' ', u1.last_name) AS '处理人',
cu1.first_name AS '用户姓名',
max(case when IFNULL(f.id,0)<>0 then f.create_time ELSE '' END) 关闭时间,
max(case when cu1.email is null then cu1.login else cu1.email END) 邮箱,
cu1.title AS '是否VIP',
max(case when b.field_id=40 then b.value_text else '' END) 位置,
max(case when b.field_id=41 then b.value_text else '' END) Inprocess,
cu1.login 用户姓名,
max(case when b.field_id=45 then b.value_text else '' END) 是否超时,
max(case when b.field_id=46 then b.value_text else '' END) 一级目录,
max(case when b.field_id=47 then b.value_text else '' END) 二级目录,
max(case when b.field_id=48 then b.value_text else '' END) 三级目录,
MAX(case
when SUBSTRING_INDEX(cu1.email, '@', -1) = 'cn.lvmh-pc.com' then 'SSC'
when SUBSTRING_INDEX(cu1.email, '@', -1) = 'lvmh-pc.com' then 'SSC'
when SUBSTRING_INDEX(cu1.email, '@', -1) = 'guerlain.fr' then 'GUE'
when SUBSTRING_INDEX(cu1.email, '@', -1) = 'diormail.com' then 'PCD'
when SUBSTRING_INDEX(cu1.email, '@', -1) = 'parfumsgivenchy.fr' then 'FB'
when SUBSTRING_INDEX(cu1.email, '@', -1) = 'benefitcosmetics.com' then 'BEN'
when SUBSTRING_INDEX(cu1.email, '@', -1) = 'makeupforever.cn' then 'MUFE'
when SUBSTRING_INDEX(cu1.email, '@', -1) = 'acquadiparma.com' then 'ADP'
when SUBSTRING_INDEX(cu1.email, '@', -1) = 'fresh.com' then 'Fresh'
ELSE 'SSC'END) brand
FROM ticket t
LEFT JOIN ticket_type ty ON ty.id = t.type_id
LEFT JOIN users u1 ON u1.id = t.user_id
LEFT JOIN queue q ON q.id = t.queue_id
left join dynamic_field_value b on t.id = b.object_id and b.field_id IN(40,41,39,45,46,47,48)
left JOIN ticket_history f on t.id = f.ticket_id AND (f.name LIKE '%closed%' OR f.name LIKE '%首次电话解决%')
LEFT JOIN customer_user cu1 ON cu1.login = t.customer_user_id
where
t.create_time > '2022-02-01'
group BY t.create_time, t.tn
