public async Task <ActionResult> PrintPpkpKhusus(string id)
{
var context = new SphDataContext();
var sesi = await context.LoadOneAsync <SesiUjian>(x => x.Id == id);
var user = await context.LoadOneAsync <Pengguna>(x => x.MyKad == sesi.MyKad);
var ujianTask = context.LoadOneAsync <Ujian>(x => x.Id == sesi.NamaUjian);
var permohonanTask = context.LoadOneAsync <Permohonan>(x => x.PermohonanNo == sesi.NamaProgram);
var recommendationTask = context.LoadAsync(context.CreateQueryable <PpkpRecommendation>());
var skorTask = context.LoadAsync(context.CreateQueryable <SkorPpkp>(), 1, 150, true);
await Task.WhenAll(ujianTask, permohonanTask, recommendationTask, skorTask);
var rlo = await recommendationTask;
var skorLo = await skorTask;
if (null == sesi)
{
return(HttpNotFound("Cannot find SesiUjian " + id));
}
if (null == user)
{
return(HttpNotFound("Cannot find user with MyKad " + sesi.MyKad));
}
var vm = new PpkpTraitViewModel(sesi, rlo.ItemCollection.ToArray(), skorLo.ItemCollection.ToArray())
{
Permohonan = await permohonanTask,
Ujian = await ujianTask,
Pengguna = user
};
return(View("ppkp.khusus", vm));
}
public async Task <ActionResult> PrintIndikatorIso(string id)
{
var context = new SphDataContext();
var sesi = await context.LoadOneAsync <SesiUjian>(x => x.Id == id);
var user = await context.LoadOneAsync <Pengguna>(x => x.MyKad == sesi.MyKad);
var ujianTask = context.LoadOneAsync <Ujian>(x => x.Id == sesi.NamaUjian);
var permohonanTask = context.LoadOneAsync <Permohonan>(x => x.PermohonanNo == sesi.NamaProgram);
var recommendationTask = context.LoadAsync(context.CreateQueryable <IsoRecommendation>());
var soalanTask = context.LoadAsync(context.CreateQueryable <Soalan>().Where(x => x.NamaUjian == "Inventori Stres Organisasi (ISO)"), 1, 120, true);
await Task.WhenAll(ujianTask, permohonanTask, recommendationTask, soalanTask);
var rlo = await recommendationTask;
var soalanLo = await soalanTask;
if (null == sesi)
{
return(HttpNotFound("Cannot find SesiUjian " + id));
}
if (null == user)
{
return(HttpNotFound("Cannot find user with MyKad " + sesi.MyKad));
}
var vm = new IsoTraitViewModel(sesi, user, rlo.ItemCollection, soalanLo.ItemCollection)
{
Permohonan = await permohonanTask,
Ujian = await ujianTask,
Pengguna = user
};
return(View("Indikator-Iso", vm));
}
public async Task <ActionResult> PrintTraitForHlp(string id)
{
var context = new SphDataContext();
var sesi = await context.LoadOneAsync <SesiUjian>(x => x.Id == id);
var user = await context.LoadOneAsync <Pengguna>(x => x.MyKad == sesi.MyKad);
var ujianTask = context.LoadOneAsync <Bespoke.epsikologi_ujian.Domain.Ujian>(x => x.Id == sesi.NamaUjian);
var permohonanTask =
context.LoadOneAsync <Bespoke.epsikologi_permohonan.Domain.Permohonan>(
x => x.PermohonanNo == sesi.NamaProgram);
await Task.WhenAll(ujianTask, permohonanTask);
var query = context.CreateQueryable <SkorHlp>();
var lo = await context.LoadAsync(query, size : 1000);
var scoreTables = lo.ItemCollection;
var rq = context.CreateQueryable <HlpRecomendation>();
var rlo = await context.LoadAsync(rq, size : 200);
var recommendations = rlo.ItemCollection;
if (null == sesi)
{
return(HttpNotFound("Cannot find SesiUjian " + id));
}
if (null == user)
{
return(HttpNotFound("Cannot find user with MyKad " + sesi.MyKad));
}
var vm = new HlpTraitViewModel(sesi, user, scoreTables.ToArray(), recommendations.ToArray())
{
Permohonan = await permohonanTask,
Ujian = await ujianTask
};
var viewName = "Trait-Hlp-" + user.Jantina;
// const string STYLE = "border:3px solid red";
// return Pdf(viewName, vm, "~/Views/PrintReport/_MasterPage.NoHeader.cshtml",
// x => x
// .Replace($"id=\"KB{vm.KB.Point}\"", $"id=\"KB{vm.KB.Point}\" style=\"{STYLE}\"")
// .Replace($"id=\"FR{vm.FR.Percentile}\"", $"id=\"FR{vm.FR.Percentile}\" style=\"{STYLE}\"")
// .Replace($"id=\"KT{vm.KT.Percentile}\"", $"id=\"KT{vm.KT.Percentile}\" style=\"{STYLE}\"")
// .Replace($"id=\"KC{vm.KC.Percentile}\"", $"id=\"KC{vm.KC.Percentile}\" style=\"{STYLE}\"")
// .Replace($"id=\"LP{vm.LP.Percentile}\"", $"id=\"LP{vm.LP.Percentile}\" style=\"{STYLE}\"")
// .Replace($"id=\"SM{vm.SM.Percentile}\"", $"id=\"SM{vm.SM.Percentile}\" style=\"{STYLE}\"")
// .Replace($"id=\"AS{vm.AS.Percentile}\"", $"id=\"AS{vm.AS.Percentile}\" style=\"{STYLE}\"")
// .Replace($"id=\"AF{vm.AF.Percentile}\"", $"id=\"AF{vm.AF.Percentile}\" style=\"{STYLE}\"")
// .Replace($"id=\"TL{vm.TL.Percentile}\"", $"id=\"TL{vm.TL.Percentile}\" style=\"{STYLE}\"")
// .Replace($"id=\"DT{vm.DT.Percentile}\"", $"id=\"DT{vm.DT.Percentile}\" style=\"{STYLE}\""), true);
return(View(viewName, vm));
}
public async Task <ActionResult> IndikatorUkbp(string id)
{
var context = new SphDataContext();
var sesi = await context.LoadOneAsync <SesiUjian>(x => x.Id == id);
var user = await context.LoadOneAsync <Pengguna>(x => x.MyKad == sesi.MyKad);
var ujianTask = context.LoadOneAsync <Ujian>(x => x.Id == sesi.NamaUjian);
var permohonanTask = context.LoadOneAsync <Permohonan>(x => x.PermohonanNo == sesi.NamaProgram);
var querySkorUkbp = context.CreateQueryable <SkorUkbp>().Where(x => x.Jantina == user.Jantina || x.Jantina == "NA");
var scoreTask = context.LoadAsync(querySkorUkbp, 1, 200);
var recommendationTask = context.LoadAsync(context.CreateQueryable <UkbpRecommendation>(), 1, 200);
await Task.WhenAll(ujianTask, permohonanTask, scoreTask, recommendationTask);
var scores = await scoreTask;
var recommendations = await recommendationTask;
if (null == sesi)
{
return(HttpNotFound("Cannot find SesiUjian " + id));
}
if (null == user)
{
return(HttpNotFound("Cannot find user with MyKad " + sesi.MyKad));
}
SesiUjian sesiA, sesiB;
if (sesi.NamaUjian == "UKBP-A")
{
sesiA = sesi;
sesiB = await context.LoadOneAsync <SesiUjian>(x => x.NamaUjian == "UKBP-B" && x.NamaProgram == sesi.NamaProgram && x.MyKad == sesi.MyKad);
}
else
{
sesiB = sesi;
sesiA = await context.LoadOneAsync <SesiUjian>(x => x.NamaUjian == "UKBP-A" && x.NamaProgram == sesi.NamaProgram && x.MyKad == sesi.MyKad);
}
var vm = new UkbpTraitViewModel(sesiA, sesiB, scores.ItemCollection.ToArray(), recommendations.ItemCollection.ToArray())
{
Permohonan = await permohonanTask,
Ujian = await ujianTask,
Pengguna = user
};
return(View("Indikator-UKBP", vm));
}
public async Task <ActionResult> Index()
{
var context = new SphDataContext();
var profile = await context.LoadOneAsync <UserProfile>(ua => ua.UserName == User.Identity.Name);
if (null == profile)
{
return(View("Default", new JpaHomeViewModel {
Designation = new Designation {
IsHelpVisible = false
}
}));
}
var designation = (await context.LoadOneAsync <Designation>(d => d.Name == profile.Designation)) ?? new Designation {
IsHelpVisible = true, HelpUri = "/docs/"
};
designation.HelpUri = string.IsNullOrWhiteSpace(designation.HelpUri) ? "/docs/" : designation.HelpUri;
var query = context.CreateQueryable <Message>();
var messagesLo = await context.LoadAsync(query.Where(x => x.UserName == User.Identity.Name && x.IsRead == false), 1, 5, true);
var vm = new JpaHomeViewModel
{
Profile = profile,
Designation = designation,
StartModule = "#" + profile.StartModule,
TotalMessageCount = messagesLo.TotalRows ?? 0
};
vm.Messages.AddRange(messagesLo.ItemCollection);
if (this.User.IsInRole("Developers"))
{
return(Redirect("/sph#dev.home"));
}
return(View("Default", vm));
}
public async Task <ActionResult> ExportSesiUjianByYearToExcel(ProgramReportModel model)
{
var year = model.Tahun;
if (0 == year)
{
year = DateTime.Now.Year;
}
var temp = Path.GetTempFileName() + ".xlsx";
System.IO.File.Copy(Server.MapPath("~/App_Data/template/laporan-sesi-ujian.xlsx"), temp, true);
var file = new FileInfo(temp);
var excel = new ExcelPackage(file);
var ws = excel.Workbook.Worksheets[model.Ujian];
var context = new SphDataContext();
//var program = await context.GetScalarAsync<SesiUjian, string>(x => x.TarikhUjian.Value.Year == year, x => x.NamaProgram);
var query = context.CreateQueryable <SesiUjian>()
.Where(s => s.NamaProgram == model.Program)
.Where(s => s.Status == "Diambil")
.Where(s => s.NamaUjian == model.Ujian);
var sesiLo = await context.LoadAsync(query, 1, 300, true);
var sesi = sesiLo.ItemCollection;
while (sesiLo.HasNextPage)
{
sesiLo = await context.LoadAsync(query, sesiLo.CurrentPage + 1, 300, true);
sesi.AddRange(sesiLo.ItemCollection);
}
var soalanQuery = context.CreateQueryable <Soalan>()
.Where(s => s.NamaUjian == model.Ujian);
var soalanLo = await context.LoadAsync(soalanQuery, 1, 300, true);
var soalans = soalanLo.ItemCollection.OrderBy(s => s.Susunan);
ws.Cells[1, 1].Value = "Laporan Sesi Ujian "
+ model.Ujian
+ " bagi Program "
+ model.Program
+ " tahun "
+ model.Tahun.ToString();
var column1 = 4;
foreach (var soalan in soalans)
{
column1++;
var noSeksyen = !String.IsNullOrEmpty(soalan.SeksyenSoalan) ? "S" + soalan.SeksyenSoalan : "";
var noSoalan = "Q" + Convert.ToInt32(soalan.Susunan);
ws.Cells[2, column1].Value = noSeksyen + noSoalan;
}
var row = 2;
foreach (var s in sesi)
{
row++;
ws.InsertRow(row, 1, row);
ws.Cells[row, 1].Value = s.NamaPengguna;
ws.Cells[row, 2].Value = model.Ujian;
ws.Cells[row, 3].Value = s.MyKad;
ws.Cells[row, 4].Value = $"{s.TarikhUjian:dd/MM/yyyy HH:mm}";
var column = 4;
foreach (var t in soalans)
{
column += 1;
var jawapan = s.JawapanCollection.FirstOrDefault(j => j.SoalanNo == t.SoalanNo);
var score = jawapan != null ? jawapan.Nilai : 0;
ws.Cells[row, column].Value = score;
}
}
excel.Save();
excel.Dispose();
return(Json(new { success = true, path = Path.GetFileName(temp) }));
//return File(temp, MimeMapping.GetMimeMapping(".xlsx"), "Laporan Markah dan Percentile IPU.xlsx");
}
public async Task <ActionResult> Program(ProgramReportModel model)
{
if (model.Ujian == "IPU")
{
var html2 = await IpuTraitViewModel.GenerateLaporanTable(model);
return(Content(html2, "text/html", Encoding.UTF8));
}
if (model.Ujian.Contains("UKBP"))
{
var html2 = await UkbpTraitViewModel.GenerateLaporanTable(model);
return(Content(html2, "text/html", Encoding.UTF8));
}
var context = new SphDataContext();
var no = $"{model.Program}/{model.Bil}/{model.Siri}/{model.Tahun}";
var ujian = await context.LoadOneAsync <Ujian>(x => x.UjianNo == model.Ujian || x.NamaUjian == model.Ujian);
var query = context.CreateQueryable <SesiUjian>()
.Where(s => s.NamaProgram == model.Program)
.Where(s => s.NamaUjian == model.Ujian)
.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 == ujian.UjianNo || s.NamaUjian == ujian.NamaUjian);
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 ppkp = model.Ujian == "PPKP";
var html = new StringBuilder();
html.AppendLine("<table class=\"table table-striped table-bordered\">");
html.AppendLine(" <thead>");
html.AppendLine(" <tr>");
html.AppendLine(" <th>Nama</th>");
html.AppendLine(" <th>Tarikh</th>");
if (ppkp)
{
var namaTraits = new [] { "A", "B", "C", "D", "E" };
foreach (var t in namaTraits)
{
html.AppendLine(" <th>" + t + "</th>");
}
}
foreach (var t in traits)
{
html.AppendLine(" <th>" + t + "</th>");
}
html.AppendLine(" <th>Cetakan Individu</th>");
html.AppendLine(" </tr>");
html.AppendLine(" </thead>");
html.AppendLine(" <tbody>");
foreach (var s in sesi)
{
html.AppendLine(" <tr>");
html.AppendLine(" <td>" + s.NamaPengguna + "</td>");
html.AppendFormat(" <td>{0:dd/MM/yyyy}</td>", s.TarikhUjian);
if (ppkp)
{
var namaTraits = new [] { "A", "B", "C", "D", "E" };
foreach (var t in namaTraits)
{
var t1 = t;
var score = s.JawapanCollection.Where(a => a.Trait.StartsWith(t1)).Sum(a => a.Nilai);
html.AppendLine(" <td>" + score + "</td>");
}
}
foreach (var t in traits)
{
var t1 = t;
var score = s.JawapanCollection.Where(a => a.Trait == t1).Sum(a => a.Nilai);
html.AppendLine(" <td>" + score + "</td>");
}
var ip = s.NamaUjian.Contains("IP") && !s.NamaUjian.Contains("IPU");
var ibk = s.NamaUjian.Contains("IBK");
var iso = s.NamaUjian.Contains("ISO");
var hlp = s.NamaUjian.Contains("HLP");
var ukbp = s.NamaUjian.Contains("UKBP");
var indikator = ibk || ip || hlp || iso ? "" :
$@"<a class=""indikator-report btn btn-info"" target=""_blank"" href=""cetak-laporan/indikator/{
s.NamaUjian}/{s.Id}""> <i class=""fa fa-print""></i> Indikator</a>";
if (ppkp)
{
indikator = $@"<a class=""laporan-profile-report btn btn-info"" target=""_blank""
href=""cetak-laporan/ppkp/profile/{s.Id}""> <i class=""fa fa-print""></i> Profil</a>
<a class=""laporan-umum-report btn btn-info"" target=""_blank""
href=""cetak-laporan/ppkp/umum/{s.Id}""> <i class=""fa fa-print""></i> Umum</a>
<a class=""laporan-khusus-report btn btn-info"" target=""_blank""
href=""cetak-laporan/ppkp/khusus/{s.Id}""> <i class=""fa fa-print""></i> Khusus</a>"
;
html.AppendLine($@"
<td>
{indikator}
</td>");
}
else if (ukbp)
{
indikator = $@"<a class=""btn btn-info"" target=""_blank""
href=""cetak-laporan/indikator/ukbp/{s.Id}""> <i class=""fa fa-print""></i>Indikator</a>";
html.AppendLine($@"
<td>
{indikator}
</td>");
}
else
{
html.AppendFormat(@"
<td>
<a class=""trait-report btn btn-info"" target=""_blank"" href=""cetak-laporan/trait/{2}/{0}""> <i class=""fa fa-print""></i> Tret</a>
{1}
</td>", s.Id, indikator, s.NamaUjian);
}
html.AppendLine(" </tr>");
}
// sum
html.AppendLine(" <tr>");
html.AppendLine(" <td>Jumlah Markah</td>");
html.AppendLine(" <td></td>");
if (ppkp)
{
var namaTraits = new [] { "A", "B", "C", "D", "E" };
foreach (var t in namaTraits)
{
var t1 = t;
var score = sesi.SelectMany(x => x.JawapanCollection).Where(a => a.Trait.StartsWith(t1)).Sum(a => a.Nilai);
html.AppendLine(" <td>" + score + "</td>");
}
}
foreach (var t in traits)
{
var t1 = t;
var score = sesi.SelectMany(x => x.JawapanCollection).Where(a => a.Trait == t1).Sum(a => a.Nilai);
html.AppendLine(" <td>" + score + "</td>");
}
html.AppendLine(" <td></td>");
html.AppendLine(" </tr>");
// average
html.AppendLine(" <tr>");
html.AppendLine(" <td>Purata Markah</td>");
html.AppendLine(" <td></td>");
if (ppkp)
{
var namaTraits = new [] { "A", "B", "C", "D", "E" };
foreach (var t in namaTraits)
{
var t1 = t;
if (sesi.Count > 0)
{
var avg = sesi.SelectMany(x => x.JawapanCollection).Where(a => a.Trait.StartsWith(t1)).Sum(a => a.Nilai) / sesi.Count;
html.AppendLine(" <td>" + avg + "</td>");
}
else
{
html.AppendLine(" <td> NA</td>");
}
}
}
foreach (var t in traits)
{
var t1 = t;
if (sesi.Count > 0)
{
var avg = sesi.SelectMany(x => x.JawapanCollection).Where(a => a.Trait == t1).Sum(a => a.Nilai) / sesi.Count;
html.AppendLine(" <td>" + avg + "</td>");
}
else
{
html.AppendLine(" <td> NA</td>");
}
}
html.AppendLine(" <td></td>");
html.AppendLine(" </tr>");
html.AppendLine("</tbody>");
html.AppendLine("</table>");
return(Content(html.ToString(), "text/html", Encoding.UTF8));
}
public static async Task <string> GenerateLaporanTable(ProgramReportModel model)
{
var context = new SphDataContext();
var no = $"{model.Program}/{model.Bil}/{model.Siri}/{model.Tahun}";
var ujian = await context.LoadOneAsync <Ujian>(x => x.Id == "UKBP-A");
var query = context.CreateQueryable <SesiUjian>()
.Where(s => s.NamaProgram == model.Program)
.Where(s => s.NamaUjian == model.Ujian)
.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 == ujian.UjianNo || s.NamaUjian == ujian.NamaUjian);
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 html = new StringBuilder();
html.AppendLine("<table class=\"table table-striped table-bordered\">");
html.AppendLine(" <thead>");
html.AppendLine(" <tr>");
html.AppendLine(" <th>Nama</th>");
html.AppendLine(" <th>Tarikh</th>");
foreach (var t in traits)
{
html.AppendLine(" <th>" + t + "</th>");
var t1 = t;
if (t1 == "KBY" || t1 == "NAS" || t1 == "PTL1" || t1 == "PTL2" || t1 == "PTL3")
{
continue;
}
html.AppendLine(" <th>%</th>");
}
html.AppendLine(" <th>Cetakan Individu</th>");
html.AppendLine(" </tr>");
html.AppendLine(" </thead>");
html.AppendLine(" <tbody>");
foreach (var s in sesi)
{
html.AppendLine(" <tr>");
html.AppendLine(" <td>" + s.NamaPengguna + "</td>");
html.AppendFormat(" <td>{0:dd/MM/yyyy HH:mm}</td>", s.TarikhUjian);
var responden = await context.LoadOneAsync <Pengguna>(x => x.MyKad == s.MyKad);
foreach (var t in traits)
{
var t1 = t;
var t2 = t;
switch (t1)
{
case "AKS": t2 = "AS"; break;
case "ASF": t2 = "AF"; break;
case "KBY": t2 = "KBY"; break;
case "KCN": t2 = "KC"; break;
case "KTG": t2 = "KT"; break;
case "LPN": t2 = "LP"; break;
case "NAS": t2 = "NE"; break;
case "RAS": t2 = "BR"; break;
case "SIM": t2 = "BS"; break;
case "TOL": t2 = "TL"; break;
}
var score = s.JawapanCollection.Where(a => a.Trait == t1).Sum(a => a.Nilai);
html.AppendLine(" <td>" + score + "</td>");
if (t1 == "KBY" || t1 == "NAS" || t1 == "PTL1" || t1 == "PTL2" || t1 == "PTL3")
{
continue;
}
var lookup = await context.LoadOneAsync <SkorUkbp>(x => score.IsBetween(x.NilaiMin, x.NilaiMax, true, true) && x.Tret == t2 && x.Jantina == responden.Jantina);
html.AppendLine(null == lookup
? $" <td>{score} : {t1} : {responden.Jantina}</td>"
: $" <td>{lookup.Percentile}</td>");
}
var button = $@"
<td>
<a class=""trait-report btn btn-info"" target=""_blank"" href=""cetak-laporan/indikator/ukbp/{s.Id}""> <i class=""fa fa-print""></i> Indikator</a>
</td>";
html.AppendLine(button);
html.AppendLine(" </tr>");
}
// sum
html.AppendLine(" <tr>");
html.AppendLine(" <td>Jumlah Markah</td>");
html.AppendLine(" <td></td>");
foreach (var t in traits)
{
var t1 = t;
var score = sesi.SelectMany(x => x.JawapanCollection).Where(a => a.Trait == t1).Sum(a => a.Nilai);
html.AppendLine(" <td colspan=\"2\">" + score + "</td>");
}
html.AppendLine(" <td></td>");
html.AppendLine(" </tr>");
// average
html.AppendLine(" <tr>");
html.AppendLine(" <td>Purata Markah</td>");
html.AppendLine(" <td></td>");
foreach (var t in traits)
{
var t1 = t;
if (sesi.Count > 0)
{
var avg = sesi.SelectMany(x => x.JawapanCollection).Where(a => a.Trait == t1).Sum(a => a.Nilai) / sesi.Count;
html.AppendLine(" <td colspan=\"2\">" + avg + "</td>");
}
else
{
html.AppendLine(" <td> NA</td>");
}
}
html.AppendLine(" <td></td>");
html.AppendLine(" </tr>");
html.AppendLine("</tbody>");
html.AppendLine("</table>");
html.AppendLine("<div><em>Nota: % = Percentile</em></di>");
return(html.ToString());
}
public static async Task <string> GenerateLaporanTable(ProgramReportModel model)
{
var context = new SphDataContext();
var no = $"{model.Program}/{model.Bil}/{model.Siri}/{model.Tahun}";
var ujian = await context.LoadOneAsync <Ujian>(x => x.Id == "IPU");
var query = context.CreateQueryable <SesiUjian>()
.Where(s => s.NamaProgram == model.Program)
.Where(s => s.NamaUjian == model.Ujian)
.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 == ujian.UjianNo || s.NamaUjian == ujian.NamaUjian);
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 html = new StringBuilder();
html.AppendLine("<table class=\"table table-striped table-bordered\">");
html.AppendLine(" <thead>");
html.AppendLine(" <tr>");
html.AppendLine(" <th>Nama</th>");
html.AppendLine(" <th>Tarikh</th>");
foreach (var t in traits)
{
html.AppendLine(" <th>" + t + "</th>");
html.AppendLine(" <th>%</th>");
}
html.AppendLine(" <th>Cetakan Individu</th>");
html.AppendLine(" </tr>");
html.AppendLine(" </thead>");
html.AppendLine(" <tbody>");
foreach (var s in sesi)
{
var user = await context.LoadOneAsync <Pengguna>(x => x.MyKad == s.MyKad);
html.AppendLine(" <tr>");
html.AppendLine(" <td>" + s.NamaPengguna + "</td>");
html.AppendFormat(" <td>{0:dd/MM/yyyy HH:mm}</td>", s.TarikhUjian);
foreach (var t in traits)
{
var t1 = t;
var score = s.JawapanCollection.Where(a => a.Trait == t1).Sum(a => a.Nilai);
html.AppendLine(" <td>" + score + "</td>");
if (t1 == "J")
{
html.AppendLine($" <td>{score}</td>");
}
else
{
var lookup = await context.LoadOneAsync <SkorIPU>(x => score.IsBetween(x.NilaiMin, x.NilaiMax, true, true) && x.Jantina == user.Jantina && x.Tret == t1);
html.AppendLine($" <td>{lookup.Percentile}</td>");
}
}
var button = $@"
<td>
<a class=""trait-report btn btn-info"" target=""_blank"" href=""cetak-laporan/trait/ipu/{s.Id}""> <i class=""fa fa-print""></i> Tret</a>
<a class=""indikator-report btn btn-info"" target=""_blank"" href=""cetak-laporan/indikator/ipu/{s.Id}""> <i class=""fa fa-table""></i> Indikator</a>
<a class=""excel-report btn btn-info"" download href=""cetak-laporan/xls/ipu/{s.Id}""> <i class=""fa fa-file-excel-o""></i> Simpan Markah</a>
</td>";
html.AppendLine(button);
html.AppendLine(" </tr>");
}
// sum
html.AppendLine(" <tr>");
html.AppendLine(" <td>Jumlah Markah</td>");
html.AppendLine(" <td></td>");
foreach (var t in traits)
{
var t1 = t;
var score = sesi.SelectMany(x => x.JawapanCollection).Where(a => a.Trait == t1).Sum(a => a.Nilai);
html.AppendLine(" <td colspan=\"2\">" + score + "</td>");
}
html.AppendLine(" <td></td>");
html.AppendLine(" </tr>");
// average
html.AppendLine(" <tr>");
html.AppendLine(" <td>Purata Markah</td>");
html.AppendLine(" <td></td>");
foreach (var t in traits)
{
var t1 = t;
if (sesi.Count > 0)
{
var avg = sesi.SelectMany(x => x.JawapanCollection).Where(a => a.Trait == t1).Sum(a => a.Nilai) / sesi.Count;
html.AppendLine(" <td colspan=\"2\">" + avg + "</td>");
}
else
{
html.AppendLine(" <td> NA</td>");
}
}
html.AppendLine(" <td></td>");
html.AppendLine(" </tr>");
html.AppendLine("</tbody>");
html.AppendLine("</table>");
html.AppendLine("<div><em>Nota: % = Percentile</em></di>");
return(html.ToString());
}
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"));
}