[chronojump/77] v. 0.8.18 sqlite server: 0.77. Work: Query & stats

[Xavier de Blas <xaviblas src gnome org>]

commit 68b3d986458e167febce5cda1d63a80cadcbad6d
Author: Xavier de Blas <xaviblas gmail com>
Date:   Thu Mar 4 17:05:17 2010 +0800

    v. 0.8.18 sqlite server: 0.77. Work: Query & stats

 chronojump_server/bin/chronojumpServer.dll  |  Bin 278016 -> 279552 bytes
 chronojump_server/chronojumpServerCSharp.cs |    8 ++--
 configure.ac                                |    2 +-
 src/gui/queryServer.cs                      |    7 ++-
 src/sqlite/main.cs                          |   65 ++++++++++++++++++++++++---
 src/util.cs                                 |   53 ----------------------
 6 files changed, 69 insertions(+), 66 deletions(-)
---
diff --git a/chronojump_server/bin/chronojumpServer.dll b/chronojump_server/bin/chronojumpServer.dll
index bac624c..e5aa1b9 100755
Binary files a/chronojump_server/bin/chronojumpServer.dll and b/chronojump_server/bin/chronojumpServer.dll differ
diff --git a/chronojump_server/chronojumpServerCSharp.cs b/chronojump_server/chronojumpServerCSharp.cs
index 9afc6bd..b18f6c5 100755
--- a/chronojump_server/chronojumpServerCSharp.cs
+++ b/chronojump_server/chronojumpServerCSharp.cs
@@ -63,11 +63,11 @@ public class ChronojumpServer {
 	public bool CanINew(string action, string clientVersion)
 	{
 		Version cv = new Version(clientVersion);
-		if(action == Constants.ServerActionUploadSession && cv >= new Version(0,8,11))
+		if(action == Constants.ServerActionUploadSession && cv >= new Version(0,8,18))
 			return true;
-		else if(action == Constants.ServerActionStats && cv >= new Version(0,8))
+		else if(action == Constants.ServerActionStats && cv >= new Version(0,8,18))
 			return true;
-		else if(action == Constants.ServerActionQuery && cv >= new Version(0,8,9,6))
+		else if(action == Constants.ServerActionQuery && cv >= new Version(0,8,18))
 			return true;
 
 		return false;
@@ -101,7 +101,7 @@ public class ChronojumpServer {
 			int sex, string ageInterval,
 			int countryID, int sportID, int speciallityID, int levelID)
 	{
-		string str = Util.SQLBuildString(tableName, test, variable,
+		string str = Sqlite.SQLBuildQueryString(tableName, test, variable,
 				sex, ageInterval, 
 				countryID, sportID, speciallityID, levelID
 				);
diff --git a/configure.ac b/configure.ac
index a0368a4..c3f43a5 100644
--- a/configure.ac
+++ b/configure.ac
@@ -1,7 +1,7 @@
 dnl Warning: This is an automatically generated file, do not edit!
 dnl Process this file with autoconf to produce a configure script.
 AC_PREREQ([2.54])
-AC_INIT([chronojump], [0.8.17])
+AC_INIT([chronojump], [0.8.18])
 AM_INIT_AUTOMAKE([foreign])
 
 AC_CONFIG_MACRO_DIR([m4])
diff --git a/src/gui/queryServer.cs b/src/gui/queryServer.cs
index 81bcf5e..2dc0fff 100644
--- a/src/gui/queryServer.cs
+++ b/src/gui/queryServer.cs
@@ -701,7 +701,7 @@ public class QueryServerWindow
 			
 
 		try {
-			string sqlString = Util.SQLBuildString(
+			string sqlString = Sqlite.SQLBuildQueryString(
 					tableName, 
 					UtilGtk.ComboGetActive(combo_tests),
 					strVariable,
@@ -731,7 +731,10 @@ public class QueryServerWindow
 
 				string [] resultFull = result.Split(new char[] {':'});
 				label_results_num.Text = resultFull[0];
-				label_results_avg.Text = Util.TrimDecimals(resultFull[1], pDN);
+				if(resultFull[0] == "0")
+					label_results_avg.Text = "-";
+				else
+					label_results_avg.Text = Util.TrimDecimals(resultFull[1], pDN);
 			}
 
 			return sqlString;
diff --git a/src/sqlite/main.cs b/src/sqlite/main.cs
index 11eabee..c7f7b68 100644
--- a/src/sqlite/main.cs
+++ b/src/sqlite/main.cs
@@ -1012,16 +1012,12 @@ class Sqlite
 				currentVersion = "0.76";
 			}
 			if(currentVersion == "0.76") {
-				conversionRateTotal = 3;
-				conversionRate = 1;
 				dbcon.Open();
-
-				convertPersonAndPersonSessionTo77();
 				
+				convertPersonAndPersonSessionTo77();
 				SqlitePreferences.Update ("databaseVersion", "0.77", true); 
-				conversionRate++;
-				
 				Log.WriteLine("Converted DB to 0.77 (person77, personSession77)"); 
+				
 				dbcon.Close();
 				currentVersion = "0.77";
 			}
@@ -1222,6 +1218,63 @@ class Sqlite
 		return exists;
 	}
 
+	public static string SQLBuildQueryString (string tableName, string test, string variable,
+			int sex, string ageInterval,
+			int countryID, int sportID, int speciallityID, int levelID)
+	{
+		string tp = Constants.PersonTable;
+		string tps = Constants.PersonSessionTable;
+
+		string strSelect = "SELECT COUNT(" + variable + "), AVG(" + variable + ")";
+		string strFrom   = " FROM " + tableName;
+		string strWhere  = " WHERE " + tableName + ".type = '" + test + "'";
+
+		string strSex = "";
+		if(sex == Constants.MaleID) 
+			strSex = " AND " + tp + ".sex == '" + Constants.M + "'";
+		else if (sex == Constants.FemaleID) 
+			strSex = " AND " + tp + ".sex == '" + Constants.F + "'";
+
+		string strAge = "";
+		if(ageInterval != "") {
+			strFrom += ", session";
+			string [] strFull = ageInterval.Split(new char[] {':'});
+			strAge = " AND (julianday(session.date) - julianday(" + tp + ".dateBorn))/365.25 " + 
+				strFull[0] + " " + strFull[1];
+			if(strFull.Length == 4)
+				strAge += " AND (julianday(session.date) - julianday(" + tp + ".dateBorn))/365.25 " + 
+					strFull[2] + " " + strFull[3];
+			strAge += " AND " + tableName + ".sessionID = session.uniqueID";
+		}
+
+		string strCountry = "";
+		if(countryID != Constants.CountryUndefinedID) 
+			strCountry = " AND " + tp + ".countryID == " + countryID;
+
+		string strSport = "";
+		if(sportID != Constants.SportUndefinedID) 
+			strSport = " AND " + tps + ".sportID == " + sportID;
+
+		string strSpeciallity = "";
+		if(speciallityID != Constants.SpeciallityUndefinedID) 
+			strSpeciallity = " AND " + tps + ".speciallityID == " + speciallityID;
+		
+		string strLevel = "";
+		if(levelID != Constants.LevelUndefinedID) 
+			strLevel = " AND " + tps + ".practice == " + levelID;
+
+		string strLast = "";
+		if(strSex.Length > 0 || strAge.Length > 0 || 
+				strCountry.Length > 0 || strSport.Length > 0 || strSpeciallity.Length > 0 || strLevel.Length > 0) {
+			strFrom += ", " + tp + ", " + tps;
+			strLast = " AND " + tableName + ".personID == " + tp + ".uniqueID" +
+				" AND " + tp + ".uniqueID == " + tps + ".personID";
+		}	
+		return strSelect + strFrom + strWhere + strSex + strAge
+			+ strCountry + strSport + strSpeciallity + strLevel + strLast;
+	}
+
+
 	/* 
 	 * temp data stuff
 	 */
diff --git a/src/util.cs b/src/util.cs
index dada29c..922f50a 100644
--- a/src/util.cs
+++ b/src/util.cs
@@ -1125,57 +1125,4 @@ public class Util
 		return searchedValue;
 	}
 
-	public static string SQLBuildString (string tableName, string test, string variable,
-			int sex, string ageInterval,
-			int countryID, int sportID, int speciallityID, int levelID)
-	{
-		string strSelect = "SELECT COUNT(" + variable + "), AVG(" + variable + ")";
-		string strFrom   = " FROM " + tableName;
-		string strWhere  = " WHERE " + tableName + ".type = '" + test + "'";
-
-		string strSex = "";
-		if(sex == Constants.MaleID) 
-			strSex = " AND person.sex == '" + Constants.M + "'";
-		else if (sex == Constants.FemaleID) 
-			strSex = " AND person.sex == '" + Constants.F + "'";
-
-		string strAge = "";
-		if(ageInterval != "") {
-			strFrom += ", session";
-			string [] strFull = ageInterval.Split(new char[] {':'});
-			strAge = " AND (julianday(session.date) - julianday(person.dateBorn))/365.25 " + 
-				strFull[0] + " " + strFull[1];
-			if(strFull.Length == 4)
-				strAge += " AND (julianday(session.date) - julianday(person.dateBorn))/365.25 " + 
-					strFull[2] + " " + strFull[3];
-			strAge += " AND " + tableName + ".sessionID = session.uniqueID";
-		}
-
-		string strCountry = "";
-		if(countryID != Constants.CountryUndefinedID) 
-			strCountry = " AND person.countryID == " + countryID;
-
-		string strSport = "";
-		if(sportID != Constants.SportUndefinedID) 
-			strSport = " AND person.sportID == " + sportID;
-
-		string strSpeciallity = "";
-		if(speciallityID != Constants.SpeciallityUndefinedID) 
-			strSpeciallity = " AND person.speciallityID == " + speciallityID;
-		
-		string strLevel = "";
-		if(levelID != Constants.LevelUndefinedID) 
-			strLevel = " AND person.practice == " + levelID;
-
-		string strLast = "";
-		if(strSex.Length > 0 || strAge.Length > 0 || 
-				strCountry.Length > 0 || strSport.Length > 0 || strSpeciallity.Length > 0 || strLevel.Length > 0) {
-			strFrom += ", person";
-			strLast = " AND " + tableName + ".personID == person.UniqueID";
-		}	
-		return strSelect + strFrom + strWhere + strSex + strAge
-			+ strCountry + strSport + strSpeciallity + strLevel + strLast;
-
-	}
-
 }