query

query.sql

SELECT
					 agent_plan_id, plan_id
					,agent_plan_cim_customer_id
					,plan_trial_interval_price
					,agent_plan_cim_payment_profile_id

				FROM 
				(SELECT COUNT(agent_plan_payment_id) AS payment_count FROM agent_plan_payments JOIN agent_plan_payment_reasons ON agent_plan_payment_reasons.agent_plan_payment_reason_id = agent_plan_payments.agent_plan_payment_reason_id WHERE agent_plan_payments.agent_plan_id = agent_plans_with_modifiers.agent_plan_id AND agent_plan_payment_reasons.agent_plan_payment_reason_code='trial-interval-fee') blah
				, agent_plans_with_modifiers
				WHERE
				-- 'paid through' date = start_date + trial_interval_length * payment(s)
				(agent_plan_start_date +
				((CASE
					WHEN plan_trial_interval_days IS NOT NULL THEN (plan_trial_interval_days || ' days')
					WHEN plan_trial_interval_months IS NOT NULL THEN (plan_trial_interval_months || ' months')
					ELSE '0'
				END)::INTERVAL *
					-- Number of payments made so far
					blah.payment_count
				)::INTERVAL)
				< NOW()
				
				-- Number of trial intervals has not elapsed
				-- @bug How can this subquery be reused from above??
				AND plan_trial_interval_count < (SELECT COUNT(agent_plan_payment_id) FROM agent_plan_payments JOIN agent_plan_payment_reasons ON agent_plan_payment_reasons.agent_plan_payment_reason_id = agent_plan_payments.agent_plan_payment_reason_id WHERE agent_plan_payments.agent_plan_id = agent_plans_with_modifiers.agent_plan_id AND agent_plan_payment_reasons.agent_plan_payment_reason_code='trial-interval-fee')

				-- Obviously we only want active plans
				AND agent_plan_active IS TRUE
				AND plan_enabled IS TRUE

				-- Plan is trial
				AND (plan_trial_interval_months IS NOT NULL OR plan_trial_interval_days IS NOT NULL)

				-- Plan is not free trial
				AND plan_trial_interval_price > 0.00