public static bool TryParse(string str, out ReportPeriodType type)
{
var parsed = true;
type = ReportPeriodType.LastDay;
str = str ?? string.Empty;
if (str.Equals(Resources.Strings.TimePeriodOptionAllTime))
{
type = ReportPeriodType.AllTime;
}
else if (str.Equals(Resources.Strings.TimePeriodOptionLast7Days))
{
type = ReportPeriodType.Last7Days;
}
else if (str.Equals(Resources.Strings.TimePeriodOptionLastDay))
{
type = ReportPeriodType.LastDay;
}
else
{
parsed = false;
}
return(parsed);
}
public static string GetReportPeriod(ReportPeriodType type)
{
switch (type)
{
case ReportPeriodType.AllTime:
return(Resources.Strings.TimePeriodOptionAllTime);
case ReportPeriodType.Last7Days:
return(Resources.Strings.TimePeriodOptionLast7Days);
case ReportPeriodType.LastDay:
return(Resources.Strings.TimePeriodOptionLastDay);
default:
break;
}
return(null);
}
private void BuildReport()
{
if (ReportProvider != null)
try
{
using (new WaitWrapper())
{
CreateModel();
Model.ReportName = ReportProvider.GetType().Name;
var filter = ReportProvider is IFilteredSKDReportProvider ? ((IFilteredSKDReportProvider)ReportProvider).GetFilter() : null;
FilterName = filter == null ? null : filter.Name;
if (filter != null)
filter.User = ClientManager.CurrentUser;
var filterPeriod = filter as IReportFilterPeriod;
IsPeriodReport = filterPeriod != null;
if (IsPeriodReport)
PeriodType = filterPeriod.PeriodType;
Model.Build(filter);
}
}
catch (Exception ex)
{
Logger.Error(ex, "SKDReportPresenterViewModel.BuidlReport");
if (ApplicationService.ApplicationActivated)
MessageBoxService.ShowError("Возникла ошибка при построении отчета.");
if (ex is CommunicationException)
CreateClient();
}
else
{
IsPeriodReport = false;
FilterName = null;
}
}
public async Task <IList <HeatMapCountingReportHeaderModel> > GetHeatMapCoutingReportAsync(DateTime reportDate, ReportPeriodType period)
{
return(await Task.Run(() =>
{
using (var db = new MbkCameraDb(ConnectionString))
{
string queryDate = ToDateString(reportDate);
var dbData = from cam in db.Cameras
join hm in db.HeatMaps on cam.Id equals hm.CameraId
join ct in db.Countings on
new { CameraId = cam.Id, Date = hm.Date, Time = hm.Time, Gmt = hm.Gmt } equals
new { CameraId = ct.CameraId, Date = ct.Date, ct.Time, ct.Gmt }
where ct.Date == queryDate
select new
{
Id = cam.Id,
CameraArea = cam.Height,
CameraFloor = cam.Floor,
CameraName = cam.Name,
Date = ct.Date,
Time = ct.Time,
HeatMapValue = hm.TotalValue,
HeatMapCount = hm.TotalCount,
Countings = ct.CountingDetails,
Gmt = ct.Gmt
};
int separateValue = 1;
switch (period)
{
case ReportPeriodType.M15: separateValue = 1; break;
case ReportPeriodType.M30: separateValue = 2; break;
case ReportPeriodType.H1: separateValue = 4; break;
}
var cameraData = dbData
.GroupBy(x => new { x.Id, x.CameraArea, x.CameraFloor, x.CameraName, x.Date })
.AsEnumerable()
.Select((g) => new
{
Id = g.Key.Id,
CameraArea = g.Key.CameraArea,
CameraFloor = g.Key.CameraFloor,
CameraName = g.Key.CameraName,
Date = g.Key.Date,
Report = g
.OrderBy(x => x.Time)
.Select((x, i) => new
{
Period = i / separateValue,
Time = ConvertToTime(x.Time).Add(ConvertToTime(x.Gmt)),
Density = x.HeatMapCount > 0 ?
Math.Round((decimal)x.HeatMapValue / (decimal)x.HeatMapCount, 2) : 0,
Countings = x.Countings.Select((a, index) => new CountingReportDetailModel
{
LineNo = index,
A = a.A,
B = a.B
}),
})
.GroupBy(x => x.Period)
.Select(g2 => new
{
StartTime = g2.Min(a => a.Time),
EndTime = g2.Max(a => a.Time),
Density = g2.Sum(a => a.Density),
Countings = g2.SelectMany(a => a.Countings)
.GroupBy(b => b.LineNo)
.Select(g3 => new CountingReportDetailModel
{
LineNo = g3.Key,
A = g3.Sum(b => b.A),
B = g3.Sum(b => b.B)
})
})
})
.OrderBy(x => x.Id);
var query = cameraData
.Select((header, index) => new HeatMapCountingReportHeaderModel
{
CameraNo = index + 1,
CameraFloor = header.CameraFloor,
CameraName = header.CameraName,
Date = ConvertToDate(header.Date).ToString("d/MM/yyyy"),
Details = header.Report.Select(detail => new HeatMapCountingReportDetailModel
{
Time = string.Format("{0}-{1}",
detail.StartTime.Add(TimeSpan.FromMinutes(1)).ToString("hh\\:mm"),
detail.EndTime.Add(TimeSpan.FromMinutes(15)).ToString("hh\\:mm")),
Density = detail.Density,
Area = header.CameraArea,
DensityPerArea = Math.Round(detail.Density / header.CameraArea, 2),
Countings = detail.Countings.ToList(),
}).ToList()
});
return query.ToList();
}
}));
}
public async Task <int> GenerateDataReportAsync(
string reportLocation,
DateTime reportDate,
ReportPeriodType reportPeriod)
{
var source = await _reportRepository.GetHeatMapCoutingReportAsync(reportDate, reportPeriod);
var totalCamera = source.Count();
if (totalCamera > 0)
{
string timePeriod = reportPeriod == ReportPeriodType.M15 ? "15 นาที" : (reportPeriod == ReportPeriodType.M30 ? "30 นาที" : "1 ชั่วโมง");
using (ExcelPackage package = new ExcelPackage())
{
var heatmapSheet = InitHeatmap(package);
var countingSheet = InitCounting(package);
int rowIndex = 3;
int newCamRowIndex = rowIndex;
// loop for print data to excel
for (int i = 0; i < source.Count; i++)
{
var cam = source[i];
for (int j = 0; j < cam.Details.Count; j++, rowIndex++)
{
var detail = cam.Details[j];
// print heatmap
heatmapSheet.Cells[rowIndex, 1].Value = cam.CameraNo;
heatmapSheet.Cells[rowIndex, 2].Value = cam.CameraFloor;
heatmapSheet.Cells[rowIndex, 3].Value = cam.CameraName;
heatmapSheet.Cells[rowIndex, 4].Value = cam.Date;
heatmapSheet.Cells[rowIndex, 5].Value = detail.Time;
heatmapSheet.Cells[rowIndex, 6].Value = detail.Density;
heatmapSheet.Cells[rowIndex, 7].Value = detail.Area;
heatmapSheet.Cells[rowIndex, 8].Value = detail.DensityPerArea;
// print counting
countingSheet.Cells[rowIndex, 1].Value = cam.CameraNo;
countingSheet.Cells[rowIndex, 2].Value = cam.CameraFloor;
countingSheet.Cells[rowIndex, 3].Value = cam.CameraName;
countingSheet.Cells[rowIndex, 4].Value = cam.Date;
countingSheet.Cells[rowIndex, 5].Value = detail.Time;
for (int k = 0; k < detail.Countings.Count; k++)
{
var counting = detail.Countings[k];
int column = (k * 2) + 6;
countingSheet.Cells[rowIndex, column].Value = counting.A;
countingSheet.Cells[rowIndex, column + 1].Value = counting.B;
}
}
// create chart by heatmap data
var ws = package.Workbook.Worksheets.Add($"กล้องที่{cam.CameraNo} {cam.CameraName}");
ExcelChart chart = ws.Drawings.AddChart($"chart_{i}", eChartType.ColumnClustered);
chart.SetPosition(1, 0, 1, 0);
chart.SetSize(1800, 480);
chart.Series.Add(
heatmapSheet.Cells[newCamRowIndex, 6, rowIndex - 1, 6],
heatmapSheet.Cells[newCamRowIndex, 5, rowIndex - 1, 5]);
chart.Legend.Remove();
ws.Cells["B27:AC27"].Merge = true;
ws.Cells["B27:AC27"].Value = cam.CameraName;
ws.Cells["B27:AC27"].Style.Font.Bold = true;
ws.Cells["B27:AC27"].Style.Font.UnderLine = true;
ws.Cells["B27:AC27"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
newCamRowIndex = rowIndex;
}
string filename = string.Format("CameraReport_{0}_Per_{1}.xlsx",
reportDate.ToString("yyyyMMdd"),
reportPeriod == ReportPeriodType.M15 ? "15Minutes" : (reportPeriod == ReportPeriodType.M30 ? "30Minutes" : "1Hour"));
string exportFullPath = Path.Combine(reportLocation, filename);
package.SaveAs(new FileInfo(exportFullPath));
}
}
return(totalCamera);
}