Query subscriptions over 50% time quota.

orbis-query-subscriptions-over-50-quota.sql

SELECT
	company.id AS company_id,
	company.name AS company_name,
	subscription.id AS subscription_id,
	subscription.name AS subscription_name,
	product.id AS product_id,
	product.name AS product_name,
	product.time_per_year,
	SUM( timesheet.number_seconds ) AS registered_time,
	100 / product.time_per_year * SUM( timesheet.number_seconds ) AS time_percentage,
	user.ID AS user_id,
	user.display_name AS user_display_name,
	user.user_email AS user_email
FROM
	orbis_subscriptions AS subscription
		INNER JOIN
	orbis_companies AS company
			ON subscription.company_id = company.id
		INNER JOIN
	orbis_subscription_types AS product
			ON subscription.type_id = product.id
		LEFT JOIN
	orbis_hours_registration AS timesheet
			ON (
				timesheet.subscription_id = subscription.id
					AND
				timesheet.date > DATE_ADD( subscription.activation_date, INTERVAL TIMESTAMPDIFF( YEAR, subscription.activation_date, NOW() ) YEAR )
			)
		LEFT JOIN
	wp_p2p AS user_company_p2p
			ON (
				user_company_p2p.p2p_type = 'orbis_users_to_companies'
					AND
				user_company_p2p.p2p_to = company.post_id
			)
		LEFT JOIN
	wp_users AS user
		ON user_company_p2p.p2p_from = user.ID
WHERE
	product.name IN (
		'WordPress onderhoud XS',
		'WordPress onderhoud S',
		'WordPress onderhoud M'
	)
		AND
	(
		subscription.cancel_date IS NULL
			OR
		subscription.expiration_date > NOW()
	)
GROUP BY
	subscription.id
HAVING
	( 100 / product.time_per_year * SUM( timesheet.number_seconds ) > 50 )
;