$date_range, 'custom_start' => $custom_start, 'custom_end' => $custom_end, 'period' => $period, 'start_date' => $start_date, 'end_date' => $end_date, 'start_date_sql' => $start_date_sql, 'end_date_sql' => $end_date_sql ]; } /** * 生成图表颜色数组 * * @param int $count 需要的颜色数量 * @param bool $transparent 是否透明 * @return array 背景色和边框色数组 */ function generateChartColors($count = 10, $transparent = true) { $colors = [ ['rgba(255, 99, 132, ' . ($transparent ? '0.7' : '1') . ')', 'rgba(255, 99, 132, 1)'], ['rgba(54, 162, 235, ' . ($transparent ? '0.7' : '1') . ')', 'rgba(54, 162, 235, 1)'], ['rgba(255, 206, 86, ' . ($transparent ? '0.7' : '1') . ')', 'rgba(255, 206, 86, 1)'], ['rgba(75, 192, 192, ' . ($transparent ? '0.7' : '1') . ')', 'rgba(75, 192, 192, 1)'], ['rgba(153, 102, 255, ' . ($transparent ? '0.7' : '1') . ')', 'rgba(153, 102, 255, 1)'], ['rgba(255, 159, 64, ' . ($transparent ? '0.7' : '1') . ')', 'rgba(255, 159, 64, 1)'], ['rgba(199, 199, 199, ' . ($transparent ? '0.7' : '1') . ')', 'rgba(199, 199, 199, 1)'], ['rgba(83, 102, 255, ' . ($transparent ? '0.7' : '1') . ')', 'rgba(83, 102, 255, 1)'], ['rgba(40, 159, 64, ' . ($transparent ? '0.7' : '1') . ')', 'rgba(40, 159, 64, 1)'], ['rgba(210, 199, 199, ' . ($transparent ? '0.7' : '1') . ')', 'rgba(210, 199, 199, 1)'] ]; $result = []; // 确保有足够的颜色 while (count($result) < $count) { foreach ($colors as $color) { $result[] = $color; if (count($result) >= $count) { break; } } } return array_slice($result, 0, $count); } /** * 格式化数值,处理空值和小数位数 * * @param mixed $value 要格式化的值 * @param int $decimals 小数位数 * @return string 格式化后的数值 */ function formatNumber($value, $decimals = 2) { if ($value === null || $value === '') { return '0'; } return number_format((float)$value, $decimals); } /** * 获取时间粒度对应的MySQL DATE_FORMAT格式 * * @param string $period 时间粒度 (day/week/month) * @return string MySQL DATE_FORMAT格式字符串 */ function getPeriodFormat($period) { switch ($period) { case 'week': return '%x-W%v'; // ISO year and week number case 'month': return '%Y-%m'; case 'day': default: return '%Y-%m-%d'; } } /** * 获取新增客户详细信息 * * @param mysqli $conn 数据库连接 * @param string $start_date 开始日期 * @param string $end_date 结束日期 * @param array|int $employee_filter 业务员ID或ID数组,用于过滤数据 (可选) * @return array 新增客户详细数据 */ function getNewCustomersDetails($conn, $start_date, $end_date, $employee_filter = null) { // 使用 mysqli_real_escape_string 防止 SQL 注入 $start_date = $conn->real_escape_string($start_date); $end_date = $conn->real_escape_string($end_date); $sql = "SELECT c.id, c.cs_company as company_name, c.cs_code as customer_code, co.countryName as country, ct.businessType as customer_type, e.em_user as employee_name, c.cs_addtime as add_date FROM customer c LEFT JOIN country co ON c.cs_country = co.id LEFT JOIN clienttype ct ON c.cs_type = ct.id LEFT JOIN employee e ON c.cs_belong = e.id WHERE c.cs_addtime BETWEEN '$start_date' AND '$end_date'"; // 如果有业务员过滤条件 if ($employee_filter !== null) { if (is_array($employee_filter) && !empty($employee_filter)) { // 处理数组形式的业务员ID列表 $emp_ids = array(); foreach ($employee_filter as $emp_id) { if (is_numeric($emp_id)) { $emp_ids[] = intval($emp_id); } } if (!empty($emp_ids)) { $emp_ids_str = implode(',', $emp_ids); $sql .= " AND c.cs_belong IN ($emp_ids_str)"; } } else if (is_numeric($employee_filter) && $employee_filter > 0) { // 处理单个业务员ID $employee_filter = intval($employee_filter); $sql .= " AND c.cs_belong = $employee_filter"; } } $sql .= " ORDER BY c.cs_addtime DESC LIMIT 30"; $result = $conn->query($sql); $customers = []; if ($result) { while ($row = $result->fetch_assoc()) { $customers[] = $row; } } return $customers; } /** * 获取各业务员新增客户统计 * * @param mysqli $conn 数据库连接 * @param string $start_date 开始日期 * @param string $end_date 结束日期 * @param array $visible_employees 可见的业务员ID列表 (可选) * @return array 业务员新增客户统计数据 */ function getNewCustomersByEmployee($conn, $start_date, $end_date, $visible_employees = null) { // 使用 mysqli_real_escape_string 防止 SQL 注入 $start_date = $conn->real_escape_string($start_date); $end_date = $conn->real_escape_string($end_date); $sql = "SELECT e.id as employee_id, e.em_user as employee_name, COUNT(c.id) as customer_count FROM employee e LEFT JOIN customer c ON e.id = c.cs_belong AND c.cs_addtime BETWEEN '$start_date' AND '$end_date' WHERE e.em_role IS NOT NULL"; // 如果指定了可见业务员列表,则添加过滤条件 if ($visible_employees !== null && !empty($visible_employees)) { $emp_ids = array(); foreach ($visible_employees as $emp_id) { if (is_numeric($emp_id)) { $emp_ids[] = intval($emp_id); } } if (!empty($emp_ids)) { $emp_ids_str = implode(',', $emp_ids); $sql .= " AND e.id IN ($emp_ids_str)"; } } $sql .= " GROUP BY e.id ORDER BY customer_count DESC"; $result = $conn->query($sql); $data = []; if ($result) { while ($row = $result->fetch_assoc()) { $data[] = $row; } } return $data; } /** * 渲染新增客户图表 * * @param array $customers 新增客户数据 * @return void */ function renderNewCustomersChart($customers) { if (empty($customers)) { echo '
该时间段内没有新增客户数据
'; return; } ?>
客户名称 客户编码 国家/地区 客户类型 负责业务员 添加日期
该时间段内没有业务员新增客户数据'; return; } // 准备图表数据 $employee_names = []; $customer_counts = []; foreach ($employee_data as $row) { $employee_names[] = $row['employee_name']; $customer_counts[] = $row['customer_count']; } // 生成图表背景色 $colors = generateChartColors(count($employee_data)); $backgroundColors = []; $borderColors = []; foreach ($colors as $color) { $backgroundColors[] = $color[0]; $borderColors[] = $color[1]; } ?>
业务员 新增客户数量
prepare($sql); $stmt->bind_param("i", $user_id); $stmt->execute(); $result = $stmt->get_result(); $user = $result->fetch_assoc(); $role = $user['em_role'] ?? 0; $employees = []; if ($role == 1) { // 管理员可以看到所有业务员 $sql = "SELECT id, em_user FROM employee WHERE em_role IS NOT NULL ORDER BY em_user"; $result = $conn->query($sql); while ($row = $result->fetch_assoc()) { $employees[] = [ 'id' => $row['id'], 'name' => $row['em_user'] ]; } } else if ($role == 2) { // 获取组长自己和其团队成员 $sql = "SELECT id, em_user FROM employee WHERE id = ? OR em_role = ? ORDER BY em_user"; $stmt = $conn->prepare($sql); $stmt->bind_param("ii", $user_id, $user_id); $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { $employees[] = [ 'id' => $row['id'], 'name' => $row['em_user'] ]; } } else { // 普通业务员只能看到自己 $sql = "SELECT id, em_user FROM employee WHERE id = ?"; $stmt = $conn->prepare($sql); $stmt->bind_param("i", $user_id); $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { $employees[] = [ 'id' => $row['id'], 'name' => $row['em_user'] ]; } } return $employees; } /** * 根据用户角色获取默认的业务员筛选列表 * * @param mysqli $conn 数据库连接 * @param int $user_id 当前用户ID * @return array|int|null 业务员筛选值,可能是单个ID、ID数组或null */ function getDefaultEmployeeFilter($conn, $user_id) { // 获取当前用户角色 $sql = "SELECT em_role FROM employee WHERE id = ?"; $stmt = $conn->prepare($sql); $stmt->bind_param("i", $user_id); $stmt->execute(); $result = $stmt->get_result(); $user = $result->fetch_assoc(); $role = $user['em_role'] ?? 0; if ($role == 1) { // 管理员默认看所有人 return null; } else if ($role == 2) { // 团队组长默认看他的团队 $sql = "SELECT id FROM employee WHERE id = ? OR em_role = ?"; $stmt = $conn->prepare($sql); $stmt->bind_param("ii", $user_id, $user_id); $stmt->execute(); $result = $stmt->get_result(); $emp_ids = []; while ($row = $result->fetch_assoc()) { $emp_ids[] = $row['id']; } return $emp_ids; } else { // 普通业务员只看自己 return $user_id; } } /** * 渲染新客户产品购买明细 * * @param array $product_data 产品购买数据 * @return void */ function renderNewCustomerProductPurchases($product_data) { if (empty($product_data) || empty($product_data['products'])) { echo '
该时间段内没有新客户购买产品数据
'; return; } $products = $product_data['products']; $new_customer_count = $product_data['new_customer_count']; ?>

本期间共有 名新客户进行了购买。以下是他们购买的产品明细:

产品名称 产品分类 订单数 购买客户数 销售数量 销售金额 平均单价