source: trunk/aeres/scripts/aeresrh.py @ 195

#!/usr/bin/env python
# -*- coding: iso-8859-1 -*-

__docformat__ = 'reStructuredText'
__revision__ = "$Id$"
__autodoc__ = ['aeresrh']


"""

==========
aeresrh.py
==========

DESCRIPTION
===========

extract information from aeresrh.xls

.. option:: --ifile <file>

      file to read

.. only:: man

    Figure is visible on PDF and HTML documents only.

    .. only:: html or latex

       .. graphviz::

          digraph aeresrh {
                 rh [shape=diamond,
                     fontname=Courier,
                     label='{ifile}'

                 aeresrh [shape=box,
                     fontname=Courier,
                     color=blue,
                     URL="http://forge.ipsl.jussieu.fr/pulsation/browser/trunk/aeres/scripts/aeresrh.py",
                     label="${PROJECT}/aeres/scripts/aeresrh.py"];
             }

SEE ALSO
========

.. index:: ! Excel

https://secure.simplistix.co.uk/svn/xlrd/trunk/xlrd/doc/xlrd.html
http://www.simplistix.co.uk/presentations/python-excel.pdf


EXAMPLES
========

::

 aeresrh.py --ifile ${PROJECT}/data/aeresrh.xls

 aeresrh.py  --ifile ${HOME}/Downloads/personnel_franc\314\247oise.xls

TODO
====

pb de type de fichier  ~/Downloads/personnel\ franc\314\247oise.xlsx

ne marche pas avec des chemins relatifs

append sur ifiles car liste permanent et liste thesard

comment j'ai la liste des gens partis au 30 juin 2012 avec 
publication (retraite, decès, mutation)

test with real file (encoding, francais, etc)

missing values

coding rules (pylint)

EVOLUTIONS
==========

$Id$

$URL$

- fplod 20120426

  * big process in dictionnary using itertools

- fplod 20120410

  * real titles
  * start dictionnary

- fplod 20120409

  * no more hard coded column number

- fplod 20120406

  * tst avec copie de vrai fichier 
   (fplod@halios:Downloads/personnel\ franc\314\247oise.xlsx) dans toto.xsl
- fplod 20120405
 
  * gestion des temps (arrivée, départ) (to be cont.)
  * creation draft
    thanks to http://www.portailsig.org/content/python-lire-et-ecrire-des-fichiers-microsoft-excel-application-quantum-gis
    and http://scienceoss.com/read-excel-files-from-python/

"""

import sys
import os
from os.path import dirname, basename
import glob
from optparse import OptionParser

import datetime

import itertools

try:
    import xlrd
except ImportError:
    print("Failed to import xlrd from any known place")
    sys.exit(1)

from build_surname_id import build_surname_id
from build_firstname_id import build_firstname_id

def get_option_parser ():
    """parse CLI arguments

           :returns: parser
          :rtype: :class:`optparse.OptionParser`
    """

    parser = OptionParser('%prog [--verbose ] [--ifile file')
    parser.add_option ('-V', '--verbose',
        help='produce verbose output',
        action='store_true',
        default=False,
        dest='is_verbose')
    parser.add_option ('-i', '--ifile',
        help='file to be read',
        type='string',
        dest='ifile',
        default=None,
        action='store')

    return parser

def find_in_dict(d, criterion, lookup):
    """
    >>> people = {'pk1':{'firstname':'Brian', 'age':42}, 'pk2':{'firstname':'Alex', 'age':50}}
    >>> result = find_in_dict(people, 'age', 42)

    http://stackoverflow.com/questions/8987631/searching-2-dimensional-dictionary
    """

    result = []
    for key, val in d.iteritems():
        if criterion in val and val[criterion] == lookup:
            result.append((key,val))
    return result

def readrh(ifile, is_verbose):

    """
    read ifile

    return surname, firstname, arrival,  publi
    """

    if is_verbose == True:
        print('iii : read %s' % ifile)

    # ouverture du fichier Excel
    wb = xlrd.open_workbook(ifile, formatting_info=True)

    # feuilles dans le classeur
    if is_verbose == True:
        print('sheets name %s' % ( wb.sheet_names()))

    # lecture des données dans la première feuille
    sh = wb.sheet_by_index(0)

    #
    if is_verbose == True:
        for rownum in range(sh.nrows):
            print('values of in row %d : %s' % (rownum, sh.row_values(rownum)))

    if is_verbose == True:
        for colnum in range(sh.ncols):
            print('values of in col %d : %s' % (colnum, sh.col_values(colnum)))

    # detect title row
    for rownum in range(sh.nrows):
        if sh.row_values(rownum)[0] == "Type d'emploi (EC, Ch, AP)":
            titlerow = rownum 
    if is_verbose == True:
        print('titlerow : %d ' % (titlerow))

    # deduce first usefull row
    firstrow = titlerow + 1
    #
    # detect last usefull row
    uselessrow = []
    for rownum in range(titlerow, sh.nrows):
        if sh.row_values(rownum)[0] == '': 
            uselessrow.append(rownum)
    lastrow = uselessrow[0]
    if is_verbose == True:
        print('lastrow : %d ' % (lastrow))

    # detect surname column
    for colnum in range(sh.ncols):
        if sh.col_values(colnum)[titlerow] == 'Nom':
            colsurname = colnum
    if is_verbose == True:
        print('colsurname: %d ' % (colsurname))

    # detect firstname column
    for colnum in range(sh.ncols):
        if sh.col_values(colnum)[titlerow] == u'Prénom':
            colfirstname = colnum
    if is_verbose == True:
        print('colfirstname: %d ' % (colfirstname))

    # detect arrival column
    for colnum in range(sh.ncols):
        if sh.col_values(colnum)[titlerow] ==  u"Date d'arriv\xe9e dans l'unit\xe9\n(9)":
            colarrival = colnum
    if is_verbose == True:
        print('colarrival: %d ' % (colarrival))

    # detect publi column
    for colnum in range(sh.ncols):
        if sh.col_values(colnum)[titlerow] ==  u'N\xb0 des 5 productions les plus significatives dans la p\xe9riode \xe9valu\xe9e\n(11)':
            colpubli = colnum
    if is_verbose == True:
        print('colpubli: %d ' % (colpubli))

    datemode = 0 

    surname = sh.col_values(colsurname)[firstrow:lastrow]

    if is_verbose == True:
        print(' nb surname %d' % len(surname))
        print(' surname %s' % surname)

    firstname = sh.col_values(colfirstname)[firstrow:lastrow]

    if is_verbose == True:
        print(' nb firstname %d' % len(firstname))
        print(' firstname %s' % firstname)

    temp = sh.col_values(colarrival)[firstrow:lastrow]
    arrival = []
    for rownum in range(len(temp)):
        if is_verbose == True:
            print(' temp arrival AAA%sAAA' % temp[rownum])
        if temp[rownum] == '':
            print('eee : pas de date arrivee colonne %d ' % rownum)
        else:
            arrival.append(datetime.datetime(*xlrd.xldate_as_tuple(temp[rownum], datemode)))

    if is_verbose == True:
        print(' nb arrival %d' % len(arrival))
        print(' arrival %s' % arrival)

    publi = sh.col_values(colpubli)[firstrow:lastrow]

    if is_verbose == True:
        print(' nb publi %d' % len(publi))
        print(' publi %s' % publi)

    return surname, firstname, arrival, publi

def aeresrh():
    """main
    """
    try:
        parser = get_option_parser ()
        (fromcli, args) = parser.parse_args()
    except IOError, msg:
        parser.error(str(msg))

    is_verbose = fromcli.is_verbose
    ifile = fromcli.ifile
    if fromcli.is_verbose == True:
        print ('ifile : %s' % (ifile))

    surname, firstname, arrival, publi = readrh(ifile, is_verbose)
    #
    # build surname id
    surname_id = build_surname_id(surname)
    firstname_id = build_firstname_id(firstname)
    #
    # populate dictionary
    keys_for_all = []
    values_for_all = []
    keys_for_one = ['surname', 'firstname', 'arrival', 'publi']
    for index in range(len(surname_id)):
        values_for_one = []
        values_for_one.append(surname[index])
        values_for_one.append(firstname[index])
        values_for_one.append(arrival[index])
        values_for_one.append(publi[index])
        values_for_all.append(dict(itertools.izip(keys_for_one, values_for_one)))
        keys_for_all.append('{surname_id}_{firstname_id}'.format(surname_id=surname_id[index], firstname_id=firstname_id[index]))


    if is_verbose == True:
        print ('len keys_for_all : %d ' % len(keys_for_all))
        print ('person_id 0 : %s ' % keys_for_all[0])
        for index, key_for_all in enumerate(keys_for_all):
            print ('person_id (key) : %s ' % key_for_all)
            print ('associated info : %s ' % values_for_all[index])

    people = dict(itertools.izip(keys_for_all, values_for_all))

    if is_verbose == True:
        print (' result looking for key PINSARD_francoise : %s ' % people['PINSARD_francoise'])

    if is_verbose == True:
        result = find_in_dict(people, 'surname', 'PINSARD')
        print (' result looking for PINSARD %s ' % result)

# Run main, if called from the command line
if __name__ == '__main__':
    aeresrh()