GuDingZiChan/backend/scripts/importSheet2PublicAssetsToLive.js

614 lines
20 KiB
JavaScript

let PrismaClient
try {
;({ PrismaClient } = require('@prisma/client'))
} catch {
;({ PrismaClient } = require('/app/node_modules/@prisma/client'))
}
const fs = require('fs')
const path = require('path')
const { spawnSync } = require('child_process')
const {
buildAssignableExistingSheet2PublicEquipmentWhere,
PUBLIC_ASSET_ASSIGNABLE_EXISTING_STATUSES,
buildPublicAssetBorrowState,
isSheet2PublicEquipmentDescription,
normalizeText
} = require('./sheet2PublicAssetImportUtils')
const OUTPUT_ROOT = path.resolve(__dirname, '../../temp_migration_output')
const REMOTE_NODE_COMMAND = 'cd /opt/equipment-management && docker exec -i equipment-backend node'
const DEFAULT_PUBLIC_PERSON = {
nickname: '公共资产',
real_name: '公共资产',
job_number: null,
id_number: null,
department: null,
main_department_id: null,
position: '系统保留',
phone: null,
email: null,
status: 'active',
remarks: '系统保留人员:用于承接公共设备,不参与 HR 组织同步。',
source_userid: '__public_asset__',
source_system: 'system_reserved',
source_main_department_id: null,
source_dept_ids: null,
source_department_name: null,
source_main_department_name: null,
department_sync_bucket: null,
manager_source_userid: null,
source_employee_status: null,
last_synced_at: null
}
const requireArgValue = (args, index, argumentName) => {
const value = args[index + 1]
if (!value || String(value).startsWith('--')) {
throw new Error(`${argumentName} 缺少参数值`)
}
return value
}
const parseArgs = (args = process.argv.slice(2)) => {
const options = {
inputDir: '',
apply: false,
dryRun: true,
plinkPath: '',
sshHost: '',
sshHostkey: '',
sshPassword: ''
}
let explicitDryRun = false
for (let index = 0; index < args.length; index += 1) {
const arg = args[index]
if (arg === '--input-dir') {
options.inputDir = requireArgValue(args, index, arg)
index += 1
} else if (arg === '--dry-run') {
explicitDryRun = true
} else if (arg === '--apply') {
options.apply = true
} else if (arg === '--plink-path') {
options.plinkPath = requireArgValue(args, index, arg)
index += 1
} else if (arg === '--ssh-host') {
options.sshHost = requireArgValue(args, index, arg)
index += 1
} else if (arg === '--ssh-hostkey') {
options.sshHostkey = requireArgValue(args, index, arg)
index += 1
} else if (arg === '--ssh-password') {
options.sshPassword = requireArgValue(args, index, arg)
index += 1
} else {
throw new Error(`不支持的参数: ${arg}`)
}
}
if (options.apply && explicitDryRun) {
throw new Error('--apply 与 --dry-run 不能同时使用')
}
options.dryRun = !options.apply
return options
}
const resolveRemoteConnection = (options, env = process.env) => {
const connection = {
plinkPath: normalizeText(options.plinkPath || env.FIXED_ASSET_PLINK_PATH),
sshHost: normalizeText(options.sshHost || env.FIXED_ASSET_SSH_HOST),
sshHostkey: normalizeText(options.sshHostkey || env.FIXED_ASSET_SSH_HOSTKEY),
sshPassword: options.sshPassword || env.FIXED_ASSET_SSH_PASSWORD || ''
}
const missing = []
if (!connection.plinkPath) missing.push('FIXED_ASSET_PLINK_PATH / --plink-path')
if (!connection.sshHost) missing.push('FIXED_ASSET_SSH_HOST / --ssh-host')
if (!connection.sshHostkey) missing.push('FIXED_ASSET_SSH_HOSTKEY / --ssh-hostkey')
if (!connection.sshPassword) missing.push('FIXED_ASSET_SSH_PASSWORD / --ssh-password')
if (missing.length > 0) {
throw new Error(`缺少固定资产远程连接参数:${missing.join('、')}。未发起网络连接。`)
}
return connection
}
const ensureOutputDir = (prefix) => {
const timestamp = new Date().toISOString().replace(/[:.]/g, '-').replace('T', '_').slice(0, 19)
const outputDir = path.join(OUTPUT_ROOT, `${prefix}_${timestamp}`)
fs.mkdirSync(outputDir, { recursive: true })
return outputDir
}
const resolveLatestSheet2Dir = () => {
const candidates = fs
.readdirSync(OUTPUT_ROOT, { withFileTypes: true })
.filter((entry) => entry.isDirectory() && entry.name.startsWith('sheet2_public_dry_run_'))
.map((entry) => entry.name)
.sort()
if (!candidates.length) {
throw new Error(`未找到 Sheet2 公共设备 dry-run 输出目录: ${OUTPUT_ROOT}`)
}
return path.join(OUTPUT_ROOT, candidates[candidates.length - 1])
}
const resolveInputDir = (inputDirArg) => {
if (!inputDirArg) {
return resolveLatestSheet2Dir()
}
const resolved = path.isAbsolute(inputDirArg)
? inputDirArg
: path.resolve(process.cwd(), inputDirArg)
if (!fs.existsSync(resolved)) {
throw new Error(`指定的输入目录不存在: ${resolved}`)
}
return resolved
}
const writeJson = (filePath, value) => {
fs.writeFileSync(filePath, `${JSON.stringify(value, null, 2)}\n`)
}
const readJson = (filePath) => JSON.parse(fs.readFileSync(filePath, 'utf8'))
const runRemoteNodeScript = (script, connection) => {
const result = spawnSync(
connection.plinkPath,
[
'-batch',
'-ssh',
'-hostkey',
connection.sshHostkey,
'-pw',
connection.sshPassword,
connection.sshHost,
REMOTE_NODE_COMMAND
],
{
encoding: 'utf8',
input: script,
maxBuffer: 64 * 1024 * 1024,
windowsHide: true
}
)
if (result.error) {
throw new Error('无法启动远程连接工具,请检查 FIXED_ASSET_PLINK_PATH / --plink-path。')
}
if (result.status !== 0) {
throw new Error('远程脚本执行失败,请检查连接参数、远程服务状态和服务器日志。')
}
const stdout = normalizeText(result.stdout)
if (!stdout) {
throw new Error('远程脚本返回为空')
}
try {
return JSON.parse(stdout)
} catch {
throw new Error('远程脚本返回格式无效')
}
}
const buildRemotePreviewScript = (payload) => {
const payloadBase64 = Buffer.from(JSON.stringify(payload), 'utf8').toString('base64')
const seedBase64 = Buffer.from(JSON.stringify(DEFAULT_PUBLIC_PERSON), 'utf8').toString('base64')
const assignableExistingWhereBase64 = Buffer.from(
JSON.stringify(
buildAssignableExistingSheet2PublicEquipmentWhere(PUBLIC_ASSET_ASSIGNABLE_EXISTING_STATUSES)
),
'utf8'
).toString('base64')
return `
let PrismaClient
try { ({ PrismaClient } = require('@prisma/client')) } catch { ({ PrismaClient } = require('/app/node_modules/@prisma/client')) }
const prisma = new PrismaClient()
const payload = JSON.parse(Buffer.from('${payloadBase64}', 'base64').toString('utf8'))
const publicSeed = JSON.parse(Buffer.from('${seedBase64}', 'base64').toString('utf8'))
const assignableExistingWhere = JSON.parse(Buffer.from('${assignableExistingWhereBase64}', 'base64').toString('utf8'))
const normalizeText = (value) =>
String(value == null ? '' : value)
.replace(/\\r/g, ' ')
.replace(/\\n/g, ' ')
.replace(/\\s+/g, ' ')
.trim()
;(async () => {
const payloadCodes = payload.map((item) => normalizeText(item.legacyCode).toUpperCase()).filter(Boolean)
const [existingCodes, assignableExisting, publicAssetPerson] = await Promise.all([
prisma.equipment.findMany({
where: { equipment_code: { in: payloadCodes } },
select: { equipment_code: true, equipment_name: true, status: true },
orderBy: { equipment_code: 'asc' }
}),
prisma.equipment.findMany({
where: assignableExistingWhere,
select: { id: true, equipment_code: true, equipment_name: true, status: true },
orderBy: { equipment_code: 'asc' }
}),
prisma.person.findFirst({
where: { source_userid: publicSeed.source_userid },
select: { id: true, nickname: true, status: true, source_system: true }
})
])
console.log(JSON.stringify({
payloadCount: payload.length,
existingPayloadCodeCount: existingCodes.length,
existingPayloadCodes: existingCodes,
assignableExistingCount: assignableExisting.length,
assignableExistingSample: assignableExisting.slice(0, 30),
publicAssetPerson
}))
await prisma.$disconnect()
})().catch(async (error) => {
console.error(error && error.stack ? error.stack : String(error))
try { await prisma.$disconnect() } catch {}
process.exit(1)
})
`
}
const buildRemoteApplyScript = (payload) => {
const payloadBase64 = Buffer.from(JSON.stringify(payload), 'utf8').toString('base64')
const seedBase64 = Buffer.from(JSON.stringify(DEFAULT_PUBLIC_PERSON), 'utf8').toString('base64')
const assignableExistingWhereBase64 = Buffer.from(
JSON.stringify(
buildAssignableExistingSheet2PublicEquipmentWhere(PUBLIC_ASSET_ASSIGNABLE_EXISTING_STATUSES)
),
'utf8'
).toString('base64')
return `
let PrismaClient
try { ({ PrismaClient } = require('@prisma/client')) } catch { ({ PrismaClient } = require('/app/node_modules/@prisma/client')) }
const prisma = new PrismaClient()
const payload = JSON.parse(Buffer.from('${payloadBase64}', 'base64').toString('utf8'))
const publicSeed = JSON.parse(Buffer.from('${seedBase64}', 'base64').toString('utf8'))
const assignableExistingWhere = JSON.parse(Buffer.from('${assignableExistingWhereBase64}', 'base64').toString('utf8'))
const normalizeText = (value) =>
String(value == null ? '' : value)
.replace(/\\r/g, ' ')
.replace(/\\n/g, ' ')
.replace(/\\s+/g, ' ')
.trim()
const buildBorrowState = (unitPrice) => {
const normalizedUnitPrice = Number(unitPrice || 0)
return {
equipmentStatus: '已借出',
remainingQuantity: 0,
totalValue: normalizedUnitPrice,
borrowedValue: normalizedUnitPrice,
availableValue: 0
}
}
const importResults = []
const skippedExistingImports = []
const assignedExisting = []
;(async () => {
const publicAssetPerson = await prisma.$transaction(async (tx) => {
const person = await tx.person.upsert({
where: { source_userid: publicSeed.source_userid },
create: publicSeed,
update: {
nickname: publicSeed.nickname,
real_name: publicSeed.real_name,
department: null,
main_department_id: null,
position: publicSeed.position,
status: 'active',
remarks: publicSeed.remarks,
source_system: publicSeed.source_system,
source_main_department_id: null,
source_dept_ids: null,
source_department_name: null,
source_main_department_name: null,
department_sync_bucket: null,
manager_source_userid: null,
source_employee_status: null,
last_synced_at: null
}
})
await tx.personDepartment.deleteMany({
where: { person_id: person.id }
})
return person
})
for (const item of payload) {
const legacyCode = normalizeText(item.legacyCode).toUpperCase()
if (!legacyCode) {
continue
}
const existing = await prisma.equipment.findUnique({
where: { equipment_code: legacyCode },
select: { id: true, equipment_code: true, equipment_name: true, status: true }
})
if (existing) {
skippedExistingImports.push(existing)
continue
}
const [assetType, department, location] = await Promise.all([
prisma.assetType.findFirst({
where: { name: normalizeText(item.equipmentType), status: 'active' },
select: { id: true, name: true }
}),
normalizeText(item.departmentName)
? prisma.department.findFirst({
where: { name: normalizeText(item.departmentName), status: 'active' },
select: { id: true, name: true }
})
: Promise.resolve(null),
normalizeText(item.storageLocationName)
? prisma.storageLocation.findFirst({
where: { name: normalizeText(item.storageLocationName), status: 'active' },
select: { id: true, name: true }
})
: Promise.resolve(null)
])
if (!assetType) {
throw new Error('资产类型不存在或未启用: ' + normalizeText(item.equipmentType) + ' code=' + legacyCode)
}
if (normalizeText(item.departmentName) && !department) {
throw new Error('部门不存在或未启用: ' + normalizeText(item.departmentName) + ' code=' + legacyCode)
}
if (normalizeText(item.storageLocationName) && !location) {
throw new Error('地址不存在或未启用: ' + normalizeText(item.storageLocationName) + ' code=' + legacyCode)
}
const unitPrice = Number(item.unitPrice || 0)
const purchaseDate = normalizeText(item.purchaseDate) ? new Date(item.purchaseDate) : null
const borrowTime = purchaseDate || new Date()
const borrowState = buildBorrowState(unitPrice)
const created = await prisma.$transaction(async (tx) => {
const equipment = await tx.equipment.create({
data: {
equipment_code: legacyCode,
equipment_name: normalizeText(item.equipmentName),
equipment_model: normalizeText(item.equipmentModel) || null,
equipment_type: assetType.name,
code_generation_mode: 'manual',
status: borrowState.equipmentStatus,
total_quantity: 1,
remaining_quantity: borrowState.remainingQuantity,
unit_price: unitPrice,
total_value: borrowState.totalValue,
borrowed_value: borrowState.borrowedValue,
available_value: borrowState.availableValue,
total_value_with_children: borrowState.totalValue,
description: normalizeText(item.description) || null,
purchase_date: purchaseDate,
supplier: normalizeText(item.supplier) || null,
storage_location_id: location?.id || null,
asset_type_id: assetType.id,
department_id: department?.id || null,
mount_status: 'none'
}
})
await tx.borrowRecord.create({
data: {
equipment_id: equipment.id,
equipment_code: equipment.equipment_code,
person_id: publicAssetPerson.id,
person_name: publicAssetPerson.real_name,
person_nickname: publicAssetPerson.nickname,
person_identifier: publicAssetPerson.job_number || null,
borrow_time: borrowTime,
expected_return_time: null,
is_long_term: true,
return_time: null,
status: '已借出',
return_condition: '正常',
notes: '总表待导入 Sheet2 公共设备补导;系统挂名到公共资产'
}
})
return {
id: equipment.id,
equipment_code: equipment.equipment_code,
equipment_name: equipment.equipment_name,
status: equipment.status
}
})
importResults.push(created)
}
const existingCandidates = await prisma.equipment.findMany({
where: assignableExistingWhere,
select: {
id: true,
equipment_code: true,
equipment_name: true,
unit_price: true,
purchase_date: true,
status: true
},
orderBy: { equipment_code: 'asc' }
})
for (const equipment of existingCandidates) {
const borrowState = buildBorrowState(equipment.unit_price)
const borrowTime = equipment.purchase_date || new Date()
const assigned = await prisma.$transaction(async (tx) => {
const updatedEquipment = await tx.equipment.update({
where: { id: equipment.id },
data: {
status: borrowState.equipmentStatus,
remaining_quantity: borrowState.remainingQuantity,
total_value: borrowState.totalValue,
borrowed_value: borrowState.borrowedValue,
available_value: borrowState.availableValue,
total_value_with_children: borrowState.totalValue
}
})
await tx.borrowRecord.create({
data: {
equipment_id: updatedEquipment.id,
equipment_code: updatedEquipment.equipment_code,
person_id: publicAssetPerson.id,
person_name: publicAssetPerson.real_name,
person_nickname: publicAssetPerson.nickname,
person_identifier: publicAssetPerson.job_number || null,
borrow_time: borrowTime,
expected_return_time: null,
is_long_term: true,
return_time: null,
status: '已借出',
return_condition: '正常',
notes: '总表待导入 Sheet2 公共设备补挂;系统挂名到公共资产'
}
})
return {
id: updatedEquipment.id,
equipment_code: updatedEquipment.equipment_code,
equipment_name: updatedEquipment.equipment_name,
status: updatedEquipment.status
}
})
assignedExisting.push(assigned)
}
const verification = {
publicAssetPerson: {
id: publicAssetPerson.id,
nickname: publicAssetPerson.nickname,
realName: publicAssetPerson.real_name
},
importedCount: importResults.length,
skippedExistingImportCount: skippedExistingImports.length,
assignedExistingCount: assignedExisting.length,
sheet2PublicEquipmentCount: await prisma.equipment.count({
where: {
AND: [
{ description: { contains: 'source_workbook=docs/总表待导入.xlsx' } },
{ description: { contains: 'source_sheet=Sheet2' } }
]
}
}),
activePublicBorrowCount: await prisma.borrowRecord.count({
where: {
person_id: publicAssetPerson.id,
status: '已借出',
equipment: {
AND: [
{ description: { contains: 'source_workbook=docs/总表待导入.xlsx' } },
{ description: { contains: 'source_sheet=Sheet2' } }
]
}
}
})
}
console.log(JSON.stringify({
publicAssetPerson: verification.publicAssetPerson,
importedCount: importResults.length,
skippedExistingImportCount: skippedExistingImports.length,
assignedExistingCount: assignedExisting.length,
importedSample: importResults.slice(0, 30),
assignedExistingSample: assignedExisting.slice(0, 30),
skippedExistingImportSample: skippedExistingImports.slice(0, 30),
verification
}))
await prisma.$disconnect()
})().catch(async (error) => {
console.error(error && error.stack ? error.stack : String(error))
try { await prisma.$disconnect() } catch {}
process.exit(1)
})
`
}
const run = () => {
const options = parseArgs()
const connection = resolveRemoteConnection(options)
const inputDir = resolveInputDir(options.inputDir)
const payloadPath = path.join(inputDir, 'sheet2_public_ready_payload.json')
if (!fs.existsSync(payloadPath)) {
throw new Error(`未找到 sheet2_public_ready_payload.json: ${payloadPath}`)
}
const payloadDocument = readJson(payloadPath)
const payload = Array.isArray(payloadDocument.payload) ? payloadDocument.payload : []
if (!payload.length) {
throw new Error('可导入 payload 为空')
}
const backupDir = ensureOutputDir(options.dryRun ? 'sheet2_public_preview' : 'sheet2_public_apply')
writeJson(path.join(backupDir, 'planned_payload.json'), {
inputDir,
payloadCount: payload.length,
payload
})
const preview = runRemoteNodeScript(buildRemotePreviewScript(payload), connection)
writeJson(path.join(backupDir, 'preview_result.json'), preview)
if (options.dryRun) {
console.log(JSON.stringify({ backupDir, inputDir, dryRun: true, preview }, null, 2))
return
}
const applyResult = runRemoteNodeScript(buildRemoteApplyScript(payload), connection)
writeJson(path.join(backupDir, 'apply_result.json'), applyResult)
console.log(
JSON.stringify(
{
backupDir,
inputDir,
preview,
applyResult
},
null,
2
)
)
}
if (require.main === module) {
try {
run()
} catch (error) {
console.error(error && error.stack ? error.stack : String(error))
process.exit(1)
}
}
module.exports = {
parseArgs,
resolveRemoteConnection
}