public void GenerEventCyclingMatch(List <t_event_cycling_match> list, t_event_cycling cyc)
{
try
{
_dbContext.BeginTransaction();
//先把当前轮次，组，比赛的准备录入状态的对垒表删除
string sql1 = $@" DELETE from t_event_cycling_match
where cyclingMatchStatus = 1 and eventid = {cyc.eventId} and eventgroupid = {cyc.eventGroupId} and cyclingDetailId in (SELECT id from t_event_cycling_detail where cyclingraceid = {cyc.id})
";
object r1 = _dbContext.ExecuteScalar(sql1);
string sql2 = $@" insert into t_event_cycling_match
(eventId,eventGroupId,progroupNum
,congroupNum
,roomId
,refereeId
,cyclingDetailId
,isBye
,cyclingMatchStatus
,createtime
,updatetime
,isdelete
) ";
string values = string.Empty;
for (int i = 0; i < list.Count; i++)
{
if (i == 0)
{
values += $@" SELECT '{list[i].eventId}','{list[i].eventGroupId}','{list[i].progroupNum}','{list[i].congroupNum}','{list[i].roomId}'
,'{list[i].refereeId}','{list[i].cyclingDetailId}',{list[i].isBye},1,'{DateTime.Now}','{DateTime.Now}',FALSE ";
}
else
{
values += $@" union ALL
SELECT '{list[i].eventId}','{list[i].eventGroupId}','{list[i].progroupNum}','{list[i].congroupNum}','{list[i].roomId}'
,'{list[i].refereeId}','{list[i].cyclingDetailId}',{list[i].isBye},1,'{DateTime.Now}','{DateTime.Now}',FALSE ";
}
}
if (list.Count > 0)
{
sql2 = sql2 + values;
object r2 = _dbContext.ExecuteScalar(sql2);
}
_dbContext.CommitChanges();
}
catch (Exception ex)
{
_dbContext.Rollback();
LogUtils.LogError("EventCyclingMatchRepo.GenerEventCyclingMatch", ex);
}
}
//赛事管理员赛事列表
public List <EventResponse> EventList(EventQueryRequest request)
{
List <EventResponse> list = new List <EventResponse>();
try
{
StringBuilder join = new StringBuilder();
if (request.EventType != null && request.EventType > 0)
{
join.Append(" and eventtype=@EventType");
}
if (request.KeyValue.IsNotEmpty())
{
request.KeyValue = $"%{request.KeyValue}%";
join.Append(" and (code like @KeyValue or name like @KeyValue)");
}
var sql = $@"select * from t_event where isdelete=0 and memberId=@MemberId {join.ToString()} order by id desc ";
int totalCount = 0;
list = _dbContext.Page <EventResponse>(sql, out totalCount, request.PageIndex, request.PageSize, request);
if (list != null && list.Count > 0)
{
foreach (var item in list)
{
//计算报名人数或者队伍
if (item.EventStatus != EventStatusEm.审核中 && item.EventStatus != EventStatusEm.拒绝)
{
item.SignUpCount = _dbContext.ExecuteScalar($"select count(distinct(groupnum)) from t_event_player_signup where isdelete=0 and signUpStatus in ({ParamsConfig._signup_in}) and eventId={item.Id}").ToObjInt();
}
}
}
request.Records = totalCount;
}
catch (Exception ex)
{
LogUtils.LogError("EventService.EventList", ex);
}
return(list);
}
// 生成会员编码
public string RenderCode(string code = "nsda")
{
lock (lockObject)
{
var dy = new DynamicParameters();
string sql = $@"select code from t_member where code like '{code}%' order by Id desc limit 1";
object obj = _dbContext.ExecuteScalar(sql);
if (obj == null || obj.ToString().IsEmpty())
{
return($"{code}1000001");
}
else
{
string number = obj.ToString();
number = number.Substring(code.Length);
int sequence = Convert.ToInt32(number);
sequence += 1;
return($"{code}{sequence}");
}
}
}
//查询当天组别签到人数
public int SignUpCount(int eventId, int eventGroupId)
{
int signUpCount = 0;
try
{
signUpCount = _dbContext.ExecuteScalar($"select count(*) from t_event_sign where eventId={eventId} and eventGroupId={eventGroupId} and eventSignType={(int)EventSignTypeEm.选手} and signdate={DateTime.Now.ToString("yyyy-MM-dd")}").ToObjInt();
}
catch (Exception ex)
{
LogUtils.LogError("EventSignService.SignUpCount", ex);
}
return(signUpCount);
}
public string getLogDetailSequence(string PROCESS_ID)
{
string result = null;
try
{
IDBContext db = DatabaseManager.Instance.GetContext();
result = db.ExecuteScalar <string>("CENTRALMessage/CENTRALMessageGetLogDetailSeq", new { PROCESS_ID = PROCESS_ID });
db.Close();
}
catch
{
}
return(result);
}
public string getProcessID()
{
string result = null;
try
{
IDBContext db = DatabaseManager.Instance.GetContext();
result = db.ExecuteScalar <string>("CENTRALMessage/CENTRALMessageGetProcessID", null);
db.Close();
}
catch
{
}
return(result);
}
//选手积分列表(返回总积分)
public List <PlayerPointsRecordResponse> PlayerPointsRecord(PlayerPointsRecordQueryRequest request, out decimal totalPoints)
{
totalPoints = 0m;
List <PlayerPointsRecordResponse> list = new List <PlayerPointsRecordResponse>();
try
{
StringBuilder sqljoin = new StringBuilder();
if (request.StartDate != null)
{
sqljoin.Append(" and createtime>=@StartDate ");
}
if (request.EndDate != null)
{
request.EndDate = request.EndDate.Value.AddDays(1).AddSeconds(-1);
sqljoin.Append(" and createtime<=@EndDate ");
}
var sqlTotalPoints = $@"select IFNULL(sum(points),0) from t_member_points_record where memberId=@MemberId and isdelete=0 {sqljoin.ToString()}";
totalPoints = _dbContext.ExecuteScalar(sqlTotalPoints, request).ToObjDecimal();
if (totalPoints > 0)//有积分再查询列表
{
var sql = $@"select b.starteventtime StartDate,b.endeventtime EndDate,b.name EventName,
a.points,c.name ProvinceName,d.name CityName,a.Id
from t_member_points_record a
inner join t_event b on a.eventId=b.id
left join t_sys_province c on b.provinceId=c.id
left join t_sys_city d on b.cityId = d.id
where a.isdelete=0 and a.memberId=@memberId {sqljoin.ToString()}
order by a.createtime desc
";
int totalCount = 0;
list = _dbContext.Page <PlayerPointsRecordResponse>(sql, out totalCount, request.PageIndex, request.PageSize, request);
request.Records = totalCount;
}
}
catch (Exception ex)
{
LogUtils.LogError("MemberPointsService.PlayerPointsRecord", ex);
}
return(list);
}
public string RenderCode(int eventId, string code = "t")
{
lock (lockObject)
{
var dy = new DynamicParameters();
string sql = $@"select groupnum from t_event_player_signup where eventId={eventId} and groupnum like '{code}%' order by Id desc limit 1";
object obj = _dbContext.ExecuteScalar(sql);
if (obj == null || obj.ToString().IsEmpty())
{
return($"{code}1001");
}
else
{
string number = obj.ToString();
number = number.Substring(code.Length);
int sequence = Convert.ToInt32(number);
sequence += 1;
return($"{code}{sequence}");
}
}
}
//教练下的学生列表
public List <CoachPlayerResponse> Coach_PlayerList(PlayerCoachQueryRequest request)
{
List <CoachPlayerResponse> list = new List <CoachPlayerResponse>();
try
{
var sql = @"select * from
(
select a.*,b.completepinyin PlayerPinYinName,b.completename PlayerName,b.code PlayerCode,c.PlayerPoints
from t_player_coach a
inner join t_member_player b on a.MemberId=b.memberId
inner join t_member_points c on a.memberId=c.memberId
where a.isdelete=0 and a.isCoach=0 and a.isPositive=1 and a.toMemberId=@MemberId
union all
select a.*,b.completepinyin PlayerPinYinName,b.completename PlayerName,b.code PlayerCode,c.PlayerPoints
from t_player_coach a
inner join t_member_player b on a.ToMemberId=b.memberId
inner join t_member_points c on a.memberId=c.memberId
where a.isdelete=0 and a.isCoach=1 and a.isPositive=0 and a.memberId=@MemberId
) a order by a.createTime desc
";
int totalCount = 0;
list = _dbContext.Page <CoachPlayerResponse>(sql, out totalCount, request.PageIndex, request.PageSize, request);
request.Records = totalCount;
if (list != null && list.Count > 0)
{
foreach (var item in list)
{
if (item.MemberId == request.MemberId)
{
item.Flag = true;
}
if (item.PlayerCoachStatus == PlayerCoachStatusEm.意)
{
var playerId = item.Flag ? item.ToMemberId : item.MemberId;
//参与比赛次数
item.Times = _dbContext.ExecuteScalar($"select count(distinct(eventGroupId)) from t_event_cycling_match_playerresult where playerId={playerId}").ToObjInt();
//指教期间获胜次数
StringBuilder sb = new StringBuilder();
if (item.EndDate.IsEmpty())
{
sb.Append($" and c.starteventdate>={item.StartDate} ");
}
else
{
sb.Append($" and c.starteventdate>={item.StartDate} and c.endeventdate<={item.EndDate}");
}
item.WinTimes = _dbContext.ExecuteScalar($@"select
(select count(a.id) Count from t_event_cycling_match_playerresult a
inner join t_event_cycling_match_teamresult b
on a.eventId = b.eventId and a.eventGroupId = b.eventGroupId and a.cyclingMatchId = b.cyclingMatchId
and a.groupNum = b.groupNum
inner join t_event c on a.eventId=c.id
where a.playerId = {playerId} and b.isWin = 1 {sb.ToString()})
+
(select count(a.id) Count from t_event_knockout_match_teamresult a
left join t_event_player_signup b on a.groupNum=b.groupNum and a.eventGroupId=b.eventGroupId and a.eventId=a.eventId
inner join t_event c on a.eventId=c.id
where b.memberId ={playerId} and a.isWin = 1 {sb.ToString()}) as Count").ToObjInt();
}
}
}
}
catch (Exception ex)
{
LogUtils.LogError("PlayerCoachService.Coach_PlayerList", ex);
}
return(list);
}