public ActionResult GetUserOrderNoEatList(string dateBegin, string dateEnd, int?pageIndex, int?pageSize)
{
string Condition = " and (CancUser is null or CancUser = '')";
if (!string.IsNullOrEmpty(dateBegin))
{
Condition += " and OrderDinner.OrderDate >= '" + DateTime.Parse(dateBegin).ToString("yyyy-MM-dd 00:00:00") + "'";
}
if (!string.IsNullOrEmpty(dateEnd))
{
Condition += " and OrderDinner.OrderDate <= '" + DateTime.Parse(dateEnd).ToString("yyyy-MM-dd 23:59:59") + "'";
}
IList <UserOrderDetail> orderTotal = new List <UserOrderDetail>();
DataTable dataDinner = userOrderLogic.GetOrderDinnerDetail(Condition, 888888, 1, "", "");
int rowIndex = 1;
if (dataDinner.Rows.Count > 0)
{
foreach (DataRow row in dataDinner.Rows)
{
rowIndex += 1;
string typeCode = row["TypeCode"].ToString();
string userid = row["UserId"].ToString();
string strBeg = DateTime.Parse(row["OrderDate"].ToString()).ToString("yyyy-MM-dd 00:00:00.000");
string strEnd = DateTime.Parse(row["OrderDate"].ToString()).ToString("yyyy-MM-dd 23:59:59");
string strCondition = " and IsUnusual='0' and UserId='" + userid + "' and TypeCode='" + typeCode + "' and CreateDate between '" + strBeg + "' and '" + strEnd + "'";
//string strSql = @" select * from PatiInBillItem where ";
int rowount = billitemLogic.Count(strCondition);
if (rowount > 0)
{
continue;
}
UserOrderDetail orderInfo = new UserOrderDetail();
orderInfo.orderDate = DateTime.Parse(row["OrderDate"].ToString());
orderInfo.type = row["TypeCode"].ToString();
orderInfo.userId = row["userid"].ToString();
orderInfo.userName = row["UserName"].ToString();
orderInfo.Quantity = int.Parse(row["Quantity"].ToString());
orderTotal.Add(orderInfo);
}
}
IDictionary <string, object> dic = new Dictionary <string, object>();
int count = orderTotal.Count();
dic.Add("total", orderTotal.Count);
dic.Add("data", orderTotal);
return(Json(dic, JsonRequestBehavior.AllowGet));
}
public UserOrderDetail GetByOrderNo(string OrderNo)
{
var model = new UserOrderDetail();
string sql = "select * from fz_Orders where orderNo=@orderNo";
var item = new QueryObject <Order>(_databaseInstance, sql, new { orderNo = OrderNo }).FirstOrDefault();
if (item != null)
{
string sql1 = "select * from fz_OrderDetails where orderNo=@orderNo";
var list = new QueryObject <OrderDetail>(_databaseInstance, sql1, new { orderNo = OrderNo }).ToList();
model.item = item;
model.OrderDetails = list;
}
return(model);
}
public ActionResult UserOrderDetailList(string dateBegin, string dateEnd, int?pageIndex, int?pageSize)
{
pageIndex = pageIndex + 1;
if (pageSize == null)
{
pageSize = 25;
}
string Condition = " and (CancUser is null or CancUser='')";
if (!string.IsNullOrEmpty(dateBegin))
{
Condition += " and OrderDinner.OrderDate >= '" + DateTime.Parse(dateBegin).ToString("yyyy-MM-dd 00:00:00") + "'";
}
if (!string.IsNullOrEmpty(dateEnd))
{
Condition += " and OrderDinner.OrderDate <= '" + DateTime.Parse(dateEnd).ToString("yyyy-MM-dd 23:59:59") + "'";
}
IList <UserOrderDetail> orderTotal = new List <UserOrderDetail>();
DataTable dataDinner = userOrderLogic.GetOrderDinnerDetail(Condition, pageSize.Value, pageIndex.Value, "", "");
int rowcount = userOrderLogic.GetOrderDinnerDetailCount(Condition);
if (dataDinner != null && dataDinner.Rows.Count > 0)
{
foreach (DataRow drNew in dataDinner.Rows)
{
UserOrderDetail item = new UserOrderDetail();
item.orderDate = DateTime.Parse(drNew["OrderDate"].ToString());
item.userId = drNew[0].ToString();
item.userName = drNew["UserName"].ToString();
item.type = drNew["TypeCode"].ToString();
item.Quantity = int.Parse(drNew["Quantity"].ToString());
orderTotal.Add(item);
}
}
IDictionary <string, object> dic = new Dictionary <string, object>();
int count = orderTotal.Count();
dic.Add("total", rowcount);
dic.Add("data", orderTotal);
return(Json(dic, JsonRequestBehavior.AllowGet));
}
public UserOrderDetailViewModel GetViewModel(UserOrderDetail domain)
{
var result = Mapper.Map <UserOrderDetail, UserOrderDetailViewModel>(domain);
return(result);
}
public FileResult Export_UserOrderNoEat_Excel(string dateBegin, string dateEnd)
{
string Condition = " and (CancUser is null or CancUser = '')";
if (!string.IsNullOrEmpty(dateBegin))
{
Condition += " and OrderDinner.OrderDate >= '" + DateTime.Parse(dateBegin).ToString("yyyy-MM-dd 00:00:00") + "'";
}
if (!string.IsNullOrEmpty(dateEnd))
{
Condition += " and OrderDinner.OrderDate <= '" + DateTime.Parse(dateEnd).ToString("yyyy-MM-dd 23:59:59") + "'";
}
IList <UserOrderDetail> orderTotal = new List <UserOrderDetail>();
DataTable dataDinner = userOrderLogic.GetOrderDinnerDetail(Condition, 888888, 1, "", "");
int rowIndex = 1;
if (dataDinner.Rows.Count > 0)
{
foreach (DataRow row in dataDinner.Rows)
{
rowIndex += 1;
string typeCode = row["TypeCode"].ToString();
string userid = row["UserId"].ToString();
string strBeg = DateTime.Parse(row["OrderDate"].ToString()).ToString("yyyy-MM-dd 00:00:00.000");
string strEnd = DateTime.Parse(row["OrderDate"].ToString()).ToString("yyyy-MM-dd 23:59:59");
string strCondition = " and IsUnusual='0' and UserId='" + userid + "' and TypeCode='" + typeCode + "' and CreateDate between '" + strBeg + "' and '" + strEnd + "'";
//string strSql = @" select * from PatiInBillItem where ";
int rowount = billitemLogic.Count(strCondition);
if (rowount > 0)
{
continue;
}
UserOrderDetail orderInfo = new UserOrderDetail();
orderInfo.orderDate = DateTime.Parse(row["OrderDate"].ToString());
orderInfo.type = row["TypeCode"].ToString();
orderInfo.userId = row["userid"].ToString();
orderInfo.userName = row["UserName"].ToString();
orderInfo.Quantity = int.Parse(row["Quantity"].ToString());
orderTotal.Add(orderInfo);
}
}
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据
// List<modelList> listRainInfo = m_BLL.GetSchoolListAATQ(schoolname);
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("预约日期");
row1.CreateCell(1).SetCellValue("员工工号");
row1.CreateCell(2).SetCellValue("员工姓名");
row1.CreateCell(3).SetCellValue("午别");
row1.CreateCell(4).SetCellValue("数量");
//将数据逐步写入sheet1各个行
for (int i = 0; i < orderTotal.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(orderTotal[i].orderDate.ToString("yyyy-MM-dd"));
rowtemp.CreateCell(1).SetCellValue(orderTotal[i].userId);
rowtemp.CreateCell(2).SetCellValue(orderTotal[i].userName);
if (orderTotal[i].type == "01")
{
rowtemp.CreateCell(3).SetCellValue("早餐");
}
else if (orderTotal[i].type == "02")
{
rowtemp.CreateCell(3).SetCellValue("午餐");
}
else if (orderTotal[i].type == "03")
{
rowtemp.CreateCell(3).SetCellValue("晚餐");
}
rowtemp.CreateCell(4).SetCellValue(orderTotal[i].Quantity.ToString());
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"));
}
public FileResult Export_UserOrderDetail_Excel(string dateBegin, string dateEnd)
{
string Condition = " and (CancUser is null or CancUser='')";
if (!string.IsNullOrEmpty(dateBegin))
{
Condition += " and OrderDinner.OrderDate >= '" + DateTime.Parse(dateBegin).ToString("yyyy-MM-dd 00:00:00") + "'";
}
if (!string.IsNullOrEmpty(dateEnd))
{
Condition += " and OrderDinner.OrderDate <= '" + DateTime.Parse(dateEnd).ToString("yyyy-MM-dd 23:59:59") + "'";
}
IList <UserOrderDetail> orderTotal = new List <UserOrderDetail>();
DataTable dataDinner = userOrderLogic.GetOrderDinnerDetail(Condition, 9999, 1, "OrderDate", "");
if (dataDinner != null && dataDinner.Rows.Count > 0)
{
foreach (DataRow drNew in dataDinner.Rows)
{
UserOrderDetail item = new UserOrderDetail();
item.orderDate = DateTime.Parse(drNew["OrderDate"].ToString());
item.userId = drNew[0].ToString();
item.userName = drNew["UserName"].ToString();
item.type = drNew["TypeCode"].ToString();
item.Quantity = int.Parse(drNew["Quantity"].ToString());
orderTotal.Add(item);
}
}
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据
// List<modelList> listRainInfo = m_BLL.GetSchoolListAATQ(schoolname);
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("预约日期");
row1.CreateCell(1).SetCellValue("工号");
row1.CreateCell(2).SetCellValue("姓名");
row1.CreateCell(3).SetCellValue("午别");
row1.CreateCell(4).SetCellValue("数量");
//将数据逐步写入sheet1各个行
for (int i = 0; i < orderTotal.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(orderTotal[i].orderDate.ToString("yyyy-MM-dd"));
rowtemp.CreateCell(1).SetCellValue(orderTotal[i].userId);
rowtemp.CreateCell(2).SetCellValue(orderTotal[i].userName);
if (orderTotal[i].type == "01")
{
rowtemp.CreateCell(3).SetCellValue("早餐");
}
else if (orderTotal[i].type == "02")
{
rowtemp.CreateCell(3).SetCellValue("午餐");
}
else if (orderTotal[i].type == "03")
{
rowtemp.CreateCell(3).SetCellValue("晚餐");
}
rowtemp.CreateCell(4).SetCellValue(orderTotal[i].Quantity.ToString());
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"));
}