public ActionResult Index(RiskTableViewModel vm)
{
vm.RiskList = new List <Risk>();
vm.CellList = new List <CellData>();
UpdateParam(vm);
PrepareRowsAndCols(vm);
CalcRisk(vm);
CalcCellValues(vm);
ViewBag.DataTypeText = GetDataTypeText(vm.Param.DataTypeId);
return(View(vm));
}
public ActionResult Index()
{
RiskTableViewModel vm = new RiskTableViewModel();
vm.RiskList = new List <Risk>();
vm.Param = new RiskTableParam();
vm.CellList = new List <CellData>();
vm.Param.PosId = 1;
vm.Param.IsApproved = true;
vm.Param.ReportDate = DateTime.Now;
vm.Param.ReportDate2 = DateTime.Now;
vm.Param.DataTypeId = DATATYPE_COUNT;
vm.Param.RowGroupId = GROUP_NONE;
vm.Param.ColGroupId = GROUP_NONE;
UpdateParam(vm);
PrepareRowsAndCols(vm);
CalcRisk(vm);
CalcCellValues(vm);
ViewBag.DataTypeText = GetDataTypeText(vm.Param.DataTypeId);
return(View(vm));
}
private void PrepareRowsAndCols(RiskTableViewModel vm)
{
vm.Param.RowIds = new List <int>();
vm.Param.ColIds = new List <int>();
vm.Param.RowLabels = new List <string>();
vm.Param.ColLabels = new List <string>();
string header;
GetGroupIdsLabels(vm.Param.ColGroupId, vm.Param.ColIds, vm.Param.ColLabels, out header);
vm.Param.ColHeader = header;
GetGroupIdsLabels(vm.Param.RowGroupId, vm.Param.RowIds, vm.Param.RowLabels, out header);
vm.Param.RowHeader = header;
vm.Param.ColCount = vm.Param.ColIds.Count();
vm.Param.RowCount = vm.Param.RowIds.Count();
vm.Param.RowTotals = new List <decimal>();
for (var i = 0; i < vm.Param.RowCount; i++)
{
vm.Param.RowTotals.Add(0M);
}
vm.Param.ColTotals = new List <decimal>();
for (var i = 0; i < vm.Param.ColCount; i++)
{
vm.Param.ColTotals.Add(0M);
}
foreach (var i in vm.Param.RowIds)
{
foreach (var j in vm.Param.ColIds)
{
vm.CellList.Add(new CellData()
{
RowId = i, ColId = j, Count = 0, Values = 0
});
}
}
}
private void CalcCellValues(RiskTableViewModel vm)
{
int row;
int col;
foreach (var r in vm.RiskList)
{
row = GetValueId(r, vm.Param.RowGroupId, vm.Param.RowIds);
col = GetValueId(r, vm.Param.ColGroupId, vm.Param.ColIds);
foreach (var cell in vm.CellList)
{
if (cell.RowId == row && cell.ColId == col)
{
cell.Count++;
switch (vm.Param.DataTypeId)
{
case DATATYPE_COUNT:
cell.Values = 1;
break;
case DATATYPE_PROBLEVEL:
cell.Values += (int)r.ProbLevelId;
break;
case DATATYPE_IMPACTLEVEL:
cell.Values += (int)r.ImpactLevelId;
break;
case DATATYPE_RISKLEVEL:
cell.Values += (int)r.RiskLevel;
break;
}
}
}
}
}
private void UpdateParam(RiskTableViewModel vm)
{
Dictionary <int, string> posList = new Dictionary <int, string>();
posList.Add(1, "Nasional");
posList.Add(2, "Kantor Pusat");
posList.Add(3, "Kantor Cabang");
posList.Add(4, "Korwil");
posList.Add(5, "Kelas KC");
vm.Param.PosList = new SelectList(posList, "Key", "Value", vm.Param.PosId);
Dictionary <int, string> branchList = new Dictionary <int, string>();
foreach (var branch in db.Branches.OrderBy(m => m.ClassId).ThenBy(m => m.BranchName))
{
branchList.Add(branch.BranchId, branch.BranchName + " (Kelas " + branch.BranchClass.ClassName + ")");
}
vm.Param.Branches = new SelectList(branchList, "Key", "Value", vm.Param.BranchId);
if (vm.Param.PosId == 2)
{
Dictionary <int, string> divList = new Dictionary <int, string>();
foreach (var div in db.SubDivs.OrderBy(m => m.SubDivId).ThenBy(m => m.SubDivId))
{
divList.Add(div.SubDivId, div.SubDivName);
}
vm.Param.Unit = new SelectList(divList, "Key", "Value", vm.Param.SubDivId);
}
else if (vm.Param.PosId == 3)
{
Dictionary <int, string> branchList2 = new Dictionary <int, string>();
foreach (var branch in db.Branches.OrderBy(m => m.ClassId).ThenBy(m => m.BranchName))
{
branchList2.Add(branch.BranchId, branch.BranchName + " (Kelas " + branch.BranchClass.ClassName + ")");
}
vm.Param.Unit = new SelectList(branchList2, "Key", "Value", vm.Param.BranchId);
}
else if (vm.Param.PosId == 4)
{
Dictionary <int, string> korwilList = new Dictionary <int, string>();
foreach (var korwil in db.Korwils.OrderBy(m => m.KorwilId).ThenBy(m => m.Korwil1))
{
korwilList.Add(korwil.KorwilId, korwil.Korwil1);
}
vm.Param.Unit = new SelectList(korwilList, "Key", "Value", vm.Param.BranchId);
}
else if (vm.Param.PosId == 5)
{
Dictionary <int, string> kelasList = new Dictionary <int, string>();
foreach (var kelas in db.BranchClasses.OrderBy(m => m.ClassId).ThenBy(m => m.ClassName))
{
kelasList.Add(kelas.ClassId, kelas.ClassName);
}
vm.Param.Unit = new SelectList(kelasList, "Key", "Value", vm.Param.BranchId);
}
else
{
vm.Param.Unit = new SelectList(Enumerable.Empty <SelectListItem>());
}
Dictionary <int, string> dataList = new Dictionary <int, string>();
dataList.Add(DATATYPE_COUNT, "Jumlah Data");
dataList.Add(DATATYPE_PROBLEVEL, "Tingkat Probabilitas");
dataList.Add(DATATYPE_IMPACTLEVEL, "Tingkat Dampak");
dataList.Add(DATATYPE_RISKLEVEL, "Tingkat Risiko");
vm.Param.DataTypes = new SelectList(dataList, "Key", "Value", vm.Param.DataTypeId);
Dictionary <int, string> groupList = new Dictionary <int, string>();
groupList.Add(GROUP_NONE, "(Tanpa pengelompokan)");
groupList.Add(GROUP_CAUSE, "Sebab Risiko");
groupList.Add(GROUP_EFFECT, "Akibat Risiko");
groupList.Add(GROUP_RISKCLASS, "Klasifikasi Risiko");
groupList.Add(GROUP_HQ_BRANCH, "Pusat/Cabang");
groupList.Add(GROUP_CLASSBRANCH, "Kelas Cabang");
groupList.Add(GROUP_BRANCH_CLASS1, "Cabang Kelas I");
groupList.Add(GROUP_BRANCH_CLASS2, "Cabang Kelas II");
groupList.Add(GROUP_BRANCH_CLASS3, "Cabang Kelas III");
groupList.Add(GROUP_PROBLEVEL, "Tingkat Probabilitas");
groupList.Add(GROUP_IMPACTLEVEL, "Tingkat Dampak");
groupList.Add(GROUP_RISKLEVEL, "Tingkat Risiko");
groupList.Add(GROUP_RANGERISKLEVEL, "Kelompok Tingkat Risiko");
vm.Param.RowGroups = new SelectList(groupList, "Key", "Value", vm.Param.RowGroupId);
vm.Param.ColGroups = new SelectList(groupList, "Key", "Value", vm.Param.ColGroupId);
}
public void ExportToExcel(int?posId, int?branchId, DateTime reportDate, DateTime reportDate2, bool isApproved, int dataTypeId, int rowGroupId, int colGroupId)
{
RiskTableViewModel vm = new RiskTableViewModel();
vm.RiskList = new List <Risk>();
vm.Param = new RiskTableParam();
vm.CellList = new List <CellData>();
vm.Param.PosId = posId;
vm.Param.BranchId = branchId;
vm.Param.ReportDate = reportDate;
vm.Param.ReportDate2 = reportDate2;
vm.Param.IsApproved = isApproved;
vm.Param.DataTypeId = dataTypeId;
vm.Param.RowGroupId = rowGroupId;
vm.Param.ColGroupId = colGroupId;
UpdateParam(vm);
PrepareRowsAndCols(vm);
CalcRisk(vm);
CalcCellValues(vm);
int rowId = -1;
int colId = -1;
int index = -1;
int intValue = 0;
decimal decValue = 0;
StringWriter sw = new StringWriter();
sw.WriteLine("Data: " + GetDataTypeText(vm.Param.DataTypeId));
sw.WriteLine("<table rules='all' border='1' style='border-collapse:collapse;'>");
if (vm.Param.ColCount > 1)
{
sw.WriteLine("<tr>");
sw.WriteLine("<th rowspan='2' style='text-align: center; background-color: #eee'>" + vm.Param.RowHeader + "</th>");
sw.WriteLine("<th colspan='" + vm.Param.ColCount + "' style='text-align: center; background-color: #eee'>" + vm.Param.ColHeader + "</th>");
sw.WriteLine("</tr>");
}
sw.WriteLine("<tr>");
if (vm.Param.ColCount == 1)
{
sw.WriteLine("<th style='text-align: center; background-color: #eee'>" + vm.Param.RowHeader + "</th>");
}
for (var i = 0; i < vm.Param.ColCount; i++)
{
sw.WriteLine("<th style='text-align: center; background-color: #eee'>");
sw.WriteLine(vm.Param.ColLabels[i]);
sw.WriteLine("</th>");
}
sw.WriteLine("</tr>");
for (var i = 0; i < vm.Param.RowCount; i++)
{
sw.WriteLine("<tr>");
sw.WriteLine("<td style='background-color: #eee; font-weight: bold'>" + vm.Param.RowLabels[i] + "</td>");
for (var j = 0; j < vm.Param.ColCount; j++)
{
sw.WriteLine("<td align='right'>");
{
rowId = vm.Param.RowIds[i];
colId = vm.Param.ColIds[j];
index = RiskTableViewModel.GetCellDataIndex(rowId, colId, vm.CellList);
if (index >= 0)
{
if (vm.Param.DataTypeId == 1)
{
intValue = vm.CellList[index].Count;
if (intValue > 0)
{
sw.WriteLine(intValue);
}
}
else
{
if (vm.CellList[index].Count > 0)
{
decValue = vm.CellList[index].Values / vm.CellList[index].Count;
if (decValue > 0)
{
sw.WriteLine(string.Format("{0:#,##0.##}", decValue));
}
}
}
}
}
sw.WriteLine("</td>");
}
sw.WriteLine("</tr>");
}
sw.WriteLine("</table>");
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment;filename=risk_table.xls");
Response.ContentType = "application/vnd.ms-excel";
Response.Write(sw.ToString());
Response.End();
}
private void CalcRisk(RiskTableViewModel vm)
{
var risks = db.Risks.Where(p => p.ProbLevelId != null && p.ImpactLevelId != null && p.RiskCatId != null);
/*if (vm.Param.PosId == 1)
* {
* risks = risks.Where(p => p.DeptId != null && p.BranchId != null);
* }
* else if (vm.Param.PosId == 2)
* {
* risks = risks.Where(p => p.DeptId != null);
* }
* else if (vm.Param.PosId == 3)
* {
* risks = risks.Where(p => p.BranchId != null);
* if (vm.Param.BranchId != null)
* risks = risks.Where(p => p.BranchId == vm.Param.BranchId);
* }*/
if (vm.Param.PosId == 1)
{
risks = risks.Where(p => p.DeptId != null || p.BranchId != null);
}
else if (vm.Param.PosId == 2)
{
risks = risks.Where(p => p.DeptId != null && p.SubDivId == vm.Param.BranchId);
}
else if (vm.Param.PosId == 3)
{
risks = risks.Where(p => p.BranchId != null);
if (vm.Param.BranchId != null)
{
risks = risks.Where(p => p.BranchId == vm.Param.BranchId);
}
}
else if (vm.Param.PosId == 4)
{
risks = risks.Where(p => p.BranchId != null);
if (vm.Param.BranchId != null)
{
risks = risks.Where(p => p.Branch.KorwilId == vm.Param.BranchId);
}
}
else if (vm.Param.PosId == 5)
{
risks = risks.Where(p => p.BranchId != null);
if (vm.Param.BranchId != null)
{
risks = risks.Where(p => p.Branch.ClassId == vm.Param.BranchId);
}
}
if (vm.Param.IsApproved)
{
risks = risks.Where(p => p.CloseDate == null || p.CloseDate > vm.Param.ReportDate && p.CloseDate < vm.Param.ReportDate2);
risks = risks.Where(p => p.ApprovalDate != null && p.ApprovalDate <= vm.Param.ReportDate2 && p.ApprovalDate >= vm.Param.ReportDate);
}
else
{
risks = risks.Where(p => p.ApprovalDate == null && p.RiskDate <= vm.Param.ReportDate2 && p.RiskDate >= vm.Param.ReportDate);
}
foreach (var r in risks)
{
if (vm.Param.IsApproved)
{
var rm = db.RiskMitigations.Where(p => p.RiskId == r.RiskId && p.ApprovalDate != null && p.ApprovalDate <= vm.Param.ReportDate2 && p.ApprovalDate >= vm.Param.ReportDate);
if (rm.Count() > 0)
{
var m = rm.OrderByDescending(p => p.ApprovalDate).First();
r.ProbLevelId = (int)m.ProbLevelId;
r.ImpactLevelId = (int)m.ImpactLevelId;
r.RiskLevel = (int)m.RiskLevel;
}
}
vm.RiskList.Add(r);
}
}
