public byte[] GetReport(int locationId, DateTime beginDate, DateTime endDate)
{
using (ExcelPackage excel = new ExcelPackage())
{
DeviceDbContext context = new DeviceDbContext();
endDate = endDate.AddDays(1);
var checks = context.Checks.Where(y => y.SerialNumber == locationId && y.CheckDate.HasValue && (y.CheckDate >= beginDate && y.CheckDate <= endDate)).ToList().
GroupBy(x => x.CheckDate.Value.ToString("dd.MM.yyyy"));
var sales = checks.Select(x => new
{
Date = DateTime.Parse(x.Key),
Total = (float)x.Sum(m => m.Total),
CountCheck = x.Count()
}).ToList();
excel.Workbook.Worksheets.Add(locationId.ToString());
ExcelWorksheet report = excel.Workbook.Worksheets[1];
report.Name = locationId.ToString();
report.Cells["A1"].Value = "Дата";
report.Cells["B1"].Value = "Сумма";
report.Cells["C1"].Value = "Чекки";
report.Column(1).Style.Numberformat.Format = "dd.MM.yyyy";
int i = 2;
foreach (var sale in sales)
{
report.Cells[i, 1].Value = sale.Date;
report.Cells[i, 2].Value = sale.Total;
report.Cells[i, 3].Value = sale.CountCheck;
i++;
}
report.Cells[i, 1].Value = "Итого";
report.Cells[i, 2].Value = sales.Sum(x => x.Total);
report.Cells[i, 3].Value = sales.Sum(x => x.CountCheck);
return(excel.GetAsByteArray());
}
}
public static void InitDbContext(ModelBuilder modelBuilder)
{
modelBuilder.Ignore <User>().Ignore <Group>().Ignore <DevicePlatform>();
LoginDbContext.InitDbContext(modelBuilder, false);
DeviceDbContext.InitDbContext(modelBuilder, false);
modelBuilder.Entity <LoginDevices>().HasOne(x => x.LdLogin);
modelBuilder.Entity <LoginDevices>().HasOne(x => x.LdDevice);
}
public DeviceRepositoryTests(DatabaseFixture dbFixture, ITestOutputHelper output)
{
this._dbFixture = dbFixture;
this._output = output;
var dbOptBuilder = GetDbOptionsBuilder();
this._dbContext = new DeviceDbContext(dbOptBuilder.Options);
this._repository = new DeviceRepository(_dbContext);
DeviceDbContextSeed.EnsureSeedDataForContext(_dbContext);
//_output.WriteLine($"Database: {_dbContext.Database.GetDbConnection().Database}\n" +
_output.WriteLine($"" +
$"Locations: {_dbContext.Locations.Count()} \n" +
$"Devices: {_dbContext.Devices.Count()} \n" +
$"Device Types: {_dbContext.DeviceTypes.Count()} \n\n");
//_output.WriteLine(deviceDbContextToString(_dbContext));
}
public byte[] GetReport(int locationId, DateTime beginDate, DateTime endDate)
{
using (ExcelPackage excel = new ExcelPackage())
{
DeviceDbContext context = new DeviceDbContext();
endDate = endDate.AddDays(1);
var check = context.Checks.Where(x => x.SerialNumber == locationId && x.CheckDate.HasValue && (x.CheckDate >= beginDate && x.CheckDate <= endDate)).ToList();
var article = context.Articles.GroupBy(x => x.CheckId).ToList();
var model = check.GroupJoin(article, x => x.id, y => y.Key, (x, y) => new
{
CheckId = x.id,
CheckNumber = x.CheckNumber.ToString(),
Price = x.Total,
Position = y.Count(),
Date = x.CheckDate.HasValue ? x.CheckDate.Value.ToString("dd.MM.yyyy") : ""
}).ToList();
excel.Workbook.Worksheets.Add(locationId.ToString());
ExcelWorksheet report = excel.Workbook.Worksheets[1];
report.Name = locationId.ToString();
report.Cells["A1"].Value = "Номер чека";
report.Cells["B1"].Value = "Дата";
report.Cells["C1"].Value = "Позиции";
report.Cells["D1"].Value = "Сумма";
report.Column(1).Style.Numberformat.Format = "dd.MM.yyyy";
int i = 2;
foreach (var _check in model)
{
report.Cells[i, 1].Value = _check.CheckNumber;
report.Cells[i, 2].Value = _check.Date;
report.Cells[i, 3].Value = _check.Position;
report.Cells[i, 3].Value = _check.Price;
i++;
}
report.Cells[i, 1].Value = "Итого";
report.Cells[i, 3].Value = model.Sum(x => x.Position);
report.Cells[i, 3].Value = model.Sum(x => x.Price);
return(excel.GetAsByteArray());
}
}
public LocationController()
{
context = new DeviceDbContext();
}
public DashBoardController()
{
context = new DeviceDbContext();
}
public ConfigurationController()
{
context = new DeviceDbContext();
}
public DeviceTokenRepotitory()
{
_context = new DeviceDbContext();
}
public ArticleController()
{
context = new DeviceDbContext();
}
public DeviceFilesController(IMapper mapper, DeviceDbContext deviceDbContext)
{
_mapper = mapper ?? throw new ArgumentNullException(nameof(mapper));
_deviceDbContext = deviceDbContext ?? throw new ArgumentNullException(nameof(deviceDbContext));
}
//private static List<AddDevice>addDevices = new List<AddDevice>();
//private SimulatedDevice simulated;
public DevicesController(DeviceDbContext context, UserManager <ApplicationUser> userManager, IHttpContextAccessor httpContextAccessor)
{
_context = context;
_userManager = userManager;
_httpContextAccessor = httpContextAccessor;
}
public CheckController()
{
context = new DeviceDbContext();
}
public DeviceRepository(DeviceDbContext deviceDbContext)
{
this.deviceDbContext = deviceDbContext;
}
public DeviceRepository(DeviceDbContext dbContext)
{
_context = dbContext;
}
public QueryLogController()
{
context = new DeviceDbContext();
}
public LogMessageTypesController()
{
context = new DeviceDbContext();
}
public ParamsController()
{
context = new DeviceDbContext();
}
public DeviceItemsController(DeviceDbContext context)
{
_context = context;
}
