//  
//  gripdbs.cs
//  
//  Author:
//       Robert BRACCAGNI alias Gai-Luron <lfsgailuron@free.fr>
// 
//  Copyright (c) 2010 Gai-Luron
// 
//  This program is free software: you can redistribute it and/or modify
//  it under the terms of the GNU General Public License as published by
//  the Free Software Foundation, either version 3 of the License, or
//  (at your option) any later version.
// 
//  This program is distributed in the hope that it will be useful,
//  but WITHOUT ANY WARRANTY; without even the implied warranty of
//  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//  GNU General Public License for more details.
// 
//  You should have received a copy of the GNU General Public License
//  along with this program.  If not, see <http://www.gnu.org/licenses/>.

using System;
using System.Text;
using System.Data;
using System.Collections;
#if MONOSQLITE
    using Mono.Data.SqliteClient;
#else
    using System.Data.SQLite;
#endif
using System.Threading;
using LapperThreads;
using LFSLapper;


namespace LFSDbs
{
/*
    static public class listOfGripDbs
    {
        static Hashtable LODB = new Hashtable();
        public static gripDbs getDbCon( GLDebug.Debug pmyDebug, string puniqueConnectionId, string DbName, int LapTimeUsedForPbPar, string FtpServer, string FtpLogin, string FtpPasswd, string FtpRemotePath, string dateFormat){
            string fullPath = System.IO.Path.GetFullPath(DbName);
            if (!LODB.Contains(fullPath))
                LODB[fullPath] = new gripDbs(pmyDebug, puniqueConnectionId, DbName, LapTimeUsedForPbPar, FtpServer, FtpLogin, FtpPasswd, FtpRemotePath, dateFormat);
            return (gripDbs)LODB[fullPath];
        }
    }
*/
    public class gripDbs 
    {
        private class rowEpb
        {
            public long idEpb;
            public string strVal;
            public rowEpb(long pidEpb, string str)
            {
                this.idEpb = pidEpb;
                this.strVal = str;
            }
        }

        private static Mutex mut = new Mutex();
        public DbsAccess dbCon;
        private int LapTimeUsedForPb = 1;
        private string uniqueConnectionId = "";
        LapperThreads.ftpDbsUpload threadDbsUpload;
        Thread threadUpload = null;
        private GLDebug.Debug myDebug;

        public gripDbs( GLDebug.Debug pmyDebug, string puniqueConnectionId, string DbName, int LapTimeUsedForPbPar, string FtpServer, string FtpLogin, string FtpPasswd, string FtpRemotePath, string dateFormat)
        {
            bool newBase = false;

            string sql;
            this.LapTimeUsedForPb = LapTimeUsedForPbPar;
            this.uniqueConnectionId = puniqueConnectionId;
            this.myDebug = pmyDebug;

            dbCon = new DbsAccess( myDebug, DbName);
            IDataReader retQuery;

                if (!dbCon.isExistTable("fi_epb"))
                {
                    newBase = true;
                    sql = "CREATE TABLE fi_epb ( "
                                            + " idEpb INTEGER PRIMARY KEY  NOT NULL DEFAULT ''"
                                            + ",userName CHAR( 20 )"
                                            + ",nickName CHAR( 20 )"
                                            + ",nickNameStripped CHAR( 20 )"
                                            + ",carName CHAR( 4 )"
                                            + ",trackName CHAR( 4 )"
                                            + ",laps INTEGER"
                                            + ",date CHAR( 10 )"
                                            + ",time CHAR( 10 )"
                                            + ",pbLapTime INTEGER"
                                            + ",pbLapTimeSplit1 INTEGER"
                                            + ",pbLapTimeSplit2 INTEGER"
                                            + ",pbLapTimeSplit3 INTEGER"
                                            + ",pbSectorSplit1 INTEGER"
                                            + ",pbSectorSplit2 INTEGER"
                                            + ",pbSectorSplit3 INTEGER"
                                            + ",pbSectorSplitLast INTEGER"

                                            + ")";
                    dbCon.executeNonQuery(sql);

                    sql = "CREATE UNIQUE INDEX i_fi_epb1 ON fi_epb( userName,carName,trackName )";
                    dbCon.executeNonQuery(sql);
                }
                if (!dbCon.isExistTable("fi_lpb"))
                {

                    sql = "CREATE TABLE fi_lpb ( "
                                            + " idEpb INTEGER "
                                            + ",date CHAR( 10 )"
                                            + ",time CHAR( 10 )"
                                            + ",lapTime INTEGER"
                                            + ",split1 INTEGER"
                                            + ",split2 INTEGER"
                                            + ",split3 INTEGER"
                                            + ")";
                    dbCon.executeNonQuery(sql);

                    sql = "CREATE INDEX i_fi_lpb1 ON fi_lpb( idEpb )";
                    dbCon.executeNonQuery(sql);

                }
                if (!dbCon.isExistTable("fi_param"))
                {

                    sql = "CREATE TABLE fi_param ( "
                            + " cle CHAR( 30 ) "
                            + ", vartxt CHAR( 30 ) "
                            + ",varint INTEGER"
                            + ")";
                    dbCon.executeNonQuery(sql);

                    sql = "CREATE INDEX i_fi_param1 ON fi_param( cle )";
                    dbCon.executeNonQuery(sql);


                }
                if( !dbCon.isExistIndex( "i_fi_epb2" )){
                    sql = "CREATE INDEX i_fi_epb2 ON fi_epb( trackName,carName )";
                    dbCon.executeNonQuery(sql);

                }

                if (!dbCon.isExistColum( "fi_epb","nickNameStripped"))
                {
                        sql = "ALTER TABLE fi_epb ADD nickNameStripped CHAR(20)";
                        dbCon.executeNonQuery(sql);
                        dbCon.executeNonQuery("BEGIN TRANSACTION");
                        sql = "SELECT * FROM fi_epb";
                        retQuery = dbCon.executeQuery(sql);
                        while (retQuery.Read())
                        {
                            long idEpb = retQuery.GetInt64(retQuery.GetOrdinal("idEpb"));
                            string nickNameStripped = UTILS.utils.stripLFSColor(retQuery.GetString(retQuery.GetOrdinal("nickName")));
                            string sql2 = "UPDATE fi_epb SET nickNameStripped = '" + nickNameStripped.Replace("'", "''").Replace("\0", "") + "'"
                                            + " WHERE idEpb = " + idEpb;
                            dbCon.executeNonQuery(sql2);
                        }
                        retQuery.Close();
                        retQuery.Dispose();
                        dbCon.executeNonQuery("COMMIT TRANSACTION");

                }
                string myTable = "fi_" + uniqueConnectionId;
                if (!dbCon.isExistTable(myTable))
                {

                    sql = "CREATE TABLE " + myTable + " ( "
                                            + "userName CHAR( 20 )"
                                            + ",CarName CHAR( 3 )"
                                            + ")";
                    dbCon.executeNonQuery(sql);

                    sql = "CREATE INDEX i_" + myTable + " ON " + myTable + "( userName,carName )";
                    dbCon.executeNonQuery(sql);

                }

                try
                {
                    if( newBase )
                        ImportLapperPB(DbName + ".txt");
                }
                catch { }



/*            
                                Console.WriteLine("Insertion");
                                dbCon.executeNonQuery("BEGIN TRANSACTION");
                                for (int i = 0; i < 100000; i++)
                                {
                                    dbCon.executeNonQuery("DELETE FROM fi_epb WHERE username = 'bob'");
                                    dbCon.executeNonQuery("INSERT INTO fi_epb ("
                                    + "userName"
                                    + ",nickName"
                                    + ",carName"
                                    + ",trackName"
                                    + ",laps"
                                    + ",pbSectorSplit1"
                                    + ",pbSectorSplit2"
                                    + ",pbSectorSplit3"
                                    + ",pbSectorSplitLast"
                                    + ") VALUES ("
                                    + "'" + "bob" + "'"
                                    + ",'" + "bob" + "'"
                                    + ",'" + "MYCA" + "'"
                                    + ",'" + "MYTR" + "'"
                                    + "," + "12"
                                    + "," + "3000"
                                    + "," + "3000"
                                    + "," + "3000"
                                    + "," + "3000"
                                    + ")");
//                                System.Threading.Thread.Sleep(1000);
                                }
                                dbCon.executeNonQuery("COMMIT TRANSACTION");
                                Console.WriteLine("Fin Insertion");
 
*/
            sql = "SELECT varint FROM fi_param WHERE "
                + "cle = 'LapTimeUsedForPb'";
            retQuery = dbCon.executeQuery( sql );
            int OldLapTimeUsedForPb = -1;
            if( retQuery.Read() ){
                OldLapTimeUsedForPb = (int) retQuery.GetInt64(retQuery.GetOrdinal("varint"));
            }
            retQuery.Close();
            retQuery.Dispose();
//Only for Test
//            majDbsPb();
            if (LapTimeUsedForPb != OldLapTimeUsedForPb)
            {
                majDbsPb();
                sql = "DELETE FROM fi_param WHERE "
                    + "cle = 'LapTimeUsedForPb'";
                dbCon.executeNonQuery(sql);
                sql = "INSERT INTO fi_param ( cle, varint ) VALUES ( 'LapTimeUsedForPb', " + LapTimeUsedForPb + ")";
                dbCon.executeNonQuery(sql);
            }
            this.threadDbsUpload = new ftpDbsUpload(myDebug, DbName, FtpServer, FtpLogin, FtpPasswd, FtpRemotePath, dateFormat, LapTimeUsedForPb);
            threadUpload = new Thread(new ThreadStart(threadDbsUpload.SendGripDbsToFtp));
            threadUpload.Start();

            
        }
        public void killThreads()
        {
            if (threadUpload != null)
            {
                threadUpload.Abort();
                threadDbsUpload.dbCon.dbCon.Close();
                threadDbsUpload.dbCon.dbCon.Dispose();
            }
            dbCon.dbCon.Close();
            dbCon.dbCon.Dispose();
        }
        public bool isThreadsAlive()
        {
            if (threadUpload != null)
                return threadUpload.IsAlive;
            return false;
        }
        private void majDbsPb()
        {
            long idEpb;
            string sqlStr;
            long sumPbLapTime = 0;
            long sumSpliTime1 = 0;
            long sumSpliTime2 = 0;
            long sumSpliTime3 = 0;
            string datePb = "0000/00/00";
            string timePb = "00:00";
            int nbEnreg = 0;
            int div = 0;
            long updPbLapTime;
            long updPbLapTimeSplit1;
            long updPbLapTimeSplit2;
            long updPbLapTimeSplit3;

            myDebug.WriteLine("mss", "New $LapTimeUsedForPb, update DBS ... " );
            dbCon.executeNonQuery("BEGIN TRANSACTION");
            string sql = "SELECT idEpb FROM fi_epb ";
            IDataReader retQuery = dbCon.executeQuery( sql );
            while(retQuery.Read())
            {
                idEpb = retQuery.GetInt64(retQuery.GetOrdinal("idEpb"));
                sumPbLapTime = 0;
                sumSpliTime1 = 0;
                sumSpliTime2 = 0;
                sumSpliTime3 = 0;
                datePb = "0000/00/00";
                timePb = "00:00";
                sqlStr = "SELECT * FROM fi_lpb WHERE idEpb = " + idEpb + " ORDER BY LapTime ASC,date ASC,time ASC";
                IDataReader retQuery2 = dbCon.executeQuery(sqlStr);
                nbEnreg = 0;
                div = 0;
                while (retQuery2.Read())
                {
                    nbEnreg++;
                    sumPbLapTime += retQuery2.GetInt64( retQuery2.GetOrdinal("lapTime" ));
                    sumSpliTime1 += retQuery2.GetInt64(retQuery2.GetOrdinal("split1"));
                    sumSpliTime2 += retQuery2.GetInt64(retQuery2.GetOrdinal("split2"));
                    sumSpliTime3 += retQuery2.GetInt64(retQuery2.GetOrdinal("split3"));
                    string dateTime1 = retQuery2.GetString(retQuery2.GetOrdinal("date")) + " " + retQuery2.GetString(retQuery2.GetOrdinal("time"));
                    string dateTime2 = datePb + " " + timePb;
                    if( dateTime1.CompareTo( dateTime2 ) > 0 ){
                        datePb = retQuery2.GetString(retQuery2.GetOrdinal("date"));
                        timePb = retQuery2.GetString(retQuery2.GetOrdinal("time"));
                    }
                    if (nbEnreg >= this.LapTimeUsedForPb)
                    {
                        div = this.LapTimeUsedForPb;
                        break;
                    }

                }
                retQuery2.Close();
                retQuery2.Dispose();
                if( div != 0 ){
                    updPbLapTime = sumPbLapTime / div;
                    updPbLapTimeSplit1 = sumSpliTime1 / div;
                    updPbLapTimeSplit2 = sumSpliTime2 / div;
                    updPbLapTimeSplit3 = sumSpliTime3 / div;
                }
                else{
                    updPbLapTime = 3600000 - (nbEnreg*10);
                    updPbLapTimeSplit1 = 0;
                    updPbLapTimeSplit2 = 0;
                    updPbLapTimeSplit3 = 0;
                }
                sqlStr = "UPDATE fi_epb SET pbLapTime = " + updPbLapTime
                            + ",pbLapTimeSplit1 = " + updPbLapTimeSplit1
                            + ",pbLapTimeSplit2 = " + updPbLapTimeSplit2
                            + ",pbLapTimeSplit3 = " + updPbLapTimeSplit3
                            + ",date = '" + datePb + "'"
                            + ",time = '" + timePb + "'"
                        + " WHERE idEpb = " + idEpb;
                dbCon.executeNonQuery(sqlStr);
  
            }
            retQuery.Close();
            retQuery.Dispose();
            dbCon.executeNonQuery("COMMIT TRANSACTION");

        }
        public void updateRow(bool immediateUpload, LFSLapper.infoPlayer currInfoPlayer, string trackName)
        {
            updateRow(immediateUpload, currInfoPlayer.userName,
                    currInfoPlayer.nickName,
                    currInfoPlayer.gripDriverLapInfo.datePb,
                    currInfoPlayer.gripDriverLapInfo.timePb,
                    currInfoPlayer.CName,
                    trackName,
                    currInfoPlayer.gripDriverLapInfo
                    
            );

        }
        public void lockBase()
        {
            mut.WaitOne();
        }
        public void unlockBase()
        {
            mut.ReleaseMutex();
        }
        public void updateRow(bool immediateUpload, string userName, string nickName, string datePb, string timePb, string carName, string trackName, DriverLapEntry driverLapInfo)
        {
            lockBase();
			try
			{
				updateRow2(immediateUpload, userName, nickName, datePb, timePb, carName, trackName, driverLapInfo);
			}
			finally
			{
				unlockBase();
			}
        }
        public void updateRow2(bool immediateUpload, string userName, string nickName, string datePb, string timePb, string carName, string trackName, DriverLapEntry driverLapInfo )
        {
            long idEpb;
            userName = userName.ToLower();
            dbCon.executeNonQuery("BEGIN TRANSACTION");
            string sql = "SELECT idEpb FROM fi_epb WHERE "
                            + "userName = '" + userName.Replace("'", "''") + "'"
                            + " AND carName = '" + carName + "'"
                            + " AND trackName = '" + trackName + "'";
            IDataReader retQuery = dbCon.executeQuery( sql );
            if( retQuery.Read() ){
                idEpb = retQuery.GetInt64( 0 );
                sql = "UPDATE fi_epb SET "
                        + " nickName = '" + nickName.Replace("'", "''").Replace("\0", "") + "'"
                        + " ,nickNameStripped = '" + UTILS.utils.stripLFSColor( nickName.Replace("'", "''").Replace("\0", "")) + "'"
                        + " ,laps = " + driverLapInfo.laps
                        + ",date ='" + driverLapInfo.datePb + "'"
                        + ",time = '" + driverLapInfo.timePb + "'"
                        + ",pbLapTime = " + driverLapInfo.personalBestLapTime
                        + ",pbLapTimeSplit1 = " + driverLapInfo.splitTime[0]
                        + ",pbLapTimeSplit2 = " + driverLapInfo.splitTime[1]
                        + ",pbLapTimeSplit3 = " + driverLapInfo.splitTime[2]
                        + " ,pbSectorSplit1 = " + driverLapInfo.PBBestSplitDiff[0]
                        + " ,pbSectorSplit2 = " + driverLapInfo.PBBestSplitDiff[1]
                        + " ,pbSectorSplit3 = " + driverLapInfo.PBBestSplitDiff[2]
                        + " ,pbSectorSplitLast = " + driverLapInfo.PBBestSplitDiffLast
                        + " WHERE idEpb = " + idEpb
                ;
                dbCon.executeNonQuery(sql);
                dbCon.executeNonQuery("DELETE FROM fi_lpb WHERE idEpb = " + idEpb);
            }
            else{
                if (driverLapInfo.listPB.Count > 0)
                {
                    dbCon.executeNonQuery("INSERT INTO fi_epb ("
                                            + "userName"
                                            + ",nickName"
                                            + ",nickNameStripped"
                                            + ",carName"
                                            + ",trackName"
                                            + ",laps"
                                            + ",date"
                                            + ",time"
                                            + ",pbLapTime"
                                            + ",pbLapTimeSplit1"
                                            + ",pbLapTimeSplit2"
                                            + ",pbLapTimeSplit3"
                                            + ",pbSectorSplit1"
                                            + ",pbSectorSplit2"
                                            + ",pbSectorSplit3"
                                            + ",pbSectorSplitLast"
                                        + ") VALUES ("
                                            + "'" + userName.ToLower().Replace("'", "''") + "'"
                                            + ",'" + nickName.Replace("'", "''").Replace("\0", "") + "'"
                                            + ",'" + UTILS.utils.stripLFSColor(nickName.Replace("'", "''").Replace("\0", "")) + "'"
                                            + ",'" + carName + "'"
                                            + ",'" + trackName + "'"
                                            + "," + driverLapInfo.laps
                                            + ",'" + driverLapInfo.datePb + "'"
                                            + ",'" + driverLapInfo.timePb + "'"
                                            + "," + driverLapInfo.personalBestLapTime
                                            +"," + driverLapInfo.splitTime[0]
                                            +"," + driverLapInfo.splitTime[1]
                                            +"," + driverLapInfo.splitTime[2]
                                            +"," + driverLapInfo.PBBestSplitDiff[0]
                                            + "," + driverLapInfo.PBBestSplitDiff[1]
                                            + "," + driverLapInfo.PBBestSplitDiff[2]
                                            + "," + driverLapInfo.PBBestSplitDiffLast
                                        + ")");
                    uint lastRowId = dbCon.getLastRowId("fi_epb");
                    IDataReader retQuery2 = dbCon.executeQuery("SELECT idEpb FROM fi_epb WHERE rowid = " + lastRowId);
                    retQuery2.Read();
                    idEpb = retQuery2.GetInt64(0);
                    retQuery2.Close();
                    retQuery2.Dispose();
                }
                else
                {
                    dbCon.executeNonQuery("COMMIT TRANSACTION");
                    return;
                }
            }
            retQuery.Close();
            retQuery.Dispose();
            for (int i = 0; i < driverLapInfo.listPB.Count; i++)
            {
                sql = "INSERT INTO fi_lpb ("
                                + "idEpb"
                                + ",date"
                                + ",time"
                                + ",lapTime"
                                + ",split1"
                                + ",split2"
                                + ",split3"
                            + ") VALUES ("
                                + idEpb
                                + ",'" + (driverLapInfo.listPB[i] as PbLapEntry).datePb + "'"
                                + ",'" + (driverLapInfo.listPB[i] as PbLapEntry).timePb + "'"
                                + "," + (driverLapInfo.listPB[i] as PbLapEntry).personalBestLapTime
                                + "," + (driverLapInfo.listPB[i] as PbLapEntry).splitTime[0]
                                + "," + (driverLapInfo.listPB[i] as PbLapEntry).splitTime[1]
                                + "," + (driverLapInfo.listPB[i] as PbLapEntry).splitTime[2]
                            + ")";
                dbCon.executeNonQuery(sql);
            }
            dbCon.executeNonQuery("COMMIT TRANSACTION");

            if (immediateUpload)
                threadDbsUpload.immediateUpload();
            else
                threadDbsUpload.delayedUpload();


        }

        public void deletetrackrecords(string UserName, String Car, String TrackName)
        {
            try
            {
                string sql = "";
            dbCon.executeNonQuery("BEGIN TRANSACTION");
            if ((UserName == "-")&&(Car == "-"))
            {
                sql = "DELETE FROM fi_epb WHERE trackName = '" + TrackName + "'";
            }
            else if ((UserName == "-") && (Car != "-"))
            {
                sql = "DELETE FROM fi_epb WHERE trackName = '" + TrackName + "'" + " AND carName = '" + Car.ToUpper() + "'";
            }
            else if ((UserName != "-") && (Car == "-"))
            {
                sql = "DELETE FROM fi_epb WHERE trackName = '" + TrackName + "'" + " AND userName = + '" + UserName.ToLower().Replace("'", "''") + "'";
            }
            else if ((UserName != "-") && (Car != "-"))
            {
                sql = "DELETE FROM fi_epb WHERE trackName = '" + TrackName + "'" + " AND carName = '" + Car.ToUpper() + "'" + " AND userName = '" + UserName.ToLower().Replace("'", "''") + "'";
            }
            dbCon.executeNonQuery(sql);
            dbCon.executeNonQuery("COMMIT TRANSACTION");
            }
            catch
            {
                throw new GLScript.GLApp.GLScriptException("Error: with deleting laptime records from database");
            }
        }


        public void FORCED_UPLOAD()
        {
            threadDbsUpload.immediateUpload();
        }
        public void retreiveRow(infoPlayer currInfoPlayer, string trackName)
        {
            dbCon.executeNonQuery("BEGIN TRANSACTION");
            currInfoPlayer.gripDriverLapInfo = new DriverLapEntry( dbCon,currInfoPlayer.userName,currInfoPlayer.nickName,currInfoPlayer.CName,trackName, LapTimeUsedForPb );
            currInfoPlayer.TName = trackName;
            dbCon.executeNonQuery("COMMIT TRANSACTION");

            currInfoPlayer.sessLaps = 0;

        }
        public void addPlayerFilter( string userName, string carName )
        {
            string myTable = "fi_" + uniqueConnectionId;
            string sql = "INSERT INTO " + myTable + "("
                        + "userName"
                        + ",carName"
                        + ") VALUES ("
                        + "'" + userName.ToLower().Replace("'", "''") + "'"
                        + ",'" + carName + "'"
                        + ")";

            dbCon.executeNonQuery(sql);
        }
        public void clearPlayerFilter()
        {
            string sql = "";
            string myTable = "fi_" + uniqueConnectionId;

            dbCon.executeNonQuery("BEGIN TRANSACTION");
            sql = "DELETE FROM " + myTable;
            dbCon.executeNonQuery(sql);
            dbCon.executeNonQuery("COMMIT TRANSACTION");

        }
        public System.Collections.ArrayList MkGroup(int MaxGroupQual, int MaxUserGroupQual, int MinUserGroupQual, int NbRacer)
        {

            System.Collections.ArrayList group = new System.Collections.ArrayList();

            int NbGroup = NbRacer / MaxUserGroupQual;
            int reste = NbRacer - (NbGroup * MaxUserGroupQual);
            //        Console.WriteLine("NbGroup:" + NbGroup + " Reste:" + reste);
            if (NbGroup >= MaxGroupQual)
            {
                NbGroup = MaxGroupQual - 1;
                reste = MaxUserGroupQual;
            }
            // Mettre le nombre de pilote pour chaque poule
            int i;
            for (i = 0; i < NbGroup; i++)
                group.Add(MaxUserGroupQual);
            if (reste != 0)
                group.Add(reste);

            // Equilibration des poules
            if ((reste < MinUserGroupQual) && (NbGroup > 0) && (reste != 0))
            {
                //            Console.WriteLine(group[i]);
                group[i] = MinUserGroupQual;
                int diff = MinUserGroupQual - reste;
                while (true)
                {
                    for (int j = NbGroup - 1; j >= 0; j--)
                    {
                        group[j] = (int)group[j] - 1;
                        diff--;
                        if (diff == 0)
                        {
                            return group;
                        }
                    }
                }
            }
            else
                return group;

        }
        public long retreivePB(string userName, string nickName, string carName, string trackName)
        {
            dbCon.executeNonQuery("BEGIN TRANSACTION");
            DriverLapEntry retValue = new DriverLapEntry(dbCon, userName, nickName, carName, trackName, LapTimeUsedForPb);
            dbCon.executeNonQuery("COMMIT TRANSACTION");
            if (retValue == null || retValue.personalBestLapTime > 3500000 )
                return -1;
            else
                return retValue.personalBestLapTime;
        }

        public class PbLapEntry : System.IComparable
        {
            public string datePb;
            public string timePb;
            public long personalBestLapTime;
            public long[] splitTime = new long[(int)paramLapper.maxSplit];
            public PbLapEntry(long PB, long split1, long split2, long split3, string dPB, string tPB)
            {
                this.personalBestLapTime = PB;
                this.splitTime[0] = split1;
                this.splitTime[1] = split2;
                this.splitTime[2] = split3;
                this.datePb = dPB;
                this.timePb = tPB;

            }

            public int CompareTo(object x)
            {
                if ((x as PbLapEntry).personalBestLapTime < personalBestLapTime)
                    return 1;
                else if ((x as PbLapEntry).personalBestLapTime > personalBestLapTime)
                    return -1;
                else
                {
                    string myDT1 = (x as PbLapEntry).datePb + " " + (x as PbLapEntry).timePb;
                    string myDT2 = datePb + " " + timePb;
                    return myDT1.CompareTo(myDT2);
                }
            }

        }


        public class DriverLapEntry : System.IComparable
        {
            public string userName;
            public string nickName;
            public string carName;
            public string datePb;
            public string timePb;
            public int laps;
            public int pos = -1;
            public int total = -1;
            public int group = -1;
            public long personalBestLapTime = 0;
            public long[] splitTime = new long[(int)paramLapper.maxSplit];
            public long[] PBBestSplitDiff = new long[(int)paramLapper.maxSplit];
            public long PBBestSplitDiffLast;
            public System.Collections.ArrayList listPB = new System.Collections.ArrayList();

            public DriverLapEntry( DbsAccess dbCon, string uN, string nN, string cN, string tN, int LapTimeUsedForPb )
            {
                string sqlStr;

                this.userName = uN;
                this.nickName = nN;
                this.carName = cN;
                this.personalBestLapTime = 0;
                this.laps = 0;
                this.PBBestSplitDiffLast = 0;
                sqlStr = "SELECT * FROM fi_epb"
                    + " WHERE carName = '" + cN + "'"
                    + " AND trackName = '" + tN + "'"
                    + " AND userName = '" + uN.ToLower().Replace("'", "''") + "'"
                ;
                IDataReader retQuery = dbCon.executeQuery(sqlStr);
                if (retQuery.Read())
                {
                    long idEpb = retQuery.GetInt64(retQuery.GetOrdinal("idEpb"));
                    this.laps = (int)retQuery.GetInt64(retQuery.GetOrdinal("laps"));
                    this.datePb = retQuery.GetString(retQuery.GetOrdinal("date"));
                    this.timePb = retQuery.GetString(retQuery.GetOrdinal("time"));
                    this.personalBestLapTime = retQuery.GetInt64(retQuery.GetOrdinal("pbLapTime"));
                    this.splitTime[0] = retQuery.GetInt64(retQuery.GetOrdinal("pbLapTimeSplit1"));
                    this.splitTime[1] = retQuery.GetInt64(retQuery.GetOrdinal("pbLapTimeSplit2"));
                    this.splitTime[2] = retQuery.GetInt64(retQuery.GetOrdinal("pbLapTimeSplit3"));
                    this.PBBestSplitDiff[0] = retQuery.GetInt64(retQuery.GetOrdinal("pbSectorSplit1"));
                    this.PBBestSplitDiff[1] = retQuery.GetInt64(retQuery.GetOrdinal("pbSectorSplit2"));
                    this.PBBestSplitDiff[2] = retQuery.GetInt64(retQuery.GetOrdinal("pbSectorSplit3"));
                    this.PBBestSplitDiffLast = retQuery.GetInt64(retQuery.GetOrdinal("pbSectorSplitLast"));

                    sqlStr = "SELECT * FROM fi_lpb WHERE idEpb = " + idEpb;
                    IDataReader retQuery2 = dbCon.executeQuery(sqlStr);
                    while (retQuery2.Read())
                    {
                        long split1 = retQuery2.GetInt64(retQuery2.GetOrdinal("split1"));
                        long split2 = retQuery2.GetInt64(retQuery2.GetOrdinal("split2"));
                        long split3 = retQuery2.GetInt64(retQuery2.GetOrdinal("split3"));
                        long PB = retQuery2.GetInt64(retQuery2.GetOrdinal("lapTime"));
                        string datePB = retQuery2.GetString(retQuery2.GetOrdinal("date"));
                        string timePB = retQuery2.GetString(retQuery2.GetOrdinal("time"));
                        this.listPB.Add(new PbLapEntry(PB, split1, split2, split3, datePb, timePB));
                    }
                    retQuery2.Close();
                    retQuery2.Dispose();
                }
                else
                {
                    for (int i = 0; i < (int)paramLapper.maxSplit - 1; i++)
                    {
                        this.splitTime[i] = 0;
                        this.PBBestSplitDiff[i] = 0;
                    }

                }
                retQuery.Close();
                retQuery.Dispose();
                majPB( LapTimeUsedForPb );
            }


            public void majPB(int LapTimeUsedForPb)
            {
 // Retreive only the best for now
                listPB.Sort();
                long sumPbLapTime = 0;
                long sumSpliTime1 = 0;
                long sumSpliTime2 = 0;
                long sumSpliTime3 = 0;
                string date = "0000/00/00";
                string time = "00:00";
                int div = 0;
                int div1 = 0;
                int div2 = 0;
                int div3 = 0;
                int nbEnreg = 0;

                for (int i = 0; i < listPB.Count ; i++){
                        nbEnreg++;
                        sumPbLapTime += (this.listPB[i] as PbLapEntry).personalBestLapTime;
                        if( ( this.listPB[i] as PbLapEntry).splitTime[0] != 0 ){
                            sumSpliTime1 += (this.listPB[i] as PbLapEntry).splitTime[0];
                            div1++;
                        }
                        if ((this.listPB[i] as PbLapEntry).splitTime[1] != 0)
                        {
                            sumSpliTime2 += (this.listPB[i] as PbLapEntry).splitTime[1];
                            div2++;
                        }
                        if ((this.listPB[i] as PbLapEntry).splitTime[2] != 0)
                        {
                            sumSpliTime3 += (this.listPB[i] as PbLapEntry).splitTime[2];
                            div3++;
                        }
                        string dateTime1 = (this.listPB[i] as PbLapEntry).datePb + " " + (this.listPB[i] as PbLapEntry).timePb;
                        string dateTime2 = date + " " + time;
                        if( dateTime1.CompareTo( dateTime2 ) > 0 ){
                            this.datePb = (this.listPB[i] as PbLapEntry).datePb;
                            this.timePb = (this.listPB[i] as PbLapEntry).timePb;
                        }
                        if (nbEnreg >= LapTimeUsedForPb)
                        {
                            div = LapTimeUsedForPb;
                            break;
                        }
                    }
                  if( div != 0 ){
                      this.personalBestLapTime = sumPbLapTime / div;
                      if( div1 != 0 )
                        this.splitTime[0] = sumSpliTime1 / div1;
                    if (div2 != 0)
                        this.splitTime[1] = sumSpliTime2 / div2;
                    if (div3 != 0)
                        this.splitTime[2] = sumSpliTime3 / div3;
                  }
                  else{
                    this.personalBestLapTime = 3600000 - (nbEnreg*10);
                    this.splitTime[0] = 0;
                    this.splitTime[1] = 0;
                    this.splitTime[2] = 0;
                 }
  

            }

            public int CompareTo(object x)
            {
                if ((x as DriverLapEntry).personalBestLapTime < personalBestLapTime)
                    return 1;
                else if ((x as DriverLapEntry).personalBestLapTime > personalBestLapTime)
                    return -1;
                else 
                    return 0;
            }

        }

        public System.Collections.ArrayList GetTable2(int from, string relativeToUserName, int nbRead, string trackName, string carName, string Filter, bool onlyQualUser, int MaxGroupQual, int MaxUserGroupQual, int MinUserGroupQual)
        {
            string sqlStr = "";
            string sqlSelect = "";
            string sqlCount = "";
            string sqlWhere = "";
            string sqlOrder = "";
            long cnt = 0;
            IDataReader retQuery;
            IDataReader retQuery2;
            System.Collections.ArrayList list = new System.Collections.ArrayList();
            string filterUserTable = "fi_" + uniqueConnectionId;


            carName = UTILS.utils.replaceGroupCar(carName);
            string[] lcarName = carName.Split('+');
            carName = "'" + carName.Replace("+", "','") + "'";

            relativeToUserName = relativeToUserName.ToLower();


            if (onlyQualUser)
            {
                sqlSelect = "SELECT a.* FROM fi_epb a , " + filterUserTable + " b";
                sqlCount = "SELECT COUNT( * ) cnt FROM fi_epb a , " + filterUserTable + " b";
                sqlWhere = "WHERE a.carName IN ( " + carName + " )"
                        + " AND a.trackName = '" + trackName + "'"
                        + " AND a.userName = b.userName"
                        + " AND ( b.carName = '' OR a.carName = b.carName )";
            }
            else
            {
                sqlSelect = "SELECT a.* FROM fi_epb a";
                sqlCount = "SELECT COUNT(*) cnt FROM fi_epb a";
                sqlWhere = "WHERE carName IN ( " + carName + " )"
                        + " AND trackName = '" + trackName + "'";
            }

            if (Filter != "" && !onlyQualUser )
            {
                sqlWhere = sqlWhere + " AND nickNameStripped LIKE '%" + Filter.Replace("'", "''") + "%'"; 
            }
            cnt = 0;

            if ( relativeToUserName != "" )
            {
                sqlStr = sqlSelect + " " + sqlWhere + " AND a.userName = '" + relativeToUserName.Replace("'", "''") + "'";
                retQuery = dbCon.executeQuery(sqlStr);
                if (retQuery.Read())
                {
                    dbCon.executeNonQuery("BEGIN TRANSACTION");
                    long pbLapTime = retQuery.GetInt64(retQuery.GetOrdinal("pbLapTime"));

                    sqlStr = sqlCount + " " + sqlWhere + " AND a.pbLapTime <= " + pbLapTime;
                    retQuery2 = dbCon.executeQuery(sqlStr);
                    retQuery2.Read();
                    cnt = retQuery2.GetInt32(retQuery2.GetOrdinal("cnt"));
                    retQuery2.Close();
                    retQuery2.Dispose();

                    sqlStr = sqlSelect + " " + sqlWhere + " AND a.pbLapTime = " + pbLapTime + " ORDER BY pbLapTime,date,time";
                    retQuery2 = dbCon.executeQuery(sqlStr);
                    int i = 0;
                    int pos = 0;
                    while (retQuery2.Read())
                    {
                        string userName = retQuery2.GetString(retQuery2.GetOrdinal("userName"));
                        if (userName == relativeToUserName)
                        {
                            pos = i;
                        }
                        i++;
                    }
                    retQuery2.Close();
                    retQuery2.Dispose();

                    from = (int)cnt - nbRead / 2 - (i - pos);
                    if (from < 0)
                        from = 0;
                    dbCon.executeNonQuery("COMMIT TRANSACTION");
                }
                retQuery.Close();
                retQuery.Dispose();
            }


            sqlOrder = "ORDER BY pbLapTime,date,time LIMIT " + nbRead + " OFFSET  " + from;


            cnt = 0;

            sqlStr = sqlCount + " " + sqlWhere;
            retQuery = dbCon.executeQuery(sqlStr);
            retQuery.Read();
            cnt = retQuery.GetInt32(0);
            retQuery.Close();
            retQuery.Dispose();

            sqlStr = sqlSelect + " " + sqlWhere + " " + sqlOrder;

            dbCon.executeNonQuery("BEGIN TRANSACTION");
            retQuery = dbCon.executeQuery(sqlStr);
            int topPos = from;
            while (retQuery.Read() )
            {
                string userName = retQuery.GetString(retQuery.GetOrdinal("userName"));
                string nickName = retQuery.GetString(retQuery.GetOrdinal("nickName"));
                string curr_carName = retQuery.GetString(retQuery.GetOrdinal("carName"));
                int laps = (int)retQuery.GetInt64(retQuery.GetOrdinal("laps"));
                long idEpb = retQuery.GetInt64(retQuery.GetOrdinal("idEpb"));

                list.Add(new DriverLapEntry(dbCon, userName, nickName, curr_carName, trackName, LapTimeUsedForPb));
                int last = list.Count - 1;
                (list[last] as DriverLapEntry).pos = topPos + 1;
                (list[last] as DriverLapEntry).total = (int)cnt;
                //if noPB, remove entry
//                if ((list[last] as DriverLapEntry).personalBestLapTime == 0)
//                   list.RemoveAt(last);
                topPos++;

            }
            retQuery.Close();
            retQuery.Dispose();
            dbCon.executeNonQuery("COMMIT TRANSACTION");

            // Mise à jour des poules, par défaut tous le monde est -1 = Not Qualified
            if (onlyQualUser && MaxGroupQual != 0 )
            {
                System.Collections.ArrayList group = MkGroup(MaxGroupQual, MaxUserGroupQual, MinUserGroupQual, (int)cnt);
                int j = 0;
                for (int i = 0; i < group.Count; i++)
                {
                    int k = (int)group[i];
                    while (k-- != 0)
                    {
                        if (j >= from && j < ( from + list.Count) )
                            (list[j-from] as DriverLapEntry).group = i;
                        j++;
                    }
                }
            }
            return list;

        }
        public System.Collections.ArrayList GetCars(string trackName)
        {
            string sqlStr = "";
            System.Collections.ArrayList list = new System.Collections.ArrayList();

            sqlStr = "SELECT DISTINCT( carName ) FROM fi_epb WHERE trackName = '" + trackName + "'";
            IDataReader retQuery = dbCon.executeQuery( sqlStr );
            while (retQuery.Read())
            {
                list.Add( retQuery.GetString( 0 ) );
            }
            retQuery.Close();
            retQuery.Dispose();
            list.Sort();
            return list;
        }



       public bool ImportLapperPB(string filepath) // importation du fichier Lapper des PB
        {
//Searching format dat
            bool frenchDat = true;
            using (System.IO.StreamReader sr = new System.IO.StreamReader(filepath))
            {
                string userName, nickName, datePb, timePb, carName, trackName;
                int laps;
                long LTime;
                long[] PBsplit = new long[3];
                long[] PBBestSplitDiff = new long[3];
                long PBBestSplitDiffLast;
                string insertDate = "";

                string pbVersion = sr.ReadLine();
                if (pbVersion.IndexOf("USERNAME") == -1) // Si pas fichier avec Username, on efface et on recrer
                    return false;
                dbCon.executeNonQuery("BEGIN TRANSACTION");
                myDebug.WriteLine("mss","Import old PB file " + filepath + "...");
                dbCon.executeNonQuery("DELETE FROM fi_epb");
                while ( !sr.EndOfStream )
                {
                    userName = sr.ReadLine();
                    if (userName == "")
                        break;
                    nickName = sr.ReadLine();
                    laps = int.Parse(sr.ReadLine());
                    datePb = sr.ReadLine();
                    timePb = sr.ReadLine();
                    carName = sr.ReadLine();
                    LTime = unitConv.HMSToLong(sr.ReadLine());
                    trackName = sr.ReadLine();
                    PBsplit[0] = unitConv.HMSToLong(sr.ReadLine());
                    PBsplit[1] = unitConv.HMSToLong(sr.ReadLine());
                    PBsplit[2] = unitConv.HMSToLong(sr.ReadLine());
                    PBBestSplitDiff[0] = unitConv.HMSToLong(sr.ReadLine());
                    PBBestSplitDiff[1] = unitConv.HMSToLong(sr.ReadLine());
                    PBBestSplitDiff[2] = unitConv.HMSToLong(sr.ReadLine());
                    PBBestSplitDiffLast = unitConv.HMSToLong(sr.ReadLine());

                    char splitChar = '/';
                    if( datePb.IndexOf( '.' ) != -1 )
                        splitChar = '.';
                    else if( datePb.IndexOf( '/' ) != -1 )
                        splitChar = '/';
                    string[] elem = datePb.Split(splitChar);
                    if (int.Parse(elem[1]) > 12)
                    {
                        frenchDat = false;
                    }
                    if (int.Parse(elem[0]) > 12)
                    {
                        frenchDat = true;
                    }
                    if (frenchDat)
                    {
                        insertDate = elem[2].PadLeft(4, '0')
                                        + "/"
                                        + elem[1].PadLeft(2, '0')
                                        + "/"
                                        + elem[0].PadLeft(2, '0');
                    }
                    else
                    {
                        insertDate = elem[2].PadLeft(4, '0')
                                        + "/"
                                        + elem[0].PadLeft(2, '0')
                                        + "/"
                                        + elem[1].PadLeft(2, '0');
                    }
                    dbCon.executeNonQuery("INSERT INTO fi_epb ("
                                + "userName"
                                + ",nickName"
                                + ",nickNameStripped"
                                + ",carName"
                                + ",trackName"
                                + ",laps"
                                + ",date"
                                + ",time"
                                + ",pbLapTime"
                                + ",pbLapTimeSplit1"
                                + ",pbLapTimeSplit2"
                                + ",pbLapTimeSplit3"
                                + ",pbSectorSplit1"
                                + ",pbSectorSplit2"
                                + ",pbSectorSplit3"
                                + ",pbSectorSplitLast"
                            + ") VALUES ("
                                + "'" + userName.ToLower().Replace("'", "''") + "'"
                                + ",'" + nickName.Replace("'", "''").Replace("\0", "") + "'"
                                + ",'" + UTILS.utils.stripLFSColor( nickName.Replace("'", "''").Replace("\0", "")) + "'"
                                + ",'" + carName + "'"
                                + ",'" + trackName + "'"
                                + "," + laps
                                + ",'" + insertDate + "'"
                                + ",'" + timePb + "'"
                                + "," + LTime
                                + "," + PBsplit[0]
                                + "," + PBsplit[1]
                                + "," + PBsplit[2]
                                + "," + PBBestSplitDiff[0]
                                + "," + PBBestSplitDiff[1]
                                + "," + PBBestSplitDiff[2]
                                + "," + PBBestSplitDiffLast
                            + ")");
                    uint lastRowId = dbCon.getLastRowId("fi_epb");
                    IDataReader retQuery = dbCon.executeQuery("SELECT idEpb FROM fi_epb WHERE rowid = " + lastRowId);
                    while ( retQuery.Read() )
                    {
                        long idEpB = retQuery.GetInt64(0);
                        dbCon.executeNonQuery("INSERT INTO fi_lpb ("
                            + "idEpb"
                            + ",date"
                            + ",time"
                            + ",lapTime"
                            + ",split1"
                            + ",split2"
                            + ",split3"
                        + ") VALUES ("
                            + idEpB
                            + ",'" + insertDate + "'"
                            + ",'" + timePb + "'"
                            + "," + LTime
                            + "," + PBsplit[0]
                            + "," + PBsplit[1]
                            + "," + PBsplit[2]
                        + ")");

                    }
                    retQuery.Close();
                    retQuery.Dispose();
                }

                dbCon.executeNonQuery("COMMIT TRANSACTION");
            }

            return true;
        }

    }

}
