public List<TimeSheetEntry> RetrieveDailyDataForUsers(DateTime from, DateTime to, List<string> projects)
{
List<TimeSheetEntry> entries = new List<TimeSheetEntry>();
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(Sql_TimeSheet_Entries_Breakdown, connection))
{
command.CommandText = command.CommandText.Replace("@Projects", string.Join(",", projects));
command.Parameters.AddWithValue("@From", from);
command.Parameters.AddWithValue("@To", to);
command.CommandTimeout = 0;
using (var reader = command.ExecuteReader())
{
if (reader != null)
{
while (reader.Read())
{
TimeSheetEntry entry = new TimeSheetEntry();
entry.Name = reader.GetString(0);
entry.Date = reader.GetDateTime(1);
entry.ProjectName = reader.GetString(2);
entry.Task = reader.GetString(3);
entry.Hours = reader.GetDouble(4);
entry.Rate = reader.GetDouble(5);
entries.Add(entry);
}
}
}
}
}
return entries;
}
/// <summary>
/// Retrieves the cost centre details if already cached. If not will call GetCostCentre to retrieve it.
/// It is assumed that the project Id will be located at the end of the project name found in the time sheet reports database
/// </summary>
/// <param name="timeSheetEntry">the daily project entry</param>
/// <exception cref="Exception">if the cost centre is not found for the given project Id</exception>
/// <returns>the cost centre info</returns>
public CostCentreInfo GetCostCentreDetailsFromSheet(TimeSheetEntry timeSheetEntry)
{
var projectName = timeSheetEntry.ProjectName;
var projectId = projectName.Substring(projectName.LastIndexOf(" ") + 1);
if (CostCentreCache.ContainsKey(projectId))
{
return CostCentreCache[projectId];
}
var costCentreInfo = GetCostCentreInfo(projectId);
CostCentreCache.Add(projectId, costCentreInfo);
return costCentreInfo;
}