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