~openerp-chinese-team/openerp-china/openerp-china

« back to all changes in this revision

Viewing changes to oecn_account_print/report/report_ledger.py

  • Committer: JoshuaJan
  • Date: 2012-02-21 03:32:39 UTC
  • Revision ID: joshua@openerp.cn-20120221033239-0q1f281aswpku2zh
1.Add Threecolumns Ledger

Show diffs side-by-side

added added

removed removed

Lines of Context:
 
1
# -*- encoding: utf-8 -*-
 
2
##############################################################################
 
3
# 帐簿报表解析器实现
 
4
#
 
5
# Authors: * Camptocamp
 
6
#          * oldrev <oldrev@gmail.com>
 
7
#          * add you
 
8
 
9
# Copyright (C) 2010-TODAY by The HornERP Team
 
10
#
 
11
# WARNING: This program as such is intended to be used by professional
 
12
# programmers who take the whole responsability of assessing all potential
 
13
# consequences resulting from its eventual inadequacies and bugs
 
14
# End users who are looking for a ready-to-use solution with commercial
 
15
# garantees and support are strongly adviced to contract a Free Software
 
16
# Service Company
 
17
#
 
18
# This program is Free Software; you can redistribute it and/or
 
19
# modify it under the terms of the GNU General Public License
 
20
# as published by the Free Software Foundation; either version 2
 
21
# of the License, or (at your option) any later version.
 
22
#
 
23
# This program is distributed in the hope that it will be useful,
 
24
# but WITHOUT ANY WARRANTY; without even the implied warranty of
 
25
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 
26
# GNU General Public License for more details.
 
27
#
 
28
# You should have received a copy of the GNU General Public License
 
29
# along with this program; if not, write to the Free Software
 
30
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 
31
#
 
32
##############################################################################
 
33
 
 
34
import time
 
35
from mx.DateTime import *
 
36
from report import report_sxw
 
37
import xml
 
38
import rml_parse
 
39
import pooler
 
40
 
 
41
 
 
42
class LedgerParser(rml_parse.rml_parse):
 
43
    """
 
44
    通用帐簿解析器基类(即报表后台)
 
45
    """
 
46
    def __init__(self, cr, uid, name, context):
 
47
        super(LedgerParser, self).__init__(cr, uid, name, context)
 
48
        # self.date_borne = {}
 
49
        self.query = ""
 
50
        self.child_ids = ""
 
51
        self.sql_condition = " "
 
52
        self.tot_currency = 0.0
 
53
        self.period_sql = ""
 
54
        self.sold_accounts = {}
 
55
        self.localcontext.update( { # 注册报表模板里可以访问的函数
 
56
            'time': time,
 
57
            'lines': self.lines,
 
58
            'type':self._check_type,
 
59
            'period_date':self.get_dete,
 
60
            # 'sum_debit_account': self._sum_debit_account,
 
61
            # 'sum_credit_account': self._sum_credit_account,
 
62
            # 'sum_solde_account': self._sum_solde_account,
 
63
            'sum_debit': self._sum_debit,
 
64
            'sum_credit': self._sum_credit,
 
65
            'sum_solde': self._sum_solde,
 
66
            'get_children_accounts': self.get_children_accounts,
 
67
            # 'sum_currency_amount_account': self._sum_currency_amount_account,
 
68
            'get_direction':self._get_direction,
 
69
            # 'sum_quantity_account':self._sum_quantity_account,
 
70
            'account_name' :self._get_account_name,
 
71
            'get_periods':self._get_periods,
 
72
            'sum_begin_solde':self._sum_begin_solde,
 
73
            'sum_year_amount_solde':self._sum_year_amount_solde,
 
74
            'sum_year_amount_currency':self._sum_year_amount_currency,
 
75
            'sum_amount_currency':self._sum_amount_currency,
 
76
            'sum_amount_quantity':self._sum_amount_quantity,
 
77
            'sum_year_quantity':self._sum_year_quantity,
 
78
            'sum_begin_balance_amount_currency':self._sum_begin_balance_amount_currency,
 
79
            'sum_year_balance_amount_currency':self._sum_year_balance_amount_currency,
 
80
            'sum_begin_balance_quantity':self._sum_begin_balance_quantity,
 
81
            'sum_year_balance_quantity':self._sum_year_balance_quantity,
 
82
        })
 
83
        self.context = context
 
84
 
 
85
    def set_context(self, objects, data, ids, report_type = None):
 
86
        """
 
87
        设置 OE context
 
88
        """
 
89
        # self.borne_date = self.get_date(data['form'])
 
90
        self.all_dete = self.get_dete(data['form'])
 
91
        self.sql_condition = self.get_threecolumns_ledger_type(data['form'])
 
92
 
 
93
        new_ids = []
 
94
        if (data['model'] == 'account.account'):
 
95
            new_ids = ids
 
96
        else:
 
97
            new_ids.append(data['form']['Account_list'])
 
98
            objects = self.pool.get('account.account').browse(self.cr, self.uid, new_ids)
 
99
 
 
100
        super(LedgerParser, self).set_context(objects, data, new_ids, report_type)
 
101
 
 
102
    def get_dete(self, form):
 
103
        """
 
104
        分析日期
 
105
        """
 
106
        period_form_id = form['period_form']
 
107
        period_to_id = form['period_to']
 
108
        period_obj = self.pool.get('account.period')
 
109
        period_start_obj = period_obj.read(self.cr, self.uid, period_form_id, ['date_start','fiscalyear_id'])
 
110
        period_end_obj = period_obj.read(self.cr, self.uid, period_to_id, ['date_stop'])
 
111
        fiscalyear_obj = self.pool.get('account.fiscalyear').read(self.cr, self.uid, period_start_obj['fiscalyear_id'], ['date_start'])
 
112
        self.all_dete = {
 
113
            'period_start_date_start':period_start_obj['date_start'],
 
114
            'period_end_date_stop':period_end_obj['date_stop'],
 
115
            'fiscalyear_obj_date_start':fiscalyear_obj[0]['date_start'],
 
116
        }
 
117
 
 
118
        return self.all_dete
 
119
 
 
120
    def _get_periods(self, form):
 
121
        """
 
122
        获取期间
 
123
        """
 
124
        self.cr.execute(("select date_start,date_stop,name as period_name "\
 
125
                    "from account_period where "\
 
126
                    " date_start>='%s' and date_stop<='%s' "\
 
127
                    " order by date_start")% (self.all_dete['period_start_date_start'], self.all_dete['period_end_date_stop']))
 
128
        periods = self.cr.dictfetchall()
 
129
        return periods
 
130
 
 
131
    def _check_type(self, form):
 
132
        """
 
133
        检测报表类型
 
134
        """
 
135
        res = {}
 
136
        res['product'] = ""
 
137
        res['partner'] = ""
 
138
        res['report_name'] = u'三栏式'
 
139
        if form.get('product',''):
 
140
            res['report_name'] = u'产品'
 
141
            product_sql = """SELECT t.name AS name
 
142
                    FROM product_product AS p join product_template AS t on (p.product_tmpl_id=t.id)
 
143
                        WHERE p.id='%s'"""%(form['product'])
 
144
            self.cr.execute(product_sql)
 
145
            product_res = self.cr.dictfetchall()
 
146
            res['product']=product_res[0]['name']
 
147
        if form.get('partner',''):
 
148
            res['report_name'] = u'业务伙伴'
 
149
            partner_sql = """SELECT name 
 
150
                    FROM res_partner 
 
151
                        WHERE res_partner.id='%s'"""%(form['partner'])
 
152
            self.cr.execute(partner_sql)
 
153
            partner_res = self.cr.dictfetchall()
 
154
            res['partner']=partner_res[0]['name']
 
155
        return res
 
156
 
 
157
    def _calc_contrepartie(self,cr,uid,ids, context={}):
 
158
        """
 
159
        计算"对方科目",下边这是法语吧
 
160
        """
 
161
        result = {}
 
162
        #for id in ids:
 
163
        #    result.setdefault(id, False)
 
164
 
 
165
        for account_line in self.pool.get('account.move.line').browse(cr, uid, ids, context):
 
166
            # For avoid long text in the field we will limit it to 5 lines
 
167
 
 
168
            result[account_line.id] = ' '
 
169
            num_id_move = str(account_line.move_id.id)
 
170
            num_id_line = str(account_line.id)
 
171
            account_id = str(account_line.account_id.id)
 
172
            # search the basic account
 
173
            # We have the account ID we will search all account move line from now until this time
 
174
            # We are in the case of we are on the top of the account move Line
 
175
            cr.execute('SELECT distinct(ac.code) as code_rest,ac.name as name_rest from account_account AS ac, account_move_line mv\
 
176
                    where ac.id = mv.account_id and mv.move_id = ' + num_id_move +' and mv.account_id <> ' + account_id )
 
177
            res_mv = cr.dictfetchall()
 
178
            # we need a result more than 2 line to make the test so we will made the the on 1 because we have exclude the current line
 
179
            if (len(res_mv) >=1):
 
180
                concat = ''
 
181
                rup_id = 0
 
182
                for move_rest in res_mv:
 
183
                    concat = concat + move_rest['code_rest'] + u' ' + move_rest['name_rest'] + u';'
 
184
                    result[account_line.id] = concat
 
185
                    if rup_id >5:
 
186
                        # we need to stop the computing and to escape but before we will add "..."
 
187
                        result[account_line.id] = concat + '...'
 
188
                        break
 
189
                    rup_id+=1
 
190
        return result
 
191
 
 
192
    def get_date(self, form):
 
193
        """
 
194
        获取 move.lines 的日期区间
 
195
        """
 
196
        period_form_id = form['period_form']
 
197
        period_to_id = form['period_to']
 
198
        period_obj = self.pool.get('account.period')
 
199
        period_start_obj = period_obj.read(self.cr, self.uid, period_form_id, ['date_start'])
 
200
        period_end_obj = period_obj.read(self.cr, self.uid, period_to_id, ['date_stop'])
 
201
        borne_min = period_start_obj['date_start']
 
202
        borne_max = period_end_obj['date_stop']
 
203
 
 
204
        self.date_borne = {
 
205
            'min_date': borne_min,
 
206
            'max_date': borne_max,
 
207
            }
 
208
        return self.date_borne
 
209
 
 
210
    def get_threecolumns_ledger_type(self, form):
 
211
        if form.get('product',''):
 
212
            self.sql_condition = " AND l.product_id ='"+str(form['product'])+"'"
 
213
        if form.get('partner',''):
 
214
            self.sql_condition = " AND l.partner_id ='"+str(form['partner'])+"'"
 
215
        return self.sql_condition
 
216
 
 
217
 
 
218
    def get_children_accounts(self, account, form, period, recursion=True):
 
219
        """
 
220
        遍历指定科目下的所有子科目
 
221
        """
 
222
        self.child_ids = self.pool.get('account.account').search(self.cr, self.uid,
 
223
            [('parent_id', 'child_of', self.ids)])
 
224
 
 
225
        res = []
 
226
        ctx = self.context.copy()
 
227
        ## We will make the test for period or date
 
228
        ## We will now make the test
 
229
        #
 
230
        #ctx['state'] = form['context'].get('state','all')
 
231
        #if form.has_key('fiscalyear'):
 
232
        #    ctx['fiscalyear'] = form['fiscalyear']
 
233
        #    ctx['periods'] = form['periods'][0][2]
 
234
        #else:
 
235
        #    ctx['date_from'] = form['date_from']
 
236
        #    ctx['date_to'] = form['date_to']
 
237
 
 
238
        self.query = self.pool.get('account.move.line')._query_get(self.cr, self.uid, context=ctx)
 
239
        if account and account.child_consol_ids: # add ids of consolidated childs also of selected account
 
240
            ctx['consolidate_childs'] = True
 
241
            ctx['account_id'] = account.id                    
 
242
 
 
243
        ids_acc = self.pool.get('account.account').search(
 
244
            self.cr, self.uid,[('parent_id', 'child_of', [account.id])], context=ctx)
 
245
 
 
246
        for child_id in ids_acc:
 
247
            child_account = self.pool.get('account.account').browse(self.cr, self.uid, child_id)
 
248
            sold_account = self._sum_solde_account(child_account,form, period)
 
249
            self.sold_accounts[child_account.id] = sold_account
 
250
            
 
251
            if child_account.type != 'view' \
 
252
            and len(self.pool.get('account.move.line').search(self.cr, self.uid,
 
253
                [('account_id','>=',child_account.id)],
 
254
                context=ctx)) <> 0 :
 
255
                res.append(child_account)
 
256
 
 
257
        if not len(res):
 
258
            return [account]
 
259
        for move in res:
 
260
            SOLDEINIT = "SELECT sum(l.debit) AS sum_debit, sum(l.credit) AS sum_credit, sum(l.quantity) AS sum_quantity, sum(l.amount_currency) AS sum_amount_currency FROM account_move_line l WHERE l.account_id = " + str(move.id) +  " AND l.date < '" + period['date_stop'] + "'" +  " AND l.date > '" + period['date_start'] +"'"+ self.sql_condition
 
261
            self.cr.execute(SOLDEINIT)
 
262
            resultat = self.cr.dictfetchall()
 
263
            if resultat[0] :
 
264
                if resultat[0]['sum_debit'] == None:
 
265
                    sum_debit = 0
 
266
                else:
 
267
                    sum_debit = resultat[0]['sum_debit']
 
268
                if resultat[0]['sum_credit'] == None:
 
269
                    sum_credit = 0
 
270
                else:
 
271
                    sum_credit = resultat[0]['sum_credit']
 
272
                if resultat[0]['sum_amount_currency'] == None:
 
273
                    sum_amount_currency =0
 
274
                else:
 
275
                    sum_amount_currency = resultat[0]['sum_amount_currency']
 
276
                if resultat[0]['sum_quantity'] == None:
 
277
                    sum_quantity = 0
 
278
                else:
 
279
                    sum_quantity = resultat[0]['sum_quantity']
 
280
 
 
281
                move.init_credit = sum_credit
 
282
                move.init_debit = sum_debit
 
283
                move.init_amount_currency = sum_amount_currency
 
284
                move.init_quantity = sum_quantity
 
285
 
 
286
            else:
 
287
                move.init_credit = 0
 
288
                move.init_debit = 0
 
289
                move.init_currency = 0
 
290
                move.init_quantity = 0
 
291
 
 
292
        return res
 
293
 
 
294
 
 
295
    def lines(self, account, form, period, day=False):
 
296
        """
 
297
        按向导指定的参数获取所有的 account.move.line
 
298
        """
 
299
        self.tot_currency = 0.0
 
300
 
 
301
        inv_types = {
 
302
                'out_invoice': 'CI: ',
 
303
                'in_invoice': 'SI: ',
 
304
                'out_refund': 'OR: ',
 
305
                'in_refund': 'SR: ',
 
306
                }
 
307
        sql = """
 
308
            SELECT l.id, l.date, j.code,c.code AS currency_code,l.amount_currency ,l.ref, l.name , l.debit, l.credit, l.period_id, l.quantity
 
309
                    FROM account_move_line as l
 
310
                       LEFT JOIN res_currency c on (l.currency_id=c.id)
 
311
                                JOIN account_journal j on (l.journal_id=j.id)
 
312
                                AND account_id = %%s
 
313
                                AND %s
 
314
                                    %s
 
315
                                WHERE l.date<=%%s
 
316
                                AND l.date>=%%s
 
317
                                ORDER BY l.date, l.id""" % (self.query, self.sql_condition)
 
318
        if day:
 
319
            self.cr.execute(sql, (account.id, day, day,))
 
320
            res = self.cr.dictfetchall()
 
321
        else:
 
322
            self.cr.execute(sql, (account.id, period['date_stop'], period['date_start'],))
 
323
            res = self.cr.dictfetchall()
 
324
        sum = 0.0
 
325
        account_move_line_obj = pooler.get_pool(self.cr.dbname).get('account.move.line')
 
326
        for l in res:
 
327
            line = self.pool.get('account.move.line').browse(self.cr, self.uid, l['id'])
 
328
            l['move'] = line.move_id.name
 
329
            self.cr.execute('Select id from account_invoice where move_id =%s'%(line.move_id.id))
 
330
            tmpres = self.cr.dictfetchall()
 
331
            if len(tmpres) > 0 :
 
332
                inv = self.pool.get('account.invoice').browse(self.cr, self.uid, tmpres[0]['id'])
 
333
                l['ref'] = inv_types[inv.type] + ': '+str(inv.number)
 
334
            if line.partner_id :
 
335
                l['partner'] = line.partner_id.name
 
336
            else :
 
337
                l['partner'] = ''
 
338
 
 
339
            # 需要修正的地方请加上 FIXME 标记,需要尚未实现完全的地方请加上 TODO
 
340
            # by mrshelly 为啥要初始化, 这里也需要处理
 
341
            if type(l['debit'])  == type(None):
 
342
                l['debit'] = 0.0
 
343
            if type(l['credit'])  == type(None):
 
344
                l['credit'] = 0.0
 
345
            # by mrshelly 为啥要初始化, 这里也需要处理
 
346
 
 
347
            sum = l['debit'] - l ['credit']
 
348
            l['progress'] = sum
 
349
            balance = line.balance
 
350
        
 
351
            l['balance'] = abs(balance)
 
352
            #if balance == 0:
 
353
            #    str = u'平'
 
354
            #elif balance > 0:
 
355
            #    str = u'借'
 
356
            #else:
 
357
            #    str = u'贷'
 
358
            l['direction'] = self._get_direction(l['debit'] - l ['credit'])
 
359
            l['line_corresp'] = self._calc_contrepartie(self.cr,self.uid,[l['id']])[l['id']]
 
360
            # Modification du amount Currency
 
361
            #if (l['credit'] > 0):
 
362
            #    if l['amount_currency'] != None:
 
363
            #        l['amount_currency'] = abs(l['amount_currency']) * -1
 
364
 
 
365
            #if l['amount_currency'] != None:
 
366
            #    l['amount_currency_balance'] = self.tot_currency + l['amount_currency']
 
367
 
 
368
            #单价
 
369
            if (l['quantity'] != None and l['quantity'] !=0):
 
370
                if  l['debit'] != 0.0:
 
371
                    l['price'] = l['debit']/l['quantity']
 
372
                elif  l['credit'] != 0.0:
 
373
                    l['price'] = l['credit']/l['quantity']
 
374
            #汇率
 
375
            if (l['amount_currency'] != None and l['amount_currency'] !=0):
 
376
                if  l['debit'] != 0.0:
 
377
                    l['rate'] = l['debit']/l['amount_currency']
 
378
                elif  l['credit'] != 0.0:
 
379
                    l['rate'] = l['credit']/l['amount_currency']
 
380
            l['sum_balance_amount_currency'] = (self._sum_balance_currency_quantiry(l['date'],l['id']))['sum_balance_amount_currency']
 
381
            l['sum_balance_quantity'] = (self._sum_balance_currency_quantiry(l['date'],l['id']))['sum_balance_quantity']
 
382
 
 
383
        return res
 
384
 
 
385
    def _sum_solde_account(self, account, form, period=False):
 
386
        """
 
387
        科目余额合计
 
388
        """
 
389
        if period==False:
 
390
            self.cr.execute("SELECT (sum(debit) - sum(credit)) as tot_solde "\
 
391
                "FROM account_move_line l "\
 
392
                "WHERE l.account_id = "+str(account.id)+" AND l.date<='"+self.all_dete['period_end_date_stop']+"' AND l.date>='"+self.all_dete['period_start_date_start']+"' AND "+self.query+ " "+self.sql_condition)
 
393
        else:
 
394
            self.cr.execute("SELECT (sum(debit) - sum(credit)) as tot_solde "\
 
395
                "FROM account_move_line l "\
 
396
                "WHERE l.account_id = "+str(account.id)+" AND l.date<='"+period['date_stop']+"' AND l.date>='"+period['date_start']+"' AND "+self.query+ " "+self.sql_condition)
 
397
        sum_solde = self.cr.fetchone()[0] or 0.0
 
398
        if form.get('soldeinit', False):
 
399
            sum_solde += account.init_debit - account.init_credit
 
400
 
 
401
        return sum_solde
 
402
 
 
403
    def _sum_begin_solde(self):
 
404
        """
 
405
        期初余额
 
406
        """
 
407
        result = {
 
408
            'begin_date':'',
 
409
            'direction':'',
 
410
            'debit':'',
 
411
            'credit':'',
 
412
            'sum_begin_solde':0,
 
413
        }
 
414
        if not self.ids:
 
415
            return 0.0
 
416
        self.cr.execute("SELECT sum(debit) as debit , sum(credit) as credit "\
 
417
                "FROM account_move_line l "\
 
418
                "WHERE l.account_id in ("+','.join(map(str, self.pool.get('account.account').search(self.cr, self.uid,
 
419
            [('parent_id', 'child_of', self.ids)])))+") "+ " AND l.date < '" + self.all_dete['period_start_date_start'] +"'"+self.sql_condition)
 
420
        res = self.cr.dictfetchall()
 
421
        sum_begin_solde = (res[0]['debit'] or 0.0) - (res[0]['credit'] or 0.0)
 
422
        if sum_begin_solde == 0:
 
423
            result['direction'] = u'平'
 
424
        elif sum_begin_solde > 0:
 
425
            result['direction'] = u'借'
 
426
        else:
 
427
            result['direction'] = u'贷'
 
428
        result['sum_begin_solde'] = abs(sum_begin_solde or 0.0)
 
429
        result['begin_date'] = self.all_dete['period_start_date_start']
 
430
        return result
 
431
 
 
432
    def _sum_year_amount_solde(self, period):
 
433
        """
 
434
        本年累计
 
435
        """
 
436
        result = {
 
437
            'end_date':'',
 
438
            'direction':'',
 
439
            'debit':'',
 
440
            'credit':'',
 
441
            'sum_year_amount_solde':0,
 
442
        }
 
443
        self.cr.execute("SELECT sum(debit) as debit , sum(credit) as credit  "\
 
444
                "FROM account_move_line l "\
 
445
                "WHERE l.account_id in ("+','.join(map(str, self.pool.get('account.account').search(self.cr, self.uid,
 
446
            [('parent_id', 'child_of', self.ids)])))+") "+ " AND l.state<>'draft' AND l.date <= '" + period['date_stop']  +"'"+ " AND l.date >= '" + self.all_dete['fiscalyear_obj_date_start']  + "'"+self.sql_condition)
 
447
        res = self.cr.dictfetchall()
 
448
        sum_year_amount_solde = (res[0]['debit'] or 0.0) - (res[0]['credit'] or 0.0)
 
449
        if sum_year_amount_solde == 0:
 
450
            result['direction'] = u'平'
 
451
        elif sum_year_amount_solde > 0:
 
452
            result['direction'] = u'借'
 
453
        else:
 
454
            result['direction'] = u'贷'
 
455
        result['sum_year_amount_solde'] = abs(sum_year_amount_solde or 0.0)
 
456
        result['end_date'] = self.all_dete['fiscalyear_obj_date_start']
 
457
        result['debit'] = res[0]['debit']
 
458
        result['credit'] = res[0]['credit']
 
459
        return result
 
460
 
 
461
    def _sum_amount_currency_quantiry(self, start_date = False, end_date = False):
 
462
        """
 
463
        获取外币、数量合计值
 
464
        """
 
465
        result = {
 
466
            'sum_debit_amount_currency':0.00,
 
467
            'sum_credit_amount_currency':0.00,
 
468
            'sum_debit_quantity':0.00,
 
469
            'sum_credit_quantity':0.00,
 
470
        }
 
471
        if end_date:
 
472
            end_date_sql = " AND l.date <= '" + end_date +"'"
 
473
        else:
 
474
            end_date_sql = ' '
 
475
        if start_date:
 
476
            start_date_sql = " AND l.date >= '" + start_date  + "'"
 
477
        else:
 
478
            start_date_sql = ' '
 
479
 
 
480
        #方向是借
 
481
        self.cr.execute("SELECT  sum(amount_currency) AS amount_currency, sum(quantity) AS quantity "\
 
482
                " FROM account_move_line l "\
 
483
                " WHERE l.account_id in ("+','.join(map(str, self.pool.get('account.account').search(self.cr, self.uid,
 
484
            [('parent_id', 'child_of', self.ids)])))+") "+ 
 
485
            " AND l.state<>'draft' AND l.debit-l.credit>0 " + end_date_sql + start_date_sql + self.sql_condition)
 
486
        debit_res = self.cr.dictfetchall()
 
487
 
 
488
        #方向是贷
 
489
        self.cr.execute("SELECT  sum(amount_currency) AS amount_currency, sum(quantity) AS quantity "\
 
490
                " FROM account_move_line l "\
 
491
                " WHERE l.account_id in ("+','.join(map(str, self.pool.get('account.account').search(self.cr, self.uid,
 
492
            [('parent_id', 'child_of', self.ids)])))+") "+ 
 
493
            " AND l.state<>'draft' AND l.debit-l.credit<0 " + end_date_sql + start_date_sql + self.sql_condition)
 
494
        cedit_res = self.cr.dictfetchall()
 
495
        if debit_res[0].get('amount_currency',0.00)!= None:
 
496
            result['sum_debit_amount_currency'] = debit_res[0].get('amount_currency',0.00)
 
497
        if debit_res[0].get('quantity',0.00)!=None:
 
498
            result['sum_debit_quantity'] = debit_res[0].get('quantity',0.00)
 
499
        if cedit_res[0].get('amount_currency',0.00)!=None:
 
500
            result['sum_credit_amount_currency'] = cedit_res[0].get('amount_currency',0.00)
 
501
        if cedit_res[0].get('quantity',0.00)!=None:
 
502
            result['sum_credit_quantity'] = cedit_res[0].get('quantity',0.00)
 
503
        return result
 
504
 
 
505
    def _sum_balance_currency_quantiry(self,date = False, id = False,):
 
506
        """
 
507
        获取外币、数量的余额
 
508
        """
 
509
        result = {
 
510
            'sum_balance_amount_currency':0.00,
 
511
            'sum_balance_quantity':0.00,
 
512
        }
 
513
        sum_debit_amount_currency = 0.00
 
514
        sum_credit_amount_currency = 0.00
 
515
        sum_debit_quantity = 0.00
 
516
        sum_credit_quantity = 0.00
 
517
        balance_condition = ' '
 
518
        if date:
 
519
            balance_condition = " AND l.date <= '" + date +"'"
 
520
        if id and date:
 
521
            balance_condition = " AND (date<'"+str(date)+"' OR (date='"+str(date)+"' AND id<='"+str(id)+"')) "
 
522
 
 
523
        #方向是借
 
524
        self.cr.execute("SELECT sum(amount_currency) AS amount_currency, sum(quantity) AS quantity"\
 
525
                " FROM account_move_line l "\
 
526
                " WHERE l.account_id in ("+','.join(map(str, self.pool.get('account.account').search(self.cr, self.uid,
 
527
            [('parent_id', 'child_of', self.ids)])))+") "+ 
 
528
            " AND l.state<>'draft' AND l.debit-l.credit>0 " + balance_condition + self.sql_condition)
 
529
        debit_res = self.cr.dictfetchall()
 
530
 
 
531
        #方向是贷
 
532
        self.cr.execute("SELECT sum(amount_currency) AS amount_currency, sum(quantity) AS quantity"\
 
533
                " FROM account_move_line l "\
 
534
                " WHERE l.account_id in ("+','.join(map(str, self.pool.get('account.account').search(self.cr, self.uid,
 
535
            [('parent_id', 'child_of', self.ids)])))+") "+ 
 
536
            " AND l.state<>'draft' AND l.debit-l.credit<0 " + balance_condition  + self.sql_condition)
 
537
        cedit_res = self.cr.dictfetchall()
 
538
 
 
539
        if debit_res[0].get('amount_currency',0.0)!=None:
 
540
            sum_debit_amount_currency = debit_res[0].get('amount_currency',0.0)
 
541
            
 
542
        if debit_res[0].get('quantity',0.0)!=None:
 
543
            sum_debit_quantity = debit_res[0].get('quantity',0.0)
 
544
        
 
545
        if cedit_res[0].get('amount_currency',0.0)!=None:
 
546
            sum_credit_amount_currency = cedit_res[0].get('amount_currency',0.0)
 
547
            
 
548
        if cedit_res[0].get('quantity',0.0)!=None:
 
549
            sum_credit_quantity = cedit_res[0].get('quantity',0.0)
 
550
 
 
551
        result['sum_balance_amount_currency'] = abs(sum_debit_amount_currency - sum_credit_amount_currency ) 
 
552
        result['sum_balance_quantity'] = abs(sum_debit_quantity - sum_credit_quantity )
 
553
 
 
554
        return result
 
555
 
 
556
    def _sum_begin_balance_amount_currency(self):
 
557
        """
 
558
        外币期初余额
 
559
        """
 
560
        result = self._sum_balance_currency_quantiry(date = self.all_dete['period_start_date_start'])
 
561
        return result
 
562
 
 
563
    def _sum_year_balance_amount_currency(self, period):
 
564
        """
 
565
        外币期间、本年余额
 
566
        """
 
567
        result = self._sum_balance_currency_quantiry(date = period['date_stop'])
 
568
        return result
 
569
 
 
570
    def _sum_year_amount_currency(self, period):
 
571
        """
 
572
        外币本年借、贷合计
 
573
        """
 
574
        result = self._sum_amount_currency_quantiry(self.all_dete['fiscalyear_obj_date_start'], period['date_stop'])
 
575
        return result
 
576
 
 
577
    def _sum_amount_currency(self, period):
 
578
        """
 
579
        外币期间借、贷合计
 
580
        """
 
581
        result = self._sum_amount_currency_quantiry(self.all_dete['period_start_date_start'],self.all_dete['period_end_date_stop'])
 
582
        return result
 
583
 
 
584
    def _sum_year_quantity(self, period):
 
585
        """
 
586
        数量本年借、贷合计
 
587
        """
 
588
        result = self._sum_amount_currency_quantiry(self.all_dete['fiscalyear_obj_date_start'], period['date_stop'])
 
589
        return result
 
590
 
 
591
    def _sum_amount_quantity(self, form, period=False):
 
592
        """
 
593
        数量期间借、贷合计
 
594
        """
 
595
        result = self._sum_amount_currency_quantiry( self.all_dete['period_start_date_start'], self.all_dete['period_end_date_stop'])
 
596
        return result
 
597
 
 
598
    def _sum_begin_balance_quantity(self):
 
599
        """
 
600
        数量期初余额
 
601
        """
 
602
        result = self._sum_balance_currency_quantiry(date = self.all_dete['period_start_date_start'])
 
603
        return result
 
604
 
 
605
    def _sum_year_balance_quantity(self,period):
 
606
        """
 
607
        数量期间、本年余额
 
608
        """
 
609
        result = self._sum_balance_currency_quantiry(date = period['date_stop'])
 
610
        return result
 
611
 
 
612
    def _sum_debit(self, form, period=False):
 
613
        """
 
614
        借方期间总计
 
615
        """
 
616
        if not self.ids:
 
617
            return 0.0
 
618
        if period==False:
 
619
            self.cr.execute("SELECT sum(debit) "\
 
620
                "FROM account_move_line l "\
 
621
                "WHERE l.account_id in ("+','.join(map(str, self.child_ids))+") AND "+self.query + " AND l.date < '" +self.all_dete['period_end_date_stop'] + "'" +  " AND l.date > '" + self.all_dete['period_start_date_start'] +"'"+self.sql_condition)
 
622
        else:
 
623
            self.cr.execute("SELECT sum(debit) "\
 
624
                "FROM account_move_line l "\
 
625
                "WHERE l.account_id in ("+','.join(map(str, self.child_ids))+") AND "+self.query + " AND l.date < '" + period['date_stop'] + "'" +  " AND l.date > '" + period['date_start'] +"'"+self.sql_condition)
 
626
        sum_debit = self.cr.fetchone()[0] or 0.0
 
627
        return sum_debit
 
628
 
 
629
    def _sum_credit(self, form, period=False):
 
630
        """
 
631
        贷方期间总计
 
632
        """
 
633
        if not self.ids:
 
634
            return 0.0
 
635
        if period==False:
 
636
            self.cr.execute("SELECT sum(credit) "\
 
637
                "FROM account_move_line l "\
 
638
                "WHERE l.account_id in ("+','.join(map(str, self.child_ids))+") AND "+self.query + " AND l.date < '" +self.all_dete['period_end_date_stop'] + "'" +  " AND l.date > '" + self.all_dete['period_start_date_start'] +"'"+self.sql_condition)
 
639
        else:
 
640
            self.cr.execute("SELECT sum(credit) "\
 
641
                "FROM account_move_line l "\
 
642
                "WHERE l.account_id in ("+','.join(map(str, self.child_ids))+") AND "+self.query + " AND l.date < '" + period['date_stop'] + "'" +  " AND l.date > '" + period['date_start'] +"'"+self.sql_condition)
 
643
        ## Add solde init to the result
 
644
        sum_credit = self.cr.fetchone()[0] or 0.0
 
645
        return sum_credit
 
646
 
 
647
    def _sum_solde(self, form, period=False):
 
648
        """
 
649
        余额期间总计
 
650
        """
 
651
        if not self.ids:
 
652
            return 0.0
 
653
        if period==False:
 
654
             self.cr.execute("SELECT (sum(debit) - sum(credit)) as tot_solde "\
 
655
                "FROM account_move_line l "\
 
656
                "WHERE l.account_id in ("+','.join(map(str, self.child_ids))+") AND "+self.query + " AND l.date < '" +self.all_dete['period_end_date_stop'] + "'" +  " AND l.date > '" + self.all_dete['period_start_date_start'] +"'"+self.sql_condition)
 
657
        else:
 
658
            self.cr.execute("SELECT (sum(debit) - sum(credit)) as tot_solde "\
 
659
                "FROM account_move_line l "\
 
660
                "WHERE l.account_id in ("+','.join(map(str, self.child_ids))+") AND "+self.query + " AND l.date < '" + period['date_stop'] + "'" +  " AND l.date > '" + period['date_start'] +"'"+self.sql_condition)
 
661
        sum_solde = abs(self.cr.fetchone()[0] or 0.0)
 
662
        return sum_solde
 
663
 
 
664
    #    def _sum_amount_currency(self, form, period=False):
 
665
    #        if not self.ids:
 
666
    #            return 0.0
 
667
    #        if period==False:
 
668
    #            self.cr.execute("SELECT sum(amount_currency) "\
 
669
    #                        "FROM account_move_line l "\
 
670
    #                        "WHERE l.account_id in ("+','.join(map(str, self.child_ids))+") AND "+self.query + " AND l.date < '" +self.all_dete['period_end_date_stop'] + "'" +  " AND l.date > '" + self.all_dete['period_start_date_start'] +"'"+self.sql_condition)
 
671
    #        else:
 
672
    #            self.cr.execute("SELECT sum(amount_currency) "\
 
673
    #                            "FROM account_move_line l "\
 
674
    #                            "WHERE l.account_id in ("+','.join(map(str, self.child_ids))+") AND "+self.query + " AND l.date < '" + period['date_stop'] + "'" +  " AND l.date > '" + period['date_start'] +"'"+self.sql_condition)
 
675
    #        sum_amount_currency = self.cr.fetchone()[0] or 0.0
 
676
    #        return sum_amount_currency
 
677
 
 
678
    def _set_get_account_currency_code(self, account_id):
 
679
        self.cr.execute("SELECT c.code as code "\
 
680
                "FROM res_currency c,account_account as ac "\
 
681
                "WHERE ac.id = %s AND ac.currency_id = c.id"%(account_id))
 
682
        result = self.cr.fetchone()
 
683
        if result:
 
684
            self.account_currency = result[0]
 
685
        else:
 
686
            self.account_currency = False
 
687
 
 
688
    #    def _sum_currency_amount_account(self, account, form):
 
689
    #        self._set_get_account_currency_code(account.id)
 
690
    #        self.cr.execute("SELECT sum(aml.amount_currency) FROM account_move_line as aml,res_currency as rc WHERE aml.currency_id = rc.id AND aml.account_id= %s ", (account.id,))
 
691
    #        total = self.cr.fetchone()
 
692
    #        if self.account_currency:
 
693
    #            return_field = str(total[0]) + self.account_currency
 
694
    #            return return_field
 
695
    #        else:
 
696
    #            currency_total = self.tot_currency = 0.0
 
697
    #            return currency_total
 
698
 
 
699
    def _get_direction(self, balance):
 
700
        #FIXME: 这里估计是错的,还待研判
 
701
        str = ''
 
702
        if balance == 0:
 
703
            str = u'平'
 
704
        elif balance > 0:
 
705
            str = u'借'
 
706
        else:
 
707
            str = u'贷'
 
708
        return str
 
709
 
 
710
    def _get_account_name(self,account):
 
711
        """
 
712
        获取完整的科目名称
 
713
        """
 
714
        id = str(account.id)
 
715
        account_name = self.pool.get('account.account').name_get(self.cr, self.uid, id,{})
 
716
        return account_name[0][1]
 
717
 
 
718
 
 
719
class CashLedgerParser(LedgerParser):
 
720
    """
 
721
    现金日记账报表解析器
 
722
    """
 
723
    def __init__(self, cr, uid, name, context):
 
724
        super(CashLedgerParser, self).__init__(cr, uid, name, context)
 
725
        self.localcontext.update({
 
726
            'days': self.days,
 
727
        })
 
728
 
 
729
    def days(self, account, form, period):
 
730
        """
 
731
        从 account.move.line 里按日期分组
 
732
        也就是把所有的凭证明细的不同日分出来,方便计算单日合计
 
733
        """
 
734
        sql = """
 
735
            SELECT l.date AS date, SUM(l.debit) AS debit, SUM(l.credit) AS credit, MAX(l.id) AS last_line_id, sum(l.amount_currency) AS sum_currency
 
736
                FROM account_move_line AS l
 
737
                       LEFT JOIN res_currency c on (l.currency_id=c.id)
 
738
                          JOIN account_journal j on (l.journal_id=j.id)
 
739
                             AND account_id = %%s
 
740
                             AND %s
 
741
                               WHERE l.date<=%%s
 
742
                               AND l.date>=%%s
 
743
                               GROUP BY l.date
 
744
                               ORDER BY l.date """ % (self.query)
 
745
        max_date = period['date_stop']
 
746
        min_date = period['date_start']
 
747
        self.cr.execute(sql, (account.id, max_date, min_date))
 
748
 
 
749
        res = self.cr.dictfetchall()
 
750
        account_move_line_obj = pooler.get_pool(self.cr.dbname).get('account.move.line')
 
751
        for day in res:
 
752
            start_date = ''
 
753
            day['sum_balance_amount_currency'] = (self._sum_balance_currency_quantiry( date = str(day.get('date','')))).get('sum_balance_amount_currency',0.0)
 
754
            day['sum_debit_amount_currency'] = (self._sum_amount_currency_quantiry( start_date = str(day.get('date','')),end_date = str(day.get('date','')))).get('sum_debit_amount_currency',0.0)
 
755
            day['sum_credit_amount_currency'] = (self._sum_amount_currency_quantiry( start_date = str(day.get('date','')),end_date = str(day.get('date','')))).get('sum_credit_amount_currency',0.0)
 
756
            line = self.pool.get('account.move.line').browse(self.cr, self.uid, day['last_line_id'])
 
757
            day['balance'] = abs(line.balance)
 
758
            if(type(day['debit']) == type(None)):
 
759
                day['debit'] = 0.0              # by mrshelly 只是为了报表出来.这里需要处理.
 
760
            if(type(day['credit']) == type(None)):
 
761
                day['credit'] = 0.0             # by mrshelly 只是为了报表出来.这里需要处理.
 
762
 
 
763
        return res
 
764
 
 
765
#注册报表类
 
766
 
 
767
#总帐
 
768
 
 
769
#现金日记帐
 
770
report_sxw.report_sxw('report.account.cash_ledger', 'account.account', 'addons/l10n_account_cn/report/cash_ledger.odt', parser=CashLedgerParser, header=False)
 
771
 
 
772
#外币日记帐
 
773
report_sxw.report_sxw('report.account.foreign_currency_cash_ledger', 'account.account', 'addons/l10n_account_cn/report/foreign_currency_cash_ledger.odt', parser=CashLedgerParser, header=False)
 
774
 
 
775
#三栏明细帐
 
776
report_sxw.report_sxw('report.account.threecolumns_ledger', 'account.account', 'addons/l10n_account_cn/report/threecolumns_ledger.odt', parser=LedgerParser, header=False)
 
777
        
 
778
#数量金额明细帐
 
779
report_sxw.report_sxw('report.account.product_ledger', 'account.account', 'addons/l10n_account_cn/report/product_ledger.odt', parser=LedgerParser, header=False)
 
780
 
 
781
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: