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']; // 设置询盘回溯月份数 $lookback_months = isset($_GET['lookback_months']) ? intval($_GET['lookback_months']) : 12; if ($lookback_months < 1) { $lookback_months = 12; // 默认为12个月 } // 如果不是导出操作，则包含页面头部 if (!$is_export) { include('statistics_header.php'); } /** * 获取按业务员统计的询盘转化率 */ function getEmployeeInquiryConversionRate($conn, $start_date, $end_date, $lookback_months, $employee_filter = null, $channel_filter = null) { // 计算回溯日期（X个月前） $lookback_date = date('Y-m-d', strtotime($start_date . ' -' . $lookback_months . ' months')); $sql = "SELECT e.id AS employee_id, e.em_user AS employee_name, COUNT(DISTINCT CASE WHEN c.cs_deal = 3 AND c.cs_dealdate BETWEEN '$start_date' AND '$end_date' THEN c.id ELSE NULL END) AS deal_count, COUNT(DISTINCT CASE WHEN c.cs_addtime BETWEEN '$lookback_date' AND '$end_date' THEN c.id ELSE NULL END) AS inquiry_count, (SELECT ch_name FROM qudao WHERE id = c.cs_from) AS channel_name FROM customer c JOIN employee e ON c.cs_belong = e.id WHERE c.cs_addtime BETWEEN '$lookback_date' AND '$end_date'"; // 添加渠道筛选条件 if ($channel_filter !== null && $channel_filter != 'all') { $sql .= " AND c.cs_from = " . intval($channel_filter); } // 根据员工过滤条件添加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, c.cs_from ORDER BY e.em_user"; $result = $conn->query($sql); $data = []; if ($result) { while ($row = $result->fetch_assoc()) { // 计算转化率 $conversion_rate = $row['inquiry_count'] > 0 ? $row['deal_count'] / $row['inquiry_count'] : 0; $row['conversion_rate'] = $conversion_rate; $data[] = $row; } } return $data; } /** * 获取按国家和渠道统计的询盘转化率 */ function getCountryChannelConversionRate($conn, $start_date, $end_date, $lookback_months, $employee_filter = null, $channel_filter = null) { // 计算回溯日期（X个月前） $lookback_date = date('Y-m-d', strtotime($start_date . ' -' . $lookback_months . ' months')); $sql = "SELECT co.id AS country_id, co.countryName AS country_name, q.id AS channel_id, q.ch_name AS channel_name, COUNT(DISTINCT CASE WHEN c.cs_deal = 3 AND c.cs_dealdate BETWEEN '$start_date' AND '$end_date' THEN c.id ELSE NULL END) AS deal_count, COUNT(DISTINCT CASE WHEN c.cs_addtime BETWEEN '$lookback_date' AND '$end_date' THEN c.id ELSE NULL END) AS inquiry_count, DATE_FORMAT(c.cs_addtime, '%Y-%m') AS month FROM customer c JOIN country co ON c.cs_country = co.id JOIN qudao q ON c.cs_from = q.id WHERE c.cs_addtime BETWEEN '$lookback_date' AND '$end_date'"; // 添加渠道筛选条件 if ($channel_filter !== null && $channel_filter != 'all') { $sql .= " AND c.cs_from = " . intval($channel_filter); } // 根据员工过滤条件添加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, q.id, DATE_FORMAT(c.cs_addtime, '%Y-%m') ORDER BY co.countryName, q.ch_name, month"; $result = $conn->query($sql); $data = []; if ($result) { while ($row = $result->fetch_assoc()) { // 计算转化率 $conversion_rate = $row['inquiry_count'] > 0 ? $row['deal_count'] / $row['inquiry_count'] : 0; $row['conversion_rate'] = $conversion_rate; $data[] = $row; } } return $data; } /** * 获取按渠道统计的累计询盘转化率 */ function getChannelTotalConversionRate($conn, $start_date, $end_date, $lookback_months, $employee_filter = null, $channel_filter = null) { // 计算回溯日期（X个月前） $lookback_date = date('Y-m-d', strtotime($start_date . ' -' . $lookback_months . ' months')); $sql = "SELECT q.id AS channel_id, q.ch_name AS channel_name, COUNT(DISTINCT CASE WHEN c.cs_deal = 3 AND c.cs_dealdate BETWEEN '$start_date' AND '$end_date' THEN c.id ELSE NULL END) AS deal_count, COUNT(DISTINCT CASE WHEN c.cs_addtime BETWEEN '$lookback_date' AND '$end_date' THEN c.id ELSE NULL END) AS inquiry_count FROM customer c JOIN qudao q ON c.cs_from = q.id WHERE c.cs_addtime BETWEEN '$lookback_date' AND '$end_date'"; // 添加渠道筛选条件 if ($channel_filter !== null && $channel_filter != 'all') { $sql .= " AND c.cs_from = " . intval($channel_filter); } // 根据员工过滤条件添加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 q.id ORDER BY q.ch_name"; $result = $conn->query($sql); $data = []; if ($result) { while ($row = $result->fetch_assoc()) { // 计算转化率 $conversion_rate = $row['inquiry_count'] > 0 ? $row['deal_count'] / $row['inquiry_count'] : 0; $row['conversion_rate'] = $conversion_rate; $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 renderEmployeeConversionRateTable($data, $is_export = false) { if (empty($data)) { if (!$is_export) { echo '

当前选择范围内没有业务员询盘转化率数据

'; } return; } // 准备数据 $table_data = []; foreach ($data as $item) { $table_data[] = [ '业务员' => $item['employee_name'], '渠道' => $item['channel_name'] ?? '未知', '询盘数量' => $item['inquiry_count'], '成交数量' => $item['deal_count'], '转化率' => $is_export ? $item['conversion_rate'] : formatPercentage($item['conversion_rate']) ]; } // 如果是导出请求，则导出数据 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 ''; echo ''; foreach ($data as $item) { echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; } echo ''; echo '

业务员	渠道	询盘数量	成交数量	转化率
'.$item['employee_name'].'	'.($item['channel_name'] ?? '未知').'	'.$item['inquiry_count'].'	'.$item['deal_count'].'	'.formatPercentage($item['conversion_rate']).'

'; echo '

'; } /** * 渲染渠道累计转化率表格 */ function renderChannelTotalConversionRateTable($data, $is_export = false) { if (empty($data)) { if (!$is_export) { echo '

当前选择范围内没有渠道转化率数据

'; } return; } // 准备数据 $table_data = []; foreach ($data as $item) { $table_data[] = [ '渠道' => $item['channel_name'], '询盘数量' => $item['inquiry_count'], '成交数量' => $item['deal_count'], '转化率' => $is_export ? $item['conversion_rate'] : formatPercentage($item['conversion_rate']) ]; } // 如果是导出请求，则导出数据 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) { echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; } echo ''; echo '

渠道	询盘数量	成交数量	转化率
'.$item['channel_name'].'	'.$item['inquiry_count'].'	'.$item['deal_count'].'	'.formatPercentage($item['conversion_rate']).'

'; echo '

'; } /** * 渲染国家/渠道月度转化率表格 */ function renderCountryChannelConversionRateTable($data, $is_export = false) { if (empty($data)) { if (!$is_export) { echo '

当前选择范围内没有国家/渠道月度转化率数据

'; } return; } // 准备数据 $table_data = []; foreach ($data as $item) { $table_data[] = [ '国家' => $item['country_name'], '渠道' => $item['channel_name'], '月份' => $item['month'], '询盘数量' => $item['inquiry_count'], '成交数量' => $item['deal_count'], '转化率' => $is_export ? $item['conversion_rate'] : formatPercentage($item['conversion_rate']) ]; } // 如果是导出请求，则导出数据 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 ''; echo ''; echo ''; foreach ($data as $item) { echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; } echo ''; echo '

国家	渠道	月份	询盘数量	成交数量	转化率
' . $item['country_name'] . '	' . $item['channel_name'] . '	' . $item['month'] . '	' . $item['inquiry_count'] . '	' . $item['deal_count'] . '	' . formatPercentage($item['conversion_rate']) . '

'; echo '

'; } // 获取选择的业务员 $selected_employee = isset($_GET['selected_employee']) ? $_GET['selected_employee'] : 'all'; // 获取选择的渠道 $selected_channel = isset($_GET['selected_channel']) ? $_GET['selected_channel'] : '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); } } // 获取业务员询盘转化率数据 $employee_conversion_rate = getEmployeeInquiryConversionRate($conn, $start_date, $end_date, $lookback_months, $employee_filter, $selected_channel); // 获取渠道累计转化率数据 $channel_total_conversion_rate = getChannelTotalConversionRate($conn, $start_date, $end_date, $lookback_months, $employee_filter, $selected_channel); // 获取国家/渠道月度转化率数据 $country_channel_conversion_rate = getCountryChannelConversionRate($conn, $start_date, $end_date, $lookback_months, $employee_filter, $selected_channel); // 处理导出请求 if ($is_export) { $export_type = isset($_GET['type']) ? $_GET['type'] : ''; switch ($export_type) { case 'employee': renderEmployeeConversionRateTable($employee_conversion_rate, true); break; case 'channel': renderChannelTotalConversionRateTable($channel_total_conversion_rate, true); break; case 'country_channel': renderCountryChannelConversionRateTable($country_channel_conversion_rate, true); break; } exit; // 确保导出后停止执行 } ?>