public static Session SelectDo(SqliteCommand mydbcmd)
{
try {
Sqlite.Open();
} catch {
//done because there's an eventual problem maybe thread related on very few starts of chronojump
LogB.SQL("Catched dbcon problem at Session.Select");
Sqlite.Close();
Sqlite.Open();
LogB.SQL("reopened again");
}
LogB.SQL(mydbcmd.CommandText.ToString());
SqliteDataReader reader;
reader = mydbcmd.ExecuteReader();
string [] values = new string[9];
while (reader.Read())
{
values[0] = reader[0].ToString();
values[1] = reader[1].ToString();
values[2] = reader[2].ToString();
values[3] = reader[3].ToString();
values[4] = reader[4].ToString();
values[5] = reader[5].ToString();
values[6] = reader[6].ToString();
values[7] = reader[7].ToString();
values[8] = reader[8].ToString();
}
Session mySession = new Session(values[0],
values[1], values[2], UtilDate.FromSql(values[3]),
Convert.ToInt32(values[4]), Convert.ToInt32(values[5]), Convert.ToInt32(values[6]),
values[7], Convert.ToInt32(values[8]));
reader.Close();
Sqlite.Close();
return(mySession);
}
public static Person Select(bool dbconOpened, string whereStr)
{
if (!dbconOpened)
{
Sqlite.Open();
}
dbcmd.CommandText = "SELECT * FROM " + Constants.PersonTable + " " + whereStr;
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
Person p = new Person(-1);
if (reader.Read())
{
p = new Person(
Convert.ToInt32(reader[0].ToString()), //uniqueID
reader[1].ToString(), //name
reader[2].ToString(), //sex
UtilDate.FromSql(reader[3].ToString()), //dateBorn
Convert.ToInt32(reader[4].ToString()), //race
Convert.ToInt32(reader[5].ToString()), //countryID
reader[6].ToString(), //description
reader[7].ToString(), //future1: rfid
Convert.ToInt32(reader[9].ToString()) //serverUniqueID
);
}
reader.Close();
if (!dbconOpened)
{
Sqlite.Close();
}
return(p);
}
private static string[] selectAllSessionsDo(string filterName, SqliteConnection dbcon)
{
// This method should NOT use Sqlite.open() / Sqlite.close(): it should only use dbcon
// to connect to the database. This methos is used by the importer after opening an arbitrary
// ChronoJump sqlite database. It needs to be refactored to the new database system.
dbcmd = dbcon.CreateCommand();
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 stuff of each session
dbcmd.CommandText = "SELECT sessionID, encoderConfiguration, signalOrCurve FROM " + Constants.EncoderTable + " ORDER BY sessionID";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader_enc = dbcmd.ExecuteReader();
ArrayList myArray_enc_g_s = new ArrayList(2); //gravitatory sets
ArrayList myArray_enc_g_r = new ArrayList(2); //gravitatory repetitions
ArrayList myArray_enc_i_s = new ArrayList(2); //inertial sets
ArrayList myArray_enc_i_r = new ArrayList(2); //inertial repetitions
int count_g_s = 0;
int count_g_r = 0;
int count_i_s = 0;
int count_i_r = 0;
int sessionBefore = -1;
int sessionNow = -1;
while (reader_enc.Read())
{
//get econf to separate gravitatory and inertial
string [] strFull = reader_enc[1].ToString().Split(new char[] { ':' });
EncoderConfiguration econf = new EncoderConfiguration(
(Constants.EncoderConfigurationNames)
Enum.Parse(typeof(Constants.EncoderConfigurationNames), strFull[0]));
sessionNow = Convert.ToInt32(reader_enc[0].ToString());
if (sessionNow != sessionBefore && sessionBefore != -1)
{
myArray_enc_g_s.Add(sessionBefore.ToString() + ":" + count_g_s.ToString() + ":");
myArray_enc_g_r.Add(sessionBefore.ToString() + ":" + count_g_r.ToString() + ":");
myArray_enc_i_s.Add(sessionBefore.ToString() + ":" + count_i_s.ToString() + ":");
myArray_enc_i_r.Add(sessionBefore.ToString() + ":" + count_i_r.ToString() + ":");
count_g_s = 0;
count_g_r = 0;
count_i_s = 0;
count_i_r = 0;
}
sessionBefore = sessionNow;
if (!econf.has_inertia)
{
if (reader_enc[2].ToString() == "signal")
{
count_g_s++;
}
else
{
count_g_r++;
}
}
else
{
if (reader_enc[2].ToString() == "signal")
{
count_i_s++;
}
else
{
count_i_r++;
}
}
}
myArray_enc_g_s.Add(sessionBefore.ToString() + ":" + count_g_s.ToString() + ":");
myArray_enc_g_r.Add(sessionBefore.ToString() + ":" + count_g_r.ToString() + ":");
myArray_enc_i_s.Add(sessionBefore.ToString() + ":" + count_i_s.ToString() + ":");
myArray_enc_i_r.Add(sessionBefore.ToString() + ":" + count_i_r.ToString() + ":");
reader_enc.Close();
//mix nine arrayLists
string [] mySessions = new string[count];
count = 0;
bool found;
string result_enc_s; //sets
string result_enc_r; //repetitions
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 gravitatory for each session (sets ; repetitions)
result_enc_s = "0";
result_enc_r = "0";
foreach (string line_enc_g_s in myArray_enc_g_s)
{
string [] myStringFull = line_enc_g_s.Split(new char[] { ':' });
if (myStringFull[0] == mixingSessionID)
{
result_enc_s = myStringFull[1];
}
}
foreach (string line_enc_g_r in myArray_enc_g_r)
{
string [] myStringFull = line_enc_g_r.Split(new char[] { ':' });
if (myStringFull[0] == mixingSessionID)
{
result_enc_r = myStringFull[1];
}
}
lineNotReadOnly = lineNotReadOnly + ":" + result_enc_s + " ; " + result_enc_r;
//add encoder inertial for each session (sets ; repetitions)
result_enc_s = "0";
result_enc_r = "0";
foreach (string line_enc_i_s in myArray_enc_i_s)
{
string [] myStringFull = line_enc_i_s.Split(new char[] { ':' });
if (myStringFull[0] == mixingSessionID)
{
result_enc_s = myStringFull[1];
}
}
foreach (string line_enc_i_r in myArray_enc_i_r)
{
string [] myStringFull = line_enc_i_r.Split(new char[] { ':' });
if (myStringFull[0] == mixingSessionID)
{
result_enc_r = myStringFull[1];
}
}
lineNotReadOnly = lineNotReadOnly + ":" + result_enc_s + " ; " + result_enc_r;
mySessions [count++] = lineNotReadOnly;
}
if (mySessions.Length > 0)
{
LogB.SQL(mySessions [0]);
}
else
{
LogB.Debug("SelectAllSessions with filter: " + filterName + " is empty");
}
return(mySessions);
}
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 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 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 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);
}
//the difference between this select and others, is that this returns and ArrayList of Persons
//this is better than return the strings that can produce bugs in the future
//use this in the future:
public static ArrayList SelectCurrentSessionPersons(int sessionID, bool returnPersonAndPSlist)
{
string tp = Constants.PersonTable;
string tps = Constants.PersonSessionTable;
string tpsString = "";
if (returnPersonAndPSlist)
{
tpsString = ", " + tps + ".* ";
}
Sqlite.Open();
dbcmd.CommandText = "SELECT " + tp + ".*" + tpsString +
" FROM " + tp + ", " + tps +
" WHERE " + tps + ".sessionID == " + sessionID +
" AND " + tp + ".uniqueID == " + tps + ".personID " +
" ORDER BY upper(" + tp + ".name)";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
ArrayList myArray = new ArrayList(1);
while (reader.Read())
{
Person person = new Person(
Convert.ToInt32(reader[0].ToString()), //uniqueID
reader[1].ToString(), //name
reader[2].ToString(), //sex
UtilDate.FromSql(reader[3].ToString()), //dateBorn
Convert.ToInt32(reader[4].ToString()), //race
Convert.ToInt32(reader[5].ToString()), //countryID
reader[6].ToString(), //description
Convert.ToInt32(reader[9].ToString()) //serverUniqueID
);
if (returnPersonAndPSlist)
{
PersonSession ps = new PersonSession(
Convert.ToInt32(reader[10].ToString()), //uniqueID
Convert.ToInt32(reader[11].ToString()), //personID
Convert.ToInt32(reader[12].ToString()), //sessionID
Convert.ToDouble(Util.ChangeDecimalSeparator(reader[13].ToString())), //height
Convert.ToDouble(Util.ChangeDecimalSeparator(reader[14].ToString())), //weight
Convert.ToInt32(reader[15].ToString()), //sportID
Convert.ToInt32(reader[16].ToString()), //speciallityID
Convert.ToInt32(reader[17].ToString()), //practice
reader[18].ToString() //comments
);
myArray.Add(new PersonAndPS(person, ps));
}
else
{
myArray.Add(person);
}
}
reader.Close();
Sqlite.Close();
return(myArray);
}