''' ''' 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()