fcn-toolbox/fcn-ispdb

#!/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))