/// <summary>
/// 获取全部菜单列表
/// </summary>
/// <param name="rolename"></param>
/// <returns></returns>
public List <TbMenuEntity> GetAllMenuByRoleId(string roleid)
{
try
{
using (BFdbContext db = new BFdbContext())
{
if (db.DatabaseType == DatabaseType.Mysql)
{
string sql = string.Format(@"select a.*,if(isnull(b.menuid),'1','0') FLG from Tb_menu a left join
(select tb.menuid from Tb_rolemenurelation tb where tb.roleid='{0}') b
on a.id=b.menuid", roleid);
return(db.SqlQuery <TbMenuEntity>(sql).ToList());
}
else
{
string sql = string.Format(@"select a.*,nvl2(b.menuid,'0','1') FLG from Tb_menu a,
(select tb.menuid from Tb_rolemenurelation tb where tb.roleid='{0}') b
where a.id=b.menuid(+)", roleid);
return(db.SqlQuery <TbMenuEntity>(sql).ToList());
}
}
}
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());
}
}
/// <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 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>
/// 导出队伍信息
/// </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());
}
}
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="teamname"></param>
/// <param name="company"></param>
/// <param name="status"></param>
/// <param name="pageindex"></param>
/// <returns></returns>
public List <tblordersView> ExportOrders(string teamname, string moblie, string status)
{
using (var db = new BFdbContext())
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT m.match_name as Matchname,t.teamname as Teamname,u.mobile as Mobile,l.name as Linename,o.* FROM tbl_orders o left join tbl_match m on m.match_id = o.match_id left join tbl_teams t on t.teamid = o.teamid left join tbl_users u on u.userid = o.userid left join tbl_line l on l.lineid = o.lineid where 1=1 ");
if (!string.IsNullOrEmpty(moblie))
{
sql.AppendFormat(" AND u.mobile like '%{0}%'", moblie);
}
if (!string.IsNullOrEmpty(teamname))
{
sql.AppendFormat(" AND t.teamname like '%{0}%'", teamname);
}
if (!string.IsNullOrEmpty(status))
{
sql.AppendFormat(" AND o.status = '{0}'", status);
}
return(db.SqlQuery <tblordersView>(sql.ToString()).ToList());
}
}
/// <summary>
/// 查询订单信息
/// </summary>
/// <param name="matchname"></param>
/// <param name="teamname"></param>
/// <param name="status"></param>
/// <param name="pageindex"></param>
/// <returns></returns>
public PagedList <tblordersView> GetOrders(string teamname, string moblie, string status, int pageindex)
{
using (var db = new BFdbContext())
{
StringBuilder sql = new StringBuilder();
sql.Append("select m.match_name as Matchname,t.teamname as Teamname,o.mobile as Mobile,l.name as Linename,o.* from(SELECT o.*,u.mobile from tbl_orders o ,tbl_users u where u.userid = o.userid) o left join tbl_match m on m.match_id = o.match_id left join tbl_teams t on t.teamid = o.teamid left join tbl_line l on l.lineid = o.lineid where 1=1 ");
if (!string.IsNullOrEmpty(moblie))
{
sql.AppendFormat(" AND o.mobile like '%{0}%'", moblie);
}
if (!string.IsNullOrEmpty(teamname))
{
sql.AppendFormat(" AND t.teamname like '%{0}%'", teamname);
}
if (!string.IsNullOrEmpty(status))
{
sql.AppendFormat(" AND o.status = '{0}'", status);
}
return(db.SqlQuery <tblordersView, DateTime?>(sql.ToString(), pageindex, p => p.Createtime));
}
}
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="userid"></param>
/// <returns></returns>
public List <tblmatch> GetMymatch(string userid)
{
using (var db = new BFdbContext())
{
string sql = string.Format(@"select a.* from tbl_match a,tbl_match_users b,tbl_users c where a.match_id=b.match_id and b.mobile=c.mobile and c.userid='{0}'", userid);
return(db.SqlQuery <tblmatch>(sql).ToList());
}
}
/// <summary>
/// 我的消息
/// </summary>
/// <param name="userid"></param>
/// <returns></returns>
public List <tblinfomation> GetMyinfo(string userid)
{
using (var db = new BFdbContext())
{
string sql = string.Format(@"select a.* from tbl_infomation a where a.type in ('2','3','4') and a.userid='{0}' order by createtime desc", userid);
return(db.SqlQuery <tblinfomation>(sql).ToList());
}
}
public tblusers GetUserByTeamId(string tid)
{
using (var db = new BFdbContext())
{
string sql = string.Format("select a.* from tbl_users a,tbl_teams b where a.userid=b.userid and b.teamid='{0}'", tid);
return(db.SqlQuery <tblusers>(sql).FirstOrDefault());
}
}
public List <tblmatch> GetMatcheByDt(DateTime dt)
{
using (var db = new BFdbContext())
{
string sql = "select * from tbl_match";
return(db.SqlQuery <tblmatch>(sql).ToList());
}
}
/// <summary>
/// 我的消息
/// </summary>
/// <param name="userid"></param>
/// <returns></returns>
public List <tblinfomation> GetMyinfo(string userid)
{
using (var db = new BFdbContext())
{
string sql = string.Format(@"select a.* from tbl_infomation a where a.type='2' and a.userid='{0}'", userid);
return(db.SqlQuery <tblinfomation>(sql).ToList());
}
}
/// <summary>
/// 获取我的比赛
/// </summary>
/// <param name="userid"></param>
/// <returns></returns>
public List <tblmatchentity> GetMymatch(string userid)
{
using (var db = new BFdbContext())
{
string sql = string.Format(@"SELECT a.*,b.match_name,b.date1,b.date2,b.date3,b.status as mstatus,c.status as paystatus FROM tbl_teams a,tbl_match b,tbl_orders c
where a.match_id=b.match_id and a.teamid=c.teamid and a.userid ='{0}'", userid);
return(db.SqlQuery <tblmatchentity>(sql).ToList());
}
}
public tblmatchentity GetMatchByOrderId(string oid)
{
using (var db = new BFdbContext())
{
string sql = string.Format("select a.*,c.mobile from tbl_match a,tbl_orders b,tbl_users c where a.match_id=b.match_id and b.userid=c.userid and b.orderid='{0}'", oid);
return(db.SqlQuery <tblmatchentity>(sql).FirstOrDefault());
}
}
/// <summary>
/// 查询线路信息
/// </summary>
public List <SelectListItem> GetLine()
{
using (var db = new BFdbContext())
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT a.lineid as value,a.name as text FROM tbl_line a order by a.name");
return(db.SqlQuery <SelectListItem>(sql.ToString()).ToList());
}
}
/// <summary>
/// 获取我的比赛
/// </summary>
/// <param name="userid"></param>
/// <returns></returns>
public List <tblmatchentity> GetMymatch(string userid)
{
using (var db = new BFdbContext())
{
string sql = string.Format(@"select a.*,b.leader,b.pay,concat(b.teamname,'【',d.linename,'】') as teamname,b.teamid,CAST(c.status as char) as mstatus,c.teamtype
from tbl_match a,tbl_match_users b,tbl_teams c ,tbl_lines d where a.match_id=b.match_id and
b.teamid=c.teamid and c.linesid=d.lines_id
and (b.status in ('0','1') or b.leader=1) and b.userid='{0}' order by c.createtime desc", userid);
return(db.SqlQuery <tblmatchentity>(sql).ToList());
}
}
public List <tblmatchentity> GetMatchUserByUidMid2(string userid, string matchid)
{
using (var db = new BFdbContext())
{
return(db.SqlQuery <tblmatchentity>(string.Format(@"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 c.eventid = 3
and a.teamid in (select teamid from tbl_match_users where status='1' and userid='{0}'
and match_id='{1}') order by a.leader desc", userid, matchid)).ToList());
}
}
/// <summary>
/// 根据dictid查询字典
/// </summary>
/// <param name="dictid"></param>
/// <returns></returns>
public List <SelectListItem> GetDict(int dictid)
{
using (var db = new BFdbContext())
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT a.code as value,a.name as text FROM tbl_dict a WHERE 1=1");
sql.AppendFormat(" AND a.dictid = {0}", dictid);
return(db.SqlQuery <SelectListItem>(sql.ToString()).ToList());
}
}
public List <tblmatch> GetMatch(string status)
{
using (var db = new BFdbContext())
{
//if (!string.IsNullOrEmpty(status))
// return db.tblmatch.Where(p => p.Status == status).ToList();
//else
// return db.tblmatch.ToList();
string sql = "select * from tbl_match order by date1 desc";
return(db.SqlQuery <tblmatch>(sql).ToList());
}
}
/// <summary>
/// 查询赛事分组
/// </summary>
/// <param name="matchid"></param>
/// <returns></returns>
public List <GroupLines> GetGroupLines(string matchid)
{
using (var db = new BFdbContext())
{
StringBuilder sql = new StringBuilder();
sql.Append("select m.match_id as matchid,m.match_name as matchname,n.lineid,n.name as linename,l.lines_id as linesid,linename as linesname,l.line_no as linesno from tbl_lines l left join tbl_match m on m.match_id = l.match_id left join tbl_line n on n.lineid = l.line_id where 1=1 ");
if (!string.IsNullOrEmpty(matchid))
{
sql.AppendFormat(" AND l.match_id = '{0}'", matchid);
}
return(db.SqlQuery <GroupLines>(sql.ToString()).ToList());
}
}
/// <summary>
/// 查询线路信息
/// </summary>
/// <param name="linename"></param>
/// <param name="players"></param>
/// <param name="pageindex"></param>
/// <returns></returns>
public PagedList <tblline> GetLines(string linename, int pageindex)
{
using (var db = new BFdbContext())
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT a.* FROM tbl_line a WHERE 1=1");
if (!string.IsNullOrEmpty(linename))
{
sql.AppendFormat(" AND a.linename like like '%{0}%'", linename);
}
return(db.SqlQuery <tblline, DateTime?>(sql.ToString(), pageindex, p => p.Createtime));
}
}
public List <TBUSERLOG> GetHeartBeatInterval(int interval = 10)
{
try
{
using (BFdbContext db = new BFdbContext())
{
if (db.DatabaseType == DatabaseType.Mysql)
{
string sql = string.Format("select * from tb_userlog t where t.type = '9' and TIMESTAMPDIFF(MINUTE,t.createdate,now())>{0}", interval);
return(db.SqlQuery <TBUSERLOG>(sql).ToList());
}
else
{
string sql = string.Format("select * from tb_userlog t where t.type = '9' and round(to_number(sysdate - t.createdate) * 24 * 60)>{0}", interval);
return(db.SqlQuery <TBUSERLOG>(sql).ToList());
}
}
}
catch (Exception ex)
{
log.Error(ex);
throw GetFault(ex);
}
}
/// <summary>
/// 查询队伍信息
/// </summary>
/// <param name="matchid"></param>
/// <returns></returns>
public List <GroupUser> GetGrouUser(string matchid)
{
using (var db = new BFdbContext())
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT t.matchuserid, t.userid,t.nickname,t.mono as userno,t.leader,t.teamid from tbl_match_users t where t.status = 1 ");
if (!string.IsNullOrEmpty(matchid))
{
sql.AppendFormat(" AND t.match_id = '{0}'", matchid);
}
sql.Append(" order by t.mono desc ");
return(db.SqlQuery <GroupUser>(sql.ToString()).ToList());
}
}
/// <summary>
/// 查询队伍信息
/// </summary>
/// <param name="matchid"></param>
/// <returns></returns>
public List <GroupTeam> GetGroupTeams(string matchid)
{
using (var db = new BFdbContext())
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT t.teamid,t.teamname,t.teamno,t.lineid,t.linesid from tbl_teams t where t.status=0 ");
if (!string.IsNullOrEmpty(matchid))
{
sql.AppendFormat(" AND t.match_id = '{0}'", matchid);
}
sql.Append(" order by t.teamno desc ");
return(db.SqlQuery <GroupTeam>(sql.ToString()).ToList());
}
}
/// <summary>
/// 获取用户权限
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public List <TbMenuEntity> GetMenuByUserId(string id)
{
try
{
using (BFdbContext db = new BFdbContext())
{
string sql = string.Format(@"select distinct a.id,a.menuname,a.menucode from tb_menu a,tb_rolemenurelation b,tb_userrolerelation c
where a.id=b.menuid and b.roleid=c.roleid and lower(c.userid)='{0}'", id.ToLower());
return(db.SqlQuery <TbMenuEntity>(sql).ToList());
}
}
catch (Exception ex)
{
log.Error(ex);
throw GetFault(ex);
}
}
public List <CmnRuleEntity> GetRuleByUserid(string userid)
{
try
{
using (BFdbContext db = new BFdbContext())
{
string sql = string.Format(@"select a.userid,a.ruleid,a.device,a.layer,b.rulename,b.descrp from cmn_relation a,cmn_rule b
where a.ruleid=b.id and a.userid='{0}'", userid);
return(db.SqlQuery <CmnRuleEntity>(sql).ToList());
}
}
catch (Exception ex)
{
log.Error(ex);
throw GetFault(ex);
}
}
public List <tblmatchdetailentity> GetMatchUsersByUidMid(string userid, string matchid)
{
using (var db = new BFdbContext())
{
string sql = string.Format(@"SELECT a.*,b.match_name,b.date4 as MatchDate,b.lineno,b.Teamstatus,b.teamno_t,b.Matchstatus from tbl_match_users a,
(SELECT a.teamid,a.match_id,b.match_name,b.date4,
/* concat(case when a.match_id='6a61b95b-2d5d-4373-abaf-bf4e4c438800' then d.line_no else CONCAT(d.line_no,'-',d.linename) end,if(c.teamno is null,'',if(c.teamno='0','',if(c.teamno='00000','',concat('[', c.teamno,']'))))) lineno, */
case when a.match_id='6a61b95b-2d5d-4373-abaf-bf4e4c438800' then d.line_no else CONCAT(d.line_no,'-',d.linename) end lineno,
concat(c.`status` ,'') Teamstatus,
b.status Matchstatus,
if(c.teamno is null,'-',if(c.teamno='0','-',if(c.teamno='00000','-',c.teamno))) as teamno_t
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.userid='{0}' and a.match_id='{1}') b
WHERE a.teamid=b.teamid and a.match_id=b.match_id and a.status<>'9' order by a.leader desc", userid, matchid);
return(db.SqlQuery <tblmatchdetailentity>(sql).ToList());
}
}
/// <summary>
/// 获取菜单列表
/// </summary>
/// <param name="rolename"></param>
/// <returns></returns>
public List <TBMENU> GetMenuByRoleId(string id)
{
try
{
using (BFdbContext db = new BFdbContext())
{
string sql = string.Format(@"select distinct a.* from Tb_menu a,Tb_rolemenurelation b
where a.id=b.menuid and b.roleid='{0}'", id);
return(db.SqlQuery <TBMENU>(sql).ToList());
}
}
catch (Exception ex)
{
log.Error(ex);
throw GetFault(ex);
}
}