/// <summary> /// 增加一条数据 /// </summary> public long Add(StockListModel model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into StockList("); strSql.Append("ManaTaskID,StoreHouseName,StockID,ProductCode,ProductNum,ProductStatus,ProductFrameCode,ProductName,GoodsSiteName,ProductBatchNum,InHouseTime,UpdateTime,Remarks)"); strSql.Append(" values ("); strSql.Append("@ManaTaskID,@StoreHouseName,@StockID,@ProductCode,@ProductNum,@ProductStatus,@ProductFrameCode,@ProductName,@GoodsSiteName,@ProductBatchNum,@InHouseTime,@UpdateTime,@Remarks)"); strSql.Append(";select @@IDENTITY"); SqlParameter[] parameters = { new SqlParameter("@ManaTaskID", SqlDbType.BigInt, 8), new SqlParameter("@StoreHouseName", SqlDbType.NVarChar, 20), new SqlParameter("@StockID", SqlDbType.BigInt, 8), new SqlParameter("@ProductCode", SqlDbType.NVarChar, 50), new SqlParameter("@ProductNum", SqlDbType.Int, 4), new SqlParameter("@ProductStatus", SqlDbType.NVarChar, 50), new SqlParameter("@ProductFrameCode", SqlDbType.NVarChar, 50), new SqlParameter("@ProductName", SqlDbType.NVarChar, 20), new SqlParameter("@GoodsSiteName", SqlDbType.NVarChar, 20), new SqlParameter("@ProductBatchNum", SqlDbType.NVarChar, 50), new SqlParameter("@InHouseTime", SqlDbType.DateTime), new SqlParameter("@UpdateTime", SqlDbType.DateTime), new SqlParameter("@Remarks", SqlDbType.NVarChar, 100) }; parameters[0].Value = model.ManaTaskID; parameters[1].Value = model.StoreHouseName; parameters[2].Value = model.StockID; parameters[3].Value = model.ProductCode; parameters[4].Value = model.ProductNum; parameters[5].Value = model.ProductStatus; parameters[6].Value = model.ProductFrameCode; parameters[7].Value = model.ProductName; parameters[8].Value = model.GoodsSiteName; parameters[9].Value = model.ProductBatchNum; parameters[10].Value = model.InHouseTime; parameters[11].Value = model.UpdateTime; parameters[12].Value = model.Remarks; object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters); if (obj == null) { return(0); } else { return(Convert.ToInt64(obj)); } }
/// <summary> /// 得到一个对象实体 /// </summary> public StockListModel GetModel(long StockListID) { StringBuilder strSql = new StringBuilder(); strSql.Append("select top 1 StockListID,ManaTaskID,StoreHouseName,StockID,ProductCode,ProductNum,ProductStatus,ProductFrameCode,ProductName,GoodsSiteName,ProductBatchNum,InHouseTime,UpdateTime,Remarks from StockList "); strSql.Append(" where StockListID=@StockListID"); SqlParameter[] parameters = { new SqlParameter("@StockListID", SqlDbType.BigInt) }; parameters[0].Value = StockListID; StockListModel model = new StockListModel(); DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { return(DataRowToModel(ds.Tables[0].Rows[0])); } else { return(null); } }
/// <summary> /// 更新一条数据 /// </summary> public bool Update(StockListModel model) { return(dal.Update(model)); }
/// <summary> /// 增加一条数据 /// </summary> public long Add(StockListModel model) { return(dal.Add(model)); }
/// <summary> /// 获取符合条件自动出库库存列表 /// </summary> /// <param name="productStatus">物料状态</param> /// <param name="timeInterval">当前时间减去更新时间的时间差(单位小时)</param> /// <returns></returns> public DataTable GetTimeArriveModelList(string productStatus, int timeInterval, Dictionary <string, List <string> > outStorageBatchDic) { if (outStorageBatchDic == null || outStorageBatchDic.Count == 0) { return(null); } string batchNumSqlA1 = ""; string batchNumSqlB1 = ""; for (int i = 0; i < outStorageBatchDic.Keys.Count; i++) { string houseName = outStorageBatchDic.Keys.ElementAt(i); List <string> batchesList = outStorageBatchDic[houseName]; switch (houseName) { case "A1库房": for (int a = 0; a < batchesList.Count; a++) { if (a == 0) { batchNumSqlA1 += "(ProductBatchNum ='" + batchesList[a] + "'"; } else { batchNumSqlA1 += "or ProductBatchNum='" + batchesList[a] + "'"; } } batchNumSqlA1 += "and StoreHouseName = '" + houseName + "')"; break; case "B1库房": for (int b = 0; b < batchesList.Count; b++) { if (b == 0) { batchNumSqlB1 += "(ProductBatchNum ='" + batchesList[b] + "'"; } else { batchNumSqlB1 += "or ProductBatchNum='" + batchesList[b] + "'"; } } batchNumSqlB1 += "and StoreHouseName = '" + houseName + "')"; break; } } string dtNow = DateTime.Now.ToString("yyyy-MM-dd HH:mm:dd"); StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT StockList .* FROM GoodsSite INNER JOIN Stock ON GoodsSite.GoodsSiteID = Stock.GoodsSiteID INNER JOIN StockList ON Stock.StockID = StockList.StockID "); strSql.Append(" where GoodsSiteStoreStatus='有货' and GoodsSiteRunStatus = '任务完成' and"); strSql.Append(" ProductStatus = '" + productStatus + "' and datediff(hour,UpdateTime,'" + dtNow + "') >=" + timeInterval + " and UpdateTime != '' and (" + batchNumSqlA1 + "or" + batchNumSqlB1 + ")"); StockListModel model = new StockListModel(); DataSet ds = DbHelperSQL.Query(strSql.ToString()); if (ds.Tables[0].Rows.Count > 0) { return(ds.Tables[0]); } else { return(null); } }
/// <summary> /// 得到一个对象实体 /// </summary> public StockListModel DataRowToModel(DataRow row) { StockListModel model = new StockListModel(); if (row != null) { if (row["StockListID"] != null && row["StockListID"].ToString() != "") { model.StockListID = long.Parse(row["StockListID"].ToString()); } if (row["ManaTaskID"] != null && row["ManaTaskID"].ToString() != "") { model.ManaTaskID = long.Parse(row["ManaTaskID"].ToString()); } if (row["StoreHouseName"] != null) { model.StoreHouseName = row["StoreHouseName"].ToString(); } if (row["StockID"] != null && row["StockID"].ToString() != "") { model.StockID = long.Parse(row["StockID"].ToString()); } if (row["ProductCode"] != null) { model.ProductCode = row["ProductCode"].ToString(); } if (row["ProductNum"] != null && row["ProductNum"].ToString() != "") { model.ProductNum = int.Parse(row["ProductNum"].ToString()); } if (row["ProductStatus"] != null) { model.ProductStatus = row["ProductStatus"].ToString(); } if (row["ProductFrameCode"] != null) { model.ProductFrameCode = row["ProductFrameCode"].ToString(); } if (row["ProductName"] != null) { model.ProductName = row["ProductName"].ToString(); } if (row["GoodsSiteName"] != null) { model.GoodsSiteName = row["GoodsSiteName"].ToString(); } if (row["ProductBatchNum"] != null) { model.ProductBatchNum = row["ProductBatchNum"].ToString(); } if (row["InHouseTime"] != null && row["InHouseTime"].ToString() != "") { model.InHouseTime = DateTime.Parse(row["InHouseTime"].ToString()); } if (row["UpdateTime"] != null && row["UpdateTime"].ToString() != "") { model.UpdateTime = DateTime.Parse(row["UpdateTime"].ToString()); } if (row["Remarks"] != null) { model.Remarks = row["Remarks"].ToString(); } } return(model); }
/// <summary> /// 更新一条数据 /// </summary> public bool Update(StockListModel model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update StockList set "); strSql.Append("ManaTaskID=@ManaTaskID,"); strSql.Append("StoreHouseName=@StoreHouseName,"); strSql.Append("StockID=@StockID,"); strSql.Append("ProductCode=@ProductCode,"); strSql.Append("ProductNum=@ProductNum,"); strSql.Append("ProductStatus=@ProductStatus,"); strSql.Append("ProductFrameCode=@ProductFrameCode,"); strSql.Append("ProductName=@ProductName,"); strSql.Append("GoodsSiteName=@GoodsSiteName,"); strSql.Append("ProductBatchNum=@ProductBatchNum,"); strSql.Append("InHouseTime=@InHouseTime,"); strSql.Append("UpdateTime=@UpdateTime,"); strSql.Append("Remarks=@Remarks"); strSql.Append(" where StockListID=@StockListID"); SqlParameter[] parameters = { new SqlParameter("@ManaTaskID", SqlDbType.BigInt, 8), new SqlParameter("@StoreHouseName", SqlDbType.NVarChar, 20), new SqlParameter("@StockID", SqlDbType.BigInt, 8), new SqlParameter("@ProductCode", SqlDbType.NVarChar, 50), new SqlParameter("@ProductNum", SqlDbType.Int, 4), new SqlParameter("@ProductStatus", SqlDbType.NVarChar, 50), new SqlParameter("@ProductFrameCode", SqlDbType.NVarChar, 50), new SqlParameter("@ProductName", SqlDbType.NVarChar, 20), new SqlParameter("@GoodsSiteName", SqlDbType.NVarChar, 20), new SqlParameter("@ProductBatchNum", SqlDbType.NVarChar, 50), new SqlParameter("@InHouseTime", SqlDbType.DateTime), new SqlParameter("@UpdateTime", SqlDbType.DateTime), new SqlParameter("@Remarks", SqlDbType.NVarChar, 100), new SqlParameter("@StockListID", SqlDbType.BigInt, 8) }; parameters[0].Value = model.ManaTaskID; parameters[1].Value = model.StoreHouseName; parameters[2].Value = model.StockID; parameters[3].Value = model.ProductCode; parameters[4].Value = model.ProductNum; parameters[5].Value = model.ProductStatus; parameters[6].Value = model.ProductFrameCode; parameters[7].Value = model.ProductName; parameters[8].Value = model.GoodsSiteName; parameters[9].Value = model.ProductBatchNum; parameters[10].Value = model.InHouseTime; parameters[11].Value = model.UpdateTime; parameters[12].Value = model.Remarks; parameters[13].Value = model.StockListID; int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return(true); } else { return(false); } }