//派單主畫面-該執行人異常案件 public IQueryable<AssignPersonCaseViewModel> ReadPersonAbnormalCase(String uid) { List<AssignPersonCaseViewModel> ret = new List<AssignPersonCaseViewModel>(); List<AlertDetailSettingViewModel> alertMList = new List<AlertDetailSettingViewModel>(); var Aqry = db.AlertDetailSetting; foreach (AlertDetailSetting o in Aqry) { AlertDetailSettingViewModel a = new AlertDetailSettingViewModel(); a.EventClass = o.EventClass; a.AlertEvent = o.AlertEvent; a.FirstTime = o.FirstTime; alertMList.Add(a); } var qryR = db.ResponsibleClass.Where(x => x.UserID == uid); foreach (ResponsibleClass r in qryR) { //for (int i = 0; i < 2; i++) { var qryC = db.WBSEventC.Where(x => x.ResponsibleID == r.ID && x.EventStatus < 50); //if (i == 0) //已到達逾時未離開 // qryC = qryC.Where(x => x.EventStatus == 41 && x.ScheduleEndDT.Value.CompareTo(DateTime.Now) < 0); //else if (i == 1) //已排程逾時未到達 // qryC = qryC.Where(x => x.EventStatus == 31 && x.ScheduleStartDT.Value.CompareTo(DateTime.Now) < 0); foreach (WBSEventC c in qryC) { AssignPersonCaseViewModel v = new AssignPersonCaseViewModel(); v.AbnormalReason = ""; int temp = 0; if (c.EventStatus == 31) { if (alertMList.Exists(x => x.EventClass == c.EventClass)) temp = alertMList.Find(x => x.EventClass == c.EventClass && x.AlertEvent == 3).FirstTime; else temp = alertMList.Find(x => x.EventClass == null && x.AlertEvent == 3).FirstTime; //if (c.CreateDateTime.Value.AddMinutes(temp).CompareTo(DateTime.Now) < 0) if (c.ScheduleStartDT.Value.AddMinutes(temp).CompareTo(DateTime.Now) < 0) { //排程後超過"預計到達時間"N分未到達 v.AbnormalReason = "逾時未到達"; v.OverTimeColor = "Red"; v.OverTimeDisplay = ""; } } else if (c.EventStatus == 41) { if (alertMList.Exists(x => x.EventClass == c.EventClass)) temp = alertMList.Find(x => x.EventClass == c.EventClass && x.AlertEvent == 4).FirstTime; else temp = alertMList.Find(x => x.EventClass == null && x.AlertEvent == 4).FirstTime; if (c.ServiceArriveDT.Value.AddMinutes(temp).CompareTo(DateTime.Now) < 0) { //排程後超過"實際到達時間"N分未離開 v.AbnormalReason = "逾時未離開"; v.OverTimeColor = "Red"; v.OverTimeDisplay = ""; } } if (v.AbnormalReason == "") continue; //v.OverTime = ""; //v.OverTimeDisplay = "none"; ////if (i == 0) { //// v.AbnormalReason = "逾時未離開"; //// v.OverTimeColor = "Red"; //// v.OverTimeDisplay = ""; ////} ////else if (i == 1) { //// v.AbnormalReason = "逾時未到達"; //// v.OverTimeColor = "Red"; //// v.OverTimeDisplay = ""; ////} v.MCaseID = c.MomCaseID; var qryM = db.WBSEventM.Where(x => x.CaseID == c.MomCaseID); foreach (WBSEventM m in qryM) { v.CaseNumM = m.CaseClass + m.CaseDate + "-"; if (m.CaseCode < 10) v.CaseNumM += "0000" + m.CaseCode.ToString(); else if (m.CaseCode < 100 && m.CaseCode >= 10) v.CaseNumM += "000" + m.CaseCode.ToString(); else if (m.CaseCode < 1000 && m.CaseCode >= 100) v.CaseNumM += "00" + m.CaseCode.ToString(); else if (m.CaseCode < 10000 && m.CaseCode >= 1000) v.CaseNumM += "0" + m.CaseCode.ToString(); else v.CaseNumM += m.CaseCode.ToString(); v.CustomerPhone1 = m.CustomerUserPhone1; v.CustomerPhone = m.CustomerUserPhone1; if (m.CustomerUserPhone2 != null && m.CustomerUserPhone2 != "") v.CustomerPhone += " /" + m.CustomerUserPhone2; v.CustomerRef = m.CustomerRef; v.CustomerSName = m.Customer1.ShortName; //db.Customer.AsEnumerable().Where(x => x.CustomerRef == m.CustomerRef).Select(x => x.ShortName).First(); v.CustomerUserName = m.CustomerUserName; v.CustomerCaseNum = m.CustomerCaseNum; } v.CCaseNum = c.Num; v.CaseNumC = (c.ChildCaseID < 10) ? ("0" + c.ChildCaseID.ToString()) : c.ChildCaseID.ToString(); v.ResponsibleUserName = c.ResponsibleClass.Name + "-" + c.ResponsibleClass.UserData2.UserName; v.DefaultUserName = r.Name + "-" + db.UserData.AsEnumerable().Where(x => x.UserID == r.UserID).Select(x => x.UserName).First(); v.AssignDT = c.AssignDT; v.AssignDTStr = (c.AssignDT ?? DateTime.Now).ToString("MM/dd HH:mm"); v.AssignUserName = db.UserData.AsEnumerable().Where(x => x.UserID == c.AssignCreateUserID).Select(x => x.UserName).First(); v.CreateDT = c.CreateDateTime; v.CreateDTStr = (c.CreateDateTime ?? DateTime.Now).ToString("MM/dd HH:mm"); v.CreateUserID = c.CreateUserID; v.CreateUserName = db.UserData.AsEnumerable().Where(x => x.UserID == c.CreateUserID).Select(x => x.UserName).First(); v.ExpectArriveDT = c.ExpectArriveDateTime; v.ExpectArriveDTStr = (c.ExpectArriveDateTime ?? DateTime.Now).ToString("MM/dd HH:mm"); if (c.ExpectArriveSW == "0") { v.ExpectArriveSW = "自行約定"; v.ExpectArriveDTStr = "自行約定"; } else if (c.ExpectArriveSW == "1") v.ExpectArriveSW = "合約規定"; else if (c.ExpectArriveSW == "2") v.ExpectArriveSW = "建議期望"; else if (c.ExpectArriveSW == "4") v.ExpectArriveSW = "自派新單"; else v.ExpectArriveSW = "客戶指定"; //尚未處理 v.ProductNote = c.ProductClass1.Note; //db.ProductClass.AsEnumerable().Where(x => x.ProductClassID == c.ProductClass).Select(x => x.Note).First(); v.ProductClass = c.ProductClass; v.ItemClass = c.ItemClass; v.ItemNote = c.ItemClass1.Note; //db.ItemClass.AsEnumerable().Where(x => x.ItemClassID == c.ItemClass).Select(x => x.Note).First(); v.QuestionClass = c.QuestionClass; v.QuestionNote = c.QuestionClass1.Note; //db.QuestionClass.AsEnumerable().Where(x => x.QuestionClassID == c.QuestionClass).Select(x => x.Note).First(); v.QuestionDesc = c.QuestionDescription; v.RespondDT = c.RespondDT; v.RespondReason = c.RespondReason; v.RespondSW = c.RespondSW; v.ScheduleEndDT = c.ScheduleEndDT; v.ScheduleEndDTStr = (c.ScheduleEndDT ?? DateTime.Now).ToString("MM/dd HH:mm"); v.ScheduleStartDT = c.ScheduleStartDT; v.ScheduleStartDTStr = (c.ScheduleStartDT ?? DateTime.Now).ToString("MM/dd HH:mm"); v.ServiceHours = c.ScheduleServiceHours ?? 0; //問題附件 v.HasActtachment = ""; if (db.Files.Where(x => x.CID == c.Num && x.Class == 1).Count() > 0) v.HasActtachment = "@"; //預先保養 v.MaintainNum = ""; int maintainNum = db.EventRelation.Where(x => x.CID == c.Num).Count(); if (maintainNum != 0) v.MaintainNum = maintainNum.ToString(); //解決附件 v.HasAssignActtachment = ""; if (db.Files.Where(x => x.CID == c.Num && x.Class == 2).Count() > 0) v.HasAssignActtachment = "@"; //知識庫附件 v.HasKnowledge = ""; if (db.Files.Where(x => x.CID == c.Num && x.Class == 3).Count() > 0) v.HasKnowledge = "@"; //解決路徑 v.HasHyperlink = ""; if (db.Files.Where(x => x.CID == c.Num && x.Class == 9).Count() > 0) v.HasHyperlink = "@"; v.EventStatusStr = (c.EventStatus == 0) ? "0" + c.EventStatus.ToString() : c.EventStatus.ToString(); v.EventStatusStr += "." + GetEventStatusStr(c.EventStatus); v.ServiceArriveDT = c.ServiceArriveDT; v.ServiceFinishDT = c.ServiceLeaveDT; v.EventClassStr = c.EventClass + "-" + db.EventClass.Where(x => x.Class == c.EventClass).Select(x => x.Name).First(); String RegionID = c.WBSEventM.Customer1.RegionID; //db.Customer.AsEnumerable().Where(x2 => x2.CustomerRef == m.CustomerRef).Select(x2 => x2.RegionID).First(); String RegionID2 = ""; int RID2 = db.SubArea.AsEnumerable().Where(x2 => x2.SubAreaID == RegionID).Select(x2 => x2.ResponsibleID2).First() ?? 0; if (RID2 != 0) { RegionID2 = db.ResponsibleClass.AsEnumerable().Where(x2 => x2.ID == RID2).Select(x2 => x2.UserID).First(); v.DefaultUserName2 = db.ResponsibleClass.AsEnumerable().Where(x2 => x2.ID == RID2).Select(x2 => x2.Name).First() + "-" + db.UserData.AsEnumerable().Where(x => x.UserID == RegionID2).Select(x => x.UserName).First(); } ret.Add(v); } //} } return ret.AsQueryable().OrderByDescending(x => x.CreateDT); }
//派單主畫面-該執行人異常案件 public IQueryable<AssignPersonCaseViewModel> ReadPersonAbnormalCase(string uid) { List<AssignPersonCaseViewModel> ret = new List<AssignPersonCaseViewModel>(); var qryR = db.ResponsibleClass.Where(x => x.UserID == uid); foreach (ResponsibleClass r in qryR) { for (int i = 0; i < 2; i++) { var qryC = db.WBSEventC.Where(x => x.ResponsibleID == r.ID); if (i == 0) //已到達逾時未離開 qryC = qryC.Where(x => x.EventStatus == 41 && x.ScheduleEndDT.Value.CompareTo(DateTime.Now) < 0); else if (i == 1) //已排程逾時未到達 qryC = qryC.Where(x => x.EventStatus == 31 && x.ScheduleStartDT.Value.CompareTo(DateTime.Now) < 0); foreach (WBSEventC c in qryC) { AssignPersonCaseViewModel v = new AssignPersonCaseViewModel(); //v.OverTime = ""; //v.OverTimeDisplay = "none"; if (i == 0) { v.AbnormalReason = "逾時未離開"; v.OverTimeColor = "Red"; v.OverTimeDisplay = ""; } else if (i == 1) { v.AbnormalReason = "逾時未到達"; v.OverTimeColor = "Red"; v.OverTimeDisplay = ""; } v.MCaseID = c.MomCaseID; var qryM = db.WBSEventM.Where(x => x.CaseID == c.MomCaseID); foreach (WBSEventM m in qryM) { v.CaseNumM = m.CaseClass + m.CaseDate + "-"; if (m.CaseCode < 10) v.CaseNumM += "0000" + m.CaseCode.ToString(); else if (m.CaseCode < 100 && m.CaseCode >= 10) v.CaseNumM += "000" + m.CaseCode.ToString(); else if (m.CaseCode < 1000 && m.CaseCode >= 100) v.CaseNumM += "00" + m.CaseCode.ToString(); else if (m.CaseCode < 10000 && m.CaseCode >= 1000) v.CaseNumM += "0" + m.CaseCode.ToString(); else v.CaseNumM += m.CaseCode.ToString(); v.CustomerPhone1 = m.CustomerUserPhone1; if (m.CustomerUserPhone2 != null) v.CustomerPhone1 += ";" + m.CustomerUserPhone2; v.CustomerRef = m.CustomerRef; v.CustomerSName = m.Customer1.ShortName; //db.Customer.AsEnumerable().Where(x => x.CustomerRef == m.CustomerRef).Select(x => x.ShortName).First(); v.CustomerUserName = m.CustomerUserName; } v.CCaseNum = c.Num; v.CaseNumC = (c.ChildCaseID < 10) ? ("0" + c.ChildCaseID.ToString()) : c.ChildCaseID.ToString(); v.ResponsibleUserName = db.UserData.AsEnumerable().Where(x => x.UserID == uid).Select(x => x.UserName).First(); v.DefaultUserName = r.Name + "-" + db.UserData.AsEnumerable().Where(x => x.UserID == r.UserID).Select(x => x.UserName).First(); v.AssignDT = c.AssignDT; v.AssignDTStr = (c.AssignDT ?? DateTime.Now).ToString("yyyy/MM/dd HH:mm"); v.AssignUserName = db.UserData.AsEnumerable().Where(x => x.UserID == c.AssignCreateUserID).Select(x => x.UserName).First(); v.CreateDT = c.CreateDateTime; v.CreateDTStr = (c.CreateDateTime ?? DateTime.Now).ToString("yyyy/MM/dd HH:mm"); v.CreateUserID = c.CreateUserID; v.CreateUserName = db.UserData.AsEnumerable().Where(x => x.UserID == c.CreateUserID).Select(x => x.UserName).First(); v.ExpectArriveDT = c.ExpectArriveDateTime; v.ExpectArriveDTStr = (c.ExpectArriveDateTime ?? DateTime.Now).ToString("yyyy/MM/dd HH:mm"); if (c.ExpectArriveSW == "0") { v.ExpectArriveSW = "自行約定"; v.ExpectArriveDTStr = "自行約定"; } else if (c.ExpectArriveSW == "1") v.ExpectArriveSW = "合約規定"; else if (c.ExpectArriveSW == "2") v.ExpectArriveSW = "建議期望"; else v.ExpectArriveSW = "客戶指定"; //尚未處理 v.ProductNote = c.ProductClass1.Note; //db.ProductClass.AsEnumerable().Where(x => x.ProductClassID == c.ProductClass).Select(x => x.Note).First(); v.ProductClass = c.ProductClass; v.ItemClass = c.ItemClass; v.ItemNote = c.ItemClass1.Note; //db.ItemClass.AsEnumerable().Where(x => x.ItemClassID == c.ItemClass).Select(x => x.Note).First(); v.QuestionClass = c.QuestionClass; v.QuestionNote = c.QuestionClass1.Note; //db.QuestionClass.AsEnumerable().Where(x => x.QuestionClassID == c.QuestionClass).Select(x => x.Note).First(); v.QuestionDesc = c.QuestionDescription; v.RespondDT = c.RespondDT; v.RespondReason = c.RespondReason; v.RespondSW = c.RespondSW; v.ScheduleEndDT = c.ScheduleEndDT; v.ScheduleEndDTStr = (c.ScheduleEndDT ?? DateTime.Now).ToString("yyyy/MM/dd HH:mm"); v.ScheduleStartDT = c.ScheduleStartDT; v.ScheduleStartDTStr = (c.ScheduleStartDT ?? DateTime.Now).ToString("yyyy/MM/dd HH:mm"); v.ServiceHours = c.ScheduleServiceHours ?? 0; //問題附件 v.HasActtachment = ""; if (db.Files.Where(x => x.CID == c.Num && x.Class == 1).Count() > 0) v.HasActtachment = "@"; //預先保養 v.MaintainNum = ""; int maintainNum = db.EventRelation.Where(x => x.CID == c.Num).Count(); if (maintainNum != 0) v.MaintainNum = maintainNum.ToString(); //解決方式附件 v.HasAssignActtachment = ""; if (db.Files.Where(x => x.CID == c.Num && x.Class == 2).Count() > 0) v.HasAssignActtachment = "@"; //解決方式路徑 v.HasKnowledge = ""; if (db.Files.Where(x => x.CID == c.Num && x.Class == 3).Count() > 0) v.HasKnowledge = "@"; ret.Add(v); } } /* //逾時已到達未離開 var qryC = db.WBSEventC.Where(x => x.ResponsibleID == r.ID && x.EventStatus == 41 && x.ScheduleEndDT.Value.CompareTo(DateTime.Now) < 0); foreach (WBSEventC c in qryC) { AssignPersonCaseViewModel v = new AssignPersonCaseViewModel(); v.AbnormalReason = "逾時未離開"; v.MCaseID = c.MomCaseID; var qryM = db.WBSEventM.Where(x => x.CaseID == c.MomCaseID); foreach (WBSEventM m in qryM) { v.CaseNumM = m.CaseClass + m.CaseDate + "-"; if (m.CaseCode < 10) v.CaseNumM += "0000" + m.CaseCode.ToString(); else if (m.CaseCode < 100 && m.CaseCode >= 10) v.CaseNumM += "000" + m.CaseCode.ToString(); else if (m.CaseCode < 1000 && m.CaseCode >= 100) v.CaseNumM += "00" + m.CaseCode.ToString(); else if (m.CaseCode < 10000 && m.CaseCode >= 1000) v.CaseNumM += "0" + m.CaseCode.ToString(); else v.CaseNumM += m.CaseCode.ToString(); v.CustomerPhone1 = m.CustomerUserPhone1; if (m.CustomerUserPhone2 != null) v.CustomerPhone1 += ";" + m.CustomerUserPhone2; v.CustomerRef = m.CustomerRef; v.CustomerSName = m.Customer1.ShortName; //db.Customer.AsEnumerable().Where(x => x.CustomerRef == m.CustomerRef).Select(x => x.ShortName).First(); v.CustomerUserName = m.CustomerUserName; } v.CCaseNum = c.Num; v.CaseNumC = (c.ChildCaseID < 10) ? ("0" + c.ChildCaseID.ToString()) : c.ChildCaseID.ToString(); v.ResponsibleUserName = db.UserData.AsEnumerable().Where(x => x.UserID == uid).Select(x => x.UserName).First(); v.DefaultUserName = r.Name + "-" + db.UserData.AsEnumerable().Where(x => x.UserID == r.UserID).Select(x => x.UserName).First(); v.AssignDT = c.AssignDT; v.AssignDTStr = (c.AssignDT ?? DateTime.Now).ToString("yyyy/MM/dd HH:mm"); v.AssignUserName = db.UserData.AsEnumerable().Where(x => x.UserID == c.AssignCreateUserID).Select(x => x.UserName).First(); v.CreateDT = c.CreateDateTime; v.CreateDTStr = (c.CreateDateTime ?? DateTime.Now).ToString("yyyy/MM/dd HH:mm"); v.CreateUserID = c.CreateUserID; v.CreateUserName = db.UserData.AsEnumerable().Where(x => x.UserID == c.CreateUserID).Select(x => x.UserName).First(); v.ExpectArriveDT = c.ExpectArriveDateTime; v.ExpectArriveDTStr = (c.ExpectArriveDateTime ?? DateTime.Now).ToString("yyyy/MM/dd HH:mm"); if (c.ExpectArriveSW == "0") { v.ExpectArriveSW = "自行約定"; v.ExpectArriveDTStr = "自行約定"; } else if (c.ExpectArriveSW == "1") v.ExpectArriveSW = "合約規定"; else if (c.ExpectArriveSW == "2") v.ExpectArriveSW = "建議期望"; else v.ExpectArriveSW = "客戶指定"; //尚未處理 v.ProductNote = c.ProductClass1.Note; //db.ProductClass.AsEnumerable().Where(x => x.ProductClassID == c.ProductClass).Select(x => x.Note).First(); v.ProductClass = c.ProductClass; v.ItemClass = c.ItemClass; v.ItemNote = c.ItemClass1.Note; //db.ItemClass.AsEnumerable().Where(x => x.ItemClassID == c.ItemClass).Select(x => x.Note).First(); v.QuestionClass = c.QuestionClass; v.QuestionNote = c.QuestionClass1.Note; //db.QuestionClass.AsEnumerable().Where(x => x.QuestionClassID == c.QuestionClass).Select(x => x.Note).First(); v.QuestionDesc = c.QuestionDescription; v.RespondDT = c.RespondDT; v.RespondReason = c.RespondReason; v.RespondSW = c.RespondSW; v.ScheduleEndDT = c.ScheduleEndDT; v.ScheduleEndDTStr = (c.ScheduleEndDT ?? DateTime.Now).ToString("yyyy/MM/dd HH:mm"); v.ScheduleStartDT = c.ScheduleStartDT; v.ScheduleStartDTStr = (c.ScheduleStartDT ?? DateTime.Now).ToString("yyyy/MM/dd HH:mm"); v.ServiceHours = c.ScheduleServiceHours ?? 0; //問題附件 v.HasActtachment = ""; if (db.Files.Where(x => x.CID == c.Num && x.Class == 1).Count() > 0) v.HasActtachment = "@"; //預先保養 v.MaintainNum = ""; int maintainNum = db.EventRelation.Where(x => x.CID == c.Num).Count(); if (maintainNum != 0) v.MaintainNum = maintainNum.ToString(); //解決方式附件 v.HasAssignActtachment = ""; if (db.Files.Where(x => x.CID == c.Num && x.Class == 2).Count() > 0) v.HasAssignActtachment = "@"; //解決方式路徑 v.HasKnowledge = ""; if (db.Files.Where(x => x.CID == c.Num && x.Class == 3).Count() > 0) v.HasKnowledge = "@"; ret.Add(v); } //已排程逾時未到達 qryC = db.WBSEventC.Where(x => x.ResponsibleID == r.ID && x.EventStatus == 31 && x.ScheduleStartDT.Value.CompareTo(DateTime.Now) < 0); foreach (WBSEventC c in qryC) { AssignPersonCaseViewModel v = new AssignPersonCaseViewModel(); v.AbnormalReason = "逾時未到達"; v.MCaseID = c.MomCaseID; var qryM = db.WBSEventM.Where(x => x.CaseID == c.MomCaseID); foreach (WBSEventM m in qryM) { v.CaseNumM = m.CaseClass + m.CaseDate + "-"; if (m.CaseCode < 10) v.CaseNumM += "0000" + m.CaseCode.ToString(); else if (m.CaseCode < 100 && m.CaseCode >= 10) v.CaseNumM += "000" + m.CaseCode.ToString(); else if (m.CaseCode < 1000 && m.CaseCode >= 100) v.CaseNumM += "00" + m.CaseCode.ToString(); else if (m.CaseCode < 10000 && m.CaseCode >= 1000) v.CaseNumM += "0" + m.CaseCode.ToString(); else v.CaseNumM += m.CaseCode.ToString(); v.CustomerPhone1 = m.CustomerUserPhone1; if (m.CustomerUserPhone2 != null) v.CustomerPhone1 += ";" + m.CustomerUserPhone2; v.CustomerRef = m.CustomerRef; v.CustomerSName = m.Customer1.ShortName; //db.Customer.AsEnumerable().Where(x => x.CustomerRef == m.CustomerRef).Select(x => x.ShortName).First(); v.CustomerUserName = m.CustomerUserName; } v.CCaseNum = c.Num; v.CaseNumC = (c.ChildCaseID < 10) ? ("0" + c.ChildCaseID.ToString()) : c.ChildCaseID.ToString(); v.ResponsibleUserName = db.UserData.AsEnumerable().Where(x => x.UserID == uid).Select(x => x.UserName).First(); v.DefaultUserName = c.WBSEventM.Customer1.SubArea.ResponsibleClass.Name + "-" + db.UserData.AsEnumerable().Where(x => x.UserID == r.UserID).Select(x => x.UserName).First(); v.AssignDT = c.AssignDT; v.AssignDTStr = (c.AssignDT ?? DateTime.Now).ToString("yyyy/MM/dd HH:mm"); v.AssignUserName = db.UserData.AsEnumerable().Where(x => x.UserID == c.AssignCreateUserID).Select(x => x.UserName).First(); v.CreateDT = c.CreateDateTime; v.CreateDTStr = (c.CreateDateTime ?? DateTime.Now).ToString("yyyy/MM/dd HH:mm"); v.CreateUserID = c.CreateUserID; v.CreateUserName = db.UserData.AsEnumerable().Where(x => x.UserID == c.CreateUserID).Select(x => x.UserName).First(); v.ExpectArriveDT = c.ExpectArriveDateTime; v.ExpectArriveDTStr = (c.ExpectArriveDateTime ?? DateTime.Now).ToString("yyyy/MM/dd HH:mm"); if (c.ExpectArriveSW == "0") { v.ExpectArriveSW = "自行約定"; v.ExpectArriveDTStr = "自行約定"; } else if (c.ExpectArriveSW == "1") v.ExpectArriveSW = "合約規定"; else if (c.ExpectArriveSW == "2") v.ExpectArriveSW = "建議期望"; else v.ExpectArriveSW = "客戶指定"; //尚未處理 v.ProductNote = c.ProductClass1.Note; //db.ProductClass.AsEnumerable().Where(x => x.ProductClassID == c.ProductClass).Select(x => x.Note).First(); v.ProductClass = c.ProductClass; v.ItemClass = c.ItemClass; v.ItemNote = c.ItemClass1.Note; //db.ItemClass.AsEnumerable().Where(x => x.ItemClassID == c.ItemClass).Select(x => x.Note).First(); v.QuestionClass = c.QuestionClass; v.QuestionNote = c.QuestionClass1.Note; //db.QuestionClass.AsEnumerable().Where(x => x.QuestionClassID == c.QuestionClass).Select(x => x.Note).First(); v.QuestionDesc = c.QuestionDescription; v.RespondDT = c.RespondDT; v.RespondReason = c.RespondReason; v.RespondSW = c.RespondSW; v.ScheduleEndDT = c.ScheduleEndDT; v.ScheduleEndDTStr = (c.ScheduleEndDT ?? DateTime.Now).ToString("yyyy/MM/dd HH:mm"); v.ScheduleStartDT = c.ScheduleStartDT; v.ScheduleStartDTStr = (c.ScheduleStartDT ?? DateTime.Now).ToString("yyyy/MM/dd HH:mm"); v.ServiceHours = c.ScheduleServiceHours ?? 0; //問題附件 v.HasActtachment = ""; if (db.Files.Where(x => x.CID == c.Num && x.Class == 1).Count() > 0) v.HasActtachment = "@"; //預先保養 v.MaintainNum = ""; int maintainNum = db.EventRelation.Where(x => x.CID == c.Num).Count(); if (maintainNum != 0) v.MaintainNum = maintainNum.ToString(); //解決方式附件 v.HasAssignActtachment = ""; if (db.Files.Where(x => x.CID == c.Num && x.Class == 2).Count() > 0) v.HasAssignActtachment = "@"; //解決方式路徑 v.HasKnowledge = ""; if (db.Files.Where(x => x.CID == c.Num && x.Class == 3).Count() > 0) v.HasKnowledge = "@"; ret.Add(v); } */ } return ret.AsQueryable().OrderBy(p => p.ScheduleStartDTStr); }
//派單主畫面-該執行人派工狀況 public IQueryable<AssignPersonCaseViewModel> ReadPersonCase(String uid, String day) { List<AssignPersonCaseViewModel> ret = new List<AssignPersonCaseViewModel>(); if (day == "") day = "0"; int D = Int32.Parse(day); //DateTime today = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day); var qryR = db.ResponsibleClass.Where(x => x.UserID == uid); foreach(ResponsibleClass r in qryR) { var qryC = db.WBSEventC.Where(x => x.ResponsibleID == r.ID); foreach (WBSEventC c in qryC) { if (c.EventStatus != 30 && c.ScheduleStartDT != null)//20160803 加上&& c.ScheduleStartDT != null { if (c.RespondSW == "Y" && (Convert.ToDateTime(c.ScheduleStartDT.Value).CompareTo(DateTime.Today.AddDays(D)) < 0) && Convert.ToDateTime(c.ScheduleStartDT.Value).CompareTo(DateTime.Today) > 0 && c.EventStatus > 30 && c.EventStatus != 0) { //if (c.RespondSW == "Y" && !(Convert.ToDateTime(c.ScheduleStartDT.Value).CompareTo(DateTime.Today.AddDays(D)) > 0) && c.ServiceLeaveDT == null) AssignPersonCaseViewModel v = new AssignPersonCaseViewModel(); v.MCaseID = c.MomCaseID; var qryM = db.WBSEventM.Where(x => x.CaseID == c.MomCaseID); foreach (WBSEventM m in qryM) { v.CaseNumM = m.CaseClass + m.CaseDate + "-"; if (m.CaseCode < 10) v.CaseNumM += "0000" + m.CaseCode.ToString(); else if (m.CaseCode < 100 && m.CaseCode >= 10) v.CaseNumM += "000" + m.CaseCode.ToString(); else if (m.CaseCode < 1000 && m.CaseCode >= 100) v.CaseNumM += "00" + m.CaseCode.ToString(); else if (m.CaseCode < 10000 && m.CaseCode >= 1000) v.CaseNumM += "0" + m.CaseCode.ToString(); else v.CaseNumM += m.CaseCode.ToString(); v.CustomerPhone1 = m.CustomerUserPhone1; v.CustomerPhone = m.CustomerUserPhone1; if (m.CustomerUserPhone2 != null && m.CustomerUserPhone2 != "") v.CustomerPhone += " /" + m.CustomerUserPhone2; v.CustomerCaseNum = m.CustomerCaseNum; v.CustomerRef = m.CustomerRef; v.CustomerSName = m.Customer1.ShortName; //db.Customer.AsEnumerable().Where(x => x.CustomerRef == m.CustomerRef).Select(x => x.ShortName).First(); v.CustomerUserName = m.CustomerUserName; } v.CCaseNum = c.Num; v.CaseNumC = (c.ChildCaseID < 10) ? ("0" + c.ChildCaseID.ToString()) : c.ChildCaseID.ToString(); v.ResponsibleUserName = c.ResponsibleClass.Name + "-" + c.ResponsibleClass.UserData2.UserName; v.DefaultUserName =r.Name + "-" + db.UserData.AsEnumerable().Where(x => x.UserID == r.UserID).Select(x => x.UserName).First(); v.AssignDT = c.AssignDT; v.AssignDTStr = (c.AssignDT ?? DateTime.Now).ToString("MM/dd HH:mm"); if(c.AssignModifyUserID!=null) v.AssignUserName = db.UserData.AsEnumerable().Where(x => x.UserID == c.AssignModifyUserID).Select(x => x.UserName).First(); else v.AssignUserName = db.UserData.AsEnumerable().Where(x => x.UserID == c.AssignCreateUserID).Select(x => x.UserName).First(); v.CreateDT = c.CreateDateTime; v.CreateDTStr = (c.CreateDateTime ?? DateTime.Now).ToString("MM/dd HH:mm"); v.CreateUserID = c.CreateUserID; v.CreateUserName = db.UserData.AsEnumerable().Where(x => x.UserID == c.CreateUserID).Select(x => x.UserName).First(); v.ExpectArriveDT = c.ExpectArriveDateTime; v.ExpectArriveDTStr = (c.ExpectArriveDateTime ?? DateTime.Now).ToString("MM/dd HH:mm"); if (c.ExpectArriveSW == "0") { v.ExpectArriveSW = "自行約定"; v.ExpectArriveDTStr = "自行約定"; } else if (c.ExpectArriveSW == "1") v.ExpectArriveSW = "合約規定"; else if (c.ExpectArriveSW == "2") v.ExpectArriveSW = "建議期望"; else if (c.ExpectArriveSW == "4") v.ExpectArriveSW = "自派新單"; else v.ExpectArriveSW = "客戶指定"; //v.HasActtachment = ""; //var qryF = db.Files.Where(x => x.CID == c.Num); //foreach (Files f in qryF) //{ // v.HasActtachment = "@"; //} //v.HasAssignActtachment = ""; //尚未處理 v.ProductNote = c.ProductClass1.Note; //db.ProductClass.AsEnumerable().Where(x => x.ProductClassID == c.ProductClass).Select(x => x.Note).First(); v.ProductClass = c.ProductClass; v.ItemClass = c.ItemClass; v.ItemNote = c.ItemClass1.Note; //db.ItemClass.AsEnumerable().Where(x => x.ItemClassID == c.ItemClass).Select(x => x.Note).First(); v.QuestionClass = c.QuestionClass; v.QuestionNote = c.QuestionClass1.Note; //db.QuestionClass.AsEnumerable().Where(x => x.QuestionClassID == c.QuestionClass).Select(x => x.Note).First(); v.QuestionDesc = c.QuestionDescription; v.RespondDT = c.RespondDT; v.RespondReason = c.RespondReason; v.RespondSW = c.RespondSW; v.ScheduleEndDT = c.ScheduleEndDT; v.ScheduleEndDTStr = (c.ScheduleEndDT ?? DateTime.Now).ToString("MM/dd HH:mm"); v.ScheduleStartDT = c.ScheduleStartDT; v.ScheduleStartDTStr = (c.ScheduleStartDT ?? DateTime.Now).ToString("MM/dd HH:mm"); v.ServiceHours = c.ScheduleServiceHours ?? 0; v.ServiceArriveDT = c.ServiceArriveDT; v.ServiceFinishDT = c.ServiceLeaveDT; v.EventClassStr = c.EventClass + "-" + db.EventClass.Where(x => x.Class == c.EventClass).Select(x => x.Name).First(); String RegionID = c.WBSEventM.Customer1.RegionID; //db.Customer.AsEnumerable().Where(x2 => x2.CustomerRef == m.CustomerRef).Select(x2 => x2.RegionID).First(); String RegionID2 = ""; int RID2 = db.SubArea.AsEnumerable().Where(x2 => x2.SubAreaID == RegionID).Select(x2 => x2.ResponsibleID2).First() ?? 0; if (RID2 != 0) { RegionID2 = db.ResponsibleClass.AsEnumerable().Where(x2 => x2.ID == RID2).Select(x2 => x2.UserID).First(); v.DefaultUserName2 = db.ResponsibleClass.AsEnumerable().Where(x2 => x2.ID == RID2).Select(x2 => x2.Name).First() + "-" + db.UserData.AsEnumerable().Where(x => x.UserID == RegionID2).Select(x => x.UserName).First(); } //問題附件 v.HasActtachment = ""; if (db.Files.Where(x => x.CID == c.Num && x.Class == 1).Count() > 0) v.HasActtachment = "@"; //預先保養 v.MaintainNum = ""; int maintainNum = db.EventRelation.Where(x => x.CID == c.Num).Count(); if (maintainNum != 0) v.MaintainNum = maintainNum.ToString(); //解決附件 v.HasAssignActtachment = ""; if (db.Files.Where(x => x.CID == c.Num && x.Class == 2).Count() > 0) v.HasAssignActtachment = "@"; //知識庫附件 v.HasKnowledge = ""; if (db.Files.Where(x => x.CID == c.Num && x.Class == 3).Count() > 0) v.HasKnowledge = "@"; //解決路徑 v.HasHyperlink = ""; if (db.Files.Where(x => x.CID == c.Num && x.Class == 9).Count() > 0) v.HasHyperlink = "@"; v.EventStatusStr = (c.EventStatus == 0) ? "0" + c.EventStatus.ToString() : c.EventStatus.ToString(); v.EventStatusStr += "." + GetEventStatusStr(c.EventStatus); if (c.WBSEventM.CloseDT != null) v.EventStatusStr = "99.已結案"; ret.Add(v); } } } } return ret.AsQueryable().OrderByDescending(x => x.CreateDT); }
//派單主畫面-該執行人派工狀況 public IQueryable<AssignPersonCaseViewModel> ReadPersonCase(string uid, string day) { List<AssignPersonCaseViewModel> ret = new List<AssignPersonCaseViewModel>(); if (day == "") day = "0"; int D = Int32.Parse(day); //DateTime today = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day); var qryR = db.ResponsibleClass.Where(x => x.UserID == uid); foreach(ResponsibleClass r in qryR){ var qryC = db.WBSEventC.Where(x => x.ResponsibleID == r.ID); foreach (WBSEventC c in qryC) { if (c.EventStatus != 30) { if (c.RespondSW == "Y" && (Convert.ToDateTime(c.ScheduleStartDT.Value).CompareTo(DateTime.Today.AddDays(D)) < 0) && Convert.ToDateTime(c.ScheduleStartDT.Value).CompareTo(DateTime.Today) > 0 && c.EventStatus > 30 && c.EventStatus != 0) //if (c.RespondSW == "Y" && !(Convert.ToDateTime(c.ScheduleStartDT.Value).CompareTo(DateTime.Today.AddDays(D)) > 0) && c.ServiceLeaveDT == null) { AssignPersonCaseViewModel v = new AssignPersonCaseViewModel(); v.MCaseID = c.MomCaseID; var qryM = db.WBSEventM.Where(x => x.CaseID == c.MomCaseID); foreach (WBSEventM m in qryM) { v.CaseNumM = m.CaseClass + m.CaseDate + "-"; if (m.CaseCode < 10) v.CaseNumM += "0000" + m.CaseCode.ToString(); else if (m.CaseCode < 100 && m.CaseCode >= 10) v.CaseNumM += "000" + m.CaseCode.ToString(); else if (m.CaseCode < 1000 && m.CaseCode >= 100) v.CaseNumM += "00" + m.CaseCode.ToString(); else if (m.CaseCode < 10000 && m.CaseCode >= 1000) v.CaseNumM += "0" + m.CaseCode.ToString(); else v.CaseNumM += m.CaseCode.ToString(); v.CustomerPhone1 = m.CustomerUserPhone1; if (m.CustomerUserPhone2 != null) v.CustomerPhone1 += ";" + m.CustomerUserPhone2; v.CustomerRef = m.CustomerRef; v.CustomerSName = m.Customer1.ShortName; //db.Customer.AsEnumerable().Where(x => x.CustomerRef == m.CustomerRef).Select(x => x.ShortName).First(); v.CustomerUserName = m.CustomerUserName; } v.CCaseNum = c.Num; v.CaseNumC = (c.ChildCaseID < 10) ? ("0" + c.ChildCaseID.ToString()) : c.ChildCaseID.ToString(); v.ResponsibleUserName = db.UserData.AsEnumerable().Where(x => x.UserID == uid).Select(x => x.UserName).First(); v.DefaultUserName =r.Name + "-" + db.UserData.AsEnumerable().Where(x => x.UserID == r.UserID).Select(x => x.UserName).First(); v.AssignDT = c.AssignDT; v.AssignDTStr = (c.AssignDT ?? DateTime.Now).ToString("yyyy/MM/dd HH:mm"); if(c.AssignModifyUserID!=null) v.AssignUserName = db.UserData.AsEnumerable().Where(x => x.UserID == c.AssignModifyUserID).Select(x => x.UserName).First(); else v.AssignUserName = db.UserData.AsEnumerable().Where(x => x.UserID == c.AssignCreateUserID).Select(x => x.UserName).First(); v.CreateDT = c.CreateDateTime; v.CreateDTStr = (c.CreateDateTime ?? DateTime.Now).ToString("yyyy/MM/dd HH:mm"); v.CreateUserID = c.CreateUserID; v.CreateUserName = db.UserData.AsEnumerable().Where(x => x.UserID == c.CreateUserID).Select(x => x.UserName).First(); v.ExpectArriveDT = c.ExpectArriveDateTime; v.ExpectArriveDTStr = (c.ExpectArriveDateTime ?? DateTime.Now).ToString("yyyy/MM/dd HH:mm"); if (c.ExpectArriveSW == "0") { v.ExpectArriveSW = "自行約定"; v.ExpectArriveDTStr = "自行約定"; } else if (c.ExpectArriveSW == "1") v.ExpectArriveSW = "合約規定"; else if (c.ExpectArriveSW == "2") v.ExpectArriveSW = "建議期望"; else v.ExpectArriveSW = "客戶指定"; //v.HasActtachment = ""; //var qryF = db.Files.Where(x => x.CID == c.Num); //foreach (Files f in qryF) //{ // v.HasActtachment = "@"; //} //v.HasAssignActtachment = ""; //尚未處理 v.ProductNote = c.ProductClass1.Note; //db.ProductClass.AsEnumerable().Where(x => x.ProductClassID == c.ProductClass).Select(x => x.Note).First(); v.ProductClass = c.ProductClass; v.ItemClass = c.ItemClass; v.ItemNote = c.ItemClass1.Note; //db.ItemClass.AsEnumerable().Where(x => x.ItemClassID == c.ItemClass).Select(x => x.Note).First(); v.QuestionClass = c.QuestionClass; v.QuestionNote = c.QuestionClass1.Note; //db.QuestionClass.AsEnumerable().Where(x => x.QuestionClassID == c.QuestionClass).Select(x => x.Note).First(); v.QuestionDesc = c.QuestionDescription; v.RespondDT = c.RespondDT; v.RespondReason = c.RespondReason; v.RespondSW = c.RespondSW; v.ScheduleEndDT = c.ScheduleEndDT; v.ScheduleEndDTStr = (c.ScheduleEndDT ?? DateTime.Now).ToString("yyyy/MM/dd HH:mm"); v.ScheduleStartDT = c.ScheduleStartDT; v.ScheduleStartDTStr = (c.ScheduleStartDT ?? DateTime.Now).ToString("yyyy/MM/dd HH:mm"); v.ServiceHours = c.ScheduleServiceHours ?? 0; //問題附件 v.HasActtachment = ""; if (db.Files.Where(x => x.CID == c.Num && x.Class == 1).Count() > 0) v.HasActtachment = "@"; //預先保養 v.MaintainNum = ""; int maintainNum = db.EventRelation.Where(x => x.CID == c.Num).Count(); if (maintainNum != 0) v.MaintainNum = maintainNum.ToString(); //解決方式附件 v.HasAssignActtachment = ""; if (db.Files.Where(x => x.CID == c.Num && x.Class == 2).Count() > 0) v.HasAssignActtachment = "@"; //解決方式路徑 v.HasKnowledge = ""; if (db.Files.Where(x => x.CID == c.Num && x.Class == 3).Count() > 0) v.HasKnowledge = "@"; if (c.EventStatus == 30) v.EventStatusStr = "30.已接受未排程"; else if (c.EventStatus == 31) v.EventStatusStr = "31.已接受已排程"; else if (c.EventStatus == 41) v.EventStatusStr = "41.已到達未離開"; else if (c.EventStatus == 42) v.EventStatusStr = "42.已離開待附件"; else if (c.EventStatus == 46) v.EventStatusStr = "46.已解決未審核"; else if (c.EventStatus == 50) v.EventStatusStr = "50.已解決已審核"; if (c.WBSEventM.CloseDT != null) v.EventStatusStr = "99.已結案"; ret.Add(v); } } } } return ret.AsQueryable().OrderBy(p => p.ScheduleStartDTStr); }