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