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
116
117
118
119
120
121
122
123
124
125
126
127
# -*- coding: utf-8 -*-
from openerp import _, api, models, fields
class PosPaymentsExport(models.Model):
_name = "lacagette.pos_payments_export"
def _retrieve_sessions_orders_min_max_dates(self, session_ids):
res = []
try:
sql = """
SELECT
session_id, MIN(date_order) as min_date, MAX(date_order) as max_date
FROM pos_order
WHERE session_id IN %s
GROUP BY session_id
"""
self.env.cr.execute(sql , [tuple(session_ids)])
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 requete SQL : "+ str(e)
return res
def _retrieve_sessions_payments(self, session_ids):
res = []
try:
sql = """
SELECT
name,
(SELECT name FROM account_journal WHERE id = abs.journal_id) as journal,
total_entry_encoding_sales as total_amount,
pos_session_id
FROM account_bank_statement as abs
WHERE
abs.pos_session_id IN %s
"""
self.env.cr.execute(sql , [tuple(session_ids)])
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 requete SQL : "+ str(e)
return res
def _retrieve_sessions(self, month):
res = []
try:
sql = """
SELECT
id, name, total_amount, stop_at,
(SELECT name FROM res_partner WHERE id = (select partner_id from res_users where id = pos_session.user_id)) as caisse
FROM pos_session
WHERE
TO_CHAR(stop_at, 'YYYY-MM') = %s
AND state = 'closed'
ORDER BY stop_at ASC
"""
self.env.cr.execute(sql , [month])
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 requete SQL : "+ str(e)
return res
@api.model
def get_pos_payments(self, data):
res = {}
try:
if 'month' in data:
month = data['month']
y, m = month.split('-')
try:
y = int(y)
m = int(m)
sessions = self._retrieve_sessions(month)
if sessions:
sessions_ids = []
for s in sessions:
sessions_ids. append(s['id'])
orders_dates = self._retrieve_sessions_orders_min_max_dates(sessions_ids)
payments = self._retrieve_sessions_payments(sessions_ids)
sessions_mm_dates = {}
sessions_payments = {}
for row in orders_dates:
sessions_mm_dates[str(row['session_id'])] = {'min': row['min_date'], 'max': row['max_date']}
for row in payments:
key = str(row['pos_session_id'])
if not (key in sessions_payments):
sessions_payments[key] = []
sessions_payments[key].append(row)
for s in sessions:
if str(s['id']) in sessions_mm_dates:
s['mm_dates'] = sessions_mm_dates[str(s['id'])]
else:
s['mm_dates'] = {}
if str(s['id']) in sessions_payments:
s['payments'] = sessions_payments[str(s['id'])]
res['sessions'] = sessions
# res['orders_dates'] = orders_dates
# res['payments'] = payments
except Exception as e:
res['error'] = str(e)
except Exception as e:
res['error'] = str(e)
return res