public override UserPayCollection GetUserPays(int userID, PaylogFilter filter, int pageSize, int pageNumber)
{
using (SqlQuery query = new SqlQuery())
{
StringBuilder condition = new StringBuilder(" AND UserID = @UserID");
query.CreateParameter <int>("@UserID", userID, SqlDbType.Int);
if (filter != null)
{
if (filter.State < 2)
{
condition.Append(" AND State=@State");
query.CreateParameter <bool>("@State", (filter.State == 1), SqlDbType.Bit);
}
if (filter.BeginDate != null)
{
condition.Append(" AND PayDate>= @BeginDate");
query.CreateParameter <DateTime>("@BeginDate", filter.BeginDate.Value, SqlDbType.DateTime);
}
if (filter.EndDate != null)
{
condition.Append(" AND PayDate<= @EndDate");
query.CreateParameter <DateTime>("@EndDate", filter.EndDate.Value, SqlDbType.DateTime);
}
}
if (condition.Length > 5)
{
condition.Remove(0, 5);
}
query.Pager.TableName = "bx_Pay";
query.Pager.PageSize = pageSize;
query.Pager.PageNumber = pageNumber;
query.Pager.PrimaryKey = "PayID";
query.Pager.SelectCount = true;
query.Pager.SortField = "PayID";
query.Pager.Condition = condition.ToString();
query.Pager.IsDesc = true;
using (XSqlDataReader reader = query.ExecuteReader())
{
UserPayCollection result = new UserPayCollection(reader);
if (reader.NextResult())
{
while (reader.Next)
{
result.TotalRecords = reader.Get <int>(0);
}
}
return(result);
}
}
}
public override UserPayCollection GetUserPays(int userID, PaylogFilter filter, int pageSize, int pageNumber)
{
using (SqlQuery query = new SqlQuery())
{
StringBuilder condition = new StringBuilder(" AND UserID = @UserID");
query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
if (filter != null)
{
if (filter.State < 2)
{
condition.Append(" AND State=@State");
query.CreateParameter<bool>("@State", (filter.State == 1), SqlDbType.Bit);
}
if (filter.BeginDate != null)
{
condition.Append(" AND PayDate>= @BeginDate");
query.CreateParameter<DateTime>("@BeginDate", filter.BeginDate.Value, SqlDbType.DateTime);
}
if (filter.EndDate != null)
{
condition.Append(" AND PayDate<= @EndDate");
query.CreateParameter<DateTime>("@EndDate", filter.EndDate.Value, SqlDbType.DateTime);
}
}
if (condition.Length > 5)
condition.Remove(0, 5);
query.Pager.TableName = "bx_Pay";
query.Pager.PageSize = pageSize;
query.Pager.PageNumber = pageNumber;
query.Pager.PrimaryKey = "PayID";
query.Pager.SelectCount = true;
query.Pager.SortField = "PayID";
query.Pager.Condition = condition.ToString();
query.Pager.IsDesc = true;
using (XSqlDataReader reader = query.ExecuteReader())
{
UserPayCollection result = new UserPayCollection(reader);
if (reader.NextResult())
{
while (reader.Next)
result.TotalRecords = reader.Get<int>(0);
}
return result;
}
}
}
public override UserPayCollection AdminSearchUserPays(PaylogFilter filter, int pageSize, int pageNumber)
{
using (SqlQuery query = new SqlQuery())
{
string SordField = "PayID";
StringBuilder condition = new StringBuilder();
if (filter != null)
{
if (!string.IsNullOrEmpty(filter.OrderNo))
{
condition.Append(" AND OrderNo LIKE '%' + @OrderNo +'%'");
query.CreateParameter <string>("@OrderNo", filter.OrderNo, SqlDbType.NVarChar, 200);
}
if (!string.IsNullOrEmpty(filter.TransactionNo))
{
condition.Append(" AND TransactionNo LIKE '%' + @TransactionNo +'%'");
query.CreateParameter <string>("@TransactionNo", filter.TransactionNo, SqlDbType.NVarChar, 200);
}
if (filter.Payment > 0)
{
condition.Append(" AND Payment = @Payment");
query.CreateParameter <byte>("@Payment", filter.Payment, SqlDbType.TinyInt);
}
if (filter.BeginAmount > 0 && filter.EndAmount > 0)
{
condition.Append(" AND OrderAmount >= @BeginAmount AND OrderAmount <= @EndAmount");
query.CreateParameter <decimal>("@BeginAmount", filter.BeginAmount, SqlDbType.Decimal);
query.CreateParameter <decimal>("@EndAmount", filter.EndAmount, SqlDbType.Decimal);
}
if (filter.PayType > 0)
{
condition.Append(" AND PayType LIKE '%' + @PayType + '%'");
query.CreateParameter <byte>("@PayType", filter.PayType, SqlDbType.TinyInt);
}
if (filter.BeginValue > 0 && filter.EndValue > 0)
{
condition.Append(" AND PayValue >= @BeginValue AND PayValue <= @EndValue");
query.CreateParameter <int>("@BeginValue", filter.BeginValue, SqlDbType.Int);
query.CreateParameter <int>("@EndValue", filter.EndValue, SqlDbType.Int);
}
if (filter.State < 2)
{
condition.Append(" AND State = @State");
query.CreateParameter <bool>("@State", (filter.State == 1), SqlDbType.Bit);
}
if (filter.BeginDate != null)
{
condition.Append(" AND PayDate >= @BeginDate");
query.CreateParameter <DateTime>("@BeginDate", filter.BeginDate.Value, SqlDbType.DateTime);
}
if (filter.EndDate != null)
{
condition.Append(" AND PayDate <= @EndDate");
query.CreateParameter <DateTime>("@EndDate", filter.EndDate.Value, SqlDbType.DateTime);
}
if (!string.IsNullOrEmpty(filter.Username))
{
condition.Append(" AND UserID IN( SELECT UserID FROM bx_Users WHERE Username LIKE '%' + @Username +'%')");
query.CreateParameter <string>("@Username", filter.Username, SqlDbType.NVarChar, 50);
}
}
if (condition.Length > 5)
{
condition.Remove(0, 5);
}
query.Pager.TableName = "bx_Pay";
query.Pager.PageSize = pageSize;
query.Pager.PageNumber = pageNumber;
query.Pager.PrimaryKey = "PayID";
query.Pager.SortField = SordField;
query.Pager.Condition = condition.ToString();
query.Pager.SelectCount = true;
query.Pager.IsDesc = true;
using (XSqlDataReader reader = query.ExecuteReader())
{
UserPayCollection usertypelist = new UserPayCollection(reader);
if (reader.NextResult())
{
while (reader.Next)
{
usertypelist.TotalRecords = reader.Get <int>(0);
}
}
return(usertypelist);
}
}
}
public override UserPayCollection AdminSearchUserPays(PaylogFilter filter, int pageSize, int pageNumber)
{
using (SqlQuery query = new SqlQuery())
{
string SordField = "PayID";
StringBuilder condition = new StringBuilder();
if (filter != null)
{
if (!string.IsNullOrEmpty(filter.OrderNo))
{
condition.Append(" AND OrderNo LIKE '%' + @OrderNo +'%'");
query.CreateParameter<string>("@OrderNo", filter.OrderNo, SqlDbType.NVarChar,200);
}
if (!string.IsNullOrEmpty(filter.TransactionNo))
{
condition.Append(" AND TransactionNo LIKE '%' + @TransactionNo +'%'");
query.CreateParameter<string>("@TransactionNo", filter.TransactionNo, SqlDbType.NVarChar, 200);
}
if (filter.Payment > 0)
{
condition.Append(" AND Payment = @Payment");
query.CreateParameter<byte>("@Payment", filter.Payment, SqlDbType.TinyInt);
}
if (filter.BeginAmount > 0 && filter.EndAmount > 0)
{
condition.Append(" AND OrderAmount >= @BeginAmount AND OrderAmount <= @EndAmount");
query.CreateParameter<decimal>("@BeginAmount", filter.BeginAmount, SqlDbType.Decimal);
query.CreateParameter<decimal>("@EndAmount", filter.EndAmount, SqlDbType.Decimal);
}
if (filter.PayType > 0)
{
condition.Append(" AND PayType LIKE '%' + @PayType + '%'");
query.CreateParameter<byte>("@PayType", filter.PayType, SqlDbType.TinyInt);
}
if (filter.BeginValue>0 && filter.EndValue>0)
{
condition.Append(" AND PayValue >= @BeginValue AND PayValue <= @EndValue");
query.CreateParameter<int>("@BeginValue", filter.BeginValue, SqlDbType.Int);
query.CreateParameter<int>("@EndValue", filter.EndValue, SqlDbType.Int);
}
if (filter.State < 2)
{
condition.Append(" AND State = @State");
query.CreateParameter<bool>("@State", (filter.State==1), SqlDbType.Bit);
}
if (filter.BeginDate != null)
{
condition.Append(" AND PayDate >= @BeginDate");
query.CreateParameter<DateTime>("@BeginDate", filter.BeginDate.Value, SqlDbType.DateTime);
}
if (filter.EndDate != null)
{
condition.Append(" AND PayDate <= @EndDate");
query.CreateParameter<DateTime>("@EndDate", filter.EndDate.Value, SqlDbType.DateTime);
}
if (!string.IsNullOrEmpty(filter.Username))
{
condition.Append(" AND UserID IN( SELECT UserID FROM bx_Users WHERE Username LIKE '%' + @Username +'%')");
query.CreateParameter<string>("@Username", filter.Username, SqlDbType.NVarChar, 50);
}
}
if (condition.Length > 5)
condition.Remove(0, 5);
query.Pager.TableName = "bx_Pay";
query.Pager.PageSize = pageSize;
query.Pager.PageNumber = pageNumber;
query.Pager.PrimaryKey = "PayID";
query.Pager.SortField = SordField;
query.Pager.Condition = condition.ToString();
query.Pager.SelectCount = true;
query.Pager.IsDesc = true;
using (XSqlDataReader reader = query.ExecuteReader())
{
UserPayCollection usertypelist = new UserPayCollection(reader);
if (reader.NextResult())
{
while (reader.Next)
usertypelist.TotalRecords = reader.Get<int>(0);
}
return usertypelist;
}
}
}
