addons/point_of_sale/report/pos_details.py

   1 # -*- encoding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 #    OpenERP, Open Source Management Solution
   5 #    Copyright (C) 2004-2009 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 from report import report_sxw
  25
  26
  27 class pos_details(report_sxw.rml_parse):
  28
  29     def _get_invoice(self,inv_id,user):
  30         res={}
  31         self.cr.execute("select name from account_invoice as ac where id = %d" %(inv_id))
  32         res = self.cr.fetchone()
  33         if res:
  34             return res[0]
  35         else:
  36             return  ''
  37
  38     def _pos_sales_details(self,form,user):
  39         data={}
  40         self.cr.execute ("select po.name as pos_name,po.date_order,pt.name ,pol.qty,pol.price_unit,pol.discount,po.invoice_id,sum((pol.price_unit * pol.qty * (1 - (pol.discount) / 100.0))) as Total " \
  41                          "from pos_order as po,pos_order_line as pol,product_product as pp,product_template as pt,res_users as ru,res_company as rc " \
  42                          "where  pt.id=pp.product_tmpl_id and pp.id=pol.product_id and po.id = pol.order_id and po.state  in ('done','paid','invoiced') " \
  43                          "and to_char(date_trunc('day',po.date_order),'YYYY-MM-DD')::date  >= %s and to_char(date_trunc('day',po.date_order),'YYYY-MM-DD')::date  <= %s " \
  44                          "and po.user_id = ru.id and rc.id = %s and ru.id = %s " \
  45                          "group by po.name,pol.qty,po.date_order,pt.name,pol.price_unit,pol.discount,po.invoice_id " \
  46                              ,(form['date_start'],form['date_end'],str(user.company_id.id),str(self.uid)))
  47         data=self.cr.dictfetchall()
  48         if data:
  49             for d in data:
  50                 self.total += d['total']
  51                 self.qty += d['qty']
  52                 return data
  53         else:
  54             return {}
  55
  56     def _get_qty_total_2(self, form,user):
  57         qty=[]
  58         self.cr.execute("select sum(pol.qty) as qty " \
  59                         "from pos_order as po,pos_order_line as pol,product_product as pp,product_template as pt,res_users as ru,res_company as rc " \
  60                         "where  pt.id=pp.product_tmpl_id and pp.id=pol.product_id and po.id = pol.order_id and po.state  in ('done','paid','invoiced') " \
  61                         " and to_char(date_trunc('day',po.date_order),'YYYY-MM-DD')::date  >= %s and to_char(date_trunc('day',po.date_order),'YYYY-MM-DD')::date  <= %s " \
  62                         "and po.user_id = ru.id and rc.id = %s and ru.id = %s " \
  63                          ,(form['date_start'],form['date_end'],str(user.company_id.id),str(self.uid)))
  64         qty = self.cr.fetchone()
  65         return qty[0] or 0.00
  66
  67     def _get_sales_total_2(self, form,user):
  68         self.cr.execute("sELECT sum((pol.price_unit * pol.qty * (1 - (pol.discount) / 100.0))) as Total from  pos_order_line as pol , pos_order po, product_product as pp,product_template as pt  where po.company_id='%s' and po.id=pol.order_id and to_char(date_trunc('day',po.date_order),'YYYY-MM-DD')::date  >= '%s' and  to_char(date_trunc('day',po.date_order),'YYYY-MM-DD')::date  <= '%s' and po.state in ('paid','invoiced','done') and pt.id=pp.product_tmpl_id and pol.product_id=pp.id"% (str(user.company_id.id),form['date_start'],form['date_end']))
  69         res2=self.cr.fetchone()
  70         return res2 and res2[0] or 0.0
  71
  72     def _get_sum_invoice_2(self,form,user):
  73         res2=[]
  74         self.cr.execute ("select sum(pol.price_unit * pol.qty * (1 - (pol.discount) / 100.0))" \
  75                          "from pos_order as po,pos_order_line as pol,product_product as pp,product_template as pt ,res_users as ru,res_company as rc " \
  76                          "where pt.id=pp.product_tmpl_id and pp.id=pol.product_id and po.id = pol.order_id and po.state  in('invoiced')  " \
  77                          "and to_char(date_trunc('day',po.date_order),'YYYY-MM-DD')::date  >= %s and to_char(date_trunc('day',po.date_order),'YYYY-MM-DD')::date  <= %s " \
  78                          "and po.user_id = ru.id and rc.id = %s and ru.id = %s " \
  79                          ,(form['date_start'],form['date_end'],str(user.company_id.id),str(self.uid)))
  80         res2=self.cr.fetchone()
  81         self.total_invoiced=res2[0]
  82         return res2[0] or False
  83
  84     def _paid_total_2(self,form,user):
  85         res3=[]
  86         self.cr.execute ("select sum(pol.price_unit * pol.qty * (1 - (pol.discount) / 100.0))" \
  87                          "from pos_order as po,pos_order_line as pol,product_product as pp,product_template as pt, res_users as ru,res_company as rc " \
  88                          "where pt.id=pp.product_tmpl_id and pp.id=pol.product_id and po.id = pol.order_id and po.state  in('paid','invoiced','done')  " \
  89                          "and to_char(date_trunc('day',po.date_order),'YYYY-MM-DD')::date  >= %s and to_char(date_trunc('day',po.date_order),'YYYY-MM-DD')::date  <= %s " \
  90                          "and po.user_id = ru.id and rc.id = %s and ru.id = %s " \
  91                          ,(form['date_start'],form['date_end'],str(user.company_id.id),str(self.uid)))
  92         res3=self.cr.fetchone()
  93         self.total_paid=res3[0]
  94         return res3[0] or False
  95
  96 #    def _get_qty_total(self, objects):
  97 #        #code for the sum of qty_total
  98 #        return reduce(lambda acc, object:
  99 #                                        acc + reduce(
 100 #                                                lambda sum_qty, line:
 101 #                                                        sum_qty + line.qty,
 102 #                                                object.lines,
 103 #                                                0),
 104 #                                    objects,
 105 #                                    0)
 106
 107     def _get_sum_discount(self, objects):
 108         #code for the sum of discount value
 109         return reduce(lambda acc, object:
 110                                         acc + reduce(
 111                                                 lambda sum_dis, line:
 112                                                         sum_dis + ((line.price_unit * line.qty) * (line.discount / 100)),
 113                                                 object.lines,
 114                                                 0.0),
 115                                     objects,
 116                                     0.0)
 117
 118     def _get_payments(self, form,user, ignore_gift=False):
 119 #        gift_journal_id = None
 120 #        if ignore_gift:
 121 #            config_journal_ids = self.pool.get("pos.config.journal").search(self.cr, self.uid, [('code', '=', 'GIFT')])
 122 #            if len(config_journal_ids):
 123 #                config_journal = self.pool.get("pos.config.journal").browse(self.cr, self.uid, config_journal_ids, {})[0]
 124 #                gift_journal_id = config_journal.journal_id.id
 125 #
 126 #        result = {}
 127 #        for obj in objects:
 128 #            for payment in obj.statement_ids:
 129 #                result[payment.journal_id] = result.get(payment.journal_id, 0.0) + payment.amount
 130 #        return result
 131         statement_line_obj = self.pool.get("account.bank.statement.line")
 132         gift_journal_id = None
 133         if ignore_gift:
 134             config_journal_ids = self.pool.get("pos.config.journal").search(self.cr, self.uid, [('code', '=', 'GIFT')])
 135             if len(config_journal_ids):
 136                 config_journal = self.pool.get("pos.config.journal").browse(self.cr, self.uid, config_journal_ids, {})[0]
 137                 gift_journal_id = config_journal.journal_id.id
 138         pos_ids=self.pool.get("pos.order").search(self.cr, self.uid, [('date_order','>=',form['date_start'] + ' 00:00:00'),('date_order','<=',form['date_end'] + ' 23:59:59'),('state','in',['paid','invoiced','done']),('user_id','=',self.uid)])
 139         data={}
 140         if pos_ids:
 141             st_line_ids = statement_line_obj.search(self.cr, self.uid, [('pos_statement_id', 'in', pos_ids)])
 142             if st_line_ids:
 143                 st_id = statement_line_obj.browse(self.cr, self.uid, st_line_ids)
 144                 a_l=[]
 145                 for r in st_id :
 146                     a_l.append(r['id'])
 147                     a = ','.join(map(str,a_l))
 148                 self.cr.execute("select aj.name,sum(amount) from account_bank_statement_line as absl,account_bank_statement as abs,account_journal as aj " \
 149                                 "where absl.statement_id = abs.id and abs.journal_id = aj.id  and absl.id in (%s) " \
 150                                 "group by aj.name " \
 151                                 %(a))
 152
 153                 data=self.cr.dictfetchall()
 154                 return data
 155         else:
 156             return {}
 157
 158     def _total_of_the_day(self, objects):
 159         if self.total_paid:
 160              if self.total_paid == self.total_invoiced :
 161                  return self.total_paid
 162              else:
 163                  return ((self.total_paid or 0.00) - (self.total_invoiced or 0.00))
 164         else:
 165             return False
 166
 167     def _sum_invoice(self, objects):
 168         return reduce(lambda acc, obj:
 169                         acc + obj.invoice_id.amount_total,
 170                         [o for o in objects if o.invoice_id and o.invoice_id.number],
 171                         0.0)
 172
 173     def _ellipsis(self, orig_str, maxlen=100, ellipsis='...'):
 174         maxlen = maxlen - len(ellipsis)
 175         if maxlen <= 0:
 176             maxlen = 1
 177         new_str = orig_str[:maxlen]
 178         return new_str
 179
 180     def _strip_name(self, name, maxlen=50):
 181         return self._ellipsis(name, maxlen, ' ...')
 182
 183     def _get_tax_amount(self, form,user):
 184         res = {}
 185         temp={}
 186         list_ids = []
 187         c=[]
 188         temp2 = 0.0
 189         pos_ids=self.pool.get("pos.order").search(self.cr, self.uid, [('date_order','>=',form['date_start'] + ' 00:00:00'),('date_order','<=',form['date_end'] + ' 23:59:59'),('state','in',['paid','invoiced','done']),('user_id','=',self.uid)])
 190         temp.update({'name':''})
 191         for order in self.pool.get("pos.order").browse(self.cr, self.uid, pos_ids):
 192             temp2 +=order.amount_tax
 193             for line in order.lines:
 194                 if len(line.product_id.taxes_id):
 195                     tax = line.product_id.taxes_id[0]
 196                     res[tax.name] = (line.price_unit * line.qty * (1-(line.discount or 0.0) / 100.0)) + (tax.id in list_ids and res[tax.name] or 0)
 197                     list_ids.append(tax.id)
 198                     temp.update({'name':tax.name})
 199         temp.update({'amount':temp2})
 200         return [temp] or False
 201
 202 #    def _get_period(self, form):
 203 #        min_date = form['date_start']
 204 #        max_date = form['date_end']
 205 #        if min_date == max_date:
 206 #            return '%s' % min_date
 207 #        else:
 208 #            return '%s - %s' % (min_date, max_date)
 209     def _get_period(self, form):
 210         return form['date_start']
 211
 212     def _get_period2(self,form):
 213         return form['date_end']
 214
 215     def __init__(self, cr, uid, name, context):
 216         super(pos_details, self).__init__(cr, uid, name, context)
 217         self.total = 0.0
 218         self.qty = 0.0
 219         self.invoice_id = ''
 220         self.total_paid = 0.0
 221         self.total_invoiced = 0.0
 222         self.localcontext.update({
 223             'time': time,
 224             'strip_name': self._strip_name,
 225             'getpayments': self._get_payments,
 226             'getsumdisc': self._get_sum_discount,
 227             'gettotalofthaday': self._total_of_the_day,
 228             'gettaxamount': self._get_tax_amount,
 229             'getperiod': self._get_period,
 230             'getperiod2':self._get_period2,
 231             'pos_sales_details':self._pos_sales_details,
 232             'getqtytotal2': self._get_qty_total_2,
 233             'getsalestotal2': self._get_sales_total_2,
 234             'getsuminvoice2':self._get_sum_invoice_2,
 235             'getpaidtotal2': self._paid_total_2,
 236             'getinvoice':self._get_invoice,
 237         })
 238
 239 report_sxw.report_sxw('report.pos.details', 'pos.order', 'addons/point_of_sale_singer/report/pos_details.rml', parser=pos_details, header=None)
 240
 241 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
 242