/// <summary>
/// 获取当前最大排序号
/// </summary>
/// <returns></returns>
public int MaxOrderID()
{
int maxOrderID = 0;
ShopMssqlPagerClass pc = new ShopMssqlPagerClass();
pc.TableName = "(select max([OrderID]) as [MOID] from [VoteItem]) as temp";
pc.Fields = "[MOID]";
pc.OrderField = "[MOID]";
pc.OrderType = OrderType.Desc;
if (pc.ExecuteDataTable().Rows.Count > 0)
{
maxOrderID = Convert.IsDBNull(pc.ExecuteDataTable().Rows[0][0]) ? 0 : Convert.ToInt32(pc.ExecuteDataTable().Rows[0][0]);
}
return(maxOrderID);
}
public DataTable StatisticsSaleDetail(int currentPage, int pageSize, OrderSearchInfo orderSearch, ProductSearchInfo productSearch, ref int count)
{
ShopMssqlPagerClass pc = new ShopMssqlPagerClass();
pc.TableName = "View_SaleDetail";
pc.Fields = "[Id],[Name],[ClassId],[BrandId],[BuyCount],[Money],[OrderNumber],[AddDate],[UserName]";
pc.CurrentPage = currentPage;
pc.PageSize = pageSize;
pc.OrderField = "[AddDate],[Id]";
pc.OrderType = OrderType.Desc;
pc.MssqlCondition = PrepareCondition(orderSearch, productSearch);
count = pc.Count;
return(pc.ExecuteDataTable());
}
public DataTable StatisticsProductSale(int currentPage, int pageSize, ProductSearchInfo productSearch, ref int count, DateTime startDate, DateTime endDate)
{
List <ProductInfo> productList = new List <ProductInfo>();
string orderCondition = string.Empty;
MssqlCondition orderCh = new MssqlCondition();
orderCh.Add("[AddDate]", startDate, ConditionType.MoreOrEqual);
orderCh.Add("[AddDate]", endDate, ConditionType.Less);
orderCondition = orderCh.ToString();
if (orderCondition != string.Empty)
{
orderCondition = " AND " + orderCondition;
}
ShopMssqlPagerClass pc = new ShopMssqlPagerClass();
pc.TableName = "(SELECT Id,Name,ClassId,BrandId,IsSale, ISNULL(SellCount, 0) AS SellCount, ISNULL(SellMoney,0) AS SellMoney FROM [Product] ";
pc.TableName += "LEFT OUTER JOIN (SELECT ProductId, SUM(BuyCount) AS SellCount, SUM(ProductPrice * BuyCount) AS SellMoney FROM ( ";
pc.TableName += "SELECT OrderDetail.ProductId,OrderDetail.ProductPrice, OrderDetail.BuyCount FROM [OrderDetail] ";
pc.TableName += " INNER JOIN [Order] ON [OrderDetail].OrderId = [Order].Id WHERE ([Order].OrderStatus = 6 " + orderCondition + ")) AS TEMP1 GROUP BY ProductId) AS TEMP2 ON [Product].Id = TEMP2.ProductId) As PageTable";
pc.Fields = "[Id],[Name],[ClassId],[SellCount],[SellMoney]";
pc.CurrentPage = currentPage;
pc.PageSize = pageSize;
pc.OrderField = "[Id]";
if (productSearch.ProductOrderType != string.Empty)
{
switch (productSearch.ProductOrderType)
{
case "SellCount":
pc.OrderField = "[SellCount],[Id]";
break;
case "SellMoney":
pc.OrderField = "[SellMoney],[Id]";
break;
default:
break;
}
}
pc.OrderType = OrderType.Desc;
pc.MssqlCondition = PrepareCondition(productSearch);
pc.Count = count;
count = pc.Count;
return(pc.ExecuteDataTable());
}
/// <summary>
/// 用户消费分析
/// </summary>
public DataTable StatisticsUserConsume(int currentPage, int pageSize, UserSearchInfo userSearch, ref int count, string orderField, DateTime startDate, DateTime endDate)
{
List <UserInfo> userList = new List <UserInfo>();
string orderCondition = string.Empty;
MssqlCondition orderCh = new MssqlCondition();
orderCh.Add("[AddDate]", startDate, ConditionType.MoreOrEqual);
orderCh.Add("[AddDate]", endDate, ConditionType.Less);
orderCondition = orderCh.ToString();
if (!string.IsNullOrEmpty(orderCondition))
{
orderCondition = " AND " + orderCondition;
}
ShopMssqlPagerClass pc = new ShopMssqlPagerClass();
pc.TableName = "(SELECT Id,UserName,Sex,ISNULL(OrderCount,0) AS OrderCount,ISNULL(OrderMoney,0) AS OrderMoney ";
pc.TableName += "FROM Usr ";
pc.TableName += "LEFT OUTER JOIN (SELECT UserId, COUNT(*) AS OrderCount,Sum(ProductMoney-FavorableMoney+ShippingMoney+OtherMoney-CouponMoney) AS OrderMoney FROM [Order] WHERE OrderStatus=6 " + orderCondition + " GROUP BY UserId) AS TEMP3 ON Usr.Id = TEMP3.UserId where Usr.status = 2) AS PageTable";
pc.Fields = "[Id],[UserName],[Sex],[OrderCount],[OrderMoney]";
pc.CurrentPage = currentPage;
pc.PageSize = pageSize;
switch (orderField)
{
case "OrderCount":
pc.OrderField = "[OrderCount],[Id]";
break;
case "OrderMoney":
pc.OrderField = "[OrderMoney],[Id]";
break;
default:
pc.OrderField = "[OrderCount],[Id]";
break;
}
pc.OrderType = OrderType.Desc;
pc.MssqlCondition = PrepareCondition(userSearch);
pc.Count = count;
count = pc.Count;
return(pc.ExecuteDataTable());
}
/// <summary>
/// 用户活跃度分析
/// </summary>
public DataTable StatisticsUserActive(int currentPage, int pageSize, UserSearchInfo userSearch, ref int count, string orderField)
{
List <UserInfo> userList = new List <UserInfo>();
ShopMssqlPagerClass pc = new ShopMssqlPagerClass();
pc.TableName = "View_UserActive";
pc.Fields = "[Id],[UserName],[Sex],[RegisterDate],[LoginTimes],[CommentCount]";
pc.CurrentPage = currentPage;
pc.PageSize = pageSize;
switch (orderField)
{
case "LoginTimes":
pc.OrderField = "[LoginTimes],[Id]";
break;
case "CommentCount":
pc.OrderField = "[CommentCount],[Id]";
break;
case "ReplyCount":
pc.OrderField = "[ReplyCount],[Id]";
break;
case "MessageCount":
pc.OrderField = "[MessageCount],[Id]";
break;
default:
pc.OrderField = "[LoginTimes],[Id]";
break;
}
pc.OrderType = OrderType.Desc;
pc.MssqlCondition = PrepareCondition(userSearch);
pc.Count = count;
count = pc.Count;
return(pc.ExecuteDataTable());
}