/// <summary> /// 获得商品回复数据列表 /// </summary> /// <param name="currentPage">当前的页数</param> /// <param name="pageSize">每页记录数</param> /// <param name="count">总数量</param> /// <param name="userID">用户ID</param> /// <returns>商品回复数据列表</returns> public List <ProductReplyInfo> ReadProductReplyList(int currentPage, int pageSize, ref int count, int userID) { using (var conn = new SqlConnection(connectString)) { List <ProductReplyInfo> productReplyList = new List <ProductReplyInfo>(); ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); pc.TableName = ShopMssqlHelper.TablePrefix + "ProductReply"; pc.Fields = "[ID],[ProductID],[CommentID],[Content],[UserIP],[PostDate],[UserID],[UserName]"; pc.CurrentPage = currentPage; pc.PageSize = pageSize; pc.OrderField = "[ID]"; pc.OrderType = OrderType.Desc; pc.MssqlCondition.Add("[UserID]", userID, ConditionType.Equal); pc.Count = count; count = pc.Count; return(conn.Query <ProductReplyInfo>(ShopMssqlHelper.TablePrefix + "ReadPageList", new { tableName = pc.TableName, fields = pc.Fields, pageSize = pc.PageSize, currentPage = pc.CurrentPage, fieldName = pc.OrderField, orderType = pc.OrderType, condition = pc.MssqlCondition.ToString() }, null, true, null, CommandType.StoredProcedure).ToList()); } }
/// <summary> /// 积分明细(区分收入、支出) /// </summary> /// <param name="currentPage"></param> /// <param name="pageSize"></param> /// <param name="accountType"></param> /// <param name="userId"></param> /// <param name="inCome">1-收入,0-支出</param> /// <param name="count"></param> /// <returns></returns> public List <UserAccountRecordInfo> ReadList(int currentPage, int pageSize, AccountRecordType accountType, int userId, int inCome, ref int count) { using (var conn = new SqlConnection(connectString)) { ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); pc.TableName = "UsrAccountRecord"; pc.Fields = "[Id], [RecordType], [Money], [Point], [Date], [IP], [Note], [UserId], [UserName]"; pc.CurrentPage = currentPage; pc.PageSize = pageSize; pc.OrderField = "[Id]"; pc.OrderType = OrderType.Desc; if ((int)accountType > 0) { pc.MssqlCondition.Add("[RecordType]", (int)accountType, ConditionType.Equal); } if (userId > 0) { pc.MssqlCondition.Add("[UserId]", userId, ConditionType.Equal); } if (inCome > 0) {//查询收入 pc.MssqlCondition.Add("[Point]", 0, ConditionType.More); } else {//查询支出 pc.MssqlCondition.Add("[Point]", 0, ConditionType.Less); } count = pc.Count; return(conn.Query <UserAccountRecordInfo>(pc).ToList()); } }
//public List<UserCouponInfo> SearchList(int currentPage, int pageSize, UserCouponSearchInfo searchInfo, ref int count) //{ // using (var conn = new SqlConnection(connectString)) // { // ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); // pc.TableName = "UsrCoupon"; // pc.Fields = "[Id],[CouponId],[GetType],[Number],[Password],[IsUse],[OrderId],[UserId],[UserName]"; // pc.CurrentPage = currentPage; // pc.PageSize = pageSize; // pc.OrderField = "[Id]"; // pc.MssqlCondition = PrepareCondition(searchInfo); // count = pc.Count; // return conn.Query<UserCouponInfo>(pc).ToList(); // } //} public List <UserCouponInfo> SearchList(int currentPage, int pageSize, UserCouponSearchInfo searchInfo, ref int count) { using (var conn = new SqlConnection(connectString)) { ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); if (searchInfo.IsTimeOut == (int)BoolType.True)//已过期 { pc.TableName = @" (SELECT UsrCoupon.[Id],[CouponId],[GetType],[Number],[Password],[IsUse],[OrderId],[UserId],[UserName],[Money],[UseMinAmount] FROM UsrCoupon INNER JOIN Coupon ON UsrCoupon.[CouponId]=Coupon.[Id] AND [UseEndDate]<getdate()) temp" ; } if (searchInfo.IsTimeOut == (int)BoolType.False)//未过期 { pc.TableName = @" (SELECT UsrCoupon.[Id],[CouponId],[GetType],[Number],[Password],[IsUse],[OrderId],[UserId],[UserName],[Money],[UseMinAmount] FROM UsrCoupon INNER JOIN Coupon ON UsrCoupon.[CouponId]=Coupon.[Id] AND [UseStartDate]<=getdate() AND [UseEndDate]>=getdate()) temp" ; } if (searchInfo.IsTimeOut == -1)//所有 { pc.TableName = "UsrCoupon"; } pc.Fields = "[Id],[CouponId],[GetType],[Number],[Password],[IsUse],[OrderId],[UserId],[UserName]"; pc.CurrentPage = currentPage; pc.PageSize = pageSize; pc.OrderField = "[Id]"; pc.MssqlCondition = PrepareCondition(searchInfo); count = pc.Count; return(conn.Query <UserCouponInfo>(pc).ToList()); } }
public List <GroupBuyInfo> SearchList(int currentPage, int pageSize, GroupBuySearchInfo searchInfo, ref int count) { using (var conn = new SqlConnection(connectString)) { ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); pc.TableName = @"(select a.*,b.username as GroupUserName,b.photo as GroupUserAvatar,d.ProductName,d.ProductPrice,e.Photo as ProductPhoto,c.Id as GroupOrderId,c.OrderStatus as GroupOrderStatus,c.IsRefund from [GroupBuy] a inner join [usr] b on a.[Leader] = b.[Id] inner join [Order] c on a.[Id]=c.[FavorableActivityId] and a.[Leader]=c.[UserId] and c.[IsActivity]=2 inner join [orderdetail] d on d.[OrderId]=c.[Id] inner join [Product] e on e.[Id]=a.[ProductId]) tmp"; pc.Fields = "*"; pc.CurrentPage = currentPage; pc.PageSize = pageSize; pc.OrderField = "[Id]"; pc.OrderType = OrderType.Desc; pc.MssqlCondition = PrepareCondition(searchInfo); count = pc.Count; return(conn.Query <GroupBuyInfo>(pc).ToList()); } }
public List <GroupSignInfo> SearchListByUserId(int userId, int pageIndex, int pageSize, ref int count) { using (var conn = new SqlConnection(connectString)) { ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); pc.TableName = @"( select a.*,b.Leader,b.StartTime,b.EndTime,b.Quantity,b.SignCount,c.OrderNumber as GroupOrderNumber,c.OrderStatus as GroupOrderStatus,c.IsRefund,b.ProductId,d.ProductName,d.ProductPrice,e.Photo as ProductPhoto from GroupSign a inner join [GroupBuy] b on a.[GroupId]=b.[Id] inner join [Order] c on a.[OrderId]=c.[Id] and c.[IsActivity]=2 inner join [orderdetail] d on d.[OrderId]=a.[OrderId] inner join [Product] e on e.[Id]=b.[ProductId]) tmp"; pc.Fields = "*"; pc.CurrentPage = pageIndex; pc.PageSize = pageSize; pc.OrderField = "[Id]"; pc.OrderType = OrderType.Desc; pc.MssqlCondition.Add(" [UserId]=" + userId); count = pc.Count; return(conn.Query <GroupSignInfo>(pc).ToList()); } }
/// <summary> /// 获得投票选项数据列表 /// </summary> /// <param name="currentPage">当前的页数</param> /// <param name="pageSize">每页记录数</param> /// <param name="count">总数量</param> /// <returns>投票选项数据列表</returns> public List <VoteItemInfo> ReadVoteItemListByVote(int currentPage, int pageSize, ref int count, string voteID, int voteItemID, int ascDesc) { List <VoteItemInfo> voteItemList = new List <VoteItemInfo>(); ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); pc.TableName = "VoteItem"; pc.Fields = "[ID],[VoteID],[ItemName],[VoteCount],[OrderID],[Image],[Department],[Solution],[Point],[CoverDepartment],[Detail],[Exp1],[Exp2],[Exp3],[Exp4],[Exp5]"; pc.CurrentPage = currentPage; pc.PageSize = pageSize; pc.MssqlCondition.Add("[VoteID]", voteID, ConditionType.Like); pc.OrderField = "[OrderID],[ID]"; if (ascDesc == 1) { pc.OrderType = OrderType.Desc; pc.MssqlCondition.Add("[OrderID]", voteItemID, ConditionType.Less); } else { pc.OrderType = OrderType.Asc; pc.MssqlCondition.Add("[OrderID]", voteItemID, ConditionType.More); } pc.Count = count; count = pc.Count; using (SqlDataReader dr = pc.ExecuteReader()) { PrepareVoteItemModel(dr, voteItemList); } return(voteItemList); }
/// <summary> /// 获得投票选项数据列表 /// </summary> /// <param name="currentPage">当前的页数</param> /// <param name="pageSize">每页记录数</param> /// <param name="count">总数量</param> /// <returns>投票选项数据列表</returns> public List <VoteItemInfo> ReadVoteItemList(int currentPage, int pageSize, ref int count, string orderType, int ascDesc) { List <VoteItemInfo> voteItemList = new List <VoteItemInfo>(); ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); pc.TableName = "VoteItem"; pc.Fields = "[ID],[VoteID],[ItemName],[VoteCount],[OrderID],[Image],[Department],[Solution],[Point],[CoverDepartment],[Detail],[Exp1],[Exp2],[Exp3],[Exp4],[Exp5]"; pc.CurrentPage = currentPage; pc.PageSize = pageSize; if (string.IsNullOrEmpty(orderType)) { pc.OrderField = "[ID]"; } else { pc.OrderField = "[" + orderType + "]"; } if (ascDesc == 1) { pc.OrderType = OrderType.Asc; } else { pc.OrderType = OrderType.Desc; } //pc.Count = count; count = pc.Count; using (SqlDataReader dr = pc.ExecuteReader()) { PrepareVoteItemModel(dr, voteItemList); } return(voteItemList); }
/// <summary> /// 根据有效期状态查询 /// </summary> /// <param name="currentPage"></param> /// <param name="pageSize"></param> /// <param name="timePeriod">状态:未开始 1,进行中 2,已结束 3</param> /// <param name="count"></param> /// <returns></returns> public List <FavorableActivityInfo> ReadList(int currentPage, int pageSize, ref int count, int timePeriod = 2) { using (var conn = new SqlConnection(connectString)) { ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); pc.TableName = "FavorableActivity"; pc.Fields = "[Id], [Name], [Photo], [Content], [StartDate], [EndDate], [UserGrade], [OrderProductMoney], [RegionId], [ShippingWay], [ReduceWay], [ReduceMoney], [ReduceDiscount], [GiftId],[Type] "; pc.CurrentPage = currentPage; pc.PageSize = pageSize; pc.OrderField = "[Id]"; //未开始 if (timePeriod == 1) { pc.MssqlCondition.Add(" DATEDIFF ( day , getdate(), [StartDate] )>0"); } //进行中 if (timePeriod == 2) { pc.MssqlCondition.Add(" DATEDIFF(day,getdate(),[StartDate])<=0 and DATEDIFF(day,getdate(),[EndDate])>=0"); } //已结束 if (timePeriod == 3) { pc.MssqlCondition.Add(" DATEDIFF(day,getdate(),[EndDate])<0"); } count = pc.Count; return(conn.Query <FavorableActivityInfo>(pc).ToList()); } }
public static IEnumerable <T> Query <T>(this IDbConnection cnn, ShopMssqlPagerClass pc) { return(cnn.Query <T>(ShopMssqlHelper.TablePrefix + "ReadPageList", new { tableName = pc.TableName, fields = pc.Fields, pageSize = pc.PageSize, currentPage = pc.CurrentPage, fieldName = pc.OrderField, orderType = pc.OrderType, condition = pc.MssqlCondition.ToString() }, commandType: CommandType.StoredProcedure)); }
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 List <FavorableActivityGiftInfo> SearchList(int currentPage, int pageSize, FavorableActivityGiftSearchInfo searchInfo, ref int count) { using (var conn = new SqlConnection(connectString)) { ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); pc.TableName = "FavorableActivityGift"; pc.Fields = "[Id], [Name], [Photo], [Description] "; pc.CurrentPage = currentPage; pc.PageSize = pageSize; pc.OrderField = "[Id]"; pc.MssqlCondition = PrepareCondition(searchInfo); count = pc.Count; return(conn.Query <FavorableActivityGiftInfo>(pc).ToList()); } }
public List <AdminInfo> ReadList(int currentPage, int pageSize, ref int count) { using (var conn = new SqlConnection(connectString)) { ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); pc.TableName = "[Admin]"; pc.Fields = "*"; pc.CurrentPage = currentPage; pc.PageSize = pageSize; pc.OrderField = "[Id]"; pc.OrderType = OrderType.Desc; count = pc.Count; return(conn.Query <AdminInfo>(pc).ToList()); } }
public List <CouponInfo> SearchList(int currentPage, int pageSize, CouponSearchInfo searchInfo, ref int count) { using (var conn = new SqlConnection(connectString)) { ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); pc.TableName = "Coupon"; pc.Fields = " * "; pc.CurrentPage = currentPage; pc.PageSize = pageSize; pc.OrderField = "[Id]"; pc.MssqlCondition = PrepareCondition(searchInfo); count = pc.Count; return(conn.Query <CouponInfo>(pc).ToList()); } }
public List <OtherShiyaInfo> SearchList(int currentPage, int pageSize, OtherShiyaSearchInfo searchInfo, ref int count) { using (var conn = new SqlConnection(connectString)) { ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); pc.TableName = "OtherShiya"; pc.Fields = "saleid,shuigongid,shiyaid,customerid,orderId,truename,mobile,address,anname,anmobile,housetype,kaifashang,jiazhuang,kongtiao,guanjian,pushe,baohu,beizhu,zongfa,hunzhuang,huaheng,xiangqian,guolvqi,jietou,baoyastart,baoyaend,yunxing,jiance,shiyaname,shiyamobile,gaozhi,stat,addtime "; pc.CurrentPage = currentPage; pc.PageSize = pageSize; pc.OrderField = "[id]"; pc.MssqlCondition = PrepareCondition(searchInfo); count = pc.Count; return(conn.Query <OtherShiyaInfo>(pc).ToList()); } }
public List <FavorableActivityInfo> ReadList(int currentPage, int pageSize, ref int count) { using (var conn = new SqlConnection(connectString)) { ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); pc.TableName = "FavorableActivity"; pc.Fields = "[Id], [Name], [Photo], [Content], [StartDate], [EndDate], [UserGrade], [OrderProductMoney], [RegionId], [ShippingWay], [ReduceWay], [ReduceMoney], [ReduceDiscount], [GiftId],[Type] "; pc.CurrentPage = currentPage; pc.PageSize = pageSize; pc.OrderField = "[Id]"; count = pc.Count; return(conn.Query <FavorableActivityInfo>(pc).ToList()); } }
public List <FlashInfo> SearchList(int currentPage, int pageSize, ref int count) { using (var conn = new SqlConnection(connectString)) { ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); pc.TableName = "Flash"; pc.Fields = "*"; pc.CurrentPage = currentPage; pc.PageSize = pageSize; pc.OrderField = "[Id]"; pc.OrderType = OrderType.Asc; pc.MssqlCondition.Add(" [Id]>10 "); count = pc.Count; return(conn.Query <FlashInfo>(pc).ToList()); } }
/// <summary> /// 获得投票类型列表--分页 /// </summary> /// <param name="currentPage">当前的页数</param> /// <param name="pageSize">每页记录数</param> /// <param name="count">总数量</param> /// <returns>投票数据列表</returns> public List <VoteInfo> ReadVoteList(int currentPage, int pageSize, ref int count) { using (var conn = new SqlConnection(connectString)) { List <VoteInfo> voteList = new List <VoteInfo>(); ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); pc.TableName = "Vote"; pc.Fields = "[ID],[FatherID],[Title],[ItemCount],[VoteType],[Note],[OrderID]"; pc.CurrentPage = currentPage; pc.PageSize = pageSize; pc.OrderField = "[OrderID],[ID]"; pc.OrderType = OrderType.Asc; count = pc.Count; return(conn.Query <VoteInfo>(pc).ToList()); } }
/// <summary> /// 搜索砍价记录表数据列表 /// </summary> /// <param name="currentPage">当前的页数</param> /// <param name="pageSize">每页记录数</param> /// <param name="search">RecordingSearch模型变量</param> /// <param name="count">总数量</param> /// <returns>砍价记录表数据列表</returns> public List <RecordingInfo> SearchRecordingList(int currentPage, int pageSize, RecordingSearch search, ref int count) { using (var conn = new SqlConnection(connectString)) { ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); pc.TableName = "Recording"; pc.Fields = "*"; pc.CurrentPage = currentPage; pc.PageSize = pageSize; pc.OrderType = OrderType.Desc; pc.MssqlCondition = PrepareCondition(search); count = pc.Count; return(conn.Query <RecordingInfo>(pc).ToList()); } }
public List <OtherDuihuanInfo> SearchList(int currentPage, int pageSize, OtherDuihuanSearchInfo searchInfo, ref int count) { using (var conn = new SqlConnection(connectString)) { ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); pc.TableName = "OtherDuihuan"; pc.Fields = "[id], [userid], [truename], [mobile], [note], [integral], [adminid], [addtime] "; pc.CurrentPage = currentPage; pc.PageSize = pageSize; pc.OrderField = "[id]"; pc.MssqlCondition = PrepareCondition(searchInfo); count = pc.Count; return(conn.Query <OtherDuihuanInfo>(pc).ToList()); } }
public List <UserMessageInfo> SearchList(int currentPage, int pageSize, UserMessageSeachInfo searchInfo, ref int count) { using (var conn = new SqlConnection(connectString)) { ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); pc.TableName = "UsrMessage"; pc.Fields = "[Id], [MessageClass], [Title], [Content], [UserIP], [PostDate], [IsHandler], [AdminReplyContent], [AdminReplyDate], [UserId], [UserName],[Tel],[Email],[Gender],[Birthday],[Birthplace],[LivePlace],[Address],[Servedays],[Servemode],[AddCol1],[AddCol2] "; pc.CurrentPage = currentPage; pc.PageSize = pageSize; pc.OrderField = "[Id]"; pc.MssqlCondition = PrepareCondition(searchInfo); count = pc.Count; return(conn.Query <UserMessageInfo>(pc).ToList()); } }
/// <summary> /// 获得产品收藏数据列表 /// </summary> /// <param name="currentPage">当前的页数</param> /// <param name="pageSize">每页记录数</param> /// <param name="count">总数量</param> /// <returns>产品收藏数据列表</returns> public List <ProductCollectInfo> ReadList(int currentPage, int pageSize, ref int count, int userID) { using (var conn = new SqlConnection(connectString)) { ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); pc.TableName = "ProductCollect"; pc.Fields = "[ID],[ProductID],[Tm],[UserID],[UserName]"; pc.CurrentPage = currentPage; pc.PageSize = pageSize; pc.OrderField = "[ID]"; pc.OrderType = OrderType.Desc; pc.MssqlCondition.Add("[UserID]", userID, ConditionType.Equal); count = pc.Count; return(conn.Query <ProductCollectInfo>(pc).ToList()); } }
public List <ReceiveMessageInfo> SearchList(int currentPage, int pageSize, ReceiveMessageSearchInfo searchEntity, ref int count) { using (var conn = new SqlConnection(connectString)) { ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); pc.TableName = "ReceiveMessage"; pc.Fields = "*"; pc.CurrentPage = currentPage; pc.PageSize = pageSize; pc.OrderField = "[Id]"; pc.OrderType = OrderType.Desc; pc.MssqlCondition = PrepareCondition(searchEntity); count = pc.Count; return(conn.Query <ReceiveMessageInfo>(pc).ToList()); } }
public List <OrderInfo> SearchList(int currentPage, int pageSize, OrderSearchInfo searchInfo, ref int count) { using (var conn = new SqlConnection(connectString)) { ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); pc.TableName = "[Order]"; pc.Fields = "[Id], [OrderNumber], [IsActivity], [OrderStatus], [OrderNote], [ProductMoney], [Balance], [FavorableMoney], [OtherMoney], [CouponMoney], [Point], [PointMoney], [Consignee], [RegionId], [Address], [ZipCode], [Tel], [Email], [Mobile], [ShippingId], [ShippingDate], [ShippingNumber], [ShippingMoney], [PayKey], [PayName], [PayDate], [IsRefund], [FavorableActivityId], [GiftId], [InvoiceTitle], [InvoiceContent], [UserMessage], [AddDate], [IP], [UserId], [UserName], [IsDelete], [ActivityPoint], [AliPayTradeNo], [WxPayTradeNo],[SelfPick],[Saleid],[Shuigongid],[Shiyaid],[Shuigong_name],[Shuigong_tel],[Shiya_name],[Shiya_tel],[Shuigong_stat],[Shiya_stat]"; pc.CurrentPage = currentPage; pc.PageSize = pageSize; pc.OrderField = "[Id]"; pc.OrderType = OrderType.Desc; pc.MssqlCondition = PrepareCondition(searchInfo); count = pc.Count; return(conn.Query <OrderInfo>(pc).ToList()); } }
public List <UserRechargeInfo> SearchList(int currentPage, int pageSize, UserRechargeSearchInfo searchInfo, ref int count) { using (var conn = new SqlConnection(connectString)) { ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); pc.TableName = "UsrRecharge"; pc.Fields = "[Id], [Number], [Money], [PayKey], [PayName], [RechargeDate], [RechargeIP], [IsFinish], [UserId] "; pc.CurrentPage = currentPage; pc.PageSize = pageSize; pc.OrderField = "[Id]"; pc.OrderType = OrderType.Desc; pc.MssqlCondition = PrepareCondition(searchInfo); count = pc.Count; return(conn.Query <UserRechargeInfo>(pc).ToList()); } }
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> /// <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 List <WithdrawInfo> SearchList(int currentPage, int pageSize, WithdrawSearchInfo searchModel, ref int count) { using (var conn = new SqlConnection(connectString)) { ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); pc.TableName = @"(select a.*,b.[UserName],b.[RealName],b.[Mobile] from [Withdraw] a LEFT JOIN[Usr] b ON a.[Distributor_Id] = b.[Id]) TMP"; pc.Fields = "*"; pc.CurrentPage = currentPage; pc.PageSize = pageSize; pc.OrderField = "[Id]"; pc.OrderType = OrderType.Desc; pc.MssqlCondition = PrepareCondition(searchModel); count = pc.Count; return(conn.Query <WithdrawInfo>(pc).ToList()); } }
/// <summary> /// 搜索缺货登记数据列表 /// </summary> /// <param name="currentPage">当前的页数</param> /// <param name="pageSize">每页记录数</param> /// <param name="bookingProductSearch">BookingProductSearchInfo模型变量</param> /// <param name="count">总数量</param> /// <returns>缺货登记数据列表</returns> public List <BookingProductInfo> SearchBookingProductList(int currentPage, int pageSize, BookingProductSearchInfo bookingProductSearch, ref int count) { List <BookingProductInfo> bookingProductList = new List <BookingProductInfo>(); ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); pc.TableName = "ProductBooking"; pc.Fields = "[ID],[ProductID],[ProductName],[RelationUser],[Email],[Tel],[UserNote],[BookingDate],[BookingIP],[IsHandler],[HandlerDate],[HandlerAdminID],[HandlerAdminName],[HandlerNote],[UserID],[UserName]"; pc.CurrentPage = currentPage; pc.PageSize = pageSize; pc.OrderField = "[ID]"; pc.OrderType = OrderType.Desc; pc.MssqlCondition = PrepareCondition(bookingProductSearch); count = pc.Count; using (SqlDataReader dr = pc.ExecuteReader()) { PrepareBookingProductModel(dr, bookingProductList); } return(bookingProductList); }
/// <summary> /// 获取userlist 包括usergrade /// </summary> /// <param name="searchInfo"></param> /// <returns></returns> public List <UserInfo> SearchListAndUserGrade(int currentPage, int pageSize, UserSearchInfo searchModel, ref int count) { using (var conn = new SqlConnection(connectString)) { ShopMssqlPagerClass pc = new ShopMssqlPagerClass() { TableName = "[View_UserGrade]", Fields = "*", CurrentPage = currentPage, PageSize = pageSize, MssqlCondition = PrepareCondition(searchModel), OrderField = "[Id]", OrderType = OrderType.Desc }; count = pc.Count; var data = conn.Query <UserInfo>(pc).ToList(); return(data); } }
public List <AdminLogInfo> ReadList(int currentPage, int pageSize, ref int count, string logType, DateTime startAddDate, DateTime endAddDate, int adminId) { using (var conn = new SqlConnection(connectString)) { ShopMssqlPagerClass pc = new ShopMssqlPagerClass(); pc.TableName = "AdminLog"; pc.Fields = "*"; pc.CurrentPage = currentPage; pc.PageSize = pageSize; pc.OrderField = "[Id]"; pc.OrderType = OrderType.Desc; pc.MssqlCondition.Add("[Action]", logType, ConditionType.Like); pc.MssqlCondition.Add("[AddDate]", startAddDate, ConditionType.MoreOrEqual); pc.MssqlCondition.Add("[AddDate]", endAddDate, ConditionType.Less); pc.MssqlCondition.Add("[AdminId]", adminId, ConditionType.Equal); count = pc.Count; return(conn.Query <AdminLogInfo>(pc).ToList()); } }