public IActionResult date_search(int?cate, int?status, DateTime?start, DateTime?end)
{
int DepId = int.Parse(HttpContext.Session.GetString(CDictionary.CURRENT_LOGINED_USERDEPARTMENTID));
var table = DB.TLeaveApplications.Join(DB.TUsers,
l => l.CEmployeeId,
u => u.CEmployeeId,
(l, u) => new {
CEmployeeId = l.CEmployeeId,
CApplyNumber = l.CApplyNumber,
CApplyDate = l.CApplyDate,
CLeaveCategory = l.CLeaveCategory,
CLeaveStartTime = l.CLeaveStartTime,
CLeaveEndTime = l.CLeaveEndTime,
CCheckStatus = l.CCheckStatus,
CReason = l.CReason,
employeeName = u.CEmployeeName,
CDepartmentId = u.CDepartmentId
}).OrderByDescending(l => l.CApplyDate).AsEnumerable().Where(sc =>
sc.CDepartmentId == DepId &&
(sc.CEmployeeId != 1) &&
(cate != null?sc.CLeaveCategory == cate:true) &&
(status != null?sc.CCheckStatus == status:true) &&
(start != null? DateTime.Parse(sc.CApplyDate) >= start:true) &&
(end != null? DateTime.Parse(sc.CApplyDate) <= end:true)).ToList();
List <TLeaveApplicationViewModel> list = new List <TLeaveApplicationViewModel>();
foreach (var item in table)
{
TLeaveApplicationViewModel leaObj = new TLeaveApplicationViewModel()
{
CApplyNumber = item.CApplyNumber,
CEmployeeId = item.CEmployeeId,
employeeName = item.employeeName,
CApplyDate = item.CApplyDate,
CLeaveCategory = item.CLeaveCategory,
CLeaveStartTime = item.CLeaveStartTime,
CLeaveEndTime = item.CLeaveEndTime,
CReason = item.CReason,
CCheckStatus = item.CCheckStatus
};
list.Add(leaObj);
}
return(PartialView("date_search", list));
}
public IActionResult List()
{
int DepId = int.Parse(HttpContext.Session.GetString(CDictionary.CURRENT_LOGINED_USERDEPARTMENTID));
List <TLeaveApplicationViewModel> list = new List <TLeaveApplicationViewModel>();
List <TLeave> leaveCate = getLeaveCategory();//取得資料庫的類別
ViewBag.leaveCategory = leaveCate;
List <TCheckStatus> checkSta = getCheckStatus();//取的資料庫的審核狀態
ViewBag.leaveStatus = checkSta;
if (!string.IsNullOrEmpty(Request.ContentType))
{
string AppNum = Request.Form["txtAppNum"];
string Id = Request.Form["txtId"];
string Name = Request.Form["txtName"];
//AppNum有值
if (!string.IsNullOrEmpty(AppNum) && string.IsNullOrEmpty(Id) && string.IsNullOrEmpty(Name))
{
var table = from leave in DB.TLeaveApplications
join user in DB.TUsers on leave.CEmployeeId equals user.CEmployeeId
where leave.CDepartmentId == DepId &&
(leave.CEmployeeId != 1) &&
leave.CApplyNumber.ToString().Contains(AppNum)
orderby leave.CApplyDate descending
select new
{
user.CEmployeeName,
leave.CApplyDate,
leave.CApplyNumber,
leave.CCheckStatus,
leave.CLeaveCategory,
leave.CLeaveStartTime,
leave.CLeaveEndTime,
leave.CEmployeeId,
leave.CReason
};
foreach (var item in table)
{
TLeaveApplicationViewModel newObj = new TLeaveApplicationViewModel()
{
CApplyNumber = item.CApplyNumber,
CApplyDate = item.CApplyDate,
employeeName = item.CEmployeeName,
CEmployeeId = item.CEmployeeId,
CReason = item.CReason,
CLeaveStartTime = item.CLeaveStartTime,
CLeaveEndTime = item.CLeaveEndTime,
CLeaveCategory = item.CLeaveCategory,
CCheckStatus = item.CCheckStatus
};
list.Add(newObj);
}
}
//Id有值
else if (string.IsNullOrEmpty(AppNum) && !string.IsNullOrEmpty(Id) && string.IsNullOrEmpty(Name))
{
var table = from leave in DB.TLeaveApplications
join user in DB.TUsers
on leave.CEmployeeId equals user.CEmployeeId
where leave.CDepartmentId == DepId &&
(leave.CEmployeeId != 1) &&
leave.CEmployeeId.ToString().Contains(Id)
orderby leave.CApplyDate descending
select new
{
user.CEmployeeName,
leave.CApplyDate,
leave.CApplyNumber,
leave.CCheckStatus,
leave.CLeaveCategory,
leave.CLeaveStartTime,
leave.CLeaveEndTime,
leave.CEmployeeId,
leave.CReason
};
foreach (var item in table)
{
TLeaveApplicationViewModel newObj = new TLeaveApplicationViewModel()
{
CApplyNumber = item.CApplyNumber,
CApplyDate = item.CApplyDate,
employeeName = item.CEmployeeName,
CEmployeeId = item.CEmployeeId,
CReason = item.CReason,
CLeaveStartTime = item.CLeaveStartTime,
CLeaveEndTime = item.CLeaveEndTime,
CLeaveCategory = item.CLeaveCategory,
CCheckStatus = item.CCheckStatus
};
list.Add(newObj);
}
}
//Name有值
else if (string.IsNullOrEmpty(AppNum) && string.IsNullOrEmpty(Id) && !string.IsNullOrEmpty(Name))
{
var table = from leave in DB.TLeaveApplications
join user in DB.TUsers
on leave.CEmployeeId equals user.CEmployeeId
where leave.CDepartmentId == DepId &&
(leave.CEmployeeId != 1) &&
user.CEmployeeName.Contains(Name)
orderby leave.CApplyDate descending
select new
{
user.CEmployeeName,
leave.CApplyDate,
leave.CApplyNumber,
leave.CCheckStatus,
leave.CLeaveCategory,
leave.CLeaveStartTime,
leave.CLeaveEndTime,
leave.CEmployeeId,
leave.CReason
};
foreach (var item in table)
{
TLeaveApplicationViewModel newObj = new TLeaveApplicationViewModel()
{
CApplyNumber = item.CApplyNumber,
CApplyDate = item.CApplyDate,
employeeName = item.CEmployeeName,
CEmployeeId = item.CEmployeeId,
CReason = item.CReason,
CLeaveStartTime = item.CLeaveStartTime,
CLeaveEndTime = item.CLeaveEndTime,
CLeaveCategory = item.CLeaveCategory,
CCheckStatus = item.CCheckStatus
};
list.Add(newObj);
}
}
//AppNum, Id有值
else if (!string.IsNullOrEmpty(AppNum) && !string.IsNullOrEmpty(Id) && string.IsNullOrEmpty(Name))
{
var table = from leave in DB.TLeaveApplications
join user in DB.TUsers
on leave.CEmployeeId equals user.CEmployeeId
where leave.CDepartmentId == DepId &&
(leave.CEmployeeId != 1) &&
leave.CApplyNumber.ToString().Contains(AppNum) &&
leave.CEmployeeId.ToString().Contains(Id)
orderby leave.CApplyDate descending
select new
{
user.CEmployeeName,
leave.CApplyDate,
leave.CApplyNumber,
leave.CCheckStatus,
leave.CLeaveCategory,
leave.CLeaveStartTime,
leave.CLeaveEndTime,
leave.CEmployeeId,
leave.CReason
};
foreach (var item in table)
{
TLeaveApplicationViewModel newObj = new TLeaveApplicationViewModel()
{
CApplyNumber = item.CApplyNumber,
CApplyDate = item.CApplyDate,
employeeName = item.CEmployeeName,
CEmployeeId = item.CEmployeeId,
CReason = item.CReason,
CLeaveStartTime = item.CLeaveStartTime,
CLeaveEndTime = item.CLeaveEndTime,
CLeaveCategory = item.CLeaveCategory,
CCheckStatus = item.CCheckStatus
};
list.Add(newObj);
}
}
//AppNum, Name有值
else if (!string.IsNullOrEmpty(AppNum) && string.IsNullOrEmpty(Id) && !string.IsNullOrEmpty(Name))
{
var table = from leave in DB.TLeaveApplications
join user in DB.TUsers
on leave.CEmployeeId equals user.CEmployeeId
where leave.CDepartmentId == DepId &&
(leave.CEmployeeId != 1) &&
leave.CApplyNumber.ToString().Contains(AppNum) &&
user.CEmployeeName.Contains(Name)
orderby leave.CApplyDate descending
select new
{
user.CEmployeeName,
leave.CApplyDate,
leave.CApplyNumber,
leave.CCheckStatus,
leave.CLeaveCategory,
leave.CLeaveStartTime,
leave.CLeaveEndTime,
leave.CEmployeeId,
leave.CReason
};
foreach (var item in table)
{
TLeaveApplicationViewModel newObj = new TLeaveApplicationViewModel()
{
CApplyNumber = item.CApplyNumber,
CApplyDate = item.CApplyDate,
employeeName = item.CEmployeeName,
CEmployeeId = item.CEmployeeId,
CReason = item.CReason,
CLeaveStartTime = item.CLeaveStartTime,
CLeaveEndTime = item.CLeaveEndTime,
CLeaveCategory = item.CLeaveCategory,
CCheckStatus = item.CCheckStatus
};
list.Add(newObj);
}
}
//Id, Name有值
else if (string.IsNullOrEmpty(AppNum) && !string.IsNullOrEmpty(Id) && !string.IsNullOrEmpty(Name))
{
var table = from leave in DB.TLeaveApplications
join user in DB.TUsers
on leave.CEmployeeId equals user.CEmployeeId
where leave.CDepartmentId == DepId &&
(leave.CEmployeeId != 1) &&
leave.CEmployeeId.ToString().Contains(Id) &&
user.CEmployeeName.Contains(Name)
orderby leave.CApplyDate descending
select new
{
user.CEmployeeName,
leave.CApplyDate,
leave.CApplyNumber,
leave.CCheckStatus,
leave.CLeaveCategory,
leave.CLeaveStartTime,
leave.CLeaveEndTime,
leave.CEmployeeId,
leave.CReason
};
foreach (var item in table)
{
TLeaveApplicationViewModel newObj = new TLeaveApplicationViewModel()
{
CApplyNumber = item.CApplyNumber,
CApplyDate = item.CApplyDate,
employeeName = item.CEmployeeName,
CEmployeeId = item.CEmployeeId,
CReason = item.CReason,
CLeaveStartTime = item.CLeaveStartTime,
CLeaveEndTime = item.CLeaveEndTime,
CLeaveCategory = item.CLeaveCategory,
CCheckStatus = item.CCheckStatus
};
list.Add(newObj);
}
}
//AppNum, Id, Name有值
else if (!string.IsNullOrEmpty(AppNum) && !string.IsNullOrEmpty(Id) && !string.IsNullOrEmpty(Name))
{
var table = from leave in DB.TLeaveApplications
join user in DB.TUsers
on leave.CEmployeeId equals user.CEmployeeId
where leave.CDepartmentId == DepId &&
(leave.CEmployeeId != 1) &&
leave.CApplyNumber.ToString().Contains(AppNum) &&
leave.CEmployeeId.ToString().Contains(Id) &&
user.CEmployeeName.Contains(Name)
orderby leave.CApplyDate descending
select new
{
user.CEmployeeName,
leave.CApplyDate,
leave.CApplyNumber,
leave.CCheckStatus,
leave.CLeaveCategory,
leave.CLeaveStartTime,
leave.CLeaveEndTime,
leave.CEmployeeId,
leave.CReason
};
foreach (var item in table)
{
TLeaveApplicationViewModel newObj = new TLeaveApplicationViewModel()
{
CApplyNumber = item.CApplyNumber,
CApplyDate = item.CApplyDate,
employeeName = item.CEmployeeName,
CEmployeeId = item.CEmployeeId,
CReason = item.CReason,
CLeaveStartTime = item.CLeaveStartTime,
CLeaveEndTime = item.CLeaveEndTime,
CLeaveCategory = item.CLeaveCategory,
CCheckStatus = item.CCheckStatus
};
list.Add(newObj);
}
}
//全部皆為空白
else if (AppNum == "" && Id == "" && Name == "")
{
var table = from leave in DB.TLeaveApplications
join user in DB.TUsers
on leave.CEmployeeId equals user.CEmployeeId
where leave.CDepartmentId == DepId &&
(leave.CEmployeeId != 1)
orderby leave.CApplyDate descending
select new
{
user.CEmployeeName,
leave.CApplyDate,
leave.CApplyNumber,
leave.CCheckStatus,
leave.CLeaveCategory,
leave.CLeaveStartTime,
leave.CLeaveEndTime,
leave.CEmployeeId,
leave.CReason
};
foreach (var item in table)
{
TLeaveApplicationViewModel newObj = new TLeaveApplicationViewModel()
{
CApplyNumber = item.CApplyNumber,
CApplyDate = item.CApplyDate,
employeeName = item.CEmployeeName,
CEmployeeId = item.CEmployeeId,
CReason = item.CReason,
CLeaveStartTime = item.CLeaveStartTime,
CLeaveEndTime = item.CLeaveEndTime,
CLeaveCategory = item.CLeaveCategory,
CCheckStatus = item.CCheckStatus
};
list.Add(newObj);
}
}
}
//全部皆為空白
else
{
var table = from leave in DB.TLeaveApplications
join user in DB.TUsers
on leave.CEmployeeId equals user.CEmployeeId
where leave.CDepartmentId == DepId &&
(leave.CEmployeeId != 1)
orderby leave.CApplyDate descending
select new
{
user.CEmployeeName,
leave.CApplyDate,
leave.CApplyNumber,
leave.CCheckStatus,
leave.CLeaveCategory,
leave.CLeaveStartTime,
leave.CLeaveEndTime,
leave.CEmployeeId,
leave.CReason
};
foreach (var item in table)
{
TLeaveApplicationViewModel newObj = new TLeaveApplicationViewModel()
{
CApplyNumber = item.CApplyNumber,
CApplyDate = item.CApplyDate,
employeeName = item.CEmployeeName,
CEmployeeId = item.CEmployeeId,
CReason = item.CReason,
CLeaveStartTime = item.CLeaveStartTime,
CLeaveEndTime = item.CLeaveEndTime,
CLeaveCategory = item.CLeaveCategory,
CCheckStatus = item.CCheckStatus
};
list.Add(newObj);
}
}
return(View(list));
}
public IActionResult Mutiple_search(int?cate, int?status, string?start, string?end)
{
//List<TLeaveApplication> table = MyHr.TLeaveApplications.Where(n => n.CLeaveCategory == cate).ToList();
//string str_json = JsonConvert.SerializeObject(table, Formatting.Indented);
//return str_json;
//顯示JSON字串
//li_showData.Text = str_json;
ViewBag.UserId = int.Parse(HttpContext.Session.GetString(CDictionary.CURRENT_LOGINED_USERID));
ViewBag.UserName = HttpContext.Session.GetString(CDictionary.CURRENT_LOGINED_USERNAME);
var table = MyHr.TLeaveApplications
.Join(MyHr.TUserDepartments, d => d.CDepartmentId, u => u.CDepartmentId, (d, u) => new
{
CApplyNumber = d.CApplyNumber,
CEmployeeId = d.CEmployeeId,
//CEmployeeName = u.CEmployeeName,
CDepartmentId = d.CDepartmentId,
CDepartmentName = u.CDepartment,
CApplyDate = d.CApplyDate,
CLeaveCategory = d.CLeaveCategory,
CLeaveStartTime = d.CLeaveStartTime,
CLeaveEndTime = d.CLeaveEndTime,
CReason = d.CReason,
CCheckStatus = d.CCheckStatus,
CLeaveHours = d.CLeaveHours,
}).OrderBy(du => du.CApplyDate).AsEnumerable().Where(du =>
(cate != null ? du.CLeaveCategory == cate:true) &&
(status != null ? du.CCheckStatus == status : true) &&
(start != null ? DateTime.Parse(du.CLeaveEndTime) >= DateTime.Parse(start) : true) &&
(end != null? DateTime.Parse(du.CLeaveStartTime) <= DateTime.Parse(end):true) &&
((end != null && start != null)? DateTime.Parse(du.CLeaveEndTime) >= DateTime.Parse(start) && DateTime.Parse(du.CLeaveStartTime) <= DateTime.Parse(end) : true)
).ToList();
//int abc = table.Count; ■ 這邊搜尋的資料並未針對USER，當沒有資料想要在前端做變化時，應當加入此條件。
List <TLeaveApplicationViewModel> T = new List <TLeaveApplicationViewModel>();
foreach (var item in table)
{
TLeaveApplicationViewModel obj = new TLeaveApplicationViewModel()
{
CApplyNumber = item.CApplyNumber,
CEmployeeId = item.CEmployeeId,
CDepartmentId = item.CDepartmentId,
CDepartmentName = item.CDepartmentName,
CApplyDate = item.CApplyDate,
CLeaveCategory = item.CLeaveCategory,
CLeaveStartTime = item.CLeaveStartTime,
CLeaveEndTime = item.CLeaveEndTime,
CReason = item.CReason,
CCheckStatus = item.CCheckStatus,
CLeaveHours = item.CLeaveHours
};
T.Add(obj);
}
//foreach (TLeaveApplication C in table)
// T.Add(new TLeaveApplicationViewModel(C,null));
return(PartialView("Mutiple_search", T));
}
