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);
}
}