alert('请选择一个有效的客户'); window.location.href='rebate_summary.php';";
exit;
}
// 获取客户信息
$customerQuery = $conn->query("SELECT c.id, c.cs_code, c.cs_company
FROM customer c
WHERE c.id = " . $customerId);
if (!$customerQuery || $customerQuery->num_rows == 0) {
echo "";
exit;
}
$customerInfo = $customerQuery->fetch_assoc();
// 获取可兑换的返点信息 - 从订单项目中获取
$rebateQuery = "
SELECT
o.id AS order_id,
o.order_code,
o.order_date,
oi.id AS order_item_id,
oi.product_id,
p.ProductName,
oi.quantity,
oi.unit,
(
SELECT rr.id
FROM rebate_rules rr
WHERE rr.product_id = oi.product_id
AND rr.min_quantity <= (
SELECT SUM(oi2.quantity)
FROM order_items oi2
JOIN orders o2 ON oi2.order_id = o2.id
WHERE o2.customer_id = o.customer_id
AND oi2.product_id = oi.product_id
AND o2.order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
AND NOT EXISTS (
SELECT 1
FROM rebate_redemption_items rri
WHERE rri.order_item_id = oi2.id
)
)
ORDER BY rr.min_quantity DESC
LIMIT 1
) AS rebate_rule_id,
(
SELECT rr.rebate_amount
FROM rebate_rules rr
WHERE rr.product_id = oi.product_id
AND rr.min_quantity <= (
SELECT SUM(oi2.quantity)
FROM order_items oi2
JOIN orders o2 ON oi2.order_id = o2.id
WHERE o2.customer_id = o.customer_id
AND oi2.product_id = oi.product_id
AND o2.order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
AND NOT EXISTS (
SELECT 1
FROM rebate_redemption_items rri
WHERE rri.order_item_id = oi2.id
)
)
ORDER BY rr.min_quantity DESC
LIMIT 1
) AS rebate_amount,
oi.quantity * (
SELECT rr.rebate_amount
FROM rebate_rules rr
WHERE rr.product_id = oi.product_id
AND rr.min_quantity <= (
SELECT SUM(oi2.quantity)
FROM order_items oi2
JOIN orders o2 ON oi2.order_id = o2.id
WHERE o2.customer_id = o.customer_id
AND oi2.product_id = oi.product_id
AND o2.order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
AND NOT EXISTS (
SELECT 1
FROM rebate_redemption_items rri
WHERE rri.order_item_id = oi2.id
)
)
ORDER BY rr.min_quantity DESC
LIMIT 1
) AS item_rebate_total
FROM
orders o
JOIN
order_items oi ON o.id = oi.order_id
JOIN
products p ON oi.product_id = p.id
WHERE
o.customer_id = $customerId
AND o.order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
AND p.rebate = 1
AND NOT EXISTS (
SELECT 1
FROM rebate_redemption_items rri
WHERE rri.order_item_id = oi.id
)
-- 确保该订单项有返点规则可用
AND EXISTS (
SELECT 1
FROM rebate_rules rr
WHERE rr.product_id = oi.product_id
AND rr.min_quantity <= (
SELECT SUM(oi2.quantity)
FROM order_items oi2
JOIN orders o2 ON oi2.order_id = o2.id
WHERE o2.customer_id = o.customer_id
AND oi2.product_id = oi.product_id
AND o2.order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
AND NOT EXISTS (
SELECT 1
FROM rebate_redemption_items rri
WHERE rri.order_item_id = oi2.id
)
)
)
ORDER BY
o.order_date DESC, p.ProductName ASC";
$rebateResult = $conn->query($rebateQuery);
if (!$rebateResult) {
echo "";
exit;
}
// 计算总返点金额
$totalRebateAmount = 0;
$rebateItems = [];
while ($row = $rebateResult->fetch_assoc()) {
$rebateItems[] = $row;
$totalRebateAmount += floatval($row['item_rebate_total']);
}
// 如果没有可兑换的返点，返回列表页
if (count($rebateItems) == 0) {
echo "";
exit;
}
?>
返点兑换