mkd/backend/balanceSheet76Processor.js

const csv = require('csv-parser');
const XLSX = require('xlsx');
const fs = require('fs');

/**
 * Процессор для обработки ОСВ по счёту 76.06 (лицевые счета жителей)
 */
class BalanceSheet76Processor {
  constructor() {}

  parseAmount(value) {
    if (!value) return 0;
    const str = String(value).replace(/\s/g, '').replace(',', '.');
    const num = parseFloat(str);
    return isNaN(num) ? 0 : num;
  }

  _isDataRow(rowArray) {
    if (!rowArray || rowArray.length < 5) return false;
    const firstCell = String(rowArray[0] || '').trim();
    const col1 = this.parseAmount(rowArray[1]);
    const col3 = this.parseAmount(rowArray[3]);
    const col4 = this.parseAmount(rowArray[4]);
    return firstCell.length >= 1 && (col1 > 0 || col3 > 0 || col4 > 0);
  }

  /**
   * Извлечь номер л/с из колонки 0 (например "00-000000001, ФИО (л/с)" -> "00-000000001")
   */
  extractAccountLs(firstCell) {
    const s = String(firstCell || '').trim();
    const comma = s.indexOf(',');
    if (comma > 0) {
      const prefix = s.substring(0, comma).trim();
      if (/^[\d\-]+$/.test(prefix) || /^[\d\-]+\s*$/.test(prefix)) return prefix;
    }
    return null;
  }

  /**
   * Парсинг CSV ОСВ 76 (UTF-8, BOM, автоопределение начала данных)
   */
  async parseBalanceSheet76CSV(filePath) {
    return new Promise((resolve, reject) => {
      const allRows = [];
      let currentRowIndex = 0;
      let dataStartRowIndex = -1;

      fs.createReadStream(filePath, { encoding: 'utf8' })
        .pipe(csv({ separator: ';', headers: false }))
        .on('data', (row) => {
          const rowArray = Object.values(row);
          if (currentRowIndex === 0 && rowArray[0] !== undefined) {
            rowArray[0] = String(rowArray[0]).replace(/^\uFEFF/, '');
          }
          allRows.push({ index: currentRowIndex, data: rowArray });
          currentRowIndex++;
        })
        .on('end', () => {
          for (let i = 0; i < allRows.length; i++) {
            const rowArray = allRows[i].data;
            const firstCell = String(rowArray[0] || '').trim();
            if (firstCell.includes('Обороты за период') || firstCell.includes('Контрагенты') || firstCell.includes('Счет,')) {
              dataStartRowIndex = i + 1;
              break;
            }
            if (this._isDataRow(rowArray)) {
              dataStartRowIndex = i;
              break;
            }
          }
          if (dataStartRowIndex < 0) dataStartRowIndex = 9;
          const rows = allRows.filter(r => r.index >= dataStartRowIndex);
          resolve(rows);
        })
        .on('error', reject);
    });
  }

  async parseBalanceSheet76XLSX(filePath) {
    const workbook = XLSX.readFile(filePath);
    const sheetName = workbook.SheetNames[0];
    const worksheet = workbook.Sheets[sheetName];
    const rawData = XLSX.utils.sheet_to_json(worksheet, { header: 1, defval: '', raw: false });
    const rows = [];
    for (let i = 9; i < rawData.length; i++) {
      rows.push({ index: i, data: rawData[i] || [] });
    }
    return rows;
  }

  /**
   * Парсинг ведомости 76 и возврат массива строк по лицевым счетам
   */
  async parseBalanceSheet76(filePath, fileType) {
    const rows = fileType === 'CSV'
      ? await this.parseBalanceSheet76CSV(filePath)
      : await this.parseBalanceSheet76XLSX(filePath);

    const result = [];
    let rowIndex = 0;

    for (const row of rows) {
      const rowData = row.data;
      if (!rowData || rowData.length === 0) continue;

      const firstCell = String(rowData[0] || '').trim();
      if (!firstCell) continue;
      if (firstCell === '76.06' || firstCell === 'Итого') continue;

      const saldoStartDebet = this.parseAmount(rowData[1]);
      const turnoverDebet = this.parseAmount(rowData[3]);
      const turnoverCredit = this.parseAmount(rowData[4]);
      const saldoEndDebet = this.parseAmount(rowData[5]);
      const saldoEndCredit = this.parseAmount(rowData[6]);

      result.push({
        row_index: rowIndex,
        account_label: firstCell,
        account_ls: this.extractAccountLs(firstCell),
        saldo_start_debet: saldoStartDebet,
        turnover_debet: turnoverDebet,
        turnover_credit: turnoverCredit,
        saldo_end_debet: saldoEndDebet,
        saldo_end_credit: saldoEndCredit
      });
      rowIndex++;
    }

    console.log(`[BalanceSheet76Processor] Распознано строк (лицевых счетов): ${result.length}`);
    return result;
  }

  extractPeriodFromFilename(filename) {
    const yearMatch = filename.match(/20\d{2}/);
    const year = yearMatch ? parseInt(yearMatch[0]) : new Date().getFullYear();
    const monthMatch = filename.match(/(\d{1,2})[\.\/](\d{4})|(\d{4})[\.\/](\d{1,2})|Январь|январь/i);
    if (monthMatch) {
      const monthNames = { январ: 1, феврал: 2, март: 3, апрел: 4, май: 5, июн: 6, июл: 7, август: 8, сентябр: 9, октябр: 10, ноябр: 11, декабр: 12 };
      const lower = filename.toLowerCase();
      for (const [key, m] of Object.entries(monthNames)) {
        if (lower.includes(key)) {
          const startDate = new Date(year, m - 1, 1);
          const endDate = new Date(year, m, 0);
          return {
            start: `${year}-${String(m).padStart(2, '0')}-01`,
            end: `${year}-${String(m).padStart(2, '0')}-${String(endDate.getDate()).padStart(2, '0')}`,
            type: 'month'
          };
        }
      }
    }
    return { start: `${year}-01-01`, end: `${year}-12-31`, type: 'year' };
  }
}

module.exports = BalanceSheet76Processor;