query($query); if ($result && $row = $result->fetch_assoc()) { $current_permission_role = $row['em_permission_role_id']; } // 检查是否为导出请求 $is_export = isset($_GET['export']) && $_GET['export'] == 'excel'; // 如果是导出请求但当前用户不是管理员，则拒绝导出 if ($is_export && $current_permission_role != 1) { // 不允许导出，重定向回当前页面(不带export参数) $redirect_url = strtok($_SERVER['REQUEST_URI'], '?') . '?' . http_build_query(array_diff_key($_GET, ['export' => '', 'type' => ''])); echo ""; exit; } // 获取日期范围参数 $date_params = getDateRangeParams(); $start_date = $date_params['start_date_sql']; $end_date = $date_params['end_date_sql']; $date_range = $date_params['date_range']; $period = $date_params['period']; // 如果不是导出操作，则包含页面头部 if (!$is_export) { include('statistics_header.php'); } /** * 获取业务员业绩的客户构成 */ function getCustomerCompositionStats($conn, $start_date, $end_date, $employee_filter = null) { // 查询业务员的新客户业绩（客户编码以0开头的） $new_customer_sql = "SELECT e.id AS employee_id, e.em_user AS employee_name, COUNT(DISTINCT c.id) AS customer_count, SUM(o.total_amount) AS total_amount FROM orders o JOIN customer c ON o.customer_id = c.id JOIN employee e ON c.cs_belong = e.id WHERE o.order_date BETWEEN '$start_date' AND '$end_date' AND c.cs_deal = 3 AND c.cs_code LIKE '%.0%'"; // 查询业务员的老客户业绩（客户编码以3开头且不包含斜杠的） $old_customer_sql = "SELECT e.id AS employee_id, e.em_user AS employee_name, COUNT(DISTINCT c.id) AS customer_count, SUM(o.total_amount) AS total_amount FROM orders o JOIN customer c ON o.customer_id = c.id JOIN employee e ON c.cs_belong = e.id WHERE o.order_date BETWEEN '$start_date' AND '$end_date' AND c.cs_deal = 3 AND c.cs_code LIKE '%.3%' AND c.cs_code NOT LIKE '%/%'"; // 查询业务员的分配客户业绩（客户编码包含斜杠的） $assigned_customer_sql = "SELECT e.id AS employee_id, e.em_user AS employee_name, COUNT(DISTINCT c.id) AS customer_count, SUM(o.total_amount) AS total_amount FROM orders o JOIN customer c ON o.customer_id = c.id JOIN employee e ON c.cs_belong = e.id WHERE o.order_date BETWEEN '$start_date' AND '$end_date' AND c.cs_deal = 3 AND c.cs_code LIKE '%/%'"; // 查询业务员的总业绩 $total_sql = "SELECT e.id AS employee_id, e.em_user AS employee_name, COUNT(DISTINCT c.id) AS customer_count, SUM(o.total_amount) AS total_amount FROM orders o JOIN customer c ON o.customer_id = c.id JOIN employee e ON c.cs_belong = e.id WHERE o.order_date BETWEEN '$start_date' AND '$end_date' AND c.cs_deal = 3"; // 根据员工过滤条件添加WHERE子句 if ($employee_filter !== null) { if (is_array($employee_filter)) { if (!empty($employee_filter)) { $employee_ids = implode(',', array_map('intval', $employee_filter)); $new_customer_sql .= " AND c.cs_belong IN ($employee_ids)"; $old_customer_sql .= " AND c.cs_belong IN ($employee_ids)"; $assigned_customer_sql .= " AND c.cs_belong IN ($employee_ids)"; $total_sql .= " AND c.cs_belong IN ($employee_ids)"; } } else { $employee_id = intval($employee_filter); $new_customer_sql .= " AND c.cs_belong = $employee_id"; $old_customer_sql .= " AND c.cs_belong = $employee_id"; $assigned_customer_sql .= " AND c.cs_belong = $employee_id"; $total_sql .= " AND c.cs_belong = $employee_id"; } } $new_customer_sql .= " GROUP BY e.id ORDER BY total_amount DESC"; $old_customer_sql .= " GROUP BY e.id ORDER BY total_amount DESC"; $assigned_customer_sql .= " GROUP BY e.id ORDER BY total_amount DESC"; $total_sql .= " GROUP BY e.id ORDER BY total_amount DESC"; $new_result = $conn->query($new_customer_sql); $old_result = $conn->query($old_customer_sql); $assigned_result = $conn->query($assigned_customer_sql); $total_result = $conn->query($total_sql); $new_data = []; $old_data = []; $assigned_data = []; $total_data = []; if ($new_result) { while ($row = $new_result->fetch_assoc()) { $new_data[$row['employee_id']] = $row; } } if ($old_result) { while ($row = $old_result->fetch_assoc()) { $old_data[$row['employee_id']] = $row; } } if ($assigned_result) { while ($row = $assigned_result->fetch_assoc()) { $assigned_data[$row['employee_id']] = $row; } } if ($total_result) { while ($row = $total_result->fetch_assoc()) { $total_data[$row['employee_id']] = $row; } } // 合并数据 $combined_data = []; foreach ($total_data as $employee_id => $total) { $combined_data[$employee_id] = [ 'employee_id' => $employee_id, 'employee_name' => $total['employee_name'], 'total_customer_count' => $total['customer_count'], 'total_amount' => $total['total_amount'], 'new_customer_count' => $new_data[$employee_id]['customer_count'] ?? 0, 'new_customer_amount' => $new_data[$employee_id]['total_amount'] ?? 0, 'old_customer_count' => $old_data[$employee_id]['customer_count'] ?? 0, 'old_customer_amount' => $old_data[$employee_id]['total_amount'] ?? 0, 'assigned_customer_count' => $assigned_data[$employee_id]['customer_count'] ?? 0, 'assigned_customer_amount' => $assigned_data[$employee_id]['total_amount'] ?? 0 ]; } // 按总业绩降序排序 usort($combined_data, function($a, $b) { return $b['total_amount'] - $a['total_amount']; }); return $combined_data; } /** * 获取按月统计的客户构成业绩 */ function getMonthlyCustomerComposition($conn, $start_date, $end_date, $employee_filter = null) { // 查询每月新客户业绩（客户编码以0开头的） $new_customer_sql = "SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month, COUNT(DISTINCT c.id) AS customer_count, SUM(o.total_amount) AS total_amount FROM orders o JOIN customer c ON o.customer_id = c.id WHERE o.order_date BETWEEN '$start_date' AND '$end_date' AND c.cs_deal = 3 AND c.cs_code LIKE '%.0%'"; // 查询每月老客户业绩（客户编码以3开头且不包含斜杠的） $old_customer_sql = "SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month, COUNT(DISTINCT c.id) AS customer_count, SUM(o.total_amount) AS total_amount FROM orders o JOIN customer c ON o.customer_id = c.id WHERE o.order_date BETWEEN '$start_date' AND '$end_date' AND c.cs_deal = 3 AND c.cs_code LIKE '%.3%' AND c.cs_code NOT LIKE '%/%'"; // 查询每月分配客户业绩（客户编码包含斜杠的） $assigned_customer_sql = "SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month, COUNT(DISTINCT c.id) AS customer_count, SUM(o.total_amount) AS total_amount FROM orders o JOIN customer c ON o.customer_id = c.id WHERE o.order_date BETWEEN '$start_date' AND '$end_date' AND c.cs_deal = 3 AND c.cs_code LIKE '%/%'"; // 查询每月总业绩 $total_sql = "SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month, COUNT(DISTINCT c.id) AS customer_count, SUM(o.total_amount) AS total_amount FROM orders o JOIN customer c ON o.customer_id = c.id WHERE o.order_date BETWEEN '$start_date' AND '$end_date' AND c.cs_deal = 3"; // 根据员工过滤条件添加WHERE子句 if ($employee_filter !== null) { if (is_array($employee_filter)) { if (!empty($employee_filter)) { $employee_ids = implode(',', array_map('intval', $employee_filter)); $new_customer_sql .= " AND c.cs_belong IN ($employee_ids)"; $old_customer_sql .= " AND c.cs_belong IN ($employee_ids)"; $assigned_customer_sql .= " AND c.cs_belong IN ($employee_ids)"; $total_sql .= " AND c.cs_belong IN ($employee_ids)"; } } else { $employee_id = intval($employee_filter); $new_customer_sql .= " AND c.cs_belong = $employee_id"; $old_customer_sql .= " AND c.cs_belong = $employee_id"; $assigned_customer_sql .= " AND c.cs_belong = $employee_id"; $total_sql .= " AND c.cs_belong = $employee_id"; } } $new_customer_sql .= " GROUP BY DATE_FORMAT(o.order_date, '%Y-%m') ORDER BY month"; $old_customer_sql .= " GROUP BY DATE_FORMAT(o.order_date, '%Y-%m') ORDER BY month"; $assigned_customer_sql .= " GROUP BY DATE_FORMAT(o.order_date, '%Y-%m') ORDER BY month"; $total_sql .= " GROUP BY DATE_FORMAT(o.order_date, '%Y-%m') ORDER BY month"; $new_result = $conn->query($new_customer_sql); $old_result = $conn->query($old_customer_sql); $assigned_result = $conn->query($assigned_customer_sql); $total_result = $conn->query($total_sql); $new_data = []; $old_data = []; $assigned_data = []; $total_data = []; if ($new_result) { while ($row = $new_result->fetch_assoc()) { $new_data[$row['month']] = $row; } } if ($old_result) { while ($row = $old_result->fetch_assoc()) { $old_data[$row['month']] = $row; } } if ($assigned_result) { while ($row = $assigned_result->fetch_assoc()) { $assigned_data[$row['month']] = $row; } } if ($total_result) { while ($row = $total_result->fetch_assoc()) { $total_data[$row['month']] = $row; } } // 合并数据 $combined_data = []; foreach ($total_data as $month => $total) { $combined_data[$month] = [ 'month' => $month, 'total_customer_count' => $total['customer_count'], 'total_amount' => $total['total_amount'], 'new_customer_count' => $new_data[$month]['customer_count'] ?? 0, 'new_customer_amount' => $new_data[$month]['total_amount'] ?? 0, 'old_customer_count' => $old_data[$month]['customer_count'] ?? 0, 'old_customer_amount' => $old_data[$month]['total_amount'] ?? 0, 'assigned_customer_count' => $assigned_data[$month]['customer_count'] ?? 0, 'assigned_customer_amount' => $assigned_data[$month]['total_amount'] ?? 0 ]; } // 按月份排序 ksort($combined_data); return array_values($combined_data); } /** * 导出数据为CSV */ function exportToCSV($data, $columns, $filename) { // 设置头信息 header('Content-Type: text/csv; charset=utf-8'); header('Content-Disposition: attachment;filename="' . $filename . '.csv"'); header('Cache-Control: max-age=0'); // 创建输出流 $output = fopen('php://output', 'w'); // 添加UTF-8 BOM以确保Excel正确显示中文 fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF)); // 输出列头 fputcsv($output, $columns); // 输出数据行 foreach ($data as $row) { // 确保所有数据都是数组格式 $rowData = array_values($row); fputcsv($output, $rowData); } fclose($output); exit; } /** * 渲染业务员客户构成统计表格 */ function renderCustomerCompositionTable($data, $is_export = false) { if (empty($data)) { if (!$is_export) { echo '
当前选择范围内没有业绩数据
'; } return; } // 准备数据 $table_data = []; foreach ($data as $item) { $table_data[] = [ '业务员' => $item['employee_name'], '总业绩' => $is_export ? $item['total_amount'] : formatCurrency($item['total_amount']), '新客户业绩' => $is_export ? $item['new_customer_amount'] : formatCurrency($item['new_customer_amount']), '新客户占比' => $is_export ? ($item['total_amount'] > 0 ? ($item['new_customer_amount'] / $item['total_amount']) * 100 : 0) : formatPercentage($item['new_customer_amount'], $item['total_amount']), '老客户业绩' => $is_export ? $item['old_customer_amount'] : formatCurrency($item['old_customer_amount']), '老客户占比' => $is_export ? ($item['total_amount'] > 0 ? ($item['old_customer_amount'] / $item['total_amount']) * 100 : 0) : formatPercentage($item['old_customer_amount'], $item['total_amount']), '分配客户业绩' => $is_export ? $item['assigned_customer_amount'] : formatCurrency($item['assigned_customer_amount']), '分配客户占比' => $is_export ? ($item['total_amount'] > 0 ? ($item['assigned_customer_amount'] / $item['total_amount']) * 100 : 0) : formatPercentage($item['assigned_customer_amount'], $item['total_amount']) ]; } // 如果是导出请求，则导出数据 if ($is_export) { exportToCSV( $table_data, ['业务员', '总业绩', '新客户业绩', '新客户占比(%)', '老客户业绩', '老客户占比(%)', '分配客户业绩', '分配客户占比(%)'], '业务员客户构成统计_' . date('Ymd') ); return; } // 渲染表格 echo '
'; echo '
'; echo '
'; echo '
'; echo '业务员客户构成统计'; // 只有管理员才显示导出按钮 if ($GLOBALS['current_permission_role'] == 1) { echo '导出CSV'; } echo '
'; echo '
'; echo '
'; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; foreach ($data as $item) { echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; } echo ''; echo '
业务员总业绩新客户业绩新客户占比老客户业绩老客户占比分配客户业绩分配客户占比
' . $item['employee_name'] . '' . formatCurrency($item['total_amount']) . '' . formatCurrency($item['new_customer_amount']) . '' . formatPercentage($item['new_customer_amount'], $item['total_amount']) . '' . formatCurrency($item['old_customer_amount']) . '' . formatPercentage($item['old_customer_amount'], $item['total_amount']) . '' . formatCurrency($item['assigned_customer_amount']) . '' . formatPercentage($item['assigned_customer_amount'], $item['total_amount']) . '
'; echo '
'; echo '
'; echo '
'; echo '
'; echo '
'; } /** * 渲染每月客户构成统计表格 */ function renderMonthlyCustomerCompositionTable($data, $is_export = false) { if (empty($data)) { if (!$is_export) { echo '
当前选择范围内没有月度业绩数据
'; } return; } // 准备数据 $table_data = []; foreach ($data as $item) { $table_data[] = [ '月份' => $item['month'], '总业绩' => $is_export ? $item['total_amount'] : formatCurrency($item['total_amount']), '新客户业绩' => $is_export ? $item['new_customer_amount'] : formatCurrency($item['new_customer_amount']), '新客户占比' => $is_export ? ($item['total_amount'] > 0 ? ($item['new_customer_amount'] / $item['total_amount']) * 100 : 0) : formatPercentage($item['new_customer_amount'], $item['total_amount']), '老客户业绩' => $is_export ? $item['old_customer_amount'] : formatCurrency($item['old_customer_amount']), '老客户占比' => $is_export ? ($item['total_amount'] > 0 ? ($item['old_customer_amount'] / $item['total_amount']) * 100 : 0) : formatPercentage($item['old_customer_amount'], $item['total_amount']), '分配客户业绩' => $is_export ? $item['assigned_customer_amount'] : formatCurrency($item['assigned_customer_amount']), '分配客户占比' => $is_export ? ($item['total_amount'] > 0 ? ($item['assigned_customer_amount'] / $item['total_amount']) * 100 : 0) : formatPercentage($item['assigned_customer_amount'], $item['total_amount']) ]; } // 如果是导出请求，则导出数据 if ($is_export) { exportToCSV( $table_data, ['月份', '总业绩', '新客户业绩', '新客户占比(%)', '老客户业绩', '老客户占比(%)', '分配客户业绩', '分配客户占比(%)'], '每月客户构成统计_' . date('Ymd') ); return; } // 渲染表格 echo '
'; echo '
'; echo '
'; echo '
'; echo '每月客户构成统计'; // 只有管理员才显示导出按钮 if ($GLOBALS['current_permission_role'] == 1) { echo '导出CSV'; } echo '
'; echo '
'; echo '
'; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; foreach ($data as $item) { echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; } echo ''; echo '
月份总业绩新客户业绩新客户占比老客户业绩老客户占比分配客户业绩分配客户占比
' . $item['month'] . '' . formatCurrency($item['total_amount']) . '' . formatCurrency($item['new_customer_amount']) . '' . formatPercentage($item['new_customer_amount'], $item['total_amount']) . '' . formatCurrency($item['old_customer_amount']) . '' . formatPercentage($item['old_customer_amount'], $item['total_amount']) . '' . formatCurrency($item['assigned_customer_amount']) . '' . formatPercentage($item['assigned_customer_amount'], $item['total_amount']) . '
'; echo '
'; echo '
'; echo '
'; echo '
'; echo '
'; } // 获取选择的业务员 $selected_employee = isset($_GET['selected_employee']) ? $_GET['selected_employee'] : 'all'; // 确定要显示哪些业务员的数据 $employee_filter = null; if ($selected_employee != 'all') { // 如果选择了特定业务员，则只显示该业务员的数据 $employee_filter = intval($selected_employee); } else { // 否则按权限显示相应的业务员数据 if ($current_permission_role == 1) { // 管理员可以看到所有业务员 $employee_filter = null; } elseif ($current_permission_role == 2) { // 组长可以看到自己和组员 $visible_employees = []; $query = "SELECT id FROM employee WHERE id = " . intval($current_employee_id) . " OR em_role = " . intval($current_employee_id); $result = $conn->query($query); if ($result) { while ($row = $result->fetch_assoc()) { $visible_employees[] = $row['id']; } } $employee_filter = $visible_employees; } else { // 组员只能看到自己 $employee_filter = intval($current_employee_id); } } // 获取业务员客户构成统计数据 $customer_composition_stats = getCustomerCompositionStats($conn, $start_date, $end_date, $employee_filter); // 获取每月客户构成统计数据 $monthly_customer_composition = getMonthlyCustomerComposition($conn, $start_date, $end_date, $employee_filter); // 处理导出请求 if ($is_export) { $export_type = isset($_GET['type']) ? $_GET['type'] : ''; switch ($export_type) { case 'employee': renderCustomerCompositionTable($customer_composition_stats, true); break; case 'monthly': renderMonthlyCustomerCompositionTable($monthly_customer_composition, true); break; } exit; // 确保导出后停止执行 } ?>

业绩客户构成统计