Beispiel #1
0
        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);
            }));
        }
Beispiel #3
0
        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);
            }
        }
Beispiel #4
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);
            }
        }
Beispiel #5
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);
            }
        }