# -*- coding: utf8 -*-
from sqlalchemy import text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (
    scoped_session,
    sessionmaker,
)

from zope.sqlalchemy import ZopeTransactionExtension, mark_changed

from datetime import *
import transaction

from .default import (
    execute_query,
)

def to_int(x):
    try:
        number = int(x.replace(',', '.'))
        return number
    except ValueError:
        return 0

def get_examens_byDate(request, datedeb, permis):
    # lire les examens à partir de datedeb
    query = "SELECT * FROM `examens` WHERE date >= :datedeb AND permis = :permis ORDER BY date, heure;"
    results = request.dbsession.execute(query, {'datedeb': datedeb, 'permis': permis}).fetchall()
    return results

def get_examens_aff(request, date, no_exa):
    # lire les examens à partir de datedeb
    query = """
SELECT a.*, e.NOM_JF, e.filiere, e.TR_T_OK, e.TEL, e.TEL2, e.TEL3, e.TEL4 
FROM examens_aff a INNER JOIN eleves e ON a.cd_cli = e.cd_cli
WHERE a.date = :date  AND a.no_exa= :no_exa;"""
    results = request.dbsession.execute(query, {'date': date, 'no_exa': no_exa}).fetchall()
    return results

def get_examens(request, date, no_exa):
    # lire l'examen
    query = "SELECT * FROM examens WHERE date = :date  AND no_exa= :no_exa;"
    results = request.dbsession.execute(query, {'date': date, 'no_exa': no_exa}).first()
    return results


def get_examens_aff_color(request, row):
    # lire les inscrits provisoires 80
    query = "SELECT * FROM examens_aff WHERE date = :date  AND no_exa= :no_exa AND agence= :agence AND cd_cli = 80;"
    results = request.dbsession.execute(query, {'date': row.DATE, 'no_exa': row.NO_EXA, 'agence': row.AGENCE}).first()
    if results:
        return 'Gold'
    # lire les inscrits provisoires 
    query = "SELECT * FROM examens_aff WHERE date = :date  AND no_exa= :no_exa AND agence= :agence AND cd_cli < 100000;"
    results = request.dbsession.execute(query, {'date': row.DATE, 'no_exa': row.NO_EXA, 'agence': row.AGENCE}).first()
    if results:
        return 'Tomato'
    
    # déterminer la couleur de l'event 
    if row.VALIDE == 1:
        color = 'LightGreen'  # planning validé = vert pale 
    elif row.INSCRITS > 0 :
        color = "LightYellow"  # disponible
    else:
        color = "LightBlue"  # complet 
        
    return color

def get_eleves_by_neph(request, neph):
    # lire l'eleve par son no d'immatricualtion
    query = """SELECT * FROM eleves WHERE dossier_no=:neph AND statut < 10;"""
    results = request.dbsession.execute(query, {'neph': neph}).first()
    return results

def get_next_cepc(request, cd_cli, dateexa):    
    # lire le dernier CEPC uploadé
    query = "SELECT * FROM eleves_upload WHERE cd_cli=:cd_cli AND code LIKE 'CEPC%' ORDER BY code DESC;"
    results = request.dbsession.execute(query, {'cd_cli': cd_cli}).first()
    if results:
        if results.nom_fic.find(dateexa) > 0:
            # cet examen est déjà mis à jour
            code = ""
        else:
            # incrementer le code (dernier chiffre)
            code = 'CEPC' + str(int(results.code[-1]) + 1)
    else:
        code = 'CEPC1'
    return code

def insert_eleve_cepc(request, cd_cli, next_code, filename, filesize, logged_in):
    
    query = """INSERT INTO eleves_upload (cd_cli, code, type, nom_fic, taille_fic, cd_uti) VALUES
    (:cd_cli, :next_code, 'DOC', :filename, :filesize, :logged_in)"""
    execute_query(request, query, {'cd_cli': cd_cli, 'next_code': next_code, 'filename': filename, 'filesize': filesize, 'logged_in': logged_in})

def update_resultat(request, cd_cli, date_exa, resultat_exa, logged_in):
    # maj le statut de l'examen de l'élève
    query ="""UPDATE examens_aff SET 
        resultat = :resultat_exa,
        reussite = (SELECT COALESCE(reussite,2) FROM P_STATUTS_EXAM WHERE code=:resultat_exa),
        cd_uti = :logged_in
        WHERE cd_cli = :cd_cli AND date = :date_exa; """
    execute_query(request, query, {'cd_cli': cd_cli, 'resultat_exa': resultat_exa, 'date_exa': date_exa, 'logged_in': logged_in})
    
def get_examens_maj(request, logged_in):
    # lire les examens mis à jour aujourd'hui
    query = """SELECT a.*, s.libelle 
FROM examens_aff a INNER JOIN p_statuts_exam s ON a.resultat = s.code
WHERE a.cd_uti = :logged_in  AND date(a.modif_le) = CURRENT_DATE();"""
    results = request.dbsession.execute(query, {'logged_in': logged_in}).fetchall()
    return results