pos_payments_export.py 4.73 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 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