public async Task <List <ProjectForPanel> > ProjectList(SipUser CurrentUser, int PageSectionId)
{
//string ProjectConditionSQLFrom = " DECLARE @LanguageId int; " +
// " SELECT @LanguageId = IntPreference " +
// " FROM UserPreferences " +
// " WHERE USerId = '" + CurrentUser.Id + "' " +
// " AND UserPreferences.PreferenceTypeId = 1; " +
// " SELECT " +
// " Projects.ProjectID " +
// " , Projects.ProjectTypeID " +
// " , ISNULL(UserLanguage.Name, ISNULL(DefaultLanguage.Name, 'No name for this Project')) Name " +
// " , ISNULL(UserLanguage.Description, ISNULL(DefaultLanguage.Description, 'No description for this Project')) Description " +
// " , ISNULL(UserLanguage.MenuName, ISNULL(DefaultLanguage.MenuName, 'No menu name for this Project')) MenuName " +
// " , ISNULL(UserLanguage.MouseOver, ISNULL(DefaultLanguage.MouseOver, 'No mouse over for this Project')) MouseOver " +
// " , ISNULL(UserTypeLanguage.Name, ISNULL(DefaultTypeLanguage.Name, 'No name for this Project type')) ProjectTypeName " +
// " , ISNULL(UserStatusName.Customization, StatusName.Name) StatusName " +
// " , Creator.FirstName + ' ' + Creator.LastName CreatorName " +
// " , Creator.PersonID CreatorID " +
// " , Projects.CreatedDate " +
// " , Modifier.FirstName + ' ' + Modifier.LastName ModifierName " +
// " , Modifier.PersonId ModifierID " +
// " , Projects.ModifiedDate " +
// " , CONCAT('controlID', Projects.ProjectId) ControlId " +
// " FROM Projects JOIN Statuses " +
// " ON Statuses.StatusId = Projects.StatusID " +
// " JOIN ProjectTypes " +
// " ON Projects.ProjectTypeId = ProjectTypes.ProjectTypeID " +
// " LEFT JOIN(SELECT ProjectId, Name, Description, MenuName, MouseOver FROM ProjectLanguages WHERE LanguageId = @LanguageID) UserLanguage " +
// " ON UserLanguage.ProjectID = Projects.ProjectID " +
// " LEFT JOIN(SELECT ProjectId, Name, Description, MenuName, MouseOver FROM ProjectLanguages JOIN Settings ON ProjectLanguages.LanguageId = Settings.IntValue WHERE Settings.SettingId = 1) DefaultLanguage " +
// " ON DefaultLanguage.ProjectId = Projects.ProjectID " +
// " LEFT JOIN(SELECT ProjectTypeId, Name FROM ProjectTypeLanguages WHERE LanguageId = @LanguageID) UserTypeLanguage " +
// " ON UserTypeLanguage.ProjectTypeID = Projects.ProjectTypeID " +
// "LEFT JOIN(SELECT ProjectTypeId, Name FROM ProjectTypeLanguages JOIN Settings ON ProjectTypeLanguages.LanguageId = Settings.IntValue WHERE Settings.SettingId = 1) DefaultTypeLanguage " +
// " ON DefaultTypeLanguage.ProjectTypeID = Projects.ProjectTypeID " +
// " JOIN UITermLanguages StatusName " +
// " ON Statuses.NameTermId = StatusName.UITermId " +
// " LEFT JOIN(SELECT* FROM UITermLanguageCustomizations WHERE UITermLanguageCustomizations.LanguageId = @LanguageID) UserStatusName " +
// " ON Statuses.NameTermId = UserStatusName.UITermId" +
// " JOIN Persons Creator " +
// " ON Creator.UserId = Projects.CreatorID " +
// " JOIN Persons Modifier " +
// " ON Modifier.UserId = Projects.ModifierID ";
string ProjectConditionSQLFrom = " ";
string ProjectConditionSQLWhere = " WHERE StatusName.LanguageId = @LanguageId ";
string ProjectConditionSQLContains = "";
var ProjectConditions = await _projectProvider.PanelCondition(PageSectionId);
foreach (var ProjectCondition in ProjectConditions)
{
switch (ProjectCondition.PageSectionProjectConditionTypeId)
{
case 1: // Parent Project
ProjectConditionSQLWhere += " AND Projects.ParentProjectId = " + ProjectCondition.PageSectionProjectConditionInt;
break;
case 2: // My Project
ProjectConditionSQLWhere += " AND Projects.ProjectId IN (SELECT AspNetRoles.ProjectId FROM AspNetRoles JOIN AspNetUserRoles ON AspNetRoles.Id JOIN AspNetUserRoles.RoleId WHERE AspNetUserRoles.UserId = '" + CurrentUser.Id + "' ) ";
break;
case 3: // Contains
ProjectConditionSQLFrom += " JOIN ( SELECT ProjectId FROM ProjectLanguages WHERE LanguageId = @LanguageID AND CONTAINS (ProjectLanguages.FullText, '" + ProjectCondition.PageSectionProjectConditionString + "' )) ProjectTable" + ProjectCondition.PageSectionProjectConditionId + " ON ProjectTable" + ProjectCondition.PageSectionProjectConditionId + ".ProjectId = Projects.ProjectID ";
break;
case 4: // Project type
ProjectConditionSQLWhere += " AND Projects.ProjectTypeId = " + ProjectCondition.PageSectionProjectConditionInt;
break;
}
}
var ProjectList = await _projectProvider.Panel(CurrentUser.Id, ProjectConditionSQLFrom, ProjectConditionSQLWhere, ProjectConditionSQLContains);
return(ProjectList);
}
