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-2016 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?, attr_readonly_with_conditions, #check_if_destroyable?, #check_if_updateable?, columns_definition, complex_scopes, customizable?, #customizable?, #customized?, #destroyable?, #editable?, has_picture, #human_attribute_name, human_attribute_name_with_id, nomenclature_reflections, #old_record, #others, refers_to, scope_with_registration, simple_scopes, #updateable?

Class Method Details

.accounting_systemObject

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


218
219
220
# File 'app/models/account.rb', line 218

def accounting_system
  Preference[:accounting_system]
end

.accounting_system=(name) ⇒ Object

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


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

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


236
237
238
# File 'app/models/account.rb', line 236

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

.accounting_systemsObject

Find.all available accounting systems in all languages


241
242
243
# File 'app/models/account.rb', line 241

def accounting_systems
  Nomen::AccountingSystem.all
end

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

This method loads the balance for a given period.


391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
# File 'app/models/account.rb', line 391

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


261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
# File 'app/models/account.rb', line 261

def clean_range_condition(range, _table_name = nil)
  expression = ''
  unless range.blank?
    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


160
161
162
163
164
165
166
167
# File 'app/models/account.rb', line 160

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

.find_or_create_by_number(*args) ⇒ Object

Create an account with its number (and name)


130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
# File 'app/models/account.rb', line 130

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) }
  while number =~ /0$/
    break if numbers.include?(number)
    number.gsub!(/0$/, '')
  end unless numbers.include?(number)
  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


204
205
206
207
208
209
210
211
212
213
# File 'app/models/account.rb', line 204

def find_or_import_from_nomenclature(usage)
  if  = find_in_nomenclature(usage)
    return 
  elsif item = Nomen::Account.find(usage)
     = create!(name: item.human_name, number: item.send(accounting_system), debtor: !!item.debtor, usages: item.name)
    return 
  else
    raise ArgumentError, "The usage #{usage.inspect} is unknown"
  end
end

.french_accounting_system?Boolean

Returns:

  • (Boolean)

222
223
224
# File 'app/models/account.rb', line 222

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.


468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
# File 'app/models/account.rb', line 468

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


246
247
248
249
250
251
252
253
254
255
256
257
# File 'app/models/account.rb', line 246

def load_defaults
  transaction do
    # Destroy unused existing accounts
    find_each do ||
      .destroy if .destroyable?
    end
    Nomen::Account.find_each do |item|
      find_or_import_from_nomenclature(item.name)
    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


280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
# File 'app/models/account.rb', line 280

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
  '(' + conditions.join(' OR ') + ')'
end

.reconcilable_prefixesObject

Returns list of reconcilable prefixes defined in preferences


301
302
303
304
305
# File 'app/models/account.rb', line 301

def reconcilable_prefixes
  [: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


308
309
310
# File 'app/models/account.rb', line 308

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*


172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
# File 'app/models/account.rb', line 172

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

#balanced_letter?(letter) ⇒ Boolean

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

Returns:

  • (Boolean)

362
363
364
365
366
# File 'app/models/account.rb', line 362

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


385
386
387
388
# File 'app/models/account.rb', line 385

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


347
348
349
350
351
352
353
354
# File 'app/models/account.rb', line 347

def mark(item_ids, letter = nil)
  conditions = ['id IN (?) AND (letter IS NULL OR LENGTH(TRIM(letter)) <= 0)', item_ids]
  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_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


340
341
342
343
# File 'app/models/account.rb', line 340

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

#new_letterObject


329
330
331
332
333
334
335
336
# File 'app/models/account.rb', line 329

def new_letter
  letter = last_letter
  letter = letter.blank? ? 'AAA' : letter.succ
  update_column(:last_letter, letter)
  # item = self.journal_entry_items.where("LENGTH(TRIM(letter)) > 0").order("letter DESC").first
  # return (item ? item.letter.succ : "AAA")
  letter
end

#reconcilable_entry_items(period, started_at, stopped_at) ⇒ Object


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

def reconcilable_entry_items(period, started_at, stopped_at)
  journal_entry_items.joins("JOIN #{JournalEntry.table_name} AS je ON (entry_id=je.id)").where(JournalEntry.period_condition(period, started_at, stopped_at, 'je')).reorder('letter DESC, je.printed_on')
end

#reconcilableable?Boolean

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

Returns:

  • (Boolean)

321
322
323
# File 'app/models/account.rb', line 321

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


370
371
372
373
374
375
376
377
378
379
# File 'app/models/account.rb', line 370

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


357
358
359
# File 'app/models/account.rb', line 357

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


314
315
316
317
318
# File 'app/models/account.rb', line 314

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