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, MAX(e.em_user) AS employee_name, MAX(c.cs_code) AS customer_code, o.customer_id, SUM(o.total_amount) AS order_amount FROM orders o JOIN customer c ON o.customer_id = c.id JOIN employee e ON c.cs_belong = e.id WHERE o.is_deleted = 0 AND 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, o.customer_id ORDER BY e.id, order_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) { $table_data[] = [ '业务员' => $item['employee_name'], '成交客户编码' => $item['customer_code'], '成交金额' => $is_export ? $item['order_amount'] : formatCurrency($item['order_amount']) ]; } // 如果是导出请求，则导出数据 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 ''; foreach ($data as $item) { echo ''; echo ''; echo ''; echo ''; echo ''; } echo ''; echo '

业务员	成交客户编码	成交金额
'.$item['employee_name'].'	'.$item['customer_code'].'	'.formatCurrency($item['order_amount']).'

'; echo '

'; } /** * 获取团队数据统计 */ function getTeamStatistics($conn, $start_date, $end_date) { $team_data = []; // 获取组长信息 $result = $conn->query("SELECT id, em_user FROM employee WHERE em_role=0 AND em_permission_role_id in(2,3,4)"); while ($row = $result->fetch_assoc()) { $team_leader = [ 'id' => $row['id'], 'name' => $row['em_user'], 'members' => [], 'total_customers' => 0, 'deals' => [], 'sea_customers' => 0, 'claimed_customers' => 0 ]; // 获取组长的总客户数量 $c1Result = $conn->query("SELECT COUNT(DISTINCT c.cs_code) as c1 FROM customer c WHERE c.cs_belong=" . $row['id']); $c1Row = $c1Result->fetch_assoc(); $team_leader['total_customers'] = $c1Row['c1']; // 获取组长的进公海客户总数 $c2Result = $conn->query("SELECT COUNT(c.id) as c2 FROM customer c WHERE c.cs_deal<>3 AND c.cs_deal<>0 AND c.cs_type<>2 AND DATEDIFF(NOW(), c.cs_updatetime) > 90 AND c.cs_belongclient=0 AND c.cs_belong=" . $row['id']); $c2Row = $c2Result->fetch_assoc(); $team_leader['sea_customers'] = $c2Row['c2']; // 获取组长在日期范围内的成交客户 $c3Result = $conn->query("SELECT DISTINCT c.cs_code FROM customer c WHERE c.cs_dealdate > '" . $conn->real_escape_string($start_date) . "' AND c.cs_dealdate <= '" . $conn->real_escape_string($end_date) . "' AND c.cs_deal=3 AND c.cs_belong=" . $row['id']); while ($c3Row = $c3Result->fetch_assoc()) { $team_leader['deals'][] = $c3Row['cs_code']; } // 获取组长的公海认领客户数 $c4Result = $conn->query("SELECT COUNT(DISTINCT c.cs_code) as c4 FROM customer c WHERE c.cs_claimdate > '" . $conn->real_escape_string($start_date) . "' AND c.cs_claimdate <= '" . $conn->real_escape_string($end_date) . "' AND c.cs_belong=" . $row['id']); $c4Row = $c4Result->fetch_assoc(); $team_leader['claimed_customers'] = $c4Row['c4']; // 获取组员数量 $c5Result = $conn->query("SELECT COUNT(id) as c5 FROM employee WHERE em_role=" . $row['id']); $c5Row = $c5Result->fetch_assoc(); $team_leader['member_count'] = $c5Row['c5']; // 获取组员数据 $memberResult = $conn->query("SELECT id, em_user FROM employee WHERE em_role=" . $row['id']); while ($memberRow = $memberResult->fetch_assoc()) { $member = [ 'id' => $memberRow['id'], 'name' => $memberRow['em_user'], 'total_customers' => 0, 'deals' => [], 'sea_customers' => 0, 'claimed_customers' => 0 ]; // 获取组员的总客户数量 $mc1Result = $conn->query("SELECT COUNT(DISTINCT c.cs_code) as c1 FROM customer c WHERE c.cs_belong=" . $memberRow['id']); $mc1Row = $mc1Result->fetch_assoc(); $member['total_customers'] = $mc1Row['c1']; // 获取组员的进公海客户总数 $mc2Result = $conn->query("SELECT COUNT(DISTINCT c.cs_code) as c2 FROM customer c WHERE c.cs_deal<>3 AND c.cs_deal<>0 AND c.cs_type<>2 AND DATEDIFF(NOW(), c.cs_updatetime) > 90 AND c.cs_belongclient=0 AND c.cs_belong=" . $memberRow['id']); $mc2Row = $mc2Result->fetch_assoc(); $member['sea_customers'] = $mc2Row['c2']; // 获取组员在日期范围内的成交客户 $mc3Result = $conn->query("SELECT DISTINCT c.cs_code FROM customer c WHERE c.cs_dealdate > '" . $conn->real_escape_string($start_date) . "' AND c.cs_dealdate <= '" . $conn->real_escape_string($end_date) . "' AND c.cs_deal=3 AND c.cs_belong=" . $memberRow['id']); while ($mc3Row = $mc3Result->fetch_assoc()) { $member['deals'][] = $mc3Row['cs_code']; } // 获取组员的公海认领客户数 $mc4Result = $conn->query("SELECT COUNT(c.id) as c4 FROM customer c WHERE c.cs_claimdate > '" . $conn->real_escape_string($start_date) . "' AND c.cs_claimdate <= '" . $conn->real_escape_string($end_date) . "' AND c.cs_belong=" . $memberRow['id']); $mc4Row = $mc4Result->fetch_assoc(); $member['claimed_customers'] = $mc4Row['c4']; $team_leader['members'][] = $member; } $team_data[] = $team_leader; } return $team_data; } /** * 渲染团队统计表格 */ function renderTeamStatisticsTable($data, $is_export = false) { if (empty($data)) { if (!$is_export) { echo '

当前选择范围内没有团队数据

'; } return; } // 准备导出数据 if ($is_export) { $export_data = []; foreach ($data as $team) { // 添加组长行 $export_data[] = [ '组长' => $team['name'], '组员' => $team['name'], '总客户数量' => $team['total_customers'], '本月成交' => implode(', ', $team['deals']), '进公海客户总数' => $team['sea_customers'], '公海认领' => $team['claimed_customers'] ]; // 添加组员行 foreach ($team['members'] as $member) { $export_data[] = [ '组长' => '', '组员' => $member['name'], '总客户数量' => $member['total_customers'], '本月成交' => implode(', ', $member['deals']), '进公海客户总数' => $member['sea_customers'], '公海认领' => $member['claimed_customers'] ]; } } exportToCSV( $export_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 ''; echo ''; echo ''; foreach ($data as $team) { $member_count = count($team['members']); echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; foreach ($team['members'] as $member) { echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; } } echo ''; echo '

组长	组员	总客户数量	本月成交	进公海客户总数	公海认领
' . htmlspecialchars($team['name']) . '	' . htmlspecialchars($team['name']) . '	' . $team['total_customers'] . '	' . implode(' ', $team['deals']) . '	' . $team['sea_customers'] . '	' . $team['claimed_customers'] . '
' . htmlspecialchars($member['name']) . '	' . $member['total_customers'] . '	' . implode(' ', $member['deals']) . '	' . $member['sea_customers'] . '	' . $member['claimed_customers'] . '

'; 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); // 获取团队统计数据 $team_statistics = getTeamStatistics($conn, $start_date, $end_date); // 处理导出请求 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; case 'team': renderTeamStatisticsTable($team_statistics, true); break; } exit; // 确保导出后停止执行 } ?>

每月成交客户统计