/// <summary> /// 增加一条数据 /// </summary> /// <param name="model">model</param> public int AddRecord(TSPickOutPlanData model) { StringBuilder strSql = new StringBuilder(); strSql.Append("set nocount on; "); strSql.Append("insert into TSPickOutPlan("); strSql.Append(@"stockUpBillNO,wareNo,wareLocatorNo,palletNo,financeBillNo,stockUpMaterialNo,materialNo,pickOutEmpId,pickOutDt,instantState, isrtEmpId,isrtDt,num,tsOutBillNo)"); strSql.Append(" values ("); strSql.Append(@"@stockUpBillNO,@wareNo,@wareLocatorNo,@palletNo,@financeBillNo,@stockUpMaterialNo,@materialNo,@pickOutEmpId,@pickOutDt,@instantState, @isrtEmpId,@isrtDt,@num,@tsOutBillNo)"); strSql.Append("; select @@identity; set nocount off; "); SqlParameter[] parameters = { new SqlParameter("@stockUpBillNO", SqlDbType.NVarChar,20), new SqlParameter("@wareNo", SqlDbType.NVarChar,20), new SqlParameter("@wareLocatorNo", SqlDbType.NVarChar,20), new SqlParameter("@palletNo", SqlDbType.NVarChar,20), new SqlParameter("@financeBillNo", SqlDbType.NVarChar,50), new SqlParameter("@stockUpMaterialNo", SqlDbType.NVarChar,20), new SqlParameter("@materialNo", SqlDbType.NVarChar,20), new SqlParameter("@pickOutEmpId", SqlDbType.Int), new SqlParameter("@pickOutDt", SqlDbType.DateTime), new SqlParameter("@instantState", SqlDbType.NChar,10), new SqlParameter("@isrtEmpId", SqlDbType.Int), new SqlParameter("@isrtDt", SqlDbType.DateTime), new SqlParameter("@num", SqlDbType.Float), new SqlParameter("@tsOutBillNo", SqlDbType.NVarChar,50) }; parameters[0].Value = model.stockUpBillNO; parameters[1].Value = model.wareNo; parameters[2].Value = model.wareLocatorNo; parameters[3].Value = model.palletNo; parameters[4].Value = model.financeBillNo; parameters[5].Value = model.stockUpMaterialNo; parameters[6].Value = model.materialNo; parameters[7].Value = model.pickOutEmpId; parameters[8].Value = model.pickOutDt == string.Empty ? null : model.pickOutDt; parameters[9].Value = model.instantState; parameters[10].Value = model.isrtEmpId; parameters[11].Value = model.isrtDt == string.Empty ? null : model.isrtDt; parameters[12].Value = model.num; parameters[13].Value = model.tsOutBillNo; int id = 0; try { object ret = SqlHelper.ExecuteScalar(this.connection, this.transaction, CommandType.Text, strSql.ToString(), parameters); if (ret != null && ret != DBNull.Value) { id = Convert.ToInt32(ret); } } catch (Exception ex) { throw ex; } return id; }
/// <summary> /// 2015-09-16 增加保存 /// </summary> /// <param name="dtPlan"></param> /// <param name="strStockUpBillNo"></param> /// <param name="issubmit"></param> /// <returns></returns> public bool SaveTSPickOutPlanBill(DataTable dtPlan, string strStockUpBillNo,string tsOutBillNo, bool issubmit) { bool ret = false; UStockBB stockBB = new UStockBB(this.connection); SCommBB commBB = new SCommBB(this.connection); SqlTransaction trans = null; try { if (this.transaction == null) { trans = this.connection.BeginTransaction("TranSave"); this.sPickOutPlanBB.Transaction = trans; stockBB.Transaction = trans; commBB.Transaction = trans; } else { this.sPickOutPlanBB.Transaction = this.transaction; stockBB.Transaction = this.transaction; commBB.Transaction = this.transaction; } TSPickOutPlanData pickOutPlanModel = new TSPickOutPlanData(); TSPickOutPlanBB stockUpBillNo = new TSPickOutPlanBB(); string stockUpBillNOValue = stockUpBillNo.GetCurrentInBillNoByStockUpBillNoByQT(); #region 逐条保存拣货计划 //逐条保存拣货计划 foreach (DataRow dr in dtPlan.Rows) { string strWareLocatorNo = "", strMaterialNo = "", strWhere = "", strSql = ""; float num = 0; DataTable dtStock = new DataTable(); // stockUpBillNo.DeleteRecord(int.Parse(dr["id"].ToString())); strWareLocatorNo = dr["wareLocatorNo"].ToString();//库位 strMaterialNo = dr["materialNo"].ToString();//物料编号 num = Convert.ToSingle(dr["num"]);//数量 pickOutPlanModel = new TSPickOutPlanData(); pickOutPlanModel.stockUpBillNO = stockUpBillNOValue;//获取其他备货单号 pickOutPlanModel.wareNo = dr["wareNo"].ToString(); pickOutPlanModel.wareLocatorNo = dr["wareLocatorNo"].ToString(); pickOutPlanModel.palletNo = dr["palletNo"].ToString(); pickOutPlanModel.stockUpMaterialNo = dr["stockUpMaterialNo"].ToString();//备货物料 pickOutPlanModel.materialNo = dr["materialNo"].ToString();//实际拣货物料 pickOutPlanModel.num = Convert.ToInt32(dr["num"]); pickOutPlanModel.financeBillNo = dr["financeBillNo"].ToString();//采购合同号 pickOutPlanModel.tsOutBillNo = tsOutBillNo; //pickOutPlanModel.financeBillSort = dr["financeBillSort"].ToString();//采购合同类型 pickOutPlanModel.isrtDt = System.DateTime.Now.ToString(); pickOutPlanModel.isrtEmpId = this.empId; if (issubmit) { pickOutPlanModel.instantState = "02"; } else { pickOutPlanModel.instantState = "01"; } this.sPickOutPlanBB.AddRecord(pickOutPlanModel); ret = true; } string command = "Update TSoutBIll set StockUpbillNo = '" + stockUpBillNOValue + "',InstantState = '02' where TSoutBill = '" + tsOutBillNo + "'"; SQLHelperDM.ExecuteNonQuery(HS.Config.SqlDataObject.GetJoeyConnectionString, command); stockUpBillNo.Dispose(); #endregion 保存拣货计划 } catch { } try { if (this.transaction == null) trans.Commit(); } catch { ret = false; } finally { stockBB.Dispose(); commBB.Dispose(); sPickOutPlanBB.Dispose(); } return ret; }
/// <summary> /// 更新一条"TSPickOutPlan"信息 /// </summary> /// <param name="model">model</param> public bool ModifyRecord(TSPickOutPlanData model) { bool ret = false; SqlTransaction trans = null; try { if (this.transaction == null) { trans = this.connection.BeginTransaction("TransModify"); this.sPickOutPlanBB.Transaction = trans; this.errorDiaryBB.Transaction = trans; this.operatDiaryBB.Transaction = trans; } this.sPickOutPlanBB.ModifyRecord(model); SOperatDiaryData operatDiaryData = new SOperatDiaryData(); operatDiaryData.empId = this.empId; operatDiaryData.functionId = ""; operatDiaryData.recordId = model.id.ToString(); operatDiaryData.operateContent = "TSPickOutPlan修改一条id为“" + model.id.ToString() + "”的记录"; this.operatDiaryBB.AddRecord(operatDiaryData); if (this.transaction == null) trans.Commit(); ret = true; } catch (Exception ex) { if (this.transaction == null) trans.Rollback("TransModify"); SErrorDiaryData errorDiaryData = new SErrorDiaryData(); errorDiaryData.empId = this.empId; errorDiaryData.functionId = ""; errorDiaryData.errorText = "TSPickOutPlan修改记录“" + model.id.ToString() + "”时报错:" + ex.Message; this.errorDiaryBB.AddRecord(errorDiaryData); throw ex; } finally { } return ret; }
/// <summary> /// 增加一条"TSPickOutPlan"信息 /// </summary> /// <param name="model">model</param> public int AddRecord(TSPickOutPlanData model) { int id = 0; SqlTransaction trans = null; try { if (this.transaction == null) { trans = this.connection.BeginTransaction("TransAdd"); this.sPickOutPlanBB.Transaction = trans; this.errorDiaryBB.Transaction = trans; this.operatDiaryBB.Transaction = trans; } id = this.sPickOutPlanBB.AddRecord(model); SOperatDiaryData operatDiaryData = new SOperatDiaryData(); operatDiaryData.empId = this.empId; operatDiaryData.functionId = ""; operatDiaryData.recordId = id.ToString(); operatDiaryData.operateContent = "TSPickOutPlan增加一条id为“" + id.ToString() + "”的记录"; this.operatDiaryBB.AddRecord(operatDiaryData); if (this.transaction == null) trans.Commit(); } catch (Exception ex) { if (this.transaction == null) trans.Rollback("TransAdd"); SErrorDiaryData errorDiaryData = new SErrorDiaryData(); errorDiaryData.empId = this.empId; errorDiaryData.functionId = ""; errorDiaryData.errorText = "TSPickOutPlan增加记录时报错:" + ex.Message; this.errorDiaryBB.AddRecord(errorDiaryData); throw ex; } finally { } return id; }
/// <summary> /// 更新一条数据 /// </summary> /// <param name="model">model</param> public bool ModifyRecord(TSPickOutPlanData model) { return this.sPickOutPlanDB.ModifyRecord(model); }
/// <summary> /// 增加一条数据 /// </summary> /// <param name="model">model</param> public int AddRecord(TSPickOutPlanData model) { return this.sPickOutPlanDB.AddRecord(model); }
/// <summary> /// 更新一条数据 /// </summary> /// <param name="model">model</param> public bool ModifyRecord(TSPickOutPlanData model) { bool ret = false; StringBuilder strSql = new StringBuilder(); strSql.Append("update TSPickOutPlan set "); strSql.Append("stockUpBillNO=@stockUpBillNO,"); strSql.Append("wareNo=@wareNo,"); strSql.Append("wareLocatorNo=@wareLocatorNo,"); strSql.Append("palletNo=@palletNo,"); strSql.Append("financeBillNo=@financeBillNo,"); strSql.Append("stockUpMaterialNo=@stockUpMaterialNo,"); strSql.Append("materialNo=@materialNo,"); strSql.Append("pickOutEmpId=@pickOutEmpId,"); strSql.Append("pickOutDt=@pickOutDt,"); strSql.Append("instantState=@instantState,"); strSql.Append("isrtEmpId=@isrtEmpId,"); strSql.Append("isrtDt=@isrtDt"); strSql.Append(" where id = @id "); SqlParameter[] parameters = { new SqlParameter("@stockUpBillNO", SqlDbType.NVarChar,20), new SqlParameter("@wareNo", SqlDbType.NVarChar,20), new SqlParameter("@wareLocatorNo", SqlDbType.NVarChar,20), new SqlParameter("@palletNo", SqlDbType.NVarChar,20), new SqlParameter("@financeBillNo", SqlDbType.NVarChar,50), new SqlParameter("@stockUpMaterialNo", SqlDbType.NVarChar,20), new SqlParameter("@materialNo", SqlDbType.NVarChar,20), new SqlParameter("@pickOutEmpId", SqlDbType.Int), new SqlParameter("@pickOutDt", SqlDbType.DateTime), new SqlParameter("@instantState", SqlDbType.NChar,10), new SqlParameter("@isrtEmpId", SqlDbType.Int), new SqlParameter("@isrtDt", SqlDbType.DateTime), new SqlParameter("@id",SqlDbType.Int) }; parameters[0].Value = model.stockUpBillNO; parameters[1].Value = model.wareNo; parameters[2].Value = model.wareLocatorNo; parameters[3].Value = model.palletNo; parameters[4].Value = model.financeBillNo; parameters[5].Value = model.stockUpMaterialNo; parameters[6].Value = model.materialNo; parameters[7].Value = model.pickOutEmpId; parameters[8].Value = model.pickOutDt == string.Empty ? null : model.pickOutDt; parameters[9].Value = model.instantState; parameters[10].Value = model.isrtEmpId; parameters[11].Value = model.isrtDt == string.Empty ? null : model.isrtDt; parameters[12].Value = model.id; try { SqlHelper.ExecuteNonQuery(this.connection, this.transaction, CommandType.Text, strSql.ToString(), parameters); ret = true; } catch (Exception ex) { throw ex; } return ret; }
/// <summary> /// 得到一个model /// </summary> /// <returns>model</returns> public TSPickOutPlanData GetModel(int id) { StringBuilder strSql = new StringBuilder(); strSql.Append(@"select id,stockUpBillNO,wareNo,wareLocatorNo,palletNo,financeBillNo,stockUpMaterialNo,materialNo,pickOutEmpId,pickOutDt,instantState, isrtEmpId,isrtDt from TSPickOutPlan"); strSql.Append(" where id = @id "); SqlParameter[] parameters = { new SqlParameter("@id", SqlDbType.Int) }; parameters[0].Value = id; TSPickOutPlanData model = new TSPickOutPlanData(); DataSet ds = SqlHelper.ExecuteDataset(this.connection, this.transaction, CommandType.Text, strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { DataRow row = ds.Tables[0].Rows[0]; if (row["id"] != DBNull.Value) { model.id = Convert.ToInt32(row["id"]); } if (row["stockUpBillNO"] != DBNull.Value) { model.stockUpBillNO = Convert.ToString(row["stockUpBillNO"]); } if (row["wareNo"] != DBNull.Value) { model.wareNo = Convert.ToString(row["wareNo"]); } if (row["wareLocatorNo"] != DBNull.Value) { model.wareLocatorNo = Convert.ToString(row["wareLocatorNo"]); } if (row["palletNo"] != DBNull.Value) { model.palletNo = Convert.ToString(row["palletNo"]); } if (row["financeBillNo"] != DBNull.Value) { model.financeBillNo = Convert.ToString(row["financeBillNo"]); } if (row["stockUpMaterialNo"] != DBNull.Value) { model.stockUpMaterialNo = Convert.ToString(row["stockUpMaterialNo"]); } if (row["materialNo"] != DBNull.Value) { model.materialNo = Convert.ToString(row["materialNo"]); } if (row["pickOutEmpId"] != DBNull.Value) { model.pickOutEmpId = Convert.ToInt32(row["pickOutEmpId"]); } if (row["pickOutDt"] != DBNull.Value) { model.pickOutDt = Convert.ToString(row["pickOutDt"]); } if (row["instantState"] != DBNull.Value) { model.instantState = Convert.ToString(row["instantState"]); } if (row["isrtEmpId"] != DBNull.Value) { model.isrtEmpId = Convert.ToInt32(row["isrtEmpId"]); } if (row["isrtDt"] != DBNull.Value) { model.isrtDt = Convert.ToString(row["isrtDt"]); } return model; } else { return null; } }
public void FinishTSPickOutPlan(int pickOutPlanId, int pickOutEmpId) { TSPickOutPlanBB pickOutPlanBB = new TSPickOutPlanBB(); SCommBB commBB = new SCommBB(); try { StringBuilder strSql = new StringBuilder(); TSPickOutPlanData pickOutPlanModel = new TSPickOutPlanData(); pickOutPlanModel = pickOutPlanBB.GetModel(pickOutPlanId); pickOutPlanModel.pickOutDt = System.DateTime.Now.ToString();//拣货时间 pickOutPlanModel.pickOutEmpId = pickOutEmpId;//拣货人 pickOutPlanModel.instantState = "03";//已完成 pickOutPlanBB.ModifyRecord(pickOutPlanModel); ////解除库位的锁定 //strSql.Append(@"update dbo.UStock set isOutStocking=0 where wareLocatorNo='" + pickOutPlanModel.wareLocatorNo // + "' and materialNo='" + pickOutPlanModel.materialNo + "'and isOutStocking=1"); //commBB.ExecuteSql(strSql.ToString()); ////更改原箱的是否锁定状态 //strSql = new StringBuilder(); //strSql.Append("update dbo.BArrangeBillBox set isOutStocking=0 where wareLocatorNo='" + pickOutPlanModel.wareLocatorNo // + "' and materialNo='" + pickOutPlanModel.materialNo + "' and isOutStocking=1"); //commBB.ExecuteSql(strSql.ToString()); } finally { pickOutPlanBB.Dispose(); commBB.Dispose(); } }