himport/himports/dolibarrAlchemyHledger.py

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

import settings

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

    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)


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'
    
    def get_third_code(self):
        e = self.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:
            third_code = e.url_payment_sc.payment_sc.cotisation_sociale.type.accountancy_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(self.e)

        return third_code

    def get_description(self):
        e = 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()

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

        third_code = self.get_third_code()
        if third_code == "":
            third_code = self.pc_default_tiers

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

        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': -e.amount
        }
        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).filter(Bank.num_releve != "").all()]


class HledgerSupplierEntry(HledgerEntry):
    sql_class = FactureFourn
    k_accounting_date = 'datef'

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

    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()
        s += "    %(compte_tiers)s    %(amount_ttc)s\n" % {
            'compte_tiers': settings.get_ledger_account(s_code),
            'amount_ttc': e.total_ttc,
        }

        if e.total_tva != 0:
            if s_code.startswith("2"):
                tva_account = settings.get('PC_REFS')['tva_deductible']
            else:
                tva_account = settings.get('PC_REFS')['tva_deductible_immo']
            s += "    %(compte_tva)s  %(amount_tva)s\n" % {
                'compte_tva': settings.get_ledger_account(tva_account),
                'amount_tva': -e.total_tva,
            }

        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': -ed.total_ht
            }
        return s

    def get_product_account_code(self, 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 = self.pc_default_charge
        return p_code
    
    def get_supplier_code(self):
        e = self.e
        s_code = e.societe.code_compta_fournisseur
        if s_code == "":
            s_code = self.pc_default_supplier
        return s_code



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

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

    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.facnumber + " - " + e.societe.nom,
        }

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

        }

        if float(e.tva) != 0:
            tva_account = settings.get('PC_REFS')['tva_collecte']
            s += "    %(compte_tva_collecte)s  %(amount_tva)s\n" % {
                'compte_tva_collecte': settings.get_ledger_account(tva_account),
                'amount_tva': e.tva,
            }

        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': ed.total_ht
            }
        return s

    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

    def get_product_account_code(self, 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 = self.pc_default_produit
        return p_code

    def get_client_code(self):
        e = self.e
        s_code = e.societe.code_compta
        if s_code == "":
            s_code = self.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()]

    def get_third_code(self):
        e = self.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 = self.pc_default_supplier
        return third_code

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

        if self.get_third_code() == 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()
        s_code = self.get_social_code()

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

        s += "    %(account)s \t %(amount)s\n" % {
            'account': settings.get_ledger_account(s_code),
            'amount': -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)