public void IlocChangeDetailExcelList() { string json = string.Empty; IlocChangeDetailQuery ilocDetailQuery = new IlocChangeDetailQuery(); DataTable dtExcel = new DataTable(); try { if (!string.IsNullOrEmpty(Request.Params["productids"])) { ilocDetailQuery.productids = Request.Params["productids"].Replace(',', ',').Replace('|', ',').Replace(' ', ','); } if (!string.IsNullOrEmpty(Request.Params["oldilocid"]))//model中默認為F { ilocDetailQuery.icd_old_loc_id = Request.Params["oldilocid"]; } if (!string.IsNullOrEmpty(Request.Params["newilocid"])) { ilocDetailQuery.icd_new_loc_id = Request.Params["newilocid"].ToString(); } DateTime time; if (DateTime.TryParse(Request.Params["start_time"].ToString(), out time)) { ilocDetailQuery.start_time = DateTime.Parse(time.ToString("yyyy-MM-dd HH:mm:ss")); } if (DateTime.TryParse(Request.Params["end_time"].ToString(), out time)) { ilocDetailQuery.end_time = DateTime.Parse(time.ToString("yyyy-MM-dd HH:mm:ss")); } if (!string.IsNullOrEmpty(Request.Params["startloc"])) { ilocDetailQuery.startloc = Request.Params["startloc"].ToUpper(); } if (!string.IsNullOrEmpty(Request.Params["endloc"])) { ilocDetailQuery.endloc = Request.Params["endloc"].ToUpper() + "Z"; } if (!string.IsNullOrEmpty(Request.Params["icd_status"]) && Request.Params["icd_status"] != "全部") { ilocDetailQuery.icd_status = Request.Params["icd_status"]; } DataTable store = new DataTable(); _ilocDetailMger = new IlocChangeDetailMgr(mySqlConnectionString); store = _ilocDetailMger.GetIlocChangeDetailExcelList(ilocDetailQuery); dtExcel.Columns.Add("商品編號", typeof(String)); dtExcel.Columns.Add("商品名稱", typeof(String)); dtExcel.Columns.Add("規格", typeof(String)); dtExcel.Columns.Add("是否買斷", typeof(String)); dtExcel.Columns.Add("原料位編號", typeof(String)); dtExcel.Columns.Add("新料位編號", typeof(String)); // dtExcel.Columns.Add("製造日期", typeof(String)); //dtExcel.Columns.Add("保存期限", typeof(String)); dtExcel.Columns.Add("搬移日期", typeof(String)); dtExcel.Columns.Add("庫存", typeof(String)); dtExcel.Columns.Add("是否有效期控管", typeof(String)); //dtExcel.Columns.Add("是否即期", typeof(String)); //dtExcel.Columns.Add("是否過期", typeof(String)); dtExcel.Columns.Add("允收天數", typeof(String)); dtExcel.Columns.Add("允出天數", typeof(String)); dtExcel.Columns.Add("搬移狀態", typeof(String)); IinvdMgr iinvdMgr = new IinvdMgr(mySqlConnectionString); List<Parametersrc> stores = new List<Parametersrc>(); _ptersrc = new ParameterMgr(mySqlConnectionString); stores = _ptersrc.GetElementType("icd_status"); for (int i = 0; i < store.Rows.Count; i++) { Iinvd invd = new Iinvd(); invd.item_id = uint.Parse(store.Rows[i]["icd_item_id"].ToString()); DataRow newRow = dtExcel.NewRow(); newRow[0] = store.Rows[i]["icd_item_id"].ToString(); newRow[1] = store.Rows[i]["product_name"].ToString(); newRow[2] = GetProductSpec(store.Rows[i]["icd_item_id"].ToString()); newRow[3] = store.Rows[i]["prepaid"].ToString() == "1" ? "是" : "否"; newRow[4] = store.Rows[i]["icd_old_loc_id"].ToString(); newRow[5] = store.Rows[i]["icd_new_loc_id"].ToString(); // newRow[6] = store.Rows[i]["made_date"].ToString(); newRow[6] = store.Rows[i]["icd_create_time"].ToString(); // newRow[8] = store.Rows[i]["cde_dt"].ToString(); newRow[7] = iinvdMgr.SumProd_qty(invd);//store.Rows[i]["prod_qty"].ToString(); newRow[8] = store.Rows[i]["pwy_dte_ctl"].ToString(); //if (store.Rows[i]["pwy_dte_ctl"].ToString() == "Y") //{ // DateTime made = DateTime.Parse(store.Rows[i]["made_date"].ToString()); // int incr = int.Parse(store.Rows[i]["cde_dt_incr"].ToString()); // int shp = int.Parse(store.Rows[i]["cde_dt_shp"].ToString()); // if (made.AddDays(incr) < DateTime.Now) // { // newRow[9] = "是"; // } // else if (made.AddDays(incr - shp) < DateTime.Now) // { // newRow[10] = "是"; // } // else // { // newRow[10] = "否"; // newRow[9] = "否"; // } //} //else //{ // newRow[9] = ""; // newRow[10] = ""; //} newRow[9] = store.Rows[i]["cde_dt_var"].ToString(); newRow[10] = store.Rows[i]["cde_dt_shp"].ToString(); for (int a = 0; a < stores.Count; a++) { if (stores[a].ParameterCode == store.Rows[i]["icd_status"].ToString()) { newRow[11] = stores[a].parameterName; } } dtExcel.Rows.Add(newRow); } if (dtExcel.Rows.Count > 0) { string fileName = "料位移動查詢_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; MemoryStream ms = ExcelHelperXhf.ExportDT(dtExcel, "料位移動查詢_" + DateTime.Now.ToString("yyyyMMddHHmmss")); Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName); Response.BinaryWrite(ms.ToArray()); } else { Response.Write("匯出數據不存在"); } } 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); } }
public HttpResponseBase GetIlocChangeDetailList() { string json = string.Empty; IlocChangeDetailQuery ilocDetailQuery = new IlocChangeDetailQuery(); ilocDetailQuery.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量 ilocDetailQuery.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量 if (!string.IsNullOrEmpty(Request.Params["productids"])) { ilocDetailQuery.productids = Request.Params["productids"].Replace(',', ',').Replace('|', ',').Replace(' ', ','); } if (!string.IsNullOrEmpty(Request.Params["oldilocid"]))//model中默認為F { ilocDetailQuery.icd_old_loc_id = Request.Params["oldilocid"]; } if (!string.IsNullOrEmpty(Request.Params["newilocid"])) { ilocDetailQuery.icd_new_loc_id = Request.Params["newilocid"].ToString(); } DateTime time; if (DateTime.TryParse(Request.Params["start_time"].ToString(), out time)) { ilocDetailQuery.start_time = DateTime.Parse(time.ToString("yyyy-MM-dd HH:mm:ss")); } if (DateTime.TryParse(Request.Params["end_time"].ToString(), out time)) { ilocDetailQuery.end_time = DateTime.Parse(time.ToString("yyyy-MM-dd HH:mm:ss")); } if (!string.IsNullOrEmpty(Request.Params["startloc"])) { ilocDetailQuery.startloc = Request.Params["startloc"].ToUpper(); } if (!string.IsNullOrEmpty(Request.Params["endloc"])) { ilocDetailQuery.endloc = Request.Params["endloc"].ToUpper() + "Z"; } if (!string.IsNullOrEmpty(Request.Params["icd_status"]) && Request.Params["icd_status"] != "全部") { ilocDetailQuery.icd_status = Request.Params["icd_status"]; } try { List<IlocChangeDetailQuery> store = new List<IlocChangeDetailQuery>(); _ilocDetailMger = new IlocChangeDetailMgr(mySqlConnectionString); int totalCount = 0; store = _ilocDetailMger.GetIlocChangeDetailList(ilocDetailQuery, out totalCount); IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式 timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; IinvdMgr iinvdMgr = new IinvdMgr(mySqlConnectionString); foreach (var item in store) { Iinvd invd = new Iinvd(); invd.item_id = item.icd_item_id; item.prod_qty = iinvdMgr.SumProd_qty(invd); if (item.prepaid == 0) { item.prepa_name = "否"; } else { item.prepa_name = "是"; } if (item.icd_item_id > 0) { item.product_sz = GetProductSpec(item.icd_item_id.ToString()); } //if (item.pwy_dte_ctl == "Y") //{ // if (item.made_date.AddDays(item.cde_dt_incr) < DateTime.Now) // { // item.isgq = "是"; // } // else if (item.made_date.AddDays(item.cde_dt_incr - item.cde_dt_shp) < DateTime.Now) // { // item.isjq = "是"; // } // else // { // item.isgq = "否"; // item.isjq = "否"; // } //} //else //{ // item.isgq = "-"; // item.isjq = "-"; //} } json = "{success:true,'msg':'user',totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";//返回json數據 } 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,totalCount:0,data:[]}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }