dolibarrAlchemyHledger.py

# -*- coding: utf-8 -*-
from __future__ import unicode_literals

import settings

import decimal

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']

    tva_type = settings.get('TVA_TYPE')

    sql_class = None

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

    def __init__(self, e):
        super(HledgerEntry, self).__init__()
        self.e = e
        self.accounting_date = e
        for attr in self.k_accounting_date.split('.'):
            self.accounting_date = getattr(self.accounting_date, attr)

    @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
        if isinstance(date, datetime.datetime):
            date = date.date()
        for (year, dbegin, dend) in HledgerEntry.accounting_years:
            if date >= dbegin and date <= dend:
                return year

        return str(date.year)

    @staticmethod
    def _value(value):
        return '{number:.{digits}f}'.format(number=value, digits=4)


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
    k_accounting_date = 'datev'
   
    @classmethod
    def get_third_code(cls, e):
        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:
            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]

        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']
            third_code = fn(e)
        
        if third_code == "":
            third_code = cls.pc_default_tiers

        return third_code

    @classmethod
    def get_description(self, e):
        s_nom = ""
        s_description = ""

        if e.url_company:
            s_nom = e.url_company.societe.nom

        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 = ""

        s_description = self.get_description(self.e)

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

        third_code = self.get_third_code(self.e)

        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),
            'amount': self._value(-e.amount)
        }
        s += "    %(account)s \t %(amount)s\n" % {
            'account': settings.get_ledger_account(third_code),
            'amount': self._value(e.amount)
        }
        
        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),
                        'amount_tva': self._value(tvas[k] * (f.amount / f.facture.total_ttc))
                    }
        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),
                        'amount_tva': self._value(tvas[k] * (f.amount / f.facture.total_ttc))
                    }
        else:
            pass

        return s

    @classmethod
    def get_entries(cls, session):
        return [cls(e) for e in session.query(cls.sql_class).order_by(Bank.datev, Bank.num_releve).all()]


class HledgerFactureEntry(HledgerEntry):

    @classmethod
    def get_entries(cls, session):
        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):
        tx = int(float(ed.tva_tx) * 100)
        
        key = "tva_regul_%s" % (tx,)

        return settings.get('PC_REFS')[key]

    # 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'

    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,
        }

        s_code = self.get_supplier_code(self.e)
        s += "    %(compte_tiers)s \t %(amount_ttc)s\n" % {
            'compte_tiers': settings.get_ledger_account(s_code),
            'amount_ttc': self._value(e.total_ttc),
        }

        for ed in e.details:
            p_code = self.get_product_account_code(ed)
            s += "    %(compte_produit)s \t %(amount_ht)s\n" % {
                'compte_produit': settings.get_ledger_account(p_code),
                'amount_ht': self._value(-ed.total_ht)
            }
        
        tvas = self.get_tva_facture_amounts(self.e, journal="supplier")
        for k in tvas:
            s += "    %(compte_tva)s \t %(amount_tva)s\n" % {
                'compte_tva': settings.get_ledger_account(k),
                'amount_tva': self._value(tvas[k]),
            }

        return s
  
    @classmethod
    def get_tva_account(cls, ed):
        p_code = cls.get_product_account_code(ed)
        tx = int(float(ed.tva_tx) * 100)
        
        if p_code.startswith("2"):
            prefix = 'tva_deductible'
        else:
            prefix = 'tva_deductible_immo'
        key = "%s_%s" % (prefix, tx)
        tva_account = settings.get('PC_REFS')[key]
        return tva_account

    @classmethod
    def get_product_account_code(cls, ed):
        p_code = ""
        if ed.accounting_account:
            p_code = ed.accounting_account.account_number
        elif ed.product:
            p_code = ed.product.accountancy_code_buy
        else:
            p_code = cls.pc_default_charge
        return p_code
   
    @classmethod
    def get_supplier_code(cls, e):
        s_code = e.societe.code_compta_fournisseur
        if s_code == "":
            s_code = cls.pc_default_supplier
        return s_code


class HledgerSellEntry(HledgerFactureEntry):
    sql_class = Facture
    k_accounting_date = 'datef'

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

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

        # ligne pour compte client
        s_code = self.get_client_code(self.e)
        s += "    %(compte_tiers)s    %(amount_ttc)s\n" % {
            'compte_tiers': settings.get_ledger_account(s_code),
            'amount_ttc': self._value(-e.total_ttc),

        }
        
        # lignes pour compte de produits
        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),
                'amount_ht': self._value(ed.total_ht)
            }

        # 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),
                'amount_tva': self._value(tvas[k]),
            }

        return s
    
    @classmethod
    def get_tva_account(cls, ed):
        tx = int(float(ed.tva_tx) * 100)
        key = "tva_collecte_%s" % (tx,)
        return settings.get('PC_REFS')[key]

    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

    @classmethod
    def get_product_account_code(cls, ed):
        p_code = ""
        if ed.accounting_account:
            p_code = ed.accounting_account.account_number
        elif ed.product:
            p_code = ed.product.accountancy_code_sell
        else:
            p_code = cls.pc_default_produit
        return p_code

    @classmethod
    def get_client_code(cls, e):
        s_code = e.societe.code_compta
        if s_code == "":
            s_code = cls.pc_default_client
        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)


class HledgerSocialEntry(HledgerEntry):
    sql_class = CotisationsSociales
    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()]

    @classmethod
    def get_third_code(cls, e):
        third_code = ""
        if e.type.code in settings.get('SOCIAL_REFS'):
            third_code = settings.get('SOCIAL_REFS')[e.type.code]
        if third_code == "":
            third_code = cls.pc_default_supplier
        return third_code

    @classmethod
    def get_social_code(cls, e):
        s_code = ""
        if e.type:
            s_code = e.type.accountancy_code
        if s_code == "":
            s_code = cls.pc_default_charge
        return s_code
    
    def getMissingPC(self):
        pc_missing = []
        if self.get_social_code(self.e) == self.pc_default_charge:
            pc_missing.append("charges: %s" % (e.libelle))

        if self.get_third_code(self.e) == self.pc_default_supplier:
            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
        }

        third_code = self.get_third_code(self.e)
        s_code = self.get_social_code(self.e)

        s += "    %(account)s \t %(amount)s\n" % {
            'account': settings.get_ledger_account(third_code),
            'amount': self._value(e.amount)
        }

        s += "    %(account)s \t %(amount)s\n" % {
            'account': settings.get_ledger_account(s_code),
            'amount': self._value(-e.amount)
        }

        return s

    def check(self):
        e = self.e


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)