public bool Create(Hunt hunt, SQLiteConnection conn)
{
string insertDML = config.getValue("InsertHuntDML");
string insertHuntGuideDML = config.getValue("InsertHuntGuideDML");
try
{
//group_id, habitat_id, number_of_guns, birds_seen, birds_missed, crop, crop_harvested, hunt_date, pheasant_harvest, grouse_harvest, partridge_harvest, comment
SQLiteHelper.ExecuteDML(conn,
insertDML,
hunt.Group.GroupId,
hunt.Habitat.HabitatId,
hunt.NumberOfGuns,
hunt.Harvest.BirdsSeen,
hunt.Harvest.BirdsMissed,
hunt.Harvest.Crop,
hunt.Harvest.CropHarvested,
hunt.HuntDate,
hunt.Harvest.Pheasant,
hunt.Harvest.Grouse,
hunt.Harvest.Partridge,
hunt.Comments);
hunt.HuntId = GetHuntId(conn);
foreach (Guide guide in hunt.Guides)
{
SQLiteHelper.ExecuteDML(conn,
insertHuntGuideDML,
hunt.HuntId,
guide.GuideId);
}
}
catch (Exception e)
{
string exceptionMessage = "Exception caught when Creating new Hunt -> Group: " + hunt.Group.GroupName +
", Habitat: " + hunt.Habitat.HabitatName +
", Date: " + hunt.HuntDate.ToString();
log.Error(exceptionMessage, e);
throw new Exception(exceptionMessage, e);
}
log.Debug("Create completed sucessfully");
return true;
}
public bool Delete(Hunt hunt, SQLiteConnection conn)
{
string deleteDML = config.getValue("DeleteHuntDML");
string deleteHuntGuideDML = config.getValue("DeleteHuntGuideDML");
try
{
SQLiteHelper.ExecuteDML(conn,
deleteDML,
hunt.HuntId);
SQLiteHelper.ExecuteDML(conn,
deleteHuntGuideDML,
hunt.HuntId);
}
catch (Exception e)
{
string exceptionMessage = "Exception caught when Deleting Hunt " + hunt.HuntId;
log.Error(exceptionMessage, e);
throw new Exception(exceptionMessage, e);
}
log.Debug("Sucessfully delete Hunt " + hunt.HuntId);
return true;
}
private Hunt getHunt(Habitat habitat, Group group, List<Guide> guides, SQLiteConnection conn)
{
Harvest harvest = new Harvest()
{
Pheasant = rand.Next(3, 20),
Grouse = rand.Next(0, 6),
Partridge = rand.Next(0, 3),
BirdsSeen = rand.Next(0,100)*10,
BirdsMissed = rand.Next(0,15),
Crop = crops[rand.Next(0,crops.Length)],
CropHarvested = "Y"
};
if (rand.Next(0, 1) == 1)
{
harvest.CropHarvested = "N";
}
Hunt h = new Hunt()
{
Habitat = habitat,
Group = group,
Guides = guides,
NumberOfGuns = rand.Next(2,15),
Harvest = harvest,
HuntDate = new DateTime(2015, rand.Next(10,13), rand.Next(1,31)),
Comments = "Comment here"
};
return h;
}
private void AddGuidesToList(Hunt hunt)
{
foreach (Guide guide in hunt.Guides)
{
AddGuide(guide);
}
}
private void button_save_Click(object sender, EventArgs e)
{
log.Debug("Save button Clicked");
SQLiteConnection conn = SQLiteConnectionFactory.GetPrimaryDBConnection();
HuntDAO dao = new HuntDAO();
DataRow selectedDataRow;
Hunt hunt = new Hunt();
Habitat habitat = new Habitat();
Group group = new Group();
Harvest harvest = new Harvest();
selectedDataRow = ((DataRowView)comboBox_habitat.SelectedItem).Row;
int habitatId = Convert.ToInt32(selectedDataRow["habitat_id"]);
habitat.HabitatId = habitatId;
selectedDataRow = ((DataRowView)comboBox_group.SelectedItem).Row;
int groupId = Convert.ToInt32(selectedDataRow["group_id"]);
group.GroupId = groupId;
DateTime huntDate = datePicker_huntDate.Value.Date;
hunt.NumberOfGuns = (int)numericUpDown_numberOfGuns.Value;
harvest.Pheasant = (int)numericUpDown_pheasant.Value;
harvest.Grouse = (int)numericUpDown_grouse.Value;
harvest.Partridge = (int)numericUpDown_partridge.Value;
harvest.BirdsSeen = (int)numericUpDown_birdsSeen.Value;
harvest.BirdsMissed = (int)numericUpDown_birdsMissed.Value;
harvest.Crop = textBox_crop.Text;
harvest.CropHarvested = "";
if (radioButton_harvestYes.Checked == true)
{
harvest.CropHarvested = "Y";
}
else if (radioButton_harvestNo.Checked == true)
{
harvest.CropHarvested = "N";
}
string comments = textBox_comments.Text;
hunt.Habitat = habitat;
hunt.Group = group;
hunt.Guides = guides.ToList();
hunt.HuntDate = huntDate;
hunt.Harvest = harvest;
hunt.Comments = comments;
try
{
label_message.ForeColor = Color.Blue;
if (isUpdate)
{
hunt.HuntId = Convert.ToInt32(label_huntId.Text);
log.Debug("Attempting to update hunt " + hunt.HuntId);
dao.Update(hunt, conn);
label_message.Text = "Updated Hunt: " + hunt.HuntId;
}
else
{
log.Debug("Attempting to save new hunt");
dao.Create(hunt, conn);
label_message.Text = "Saved new Hunt";
this.Close();
}
}
catch (Exception ex)
{
log.Error("Error saving/updating hunt", ex);
label_message.ForeColor = Color.Red;
label_message.Text = ex.Message;
}
}
private void button_delete_Click(object sender, EventArgs e)
{
log.Debug("Delete button clicked");
HuntDAO dao = new HuntDAO();
SQLiteConnection conn = SQLiteConnectionFactory.GetPrimaryDBConnection();
label_message.ForeColor = Color.Blue;
if (label_huntId.Text.Length <= 0)
{
label_message.Text = "No Hunt Selected";
return;
}
int huntId = Convert.ToInt32(label_huntId.Text);
Hunt hunt = new Hunt();
hunt.HuntId = huntId;
try
{
log.Debug("Attempting to delete hunt " + huntId);
dao.Delete(hunt, conn);
label_message.Text = "Hunt " + huntId + " Deleted";
log.Debug("Hunt " + huntId + " Deleted");
button_save.Enabled = false;
button_delete.Enabled = false;
checkBox_allowDelete.Enabled = false;
}
catch (Exception ex)
{
log.Error("Error deleting hunt " + hunt.HuntId + ": " + ex.Message);
label_message.ForeColor = Color.Red;
label_message.Text = "Failed to delete";
}
}
public List<Hunt> Read(string query, SQLiteConnection conn)
{
List<Hunt> results = new List<Hunt>();
SQLiteCommand command = conn.CreateCommand();
log.Debug("Performing query: " + query);
command.CommandText = query;
try
{
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Hunt h = new Hunt();
int huntId = Convert.ToInt32(reader["hunt_id"]);
int habitatId = Convert.ToInt32(reader["habitat_id"]);
int groupId = Convert.ToInt32(reader["group_id"]);
int numberOfGuns = Convert.ToInt32(reader["number_of_guns"]);
int pheasantHarvest = Convert.ToInt32(reader["pheasant_harvest"]);
int grouseHarvest = Convert.ToInt32(reader["grouse_harvest"]);
int partridgeHarvest = Convert.ToInt32(reader["partridge_harvest"]);
int birdsSeen = Convert.ToInt32(reader["birds_seen"]);
int birdsMissed = Convert.ToInt32(reader["birds_missed"]);
string crop = Convert.ToString(reader["crop"]);
string cropsHarvested = Convert.ToString(reader["crop_harvested"]);
string comment = Convert.ToString(reader["comment"]);
Habitat habitat = habitatDAO.Read(habitatId, conn);
Group group = groupDAO.Read(groupId, conn);
List<Guide> guides = guideDAO.ReadByHuntId(huntId, conn);
Harvest harvest = new Harvest(pheasantHarvest, grouseHarvest, partridgeHarvest);
DateTime huntDate = SQLiteHelper.ToDateTimeFromMillis(Convert.ToInt64(reader["hunt_date"]));
h.HuntId = huntId;
h.Habitat = habitat;
h.Group = group;
h.Guides = guides;
h.NumberOfGuns = numberOfGuns;
h.Harvest = harvest;
h.HuntDate = huntDate;
h.Comments = comment;
h.Harvest.BirdsSeen = birdsSeen;
h.Harvest.BirdsMissed = birdsMissed;
h.Harvest.Crop = crop;
h.Harvest.CropHarvested = cropsHarvested;
results.Add(h);
}
log.Debug("Query returned " + results.Count + " result(s)");
}
catch (Exception e)
{
log.Error("Error reading from database", e);
throw e;
}
return results;
}
/* Helper methods */
private Hunt getHunt(int pheasant)
{
Provider p = InsertTestProvider(conn);
Habitat habitat = InsertTestHabitat(conn, p);
Group group = InsertTestGroup(conn);
Guide guide = InsertTestGuide(conn);
List<Guide> guides = new List<Guide>();
guides.Add(guide);
Harvest harvest = new Harvest()
{
Pheasant = pheasant,
Grouse = 2,
Partridge = 0,
BirdsSeen = 200,
BirdsMissed = 3,
Crop = "Sunflowers",
CropHarvested = "Y"
};
Hunt h = new Hunt()
{
HuntId = 123,
Habitat = habitat,
Group = group,
Guides = guides,
NumberOfGuns = 3,
Harvest = harvest,
HuntDate = new DateTime(2014, 10, 17),
Comments = "Comment here"
};
return h;
}
