Export email activity

export-emails.sql

-- Generates a CSV export of all user-logged activities.
-- Adjust Company ID and activity time as needed.

-- If running against a follower DB, selecting into a temporary
-- table won't work. Instead, remove all the newlines to fit
-- the entire query into one line, and use it directly in the
-- \copy command, e.g.:
--
-- \copy (WITH entity_types ... SELECT * FROM tmp_results_1) TO 'filename.csv' WITH CSV HEADER

-- BEGIN;

WITH entity_types (type_id, name) AS (VALUES 
  ( 3, 'Person'),
  ( 5, 'Project'),
  ( 6, 'Task'),
  ( 7, 'Opportunity'),
  (19, 'Company'),
  (21, 'Lead')
),
tmp_results_1 AS (
  SELECT
    correspondences.sent_at "Date",
    coalesce(source_entity_types.name, activity_logs.source_type::varchar) "Record Type",
    coalesce(activity_logs.source_id) "Record ID",
    coalesce(
      contacts.name,
      projects.name,
      tasks.name,
      deals.name,
      organizations.name,
      leads.name,
      (activity_logs.source_type::varchar || ' ' || activity_logs.source_id::varchar)
    ) "Record Name",
    correspondences.subject "Subject",
    senders.value_1 || ' <' || senders.value_2 || '>' "Sender",
    string_agg(recipients.value_1 || ' <' || recipients.value_2 || '>', ',') "Recipients",
    string_agg(cc_recipients.value_1 || ' <' || cc_recipients.value_2 || '>', ',') "CC Recipients",
    regexp_replace(
      substring(correspondences.text_data FROM 1 FOR 10),
      E'[\\n\\r]+',
      ' ',
      'g'
    ) || '...' "Body"
  FROM
    activity_logs
  LEFT OUTER JOIN
    entity_types "source_entity_types"
    ON activity_logs.source_type = source_entity_types.type_id
  LEFT OUTER JOIN
    contacts
    ON activity_logs.company_id = contacts.company_id
    AND activity_logs.source_type = 3
    AND activity_logs.source_id = contacts.id
  LEFT OUTER JOIN
    projects
    ON activity_logs.company_id = projects.company_id
    AND activity_logs.source_type = 5
    AND activity_logs.source_id = projects.id
  LEFT OUTER JOIN
    tasks
    ON activity_logs.company_id = tasks.company_id
    AND activity_logs.source_type = 6
    AND activity_logs.source_id = tasks.id
  LEFT OUTER JOIN
    deals
    ON activity_logs.company_id = deals.company_id
    AND activity_logs.source_type = 7
    AND activity_logs.source_id = deals.id
  LEFT OUTER JOIN
    organizations
    ON activity_logs.company_id = organizations.company_id
    AND activity_logs.source_type = 19
    AND activity_logs.source_id = organizations.id
  LEFT OUTER JOIN
    leads
    ON activity_logs.company_id = leads.company_id
    AND activity_logs.source_type = 21
    AND activity_logs.source_id = leads.id
  LEFT OUTER JOIN
    correspondences
    ON activity_logs.company_id = correspondences.company_id
    AND activity_logs.target_type = 8
    AND activity_logs.target_id = correspondences.id
  LEFT OUTER JOIN
    typed_properties "senders"
    ON correspondences.company_id = senders.company_id
    AND 'Correspondence' = senders.property_owner_type
    AND correspondences.id = senders.property_owner_id
    AND 4 = senders.data_type
    AND 'SENDER' = senders.category
  LEFT OUTER JOIN
    typed_properties "recipients"
    ON correspondences.company_id = recipients.company_id
    AND 'Correspondence' = recipients.property_owner_type
    AND correspondences.id = recipients.property_owner_id
    AND 4 = recipients.data_type
    AND 'RECIPIENT' = recipients.category
  LEFT OUTER JOIN
    typed_properties "cc_recipients"
    ON correspondences.company_id = cc_recipients.company_id
    AND 'Correspondence' = cc_recipients.property_owner_type
    AND correspondences.id = cc_recipients.property_owner_id
    AND 4 = cc_recipients.data_type
    AND 'CC_RECIPIENT' = cc_recipients.category
  WHERE
    activity_logs.company_id = 236150
    AND coalesce(activity_logs.updated_at_override, activity_logs.created_at) >= '2017-01-01 12:00:00'::timestamptz
    AND (
      activity_logs.activity_type = 6  -- ACTIVITY_TYPE_LINK_ENTITY
      AND activity_logs.source_type IN (3, 5, 6, 7, 19, 21)
      AND activity_logs.target_type = 8 -- TYPE_CORRESPONDENCE
    )
    AND coalesce(contacts.id, projects.id, tasks.id, deals.id, organizations.id, leads.id) IS NOT NULL
  GROUP BY
    correspondences.sent_at,
    coalesce(source_entity_types.name, activity_logs.source_type::varchar),
    coalesce(activity_logs.source_id),
    coalesce(
      contacts.name,
      projects.name,
      tasks.name,
      deals.name,
      organizations.name,
      leads.name,
      (activity_logs.source_type::varchar || ' ' || activity_logs.source_id::varchar)
    ),
    correspondences.subject,
    senders.value_1 || ' <' || senders.value_2 || '>',
    regexp_replace(
      substring(correspondences.text_data FROM 1 FOR 10),
      E'[\\n\\r]+',
      ' ',
      'g'
    ) || '...'
  ORDER BY
    correspondences.sent_at ASC
)
SELECT
  *
-- INTO TEMPORARY TABLE
--   tmp_results_2
FROM
  tmp_results_1;