Ejemplo n.º 1
0
        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());
        }
Ejemplo n.º 2
0
        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());
        }
Ejemplo n.º 3
0
        /// <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));
        }
Ejemplo n.º 4
0
        /// <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);
            }
        }
Ejemplo n.º 5
0
        /// <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());
        }
Ejemplo n.º 6
0
        /// <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));
        }
Ejemplo n.º 7
0
        /// <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());
        }
Ejemplo n.º 8
0
        /// <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());
        }
Ejemplo n.º 9
0
        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());
        }
Ejemplo n.º 10
0
        /// <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);
        }
Ejemplo n.º 11
0
        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);
        }
Ejemplo n.º 12
0
        /// <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());
        }
Ejemplo n.º 13
0
        /// <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());
        }
Ejemplo n.º 14
0
        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());
        }
Ejemplo n.º 15
0
        /// <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());
        }
Ejemplo n.º 16
0
        /// <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));
        }
Ejemplo n.º 17
0
        /// <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);
        }
Ejemplo n.º 18
0
        /// <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);
        }
Ejemplo n.º 19
0
        /// <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));
        }
Ejemplo n.º 20
0
        /// <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);
        }
Ejemplo n.º 21
0
        /// <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());
        }
Ejemplo n.º 22
0
        /// <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);
        }
Ejemplo n.º 23
0
        /// <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);
        }
Ejemplo n.º 24
0
        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);
        }
Ejemplo n.º 25
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);
        }
Ejemplo n.º 26
0
        /// <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);
        }
Ejemplo n.º 27
0
        /// <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);
        }
Ejemplo n.º 28
0
 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());
 }
Ejemplo n.º 29
0
        /// <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);
                    }
                }
            }
        }
Ejemplo n.º 30
0
        /// <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());
        }