as count_total_login , count(distinct case when created_time >= current_date - interval'7 days' then created_time end) as count_login_in_7 from partner_login group by partner_id ), partner_movie_attach as ( select mh.partner_id , max(case when mmh.movie_id is not null then 1 else 0 end) as movie_attach_flg from message_header mh left join movie_message_header mmh on (mmh.message_header_id = mh.id) group by mh.partner_id ), partner_sug_reg_count as ( select a.partner_id , count(distinct d.personal_code) as count_suggest , count(distinct case when r.user_id is not null and r.deleted_time is null then d.personal_code end) as count_register from assignment a join users d on (d.id = a.users_id and d.client_id = 1000) left join m3_user m on (m.personal_code = d.personal_code) left join registration r on (r.user_id = m.user_id and r.client_id = 1000) where exists (select 1 from partner f where f.client_id = 1000 and f.id = a.partner_id) and priority = 1 group by a.partner_id ) select m.message_body_id ,to_char(m.send_time, 'YYYY-MM-DD') as send_date ,(select h.message_body_id from message_header h where h.id = m.parent_id) as parent_body_id ,g1.name as shiten ,g2.name as team ,to_char(m.send_time, 'YYYY-MM-DD') as person_answered_date ,'"'||d.workplace_name||'"' as workplace_name ,m.user_id as user_id ,d.personal_code as personal_person_cd ,d.name as person_name ,g3.name as tanto ,f.name as partner_name ,f.id as partner_id ,coalesce(a.movie_attach_flg, 0) as partner_movie_attach_flg ,coalesce(l.count_total_login, 0) as partner_login_count_total ,coalesce(l.count_login_in_7, 0) as partner_login_count_in_7 ,coalesce(src.count_suggest, 0) as partner_count_suggest_person ,coalesce(src.count_register, 0) as partner_count_register_person ,case when (case when not ( 1=0 OR m.title like '「ありがとう」が押されました%' OR m.title like '「良かったよ」が押さ れました%' OR m.title like '「もう少し詳しく」が押されました%' OR m.title like '「見たよ」が押されました%' OR m.title like '「ありがとう」が押されました%' OR m.title like '「良かったよ」が押されました%' OR m.title like '「もう少し詳しく」 が押されました%' OR m.title like '「見たよ」が押されました%' OR m.title like '「なるほど」が押されました%' OR m.title like '「頼りにしてます」が押されました%' OR m.title like '「ありがとう(K)」が押されました%' OR m.title like '「いいね (K)」が押されました%' OR m.title like '「論文(K)」が押されました%' OR m.title like '「見たよ(K)」が押されました%' OR m.title like '「次回に期待(K)」が押されました%' OR m.title like '「頼りにしてる(K)」が押されました%' ) then '"'|| regexp_replace( regexp_replace(regexp_replace(m.insertion_message, E'<[^>]*>', '', 'g'), E'[ ¥r¥n¥t]+', ' ', 'g'), '引 用本文.+', '') ||'"' end) is not null and not m.title like 'はい・いいえ回答%' then '1' else '0' end as person_text_message_flg ,case when m.title like '「イイね!」が押されました%' then 1 else 0 end as stamp_mat ,case when m.title like '「すごくイイね!」が押されました%' then 1 else 0 end as stamp_good ,case when m.title like '「ぜひ会いたい!」が押されました%' then 1 else 0 end as stamp_visit ,case when m.title like '「見たよ」が押されました%' then 1 else 0 end as stamp_watch ,case when m.title like '「よくわからない」が押されました%' then 1 else 0 end as stamp_pre ,case when m.title like '「残念!」が押されました%' then 1 else 0 end as stamp_for ,case when m.title like '「もう少し詳しく」が押されました%' then 1 else 0 end as stamp_detail ,case when m.title like '「参考になったよ」が押されました%' then 1 else 0 end as stamp_helpful ,case when m.title like '「試してみようかな」が押されました%' then 1 else 0 end as stamp_try ,case when m.title like '「試してみたよ」が押されました%' then 1 else 0 end as stamp_new ,case when m.title like 'はい・いいえ回答%' then '1' else '0' end as yes_no ,case when m.title like '%が押されました%' then 1 else 0 end as stamp_all ,to_char(res.send_time, 'YYYY-MM-DD') as reply_date from partner f left join ( select msg.user_id , msg.personal_code , msg.message_body_id , msg.send_time , msg.parent_id , msg.title from message msg where msg.client_id = 1000 and msg.message_type = 'USER_TO_PARTNER' and not exists (select 1 from test_users t where msg.user_id = t.user_id) and msg.send_time is not null and msg.send_time < current_date ) m on m.partner_id = f.id left join m3_user m3 on m.user_id = m3.user_id left join users d on m3.personal_code = d.personal_code and d.client_id = 1000 left join login_count_partner l on l.partner_id = f.id left join partner_movie_attach a on (a.partner_id = f.id) left join partner_sug_reg_count src on (src.partner_id = f.id) left join message res on m.message_header_id = res.parent_id left join partner_group g1 on f.group1 = g1.id left join partner_group g2 on f.group2 = g2.id left join partner_group g3 on f.group3 = g3.id where f.client_id = 1000