2229 lines
83 KiB
TypeScript
2229 lines
83 KiB
TypeScript
import { randomUUID } from 'crypto'
|
||
import { prisma } from '@/lib/prisma'
|
||
import mssql from 'mssql'
|
||
|
||
type ResolvedEmployeeDepartment = {
|
||
sourceDeptId: string
|
||
departmentName: string | null
|
||
departmentPath: string | null
|
||
isPrimary: boolean
|
||
}
|
||
|
||
type ReportEmployeeRow = {
|
||
dingtalkUserId?: string | null
|
||
dingEmail?: string | null
|
||
jobNumber?: string | null
|
||
employeeDeptIdList?: string | null
|
||
contactName?: string | null
|
||
contactTitle?: string | null
|
||
workPlace?: string | null
|
||
extension?: string | null
|
||
isDingtalkAdmin?: string | number | boolean | null
|
||
isDingtalkBoss?: string | number | boolean | null
|
||
isDingtalkSenior?: string | number | boolean | null
|
||
registerName?: string | null
|
||
flowerName?: string | null
|
||
realName?: string | null
|
||
registerJobNumber?: string | null
|
||
positionTitle?: string | null
|
||
positionLevel?: string | null
|
||
departmentName?: string | null
|
||
departmentPath?: string | null
|
||
registerDeptIds?: string | null
|
||
registerMainDeptId?: string | null
|
||
employmentStatus?: string | null
|
||
managerDingtalkUserId?: string | null
|
||
managerJobNumber?: string | null
|
||
managerName?: string | null
|
||
flowerBookName?: string | null
|
||
flowerBookFlowerName?: string | null
|
||
flowerBookDept?: string | null
|
||
flowerBookDeptId?: string | null
|
||
flowerBookWorkPlace?: string | null
|
||
flowerBookJob?: string | null
|
||
flowerBookJobLevel?: string | null
|
||
flowerBookRole?: string | null
|
||
flowerBookDirector?: string | null
|
||
flowerBookManager?: string | null
|
||
flowerBookEmployeeStatus?: string | null
|
||
groupDepartmentName?: string | null
|
||
groupDepartmentPath?: string | null
|
||
resolvedDepartments?: ResolvedEmployeeDepartment[]
|
||
}
|
||
|
||
type ReportIdentity = {
|
||
dingtalkUserId?: string | null
|
||
email?: string | null
|
||
username?: string | null
|
||
realName?: string | null
|
||
displayName?: string | null
|
||
flowerName?: string | null
|
||
userId?: string | null
|
||
department?: string | null
|
||
}
|
||
|
||
type EmployeeProfileSnapshot = {
|
||
id: string
|
||
dingtalkUserId?: string | null
|
||
dingEmail?: string | null
|
||
jobNumber?: string | null
|
||
flowerName?: string | null
|
||
realName?: string | null
|
||
displayName: string
|
||
departmentName?: string | null
|
||
departmentPath?: string | null
|
||
positionTitle?: string | null
|
||
positionLevel?: string | null
|
||
levelType?: string | null
|
||
isLeadership: boolean
|
||
isDingtalkAdmin?: boolean
|
||
isDingtalkBoss?: boolean
|
||
isDingtalkSenior?: boolean
|
||
managerDingtalkUserId?: string | null
|
||
managerJobNumber?: string | null
|
||
managerName?: string | null
|
||
employmentStatus?: string | null
|
||
workPlace?: string | null
|
||
sourcePayload?: unknown
|
||
syncedAt?: Date | null
|
||
departmentMemberships?: ResolvedEmployeeDepartment[]
|
||
}
|
||
|
||
type TableColumns = Map<string, string>
|
||
|
||
const EMPLOYEE_TABLE = {
|
||
schema: process.env.REPORT_DB_EMPLOYEE_SCHEMA || 'rpt',
|
||
table: process.env.REPORT_DB_EMPLOYEE_TABLE || 'hr_employee',
|
||
}
|
||
|
||
const REGISTER_TABLE = {
|
||
schema: process.env.REPORT_DB_REGISTER_SCHEMA || 'rpt',
|
||
table: process.env.REPORT_DB_REGISTER_TABLE || 'hr_employee_register',
|
||
}
|
||
|
||
const FLOWER_TABLE = {
|
||
schema: process.env.REPORT_DB_FLOWER_NAME_SCHEMA || 'rpt',
|
||
table: process.env.REPORT_DB_FLOWER_NAME_TABLE || 'hr_flowerName_book',
|
||
}
|
||
|
||
const GROUP_TABLE = {
|
||
schema: process.env.REPORT_DB_GROUP_SCHEMA || 'rpt',
|
||
table: process.env.REPORT_DB_GROUP_TABLE || 'hr_groups',
|
||
}
|
||
|
||
const INACTIVE_EMPLOYEE_STATUS_KEYWORDS = ['离职', '停用', '退休']
|
||
|
||
export function getActivePerformanceSurveyEmployeeProfileWhere() {
|
||
return {
|
||
OR: [
|
||
{ employmentStatus: null },
|
||
{
|
||
AND: INACTIVE_EMPLOYEE_STATUS_KEYWORDS.map((keyword) => ({
|
||
employmentStatus: { not: { contains: keyword } },
|
||
})),
|
||
},
|
||
],
|
||
} as any
|
||
}
|
||
|
||
const FIELD_CANDIDATES = {
|
||
dingtalkUserId: ['userid', 'user_id', 'ding_userid', 'dingtalk_userid', 'dingtalk_user_id', 'userId'],
|
||
email: ['email', 'org_email', 'work_email', 'mail', '邮箱'],
|
||
jobNumber: ['job_number', 'jobNumber', 'job_no', 'employee_no', '工号'],
|
||
name: ['name', 'real_name', 'realName', 'username', '姓名'],
|
||
title: ['title', 'position', 'job_title', '岗位'],
|
||
workPlace: ['work_place', 'workPlace', 'workplace', 'office_location', '办公地点'],
|
||
extension: ['extension', 'ext', 'extattr', 'ext_attr'],
|
||
active: ['active', 'is_active', 'enabled'],
|
||
admin: ['admin', 'is_admin', 'administrator'],
|
||
boss: ['boss', 'is_boss'],
|
||
senior: ['senior', 'is_senior'],
|
||
employeeDeptIdList: ['dept_id_list', 'deptIdList', 'department_id_list', '部门id列表'],
|
||
registerName: ['sys00-name', 'name', '姓名'],
|
||
flowerName: ['sys00-flowerName', 'flower_name', 'flowerName', '花名'],
|
||
registerRealName: ['sys02-realName', 'real_name', 'realName', '真实姓名'],
|
||
registerJobNumber: ['sys00-jobNumber', 'job_number', 'jobNumber', '工号'],
|
||
positionTitle: ['sys00-position', 'position', 'title', '岗位'],
|
||
positionLevel: ['sys01-positionLevel', 'position_level', 'positionLevel', '岗位职级', '职级'],
|
||
departmentName: ['sys00-dept', 'department', 'department_name', 'dept_name', '部门'],
|
||
departmentPath: ['sys00-mainDepts', 'department_path', 'main_departments', '部门路径'],
|
||
registerDeptIds: ['sys00-deptIds', 'dept_ids', 'department_ids', '部门ids'],
|
||
registerMainDeptId: ['sys00-mainDeptId', 'main_dept_id', 'mainDepartmentId', '主部门id'],
|
||
employmentStatus: ['sys01-employeeStatus', 'employee_status', 'employment_status', '在职状态'],
|
||
managerDingtalkUserId: ['manager_userid', 'managerUserid', 'leader_userid', '主管userid'],
|
||
managerJobNumber: ['manager_job_number', 'managerJobNumber', 'leader_job_number', '主管工号'],
|
||
managerName: ['manager_name', 'managerName', 'leader_name', '主管'],
|
||
flowerBookJobNumber: ['jobNum', 'job_number', 'jobNumber', '工号'],
|
||
flowerBookName: ['name', '姓名'],
|
||
flowerBookFlowerName: ['flowerName', 'flower_name', '花名'],
|
||
flowerBookDept: ['dept', 'department', 'department_name', '部门'],
|
||
flowerBookDeptId: ['deptId', 'dept_id', 'department_id', '部门id'],
|
||
flowerBookWorkPlace: ['workPlace', 'work_place', '办公地点'],
|
||
flowerBookJob: ['job', 'position', '岗位'],
|
||
flowerBookJobLevel: ['jobLevel', 'job_level', '岗位职级', '职级'],
|
||
flowerBookRole: ['role', '角色'],
|
||
flowerBookDirector: ['director', 'directorName', '主管'],
|
||
flowerBookManager: ['manager', 'managerName', '经理'],
|
||
flowerBookEmployeeStatus: ['empStatus', 'employee_status', '在职状态'],
|
||
flowerBookStatus: ['status'],
|
||
flowerBookLeaveDate: ['leaveDate', 'leave_date', '离职日期'],
|
||
}
|
||
|
||
let dailyWorkerStarted = false
|
||
|
||
function normalizeEmployeeSyncLimit(value: unknown, fallback = 3000) {
|
||
const parsed = Number(value)
|
||
return Math.max(1, Math.min(Number.isFinite(parsed) ? parsed : fallback, 10_000))
|
||
}
|
||
|
||
export function isPerformanceSurveyReportDbConfigured() {
|
||
return Boolean(
|
||
process.env.REPORT_DB_SERVER &&
|
||
process.env.REPORT_DB_DATABASE &&
|
||
process.env.REPORT_DB_USER &&
|
||
process.env.REPORT_DB_PASSWORD,
|
||
)
|
||
}
|
||
|
||
function getReportDbConfig() {
|
||
const server = process.env.REPORT_DB_SERVER
|
||
const database = process.env.REPORT_DB_DATABASE
|
||
const user = process.env.REPORT_DB_USER
|
||
const password = process.env.REPORT_DB_PASSWORD
|
||
const port = Number(process.env.REPORT_DB_PORT || 1433)
|
||
|
||
if (!server || !database || !user || !password) {
|
||
throw new Error('报表库未配置 REPORT_DB_SERVER/REPORT_DB_DATABASE/REPORT_DB_USER/REPORT_DB_PASSWORD')
|
||
}
|
||
|
||
return {
|
||
server,
|
||
database,
|
||
user,
|
||
password,
|
||
port: Number.isFinite(port) ? port : 1433,
|
||
options: {
|
||
encrypt: process.env.REPORT_DB_ENCRYPT === 'true',
|
||
trustServerCertificate: process.env.REPORT_DB_TRUST_SERVER_CERTIFICATE !== 'false',
|
||
enableArithAbort: true,
|
||
},
|
||
pool: {
|
||
max: 4,
|
||
min: 0,
|
||
idleTimeoutMillis: 30_000,
|
||
},
|
||
requestTimeout: 60_000,
|
||
connectionTimeout: 15_000,
|
||
}
|
||
}
|
||
|
||
function clean(value: unknown) {
|
||
const normalized = String(value ?? '').trim()
|
||
return normalized.length > 0 ? normalized : null
|
||
}
|
||
|
||
function normalizeBoolean(value: unknown) {
|
||
if (typeof value === 'boolean') return value
|
||
if (typeof value === 'number') return value === 1
|
||
const normalized = clean(value)?.toLowerCase()
|
||
return normalized === '1'
|
||
|| normalized === 'true'
|
||
|| normalized === 'yes'
|
||
|| normalized === 'y'
|
||
|| normalized === '是'
|
||
}
|
||
|
||
const ACCOUNT_PROFILE_WHERE_CACHE_MS = Math.max(
|
||
5,
|
||
Number(process.env.PERFORMANCE_SURVEY_ACCOUNT_PROFILE_WHERE_CACHE_SECONDS || 60),
|
||
) * 1000
|
||
const PROFILE_BACKGROUND_REFRESH_MIN_MS = Math.max(
|
||
10,
|
||
Number(process.env.PERFORMANCE_SURVEY_PROFILE_BACKGROUND_REFRESH_MIN_SECONDS || 60),
|
||
) * 1000
|
||
const PROFILE_BACKGROUND_REFRESH_RETENTION_MS = Math.max(PROFILE_BACKGROUND_REFRESH_MIN_MS * 10, 10 * 60 * 1000)
|
||
const PROFILE_BACKGROUND_REFRESH_MAX_IN_FLIGHT = 32
|
||
const PROFILE_BACKGROUND_REFRESH_MAX_HISTORY = 1024
|
||
|
||
let accountProfileWhereCache: { expiresAt: number; where: any } | null = null
|
||
let accountProfileWhereBuildPromise: Promise<any> | null = null
|
||
const profileRefreshInFlight = new Map<string, Promise<unknown>>()
|
||
const profileRefreshStartedAt = new Map<string, number>()
|
||
|
||
function pruneProfileRefreshTracking(now = Date.now()) {
|
||
for (const [key, startedAt] of Array.from(profileRefreshStartedAt.entries())) {
|
||
if (!profileRefreshInFlight.has(key) && now - startedAt >= PROFILE_BACKGROUND_REFRESH_RETENTION_MS) {
|
||
profileRefreshStartedAt.delete(key)
|
||
}
|
||
}
|
||
|
||
while (profileRefreshStartedAt.size > PROFILE_BACKGROUND_REFRESH_MAX_HISTORY) {
|
||
const oldestKey = Array.from(profileRefreshStartedAt.keys()).find((key) => !profileRefreshInFlight.has(key))
|
||
if (!oldestKey) break
|
||
profileRefreshStartedAt.delete(oldestKey)
|
||
}
|
||
}
|
||
|
||
export function invalidatePerformanceSurveyAccountProfileWhereCache() {
|
||
accountProfileWhereCache = null
|
||
accountProfileWhereBuildPromise = null
|
||
}
|
||
|
||
function parseExtension(value?: string | null) {
|
||
if (!value) return {}
|
||
try {
|
||
const parsed = JSON.parse(value)
|
||
return parsed && typeof parsed === 'object' && !Array.isArray(parsed)
|
||
? (parsed as Record<string, unknown>)
|
||
: {}
|
||
} catch {
|
||
return {}
|
||
}
|
||
}
|
||
|
||
function parseDepartmentValue(value?: string | null) {
|
||
const parts = parseDepartmentParts(value)
|
||
if (parts.length > 0) return parts[parts.length - 1] || parts[0] || null
|
||
|
||
const normalized = clean(value)
|
||
if (!normalized) return null
|
||
|
||
return normalized
|
||
}
|
||
|
||
function parseDepartmentParts(value?: string | null) {
|
||
const normalized = clean(value)
|
||
if (!normalized) return []
|
||
|
||
if (normalized.startsWith('[')) {
|
||
try {
|
||
const parsed = JSON.parse(normalized)
|
||
if (Array.isArray(parsed)) {
|
||
return parsed.map((item) => clean(item)).filter((item): item is string => Boolean(item))
|
||
}
|
||
} catch {
|
||
// 继续按普通字符串拆分，兼容报表库里偶发的非标准 JSON 文本。
|
||
}
|
||
}
|
||
|
||
return normalized
|
||
.split(/[/>｜|,，;；]/)
|
||
.map((item) => item.trim())
|
||
.filter(Boolean)
|
||
}
|
||
|
||
function getDepartmentParts(row: ReportEmployeeRow) {
|
||
const groupParts = parseDepartmentParts(row.groupDepartmentPath)
|
||
if (groupParts.length > 0) return groupParts
|
||
|
||
const flowerBookParts = parseDepartmentParts(row.flowerBookDept)
|
||
if (flowerBookParts.length > 0) return flowerBookParts
|
||
|
||
const pathParts = parseDepartmentParts(row.departmentPath)
|
||
const nameParts = parseDepartmentParts(row.departmentName)
|
||
if (pathParts.length === 0) return nameParts
|
||
if (nameParts.length === 0) return pathParts
|
||
|
||
const missingNameParts = nameParts.filter((part) => !pathParts.includes(part))
|
||
return missingNameParts.length > 0 ? [...missingNameParts, ...pathParts] : pathParts
|
||
}
|
||
|
||
function joinDepartmentParts(parts: string[]) {
|
||
return parts.length > 0 ? parts.join('/') : null
|
||
}
|
||
|
||
function getDepartmentLeaf(parts: string[]) {
|
||
return parts.length > 0 ? parts[parts.length - 1] || parts[0] || null : null
|
||
}
|
||
|
||
function parseDepartmentIdParts(value?: string | null) {
|
||
const normalized = clean(value)
|
||
if (!normalized) return []
|
||
|
||
if (normalized.startsWith('[')) {
|
||
try {
|
||
const parsed = JSON.parse(normalized)
|
||
if (Array.isArray(parsed)) {
|
||
return parsed.map((item) => clean(item)).filter((item): item is string => Boolean(item))
|
||
}
|
||
} catch {
|
||
// 继续按普通分隔符拆分。
|
||
}
|
||
}
|
||
|
||
return normalized
|
||
.split(/[/>｜|,，;；\s]+/)
|
||
.map((item) => item.trim())
|
||
.filter(Boolean)
|
||
}
|
||
|
||
function normalizeDepartmentId(value: unknown) {
|
||
const normalized = clean(value)
|
||
if (!normalized) return null
|
||
|
||
const numeric = Number(normalized)
|
||
return Number.isInteger(numeric) && numeric > 0 ? String(numeric) : normalized
|
||
}
|
||
|
||
function getFlowerBookDepartmentId(row: ReportEmployeeRow) {
|
||
const ids = parseDepartmentIdParts(row.flowerBookDeptId)
|
||
return normalizeDepartmentId(ids[ids.length - 1])
|
||
}
|
||
|
||
function getPrimaryDepartmentId(row: ReportEmployeeRow) {
|
||
return (
|
||
normalizeDepartmentId(parseDepartmentIdParts(row.registerMainDeptId)[0]) ||
|
||
normalizeDepartmentId(parseDepartmentIdParts(row.registerDeptIds)[0]) ||
|
||
getFlowerBookDepartmentId(row) ||
|
||
normalizeDepartmentId(parseDepartmentIdParts(row.employeeDeptIdList)[0]) ||
|
||
null
|
||
)
|
||
}
|
||
|
||
function getAllDepartmentIds(row: ReportEmployeeRow) {
|
||
const primaryDepartmentId = getPrimaryDepartmentId(row)
|
||
const ids = [
|
||
primaryDepartmentId,
|
||
...parseDepartmentIdParts(row.registerDeptIds).map(normalizeDepartmentId),
|
||
...parseDepartmentIdParts(row.employeeDeptIdList).map(normalizeDepartmentId),
|
||
getFlowerBookDepartmentId(row),
|
||
].filter((id): id is string => Boolean(id))
|
||
|
||
return Array.from(new Set(ids))
|
||
}
|
||
|
||
async function resolveDepartmentPathsByIds(pool: any, ids: string[]) {
|
||
const numericIds = Array.from(
|
||
new Set(
|
||
ids
|
||
.map((id) => Number(id))
|
||
.filter((id) => Number.isInteger(id) && id > 0),
|
||
),
|
||
)
|
||
if (numericIds.length === 0) return new Map<string, string>()
|
||
|
||
const valuesSql = numericIds.map((_, index) => `(@dept${index})`).join(', ')
|
||
const request = pool.request()
|
||
numericIds.forEach((id, index) => request.input(`dept${index}`, id))
|
||
|
||
const result = await request.query(`
|
||
;WITH input_depts(dept_id) AS (
|
||
SELECT value FROM (VALUES ${valuesSql}) AS v(value)
|
||
),
|
||
dept_tree AS (
|
||
SELECT
|
||
input_depts.dept_id,
|
||
g.Id,
|
||
g.parent_id,
|
||
CAST(g.name AS NVARCHAR(MAX)) AS path,
|
||
0 AS depth
|
||
FROM input_depts
|
||
JOIN ${quoteIdentifier(GROUP_TABLE.schema)}.${quoteIdentifier(GROUP_TABLE.table)} g ON g.Id = input_depts.dept_id
|
||
UNION ALL
|
||
SELECT
|
||
dept_tree.dept_id,
|
||
parent.Id,
|
||
parent.parent_id,
|
||
CAST(parent.name + N'/' + dept_tree.path AS NVARCHAR(MAX)) AS path,
|
||
dept_tree.depth + 1 AS depth
|
||
FROM dept_tree
|
||
JOIN ${quoteIdentifier(GROUP_TABLE.schema)}.${quoteIdentifier(GROUP_TABLE.table)} parent ON parent.Id = dept_tree.parent_id
|
||
WHERE dept_tree.depth < 20
|
||
),
|
||
ranked AS (
|
||
SELECT
|
||
dept_id,
|
||
path,
|
||
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY depth DESC) AS rn
|
||
FROM dept_tree
|
||
)
|
||
SELECT dept_id, path
|
||
FROM ranked
|
||
WHERE rn = 1
|
||
OPTION (MAXRECURSION 50)
|
||
`)
|
||
|
||
return new Map(
|
||
(result.recordset || [])
|
||
.map((row: { dept_id: number; path: unknown }) => [
|
||
String(row.dept_id),
|
||
clean(row.path),
|
||
] as const)
|
||
.filter((item: readonly [string, string | null]): item is readonly [string, string] => Boolean(item[1])),
|
||
)
|
||
}
|
||
|
||
async function applyResolvedDepartmentPaths(pool: any, rows: ReportEmployeeRow[]) {
|
||
const ids = rows.flatMap((row) => getAllDepartmentIds(row))
|
||
if (ids.length === 0) return rows
|
||
|
||
const pathById = await resolveDepartmentPathsByIds(pool, ids)
|
||
return rows.map((row) => {
|
||
const primaryDepartmentId = getPrimaryDepartmentId(row)
|
||
const fallbackParts = getDepartmentParts(row)
|
||
const fallbackPath = joinDepartmentParts(fallbackParts)
|
||
const fallbackName = getDepartmentLeaf(fallbackParts)
|
||
const resolvedDepartments = getAllDepartmentIds(row).map((sourceDeptId) => {
|
||
const resolvedPath = clean(pathById.get(sourceDeptId))
|
||
const isPrimary = sourceDeptId === primaryDepartmentId
|
||
const departmentPath = resolvedPath || (isPrimary ? fallbackPath : null)
|
||
const parts = parseDepartmentParts(departmentPath)
|
||
return {
|
||
sourceDeptId,
|
||
departmentName: getDepartmentLeaf(parts) || (isPrimary ? fallbackName : null),
|
||
departmentPath,
|
||
isPrimary,
|
||
}
|
||
})
|
||
const primaryDepartment = resolvedDepartments.find((item) => item.isPrimary)
|
||
if (!primaryDepartment?.departmentPath) {
|
||
return {
|
||
...row,
|
||
resolvedDepartments,
|
||
}
|
||
}
|
||
|
||
return {
|
||
...row,
|
||
groupDepartmentName: primaryDepartment.departmentName,
|
||
groupDepartmentPath: primaryDepartment.departmentPath,
|
||
resolvedDepartments,
|
||
}
|
||
})
|
||
}
|
||
|
||
function getLevelType(positionLevel?: string | null) {
|
||
const first = clean(positionLevel)?.slice(0, 1).toUpperCase()
|
||
if (first === 'P' || first === 'M') return first
|
||
return null
|
||
}
|
||
|
||
function isLeadershipLevel(positionLevel?: string | null) {
|
||
return getLevelType(positionLevel) === 'M'
|
||
}
|
||
|
||
function quoteIdentifier(value: string) {
|
||
return `[${value.replace(/]/g, ']]')}]`
|
||
}
|
||
|
||
function pickColumn(columns: TableColumns, candidates: string[]) {
|
||
for (const candidate of candidates) {
|
||
const matched = columns.get(candidate.toLowerCase())
|
||
if (matched) return matched
|
||
}
|
||
return null
|
||
}
|
||
|
||
function columnExpr(alias: string, column: string | null, cast = 'nvarchar(4000)') {
|
||
return column ? `${alias}.${quoteIdentifier(column)}` : `CAST(NULL AS ${cast})`
|
||
}
|
||
|
||
function eqCondition(alias: string, column: string | null, paramName: string) {
|
||
return column ? `${alias}.${quoteIdentifier(column)} = @${paramName}` : null
|
||
}
|
||
|
||
function notInactiveStatusCondition(alias: string, column: string | null) {
|
||
if (!column) return null
|
||
const expr = `${alias}.${quoteIdentifier(column)}`
|
||
return `(${expr} IS NULL OR ${expr} NOT LIKE N'%离职%')`
|
||
}
|
||
|
||
function emptyDateCondition(alias: string, column: string | null) {
|
||
return column ? `${alias}.${quoteIdentifier(column)} IS NULL` : null
|
||
}
|
||
|
||
function getFlowerBookActiveConditions(
|
||
alias: string,
|
||
flowerColumns: {
|
||
status?: string | null
|
||
employeeStatus?: string | null
|
||
leaveDate?: string | null
|
||
},
|
||
) {
|
||
return [
|
||
notInactiveStatusCondition(alias, flowerColumns.status || null),
|
||
notInactiveStatusCondition(alias, flowerColumns.employeeStatus || null),
|
||
emptyDateCondition(alias, flowerColumns.leaveDate || null),
|
||
].filter((item): item is string => Boolean(item))
|
||
}
|
||
|
||
async function getTableColumns(pool: any, schema: string, table: string): Promise<TableColumns> {
|
||
const result = await pool
|
||
.request()
|
||
.input('schema', schema)
|
||
.input('table', table)
|
||
.query(`
|
||
SELECT COLUMN_NAME
|
||
FROM INFORMATION_SCHEMA.COLUMNS
|
||
WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @table
|
||
`)
|
||
|
||
return new Map(
|
||
((result.recordset || []) as Array<{ COLUMN_NAME: string }>).map((row) => [
|
||
String(row.COLUMN_NAME).toLowerCase(),
|
||
row.COLUMN_NAME,
|
||
]),
|
||
)
|
||
}
|
||
|
||
async function getReportSchema(pool: any) {
|
||
const employeeColumns = await getTableColumns(pool, EMPLOYEE_TABLE.schema, EMPLOYEE_TABLE.table)
|
||
const registerColumns = await getTableColumns(pool, REGISTER_TABLE.schema, REGISTER_TABLE.table)
|
||
const flowerColumns = await getTableColumns(pool, FLOWER_TABLE.schema, FLOWER_TABLE.table)
|
||
|
||
if (employeeColumns.size === 0) {
|
||
throw new Error(`报表库未找到人员表 ${EMPLOYEE_TABLE.schema}.${EMPLOYEE_TABLE.table}`)
|
||
}
|
||
|
||
return { employeeColumns, registerColumns, flowerColumns }
|
||
}
|
||
|
||
function buildJoinCondition(employeeColumns: TableColumns, registerColumns: TableColumns) {
|
||
const employeeUserId = pickColumn(employeeColumns, FIELD_CANDIDATES.dingtalkUserId)
|
||
const registerUserId = pickColumn(registerColumns, FIELD_CANDIDATES.dingtalkUserId)
|
||
const employeeJobNumber = pickColumn(employeeColumns, FIELD_CANDIDATES.jobNumber)
|
||
const registerJobNumber = pickColumn(registerColumns, FIELD_CANDIDATES.registerJobNumber)
|
||
const conditions = [
|
||
employeeUserId && registerUserId
|
||
? `r.${quoteIdentifier(registerUserId)} = e.${quoteIdentifier(employeeUserId)}`
|
||
: null,
|
||
employeeJobNumber && registerJobNumber
|
||
? `r.${quoteIdentifier(registerJobNumber)} = e.${quoteIdentifier(employeeJobNumber)}`
|
||
: null,
|
||
].filter(Boolean)
|
||
|
||
return conditions.length > 0 ? conditions.join(' OR ') : '1 = 0'
|
||
}
|
||
|
||
function buildSelectSql(
|
||
employeeColumns: TableColumns,
|
||
registerColumns: TableColumns,
|
||
flowerColumns: TableColumns,
|
||
whereSql: string,
|
||
limitSql = '@limit',
|
||
) {
|
||
const e = {
|
||
dingtalkUserId: pickColumn(employeeColumns, FIELD_CANDIDATES.dingtalkUserId),
|
||
email: pickColumn(employeeColumns, FIELD_CANDIDATES.email),
|
||
jobNumber: pickColumn(employeeColumns, FIELD_CANDIDATES.jobNumber),
|
||
name: pickColumn(employeeColumns, FIELD_CANDIDATES.name),
|
||
title: pickColumn(employeeColumns, FIELD_CANDIDATES.title),
|
||
workPlace: pickColumn(employeeColumns, FIELD_CANDIDATES.workPlace),
|
||
extension: pickColumn(employeeColumns, FIELD_CANDIDATES.extension),
|
||
active: pickColumn(employeeColumns, FIELD_CANDIDATES.active),
|
||
admin: pickColumn(employeeColumns, FIELD_CANDIDATES.admin),
|
||
boss: pickColumn(employeeColumns, FIELD_CANDIDATES.boss),
|
||
senior: pickColumn(employeeColumns, FIELD_CANDIDATES.senior),
|
||
deptIdList: pickColumn(employeeColumns, FIELD_CANDIDATES.employeeDeptIdList),
|
||
managerDingtalkUserId: pickColumn(employeeColumns, FIELD_CANDIDATES.managerDingtalkUserId),
|
||
managerJobNumber: pickColumn(employeeColumns, FIELD_CANDIDATES.managerJobNumber),
|
||
managerName: pickColumn(employeeColumns, FIELD_CANDIDATES.managerName),
|
||
}
|
||
const r = {
|
||
dingtalkUserId: pickColumn(registerColumns, FIELD_CANDIDATES.dingtalkUserId),
|
||
email: pickColumn(registerColumns, FIELD_CANDIDATES.email),
|
||
registerName: pickColumn(registerColumns, FIELD_CANDIDATES.registerName),
|
||
flowerName: pickColumn(registerColumns, FIELD_CANDIDATES.flowerName),
|
||
realName: pickColumn(registerColumns, FIELD_CANDIDATES.registerRealName),
|
||
jobNumber: pickColumn(registerColumns, FIELD_CANDIDATES.registerJobNumber),
|
||
positionTitle: pickColumn(registerColumns, FIELD_CANDIDATES.positionTitle),
|
||
positionLevel: pickColumn(registerColumns, FIELD_CANDIDATES.positionLevel),
|
||
departmentName: pickColumn(registerColumns, FIELD_CANDIDATES.departmentName),
|
||
departmentPath: pickColumn(registerColumns, FIELD_CANDIDATES.departmentPath),
|
||
deptIds: pickColumn(registerColumns, FIELD_CANDIDATES.registerDeptIds),
|
||
mainDeptId: pickColumn(registerColumns, FIELD_CANDIDATES.registerMainDeptId),
|
||
employmentStatus: pickColumn(registerColumns, FIELD_CANDIDATES.employmentStatus),
|
||
managerDingtalkUserId: pickColumn(registerColumns, FIELD_CANDIDATES.managerDingtalkUserId),
|
||
managerJobNumber: pickColumn(registerColumns, FIELD_CANDIDATES.managerJobNumber),
|
||
managerName: pickColumn(registerColumns, FIELD_CANDIDATES.managerName),
|
||
}
|
||
const f = {
|
||
jobNumber: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookJobNumber),
|
||
name: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookName),
|
||
flowerName: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookFlowerName),
|
||
dept: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookDept),
|
||
deptId: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookDeptId),
|
||
workPlace: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookWorkPlace),
|
||
job: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookJob),
|
||
jobLevel: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookJobLevel),
|
||
role: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookRole),
|
||
director: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookDirector),
|
||
manager: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookManager),
|
||
employeeStatus: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookEmployeeStatus),
|
||
status: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookStatus),
|
||
leaveDate: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookLeaveDate),
|
||
}
|
||
const activeFilter = e.active ? `AND (e.${quoteIdentifier(e.active)} = 1 OR e.${quoteIdentifier(e.active)} = '1')` : ''
|
||
const joinSql = registerColumns.size > 0
|
||
? `LEFT JOIN ${quoteIdentifier(REGISTER_TABLE.schema)}.${quoteIdentifier(REGISTER_TABLE.table)} r ON ${buildJoinCondition(employeeColumns, registerColumns)}`
|
||
: ''
|
||
const flowerJoinConditions = [
|
||
f.jobNumber && r.jobNumber ? `f.${quoteIdentifier(f.jobNumber)} = r.${quoteIdentifier(r.jobNumber)}` : null,
|
||
f.jobNumber && e.jobNumber ? `f.${quoteIdentifier(f.jobNumber)} = e.${quoteIdentifier(e.jobNumber)}` : null,
|
||
f.flowerName && r.flowerName ? `f.${quoteIdentifier(f.flowerName)} = r.${quoteIdentifier(r.flowerName)}` : null,
|
||
].filter(Boolean)
|
||
const flowerJoinSql = flowerColumns.size > 0 && flowerJoinConditions.length > 0
|
||
? `LEFT JOIN ${quoteIdentifier(FLOWER_TABLE.schema)}.${quoteIdentifier(FLOWER_TABLE.table)} f ON (${flowerJoinConditions.join(' OR ')})${getFlowerBookActiveConditions('f', f).map((condition) => ` AND ${condition}`).join('')}`
|
||
: ''
|
||
|
||
return `
|
||
SELECT TOP (${limitSql})
|
||
COALESCE(${columnExpr('e', e.dingtalkUserId)}, ${columnExpr('r', r.dingtalkUserId)}) AS "dingtalkUserId",
|
||
COALESCE(${columnExpr('e', e.email)}, ${columnExpr('r', r.email)}) AS "dingEmail",
|
||
${columnExpr('e', e.jobNumber)} AS "jobNumber",
|
||
${columnExpr('e', e.name)} AS "contactName",
|
||
${columnExpr('e', e.title)} AS "contactTitle",
|
||
${columnExpr('e', e.workPlace)} AS "workPlace",
|
||
${columnExpr('e', e.extension)} AS "extension",
|
||
${columnExpr('e', e.admin, 'bit')} AS "isDingtalkAdmin",
|
||
${columnExpr('e', e.boss, 'bit')} AS "isDingtalkBoss",
|
||
${columnExpr('e', e.senior, 'bit')} AS "isDingtalkSenior",
|
||
${columnExpr('e', e.deptIdList)} AS "employeeDeptIdList",
|
||
${columnExpr('r', r.registerName)} AS "registerName",
|
||
${columnExpr('r', r.flowerName)} AS "flowerName",
|
||
${columnExpr('r', r.realName)} AS "realName",
|
||
${columnExpr('r', r.jobNumber)} AS "registerJobNumber",
|
||
${columnExpr('r', r.positionTitle)} AS "positionTitle",
|
||
${columnExpr('r', r.positionLevel)} AS "positionLevel",
|
||
${columnExpr('r', r.departmentName)} AS "departmentName",
|
||
${columnExpr('r', r.departmentPath)} AS "departmentPath",
|
||
${columnExpr('r', r.deptIds)} AS "registerDeptIds",
|
||
${columnExpr('r', r.mainDeptId)} AS "registerMainDeptId",
|
||
${columnExpr('r', r.employmentStatus)} AS "employmentStatus",
|
||
COALESCE(${columnExpr('r', r.managerDingtalkUserId)}, ${columnExpr('e', e.managerDingtalkUserId)}) AS "managerDingtalkUserId",
|
||
COALESCE(${columnExpr('r', r.managerJobNumber)}, ${columnExpr('e', e.managerJobNumber)}) AS "managerJobNumber",
|
||
COALESCE(${columnExpr('r', r.managerName)}, ${columnExpr('e', e.managerName)}) AS "managerName",
|
||
${columnExpr('f', f.name)} AS "flowerBookName",
|
||
${columnExpr('f', f.flowerName)} AS "flowerBookFlowerName",
|
||
${columnExpr('f', f.dept)} AS "flowerBookDept",
|
||
${columnExpr('f', f.deptId)} AS "flowerBookDeptId",
|
||
${columnExpr('f', f.workPlace)} AS "flowerBookWorkPlace",
|
||
${columnExpr('f', f.job)} AS "flowerBookJob",
|
||
${columnExpr('f', f.jobLevel)} AS "flowerBookJobLevel",
|
||
${columnExpr('f', f.role)} AS "flowerBookRole",
|
||
${columnExpr('f', f.director)} AS "flowerBookDirector",
|
||
${columnExpr('f', f.manager)} AS "flowerBookManager",
|
||
${columnExpr('f', f.employeeStatus)} AS "flowerBookEmployeeStatus"
|
||
FROM ${quoteIdentifier(EMPLOYEE_TABLE.schema)}.${quoteIdentifier(EMPLOYEE_TABLE.table)} e
|
||
${joinSql}
|
||
${flowerJoinSql}
|
||
WHERE ${whereSql}
|
||
${activeFilter}
|
||
ORDER BY COALESCE(${columnExpr('r', r.flowerName)}, ${columnExpr('r', r.registerName)}, ${columnExpr('e', e.name)}, ${columnExpr('e', e.jobNumber)}) ASC
|
||
`
|
||
}
|
||
|
||
function buildEmployeeListSql(employeeColumns: TableColumns, whereSql?: string) {
|
||
const e = {
|
||
dingtalkUserId: pickColumn(employeeColumns, FIELD_CANDIDATES.dingtalkUserId),
|
||
email: pickColumn(employeeColumns, FIELD_CANDIDATES.email),
|
||
jobNumber: pickColumn(employeeColumns, FIELD_CANDIDATES.jobNumber),
|
||
name: pickColumn(employeeColumns, FIELD_CANDIDATES.name),
|
||
title: pickColumn(employeeColumns, FIELD_CANDIDATES.title),
|
||
workPlace: pickColumn(employeeColumns, FIELD_CANDIDATES.workPlace),
|
||
extension: pickColumn(employeeColumns, FIELD_CANDIDATES.extension),
|
||
active: pickColumn(employeeColumns, FIELD_CANDIDATES.active),
|
||
admin: pickColumn(employeeColumns, FIELD_CANDIDATES.admin),
|
||
boss: pickColumn(employeeColumns, FIELD_CANDIDATES.boss),
|
||
senior: pickColumn(employeeColumns, FIELD_CANDIDATES.senior),
|
||
deptIdList: pickColumn(employeeColumns, FIELD_CANDIDATES.employeeDeptIdList),
|
||
managerDingtalkUserId: pickColumn(employeeColumns, FIELD_CANDIDATES.managerDingtalkUserId),
|
||
managerJobNumber: pickColumn(employeeColumns, FIELD_CANDIDATES.managerJobNumber),
|
||
managerName: pickColumn(employeeColumns, FIELD_CANDIDATES.managerName),
|
||
}
|
||
const whereParts = [
|
||
e.active ? `(e.${quoteIdentifier(e.active)} = 1 OR e.${quoteIdentifier(e.active)} = '1')` : null,
|
||
whereSql ? `(${whereSql})` : null,
|
||
].filter(Boolean)
|
||
const whereClause = whereParts.length > 0 ? `WHERE ${whereParts.join(' AND ')}` : ''
|
||
|
||
return `
|
||
SELECT TOP (@limit)
|
||
${columnExpr('e', e.dingtalkUserId)} AS "dingtalkUserId",
|
||
${columnExpr('e', e.email)} AS "dingEmail",
|
||
${columnExpr('e', e.jobNumber)} AS "jobNumber",
|
||
${columnExpr('e', e.name)} AS "contactName",
|
||
${columnExpr('e', e.title)} AS "contactTitle",
|
||
${columnExpr('e', e.workPlace)} AS "workPlace",
|
||
${columnExpr('e', e.extension)} AS "extension",
|
||
${columnExpr('e', e.admin, 'bit')} AS "isDingtalkAdmin",
|
||
${columnExpr('e', e.boss, 'bit')} AS "isDingtalkBoss",
|
||
${columnExpr('e', e.senior, 'bit')} AS "isDingtalkSenior",
|
||
${columnExpr('e', e.deptIdList)} AS "employeeDeptIdList",
|
||
${columnExpr('e', e.managerDingtalkUserId)} AS "managerDingtalkUserId",
|
||
${columnExpr('e', e.managerJobNumber)} AS "managerJobNumber",
|
||
${columnExpr('e', e.managerName)} AS "managerName"
|
||
FROM ${quoteIdentifier(EMPLOYEE_TABLE.schema)}.${quoteIdentifier(EMPLOYEE_TABLE.table)} e
|
||
${whereClause}
|
||
ORDER BY COALESCE(${columnExpr('e', e.name)}, ${columnExpr('e', e.jobNumber)}, ${columnExpr('e', e.dingtalkUserId)}) ASC
|
||
`
|
||
}
|
||
|
||
function buildRegisterListSql(registerColumns: TableColumns, whereSql?: string) {
|
||
const r = {
|
||
dingtalkUserId: pickColumn(registerColumns, FIELD_CANDIDATES.dingtalkUserId),
|
||
email: pickColumn(registerColumns, FIELD_CANDIDATES.email),
|
||
registerName: pickColumn(registerColumns, FIELD_CANDIDATES.registerName),
|
||
flowerName: pickColumn(registerColumns, FIELD_CANDIDATES.flowerName),
|
||
realName: pickColumn(registerColumns, FIELD_CANDIDATES.registerRealName),
|
||
jobNumber: pickColumn(registerColumns, FIELD_CANDIDATES.registerJobNumber),
|
||
positionTitle: pickColumn(registerColumns, FIELD_CANDIDATES.positionTitle),
|
||
positionLevel: pickColumn(registerColumns, FIELD_CANDIDATES.positionLevel),
|
||
departmentName: pickColumn(registerColumns, FIELD_CANDIDATES.departmentName),
|
||
departmentPath: pickColumn(registerColumns, FIELD_CANDIDATES.departmentPath),
|
||
deptIds: pickColumn(registerColumns, FIELD_CANDIDATES.registerDeptIds),
|
||
mainDeptId: pickColumn(registerColumns, FIELD_CANDIDATES.registerMainDeptId),
|
||
employmentStatus: pickColumn(registerColumns, FIELD_CANDIDATES.employmentStatus),
|
||
managerDingtalkUserId: pickColumn(registerColumns, FIELD_CANDIDATES.managerDingtalkUserId),
|
||
managerJobNumber: pickColumn(registerColumns, FIELD_CANDIDATES.managerJobNumber),
|
||
managerName: pickColumn(registerColumns, FIELD_CANDIDATES.managerName),
|
||
}
|
||
|
||
const whereParts = [
|
||
notInactiveStatusCondition('r', r.employmentStatus),
|
||
whereSql ? `(${whereSql})` : null,
|
||
].filter(Boolean)
|
||
const whereClause = whereParts.length > 0 ? `WHERE ${whereParts.join(' AND ')}` : ''
|
||
|
||
return `
|
||
SELECT TOP (@limit)
|
||
${columnExpr('r', r.dingtalkUserId)} AS "dingtalkUserId",
|
||
${columnExpr('r', r.email)} AS "dingEmail",
|
||
${columnExpr('r', r.registerName)} AS "registerName",
|
||
${columnExpr('r', r.flowerName)} AS "flowerName",
|
||
${columnExpr('r', r.realName)} AS "realName",
|
||
${columnExpr('r', r.jobNumber)} AS "registerJobNumber",
|
||
${columnExpr('r', r.positionTitle)} AS "positionTitle",
|
||
${columnExpr('r', r.positionLevel)} AS "positionLevel",
|
||
${columnExpr('r', r.departmentName)} AS "departmentName",
|
||
${columnExpr('r', r.departmentPath)} AS "departmentPath",
|
||
${columnExpr('r', r.deptIds)} AS "registerDeptIds",
|
||
${columnExpr('r', r.mainDeptId)} AS "registerMainDeptId",
|
||
${columnExpr('r', r.employmentStatus)} AS "employmentStatus",
|
||
${columnExpr('r', r.managerDingtalkUserId)} AS "managerDingtalkUserId",
|
||
${columnExpr('r', r.managerJobNumber)} AS "managerJobNumber",
|
||
${columnExpr('r', r.managerName)} AS "managerName"
|
||
FROM ${quoteIdentifier(REGISTER_TABLE.schema)}.${quoteIdentifier(REGISTER_TABLE.table)} r
|
||
${whereClause}
|
||
ORDER BY COALESCE(${columnExpr('r', r.flowerName)}, ${columnExpr('r', r.registerName)}, ${columnExpr('r', r.jobNumber)}, ${columnExpr('r', r.dingtalkUserId)}) ASC
|
||
`
|
||
}
|
||
|
||
function buildFlowerListSql(flowerColumns: TableColumns, whereSql?: string) {
|
||
const f = {
|
||
jobNumber: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookJobNumber),
|
||
name: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookName),
|
||
flowerName: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookFlowerName),
|
||
dept: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookDept),
|
||
deptId: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookDeptId),
|
||
workPlace: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookWorkPlace),
|
||
job: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookJob),
|
||
jobLevel: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookJobLevel),
|
||
role: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookRole),
|
||
director: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookDirector),
|
||
manager: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookManager),
|
||
employeeStatus: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookEmployeeStatus),
|
||
status: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookStatus),
|
||
leaveDate: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookLeaveDate),
|
||
}
|
||
const whereParts = [
|
||
...getFlowerBookActiveConditions('f', f),
|
||
whereSql ? `(${whereSql})` : null,
|
||
].filter(Boolean)
|
||
const whereClause = whereParts.length > 0 ? `WHERE ${whereParts.join(' AND ')}` : ''
|
||
|
||
return `
|
||
SELECT TOP (@limit)
|
||
${columnExpr('f', f.jobNumber)} AS "registerJobNumber",
|
||
${columnExpr('f', f.name)} AS "flowerBookName",
|
||
${columnExpr('f', f.flowerName)} AS "flowerBookFlowerName",
|
||
${columnExpr('f', f.dept)} AS "flowerBookDept",
|
||
${columnExpr('f', f.deptId)} AS "flowerBookDeptId",
|
||
${columnExpr('f', f.workPlace)} AS "flowerBookWorkPlace",
|
||
${columnExpr('f', f.job)} AS "flowerBookJob",
|
||
${columnExpr('f', f.jobLevel)} AS "flowerBookJobLevel",
|
||
${columnExpr('f', f.role)} AS "flowerBookRole",
|
||
${columnExpr('f', f.director)} AS "flowerBookDirector",
|
||
${columnExpr('f', f.manager)} AS "flowerBookManager",
|
||
${columnExpr('f', f.employeeStatus)} AS "flowerBookEmployeeStatus"
|
||
FROM ${quoteIdentifier(FLOWER_TABLE.schema)}.${quoteIdentifier(FLOWER_TABLE.table)} f
|
||
${whereClause}
|
||
ORDER BY COALESCE(${columnExpr('f', f.flowerName)}, ${columnExpr('f', f.name)}, ${columnExpr('f', f.jobNumber)}) ASC
|
||
`
|
||
}
|
||
|
||
function normalizedKey(value: unknown) {
|
||
return clean(value)?.toLowerCase() || null
|
||
}
|
||
|
||
function addToIndex<T>(index: Map<string, T>, key: unknown, value: T) {
|
||
const normalized = normalizedKey(key)
|
||
if (normalized && !index.has(normalized)) {
|
||
index.set(normalized, value)
|
||
}
|
||
}
|
||
|
||
function indexRows(rows: ReportEmployeeRow[], keys: Array<keyof ReportEmployeeRow>) {
|
||
const index = new Map<string, ReportEmployeeRow>()
|
||
for (const row of rows) {
|
||
for (const key of keys) {
|
||
addToIndex(index, row[key], row)
|
||
}
|
||
}
|
||
return index
|
||
}
|
||
|
||
function getSyncableUserWhere() {
|
||
return {
|
||
archivedAt: null,
|
||
NOT: [
|
||
{ status: 'archived' },
|
||
{ status: 'suspended' },
|
||
],
|
||
} as any
|
||
}
|
||
|
||
async function listMaterialAccountSyncIdentities(limit?: number) {
|
||
const users = await prisma.user.findMany({
|
||
where: getSyncableUserWhere(),
|
||
select: {
|
||
id: true,
|
||
email: true,
|
||
username: true,
|
||
realName: true,
|
||
dingtalkUserId: true,
|
||
department: true,
|
||
},
|
||
orderBy: [{ updatedAt: 'desc' }, { createdAt: 'desc' }],
|
||
...(limit ? { take: limit } : {}),
|
||
})
|
||
|
||
return users.map((user) => ({
|
||
userId: user.id,
|
||
email: user.email,
|
||
username: user.username,
|
||
realName: user.realName,
|
||
displayName: clean(user.realName) || clean(user.username),
|
||
flowerName: clean(user.realName) || clean(user.username),
|
||
dingtalkUserId: user.dingtalkUserId,
|
||
department: user.department,
|
||
}))
|
||
}
|
||
|
||
async function countMaterialAccountSyncIdentities() {
|
||
return prisma.user.count({ where: getSyncableUserWhere() })
|
||
}
|
||
|
||
export async function getPerformanceSurveyAccountProfileWhere() {
|
||
if (accountProfileWhereCache && accountProfileWhereCache.expiresAt > Date.now()) {
|
||
return accountProfileWhereCache.where
|
||
}
|
||
if (accountProfileWhereBuildPromise) {
|
||
return accountProfileWhereBuildPromise
|
||
}
|
||
|
||
accountProfileWhereBuildPromise = buildPerformanceSurveyAccountProfileWhere()
|
||
.then((where) => {
|
||
accountProfileWhereCache = {
|
||
where,
|
||
expiresAt: Date.now() + ACCOUNT_PROFILE_WHERE_CACHE_MS,
|
||
}
|
||
return where
|
||
})
|
||
.finally(() => {
|
||
accountProfileWhereBuildPromise = null
|
||
})
|
||
|
||
return accountProfileWhereBuildPromise
|
||
}
|
||
|
||
async function buildPerformanceSurveyAccountProfileWhere() {
|
||
const identities = await listMaterialAccountSyncIdentities()
|
||
const userIds = Array.from(new Set(identities.map((item) => clean(item.userId)).filter(Boolean))) as string[]
|
||
const dingtalkUserIds = Array.from(new Set(identities.map((item) => clean(item.dingtalkUserId)).filter(Boolean))) as string[]
|
||
const emails = Array.from(new Set(identities.map((item) => clean(item.email)).filter(Boolean))) as string[]
|
||
|
||
const or: any[] = []
|
||
if (dingtalkUserIds.length > 0) {
|
||
or.push({ dingtalkUserId: { in: dingtalkUserIds } })
|
||
}
|
||
if (emails.length > 0) {
|
||
or.push({ dingEmail: { in: emails } })
|
||
}
|
||
if (userIds.length > 0) {
|
||
for (const userId of userIds) {
|
||
or.push({ sourcePayload: { path: ['materialUserId'], equals: userId } })
|
||
or.push({ sourcePayload: { path: ['userId'], equals: userId } })
|
||
}
|
||
}
|
||
|
||
const accountWhere = or.length > 0 ? { OR: or } : { id: { in: [] } }
|
||
return { AND: [accountWhere, getActivePerformanceSurveyEmployeeProfileWhere()] }
|
||
}
|
||
|
||
function uniqueClean(values: unknown[]) {
|
||
return Array.from(new Set(values.map((item) => clean(item)).filter((item): item is string => Boolean(item))))
|
||
}
|
||
|
||
function getIdentityNames(identity: ReportIdentity) {
|
||
return uniqueClean([identity.flowerName, identity.displayName, identity.realName, identity.username])
|
||
}
|
||
|
||
function getIdentityMatchKeys(identity: ReportIdentity) {
|
||
return [
|
||
...uniqueClean([identity.dingtalkUserId]).map((item) => `ding:${item.toLowerCase()}`),
|
||
...uniqueClean([identity.email]).map((item) => `email:${item.toLowerCase()}`),
|
||
...getIdentityNames(identity).map((item) => `name:${item.toLowerCase()}`),
|
||
]
|
||
}
|
||
|
||
function getNormalizedProfileMatchKeys(profile: ReturnType<typeof normalizeEmployeeRow>) {
|
||
return [
|
||
...uniqueClean([profile.dingtalkUserId]).map((item) => `ding:${item.toLowerCase()}`),
|
||
...uniqueClean([profile.dingEmail]).map((item) => `email:${item.toLowerCase()}`),
|
||
...uniqueClean([profile.displayName, profile.flowerName, profile.realName]).map((item) => `name:${item.toLowerCase()}`),
|
||
]
|
||
}
|
||
|
||
function getIdentityProfileMatchScore(identity: ReportIdentity, profile: ReturnType<typeof normalizeEmployeeRow>) {
|
||
const identityDing = clean(identity.dingtalkUserId)?.toLowerCase()
|
||
const profileDing = clean(profile.dingtalkUserId)?.toLowerCase()
|
||
if (identityDing && profileDing && identityDing === profileDing) return 3
|
||
|
||
const identityEmail = clean(identity.email)?.toLowerCase()
|
||
const profileEmail = clean(profile.dingEmail)?.toLowerCase()
|
||
if (identityEmail && profileEmail && identityEmail === profileEmail) return 2
|
||
|
||
const profileNames = new Set(uniqueClean([profile.displayName, profile.flowerName, profile.realName]).map((item) => item.toLowerCase()))
|
||
return getIdentityNames(identity).some((name) => profileNames.has(name.toLowerCase())) ? 1 : 0
|
||
}
|
||
|
||
function getIndexed(index: Map<string, ReportEmployeeRow>, ...keys: unknown[]) {
|
||
for (const key of keys) {
|
||
const normalized = normalizedKey(key)
|
||
if (!normalized) continue
|
||
const matched = index.get(normalized)
|
||
if (matched) return matched
|
||
}
|
||
return null
|
||
}
|
||
|
||
function mergeReportRows(...rows: Array<ReportEmployeeRow | null | undefined>): ReportEmployeeRow {
|
||
const merged: ReportEmployeeRow = {}
|
||
for (const row of rows) {
|
||
if (!row) continue
|
||
for (const [key, value] of Object.entries(row) as Array<[keyof ReportEmployeeRow, unknown]>) {
|
||
if (clean(merged[key]) || clean(value) === null) continue
|
||
;(merged as Record<string, unknown>)[key] = value
|
||
}
|
||
}
|
||
return merged
|
||
}
|
||
|
||
async function queryReportRows(
|
||
pool: any,
|
||
sql: typeof import('mssql'),
|
||
query: string,
|
||
limit: number,
|
||
bindInputs?: (request: any) => void,
|
||
): Promise<ReportEmployeeRow[]> {
|
||
const request = pool.request().input('limit', sql.Int, limit)
|
||
bindInputs?.(request)
|
||
const result = await request.query(query)
|
||
return (result.recordset || []) as ReportEmployeeRow[]
|
||
}
|
||
|
||
async function fetchFullSyncRows(
|
||
pool: any,
|
||
sql: typeof import('mssql'),
|
||
schema: Awaited<ReturnType<typeof getReportSchema>>,
|
||
limit: number,
|
||
) {
|
||
const employeeRows = await queryReportRows(pool, sql, buildEmployeeListSql(schema.employeeColumns), limit)
|
||
const registerRows = schema.registerColumns.size > 0
|
||
? await queryReportRows(pool, sql, buildRegisterListSql(schema.registerColumns), limit)
|
||
: []
|
||
const flowerRows = schema.flowerColumns.size > 0
|
||
? await queryReportRows(pool, sql, buildFlowerListSql(schema.flowerColumns), Math.max(limit, Math.min(limit * 3, 10_000)))
|
||
: []
|
||
|
||
const registerByUserId = indexRows(registerRows, ['dingtalkUserId'])
|
||
const registerByJobNumber = indexRows(registerRows, ['registerJobNumber', 'jobNumber'])
|
||
const registerByName = indexRows(registerRows, ['flowerName', 'realName', 'registerName'])
|
||
const flowerByJobNumber = indexRows(flowerRows, ['registerJobNumber', 'jobNumber'])
|
||
const flowerByName = indexRows(flowerRows, ['flowerBookFlowerName', 'flowerBookName'])
|
||
|
||
const mergedRows: ReportEmployeeRow[] = []
|
||
const seen = new Set<string>()
|
||
const pushMerged = (row: ReportEmployeeRow) => {
|
||
const normalized = normalizeEmployeeRow(row)
|
||
const key = normalized.dingtalkUserId || normalized.jobNumber || normalized.dingEmail || normalized.displayName
|
||
if (seen.has(key)) return
|
||
seen.add(key)
|
||
mergedRows.push(row)
|
||
}
|
||
|
||
for (const employee of employeeRows) {
|
||
const register = getIndexed(registerByUserId, employee.dingtalkUserId)
|
||
|| getIndexed(registerByJobNumber, employee.jobNumber)
|
||
|| getIndexed(registerByName, employee.contactName)
|
||
const flower = getIndexed(flowerByJobNumber, register?.registerJobNumber, employee.jobNumber)
|
||
|| getIndexed(flowerByName, register?.flowerName, register?.realName, register?.registerName, employee.contactName)
|
||
pushMerged(mergeReportRows(employee, register, flower))
|
||
}
|
||
|
||
for (const register of registerRows) {
|
||
const flower = getIndexed(flowerByJobNumber, register.registerJobNumber, register.jobNumber)
|
||
|| getIndexed(flowerByName, register.flowerName, register.realName, register.registerName)
|
||
pushMerged(mergeReportRows(register, flower))
|
||
}
|
||
|
||
return applyResolvedDepartmentPaths(pool, mergedRows.slice(0, limit))
|
||
}
|
||
|
||
function buildIdentityWhere(
|
||
employeeColumns: TableColumns,
|
||
registerColumns: TableColumns,
|
||
flowerColumns: TableColumns,
|
||
identity: ReportIdentity,
|
||
) {
|
||
const conditions: string[] = []
|
||
const e = {
|
||
dingtalkUserId: pickColumn(employeeColumns, FIELD_CANDIDATES.dingtalkUserId),
|
||
email: pickColumn(employeeColumns, FIELD_CANDIDATES.email),
|
||
name: pickColumn(employeeColumns, FIELD_CANDIDATES.name),
|
||
}
|
||
const r = {
|
||
dingtalkUserId: pickColumn(registerColumns, FIELD_CANDIDATES.dingtalkUserId),
|
||
email: pickColumn(registerColumns, FIELD_CANDIDATES.email),
|
||
flowerName: pickColumn(registerColumns, FIELD_CANDIDATES.flowerName),
|
||
realName: pickColumn(registerColumns, FIELD_CANDIDATES.registerRealName),
|
||
registerName: pickColumn(registerColumns, FIELD_CANDIDATES.registerName),
|
||
}
|
||
const f = {
|
||
flowerName: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookFlowerName),
|
||
name: pickColumn(flowerColumns, FIELD_CANDIDATES.flowerBookName),
|
||
}
|
||
|
||
if (clean(identity.dingtalkUserId)) {
|
||
conditions.push(
|
||
...[
|
||
eqCondition('e', e.dingtalkUserId, 'dingtalkUserId'),
|
||
eqCondition('r', r.dingtalkUserId, 'dingtalkUserId'),
|
||
].filter((item): item is string => Boolean(item)),
|
||
)
|
||
}
|
||
if (clean(identity.email)) {
|
||
conditions.push(
|
||
...[
|
||
eqCondition('e', e.email, 'email'),
|
||
eqCondition('r', r.email, 'email'),
|
||
].filter((item): item is string => Boolean(item)),
|
||
)
|
||
}
|
||
|
||
const nameParam = clean(identity.flowerName) || clean(identity.displayName) || clean(identity.realName) || clean(identity.username)
|
||
if (nameParam) {
|
||
conditions.push(
|
||
...[
|
||
eqCondition('r', r.flowerName, 'name'),
|
||
eqCondition('r', r.realName, 'name'),
|
||
eqCondition('r', r.registerName, 'name'),
|
||
eqCondition('e', e.name, 'name'),
|
||
eqCondition('f', f.flowerName, 'name'),
|
||
eqCondition('f', f.name, 'name'),
|
||
].filter((item): item is string => Boolean(item)),
|
||
)
|
||
}
|
||
|
||
return {
|
||
whereSql: conditions.length > 0 ? `(${conditions.join(' OR ')})` : '1 = 0',
|
||
nameParam,
|
||
}
|
||
}
|
||
|
||
function normalizeEmployeeRow(row: ReportEmployeeRow) {
|
||
const extension = parseExtension(row.extension)
|
||
const dingtalkUserId = clean(row.dingtalkUserId)
|
||
const dingEmail = clean(row.dingEmail)
|
||
const jobNumber = clean(row.registerJobNumber) || clean(row.jobNumber)
|
||
const flowerName = clean(row.flowerBookFlowerName) || clean(row.flowerName) || clean(extension['花名'])
|
||
const realName = clean(row.realName) || clean(row.flowerBookName) || clean(row.registerName) || clean(row.contactName)
|
||
const displayName = flowerName || realName || jobNumber || dingtalkUserId || dingEmail || '未命名员工'
|
||
const positionLevel = clean(row.flowerBookJobLevel) || clean(row.positionLevel) || clean(extension['岗位职级'])
|
||
const levelType = getLevelType(positionLevel)
|
||
const departmentParts = getDepartmentParts(row)
|
||
const departmentName = clean(row.groupDepartmentName) || getDepartmentLeaf(departmentParts) || parseDepartmentValue(row.flowerBookDept) || clean(row.departmentName)
|
||
const departmentPath = clean(row.groupDepartmentPath) || joinDepartmentParts(departmentParts) || clean(row.departmentPath)
|
||
const positionTitle = clean(row.flowerBookJob) || clean(row.positionTitle) || clean(row.contactTitle)
|
||
const managerName = clean(row.flowerBookManager) || clean(row.flowerBookDirector) || clean(row.managerName) || clean(extension['主管'])
|
||
const employmentStatus = clean(row.flowerBookEmployeeStatus) || clean(row.employmentStatus)
|
||
const workPlace = clean(row.flowerBookWorkPlace) || clean(row.workPlace)
|
||
const departmentMemberships = row.resolvedDepartments || []
|
||
const isDingtalkAdmin = normalizeBoolean(row.isDingtalkAdmin)
|
||
const isDingtalkBoss = normalizeBoolean(row.isDingtalkBoss)
|
||
const isDingtalkSenior = normalizeBoolean(row.isDingtalkSenior)
|
||
|
||
return {
|
||
dingtalkUserId,
|
||
dingEmail,
|
||
jobNumber,
|
||
flowerName,
|
||
realName,
|
||
displayName,
|
||
departmentName,
|
||
departmentPath,
|
||
positionTitle,
|
||
positionLevel,
|
||
levelType,
|
||
isLeadership: isLeadershipLevel(positionLevel),
|
||
isDingtalkAdmin,
|
||
isDingtalkBoss,
|
||
isDingtalkSenior,
|
||
managerDingtalkUserId: clean(row.managerDingtalkUserId) || clean(extension['主管userid']),
|
||
managerJobNumber: clean(row.managerJobNumber) || clean(extension['主管工号']),
|
||
managerName,
|
||
employmentStatus,
|
||
workPlace,
|
||
sourcePayload: {
|
||
source: 'JSReportReplica.rpt',
|
||
syncedFields: {
|
||
hasDingtalkUserId: Boolean(dingtalkUserId),
|
||
hasDingEmail: Boolean(dingEmail),
|
||
jobNumber,
|
||
displayName,
|
||
departmentName,
|
||
departmentPath,
|
||
primaryDeptId: getPrimaryDepartmentId(row),
|
||
departmentIds: departmentMemberships.map((item) => item.sourceDeptId),
|
||
employeeDeptIdList: clean(row.employeeDeptIdList),
|
||
registerDeptIds: clean(row.registerDeptIds),
|
||
registerMainDeptId: clean(row.registerMainDeptId),
|
||
flowerBookDeptId: clean(row.flowerBookDeptId),
|
||
positionLevel,
|
||
levelType,
|
||
flowerBookRole: clean(row.flowerBookRole),
|
||
isDingtalkAdmin,
|
||
isDingtalkBoss,
|
||
isDingtalkSenior,
|
||
},
|
||
},
|
||
departmentMemberships,
|
||
syncedAt: new Date(),
|
||
}
|
||
}
|
||
|
||
type PreviewProfileData = Partial<Omit<ReturnType<typeof normalizeEmployeeRow>, 'sourcePayload' | 'syncedAt'>> & {
|
||
sourcePayload?: unknown
|
||
syncedAt?: Date
|
||
}
|
||
|
||
const PREVIEW_PROFILE_FIELDS = [
|
||
'dingtalkUserId',
|
||
'dingEmail',
|
||
'jobNumber',
|
||
'flowerName',
|
||
'realName',
|
||
'displayName',
|
||
'departmentName',
|
||
'departmentPath',
|
||
'positionTitle',
|
||
'positionLevel',
|
||
'levelType',
|
||
'isLeadership',
|
||
'isDingtalkAdmin',
|
||
'isDingtalkBoss',
|
||
'isDingtalkSenior',
|
||
'managerDingtalkUserId',
|
||
'managerJobNumber',
|
||
'managerName',
|
||
'employmentStatus',
|
||
'workPlace',
|
||
] as const
|
||
type PreviewChangedField = (typeof PREVIEW_PROFILE_FIELDS)[number] | 'sourcePayload' | 'departmentMemberships'
|
||
|
||
function getSourcePayloadLink(payload: unknown) {
|
||
if (!payload || typeof payload !== 'object' || Array.isArray(payload)) {
|
||
return { source: null, materialUserId: null, materialUserEmail: null }
|
||
}
|
||
const record = payload as Record<string, unknown>
|
||
return {
|
||
source: clean(record.source),
|
||
materialUserId: clean(record.materialUserId) || clean(record.userId),
|
||
materialUserEmail: clean(record.materialUserEmail),
|
||
}
|
||
}
|
||
|
||
function mergeExistingMaterialAccountLink(payload: unknown, existingPayload?: unknown) {
|
||
const merged = payload && typeof payload === 'object' && !Array.isArray(payload)
|
||
? { ...(payload as Record<string, unknown>) }
|
||
: {}
|
||
const incomingLink = getSourcePayloadLink(merged)
|
||
const existingLink = getSourcePayloadLink(existingPayload)
|
||
|
||
if (!incomingLink.materialUserId && existingLink.materialUserId) {
|
||
merged.materialUserId = existingLink.materialUserId
|
||
}
|
||
if (!incomingLink.materialUserEmail && existingLink.materialUserEmail) {
|
||
merged.materialUserEmail = existingLink.materialUserEmail
|
||
}
|
||
|
||
return merged
|
||
}
|
||
|
||
function getNormalizedProfileSyncKey(profile: ReturnType<typeof normalizeEmployeeRow>) {
|
||
return clean(profile.dingtalkUserId)?.toLowerCase()
|
||
|| clean(profile.jobNumber)?.toLowerCase()
|
||
|| clean(profile.dingEmail)?.toLowerCase()
|
||
|| `name:${clean(profile.displayName)?.toLowerCase() || 'unknown'}`
|
||
}
|
||
|
||
function previewValueEquals(left: unknown, right: unknown) {
|
||
if (typeof left === 'boolean' || typeof right === 'boolean') {
|
||
return Boolean(left) === Boolean(right)
|
||
}
|
||
return clean(left) === clean(right)
|
||
}
|
||
|
||
function normalizePreviewDepartmentMemberships(value: unknown) {
|
||
if (!Array.isArray(value)) return []
|
||
|
||
return value
|
||
.map((item) => {
|
||
if (!item || typeof item !== 'object' || Array.isArray(item)) return null
|
||
const record = item as Record<string, unknown>
|
||
const sourceDeptId = normalizeDepartmentId(record.sourceDeptId)
|
||
if (!sourceDeptId) return null
|
||
return {
|
||
sourceDeptId,
|
||
departmentName: clean(record.departmentName),
|
||
departmentPath: clean(record.departmentPath),
|
||
isPrimary: Boolean(record.isPrimary),
|
||
}
|
||
})
|
||
.filter((item): item is ResolvedEmployeeDepartment => Boolean(item))
|
||
.sort((left, right) => left.sourceDeptId.localeCompare(right.sourceDeptId))
|
||
}
|
||
|
||
function hasBusinessProfileChanges(existing: EmployeeProfileSnapshot, proposed: PreviewProfileData) {
|
||
const changedFields: PreviewChangedField[] = PREVIEW_PROFILE_FIELDS.filter((field) => (
|
||
Object.prototype.hasOwnProperty.call(proposed, field)
|
||
&& !previewValueEquals(existing[field], proposed[field])
|
||
))
|
||
|
||
if (Object.prototype.hasOwnProperty.call(proposed, 'sourcePayload')) {
|
||
const currentLink = getSourcePayloadLink(existing.sourcePayload)
|
||
const proposedLink = getSourcePayloadLink(proposed.sourcePayload)
|
||
if (
|
||
currentLink.source !== proposedLink.source
|
||
|| currentLink.materialUserId !== proposedLink.materialUserId
|
||
|| currentLink.materialUserEmail !== proposedLink.materialUserEmail
|
||
) {
|
||
changedFields.push('sourcePayload')
|
||
}
|
||
}
|
||
|
||
if (Object.prototype.hasOwnProperty.call(proposed, 'departmentMemberships')) {
|
||
const currentDepartments = normalizePreviewDepartmentMemberships(existing.departmentMemberships)
|
||
const proposedDepartments = normalizePreviewDepartmentMemberships(proposed.departmentMemberships)
|
||
if (JSON.stringify(currentDepartments) !== JSON.stringify(proposedDepartments)) {
|
||
changedFields.push('departmentMemberships')
|
||
}
|
||
}
|
||
|
||
return changedFields
|
||
}
|
||
|
||
function getExistingProfileMatchKeys(profile: Partial<EmployeeProfileSnapshot>) {
|
||
return [
|
||
...uniqueClean([profile.dingtalkUserId]).map((value) => `ding:${value.toLowerCase()}`),
|
||
...uniqueClean([profile.jobNumber]).map((value) => `job:${value.toLowerCase()}`),
|
||
...uniqueClean([profile.dingEmail]).map((value) => `email:${value.toLowerCase()}`),
|
||
]
|
||
}
|
||
|
||
function getProposedProfileMatchKeys(profile: PreviewProfileData) {
|
||
return [
|
||
...uniqueClean([profile.dingtalkUserId]).map((value) => `ding:${value.toLowerCase()}`),
|
||
...uniqueClean([profile.jobNumber]).map((value) => `job:${value.toLowerCase()}`),
|
||
...uniqueClean([profile.dingEmail]).map((value) => `email:${value.toLowerCase()}`),
|
||
]
|
||
}
|
||
|
||
function buildFallbackPreviewProfile(identity: ReportIdentity & { userId?: string }): PreviewProfileData {
|
||
return {
|
||
dingtalkUserId: clean(identity.dingtalkUserId),
|
||
dingEmail: clean(identity.email),
|
||
displayName: clean(identity.flowerName)
|
||
|| clean(identity.displayName)
|
||
|| clean(identity.realName)
|
||
|| clean(identity.username)
|
||
|| '未知员工',
|
||
realName: clean(identity.realName) || clean(identity.username),
|
||
flowerName: clean(identity.flowerName),
|
||
departmentName: clean(identity.department),
|
||
departmentPath: clean(identity.department),
|
||
sourcePayload: {
|
||
source: 'material_user_fallback',
|
||
userId: identity.userId || null,
|
||
materialUserId: identity.userId || null,
|
||
materialUserEmail: clean(identity.email),
|
||
},
|
||
}
|
||
}
|
||
|
||
function matchNormalizedRowsToIdentities(
|
||
identities: Array<ReportIdentity & { userId?: string }>,
|
||
rows: ReportEmployeeRow[],
|
||
) {
|
||
const identityIndexesByKey = new Map<string, number[]>()
|
||
identities.forEach((identity, index) => {
|
||
for (const key of getIdentityMatchKeys(identity)) {
|
||
const indexes = identityIndexesByKey.get(key) || []
|
||
indexes.push(index)
|
||
identityIndexesByKey.set(key, indexes)
|
||
}
|
||
})
|
||
|
||
let skippedRows = 0
|
||
const bestMatchesByIdentity = new Map<number, {
|
||
normalized: ReturnType<typeof normalizeEmployeeRow>
|
||
score: number
|
||
}>()
|
||
|
||
for (const row of rows) {
|
||
const normalized = normalizeEmployeeRow(row)
|
||
if (!normalized.dingtalkUserId && !normalized.jobNumber && !normalized.dingEmail) {
|
||
skippedRows += 1
|
||
continue
|
||
}
|
||
|
||
const candidateIndexes = new Set<number>()
|
||
for (const key of getNormalizedProfileMatchKeys(normalized)) {
|
||
for (const index of identityIndexesByKey.get(key) || []) {
|
||
candidateIndexes.add(index)
|
||
}
|
||
}
|
||
|
||
for (const index of Array.from(candidateIndexes)) {
|
||
const score = getIdentityProfileMatchScore(identities[index], normalized)
|
||
if (score <= 0) continue
|
||
const current = bestMatchesByIdentity.get(index)
|
||
if (!current || score > current.score) {
|
||
bestMatchesByIdentity.set(index, { normalized, score })
|
||
}
|
||
}
|
||
}
|
||
|
||
return { bestMatchesByIdentity, skippedRows }
|
||
}
|
||
|
||
export function comparePerformanceSurveyEmployeeSyncPreview(input: {
|
||
identities: Array<ReportIdentity & { userId?: string }>
|
||
rows: ReportEmployeeRow[]
|
||
existingProfiles: EmployeeProfileSnapshot[]
|
||
}) {
|
||
const { identities, rows, existingProfiles } = input
|
||
const { bestMatchesByIdentity, skippedRows } = matchNormalizedRowsToIdentities(identities, rows)
|
||
const existingByKey = new Map<string, EmployeeProfileSnapshot>()
|
||
for (const profile of existingProfiles) {
|
||
for (const key of getExistingProfileMatchKeys(profile)) {
|
||
if (!existingByKey.has(key)) existingByKey.set(key, profile)
|
||
}
|
||
}
|
||
|
||
const findExisting = (proposed: PreviewProfileData) => {
|
||
for (const key of getProposedProfileMatchKeys(proposed)) {
|
||
const existing = existingByKey.get(key)
|
||
if (existing) return existing
|
||
}
|
||
return null
|
||
}
|
||
|
||
let created = 0
|
||
let updated = 0
|
||
let unchanged = 0
|
||
let fallback = 0
|
||
let skipped = skippedRows
|
||
let deduplicated = 0
|
||
const matchedIdentityIndexes = new Set<number>()
|
||
const plannedProfileKeys = new Set<string>()
|
||
const changedFields = new Map<string, number>()
|
||
|
||
const classify = (proposed: PreviewProfileData) => {
|
||
const existing = findExisting(proposed)
|
||
if (!existing) {
|
||
created += 1
|
||
return
|
||
}
|
||
|
||
const fields = hasBusinessProfileChanges(existing, {
|
||
...proposed,
|
||
sourcePayload: mergeExistingMaterialAccountLink(
|
||
proposed.sourcePayload,
|
||
existing.sourcePayload,
|
||
),
|
||
})
|
||
if (fields.length === 0) {
|
||
unchanged += 1
|
||
return
|
||
}
|
||
|
||
updated += 1
|
||
for (const field of fields) {
|
||
changedFields.set(field, (changedFields.get(field) || 0) + 1)
|
||
}
|
||
}
|
||
|
||
const materialIdentityByProfileKey = new Map<string, ReportIdentity & { userId?: string }>()
|
||
for (const [index, match] of Array.from(bestMatchesByIdentity.entries())) {
|
||
const identity = identities[index]
|
||
const profileKey = getNormalizedProfileSyncKey(match.normalized)
|
||
if (!materialIdentityByProfileKey.has(profileKey)) {
|
||
materialIdentityByProfileKey.set(profileKey, identity)
|
||
}
|
||
matchedIdentityIndexes.add(index)
|
||
}
|
||
|
||
for (const row of rows) {
|
||
const normalized = normalizeEmployeeRow(row)
|
||
if (!normalized.dingtalkUserId && !normalized.jobNumber && !normalized.dingEmail) {
|
||
continue
|
||
}
|
||
const profileKey = getNormalizedProfileSyncKey(normalized)
|
||
if (plannedProfileKeys.has(profileKey)) {
|
||
deduplicated += 1
|
||
continue
|
||
}
|
||
|
||
plannedProfileKeys.add(profileKey)
|
||
const identity = materialIdentityByProfileKey.get(profileKey)
|
||
const proposed: PreviewProfileData = identity
|
||
? {
|
||
...normalized,
|
||
sourcePayload: {
|
||
...(normalized.sourcePayload || {}),
|
||
materialUserId: identity.userId || null,
|
||
materialUserEmail: clean(identity.email),
|
||
},
|
||
}
|
||
: normalized
|
||
classify(proposed)
|
||
}
|
||
|
||
identities.forEach((identity, index) => {
|
||
if (matchedIdentityIndexes.has(index)) return
|
||
if (
|
||
!clean(identity.dingtalkUserId)
|
||
&& !clean(identity.email)
|
||
&& !clean(identity.username)
|
||
&& !clean(identity.realName)
|
||
) {
|
||
skipped += 1
|
||
return
|
||
}
|
||
|
||
fallback += 1
|
||
classify(buildFallbackPreviewProfile(identity))
|
||
})
|
||
|
||
return {
|
||
fetched: rows.length,
|
||
matchedAccounts: matchedIdentityIndexes.size,
|
||
created,
|
||
updated,
|
||
unchanged,
|
||
fallback,
|
||
skipped,
|
||
deduplicated,
|
||
plannedProfileWrites: created + updated + unchanged,
|
||
changedFields: Array.from(changedFields.entries())
|
||
.map(([field, count]) => ({ field, count }))
|
||
.sort((left, right) => right.count - left.count || left.field.localeCompare(right.field)),
|
||
}
|
||
}
|
||
|
||
async function findExistingEmployeeProfile(data: Partial<ReturnType<typeof normalizeEmployeeRow>>) {
|
||
const or = [
|
||
data.dingtalkUserId ? { dingtalkUserId: data.dingtalkUserId } : null,
|
||
data.jobNumber ? { jobNumber: data.jobNumber } : null,
|
||
data.dingEmail ? { dingEmail: data.dingEmail } : null,
|
||
].filter(Boolean) as any[]
|
||
|
||
if (or.length === 0) return null
|
||
return prisma.performanceSurveyEmployeeProfile.findFirst({ where: { OR: or } })
|
||
}
|
||
|
||
async function countEmployeeProfileBusinessReferences(tx: any, employeeProfileId: string) {
|
||
const counts = await Promise.all([
|
||
tx.performanceSurveyAssignment.count({ where: { evaluatorEmployeeId: employeeProfileId } }),
|
||
tx.performanceSurveyTarget.count({ where: { employeeProfileId } }),
|
||
tx.performanceSurveyResponse.count({ where: { respondentEmployeeId: employeeProfileId } }),
|
||
tx.performanceSurveyWorkItem.count({ where: { submitterEmployeeId: employeeProfileId } }),
|
||
tx.performanceSurveyWorkItemScore.count({ where: { evaluatorEmployeeId: employeeProfileId } }),
|
||
tx.performanceSurveyNotification.count({ where: { recipientEmployeeId: employeeProfileId } }),
|
||
tx.performanceSurveyEmployeePositionAssignment.count({ where: { employeeProfileId } }),
|
||
tx.performanceSurvey.count({ where: { creatorEmployeeId: employeeProfileId } }),
|
||
])
|
||
|
||
return counts.reduce((total: number, count: number) => total + count, 0)
|
||
}
|
||
|
||
async function resolveEmployeeProfileIdentityInTransaction(
|
||
tx: any,
|
||
data: ReturnType<typeof normalizeEmployeeRow>,
|
||
) {
|
||
const strongIdentityConditions = [
|
||
data.dingtalkUserId ? { dingtalkUserId: data.dingtalkUserId } : null,
|
||
data.jobNumber ? { jobNumber: data.jobNumber } : null,
|
||
].filter(Boolean) as any[]
|
||
|
||
let matches = strongIdentityConditions.length > 0
|
||
? await tx.performanceSurveyEmployeeProfile.findMany({
|
||
where: { OR: strongIdentityConditions },
|
||
orderBy: { createdAt: 'asc' },
|
||
})
|
||
: []
|
||
|
||
if (matches.length === 0 && data.dingEmail) {
|
||
const emailMatch = await tx.performanceSurveyEmployeeProfile.findFirst({
|
||
where: { dingEmail: data.dingEmail },
|
||
orderBy: { createdAt: 'asc' },
|
||
})
|
||
matches = emailMatch ? [emailMatch] : []
|
||
}
|
||
|
||
if (matches.length <= 1) return matches[0] || null
|
||
|
||
const matchesWithReferences = await Promise.all(
|
||
matches.map(async (profile: any) => ({
|
||
profile,
|
||
businessReferenceCount: await countEmployeeProfileBusinessReferences(tx, profile.id),
|
||
})),
|
||
)
|
||
const referencedMatches = matchesWithReferences.filter((item) => item.businessReferenceCount > 0)
|
||
|
||
if (referencedMatches.length > 1) {
|
||
throw new Error(
|
||
`人员身份冲突：钉钉账号与工号分别命中了多条已承载考核数据的档案（${referencedMatches
|
||
.map((item) => item.profile.id)
|
||
.join(', ')}），为避免错误合并已中止同步`,
|
||
)
|
||
}
|
||
|
||
const canonical = referencedMatches[0]?.profile
|
||
|| matches.find((profile: any) => (
|
||
data.dingtalkUserId && profile.dingtalkUserId === data.dingtalkUserId
|
||
))
|
||
|| matches.find((profile: any) => (
|
||
data.jobNumber && profile.jobNumber === data.jobNumber
|
||
))
|
||
|| matches[0]
|
||
|
||
for (const item of matchesWithReferences) {
|
||
if (item.profile.id === canonical.id) continue
|
||
if (item.businessReferenceCount > 0) {
|
||
throw new Error(`人员身份冲突：档案 ${item.profile.id} 已承载考核数据，无法自动合并`)
|
||
}
|
||
await tx.performanceSurveyEmployeeProfile.delete({
|
||
where: { id: item.profile.id },
|
||
})
|
||
}
|
||
|
||
return canonical
|
||
}
|
||
|
||
export async function upsertPerformanceSurveyEmployeeProfileForSync(
|
||
data: ReturnType<typeof normalizeEmployeeRow>,
|
||
) {
|
||
const { departmentMemberships, ...profileData } = data
|
||
|
||
return prisma.$transaction(async (tx) => {
|
||
const existing = await resolveEmployeeProfileIdentityInTransaction(tx, data)
|
||
const profileId = existing?.id || randomUUID()
|
||
const profileWriteData = {
|
||
...profileData,
|
||
sourcePayload: mergeExistingMaterialAccountLink(
|
||
profileData.sourcePayload,
|
||
existing?.sourcePayload,
|
||
) as any,
|
||
}
|
||
const profile = existing
|
||
? await tx.performanceSurveyEmployeeProfile.update({
|
||
where: { id: existing.id },
|
||
data: profileWriteData,
|
||
})
|
||
: await tx.performanceSurveyEmployeeProfile.create({
|
||
data: {
|
||
id: profileId,
|
||
...profileWriteData,
|
||
},
|
||
})
|
||
|
||
await tx.performanceSurveyEmployeeDepartmentMembership.deleteMany({
|
||
where: { employeeProfileId: profile.id },
|
||
})
|
||
|
||
if (departmentMemberships.length > 0) {
|
||
const syncedAt = new Date()
|
||
await tx.performanceSurveyEmployeeDepartmentMembership.createMany({
|
||
data: departmentMemberships.map((membership) => ({
|
||
id: randomUUID(),
|
||
employeeProfileId: profile.id,
|
||
sourceDeptId: membership.sourceDeptId,
|
||
departmentName: membership.departmentName,
|
||
departmentPath: membership.departmentPath,
|
||
isPrimary: membership.isPrimary,
|
||
syncedAt,
|
||
})),
|
||
})
|
||
}
|
||
|
||
return profile
|
||
})
|
||
}
|
||
|
||
function getParentDepartmentName(departmentName?: string | null, departmentPath?: string | null) {
|
||
const current = clean(departmentName)
|
||
const parts = parseDepartmentParts(departmentPath)
|
||
if (!current || parts.length < 2) return null
|
||
const currentIndex = parts.lastIndexOf(current)
|
||
return currentIndex > 0 ? parts[currentIndex - 1] : parts[parts.length - 2] || null
|
||
}
|
||
|
||
async function upsertDepartments() {
|
||
const employees = await prisma.performanceSurveyEmployeeProfile.findMany({
|
||
where: {
|
||
AND: [
|
||
getActivePerformanceSurveyEmployeeProfileWhere(),
|
||
{ departmentName: { not: null } },
|
||
],
|
||
},
|
||
select: {
|
||
departmentName: true,
|
||
departmentPath: true,
|
||
departmentMemberships: {
|
||
select: {
|
||
departmentName: true,
|
||
departmentPath: true,
|
||
},
|
||
},
|
||
},
|
||
})
|
||
|
||
const grouped = new Map<string, { name: string; parentName: string | null; count: number }>()
|
||
for (const employee of employees) {
|
||
const departments = employee.departmentMemberships.length > 0
|
||
? employee.departmentMemberships
|
||
: [{
|
||
departmentName: employee.departmentName,
|
||
departmentPath: employee.departmentPath,
|
||
}]
|
||
const seenNames = new Set<string>()
|
||
for (const department of departments) {
|
||
if (!department.departmentName || seenNames.has(department.departmentName)) continue
|
||
seenNames.add(department.departmentName)
|
||
const existing = grouped.get(department.departmentName)
|
||
grouped.set(department.departmentName, {
|
||
name: department.departmentName,
|
||
parentName: existing?.parentName || getParentDepartmentName(
|
||
department.departmentName,
|
||
department.departmentPath,
|
||
),
|
||
count: (existing?.count || 0) + 1,
|
||
})
|
||
}
|
||
}
|
||
|
||
let count = 0
|
||
for (const item of Array.from(grouped.values())) {
|
||
await prisma.performanceSurveyDepartmentProfile.upsert({
|
||
where: { name: item.name },
|
||
update: {
|
||
parentName: item.parentName,
|
||
employeeCount: item.count,
|
||
syncedAt: new Date(),
|
||
},
|
||
create: {
|
||
id: randomUUID(),
|
||
name: item.name,
|
||
parentName: item.parentName,
|
||
employeeCount: item.count,
|
||
syncedAt: new Date(),
|
||
},
|
||
})
|
||
count += 1
|
||
}
|
||
|
||
return count
|
||
}
|
||
|
||
function shouldRefreshProfile(profile: { syncedAt?: Date | null; departmentName?: string | null; positionLevel?: string | null }) {
|
||
if (!profile.syncedAt) return true
|
||
const hours = Math.max(1, Number(process.env.PERFORMANCE_SURVEY_PROFILE_SYNC_HOURS || 24))
|
||
return Date.now() - profile.syncedAt.getTime() > hours * 60 * 60 * 1000
|
||
}
|
||
|
||
async function getReportPool() {
|
||
const sql = mssql as typeof import('mssql')
|
||
return {
|
||
sql,
|
||
pool: await sql.connect(getReportDbConfig() as any),
|
||
}
|
||
}
|
||
|
||
async function fetchNormalizedEmployeeByIdentityWithContext(
|
||
pool: any,
|
||
sql: typeof import('mssql'),
|
||
schema: Awaited<ReturnType<typeof getReportSchema>>,
|
||
identity: ReportIdentity,
|
||
) {
|
||
const where = buildIdentityWhere(
|
||
schema.employeeColumns,
|
||
schema.registerColumns,
|
||
schema.flowerColumns,
|
||
identity,
|
||
)
|
||
if (where.whereSql === '1 = 0') return null
|
||
|
||
const request = pool.request().input('limit', sql.Int, 1)
|
||
if (clean(identity.dingtalkUserId)) request.input('dingtalkUserId', sql.NVarChar(200), clean(identity.dingtalkUserId))
|
||
if (clean(identity.email)) request.input('email', sql.NVarChar(320), clean(identity.email))
|
||
if (where.nameParam) request.input('name', sql.NVarChar(200), where.nameParam)
|
||
|
||
const result = await request.query(
|
||
buildSelectSql(
|
||
schema.employeeColumns,
|
||
schema.registerColumns,
|
||
schema.flowerColumns,
|
||
where.whereSql,
|
||
'@limit',
|
||
),
|
||
)
|
||
const rows = await applyResolvedDepartmentPaths(pool, result.recordset || [])
|
||
const row = rows[0] as ReportEmployeeRow | undefined
|
||
if (!row) return null
|
||
|
||
const normalized = normalizeEmployeeRow(row)
|
||
;(normalized as any).sourcePayload = {
|
||
...(normalized.sourcePayload || {}),
|
||
materialUserId: identity.userId || null,
|
||
materialUserEmail: clean(identity.email),
|
||
}
|
||
return normalized
|
||
}
|
||
|
||
async function syncPerformanceSurveyEmployeeByIdentityWithContext(
|
||
pool: any,
|
||
sql: typeof import('mssql'),
|
||
schema: Awaited<ReturnType<typeof getReportSchema>>,
|
||
identity: ReportIdentity,
|
||
) {
|
||
const normalized = await fetchNormalizedEmployeeByIdentityWithContext(pool, sql, schema, identity)
|
||
if (!normalized) return null
|
||
if (!normalized.dingtalkUserId && !normalized.jobNumber && !normalized.dingEmail) return null
|
||
return upsertPerformanceSurveyEmployeeProfileForSync(normalized)
|
||
}
|
||
|
||
async function upsertFallbackEmployeeProfileForUser(identity: ReportIdentity & { userId?: string }) {
|
||
const existing = await findExistingEmployeeProfile({
|
||
dingtalkUserId: clean(identity.dingtalkUserId),
|
||
dingEmail: clean(identity.email),
|
||
})
|
||
const data = {
|
||
dingtalkUserId: clean(identity.dingtalkUserId),
|
||
dingEmail: clean(identity.email),
|
||
displayName: clean(identity.flowerName) || clean(identity.displayName) || clean(identity.realName) || clean(identity.username) || '未知员工',
|
||
realName: clean(identity.realName) || clean(identity.username),
|
||
flowerName: clean(identity.flowerName) || null,
|
||
departmentName: clean(identity.department),
|
||
departmentPath: clean(identity.department),
|
||
sourcePayload: {
|
||
source: 'material_user_fallback',
|
||
userId: identity.userId || null,
|
||
materialUserId: identity.userId || null,
|
||
materialUserEmail: clean(identity.email),
|
||
},
|
||
syncedAt: new Date(),
|
||
}
|
||
|
||
if (existing) {
|
||
return prisma.performanceSurveyEmployeeProfile.update({
|
||
where: { id: existing.id },
|
||
data,
|
||
})
|
||
}
|
||
|
||
return prisma.performanceSurveyEmployeeProfile.create({
|
||
data: {
|
||
id: randomUUID(),
|
||
...data,
|
||
},
|
||
})
|
||
}
|
||
|
||
export async function syncPerformanceSurveyEmployeeByIdentity(identity: ReportIdentity) {
|
||
if (!isPerformanceSurveyReportDbConfigured()) return null
|
||
|
||
const { sql, pool } = await getReportPool()
|
||
try {
|
||
const schema = await getReportSchema(pool)
|
||
const profile = await syncPerformanceSurveyEmployeeByIdentityWithContext(pool, sql, schema, identity)
|
||
if (profile) await upsertDepartments()
|
||
return profile
|
||
} finally {
|
||
await pool.close()
|
||
}
|
||
}
|
||
|
||
export async function previewPerformanceSurveyEmployeeByIdentity(identity: ReportIdentity) {
|
||
if (!isPerformanceSurveyReportDbConfigured()) return null
|
||
|
||
const { sql, pool } = await getReportPool()
|
||
try {
|
||
const schema = await getReportSchema(pool)
|
||
return fetchNormalizedEmployeeByIdentityWithContext(pool, sql, schema, identity)
|
||
} finally {
|
||
await pool.close()
|
||
}
|
||
}
|
||
|
||
function getProfileRefreshKey(identity: ReportIdentity & { userId?: string }) {
|
||
return clean(identity.dingtalkUserId)
|
||
|| clean(identity.email)?.toLowerCase()
|
||
|| clean(identity.userId)
|
||
|| clean(identity.username)?.toLowerCase()
|
||
|| clean(identity.realName)
|
||
|| clean(identity.displayName)
|
||
|| 'anonymous'
|
||
}
|
||
|
||
function refreshPerformanceSurveyEmployeeProfileInBackground(identity: ReportIdentity & { userId?: string }) {
|
||
if (!isPerformanceSurveyReportDbConfigured()) return
|
||
|
||
const key = getProfileRefreshKey(identity)
|
||
pruneProfileRefreshTracking()
|
||
if (profileRefreshInFlight.has(key)) return
|
||
if (profileRefreshInFlight.size >= PROFILE_BACKGROUND_REFRESH_MAX_IN_FLIGHT) return
|
||
|
||
const lastStartedAt = profileRefreshStartedAt.get(key) || 0
|
||
if (Date.now() - lastStartedAt < PROFILE_BACKGROUND_REFRESH_MIN_MS) return
|
||
|
||
profileRefreshStartedAt.set(key, Date.now())
|
||
const refreshPromise = syncPerformanceSurveyEmployeeByIdentity(identity)
|
||
.catch((error) => {
|
||
console.warn('[performance-survey] 后台人员档案同步失败:', error)
|
||
})
|
||
.finally(() => {
|
||
profileRefreshInFlight.delete(key)
|
||
pruneProfileRefreshTracking()
|
||
})
|
||
|
||
profileRefreshInFlight.set(key, refreshPromise)
|
||
pruneProfileRefreshTracking()
|
||
}
|
||
|
||
export async function ensurePerformanceSurveyEmployeeProfileForUser(identity: ReportIdentity & { userId?: string }) {
|
||
const existing = await findExistingEmployeeProfile({
|
||
dingtalkUserId: clean(identity.dingtalkUserId),
|
||
dingEmail: clean(identity.email),
|
||
})
|
||
|
||
if (existing) {
|
||
if (shouldRefreshProfile(existing)) {
|
||
refreshPerformanceSurveyEmployeeProfileInBackground(identity)
|
||
}
|
||
return existing
|
||
}
|
||
|
||
const fallback = await upsertFallbackEmployeeProfileForUser(identity)
|
||
refreshPerformanceSurveyEmployeeProfileInBackground(identity)
|
||
return fallback
|
||
}
|
||
|
||
export function ensurePerformanceSurveyDailyEmployeeSyncStarted() {
|
||
if (dailyWorkerStarted) return
|
||
dailyWorkerStarted = true
|
||
|
||
const intervalHours = Math.max(1, Number(process.env.PERFORMANCE_SURVEY_DAILY_SYNC_INTERVAL_HOURS || 24))
|
||
const initialDelayMs = Math.max(10, Number(process.env.PERFORMANCE_SURVEY_DAILY_SYNC_INITIAL_DELAY_SECONDS || 300)) * 1000
|
||
const intervalMs = intervalHours * 60 * 60 * 1000
|
||
const limit = Math.max(1, Math.min(Number(process.env.PERFORMANCE_SURVEY_DAILY_SYNC_LIMIT || 3000), 10_000))
|
||
|
||
const run = async () => {
|
||
if (!isPerformanceSurveyReportDbConfigured()) return
|
||
try {
|
||
await syncPerformanceSurveyEmployees({ limit })
|
||
} catch (error) {
|
||
console.warn('[performance-survey] 每日人员档案同步失败:', error)
|
||
}
|
||
}
|
||
|
||
const bootstrap = setTimeout(() => void run(), initialDelayMs)
|
||
bootstrap.unref?.()
|
||
|
||
const timer = setInterval(() => void run(), intervalMs)
|
||
timer.unref?.()
|
||
}
|
||
|
||
export async function previewPerformanceSurveyEmployeeSync(options?: { limit?: number }) {
|
||
const startedAt = new Date()
|
||
const limit = normalizeEmployeeSyncLimit(options?.limit)
|
||
const [identities, totalAccounts, existingProfiles] = await Promise.all([
|
||
listMaterialAccountSyncIdentities(limit),
|
||
countMaterialAccountSyncIdentities(),
|
||
prisma.performanceSurveyEmployeeProfile.findMany({
|
||
select: {
|
||
id: true,
|
||
dingtalkUserId: true,
|
||
dingEmail: true,
|
||
jobNumber: true,
|
||
flowerName: true,
|
||
realName: true,
|
||
displayName: true,
|
||
departmentName: true,
|
||
departmentPath: true,
|
||
positionTitle: true,
|
||
positionLevel: true,
|
||
levelType: true,
|
||
isLeadership: true,
|
||
isDingtalkAdmin: true,
|
||
isDingtalkBoss: true,
|
||
isDingtalkSenior: true,
|
||
managerDingtalkUserId: true,
|
||
managerJobNumber: true,
|
||
managerName: true,
|
||
employmentStatus: true,
|
||
workPlace: true,
|
||
sourcePayload: true,
|
||
syncedAt: true,
|
||
departmentMemberships: {
|
||
select: {
|
||
sourceDeptId: true,
|
||
departmentName: true,
|
||
departmentPath: true,
|
||
isPrimary: true,
|
||
},
|
||
},
|
||
},
|
||
}),
|
||
])
|
||
const configured = isPerformanceSurveyReportDbConfigured()
|
||
let rows: ReportEmployeeRow[] = []
|
||
|
||
if (configured) {
|
||
const { sql, pool } = await getReportPool()
|
||
try {
|
||
const schema = await getReportSchema(pool)
|
||
rows = await fetchFullSyncRows(pool, sql, schema, limit)
|
||
} finally {
|
||
await pool.close()
|
||
}
|
||
}
|
||
|
||
const comparison = comparePerformanceSurveyEmployeeSyncPreview({
|
||
identities,
|
||
rows,
|
||
existingProfiles: existingProfiles as EmployeeProfileSnapshot[],
|
||
})
|
||
const completedAt = new Date()
|
||
const latestLocalSyncedAt = existingProfiles.reduce<Date | null>((latest, profile) => {
|
||
if (!profile.syncedAt) return latest
|
||
return !latest || profile.syncedAt > latest ? profile.syncedAt : latest
|
||
}, null)
|
||
const warnings = [
|
||
'这是只读预检：只读取素材库账号、本地人员档案和完整在职花名册，不会写入或停用任何人员。',
|
||
'系统尚未记录“整批同步成功时间”；last.latestLocalProfileSyncedAt 仅表示最近一条本地人员档案的同步时间。',
|
||
'现有正式同步不会把“本次上游未返回”直接视为离职或停用，因此停用人数无法可靠推断。',
|
||
]
|
||
|
||
if (totalAccounts > identities.length) {
|
||
warnings.push(`本次只读取了 ${identities.length}/${totalAccounts} 个素材库账号用于建立账号关联；花名册人员仍按上限独立读取。`)
|
||
}
|
||
if (configured && rows.length >= limit) {
|
||
warnings.push(`本次花名册读取达到上限 ${limit} 人，可能不是完整人员范围，请提高同步上限后重试。`)
|
||
}
|
||
if (!configured) {
|
||
warnings.push('上游花名册数据库未配置；正式同步只会使用素材库账号建立或更新兜底档案。')
|
||
} else if (comparison.fallback > 0) {
|
||
warnings.push(`${comparison.fallback} 个素材库账号未匹配到上游花名册，正式同步时会使用素材库账号信息作为兜底。`)
|
||
}
|
||
if (comparison.skipped > 0) {
|
||
warnings.push(`${comparison.skipped} 条数据缺少可用身份标识，正式同步时会跳过。`)
|
||
}
|
||
|
||
return {
|
||
mode: 'preview',
|
||
dryRun: true,
|
||
source: {
|
||
type: configured ? 'report_database' : 'material_user_fallback',
|
||
name: configured ? 'JSReportReplica.rpt' : '素材库用户表',
|
||
tables: configured
|
||
? [
|
||
`${EMPLOYEE_TABLE.schema}.${EMPLOYEE_TABLE.table}`,
|
||
`${REGISTER_TABLE.schema}.${REGISTER_TABLE.table}`,
|
||
`${FLOWER_TABLE.schema}.${FLOWER_TABLE.table}`,
|
||
]
|
||
: ['User'],
|
||
access: 'read_only',
|
||
trigger: 'admin_manual_preview',
|
||
upstreamQueried: configured,
|
||
},
|
||
last: {
|
||
status: latestLocalSyncedAt ? 'approximate' : 'unavailable',
|
||
latestLocalProfileSyncedAt: latestLocalSyncedAt?.toISOString() || null,
|
||
localProfileCount: existingProfiles.length,
|
||
note: latestLocalSyncedAt
|
||
? '该时间来自最近一条本地人员档案，不代表最近一次整批同步成功时间。'
|
||
: '本地尚无可用的人员档案同步时间。',
|
||
},
|
||
now: {
|
||
requestedLimit: limit,
|
||
totalAccounts,
|
||
scannedAccounts: identities.length,
|
||
scannedProfiles: rows.length,
|
||
truncated: configured ? rows.length >= limit : totalAccounts > identities.length,
|
||
previewStartedAt: startedAt.toISOString(),
|
||
previewCompletedAt: completedAt.toISOString(),
|
||
durationMs: completedAt.getTime() - startedAt.getTime(),
|
||
databaseWritten: false,
|
||
},
|
||
counts: {
|
||
fetched: comparison.fetched,
|
||
matchedAccounts: comparison.matchedAccounts,
|
||
created: comparison.created,
|
||
updated: comparison.updated,
|
||
unchanged: comparison.unchanged,
|
||
fallback: comparison.fallback,
|
||
skipped: comparison.skipped,
|
||
deduplicated: comparison.deduplicated,
|
||
plannedProfileWrites: comparison.plannedProfileWrites,
|
||
deactivated: null,
|
||
},
|
||
deactivation: {
|
||
status: 'unsupported',
|
||
count: null,
|
||
reason: '正式同步不会把“本次上游未返回”直接视为离职或停用，无法仅根据一次读取可靠计算停用人数。',
|
||
},
|
||
changedFields: comparison.changedFields,
|
||
warnings,
|
||
}
|
||
}
|
||
|
||
export async function syncPerformanceSurveyEmployees(options?: { limit?: number }) {
|
||
const limit = normalizeEmployeeSyncLimit(options?.limit)
|
||
const identities = await listMaterialAccountSyncIdentities(limit)
|
||
|
||
if (!isPerformanceSurveyReportDbConfigured()) {
|
||
for (const identity of identities) {
|
||
await upsertFallbackEmployeeProfileForUser(identity)
|
||
}
|
||
const departmentCount = await upsertDepartments()
|
||
invalidatePerformanceSurveyAccountProfileWhereCache()
|
||
return {
|
||
scannedAccounts: identities.length,
|
||
scannedProfiles: 0,
|
||
upserted: 0,
|
||
fallback: identities.length,
|
||
skipped: 0,
|
||
failed: 0,
|
||
departmentCount,
|
||
syncedAt: new Date().toISOString(),
|
||
}
|
||
}
|
||
|
||
const { sql, pool } = await getReportPool()
|
||
|
||
try {
|
||
const schema = await getReportSchema(pool)
|
||
const rows = await fetchFullSyncRows(pool, sql, schema, limit)
|
||
const { bestMatchesByIdentity } = matchNormalizedRowsToIdentities(identities, rows)
|
||
|
||
let upserted = 0
|
||
let fallback = 0
|
||
let skipped = 0
|
||
let failed = 0
|
||
const matchedIdentityIndexes = new Set<number>()
|
||
const materialIdentityByProfileKey = new Map<string, ReportIdentity & { userId?: string }>()
|
||
for (const [index, match] of Array.from(bestMatchesByIdentity.entries())) {
|
||
const profileKey = getNormalizedProfileSyncKey(match.normalized)
|
||
if (!materialIdentityByProfileKey.has(profileKey)) {
|
||
materialIdentityByProfileKey.set(profileKey, identities[index])
|
||
}
|
||
matchedIdentityIndexes.add(index)
|
||
}
|
||
|
||
const syncedProfileKeys = new Set<string>()
|
||
for (const row of rows) {
|
||
const normalized = normalizeEmployeeRow(row)
|
||
if (!normalized.dingtalkUserId && !normalized.jobNumber && !normalized.dingEmail) {
|
||
skipped += 1
|
||
continue
|
||
}
|
||
const profileKey = getNormalizedProfileSyncKey(normalized)
|
||
if (syncedProfileKeys.has(profileKey)) {
|
||
skipped += 1
|
||
continue
|
||
}
|
||
syncedProfileKeys.add(profileKey)
|
||
|
||
const identity = materialIdentityByProfileKey.get(profileKey)
|
||
if (identity) {
|
||
;(normalized as any).sourcePayload = {
|
||
...(normalized.sourcePayload || {}),
|
||
materialUserId: identity.userId || null,
|
||
materialUserEmail: clean(identity.email),
|
||
}
|
||
}
|
||
|
||
try {
|
||
await upsertPerformanceSurveyEmployeeProfileForSync(normalized)
|
||
upserted += 1
|
||
} catch (error) {
|
||
failed += 1
|
||
console.warn('[performance-survey] 单个人员档案同步失败:', {
|
||
dingtalkUserId: normalized.dingtalkUserId,
|
||
dingEmail: normalized.dingEmail,
|
||
error,
|
||
})
|
||
}
|
||
}
|
||
|
||
for (let index = 0; index < identities.length; index += 1) {
|
||
const identity = identities[index]
|
||
if (matchedIdentityIndexes.has(index)) continue
|
||
if (!clean(identity.dingtalkUserId) && !clean(identity.email) && !clean(identity.username) && !clean(identity.realName)) {
|
||
skipped += 1
|
||
continue
|
||
}
|
||
try {
|
||
await upsertFallbackEmployeeProfileForUser(identity)
|
||
fallback += 1
|
||
} catch (error) {
|
||
failed += 1
|
||
console.warn('[performance-survey] 素材库账号兜底档案同步失败:', {
|
||
dingtalkUserId: identity.dingtalkUserId,
|
||
email: identity.email,
|
||
error,
|
||
})
|
||
}
|
||
}
|
||
|
||
const departmentCount = await upsertDepartments()
|
||
invalidatePerformanceSurveyAccountProfileWhereCache()
|
||
|
||
return {
|
||
scannedAccounts: identities.length,
|
||
scannedProfiles: rows.length,
|
||
upserted,
|
||
fallback,
|
||
skipped,
|
||
failed,
|
||
departmentCount,
|
||
syncedAt: new Date().toISOString(),
|
||
}
|
||
} finally {
|
||
await pool.close()
|
||
}
|
||
}
|