Detect Pending Paid Subscriptions

Overview

This document provides information about Detect Pending Paid Subscriptions.

SELECT
 tenants. `name`,
 `payments`.tenant_id,
 `payments`.id as payment_id,
 `payments`.plan_type,
 `payments`.plan_name,
 `payments`.gateway,
 users_plans.gateway AS user_plan_gateway,
 `payments`.currency_id,
 `payments`.amount_in_cents,
 `payments`.user_plan_id,
 `payments`.status,
 `payments`.gateway_status,
 users_plans.status AS user_plan_status,
 users_plans.valid_to,
 CASE WHEN `payments`.status = 'approved' && `payments`.status <> users_plans.status && users_plans.valid_to IS NULL THEN
  '_ HERE _'
 END AS needs_update,
 `payments`.recurring_cycle,
 `payments`.payment_date,
 users_plans.created_at,
 `payments`.user_id,
 users.email
FROM
 `farfalla`.`payments`
 JOIN tenants ON tenants.id = payments.tenant_id
 JOIN users_plans ON users_plans.id = payments.user_plan_id
 JOIN users ON users.id = payments.user_id
WHERE
 `payments`.plan_type in('recurring')
 and `payments`.gateway in('mercadopago')
 and `payments`.sandbox = 0
 AND `user_plan_id` IN(
  SELECT
   id FROM `farfalla`.`users_plans`
  WHERE (`status` IN("pending")))
ORDER BY
 tenant_id, user_id, user_plan_id, payment_date;

---