public JsonResult GetKPI(string ID)
{
KpiManager km = KpiManager.GetInstance();
List <KPI> kpis = km.Select(ID);
return(Json(kpis));
}
public JsonResult RegPrj(PROJECT Project, List <PROJECT_PLAN> Plans, List <KPI> KPIs)
{
int result = 0;
PlanManager plan = PlanManager.GetInstance();
ProjectManager prj = ProjectManager.GetInstance();
KpiManager km = KpiManager.GetInstance();
EmployeeManager em = EmployeeManager.GetInstance();
IdeaManager idea = IdeaManager.GetInstance();
string existID = prj.IsExist(Project.IDEA_ID);
if (string.IsNullOrEmpty(existID))
{
// generate id
if (string.IsNullOrWhiteSpace(Project.IDEA_ID))
{
Project.IDEA_ID = idea.GenerateId();
idea.Insert(Project.EMP_ID, Project.IDEA_TITLE, Project.BACKGROUND, "", "");
}
result = prj.InsertProject(Project);
if (result > 0)
{
foreach (var p in Plans)
{
plan.InsertPlan(Project.IDEA_ID, p.PLAN_CONTENTS, p.PLAN_DATE);
}
foreach (var kpi in KPIs)
{
km.InsertKPI(Project.IDEA_ID, kpi.PRJ_MONTH, kpi.TARGET_VALUE);
}
try
{
var emails = em.GetListEmail();
GMailer.GmailUsername = "******";
GMailer.GmailPassword = "******";
GMailer mailer = new GMailer();
mailer.Subject = "[Project Management - New project uploaded]";
mailer.Body = " Employee " + Project.EMP_ID + " Has uploaded a project: " + Project.IDEA_TITLE + ".<br> Please check it out at<br> <a href='http://172.25.215.17/idea'>Project Management</a>";
mailer.IsHtml = true;
foreach (var email in emails)
{
mailer.ToEmail = email.EMAIL.Trim();
mailer.Send();
}
}
catch
{
return(Json(result));
}
}
}
else
{
}
return(Json(result));
}
public JsonResult UpdateProject(PROJECT Project, List <PROJECT_PLAN> Plans, List <KPI> KPIs)
{
int result = 0;
PlanManager plan = PlanManager.GetInstance();
ProjectManager prj = ProjectManager.GetInstance();
KpiManager km = KpiManager.GetInstance();
string existID = prj.IsExist(Project.IDEA_ID);
if (!string.IsNullOrEmpty(existID))
{
result = prj.UpdatePrj(Project.IDEA_ID, Project.EMP_ID, Project.IDEA_TITLE, Project.KPI_NAME, Project.KPI_UNIT,
Project.REMARK, Project.NAME, Project.PRJ_CURR, Project.CURR_VALUE, Project.PRJECT_GRADE, Project.ATTACHMENT,
Project.ISSUE, Project.REQUEST);
if (result > 0)
{
if (Plans != null)
{
foreach (var p in Plans)
{
if (string.IsNullOrWhiteSpace(p.ID))
{
plan.InsertPlan(p.IDEA_ID, p.PLAN_CONTENTS, p.PLAN_DATE, p.COMPLETE_YN, p.COMPLETE_DATE);
}
else
{
plan.Update(p.IDEA_ID, p.PLAN_CONTENTS, p.PLAN_DATE, p.COMPLETE_YN, p.COMPLETE_DATE, p.ID);
}
}
}
if (KPIs != null)
{
foreach (var kpi in KPIs)
{
if (!string.IsNullOrWhiteSpace(kpi.ID))
{
km.Update(kpi.IDEA_ID, kpi.PRJ_MONTH, kpi.TARGET_VALUE, kpi.RESULT_VALUE, kpi.ID);
}
else
{
km.InsertKPI(kpi.IDEA_ID, kpi.PRJ_MONTH, kpi.TARGET_VALUE, kpi.RESULT_VALUE);
}
}
}
}
}
return(Json(result));
}
public JsonResult DeleteProject(string ID)
{
if (string.IsNullOrWhiteSpace(ID))
{
return(Json(-1));
}
var result = 0;
ProjectManager pm = ProjectManager.GetInstance();
PlanManager plan = PlanManager.GetInstance();
KpiManager kpi = KpiManager.GetInstance();
plan.DeletePlanByIdeaId(ID);
kpi.DeleteKPIByIdeaId(ID);
pm.DeleteLikeReply(ID);
result = pm.Delete(ID);
return(Json(result));
}
public ActionResult Index(string div = "", string dept = "", string grade = "", int page = 1)
{
ProjectManager prj = ProjectManager.GetInstance();
KpiManager km = KpiManager.GetInstance();
EmployeeManager employeeManager = EmployeeManager.GetInstance();
ViewBag.Divisions = employeeManager.GetDivision();
ViewBag.Departments = employeeManager.GetDepartment();
//var from1 = DateTime.Now.AddMonths(-2);
//var to1 = from1.AddMonths(11);
//var colspan1 = 0;
//var colspan2 = 0;
//for (DateTime d = from1; d <= to1; d = d.AddMonths(1))
//{
// if (d.Year == from1.Year)
// colspan1++;
// if (d.Year == to1.Year)
// colspan2++;
//}
//if (from1.Year == to1.Year)
//{
// colspan1 = 12;
// colspan2 = 0;
//}
var projects = prj.GetProject(div, dept, grade, page);
var total = prj.GetSearchCount(div, dept, grade);
Dictionary <string, object> kpis = new Dictionary <string, object>();
var date1 = new DateTime(DateTime.Now.Year, 1, 1);
var date2 = new DateTime(DateTime.Now.Year, 12, 1);
foreach (var item in projects)
{
var kpi = km.GetResultKpi(item.IDEA_ID);
List <KPI> lst = new List <KPI>();
for (var d = date1; d <= date2; d = d.AddMonths(1))
{
var count = 0;
foreach (var result in kpi)
{
var k1 = new KPI();
if (d.ToString("yyyy-MM").Equals(result.PRJ_MONTH, StringComparison.CurrentCultureIgnoreCase))
{
count++;
k1.PRJ_MONTH = result.PRJ_MONTH;
k1.RESULT_VALUE = result.RESULT_VALUE;
k1.TARGET_VALUE = result.TARGET_VALUE;
lst.Add(k1);
}
}
if (count == 0)
{
var k1 = new KPI();
k1.PRJ_MONTH = "";
k1.RESULT_VALUE = 0;
k1.TARGET_VALUE = 0;
lst.Add(k1);
}
}
kpis.Add(item.IDEA_ID, lst);
}
ViewBag.Colspan1 = 12;
ViewBag.Colspan2 = 0;
ViewBag.PRJ = projects;
ViewBag.KPI = kpis;
ViewBag.Total = total;
return(View());
}
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"));
}