// Filter entries that are displayed
private void filter_btn_Click(object sender, EventArgs e)
{
// Set up the filter
EntriesFilter filter = Helper.GenerateFilter(fromFilter_DTP, toFilter_DTP, projectFilter_cb);
List <Entry> entries = (new EntriesController()).FilterEntriesMain(filter);
PopulateEntries(entries);
}
/// <summary>
/// Filters entries and displays them in the chart control
/// </summary>
private void SetUpChart()
{
// Set up the filter
EntriesFilter filter = Helper.GenerateFilter(from_DTP, to_DTP, project_cb);
// Remove Points from before
chartControl.Series["Hours/day"].Points.Clear();
// Get entries from db based on filter
// This list is being sorted in procedure by start_time ascending
List <Entry> entries = (new Controllers.EntriesController()).FilterEntriesForChart(filter);
int no_of_points = 0;
double maxHours = 0, minHours = double.MaxValue, sumHours = 0;
if (entries.Count > 0)
{
// Current chart point that will accumulate data for specific day
ChartPoint current_point = new ChartPoint();
for (int i = 0; i < entries.Count; i++)
{
// If point is not set already
if (i == 0)
{
// We store the whole DateTime object, but will use it to get the day only
current_point.date = entries[i].start_time;
}
// If point is set, but the day has changed
else if (entries[i].start_time.DayOfYear != current_point.date.DayOfYear)
{
AddPointToChart(current_point, ref no_of_points, ref sumHours, ref maxHours, ref minHours);
current_point.total_hours = 0;
current_point.date = entries[i].start_time;
}
// Add hours and earnings to point
// -- Convert to non-nullable DateTime object
current_point.total_hours += ((DateTime)entries[i].end_time).Subtract(entries[i].start_time).TotalHours;
}
AddPointToChart(current_point, ref no_of_points, ref sumHours, ref maxHours, ref minHours);
if (no_of_points > 0)
{
avgHours_lb.Text = Math.Round(sumHours / no_of_points, 2).ToString() + "h/day";
minHours_lb.Text = Math.Round(minHours, 2).ToString() + "h/day";
maxHours_lb.Text = Math.Round(maxHours, 2).ToString() + "h/day";
}
}
else
{
MessageBox.Show("No entries found for specified filters.");
}
}
/// <summary>
/// Filters entries and displays them in the chart control
/// </summary>
private void SetUpChart()
{
// Set up the filter
EntriesFilter filter = Helper.GenerateFilter(from_DTP, to_DTP, project_cb);
// Remove Points from before for all currencies (series)
for (int i = 0; i < chartControl.Series.Count; i++)
{
chartControl.Series[i].Points.Clear();
}
// Get entries from db based on filter
// This list is being sorted in procedure by start_time ascending
List <Entry> entries = (new EntriesController()).FilterEntriesForChart(filter);
if (entries.Count > 0)
{
// Current chart point that will accumulate data for specific day
ChartPoint current_point = new ChartPoint();
for (int i = 0; i < entries.Count; i++)
{
// If point is not set already
if (i == 0)
{
// We store the whole DateTime object, but will use it to get the day only
current_point.date = entries[i].start_time;
}
// If point is set, but the day has changed
else if (entries[i].start_time.DayOfYear != current_point.date.DayOfYear)
{
AddPoint(current_point);
current_point.date = entries[i].start_time;
current_point.total_earnings.Clear();
}
if (entries[i].hourly_rate > 0)
{
// If we already have the currency in dictionary, just increment it
if (current_point.total_earnings.ContainsKey(entries[i].currency))
{
current_point.total_earnings[entries[i].currency] += entries[i].CalculateEarnings();
}
// If we don't have this currency, initialize it
else
{
current_point.total_earnings.Add(entries[i].currency, entries[i].CalculateEarnings());
}
}
}
AddPoint(current_point);
}
}
/// <summary>
/// Filters entries for report to send to contact. Sorted by start_time - desc.
/// </summary>
/// <param name="filter">Filter object to be applied to query</param>
/// <returns></returns>
public List <Entry> GetForReport(EntriesFilter filter)
{
using (IDbConnection conn = new SqlConnection(Helper.ConnectionString(db_name)))
{
string query = "dbo.Entry_GetForReport @from_date, @to_date";
if (filter.project_id > 0)
{
query += ", @project_id";
}
return(conn.Query <Entry>(query, filter).ToList());
}
}
// Create the report object
private Report CreateReportObject()
{
// Set up the filter
EntriesFilter filter = Helper.GenerateFilter(from_DTP, to_DTP, projects_cb);
// Get entries from db based on filter
// This list is being sorted in procedure by start_time ascending
List <Entry> entries = (new EntriesController()).GetForReport(filter);
// Report
return(new Report(selected_contact, filter, entries));
}
// -- Filter entries for chart
// -- -- Returns entries in a time span, sorted by start_time
/// <summary>
/// Filters entries by date and project. Returns list sorted by start_time.
/// </summary>
/// <param name="filter">Filter object to be applied to query</param>
/// <returns></returns>
public List <Entry> FilterEntriesForChart(EntriesFilter filter)
{
using (IDbConnection conn = new SqlConnection(Helper.ConnectionString(db_name)))
{
if (filter.project_id > 0)
{
return(conn.Query <Entry>("dbo.Entry_Filter @from_date, @to_date, @project_id", filter).ToList());
}
else
{
return(conn.Query <Entry>("dbo.Entry_Filter @from_date, @to_date", filter).ToList());
}
}
}
/// <summary>
/// Generates filter object for entries based on from and to dates and combobox with projects names
/// </summary>
/// <param name="from">Form control containing min. date</param>
/// <param name="to">Form control containing max. date</param>
/// <param name="cb">Form control containing list of projects names where one is selected (or an empty string is selected)</param>
/// <returns></returns>
public static EntriesFilter GenerateFilter(DateTimePicker from, DateTimePicker to, ComboBox cb)
{
EntriesFilter filter = new EntriesFilter(from.Value, to.Value);
if (cb.SelectedItem != null)
{
string project_name = cb.SelectedItem.ToString();
Project project = (new Controllers.ProjectsController()).FindProjectByName(project_name);
if (project != null)
{
filter.project_id = project.id;
}
}
return(filter);
}
/// <summary>
/// Used to populate quick preview listboxes
/// </summary>
private void GenerateQuickPreview()
{
DateTime to = DateTime.Now;
DateTime from;
List <Entry> entries;
EntriesController eController = new EntriesController();
EntriesFilter filter = new EntriesFilter(DateTime.Now, to); // using DateTime.Now just to initialize
// Today, past 7, past 30
int[] decrease = { 0, -7, -30 };
ListBox[] lbs = { today_lb, pastSevenDays_lb, pastThrityDays_lb };
for (int i = 0; i < decrease.Length; i++)
{
from = to.AddDays(decrease[i]).AddHours(to.Hour * -1).AddMinutes(to.Minute * -1).AddSeconds(to.Second * -1);
filter.from_date = from;
entries = eController.FilterEntriesForChart(filter);
PopulateListBox(entries, lbs[i]);
}
// Past 6 months
from = to.AddMonths(-6).AddHours(to.Hour * -1).AddMinutes(to.Minute * -1).AddSeconds(to.Second * -1);
filter.from_date = from;
entries = eController.FilterEntriesForChart(filter);
PopulateListBox(entries, pastSixMonths_lb);
// Past year
from = to.AddYears(-1).AddHours(to.Hour * -1).AddMinutes(to.Minute * -1).AddSeconds(to.Second * -1);
filter.from_date = from;
entries = eController.FilterEntriesForChart(filter);
PopulateListBox(entries, pastYear_lb);
// This year
from = to.AddMonths((to.Month - 1) * -1).AddDays((to.Day - 1) * -1).AddHours(to.Hour * -1).AddMinutes(to.Minute * -1).AddSeconds(to.Second * -1);
filter.from_date = from;
entries = eController.FilterEntriesForChart(filter);
PopulateListBox(entries, thisYear_lb);
// All time
entries = eController.GetAllEntries();
PopulateListBox(entries, allTime_lb);
}
/// <summary>
/// Will filter entries on Main.cs and automatically sort them by id-asc, as in Main.cs entries are added in reverse order then they are in list.
/// </summary>
/// <param name="filter">Filter object to be applied to query</param>
/// <returns></returns>
public List <Entry> FilterEntriesMain(EntriesFilter filter)
{
using (IDbConnection conn = new SqlConnection(Helper.ConnectionString(db_name)))
{
string query = "dbo.Entry_FilterMain ";
if (filter.from_date != DateTime.MinValue)
{
query += "@from_date, ";
}
if (filter.to_date != DateTime.MinValue)
{
query += "@to_date, ";
}
if (filter.project_id > 0)
{
query += "@project_id, ";
}
query = query.Substring(0, query.Length - 2);
return(conn.Query <Entry>(query, filter).ToList());
}
}
public void AddFilter(EntriesFilter filter)
{
this.filters.Add(filter);
}
public void AddFilter(EntriesFilter filter)
{
throw new NotImplementedException();
}
