//派單畫面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()); ;
}
