public IActionResult date_search(int?status, DateTime?start, DateTime?end)
{
int DepId = int.Parse(HttpContext.Session.GetString(CDictionary.CURRENT_LOGINED_USERDEPARTMENTID));
var table = DB.TTravelExpenseApplications
.Join(DB.TUsers, t => t.CEmployeeId, u => u.CEmployeeId, (t, u) => new {
CApplyDate = t.CApplyDate,
CEmployeeId = t.CEmployeeId,
CAmont = t.CAmont,
CCheckStatus = t.CCheckStatus,
CTravelStartTime = t.CTravelStartTime,
CTravelEndTime = t.CTravelEndTime,
CReason = t.CReason,
CApplyNumber = t.CApplyNumber,
CEmployeeName = u.CEmployeeName,
CDepartmentId = u.CDepartmentId
}).OrderByDescending(t => t.CApplyDate).Where(sc =>
sc.CDepartmentId == DepId &&
sc.CEmployeeId != 1 &&
(status != null?sc.CCheckStatus == status:true) &&
(start != null?sc.CApplyDate >= start:true) &&
(end != null?sc.CApplyDate <= end:true)).ToList();
List <Travel_Expense_ApplicationViewModel> list = new List <Travel_Expense_ApplicationViewModel>();
foreach (var item in table)
{
Travel_Expense_ApplicationViewModel traObj = new Travel_Expense_ApplicationViewModel()
{
employeeName = item.CEmployeeName,
CApplyNumber = item.CApplyNumber,
CApplyDate = item.CApplyDate,
CTravelStartTime = item.CTravelStartTime,
CTravelEndTime = item.CTravelEndTime,
CAmont = item.CAmont,
CReason = item.CReason,
CEmployeeId = item.CEmployeeId,
CCheckStatus = item.CCheckStatus
};
list.Add(traObj);
}
return(PartialView("date_search", list));
}
public IActionResult List()
{
int DepId = int.Parse(HttpContext.Session.GetString(CDictionary.CURRENT_LOGINED_USERDEPARTMENTID));
List <Travel_Expense_ApplicationViewModel> list = new List <Travel_Expense_ApplicationViewModel>();
List <TCheckStatus> checkSta = getCheckStatus();//取得資料庫的審核狀態
ViewBag.travelStatus = checkSta;
//searching
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 travel in DB.TTravelExpenseApplications
join user in DB.TUsers on travel.CEmployeeId equals user.CEmployeeId
where travel.CDepartmentId == DepId &&
travel.CEmployeeId != 1 &&
travel.CApplyNumber.ToString().Contains(AppNum)
orderby travel.CApplyDate descending
select new
{
user.CEmployeeName,
travel.CApplyDate,
travel.CEmployeeId,
travel.CAmont,
travel.CCheckStatus,
travel.CTravelStartTime,
travel.CTravelEndTime,
travel.CReason,
travel.CApplyNumber
};
foreach (var item in table)
{
Travel_Expense_ApplicationViewModel traObj = new Travel_Expense_ApplicationViewModel()
{
employeeName = item.CEmployeeName,
CApplyNumber = item.CApplyNumber,
CApplyDate = item.CApplyDate,
CTravelStartTime = item.CTravelStartTime,
CTravelEndTime = item.CTravelEndTime,
CAmont = item.CAmont,
CReason = item.CReason,
CEmployeeId = item.CEmployeeId,
CCheckStatus = item.CCheckStatus
};
list.Add(traObj);
}
}
//Id有值
else if (string.IsNullOrEmpty(AppNum) && !string.IsNullOrEmpty(Id) && string.IsNullOrEmpty(Name))
{
var table = from travel in DB.TTravelExpenseApplications
join user in DB.TUsers on travel.CEmployeeId equals user.CEmployeeId
where travel.CDepartmentId == DepId &&
travel.CEmployeeId != 1 &&
travel.CEmployeeId.ToString().Contains(Id)
orderby travel.CApplyDate descending
select new
{
user.CEmployeeName,
travel.CApplyDate,
travel.CEmployeeId,
travel.CAmont,
travel.CCheckStatus,
travel.CTravelStartTime,
travel.CTravelEndTime,
travel.CReason,
travel.CApplyNumber
};
foreach (var item in table)
{
Travel_Expense_ApplicationViewModel traObj = new Travel_Expense_ApplicationViewModel()
{
employeeName = item.CEmployeeName,
CApplyNumber = item.CApplyNumber,
CApplyDate = item.CApplyDate,
CTravelStartTime = item.CTravelStartTime,
CTravelEndTime = item.CTravelEndTime,
CAmont = item.CAmont,
CReason = item.CReason,
CEmployeeId = item.CEmployeeId,
CCheckStatus = item.CCheckStatus
};
list.Add(traObj);
}
}
//Name有值
else if (string.IsNullOrEmpty(AppNum) && string.IsNullOrEmpty(Id) && !string.IsNullOrEmpty(Name))
{
var table = from travel in DB.TTravelExpenseApplications
join user in DB.TUsers on travel.CEmployeeId equals user.CEmployeeId
where travel.CDepartmentId == DepId &&
travel.CEmployeeId != 1 &&
user.CEmployeeName.Contains(Name)
orderby travel.CApplyDate descending
select new
{
user.CEmployeeName,
travel.CApplyDate,
travel.CEmployeeId,
travel.CAmont,
travel.CCheckStatus,
travel.CTravelStartTime,
travel.CTravelEndTime,
travel.CReason,
travel.CApplyNumber
};
foreach (var item in table)
{
Travel_Expense_ApplicationViewModel traObj = new Travel_Expense_ApplicationViewModel()
{
employeeName = item.CEmployeeName,
CApplyNumber = item.CApplyNumber,
CApplyDate = item.CApplyDate,
CTravelStartTime = item.CTravelStartTime,
CTravelEndTime = item.CTravelEndTime,
CAmont = item.CAmont,
CReason = item.CReason,
CEmployeeId = item.CEmployeeId,
CCheckStatus = item.CCheckStatus
};
list.Add(traObj);
}
}
//AppNum, Id有值
else if (!string.IsNullOrEmpty(AppNum) && !string.IsNullOrEmpty(Id) && string.IsNullOrEmpty(Name))
{
var table = from travel in DB.TTravelExpenseApplications
join user in DB.TUsers on travel.CEmployeeId equals user.CEmployeeId
where travel.CDepartmentId == DepId &&
travel.CEmployeeId != 1 &&
travel.CApplyNumber.ToString().Contains(AppNum) &&
travel.CEmployeeId.ToString().Contains(Id)
orderby travel.CApplyDate descending
select new
{
user.CEmployeeName,
travel.CApplyDate,
travel.CEmployeeId,
travel.CAmont,
travel.CCheckStatus,
travel.CTravelStartTime,
travel.CTravelEndTime,
travel.CReason,
travel.CApplyNumber
};
foreach (var item in table)
{
Travel_Expense_ApplicationViewModel traObj = new Travel_Expense_ApplicationViewModel()
{
employeeName = item.CEmployeeName,
CApplyNumber = item.CApplyNumber,
CApplyDate = item.CApplyDate,
CTravelStartTime = item.CTravelStartTime,
CTravelEndTime = item.CTravelEndTime,
CAmont = item.CAmont,
CReason = item.CReason,
CEmployeeId = item.CEmployeeId,
CCheckStatus = item.CCheckStatus
};
list.Add(traObj);
}
}
//Name, Id有值
else if (!string.IsNullOrEmpty(Id) && !string.IsNullOrEmpty(Name) && string.IsNullOrEmpty(AppNum))
{
var table = from travel in DB.TTravelExpenseApplications
join user in DB.TUsers on travel.CEmployeeId equals user.CEmployeeId
where travel.CDepartmentId == DepId &&
travel.CEmployeeId != 1 &&
travel.CEmployeeId.ToString().Contains(Id) &&
user.CEmployeeName.Contains(Name)
orderby travel.CApplyDate descending
select new
{
user.CEmployeeName,
travel.CApplyDate,
travel.CEmployeeId,
travel.CAmont,
travel.CCheckStatus,
travel.CTravelStartTime,
travel.CTravelEndTime,
travel.CReason,
travel.CApplyNumber
};
foreach (var item in table)
{
Travel_Expense_ApplicationViewModel traObj = new Travel_Expense_ApplicationViewModel()
{
employeeName = item.CEmployeeName,
CApplyNumber = item.CApplyNumber,
CApplyDate = item.CApplyDate,
CTravelStartTime = item.CTravelStartTime,
CTravelEndTime = item.CTravelEndTime,
CAmont = item.CAmont,
CReason = item.CReason,
CEmployeeId = item.CEmployeeId,
CCheckStatus = item.CCheckStatus
};
list.Add(traObj);
}
}
//Name, AppNum有值
else if (!string.IsNullOrEmpty(AppNum) && !string.IsNullOrEmpty(Name) && string.IsNullOrEmpty(Id))
{
var table = from travel in DB.TTravelExpenseApplications
join user in DB.TUsers on travel.CEmployeeId equals user.CEmployeeId
where travel.CDepartmentId == DepId &&
travel.CEmployeeId != 1 &&
travel.CApplyNumber.ToString().Contains(AppNum) &&
user.CEmployeeName.Contains(Name)
orderby travel.CApplyDate descending
select new
{
user.CEmployeeName,
travel.CApplyDate,
travel.CEmployeeId,
travel.CAmont,
travel.CCheckStatus,
travel.CTravelStartTime,
travel.CTravelEndTime,
travel.CReason,
travel.CApplyNumber
};
foreach (var item in table)
{
Travel_Expense_ApplicationViewModel traObj = new Travel_Expense_ApplicationViewModel()
{
employeeName = item.CEmployeeName,
CApplyNumber = item.CApplyNumber,
CApplyDate = item.CApplyDate,
CTravelStartTime = item.CTravelStartTime,
CTravelEndTime = item.CTravelEndTime,
CAmont = item.CAmont,
CReason = item.CReason,
CEmployeeId = item.CEmployeeId,
CCheckStatus = item.CCheckStatus
};
list.Add(traObj);
}
}
//全部有值
else if (!string.IsNullOrEmpty(AppNum) && !string.IsNullOrEmpty(Id) && !string.IsNullOrEmpty(Name))
{
var table = from travel in DB.TTravelExpenseApplications
join user in DB.TUsers on travel.CEmployeeId equals user.CEmployeeId
where travel.CDepartmentId == DepId &&
travel.CEmployeeId != 1 &&
travel.CApplyNumber.ToString().Contains(AppNum) &&
travel.CEmployeeId.ToString().Contains(Id) &&
user.CEmployeeName.Contains(Name)
orderby travel.CApplyDate descending
select new
{
user.CEmployeeName,
travel.CApplyDate,
travel.CEmployeeId,
travel.CAmont,
travel.CCheckStatus,
travel.CTravelStartTime,
travel.CTravelEndTime,
travel.CReason,
travel.CApplyNumber
};
foreach (var item in table)
{
Travel_Expense_ApplicationViewModel traObj = new Travel_Expense_ApplicationViewModel()
{
employeeName = item.CEmployeeName,
CApplyNumber = item.CApplyNumber,
CApplyDate = item.CApplyDate,
CTravelStartTime = item.CTravelStartTime,
CTravelEndTime = item.CTravelEndTime,
CAmont = item.CAmont,
CReason = item.CReason,
CEmployeeId = item.CEmployeeId,
CCheckStatus = item.CCheckStatus
};
list.Add(traObj);
}
}
//全部條件為空白
else if (AppNum == "" && Id == "" && Name == "")
{
var table = from travel in DB.TTravelExpenseApplications
join user in DB.TUsers on travel.CEmployeeId equals user.CEmployeeId
where travel.CDepartmentId == DepId &&
travel.CEmployeeId != 1
orderby travel.CApplyDate descending
select new
{
user.CEmployeeName,
travel.CApplyDate,
travel.CEmployeeId,
travel.CAmont,
travel.CCheckStatus,
travel.CTravelStartTime,
travel.CTravelEndTime,
travel.CReason,
travel.CApplyNumber
};
foreach (var item in table)
{
Travel_Expense_ApplicationViewModel traObj = new Travel_Expense_ApplicationViewModel()
{
employeeName = item.CEmployeeName,
CApplyNumber = item.CApplyNumber,
CApplyDate = item.CApplyDate,
CTravelStartTime = item.CTravelStartTime,
CTravelEndTime = item.CTravelEndTime,
CAmont = item.CAmont,
CReason = item.CReason,
CEmployeeId = item.CEmployeeId,
CCheckStatus = item.CCheckStatus
};
list.Add(traObj);
}
}
}
//全部條件為空白
else
{
var table = from travel in DB.TTravelExpenseApplications
join user in DB.TUsers
on travel.CEmployeeId equals user.CEmployeeId
where travel.CDepartmentId == DepId &&
travel.CEmployeeId != 1
orderby travel.CApplyDate descending
select new
{
user.CEmployeeName,
travel.CApplyDate,
travel.CEmployeeId,
travel.CAmont,
travel.CCheckStatus,
travel.CTravelStartTime,
travel.CTravelEndTime,
travel.CReason,
travel.CApplyNumber
};
foreach (var item in table)
{
Travel_Expense_ApplicationViewModel traObj = new Travel_Expense_ApplicationViewModel()
{
employeeName = item.CEmployeeName,
CApplyNumber = item.CApplyNumber,
CApplyDate = item.CApplyDate,
CTravelStartTime = item.CTravelStartTime,
CTravelEndTime = item.CTravelEndTime,
CAmont = item.CAmont,
CReason = item.CReason,
CEmployeeId = item.CEmployeeId,
CCheckStatus = item.CCheckStatus
};
list.Add(traObj);
}
}
return(View(list));
}
