queries.sql

SELECT p.id, CASE p.method_id 
	WHEN 1 THEN 'MANUAL'
	WHEN 2 THEN 'VISA'
	WHEN 3 THEN 'MASTERCARD'
	WHEN 4 THEN 'AMEX'
	WHEN 6 THEN 'DISCOVER'
	WHEN 8 THEN 'PAYPAL'
	WHEN 9 THEN 'GATEWAY'
	WHEN 100 THEN 'CUSTOM'
	WHEN 101 THEN 'DTAG'
	WHEN 102 THEN 'DATATRANS'
	WHEN 103 THEN 'STAPLES'
	WHEN 104 THEN 'JCB'
	WHEN 105 THEN 'STAPLESCARD'
	WHEN 106 THEN 'CLOUDFOUNDRY'
	WHEN 107 THEN 'NETS_CREDIT_CARD'
	WHEN 108 THEN 'NETS_INVOICE'
	WHEN 109 THEN 'TMUS'
	WHEN 110 THEN 'SNAP'
	WHEN 111 THEN 'TBILL'
	WHEN 112 THEN 'BILLPAY'
	WHEN 113 THEN 'THISTLE'
	WHEN 114 THEN 'TEO'
	WHEN 115 THEN 'NEXTEL'
	WHEN 116 THEN 'IBM_CC'
	WHEN 117 THEN 'BT_BILL'
	WHEN 118 THEN 'IBM_INVOICE'
	WHEN 119 THEN 'CUMULA_BILL'
	ELSE 'UNKNOWN'
END method_name, p.attempt, p.result_id, 
CASE gs.status_value
	WHEN 1 THEN 'ACTIVE'
	WHEN 2 THEN 'PENDING_UNSUBSCRIPTION'
	WHEN 3 THEN 'UNSUBSCRIBED'
	WHEN 4 THEN 'PENDING_EXPIRATION'
	WHEN 5 THEN 'EXPIRED'
	WHEN 6 THEN 'NONSUBSCRIBED'
	WHEN 7 THEN 'DISCONTINUED'
	ELSE 'UNKNOWN'
END subcriber_status, 
i.id, i.create_datetime invoice_cdt, i.billing_process_id, i.payment_attempts, CASE gsi.status_value
	WHEN 1 THEN 'PAID'
	WHEN 2 THEN 'UNPAID'
	WHEN 3 THEN 'CARRIED'
	ELSE 'UNKNOWN'
END invoice_status, i.in_process_payment
FROM payment p
INNER JOIN base_user bu ON p.user_id = bu.id
INNER JOIN generic_status gs ON gs.id = bu.subscriber_status
LEFT OUTER JOIN payment_invoice pi ON pi.payment_id = p.id
LEFT OUTER JOIN invoice i ON pi.invoice_id = i.id
LEFT OUTER JOIN generic_status gsi ON gsi.id = i.status_id
WHERE bu.id = xxx;


SELECT t.NAME, el.foreign_id, el.user_id, el.affected_user_id, el.create_datetime, 
CASE el.level_field
WHEN 1 THEN 'DEBUG'
WHEN 2 THEN 'INFO'
WHEN 3 THEN 'WARN'
WHEN 4 THEN 'ERROR'
WHEN 5 THEN 'FATAL'
END LEVEL,
CASE el.module_id
WHEN  1 THEN 'BILLING_PROCESS'
WHEN  2 THEN 'USER_MAINTENANCE'
WHEN  3 THEN 'ITEM_MAINTENANCE'
WHEN  4 THEN 'ITEM_TYPE_MAINTENANCE'
WHEN  5 THEN 'ITEM_USER_PRICE_MAINTENANCE'
WHEN  6 THEN 'PROMOTION_MAINTENANCE'
WHEN  7 THEN 'ORDER_MAINTENANCE'
WHEN  8 THEN 'CREDIT_CARD_MAINTENANCE'
WHEN  9 THEN 'INVOICE_MAINTENANCE'
WHEN 10 THEN 'PAYMENT_MAINTENANCE'
WHEN 11 THEN 'TASK_MAINTENANCE'
WHEN 12 THEN 'WEBSERVICES'
WHEN 13 THEN 'MEDIATION'
WHEN 14 THEN 'BLACKLIST'
WHEN 15 THEN 'PROVISIONING'
END module, 
CASE el.message_id
WHEN  1 THEN 'BILLING_PROCESS_UNBILLED_PERIOD'
WHEN  2 THEN 'BILLING_PROCESS_NOT_ACTIVE_YET'
WHEN  3 THEN 'BILLING_PROCESS_ONE_PERIOD_NEEDED'
WHEN  4 THEN 'BILLING_PROCESS_RECENTLY_BILLED'
WHEN  5 THEN 'BILLING_PROCESS_WRONG_FLAG_ON'
WHEN  6 THEN 'BILLING_PROCESS_EXPIRED'
WHEN 10 THEN 'BILLING_REVIEW_NOT_APPROVED'
WHEN 11 THEN 'BILLING_REVIEW_NOT_GENERATED'
WHEN  8 THEN 'PASSWORD_CHANGE'
WHEN 12 THEN 'STATUS_CHANGE'
WHEN 14 THEN 'NO_FURTHER_STEP'
WHEN 15 THEN 'CANT_PAY_PARTNER'
WHEN 20 THEN 'SUBSCRIPTION_STATUS_CHANGE'
WHEN 32 THEN 'SUBSCRIPTION_STATUS_NO_CHANGE'
WHEN 21 THEN 'ACCOUNT_LOCKED'
WHEN 33 THEN 'DYNAMIC_BALANCE_CHANGE'
WHEN 34 THEN 'INVOICE_IF_CHILD_CHANGE'
WHEN 35 THEN 'NEXT_INVOICE_DATE_CHANGE'
WHEN 13 THEN 'ORDER_STATUS_CHANGE'
WHEN 17 THEN 'ORDER_LINE_UPDATED'
WHEN 18 THEN 'ORDER_NEXT_BILL_DATE_UPDATED'
WHEN 22 THEN 'ORDER_MAIN_SUBSCRIPTION_UPDATED'
WHEN 26 THEN 'ORDER_CANCEL_AND_CREDIT'
WHEN 24 THEN 'PAYMENT_INSTRUMENT_NOT_FOUND'
WHEN 16 THEN 'INVOICE_ORDER_APPLIED'
WHEN 23 THEN 'CURRENT_ORDER_FINISHED'
WHEN 27 THEN 'BLACKLIST_USER_ID_ADDED'
WHEN 28 THEN 'BLACKLIST_USER_ID_REMOVED'
WHEN 29 THEN 'PROVISIONING_UUID'
WHEN 30 THEN 'PROVISIONING_COMMAND'
WHEN 31 THEN 'PROVISIONING_STATUS_CHANGE'
WHEN 25 THEN 'ROW_CREATED'
WHEN 31 THEN 'PROVISIONING_STATUS_CHANGE'
WHEN 7 THEN 'ROW_DELETED'
WHEN 9 THEN 'ROW_UPDATED'
WHEN 19 THEN 'USER_TRANSITIONS_LIST'
END message, 
el.old_num, el.old_str, el.old_date
FROM event_log el
INNER JOIN jbilling_table t ON el.table_id = t.id
WHERE el.entity_id = 1
AND el.affected_user_id = MYAFFECTED_USER
AND t.name = 'purchase_order' 
AND el.foreign_id = MYAFFECTED_ORDER
AND el.message_id NOT IN (2, 3);



SELECT
po.id order_id, po.user_id, op.value nb_periods, 
CASE op.unit_id
	WHEN 1 THEN 'MONTH'
	WHEN 2 THEN 'WEEK'
	WHEN 3 THEN 'DAY'
	WHEN 4 THEN 'YEAR'
	WHEN 5 THEN 'SEMI_MONTHLY'
	ELSE ''
END period,
CASE po.billing_type_id
	WHEN 1 THEN 'PREPAID'
	WHEN 2 THEN 'POSTPAID'
END billing_type, po.active_since, po.active_until, po.cycle_start, po.next_billable_day, po.deleted, po.prorate_flag, 
CASE gs.status_value
	WHEN 1 THEN 'ACTIVE'
	WHEN 2 THEN 'FINISHED'
	WHEN 3 THEN 'SUSPENDED'
END STATUS, oper.period_start, oper.period_end, oper.invoice_id, i.billing_process_id, il.description, il.period_start, il.period_end, il.amount
FROM jbilling.purchase_order po 
INNER JOIN jbilling.order_period op ON po.period_id = op.id
INNER JOIN jbilling.generic_status gs ON gs.id = po.status_id
LEFT OUTER JOIN jbilling.order_process oper ON po.id = oper.order_id
LEFT OUTER JOIN jbilling.invoice i ON i.id = oper.invoice_id
LEFT OUTER JOIN jbilling.invoice_line il ON il.invoice_id = i.id
WHERE po.id = XXXX;




SELECT
po.id o_id, po.previous_order_id o_prev, po.next_order_id o_next, po.end_of_contract_order_id o_contract, po.created_on po_created_on, DATE(po.start_date) start_dt, DATE(po.end_date) end_dt, DATE(po.end_of_contract_date) end_of_contract_dt, 
po.billing_order_id, 
CASE gs.status_value
	WHEN 1 THEN 'ACTIVE'
	WHEN 2 THEN 'FINISHED'
	WHEN 3 THEN 'SUSPENDED'
END STATUS, 
jpo.active_since, jpo.active_until, jpo.next_billable_day, jpo.deleted, 
po.status o_status, 
 CASE po.id
	WHEN ce.active_order_id THEN 'XXX'
	ELSE ''
END 'active', 
CASE po.id
	WHEN ce.active_billing_order_id THEN 'XXX'
	ELSE ''
END 'billing',
po.created_by, po.sales_rep_id, po.subscription_type, 
ce.max_number_of_users ce_max_users, round(poi.units) new_units, round(poi_child.units) old_units, DATE(po_child.start_date) credit_start, DATE(po_child.end_date) credit_end, 
DATE(ev.created_on) event_created_on, 
group_concat('date: ', jel.create_datetime, 
', msg: ', CASE jel.message_id
WHEN  1 THEN 'BILLING_PROCESS_UNBILLED_PERIOD'
WHEN  2 THEN 'BILLING_PROCESS_NOT_ACTIVE_YET'
WHEN  3 THEN 'BILLING_PROCESS_ONE_PERIOD_NEEDED'
WHEN  4 THEN 'BILLING_PROCESS_RECENTLY_BILLED'
WHEN  5 THEN 'BILLING_PROCESS_WRONG_FLAG_ON'
WHEN  6 THEN 'BILLING_PROCESS_EXPIRED'
WHEN 10 THEN 'BILLING_REVIEW_NOT_APPROVED'
WHEN 11 THEN 'BILLING_REVIEW_NOT_GENERATED'
WHEN  8 THEN 'PASSWORD_CHANGE'
WHEN 12 THEN 'STATUS_CHANGE'
WHEN 14 THEN 'NO_FURTHER_STEP'
WHEN 15 THEN 'CANT_PAY_PARTNER'
WHEN 20 THEN 'SUBSCRIPTION_STATUS_CHANGE'
WHEN 32 THEN 'SUBSCRIPTION_STATUS_NO_CHANGE'
WHEN 21 THEN 'ACCOUNT_LOCKED'
WHEN 33 THEN 'DYNAMIC_BALANCE_CHANGE'
WHEN 34 THEN 'INVOICE_IF_CHILD_CHANGE'
WHEN 35 THEN 'NEXT_INVOICE_DATE_CHANGE'
WHEN 13 THEN 'ORDER_STATUS_CHANGE'
WHEN 17 THEN 'ORDER_LINE_UPDATED'
WHEN 18 THEN 'ORDER_NEXT_BILL_DATE_UPDATED'
WHEN 22 THEN 'ORDER_MAIN_SUBSCRIPTION_UPDATED'
WHEN 26 THEN 'ORDER_CANCEL_AND_CREDIT'
WHEN 24 THEN 'PAYMENT_INSTRUMENT_NOT_FOUND'
WHEN 16 THEN 'INVOICE_ORDER_APPLIED'
WHEN 23 THEN 'CURRENT_ORDER_FINISHED'
WHEN 27 THEN 'BLACKLIST_USER_ID_ADDED'
WHEN 28 THEN 'BLACKLIST_USER_ID_REMOVED'
WHEN 29 THEN 'PROVISIONING_UUID'
WHEN 30 THEN 'PROVISIONING_COMMAND'
WHEN 31 THEN 'PROVISIONING_STATUS_CHANGE'
WHEN 25 THEN 'ROW_CREATED'
WHEN 31 THEN 'PROVISIONING_STATUS_CHANGE'
WHEN 7 THEN 'ROW_DELETED'
WHEN 9 THEN 'ROW_UPDATED'
WHEN 19 THEN 'USER_TRANSITIONS_LIST'
END, 
', old_value: ', coalesce(jel.old_num, jel.old_str, jel.old_date) SEPARATOR '\n')
FROM purchase_orders po
INNER JOIN company_entitlements ce ON ce.id = po.company_entitlement_id
LEFT OUTER JOIN purchase_order_items poi ON po.id = poi.purchase_order_id AND poi.pricing_unit = 'USER'
LEFT OUTER JOIN purchase_orders po_child ON po.id = po_child.parent_order_id
LEFT OUTER JOIN purchase_order_items poi_child ON po_child.id = poi_child.purchase_order_id AND poi_child.pricing_unit = 'USER' AND poi_child.units < 0
LEFT OUTER JOIN events ev ON ev.purchase_order_id = po.id
INNER JOIN jbilling.purchase_order jpo ON jpo.id = po.billing_order_id
INNER JOIN jbilling.generic_status gs ON gs.id = jpo.status_id
LEFT OUTER JOIN jbilling.event_log jel ON jel.affected_user_id = jpo.user_id AND jpo.id = jel.foreign_id
LEFT OUTER JOIN jbilling.jbilling_table jt ON jel.table_id = jt.id AND jt.name = 'purchase_order'
WHERE po.company_entitlement_id = xxxx 
AND po.parent_order_id IS NULL
GROUP BY o_id, o_prev, o_next, o_contract, po_created_on, start_dt, end_dt, end_of_contract_dt, billing_order_id, STATUS, jpo.active_since, jpo.active_until, jpo.next_billable_day, jpo.deleted, o_status, active, billing, po.created_by, po.sales_rep_id, po.subscription_type, 
ce_max_users, new_units, old_units, credit_start, credit_end, event_created_on
ORDER BY po.id;


SELECT po.id po_id, po.status po_status, DATE(po.start_date) po_start_date, DATE(po.end_date) po_end_date, DATE(po.billingEndDate) po_billing_end_date, po.billing_order_id, po.edition_pricing_id, 
CASE 
	WHEN ce.addon_parent_company_entitlement_id IS NOT NULL THEN 'ADDON_V2'
	WHEN ce.id IS NOT NULL THEN 'APPLICATION'
	WHEN ai.uuid IS NOT NULL THEN 'ADDON_V1'
END 'application_type',
CASE 
	WHEN ai.uuid IS NOT NULL THEN ai.external_identifier
	WHEN ce.id IS NOT NULL THEN ce.external_vendor_identifier
END 'external_identifier',
CASE 
	WHEN aep.allow_custom_usage = 'Y' THEN 'custom' 
	WHEN aepi.id IS NOT NULL THEN concat("unit='", aepi.pricing_unit, "' with pricing=", aip.prices_json) 
	ELSE 'not allowed' 
END 'metered usage settings', 
aep.separate_prepaid
FROM purchase_orders po
LEFT OUTER JOIN company_entitlements ce ON po.company_entitlement_id = ce.id
LEFT OUTER JOIN addon_instances ai ON ai.uuid = po.addon_instance_uuid
LEFT OUTER JOIN app_edition_pricings aep ON aep.id = po.edition_pricing_id
LEFT OUTER JOIN app_edition_pricing_items aepi ON aepi.edition_pricing_id = aep.id AND aepi.metered_usage = 'Y'
LEFT OUTER JOIN app_item_prices aip ON aip.pricing_item_id = aepi.id
WHERE po.id IN (6974, 6972);