411 lines
14 KiB
JavaScript
411 lines
14 KiB
JavaScript
|
const { Pool } = require('pg');
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* Обработчик зарплатных данных из 1С
|
|||
|
|
* Расширяет функциональность fileProcessor для работы с зарплатными отчетами
|
|||
|
|
*/
|
|||
|
|
class SalaryProcessor {
|
|||
|
|
constructor(pool) {
|
|||
|
|
this.pool = pool;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* Обработка зарплатных данных из файла
|
|||
|
|
* @param {Array} rows - Строки из файла после парсинга
|
|||
|
|
* @param {Object} mapping - Маппинг полей из файла в структуру данных
|
|||
|
|
* @param {number} reportId - ID отчета в БД
|
|||
|
|
* @returns {Promise<Object>} Результат обработки
|
|||
|
|
*/
|
|||
|
|
async processSalaryData(rows, mapping, reportId) {
|
|||
|
|
const client = await this.pool.connect();
|
|||
|
|
let processedRows = 0;
|
|||
|
|
let errorRows = 0;
|
|||
|
|
let employeesUpdated = 0;
|
|||
|
|
const errors = [];
|
|||
|
|
|
|||
|
|
try {
|
|||
|
|
await client.query('BEGIN');
|
|||
|
|
|
|||
|
|
for (let i = 0; i < rows.length; i++) {
|
|||
|
|
const row = rows[i];
|
|||
|
|
const rowNum = i + 2; // +2 потому что первая строка - заголовок, нумерация с 1
|
|||
|
|
|
|||
|
|
try {
|
|||
|
|
// Применяем маппинг полей
|
|||
|
|
const mappedData = this.applyMapping(row, mapping);
|
|||
|
|
|
|||
|
|
// Валидация обязательных полей
|
|||
|
|
const validation = this.validateSalaryRow(mappedData);
|
|||
|
|
if (!validation.valid) {
|
|||
|
|
errors.push({
|
|||
|
|
row: rowNum,
|
|||
|
|
message: validation.error,
|
|||
|
|
data: mappedData
|
|||
|
|
});
|
|||
|
|
errorRows++;
|
|||
|
|
continue;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// Находим сотрудника по идентификатору
|
|||
|
|
const employee = await this.findEmployee(client, mappedData);
|
|||
|
|
if (!employee) {
|
|||
|
|
errors.push({
|
|||
|
|
row: rowNum,
|
|||
|
|
message: `Сотрудник не найден: ${mappedData.employeeIdentifier || 'не указан'}`,
|
|||
|
|
suggestion: 'Проверьте ФИО, ИНН или табельный номер в файле'
|
|||
|
|
});
|
|||
|
|
errorRows++;
|
|||
|
|
continue;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// Парсим период
|
|||
|
|
const period = this.parsePeriod(mappedData.period);
|
|||
|
|
if (!period) {
|
|||
|
|
errors.push({
|
|||
|
|
row: rowNum,
|
|||
|
|
message: 'Не удалось определить период из данных',
|
|||
|
|
data: mappedData.period
|
|||
|
|
});
|
|||
|
|
errorRows++;
|
|||
|
|
continue;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// Сохраняем историю зарплаты
|
|||
|
|
await this.saveSalaryHistory(client, {
|
|||
|
|
employeeId: employee.id,
|
|||
|
|
reportId: reportId,
|
|||
|
|
periodMonth: period.month,
|
|||
|
|
periodYear: period.year,
|
|||
|
|
baseSalary: this.parseNumber(mappedData.baseSalary) || 0,
|
|||
|
|
actualSalary: this.parseNumber(mappedData.actualSalary) || 0,
|
|||
|
|
bonuses: this.parseNumber(mappedData.bonuses) || 0,
|
|||
|
|
deductions: this.parseNumber(mappedData.deductions) || 0,
|
|||
|
|
netSalary: this.parseNumber(mappedData.netSalary) || 0,
|
|||
|
|
workedDays: this.parseNumber(mappedData.workedDays),
|
|||
|
|
workedHours: this.parseNumber(mappedData.workedHours),
|
|||
|
|
vacationDays: this.parseNumber(mappedData.vacationDays) || 0,
|
|||
|
|
sickLeaveDays: this.parseNumber(mappedData.sickLeaveDays) || 0,
|
|||
|
|
metadata: this.extractMetadata(mappedData)
|
|||
|
|
});
|
|||
|
|
|
|||
|
|
// Обновляем текущую зарплату сотрудника (берем последний период)
|
|||
|
|
if (mappedData.actualSalary || mappedData.baseSalary) {
|
|||
|
|
const newSalary = this.parseNumber(mappedData.actualSalary) ||
|
|||
|
|
this.parseNumber(mappedData.baseSalary) ||
|
|||
|
|
employee.salary;
|
|||
|
|
|
|||
|
|
if (newSalary !== employee.salary) {
|
|||
|
|
await client.query(
|
|||
|
|
'UPDATE employees SET salary = $1, updated_at = NOW() WHERE id = $2',
|
|||
|
|
[newSalary, employee.id]
|
|||
|
|
);
|
|||
|
|
employeesUpdated++;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
processedRows++;
|
|||
|
|
|
|||
|
|
} catch (error) {
|
|||
|
|
console.error(`Ошибка обработки строки ${rowNum}:`, error);
|
|||
|
|
errors.push({
|
|||
|
|
row: rowNum,
|
|||
|
|
message: error.message,
|
|||
|
|
data: row
|
|||
|
|
});
|
|||
|
|
errorRows++;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
await client.query('COMMIT');
|
|||
|
|
|
|||
|
|
return {
|
|||
|
|
success: true,
|
|||
|
|
processedRows,
|
|||
|
|
errorRows,
|
|||
|
|
employeesUpdated,
|
|||
|
|
errors: errors.slice(0, 50) // Ограничиваем количество ошибок в ответе
|
|||
|
|
};
|
|||
|
|
|
|||
|
|
} catch (error) {
|
|||
|
|
await client.query('ROLLBACK');
|
|||
|
|
throw error;
|
|||
|
|
} finally {
|
|||
|
|
client.release();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* Применение маппинга полей из файла
|
|||
|
|
*/
|
|||
|
|
applyMapping(row, mapping) {
|
|||
|
|
const mapped = {};
|
|||
|
|
const columnMappings = mapping.columnMappings || {};
|
|||
|
|
|
|||
|
|
// Стандартные поля
|
|||
|
|
const fieldMappings = {
|
|||
|
|
employeeIdentifier: ['ФИО', 'Имя', 'Сотрудник', 'СотрудникФИО', 'employeeName', 'name'],
|
|||
|
|
inn: ['ИНН', 'inn'],
|
|||
|
|
snils: ['СНИЛС', 'snils'],
|
|||
|
|
period: ['Период', 'Месяц', 'Дата', 'period', 'month', 'date'],
|
|||
|
|
baseSalary: ['Оклад', 'Базовая зарплата', 'baseSalary', 'salary'],
|
|||
|
|
actualSalary: ['Зарплата', 'Начислено', 'actualSalary', 'accrued'],
|
|||
|
|
bonuses: ['Премия', 'Премии', 'bonuses', 'bonus'],
|
|||
|
|
deductions: ['Удержано', 'Удержания', 'deductions', 'deduction'],
|
|||
|
|
netSalary: ['К выплате', 'К выплате', 'netSalary', 'toPay'],
|
|||
|
|
workedDays: ['Отработано дней', 'Дней', 'workedDays', 'days'],
|
|||
|
|
workedHours: ['Отработано часов', 'Часов', 'workedHours', 'hours'],
|
|||
|
|
vacationDays: ['Отпуск', 'Дни отпуска', 'vacationDays', 'vacation'],
|
|||
|
|
sickLeaveDays: ['Больничный', 'Дни больничного', 'sickLeaveDays', 'sickLeave']
|
|||
|
|
};
|
|||
|
|
|
|||
|
|
// Применяем маппинг
|
|||
|
|
for (const [targetField, sourceFields] of Object.entries(fieldMappings)) {
|
|||
|
|
for (const sourceField of sourceFields) {
|
|||
|
|
// Сначала проверяем маппинг из настроек
|
|||
|
|
const mappedField = columnMappings[sourceField];
|
|||
|
|
if (mappedField && row[mappedField] !== undefined) {
|
|||
|
|
mapped[targetField] = row[mappedField];
|
|||
|
|
break;
|
|||
|
|
}
|
|||
|
|
// Затем проверяем прямое совпадение
|
|||
|
|
if (row[sourceField] !== undefined) {
|
|||
|
|
mapped[targetField] = row[sourceField];
|
|||
|
|
break;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return mapped;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* Валидация строки зарплатных данных
|
|||
|
|
*/
|
|||
|
|
validateSalaryRow(data) {
|
|||
|
|
if (!data.employeeIdentifier && !data.inn) {
|
|||
|
|
return {
|
|||
|
|
valid: false,
|
|||
|
|
error: 'Не указан идентификатор сотрудника (ФИО или ИНН)'
|
|||
|
|
};
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (!data.period) {
|
|||
|
|
return {
|
|||
|
|
valid: false,
|
|||
|
|
error: 'Не указан период (месяц/год)'
|
|||
|
|
};
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return { valid: true };
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* Поиск сотрудника по идентификатору
|
|||
|
|
*/
|
|||
|
|
async findEmployee(client, data) {
|
|||
|
|
// Сначала пробуем найти по ИНН (самый точный способ)
|
|||
|
|
if (data.inn) {
|
|||
|
|
const result = await client.query(
|
|||
|
|
`SELECT e.* FROM employees e
|
|||
|
|
INNER JOIN employee_accounting_data a ON e.id = a.employee_id
|
|||
|
|
WHERE a.inn = $1`,
|
|||
|
|
[data.inn]
|
|||
|
|
);
|
|||
|
|
if (result.rows.length > 0) {
|
|||
|
|
return result.rows[0];
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// Затем по СНИЛС
|
|||
|
|
if (data.snils) {
|
|||
|
|
const result = await client.query(
|
|||
|
|
`SELECT e.* FROM employees e
|
|||
|
|
INNER JOIN employee_accounting_data a ON e.id = a.employee_id
|
|||
|
|
WHERE a.snils = $1`,
|
|||
|
|
[data.snils]
|
|||
|
|
);
|
|||
|
|
if (result.rows.length > 0) {
|
|||
|
|
return result.rows[0];
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// Затем по ФИО (частичное совпадение)
|
|||
|
|
if (data.employeeIdentifier) {
|
|||
|
|
const nameParts = data.employeeIdentifier.trim().split(/\s+/);
|
|||
|
|
if (nameParts.length >= 2) {
|
|||
|
|
// Ищем по фамилии и имени
|
|||
|
|
const lastName = nameParts[0];
|
|||
|
|
const firstName = nameParts[1];
|
|||
|
|
|
|||
|
|
const result = await client.query(
|
|||
|
|
`SELECT * FROM employees
|
|||
|
|
WHERE name ILIKE $1 OR name ILIKE $2`,
|
|||
|
|
[`%${lastName}%${firstName}%`, `%${firstName}%${lastName}%`]
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
if (result.rows.length === 1) {
|
|||
|
|
return result.rows[0];
|
|||
|
|
} else if (result.rows.length > 1) {
|
|||
|
|
// Несколько совпадений - нужен более точный поиск
|
|||
|
|
// Пробуем точное совпадение
|
|||
|
|
const exactMatch = result.rows.find(e =>
|
|||
|
|
e.name.toLowerCase() === data.employeeIdentifier.toLowerCase()
|
|||
|
|
);
|
|||
|
|
if (exactMatch) {
|
|||
|
|
return exactMatch;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* Парсинг периода из строки
|
|||
|
|
* Поддерживает форматы: "01.2024", "январь 2024", "2024-01", "01/2024"
|
|||
|
|
*/
|
|||
|
|
parsePeriod(periodStr) {
|
|||
|
|
if (!periodStr) return null;
|
|||
|
|
|
|||
|
|
const str = String(periodStr).trim();
|
|||
|
|
|
|||
|
|
// Формат "01.2024" или "01/2024"
|
|||
|
|
const dotMatch = str.match(/^(\d{1,2})[./](\d{4})$/);
|
|||
|
|
if (dotMatch) {
|
|||
|
|
const month = parseInt(dotMatch[1], 10);
|
|||
|
|
const year = parseInt(dotMatch[2], 10);
|
|||
|
|
if (month >= 1 && month <= 12 && year >= 2000) {
|
|||
|
|
return { month, year };
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// Формат "2024-01"
|
|||
|
|
const dashMatch = str.match(/^(\d{4})-(\d{1,2})$/);
|
|||
|
|
if (dashMatch) {
|
|||
|
|
const year = parseInt(dashMatch[1], 10);
|
|||
|
|
const month = parseInt(dashMatch[2], 10);
|
|||
|
|
if (month >= 1 && month <= 12 && year >= 2000) {
|
|||
|
|
return { month, year };
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// Формат "январь 2024" или "Январь 2024"
|
|||
|
|
const monthNames = {
|
|||
|
|
'январь': 1, 'февраль': 2, 'март': 3, 'апрель': 4,
|
|||
|
|
'май': 5, 'июнь': 6, 'июль': 7, 'август': 8,
|
|||
|
|
'сентябрь': 9, 'октябрь': 10, 'ноябрь': 11, 'декабрь': 12
|
|||
|
|
};
|
|||
|
|
|
|||
|
|
for (const [monthName, monthNum] of Object.entries(monthNames)) {
|
|||
|
|
const regex = new RegExp(`${monthName}\\s+(\\d{4})`, 'i');
|
|||
|
|
const match = str.match(regex);
|
|||
|
|
if (match) {
|
|||
|
|
const year = parseInt(match[1], 10);
|
|||
|
|
if (year >= 2000) {
|
|||
|
|
return { month: monthNum, year };
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// Пробуем распарсить как дату
|
|||
|
|
const date = new Date(str);
|
|||
|
|
if (!isNaN(date.getTime())) {
|
|||
|
|
return {
|
|||
|
|
month: date.getMonth() + 1,
|
|||
|
|
year: date.getFullYear()
|
|||
|
|
};
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* Парсинг числа из строки
|
|||
|
|
*/
|
|||
|
|
parseNumber(value) {
|
|||
|
|
if (value === null || value === undefined || value === '') {
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (typeof value === 'number') {
|
|||
|
|
return value;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// Убираем пробелы и заменяем запятую на точку
|
|||
|
|
const cleaned = String(value)
|
|||
|
|
.replace(/\s/g, '')
|
|||
|
|
.replace(',', '.');
|
|||
|
|
|
|||
|
|
const parsed = parseFloat(cleaned);
|
|||
|
|
return isNaN(parsed) ? null : parsed;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* Извлечение дополнительных метаданных
|
|||
|
|
*/
|
|||
|
|
extractMetadata(data) {
|
|||
|
|
const metadata = {};
|
|||
|
|
const knownFields = [
|
|||
|
|
'employeeIdentifier', 'inn', 'snils', 'period',
|
|||
|
|
'baseSalary', 'actualSalary', 'bonuses', 'deductions', 'netSalary',
|
|||
|
|
'workedDays', 'workedHours', 'vacationDays', 'sickLeaveDays'
|
|||
|
|
];
|
|||
|
|
|
|||
|
|
for (const [key, value] of Object.entries(data)) {
|
|||
|
|
if (!knownFields.includes(key) && value !== null && value !== undefined) {
|
|||
|
|
metadata[key] = value;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return Object.keys(metadata).length > 0 ? metadata : null;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* Сохранение истории зарплаты
|
|||
|
|
*/
|
|||
|
|
async saveSalaryHistory(client, data) {
|
|||
|
|
await client.query(
|
|||
|
|
`INSERT INTO employee_salary_history (
|
|||
|
|
employee_id, report_id, period_month, period_year,
|
|||
|
|
base_salary, actual_salary, bonuses, deductions, net_salary,
|
|||
|
|
worked_days, worked_hours, vacation_days, sick_leave_days,
|
|||
|
|
metadata, imported_from_1c
|
|||
|
|
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)
|
|||
|
|
ON CONFLICT (employee_id, period_month, period_year)
|
|||
|
|
DO UPDATE SET
|
|||
|
|
base_salary = EXCLUDED.base_salary,
|
|||
|
|
actual_salary = EXCLUDED.actual_salary,
|
|||
|
|
bonuses = EXCLUDED.bonuses,
|
|||
|
|
deductions = EXCLUDED.deductions,
|
|||
|
|
net_salary = EXCLUDED.net_salary,
|
|||
|
|
worked_days = EXCLUDED.worked_days,
|
|||
|
|
worked_hours = EXCLUDED.worked_hours,
|
|||
|
|
vacation_days = EXCLUDED.vacation_days,
|
|||
|
|
sick_leave_days = EXCLUDED.sick_leave_days,
|
|||
|
|
metadata = EXCLUDED.metadata,
|
|||
|
|
report_id = EXCLUDED.report_id,
|
|||
|
|
updated_at = NOW()`,
|
|||
|
|
[
|
|||
|
|
data.employeeId,
|
|||
|
|
data.reportId,
|
|||
|
|
data.periodMonth,
|
|||
|
|
data.periodYear,
|
|||
|
|
data.baseSalary,
|
|||
|
|
data.actualSalary,
|
|||
|
|
data.bonuses,
|
|||
|
|
data.deductions,
|
|||
|
|
data.netSalary,
|
|||
|
|
data.workedDays,
|
|||
|
|
data.workedHours,
|
|||
|
|
data.vacationDays,
|
|||
|
|
data.sickLeaveDays,
|
|||
|
|
data.metadata ? JSON.stringify(data.metadata) : null,
|
|||
|
|
true
|
|||
|
|
]
|
|||
|
|
);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
module.exports = SalaryProcessor;
|