~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-08-24 05:27:14 UTC
  • Revision ID: popkar77@gmail.com-20120824052714-az7x5huju9e4onb4
调整格式

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: