scripts-pde/agrhum_traitement.py

'''

'''

from core.pde import AgrhumDb , FraisDeplacement, HeureSupp
import logging
import sys
import json

from datetime import datetime
from path import Path  # @UnusedImport
from core import logconf
from core.model import Sql

#logger = logging.getLogger("ctrl2analytique")
#logconf.start("ctrl2analytique", logging.DEBUG)

# # POUR TESTER, d�commenter les lignes suivantes
# > Lancer le script /resources/test_ctrl2analytique.py pour reinitialiser les donn�es de la base de test
##-----------------------------------------------

AgrhumDb._path = Path(r"\\h2o\local\4-transversal\BDD\mdb_test\BDD_ParcRH.mdb")
#CommunDb._path = Path(r"\\h2o\local\4-transversal\BDD\mdb_test\Commun_Data.mdb")
#logger.handlers = [h for h in logger.handlers if (type(h) == logging.StreamHandler)]
#logger.warning("<<<<<<<<<<<<<<   Mode TEST   >>>>>>>>>>>>>>>>>")

##-----------------------------------------------

print ("La recherche du python de l'amazonie commence ici, pas encore l'anaconda :")

agrhum_db = AgrhumDb(autocommit=False)

CodeAgent = "T9"
MoisRH = 12 
AnneeRH = 2017

#Verification dans la table tbl_suiviRH suivi si la ligne agent mois annnée existe
#Verfification si etat != de Importé ou si valide = True : demande confirmation pour retraitement
#Si retraitement on suprime des tables cibles tbl_formHS et tbl_formDep
#Parcours de tbl_importRH transfo donnée et écriture dans tbl_formdep
#Parcours de tbl_importRH transfo donnée et écriture dans tbl_formHS

def main():
    suivi = agrhum_db.first("select * from tbl_suiviRH where codeagent ='{}' and moisrh = {}  and anneerh = {}".format(CodeAgent, MoisRH,AnneeRH))
    if not suivi:
        print("suivi manquant")
        return

    #if input("Voulez vous retarite ces donnees(o/n)") != "o" :
    #    print("Annulation")
    #    return


    agrhum_db.execute("delete  * from tbl_formDep where codeagent ='{}' and moisrh = {}  and anneerh = {}".format(CodeAgent, MoisRH,AnneeRH))
    agrhum_db.execute("delete  * from tbl_formHS where codeagent ='{}' and moisrh = {}  and anneerh = {}".format(CodeAgent, MoisRH,AnneeRH))


    data = agrhum_db.read_all("select * from tbl_importrh where codeagent ='{}' and Month([daterh]) = {}  and Year([daterh]) = {} order by DateRH".format(CodeAgent, MoisRH,AnneeRH))
    sql = Sql.format("""SELECT 
                                            tbl_baremes.NomBareme, 
                                            tbl_baremes.BorneInf, 
                                            tbl_baremes.BorneSup, 
                                            tbl_baremes.Valeur, 
                                            tbl_PeriodeBareme.DateInf, 
                                            tbl_PeriodeBareme.DateSup
                                    FROM tbl_baremes 
                                    INNER JOIN tbl_PeriodeBareme ON (tbl_baremes.PeriodeValidite = tbl_PeriodeBareme.CodePeriode) AND (tbl_baremes.NomBareme = tbl_PeriodeBareme.NomBareme) 
                                    WHERE  tbl_baremes.NomBareme = 'Heures de route' 
                                    AND tbl_PeriodeBareme.DateInf <= {:date} 
                                    AND (tbl_PeriodeBareme.DateSup is null or tbl_PeriodeBareme.DateSup > {:date}) """, datetime(AnneeRH,MoisRH,1), datetime(AnneeRH,MoisRH,1))
    baremes = agrhum_db.read_all(sql)

    index = {} 
    indexHS ={}
    for el in data :
        if not el.DateRH.day in index :
            fraisdep = FraisDeplacement()
            fraisdep.IDSuivi = suivi.IDSuivi
            fraisdep.AnneeRH = AnneeRH
            fraisdep.CodeAgent = CodeAgent
            fraisdep.MoisRH = MoisRH
            fraisdep.JourRH = el.DateRH.day
            fraisdep.Depart = el.Depart 
            # mettre a jour le codedepart
            fraisdep.Itineraire  = " - ".join([loc for loc in (fraisdep.Depart, el.Localisation) if loc])
            fraisdep.Distance1_perso = int(el.DistanceTranche1) if el.VehiculePersoTranche1 == "True" else 0
            fraisdep.Distance2_perso = int(el.DistanceTranche2) if el.VehiculePersoTranche2 == "True" else 0
            fraisdep.Distance2_service = int(el.DistanceTranche2) if not el.VehiculePersoTranche2 == "True" else 0
            fraisdep.Repas = int(el.Repas)
            
            index[el.DateRH.day] = fraisdep

         
        else :
            index[el.DateRH.day].Itineraire += " - " + el.Localisation
            index[el.DateRH.day].Distance1_perso += int(el.DistanceTranche1) if el.VehiculePersoTranche1 == "True" else 0
            index[el.DateRH.day].Distance2_perso += int(el.DistanceTranche2) if el.VehiculePersoTranche2 == "True" else 0
            index[el.DateRH.day].Distance2_service += int(el.DistanceTranche2) if not el.VehiculePersoTranche2 == "True" else 0
            index[el.DateRH.day].Repas += int(el.Repas)


    for fraisdep in index.values() :
        distance2 = (fraisdep.Distance2_perso + fraisdep.Distance2_service)
        fraisdep.HeuresDep = next((bareme.Valeur for bareme in baremes if bareme.BorneInf <= distance2 and bareme.BorneSup > distance2))
        fraisdep.HeuresDepNuit = next((bareme.Valeur for bareme in baremes if bareme.BorneInf <= fraisdep.Distance1_perso and bareme.BorneSup > fraisdep.Distance1_perso))

     
          

        sqlexec = Sql.format("""INSERT INTO tbl_FormDep ( IDSuivi, 
                                                          CodeAgent, 
                                                          JourRH, 
                                                          MoisRH, 
                                                          AnneeRH, 
                                                          Depart, 
                                                          Itineraire, 
                                                          Distance2_perso, 
                                                          Distance2_service, 
                                                          Distance1_perso, 
                                                          HeuresDep, 
                                                          HeuresDepNuit, 
                                                          Repas, 
                                                          Remarque, 
                                                          Valide, 
                                                          CreePar, 
                                                          CreeLe )
                                                  VALUES ({fraisdep.IDSuivi}, 
                                                          {fraisdep.CodeAgent:text}, 
                                                          {fraisdep.JourRH}, 
                                                          {fraisdep.MoisRH}, 
                                                          {fraisdep.AnneeRH}, 
                                                          {fraisdep.Depart:text}, 
                                                          {fraisdep.Itineraire:text}, 
                                                          {fraisdep.Distance2_perso}, 
                                                          {fraisdep.Distance2_service}, 
                                                          {fraisdep.Distance1_perso}, 
                                                          {fraisdep.HeuresDep}, 
                                                          {fraisdep.HeuresDepNuit}, 
                                                          {fraisdep.Repas}, 
                                                          {fraisdep.Remarque:text}, 
                                                          {fraisdep.Valide}, 
                                                          {fraisdep.CreePar:text}, 
                                                          {fraisdep.CreeLe:date})""",fraisdep = fraisdep)
        
      
        
        agrhum_db.execute(sqlexec)

    indexHS ={}
    
    for el in data :
        if not el.DateRH.day in indexHS :
            hs = HeureSupp()
            hs.IDSuivi = suivi.IDSuivi
            hs.AnneeRH = AnneeRH
            hs.CodeAgent = CodeAgent
            hs.MoisRH = MoisRH
            hs.JourRH = el.DateRH.day
            hs.HeureSup1 = el.HeureSup1
            hs.HeuresDep = index[el.DateRH.day].HeuresDep
            hs.HeuresDepNuit = index[el.DateRH.day].HeuresDepNuit
            hs.HeureSupNuit = float( el.HeureSup2) + hs.HeuresDepNuit
            hs.HeureSupDim = el.HeureSupDimanche
            hs.HS_VHCanal = float(el.HeureSup1) + float(el.HeureSup2) + float(el.HeureSupDimanche) if el.strCategorieInterventionId  == "07" else 0.0
            hs.HS_Chantier = float(el.HeureSup1) + float(el.HeureSup2) + float(el.HeureSupDimanche) if el.strCategorieInterventionId  != "07" else 0.0

            indexHS[el.DateRH.day] = hs
        else :
            indexHS[el.DateRH.day].HeureSup1 += float( el.HeureSup1)
            indexHS[el.DateRH.day].HeureSupNuit += float( el.HeureSup2)
            indexHS[el.DateRH.day].HeureSupDim += float( el.HeureSupDimanche)
            indexHS[el.DateRH.day].HS_VHCanal += float(el.HeureSup1) + float(el.HeureSup2) + float(el.HeureSupDimanche) if el.strCategorieInterventionId  == "07" else 0.0
            indexHS[el.DateRH.day].HS_Chantier += float(el.HeureSup1) + float(el.HeureSup2) + float(el.HeureSupDimanche) if el.strCategorieInterventionId  != "07" else 0.0
   
    totalhs = 0.0
    totalhsmois = 0.0
           
    for hs in indexHS.values() :
        totalhs = hs.HeureSup1 + hs.HeureSupNuit + hs.HeureSupDim
        if totalhs + totalhsmois <= 14 :
            hs.HeureSup1Inf14 = totalhs
        elif totalhsmois >14 :
            hs.HeureSup1Sup14 = totalhs
        elif  totalhs + totalhsmois > 14 :         
            hs.HeureSup1Inf14 = 14.0 - totalhsmois
            hs.HeureSup1Sup14 = totalhs + totalhsmois -14.0
        
        totalhsmois += totalhs
                
        sqlexechs = Sql.format("""INSERT INTO tbl_FormHS ( IDSuivi, CodeAgent, JourRH, MoisRH, AnneeRH, HeureSup1, HeuresDep, HeuresDepNuit, [HeureSup1<=14], [HeureSup1>14], HeureSupNuit, HeureSupDim, HS_VHCanal, HS_Chantier, Valide, CreePar, CreeLe )
VALUES ({hs.IDSuivi}, {hs.CodeAgent:text}, {hs.JourRH}, {hs.MoisRH}, {hs.AnneeRH}, {hs.HeureSup1}, {hs.HeuresDep}, {hs.HeuresDepNuit}, {hs.HeureSup1Inf14}, {hs.HeureSup1Sup14}, {hs.HeureSupNuit}, {hs.HeureSupDim}, {hs.HS_VHCanal}, {hs.HS_Chantier}, {hs.Valide}, {hs.CreePar:text}, {hs.CreeLe:date})
""", hs = hs)
       
        agrhum_db.execute(sqlexechs)

    with open(Path(r"%temp%\test.json").expandvars(), "w+") as f:
        f.write(str(index))

    agrhum_db.commit()



if __name__ == "__main__":
    main()