48ec3a3d89
- db: added client_negative_floor column to banks - bank_manager: vault check on depot, negative floor on retrait/virement - banque: new /banque set-plafond and /banque set-reserve commands - bank_card: new Pillow-based bank card image generator - panel: full rewrite (no more JSON), guild name in title, new banking flow
557 lines
22 KiB
Python
557 lines
22 KiB
Python
import json
|
|
from datetime import datetime
|
|
from typing import Optional
|
|
|
|
import aiosqlite
|
|
|
|
from utils.db import get_db
|
|
from utils.rib import generer_rib, valider_rib, decoder_rib
|
|
|
|
GUIICHET_PARTICULIER = 4
|
|
GUIICHET_ENTREPRISE = 3
|
|
GUIICHET_BANQUE = 2
|
|
GUIICHET_INSTITUTION = 1
|
|
|
|
|
|
class BankManager:
|
|
|
|
async def creer_banque(self, guild_id: int, name: str, role_id: int) -> dict:
|
|
db = await get_db()
|
|
try:
|
|
cursor = await db.execute(
|
|
"SELECT COALESCE(MAX(establishment_code), 9999) FROM banks WHERE guild_id = ?",
|
|
(guild_id,),
|
|
)
|
|
row = await cursor.fetchone()
|
|
last_code = row[0]
|
|
new_code = last_code + 1
|
|
if new_code > 89999:
|
|
raise ValueError("Plus de codes d'établissement disponibles.")
|
|
|
|
cursor = await db.execute(
|
|
"INSERT INTO banks (guild_id, name, role_id, establishment_code) VALUES (?, ?, ?, ?)",
|
|
(guild_id, name, role_id, new_code),
|
|
)
|
|
await db.commit()
|
|
bank_id = cursor.lastrowid
|
|
return {"id": bank_id, "name": name, "role_id": role_id, "establishment_code": new_code}
|
|
finally:
|
|
await db.close()
|
|
|
|
async def supprimer_banque(self, guild_id: int, bank_id: int) -> bool:
|
|
db = await get_db()
|
|
try:
|
|
cursor = await db.execute(
|
|
"DELETE FROM banks WHERE id = ? AND guild_id = ?", (bank_id, guild_id)
|
|
)
|
|
await db.commit()
|
|
return cursor.rowcount > 0
|
|
finally:
|
|
await db.close()
|
|
|
|
async def get_banque(self, guild_id: int, bank_id: int) -> Optional[dict]:
|
|
db = await get_db()
|
|
try:
|
|
cursor = await db.execute(
|
|
"SELECT * FROM banks WHERE id = ? AND guild_id = ?", (bank_id, guild_id)
|
|
)
|
|
row = await cursor.fetchone()
|
|
return dict(row) if row else None
|
|
finally:
|
|
await db.close()
|
|
|
|
async def get_banque_by_role(self, guild_id: int, role_id: int) -> Optional[dict]:
|
|
db = await get_db()
|
|
try:
|
|
cursor = await db.execute(
|
|
"SELECT * FROM banks WHERE guild_id = ? AND role_id = ?", (guild_id, role_id)
|
|
)
|
|
row = await cursor.fetchone()
|
|
return dict(row) if row else None
|
|
finally:
|
|
await db.close()
|
|
|
|
async def get_banque_by_name(self, guild_id: int, name: str) -> Optional[dict]:
|
|
db = await get_db()
|
|
try:
|
|
cursor = await db.execute(
|
|
"SELECT * FROM banks WHERE guild_id = ? AND name = ?", (guild_id, name)
|
|
)
|
|
row = await cursor.fetchone()
|
|
return dict(row) if row else None
|
|
finally:
|
|
await db.close()
|
|
|
|
async def lister_banques(self, guild_id: int) -> list:
|
|
db = await get_db()
|
|
try:
|
|
cursor = await db.execute(
|
|
"SELECT * FROM banks WHERE guild_id = ? ORDER BY name", (guild_id,)
|
|
)
|
|
rows = await cursor.fetchall()
|
|
return [dict(r) for r in rows]
|
|
finally:
|
|
await db.close()
|
|
|
|
async def ouvrir_compte(
|
|
self,
|
|
guild_id: int,
|
|
bank_id: int,
|
|
user_id: int,
|
|
account_type: str,
|
|
tva_rate: float = 0.0,
|
|
password: str = None,
|
|
) -> dict:
|
|
db = await get_db()
|
|
try:
|
|
cursor = await db.execute(
|
|
"SELECT * FROM banks WHERE id = ? AND guild_id = ?", (bank_id, guild_id)
|
|
)
|
|
bank = await cursor.fetchone()
|
|
if not bank:
|
|
raise ValueError("Banque introuvable.")
|
|
|
|
guichet = GUIICHET_ENTREPRISE if account_type == "entreprise" else GUIICHET_PARTICULIER
|
|
|
|
cursor = await db.execute(
|
|
"SELECT COUNT(*) FROM accounts WHERE guild_id = ? AND bank_id = ? AND user_id = ? AND account_type = ?",
|
|
(guild_id, bank_id, user_id, account_type),
|
|
)
|
|
count = (await cursor.fetchone())[0]
|
|
if count > 0:
|
|
raise ValueError(f"Ce membre a déjà un compte {account_type} dans cette banque.")
|
|
|
|
rib = generer_rib(bank["establishment_code"], guichet)
|
|
|
|
cursor = await db.execute(
|
|
"INSERT INTO accounts (guild_id, bank_id, user_id, rib, account_type, tva_rate, password) VALUES (?, ?, ?, ?, ?, ?, ?)",
|
|
(guild_id, bank_id, user_id, rib, account_type, tva_rate, password),
|
|
)
|
|
await db.commit()
|
|
return {"id": cursor.lastrowid, "rib": rib, "account_type": account_type, "tva_rate": tva_rate}
|
|
finally:
|
|
await db.close()
|
|
|
|
async def get_compte_by_rib(self, guild_id: int, rib: str) -> Optional[dict]:
|
|
db = await get_db()
|
|
try:
|
|
cursor = await db.execute(
|
|
"""SELECT a.*, b.name as bank_name, b.establishment_code, b.role_id as bank_role_id
|
|
FROM accounts a JOIN banks b ON a.bank_id = b.id
|
|
WHERE a.rib = ? AND a.guild_id = ?""",
|
|
(rib, guild_id),
|
|
)
|
|
row = await cursor.fetchone()
|
|
return dict(row) if row else None
|
|
finally:
|
|
await db.close()
|
|
|
|
async def get_comptes_user(self, guild_id: int, user_id: int) -> list:
|
|
db = await get_db()
|
|
try:
|
|
cursor = await db.execute(
|
|
"""SELECT a.*, b.name as bank_name, b.establishment_code
|
|
FROM accounts a JOIN banks b ON a.bank_id = b.id
|
|
WHERE a.guild_id = ? AND a.user_id = ?
|
|
ORDER BY b.name, a.account_type""",
|
|
(guild_id, user_id),
|
|
)
|
|
rows = await cursor.fetchall()
|
|
return [dict(r) for r in rows]
|
|
finally:
|
|
await db.close()
|
|
|
|
async def get_comptes_banque(self, guild_id: int, bank_id: int) -> list:
|
|
db = await get_db()
|
|
try:
|
|
cursor = await db.execute(
|
|
"""SELECT a.*, b.name as bank_name
|
|
FROM accounts a JOIN banks b ON a.bank_id = b.id
|
|
WHERE a.guild_id = ? AND a.bank_id = ?
|
|
ORDER BY a.user_id, a.account_type""",
|
|
(guild_id, bank_id),
|
|
)
|
|
rows = await cursor.fetchall()
|
|
return [dict(r) for r in rows]
|
|
finally:
|
|
await db.close()
|
|
|
|
async def modifier_tva(self, guild_id: int, rib: str, nouveau_taux: float) -> bool:
|
|
db = await get_db()
|
|
try:
|
|
cursor = await db.execute(
|
|
"UPDATE accounts SET tva_rate = ? WHERE rib = ? AND guild_id = ? AND account_type = 'entreprise'",
|
|
(nouveau_taux, rib, guild_id),
|
|
)
|
|
await db.commit()
|
|
return cursor.rowcount > 0
|
|
finally:
|
|
await db.close()
|
|
|
|
async def virement(
|
|
self,
|
|
guild_id: int,
|
|
rib_source: str,
|
|
rib_dest: str,
|
|
montant: float,
|
|
emetteur_id: int = None,
|
|
destinataire_id: int = None,
|
|
) -> dict:
|
|
if montant <= 0:
|
|
raise ValueError("Le montant doit être supérieur à 0.")
|
|
if rib_source == rib_dest:
|
|
raise ValueError("Impossible de se virer à soi-même.")
|
|
|
|
db = await get_db()
|
|
try:
|
|
source = await self._get_account(db, guild_id, rib_source)
|
|
if not source:
|
|
raise ValueError("Compte source introuvable.")
|
|
dest = await self._get_account(db, guild_id, rib_dest)
|
|
if not dest:
|
|
raise ValueError("Compte destination introuvable.")
|
|
|
|
if source["solde"] < montant:
|
|
cursor = await db.execute(
|
|
"SELECT client_negative_floor FROM banks WHERE id = ? AND guild_id = ?",
|
|
(source["bank_id"], guild_id),
|
|
)
|
|
bank_row = await cursor.fetchone()
|
|
floor = bank_row[0] if bank_row else 0.0
|
|
nouveau_solde = source["solde"] - montant
|
|
if nouveau_solde < -floor:
|
|
raise ValueError(
|
|
f"Solde insuffisant. Disponible : {source['solde']:.2f}€, plafond négatif : {-floor:.2f}€"
|
|
)
|
|
|
|
tva_montant = 0.0
|
|
montant_net = montant
|
|
|
|
if source["account_type"] == "particulier" and dest["account_type"] == "entreprise":
|
|
tva_rate = dest["tva_rate"] or 0.0
|
|
if tva_rate > 0:
|
|
tva_montant = round(montant * tva_rate / 100, 2)
|
|
montant_net = round(montant - tva_montant, 2)
|
|
|
|
cursor = await db.execute(
|
|
"UPDATE accounts SET solde = solde - ? WHERE rib = ? AND guild_id = ?",
|
|
(montant, rib_source, guild_id),
|
|
)
|
|
cursor = await db.execute(
|
|
"UPDATE accounts SET solde = solde + ? WHERE rib = ? AND guild_id = ?",
|
|
(montant_net, rib_dest, guild_id),
|
|
)
|
|
|
|
if tva_montant > 0:
|
|
fisc_rib = await self._get_fisc_rib(db, guild_id)
|
|
if fisc_rib:
|
|
cursor = await db.execute(
|
|
"UPDATE accounts SET solde = solde + ? WHERE rib = ? AND guild_id = ?",
|
|
(tva_montant, fisc_rib, guild_id),
|
|
)
|
|
|
|
cursor = await db.execute(
|
|
"UPDATE banks SET clients_total_balance = clients_total_balance - ? WHERE id = ? AND guild_id = ?",
|
|
(montant, source["bank_id"], guild_id),
|
|
)
|
|
cursor = await db.execute(
|
|
"UPDATE banks SET clients_total_balance = clients_total_balance + ? WHERE id = ? AND guild_id = ?",
|
|
(montant_net, dest["bank_id"], guild_id),
|
|
)
|
|
|
|
now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
|
|
await db.execute(
|
|
"""INSERT INTO transactions (guild_id, emetteur_rib, destinataire_rib, emetteur_id, destinataire_id, montant, type, date)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
|
|
(guild_id, rib_source, rib_dest, emetteur_id or source["user_id"], destinataire_id or dest["user_id"], montant, "virement", now),
|
|
)
|
|
if tva_montant > 0 and fisc_rib:
|
|
await db.execute(
|
|
"""INSERT INTO transactions (guild_id, destinataire_rib, montant, type, date, metadata)
|
|
VALUES (?, ?, ?, ?, ?, ?)""",
|
|
(guild_id, fisc_rib, tva_montant, "tva_prelevee", now, json.dumps({"source_rib": rib_source, "dest_rib": rib_dest})),
|
|
)
|
|
|
|
await db.commit()
|
|
|
|
result = {
|
|
"montant_envoye": montant,
|
|
"montant_recu": montant_net,
|
|
"tva_prelevee": tva_montant,
|
|
}
|
|
if tva_montant > 0:
|
|
result["tva_dest"] = fisc_rib
|
|
return result
|
|
finally:
|
|
await db.close()
|
|
|
|
async def depot_banquier(
|
|
self, guild_id: int, rib: str, montant: float, banquier_id: int
|
|
) -> dict:
|
|
if montant <= 0:
|
|
raise ValueError("Le montant doit être supérieur à 0.")
|
|
|
|
db = await get_db()
|
|
try:
|
|
compte = await self._get_account(db, guild_id, rib)
|
|
if not compte:
|
|
raise ValueError("Compte introuvable.")
|
|
|
|
cursor = await db.execute(
|
|
"SELECT vault_balance FROM banks WHERE id = ? AND guild_id = ?",
|
|
(compte["bank_id"], guild_id),
|
|
)
|
|
bank_row = await cursor.fetchone()
|
|
if not bank_row:
|
|
raise ValueError("Banque introuvable.")
|
|
vault = bank_row[0]
|
|
if vault < montant:
|
|
raise ValueError(
|
|
f"Réserve bancaire insuffisante. Disponible : {vault:.2f}€"
|
|
)
|
|
|
|
cursor = await db.execute(
|
|
"UPDATE accounts SET solde = solde + ? WHERE rib = ? AND guild_id = ?",
|
|
(montant, rib, guild_id),
|
|
)
|
|
cursor = await db.execute(
|
|
"UPDATE banks SET clients_total_balance = clients_total_balance + ?, vault_balance = vault_balance - ? WHERE id = ? AND guild_id = ?",
|
|
(montant, montant, compte["bank_id"], guild_id),
|
|
)
|
|
|
|
now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
|
|
await db.execute(
|
|
"""INSERT INTO transactions (guild_id, destinataire_rib, destinataire_id, montant, type, date, metadata)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?)""",
|
|
(guild_id, rib, compte["user_id"], montant, "depot_banquier", now, json.dumps({"banquier_id": banquier_id})),
|
|
)
|
|
await db.commit()
|
|
return {"nouveau_solde": compte["solde"] + montant}
|
|
finally:
|
|
await db.close()
|
|
|
|
async def retrait_banquier(
|
|
self, guild_id: int, rib: str, montant: float, banquier_id: int
|
|
) -> dict:
|
|
if montant <= 0:
|
|
raise ValueError("Le montant doit être supérieur à 0.")
|
|
|
|
db = await get_db()
|
|
try:
|
|
compte = await self._get_account(db, guild_id, rib)
|
|
if not compte:
|
|
raise ValueError("Compte introuvable.")
|
|
|
|
cursor = await db.execute(
|
|
"SELECT client_negative_floor FROM banks WHERE id = ? AND guild_id = ?",
|
|
(compte["bank_id"], guild_id),
|
|
)
|
|
bank_row = await cursor.fetchone()
|
|
floor = bank_row[0] if bank_row else 0.0
|
|
nouveau_solde = compte["solde"] - montant
|
|
if nouveau_solde < -floor:
|
|
raise ValueError(
|
|
f"Plafond négatif atteint ({-floor:.2f}€). Solde actuel : {compte['solde']:.2f}€"
|
|
)
|
|
|
|
cursor = await db.execute(
|
|
"UPDATE accounts SET solde = solde - ? WHERE rib = ? AND guild_id = ?",
|
|
(montant, rib, guild_id),
|
|
)
|
|
cursor = await db.execute(
|
|
"UPDATE banks SET clients_total_balance = clients_total_balance - ?, vault_balance = vault_balance + ? WHERE id = ? AND guild_id = ?",
|
|
(montant, montant, compte["bank_id"], guild_id),
|
|
)
|
|
|
|
now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
|
|
await db.execute(
|
|
"""INSERT INTO transactions (guild_id, emetteur_rib, emetteur_id, montant, type, date, metadata)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?)""",
|
|
(guild_id, rib, compte["user_id"], montant, "retrait_banquier", now, json.dumps({"banquier_id": banquier_id})),
|
|
)
|
|
await db.commit()
|
|
return {"nouveau_solde": nouveau_solde}
|
|
finally:
|
|
await db.close()
|
|
|
|
async def get_transactions(self, guild_id: int, rib: str, limit: int = 10) -> list:
|
|
db = await get_db()
|
|
try:
|
|
cursor = await db.execute(
|
|
"""SELECT * FROM transactions
|
|
WHERE guild_id = ? AND (emetteur_rib = ? OR destinataire_rib = ?)
|
|
ORDER BY date DESC LIMIT ?""",
|
|
(guild_id, rib, rib, limit),
|
|
)
|
|
rows = await cursor.fetchall()
|
|
return [dict(r) for r in rows]
|
|
finally:
|
|
await db.close()
|
|
|
|
async def creer_institution(
|
|
self, guild_id: int, name: str, role_id: int = None, can_audit_all: bool = False
|
|
) -> dict:
|
|
db = await get_db()
|
|
try:
|
|
cursor = await db.execute(
|
|
"INSERT INTO institutions (guild_id, name, role_id, can_audit_all) VALUES (?, ?, ?, ?)",
|
|
(guild_id, name, role_id, int(can_audit_all)),
|
|
)
|
|
await db.commit()
|
|
return {"id": cursor.lastrowid, "name": name, "can_audit_all": can_audit_all}
|
|
finally:
|
|
await db.close()
|
|
|
|
async def get_institution(self, guild_id: int, institution_id: int) -> Optional[dict]:
|
|
db = await get_db()
|
|
try:
|
|
cursor = await db.execute(
|
|
"SELECT * FROM institutions WHERE id = ? AND guild_id = ?",
|
|
(institution_id, guild_id),
|
|
)
|
|
row = await cursor.fetchone()
|
|
return dict(row) if row else None
|
|
finally:
|
|
await db.close()
|
|
|
|
async def get_institution_by_role(self, guild_id: int, role_id: int) -> Optional[dict]:
|
|
db = await get_db()
|
|
try:
|
|
cursor = await db.execute(
|
|
"SELECT * FROM institutions WHERE guild_id = ? AND role_id = ?",
|
|
(guild_id, role_id),
|
|
)
|
|
row = await cursor.fetchone()
|
|
return dict(row) if row else None
|
|
finally:
|
|
await db.close()
|
|
|
|
async def lister_institutions(self, guild_id: int) -> list:
|
|
db = await get_db()
|
|
try:
|
|
cursor = await db.execute(
|
|
"SELECT * FROM institutions WHERE guild_id = ? ORDER BY name", (guild_id,)
|
|
)
|
|
rows = await cursor.fetchall()
|
|
return [dict(r) for r in rows]
|
|
finally:
|
|
await db.close()
|
|
|
|
async def configurer_fisc(self, guild_id: int, institution_id: int) -> bool:
|
|
db = await get_db()
|
|
try:
|
|
inst = await self.get_institution(guild_id, institution_id)
|
|
if not inst:
|
|
return False
|
|
|
|
guichet = GUIICHET_INSTITUTION
|
|
bank = await self._get_gouvernement_bank(db, guild_id)
|
|
if not bank:
|
|
cursor = await db.execute(
|
|
"INSERT INTO banks (guild_id, name, role_id, establishment_code) VALUES (?, ?, ?, ?)",
|
|
(guild_id, "Gouvernement", 0, 1),
|
|
)
|
|
bank_id = cursor.lastrowid
|
|
bank = {"id": bank_id, "establishment_code": 1}
|
|
else:
|
|
bank_id = bank["id"]
|
|
|
|
rib = generer_rib(bank["establishment_code"], guichet)
|
|
|
|
existing = await db.execute(
|
|
"SELECT rib FROM accounts WHERE guild_id = ? AND bank_id = ? AND user_id = ? AND account_type = 'entreprise'",
|
|
(guild_id, bank_id, institution_id),
|
|
)
|
|
existing_row = await existing.fetchone()
|
|
if existing_row:
|
|
rib = existing_row[0]
|
|
else:
|
|
cursor = await db.execute(
|
|
"INSERT INTO accounts (guild_id, bank_id, user_id, rib, account_type, solde) VALUES (?, ?, ?, ?, 'entreprise', 0.0)",
|
|
(guild_id, bank_id, institution_id, rib),
|
|
)
|
|
|
|
await db.execute(
|
|
"INSERT OR REPLACE INTO fisc_config (guild_id, institution_id) VALUES (?, ?)",
|
|
(guild_id, institution_id),
|
|
)
|
|
await db.commit()
|
|
return True
|
|
finally:
|
|
await db.close()
|
|
|
|
async def get_fisc_config(self, guild_id: int) -> Optional[dict]:
|
|
db = await get_db()
|
|
try:
|
|
cursor = await db.execute(
|
|
"""SELECT fc.*, i.name as institution_name, i.role_id as institution_role_id
|
|
FROM fisc_config fc
|
|
JOIN institutions i ON fc.institution_id = i.id
|
|
WHERE fc.guild_id = ?""",
|
|
(guild_id,),
|
|
)
|
|
row = await cursor.fetchone()
|
|
return dict(row) if row else None
|
|
finally:
|
|
await db.close()
|
|
|
|
async def get_next_bank_codes(self, guild_id: int) -> list:
|
|
db = await get_db()
|
|
try:
|
|
cursor = await db.execute(
|
|
"SELECT establishment_code FROM banks WHERE guild_id = ? ORDER BY establishment_code",
|
|
(guild_id,),
|
|
)
|
|
rows = await cursor.fetchall()
|
|
return [r[0] for r in rows]
|
|
finally:
|
|
await db.close()
|
|
|
|
async def _get_account(self, db: aiosqlite.Connection, guild_id: int, rib: str) -> Optional[dict]:
|
|
cursor = await db.execute(
|
|
"SELECT * FROM accounts WHERE rib = ? AND guild_id = ?", (rib, guild_id)
|
|
)
|
|
row = await cursor.fetchone()
|
|
return dict(row) if row else None
|
|
|
|
async def _get_fisc_rib(self, db: aiosqlite.Connection, guild_id: int) -> Optional[str]:
|
|
cursor = await db.execute(
|
|
"""SELECT a.rib FROM accounts a
|
|
JOIN fisc_config fc ON a.user_id = fc.institution_id
|
|
WHERE fc.guild_id = ? AND a.account_type = 'entreprise'""",
|
|
(guild_id,),
|
|
)
|
|
row = await cursor.fetchone()
|
|
return row[0] if row else None
|
|
|
|
async def _get_gouvernement_bank(self, db: aiosqlite.Connection, guild_id: int) -> Optional[dict]:
|
|
cursor = await db.execute(
|
|
"SELECT * FROM banks WHERE guild_id = ? AND name = 'Gouvernement'",
|
|
(guild_id,),
|
|
)
|
|
row = await cursor.fetchone()
|
|
return dict(row) if row else None
|
|
|
|
async def set_negative_floor(self, guild_id: int, bank_id: int, floor: float) -> bool:
|
|
db = await get_db()
|
|
try:
|
|
cursor = await db.execute(
|
|
"UPDATE banks SET client_negative_floor = ? WHERE id = ? AND guild_id = ?",
|
|
(floor, bank_id, guild_id),
|
|
)
|
|
await db.commit()
|
|
return cursor.rowcount > 0
|
|
finally:
|
|
await db.close()
|
|
|
|
async def set_vault_balance(self, guild_id: int, bank_id: int, montant: float) -> bool:
|
|
db = await get_db()
|
|
try:
|
|
cursor = await db.execute(
|
|
"UPDATE banks SET vault_balance = ? WHERE id = ? AND guild_id = ?",
|
|
(montant, bank_id, guild_id),
|
|
)
|
|
await db.commit()
|
|
return cursor.rowcount > 0
|
|
finally:
|
|
await db.close()
|