public int CountMaxAmountOrderByNum(int userId, int[] gameplayways, BetStatus invalidStatus, DateTime date)
{
string gpwCondition = string.Empty;
bool isFirst = true;
foreach (var gpwId in gameplayways)
{
if (isFirst) isFirst = false;
else gpwCondition += ",";
gpwCondition += gpwId;
}
string sql = string.Format(@";WITH USERTABLE AS
(
SELECT * FROM tb_User WHERE {0}=@{0}
UNION ALL
SELECT B.* FROM tb_User AS B,USERTABLE AS C WHERE B.parentId=C.UserId and B.UserId>C.UserId
)
select COUNT(Distinct(bo.Num)) from tb_BetOrder bo WITH (NOLOCK)
join tb_BetSheet bs WITH (NOLOCK) on bs.SheetId=bo.SheetId
join USERTABLE u on u.UserId=bs.UserId
where bo.{1}<>@{1} and GamePlayWayId in ({2}) and
CONVERT(char(10),bo.{3},120)=CONVERT(char(10),@{3},120) ", User.USERID, BetOrder.STATUS, gpwCondition, BetOrder.CREATETIME);
object count = base.ExecuteScalar(sql, new SqlParameter(User.USERID, userId),
new SqlParameter(BetOrder.STATUS, (int)invalidStatus),
new SqlParameter(BetOrder.CREATETIME, date));
return Convert.ToInt32(count);
}
public void ClosedBet(long betId, BetStatus status, double result, double teamRating, double teamBattleGain, int openedBetsCount, int closedBetsCount)
{
var bet = _repositoryOfBet.All().Where(bb => bb.Id == betId && bb.CloseDateTime != null && bb.CloseBetScreenshotId != null && bb.Status == (sbyte)status && bb.Result == result).Single();
Team(bet.TeamId);
TeamStatistics(bet.TeamId, teamRating, openedBetsCount, closedBetsCount);
TeamBattleStatistics(bet.BattleId, bet.TeamId, teamBattleGain, openedBetsCount, closedBetsCount);
}
public BetfairServerResponse <ClearedOrderSummaryReport> ListClearedOrders(
BetStatus betStatus,
ISet <string> eventTypeIds = null,
ISet <string> eventIds = null,
ISet <string> marketIds = null,
ISet <RunnerId> runnerIds = null,
ISet <string> betIds = null,
Side?side = null,
TimeRange settledDateRange = null,
GroupBy?groupBy = null,
bool?includeItemDescription = null,
int?fromRecord = null,
int?recordCount = null)
{
return(client.ListClearedOrders(
betStatus,
eventTypeIds,
eventIds,
marketIds,
runnerIds,
betIds,
side,
settledDateRange,
groupBy,
includeItemDescription,
fromRecord,
recordCount).Result);
}
public void Should_Flag_Unsettled_Bet_With_Highly_Unusual()
{
Customer customer = CreateSut();
customer.SettledBets = new List <Bet>
{
new Bet {
Stake = 10, Win = 0
}, new Bet {
Stake = 20, Win = 20
}, new Bet {
Stake = 30, Win = 30
},
};
customer.UnSettledBets = new List <Bet>
{
new Bet {
Stake = 601, Win = 0
}
};
customer.ComputeUnSettledBetsStatus();
BetStatus betStatus = customer.UnSettledBets.First().Status;
Assert.IsTrue(betStatus.IsHighlyUnusual);
Assert.IsFalse(betStatus.IsUnusual);
}
public void Should_Flag_Unsettled_Bet_With_Risky()
{
Customer customer = CreateSut();
customer.SettledBets = new List <Bet>
{
new Bet {
Stake = 10, Win = 0
}, new Bet {
Stake = 20, Win = 20
}, new Bet {
Stake = 30, Win = 30
},
};
customer.UnSettledBets = new List <Bet>
{
new Bet {
Stake = 110, Win = 0
}
};
customer.ComputeUnSettledBetsStatus();
BetStatus betStatus = customer.UnSettledBets.First().Status;
Assert.IsTrue(betStatus.IsRisky);
}
/// <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);
}
/// <summary>
/// 计算某条Sheet中指定状态的注单数量
/// </summary>
/// <param name="sheetdId">The sheetd id.</param>
/// <param name="status">The status.</param>
/// <returns></returns>
public int CountOrder(int sheetdId, BetStatus status)
{
string sql = string.Format(@"select * from {0} where {1}=@{1} and {2}=@{2}", BetOrder.TABLENAME, BetOrder.SHEETID, BetOrder.STATUS);
object count = base.ExecuteScalar(sql, new SqlParameter(BetOrder.SHEETID, sheetdId),
new SqlParameter(BetOrder.STATUS, (int)status));
return Convert.ToInt32(count);
}
public Task <BetfairServerResponse <ClearedOrderSummaryReport> > ListClearedOrders(
BetStatus betStatus,
ISet <string> eventTypeIds = null,
ISet <string> eventIds = null,
ISet <string> marketIds = null,
ISet <RunnerId> runnerIds = null,
ISet <string> betIds = null,
Side?side = null,
TimeRange settledDateRange = null,
GroupBy?groupBy = null,
bool?includeItemDescription = null,
int?fromRecord = null,
int?recordCount = null)
{
var args = new Dictionary <string, object>
{
[BET_STATUS] = betStatus,
[EVENT_TYPE_IDS] = eventTypeIds,
[EVENT_IDS] = eventIds,
[MARKET_IDS] = marketIds,
[RUNNER_IDS] = runnerIds,
[BET_IDS] = betIds,
[SIDE] = side,
[SETTLED_DATE_RANGE] = settledDateRange,
[GROUP_BY] = groupBy,
[INCLUDE_ITEM_DESCRIPTION] = includeItemDescription,
[FROM_RECORD] = fromRecord,
[RECORD_COUNT] = recordCount
};
return(networkClient.Invoke <ClearedOrderSummaryReport>(Endpoint.Betting, LIST_CLEARED_ORDERS_METHOD, args));
}
public BetfairServerResponse<ClearedOrderSummaryReport> ListClearedOrders(
BetStatus betStatus,
ISet<string> eventTypeIds = null,
ISet<string> eventIds = null,
ISet<string> marketIds = null,
ISet<RunnerId> runnerIds = null,
ISet<string> betIds = null,
Side? side = null,
TimeRange settledDateRange = null,
GroupBy? groupBy = null,
bool? includeItemDescription = null,
int? fromRecord = null,
int? recordCount = null)
{
return client.ListClearedOrders(
betStatus,
eventTypeIds,
eventIds,
marketIds,
runnerIds,
betIds,
side,
settledDateRange,
groupBy,
includeItemDescription,
fromRecord,
recordCount).Result;
}
public Bet(Guid spinId, string betString, decimal betAmount, int winningNumber,
decimal wonAmount, BetStatus status, string ipAddress, DateTime createDate)
{
Id = spinId;
BetString = betString;
BetAmount = betAmount;
WinningNumber = winningNumber;
WonAmount = wonAmount;
Status = status;
IpAddress = ipAddress;
CreateDate = createDate;
}
public IEnumerable<OrderAncestorCommInfo> GetAncestorComms(User user, int companyId, BetStatus status, DateTime date)
{
string sql = string.Format(@"select oac.* from tb_BetOrder bo WITH (NOLOCK)
join tb_BetSheet bs WITH (NOLOCK) on bs.SheetId=bo.SheetId
join tb_OrderAncestorCommInfo oac WITH (NOLOCK) on oac.OrderId=bo.OrderId
where bs.{0}=@{0} and bo.{1}=@{1} and bo.{2}=@{2} and CAST(bo.{3} as DATE)=CAST(@{3} as DATE)", BetSheet.USERID, BetOrder.COMPANYID,
BetOrder.STATUS, BetOrder.CREATETIME);
return base.ExecuteList<OrderAncestorCommInfo>(sql, new SqlParameter(BetSheet.USERID, user.UserId),
new SqlParameter(BetOrder.COMPANYID, companyId),
new SqlParameter(BetOrder.STATUS, (int)status),
new SqlParameter(BetOrder.CREATETIME, date));
}
public RDeepBet(RDeepPlayer player, RDeepBetPosition betPosition, List <Coin> betCoins)
{
try
{
Player = player;
BetPosition = betPosition;
BetCoins = betCoins;
Coins.SetBetStatus(betCoins, true);
status = BetStatus.Placed;
}
catch (Exception e)
{
Coins.SetBetStatus(betCoins, false);
throw e;
}
}
public void Should_Flag_Unsettled_Bet_With_Highly_Win_Rate()
{
Customer customer = CreateSut();
customer.UnSettledBets = new List <Bet>
{
new Bet {
Stake = 601, Win = 1000
}
};
customer.ComputeUnSettledBetsStatus();
BetStatus betStatus = customer.UnSettledBets.First().Status;
Assert.IsTrue(betStatus.HasHighWinAmount);
}
public void Should_Not_Flag_Unsettled_Bet_With_HighlyUnusual_If_Customer_Has_No_Bet_History()
{
Customer customer = CreateSut();
customer.UnSettledBets = new List <Bet>
{
new Bet {
Stake = 601, Win = 5000
}
};
customer.ComputeUnSettledBetsStatus();
BetStatus betStatus = customer.UnSettledBets.First().Status;
Assert.IsFalse(betStatus.IsHighlyUnusual);
}
/// <summary>
/// 计算指定日期，状态的注单数(包含下属用户)
/// </summary>
/// <param name="user">The user.</param>
/// <param name="status">The status.</param>
/// <param name="date">The date.</param>
/// <returns></returns>
public int CountSheets(User user, BetStatus status, DateTime date)
{
string sql = string.Format(@";WITH USERTABLE AS
(
SELECT * FROM tb_User WHERE UserId=@{0}
UNION ALL
SELECT B.* FROM tb_User AS B,USERTABLE AS C WHERE B.parentId=C.UserId and B.UserId>C.UserId
)
SELECT COUNT(0) FROM USERTABLE ut
join tb_BetSheet bs on bs.UserId=ut.UserId
where ut.{1}=@{1} and bs.{2}=@{2} and CONVERT(char(10),{3},120)=CONVERT(char(10),@{3},120)", User.USERID,
User.ROLEID, BetSheet.STATUS, BetSheet.CREATETIME);
object count = base.ExecuteScalar(sql, new SqlParameter(User.USERID, user.UserId),
new SqlParameter(User.ROLEID, (int)Role.Guest),
new SqlParameter(BetSheet.STATUS, (int)status),
new SqlParameter(BetSheet.CREATETIME, date));
return Convert.ToInt32(count);
}
public ClearedOrderSummaryReport listClearedOrders(BetStatus betStatus, ISet<string> eventTypeIds = null, ISet<string> eventIds = null, ISet<string> marketIds = null, ISet<RunnerId> runnerIds = null, ISet<string> betIds = null, Side? side = null, TimeRange settledDateRange = null, GroupBy? groupBy = null, bool? includeItemDescription = null, String locale = null, int? fromRecord = null, int? recordCount = null)
{
var args = new Dictionary<string, object>();
args[BET_STATUS] = betStatus;
args[EVENT_TYPE_IDS] = eventTypeIds;
args[EVENT_IDS] = eventIds;
args[MARKET_IDS] = marketIds;
args[RUNNER_IDS] = runnerIds;
args[BET_IDS] = betIds;
args[SIDE] = side;
args[SETTLED_DATE_RANGE] = settledDateRange;
args[GROUP_BY] = groupBy;
args[INCLUDE_ITEM_DESCRIPTION] = includeItemDescription;
args[LOCALE] = locale;
args[FROM_RECORD] = fromRecord;
args[RECORD_COUNT] = recordCount;
return Invoke<ClearedOrderSummaryReport>(LIST_CLEARED_ORDERS_METHOD, args);
}
public void CloseBet(long battleBetId, long userId, BetStatus status, out long battleId)
{
using (var unitOfWorkScope = _unitOfWorkScopeFactory.Create())
{
var bet = _repositoryOfBet.Get(EntitySpecifications.IdIsEqualTo<Bet>(battleBetId)).Single();
battleId = bet.BattleId;
if (userId != bet.UserId)
throw new ArgumentException("You are trying to close not your bet");
if (bet.IsClosed)
throw new ArgumentException("This bet is already closed");
bet.CloseDateTime = DateTime.UtcNow;
bet.CloseBetScreenshot = new BetScreenshot();
bet.StatusEnum = status;
var teamStatistics = _repositoryOfTeamStatistics.Get(EntitySpecifications.IdIsEqualTo<TeamStatistics>(bet.TeamId)).Single();
var teamBattleStatistics = _repositoryOfTeamBattleStatistics.Get(TeamBattleStatisticsSpecifications.BattleIdAndTeamIdAreEqualTo(bet.BattleId, bet.TeamId)).Single();
double balanceChange;
if (status == BetStatus.Succeeded)
balanceChange = bet.Amount * bet.Coefficient;
else if (status == BetStatus.Failed)
balanceChange = 0;
else if (status == BetStatus.CanceledByBookmaker)
balanceChange = bet.Amount;
else
throw new ArgumentOutOfRangeException("status");
bet.Result = balanceChange - bet.Amount;
teamStatistics.Rating += balanceChange;
teamBattleStatistics.Gain += balanceChange;
teamStatistics.OpenedBetsCount--;
teamBattleStatistics.OpenedBetsCount--;
teamStatistics.ClosedBetsCount++;
teamBattleStatistics.ClosedBetsCount++;
unitOfWorkScope.SaveChanges();
}
}
public IEnumerable <Bet> GetBetsByStatus(BetStatus status)
{
IEnumerable <Models.Bet> result;
string sql = $@"SELECT Id, AccountId, GameEventId, Amount, BetStatusId, Payout, CreateDate from dbo.Bet WHERE BetStatusId = @Status ORDER BY CreateDate ASC";
try
{
_logger.LogDebug($@"{_className}: Getting all Bets for Status {status}");
using (var connection = new SqlConnection(_connectionString))
{
result = connection.Query <Models.Bet>(sql, new { Status = status });
}
return(result);
}
catch (Exception e)
{
_logger.LogError($@"{_className}: Error getting Bets for Status {status} - {e.Message}");
return(null);
}
}
public async Task <BetDto> Handle(MakeBetCommand request, CancellationToken cancellationToken)
{
var user = await _queryRepository.FindUserById(_currentUserService.CurrentUserId)
?? throw new UserNotFoundException();
string betString = Convert.ToString(request.BetString);
CheckBet(betString, request.BetAmount, user.Balance);
int winningNumber = new Random().Next(0, 36);
var wonAmount = CheckBets.EstimateWin(betString, winningNumber);
BetStatus betStatus = wonAmount > 0 ? BetStatus.Won : BetStatus.Lost;
var jackpotAmount = await _queryRepository.GetCurrentJackpot();
jackpotAmount += request.BetAmount * 0.01M;
user.Balance -= request.BetAmount;
var bet = new BetEntity
{
SpinId = request.SpinId,
BetAmount = request.BetAmount,
BetString = betString,
IpAddress = _currentUserService.IpAddress,
JackpotAmount = jackpotAmount,
UserId = _currentUserService.CurrentUserId,
WonAmount = wonAmount,
BetStatus = betStatus,
CreatedAt = DateTime.Now
};
await _commandRepository.MakeBet(bet, user.Balance, winningNumber);
return(new BetDto {
SpinId = bet.SpinId, BetStatus = bet.BetStatus, WonAmount = bet.WonAmount, WinningNumber = winningNumber
});
}
/// <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="gameplayways">The gameplayways.</param>
/// <param name="invalidStatus">The invalid status.</param>
/// <param name="date">The date.</param>
/// <param name="pageItemCount">The page item count.</param>
/// <param name="pageIndex">Index of the page.</param>
/// <returns></returns>
public DataTable ListMaxAmountOrderByNum(int userId, int[] gameplayways, BetStatus invalidStatus, DateTime date, int startRow, int endRow)
{
string gpwCondition = string.Empty;
bool isFirst = true;
foreach (var gpwId in gameplayways)
{
if (isFirst) isFirst = false;
else gpwCondition += ",";
gpwCondition += gpwId;
}
string sql = string.Format(@";WITH USERTABLE AS
(
SELECT * FROM tb_User WHERE {0}=@{0}
UNION ALL
SELECT B.* FROM tb_User AS B,USERTABLE AS C WHERE B.parentId=C.UserId and B.UserId>C.UserId
)
select u.username,bo.* from tb_BetOrder bo
join tb_BetSheet bs on bs.SheetId=bo.SheetId
join USERTABLE u on u.UserId=bs.UserId
where bo.Num in (
select Num from(
select ROW_NUMBER() over(order by Sum(bo.Amount) desc) as RowNumber,bo.Num from tb_BetOrder bo
join tb_BetSheet bs on bs.SheetId=bo.SheetId
join USERTABLE u on u.UserId=bs.UserId
where bo.GamePlayWayId in ({1}) and bo.{2}<>@{2} and DATEDIFF(DD,bo.{3},@{3})=0 group by bo.Num
)T
where RowNumber between {4} and {5})
and bo.GamePlayWayId in ({1}) and bo.{2}<>@{2} and DATEDIFF(DD,bo.{3},@{3})=0",
User.USERID, gpwCondition, BetOrder.STATUS, BetOrder.CREATETIME, startRow, endRow);
return base.ExecuteDataTable(sql, new SqlParameter(User.USERID, userId),
new SqlParameter(BetOrder.STATUS, (int)invalidStatus),
new SqlParameter(BetOrder.CREATETIME, date));
}
public PagedList<BetOrder> SearchUserTodayOrderByCondition(User user, int? sheetId, BetStatus status, string num, int companyId, int gameplaywayId, string sortField, string sortType, int pageIndex)
{
IEnumerable<BetOrder> orders;
int totalCount;
int start = (pageIndex - 1) * pageSize + 1;
int end = pageIndex * pageSize;
if (sheetId.HasValue)
{
User betUser;
var sheet = DaSheet.GetBetSheet(sheetId.Value);
if (sheet == null || !ManagerHelper.Instance.GetManager<UserManager>().IsParent(user.UserId, sheet.UserId, out betUser))
throw new NoPermissionException("Get Bet Order", string.Format("用户:{0}没有查看Sheet：{1}的权限", user.UserId, sheetId));
orders =
DaOrder.ListOrderByCondition(sheetId, sheet.UserId, status, DateTime.Today, num, companyId, gameplaywayId, sortField, sortType, start, end);
totalCount =
DaOrder.CountOrderByCondition(sheetId, sheet.UserId, status, DateTime.Today, num, companyId, gameplaywayId);
}
else
{
orders = DaOrder.ListDescendantOrderByCondition(user.UserId, status, num, companyId, gameplaywayId, DateTime.Today, DateTime.Today, start, end);
totalCount = DaOrder.CountDescendantOrderCondition(user.UserId, status, num, companyId, gameplaywayId, DateTime.Today, DateTime.Today);
}
return new PagedList<BetOrder>(orders, pageIndex, pageSize, totalCount);
}
/// <summary>
/// 获取今日指定用户，公司的注单分成信息
/// </summary>
/// <param name="user">The user.</param>
/// <param name="companyId">The company id.</param>
/// <param name="state">The state.</param>
/// <returns></returns>
public IEnumerable<OrderAncestorCommInfo> GetTodayOac(User user, int companyId, BetStatus state)
{
return DaOAC.GetAncestorComms(user, companyId, state, DateTime.Today);
}
public FreezeFunds GetFreezeFundsByUser(int userId, BetStatus status)
{
string sql = string.Format(@"SELECT * FROM {0} WHERE {1}=@{1} AND {2}=@{2}", FreezeFunds.TABLENAME, FreezeFunds.USERID, FreezeFunds.STATUS);
return base.ExecuteModel<FreezeFunds>(sql, new SqlParameter(FreezeFunds.USERID, userId),
new SqlParameter(FreezeFunds.STATUS, (int)status));
}
/// <summary>
/// 获取下注列表
/// </summary>
/// <param name="status">sheet状态</param>
/// <param name="date">The date.</param>
/// <param name="startRow">开始获取数据的行数.</param>
/// <param name="endRow">结束获取数据的行数.</param>
/// <returns></returns>
public IEnumerable<BetSheet> ListBetSheet(BetStatus[] status, DateTime date, int startRow, int endRow)
{
string statusStatement = getStatusInStatement(status);
string sql = string.Format(@";WITH Sheet AS(
select * from
(
SELECT ROW_NUMBER() OVER(ORDER BY SheetId DESC) AS RowNumber,bs.*,u.RoleId,u.ParentId,u.UserName from {0} bs
join tb_User u on u.UserId=bs.UserId
where bs.Status in {1} And CONVERT(char(10),{2},120)=CONVERT(char(10),@{2},120)
)T
where RowNumber BETWEEN {3} AND {4}
)
select * from Sheet bs
inner join (select SheetId as SheetId,SUM(Turnover) as Turnover,
Sum(Commission) as Commission,SUM(NetAmount) as NetAmount from tb_BetOrder where SheetId in (select SheetId from Sheet) group by SheetId) bo
on bo.SheetId=bs.SheetId", BetSheet.TABLENAME, statusStatement, BetSheet.CREATETIME, startRow, endRow);
return base.ExecuteList<BetSheet>(sql, new SqlParameter(BetSheet.CREATETIME, date));
}
public IEnumerable<BetOrder> GetOrders(int userId, BetStatus status, DateTime day)
{
string sql = string.Format(@"SELECT BO.* FROM {0} BO
JOIN {1} BS ON BS.{2}=BO.{3}
WHERE BO.{4}=@{4} AND CONVERT(char(10),BO.{5},120)=CONVERT(char(10),@{5},120) AND BS.{6}=@{6}",
BetOrder.TABLENAME, BetSheet.TABLENAME, BetSheet.SHEETID, BetOrder.SHEETID,
BetOrder.STATUS, BetOrder.CREATETIME, BetSheet.USERID);
return base.ExecuteDataTable(sql, new SqlParameter(BetOrder.STATUS, (int)status),
new SqlParameter(BetOrder.CREATETIME, day),
new SqlParameter(BetSheet.USERID, userId)).AsEnumerable().Select(it => new BetOrder(it));
}
public void UpdateState(int orderId, BetStatus status)
{
string updateCancelTime = (status == BetStatus.Invalid) ? ", " + BetOrder.CANCELTIME + "=getdate()" : string.Empty;
string sql = string.Format(@"UPDATE {0} SET {1}=@{1} {2} WHERE {3}=@{3}", BetOrder.TABLENAME,
BetOrder.STATUS, updateCancelTime, BetOrder.ORDERID);
base.ExecuteNonQuery(sql, new SqlParameter(BetOrder.STATUS, (int)status),
new SqlParameter(BetOrder.ORDERID, orderId));
}
/// <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(int? sheetId, int userId, BetStatus status, DateTime date, string num, int companyId, int gameplaywayId)
{
string condition;
var parameterList = BuildCondition(sheetId, num, companyId, gameplaywayId, out condition, "bo.");
//添加用户条件
string subCondition = string.Format("bs.{0}=@{0}", BetSheet.USERID);
condition += string.IsNullOrEmpty(condition) ? subCondition : " AND " + subCondition;
parameterList.Add(new SqlParameter(BetSheet.USERID, userId));
condition = string.IsNullOrEmpty(condition) ? condition : " AND " + condition;
string sql = string.Format(@"SELECT COUNT(0) FROM {0} bo
JOIN {4} bs on bs.{5}=bo.{6}
WHERE bo.{1}=@{1} AND CONVERT(char(10),bo.{2},120)=CONVERT(char(10),@{2},120) {3}", BetOrder.TABLENAME, BetOrder.STATUS, BetOrder.CREATETIME, condition,
BetSheet.TABLENAME, BetSheet.SHEETID, BetOrder.SHEETID);
parameterList.Add(new SqlParameter(BetOrder.STATUS, (int)status));
parameterList.Add(new SqlParameter(BetOrder.CREATETIME, date));
object count = base.ExecuteScalar(sql, parameterList.ToArray());
return Convert.ToInt32(count);
}
/// <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);
}
public PagedList<BetSheet> GetTodaySheets(User user, BetStatus status, string num, int pageIndex)
{
int start = (pageIndex - 1) * pageSize + 1;
int end = pageIndex * pageSize;
return
string.IsNullOrEmpty(num) ?
new PagedList<BetSheet>(DaSheet.GetSheets(user, status, DateTime.Today, start, end), pageIndex, pageSize,
DaSheet.CountSheets(user, status, DateTime.Today))
:
new PagedList<BetSheet>(DaSheet.GetSheets(user, status, DateTime.Today, num, start, end), pageIndex, pageSize, DaSheet.CountSheets(user, status, DateTime.Today, num));
}
/// <summary>
/// 搜索会员今日的注单
/// </summary>
/// <param name="member">The member.</param>
/// <param name="sheetId">The sheet id.</param>
/// <param name="status">The status.</param>
/// <param name="num">The num.</param>
/// <param name="companyId">The company id.</param>
/// <param name="gameplaywayId">The gameplayway id.</param>
/// <param name="sortField">The sort field.</param>
/// <param name="sortType">Type of the sort.</param>
/// <param name="pageIndex">Index of the page.</param>
/// <returns></returns>
public PagedList<BetOrder> SearchMemberTodayOrderByCondition(User member, int? sheetId, BetStatus status, string num, int companyId, int gameplaywayId, string sortField, string sortType, 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;
if (sheetId.HasValue)
{
var sheet = DaSheet.GetBetSheet(sheetId.Value);
if (sheet == null || sheet.UserId != member.UserId)
throw new NoPermissionException("会员查看Sheet注单", string.Format("SheetId:{0},User:{1}", sheetId.Value, member));
}
return new PagedList<BetOrder>(DaOrder.ListOrderByCondition(sheetId, member.UserId, status, DateTime.Today, num, companyId, gameplaywayId, sortField, sortType, start, end),
pageIndex, pageSize, DaOrder.CountOrderByCondition(sheetId, member.UserId, status, DateTime.Today, num, companyId, gameplaywayId));
}
public PagedList<BetSheet> SearchUserSheets(User user, string userName, BetStatus status, string num, int pageIndex)
{
var userManager = ManagerHelper.Instance.GetManager<UserManager>();
var targetUser = userManager.DaUser.GetUserByUserName(userName);
if (targetUser == null) return PagedList<BetSheet>.Empty;
if (!userManager.IsParent(user.UserId, targetUser.UserId, out targetUser)) return PagedList<BetSheet>.Empty;
return GetTodaySheets(targetUser, status, num, pageIndex);
}
/// <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>
/// <param name="sortField">排序字段.</param>
/// <param name="sortType">排序类型(acs/desc).</param>
/// <param name="startRow">The start row.</param>
/// <param name="endRow">The end row.</param>
/// <returns></returns>
public IEnumerable<BetOrder> ListOrderByCondition(int? sheetId, int userId, BetStatus status, DateTime date, string num, int companyId,
int gameplaywayId, string sortField, string sortType, int startRow, int endRow)
{
if (string.IsNullOrEmpty(sortField)) sortField = BetOrder.ORDERID;
string condition;
var parameterList = BuildCondition(sheetId, num, companyId, gameplaywayId, out condition, "bo.");
//添加用户条件
string subCondition = string.Format("bs.{0}=@{0}", BetSheet.USERID);
condition += string.IsNullOrEmpty(condition) ? subCondition : " AND " + subCondition;
parameterList.Add(new SqlParameter(BetSheet.USERID, userId));
condition = string.IsNullOrEmpty(condition) ? condition : " AND " + condition;
string sql = string.Format(@"WITH CTE AS(select bo.*,bs.{10},bs.{6},u.{11} from {0} bo
join {1} bs on bs.{2}=bo.{3}
join {4} u on u.{5}=bs.{6}
where CONVERT(char(10),bo.{7},120)=CONVERT(char(10),@{7},120) and bo.{8}=@{8} {9} )
SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY {12}) AS RowNumber,*
FROM CTE
) T
WHERE RowNumber BETWEEN {14} AND {15} ORDER BY RowNumber {13}", BetOrder.TABLENAME, BetSheet.TABLENAME, BetSheet.SHEETID, BetOrder.SHEETID,
User.TABLENAME, User.USERID, BetSheet.USERID, BetOrder.CREATETIME, BetOrder.STATUS, condition, BetSheet.IPADDRESS, User.USERNAME,
sortField, sortType, startRow, endRow);
parameterList.Add(new SqlParameter(BetOrder.CREATETIME, date));
parameterList.Add(new SqlParameter(BetOrder.STATUS, (int)status));
return base.ExecuteList<BetOrder>(sql, parameterList.ToArray());
}
public IEnumerable<BetOrder> GetOrders(int userId, int companyId, BetStatus status, DateTime date)
{
string sql = string.Format(@"SELECT BO.*,BS.UserId FROM {0} BO
JOIN {1} BS ON BS.{2}=BO.{3}
WHERE BO.{4}=@{4} AND CAST(BO.{5} AS DATE)=CAST(@{5} AS DATE) AND BS.{6}=@{6} AND BO.{7}=@{7}",
BetOrder.TABLENAME, BetSheet.TABLENAME, BetSheet.SHEETID, BetOrder.SHEETID,
BetOrder.STATUS, BetOrder.CREATETIME, BetSheet.USERID, BetOrder.COMPANYID);
return base.ExecuteList<BetOrder>(sql, new SqlParameter(BetOrder.STATUS, (int)status),
new SqlParameter(BetOrder.CREATETIME, date),
new SqlParameter(BetSheet.USERID, userId),
new SqlParameter(BetOrder.COMPANYID, companyId));
}
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="status">The status.</param>
/// <param name="companyId">The company id.</param>
/// <param name="date">The date.</param>
/// <returns></returns>
public IEnumerable<BetOrder> GetOrders(BetStatus status, int companyId, DateTime date)
{
string sql = string.Format(@"SELECT bo.*,bs.{6} FROM {0} as bo with (nolock) JOIN {3} bs on bs.{4}=bo.{5} WHERE bo.{1}=@{1} AND CONVERT(char(10),bo.{2},120)=CONVERT(char(10),@{2},120) AND bo.{7}=@{7}",
BetOrder.TABLENAME, BetOrder.STATUS, BetOrder.CREATETIME, BetSheet.TABLENAME, BetSheet.SHEETID, BetOrder.SHEETID, BetSheet.USERID, BetOrder.COMPANYID);
return base.ExecuteDataTable(sql, new SqlParameter(BetOrder.STATUS, (int)status),
new SqlParameter(BetOrder.CREATETIME, date),
new SqlParameter(BetOrder.COMPANYID, companyId)).AsEnumerable().Select(it => new BetOrder(it));
}
/// <summary>
/// 修改Sheet所有Order的状态
/// </summary>
/// <param name="sheetId">The sheet id.</param>
/// <param name="status">The status.</param>
public void UpdateStateBySheet(int sheetId, BetStatus status)
{
string condition = status == BetStatus.Invalid ? string.Format(@", {0}=getdate()", BetOrder.CANCELTIME) : string.Empty;
string sql = string.Format(@"UPDATE {0} SET {1}=@{1} {3} WHERE {2}=@{2}", BetOrder.TABLENAME, BetOrder.STATUS, BetOrder.SHEETID, condition);
base.ExecuteNonQuery(sql, new SqlParameter(BetOrder.STATUS, (int)status),
new SqlParameter(BetOrder.SHEETID, sheetId));
}
/// <summary>
/// 获取指定用户的注单之和
/// </summary>
/// <param name="user">The user.</param>
/// <param name="status">The status.</param>
/// <param name="day">The day.</param>
/// <returns></returns>
public IEnumerable<BetOrder> GetOrdersTotals(User user, BetStatus status, DateTime day)
{
string sql = string.Format(@"select bo.Num,bo.CompanyId,bo.GamePlayWayId,SUM(bo.amount)as Amount,SUM(bo.Turnover)as Turnover,
SUM(bo.Commission)as Commission,SUM(bo.NetAmount)as NetAmount
from tb_BetOrder bo
join tb_BetSheet bs on bs.SheetId=bo.SheetId
where bs.{0}=@{0} and bo.{1}=@{1} and CAST(bo.{2} as DATE)=CAST(@{2} as DATE)
group by bo.CompanyId,bo.GamePlayWayId,bo.Num", BetSheet.USERID, BetOrder.STATUS, BetOrder.CREATETIME);
return base.ExecuteDataTable(sql, new SqlParameter(BetSheet.USERID, user.UserId),
new SqlParameter(BetOrder.STATUS, (int)status),
new SqlParameter(BetOrder.CREATETIME, day))
.AsEnumerable().Select(it => new BetOrder
{
Num = it.Field<string>(BetOrder.NUM),
CompanyId = it.Field<int>(BetOrder.COMPANYID),
GamePlayWayId = it.Field<int>(BetOrder.GAMEPLAYWAYID),
Amount = it.Field<decimal>(BetOrder.AMOUNT),
Turnover = it.Field<decimal>(BetOrder.TURNOVER),
Commission = it.Field<decimal>(BetOrder.COMMISSION),
NetAmount = it.Field<decimal>(BetOrder.NETAMOUNT)
});
}
public void UpdateBetSheetStatus(int sheetId, BetStatus status)
{
string sql = string.Format(@"UPDATE {0} SET {1}=@{1} WHERE {2}=@{2}", BetSheet.TABLENAME, BetSheet.STATUS, BetSheet.SHEETID);
base.ExecuteNonQuery(sql, new SqlParameter(BetSheet.STATUS, (int)status),
new SqlParameter(BetSheet.SHEETID, sheetId));
}
/// <summary>
/// 获取子用户所下注的汇总数据
/// </summary>
/// <param name="userId">The user id.</param>
/// <param name="status">The status.</param>
/// <param name="date">The date.</param>
/// <returns></returns>
public IEnumerable<BetOrder> ListChildBetAmounts(int userId, Role role, BetStatus status, DateTime date)
{
return base.ExecuteList<BetOrder>(CommandType.StoredProcedure, "SelectUserBetAmount", new SqlParameter(User.USERID, userId),
new SqlParameter(BetOrder.STATUS, (int)status),
new SqlParameter(User.ROLEID, (int)role),
new SqlParameter(BetOrder.CREATETIME, date));
}
public static int GetStatusNumber(BetStatus status)
{
return((int)status);
}
private StatusActionImageViewModel GeBetStatusImage(long battleBetId, BetStatus betStatus)
{
return GetBetStatusImageMappings(battleBetId)[betStatus];
}
