addons/point_of_sale/report/all_closed_cashbox_of_the_day.py

   1 # -*- coding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 #    OpenERP, Open Source Management Solution
   5 #    Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
   6 #
   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.
  11 #
  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.
  16 #
  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/>.
  19 #
  20 ##############################################################################
  21
  22 import time
  23 from report import report_sxw
  24
  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({
  30                 'time': time,
  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,
  39         })
  40
  41     def _get_user(self,line_ids):
  42         sql = "select name from res_users where id = %d"%(line_ids['create_uid'])
  43         self.cr.execute(sql)
  44         user = self.cr.fetchone()
  45         return user[0]
  46
  47     def _get_data(self,user):
  48         data = {}
  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)
  56         self.cr.execute(sql)
  57         data = self.cr.dictfetchall()
  58         return data
  59
  60     def _get_lines(self,statement):
  61         data = {}
  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'])
  64         self.cr.execute(sql)
  65         data = self.cr.dictfetchall()
  66         return data
  67
  68     def _get_bal(self,data):
  69         res = {}
  70         sql =""" select sum(pieces*number) as bal from account_cashbox_line where starting_id = %d """%(data['id'])
  71         self.cr.execute(sql)
  72         res = self.cr.dictfetchall()
  73         if res:
  74             return res[0]['bal']
  75         else:
  76             return False
  77
  78     def _get_sub_total(self,user,data,date):
  79         res={}
  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')
  84                             and abs.date = '%s'
  85                             and abs.user_id = %d
  86                             """%(data,date,user.id))
  87         res = self.cr.fetchall()
  88         if res[0][0]:
  89             return res[0][0]
  90         else:
  91             return False
  92
  93     def _get_partner(self,statement):
  94         res = {}
  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'])
  99             self.cr.execute(sql)
 100             res = self.cr.dictfetchall() or {}
 101             return res and res[0]['name']
 102         else:
 103             return 0.00
 104
 105     def _get_net_total_starting(self,user):
 106         lst = []
 107         res={}
 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)
 114         self.cr.execute(sql)
 115         res = self.cr.dictfetchall()
 116         for r in res:
 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()
 121             if data[0]['bal']:
 122                 total_starting_bal += data[0]['bal']
 123         lst.append(total_ending_bal)
 124         lst.append(total_starting_bal)
 125         return lst
 126
 127     def _get_net_total(self,user):
 128         res={}
 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)
 133
 134         self.cr.execute(sql)
 135         res = self.cr.dictfetchall()
 136         return res[0]['net_total'] or 0.0
 137
 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')
 139
 140 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: