public IEnumerable <Vip> GetPagedVips(DataTableAjaxConfig dtConfig, string vipName)
{
var sql = $"select * from t_vip as v where 1=1 ";
if (!string.IsNullOrEmpty(vipName))
{
sql += " and vipName like CONCAT('%',@VipName,'%') ";
}
return(GetPagedEntities <Vip>(sql, dtConfig, new { VipName = vipName }));
}
public IEnumerable <Permission> GetPagePermissionsList(DataTableAjaxConfig config)
{
config.recordCount = GetTotal();
var sql = @"with t as(select top (@start + @length) *, ROW_NUMBER() over(order by id) as num
from [dbo].[t_sys_permission] where isDeleted = 0 order by id asc )
select Id,PermissionName,PermissionUrl,Level,Sort,isnull( (select PermissionName from [dbo].[t_sys_permission] where id = t.ParentId) ,'') as ParentName , CreatedBy, CreatedTime, UpdatedBy,UpdatedTime, IsDeleted
from t where t.num > @start";
return(Connection.Query <Permission>(sql, config));
}
public IEnumerable <VipFee> GetPagedVipFees(DataTableAjaxConfig config, string keyword)
{
var whereStr = "";
if (!string.IsNullOrEmpty(keyword))
{
whereStr += " and (vipName like '%' + @Keyword + '%' or orderCode like '%' + @Keyword + '%') ";
}
return(GetPagedEntities <VipFee>($"select * from t_biz_vip_fee where status = 1 {whereStr}", config, new { Keyword = keyword }, order: "orderCode", isAsc: false));
}
public ActionResult AjaxGet(DataTableAjaxConfig dtConfig, string keyword = "")
{
var miles = _dal.GetPagedVipFees(dtConfig, keyword);
return(Json(new DataTableAjaxResult
{
draw = dtConfig.draw,
recordsFiltered = dtConfig.recordCount,
recordsTotal = dtConfig.recordCount,
data = miles
}, JsonRequestBehavior.AllowGet));
}
public IEnumerable <Role> GetPagedRoleList(DataTableAjaxConfig config)
{
config.recordCount = GetTotal();
var sql = @"with t as(
select top (@start + @length) *, ROW_NUMBER() over(order by id) as num
from t_sys_role where isDeleted = 0 order by id asc
)
select Id,RoleName,Description,CreatedBy, CreatedTime, UpdatedBy,UpdatedTime, IsDeleted
from t where t.num > @start";
return(Connection.Query <Role>(sql, config));
}
public ActionResult AjaxGetUsers(DataTableAjaxConfig dtConfig)
{
var users = _userService.GetPagedUserList(dtConfig);
return(Json(new DataTableAjaxResult
{
draw = dtConfig.draw,
recordsFiltered = dtConfig.recordCount,
recordsTotal = dtConfig.recordCount,
data = users
}, JsonRequestBehavior.AllowGet));
}
public ActionResult AjaxGetRoles(DataTableAjaxConfig dtConfig)
{
var roles = _roleService.GetRoles(dtConfig);
return(Json(new DataTableAjaxResult
{
draw = dtConfig.draw,
recordsFiltered = dtConfig.recordCount,
recordsTotal = dtConfig.recordCount,
data = roles
}, JsonRequestBehavior.AllowGet));
}
public IEnumerable <User> GetPagedUserList(DataTableAjaxConfig config)
{
config.recordCount = GetTotal();
var sql = @"with t as(
select top (@start + @length) *, ROW_NUMBER() over(order by id) as num
from t_sys_user where isDeleted = 0 and loginName <> 'admin' order by id asc
)
select Id,UserName,LoginName,Password,CreatedBy,CreatedTime, UpdatedBy,UpdatedTime, IsDeleted
from t where t.num > @start";
return(Connection.Query <User>(sql, config));
}
public ActionResult AjaxGet(DataTableAjaxConfig dtConfig)
{
var news = dal.GetPagedEntities <VideoClass>("select * from t_video_class", dtConfig).ToList();
return(Json(new DataTableAjaxResult
{
draw = dtConfig.draw,
recordsFiltered = dtConfig.recordCount,
recordsTotal = dtConfig.recordCount,
data = news
}, JsonRequestBehavior.AllowGet));
}
public ActionResult AjaxGet(DataTableAjaxConfig dtConfig)
{
var miles = _menuDal.GetPagedList(dtConfig);
return(Json(new DataTableAjaxResult
{
draw = dtConfig.draw,
recordsFiltered = dtConfig.recordCount,
recordsTotal = dtConfig.recordCount,
data = miles
}, JsonRequestBehavior.AllowGet));
}
public IEnumerable <VideoModel> GetPagedVideos(DataTableAjaxConfig dtConfig, string title)
{
var whereStr = " where 1=1";
if (!string.IsNullOrEmpty(title))
{
whereStr += $" and title like '%{title}%'";
}
var sql = "select v.*, c.ClassName from t_video as v inner join t_video_class as c on v.classId = c.id" + whereStr;
return(GetPagedEntities <VideoModel>(sql, dtConfig));
}
public ActionResult AjaxGet(DataTableAjaxConfig dtConfig, int isReply = 0, string videoName = "")
{
var miles = _dal.GetPagedComments(dtConfig, isReply, videoName);
return(Json(new DataTableAjaxResult
{
draw = dtConfig.draw,
recordsFiltered = dtConfig.recordCount,
recordsTotal = dtConfig.recordCount,
data = miles
}, JsonRequestBehavior.AllowGet));
}
public IEnumerable <RoleModel> GetRoles(DataTableAjaxConfig dtConfig)
{
var roles = _roleDal.GetPagedRoleList(dtConfig);
return(roles.Select(p => new RoleModel
{
Id = p.Id,
RoleName = p.RoleName,
Description = p.Description,
AddTime = p.CreatedTime
}));
}
public ActionResult AjaxGetFunctions(DataTableAjaxConfig dtConfig)
{
var functions = _funService.GetPageFunctionList(dtConfig);
return(Json(new DataTableAjaxResult
{
draw = dtConfig.draw,
recordsFiltered = dtConfig.recordCount,
recordsTotal = dtConfig.recordCount,
data = functions
}, JsonRequestBehavior.AllowGet));
}
public IEnumerable <T> GetPagedEntities <T>(string sql, DataTableAjaxConfig config, object parameters = null, string order = "id", bool isAsc = true) where T : class
{
config.recordCount = Connection.QueryFirstOrDefault <int>($"select count(1) from ({sql}) as t", parameters);
var _sql = $@"with t as(
select top ({config.start} + {config.length}) *, ROW_NUMBER() over(order by {order} {(isAsc ? "asc" : "desc")}) as num
from ({sql}) as tt
)
select *
from t where t.num > {config.start}";
return(Connection.Query <T>(_sql, parameters));
}
/// <summary>
/// 分页获取功能列表
/// </summary>
/// <param name="config"></param>
/// <returns></returns>
public IEnumerable <FunctionModel> GetPageFunctionList(DataTableAjaxConfig config)
{
var functions = _funcDal.GetPagePermissionsList(config);
return(functions.Select(p => new FunctionModel
{
Id = p.Id,
FunctionName = p.PermissionName,
FunctionLevel = p.Level,
FunctionSort = p.Sort,
PathUrl = p.PermissionUrl,
ParentName = p.ParentName,
}));
}
public ActionResult AjaxGet(DataTableAjaxConfig dtConfig, string title)
{
var whereStr = " where 1=1";
if (!string.IsNullOrEmpty(title))
{
whereStr += $" and title like '%{title}%'";
}
var list = dal.GetPagedVideos(dtConfig, title);
return(Json(new DataTableAjaxResult
{
draw = dtConfig.draw,
recordsFiltered = dtConfig.recordCount,
recordsTotal = dtConfig.recordCount,
data = list
}, JsonRequestBehavior.AllowGet));
}
public IEnumerable <VideoCommentModel> GetPagedComments(DataTableAjaxConfig dtConfig, int isReply, string videoName)
{
var whereStr = "";
var sql = @"SELECT tvc.*, tv.Title
FROM t_video_comment AS tvc INNER JOIN t_video AS tv ON tvc.VideoId = tv.Id
WHERE 1=1 ";
if (isReply == 1) //未回复
{
whereStr += " and replyTime is null";
}
else if (isReply == 2)
{
whereStr += " and replyTime is not null";
}
if (!string.IsNullOrEmpty(videoName))
{
whereStr += " and title like '%' + @Title + '%'";
}
return(GetPagedEntities <VideoCommentModel>(sql + whereStr, dtConfig, new { Title = videoName }, "createdTime", false));
}
/// <summary> /// 获取用户列表 /// </summary> /// <param name="config"></param> /// <returns></returns> public IEnumerable <User> GetPagedUserList(DataTableAjaxConfig config) => _dal.GetPagedUserList(config);
public IEnumerable <Notice> GetNoticeList(DataTableAjaxConfig config)
{
return(GetPagedEntities <Notice>("select * from t_biz_notice ", config, null, "createdTime", false));
}
public IEnumerable <Menu> GetPagedList(DataTableAjaxConfig config)
{
return(GetPagedEntities <Menu>("select * from t_biz_menu ", config));
}
