/// <summary>
/// track查询
/// </summary>
/// <param name="eventId"></param>
/// <param name="eventGroupId"></param>
/// <param name="keyValue"></param>
/// <returns></returns>
public List <TrackCyclingResponse> GetTrackCycling(int eventId, int eventGroupId, string keyValue)
{
List <TrackCyclingResponse> list = new List <TrackCyclingResponse>();
StringBuilder join = new StringBuilder();
var sql = $@" SELECT * FROM t_event_cycling_match
WHERE eventid = {eventId} and eventGroupId = {eventGroupId}
";
if (keyValue.IsNotEmpty())
{
var key = $"%{keyValue}%";
sql = $@" select t.id,t.eventId,t.eventGroupId,t.progroupNum,t.congroupNum,t.roomId,t.refereeId
,t.cyclingDetailId,t.isBye,t.cyclingMatchStatus
FROM
(
SELECT a.*,c.code as proCode,'' as conCode, d.completename as proCName,'' as conCName from t_event_cycling_match a
LEFT JOIN t_event_player_signup b on a.progroupNum = b.groupnum
LEFT JOIN t_member c on b.memberId = c.id
LEFT JOIN t_member_player d on d.memberId = c.id
where a.eventId = {eventId} and a.eventGroupId = {eventGroupId}
union
SELECT a.*,'' as proCode, c.code as conCode,'' as proCName, d.completename as conCName from t_event_cycling_match a
LEFT JOIN t_event_player_signup b on a.congroupNum = b.groupnum
LEFT JOIN t_member c on b.memberId = c.id
LEFT JOIN t_member_player d on d.memberId = c.id
where a.eventId = {eventId} and a.eventGroupId = {eventGroupId}
) t
where t.proCode like '{key}' or t.conCode like '{key}' or t.proCName like '{key}' or t.conCName like '{key}'
ORDER BY t.id
";
}
List <t_event_cycling_match> matchs = _dbContext.Query <t_event_cycling_match>(sql).ToList();
//初筛team成绩
var teams = _dbContext.Select <t_event_cycling_match_teamresult>(c => c.eventId == eventId && c.eventGroupId == eventGroupId).ToList();
//初筛player成绩
var players = _dbContext.Select <t_event_cycling_match_playerresult>(c => c.eventId == eventId && c.eventGroupId == eventGroupId).ToList();
foreach (var li in matchs)
{
TrackCyclingResponse obj = new TrackCyclingResponse()
{
id = li.id,
eventId = li.eventId,
eventGroupId = li.eventGroupId
,
congroupNum = li.congroupNum,
cyclingDetailId = li.cyclingDetailId,
cyclingMatchStatus = li.cyclingMatchStatus
,
isBye = li.isBye,
progroupNum = li.progroupNum,
refereeId = (int)li.refereeId,
roomId = (int)li.roomId
};
var q1 = from t in teams
where (t.groupNum == li.progroupNum || t.groupNum == li.congroupNum) && t.eventId == eventId && t.eventGroupId == eventGroupId && t.cyclingMatchId == li.id
select t;
//应该有两条记录，说明两个队伍pk
List <t_event_cycling_match_teamresult> curTeams = q1.Cast <t_event_cycling_match_teamresult>().ToList <t_event_cycling_match_teamresult>();
List <TrackCyclingTeamResponse> teamResp = new List <TrackCyclingTeamResponse>();
foreach (var ct in curTeams)
{
TrackCyclingTeamResponse ttp = new TrackCyclingTeamResponse()
{
id = ct.id,
cyclingMatchId = ct.cyclingMatchId
,
eventGroupId = ct.eventGroupId,
eventId = ct.eventId,
groupNum = ct.groupNum,
isWin = ct.isWin
,
totalScore = ct.totalScore,
winType = ct.winType
};
var q2 = from pp in players
where pp.eventId == eventId && pp.eventGroupId == eventGroupId && pp.cyclingMatchId == li.id && pp.groupNum == ct.groupNum
select pp;
List <t_event_cycling_match_playerresult> curPlayers = q2.Cast <t_event_cycling_match_playerresult>().ToList <t_event_cycling_match_playerresult>();
List <TrackCyclingPlayerResponse> arrPlayers = new List <TrackCyclingPlayerResponse>();
foreach (var pl in curPlayers)
{
var objp = new TrackCyclingPlayerResponse()
{
id = pl.id, cyclingMatchId = pl.cyclingMatchId
, eventGroupId = pl.eventGroupId, eventId = pl.eventId, groupNum = pl.groupNum
, playerId = pl.playerId, ranking = pl.ranking, refereeId = pl.refereeId, score = pl.score
};
arrPlayers.Add(objp);
}
ttp.players = arrPlayers;
teamResp.Add(ttp);
}
obj.teams = teamResp;
list.Add(obj);
}
return(list);
}
/// <summary>
/// 当前轮次的track
/// </summary>
/// <param name="eventId"></param>
/// <param name="eventGroupId"></param>
/// <param name="keyValue"></param>
/// <returns></returns>
public List <TrackCyclingResponse> GetTrackCyclingCur(int eventId, int eventGroupId, string keyValue)
{
List <TrackCyclingResponse> list = new List <TrackCyclingResponse>();
StringBuilder join = new StringBuilder();
//if (request.KeyValue.IsNotEmpty())
//{
// request.KeyValue = $"%{request.KeyValue}%";
// join.Append(" and (b.code like @KeyValue or b.completename like @KeyValue or a.groupnum like @KeyValue)");
//}
var sql = $@" SELECT * FROM t_event_cycling_match
WHERE cyclingDetailId IN (SELECT id FROM t_event_cycling_detail
WHERE eventId = {eventId} AND eventGroupId = {eventGroupId} AND cyclingraceId = (
SELECT id FROM t_event_cycling
WHERE eventId = {eventId} AND eventGroupId = {eventGroupId} AND cyclingRaceStatus = 2
)
)";
List <t_event_cycling_match> matchs = _dbContext.Query <t_event_cycling_match>(sql).ToList();
//初筛team成绩
var teams = _dbContext.Select <t_event_cycling_match_teamresult>(c => c.eventId == eventId && c.eventGroupId == eventGroupId).ToList();
//初筛player成绩
var players = _dbContext.Select <t_event_cycling_match_playerresult>(c => c.eventId == eventId && c.eventGroupId == eventGroupId).ToList();
foreach (var li in matchs)
{
TrackCyclingResponse obj = new TrackCyclingResponse()
{
id = li.id,
eventId = li.eventId,
eventGroupId = li.eventGroupId
,
congroupNum = li.congroupNum,
cyclingDetailId = li.cyclingDetailId,
cyclingMatchStatus = li.cyclingMatchStatus
,
isBye = li.isBye,
progroupNum = li.progroupNum,
refereeId = (int)li.refereeId,
roomId = (int)li.roomId
};
var q1 = from t in teams
where (t.groupNum == li.progroupNum || t.groupNum == li.congroupNum) && t.eventId == eventId && t.eventGroupId == eventGroupId && t.cyclingMatchId == li.id
select t;
//应该有两条记录，说明两个队伍pk
List <t_event_cycling_match_teamresult> curTeams = q1.Cast <t_event_cycling_match_teamresult>().ToList <t_event_cycling_match_teamresult>();
List <TrackCyclingTeamResponse> teamResp = new List <TrackCyclingTeamResponse>();
foreach (var ct in curTeams)
{
TrackCyclingTeamResponse ttp = new TrackCyclingTeamResponse()
{
id = ct.id, cyclingMatchId = ct.cyclingMatchId
, eventGroupId = ct.eventGroupId, eventId = ct.eventId, groupNum = ct.groupNum, isWin = ct.isWin
, totalScore = ct.totalScore, winType = ct.winType
};
var q2 = from pp in players
where pp.eventId == eventId && pp.eventGroupId == eventGroupId && pp.cyclingMatchId == li.id && pp.groupNum == ct.groupNum
select pp;
List <t_event_cycling_match_playerresult> curPlayers = q2.Cast <t_event_cycling_match_playerresult>().ToList <t_event_cycling_match_playerresult>();
List <TrackCyclingPlayerResponse> arrPlayers = new List <TrackCyclingPlayerResponse>();
foreach (var pl in curPlayers)
{
var objp = new TrackCyclingPlayerResponse()
{
id = pl.id,
cyclingMatchId = pl.cyclingMatchId
,
eventGroupId = pl.eventGroupId,
eventId = pl.eventId,
groupNum = pl.groupNum
,
playerId = pl.playerId,
ranking = pl.ranking,
refereeId = pl.refereeId,
score = pl.score
};
arrPlayers.Add(objp);
}
ttp.players = arrPlayers;
teamResp.Add(ttp);
}
obj.teams = teamResp;
list.Add(obj);
}
return(list);
}