public List <ScoreCardObject> GetScoreCardData(int year, int supplierId, DateTime?fromDate, DateTime?toDate)
{
if (toDate.HasValue && toDate.Value > DateTime.Now.AddDays(1))
{
toDate = DateTime.Now.AddDays(1);
}
var scMeasures = _scMeasureRepository.Table.Where(m => m.IsDisplay).ToList();
var result = scMeasures.Select(scm => new ScoreCardObject
{
MsqMeasure = scm.Name,
ScMeasureId = scm.Id,
IsBold = scm.IsBold
}).ToList();
var result2 = scMeasures.Select(scm => new ScoreCardObject
{
MsqMeasure = scm.Name,
ScMeasureId = scm.Id
}).ToList();
//Get supplier
var supplier = _supplierService.GetByIdAsync(supplierId).Result;
//Get all quality alert in year from 1st July to 30th Jun of supplier
var quanlityAlerts = _qualityAlertService.SearchQualityAlertObjectAsync(startDate: new DateTime(year - 1, 7, 1), endDate: new DateTime(year, 7, 1), supplierIds: new List <int>()
{
supplierId
}).Result;
//Get all MQS measure
var mqsMeasures = _scMeasureRepository.Table.Where(m => m.IsImported).OrderBy(m => m.DisplayOrder).ToList();
//Get all sub-Column to add to Quality alert table in excel sheeet
var subColumns = _subColumnService.GetAllAsync().Result;
//get all scoreCardItems of supplier
var scoreCardItems = _mqsMeasureRepository.Table.Where(x => x.SupplierId == supplierId).ToList();
//Get all score card target of supplier
var scMeasureTargets = _scMeasureTargetService.GetAllScMeasureTargetBySupplierId(supplier.Id).Result;
var listTask = new List <Task>();
DateTime fStartDate = new DateTime(year - 1, 7, 1);
DateTime fEndDate = DateTime.Now.AddDays(1);
do
{
DateTime tDate;
if (fStartDate.Year == fEndDate.Year && fStartDate.Month == fEndDate.Month)
{
tDate = fEndDate;
}
else
{
tDate = fStartDate.AddMonths(1).AddDays(1 - fStartDate.Day);
}
listTask.Add(CreateScoreCardAsync(fStartDate, tDate, result, quanlityAlerts, scMeasures, mqsMeasures, scoreCardItems, scMeasureTargets, subColumns));
fStartDate = tDate;
} while (fStartDate < fEndDate);
if (fromDate.HasValue && toDate.HasValue && fromDate.Value.Year == toDate.Value.Year && fromDate.Value.Month == toDate.Value.Month)
{
listTask.Add(CreateScoreCardAsync(fromDate.Value, toDate.Value, result2, quanlityAlerts, scMeasures, mqsMeasures, scoreCardItems, scMeasureTargets, subColumns));
Task.WaitAll(listTask.ToArray());
YtdCalculate(result, scMeasures);
for (int j = 0; j < scMeasures.Count; j++)
{
result[j].Data[(fromDate.Value.Month + 5) % 12] = result2[j].Data[(fromDate.Value.Month + 5) % 12];
}
return(result);
}
if (fromDate.HasValue && fromDate.Value != new DateTime(year - 1, 7, 1))
{
var tDate = fromDate.Value.AddMonths(1).AddDays(1 - fromDate.Value.Day);
listTask.Add(CreateScoreCardAsync(fromDate.Value, tDate, result2, quanlityAlerts, scMeasures, mqsMeasures, scoreCardItems, scMeasureTargets, subColumns));
}
if (toDate.HasValue && toDate.Value != DateTime.Now.Date)
{
var sDate = toDate.Value.AddDays(1 - toDate.Value.Day);
listTask.Add(CreateScoreCardAsync(sDate, toDate.Value, result2, quanlityAlerts, scMeasures, mqsMeasures, scoreCardItems, scMeasureTargets, subColumns));
}
//Calculate for every month from July to Jun
Task.WaitAll(listTask.ToArray());
YtdCalculate(result, scMeasures);
if (fromDate.HasValue && fromDate.Value != new DateTime(year - 1, 7, 1))
{
for (int j = 0; j < scMeasures.Count; j++)
{
result[j].Data[(fromDate.Value.Month + 5) % 12] = result2[j].Data[(fromDate.Value.Month + 5) % 12];
}
}
if (toDate.HasValue && toDate.Value != DateTime.Now.Date)
{
for (int j = 0; j < scMeasures.Count; j++)
{
result[j].Data[(toDate.Value.Month + 5) % 12] = result2[j].Data[(toDate.Value.Month + 5) % 12];
}
}
return(result);
}
public void ImportScoreCardMeasure(MemoryStream stream, out string message)
{
message = "";
try
{
using (var package = new ExcelPackage(stream))
{
if (package.Workbook.Worksheets.Count <= 0)
{
message = "Your Excel file does not contain any work sheets";
}
else
{
var worksheet = package.Workbook.Worksheets["Score Card"];
//Todo: Check worksheet
var rowIndex = 3;
var rows = worksheet.Cells[rowIndex, 1, worksheet.Dimension.End.Row, 1];
foreach (var row in rows)
{
var measure = _scMeasureService.GetScMeasureByNameAsync(row.Value == null ? "" : row.Value.ToString()).Result;
if (measure == null)
{
continue;
}
measure.Formula = CellFormula(worksheet, rowIndex, 3);
measure.YtdCalculatedTypeId = 0;
if (CellFormula(worksheet, rowIndex, 2).ToLower() == YtdCalculatedType.Average.ToString().ToLower())
{
measure.YtdCalculatedType = YtdCalculatedType.Average;
}
if (CellFormula(worksheet, rowIndex, 2).ToLower() == YtdCalculatedType.LatestData.ToString().ToLower())
{
measure.YtdCalculatedType = YtdCalculatedType.LatestData;
}
if (CellFormula(worksheet, rowIndex, 2).ToLower() == YtdCalculatedType.Sum.ToString().ToLower())
{
measure.YtdCalculatedType = YtdCalculatedType.Sum;
}
_scMeasureService.UpdateAsync(measure).Wait();
rowIndex++;
}
//sub column
var subcolumnws = package.Workbook.Worksheets["Sub-Column"];
//Todo: Check worksheet
rowIndex = 3;
rows = subcolumnws.Cells[rowIndex, 1, subcolumnws.Dimension.End.Row, 1];
var subColumns = _subColumnFormulaService.GetAllAsync().Result;
if (rows.Count() == 0 && subColumns.Count() != 0)
{
foreach (var subC in subColumns)
{
_subColumnFormulaService.DeleteAsync(subC);
}
}
else
{
if (rows.Count() > 0)
{
var updates = subColumns.Take(rows.Count());
var count = updates.Count();
foreach (var subC in updates)
{
subC.Name = CellValue(subcolumnws, rowIndex, 1);
subC.Formula = CellFormula(subcolumnws, rowIndex, 2);
_subColumnFormulaService.UpdateAsync(subC).Wait();
rowIndex++;
}
var deletes = subColumns.Skip(count);
foreach (var subC in deletes)
{
_subColumnFormulaService.DeleteAsync(subC);
}
var adds = rows.Skip(count);
foreach (var subC in adds)
{
var aSubC = new SubColumnFormula();
aSubC.Name = CellValue(subcolumnws, rowIndex, 1);
aSubC.Formula = CellFormula(subcolumnws, rowIndex, 2);
_subColumnFormulaService.InsertAsync(aSubC).Wait();
rowIndex++;
}
}
}
}
}
}
catch (Exception ex)
{
message = ex.Message;
}
}