public IEnumerable <UserRoleEntity> GetRoleList(string userid)
{
var sql = PetaPoco.Sql.Builder.Append(@"select * from Base_UserRole where UserId=@0", userid);
var list = Base_UserRole.Query(sql);
return(EntityConvertTools.CopyToList <Base_UserRole, UserRoleEntity>(list.ToList()));
}
public IEnumerable <UserRoleEntity> GetPageList(Pagination pagination, string queryJson)
{
var sql = PetaPoco.Sql.Builder.Append(@"select * from Base_UserRole where 1=1");
var queryParam = queryJson.ToJObject();
//查询条件
if (!queryParam["condition"].IsEmpty() && !queryParam["keyword"].IsEmpty())
{
string condition = queryParam["condition"].ToString();
string keyword = queryParam["keyword"].ToString();
//switch (condition)
//{
// case "EnCode": //岗位编号
// sql.Append(" and (charindex(@0,EnCode)>0)", keyword);
// break;
// case "FullName": //岗位名称
// sql.Append(" and (charindex(@0,FullName)>0)", keyword);
// break;
// default:
// break;
//}
}
if (!string.IsNullOrWhiteSpace(pagination.sidx))
{
sql.OrderBy(new object[] { pagination.sidx + " " + pagination.sord });
}
var currentpage = Base_UserRole.Page(pagination.page, pagination.rows, sql);
//数据对象
var pageList = currentpage.Items;
//分页对象
pagination.records = Converter.ParseInt32(currentpage.TotalItems);
return(EntityConvertTools.CopyToList <Base_UserRole, UserRoleEntity>(pageList.ToList()));
}
public IEnumerable <UserRoleEntity> GetList()
{
var sql = PetaPoco.Sql.Builder.Append(@"select * from Base_UserRole ");
var list = Base_UserRole.Query(sql);
return(EntityConvertTools.CopyToList <Base_UserRole, UserRoleEntity>(list.ToList()));
}
public List <TestEntity> Query()
{
var list = T_Test.Query("");
List <TestEntity> aa = EntityConvertTools.CopyToList <T_Test, TestEntity>(list.ToList());
return(aa);
}
/// <summary>
/// 机构列表
/// </summary>
/// <returns></returns>
public IEnumerable <OrganizeEntity> GetList()
{
var sql = PetaPoco.Sql.Builder.Append(@"select * from Base_Organize where 1=1");
sql.Append(" order by SortCode");
var list = Base_Organize.Query(sql);
return(EntityConvertTools.CopyToList <Base_Organize, OrganizeEntity>(list.ToList()));
}
/// <summary>
/// 区域列表
/// </summary>
/// <returns></returns>
public IEnumerable <AreaEntity> GetList()
{
var sql = PetaPoco.Sql.Builder.Append(@"select * from Base_Area where 1=1 and Layer<>4 and EnabledMark<>0");
sql.Append(" order by CreateDate desc");
var list = Base_Area.Query(sql);
return(EntityConvertTools.CopyToList <Base_Area, AreaEntity>(list.ToList()));
}
/// <summary>
/// 角色列表
/// </summary>
/// <returns></returns>
public IEnumerable <RoleEntity> GetList()
{
var sql = PetaPoco.Sql.Builder.Append(@"select * from Base_Role where 1=1 and Category=@0 and EnabledMark=1 and DeleteMark=0", (int)QSDMS.Model.Enums.RoleCategoryEnum.角色);
sql.Append(" order by CreateDate desc");
var list = Base_Role.Query(sql);
return(EntityConvertTools.CopyToList <Base_Role, RoleEntity>(list.ToList()));
}
/// <summary>
/// 部门列表
/// </summary>
/// <returns></returns>
public IEnumerable <DepartmentEntity> GetList()
{
var sql = PetaPoco.Sql.Builder.Append(@"select * from Base_Department where 1=1 ");
sql.Append(" order by CreateDate desc");
var list = Base_Department.Query(sql);
return(EntityConvertTools.CopyToList <Base_Department, DepartmentEntity>(list.ToList()));
}
/// <summary>
/// 视图列表
/// </summary>
/// <returns></returns>
public List <ModuleColumnEntity> GetList()
{
var sql = PetaPoco.Sql.Builder.Append(@"select * from Base_ModuleColumn where 1=1 ");
sql.Append(" order by SortCode");
var list = Base_ModuleColumn.Query(sql);
return(EntityConvertTools.CopyToList <Base_ModuleColumn, ModuleColumnEntity>(list.ToList()));
}
/// <summary>
/// 用户列表
/// </summary>
/// <returns></returns>
public IEnumerable <UserEntity> GetList()
{
var sql = PetaPoco.Sql.Builder.Append(@"select * from Base_User where 1=1 and UserId<>'System' and DeleteMark=0");
sql.Append(" order by CreateDate desc");
var list = Base_User.Query(sql);
return(EntityConvertTools.CopyToList <Base_User, UserEntity>(list.ToList()));
}
/// <summary>
/// 功能列表
/// </summary>
/// <returns></returns>
public IEnumerable <ModuleEntity> GetList()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM Base_Module Order By SortCode");
var list = Base_Module.Query(strSql.ToString());
return(EntityConvertTools.CopyToList <Base_Module, ModuleEntity>(list.ToList()));
}
/// <summary>
/// 日志列表
/// </summary>
/// <param name="pagination">分页</param>
/// <param name="queryJson">查询参数</param>
/// <returns></returns>
public IEnumerable <LogEntity> GetPageList(Pagination pagination, string queryJson)
{
var sql = PetaPoco.Sql.Builder.Append(@"select * from Base_Log where 1=1");
var queryParam = queryJson.ToJObject();
//日志分类
if (!queryParam["Category"].IsEmpty())
{
int categoryId = queryParam["CategoryId"].ToInt();
sql.Append(" and CategoryId=@0", categoryId);
}
//操作时间
if (!queryParam["StartTime"].IsEmpty() && !queryParam["EndTime"].IsEmpty())
{
DateTime startTime = queryParam["StartTime"].ToDate();
DateTime endTime = queryParam["EndTime"].ToDate().AddDays(1);
sql.Append(" and OperateTime>=@0", startTime);
sql.Append(" and OperateTime<=@0", endTime);
}
//操作用户Id
if (!queryParam["OperateUserId"].IsEmpty())
{
string operateUserId = queryParam["OperateUserId"].ToString();
sql.Append(" and OperateUserId=@0", operateUserId);
}
//操作用户账户
if (!queryParam["OperateAccount"].IsEmpty())
{
string OperateAccount = queryParam["OperateAccount"].ToString();
sql.Append(" and (charindex(@0,OperateAccount)>0)", OperateAccount);
}
//操作类型
if (!queryParam["OperateType"].IsEmpty())
{
string operateType = queryParam["OperateType"].ToString();
sql.Append(" and OperateType=@0", operateType);
}
//功能模块
if (!queryParam["Module"].IsEmpty())
{
string module = queryParam["Module"].ToString();
sql.Append(" and (charindex(@0,Module)>0)", module);
}
if (!string.IsNullOrWhiteSpace(pagination.sidx))
{
sql.OrderBy(new object[] { pagination.sidx + " " + pagination.sord });
}
var currentpage = Base_Log.Page(pagination.page, pagination.rows, sql);
//数据对象
var pageList = currentpage.Items;
//分页对象
pagination.records = Converter.ParseInt32(currentpage.TotalItems);
return(EntityConvertTools.CopyToList <Base_Log, LogEntity>(pageList));
}
/// <summary>
/// 获取部门下面用户列表
/// </summary>
/// <returns></returns>
public IEnumerable <UserEntity> GetDepartmentUserList()
{
var sql = PetaPoco.Sql.Builder.Append(@"SELECT u.* FROM Base_User u
LEFT JOIN Base_Department d ON d.DepartmentId = u.DepartmentId
WHERE 1=1
AND u.UserId <> 'System' AND u.EnabledMark = 1 AND u.DeleteMark=0");
var list = Base_User.Query(sql);
return(EntityConvertTools.CopyToList <Base_User, UserEntity>(list.ToList()));
}
/// <summary>
/// 获取视图列表
/// </summary>
/// <param name="objectId">对象Id</param>
/// <returns></returns>
public IEnumerable <AuthorizeEntity> GetModuleColumnList(string objectId)
{
var sql = PetaPoco.Sql.Builder.Append(@"select * from Base_Authorize where 1=1 and ItemType=3");
if (!string.IsNullOrEmpty(objectId))
{
sql.Append(" and ObjectId=@0", objectId);
}
var list = Base_Authorize.Query(sql);
return(EntityConvertTools.CopyToList <Base_Authorize, AuthorizeEntity>(list.ToList()));
}
/// <summary>
/// 获取数据权限列表
/// </summary>
/// <param name="objectId">对象Id</param>
/// <returns></returns>
public IEnumerable <AuthorizeDataEntity> GetAuthorizeDataList(string objectId)
{
var sql = PetaPoco.Sql.Builder.Append(@"select * from Base_AuthorizeData where 1=1");
if (!string.IsNullOrEmpty(objectId))
{
sql.Append(" and ObjectId=@0", objectId);
}
sql.Append(" order by SortCode");
var list = Base_AuthorizeDatum.Query(sql);
return(EntityConvertTools.CopyToList <Base_AuthorizeDatum, AuthorizeDataEntity>(list.ToList()));
}
/// <summary>
/// 获取对象列表
/// </summary>
/// <param name="UserId"></param>
/// <returns></returns>
public IEnumerable <UserRelationEntity> GetObjectList(string userId)
{
var sql = PetaPoco.Sql.Builder.Append(@"select * from Base_UserRelation where 1=1 ");
if (!string.IsNullOrEmpty(userId))
{
sql.Append(" and UserId=@0", userId);
}
sql.Append(" order by CreateDate desc");
var list = Base_UserRelation.Query(sql);
return(EntityConvertTools.CopyToList <Base_UserRelation, UserRelationEntity>(list.ToList()));
}
/// <summary>
/// 视图列表
/// </summary>
/// <param name="moduleId">功能Id</param>
/// <returns></returns>
public List <ModuleColumnEntity> GetList(string moduleId)
{
var sql = PetaPoco.Sql.Builder.Append(@"select * from Base_ModuleColumn where 1=1 ");
//if (!string.IsNullOrEmpty(moduleId))
//{
sql.Append(" and ModuleId=@0", moduleId);
//}
sql.Append(" order by SortCode");
var list = Base_ModuleColumn.Query(sql);
return(EntityConvertTools.CopyToList <Base_ModuleColumn, ModuleColumnEntity>(list.ToList()));
}
/// <summary>
/// 明细列表
/// </summary>
/// <param name="itemId">分类Id</param>
/// <returns></returns>
public IEnumerable <DataItemDetailEntity> GetList(string itemId)
{
var sql = PetaPoco.Sql.Builder.Append(@"select * from Base_DataItemDetail where 1=1");
if (!string.IsNullOrEmpty(itemId))
{
sql.Append(" and ItemId=@0", itemId);
}
sql.Append(" order by SortCode");
var list = Base_DataItemDetail.Query(sql);
return(EntityConvertTools.CopyToList <Base_DataItemDetail, DataItemDetailEntity>(list.ToList()));
}
/// <summary>
/// 过滤时段列表
/// </summary>
/// <param name="objectId">对象Id,用逗号隔开</param>
/// <returns></returns>
public IEnumerable <FilterTimeEntity> GetList(string objectId)
{
var sql = PetaPoco.Sql.Builder.Append(@"select * from Base_FilterTime where 1=1 ");
if (!string.IsNullOrEmpty(objectId))
{
sql.Append(" and ObjectId=@0", objectId);
}
sql.Append(" order by CreateDate desc");
var list = Base_FilterTime.Query(sql);
return(EntityConvertTools.CopyToList <Base_FilterTime, FilterTimeEntity>(list.ToList()));
}
public List <TestEntity> GetList(TestEntity para)
{
var sql = new StringBuilder();
sql.Append(@"select * from web_test1");
string where = ConverPara(para);
if (!string.IsNullOrEmpty(where))
{
sql.AppendFormat(" where 1=1 {0}", where);
}
var list = web_test.Query(sql.ToString());
return(EntityConvertTools.CopyToList <web_test, TestEntity>(list.ToList()));
}
public List <SettingsEntity> GetList(SettingsEntity para)
{
var sql = new StringBuilder();
sql.Append(@"select * from tbl_Settings");
string where = ConverPara(para);
if (!string.IsNullOrEmpty(where))
{
sql.AppendFormat(" where 1=1 {0}", where);
}
var list = tbl_Setting.Query(sql.ToString());
return(EntityConvertTools.CopyToList <tbl_Setting, SettingsEntity>(list.ToList()));
}
public List <TrainingCustomFreeTimeEntity> GetList(TrainingCustomFreeTimeEntity para)
{
var sql = new StringBuilder();
sql.Append(@"select * from tbl_TrainingCustomFreeTime");
string where = ConverPara(para);
if (!string.IsNullOrEmpty(where))
{
sql.AppendFormat(" where 1=1 {0}", where);
}
var list = tbl_TrainingCustomFreeTime.Query(sql.ToString());
return(EntityConvertTools.CopyToList <tbl_TrainingCustomFreeTime, TrainingCustomFreeTimeEntity>(list.ToList()));
}
/// <summary>
/// 用户列表
/// </summary>
/// <param name="pagination">分页</param>
/// <param name="queryJson">查询参数</param>
/// <returns></returns>
public IEnumerable <UserEntity> GetPageList(Pagination pagination, UserEntity para)
{
var sql = PetaPoco.Sql.Builder.Append(@"select * from Base_User where 1=1 and UserId<>'System' and DeleteMark=0");
//公司主键
if (para.OrganizeId != null)
{
sql.Append(" and OrganizeId=@0", para.OrganizeId);
}
//部门主键
if (para.DepartmentId != null)
{
sql.Append(" and DepartmentId=@0", para.DepartmentId);
}
if (para.DepartmentId != null)
{
sql.Append(" and DepartmentId=@0", para.DepartmentId);
}
if (para.Account != null)
{
sql.Append("and (charindex(@0,Account)>0)", para.Account);
}
if (para.RealName != null)
{
sql.Append("and (charindex(@0,RealName)>0)", para.RealName);
}
if (para.Mobile != null)
{
sql.Append("and (charindex(@0,Mobile)>0)", para.Mobile);
}
if (para.CreateUserId != null)
{
sql.Append("and (CreateUserId=@0 or UserId=@0)", para.CreateUserId);
}
if (!string.IsNullOrWhiteSpace(pagination.sidx))
{
sql.OrderBy(new object[] { pagination.sidx + " " + pagination.sord });
}
var currentpage = Base_User.Page(pagination.page, pagination.rows, sql);
//数据对象
var pageList = currentpage.Items;
//分页对象
pagination.records = Converter.ParseInt32(currentpage.TotalItems);
return(EntityConvertTools.CopyToList <Base_User, UserEntity>(pageList.ToList()));
}
public List<MessageEntity> GetList(MessageEntity para)
{
var sql = new StringBuilder();
sql.Append(@"select * from tbl_Message");
string where = ConverPara(para);
if (!string.IsNullOrEmpty(where))
{
sql.AppendFormat(" where 1=1 {0}", where);
}
if (!string.IsNullOrWhiteSpace(para.sidx))
{
sql.AppendFormat(" order by {0} {1}", para.sidx, para.sord);
}
var list = tbl_Message.Query(sql.ToString());
return EntityConvertTools.CopyToList<tbl_Message, MessageEntity>(list.ToList());
}
/// <summary>
/// 区域列表
/// </summary>
/// <param name="parentId">节点Id</param>
/// <param name="keyword">关键字查询</param>
/// <returns></returns>
public IEnumerable <AreaEntity> GetList(string parentId, string keyword)
{
var sql = PetaPoco.Sql.Builder.Append(@"select * from Base_Area where 1=1");
if (!string.IsNullOrEmpty(parentId))
{
sql.Append(" and ParentId=@0", parentId);
}
if (!string.IsNullOrEmpty(keyword))
{
sql.Append(" and (charindex(@0,AreaCode)>0 or (charindex(@0,AreaName)>0", keyword);
}
var list = Base_Area.Query(sql);
return(EntityConvertTools.CopyToList <Base_Area, AreaEntity>(list.ToList()));
}
/// <summary>
/// 过滤IP列表
/// </summary>
/// <param name="objectId">对象Id</param>
/// <param name="visitType">访问:0-拒绝，1-允许</param>
/// <returns></returns>
public IEnumerable <FilterIPEntity> GetList(string objectId, string visitType)
{
var sql = PetaPoco.Sql.Builder.Append(@"select * from Base_FilterIP where 1=1 ");
if (!string.IsNullOrEmpty(visitType))
{
int _visittype = visitType.ToInt();
sql.Append(" and VisitType=@0", _visittype);
}
if (!string.IsNullOrEmpty(objectId))
{
sql.Append(" and ObjectId=@0", objectId);
}
sql.Append(" order by CreateDate desc");
var list = Base_FilterIP.Query(sql);
return(EntityConvertTools.CopyToList <Base_FilterIP, FilterIPEntity>(list.ToList()));
}
/// <summary>
/// 职位列表
/// </summary>
/// <param name="pagination">分页</param>
/// <param name="queryJson">查询参数</param>
/// <returns></returns>
public IEnumerable <RoleEntity> GetPageList(Pagination pagination, string queryJson)
{
var sql = PetaPoco.Sql.Builder.Append(@"select * from Base_Role where 1=1 and Category=@0", (int)QSDMS.Model.Enums.RoleCategoryEnum.职位);
var queryParam = queryJson.ToJObject();
//机构主键
if (!queryParam["organizeId"].IsEmpty())
{
string organizeId = queryParam["organizeId"].ToString();
sql.Append(" and OrganizeId=@0", organizeId);
}
//查询条件
if (!queryParam["condition"].IsEmpty() && !queryParam["keyword"].IsEmpty())
{
string condition = queryParam["condition"].ToString();
string keyword = queryParam["keyword"].ToString();
switch (condition)
{
case "EnCode": //职位编号
sql.Append(" and (charindex(@0,EnCode)>0)", keyword);
break;
case "FullName": //职位名称
sql.Append(" and (charindex(@0,FullName)>0)", keyword);
break;
default:
break;
}
}
if (!string.IsNullOrWhiteSpace(pagination.sidx))
{
sql.OrderBy(new object[] { pagination.sidx + " " + pagination.sord });
}
var currentpage = Base_Role.Page(pagination.page, pagination.rows, sql);
//数据对象
var pageList = currentpage.Items;
//分页对象
pagination.records = Converter.ParseInt32(currentpage.TotalItems);
return(EntityConvertTools.CopyToList <Base_Role, RoleEntity>(pageList.ToList()));
}
public List<MessageEntity> GetPageList(MessageEntity para, ref Pagination pagination)
{
var sql = new StringBuilder();
sql.Append(@"select * from tbl_Message");
string where = ConverPara(para);
if (!string.IsNullOrEmpty(where))
{
sql.AppendFormat(" where 1=1 {0}", where);
}
if (!string.IsNullOrWhiteSpace(pagination.sidx))
{
sql.AppendFormat(" order by {0} {1}", pagination.sidx, pagination.sord);
}
var currentpage = tbl_Message.Page(pagination.page, pagination.rows, sql.ToString());
//数据对象
var pageList = currentpage.Items;
//分页对象
pagination.records = Converter.ParseInt32(currentpage.TotalItems);
return EntityConvertTools.CopyToList<tbl_Message, MessageEntity>(pageList.ToList());
}
public List <CourseItemEntity> GetList(CourseItemEntity para)
{
var sql = new StringBuilder();
sql.Append(@"select * from tbl_CourseItem");
string where = ConverPara(para);
if (!string.IsNullOrEmpty(where))
{
sql.AppendFormat(" where 1=1 {0}", where);
}
if (para != null)
{
if (!string.IsNullOrWhiteSpace(para.sidx))
{
sql.AppendFormat(" order by {0} {1}", para.sidx, para.sord);
}
}
var list = tbl_CourseItem.Query(sql.ToString());
return(EntityConvertTools.CopyToList <tbl_CourseItem, CourseItemEntity>(list.ToList()));
}
public virtual List <SettingsEntity> GetList(SettingsEntity para)
{
var sql = new StringBuilder();
sql.Append(@"select * from Sys_Settings");
string where = ConverPara(para);
if (!string.IsNullOrEmpty(where))
{
sql.AppendFormat(" where 1=1 {0}", where);
}
if (para != null)
{
if (!string.IsNullOrWhiteSpace(para.sidx))
{
sql.AppendFormat(" order by {0} {1}", para.sidx, para.sord);
}
}
var list = db.Query <Sys_Setting>(sql.ToString());
return(EntityConvertTools.CopyToList <Sys_Setting, SettingsEntity>(list.ToList()));
}
