public async Task <PaginatedList <SettlementView> > Settlement(string timestart, string timeend, string companyid, int page, int pageSize)
{
DateTime start = Method.StampToDateTime(timestart);
DateTime end = Method.StampToDateTime(timeend);
IQueryable <Company> cmps = Context.Set <Company>().Where(x => x.Id.Contains(companyid));
if (cmps.FirstOrDefault().ParentId.Trim() == "0" && cmps.Count() == 1)
{
cmps = Context.Set <Company>().Where(x => x.ParentId.Equals(companyid));
}
else
{
cmps = Context.Set <Company>().Where(x => x.Id.Contains(companyid) && x.ParentId.Trim() != "0");
}
var price = Convert.ToDouble(Context.Set <TbDictionary>().Where(x => x.DictionaryCode.Trim() == "X1000").FirstOrDefault().DictionaryName);
var dbitems = from cmp in cmps
orderby cmp.Sort ascending
select new SettlementView
{
DepartmentName = cmp.Name.Trim(),
CompanyName = Context.Set <Company>().Where(x => x.Id == cmp.ParentId).FirstOrDefault().Name.Trim(),
TotalPersonNumber = Context.Set <Employee>().Where(x => x.CompanyId.Trim().Equals(cmp.Id.Trim())).Count(),
PriceOfSettlement = price,
AmountOfSettlement = Context.Set <Employee>().Where(x => x.CompanyId.Trim().Equals(cmp.Id.Trim())).Count() * price,
ActualSettlementAmount = 0
};
var result = await PaginatedList <SettlementView> .CreatepagingAsync(dbitems.AsNoTracking(), page, pageSize);
return(result);
}
public async Task <PaginatedList <Rules> > Page(string searchString, int page, int pageSize)
{
var rules = Context.Set <Rules>().Where(x => x.Name.Contains(searchString)).OrderBy(y => y.Sort);
var rule = await PaginatedList <Rules> .CreatepagingAsync(rules.AsNoTracking(), page, pageSize);
return(rule);
}
public async Task <PaginatedList <Company> > DepartmentPage(string searchString, int page, int pageSize, string parentid)
{
var companys = Context.Set <Company>().Where(x => x.Name.Contains(searchString) && x.ParentId == parentid).OrderBy(y => y.Sort);
var company = await PaginatedList <Company> .CreatepagingAsync(companys.AsNoTracking(), page, pageSize);
return(company);
}
//后台主页
public async Task <IActionResult> Index(string sortOrder, string currentFilter, string searchString, int?page)
{
//从session中获取用户名和头像
ViewData["Name"] = HttpContext.Session.GetString(AdminName);
ViewData["Img"] = HttpContext.Session.GetString(AdminImg);
//排序
ViewData["CurrentSort"] = sortOrder;
ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
ViewData["DateSortParm"] = sortOrder == "Date" ? "date_desc" : "Date";
if (searchString != null)
{
page = 1;
}
else
{
searchString = currentFilter;
}
ViewData["CurrentFilter"] = searchString;
var blogs = from a in blogContext.Blogs
join b in blogContext.Tallys on a.Tally_Id equals b.Tally_Id
select new AdminHome
{
ID = a.Blog_Id,
Title = a.Blog_Title,
Time = a.Blog_Time,
Digest = a.Blog_Digest,
TallyName = b.Tally_Name
};
if (!String.IsNullOrEmpty(searchString))
{
blogs = blogs.Where(s => s.Title.Contains(searchString));
}
switch (sortOrder)
{
case "name_desc":
blogs = blogs.OrderByDescending(s => s.Title);
break;
case "Date":
blogs = blogs.OrderBy(s => s.Time);
break;
case "date_desc":
blogs = blogs.OrderByDescending(s => s.Time);
break;
default:
blogs = blogs.OrderBy(s => s.Title);
break;
}
int pageSize = 6;
return(View(await PaginatedList <AdminHome> .CreatepagingAsync(blogs.AsNoTracking(), page ?? 1, pageSize)));
}
public async Task <PaginatedList <EmployeeCountAneNum> > PageCountNum(string companyid, string employeename, string jobid, int page, int pageSize)
{
var employees = Context.Set <Employee>().Where(x => x.Name.Contains(employeename) && x.JobId.Contains(jobid)).OrderBy(y => y.Sort);
IQueryable <Company> cmps = Context.Set <Company>().Where(x => x.Id.Contains(companyid));
if (cmps.FirstOrDefault().ParentId.Trim() == "0" && cmps.Count() == 1)
{
cmps = Context.Set <Company>().Where(x => x.ParentId.Equals(companyid));
}
else
{
cmps = Context.Set <Company>().Where(x => x.Id.Contains(companyid) && x.ParentId.Trim() != "0");
}
var dbitems = from emp in employees
join company in cmps
on emp.CompanyId.Trim() equals company.Id.Trim()
// where emp.CompanyId.Trim().Contains(companyid.Trim())
select new EmployeeCountAneNum
{
jobId = emp.JobId.Trim(),
employeeName = emp.Name.Trim(),
departmentName = company.Name.Trim(),
departmentId = company.Id.Trim(),
conpanyId = company.ParentId.Trim(),
companyName = Context.Set <Company>().Where(x => x.Id == company.ParentId).FirstOrDefault().Name.Trim(),
availableCount = emp.AvailableCount,
availableMoney = emp.AvailableMoney
};
var Employee = await PaginatedList <EmployeeCountAneNum> .CreatepagingAsync(dbitems.AsNoTracking(), page, pageSize);
return(Employee);
}
public async Task <PaginatedList <EmployeeInfo> > Page(string companyid, string employeename, string jobid, int page, int pageSize)
{
var employees = Context.Set <Employee>().Where(x => x.Name.Contains(employeename) && x.JobId.Contains(jobid)).OrderBy(y => y.Sort);
IQueryable <Company> cmps = Context.Set <Company>().Where(x => x.Id.Contains(companyid));
if (cmps.FirstOrDefault().ParentId.Trim() == "0" && cmps.Count() == 1)
{
cmps = Context.Set <Company>().Where(x => x.ParentId.Equals(companyid));
}
else
{
cmps = Context.Set <Company>().Where(x => x.Id.Contains(companyid) && x.ParentId.Trim() != "0");
}
var dbitems = from emp in employees
join company in cmps
on emp.CompanyId.Trim() equals company.Id.Trim()
// where emp.CompanyId.Trim().Contains(companyid.Trim())
select new EmployeeInfo
{
sort = emp.Sort,
name = emp.Name.Trim(),
jobId = emp.JobId.Trim(),
numbers = emp.Numbers,
companyName = company.Name.Trim(),
companyId = company.Id.Trim(),
remarks = emp.Remarks.Trim(),
password = emp.Password.Trim(),
qrCode = emp.QrCodeNumber.Trim()
};
var Employee = await PaginatedList <EmployeeInfo> .CreatepagingAsync(dbitems.AsNoTracking(), page, pageSize);
return(Employee);
}
public async Task <PaginatedList <EmployeeRecoreDetail> > RecordDetail(string jobid, string timestart, string timeend, string usagetype, string consumptiontype, int page, int pageSize)
{
DateTime start = string.IsNullOrEmpty(timestart) ? DateTime.Now.AddDays(-DateTime.Now.DayOfYear + 1) : Method.StampToDateTime(timestart);
DateTime end = string.IsNullOrEmpty(timeend) ? DateTime.Now.AddYears(1).AddDays(-DateTime.Now.AddYears(1).DayOfYear) : Method.StampToDateTime(timeend);
var dictionary = Context.Set <TbDictionary>().Where(x => x.ParentDictionaryCode == "A1000" || x.ParentDictionaryCode == "A5000" || x.ParentDictionaryCode == "A4000");
var employee = await Context.Set <Employee>().Where(x => x.JobId == jobid).FirstOrDefaultAsync();
var department = await Context.Set <Company>().Where(x => x.Id == employee.CompanyId).FirstOrDefaultAsync();
var company = await Context.Set <Company>().Where(x => x.Id == department.ParentId).FirstOrDefaultAsync();
var givedbitems = from give in Context.Vw_GiveRecords //Context.Vw_GiveRecords // join use in Context.Set<Vw_UseRecord>()
where give.JobId.Trim() == jobid.Trim()
where start <= give.AddAt && give.AddAt <= end
where string.IsNullOrEmpty(usagetype) || usagetype.Trim() == give.UseType.Trim() //A100001次数 A100002钱包
where string.IsNullOrEmpty(consumptiontype) || give.GiveType == consumptiontype //A600001发放 A600002消费
orderby give.AddAt descending
select new EmployeeRecoreDetail
{
CompanyName = company.Name.Trim(),
CompanyId = company.Id.Trim(),
DepartmentName = department.Name.Trim(),
DepartmentId = department.Id.Trim(),
JobId = employee.JobId.Trim(),
EmployeeName = employee.Name.Trim(),
AddAt = give.AddAt,
GiveCountType = dictionary.Where(x => x.DictionaryCode == give.GiveMoneyOrNumType).FirstOrDefault().DictionaryName,
UsageType = give.UseType,
ConsumptionType = dictionary.Where(x => x.DictionaryCode == give.GiveType).FirstOrDefault().DictionaryName,
UseMoney = give.GiveType.Equals("A100002") ? give.GiveMoneyOrNum : 0,
UseNumber = give.GiveType.Equals("A100001") ? give.GiveMoneyOrNum : 0
};
var usedbitems = from use in Context.Vw_UseRecords //Context.Vw_GiveRecords // join use in Context.Set<Vw_UseRecord>()
where use.JobId.Trim() == jobid.Trim()
where start <= use.AddAt && use.AddAt <= end
where string.IsNullOrEmpty(usagetype) || usagetype.Trim() == use.UseType.Trim() //A100001次数 A100002钱包
where string.IsNullOrEmpty(consumptiontype) || use.GiveCountType == consumptiontype //A600001发放 A600002消费
orderby use.AddAt descending
select new EmployeeRecoreDetail
{
CompanyName = company.Name.Trim(),
CompanyId = company.Id.Trim(),
DepartmentName = department.Name.Trim(),
DepartmentId = department.Id.Trim(),
JobId = employee.JobId.Trim(),
EmployeeName = employee.Name.Trim(),
AddAt = use.AddAt,
GiveCountType = null,
UsageType = use.UseType,
ConsumptionType = dictionary.Where(x => x.DictionaryCode == use.GiveCountType).FirstOrDefault().DictionaryName,
UseMoney = use.UseMoney,
UseNumber = use.UseNumber
};
var items = givedbitems.Union(usedbitems);
var Employee = await PaginatedList <EmployeeRecoreDetail> .CreatepagingAsync(items.AsNoTracking(), page, pageSize);
return(Employee);
}
//后台主页
public async Task <IActionResult> Index(string sortOrder, string currentFilter, string searchString, int?page)
{
//排序
ViewData["CurrentSort"] = sortOrder;
ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
ViewData["DateSortParm"] = sortOrder == "Date" ? "date_desc" : "Date";
if (searchString != null)
{
page = 1;
}
else
{
searchString = currentFilter;
}
ViewData["CurrentFilter"] = searchString;
var contacts = from a in blogContext.Contacts
select a;
if (!String.IsNullOrEmpty(searchString))
{
contacts = contacts.Where(s => s.Name.Contains(searchString));
}
switch (sortOrder)
{
case "name_desc":
contacts = contacts.OrderByDescending(s => s.Name);
break;
case "Date":
contacts = contacts.OrderBy(s => s.Time);
break;
case "date_desc":
contacts = contacts.OrderByDescending(s => s.Time);
break;
default:
contacts = contacts.OrderBy(s => s.Name);
break;
}
int pageSize = 6;
return(View(await PaginatedList <Contact> .CreatepagingAsync(contacts.AsNoTracking(), page ?? 1, pageSize)));
}
//展示所有标签
public async Task <IActionResult> Index(string sortOrder, string currentFilter, string searchString, int?page)
{
ViewData["CurrentSort"] = sortOrder;
if (searchString != null)
{
page = 1;
}
else
{
searchString = currentFilter;
}
ViewData["CurrentFilter"] = searchString;
var tallys = from Tally in blogContext.Tallys select Tally;
int pageSize = 8;
return(View(await PaginatedList <Tally> .CreatepagingAsync(tallys.AsNoTracking(), page ?? 1, pageSize)));
}
//首页
public async Task <IActionResult> Index(string blogtitle, string sortOrder, string currentFilter, string searchString, int?page)
{
MenuList();
ViewData["tag"] = "博客";
ViewData["CurrentSort"] = sortOrder;
//搜索的关键字
ViewData["blogtitle"] = blogtitle;
if (searchString != null)
{
page = 1;
}
else
{
searchString = currentFilter;
}
ViewData["CurrentFilter"] = searchString;
var blogs = (from a in blogContext.Blogs
join c in blogContext.Tallys on a.Tally_Id equals c.Tally_Id
select new HomeBlog
{
Title = a.Blog_Title,
dateTime = a.Blog_Time,
BlogImg = a.Blog_Img,
tallyName = c.Tally_Name,
Digest = a.Blog_Digest,
Id = a.Blog_Id
});
//模糊搜索
if (!string.IsNullOrWhiteSpace(blogtitle))
{
blogs = blogs.Where(a => a.Title.Contains(blogtitle));
}
//每页显示数量
int pageSize = 4;
return(View(await PaginatedList <HomeBlog> .CreatepagingAsync(blogs.AsNoTracking(), page ?? 1, pageSize)));
}
public async Task <PaginatedList <EmployeeMoneyRecord> > GiveMoneyRecord(string jobid, string timestart, string timeend, string empname, string companyid, int page, int pageSize)
{
DateTime start = string.IsNullOrEmpty(timestart) ? DateTime.Now.AddDays(-DateTime.Now.DayOfYear + 1) : Method.StampToDateTime(timestart);
DateTime end = string.IsNullOrEmpty(timeend) ? DateTime.Now.AddYears(1).AddDays(-DateTime.Now.AddYears(1).DayOfYear) : Method.StampToDateTime(timeend);
var dictionary = Context.Set <TbDictionary>().Where(x => x.ParentDictionaryCode == "A5000");
var employees = Context.Set <Employee>().Where(x => x.Name.Contains(empname) && x.JobId.Contains(jobid));
IQueryable <Company> cmps = Context.Set <Company>().Where(x => x.Id.Contains(companyid));
if (cmps.FirstOrDefault().ParentId.Trim() == "0" && cmps.Count() == 1)
{
cmps = Context.Set <Company>().Where(x => x.ParentId.Equals(companyid));
}
else
{
cmps = Context.Set <Company>().Where(x => x.Id.Contains(companyid) && x.ParentId.Trim() != "0");
}
var dbitems = from emp in employees
join company in cmps
on emp.CompanyId.Trim() equals company.Id.Trim()
join givecount in Context.Set <GiveMoney>()
on emp.JobId.Trim() equals givecount.JobId
// where emp.CompanyId.Trim().Contains(companyid.Trim())
where start <= givecount.AddAt && givecount.AddAt <= end
orderby givecount.AddAt descending
select new EmployeeMoneyRecord
{
jobId = emp.JobId.Trim(),
name = emp.Name.Trim(),
departmentName = company.Name.Trim(),
departmentId = company.Id.Trim(),
conpanyId = company.ParentId.Trim(),
companyName = Context.Set <Company>().Where(x => x.Id == company.ParentId).FirstOrDefault().Name.Trim(),
giveTime = givecount.AddAt,
giveMoney = givecount.GiveMoney1,
giveType = dictionary.Where(x => x.DictionaryCode == givecount.GiveMoneyType).FirstOrDefault().DictionaryName
};
var Employee = await PaginatedList <EmployeeMoneyRecord> .CreatepagingAsync(dbitems.AsNoTracking(), page, pageSize);
return(Employee);
}
// GET: Students
public async Task <IActionResult> Index(string sortOrder, string searchStudent, int?page, string currentStudent)
{
#region 搜索和排序
//姓名的排序参数
ViewData["Name_Sort_Parm"] = string.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
//时间的排序参数
ViewData["Date_Sort_Parm"] = sortOrder == "Date" ? "date_desc" : "Date";
//搜索的关键字
ViewData["SearchStudent"] = searchStudent;
#endregion
ViewData["CurrentSort"] = sortOrder;
if (searchStudent != null)
{
page = 1;
}
else
{
searchStudent = currentStudent;
}
#region 搜索和排序功能
var students = from student in _context.Students select student;
if (!string.IsNullOrWhiteSpace(searchStudent))
{
students = students.Where(a => a.RealName.Contains(searchStudent));
}
switch (sortOrder)
{
case "name_desc":
students = students.OrderByDescending(a => a.RealName);
break;
case "Date":
students = students.OrderBy(a => a.EnrollmentDate);
break;
case "date_desc":
students = students.OrderByDescending(a => a.EnrollmentDate);
break;
default:
students = students.OrderBy(a => a.RealName);
break;
}
#endregion
var pageSize = 5;
var entities = students.AsNoTracking();
var dtos = await PaginatedList <Student> .CreatepagingAsync(entities, page ?? 1, pageSize);
return(View(dtos));
}
public async Task <PaginatedList <PersonPayoffTableView> > PersonPayoffTable(string timestart, string timeend, string companyid, string employeename, int page, int pageSize)
{
DateTime start = string.IsNullOrEmpty(timestart) ? DateTime.Now.AddDays(-DateTime.Now.DayOfYear + 1) : Method.StampToDateTime(timestart);
DateTime end = string.IsNullOrEmpty(timeend) ? DateTime.Now.AddYears(1).AddDays(-DateTime.Now.AddYears(1).DayOfYear) : Method.StampToDateTime(timeend);
var employees = Context.Set <Employee>().Where(x => x.Name.Contains(employeename)).OrderBy(y => y.Sort);
var records = (from emp in employees
join ur in Context.Vw_UseRecords
on emp.JobId.Trim() equals ur.JobId.Trim()
from rule in Context.Set <Rules>()
where start <= ur.AddAt && ur.AddAt <= end
where Method.isLegalTime(Convert.ToDateTime(ur.AddAt), rule.StartTime + "-" + rule.EndTime)
select new
{
recordId = ur.UseRecordId,
useNumber = ur.UseNumber,
useMoney = ur.UseMoney,
ruleId = rule.Id,
ruleName = rule.Name,
rulePrice = rule.Price,
jobId = emp.JobId,
type = ur.GiveCountType
}).ToList();
IQueryable <Company> cmps = Context.Set <Company>().Where(x => x.Id.Contains(companyid));
if (cmps.FirstOrDefault().ParentId.Trim() == "0" && cmps.Count() == 1)
{
cmps = Context.Set <Company>().Where(x => x.ParentId.Equals(companyid));
}
else
{
cmps = Context.Set <Company>().Where(x => x.Id.Contains(companyid) && x.ParentId.Trim() != "0");
}
//breakfast
var breakfastId = Context.Set <Rules>().Where(x => x.Name.Trim().Equals("早餐")).FirstOrDefault().Id;
//lunch
var lunchId = Context.Set <Rules>().Where(x => x.Name.Trim().Equals("午餐")).FirstOrDefault().Id;
//dinner
var dinnerId = Context.Set <Rules>().Where(x => x.Name.Trim().Equals("晚餐")).FirstOrDefault().Id;
//snake
var snakeId = Context.Set <Rules>().Where(x => x.Name.Trim().Equals("夜宵")).FirstOrDefault().Id;
var dbitems = from emp in employees
join company in cmps
on emp.CompanyId.Trim() equals company.Id.Trim()
orderby company.Sort ascending
orderby emp.Sort ascending
select new PersonPayoffTableView
{
DepartmentName = company.Name.Trim(),
CompanyName = Context.Set <Company>().Where(x => x.Id == company.ParentId).FirstOrDefault().Name.Trim(),
EmployeeName = emp.Name,
NumberOfBreakfast = records.Where(x => x.jobId.Trim().Equals(emp.JobId.Trim()) && x.ruleId.Equals(breakfastId) && x.type.Trim().Equals("A100001")).Sum(x => x.useNumber),
MoneyOfBreakfast = records.Where(x => x.jobId.Trim().Equals(emp.JobId.Trim()) && x.ruleId.Equals(breakfastId) && x.type.Trim().Equals("A100001")).Sum(x => x.useNumber * x.rulePrice),
NumberOfLunch = records.Where(x => x.jobId.Trim().Equals(emp.JobId.Trim()) && x.ruleId.Equals(lunchId) && x.type.Trim().Equals("A100001")).Sum(x => x.useNumber),
MoneyOfLunch = records.Where(x => x.jobId.Trim().Equals(emp.JobId.Trim()) && x.ruleId.Equals(lunchId) && x.type.Trim().Equals("A100001")).Sum(x => x.useNumber * x.rulePrice),
NumberOfDinner = records.Where(x => x.jobId.Trim().Equals(emp.JobId.Trim()) && x.ruleId.Equals(dinnerId) && x.type.Trim().Equals("A100001")).Sum(x => x.useNumber),
MoneyOfDinner = records.Where(x => x.jobId.Trim().Equals(emp.JobId.Trim()) && x.ruleId.Equals(dinnerId) && x.type.Trim().Equals("A100001")).Sum(x => x.useNumber * x.rulePrice),
NumberOfMidnightsnack = records.Where(x => x.jobId.Trim().Equals(emp.JobId.Trim()) && x.ruleId.Equals(snakeId) && x.type.Trim().Equals("A100001")).Sum(x => x.useNumber),
MoneyOfMidnightsnack = records.Where(x => x.jobId.Trim().Equals(emp.JobId.Trim()) && x.ruleId.Equals(snakeId) && x.type.Trim().Equals("A100001")).Sum(x => x.useNumber * x.rulePrice),
OtherBreakfastCount = records.Where(x => x.jobId.Trim().Equals(emp.JobId.Trim()) && x.ruleId.Equals(breakfastId) && x.type.Trim().Equals("A100002")).Count(),
OtherLunchCount = records.Where(x => x.jobId.Trim().Equals(emp.JobId.Trim()) && x.ruleId.Equals(lunchId) && x.type.Trim().Equals("A100002")).Count(),
OtherDinnerCount = records.Where(x => x.jobId.Trim().Equals(emp.JobId.Trim()) && x.ruleId.Equals(dinnerId) && x.type.Trim().Equals("A100002")).Count(),
OtherSnackCount = records.Where(x => x.jobId.Trim().Equals(emp.JobId.Trim()) && x.ruleId.Equals(snakeId) && x.type.Trim().Equals("A100002")).Count(),
OtherBreakfastMoney = records.Where(x => x.jobId.Trim().Equals(emp.JobId.Trim()) && x.ruleId.Equals(breakfastId) && x.type.Trim().Equals("A100002")).Sum(x => x.useMoney),
OtherLunchMoney = records.Where(x => x.jobId.Trim().Equals(emp.JobId.Trim()) && x.ruleId.Equals(lunchId) && x.type.Trim().Equals("A100002")).Sum(x => x.useMoney),
OtherDinnerMoney = records.Where(x => x.jobId.Trim().Equals(emp.JobId.Trim()) && x.ruleId.Equals(dinnerId) && x.type.Trim().Equals("A100002")).Sum(x => x.useMoney),
OtherSnackMoney = records.Where(x => x.jobId.Trim().Equals(emp.JobId.Trim()) && x.ruleId.Equals(snakeId) && x.type.Trim().Equals("A100002")).Sum(x => x.useMoney),
TotalNumber = 0,
TotalMoney = 0
};
var result = await PaginatedList <PersonPayoffTableView> .CreatepagingAsync(dbitems.AsNoTracking(), page, pageSize);
return(result);
}
// GET: Students
/// <summary>
///
/// </summary>
/// <param name="sortOrder">排序字段</param>
/// <param name="searchStudent">搜索参数</param>
/// <param name="page">第几页</param>
/// <param name="currentStudent">当前学生参数</param>
/// <returns></returns>
public async Task <IActionResult> Index(string sortOrder, string searchStudent, int?page, string currentStudent)
{
#region 参数
// 姓名排序参数
ViewData["Name_Sort_Parm"] = string.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
// 时间排序参数
ViewData["Date_Sort_Parm"] = sortOrder == "Date" ? "date_desc" : "Date";
// 搜索关键字
ViewData["SearchStudent"] = searchStudent;
ViewData["CurrentSort"] = sortOrder;
#endregion
var students = from student in _context.Students select student;
// 当前排序得关键字
// 当前过滤页得参数
if (!string.IsNullOrEmpty(searchStudent))
{
page = 1;
}
else
{
searchStudent = currentStudent;
}
#region 排序搜索功能
#region 搜索
if (!string.IsNullOrWhiteSpace(searchStudent))
{
// Contains 相当于模糊查询 sql中的 like
students = students.Where(a => a.RealName.Contains(searchStudent));
}
#endregion
#region 排序
switch (sortOrder)
{
case "name_desc":
students = students.OrderByDescending(a => a.RealName);
break;
case "Date":
students = students.OrderBy(a => a.EnrollmentDate);
break;
case "date_desc":
students = students.OrderByDescending(a => a.EnrollmentDate);
break;
default:
students = students.OrderBy(a => a.RealName);
break;
}
#endregion
//var dtos = await students.AsNoTracking().ToListAsync();
#endregion
#region 分页 这块更排序和搜索功能一起用有问题 紧紧参考代码逻辑就好
int pageSize = 3;
var entities = students.AsNoTracking();
var dtos = await PaginatedList <Student> .CreatepagingAsync(entities, page ?? 1, pageSize);
#endregion
return(View(dtos));
}
// GET: Students
public async Task <IActionResult> Index(string sortOrder, string searchStudent, int?page, string currentStudent)//首先看传过来的值，排序是否有值
{
#region 搜索和排序
//ViewData DataView表示用于排序、筛选、搜索、编辑和导航的 DataTable 的可绑定数据的自定义视图。
//姓名的排序参数
ViewData["Name_Sort_Parm"] = string.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
//时间的排序参数
ViewData["Date_Sort_Parm"] = sortOrder == "Date" ? "date_desc" : "Date";
//搜索的关键字
ViewData["SearchStudent"] = searchStudent;
//当前排序的参数
//当前过滤页的参数
#endregion
ViewData["CurrentSort"] = sortOrder;
if (searchStudent != null)
{
page = 1;
}
else
{
searchStudent = currentStudent;
}
#region 搜索和排序功能
var students = from student in _context.Students select student;
if (!string.IsNullOrWhiteSpace(searchStudent))
{
students = students.Where(a => a.RealName.Contains(searchStudent));//Contains模糊查询相当于sql 的likeS
}
switch (sortOrder)
{
case "name_desc":
students = students.OrderByDescending(a => a.RealName); //倒序
break;
case "Date":
students = students.OrderBy(a => a.EnrollmentDate); //正序
break;
case "date_desc":
students = students.OrderByDescending(a => a.EnrollmentDate);
break;
default:
students = students.OrderBy(a => a.RealName);
break;
}
#endregion
var pageSize = 4;//当前页为4页
var entities = students.AsNoTracking();
//将当前的页传过去，如果当前页为1，再把页传给他 //先将当前数据库实体传过去，在将当前页传过去，在将每一页有多少分页值
var dtos = await PaginatedList <Student> .CreatepagingAsync(entities, page ?? 1, pageSize);
return(View(dtos));
}
public async Task <PaginatedList <TopUpView> > PersonTopUpTable(string timestart, string timeend, string companyid, string employeename, int page, int pageSize)
{
DateTime start = string.IsNullOrEmpty(timestart) ? DateTime.Now.AddDays(-DateTime.Now.DayOfYear + 1) : Method.StampToDateTime(timestart);
DateTime end = string.IsNullOrEmpty(timeend) ? DateTime.Now.AddYears(1).AddDays(-DateTime.Now.AddYears(1).DayOfYear) : Method.StampToDateTime(timeend);
// DateTime dt = DateTime.Now;
// //本月第一天时间
// DateTime dt_First = dt.AddDays(1 - (dt.Day));
// //获得某年某月的天数
// int year = dt.Date.Year;
// int month = dt.Date.Month;
// int dayCount = DateTime.DaysInMonth(year, month);
// //本月最后一天时间
// DateTime dt_Last = dt_First.AddDays(dayCount - 1);
var employees = Context.Set <Employee>().Where(x => x.Name.Contains(employeename)).OrderBy(y => y.Sort);
IQueryable <Company> cmps = Context.Set <Company>().Where(x => x.Id.Contains(companyid));
if (cmps.FirstOrDefault().ParentId.Trim() == "0" && cmps.Count() == 1)
{
cmps = Context.Set <Company>().Where(x => x.ParentId.Equals(companyid));
}
else
{
cmps = Context.Set <Company>().Where(x => x.Id.Contains(companyid) && x.ParentId.Trim() != "0");
}
var countToMoney = Convert.ToInt32(Context.Set <TbDictionary>().Where(x => x.DictionaryCode.Trim() == "A300001").FirstOrDefault().DictionaryName);
//breakfast
var breakfastId = Context.Set <Rules>().Where(x => x.Name.Trim().Equals("早餐")).FirstOrDefault().Id;
//lunch
var lunchId = Context.Set <Rules>().Where(x => x.Name.Trim().Equals("午餐")).FirstOrDefault().Id;
//dinner
var dinnerId = Context.Set <Rules>().Where(x => x.Name.Trim().Equals("晚餐")).FirstOrDefault().Id;
//snake
var snakeId = Context.Set <Rules>().Where(x => x.Name.Trim().Equals("夜宵")).FirstOrDefault().Id;
var dbGive = (from emp in employees
join give in Context.Vw_GiveRecords
on emp.JobId.Trim() equals give.JobId.Trim()
where start <= give.AddAt && give.AddAt <= end
select give).ToList();
var dbRecord = (from emp in employees
join ur in Context.Vw_UseRecords
on emp.JobId.Trim() equals ur.JobId.Trim()
from rule in Context.Set <Rules>()
where start <= ur.AddAt && ur.AddAt <= end
where Method.isLegalTime(Convert.ToDateTime(ur.AddAt), rule.StartTime + "-" + rule.EndTime)
select new
{
recordId = ur.UseRecordId,
useNumber = ur.UseNumber,
ruleId = rule.Id,
ruleName = rule.Name,
jobId = emp.JobId,
type = ur.GiveCountType
}).ToList();
var dbitems = from emp in employees
join company in cmps
on emp.CompanyId.Trim() equals company.Id.Trim()
orderby company.Sort ascending
orderby emp.Sort ascending
select new TopUpView
{
DepartmentName = company.Name.Trim(),
CompanyName = Context.Set <Company>().Where(x => x.Id == company.ParentId).FirstOrDefault().Name.Trim(),
EmployeeName = emp.Name,
LastMonthCarryNumber = dbRecord.Where(x => x.jobId.Trim().Equals(emp.JobId.Trim()) && x.type.Trim().Equals("A100003")).Sum(x => x.useNumber),
LastMonthCarryMoney = dbGive.Where(x => x.JobId.Trim().Equals(emp.JobId.Trim()) && x.GiveMoneyOrNumType.Trim().Equals("A500001")).Sum(x => x.GiveMoneyOrNum),
TopUpNumber = dbGive.Where(x => x.JobId.Trim().Equals(emp.JobId.Trim()) && x.GiveMoneyOrNumType.Trim().Equals("A400003")).Sum(x => x.GiveMoneyOrNum),
SubsidiesMoney = 0, //dbGive.Where(x => x.JobId.Trim().Equals(emp.JobId.Trim()) && x.GiveMoneyOrNumType.Trim().Equals("A500002")).Sum(x => x.GiveMoneyOrNum),
DeductionsBreakfast = dbRecord.Where(x => x.jobId.Trim().Equals(emp.JobId.Trim()) && x.ruleId.Equals(breakfastId) && x.type.Trim().Equals("A100001")).Sum(x => x.useNumber),
DeductionsLunch = dbRecord.Where(x => x.jobId.Trim().Equals(emp.JobId.Trim()) && x.ruleId.Equals(lunchId) && x.type.Trim().Equals("A100001")).Sum(x => x.useNumber),
DeductionsDinner = dbRecord.Where(x => x.jobId.Trim().Equals(emp.JobId.Trim()) && x.ruleId.Equals(dinnerId) && x.type.Trim().Equals("A100001")).Sum(x => x.useNumber),
DeductionsSnack = dbRecord.Where(x => x.jobId.Trim().Equals(emp.JobId.Trim()) && x.ruleId.Equals(snakeId) && x.type.Trim().Equals("A100001")).Sum(x => x.useNumber),
TotalDeductions = dbRecord.Where(x => x.jobId.Trim().Equals(emp.JobId.Trim()) && x.type.Trim().Equals("A100001")).Sum(x => x.useNumber),
ThisMonthCarryNumber = emp.AvailableCount,
ThisMonthCarryMoney = emp.AvailableCount * countToMoney
};
var result = await PaginatedList <TopUpView> .CreatepagingAsync(dbitems.AsNoTracking(), page, pageSize);
return(result);
}
public async Task <PaginatedList <CompanyPayoffTableView> > CompanyPayoffTable(string timestart, string timeend, string companyid, int page, int pageSize)
{
DateTime start = Method.StampToDateTime(timestart);
DateTime end = Method.StampToDateTime(timeend);
IQueryable <Company> cmps = Context.Set <Company>().Where(x => x.Id.Contains(companyid));
if (cmps.FirstOrDefault().ParentId.Trim() == "0" && cmps.Count() == 1)
{
cmps = Context.Set <Company>().Where(x => x.ParentId.Equals(companyid));
}
else
{
cmps = Context.Set <Company>().Where(x => x.Id.Contains(companyid) && x.ParentId.Trim() != "0");
}
var records = (from ur in Context.Vw_UseRecords
from rule in Context.Set <Rules>()
from cmp in cmps
join emp in Context.Set <Employee>()
on cmp.Id.Trim() equals emp.CompanyId.Trim()
where ur.JobId.Trim() == emp.JobId.Trim()
where start <= ur.AddAt && ur.AddAt <= end
where ur.GiveCountType.Trim() == "A100001"
where Method.isLegalTime(Convert.ToDateTime(ur.AddAt), rule.StartTime + "-" + rule.EndTime)
select new
{
recordId = ur.UseRecordId,
useNumber = ur.UseNumber,
useMoney = ur.UseMoney,
ruleId = rule.Id,
ruleName = rule.Name,
rulePrice = rule.Price,
companyId = cmp.Id,
}).ToList();
var breakfastId = Context.Set <Rules>().Where(x => x.Name.Trim().Equals("早餐")).FirstOrDefault().Id;
//lunch
var lunchId = Context.Set <Rules>().Where(x => x.Name.Trim().Equals("午餐")).FirstOrDefault().Id;
//dinner
var dinnerId = Context.Set <Rules>().Where(x => x.Name.Trim().Equals("晚餐")).FirstOrDefault().Id;
//snake
var snakeId = Context.Set <Rules>().Where(x => x.Name.Trim().Equals("夜宵")).FirstOrDefault().Id;
var dbitems = from cmp in cmps
orderby cmp.Sort ascending
select new CompanyPayoffTableView
{
DepartmentName = cmp.Name.Trim(),
CompanyName = Context.Set <Company>().Where(x => x.Id == cmp.ParentId).FirstOrDefault().Name.Trim(),
NumberOfBreakfast = records.Where(x => x.companyId.Trim().Equals(cmp.Id.Trim()) && x.ruleId.Equals(breakfastId)).Sum(x => x.useNumber),
BreakfastPrice = Context.Set <Rules>().Where(x => x.Id.Equals(breakfastId)).FirstOrDefault().Price,
UseMoneyOfBreakfast = records.Where(x => x.companyId.Trim().Equals(cmp.Id.Trim()) && x.ruleId.Equals(breakfastId)).Sum(x => x.useNumber * x.rulePrice),
NumberOfLunch = records.Where(x => x.companyId.Trim().Equals(cmp.Id.Trim()) && x.ruleId.Equals(lunchId)).Sum(x => x.useNumber),
LunchPrice = Context.Set <Rules>().Where(x => x.Id.Equals(lunchId)).FirstOrDefault().Price,
UseMoneyOfLunch = records.Where(x => x.companyId.Trim().Equals(cmp.Id.Trim()) && x.ruleId.Equals(lunchId)).Sum(x => x.useNumber * x.rulePrice),
NumberOfDinner = records.Where(x => x.companyId.Trim().Equals(cmp.Id.Trim()) && x.ruleId.Equals(dinnerId)).Sum(x => x.useNumber),
DinnerPrice = Context.Set <Rules>().Where(x => x.Id.Equals(dinnerId)).FirstOrDefault().Price,
UseMoneyOfDinner = records.Where(x => x.companyId.Trim().Equals(cmp.Id.Trim()) && x.ruleId.Equals(dinnerId)).Sum(x => x.useNumber * x.rulePrice),
NumberOfMidnightsnack = records.Where(x => x.companyId.Trim().Equals(cmp.Id.Trim()) && x.ruleId.Equals(snakeId)).Sum(x => x.useNumber),
MidnightsnackPrice = Context.Set <Rules>().Where(x => x.Id.Equals(snakeId)).FirstOrDefault().Price,
UseMoneyOfMidnightsnack = records.Where(x => x.companyId.Trim().Equals(cmp.Id.Trim()) && x.ruleId.Equals(snakeId)).Sum(x => x.useNumber * x.rulePrice),
AmountPayable = 0,
ActualAmount = 0,
Remarks = ""
};
var result = await PaginatedList <CompanyPayoffTableView> .CreatepagingAsync(dbitems.AsNoTracking(), page, pageSize);
return(result);
}
