public List <ResSurveyQuestion> GetQuestioins(int tenantId, int[] surveyIds)
{
var sql = string.Format(@"SELECT q.* FROM Res_SurveyQuestion q LEFT JOIN Res_SurveyInfo s ON s.ExampaperID=q.ExampaperID
WHERE s.SurveyID IN ({0})", surveyIds.GetString());
return(_dataAccess.FetchListBySql <ResSurveyQuestion>(sql).ToList());
}
public List <string> GetSearchAbilityLevelList(int tenantId)
{
var list = _dataAccess.FetchListBySql <string>("select distinct AbilityLevel from Ab_AbilityInfo where IsDelete = 0").ToList();
list.AddRange(_dataAccess.FetchListBySql <string>("select LevelName from Ab_AbilityLevel"));
if (list.Count == 0)
{
_dataAccess.AddEntities(new List <AbilityLevel>()
{
new AbilityLevel()
{
LevelName = "低级", OrderNum = 0, TenantId = tenantId
},
new AbilityLevel()
{
LevelName = "中级", OrderNum = 1, TenantId = tenantId
},
new AbilityLevel()
{
LevelName = "高级", OrderNum = 2, TenantId = tenantId
},
new AbilityLevel()
{
LevelName = "专家级", OrderNum = 3, TenantId = tenantId
}
});
list.AddRange(new List <string>()
{
"低级", "中级", "高级", "专家级"
});
}
return(list.Distinct().ToList());
}
/// <summary>
/// 分页获取问题
/// </summary>
/// <param name="total"></param>
/// <param name="tenantId"></param>
/// <param name="sortId"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="order"></param>
/// <param name="rname"></param>
/// <param name="title"></param>
/// <param name="stime"></param>
/// <param name="etime"></param>
/// <param name="ishigh"></param>
/// <returns></returns>
public IEnumerable <FullTextIndexQuestion> GetQuestions(out int total, int tenantId, int sortId = 0, int pageIndex = 1, int pageSize = 20,
string order = "QuestionTime DESC", string rname = "", string title = "", DateTime?stime = null, DateTime?etime = null, int ishigh = 0)
{
var dataSql = @"SELECT q.QuestionId,q.Title,q.Question,q.Tags,q.NewAnswer,
q.isThematic,q.IsDelete,q.answerCount,
q.QuestionTime,q.AnswerId,q.Integration,q.SortId,q.LastAnswerTime,u.Realname, u1.Realname AS answerName,
q.LastAnswerUserId ,s.SortName,s.LevelPath
FROM Int_Question q
LEFT JOIN Int_QuestionSort s ON q.SortId=s.SortId
LEFT JOIN Sys_Users u ON q.UserId=u.UserId
LEFT JOIN sys_users u1 ON q.LastAnswerUserId=u1.UserId ";
var countSql = @"SELECT COUNT(*)
FROM Int_Question q
LEFT JOIN Int_QuestionSort s ON q.SortId=s.SortId
LEFT JOIN Sys_Users u ON q.UserId=u.UserId
LEFT JOIN sys_users u1 ON q.LastAnswerUserId=u1.UserId ";
var sqlwhere = "WHERE q.IsDelete=0 AND q.TenantId=" + tenantId;
if (sortId != 0)
{
//查询所有子分类
var allsorts = GetAllSorts(tenantId);
var allchildrens = new List <QuestionSort>();
GetAllSortChildren(allchildrens, allsorts, sortId);
var allsortIds = allchildrens.Select(p => p.SortId).ToList();
allsortIds.Add(sortId);
sqlwhere += " AND q.SortId IN (" + allsortIds.GetString() + ")";
}
if (ishigh != 0)
{
sqlwhere += " AND q.Integration>0";
order = "Integration DESC";
}
if (!string.IsNullOrEmpty(rname))
{
sqlwhere += " AND u.Realname LIKE '%" + rname.ReplaceSql() + "%'";
}
if (!string.IsNullOrEmpty(title))
{
sqlwhere += " AND q.Title LIKE '%" + title.ReplaceSql() + "%'";
}
if (stime.HasValue)
{
sqlwhere += " AND q.QuestionTime>='" + stime.Value.ToString("yyyy-MM-dd HH:mm:ss") +
"'";
}
if (etime.HasValue)
{
sqlwhere += " AND q.QuestionTime<='" + etime.Value.ToString("yyyy-MM-dd HH:mm:ss") +
"'";
}
total = _dataAccess.ExecuteScalar(countSql + sqlwhere).ToInt32();
if (total == 0)
{
return(new List <FullTextIndexQuestion>());
}
return(_dataAccess.FetchListBySql <FullTextIndexQuestion>(dataSql + sqlwhere));
}
/// <summary>
/// 从缓存中清除指定角色下的用户权限
/// </summary>
/// <param name="roleId"></param>
public void ClearRolePermissioni(int roleId)
{
var userIds = _dataAccess.FetchListBySql <int>("SELECT UserId FROM Sys_RoleUser WHERE RoleId=" + roleId);
foreach (var userId in userIds)
{
ClearUserPermission(userId);
}
}
/// <summary>
/// 根据用户Id查找用户
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public SysUser GetUserBySql(int userId)
{
var sql = string.Format(@"select s.*,t1.DeptName,t2.PostName,LeaderName=(select Realname from Sys_Users sysuser where sysuser.UserId = s.Leader) from dbo.Sys_Users s
LEFT JOIN Sys_Department t1 ON s.DeptId=t1.DeptId
LEFT JOIN Sys_Posts t2 ON s.PostId=t2.PostId
where UserId={0}", userId);
return(_dataAccess.FetchListBySql <SysUser>(sql).FirstOrDefault());
}
/// <summary>
/// 查询租户默认租户的权限
/// </summary>
/// <param name="TenantId"></param>
/// <returns></returns>
public IEnumerable <SysPermission> GetDefaultRoleByTenantId(int tenantId, int type)
{
var sql = string.Format(@"SELECT * FROM Sys_Permission
WHERE PermissionId IN (
SELECT PermissionId FROM Sys_TenantPermission
WHERE TenantId={0}) and UserType={1}", tenantId, type);
return(_dataAccess.FetchListBySql <SysPermission>(sql));
}
/// <summary>
/// 根据ID查询信息
/// </summary>
/// <param name="adminId"></param>
/// <returns></returns>
public SysAdmin GetSingleAdminById(int adminId)
{
var sql = @"SELECT *,
roleIds=(SELECT ltrim(str(RoleId))+',' FROM Sys_AdminRole WHERE AdminId=Sys_Admin.AdminId and
RoleId in (SELECT RoleId FROM Sys_OperationsRole WHERE IsDelete=0) FOR XML path(''))
FROM Sys_Admin
where AdminId=" + adminId;
return(_dataAccess.FetchListBySql <SysAdmin>(sql).FirstOrDefault());
}
/// <summary>
/// 我能报名的培训班
/// </summary>
/// <param name="totalCount"></param>
/// <param name="userId"></param>
/// <param name="where"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public List <TrainClassSignUpShow> GetCanSingUpClassList(out int totalCount, int userId, string where = "1=1", int pageIndex = 1,
int pageSize = int.MaxValue)
{
var sql = string.Format(@"SELECT * FROM (
SELECT row_number()OVER(ORDER BY ClassId) RowIndex,count(1)OVER(PARTITION BY null) totalCount,ClassId,ClassType,Code,tt.Number,StartTime, EndTime,SignUpCondition,SignUpEndTime,
SignUpUser=(SELECT count(1) FROM Tr_TrainClassToUser WHERE ClassId=tt.ClassId AND ApproveStatus=2),
tp.TrainName,syu.Realname FROM Tr_TrainClass tt
LEFT JOIN Tr_PlanClass tp ON tt.PlanClassId=tp.PlanClassID
LEFT JOIN sys_users syu ON syu.UserId=tt.CreateUserID
WHERE tt.ApproveStatus=2 AND tt.OldClassId=0
AND IsSignUp=1 and
tt.ClassId NOT IN
(select ClassId from Tr_TrainClassToUser where ClassId=tt.ClassId and UserId={1} and ApproveStatus=2)
and {0}
)result WHERE RowIndex BETWEEN @startLength*(@startIndex-1)+1 AND @startLength*@startIndex", where, userId);
var param = new
{
startIndex = pageIndex,
startLength = pageSize
};
var list = _dataAccess.FetchListBySql <TrainClassSignUpShow>(sql, param);
totalCount = list.Any() ? list.FirstOrDefault().totalCount : 0;
return(list.ToList());
}
public IList <ViewUserSurvey> GetUserSurveys(out int total, int userId, int status, string surveyTitle,
DateTime?sstime,
DateTime?setime,
DateTime?estime,
DateTime?eetime,
int pageIndex, int pageSize, string orderField)
{
var sqlwhere = string.Format("WHERE si.Status=1 AND si.IsDelete=0 AND si.SurveyTitle LIKE '%{0}%' AND usy.UserId=" + userId, surveyTitle.ReplaceSql());
if (status != -1)
{
sqlwhere += " AND usy.ApproveFlag=" + status;
}
if (sstime.HasValue)
{
sqlwhere += " AND si.StartTime>='" + sstime.Value.ToString("yyyy-MM-dd HH:mm:ss") + "'";
}
if (setime.HasValue)
{
sqlwhere += " AND si.StartTime<'" + setime.Value.ToString("yyyy-MM-dd HH:mm:ss") + "'";
}
if (estime.HasValue)
{
sqlwhere += " AND si.EndTime>='" + estime.Value.ToString("yyyy-MM-dd HH:mm:ss") + "'";
}
if (eetime.HasValue)
{
sqlwhere += " AND si.EndTime<'" + eetime.Value.ToString("yyyy-MM-dd HH:mm:ss") + "'";
}
var dataSql = @"SELECT usy.UserSurveyId,si.SurveyId, si.SurveyTitle,si.StartTime,si.EndTime,usy.ApproveFlag ,usy.ReportGenerated
FROM Cde_UserSurvey usy
LEFT JOIN Cde_SurveyInfo si ON usy.SurveyId=si.SurveyId
" + sqlwhere;
var countSql = @"SELECT COUNT(1)
FROM Cde_UserSurvey usy
LEFT JOIN Cde_SurveyInfo si ON usy.SurveyId=si.SurveyId
" + sqlwhere;
total =
(int)
_dataAccess.ExecuteScalar(countSql);
if (total == 0)
{
return(new List <ViewUserSurvey>());
}
if (string.IsNullOrEmpty(orderField))
{
orderField = "StartTime DESC";
}
return(_dataAccess.FetchListBySql <ViewUserSurvey>(
string.Format(DataAccessProvider.DataAccess.PagingSelect, dataSql, orderField, (pageIndex - 1) * pageSize + 1, pageIndex * pageSize),
new { surveyTitle }).ToList());
}
/// <summary>
/// 我收藏的知识
/// </summary>
/// <param name="totalCount"></param>
/// <param name="userId"></param>
/// <param name="resourcename"></param>
/// <param name="sortname"></param>
/// <param name="creater"></param>
/// <param name="starttime"></param>
/// <param name="endtime"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
public IEnumerable <KL_Resource> GetMyCollectResourceList(out int totalCount, int userId, string resourcename, string sortname, string creater, DateTime?starttime, DateTime?endtime, int pageSize, int pageIndex, string order = " MyCollectTime desc ")
{
string where = string.Format(" KL_Resource.IsDeleted = 0 and KL_Resource.Status = 1 and ( KL_Resource.IsOpen = 1 or ( KL_Resource.IsOpen = 2 and ( select count(0) from KL_KnowledgeOpenUser where KL_KnowledgeOpenUser.ResourceId = KL_Resource.ResourceId and KL_KnowledgeOpenUser.UserId = {0}) > 0 ) ) and KL_Resource.ResourceId in (select ResourceId from KL_MyFavorite where UserId = {0} and Status = 0)", userId);
if (!string.IsNullOrWhiteSpace(resourcename))
{
where += string.Format(" and KL_Resource.ResourceName like '%{0}%'", resourcename.ReplaceSql());
}
if (!string.IsNullOrWhiteSpace(sortname))
{
where += string.Format(" and KL_KnowledgeSort.SortName like '%{0}%'", sortname.ReplaceSql());
}
if (!string.IsNullOrWhiteSpace(creater))
{
where += string.Format(" and Sys_Users.Realname like '%{0}%'", creater.ReplaceSql());
}
if (starttime.HasValue)
{
where += string.Format(" and KL_Resource.CreateTime >= '{0}'", starttime.Value);
}
if (endtime.HasValue)
{
where += string.Format(" and KL_Resource.CreateTime <= '{0}'", endtime.Value);
}
totalCount = _dataAccess.GetCount <KL_Resource>(where);
if (totalCount == 0)
{
return(new List <KL_Resource>());
}
var sql = string.Format(@"select KL_Resource.ResourceId
,KL_Resource.ResourceName
,KL_Resource.Realname
,KL_Resource.CreateTime
,KL_Resource.CanDownload
,KL_KnowledgeSort.SortName
,Sys_Users.Realname as CreaterName
,(select top 1 FavTime from KL_MyFavorite where ResourceId = KL_Resource.ResourceId and Status = 0 and UserId = {0}) as MyCollectTime
from KL_Resource
left join KL_KnowledgeSort on KL_Resource.SortId = KL_KnowledgeSort.SortId
left join Sys_Users on Sys_Users.UserId = KL_Resource.UserId
where " + where, userId);
var list = _dataAccess.GetListBySql <KL_Resource>(sql, pageIndex, pageSize, string.IsNullOrWhiteSpace(order) ? "MyCollectTime desc " : order);
if (list.Count() > 0)
{
var tmp = _dataAccess.FetchListBySql <int>(string.Format("select ResourceId from KL_LoadRecord where ResourceId in ({0}) and UserId = {1} and Status = 0", list.Select(p => p.ResourceId).GetString(), userId));
foreach (var item in list)
{
item.IsMyDownload = tmp.Contains(item.ResourceId) ? 1 : 0;
}
}
return(list);
}
private static Dictionary <int, string> GetTrainAssociateWords(string word, int tenantId, int topNum)
{
var sql = @"SELECT TOP {0} TrainName,TrainId FROM Tr_TrainingInfo WHERE Status=4
AND (TrainName LIKE '" + word.ReplaceSql() + "%' OR Lookup LIKE @word+'%') AND TenantId={1}";
var list = _dataAccess.FetchListBySql <TrTrainingInfo>(string.Format(sql, topNum, tenantId), new { word });
var dict = new Dictionary <int, string>();
foreach (var info in list)
{
dict.Add(info.TrainId, info.TrainName);
}
return(dict);
}
/// <summary>
/// 根据Id查找服务中心
/// </summary>
/// <param name="deptId"></param>
/// <returns></returns>
public SysDepartment GetDeptById(int deptId)
{
var sql = string.Format(@"SELECT
sd.*,t3.UserId,t3.Realname,t3.Username,
t3.Sex,t3.Email,t3.Phone
FROM Sys_Department sd
LEFT JOIN Dept_Users t3 ON t3.DeptId=sd.DepartmentId
WHERE t3.IsDefault=1
AND t3.IsDelete=0 and sd.DepartmentId={0}", deptId);
var list = _dataAccess.FetchListBySql <SysDepartment>(sql);
return(list.FirstOrDefault());
}
/// <summary>
/// 获取场地信息
/// </summary>
/// <param name="totalCount"></param>
/// <param name="where"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public List <SysAdress> GetAdressList(out int totalCount, string where = "1=1", int pageIndex = 1, int pageSize = int.MaxValue)
{
var sql = string.Format(@"SELECT * FROM ( SELECT row_number()OVER(ORDER BY ID ASC) RowIndex,* from dbo.Sys_Adress {0}
)t where t.RowIndex between @startLength*(@startIndex-1)+1 AND @startLength*@startIndex", where);
var param = new
{
startIndex = pageIndex,
startLength = pageSize
};
var list = _dataAccess.FetchListBySql <SysAdress>(sql, param);
totalCount = list.Any() ? list.Count() : 0;
return(list.ToList());
}
public List <PostAbility> GetPostAbility(out int total, int postId, string abname, int catId, string level,
int pageIndex, int pageSize)
{
var sql = string.Format(@"SELECT pa.RecordId ,
ai.AbilityId ,
pa.PostId ,
ai.AbilityName ,
ai.AbilityLevel ,
ac.CategoryName ,
( SELECT COUNT(1)
FROM Ab_AbilityResource
WHERE Ab_AbilityResource.AbilityId = ai.AbilityId
AND ResourceType = 0 and IsDelete = 0
) AS CourseCount ,
( SELECT COUNT(1)
FROM Ab_AbilityResource
WHERE Ab_AbilityResource.AbilityId = ai.AbilityId
AND ResourceType = 1 and IsDelete = 0
) AS KnowledgeCount
FROM Ab_PostAbility pa
LEFT JOIN Sys_Posts p ON p.PostId = pa.PostId
LEFT JOIN Ab_AbilityInfo ai ON ai.AbilityId = pa.AbilityId
LEFT JOIN Ab_AbilityCategory ac ON ac.CategoryId = ai.CategoryId
WHERE pa.PostId=@postId AND pa.IsDelete=0
AND ai.IsDelete=0
AND ai.AbilityName LIKE '%{0}%'
AND (@catId=-1 OR @catId=ai.CategoryId) " +
(string.IsNullOrEmpty(level) ? "" : " AND ai.AbilityLevel=@level"), abname.ReplaceSql());
var countSql = string.Format(@"SELECT COUNT(1)
FROM Ab_PostAbility pa
LEFT JOIN Sys_Posts p ON p.PostId = pa.PostId
LEFT JOIN Ab_AbilityInfo ai ON ai.AbilityId = pa.AbilityId
LEFT JOIN Ab_AbilityCategory ac ON ac.CategoryId = ai.CategoryId
WHERE pa.PostId=@postId AND pa.IsDelete=0
AND ai.IsDelete=0
AND ai.AbilityName LIKE '%{0}%'
AND (@catId=-1 OR @catId=ai.CategoryId) " +
(string.IsNullOrEmpty(level) ? "" : " AND ai.AbilityLevel=@level"), abname.ReplaceSql());
var ps = new { postId, catId, level, abname };
total = _dataAccess.FetchListBySql <int>(countSql, ps).First();
if (total == 0)
{
return(new List <PostAbility>());
}
var list = _dataAccess.GetListBySql <PostAbility>(sql, pageIndex, pageSize, "AbilityId DESC", ps);
return(list.ToList());
}
/// <summary>
/// Gets the online users.
/// </summary>
/// <returns>The online users.</returns>
/// <param name="tenantId">Tenant identifier.</param>
public static int[] GetOnlineUsers(int tenantId)
{
var userIds =
_dataAccess.FetchListBySql <int>("SELECT UserId FROM sys_onlineuser where TenantId=" + tenantId + ";");
return(userIds.ToArray());
}
/// <summary>
/// 根据角色获取权限
/// </summary>
/// <param name="roleId"></param>
/// <returns></returns>
public IEnumerable <Sys_OperPermission> GetPermissionsByRoleId(int roleId)
{
var sql = string.Format(@"SELECT * FROM Sys_OperPermission
WHERE PermissionId IN (SELECT PermissionId FROM Sys_OperRolePermission WHERE RoleId={0})", roleId);
return(_dataAccess.FetchListBySql <Sys_OperPermission>(sql));
}
/// <summary>
/// 获取指定用户关注的所有的用户的动态（包含指定用户的动态）
/// 目前移动端只需要 说说、分享课程
/// </summary>
/// <param name="userId"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public List <IntUserActivity> GetMobileUsersActivities(int userId, int pageIndex, int pageSize = 10)
{
var activityService = new RelationshipService();
var attends = activityService.GetAttentions(userId).ToList();
var sqlwhere = string.Format(@"Int_UserActivity.IsDelete=0 AND (
Int_UserActivity.UserId = {0} OR ( Int_UserActivity.UserId <> {0}
AND Int_UserActivity.IsPrivate = 0
AND Int_UserActivity.UserId IN ({1})
)
)
And Int_UserActivity.ActionType in (1,2)
", userId, attends.GetString());
int total;
var list = GetActivities(out total, pageIndex, pageSize, sqlwhere, " ActionTime DESC");
if (list.Any(p => p.ActionType == ActionType.CourseShared))
{
var courseList = _dataAccess.FetchListBySql <SharedResourceDetail>(string.Format("select CourseId as ResourceId,CourseName as ResourceName,FrontImage as FrontImg,OutLine as ResourceDesc from Res_Course where CourseId in ({0})", list.Where(p => p.ActionType == ActionType.CourseShared).Select(p => p.ResourceId).GetString()));
foreach (var item in list.Where(p => p.ActionType == ActionType.CourseShared))
{
item.SharedResourceDetail = courseList.FirstOrDefault(p => p.ResourceId == item.ResourceId);
}
}
return(list);
}
/// <summary>
/// 同意/拒绝加入小组
/// 同步更新小组中的成员数量
/// </summary>
/// <param name="groupId"></param>
/// <param name="userId"></param>
/// <param name="status">
/// <para>0：同意</para>
/// <para>1：拒绝</para>
/// </param>
/// <returns></returns>
public bool AgreeJoinGroup(int userId, int status, IEnumerable <int> groupId)
{
if (groupId == null || groupId.Count() == 0)
{
groupId = _dataAccess.FetchListBySql <int>("select GroupId from Int_GroupMember where Status = 1 and UserId = " + userId);
}
if (groupId.Count() == 0)
{
return(false);
}
var doflag = false;
if (status == 0)
{
var list = new List <KeyValuePair <string, string> >();
list.Add(new KeyValuePair <string, string>("Status", "0"));
list.Add(new KeyValuePair <string, string>("JoinTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
doflag = _dataAccess.UpdateFields("Int_GroupMember", list, string.Format(" GroupId in ({0}) and UserId = {1} and Status = 1", groupId.GetString(), userId)) > 0;
if (doflag)
{
string sql = string.Format(@"update Int_Group set MemberCount = ( select count(0) from Int_GroupMember where groupId = Int_Group.groupId and Status = 0 and IsDelete = 0 ) where GroupId in ({0})", groupId.GetString());
_dataAccess.ExecuteSql(sql);
}
}
else
{
doflag = _dataAccess.UpdateField("Int_GroupMember", "Status", "2", string.Format(" GroupId in ({0}) and UserId = {1} and Status = 1", groupId.GetString(), userId)) > 0;
}
return(doflag);
}
/// <summary>
/// 获取培训班参与人员
/// </summary>
/// <param name="classId"></param>
/// <param name="userId"></param>
/// <param name="username"></param>
/// <param name="realname"></param>
/// <param name="postName"></param>
/// <param name="deptName"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public PagedList <SysUser> GetTrainClassToUsers(int classId, int[] userId, string username = "",
string realname = "", string postName = "", string deptName = "", int pageIndex = 1, int pageSize = int.MaxValue)
{
var dataSql = string.Format(@"SELECT u.*,d.DeptName,p.PostName,isnull(
(SELECT tu.IsJoinSurvey FROM Tr_TrainClassToUser AS tu WHERE tu.IsDelete=0 AND tu.ClassId={0} AND tu.UserId=u.UserId),0) AS IsJoinSurvey
FROM Sys_Users AS u
LEFT JOIN Sys_Department AS d ON d.DeptId=u.DeptId
LEFT JOIN Sys_Posts AS p ON p.PostId=u.PostId ", classId);
var countSql = @"select count(1) FROM Sys_Users AS u
LEFT JOIN Sys_Department AS d ON d.DeptId=u.DeptId
LEFT JOIN Sys_Posts AS p ON p.PostId=u.PostId ";
string sqlwhere = " WHERE u.Status=0 ";
if (userId.Count() > 0)
{
sqlwhere += string.Format(" and u.UserId in ({0}) ", userId.GetString());
}
if (!string.IsNullOrEmpty(username))
{
sqlwhere += string.Format(" AND u.Username LIKE '%{0}%'", username.ReplaceSql());
}
if (!string.IsNullOrEmpty(realname))
{
sqlwhere += string.Format(" AND u.Realname LIKE '%{0}%'", realname.ReplaceSql());
}
if (!string.IsNullOrEmpty(postName))
{
sqlwhere += string.Format(" AND p.PostName LIKE '%{0}%'", postName.ReplaceSql());
}
if (!string.IsNullOrEmpty(deptName))
{
sqlwhere += string.Format(" AND d.DeptName LIKE '%{0}%'", deptName.ReplaceSql());
}
var count = (int)_dataAccess.ExecuteScalar(countSql + sqlwhere);
if (count == 0)
{
return(new PagedList <SysUser>(new List <SysUser>(), pageIndex, pageSize, 0));
}
var realsql = string.Format(_dataAccess.PagingSelect, dataSql + sqlwhere, "UserId desc",
(pageIndex - 1) * pageSize + 1, pageIndex * pageSize);
var list = _dataAccess.FetchListBySql <SysUser>(realsql);
return(new PagedList <SysUser>(list, pageIndex, pageSize, count));
}
/// <summary>
/// 获取用户回复过的帖子
/// </summary>
/// <param name="total"></param>
/// <param name="userId"></param>
/// <param name="kw"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public IEnumerable <Topic> GetReplyTopic(out int total, int userId, string kw, int pageIndex, int pageSize)
{
var ids = _dataAccess.FetchListBySql <int>("SELECT TopicId FROM bbs_Reply WHERE UserId=" + userId);
var topics = _dataAccess.GetEntities <Topic>(out total, "bbs_Topic.Title LIKE '%" + kw.ReplaceSql() + "%' and bbs_Topic.IsHide=0 AND bbs_Topic.IsDelete=0 AND bbs_Topic.TopicId IN (" + ids.GetString() + ")"
, (pageIndex - 1) * pageSize + 1, pageSize, "CreateTime DESC");
return(topics);
}
/// <summary>
/// 根据用户名获得公司信息
/// </summary>
/// <param name="username"></param>
/// <returns></returns>
public CompanyModel GetCompanyByUsername(string username)
{
var sql = string.Format(@"select c.*
from userInfo u
left join company c on c.company_id = u.company_id
where u.username='******'", username);
return(_dataAccess.FetchListBySql <CompanyModel>(sql).FirstOrDefault());
}
/// <summary>
/// 分页查询租户
/// </summary>
/// <returns></returns>
public IEnumerable <SysTenant> GetTenants(out int total, string where = "1=1",
int pageIndex = 1, int pageSize = int.MaxValue, string jsRenderSortField = "TenantCode desc")
{
var sql = string.Format(@"SELECT * FROM (
SELECT row_number()OVER(ORDER BY {1}) number,count(1)OVER(PARTITION BY null) totalCount, *
FROM Sys_Tenant
WHERE IsDelete=0 and {0}) t WHERE number BETWEEN @startLength*(@startIndex-1)+1 AND @startLength*@startIndex
", where, jsRenderSortField);
var param = new
{
startIndex = pageIndex,
startLength = pageSize
};
var list = _dataAccess.FetchListBySql <SysTenant>(sql, param);
total = list.Any() ? list.FirstOrDefault().totalCount : 0;
return(list);
}
/// <summary>
/// 获取收件箱 and 系统收件箱未读消息数量
/// </summary>
/// <param name="recUserId"></param>
/// <returns></returns>
public List <int> GetAllNotReadSysRecMessageCount(int recUserId)
{
var data1 = _dataAccess.FetchListBySql <dynamic>(string.Format(@"select RECORDID
from Sys_UserMessage
where Sys_UserMessage.SendUserId > 0 and Sys_UserMessage.RecStatus = 0
and Sys_UserMessage.RecUserId = {0}", recUserId)).ToList();
var data2 = _dataAccess.FetchListBySql <dynamic>(string.Format(@"select RECORDID
from Sys_UserMessage
where Sys_UserMessage.SendUserId = 0 and Sys_UserMessage.RecStatus = 0
and Sys_UserMessage.RecUserId = {0}", recUserId)).ToList();
var list = new List <int>();
list.Add(data1.Count);
list.Add(data2.Count);
return(list);
}
public bool IsExsitName(int tenantId, int abilityId, string abilityName, string abilityLevel, int categoryId)
{
string sql = "select Count(0) from Ab_AbilityInfo where IsDelete = 0 AND TenantId = @tenantId AND AbilityName = @abilityName and AbilityLevel = @abilityLevel and CategoryId = @categoryId";
if (abilityId > 0)
{
sql += " and AbilityId <> " + abilityId;
}
int count = _dataAccess.FetchListBySql <int>(sql, new { abilityName, abilityLevel, tenantId, categoryId }).FirstOrDefault();
return(count == 0);
}
public System.Collections.Generic.List <Models.Cde.Cde_IndexQuestion> GetQuestions(out int total, int tenantId, string qtitle, int sortId, int pageIndex, int pageSize, int[] excludes = null)
{
string sqlwhere = String.Format("Cde_IndexQuestion.IsDelete=0 AND Cde_IndexQuestion.QuestionName LIKE'%{0}%' AND Cde_IndexQuestion.TenantId=@tenantId", qtitle.ReplaceSql());
if (sortId != 0)
{
var sorts = _dataAccess.FetchListBySql <int>("SELECT SortId FROM Cde_IndexSort WHERE ParentId=" + sortId);
var ss = sorts.ToList();
ss.Add(sortId);
sqlwhere += " AND (Cde_IndexQuestion.SortId IN (" + ss.GetString() + ") OR Cde_IndexQuestion.SecondSortId IN (" + ss.GetString() + ")) ";
}
sqlwhere += " AND Cde_IndexQuestion.QuestionId NOT IN (" + excludes.GetString() + ")";
var list = _dataAccess.GetEntities <Models.Cde.Cde_IndexQuestion>(out total, sqlwhere,
(pageIndex - 1) * pageSize + 1, pageSize, "QuestionId DESC", new { qtitle, tenantId }).ToList();
return(list);
}
/// <summary>
/// 查询线下汇款
/// </summary>
/// <returns></returns>
public IEnumerable <Money_LineRemit> GetLineRemitList(out int totalCount, string where = "1=1",
int pageIndex = 1, int pageSize = int.MaxValue, string Sort = " ID DESC")
{
var sql = string.Format(@"select * from (
SELECT row_number()OVER(ORDER BY {0}) number,count(1)OVER(PARTITION BY null) totalCount,ml.*,sd.DeptName,sd.DeptCode
FROM Money_LineRemit ml
LEFT JOIN Sys_Department sd ON ml.DeptId=sd.DepartmentId
where {1}
)t WHERE number BETWEEN @startLength*(@startIndex-1)+1 AND @startLength*@startIndex", Sort, where);
var param = new
{
startIndex = pageIndex,
startLength = pageSize
};
var list = _dataAccess.FetchListBySql <Money_LineRemit>(sql, param);
totalCount = list.Any() ? list.FirstOrDefault().totalCount : 0;
return(list);
}
/// <summary>
/// 分页获取用户
/// </summary>
public IEnumerable <SysUser> GetAllUsers(out int totalCount, string whereStr = "Sys_Users.Status=0 and Sys_Users.IsDelete=0 and isnull(Sys_Users.IsTeacher,0)=0 ", int pageIndex = 1, int pageSize = int.MaxValue, string Sort = "UserId DESC")
{
var sql = string.Format(@"SELECT * FROM (
SELECT row_number()OVER(ORDER BY {1}) number,count(1)OVER(PARTITION BY null) totalCount, *,
roleStr=(SELECT RoleName +',' FROM Sys_Roles WHERE IsDelete=0 and
RoleId IN (SELECT RoleId FROM Sys_RoleUser WHERE UserId=Sys_Users.UserId)
FOR XML path(''))
FROM Sys_Users
WHERE 1=1 and {0}) t WHERE number BETWEEN @startLength*(@startIndex-1)+1 AND @startLength*@startIndex", whereStr, Sort);
var param = new
{
startIndex = pageIndex,
startLength = pageSize
};
var list = _dataAccess.FetchListBySql <SysUser>(sql, param);
totalCount = list.Count() > 0 ? list.FirstOrDefault().totalCount : 0;
return(list);
}
public int GetApplyPromotionCount(int promotionId, int userId, DateTime?startTime, DateTime?endTime)
{
return(_dataAccess.FetchListBySql <int>(string.Format(@"select count(0) from Ab_PromotionApply where IsDelete = 0 and PromotionId = {0} and UserId = {1} {2} {3}"
, promotionId
, userId
, startTime.HasValue ? string.Format("and ApplyTime >= '{0}'", startTime.Value.ToString("yyyy-MM-dd HH:mm:ss")) : ""
, endTime.HasValue ? string.Format("and ApplyTime <= '{0}'", endTime.Value.ToString("yyyy-MM-dd HH:mm:ss")) : ""
)).FirstOrDefault());
}
/// <summary>
/// 学习指派
/// </summary>
/// <param name="designateUser">指派者ID</param>
/// <param name="userId">被指派者id</param>
/// <returns></returns>
public void GetDesignateDetail(int designateUser, int userId)
{
var userCourseListSql = string.Format(@"SELECT distinct u.UserId,u.Username,u.Realname,u.Sex,u.UserCode,dp.LevelPath,p.PostName,d.CourseId FROM Len_DesignateCourse d LEFT JOIN Sys_Users u
ON u.UserId=d.UserId
LEFT JOIN Sys_Department dp ON u.DeptId=dp.DeptId
LEFT JOIN Sys_Posts p ON u.PostId=p.PostId
WHERE DesignateUser={0} AND d.InfoType IN(0) and u.UserId={1}", designateUser, userId);
var userCourseList = _dataAccess.FetchListBySql <DesignateUserList>(userCourseListSql);
var courseIds = userCourseList.Select(p => p.CourseId).Distinct().GetString();
string[] courseId = courseIds.Split(',');
//更新学习记录
if (userCourseList.Any())
{
// var sql = string.Format(@"DELETE FROM dbo.Len_LearningRecord WHERE UserId = {0} AND CourseId IN ({1})
//AND LearnPath=0 AND SourceId=0 ", userId, courseIds);
// var DeletelearnRecords = _dataAccess.ExecuteSql(sql);
List <LenLearningRecord> learnRecords = new List <LenLearningRecord>();
foreach (var item in courseId)
{
var learnRecord = _dataAccess.GetList <LenLearningRecord>(string.Format(@"WHERE UserId = ({0}) AND CourseId = ({1})
AND LearnPath=0 AND SourceId=0 ", userId, Convert.ToInt32(item)));
if (learnRecord.Count > 0)
{
_dataAccess.UpdateFieldCalc("dbo.Len_LearningRecord", "LearnProcess", "2", "where RecordId=" + learnRecord[0].RecordId);
}
else
{
LenLearningRecord lr = new LenLearningRecord();
lr.CourseId = Convert.ToInt32(item);
lr.UserId = userId;
lr.LearnProcess = 2;
lr.LearnPath = 0;
lr.SourceId = 0;
lr.StartTime = DateTime.Now;
lr.EndTime = DateTime.Now;
lr.LastLearnTime = DateTime.Now;
_dataAccess.AddEntity(lr);
}
}
}
}
/// <summary>
/// 讲师预约
/// </summary>
/// <param name="totalCount"></param>
/// <param name="where"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public List <Tr_TrainClassToCourse> GetTeacherReservation(out int totalCount, string where = "1-1", int pageIndex = 1, int pageSize = int.MaxValue)
{
var sql = string.Format(@"SELECT * FROM (
SELECT row_number()OVER(ORDER BY tc.ID ) RowIndex, tc.*,u.Realname,tpc.TrainName,d.DeptName,tto.OrderStatu Statu from dbo.Tr_TrainClassToCourse tc
left join dbo.Tr_TrainClass tp on tp.ClassId=tc.ClassId
left join dbo.Sys_Users u on u.UserId=tc.CreateUserID
left join Tr_PlanClass tpc on tpc.PlanClassID=tp.PlanClassId
left join dbo.Sys_Department d on d.DeptId=u.DeptId
left join Tr_TrainTeacherOrder tto ON tto.ClassId=tc.ID
where tp.ApproveStatus=2 and tp.IsDelete=0 and tc.IsDelete=0 {0}
)result WHERE RowIndex BETWEEN @startLength*(@startIndex-1)+1 AND @startLength*@startIndex", where);
var param = new
{
startIndex = pageIndex,
startLength = pageSize
};
var list = _dataAccess.FetchListBySql <Tr_TrainClassToCourse>(sql, param);
totalCount = list.Any() ? list.Count() : 0;
return(list.ToList());
}