Class: Account

Inherits:
Ekylibre::Record::Base show all
Includes:
Customizable
Defined in:
app/models/account.rb

Overview

Informations

License

Ekylibre - Simple agricultural ERP Copyright (C) 2008-2009 Brice Texier, Thibaud Merigon Copyright (C) 2010-2012 Brice Texier Copyright (C) 2012-2019 Brice Texier, David Joulin

This program is free software: you can redistribute it and/or modify it under the terms of the GNU Affero General Public License as published by the Free Software Foundation, either version 3 of the License, or any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more details.

You should have received a copy of the GNU Affero General Public License along with this program. If not, see www.gnu.org/licenses.

Table: accounts

created_at    :datetime         not null
creator_id    :integer
custom_fields :jsonb
debtor        :boolean          default(FALSE), not null
description   :text
id            :integer          not null, primary key
label         :string           not null
last_letter   :string
lock_version  :integer          default(0), not null
name          :string           not null
number        :string           not null
reconcilable  :boolean          default(FALSE), not null
updated_at    :datetime         not null
updater_id    :integer
usages        :text

Constant Summary collapse

@@references =
[]

Class Method Summary collapse

Instance Method Summary collapse

Methods included from Customizable

#custom_value, #set_custom_value, #validate_custom_fields

Methods inherited from Ekylibre::Record::Base

#already_updated?, #check_if_destroyable?, #check_if_updateable?, columns_definition, #customizable?, customizable?, #customized?, #destroyable?, #editable?, has_picture, #human_attribute_name, nomenclature_reflections, #old_record, #others, refers_to, #unsuppress, #updateable?

Methods included from Userstamp::Stampable

included

Methods included from Userstamp::Stamper

included

Class Method Details

.accounting_systemObject

Returns the name of the used accounting system It takes the information in preferences


311
312
313
314
315
316
# File 'app/models/account.rb', line 311

def accounting_system
  @tenant_when_last_cached ||= Ekylibre::Tenant.current
  invalid_cache = @tenant_when_last_cached && @tenant_when_last_cached != Ekylibre::Tenant.current
  @accounting_system = nil if invalid_cache
  @accounting_system ||= Preference[:accounting_system]
end

.accounting_system=(name) ⇒ Object

Returns the name of the used accounting system It takes the information in preferences


325
326
327
328
329
330
# File 'app/models/account.rb', line 325

def accounting_system=(name)
  unless item = Nomen::AccountingSystem[name]
    raise ArgumentError, "The accounting system #{name.inspect} is unknown."
  end
  Preference.set!(:accounting_system, item.name)
end

.accounting_system_name(name = nil) ⇒ Object

Returns the human name of the accounting system


333
334
335
# File 'app/models/account.rb', line 333

def accounting_system_name(name = nil)
  Nomen::AccountingSystem[name || accounting_system].human_name
end

.accounting_systemsObject

Find.all available accounting systems in all languages


338
339
340
# File 'app/models/account.rb', line 338

def accounting_systems
  Nomen::AccountingSystem.all
end

.balance(from, to, list_accounts = []) ⇒ Object

This method loads the balance for a given period.


558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
# File 'app/models/account.rb', line 558

def self.balance(from, to, list_accounts = [])
  balance = []
  conditions = '1=1'
  unless list_accounts.empty?
    conditions += ' AND ' + list_accounts.collect do ||
      "number LIKE '" + .to_s + "%'"
    end.join(' OR ')
  end
  accounts = Account.where(conditions).order('number ASC')
  # solde = 0

  res_debit = 0
  res_credit = 0
  res_balance = 0

  accounts.each do ||
    debit  = .journal_entry_items.sum(:debit,  conditions: { 'r.created_at' => from..to }, joins: "INNER JOIN #{JournalEntry.table_name} AS r ON r.id=#{JournalEntryItem.table_name}.entry_id").to_f
    credit = .journal_entry_items.sum(:credit, conditions: { 'r.created_at' => from..to }, joins: "INNER JOIN #{JournalEntry.table_name} AS r ON r.id=#{JournalEntryItem.table_name}.entry_id").to_f

    compute = HashWithIndifferentAccess.new
    compute[:id] = .id.to_i
    compute[:number] = .number.to_i
    compute[:name] = .name.to_s
    compute[:debit] = debit
    compute[:credit] = credit
    compute[:balance] = debit - credit

    if debit.zero? || credit.zero?
      compute[:debit] = debit
      compute[:credit] = credit
    end

    # if not debit.zero? and not credit.zero?
    #         if compute[:balance] > 0
    #           compute[:debit] = compute[:balance]
    #           compute[:credit] = 0
    #         else
    #           compute[:debit] = 0
    #           compute[:credit] = compute[:balance].abs
    #         end
    #       end

    # if account.number.match /^12/
    # raise StandardError.new compute[:balance].to_s
    # end

    if .number =~ /^(6|7)/
      res_debit += compute[:debit]
      res_credit += compute[:credit]
      res_balance += compute[:balance]
    end

    # solde += compute[:balance] if account.number.match /^(6|7)/
    #      raise StandardError.new solde.to_s if account.number.match /^(6|7)/
    balance << compute
  end
  # raise StandardError.new res_balance.to_s
  balance.each do ||
    if res_balance > 0
      if [:number].to_s =~ /^12/
        [:debit] += res_debit
        [:credit] += res_credit
        [:balance] += res_balance # solde
      end
    elsif res_balance < 0
      if [:number].to_s =~ /^129/
        [:debit] += res_debit
        [:credit] += res_credit
        [:balance] += res_balance # solde
      end
    end
  end
  # raise StandardError.new(balance.inspect)
  balance.compact
end

.clean_range_condition(range, _table_name = nil) ⇒ Object

Clean ranges of accounts Example : 1-3 41 43


360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
# File 'app/models/account.rb', line 360

def clean_range_condition(range, _table_name = nil)
  expression = ''
  if range.present?
    valid_expr = /^\d(\d(\d[0-9A-Z]*)?)?$/
    for expr in range.split(/[^0-9A-Z\-\*]+/)
      if expr =~ /\-/
        start, finish = expr.split(/\-+/)[0..1]
        next unless start < finish && start.match(valid_expr) && finish.match(valid_expr)
        expression << " #{start}-#{finish}"
      elsif expr.match(valid_expr)
        expression << " #{expr}"
      end
    end
  end
  expression.strip
end

.find_in_nomenclature(usage) ⇒ Object

Find account with its usage among all existing account records


225
226
227
228
229
230
231
232
# File 'app/models/account.rb', line 225

def find_in_nomenclature(usage)
   = of_usage(usage).first
  unless 
    item = Nomen::Account[usage]
     = find_by(number: item.send(accounting_system)) if item
  end
  
end

.find_or_create_by_number(*args) ⇒ Object

Create an account with its number (and name)


193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
# File 'app/models/account.rb', line 193

def find_or_create_by_number(*args)
  options = args.extract_options!
  number = args.shift.to_s.strip
  options[:name] ||= args.shift
  numbers = Nomen::Account.items.values.collect { |i| i.send(accounting_system) }
  unless numbers.include?(number)
    while number =~ /0$/
      break if numbers.include?(number)
      number.gsub!(/0$/, '')
    end
  end
  item = Nomen::Account.items.values.detect { |i| i.send(accounting_system) == number }
   = find_by(number: number)
  if 
    if item && !.usages_array.include?(item)
      .usages ||= ''
      .usages << ' ' + item.name.to_s
      .save!
    end
  else
    if item
      options[:name] ||= item.human_name
      options[:usages] ||= ''
      options[:usages] << ' ' + item.name.to_s
    end
    options[:name] ||= number.to_s
     = create!(options.merge(number: number))
  end
  
end

.find_or_import_from_nomenclature(usage) ⇒ Object Also known as: import_from_nomenclature

Find or create an account with its name in accounting system if not exist in DB

Raises:

  • (ArgumentError)

294
295
296
297
298
299
300
301
302
303
304
305
306
# File 'app/models/account.rb', line 294

def find_or_import_from_nomenclature(usage)
  item = Nomen::Account.find(usage)
  raise ArgumentError, "The usage #{usage.inspect} is unknown" unless item
  raise ArgumentError, "The usage #{usage.inspect} is not implemented in #{accounting_system.inspect}" unless item.send(accounting_system)
   = find_in_nomenclature(usage)
   ||= create!(
    name: item.human_name,
    number: item.send(accounting_system),
    debtor: !!item.debtor,
    usages: item.name
  )
  
end

.find_parent_usage(number) ⇒ Object

Find usage in parent account by number


235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
# File 'app/models/account.rb', line 235

def find_parent_usage(number)
  number = number.to_s

  parent_accounts = nil
  items = nil

  max = number.size - 1
  # get usages of nearest existing account by number
  (0..max).to_a.reverse.each do |i|
    n = number[0, i]
    items = Nomen::Account.where(accounting_system.to_sym => n)
    parent_accounts = Account.find_with_regexp(n).where('LENGTH("accounts"."number") <= ?', i).reorder(:number)
    break if parent_accounts.any?
  end

  usages = if parent_accounts && parent_accounts.any? && parent_accounts.first.usages
             parent_accounts.first.usages
           elsif items.present?
             items.first.name
           end

  usages
end

.french_accounting_system?Boolean

FIXME: This is an aberration of internationalization.

Returns:

  • (Boolean)

319
320
321
# File 'app/models/account.rb', line 319

def french_accounting_system?
  %w[fr_pcg82 fr_pcga].include?(accounting_system)
end

.ledger(from, to) ⇒ Object

this method loads the general ledger for.all the accounts.


635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
# File 'app/models/account.rb', line 635

def self.ledger(from, to)
  ledger = []
  accounts = Account.order('number ASC')
  accounts.each do ||
    compute = [] # HashWithIndifferentAccess.new

    journal_entry_items = .journal_entry_items.where('r.created_at' => from..to).joins("INNER JOIN #{JournalEntry.table_name} AS r ON r.id=#{JournalEntryItem.table_name}.entry_id").order('r.number ASC')

    next if journal_entry_items.empty?
    entries = []
    compute << .number.to_i
    compute << .name.to_s
    journal_entry_items.each do |e|
      entry = HashWithIndifferentAccess.new
      entry[:date] = e.entry.created_at
      entry[:name] = e.name.to_s
      entry[:number_entry] = e.entry.number
      entry[:journal] = e.entry.journal.name.to_s
      entry[:credit] = e.credit
      entry[:debit] = e.debit
      entries << entry
      # compute[:journal_entry_items] << entry
    end
    compute << entries
    ledger << compute
  end

  ledger.compact
end

.load_defaultsObject

Load a accounting system


343
344
345
346
347
348
349
350
351
352
353
354
355
356
# File 'app/models/account.rb', line 343

def load_defaults
  transaction do
    # Destroy unused existing accounts
    find_each do ||
      .destroy if .destroyable?
    end
    Nomen::Account.find_each do |item|
      if item.send(accounting_system)
        find_or_import_from_nomenclature(item.name)
      end
    end
  end
  true
end

.range_condition(range, table_name = nil) ⇒ Object

Build an SQL condition to restrein accounts to some ranges Example : 1-3 41 43


379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
# File 'app/models/account.rb', line 379

def range_condition(range, table_name = nil)
  conditions = []
  if range.blank?
    return connection.quoted_true
  else
    range = clean_range_condition(range)
    table = table_name || Account.table_name
    for expr in range.split(/\s+/)
      if expr =~ /\-/
        start, finish = expr.split(/\-+/)[0..1]
        max = [start.length, finish.length].max
        conditions << "SUBSTR(#{table}.number, 1, #{max}) BETWEEN #{connection.quote(start.ljust(max, '0'))} AND #{connection.quote(finish.ljust(max, 'Z'))}"
      else
        conditions << "#{table}.number LIKE #{connection.quote(expr + '%%')}"
      end
    end
  end
  return false if conditions.empty?
  '(' + conditions.join(' OR ') + ')'
end

.reconcilable_prefixesObject

Returns list of reconcilable prefixes defined in preferences


401
402
403
404
405
# File 'app/models/account.rb', line 401

def reconcilable_prefixes
  %i[clients suppliers attorneys].collect do |mode|
    Nomen::Account[mode].send(accounting_system).to_s
  end
end

.reconcilable_regexpObject

Returns a RegExp based on reconcilable_prefixes


408
409
410
# File 'app/models/account.rb', line 408

def reconcilable_regexp
  Regexp.new("^(#{reconcilable_prefixes.join('|')})")
end

.regexp_condition(expr, options = {}) ⇒ Object Also known as: find_with_regexp

Find all account matching with the regexp in a String 123 will take all accounts 123* ^456 will remove all accounts 456*


262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
# File 'app/models/account.rb', line 262

def regexp_condition(expr, options = {})
  table = options[:table] || table_name
  normals = ['(XD)']
  excepts = []
  for prefix in expr.strip.split(/[\,\s]+/)
    code = prefix.gsub(/(^(\-|\^)|[CDX]+$)/, '')
    excepts << code if prefix =~ /^\^\d+$/
    normals << code if prefix =~ /^\-?\d+[CDX]?$/
  end
  conditions = ''
  if normals.any?
    conditions << '(' + normals.sort.collect do |c|
      "#{table}.number LIKE '#{c}%'"
    end.join(' OR ') + ')'
  end
  if excepts.any?
    conditions << ' AND NOT (' + excepts.sort.collect do |c|
      "#{table}.number LIKE '#{c}%'"
    end.join(' OR ') + ')'
  end
  conditions
end

Instance Method Details

#account_statement_reporting(options = {}, non_letter) ⇒ Object

this method generate a dataset for one account


666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
# File 'app/models/account.rb', line 666

def (options = {}, non_letter)
  report = HashWithIndifferentAccess.new
  report[:items] = []
  items = if non_letter == 'true'
    journal_entry_items.where("letter LIKE ? OR letter IS ?", "%*%", nil)
  else
    journal_entry_items
  end
  items.order(:printed_on).includes(entry: [:sales, :purchases]).find_each do |item|
    i = HashWithIndifferentAccess.new
    i[:account_number] = number
    i[:name] = name
    i[:printed_on] = item.printed_on.l(format: '%d/%m/%Y')
    i[:journal_entry_items_number] = item.entry_number
    i[:sales_code] = item.entry.sales.pluck(:codes).first&.values&.join(', ') if item.entry.sales.present?
    i[:purchase_reference_number] = item.entry.purchases.pluck(:reference_number).join(', ') if item.entry.purchases.present?
    i[:letter] = item.letter
    i[:real_debit] = item.real_debit
    i[:real_credit] = item.real_credit
    report[:items] << i
  end
  report
end

#balanced_letter?(letter) ⇒ Boolean

Check if the balance of the entry items of the given letter is zero.

Returns:

  • (Boolean)

485
486
487
488
489
# File 'app/models/account.rb', line 485

def balanced_letter?(letter)
  items = journal_entry_items.where('letter = ?', letter.to_s)
  return true if items.count.zero?
  items.sum('debit - credit').to_f.zero?
end

#journal_entry_items_calculate(column, started_at, stopped_at, operation = :sum) ⇒ Object

def journal_entry_items_between(started_at, stopped_at)

self.journal_entry_items.joins("JOIN #{JournalEntry.table_name} AS journal_entries ON (journal_entries.id=entry_id)").where(printed_on: started_at..stopped_at).order("printed_on, journal_entries.id, #{JournalEntryItem.table_name}.id")

end


552
553
554
555
# File 'app/models/account.rb', line 552

def journal_entry_items_calculate(column, started_at, stopped_at, operation = :sum)
  column = (column == :balance ? "#{JournalEntryItem.table_name}.real_debit - #{JournalEntryItem.table_name}.real_credit" : "#{JournalEntryItem.table_name}.real_#{column}")
  journal_entry_items.where(printed_on: started_at..stopped_at).calculate(operation, column)
end

#mark(item_ids, letter = nil) ⇒ Object

Mark entry items with the given letter. If no letter given, it uses a new letter. Don't mark unless.all the marked items will be balanced together


459
460
461
462
463
464
465
466
467
# File 'app/models/account.rb', line 459

def mark(item_ids, letter = nil)
  conditions = ['id IN (?) AND (letter IS NULL OR LENGTH(TRIM(letter)) <= 0 OR (letter SIMILAR TO ?))', item_ids, '[A-z]*\*?']
  items = journal_entry_items.where(conditions)
  return nil unless item_ids.size > 1 && items.count == item_ids.size &&
                    items.collect { |l| l.debit - l.credit }.sum.to_f.zero?
  letter ||= new_letter
  journal_entry_items.where(conditions).update_all(letter: letter)
  letter
end

#mark!(item_ids, letter = nil) ⇒ Object

Mark entry items with the given letter, even when the items are not balanced together. If no letter given, it uses a new letter.


471
472
473
474
475
476
477
# File 'app/models/account.rb', line 471

def mark!(item_ids, letter = nil)
  return nil unless item_ids.is_a?(Array) && item_ids.any?
  letter ||= new_letter
  conditions = ['id IN (?) AND (letter IS NULL OR LENGTH(TRIM(COALESCE(letter, \'\'))) <= 0 OR letter SIMILAR TO \'[A-z]+\\*\')', item_ids]
  journal_entry_items.where(conditions).update_all(letter: letter)
  letter
end

#mark_entries(*journal_entries) ⇒ Object

Finds entry items to mark, checks their “markability” and if.all valids mark.all with a new letter or the first defined before


452
453
454
455
# File 'app/models/account.rb', line 452

def mark_entries(*journal_entries)
  ids = journal_entries.flatten.compact.collect(&:id)
  mark(journal_entry_items.where(entry_id: ids).map(&:id))
end

#merge_with(other) ⇒ Object

Merge given account into self. Given account is destroyed at the end, self remains.


493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
# File 'app/models/account.rb', line 493

def merge_with(other)
  Ekylibre::Record::Base.transaction do
    # Relations with DB approach to prevent missing reflection
    connection = self.class.connection
    base_class = self.class.base_class
    base_model = base_class.name.underscore.to_sym
    models_set = ([base_class] + base_class.descendants)
    models_group = '(' + models_set.map do |model|
      "'#{model.name}'"
    end.join(', ') + ')'
    Ekylibre::Schema.tables.each do |table, columns|
      columns.each do |_name, column|
        next unless column.references
        if column.references.is_a?(String) # Polymorphic
          connection.execute("UPDATE #{table} SET #{column.name}=#{id} WHERE #{column.name}=#{other.id} AND #{column.references} IN #{models_group}")
        elsif column.references == base_model # Straight
          connection.execute("UPDATE #{table} SET #{column.name}=#{id} WHERE #{column.name}=#{other.id}")
        end
      end
    end

    # Update attributes
    self.class.columns_definition.each do |attr, column|
      next if column.references
      send("#{attr}=", other.send(attr)) if send(attr).blank?
    end

    # Update custom fields
    self.custom_fields ||= {}
    other.custom_fields ||= {}
    Entity.custom_fields.each do |custom_field|
      attr = custom_field.column_name
      if self.custom_fields[attr].blank? && other.custom_fields[attr].present?
        self.custom_fields[attr] = other.custom_fields[attr]
      end
    end

    save!
    other.destroy!
  end
end

#new_letterObject


443
444
445
446
447
448
# File 'app/models/account.rb', line 443

def new_letter
  letter = last_letter
  letter = letter.blank? ? 'A' : letter.succ
  update_column(:last_letter, letter)
  letter
end

#reconcilable_entry_items(period, started_at, stopped_at, options = {}) ⇒ Object


425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
# File 'app/models/account.rb', line 425

def reconcilable_entry_items(period, started_at, stopped_at, options = {})
  relation_name = 'journal_entry_items'

  lettered_condition = "1=1"

  if options[:hide_lettered]
    lettered_condition += ' AND('
    lettered_condition += "#{JournalEntryItem.table_name}.letter IS NULL"
    lettered_condition += " OR #{JournalEntryItem.table_name}.letter ILIKE '%*'"
    lettered_condition << ')'
  end

  journal_entry_items
    .where(JournalEntry.period_condition(period, started_at, stopped_at, relation_name))
    .where(lettered_condition)
    .reorder(relation_name + '.printed_on, ' + relation_name + '.real_credit, ' + relation_name + '.real_debit')
end

#reconcilableable?Boolean

Check if the account is a third account and therefore returns if it should be reconcilable

Returns:

  • (Boolean)

421
422
423
# File 'app/models/account.rb', line 421

def reconcilableable?
  (number.to_s.match(self.class.reconcilable_regexp) ? true : false)
end

#totalsObject

Compute debit, credit, balance, balance_debit and balance_credit of the account with.all the entry items


537
538
539
540
541
542
543
544
545
546
# File 'app/models/account.rb', line 537

def totals
  hash = {}
  hash[:debit]  = journal_entry_items.sum(:debit)
  hash[:credit] = journal_entry_items.sum(:credit)
  hash[:balance_debit] = 0.0
  hash[:balance_credit] = 0.0
  hash[:balance] = (hash[:debit] - hash[:credit]).abs
  hash["balance_#{hash[:debit] > hash[:credit] ? 'debit' : 'credit'}".to_sym] = hash[:balance]
  hash
end

#unmark(letter) ⇒ Object

Unmark.all the entry items concerned by the letter


480
481
482
# File 'app/models/account.rb', line 480

def unmark(letter)
  journal_entry_items.where(letter: letter).update_all(letter: nil)
end

#usages_arrayObject

Returns list of usages as an array of usage items from the nomenclature


414
415
416
417
418
# File 'app/models/account.rb', line 414

def usages_array
  usages.to_s.strip.split(/[\,\s]/).collect do |i|
    Nomen::Account[i]
  end.compact
end