private IList <StockWarningInfo> GetStockWarningList(List <Guid> realGoodsIds, Guid warehouseId, Guid hostingFilialeId, int days) { var now = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day); var totalSales = _stockWarningDao.GetGoodsDaySalesInfos(realGoodsIds, warehouseId, now.AddDays(-110), now, hostingFilialeId); //计算前第一个备货周期的销售额 var childSaleList1 = totalSales.Where(act => act.DayTime >= now.AddDays(-30) && act.DayTime < now); //计算前第二个备货周期的销售额 var childSaleList2 = totalSales.Where(act => act.DayTime >= now.AddDays(-60) && act.DayTime < now.AddDays(-30)); //计算前第三个备货周期的销售额 var childSaleList3 = totalSales.Where(act => act.DayTime >= now.AddDays(-90) && act.DayTime < now.AddDays(-60)); var stockWarningInfos = new List <StockWarningInfo>(); var saleDays = _stockWarningDao.GetSaleDays(realGoodsIds, warehouseId, now, hostingFilialeId); foreach (var guid in realGoodsIds) { double count1 = childSaleList1.Where(act => act.RealGoodsID == guid).Sum(act => act.GoodsSales); double count2 = childSaleList2.Where(act => act.RealGoodsID == guid).Sum(act => act.GoodsSales); double count3 = childSaleList3.Where(act => act.RealGoodsID == guid).Sum(act => act.GoodsSales); var preStepSales = GetPreStepSales(totalSales, guid, now, saleDays.ContainsKey(guid) ? saleDays[guid] : 0); var newStockWarning = new StockWarningInfo { GoodsId = guid, FirstNumberOneStockUpSale = count1, FirstNumberTwoStockUpSale = count2, FirstNumberThreeStockUpSale = count3, StockDay = days }; newStockWarning.SetStepSales(preStepSales); stockWarningInfos.Add(newStockWarning); } return(stockWarningInfos); }
/// <summary> /// 获取指定时间范围内指定公司某产品下具体产品的库存预警信息 /// 最后修改时间:2010/11/8 17:43 by wh /// </summary> /// <param name="warehouseId"></param> /// <param name="hostingFilialeId"></param> /// <param name="realGoodsList">子商品集合</param> /// <param name="days"> </param> /// <returns></returns> public IList <StockWarningInfo> GetStockWarningList(Guid warehouseId, Guid hostingFilialeId, List <Guid> realGoodsList, int days) { IList <StockWarningInfo> stockWarningList = new List <StockWarningInfo>(); if (realGoodsList.Count > 0) { var strbGoodsIds = new StringBuilder(string.Empty); for (int i = 0; i < realGoodsList.Count; i++) { if (i == 0) { strbGoodsIds.Append(realGoodsList[i]); } else { strbGoodsIds.Append((i + 1) % 100 == 0 ? "&" : ","); strbGoodsIds.Append(realGoodsList[i]); } } var parms = new[] { new SqlParameter(PARM_WAREHOUSEID, SqlDbType.UniqueIdentifier), new SqlParameter(PARM_GOODSID, SqlDbType.VarChar), new SqlParameter("@FilialeId", SqlDbType.UniqueIdentifier), }; parms[0].Value = warehouseId; parms[1].Value = strbGoodsIds.ToString(); parms[2].Value = hostingFilialeId; using (var rdr = SqlHelper.ExecuteReaderSP(GlobalConfig.ERP_DB_NAME, false, CommandType.StoredProcedure, "P_StockStatistics", parms)) { while (rdr.Read()) { var stockWarningInfo = new StockWarningInfo { GoodsId = rdr.GetGuid(0), FirstNumberThreeStockUpSale = Convert.ToInt32(rdr[1]), FirstNumberTwoStockUpSale = Convert.ToInt32(rdr[2]), FirstNumberOneStockUpSale = Convert.ToInt32(rdr[3]), SubtractPurchasingQuantity = Convert.ToInt32(rdr[4]), StockDay = days }; stockWarningList.Add(stockWarningInfo); } } } return(stockWarningList); }
public IList <StockWarningInfo> GetList(int pageIndex, int pageSize, string sqlWhere, params SqlParameter[] cmdParms) { StringBuilder sb = new StringBuilder(500); int startIndex = (pageIndex - 1) * pageSize + 1; int endIndex = pageIndex * pageSize; sb.Append(@"select * from(select row_number() over(order by Sort) as RowNumber, Id,UserId,ZoneId,StockLocationId,Coded,ZoneProperty,StockLocationProperty,StockAmount,OverdueDay,MinQty,MaxQty,Remark,Sort,IsDisable,LastUpdatedDate from StockWarning " ); if (!string.IsNullOrEmpty(sqlWhere)) { sb.AppendFormat(" where 1=1 {0} ", sqlWhere); } sb.AppendFormat(@")as objTable where RowNumber between {0} and {1} ", startIndex, endIndex); IList <StockWarningInfo> list = new List <StockWarningInfo>(); using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString(), cmdParms)) { if (reader != null && reader.HasRows) { while (reader.Read()) { StockWarningInfo model = new StockWarningInfo(); model.Id = reader.GetGuid(1); model.UserId = reader.GetGuid(2); model.ZoneId = reader.GetGuid(3); model.StockLocationId = reader.GetGuid(4); model.Coded = reader.GetString(5); model.ZoneProperty = reader.GetString(6); model.StockLocationProperty = reader.GetString(7); model.StockAmount = reader.GetDecimal(8); model.OverdueDay = reader.GetInt32(9); model.MinQty = reader.GetDouble(10); model.MaxQty = reader.GetDouble(11); model.Remark = reader.GetString(12); model.Sort = reader.GetInt32(13); model.IsDisable = reader.GetBoolean(14); model.LastUpdatedDate = reader.GetDateTime(15); list.Add(model); } } } return(list); }
public int InsertByOutput(StockWarningInfo model) { StringBuilder sb = new StringBuilder(300); sb.Append(@"insert into StockWarning (Id,UserId,ZoneId,StockLocationId,Coded,ZoneProperty,StockLocationProperty,StockAmount,OverdueDay,MinQty,MaxQty,Remark,Sort,IsDisable,LastUpdatedDate) values (@Id,@UserId,@ZoneId,@StockLocationId,@Coded,@ZoneProperty,@StockLocationProperty,@StockAmount,@OverdueDay,@MinQty,@MaxQty,@Remark,@Sort,@IsDisable,@LastUpdatedDate) " ); SqlParameter[] parms = { new SqlParameter("@Id", SqlDbType.UniqueIdentifier), new SqlParameter("@UserId", SqlDbType.UniqueIdentifier), new SqlParameter("@ZoneId", SqlDbType.UniqueIdentifier), new SqlParameter("@StockLocationId", SqlDbType.UniqueIdentifier), new SqlParameter("@Coded", SqlDbType.VarChar, 36), new SqlParameter("@ZoneProperty", SqlDbType.NVarChar, 20), new SqlParameter("@StockLocationProperty", SqlDbType.NVarChar, 20), new SqlParameter("@StockAmount", SqlDbType.Decimal), new SqlParameter("@OverdueDay", SqlDbType.Int), new SqlParameter("@MinQty", SqlDbType.Float), new SqlParameter("@MaxQty", SqlDbType.Float), new SqlParameter("@Remark", SqlDbType.NVarChar, 100), new SqlParameter("@Sort", SqlDbType.Int), new SqlParameter("@IsDisable", SqlDbType.Bit), new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime) }; parms[0].Value = model.Id; parms[1].Value = model.UserId; parms[2].Value = model.ZoneId; parms[3].Value = model.StockLocationId; parms[4].Value = model.Coded; parms[5].Value = model.ZoneProperty; parms[6].Value = model.StockLocationProperty; parms[7].Value = model.StockAmount; parms[8].Value = model.OverdueDay; parms[9].Value = model.MinQty; parms[10].Value = model.MaxQty; parms[11].Value = model.Remark; parms[12].Value = model.Sort; parms[13].Value = model.IsDisable; parms[14].Value = model.LastUpdatedDate; return(SqlHelper.ExecuteNonQuery(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString(), parms)); }
public StockWarningInfo GetModel(Guid id) { StockWarningInfo model = null; StringBuilder sb = new StringBuilder(300); sb.Append(@"select top 1 Id,UserId,ZoneId,StockLocationId,Coded,ZoneProperty,StockLocationProperty,StockAmount,OverdueDay,MinQty,MaxQty,Remark,Sort,IsDisable,LastUpdatedDate from StockWarning where Id = @Id " ); SqlParameter[] parms = { new SqlParameter("@Id", SqlDbType.UniqueIdentifier) }; parms[0].Value = id; using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString(), parms)) { if (reader != null) { if (reader.Read()) { model = new StockWarningInfo(); model.Id = reader.GetGuid(0); model.UserId = reader.GetGuid(1); model.ZoneId = reader.GetGuid(2); model.StockLocationId = reader.GetGuid(3); model.Coded = reader.GetString(4); model.ZoneProperty = reader.GetString(5); model.StockLocationProperty = reader.GetString(6); model.StockAmount = reader.GetDecimal(7); model.OverdueDay = reader.GetInt32(8); model.MinQty = reader.GetDouble(9); model.MaxQty = reader.GetDouble(10); model.Remark = reader.GetString(11); model.Sort = reader.GetInt32(12); model.IsDisable = reader.GetBoolean(13); model.LastUpdatedDate = reader.GetDateTime(14); } } } return(model); }
public IList <StockWarningInfo> GetList() { StringBuilder sb = new StringBuilder(300); sb.Append(@"select Id,UserId,ZoneId,StockLocationId,Coded,ZoneProperty,StockLocationProperty,StockAmount,OverdueDay,MinQty,MaxQty,Remark,Sort,IsDisable,LastUpdatedDate from StockWarning order by Sort " ); IList <StockWarningInfo> list = new List <StockWarningInfo>(); using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString())) { if (reader != null && reader.HasRows) { while (reader.Read()) { StockWarningInfo model = new StockWarningInfo(); model.Id = reader.GetGuid(0); model.UserId = reader.GetGuid(1); model.ZoneId = reader.GetGuid(2); model.StockLocationId = reader.GetGuid(3); model.Coded = reader.GetString(4); model.ZoneProperty = reader.GetString(5); model.StockLocationProperty = reader.GetString(6); model.StockAmount = reader.GetDecimal(7); model.OverdueDay = reader.GetInt32(8); model.MinQty = reader.GetDouble(9); model.MaxQty = reader.GetDouble(10); model.Remark = reader.GetString(11); model.Sort = reader.GetInt32(12); model.IsDisable = reader.GetBoolean(13); model.LastUpdatedDate = reader.GetDateTime(14); list.Add(model); } } } return(list); }
public IList <StockWarningInfo> GetListByJoin(int pageIndex, int pageSize, out int totalRecords, string sqlWhere, params SqlParameter[] cmdParms) { StringBuilder sb = new StringBuilder(250); sb.Append(@"select count(*) from StockWarning sw left join Zone z on z.Id = sw.ZoneId left join StockLocation sl on sl.Id = sw.StockLocationId "); if (!string.IsNullOrEmpty(sqlWhere)) { sb.AppendFormat(" where 1=1 {0} ", sqlWhere); } totalRecords = (int)SqlHelper.ExecuteScalar(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString(), cmdParms); if (totalRecords == 0) { return(new List <StockWarningInfo>()); } sb.Clear(); int startIndex = (pageIndex - 1) * pageSize + 1; int endIndex = pageIndex * pageSize; sb.Append(@"select * from(select row_number() over(order by sw.LastUpdatedDate desc) as RowNumber, sw.Id,sw.UserId,sw.ZoneId,sw.StockLocationId,sw.Coded,sw.ZoneProperty,sw.StockLocationProperty,sw.StockAmount,sw.OverdueDay,sw.MinQty,sw.MaxQty,sw.Remark,sw.Sort,sw.IsDisable,sw.LastUpdatedDate ,z.ZoneName,sl.Code StockLocationCode,sl.Named StockLocationNamed from StockWarning sw left join Zone z on z.Id = sw.ZoneId left join StockLocation sl on sl.Id = sw.StockLocationId "); if (!string.IsNullOrEmpty(sqlWhere)) { sb.AppendFormat(" where 1=1 {0} ", sqlWhere); } sb.AppendFormat(@")as objTable where RowNumber between {0} and {1} ", startIndex, endIndex); var list = new List <StockWarningInfo>(); using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString(), cmdParms)) { if (reader != null && reader.HasRows) { while (reader.Read()) { var model = new StockWarningInfo(); model.Id = reader.GetGuid(1); model.ZoneId = reader.GetGuid(3); model.StockLocationId = reader.GetGuid(4); model.Coded = reader.GetString(5); model.ZoneProperty = reader.GetString(6); model.StockLocationProperty = reader.GetString(7); model.StockAmount = reader.GetDecimal(8); model.OverdueDay = reader.GetInt32(9); model.MinQty = reader.GetDouble(10); model.MaxQty = reader.GetDouble(11); model.Remark = reader.GetString(12); model.Sort = reader.GetInt32(13); model.IsDisable = reader.GetBoolean(14); model.SLastUpdatedDate = reader.GetDateTime(15).ToString("yyyy-MM-dd HH:mm"); model.ZoneName = reader.IsDBNull(16) ? "" : reader.GetString(16); model.StockLocationCode = reader.IsDBNull(17) ? "" : reader.GetString(17); model.IsDisableName = model.IsDisable ? "是" : "否"; list.Add(model); } } } return(list); }
public int Update(StockWarningInfo model) { return(dal.Update(model)); }
public int InsertByOutput(StockWarningInfo model) { return(dal.InsertByOutput(model)); }