public string EditUserRule(string userid, string ruleids)
{
try
{
using (BFdbContext db = new BFdbContext())
{
var tran = db.BeginTransaction();
db.ExecuteSqlCommand(string.Format("delete from cmn_relation where userid='{0}'", userid));
var rids = ruleids.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
foreach (var id in rids)
{
db.ExecuteSqlCommand(string.Format(@"insert into cmn_relation(userid, device, layer, ruleid)
select '{0}',a.device,a.layer,a.id from cmn_rule a where a.id='{1}'", userid, id));
}
tran.Commit();
return("");
}
}
catch (Exception ex)
{
log.Error(ex);
throw GetFault(ex);
}
}
public tblline GetLineById(int tid)
{
using (var db = new BFdbContext())
{
return(db.SqlQuery <tblline>("select a.* from tbl_line a,tbl_teams b where a.id=b.lineid and b.id=" + tid).FirstOrDefault());
}
}
/// <summary>
/// 检查队伍名称是否重复
/// </summary>
/// <param name="matchid"></param>
/// <param name="tname"></param>
/// <returns></returns>
public bool CheckTname(string matchid, string tname)
{
using (var db = new BFdbContext())
{
return(db.tblteams.Any(p => p.match_id == matchid && p.Teamname == tname));
}
}
/// <summary>
/// 更新队伍线路
/// </summary>
/// <param name="ent"></param>
/// <returns></returns>
public int AssignsTeams(List <AssignsTeam> AssignsTeam)
{
int ret = -1;
try
{
using (var db = new BFdbContext())
{
for (int i = 0; i < AssignsTeam.Count; i++)
{
tblteams ent = GetTeamById(AssignsTeam[i].teamid);
ent.Linesid = AssignsTeam[i].linesid;
db.TUpdate <tblteams>(ent);
}
db.SaveChanges();
}
ret = 0;
}
catch
{
ret = 99;
}
return(ret);
}
public int TeamDown(List <string> ids)
{
using (var db = new BFdbContext())
{
int res = 0;
using (var tx = db.BeginTransaction())
{
try
{
foreach (string id in ids)
{
string sqlteam = string.Format("update tbl_teams set eventid=2 where teamid='{0}'", id);
db.ExecuteSqlCommand(sqlteam);
string sqlusers = string.Format("update tbl_match_users set isdown='1' where teamid='{0}'", id);
db.ExecuteSqlCommand(sqlusers);
}
tx.Commit();
}
catch (Exception ex)
{
tx.Rollback();
throw ex;
}
}
return(res);
}
}
/// <summary>
/// 根据teamname查询队伍
/// </summary>
/// <param name="Teamname"></param>
/// <returns></returns>
public tblteams GetTeamByName(string Teamname)
{
using (var db = new BFdbContext())
{
return(db.tblteams.FirstOrDefault(p => p.Teamname == Teamname));
}
}
/// <summary>
/// 更新队伍
/// </summary>
/// <param name="ent"></param>
/// <returns></returns>
public int EditTeam(tblteams ent)
{
using (var db = new BFdbContext())
{
return(db.Update <tblteams>(ent));
}
}
public int GetLimitLinesCount(string linesid)
{
using (var db = new BFdbContext())
{
return(db.tbllines.FirstOrDefault(p => p.Lines_id == linesid).Pointscount.Value);
}
}
public int GetLinesTeamCount(string linesid)
{
using (var db = new BFdbContext())
{
return(db.FindAll <tblteams>(p => p.Linesid == linesid && p.Status == 0).ToList().Count);
}
}
/// <summary>
/// 删除数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entity"></param>
/// <returns></returns>
public int Delete <T>(T entity) where T : class
{
using (var db = new BFdbContext())
{
return(db.Delete <T>(entity));
}
}
/// <summary>
/// sql执行数据库
/// </summary>
/// <param name="sql"></param>
/// <param name="parms"></param>
/// <returns></returns>
public int ExecuteSqlCommand(string sql, params object[] parms)
{
using (var db = new BFdbContext())
{
return(db.ExecuteSqlCommand(sql, parms));
}
}
/// <summary>
/// 新增数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entity"></param>
/// <returns></returns>
public int Insert <T>(T entity) where T : class
{
using (var db = new BFdbContext())
{
return(db.Insert <T>(entity));
}
}
public string UpdateDict(List <CMNDICT> dictEntity)
{
try
{
var rs = false;
using (BFdbContext db = new BFdbContext())
{
using (db.BeginTransaction())
{
foreach (var entity in dictEntity)
{
db.TUpdate <CMNDICT>(entity);
}
}
db.SaveChanges();
rs = true;
}
return(rs ? "0" : "-1");
}
catch (Exception ex)
{
log.Error(ex);
throw GetFault(ex);
}
}
public string AddDict(List <CMNDICT> dictEntity)
{
try
{
var rs = false;
using (BFdbContext db = new BFdbContext())
{
db.ExecuteSqlCommand("delete from cmn_dict where dictid='3000'");
using (db.BeginTransaction())
{
foreach (var entity in dictEntity)
{
db.TInsert <CMNDICT>(entity);
}
}
db.SaveChanges();
rs = true;
}
return(rs ? "0" : "-1");
}
catch (Exception ex)
{
log.Error(ex);
throw GetFault(ex);
}
}
//读取线路待分配队伍
public List <tblteams> GetAssignsTeams(string matchid, string lineid, string linesid)
{
using (var db = new BFdbContext())
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT * from tbl_teams where status=0 ");
if (!string.IsNullOrEmpty(matchid))
{
sql.AppendFormat(" AND match_id = '{0}'", matchid);
}
if (!string.IsNullOrEmpty(lineid))
{
sql.AppendFormat(" AND lineid = '{0}'", lineid);
}
if (!string.IsNullOrEmpty(linesid))
{
sql.AppendFormat(" AND linesid = '{0}'", linesid);
}
sql.AppendFormat(" AND teamno not like '_____' order by rand() ");
return(db.SqlQuery <tblteams>(sql.ToString()).ToList());
}
}
public int TeamDis(TeamDisEntiyList r)
{
using (var db = new BFdbContext())
{
int res = -1;
using (var tx = db.BeginTransaction())
{
try
{
for (int i = 0; i < r.teams.Count; i++)
{
string id = r.teams[i].teamid;
tblteams team = db.tblteams.FirstOrDefault(p => p.teamid == id);
team.Lineid = r.lineid;
team.Linesid = r.linesid;
team.Teamno = r.teams[i].Teamno.PadLeft(5, '0');
db.TUpdate <tblteams>(team);
//BuildSerial1(id,team.Teamno);
}
db.SaveChanges();
tx.Commit();
res = 0;
}
catch (Exception ex)
{
tx.Rollback();
throw ex;
}
}
return(res);
}
}
/// <summary>
/// 根据线路赛事查询线路类型
/// </summary>
/// <param name="Teamname"></param>
/// <returns></returns>
public tbllines getLineBylinesid(string matchid, string linesid)
{
using (var db = new BFdbContext())
{
return(db.tbllines.FirstOrDefault(p => p.Match_id == matchid && p.Lines_id == linesid));
}
}
public string ResetSerial(string matchid, string lineid, string linesid)
{
string ret = "-1";
using (var db = new BFdbContext())
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append("update tbl_teams set teamno = '0' where 1=1 ");
if (!string.IsNullOrEmpty(matchid))
{
sql.AppendFormat(" AND match_id = '{0}'", matchid);
}
if (!string.IsNullOrEmpty(lineid))
{
sql.AppendFormat(" AND lineid = '{0}'", lineid);
}
if (!string.IsNullOrEmpty(linesid))
{
sql.AppendFormat(" AND linesid = '{0}'", linesid);
}
db.ExecuteSqlCommand(sql.ToString());
ret = "0";
}
catch { }
}
return(ret);
}
/// <summary>
/// 根据id查询队伍
/// </summary>
/// <param name="Teamname"></param>
/// <returns></returns>
public tblteams GetTeamById(string teamid)
{
using (var db = new BFdbContext())
{
return(db.tblteams.FirstOrDefault(p => p.teamid == teamid));
}
}
/// <summary>
/// zzy 2018-12-30
/// 设置队长
/// </summary>
/// <param name="mid"></param>
/// <returns></returns>
public int ReLeader(string mid)
{
using (var db = new BFdbContext())
{
var player = db.tblmatchusers.FirstOrDefault(p => p.Matchuserid == mid);
var leader = db.tblmatchusers.FirstOrDefault(p => p.Leader == 1 && p.Teamid == player.Teamid);
if (leader.Matchuserid == player.Matchuserid)
{
return(-1);
}
var team = db.tblteams.FirstOrDefault(p => p.teamid == leader.Teamid);
leader.Leader = 0;
leader.Mono = DateTime.Now.ToString("yyMMddHHmmss");
player.Leader = 1;
team.Userid = player.Userid;
db.TUpdate <tblmatchusers>(leader);
db.TUpdate <tblmatchusers>(player);
db.TUpdate <tblteams>(team);
return(db.SaveChanges());
}
}
/// <summary>
/// 删除队伍
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public int DeleteTeam(List <string> ids)
{
using (var db = new BFdbContext())
{
int res = 0;
using (var tx = db.BeginTransaction())
{
try
{
foreach (string id in ids)
{
tblteams ent = db.tblteams.FirstOrDefault(p => p.teamid == id);
ent.Status = 2;
db.TUpdate <tblteams>(ent);
}
db.SaveChanges();
tx.Commit();
}
catch (Exception ex)
{
tx.Rollback();
throw ex;
}
}
return(res);
}
}
/// <summary>
/// 查询team
/// </summary>
/// <param name="matchid"></param>
/// <returns></returns>
public List <tblteams> GetTeamsBymatchid(string matchid, string linesid)
{
using (var db = new BFdbContext())
{
return(db.FindAll <tblteams>(p => p.match_id == matchid && p.Linesid == linesid && p.Status == 0).ToList());
}
}
public PagedList <tblteamsVew> GetTeamsLinedown(string matchid, string teamname, string teamno, string dstatus, int pageindex)
{
using (var db = new BFdbContext())
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT ls.linename as linesname,u.name as nickname,t.*,u.mobile as Moblie,l.name as Linename,m.match_name as matchname FROM tbl_teams t left join tbl_line l on l.lineid = t.lineid left join tbl_users u on u.userid = t.userid left join tbl_match m on m.match_id = t.match_id left join tbl_lines ls on ls.lines_id = t.linesid where t.status = 0 ");
if (!string.IsNullOrEmpty(matchid))
{
sql.AppendFormat(" AND t.match_id = '{0}'", matchid);
}
if (!string.IsNullOrEmpty(teamname))
{
sql.AppendFormat(" AND t.teamname like '{0}%'", teamname);
}
if (!string.IsNullOrEmpty(teamno))
{
sql.AppendFormat(" AND t.teamno = '{0}'", teamno);
}
if (!string.IsNullOrEmpty(dstatus))
{
sql.AppendFormat(" AND t.eventid = {0}", dstatus);
}
return(db.SqlQuery <tblteamsVew, DateTime?>(sql.ToString(), pageindex, p => p.Createtime));
}
}
/// <summary>
/// 查询team
/// </summary>
/// <param name="matchid"></param>
/// <returns></returns>
public List <tblteams> CheckTeamsNo(string matchid, string teamno, string teamid)
{
using (var db = new BFdbContext())
{
return(db.FindAll <tblteams>(p => p.match_id == matchid && p.Teamno == teamno && p.Status == 0 && p.teamid != teamid && p.Teamno != "0").ToList());
}
}
public int AllTeamDown(string matchid)
{
using (var db = new BFdbContext())
{
int res = 0;
using (var tx = db.BeginTransaction())
{
try
{
string sqlteam = string.Format("update tbl_teams set eventid=2 where status=0 and match_id='{0}'", matchid);
db.ExecuteSqlCommand(sqlteam);
string sqlusers = string.Format("update tbl_match_users set isdown='1' where status=1 and match_id='{0}'", matchid);
db.ExecuteSqlCommand(sqlusers);
tx.Commit();
}
catch (Exception ex)
{
tx.Rollback();
throw ex;
}
}
return(res);
}
}
/// <summary>
/// 查询matchuser
/// </summary>
/// <param name="matchid"></param>
/// <returns></returns>
public List <tblmatchusers> GetMatchUserBymatchid(string matchid)
{
using (var db = new BFdbContext())
{
return(db.FindAll <tblmatchusers>(p => p.Match_Id == matchid && p.Status == "1").ToList());
}
}
public tblmatchentity GetMatchUserByUidMid(string userid, string matchid)
{
using (var db = new BFdbContext())
{
return(db.SqlQuery <tblmatchentity>("select a.*,b.match_name,c.teamno as teamno_t,d.linename as Lineno,b.date4,b.logopic from tbl_match_users a,tbl_match b,tbl_teams c,tbl_lines d where a.match_id=b.match_id and a.teamid=c.teamid and c.linesid=d.lines_id and a.status='1' and a.userid='" + userid + "' and a.match_id='" + matchid + "' and c.eventid = 3").FirstOrDefault());
}
}
/// <summary>
/// 导出队伍信息
/// </summary>
/// <param name="teamname"></param>
/// <param name="company"></param>
/// <param name="status"></param>
/// <param name="pageindex"></param>
/// <returns></returns>
public List <tblteamsVew> ExportTeams(string matchname, string teamname, string status)
{
using (var db = new BFdbContext())
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT t.*,u.mobile as Moblie,c.name as Companyname,l.name as Linename,m.match_name as Matchname FROM tbl_teams t left join tbl_company c on c.companyid = t.company left join tbl_line l on l.lineid = t.lineid left join tbl_users u on u.userid = t.userid left join tbl_match m on m.match_id = t.match_id where 1=1 ");
if (!string.IsNullOrEmpty(matchname))
{
sql.AppendFormat(" AND m.match_name like '%{0}%'", matchname);
}
if (!string.IsNullOrEmpty(teamname))
{
sql.AppendFormat(" AND t.teamname like '%{0}%'", teamname);
}
if (!string.IsNullOrEmpty(status))
{
sql.AppendFormat(" AND t.status = '{0}'", status);
}
return(db.SqlQuery <tblteamsVew>(sql.ToString()).ToList());
}
}
/// <summary>
/// 注册队伍
/// </summary>
/// <param name="id"></param>
/// <param name="tid"></param>
/// <param name="tname"></param>
/// <param name="tcom"></param>
/// <returns></returns>
public int RegTname(string id, string tid, string tname, string tcom, string pwd)
{
using (var db = new BFdbContext())
{
if (db.tblteams.Any(p => p.match_id == tid && p.Teamname == tname))
{
return(-1);
}
int uid = int.Parse(id);
var usr = db.tblusers.FirstOrDefault(p => p.userid == uid.ToString());
if (usr != null)
{
usr.Passwd = pwd;
db.Update <tblusers>(usr);
}
tblteams tm = new tblteams();
//tm.Company = string.IsNullOrEmpty(tcom) ? "个人" : tcom;
tm.Createtime = DateTime.Now;
tm.Eventid = 1;
tm.Lineid = "";
tm.match_id = tid;
tm.Status = 6;
tm.Teamname = tname;
tm.Teamno = "";
tm.Userid = id;
db.Insert <tblteams>(tm);
return(db.SqlQuery <autoid>("select @@identity as id").FirstOrDefault().Id);
}
}
public string EditRule(string ruleid, string rulename, string descr, string device, string layer)
{
try
{
using (BFdbContext db = new BFdbContext())
{
var rs = db.CMNRULE.Count(s => s.RULENAME == rulename && s.ID != ruleid) > 0;
if (rs)
{
return("-1");
}
var ent = db.CMNRULE.FirstOrDefault(p => p.ID == ruleid);
ent.DESCRP = descr;
ent.DEVICE = device;
//ent.ID = ruleid;
ent.LAYER = layer;
ent.RULENAME = rulename;
return(db.Update <CMNRULE>(ent).ToString());
}
}
catch (Exception ex)
{
log.Error(ex);
throw GetFault(ex);
}
}
