/// <summary>
/// The fetch log.
/// </summary>
/// <param name="empNr">
/// The emp nr.
/// </param>
/// <param name="projectList">
/// The project list.
/// </param>
/// <param name="from">
/// The from.
/// </param>
/// <param name="to">
/// The to.
/// </param>
/// <returns>
/// The <see cref="DataTable"/>.
/// </returns>
/// <exception cref="Exception">
/// Unable to fetch log.
/// </exception>
public DataTable FetchLog(string empNr, IEnumerable <string> projectList, DateTime from, DateTime to)
{
BasicValidator.ValidateEntry(empNr, "DummyValue", from, to, sqlHelper.ErrorLogger);
if (!projectList.Any())
{
return(new DataTable());
}
var projectArray = projectList.Select(x => x.Trim()).ToArray();
var joinQuery = sqlHelper.GetFetchLogSelectQuery(empNr, @from, to, projectArray[0]);
for (int i = 1; i < projectArray.Length; i++)
{
BasicValidator.ValidateAsNonSpacedString(projectArray[i], sqlHelper.ErrorLogger);
var joinSuffix =
$" full outer join {sqlHelper.GetFetchLogSelectQuery(empNr, @from, to, projectArray[i])} on {projectArray[0]}.WorkDay = {projectArray[i]}.WorkDay ";
joinQuery += joinSuffix;
}
string coalease_value = projectList.Count() > 1
? $"coalesce( {string.Join(",", projectList.Select(x => $"{x}.WorkDay"))} )"
: $"{projectList.First()}.WorkDay";
var query =
$"select convert( varchar(10), {coalease_value}, 120) as WorkDay, {string.Join(",", projectList.Select(x => $"{x}.TimeLog as Project_{x}"))} from {joinQuery} order by {projectArray[0]}.WorkDay";
return(sqlHelper.RunCommand(query, new SqlParameter[0]));
}
/// <summary>
/// The fetch log.
/// </summary>
/// <param name="empNr">
/// The emp nr.
/// </param>
/// <param name="projectList">
/// The project list.
/// </param>
/// <param name="from">
/// The from.
/// </param>
/// <param name="to">
/// The to.
/// </param>
/// <returns>
/// The <see cref="DataTable"/>.
/// </returns>
/// <exception cref="Exception">
/// Unable to fetch log.
/// </exception>
public DataTable FetchLog(string empNr, IEnumerable <string> projectList, DateTime from, DateTime to)
{
BasicValidator.ValidateEntry(empNr, "DummyValue", from, to, sqlHelper.ErrorLogger);
if (!projectList.Any())
{
return(new DataTable());
}
var projectArray = projectList.Select(x => x.Trim()).ToArray();
var joinQuery = sqlHelper.GetFetchLogSelectQuery(empNr, @from, to, projectArray[0]);
for (int i = 1; i < projectArray.Length; i++)
{
BasicValidator.ValidateAsNonSpacedString(projectArray[i], sqlHelper.ErrorLogger);
var joinSuffix =
$" full outer join {sqlHelper.GetFetchLogSelectQuery(empNr, @from, to, projectArray[i])} on {projectArray[0]}.WorkDay = {projectArray[i]}.WorkDay ";
joinQuery += joinSuffix;
}
string coalease_value = projectList.Count() > 1
? $"coalesce( {string.Join(",", projectList.Select(x => $"{x}.WorkDay"))} )"
: $"{projectList.First()}.WorkDay";
var query =
$"select convert( varchar(10), {coalease_value}, 120) as WorkDay, {string.Join(",", projectList.Select(x => $"{x}.TimeLog as {x}"))} from {joinQuery} order by {projectArray[0]}.WorkDay";
var dt = sqlHelper.RunCommand(query, new SqlParameter[0]);
var grid = new DataTable();
grid.Columns.Add("WorkDay");
grid.PrimaryKey = new[] { grid.Columns["WorkDay"] };
grid.Columns.AddRange(projectList.Select(x => new DataColumn($"{x}")).ToArray());
for (var i = from; i <= to; i = i.AddDays(1))
{
grid.Rows.Add(i.ToShortDateString());
}
grid.BeginLoadData();
foreach (DataRow dtrow in dt.Rows)
{
var itemArray = new string[dtrow.ItemArray.Length];
itemArray[0] = DateTime.Parse(dtrow.ItemArray[0].ToString()).ToShortDateString();
for (var i = 1; i < dtrow.ItemArray.Length; i++)
{
itemArray[i] = dtrow.ItemArray[i].ToString();
}
grid.LoadDataRow(itemArray.Select(x => (object)x).ToArray(), LoadOption.OverwriteChanges);
}
grid.EndLoadData();
return(grid);
}
/// <summary>
/// The fetch statistics.
/// </summary>
/// <param name="empNr">
/// The emp nr.
/// </param>
/// <param name="project">
/// The project.
/// </param>
/// <param name="from">
/// The from.
/// </param>
/// <param name="to">
/// The to.
/// </param>
/// <returns>
/// The <see cref="DataTable"/>.
/// </returns>
/// <exception cref="Exception">
/// Statistic Fetch failed.
/// </exception>
public DataTable FetchStatistics(string empNr, string project, DateTime from, DateTime to)
{
BasicValidator.ValidateEntry(empNr, project, from, to, sqlHelper.ErrorLogger);
if (sqlHelper.RunCommand($"SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Project_{project}_Log'",
new SqlParameter[0]).Rows.Count == 0)
{
return(new DataTable());
}
return(sqlHelper.RunCommand($"{sqlHelper.GetProjectFetchQuery(empNr, from, to, project)} order by WorkDay", new SqlParameter[0]));
}
