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 getMonthlyDealCustomers($conn, $start_date, $end_date, $employee_filter = null) { $sql = "SELECT DATE_FORMAT(cs_dealdate, '%Y-%m') AS month, COUNT(*) AS customer_count FROM customer WHERE cs_dealdate BETWEEN '$start_date' AND '$end_date' AND 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)); $sql .= " AND cs_belong IN ($employee_ids)"; } } else { $sql .= " AND cs_belong = " . intval($employee_filter); } } $sql .= " GROUP BY DATE_FORMAT(cs_dealdate, '%Y-%m') ORDER BY month"; $result = $conn->query($sql); $data = []; if ($result) { while ($row = $result->fetch_assoc()) { $data[] = $row; } } return $data; } /** * 获取按业务员统计的成交客户数量和金额 */ function getDealStatsByEmployee($conn, $start_date, $end_date, $employee_filter = null) { $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_dealdate BETWEEN '$start_date' AND '$end_date'"; // 根据员工过滤条件添加WHERE子句 if ($employee_filter !== null) { if (is_array($employee_filter)) { if (!empty($employee_filter)) { $employee_ids = implode(',', array_map('intval', $employee_filter)); $sql .= " AND c.cs_belong IN ($employee_ids)"; } } else { $sql .= " AND c.cs_belong = " . intval($employee_filter); } } $sql .= " GROUP BY e.id ORDER BY total_amount DESC"; $result = $conn->query($sql); $data = []; if ($result) { while ($row = $result->fetch_assoc()) { $data[] = $row; } } return $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 renderMonthlyDealCustomersTable($data, $is_export = false) { if (empty($data)) { if (!$is_export) { echo '

当前选择范围内没有成交客户数据

'; } return; } // 准备数据 $table_data = []; $total_customers = 0; foreach ($data as $item) { $table_data[] = [ '月份' => $item['month'], '新增成交客户数量' => $item['customer_count'] ]; $total_customers += intval($item['customer_count']); } // 如果是导出请求，则导出数据 if ($is_export) { exportToCSV( $table_data, ['月份', '新增成交客户数量'], '每月新增成交客户数量_' . date('Ymd') ); return; } // 渲染表格 echo '

'; echo '

'; echo '每月新增成交客户数量明细 (总计: '.$total_customers.' 客户)'; // 只有管理员才显示导出按钮 if ($GLOBALS['current_permission_role'] == 1) { echo '导出CSV'; } echo '

'; echo '

'; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; foreach ($data as $item) { echo ''; echo ''; echo ''; echo ''; } echo ''; echo '

月份	新增成交客户数量
'.$item['month'].'	'.$item['customer_count'].'

'; echo '

'; } /** * 渲染业务员成交统计表格 */ function renderDealStatsByEmployeeTable($data, $is_export = false) { if (empty($data)) { if (!$is_export) { echo '

当前选择范围内没有业务员成交数据

'; } return; } // 准备数据 $table_data = []; foreach ($data as $item) { $avg_customer_value = $item['customer_count'] > 0 ? $item['total_amount'] / $item['customer_count'] : 0; $table_data[] = [ '业务员' => $item['employee_name'], '成交客户数' => $item['customer_count'], '成交金额' => $is_export ? $item['total_amount'] : formatCurrency($item['total_amount']), '客单价' => $is_export ? $avg_customer_value : formatCurrency($avg_customer_value) ]; } // 如果是导出请求，则导出数据 if ($is_export) { exportToCSV( $table_data, ['业务员', '成交客户数', '成交金额', '客单价'], '业务员成交统计_' . date('Ymd') ); return; } // 渲染表格 echo '

'; echo '

'; echo '业务员成交统计明细'; // 只有管理员才显示导出按钮 if ($GLOBALS['current_permission_role'] == 1) { echo '导出CSV'; } echo '

'; echo '

'; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; foreach ($data as $item) { $avg_customer_value = $item['customer_count'] > 0 ? $item['total_amount'] / $item['customer_count'] : 0; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; } echo ''; echo '

业务员	成交客户数	成交金额	客单价
'.$item['employee_name'].'	'.$item['customer_count'].'	'.formatCurrency($item['total_amount']).'	'.formatCurrency($avg_customer_value).'

'; 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); } } // 获取每月新增成交客户数量数据 $monthly_deal_customers = getMonthlyDealCustomers($conn, $start_date, $end_date, $employee_filter); // 获取业务员成交统计数据 $deal_stats_by_employee = getDealStatsByEmployee($conn, $start_date, $end_date, $employee_filter); // 处理导出请求 if ($is_export) { $export_type = isset($_GET['type']) ? $_GET['type'] : ''; switch ($export_type) { case 'customers': renderMonthlyDealCustomersTable($monthly_deal_customers, true); break; case 'employee': renderDealStatsByEmployeeTable($deal_stats_by_employee, true); break; } exit; // 确保导出后停止执行 } ?>

每月新增成交客户统计