public DataTable GetTimesheetReport(TimesheetReportSearch search, out int totalCount)
{
DataTable dt = new DataTable();
EnsureConnectionOpen();
var outputTotalSqlParameter = new SqlParameter("@totalRecords", SqlDbType.Int);
outputTotalSqlParameter.Direction = ParameterDirection.Output;
var isPageSqlParameter = new SqlParameter("@isPage", SqlDbType.Bit);
isPageSqlParameter.Value = search.isPage ? 1 : 0;
using (var command = CreateCommand("Proc_TimesheetReport", CommandType.StoredProcedure,
new SqlParameter("startDate", search.startDate), new SqlParameter("endDate", search.endDate),
new SqlParameter("departmentIDs", search.departmentIds ?? ""), new SqlParameter("productionLineList", search.productionLineList ?? ""),
new SqlParameter("projectIds", search.projectIds ?? ""), new SqlParameter("userIds", search.userIds ?? ""), new SqlParameter("currentUserID", search.currentUserID), new SqlParameter("status", search.status ?? ""),
isPageSqlParameter, new SqlParameter("pageSize", search.pageSize), new SqlParameter("pageStart", search.pageStart), outputTotalSqlParameter
))
{
using (var da = new SqlDataAdapter(command))
{
da.Fill(dt);
}
}
totalCount = int.Parse(outputTotalSqlParameter.Value.ToString());
return(dt);
}
public FileResult GetExcelForReport(TimesheetReportSearch search)
{
int totalRow = 0;
search.currentUserID = Common.CommonHelper.CurrentUser;
search.isPage = false;
DataTable dt = _reportAppService.GetTimesheetReport(search, out totalRow);
string sheetName = "员工工时报表";
var book = Common.CommonHelper.CreateHSSFromDataTable(sheetName, dt, new List <int>()
{
});
MemoryStream ms = new MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", sheetName + ".xls"));
}
public JsonResult GetTimesheetReport()
{
int start = Convert.ToInt32(Request["start"]);
int length = Convert.ToInt32(Request["length"]);
int totalRow = 0;
TimesheetReportSearch search = new TimesheetReportSearch();
search.isPage = true;
search.pageStart = Convert.ToInt32(Request["start"]);
search.pageSize = Convert.ToInt32(Request["length"]);
search.startDate = string.IsNullOrEmpty(Request["columns[0][search][value]"]) ? DateTime.Parse(DateTime.Now.AddDays(-7).ToString("yyyy-MM-dd")) : DateTime.Parse(Request["columns[0][search][value]"]);
search.endDate = string.IsNullOrEmpty(Request["columns[1][search][value]"]) ? DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")) : DateTime.Parse(Request["columns[1][search][value]"]);
if (!string.IsNullOrEmpty(Request["columns[2][search][value]"]))
{
search.departmentIds = Request["columns[2][search][value]"];
}
if (!string.IsNullOrEmpty(Request["columns[3][search][value]"]))
{
search.productionLineList = Request["columns[3][search][value]"];
}
if (!string.IsNullOrEmpty(Request["columns[4][search][value]"]))
{
search.projectIds = Request["columns[4][search][value]"];
}
if (!string.IsNullOrEmpty(Request["columns[5][search][value]"]))
{
search.userIds = Request["columns[5][search][value]"];
}
if (!string.IsNullOrEmpty(Request["columns[6][search][value]"]))
{
search.status = Request["columns[6][search][value]"];
}
search.currentUserID = Common.CommonHelper.CurrentUser;
DataTable dt = _reportAppService.GetTimesheetReport(search, out totalRow);
return(Json(new { data = GetListByDataTable(dt), draw = Request["draw"], recordsTotal = totalRow, recordsFiltered = totalRow }, JsonRequestBehavior.AllowGet));
}
