Files

2508 lines
120 KiB
HTML
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>测试路径平衡系统【TRAE】</title>
<link rel="icon" href="/icons/rebalance.svg" type="image/svg+xml" />
<script src="/assets/auth.js"></script>
<script src="/lib/xlsx.full.min.js"></script>
<style>
* {
margin: 0;
padding: 0;
box-sizing: border-box;
}
body {
font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', 'Microsoft YaHei', sans-serif;
line-height: 1.6;
color: #333;
background-color: #f5f5f5;
padding: 20px;
}
.container {
max-width: 1200px;
margin: 0 auto;
background: white;
border-radius: 8px;
box-shadow: 0 2px 10px rgba(0,0,0,0.1);
padding: 30px;
}
h1 {
text-align: center;
color: #2c3e50;
margin-bottom: 30px;
font-size: 28px;
}
.upload-section {
background: #f8f9fa;
border: 2px dashed #ddd;
border-radius: 8px;
padding: 30px;
text-align: center;
margin-bottom: 30px;
transition: all 0.3s ease;
}
.upload-section:hover {
border-color: #3498db;
background: #f0f7fb;
}
.upload-section h2 {
margin-bottom: 20px;
color: #555;
font-size: 20px;
}
.file-input-group {
margin-bottom: 20px;
text-align: left;
max-width: 500px;
margin-left: auto;
margin-right: auto;
}
.file-input-group label {
display: block;
margin-bottom: 8px;
font-weight: 500;
color: #555;
}
.file-input-group input[type="file"] {
width: 100%;
padding: 10px;
border: 1px solid #ddd;
border-radius: 4px;
font-size: 14px;
}
.btn-analyze {
background: #3498db;
color: white;
border: none;
padding: 12px 30px;
font-size: 16px;
border-radius: 4px;
cursor: pointer;
transition: background 0.3s ease;
}
.btn-analyze:hover {
background: #2980b9;
}
.btn-analyze:disabled {
background: #bdc3c7;
cursor: not-allowed;
}
.result-section {
margin-top: 40px;
}
.section-title {
font-size: 22px;
color: #2c3e50;
margin: 30px 0 15px 0;
padding-bottom: 10px;
border-bottom: 2px solid #3498db;
}
.table-container {
overflow-x: auto;
margin-bottom: 30px;
border: 1px solid #e0e0e0;
border-radius: 4px;
}
table {
width: 100%;
border-collapse: collapse;
font-size: 14px;
}
th {
background: #f8f9fa;
padding: 12px;
text-align: left;
font-weight: 600;
border-bottom: 2px solid #e0e0e0;
color: #495057;
}
td {
padding: 10px 12px;
border-bottom: 1px solid #e0e0e0;
text-align: left;
}
tr:last-child td {
border-bottom: none;
}
tr:hover {
background: #f8f9fa;
}
.highlight-green {
background-color: #d4edda !important;
}
.highlight-red {
background-color: #f8d7da !important;
}
.highlight-pink {
background-color: #f8c4c4 !important;
}
.highlight-light-green {
background-color: #c3e6cb !important;
}
.highlight-light-red {
background-color: #f5c6cb !important;
}
.strategy-section {
background: #f8f9fa;
border-radius: 8px;
padding: 20px;
border-left: 4px solid #3498db;
margin-top: 30px;
}
.strategy-section h3 {
color: #2c3e50;
margin-bottom: 15px;
}
.strategy-section ul {
padding-left: 20px;
}
.strategy-section li {
margin-bottom: 8px;
line-height: 1.6;
}
.error-message {
color: #dc3545;
font-weight: 500;
margin-top: 10px;
padding: 10px;
background: #f8d7da;
border-radius: 4px;
display: none;
}
.success-message {
color: #28a745;
font-weight: 500;
margin-top: 10px;
padding: 10px;
background: #d4edda;
border-radius: 4px;
display: none;
}
.category-section {
margin-bottom: 30px;
}
.category-section h3 {
color: #495057;
margin-bottom: 10px;
font-size: 18px;
}
@media (max-width: 768px) {
.container {
padding: 20px;
}
h1 {
font-size: 24px;
}
.upload-section {
padding: 20px;
}
}
.tabs {
display: flex;
justify-content: flex-end;
gap: 10px;
margin-bottom: 10px;
}
.tab-btn {
background: #e9ecef;
color: #333;
border: none;
padding: 8px 16px;
border-radius: 4px;
cursor: pointer;
}
.tab-btn.active {
background: #3498db;
color: #fff;
}
.controls {
display: flex;
align-items: center;
justify-content: center;
gap: 10px;
flex-wrap: wrap;
margin-bottom: 10px;
}
.btn-small {
background: #3498db;
color: white;
border: none;
padding: 8px 16px;
font-size: 14px;
border-radius: 4px;
cursor: pointer;
}
.fund-summary { border: 1px solid #e0e0e0; border-radius: 4px; padding: 10px 12px; margin: 10px 0 20px 0; display: grid; grid-template-columns: repeat(6, 1fr); gap: 8px 12px; background: #f8f9fa; }
.fund-summary-item { display: flex; gap: 6px; align-items: baseline; font-size: 14px; }
.fund-summary-label { color: #555; min-width: 70px; }
.fund-summary-value { color: #2c3e50; font-weight: 600; }
/* 仓位总览相关样式 */
.help-icon {
cursor: pointer;
margin-left: 4px;
color: #6c757d;
font-weight: bold;
display: inline-block;
width: 18px;
height: 18px;
line-height: 18px;
text-align: center;
border-radius: 50%;
font-size: 12px;
border: 1px solid #6c757d;
}
.help-icon:hover {
background: #6c757d;
color: white;
}
/* 模态窗口样式 */
.modal {
position: fixed;
z-index: 10000;
left: 0;
top: 0;
width: 100%;
height: 100%;
background-color: rgba(0, 0, 0, 0.5);
}
.modal-content {
background-color: white;
margin: 10% auto;
padding: 0;
border-radius: 8px;
width: 80%;
max-width: 500px;
box-shadow: 0 4px 20px rgba(0, 0, 0, 0.2);
}
.modal-header {
display: flex;
justify-content: space-between;
align-items: center;
padding: 15px 20px;
border-bottom: 1px solid #e9ecef;
}
.modal-header h3 {
margin: 0;
font-size: 18px;
color: #333;
}
.modal-close {
font-size: 24px;
font-weight: bold;
color: #6c757d;
cursor: pointer;
line-height: 1;
}
.modal-close:hover {
color: #333;
}
.modal-body {
padding: 20px;
}
.modal-body pre {
margin: 0;
white-space: pre-wrap;
font-family: inherit;
font-size: 14px;
line-height: 1.8;
color: #333;
}
.controls {
display: flex;
gap: 15px;
align-items: center;
margin-bottom: 15px;
padding: 10px;
background: #f8f9fa;
border-radius: 4px;
}
.controls label {
font-weight: 500;
color: #495057;
}
.controls select,
.controls input {
padding: 6px 10px;
border: 1px solid #ced4da;
border-radius: 4px;
font-size: 14px;
}
.btn-small {
padding: 6px 12px;
font-size: 13px;
background: #6c757d;
color: white;
border: none;
border-radius: 4px;
cursor: pointer;
}
.btn-small:hover {
background: #5a6268;
}
</style>
</head>
<body>
<div class="container">
<h1>测试路径平衡系统</h1>
<div class="tabs">
<button class="tab-btn active" data-tab="analysis">平衡分析</button>
<button class="tab-btn" data-tab="overview">仓位总览</button>
<button class="tab-btn" data-tab="history">历史数据查询</button>
<button class="tab-btn" data-tab="fundshares">资金股份历史</button>
<button class="tab-btn" data-tab="settings">数据设置</button>
</div>
<div class="upload-section">
<h2>上传数据源文件</h2>
<div class="file-input-group">
<label for="positionFile">1. 投资仓位表 (*.xls, *.xlsx)</label>
<input type="file" id="positionFile" accept=".xls,.xlsx">
</div>
<div class="file-input-group">
<label for="planFile">2. 计划仓位表 (*.xls, *.xlsx)</label>
<input type="file" id="planFile" accept=".xls,.xlsx">
</div>
<div class="file-input-group">
<label for="assetFile">3. 资产情况表 (*.xls, *.xlsx)</label>
<input type="file" id="assetFile" accept=".xls,.xlsx">
</div>
<div class="controls">
<label>起始时间</label>
<input type="date" id="uploadStart">
<label>结束时间</label>
<input type="date" id="uploadEnd">
<button id="btnUploadPrevWeek" class="btn-small">上周</button>
<button id="btnUploadThisWeek" class="btn-small">本周</button>
<button id="btnUploadNextWeek" class="btn-small">下周</button>
</div>
<button id="analyzeBtn" class="btn-analyze" disabled>开始分析</button>
<button id="saveWeekBtn" class="btn-analyze" disabled>保存当前数据</button>
<div id="errorMsg" class="error-message"></div>
<div id="successMsg" class="success-message"></div>
<div id="saveErrorMsg" class="error-message"></div>
<div id="saveMsg" class="success-message"></div>
</div>
<div id="historySection" style="display: none;">
<h2 class="section-title">历史数据查询</h2>
<div class="controls">
<label>起始时间</label>
<input type="date" id="historyStart">
<label>结束时间</label>
<input type="date" id="historyEnd">
<button id="btnPrevWeek" class="btn-analyze">上周</button>
<button id="btnThisWeek" class="btn-analyze">本周</button>
<button id="btnNextWeek" class="btn-analyze">下周</button>
<button id="btnQueryHistory" class="btn-analyze">查询</button>
</div>
<div id="historyErrorMsg" class="error-message"></div>
</div>
<div id="fundSharesSection" style="display: none;">
<h2 class="section-title">资金股份历史</h2>
<div class="controls">
<label>起始时间</label>
<input type="date" id="fundStart">
<label>结束时间</label>
<input type="date" id="fundEnd">
<button id="btnFundPrevWeek" class="btn-analyze">上周</button>
<button id="btnFundThisWeek" class="btn-analyze">本周</button>
<button id="btnFundNextWeek" class="btn-analyze">下周</button>
<button id="btnFundQuery" class="btn-analyze">查询</button>
</div>
<div class="file-input-group" style="max-width: 500px; margin: 0 auto 10px auto;">
<label for="fundSharesFile">4. 资金股份表 (*.xls, *.xlsx)</label>
<input type="file" id="fundSharesFile" accept=".xls,.xlsx">
</div>
<div class="controls">
<button id="saveFundWeekBtn" class="btn-analyze" disabled>保存当前数据</button>
<button id="clearFundWeekBtn" class="btn-analyze">清空所有数据</button>
</div>
<div style="margin:10px 0;">
<label><input type="checkbox" id="showSqlCheckbox"> 显示SQL脚本</label>
</div>
<div id="fundSqlContainer" style="margin:10px 0; display:none;">
<label>SQL脚本预览</label>
<textarea id="fundSqlPreview" style="width:100%;height:140px;" readonly></textarea>
</div>
<div id="fundErrorMsg" class="error-message"></div>
<div id="fundSaveErrorMsg" class="error-message"></div>
<div id="fundSaveMsg" class="success-message"></div>
<div id="fundSharesSummary" class="fund-summary"></div>
<div class="table-container">
<table id="fundSharesTable">
<thead>
<tr>
<th>证券代码</th>
<th>证券名称</th>
<th>股份余额</th>
<th>实际数量</th>
<th>购买数量</th>
<th>今买数量</th>
<th>持仓成本价</th>
<th>买入均价</th>
<th>当前价</th>
<th>最新市值</th>
<th>当日盈亏</th>
<th>持仓盈亏</th>
<th>盈亏比例(%)</th>
<th>冻结数量</th>
<th>股东代码</th>
<th>交易所名称</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
</div>
<div id="settingsSection" style="display: none;">
<h2 class="section-title">数据设置</h2>
<div class="file-input-group">
<label for="excludedCodesInput">排除股票设置(证券代码,逗号分隔)</label>
<input type="text" id="excludedCodesInput" placeholder="例如:000333,600900,511880">
</div>
<button id="btnSaveSettings" class="btn-analyze">保存设置</button>
<div id="settingsMsg" class="success-message"></div>
<div id="settingsErrorMsg" class="error-message"></div>
</div>
<div id="overviewSection" class="result-section" style="display: none;">
<h2 class="section-title">仓位总览</h2>
<div class="controls">
<label>大类名称:</label>
<select id="overviewCategoryFilter">
<option value="">全部</option>
</select>
<label>证券名称/代码:</label>
<input type="text" id="overviewSearchInput" placeholder="输入证券名称或代码">
<button id="overviewResetFilter" class="btn-small">重置</button>
</div>
<div class="table-container">
<table id="overviewTable">
<thead>
<tr>
<th>大类名称<span class="help-icon" data-title="大类名称" data-content="该证券所属的资产类别(中国/海外/防御等),直接从计划仓位表获取">?</span></th>
<th>证券名称<span class="help-icon" data-title="证券名称" data-content="证券的简称,直接从投资仓位表获取">?</span></th>
<th>证券代码<span class="help-icon" data-title="证券代码" data-content="证券的唯一代码标识,直接从投资仓位表获取">?</span></th>
<th>本轮计划仓位<span class="help-icon" data-title="本轮计划仓位" data-content="本轮计划配置的仓位金额,直接从计划仓位表获取">?</span></th>
<th>占大类的计划仓位比例<span class="help-icon" data-title="占大类的计划仓位比例" data-content="该证券本轮计划仓位占其所属大类总计划仓位的百分比\n计算公式:证券本轮计划仓位 ÷ 所在大类本轮计划仓位总和 × 100%">?</span></th>
<th>占整体的计划仓位比例<span class="help-icon" data-title="占整体的计划仓位比例" data-content="该证券本轮计划仓位占所有证券总计划仓位的百分比\n计算公式:证券本轮计划仓位 ÷ 所有证券本轮计划仓位总和 × 100%">?</span></th>
<th>实际仓位<span class="help-icon" data-title="实际仓位" data-content="当前持有的该证券市值,直接从投资仓位表获取">?</span></th>
<th>占大类的实际仓位比例<span class="help-icon" data-title="占大类的实际仓位比例" data-content="该证券当前市值占其所属大类总市值的百分比\n计算公式:证券当前市值 ÷ 所在大类当前市值总和 × 100%">?</span></th>
<th>占整体的实际仓位比例<span class="help-icon" data-title="占整体的实际仓位比例" data-content="该证券当前市值占所有证券总市值的百分比\n计算公式:证券当前市值 ÷ 所有证券当前市值总和 × 100%">?</span></th>
<th>上一轮计划仓位<span class="help-icon" data-title="上一轮计划仓位" data-content="上一轮计划配置的仓位金额,直接从计划仓位表获取">?</span></th>
<th>两个仓位差额<span class="help-icon" data-title="两个仓位差额" data-content="本轮计划仓位与当前实际持仓的差额\n计算公式:本轮计划仓位 - 当前市值">?</span></th>
<th>仓位相差的比例<span class="help-icon" data-title="仓位相差的比例" data-content="本轮计划仓位与当前实际持仓的差额占本轮计划仓位的百分比\n计算公式:(本轮计划仓位 - 当前市值) ÷ 本轮计划仓位 × 100%">?</span></th>
<th>仓位调整金额<span class="help-icon" data-title="仓位调整金额" data-content="本轮相对于上一轮的计划仓位调整金额\n计算公式:本轮计划仓位 - 上一轮计划仓位">?</span></th>
<th>仓位调整比例<span class="help-icon" data-title="仓位调整比例" data-content="本轮相对于上一轮的计划仓位调整比例\n计算公式:(本轮计划仓位 - 上一轮计划仓位) ÷ 本轮计划仓位 × 100%">?</span></th>
</tr>
</thead>
<tbody></tbody>
<tfoot id="overviewTableFoot"></tfoot>
</table>
</div>
</div>
<!-- 帮助模态窗口 -->
<div id="helpModal" class="modal" style="display: none;">
<div class="modal-content">
<div class="modal-header">
<h3 id="modalTitle"></h3>
<span class="modal-close">&times;</span>
</div>
<div class="modal-body">
<pre id="modalContent"></pre>
</div>
</div>
</div>
<div id="resultSection" class="result-section" style="display: none;">
<h2 class="section-title">分析结果</h2>
<!-- 资产统计表 -->
<h3 class="section-title">资产统计</h3>
<div class="table-container">
<table id="assetTable">
<thead>
<tr>
<th>资产总值</th>
<th>投入本金</th>
<th>可用现金</th>
<th>浮动盈亏</th>
<th>浮动盈亏比例</th>
<th>股票市值-再平衡</th>
<th>股票市值-所有</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<!-- 三大类仓位表 -->
<h3 class="section-title">三大类仓位表</h3>
<div class="table-container">
<table id="categoryTable">
<thead>
<tr>
<th>大类名称</th>
<th>大类的目标比例</th>
<th>大类的实际比例</th>
<th>大类比例偏差</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<!-- 偏离情况表 -->
<h3 class="section-title">偏离情况表</h3>
<div class="table-container">
<table id="deviationTable">
<thead>
<tr>
<th>类别</th>
<th>证券代码</th>
<th>证券名称</th>
<th>当前市值</th>
<th>当前占比</th>
<th>目标比例</th>
<th>目标市值</th>
<th>计划定投周期</th>
<th>计划定投金额</th>
<th>偏离比例</th>
<th>盈亏比例</th>
<th>策略</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<!-- 偏离调整表 -->
<h3 class="section-title">偏离调整表</h3>
<div class="category-section" id="chinaSection">
<h3>中国</h3>
<div class="table-container">
<table id="chinaAdjustTable">
<thead>
<tr>
<th>证券代码</th>
<th>证券名称</th>
<th>当前市值</th>
<th>当前占比</th>
<th>目标比例</th>
<th>目标市值</th>
<th>调整金额</th>
<th>偏离比例</th>
<th>操作建议</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
</div>
<div class="category-section" id="overseasSection">
<h3>海外</h3>
<div class="table-container">
<table id="overseasAdjustTable">
<thead>
<tr>
<th>证券代码</th>
<th>证券名称</th>
<th>当前市值</th>
<th>当前占比</th>
<th>目标比例</th>
<th>目标市值</th>
<th>调整金额</th>
<th>偏离比例</th>
<th>操作建议</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
</div>
<div class="category-section" id="defenseSection">
<h3>防御</h3>
<div class="table-container">
<table id="defenseAdjustTable">
<thead>
<tr>
<th>证券代码</th>
<th>证券名称</th>
<th>当前市值</th>
<th>当前占比</th>
<th>目标比例</th>
<th>目标市值</th>
<th>调整金额</th>
<th>偏离比例</th>
<th>操作建议</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
</div>
<!-- 综合策略建议 -->
<div class="strategy-section">
<h3>综合策略建议</h3>
<ul id="strategySuggestions"></ul>
</div>
</div>
</div>
<script>
// 文件上传和分析功能
const positionFile = document.getElementById('positionFile');
const planFile = document.getElementById('planFile');
const assetFile = document.getElementById('assetFile');
const analyzeBtn = document.getElementById('analyzeBtn');
const errorMsg = document.getElementById('errorMsg');
const successMsg = document.getElementById('successMsg');
const resultSection = document.getElementById('resultSection');
const saveWeekBtn = document.getElementById('saveWeekBtn');
const saveErrorMsg = document.getElementById('saveErrorMsg');
const saveMsg = document.getElementById('saveMsg');
const historySection = document.getElementById('historySection');
const fundSharesSection = document.getElementById('fundSharesSection');
const settingsSection = document.getElementById('settingsSection');
const overviewSection = document.getElementById('overviewSection');
const uploadSection = document.querySelector('.upload-section');
const tabs = document.querySelectorAll('.tab-btn');
const historyStart = document.getElementById('historyStart');
const historyEnd = document.getElementById('historyEnd');
const btnPrevWeek = document.getElementById('btnPrevWeek');
const btnThisWeek = document.getElementById('btnThisWeek');
const btnNextWeek = document.getElementById('btnNextWeek');
const btnQueryHistory = document.getElementById('btnQueryHistory');
const historyErrorMsg = document.getElementById('historyErrorMsg');
const fundStart = document.getElementById('fundStart');
const fundEnd = document.getElementById('fundEnd');
const btnFundPrevWeek = document.getElementById('btnFundPrevWeek');
const btnFundThisWeek = document.getElementById('btnFundThisWeek');
const btnFundNextWeek = document.getElementById('btnFundNextWeek');
const btnFundQuery = document.getElementById('btnFundQuery');
const fundErrorMsg = document.getElementById('fundErrorMsg');
const fundSharesFile = document.getElementById('fundSharesFile');
const saveFundWeekBtn = document.getElementById('saveFundWeekBtn');
const clearFundWeekBtn = document.getElementById('clearFundWeekBtn');
const fundSqlPreview = document.getElementById('fundSqlPreview');
const fundSqlContainer = document.getElementById('fundSqlContainer');
const showSqlCheckbox = document.getElementById('showSqlCheckbox');
const fundSaveMsg = document.getElementById('fundSaveMsg');
const fundSaveErrorMsg = document.getElementById('fundSaveErrorMsg');
const excludedCodesInput = document.getElementById('excludedCodesInput');
const btnSaveSettings = document.getElementById('btnSaveSettings');
const settingsMsg = document.getElementById('settingsMsg');
const settingsErrorMsg = document.getElementById('settingsErrorMsg');
const uploadStart = document.getElementById('uploadStart');
const uploadEnd = document.getElementById('uploadEnd');
const btnUploadPrevWeek = document.getElementById('btnUploadPrevWeek');
const btnUploadThisWeek = document.getElementById('btnUploadThisWeek');
const btnUploadNextWeek = document.getElementById('btnUploadNextWeek');
const overviewCategoryFilter = document.getElementById('overviewCategoryFilter');
const overviewSearchInput = document.getElementById('overviewSearchInput');
const overviewResetFilter = document.getElementById('overviewResetFilter');
const helpModal = document.getElementById('helpModal');
const modalTitle = document.getElementById('modalTitle');
const modalContent = document.getElementById('modalContent');
const modalClose = document.querySelector('.modal-close');
let excludedCodesCache = null;
let historyWeekOffset = 0;
let uploadWeekOffset = 0;
let fundWeekOffset = 0;
let fundSummaryCache = null;
// 检查是否所有文件都已上传
function checkFiles() {
const ready = positionFile.files.length && planFile.files.length && assetFile.files.length;
analyzeBtn.disabled = !ready;
saveWeekBtn.disabled = !ready;
}
positionFile.addEventListener('change', checkFiles);
planFile.addEventListener('change', checkFiles);
assetFile.addEventListener('change', checkFiles);
// 分析按钮点击事件
analyzeBtn.addEventListener('click', async function() {
errorMsg.style.display = 'none';
successMsg.style.display = 'none';
resultSection.style.display = 'none';
overviewSection.style.display = 'none';
clearResults();
try {
const positionData = await parseExcel(positionFile.files[0]);
const planData = await parseExcel(planFile.files[0]);
const assetData = await parseExcel(assetFile.files[0]);
const validationResult = validateColumns(positionData, planData, assetData);
if (!validationResult.valid) {
errorMsg.textContent = validationResult.message;
errorMsg.style.display = 'block';
return;
}
if (!uploadStart.value || !uploadEnd.value || !isValidWeekRange(uploadStart.value, uploadEnd.value)) {
errorMsg.textContent = '起止日期必须为同一周的周一到周日';
errorMsg.style.display = 'block';
return;
}
const codes = await getExcludedCodes();
analyzeData(sanitizeList(positionData), sanitizeList(planData), sanitizeList(assetData), codes);
successMsg.textContent = '分析成功!';
successMsg.style.display = 'block';
// 根据当前Tab显示对应内容
const activeTab = document.querySelector('.tab-btn.active');
const tabName = activeTab ? activeTab.getAttribute('data-tab') : 'analysis';
if (tabName === 'overview') {
overviewSection.style.display = 'block';
overviewSection.scrollIntoView({ behavior: 'smooth' });
} else {
resultSection.style.display = 'block';
resultSection.scrollIntoView({ behavior: 'smooth' });
}
} catch (error) {
console.error('分析错误:', error);
errorMsg.textContent = '分析过程中出现错误: ' + error.message;
errorMsg.style.display = 'block';
}
});
saveWeekBtn.addEventListener('click', async function() {
saveErrorMsg.style.display = 'none';
saveMsg.style.display = 'none';
try {
const positionData = await parseExcel(positionFile.files[0]);
const planData = await parseExcel(planFile.files[0]);
const assetData = await parseExcel(assetFile.files[0]);
const validationResult = validateColumns(positionData, planData, assetData);
if (!validationResult.valid) {
saveErrorMsg.textContent = validationResult.message;
saveErrorMsg.style.display = 'block';
return;
}
const start = uploadStart.value;
const end = uploadEnd.value;
if (!start || !end || !isValidWeekRange(start, end)) {
saveErrorMsg.textContent = '起止日期必须为同一周的周一到周日';
saveErrorMsg.style.display = 'block';
return;
}
const existsResp = await fetch('/api/weekly-data?start=' + start + '&end=' + end);
if (existsResp.status === 200) {
const confirmOverwrite = window.confirm('该周数据已存在,是否覆盖该周的导入数据?');
if (!confirmOverwrite) {
saveMsg.textContent = '已取消覆盖';
saveMsg.style.display = 'block';
return;
}
}
const overwrite = existsResp.status === 200;
const resp = await fetch('/api/weekly-data', {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({
weekStart: start,
weekEnd: end,
positionData,
planData,
assetData,
overwrite
})
});
if (!resp.ok) {
const j = await resp.json().catch(() => ({}));
throw new Error(j.error || '保存失败');
}
saveMsg.textContent = '保存成功:' + start + ' 至 ' + end;
saveMsg.style.display = 'block';
// Sync to weekly report
try {
const table = document.getElementById('assetTable');
if (table && table.rows.length > 1) {
const cells = table.rows[1].cells;
const assets = {
total: cells[0].textContent,
investment: cells[1].textContent,
available: cells[2].textContent,
profit: cells[3].textContent,
profitRatio: cells[4].textContent
};
// Use start date (Monday) as the date
const dateToSend = String(start).trim();
console.log('Calling syncToWeekly for analysis with date:', dateToSend);
await syncToWeekly('analysis', dateToSend, { assets });
saveMsg.textContent += ' (已同步至周报)';
}
} catch (e) {
console.error('Sync failed', e);
saveMsg.textContent += ' (同步周报失败: ' + e.message + ')';
}
} catch (e) {
saveErrorMsg.textContent = e.message;
saveErrorMsg.style.display = 'block';
}
});
function loadScript(src) {
return new Promise((resolve, reject) => {
const s = document.createElement('script');
s.src = src;
s.onload = resolve;
s.onerror = reject;
document.head.appendChild(s);
});
}
async function ensureXLSX() {
if (typeof XLSX === 'undefined') {
try { await loadScript('/lib/xlsx.full.min.js'); } catch (e) {}
if (typeof XLSX === 'undefined') {
await loadScript('https://cdn.jsdelivr.net/npm/xlsx@0.18.5/dist/xlsx.full.min.js');
}
}
// 加载cpexcel以支持旧版xls编码(GBK等)
try { await loadScript('/lib/cpexcel.js'); } catch (e) { try { await loadScript('https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/cpexcel.min.js'); } catch (e2) {} }
try { if (typeof XLSX !== 'undefined' && XLSX.set_cptable && typeof window !== 'undefined' && window.cptable) { XLSX.set_cptable(window.cptable); } } catch (e) {}
}
// 解析Excel文件
function parseExcel(file) {
return new Promise((resolve, reject) => {
const reader = new FileReader();
reader.onload = async function(e) {
try {
await ensureXLSX();
const data = new Uint8Array(e.target.result);
const workbook = XLSX.read(data, { type: 'array', codepage: 936 });
const sheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[sheetName];
const jsonData = XLSX.utils.sheet_to_json(worksheet);
resolve(sanitizeList(jsonData));
} catch (error) {
reject(new Error('解析Excel文件失败: ' + error.message));
}
};
reader.onerror = () => reject(new Error('读取文件失败'));
reader.readAsArrayBuffer(file);
});
}
// 验证列是否完整
function validateColumns(positionData, planData, assetData) {
// 验证投资仓位表列
const positionRequiredColumns = ['证券代码', '证券简称', '当前市值', '数量', '成本价格', '当前价格'];
if (positionData.length === 0) {
return { valid: false, message: '投资仓位表数据为空' };
}
for (const column of positionRequiredColumns) {
if (!(column in positionData[0])) {
return { valid: false, message: `投资仓位表缺少必要列: ${column}` };
}
}
if (!hasProfitRatioColumn(positionData[0])) {
return { valid: false, message: '投资仓位表缺少必要列: 盈亏比例' };
}
// 验证计划仓位表列
const planRequiredColumns = ['证券代码', '证券名称', '类别', '计划定投周期', '计划定投金额', '策略'];
if (planData.length === 0) {
return { valid: false, message: '计划仓位表数据为空' };
}
for (const column of planRequiredColumns) {
if (!(column in planData[0])) {
return { valid: false, message: `计划仓位表缺少必要列: ${column}` };
}
}
// 验证资产情况表列
const assetRequiredColumns = ['投入本金', '可用现金'];
if (assetData.length === 0) {
return { valid: false, message: '资产情况表数据为空' };
}
for (const column of assetRequiredColumns) {
if (!(column in assetData[0])) {
return { valid: false, message: `资产情况表缺少必要列: ${column}` };
}
}
return { valid: true };
}
// 主分析函数
function analyzeData(positionData, planData, assetData, excludedCodes) {
const filteredPositionData = positionData.filter(item =>
!excludedCodes.includes(item['证券代码']) && item['当前市值'] > 0
);
const filteredPlanData = planData.filter(item =>
!excludedCodes.includes(item['证券代码'])
);
// 计算股票总市值-再平衡(过滤掉特殊股票)
const stockMarketValueRebalance = filteredPositionData.reduce((sum, item) => sum + (item['当前市值'] || 0), 0);
// 计算股票总市值-所有(包括所有股票)
const stockMarketValueAll = positionData.reduce((sum, item) => sum + (item['当前市值'] || 0), 0);
// 获取资产情况
const totalInvestment = assetData[0]['投入本金'] || 0;
const availableCash = assetData[0]['可用现金'] || 0;
// 计算资产总值(包括所有股票)
const totalAssets = stockMarketValueAll + availableCash;
// 浮动盈亏 = 资产总值 - 投入本金
const floatingProfit = totalAssets - totalInvestment;
// 处理规则6:总盈利大于1万元时提取10%作为奖金
let bonusAmount = 0;
if (floatingProfit > 10000) {
bonusAmount = floatingProfit * 0.1;
}
// 奖金处理后的可用资金
const availableForRebalance = totalAssets - bonusAmount;
// 计算计划仓位的总投资金额
const totalPlanAmount = filteredPlanData.reduce((sum, item) => sum + (item['计划定投金额'] || 0), 0);
// 计算大类比例
const categoryMap = {};
filteredPlanData.forEach(item => {
const category = item['类别'] || '其他';
const amount = item['计划定投金额'] || 0;
if (!categoryMap[category]) {
categoryMap[category] = 0;
}
categoryMap[category] += amount;
});
// 计算大类比例
const categoryPercentages = {};
for (const [category, amount] of Object.entries(categoryMap)) {
categoryPercentages[category] = (amount / totalPlanAmount * 100).toFixed(2) + '%';
}
// 创建股票代码到计划数据的映射
const planMap = {};
filteredPlanData.forEach(item => {
planMap[item['证券代码']] = item;
});
// 计算大类实际比例
const categoryActualMap = {};
filteredPositionData.forEach(position => {
const plan = planMap[position['证券代码']];
if (plan) {
const category = plan['类别'] || '其他';
const marketValue = position['当前市值'] || 0;
if (!categoryActualMap[category]) {
categoryActualMap[category] = 0;
}
categoryActualMap[category] += marketValue;
}
});
const categoryActualPercentages = {};
for (const [category, amount] of Object.entries(categoryActualMap)) {
categoryActualPercentages[category] = stockMarketValueRebalance > 0
? (amount / stockMarketValueRebalance * 100).toFixed(2) + '%'
: '0.00%';
}
// 创建偏离情况表数据
const deviationData = [];
const addedCodes = new Set(); // 用于跟踪已添加的股票代码
filteredPositionData.forEach(position => {
const code = position['证券代码'];
const plan = planMap[code];
if (plan && !addedCodes.has(code)) {
// 基于再平衡资产(排除特殊股票)计算当前占比
const rebalanceAssets = stockMarketValueRebalance + availableCash;
const currentRatio = (position['当前市值'] / rebalanceAssets * 100).toFixed(2);
const targetRatio = (plan['计划定投金额'] / totalPlanAmount * 100).toFixed(2);
// 使用股票市值-再平衡 * 目标比例计算目标市值
const targetRatioValue = plan['计划定投金额'] / totalPlanAmount;
const targetMarketValue = (stockMarketValueRebalance * targetRatioValue).toFixed(2);
// 计算偏离比例(当前占比 - 目标比例)
const deviationRatio = (parseFloat(currentRatio) - parseFloat(targetRatio)).toFixed(2);
deviationData.push({
category: plan['类别'],
code: code,
name: position['证券简称'] || plan['证券名称'],
currentMarketValue: position['当前市值'],
currentRatio: currentRatio + '%',
targetRatio: targetRatio + '%',
targetMarketValue: targetMarketValue,
planPeriod: plan['计划定投周期'],
planAmount: plan['计划定投金额'],
deviationRatio: deviationRatio + '%',
profitRatio: formatProfitRatio(position),
strategy: plan['策略']
});
addedCodes.add(code); // 标记该代码已添加
}
});
// 处理规则7:计划中有的但实际没有的股票(买入)
filteredPlanData.forEach(plan => {
const code = plan['证券代码'];
if (!addedCodes.has(code)) { // 只添加未添加过的代码
const existingPosition = filteredPositionData.find(p => p['证券代码'] === code);
if (!existingPosition) {
const targetRatio = (plan['计划定投金额'] / totalPlanAmount * 100).toFixed(2);
// 使用股票市值-再平衡 * 目标比例计算目标市值
const targetRatioValue = plan['计划定投金额'] / totalPlanAmount;
const targetMarketValue = (stockMarketValueRebalance * targetRatioValue).toFixed(2);
// 计算偏离比例(当前占比 - 目标比例)
const deviationRatio = (0 - parseFloat(targetRatio)).toFixed(2);
deviationData.push({
category: plan['类别'],
code: code,
name: plan['证券名称'],
currentMarketValue: 0,
currentRatio: '0.00%',
targetRatio: targetRatio + '%',
targetMarketValue: targetMarketValue,
planPeriod: plan['计划定投周期'],
planAmount: plan['计划定投金额'],
deviationRatio: deviationRatio + '%',
profitRatio: '0.00%',
strategy: plan['策略']
});
addedCodes.add(code); // 标记该代码已添加
}
}
});
// 准备偏离调整表数据
const adjustData = {
'中国': [],
'海外': [],
'防御': []
};
deviationData.forEach(item => {
const category = item.category;
if (!adjustData[category]) return;
const currentValue = parseFloat(item.currentMarketValue);
const targetValue = parseFloat(item.targetMarketValue);
const adjustAmount = targetValue - currentValue;
const currentRatioNum = parseFloat(item.currentRatio);
const targetRatioNum = parseFloat(item.targetRatio);
const deviationRatio = (currentRatioNum - targetRatioNum).toFixed(2);
let operation = '';
if (adjustAmount > 3000) {
operation = '买入 ' + adjustAmount.toFixed(2);
} else if (adjustAmount < -3000) {
operation = '卖出 ' + Math.abs(adjustAmount).toFixed(2);
} else {
operation = '无需调整(调整金额小于3000元)';
}
adjustData[category].push({
code: item.code,
name: item.name,
currentMarketValue: item.currentMarketValue,
currentRatio: item.currentRatio,
targetRatio: item.targetRatio,
targetMarketValue: item.targetMarketValue,
adjustAmount: adjustAmount.toFixed(2),
deviationRatio: deviationRatio + '%',
operation: operation
});
});
// 生成策略建议
const strategySuggestions = [];
// 规则4:分批止盈检查
filteredPositionData.forEach(position => {
const profitRatio = getProfitRatioValue(position);
if (profitRatio >= 20) {
strategySuggestions.push(`需要对${position['证券简称']}(${position['证券代码']})卖出20%,卖出资金进入511880`);
} else if (profitRatio >= 10) {
strategySuggestions.push(`建议对${position['证券简称']}(${position['证券代码']})设置条件单`);
}
});
// 规则5:再平衡月份检查
const currentMonth = new Date().getMonth() + 1;
if (currentMonth !== 1 && currentMonth !== 7) {
strategySuggestions.push('当前为模拟阶段,不建议实际操作');
}
// 规则6:奖金检查
if (bonusAmount > 0) {
strategySuggestions.push(`总盈利${floatingProfit.toFixed(2)},需提取10%作为个人奖金:${bonusAmount.toFixed(2)}`);
}
// 规则4:大类偏离检查
for (const [category, amount] of Object.entries(categoryMap)) {
const targetRatio = amount / totalPlanAmount;
const categoryStocks = filteredPositionData.filter(p => {
const plan = planMap[p['证券代码']];
return plan && plan['类别'] === category;
});
const categoryMarketValue = categoryStocks.reduce((sum, stock) => sum + (stock['当前市值'] || 0), 0);
const currentRatio = categoryMarketValue / availableForRebalance;
const deviation = Math.abs(currentRatio - targetRatio);
if (deviation > 0.05) {
strategySuggestions.push(`${category}类别偏离目标比例${(deviation * 100).toFixed(2)}%,建议进行大类再平衡`);
}
}
// 规则7:股票缺失检查
filteredPlanData.forEach(plan => {
const existing = filteredPositionData.find(p => p['证券代码'] === plan['证券代码']);
if (!existing) {
strategySuggestions.push(`计划中有${plan['证券名称']}(${plan['证券代码']}),但实际持仓中没有,建议买入`);
}
});
filteredPositionData.forEach(position => {
const existing = filteredPlanData.find(p => p['证券代码'] === position['证券代码']);
if (!existing) {
strategySuggestions.push(`实际持仓中有${position['证券简称']}(${position['证券代码']}),但计划中没有,建议卖出`);
}
});
// 打新底仓检查
const mideaStock = positionData.find(p => p['证券代码'] === '000333');
const yangtzeStock = positionData.find(p => p['证券代码'] === '600900');
const totalIPOStockValue = (mideaStock ? mideaStock['当前市值'] : 0) + (yangtzeStock ? yangtzeStock['当前市值'] : 0);
if (totalIPOStockValue < 20000) {
strategySuggestions.push('打新底仓不足2万元,建议保留打新底仓');
}
// 渲染资产统计表
renderAssetTable(totalAssets, totalInvestment, availableCash, floatingProfit, stockMarketValueRebalance, stockMarketValueAll);
renderCategoryTable(categoryPercentages, categoryActualPercentages);
renderDeviationTable(deviationData);
renderAdjustTables(adjustData);
renderStrategySuggestions(strategySuggestions);
// 计算并渲染仓位总览
computeAndRenderOverview(positionData, planData);
}
// 渲染资产统计表
function renderAssetTable(totalAssets, totalInvestment, availableCash, floatingProfit, stockMarketValueRebalance, stockMarketValueAll) {
const table = document.getElementById('assetTable');
const tbody = table.querySelector('tbody');
tbody.innerHTML = '';
// 计算浮动盈亏比例
const floatingProfitRatio = totalInvestment > 0 ? (floatingProfit / totalInvestment * 100) : 0;
const row = tbody.insertRow();
row.insertCell().textContent = formatNumber(totalAssets);
row.insertCell().textContent = formatNumber(totalInvestment);
row.insertCell().textContent = formatNumber(availableCash);
// 浮动盈亏列,带条件样式
const profitCell = row.insertCell();
profitCell.textContent = formatNumber(floatingProfit);
if (floatingProfit > 0) {
profitCell.style.color = 'red';
} else if (floatingProfit < 0) {
profitCell.style.color = 'green';
}
// 浮动盈亏比例列,带条件样式
const profitRatioCell = row.insertCell();
profitRatioCell.textContent = floatingProfitRatio.toFixed(2) + '%';
if (floatingProfitRatio > 0) {
profitRatioCell.style.color = 'red';
} else if (floatingProfitRatio < 0) {
profitRatioCell.style.color = 'green';
}
row.insertCell().textContent = formatNumber(stockMarketValueRebalance);
row.insertCell().textContent = formatNumber(stockMarketValueAll);
}
// 渲染三大类仓位表
function renderCategoryTable(categoryPercentages, categoryActualPercentages) {
const table = document.getElementById('categoryTable');
const tbody = table.querySelector('tbody');
tbody.innerHTML = '';
// 获取所有类别的集合
const allCategories = new Set([...Object.keys(categoryPercentages), ...Object.keys(categoryActualPercentages)]);
for (const category of allCategories) {
const row = tbody.insertRow();
row.insertCell().textContent = category;
const targetRatio = categoryPercentages[category] || '0.00%';
const actualRatio = categoryActualPercentages[category] || '0.00%';
row.insertCell().textContent = targetRatio;
row.insertCell().textContent = actualRatio;
// 计算偏差值
const targetValue = parseFloat(targetRatio) || 0;
const actualValue = parseFloat(actualRatio) || 0;
const deviation = actualValue - targetValue;
// 添加偏差列并应用条件样式
const deviationCell = row.insertCell();
deviationCell.textContent = deviation.toFixed(2) + '%';
// 如果偏差绝对值大于5%,设置为粉红色
if (Math.abs(deviation) > 5) {
deviationCell.style.backgroundColor = 'pink';
}
}
}
// 渲染偏离情况表
function renderDeviationTable(data) {
const table = document.getElementById('deviationTable');
const tbody = table.querySelector('tbody');
tbody.innerHTML = '';
// 按证券代码去重,保留市值不为0的记录(如果有)
const uniqueData = {};
data.forEach(item => {
const code = item.code;
// 如果该代码还没有记录,或者当前记录的市值不为0且之前的记录市值为0,则替换
if (!uniqueData[code] || (parseFloat(item.currentMarketValue) > 0 && parseFloat(uniqueData[code].currentMarketValue) === 0)) {
uniqueData[code] = item;
}
});
// 渲染去重后的数据,并按类别排序
Object.values(uniqueData).sort((a, b) => a.category.localeCompare(b.category)).forEach(item => {
const row = tbody.insertRow();
row.insertCell().textContent = item.category;
row.insertCell().textContent = item.code;
row.insertCell().textContent = item.name;
row.insertCell().textContent = formatNumber(item.currentMarketValue);
row.insertCell().textContent = item.currentRatio;
row.insertCell().textContent = item.targetRatio;
row.insertCell().textContent = formatNumber(item.targetMarketValue);
row.insertCell().textContent = item.planPeriod;
row.insertCell().textContent = formatNumber(item.planAmount);
// 渲染偏离比例列
const deviationCell = row.insertCell();
deviationCell.textContent = item.deviationRatio;
const deviationValue = parseFloat(item.deviationRatio);
// 根据偏离比例设置颜色:正值红色,负值绿色
if (deviationValue > 0) {
deviationCell.classList.add('highlight-red');
} else if (deviationValue < 0) {
deviationCell.classList.add('highlight-green');
}
const profitCell = row.insertCell();
profitCell.textContent = item.profitRatio;
const profitValue = parseFloat(item.profitRatio);
if (profitValue > 5) {
profitCell.classList.add('highlight-light-red');
} else if (profitValue < -5) {
profitCell.classList.add('highlight-light-green');
}
row.insertCell().textContent = item.strategy;
});
}
// 渲染调整表
function renderAdjustTables(adjustData) {
const tables = {
'中国': document.getElementById('chinaAdjustTable'),
'海外': document.getElementById('overseasAdjustTable'),
'防御': document.getElementById('defenseAdjustTable')
};
for (const [category, data] of Object.entries(adjustData)) {
const table = tables[category];
const tbody = table.querySelector('tbody');
tbody.innerHTML = '';
// 按证券代码去重,保留市值不为0的记录(如果有)
const uniqueData = {};
data.forEach(item => {
const code = item.code;
// 如果该代码还没有记录,或者当前记录的市值不为0且之前的记录市值为0,则替换
if (!uniqueData[code] || (parseFloat(item.currentMarketValue) > 0 && parseFloat(uniqueData[code].currentMarketValue) === 0)) {
uniqueData[code] = item;
}
});
// 渲染去重后的数据
Object.values(uniqueData).forEach(item => {
const row = tbody.insertRow();
row.insertCell().textContent = item.code;
row.insertCell().textContent = item.name;
row.insertCell().textContent = formatNumber(item.currentMarketValue);
row.insertCell().textContent = item.currentRatio;
row.insertCell().textContent = item.targetRatio;
row.insertCell().textContent = formatNumber(item.targetMarketValue);
const adjustCell = row.insertCell();
adjustCell.textContent = formatNumber(item.adjustAmount);
if (Math.abs(parseFloat(item.adjustAmount)) > 3000) {
adjustCell.classList.add('highlight-pink');
}
const deviationCell = row.insertCell();
deviationCell.textContent = item.deviationRatio;
if (Math.abs(parseFloat(item.deviationRatio)) > 5) {
deviationCell.classList.add('highlight-light-green');
}
row.insertCell().textContent = item.operation;
});
}
}
// 渲染策略建议
function renderStrategySuggestions(suggestions) {
const list = document.getElementById('strategySuggestions');
list.innerHTML = '';
suggestions.forEach(suggestion => {
const li = document.createElement('li');
li.textContent = suggestion;
list.appendChild(li);
});
}
// 仓位总览的原始数据缓存
let overviewRawData = [];
// 计算并渲染仓位总览
function computeAndRenderOverview(positionData, planData) {
const table = document.getElementById('overviewTable');
const tbody = table.querySelector('tbody');
const tfoot = document.getElementById('overviewTableFoot');
tbody.innerHTML = '';
tfoot.innerHTML = '';
// 创建positionData的映射
const positionMap = {};
positionData.forEach(item => {
positionMap[item['证券代码']] = item;
});
// 计算大类计划仓位总和(计划仓位表已经是万元)
const categoryPlanTotal = {};
planData.forEach(item => {
const category = item['类别'] || '其他';
const planPosition = toNum(item[' 本轮计划仓位']);
if (!categoryPlanTotal[category]) {
categoryPlanTotal[category] = 0;
}
categoryPlanTotal[category] += planPosition;
});
// 计算所有证券的本轮计划仓位总和
const totalPlanPosition = Object.values(categoryPlanTotal).reduce((sum, val) => sum + val, 0);
// 计算大类实际市值总和(投资仓位表是元,转换为万元,保留两位小数)
const categoryActualTotal = {};
planData.forEach(item => {
const category = item['类别'] || '其他';
const positionItem = positionMap[item['证券代码']];
// 元转换为万元,保留两位小数
const actualPosition = positionItem ? Number((toNum(positionItem['当前市值']) / 10000).toFixed(2)) : 0;
if (!categoryActualTotal[category]) {
categoryActualTotal[category] = 0;
}
categoryActualTotal[category] += actualPosition;
});
// 计算所有证券的实际市值总和
const totalActualPosition = Object.values(categoryActualTotal).reduce((sum, val) => sum + val, 0);
// 保存原始数据
overviewRawData = planData.map(planItem => {
const code = planItem['证券代码'];
const category = planItem['类别'] || '其他';
const positionItem = positionMap[code];
// 获取基本数据
const securityName = positionItem ? positionItem['证券简称'] : planItem['证券名称'];
const currentPlanPosition = toNum(planItem[' 本轮计划仓位']); // 已经是万元
const lastPlanPosition = toNum(planItem['上一轮计划仓位']); // 已经是万元
// 元转换为万元,保留两位小数
const actualPosition = positionItem ? Number((toNum(positionItem['当前市值']) / 10000).toFixed(2)) : 0;
// 计算占大类的计划仓位比例
const categoryPlanRatio = categoryPlanTotal[category] > 0
? (currentPlanPosition / categoryPlanTotal[category] * 100).toFixed(2) + '%'
: '0.00%';
// 计算占整体的计划仓位比例
const totalPlanRatio = totalPlanPosition > 0
? (currentPlanPosition / totalPlanPosition * 100).toFixed(2) + '%'
: '0.00%';
// 计算占大类的实际仓位比例
const categoryActualRatio = categoryActualTotal[category] > 0
? (actualPosition / categoryActualTotal[category] * 100).toFixed(2) + '%'
: '0.00%';
// 计算占整体的实际仓位比例
const totalActualRatio = totalActualPosition > 0
? (actualPosition / totalActualPosition * 100).toFixed(2) + '%'
: '0.00%';
// 计算两个仓位差额
const positionDiff = currentPlanPosition - actualPosition;
// 计算仓位相差的比例
const positionDiffRatio = currentPlanPosition > 0
? (positionDiff / currentPlanPosition * 100).toFixed(2) + '%'
: '0.00%';
// 计算仓位调整金额
const adjustAmount = currentPlanPosition - lastPlanPosition;
// 计算仓位调整比例
const adjustRatio = currentPlanPosition > 0
? (adjustAmount / currentPlanPosition * 100).toFixed(2) + '%'
: '0.00%';
return {
category,
securityName,
code,
currentPlanPosition,
categoryPlanRatio,
totalPlanRatio,
actualPosition,
categoryActualRatio,
totalActualRatio,
lastPlanPosition,
positionDiff,
positionDiffRatio,
adjustAmount,
adjustRatio
};
});
// 填充大类下拉框
const categorySelect = document.getElementById('overviewCategoryFilter');
const categories = Array.from(new Set(overviewRawData.map(item => item.category))).sort();
categorySelect.innerHTML = '<option value="">全部</option>';
categories.forEach(cat => {
const option = document.createElement('option');
option.value = cat;
option.textContent = cat;
categorySelect.appendChild(option);
});
// 渲染表格
renderFilteredOverviewTable();
}
// 渲染筛选后的仓位总览表格
function renderFilteredOverviewTable() {
const table = document.getElementById('overviewTable');
const tbody = table.querySelector('tbody');
const tfoot = document.getElementById('overviewTableFoot');
tbody.innerHTML = '';
tfoot.innerHTML = '';
// 获取筛选条件
const selectedCategory = document.getElementById('overviewCategoryFilter').value;
const searchText = document.getElementById('overviewSearchInput').value.toLowerCase();
// 筛选数据
const filteredData = overviewRawData.filter(item => {
const categoryMatch = !selectedCategory || item.category === selectedCategory;
const searchMatch = !searchText ||
item.securityName.toLowerCase().includes(searchText) ||
item.code.toLowerCase().includes(searchText);
return categoryMatch && searchMatch;
});
// 渲染行
filteredData.forEach(item => {
const row = tbody.insertRow();
row.insertCell().textContent = item.category;
row.insertCell().textContent = item.securityName;
row.insertCell().textContent = item.code;
row.insertCell().textContent = formatNumber(item.currentPlanPosition);
row.insertCell().textContent = item.categoryPlanRatio;
row.insertCell().textContent = item.totalPlanRatio;
row.insertCell().textContent = formatNumber(item.actualPosition);
row.insertCell().textContent = item.categoryActualRatio;
row.insertCell().textContent = item.totalActualRatio;
row.insertCell().textContent = formatNumber(item.lastPlanPosition);
row.insertCell().textContent = formatNumber(item.positionDiff);
row.insertCell().textContent = item.positionDiffRatio;
row.insertCell().textContent = formatNumber(item.adjustAmount);
row.insertCell().textContent = item.adjustRatio;
});
// 计算汇总
const summary = filteredData.reduce((acc, item) => {
acc.currentPlanPosition += item.currentPlanPosition;
acc.actualPosition += item.actualPosition;
acc.lastPlanPosition += item.lastPlanPosition;
acc.positionDiff += item.positionDiff;
acc.adjustAmount += item.adjustAmount;
return acc;
}, {
currentPlanPosition: 0,
actualPosition: 0,
lastPlanPosition: 0,
positionDiff: 0,
adjustAmount: 0
});
// 渲染汇总行
const footRow = tfoot.insertRow();
footRow.style.fontWeight = 'bold';
footRow.style.backgroundColor = '#f0f0f0';
footRow.insertCell().textContent = '汇总';
footRow.insertCell().textContent = '';
footRow.insertCell().textContent = '';
footRow.insertCell().textContent = formatNumber(summary.currentPlanPosition);
footRow.insertCell().textContent = '';
footRow.insertCell().textContent = '';
footRow.insertCell().textContent = formatNumber(summary.actualPosition);
footRow.insertCell().textContent = '';
footRow.insertCell().textContent = '';
footRow.insertCell().textContent = formatNumber(summary.lastPlanPosition);
footRow.insertCell().textContent = formatNumber(summary.positionDiff);
footRow.insertCell().textContent = '';
footRow.insertCell().textContent = formatNumber(summary.adjustAmount);
footRow.insertCell().textContent = '';
}
function getExcludedCodes() {
if (excludedCodesCache) return Promise.resolve(excludedCodesCache);
return fetch('/api/settings/excluded-stocks')
.then(r => r.ok ? r.json() : Promise.resolve({ codes: [] }))
.then(j => {
const codes = Array.isArray(j.codes) && j.codes.length ? j.codes : ['000333', '600900', '511880'];
excludedCodesCache = codes;
return codes;
})
.catch(() => {
const codes = ['000333', '600900', '511880'];
excludedCodesCache = codes;
return codes;
});
}
function computeWeekRange(date) {
const d = new Date(date.getFullYear(), date.getMonth(), date.getDate());
const day = d.getDay();
const diffToMonday = day === 0 ? 6 : day - 1;
const start = new Date(d);
start.setDate(d.getDate() - diffToMonday);
const end = new Date(start);
end.setDate(start.getDate() + 6);
return { start: formatDate(start), end: formatDate(end) };
}
function formatDate(dt) {
const y = dt.getFullYear();
const m = (dt.getMonth() + 1).toString().padStart(2, '0');
const d = dt.getDate().toString().padStart(2, '0');
return y + '-' + m + '-' + d;
}
tabs.forEach(btn => {
btn.addEventListener('click', () => {
tabs.forEach(b => b.classList.remove('active'));
btn.classList.add('active');
const tab = btn.getAttribute('data-tab');
if (tab === 'analysis') {
uploadSection.style.display = 'block';
historySection.style.display = 'none';
fundSharesSection.style.display = 'none';
settingsSection.style.display = 'none';
overviewSection.style.display = 'none';
resultSection.style.display = 'none';
clearResults();
} else if (tab === 'overview') {
uploadSection.style.display = 'block';
historySection.style.display = 'none';
fundSharesSection.style.display = 'none';
settingsSection.style.display = 'none';
overviewSection.style.display = 'none';
resultSection.style.display = 'none';
clearResults();
} else if (tab === 'history') {
uploadSection.style.display = 'none';
historySection.style.display = 'block';
fundSharesSection.style.display = 'none';
settingsSection.style.display = 'none';
overviewSection.style.display = 'none';
resultSection.style.display = 'none';
clearResults();
} else if (tab === 'fundshares') {
uploadSection.style.display = 'none';
historySection.style.display = 'none';
fundSharesSection.style.display = 'block';
settingsSection.style.display = 'none';
overviewSection.style.display = 'none';
resultSection.style.display = 'none';
clearResults();
} else if (tab === 'settings') {
uploadSection.style.display = 'none';
historySection.style.display = 'none';
fundSharesSection.style.display = 'none';
settingsSection.style.display = 'block';
overviewSection.style.display = 'none';
loadExcludedSettings();
resultSection.style.display = 'none';
clearResults();
}
});
});
// 仓位总览筛选事件
if (overviewCategoryFilter) {
overviewCategoryFilter.addEventListener('change', () => {
if (overviewRawData.length > 0) {
renderFilteredOverviewTable();
}
});
}
if (overviewSearchInput) {
overviewSearchInput.addEventListener('input', () => {
if (overviewRawData.length > 0) {
renderFilteredOverviewTable();
}
});
}
if (overviewResetFilter) {
overviewResetFilter.addEventListener('click', () => {
if (overviewCategoryFilter) overviewCategoryFilter.value = '';
if (overviewSearchInput) overviewSearchInput.value = '';
if (overviewRawData.length > 0) {
renderFilteredOverviewTable();
}
});
}
// 模态窗口事件
if (helpModal && modalClose) {
// 点击问号显示模态窗口
document.addEventListener('click', (e) => {
if (e.target.classList.contains('help-icon')) {
const title = e.target.getAttribute('data-title');
const content = e.target.getAttribute('data-content');
modalTitle.textContent = title;
modalContent.textContent = content;
helpModal.style.display = 'block';
}
});
// 关闭按钮
modalClose.addEventListener('click', () => {
helpModal.style.display = 'none';
});
// 点击遮罩层关闭
helpModal.addEventListener('click', (e) => {
if (e.target === helpModal) {
helpModal.style.display = 'none';
}
});
// ESC键关闭
document.addEventListener('keydown', (e) => {
if (e.key === 'Escape' && helpModal.style.display === 'block') {
helpModal.style.display = 'none';
}
});
}
function setWeek(offsetDelta) {
if (offsetDelta === 0) historyWeekOffset = 0; else historyWeekOffset += offsetDelta;
const now = new Date();
const base = computeWeekRange(now);
const start = new Date(base.start);
const end = new Date(base.end);
start.setDate(start.getDate() + historyWeekOffset * 7);
end.setDate(end.getDate() + historyWeekOffset * 7);
historyStart.value = formatDate(start);
historyEnd.value = formatDate(end);
queryHistory();
}
function setUploadWeek(offsetDelta) {
if (offsetDelta === 0) uploadWeekOffset = 0; else uploadWeekOffset += offsetDelta;
const now = new Date();
const base = computeWeekRange(now);
const start = new Date(base.start);
const end = new Date(base.end);
start.setDate(start.getDate() + uploadWeekOffset * 7);
end.setDate(end.getDate() + uploadWeekOffset * 7);
uploadStart.value = formatDate(start);
uploadEnd.value = formatDate(end);
historyStart.value = formatDate(start);
historyEnd.value = formatDate(end);
fundStart.value = formatDate(start);
fundEnd.value = formatDate(end);
queryHistory();
queryFundShares();
}
btnPrevWeek.addEventListener('click', () => setWeek(-1));
btnThisWeek.addEventListener('click', () => setWeek(0));
btnNextWeek.addEventListener('click', () => setWeek(1));
btnQueryHistory.addEventListener('click', () => queryHistory());
btnUploadPrevWeek.addEventListener('click', () => { setUploadWeek(-1); });
btnUploadThisWeek.addEventListener('click', () => { setUploadWeek(0); });
btnUploadNextWeek.addEventListener('click', () => { setUploadWeek(1); });
function setFundWeek(offsetDelta) {
if (offsetDelta === 0) fundWeekOffset = 0; else fundWeekOffset += offsetDelta;
const now = new Date();
const base = computeWeekRange(now);
const start = new Date(base.start);
const end = new Date(base.end);
start.setDate(start.getDate() + fundWeekOffset * 7);
end.setDate(end.getDate() + fundWeekOffset * 7);
fundStart.value = formatDate(start);
fundEnd.value = formatDate(end);
}
btnFundPrevWeek.addEventListener('click', () => { setFundWeek(-1); queryFundShares(); });
btnFundThisWeek.addEventListener('click', () => { setFundWeek(0); queryFundShares(); });
btnFundNextWeek.addEventListener('click', () => { setFundWeek(1); queryFundShares(); });
function isValidWeekRange(startStr, endStr) {
const s = new Date(startStr);
const e = new Date(endStr);
const ms = e - s;
const d1 = s.getDay();
const d2 = e.getDay();
return ms === 6 * 24 * 60 * 60 * 1000 && d1 === 1 && d2 === 0;
}
function queryHistory() {
historyErrorMsg.style.display = 'none';
resultSection.style.display = 'none';
overviewSection.style.display = 'none';
clearResults();
const start = historyStart.value;
const end = historyEnd.value;
if (!start || !end) {
historyErrorMsg.textContent = '请选择起始和结束时间';
historyErrorMsg.style.display = 'block';
return;
}
if (!isValidWeekRange(start, end)) {
historyErrorMsg.textContent = '必须为同一周的周一至周日范围';
historyErrorMsg.style.display = 'block';
return;
}
fetch('/api/weekly-data?start=' + start + '&end=' + end)
.then(r => {
if (r.status === 404) throw new Error('未找到本周数据');
if (!r.ok) throw new Error('查询失败');
return r.json();
})
.then(async j => {
const codes = await getExcludedCodes();
analyzeData(sanitizeList(j.positionData), sanitizeList(j.planData), sanitizeList(j.assetData), codes);
// 根据当前Tab显示对应内容
const activeTab = document.querySelector('.tab-btn.active');
const tabName = activeTab ? activeTab.getAttribute('data-tab') : 'analysis';
if (tabName === 'overview') {
overviewSection.style.display = 'block';
} else {
resultSection.style.display = 'block';
}
})
.catch(e => {
historyErrorMsg.textContent = e.message;
historyErrorMsg.style.display = 'block';
resultSection.style.display = 'none';
overviewSection.style.display = 'none';
clearResults();
});
}
function queryFundShares() {
fundErrorMsg.style.display = 'none';
const start = fundStart.value;
const end = fundEnd.value;
if (!start || !end) {
fundErrorMsg.textContent = '请选择起始和结束时间';
fundErrorMsg.style.display = 'block';
return;
}
if (!isValidWeekRange(start, end)) {
fundErrorMsg.textContent = '必须为同一周的周一至周日范围';
fundErrorMsg.style.display = 'block';
return;
}
fetch('/api/fund-shares?start=' + start + '&end=' + end)
.then(r => {
if (r.status === 404) throw new Error('未找到该周数据');
if (!r.ok) throw new Error('查询失败');
return r.json();
})
.then(j => {
fundSummaryCache = j.summary || null;
const data = sanitizeList(j.data);
renderFundSharesSummary(data);
renderFundSharesTable(data);
})
.catch(e => {
fundErrorMsg.textContent = e.message;
fundErrorMsg.style.display = 'block';
const tb = document.getElementById('fundSharesTable').querySelector('tbody');
tb.innerHTML = '';
document.getElementById('fundSharesSummary').innerHTML = '';
});
}
btnFundQuery.addEventListener('click', () => queryFundShares());
fundSharesFile.addEventListener('change', async () => {
saveFundWeekBtn.disabled = !(fundSharesFile.files && fundSharesFile.files.length);
fundSaveErrorMsg.style.display = 'none';
fundSaveMsg.style.display = 'none';
fundSqlPreview.value = '';
try {
if (!(fundSharesFile.files && fundSharesFile.files.length)) return;
const data = await parseFundSharesExcel(fundSharesFile.files[0]);
fundParsedDataCache = data;
updateFundSqlPreview();
fundSqlContainer.style.display = showSqlCheckbox.checked ? 'block' : 'none';
} catch (e) {
fundSqlPreview.value = '生成SQL失败: ' + e.message;
}
});
[fundStart, fundEnd].forEach(el => el.addEventListener('change', () => { updateFundSqlPreview(); fundSqlContainer.style.display = showSqlCheckbox.checked ? 'block' : 'none'; }));
showSqlCheckbox.addEventListener('change', () => { fundSqlContainer.style.display = showSqlCheckbox.checked ? 'block' : 'none'; updateFundSqlPreview(); });
clearFundWeekBtn.disabled = false;
saveFundWeekBtn.addEventListener('click', async () => {
fundSaveErrorMsg.style.display = 'none';
fundSaveMsg.style.display = 'none';
try {
if (!fundStart.value || !fundEnd.value || !isValidWeekRange(fundStart.value, fundEnd.value)) {
fundSaveErrorMsg.textContent = '起止日期必须为同一周的周一到周日';
fundSaveErrorMsg.style.display = 'block';
return;
}
const data = await parseFundSharesExcel(fundSharesFile.files[0]);
if (!Array.isArray(data) || !data.length || !hasProfitRatioColumn(data[0])) {
throw new Error('资金股份表缺少必要列:盈亏比例');
}
const start = fundStart.value;
const end = fundEnd.value;
const existsResp = await fetch('/api/fund-shares?start=' + start + '&end=' + end);
if (existsResp.status === 200) {
const confirmOverwrite = window.confirm('该周资金股份数据已存在,是否覆盖?');
if (!confirmOverwrite) {
fundSaveMsg.textContent = '已取消覆盖';
fundSaveMsg.style.display = 'block';
return;
}
}
const overwrite = existsResp.status === 200;
const summaryToSave = (fundSummaryCache && Object.keys(fundSummaryCache).length)
? fundSummaryCache
: null;
if (!summaryToSave || (summaryToSave['余额'] == null && summaryToSave['可用'] == null && summaryToSave['参考市值'] == null && summaryToSave['资产'] == null)) {
fundSaveErrorMsg.textContent = '未识别到顶部汇总(币种/余额/可用/参考市值/资产/盈亏/当日盈亏)。请确认Excel第一行是标签、第二行是对应数值。';
fundSaveErrorMsg.style.display = 'block';
return;
}
const resp = await fetch('/api/fund-shares', {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({ weekStart: start, weekEnd: end, data, summary: summaryToSave, overwrite })
});
if (!resp.ok) {
const j = await resp.json().catch(() => ({}));
throw new Error(j.error || '保存失败');
}
fundSaveMsg.textContent = '保存成功:' + start + ' 至 ' + end;
fundSaveMsg.style.display = 'block';
updateFundSqlPreview();
// Sync to weekly report
try {
// Use start date (Monday) as the date
const dateToSend = String(start).trim();
console.log('Calling syncToWeekly for fund_shares with date:', dateToSend);
await syncToWeekly('fund_shares', dateToSend, { summary: summaryToSave });
fundSaveMsg.textContent += ' (已同步至周报)';
} catch (e) {
console.error('Sync failed', e);
fundSaveMsg.textContent += ' (同步周报失败: ' + e.message + ')';
}
} catch (e) {
fundSaveErrorMsg.textContent = e.message;
fundSaveErrorMsg.style.display = 'block';
}
});
clearFundWeekBtn.addEventListener('click', async () => {
fundSaveErrorMsg.style.display = 'none';
fundSaveMsg.style.display = 'none';
try {
const pwd = window.prompt('请输入清空密码');
if (pwd !== '93220') {
fundSaveErrorMsg.textContent = '密码错误,无法清空';
fundSaveErrorMsg.style.display = 'block';
return;
}
const resp = await fetch('/api/fund-shares/clear-all', {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({ password: '93220' })
});
if (!resp.ok) {
const j = await resp.json().catch(() => ({}));
throw new Error(j.error || '清空失败');
}
fundSaveMsg.textContent = '已清空所有资金股份历史数据';
fundSaveMsg.style.display = 'block';
document.getElementById('fundSharesSummary').innerHTML = '';
const tb = document.getElementById('fundSharesTable').querySelector('tbody');
tb.innerHTML = '';
} catch (e) {
fundSaveErrorMsg.textContent = e.message;
fundSaveErrorMsg.style.display = 'block';
}
});
function renderFundSharesTable(data) {
const table = document.getElementById('fundSharesTable');
const tbody = table.querySelector('tbody');
tbody.innerHTML = '';
data.forEach(item => {
const row = tbody.insertRow();
const pr = getProfitRatioValue(item);
if (!isNaN(pr)) {
if (pr > 0) row.style.color = 'red';
else if (pr < 0) row.style.color = 'blue';
}
const cells = [
item['证券代码'],
item['证券名称'],
item['股份余额'],
item['实际数量'],
item['购买数量'],
item['今买数量'],
item['持仓成本价'],
item['买入均价'],
item['当前价'],
item['最新市值'],
item['当日盈亏'],
item['持仓盈亏'],
getProfitRatioDisplay(item),
item['冻结数量'],
item['股东代码'],
item['交易所名称']
];
cells.forEach(v => {
const c = row.insertCell();
if (typeof v === 'number') c.textContent = formatNumber(v); else c.textContent = v || '';
});
});
}
function renderFundSharesSummary(data) {
const el = document.getElementById('fundSharesSummary');
const totalMarket = (fundSummaryCache && typeof fundSummaryCache['参考市值'] === 'number') ? fundSummaryCache['参考市值'] : null;
const dayProfit = (fundSummaryCache && typeof fundSummaryCache['当日盈亏'] === 'number') ? fundSummaryCache['当日盈亏'] : null;
const holdProfit = (fundSummaryCache && typeof fundSummaryCache['盈亏'] === 'number') ? fundSummaryCache['盈亏'] : null;
const balance = (fundSummaryCache && typeof fundSummaryCache['余额'] === 'number') ? fundSummaryCache['余额'] : null;
const available = (fundSummaryCache && typeof fundSummaryCache['可用'] === 'number') ? fundSummaryCache['可用'] : null;
const items = [
{ label: '余额', value: balance },
{ label: '可用', value: available },
{ label: '参考市值', value: totalMarket },
{ label: '资产', value: (fundSummaryCache && typeof fundSummaryCache['资产'] === 'number') ? fundSummaryCache['资产'] : totalMarket },
{ label: '盈亏', value: holdProfit },
{ label: '当日盈亏', value: dayProfit }
];
el.innerHTML = items.map(it => {
const val = typeof it.value === 'number' ? formatNumber(it.value) : '—';
const color = typeof it.value === 'number' ? (it.value > 0 ? 'red' : (it.value < 0 ? 'blue' : '#2c3e50')) : '#2c3e50';
return '<div class="fund-summary-item"><span class="fund-summary-label">' + it.label + ':</span><span class="fund-summary-value" style="color:' + color + '">' + val + '</span></div>';
}).join('');
}
function toNum(v) { const n = parseFloat(String(v||'').replace(/[,\s]/g,'')); return isNaN(n) ? 0 : n; }
function normalizeKey(k){ return String(k||'').replace(/[\s%%()()]/g,''); }
function findColumnKey(obj, names){ const ks = Object.keys(obj||{}); for (let i=0;i<ks.length;i++){ const nk = normalizeKey(ks[i]); for (const nm of names){ if (nk === normalizeKey(nm)) return ks[i]; } } return null; }
function hasProfitRatioColumn(obj){ return !!findColumnKey(obj, ['盈亏比例','盈亏比例(%)']); }
function getProfitRatioValue(item){ const k = findColumnKey(item, ['盈亏比例','盈亏比例(%)']); if (!k) return NaN; const v = item[k]; const n = parseFloat(String(v||'').replace(/[,%%\s]/g,'')); return isNaN(n)?NaN:n; }
function getProfitRatioDisplay(item){ const v = getProfitRatioValue(item); if (isNaN(v)) return ''; return v.toFixed(2); }
function formatProfitRatio(obj){ const v = getProfitRatioValue(obj); if (isNaN(v)) return ''; return v.toFixed(2) + '%'; }
function findScalar(list, names){ if (!Array.isArray(list)) return null; for (let i=0;i<list.length;i++){ const k = findColumnKey(list[i], names); if (k){ const val = toNum(list[i][k]); if (!isNaN(val)) return val; } } return null; }
function parseFundSharesExcel(file){
return new Promise((resolve, reject) => {
const reader = new FileReader();
reader.onload = async function(e){
try {
await ensureXLSX();
const data = new Uint8Array(e.target.result);
const wb = XLSX.read(data, { type: 'array', codepage: 936 });
const sheetName = wb.SheetNames[0];
const ws = wb.Sheets[sheetName];
const rows = XLSX.utils.sheet_to_json(ws, { header: 1, raw: false, defval: '' });
try {
fundSummaryCache = extractFundSummaryFixed(ws);
if (!fundSummaryCache || !Object.keys(fundSummaryCache).length) {
fundSummaryCache = extractFundSummary(rows);
}
} catch (e) { fundSummaryCache = null; }
if (!Array.isArray(rows) || !rows.length) return resolve([]);
const norm = (s) => String(s||'').trim();
const nkey = (s) => String(s||'').replace(/[\s%%()()]/g,'');
const synonyms = {
'证券代码': ['证券代码','股票代码','代码'],
'证券名称': ['证券名称','证券简称','名称'],
'股份余额': ['股份余额','股票余额','余额股份'],
'实际数量': ['实际数量','数量'],
'购买数量': ['购买数量','买入数量','可买数量'],
'今买数量': ['今买数量','今买'],
'持仓成本价': ['持仓成本价','持仓成本','成本价'],
'买入均价': ['买入均价','买入均价(元)','买入均价元'],
'当前价': ['当前价','当前价格','现价'],
'最新市值': ['最新市值','市值'],
'当日盈亏': ['当日盈亏','今日盈亏'],
'持仓盈亏': ['持仓盈亏','盈亏'],
'盈亏比例': ['盈亏比例','盈亏比例(%)'],
'冻结数量': ['冻结数量','冻结'],
'股东代码': ['股东代码','股东ID'],
'交易所名称': ['交易所名称','交易所']
};
function buildHeaderMap(row){
const map = {};
for (let ci=0; ci<row.length; ci++){
const cell = norm(row[ci]);
const nk = nkey(cell);
Object.keys(synonyms).forEach(canon => {
synonyms[canon].forEach(syn => {
const sk = nkey(syn);
if (nk === sk || nk.includes(sk)) map[canon] = ci;
});
});
}
return map;
}
function rowMatchScore(row){
const map = buildHeaderMap(row);
const essential = ['证券代码','证券名称','最新市值','盈亏比例','当前价','持仓盈亏'];
let score = 0; essential.forEach(k => { if (map[k] !== undefined) score++; });
return { score, map };
}
let headerIdx = -1; let headerMap = {};
for (let i=0;i<rows.length;i++){
const { score, map } = rowMatchScore(rows[i]);
const hasCode = map['证券代码'] !== undefined;
if (hasCode && score >= 3){ headerIdx = i; headerMap = map; break; }
}
if (headerIdx < 0){
// try near first data row
let startIdx = -1;
for (let i=0;i<rows.length;i++){
const r = rows[i];
if (!Array.isArray(r) || !r.length) continue;
const c0 = norm(r[0]);
if (/^\d{6}$/.test(c0)) { startIdx = i; break; }
}
if (startIdx >= 0){
for (let k=Math.max(0,startIdx-5); k<startIdx; k++){
const { score, map } = rowMatchScore(rows[k]);
if (map['证券代码'] !== undefined && score >= 3){ headerIdx = k; headerMap = map; break; }
}
}
}
if (headerIdx < 0){
// Fallback A: use first row as header to objects, then map keys
const objs = XLSX.utils.sheet_to_json(ws, { defval: '' });
if (Array.isArray(objs) && objs.length){
const sample = objs[0];
const keys = Object.keys(sample);
const map = {};
keys.forEach(k => {
const nk = nkey(k);
Object.keys(synonyms).forEach(canon => {
synonyms[canon].forEach(syn => { if (nk === nkey(syn)) map[canon] = k; });
});
});
if (map['证券代码']){
let out = objs.map(o => {
const obj = {};
Object.keys(map).forEach(canon => { obj[canon] = o[map[canon]]; });
return obj;
}).filter(o => o['证券代码']);
out = out.map(o => {
if (o['盈亏比例'] === undefined && (o['持仓盈亏'] !== undefined) && (o['最新市值'] !== undefined)){
const mv = toNum(o['最新市值']); const hp = toNum(o['持仓盈亏']);
o['盈亏比例'] = mv ? (hp / mv * 100) : 0;
}
o['证券名称'] = fixGarbledText(o['证券名称']);
o['交易所名称'] = fixGarbledText(o['交易所名称']);
return o;
});
return resolve(sanitizeList(out));
}
}
// Fallback B: infer by position from first data row with 6位代码
const canonOrder = ['证券代码','证券名称','股份余额','实际数量','购买数量','今买数量','持仓成本价','买入均价','当前价','最新市值','当日盈亏','持仓盈亏','盈亏比例','冻结数量','股东代码','交易所名称'];
let startIdx = -1;
for (let i=0;i<rows.length;i++){
const r = rows[i];
if (!Array.isArray(r) || !r.length) continue;
const c0 = norm(r[0]);
if (/^\d{6}$/.test(c0)) { startIdx = i; break; }
}
if (startIdx >= 0){
const dataRows = rows.slice(startIdx).filter(r => Array.isArray(r) && /^\d{6}$/.test(norm(r[0])));
const out = dataRows.map(r => {
const obj = {};
for (let j=0;j<canonOrder.length;j++){
obj[canonOrder[j]] = r[j] !== undefined ? r[j] : '';
}
if (obj['盈亏比例'] === '' || obj['盈亏比例'] === undefined){
const mv = toNum(obj['最新市值']); const hp = toNum(obj['持仓盈亏']);
obj['盈亏比例'] = mv ? (hp / mv * 100) : 0;
}
obj['证券名称'] = fixGarbledText(obj['证券名称']);
obj['交易所名称'] = fixGarbledText(obj['交易所名称']);
return obj;
});
return resolve(sanitizeList(out));
}
// Fallback C: return empty to让上层提示缺列
return resolve([]);
}
const dataRows = rows.slice(headerIdx+1).filter(r => r && r.length);
let out = dataRows.map(r => {
const obj = {};
Object.keys(headerMap).forEach(canon => {
const idx = headerMap[canon];
obj[canon] = r[idx];
});
return obj;
}).filter(o => o['证券代码']);
out = out.map(o => {
if (o['盈亏比例'] === undefined && (o['持仓盈亏'] !== undefined) && (o['最新市值'] !== undefined)){
const mv = toNum(o['最新市值']); const hp = toNum(o['持仓盈亏']);
o['盈亏比例'] = mv ? (hp / mv * 100) : 0;
}
o['证券名称'] = fixGarbledText(o['证券名称']);
o['交易所名称'] = fixGarbledText(o['交易所名称']);
return o;
});
resolve(sanitizeList(out));
} catch (err){
reject(new Error('解析Excel文件失败: ' + err.message));
}
};
reader.onerror = () => reject(new Error('读取文件失败'));
reader.readAsArrayBuffer(file);
});
}
function extractFundSummary(rows){
const pickNum = (str) => {
const m = String(str||'').match(/-?\d[\d,]*(?:\.\d+)?/);
if (!m) return null;
const t = m[0].replace(/,/g,'');
const v = parseFloat(t);
return isNaN(v) ? null : v;
};
const keys = ['余额','可用','参考市值','资产','盈亏','当日盈亏'];
const res = {};
const maxScan = Math.min(rows.length, 50);
const syn = {
'余额': ['余额','资金余额','人民币余额'],
'可用': ['可用','可用资金','人民币可用'],
'参考市值': ['参考市值','参考市值元','参考市值金额'],
'资产': ['资产','资产值','资产总值'],
'盈亏': ['盈亏','总盈亏'],
'当日盈亏': ['当日盈亏','今日盈亏']
};
const norm = (s) => String(s||'').replace(/[\s::,,()()]/g,'');
function findLabelIndices(row) {
const idx = {};
for (let j=0;j<row.length;j++){
const s = norm(row[j]);
keys.concat(['币种']).forEach(k => {
const arr = syn[k] || [k];
if (arr.some(x => norm(x) === s)) idx[k] = j;
});
}
return idx;
}
for (let i=0;i<maxScan;i++){
const r = rows[i];
if (!Array.isArray(r)) continue;
const labelIdx = findLabelIndices(r);
// 如果该行是摘要的表头行,则优先用下一行同列填充所有摘要字段
const hasLabels = ['币种','余额','可用','参考市值','资产','盈亏','当日盈亏'].filter(k => labelIdx[k] != null).length;
if (hasLabels >= 3 && rows[i+1] && Array.isArray(rows[i+1])) {
const next = rows[i+1];
if (labelIdx['币种'] != null && res['币种'] == null) {
const j = labelIdx['币种'];
res['币种'] = String(next[j]||'').trim();
}
['余额','可用','参考市值','资产','盈亏','当日盈亏'].forEach(k => {
if (res[k] == null && labelIdx[k] != null) {
const j = labelIdx[k];
let val = pickNum(next[j]);
if (val == null && r[j+1] != null) val = pickNum(r[j+1]);
if (val == null && j>0 && r[j-1] != null) val = pickNum(r[j-1]);
if (val == null && rows[i-1] && Array.isArray(rows[i-1])) val = pickNum(rows[i-1][j]);
if (val != null) res[k] = val;
}
});
}
for (let j=0;j<r.length;j++){
const cell = String(r[j]||'');
keys.forEach(k => {
if (res[k] == null && (cell.includes(k) || /余额|可用|参考市值|资产|盈亏|当日盈亏/.test(cell))){
let val = pickNum(cell);
if (val == null && r[j+1] != null) val = pickNum(r[j+1]);
if (val == null && j > 0 && r[j-1] != null) val = pickNum(r[j-1]);
if (val == null && rows[i+1] && Array.isArray(rows[i+1])) val = pickNum(rows[i+1][j]);
if (val == null && rows[i-1] && Array.isArray(rows[i-1])) val = pickNum(rows[i-1][j]);
if (val != null) res[k] = val;
}
});
}
// Fallback by sequence: if next row contains currency text and multiple numbers, assign in order
if (rows[i+1] && Array.isArray(rows[i+1])) {
const next = rows[i+1];
const hasCurrency = next.some(c => /^(人民币|CNY|RMB)$/i.test(String(c||'').trim()));
const numbers = [];
for (let j=0;j<next.length;j++){
const v = pickNum(next[j]);
if (v != null) numbers.push(v);
}
if (hasCurrency && numbers.length >= 2) {
const orderKeys = ['余额','可用','参考市值','资产','盈亏','当日盈亏'];
let idx = 0;
orderKeys.forEach(k => { if (res[k] == null && numbers[idx] != null) { res[k] = numbers[idx]; idx++; } });
if (res['币种'] == null) res['币种'] = '人民币';
}
}
}
return res;
}
function extractFundSummaryFixed(ws){
function getCell(addr){ const c = ws[addr]; return c ? (typeof c.w !== 'undefined' ? c.w : c.v) : ''; }
const pickNum = (str) => { const m = String(str||'').match(/-?\d[\d,]*(?:\.\d+)?/); if (!m) return null; const t = m[0].replace(/,/g,''); const v = parseFloat(t); return isNaN(v) ? null : v; };
function normalizeCurrencyText(s){
const t = String(s||'').trim();
if (/^(人民币|RMB|CNY)$/i.test(t)) return '人民币';
const dec = fixGarbledText(t);
if (/[\u4E00-\u9FFF]/.test(dec)) {
if (/人民币/.test(dec)) return '人民币';
return dec;
}
return t;
}
const out = {};
// 固定位置读取(不再校验标签文字)
out['币种'] = normalizeCurrencyText(getCell('A2'));
out['余额'] = pickNum(getCell('B2'));
out['可用'] = pickNum(getCell('C2'));
out['参考市值'] = pickNum(getCell('D2'));
out['资产'] = pickNum(getCell('E2'));
out['盈亏'] = pickNum(getCell('F2'));
out['当日盈亏'] = pickNum(getCell('G2'));
return out;
}
// 移除聚合兜底,摘要只来源于Excel顶部摘要区域
function fixGarbledText(s){
if (!s || typeof s !== 'string') return s;
const hasCJK = /[\u3400-\u9FBF]/.test(s);
const hasHigh = /[\x80-\xFF]/.test(s);
if (hasCJK || !hasHigh) return s;
try {
const bytes = [];
for (let i=0;i<s.length;i++){ bytes.push(s.charCodeAt(i) & 0xFF); }
if (window.cptable && window.cptable.utils && window.cptable.utils.decode){
return window.cptable.utils.decode(936, bytes);
}
} catch (e) {}
return s;
}
function clearResults() {
const ids = ['assetTable','categoryTable','deviationTable','chinaAdjustTable','overseasAdjustTable','defenseAdjustTable'];
ids.forEach(id => {
const t = document.getElementById(id);
if (t) {
const tb = t.querySelector('tbody');
if (tb) tb.innerHTML = '';
}
});
const list = document.getElementById('strategySuggestions');
if (list) list.innerHTML = '';
}
function loadExcludedSettings() {
settingsMsg.style.display = 'none';
settingsErrorMsg.style.display = 'none';
fetch('/api/settings/excluded-stocks')
.then(r => r.ok ? r.json() : Promise.resolve({ codes: ['000333','600900','511880'] }))
.then(j => {
const codes = Array.isArray(j.codes) && j.codes.length ? j.codes : ['000333','600900','511880'];
excludedCodesInput.value = codes.join(',');
})
.catch(() => {
excludedCodesInput.value = ['000333','600900','511880'].join(',');
});
}
btnSaveSettings.addEventListener('click', () => {
settingsMsg.style.display = 'none';
settingsErrorMsg.style.display = 'none';
const raw = excludedCodesInput.value || '';
const codes = raw.split(/[,,\s]+/).map(s => s.trim()).filter(s => s);
fetch('/api/settings/excluded-stocks', {
method: 'PUT',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({ codes })
}).then(r => {
if (!r.ok) throw new Error('保存失败');
excludedCodesCache = codes;
settingsMsg.textContent = '保存成功';
settingsMsg.style.display = 'block';
}).catch(e => {
settingsErrorMsg.textContent = e.message;
settingsErrorMsg.style.display = 'block';
});
});
// 格式化数字
function formatNumber(num) {
return parseFloat(num).toLocaleString('zh-CN', {
minimumFractionDigits: 2,
maximumFractionDigits: 2
});
}
function sanitizeList(list) {
if (!Array.isArray(list)) return [];
return list.map(sanitizeObject);
}
function sanitizeObject(obj) {
if (!obj || typeof obj !== 'object') return obj;
const res = {};
Object.keys(obj).forEach(k => {
if (k === '__proto__' || k === 'prototype' || k === 'constructor') return;
let v = obj[k];
if (typeof v === 'string') v = v.slice(0, 10000);
else if (v && typeof v === 'object') v = sanitizeObject(v);
res[k] = v;
});
return res;
}
setUploadWeek(0);
setFundWeek(0);
</script>
<script>
function sqlQuote(s){ return String(s||'').replace(/'/g, "''"); }
function sqlVal(v){ if (v === null || typeof v === 'undefined' || (typeof v === 'number' && isNaN(v))) return 'NULL'; if (typeof v === 'number') return String(v); return "'"+sqlQuote(v)+"'"; }
function generateFundSql(start, end, summary, data){
const nowFn = "datetime('now')";
const sumLine = `INSERT OR REPLACE INTO fund_summaries (start_date, end_date, currency, balance, available, reference_value, asset_value, profit, day_profit, created_at, updated_at) VALUES ('${sqlQuote(start)}','${sqlQuote(end)}',${sqlVal(summary && summary['币种'])},${sqlVal(summary && summary['余额'])},${sqlVal(summary && summary['可用'])},${sqlVal(summary && summary['参考市值'])},${sqlVal(summary && summary['资产'])},${sqlVal(summary && summary['盈亏'])},${sqlVal(summary && summary['当日盈亏'])},${nowFn},${nowFn});`;
const jsonStr = JSON.stringify(Array.isArray(data)?data:[]).replace(/'/g, "''");
const dataLine = `INSERT OR REPLACE INTO fund_shares (start_date, end_date, data_json, created_at, updated_at) VALUES ('${sqlQuote(start)}','${sqlQuote(end)}','${jsonStr}',${nowFn},${nowFn});`;
return ['BEGIN TRANSACTION;', sumLine, dataLine, 'COMMIT;'].join('\n');
}
function updateFundSqlPreview(){
try {
const start = fundStart.value; const end = fundEnd.value;
if (!start || !end) { fundSqlPreview.value = ''; return; }
const script = generateFundSql(start, end, fundSummaryCache || {}, fundParsedDataCache || []);
fundSqlPreview.value = script;
} catch (e) {
fundSqlPreview.value = '生成SQL失败: ' + e.message;
}
}
async function syncToWeekly(type, date, data) {
console.log('syncToWeekly invoked:', { type, date });
const resp = await fetch('/api/weekly/ext/rebalance', {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({
key: '93220',
type,
date: date,
startDate: date,
weekStart: date,
...data
})
});
if (!resp.ok) {
const j = await resp.json().catch(() => ({}));
throw new Error(j.error || '同步失败');
}
// Check application level success if status is 200
const j = await resp.json().catch(() => ({}));
if (j && j.ok === false) throw new Error(j.error || '同步返回错误');
console.log('syncToWeekly success:', j);
}
</script>
</body>
</html>