/// <summary>
/// 动态生成表格的数据
/// </summary>
/// <param name="page"></param>
/// <param name="rows"></param>
/// <returns></returns>
public ActionResult GetJson(int?page, int?rows)
{
try
{
page = page == null ? 1 : page; //第几页
rows = rows == null ? 10 : rows; //行数
try
{
//后台接受前台数据是一个对象，所以先要实例化Tostring()
EndDate = Request.Form["EndDate"].ToString() == string.Empty ? EndDate : Request.Form["EndDate"].ToString();
StartDate = Request.Form["StartDate"].ToString() == string.Empty ? StartDate : Request.Form["StartDate"].ToString();
Queryid = Request.Form["Queryid"].ToString() == string.Empty ? Queryid : Request.Form["Queryid"].ToString();
SelectVal = Request.Form["SelectVal"].ToString() == string.Empty ? SelectVal : Request.Form["SelectVal"].ToString();
InputVals = Request.Form["inputVals"].ToString() == string.Empty ? InputVals : Request.Form["inputVals"].ToString();
shopID = Request.Form["shopID"].ToString() == string.Empty ? "" : Request.Form["shopID"].ToString();
}
catch (Exception ex)
{
return(Json(data: new { exs = ex.Message }));
}
StaffInfoparam staffInfoparam = new StaffInfoparam()
{
_endDate = DateTime.Parse(EndDate), //结束时间
_startDate = DateTime.Parse(StartDate), //起始时间
_queryName = Queryid, //时间查询字段
_rows = (int)rows, //行数
_page = (int)page, //页数
_total = 0, //总行数
_fileName = "", //导出文件名
_selectVal = SelectVal, //输入查询字段
_inputVals = InputVals, //用户信息
_shopID = shopID // 门店ID
};
// return Json(data: new { shopids = staffInfoparam._shopID }); //??? 单独测试，存在数据
var json = helpStaffs.Contaion(_hostingEnvironment, staffInfoparam, export);
return(Json(new { rows = json, total = staffInfoparam._total }));
}
catch (Exception ex)
{
return(Json(ResultMsg.FormatResult(ex)));
}
}
/// <summary>
/// 导出数据
/// </summary>
/// <returns></returns>
public IActionResult GetExcel()
{
export = Request.Form["export"];
StaffInfoparam staffInfoparam = new StaffInfoparam()
{
_endDate = DateTime.Parse(EndDate),
_startDate = DateTime.Parse(StartDate),
_queryName = Queryid,
_rows = (int)rows,
_page = (int)page,
_total = 0,
_fileName = ""
};
var json = helpStaffs.Contaion(_hostingEnvironment, staffInfoparam, export);
if (staffInfoparam._fileName == "")
{
return(Json(ResultMsg.FormatResult(404, "没有找到该文件啊", "处理有误")));
}
return(File(staffInfoparam._fileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
}
/// <summary>
/// 分页查询数据
/// </summary>
/// <param name="infoparam"></param>
/// <param name="export"></param>
/// <returns></returns>
public dynamic Contaion(IHostingEnvironment _hostingEnvironment, StaffInfoparam infoparam, string export)
{
var db = GlobalDBContext.Instance();
string QueryfieldName = infoparam._queryName;
DateTime startDate = infoparam._startDate;
DateTime endDate = infoparam._endDate;
var Rowset = (from S in db.Staffs
join O in db.Operations on S.OperationID equals O.OperationID
orderby S.StaffID
select new
{
ShopID = S.ShopID,
AuthorityID = S.AuthorityID,
StaffBirth = S.StaffBirth,
StaffBirthType = S.StaffBirthType,
StaffEntryTime = S.StaffEntryTime,
StaffIdentity = S.StaffIdentity,
StaffName = S.StaffName,
StaffPhone = S.StaffPhone,
StaffSex = S.StaffSex,
AcrossTheStore = S.AcrossTheStore,
StaffDuty = S.StaffDuty,
StaffCreateTime = O.StaffCreateTime,
StaffCreator = O.StaffCreator,
StaffUpdateTime = O.StaffUpdateTime,
StaffModifyUser = O.StaffModifyUser
});
///按照时间筛选
if (!string.IsNullOrEmpty(infoparam._queryName) && !infoparam._endDate.ToString().StartsWith("0") && !infoparam._startDate.ToString().StartsWith("0"))
{
switch (QueryfieldName)
{
case "StaffBirth":
Rowset.Where(S => S.StaffBirth >= startDate && S.StaffBirth <= endDate);
break;
case "StaffEntryTime":
Rowset.Where(S => S.StaffEntryTime >= startDate && S.StaffEntryTime <= endDate);
break;
case "StaffCreateTime":
Rowset.Where(S => S.StaffCreateTime >= startDate && S.StaffCreateTime <= endDate);
break;
case "StaffUpdateTime":
Rowset.Where(S => S.StaffUpdateTime >= startDate && S.StaffUpdateTime <= endDate);
break;
}
}
//按照用户输入筛选
if (!string.IsNullOrEmpty(infoparam._selectVal) && !string.IsNullOrEmpty(infoparam._inputVals))
{
switch (infoparam._selectVal)
{
case "StaffSex":
Rowset.Where(S => S.StaffSex.ToString().Contains(infoparam._inputVals));
break;
case "StaffDuty":
Rowset.Where(S => S.StaffDuty.ToString().Contains(infoparam._inputVals));
break;
case "StaffIdentity":
Rowset.Where(S => S.StaffIdentity.ToString().Contains(infoparam._inputVals));
break;
case "StaffPhone":
Rowset.Where(S => S.StaffPhone.ToString().Contains(infoparam._inputVals));
break;
case "StaffName":
Rowset.Where(S => S.StaffName.ToString().Contains(infoparam._inputVals));
break;
case "StaffBirth":
Rowset.Where(S => S.StaffBirth.ToString().Contains(infoparam._inputVals));
break;
case "StaffEntryTime":
Rowset.Where(S => S.StaffEntryTime.ToString().Contains(infoparam._inputVals));
break;
case "StaffCreateTime":
Rowset.Where(S => S.StaffCreateTime.ToString().Contains(infoparam._inputVals));
break;
case "StaffUpdateTime":
Rowset.Where(S => S.StaffUpdateTime.ToString().Contains(infoparam._inputVals));
break;
}
}
// 按门店查询
if (!string.IsNullOrEmpty(infoparam._shopID))
{
Rowset.Where(u => u.ShopID == infoparam._shopID);
}
///导出报表
if (export != "")
{
DataTable table = new DataTable();//创建表
List <string> Columns = new List <string>()
{
"姓名", "性别", "职位", "生日类型", "生日", "身份证", "手机", "是否跨店", "入职时间", "创建时间", "创建人", "修改时间", "修改用户"
};
table = CreateTable.ReturnColuns(table, Columns);
foreach (var item in Rowset)
{
table.Rows.Add(new object[] { item.StaffName, item.StaffSex, item.StaffDuty, item.StaffBirthType, item.StaffBirth, item.StaffIdentity, item.StaffPhone, item.AcrossTheStore, item.StaffEntryTime, item.StaffCreateTime, item.StaffCreator, item.StaffUpdateTime, item.StaffModifyUser });
}
infoparam._fileName = ExportExecl.CrateExecl(_hostingEnvironment, table);
}
infoparam._total = Rowset.Count();
return(Rowset.OrderBy(p => p.AuthorityID).Skip(Convert.ToInt32(infoparam._rows) * Convert.ToInt32(infoparam._page) - 1).Take(Convert.ToInt32(infoparam._rows)));
}