//派單畫面GridView2 public IQueryable<WBSAssignPeopleViewModel> ReadPeople(String area) { List<WBSAssignPeopleViewModel> ret = new List<WBSAssignPeopleViewModel>(); var qryS = db.AreaReResponsibleClass.Where(x => x.AreaID != "ALL"); if (area != "ALL") qryS = qryS.Where(x => x.AreaID == area); 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); } foreach (AreaReResponsibleClass s in qryS) { //var qryR = db.ResponsibleClass.Where(x => x.SubAreaID == s.SubAreaID); var qryR = db.ResponsibleClass.Where(x => x.ID == s.ResponsibleClassID); foreach (ResponsibleClass R in qryR) { var qry = db.UserData.Where(x => x.UserID == R.UserID); foreach (UserData u in qry) { int Anum = 0; //派於今日且接受(預計到達時間為今日) int Anum2 = 0; //派於今日未接受(期望到達時間為今日) //20160803改全部未接受 int Anum3 = 0; //派於今日且拒絕(期望到達時間為今日) //20160803改全部已拒絕 int Allnum = 0; //總數 var qryA = db.ResponsibleClass.Where(x => x.UserID == u.UserID); foreach (ResponsibleClass a in qryA) { //var qryN = db.WBSEventC.Where(x => x.ResponsibleID == a.ID && x.ScheduleStartDT != null); var qryN = db.WBSEventC.Where(x => x.ResponsibleID == a.ID && x.RespondSW == "Y"); //var qryN = db.WBSEventC.Where(x => x.AssignDT != null && (x.RespondSW == null || x.RespondSW == "Y")); foreach (WBSEventC n in qryN) { //DateTime ADT = n.ScheduleStartDT ?? DateTime.Now; if (n.ScheduleStartDT != null) { DateTime ADT = n.ScheduleStartDT.Value; if (ADT.Year == DateTime.Now.Year && ADT.Month == DateTime.Now.Month && ADT.Day == DateTime.Now.Day) Anum++; } } var qryN2 = db.WBSEventC.Where(x => x.ResponsibleID == a.ID && x.EventStatus == 21); //var qryN = db.WBSEventC.Where(x => x.AssignDT != null && (x.RespondSW == null || x.RespondSW == "Y")); foreach (WBSEventC n in qryN2) { if (n.ExpectArriveDateTime != null) { //DateTime ADT = n.ExpectArriveDateTime.Value; //if (ADT.Year == DateTime.Now.Year && ADT.Month == DateTime.Now.Month && ADT.Day == DateTime.Now.Day) Anum2++; } } var qryN3 = db.WBSEventC.Where(x => x.ResponsibleID == a.ID && x.EventStatus == 20); foreach (WBSEventC n in qryN3) { if (n.ExpectArriveDateTime != null) { //DateTime ADT = n.ExpectArriveDateTime.Value; //if (ADT.Year == DateTime.Now.Year && ADT.Month == DateTime.Now.Month && ADT.Day == DateTime.Now.Day) Anum3++; } } //var qryN4 = db.WBSEventC.Where(x => x.ResponsibleID == a.ID); //foreach (WBSEventC n in qryN4) //{ // if (n.ScheduleStartDT != null) { // DateTime ADT = n.ScheduleStartDT.Value; // if (ADT.Year == DateTime.Now.Year && ADT.Month == DateTime.Now.Month && ADT.Day == DateTime.Now.Day) // Allnum++; // } // else // { // if (n.ExpectArriveDateTime != null) // { // DateTime ADT = n.ExpectArriveDateTime.Value; // if (ADT.Year == DateTime.Now.Year && ADT.Month == DateTime.Now.Month && ADT.Day == DateTime.Now.Day) // Allnum++; // } // } //} } WBSAssignPeopleViewModel v = new WBSAssignPeopleViewModel(); v.HasActtachment = ""; v.AssignNum = Anum; //派於今日且接受 v.AssignNum2 = Anum2; //ALL未接受 Allnum = Anum + Anum2 + Anum3; v.Num1DNum2 = Anum.ToString() + "/" + Anum2.ToString() + "/" + Anum3.ToString() + "/" + Allnum.ToString(); v.Now = "待命中"; v.UserID = u.UserID; v.UserName = R.Name + "-" + u.UserName; if (R.UserID2 != null) v.UserName2 = R.Name + "-" + db.UserData.AsEnumerable().Where(x => x.UserID == R.UserID2).Select(x => x.UserName).First(); v.RoleID = u.RoleID; v.Phone1 = u.Phone1; v.Phone2 = u.Phone2; v.Phone3 = u.Phone3; v.LoginID = u.LoginID; v.StartDate = u.StartDate; v.EndDate = u.EndDate; v.Email = u.Email; v.ResponsibleID = u.ResponsibleID; foreach (ResponsibleClass a in qryA) { //var qryC = db.WBSEventC.Where(x => x.ResponsibleID == a.ID && x.ServiceLeaveDT == null && x.ServiceArriveDT != null); //var qryC = db.WBSEventC.Where(x => x.ServiceLeaveDT == null && x.ServiceArriveDT != null); if (db.WBSEventC.Where(x => x.ResponsibleID == a.ID && x.EventStatus == 41).Count() > 0) { var c = db.WBSEventC.Where(x => x.ResponsibleID == a.ID && x.EventStatus == 41).OrderByDescending(x => x.ServiceArriveDT).First(); //foreach (WBSEventC c in qryC) //{ var qryM = db.WBSEventM.Where(x => x.CaseID == c.MomCaseID); foreach (WBSEventM m in qryM) { v.CustomerSName = db.Customer.AsEnumerable().Where(x => x.CustomerRef == m.CustomerRef).Select(x => x.ShortName).First(); 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.MCaseID = m.CaseID; } v.CCaseNum = c.Num; v.CaseNumC = (c.ChildCaseID < 10) ? ("0" + c.ChildCaseID.ToString()) : c.ChildCaseID.ToString(); v.Now = "服務中"; v.ProductClass = c.ProductClass; v.ProductNote = db.ProductClass.AsEnumerable().Where(x => x.ProductClassID == c.ProductClass).Select(x => x.Note).First(); v.ItemClass = c.ItemClass; v.ItemNote = db.ItemClass.AsEnumerable().Where(x => x.ItemClassID == c.ItemClass).Select(x => x.Note).First(); v.QuestionClass = c.QuestionClass; v.QuestionNote = db.QuestionClass.AsEnumerable().Where(x => x.QuestionClassID == c.QuestionClass).Select(x => x.Note).First(); v.QuestionDesc = c.QuestionDescription; if (db.Files.Where(x => x.CID == c.Num && x.Class == 1).Count() > 0) v.HasActtachment = "@"; v.StartDT = c.ServiceArriveDT; v.StartDTStr = (c.ServiceArriveDT ?? DateTime.Now).ToString("MM/dd HH:mm"); 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.ServiceArriveDT.Value.AddMinutes(temp).CompareTo(DateTime.Now) < 0) { //排程後超過"預計到達時間"N分未到達 v.AbnormalReason = "逾時未到達"; //isAbnormal = true; } } 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.CreateDateTime.Value.AddMinutes(temp).CompareTo(DateTime.Now) < 0) if (c.ServiceArriveDT.Value.AddMinutes(temp).CompareTo(DateTime.Now) < 0) { //排程後超過"實際到達時間"N分未離開 v.AbnormalReason = "逾時未離開"; //isAbnormal = true; } } System.TimeSpan diff1 = DateTime.Now.Subtract(c.ServiceArriveDT ?? DateTime.Now); //v.ServiceDT = Convert.ToDateTime(diff1.ToString()); //int day = Int32.Parse(diff1.ToString("%d")); //int hour = Int32.Parse(diff1.ToString("%h")); //hour += day * 24; v.ServiceDTStr = diff1.Days.ToString() + "日" + diff1.Hours.ToString() + "時" + diff1.Minutes.ToString() + "分"; ////v.ServiceDT = DateTime.Now.Subtract(c.ServiceArriveDT; //TimeSpan duration = new TimeSpan(0, c.ScheduleServiceHours ?? 0, 0, 0); //v.OverTime = ""; //if (diff1.CompareTo(duration) > 0) // v.OverTime = "逾時"; //} } } //v.UserName2 = db.UserData.AsEnumerable().Where(x => x.UserID == R.UserID2).Select(x => x.UserName).First(); v.Name = R.Name; v.ID = R.ID; if (v.CaseNumC == null) v.CaseNumC = ""; if (v.CaseNumM == null) v.CaseNumM = ""; if (v.CustomerSName == null) v.CustomerSName = ""; ret.Add(v); } } } return ret.AsQueryable().GroupBy(x => x.ID).Select(x => x.First()); ; }
//派單畫面GridView2 public IQueryable<WBSAssignPeopleViewModel> ReadPeople(string area) { List<WBSAssignPeopleViewModel> ret = new List<WBSAssignPeopleViewModel>(); var qryS = db.AreaReResponsibleClass.Where(x => x.AreaID != "ALL"); if (area != "ALL") qryS = qryS.Where(x => x.AreaID == area); foreach (AreaReResponsibleClass s in qryS) { //var qryR = db.ResponsibleClass.Where(x => x.SubAreaID == s.SubAreaID); var qryR = db.ResponsibleClass.Where(x => x.ID == s.ResponsibleClassID); foreach (ResponsibleClass R in qryR) { var qry = db.UserData.Where(x => x.UserID == R.UserID); foreach (UserData u in qry) { int Anum = 0; //派於今日且接受(預計到達時間為今日) int Anum2 = 0; //派於今日未拒絕(期望到達時間為今日) var qryA = db.ResponsibleClass.Where(x => x.UserID == u.UserID); foreach (ResponsibleClass a in qryA) { var qryN = db.WBSEventC.Where(x => x.ResponsibleID == a.ID && x.ScheduleStartDT != null); //var qryN = db.WBSEventC.Where(x => x.AssignDT != null && (x.RespondSW == null || x.RespondSW == "Y")); foreach (WBSEventC n in qryN) { DateTime ADT = n.ScheduleStartDT ?? DateTime.Now; if (ADT.Year == DateTime.Now.Year && ADT.Month == DateTime.Now.Month && ADT.Day == DateTime.Now.Day) Anum++; } var qryN2 = db.WBSEventC.Where(x => x.ResponsibleID == a.ID && (x.RespondSW == null || x.RespondSW == "Y")); //var qryN = db.WBSEventC.Where(x => x.AssignDT != null && (x.RespondSW == null || x.RespondSW == "Y")); foreach (WBSEventC n in qryN2) { DateTime ADT = n.ExpectArriveDateTime ?? DateTime.Now; if (ADT.Year == DateTime.Now.Year && ADT.Month == DateTime.Now.Month && ADT.Day == DateTime.Now.Day) Anum2++; } } WBSAssignPeopleViewModel v = new WBSAssignPeopleViewModel(); v.HasActtachment = ""; v.AssignNum = Anum; //派於今日且接受 v.AssignNum2 = Anum2; //派於今日未拒絕 v.Num1DNum2 = Anum.ToString() + "/" + Anum2.ToString(); v.Now = "待命中"; v.UserID = u.UserID; v.UserName = R.Name + "-" + u.UserName; if (R.UserID2 != null) v.UserName2 = R.Name + "-" + db.UserData.AsEnumerable().Where(x => x.UserID == R.UserID2).Select(x => x.UserName).First(); v.RoleID = u.RoleID; v.Phone1 = u.Phone1; v.Phone2 = u.Phone2; v.Phone3 = u.Phone3; v.LoginID = u.LoginID; v.StartDate = u.StartDate; v.EndDate = u.EndDate; v.Email = u.Email; v.ResponsibleID = u.ResponsibleID; foreach (ResponsibleClass a in qryA) { //var qryC = db.WBSEventC.Where(x => x.ResponsibleID == a.ID && x.ServiceLeaveDT == null && x.ServiceArriveDT != null); //var qryC = db.WBSEventC.Where(x => x.ServiceLeaveDT == null && x.ServiceArriveDT != null); var qryC = db.WBSEventC.Where(x => x.ResponsibleID == a.ID && x.EventStatus == 41); foreach (WBSEventC c in qryC) { var qryM = db.WBSEventM.Where(x => x.CaseID == c.MomCaseID); foreach (WBSEventM m in qryM) { v.CustomerSName = db.Customer.AsEnumerable().Where(x => x.CustomerRef == m.CustomerRef).Select(x => x.ShortName).First(); 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.CCaseNum = c.Num; v.CaseNumC = (c.ChildCaseID < 10) ? ("0" + c.ChildCaseID.ToString()) : c.ChildCaseID.ToString(); v.Now = "服務中"; v.ProductClass = c.ProductClass; v.ProductNote = db.ProductClass.AsEnumerable().Where(x => x.ProductClassID == c.ProductClass).Select(x => x.Note).First(); v.ItemClass = c.ItemClass; v.ItemNote = db.ItemClass.AsEnumerable().Where(x => x.ItemClassID == c.ItemClass).Select(x => x.Note).First(); v.QuestionClass = c.QuestionClass; v.QuestionNote = db.QuestionClass.AsEnumerable().Where(x => x.QuestionClassID == c.QuestionClass).Select(x => x.Note).First(); v.QuestionDesc = c.QuestionDescription; if (db.Files.Where(x => x.CID == c.Num && x.Class == 1).Count() > 0) v.HasActtachment = "@"; v.StartDT = c.ServiceArriveDT; v.StartDTStr = (c.ServiceArriveDT ?? DateTime.Now).ToString("yyyy/MM/dd HH:mm"); System.TimeSpan diff1 = DateTime.Now.Subtract(c.ServiceArriveDT ?? DateTime.Now); //v.ServiceDT = Convert.ToDateTime(diff1.ToString()); int day = Int32.Parse(diff1.ToString("%d")); int hour = Int32.Parse(diff1.ToString("%h")); hour += day * 24; v.ServiceDTStr = hour.ToString() + ":" + diff1.ToString(@"mm\:ss"); //v.ServiceDT = DateTime.Now.Subtract(c.ServiceArriveDT; TimeSpan duration = new TimeSpan(0, c.ScheduleServiceHours ?? 0, 0, 0); v.OverTime = ""; if (diff1.CompareTo(duration) > 0) v.OverTime = "逾時"; } } //v.UserName2 = db.UserData.AsEnumerable().Where(x => x.UserID == R.UserID2).Select(x => x.UserName).First(); v.Name = R.Name; v.ID = R.ID; ret.Add(v); } } } //var qryS = db.SubArea.Where(x => x.AreaID != "ALL"); //if(area != "ALL") // qryS = qryS.Where(x => x.AreaID == area); //foreach (SubArea s in qryS) //{ // //var qryR = db.ResponsibleClass.Where(x => x.SubAreaID == s.SubAreaID); // var qryR = db.ResponsibleClass.Where(x => x.ID == s.ResponsibleID); // foreach (ResponsibleClass R in qryR) // { // var qry = db.UserData.Where(x => x.UserID == R.UserID); // foreach (UserData u in qry) // { // int Anum = 0; // var qryA = db.ResponsibleClass.Where(x => x.UserID == u.UserID); // foreach (ResponsibleClass a in qryA) // { // var qryN = db.WBSEventC.Where(x => x.ResponsibleID == a.ID && x.ScheduleStartDT != null ); // //var qryN = db.WBSEventC.Where(x => x.AssignDT != null && (x.RespondSW == null || x.RespondSW == "Y")); // foreach (WBSEventC n in qryN) // { // DateTime ADT = n.ScheduleStartDT ?? DateTime.Now; // if (ADT.Year == DateTime.Now.Year && ADT.Month == DateTime.Now.Month && ADT.Day == DateTime.Now.Day) // Anum++; // } // } // WBSAssignPeopleViewModel v = new WBSAssignPeopleViewModel(); // v.AssignNum = Anum; // v.Now = "待命中"; // v.UserID = u.UserID; // v.UserName = R.Name + "-" + u.UserName; // if (R.UserID2!=null) // v.UserName2 = R.Name + "-" + db.UserData.AsEnumerable().Where(x => x.UserID == R.UserID2).Select(x => x.UserName).First(); // v.RoleID = u.RoleID; // v.Phone1 = u.Phone1; // v.Phone2 = u.Phone2; // v.Phone3 = u.Phone3; // v.LoginID = u.LoginID; // v.StartDate = u.StartDate; // v.EndDate = u.EndDate; // v.Email = u.Email; // v.ResponsibleID = u.ResponsibleID; // foreach (ResponsibleClass a in qryA) // { // //var qryC = db.WBSEventC.Where(x => x.ResponsibleID == a.ID && x.ServiceLeaveDT == null && x.ServiceArriveDT != null); // //var qryC = db.WBSEventC.Where(x => x.ServiceLeaveDT == null && x.ServiceArriveDT != null); // var qryC = db.WBSEventC.Where(x => x.ResponsibleID == a.ID && x.EventStatus == 41); // foreach (WBSEventC c in qryC) // { // var qryM = db.WBSEventM.Where(x => x.CaseID == c.MomCaseID); // foreach (WBSEventM m in qryM) // { // v.CustomerSName = db.Customer.AsEnumerable().Where(x => x.CustomerRef == m.CustomerRef).Select(x => x.ShortName).First(); // 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.CaseNumC = (c.ChildCaseID < 10) ? ("0" + c.ChildCaseID.ToString()) : c.ChildCaseID.ToString(); // v.Now = "服務中"; // v.ProductClass = c.ProductClass; // v.ProductNote = db.ProductClass.AsEnumerable().Where(x => x.ProductClassID == c.ProductClass).Select(x => x.Note).First(); // v.ItemClass = c.ItemClass; // v.ItemNote = db.ItemClass.AsEnumerable().Where(x => x.ItemClassID == c.ItemClass).Select(x => x.Note).First(); // v.QuestionClass = c.QuestionClass; // v.QuestionNote = db.QuestionClass.AsEnumerable().Where(x => x.QuestionClassID == c.QuestionClass).Select(x => x.Note).First(); // v.QuestionDesc = c.QuestionDescription; // v.StartDT = c.ServiceArriveDT; // v.StartDTStr = (c.ServiceArriveDT ?? DateTime.Now).ToString("yyyy/MM/dd HH:mm"); // System.TimeSpan diff1 = DateTime.Now.Subtract(c.ServiceArriveDT ?? DateTime.Now); // //v.ServiceDT = Convert.ToDateTime(diff1.ToString()); // int day = Int32.Parse(diff1.ToString("%d")); // int hour = Int32.Parse(diff1.ToString("%h")); // hour += day * 24; // v.ServiceDTStr = hour.ToString() + ":" + diff1.ToString(@"mm\:ss"); // //v.ServiceDT = DateTime.Now.Subtract(c.ServiceArriveDT; // TimeSpan duration = new TimeSpan(0, c.ScheduleServiceHours ?? 0, 0, 0); // v.OverTime = ""; // if (diff1.CompareTo(duration) > 0) // v.OverTime = "逾時"; // } // } // //v.UserName2 = db.UserData.AsEnumerable().Where(x => x.UserID == R.UserID2).Select(x => x.UserName).First(); // v.Name = R.Name; // v.ID = R.ID; // ret.Add(v); // } // } //} return ret.AsQueryable().GroupBy(x => x.ID).Select(x => x.First()); ; }