//public static string SelectJumperName(int uniqueID)
//select strings
public static string SelectAttribute(int uniqueID, string attribute)
{
Sqlite.Open();
dbcmd.CommandText = "SELECT " + attribute + " FROM " + Constants.PersonTable + " WHERE uniqueID == " + uniqueID;
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
string myReturn = "";
if (reader.Read())
{
myReturn = reader[0].ToString();
}
reader.Close();
Sqlite.Close();
return(myReturn);
}
protected virtual void getData()
{
myPersonsAndPS = SqlitePersonSession.SelectCurrentSessionPersons(mySession.UniqueID, true);
//Leave SQL opened in all this process
Sqlite.Open(); // ------------------------------
myJumps = SqliteJump.SelectJumps(true, mySession.UniqueID, -1, "", "",
Sqlite.Orders_by.DEFAULT, -1);
myJumpsRj = SqliteJumpRj.SelectJumps(true, mySession.UniqueID, -1, "", "");
myRuns = SqliteRun.SelectRuns(true, mySession.UniqueID, -1, "",
Sqlite.Orders_by.DEFAULT, -1);
myRunsInterval = SqliteRunInterval.SelectRuns(true, mySession.UniqueID, -1, "");
myReactionTimes = SqliteReactionTime.SelectReactionTimes(true, mySession.UniqueID, -1,
Sqlite.Orders_by.DEFAULT, -1);
myPulses = SqlitePulse.SelectPulses(true, mySession.UniqueID, -1);
myMCs = SqliteMultiChronopic.SelectTests(true, mySession.UniqueID, -1);
Sqlite.Close(); // ------------------------------
}
public static int Insert(bool dbconOpened, string tableName, string name, string place, DateTime date, int personsSportID, int personsSpeciallityID, int personsPractice, string comments, int serverUniqueID, int evaluatorID, string evaluatorCJVersion, string evaluatorOS, DateTime uploadedDate, int uploadingState)
{
if (!dbconOpened)
{
Sqlite.Open();
}
//(uniqueID == "-1")
// uniqueID = "NULL";
string uniqueID = "NULL";
dbcmd.CommandText = "INSERT INTO " + tableName + " (uniqueID, name, place, date, personsSportID, personsSpeciallityID, personsPractice, comments, serverUniqueID, evaluatorID, evaluatorCJVersion, evaluatorOS, uploadedDate, uploadingState)" +
" VALUES (" + uniqueID + ", \""
+ name + "\", \"" + place + "\", \"" + UtilDate.ToSql(date) + "\", " +
personsSportID + ", " + personsSpeciallityID + ", " +
personsPractice + ", \"" + comments + "\", " +
serverUniqueID + ", " + evaluatorID + ", \"" +
evaluatorCJVersion + "\", \"" + evaluatorOS + "\", \"" +
UtilDate.ToSql(uploadedDate) + "\", " + uploadingState +
")";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
//int myLast = dbcon.LastInsertRowId;
//http://stackoverflow.com/questions/4341178/getting-the-last-insert-id-with-sqlite-net-in-c
string myString = @"select last_insert_rowid()";
dbcmd.CommandText = myString;
int myLast = Convert.ToInt32(dbcmd.ExecuteScalar()); // Need to type-cast since `ExecuteScalar` returns an object.
if (!dbconOpened)
{
Sqlite.Close();
}
return(myLast);
}
public static JumpType SelectAndReturnJumpType(string typeName, bool dbconOpened)
{
if (!dbconOpened)
{
Sqlite.Open();
}
dbcmd.CommandText = "SELECT * " +
" FROM " + Constants.JumpTypeTable + " " +
" WHERE name = \"" + typeName +
"\" ORDER BY uniqueID";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
JumpType myJumpType = new JumpType();
while (reader.Read())
{
myJumpType.Name = reader[1].ToString();
myJumpType.StartIn = Util.IntToBool(Convert.ToInt32(reader[2].ToString()));
myJumpType.HasWeight = Util.IntToBool(Convert.ToInt32(reader[3].ToString()));
myJumpType.Description = reader[4].ToString();
}
myJumpType.IsPredefined = myJumpType.FindIfIsPredefined();
reader.Close();
if (!dbconOpened)
{
Sqlite.Close();
}
return(myJumpType);
}
public static RunType SelectAndReturnRunType(string typeName, bool dbconOpened)
{
if (!dbconOpened)
{
Sqlite.Open();
}
dbcmd.CommandText = "SELECT * " +
" FROM " + Constants.RunTypeTable +
" WHERE name = \"" + typeName +
"\" ORDER BY uniqueID";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
RunType myRunType = new RunType();
while (reader.Read())
{
myRunType.Name = reader[1].ToString();
myRunType.Distance = Convert.ToDouble(reader[2].ToString());
myRunType.Description = reader[3].ToString();
}
myRunType.IsPredefined = myRunType.FindIfIsPredefined();
reader.Close();
if (!dbconOpened)
{
Sqlite.Close();
}
return(myRunType);
}
public static double Distance(string typeName)
{
Sqlite.Open();
dbcmd.CommandText = "SELECT distance " +
" FROM " + Constants.RunTypeTable +
" WHERE name == \"" + typeName + "\"";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
double distance = 0;
while (reader.Read())
{
distance = Convert.ToDouble(reader[0].ToString());
}
reader.Close();
Sqlite.Close();
return(distance);
}
/*
* Jump class methods
*/
//public static int Insert(int personID, int sessionID, string type, double tv, double tc, int fall, double weight, string limited, string description, int simulated)
public static int Insert(bool dbconOpened, string tableName, string uniqueID, int personID, int sessionID, string type, double tv, double tc, double fall, double weight, string description, double angle, int simulated)
{
if (!dbconOpened)
{
Sqlite.Open();
}
if (uniqueID == "-1")
{
uniqueID = "NULL";
}
dbcmd.CommandText = "INSERT INTO " + tableName +
" (uniqueID, personID, sessionID, type, tv, tc, fall, weight, description, angle, simulated)" +
" VALUES (" + uniqueID + ", "
+ personID + ", " + sessionID + ", \"" + type + "\", "
+ Util.ConvertToPoint(tv) + ", " + Util.ConvertToPoint(tc) + ", " + Util.ConvertToPoint(fall) + ", \""
+ Util.ConvertToPoint(weight) + "\", \"" + description + "\", "
+ Util.ConvertToPoint(angle) + ", " + simulated + ")";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
//int myLast = dbcon.LastInsertRowId;
//http://stackoverflow.com/questions/4341178/getting-the-last-insert-id-with-sqlite-net-in-c
string myString = @"select last_insert_rowid()";
dbcmd.CommandText = myString;
int myLast = Convert.ToInt32(dbcmd.ExecuteScalar()); // Need to type-cast since `ExecuteScalar` returns an object.
if (!dbconOpened)
{
Sqlite.Close();
}
return(myLast);
}
//do not use this because some graph like the takeoff.png are not in this SQL table
public static string GraphLinkSelectFileName(string tableName, string eventName)
{
Sqlite.Open();
dbcmd.CommandText = "SELECT graphFileName FROM graphLinkTable WHERE tableName == \"" + tableName + "\" AND eventName ==\"" + eventName + "\"";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
string returnString = "";
while (reader.Read())
{
returnString = reader[0].ToString();
}
reader.Close();
Sqlite.Close();
return(returnString);
}
public static string [] Select(int uniqueID)
{
Sqlite.Open();
dbcmd.CommandText = "SELECT * FROM " + Constants.CountryTable + " WHERE uniqueID == " + uniqueID;
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
reader.Read();
string [] myReturn = new String [4];
myReturn[0] = reader[0].ToString(); //uniqueID
myReturn[1] = reader[1].ToString(); //name
myReturn[2] = reader[2].ToString(); //code
myReturn[3] = reader[3].ToString(); //continent
reader.Close();
Sqlite.Close();
return(myReturn);
}
public static void UpdateExercise(bool dbconOpened, string nameOld, string name, int percentBodyWeight,
string ressistance, string description, string speed1RM)
{
if (!dbconOpened)
{
Sqlite.Open();
}
dbcmd.CommandText = "UPDATE " + Constants.EncoderExerciseTable + " SET " +
" name = \"" + name +
"\", percentBodyWeight = " + percentBodyWeight +
", ressistance = \"" + ressistance +
"\", description = \"" + description +
"\", future1 = \"" + speed1RM +
"\" WHERE name = \"" + nameOld + "\"";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
if (!dbconOpened)
{
Sqlite.Close();
}
}
public RunningNumberObj select(int id)
{
string txtQuery = string.Format("SELECT * FROM {0} WHERE id = {1}", this.tableName, id);
try
{
RunningNumberObj data = new RunningNumberObj();
using (SQLiteDataReader dr = sqlite.ExecuteReader(txtQuery))
{
if (dr.Read())
{
data.id = Convert.ToInt32(dr["id"]);
data.prefix = dr["prefix"].ToString();
data.number = dr["number"].ToString();
}
}
sqlite.Close();
return(data);
}
catch (Exception ex)
{
throw ex;
}
}
public static ArrayList SelectAllPersonsRecuperable(string sortedBy, int except, int inSession, string searchFilterName)
{
//sortedBy = name or uniqueID (= creation date)
//1st select all the person.uniqueID of people who are in CurrentSession (or none if except == -1)
//2n select all names in database (or in one session if inSession != -1)
//3d filter all names (save all found in 2 that is not in 1)
//
//probably this can be made in only one time... future
//
//1
string tp = Constants.PersonTable;
string tps = Constants.PersonSessionTable;
Sqlite.Open();
dbcmd.CommandText = "SELECT " + tp + ".uniqueID " +
" FROM " + tp + "," + tps +
" WHERE " + tps + ".sessionID == " + except +
" AND " + tp + ".uniqueID == " + tps + ".personID ";
LogB.SQL(dbcmd.CommandText.ToString());
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
ArrayList arrayExcept = new ArrayList(2);
while (reader.Read())
{
arrayExcept.Add(reader[0].ToString());
}
reader.Close();
Sqlite.Close();
//2
//sort no case sensitive when we sort by name
if (sortedBy == "name")
{
sortedBy = "lower(" + tp + ".name)";
}
else
{
sortedBy = tp + ".uniqueID";
}
Sqlite.Open();
if (inSession == -1)
{
string nameLike = "";
if (searchFilterName != "")
{
nameLike = " WHERE LOWER(" + tp + ".name) LIKE LOWER (\"%" + searchFilterName + "%\") ";
}
dbcmd.CommandText =
"SELECT * FROM " + tp +
nameLike +
" ORDER BY " + sortedBy;
}
else
{
dbcmd.CommandText =
"SELECT " + tp + ".* FROM " + tp + ", " + tps +
" WHERE " + tps + ".sessionID == " + inSession +
" AND " + tp + ".uniqueID == " + tps + ".personID " +
" ORDER BY " + sortedBy;
}
LogB.SQL(dbcmd.CommandText.ToString());
SqliteDataReader reader2;
reader2 = dbcmd.ExecuteReader();
ArrayList arrayReturn = new ArrayList(2);
bool found;
//3
while (reader2.Read())
{
found = false;
foreach (string line in arrayExcept)
{
if (line == reader2[0].ToString())
{
found = true;
goto finishForeach;
}
}
finishForeach:
if (!found)
{
Person p = new Person(
Convert.ToInt32(reader2[0].ToString()), //uniqueID
reader2[1].ToString(), //name
reader2[2].ToString(), //sex
UtilDate.FromSql(reader2[3].ToString()), //dateBorn
Convert.ToInt32(reader2[4].ToString()), //race
Convert.ToInt32(reader2[5].ToString()), //countryID
reader2[6].ToString(), //description
reader2[7].ToString(), //future1: rfid
Convert.ToInt32(reader2[9].ToString()) //serverUniqueID
);
arrayReturn.Add(p);
}
}
reader2.Close();
Sqlite.Close();
return(arrayReturn);
}
//if all persons, put -1 in personID
//if all types put, "" in filterType
public static string[] SelectReactionTimes(bool dbconOpened, int sessionID, int personID, string filterType,
Orders_by order, int limit)
{
if (!dbconOpened)
{
Sqlite.Open();
}
string tp = Constants.PersonTable;
string filterPersonString = "";
if (personID != -1)
{
filterPersonString = " AND " + tp + ".uniqueID = " + personID;
}
string filterTypeString = "";
if (filterType != "")
{
filterTypeString = " AND reactionTime.type == \"" + filterType + "\" ";
}
string orderByString = " ORDER BY upper(" + tp + ".name), reactionTime.uniqueID";
if (order == Orders_by.ID_DESC)
{
orderByString = " ORDER BY reactionTime.uniqueID DESC ";
}
string limitString = "";
if (limit != -1)
{
limitString = " LIMIT " + limit;
}
dbcmd.CommandText = "SELECT " + tp + ".name, reactionTime.* " +
" FROM " + tp + ", reactionTime " +
" WHERE " + tp + ".uniqueID = reactionTime.personID" +
" AND reactionTime.sessionID = " + sessionID +
filterPersonString +
filterTypeString +
orderByString +
limitString;
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
ArrayList myArray = new ArrayList(2);
int count = new int();
count = 0;
while (reader.Read())
{
myArray.Add(reader[0].ToString() + ":" + //person.name
reader[1].ToString() + ":" + //uniqueID
reader[2].ToString() + ":" + //personID
reader[3].ToString() + ":" + //sessionID
reader[4].ToString() + ":" + //type
Util.ChangeDecimalSeparator(reader[5].ToString()) + ":" + //time
reader[6].ToString() + ":" + //description
reader[7].ToString() //simulated
);
count++;
}
reader.Close();
if (!dbconOpened)
{
Sqlite.Close();
}
string [] myEvents = new string[count];
count = 0;
foreach (string line in myArray)
{
myEvents [count++] = line;
}
return(myEvents);
}
//if uniqueID != -1, returns an especific EncoderExercise that can be read like this
//EncoderExercise ex = (EncoderExercise) SqliteEncoder.SelectEncoderExercises(eSQL.exerciseID)[0];
public static ArrayList SelectEncoderExercises(bool dbconOpened, int uniqueID, bool onlyNames)
{
if (!dbconOpened)
{
Sqlite.Open();
}
string uniqueIDStr = "";
if (uniqueID != -1)
{
uniqueIDStr = " WHERE " + Constants.EncoderExerciseTable + ".uniqueID = " + uniqueID;
}
if (onlyNames)
{
dbcmd.CommandText = "SELECT name FROM " + Constants.EncoderExerciseTable + uniqueIDStr;
}
else
{
dbcmd.CommandText = "SELECT * FROM " + Constants.EncoderExerciseTable + uniqueIDStr;
}
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
ArrayList array = new ArrayList(1);
EncoderExercise ex = new EncoderExercise();
if (onlyNames)
{
while (reader.Read())
{
ex = new EncoderExercise(reader[0].ToString());
array.Add(ex);
}
}
else
{
while (reader.Read())
{
double speed1RM = 0;
if (reader[5].ToString() != "")
{
speed1RM = Convert.ToDouble(Util.ChangeDecimalSeparator(reader[5].ToString()));
}
ex = new EncoderExercise(
Convert.ToInt32(reader[0].ToString()), //uniqueID
reader[1].ToString(), //name
Convert.ToInt32(reader[2].ToString()), //percentBodyWeight
reader[3].ToString(), //ressistance
reader[4].ToString(), //description
speed1RM
);
array.Add(ex);
}
}
reader.Close();
if (!dbconOpened)
{
Sqlite.Close();
}
return(array);
}
public static void Creat(string csFilePath, string dbFilePath, string tableName, bool firstCreat)
{
List <string> tableInfo = new List <string>();
Sqlite sqlite = new Sqlite(dbFilePath);
tableInfo = sqlite.GetTableInfo(tableName);
sqlite.Close();
sqlite = null;
if (firstCreat)
{
var me = new StreamWriter(csFilePath + "/" + tableName + "Method.cs", false, new UTF8Encoding(false));
var meBuilder = new StringBuilder();
meBuilder.AppendFormat("public partial class {0} : IConfig", tableName);
meBuilder.AppendLine();
meBuilder.AppendLine("{");
meBuilder.AppendLine("}");
me.Write(meBuilder);
me.Flush();
me.Close();
}
var sw = new StreamWriter(csFilePath + "/" + tableName + ".cs", false, new UTF8Encoding(false));
var strBuilder = new StringBuilder();
strBuilder.AppendLine("/****************************************************************************");
strBuilder.AppendFormat(" * {0}.{1} {2}\n", DateTime.Now.Year, DateTime.Now.Month, SystemInfo.deviceName);
strBuilder.AppendLine(" ****************************************************************************/");
strBuilder.AppendLine();
strBuilder.AppendFormat("public partial class {0} : IConfig", tableName);
strBuilder.AppendLine();
strBuilder.AppendLine("{");
for (int i = 0; i < tableInfo.Count; i += 6)
{
strBuilder.AppendFormat("\t[ConfigField(\"{0}\")]", tableInfo[i + 1]).AppendLine();
if (tableInfo[i + 2] == "TEXT" || tableInfo[i + 2] == "VARCHAR(255)")
{
strBuilder.AppendFormat("\tpublic string {0}", tableInfo[i + 1]);
strBuilder.AppendLine(" { get; set; }");
}
else if (tableInfo[i + 2] == "INTEGER" || tableInfo[i + 2] == "INT")
{
strBuilder.AppendFormat("\tpublic int {0} ", tableInfo[i + 1]);
strBuilder.AppendLine(" { get; set; }");
}
else if (tableInfo[i + 2] == "FLOAT")
{
strBuilder.AppendFormat("\tpublic float {0} ", tableInfo[i + 1]);
strBuilder.AppendLine(" { get; set; }");
}
strBuilder.AppendLine();
}
strBuilder.AppendLine("}");
sw.Write(strBuilder);
sw.Flush();
sw.Close();
}
//pass uniqueID value and then will return one record. do like this:
//EncoderSQL eSQL = (EncoderSQL) SqliteEncoder.Select(false, myUniqueID, 0, 0, "")[0];
//or
//pass uniqueID==-1 and personID, sessionID, signalOrCurve values, and will return some records
//personID can be -1 to get all on that session
//sessionID can be -1 to get all sessions
//signalOrCurve can be "all"
public static ArrayList EncoderSelect098(bool dbconOpened,
int uniqueID, int personID, int sessionID, string signalOrCurve, bool onlyActive)
{
if (!dbconOpened)
{
Sqlite.Open();
}
string personIDStr = "";
if (personID != -1)
{
personIDStr = " personID = " + personID + " AND ";
}
string sessionIDStr = "";
if (sessionID != -1)
{
sessionIDStr = " sessionID = " + sessionID + " AND ";
}
string selectStr = "";
if (uniqueID != -1)
{
selectStr = Constants.EncoderTable + ".uniqueID = " + uniqueID;
}
else
{
if (signalOrCurve == "all")
{
selectStr = personIDStr + sessionIDStr;
}
else
{
selectStr = personIDStr + sessionIDStr + " signalOrCurve = \"" + signalOrCurve + "\"";
}
}
string andString = "";
if (selectStr != "")
{
andString = " AND ";
}
string onlyActiveString = "";
if (onlyActive)
{
onlyActiveString = " AND " + Constants.EncoderTable + ".future1 = \"active\" ";
}
dbcmd.CommandText = "SELECT " +
Constants.EncoderTable + ".*, " + Constants.EncoderExerciseTable + ".name FROM " +
Constants.EncoderTable + ", " + Constants.EncoderExerciseTable +
" WHERE " + selectStr +
andString + Constants.EncoderTable + ".exerciseID = " +
Constants.EncoderExerciseTable + ".uniqueID " +
onlyActiveString +
" ORDER BY substr(filename,-23,19)"; //this contains the date of capture signal
LogB.SQL(dbcmd.CommandText.ToString());
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
ArrayList array = new ArrayList(1);
EncoderSQL098 es = new EncoderSQL098();
while (reader.Read())
{
es = new EncoderSQL098(
reader[0].ToString(), //uniqueID
Convert.ToInt32(reader[1].ToString()), //personID
Convert.ToInt32(reader[2].ToString()), //sessionID
Convert.ToInt32(reader[3].ToString()), //exerciseID
reader[4].ToString(), //eccon
reader[5].ToString(), //laterality
reader[6].ToString(), //extraWeight
reader[7].ToString(), //signalOrCurve
reader[8].ToString(), //filename
reader[9].ToString(), //url
Convert.ToInt32(reader[10].ToString()), //time
Convert.ToInt32(reader[11].ToString()), //minHeight
Convert.ToDouble(Util.ChangeDecimalSeparator(reader[12].ToString())), //smooth UNUSED
reader[13].ToString(), //description
reader[14].ToString(), //future1
reader[15].ToString(), //future2
reader[16].ToString(), //future3
reader[17].ToString() //EncoderExercise.name
);
array.Add(es);
}
reader.Close();
if (!dbconOpened)
{
Sqlite.Close();
}
return(array);
}
protected void sqliteThings()
{
configChronojump = new Config();
configChronojump.Read();
bool badExit = checkIfChronojumpExitAbnormally();
if (badExit)
{
if (chronojumpIsExecutingNTimes())
{
messageToShowOnBoot += Catalog.GetString("Chronojump is already running") + "\n\n" +
Catalog.GetString("Chronojump will exit now.");
chronojumpHasToExit = true;
quitNowCjTwoTimes = true;
LogB.Error("Chronojump is already running.");
return;
}
else
{
chronojumpCrashedBefore();
}
}
//print version of chronojump
progVersion = BuildInfo.chronojumpVersion;
LogB.Information("Chronojump version: {0}", progVersion);
//to store user videos and photos
Util.CreateMultimediaDirsIfNeeded();
//to store (encoder, force sensor, run encoder) data and graphs
UtilEncoder.CreateEncoderDirIfNeeded();
Util.CreateForceSensorDirIfNeeded();
Util.CreateRaceAnalyzerDirIfNeeded();
//TODO: when a session is deleted, encoder data has to be deleted, also multimedia videos, I suppose. Show message to user warning about it
//TODO: encoder weight auto written depending on person loaded, and changes if it changes person or weight
//move database to new location if chronojump version is before 0.7
moveDatabaseToNewLocationIfNeeded();
LogB.Information("move? ended");
splashMessageChange(1); //checking database
/*
* splashMessage = "pre-connect";
* needUpdateSplashMessage = true;
* Console.ReadLine();
*/
Sqlite.CreateDir();
bool defaultDBLocation = Sqlite.Connect();
LogB.SQL("sqlite connected");
/*
* splashMessage = "post-connect" + defaultDBLocation.ToString();
* needUpdateSplashMessage = true;
* Console.ReadLine();
*/
//Chech if the DB file exists
if (!Sqlite.CheckTables(defaultDBLocation))
{
LogB.SQL(Catalog.GetString("no tables, creating ..."));
creatingDB = true;
splashMessageChange(2); //creating database
/*
* splashMessage = "pre-create";
* needUpdateSplashMessage = true;
* Console.ReadLine();
*/
Sqlite.CreateDir();
Sqlite.CreateFile();
//Sqlite.CreateFile(defaultDBLocation);
/*
* splashMessage = "post-create";
* needUpdateSplashMessage = true;
* Console.ReadLine();
*/
createRunningFileName(runningFileName);
Sqlite.CreateTables(false); //not server
creatingDB = false;
}
else
{
LogB.SQL("doing backup");
//backup the database
Util.BackupDirCreateIfNeeded();
splashMessageChange(3); //making db backup
Util.BackupDatabase();
LogB.SQL("made a database backup"); //not compressed yet, it seems System.IO.Compression.DeflateStream and
//System.IO.Compression.GZipStream are not in mono
if (!Sqlite.IsSqlite3())
{
bool ok = Sqlite.ConvertFromSqlite2To3();
if (!ok)
{
LogB.Error("problem with sqlite");
//check (spanish)
//http://mail.gnome.org/archives/chronojump-devel-list/2008-March/msg00011.html
string errorMessage = Catalog.GetString("Failed database conversion, ensure you have libsqlite3-0 installed. \nIf problems persist ask in chronojump-list");
errorMessage += "\n\n" + string.Format(Catalog.GetString("If you have no data on your database (you just installed Chronojump), you can fix this problem deleting this file: {0}"),
Util.GetDatabaseDir() + Path.DirectorySeparatorChar + "chronojump.db") +
"\n" + Catalog.GetString("And starting Chronojump again.");
LogB.Error(errorMessage);
messageToShowOnBoot += errorMessage;
chronojumpHasToExit = true;
return;
}
Sqlite.Connect();
}
splashMessageChange(4); //updating DB
updatingDB = true;
if (Sqlite.ChangeDjToDJna())
{
messageToShowOnBoot += Catalog.GetString("All DJ jumps have been renamed as 'DJna' (Drop Jumps with No Arms).") + "\n\n" +
Catalog.GetString("If your Drop Jumps were executed using the arms, please rename them manually as 'DJa'.") + "\n";
}
bool softwareIsNew = Sqlite.ConvertToLastChronojumpDBVersion();
updatingDB = false;
if (!softwareIsNew)
{
//Console.Clear();
string errorMessage = string.Format(Catalog.GetString("Sorry, this Chronojump version ({0}) is too old for your database."), progVersion) + "\n" +
Catalog.GetString("Please update Chronojump") + ":\n";
errorMessage += "http://chronojump.org";
//errorMessage += "\n\n" + Catalog.GetString("Press any key");
LogB.Error(errorMessage);
messageToShowOnBoot += errorMessage;
chronojumpHasToExit = true;
}
LogB.Information(Catalog.GetString("tables already created"));
//check for bad Rjs (activate if program crashes and you use it in the same db before v.0.41)
//SqliteJump.FindBadRjs();
createRunningFileName(runningFileName);
}
//splashMessageChange(5); //check for new version
splashMessageChange(5); //connecting to server
messageToShowOnBoot += recuperateBrokenEvents();
//connect to server to Ping
versionAvailable = "";
versionAvailable = Constants.ServerOffline;
//doing ping using json methods
/*
* temporarily disabled on start
* string machineID = SqlitePreferences.Select("machineID", false);
* Json js = new Json();
* bool success = js.Ping(UtilAll.GetOS(), progVersion, machineID);
* if(success)
* LogB.Information(js.ResultMessage);
* else
* LogB.Error(js.ResultMessage);
*/
allSQLCallsDoneOnSqliteThingsThread = false;
//wait until pinging ends (or it's cancelled)
//while(! pingEnd) {
//}
Sqlite.Open();
//TODO: fix this to the new code
string versionAvailableKnown = SqlitePreferences.Select("versionAvailable", true);
if (versionAvailable != Constants.ServerOffline && new Version(versionAvailable) > new Version(progVersion))
{
//check if available version is higher than known available version
Version versionAvailableAsV = new Version(versionAvailable);
Version versionAvailableKnownAsV;
bool updateKnownVersion = false;
if (versionAvailableKnown == "")
{
updateKnownVersion = true;
}
else
{
versionAvailableKnownAsV = new Version(versionAvailableKnown);
if (versionAvailableAsV > versionAvailableKnownAsV)
{
updateKnownVersion = true;
}
}
if (updateKnownVersion)
{
//is the first time we know about this new version
//just write on db and show message to user
SqlitePreferences.Update(Constants.PrefVersionAvailable, versionAvailable, true);
versionAvailableKnown = versionAvailable;
messageToShowOnBoot += string.Format(Catalog.GetString(
"\nNew Chronojump version available on website.\nYour Chronojump version is: {1}"),
versionAvailable, progVersion) + "\n\n" +
Catalog.GetString("Please, update to new version.") + "\n";
}
}
//if chronojump chrashed before
if (badExit)
{
if (versionAvailableKnown.Length > 0 && new Version(versionAvailableKnown) > new Version(progVersion))
{
messageToShowOnBoot += "\n" + Catalog.GetString("Chronojump crashed before.") + "\n" +
Catalog.GetString("Please, update to new version: ") + versionAvailableKnown + "\n";
}
else
{
messageToShowOnBoot += messageCrashedBefore;
//SqlitePreferences.Update("videoOn", "False", true);
}
}
splashMessageChange(6); //preparing main window
//start as "simulated"
SqlitePreferences.Update("simulated", "True", true); //dbcon opened
Sqlite.Close();
// Chronojump sqlite is in an initialized state, let's keep the Sqlite state here
// to be re-used
Sqlite.saveClassState();
allSQLCallsDoneOnSqliteThingsThread = true;
LogB.SQL("all SQL calls done on sqliteThings thread");
UtilAll.IsWindows(); //only as additional info here
//Application.Init();
needEndSplashWin = true;
}
public static ArrayList Select1RM(bool dbconOpened, int personID, int sessionID, int exerciseID, bool returnPersonNameAndExerciseName)
{
if (!dbconOpened)
{
Sqlite.Open();
}
string whereStr = "";
if (personID != -1 || sessionID != -1 || exerciseID != -1)
{
whereStr = " WHERE ";
string andStr = "";
if (personID != -1)
{
whereStr += " " + Constants.Encoder1RMTable + ".personID = " + personID;
andStr = " AND ";
}
if (sessionID != -1)
{
whereStr += andStr + " " + Constants.Encoder1RMTable + ".sessionID = " + sessionID;
andStr = " AND ";
}
if (exerciseID != -1)
{
whereStr += andStr + " " + Constants.Encoder1RMTable + ".exerciseID = " + exerciseID;
}
}
if (returnPersonNameAndExerciseName)
{
if (whereStr == "")
{
whereStr = " WHERE ";
}
else
{
whereStr += " AND ";
}
whereStr += Constants.Encoder1RMTable + ".personID = person77.uniqueID AND " +
Constants.Encoder1RMTable + ".exerciseID = encoderExercise.uniqueID";
}
if (returnPersonNameAndExerciseName)
{
dbcmd.CommandText = "SELECT " + Constants.Encoder1RMTable + ".*, person77.name, encoderExercise.name, session.date" +
" FROM " + Constants.Encoder1RMTable + ", person77, encoderExercise, session " +
whereStr + " AND " + Constants.Encoder1RMTable + ".sessionID = session.uniqueID " +
" ORDER BY uniqueID DESC"; //this allows to select the last uniqueID because will be the first in the returned array
}
else
{
dbcmd.CommandText = "SELECT " + Constants.Encoder1RMTable + ".*, session.date FROM " +
Constants.Encoder1RMTable + ", session" + whereStr +
" ORDER BY uniqueID DESC"; //this allows to select the last uniqueID because will be the first in the returned array
}
LogB.SQL(dbcmd.CommandText.ToString());
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
ArrayList array = new ArrayList(1);
Encoder1RM e1RM = new Encoder1RM();
while (reader.Read())
{
if (returnPersonNameAndExerciseName)
{
e1RM = new Encoder1RM(
Convert.ToInt32(reader[0].ToString()), //uniqueID
Convert.ToInt32(reader[1].ToString()), //personID
Convert.ToInt32(reader[2].ToString()), //sessionID
UtilDate.FromSql(reader[10].ToString()), //date
Convert.ToInt32(reader[3].ToString()), //exerciseID
Convert.ToDouble(Util.ChangeDecimalSeparator(reader[4].ToString())), //load1RM
reader[8].ToString(), //personName
reader[9].ToString() //exerciseName
);
}
else
{
e1RM = new Encoder1RM(
Convert.ToInt32(reader[0].ToString()), //uniqueID
Convert.ToInt32(reader[1].ToString()), //personID
Convert.ToInt32(reader[2].ToString()), //sessionID
UtilDate.FromSql(reader[5].ToString()), //date
Convert.ToInt32(reader[3].ToString()), //exerciseID
Convert.ToDouble(Util.ChangeDecimalSeparator(reader[4].ToString())) //load1RM
);
}
array.Add(e1RM);
}
reader.Close();
if (!dbconOpened)
{
Sqlite.Close();
}
return(array);
}
public static ArrayList SelectAllPersonEvents(int personID)
{
SqliteDataReader reader;
ArrayList arraySessions = new ArrayList(2);
ArrayList arrayJumps = new ArrayList(2);
ArrayList arrayJumpsRj = new ArrayList(2);
ArrayList arrayRuns = new ArrayList(2);
ArrayList arrayRunsInterval = new ArrayList(2);
ArrayList arrayRTs = new ArrayList(2);
ArrayList arrayPulses = new ArrayList(2);
ArrayList arrayMCs = new ArrayList(2);
ArrayList arrayEncS = new ArrayList(2);
ArrayList arrayEncC = new ArrayList(2);
string tps = Constants.PersonSessionTable;
Sqlite.Open();
//session where this person is loaded
dbcmd.CommandText = "SELECT sessionID, session.Name, session.Place, session.Date " +
" FROM " + tps + ", session " +
" WHERE personID = " + personID + " AND session.uniqueID == " + tps + ".sessionID " +
" ORDER BY sessionID";
LogB.SQL(dbcmd.CommandText.ToString());
reader = dbcmd.ExecuteReader();
while (reader.Read())
{
arraySessions.Add(reader[0].ToString() + ":" + reader[1].ToString() + ":" +
reader[2].ToString() + ":" +
UtilDate.FromSql(reader[3].ToString()).ToShortDateString()
);
}
reader.Close();
//jumps
dbcmd.CommandText = "SELECT sessionID, count(*) FROM jump WHERE personID = " + personID +
" GROUP BY sessionID ORDER BY sessionID";
LogB.SQL(dbcmd.CommandText.ToString());
reader = dbcmd.ExecuteReader();
while (reader.Read())
{
arrayJumps.Add(reader[0].ToString() + ":" + reader[1].ToString());
}
reader.Close();
//jumpsRj
dbcmd.CommandText = "SELECT sessionID, count(*) FROM jumpRj WHERE personID = " + personID +
" GROUP BY sessionID ORDER BY sessionID";
LogB.SQL(dbcmd.CommandText.ToString());
reader = dbcmd.ExecuteReader();
while (reader.Read())
{
arrayJumpsRj.Add(reader[0].ToString() + ":" + reader[1].ToString());
}
reader.Close();
//runs
dbcmd.CommandText = "SELECT sessionID, count(*) FROM run WHERE personID = " + personID +
" GROUP BY sessionID ORDER BY sessionID";
LogB.SQL(dbcmd.CommandText.ToString());
reader = dbcmd.ExecuteReader();
while (reader.Read())
{
arrayRuns.Add(reader[0].ToString() + ":" + reader[1].ToString());
}
reader.Close();
//runsInterval
dbcmd.CommandText = "SELECT sessionID, count(*) FROM runInterval WHERE personID = " + personID +
" GROUP BY sessionID ORDER BY sessionID";
LogB.SQL(dbcmd.CommandText.ToString());
reader = dbcmd.ExecuteReader();
while (reader.Read())
{
arrayRunsInterval.Add(reader[0].ToString() + ":" + reader[1].ToString());
}
reader.Close();
//reaction time
dbcmd.CommandText = "SELECT sessionID, count(*) FROM reactiontime WHERE personID = " + personID +
" GROUP BY sessionID ORDER BY sessionID";
LogB.SQL(dbcmd.CommandText.ToString());
reader = dbcmd.ExecuteReader();
while (reader.Read())
{
arrayRTs.Add(reader[0].ToString() + ":" + reader[1].ToString());
}
reader.Close();
//pulses
dbcmd.CommandText = "SELECT sessionID, count(*) FROM pulse WHERE personID = " + personID +
" GROUP BY sessionID ORDER BY sessionID";
LogB.SQL(dbcmd.CommandText.ToString());
reader = dbcmd.ExecuteReader();
while (reader.Read())
{
arrayPulses.Add(reader[0].ToString() + ":" + reader[1].ToString());
}
reader.Close();
//MC
dbcmd.CommandText = "SELECT sessionID, count(*) FROM multiChronopic WHERE personID = " + personID +
" GROUP BY sessionID ORDER BY sessionID";
LogB.SQL(dbcmd.CommandText.ToString());
reader = dbcmd.ExecuteReader();
while (reader.Read())
{
arrayMCs.Add(reader[0].ToString() + ":" + reader[1].ToString());
}
reader.Close();
//EncS (encoder signal)
dbcmd.CommandText = "SELECT sessionID, count(*) FROM " + Constants.EncoderTable +
" WHERE personID == " + personID +
" AND signalOrCurve == \"signal\" " +
" GROUP BY sessionID ORDER BY sessionID";
LogB.SQL(dbcmd.CommandText.ToString());
reader = dbcmd.ExecuteReader();
while (reader.Read())
{
arrayEncS.Add(reader[0].ToString() + ":" + reader[1].ToString());
}
reader.Close();
//EncC (encoder curve)
dbcmd.CommandText = "SELECT sessionID, count(*) FROM " + Constants.EncoderTable +
" WHERE personID == " + personID +
" AND signalOrCurve == \"curve\" " +
" GROUP BY sessionID ORDER BY sessionID";
LogB.SQL(dbcmd.CommandText.ToString());
reader = dbcmd.ExecuteReader();
while (reader.Read())
{
arrayEncC.Add(reader[0].ToString() + ":" + reader[1].ToString());
}
reader.Close();
Sqlite.Close();
ArrayList arrayAll = new ArrayList(2);
string tempJumps;
string tempJumpsRj;
string tempRuns;
string tempRunsInterval;
string tempRTs;
string tempPulses;
string tempMCs;
string tempEncS;
string tempEncC;
bool found; //using found because a person can be loaded in a session
//but whithout having done any event yet
//foreach session where this jumper it's loaded, check which events has
foreach (string mySession in arraySessions)
{
string [] myStrSession = mySession.Split(new char[] { ':' });
tempJumps = "";
tempJumpsRj = "";
tempRuns = "";
tempRunsInterval = "";
tempRTs = "";
tempPulses = "";
tempMCs = "";
tempEncS = "";
tempEncC = "";
found = false;
foreach (string myJumps in arrayJumps)
{
string [] myStr = myJumps.Split(new char[] { ':' });
if (myStrSession[0] == myStr[0])
{
tempJumps = myStr[1];
found = true;
break;
}
}
foreach (string myJumpsRj in arrayJumpsRj)
{
string [] myStr = myJumpsRj.Split(new char[] { ':' });
if (myStrSession[0] == myStr[0])
{
tempJumpsRj = myStr[1];
found = true;
break;
}
}
foreach (string myRuns in arrayRuns)
{
string [] myStr = myRuns.Split(new char[] { ':' });
if (myStrSession[0] == myStr[0])
{
tempRuns = myStr[1];
found = true;
break;
}
}
foreach (string myRunsInterval in arrayRunsInterval)
{
string [] myStr = myRunsInterval.Split(new char[] { ':' });
if (myStrSession[0] == myStr[0])
{
tempRunsInterval = myStr[1];
found = true;
break;
}
}
foreach (string myRTs in arrayRTs)
{
string [] myStr = myRTs.Split(new char[] { ':' });
if (myStrSession[0] == myStr[0])
{
tempRTs = myStr[1];
found = true;
break;
}
}
foreach (string myPulses in arrayPulses)
{
string [] myStr = myPulses.Split(new char[] { ':' });
if (myStrSession[0] == myStr[0])
{
tempPulses = myStr[1];
found = true;
break;
}
}
foreach (string myMCs in arrayMCs)
{
string [] myStr = myMCs.Split(new char[] { ':' });
if (myStrSession[0] == myStr[0])
{
tempMCs = myStr[1];
found = true;
break;
}
}
foreach (string myEncS in arrayEncS)
{
string [] myStr = myEncS.Split(new char[] { ':' });
if (myStrSession[0] == myStr[0])
{
tempEncS = myStr[1];
found = true;
break;
}
}
foreach (string myEncC in arrayEncC)
{
string [] myStr = myEncC.Split(new char[] { ':' });
if (myStrSession[0] == myStr[0])
{
tempEncC = myStr[1];
found = true;
break;
}
}
//if has events, write it's data
if (found)
{
arrayAll.Add(myStrSession[1] + ":" + myStrSession[2] + ":" + //session name, place
myStrSession[3] + ":" + tempJumps + ":" + //sessionDate, jumps
tempJumpsRj + ":" + tempRuns + ":" + //jumpsRj, Runs
tempRunsInterval + ":" + tempRTs + ":" + //runsInterval, Reaction times
tempPulses + ":" + tempMCs + ":" + //pulses, MultiChronopic
tempEncS + ":" + tempEncC //encoder signal, encoder curve
);
}
}
return(arrayAll);
}
public static string[] SelectPulseTypes(string allPulsesName, bool onlyName)
{
//allPulsesName: add and "allPulsesName" value
//onlyName: return only type name
Sqlite.Open();
dbcmd.CommandText = "SELECT * " +
" FROM " + Constants.PulseTypeTable +
" ORDER BY uniqueID";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
ArrayList myArray = new ArrayList(2);
int count = new int();
count = 0;
while (reader.Read())
{
if (onlyName)
{
myArray.Add(reader[1].ToString());
}
else
{
myArray.Add(reader[0].ToString() + ":" + //uniqueID
reader[1].ToString() + ":" + //name
reader[2].ToString() + ":" + //fixedPulse
reader[3].ToString() + ":" + //totalPulsesNum
reader[4].ToString() //description
);
}
count++;
}
reader.Close();
Sqlite.Close();
int numRows;
if (allPulsesName != "")
{
numRows = count + 1;
}
else
{
numRows = count;
}
string [] myTypes = new string[numRows];
count = 0;
if (allPulsesName != "")
{
myTypes [count++] = allPulsesName;
//LogB.SQL("{0} - {1}", myTypes[count-1], count-1);
}
foreach (string line in myArray)
{
myTypes [count++] = line;
//LogB.SQL("{0} - {1}", myTypes[count-1], count-1);
}
return(myTypes);
}
public static Preferences SelectAll()
{
Sqlite.Open();
dbcmd.CommandText = "SELECT * FROM " + Constants.PreferencesTable;
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
Preferences preferences = new Preferences();
while (reader.Read())
{
//LogB.Debug("Reading preferences");
//LogB.Information(reader[0].ToString() + ":" + reader[1].ToString());
//these are sent to preferences window
if (reader[0].ToString() == "digitsNumber")
{
preferences.digitsNumber = Convert.ToInt32(reader[1].ToString());
}
else if (reader[0].ToString() == "showPower")
{
preferences.showPower = reader[1].ToString() == "True";
}
else if (reader[0].ToString() == "showStiffness")
{
preferences.showStiffness = reader[1].ToString() == "True";
}
else if (reader[0].ToString() == "showInitialSpeed")
{
preferences.showInitialSpeed = reader[1].ToString() == "True";
}
else if (reader[0].ToString() == "showAngle")
{
preferences.showAngle = reader[1].ToString() == "True";
}
else if (reader[0].ToString() == "showQIndex")
{
preferences.showQIndex = reader[1].ToString() == "True";
}
else if (reader[0].ToString() == "showDjIndex")
{
preferences.showDjIndex = reader[1].ToString() == "True";
}
else if (reader[0].ToString() == "askDeletion")
{
preferences.askDeletion = reader[1].ToString() == "True";
}
else if (reader[0].ToString() == "weightStatsPercent")
{
preferences.weightStatsPercent = reader[1].ToString() == "True";
}
else if (reader[0].ToString() == "heightPreferred")
{
preferences.heightPreferred = reader[1].ToString() == "True";
}
else if (reader[0].ToString() == "metersSecondsPreferred")
{
preferences.metersSecondsPreferred = reader[1].ToString() == "True";
}
//encoder capture
else if (reader[0].ToString() == "encoderCaptureTime")
{
preferences.encoderCaptureTime = Convert.ToInt32(reader[1].ToString());
}
else if (reader[0].ToString() == "encoderCaptureInactivityEndTime")
{
preferences.encoderCaptureInactivityEndTime = Convert.ToInt32(reader[1].ToString());
}
else if (reader[0].ToString() == "encoderCaptureMainVariable")
{
preferences.encoderCaptureMainVariable = (Constants.EncoderVariablesCapture)
Enum.Parse(typeof(Constants.EncoderVariablesCapture), reader[1].ToString());
}
else if (reader[0].ToString() == "encoderCaptureMinHeightGravitatory")
{
preferences.encoderCaptureMinHeightGravitatory = Convert.ToInt32(reader[1].ToString());
}
else if (reader[0].ToString() == "encoderCaptureMinHeightInertial")
{
preferences.encoderCaptureMinHeightInertial = Convert.ToInt32(reader[1].ToString());
}
else if (reader[0].ToString() == "encoderCaptureCheckFullyExtended")
{
preferences.encoderCaptureCheckFullyExtended = reader[1].ToString() == "True";
}
else if (reader[0].ToString() == "encoderCaptureCheckFullyExtendedValue")
{
preferences.encoderCaptureCheckFullyExtendedValue = Convert.ToInt32(reader[1].ToString());
}
else if (reader[0].ToString() == "encoderAutoSaveCurve")
{
preferences.encoderAutoSaveCurve = (Constants.EncoderAutoSaveCurve)
Enum.Parse(typeof(Constants.EncoderAutoSaveCurve), reader[1].ToString());
}
else if (reader[0].ToString() == "encoderShowStartAndDuration")
{
preferences.encoderShowStartAndDuration = reader[1].ToString() == "True";
}
//encoder other
else if (reader[0].ToString() == "encoderPropulsive")
{
preferences.encoderPropulsive = reader[1].ToString() == "True";
}
else if (reader[0].ToString() == "encoderSmoothCon")
{
preferences.encoderSmoothCon = Convert.ToDouble(
Util.ChangeDecimalSeparator(reader[1].ToString()));
}
else if (reader[0].ToString() == "encoder1RMMethod")
{
preferences.encoder1RMMethod = (Constants.Encoder1RMMethod)
Enum.Parse(typeof(Constants.Encoder1RMMethod), reader[1].ToString());
}
//video... other
else if (reader[0].ToString() == "videoDevice")
{
preferences.videoDeviceNum = Convert.ToInt32(reader[1].ToString());
}
else if (reader[0].ToString() == "CSVExportDecimalSeparator")
{
preferences.CSVExportDecimalSeparator = reader[1].ToString();
}
else if (reader[0].ToString() == "language")
{
preferences.language = reader[1].ToString();
}
else if (reader[0].ToString() == "RGraphsTranslate")
{
preferences.RGraphsTranslate = reader[1].ToString() == "True";
}
else if (reader[0].ToString() == "useHeightsOnJumpIndexes")
{
preferences.useHeightsOnJumpIndexes = reader[1].ToString() == "True";
}
//these are NOT sent to preferences window
else if (reader[0].ToString() == "allowFinishRjAfterTime")
{
preferences.allowFinishRjAfterTime = reader[1].ToString() == "True";
}
else if (reader[0].ToString() == "volumeOn")
{
preferences.volumeOn = reader[1].ToString() == "True";
}
else if (reader[0].ToString() == "videoOn")
{
preferences.videoOn = reader[1].ToString() == "True";
}
else if (reader[0].ToString() == "evaluatorServerID")
{
preferences.evaluatorServerID = Convert.ToInt32(reader[1].ToString());
}
else if (reader[0].ToString() == "versionAvailable")
{
preferences.versionAvailable = reader[1].ToString();
}
else if (reader[0].ToString() == "runSpeedStartArrival")
{
preferences.runSpeedStartArrival = reader[1].ToString() == "True";
}
else if (reader[0].ToString() == "runDoubleContactsMode")
{
preferences.runDoubleContactsMode = (Constants.DoubleContact)
Enum.Parse(typeof(Constants.DoubleContact), reader[1].ToString());
}
else if (reader[0].ToString() == "runDoubleContactsMS")
{
preferences.runDoubleContactsMS = Convert.ToInt32(reader[1].ToString());
}
else if (reader[0].ToString() == "runIDoubleContactsMode")
{
preferences.runIDoubleContactsMode = (Constants.DoubleContact)
Enum.Parse(typeof(Constants.DoubleContact), reader[1].ToString());
}
else if (reader[0].ToString() == "runIDoubleContactsMS")
{
preferences.runIDoubleContactsMS = Convert.ToInt32(reader[1].ToString());
}
else if (reader[0].ToString() == "thresholdJumps")
{
preferences.thresholdJumps = Convert.ToInt32(reader[1].ToString());
}
else if (reader[0].ToString() == "thresholdRuns")
{
preferences.thresholdRuns = Convert.ToInt32(reader[1].ToString());
}
else if (reader[0].ToString() == "thresholdOther")
{
preferences.thresholdOther = Convert.ToInt32(reader[1].ToString());
}
else if (reader[0].ToString() == "machineID")
{
preferences.machineID = reader[1].ToString();
}
else if (reader[0].ToString() == "multimediaStorage")
{
preferences.multimediaStorage = (Constants.MultimediaStorage)
Enum.Parse(typeof(Constants.MultimediaStorage), reader[1].ToString());
}
else if (reader[0].ToString() == "databaseVersion")
{
preferences.databaseVersion = reader[1].ToString();
}
}
reader.Close();
Sqlite.Close();
return(preferences);
}
public static string[] SelectRuns(bool dbconOpened, int sessionID, int personID, string filterType)
{
if (!dbconOpened)
{
Sqlite.Open();
}
string tp = Constants.PersonTable;
string filterSessionString = "";
if (sessionID != -1)
{
filterSessionString = " AND runInterval.sessionID == " + sessionID;
}
string filterPersonString = "";
if (personID != -1)
{
filterPersonString = " AND " + tp + ".uniqueID == " + personID;
}
string filterTypeString = "";
if (filterType != "")
{
filterTypeString = " AND runInterval.type == \"" + filterType + "\" ";
}
dbcmd.CommandText = "SELECT " + tp + ".name, runInterval.* " +
" FROM " + tp + ", runInterval " +
" WHERE " + tp + ".uniqueID == runInterval.personID" +
filterSessionString +
filterPersonString +
filterTypeString +
" ORDER BY upper(" + tp + ".name), runInterval.uniqueID";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
ArrayList myArray = new ArrayList(2);
int count = new int();
count = 0;
while (reader.Read())
{
myArray.Add(reader[0].ToString() + ":" + //person.name
reader[1].ToString() + ":" + //runInterval.uniqueID
reader[2].ToString() + ":" + //runInterval.personID
reader[3].ToString() + ":" + //runInterval.sessionID
reader[4].ToString() + ":" + //runInterval.type
Util.ChangeDecimalSeparator(reader[5].ToString()) + ":" + //distanceTotal
Util.ChangeDecimalSeparator(reader[6].ToString()) + ":" + //timeTotal
Util.ChangeDecimalSeparator(reader[7].ToString()) + ":" + //distanceInterval
Util.ChangeDecimalSeparator(reader[8].ToString()) + ":" + //intervalTimesString
Util.ChangeDecimalSeparator(reader[9].ToString()) + ":" + //tracks
reader[10].ToString() + ":" + //description
reader[11].ToString() + ":" + //limited
reader[12].ToString() + ":" + //simulated
Util.IntToBool(Convert.ToInt32(reader[13])) //initialSpeed
);
count++;
}
reader.Close();
if (!dbconOpened)
{
Sqlite.Close();
}
string [] myRuns = new string[count];
count = 0;
foreach (string line in myArray)
{
myRuns [count++] = line;
}
return(myRuns);
}
public static ArrayList Select(bool dbconOpened, int uniqueID, bool onlyNames)
{
if (!dbconOpened)
{
Sqlite.Open();
}
string uniqueIDStr = "";
if (uniqueID != -1)
{
uniqueIDStr = " WHERE " + table + ".uniqueID = " + uniqueID;
}
if (onlyNames)
{
dbcmd.CommandText = "SELECT name FROM " + table + uniqueIDStr;
}
else
{
dbcmd.CommandText = "SELECT * FROM " + table + uniqueIDStr;
}
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
ArrayList array = new ArrayList(1);
ForceSensorExercise ex = new ForceSensorExercise();
if (onlyNames)
{
while (reader.Read())
{
ex = new ForceSensorExercise(reader[0].ToString());
array.Add(ex);
}
}
else
{
while (reader.Read())
{
int angleDefault = 0;
ex = new ForceSensorExercise(
Convert.ToInt32(reader[0].ToString()), //uniqueID
reader[1].ToString(), //name
Convert.ToInt32(reader[2].ToString()), //percentBodyWeight
reader[3].ToString(), //resistance
angleDefault,
reader[5].ToString() //description
);
array.Add(ex);
}
}
reader.Close();
if (!dbconOpened)
{
Sqlite.Close();
}
return(array);
}
public static double selectEventsOfAType(bool dbconOpened, int sessionID, int personID,
string table, string type, string valueToSelect, string statistic)
{
if (!dbconOpened)
{
Sqlite.Open();
}
//if personIDString == -1, the applies for all persons
string personIDString = "";
if (personID != -1)
{
personIDString = " AND personID = " + personID;
}
string sessionIDString = "";
if (sessionID != -1)
{
sessionIDString = " AND sessionID = " + sessionID;
}
dbcmd.CommandText = "SELECT " + statistic + "(" + valueToSelect + ")" +
" FROM " + table +
" WHERE type = \"" + type + "\" " +
personIDString +
sessionIDString;
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
double myReturn = 0;
bool found = false;
if (reader.Read())
{
found = true;
myReturn = Convert.ToDouble(Util.ChangeDecimalSeparator(reader[0].ToString()));
}
reader.Close();
if (!dbconOpened)
{
Sqlite.Close();
}
if (found)
{
return(myReturn);
}
else
{
return(0);
}
}
public static void DeleteAllStuff(string uniqueID)
{
Sqlite.Open();
//delete the session
dbcmd.CommandText = "Delete FROM " + Constants.SessionTable + " WHERE uniqueID == " + uniqueID;
dbcmd.ExecuteNonQuery();
//delete relations (existance) within persons and sessions in this session
dbcmd.CommandText = "Delete FROM " + Constants.PersonSessionTable + " WHERE sessionID == " + uniqueID;
dbcmd.ExecuteNonQuery();
Sqlite.deleteOrphanedPersons();
//delete normal jumps
dbcmd.CommandText = "Delete FROM " + Constants.JumpTable + " WHERE sessionID == " + uniqueID;
dbcmd.ExecuteNonQuery();
//delete repetitive jumps
dbcmd.CommandText = "Delete FROM " + Constants.JumpRjTable + " WHERE sessionID == " + uniqueID;
dbcmd.ExecuteNonQuery();
//delete normal runs
dbcmd.CommandText = "Delete FROM " + Constants.RunTable + " WHERE sessionID == " + uniqueID;
dbcmd.ExecuteNonQuery();
//delete intervallic runs
dbcmd.CommandText = "Delete FROM " + Constants.RunIntervalTable + " WHERE sessionID == " + uniqueID;
dbcmd.ExecuteNonQuery();
//delete reaction times
dbcmd.CommandText = "Delete FROM " + Constants.ReactionTimeTable + " WHERE sessionID == " + uniqueID;
dbcmd.ExecuteNonQuery();
//delete pulses
dbcmd.CommandText = "Delete FROM " + Constants.PulseTable + " WHERE sessionID == " + uniqueID;
dbcmd.ExecuteNonQuery();
//delete multiChronopic
dbcmd.CommandText = "Delete FROM " + Constants.MultiChronopicTable + " WHERE sessionID == " + uniqueID;
dbcmd.ExecuteNonQuery();
//delete from encoder start ------>
//signals
ArrayList encoderArray = SqliteEncoder.Select(
true, -1, -1, Convert.ToInt32(uniqueID), Constants.EncoderGI.ALL,
-1, "signal", EncoderSQL.Eccons.ALL,
false, true);
foreach (EncoderSQL eSQL in encoderArray)
{
Util.FileDelete(eSQL.GetFullURL(false)); //signal, don't convertPathToR
if (eSQL.videoURL != "")
{
Util.FileDelete(eSQL.videoURL); //video
}
Sqlite.Delete(true, Constants.EncoderTable, Convert.ToInt32(eSQL.uniqueID));
}
//curves
encoderArray = SqliteEncoder.Select(
true, -1, -1, Convert.ToInt32(uniqueID), Constants.EncoderGI.ALL,
-1, "curve", EncoderSQL.Eccons.ALL,
false, true);
foreach (EncoderSQL eSQL in encoderArray)
{
Util.FileDelete(eSQL.GetFullURL(false)); //don't convertPathToR
/* commented: curve has no video
* if(eSQL.videoURL != "")
* Util.FileDelete(eSQL.videoURL);
*/
Sqlite.Delete(true, Constants.EncoderTable, Convert.ToInt32(eSQL.uniqueID));
SqliteEncoder.DeleteSignalCurveWithCurveID(true, Convert.ToInt32(eSQL.uniqueID));
}
//<------- delete from encoder end
Sqlite.Close();
}
//pass uniqueID value and then will return one record. do like this:
//EncoderSQL eSQL = (EncoderSQL) SqliteEncoder.Select(false, myUniqueID, 0, 0, 0, "", EncoderSQL.Eccons.ALL, false, true)[0];
//WARNING because SqliteEncoder.Select may not return nothing, and then cannot be assigned to eSQL
//see: delete_encoder_curve(bool dbconOpened, int uniqueID)
//don't care for the 0, 0, 0 because selection will be based on the myUniqueID and only one row will be returned
//or
//pass uniqueID==-1 and personID, sessionID, signalOrCurve values, and will return some records
//personID can be -1 to get all on that session
//sessionID can be -1 to get all sessions
//exerciseID can be -1 to get all exercises
//signalOrCurve can be "all"
//orderIDascendent is good for all the situations except when we want to convert from 1.05 to 1.06
//in that conversion, we want first the last ones, and later the previous
// (to delete them if they are old copies)
public static ArrayList Select(
bool dbconOpened, int uniqueID, int personID, int sessionID, Constants.EncoderGI encoderGI,
int exerciseID, string signalOrCurve, EncoderSQL.Eccons ecconSelect,
bool onlyActive, bool orderIDascendent)
{
if (!dbconOpened)
{
Sqlite.Open();
}
string personIDStr = "";
if (personID != -1)
{
personIDStr = " personID = " + personID + " AND ";
}
string sessionIDStr = "";
if (sessionID != -1)
{
sessionIDStr = " sessionID = " + sessionID + " AND ";
}
string exerciseIDStr = "";
if (exerciseID != -1)
{
exerciseIDStr = " exerciseID = " + exerciseID + " AND ";
}
string selectStr = "";
if (uniqueID != -1)
{
selectStr = Constants.EncoderTable + ".uniqueID = " + uniqueID;
}
else
{
if (signalOrCurve == "all")
{
selectStr = personIDStr + sessionIDStr + exerciseIDStr;
}
else
{
selectStr = personIDStr + sessionIDStr + exerciseIDStr + " signalOrCurve = \"" + signalOrCurve + "\"";
}
if (ecconSelect != EncoderSQL.Eccons.ALL)
{
selectStr += " AND " + Constants.EncoderTable + ".eccon = \"" + EncoderSQL.Eccons.ecS.ToString() + "\"";
}
}
string andString = "";
if (selectStr != "")
{
andString = " AND ";
}
string onlyActiveString = "";
if (onlyActive)
{
onlyActiveString = " AND " + Constants.EncoderTable + ".status = \"active\" ";
}
string orderIDstr = "";
if (!orderIDascendent)
{
orderIDstr = " DESC";
}
dbcmd.CommandText = "SELECT " +
Constants.EncoderTable + ".*, " + Constants.EncoderExerciseTable + ".name FROM " +
Constants.EncoderTable + ", " + Constants.EncoderExerciseTable +
" WHERE " + selectStr +
andString + Constants.EncoderTable + ".exerciseID = " +
Constants.EncoderExerciseTable + ".uniqueID " +
onlyActiveString +
" ORDER BY substr(filename,-23,19), " + //'filename,-23,19' has the date of capture signal
"uniqueID " + orderIDstr;
LogB.SQL(dbcmd.CommandText.ToString());
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
ArrayList array = new ArrayList(1);
EncoderSQL es = new EncoderSQL();
while (reader.Read())
{
string [] strFull = reader[15].ToString().Split(new char[] { ':' });
EncoderConfiguration econf = new EncoderConfiguration(
(Constants.EncoderConfigurationNames)
Enum.Parse(typeof(Constants.EncoderConfigurationNames), strFull[0]));
econf.ReadParamsFromSQL(strFull);
//if encoderGI != ALL discard non wanted repetitions
if (encoderGI == Constants.EncoderGI.GRAVITATORY && econf.has_inertia)
{
continue;
}
else if (encoderGI == Constants.EncoderGI.INERTIAL && !econf.has_inertia)
{
continue;
}
LogB.Debug("EncoderConfiguration = " + econf.ToStringOutput(EncoderConfiguration.Outputs.SQL));
//if there's no video, will be "".
//if there's video, will be with full path
string videoURL = "";
if (reader[14].ToString() != "")
{
videoURL = addURLpath(fixOSpath(reader[14].ToString()));
}
//LogB.SQL(econf.ToString(":", true));
es = new EncoderSQL(
reader[0].ToString(), //uniqueID
Convert.ToInt32(reader[1].ToString()), //personID
Convert.ToInt32(reader[2].ToString()), //sessionID
Convert.ToInt32(reader[3].ToString()), //exerciseID
reader[4].ToString(), //eccon
Catalog.GetString(reader[5].ToString()), //laterality
reader[6].ToString(), //extraWeight
reader[7].ToString(), //signalOrCurve
reader[8].ToString(), //filename
addURLpath(fixOSpath(reader[9].ToString())), //url
Convert.ToInt32(reader[10].ToString()), //time
Convert.ToInt32(reader[11].ToString()), //minHeight
reader[12].ToString(), //description
reader[13].ToString(), //status
videoURL, //videoURL
econf, //encoderConfiguration
Util.ChangeDecimalSeparator(reader[16].ToString()), //future1 (meanPower on curves)
reader[17].ToString(), //future2
reader[18].ToString(), //future3
reader[19].ToString() //EncoderExercise.name
);
array.Add(es);
}
reader.Close();
if (!dbconOpened)
{
Sqlite.Close();
}
return(array);
}
//exerciseID can be -1 to get all exercises
public static ArrayList SelectCompareIntersession(bool dbconOpened, Constants.EncoderGI encoderGI, int exerciseID, int personID)
{
if (!dbconOpened)
{
Sqlite.Open();
}
string exerciseIDStr = "";
if (exerciseID != -1)
{
exerciseIDStr = "encoder.exerciseID = " + exerciseID + " AND ";
}
//returns a row for each session where there are active or inactive
dbcmd.CommandText =
"SELECT encoder.sessionID, session.name, session.date, encoder.extraWeight, " +
" SUM(CASE WHEN encoder.status = \"active\" THEN 1 END) as active, " +
" SUM(CASE WHEN encoder.status = \"inactive\" THEN 1 END) as inactive," +
" encoder.encoderConfiguration " +
" FROM encoder, session, person77 " +
" WHERE " +
exerciseIDStr +
" encoder.personID = " + personID + " AND signalOrCurve = \"curve\" AND " +
" encoder.personID = person77.uniqueID AND encoder.sessionID = session.uniqueID " +
" GROUP BY encoder.sessionID, encoder.extraWeight ORDER BY encoder.sessionID, encoder.extraWeight, encoder.status";
LogB.SQL(dbcmd.CommandText.ToString());
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
ArrayList array = new ArrayList();
EncoderPersonCurvesInDB encPS = new EncoderPersonCurvesInDB();
/*
* eg.
* sessID|sess name|date|extraWe|a|i (a: active, i: inactive)
* 20|Encoder tests|2012-12-10|7|3|
* 20|Encoder tests|2012-12-10|0||9
* 20|Encoder tests|2012-12-10|10||34
* 20|Encoder tests|2012-12-10|58||1
* 20|Encoder tests|2012-12-10|61||1
* 26|sessio-proves|2013-07-08|10|5|36
* 30|proves encoder|2013-11-08|0|2|
* 30|proves encoder|2013-11-08|100|5|
*
* convert to:
*
* sessID|sess name|date|a|i|reps*weights (a: active, i: inactive)
* 20|Encoder tests|2012-12-10|3|45|3*7 9*0 34*10 1*58 1*61 (but sorted)
*
*/
int sessIDDoing = -1; //of this sessionID
int sessIDThisRow = -1; //of this SQL row
List <EncoderPersonCurvesInDBDeep> lDeep = new List <EncoderPersonCurvesInDBDeep>();
bool firstSession = true;
int activeThisRow;
int inactiveThisRow;
int activeThisSession = 0;
int inactiveThisSession = 0;
while (reader.Read())
{
//discard if != encoderGI
string [] strFull = reader[6].ToString().Split(new char[] { ':' });
EncoderConfiguration econf = new EncoderConfiguration(
(Constants.EncoderConfigurationNames)
Enum.Parse(typeof(Constants.EncoderConfigurationNames), strFull[0]));
//if encoderGI != ALL discard non wanted repetitions
if (encoderGI == Constants.EncoderGI.GRAVITATORY && econf.has_inertia)
{
continue;
}
else if (encoderGI == Constants.EncoderGI.INERTIAL && !econf.has_inertia)
{
continue;
}
//1 get sessionID of this row
sessIDThisRow = Convert.ToInt32(reader[0].ToString());
//2 get active an inactive curves of this row
activeThisRow = 0;
string activeStr = reader[4].ToString();
if (Util.IsNumber(activeStr, false))
{
activeThisRow = Convert.ToInt32(activeStr);
}
inactiveThisRow = 0;
string inactiveStr = reader[5].ToString();
if (Util.IsNumber(inactiveStr, false))
{
inactiveThisRow = Convert.ToInt32(inactiveStr);
}
//3 if session of this row is different than previous row
if (sessIDThisRow != sessIDDoing)
{
sessIDDoing = sessIDThisRow;
if (!firstSession)
{
//if is not first session (means we have processed a session before)
//update encPS with the lDeep and then add to array
encPS.lDeep = lDeep;
encPS.countActive = activeThisSession;
encPS.countAll = activeThisSession + inactiveThisSession;
array.Add(encPS);
}
firstSession = false;
//create new EncoderPersonCurvesInDB
encPS = new EncoderPersonCurvesInDB(
personID,
Convert.ToInt32(reader[0].ToString()), //sessionID
reader[1].ToString(), //sessionName
reader[2].ToString()); //sessionDate
activeThisSession = 0;
inactiveThisSession = 0;
//empty lDeep
lDeep = new List <EncoderPersonCurvesInDBDeep>();
}
//4 add deep info: (weight, all reps)
EncoderPersonCurvesInDBDeep deep = new EncoderPersonCurvesInDBDeep(
Convert.ToDouble(Util.ChangeDecimalSeparator(reader[3].ToString())), activeThisRow + inactiveThisRow);
//add to lDeep
lDeep.Add(deep);
activeThisSession += activeThisRow;
inactiveThisSession += inactiveThisRow;
}
//store last row in array (once we are out the while)
if (!firstSession)
{
//if is not first session (means we have processed a session before)
//update encPS with the lDeep and then add to array
encPS.lDeep = lDeep;
encPS.countActive = activeThisSession;
encPS.countAll = activeThisSession + inactiveThisSession;
array.Add(encPS);
}
reader.Close();
if (!dbconOpened)
{
Sqlite.Close();
}
return(array);
}
//signalID == -1 (any signal)
//curveID == -1 (any curve)
//if msStart and msEnd != -1 (means find a curve with msCentral contained between both values)
public static ArrayList SelectSignalCurve(bool dbconOpened, int signalID, int curveID, double msStart, double msEnd)
{
if (!dbconOpened)
{
Sqlite.Open();
}
string whereStr = "";
if (signalID != -1 || curveID != -1 || msStart != -1)
{
whereStr = " WHERE ";
}
string signalIDstr = "";
if (signalID != -1)
{
signalIDstr = " signalID == " + signalID;
}
string curveIDstr = "";
if (curveID != -1)
{
curveIDstr = " curveID == " + curveID;
if (signalID != -1)
{
curveIDstr = " AND" + curveIDstr;
}
}
string msCentralstr = "";
if (msStart != -1)
{
msCentralstr = " msCentral >= " + Util.ConvertToPoint(msStart) + " AND msCentral <= " + Util.ConvertToPoint(msEnd);
if (signalID != -1 || curveID != -1)
{
msCentralstr = " AND" + msCentralstr;
}
}
dbcmd.CommandText =
"SELECT uniqueID, signalID, curveID, msCentral " +
" FROM " + Constants.EncoderSignalCurveTable +
whereStr + signalIDstr + curveIDstr + msCentralstr;
LogB.SQL(dbcmd.CommandText.ToString());
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
ArrayList array = new ArrayList();
while (reader.Read())
{
EncoderSignalCurve esc = new EncoderSignalCurve(
Convert.ToInt32(reader[0].ToString()),
Convert.ToInt32(reader[1].ToString()),
Convert.ToInt32(reader[2].ToString()),
Convert.ToInt32(reader[3].ToString()));
array.Add(esc);
}
reader.Close();
if (!dbconOpened)
{
Sqlite.Close();
}
return(array);
}
public static string[] SelectAllSessions(string filterName)
{
Sqlite.Open();
string filterNameString = "";
if (filterName != "")
{
filterNameString = " AND LOWER(session.name) LIKE LOWER (\"%" + filterName + "%\") ";
}
dbcmd.CommandText =
"SELECT session.*, sport.name, speciallity.name" +
" FROM session, sport, speciallity " +
" WHERE session.personsSportID == sport.uniqueID " +
" AND session.personsSpeciallityID == speciallity.UniqueID " +
filterNameString +
" ORDER BY session.uniqueID";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
ArrayList myArray = new ArrayList(2);
int count = new int();
count = 0;
while (reader.Read())
{
string sportName = Catalog.GetString(reader[9].ToString());
string speciallityName = ""; //to solve a gettext bug (probably because speciallity undefined name is "")
if (reader[10].ToString() != "")
{
speciallityName = Catalog.GetString(reader[10].ToString());
}
string levelName = Catalog.GetString(Util.FindLevelName(Convert.ToInt32(reader[6])));
myArray.Add(reader[0].ToString() + ":" + reader[1].ToString() + ":" +
reader[2].ToString() + ":" + UtilDate.FromSql(reader[3].ToString()).ToShortDateString() + ":" +
sportName + ":" + speciallityName + ":" +
levelName + ":" + reader[7].ToString()); //desc
count++;
}
reader.Close();
/* FIXME:
* all this thing it's because if someone has createds sessions without jumps or jumpers,
* and we make a GROUP BY selection, this sessions doesn't appear as results
* in the near future, learn better sqlite for solving this in a nicer way
* */
/* another solution is not show nothing about jumpers and jumps, but show a button of "details"
* this will open a new window showing this values.
* this solution it's more "lighter" for people who have abig DB
* */
//select persons of each session
dbcmd.CommandText = "SELECT sessionID, count(*) FROM " + Constants.PersonSessionTable +
" GROUP BY sessionID ORDER BY sessionID";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader_persons;
reader_persons = dbcmd.ExecuteReader();
ArrayList myArray_persons = new ArrayList(2);
while (reader_persons.Read())
{
myArray_persons.Add(reader_persons[0].ToString() + ":" + reader_persons[1].ToString() + ":");
}
reader_persons.Close();
//select jumps of each session
dbcmd.CommandText = "SELECT sessionID, count(*) FROM " + Constants.JumpTable +
" GROUP BY sessionID ORDER BY sessionID";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader_jumps;
reader_jumps = dbcmd.ExecuteReader();
ArrayList myArray_jumps = new ArrayList(2);
while (reader_jumps.Read())
{
myArray_jumps.Add(reader_jumps[0].ToString() + ":" + reader_jumps[1].ToString() + ":");
}
reader_jumps.Close();
//select jumpsRj of each session
dbcmd.CommandText = "SELECT sessionID, count(*) FROM " + Constants.JumpRjTable +
" GROUP BY sessionID ORDER BY sessionID";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader_jumpsRj;
reader_jumpsRj = dbcmd.ExecuteReader();
ArrayList myArray_jumpsRj = new ArrayList(2);
while (reader_jumpsRj.Read())
{
myArray_jumpsRj.Add(reader_jumpsRj[0].ToString() + ":" + reader_jumpsRj[1].ToString() + ":");
}
reader_jumpsRj.Close();
//select runs of each session
dbcmd.CommandText = "SELECT sessionID, count(*) FROM " + Constants.RunTable +
" GROUP BY sessionID ORDER BY sessionID";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader_runs;
reader_runs = dbcmd.ExecuteReader();
ArrayList myArray_runs = new ArrayList(2);
while (reader_runs.Read())
{
myArray_runs.Add(reader_runs[0].ToString() + ":" + reader_runs[1].ToString() + ":");
}
reader_runs.Close();
//select runsInterval of each session
dbcmd.CommandText = "SELECT sessionID, count(*) FROM " + Constants.RunIntervalTable +
" GROUP BY sessionID ORDER BY sessionID";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader_runs_interval;
reader_runs_interval = dbcmd.ExecuteReader();
ArrayList myArray_runs_interval = new ArrayList(2);
while (reader_runs_interval.Read())
{
myArray_runs_interval.Add(reader_runs_interval[0].ToString() + ":" + reader_runs_interval[1].ToString() + ":");
}
reader_runs_interval.Close();
//select reaction time of each session
dbcmd.CommandText = "SELECT sessionID, count(*) FROM " + Constants.ReactionTimeTable +
" GROUP BY sessionID ORDER BY sessionID";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader_rt;
reader_rt = dbcmd.ExecuteReader();
ArrayList myArray_rt = new ArrayList(2);
while (reader_rt.Read())
{
myArray_rt.Add(reader_rt[0].ToString() + ":" + reader_rt[1].ToString() + ":");
}
reader_rt.Close();
//select pulses of each session
dbcmd.CommandText = "SELECT sessionID, count(*) FROM " + Constants.PulseTable +
" GROUP BY sessionID ORDER BY sessionID";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader_pulses;
reader_pulses = dbcmd.ExecuteReader();
ArrayList myArray_pulses = new ArrayList(2);
while (reader_pulses.Read())
{
myArray_pulses.Add(reader_pulses[0].ToString() + ":" + reader_pulses[1].ToString() + ":");
}
reader_pulses.Close();
//select multichronopic of each session
dbcmd.CommandText = "SELECT sessionID, count(*) FROM " + Constants.MultiChronopicTable +
" GROUP BY sessionID ORDER BY sessionID";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader_mcs;
reader_mcs = dbcmd.ExecuteReader();
ArrayList myArray_mcs = new ArrayList(2);
while (reader_mcs.Read())
{
myArray_mcs.Add(reader_mcs[0].ToString() + ":" + reader_mcs[1].ToString() + ":");
}
reader_mcs.Close();
//select encoder signal of each session
dbcmd.CommandText = "SELECT sessionID, count(*) FROM " + Constants.EncoderTable +
" WHERE signalOrCurve == \"signal\" GROUP BY sessionID ORDER BY sessionID";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader_enc_s;
reader_enc_s = dbcmd.ExecuteReader();
ArrayList myArray_enc_s = new ArrayList(2);
while (reader_enc_s.Read())
{
myArray_enc_s.Add(reader_enc_s[0].ToString() + ":" + reader_enc_s[1].ToString() + ":");
}
reader_enc_s.Close();
//select encoder curve of each session
dbcmd.CommandText = "SELECT sessionID, count(*) FROM " + Constants.EncoderTable +
" WHERE signalOrCurve == \"curve\" GROUP BY sessionID ORDER BY sessionID";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader_enc_c;
reader_enc_c = dbcmd.ExecuteReader();
ArrayList myArray_enc_c = new ArrayList(2);
while (reader_enc_c.Read())
{
myArray_enc_c.Add(reader_enc_c[0].ToString() + ":" + reader_enc_c[1].ToString() + ":");
}
reader_enc_c.Close();
//close database connection
Sqlite.Close();
//mix seven arrayLists
string [] mySessions = new string[count];
count = 0;
bool found;
foreach (string line in myArray)
{
string lineNotReadOnly = line;
//if some sessions are deleted, do not use count=0 to mix arrays, use sessionID of line
string [] mixingSessionFull = line.Split(new char[] { ':' });
string mixingSessionID = mixingSessionFull[0];
//add persons for each session
found = false;
foreach (string line_persons in myArray_persons)
{
string [] myStringFull = line_persons.Split(new char[] { ':' });
if (myStringFull[0] == mixingSessionID)
{
lineNotReadOnly = lineNotReadOnly + ":" + myStringFull[1];
found = true;
}
}
if (!found)
{
lineNotReadOnly = lineNotReadOnly + ":0";
}
//add jumps for each session
found = false;
foreach (string line_jumps in myArray_jumps)
{
string [] myStringFull = line_jumps.Split(new char[] { ':' });
if (myStringFull[0] == mixingSessionID)
{
lineNotReadOnly = lineNotReadOnly + ":" + myStringFull[1];
found = true;
}
}
if (!found)
{
lineNotReadOnly = lineNotReadOnly + ":0";
}
//add jumpsRj for each session
found = false;
foreach (string line_jumpsRj in myArray_jumpsRj)
{
string [] myStringFull = line_jumpsRj.Split(new char[] { ':' });
if (myStringFull[0] == mixingSessionID)
{
lineNotReadOnly = lineNotReadOnly + ":" + myStringFull[1];
found = true;
}
}
if (!found)
{
lineNotReadOnly = lineNotReadOnly + ":0";
}
//add runs for each session
found = false;
foreach (string line_runs in myArray_runs)
{
string [] myStringFull = line_runs.Split(new char[] { ':' });
if (myStringFull[0] == mixingSessionID)
{
lineNotReadOnly = lineNotReadOnly + ":" + myStringFull[1];
found = true;
}
}
if (!found)
{
lineNotReadOnly = lineNotReadOnly + ":0";
}
//add runsInterval for each session
found = false;
foreach (string line_runs_interval in myArray_runs_interval)
{
string [] myStringFull = line_runs_interval.Split(new char[] { ':' });
if (myStringFull[0] == mixingSessionID)
{
lineNotReadOnly = lineNotReadOnly + ":" + myStringFull[1];
found = true;
}
}
if (!found)
{
lineNotReadOnly = lineNotReadOnly + ":0";
}
//add reaction time for each session
found = false;
foreach (string line_rt in myArray_rt)
{
string [] myStringFull = line_rt.Split(new char[] { ':' });
if (myStringFull[0] == mixingSessionID)
{
lineNotReadOnly = lineNotReadOnly + ":" + myStringFull[1];
found = true;
}
}
if (!found)
{
lineNotReadOnly = lineNotReadOnly + ":0";
}
//add pulses for each session
found = false;
foreach (string line_pulses in myArray_pulses)
{
string [] myStringFull = line_pulses.Split(new char[] { ':' });
if (myStringFull[0] == mixingSessionID)
{
lineNotReadOnly = lineNotReadOnly + ":" + myStringFull[1];
found = true;
}
}
if (!found)
{
lineNotReadOnly = lineNotReadOnly + ":0";
}
//add multiChronopic for each session
found = false;
foreach (string line_mcs in myArray_mcs)
{
string [] myStringFull = line_mcs.Split(new char[] { ':' });
if (myStringFull[0] == mixingSessionID)
{
lineNotReadOnly = lineNotReadOnly + ":" + myStringFull[1];
found = true;
}
}
if (!found)
{
lineNotReadOnly = lineNotReadOnly + ":0";
}
//add encoder signal for each session
found = false;
foreach (string line_enc_s in myArray_enc_s)
{
string [] myStringFull = line_enc_s.Split(new char[] { ':' });
if (myStringFull[0] == mixingSessionID)
{
lineNotReadOnly = lineNotReadOnly + ":" + myStringFull[1];
found = true;
}
}
if (!found)
{
lineNotReadOnly = lineNotReadOnly + ":0";
}
//add encoder curve for each session
found = false;
foreach (string line_enc_c in myArray_enc_c)
{
string [] myStringFull = line_enc_c.Split(new char[] { ':' });
if (myStringFull[0] == mixingSessionID)
{
lineNotReadOnly = lineNotReadOnly + ":" + myStringFull[1];
found = true;
}
}
if (!found)
{
lineNotReadOnly = lineNotReadOnly + ":0";
}
mySessions [count++] = lineNotReadOnly;
}
return(mySessions);
}
public static string[] SelectJumps(bool dbconOpened, int sessionID, int personID, string filterWeight, string filterType)
{
if (!dbconOpened)
{
Sqlite.Open();
}
string tp = Constants.PersonTable;
string tps = Constants.PersonSessionTable;
string filterSessionString = "";
if (sessionID != -1)
{
filterSessionString = " AND jumpRj.sessionID == " + sessionID;
}
string filterPersonString = "";
if (personID != -1)
{
filterPersonString = " AND " + tp + ".uniqueID == " + personID;
}
string filterWeightString = "";
if (filterWeight == "withWeight")
{
filterWeightString = " AND jumpRj.weight != 0 ";
}
string filterTypeString = "";
if (filterType != "")
{
filterTypeString = " AND jumpRj.type == \"" + filterType + "\" ";
}
dbcmd.CommandText = "SELECT " + tp + ".name, jumpRj.*, " + tps + ".weight " +
" FROM " + tp + ", jumpRj, " + tps + " " +
" WHERE " + tp + ".uniqueID == jumpRj.personID" +
filterSessionString +
filterPersonString +
filterWeightString +
filterTypeString +
" AND " + tps + ".personID == " + tp + ".uniqueID " +
" AND " + tps + ".sessionID == jumpRj.sessionID " +
" ORDER BY upper(" + tp + ".name), jumpRj.uniqueID";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
ArrayList myArray = new ArrayList(2);
int count = new int();
count = 0;
while (reader.Read())
{
myArray.Add(reader[0].ToString() + ":" + //person.name
reader[1].ToString() + ":" + //jumpRj.uniqueID
reader[2].ToString() + ":" + //jumpRj.personID
reader[3].ToString() + ":" + //jumpRj.sessionID
reader[4].ToString() + ":" + //jumpRj.type
Util.ChangeDecimalSeparator(reader[5].ToString()) + ":" + //tvMax
Util.ChangeDecimalSeparator(reader[6].ToString()) + ":" + //tcMax
Util.ChangeDecimalSeparator(reader[7].ToString()) + ":" + //fall
Util.ChangeDecimalSeparator(reader[8].ToString()) + ":" + //weight
reader[9].ToString() + ":" + //description
Util.ChangeDecimalSeparator(reader[10].ToString()) + ":" + //tvAvg,
Util.ChangeDecimalSeparator(reader[11].ToString()) + ":" + //tcAvg,
Util.ChangeDecimalSeparator(reader[12].ToString()) + ":" + //tvString,
Util.ChangeDecimalSeparator(reader[13].ToString()) + ":" + //tcString,
reader[14].ToString() + ":" + //jumps,
reader[15].ToString() + ":" + //time,
reader[16].ToString() + ":" + //limited
reader[17].ToString() + ":" + //angleString
reader[18].ToString() + ":" + //simulated
reader[19].ToString() //person.weight
);
count++;
}
reader.Close();
if (!dbconOpened)
{
Sqlite.Close();
}
string [] myJumps = new string[count];
count = 0;
foreach (string line in myArray)
{
myJumps [count++] = line;
}
return(myJumps);
}
