public async Task <ActionResult> ExportIpuToExcel(string id)
{
var temp = Path.GetTempFileName() + ".xlsx";
System.IO.File.Copy(Server.MapPath("~/App_Data/template/laporan-ipu.xlsx"), temp, true);
var file = new FileInfo(temp);
var excel = new ExcelPackage(file);
var ws = excel.Workbook.Worksheets["IPU"];
var context = new SphDataContext();
var program = await context.GetScalarAsync <SesiUjian, string>(x => x.Id == id, x => x.NamaProgram);
var query = context.CreateQueryable <SesiUjian>()
.Where(s => s.NamaProgram == program)
.Where(s => s.NamaUjian == "IPU")
.Where(s => s.Status == "Diambil");
var sesiLo = await context.LoadAsync(query, 1, 200, true);
var sesi = sesiLo.ItemCollection;
while (sesiLo.HasNextPage)
{
sesiLo = await context.LoadAsync(query, sesiLo.CurrentPage + 1, 200, true);
sesi.AddRange(sesiLo.ItemCollection);
}
var soalanQuery = context.CreateQueryable <Soalan>()
.Where(s => s.NamaUjian == "IPU");
var soalanLo = await context.LoadAsync(soalanQuery, 1, 200, true);
var soalans = soalanLo.ItemCollection;
while (soalanLo.HasNextPage)
{
soalanLo = await context.LoadAsync(soalanQuery, soalanLo.CurrentPage + 1, 200, true);
soalans.AddRange(soalanLo.ItemCollection);
}
var traits = soalans.Select(s => s.Trait).Distinct().OrderBy(s => s).ToArray();
var row = 2;
foreach (var s in sesi)
{
var user = await context.LoadOneAsync <Pengguna>(x => x.MyKad == s.MyKad);
row++;
ws.InsertRow(row, 1, row);
ws.Cells[row, 1].Value = s.NamaPengguna;
ws.Cells[row, 2].Value = $"{s.TarikhUjian:dd/MM/yyyy HH:mm}";
var column = 1;
foreach (var t in traits)
{
column += 2;
var t1 = t;
var score = s.JawapanCollection.Where(a => a.Trait == t1).Sum(a => a.Nilai);
ws.Cells[row, column].Value = score;
if (t1 == "J")
{
ws.Cells[row, column + 1].Value = score;
}
else
{
/*var lookup = await context.LoadOneAsync<SkorIPU>(x => score.IsBetween(x.NilaiMin, x.NilaiMax, true, true));*/
var lookup = await context.LoadOneAsync <SkorIPU>(x => score.IsBetween(x.NilaiMin, x.NilaiMax, true, true) && x.Jantina == user.Jantina && x.Tret == t1);
ws.Cells[row, column + 1].Value = lookup.Percentile;
}
}
}
excel.Save();
excel.Dispose();
return(File(temp, MimeMapping.GetMimeMapping(".xlsx"), "Laporan Markah dan Percentile IPU.xlsx"));
}