Holistics-postgres public.customer users materialized view
DISPLAYTITLE:Holistics-postgres public.customer_users materialized view
Query
SELECT
`` utcs.customer_id AS customer_id,
`` cs.company AS customer_name,
`` CASE
`` WHEN cs.id IS NULL THEN 'Unexisting'
`` ELSE 'Existing'
`` END as customer_status,
`` utcs.wp_user_id AS wp_user_id,
`` uutc.unguess_wp_user_id as unguess_wp_user_id,
`` p.id AS profile_id,
`` CASE
`` WHEN ( p.email IS NULL ) OR ( p.email = `''` ) THEN 'Unexisting'
`` ELSE 'Existing'
`` END AS profile_status,
`` CASE
`` WHEN utcs.customer_id IN (2, 7, 38, 91, 115, 158, 160, 223, 234, 254, 255, 256, 267, 272, 273) THEN 'Internal'
`` WHEN ( (u.user_email LIKE '%@app-quality.com') AND (utcs.customer_id NOT IN (2, 7, 38, 91, 115, 158, 160, 223, 234, 254, 255, 256, 267, 272, 273)) ) THEN 'Old'
`` WHEN u.ID IS NULL THEN 'Unexisting'
`` WHEN u.user_email IS NULL THEN 'Undetermined'
`` ELSE 'New'
`` END AS user_type,
`` CASE
`` WHEN cai.status = '1' THEN 'Approved'
`` WHEN cai.status = '0' THEN 'Pending'
`` WHEN cai.status = '-1' THEN 'Rejected'
`` ELSE NULL
`` END AS invitation_status,
`` p.email AS profile_email,
`` u.user_email AS user_email,
`` CONCAT ( p.surname, ' ', p.name ) AS profile_full_name,
`` u.display_name AS user_display_name,
`` p.creation_time AS profile_creation_date,
`` u.user_registered AS user_creation_date,
`` p.last_activity AS profile_last_activity
`` --user_last_activity? Se serve si possono esaminare le "tracce lasciate" (ask @cannarocks)
FROM
`` wp_appq_user_to_customer utcs
`` LEFT JOIN wp_appq_customer cs ON utcs.customer_id = cs.id
`` LEFT JOIN wp_appq_evd_profile p ON utcs.wp_user_id = p.wp_user_id
`` LEFT JOIN wp_users u ON utcs.wp_user_id = u.ID
`` LEFT JOIN wp_appq_customer_account_invitations cai ON p.id = cai.tester_id
`` LEFT JOIN wp_unguess_user_to_customer uutc ON utcs.wp_user_id = uutc.tryber_wp_user_id
ORDER BY
`` customer_id ASC,
`` invitation_status ASC,
`` profile_status DESC,
`` user_type ASC,
`` profile_full_name ASC