/// <summary> /// 生成盤點工作 /// </summary> /// <returns></returns> public HttpResponseBase GetCountBook2() { // DataSet.Tables["XX"].Columns["xx"].ColumnName = "NewColumnName"; string json = string.Empty; IinvdQuery m = new IinvdQuery(); CbjobMaster cm = new CbjobMaster(); CbjobDetail cd = new CbjobDetail(); IinvdMgr iinvdMgr = new IinvdMgr(mySqlConnectionString); _IiupcMgr = new IupcMgr(mySqlConnectionString); string cbjob_id = "PC";// +DateTime.Now.ToString("yyyyMMddHHmmss"); try { #region 條件 if (!string.IsNullOrEmpty(Request.Params["startIloc"].Trim())) {//料位開始 m.startIloc = Request.Params["startIloc"].Trim().ToUpper(); } int length = m.startIloc.Length; switch (length) { case 0: cbjob_id = cbjob_id + "AA"; break; case 1: cbjob_id = cbjob_id+ m.startIloc+"A"; break; default: cbjob_id = cbjob_id + m.startIloc.Substring(0, 2); break; } if (!string.IsNullOrEmpty(Request.Params["endIloc"].Trim())) { m.endIloc = Request.Params["endIloc"].Trim() + "Z"; m.endIloc = m.endIloc.ToUpper(); } length = m.endIloc.Length; switch (length) { case 0: cbjob_id = cbjob_id + "ZZ"; break; case 1: cbjob_id = cbjob_id +m.endIloc +"Z"; break; default: cbjob_id = cbjob_id + m.endIloc.Substring(0, 2); break; } if (!string.IsNullOrEmpty(Request.Params["level"]))//層數 {//層數選擇 m.lot_no = Request.Params["level"].ToString().ToUpper(); } if (!string.IsNullOrEmpty(Request.Params["sort"]))//排序 {//排序方式 m.Sort = Request.Params["sort"]; if (m.Sort == "0" && !string.IsNullOrEmpty(Request.Params["firstsd"])) { m.Firstsd = Request.Params["firstsd"]; } } if (!string.IsNullOrEmpty(m.Firstsd)) { if (m.Firstsd == "0") { cbjob_id += "S";//單 } else { cbjob_id += "M";//雙 } } else { cbjob_id += "Z";//不分 } cbjob_id += DateTime.Now.ToString("yyyyMMddHHmmss"); if (!string.IsNullOrEmpty(Request.Params["vender"])) {//vender m.vender = Request.Params["vender"].ToString().ToUpper(); } if (!string.IsNullOrEmpty(Request.Params["prepaid"])) { m.prepaid = int.Parse(Request.Params["prepaid"]); } #endregion DataTable dt = iinvdMgr.getVentory(m); if (dt.Rows.Count > 0) { #region 往cbjob_master裡面插入一條數據 StringBuilder sb = new StringBuilder(); _cbjobMgr = new CbjobDetailMgr(mySqlConnectionString); _cbMasterMgr = new CbjobMasterMgr(mySqlConnectionString); cm.cbjob_id = cbjob_id; cm.create_datetime = DateTime.Now; cm.sta_id = "CNT"; cm.create_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id; sb.Append(_cbMasterMgr.Insertsql(cm)); #endregion #region 修改iinvd數據,往cbjob_detail循環插入數據 int a = 0; for (int i = 0; i < dt.Rows.Count; i++) { if (!string.IsNullOrEmpty(dt.Rows[i]["row_id"].ToString())) { cd.cb_jobid = cm.cbjob_id; cd.cb_newid = a + 1; cd.chang_user = cm.create_user; cd.change_datetime = DateTime.Now; cd.create_datetime = DateTime.Now; cd.create_user = cm.create_user; cd.status = 1; cd.iinvd_id = int.Parse(dt.Rows[i]["row_id"].ToString()); sb.AppendFormat("set sql_safe_updates = 0; UPDATE iinvd set st_qty=prod_qty where row_id='{0}'; set sql_safe_updates = 1;", cd.iinvd_id); sb.Append(_cbjobMgr.insertsql(cd)); a++; } } #endregion _cbjobMgr.InsertSql(sb.ToString()); json = "{success:true,msg:" + 1 + "}"; } else { json = "{success:true,msg:" + 2 + "}"; #region 應用 //Response.Clear(); //this.Response.Write("沒有數據<br/>"); #endregion } } 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,msg:" + 0 + "}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
public HttpResponseBase GetCountBook() { // DataSet.Tables["XX"].Columns["xx"].ColumnName = "NewColumnName"; string json = string.Empty; IinvdQuery m = new IinvdQuery(); CbjobMaster cm = new CbjobMaster(); CbjobDetail cd = new CbjobDetail(); IinvdMgr iinvdMgr = new IinvdMgr(mySqlConnectionString); _IiupcMgr = new IupcMgr(mySqlConnectionString); string cbjob_id = "PC" + DateTime.Now.ToString("yyyyMMddHHmmss"); try { #region 條件 if (!string.IsNullOrEmpty(Request.Params["startIloc"])) {//料位開始 m.startIloc = Request.Params["startIloc"].ToUpper(); } if (!string.IsNullOrEmpty(Request.Params["endIloc"])) { m.endIloc = Request.Params["endIloc"] + "Z"; m.endIloc = m.endIloc.ToUpper(); } if (!string.IsNullOrEmpty(Request.Params["level"]))//層數 {//層數選擇 m.lot_no = Request.Params["level"].ToString().ToUpper(); } if (!string.IsNullOrEmpty(Request.Params["sort"]))//排序 {//排序方式 m.Sort = Request.Params["sort"]; if (m.Sort == "0" && !string.IsNullOrEmpty(Request.Params["firstsd"])) { m.Firstsd = Request.Params["firstsd"]; } } if (!string.IsNullOrEmpty(Request.Params["vender"])) {//vender m.vender = Request.Params["vender"].ToString().ToUpper(); } if (!string.IsNullOrEmpty(Request.Params["prepaid"])) { m.prepaid = int.Parse(Request.Params["prepaid"]); } #endregion DataTable dt = iinvdMgr.getproduct(m); #region 列名 DataTable dtCountBook = new DataTable(); dtCountBook.Columns.Add("編號", typeof(String)); dtCountBook.Columns.Add("料位", typeof(String)); dtCountBook.Columns.Add("狀態", typeof(String)); dtCountBook.Columns.Add("商品細項編號", typeof(String)); dtCountBook.Columns.Add("庫存", typeof(String)); dtCountBook.Columns.Add("成本", typeof(String)); dtCountBook.Columns.Add("商品名稱", typeof(String)); dtCountBook.Columns.Add("商品規格", typeof(String)); dtCountBook.Columns.Add("是否買斷", typeof(String)); dtCountBook.Columns.Add("製造日期", typeof(String)); dtCountBook.Columns.Add("保存期限", typeof(String)); dtCountBook.Columns.Add("有效日期", typeof(String)); dtCountBook.Columns.Add("是否有效期控制", typeof(String)); dtCountBook.Columns.Add("是否即期", typeof(String)); dtCountBook.Columns.Add("是否過期", typeof(String)); dtCountBook.Columns.Add("允出天數", typeof(String)); dtCountBook.Columns.Add("允收天數", typeof(String)); dtCountBook.Columns.Add("國際條碼", typeof(String)); dtCountBook.Columns.Add("最新店內碼", typeof(String)); #endregion if (dt.Rows.Count > 0) { #region 往cbjob_master裡面插入一條數據 StringBuilder sb = new StringBuilder(); _cbjobMgr = new CbjobDetailMgr(mySqlConnectionString); _cbMasterMgr = new CbjobMasterMgr(mySqlConnectionString); cm.cbjob_id = cbjob_id; cm.create_datetime = DateTime.Now; cm.sta_id = "CNT"; cm.create_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id; sb.Append(_cbMasterMgr.Insertsql(cm)); #endregion #region 修改iinvd數據,往cbjob_detail循環插入數據 int a = 0; for (int i = 0; i < dt.Rows.Count; i++) { if (!string.IsNullOrEmpty(dt.Rows[i]["row_id"].ToString())) { cd.cb_jobid = cm.cbjob_id; cd.cb_newid = a + 1; cd.chang_user = cm.create_user; cd.change_datetime = DateTime.Now; cd.create_datetime = DateTime.Now; cd.create_user = cm.create_user; cd.status = 1; cd.iinvd_id = int.Parse(dt.Rows[i]["row_id"].ToString()); sb.AppendFormat("set sql_safe_updates = 0; UPDATE iinvd set st_qty=prod_qty where row_id='{0}'; set sql_safe_updates = 1;", cd.iinvd_id); sb.Append(_cbjobMgr.insertsql(cd)); a++; } } _cbjobMgr.InsertSql(sb.ToString()); #endregion m.cb_jobid = cm.cbjob_id;//賦值給m //dt = iinvdMgr.CountBook(m); int bh = 1; string loc = ""; foreach (DataRow item2 in dt.Rows) { if (item2["loc_id"].ToString() != loc) { if (!string.IsNullOrEmpty(item2["row_id"].ToString())) { #region 讀取iinvd裡面的數據 m.loc_id = item2["loc_id"].ToString(); DataTable Invdt = iinvdMgr.GetIinvdCountBook(m); foreach (DataRow item in Invdt.Rows) { DataRow dr = dtCountBook.NewRow(); dr[0] = bh; dr[1] = item["loc_id"]; dr[2] = item["lsta_id"]; if (!string.IsNullOrEmpty(item["item_id"].ToString())) { dr[3] = item["item_id"]; } if (!string.IsNullOrEmpty(item["prod_qty"].ToString())) { dr[4] = item["prod_qty"]; } else { dr[4] = "0"; } if (!string.IsNullOrEmpty(item["product_id"].ToString())) { dr[5] = iinvdMgr.Getcost(item["product_id"].ToString()); } dr[6] = item["product_name"]; dr[7] = GetProductSpec(item["item_id"].ToString()); dr[8] = item["prepaid"].ToString() == "0" ? "否" : "是"; DateTime md; if (DateTime.TryParse(item["made_date"].ToString(), out md)) { dr[9] = DateTime.Parse(item["made_date"].ToString()).ToString("yyyy/MM/dd"); } dr[10] = item["cde_dt_incr"]; DateTime cdate; if (DateTime.TryParse(item["cde_dt"].ToString(), out cdate)) { dr[11] = cdate.ToString("yyyy/MM/dd"); } int shp = 0; if (item["pwy_dte_ctl"].ToString() == "Y" && Int32.TryParse(item["cde_dt_shp"].ToString(), out shp))//表示是有效期控管的商品 { dr[12] = "Y"; if (cdate.AddDays(-shp) <= DateTime.Now && cdate >= DateTime.Now) { dr[13] = "Y"; } else { dr[13] = "N"; } if (cdate < DateTime.Now) { dr[14] = "Y"; } else { dr[14] = "N"; } dr[15] = item["cde_dt_shp"];//允出天數 dr[16] = item["cde_dt_var"]; } else if (item["pwy_dte_ctl"].ToString() == "N") { dr[12] = "N"; dr[13] = ""; dr[14] = ""; dr[15] = 0;//允出天數 dr[16] = 0; } else { dr[12] = ""; dr[13] = ""; dr[14] = ""; dr[15] = 0;//允出天數 dr[16] = 0; } dr[17] = " " + _IiupcMgr.Getupc(item["item_id"].ToString(), "1"); dr[18] = " " + _IiupcMgr.Getupc(item["item_id"].ToString(), "2"); dtCountBook.Rows.Add(dr); bh++; } #endregion } else { #region iinvd沒有的料位信息從iplas表查出 DataRow dr = dtCountBook.NewRow(); m.loc_id = item2["loc_id"].ToString(); DataTable dt1 = iinvdMgr.GetIplasCountBook(m); foreach (DataRow item1 in dt1.Rows) { dr[0] = bh; dr[1] = item2["loc_id"]; dr[2] = item1["lsta_id"]; if (!string.IsNullOrEmpty(item1["item_id"].ToString())) { dr[3] = item1["item_id"]; } if (!string.IsNullOrEmpty(item1["prod_qty"].ToString())) { dr[4] = item1["prod_qty"]; } else { dr[4] = "0"; } if (!string.IsNullOrEmpty(item1["product_id"].ToString())) { dr[5] = iinvdMgr.Getcost(item1["product_id"].ToString()); } else { dr[5] = "0"; } dr[6] = item1["product_name"]; dr[7] = GetProductSpec(item1["item_id"].ToString()); dr[8] = item1["prepaid"].ToString() == "0" ? "否" : "是"; dr[9] = ""; dr[10] = item1["cde_dt_incr"]; dr[11] = ""; if (item1["pwy_dte_ctl"].ToString() == "Y")//表示是有效期控管的商品 { dr[12] = "Y"; dr[13] = "N"; dr[14] = "N"; dr[15] = item1["cde_dt_shp"];//允出天數 dr[16] = item1["cde_dt_var"]; } else if (item1["pwy_dte_ctl"].ToString() == "N") { dr[12] = "N"; dr[13] = ""; dr[14] = ""; dr[15] = 0;//允出天數 dr[16] = 0; } else { dr[12] = ""; dr[13] = ""; dr[14] = ""; dr[15] = 0;//允出天數 dr[16] = 0; } dr[17] = " " + _IiupcMgr.Getupc(item1["item_id"].ToString(), "1"); dr[18] = " " + _IiupcMgr.Getupc(item1["item_id"].ToString(), "2"); dtCountBook.Rows.Add(dr); bh++; } #endregion } loc = item2["loc_id"].ToString(); } } string fileName = "盤點簿" + cm.cbjob_id.Substring(2, 14) + ".xls"; String str = "盤點簿報表-" + cm.cbjob_id; MemoryStream ms = ExcelHelperXhf.ExportDT(dtCountBook, str); Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName); Response.BinaryWrite(ms.ToArray()); } else { Response.Clear(); this.Response.Write("沒有數據<br/>"); } } 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); } return this.Response; }