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