<table align="center" border="0" cellpadding="0" cellspacing="0" class="tableForm" style="table-layout: fixed;">
<colgroup>
<col width="80" />
<col /><!--hide4phone.start-->
<col width="80" />
<col width="380" /><!--hide4phone.end-->
</colgroup>
<tbody>
<tr>
<td style="text-align: right;"> <span style="color: rgb(255, 0, 0);">*</span>Subject:</td>
<td dbf.type="required" id="dbf.subject"> </td>
<!--show4phone.start></tr><tr><show4phone.end-->
<td style="text-align: right;"> Status:</td>
<td><span id="mapping.dbf.procXSource"> </span> Responsor: <span id="mapping.dbf.responsorSource"> </span> Participants: <span id="mapping.dbf.participantsSource"> </span></td>
</tr>
</tbody>
</table>
<div> </div>
<div style="text-align: center;">
<h1><img src="../common/logo.png" /> [Form Name]</h1>
</div>
<div>[Design form here, based on the template below, or customized by yourself after the template removed]</div>
<table align="center" border="0" cellpadding="0" cellspacing="0" class="tableListBorder" style="table-layout: fixed;">
<colgroup>
<col width="130" />
<col /><!--hide4phone.start-->
<col width="130" />
<col width="330" /><!--hide4phone.end-->
</colgroup>
<tbody>
<tr>
<td colspan="4" colspan4phone="2" dbf.source="" dbf.type="" style="background-color: lightyellow;"> <strong>[Brief Information]</strong></td>
</tr>
<tr>
<td class="fieldLabel" style="text-align: center; width: 147px;"><span style="color: red;">*</span> username</td>
<td id="username" style="width: 210px;"> </td>
<!--show4phone.start-->
</tr>
<tr><!--show4phone.end-->
<td class="fieldLabel" style="text-align: center; width: 63px;"><span style="color: red;">*</span> id</td>
<td id="ID" style="width: 254px;"> </td>
</tr>
<tr>
<td class="fieldLabel" style="text-align: center;">项目名称</td>
<td id="项目名称" style="width: 210px;"> </td>
<td class="fieldLabel" style="text-align: center;">装置名称</td>
<td id="装置名称" style="width: 254px;"> </td>
</tr>
<tr>
<td class="fieldLabel" style="text-align: center;">Customer Name</td>
<td id="CustomerName" style="width: 210px;"> </td>
<td class="fieldLabel" style="text-align: center;">E-NO/序列号</td>
<td id="ENO号" style="width: 254px;"> </td>
</tr>
<tr>
<td class="fieldLabel" style="text-align: center;">产品描述</td>
<td id="产品描述" style="width: 210px;"> </td>
<td class="fieldLabel" style="text-align: center;">Remark</td>
<td id="Remark" style="width: 254px;"> </td>
</tr>
<tr>
<td class="fieldLabel" style="text-align: center;">Subject</td>
<td id="subjectid" style="width: 210px;"> </td>
<td class="fieldLabel" dbf.source="" dbf.type="" id="" style="text-align: center;"> </td>
<td dbf.source="" dbf.type="" id="" style="width: 254px;"> </td>
</tr>
<tr>
<td colspan="4" colspan4phone="2" dbf.source="" dbf.type="" style="background-color: lightyellow;"><strong> </strong><strong>[Detailed Information]</strong></td>
</tr>
</tbody>
</table>
<div style="text-align: center; margin: 20px 0;"> <input id="idslist" name="idslist" type="hidden" /> <input id="sql内容" name="sql内容" type="hidden" /> <strong> <input id="check" name="check" onclick="clickData()" type="button" value="check记录" /> </strong> <strong> <input id="check2" name="check2" onclick="downloadCSV()" type="button" value="下载结果" /> </strong></div>
<!-- ==================== 移动端表格占位(暂不使用)==================== --><!-- ==================== 查询结果表格容器 ==================== -->
<div id="resultTableContainer" style="margin-top: 20px; padding: 0 10px; min-height: 20px;"><!-- 动态表格将插入到这里 --></div>
<div class="slide4phone2" id="reptable" style="display:none;">
<table align="center" border="0" cellpadding="0" cellspacing="0" class="tableListBorder2" style="width: 1300px;">
</table>
</div>
<!-- ==================== 核心脚本:查询与渲染 ==================== --><script language="javascript">
function clickData() {
if (window.__isQuerying) {
console.warn("请勿频繁点击!");
return;
}
window.__isQuerying = true;
try {
// 获取所有输入框的值
var projectName = $("项目名称")?.value ? $("项目名称").value().trim() : "";
var deviceName = $("装置名称")?.value ? $("装置名称").value().trim() : "";
var customerName = $("CustomerName")?.value ? $("CustomerName").value().trim() : "";
var enonumber = $("ENO号")?.value ? $("ENO号").value().trim() : ""; // 关键字段
var remark = $("Remark")?.value ? $("Remark").value().trim() : "";
var productDesc = $("产品描述")?.value ? $("产品描述").value().trim() : "";
var formsubject = $("subjectid")?.value ? $("subjectid").value().trim() : "";
/**
* 安全转义 SQL 单引号
*/
function escapeSql(str) {
if (typeof str !== 'string' && typeof str !== 'number') return '';
return String(str).replace(/'/g, "''");
}
// 构建模糊匹配模式:必须加 %%
var pName = '%' + escapeSql(projectName) + '%';
var dName = '%' + escapeSql(deviceName) + '%';
var cName = '%' + escapeSql(customerName) + '%';
var enoNum = '%' + escapeSql(enonumber) + '%'; // ✅ 必须加 %%
var rmk = '%' + escapeSql(remark) + '%';
var prodDesc = '%' + escapeSql(productDesc) + '%';
var subj = '%' + escapeSql(formsubject) + '%';
var subj2 = '%-' + escapeSql(formsubject) + '%';
// ✅ 核心修复点:清理 ENO序列号 中的换行、空格、制表符
const cleanedEnoField = `
REPLACE(
REPLACE(
REPLACE(
ISNULL([ENO序列号], ''),
CHAR(10), ''),
CHAR(13), ''),
' ', '')
`;
// 主 SQL 查询语句
var sql = `SELECT TOP 100
Subject, Status, 申请人, 申请日期, 产品类别, 其他, type, 内勤, 系统, customer,
ProjectName, DeviceName, 新产品, [ENO序列号], 目标价, 数量, 产地, 含税报价, HandlerRemark, Remark, 描述
FROM X_BPM_DWH_819_InquiryLines
WHERE
ISNULL(ProjectName, '') LIKE '${pName}' AND
ISNULL(DeviceName, '') LIKE '${dName}' AND
ISNULL(customer, '') LIKE '${cName}' AND
${cleanedEnoField} LIKE '${enoNum}' AND
ISNULL(Remark, '') LIKE '${rmk}' AND
ISNULL(描述, '') LIKE '${prodDesc}' AND
(
ISNULL(Subject, '') LIKE '${subj}' OR
ISNULL(Subject, '') LIKE '${subj2}'
)
ORDER BY 申请日期 DESC
`;
alert( sql); // ⚠️ 打开浏览器控制台查看是否真包含 %888888%
// 执行查询
var rawResult = service("common.js", "getDbsRecords", sql, "array");
let arr = parseServiceResult(rawResult);
renderTable(arr);
} catch (e) {
console.error("【查询失败】", e);
alert("查询出错:" + (e.message || e));
document.getElementById("resultTableContainer").innerHTML = `
<div style="color:red;text-align:center;padding:10px;">
❌ ${e.message || e}
</div>
`;
} finally {
window.__isQuerying = false;
}
}
/**
* 安全解析 service 返回的结果
* 支持:合法JSON / 非法JSON / JS数组字面量 / 字符串化数组 / null等
*/
function parseServiceResult(rawResult) {
console.log("【原始返回类型】", typeof rawResult);
console.log("【原始内容】", rawResult);
// 情况1:已经是数组
if (Array.isArray(rawResult)) {
console.log("✅ 已识别为原生数组");
return rawResult;
}
// 情况2:null/undefined/false
if (rawResult == null || rawResult === 'null' || rawResult === 'undefined') {
console.warn("⚠️ 返回为空值");
return [];
}
// 转为字符串处理
let str = String(rawResult).trim();
if (!str) {
console.warn("⚠️ 返回空字符串");
return [];
}
// 情况3:可能是HTML错误页(常见于服务异常)
if (str.startsWith('<') || str.includes('<html') || str.toLowerCase().includes('error') || str.includes('Exception')) {
console.error("🔴 返回了HTML错误页!", str.substring(0, 200));
throw new Error('服务端错误,请检查SQL语法或联系管理员');
}
/**
* 数据清洗函数:修复各种非法字符
*/
function clean(str) {
return str
.replace(/\\\\/g, '\\') // 修复多个反斜杠
.replace(/"/g, '\\"') // 先转义双引号
.replace(/'/g, '"') // 单引号 → 双引号
.replace(/\\"/g, '"') // 还原正常引号
.replace(/\\n/g, '\\n') // 显式换行
.replace(/\\r/g, '\\r')
.replace(/\\t/g, '\\t')
.replace(/\n/g, '\\n')
.replace(/\r/g, '\\r')
.replace(/\t/g, '\\t')
.replace(/[\x00-\x08\x0B\x0C\x0E-\x1F\x7F-\x9F]/g, '') // 移除控制字符
.replace(/^"(\[.*\])"$/, '$1') // 去掉 "[...]" 外层引号
.replace(/^"(\{.*\})"$/, '$1')
.replace(/,\s*}/g, '}') // 去除对象尾部逗号
.replace(/,\s*]/g, ']'); // 去除数组尾部逗号
}
// 尝试多种解析方式
const parsers = [
// 方式1:直接JSON.parse
() => JSON.parse(str),
// 方式2:清洗后再解析
() => JSON.parse(clean(str)),
// 方式3:作为JS表达式执行(仅限可信环境)
() => new Function(`return (${str});`)(),
// 方式4:进一步清理后尝试
() => JSON.parse(clean(str).replace(/""/g, '"').replace(/"\s+:/g, '":').replace(/:\s+"/g, ':"'))
];
for (let i = 0; i < parsers.length; i++) {
try {
const result = parsers[i]();
if (Array.isArray(result)) {
console.log(`✅ 第 ${i+1} 种方式解析成功`);
return result;
}
// 兼容 { data: [...] } 结构
if (result && Array.isArray(result.data)) return result.data;
if (result && Array.isArray(result.rows)) return result.rows;
} catch (e) {
console.warn(`📌 方式 ${i+1} 解析失败`, e.message);
}
}
// 情况5:手动解析简单二维数组字符串 [[...],[...]]
if (/^\[\s*$$[^$$]*$\s*(,\s*$$[^$$]*$)*\s*\]$/.test(str)) {
try {
console.log("🔍 检测到疑似二维数组字符串,尝试手动解析");
const rows = str.match(/$[^$$]*$/g) || [];
return rows.map(row => {
return row.replace(/^\[/, '').replace(/\]$/, '')
.split(',')
.map(field => field.trim().replace(/^"(.*)"$/, '$1').replace(/^'(.*)'$/, '$1'));
}).filter(r => r.length > 0);
} catch (e) {
console.error("手动解析失败", e);
}
}
// 所有方式都失败
console.error("🔴 所有解析方式均失败", { raw: rawResult, cleaned: clean(str) });
throw new Error('返回数据格式异常,无法解析。请检查SQL是否正确或联系开发人员。');
}
/**
* 渲染查询结果为表格
*/
function renderTable(arr) {
var container = document.getElementById("resultTableContainer");
if (!container) return;
// 清空容器
container.innerHTML = '';
if (!arr || arr.length === 0) {
container.innerHTML = `
<p style="text-align:center;color:#999;font-style:italic;padding:20px;">
📭 未找到匹配的数据。
</p>
`;
return;
}
// 创建表格
var table = document.createElement('table');
table.className = 'tableListBorder';
table.style.width = '100%';
table.style.tableLayout = 'fixed';
table.setAttribute('border', '1');
table.setAttribute('cellpadding', '6');
table.setAttribute('cellspacing', '0');
table.style.fontFamily = 'Arial, sans-serif';
table.style.fontSize = '13px';
// 表头
var thead = document.createElement('thead');
var headerRow = document.createElement('tr');
var headers = [
"Subject", "Status", "申请人", "申请日期", "产品类别", "其他", "类型",
"内勤", "系统", "客户", "项目名称", "装置名称", "新产品",
"E-NO/序列号", "目标价", "数量", "产地", "含税报价", "处理备注", "备注", "描述"
];
headers.forEach(function(text) {
var th = document.createElement('th');
th.style.backgroundColor = '#f5f5f5';
th.style.textAlign = 'center';
th.style.padding = '10px';
th.style.whiteSpace = 'nowrap';
th.style.fontWeight = 'bold';
th.textContent = text;
headerRow.appendChild(th);
});
thead.appendChild(headerRow);
table.appendChild(thead);
// 表体
var tbody = document.createElement('tbody');
arr.forEach(function(row, index) {
if (!row || !Array.isArray(row) && typeof row !== 'object') {
console.warn(`第 ${index} 行数据异常`, row);
return;
}
var tr = document.createElement('tr');
Object.values(row).forEach(function(cell) {
var td = document.createElement('td');
td.style.padding = '8px';
td.style.borderTop = '1px solid #eee';
td.style.wordBreak = 'break-word';
td.style.maxWidth = '200px';
td.textContent = cell == null ? '' : String(cell).trim();
tr.appendChild(td);
});
tbody.appendChild(tr);
});
table.appendChild(tbody);
container.appendChild(table);
console.log(`✅ 成功渲染 ${arr.length} 行数据`);
}
/**
* 将当前查询结果导出为 CSV 文件
*/
function downloadCSV() {
if (!currentQueryResult || currentQueryResult.length === 0) {
alert("没有可下载的数据,请先执行查询。");
return;
}
// 定义表头(中文标题)
const headers = [
"Subject", "Status", "申请人", "申请日期", "产品类别", "其他", "类型",
"内勤", "系统", "客户", "项目名称", "装置名称", "新产品",
"E-NO/序列号", "目标价", "数量", "产地", "含税报价", "处理备注", "备注", "描述"
];
// 映射原始字段名(必须与 SQL 查询顺序一致)
const fieldKeys = [
"Subject", "Status", "申请人", "申请日期", "产品类别", "其他", "type",
"内勤", "系统", "customer", "ProjectName", "DeviceName", "新产品",
"ENO序列号", "目标价", "数量", "产地", "含税报价", "HandlerRemark", "Remark", "描述"
];
// 构建 CSV 内容
const BOM = '\uFEFF'; // 支持中文 Excel 打开不乱码
let csv = [];
// 添加表头
csv.push(headers.join(','));
// 添加每一行数据
currentQueryResult.forEach(row => {
const values = fieldKeys.map(key => {
let val = '';
if (Array.isArray(row)) {
// 如果是数组格式,按索引取值
val = row[fieldKeys.indexOf(key)] || '';
} else {
// 如果是对象格式,按 key 取值
val = row[key] || '';
}
// 转字符串 + 处理双引号和逗号
val = String(val || '').trim();
// CSV 中双引号要转义成两个双引号,并整体用双引号包裹
if (val.includes(',') || val.includes('"') || val.includes('\n')) {
val = `"${val.replace(/"/g, '""')}"`;
}
return val;
});
csv.push(values.join(','));
});
// 生成文件内容
const csvContent = BOM + csv.join('\n');
// 创建 Blob 对象
const blob = new Blob([csvContent], { type: 'text/csv;charset=utf-8;' });
// 创建下载链接
const link = document.createElement('a');
const url = URL.createObjectURL(blob);
const now = new Date().toISOString().slice(0, 19).replace(/:/g, '-'); // 当前时间作为文件名
link.setAttribute('href', url);
link.setAttribute('download', `查询结果_${now}.csv`);
link.style.visibility = 'hidden';
// 添加到 DOM 并触发点击
document.body.appendChild(link);
link.click();
// 清理内存
document.body.removeChild(link);
URL.revokeObjectURL(url);
console.log(`✅ 已下载 ${currentQueryResult.length} 行数据为 CSV`);
}
</script>
<div id="resultTableContainer" style="margin-top: 20px; overflow-x: auto;"> </div>