Built-In Reports Reference¶

SELECT COUNT(distinct co.id)
FROM core_order co
JOIN core_action ca ON (co.action_id = ca.id)
LEFT JOIN core_orderrecurring cor ON (co.id = cor.order_id)
WHERE co.status = 'completed'
  AND cor.order_id is null
  AND mailing_id = {mailing_id}
  AND page_id = {page_id};

SELECT coalesce(sum(ct.amount),0)
FROM core_transaction ct
JOIN core_orderrecurring cor using (order_id)
JOIN core_action ca ON (ca.id = cor.action_id)
WHERE type = 'sale'
  AND success = 1
  AND cor.status = 'active'
  AND ca.mailing_id = {mailing_id}
  AND ca.page_id = {page_id}

SELECT COUNT(distinct user_id)
FROM core_click
JOIN core_clickurl ON (core_click.clickurl_id = core_clickurl.id)
WHERE mailing_id = {mailing_id}
  AND page_id = {page_id}

SELECT COUNT(distinct cor.id)
FROM core_orderrecurring cor
JOIN core_order co ON (cor.order_id = co.id)
JOIN core_action ca ON (cor.action_id = ca.id)
WHERE co.status = 'completed'
  AND cor.status = 'active'
  AND ca.status = 'complete'
  AND mailing_id = {mailing_id}
  AND page_id = {page_id}

SELECT coalesce(sum(ct.amount),0)
FROM core_transaction ct
JOIN core_order co ON (co.id = ct.order_id)
JOIN core_action ca ON (ca.id = co.action_id)
LEFT JOIN core_orderrecurring cor ON (co.id = cor.order_id)
WHERE type = 'sale'
  AND success = 1
  AND co.status = 'completed'
  AND cor.order_id is null
  AND ca.mailing_id = {mailing_id}
  AND ca.page_id = {page_id}

SELECT COUNT(core_action.id)
FROM core_action
JOIN core_page ON (core_action.page_id = core_page.id)
WHERE core_action.created_at > {month}
  AND core_action.created_at < date_add({month},interval 1 month)
  AND core_page.real_actions
  AND core_action.status = 'complete'

SELECT COUNT(distinct user_id)
FROM core_subscriptionhistory
WHERE change_id = 3
  AND created_at > {month}
  AND created_at < date_add({month},interval 1 month)

SELECT COUNT(id)
FROM core_usermailing
WHERE created_at > {month}
  AND created_at < date_add({month},interval 1 month)

SELECT COUNT(distinct sh.user_id)
FROM (
  SELECT user_id, list_id, max(id) id
  FROM core_subscriptionhistory
  WHERE created_at < date_add({month}, interval 1 month)
  GROUP BY 1, 2) sh
JOIN core_subscriptionhistory using(id)
WHERE change_id IN (1,2,7)

SELECT COUNT(id)
FROM core_mailing
WHERE started_at > {month}
  AND started_at < date_add({month},interval 1 month)
  AND status = 'completed'

SELECT COUNT(*)
FROM (
  SELECT user_id, min(id) id
  FROM core_subscriptionhistory
  WHERE change_id IN (1,2,3)
  GROUP BY 1) sh
JOIN core_subscriptionhistory using(id)
WHERE created_at > {month}
  AND created_at < date_add({month},interval 1 month)

SELECT coalesce(sum(ct.amount),0)
FROM core_transaction ct
JOIN core_order co ON (co.id = ct.order_id)
LEFT JOIN core_orderrecurring cor ON (co.id = cor.order_id)
WHERE type = 'sale'
  AND success = 1
  AND co.status = 'completed'
  AND cor.order_id is null
  AND co.created_at > {month}
  AND co.created_at < date_add({month}, interval 1 month)

SELECT COUNT(distinct user_id)
FROM core_subscriptionhistory
WHERE change_id IN (4,5,6)
  AND created_at > {month}
  AND created_at < date_add({month},interval 1 month)

SELECT COUNT(distinct co.id)
FROM core_order co
JOIN core_action ca ON (co.action_id = ca.id)
LEFT JOIN core_orderrecurring cor ON (co.id = cor.order_id)
WHERE co.status = 'completed'
  AND cor.order_id is null
  AND page_id = {page_id}

SELECT COUNT(distinct co.id)
FROM core_order co
JOIN core_action ca ON (co.action_id = ca.id)
LEFT JOIN core_orderrecurring cor ON (co.id = cor.order_id)
WHERE co.status = 'completed'
  AND cor.order_id is null
  AND ca.source = {source}
  AND ca.page_id = {page_id}

SELECT coalesce(sum(ct.amount),0)
FROM core_transaction ct
JOIN core_orderrecurring cor using (order_id)
JOIN core_action ca ON (ca.id = cor.action_id)
WHERE type = 'sale'
  AND success = 1
  AND cor.status = 'active'
  AND ca.page_id = {page_id}

SELECT coalesce(sum(ct.amount),0)
FROM core_transaction ct
JOIN core_orderrecurring cor using (order_id)
JOIN core_action ca ON (ca.id = cor.action_id)
WHERE type = 'sale'
  AND success = 1
  AND cor.status = 'active'
  AND ca.source = {source}
  AND ca.page_id = {page_id}

SELECT COUNT(distinct cor.id)
FROM core_orderrecurring cor
JOIN core_order co ON (cor.order_id = co.id)
JOIN core_action ca ON (cor.action_id = ca.id)
WHERE co.status = 'completed'
  AND cor.status = 'active'
  AND ca.status = 'complete'
  AND page_id = {page_id}

SELECT COUNT(distinct cor.id)
FROM core_orderrecurring cor
JOIN core_order co ON (cor.order_id = co.id)
JOIN core_action ca ON (cor.action_id = ca.id)
WHERE co.status = 'completed'
  AND cor.status = 'active'
  AND ca.status = 'complete'
  AND ca.source = {source}
  AND page_id = {page_id}

SELECT coalesce(sum(ct.amount),0)
FROM core_transaction ct
JOIN core_order co ON (co.id = ct.order_id)
JOIN core_action ca ON (ca.id = co.action_id)
LEFT JOIN core_orderrecurring cor ON (co.id = cor.order_id)
WHERE type = 'sale'
  AND success = 1
  AND co.status = 'completed'
  AND cor.order_id is null
  AND ca.page_id = {page_id}

SELECT coalesce(sum(ct.amount),0)
FROM core_transaction ct
JOIN core_order co ON (co.id = ct.order_id)
JOIN core_action ca ON (ca.id = co.action_id)
LEFT JOIN core_orderrecurring cor ON (co.id = cor.order_id)
WHERE type = 'sale'
  AND success = 1
  AND co.status = 'completed'
  AND cor.order_id is null
  AND ca.source = {source}
  AND ca.page_id = {page_id}

{% if partial_run %}
SELECT COUNT(DISTINCT a.user_id)
FROM core_action a
LEFT join core_action old ON (old.page_id    = a.page_id
                          AND old.user_id    = a.user_id
                          AND old.created_at < a.created_at
                          AND old.status     = 'complete')
WHERE a.page_id  = {page_id}
AND a.created_at > {last_run}
AND a.status     = 'complete'
AND old.user_id  is null;
{% else %}
SELECT COUNT(DISTINCT user_id)
FROM core_action a
WHERE page_id = {page_id}
AND status    = 'complete';
{% endif %}

SET @Jurisdiction = (SELECT REPLACE(LOWER({Jurisdiction}),' ','_'));

{% if Jurisdiction|spaces_to_underscores == 'senate' %}
  SELECT
    t.seat AS 'Jurisdiction',
    CONCAT(t.title,' ',t.official_full) AS 'Name',
    a.count AS 'Action Takers'
  FROM core_target t
  JOIN (
    SELECT
      u.state,
      COUNT(DISTINCT u.id) AS count
    FROM core_action a
    JOIN core_user u ON u.id = a.user_id
    WHERE a.page_id = {page_id}
    AND a.status = 'complete'
    GROUP BY 1
    ORDER BY 1
  ) AS a ON t.state = a.state
  WHERE t.type = 'senate' AND t.hidden = 0
  ORDER BY 1;
{% else %}
  SELECT
    a.jurisdiction AS 'Jurisdiction',
    CONCAT(t.title,' ',t.first,' ',t.last) AS 'Name',
    a.count AS 'Action Takers'
  FROM core_target t
  JOIN (
    SELECT
    {% if Jurisdiction|spaces_to_underscores == 'state_house' %}
      l.us_state_district AS jurisdiction,
    {% elif Jurisdiction|spaces_to_underscores == 'state_senate' %}
      l.us_state_senate AS jurisdiction,
    {% else %}
      l.us_district AS jurisdiction,
    {% endif %}
      COUNT(DISTINCT a.user_id) AS count
    FROM core_action a
    JOIN core_location l ON a.user_id = l.user_id
    WHERE a.page_id = {page_id}
    AND a.status = 'complete'
    GROUP BY 1
    ORDER BY 1
  ) AS a ON a.jurisdiction =
    {% if Jurisdiction|spaces_to_underscores == 'state_senate' %}
      t.seat
    {% else %}
      t.us_district
    {% endif %}
  WHERE t.type = @Jurisdiction AND t.hidden = 0
  ORDER BY 1;
{% endif %}

SELECT COUNT(*)
FROM core_action
WHERE source <> 'import'
{% if partial_run %}
AND created_at BETWEEN {last_run} AND {now}
{% endif %}
;

select
    previous_action_months_ago,
    number_of_users,
    took_action_this_month,
    format( 100 * took_action_this_month / number_of_users, 1 ) as action_pct
from (
    select
        floor( datediff( now(), ( select max(created_at) from core_action where core_action.user_id = core_user.id and created_at < now() - interval 30 day ) ) / 30 ) as previous_action_months_ago,
        count(*) as number_of_users,
        sum( if( exists ( select * from core_action where core_action.user_id = core_user.id and created_at >= now() - interval 30 day ), 1, 0 ) ) as took_action_this_month
    from core_user
    where core_user.subscription_status in ( 'subscribed' )
    group by 1
) as activity_rates

SELECT distinct es.user_id
FROM events_event e
JOIN events_eventsignup es
  ON es.event_id=e.id
JOIN events_campaign c
  ON e.campaign_id = c.id
WHERE c.name = {campaign_name}
  AND e.status='active'
  AND es.status='active'
  AND es.role='attendee'
  AND host_is_confirmed=1
  AND e.starts_at >= now() - interval 10 hour;

SELECT core_product.name AS 'product name',
  core_product.price AS 'price',
  SUM(core_order_detail.quantity) AS 'qty',
  SUM(core_order_detail.amount) AS 'total',
  COUNT(DISTINCT core_order.user_id) AS 'users'
FROM core_order
JOIN core_order_detail
  ON core_order_detail.order_id=core_order.id
JOIN core_product
  ON core_product.id=core_order_detail.product_id
WHERE core_order.status = 'completed'
  AND DATE_FORMAT(core_order_detail.created_at, '%Y-%m-%d') >= {{ 1. start_date YYYY-MM-DD }}
  AND DATE_FORMAT(core_order_detail.created_at, '%Y-%m-%d') <= {{ 2. end_date YYYY-MM-DD }}
  AND core_order_detail.product_id={{ product_id }}
GROUP BY core_product.id;

SELECT ct.state, COUNT(distinct cat.callaction_id)
FROM core_action ca
JOIN core_callaction_targeted cat ON (ca.id = cat.callaction_id)
JOIN core_target ct ON (cat.target_id = ct.id)
WHERE page_id = {page_id}
  AND ca.status = 'complete'
GROUP BY ct.state

SELECT
  CONCAT(ct.title,' ',ct.first,' ',ct.last) target, ct.type,
  IF(ct.us_district='',ct.seat,ct.us_district) seat,
    ct.title, ct.long_title, ct.fax, ct.email, ct.created_at,
  COUNT(distinct cat.callaction_id) actions,
  COUNT(distinct cac.callaction_id) checked
FROM core_action ca
JOIN core_callaction_targeted cat
  ON (ca.id = cat.callaction_id)
JOIN core_target ct
  ON (cat.target_id = ct.id)
LEFT JOIN core_callaction_checked cac
  ON (ca.id = cac.callaction_id AND ct.id = cac.target_id)
WHERE page_id = {page_id}
  AND ca.status = 'complete'
GROUP BY ct.id

SELECT u.id, u.first_name, u.last_name,
  u.address1, u.address2,
  u.city, u.state, u.zip,
  COALESCE(occ.value) AS occupation,
  COALESCE(emp.value) AS employer,
  SUM(od.amount) AS total
FROM core_user u
JOIN core_order o ON (u.id = o.user_id)
JOIN core_order_detail od ON (o.id = od.order_id)
JOIN core_transaction t ON (t.order_id = od.order_id AND t.success = 1
  AND t.type = 'sale')
LEFT JOIN core_actionfield occ ON (o.action_id = occ.parent_id AND occ.name = 'occupation')
LEFT JOIN core_actionfield emp ON (o.action_id = emp.parent_id AND emp.name = 'employer')
WHERE o.status = 'completed' AND od.candidate_id = {{candidate_id}}
GROUP BY u.id

SELECT cc.user_id
FROM core_click cc
JOIN core_clickurl ccu ON (cc.clickurl_id=ccu.id)
LEFT JOIN core_action ca
  ON (cc.user_id=ca.user_id
  AND ccu.page_id=ca.page_id
  AND ca.status='complete')
WHERE ccu.page_id = {page_id}
  AND cc.user_id is not null
  AND ca.id is null

SELECT COUNT(*) FROM (
  SELECT click.user_id
  FROM core_click click
  JOIN core_clickurl url ON (click.clickurl_id = url.id)
  WHERE mailing_id = {mailing_id} AND
        NOT url.url LIKE '%%/cms/unsubscribe/%%'
  GROUP BY click.user_id
{% if partial_run %}
  HAVING MIN(click.created_at) > {last_run}
{% endif %}
) recent_clicks;

SELECT ca.user_id, af.value
FROM core_actionfield af
JOIN core_action ca ON (ca.id=af.parent_id)
WHERE af.name='comment'
  AND length(af.value) > 0 AND page_id = {page_id}

SELECT DISTINCT tms.user_id
FROM core_transactionalmailingsent tms
JOIN core_transactionalmailingclick tmc
  ON (tms.id = tmc.transactional_mailing_sent_id)
JOIN core_transactionalmailing tm
  ON (tm.id = tms.transactional_mailing_id)
WHERE tm.page_id = {{ page_id }} AND tm.type = 'confirmation' AND tms.user_id IS NOT NULL;

SELECT tms.user_id
FROM core_transactionalmailingsent tms
JOIN core_transactionalmailingopen tmo
  ON (tms.id = tmo.transactional_mailing_sent_id)
JOIN core_transactionalmailing tm
  ON (tm.id = tms.transactional_mailing_id)
WHERE tm.page_id = {{ page_id }} AND tm.type = 'confirmation' AND tms.user_id IS NOT NULL;

SELECT subject,
       type,
       sent,
       opens,
       opens / sent * 100 as open_pct,
       clicks,
       clicks / opens * 100 as clicks_per_open,
       actions,
       actions / opens * 100 as actions_per_open,
       NTL as new_to_list
FROM (
        SELECT tm.subject,
           tm.type,
           COUNT(DISTINCT tms.id) as sent,
           COUNT(DISTINCT tmo.transactional_mailing_sent_id) as opens,
           COUNT(DISTINCT tmc.transactional_mailing_sent_id) as clicks,
           COUNT(DISTINCT tma.id) as actions,
           COUNT(DISTINCT(tma_ntl.id)) as NTL
        FROM core_transactionalmailing tm
        JOIN core_transactionalmailingsent tms
          ON (tm.id = tms.transactional_mailing_id)
        LEFT JOIN core_transactionalmailingopen tmo
          ON (tms.id = tmo.transactional_mailing_sent_id)
        LEFT JOIN core_transactionalmailingclick tmc
          ON (tms.id = tmc.transactional_mailing_sent_id)
        LEFT JOIN core_transactionalmailingaction tma
          ON (tms.id = tma.transactional_mailing_sent_id)
        LEFT JOIN core_action tma_ntl
          ON (tma_ntl.id = tma.action_id AND tma_ntl.subscribed_user = 1)

        WHERE tm.page_id = {{ page_id }} AND tm.type = 'confirmation' GROUP BY 1, 2
) stats;

SELECT name AS custom_action_field, count(*) AS action_count
FROM core_actionfield
JOIN core_action ON core_actionfield.parent_id = core_action.id
WHERE page_id in ({ page_id })
GROUP BY 1

SELECT COALESCE(SUM(total), 0)
FROM core_order
WHERE status = 'completed'
{% if partial_run %}
AND created_at BETWEEN {last_run} AND {now}
{% endif %}
;

SELECT COALESCE(SUM(t.amount), 0)
FROM core_order o
JOIN  core_transaction t ON (o.id=t.order_id)
WHERE o.status = 'completed'
  AND t.type = 'sale'
  AND t.success = 1
{% if partial_run %}
AND t.created_at BETWEEN {last_run} AND {now}
{% endif %}

SELECT e.city City, COUNT(distinct e.id) Events
FROM events_event e
JOIN events_campaign c
  ON (e.campaign_id=c.id)
WHERE e.country = 'United States'
  AND e.city <> ''
  AND e.city is not null
  AND c.name = {campaign_name}
  AND e.status = 'active'
GROUP BY 1
ORDER BY e.city desc limit 50

SELECT date(ee.starts_at) 'date',
  COUNT(distinct ee.id) 'events',
  sum(ee.attendee_count) 'signup total',
  coalesce(sum(att.total),0) 'attendance'
FROM events_event ee
JOIN events_campaign ec ON (ee.campaign_id = ec.id)
LEFT JOIN (
  SELECT ees.event_id 'event_id', sum(ees.attended) 'total'
  FROM events_eventsignup ees
  WHERE ees.status = 'active'
  GROUP BY 1) att
  ON (ee.id = att.event_id)
WHERE ec.name = {campaign_name}
  AND ee.status = 'active'
GROUP BY 1

SELECT COUNT(distinct e.id)
FROM events_event e
JOIN events_campaign c ON (e.campaign_id = c.id)
WHERE c.name = {campaign_name}
  AND e.status = 'active'
  AND attendee_count between {min_signups} AND {max_signups};

SELECT state State, COUNT(*) Events
FROM events_event e
JOIN events_campaign c ON (e.campaign_id=c.id)
WHERE c.name = {campaign_name}
  AND e.status = 'active'
  GROUP BY 1;

SELECT e.id AS "ID",
e.title AS "Title",
e.starts_at AS "Date",
e.attendee_count AS "Attendees",
e.max_attendees AS "Max Allowed",
e.max_attendees - e.attendee_count AS "Available spots"
FROM events_event e
JOIN events_campaign c ON (c.id = e.campaign_id) and c.name IN ( {{ campaign }} )
WHERE e.attendee_count < {{ rsvps }}
AND e.max_attendees - e.attendee_count >= {{ num }}
AND e.status = 'active'
AND e.starts_at >= NOW()
ORDER BY 1;

{% required_parameter "campaign" label "Event Campaign" order "1" %}
{% required_parameter "rsvps" label "RSVP count less than" order "2" default "5" type "number" hint "Return events with fewer RSVPs than this number" %}
{% required_parameter "num" label "Number of open spots left" order "3" default "5" type "number" hint "Return events with open spots of at least this number" %}

SELECT e.id AS "ID",
e.title AS "Title",
e.starts_at AS "Date",
e.attendee_count AS "Attendees",
e.max_attendees AS "Max Allowed",
e.max_attendees - e.attendee_count AS "Available spots"
FROM events_event e
JOIN events_campaign c ON (c.id = e.campaign_id) and c.name IN ( {{ campaign }} )
WHERE e.max_attendees - e.attendee_count <= {{ num }}
AND e.status = 'active'
AND e.starts_at >= NOW()
ORDER BY 1;

{% required_parameter "campaign" label "Event Campaign" order "1" %}
{% required_parameter "num" label "Number of open spots left" default "5" type "number" hint "Return events with open spots of at least this number" %}

SELECT distinct es.user_id
FROM events_event e
JOIN events_eventsignup es
  ON es.event_id=e.id
JOIN events_campaign c
  ON e.campaign_id = c.id
WHERE c.name = {campaign_name}
  AND e.status='active'
  AND es.status='active'
  AND es.role='host'
  AND host_is_confirmed=1
  AND e.starts_at >= now() - interval 10 hour;

SELECT AVG(n) AS 'Average letters per user'
FROM (
  SELECT ca.user_id, COUNT(*) AS 'n'
  FROM core_page AS 'cp'
  JOIN core_ltepage AS 'cle'
    ON (cp.id = cle.page_ptr_id)
  JOIN core_action AS 'ca'
    ON (cp.id = ca.page_id)
  JOIN core_lteaction AS 'cla'
    ON (ca.id = cla.action_ptr_id)
  JOIN core_user AS 'cu'
    ON (ca.user_id = cu.id)
  WHERE cp.id = {lte_page_id}
  GROUP BY ca.user_id
  ) AS 't';

SELECT cu.state, COUNT(ca.id) AS 'Letters per state', COUNT(DISTINCT cu.id) AS 'Users per state'
FROM core_page AS 'cp'
JOIN core_ltepage AS 'cle'
  ON (cp.id = cle.page_ptr_id)
JOIN core_action AS 'ca'
  ON (cp.id = ca.page_id)
JOIN core_lteaction AS 'cla'
  ON (ca.id = cla.action_ptr_id)
JOIN core_user AS 'cu'
  ON (ca.user_id = cu.id)
WHERE cp.id = {lte_page_id}
GROUP BY cu.state;

SELECT cmt.id, cmt.name, COUNT(*) AS 'Letters per paper'
FROM core_page AS 'cp'
JOIN core_ltepage AS 'cle'
  ON (cp.id = cle.page_ptr_id)
JOIN core_action AS 'ca'
  ON (cp.id = ca.page_id)
JOIN core_lteaction AS 'cla'
  ON (ca.id = cla.action_ptr_id)
JOIN core_mediatarget AS 'cmt'
  ON (cla.target_id = cmt.id)
WHERE cp.id = {lte_page_id}
GROUP BY cmt.id;

SELECT cmt.levelcode, COUNT(*) AS 'Letters per paper type'
FROM core_page AS 'cp'
JOIN core_ltepage AS 'cle'
  ON (cp.id = cle.page_ptr_id)
JOIN core_action AS 'ca'
  ON (cp.id = ca.page_id)
JOIN core_lteaction AS 'cla'
  ON (ca.id = cla.action_ptr_id)
JOIN core_mediatarget AS 'cmt'
  ON (cla.target_id = cmt.id)
WHERE cp.id = {lte_page_id}
GROUP BY cmt.levelcode;

SELECT COUNT(DISTINCT ca.id) AS 'Total letters submitted'
FROM core_page AS 'cp'
JOIN core_ltepage AS 'cle'
  ON (cp.id = cle.page_ptr_id)
JOIN core_action AS 'ca'
  ON (cp.id = ca.page_id)
JOIN core_lteaction AS 'cla'
  ON (ca.id = cla.action_ptr_id)
JOIN core_mediatarget AS `cmt`
  ON (cla.target_id = cmt.id)
WHERE cp.id = {lte_page_id};

SELECT COUNT(DISTINCT cu.id)
  AS 'Total distinct users'
FROM core_page AS 'cp'
JOIN core_ltepage
  AS 'cle'
  ON (cp.id = cle.page_ptr_id)
JOIN core_action
  AS 'ca'
  ON (cp.id = ca.page_id)
JOIN core_lteaction
  AS 'cla'
  ON (ca.id = cla.action_ptr_id)
JOIN core_user
  AS 'cu'
  ON (ca.user_id = cu.id)
WHERE cp.id = {lte_page_id};

SELECT COUNT(DISTINCT core_user.id) FROM core_user JOIN core_subscription
ON (core_user.id = core_subscription.user_id)
{% if partial_run %}
WHERE core_user.created_at BETWEEN {last_run} AND {now}
{% endif %}
;

SELECT COUNT(*) FROM (
  SELECT user_id
  FROM core_open
  WHERE mailing_id = {mailing_id}
  GROUP BY user_id
{% if partial_run %}
  HAVING MIN(created_at) > {last_run}
{% endif %}
) recent_opens;

SET @tz = {% if Time_Zone %}{Time_Zone}{% else %}'US/Eastern'{% endif %};

SELECT
  GROUP_CONCAT(m.id ORDER BY m.id)
FROM core_mailing m
WHERE m.progress > 0
  AND m.started_at >= DATE(CONVERT_TZ(NOW(),'GMT',@tz)) - INTERVAL {Days_Past} DAY
  AND recurring_schedule_id IS NULL;

SET SQL_BIG_SELECTS=1;
SET SESSION group_concat_max_len = 100000;
SET @tz = {% if Time_Zone %}{ Time_Zone }{% else %}'US/Eastern'{% endif %};
SET @type = { Type };

SELECT
  GROUP_CONCAT(rate ORDER BY number SEPARATOR '|')
FROM (
  SELECT
    number,
    GROUP_CONCAT(rate ORDER BY id) AS rate
  FROM (
    SELECT
      m.id,
      n.number,
      IF(n.number = 0, 0.00, FORMAT(100*COUNT(DISTINCT c.user_id) / m.progress,2)) AS rate
    FROM numeric_9999 n
    JOIN core_mailing m ON m.progress > 0
        AND m.started_at >= DATE(CONVERT_TZ(NOW(),'GMT',@tz)) - INTERVAL {Days_Past} DAY
        AND recurring_schedule_id IS NULL
    {% if Type == 'opens' %}
      LEFT JOIN core_open c ON c.mailing_id = m.id AND c.created_at < m.started_at + INTERVAL 24 HOUR
          AND HOUR(TIMEDIFF(c.created_at, m.started_at)) < n.number
    {% else %}
      LEFT JOIN core_action c ON c.mailing_id = m.id AND c.created_at < m.started_at + INTERVAL 24 HOUR
          AND HOUR(TIMEDIFF(c.created_at, m.started_at)) < n.number
          AND c.page_id NOT IN (SELECT id FROM core_page WHERE real_actions = 0)
    {% endif %}
    WHERE n.number <= 24
    GROUP BY 1, 2
    ORDER BY 1, 2
  ) y
  GROUP BY number
  ORDER BY number
) z;

SET @tz = {% if Time_Zone %}{Time_Zone}{% else %}'US/Eastern'{% endif %};

SELECT x.id_field AS 'ID',
  IFNULL(DATE_FORMAT(CONVERT_TZ(cm.started_at,'GMT',@tz),'%Y-%m-%d %h:%i %p'),'z') AS 'Sent At',
  IFNULL(ms.text,'') AS 'Subject',
  IFNULL(cm.notes,'') AS 'Notes',
  IFNULL((SELECT GROUP_CONCAT(t.name)
    FROM core_mailing_tags mt
    JOIN core_tag t ON t.id = mt.tag_id
    WHERE mt.mailing_id = cm.id),'') AS 'Tags',
  x.sent AS 'Sent',
  x.open_rate AS 'Open %',
  x.cpo AS 'Clicks / Open',
  x.upo AS 'Unsubs / Open',
  x.actions AS 'Action Takers',
  x.act_rate AS 'Act%',
  x.paid AS '$',
  x.paid_mailed AS '$ / 100 Mailed'
FROM (
  SELECT
    id,
    IFNULL(CONCAT('/mailings/reports/',id,' ',id),'TOTAL') AS 'id_field',
    FORMAT(SUM(sent),0) AS 'sent',
    FORMAT(100*SUM(opens)/SUM(sent),2) AS open_rate,
    IFNULL(FORMAT(100*SUM(clicks)/SUM(opens),2),'No opens') AS cpo,
    IFNULL(FORMAT(100*SUM(unsubs)/SUM(opens),2),'No opens') AS upo,
      FORMAT(SUM(actions), 0) AS actions,
      FORMAT(100 * SUM(actions) / SUM(sent), 2) AS act_rate,
      FORMAT(SUM(paid),2) AS paid,
      FORMAT(SUM(paid) / (SUM(sent)/100), 2) AS paid_mailed
  FROM (
    SELECT m.id, m.progress AS sent,
      IFNULL((SELECT GROUP_CONCAT(t.name)
        FROM core_mailing_tags mt
        JOIN core_tag t ON t.id = mt.tag_id
        WHERE mt.mailing_id = m.id),'') AS tags,
      COALESCE((SELECT COUNT(DISTINCT user_id)
        FROM core_open
        WHERE mailing_id = m.id
        AND created_at < m.started_at + INTERVAL {Hours_Since_Send} HOUR),0) AS opens,
      COALESCE((SELECT COUNT(DISTINCT c.user_id)
        FROM core_click c
        JOIN core_clickurl cu ON cu.id = c.clickurl_id
        JOIN core_page p on p.id = cu.page_id
        WHERE c.mailing_id = m.id
        AND p.real_actions = 1
        AND c.created_at < m.started_at + INTERVAL {Hours_Since_Send} HOUR),0) AS clicks,
      COALESCE((SELECT COUNT(DISTINCT a.user_id)
        FROM core_unsubscribeaction u
        JOIN core_action a ON a.id = u.action_ptr_id
        WHERE a.mailing_id = m.id
        AND a.created_at < m.started_at + INTERVAL {Hours_Since_Send} HOUR),0) AS unsubs,
      COALESCE((SELECT COUNT(DISTINCT a.user_id)
        FROM core_action a
        JOIN core_page p ON p.id = a.page_id
        WHERE a.mailing_id = m.id
        AND p.real_actions = 1
        AND a.created_at < m.started_at + INTERVAL {Hours_Since_Send} HOUR),0) AS actions,
      COALESCE((SELECT SUM(t.amount_converted) AS paid
            FROM core_order o
            JOIN core_action a ON a.id = o.action_id
            JOIN core_transaction t ON t.order_id = o.id AND t.type = 'sale' AND t.status = 'completed' AND t.success = 1
            JOIN core_page p ON p.id = a.page_id
            WHERE a.status = 'complete'
            AND a.mailing_id = m.id
        AND p.real_actions = 1
        AND t.created_at < m.started_at + INTERVAL {Hours_Since_Send} HOUR),0) AS paid
    FROM core_mailing m
    WHERE m.progress > 0
    AND m.started_at >= DATE(CONVERT_TZ(NOW(),'GMT',@tz)) - INTERVAL {Days_Past} DAY
    AND recurring_schedule_id IS NULL
    GROUP BY m.id
    ORDER BY m.started_at
  ) mailings
  GROUP BY 1 WITH ROLLUP
) x
LEFT JOIN core_mailing cm ON cm.id = x.id
LEFT JOIN core_mailingsubject ms ON ms.mailing_id = cm.id
GROUP BY 1
ORDER BY 2,1;

SELECT COUNT(*) FROM core_usermailing WHERE mailing_id={mailing_id}

select coalesce(sum( (   select count( distinct user_id )
      from core_action
      left join core_unsubscribeaction u  on (u.action_ptr_id=core_action.id)
      where core_action.mailing_id = core_mailing.id
      and core_action.status = 'complete'
      and u.action_ptr_id is null  )), 0) as actions
from core_mailing
where left(core_mailing.started_at, 7) = left({{date}}, 7);

select count(distinct core_action.user_id, core_mailing.id) as bounces
from core_action
join core_unsubscribeaction us on (us.action_ptr_id=core_action.id)
join core_subscriptionhistory sh on (core_action.id=sh.action_id)
join core_mailing on (core_mailing.id = core_action.mailing_id)
join core_subscriptionchangetype ct on (ct.id=sh.change_id)
where core_action.mailing_id = core_mailing.id
and ct.name='unsubscribe_bounce'
and left(core_mailing.started_at, 7) = left({{date}}, 7);

select  coalesce(sum( ( select count( distinct user_id )
from core_click join core_clickurl cu on (cu.id=core_click.clickurl_id) where core_click.mailing_id = core_mailing.id AND NOT cu.url like '%/unsubscribe/%' ) ), 0) as clicks
from core_mailing
where left(core_mailing.started_at, 7) = left({{date}}, 7);

select count( distinct core_action.user_id, core_mailing.id ) as complaints
from core_action
join core_unsubscribeaction us on (us.action_ptr_id=core_action.id)
join core_mailing on (core_mailing.id = core_action.mailing_id)
join core_subscriptionhistory sh on (core_action.id=sh.action_id)
join core_subscriptionchangetype ct on (ct.id=sh.change_id)
where core_action.mailing_id = core_mailing.id
and ct.name='unsubscribe_email'
and left(core_mailing.started_at, 7) = left({{date}}, 7);

select coalesce(sum( ( select count( distinct user_id ) from core_open where core_open.mailing_id = core_mailing.id  )), 0) as opens
from core_mailing
where left(core_mailing.started_at, 7) = left({{date}}, 7);

select coalesce(sum( ( select count(*) from core_usermailing where core_usermailing.mailing_id = core_mailing.id )), 0) as sent_mailings
from core_mailing
where left(core_mailing.started_at, 7) = left({{date}}, 7);

select count( distinct core_action.user_id, core_mailing.id ) as unsubs
from core_action
join core_unsubscribeaction us on (us.action_ptr_id=core_action.id)
join core_mailing on core_action.mailing_id = core_mailing.id
where core_action.mailing_id = core_mailing.id
and left(core_mailing.started_at, 7) = left({{date}}, 7);

SELECT user_id
FROM core_action
WHERE page_id = {page_id}
  AND subscribed_user = 1 AND status = 'complete'

SELECT id
FROM core_user u
WHERE
  created_at between curdate() - interval {{ days }} day
  AND curdate() - interval ({{ days }}-1) day;

SELECT user_id
FROM core_subscription
WHERE
  list_id = {{ list_id }}
  AND created_at between curdate() - interval {{ days }} day
  AND curdate() - interval ({{ days }}-1) day;

SELECT subject,
       sent,
       clicks,
       actions,
       NTL as new_to_list
FROM (
        SELECT tm.subject,
           tm.type,
           COUNT(DISTINCT(tms.id)) as sent,
           COUNT(DISTINCT tmo.transactional_mailing_sent_id) as opens,
           COUNT(DISTINCT tmc.transactional_mailing_sent_id) as clicks,
           COUNT(DISTINCT tma.id) as actions,
           COUNT(DISTINCT(tma_ntl.id)) as NTL
        FROM core_transactionalmailing tm
        JOIN core_transactionalmailingsent tms
          ON (tm.id = tms.transactional_mailing_id)
        LEFT JOIN core_transactionalmailingopen tmo
          ON (tms.id = tmo.transactional_mailing_sent_id)
        LEFT JOIN core_transactionalmailingclick tmc
          ON (tms.id = tmc.transactional_mailing_sent_id)
        LEFT JOIN core_transactionalmailingaction tma
          ON (tms.id = tma.transactional_mailing_sent_id)
        LEFT JOIN core_action tma_ntl
          ON (tma_ntl.id = tma.action_id AND tma_ntl.subscribed_user = 1)
        WHERE tm.page_id = {{ page_id }} AND tm.type = 'notification'
 GROUP BY 1, 2
) stats;

SET @start = {1._Start_on_date_YYYY-MM-DD};
SET @end = {2._End_by_date_YYYY-MM-DD};

SELECT
  IFNULL(type,'TOTAL') AS 'Page Type',
  FORMAT(SUM(pages),0) AS Pages,
  FORMAT(SUM(actions),0) AS 'Actions',
  FORMAT(SUM(ntl),0) AS 'NTL',
  FORMAT(100*SUM(ntl)/SUM(actions),1) AS 'NTL%',
  FORMAT(SUM(donors),0) AS 'Donors',
  FORMAT(SUM(payments),0) AS 'Payments',
  FORMAT(SUM(paid),2) AS '$',
  FORMAT(IFNULL(SUM(paid)/SUM(payments),0),2) AS 'Average $'
FROM (
  SELECT
    p.type,
    COUNT(DISTINCT p.id) AS pages,
    COUNT(a.id) AS actions,
    COUNT(DISTINCT CASE WHEN a.subscribed_user = 1 THEN a.user_id ELSE NULL END) AS ntl,
    0 AS donors,
    0 AS payments,
    0 AS paid
  FROM core_action a
  JOIN core_page p ON p.id = a.page_id
  WHERE a.status = 'complete'
  AND a.created_at BETWEEN @start AND @end
  AND p.real_actions = 1
  AND p.type <> 'Donation'
  GROUP BY 1
  UNION ALL
  SELECT
    p.type AS type,
    COUNT(DISTINCT p.id) AS pages,
    COUNT(t.id) AS actions,
    COUNT(DISTINCT CASE WHEN a.subscribed_user = 1 AND a.created_at BETWEEN @start AND @end THEN a.user_id ELSE NULL END) AS ntl,
    COUNT(DISTINCT a.user_id) AS donors,
    COUNT(DISTINCT t.id) AS payments,
    SUM(t.amount_converted) AS paid
  FROM core_transaction t
  JOIN core_order o ON  t.order_id = o.id
  JOIN core_action a ON o.action_id = a.id
  JOIN core_page p ON p.id = a.page_id
  WHERE t.status IN ('completed','')
  AND t.type = 'sale'
  AND t.success = 1
  AND t.created_at BETWEEN @start AND @end
  GROUP BY 1
) x
GROUP BY type WITH ROLLUP;

SET @start = {1._Start_on_date_YYYY-MM-DD};
SET @end = {2._End_by_date_YYYY-MM-DD};

SET @unsubs = (SELECT COUNT(DISTINCT sh.user_id)
        FROM core_subscriptionhistory sh
        JOIN core_subscriptionchangetype sct ON sct.id = sh.change_id
        WHERE sct.subscribed = 0
          AND sh.created_at BETWEEN @start AND @end);

SELECT FORMAT(COUNT(*),0) AS 'Sent Mails',
  FORMAT(COUNT(DISTINCT um.user_id),0) AS 'Users Mailed',
  FORMAT(COUNT(*) - COUNT(DISTINCT um.user_id),0) AS 'Duplicate Mails',
  FORMAT(@unsubs,0) AS 'Unsubs'
FROM core_usermailing um
WHERE um.created_at BETWEEN @start AND @end;

SET SQL_BIG_SELECTS=1;
SET @start = {1._Start_on_date_YYYY-MM-DD};
SET @end = {2._End_by_date_YYYY-MM-DD};

SELECT
    CONCAT('/mailings/reports/',mailing_id,' ', mailing_id) AS ID,
    started_at AS 'Sent At',
    subject_line AS 'Subject Line',
    notes AS Notes,
    FORMAT(sent, 0) AS Sent,
    FORMAT(100 * opens / sent, 1) AS 'Open%',
    IFNULL(FORMAT(100 * clicks / opens, 1),'No opens') AS 'Clicks /Open',
    IFNULL(FORMAT(100 * unsubs / opens, 2),'No opens') AS 'Unsubs /Open',
    FORMAT(actions, 0) AS 'Action Takers',
    FORMAT(100 * actions / sent, 2) AS 'Act%',
    FORMAT(ntl, 0) AS NTL,
    FORMAT(100 * (ntl-unsubs) / (sent/1000), 2) AS 'Net NTL /1000 Mailed',
    FORMAT(payments,0) AS Payments,
    FORMAT(paid,2) AS $,
    FORMAT(paid / (sent/100), 2) AS '$ /100 Mailed'
FROM (
    SELECT
        cm.id AS mailing_id,
        cm.started_at AS started_at,
        (SELECT text FROM core_mailingsubject ms WHERE ms.mailing_id = cm.id LIMIT 1) AS subject_line,
        cm.notes,
        cm.progress AS sent,
        COALESCE((SELECT COUNT(DISTINCT co.user_id)
            FROM core_open co
            WHERE co.mailing_id = cm.id), 0) AS opens,
        COALESCE((SELECT COUNT(DISTINCT cc.user_id)
            FROM core_click cc
            JOIN core_clickurl ccu ON ( ccu.id = cc.clickurl_id )
            WHERE cc.mailing_id = cm.id
            AND NOT ccu.url LIKE '%/cms/unsubscribe/%'), 0) AS clicks, -- do not count unsub clicks
        COALESCE((SELECT COUNT(DISTINCT ca.user_id)
            FROM core_unsubscribeaction cu
            JOIN core_action ca ON cu.action_ptr_id = ca.id
            WHERE ca.mailing_id = cm.id), 0) AS unsubs,
        COALESCE(d.payments,0) AS payments,
        COALESCE(d.paid,0) AS paid,
        COALESCE(acts.actions,0) AS actions,
        COALESCE(acts.new_to_list,0) AS NTL
    FROM core_mailing cm
    LEFT JOIN (
        SELECT mailing_id,
            COUNT(DISTINCT user_id) AS actions,
            COUNT(DISTINCT CASE WHEN subscribed_user = 1 THEN user_id ELSE NULL END) AS new_to_list
        FROM core_action a
        JOIN core_page p ON p.id = a.page_id
        WHERE p.real_actions = 1
            AND a.status = 'complete'
            AND a.created_at BETWEEN @start AND @end
        GROUP BY mailing_id
    ) acts ON acts.mailing_id = cm.id
    LEFT JOIN (
        SELECT mailing_id,
            COUNT(t.id) AS payments,
            SUM(t.amount_converted) AS paid
        FROM core_order o
        JOIN core_action a ON a.id = o.action_id
        JOIN core_transaction t ON t.order_id = o.id AND t.type = 'sale' AND t.status = 'completed' AND t.success = 1
        WHERE a.status = 'complete'
            AND t.created_at BETWEEN @start AND @end
        GROUP BY mailing_id
    ) d ON d.mailing_id = cm.id
    WHERE cm.started_at BETWEEN @start AND @end
    GROUP BY cm.id
) tt
ORDER BY started_at, mailing_id;

SET @start = {1._Start_on_date_YYYY-MM-DD};
SET @end = {2._End_by_date_YYYY-MM-DD};

SELECT
  paid_profiles AS 'Profiles with Payments',
  current_payments AS 'Payments',
  FORMAT(current_dollars,2) AS '$',
  new_payments AS 'New Profiles',
  FORMAT(new_dollars,2) AS 'New $',
  failed_payments AS 'Failed Payments',
  FORMAT(failed_dollars,2) AS 'Failed $',
  cancelled_payments AS 'Cancelled Profiles',
  FORMAT(cancelled_dollars,2) AS 'Cancelled $'
FROM (
  SELECT
    COUNT(DISTINCT cor.id) AS paid_profiles,
    COUNT(DISTINCT t.id) AS current_payments,
    COALESCE(SUM(t.amount_converted),0) AS current_dollars
  FROM core_orderrecurring cor
  JOIN core_transaction t ON t.order_id = cor.order_id
  WHERE t.created_at BETWEEN @start AND @end
  AND t.type = 'sale'
  AND t.status IN ('completed','')
  AND t.success = 1
) current
JOIN (
  SELECT
    COUNT(id) AS new_payments,
    COALESCE(SUM(amount_converted),0) AS new_dollars
  FROM (
    SELECT cor.id, MIN(t.created_at) AS 'first_payment', cor.amount_converted
      FROM core_orderrecurring cor
    JOIN core_transaction t ON t.order_id = cor.order_id
    WHERE t.status IN ('completed','')
    AND t.type = 'sale'
    AND t.success = 1
    AND t.created_at < @end
      GROUP BY 1
  ) a
  WHERE first_payment >= @start
) new_payments
JOIN (
  SELECT
    COALESCE(SUM(failed_month_payments),0) AS failed_payments,
    COALESCE(SUM(failed_month_dollars),0) AS failed_dollars
  FROM (
    SELECT cor.id,
    MONTH(t.created_at) AS mon,
    IF(SUM(CASE WHEN t.status IN ('completed','') AND t.success = 1 THEN 1 ELSE 0 END),0,IF(t.status = 'failed',1,0)) AS failed_month_payments,
    IF(SUM(CASE WHEN t.status IN ('completed','') AND t.success = 1 THEN 1 ELSE 0 END),0,IF(t.status = 'failed',cor.amount_converted,0)) AS failed_month_dollars
    FROM core_orderrecurring cor
    JOIN core_transaction t ON t.order_id = cor.order_id
    WHERE t.created_at >= @start AND t.created_at < @end
    AND t.type = 'sale'
    GROUP BY 1,2
  ) a
) failed
JOIN (
  SELECT
    COUNT(cor.id) AS cancelled_payments,
    COALESCE(SUM(cor.amount_converted),0) AS cancelled_dollars
  FROM core_orderrecurring cor
  WHERE cor.status LIKE 'cancel%'
    AND cor.updated_at > @start AND cor.updated_at < @end
) cancelled;

SET @start = {1._Start_on_date_YYYY-MM-DD};
SET @end = {2._End_by_date_YYYY-MM-DD};

SELECT a.source AS Source,
  COUNT(DISTINCT a.user_id) AS 'Action Takers',
  COUNT(DISTINCT CASE WHEN a.subscribed_user = 1 THEN a.user_id ELSE NULL END) AS NTL,
  FORMAT(100*COUNT(DISTINCT CASE WHEN a.subscribed_user = 1 THEN a.user_id ELSE NULL END) / COUNT(DISTINCT a.user_id),1) AS 'NTL %',
  COUNT(DISTINCT t.id) AS Payments,
  IFNULL(SUM(t.amount_converted),'0.00') AS '$',
  FORMAT(IFNULL(SUM(t.amount_converted)/COUNT(DISTINCT t.id),0),2) AS 'Average $'
FROM core_action a
  JOIN core_page p ON p.id = a.page_id
  LEFT JOIN core_order o ON o.action_id = a.id
  LEFT JOIN core_transaction t ON t.order_id = o.id AND t.type = 'sale' AND t.status = 'completed' AND t.success = 1
WHERE a.status = 'complete'
  AND a.created_at BETWEEN @start AND @end
  AND p.real_actions = 1
GROUP BY a.source
ORDER BY 2 DESC
LIMIT 10;

SET @start = {1._Start_on_date_YYYY-MM-DD};
SET @end = {2._End_by_date_YYYY-MM-DD};

SELECT *
FROM (
  SELECT
    CONCAT('/admin/core/page/',a.page_id,' ',a.page_id) AS 'Page ID',
    IF(p.type <> 'Import', CONCAT('/act/',p.name,' ',p.title), p.title) AS 'Title',
    p.type AS 'Type',
    IFNULL(notes,'') AS 'Notes',
    COUNT(DISTINCT a.user_id) AS 'Action Takers',
    COUNT(DISTINCT CASE WHEN a.subscribed_user = 1 THEN a.user_id ELSE NULL END) AS 'NTL',
    FORMAT(100*COUNT(DISTINCT CASE WHEN a.subscribed_user = 1 THEN a.user_id ELSE NULL END) / COUNT(DISTINCT a.user_id),1) AS 'NTL%',
    COUNT(DISTINCT t.id) AS 'Payments',
    FORMAT(IFNULL(SUM(t.amount_converted),0),2) AS '$',
    FORMAT(IFNULL(SUM(t.amount_converted)/COUNT(DISTINCT t.id),0),2) AS 'Avg $',
    CONCAT('/report/page_drilldown_dashboard/?page_id=',a.page_id,' ','Report') AS ''
  FROM core_action a
  JOIN core_page p ON p.id = a.page_id
  -- then include order tables because you can have payments on import pages, and import pages do not deal with recurring
  LEFT JOIN core_order o ON o.action_id = a.id
  LEFT JOIN core_transaction t ON t.order_id = o.id
    AND t.status IN ('completed','')
    AND t.type = 'sale'
    AND t.success = 1
    AND t.created_at BETWEEN @start AND @end
  WHERE a.status = 'complete'
  AND a.created_at BETWEEN @start AND @end
  AND p.real_actions = 1
  AND p.type <> 'donation'
  GROUP BY a.page_id
  UNION
  -- need a separate query for donations because recurring donations will have action timestamps in the past
  -- so need to look at transaction timestamps within this date range instead of action timestamps
  SELECT
    CONCAT('/admin/core/page/',a.page_id,' ',a.page_id) AS 'Page ID',
    CONCAT('/act/',p.name,' ',p.title) AS 'Title',
    p.type AS 'Type',
    IFNULL(notes,'') AS 'Notes',
    COUNT(DISTINCT a.user_id) AS 'Action takers',
    -- a.created_at can be outside of the date range
    -- because it may be associated with a recurring profile created outside of the range
    -- so make sure we limit NTL count to just the date range
    COUNT(DISTINCT CASE WHEN a.subscribed_user = 1 AND a.created_at BETWEEN @start AND @end THEN a.user_id ELSE NULL END) AS 'NTL',
    FORMAT(100*COUNT(DISTINCT CASE WHEN a.subscribed_user = 1 AND a.created_at BETWEEN @start AND @end THEN a.user_id ELSE NULL END) / COUNT(DISTINCT a.user_id),1) AS 'NTL%',
    COUNT(DISTINCT t.id) AS 'Payments',
    FORMAT(IFNULL(SUM(t.amount_converted),0),2) AS '$',
    FORMAT(IFNULL(SUM(t.amount_converted)/COUNT(DISTINCT t.id),0),2) AS 'Avg $',
    CONCAT('/report/page_drilldown_dashboard/?page_id=',a.page_id,' ','Report') AS ''
  FROM core_transaction t
  JOIN core_order o ON  t.order_id = o.id
  JOIN core_action a ON o.action_id = a.id
  JOIN core_page p ON p.id = a.page_id
  WHERE p.type = 'donation'
  AND t.status IN ('completed','')
  AND t.type = 'sale'
  AND t.success = 1
  AND t.created_at BETWEEN @start AND @end
  GROUP BY a.page_id
) x
ORDER BY 5 DESC, 9 DESC
LIMIT 20;