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 getMonthlyRegionPerformance($conn, $start_date, $end_date, $employee_filter = null) { $sql = "SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month, co.countryName AS region, 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 country co ON c.cs_country = co.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 DATE_FORMAT(o.order_date, '%Y-%m'), co.id ORDER BY month, total_amount DESC"; $result = $conn->query($sql); $data = []; if ($result) { while ($row = $result->fetch_assoc()) { $data[] = $row; } } return $data; } /** * 获取区域业绩汇总数据 */ function getRegionPerformanceSummary($conn, $start_date, $end_date, $employee_filter = null) { $sql = "SELECT co.countryName AS region, 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 country co ON c.cs_country = co.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 co.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 renderMonthlyRegionPerformanceTable($data, $is_export = false) { if (empty($data)) { if (!$is_export) { echo '

当前选择范围内没有区域业绩数据

'; } return; } // 准备数据 $table_data = []; $total_amount = 0; $total_customers = 0; // 按月份分组整理数据 $months_data = []; foreach ($data as $item) { $months_data[$item['month']][] = $item; $total_amount += floatval($item['total_amount']); $total_customers += intval($item['customer_count']); } foreach ($months_data as $month => $regions) { foreach ($regions as $region) { $table_data[] = [ '月份' => $month, '区域' => $region['region'], '新成交客户数' => $region['customer_count'], '成交金额' => $is_export ? $region['total_amount'] : formatCurrency($region['total_amount']) ]; } } // 如果是导出请求，则导出数据 if ($is_export) { exportToCSV( $table_data, ['月份', '区域', '新成交客户数', '成交金额'], '每月区域新客户业绩_' . date('Ymd') ); return; } // 渲染表格 echo '

'; echo '

'; echo '每月区域新客户业绩明细 (总计: '.formatCurrency($total_amount).', 共'.$total_customers.'个新客户)'; // 只有管理员才显示导出按钮 if ($GLOBALS['current_permission_role'] == 1) { echo '导出CSV'; } echo '

'; echo '

'; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; $current_month = ''; foreach ($months_data as $month => $regions) { foreach ($regions as $index => $region) { echo ''; // 只在每个月份的第一行显示月份 if ($index === 0) { echo ''; } echo ''; echo ''; echo ''; echo ''; } } echo ''; echo '

月份	区域	新成交客户数	成交金额
' . $month . '	' . $region['region'] . '	' . $region['customer_count'] . '	' . formatCurrency($region['total_amount']) . '

'; echo '

'; } /** * 渲染区域业绩汇总表格 */ function renderRegionPerformanceSummaryTable($data, $is_export = false) { if (empty($data)) { if (!$is_export) { echo '

当前选择范围内没有区域业绩汇总数据

'; } return; } // 准备数据 $table_data = []; $total_amount = 0; $total_customers = 0; foreach ($data as $item) { $table_data[] = [ '区域' => $item['region'], '新成交客户数' => $item['customer_count'], '成交金额' => $is_export ? $item['total_amount'] : formatCurrency($item['total_amount']) ]; $total_amount += floatval($item['total_amount']); $total_customers += intval($item['customer_count']); } // 如果是导出请求，则导出数据 if ($is_export) { exportToCSV( $table_data, ['区域', '新成交客户数', '成交金额'], '区域新客户业绩汇总_' . date('Ymd') ); return; } // 渲染表格 echo '

'; echo '

'; echo '区域新客户业绩汇总 (总计: '.formatCurrency($total_amount).', 共'.$total_customers.'个新客户)'; // 只有管理员才显示导出按钮 if ($GLOBALS['current_permission_role'] == 1) { echo '导出CSV'; } echo '

'; echo '

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

区域	新成交客户数	成交金额
' . $item['region'] . '	' . $item['customer_count'] . '	' . formatCurrency($item['total_amount']) . '

'; 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_region_performance = getMonthlyRegionPerformance($conn, $start_date, $end_date, $employee_filter); // 获取区域业绩汇总数据 $region_performance_summary = getRegionPerformanceSummary($conn, $start_date, $end_date, $employee_filter); // 处理导出请求 if ($is_export) { $export_type = isset($_GET['type']) ? $_GET['type'] : ''; switch ($export_type) { case 'monthly': renderMonthlyRegionPerformanceTable($monthly_region_performance, true); break; case 'summary': renderRegionPerformanceSummaryTable($region_performance_summary, true); break; } exit; // 确保导出后停止执行 } ?>

每月区域新客户总业绩统计