1 # -*- encoding: utf-8 -*-
2 ##############################################################################
4 # Author: Nicolas Bessi, 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 ##############################################################################
22 # TODO refactor helper in order to act more like mixin
23 # By using properties we will have a more simple signature in fuctions
25 from collections
import defaultdict
26 from datetime
import datetime
28 from openerp
.tools
import DEFAULT_SERVER_DATE_FORMAT
29 from .common_reports
import CommonReportHeaderWebkit
32 class CommonPartnersReportHeaderWebkit(CommonReportHeaderWebkit
):
33 """Define common helper for partner oriented financial report"""
35 ####################Account move line retrieval helper ##########################
36 def get_partners_move_lines_ids(self
, account_id
, main_filter
, start
, stop
, target_move
,
37 exclude_reconcile
=False,
38 partner_filter
=False):
40 if main_filter
in ('filter_period', 'filter_no'):
41 filter_from
= 'period'
42 elif main_filter
== 'filter_date':
45 return self
._get
_partners
_move
_line
_ids
(filter_from
,
50 exclude_reconcile
=exclude_reconcile
,
51 partner_filter
=partner_filter
)
53 def _get_first_special_period(self
):
55 Returns the browse record of the period with the `special` flag, which
56 is the special period of the first fiscal year used in the accounting.
58 i.e. it searches the first fiscal year with at least one journal entry,
59 and it returns the id of the first period for which `special` is True
62 It is used for example in the partners reports, where we have to include
63 the first, and only the first opening period.
65 :return: browse record of the first special period.
67 move_line_obj
= self
.pool
.get('account.move.line')
68 first_entry_id
= move_line_obj
.search(
69 self
.cr
, self
.uid
, [], order
='date ASC', limit
=1)
70 # it means there is no entry at all, that's unlikely to happen, but
72 if not first_entry_id
:
74 first_entry
= move_line_obj
.browse(self
.cr
, self
.uid
, first_entry_id
[0])
75 fiscalyear
= first_entry
.period_id
.fiscalyear_id
76 special_periods
= [period
for period
in fiscalyear
.period_ids
if period
.special
]
77 # so, we have no opening period on the first year, nothing to return
78 if not special_periods
:
80 return min(special_periods
,
81 key
=lambda p
: datetime
.strptime(p
.date_start
, DEFAULT_SERVER_DATE_FORMAT
))
83 def _get_period_range_from_start_period(self
, start_period
, include_opening
=False,
85 stop_at_previous_opening
=False):
86 """We retrieve all periods before start period"""
87 periods
= super(CommonPartnersReportHeaderWebkit
, self
).\
88 _get_period_range_from_start_period(
90 include_opening
=include_opening
,
91 fiscalyear
=fiscalyear
,
92 stop_at_previous_opening
=stop_at_previous_opening
)
93 first_special
= self
._get
_first
_special
_period
()
94 if first_special
and first_special
.id not in periods
:
95 periods
.append(first_special
.id)
98 def _get_query_params_from_periods(self
, period_start
, period_stop
, mode
='exclude_opening'):
100 Build the part of the sql "where clause" which filters on the selected
103 :param browse_record period_start: first period of the report to print
104 :param browse_record period_stop: last period of the report to print
105 :param str mode: deprecated
107 # we do not want opening period so we exclude opening
108 periods
= self
.pool
.get('account.period').build_ctx_periods(
109 self
.cr
, self
.uid
, period_start
.id, period_stop
.id)
113 if mode
!= 'include_opening':
114 periods
= self
.exclude_opening_periods(periods
)
116 search_params
= {'period_ids': tuple(periods
),
117 'date_stop': period_stop
.date_stop
}
121 sql_conditions
= " AND account_move_line.period_id in %(period_ids)s"
123 return sql_conditions
, search_params
125 def _get_query_params_from_dates(self
, date_start
, date_stop
, **args
):
127 Build the part of the sql where clause based on the dates to print.
129 :param str date_start: start date of the report to print
130 :param str date_stop: end date of the report to print
133 periods
= self
._get
_opening
_periods
()
137 search_params
= {'period_ids': tuple(periods
),
138 'date_start': date_start
,
139 'date_stop': date_stop
}
141 sql_conditions
= " AND account_move_line.period_id not in %(period_ids)s" \
142 " AND account_move_line.date between date(%(date_start)s) and date((%(date_stop)s))"
144 return sql_conditions
, search_params
146 def _get_partners_move_line_ids(self
, filter_from
, account_id
, start
, stop
,
147 target_move
, opening_mode
='exclude_opening',
148 exclude_reconcile
=False, partner_filter
=None):
151 :param str filter_from: "periods" or "dates"
152 :param int account_id: id of the account where to search move lines
153 :param str or browse_record start: start date or start period
154 :param str or browse_record stop: stop date or stop period
155 :param str target_move: 'posted' or 'all'
156 :param opening_mode: deprecated
157 :param boolean exclude_reconcile: wether the reconciled entries are
159 :param list partner_filter: list of partner ids, will filter on their
163 final_res
= defaultdict(list)
165 sql_select
= "SELECT account_move_line.id, account_move_line.partner_id FROM account_move_line"
167 sql_where
= " WHERE account_move_line.account_id = %(account_ids)s " \
168 " AND account_move_line.state = 'valid' "
170 method
= getattr(self
, '_get_query_params_from_' + filter_from
+ 's')
171 sql_conditions
, search_params
= method(start
, stop
)
173 sql_where
+= sql_conditions
175 if exclude_reconcile
:
176 sql_where
+= (" AND ((account_move_line.reconcile_id IS NULL)"
177 " OR (account_move_line.reconcile_id IS NOT NULL AND account_move_line.last_rec_date > date(%(date_stop)s)))")
180 sql_where
+= " AND account_move_line.partner_id in %(partner_ids)s"
182 if target_move
== 'posted':
183 sql_joins
+= "INNER JOIN account_move ON account_move_line.move_id = account_move.id"
184 sql_where
+= " AND account_move.state = %(target_move)s"
185 search_params
.update({'target_move': target_move
})
187 search_params
.update({
188 'account_ids': account_id
,
189 'partner_ids': tuple(partner_filter
),
192 sql
= ' '.join((sql_select
, sql_joins
, sql_where
))
193 self
.cursor
.execute(sql
, search_params
)
194 res
= self
.cursor
.dictfetchall()
197 final_res
[row
['partner_id']].append(row
['id'])
200 def _get_clearance_move_line_ids(self
, move_line_ids
, date_stop
, date_until
):
201 if not move_line_ids
:
203 move_line_obj
= self
.pool
.get('account.move.line')
204 # we do not use orm in order to gain perfo
205 # In this case I have to test the effective gain over an itteration
206 # Actually ORM does not allows distinct behavior
207 sql
= "Select distinct reconcile_id from account_move_line where id in %s"
208 self
.cursor
.execute(sql
, (tuple(move_line_ids
),))
209 rec_ids
= self
.cursor
.fetchall()
211 rec_ids
= [x
[0] for x
in rec_ids
]
212 l_ids
= move_line_obj
.search(self
.cursor
,
214 [('reconcile_id', 'in', rec_ids
),
215 ('date', '>=', date_stop
),
216 ('date', '<=', date_until
)])
221 ####################Initial Partner Balance helper ########################
223 def _tree_move_line_ids(self
, move_lines_data
, key
=None):
225 move_lines_data must be a list of dict which contains at least keys :
228 - other keys with values of the line
229 - if param key is defined, only this key will be inserted in the tree
231 res[account_id.1][partner_id.1][move_line.1,
233 [partner_id.2][move_line.3]
234 res[account_id.2][partner_id.1][move_line.4]
236 res
= defaultdict(dict)
237 for row
in move_lines_data
[:]:
238 account_id
= row
.pop('account_id')
239 partner_id
= row
.pop('partner_id')
241 res
[account_id
].setdefault(partner_id
, []).append(row
[key
])
243 res
[account_id
][partner_id
] = row
246 def _partners_initial_balance_line_ids(self
, account_ids
, start_period
, partner_filter
, exclude_reconcile
=False, force_period_ids
=False, date_stop
=None):
247 # take ALL previous periods
248 period_ids
= force_period_ids \
249 if force_period_ids \
250 else self
._get
_period
_range
_from
_start
_period
(start_period
, fiscalyear
=False, include_opening
=False)
255 'date_start': start_period
.date_start
,
256 'period_ids': tuple(period_ids
),
257 'account_ids': tuple(account_ids
),
259 sql
= ("SELECT ml.id, ml.account_id, ml.partner_id "
260 "FROM account_move_line ml "
261 "INNER JOIN account_account a "
262 "ON a.id = ml.account_id "
263 "WHERE ml.period_id in %(period_ids)s "
264 "AND ml.account_id in %(account_ids)s ")
265 if exclude_reconcile
:
267 raise Exception("Missing \"date_stop\" to compute the open invoices.")
268 search_param
.update({'date_stop': date_stop
})
269 sql
+= ("AND ((ml.reconcile_id IS NULL)"
270 "OR (ml.reconcile_id IS NOT NULL AND ml.last_rec_date > date(%(date_stop)s))) ")
272 sql
+= "AND ml.partner_id in %(partner_ids)s "
273 search_param
.update({'partner_ids': tuple(partner_filter
)})
275 self
.cursor
.execute(sql
, search_param
)
276 return self
.cursor
.dictfetchall()
278 def _compute_partners_initial_balances(self
, account_ids
, start_period
, partner_filter
=None, exclude_reconcile
=False, force_period_ids
=False):
279 """We compute initial balance.
280 If form is filtered by date all initial balance are equal to 0
281 This function will sum pear and apple in currency amount if account as no secondary currency"""
282 if isinstance(account_ids
, (int, long)):
283 account_ids
= [account_ids
]
284 move_line_ids
= self
._partners
_initial
_balance
_line
_ids
(account_ids
, start_period
, partner_filter
,
285 exclude_reconcile
=exclude_reconcile
,
286 force_period_ids
=force_period_ids
)
287 if not move_line_ids
:
288 move_line_ids
= [{'id': -1}]
289 sql
= ("SELECT ml.account_id, ml.partner_id,"
290 " sum(ml.debit) as debit, sum(ml.credit) as credit,"
291 " sum(ml.debit-ml.credit) as init_balance,"
292 " CASE WHEN a.currency_id ISNULL THEN 0.0 ELSE sum(ml.amount_currency) END as init_balance_currency, "
293 " c.name as currency_name "
294 "FROM account_move_line ml "
295 "INNER JOIN account_account a "
296 "ON a.id = ml.account_id "
297 "LEFT JOIN res_currency c "
298 "ON c.id = a.currency_id "
299 "WHERE ml.id in %(move_line_ids)s "
300 "GROUP BY ml.account_id, ml.partner_id, a.currency_id, c.name")
301 search_param
= {'move_line_ids': tuple([move_line
['id'] for move_line
in move_line_ids
])}
302 self
.cursor
.execute(sql
, search_param
)
303 res
= self
.cursor
.dictfetchall()
304 return self
._tree
_move
_line
_ids
(res
)
306 ####################Partner specific helper ################################
307 def _order_partners(self
, *args
):
308 """We get the partner linked to all current accounts that are used.
309 We also use ensure that partner are ordered by name
319 existing_partner_ids
= [partner_id
for partner_id
in partner_ids
if partner_id
]
320 if existing_partner_ids
:
321 # We may use orm here as the performance optimization is not that big
322 sql
= ("SELECT name|| ' ' ||CASE WHEN ref IS NOT NULL THEN '('||ref||')' ELSE '' END, id, ref, name"
323 " FROM res_partner WHERE id IN %s ORDER BY LOWER(name), ref")
324 self
.cursor
.execute(sql
, (tuple(set(existing_partner_ids
)),))
325 res
= self
.cursor
.fetchall()
327 # move lines without partners, set None for empty partner
328 if not all(partner_ids
):
329 res
.append((None, None, None, None))