/// <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]));
}
