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 int ExistLoginName(RLib.DB.DbConn dbconn, string loginname, int except_managerid)
{
string sql = "select count(1) from manager where loginname=@loginname and state<>-1 and managerid<>@managerid;";
int count = dbconn.ExecuteScalar <int>(sql, new { loginname = loginname, managerid = except_managerid });
return(count);
}
public bool ExistPermissionKey(RLib.DB.DbConn dbconn, int managerid, string permissionkey)
{
string sql = "SELECT count(1) FROM managertaglink ml " +
"join tagpermission tp on ml.usertagId=tp.usertagId " +
"where ml.managerId=@managerid and tp.permissionKey=@permissionkey;";
int r = dbconn.ExecuteScalar <int>(sql, new { managerid = managerid, permissionkey = permissionkey });
return(r > 0);
}