Dmitry
238 lines
7.1 KiB
Ruby
238 lines
7.1 KiB
Ruby
require 'roo'
|
|
|
|
class ReportParser
|
|
FORM_DEFINITIONS = {
|
|
'1.1' => {
|
|
parser: ReportParsers::Form11Parser,
|
|
sheet_name: 'Бухгалтерский баланс'
|
|
},
|
|
'1.2' => {
|
|
parser: ReportParsers::Form12Parser,
|
|
sheet_name: 'Отчет о финансовых результатах'
|
|
},
|
|
'1.3' => {
|
|
parser: ReportParsers::Form13Parser,
|
|
sheet_name: 'Отчет об изменениях капитала'
|
|
},
|
|
'1.4' => {
|
|
parser: ReportParsers::Form14Parser,
|
|
sheet_name: 'Отчет о движении денежных средств'
|
|
}
|
|
}.freeze
|
|
|
|
DEFAULT_MISSING_VALUE = '-'
|
|
|
|
def initialize(yearly_file)
|
|
@yearly_file = yearly_file
|
|
end
|
|
|
|
def call
|
|
ActiveRecord::Base.transaction do
|
|
ReportParsers::OrganizationInfoParser.new(@yearly_file, spreadsheet: spreadsheet).call
|
|
|
|
parsed_forms = parse_forms
|
|
save_aggregated_statements(parsed_forms)
|
|
end
|
|
ensure
|
|
@tempfile&.close!
|
|
end
|
|
|
|
private
|
|
|
|
def spreadsheet
|
|
@spreadsheet ||= begin
|
|
@tempfile = Tempfile.new(['report', '.xlsx'])
|
|
@tempfile.binmode
|
|
@yearly_file.file.download { |chunk| @tempfile.write(chunk) }
|
|
@tempfile.rewind
|
|
Roo::Excelx.new(@tempfile.path)
|
|
end
|
|
end
|
|
|
|
def parse_forms
|
|
FORM_DEFINITIONS.each_with_object({}) do |(form_code, config), hash|
|
|
parser_class = config.fetch(:parser)
|
|
hash[form_code] = parser_class ? parser_class.new(@yearly_file, spreadsheet: spreadsheet).call : {}
|
|
end
|
|
end
|
|
|
|
def save_aggregated_statements(parsed_forms)
|
|
codes = report_codes
|
|
return if codes.blank?
|
|
|
|
target_years = extract_target_years(parsed_forms)
|
|
return if target_years.blank?
|
|
|
|
target_years.each do |year|
|
|
statement = find_or_build_statement(year)
|
|
if should_update_statement?(statement)
|
|
previous_data = statement.data.deep_dup if statement.persisted?
|
|
|
|
statement.assign_attributes(
|
|
yearly_file: @yearly_file,
|
|
sheet_number: year,
|
|
data: build_payload_for_year(year, parsed_forms, codes, previous_data: previous_data)
|
|
)
|
|
statement.save!
|
|
end
|
|
|
|
cleanup_duplicate_statements(year, statement.id)
|
|
end
|
|
end
|
|
|
|
def extract_target_years(parsed_forms)
|
|
years = parsed_forms.values.flat_map(&:keys).map(&:to_i).select(&:positive?).uniq
|
|
years = [@yearly_file.year.to_i] if years.blank? && @yearly_file.year.to_i.positive?
|
|
years.sort.reverse
|
|
end
|
|
|
|
def build_payload_for_year(year, parsed_forms, codes, previous_data: nil)
|
|
forms_payload = FORM_DEFINITIONS.each_with_object({}) do |(form_code, config), hash|
|
|
form_present, form_data = parsed_form_for_year(parsed_forms, form_code, year)
|
|
form_data ||= {}
|
|
|
|
unless form_present
|
|
previous_payload = previous_form_payload(previous_data, form_code)
|
|
if previous_payload.present?
|
|
hash[form_code] = previous_payload
|
|
next
|
|
end
|
|
end
|
|
|
|
source_rows = form_data[:rows] || form_data['rows'] || []
|
|
rows_by_code = index_rows_by_code(source_rows)
|
|
form_codes = report_codes_for_form(form_code, codes)
|
|
|
|
hash[form_code] = {
|
|
form_code: form_code,
|
|
sheet_name: form_data[:sheet_name] || form_data['sheet_name'] || config.fetch(:sheet_name),
|
|
rows: form_codes.map do |report_code|
|
|
source_row = rows_by_code[report_code.code]
|
|
source_value = source_row && (source_row[:value] || source_row['value'])
|
|
|
|
{
|
|
line_name: report_code.name,
|
|
line_code: report_code.code,
|
|
value: normalized_statement_value(source_value)
|
|
}
|
|
end
|
|
}
|
|
end
|
|
|
|
{
|
|
data_year: year,
|
|
forms: forms_payload
|
|
}
|
|
end
|
|
|
|
def parsed_form_for_year(parsed_forms, form_code, year)
|
|
form_results = parsed_forms.fetch(form_code, {})
|
|
return [true, form_results[year]] if form_results.key?(year)
|
|
return [true, form_results[year.to_s]] if form_results.key?(year.to_s)
|
|
|
|
[false, {}]
|
|
end
|
|
|
|
def previous_form_payload(previous_data, form_code)
|
|
forms = previous_data && (previous_data[:forms] || previous_data['forms'])
|
|
return unless forms.is_a?(Hash)
|
|
|
|
forms[form_code] || forms[form_code.to_sym]
|
|
end
|
|
|
|
def index_rows_by_code(rows)
|
|
rows.each_with_object({}) do |row, hash|
|
|
normalized_code = normalize_code(row[:line_code] || row['line_code'])
|
|
next if normalized_code.blank?
|
|
|
|
# Keep first non-blank value for duplicated codes inside one form/year.
|
|
existing = hash[normalized_code]
|
|
next if existing.present? && (existing[:value] || existing['value']).present?
|
|
|
|
hash[normalized_code] = row
|
|
end
|
|
end
|
|
|
|
def find_or_build_statement(year)
|
|
existing = FinancialStatement
|
|
.joins(:yearly_file)
|
|
.where(sheet_number: year)
|
|
.where(yearly_files: { organization_id: @yearly_file.organization_id })
|
|
.order('yearly_files.year DESC NULLS LAST, financial_statements.updated_at DESC')
|
|
.first
|
|
|
|
return existing if existing
|
|
|
|
@yearly_file.financial_statements.new(sheet_number: year)
|
|
end
|
|
|
|
def should_update_statement?(statement)
|
|
return true if statement.new_record?
|
|
|
|
existing_source_year = statement.yearly_file&.year.to_i
|
|
current_source_year = @yearly_file.year.to_i
|
|
|
|
existing_source_year <= current_source_year
|
|
end
|
|
|
|
def normalize_code(value)
|
|
value.to_s.scan(/\d+/).join.presence
|
|
end
|
|
|
|
def normalized_statement_value(value)
|
|
return DEFAULT_MISSING_VALUE if value.nil?
|
|
return value if value.is_a?(Numeric)
|
|
|
|
text = value.to_s.strip
|
|
return DEFAULT_MISSING_VALUE if text.blank?
|
|
|
|
numeric_text?(text) ? text : DEFAULT_MISSING_VALUE
|
|
end
|
|
|
|
def numeric_text?(text)
|
|
normalized = text.tr("\u00A0", ' ').squeeze(' ')
|
|
|
|
integer_or_decimal = /\A[+-]?\d+(?:[.,]\d+)?\z/
|
|
integer_or_decimal_grouped = /\A[+-]?\d{1,3}(?: \d{3})+(?:[.,]\d+)?\z/
|
|
parenthesized_integer_or_decimal = /\A\(\d+(?:[.,]\d+)?\)\z/
|
|
parenthesized_integer_or_decimal_grouped = /\A\(\d{1,3}(?: \d{3})+(?:[.,]\d+)?\)\z/
|
|
|
|
normalized.match?(integer_or_decimal) ||
|
|
normalized.match?(integer_or_decimal_grouped) ||
|
|
normalized.match?(parenthesized_integer_or_decimal) ||
|
|
normalized.match?(parenthesized_integer_or_decimal_grouped)
|
|
end
|
|
|
|
def report_codes
|
|
@report_codes ||= ReportCode.select(:code, :name).order(:id).to_a
|
|
end
|
|
|
|
def report_codes_for_form(form_code, codes)
|
|
codes.select { |report_code| report_code_belongs_to_form?(report_code.code, form_code) }
|
|
end
|
|
|
|
def report_code_belongs_to_form?(code, form_code)
|
|
normalized_code = normalize_code(code)
|
|
return false if normalized_code.blank?
|
|
|
|
case form_code
|
|
when '1.1' then normalized_code.start_with?('1')
|
|
when '1.2' then normalized_code.start_with?('2')
|
|
when '1.3' then normalized_code.start_with?('3')
|
|
when '1.4' then normalized_code.start_with?('4')
|
|
else
|
|
true
|
|
end
|
|
end
|
|
|
|
def cleanup_duplicate_statements(year, keeper_id)
|
|
FinancialStatement
|
|
.joins(:yearly_file)
|
|
.where(sheet_number: year)
|
|
.where(yearly_files: { organization_id: @yearly_file.organization_id })
|
|
.where.not(id: keeper_id)
|
|
.destroy_all
|
|
end
|
|
|
|
end
|