private string BuildExportQuery(DTO.SearchOptions o)
{
bool firstSelect = true;
bool firstWhere = true;
StringBuilder query = new StringBuilder();
//
query.Append("SELECT ");
foreach (var d in o.exportIncludeOptions)
{
if (d.Value == "false")
{
continue;
}
if (!firstSelect)
{
query.Append(", ");
}
query.Append(sanitizeColumnName(d.Key));
firstSelect = false;
}
if (query.Length == 7) // just select
{
query.Append("*");
}
//
query.Append(" FROM ").Append(o.name);
//
if (o.exportFilterField != null
&&
(o.beginDate != null ||
o.endDate != null
))
{
query.Append(" WHERE ");
if (o.beginDate != null)
{
query.Append(o.exportFilterField)
.Append(" > '")
.Append(((DateTime)o.beginDate).ToShortDateString())
.Append("' ");
firstWhere = false;
}
if (o.endDate != null)
{
if (!firstWhere)
{
query.Append(" AND ");
}
query.Append(o.exportFilterField)
.Append(" < '")
.Append(((DateTime)o.endDate).ToShortDateString())
.Append("' ");
}
}
return(query.ToString());
}
public void getXlsxFile(DTO.SearchOptions o, ref byte[] bytes)
{
var oo = map.Map <DTO.SearchOptions, Data.DTO.SearchOptions>(o);
var tbl = rRepo.getDataTable(buildExportQuery(o), oo);
using (ExcelPackage pck = new ExcelPackage())
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add(o.name);
ws.Cells["A1"].LoadFromDataTable(tbl, true);
bytes = pck.GetAsByteArray();
}
}
public List <dynamic> GetQuery(DTO.SearchOptions o)
{
// if name, get id for report definition
if (!Int32.TryParse(o.idOrName, out var id))
{
id = GetMany(r => string.Equals(r.name, o.idOrName, StringComparison.OrdinalIgnoreCase)).First().ID;
}
var oo = map.Map <DTO.SearchOptions, SearchOptions>(o);
return(GetDynamicQuery(id, oo));
}
public void GetSimpleAggregate_MissingDefThrowsException()
{
// Arrange
frb.AddWorkOrder(dateTimeOfWork: DateTime.Parse("1/2/2013"))
.AddWorkAssignment(skill: 63); // known skill ID from machete lookup initializer
o = new DTO.SearchOptions
{
idOrName = "blah",
beginDate = DateTime.Parse("1/1/2013"),
endDate = DateTime.Parse("1/1/2014")
};
// Act
List <dynamic> result = frb.ToServReportsV2().getQuery(o);
// Assert
}
public void GetSimpleAggregate_IsCaseInsensitive()
{
// Arrange
frb.AddWorkOrder(dateTimeOfWork: DateTime.Parse("1/2/2013"))
.AddWorkAssignment(skill: 63); // known skill ID from machete lookup initializer
o = new DTO.SearchOptions
{
idOrName = "dispatchesbyjob",
beginDate = DateTime.Parse("1/1/2013"),
endDate = DateTime.Parse("1/1/2014")
};
// Act
List<dynamic> result = frb.ToServ<IReportsV2Service>().getQuery(o);
// Assert
Assert.IsNotNull(result);
Assert.AreEqual("20130101-20140101-DispatchesByJob-63", result[0].id);
}
public void GetSimpleAggregate_IdGetSucceeds()
{
// Arrange
frb.AddWorkOrder(dateTimeOfWork: DateTime.Parse("1/2/2013"))
.AddWorkAssignment(skill: 63); // known skill ID from machete lookup initializer
o = new DTO.SearchOptions
{
idOrName = "1",
beginDate = DateTime.Parse("1/1/2013"),
endDate = DateTime.Parse("1/1/2014")
};
// Act
List<dynamic> result = frb.ToServ<IReportsV2Service>().getQuery(o);
// Assert
Assert.IsNotNull(result);
Assert.AreNotEqual(0, result.Count);
}
public void get_XlsxFile_noParams_succeeds()
{
// arrange
var dict = new Dictionary<string, string>();
dict.Add("ID", "true");
dict.Add("dwccardnum", "true");
dict.Add("datecreated", "true");
byte[] result = null;
var o = new DTO.SearchOptions {
name = "Workers",
beginDate = null,
endDate = null,
exportFilterField = null,
exportIncludeOptions = dict
};
// act
frb.ToServ<IReportsV2Service>()
.getXlsxFile(o, ref result);
// assert
Assert.IsNotNull(result);
}
public DataTable getDataTable(string query, DTO.SearchOptions o)
{
var oo = map.Map <DTO.SearchOptions, Data.DTO.SearchOptions>(o);
return(rRepo.getDataTable(query, oo));
}