Dmitry
116 lines
3.4 KiB
Ruby
116 lines
3.4 KiB
Ruby
module ReportParsers
|
||
class Form13Parser < BaseParser
|
||
SHEET_NAME = 'Отчет об изменениях капитала'
|
||
FORM_CODE = '1.3'
|
||
|
||
def call
|
||
return {} unless sheet_exists?(SHEET_NAME)
|
||
|
||
sheet = spreadsheet.sheet(SHEET_NAME)
|
||
build_payloads(sheet)
|
||
end
|
||
|
||
private
|
||
|
||
def build_payloads(sheet)
|
||
header_row = find_section1_header_row(sheet)
|
||
return {} unless header_row
|
||
|
||
report_year = extract_report_year(sheet)
|
||
return {} unless report_year
|
||
|
||
name_col = find_name_column(sheet, header_row)
|
||
code_col = find_code_column(sheet, header_row)
|
||
total_col = find_total_column(sheet, header_row)
|
||
return {} unless code_col && total_col
|
||
|
||
end_row = section1_end_row(sheet, header_row)
|
||
payloads_by_year = {}
|
||
|
||
(header_row + 2).upto(end_row) do |row|
|
||
line_code = read_cell(sheet, row, code_col).to_s.strip.presence
|
||
next unless line_code&.match?(/\A3[123]\d{2}\z/)
|
||
|
||
row_year = year_from_code(line_code, report_year)
|
||
next unless row_year
|
||
|
||
name_cell = read_cell(sheet, row, name_col).to_s.strip
|
||
name_cell = read_cell(sheet, row, 1).to_s.strip if name_cell.blank?
|
||
value = read_cell(sheet, row, total_col)
|
||
|
||
payloads_by_year[row_year] ||= { form_code: FORM_CODE, sheet_name: SHEET_NAME, rows: [] }
|
||
payloads_by_year[row_year][:rows] << {
|
||
line_name: normalize_line_name(name_cell),
|
||
line_code: line_code,
|
||
value: value
|
||
}
|
||
end
|
||
|
||
payloads_by_year
|
||
end
|
||
|
||
def year_from_code(code, report_year)
|
||
case code[1]
|
||
when '1' then report_year - 2
|
||
when '2' then report_year - 1
|
||
when '3' then report_year
|
||
end
|
||
end
|
||
|
||
def extract_report_year(sheet)
|
||
1.upto([sheet.last_row, 10].min) do |row|
|
||
1.upto(sheet.last_column) do |col|
|
||
m = sheet.cell(row, col).to_s.match(/\bза\s+(20\d{2})\s*г/i)
|
||
return m[1].to_i if m
|
||
end
|
||
end
|
||
year = yearly_file&.year.to_i
|
||
year.positive? ? year : nil
|
||
end
|
||
|
||
def find_section1_header_row(sheet)
|
||
1.upto(sheet.last_row) do |row|
|
||
cells = 1.upto(sheet.last_column).map { |col| normalize_label(sheet.cell(row, col)) }
|
||
next unless cells.any? { |c| c.include?('наименование показателя') }
|
||
next unless cells.any? { |c| c.include?('код строки') || c == 'код' }
|
||
|
||
return row
|
||
end
|
||
nil
|
||
end
|
||
|
||
def find_name_column(sheet, header_row)
|
||
1.upto(sheet.last_column) do |col|
|
||
return col if normalize_label(sheet.cell(header_row, col)).include?('наименование показателя')
|
||
end
|
||
1
|
||
end
|
||
|
||
def find_code_column(sheet, header_row)
|
||
1.upto(sheet.last_column) do |col|
|
||
text = normalize_label(sheet.cell(header_row, col))
|
||
return col if text.include?('код строки') || text == 'код'
|
||
end
|
||
nil
|
||
end
|
||
|
||
def find_total_column(sheet, header_row)
|
||
result = nil
|
||
[header_row, header_row + 1].each do |row|
|
||
1.upto(sheet.last_column) do |col|
|
||
result = col if normalize_label(sheet.cell(row, col)) == 'итого'
|
||
end
|
||
return result if result
|
||
end
|
||
nil
|
||
end
|
||
|
||
def section1_end_row(sheet, from_row)
|
||
(from_row + 1).upto(sheet.last_row) do |row|
|
||
return row - 1 if normalize_label(sheet.cell(row, 1)).match?(/\A2\.\s*корректировки/i)
|
||
end
|
||
sheet.last_row
|
||
end
|
||
end
|
||
end
|