group AnalyticsSanitySqlDao;
CHECK_TENANT(prefix) ::= "<prefix>tenant_record_id = :tenantRecordId"
AND_CHECK_TENANT(prefix) ::= "AND <CHECK_TENANT(prefix)>"
checkBstMatchesSubscriptionEvents() ::= <<
select distinct
account_id
from (
select
account_id
, sum(per_event_check) account_check_left
, count(*) account_check_right
from (
select
account_id
, account_key_check and app_id and date_type and slug per_event_check
from (
select
q.account_key
, q.account_id
, b_account_key = account_key account_key_check
, b_app_id = app_id app_id
, case
when b_event like 'CANCEL_%' then b_req_dt = req_dt
when b_event like 'SYSTEM_CANCEL_%' then b_req_dt = eff_dt
else b_req_dt = req_dt and b_eff_dt = eff_dt
end date_type
, coalesce(b_slug = slug, 1) slug
from (
select
bst.total_ordering record_id
, bst.account_key b_account_key
, bst.external_key b_app_id
, bst.event b_event
, bst.next_slug b_slug
, from_unixtime(bst.requested_timestamp / 1000) b_req_dt
, from_unixtime(bst.next_start_date / 1000) b_eff_dt
, a.external_key account_key
, a.id account_id
, b.external_key app_id
, s.id
, e.event_type
, e.user_type
, e.phase_name slug
, e.effective_date eff_dt
, e.requested_date req_dt
, from_unixtime(coalesce(bst.next_start_date, bst.requested_timestamp) / 1000) b_dt
from subscription_events e
join subscriptions s on e.subscription_id = s.id
join bundles b on s.bundle_id = b.id
join accounts a on b.account_id = a.id
join bst on bst.total_ordering = e.record_id
where
e.is_active = 1
and e.user_type != 'MIGRATE_BILLING'
<AND_CHECK_TENANT("e.")>
<AND_CHECK_TENANT("s.")>
<AND_CHECK_TENANT("b.")>
<AND_CHECK_TENANT("a.")>
<AND_CHECK_TENANT("bst.")>
order by e.record_id asc
) q
) p
) r group by (account_id)
) s
where account_check_left != account_check_right
;
>>
checkBiiMatchesInvoiceItems() ::= <<
select distinct
account_id
from (
select
id
, account_id
, start_date_check and end_date_check and amount_check and currency_check and linked_item_id_check and slug_check per_item_check
from (
select
ii.id
, ii.account_id
, ii.start_date = bii.start_date start_date_check
, coalesce(ii.end_date, bii.end_date) = bii.end_date end_date_check
, ii.amount = bii.amount amount_check
, ii.currency = bii.currency currency_check
, coalesce(ii.linked_item_id = bii.linked_item_id, 1) linked_item_id_check
, ii.phase_name = bii.slug slug_check
from invoice_items ii
join bii on ii.id = bii.item_id
where <CHECK_TENANT("ii.")>
<AND_CHECK_TENANT("bii.")>
) p
) q where !per_item_check
;
>>
checkBipMatchesInvoicePayments() ::= <<
select distinct
account_id
from (
select
payment_id
, account_id
, amount_check and currency_check and payment_type_check and linked_invoice_payment_id_check and invoice_id_check total_check
from (
select
bip.payment_id
, a.id account_id
, bip.amount = ip.amount amount_check
, bip.currency = ip.currency currency_check
, bip.invoice_payment_type = ip.type payment_type_check
, bip.linked_invoice_payment_id = ip.linked_invoice_payment_id linked_invoice_payment_id_check
, bip.invoice_id = ip.invoice_id invoice_id_check
from bip
join invoice_payments ip on bip.payment_id = ip.id
join accounts a on a.record_id = ip.account_record_id
where <CHECK_TENANT("bip.")>
<AND_CHECK_TENANT("ip.")>
<AND_CHECK_TENANT("a.")>
) p
) q where !total_check
;
>>
checkBinAmountPaidMatchesInvoicePayments() ::= <<
select distinct
account_id
from (
select
bin.invoice_id
, bin.account_id
, sum(ip.amount) = amount_paid amount_paid_check
from bin
join invoice_payments ip on bin.invoice_id = ip.invoice_id
where <CHECK_TENANT("bin.")>
<AND_CHECK_TENANT("ip.")>
group by ip.invoice_id, bin.account_id
) p where !amount_paid_check
;
>>
checkBinAmountChargedMatchesInvoicePayments() ::= <<
select distinct
account_id
from (
select
bin.invoice_id
, bin.account_id
, sum(ip.amount) = amount_charged amount_charged_check
from bin
join invoice_payments ip on bin.invoice_id = ip.invoice_id
where <CHECK_TENANT("bin.")>
<AND_CHECK_TENANT("ip.")>
group by ip.invoice_id, bin.account_id
) p where !amount_charged_check
;
>>
checkBinBiiBalanceConsistency() ::= <<
select distinct
account_id
from (
select
invoice_id
, account_id
, balance = amount_charged + total_adj_amount + total_cba - amount_paid balance_check
from (
select
bin.invoice_id
, bin.account_id
, bin.amount_paid
, bin.amount_charged
, bin.amount_credited
, bin.balance
, coalesce(total_adj_amount, 0) total_adj_amount
, coalesce(total_cba, 0) total_cba
from bin
left join (
select
bii.invoice_id
, sum(amount) total_adj_amount
from bii
join bin on bin.invoice_id = bii.invoice_id
where bii.item_type in ('CREDIT_ADJ', 'REFUND_ADJ', 'ITEM_ADJ')
<AND_CHECK_TENANT("bii.")>
<AND_CHECK_TENANT("bin.")>
group by (bii.invoice_id)
) p on bin.invoice_id = p.invoice_id
left join (
select
q.invoice_id
, total_cba
from (
select
bii.invoice_id
, sum(amount) total_cba
from bii
join bin on bin.invoice_id = bii.invoice_id
where bii.item_type in ('CBA_ADJ')
<AND_CHECK_TENANT("bii.")>
<AND_CHECK_TENANT("bin.")>
group by (bii.invoice_id)
) q
) r on r.invoice_id = bin.invoice_id
where <CHECK_TENANT("bin.")>
) s
) t where !balance_check
;
>>
checkBinBiiAmountCreditedConsistency() ::= <<
select distinct
account_id
from (
select
bii.invoice_id
, bin.account_id
, sum(amount) = bin.amount_credited credit_check
from bii
join bin on bin.invoice_id = bii.invoice_id
where bii.item_type in ('CREDIT_ADJ')
<AND_CHECK_TENANT("bii.")>
<AND_CHECK_TENANT("bin.")>
group by bii.invoice_id, bin.account_id
) p where !credit_check
;
>>
checkBacBinBiiConsistency() ::= <<
select distinct
account_id
from (
select
account_id
, total_invoice_balance_check and total_account_balance_check bac_check
from (
select
account_id
, total_invoice_balance_on_account = total_invoice_balance total_invoice_balance_check
, total_account_balance = total_invoice_balance_on_account - account_cba total_account_balance_check
from (
select
bac.account_id
, bac.total_invoice_balance total_invoice_balance_on_account
, sum(bin.balance) total_invoice_balance
, bac.balance total_account_balance
, coalesce(account_cba, 0) account_cba
from bac
-- some might not have cba items
left join (
select
bin.account_id
, sum(bii.amount) account_cba
from bac
join bin on bin.account_id = bac.account_id
join bii on bii.invoice_id = bin.invoice_id
where bii.item_type = 'CBA_ADJ'
<AND_CHECK_TENANT("bac.")>
<AND_CHECK_TENANT("bin.")>
<AND_CHECK_TENANT("bii.")>
group by (bin.account_id)
) p on bac.account_id = p.account_id
left join bin on bin.account_id = bac.account_id
where <CHECK_TENANT("bac.")>
<AND_CHECK_TENANT("bin.")>
group by bac.account_id, account_cba
) q
) r
) s where !bac_check
;
>>
checkBacTagsMatchesTags() ::= <<
select distinct
account_id
from (
select
account_id
, b_tag_name = tag_name tag_name_check
from (
select
bt.account_id account_id
, bt.name b_tag_name
, case
when t.tag_definition_id = '00000000-0000-0000-0000-000000000001' then 'AUTO_PAY_OFF'
when t.tag_definition_id = '00000000-0000-0000-0000-000000000002' then 'AUTO_INVOICING_OFF'
when t.tag_definition_id = '00000000-0000-0000-0000-000000000003' then 'OVERDUE_ENFORCEMENT_OFF'
when t.tag_definition_id = '00000000-0000-0000-0000-000000000003' then 'WRITTEN_OFF'
else tdef.name
end tag_name
from bac_tags bt
join tags t on t.object_id = bt.account_id
left join tag_definitions tdef on t.tag_definition_id = tdef.id
where t.object_type = 'account'
<AND_CHECK_TENANT("bt.")>
<AND_CHECK_TENANT("t.")>
<AND_CHECK_TENANT("tdef.")>
) p
) q where ! tag_name_check;
>>