1 # -*- coding: utf-8 -*-
2 ##############################################################################
4 # OpenERP, Open Source Management Solution
5 # Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
7 # This program is free software: you can redistribute it and/or modify
8 # it under the terms of the GNU Affero General Public License as
9 # published by the Free Software Foundation, either version 3 of the
10 # License, or (at your option) any later version.
12 # This program is distributed in the hope that it will be useful,
13 # but WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 # GNU Affero General Public License for more details.
17 # You should have received a copy of the GNU Affero General Public License
18 # along with this program. If not, see <http://www.gnu.org/licenses/>.
20 ##############################################################################
23 from report import report_sxw
25 class all_closed_cashbox_of_the_day(report_sxw.rml_parse):
26 #TOFIX: sql injection problem: SQL Request must be pass from sql injection...
27 def __init__(self, cr, uid, name, context):
28 super(all_closed_cashbox_of_the_day, self).__init__(cr, uid, name, context=context)
29 self.localcontext.update({
31 'get_data':self._get_data,
32 'get_bal':self._get_bal,
33 'get_lines':self._get_lines,
34 'get_partner':self._get_partner,
35 'get_net_total':self._get_net_total,
36 'get_user':self._get_user,
37 'get_sub_total':self._get_sub_total,
38 'get_net_total_starting':self._get_net_total_starting,
41 def _get_user(self,line_ids):
42 sql = "select name from res_users where id = %d"%(line_ids['create_uid'])
44 user = self.cr.fetchone()
47 def _get_data(self,user):
49 sql = """ SELECT abs.journal_id,abs.id,abs.date,abs.closing_date,abs.name as statement,aj.name as journal,ap.name as period,ru.name as user,rc.name as company,
50 abs.state,abs.balance_end_real FROM account_bank_statement as abs
51 LEFT JOIN account_journal as aj ON aj.id = abs.journal_id
52 LEFT JOIN account_period as ap ON ap.id = abs.period_id
53 LEFT JOIN res_users as ru ON ru.id = abs.user_id
54 LEFT JOIN res_company as rc ON rc.id = abs.company_id
55 WHERE to_char(date_trunc('day',abs.date),'YYYY-MM-DD')::date = current_date and abs.state IN ('confirm','open') and abs.user_id = %d"""%(user.id)
57 data = self.cr.dictfetchall()
60 def _get_lines(self,statement):
62 sql = """ select absl.* from account_bank_statement_line as absl, account_bank_statement as abs
63 where absl.statement_id = abs.id and abs.id = %d"""%(statement['id'])
65 data = self.cr.dictfetchall()
68 def _get_bal(self,data):
70 sql =""" select sum(pieces*number) as bal from account_cashbox_line where starting_id = %d """%(data['id'])
72 res = self.cr.dictfetchall()
78 def _get_sub_total(self,user,data,date):
80 self.cr.execute(""" select sum(absl.amount) from account_bank_statement as abs
81 LEFT JOIN account_bank_statement_line as absl ON abs.id = absl.statement_id
82 WHERE abs.journal_id = %d
83 and abs.state IN ('confirm','open')
86 """%(data,date,user.id))
87 res = self.cr.fetchall()
93 def _get_partner(self,statement):
95 if statement['pos_statement_id']:
96 sql =""" select rp.name from account_bank_statement_line as absl,res_partner as rp
97 where absl.partner_id = rp.id
98 and absl.pos_statement_id = %d"""%(statement['pos_statement_id'])
100 res = self.cr.dictfetchall() or {}
101 return res and res[0]['name']
105 def _get_net_total_starting(self,user):
108 total_ending_bal = 0.0
109 total_starting_bal = 0.0
110 sql = """ SELECT abs.id,abs.balance_end_real as net_total FROM account_bank_statement as abs
111 WHERE to_char(date_trunc('day',abs.date),'YYYY-MM-DD')::date = current_date
112 and abs.state IN ('confirm','open')
113 and abs.user_id = %d"""%(user.id)
115 res = self.cr.dictfetchall()
117 total_ending_bal += (r['net_total'] or 0.0)
118 sql1 =""" select sum(pieces*number) as bal from account_cashbox_line where starting_id = %d"""%(r['id'])
119 self.cr.execute(sql1)
120 data = self.cr.dictfetchall()
122 total_starting_bal += data[0]['bal']
123 lst.append(total_ending_bal)
124 lst.append(total_starting_bal)
127 def _get_net_total(self,user):
129 sql = """select sum(absl.amount) as net_total from account_bank_statement as abs
130 LEFT JOIN account_bank_statement_line as absl ON abs.id = absl.statement_id
131 where abs.state IN ('confirm','open') and abs.user_id = %d
132 and to_char(date_trunc('day',abs.date),'YYYY-MM-DD')::date = current_date """%(user.id)
135 res = self.cr.dictfetchall()
136 return res[0]['net_total'] or 0.0
138 report_sxw.report_sxw('report.all.closed.cashbox.of.the.day', 'account.bank.statement', 'addons/point_of_sale/report/all_closed_cashbox_of_the_day.rml', parser=all_closed_cashbox_of_the_day,header='internal')
140 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: