dolibarrAlchemyHledger.py 17 KB
Newer Older
1
2
3
4
5
# -*- coding: utf-8 -*-
from __future__ import unicode_literals

import settings

plb's avatar
plb committed
6
7
import decimal

8
9
10
11
12
13
14
15
16
17
18
19
from himports.dolibarrAlchemy import *


class HledgerEntry(object):
    accounting_years = settings.get('ACCOUNTING_YEARS')
    pc_default_tiers = settings.get('PC_REFS')['default_tiers']
    pc_default_client = settings.get('PC_REFS')['default_client']
    pc_default_supplier = settings.get('PC_REFS')['default_supplier']
    pc_default_produit = settings.get('PC_REFS')['default_produit']
    pc_default_charge = settings.get('PC_REFS')['default_charge']
    pc_default_bank = settings.get('PC_REFS')['default_bank']

plb's avatar
plb committed
20
21
    tva_type = settings.get('TVA_TYPE')

22
23
    sql_class = None

24
25
26
27
    # Date permettant de déterminer dans quel exercice comptable
    # l'écriture doit se trouver
    k_accounting_date = None

28
29
30
    def __init__(self, e):
        super(HledgerEntry, self).__init__()
        self.e = e
31
32
33
        self.accounting_date = e
        for attr in self.k_accounting_date.split('.'):
            self.accounting_date = getattr(self.accounting_date, attr)
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

    @classmethod
    def get_entries(cls, session):
        return [cls(i) for i in session.query(cls.sql_class).all()]

    def get_ledger(self):
        print "WARNING: get_ledger not done"
        return u""

    def check_pc(self):
        return ()

    def get_year(self):
        raise Exception("TODO: get_year not implemented for class %s" % (self.__class__))

    def get_accounting_year(self):
        date = self.accounting_date
51
52
        if isinstance(date, datetime.datetime):
            date = date.date()
53
54
55
56
57
58
        for (year, dbegin, dend) in HledgerEntry.accounting_years:
            if date >= dbegin and date <= dend:
                return year

        return str(date.year)

plb's avatar
plb committed
59
60
61
62
    @staticmethod
    def _value(value):
        return '{number:.{digits}f}'.format(number=value, digits=4)

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

class HledgerJournal(object):

    def __init__(self, session, cls_entry):
        self.entries = cls_entry.get_entries(session)

    def get_entries(self):
        return self.entries

    def get_by_year(self):
        by_year = {}
        for entry in self.get_entries():

            entry_year = entry.get_accounting_year()
            if entry_year not in by_year:
                by_year[entry_year] = []
            by_year[entry_year].append(entry)
        return by_year
    
    def check_pc(self):
        pc_missing = set()
        for entry in self.get_entries():
            pc_missing.update(entry.check_pc())

        return pc_missing


class HledgerBankEntry(HledgerEntry):
    sql_class = Bank
92
    k_accounting_date = 'datev'
plb's avatar
plb committed
93
94
95
   
    @classmethod
    def get_third_code(cls, e):
96
97
98
99
100
101
        third_code = ""
        if e.url_payment_supplier:
            if e.url_company:
                third_code = e.url_company.societe.code_compta_fournisseur
        
        if e.url_payment_sc:
plb's avatar
plb committed
102
103
104
            code = e.url_payment_sc.payment_sc.cotisation_sociale.type.code 
            if code in settings.get('SOCIAL_REFS'):
                third_code = settings.get('SOCIAL_REFS')[code]
105
106
107
108
109
110
111

        if e.url_payment:
            if e.url_company:
                third_code = e.url_company.societe.code_compta

        if third_code == "":
            fn = settings.get('PC_REFS')['fn_custom_code']
plb's avatar
plb committed
112
113
114
115
            third_code = fn(e)
        
        if third_code == "":
            third_code = cls.pc_default_tiers
116
117
118

        return third_code

plb's avatar
plb committed
119
120
    @classmethod
    def get_description(self, e):
121
122
        s_nom = ""
        s_description = ""
123
124
125

        if e.url_company:
            s_nom = e.url_company.societe.nom
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148

        if e.url_payment_supplier:
            f_ids = [f.facture.ref_supplier for f in e.url_payment_supplier.payment_supplier.factures]
            s_description = "Règlement facture fournisseur - %s - %s" % (
                s_nom,
                "|".join(f_ids),
            )
        if e.url_payment:
            f_ids = [f.facture.facnumber for f in e.url_payment.payment.factures]
            s_description = "Règlement facture client - %s - %s" % (
                s_nom,
                "|".join(f_ids),
            )

        if s_description == "":
            s_description = s_nom + " - " + e.label

        return s_description

    def get_ledger(self):
        e = self.e
        s = ""

plb's avatar
plb committed
149
        s_description = self.get_description(self.e)
150
151
152

        s += "%(date)s    %(description)s\n" % {
            'date': e.datev.strftime("%Y/%m/%d"),
153
            'description': s_description
154
155
        }

plb's avatar
plb committed
156
        third_code = self.get_third_code(self.e)
157
158
159
160
161
162
163

        ba_code = e.account.account_number
        if ba_code == "":
            ba_code = self.pc_default_bank

        s += "    %(account)s \t %(amount)s\n" % {
            'account': settings.get_ledger_account(ba_code),
plb's avatar
plb committed
164
            'amount': self._value(-e.amount)
165
        }
plb's avatar
plb committed
166
167
        s += "    %(account)s \t %(amount)s\n" % {
            'account': settings.get_ledger_account(third_code),
plb's avatar
plb committed
168
            'amount': self._value(e.amount)
plb's avatar
plb committed
169
        }
plb's avatar
plb committed
170
        
plb's avatar
plb committed
171
172
173
174
175
176
        if e.url_payment_supplier:
            for f in e.url_payment_supplier.payment_supplier.factures:
                tvas = HledgerSupplierEntry.get_tva_paiement_amounts(f.facture, journal="bank")
                for k in tvas:
                    s += "    %(account_tva)s \t %(amount_tva)s\n" % {
                        'account_tva': settings.get_ledger_account(k),
plb's avatar
plb committed
177
                        'amount_tva': self._value(tvas[k] * (f.amount / f.facture.total_ttc))
plb's avatar
plb committed
178
179
180
181
182
183
184
                    }
        elif e.url_payment:
            for f in e.url_payment.payment.factures:
                tvas = HledgerSellEntry.get_tva_paiement_amounts(f.facture, journal="bank")
                for k in tvas:
                    s += "    %(account_tva)s \t %(amount_tva)s\n" % {
                        'account_tva': settings.get_ledger_account(k),
plb's avatar
plb committed
185
                        'amount_tva': self._value(tvas[k] * (f.amount / f.facture.total_ttc))
plb's avatar
plb committed
186
187
188
189
                    }
        else:
            pass

190
191
192
193
        return s

    @classmethod
    def get_entries(cls, session):
plb's avatar
plb committed
194
        return [cls(e) for e in session.query(cls.sql_class).order_by(Bank.datev, Bank.num_releve).all()]
195
196


plb's avatar
plb committed
197
class HledgerFactureEntry(HledgerEntry):
198
199
200

    @classmethod
    def get_entries(cls, session):
plb's avatar
plb committed
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
        return [cls(e) for e in session.query(cls.sql_class).order_by(cls.sql_class.datef).all()]

    @classmethod
    def is_tva_facture(cls, ed):
        return ed.productcls.tva_type == 'service_sur_debit' and ed.product_type == 1

    @classmethod
    def is_tva_paiement(cls, ed):
        return cls.tva_type != 'service_sur_debit' or ed.product_type != 1

    @classmethod
    def get_tva_amounts(cls, e, journal):

        tvas = dict()
        for ed in e.details:
            if isinstance(e, Facture):
                total_tva = -ed.total_tva
            elif isinstance(e, FactureFourn):
                total_tva = ed.tva
            else:
                raise Exception("Should be either Facture or FactureFourn")

            if total_tva == 0:
                continue

            tva_account = cls.get_tva_account(ed)
            tva_regul = cls.get_tva_regul_account(ed)

            if journal == "bank":
                if ed.product_type == 1 and cls.tva_type == 'standard':
                    if tva_regul not in tvas:
                        tvas[tva_regul] = 0
                    if tva_account not in tvas:
                        tvas[tva_account] = 0
                    tvas[tva_account] += -total_tva
                    tvas[tva_regul] += total_tva
 
            elif journal == "sell" or journal == "supplier":
                if ed.product_type == 1 and cls.tva_type == 'standard':
                    if tva_regul not in tvas:
                        tvas[tva_regul] = 0
                    tvas[tva_regul] += -total_tva
                else:
                    if tva_account not in tvas:
                        tvas[tva_account] = 0
                    tvas[tva_account] += -total_tva

        return tvas
    
    @classmethod
    def get_tva_regul_account(cls, ed):
plb's avatar
plb committed
252
253
254
255
256
        tx = int(float(ed.tva_tx) * 100)
        
        key = "tva_regul_%s" % (tx,)

        return settings.get('PC_REFS')[key]
plb's avatar
plb committed
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272

    # Calcul de la tva à décaisser à paiement de la facture
    #
    # Cela du type de produit (bien ou service) et du type de tva
    @classmethod
    def get_tva_facture_amounts(cls, e, journal):
        return cls.get_tva_amounts(e, journal)

    @classmethod
    def get_tva_paiement_amounts(cls, e, journal):
        return cls.get_tva_amounts(e, journal)


class HledgerSupplierEntry(HledgerFactureEntry):
    sql_class = FactureFourn
    k_accounting_date = 'datef'
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313

    def check(self):
        e = self.e

        total_ttc = e.total_ttc
        total_tva = e.total_tva
        total_ht = e.total_ht

        for ed in e.details:
            total_ttc -= ed.total_ttc
            total_tva -= ed.tva
            total_ht -= ed.total_ht

        if total_ttc > 1e-10:
            print "Erreur dans l'écriture %s: total ttc = %s" % (e.ref_supplier, total_ttc)
        if total_ht > 1e-10:
            print "Erreur dans l'écriture %s: total ht = %s" % (e.ref_supplier, total_ht)
        if total_tva > 1e-10:
            print "Erreur dans l'écriture %s: total tva = %s" % (e.ref_supplier, total_tva)

    def getMissingPC(self):
        pc_missing = []
        if e.societe.code_compta_fournisseur == "":
            pc_missing.append("tiers:fournisseur: %s %s" % (e.societe.nom, s.societe.ape))

        for ed in e.details:
            if self.get_product_account_code(ed) == self.pc_default_charge:
                pc_missing.append("achat: %s - %s" % (e.ref_supplier, ed.description.splitlines()[0]))

        return pc_missing

    def get_ledger(self):
        e = self.e
        self.check()

        s = ""
        s += "%(date)s  %(description)s\n" % {
            'date':  e.datef.strftime("%Y/%m/%d"),
            'description': e.ref_supplier + " - " + e.societe.nom,
        }

plb's avatar
plb committed
314
        s_code = self.get_supplier_code(self.e)
plb's avatar
plb committed
315
        s += "    %(compte_tiers)s \t %(amount_ttc)s\n" % {
316
            'compte_tiers': settings.get_ledger_account(s_code),
plb's avatar
plb committed
317
            'amount_ttc': self._value(e.total_ttc),
318
319
320
321
        }

        for ed in e.details:
            p_code = self.get_product_account_code(ed)
plb's avatar
plb committed
322
            s += "    %(compte_produit)s \t %(amount_ht)s\n" % {
323
                'compte_produit': settings.get_ledger_account(p_code),
plb's avatar
plb committed
324
                'amount_ht': self._value(-ed.total_ht)
325
            }
plb's avatar
plb committed
326
327
328
        
        tvas = self.get_tva_facture_amounts(self.e, journal="supplier")
        for k in tvas:
plb's avatar
plb committed
329
            s += "    %(compte_tva)s \t %(amount_tva)s\n" % {
plb's avatar
plb committed
330
                'compte_tva': settings.get_ledger_account(k),
plb's avatar
plb committed
331
                'amount_tva': self._value(tvas[k]),
plb's avatar
plb committed
332
333
            }

334
        return s
plb's avatar
plb committed
335
336
337
338
  
    @classmethod
    def get_tva_account(cls, ed):
        p_code = cls.get_product_account_code(ed)
plb's avatar
plb committed
339
340
        tx = int(float(ed.tva_tx) * 100)
        
plb's avatar
plb committed
341
        if p_code.startswith("2"):
plb's avatar
plb committed
342
            prefix = 'tva_deductible'
plb's avatar
plb committed
343
        else:
plb's avatar
plb committed
344
345
346
            prefix = 'tva_deductible_immo'
        key = "%s_%s" % (prefix, tx)
        tva_account = settings.get('PC_REFS')[key]
plb's avatar
plb committed
347
        return tva_account
348

plb's avatar
plb committed
349
350
    @classmethod
    def get_product_account_code(cls, ed):
351
352
353
354
355
356
        p_code = ""
        if ed.accounting_account:
            p_code = ed.accounting_account.account_number
        elif ed.product:
            p_code = ed.product.accountancy_code_buy
        else:
plb's avatar
plb committed
357
            p_code = cls.pc_default_charge
358
        return p_code
plb's avatar
plb committed
359
360
361
   
    @classmethod
    def get_supplier_code(cls, e):
362
363
        s_code = e.societe.code_compta_fournisseur
        if s_code == "":
plb's avatar
plb committed
364
            s_code = cls.pc_default_supplier
365
366
        return s_code

367

plb's avatar
plb committed
368
class HledgerSellEntry(HledgerFactureEntry):
369
370
371
372
373
374
375
    sql_class = Facture
    k_accounting_date = 'datef'

    def get_ledger(self):
        e = self.e
        self.check()
        s = ""
plb's avatar
plb committed
376
377

        # Date et description
378
379
380
381
382
        s += "%(date)s  %(description)s\n" % {
            'date':  e.datef.strftime("%Y/%m/%d"),
            'description': e.facnumber + " - " + e.societe.nom,
        }

plb's avatar
plb committed
383
384
        # ligne pour compte client
        s_code = self.get_client_code(self.e)
385
386
        s += "    %(compte_tiers)s    %(amount_ttc)s\n" % {
            'compte_tiers': settings.get_ledger_account(s_code),
plb's avatar
plb committed
387
            'amount_ttc': self._value(-e.total_ttc),
388
389

        }
plb's avatar
plb committed
390
391
        
        # lignes pour compte de produits
392
393
394
395
        for ed in e.details:
            p_code = self.get_product_account_code(ed)
            s += "    %(compte_produit)s   %(amount_ht)s\n" % {
                'compte_produit': settings.get_ledger_account(p_code),
plb's avatar
plb committed
396
                'amount_ht': self._value(ed.total_ht)
397
            }
plb's avatar
plb committed
398
399
400
401
402
403

        # lignes pour la tva
        tvas = self.get_tva_facture_amounts(self.e, journal="sell")
        for k in tvas:
            s += "    %(compte_tva)s  %(amount_tva)s\n" % {
                'compte_tva': settings.get_ledger_account(k),
plb's avatar
plb committed
404
                'amount_tva': self._value(tvas[k]),
plb's avatar
plb committed
405
406
            }

407
        return s
plb's avatar
plb committed
408
409
410
    
    @classmethod
    def get_tva_account(cls, ed):
plb's avatar
plb committed
411
412
413
        tx = int(float(ed.tva_tx) * 100)
        key = "tva_collecte_%s" % (tx,)
        return settings.get('PC_REFS')[key]
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430

    def getMissingPC(self):
        e = self.e
        pc_missing = []
        if e.societe.code_compta == "":
            pc_missing.append("tiers: %s %s" % (e.societe.nom, s.societe.ape))

        for ed in e.details:
            if self.get_product_account_code(ed) == self.pc_default_produit:
                if ed.description != "":
                    description = ed.description.splitlines()[0]
                else:
                    description = ed.description
                pc_missing.append("produit: %s - %s - %s" % (e.societe.nom, e.facnumber, description))

        return pc_missing

plb's avatar
plb committed
431
432
    @classmethod
    def get_product_account_code(cls, ed):
433
434
435
436
437
438
        p_code = ""
        if ed.accounting_account:
            p_code = ed.accounting_account.account_number
        elif ed.product:
            p_code = ed.product.accountancy_code_sell
        else:
plb's avatar
plb committed
439
            p_code = cls.pc_default_produit
440
441
        return p_code

plb's avatar
plb committed
442
443
    @classmethod
    def get_client_code(cls, e):
444
445
        s_code = e.societe.code_compta
        if s_code == "":
plb's avatar
plb committed
446
            s_code = cls.pc_default_client
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
        return s_code

    def check(self):
        e = self.e
        total_ttc = e.total_ttc
        total_tva = e.tva
        total_ht = e.total

        for ed in e.details:
            total_ttc -= ed.total_ttc
            total_tva -= ed.total_tva
            total_ht -= ed.total_ht

        if total_ttc > 1e-10:
            print "Erreur dans l'écriture %s: total ttc = %s" % (e.facnumber, total_ttc)
        if total_ht > 1e-10:
            print "Erreur dans l'écriture %s: total ht = %s" % (e.facnumber, total_ht)
        if total_tva > 1e-10:
            print "Erreur dans l'écriture %s: total tva = %s" % (e.facnumber, total_tva)
466
467
468
469


class HledgerSocialEntry(HledgerEntry):
    sql_class = CotisationsSociales
470
471
472
473
474
475
    k_accounting_date = 'date_ech'
    
    @classmethod
    def get_entries(cls, session):
        return [cls(e) for e in session.query(cls.sql_class).order_by(CotisationsSociales.date_ech).all()]

plb's avatar
plb committed
476
477
    @classmethod
    def get_third_code(cls, e):
478
479
480
481
        third_code = ""
        if e.type.code in settings.get('SOCIAL_REFS'):
            third_code = settings.get('SOCIAL_REFS')[e.type.code]
        if third_code == "":
plb's avatar
plb committed
482
            third_code = cls.pc_default_supplier
483
484
        return third_code

plb's avatar
plb committed
485
486
    @classmethod
    def get_social_code(cls, e):
487
488
489
490
        s_code = ""
        if e.type:
            s_code = e.type.accountancy_code
        if s_code == "":
plb's avatar
plb committed
491
            s_code = cls.pc_default_charge
492
493
494
495
        return s_code
    
    def getMissingPC(self):
        pc_missing = []
plb's avatar
plb committed
496
        if self.get_social_code(self.e) == self.pc_default_charge:
497
498
            pc_missing.append("charges: %s" % (e.libelle))

plb's avatar
plb committed
499
        if self.get_third_code(self.e) == self.pc_default_supplier:
500
501
502
503
504
505
506
507
508
509
510
511
512
513
            pc_missing.append("tiers: %s (%s)" % (e.libelle, e.type.code))

        return pc_missing

    def get_ledger(self):
        e = self.e

        s = ""

        s += "%(date)s    %(description)s\n" % {
            'date': e.date_ech.strftime("%Y/%m/%d"),
            'description': e.libelle + " - " + e.type.libelle
        }

plb's avatar
plb committed
514
515
        third_code = self.get_third_code(self.e)
        s_code = self.get_social_code(self.e)
516
517
518

        s += "    %(account)s \t %(amount)s\n" % {
            'account': settings.get_ledger_account(third_code),
plb's avatar
plb committed
519
            'amount': self._value(e.amount)
520
521
522
523
        }

        s += "    %(account)s \t %(amount)s\n" % {
            'account': settings.get_ledger_account(s_code),
plb's avatar
plb committed
524
            'amount': self._value(-e.amount)
525
526
527
528
529
530
        }

        return s

    def check(self):
        e = self.e
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548


class HledgerDolibarrSQLAlchemy(DolibarrSQLAlchemy):

    def get_bank_journal(self):
        return HledgerJournal(self.session, HledgerBankEntry)

    def get_supplier_journal(self):
        return HledgerJournal(self.session, HledgerSupplierEntry)

    def get_sell_journal(self):
        return HledgerJournal(self.session, HledgerSellEntry)

    def get_social_journal(self):
        return HledgerJournal(self.session, HledgerSocialEntry)