// Delete record by id
void DeleteRecord(long id, string deleteSQL)
{
using (SQLiteConnection conn = dBManager.GetConnection())
{
SQLiteCommand command = conn.CreateCommand();
command.CommandText = deleteSQL;
command.Parameters.Add(new SQLiteParameter("id", id));
conn.Open();
command.ExecuteNonQuery();
}
}
// Insert Club record
public void InsertClubRecord(long placeid, string cname)
{
using (SQLiteConnection conn = dBManager.GetConnection())
{
SQLiteCommand command = conn.CreateCommand();
command.CommandText = dBManager.sqlLibrary["insertClub"];
command.Parameters.Add(new SQLiteParameter("cname", cname));
command.Parameters.Add(new SQLiteParameter("placeid", placeid));
conn.Open();
command.ExecuteNonQuery();
}
}
// Insert place record
private void InsertPlaceRecord(string pname, long id)
{
using (SQLiteConnection conn = dBManager.GetConnection())
{
SQLiteCommand command = conn.CreateCommand();
command.CommandText = dBManager.sqlLibrary["insertPlace"];
command.Parameters.Add(new SQLiteParameter("id", id));
command.Parameters.Add(new SQLiteParameter("pname", pname));
conn.Open();
command.ExecuteNonQuery();
}
}
// Insert shooter into Db
private void InsertShooterRecord(long clubid, string name, string surname, string note, string team)
{
using (SQLiteConnection conn = dBManager.GetConnection())
{
SQLiteCommand command = conn.CreateCommand();
command.CommandText = dBManager.sqlLibrary["insertShooter"];
command.Parameters.Add(new SQLiteParameter("name", name));
command.Parameters.Add(new SQLiteParameter("surname", surname));
command.Parameters.Add(new SQLiteParameter("note", note));
command.Parameters.Add(new SQLiteParameter("clubid", clubid));
command.Parameters.Add(new SQLiteParameter("team", team));
conn.Open();
command.ExecuteNonQuery();
}
}
// Insert tekma record
void InsertPCompetitionRecord(string title, long rangeid, string date, int tartype, int shots, int nRounds)
{
using (SQLiteConnection conn = dBManager.GetConnection())
{
SQLiteCommand command = conn.CreateCommand();
command.CommandText = dBManager.sqlLibrary["insertPCompetition"];
command.Parameters.Add(new SQLiteParameter("title", title));
command.Parameters.Add(new SQLiteParameter("rangeid", rangeid));
command.Parameters.Add(new SQLiteParameter("date", date));
command.Parameters.Add(new SQLiteParameter("tartype", tartype));
command.Parameters.Add(new SQLiteParameter("shots", shots));
command.Parameters.Add(new SQLiteParameter("rounds", nRounds));
command.Parameters.Add(new SQLiteParameter("finished", false));
conn.Open();
command.ExecuteNonQuery();
}
}
// IMPLEMENTACIJA LOADANJA IZ DB
public void LoadFromDB()
{
PreciseShooting competition = new PreciseShooting(long.Parse(labelId.Content.ToString()), labelTitle.Content.ToString(), labelRange.Content.ToString(), DateTime.Parse(labelDate.Content.ToString()), int.Parse(labelType.Content.ToString()), int.Parse(labelShots.Content.ToString()), int.Parse(labelRounds.Content.ToString()));
Shooter cshooter;
if (competition == null)
{
return;
}
try
{
using (SQLiteConnection conn = dBManager.GetConnection())
{
SQLiteCommand command = conn.CreateCommand();
command.CommandText = dBManager.sqlLibrary["selectForStatistics"];
command.Parameters.Add(new SQLiteParameter("cid", competition.Id));
conn.Open();
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
cshooter = new Shooter(long.Parse(reader["id"].ToString()), reader["name"].ToString(), reader["surname"].ToString(), reader["cname"].ToString(), reader["team"].ToString());
List <int?> nullhits = new List <int?>();
nullhits.Add(reader.GetInt32(6));
nullhits.Add(reader.GetInt32(7));
nullhits.Add(reader.GetInt32(8));
nullhits.Add(reader.GetInt32(9));
nullhits.Add(reader.GetInt32(10));
nullhits.Add(reader.GetInt32(11));
nullhits.Add(reader.GetInt32(12));
nullhits.Add(reader.GetInt32(13));
nullhits.Add(reader.GetInt32(14));
nullhits.Add(reader.GetInt32(15));
nullhits.Add(reader.GetInt32(16));
nullhits.Add(reader.GetInt32(17));
List <int> hits = new List <int>();
// Fill non null
foreach (int?hit in nullhits)
{
if (hit != null)
{
hits.Add((int)hit);
}
else
{
break;
}
}
// If shoots length discreptency break
if (hits.Count != 13 - competition.TarType)
{
MessageBox.Show("Data corupted!", "Error!");
return;
}
cshooter.ScoresPRound.Add(hits);
//Fill remaining hits with lists filled with zero
for (int i = 1; i < competition.Round; i++)
{
cshooter.ScoresPRound.Add(new List <int>(new int[13 - competition.TarType]));
}
// Add Constructed shooter
competition.Shooters.Add(cshooter);
}
}
}
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Exception");
}
competition.EndCompetition();
WindowStatistics statWin = new WindowStatistics(competition);
statWin.Show();
}
// IMPOLEMENTACIJA PISANJA V DB
void WriteToDB()
{
// Check if write is meaningful
if (Competition == null || Competition.Shooters.Count == 0)
{
return;
}
// Closes Competition
using (SQLiteConnection conn = dBManager.GetConnection())
{
SQLiteCommand command = conn.CreateCommand();
command.CommandText = dBManager.sqlLibrary["updateEndGame"];
command.Parameters.Add(new SQLiteParameter("id", Competition.Id));
command.Parameters.Add(new SQLiteParameter("finished", true));
conn.Open();
command.ExecuteNonQuery();
}
for (int i = 0; i < Competition.Shooters.Count; i++)
{
// Get total shot list
List <int> sTotal = Competition.Shooters[i].GetShoTotal();
using (SQLiteConnection conn = dBManager.GetConnection())
{
SQLiteCommand command = conn.CreateCommand();
command.CommandText = dBManager.sqlLibrary["insertShots"];
int stotal = sTotal.Count;
if (stotal >= 1)
{
command.Parameters.Add(new SQLiteParameter("fieldzero", sTotal[0]));
}
else
{
command.Parameters.Add(new SQLiteParameter("fieldzero", null));
}
if (stotal >= 2)
{
command.Parameters.Add(new SQLiteParameter("fieldone", sTotal[1]));
}
else
{
command.Parameters.Add(new SQLiteParameter("fieldone", null));
}
if (stotal >= 3)
{
command.Parameters.Add(new SQLiteParameter("fieldtwo", sTotal[2]));
}
else
{
command.Parameters.Add(new SQLiteParameter("fieldtwo", null));
}
if (stotal >= 4)
{
command.Parameters.Add(new SQLiteParameter("fieldthree", sTotal[3]));
}
else
{
command.Parameters.Add(new SQLiteParameter("fieldthree", null));
}
if (stotal >= 5)
{
command.Parameters.Add(new SQLiteParameter("fieldfour", sTotal[4]));
}
else
{
command.Parameters.Add(new SQLiteParameter("fieldfour", null));
}
if (stotal >= 6)
{
command.Parameters.Add(new SQLiteParameter("fieldfive", sTotal[5]));
}
else
{
command.Parameters.Add(new SQLiteParameter("fieldfive", null));
}
if (stotal >= 7)
{
command.Parameters.Add(new SQLiteParameter("fieldsix", sTotal[6]));
}
else
{
command.Parameters.Add(new SQLiteParameter("fieldsix", null));
}
if (stotal >= 8)
{
command.Parameters.Add(new SQLiteParameter("fieldseven", sTotal[7]));
}
else
{
command.Parameters.Add(new SQLiteParameter("fieldseven", null));
}
if (stotal >= 9)
{
command.Parameters.Add(new SQLiteParameter("fieldeight", sTotal[8]));
}
else
{
command.Parameters.Add(new SQLiteParameter("fieldeight", null));
}
if (stotal >= 10)
{
command.Parameters.Add(new SQLiteParameter("fieldnine", sTotal[9]));
}
else
{
command.Parameters.Add(new SQLiteParameter("fieldnine", null));
}
if (stotal >= 11)
{
command.Parameters.Add(new SQLiteParameter("fieldten", sTotal[10]));
}
else
{
command.Parameters.Add(new SQLiteParameter("fieldten", null));
}
if (stotal >= 12)
{
command.Parameters.Add(new SQLiteParameter("fieldeleven", sTotal[11]));
}
else
{
command.Parameters.Add(new SQLiteParameter("fieldeleven", null));
}
conn.Open();
command.ExecuteNonQuery();
}
// Get Latest id to identify shots
long MaxID;
using (SQLiteConnection conn = dBManager.GetConnection())
{
SQLiteCommand command = conn.CreateCommand();
command.CommandText = dBManager.sqlLibrary["selectShoID"];
conn.Open();
using (SQLiteDataReader reader = command.ExecuteReader())
{
reader.Read();
MaxID = (long)reader[0];
}
}
// Closes Competition
using (SQLiteConnection conn = dBManager.GetConnection())
{
SQLiteCommand command = conn.CreateCommand();
command.CommandText = dBManager.sqlLibrary["insertPresults"];
command.Parameters.Add(new SQLiteParameter("competitionid", Competition.Id));
command.Parameters.Add(new SQLiteParameter("shooterid", Competition.Shooters[i].Id));
command.Parameters.Add(new SQLiteParameter("shotsid", MaxID));
command.Parameters.Add(new SQLiteParameter("score", Competition.Shooters[i].totalScore()));
command.Parameters.Add(new SQLiteParameter("team", Competition.Shooters[i].Team));
conn.Open();
command.ExecuteNonQuery();
}
}
}