/// <summary>
/// Restituisce l'elemento Schedina specificato dal parametro, se presente nel DB.
/// </summary>
/// <param name="t">string titolo della schedina</param>
/// <returns>Schedina</returns>
public async Task <Schedina> GetSchedina(string t)
{
Schedina s = new Schedina();
string messageForLog = string.Empty;
try
{
Database dbInstance = GetSQLiteConnection();
await dbInstance.OpenAsync(SqliteOpenMode.OpenRead);
string query = " SELECT * FROM " + Constants.TABLE_NAME_SCHEDINA + " WHERE Title=@Title";
using (Statement statement = await dbInstance.PrepareStatementAsync(query))
{
statement.BindTextParameterWithName("@Title", t);
if (await statement.StepAsync())
{
s.Id = statement.GetIntAt(0);
s.Title = statement.GetTextAt(1);
messageForLog = "Schedina trovata: Id = " + s.Id.ToString();
}
}
}
catch (Exception ex)
{
messageForLog = ex.Message;
}
finally
{
System.Diagnostics.Debug.WriteLine("[DATAACCESSDB - GetSchedina] \t" + messageForLog);
}
return(s);
}
/// <summary>
/// Consente di aggiornare un elemento Schedina presente nel DB.
/// </summary>
/// <param name="s">Schedina</param>
public async Task UpdateSchedina(Schedina s)
{
Database dbInstance = GetSQLiteConnection();
await dbInstance.OpenAsync(SqliteOpenMode.OpenReadWrite);
// TODO
System.Diagnostics.Debug.WriteLine("[DATAACCESSDB - UpdateSchedina] \t" + "Oggetto Schedina aggiornato nel DB!");
}
/// <summary>
/// Esegue l'Update di una scommessa nel DB.
/// </summary>
/// <param name="i">Incontro Oggetto con i dati dell'incontro con le quote della scommessa effettuata</param>
/// <param name="s">Schedina Oggetto con i dati della schedina in corso di modifica.</param>
/// <returns>bool False se ci sono eccezioni, True altrimenti.</returns>
public async Task <bool> UpdateScommessa(Incontro i, Schedina s)
{
bool isUpdated = false;
string messageForLog = string.Empty;
try
{
Database dbInstance = GetSQLiteConnection();
await dbInstance.OpenAsync(SqliteOpenMode.OpenReadWrite);
// Una scommessa e' composta da piu' incontri, dove per ogni incontro e' possibile
// giocare diverse quote.
// Query per inserire i dati dell'incontro, se non presente. Ogni incontro e' identificato
// da una serie di chiavi primarie.
string querySql1 = " INSERT OR IGNORE INTO " + Constants.TABLE_NAME_SCOMMESSA +
" (IdScommessa, TeamCasa, TeamFCasa, Data, IdMatch) " +
" VALUES (@IdScommessa, @TeamCasa, @TeamFCasa, @Data, @IdMatch) ";
using (Statement statement = await dbInstance.PrepareStatementAsync(querySql1))
{
statement.BindIntParameterWithName("@IdScommessa", s.Id);
statement.BindTextParameterWithName("@TeamCasa", i.TeamCasa);
statement.BindTextParameterWithName("@TeamFCasa", i.TeamFCasa);
statement.BindTextParameterWithName("@Data", i.Data);
statement.BindTextParameterWithName("@IdMatch", i.IdMatch);
await statement.StepAsync();
}
// Query per inserire le quote per un determinato incontro. Se ho valori, effettuo l'update, altrimenti lascio
// il valore presente.
string querySql2 = " UPDATE " + Constants.TABLE_NAME_SCOMMESSA +
" SET " +
" TotalScore = case when coalesce('" + i.TotalScore + "', '') = '' then TotalScore else '" + i.TotalScore + "' end, " +
" Over = case when coalesce('" + i.OVER + "', '') = '' then Over else '" + i.OVER + "' end, " +
" Under = case when coalesce('" + i.UNDER + "', '') = '' then Under else '" + i.UNDER + "' end, " +
" Q1 = case when coalesce('" + i.Q1 + "', '') = '' then Q1 else '" + i.Q1 + "' end, " +
" Qx = case when coalesce('" + i.QX + "', '') = '' then Qx else '" + i.QX + "' end, " +
" Q2 = case when coalesce('" + i.Q2 + "', '') = '' then Q2 else '" + i.Q2 + "' end, " +
////" Q1 = '" + i.Q1 + "'," +
////" Qx = '" + i.QX + "'," +
////" Q2 = '" + i.Q2 + "'," +
" Hc = case when coalesce('" + i.HC + "', '') = '' then Hc else '" + i.HC + "' end, " +
" Hc1 = case when coalesce('" + i.HC1 + "', '') = '' then Hc1 else '" + i.HC1 + "' end, " +
" Hc2 = case when coalesce('" + i.HC2 + "', '') = '' then Hc2 else '" + i.HC2 + "' end, " +
" Hcx = case when coalesce('" + i.HCX + "', '') = '' then Hcx else '" + i.HCX + "' end, " +
" Dc1x = case when coalesce('" + i.DC1X + "', '') = '' then Dc1x else '" + i.DC1X + "' end, " +
" Dc12 = case when coalesce('" + i.DC12 + "', '') = '' then Dc12 else '" + i.DC12 + "' end, " +
" Dcx2 = case when coalesce('" + i.DCX2 + "', '') = '' then Dcx2 else '" + i.DCX2 + "' end, " +
" Home12 = case when coalesce('" + i.Home12 + "', '') = '' then Home12 else '" + i.Home12 + "' end, " +
" Away12 = case when coalesce('" + i.Away12 + "', '') = '' then Away12 else '" + i.Away12 + "' end " +
" WHERE " +
" IdScommessa = '" + s.Id + "'" +
" AND " +
" TeamCasa = '" + i.TeamCasa + "'" +
" AND " +
" TeamFCasa = '" + i.TeamFCasa + "'" +
" AND " +
" Data = '" + i.Data + "'" +
" AND " +
" IdMatch = '" + i.IdMatch + "'";
using (Statement statement = await dbInstance.PrepareStatementAsync(querySql2))
{
System.Diagnostics.Debug.WriteLine("[DATAACCESSDB - UpdateScommessa] \t" + querySql2.ToString());
await statement.StepAsync();
isUpdated = true;
messageForLog = "Query eseguita con successo";
}
}
catch (Exception ex)
{
isUpdated = false;
messageForLog = ex.Message;
}
finally
{
System.Diagnostics.Debug.WriteLine("[DATAACCESSDB - UpdateScommessa] \t" + messageForLog + "\n");
}
return(isUpdated);
}
public async Task <Scommessa> GetScommessa(Incontro i, Schedina s)
{
Scommessa scommessa = null;
string messageForLog = string.Empty;
try
{
Database dbInstance = GetSQLiteConnection();
await dbInstance.OpenAsync(SqliteOpenMode.OpenRead);
string query =
" SELECT * FROM " + Constants.TABLE_NAME_SCOMMESSA +
" INNER JOIN " + Constants.TABLE_NAME_SCHEDINA +
" ON " + Constants.TABLE_NAME_SCOMMESSA + ".IdScommessa" +
" = " +
Constants.TABLE_NAME_SCHEDINA + ".Id" +
" WHERE " +
" IdScommessa = '" + s.Id + "'" +
" AND " +
" TeamCasa = '" + i.TeamCasa + "'" +
" AND " +
" TeamFCasa = '" + i.TeamFCasa + "'" +
" AND " +
" Data = '" + i.Data + "'" +
" AND " +
" IdMatch = '" + i.IdMatch + "'"
;
using (Statement statement = await dbInstance.PrepareStatementAsync(query))
{
statement.EnableColumnsProperty();
System.Diagnostics.Debug.WriteLine("[DATAACCESSDB - GetScommessa] \t" + query.ToString());
while (await statement.StepAsync())
{
var columns = statement.Columns;
scommessa = new Scommessa
{
IdScommessa = int.Parse(columns["IdScommessa"]),
TeamCasa = columns["TeamCasa"],
TeamFCasa = columns["TeamFCasa"],
Data = columns["Data"],
IdMatch = columns["IdMatch"],
TotalScore = columns["TotalScore"],
OVER = columns["Over"],
UNDER = columns["Under"],
Q1 = columns["Q1"],
QX = columns["Qx"],
Q2 = columns["Q2"],
HC = columns["Hc"],
HC1 = columns["Hc1"],
HC2 = columns["Hc2"],
HCX = columns["Hcx"],
DC1X = columns["Dc1x"],
DC12 = columns["Dc12"],
DCX2 = columns["Dcx2"],
Home12 = columns["Home12"],
Away12 = columns["Away12"]
};
}
messageForLog = "Query eseguita con successo";
}
}
catch (Exception ex)
{
messageForLog = ex.Message;
}
finally
{
System.Diagnostics.Debug.WriteLine("[DATAACCESSDB - GetScommessa] \t" + messageForLog + "\n");
}
return(scommessa);
}