#! /usr/bin/env python
# -*- coding: UTF-8 -*-
#
# TabellioOOo - Data cache file generation
# Copyright (C) 2010  Parlement de la Communauté française de Belgique
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA


import sys
import os
import psycopg2
from optparse import OptionParser

try:
    import xml.etree.ElementTree as ET
except ImportError:
    import elementtree.ElementTree as ET


parser = OptionParser()
parser.add_option('--host', dest='host',
                  help='Database hostname', metavar='HOST',
                  default='db.tabellio.pcf.be')
parser.add_option('--dbname', dest='dbname',
                  help='Database name', metavar='DBNAME',
                  default='procedure_tabellio_pcf_be')
parser.add_option('-u', '--user', dest='user',
                  help='Database username', metavar='USERNAME',
                  default='procedure')
parser.add_option('-p', '--password', dest='password',
                  help='Database password', metavar='PASSWORD')
parser.add_option('--dir', dest='directory',
                  help='Directory to hold cache files', metavar='DIR',
                  default='.')
(options, args) = parser.parse_args()

if not options.password:
    print 'Error: you must supply a password.'
    print ''
    parser.print_help()
    sys.exit(1)

conn = psycopg2.connect("host=%s dbname=%s user=%s password=%s" % (
	options.host, options.dbname, options.user, options.password))

cur = conn.cursor()

# Parls
cur.execute("SELECT t_pers.id, t_pers.titre, t_pers.prenom, t_pers.nom, t_pers.sexe, t_comppol.abbr "\
            "FROM t_comppol, t_pershistoline, t_pers "\
            "WHERE "\
               "t_pershistoline.type = 'P_CMPL' AND "\
               "t_pershistoline.fin IS NULL AND "\
               "t_pers.id = t_pershistoline.pers AND "\
               "t_comppol.id = t_pershistoline.description")

parls = ET.Element('SParlSpeaker-list')
while True:
    t = cur.fetchone()
    if not t:
        break
    parl = ET.SubElement(parls, 'SParlSpeaker')
    ET.SubElement(parl, 'id').text = t[0]
    ET.SubElement(parl, 'classname').text = 'PARL'
    ET.SubElement(parl, 'name').text = t[3]
    ET.SubElement(parl, 'firstname').text = t[2]
    ET.SubElement(parl, 'title').text = t[1]
    ET.SubElement(parl, 'sexe').text = t[4]
    ET.SubElement(parl, 'comppol').text = t[5]

fd = file(os.path.join(options.directory, 'Parls.xml'), 'w')
ET.ElementTree(parls).write(fd)

# Commissions
cur.execute("SELECT nom FROM t_com WHERE st = 'S_ACTIVE'")
coms = ET.Element('MCOMSInfo-list')
while True:
    t = cur.fetchone()
    if not t:
        break
    com = ET.SubElement(coms, 'MCOMSInfo')
    ET.SubElement(com, 'nom').text = t[0]

fd = file(os.path.join(options.directory, 'Commissions.xml'), 'w')
ET.ElementTree(coms).write(fd)

# Ministres
cur.execute("SELECT t_pers.id, t_pers.titre, t_pers.prenom, t_pers.nom, t_pers.sexe, t_pershistoline.description "\
              "FROM t_pers, t_ministre, t_pershistoline "\
             "WHERE "\
               "t_pers.id = t_ministre.id AND "\
               "t_pershistoline.pers = t_pers.id AND "\
               "t_pershistoline.type = 'M_FONC' AND "\
               "t_pershistoline.fin IS NULL "\
             "ORDER BY t_ministre.ordre")
mins = ET.Element('SMinistreSpeaker-list')
while True:
    t = cur.fetchone()
    if not t:
        break
    min = ET.SubElement(mins, 'SMinistreSpeaker')
    ET.SubElement(min, 'id').text = t[0]
    ET.SubElement(min, 'classname').text = 'MINISTRE'
    ET.SubElement(min, 'name').text = t[3]
    ET.SubElement(min, 'firstname').text = t[2]
    ET.SubElement(min, 'title').text = t[1]
    ET.SubElement(min, 'sexe').text = t[4]
    ET.SubElement(min, 'fonc').text = t[5]

fd = file(os.path.join(options.directory, 'Ministres.xml'), 'w')
ET.ElementTree(mins).write(fd)

# PresComs
cur.execute("SELECT t_pers.id, t_pers.titre, t_pers.prenom, t_pers.nom, t_pers.sexe, t_com.code "\
              "FROM t_pershistoline, t_com, t_pers "\
             "WHERE "\
                "t_pershistoline.fin IS NULL AND "\
                "t_pershistoline.type = 'P_COMM_PR' AND "\
                "t_pershistoline.description = t_com.id AND "\
                "t_pers.id = t_pershistoline.pers")
prescoms = ET.Element('SPresComSpeaker-list')
while True:
    t = cur.fetchone()
    if not t:
        break
    prescom = ET.SubElement(prescoms, 'SPresComSpeaker')
    ET.SubElement(prescom, 'id').text = t[0]
    ET.SubElement(prescom, 'name').text = t[3]
    ET.SubElement(prescom, 'firstname').text = t[2]
    ET.SubElement(prescom, 'title').text = t[1]
    ET.SubElement(prescom, 'sexe').text = t[4]
    ET.SubElement(prescom, 'com_code').text = t[5]

fd = file(os.path.join(options.directory, 'PresComs.xml'), 'w')
ET.ElementTree(prescoms).write(fd)

cur.close()
conn.close()