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-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: journals

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

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

.get(name) ⇒ Object

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

Raises:

  • (ArgumentError)

114
115
116
117
118
119
120
121
122
123
124
# File 'app/models/journal.rb', line 114

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) unless journal
    prefer!(pref_name, journal)
  end
  journal
end

.load_defaultsObject

Load default journal if not exist


131
132
133
134
135
136
137
138
139
140
141
142
143
# File 'app/models/journal.rb', line 131

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

255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
# File 'app/models/journal.rb', line 255

def self.sum_entry_items(expression, options = {})
  conn = ActiveRecord::Base.connection
  journal_entry_items = 'jei'
  journal_entries = 'je'
  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)"
  from_where << ' WHERE true'
  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


313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
# File 'app/models/journal.rb', line 313

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) + ')'

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

  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 << 
  items += conn.select_rows(query)

  # Sub-totals
  for name, value in options.select { |k, v| k.to_s.match(/^level_\d+$/) && v.to_i == 1 }
    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 << 
    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 << 
  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 << 
    query << " AND #{accounts}.number LIKE #{conn.quote(prefix + '%')}"
    query << ' GROUP BY centralize'
    items += conn.select_rows(query)
  end

  items.sort { |a, b| a[5] <=> b[5] }
end

.used_for_affairsObject


126
127
128
# File 'app/models/journal.rb', line 126

def used_for_affairs
  find_by(used_for_affairs: true)
end

Instance Method Details

#closable?(closed_on = nil) ⇒ Boolean

Test if journal is closable

Returns:

  • (Boolean)

147
148
149
150
151
152
153
# File 'app/models/journal.rb', line 147

def closable?(closed_on = nil)
  closed_on ||= Time.zone.today
  self.class.where(id: id).update_all(closed_on: Date.civil(1900, 12, 31)) if self.closed_on.nil?
  reload
  return false unless (closed_on << 1).end_of_month > self.closed_on
  true
end

#close(closed_on) ⇒ Object

this method closes a journal.


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

def close(closed_on)
  errors.add(:closed_on, :end_of_month) if self.closed_on != self.closed_on.end_of_month
  errors.add(:closed_on, :draft_entry_items, closed_on: closed_on.l) 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)..closed_on).any?
  return false unless errors.empty?
  ActiveRecord::Base.transaction do
    entries.where(printed_on: (self.closed_on + 1)..closed_on).find_each(&:close)
    update_column(:closed_on, closed_on)
  end
  true
end

#close!(closed_on) ⇒ Object

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


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

def close!(closed_on)
  finished = false
  ActiveRecord::Base.transaction do
    entries.where(printed_on: (self.closed_on + 1)..closed_on, state: :draft).find_each(&:confirm)
    entries.where(printed_on: (self.closed_on + 1)..closed_on, state: :confirmed).find_each(&:close)
    update_column(:closed_on, closed_on)
    finished = true
  end
  finished
end

#closures(noticed_on = nil) ⇒ Object


155
156
157
158
159
160
161
162
163
164
# File 'app/models/journal.rb', line 155

def closures(noticed_on = nil)
  noticed_on ||= Time.zone.today
  array = []
  date = (self.closed_on + 1).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


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

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


239
240
241
242
# File 'app/models/journal.rb', line 239

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.


231
232
233
# File 'app/models/journal.rb', line 231

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


218
219
220
221
222
223
224
225
226
227
228
# File 'app/models/journal.rb', line 218

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


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

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

#reopenable?Boolean

Returns:

  • (Boolean)

190
191
192
193
# File 'app/models/journal.rb', line 190

def reopenable?
  return false unless reopenings.any?
  true
end

#reopeningsObject


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

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