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)); }