mtb_crm_asp/region_performance_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 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 '<div class="alert alert-info">当前选择范围内没有区域业绩数据</div>';
        }
        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 '<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>每月区域新客户业绩明细 (总计: '.formatCurrency($total_amount).', 共'.$total_customers.'个新客户)</span>';
    echo '<a href="' . $_SERVER['REQUEST_URI'] . '&export=excel&type=monthly" 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: 15%; text-align: left;">月份</th>';
    echo '<th style="width: 30%; text-align: left;">区域</th>';
    echo '<th style="width: 25%; text-align: left;">新成交客户数</th>';
    echo '<th style="width: 30%; text-align: left;">成交金额</th>';
    echo '</tr>';
    echo '</thead>';
    echo '<tbody>';
    
    $current_month = '';
    foreach ($months_data as $month => $regions) {
        foreach ($regions as $index => $region) {
            echo '<tr>';
            // 只在每个月份的第一行显示月份
            if ($index === 0) {
                echo '<td rowspan="' . count($regions) . '">' . $month . '</td>';
            }
            echo '<td>' . $region['region'] . '</td>';
            echo '<td>' . $region['customer_count'] . '</td>';
            echo '<td>' . formatCurrency($region['total_amount']) . '</td>';
            echo '</tr>';
        }
    }
    
    echo '</tbody>';
    echo '</table>';
    echo '</div>';
    echo '</div>';
    echo '</div>';
    echo '</div>';
    echo '</div>';
}

/**
 * 渲染区域业绩汇总表格
 */
function renderRegionPerformanceSummaryTable($data, $is_export = false) {
    if (empty($data)) {
        if (!$is_export) {
            echo '<div class="alert alert-info">当前选择范围内没有区域业绩汇总数据</div>';
        }
        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 '<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>区域新客户业绩汇总 (总计: '.formatCurrency($total_amount).', 共'.$total_customers.'个新客户)</span>';
    echo '<a href="' . $_SERVER['REQUEST_URI'] . '&export=excel&type=summary" 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: 40%; text-align: left;">区域</th>';
    echo '<th style="width: 30%; text-align: left;">新成交客户数</th>';
    echo '<th style="width: 30%; text-align: left;">成交金额</th>';
    echo '</tr>';
    echo '</thead>';
    echo '<tbody>';
    
    foreach ($data as $item) {
        echo '<tr>';
        echo '<td>' . $item['region'] . '</td>';
        echo '<td>' . $item['customer_count'] . '</td>';
        echo '<td>' . formatCurrency($item['total_amount']) . '</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_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; // 确保导出后停止执行
}

?>

<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
        // 渲染表格
        renderRegionPerformanceSummaryTable($region_performance_summary);
        renderMonthlyRegionPerformanceTable($monthly_region_performance);
        ?>
    </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');
?>