mtb_crm_asp/monthly_deal_stats.php

<?php
/**
 * 每月新增成交客户统计分析
 */
require_once 'conn.php';
require_once 'statistics_utils.php';
require_once 'statistics_customers.php';
require_once 'statistics_sales.php';
// 检查登录状态
if (!isset($_SESSION['employee_id'])) {
    checkLogin();
}

function formatCurrency($value) {
    return '¥' . number_format($value ?? 0, 2);
}

// 获取当前登录用户信息
$current_employee_id = $_SESSION['employee_id'];
$current_permission_role = 0;

// 获取当前用户权限角色
$current_employee_id = intval($current_employee_id); // 确保是整数
$query = "SELECT em_permission_role_id FROM employee WHERE id = $current_employee_id";
$result = $conn->query($query);
if ($result && $row = $result->fetch_assoc()) {
    $current_permission_role = $row['em_permission_role_id'];
}

// 检查是否为导出请求
$is_export = isset($_GET['export']) && $_GET['export'] == 'excel';

// 获取日期范围参数
$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 '<div class="alert alert-info">当前选择范围内没有成交客户数据</div>';
        }
        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 '<div class="row mt-5 mb-5">';
    echo '<div class="col-md-12">';
    echo '<div class="card">';
    echo '<div class="card-header d-flex justify-content-between align-items-center">';
    echo '<span>每月新增成交客户数量明细 (总计: '.$total_customers.' 客户)</span>';
    echo '<a href="' . $_SERVER['REQUEST_URI'] . '&export=excel&type=customers" class="btn btn-sm btn-success ml-3">导出CSV</a>';
    echo '</div>';
    echo '<div class="card-body">';
    echo '<div class="table-responsive">';
    echo '<table class="table table-bordered table-striped">';
    echo '<thead class="thead-light">';
    echo '<tr>';
    echo '<th style="width: 50%; text-align: left;">月份</th>';
    echo '<th style="width: 50%; text-align: left;">新增成交客户数量</th>';
    echo '</tr>';
    echo '</thead>';
    echo '<tbody>';
    
    foreach ($data as $item) {
        echo '<tr>';
        echo '<td>'.$item['month'].'</td>';
        echo '<td>'.$item['customer_count'].'</td>';
        echo '</tr>';
    }
    
    echo '</tbody>';
    echo '</table>';
    echo '</div>';
    echo '</div>';
    echo '</div>';
    echo '</div>';
    echo '</div>';
}

/**
 * 渲染业务员成交统计表格
 */
function renderDealStatsByEmployeeTable($data, $is_export = false) {
    if (empty($data)) {
        if (!$is_export) {
            echo '<div class="alert alert-info">当前选择范围内没有业务员成交数据</div>';
        }
        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 '<div class="row mt-5">';
    echo '<div class="col-md-12">';
    echo '<div class="card">';
    echo '<div class="card-header d-flex justify-content-between align-items-center">';
    echo '<span>业务员成交统计明细</span>';
    echo '<a href="' . $_SERVER['REQUEST_URI'] . '&export=excel&type=employee" class="btn btn-sm btn-success ml-3">导出CSV</a>';
    echo '</div>';
    echo '<div class="card-body">';
    echo '<div class="table-responsive">';
    echo '<table class="table table-bordered table-striped">';
    echo '<thead class="thead-light">';
    echo '<tr>';
    echo '<th style="width: 25%; text-align: left;">业务员</th>';
    echo '<th style="width: 25%; text-align: left;">成交客户数</th>';
    echo '<th style="width: 25%; text-align: left;">成交金额</th>';
    echo '<th style="width: 25%; text-align: left;">客单价</th>';
    echo '</tr>';
    echo '</thead>';
    echo '<tbody>';
    
    foreach ($data as $item) {
        $avg_customer_value = $item['customer_count'] > 0 ? $item['total_amount'] / $item['customer_count'] : 0;
        echo '<tr>';
        echo '<td>'.$item['employee_name'].'</td>';
        echo '<td>'.$item['customer_count'].'</td>';
        echo '<td>'.formatCurrency($item['total_amount']).'</td>';
        echo '<td>'.formatCurrency($avg_customer_value).'</td>';
        echo '</tr>';
    }
    
    echo '</tbody>';
    echo '</table>';
    echo '</div>';
    echo '</div>';
    echo '</div>';
    echo '</div>';
    echo '</div>';
}

// 获取选择的业务员
$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; // 确保导出后停止执行
}

?>

<div class="container">
    <div class="page-header">
        <h1 class="page-title">每月新增成交客户统计</h1>
    </div>
    
    <!-- 日期筛选 -->
    <div class="filter-form mb-5">
        <form method="get" class="filter-form-inline">
            <div class="form-group mr-3">
                <label for="date_range" class="mr-2">选择日期范围</label>
                <select class="form-control" id="date_range" name="date_range" onchange="toggleCustomDates()">
                    <option value="current_month" <?php echo $date_range == 'current_month' ? 'selected' : ''; ?>>本月</option>
                    <option value="last_month" <?php echo $date_range == 'last_month' ? 'selected' : ''; ?>>上月</option>
                    <option value="current_year" <?php echo $date_range == 'current_year' ? 'selected' : ''; ?>>今年</option>
                    <option value="last_30_days" <?php echo $date_range == 'last_30_days' ? 'selected' : ''; ?>>最近30天</option>
                    <option value="last_90_days" <?php echo $date_range == 'last_90_days' ? 'selected' : ''; ?>>最近90天</option>
                    <option value="custom" <?php echo $date_range == 'custom' ? 'selected' : ''; ?>>自定义日期范围</option>
                </select>
            </div>
            <div class="form-group custom-date-inputs mr-3" id="custom_start_date" style="display: <?php echo $date_range == 'custom' ? 'inline-block' : 'none'; ?>">
                <label for="start_date" class="mr-2">开始日期</label>
                <input type="date" class="form-control" id="start_date" name="start_date" value="<?php echo $date_params['custom_start']; ?>">
            </div>
            <div class="form-group custom-date-inputs mr-3" id="custom_end_date" style="display: <?php echo $date_range == 'custom' ? 'inline-block' : 'none'; ?>">
                <label for="end_date" class="mr-2">结束日期</label>
                <input type="date" class="form-control" id="end_date" name="end_date" value="<?php echo $date_params['custom_end']; ?>">
            </div>
            
            <!-- 业务员选择 -->
            <div class="form-group mr-3">
                <label for="selected_employee" class="mr-2">选择业务员</label>
                <select class="form-control" id="selected_employee" name="selected_employee">
                    <option value="all">所有业务员</option>
                    <?php
                    // 获取当前用户可见的业务员列表
                    $visible_employees_query = "";
                    
                    if ($current_permission_role == 1) {
                        // 管理员可以看到所有业务员
                        $visible_employees_query = "SELECT id, em_user FROM employee WHERE em_role IS NOT NULL ORDER BY em_user";
                    } elseif ($current_permission_role == 2) {
                        // 组长可以看到自己和组员
                        $visible_employees_query = "SELECT id, em_user FROM employee WHERE id = $current_employee_id OR em_role = $current_employee_id ORDER BY em_user";
                    } else {
                        // 组员只能看到自己
                        $visible_employees_query = "SELECT id, em_user FROM employee WHERE id = $current_employee_id";
                    }
                    
                    $visible_employees_result = $conn->query($visible_employees_query);
                    $selected_employee = isset($_GET['selected_employee']) ? $_GET['selected_employee'] : 'all';
                    
                    while ($emp = $visible_employees_result->fetch_assoc()) {
                        $selected = ($selected_employee == $emp['id']) ? 'selected' : '';
                        echo "<option value='".$emp['id']."' $selected>".$emp['em_user']."</option>";
                    }
                    ?>
                </select>
            </div>
            
            <div class="form-group">
                <button type="submit" class="btn btn-primary">应用筛选</button>
            </div>
        </form>
    </div>
    
    <!-- 统计内容区域 -->
    <div class="stats-content">
        <?php
        // 渲染表格
        renderMonthlyDealCustomersTable($monthly_deal_customers);
        renderDealStatsByEmployeeTable($deal_stats_by_employee);
        ?>
    </div>
</div>

<style>
/* 添加一些额外的样式以改善表格显示 */
.filter-form {
    background-color: #f8f9fa;
    padding: 20px;
    border-radius: 5px;
    margin-bottom: 30px;
}

.filter-form-inline {
    display: flex;
    flex-wrap: wrap;
    align-items: flex-end;
}

.filter-form .form-group {
    margin-bottom: 10px;
    margin-right: 15px;
}

/* 表格样式 */
.table {
    width: 100%;
    margin-bottom: 1rem;
}

.table th, .table td {
    padding: 12px;
    vertical-align: middle;
}

/* 确保表头也是左对齐 */
.thead-light th {
    background-color: #f8f9fa;
    border-color: #dee2e6;
    text-align: left;
}

.table-striped tbody tr:nth-of-type(odd) {
    background-color: rgba(0, 0, 0, 0.03);
}

.card {
    box-shadow: 0 0.125rem 0.25rem rgba(0, 0, 0, 0.075);
}

.card-header {
    background-color: #f8f9fa;
    padding: 15px;
}

.card-body {
    padding: 1.5rem;
}

/* 添加按钮左边距 */
.ml-3 {
    margin-left: 15px !important;
}

/* 确保卡片头部布局正确 */
.card-header.d-flex {
    display: flex !important;
    justify-content: space-between !important;
    align-items: center !important;
}
</style>

<script>
function toggleCustomDates() {
    const dateRange = document.getElementById('date_range').value;
    const customDateInputs = document.querySelectorAll('.custom-date-inputs');
    
    if (dateRange === 'custom') {
        customDateInputs.forEach(el => el.style.display = 'inline-block');
    } else {
        customDateInputs.forEach(el => el.style.display = 'none');
    }
}
</script>

<?php
// 页面底部
include('statistics_footer.php');
?>