/// <summary>
/// 按条件计算下属用户注单数量
/// </summary>
/// <param name="userId">The user id.</param>
/// <param name="status">The status.</param>
/// <param name="num">The num.</param>
/// <param name="companyId">The company id.</param>
/// <param name="gameplayId">The gameplay id.</param>
/// <param name="fromDate">From date.</param>
/// <param name="toDate">To date.</param>
/// <returns></returns>
public int CountDescendantOrderCondition(int userId, BetStatus status, string num, int companyId, int gameplayId, DateTime fromDate, DateTime toDate, WinLost winlost = WinLost.All)
{
string condition;
var paramList = BuildCondition(null, num, companyId, gameplayId, out condition, "bo.");
condition = string.IsNullOrEmpty(condition) ? condition : " AND " + condition;
switch (winlost)
{
case WinLost.Win: condition += string.Format(@" AND {0}>0", BetOrder.DRAWRESULT); break;
case WinLost.Lost: condition += string.Format(@" AND {0}<0", BetOrder.DRAWRESULT); break;
default: break;
}
string sql = string.Format(@";WITH UserTree AS
(
SELECT * FROM {0} WHERE {1}=@{1}
UNION ALL
SELECT B.* FROM {0} AS B,UserTree AS C WHERE B.{2}=C.{1} and B.{1}>C.{1}
)
select COUNT(0) from {3} bo
join {4} bs on bs.{5}=bo.{6}
join UserTree ut on ut.{1}=bs.{7}
where bo.{8}=@{8} and CONVERT(char(10),bs.{9},120) BETWEEN CONVERT(char(10),@{10},120) AND CONVERT(char(10),@{11},120) {12}", User.TABLENAME, User.USERID, User.PARENTID, BetOrder.TABLENAME, BetSheet.TABLENAME,
BetSheet.SHEETID, BetOrder.SHEETID, BetSheet.USERID, BetOrder.STATUS, BetSheet.CREATETIME, "FromDate", "ToDate",
condition);
paramList.Add(new SqlParameter(User.USERID, userId));
paramList.Add(new SqlParameter(BetOrder.STATUS, (int)status));
paramList.Add(new SqlParameter("FromDate", fromDate));
paramList.Add(new SqlParameter("ToDate", toDate));
object count = base.ExecuteScalar(sql, paramList.ToArray());
return Convert.ToInt32(count);
}
public IEnumerable<BetOrder> ListOrderByCondition(User user, BetStatus status, string num, int companyId, int gameplayway, WinLost winlost, DateTime fromDate, DateTime toDate, int startRow, int endRow)
{
string condition;
var paramList = BuildCondition(null, num, companyId, gameplayway, out condition, "bo.");
condition = string.IsNullOrEmpty(condition) ? condition : " AND " + condition;
switch (winlost)
{
case WinLost.Win: condition += string.Format(@" AND {0}>0", BetOrder.DRAWRESULT); break;
case WinLost.Lost: condition += string.Format(@" AND {0}<0", BetOrder.DRAWRESULT); break;
default: break;
}
string sql = string.Format(@";WITH CTE AS(select bo.*,bs.IPAddress,bs.UserId,u.UserName from tb_BetOrder bo
join tb_BetSheet bs on bs.SheetId=bo.SheetId
join tb_User u on u.UserId=bs.UserId
where bs.{1}=@{1} and bo.{2}=@{2} and (CONVERT(char(10),BO.{4},120) BETWEEN CONVERT(char(10),@{5},120) AND CONVERT(char(10),@{6},120)) {3})
SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY {0}) AS RowNumber,*
FROM CTE
) T
WHERE RowNumber BETWEEN {7} AND {8} ORDER BY RowNumber", BetOrder.ORDERID,
BetSheet.USERID, BetOrder.STATUS, condition, BetOrder.CREATETIME, "From", "To", startRow, endRow);
paramList.Add(new SqlParameter(BetSheet.USERID, user.UserId));
paramList.Add(new SqlParameter(BetOrder.STATUS, (int)status));
paramList.Add(new SqlParameter("From", fromDate));
paramList.Add(new SqlParameter("To", toDate));
return base.ExecuteList<BetOrder>(sql, paramList.ToArray());
}
/// <summary>
/// 按条件搜索下属用户所有注单
/// </summary>
/// <param name="userId">The user id.</param>
/// <param name="status">The status.</param>
/// <param name="num">The num.</param>
/// <param name="companyId">The company id.</param>
/// <param name="gameplayId">The gameplay id.</param>
/// <param name="fromDate">From date.</param>
/// <param name="toDate">To date.</param>
/// <param name="startRow">The start row.</param>
/// <param name="endRow">The end row.</param>
/// <returns></returns>
public IEnumerable<BetOrder> ListDescendantOrderByCondition(int userId, BetStatus status, string num, int companyId, int gameplayId, DateTime fromDate, DateTime toDate, int startRow, int endRow, WinLost winlost = WinLost.All, string sortField = BetOrder.ORDERID, string sortType = "Asc")
{
string condition;
var paramList = BuildCondition(null, num, companyId, gameplayId, out condition, "bo.");
condition = string.IsNullOrEmpty(condition) ? condition : " AND " + condition;
switch (winlost)
{
case WinLost.Win: condition += string.Format(@" AND {0}>0", BetOrder.DRAWRESULT); break;
case WinLost.Lost: condition += string.Format(@" AND {0}<0", BetOrder.DRAWRESULT); break;
default: break;
}
string sql = string.Format(@";WITH UserTree AS
(
SELECT * FROM {0} WHERE {1}=@{1}
UNION ALL
SELECT B.* FROM {0} AS B,UserTree AS C WHERE B.{2}=C.{1} and B.{1}>C.{1}
)
select * from(
select ROW_NUMBER() over(order by {15} {16}) as RowNumber,bo.*,bs.IPAddress,u.UserName from {3} bo
join {4} bs on bs.{5}=bo.{6}
join UserTree u on u.{1}=bs.{7}
where bo.{8}=@{8} and CONVERT(char(10),bs.{9},120) BETWEEN CONVERT(char(10),@{10},120) AND CONVERT(char(10),@{11},120) {14})T
where RowNumber between {12} and {13}", User.TABLENAME, User.USERID, User.PARENTID, BetOrder.TABLENAME, BetSheet.TABLENAME,
BetSheet.SHEETID, BetOrder.SHEETID, BetSheet.USERID, BetOrder.STATUS, BetSheet.CREATETIME, "FromDate", "ToDate",
startRow, endRow, condition, sortField, sortType);
paramList.Add(new SqlParameter(User.USERID, userId));
paramList.Add(new SqlParameter(BetOrder.STATUS, (int)status));
paramList.Add(new SqlParameter("FromDate", fromDate));
paramList.Add(new SqlParameter("ToDate", toDate));
return base.ExecuteList<BetOrder>(sql, paramList.ToArray());
}
/// <summary>
/// 根据条件计算注单的数量
/// </summary>
/// <param name="sheetId">The sheet id.</param>
/// <param name="userId">The user id.</param>
/// <param name="status">The status.</param>
/// <param name="date">The date.</param>
/// <param name="num">The num.</param>
/// <param name="companyId">The company id.</param>
/// <param name="gameplaywayId">The gameplayway id.</param>
/// <returns></returns>
public int CountOrderByCondition(User user, BetStatus status, string num, int companyId, int gameplayId, WinLost winlost, DateTime fromDate, DateTime toDate)
{
string condition;
var paramList = BuildCondition(null, num, companyId, gameplayId, out condition, "bo.");
condition = string.IsNullOrEmpty(condition) ? condition : " AND " + condition;
switch (winlost)
{
case WinLost.Win: condition += string.Format(@" AND {0}>0", BetOrder.DRAWRESULT, string.IsNullOrEmpty(condition)); break;
case WinLost.Lost: condition += string.Format(@" AND {0}<0", BetOrder.DRAWRESULT, string.IsNullOrEmpty(condition)); break;
default: break;
}
string sql = string.Format(@"SELECT COUNT(0) FROM {0} bo join {1} bs on bs.{2}=bo.{3}
where bs.{4}=@{4} and bo.{5}=@{5} and (CONVERT(char(10),BO.{7},120) BETWEEN CONVERT(char(10),@{8},120) AND CONVERT(char(10),@{9},120)) {6}",
BetOrder.TABLENAME, BetSheet.TABLENAME, BetSheet.SHEETID, BetOrder.SHEETID, BetSheet.USERID, BetOrder.STATUS, condition, BetOrder.CREATETIME, "From", "To");
paramList.Add(new SqlParameter(BetSheet.USERID, user.UserId));
paramList.Add(new SqlParameter(BetOrder.STATUS, (int)status));
paramList.Add(new SqlParameter("From", fromDate));
paramList.Add(new SqlParameter("To", toDate));
object count = base.ExecuteScalar(sql, paramList.ToArray());
return Convert.ToInt32(count);
}
/// <summary>
/// 获取用户（包括下属用户）已结算的注单
/// </summary>
/// <param name="user">The user.</param>
/// <param name="num">The num.</param>
/// <param name="companyId">The company id.</param>
/// <param name="gameplaywayId">The gameplayway id.</param>
/// <param name="fromDate">From date.</param>
/// <param name="toDate">To date.</param>
/// <param name="pageIndex">Index of the page.</param>
/// <returns></returns>
public PagedList<BetOrder> GetUserSettleOrder(User user, string num, int companyId, int gameplaywayId, DateTime fromDate, DateTime toDate, WinLost winlost, int pageIndex)
{
int start = GetStart(pageIndex);
int end = GetEnd(pageIndex);
return new PagedList<BetOrder>(DaOrder.ListDescendantOrderByCondition(user.UserId, BetStatus.Settled, num, companyId, gameplaywayId, fromDate, toDate, start, end, winlost),
pageIndex, pageSize, DaOrder.CountDescendantOrderCondition(user.UserId, BetStatus.Settled, num, companyId, gameplaywayId, fromDate, toDate, winlost));
}
/// <summary>
/// 根据条件获取会员的输赢注单
/// </summary>
/// <param name="member">The member.</param>
/// <param name="num">The num.</param>
/// <param name="companyId">The company id.</param>
/// <param name="gameplaywayId">The gameplayway id.</param>
/// <param name="winlost">The winlost.</param>
/// <param name="fromDate">From date.</param>
/// <param name="toDate">To date.</param>
/// <param name="pageIndex">Index of the page.</param>
/// <returns></returns>
public PagedList<BetOrder> GetMemberWinLost(User member, string num, int companyId, int gameplaywayId, WinLost winlost,
DateTime fromDate, DateTime toDate, int pageIndex)
{
if (member.Role != Role.Guest) throw new InvalidDataException("Role", string.Format("用户:{0}不是Guest用户", member));
int start = (pageIndex - 1) * pageSize + 1;
int end = pageIndex * pageSize;
return new PagedList<BetOrder>(DaOrder.ListOrderByCondition(member, BetStatus.Settled, num, companyId, gameplaywayId, winlost, fromDate, toDate, start, end),
pageIndex, pageSize, DaOrder.CountOrderByCondition(member, BetStatus.Settled, num, companyId, gameplaywayId, winlost, fromDate, toDate));
}
