public int insertistocksome(CbjobDetailQuery cbQuery) { try { return _IstockDao.insertistocksome(cbQuery); } catch (Exception ex) { throw new Exception("IstockChangeMgr-->insertistocksome-->" + ex.Message, ex); } }
public int DeleteCbjobmessage(CbjobDetailQuery cbjobQuery) { StringBuilder sql = new StringBuilder(); int result = 0; StringBuilder sb = new StringBuilder(); StringBuilder str = new StringBuilder(); str.AppendFormat("select cbjob_id from cbjob_master where cbjob_id='{0}'and status=1", cbjobQuery.cb_jobid); if (_access.getDataTable(str.ToString()).Rows.Count == 0)//程序到此直接跳轉出去 { return -2;//-2表示不存在該工作編號或已被刪除 } sb.AppendFormat("select cbjob_id from cbjob_master where cbjob_id='{0}' and status=1 and sta_id= '{1}'", cbjobQuery.cb_jobid, "CNT"); if (_access.getDataTable(sb.ToString()).Rows.Count == 0)//判斷該工作編號是否能被刪除 { return -1; } sql.AppendFormat("set sql_safe_updates = 0;"); sql.AppendFormat(@" update cbjob_detail set status='{0}' where cb_jobid='{1}';", 0, cbjobQuery.cb_jobid); sql.AppendFormat(@" update cbjob_master set status='{0}' where cbjob_id='{1}';", 0, cbjobQuery.cb_jobid); sql.AppendFormat("set sql_safe_updates = 1;"); MySqlCommand mySqlCmd = new MySqlCommand(); MySqlConnection mySqlConn = new MySqlConnection(connStr); try { if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed) { mySqlConn.Open(); } mySqlCmd.Connection = mySqlConn; mySqlCmd.Transaction = mySqlConn.BeginTransaction(); mySqlCmd.CommandType = System.Data.CommandType.Text; mySqlCmd.CommandText = sql.ToString(); result = mySqlCmd.ExecuteNonQuery(); mySqlCmd.Transaction.Commit(); } catch (Exception ex) { mySqlCmd.Transaction.Rollback(); throw new Exception("CbjobDetailDao.DeleteCbjobmessage-->" + ex.Message + str.ToString() + sb.ToString() + sql.ToString(), ex); } finally { if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open) { mySqlConn.Close(); } } return result; }
public HttpResponseBase FupanComplete() { int result = 0; string json = string.Empty; CbjobDetailQuery cbjobQuery = new CbjobDetailQuery(); if (!string.IsNullOrEmpty(Request.Params["jobnumber"].Trim())) { cbjobQuery.cb_jobid = Request.Params["jobnumber"].Trim();//獲取工作編號 } cbjobQuery.create_datetime = DateTime.Now; //創建時間 cbjobQuery.create_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id; try { _cbjobMgr = new CbjobDetailMgr(mySqlConnectionString); result = _cbjobMgr.FupanComplete(cbjobQuery); if (result == -1) { json = "{success:true,msg:-1}";//返回json數據 } else if (result == -2) { json = "{success:true,msg:-2}";//返回json數據 } else if (result == -3) { json = "{success:true,msg:-3}";//返回json數據 } else if (result > 0) { json = "{success:true,msg:1}";//返回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; }
public HttpResponseBase UpdateCbjobstaid() { int result = 0; int resulttwo = 0; string json = string.Empty; CbjobDetailQuery cbjobQuery = new CbjobDetailQuery(); if (!string.IsNullOrEmpty(Request.Params["jobnumber"].Trim())) { cbjobQuery.cb_jobid = Request.Params["jobnumber"].Trim();//獲取工作編號 } DateTime time = DateTime.MinValue; if (DateTime.TryParse(Request.Params["starttime"].ToString(), out time)) { cbjobQuery.StartDate = time; } if (DateTime.TryParse(Request.Params["endtime"].ToString(), out time)) { cbjobQuery.EndDate = time.AddDays(1); } cbjobQuery.create_datetime = DateTime.Now; //修改時間 cbjobQuery.create_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id;//修改用戶 try { _cbjobMgr = new CbjobDetailMgr(mySqlConnectionString); _istockMgr = new IstockChangeMgr(mySqlConnectionString); resulttwo = _istockMgr.insertistocksome(cbjobQuery); result = _cbjobMgr.UpdateCbjobstaid(cbjobQuery); if (result > 0) { if (resulttwo > 0) { json = "{success:true,msg:'" + result + "'}";//返回json數據 } else { json = "{success:false,msg:'" + result + "'}";//返回json數據 } } else if (result == -1) { json = "{success:true,msg:-1}";//返回json數據 } else if (result == -2) { json = "{success:true,msg:-2}";//返回json數據 } else if (result == -3) { json = "{success:true,msg:-3}";//返回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; }
public HttpResponseBase DeleteCbjobmessage() { int result = 0; string json = string.Empty; CbjobDetailQuery cbjobQuery = new CbjobDetailQuery(); if (!string.IsNullOrEmpty(Request.Params["searchcontent"].Trim())) { cbjobQuery.cb_jobid = Request.Params["searchcontent"].Trim(); } try { _cbjobMgr = new CbjobDetailMgr(mySqlConnectionString); result = _cbjobMgr.DeleteCbjobmessage(cbjobQuery); if (result == -2) { json = "{success:true,msg:-2}"; } else if (result == -1) { json = "{success:true,msg:-1}"; } else if (result > 0) { json = "{success:true,msg:1}";//返回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; }
public HttpResponseBase GetMessage() { string json = string.Empty; CbjobDetailQuery cbjQuery = new CbjobDetailQuery(); cbjQuery.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量 cbjQuery.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量 if (!string.IsNullOrEmpty(Request.Params["searchcontent"].Trim())) { cbjQuery.searchcontent = Request.Params["searchcontent"]; } if (!string.IsNullOrEmpty(Request.Params["newid"].Trim())) { cbjQuery.cb_newid = Convert.ToInt32(Request.Params["newid"].Trim()); } try { List<CbjobDetailQuery> store = new List<CbjobDetailQuery>(); _cbjobMgr = new CbjobDetailMgr(mySqlConnectionString); int totalCount = 0; store = _cbjobMgr.GetMessage(cbjQuery, out totalCount); IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式 timeConverter.DateTimeFormat = "yyyy-MM-dd"; 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; }
public int FupanComplete(CbjobDetailQuery cbjobQuery) { StringBuilder sql = new StringBuilder(); int result = 0; StringBuilder sb = new StringBuilder(); StringBuilder str = new StringBuilder(); if (!string.IsNullOrEmpty(cbjobQuery.cb_jobid)) { str.AppendFormat("select cbjob_id from cbjob_master where cbjob_id='{0}'and status=1", cbjobQuery.cb_jobid); if (_access.getDataTable(str.ToString()).Rows.Count == 0)//程序到此直接跳轉出去 { return -2;//-2表示不存在該工作編號 } sb.AppendFormat("select cbjob_id from cbjob_master where cbjob_id='{0}' and status=1 and sta_id= '{1}'", cbjobQuery.cb_jobid, "UPD");//是否存在狀態為UPD的可復盤的狀態 if (_access.getDataTable(sb.ToString()).Rows.Count == 0)//程序到此直接跳轉出去 { return -1;//表示該狀態不為UPD } sql.AppendFormat("set sql_safe_updates = 0;"); sql.AppendFormat(@" update cbjob_master set sta_id='COM',create_user='******',create_datetime='{1}' where cbjob_id='{2}';",cbjobQuery.create_user,Common.CommonFunction.DateTimeToString(cbjobQuery.create_datetime), cbjobQuery.cb_jobid); sql.AppendFormat("set sql_safe_updates = 1;"); } else { str.AppendFormat("select cbjob_id from cbjob_master where status=1 and sta_id='UPD';"); if (_access.getDataTable(str.ToString()).Rows.Count == 0)//程序到此直接跳轉出去 { return -3;//-2表示沒有可以復盤的 } sql.AppendFormat("set sql_safe_updates = 0;"); sql.AppendFormat(@" update cbjob_master set sta_id='COM',create_user='******',create_datetime='{1}' where sta_id='UPD';",cbjobQuery.create_user,Common.CommonFunction.DateTimeToString(cbjobQuery.create_datetime)); sql.AppendFormat("set sql_safe_updates = 1;"); } MySqlCommand mySqlCmd = new MySqlCommand(); MySqlConnection mySqlConn = new MySqlConnection(connStr); try { if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed) { mySqlConn.Open(); } mySqlCmd.Connection = mySqlConn; mySqlCmd.Transaction = mySqlConn.BeginTransaction(); mySqlCmd.CommandType = System.Data.CommandType.Text; mySqlCmd.CommandText = sql.ToString(); result = mySqlCmd.ExecuteNonQuery(); mySqlCmd.Transaction.Commit(); } catch (Exception ex) { mySqlCmd.Transaction.Rollback(); throw new Exception("CbjobDetailDao.FupanComplete-->" + ex.Message + sb.ToString() + str.ToString() + sql.ToString(), ex); } finally { if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open) { mySqlConn.Close(); } } return result; }
/// <summary> /// 蓋帳按鈕--插入idiff_count_book表中即時差異報表/把COM的狀態改為END /// </summary> /// <param name="cbjobQuery"></param> /// <returns></returns> public int UpdateCbjobstaid(CbjobDetailQuery cbjobQuery) { int result = 0; int jbcount=0; StringBuilder sql = new StringBuilder(); StringBuilder sb = new StringBuilder(); StringBuilder str = new StringBuilder(); StringBuilder strsql = new StringBuilder(); StringBuilder strsqltwo = new StringBuilder(); string iinvdidstr = string.Empty; string jobnumber="P" + DateTime.Now.ToString("yyyyMMddHHmmss"); if (!string.IsNullOrEmpty(cbjobQuery.cb_jobid)) { str.AppendFormat("select cbjob_id from cbjob_master where cbjob_id='{0}'and status=1 and create_datetime>='{1}' and create_datetime<= '{2}'", cbjobQuery.cb_jobid,cbjobQuery.StartDate,cbjobQuery.EndDate); jbcount = _access.getDataTable(str.ToString()).Rows.Count; if (jbcount == 0)//程序到此直接跳轉出去 { return -2;//-2表示不存在該工作編號或已被刪除 } sb.AppendFormat("select cbjob_id from cbjob_master where cbjob_id='{0}' and status=1 and sta_id= '{1}' and create_datetime>='{2}' and create_datetime<= '{3}'", cbjobQuery.cb_jobid, "COM", cbjobQuery.StartDate, cbjobQuery.EndDate); if (_access.getDataTable(sb.ToString()).Rows.Count == 0)//判斷該工作編號是否能被蓋帳 { return -1; } else//如果該工作編號未被蓋帳,並且處於COM狀態 { strsql.AppendFormat(@"SELECT cd.iinvd_id,cd.cb_jobid FROM cbjob_master cm LEFT JOIN cbjob_detail cd on cd.cb_jobid=cm.cbjob_id LEFT JOIN iinvd invd on invd.row_id=cd.iinvd_id WHERE cm.cbjob_id='{0}' and invd.st_qty<> invd.prod_qty and cm.status=1 and sta_id= 'COM' and invd.st_qty=0 and cm.create_datetime>='{1}' and cm.create_datetime<= '{2}' group by cd.iinvd_id;", cbjobQuery.cb_jobid, cbjobQuery.StartDate, cbjobQuery.EndDate); DataTable _dt = _access.getDataTable(strsql.ToString()); strsqltwo.AppendFormat(@"SELECT cd.iinvd_id,cd.cb_jobid FROM cbjob_master cm LEFT JOIN cbjob_detail cd on cd.cb_jobid=cm.cbjob_id LEFT JOIN iinvd invd on invd.row_id=cd.iinvd_id WHERE cm.cbjob_id='{0}' and invd.st_qty<> invd.prod_qty and cm.status=1 and sta_id= 'COM'and invd.st_qty <> 0 and invd.st_qty <> invd.prod_qty and cm.create_datetime>='{1}' and cm.create_datetime<= '{2}' group by cd.iinvd_id;", cbjobQuery.cb_jobid, cbjobQuery.StartDate, cbjobQuery.EndDate); DataTable _dttwo = _access.getDataTable(strsqltwo.ToString()); if (_dt.Rows.Count > 0)//表示有要求要刪除的數據 { for (int i = 0; i < _dt.Rows.Count; i++) { StringBuilder sbstr = new StringBuilder(); StringBuilder sbstr2 = new StringBuilder(); sbstr.AppendFormat("select st_qty,plas_loc_id from iinvd where row_id ='{0}';", _dt.Rows[i][0]); DataTable qtdt = _access.getDataTable(sbstr.ToString()); sbstr.Clear(); sbstr2.AppendFormat("select row_id from iinvd where plas_loc_id='{0}';",qtdt.Rows[0][1]); DataTable qtdt2 = _access.getDataTable(sbstr2.ToString()); sbstr.AppendFormat("select st_qty,plas_loc_id,item_id,prod_qty,made_date,cde_dt from iinvd where row_id ='{0}';", _dt.Rows[i][0]); DataTable qtdt3 = _access.getDataTable(sbstr.ToString()); sbstr.Clear(); int adj_qty = Convert.ToInt32(qtdt3.Rows[0][0]) - Convert.ToInt32(qtdt3.Rows[0][3]); iinvdidstr = iinvdidstr + string.Format(@"insert into iialg (loc_id,item_id,iarc_id,qty_o,create_dtim,create_user,doc_no, made_dt,cde_dt,adj_qty ) values ('{0}','{1}','{2}','{3}', '{4}','{5}', '{6}','{7}', '{8}','{9}');", qtdt3.Rows[0][1], qtdt3.Rows[0][2], "PC", qtdt3.Rows[0][3], Common.CommonFunction.DateTimeToString(cbjobQuery.create_datetime), cbjobQuery.create_user, jobnumber, Common.CommonFunction.DateTimeToString(Convert.ToDateTime(qtdt3.Rows[0][4])), Common.CommonFunction.DateTimeToString(Convert.ToDateTime(qtdt3.Rows[0][5])), adj_qty); iinvdidstr = iinvdidstr + string.Format("delete from iinvd where row_id='{0}';", _dt.Rows[i][0]); if (qtdt2.Rows.Count < 2) { iinvdidstr=iinvdidstr+string.Format("update iloc set lsta_id='F' where loc_id='{0}' and lcat_id='R';", qtdt.Rows[0][1]); } } } if (_dttwo.Rows.Count > 0) { for (int i = 0; i < _dttwo.Rows.Count; i++) { StringBuilder sbstr = new StringBuilder(); sbstr.AppendFormat("select st_qty,plas_loc_id,item_id,prod_qty,made_date,cde_dt from iinvd where row_id ='{0}';", _dttwo.Rows[i][0]); DataTable qtdt = _access.getDataTable(sbstr.ToString()); iinvdidstr = iinvdidstr + string.Format("update iinvd set prod_qty=st_qty ,change_user='******',change_dtim='{1}' where row_id='{2}' ;", cbjobQuery.create_user, Common.CommonFunction.DateTimeToString(cbjobQuery.create_datetime), _dttwo.Rows[i][0]); int adj_qty=Convert.ToInt32(qtdt.Rows[0][0])-Convert.ToInt32(qtdt.Rows[0][3]); iinvdidstr = iinvdidstr + string.Format(@"insert into iialg (loc_id,item_id,iarc_id,qty_o,create_dtim,create_user,doc_no, made_dt,cde_dt,adj_qty ) values ('{0}','{1}','{2}','{3}', '{4}','{5}', '{6}','{7}', '{8}','{9}');", qtdt.Rows[0][1], qtdt.Rows[0][2], "PC", qtdt.Rows[0][3],Common.CommonFunction.DateTimeToString(cbjobQuery.create_datetime), cbjobQuery.create_user, jobnumber,Common.CommonFunction.DateTimeToString(Convert.ToDateTime(qtdt.Rows[0][4])),Common.CommonFunction.DateTimeToString(Convert.ToDateTime(qtdt.Rows[0][5])), adj_qty); iinvdidstr = iinvdidstr + string.Format(@"INSERT into idiff_count_book(cb_jobid,loc_id,pro_qty,st_qty,create_user,create_time,item_id,made_date,cde_dt)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}');" , _dttwo.Rows[i][1], qtdt.Rows[0][1], qtdt.Rows[0][3], qtdt.Rows[0][0], cbjobQuery.create_user, Common.CommonFunction.DateTimeToString(DateTime.Now), qtdt.Rows[0][2], Common.CommonFunction.DateTimeToString(Convert.ToDateTime(qtdt.Rows[0][4])), Common.CommonFunction.DateTimeToString(Convert.ToDateTime(qtdt.Rows[0][5]))); } } } } else { #region 其他情況 str.AppendFormat("select cbjob_id from cbjob_master where sta_id= '{0}' and status=1 and create_datetime>='{1}' and create_datetime<= '{2}'", "COM", cbjobQuery.StartDate, cbjobQuery.EndDate); jbcount = _access.getDataTable(str.ToString()).Rows.Count; if (jbcount == 0)//程序到此直接跳轉出去 { return -3;//-3沒有可蓋帳的 } else//如果存在可蓋帳的 查詢出可蓋帳的信息 { strsql.AppendFormat(@"SELECT cd.iinvd_id,cd.cb_jobid FROM cbjob_master cm LEFT JOIN cbjob_detail cd on cd.cb_jobid=cm.cbjob_id LEFT JOIN iinvd invd on invd.row_id=cd.iinvd_id WHERE invd.st_qty<> invd.prod_qty and cm.status=1 and sta_id= 'COM'and invd.st_qty = 0 and cm.create_datetime>='{0}' and cm.create_datetime<= '{1}' group by cd.iinvd_id;",cbjobQuery.StartDate, cbjobQuery.EndDate); DataTable _dt = _access.getDataTable(strsql.ToString()); strsqltwo.AppendFormat(@"SELECT cd.iinvd_id,cd.cb_jobid FROM cbjob_master cm LEFT JOIN cbjob_detail cd on cd.cb_jobid=cm.cbjob_id LEFT JOIN iinvd invd on invd.row_id=cd.iinvd_id WHERE invd.st_qty<> invd.prod_qty and cm.status=1 and sta_id= 'COM'and invd.st_qty <> 0 and invd.st_qty <> invd.prod_qty and cm.create_datetime>='{0}' and cm.create_datetime<= '{1}' group by cd.iinvd_id;",cbjobQuery.StartDate, cbjobQuery.EndDate); DataTable _dttwo = _access.getDataTable(strsqltwo.ToString()); if (_dt.Rows.Count > 0)//表示有要求要刪除的數據 { for (int i = 0; i < _dt.Rows.Count; i++) { StringBuilder sbstr = new StringBuilder(); StringBuilder sbstr2 = new StringBuilder(); sbstr.AppendFormat("select st_qty,plas_loc_id from iinvd where row_id ='{0}';", _dt.Rows[i][0]); DataTable qtdt = _access.getDataTable(sbstr.ToString()); sbstr.Clear(); sbstr2.AppendFormat("select row_id from iinvd where plas_loc_id='{0}';",qtdt.Rows[0][1]); DataTable qtdt2 = _access.getDataTable(sbstr2.ToString()); sbstr.AppendFormat("select st_qty,plas_loc_id,item_id,prod_qty,made_date,cde_dt from iinvd where row_id ='{0}';", _dt.Rows[i][0]); DataTable qtdt3 = _access.getDataTable(sbstr.ToString()); sbstr.Clear(); int adj_qty = Convert.ToInt32(qtdt3.Rows[0][0]) - Convert.ToInt32(qtdt3.Rows[0][3]); iinvdidstr = iinvdidstr + string.Format(@"insert into iialg (loc_id,item_id,iarc_id,qty_o,create_dtim,create_user,doc_no, made_dt,cde_dt,adj_qty ) values ('{0}','{1}','{2}','{3}', '{4}','{5}', '{6}','{7}', '{8}','{9}');", qtdt3.Rows[0][1], qtdt3.Rows[0][2], "PC", qtdt3.Rows[0][3], Common.CommonFunction.DateTimeToString(cbjobQuery.create_datetime), cbjobQuery.create_user, jobnumber, Common.CommonFunction.DateTimeToString(Convert.ToDateTime(qtdt3.Rows[0][4])), Common.CommonFunction.DateTimeToString(Convert.ToDateTime(qtdt3.Rows[0][5])), adj_qty); iinvdidstr = iinvdidstr + string.Format("delete from iinvd where row_id='{0}';", _dt.Rows[i][0]); if (qtdt2.Rows.Count < 2) { iinvdidstr = iinvdidstr + string.Format("update iloc set lsta_id='F' where loc_id='{0}' and lcat_id='R';", qtdt.Rows[0][1]); } } } if (_dttwo.Rows.Count > 0) { for (int i = 0; i < _dttwo.Rows.Count; i++) { StringBuilder sbstr = new StringBuilder(); sbstr.AppendFormat("select st_qty,plas_loc_id,item_id,prod_qty,made_date,cde_dt from iinvd where row_id ='{0}';", _dttwo.Rows[i][0]); DataTable qtdt = _access.getDataTable(sbstr.ToString()); iinvdidstr = iinvdidstr + string.Format("update iinvd set prod_qty=st_qty ,change_user='******',change_dtim='{1}' where row_id='{2}' ;", cbjobQuery.create_user, Common.CommonFunction.DateTimeToString(cbjobQuery.create_datetime), _dttwo.Rows[i][0]); int adj_qty = Convert.ToInt32(qtdt.Rows[0][0]) - Convert.ToInt32(qtdt.Rows[0][3]); iinvdidstr = iinvdidstr + string.Format(@"insert into iialg (loc_id,item_id,iarc_id,qty_o,create_dtim,create_user,doc_no, made_dt,cde_dt,adj_qty) values ('{0}','{1}','{2}','{3}', '{4}','{5}', '{6}','{7}', '{8}','{9}');", qtdt.Rows[0][1], qtdt.Rows[0][2], "PC", qtdt.Rows[0][3],Common.CommonFunction.DateTimeToString(cbjobQuery.create_datetime), cbjobQuery.create_user, jobnumber, Common.CommonFunction.DateTimeToString(Convert.ToDateTime(qtdt.Rows[0][4])),Common.CommonFunction.DateTimeToString(Convert.ToDateTime(qtdt.Rows[0][5])), adj_qty ); iinvdidstr = iinvdidstr + string.Format(@"INSERT into idiff_count_book(cb_jobid,loc_id,pro_qty,st_qty,create_user,create_time,item_id,made_date,cde_dt)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}');" , _dttwo.Rows[i][1], qtdt.Rows[0][1], qtdt.Rows[0][3], qtdt.Rows[0][0], cbjobQuery.create_user, Common.CommonFunction.DateTimeToString(DateTime.Now), qtdt.Rows[0][2], Common.CommonFunction.DateTimeToString(Convert.ToDateTime(qtdt.Rows[0][4])), Common.CommonFunction.DateTimeToString(Convert.ToDateTime( qtdt.Rows[0][5]))); } } } #endregion } //適當進行修改 sql.AppendFormat("set sql_safe_updates = 0;");//蓋帳是針對所有的蓋帳 sql.AppendFormat(iinvdidstr); if (!string.IsNullOrEmpty(cbjobQuery.cb_jobid)) { sql.AppendFormat(@"update cbjob_master set sta_id='{0}',create_user='******',create_datetime='{2}' where cbjob_id='{3}' and sta_id='COM' and create_datetime>='{4}' and create_datetime<= '{5}';", "END", cbjobQuery.create_user, Common.CommonFunction.DateTimeToString(cbjobQuery.create_datetime), cbjobQuery.cb_jobid, Common.CommonFunction.DateTimeToString(cbjobQuery.StartDate), Common.CommonFunction.DateTimeToString(cbjobQuery.EndDate)); } else { sql.AppendFormat(@"update cbjob_master set sta_id='{0}',create_user='******',create_datetime='{2}' where sta_id='COM'and create_datetime>='{3}' and create_datetime<= '{4}' ;", "END", cbjobQuery.create_user, Common.CommonFunction.DateTimeToString(cbjobQuery.create_datetime), Common.CommonFunction.DateTimeToString(cbjobQuery.StartDate), Common.CommonFunction.DateTimeToString(cbjobQuery.EndDate)); } sql.AppendFormat("set sql_safe_updates = 1;"); MySqlCommand mySqlCmd = new MySqlCommand(); MySqlConnection mySqlConn = new MySqlConnection(connStr); try { if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed) { mySqlConn.Open(); } mySqlCmd.Connection = mySqlConn; mySqlCmd.Transaction = mySqlConn.BeginTransaction(); mySqlCmd.CommandType = System.Data.CommandType.Text; mySqlCmd.CommandText = sql.ToString(); result = mySqlCmd.ExecuteNonQuery(); mySqlCmd.Transaction.Commit(); } catch (Exception ex) { mySqlCmd.Transaction.Rollback(); throw new Exception("CbjobDetailDao.UpdateCbjobstaid-->" + ex.Message + str.ToString() + sb.ToString() + sql.ToString(), ex); } finally { if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open) { mySqlConn.Close(); } } if (result > 0)//操作成功 { return jbcount; } else//操作失敗 { return result; } }
public int UpdateCbjobMaster(CbjobDetailQuery cbjobQuery) { StringBuilder sql = new StringBuilder(); try {//適當進行修改 sql.AppendFormat("set sql_safe_updates = 0;"); sql.AppendFormat(@"update cbjob_master set sta_id='{0}',create_user='******',create_datetime='{2}' where cbjob_id='{3}';", "UPD", cbjobQuery.create_user, Common.CommonFunction.DateTimeToString(cbjobQuery.create_datetime), cbjobQuery.cb_jobid); sql.AppendFormat("set sql_safe_updates = 1;"); return _access.execCommand(sql.ToString()); } catch (Exception ex) { throw new Exception("CbjobDetailDao-->UpdateCbjobMaster-->" + ex.Message + sql.ToString(), ex); } }
public int insertistocksome(CbjobDetailQuery cbQuery) { string iinvdidstr = string.Empty; int jbcount = 0; int result = 0; StringBuilder sb = new StringBuilder(); StringBuilder str = new StringBuilder(); StringBuilder strsqltwo = new StringBuilder(); try { if (!string.IsNullOrEmpty(cbQuery.cb_jobid)) { str.AppendFormat("select cbjob_id from cbjob_master where cbjob_id='{0}'and status=1 and create_datetime>='{1}' and create_datetime<= '{2}'", cbQuery.cb_jobid, cbQuery.StartDate, cbQuery.EndDate); jbcount = _accessMySql.getDataTable(str.ToString()).Rows.Count; if (jbcount == 0)//程序到此直接跳轉出去 { return -1; } sb.AppendFormat("select cbjob_id from cbjob_master where cbjob_id='{0}' and status=1 and sta_id= '{1}' and create_datetime>='{2}' and create_datetime<= '{3}'", cbQuery.cb_jobid, "COM", cbQuery.StartDate, cbQuery.EndDate); if (_accessMySql.getDataTable(sb.ToString()).Rows.Count == 0)//判斷該工作編號是否能被蓋帳 { return -1; } else//如果該工作編號未被蓋帳,並且處於COM狀態//and invd.st_qty <> 0 { strsqltwo.AppendFormat(@"SELECT cd.iinvd_id,cd.cb_jobid,invd.item_id,sum(invd.prod_qty) as prod_qty,sum(invd.st_qty) as st_qty FROM cbjob_master cm LEFT JOIN cbjob_detail cd on cd.cb_jobid=cm.cbjob_id LEFT JOIN iinvd invd on invd.row_id=cd.iinvd_id WHERE cm.cbjob_id='{0}' and invd.st_qty<> invd.prod_qty and cm.status=1 and sta_id= 'COM' and invd.st_qty <> invd.prod_qty and invd.ista_id='A' and cm.create_datetime>='{1}' and cm.create_datetime<= '{2}' group by invd.item_id;", cbQuery.cb_jobid, cbQuery.StartDate, cbQuery.EndDate); DataTable _dttwo = _accessMySql.getDataTable(strsqltwo.ToString()); if (_dttwo.Rows.Count > 0) { for (int i = 0; i < _dttwo.Rows.Count; i++) { StringBuilder sbstr = new StringBuilder(); sbstr.AppendFormat(" select sum(prod_qty) from iinvd where item_id='{0}' and ista_id='A' ", _dttwo.Rows[i]["item_id"]); DataTable _tdSt = _accessMySql.getDataTable(sbstr.ToString()); IstockChange Icg = new IstockChange(); Icg.sc_trans_type = 4; Icg.sc_time = DateTime.Now; Icg.sc_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id; Icg.sc_trans_id = cbQuery.cb_jobid; Icg.sc_cd_id = string.Empty; Icg.item_id = Convert.ToUInt32(_dttwo.Rows[i]["item_id"]); Icg.sc_note = string.Empty; Icg.sc_num_old = Convert.ToInt32(_dttwo.Rows[i]["prod_qty"]);//總庫存; Icg.sc_num_new = Convert.ToInt32(_dttwo.Rows[i]["st_qty"]); Icg.sc_num_chg = Convert.ToInt32(_dttwo.Rows[i]["st_qty"])-Convert.ToInt32(_dttwo.Rows[i]["prod_qty"]); Icg.sc_num_old = Convert.ToInt32(_tdSt.Rows[0][0]); Icg.sc_num_new = Icg.sc_num_old+ Icg.sc_num_chg; Icg.sc_istock_why = 2;//庫調 iinvdidstr = iinvdidstr + string.Format(@"insert into istock_change(sc_trans_id,sc_cd_id,item_id,sc_trans_type,sc_num_old,sc_num_chg,sc_num_new,sc_time,sc_user,sc_note,sc_istock_why) Values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}');", Icg.sc_trans_id, Icg.sc_cd_id, Icg.item_id, Icg.sc_trans_type, Icg.sc_num_old, Icg.sc_num_chg, Icg.sc_num_new, CommonFunction.DateTimeToString(Icg.sc_time), Icg.sc_user, Icg.sc_note,Icg.sc_istock_why); } } else { return 1; } } } else { #region 其他情況 str.AppendFormat("select cbjob_id from cbjob_master where sta_id= '{0}' and status=1 and create_datetime>='{1}' and create_datetime<= '{2}'", "COM", cbQuery.StartDate, cbQuery.EndDate); jbcount = _accessMySql.getDataTable(str.ToString()).Rows.Count; DataTable _dtresult=_accessMySql.getDataTable(str.ToString()); if (jbcount == 0)//程序到此直接跳轉出去 { return -1;// } else//如果存在可蓋帳的 查詢出可蓋帳的信息 { strsqltwo.AppendFormat(@"SELECT iinvd_id,cb_jobid,item_id,sum(prod_qty) as prod_qty,sum(st_qty) as st_qty FROM(SELECT cd.iinvd_id,cd.cb_jobid,invd.item_id,invd.prod_qty as prod_qty,invd.st_qty as st_qty FROM cbjob_master cm LEFT JOIN cbjob_detail cd on cd.cb_jobid=cm.cbjob_id LEFT JOIN iinvd invd on invd.row_id=cd.iinvd_id WHERE invd.st_qty<> invd.prod_qty and cm.status=1 and sta_id= 'COM'and invd.st_qty <> 0 and invd.st_qty <> invd.prod_qty and invd.ista_id='A' and cm.create_datetime>='{0}' and cm.create_datetime<= '{1}' group by cd.iinvd_id) as tbone GROUP BY item_id;", cbQuery.StartDate, cbQuery.EndDate); DataTable _dttwo = _accessMySql.getDataTable(strsqltwo.ToString()); if (_dttwo.Rows.Count > 0) { for (int i = 0; i < _dttwo.Rows.Count; i++) { StringBuilder sbstr = new StringBuilder(); sbstr.AppendFormat(" select sum(prod_qty) from iinvd where item_id='{0}' and ista_id='A' ", _dttwo.Rows[i]["item_id"]); DataTable _tdSt = _accessMySql.getDataTable(sbstr.ToString()); IstockChange Icg = new IstockChange(); Icg.sc_trans_type = 4; Icg.sc_time = DateTime.Now; Icg.sc_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id; Icg.sc_trans_id = cbQuery.cb_jobid; Icg.sc_cd_id = string.Empty; Icg.item_id = Convert.ToUInt32(_dttwo.Rows[i]["item_id"]); Icg.sc_note = string.Empty; Icg.sc_num_old = Convert.ToInt32(_dttwo.Rows[i]["prod_qty"]);//總庫存; Icg.sc_num_new = Convert.ToInt32(_dttwo.Rows[i]["st_qty"]); Icg.sc_num_chg = Convert.ToInt32(_dttwo.Rows[i]["st_qty"]) - Convert.ToInt32(_dttwo.Rows[i]["prod_qty"]); Icg.sc_num_old = Convert.ToInt32(_tdSt.Rows[0][0]); Icg.sc_num_new = Icg.sc_num_old + Icg.sc_num_chg; Icg.sc_istock_why = 2;//庫調 iinvdidstr = iinvdidstr + string.Format(@"insert into istock_change(sc_trans_id,sc_cd_id,item_id,sc_trans_type,sc_num_old,sc_num_chg,sc_num_new,sc_time,sc_user,sc_note,sc_istock_why) Values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}');", Icg.sc_trans_id, Icg.sc_cd_id, Icg.item_id, Icg.sc_trans_type, Icg.sc_num_old, Icg.sc_num_chg, Icg.sc_num_new, CommonFunction.DateTimeToString(Icg.sc_time), Icg.sc_user, Icg.sc_note, Icg.sc_istock_why); } } else { return 1; } } #endregion } MySqlCommand mySqlCmd = new MySqlCommand(); MySqlConnection mySqlConn = new MySqlConnection(connStr); try { if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed) { mySqlConn.Open(); } mySqlCmd.Connection = mySqlConn; mySqlCmd.Transaction = mySqlConn.BeginTransaction(); mySqlCmd.CommandType = System.Data.CommandType.Text; mySqlCmd.CommandText = iinvdidstr.ToString(); result = mySqlCmd.ExecuteNonQuery(); mySqlCmd.Transaction.Commit(); } catch (Exception ex) { mySqlCmd.Transaction.Rollback(); throw new Exception("IstockChangeDao.insertistocksome-->" + ex.Message + str.ToString() + iinvdidstr.ToString(), ex); } finally { if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open) { mySqlConn.Close(); } } if (result > 0)//操作成功 { return jbcount; } else//操作失敗 { return result; } } catch (Exception ex) { throw new Exception("IstockChangeDao-->insertistocksome-->" + ex.Message + iinvdidstr.ToString(), ex); } }