mtb_crm_asp/export_rebate_details.php

<?php
require_once 'conn.php';
checkLogin();

// 获取返点兑换ID
$redemptionId = isset($_GET['redemption_id']) ? intval($_GET['redemption_id']) : 0;

if ($redemptionId <= 0) {
    die('无效的兑换ID');
}

// 验证权限
$employee_id = $_SESSION['employee_id'];
$isAdmin = checkIfAdmin();

// 检查是否为财务角色或管理员
$isFinance = false;
$checkRoleSql = "SELECT em_permission_role_id FROM employee WHERE id = $employee_id";
$roleResult = mysqli_query($conn, $checkRoleSql);
if ($roleResult && $row = mysqli_fetch_assoc($roleResult)) {
    $isFinance = ($row['em_permission_role_id'] == 6 || $row['em_permission_role_id'] == 1);
}

// 获取客户信息和返点兑换基本信息
$customerSql = "SELECT 
                  c.cs_code, 
                  c.cs_company,
                  rr.redemption_date,
                  rr.total_rebate_amount
               FROM 
                  rebate_redemptions rr
               JOIN 
                  customer c ON rr.customer_id = c.id
               WHERE 
                  rr.id = ?";

$customerStmt = $conn->prepare($customerSql);
$customerStmt->bind_param("i", $redemptionId);
$customerStmt->execute();
$customerResult = $customerStmt->get_result();

if (!$customerResult || $customerResult->num_rows == 0) {
    die('未找到返点兑换记录');
}

$customerInfo = $customerResult->fetch_assoc();

// 获取返点兑换详情
$sql = "SELECT 
            rri.id,
            rri.order_id,
            rri.order_item_id,
            rri.product_id,
            rri.quantity,
            rri.rebate_amount,
            rri.rebate_rule_id,
            p.ProductName AS product_name,
            o.order_code,
            o.order_date,
            o.shipping_date,
            oi.unit,
            (SELECT rr.rebate_amount FROM rebate_rules rr WHERE rr.id = rri.rebate_rule_id) AS rule_amount
        FROM 
            rebate_redemption_items rri
        JOIN 
            products p ON rri.product_id = p.id
        JOIN 
            orders o ON rri.order_id = o.id
        JOIN 
            order_items oi ON rri.order_item_id = oi.id
        JOIN
            rebate_redemptions rr ON rri.redemption_id = rr.id
        JOIN
            customer c ON rr.customer_id = c.id
        WHERE 
            rri.redemption_id = ?";

// 非管理员和非财务只能查看自己客户的数据
if (!$isAdmin && !$isFinance) {
    $sql .= " AND c.cs_belong = $employee_id";
}

$sql .= " ORDER BY o.order_code, p.ProductName";

// 使用预处理语句防止SQL注入
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $redemptionId);
$stmt->execute();
$result = $stmt->get_result();

if (!$result) {
    die('查询失败: ' . $conn->error);
}

// 设置响应头，指定文件类型为CSV
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="返点详情_' . textDecode($customerInfo['cs_code']) . '_' . date('Ymd') . '.csv"');

// 创建一个文件指针，连接到PHP的输出流
$output = fopen('php://output', 'w');

// 添加UTF-8 BOM以确保Excel正确识别中文
fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));

// 写入CSV文件头信息
fputcsv($output, [
    '客户编码: ' . $customerInfo['cs_code'],
    '客户名称: ' . $customerInfo['cs_company'],
    '兑换日期: ' . date('Y-m-d', strtotime($customerInfo['redemption_date'])),
    '总返点金额: ' . number_format($customerInfo['total_rebate_amount'], 2) . ' 元'
]);

// 空行
fputcsv($output, []);

// 写入CSV标题行
fputcsv($output, [
    '订单编号',
    '订单日期',
    '出货日期',
    '产品名称',
    '数量',
    '单位',
    '返点单价(元/件)',
    '返点金额(元)'
]);

// 写入数据行
$totalRebate = 0;
while ($row = $result->fetch_assoc()) {
    // 使用规则表中的单位返点金额，而不是存储的总返点金额
    $unitRebate = isset($row['rule_amount']) ? $row['rule_amount'] : $row['rebate_amount'];
    
    // 计算每项的总返点金额
    $itemTotalRebate = $row['quantity'] * $unitRebate;
    $totalRebate += $itemTotalRebate;
    
    // 格式化日期
    $orderDate = !empty($row['order_date']) ? date('Y-m-d', strtotime($row['order_date'])) : '-';
    $shippingDate = !empty($row['shipping_date']) ? date('Y-m-d', strtotime($row['shipping_date'])) : '-';
    
    fputcsv($output, [
        $row['order_code'],
        $orderDate,
        $shippingDate,
        $row['product_name'],
        $row['quantity'],
        $row['unit'],
        number_format($unitRebate, 2),
        number_format($itemTotalRebate, 2)
    ]);
}

// 写入合计行
fputcsv($output, []);
fputcsv($output, ['', '', '', '', '', '', '合计:', number_format($totalRebate, 2)]);

// 关闭文件指针
fclose($output);
exit;
?>