Class: Journal

Inherits:
Ekylibre::Record::Base show all
Includes:
Customizable
Defined in:
app/models/journal.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: journals

accountant_id                      :integer
closed_on                          :date             not null
code                               :string           not null
created_at                         :datetime         not null
creator_id                         :integer
currency                           :string           not null
custom_fields                      :jsonb
id                                 :integer          not null, primary key
lock_version                       :integer          default(0), not null
name                               :string           not null
nature                             :string           not null
updated_at                         :datetime         not null
updater_id                         :integer
used_for_affairs                   :boolean          default(FALSE), not null
used_for_gaps                      :boolean          default(FALSE), not null
used_for_permanent_stock_inventory :boolean          default(FALSE), not null
used_for_tax_declarations          :boolean          default(FALSE), not null
used_for_unbilled_payables         :boolean          default(FALSE), not null

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

.create_one!(nature, currency, attributes = {}) ⇒ Object


208
209
210
211
212
213
214
215
216
217
218
# File 'app/models/journal.rb', line 208

def create_one!(nature, currency, attributes = {})
  attributes[:name] = "enumerize.journal.nature.#{nature}".t + ' ' + currency
  if Journal.find_by(name: attributes[:name])
    attributes[:name] += ' 2'
    attributes[:name].succ! while Journal.find_by(name: attributes[:name])
  end
  attributes[:code] ||= '??'
  attributes[:nature] = nature
  attributes[:currency] = currency
  Journal.create!(attributes)
end

.get(name) ⇒ Object

Returns the default journal from preferences Creates the journal if not exists

Raises:

  • (ArgumentError)

152
153
154
155
156
157
158
159
160
161
162
# File 'app/models/journal.rb', line 152

def get(name)
  name = name.to_s
  pref_name = "#{name}_journal"
  raise ArgumentError, "Unvalid journal name: #{name.inspect}" unless self.class.preferences_reference.key? pref_name
  unless journal = preferred(pref_name)
    journal = journals.find_by(nature: name)
    journal ||= journals.create!(name: tc("default.journals.#{name}"), nature: name, currency: default_currency)
    prefer!(pref_name, journal)
  end
  journal
end

.load_defaultsObject

Load default journal if not exist


221
222
223
224
225
226
227
228
229
230
231
232
233
# File 'app/models/journal.rb', line 221

def load_defaults
  nature.values.each do |nature|
    next if find_by(nature: nature)
    financial_year = FinancialYear.first_of_all
    closed_on = financial_year ? (financial_year.started_on - 1) : Date.new(1899, 12, 31).end_of_month
    create!(
      name: "enumerize.journal.nature.#{nature}".t,
      nature: nature,
      currency: Preference[:currency],
      closed_on: closed_on
    )
  end
end

.sum_entry_items(expression, options = {}) ⇒ Object

Computes the value of list of accounts in a String Examples:

132 !13245 !1325 D, - 52 56, 975 C

'!' exclude computation '+' does nothing. Permits to explicit direction '-' negates values Computation:

B: Balance (= Debit - Credit). Default computation.
C: Credit balance if positive
D: Debit balance if positive

364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
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
# File 'app/models/journal.rb', line 364

def self.sum_entry_items(expression, options = {})
  conn = ActiveRecord::Base.connection
  journal_entry_items = 'jei'
  journal_entries = 'je'
  journals = 'j'
  accounts = 'a'

  journal_entries_states = ''
  if options[:states]
    journal_entries_states = ' AND ' + JournalEntry.state_condition(options[:states], journal_entries)
  end

  from_where = " FROM #{JournalEntryItem.table_name} AS #{journal_entry_items} JOIN #{Account.table_name} AS #{accounts} ON (account_id=#{accounts}.id) JOIN #{JournalEntry.table_name} AS #{journal_entries} ON (entry_id=#{journal_entries}.id)"
  if options[:unwanted_journal_nature]
    from_where << " JOIN #{Journal.table_name} AS #{journals} ON (#{journal_entries}.journal_id=#{journals}.id)"
    from_where << " WHERE #{journals}.nature NOT IN (" + options[:unwanted_journal_nature].map { |c| "'#{c}'" }.join(', ') + ')'
  else
    from_where << ' WHERE true'
  end
  if options[:started_on] || options[:stopped_on]
    from_where << ' AND ' + JournalEntry.period_condition(:interval, options[:started_on], options[:stopped_on], journal_entries)
  end

  values = expression.split(/\,/).collect do |expr|
    words = expr.strip.split(/\s+/)
    direction = 1
    direction = -1 if words.first =~ /^(\+|\-)$/ && words.shift == '-'
    mode = words.last =~ /^[BCD]$/ ? words.delete_at(-1) : 'B'
    accounts_range = {}
    words.map do |word|
      position = (word =~ /\!/ ? :exclude : :include)
      strict = (word =~ /\@/)
      word.gsub!(/^[\!\@]+/, '')
      condition = "#{accounts}.number " + (strict ? "= '#{word}'" : "LIKE '#{word}%'")
      accounts_range[position] ||= []
      accounts_range[position] << condition
    end.join
    query = "SELECT SUM(#{journal_entry_items}.absolute_debit) AS debit, SUM(#{journal_entry_items}.absolute_credit) AS credit"
    query << from_where
    query << journal_entries_states
    query << " AND (#{accounts_range[:include].join(' OR ')})" if accounts_range[:include]
    query << " AND NOT (#{accounts_range[:exclude].join(' OR ')})" if accounts_range[:exclude]
    row = conn.select_rows(query).first
    debit =  row[0].blank? ? 0.0 : row[0].to_d
    credit = row[1].blank? ? 0.0 : row[1].to_d
    if mode == 'C'
      direction * (credit > debit ? credit - debit : 0)
    elsif mode == 'D'
      direction * (debit > credit ? debit - credit : 0)
    else
      direction * (debit - credit)
    end
  end
  values.sum
end

.trial_balance(options = {}) ⇒ Object

Compute a trial balance with many options

  • :started_on Use journal entries printed on after started_on

  • :stopped_on Use journal entries printed on before stopped_on

  • :draft Use draft journal entry_items

  • :confirmed Use confirmed journal entry_items

  • :closed Use closed journal entry_items

  • :accounts Select ranges of accounts

  • :centralize Select account's prefixe which permits to centralize


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
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
# File 'app/models/journal.rb', line 428

def self.trial_balance(options = {})
  conn = ActiveRecord::Base.connection
  journal_entry_items = 'jei'
  journal_entries = 'je'
  accounts = 'a'

  journal_entries_states = ' AND (' + JournalEntry.state_condition(options[:states], journal_entries) + ')'

   = Account.range_condition(options[:accounts], accounts)
   = ' AND (' +  + ')' if 

  centralize = options[:centralize].to_s.strip.split(/[^A-Z0-9]+/)
  centralized = '(' + centralize.collect { |c| "#{accounts}.number LIKE #{conn.quote(c + '%')}" }.join(' OR ') + ')'

  from_where  = " FROM #{JournalEntryItem.table_name} AS #{journal_entry_items} JOIN #{Account.table_name} AS #{accounts} ON (account_id=#{accounts}.id) JOIN #{JournalEntry.table_name} AS #{journal_entries} ON (entry_id=#{journal_entries}.id)"
  from_where += ' WHERE (' + JournalEntry.period_condition(options[:period], options[:started_on], options[:stopped_on], journal_entries) + ')'

  # Total
  items = []
  query = "SELECT '', -1, sum(COALESCE(#{journal_entry_items}.debit, 0)), sum(COALESCE(#{journal_entry_items}.credit, 0)), sum(COALESCE(#{journal_entry_items}.debit, 0)) - sum(COALESCE(#{journal_entry_items}.credit, 0)), '#{'Z' * 16}' AS skey"
  query << from_where
  query << journal_entries_states
  query <<  unless .nil?
  items += conn.select_rows(query)

  # Sub-totals
  options.select { |k, v| k.to_s.match(/^level_\d+$/) && v.to_i == 1 }.each do |name, _value|
    level = name.split(/\_/)[-1].to_i
    query = "SELECT SUBSTR(#{accounts}.number, 1, #{level}) AS subtotal, -2, sum(COALESCE(#{journal_entry_items}.debit, 0)), sum(COALESCE(#{journal_entry_items}.credit, 0)), sum(COALESCE(#{journal_entry_items}.debit, 0)) - sum(COALESCE(#{journal_entry_items}.credit, 0)), SUBSTR(#{accounts}.number, 1, #{level})||'#{'Z' * (16 - level)}' AS skey"
    query << from_where
    query << journal_entries_states
    query <<  unless .nil?
    query << " AND LENGTH(#{accounts}.number) >= #{level}"
    query << ' GROUP BY subtotal'
    items += conn.select_rows(query)
  end

  # NOT centralized accounts (default)
  query = "SELECT #{accounts}.number, #{accounts}.id AS account_id, sum(COALESCE(#{journal_entry_items}.debit, 0)), sum(COALESCE(#{journal_entry_items}.credit, 0)), sum(COALESCE(#{journal_entry_items}.debit, 0)) - sum(COALESCE(#{journal_entry_items}.credit, 0)), #{accounts}.number AS skey"
  query << from_where
  query << journal_entries_states
  query <<  unless .nil?
  query << " AND NOT #{centralized}" unless centralize.empty?
  query << " GROUP BY #{accounts}.id, #{accounts}.number"
  query << " ORDER BY #{accounts}.number"
  items += conn.select_rows(query)

  # Centralized accounts
  for prefix in centralize
    query = "SELECT SUBSTR(#{accounts}.number, 1, #{prefix.size}) AS centralize, -3, sum(COALESCE(#{journal_entry_items}.debit, 0)), sum(COALESCE(#{journal_entry_items}.credit, 0)), sum(COALESCE(#{journal_entry_items}.debit, 0)) - sum(COALESCE(#{journal_entry_items}.credit, 0)), #{conn.quote(prefix)} AS skey"
    query << from_where
    query << journal_entries_states
    query <<  unless .nil?
    query << " AND #{accounts}.number LIKE #{conn.quote(prefix + '%')}"
    query << ' GROUP BY centralize'
    items += conn.select_rows(query)
  end

  items.sort_by { |a| a[5] }
end

.used_for_affairsObject


164
165
166
# File 'app/models/journal.rb', line 164

def used_for_affairs
  find_by(used_for_affairs: true)
end

.used_for_gaps!(attributes = {}) ⇒ Object


168
169
170
171
172
173
174
175
176
# File 'app/models/journal.rb', line 168

def used_for_gaps!(attributes = {})
  attributes[:name] ||= :profits_and_losses.tl
  attributes[:code] ||= '??'
  attributes[:nature] ||= :various
  attributes[:used_for_gaps] = true
  journal = Journal.find_by(used_for_gaps: true)
  journal ||= Journal.find_by(attributes.slice(:name))
  journal || Journal.create!(attributes)
end

.used_for_permanent_stock_inventory!(attributes = {}) ⇒ Object


188
189
190
191
192
193
194
195
196
# File 'app/models/journal.rb', line 188

def used_for_permanent_stock_inventory!(attributes = {})
  attributes[:name] ||= :permanent_stock_inventory.tl
  attributes[:code] ||= '??'
  attributes[:nature] ||= :stocks
  attributes[:used_for_permanent_stock_inventory] = true
  journal = Journal.find_by(used_for_permanent_stock_inventory: true)
  journal ||= Journal.find_by(attributes.slice(:name))
  journal || Journal.create!(attributes)
end

.used_for_tax_declarations!(attributes = {}) ⇒ Object


178
179
180
181
182
183
184
185
186
# File 'app/models/journal.rb', line 178

def used_for_tax_declarations!(attributes = {})
  attributes[:name] ||= :taxes.tl
  attributes[:code] ||= '??'
  attributes[:nature] ||= :various
  attributes[:used_for_tax_declarations] = true
  journal = Journal.find_by(used_for_tax_declarations: true)
  journal ||= Journal.find_by(attributes.slice(:name))
  journal || Journal.create!(attributes)
end

.used_for_unbilled_payables!(attributes = {}) ⇒ Object


198
199
200
201
202
203
204
205
206
# File 'app/models/journal.rb', line 198

def used_for_unbilled_payables!(attributes = {})
  attributes[:name] ||= :unbilled_payables.tl
  attributes[:code] ||= '??'
  attributes[:nature] ||= :various
  attributes[:used_for_unbilled_payables] = true
  journal = Journal.find_by(used_for_unbilled_payables: true)
  journal ||= Journal.find_by(attributes.slice(:name))
  journal || Journal.create!(attributes)
end

Instance Method Details

#accountant_has_financial_year_with_opened_exchange?(accountant_or_accountant_id) ⇒ Boolean

Returns:

  • (Boolean)

348
349
350
351
# File 'app/models/journal.rb', line 348

def accountant_has_financial_year_with_opened_exchange?(accountant_or_accountant_id)
  accountant = accountant_or_accountant_id.is_a?(Integer) ? Entity.find(accountant_or_accountant_id) : accountant_or_accountant_id
  accountant && accountant.financial_year_with_opened_exchange?
end

#booked_for_accountant?Boolean

Returns:

  • (Boolean)

344
345
346
# File 'app/models/journal.rb', line 344

def booked_for_accountant?
  accountant
end

#closable?(new_closed_on = nil) ⇒ Boolean

Test if journal is closable

Returns:

  • (Boolean)

242
243
244
245
246
247
248
# File 'app/models/journal.rb', line 242

def closable?(new_closed_on = nil)
  new_closed_on ||= (Time.zone.today << 1).end_of_month
  return false if booked_for_accountant?
  return false if new_closed_on.end_of_month != new_closed_on
  return false if new_closed_on < self.closed_on
  true
end

#close(new_closed_on) ⇒ Object

this method closes a journal.


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

def close(new_closed_on)
  if new_closed_on != new_closed_on.end_of_month
    errors.add(:closed_on, :end_of_month, closed_on: new_closed_on.l)
  end
  if entry_items.joins("JOIN #{JournalEntry.table_name} AS journal_entries ON (entry_id=journal_entries.id)").where(state: :draft).where(printed_on: (self.closed_on + 1)..new_closed_on).any?
    errors.add(:closed_on, :draft_entry_items, closed_on: new_closed_on.l)
  end
  return false unless errors.empty?
  ActiveRecord::Base.transaction do
    entries.where(printed_on: (self.closed_on + 1)..new_closed_on).find_each(&:close)
    update_column(:closed_on, new_closed_on)
  end
  true
end

#close!(closed_on) ⇒ Object

Close a journal and force validation of draft entries to the given date


278
279
280
281
282
283
284
285
286
287
# File 'app/models/journal.rb', line 278

def close!(closed_on)
  finished = false
  ActiveRecord::Base.transaction do
    JournalEntryItem.where(journal_id: self.id).where('printed_on < ?', closed_on).where.not(state: :closed).update_all(state: :closed)
    JournalEntry.where(journal_id: self.id).where('printed_on < ?', closed_on).where.not(state: :closed).update_all(state: :closed)
    update_column(:closed_on, closed_on)
    finished = true
  end
  finished
end

#closures(noticed_on = nil) ⇒ Object


250
251
252
253
254
255
256
257
258
259
# File 'app/models/journal.rb', line 250

def closures(noticed_on = nil)
  noticed_on ||= Time.zone.today
  array = []
  date = [(self.closed_on + 1), FinancialYear.last_closure].compact.max.end_of_month
  while date < noticed_on
    array << date
    date = (date + 1).end_of_month
  end
  array
end

#entry_items_between(started_on, stopped_on) ⇒ Object


331
332
333
# File 'app/models/journal.rb', line 331

def entry_items_between(started_on, stopped_on)
  entry_items.joins("JOIN #{JournalEntry.table_name} AS journal_entries ON (journal_entries.id=entry_id)").where(printed_on: started_on..stopped_on).order('printed_on, journal_entries.id, journal_entry_items.id')
end

#entry_items_calculate(column, started_on, stopped_on, operation = :sum) ⇒ Object


335
336
337
338
# File 'app/models/journal.rb', line 335

def entry_items_calculate(column, started_on, stopped_on, operation = :sum)
  column = (column == :balance ? "#{JournalEntryItem.table_name}.real_debit - #{JournalEntryItem.table_name}.real_credit" : "#{JournalEntryItem.table_name}.real_#{column}")
  entry_items.joins("JOIN #{JournalEntry.table_name} AS journal_entries ON (journal_entries.id=entry_id)").where(printed_on: started_on..stopped_on).calculate(operation, column)
end

#last_entries(period, count = 30) ⇒ Object

this method searches the last entries according to a number.


327
328
329
# File 'app/models/journal.rb', line 327

def last_entries(period, count = 30)
  period.entries.order("LPAD(number, 20, '0') DESC").limit(count)
end

#next_numberObject

Takes the very last created entry in the journal to generate the entry number


314
315
316
317
318
319
320
321
322
323
324
# File 'app/models/journal.rb', line 314

def next_number
  entry = entries.order(id: :desc).first
  number = entry ? entry.number : code.to_s.upcase + '000000'
  number.gsub!(/(9+)\z/, '0\1') if number =~ /[^\d]9+\z/
  number.succ!
  while entries.where(number: number).any?
    number.gsub!(/(9+)\z/, '0\1') if number =~ /[^\d]9+\z/
    number.succ!
  end
  number
end

#reopen(closed_on) ⇒ Object


305
306
307
308
309
310
311
# File 'app/models/journal.rb', line 305

def reopen(closed_on)
  ActiveRecord::Base.transaction do
    entries.where(printed_on: (closed_on + 1)..self.closed_on).find_each(&:reopen)
    update_column :closed_on, closed_on
  end
  true
end

#reopenable?Boolean

Returns:

  • (Boolean)

289
290
291
# File 'app/models/journal.rb', line 289

def reopenable?
  !booked_for_accountant? && reopenings.any?
end

#reopeningsObject


293
294
295
296
297
298
299
300
301
302
303
# File 'app/models/journal.rb', line 293

def reopenings
  year = FinancialYear.current
  return [] if year.nil?
  array = []
  date = year.started_on - 1
  while date < self.closed_on
    array << date
    date = (date + 1).end_of_month
  end
  array
end

#various_without_cash?Boolean

Returns:

  • (Boolean)

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

def various_without_cash?
  various? && cashes.empty?
end

#writable_on?(printed_on) ⇒ Boolean

Returns:

  • (Boolean)

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

def writable_on?(printed_on)
  printed_on > self.closed_on &&
    FinancialYearExchange.where('? BETWEEN started_on AND stopped_on', printed_on).empty?
end