public void PastProductExportlist()
{
string json = string.Empty;
string fileName = "";
string fileNametwo = "";
IinvdQuery invd = new IinvdQuery();
try
{
_iinvd = new IinvdMgr(mySqlConnectionString);
_IiupcMgr = new IupcMgr(mySqlConnectionString);
invd.notimeortimeout = Convert.ToInt32(Request.Params["time_type"]);
if (!string.IsNullOrEmpty(Request.Params["startIloc"]))//model中默認為F
{
invd.startIloc = Request.Params["startIloc"].ToUpper();
}
else
{
invd.startIloc = string.Empty;
}
if (!string.IsNullOrEmpty(Request.Params["endIloc"]))
{
invd.endIloc = Request.Params["endIloc"] + "Z";
invd.endIloc = invd.endIloc.ToUpper();
}
else
{
invd.endIloc = string.Empty;
}
if (!string.IsNullOrEmpty(Request.Params["startDay"]))
{
invd.startDay = Convert.ToInt32(Request.Params["startDay"]);
}
else
{
invd.startDay = 0;
}
if (!string.IsNullOrEmpty(Request.Params["endDay"]))
{
invd.endDay = Convert.ToInt32(Request.Params["endDay"]);
}
else
{
invd.endDay = 0;
}
int yugao = 0;
if (int.TryParse(Request.Params["yugaoDay"], out yugao))
{
invd.yugaoDay = yugao;
}
else
{
invd.yugaoDay = 0;
}
if (!System.IO.Directory.Exists(Server.MapPath(excelPath)))
{
System.IO.Directory.CreateDirectory(Server.MapPath(excelPath));
}
DataTable dtHZ = new DataTable();
string newExcelName = string.Empty;
dtHZ.Columns.Add("即過", typeof(String));
dtHZ.Columns.Add("料位", typeof(String));
dtHZ.Columns.Add("屬性", typeof(String));
dtHZ.Columns.Add("品號", typeof(String));
dtHZ.Columns.Add("數量", typeof(String));
dtHZ.Columns.Add("品名", typeof(String));
dtHZ.Columns.Add("規格", typeof(String));
dtHZ.Columns.Add("條碼", typeof(String));
dtHZ.Columns.Add("允出天數", typeof(String));
dtHZ.Columns.Add("有效日期", typeof(String));
dtHZ.Columns.Add("是否買斷", typeof(String));
dtHZ.Columns.Add("溫層", typeof(String));
dtHZ.Columns.Add("即期日期", typeof(String));
dtHZ.Columns.Add("庫存鎖", typeof(String));
DataTable dt = new DataTable();
dt = _iinvd.PastProductExportExcel(invd);
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dtHZ.NewRow();
if (invd.notimeortimeout == 2)//(Convert.ToDateTime(dt.Rows[i]["cde_dt"]) <= DateTime.Now.AddDays(invd.yugaoDay))//過期有效期<=今天
{
dr[0] = "過期";
fileName = "過期品";
fileNametwo = "過期品";
}
else if (invd.notimeortimeout == 1)//(Convert.ToDateTime(dt.Rows[i]["cde_dt"]) <= DateTime.Now.AddDays(int.Parse(dt.Rows[i]["cde_dt_shp"].ToString() + invd.yugaoDay)))
{
dr[0] = "即期";
fileName = "即期品";
fileNametwo = "即期品";
}
else
{
dr[0] = "錯誤";
}
dr[1] = dt.Rows[i]["plas_loc_id"];
dr[2] = dt.Rows[i]["lcat_id"];
dr[3] = dt.Rows[i]["item_id"];
dr[4] = dt.Rows[i]["prod_qty"];
dr[5] = dt.Rows[i]["product_name"];
dr[6] = GetProductSpec(dt.Rows[i]["item_id"].ToString());
dr[7] = " " + _IiupcMgr.Getupc(dt.Rows[i]["item_id"].ToString(), "0");
dr[8] = dt.Rows[i]["cde_dt_shp"];
dr[9] = Convert.ToDateTime(dt.Rows[i]["cde_dt"]).ToString("yyyy-MM-dd");
dr[10] = dt.Rows[i]["prepaid"].ToString() == "1" ? "是" : "否";
if (dt.Rows[i]["product_freight_set"].ToString() == "1")
{
dr[11] = "常溫";
}
else if (dt.Rows[i]["product_freight_set"].ToString() == "2")
{
dr[11] = "冷凍";
}
else
{
dr[11] = "";
}
dr[12] = DateTime.Parse(dt.Rows[i]["cde_dt"].ToString()).AddDays(-int.Parse(dt.Rows[i]["cde_dt_shp"].ToString())).ToString("yyyy-MM-dd");
dr[13] = dt.Rows[i]["ista_id"];
dtHZ.Rows.Add(dr);
}
if (System.IO.File.Exists(newExcelName))
{
System.IO.File.Delete(newExcelName);
}
fileName += "預告報表預告天數:" + invd.yugaoDay + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
fileNametwo += " 預告報表 預告天數:" + invd.yugaoDay + " _" + DateTime.Now.ToString("yyyyMMddHHmm");
MemoryStream ms = ExcelHelperXhf.ExportDT(dtHZ, fileNametwo);
Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
Response.BinaryWrite(ms.ToArray());
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,data:[]}";
}
}