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