private void Done_Click(object sender, EventArgs e)
{
SQLiteDatabase sqlDatabase = null;
try
{
Contact contact = GlobalData.ContactsUserItems.Find(item => item.ID == _contactId);
if (_isDirty && contact != null)
{
Globals dbHelp = new Globals();
dbHelp.OpenDatabase();
sqlDatabase = dbHelp.GetSQLiteDatabase();
if (sqlDatabase != null && sqlDatabase.IsOpen)
{
contact.Save(sqlDatabase);
}
sqlDatabase.Close();
}
}
catch (Exception ex)
{
if (sqlDatabase != null && sqlDatabase.IsOpen)
{
sqlDatabase.Close();
}
Log.Error(TAG, "Done_Click: Exception - " + ex.Message);
if (GlobalData.ShowErrorDialog)
{
ErrorDisplay.ShowErrorAlert(_activity, ex, "Saving Contact Status", "ContactEmergencyStatusDialogFragment.Done_Click");
}
}
Dismiss();
}
public void setAdaptadorCombo(string consulta, Android.Content.Context context, ref Android.Widget.Spinner spinner)
{
try {
loadConnection();
List <string> data = new List <string>();
ICursor cursor = db.RawQuery(consulta, null);
if (cursor.MoveToFirst())
{
data.Add("");
do
{
data.Add(cursor.GetString(0));
} while (cursor.MoveToNext());
}
cursor.Close();
db.Close();
Android.Widget.ArrayAdapter <String> adapter;
adapter = new Android.Widget.ArrayAdapter <String>(context, Android.Resource.Layout.SimpleSpinnerItem, data);
spinner.Adapter = adapter;
} catch (Exception e)
{
throw e;
}
}
private void LoadAppointment()
{
SQLiteDatabase sqlDatabase = null;
try
{
Globals dbHelp = new Globals();
dbHelp.OpenDatabase();
sqlDatabase = dbHelp.GetSQLiteDatabase();
if (sqlDatabase != null)
{
if (sqlDatabase.IsOpen)
{
_appointment = dbHelp.GetAppointmentByID(_appointmentID);
sqlDatabase.Close();
}
}
}
catch (Exception e)
{
if (sqlDatabase != null)
{
if (sqlDatabase.IsOpen)
{
sqlDatabase.Close();
}
}
Log.Error(TAG, "LoadAppointment: Exception - " + e.Message);
if (GlobalData.ShowErrorDialog)
{
ErrorDisplay.ShowErrorAlert(this, e, "Loading Appointment", "ResourcesAppointmentItemActivity.LoadAppointment");
}
}
}
public void Remove()
{
SQLiteDatabase sqlDatabase = null;
try
{
Globals dbHelp = new Globals();
dbHelp.OpenDatabase();
sqlDatabase = dbHelp.GetSQLiteDatabase();
if (sqlDatabase != null && sqlDatabase.IsOpen)
{
var sql = "DELETE FROM [Relationships] WHERE RelationshipsID = " + RelationshipsID.ToString();
sqlDatabase.ExecSQL(sql);
Log.Info(TAG, "Remove: Removed Relationship with ID " + RelationshipsID.ToString() + " successfully");
sqlDatabase.Close();
}
Log.Error(TAG, "Remove: SQLite database is null or was not opened - remove failed");
}
catch (Exception e)
{
Log.Error(TAG, "Remove: Exception - " + e.Message);
if (sqlDatabase != null && sqlDatabase.IsOpen)
{
sqlDatabase.Close();
}
}
}
public bool Remove()
{
SQLiteDatabase sqlDatabase = null;
try
{
Globals dbHelp = new Globals();
dbHelp.OpenDatabase();
sqlDatabase = dbHelp.GetSQLiteDatabase();
if (sqlDatabase != null && sqlDatabase.IsOpen)
{
Log.Info(TAG, "Remove: Attempting to Remove Medication Time with ID - " + ID.ToString());
var sql = "DELETE FROM [MedicationTime] WHERE ID = " + ID.ToString();
sqlDatabase.ExecSQL(sql);
Log.Info(TAG, "Remove: Removed Medication Time with ID of " + ID.ToString() + " successfully");
sqlDatabase.Close();
return(true);
}
Log.Error(TAG, "Remove: SQLite database is null or was not opened - remove failed");
return(false);
}
catch (Exception e)
{
Log.Error(TAG, "Remove: Exception - " + e.Message);
if (sqlDatabase != null && sqlDatabase.IsOpen)
{
sqlDatabase.Close();
}
return(false);
}
}
public bool Save(int medicationSpreadID)
{
SQLiteDatabase sqlDatabase = null;
try
{
Globals dbHelp = new Globals();
dbHelp.OpenDatabase();
sqlDatabase = dbHelp.GetSQLiteDatabase();
if (sqlDatabase != null)
{
if (sqlDatabase.IsOpen)
{
if (IsNew)
{
Log.Info(TAG, "Save: Saving new Medication Time to Spread ID - " + medicationSpreadID.ToString());
ContentValues values = new ContentValues();
values.Put("MedicationSpreadID", medicationSpreadID);
values.Put("MedicationTime", (int)MedicationTime);
values.Put("TakenTime", string.Format("{0:HH:mm:ss}", TakenTime));
Log.Info(TAG, "Save: Stored Medication Time - " + StringHelper.MedicationTimeForConstant(MedicationTime) + ", Time Taken - " + TakenTime.ToShortTimeString());
ID = (int)sqlDatabase.Insert("MedicationTime", null, values);
Log.Info(TAG, "Save: Saved Medication Time with new ID - " + ID.ToString());
IsNew = false;
IsDirty = false;
Log.Info(TAG, "Save: IsNew - FALSE, IsDirty - FALSE");
}
if (IsDirty)
{
Log.Info(TAG, "Save (Update): Updating existing Medication Time for Spread ID - " + medicationSpreadID.ToString());
ContentValues values = new ContentValues();
values.Put("MedicationSpreadID", medicationSpreadID);
values.Put("MedicationTime", (int)MedicationTime);
values.Put("TakenTime", string.Format("{0:HH:mm:ss}", TakenTime));
Log.Info(TAG, "Save (Update): Stored Medication Time - " + StringHelper.MedicationTimeForConstant(MedicationTime) + ", Taken Time - " + TakenTime.ToShortTimeString());
string whereClause = "ID = ?";
sqlDatabase.Update("MedicationTime", values, whereClause, new string[] { ID.ToString() });
IsDirty = false;
Log.Info(TAG, "Save (Update): IsDirty - FALSE");
}
sqlDatabase.Close();
return(true);
}
}
return(false);
}
catch (Exception e)
{
Log.Error(TAG, "Save: Exception - " + e.Message);
if (sqlDatabase != null && sqlDatabase.IsOpen)
{
sqlDatabase.Close();
}
return(false);
}
}
public void Save()
{
SQLiteDatabase sqlDatabase = null;
try
{
Globals dbHelp = new Globals();
dbHelp.OpenDatabase();
sqlDatabase = dbHelp.GetSQLiteDatabase();
if (sqlDatabase != null)
{
if (sqlDatabase.IsOpen)
{
if (IsNew)
{
ContentValues values = new ContentValues();
values.Put("WithWhom", WithWhom);
values.Put("Type", (int)Type);
values.Put("Strength", Strength);
values.Put("Feeling", Feeling);
values.Put("Action", (int)Action);
values.Put("ActionOf", ActionOf);
RelationshipsID = (int)sqlDatabase.Insert("Relationships", null, values);
IsNew = false;
IsDirty = false;
}
if (IsDirty)
{
ContentValues values = new ContentValues();
values.Put("WithWhom", WithWhom);
values.Put("Type", (int)Type);
values.Put("Strength", Strength);
values.Put("Feeling", Feeling);
values.Put("Action", (int)Action);
values.Put("ActionOf", ActionOf);
string whereClause = "RelationshipsID = ?";
sqlDatabase.Update("Relationships", values, whereClause, new string[] { RelationshipsID.ToString() });
IsDirty = false;
}
sqlDatabase.Close();
}
}
}
catch (Exception e)
{
Log.Error(TAG, "Save: Exception - " + e.Message);
if (sqlDatabase != null && sqlDatabase.IsOpen)
{
sqlDatabase.Close();
}
}
}
public void AlertPositiveButtonSelect(object sender, DialogClickEventArgs e, string instanceId)
{
SQLiteDatabase sqlDatabase = null;
try
{
if (instanceId == "useMic")
{
PermissionsHelper.RequestApplicationPermission(this, ConstantsAndTypes.AppPermission.UseMicrophone);
return;
}
if (instanceId == "remove")
{
var question = _appointment.Questions[_selectedItemIndex];
if (question != null)
{
Globals dbHelp = new Globals();
dbHelp.OpenDatabase();
sqlDatabase = dbHelp.GetSQLiteDatabase();
if (sqlDatabase != null && sqlDatabase.IsOpen)
{
question.Remove(sqlDatabase);
_appointment.Questions.Remove(question);
UpdateAdapter();
sqlDatabase.Close();
sqlDatabase = null;
_selectedItemIndex = -1;
}
}
}
else
{
SaveAppointment();
SetResult(Result.Ok);
Finish();
}
}
catch (Exception ex)
{
if (sqlDatabase != null && sqlDatabase.IsOpen)
{
sqlDatabase.Close();
sqlDatabase = null;
}
Log.Error(TAG, "AlertPositiveButtonSelect: Exception - " + ex.Message);
if (GlobalData.ShowErrorDialog)
{
ErrorDisplay.ShowErrorAlert(this, ex, GetString(Resource.String.AppointmentQuestionRemoveAlertTitle), "ResourcesAppointmentItemActivity.AlertPositiveButtonSelect");
}
}
}
public bool Save(int medicationID)
{
SQLiteDatabase sqlDatabase = null;
try
{
Globals dbHelp = new Globals();
dbHelp.OpenDatabase();
sqlDatabase = dbHelp.GetSQLiteDatabase();
if (sqlDatabase != null)
{
if (sqlDatabase.IsOpen)
{
if (IsNew)
{
ContentValues values = new ContentValues();
values.Put("MedicationID", medicationID);
values.Put("PrescriptionType", (int)PrescriptionType);
values.Put("WeeklyDay", (int)WeeklyDay);
values.Put("MonthlyDay", MonthlyDay);
ID = (int)sqlDatabase.Insert("Prescription", null, values);
IsNew = false;
IsDirty = false;
}
if (IsDirty)
{
ContentValues values = new ContentValues();
values.Put("MedicationID", medicationID);
values.Put("PrescriptionType", (int)PrescriptionType);
values.Put("WeeklyDay", (int)WeeklyDay);
values.Put("MonthlyDay", MonthlyDay);
string whereClause = "ID = ?";
sqlDatabase.Update("Prescription", values, whereClause, new string[] { ID.ToString() });
IsDirty = false;
}
sqlDatabase.Close();
return(true);
}
}
return(false);
}
catch (Exception e)
{
Log.Error(TAG, "Save: Exception - " + e.Message);
if (sqlDatabase != null && sqlDatabase.IsOpen)
{
sqlDatabase.Close();
}
return(false);
}
}
public void Save()
{
SQLiteDatabase sqlDatabase = null;
try
{
Globals dbHelp = new Globals();
dbHelp.OpenDatabase();
sqlDatabase = dbHelp.GetSQLiteDatabase();
if (sqlDatabase != null)
{
if (sqlDatabase.IsOpen)
{
if (IsNew)
{
ContentValues values = new ContentValues();
values.Put("Aspect", Aspect);
values.Put("Importance", Importance);
values.Put("Type", (int)Type);
values.Put("Action", (int)Action);
values.Put("ActionOf", ActionOf);
HealthID = (int)sqlDatabase.Insert("Health", null, values);
IsNew = false;
IsDirty = false;
}
if (IsDirty)
{
ContentValues values = new ContentValues();
values.Put("Aspect", Aspect);
values.Put("Importance", Importance);
values.Put("Type", (int)Type);
values.Put("Action", (int)Action);
values.Put("ActionOf", ActionOf);
string whereClause = "HealthID = ?";
sqlDatabase.Update("Health", values, whereClause, new string[] { HealthID.ToString() });
IsDirty = false;
}
sqlDatabase.Close();
}
}
}
catch (Exception e)
{
Log.Error(TAG, "Save: Exception - " + e.Message);
if (sqlDatabase != null && sqlDatabase.IsOpen)
{
sqlDatabase.Close();
}
}
}
public static RiderMessage[] GetMessages(string userId,string raceId)
{
List<RiderMessage> messages = new List<RiderMessage>();
SQLiteDatabase db = new SQLiteDatabase();
string sql = "select m.RaceId, u.userid,u.username, m.message, m.SendTime " +
"from cycli_rider_messages m, cycli_riders u " +
"where m.SenderId = u.UserId and " +
"m.RaceId = @r " +
"order by m.SendTime";
DataTable dt = db.GetDataTable(sql,"@r",raceId, "@t", "fromTime");
db.Close();
foreach (DataRow dr in dt.Rows)
{
RiderMessage message = new RiderMessage();
message.SenderId = (string)dr["userid"];
message.Sender = (string)dr["username"];
message.RaceId = (string)dr["RaceId"];
message.Message = (string)dr["Message"];
message.SendTime = (long)(int)dr["SendTime"];
messages.Add(message);
}
return messages.ToArray();
}
//Сюди будемо передавати одиницю, якщо хочемо здійснити запис в базу даних
protected override bool RunInBackground(params int[] @params)
{
int param = @params[0];
if (param == 1)
{
SQLiteOpenHelper createRoomDatabaseHelper = new RationalCleaningDatabaseHelper(myActivity);
try
{
SQLiteDatabase db = createRoomDatabaseHelper.WritableDatabase;
ContentValues roomValues = new ContentValues();
roomValues.Put("TITLE", roomTitle);
roomValues.Put("IS_ROOM", 1);
roomValues.Put("IMAGE_ID", imageId);
db.Insert("ROOM_TABLE", null, roomValues);
db.Close();
return(true);
}
catch (SQLException)
{
return(false);
}
}
return(false);
}
//! Login validity checker
public bool Validity(string username, string password)
{
SQLiteDatabase db = this.ReadableDatabase;
ICursor c = db.Query("UserDataBase", new string[] { "UserName", "Password", "Email" }, "UserName =? and Password =?", new string[] { username, password }, null, null, null, null);
bool validity = new bool();
while (c.MoveToNext())
{
if (string.Compare(c.GetString(0), username) == 0 && string.Compare(c.GetString(1), password) == 0)
{
return(true);
}
else
{
return(false);
}
}
c.Close();
db.Close();
return(false);
}
//Retrive All Details
public IList <InfoMedica> GetAllInfoMedica(string idPersonal)
{
SQLiteDatabase db = this.ReadableDatabase;
ICursor c = db.Query("InfoMedica", new string[] { "id", "idPersona", "identificacion", "peso", "altura", "presionArterial", "frecuenciaCardiaca", "detalle" }, "idPersona = " + idPersonal, null, null, null, null);
var infoMedica = new List <InfoMedica>();
while (c.MoveToNext())
{
infoMedica.Add(new InfoMedica {
id = c.GetInt(0),
idPersona = Int32.Parse(c.GetString(1)),
identificacion = c.GetString(2),
peso = c.GetDouble(3),
altura = c.GetDouble(4),
presionArterial = c.GetString(5),
frecuenciaCardiaca = c.GetString(6),
detalle = c.GetString(7)
});
}
c.Close();
db.Close();
return(infoMedica);
}
protected override void OnCreate(Bundle bundle)
{
base.OnCreate(bundle);
SetContentView(Resource.Layout.ShowList);
lv = FindViewById <ListView> (Resource.Id.listView1);
lista = new List <string> ();
SQLiteDatabase sql = OpenOrCreateDatabase("OCENA", 0, null);
var kursor = sql.RawQuery("select distinct(przedmiot) from wyniki", null);
while (kursor.MoveToNext())
{
lista.Add(kursor.GetString(0));
}
sql.Close();
MyListViewAdaptorHistory adapter = new MyListViewAdaptorHistory(this, lista);
lv.Adapter = adapter;
lv.ItemClick += delegate(object sender, AdapterView.ItemClickEventArgs position) {
String selectedFromList = (String)(lv.GetItemAtPosition(position.Position));
Intent intent = new Intent(this, typeof(Subject));
intent.PutExtra("przedmiot", selectedFromList);
StartActivity(intent);
};
}
//Retrive All Contact Details
public IList <Persona> GetAllPersonas()
{
SQLiteDatabase db = this.ReadableDatabase;
ICursor c = db.Query("Persona", new string[] { "id", "identificacion", "nombre", "apellidos", "direccion", "correo", "telefono", "fechaNac", "sexo", "estadoCivil", "tipoSangre", "detalle" }, null, null, null, null, null);
var personas = new List <Persona>();
while (c.MoveToNext())
{
personas.Add(new Persona {
id = c.GetInt(0),
identificacion = c.GetString(1),
nombre = c.GetString(2),
apellidos = c.GetString(3),
direccion = c.GetString(4),
correo = c.GetString(5),
telefono = c.GetString(6),
fechaNac = Convert.ToDateTime(c.GetString(7)),
sexo = c.GetString(8),
estadoCivil = c.GetString(9),
tipoSangre = c.GetString(10),
detalle = c.GetString(11)
});
}
c.Close();
db.Close();
return(personas);
}
public void deleteTask(String task)
{
SQLiteDatabase db = this.WritableDatabase;
db.Delete(DB_TABLE, DB_COLUMN + " = ? ", new string[] { task });
db.Close();
}
//Retrive All Contact Details
public IList <User> GetUsersByName(string nameToSearch)
{
SQLiteDatabase db = this.ReadableDatabase;
ICursor c = db.Query("User", new string[] { "Id", "Firstname", "Lastname", "Address", "Email" }, "upper(Firstname) LIKE ?", new string[] { "%" + nameToSearch.ToUpper() + "%" }, null, null, null, null);
var users = new List <User>();
while (c.MoveToNext())
{
users.Add(new User
{
Id = c.GetInt(0),
Firstname = c.GetString(1),
Lastname = c.GetString(2),
Address = c.GetString(3),
Email = c.GetString(4)
});
}
c.Close();
db.Close();
return(users);
}
//Сюди будемо передавати одиницю, якщо хочемо здійснити запис в базу даних
protected override bool RunInBackground(params int[] @params)
{
int param = @params[0];
if (param == 1)
{
SQLiteOpenHelper updateRoomDatabaseHelper = new RationalCleaningDatabaseHelper(myActivity);
try
{
SQLiteDatabase db = updateRoomDatabaseHelper.WritableDatabase;
ContentValues roomValues = new ContentValues();
roomValues.Put("TITLE", roomTitle);
roomValues.Put("IMAGE_ID", roomImageId);
//Якщо провтикать, то вийде оновити title розділів, у яких title не повинен змінюватися (розділи із умовою isRoom == 0 (true))
//По хорошому, щоб ніде не провтичить, варто було б десь тут захист поставити. Сподіваюся найближчим часом я упорюсь і зроблю це. Інакше чекай новий фейлів
db.Update("ROOM_TABLE", roomValues, "_id = ?", new string[] { roomId.ToString() });
db.Close();
return(true);
}
catch (SQLException)
{
return(false);
}
}
return(false);
}
//Сюди будемо передавати одиницю, якщо хочемо здійснити запис в базу даних
protected override bool RunInBackground(params int[] @params)
{
int param = @params[0];
if (param == 1)
{
SQLiteOpenHelper updateCleaningTaskDatabaseHelper = new RationalCleaningDatabaseHelper(myActivity);
try
{
SQLiteDatabase db = updateCleaningTaskDatabaseHelper.WritableDatabase;
ContentValues taskValues = new ContentValues();
//taskValues.Put("YEAR", year);
//taskValues.Put("MONTH", month);
//taskValues.Put("DAY_OF_MONTH", dayOfMonth);
taskValues.Put("YEAR_OF_CHANGE", yearOfChange);
taskValues.Put("MONTH_OF_CHANGE", monthOfChange);
taskValues.Put("DAY_OF_MONTH_OF_CHANGE", dayOfMonthOfChange);
db.Update("CLEANING_TASK_TABLE", taskValues, "_id = ?", new string[] { taskId.ToString() });
db.Close();
return(true);
}
catch (SQLException)
{
return(false);
}
}
return(false);
}
public void QuestionAdded(int appointmentID, string question)
{
SQLiteDatabase sqlDatabase = null;
try
{
if (question.Trim() != "")
{
AppointmentQuestion newQuestion = new AppointmentQuestion();
newQuestion.AppointmentID = appointmentID;
newQuestion.Question = question.Trim();
newQuestion.IsNew = true;
_appointment.Questions.Add(newQuestion);
_isDirtyQuestions = true;
UpdateAdapter();
}
}
catch (Exception e)
{
if (sqlDatabase != null && sqlDatabase.IsOpen)
{
sqlDatabase.Close();
sqlDatabase = null;
}
Log.Error(TAG, "QuestionAdded: Exception - " + e.Message);
if (GlobalData.ShowErrorDialog)
{
ErrorDisplay.ShowErrorAlert(this, e, GetString(Resource.String.ErrorAddingAppointmentQuestion), "ResourcesAppointmentItemActivity.QuestionAdded");
}
}
}
public void RemoveTask(String task)
{
SQLiteDatabase db = this.WritableDatabase;
db.Delete(db_table, db_column + " = ?", new string[] { task });
db.Close();
}
protected override void OnCreate(Bundle bundle)
{
base.OnCreate(bundle);
SetContentView(Resource.Layout.ShowList);
lv = FindViewById <ListView> (Resource.Id.listView1);
przedmiot = Intent.GetStringExtra("przedmiot") ?? "";
TextView label = FindViewById <TextView> (Resource.Id.Label4Id);
label.Text = przedmiot;
lista = new List <Historia> ();
SQLiteDatabase sql = OpenOrCreateDatabase("OCENA", 0, null);
var kursor = sql.RawQuery("select sprawdzian, ocena, uwaga, data from wyniki where przedmiot='" + przedmiot + "'", null);
while (kursor.MoveToNext())
{
lista.Add(new Historia {
nazwaSprawdzianu = kursor.GetString(0), ocena = kursor.GetInt(1), uwagi = kursor.GetString(2), data = kursor.GetString(3)
});
}
sql.Close();
MyListViewAdaptorSubject adapter = new MyListViewAdaptorSubject(this, lista);
lv.Adapter = adapter;
}
public void DeleteTask(String task)
{
SQLiteDatabase db = this.WritableDatabase;
db.Delete(DB_Table, DB_Column + "=?", new String[] { task });
db.Close();
}
public void deleteAll()
{
SQLiteDatabase db = this.WritableDatabase;
db.Delete(DB_TABLE, null, null);
db.Close();
}
public List <Ranking> GetRanking()
{
List <Ranking> lstRanking = new List <Ranking>();
SQLiteDatabase db = this.WritableDatabase;
ICursor c;
try
{
c = db.RawQuery("SELECT * FROM Ranking ORDER BY Score", null);
if (c == null)
{
return(null);
}
c.MoveToNext();
do
{
int Id = c.GetInt(c.GetColumnIndex("Id"));
int Score = c.GetInt(c.GetColumnIndex("Score"));
Ranking ranking = new Model.Ranking(Id, Score);
lstRanking.Add(ranking);
}while (c.MoveToNext());
c.Close();
}
catch { }
db.Close();
return(lstRanking);
}
//Сюди будемо передавати одиницю, якщо хочемо здійснити запис в базу даних
protected override bool RunInBackground(params int[] @params)
{
int param = @params[0];
if (param == 1)
{
SQLiteOpenHelper deleteRoomDatabaseHelper = new RationalCleaningDatabaseHelper(myActivity);
try
{
SQLiteDatabase db = deleteRoomDatabaseHelper.WritableDatabase;
db.Delete("ROOM_TABLE", "_id = ?", new string[] { roomId.ToString() });
db.Close();
return(true);
}
catch (SQLException)
{
return(false);
}
}
return(false);
}
//Retrive All Contact Details
public IList <AddressBook> GetContactsBySearchName(string nameToSearch)
{
SQLiteDatabase db = this.ReadableDatabase;
ICursor c = db.Query("AddressBook", new string[] { "Id", "FullName", "Mobile", "Email", "Details" }, "upper(FullName) LIKE ?", new string[] { "%" + nameToSearch.ToUpper() + "%" }, null, null, null, null);
var contacts = new List <AddressBook>();
while (c.MoveToNext())
{
contacts.Add(new AddressBook
{
Id = c.GetInt(0),
FullName = c.GetString(1),
Mobile = c.GetString(2),
Email = c.GetString(3),
Details = c.GetString(4)
});
}
c.Close();
db.Close();
return(contacts);
}
public bool LoadMedication()
{
SQLiteDatabase sqlDatabase = null;
try
{
var sql = @"SELECT [MedicationName], [TotalDailyDosage] FROM [Medication] WHERE [ID] = ?";
Globals dbHelp = new Globals();
Log.Info(TAG, "LoadMedication: Opening database");
dbHelp.OpenDatabase();
sqlDatabase = dbHelp.GetSQLiteDatabase();
if (sqlDatabase != null)
{
if (sqlDatabase.IsOpen)
{
//Log.Info(TAG, "LoadMedication: Attempting to Load Medication with ID - " + ID.ToString());
ICursor med = sqlDatabase.RawQuery(sql, new string[] { ID.ToString() });
if (med.Count > 0)
{
med.MoveToFirst();
MedicationName = med.GetString(med.GetColumnIndex("MedicationName")).Trim();
TotalDailyDosage = med.GetInt(med.GetColumnIndex("TotalDailyDosage"));
//Log.Info(TAG, "LoadMedication: Retrieved Medication Name - " + MedicationName + ", Total Daily Dosage - " + TotalDailyDosage.ToString());
if (MedicationSpread == null)
{
MedicationSpread = new List <MedicationSpread>();
}
//Log.Info(TAG, "LoadMedication: Loading Medication Spreads for Medication with ID - " + ID.ToString());
LoadMedicationSpreads(sqlDatabase);
var preps = new Prescription();
preps.MedicationID = ID;
var succeeded = preps.LoadPrescription(ID);
if (succeeded)
{
PrescriptionType = preps;
}
IsNew = false;
IsDirty = false;
}
else
{
Log.Info(TAG, "LoadMedication: No result!");
}
dbHelp.CloseDatabase();
return(true);
}
}
return(false);
}
catch (Exception e)
{
Log.Error(TAG, "LoadMedication: Exception - " + e.Message);
if (sqlDatabase != null && sqlDatabase.IsOpen)
{
sqlDatabase.Close();
}
return(false);
}
}
/**
* 关闭数据库
*/
public void closeDatabase()
{
if (mOpenCounter.DecrementAndGet() == 0)
{
// Closing database
mDatabase.Close();
}
}
public void FinalizarConexao()
{
if (BancoDeDadosDisponivel)
{
pSqlDB.Close();
BancoDeDadosDisponivel = false;
}
}
public static string ChangeFriendCode(string userId)
{
SQLiteDatabase db = new SQLiteDatabase();
// Some logic here - we only allow a new friend if
// (a) The friendship does not already exist
// (b) The friendCode is correct
string g = Guid.NewGuid().ToString();
string sqlFriendCode = "update cycli_riders set friendCode=@g where UserId = @u";
db.ExecuteNonQuery(sqlFriendCode, "@g", g, "@u", userId);
// This query checks that the user has not already been invited
db.Close();
return g;
}
protected void Page_Init(object sender, EventArgs e)
{
SQLiteDatabase db = new SQLiteDatabase();
string sql = @"select nationality from cycli_nationalities";
DataTable dtNationality = db.GetDataTable(sql);
foreach (DataRow dr in dtNationality.Rows)
{
nationality.Items.Add((string)(dr[0]));
}
sql = @"select type from cycli_turbos order by type";
DataTable dtTurbos = db.GetDataTable(sql);
foreach (DataRow dr in dtTurbos.Rows)
{
turbo.Items.Add((string)(dr[0]));
}
db.Close();
}
public static Dictionary<string, int> SetHandicaps(string raceId)
{
Dictionary<string, int> handicaps = new Dictionary<string, int>();
Race race = Load(raceId);
foreach (Participant p in race.Participants)
{
handicaps.Add(p.UserId, 100);
}
if (race != null)
{
SQLiteDatabase db = new SQLiteDatabase();
long yearAgo = DbTime.ToDbSecs(DateTime.UtcNow);
string sql = "select rr1.UserId, " +
"r.StartDateTime, " +
"(1000 * cast(rr1.Energy as real)/ cast(rr1.Time as real)) as Power " +
"From cycli_race_riders rr1, cycli_race_riders rr2, cycli_races r " +
"where r.StartDateTime > @t " +
"and r.RaceId = rr2.RaceId " +
"and rr1.Time > 0 " +
"and rr1.Energy > 0 " +
"and (rr1.Status='Finished' or rr1.Status='Abandoned') " +
"and rr1.UserId = rr2.UserId " +
"and r.RaceId = @r " +
"order by rr1.Time";
DataTable dt = db.GetDataTable(sql, "@t", yearAgo, "@r", raceId);
db.Close();
// Weight the average power by (a) time in the past
// (b) closeness to the selected distance
Dictionary<string, double> userPowers = dt.AsEnumerable()
.GroupBy(p => p.Field<string>("UserId"))
.ToDictionary(p => p.Key,
p => p.Sum(q => (double)(yearAgo - q.Field<long>("StartDateTime")) * q.Field<double>("Power"))/
p.Sum(q => yearAgo - q.Field<long>("StartDateTime")));
// Total AveragePower across all users
double meanUserPower = userPowers.Values.Average(p => p);
if (meanUserPower > 0)
{
foreach (KeyValuePair<string, double> k in userPowers)
{
int handicap = (int)Math.Round(100 * meanUserPower / k.Value);
handicaps[k.Key] = handicap;
}
}
string updateSql = "update cycli_race_riders set handicap=@h where raceid=@r and userid=@u";
db = new SQLiteDatabase(true);
try
{
foreach (KeyValuePair<string, int> h in handicaps)
{
db.ExecuteNonQuery(updateSql, "@h", h.Value, "@r", raceId, "@u", h.Key);
}
try
{
db.CommitTransaction();
}
catch (Exception ex)
{
db.RollbackTransaction();
}
}
finally
{
}
}
return handicaps;
}
public static Profile[] LoadAll(string userId)
{
List<Profile> profiles = new List<Profile>();
string sql = "select p.UserId, p.ProfileId, p.Name, p.Description, p.Favourite, p.CreatedOn, p.ModifiedOn, s.X, s.Y " +
"from cycli_profiles p, cycli_profile_spots s " +
"where " +
"s.ProfileId = p.ProfileId " +
"and p.UserId = '"+userId+"' order by p.ProfileId, s.PointOrder";
SQLiteDatabase db = new SQLiteDatabase();
DataTable dt = db.GetDataTable(sql);
string profileId = "";
Profile thisProfile = null;
foreach (DataRow dr in dt.Rows)
{
if (profileId != (string)dr["ProfileId"])
{
// New profile
profileId = (string)dr["ProfileId"];
Profile p = new Profile();
p.UserId = userId;
p.ProfileId = profileId;
p.Favourite = ((string)dr["Favourite"] == bool.TrueString);
p.Name = (string)dr["Name"];
p.Description = (string)dr["Description"];
profiles.Add(p);
thisProfile = p;
}
float[] pt = {(float)(long)dr["X"], (float)(long)dr["Y"]};
thisProfile.data.Add(pt);
}
db.Close();
return profiles.ToArray();
}
public static void Delete(string userId, string riderId)
{
// Has this rider been employed in a race
string sqlPlayed = @"select count(*) from cycli_race_riders where userid=@u and (Status='Finished' or Status='Abandoned')";
string sqlRiderDelete = @"delete from cycli_virtual_riders where ownerid = @o and userid = @u";
string sqlRiderRaceDelete = @"delete from cycli_race_riders where userid = @u";
string sqlRiderInactive = @"update cycli_virtual_riders set status='Inactive' where ownerid = @o and userid = @u";
bool riderPlayed = false;
SQLiteDatabase db = new SQLiteDatabase();
try
{
riderPlayed = (int.Parse(db.ExecuteScalar(sqlPlayed,"@u",riderId)) > 0);
}
finally
{
db.Close();
}
// Now reopen for transactions
db = new SQLiteDatabase(true);
try
{
if (riderPlayed)
{
db.ExecuteNonQuery(sqlRiderInactive, "@o", userId, "@u", riderId);
}
else
{
// Get rid of rider from any pending races
db.ExecuteNonQuery(sqlRiderRaceDelete, "@u", riderId);
db.ExecuteNonQuery(sqlRiderDelete, "@o", userId, "@u", riderId);
}
// Commit closes db
db.CommitTransaction();
}
catch (Exception ex)
{
db.RollbackTransaction();
}
}
public static VirtualRider Load(string riderId)
{
VirtualRider thisRider = null;
SQLiteDatabase db = new SQLiteDatabase();
string sql = @"select OwnerId,UserId, Username, PowerMinimum, Power1Hr, Power5Min, Power1Min, Power5Sec, Aggression " +
"From cycli_virtual_riders r " +
"where UserId=@u and Status='Active'";
// Only load active accounts
DataTable dtUser = db.GetDataTable(sql,"@u", riderId);
if (dtUser.Rows.Count > 0)
{
DataRow dr = dtUser.Rows[0];
thisRider = new VirtualRider();
thisRider.UserName = dr.Field<string>("Username");
thisRider.UserId = dr.Field<string>("UserId");
thisRider.OwnerId = dr.Field<string>("OwnerId");
thisRider.BikeWheelSizeMm = 700;
thisRider.PowerMin = (int)dr.Field<long>("PowerMinimum");
thisRider.Power1Hr = (int)dr.Field<long>("Power1Hr");
thisRider.Power5Min = (int)dr.Field<long>("Power5Min");
thisRider.Power1Min = (int)dr.Field<long>("Power1Min");
thisRider.Power5Sec = (int)dr.Field<long>("Power5Sec");
thisRider.Aggression = (int)dr.Field<long>("Aggression");
}
db.Close();
return thisRider;
}
public void Save(string userId)
{
SQLiteDatabase db = new SQLiteDatabase();
// Query forces identity
string sql = @"update cycli_rider_turbos set Type=@t, " +
"Power_Model_C1=@c1, Power_Model_C2=@c2, Power_Model_C3=@c3 " +
"where UserId=@u";
int updated = db.ExecuteNonQuery(sql, "@t", this.Type,
"@c1", this.Coefficients.Length == 0 ? 0 : this.Coefficients[0],
"@c2", this.Coefficients.Length == 0 ? 0 : this.Coefficients[1],
"@c3", this.Coefficients.Length == 0 ? 0 : this.Coefficients[2],
"@u", userId);
if (updated == 0)
{
// It's a new entry
sql = @"insert into cycli_rider_turbos (UserId, Type, Power_Model_C1, Power_Model_C2, Power_Model_C3) " +
"values (@u, @t, @c1, @c2, @c3)";
db.ExecuteNonQuery(sql,
"@u", userId,
"@t", this.Type,
"@c1", this.Coefficients.Length == 0 ? 0 : this.Coefficients[0],
"@c2", this.Coefficients.Length == 0 ? 0 : this.Coefficients[1],
"@c3", this.Coefficients.Length == 0 ? 0 : this.Coefficients[2]);
}
db.Close();
}
public static TurboTrainer[] LoadAll()
{
TurboTrainer[] turbos = new TurboTrainer[]{};
SQLiteDatabase db = new SQLiteDatabase();
string sql = @"select type, power_model_c1, power_model_c2, power_model_c3 from cycli_turbos order by type";
DataTable dtTurbos = db.GetDataTable(sql);
db.Close();
if (dtTurbos.Rows.Count > 0)
{
turbos = dtTurbos.AsEnumerable().Select(p => new TurboTrainer
{
Type = (string)(p["type"]),
Coefficients = new double[] { (double)p["power_model_c1"], (double)p["power_model_c2"], (double)p["power_model_c3"] }
}).ToArray();
}
return turbos;
}
public static int[] CountBetween(string userId, long start, long end)
{
SQLiteDatabase db = new SQLiteDatabase();
string sql = "select r.Status RaceStatus, rr.Status RiderStatus " +
"From cycli_races r, cycli_race_riders rr " +
"where rr.UserId ='" + userId + "' and " +
"rr.RaceId = r.RaceId and " +
"r.StartDateTime between " + start + " and " + end +
" and (RaceStatus = 'Planned' or not rr.Status ='Invited') ";
DataTable dtRaces = db.GetDataTable(sql);
db.Close();
int[] counts = new int[] { 0, 0, 0 };
foreach (DataRow dr in dtRaces.Rows)
{
if ((string)dr["RaceStatus"] == @"Finished")
{
// Only interested if we actually raced - not if we were only invited
if ((string)dr["RiderStatus"] == @"Abandoned" || (string)dr["RiderStatus"] == @"Finished")
{
counts[2]++;
}
}
else
{
if ((string)dr["RiderStatus"] == @"Joined")
{
counts[1]++;
}
else
{
counts[0]++;
}
}
}
return counts;
}
public static Rider LoadAny(string userId)
{
Rider thisRider = null;
SQLiteDatabase db = new SQLiteDatabase();
string sql = @"select r.UserId as UserId, r.Username as Username, r.BikeWheelSizeMm as BikeWheelSizeMm, r.Turbo as Turbo, " +
"r.TurboIsCalibrated as TurboIsCalibrated, r.EstimatedPower as EstimatedPower " +
"From cycli_riders r " +
"where UserId=@u1 and AccountStatus='Active'" +
"union " +
"select r.UserId as UserId, r.Username as Username, 700 as BikeWheelSizeMm, null as Turbo, " +
"'False' as TurboIsCalibrated, 'False' as EstimatedPower " +
"From cycli_virtual_riders r " +
"where UserId=@u2 and Status='Active'";
// Only load active accounts
DataTable dtUser = db.GetDataTable(sql, "@u1", userId, "@u2",userId);
if (dtUser.Rows.Count > 0)
{
DataRow dr = dtUser.Rows[0];
thisRider = new Rider();
thisRider.UserName = dr.Field<string>("Username");
thisRider.UserId= dr.Field<string>("UserId");
thisRider.BikeWheelSizeMm = (int)dr.Field<long>("BikeWheelSizeMm");
thisRider.CurrentTurbo = dr.Field<string>("Turbo");
thisRider.TurboIsCalibrated = (dr.Field<string>("TurboIsCalibrated") == bool.TrueString);
thisRider.EstimatedPower = (dr.Field<string>("EstimatedPower") == bool.TrueString);
}
db.Close();
return thisRider;
}
public static Dictionary<string, float[][]> Biometrics(string userId, string riderId, string raceId)
{
Race race = Race.Load(raceId);
Dictionary<string, float[][]> biometrics = new Dictionary<string, float[][]>();
SQLiteDatabase db = new SQLiteDatabase();
string sql = "select s.Time, s.Distance, s.Speed, s.Cadence, s.Hr, s.Power, r.TargetType, r.Configuration " +
"From cycli_race_spots s, cycli_races r " +
"where s.UserId =@u " +
"and s.RaceId = @r " +
"and s.RaceId = r.RaceId " +
"order by s.Time";
DataTable dt = db.GetDataTable(sql, "@u", riderId, "@r", raceId);
if (dt.Rows.Count > 0)
{
// Need to scale by race type
float[][] speedProfile = dt.AsEnumerable().Select(p => new float[] { (float)p.Field<dynamic>(race.RaceType.TargetProgress), (float)p.Field<double>("Speed") }).ToArray();
biometrics.Add("speed", speedProfile);
float[][] cadenceProfile = dt.AsEnumerable().Select(p => new float[] { (float)p.Field<dynamic>(race.RaceType.TargetProgress), (float)p.Field<long>("Cadence") }).ToArray();
biometrics.Add("cadence", cadenceProfile);
float[][] hrProfile = dt.AsEnumerable().Select(p => new float[] { (float)p.Field<dynamic>(race.RaceType.TargetProgress), (float)p.Field<long>("Hr") }).ToArray();
biometrics.Add("hr", hrProfile);
float[][] powerProfile = dt.AsEnumerable().Select(p => new float[] { (float)p.Field<dynamic>(race.RaceType.TargetProgress), (float)p.Field<long>("Power") }).ToArray();
biometrics.Add("power", powerProfile);
}
db.Close();
return biometrics;
}
public static void BailVirtualRider(string userId, string riderId, string raceId)
{
SQLiteDatabase db = new SQLiteDatabase();
string sql = "delete from cycli_race_riders " +
"where Status='Joined' and RaceId=@r and UserId=@f " +
"and exists (select 1 from cycli_races where RaceId=@r1 and Status='Planned' and RaceDirectorId=@u)";
db.ExecuteNonQuery(sql, "@r", raceId, "@f", riderId, "r1", raceId, "@u", userId);
db.Close();
Race race = Race.Load(raceId);
if (race != null)
{
CycliManager.Instance.SendRiderBail(race, riderId);
}
}
public static Profile Load(string profileId)
{
Profile profile = null;
string sql = "select p.UserId, p.ProfileId, p.Name, p.Description, p.Favourite, p.CreatedOn, p.ModifiedOn, s.X, s.Y " +
"from cycli_profiles p, cycli_profile_spots s " +
"where " +
"s.ProfileId = p.ProfileId " +
"and p.ProfileId = '" + profileId + "' " +
"order by s.PointOrder";
SQLiteDatabase db = new SQLiteDatabase();
DataTable dt = db.GetDataTable(sql);
foreach (DataRow dr in dt.Rows)
{
if (profile == null)
{
// New profile
profile = new Profile();
profileId = (string)dr["ProfileId"];
profile.UserId = (string)dr["ProfileId"];
profile.ProfileId = profileId;
profile.Favourite = ((string)dr["Favourite"] == bool.TrueString);
profile.Name = (string)dr["Name"];
profile.Description = (string)dr["Description"];
}
float[] pt = { (float)(long)dr["X"], (float)(long)dr["Y"] };
profile.data.Add(pt);
}
db.Close();
return profile;
}
public static void SetRiderHandicap(string userId, string riderId, string raceId, int handicap)
{
SQLiteDatabase db = new SQLiteDatabase();
// Update with checks on race status and ownership
string sql = @"update cycli_race_riders set Handicap=@h " +
"where RaceId=@r and UserId=@rr and (Status='Invited' or Status='Joined') " +
"and exists (select 1 from cycli_races where RaceId=@r1 and Status='Planned' and RaceDirectorId=@u)";
db.ExecuteNonQuery(sql, "@h", handicap, "@r", raceId, "@rr", riderId,"@r1",raceId, "@u", userId);
db.Close();
}
public static void JoinVirtualRider(string userId, string riderId, string raceId)
{
// What is the race status
// Validation occurs first on the client - this is the backstop
UserRider owner = UserRider.Load(userId);
if (owner != null)
{
// Query checks that race is in the right mode, the invitee is not already invited and that the invite limit has not been exceeded
SQLiteDatabase db = new SQLiteDatabase();
// This query checks that the user has not already been invited
string sql = "insert into cycli_race_riders (UserId, RaceId, Status, RiderType) " +
"select @f, @r, 'Joined', 'VIRTUAL' where exists (select 1 from cycli_races where " +
"RaceId=@r1 and Status='Planned' and RaceDirectorId=@u) " +
"and not exists (select 1 from cycli_race_riders where RaceId=@r1 and UserId=@f1) " +
"and (select count(*) from cycli_race_riders where raceId=@r2) < @c ";
db.ExecuteNonQuery(sql, "@f", riderId, "@r", raceId, "@r1", raceId, "@u", userId, "@f1", riderId, "@r2", raceId, "@c", owner.MaximumRiders);
db.Close();
Race race = Race.Load(raceId);
if (race != null)
{
CycliManager.Instance.SendRiderJoin(race, riderId);
}
}
}
public void FinishRider(string userId)
{
SQLiteDatabase db = new SQLiteDatabase();
try
{
string sqlRiderUpdate = @"update cycli_race_riders set Status='Finished' " +
"where RaceId=@r and UserId=@u and Status='Started'";
db.ExecuteNonQuery(sqlRiderUpdate, "@r", this.RaceId, "@u", userId);
}
finally
{
db.Close();
}
// SHould always be here
_Participants[userId].Status = @"Finished";
}
public static Race Load(string raceId)
{
Race race = null;
SQLiteDatabase db = new SQLiteDatabase();
string sql = "select r.RaceId as RaceId, r.Name as Name, r.Description as Description, " +
"r.StartDateTime as StartDateTime, r.TargetType as TargetType, r.Configuration as Configuration , " +
"r.RaceDirectorId as RaceDirectorId, " +
"r.PNS as PNS, r.Drafting as Drafting, r.HandicapType as HandicapType, " +
"r.ProfileId as ProfileId, r.ProfileType as ProfileType, " +
"r.Minimum as Minimum, r.Maximum as Maximum,r.Status as RaceStatus, u.UserId as UserId , u.UserName as UserName, " +
"u.ThresholdPower as ThresholdPower,rr.Status as Status, rr.Position as Position, rr.Distance as Distance, rr.Time as Time, rr.Energy as Energy, " +
"rr.PedalStrokes as PedalStrokes, rr.Heartbeats as Heartbeats, rr.TSS as TSS, " +
"rr.RiderType as RiderType, rr.Handicap as Handicap " +
"From cycli_races r, cycli_race_riders rr, cycli_riders u " +
"where " +
"u.UserId = rr.UserId and " +
"rr.RaceId = r.RaceId and " +
"r.RaceId = @r1 " +
"union " +
"select r.RaceId as RaceId, r.Name as Name, r.Description as Description, " +
"r.StartDateTime as StartDateTime, r.TargetType as TargetType, r.Configuration as Configuration , " +
"r.RaceDirectorId as RaceDirectorId, " +
"r.PNS as PNS, r.Drafting as Drafting, r.HandicapType as HandicapType, " +
"r.ProfileId as ProfileId, r.ProfileType as ProfileType, " +
"r.Minimum as Minimum, r.Maximum as Maximum,r.Status as RaceStatus, u.UserId as UserId , u.UserName as UserName, " +
"u.Power1Hr as ThresholdPower,rr.Status as Status, rr.Position as Position, rr.Distance as Distance, rr.Time as Time, rr.Energy as Energy, " +
"rr.PedalStrokes as PedalStrokes, rr.Heartbeats as Heartbeats, rr.TSS as TSS, "+
"rr.RiderType as RiderType, rr.Handicap as Handicap " +
"From cycli_races r, cycli_race_riders rr, cycli_virtual_riders u " +
"where " +
"u.UserId = rr.UserId and " +
"rr.RaceId = r.RaceId and " +
"r.RaceId = @r2 " +
" order by UserName";
DataTable dtRaces = db.GetDataTable(sql, "@r1", raceId, "@r2", raceId);
db.Close();
Race[] races = BuildRaceArray(dtRaces);
if (races.Length > 0)
{
race = races[0];
}
return race;
}
public static VirtualRider New(string userId)
{
VirtualRider r = new VirtualRider();
UserRider u = UserRider.Load(userId);
r.UserId = Guid.NewGuid().ToString();
r.OwnerId = userId;
r.UserName = "******";
r.BikeWheelSizeMm = 700;
r.EstimatedPower = false;
r.TurboIsCalibrated = false;
r.CurrentTurbo = "";
r.Aggression = 50;
r.PowerMin = 150;
r.Power1Hr = 200;
r.Power5Min = 250;
r.Power1Min = 300;
r.Power5Sec = 500;
// It's a new profile
string sqlCount = @"select count(*) from cycli_virtual_riders where OwnerId=@o";
string sqlRider = @"insert into cycli_virtual_riders (UserId, OwnerId, Username, PowerMinimum, Power1Hr, Power5Min, Power1Min, Power5Sec, Aggression) " +
"values (@u, @o, @n, @p4, @p3, @p2, @p1, @p0, @a) ";
SQLiteDatabase db = new SQLiteDatabase();
try
{
// This is a backstop test - the front end should prevent extra riders
if (int.Parse(db.ExecuteScalar(sqlCount, "@o", userId)) < u.MaximumVirtualRiders)
{
db.ExecuteNonQuery(sqlRider,
"@u", r.UserId,
"@o", r.OwnerId,
"@n", r.UserName,
"@p4", r.PowerMin,
"@p3", r.Power1Hr,
"@p2", r.Power5Min,
"@p1", r.Power1Min,
"@p0", r.Power5Sec,
"@a", r.Aggression);
}
}
finally
{
db.Close();
}
return r;
}
public static RaceResult[] LoadHistories(string userId)
{
RaceResult[] res = new RaceResult[] { };
SQLiteDatabase db = new SQLiteDatabase();
string sql = "select r.raceId as RaceId, r.Name as Name, r.StartDateTime as StartDateTime, " +
"u.UserId as UserId, u.UserName as UserName, rr.Distance as Distance, rr.Time as Time, rr.Energy as Energy, " +
"rr.PedalStrokes as PedalStrokes, rr.Heartbeats as Heartbeats, rr.TSS as TSS " +
"from cycli_race_riders rr, cycli_races r, cycli_riders u " +
"where u.userId = @u " +
"and rr.raceId = r.raceId " +
"and rr.userid = u.userid " +
"and (rr.Status = 'Finished' or rr.Status = 'Abandoned') and rr.Distance > 0 and rr.Time > 0 " +
"order by r.StartDateTime";
long secsNow = DbTime.ToDbSecs(DateTime.UtcNow);
DataTable dtResults = db.GetDataTable(sql, "@u", userId);
if(dtResults.Rows.Count > 0) {
res = dtResults.AsEnumerable()
.Where(p => p.Field<long>("Time") > 0)
.Select(p => new RaceResult() {
RaceId = p.Field<string>("RaceId"),
RaceName = p.Field<string>("Name"),
DeltaStartDateTime = p.Field<long>("StartDateTime") - secsNow,
UserId = p.Field<string>("UserId"),
UserName = p.Field<string>("UserName"),
Distance = 0.001f * (float)p.Field<double>("Distance"),
Time = (float)p.Field<long>("Time")/60000f,
Energy = (int)(p.Field<long>("Energy")),
TSS = (int)(p.Field<long>("TSS")),
MeanSpeed = 3600f * (float)p.Field<double>("Distance") / (float)p.Field<long>("Time"),
MeanCadence = (int)(60000 * p.Field<long>("PedalStrokes") / (float)p.Field<long>("Time")),
MeanHeartrate = (int)(60000 * p.Field<long>("Heartbeats") / (float)p.Field<long>("Time")),
MeanPower = (int)(1000 * p.Field<long>("Energy") / (float)p.Field<long>("Time"))
}).ToArray();
}
db.Close();
return res;
}
public static VirtualRider[] LoadAll(string userId)
{
List<VirtualRider> riders = new List<VirtualRider>();
SQLiteDatabase db = new SQLiteDatabase();
string sql = @"select UserId, Username, PowerMinimum, Power1Hr, Power5Min, Power1Min, Power5Sec, Aggression " +
"From cycli_virtual_riders r " +
"where OwnerId=@u and Status='Active'";
// Only load active accounts
DataTable dtUser = db.GetDataTable(sql,"@u", userId);
foreach (DataRow dr in dtUser.Rows)
{
VirtualRider thisRider = new VirtualRider();
thisRider.UserName = dr.Field<string>("Username");
thisRider.UserId = dr.Field<string>("UserId");
thisRider.OwnerId = userId;
thisRider.BikeWheelSizeMm = 700;
thisRider.PowerMin = (int)dr.Field<long>("PowerMinimum");
thisRider.Power1Hr = (int)dr.Field<long>("Power1Hr");
thisRider.Power5Min = (int)dr.Field<long>("Power5Min");
thisRider.Power1Min = (int)dr.Field<long>("Power1Min");
thisRider.Power5Sec = (int)dr.Field<long>("Power5Sec");
thisRider.Aggression = (int)dr.Field<long>("Aggression");
riders.Add(thisRider);
}
db.Close();
return riders.ToArray();
}
public static Race LoadLastRace(string userId)
{
Race[] rs = new Race[] { };
SQLiteDatabase db = new SQLiteDatabase();
string lastSql = "select r.raceId from cycli_race_riders rr, cycli_races r " +
"where rr.userId = @u " +
"and rr.raceId = r.raceId " +
"and (r.Status = 'Finished' or r.Status = 'Abandoned') " +
"order by r.StartDateTime desc limit 1";
string raceId = db.ExecuteScalar(lastSql, "@u", userId);
if (!string.IsNullOrEmpty(raceId))
{
string sql = "select r.RaceId as RaceId, r.Name as Name, r.Description as Description, " +
"r.StartDateTime as StartDateTime, r.TargetType as TargetType, r.Configuration as Configuration , r.RaceDirectorId as RaceDirectorId, " +
"r.PNS as PNS, r.Drafting as Drafting, r.HandicapType as HandicapType, r.ProfileId as ProfileId, r.ProfileType as ProfileType, " +
"r.Minimum as Minimum, r.Maximum as Maximum,r.Status as RaceStatus, u.UserId as UserId , u.UserName as UserName, " +
"u.ThresholdPower as ThresholdPower,rr1.Status as Status, rr1.Position as Position, rr1.Distance as Distance, rr1.Time as Time, rr1.Energy as Energy, " +
"rr1.PedalStrokes as PedalStrokes, rr1.Heartbeats as Heartbeats, rr1.TSS as TSS, " +
"rr1.RiderType as RiderType, rr1.Handicap as Handicap " +
"From cycli_races r, cycli_race_riders rr1, cycli_riders u " +
"where u.UserId = rr1.UserId " +
"and r.RaceId = rr1.RaceId " +
"and r.RaceId = @r " +
"and not rr1.Status ='Invited' " +
"union " +
"select r.RaceId as RaceId, r.Name as Name, r.Description as Description, " +
"r.StartDateTime as StartDateTime, r.TargetType as TargetType, r.Configuration as Configuration , r.RaceDirectorId as RaceDirectorId, " +
"r.PNS as PNS, r.Drafting as Drafting, r.HandicapType as HandicapType, r.ProfileId as ProfileId, r.ProfileType as ProfileType, " +
"r.Minimum as Minimum, r.Maximum as Maximum,r.Status as RaceStatus, u.UserId as UserId , u.UserName as UserName, " +
"u.Power1Hr as ThresholdPower,rr1.Status as Status, rr1.Position as Position, rr1.Distance as Distance, rr1.Time as Time, rr1.Energy as Energy, " +
"rr1.PedalStrokes as PedalStrokes, rr1.Heartbeats as Heartbeats, rr1.TSS as TSS, " +
"rr1.RiderType as RiderType, rr1.Handicap as Handicap " +
"From cycli_races r, cycli_race_riders rr1, " +
"cycli_virtual_riders u " +
"where u.UserId = rr1.UserId " +
"and r.RaceId = rr1.RaceId " +
"and r.RaceId = @r " +
" order by Position";
DataTable dtRaces = db.GetDataTable(sql, "@r", raceId);
rs = BuildRaceArray(dtRaces);
}
db.Close();
return rs.Length > 0 ? rs[0] : null;
}
public static Friend MakeFriend(string userId, string friendCode)
{
Friend friend = null;
UserRider rider = UserRider.Load(userId);
if (rider != null)
{
Friend[] existingFriends = Load(userId);
SQLiteDatabase db = new SQLiteDatabase();
// Some logic here - we only allow a new friend if
// (a) The friendship does not already exist
// (b) The friendCode is correct
// (c) We haven't exeeded out friend limit
string sqlFriend = "select UserId, UserName from cycli_riders where FriendCode = @c";
DataTable dt = db.GetDataTable(sqlFriend, "@c", friendCode);
if (dt.Rows.Count > 0)
{
string friendId = dt.Rows[0].Field<string>("UserId");
string friendName = dt.Rows[0].Field<string>("UserName");
if (existingFriends.Count() < rider.MaximumFriends && existingFriends.Count(p => p.UserId == friendId) == 0)
{
string sql = "insert into cycli_friends (UserId, FriendId, Status) values (@u, @f, 'Accepted')";
if (db.ExecuteNonQuery(sql, "@u", userId, "@f", friendId) == 1)
{
friend = new Friend { UserName = friendName, UserId = friendId, Status = "Accepted" };
}
}
}
// This query checks that the user has not already been invited
db.Close();
}
return friend;
}
public static Race[] LoadScheduledOrRacing(string userId)
{
SQLiteDatabase db = new SQLiteDatabase();
// Only load if
// (a) All planned associated with the user
// (b) Started or Finished if the user is Joined, Finished or Abandoned
// REVIEW PERFORMANCE OF THE UNION IN THIS QUERY
string sql = "select r.RaceId as RaceId, r.Name as Name, r.Description as Description, " +
"r.StartDateTime as StartDateTime, r.TargetType as TargetType, r.Configuration as Configuration , r.RaceDirectorId as RaceDirectorId, " +
"r.PNS as PNS, r.Drafting as Drafting, r.HandicapType as HandicapType, r.ProfileId as ProfileId, " +
"r.ProfileType as ProfileType, " +
"r.Minimum as Minimum, r.Maximum as Maximum,r.Status as RaceStatus, u.UserId as UserId , u.UserName as UserName, " +
"u.ThresholdPower as ThresholdPower,rr1.Status as Status, rr1.Position as Position, rr1.Distance as Distance, rr1.Time as Time, rr1.Energy as Energy, " +
"rr1.PedalStrokes as PedalStrokes, rr1.Heartbeats as Heartbeats, rr1.TSS as TSS, " +
"rr1.RiderType as RiderType, rr1.Handicap as Handicap " +
"From cycli_races r, cycli_race_riders rr, cycli_race_riders rr1, " +
"cycli_riders u " +
"where rr.UserId = @u1 and " +
"rr.RaceId = rr1.RaceId and " +
"u.UserId = rr1.UserId and " +
"rr.RaceId = r.RaceId and " +
"(r.Status = 'Scheduled' or r.Status = 'Started') " +
"union " +
"select r.RaceId as RaceId, r.Name as Name, r.Description as Description, " +
"r.StartDateTime as StartDateTime, r.TargetType as TargetType, r.Configuration as Configuration , r.RaceDirectorId as RaceDirectorId, " +
"r.PNS as PNS, r.Drafting as Drafting, r.HandicapType as HandicapType, r.ProfileId as ProfileId, r.ProfileType as ProfileType, " +
"r.Minimum as Minimum, r.Maximum as Maximum,r.Status as RaceStatus, u.UserId as UserId , u.UserName as UserName, " +
"u.Power1Hr as ThresholdPower,rr1.Status as Status, rr1.Position as Position, rr1.Distance as Distance, rr1.Time as Time, rr1.Energy as Energy, " +
"rr1.PedalStrokes as PedalStrokes, rr1.Heartbeats as Heartbeats, rr1.TSS as TSS, " +
"rr1.RiderType as RiderType, rr1.Handicap as Handicap " +
"From cycli_races r, cycli_race_riders rr, cycli_race_riders rr1, " +
"cycli_virtual_riders u " +
"where rr.UserId = @u2 and " +
"rr.RaceId = rr1.RaceId and " +
"u.UserId = rr1.UserId and " +
"rr.RaceId = r.RaceId and " +
"(r.Status = 'Scheduled' or r.Status = 'Started') " +
" order by r.StartDateTime, r.RaceId";
DataTable dtRaces = db.GetDataTable(sql, "@u1", userId, "@u2", userId);
db.Close();
Race[] rs = BuildRaceArray(dtRaces);
return rs;
}
public static void Remove(string userId, string friendId)
{
SQLiteDatabase db = new SQLiteDatabase();
// This query checks that the user has not already been invited
string sql = "delete from cycli_friends where (userId=@u1 and friendId=@f1) or (friendId=@u2 and userId=@f2)";
db.ExecuteNonQuery(sql, "@u1", userId, "@f1", friendId, "@u2", userId, "@f2", friendId);
db.Close();
}
// Initiates race processors when required. Responsbile for creating the
// race object passed to the client during a race
public static Race[] ScheduleRaces(long start)
{
SQLiteDatabase db = new SQLiteDatabase();
string sql = "select r.RaceId as RaceId, r.Name as Name, r.Description as Description, " +
"r.StartDateTime as StartDateTime, r.TargetType as TargetType, r.Configuration as Configuration , r.RaceDirectorId as RaceDirectorId, " +
"r.PNS as PNS, r.Drafting as Drafting, r.HandicapType as HandicapType, r.ProfileId as ProfileId, r.ProfileType as ProfileType, " +
"r.Minimum as Minimum, r.Maximum as Maximum,r.Status as RaceStatus, u.UserId as UserId , u.UserName as UserName, " +
"u.ThresholdPower as ThresholdPower,rr.Status as Status, rr.Position as Position, rr.Result as Result, rr.Distance as Distance, rr.Time as Time, rr.Energy as Energy, " +
"rr.PedalStrokes as PedalStrokes, rr.Heartbeats as Heartbeats, rr.TSS as TSS, " +
"rr.RiderType as RiderType, rr.Handicap as Handicap " +
"From cycli_races r, cycli_race_riders rr, cycli_riders u " +
"where " +
"rr.RaceId = r.RaceId and " +
"rr.UserId = u.UserId and " +
"RaceStatus ='Planned' and " +
"r.StartDateTime < @s1 " +
"union " +
"select r.RaceId as RaceId, r.Name as Name, r.Description as Description, " +
"r.StartDateTime as StartDateTime, r.TargetType as TargetType, r.Configuration as Configuration , r.RaceDirectorId as RaceDirectorId, " +
"r.PNS as PNS, r.Drafting as Drafting, r.HandicapType as HandicapType, r.ProfileId as ProfileId, r.ProfileType as ProfileType, " +
"r.Minimum as Minimum, r.Maximum as Maximum,r.Status as RaceStatus, u.UserId as UserId , u.UserName as UserName, " +
"u.Power1Hr as ThresholdPower,rr.Status as Status, rr.Position as Position, rr.Result as Result, rr.Distance as Distance, rr.Time as Time, rr.Energy as Energy, " +
"rr.PedalStrokes as PedalStrokes, rr.Heartbeats as Heartbeats, rr.TSS as TSS, " +
"rr.RiderType as RiderType, rr.Handicap as Handicap " +
"From cycli_races r, cycli_race_riders rr, cycli_virtual_riders u " +
"where " +
"rr.RaceId = r.RaceId and " +
"rr.UserId = u.UserId and " +
"RaceStatus ='Planned' and " +
"r.StartDateTime < @s2 " +
" order by r.StartDateTime, r.RaceId";
DataTable dtRaces = db.GetDataTable(sql, "@s1", start, "@s2", start);
db.Close();
return BuildRaceArray(dtRaces);
}
public static long[] GetDates(string userId)
{
SQLiteDatabase db = new SQLiteDatabase();
string sql = "select r.StartDateTime " +
"From cycli_races r, cycli_race_riders rr " +
"where rr.UserId ='" + userId + "' and " +
"rr.RaceId = r.RaceId and " +
"r.Status = 'Finished' and " +
"not rr.Status ='Invited' " +
"order by r.StartDateTime";
DataTable dtRaces = db.GetDataTable(sql);
db.Close();
List<long> dates = new List<long>();
foreach (DataRow dr in dtRaces.Rows)
{
dates.Add(dr.Field<long>("StartDateTime"));
}
return dates.ToArray();
}
public override void SaveDetails(string userId)
{
SQLiteDatabase db = new SQLiteDatabase();
// Query forces identity
string sql = @"update cycli_virtual_riders set Username=@n, PowerMinimum=@p4, Power1Hr=@p3, " +
"Power5Min=@p2, Power1Min=@p1, Power5Sec=@p0, Aggression=@a " +
"where OwnerId=@u and UserId=@r ";
db.ExecuteNonQuery(sql, "@n", this.UserName, "@p4", this.PowerMin, "@p3", this.Power1Hr,
"@p2", this.Power5Min, "@p1", this.Power1Min, "@p0", this.Power5Sec,"@a", this.Aggression,
"@u", userId, "@r", this.UserId);
db.Close();
}
public static void JoinRace(string userId, string raceId)
{
// What is the race status
SQLiteDatabase db = new SQLiteDatabase();
string sql = @"update cycli_race_riders set Status='Joined' where UserId=@u and RaceId In (" +
"select r.RaceId from cycli_races r, cycli_race_riders rr where r.RaceId=@r and r.RaceId = rr.RaceId and r.Status='Planned')";
db.ExecuteNonQuery(sql, "@r", raceId, "@u", userId);
db.Close();
Race race = Race.Load(raceId);
if (race != null)
{
CycliManager.Instance.SendRiderJoin(race, userId);
}
}
public static TurboTrainer Load(string userId, string turboType, bool turboIsCalibrated)
{
TurboTrainer thisTurbo = null;
SQLiteDatabase db = new SQLiteDatabase();
string sqlNonCalibrated = @"select t.type, t.power_model_c1, t.power_model_c2, t.power_model_c3 from " +
"cycli_turbos t where t.type=@t";
string sqlCalibrated = @"select t.type, t.power_model_c1, t.power_model_c2, t.power_model_c3 from " +
"cycli_rider_turbos t where t.UserId=@u and t.type=@t";
DataTable dtUser = null;
if (turboIsCalibrated)
{
dtUser = db.GetDataTable(sqlCalibrated,"@u",userId,"@t",turboType);
// Not defined yet - use the non standard values
if (dtUser.Rows.Count == 0)
{
dtUser = db.GetDataTable(sqlNonCalibrated, "@t", turboType);
}
}
else
{
dtUser = db.GetDataTable(sqlNonCalibrated, "@t", turboType);
}
if (dtUser.Rows.Count > 0)
{
DataRow dr = dtUser.Rows[0];
thisTurbo = new TurboTrainer();
thisTurbo.UserId = userId;
thisTurbo.Type = (string)(dr["type"]);
thisTurbo.Coefficients = new double[]{(double)dr["power_model_c1"],(double)dr["power_model_c2"],(double)dr["power_model_c3"]};
}
db.Close();
return thisTurbo;
}
