#!/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):
  cur.execute("""SELECT COUNT(*)
FROM llx_contratdet d 
JOIN llx_product p on d.fk_product = p.rowid 
WHERE d.date_ouverture IS NOT null
  AND d.date_cloture IS null
  AND p.ref LIKE %s""", (product,))
  return cur.fetchone()[0]

subscribersADSL = getSubscriberCount('ADSL-%')
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))