import BaseService from "../baseService";
|
import * as XLSX from "xlsx";
|
import * as path from "path";
|
import { SysImportInfo } from "../../entity/sys/import/sysImportInfo";
|
import { SysImportColumnInfo } from "../../entity/sys/import/sysImportColumnInfo";
|
|
/**
|
* 通用导入Service
|
*/
|
export default class ImportCommonService extends BaseService {
|
//#region 变量
|
public ERROR_I: number = 0; //错误号
|
private m_ValidateDataTable = []; //验证规则表
|
private m_ExcelDataTable = []; //Excel数据表
|
//#endregion
|
|
//#region 枚举
|
/// <summary>
|
/// 导入状态
|
/// </summary>
|
public eState = {
|
fail: -1, //失败
|
sleep: 0, //休眠
|
start: 1, //开始线程操作
|
checkColumnName: 101, //校验字段是否合法
|
checkData: 103, //校验数据
|
importData: 104 //开始导入
|
};
|
|
/// <summary>
|
/// 导入类别
|
/// </summary>
|
public eImportType = {
|
null: 0, //未知
|
updateAndImport: 1, //更新&递增
|
clearAndImport: 2, //全清全导
|
onlyImport: 3, //只新增，但如果表中已存在也不新增
|
onlyUpdate: 4, //只更新，但如果表中不存在也不新增
|
allImport: 5 //不管存不存在都进行导入
|
};
|
|
/// <summary>
|
/// 字段导入模式
|
/// </summary>
|
public eFieldImportMode = {
|
none: 0, //无操作
|
updateAndInsert: 1, //更新&插入
|
displayNotUpdateAndInsert: 2, //显示字段，但不更新和插入
|
onlyInsert: 3, //只插入，但不更新
|
onlyUpdate: 4 //只更新，但插入
|
};
|
//#endregion
|
|
//#region 属性
|
/// <summary>
|
/// Excel当前正在分析的行号
|
/// </summary>
|
public currentRowNumber: number = 0;
|
|
/// <summary>
|
/// 线程状态，0-未开始，1-正在运行，2-任务结束并且成功执行所有操作
|
/// </summary>
|
public state = 0;
|
|
/// <summary>
|
/// 菜单ID
|
/// </summary>
|
public menu_Id = 0;
|
|
/// <summary>
|
/// 导入主表信息
|
/// </summary>
|
public importInfo: SysImportInfo;
|
|
/// <summary>
|
/// 跳过字段，不进行更新
|
/// </summary>
|
public jumpFieldCnNameUpdate;
|
|
/// <summary>
|
/// 跳过字段，不进行插入
|
/// </summary>
|
public jumpFieldCnNameInsert;
|
|
/// <summary>
|
/// 删除行数
|
/// </summary>
|
public deleteCount: number = 0;
|
|
/// <summary>
|
/// 自动编号
|
/// </summary>
|
public htCodeRegular: object = {};
|
//#endregion
|
|
private async setMsg(msg: string, color: string = "black") {
|
let redis = this.ctx.app.redis.clients.get("common"); // 将消息放入redis缓存
|
if (color) {
|
msg = `<font color="${color}">${msg}</font>`;
|
}
|
await redis.rpush(this.body.uploadKey, msg);
|
redis.expire(this.body.uploadKey, 5 * 60);
|
}
|
|
//#region start
|
public async start() {
|
try {
|
await this.initialize();
|
|
if (!this.m_ValidateDataTable.length) {
|
await this.setMsg("没有可导入的数据");
|
this.state = this.eState.fail;
|
return this.state;
|
}
|
|
this.state = this.eState.checkColumnName;
|
await this.setMsg("校验字段是否合法...");
|
//校验字段是否合法
|
let _checkCol = await this.checkColumnName();
|
if (!_checkCol) {
|
this.state = this.eState.fail;
|
return this.state;
|
}
|
|
//校验数据是否合法
|
this.state = this.eState.checkData;
|
await this.setMsg("正在校验数据是否合法...");
|
let _checkData = await this.checkData();
|
if (!_checkData) {
|
this.state = this.eState.fail;
|
return this.state;
|
}
|
|
//导入数据
|
this.state = this.eState.importData;
|
await this.setMsg("开始导入数据...");
|
let isImportData = false;
|
switch (this.importInfo.importType) {
|
case this.eImportType.updateAndImport:
|
case this.eImportType.onlyImport:
|
case this.eImportType.onlyUpdate:
|
isImportData = await this.updateAndImport();
|
break;
|
case this.eImportType.clearAndImport:
|
isImportData = await this.clearAndImport();
|
break;
|
case this.eImportType.allImport:
|
isImportData = await this.allImport();
|
break;
|
default:
|
break;
|
}
|
|
if (!isImportData && this.currentRowNumber <= 0) {
|
this.setMsg("<font color=red>没有导入任何数据！</font>");
|
}
|
this.state = this.eState.sleep;
|
return this.state;
|
} catch (e) {
|
this.setMsg("出现异常：" + e.message, "red");
|
this.info.result = false;
|
}
|
}
|
//#endregion
|
|
//#region this.initialize
|
private async initialize() {
|
this.state = this.eState.sleep;
|
this.jumpFieldCnNameInsert = ",";
|
this.jumpFieldCnNameUpdate = ",";
|
this.htCodeRegular = {};
|
|
await this.reset();
|
this.ERROR_I = 0;
|
this.currentRowNumber = 0;
|
let importInfo_Id = this.body.importInfo_Id;
|
let fileUrl = this.body.fileUrl;
|
let rootPath = path.resolve(); // 获得根目录
|
let filePath = rootPath + path.sep + fileUrl.replace(/\//gi, path.sep); // 上传文件路径
|
|
this.importInfo = await this.dbRead.findOne(SysImportInfo, importInfo_Id);
|
var workbook = XLSX.readFile(filePath); //整个 excel 文档
|
var sheetNames = workbook.SheetNames; //获取所有工作薄名
|
var sheet1 = workbook.Sheets[sheetNames[0]]; //根据工作薄名获取工作薄
|
let dataList = XLSX.utils.sheet_to_json(sheet1); // 获得当前sheet表单数据转为json格式
|
|
this.m_ValidateDataTable = await this.getValidateDataTable(); //获得验证规则表
|
this.m_ExcelDataTable = dataList;
|
}
|
//#endregion
|
|
//#region CheckColumnName，校验字段是否合法
|
public async checkColumnName() {
|
let isChecked = true; //是否通过验证
|
let columns = Object.keys(this.m_ExcelDataTable[0]);
|
for (let col of columns) {
|
let columnCnName = col; //列的中文名
|
let rows = this.m_ValidateDataTable.filter(item => item.cnName === columnCnName);
|
if (rows.length <= 0) {
|
this.ERROR_I++;
|
await this.setMsg(this.ERROR_I + "、字段【" + columnCnName + "】不存在", "red");
|
if (isChecked) isChecked = false;
|
}
|
}
|
if (isChecked) {
|
//添加带入字段，带入验证、SQL带入在数据库中不存在，所以需要添加
|
// let where = ["带入验证", "SQL带入", "计算", "字典带入", "字典多值带入", "变量替换"];
|
let rowsRegular = this.m_ValidateDataTable; //.filter(item => where.some(s => s === item.validate));
|
for (let row of rowsRegular) {
|
let cnName = row["cnName"]; //字段中文名
|
if (columns.indexOf(cnName) >= 0) {
|
//this.ERROR_I++;
|
//this.setMsg(this.ERROR_I + "、校验字段发现错误, 字段【" + cnName + "】 在Excel中存在, 请修改配置表!", "red");
|
continue;
|
}
|
this.m_ExcelDataTable[0][cnName] = null;
|
}
|
|
for (let row of this.m_ValidateDataTable) {
|
let isImport = row.importMode;
|
let cnName = row.cnName;
|
if (isImport == this.eFieldImportMode.displayNotUpdateAndInsert) {
|
//更新、新增记录时跳过
|
this.jumpFieldCnNameInsert += cnName + ",";
|
this.jumpFieldCnNameUpdate += cnName + ",";
|
} else if (isImport == this.eFieldImportMode.onlyUpdate) {
|
//只更新
|
this.jumpFieldCnNameInsert += cnName + ",";
|
} else if (isImport == this.eFieldImportMode.onlyInsert) {
|
//只插入
|
this.jumpFieldCnNameUpdate += cnName + ",";
|
}
|
}
|
}
|
if (!isChecked) {
|
//错误标题
|
this.setMsg("校验字段发现错误，共" + this.ERROR_I + "错误，请将上面出现红色的提示修正！", "red");
|
}
|
return isChecked;
|
}
|
//#endregion
|
|
//#region checkData, 校验数据是否合法
|
public async checkData() {
|
let isChecked = true;
|
for (let i = 0; i < this.m_ExcelDataTable.length; i++) {
|
for (let j = 0; j < this.m_ValidateDataTable.length; j++) {
|
let columnCnName = this.m_ValidateDataTable[j]["cnName"]; //字段中文名
|
await this.validateData(this.m_ExcelDataTable[i], this.m_ValidateDataTable[j], columnCnName, i);
|
}
|
if (this.ERROR_I > 20) {
|
this.setMsg("系统检测到一个类似的错误20个，已经暂停错误检测。", "red");
|
break;
|
}
|
this.setMsg("数据校验到第" + (i + 2) + "行");
|
}
|
if (this.ERROR_I >= 1) {
|
let err = "共发现" + this.ERROR_I + "错误，请查看上面日志错误记录，然后解决！ ";
|
this.setMsg(err, "red");
|
isChecked = false;
|
}
|
|
return isChecked;
|
}
|
//#endregion
|
|
//#region validateData 基本检验数据
|
/// <summary>
|
/// 基本检验数据, return true 表示验证未通过
|
/// </summary>
|
private async validateData(rowExcel, validateRow, excelColumnCnName, i) {
|
let userInfo = await this.userInfo;
|
let columns = Object.keys(this.m_ExcelDataTable[0]);
|
//#region 验证参数
|
let rowsRegular = this.m_ValidateDataTable.filter(item => item.cnName === excelColumnCnName);
|
if (rowsRegular == null || rowsRegular.length <= 0) return false; //是否需要验证
|
let validate = rowsRegular[0]["validate"]; //验证规则
|
if (!validate) return false;
|
|
let columnName = rowsRegular[0]["columnName"]; //字段名称
|
let cnName = rowsRegular[0]["cnName"]; //字段中文名
|
let valExpression = rowsRegular[0]["valExpression"]; //验证表达式
|
let isMust = rowsRegular[0]["isMust"]; //是否必填
|
let valDescription = rowsRegular[0]["valDescription"]; //错误描述
|
let excelCurrValue = rowExcel[excelColumnCnName]; //当前单元格的值
|
|
if (valExpression) valExpression = valExpression.replace("%2C", ",");
|
if (valDescription) valDescription = valDescription.replace("%2C", ",");
|
if (valDescription)
|
valDescription = valDescription
|
.replace("{cnName}", cnName)
|
.replace("{tableName}", this.importInfo.tableName)
|
.replace("{currentValue}", excelCurrValue);
|
|
let total = 0; //求和
|
let isBlank = false; //是否为空值
|
//#endregion
|
|
let sql = "";
|
let isError = false;
|
let ValArray: Array<string> = [];
|
switch (validate) {
|
case "正则验证":
|
//#region
|
isError = await this.regularValidate(excelCurrValue, valExpression);
|
break;
|
//#endregion
|
case "字典验证":
|
//#region
|
if (!excelCurrValue) {
|
if (isMust == 1) {
|
isError = true;
|
valDescription = "【" + excelColumnCnName + "】不能为空!";
|
}
|
} else {
|
isError = await this.dictionaryValidate(valExpression, columnName, excelCurrValue);
|
if (valDescription) valDescription = valDescription.replace("{fieldCnName}", excelColumnCnName).replace("{fieldValue}", excelCurrValue);
|
}
|
break;
|
//#endregion
|
case "字典更新":
|
case "字典带入":
|
//#region
|
isBlank = false; //是否为空值
|
|
isBlank = await this.dictionaryDriverIn(valExpression, excelColumnCnName, rowExcel);
|
let valexpArr = valExpression.split(",");
|
if (valDescription) {
|
valDescription = valDescription.replace("{tableName}", valexpArr[0]);
|
valDescription = valDescription.replace("{currentField}", valexpArr[1]);
|
valDescription = valDescription.replace("{where}", valexpArr[2]);
|
}
|
if (isMust == 1 && isBlank) {
|
isError = true;
|
valDescription = "【" + excelColumnCnName + "】不能为空!";
|
}
|
break;
|
//#endregion
|
case "字典多值带入":
|
//#region
|
isBlank = false; //是否为空值
|
|
isBlank = await this.dictionaryDriverInMulti(valExpression, excelColumnCnName, rowExcel);
|
valexpArr = valExpression.split(",");
|
if (valDescription) {
|
valDescription = valDescription.replace("{tableName}", valexpArr[0]);
|
valDescription = valDescription.replace("{currentField}", valexpArr[1]);
|
valDescription = valDescription.replace("{where}", valexpArr[2]);
|
}
|
if (isMust == 1 && isBlank) {
|
isError = true;
|
valDescription = "【" + excelColumnCnName + "】不能为空!";
|
}
|
break;
|
//#endregion
|
case "带入验证":
|
//#region
|
//ValExpression，格式：表名称,根据这个字段带入，例如：Base_RegionSub,RegionSub_Name
|
ValArray = valExpression.split(",");
|
let driveTableName = ValArray[0]; //从中获得数据的基础表名
|
let driveColumnName = ValArray[1]; //从中获得数据的字段名
|
let driveParentColumnName = ValArray[2]; //根据这个字段带入的条件字段名
|
let DriveParentColumnCnName = ValArray[3]; //根据这个字段带入的条件字段中文名
|
let parentValue = rowExcel[DriveParentColumnCnName]; //根据这个字段带入条件字段名的值
|
isBlank = false; //是否为空值
|
|
if (!parentValue) {
|
if (isMust == 1) {
|
isError = true;
|
valDescription = "Can not be empty!";
|
}
|
} else {
|
let result = await this.driveInValidate(driveTableName, driveColumnName, driveParentColumnName, parentValue, rowExcel, excelColumnCnName);
|
isError = result.isError;
|
if (valDescription) {
|
valDescription = valDescription
|
.replace("{CurrentValue}", parentValue)
|
.replace("#DriveParentColumnCnName#", DriveParentColumnCnName)
|
.replace("#cnName#", cnName);
|
}
|
if (isBlank) valDescription += "，带入字段为空！";
|
}
|
|
break;
|
//#endregion
|
case "上下文验证":
|
//#region
|
ValArray = valExpression.split(",");
|
let baseTableName = ValArray[0]; //从中获得数据的基础表名
|
let firstColumnCnName = ValArray[1]; //上下文条件字段
|
let secondColumnCnName = ValArray[2]; //上下文条件字段
|
let firstColumnName = ValArray[3]; //上下文条件字段
|
let secondColumnName = ValArray[4]; //上下文条件字段
|
let firstValue = rowExcel[firstColumnCnName]; //上下文条件字段的值
|
let secondValue = rowExcel[secondColumnCnName]; //上下文条件字段的值
|
|
isError = await this.contextValidate(baseTableName, firstColumnName, secondColumnName, firstValue, secondValue);
|
if (!secondValue) {
|
valDescription = secondColumnName + "字段Can not be empty!";
|
} else {
|
if (valDescription) {
|
valDescription = valDescription
|
.replace("#firstColumnName#", firstColumnName)
|
.replace("#secondColumnName#", secondColumnName)
|
.replace("#firstValue#", firstValue)
|
.replace("#secondValue#", secondValue);
|
}
|
}
|
break;
|
//#endregion
|
case "计算自我":
|
case "计算":
|
//#region
|
if (valDescription) valDescription = valDescription.replace("{express}", valExpression);
|
for (i = 0; i < columns.length; i++) {
|
let cnName = columns[i];
|
if (valExpression) valExpression = valExpression.replace("(" + cnName + ")", "(" + rowExcel[cnName] + ")");
|
}
|
if (valDescription) valDescription = valDescription.replace("{result}", valExpression);
|
|
isError = await this.checkExpress(valExpression);
|
if (isError) {
|
total = 0;
|
valDescription = "字段【" + cnName + "】计算有误, 表达式为：" + valExpression + "！";
|
} else {
|
try {
|
total = eval(valExpression);
|
} catch {
|
total = 0;
|
isError = true;
|
}
|
}
|
|
rowExcel[excelColumnCnName] = total; //将带出的值赋于当前Excel单元格
|
break;
|
//#endregion
|
case "SQL更新":
|
case "SQL带入":
|
//#region
|
for (
|
let index = 0;
|
index < columns.length;
|
index++ //替换需要查找
|
) {
|
let columnCnName = columns[index]; //字段中文名
|
let fieldValue = rowExcel[columnCnName];
|
if (valExpression) {
|
valExpression = valExpression.replace("{" + columnCnName + "}", fieldValue).replace("{cnName}", rowExcel[columnCnName]);
|
}
|
if (valDescription) {
|
valDescription = valDescription.replace("{" + columnCnName + "}", fieldValue).replace("{cnName}", rowExcel[columnCnName]);
|
}
|
}
|
if (valExpression) {
|
valExpression = valExpression.replace("{userProduct_Id}", userInfo.userProduct_Id);
|
}
|
|
//如果Sql带入值为空，那么使用ReplaceValue变量值进行替换
|
let isNullable = true; //允许为空
|
if (isMust == 1) isNullable = false;
|
isError = await this.SQLValidate(valExpression, rowExcel, validateRow, excelColumnCnName, isNullable);
|
if (valDescription) {
|
valDescription = valDescription.replace("{fieldCnName}", cnName);
|
}
|
break;
|
//#endregion
|
case "SQL验证":
|
//#region
|
for (
|
let index = 0;
|
index < columns.length;
|
index++ //替换需要查找
|
) {
|
let columnCnName = columns[index]; //字段中文名
|
let rows = this.m_ValidateDataTable.filter(item => item.cnName === columnCnName);
|
|
let fieldValue = rowExcel[columnCnName];
|
if (typeof fieldValue === "string") {
|
fieldValue = fieldValue.replace("'", "''");
|
}
|
if (valDescription) {
|
valExpression = valExpression.replace("{" + columnCnName + "}", fieldValue);
|
valExpression = valExpression.replace("{" + rows[0]["columnName"] + "}", fieldValue);
|
}
|
}
|
|
if (valExpression) {
|
valExpression = valExpression.replace("{userProduct_Id}", userInfo.userProduct_Id);
|
}
|
if (!!excelCurrValue && !(await this.isExist(valExpression)) && valDescription) {
|
valDescription = valDescription.replace("{fieldCnName}", excelColumnCnName);
|
valDescription = valDescription.replace("{fieldValue}", excelCurrValue);
|
isError = true;
|
}
|
break;
|
//#endregion
|
case "验证唯一性":
|
//#region
|
//#region 获得条件
|
let existTableName = this.importInfo.tableName; //判断数据是否存在的表
|
let importPrimarykeyEn = this.importInfo.importPrimarykeyEn; //主键名称
|
// let importPrimarykeyCn = this.importInfo.importPrimarykeyCn; //主键Excel中的名称
|
let arrayKeyID = importPrimarykeyEn.split(","); //多个关键词 英文
|
// let arrayCnKeyID = importPrimarykeyCn.split(","); //多个关键词 中文
|
let where = "";
|
// for (let k = 0; k < arrayCnKeyID.length; k++) {
|
// if (k > 0) where += " And ";
|
// let keyValue = rowExcel[arrayCnKeyID[k]] || 0;
|
// where += arrayKeyID[k] + "='" + keyValue + "'";
|
// }
|
//#endregion
|
where += " userProduct_Id=" + userInfo.userProduct_Id;
|
|
let tsql = "Select " + arrayKeyID[0] + " from " + existTableName + " where " + where;
|
let isRowExist = await this.isExist(tsql);
|
|
if (valExpression) {
|
valExpression = valExpression.replace("{userProduct_Id}", userInfo.userProduct_Id);
|
}
|
|
for (
|
let index = 0;
|
index < columns.length;
|
index++ //替换需要查找
|
) {
|
let columnCnName = columns[index]; //字段中文名
|
let fieldValue = rowExcel[columnCnName];
|
if (valExpression) {
|
valExpression = valExpression.replace("{" + columnCnName + "}", fieldValue);
|
valDescription = valDescription.replace("{" + columnCnName + "}", rowExcel[columnCnName]);
|
}
|
}
|
if (!excelCurrValue) {
|
valDescription = excelColumnCnName + "不能为空！";
|
isError = true;
|
} else if (isRowExist) {
|
where += " And " + columnName + "='" + excelCurrValue + "'";
|
sql = "Select " + columnName + " from " + existTableName + " where " + where;
|
let myValue = await this.dbRead.query(sql);
|
if (!myValue.length) {
|
if (this.isExist(valExpression) && valDescription) {
|
valDescription = valDescription.replace("{tableName}", this.importInfo.tableName).replace("{CurrentValue}", excelCurrValue);
|
isError = true;
|
}
|
}
|
} else {
|
if ((await this.isExist(valExpression)) && valDescription) {
|
valDescription = valDescription.replace("{tableName}", this.importInfo.tableName).replace("{CurrentValue}", excelCurrValue);
|
isError = true;
|
}
|
}
|
break;
|
//#endregion
|
case "自动编号":
|
//#region
|
let code = "";
|
let oCode = this.htCodeRegular[excelColumnCnName];
|
let menu_Id = valExpression.split(",");
|
let oldCode = rowExcel[excelColumnCnName];
|
if (!oldCode) {
|
//不存在获得自动编号
|
if (oCode != null) {
|
code = await this.ctx.service.common.getCodeRegular(menu_Id);
|
this.htCodeRegular[excelColumnCnName] = code;
|
} else {
|
code = await this.ctx.service.common.getCodeRegular(menu_Id);
|
}
|
if (!code) {
|
isError = true;
|
} else {
|
rowExcel[excelColumnCnName] = code;
|
this.htCodeRegular[excelColumnCnName] = code;
|
}
|
}
|
break;
|
//#endregion
|
case "条形码验证":
|
//#region
|
for (
|
let index = 0;
|
index < columns.length;
|
index++ //替换需要查找
|
) {
|
let columnCnName = columns[index]; //字段中文名
|
let fieldValue = rowExcel[columnCnName];
|
if (valExpression) {
|
valExpression = valExpression.replace("{" + columnCnName + "}", fieldValue);
|
valDescription = valDescription.replace("{" + columnCnName + "}", rowExcel[columnCnName]);
|
}
|
}
|
|
//如果Sql带入值为空，那么使用ReplaceValue变量值进行替换
|
isNullable = true; //允许为空
|
if (isMust == 1) isNullable = false;
|
isError = await this.SQLValidate(valExpression, rowExcel, validateRow, excelColumnCnName, isNullable);
|
if (valDescription) valDescription = valDescription.replace("{fieldCnName}", cnName);
|
|
sql = "Select productCode From Base_ProductInfo where ProductCode<>'" + rowExcel["编号"] + "' And productModel='" + rowExcel["条形码"] + "'";
|
let productCode = await this.dbRead.query(sql);
|
if (!!productCode) {
|
valDescription = "当前物料的条形码" + rowExcel["条形码"] + "已经被物料编号为" + productCode + "占用！";
|
isError = true;
|
}
|
break;
|
//#endregion
|
case "变量替换":
|
//#region
|
if (valExpression) valExpression = valExpression.replace("{", "").replace("}", "");
|
var val = this.body[valExpression];
|
if (!val) {
|
val = userInfo[valExpression];
|
}
|
|
//不能为空
|
if (!val && isMust == 1) {
|
if (valDescription) valDescription = valDescription.replace("{express}", valExpression);
|
isError = true;
|
}
|
|
rowExcel[excelColumnCnName] = val;
|
break;
|
//#endregion
|
default:
|
break;
|
}
|
|
//记录错误
|
if (isError) {
|
this.ERROR_I++;
|
let col = this.getColumnNo(cnName);
|
let err = `${this.ERROR_I}、【行${i + 2}，列${col}"，字段：${cnName}】错误，${valDescription}`;
|
this.setMsg(err, "red");
|
}
|
|
return isError;
|
}
|
|
//正则验证, return true 表示验证未通过
|
private async regularValidate(inputText, regexp) {
|
if (!regexp) return false;
|
|
regexp = "/" + regexp + "/";
|
let rg = <RegExp>eval(regexp);
|
return !rg.test(inputText);
|
}
|
|
//字典验证, return true 表示验证未通过
|
private async dictionaryValidate(valExpression, columnName, excelCurrValue) {
|
let tableName = valExpression;
|
|
//#region 如果ValExpression中存在指定的表名和指定的字段名那么TableName、ColumnName都采用指定的值
|
let expArr = valExpression.split("|");
|
if (expArr.length >= 2) {
|
tableName = expArr[0];
|
columnName = expArr[1];
|
}
|
//#endregion
|
|
let dt = await this.getBaseDataTable(tableName); //获得基础表
|
let excelCurrValueArr = excelCurrValue.split(",");
|
let isError = false;
|
for (let i = 0; i < excelCurrValueArr.length; i++) {
|
let rows = dt.filter(columnName + "='" + excelCurrValueArr[i] + "'");
|
isError = rows == null || rows.length <= 0;
|
|
if (isError) {
|
this.setMsg("校验【" + excelCurrValueArr[i] + "】在基础数据中不存在！", "red");
|
return isError;
|
}
|
}
|
return isError;
|
}
|
|
//字典带入
|
private async dictionaryDriverIn(valExpression, excelColumnCnName, rowExcel) {
|
let columns = Object.keys(this.m_ExcelDataTable[0]);
|
let valExpressionArr = valExpression.split(",");
|
let tableName = valExpressionArr[0]; //字典表名称
|
let columnName = valExpressionArr[1]; //字典字段
|
let where = valExpressionArr[2]; //字典条件
|
let isBlank = true;
|
|
for (
|
let index = 0;
|
index < columns.length;
|
index++ //替换需要查找
|
) {
|
let columnCnName = columns[index]; //字段中文名
|
let fieldValue = rowExcel[columnCnName];
|
where = where ? where.replace("{" + columnCnName + "}", fieldValue) : null;
|
}
|
|
let dt = await this.getBaseDataTable(tableName); //获得基础表
|
let rows = dt.filter(where);
|
|
let isError = rows == null || rows.length <= 0;
|
if (!isError) {
|
let val = rows[0][columnName];
|
isBlank = !val;
|
rowExcel[excelColumnCnName] = val; //将带出的值赋于当前Excel单元格
|
}
|
|
return isBlank;
|
}
|
|
//字典多值带入
|
private async dictionaryDriverInMulti(valExpression, excelColumnCnName, rowExcel) {
|
let valExpressionArr = valExpression.split(",");
|
let tableName = valExpressionArr[0]; //字典表名称
|
let columnName = valExpressionArr[1]; //字典字段，需要带入的字段值
|
let where: string = valExpressionArr[2]; //字典条件
|
let isBlank = false;
|
let IDs = ""; //需要带入的值
|
|
let dt = await this.getBaseDataTable(tableName); //获得基础表
|
|
let r = new RegExp("\\{(.*?)\\}");
|
let mc = where.match(r);
|
for (let m of mc) {
|
//配置条件字段
|
let whereColumnCnName = m.replace("{", "").replace("}", ""); //需要作为条件的字段名
|
let excelValue = rowExcel[whereColumnCnName]; //获得条件的excel值
|
let excelValues = excelValue.split(",");
|
for (let i = 0; i < excelValues.length; i++) {
|
let w = where.replace("{" + whereColumnCnName + "}", excelValues[i]);
|
let rows = dt.filter(() => {
|
return w;
|
});
|
isBlank = rows == null || rows.length <= 0;
|
if (isBlank) return isBlank;
|
|
let val = rows[0][columnName];
|
if (!val) return true;
|
if (!!IDs) IDs += ",";
|
IDs += val;
|
}
|
rowExcel[excelColumnCnName] = IDs; //将带出的值赋于当前Excel单元格
|
}
|
|
return isBlank;
|
}
|
|
//带入验证, return true 表示验证未通过
|
private async driveInValidate(driveTableName, driveColumnName, driveParentColumnName, parentValue, rowExcel, excelColumnCnName) {
|
let isBlank = false;
|
let dt = await this.getBaseDataTable(driveTableName); //获得基础表
|
let rows = dt.Select(driveParentColumnName + "='" + parentValue + "'");
|
let isError = rows == null || rows.length <= 0;
|
if (!isError) {
|
let val = rows[0][driveColumnName];
|
if (!val) {
|
isBlank = true;
|
isError = true;
|
}
|
rowExcel[excelColumnCnName] = val; //将带出的值赋于当前Excel单元格
|
}
|
return {
|
isError: isError,
|
isBlank: isBlank
|
};
|
}
|
|
//SQL带入, return true 表示验证未通过
|
private async SQLValidate(sql, rowExcel, validateRow, excelColumnCnName, isNullable) {
|
let driveInValue; //获得带入值
|
|
try {
|
let dataList = await this.dbRead.query(sql);
|
if (dataList.length) {
|
let keys = Object.keys(dataList[0]);
|
keys.forEach(key => {
|
driveInValue = dataList[0][key];
|
});
|
}
|
} catch {
|
let err = "字段【" + excelColumnCnName + "】，执行SQL语句有错，错误语句如下：<br/>" + sql + "<br/><br/>";
|
this.setMsg(err);
|
return true;
|
}
|
|
let isError = !driveInValue;
|
if (!isError) {
|
rowExcel[excelColumnCnName] = driveInValue; //将带出的值赋于当前Excel单元格
|
} else {
|
if (isNullable) {
|
rowExcel[excelColumnCnName] = "";
|
isError = false;
|
} //设定默认值
|
else {
|
let express = validateRow["express"];
|
if (express) {
|
rowExcel[excelColumnCnName] = express; //设定默认值
|
isError = false;
|
}
|
}
|
}
|
return isError;
|
}
|
|
//上下文验证, return true 表示验证未通过
|
private async contextValidate(baseTableName, firstColumnName, secondColumnName, firstValue, secondValue) {
|
let dt = await this.getBaseDataTable(baseTableName); //获得基础表
|
let rows = dt.Select(firstColumnName + "='" + firstValue + "' And " + secondColumnName + "='" + secondValue + "'");
|
let isError = rows == null || rows.length <= 0;
|
return isError;
|
}
|
|
//检测表达式是否正确, return true 表示验证未通过
|
private async checkExpress(valExpression) {
|
let opt = ["/", "*", "-", "+"];
|
for (let op in opt) {
|
if (valExpression.StartsWith(op) || valExpression.EndsWith(op) || valExpression.indexOf(op + op) >= 0) return true;
|
}
|
return false;
|
}
|
//#endregion
|
|
//#region 获得列号
|
private getColumnNo(cnName) {
|
let columns = Object.keys(this.m_ExcelDataTable[0]);
|
let columnLetterArr = (
|
"A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z," +
|
"AA,AB,AC,AD,AE,AF,AG,AH,AI,AJ,AK,AL,AM,AN,AO,AP,AQ,AR,AS,AT,AU,AV,AW,AX,AY,AZ," +
|
"BA,BB,BC,BD,BE,BF,BG,BH,BI,BJ,BK,BL,BM,BN,BO,BP,BQ,BR,BS,BT,BU,BV,BW,BX,BY,BZ," +
|
"CA,CB,CC,CD,CE,CF,CG,CH,CI,CJ,CK,CL,CM,CN,CO,CP,CQ,CR,CS,CT,CU,CV,CW,CX,CY,CZ"
|
).split(",");
|
let index = columns.findIndex(item => item === cnName);
|
if (index >= 0) {
|
return columnLetterArr[index];
|
} else {
|
return "未知";
|
}
|
}
|
//#endregion
|
|
//#region Reset，清除缓存
|
/// <summary>
|
/// 重置数据
|
/// </summary>
|
public async reset() {
|
let redis = this.ctx.app.redis.clients.get("common"); // 将消息放入redis缓存
|
let keys = await redis.keys("DataTable_*");
|
for (let key of keys) {
|
await redis.del(key);
|
}
|
}
|
//#endregion
|
|
//#region getTablesImport
|
public async getTablesImport() {
|
let userInfo = await this.userInfo;
|
let redis = this.ctx.app.redis.clients.get("common"); // 将消息放入redis缓存
|
let cacheName = "DataTable_Sys_ImportInfo_" + userInfo.userProduct_Id; //缓冲名称
|
|
let dt = await redis.get(cacheName);
|
if (!dt) {
|
dt = await this.dbRead.find(SysImportInfo);
|
await redis.set(cacheName, JSON.stringify(dt));
|
redis.expire(cacheName, 5 * 60);
|
} else {
|
dt = JSON.parse(dt);
|
}
|
return dt;
|
}
|
//#endregion
|
|
//#region getValidateDataTable
|
/// <summary>
|
/// 获得验证规则表
|
/// </summary>
|
/// <returns></returns>
|
public async getValidateDataTable() {
|
let userInfo = await this.userInfo;
|
let redis = this.ctx.app.redis.clients.get("common"); // 将消息放入redis缓存
|
let cacheName = "DataTable_Sys_ImportColumnInfo_" + userInfo.userProduct_Id; //缓冲名称
|
let importInfo_Id = this.body.importInfo_Id;
|
|
let where = `importInfo_Id in(Select importInfo_Id From Sys_ImportInfo where importInfo_Id=:importInfo_Id And importMode>0)`;
|
let dt = await redis.get(cacheName);
|
if (!dt) {
|
dt = await this.dbRead
|
.createQueryBuilder(SysImportColumnInfo, "t")
|
.where(where, {
|
importInfo_Id: importInfo_Id
|
})
|
.orderBy("orderNo Desc, columnInfo_Id")
|
.getMany();
|
await redis.set(cacheName, JSON.stringify(dt));
|
redis.expire(cacheName, 5 * 60);
|
} else {
|
dt = JSON.parse(dt);
|
}
|
return dt;
|
}
|
//#endregion
|
|
//#region getBaseDataTable
|
/// <summary>
|
/// 获得基础表数据源
|
/// </summary>
|
/// <param name="Path"></param>
|
/// <returns></returns>
|
public async getBaseDataTable(tableName) {
|
let redis = this.ctx.app.redis.clients.get("common"); // 将消息放入redis缓存
|
let sql = "";
|
tableName = tableName.replace("%2D", ",").replace("|", ",");
|
let cacheName = "DataTable_" + tableName; //缓冲名称
|
|
if (tableName.ToLower().indexOf("select") >= 0) {
|
sql = "Select * From (" + tableName + ") t";
|
} else {
|
sql = "Select * From " + tableName;
|
}
|
let dt = await redis.get(cacheName);
|
if (dt == null) {
|
dt = await this.dbRead.query(sql);
|
await redis.set(cacheName, JSON.stringify(dt));
|
redis.expire(cacheName, 5 * 60);
|
}
|
return dt;
|
}
|
//#endregion
|
|
//#region isExist
|
/// <summary>
|
/// 在指定的数据库上执行SQL，判断是否存在记录
|
/// </summary>
|
/// <param name="sql">SQL语句</param>
|
/// <returns>返回是否存在记录</returns>
|
public async isExist(sql) {
|
let obj = await this.dbRead.query(sql);
|
return obj.length > 0;
|
}
|
//#endregion
|
|
//************************************************
|
// 更新&递增
|
//************************************************
|
//#region updateAndImport
|
public async updateAndImport() {
|
let userInfo = await this.userInfo;
|
let columns = Object.keys(this.m_ExcelDataTable[0]);
|
let msg = "";
|
let columnNames = ""; //字段名称
|
let sql = "";
|
let UpdateCount = 0; //导入条数
|
let InsertCount = 0; //增加条数
|
|
let importPrimarykeyEn = this.importInfo.importPrimarykeyEn; //主键名称
|
let importPrimarykeyCn = this.importInfo.importPrimarykeyCn; //主键Excel中的名称
|
let fixUpdateField = this.importInfo.fixUpdateField; //固定更新字段
|
let arrayKeyID = importPrimarykeyEn.split(","); //多个关键词 英文
|
let arrayCnKeyID = importPrimarykeyCn.split(","); //多个关键词 中文
|
if (fixUpdateField) fixUpdateField = "," + fixUpdateField;
|
|
for (let col of columns) {
|
let excelColumnName = col; //中文字段名
|
let rows = this.m_ValidateDataTable.filter(item => item.cnName === excelColumnName);
|
let colName = rows[0]["columnName"];
|
|
if (this.jumpFieldCnNameInsert.indexOf("," + excelColumnName) >= 0) continue; //跳过字段
|
|
if (columnNames) columnNames += ",";
|
columnNames += colName; ////英文字段名
|
}
|
columnNames = columnNames.trim();
|
|
//#region 导入数据
|
let existTableName = this.importInfo.tableName; //判断数据是否存在的表
|
|
for (let row of this.m_ExcelDataTable) {
|
//#region 获得条件
|
let where = "";
|
for (let i = 0; i < arrayCnKeyID.length; i++) {
|
let keyValue = row[arrayCnKeyID[i]];
|
if (i > 0) where += " And ";
|
where += arrayKeyID[i] + "='" + keyValue + "'";
|
}
|
where = " where " + where + " And UserProduct_Id=" + userInfo.userProduct_Id;
|
//#endregion
|
|
let tsql = "Select " + arrayKeyID[0] + " from " + existTableName + where;
|
let isExist = await this.isExist(tsql);
|
if (isExist) {
|
//更新操作
|
if (this.importInfo.importType == this.eImportType.onlyImport) continue; //只新增，不更新
|
|
//#region
|
let values = "";
|
//需要更新的字段
|
for (let j = 0; j < columns.length; j++) {
|
let excelColumnName = columns[j]; //中文字段名
|
let rows = this.m_ValidateDataTable.filter(item => item.cnName === excelColumnName);
|
let columnName = rows[0]["columnName"]; ////英文字段名
|
|
if (this.jumpFieldCnNameUpdate.indexOf("," + excelColumnName) >= 0) continue; //跳过字段
|
|
let v = row[excelColumnName];
|
if (!v) {
|
values += columnName + " = null,";
|
} else {
|
if (typeof v === "string") v = v.replace("'", "''");
|
values += columnName + " = '" + v + "'" + ",";
|
}
|
}
|
values += "Modifier='批量导入', ModifyDate=getdate() " + (fixUpdateField || "");
|
|
sql += "Update " + this.importInfo.tableName + " Set " + values + where;
|
UpdateCount++;
|
//#endregion
|
} //新增
|
else {
|
if (this.importInfo.importType == this.eImportType.onlyUpdate) continue; //只更新，不新增
|
|
//#region
|
let values = "";
|
for (let j = 0; j < columns.length; j++) {
|
let excelColumnName = columns[j]; //中文字段名
|
if (this.jumpFieldCnNameInsert.indexOf("," + excelColumnName) >= 0) continue; //跳过字段
|
|
let v = row[excelColumnName];
|
if (!v) values += "null,";
|
else {
|
if (typeof v === "string") v = v.replace("'", "''");
|
values += "'" + v + "', ";
|
}
|
}
|
values += userInfo.user_Id + ", '批量导入', getdate()";
|
sql += `Insert Into ${this.importInfo.tableName}(${columnNames}, CreateID, Creator, CreateDate) values(${values});`;
|
InsertCount++;
|
//#endregion
|
}
|
|
if (this.currentRowNumber % 50 == 1) {
|
try {
|
await this.dbWrite.query(sql);
|
} catch (ex) {
|
msg = "导入失败，失败原因：" + ex.message + "<br>错误语句如下：<br>" + sql;
|
this.ERROR_I++;
|
this.setMsg(msg, "red");
|
return false;
|
}
|
msg = "数据已导入到第" + this.currentRowNumber + "行 ...";
|
this.setMsg(msg);
|
sql = "";
|
}
|
this.currentRowNumber++; //当前处理行号
|
}
|
if (!!sql) {
|
try {
|
await this.dbWrite.query(sql);
|
} catch (ex) {
|
msg = "导入失败，失败原因：" + ex.message + "<br>错误语句如下：<br>" + sql;
|
this.ERROR_I++;
|
this.setMsg(msg, "red");
|
return false;
|
}
|
msg = "数据已导入到第" + this.currentRowNumber + "行 ...";
|
this.setMsg(msg);
|
}
|
//#endregion
|
|
//#region 执行存储过程 SQL语句
|
try {
|
if (this.importInfo.execSQL) {
|
await this.dbWrite.query(this.importInfo.execSQL);
|
}
|
} catch (ex) {
|
msg = "<br><font color=red>执行自定SQL语句出错，原因：<br>" + ex.message + "</font><br>";
|
this.setMsg(msg);
|
}
|
//#endregion
|
|
msg = "数据导入成功, 共更新" + UpdateCount + "条, 共新增" + InsertCount + "条，共删除" + this.deleteCount + "条。";
|
this.setMsg(msg, "blue");
|
|
return true;
|
}
|
//#endregion
|
|
//************************************************
|
// 全清全导
|
//************************************************
|
//#region clearAndImport
|
public async clearAndImport() {
|
let userInfo = await this.userInfo;
|
let columns = Object.keys(this.m_ExcelDataTable[0]);
|
let columnNames = ""; //字段名称
|
let sql = "";
|
let msg = "";
|
|
for (let col of columns) {
|
let excelColumnName = col; //中文字段名
|
let rows = this.m_ValidateDataTable.filter(item => item.cnName === excelColumnName);
|
let colName = rows[0]["columnName"];
|
if (columnNames) {
|
columnNames += ", ";
|
}
|
columnNames += colName; //英文字段名
|
}
|
columnNames = columnNames.trim();
|
|
//#region 备份、清除数据
|
let isBackupForDelete = this.importInfo.isBackupForDelete; //删除时是否备份记录
|
|
if (isBackupForDelete) {
|
sql = "INSERT INTO " + this.importInfo.tableName + "_H Select *,getdate() from " + this.importInfo.tableName + ";";
|
}
|
sql += "truncate table " + this.importInfo.tableName + ";";
|
await this.dbWrite.query(sql);
|
//#endregion
|
|
for (let row of this.m_ExcelDataTable) {
|
let values = "";
|
for (let j = 0; j < columns.length; j++) {
|
values += "'" + row[j] + "', ";
|
}
|
values += "'" + userInfo.userTrueName + "'";
|
sql += "Insert Into " + this.importInfo.tableName + " (" + columnNames + ", Creator) values(" + values + ");";
|
if (this.currentRowNumber % 50 == 1) {
|
try {
|
await this.dbWrite.query(sql);
|
} catch (ex) {
|
msg = "导入失败，失败原因：" + ex.message + "<br>错误语句如下：<br>" + sql;
|
this.setMsg(msg);
|
return false;
|
}
|
msg = "数据已导入到第" + this.currentRowNumber + "行 ...";
|
this.setMsg(msg);
|
sql = "";
|
}
|
this.currentRowNumber++; //当前处理行号
|
}
|
if (!!sql) {
|
try {
|
await this.dbWrite.query(sql);
|
} catch (ex) {
|
msg = "导入失败，失败原因：" + ex.message + "<br>错误语句如下：<br>" + sql;
|
this.setMsg(msg);
|
return false;
|
}
|
msg = "数据已导入到第" + this.currentRowNumber + "行 ...";
|
this.setMsg(msg);
|
}
|
|
//#region 执行存储过程 SQL语句
|
try {
|
let execSQL = this.importInfo.execSQL;
|
if (execSQL) {
|
await this.dbWrite.query(sql);
|
}
|
} catch (ex) {
|
msg = "<br><font color=red>执行自定SQL语句出错，原因：<br>" + ex.message + "</font>";
|
this.setMsg(msg, "red");
|
}
|
//#endregion
|
|
msg = "数据导入成功，数据全部清除，重新导入" + this.currentRowNumber + "条数据。";
|
this.setMsg(msg, "blue");
|
return true;
|
}
|
//#endregion
|
|
//************************************************
|
// 不清除 不管存不存在都进行导入
|
//************************************************
|
//#region allImport
|
public async allImport() {
|
let userInfo = await this.userInfo;
|
let columns = Object.keys(this.m_ExcelDataTable[0]);
|
let msg = "";
|
let columnNames = ""; //字段名称
|
let sql = "";
|
let InsertCount = 0; //增加条数
|
|
for (let col of columns) {
|
let excelColumnName = col; //中文字段名
|
let rows = this.m_ValidateDataTable.filter(item => item.cnName === excelColumnName);
|
let colName = rows[0]["columnName"];
|
|
if (this.jumpFieldCnNameInsert.indexOf("," + excelColumnName) >= 0) continue; //跳过字段
|
if (columnNames) columnNames += ",";
|
columnNames += colName; ////英文字段名
|
}
|
columnNames = columnNames.trim();
|
|
sql = "";
|
for (let row of this.m_ExcelDataTable) {
|
//#region 新增数据
|
let values = "";
|
for (let j = 0; j < columns.length; j++) {
|
let excelColumnName = columns[j]; //中文字段名
|
if (this.jumpFieldCnNameInsert.indexOf("," + excelColumnName) >= 0) continue; //跳过字段
|
|
let v = row[j] ? row[j].replace("'", "''") : null;
|
if (!v) values += "null,";
|
else values += "'" + v + "', ";
|
}
|
values += "'" + userInfo.userTrueName + "'";
|
sql += "Insert Into " + this.importInfo.tableName + " (" + columnNames + ", Creator) values(" + values + ");";
|
InsertCount++;
|
//#endregion
|
|
if (this.currentRowNumber % 50 == 1) {
|
try {
|
await this.dbWrite.query(sql);
|
} catch (ex) {
|
msg = "导入失败，失败原因：" + ex.message + "<br>错误语句如下：<br>" + sql;
|
this.setMsg(msg);
|
return false;
|
}
|
msg = "数据已导入到第" + this.currentRowNumber + "行 ...";
|
this.setMsg(msg);
|
sql = "";
|
}
|
this.currentRowNumber++; //当前处理行号
|
}
|
if (!!sql) {
|
try {
|
await this.dbWrite.query(sql);
|
} catch (ex) {
|
msg = "导入失败，失败原因：" + ex.message + "<br>错误语句如下：<br>" + sql;
|
this.setMsg(msg);
|
return false;
|
}
|
msg = "数据已导入到第" + this.currentRowNumber + "行 ...";
|
this.setMsg(msg);
|
}
|
|
//#region 执行存储过程 SQL语句
|
try {
|
let execSQL = this.importInfo.execSQL;
|
if (execSQL) {
|
await this.dbWrite.query(sql);
|
}
|
} catch (ex) {
|
msg = "<br><font color=red>执行自定SQL语句出错，原因：<br>" + ex.message + "</font>";
|
this.setMsg(msg);
|
}
|
//#endregion
|
|
msg = "数据导入成功, 共新增" + InsertCount + "条，共删除" + this.deleteCount + "条。";
|
this.setMsg(msg);
|
|
return true;
|
}
|
//#endregion
|
}
|
