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