public List <Model.Manager> GetManagerPage(RLib.DB.DbConn dbconn, int pno, int pagesize, string keywords, out int totalcount)
{
string sql = @"select * from (select row_number() over (order by m.managerid asc) as rownum, * from manager m where state<>-1 and
name like ('%'+@keywords+'%')
and
subname like ('%'+@keywords+'%')
and
loginname like ('%'+@keywords+'%'))
A where A.rownum>@startindex and A.rownum<=@startindex+@pagesize; ";
string countsql = @"select count(1) from manager where state<>-1 and
name like ('%'+@keywords+'%')
and
subname like ('%'+@keywords+'%')
and
loginname like ('%'+@keywords+'%')";
var models = dbconn.Query <Model.Manager>(sql, new
{
keywords = keywords ?? "",
startindex = (pno - 1) * pagesize,
pagesize = pagesize
});
totalcount = dbconn.ExecuteScalar <int>(countsql, new
{
keywords = keywords ?? ""
});
return(models);
}
public List <Model.Manager> GetByLoginName(RLib.DB.DbConn dbconn, string loginname)
{
string sql = "select * from manager where loginname=@loginname and state<>-1;";
var model = dbconn.Query <Model.Manager>(sql, new { loginname = loginname });
return(model.ToList());
}
public List <Model.Manager> GetTagUsers(RLib.DB.DbConn dbconn, int usertagid)
{
string sql = "select m.* from managertaglink ml join manager m on m.managerid=ml.managerid where m.state<>-1 and ml.usertagid=@usertagid;";
List <Model.Manager> r = dbconn.Query <Model.Manager>(sql, new { usertagid = usertagid });
return(r);
}
public List <string> TagPermission(RLib.DB.DbConn dbconn, int usertagid)
{
string sql = "select permissionKey from tagpermission where usertagid=@usertagid;";
List <string> r = dbconn.Query <string>(sql, new { usertagid = usertagid });
return(r);
}
public Model.Manager GetManagerDetail(RLib.DB.DbConn dbconn, int managerid)
{
string sql = "select * from manager where managerid=@managerid;";
var model = dbconn.Query <Model.Manager>(sql, new { managerid = managerid }).FirstOrDefault();
return(model);
}
public List <string> ManagerKeys(RLib.DB.DbConn dbconn, int managerid)
{
string sql = "SELECT distinct tp.permissionKey FROM managertaglink ml " +
"join tagpermission tp on ml.usertagId=tp.usertagId " +
"where ml.managerId=@managerid;";
List <string> r = dbconn.Query <string>(sql, new { managerid = managerid });
return(r);
}
public List <Model.Manager> GetManagerMiniTop(RLib.DB.DbConn dbconn, int topcount)
{
string sql = @"select top " + Math.Max(0, topcount) + " managerid,name,subname from manager where state<>-1 ;";
var models = dbconn.Query <Model.Manager>(sql, new
{
topcount = topcount
});
return(models);
}