# -*- coding: utf-8 -*
# BasesX(i, 0)   numéro
# BasesX(i, 1)   nombre de citations dans les 5 premiers de chaque classement rubriques
# BasesX(i, 2)   place à  l'arrivée
# BasesX(i, 3)   cumul des valeurs dégressives de 5 à 1 points pour les 5 premiers des classements rubriques
# BasesX(i, 4)   classement ClaCX
# BasesX(i, 5)   classement ClaCRIFX
# BasesX(i, 6)   classement IndForme
# BasesX(i, 7)   classement ClaTMatic
# BasesX(i, 8)   classement ClaStatsCombi
# BasesX(i, 9)   classement ClasRD
# BasesX(i, 10)  classement ClasRE
# BasesX(i, 11)  classement ClaCote
# BasesX(i, 12)  classement ClasCoefReussite
# BasesX(i, 13)  classement ClaAR
# BasesX(i, 14)  classement ClaOR
# BasesX(i, 15)  classement ClaRub
# BasesX(i, 16)  classement ClaHisto
# BasesX(i, 17)  classement ClasStats
# BasesX(i, 18)  classement BasesX(i, 3) cumul des valeurs dégressives de 5 à 1 points pour les 5 premiers des classements rubriques
# BasesX(i, 19)  QuarteD, CouplePlace12, CouplePlace13, CouplePlace23 colonnes 0 à 3
import sqlite3
import numpy as np
from time import gmtime, strftime
from operator import itemgetter
from multiprocessing import Process
# connexion BDD
conn = sqlite3.connect("Vincennes.sqlite")
conn.row_factory = sqlite3.Row
# Initialisation du tableau de X courses XBasesX constitué de tableaux de courses BasesX
XBasesX = []
# Tableau des noms des 14 ou 16 rubriques étudiées
# LibRub = ["ClaCX","ClaIDC","ClaCRIFX","IndForme","ClaTMatic","ClaStatsCombi","ClasRD","ClasRE","ClaCote","ClaCFP","ClasCoefReussite","ClaAR","ClaOR","ClaRub","ClaHisto","ClasStats"]
LibRub = ["ClaCX","ClaCRIFX","IndForme","ClaTMatic","ClaStatsCombi","ClasRD","ClasRE","ClaCote","ClasCoefReussite","ClaAR","ClaOR","ClaRub","ClaHisto","ClasStats"]
SvNbCoursesGains = 0
SvMontantGains = 0
# Rubriques classiques
def AMHippo(NumGeny,NumCourse,Partants,Distance,LibRub,Rub):
    global BasesX
    global conn
    global LieuCourse
    global Discipline
    # TabChevaux = [[0 for i in range(2)] for j in range(20)]
    TabChevaux = [[0] * 2 for _ in range(20)]
    cur = conn.cursor()
    cur.execute("SELECT Numero, PlaceArrivee FROM Chevaux WHERE NumGeny='" + NumGeny + "' and NumCourse=" + str(NumCourse) + " ORDER BY " + LibRub)
    i = 1
    for row in cur.fetchall():
        TabChevaux[i][0] = row[0]
        TabChevaux[i][1] = row[1]
        i += 1
    if Discipline == "ATTELE" or Discipline == "MONTE":
        if Distance < 2701:
            if Distance < 2401:
                DistanceMin = "1500"
                DistanceMax = "2400"
            else:
                DistanceMin = "2401"
                DistanceMax = "2700"
        else:
            DistanceMin = "2701"
            DistanceMax = "4500"
    elif Discipline == "PLAT":
        if Distance < 1801:
            if Distance < 1501:
                DistanceMin = "500"
                DistanceMax = "1500"
            else:
                DistanceMin = "1501"
                DistanceMax = "1800"
        else:
            DistanceMin = "1801"
            DistanceMax = "4500"
    else:
        if Distance < 7500:
            if Distance < 5001:
                if Distance < 3501:
                    DistanceMin = "2500"
                    DistanceMax = "3500"
                else:
                    DistanceMin = "3501"
                    DistanceMax = "5000"
            else:
                DistanceMin = "5001"
                DistanceMax = "7500"
    if Partants > 8 and Partants < 13:
        PartantsMin = "9"
        PartantsMax = "12"
    elif Partants > 12 and Partants < 17:
        PartantsMin = "13"
        PartantsMax = "16"
    else:
        PartantsMin = "17"
        PartantsMax = "20"
    cur = conn.cursor()
    cur.execute("SELECT Classement, PrctFreqZ FROM StatsRubHippo WHERE LieuCourse='" + LieuCourse + "' and Discipline='" + Discipline + "' and Rubrique='" + LibRub + "' and DistanceMin=" \
                + DistanceMin + " and DistanceMax=" + DistanceMax + " and PartantsMin=" + PartantsMin + " and PartantsMax=" + PartantsMax + " ORDER BY PrctFreqZ desc, Classement")
    Clas = 1
    for row in cur.fetchall():
        if row[0] <= Partants:
            for j in range(0,Partants):
                if BasesX[j][0] == TabChevaux[row[0]][0]:
                    BasesX[j][1] += 1
                    BasesX[j][2] = TabChevaux[row[0]][1]
                    BasesX[j][Rub + 4] = Clas
            Clas += 1
# Rubriques combinées
def SHHippo(DateReunion,NumReunion,NumCourse):
    global BasesX
    global conn
    cur = conn.cursor()
    cur.execute("SELECT Numero, PlaceArrivee, FrequenceReussite FROM DatesChevaux WHERE DateReunion='" + DateReunion + "' and NumReunion=" + str(NumReunion) + " and NumCourse=" + str(NumCourse) + " ORDER BY ClaFrqReussite")
    for row in cur.fetchall():
        for j in range(0,20):
            if BasesX[j][0] == row["Numero"] :
                BasesX[j][1] += 1
                BasesX[j][2] = row["PlaceArrivee"]
                BasesX[j][8] += (19 - (j * 2))

# Enregistrement du résultat de l'analyse financière dans la base de données
def EnregistreParam(CoefRub,EcartMoyen,EcartMaxi,NbCourses,NbCoursesGains,MontantJeux,MontantGains,Z4Calcules):
    global conn
    global LieuCourse
    global Discipline
    global DateDebPer
    global DateFinPer
    #LibCoef = ["CoefRubCX","CoefRubCRI","CoefRubIDC","CoefRubFOR","CoefRubTGE","CoefRubSTR","CoefRubDRI","CoefRubENT","CoefRubCM","CoefRubCFP","CoefRubREU","CoefRubAR","CoefRubOR","CoefRubRUB","CoefRubHIS","CoefRubST"]
    LibRub = ["RubCX","RubCRI","RubFOR","RubTGE","RubSTR","RubDRI","RubENT","RubCM","RubREU","RubAR","RubOR","RubRUB","RubHIS","RubST"]
    LibCoef = ["CoefRubCX","CoefRubCRI","CoefRubFOR","CoefRubTGE","CoefRubSTR","CoefRubDRI","CoefRubENT","CoefRubCM","CoefRubREU","CoefRubAR","CoefRubOR","CoefRubRUB","CoefRubHIS","CoefRubST"]
    # Chargement des bilans positifs en fonction des valeurs paramètrées
    cur = conn.cursor()
    cur.execute("SELECT LieuCourse, Discipline, Jeu, NbCoursesGains, MontantGains FROM Parametres WHERE Jeu='nb'")
    row = cur.fetchone()
    if NbCoursesGains > row["NbCoursesGains"]: 
        Bilan = "UPDATE Parametres set "
        for i in range(0,14):
            if CoefRub[i] == 0:
                Bilan = Bilan + LibRub[i] + "=0, " + LibCoef[i] + "=" + str(CoefRub[i]) + ","
            else:
                Bilan = Bilan + LibRub[i] + "=1, " + LibCoef[i] + "=" + str(CoefRub[i]) + ","
        Bilan = Bilan + " EcartMoyen=" + str(EcartMoyen) + ", EcartMaxi=" + str(EcartMaxi) + ", Difficulte=100, NbCourses=" + str(NbCourses) + ", NbCoursesGains=" + str(NbCoursesGains)
        Bilan = Bilan + ", MontantJeux="+ str(MontantJeux) + ", MontantGains=" + str(MontantGains) + ", Z4Calcules=" + str(Z4Calcules) + ", DateDebPer='" + DateDebPer + "', DateFinPer='" + DateFinPer + "'"
        Bilan = Bilan + " WHERE LieuCourse='" + LieuCourse + "' and Discipline='" + Discipline + "' and Jeu='nb'"
        cur.execute(Bilan)
        conn.commit()
    cur.execute("SELECT LieuCourse, Discipline, Jeu, NbCoursesGains, MontantGains FROM Parametres WHERE Jeu='mt'")
    row = cur.fetchone()
    if MontantGains > row["MontantGains"]: 
        Bilan = "UPDATE Parametres set "
        for i in range(0,14):
            if CoefRub[i] == 0:
                Bilan = Bilan + LibRub[i] + "=0, " + LibCoef[i] + "=" + str(CoefRub[i]) + ","
            else:
                Bilan = Bilan + LibRub[i] + "=1, " + LibCoef[i] + "=" + str(CoefRub[i]) + ","
        Bilan = Bilan + " EcartMoyen=" + str(EcartMoyen) + ", EcartMaxi=" + str(EcartMaxi) + ", Difficulte=100, NbCourses=" + str(NbCourses) + ", NbCoursesGains=" + str(NbCoursesGains)
        Bilan = Bilan + ", MontantJeux="+ str(MontantJeux) + ", MontantGains=" + str(MontantGains) + ", Z4Calcules=" + str(Z4Calcules) + ", DateDebPer='" + DateDebPer + "', DateFinPer='" + DateFinPer + "'"
        Bilan = Bilan + " WHERE LieuCourse='" + LieuCourse + "' and Discipline='" + Discipline + "' and Jeu='mt'"
        cur.execute(Bilan)
        conn.commit()
    cur.close
# Fonction de traitement d'un cycle complet sur le cube
def CycleToutesCourses(CoefRub):
    global XnpX
    #global XBasesX
    global NbCycles
    global LieuCourse
    global Discipline
    global SvNbCoursesGains
    global SvMontantGains
    # Initialisation des zones de calcul pour le bilan financier
    NbCoursesGains = 0
    MontantGains = 0
    MontantJeux = 0
    CptEcartMoyen = 0
    CptEcartMaxi = 0
    Z4Calcules = 0
    EcartMoyen = 0
    EcartMaxi = 0
    Ecart = 0
    # on boucle sur les courses charges dans XBasesX
    #for BasesX in XBasesX:
    for npX in XnpX:
        # Calcul des points cumulés pour chaque cheval de la course lignes 0 à 19
	#for Bases in BasesX:
        for Bnp in npX:
            # Cumul des 14 rubriques colonnes 3 à 17
            for Rub in range(0,14):
		#Bases[3] += (CoefRub[Rub] * ((1 / Bases[Rub + 4]) * 10))
                Bnp[3] += (CoefRub[Rub] * ((1 / Bnp[Rub + 4]) * 10))
        # Tri préalable sur colonne 7 classement ClaTMatic et sur colonne 3 Cumul des valeurs dégressives des classements de chaque rubriques
	#BasesX.sort(key=lambda x : (-x[3],x[7]))
        npX = npX[npX[:,7].argsort(kind='mergesort')]
        npX = npX[npX[:,3].argsort(kind='mergesort')]
        # Bilan Z4 combiné 8 chevaux 70€
        MontantJeux += 70
        Z4 = 0
        for i in range(0,9):
	    #if 0<BasesX[i][2]<5:
            if 0<npX[i,2]<5:
                Z4 += 1
        if Z4 == 4:
            NbCoursesGains += 1
	    #GZ4 = BasesX[0][19]
            GZ4 = npX[0,19]
            if GZ4 > 0:
                MontantGains += GZ4
            else:
		# 80 € par défaut si incalculable
                MontantGains += 80
                Z4Calcules += 1
            if CptEcartMaxi > EcartMaxi:
                EcartMaxi = CptEcartMaxi
            EcartMoyen = EcartMoyen + CptEcartMoyen
            if CptEcartMoyen != 0:
                Ecart += 1
            CptEcartMoyen = 0
            CptEcartMaxi = 0
        else:
            CptEcartMoyen += 1
            CptEcartMaxi += 1
	# on sort du cycle sous les conditions suivantes =>
	# Au bout 10 courses analysées, si moins de 2 courses ont été pronostiquées
	# Au bout 20 courses analysées, si moins de 5 courses ont été pronostiquées 
	# Au bout 50 courses analysées, si moins de 11 courses ont été pronostiquées 
	# Au bout 100 courses analysées, si moins de 21 courses ont été pronostiquées 
	# Si l'écart maximum est supérieur à 10
        if (MontantJeux > 630 and NbCoursesGains < 2) or (MontantJeux > 1330 and NbCoursesGains < 5) or (MontantJeux > 3430 and NbCoursesGains < 11) or (MontantJeux > 6930 and NbCoursesGains < 21) or EcartMaxi > 10:
            break
    # Enregistrement du bilan financier dans Parametres
    # Si le montant des gains ou le nombre de courses gagnées s'est amélioré
    # deb = time()-t0	    
    if NbCoursesGains > SvNbCoursesGains or MontantGains > SvMontantGains:
        if EcartMoyen > 0:
            EcartMoyen = round(EcartMoyen / Ecart, 2)
        EnregistreParam(CoefRub,EcartMoyen,EcartMaxi,NbCourses,NbCoursesGains,MontantJeux,MontantGains,Z4Calcules)
        print(NbCycles," - ",strftime("%d-%m %H:%M:%S",gmtime())," - ",CoefRub," - NbCG=",NbCoursesGains," - Gains=",MontantGains," - Jeux=",MontantJeux," - Z4C=",Z4Calcules)
        if NbCoursesGains > SvNbCoursesGains:
            SvNbCoursesGains = NbCoursesGains
        if MontantGains > SvMontantGains:
            SvMontantGains = MontantGains
    else:
        if NbCycles % 5000 == 0:
            print(NbCycles," - ",strftime("%d-%m %H:%M:%S",gmtime())," - ",CoefRub," - NbCG=",NbCoursesGains," - Gains=",MontantGains," - Jeux=",MontantJeux," - Z4C=",Z4Calcules)
#
# Création du Cube :
#
#   Dimension 1 : les courses de l'hippodrome
#   Dimension 2 : les chevaux de la course
#   Dimension 3 : les rubriques des chevaux
#
# Chargement des données Reunions, Courses et rubriques pour 1 Hippodrome et 1 Discipline
# Par exemple pour Vincennes en attelé 566 courses pendant le meeting du 1er octobre 2017 au 31 mars 2018 :
# On divise l'année en 2, meeting (1er octobre au 31 mars) et hors meeting (1er avril au 30 septembre)
# on charges les x courses dans le tableau XBasesX qui servira de base pour l'étude financières
# Dans le cas de Vincennes, 
curR = conn.cursor()
curR.execute("SELECT NumGeny, LieuCourse, DateReunion, NumReunion FROM Reunions ORDER BY substr(DateReunion, 7, 4)||substr(DateReunion, 4,2)||substr(DateReunion, 1,2) desc")
NbCourses = 0
for rowR in curR.fetchall():
    if NbCourses == 0:
        DateDebPer = rowR["DateReunion"]
        LieuCourse = rowR["LieuCourse"]
    # Chargement des données courses
    curC = conn.cursor()
    curC.execute("SELECT NumGeny, NumCourse, Discipline, Partants, Distance, Premier, Deuxieme, Troisieme, Quatrieme, Cinquieme, round(CouplePlace12), round(CouplePlace23), \
    round(CouplePlace13), round(Trio), round(QuarteD) FROM Courses WHERE Partants>=10 and NumGeny='" + rowR["NumGeny"] + "' and Discipline='ATTELE' ORDER BY NumCourse")
    for rowC in curC.fetchall():
        NbCourses = NbCourses + 1
        Discipline = rowC["Discipline"]
        # Initialisation du tableau 1 course BasesX de 19 colonnes et 20 lignes 
        BasesX = [[0] * 20 for _ in range(20)]
        # chargement du tableau 1 course avec les numéro des chevaux de 1 à  20
        for i in range(0,20):
            BasesX[i][0] = i + 1
            # On initialise les classements rubriques à 99 pour les tris croissants
            for j in range(4,18):
                BasesX[i][j] = 99
        # Boucle d'appels à la fonction de chargement des valeurs des 16 rubriques statistiques dans BasesX
        for Rub in range(0,14):
            if Rub==4:
                # Rubriques combinées
                SHHippo(rowR["DateReunion"],rowR["NumReunion"],rowC["NumCourse"]) 
            else:
                # Rubriques classiques
                AMHippo(rowR["NumGeny"],rowC["NumCourse"],rowC["Partants"],rowC["Distance"],LibRub[Rub],Rub) 
        BasesX[0][19] = int(rowC["round(QuarteD)"])
        XBasesX.append(BasesX)
    curC.close()
    DateFinPer = rowR["DateReunion"]
curR.close()
# Boucles de calcul de l'analyse financière
SvNbCoursesGains = 0
SvMontantGains = 0
NbCycles = 0
print(NbCycles,"     - ",strftime("%d-%m %H:%M:%S",gmtime()))
# On transforme le cube en array numpy
XnpX = np.array(XBasesX,int)
# variation de 0 à 3 des coefficients des 14 rubriques
for CoefRub in np.ndindex((3,)*14):
    NbCycles += 1
    CycleToutesCourses(CoefRub)