Regen CV Calculation

new_order_cv.sql

-- 新增订单 CV 数据清单（1.1 - 4.30）
WITH OrderParticipants AS (
	-- 收集所有有效的订单和关联分销商
	SELECT
		CASE WHEN ru.role_id = 2 THEN
			o.distributor_id -- 分销商自己的订单
		ELSE
			o.personal_sponsor_distributor_id -- 客户订单，关联到赞助人
		END AS effective_distributor_id,
		o.id AS order_id,
		o.completed_at,
		li.id AS line_item_id,
		li.u_volume
	FROM
		orders o
		JOIN line_items li ON o.id = li.order_id
		JOIN variants v ON li.variant_id = v.id
		JOIN products p ON v.product_id = p.id
		JOIN users u ON o.user_id = u.id
		JOIN roles_users ru ON u.id = ru.user_id
	WHERE
		o.completed_at IS NOT NULL -- 只包含已完成的订单
		AND o.completed_at >= '2025-01-01'
		AND o.completed_at < '2025-05-01' -- 时间范围
		AND o.state != 'cancelled' -- 排除已取消订单
		AND p.id NOT IN (5, 23, 24, 25, 59, 60, 61, 65, 66, 67, 69, 70, 72, 73, 74, 75, 78, 79, 88, 89, 90, 91, 92, 93, 94, 95, 99, 100, 103, 104, 105, 107, 108, 109, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 130, 131, 132, 133, 134, 137, 901000000000029) -- 排除特定产品
		AND li.u_volume > 0 -- 只包含正CV值
)
SELECT
	op.effective_distributor_id AS distributor_id, -- 最终归属的分销商 ID
	to_char(op.completed_at, 'YYYYMM') AS original_period, -- CV 归属月份
	op.completed_at AS event_date, -- 事件发生时间
	'order_completion' AS event_type, -- 事件类型
	op.u_volume AS cv_amount, -- CV 金额 (正)
	op.order_id AS source_order_id, -- 关联订单 ID
	op.line_item_id AS source_line_item_id, -- 关联行项目 ID
	NULL::bigint AS source_refund_id, -- 退款 ID 为空
	ui.firstname || ' ' || ui.lastname AS distributor_name -- 分销商姓名
FROM
	OrderParticipants op
	JOIN user_infos ui ON op.effective_distributor_id = ui.distributor_id -- 获取分销商信息
WHERE
	op.effective_distributor_id IS NOT NULL -- 确保有关联到的分销商
	AND ui.role_code = 'D'; -- 确保是分销商
	

refund_cv.sql

-- 退款订单数据清单（1.1 - 4.30）
WITH RefundParticipants AS (
	-- 识别与退款关联的原始订单的有效分销商
	SELECT
		CASE WHEN ru.role_id = 2 THEN
			o.distributor_id -- 假设role_id=2表示分销商
		ELSE
			o.personal_sponsor_distributor_id -- 其他情况（如客户订单）
		END AS effective_distributor_id,
		o.id AS original_order_id,
		o.completed_at AS original_completed_at,
		li.id AS original_line_item_id,
		li.u_volume AS original_u_volume,
		li.quantity AS original_quantity,
		ra.id AS refund_id,
		ra.updated_at AS refund_processed_at,
		rad.quantity AS refunded_quantity
	FROM
		return_authorizations ra
		JOIN return_authorization_details rad ON ra.id = rad.return_authorization_id
			AND rad.source_type = 'lineitem'
		JOIN line_items li ON rad.source_id = li.id
		JOIN orders o ON li.order_id = o.id
		JOIN variants v ON li.variant_id = v.id
		JOIN products p ON v.product_id = p.id
		JOIN users u ON o.user_id = u.id
		JOIN roles_users ru ON u.id = ru.user_id
	WHERE
		ra.updated_at >= '2025-01-01'
		AND ra.updated_at < '2025-05-01'
		AND ra.state = 'received'
		AND p.id NOT IN (5, 23, 24, 25, 59, 60, 61, 65, 66, 67, 69, 70, 72, 73, 74, 75, 78, 79, 88, 89, 90, 91, 92, 93, 94, 95, 99, 100, 103, 104, 105, 107, 108, 109, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 130, 131, 132, 133, 134, 137, 901000000000029)
		AND li.quantity > 0 -- 保留这个条件以避免除零错误
)
SELECT
	rp.effective_distributor_id AS distributor_id, -- 最终归属的分销商 ID
	to_char(rp.original_completed_at, 'YYYYMM') AS original_period, -- CV 归属月份 (基于原始订单)
	rp.refund_processed_at AS event_date, -- 事件发生时间 (退款处理时间)
	'refund_processed' AS event_type, -- 事件类型
	CASE WHEN rp.original_quantity > 0 THEN
		round(rp.original_u_volume * -1 * (rp.refunded_quantity::numeric / rp.original_quantity::numeric), 4)
	ELSE
		0
	END AS cv_amount, -- 计算负 CV
	rp.original_order_id AS source_order_id, -- 关联原始订单 ID
	rp.original_line_item_id AS source_line_item_id, -- 关联原始行项目 ID
	rp.refund_id AS source_refund_id, -- 关联退款 ID
	ui.firstname || ' ' || ui.lastname AS distributor_name -- 分销商姓名
FROM
	RefundParticipants rp
	LEFT JOIN user_infos ui ON rp.effective_distributor_id = ui.distributor_id
WHERE
	rp.effective_distributor_id IS NOT NULL
	AND ui.role_code = 'D' -- 确保是分销商
	AND ui.entry_date::date <= rp.refund_processed_at::date -- 确保分销商在退款时已入职
	AND CASE WHEN rp.original_quantity > 0 THEN
		round(rp.original_u_volume * -1 * (rp.refunded_quantity::numeric / rp.original_quantity::numeric), 4)
	ELSE
		0
	END != 0; -- 确保计算出的 CV 不为零