public JsonResult GetKPI(string ID)
{
KpiManager km = KpiManager.GetInstance();
List <KPI> kpis = km.Select(ID);
return(Json(kpis));
}
public ActionResult ExportProgress(string IDEA_ID)
{
ProjectManager pm = ProjectManager.GetInstance();
PlanManager plan = PlanManager.GetInstance();
KpiManager km = KpiManager.GetInstance();
EmployeeManager em = EmployeeManager.GetInstance();
List <KPI> KPIs = km.Select(IDEA_ID);
List <PROJECT_PLAN> Plans = plan.Select(IDEA_ID);
PROJECT Project = pm.Select(IDEA_ID).FirstOrDefault();
EMPLOYEE EMP = em.Select(Project.EMP_ID).FirstOrDefault();
var fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
var template = Server.MapPath("~/Template/Progress.xlsx");
using (ExcelPackage package = new ExcelPackage(new FileInfo(template)))
{
ExcelWorksheet ws = package.Workbook.Worksheets.FirstOrDefault();
ws.Cells["B9:N18"].Value = "";
ws.Cells["B1"].Value = EMP.DIVISION.Trim();
ws.Cells["G1"].Value = EMP.DEPARTMENT.Trim();
ws.Cells["L1"].Value = EMP.EMP_NAME.Trim();
ws.Cells["B2"].Value = Project.IDEA_TITLE.Trim();
ws.Cells["L2"].Value = Project.PRJECT_GRADE;
ws.Cells["B19"].Value = Project.REMARK == null ? Project.REMARK : Project.REMARK.Trim();
ws.Cells["D3"].Value = Project.KPI_NAME == null ? Project.KPI_NAME : Project.KPI_NAME.Trim();
ws.Cells["L3"].Value = Project.KPI_UNIT == null ? Project.KPI_UNIT : Project.KPI_UNIT.Trim();
ws.Cells["C4"].Value = Project.PRJ_CURR;
ws.Cells["C6"].Value = Project.PRJ_CURR;
ws.Cells["C7"].Value = Project.CURR_VALUE;
var year1 = KPIs[0].PRJ_MONTH.Substring(0, 4);
var year2 = KPIs[KPIs.Count - 1].PRJ_MONTH.Substring(0, 4);
if (KPIs[0].PRJ_MONTH.Substring(0, 4) != KPIs[KPIs.Count - 1].PRJ_MONTH.Substring(0, 4))
{
var c = 'D';
foreach (var item in KPIs)
{
if (year1 == item.PRJ_MONTH.Substring(0, 4))
{
ws.Cells["D4:" + c + "4"].Merge = true;
c++;
}
}
ws.Cells["D4"].Value = year1;
ws.Cells["D4:" + c + "4"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
c++;
ws.Cells[c + "4:O4"].Merge = true;
ws.Cells[c + "4"].Value = year2;
ws.Cells[c + "4:O4"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
else
{
ws.Cells["D4:O4"].Merge = true;
ws.Cells["D4:O4"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells["D4"].Value = year1;
}
var firstMonth = new DateTime(Convert.ToInt32(year1), 1, 1);
//var firstMonth = new DateTime(Convert.ToInt32(year1), Convert.ToInt32(KPIs[0].PRJ_MONTH.Substring(5, 2)), 1);
for (var i = 'D'; i <= 'O'; i++)
{
ws.Cells[i + 5.ToString()].Value = firstMonth.Month;
foreach (var kpi in KPIs)
{
if (kpi.PRJ_MONTH.Equals(firstMonth.ToString("yyyy-MM")))
{
ws.Cells[i + 6.ToString()].Value = kpi.TARGET_VALUE;
ws.Cells[i + 7.ToString()].Value = kpi.RESULT_VALUE;
}
}
firstMonth = firstMonth.AddMonths(1);
}
var seq = 9;
foreach (var item in Plans)
{
var complete = item.COMPLETE_YN == 0 ? "NO" : "YES";
ws.Cells["B" + seq.ToString()].Value = complete;
ws.Cells["C" + seq.ToString()].Value = item.COMPLETE_DATE == DateTime.MinValue ? "" : item.COMPLETE_DATE.ToString("yyyy-MM-dd");
ws.Cells["E" + seq.ToString()].Value = item.PLAN_CONTENTS.Trim();
ws.Cells["N" + seq.ToString()].Value = item.PLAN_DATE == DateTime.MinValue ? "" : item.PLAN_DATE.ToString("yyyy-MM-dd");
seq++;
}
//buffer = package.Stream as MemoryStream;
Byte[] fileBytes = package.GetAsByteArray();
Response.ClearContent();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=" + fileName);
Response.BinaryWrite(fileBytes);
Response.Flush();
Response.End();
}
return(RedirectToAction("Index"));
}