public static bool UpdateProductStatistics(ProductStatisticsModel model) { #region sql const string sql = @" UPDATE [Tuhu_productcatalog].[dbo].[tbl_ProductStatistics] WITH(ROWLOCK) SET OrderQuantity=@OrderQuantity, SalesQuantity=@SalesQuantity, CommentTimes=@CommentTimes, CommentR1=@CommentR1, CommentR2=@CommentR2, CommentR3=@CommentR3, CommentR4=@CommentR4, CommentR5=@CommentR5, CommentRate=@CommentRate WHERE ProductID=@ProductID AND VariantID=@VariantID"; #endregion using (var cmd = new SqlCommand(sql)) { cmd.Parameters.AddWithValue("@OrderQuantity", model.OrderQuantity); cmd.Parameters.AddWithValue("@SalesQuantity", model.SalesQuantity); cmd.Parameters.AddWithValue("@CommentTimes", model.CommentTimes); cmd.Parameters.AddWithValue("@CommentR1", model.CommentR1); cmd.Parameters.AddWithValue("@CommentR2", model.CommentR2); cmd.Parameters.AddWithValue("@CommentR3", model.CommentR3); cmd.Parameters.AddWithValue("@CommentR4", model.CommentR4); cmd.Parameters.AddWithValue("@CommentR5", model.CommentR5); cmd.Parameters.AddWithValue("@CommentRate", model.CommentRate); cmd.Parameters.AddWithValue("@ProductID", model.ProductID); cmd.Parameters.AddWithValue("@VariantID", model.VariantID); return(DbHelper.ExecuteNonQuery(cmd) > 0); } }
public List <ProductStatisticsModel> GetTotalSoldProductsForMinMaxDays(DateTime minDate, DateTime maxDate) { var sqlQuery = "SELECT p.ProductId AS pid, " + "p.Name, \n" + "o.OrderDate AS date, \n" + "IFNULL(op.Amount, 0) as amount \n" + "FROM `order` as o \n" + "LEFT JOIN `orderproducts` as op ON o.OrderId = op.OrderId \n" + "LEFT JOIN `product` as p ON p.ProductId = op.ProductId \n" + "WHERE OrderDate >= \'" + minDate.ToString("yyyy-MM-dd") + "\' \n" + "AND OrderDate < \'" + maxDate.ToString("yyyy-MM-dd") + "\' \n" + "AND (OrderState = \'0\' OR OrderState = \'1\') \n" + "GROUP BY pid, DAY(date)"; return(ExecuteCustom <ProductStatisticsModel>(sqlQuery, (reader, list) => { var date = reader.GetDateTime(2); var amount = reader.GetInt32(3); var item = new ProductStatisticsModel(date.ToString("yyyy-MM-dd"), amount); item.ProductId = reader.GetInt32(0); item.ProductName = reader.GetString(1); item.Date = date; item.Amount = amount; list.Add(item); })); }
public static bool UpdateProductStatistics(ProductStatisticsModel model) { string DBName = GetSwitchValue() ? DBName_ProductStatistics + "_temp" : DBName_ProductStatistics; #region sql string sql = @"UPDATE [Tuhu_productcatalog].[dbo].[" + DBName + @"] WITH(ROWLOCK) SET OrderQuantity=@OrderQuantity, SalesQuantity=@SalesQuantity, CommentTimes=@CommentTimes, CommentR1=@CommentR1, CommentR2=@CommentR2, CommentR3=@CommentR3, CommentR4=@CommentR4, CommentR5=@CommentR5, CommentRate=@CommentRate, FavourableCount=@FavourableCount, DefaultFavourableCount=@DefaultFavourableCount, Score=@Score, CommentTimesB=@CommentTimesB, CommentR1B=@CommentR1B, CommentR2B=@CommentR2B, CommentR3B=@CommentR3B, CommentR4B=@CommentR4B, CommentR5B=@CommentR5B WHERE ProductID=@ProductID AND VariantID=@VariantID"; #endregion using (var cmd = new SqlCommand(sql)) { cmd.Parameters.AddWithValue("@OrderQuantity", model.OrderQuantity); cmd.Parameters.AddWithValue("@SalesQuantity", model.SalesQuantity); cmd.Parameters.AddWithValue("@CommentTimes", model.CommentTimes); cmd.Parameters.AddWithValue("@CommentR1", model.CommentR1); cmd.Parameters.AddWithValue("@CommentR2", model.CommentR2); cmd.Parameters.AddWithValue("@CommentR3", model.CommentR3); cmd.Parameters.AddWithValue("@CommentR4", model.CommentR4); cmd.Parameters.AddWithValue("@CommentR5", model.CommentR5); cmd.Parameters.AddWithValue("@CommentRate", model.CommentRate); cmd.Parameters.AddWithValue("@FavourableCount", model.FavourableCount); cmd.Parameters.AddWithValue("@DefaultFavourableCount", model.DefaultFavourableCount); cmd.Parameters.AddWithValue("@Score", model.Score); cmd.Parameters.AddWithValue("@CommentTimesB", model.CommentTimesB); cmd.Parameters.AddWithValue("@CommentR1B", model.CommentR1B); cmd.Parameters.AddWithValue("@CommentR2B", model.CommentR2B); cmd.Parameters.AddWithValue("@CommentR3B", model.CommentR3B); cmd.Parameters.AddWithValue("@CommentR4B", model.CommentR4B); cmd.Parameters.AddWithValue("@CommentR5B", model.CommentR5B); cmd.Parameters.AddWithValue("@ProductID", model.ProductID); cmd.Parameters.AddWithValue("@VariantID", model.VariantID); return(DbHelper.ExecuteNonQuery(cmd) > 0); } }
public static bool InsertProductStatistics(ProductStatisticsModel model) { string DBName = GetSwitchValue() ? DBName_ProductStatistics + "_temp" : DBName_ProductStatistics; #region sql string sql = @"Insert Into [Tuhu_productcatalog].[dbo].[" + DBName + @"] (ProductID,VariantID,OrderQuantity,SalesQuantity,CommentTimes,CommentR1,CommentR2,CommentR3,CommentR4,CommentR5,CommentRate, FavourableCount,DefaultFavourableCount,Score,CommentTimesB,CommentR1B,CommentR2B,CommentR3B,CommentR4B,CommentR5B) Values(@ProductID, @VariantID, @OrderQuantity, @SalesQuantity, @CommentTimes, @CommentR1, @CommentR2, @CommentR3, @CommentR4, @CommentR5, @CommentRate, @FavourableCount,@DefaultFavourableCount,@Score,@CommentTimesB,@CommentR1B,@CommentR2B,@CommentR3B,@CommentR4B,@CommentR5B ); "; #endregion using (var cmd = new SqlCommand(sql)) { cmd.Parameters.AddWithValue("@OrderQuantity", model.OrderQuantity); cmd.Parameters.AddWithValue("@SalesQuantity", model.SalesQuantity); cmd.Parameters.AddWithValue("@CommentTimes", model.CommentTimes); cmd.Parameters.AddWithValue("@CommentR1", model.CommentR1); cmd.Parameters.AddWithValue("@CommentR2", model.CommentR2); cmd.Parameters.AddWithValue("@CommentR3", model.CommentR3); cmd.Parameters.AddWithValue("@CommentR4", model.CommentR4); cmd.Parameters.AddWithValue("@CommentR5", model.CommentR5); cmd.Parameters.AddWithValue("@CommentRate", model.CommentRate); cmd.Parameters.AddWithValue("@ProductID", model.ProductID); cmd.Parameters.AddWithValue("@VariantID", model.VariantID); cmd.Parameters.AddWithValue("@FavourableCount", model.FavourableCount); cmd.Parameters.AddWithValue("@DefaultFavourableCount", model.DefaultFavourableCount); cmd.Parameters.AddWithValue("@Score", model.Score); cmd.Parameters.AddWithValue("@CommentTimesB", model.CommentTimesB); cmd.Parameters.AddWithValue("@CommentR1B", model.CommentR1B); cmd.Parameters.AddWithValue("@CommentR2B", model.CommentR2B); cmd.Parameters.AddWithValue("@CommentR3B", model.CommentR3B); cmd.Parameters.AddWithValue("@CommentR4B", model.CommentR4B); cmd.Parameters.AddWithValue("@CommentR5B", model.CommentR5B); return(DbHelper.ExecuteNonQuery(cmd) > 0); } }
public static bool InsertProductStatistics(ProductStatisticsModel model) { #region sql const string sql = @"Insert Into [Tuhu_productcatalog].[dbo].[tbl_ProductStatistics] ([ProductID] ,[VariantID] ,[OrderQuantity] ,[SalesQuantity] ,[CommentTimes] ,[CommentR1] ,[CommentR2] ,[CommentR3] ,[CommentR4] ,[CommentR5] ,[CommentRate]) Values(@ProductID, @VariantID, @OrderQuantity, @SalesQuantity, @CommentTimes, @CommentR1, @CommentR2, @CommentR3, @CommentR4, @CommentR5, @CommentRate); "; #endregion using (var cmd = new SqlCommand(sql)) { cmd.Parameters.AddWithValue("@OrderQuantity", model.OrderQuantity); cmd.Parameters.AddWithValue("@SalesQuantity", model.SalesQuantity); cmd.Parameters.AddWithValue("@CommentTimes", model.CommentTimes); cmd.Parameters.AddWithValue("@CommentR1", model.CommentR1); cmd.Parameters.AddWithValue("@CommentR2", model.CommentR2); cmd.Parameters.AddWithValue("@CommentR3", model.CommentR3); cmd.Parameters.AddWithValue("@CommentR4", model.CommentR4); cmd.Parameters.AddWithValue("@CommentR5", model.CommentR5); cmd.Parameters.AddWithValue("@CommentRate", model.CommentRate); cmd.Parameters.AddWithValue("@ProductID", model.ProductID); cmd.Parameters.AddWithValue("@VariantID", model.VariantID); return(DbHelper.ExecuteNonQuery(cmd) > 0); } }