public string QueryProductStock(string workorder, string productname, string batchnumber, string starttime, string endtime)
{
IList <ProductStock> objs = _bal.FindProductStockInfo(workorder, productname, batchnumber, starttime, endtime);
StringBuilder sb = new StringBuilder();
sb.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
sb.Append("<rows>");
string strstatus = string.Empty;
foreach (ProductStock o in objs)
{
sb.Append(string.Format("<row id='{0}'>", o.PSN));
sb.Append(string.Format("<cell>{0}</cell>", o.PSN));
sb.Append(string.Format("<cell>{0}</cell>", o.WorkOrder));
if (o.MANUFACTURE.Length == 13)
{
BasBase ibbb = _bal.FindNameBySubCode(o.MANUFACTURE);
sb.Append(string.Format("<cell>{0}</cell>", ibbb.SubName));
}
else
{
sb.Append(string.Format("<cell>{0}</cell>", o.MANUFACTURE));
}
sb.Append(string.Format("<cell>{0}</cell>", o.DOCUMENTID));
BasBase ibbbb = _bal.FindNameBySubCode(o.StockHouse);
sb.Append(string.Format("<cell>{0}</cell>", ibbbb.SubName));
sb.Append(string.Format("<cell>{0}</cell>", o.ProductCode));
sb.Append(string.Format("<cell>{0}</cell>", o.ProductName));
BasBase ibb = _bal.FindNameBySubCode(o.UNIT);
sb.Append(string.Format("<cell>{0}</cell>", ibb.SubName));
sb.Append(string.Format("<cell>{0}</cell>", o.QUANTITY.ToString()));
sb.Append(string.Format("<cell>{0}</cell>", o.BatchNumber));
sb.Append(string.Format("<cell>{0}</cell>", o.UpdatedBy));
sb.Append(string.Format("<cell>{0}</cell>", o.FromBy));
sb.Append(string.Format("<cell>{0}</cell>", o.UpdatedDate == null ? o.CreatedDate : o.UpdatedDate));
//sb.Append(string.Format("<cell>{0}</cell>", o.MEMO));
sb.Append("</row>");
}
sb.Append("</rows>");
return(sb.ToString());
}
protected void Page_Load(object sender, EventArgs e)
{
string workorder = Request.QueryString["workorder"];
string status = Request.QueryString["status"];
string productname = Request.QueryString["productname"];
string batchnumber = Request.QueryString["batchnumber"];
string starttime = Request.QueryString["starttime"];
string endtime = Request.QueryString["endtime"];
DateTime dtstart = DateTime.Today.AddDays(-7);
DateTime dtend = DateTime.Now;
if (!string.IsNullOrEmpty(starttime) && !string.IsNullOrEmpty(endtime))
{
dtstart = Convert.ToDateTime(starttime);
dtend = Convert.ToDateTime(endtime);
}
WareHouseBO _bal = BLLFactory.GetBal <WareHouseBO>(userInfo);
WsSystem ws = new WsSystem();
IList <ProductStock> objstock = null;
IList <ProductStockHistory> objhistory = null;
if (status == "2")
{
objstock = _bal.FindProductStockInfo(workorder, productname, batchnumber, starttime, endtime);
}
else
{
if (status == "3")
{
status = "";
}
objhistory = _bal.FindProductStockHistory(status, workorder, productname, batchnumber, starttime, endtime);
}
if (objstock == null && objhistory.Count == 0)
{
Response.Write("no data");
return;
}
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
Row row = null;
Cell cell = null;
Sheet hssfSheet = hssfWorkbook.CreateSheet("ProductInfo");
row = hssfSheet.CreateRow(0);
//填充头
string objs = "产品条码,工单号码,交货单位,单据编号,收货仓库,产品代码,产品名称,单位,数量,批号,保管员,移交人,时间";
for (int i = 0; i < objs.Split(',').Length; i++)
{
cell = row.CreateCell(i);
cell.SetCellValue(objs.Split(',')[i]);
}
if (objstock != null)
{
for (int i = 1; i < objstock.Count + 1; i++)
{
row = hssfSheet.CreateRow(i);
cell = row.CreateCell(0);
cell.SetCellValue(objstock[i - 1].PSN);
cell = row.CreateCell(1);
cell.SetCellValue(objstock[i - 1].WorkOrder);
cell = row.CreateCell(2);
cell.SetCellValue(objstock[i - 1].MANUFACTURE);
cell = row.CreateCell(3);
cell.SetCellValue(objstock[i - 1].DOCUMENTID);
cell = row.CreateCell(4);
cell.SetCellValue(objstock[i - 1].StockHouse);
cell = row.CreateCell(5);
cell.SetCellValue(objstock[i - 1].ProductCode);
cell = row.CreateCell(6);
cell.SetCellValue(objstock[i - 1].ProductName);
cell = row.CreateCell(7);
cell.SetCellValue(objstock[i - 1].UNIT);
cell = row.CreateCell(8);
cell.SetCellValue(objstock[i - 1].QUANTITY.ToString());
cell = row.CreateCell(9);
cell.SetCellValue(objstock[i - 1].BatchNumber);
cell = row.CreateCell(10);
cell.SetCellValue(ws.FindUserNameByCode(objstock[i - 1].UpdatedBy));
cell = row.CreateCell(11);
cell.SetCellValue(ws.FindUserNameByCode(objstock[i - 1].FromBy));
cell = row.CreateCell(12);
cell.SetCellValue(objstock[i - 1].UpdatedDate == null ? objstock[i - 1].CreatedDate.ToString() : objstock[i - 1].UpdatedDate.ToString());
}
}
else
{
for (int i = 1; i < objhistory.Count + 1; i++)
{
row = hssfSheet.CreateRow(i);
cell = row.CreateCell(0);
cell.SetCellValue(objhistory[i - 1].PSN);
cell = row.CreateCell(1);
cell.SetCellValue(objhistory[i - 1].WorkOrder);
cell = row.CreateCell(2);
cell.SetCellValue(objhistory[i - 1].MANUFACTURE);
cell = row.CreateCell(3);
cell.SetCellValue(objhistory[i - 1].DOCUMENTID);
cell = row.CreateCell(4);
cell.SetCellValue(objhistory[i - 1].StockHouse);
cell = row.CreateCell(5);
cell.SetCellValue(objhistory[i - 1].ProductCode);
cell = row.CreateCell(6);
cell.SetCellValue(objhistory[i - 1].ProductName);
cell = row.CreateCell(7);
cell.SetCellValue(objhistory[i - 1].UNIT);
cell = row.CreateCell(8);
cell.SetCellValue(objhistory[i - 1].QUANTITY.ToString());
cell = row.CreateCell(9);
cell.SetCellValue(objhistory[i - 1].BatchNumber);
cell = row.CreateCell(10);
cell.SetCellValue(ws.FindUserNameByCode(objhistory[i - 1].UpdatedBy));
cell = row.CreateCell(11);
cell.SetCellValue(ws.FindUserNameByCode(objhistory[i - 1].FromBy));
cell = row.CreateCell(12);
cell.SetCellValue(objhistory[i - 1].UpdatedDate == null ? objhistory[i - 1].CreatedDate.ToString() : objhistory[i - 1].UpdatedDate.ToString());
}
}
MemoryStream file = new MemoryStream();
hssfWorkbook.Write(file);
String fileName = "ProductInfo" + DateTime.Now.ToString("yyyyMMddHHmmss");
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", fileName));
Response.AddHeader("Content-Length", file.Length.ToString());
Response.AddHeader("Content-Transfer-Encoding", "binary");
Response.ContentType = "application/octet-stream";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
Response.BinaryWrite(file.GetBuffer());
Response.Flush();
Response.End();
}