// public static List<CodeItem> SearchAsCodeItem( string keyword, int startingPageNbr, int pageSize, ref int totalRows )
// {
// List<ThisEntity> list = Search( keyword, startingPageNbr, pageSize, ref totalRows );
// List<CodeItem> codes = new List<CodeItem>();
// foreach (ThisEntity item in list)
// {
// codes.Add(new CodeItem() {
// Id = item.Id,
// Name = item.Name,
// Description = item.Description
// });
// }
// return codes;
//}
public static List <object> Autocomplete(string keyword, int maxTerms = 25)
{
string where = "";
int totalRows = 0;
//SetKeywordFilter( keyword, true, ref where );
string keywords = ServiceHelper.HandleApostrophes(keyword);
if (keywords.IndexOf("%") == -1)
{
keywords = "%" + keywords.Trim() + "%";
}
where = string.Format(" (base.name like '{0}') ", keywords);
if (UtilityManager.GetAppKeyValue("usingElasticAssessmentSearch", false))
{
return(ElasticServices.AssessmentAutoComplete(keyword, maxTerms, ref totalRows));
}
else
{
SetKeywordFilter(keyword, true, ref where);
return(EntityMgr.Autocomplete(where, 1, maxTerms, ref totalRows));
}
}
//
private static void SetKeywordFilter(string keywords, bool isBasic, ref string where)
{
if (string.IsNullOrWhiteSpace(keywords))
{
return;
}
//trim trailing (org)
if (keywords.IndexOf("('") > 0)
{
keywords = keywords.Substring(0, keywords.IndexOf("('"));
}
//OR base.Description like '{0}'
string text = " (base.name like '{0}' OR base.SubjectWebpage like '{0}' OR base.Organization like '{0}' ) ";
bool isCustomSearch = false;
//use Entity.SearchIndex for all
//string indexFilter = " OR (base.Id in (SELECT c.id FROM [dbo].[Entity.SearchIndex] a inner join Entity b on a.EntityId = b.Id inner join TransferValue c on b.EntityUid = c.RowId where (b.EntityTypeId = 3 AND ( a.TextValue like '{0}' OR a.[CodedNotation] like '{0}' ) ))) ";
//for ctid, needs a valid ctid or guid
if (keywords.IndexOf("ce-") > -1 && keywords.Length == 39)
{
text = " ( CTID = '{0}' ) ";
isCustomSearch = true;
}
else if (ServiceHelper.IsValidGuid(keywords))
{
text = " ( CTID = 'ce-{0}' ) ";
isCustomSearch = true;
}
else if (keywords.ToLower() == "[hascredentialregistryid]")
{
text = " ( len(Isnull(CredentialRegistryId,'') ) = 36 ) ";
isCustomSearch = true;
}
string AND = "";
if (where.Length > 0)
{
AND = " AND ";
}
keywords = ServiceHelper.HandleApostrophes(keywords);
if (keywords.IndexOf("%") == -1 && !isCustomSearch)
{
keywords = SearchServices.SearchifyWord(keywords);
}
//skip url OR base.Url like '{0}'
if (isBasic || isCustomSearch)
{
where = where + AND + string.Format(" ( " + text + " ) ", keywords);
}
else
{
where = where + AND + string.Format(" ( " + text + " ) ", keywords);
}
}
/// <summary>
/// Credential autocomplete
/// Needs to check authorization level for credential
/// </summary>
/// <param name="keyword"></param>
/// <param name="maxTerms"></param>
/// <returns></returns>
public static List <object> Autocomplete(string keywords, int maxTerms = 25)
{
int userId = 0;
string where = " base.EntityStateId = 3 ";
string AND = "";
int pTotalRows = 0;
AppUser user = AccountServices.GetCurrentUser();
if (user != null && user.Id > 0)
{
userId = user.Id;
}
if (UtilityManager.GetAppKeyValue("usingElasticCredentialAutocomplete", false))
{
return(ElasticHelper.CredentialAutoComplete(keywords, maxTerms, ref pTotalRows));
}
else
{
bool usingLinqAutocomplete = true;
if (usingLinqAutocomplete)
{
return(CredentialManager.AutocompleteInternal(keywords, 1, maxTerms, ref pTotalRows));
}
else
{
string text = " (base.name like '{0}' OR base.AlternateName like '{0}' OR OwningOrganization like '{0}' ) ";
//SetKeywordFilter( keywords, true, ref where );
keywords = ServiceHelper.HandleApostrophes(keywords);
if (keywords.IndexOf("%") == -1)
{
keywords = SearchServices.SearchifyWord(keywords);
}
if (where.Length > 0)
{
AND = " AND ";
}
where = where + AND + string.Format(" ( " + text + " ) ", keywords);
return(CredentialManager.AutocompleteDB(where, 1, maxTerms, ref pTotalRows));
}
// return new List<string>();
}
}
private static void SetKeywordFilter(string keywords, ref string where)
{
if (string.IsNullOrWhiteSpace(keywords))
{
return;
}
string text = " (FirstName like '{0}' OR LastName like '{0}' OR Email like '{0}' ) ";
string AND = "";
if (where.Length > 0)
{
AND = " AND ";
}
//
keywords = ServiceHelper.HandleApostrophes(keywords);
if (keywords.IndexOf("%") == -1)
{
keywords = "%" + keywords.Trim() + "%";
}
where = where + AND + string.Format(" ( " + text + " ) ", keywords);
}
private static void SetKeywordFilter(string keywords, bool isBasic, ref string where)
{
if (string.IsNullOrWhiteSpace(keywords) || string.IsNullOrWhiteSpace(keywords.Trim()))
{
return;
}
//trim trailing (org)
if (keywords.IndexOf("('") > 0)
{
keywords = keywords.Substring(0, keywords.IndexOf("('"));
}
//OR base.Description like '{0}'
var text = " (base.name like '{0}' OR base.Organization like '{0}' OR base.Description like '{0}' ) ";
bool isCustomSearch = false;
//for ctid, needs a valid ctid or guid
if (keywords.IndexOf("ce-") > -1 && keywords.Length == 39)
{
text = " ( CTID = '{0}' ) ";
isCustomSearch = true;
}
else if (ServiceHelper.IsValidGuid(keywords))
{
text = " ( CTID = 'ce-{0}' ) ";
isCustomSearch = true;
}
else if (ServiceHelper.IsInteger(keywords))
{
text = " ( Id = '{0}' ) ";
isCustomSearch = true;
}
else if (keywords.ToLower().IndexOf("orgid:") == 0)
{
string[] parts = keywords.Split(':');
if (parts.Count() > 1)
{
if (ServiceHelper.IsInteger(parts[1]))
{
text = string.Format(" ( OwningOrganizationId={0} ) ", parts[1].Trim());
isCustomSearch = true;
}
}
}
string AND = "";
if (where.Length > 0)
{
AND = " AND ";
}
keywords = ServiceHelper.HandleApostrophes(keywords);
if (keywords.IndexOf("%") == -1 && !isCustomSearch)
{
keywords = SearchServices.SearchifyWord(keywords);
}
//skip url OR base.Url like '{0}'
if (isBasic || isCustomSearch)
{
//if ( !includingFrameworkItemsInKeywordSearch )
// where = where + AND + string.Format( " ( " + text + " ) ", keywords );
//else
where = where + AND + string.Format(" ( " + text + " ) ", keywords);
}
else
{
//if ( using_EntityIndexSearch )
// where = where + AND + string.Format( " ( " + text + indexFilter + " ) ", keywords );
//else
where = where + AND + string.Format(" ( " + text + " ) ", keywords);
}
}
private static void SetKeywordFilter(string keywords, bool isBasic, ref string where)
{
if (string.IsNullOrWhiteSpace(keywords))
{
return;
}
//OR base.Description like '{0}'
string text = " (base.name like '{0}' OR base.SubjectWebpage like '{0}' OR base.id in ( select EntityBaseId from Organization_AlternatesNames where TextValue like '{0}') ) ";
string orgDepts = "( base.Id in (SELECT o.Id FROM dbo.Entity e INNER JOIN dbo.[Entity.AgentRelationship] ear ON e.Id = ear.EntityId INNER JOIN dbo.Organization o ON e.EntityUid = o.RowId WHERE ear.RelationshipTypeId = {0} AND o.StatusId < 4) )";
bool isCustomSearch = false;
//use Entity.SearchIndex for all
string indexFilter = " OR (base.Id in (SELECT c.id FROM [dbo].[Entity.SearchIndex] a inner join Entity b on a.EntityId = b.Id inner join Organization c on b.EntityUid = c.RowId where (b.EntityTypeId = 2 AND ( a.TextValue like '{0}' OR a.[CodedNotation] like '{0}' ) ))) ";
//for ctid, needs a valid ctid or guid
if (keywords.IndexOf("ce-") > -1 && keywords.Length == 39)
{
text = " ( CTID = '{0}' ) ";
isCustomSearch = true;
}
else if (ServiceHelper.IsValidGuid(keywords))
{
text = " ( CTID = 'ce-{0}' ) ";
isCustomSearch = true;
}
else if (keywords.ToLower() == "[hascredentialregistryid]")
{
text = " ( len(Isnull(CredentialRegistryId,'') ) = 36 ) ";
isCustomSearch = true;
}
else if (keywords.ToLower() == "has subsidiary")
{
text = string.Format(orgDepts, 21);
isCustomSearch = true;
}
else if (keywords.ToLower() == "has department")
{
text = string.Format(orgDepts, 20);
isCustomSearch = true;
}
string AND = "";
if (where.Length > 0)
{
AND = " AND ";
}
keywords = ServiceHelper.HandleApostrophes(keywords);
if (keywords.IndexOf("%") == -1 && !isCustomSearch)
{
keywords = SearchServices.SearchifyWord(keywords);
//keywords = "%" + keywords.Trim() + "%";
//keywords = keywords.Replace( "&", "%" ).Replace( " and ", "%" ).Replace( " in ", "%" ).Replace( " of ", "%" );
//keywords = keywords.Replace( " - ", "%" );
//keywords = keywords.Replace( " % ", "%" );
}
//same for now, but will chg
if (isBasic || isCustomSearch)
{
where = where + AND + string.Format(" ( " + text + " ) ", keywords);
}
else
{
where = where + AND + string.Format(" ( " + text + indexFilter + " ) ", keywords);
}
}
private static void SetKeywordFilter(string keywords, bool isBasic, ref string where)
{
if (string.IsNullOrWhiteSpace(keywords))
{
return;
}
//trim trailing (org)
if (keywords.IndexOf("('") > 0)
{
keywords = keywords.Substring(0, keywords.IndexOf("('"));
}
//OR base.Description like '{0}'
string text = " (base.name like '{0}' OR base.SubjectWebpage like '{0}' OR base.Organization like '{0}' ) ";
bool isCustomSearch = false;
//use Entity.SearchIndex for all
string indexFilter = " OR (base.Id in (SELECT c.id FROM [dbo].[Entity.SearchIndex] a inner join Entity b on a.EntityId = b.Id inner join LearningOpportunity c on b.EntityUid = c.RowId where (b.EntityTypeId = 3 AND ( a.TextValue like '{0}' OR a.[CodedNotation] like '{0}' ) ))) ";
//for ctid, needs a valid ctid or guid
if (keywords.IndexOf("ce-") > -1 && keywords.Length == 39)
{
text = " ( CTID = '{0}' ) ";
isCustomSearch = true;
}
else if (ServiceHelper.IsValidGuid(keywords))
{
text = " ( CTID = 'ce-{0}' ) ";
isCustomSearch = true;
}
else if (keywords.ToLower() == "[hascredentialregistryid]")
{
text = " ( len(Isnull(CredentialRegistryId,'') ) = 36 ) ";
isCustomSearch = true;
}
string subjectsEtc = " OR (base.Id in (SELECT c.id FROM [dbo].[Entity.Reference] a inner join Entity b on a.EntityId = b.Id inner join LearningOpportunity c on b.EntityUid = c.RowId where [CategoryId] in (34 ,35) and a.TextValue like '{0}' )) ";
string frameworkItems = " OR (RowId in (SELECT EntityUid FROM [dbo].[Entity.FrameworkItemSummary] a where CategoryId= 23 and entityTypeId = 7 AND a.title like '{0}' ) ) ";
//string otherFrameworkItems = " OR (RowId in (SELECT EntityUid FROM [dbo].[Entity_Reference_Summary] a where a.TextValue like '{0}' ) ) ";
string competencies = " OR ( base.Id in (SELECT LearningOpportunityId FROM [dbo].LearningOpportunity_Competency_Summary where [Description] like '{0}' ) ) ";
string AND = "";
if (where.Length > 0)
{
AND = " AND ";
}
keywords = ServiceHelper.HandleApostrophes(keywords);
if (keywords.IndexOf("%") == -1 && !isCustomSearch)
{
keywords = SearchServices.SearchifyWord(keywords);
}
//skip url OR base.Url like '{0}'
if (isBasic || isCustomSearch)
{
where = where + AND + string.Format(" ( " + text + " ) ", keywords);
}
else
{
where = where + AND + string.Format(" ( " + text + indexFilter + " ) ", keywords);
}
//where = where + AND + string.Format( " ( " + text + subjectsEtc + frameworkItems + otherFrameworkItems + competencies + " ) ", keywords );
}
private static void SetKeywordFilter(string keywords, bool isBasic, ref string where)
{
if (string.IsNullOrWhiteSpace(keywords) || string.IsNullOrWhiteSpace(keywords.Trim()))
{
return;
}
//trim trailing (org)
if (keywords.LastIndexOf("(") > 0)
{
keywords = keywords.Substring(0, keywords.LastIndexOf("("));
}
//OR CreatorOrgs like '{0}'
bool isCustomSearch = false;
//OR base.Description like '{0}' OR base.SubjectWebpage like '{0}'
string text = " (base.name like '{0}' OR base.AlternateName like '{0}' OR OwningOrganization like '{0}' ) ";
//for ctid, needs a valid ctid or guid
if (keywords.IndexOf("ce-") > -1 && keywords.Length == 39)
{
text = " ( CTID = '{0}' ) ";
isCustomSearch = true;
}
else if (keywords.IndexOf("in (") > -1)
{
text = " base.Id " + keywords;
isCustomSearch = true;
}
else if (ServiceHelper.IsValidGuid(keywords))
{
text = " ( CTID = 'ce-{0}' ) ";
isCustomSearch = true;
}
else if (ServiceHelper.IsInteger(keywords))
{
text = " ( Id = '{0}' ) ";
isCustomSearch = true;
}
else if (keywords.ToLower() == "[hascredentialregistryid]")
{
text = " ( len(Isnull(CredentialRegistryId,'') ) = 36 ) ";
isCustomSearch = true;
}
//use Entity.SearchIndex for all
string indexFilter = " OR (base.Id in (SELECT c.id FROM [dbo].[Entity.SearchIndex] a inner join Entity b on a.EntityId = b.Id inner join Credential c on b.EntityUid = c.RowId where (b.EntityTypeId = 1 AND ( a.TextValue like '{0}' OR a.[CodedNotation] like '{0}' ) ))) ";
//removed 10,11 as part of the frameworkItemSummary
//string keywordsFilter = " OR (base.Id in (SELECT c.id FROM [dbo].[Entity.Reference] a inner join Entity b on a.EntityId = b.Id inner join Credential c on b.EntityUid = c.RowId where [CategoryId] = 35 and a.TextValue like '{0}' )) ";
// string subjects = " OR (base.EntityUid in (SELECT EntityUid FROM [Entity_Subjects] a where EntityTypeId = 1 AND a.Subject like '{0}' )) ";
//string frameworkItems = " OR (EntityUid in (SELECT EntityUid FROM [dbo].[Entity.FrameworkItemSummary_ForCredentials] a where a.title like '{0}' ) ) ";
// string otherFrameworkItems = " OR (EntityUid in (SELECT EntityUid FROM [dbo].[Entity_Reference_Summary] a where a.TextValue like '{0}' ) ) ";
string AND = "";
if (where.Length > 0)
{
AND = " AND ";
}
//
keywords = ServiceHelper.HandleApostrophes(keywords);
if (keywords.IndexOf("%") == -1 && !isCustomSearch)
{
keywords = SearchServices.SearchifyWord(keywords);
//keywords = "%" + keywords.Trim() + "%";
//keywords = keywords.Replace( "&", "%" ).Replace( " and ", "%" ).Replace( " in ", "%" ).Replace( " of ", "%" );
//keywords = keywords.Replace( " - ", "%" );
//keywords = keywords.Replace( " % ", "%" );
}
//skip url OR base.Url like '{0}'
if (isBasic || isCustomSearch)
{
where = where + AND + string.Format(" ( " + text + " ) ", keywords);
}
else
{
where = where + AND + string.Format(" ( " + text + indexFilter + " ) ", keywords);
}
//where = where + AND + string.Format( " ( " + text + keywordsFilter + subjects + frameworkItems + otherFrameworkItems + " ) ", keywords );
}