/// <summary> /// 获取已参与的项目 /// </summary> /// <param name="userId"></param> /// <returns></returns> public List <M_Project> GetJoinedSurvey(string userId) { var sql = $@"select ID,ProjectNumber,ProjectName,Integral,ServeyLenth,JoinState=(select top 1 state from ProjectJoin where ProjectId= Project.ID and MemberId={userId} ) from Project where ID<>23573 and exists(select * from ProjectJoin where ProjectJoin.ProjectId = Project.ID and ProjectJoin.MemberId = {userId}) "; return(CommonTool.DataTableConvertToList <M_Project>(DbHelperSQL.Query(sql).Tables[0])); }
/// <summary> /// 获取参与项目 /// </summary> /// <param name="sql"></param> /// <returns></returns> public List <M_Project> GetRunningSurvey(string userId) { var sql = string.Format(@"select row=row_number() over(order by ID), ID,ProjectNumber,ProjectName,Integral,ServeyLenth from Project where ID<>23573 and ProjectState=0 and exists (select * from ProjectSample where ProjectSample.ProjectID=Project.ID and ProjectSample.MemberID={0}) and (not exists(select * from ProjectJoin where ProjectJoin.ProjectId = Project.ID and ProjectJoin.MemberId = {0}) or exists(select * from ProjectJoin where ProjectJoin.ProjectId = Project.ID and ProjectJoin.MemberId = {0} and ProjectJoin.State = 0))", userId); return(CommonTool.DataTableConvertToList <M_Project>(DbHelperSQL.Query(sql).Tables[0])); }
/// <summary> /// 分页已参与项目 /// </summary> /// <param name="userId"></param> /// <returns></returns> public List <M_Project> GetJoinedSurvey(string userId, int page, int pageSize, out int count) { page = page > 0 ? page : 1; var sql = $@"select row=row_number()over(order by ID), ID,ProjectNumber,ProjectName,Integral,ServeyLenth,JoinState=(select top 1 state from ProjectJoin where ProjectId= Project.ID and MemberId={userId} ) from Project where ID<>23573 and exists(select * from ProjectJoin where ProjectJoin.ProjectId = Project.ID and ProjectJoin.MemberId = {userId}) "; var lastSql = "select * from (" + sql + $") as #temp where row between {(page - 1) * pageSize + 1} and {page * pageSize}"; var sqlCount = $@"select count(1) from Project where ID<>23573 and exists(select * from ProjectJoin where ProjectJoin.ProjectId = Project.ID and ProjectJoin.MemberId = {userId}) "; count = (int)DbHelperSQL.GetSingle(sqlCount); return(CommonTool.DataTableConvertToList <M_Project>(DbHelperSQL.Query(lastSql).Tables[0])); }
/// <summary> /// 获取分页参与项目 /// </summary> /// <param name="sql"></param> /// <returns></returns> public List <M_Project> GetRunningSurvey(string userId, int page, int pageSize, out int count) { page = page > 0 ? page : 1; var sql = string.Format(@"select row=row_number() over(order by ID), ID,ProjectNumber,ProjectName,Integral,ServeyLenth from Project where ID<>23573 and ProjectState=0 and exists (select * from ProjectSample where ProjectSample.ProjectID=Project.ID and ProjectSample.MemberID={0}) and (not exists(select * from ProjectJoin where ProjectJoin.ProjectId = Project.ID and ProjectJoin.MemberId = {0}) or exists(select * from ProjectJoin where ProjectJoin.ProjectId = Project.ID and ProjectJoin.MemberId = {0} and ProjectJoin.State = 0))", userId); var lastSql = "select * from (" + sql + $") as #temp where row between {(page - 1) * pageSize + 1} and {page * pageSize}"; var sqlCount = string.Format(@"select count(1) from Project where ID<>23573 and ProjectState=0 and exists (select * from ProjectSample where ProjectSample.ProjectID=Project.ID and ProjectSample.MemberID={0}) and (not exists(select * from ProjectJoin where ProjectJoin.ProjectId = Project.ID and ProjectJoin.MemberId = {0}) or exists(select * from ProjectJoin where ProjectJoin.ProjectId = Project.ID and ProjectJoin.MemberId = {0} and ProjectJoin.State = 0))", userId); count = (int)DbHelperSQL.GetSingle(sqlCount); return(CommonTool.DataTableConvertToList <M_Project>(DbHelperSQL.Query(lastSql).Tables[0])); }