Alfaomega duplicated content from marketplace

Click to expand and see the queries

The tenant had already uploaded 232 publications from publisher Narcea, and then they arranged a deal with Narcea to have access to all their content. So those 232 publication where duplicated. This query lists all the duplicated content shared between these two tenants.

-- List duplicated content
SELECT
--  issues.external_id,
 --  tenant_issue.ti_id,
--  GROUP_CONCAT(tenant_issue.ti_id SEPARATOR ', ') AS tenant_issue_ti_ids,
 GROUP_CONCAT((
  CASE WHEN issues.tenant_id = 1305 THEN
   tenant_issue.ti_id
  WHEN issues.tenant_id = 341 THEN
   ''
  END) SEPARATOR '') AS tenant_issue_ti_ids_to_delete
--  tenant_issue.ti_tenant_id,
 --  tenant_issue.ti_issue_id,
--  GROUP_CONCAT(tenant_issue.ti_issue_id SEPARATOR ', ') AS tenant_issue_ti_issue_ids,
--  GROUP_CONCAT(terms.name SEPARATOR ', ') AS publishers,
--  COUNT(issues.external_id) AS repetitions
FROM
 tenant_issue
 JOIN issues ON issues.id = ti_issue_id
 JOIN taxables ON taxable_id = ti_issue_id
  AND taxable_type = 'issue'
 JOIN terms ON terms.id = taxables.term_id
  AND terms.taxonomy = 'publisher'
WHERE
 ti_tenant_id = 341
 --  AND issues.tenant_id = 1305
 AND issues.tenant_id IN(1305, 341)
 AND issues.deleted_at IS NULL
 -- This line is commented because the content is already "deleted"
--  AND tenant_issue.ti_deleted_at IS NULL
 AND terms.name IN('narcea', 'Narcea Ediciones')
GROUP BY
 issues.external_id
HAVING
 COUNT(issues.external_id) > 1;

IP Ranges mass insert

Click to expand and see the queries

INSERT INTO  'users_ip_ranges ' ( 'tenant_id ',  'user_id ',  'ip_from ',  'ip_to ',  'updated_at ',  'created_at ') VALUES (341, 75998, '190.131.254.210', NULL, NOW(), NOW())

Average sessions count per user in time range

Click to expand and see the queries

-- Average sessions count per user in time range
SELECT
    AVG(views) AS avg_views
FROM
    user_stats
WHERE
    updated_at BETWEEN DATE_FORMAT(NOW(), '%Y-01-01')
    AND DATE_FORMAT(NOW(), '%Y-07-01');

Payment Reports

Click to expand and see the queries

-- Payments report

SELECT
    tenant.name AS tenant,
    payments.payment_date AS fecha,
    payments.amount_in_cents AS monto_en_cents,
    payments.payment_payload ->> '$.data[*].amount_paid' AS monto_en_cents_from_payload,
    payments.currency_id AS moneda_principal,
    payments.amount_in_usd_cents AS monto_en_cents_USD,
    payments.gateway,
    payments.status,
    payments.payment_payload,
    payments.plan_type
FROM
    payments p
    INNER JOIN tenants ON payments.tenant_id = tenant.id
    LEFT JOIN sales_settings ON payments.sale_settings_id = sales_settings.id
WHERE
    payments.status = 'approved'
    AND payments.gateway = 'stripe'
    AND payments.amount_in_cents = 0
    AND payments.payment_date BETWEEN '2020-04-01 00:00:00'
    AND '2020-06-30 23:59:59';

Tenans admins

Click to expand and see the queries

-- Individual rows
SELECT
tenants.id,
 tenants.name,
 tenants.lang,
 pla_plans. `name`,
 users.email
FROM
 tenants
 JOIN sign_up_intents ON sign_up_intents.tenant_id = tenants.id
 JOIN pla_plans ON tenants.pla_plan_id = pla_plans.id
 JOIN users ON users.tenant_id = tenants.id
WHERE
 tenants.has_plan = 1
 AND users.tenant_id = tenants.id
 AND users.admin = 1
 AND users.email NOT LIKE '%publica.la%'
 AND users.email NOT LIKE '%fgilio%'
 AND users.email NOT LIKE '%franco.gilio%'
 AND users.email NOT LIKE '%plaurino%'
 AND users.email NOT LIKE '%rd3.com%'
ORDER BY
 tenants.id;

-- One row per tenant
SELECT
 tenants.id,
 tenants.name,
 tenants.lang,
 pla_plans. `name`,
 (
  SELECT
   GROUP_CONCAT(users.email SEPARATOR ', ') AS admins_emails
  FROM
   users
  WHERE
   users.tenant_id = tenants.id
   AND users.admin = 1
   AND users.email NOT LIKE '%publica.la%'
   AND users.email NOT LIKE '%fgilio%'
   AND users.email NOT LIKE '%franco.gilio%'
   AND users.email NOT LIKE '%plaurino%'
   AND users.email NOT LIKE '%rd3.com%') AS admins_emails
 FROM
  tenants
  JOIN sign_up_intents ON sign_up_intents.tenant_id = tenants.id
  JOIN pla_plans ON tenants.pla_plan_id = pla_plans.id
 WHERE
  tenants.has_plan = 1
 GROUP BY
  tenant_id
 ORDER BY
  tenants.id;

Tenant's gateways report

Click to expand and see the queries

SELECT
 tenants.id,
 tenants.name,
 tenants.final_domain,
 tenants.lang,
 pla_plans.name as plan,
 (
        SELECT
            GROUP_CONCAT(users.email SEPARATOR ', ') AS admins_emails
        FROM
            users
        WHERE
            users.tenant_id = tenants.id
            AND users.admin = 1
            AND users.email NOT LIKE '%publica.la%'
            AND users.email NOT LIKE '%fgilio%'
            AND users.email NOT LIKE '%franco.gilio%'
            AND users.email NOT LIKE '%plaurino%'
            AND users.email NOT LIKE '%jgarcia%'
            AND users.email NOT LIKE '%rd3.com%'
    ) AS admins_emails,
    IFNULL(sign_up_intents.agreement_percentage,'30.00'),
--  sign_up_intents.agreement_percentage,
 (
  SELECT
   mercadopago_live_client_id
  FROM
   sales_settings
  WHERE
   tenant_id = tenants.id
   AND deleted_at IS NULL) AS mercadopago_live_client_id,
 (
  SELECT
   paypal_live_client_id
  FROM
   sales_settings
  WHERE
   tenant_id = tenants.id
   AND deleted_at IS NULL) AS paypal_live_client_id,
 (
  SELECT
   payu_live_account_id
  FROM
   sales_settings
  WHERE
   tenant_id = tenants.id
   AND deleted_at IS NULL) AS payu_live_account_id,
 (
  SELECT
   stripe_live_client_id
  FROM
   sales_settings
  WHERE
   tenant_id = tenants.id
   AND deleted_at IS NULL) AS stripe_live_client_id
 FROM
  tenants
  JOIN sign_up_intents ON tenants.id = sign_up_intents.tenant_id
  JOIN pla_plans ON tenants.pla_plan_id = pla_plans.id
 WHERE
  has_plan = 1;

Ebooks Patagonia Issues CSV

Click to expand and see the queries

SELECT
 --  `issues`.tenant_id,
 --  `issues`.id,
 `issues`.external_id AS 'eISBN',
 `issues`.name,
 DATE(`issues`.created_at) AS 'Fecha de carga',
 (
  SELECT
   `name`
  FROM
   `tags`
   INNER JOIN `taggables` ON `tags`.`id` = `taggables`.`tag_id`
  WHERE
   `taggables`.`taggable_id` = issues.id
   AND `taggables`.`taggable_type` = 'issue'
   AND `tags`.`type` = 'category') AS 'Categoría',
 (
  SELECT
   `name`
  FROM
   `tags`
   INNER JOIN `taggables` ON `tags`.`id` = `taggables`.`tag_id`
  WHERE
   `taggables`.`taggable_id` = issues.id
   AND `taggables`.`taggable_type` = 'issue'
   AND `tags`.`type` = 'publisher') AS 'Editorial', IF((
   SELECT
    COUNT(*)
   FROM `favorites`
  WHERE
   favorites.issue_id = issues.id AND `favorites`.`user_id` = '166422'), 'si', '') AS 'Seleccionada'
  FROM
   `farfalla`.`issues`
  WHERE (`deleted_at` IS NULL) AND(`issues`.`tenant_id` = '1484')
ORDER BY
 `id` DESC;

Tenants Report

Click to expand and see the queries

SELECT
 t.id,
 t. `name`,
 t.final_domain,
 pp. `name` AS "plan",
 t.has_plan,
 (
  SELECT
   count(*)
  FROM
   issues
  WHERE
   tenant_id = t.id
   AND deleted_at IS NULL) AS "publications", (
   SELECT
    count(*)
   FROM
    users
   WHERE
    tenant_id = t.id
    AND deleted_at IS NULL) AS "users", (
    SELECT
     sum(amount_in_usd_cents / 100)
    FROM
     payments
    WHERE
     tenant_id = t.id
     AND status = 'approved'
     AND sandbox = 0) AS "Sales in USD", t.lang, sui.country_code, t.created_at AS "creation date"
   FROM
    tenants t
   LEFT JOIN pla_plans pp ON t.pla_plan_id = pp.id
   JOIN sign_up_intents sui ON t.id = sui.tenant_id

Report of coupons usage

Click to expand and see the queries

SELECT
 up.created_at as "Fecha",
 c.code as "Cupon",
 c.amount as "Descuento",
 i. `name` as "Producto",
 u.email as "email",
 p.amount_in_cents / 100 as "Total cancelado",
 p.currency_id as "moneda"
FROM
 users_plans up
 JOIN coupons c ON up.coupon_id = c.id
 JOIN payments p ON up.id = p.user_plan_id
 JOIN users u ON up.user_id = u.id
 JOIN issues i ON up.issue_id = i.id
WHERE
 up.coupon_id IS NOT NULL
-- replace by any tenant_id
 AND up.tenant_id = 2433

Tenants with FB Pixel

Click to expand and see the queries

SELECT
 t.final_domain,
 JSON_VALUE(tm.tracking, '$.facebook_pixel_id') as 'fb'
FROM
 tenants t
 inner JOIN tenants_meta tm ON t.id = tm.tenant_id
 where t.final_domain is not NULL
 and JSON_VALUE(tm.tracking, '$.facebook_pixel_id') <> ''

Integrations Report

Click to expand and see the queries

-- IP Ranges
SELECT
 t.id, t.`name`, u.email, uir.ip_from, uir.ip_to, u.sessions_limit
FROM
 users_ip_ranges uir
 LEFT JOIN tenants t ON uir.tenant_id = t.id
 LEFT JOIN users u on uir.user_id = u.id
WHERE
 t.has_plan = 1;

SELECT
 t.id, t.`name`, u.email, u.sessions_limit,
 (select COUNT(*) from users_ip_ranges WHERE users_ip_ranges.user_id = u.id) as thecount
FROM
 users u
 LEFT JOIN tenants t ON u.tenant_id = t.id
 LEFT JOIN users_ip_ranges uir on uir.user_id = u.id
WHERE
 t.has_plan = 1
 having thecount > 0;

-- URL Referrals
SELECT
 t.id, t.`name`, u.email, ur.referrer, u.sessions_limit
FROM
 users_referrers ur
 LEFT JOIN tenants t ON ur.tenant_id = t.id
 LEFT JOIN users u on ur.user_id = u.id
WHERE
 t.has_plan = 1

-- LTI
SELECT
 t.id, t.`name`, ep.plan_id, p.`name`, ep.lti_consumer_key, ep.free_access
FROM
 entry_points ep
 LEFT JOIN tenants t ON ep.tenant_id = t.id
 LEFT JOIN plans p on ep.plan_id = p.id
WHERE
 t.has_plan = 1


-- LTI Orders
SELECT
 t.id, t.`name`,u.id, u.email,  p.`name`, up.created_at, up.updated_at
FROM
 users_plans up
 LEFT JOIN users u ON up.user_id = u.id
 LEFT JOIN tenants t on up.tenant_id = t.id
 LEFT JOIN plans p on up.plan_id = p.id
--  INNER JOIN payments pa on pa.user_plan_id = up.id
WHERE
 up.gateway = 'lti'
 AND up.status = 'approved'

Get Cancelled Reasons

Click to expand and see the queries

SELECT
--  up.tenant_id,
 up.id,
--  up.user_id,
--  al.subject_id,
--  al.subject_type,
--  al.causer_id,
--  u.id,
--  u.email,
 IF(al.causer_type is null, 'System', u.email),
 IF(up.user_id = al.causer_id,'User','Admin'),
--  al.causer_type,
--  al.properties,
  al.created_at,
  al.updated_at
FROM
 users_plans up
 inner JOIN activity_log al on up.id = al.subject_id
 LEFT JOIN users u on up.user_id = u.id
WHERE
 up.tenant_id = 26
 and up.status = 'cancelled'
 and al.subject_type = 'App\\UserPlan'
 and JSON_VALUE(al.properties,'$.attributes.status') in ('cancelled','paused')
ORDER BY

Get retail content of a specific tenant in other stores

Click to expand and see the queries

SELECT
 tenants.`name`,
 tenants.final_domain,
 tenants.has_plan as 'is_active_store',
 -- commented. it's for checking if the issue is the same in both tables
 -- tenant_issue.ti_issue_id,
 -- issues.id,
 issues.external_id,
 issues.`name`,
 tenant_issue.ti_license
FROM
 issues
 INNER JOIN tenant_issue on tenant_issue.ti_issue_id = issues.id
 INNER JOIN tenants ON tenant_issue.ti_tenant_id = tenants.id
WHERE
 issues.tenant_id = {tenant_id}
 AND issues.deleted_at is null
 AND tenant_issue.ti_license = 'retail'
 AND tenant_issue.ti_deleted_at is NULL;

Get a list of tenants and their retail content counts, filtered by a specific tenant

Click to expand and see the queries

SELECT
 tenants.`name`,
 tenants.final_domain,
 count(tenant_issue.ti_issue_id) as 'total_issues'
FROM
 issues
 INNER JOIN tenant_issue on tenant_issue.ti_issue_id = issues.id
 INNER JOIN tenants ON tenant_issue.ti_tenant_id = tenants.id
WHERE
 issues.tenant_id = {tenant_id}
 AND issues.deleted_at is null
 AND tenant_issue.ti_license = 'retail'
 AND tenant_issue.ti_deleted_at is NULL
 AND tenants.has_plan = 1
GROUP BY tenants.id
ORDER BY total_issues DESC

Get reading statistics report ,similar to the one we have in the statistics modal in the users section, the operating system column is also added

Click to expand and see the queries

SELECT
    s.uuid AS id,
    u.id AS user_id,
    u.email,
    i.id AS issue_id,
    i.name AS issue_title,
    i.external_id AS issue_isbn,
    GROUP_CONCAT(DISTINCT c.first_name SEPARATOR ',') AS issue_author,
    COUNT(DISTINCT(n.id)) AS interacciones,
    COUNT(DISTINCT(s.id)) AS sessions,

    CASE
        WHEN ROUND(SUM(s.seconds_reading) / 60) > 0 THEN ROUND(SUM(s.seconds_reading) / 60)
        ELSE 'N/A'
    END AS reading_time_minutes,
    CASE
        WHEN ROUND(SUM(s.seconds_reading) / 60) > 0 THEN SEC_TO_TIME(ROUND(SUM(s.seconds_reading) / 60))
        ELSE 'N/A'
    END AS formatted_time,
--     SEC_TO_TIME(ROUND(SUM(s.seconds_reading) / 60)) AS formatted_time,
    JSON_EXTRACT_STRING(JSON_EXTRACT_JSON(te.event_properties, "request"), "userAgent") as device,
    CASE
        WHEN device LIKE '%Mac OS%' THEN 'ios'
        WHEN device LIKE '%iPhone%' THEN 'ios'
        WHEN device LIKE '%iPad%' THEN 'ios'
        WHEN device LIKE '%Windows%' THEN 'windows'
        WHEN device LIKE '%Android%' THEN 'android'
        WHEN device LIKE '%Linux%' THEN 'linux'
        WHEN device LIKE '%Ubuntu%' THEN 'linux'
        WHEN device LIKE '%Debian%' THEN 'linux'
        WHEN device LIKE '%Fedora%' THEN 'linux'
        WHEN device LIKE '%Chromebook%' THEN 'chrome_os'
        ELSE 'otro'
    END AS sistema_operativo


FROM
    issues i
JOIN
    tenant_issue ti ON ti.ti_issue_id = i.id
JOIN
    issue_contributors ic ON ic.issue_id = i.id
LEFT JOIN
    contributors c ON ic.contributor_id = c.id
LEFT JOIN
    issue_bisac ib ON ib.issue_id = i.id AND ib.main = 1
LEFT JOIN
    bisacs b ON b.id = ib.bisac_id
JOIN
    issue_user iu ON iu.issue_id = i.id AND iu.tenant_id = ti.ti_tenant_id
JOIN
    users u ON u.id = iu.user_id
JOIN
    coniglio.sessions s ON s.tenant_id = ti.ti_tenant_id AND s.issue_id = i.id AND s.user_id = iu.user_id
JOIN
    coniglio.track_events te ON te.tenant_id = i.tenant_id AND te.issue_id = i.id AND te.user_id = iu.user_id
LEFT JOIN
 notes n on n.issue_id = i.id and n.user_id = iu.user_id  and n.tenant_id = i.tenant_id

WHERE
    ti.ti_tenant_id = 2899
    AND ti.ti_deleted_at IS NULL
    AND i.deleted_at IS NULL
    AND s.started_at > '2023-06-30'
    AND s.started_at <= '2024-06-30'
    AND te.event_class = "App\\Events\\SessionStart"
GROUP BY
    s.user_id,
    s.issue_id

ORDER BY
    reading_time_minutes DESC,
    u.email,
    i.name;