public string Insertsql(CbjobMaster m) { try { return _cbjobDao.Insertsql(m); } catch (Exception ex) { throw new Exception("CbjobMasterMgr-->Insertsql-->" + ex.Message, ex); } }
public int Insert(CbjobMaster m) { try { return _cbjobDao.Insert(m); } catch (Exception ex) { throw new Exception("CbjobMasterMgr-->Insert-->" + ex.Message, ex); } }
public string Insertsql(CbjobMaster m) { StringBuilder sbSql = new StringBuilder(); try { sbSql.AppendFormat(@"insert into cbjob_master (cbjob_id,create_datetime,create_user,status) values('{0}','{1}','{2}','{3}');", m.cbjob_id, Common.CommonFunction.DateTimeToString(m.create_datetime), m.create_user, 1); return sbSql.ToString(); } catch (Exception ex) { throw new Exception("CbjobMasterDao-->Insertsql-->" + ex.Message + sbSql.ToString(), ex); } }
public HttpResponseBase GetCountBookOBK() { string json = string.Empty; IinvdQuery m = new IinvdQuery(); CbjobMaster cm = new CbjobMaster(); CbjobDetail cd = new CbjobDetail(); 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["startcost"]))//金額 { m.startcost = int.Parse(Request.Params["startcost"].ToString()); } if (!string.IsNullOrEmpty(Request.Params["endcost"]))//金額 { m.endcost = int.Parse(Request.Params["endcost"].ToString()); } #endregion _iinvd = new IinvdMgr(mySqlConnectionString); DataTable dt = _iinvd.CountBookOBK(m); if (dt.Rows.Count > 0) { #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)); foreach (DataRow item in dt.Rows) { DataRow dr = dtCountBook.NewRow(); dr[0] = item["row_id"]; dr[1] = item["loc_id"]; dr[2] = item["lsta_id"]; dr[3] = item["item_id"]; dr[4] = item["cost"]; dr[5] = item["product_name"]; dr[6] = GetProductSpec(item["item_id"].ToString()); dr[7] = DateTime.Parse(item["made_date"].ToString()).ToString("yyyy/MM/dd"); dr[8] = DateTime.Parse(item["cde_dt"].ToString()).ToString("yyyy/MM/dd"); dr[9] = item["prod_qty"]; dr[10] = item["st_qty"]; dr[11] = item["money"]; dtCountBook.Rows.Add(dr); } #endregion string fileName = "盤點差異報表OBK" + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".xls"; String str = "盤點差異報表OBK" + DateTime.Now.ToString("_yyyyMMddHHmmss"); 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; }
/// <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; }