pos_member_purchases.py 4.05 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115
# -*- coding: utf-8 -*-

from openerp import _, api, models, fields
from . import product_categories

class PosMemberPurchases(models.Model):

    _name = "lacagette.pos_member_purchases"

            
    def _retrieve_purchases(self, ids, filters):
        res = []
        try:
            sql = """SELECT 
                     coop.id as coop_id,coop.barcode_base as coop_num,coop.city as coop_city, coop.birthdate as coop_birthdate,
                     po.date_order, po.week_number, po.week_day,
                     pol.price_unit as product_price, pol.qty as product_qty, pol.discount as product_discount,
                     pt.name as product_name, pt.categ_id as product_categ_id, 
                     p.barcode as product_barcode, p.id as product_id
                     FROM 
                     pos_order as po
                     JOIN pos_order_line as pol ON (pol.order_id = po.id)
                     JOIN product_product as p ON (p.id = pol.product_id)
                     JOIN product_template as pt ON (pt.id = p.product_tmpl_id)
                     JOIN res_partner as coop ON (coop.id = po.partner_id)
                     WHERE coop.barcode_base IN %s AND coop.barcode like '0420%%'
                  """
            sql_var = [tuple(ids)]
            if 'month' in filters:
                sql += " AND TO_CHAR(po.date_order, 'YYYY-MM') = %s"
                sql_var.append(filters['month'])
            sql += " ORDER BY coop.id, po.date_order"
            self.env.cr.execute(sql , sql_var)
            req_res = self.env.cr.dictfetchall()

            if len(req_res) > 0:
                for p in req_res:
                    for attr in p.keys():
                        if p[attr] is None:
                            p[attr] = ''
                    res.append(p)
        except Exception as e:
            res = "Erreur req SL "+ str(e)
        return res


    def _retrieve_member_purchased_products(self, member_id):
        res = []
        try:
            sql = """SELECT
                     pol.product_id, SUM(qty) as total
                     FROM pos_order as po
                     JOIN pos_order_line as pol ON (pol.order_id = po.id)
                     WHERE 
                     po.partner_id = """
            sql += str(int(member_id))
            sql += """GROUP BY pol.product_id
                   ORDER BY total DESC
                   """
            self.env.cr.execute(sql)
            res = self.env.cr.dictfetchall()
        except Exception as e:
            res = "Erreur req SL "+ str(e)
        return res

    @api.model
    def get_member_purchases(self, data):
        res = {}
        try:
            num = int(data['partner_coop_num'])
            purchases = self._retrieve_purchases([str(num)],{})
            res['purchases'] = purchases
            res['pcat'] = self.env['lacagette.product_categories'].get_all()
        except Exception as e:
            res['error'] = str(e)
        return res

    @api.model
    def get_member_available_products_among_bought_pool(self, data):
        res = {}
        try:
            res['pdts'] = self._retrieve_member_purchased_products(data['member_id'])
        except Exception as e:
            res['error'] = str(e)
        return res


    @api.model
    def get_members_purchases(self, data):
        res = {}
        try:
            nums = []
            for n in data['partners_coop_num']:
                try:
                    num = int(n)
                    nums.append(num)
                except:
                    pass
            filters = {}
            if 'month' in data:
                month = data['month']
                y, m = month.split('-')
                try:
                    y = int(y)
                    m = int(m)
                    filters['month'] = month
                except:
                    pass
            purchases = self._retrieve_purchases(nums,filters)
            res['purchases'] = purchases
            res['pcat'] = self.env['lacagette.product_categories'].get_all()
        except Exception as e:
            res['error'] = str(e)
        return res