1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
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
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
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
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
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
304
305
306
307
308
309
310
311
312
313
314
315
316
|
# encoding: utf-8
##############################################################################
#
# OpenERP, Open Source Management Solution
# Copyright (C) 2011 MSF, TeMPO consulting
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) 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 General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program. If not, see <http://www.gnu.org/licenses/>.
#
##############################################################################
from osv import osv
import datetime
class msf_budget_tools(osv.osv):
_name = "msf.budget.tools"
def get_expense_accounts(self, cr, uid, context=None):
"""
Get all "is_analytic_addicted" accounts except if context notify to only use expense ones.
By using this method you also retrieve ALL parents EXCEPT the first one: MSF account.
"""
# Checks
if context is None:
context = {}
# Prepare some values
res = []
account_obj = self.pool.get('account.account')
# get the last parent
top_ids = account_obj.search(cr, uid, [('code', '=', 'MSF')], context=context)
# get normal analytic-a-holic accounts. UTP-944: only expenses ones if "only_expenses" in context. Do not include Extra-accounting accounts and incomes one.
domain = [('type', '!=', 'view'), ('user_type_report_type', '!=', 'none')]
if context.get('only_expenses', False) and context.get('only_expenses') is True:
domain += [('user_type_code', '=', 'expense'), ('user_type_report_type', '=', 'expense')]
else:
domain += [('is_analytic_addicted', '=', True)]
account_ids = account_obj.search(cr, uid, domain, context=context)
if account_ids:
parent_ids = account_obj._get_parent_of(cr, uid, account_ids, context=context)
if parent_ids:
res = [x for x in parent_ids if x not in top_ids]
return res
def get_budget_line_template(self, cr, uid, context=None):
"""
Create a template that contains all budget line main values for a new budget.
"""
# Some checks
if context is None:
context = {}
# Prepare some values
res = []
# Search all income/expense accounts (except if context contains "only_expenses" set to True)
account_ids = self.get_expense_accounts(cr, uid, context=context)
if not account_ids:
return []
# We use a SQL request to keep an order of accounts regarding their parents so that parents could be created before their childs.
sql = """
SELECT id, CASE WHEN type != 'view' THEN 'normal' ELSE 'view' END AS account_type, parent_id
FROM account_account
WHERE id IN %s
ORDER BY parent_id ASC"""
cr.execute(sql, (tuple(account_ids),))
if not cr.rowcount:
raise osv.except_osv(_('Error'), _('Unable to find needed info.'))
tmp_res = cr.fetchall()
# We take destination ids for a given account (and we make a dictionnary to be quickly used)
accounts = self.pool.get('account.account').read(cr, uid, account_ids, ['destination_ids'], context=context)
destinations = {}
for account in accounts:
destinations[account.get('id')] = account.get('destination_ids')
# We then create the final result with all needed elements
for line_id, line_type, parent_id in tmp_res:
line_vals = {
'id': line_id,
'type': line_type,
'parent_id': parent_id,
'destination_ids': []
}
if line_id in destinations:
line_vals.update({'destination_ids': destinations[line_id]})
res.append(line_vals)
return res
def create_budget_lines(self, cr, uid, budget_id, sequence=False, context=None):
"""
Create budget lines for a given budget.
If no budget: do nothing.
If no sequence: only create budget lines without any specific amounts.
Creation synthesis:
1/ get the initial template
2/ for each line create its budget line
3/a) if sequence: fetch budget values and fill in destination lines, normal lines and parents
3/b) if NO sequence, just create destination lines, normal lines and parents.
"""
# Some checks
if context is None:
context = {}
if not budget_id:
return False
# Prepare some values
a_obj = self.pool.get('account.account')
chart_of_account_ids = a_obj.search(cr, uid, [('code', '=', 'MSF')], context=context)
budget_line_obj = self.pool.get('msf.budget.line')
imported_obj = self.pool.get('imported.msf.budget.line')
sql = """
SELECT SUM(COALESCE(month1, 0.0)), SUM(COALESCE(month2, 0.0)), SUM(COALESCE(month3, 0.0)), SUM(COALESCE(month4, 0.0)), SUM(COALESCE(month5, 0.0)), SUM(COALESCE(month6, 0.0)), SUM(COALESCE(month7, 0.0)), SUM(COALESCE(month8, 0.0)), SUM(COALESCE(month9, 0.0)), SUM(COALESCE(month10, 0.0)), SUM(COALESCE(month11, 0.0)), SUM(COALESCE(month12, 0.0))
FROM msf_budget_line
WHERE id IN %s"""
# Get budget line template from budget tools
template = self.get_budget_line_template(cr, uid, context=context)
# Browse each budget line and create needed values
to_proceed = []
mapping_accounts = {}
for budget_line in template:
# Create budget line
line_id = budget_line.get('id')
line_type = budget_line.get('type')
parent_id = budget_line.get('parent_id', False)
# Do not use top parent account (those in chart_of_account_ids)
if parent_id in chart_of_account_ids:
parent_id = False
budget_line_vals = {
'budget_id': budget_id,
'account_id': line_id,
'line_type': line_type,
'month1': 0.0,
'month2': 0.0,
'month3': 0.0,
'month4': 0.0,
'month5': 0.0,
'month6': 0.0,
'month7': 0.0,
'month8': 0.0,
'month9': 0.0,
'month10': 0.0,
'month11': 0.0,
'month12': 0.0,
}
if parent_id:
if parent_id not in mapping_accounts:
raise osv.except_osv(_('Error'), _('You did not create budget line in the right order. A parent does not exist!'))
budget_line_vals.update({'parent_id': mapping_accounts[parent_id]})
# Create line
budget_line_id = budget_line_obj.create(cr, uid, budget_line_vals, context=context)
to_proceed.append(budget_line_id)
mapping_accounts[line_id] = budget_line_id
if line_type == 'normal':
# Update vals with the new line type
budget_line_vals.update({
'line_type': 'destination',
})
# Browse each destination to create its line
for destination_id in budget_line.get('destination_ids', []):
budget_line_vals.update({
'destination_id': destination_id,
'month1': 0.0,
'month2': 0.0,
'month3': 0.0,
'month4': 0.0,
'month5': 0.0,
'month6': 0.0,
'month7': 0.0,
'month8': 0.0,
'month9': 0.0,
'month10': 0.0,
'month11': 0.0,
'month12': 0.0,
'parent_id': budget_line_id
})
# Fetch values if sequence is given (which permit to find some lines)
if sequence:
# Search if the CSV file have this kind of tuple account/destination and fetch values
csv_line_ids = imported_obj.search(cr, uid, [('account_id', '=', line_id), ('destination_id', '=', destination_id)], context=context)
# If yes, complete budget values from month1 to month12
if csv_line_ids:
csv_line = imported_obj.read(cr, uid, csv_line_ids[0], ['month1', 'month2', 'month3', 'month4', 'month5', 'month6', 'month7', 'month8', 'month9', 'month10', 'month11', 'month12'])
budget_line_vals.update({
'month1': csv_line.get('month1', 0.0),
'month2': csv_line.get('month2', 0.0),
'month3': csv_line.get('month3', 0.0),
'month4': csv_line.get('month4', 0.0),
'month5': csv_line.get('month5', 0.0),
'month6': csv_line.get('month6', 0.0),
'month7': csv_line.get('month7', 0.0),
'month8': csv_line.get('month8', 0.0),
'month9': csv_line.get('month9', 0.0),
'month10': csv_line.get('month10', 0.0),
'month11': csv_line.get('month11', 0.0),
'month12': csv_line.get('month12', 0.0),
})
# Create destination line
budget_line_obj.create(cr, uid, budget_line_vals, context=context)
# Fill in parent lines (only if sequence is given which means that we have probably some values in destination lines)
if sequence:
vals_headers = ['month1', 'month2', 'month3', 'month4', 'month5', 'month6', 'month6', 'month7', 'month8', 'month9', 'month10', 'month11', 'month12']
for budget_line_id in to_proceed:
# Search child_ids
child_ids = budget_line_obj.search(cr, uid, [('parent_id', 'child_of', budget_line_id)])
# Do the sum of them
cr.execute(sql, (tuple(child_ids),))
tmp_res = cr.fetchall()
# If result, write on the given budget line the result
if tmp_res:
budget_line_vals = dict(zip(vals_headers, [x[0] for x in tmp_res]))
budget_line_obj.write(cr, uid, budget_line_id, budget_line_vals, context=context)
return True
def _create_expense_account_line_amounts(self, cr, uid, account_ids, actual_amounts, context=None):
# Some checks
if context is None:
context = {}
if isinstance(account_ids, (int, long)):
account_ids = [account_ids]
a_obj = self.pool.get('account.account')
# Browse accounts
for account_id in account_ids:
if (account_id, False) not in actual_amounts:
account = a_obj.browse(cr, uid, account_id, context=context)
result = [0] * 12
if account.type == 'view':
# children are accounts
for child_account in account.child_id:
if (child_account.id, False) not in actual_amounts:
self._create_expense_account_line_amounts(cr, uid, child_account.id, actual_amounts, context=context)
result = [sum(pair) for pair in zip(result, actual_amounts[child_account.id, False])]
else:
# children are account, destination tuples (already in actual_amounts)
# get all tuples starting with (account_id)
for account_destination in [tuple_acc_dest for tuple_acc_dest in actual_amounts.keys() if tuple_acc_dest[0] == account_id and tuple_acc_dest[1] is not False]:
result = [sum(pair) for pair in zip(result, actual_amounts[account_destination])]
actual_amounts[account_id, False] = result
return
def _get_cost_center_ids(self, cr, uid, browse_cost_center):
return self.pool.get('account.analytic.account').search(cr, uid, [('parent_id', 'child_of', browse_cost_center.id)])
def _create_account_destination_domain(self, account_destination_list):
if len(account_destination_list) == 0:
return ['&',
('general_account_id', 'in', []),
('destination_id', 'in', [])]
elif len(account_destination_list) == 1:
return ['&',
('general_account_id', '=', account_destination_list[0][0]),
('destination_id', '=', account_destination_list[0][1])]
else:
return ['|'] + self._create_account_destination_domain([account_destination_list[0]]) + self._create_account_destination_domain(account_destination_list[1:])
def _get_actual_amounts(self, cr, uid, output_currency_id, domain=[], context=None):
# Input: domain for the selection of analytic lines (cost center, date, etc...)
# Output: a dict of list {(general_account_id, destination_id): [jan_actual, feb_actual,...]}
res = {}
if context is None:
context = {}
destination_obj = self.pool.get('account.destination.link')
# list to store every existing destination link in the system
account_ids = self.get_expense_accounts(cr, uid, context=context)
destination_link_ids = destination_obj.search(cr, uid, [('account_id', 'in', account_ids)], context=context)
account_destination_ids = [(dest.account_id.id, dest.destination_id.id)
for dest
in destination_obj.browse(cr, uid, destination_link_ids, context=context)]
# Fill all general accounts
for account_id, destination_id in account_destination_ids:
res[account_id, destination_id] = [0] * 12
# fill search domain (one search for all analytic lines)
domain += self._create_account_destination_domain(account_destination_ids)
# Analytic domain is now done; lines are retrieved and added
analytic_line_obj = self.pool.get('account.analytic.line')
analytic_lines = analytic_line_obj.search(cr, uid, domain, context=context)
# use currency_table_id
currency_table = None
if 'currency_table_id' in context:
currency_table = context['currency_table_id']
# parse each line and add it to the right array
for analytic_line in analytic_line_obj.browse(cr, uid, analytic_lines, context=context):
date_context = {'date': analytic_line.source_date or analytic_line.date,
'currency_table_id': currency_table}
actual_amount = self.pool.get('res.currency').compute(cr,
uid,
analytic_line.currency_id.id,
output_currency_id,
analytic_line.amount_currency or 0.0,
round=True,
context=date_context)
# add the amount to correct month
month = datetime.datetime.strptime(analytic_line.date, '%Y-%m-%d').month
res[analytic_line.general_account_id.id, analytic_line.destination_id.id][month - 1] += round(actual_amount, 2)
# after all lines are parsed, absolute of every column
for line in res.keys():
res[line] = [-x for x in res[line]]
# do the view lines
self._create_expense_account_line_amounts(cr, uid, account_ids, res, context=context)
return res
msf_budget_tools()
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
|