← Back to branch summary

~openerp-commiter/openobject-addons/stable-sja-branch

« back to all changes in this revision

Viewing changes to cci_account/report/aged_trial_balance.py

  • Committer: sja-axelor
  • Date: 2009-10-13 09:52:57 UTC
  • Revision ID: suniljagyasi@gmail.com-20091013095257-8u26ww0r20z9y6ey
add

Show diffs side-by-side

added added

removed removed

Lines of Context:
cci_account/report/aged_trial_balance.py
 
1
# -*- encoding: utf-8 -*-
 
2
##############################################################################
 
3
#
 
4
#    OpenERP, Open Source Management Solution   
 
5
#    Copyright (C) 2004-2008 Tiny SPRL (<http://tiny.be>). All Rights Reserved
 
6
#    $Id$
 
7
#
 
8
#    This program is free software: you can redistribute it and/or modify
 
9
#    it under the terms of the GNU General Public License as published by
 
10
#    the Free Software Foundation, either version 3 of the License, or
 
11
#    (at your option) any later version.
 
12
#
 
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 General Public License for more details.
 
17
#
 
18
#    You should have received a copy of the GNU General Public License
 
19
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
 
20
#
 
21
##############################################################################
 
22
 
 
23
import time
 
24
import pooler
 
25
from report import report_sxw
 
26
 
 
27
class aged_trial_report(report_sxw.rml_parse):
 
28
    def __init__(self, cr, uid, name, context):
 
29
        super(aged_trial_report, self).__init__(cr, uid, name, context)
 
30
        self.localcontext.update({
 
31
            'time': time,
 
32
            'get_lines': self._get_lines,
 
33
            'get_total': self._get_total,
 
34
            'get_before': self._get_before,
 
35
            'get_for_period': self._get_for_period,
 
36
            'get_company': self._get_company,
 
37
            'get_currency': self._get_currency,
 
38
        })
 
39
 
 
40
    def _add_header(self, node):
 
41
        return True
 
42
 
 
43
    def _get_lines(self, form):
 
44
        res = []
 
45
 
 
46
        if form['category'] == 'Customer' or form['category'] == 'Supplier' :
 
47
            cat_id=pooler.get_pool(self.cr.dbname).get('res.partner.category').search(self.cr,self.uid,[('name','=',form['category'])])
 
48
            cat_id+=pooler.get_pool(self.cr.dbname).get('res.partner.category').search(self.cr,self.uid,[('parent_id','child_of',cat_id)])
 
49
        else:
 
50
            cat_id=pooler.get_pool(self.cr.dbname).get('res.partner.category').search(self.cr,self.uid,[('name','in',['Customer','Supplier'])])
 
51
            cat_id+=pooler.get_pool(self.cr.dbname).get('res.partner.category').search(self.cr,self.uid,[('parent_id','child_of',cat_id)])
 
52
 
 
53
        self.cr.execute('SELECT partner_id from res_partner_category_rel where category_id in ('+','.join(map(str,cat_id))+')')
 
54
        data=self.cr.fetchall()
 
55
        self.partner_ids=[]
 
56
        self.partner_ids=list(set([x[0] for x in data]))
 
57
 
 
58
        account_move_line_obj = pooler.get_pool(self.cr.dbname).get('account.move.line')
 
59
        line_query = account_move_line_obj._query_get(self.cr, self.uid, obj='line',
 
60
                context={'fiscalyear': form['fiscalyear']})
 
61
        self.cr.execute("SELECT DISTINCT res_partner.id AS id, " \
 
62
                    "res_partner.name AS name " \
 
63
                "FROM res_partner, account_move_line AS line, account_account " \
 
64
                "WHERE (line.account_id=account_account.id) " \
 
65
                    "AND (line.reconcile_id IS NULL) " \
 
66
                    "AND (line.partner_id=res_partner.id) " \
 
67
                    "AND " + line_query + " " \
 
68
                    "AND (account_account.company_id = %s) " \
 
69
                    "AND account_account.active " \
 
70
                    "AND partner_id in ("+','.join(map(str,self.partner_ids))+")" \
 
71
                "ORDER BY res_partner.name", (form['company_id'],))
 
72
        partners = self.cr.dictfetchall()
 
73
        for partner in partners:
 
74
            values = {}
 
75
            self.cr.execute("SELECT SUM(debit-credit) " \
 
76
                    "FROM account_move_line AS line, account_account " \
 
77
                    "WHERE (line.account_id=account_account.id) " \
 
78
                        "AND (account_account.type IN ('payable','receivable')) " \
 
79
                        "AND (date < %s) AND (partner_id=%s) " \
 
80
                        "AND (reconcile_id IS NULL) " \
 
81
                        "AND " + line_query + " " \
 
82
                        "AND (account_account.company_id = %s) " \
 
83
                        "AND account_account.active",
 
84
                        (form['0']['start'], partner['id'], form['company_id']))
 
85
            before = self.cr.fetchone()
 
86
            values['before'] = before and before[0] or ""
 
87
            for i in range(5):
 
88
                self.cr.execute("SELECT SUM(debit-credit) " \
 
89
                        "FROM account_move_line AS line, account_account " \
 
90
                        "WHERE (line.account_id=account_account.id) " \
 
91
                            "AND (account_account.type IN ('payable','receivable')) " \
 
92
                            "AND (date >= %s) AND (date <= %s) " \
 
93
                            "AND (partner_id = %s) " \
 
94
                            "AND (reconcile_id IS NULL) " \
 
95
                            "AND " + line_query + " " \
 
96
                            "AND (account_account.company_id = %s) " \
 
97
                            "AND account_account.active",
 
98
                            (form[str(i)]['start'], form[str(i)]['stop'],
 
99
                                partner['id'], form['company_id']))
 
100
                during = self.cr.fetchone()
 
101
                values[str(i)] = during and during[0] or ""
 
102
 
 
103
            self.cr.execute("SELECT SUM(debit-credit) " \
 
104
                    "FROM account_move_line AS line, account_account " \
 
105
                    "WHERE (line.account_id = account_account.id) " \
 
106
                        "AND (account_account.type IN ('payable','receivable')) " \
 
107
                        "AND (partner_id = %s) " \
 
108
                        "AND (reconcile_id IS NULL) " \
 
109
                        "AND " + line_query + " " \
 
110
                        "AND (account_account.company_id = %s) " \
 
111
                        "AND account_account.active",
 
112
                        (partner['id'], form['company_id']))
 
113
            total = self.cr.fetchone()
 
114
            values['total'] = total and total[0] or 0.0
 
115
            values['name'] = partner['name']
 
116
            t = 0.0
 
117
            for i in range(5)+['before']:
 
118
                t+= float(values.get(str(i), 0.0) or 0.0)
 
119
            if values['total']:
 
120
                res.append(values)
 
121
        total = 0.0
 
122
        totals = {}
 
123
        for r in res:
 
124
            total += float(r['total'] or 0.0)
 
125
            for i in range(5)+['before']:
 
126
                totals.setdefault(str(i), 0.0)
 
127
                totals[str(i)] += float(r[str(i)] or 0.0)
 
128
        return res
 
129
 
 
130
    def _get_total(self, fiscalyear, company_id):
 
131
        account_move_line_obj = pooler.get_pool(self.cr.dbname).get('account.move.line')
 
132
        line_query = account_move_line_obj._query_get(self.cr, self.uid, obj='line',
 
133
                context={'fiscalyear': fiscalyear})
 
134
        self.cr.execute("SELECT SUM(debit - credit) " \
 
135
                "FROM account_move_line AS line, account_account " \
 
136
                "WHERE (line.account_id = account_account.id) " \
 
137
                    "AND (account_account.type IN ('payable', 'receivable')) "\
 
138
                    "AND reconcile_id IS NULL " \
 
139
                    "AND partner_id is NOT NULL " \
 
140
                    "AND " + line_query + " " \
 
141
                    "AND (account_account.company_id = %s) " \
 
142
                    "AND partner_id in ("+','.join(map(str,self.partner_ids))+")" \
 
143
                    "AND account_account.active",
 
144
                    (company_id,))
 
145
        total = self.cr.fetchone()
 
146
        return total and total[0] or 0.0
 
147
 
 
148
    def _get_before(self, date, fiscalyear, company_id):
 
149
        account_move_line_obj = pooler.get_pool(self.cr.dbname).get('account.move.line')
 
150
        line_query = account_move_line_obj._query_get(self.cr, self.uid, obj='line',
 
151
                context={'fiscalyear': fiscalyear})
 
152
        self.cr.execute("SELECT SUM(debit - credit) " \
 
153
                "FROM account_move_line AS line, account_account " \
 
154
                "WHERE (line.account_id = account_account.id) " \
 
155
                    "AND (account_account.type IN ('payable', 'receivable')) " \
 
156
                    "AND reconcile_id IS NULL " \
 
157
                    "AND (date < %s) " \
 
158
                    "AND partner_id IS NOT NULL " \
 
159
                    "AND " + line_query + " " \
 
160
                    "AND (account_account.company_id = %s) " \
 
161
                    "AND partner_id in ("+','.join(map(str,self.partner_ids))+")" \
 
162
                    "AND account_account.active",
 
163
                    (date, company_id))
 
164
        before = self.cr.fetchone()
 
165
        return before and before[0] or 0.0
 
166
 
 
167
    def _get_for_period(self, period, fiscalyear, company_id):
 
168
        account_move_line_obj = pooler.get_pool(self.cr.dbname).get('account.move.line')
 
169
        line_query = account_move_line_obj._query_get(self.cr, self.uid, obj='line',
 
170
                context={'fiscalyear': fiscalyear})
 
171
        self.cr.execute("SELECT SUM(debit - credit) " \
 
172
                "FROM account_move_line AS line, account_account " \
 
173
                "WHERE (line.account_id = account_account.id) " \
 
174
                    "AND (account_account.type IN ('payable', 'receivable')) " \
 
175
                    "AND reconcile_id IS NULL " \
 
176
                    "AND (date >= %s) " \
 
177
                    "AND (date <= %s) " \
 
178
                    "AND partner_id IS NOT NULL " \
 
179
                    "AND " + line_query + " " \
 
180
                    "AND (account_account.company_id = %s) " \
 
181
                    "AND partner_id in ("+','.join(map(str,self.partner_ids))+")" \
 
182
                    "AND account_account.active",
 
183
                    (period['start'], period['stop'], company_id))
 
184
        period = self.cr.fetchone()
 
185
        return period and period[0] or 0.0
 
186
 
 
187
    def _get_company(self, form):
 
188
        return pooler.get_pool(self.cr.dbname).get('res.company').browse(self.cr, self.uid, form['company_id']).name
 
189
 
 
190
    def _get_currency(self, form):
 
191
        return pooler.get_pool(self.cr.dbname).get('res.company').browse(self.cr, self.uid, form['company_id']).currency_id.name
 
192
 
 
193
report_sxw.report_sxw(
 
194
    'report.aged.trial.balance',
 
195
    'res.partner',
 
196
    'addons/cci_account/report/aged_trial_balance.rml',
 
197
    parser=aged_trial_report, header=False)
 
198
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
 
199