Webhook report for recurring transactions
Every 15 days we must send a report of recurring transactions to Fierro. That contains the same information as the one obtained through the sales webhook for single sales.
The report is generated with the following query
-- RECURRING PLANS
SELECT
u.email,
u. `name`,
bi.national_id,
u.billing_address,
u.extra_billing_information,
bi.address_line_1,
bi.address_line_2,
bi.city,
bi. `state`,
bi.country,
bi.postal_code,
bi.other_information,
p.plan_type,
p.status,
pl. `name`,
co.code,
co.amount,
p.currency_id,
p.amount_in_cents,
p.gateway,
p.created_at
FROM
users_plans up
LEFT JOIN users u ON up.user_id = u.id
LEFT JOIN payments p ON up.id = p.user_plan_id
LEFT JOIN tenants t ON up.tenant_id = t.id
LEFT JOIN plans pl ON up.plan_id = pl.id
LEFT JOIN coupons co on up.coupon_id = co.id
LEFT JOIN issues i ON up.issue_id = i.id,
billing_information bi
WHERE
bi.user_id = u.id
AND t.id = 1752
and p.status = 'approved'
and up.gateway not in ('manual')
and up.plan_type in ('recurring')
and p.payment_date BETWEEN '2021-03-01' AND '2021-03-15'