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