#!/usr/bin/python3 import configparser import json import psycopg2 config = configparser.RawConfigParser() config.read('/etc/fcntoolbox/config.ini') configdb = config['dolibarr'] conn = psycopg2.connect(database=configdb['database'], user=configdb['user'], password=configdb['password']) cur = conn.cursor() def getMemberCount(): cur.execute("SELECT count(*) FROM llx_adherent WHERE statut = '1'") return cur.fetchone()[0] def getSubscriberCount(product): # statut: 4 = STATUS_OPEN (cf. dolibarr/htdocs/contrat/class/contrat.class.php cur.execute("""SELECT COUNT(*) FROM llx_contratdet d JOIN llx_product p on d.fk_product = p.rowid WHERE d.statut = 4 AND p.ref LIKE %s""", (product,)) return cur.fetchone()[0] # L'ancien format de service xDSL avec Nerim était ADSL-XX-X. # À supprimer quand il n'y en aura plus. subscribersADSL = getSubscriberCount('ADSL-%') + getSubscriberCount('DSL-%') subscibersVPN = getSubscriberCount('VPN-%') members = getMemberCount() f = open('/etc/fcntoolbox/ispdb-template.json', 'r') isp = json.load(f) f = open('/etc/fcntoolbox/ispdb.geojson', 'r') ispgeo = json.load(f) isp['coveredAreas'][0]['area'] = ispgeo isp["memberCount"] = members isp["subscriberCount"] = subscribersADSL + subscibersVPN isp["_DSL_subscriberCount"] = subscribersADSL isp["_VPN_subscriberCount"] = subscibersVPN print(json.dumps(isp, indent=True))