1 # -*- encoding: utf-8 -*-
2 ##############################################################################
4 # Author: Guewen Baconnier
5 # Copyright Camptocamp SA 2011
6 # SQL inspired from OpenERP original code
8 # This program is free software: you can redistribute it and/or modify
9 # it under the terms of the GNU Affero General Public License as
10 # published by the Free Software Foundation, either version 3 of the
11 # License, or (at your option) any later version.
13 # This program is distributed in the hope that it will be useful,
14 # but WITHOUT ANY WARRANTY; without even the implied warranty of
15 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 # GNU Affero General Public License for more details.
18 # You should have received a copy of the GNU Affero General Public License
19 # along with this program. If not, see <http://www.gnu.org/licenses/>.
21 ##############################################################################
23 from collections
import defaultdict
24 from operator
import add
26 from .common_balance_reports
import CommonBalanceReportHeaderWebkit
27 from .common_partner_reports
import CommonPartnersReportHeaderWebkit
30 class CommonPartnerBalanceReportHeaderWebkit(CommonBalanceReportHeaderWebkit
, CommonPartnersReportHeaderWebkit
):
31 """Define common helper for balance (trial balance, P&L, BS oriented financial report"""
33 def _get_account_partners_details(self
, account_by_ids
, main_filter
, target_move
, start
,
34 stop
, initial_balance_mode
, partner_filter_ids
=False):
37 if main_filter
in ('filter_period', 'filter_no', 'filter_opening'):
38 filter_from
= 'period'
39 elif main_filter
== 'filter_date':
42 partners_init_balances_by_ids
= {}
43 for account_id
, account_details
in account_by_ids
.iteritems():
44 partners_init_balances_by_ids
.update(self
._get
_partners
_initial
_balances
(account_id
,
47 partner_filter_ids
=partner_filter_ids
,
48 exclude_reconcile
=False)) # we'll never exclude reconciled entries in the legal reports
49 opening_mode
= 'exclude_opening'
50 if main_filter
== 'filter_opening':
51 opening_mode
= 'include_opening'
52 # get credit and debit for partner
53 details
= self
._get
_partners
_totals
_account
(filter_from
,
58 partner_filter_ids
=partner_filter_ids
,
61 # merge initial balances in partner details
62 if partners_init_balances_by_ids
.get(account_id
):
63 for partner_id
, initial_balances
in partners_init_balances_by_ids
[account_id
].iteritems():
64 if initial_balances
.get('init_balance'):
65 details
[partner_id
].update({'init_balance': initial_balances
['init_balance']})
67 # compute balance for the partner
68 for partner_id
, partner_details
in details
.iteritems():
69 details
[partner_id
]['balance'] = details
[partner_id
].get('init_balance', 0.0) +\
70 details
[partner_id
].get('debit', 0.0) -\
71 details
[partner_id
].get('credit', 0.0)
72 res
[account_id
] = details
76 def _get_partners_initial_balances(self
, account_ids
, start_period
, initial_balance_mode
, partner_filter_ids
=None, exclude_reconcile
=False):
77 # we get the initial balance from the opening period (opening_balance) when the opening period is included in the start period and
78 # when there is at least one entry in the opening period. Otherwise we compute it from previous periods
79 if initial_balance_mode
== 'opening_balance':
80 opening_period_selected
= self
.get_included_opening_period(start_period
)
81 res
= self
._compute
_partners
_initial
_balances
(account_ids
, start_period
, partner_filter_ids
, force_period_ids
=opening_period_selected
, exclude_reconcile
=exclude_reconcile
)
82 elif initial_balance_mode
== 'initial_balance':
83 res
= self
._compute
_partners
_initial
_balances
(account_ids
, start_period
, partner_filter_ids
, exclude_reconcile
=exclude_reconcile
)
88 def _get_partners_totals_account(self
, filter_from
, account_id
, start
, stop
, target_move
, partner_filter_ids
=None, mode
='exclude_opening'):
89 final_res
= defaultdict(dict)
92 SELECT account_move_line.partner_id,
93 sum(account_move_line.debit) AS debit,
94 sum(account_move_line.credit) AS credit
95 FROM account_move_line"""
97 sql_where
= "WHERE account_move_line.account_id = %(account_id)s AND account_move_line.state = 'valid' "
98 method
= getattr(self
, '_get_query_params_from_' + filter_from
+ 's')
99 sql_conditions
, search_params
= method(start
, stop
, mode
=mode
)
100 sql_where
+= sql_conditions
102 if partner_filter_ids
:
103 sql_where
+= " AND account_move_line.partner_id in %(partner_ids)s"
104 search_params
.update({'partner_ids': tuple(partner_filter_ids
)})
106 if target_move
== 'posted':
107 sql_joins
+= "INNER JOIN account_move ON account_move_line.move_id = account_move.id"
108 sql_where
+= " AND account_move.state = %(target_move)s"
109 search_params
.update({'target_move': target_move
})
111 sql_groupby
= "GROUP BY account_move_line.partner_id"
113 search_params
.update({'account_id': account_id
})
114 query
= ' '.join((sql_select
, sql_joins
, sql_where
, sql_groupby
))
116 self
.cursor
.execute(query
, search_params
)
117 res
= self
.cursor
.dictfetchall()
120 final_res
[row
['partner_id']] = row
123 def _get_filter_type(self
, result_selection
):
124 filter_type
= ('payable', 'receivable')
125 if result_selection
== 'customer':
126 filter_type
= ('receivable',)
127 if result_selection
== 'supplier':
128 filter_type
= ('payable',)
131 def _get_partners_comparison_details(self
, data
, account_ids
, target_move
, comparison_filter
, index
, partner_filter_ids
=False):
134 @param data: data of the wizard form
135 @param account_ids: ids of the accounts to get details
136 @param comparison_filter: selected filter on the form for the comparison (filter_no, filter_year, filter_period, filter_date)
137 @param index: index of the fields to get (ie. comp1_fiscalyear_id where 1 is the index)
138 @param partner_filter_ids: list of ids of partners to select
139 @return: dict of account details (key = account id)
141 fiscalyear
= self
._get
_info
(data
, "comp%s_fiscalyear_id" % (index
,), 'account.fiscalyear')
142 start_period
= self
._get
_info
(data
, "comp%s_period_from" % (index
,), 'account.period')
143 stop_period
= self
._get
_info
(data
, "comp%s_period_to" % (index
,), 'account.period')
144 start_date
= self
._get
_form
_param
("comp%s_date_from" % (index
,), data
)
145 stop_date
= self
._get
_form
_param
("comp%s_date_to" % (index
,), data
)
146 init_balance
= self
.is_initial_balance_enabled(comparison_filter
)
149 accounts_details_by_ids
= defaultdict(dict)
150 if comparison_filter
!= 'filter_no':
151 start_period
, stop_period
, start
, stop
= \
152 self
._get
_start
_stop
_for
_filter
(comparison_filter
, fiscalyear
, start_date
, stop_date
, start_period
, stop_period
)
153 details_filter
= comparison_filter
154 if comparison_filter
== 'filter_year':
155 details_filter
= 'filter_no'
157 initial_balance_mode
= init_balance
and self
._get
_initial
_balance
_mode
(start
) or False
159 accounts_by_ids
= self
._get
_account
_details
(account_ids
, target_move
, fiscalyear
, details_filter
, start
, stop
, initial_balance_mode
)
161 partner_details_by_ids
= self
._get
_account
_partners
_details
(accounts_by_ids
, details_filter
,
162 target_move
, start
, stop
, initial_balance_mode
,
163 partner_filter_ids
=partner_filter_ids
)
165 for account_id
in account_ids
:
166 accounts_details_by_ids
[account_id
]['account'] = accounts_by_ids
[account_id
]
167 accounts_details_by_ids
[account_id
]['partners_amounts'] = partner_details_by_ids
[account_id
]
170 'comparison_filter': comparison_filter
,
171 'fiscalyear': fiscalyear
,
174 'initial_balance_mode': initial_balance_mode
,
177 return accounts_details_by_ids
, comp_params
179 def compute_partner_balance_data(self
, data
, filter_report_type
=None):
180 new_ids
= data
['form']['account_ids'] or data
['form']['chart_account_id']
181 max_comparison
= self
._get
_form
_param
('max_comparison', data
, default
=0)
182 main_filter
= self
._get
_form
_param
('filter', data
, default
='filter_no')
184 comp_filters
, nb_comparisons
, comparison_mode
= self
._comp
_filters
(data
, max_comparison
)
186 fiscalyear
= self
.get_fiscalyear_br(data
)
188 start_period
= self
.get_start_period_br(data
)
189 stop_period
= self
.get_end_period_br(data
)
190 target_move
= self
._get
_form
_param
('target_move', data
, default
='all')
191 start_date
= self
._get
_form
_param
('date_from', data
)
192 stop_date
= self
._get
_form
_param
('date_to', data
)
193 chart_account
= self
._get
_chart
_account
_id
_br
(data
)
194 result_selection
= self
._get
_form
_param
('result_selection', data
)
195 partner_ids
= self
._get
_form
_param
('partner_ids', data
)
197 filter_type
= self
._get
_filter
_type
(result_selection
)
199 start_period
, stop_period
, start
, stop
= \
200 self
._get
_start
_stop
_for
_filter
(main_filter
, fiscalyear
, start_date
, stop_date
, start_period
, stop_period
)
202 initial_balance
= self
.is_initial_balance_enabled(main_filter
)
203 initial_balance_mode
= initial_balance
and self
._get
_initial
_balance
_mode
(start
) or False
205 # Retrieving accounts
206 account_ids
= self
.get_all_accounts(new_ids
, only_type
=filter_type
,
207 filter_report_type
=filter_report_type
)
209 # get details for each accounts, total of debit / credit / balance
210 accounts_by_ids
= self
._get
_account
_details
(account_ids
, target_move
, fiscalyear
, main_filter
, start
, stop
, initial_balance_mode
)
212 partner_details_by_ids
= self
._get
_account
_partners
_details
(accounts_by_ids
,
217 initial_balance_mode
,
218 partner_filter_ids
=partner_ids
)
220 comparison_params
= []
221 comp_accounts_by_ids
= []
222 for index
in range(max_comparison
):
223 if comp_filters
[index
] != 'filter_no':
224 comparison_result
, comp_params
= self
._get
_partners
_comparison
_details
(data
, account_ids
,
225 target_move
, comp_filters
[index
],
226 index
, partner_filter_ids
=partner_ids
)
227 comparison_params
.append(comp_params
)
228 comp_accounts_by_ids
.append(comparison_result
)
231 for account
in self
.pool
.get('account.account').browse(self
.cursor
, self
.uid
, account_ids
):
232 if not account
.parent_id
: # hide top level account
234 account
.debit
= accounts_by_ids
[account
.id]['debit']
235 account
.credit
= accounts_by_ids
[account
.id]['credit']
236 account
.balance
= accounts_by_ids
[account
.id]['balance']
237 account
.init_balance
= accounts_by_ids
[account
.id].get('init_balance', 0.0)
238 account
.partners_amounts
= partner_details_by_ids
[account
.id]
240 for comp_account_by_id
in comp_accounts_by_ids
:
241 values
= comp_account_by_id
.get(account
.id)
243 values
['account'].update(self
._get
_diff
(account
.balance
, values
['account'].get('balance', 0.0)))
244 comp_accounts
.append(values
)
246 for partner_id
, partner_values
in values
['partners_amounts'].copy().iteritems():
247 base_partner_balance
= account
.partners_amounts
[partner_id
]['balance'] if \
248 account
.partners_amounts
.get(partner_id
) else 0.0
249 partner_values
.update(self
._get
_diff
(base_partner_balance
,
250 partner_values
.get('balance', 0.0)))
251 values
['partners_amounts'][partner_id
].update(partner_values
)
253 account
.comparisons
= comp_accounts
255 all_partner_ids
= reduce(add
, [comp
['partners_amounts'].keys() for comp
in comp_accounts
],
256 account
.partners_amounts
.keys())
258 account
.partners_order
= self
._order
_partners
(all_partner_ids
)
260 objects
.append(account
)
262 context_report_values
= {
263 'fiscalyear': fiscalyear
,
264 'start_date': start_date
,
265 'stop_date': stop_date
,
266 'start_period': start_period
,
267 'stop_period': stop_period
,
268 'chart_account': chart_account
,
269 'comparison_mode': comparison_mode
,
270 'nb_comparison': nb_comparisons
,
271 'comp_params': comparison_params
,
272 'initial_balance_mode': initial_balance_mode
,
273 'compute_diff': self
._get
_diff
,
276 return objects
, new_ids
, context_report_values