public MssqlCondition PrepareCondition(RebateSearchInfo searchModel)
{
MssqlCondition mssqlCondition = new MssqlCondition();
mssqlCondition.Add("[Distributor_Id]", searchModel.Distributor_Id, ConditionType.Equal);
mssqlCondition.Add("[Time]", searchModel.StartTime, ConditionType.MoreOrEqual);
mssqlCondition.Add("[Time]", searchModel.EndTtime, ConditionType.LessOrEqual);
mssqlCondition.Add("[UserName]", searchModel.UserName, ConditionType.Like);
mssqlCondition.Add("[RealName]", searchModel.RealName, ConditionType.Like);
mssqlCondition.Add("[Mobile]", searchModel.Mobile, ConditionType.Like);
return(mssqlCondition);
}
public List <RebateInfo> SearchList(RebateSearchInfo searchModel)
{
using (SqlConnection conn = new SqlConnection(connectString))
{
//string sql = @"SELECT * FROM (select a.*,b.[UserName],b.[RealName],b.[Mobile] from [Rebate] a
// LEFT JOIN [Usr] b ON a.[Distributor_Id] = b.[Id]) TMP";
string sql = @"select a.*,b.[UserName],b.[RealName],b.[Mobile],c.[OrderNumber],
[Buyer_UserName]=(SELECT [UserName] FROM [Usr] WHERE [Id]=a.[User_Id])
from [Rebate] a
LEFT JOIN [Usr] b ON a.[Distributor_Id] = b.[Id]
LEFT JOIN [Order] c ON A.[Order_id]=c.[Id]";
string condition = PrepareCondition(searchModel).ToString();
if (!string.IsNullOrEmpty(condition))
{
sql += " where " + condition;
}
sql += " Order by [Id] desc";
return(conn.Query <RebateInfo>(sql).ToList());
}
}
public List <RebateInfo> SearchList(int currentPage, int pageSize, RebateSearchInfo 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 [Rebate] a
// LEFT JOIN [Usr] b ON a.[Distributor_Id] = b.[Id]) TMP";
pc.TableName = @"(select a.*,b.[UserName],b.[RealName],b.[Mobile],c.[OrderNumber],
[Buyer_UserName]=(SELECT [UserName] FROM [Usr] WHERE [Id]=a.[User_Id])
from [Rebate] a
LEFT JOIN [Usr] b ON a.[Distributor_Id] = b.[Id]
LEFT JOIN [Order] c ON A.[Order_id]=c.[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 <RebateInfo>(pc).ToList());
}
}
public static List <RebateInfo> SearchList(int currentPage, int pageSize, RebateSearchInfo searchModel, ref int count)
{
return(dal.SearchList(currentPage, pageSize, searchModel, ref count));
}
public static List <RebateInfo> SearchList(RebateSearchInfo searchModel)
{
return(dal.SearchList(searchModel));
}