import * as path from "path";
|
import * as XLSX from "xlsx";
|
import { default as BaseController } from "../baseController";
|
import { Post } from "egg-shell-decorators";
|
import { SysExportInfo } from "../../entity/sys/import/sysExportInfo";
|
import { SysExportColumnInfo } from "../../entity/sys/import/sysExportColumnInfo";
|
import { SysExportVueData } from "../../entity/sys/import/sysExportVueData";
|
import { SysMvcTableColumn } from "../../entity/sys/core/sysMvcTableColumn";
|
import moment = require("moment");
|
import * as xlsxStye from "xlsx-style";
|
import { SysDropDown } from "../../entity/sys/core/sysDropDown";
|
import * as mssql from "mssql";
|
import { In } from "typeorm";
|
|
/**
|
* 通用导出模块
|
*/
|
export default class ExportController extends BaseController {
|
//#region getExportInfo
|
/**
|
* 获得导出字段信息
|
*/
|
@Post()
|
public async getExportInfo() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
try {
|
let dataInfo = await this.dbRead.findOne(SysExportInfo, body.id);
|
if (dataInfo != null) {
|
this.info.data = dataInfo;
|
this.info.result = true;
|
} else {
|
this.info.result = false;
|
this.info.msg = "获取数据失败";
|
}
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "获取数据失败，" + ex.message;
|
}
|
|
ctx.body = this.info;
|
}
|
//#endregion
|
|
//#region getExportColList
|
/**
|
* 获得导出字段信息
|
*/
|
@Post()
|
public async getExportColList() {
|
let { ctx } = this;
|
let showAll = this.body.showAll;
|
try {
|
let where = {
|
enable: 1,
|
exportInfo_Id: this.body.exportInfo_Id
|
};
|
if (showAll) {
|
delete where.enable;
|
}
|
let dataList = await this.dbRead.find(SysExportColumnInfo, {
|
where: where,
|
order: { orderNo: "DESC", exportInfo_Id: "ASC" }
|
});
|
if (dataList) {
|
this.info.data = dataList;
|
this.info.result = true;
|
} else {
|
this.info.result = false;
|
this.info.msg = "获取数据失败";
|
}
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "获取数据失败，" + ex.message;
|
}
|
|
ctx.body = this.info;
|
}
|
//#endregion
|
|
//#region getExportVueDataList
|
/**
|
* 获得导出VueData数据列表
|
*/
|
@Post()
|
public async getExportVueDataList() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
try {
|
let dataList = await this.dbRead.find(SysExportVueData, {
|
where: {
|
exportInfo_Id: body.exportInfo_Id
|
},
|
order: { vueData_Id: "ASC" }
|
});
|
if (dataList) {
|
this.info.data = dataList;
|
this.info.result = true;
|
} else {
|
this.info.result = false;
|
this.info.msg = "获取数据失败";
|
}
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "获取数据失败，" + ex.message;
|
}
|
|
ctx.body = this.info;
|
}
|
//#endregion
|
|
//#region updateTitle
|
/**
|
* 修改名称
|
*/
|
@Post()
|
public async updateTitle() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
try {
|
let tempInfo = await this.dbRead.findOne(SysExportVueData, body.id);
|
if (tempInfo) {
|
await this.dbWrite.update(SysExportVueData, tempInfo.vueData_Id, {
|
vueDataName: body.value
|
});
|
}
|
|
this.info.result = true;
|
this.info.msg = "保存成功";
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "保存失败，" + ex.message;
|
}
|
|
ctx.body = this.info;
|
}
|
//#endregion
|
|
//#region deleteItem
|
/**
|
* 删除项
|
*/
|
@Post()
|
public async deleteItem() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
let userInfo = await ctx.helper.userInfo();
|
try {
|
let tempInfo = await this.dbRead.findOne(SysExportVueData, {
|
where: { vueData_Id: body.vueData_Id, userProduct_Id: userInfo.userProduct_Id }
|
});
|
if (tempInfo) {
|
await this.dbWrite.delete(SysExportVueData, {
|
vueData_Id: tempInfo.vueData_Id
|
});
|
this.info.result = true;
|
this.info.msg = "删除成功";
|
} else {
|
this.info.result = false;
|
this.info.msg = "数据不存在";
|
}
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "保存失败，" + ex.message;
|
}
|
|
ctx.body = this.info;
|
}
|
//#endregion
|
|
//#region saveData
|
/**
|
* 删除项
|
*/
|
@Post()
|
public async saveData() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
let userInfo = await this.userInfo;
|
try {
|
if (body.vueData_Id == 0) {
|
this.info.result = false;
|
this.info.msg = "系统标准模板不允许操作！";
|
ctx.body = this.info;
|
}
|
let tempInfo = await this.dbRead.findOne(SysExportVueData, body.vueData_Id);
|
if (tempInfo) {
|
tempInfo.vueData = body.vueData;
|
tempInfo.vueDataName = body.label;
|
tempInfo.modifyDate = new Date();
|
tempInfo.modifyID = userInfo.user_Id;
|
tempInfo.modifier = userInfo.userTrueName;
|
await this.setAccountInfo(tempInfo);
|
await this.dbWrite.update(SysExportVueData, tempInfo.vueData_Id, {
|
vueData: body.vueData,
|
vueDataName: body.label
|
});
|
this.info.result = true;
|
this.info.msg = "修改成功";
|
} else {
|
tempInfo = new SysExportVueData();
|
tempInfo.vueData = body.vueData;
|
tempInfo.vueDataName = body.label;
|
tempInfo.exportInfo_Id = body.exportInfo_Id;
|
tempInfo.createDate = new Date();
|
tempInfo.createID = userInfo.user_Id;
|
tempInfo.creator = userInfo.userTrueName;
|
await this.dbWrite.save(SysExportVueData, tempInfo);
|
this.info.result = true;
|
this.info.data = {
|
vueData_Id: tempInfo.vueData_Id
|
};
|
this.info.msg = "新增模板成功";
|
}
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "保存失败，" + ex.message;
|
}
|
|
ctx.body = this.info;
|
}
|
//#endregion
|
|
//#region exportDataVue 获得导出数据集
|
/// <summary>
|
/// 获得导出数据集
|
/// </summary>
|
@Post()
|
public async exportDataVue() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
let resultDataTable = null;
|
let userInfo = await this.userInfo;
|
|
try {
|
let where = await ctx.service.common.getWhere();
|
|
//#region 普通导出
|
let columnInfoList = await this.dbRead.find(SysMvcTableColumn, {
|
tableView: body.tableView,
|
isManagerTable: 1
|
}); //字段属性
|
// 排除字段
|
let exceptFields = ["platuser_id", "platusercode", "platusername", "platcorpname", "userproduct_id", "userproductcode", "userproductalias"];
|
columnInfoList = columnInfoList.filter(item => exceptFields.indexOf(item.columnName.toLocaleLowerCase()) < 0);
|
|
//导出模板
|
let exportInfo_Id = body.exportInfo_Id;
|
let vueData_Id = body.vueData_Id;
|
|
let exportColList = await this.dbRead.find(SysExportColumnInfo, {
|
where: { exportInfo_Id: exportInfo_Id, enable: 1 },
|
order: { orderNo: "DESC", columnInfo_Id: "ASC" }
|
});
|
|
let tableName = body.tableName || body.tableView;
|
tableName = tableName.replace(/_/g, "");
|
let _tableName = ctx.helper.caseStyle(tableName);
|
let entityPath = `../../entity/${body.folder}/` + _tableName;
|
let module = await import(entityPath);
|
var m = module[tableName];
|
let masterRep = this.dbRead.getRepository(m);
|
let buider = masterRep.createQueryBuilder();
|
let _expandFields = []; // 扩展字段
|
|
if (vueData_Id == 0) {
|
//标准模板
|
let index = 0;
|
for (let colInfo of exportColList) {
|
let comment = colInfo.cnName;
|
if (colInfo.colExpression) {
|
if (index === 0) {
|
buider.select(colInfo.colExpression, comment);
|
} else {
|
buider.addSelect(colInfo.colExpression, comment);
|
}
|
} else {
|
if (index === 0) {
|
buider.select(colInfo.columnName, comment);
|
} else {
|
buider.addSelect(colInfo.columnName, comment);
|
}
|
}
|
index++;
|
}
|
// 在导出模板未设置，那么从默认设置中导出
|
if (exportColList.length == 0) {
|
let tableName = body.tableName || body.tableView;
|
tableName = tableName.replace(/_/g, "");
|
let colList = masterRep.metadata.columns;
|
let index = 0;
|
for (let col of colList) {
|
let colInfo = columnInfoList.find(w => ctx.helper.caseStyle(w.columnName) === col.propertyName);
|
if (colInfo == null || colInfo.isManagerTable != 1) continue;
|
|
if (colInfo != null && colInfo.columnComment) {
|
if (index === 0) {
|
buider.select(col.propertyName, colInfo.columnComment);
|
} else {
|
buider.addSelect(col.propertyName, colInfo.columnComment);
|
}
|
index++;
|
}
|
}
|
}
|
} //自定义模板
|
else {
|
let exportVueDataInfo = await this.dbRead.findOne(SysExportVueData, vueData_Id);
|
let templateInfo = JSON.parse(exportVueDataInfo.vueData);
|
let index = 0;
|
_expandFields = templateInfo.vueData.filter(item => item.isExpandField);
|
|
for (let item of templateInfo.vueData) {
|
let colExpression = item.colExpression;
|
//自定义字段
|
if (item.columnName.indexOf("custom_") >= 0 && !colExpression) {
|
continue;
|
}
|
if (item.isExpandField) {
|
continue;
|
}
|
let columnName = item.columnName;
|
if (colExpression) {
|
columnName = colExpression;
|
//获得表达式
|
exportColList.forEach(item => {
|
columnName = columnName.replace("{" + item.cnName + "}", item.columnName);
|
columnName = columnName.replace(item.cnName, item.columnName);
|
});
|
}
|
if (index === 0) {
|
buider.select(columnName, item.aliasName || item.cnName);
|
} else {
|
buider.addSelect(columnName, item.aliasName || item.cnName);
|
}
|
index++;
|
}
|
buider.addSelect("expandFields");
|
}
|
|
buider.where(where);
|
buider.take(20000);
|
if (this.body.orderBy) {
|
buider.orderBy(this.body.orderBy);
|
}
|
|
resultDataTable = await buider.getRawMany();
|
// 扩展字段处理
|
for (let item of resultDataTable) {
|
try {
|
let expandFields = item.expandFields;
|
if (expandFields) {
|
expandFields = JSON.parse(expandFields);
|
}
|
for (let _field of _expandFields) {
|
let v = null;
|
if (expandFields) {
|
v = expandFields[_field.columnName];
|
}
|
item[_field.aliasName || _field.cnName] = v;
|
}
|
delete item.expandFields;
|
} catch {
|
continue;
|
}
|
}
|
//#endregion
|
|
// 下拉框ID转译，先分组获得下拉框ID
|
let _colList = columnInfoList.filter(item => item.dropDown_Id > 0 && "tinyint,byte,int32,int64".indexOf(item.dataType) >= 0);
|
for (let a of _colList) {
|
let dd = await this.dbRead.findOne(SysDropDown, a.dropDown_Id);
|
if (!dd || !dd.sql) continue;
|
|
dd.sql = dd.sql.replace("{AUTHWHERE}", "1=1");
|
dd.sql = dd.sql.replace(/\{userProduct_Id\}/gi, userInfo.userProduct_Id.toString());
|
let dropDownTable: Array<any> = await this.dbRead.query(dd.sql);
|
dropDownTable = dropDownTable.map(item => {
|
let keys = Object.keys(item);
|
if (keys.length == 2) {
|
item.value = item[keys[0]];
|
item.text = item[keys[1]];
|
} else if (keys.length >= 3) {
|
item.value = item[keys[0]];
|
item.code = item[keys[1]];
|
item.text = item[keys[2]];
|
}
|
return item;
|
});
|
|
let findColumnList = columnInfoList.filter(w => w.dropDown_Id == a.dropDown_Id);
|
for (let cInfo of findColumnList) {
|
for (let row of resultDataTable) {
|
let val = row[cInfo.columnComment];
|
let item = dropDownTable.find(d => d.value == val);
|
if (item) {
|
row[cInfo.columnComment] = item.text;
|
}
|
}
|
}
|
}
|
|
// 将null值清空
|
for (let row of resultDataTable) {
|
Object.keys(row).forEach(key => {
|
if (row[key] === null) {
|
row[key] = "";
|
}
|
});
|
}
|
let sheetData = XLSX.utils.json_to_sheet(resultDataTable);
|
// 构造workBook
|
let workBook = {
|
SheetNames: ["导出数据"],
|
Sheets: {
|
导出数据: sheetData
|
}
|
};
|
let worksheet1 = workBook.Sheets["导出数据"];
|
|
// 设置内容格式
|
let rowCount = resultDataTable.length;
|
let colCount = rowCount ? Object.keys(resultDataTable[0]).length : 0;
|
let setStyle = (cell, horizontal, bgColor?: string) => {
|
cell.s = {
|
alignment: {
|
//对齐方式
|
vertical: "center",
|
horizontal: horizontal
|
},
|
font: {
|
sz: 10,
|
bold: false,
|
name: "Arial"
|
},
|
border: {
|
top: {
|
style: "thin",
|
color: { rgb: "757575" }
|
},
|
bottom: {
|
style: "thin",
|
color: { rgb: "757575" }
|
},
|
left: {
|
style: "thin",
|
color: { rgb: "757575" }
|
},
|
right: {
|
style: "thin",
|
color: { rgb: "757575" }
|
}
|
}
|
};
|
if (bgColor) {
|
cell.s.fill = {
|
fgColor: { rgb: bgColor }
|
};
|
}
|
};
|
for (let rowIndex = 0; rowIndex <= rowCount; rowIndex++) {
|
for (let colIndex = 0; colIndex < colCount; colIndex++) {
|
let col = XLSX.utils.encode_col(colIndex); // 转为字母
|
let cellIndex = col + (1 + rowIndex);
|
let cell = worksheet1[cellIndex];
|
if (!cell) continue;
|
if (rowIndex === 0) {
|
setStyle(cell, "center", "ff9933");
|
} else {
|
setStyle(cell, "center");
|
}
|
}
|
}
|
|
let root = path.resolve();
|
let url = `/download/导出数据${moment(new Date()).format("YYMMDD_HHmmss")}.xlsx`;
|
let fileName = root + url.replace(/\//g, path.sep);
|
let pathToCreate = fileName.substring(0, fileName.lastIndexOf(path.sep));
|
ctx.helper.mkdir(pathToCreate);
|
// XLSX.writeFile(workBook, fileName);
|
var wopts = { bookType: "xlsx", bookSST: false, type: "file" };
|
xlsxStye.writeFileSync(workBook, fileName, wopts);
|
|
this.info.result = true;
|
this.info.data = {
|
url: url
|
};
|
} catch (error) {
|
console.log("导出数据错误：" + error.message);
|
this.info.result = false;
|
this.info.msg = "导出数据错误：" + error.message;
|
}
|
ctx.body = this.info;
|
}
|
//#endregion
|
|
//#region imortModule 导入模块
|
/**
|
* 导入模块
|
*/
|
@Post()
|
public async imortModule() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
try {
|
const connection: any = await this.dbWrite.connection;
|
let request = new mssql.Request(connection.driver.master);
|
request.input("exportInfo_Id", body.exportInfo_Id);
|
request.input("tableView", body.tableView);
|
request.output("outMsg", mssql.NVarChar(2000));
|
let result = await request.execute("sp_Sys_ExportInfoFromMvc");
|
let outMsg = result.output.outMsg;
|
if (outMsg) {
|
this.info.msg = outMsg;
|
this.info.result = false;
|
} else {
|
this.info.msg = "导入成功";
|
this.info.result = true;
|
}
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "保存失败，" + ex.message;
|
}
|
|
ctx.body = this.info;
|
}
|
//#endregion
|
|
//#region saveFieldLeft 标准字段信息
|
/**
|
* 标准字段信息
|
*/
|
@Post()
|
public async saveFieldLeft() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
try {
|
let colInfo = new SysExportColumnInfo();
|
colInfo = Object.assign(colInfo, body);
|
await this.dbWrite.save(colInfo);
|
this.info.msg = "保存成功";
|
this.info.result = true;
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "保存失败，" + ex.message;
|
}
|
|
ctx.body = this.info;
|
}
|
//#endregion
|
|
//#region saveModule 保存模块
|
/**
|
* 保存模块
|
*/
|
@Post()
|
public async saveModule() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
try {
|
await this.dbWrite.update(
|
SysExportInfo,
|
{
|
exportInfo_Id: body.exportInfo_Id
|
},
|
{
|
tableNameCn: body.tableNameCn,
|
tableName: body.tableName,
|
orderNo: body.orderNo,
|
parentId: body.parentId
|
}
|
);
|
this.info.msg = "保存成功";
|
this.info.result = true;
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "保存失败，" + ex.message;
|
}
|
|
ctx.body = this.info;
|
}
|
//#endregion
|
|
//#region deleteModule 删除模块
|
/**
|
* 删除模块
|
*/
|
@Post()
|
public async deleteModule() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
try {
|
if (!Number(body.exportInfo_Id)) {
|
this.info.result = false;
|
this.info.msg = "数据无效";
|
this.ctx.body = this.info;
|
return;
|
}
|
await this.dbWrite.delete(SysExportInfo, {
|
exportInfo_Id: body.exportInfo_Id
|
});
|
this.info.msg = "删除成功";
|
this.info.result = true;
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "删除失败，" + ex.message;
|
}
|
|
ctx.body = this.info;
|
}
|
//#endregion
|
|
//#region changeEnable 开启/关闭导出
|
/**
|
* 开启/关闭导出
|
*/
|
@Post()
|
public async changeEnable() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
try {
|
if (!Array.isArray(body.idList)) {
|
this.info.result = false;
|
this.info.msg = "数据无效";
|
this.ctx.body = this.info;
|
return;
|
}
|
if (!body.idList.length) {
|
this.info.result = false;
|
this.info.msg = "数据无效";
|
this.ctx.body = this.info;
|
return;
|
}
|
await this.dbWrite.update(
|
SysExportColumnInfo,
|
{
|
columnInfo_Id: In(body.idList)
|
},
|
{
|
enable: body.enable
|
}
|
);
|
this.info.msg = "设置完成";
|
this.info.result = true;
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "设置失败，" + ex.message;
|
}
|
|
ctx.body = this.info;
|
}
|
//#endregion
|
|
//#region deleteField 删除字段
|
/**
|
* 删除字段
|
*/
|
@Post()
|
public async deleteField() {
|
let { ctx } = this;
|
let body = ctx.request.body;
|
if (!Array.isArray(body.idList)) {
|
this.info.result = false;
|
this.info.msg = "数据无效";
|
this.ctx.body = this.info;
|
return;
|
}
|
if (!body.idList.length) {
|
this.info.result = false;
|
this.info.msg = "数据无效";
|
this.ctx.body = this.info;
|
return;
|
}
|
try {
|
await this.dbWrite.delete(SysExportColumnInfo, {
|
columnInfo_Id: In(body.idList)
|
});
|
this.info.msg = "删除完成";
|
this.info.result = true;
|
} catch (ex) {
|
this.info.result = false;
|
this.info.msg = "删除失败，" + ex.message;
|
}
|
|
ctx.body = this.info;
|
}
|
//#endregion
|
}
|
