//派單主畫面-該執行人異常案件
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);
}
