partial void BeforeTransform(Bespoke.PosEntt.SalesOrders.Domain.SalesOrder item, Bespoke.PosEntt.Adapters.SnbWebApi.PostSalesOrdersRequest destination) { var context = new SphDataContext(); if (m_products.Count == 0) { var query = context.CreateQueryable <Bespoke.PosEntt.Products.Domain.Product>() .Where(p => p.Id != "0"); var productRepos = ObjectBuilder.GetObject <IRepository <Bespoke.PosEntt.Products.Domain.Product> >(); var lo = productRepos.LoadAsync(query, 1, 200, false).Result; m_products.AddRange(lo.ItemCollection); } if (m_surcharges.Count == 0) { var surchargeQuery = context.CreateQueryable <Bespoke.PosEntt.SurchargeAddOns.Domain.SurchargeAddOn>() .Where(p => p.Id != "0"); var surchargeRepos = ObjectBuilder.GetObject <IRepository <Bespoke.PosEntt.SurchargeAddOns.Domain.SurchargeAddOn> >(); var scLo = surchargeRepos.LoadAsync(surchargeQuery, 1, 200, false).Result; m_surcharges.AddRange(scLo.ItemCollection); } if (m_categories.Count == 0) { var categoryQuery = context.CreateQueryable <Bespoke.PosEntt.ItemCategories.Domain.ItemCategory>() .Where(p => p.Id != "0"); var categoryRepos = ObjectBuilder.GetObject <IRepository <Bespoke.PosEntt.ItemCategories.Domain.ItemCategory> >(); var categoriesLo = categoryRepos.LoadAsync(categoryQuery, 1, 200, false).Result; m_categories.AddRange(categoriesLo.ItemCollection); } }
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")); }