public QueryResult ExecuteSelect(Session session, PreparedQuery preparedQuery)
{
try
{
using (var txRef = core.Transactions.Begin(session))
{
var schemaMeta = core.Schemas.VirtualPathToMeta(txRef.Transaction, preparedQuery.Schema, LockOperation.Read);
if (schemaMeta == null || schemaMeta.Exists == false)
{
throw new LeafSQLSchemaDoesNotExistException(preparedQuery.Schema);
}
string documentCatalogDiskPath = Path.Combine(schemaMeta.DiskPath, Constants.DocumentCatalogFile);
var result = FindDocuments(txRef.Transaction, schemaMeta, preparedQuery.Conditions, preparedQuery.RowLimit, preparedQuery.SelectFields);
txRef.Commit();
return(result);
}
}
catch (Exception ex)
{
throw new LeafSQLExecutionException($"Failed to execute statement: {ex.Message}");
}
}
public SearchHits Search(PreparedQuery query, Sort sort, int maxResults = DefaultMaximumResults)
{
#if FEATURE_CONTENT_SEARCH
return new SearchHits(Searcher.Search(query.Query, null, maxResults, sort), Searcher.IndexReader);
#else
return new SearchHits(Searcher.Search(query.Query, sort));
#endif
}
public SearchHits Search(PreparedQuery query, Sort sort, int maxResults = DefaultMaximumResults)
{
#if SC62 || SC64 || SC66
return(new SearchHits(Searcher.Search(query.Query, sort)));
#else
return(new SearchHits(Searcher.Search(query.Query, null, maxResults, sort), Searcher.IndexReader));
#endif
}
public SearchHits Search(PreparedQuery query, Sort sort, int maxResults = DefaultMaximumResults)
{
#if SC62 || SC64 || SC66
return new SearchHits(Searcher.Search(query.Query, sort));
#else
return new SearchHits(Searcher.Search(query.Query, null, maxResults, sort), Searcher.IndexReader);
#endif
}
public SearchHits Search(PreparedQuery query, Sort sort, int maxResults = DefaultMaximumResults)
{
#if FEATURE_CONTENT_SEARCH
return(new SearchHits(Searcher.Search(query.Query, null, maxResults, sort), Searcher.IndexReader));
#else
return(new SearchHits(Searcher.Search(query.Query, sort)));
#endif
}
private Func <QueryContext, IEnumerable <TItem> > CreateCompiledEnumerableQuery <TItem>(Expression query)
{
var preparedQuery = new PreparedQuery(query, this.EntityTypeMap);
return(queryContext =>
{
object items = preparedQuery.Execute <TItem>(queryContext);
items = InterceptExceptionsMethod.MakeGenericMethod(typeof(TItem))
.Invoke(null, new[] { items, queryContext.Context.GetType(), this.logger, queryContext });
return (IEnumerable <TItem>)items;
});
}
static int ExecuteNonQueryImpl(DataConnection dataConnection, PreparedQuery preparedQuery)
{
if (preparedQuery.Commands.Length == 1)
{
dataConnection.InitCommand(CommandType.Text, preparedQuery.Commands[0], null, preparedQuery.QueryHints);
if (preparedQuery.Parameters != null)
{
foreach (var p in preparedQuery.Parameters)
{
dataConnection.Command.Parameters.Add(p);
}
}
return(dataConnection.ExecuteNonQuery());
}
for (var i = 0; i < preparedQuery.Commands.Length; i++)
{
dataConnection.InitCommand(CommandType.Text, preparedQuery.Commands[i], null, i == 0 ? preparedQuery.QueryHints : null);
if (i == 0 && preparedQuery.Parameters != null)
{
foreach (var p in preparedQuery.Parameters)
{
dataConnection.Command.Parameters.Add(p);
}
}
if (i < preparedQuery.Commands.Length - 1 && preparedQuery.Commands[i].StartsWith("DROP"))
{
try
{
dataConnection.ExecuteNonQuery();
}
catch (Exception)
{
}
}
else
{
dataConnection.ExecuteNonQuery();
}
}
return(-1);
}
void GetParameters(IQueryContext query, PreparedQuery pq)
{
if (UseQueryText)
{
return;
}
var parameters = query.GetParameters();
if (parameters.Length == 0 && pq.SqlParameters.Count == 0)
{
return;
}
var x = DataProvider.Convert("x", ConvertType.NameToQueryParameter).ToString();
var y = DataProvider.Convert("y", ConvertType.NameToQueryParameter).ToString();
var parms = new List <IDbDataParameter>(x == y ? pq.SqlParameters.Count : parameters.Length);
if (x == y)
{
for (var i = 0; i < pq.SqlParameters.Count; i++)
{
var sqlp = pq.SqlParameters[i];
if (sqlp.IsQueryParameter)
{
var parm = parameters.Length > i && parameters[i] == sqlp ? parameters[i] : parameters.First(p => p == sqlp);
AddParameter(parms, x, parm);
}
}
}
else
{
foreach (var parm in parameters)
{
if (parm.IsQueryParameter && pq.SqlParameters.Contains(parm))
{
var name = DataProvider.Convert(parm.Name, ConvertType.NameToQueryParameter).ToString();
AddParameter(parms, name, parm);
}
}
}
pq.Parameters = parms.ToArray();
}
public QueryExecutor(
PreparedQuery preparedQuery,
QueryContext queryContext,
IReadOnlyDictionary <string, IEntityType> entityTypeMap)
{
this.queryContext = queryContext;
this.createResultMapper = () => new InfoCarrierQueryResultMapper(queryContext, preparedQuery.TypeResolver, preparedQuery.TypeInfoProvider, entityTypeMap);
this.infoCarrierClient = ((InfoCarrierQueryContext)queryContext).InfoCarrierClient;
// Substitute query parameters
Expression expression = new SubstituteParametersExpressionVisitor(queryContext).Visit(preparedQuery.Expression);
// UGLY: this resembles Remote.Linq.DynamicQuery.RemoteQueryProvider<>.TranslateExpression()
this.rlinq = expression
.ToRemoteLinqExpression(preparedQuery.TypeInfoProvider, Remote.Linq.EntityFrameworkCore.ExpressionEvaluator.CanBeEvaluated)
.ReplaceQueryableByResourceDescriptors(preparedQuery.TypeInfoProvider)
.ReplaceGenericQueryArgumentsByNonGenericArguments();
}
public void ExecuteSelect(UInt64 processId, PreparedQuery preparedQuery)
{
try
{
using (var txRef = core.Transactions.Begin(processId))
{
var schemaMeta = core.Schemas.VirtualPathToMeta(txRef.Transaction, preparedQuery.Schema, LockOperation.Write);
if (schemaMeta == null || schemaMeta.Exists == false)
{
throw new DokdexSchemaDoesNotExistException(preparedQuery.Schema);
}
string documentCatalogDiskPath = Path.Combine(schemaMeta.DiskPath, Constants.DocumentCatalogFile);
FindDocuments(txRef.Transaction, schemaMeta, preparedQuery.Conditions, preparedQuery.RowLimit, preparedQuery.SelectFields);
/*
* var documentCatalog = core.IO.GetJson<PersistDocumentCatalog>(txRef.Transaction, documentCatalogDiskPath, LockOperation.Write);
*
* var persistDocument = documentCatalog.GetById(newId);
* if (persistDocument != null)
* {
* string documentDiskPath = Path.Combine(schemaMeta.DiskPath, Helpers.GetDocumentModFilePath(persistDocument.Id));
*
* core.IO.DeleteFile(txRef.Transaction, documentDiskPath);
*
* documentCatalog.Remove(persistDocument);
*
* core.Indexes.DeleteDocumentFromIndexes(txRef.Transaction, schemaMeta, persistDocument.Id);
*
* core.IO.PutJson(txRef.Transaction, documentCatalogDiskPath, documentCatalog);
* }
*/
txRef.Commit();
}
}
catch (Exception ex)
{
core.Log.Write(String.Format("Failed to delete document by ID for process {0}.", processId), ex);
throw;
}
}
public void ShouldExeccutePreparedQueryByPossition()
{
var query = new PreparedQuery("select from Profile where name = ? and surname = ?");
var docMichael = _database
.Query(query)
.Run("Michael", "Blach").SingleOrDefault();
Assert.That(docMichael, Is.Not.Null);
Assert.That(docMichael.GetField <string>("name"), Is.EqualTo("Michael"));
Assert.That(docMichael.GetField <string>("surname"), Is.EqualTo("Blach"));
var docLuca = _database
.Query(query)
.Run("Luca", "Garulli").SingleOrDefault();
Assert.That(docLuca, Is.Not.Null);
Assert.That(docLuca.GetField <string>("name"), Is.EqualTo("Luca"));
Assert.That(docLuca.GetField <string>("surname"), Is.EqualTo("Garulli"));
}
private Func <QueryContext, IAsyncEnumerable <TResult> > CreateCompiledAsyncEnumerableQuery <TResult>(Expression query, bool extractParams)
{
if (extractParams)
{
using (QueryContext qc = this.queryContextFactory.Create())
{
query = this.ExtractParameters(query, qc, false);
}
}
var preparedQuery = new PreparedQuery(query, this.EntityTypeMap);
return(queryContext =>
{
IAsyncEnumerable <TResult> result = preparedQuery.ExecuteAsync <TResult>(queryContext);
return (IAsyncEnumerable <TResult>)AsyncInterceptExceptionsMethod.MakeGenericMethod(typeof(TResult))
.Invoke(null, new object[] { result, queryContext.Context.GetType(), this.logger, queryContext });
});
}
static object ExecuteScalarImpl(DataConnection dataConnection, PreparedQuery preparedQuery)
{
IDbDataParameter idparam = null;
if (dataConnection.DataProvider.SqlProviderFlags.IsIdentityParameterRequired)
{
var sql = preparedQuery.SelectQuery;
if (sql.IsInsert && sql.Insert.WithIdentity)
{
idparam = dataConnection.Command.CreateParameter();
idparam.ParameterName = "IDENTITY_PARAMETER";
idparam.Direction = ParameterDirection.Output;
idparam.Direction = ParameterDirection.Output;
idparam.DbType = DbType.Decimal;
dataConnection.Command.Parameters.Add(idparam);
}
}
if (preparedQuery.Commands.Length == 1)
{
if (idparam != null)
{
// This is because the firebird provider does not return any parameters via ExecuteReader
// the rest of the providers must support this mode
dataConnection.ExecuteNonQuery();
return(idparam.Value);
}
return(dataConnection.ExecuteScalar());
}
dataConnection.ExecuteNonQuery();
dataConnection.InitCommand(CommandType.Text, preparedQuery.Commands[1], null, null);
return(dataConnection.ExecuteScalar());
}
static object ExecuteScalarImpl(DataConnection dataConnection, PreparedQuery preparedQuery)
{
IDbDataParameter idparam = null;
if (dataConnection.DataProvider.SqlProviderFlags.IsIdentityParameterRequired)
{
var sql = preparedQuery.SelectQuery;
if (sql.IsInsert && sql.Insert.WithIdentity)
{
idparam = dataConnection.Command.CreateParameter();
idparam.ParameterName = "IDENTITY_PARAMETER";
idparam.Direction = ParameterDirection.Output;
idparam.Direction = ParameterDirection.Output;
idparam.DbType = DbType.Decimal;
dataConnection.Command.Parameters.Add(idparam);
}
}
if (preparedQuery.Commands.Length == 1)
{
if (idparam != null)
{
// так сделано потому, что фаерберд провайдер не возвращает никаких параметров через ExecuteReader
// остальные провайдеры должны поддерживать такой режим
dataConnection.ExecuteNonQuery();
return(idparam.Value);
}
return(dataConnection.ExecuteScalar());
}
dataConnection.ExecuteNonQuery();
dataConnection.InitCommand(CommandType.Text, preparedQuery.Commands[1], null, null);
return(dataConnection.ExecuteScalar());
}
private void GetParameters(IQueryContext query, PreparedQuery pq)
{
var parameters = query.GetParameters();
if (parameters.Length == 0 && pq.SqlParameters.Count == 0)
{
return;
}
var ordered = DataProvider.SqlProviderFlags.IsParameterOrderDependent;
var c = ordered ? pq.SqlParameters.Count : parameters.Length;
var parms = new List <IDbDataParameter>(c);
if (ordered)
{
for (var i = 0; i < pq.SqlParameters.Count; i++)
{
var sqlp = pq.SqlParameters[i];
if (sqlp.IsQueryParameter)
{
var parm = parameters.Length > i && ReferenceEquals(parameters[i], sqlp)
? parameters[i]
: parameters.First(p => ReferenceEquals(p, sqlp));
AddParameter(parms, parm.Name, parm);
}
}
}
else
{
foreach (var parm in parameters)
{
if (parm.IsQueryParameter && pq.SqlParameters.Contains(parm))
{
AddParameter(parms, parm.Name, parm);
}
}
}
pq.Parameters = parms.ToArray();
}
/// <summary>
/// Searches the specified query.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="database">The database.</param>
/// <returns>Returns collection of items</returns>
public override IEnumerable<Item> Search(Query query, Database database)
{
Assert.ArgumentNotNull(query, "query");
Assert.ArgumentNotNull(database, "database");
string indexName = string.IsNullOrEmpty(this.IndexName) ? "products" : this.IndexName;
IEnumerable<Item> items;
LuceneQueryBuilder builder = new LuceneQueryBuilder(query, this.Database);
BooleanQuery luceneQuery = builder.BuildResultQuery();
using (IndexSearchContext context = SearchManager.GetIndex(indexName).CreateSearchContext())
{
this.AddDecorations(luceneQuery, database.Name);
PreparedQuery pq = new PreparedQuery(luceneQuery);
////Sitecore Query parser does not allow to use IDs in field values.
SearchHits hits = context.Search(pq, int.MaxValue);
items = this.GetSearchResultItems(hits);
}
return items;
}
//[-]
public void UpdatePriceHistory(DataRow[] ArrDr_Item)
{
DateTime ServerDate = Layer02_Common.GetServerDate();
PreparedQuery Pq = Do_Methods.CreateDataAccess().CreatePreparedQuery();
Pq.pQuery = @"Insert Into Item_PriceHistory (ItemID, Price, EmployeeID_PostedBy, DatePosted) Values (@ItemID, @Price, @EmployeeID_PostedBy, @DatePosted)";
Pq.Add_Parameter("ItemID", Do_Constants.eParameterType.Long);
Pq.Add_Parameter("EmployeeID_PostedBy", Do_Constants.eParameterType.Long, this.mCurrentUser.pDrUser["EmployeeID"]);
Pq.Add_Parameter("DatePosted", Do_Constants.eParameterType.DateTime, ServerDate);
Pq.Add_Parameter("Price", Do_Constants.eParameterType.Numeric, null, 0, 18, 2);
Pq.Prepare();
foreach (DataRow Dr in ArrDr_Item)
{
Pq.pParameter_Set("ItemID", Dr["ItemID"]);
Pq.pParameter_Set("Price", Dr["Price"]);
Pq.ExecuteNonQuery();
}
}
/// <summary>
/// Searches the specified query.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="database">The database.</param>
/// <returns>Returns collection of items</returns>
public override IEnumerable <Item> Search(Query query, Database database)
{
Assert.ArgumentNotNull(query, "query");
Assert.ArgumentNotNull(database, "database");
string indexName = string.IsNullOrEmpty(this.IndexName) ? "products" : this.IndexName;
IEnumerable <Item> items;
LuceneQueryBuilder builder = new LuceneQueryBuilder(query, this.Database);
BooleanQuery luceneQuery = builder.BuildResultQuery();
using (IndexSearchContext context = SearchManager.GetIndex(indexName).CreateSearchContext())
{
this.AddDecorations(luceneQuery, database.Name);
PreparedQuery pq = new PreparedQuery(luceneQuery);
////Sitecore Query parser does not allow to use IDs in field values.
SearchHits hits = context.Search(pq, int.MaxValue);
items = this.GetSearchResultItems(hits);
}
return(items);
}
public void ShouldExeccutePreparedQueryByName()
{
var query = new PreparedQuery("select from Profile where name = :name and surname = :surname");
var docMichael = _database
.Query(query)
.Set("name", "Michael")
.Set("surname", "Blach")
.Run().SingleOrDefault();
Assert.NotNull(docMichael);
Assert.Equal(docMichael.GetField <string>("name"), "Michael");
Assert.Equal(docMichael.GetField <string>("surname"), "Blach");
var docLuca = _database
.Query(query)
.Set("name", "Luca")
.Set("surname", "Garulli")
.Run().SingleOrDefault();
Assert.NotNull(docLuca);
Assert.Equal(docLuca.GetField <string>("name"), "Luca");
Assert.Equal(docLuca.GetField <string>("surname"), "Garulli");
}
public PreparedQuery Query(PreparedQuery query)
{
query.SetConnection(GetConnection());
return(query);
}
private static PreparedQuery GetProvisionSearchQuery(SearchRequest request, bool?hasType = null)
{
var preparedQuery = new PreparedQuery(request);
preparedQuery.Sql.Append("select");
if (request != null && request.ProvisionSearchLimit > 0)
{
preparedQuery.Sql.Append($" TOP {request.ProvisionSearchLimit} ");
}
preparedQuery.Sql.Append(" p.* ");
preparedQuery.Sql.Append(" from v_Provision p ");
preparedQuery.Sql.Append(" where 1=1 ");
if (hasType.HasValue)
{
preparedQuery.Sql.Append(hasType == true ? " and p.ProvisionType is not null " : " and p.ProvisionType is null ");
}
if (request != null)
{
// apply target text to all text columns
if (!string.IsNullOrEmpty(request.TargetText))
{
SearchHelper.AppendSearchCondition(request.TargetText,
new List <string>
{
"p.ProvisionType",
"p.Content",
"p.FundName",
"p.FundSponsorName",
"p.FundBusinessUnitName",
"p.FundStrategyName",
"p.InvestorName",
"p.InvestorType",
"p.Entity",
"p.Counsel",
"p.Notes",
"p.SideLetterFileName"
},
preparedQuery);
}
if (request.FundValues != null && request.FundValues.Length > 0)
{
preparedQuery.Sql.Append(" and p.FundName in @FundValues ");
}
if (request.InvestorValues != null && request.InvestorValues.Length > 0)
{
preparedQuery.Sql.Append(" and p.InvestorName in @InvestorValues ");
}
if (request.SponsorValues != null && request.SponsorValues.Length > 0)
{
preparedQuery.Sql.Append(" and p.FundSponsorName in @SponsorValues ");
}
if (request.BusinessUnitValues != null && request.BusinessUnitValues.Length > 0)
{
preparedQuery.Sql.Append(" and p.FundBusinessUnitName in @BusinessUnitValues ");
}
if (request.StrategyValues != null && request.StrategyValues.Length > 0)
{
preparedQuery.Sql.Append(" and p.FundStrategyName in @StrategyValues ");
}
if (request.InvestorTypeValues != null && request.InvestorTypeValues.Length > 0)
{
preparedQuery.Sql.Append(" and p.InvestorType in @InvestorTypeValues ");
}
if (request.ProvisionTypeValues != null && request.ProvisionTypeValues.Length > 0)
{
preparedQuery.Sql.Append(" and p.ProvisionType in @ProvisionTypeValues ");
}
if (request.EntityValues != null && request.EntityValues.Length > 0)
{
preparedQuery.Sql.Append(" and p.Entity in @EntityValues ");
}
if (request.CounselValues != null && request.CounselValues.Length > 0)
{
preparedQuery.Sql.Append(" and p.Counsel in @counselValues ");
}
if (request.SizeMin.HasValue)
{
preparedQuery.Sql.Append(" and p.FundSize >= @SizeMin ");
}
if (request.SizeMax.HasValue)
{
preparedQuery.Sql.Append(" and p.FundSize <= @SizeMax ");
}
if (request.YearMin.HasValue)
{
preparedQuery.Sql.Append(" and p.FundYear >= @YearMin ");
}
if (request.YearMax.HasValue)
{
preparedQuery.Sql.Append(" and p.FundYear <= @YearMax ");
}
if (request.CommitmentMin.HasValue)
{
preparedQuery.Sql.Append(" and p.Commitment >= @CommitmentMin ");
}
if (request.CommitmentMax.HasValue)
{
preparedQuery.Sql.Append(" and p.Commitment <= @CommitmentMax ");
}
if (request.AggregateSizeMin.HasValue)
{
preparedQuery.Sql.Append(" and p.Aggregated >= @AggregateSizeMin ");
}
if (request.AggregateSizeMax.HasValue)
{
preparedQuery.Sql.Append(" and p.Aggregated <= @AggregateSizeMax ");
}
if (request.FundId.HasValue)
{
preparedQuery.Sql.Append(" and p.FundId = @fundId ");
}
if (request.InvestorId.HasValue)
{
preparedQuery.Sql.Append(" and p.InvestorId = @investorId ");
}
if (request.FundInvestorIds != null && request.FundInvestorIds.Length > 0)
{
preparedQuery.Sql.Append(" and p.FundInvestorId in @fundInvestorIds ");
}
}
preparedQuery.Sql.Append(" order by p.Content ");
return(preparedQuery);
}
public SearchHits Search(PreparedQuery query, Sort sort)
{
return(new SearchHits(Searcher.Search(query.Query, sort)));
}
/// <summary>
/// Runs the facet.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="showAllVersions">if set to <c>true</c> [show all versions].</param>
/// <param name="faceted">if set to <c>true</c> [is facet].</param>
/// <param name="lookupId">if set to <c>true</c> [is id lookup].</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="pageNumber">The page number.</param>
/// <param name="termName">Name of the term.</param>
/// <param name="termValue">The term value.</param>
/// <param name="queryBase">The query base.</param>
/// <param name="locationFilter">The location filter.</param>
/// <returns>The dictionary of the running facet.</returns>
public virtual Dictionary <string, int> RunFacet(
Query query,
bool showAllVersions,
bool faceted,
bool lookupId,
int pageSize,
int pageNumber,
string termName,
List <string> termValue,
BitArray queryBase,
string locationFilter)
{
var runningCOunt = new Dictionary <string, int>();
Database db = Context.ContentDatabase ?? Context.Database;
string indexName = BucketManager.GetContextIndex(db.GetItem(locationFilter));
if (indexName.EndsWith("_remote"))
{
Index = RemoteSearchManager.GetIndex(indexName) as RemoteIndex;
}
else if (indexName.EndsWith("_inmemory"))
{
Index = InMemorySearchManager.GetIndex(indexName) as InMemoryIndex;
}
else
{
Index = SearchManager.GetIndex(indexName);
}
using (var context = new SortableIndexSearchContext(Index))
{
if (Config.EnableBucketDebug || Constants.EnableTemporaryBucketDebug)
{
Log.Info("Using: " + indexName, this);
Log.Info("Bucket Facet Original Debug Query: " + query, this);
}
foreach (string terms in termValue)
{
QueryFilter genreQueryFilter = this.GenreQueryFilter(query, faceted, lookupId, termName, terms);
if (Config.EnableBucketDebug || Constants.EnableTemporaryBucketDebug)
{
Log.Info("Bucket Facet Debug Query: " + genreQueryFilter, this);
}
PreparedQuery preparedQuery = context.PrepareQueryForFacetsUse(query);
Hits searchHits = context.Searcher.Search(preparedQuery.Query, genreQueryFilter);
int numberOfResults = searchHits.Length();
BitArray genreBitArray = genreQueryFilter.Bits(context.Searcher.GetIndexReader());
var tempSearchArray = queryBase.Clone() as BitArray;
if (tempSearchArray.Length == genreBitArray.Length)
{
BitArray combinedResults = tempSearchArray.And(genreBitArray);
int cardinality = SearchHelper.GetCardinality(combinedResults);
if (cardinality > 0)
{
if (!faceted)
{
if (!runningCOunt.ContainsKey(terms))
{
runningCOunt.Add(terms, cardinality);
}
}
else
{
if (!runningCOunt.ContainsKey(terms))
{
runningCOunt.Add(terms, numberOfResults);
}
}
}
}
}
}
return(runningCOunt);
}
protected override void SetQuery()
{
_preparedQuery = SetQuery(_dataConnection, Query.Queries[QueryNumber]);
}
public ExecutionPreparedQuery(PreparedQuery preparedQuery, IDbDataParameter[]?[] commandsParameters)
{
PreparedQuery = preparedQuery;
CommandsParameters = commandsParameters;
}
public static async Task <IEnumerable <Fund> > SearchFundsAsync(this MasterSideLetterDataAccess dataAccess, SearchRequest request)
{
var preparedQuery = new PreparedQuery(request);
preparedQuery.Sql.Append("select");
if (request.FundSearchLimit > 0)
{
preparedQuery.Sql.Append($" TOP {request.FundSearchLimit} ");
}
preparedQuery.Sql.Append(" f.*, uf.IsFavorite, uf.LastAccessedDate ");
preparedQuery.Sql.Append(" from v_Fund f ");
preparedQuery.Sql.Append(" left join UserFund uf ");
preparedQuery.Sql.Append(" on f.Id = uf.FundId ");
preparedQuery.Sql.Append(" and uf.UserName = @UserName ");
preparedQuery.Sql.Append(" where 1=1 ");
// apply target text to all text columns
if (!string.IsNullOrEmpty(request.TargetText))
{
SearchHelper.AppendSearchCondition(request.TargetText,
new List <string>
{
"f.Name",
"f.SponsorName",
"f.BusinessUnitName",
"f.StrategyName"
},
preparedQuery);
}
// apply relevant filters
if (request.FundValues != null && request.FundValues.Length > 0)
{
preparedQuery.Sql.Append(" and f.Name in @FundValues ");
}
// apply relevant filters
if (request.SponsorValues != null && request.SponsorValues.Length > 0)
{
preparedQuery.Sql.Append(" and f.SponsorName in @SponsorValues ");
}
if (request.BusinessUnitValues != null && request.BusinessUnitValues.Length > 0)
{
preparedQuery.Sql.Append(" and f.BusinessUnitName in @BusinessUnitValues ");
}
if (request.StrategyValues != null && request.StrategyValues.Length > 0)
{
preparedQuery.Sql.Append(" and f.StrategyName in @StrategyValues ");
}
if (request.SizeMin.HasValue)
{
preparedQuery.Sql.Append(" and f.Size >= @SizeMin ");
}
if (request.SizeMax.HasValue)
{
preparedQuery.Sql.Append(" and f.Size <= @SizeMax ");
}
if (request.YearMin.HasValue)
{
preparedQuery.Sql.Append(" and f.Year >= @YearMin ");
}
if (request.YearMax.HasValue)
{
preparedQuery.Sql.Append(" and f.Year <= @YearMax ");
}
preparedQuery.Sql.Append(" ORDER BY uf.LastAccessedDate DESC ");
return(await dataAccess.QueryAsync <Fund>(preparedQuery.Sql.ToString(), preparedQuery.Parameters));
}
public static async Task <List <Investor> > SearchInvestorsAsync(this MasterSideLetterDataAccess dataAccess, SearchRequest request)
{
var preparedQuery = new PreparedQuery(request);
preparedQuery.Sql.Append("select");
if (request.InvestorSearchLimit > 0)
{
preparedQuery.Sql.Append($" TOP {request.InvestorSearchLimit} ");
}
preparedQuery.Sql.Append(" i.* , ui.IsFavorite, ui.LastAccessedDate ");
preparedQuery.Sql.Append(" from v_Investor i ");
preparedQuery.Sql.Append(" left join UserInvestor ui ");
preparedQuery.Sql.Append(" on i.Id = ui.InvestorId ");
preparedQuery.Sql.Append(" and ui.UserName = @UserName ");
preparedQuery.Sql.Append(" where 1=1 ");
// apply target text to all text columns
if (!string.IsNullOrEmpty(request.TargetText))
{
SearchHelper.AppendSearchCondition(request.TargetText,
new List <string>
{
"i.Name",
"i.InvestorType"
},
preparedQuery);
}
// apply relevant filters
if (request.InvestorValues != null && request.InvestorValues.Length > 0)
{
preparedQuery.Sql.Append(" and i.Name in @InvestorValues ");
}
if (request.InvestorTypeValues != null && request.InvestorTypeValues.Length > 0)
{
preparedQuery.Sql.Append(" and i.InvestorType in @InvestorTypeValues ");
}
if (request.AggregateSizeMin.HasValue)
{
preparedQuery.Sql.Append(" and i.Aggregated >= @AggregateSizeMin ");
}
if (request.AggregateSizeMax.HasValue)
{
preparedQuery.Sql.Append(" and i.Aggregated <= @AggregateSizeMax ");
}
IEnumerable <Investor> results = await dataAccess.QueryAsync <Investor>(preparedQuery.Sql.ToString(), preparedQuery.Parameters);
var investors = results.ToList();
foreach (var investor in investors)
{
await dataAccess.AddRecentInvestmentsAsync(investor);
}
return(investors);
}
public SearchHits Search(PreparedQuery query, Sort sort)
{
return new SearchHits(Searcher.Search(query.Query, sort));
}
public SearchHits Search(PreparedQuery query, Sort sort)
{
return(Search(query.Query, SearchContext.Empty, sort));
}
public static async Task <IEnumerable <FundInvestor> > SearchFundInvestorsAsync(this MasterSideLetterDataAccess dataAccess, SearchRequest request)
{
var preparedQuery = new PreparedQuery(request);
preparedQuery.Sql.Append("select");
if (request.SideLetterSearchLimit > 0)
{
preparedQuery.Sql.Append($" TOP {request.SideLetterSearchLimit} ");
}
preparedQuery.Sql.Append(" fi.* ");
preparedQuery.Sql.Append(" from v_FundInvestor fi ");
preparedQuery.Sql.Append(" where 1=1 ");
// apply target text to all text columns
if (!string.IsNullOrEmpty(request.TargetText))
{
SearchHelper.AppendSearchCondition(request.TargetText,
new List <string>
{
"fi.FundName",
"fi.FundSponsorName",
"fi.FundBusinessUnitName",
"fi.FundStrategyName",
"fi.InvestorName",
"fi.InvestorType",
"fi.Entity",
"fi.Counsel",
"fi.Notes",
"fi.SideLetterFileName"
},
preparedQuery);
}
// apply relevant filters
if (request.FundValues != null && request.FundValues.Length > 0)
{
preparedQuery.Sql.Append(" and fi.FundName in @FundValues ");
}
if (request.InvestorValues != null && request.InvestorValues.Length > 0)
{
preparedQuery.Sql.Append(" and fi.InvestorName in @InvestorValues ");
}
if (request.SponsorValues != null && request.SponsorValues.Length > 0)
{
preparedQuery.Sql.Append(" and fi.FundSponsorName in @SponsorValues ");
}
if (request.BusinessUnitValues != null && request.BusinessUnitValues.Length > 0)
{
preparedQuery.Sql.Append(" and fi.FundBusinessUnitName in @BusinessUnitValues ");
}
if (request.StrategyValues != null && request.StrategyValues.Length > 0)
{
preparedQuery.Sql.Append(" and fi.FundStrategyName in @StrategyValues ");
}
if (request.InvestorTypeValues != null && request.InvestorTypeValues.Length > 0)
{
preparedQuery.Sql.Append(" and fi.InvestorType in @InvestorTypeValues ");
}
if (request.EntityValues != null && request.EntityValues.Length > 0)
{
preparedQuery.Sql.Append(" and fi.Entity in @EntityValues ");
}
if (request.CounselValues != null && request.CounselValues.Length > 0)
{
preparedQuery.Sql.Append(" and fi.Counsel in @CounselValues ");
}
if (request.SizeMin.HasValue)
{
preparedQuery.Sql.Append(" and fi.FundSize >= @SizeMin ");
}
if (request.SizeMax.HasValue)
{
preparedQuery.Sql.Append(" and fi.FundSize <= @SizeMax ");
}
if (request.YearMin.HasValue)
{
preparedQuery.Sql.Append(" and fi.FundYear >= @YearMin ");
}
if (request.YearMax.HasValue)
{
preparedQuery.Sql.Append(" and fi.FundYear <= @YearMax ");
}
if (request.CommitmentMin.HasValue)
{
preparedQuery.Sql.Append(" and fi.Commitment >= @CommitmentMin ");
}
if (request.CommitmentMax.HasValue)
{
preparedQuery.Sql.Append(" and fi.Commitment <= @CommitmentMax ");
}
if (request.AggregateSizeMin.HasValue)
{
preparedQuery.Sql.Append(" and fi.Aggregated >= @AggregateSizeMin ");
}
if (request.AggregateSizeMax.HasValue)
{
preparedQuery.Sql.Append(" and fi.Aggregated <= @AggregateSizeMax ");
}
if (request.InvestorId.HasValue)
{
preparedQuery.Sql.Append(" and fi.InvestorId = @InvestorId ");
}
if (request.FundId.HasValue)
{
preparedQuery.Sql.Append(" and fi.FundId = @FundId");
}
return(await dataAccess.QueryAsync <FundInvestor>(preparedQuery.Sql.ToString(), preparedQuery.Parameters));
}
//[-]
public static void AddSelected(
DataTable Dt_Target
, DataTable Dt_Selected
, string Query_Selected_Source
, string Query_Selected_Key
, string Target_Key
, List <Str_AddSelectedFields> Obj_Fields = null
, List <Str_AddSelectedFieldsDefault> Obj_FieldsDefault = null)
{
if (!(Dt_Selected.Rows.Count > 0))
{
return;
}
PreparedQuery Pq = Do_Methods.CreateDataAccess().CreatePreparedQuery();
Pq.pQuery = @"Select * From " + Query_Selected_Source + @" Where " + Query_Selected_Key + @" = @ID";
Pq.Add_Parameter("ID", Do_Constants.eParameterType.Long);
Pq.Prepare();
foreach (DataRow Dr_Selected in Dt_Selected.Rows)
{
//Pq.pParameters["ID"].Value = (Int64)Do_Methods.IsNull(Dr_Selected["ID"], 0);
Pq.pParameter_Set("ID", Do_Methods.Convert_Int64(Dr_Selected["ID"]));
DataTable Inner_Dt_Selected = Pq.ExecuteQuery().Tables[0];
if (Inner_Dt_Selected.Rows.Count > 0)
{
DataRow Inner_Dr_Selected = Inner_Dt_Selected.Rows[0];
DataRow[] Inner_ArrDr;
DataRow Inner_Dr_Target = null;
bool Inner_IsFound = false;
Inner_ArrDr = Dt_Target.Select(Target_Key + " = " + Convert.ToInt64(Inner_Dr_Selected[Query_Selected_Key]));
if (Inner_ArrDr.Length > 0)
{
Inner_Dr_Target = Inner_ArrDr[0];
if ((bool)Do_Methods.IsNull(Inner_Dr_Target["IsDeleted"], false))
{
Inner_Dr_Target["IsDeleted"] = DBNull.Value;
Inner_IsFound = true;
}
}
if (!Inner_IsFound)
{
Int64 Ct = 0;
Inner_ArrDr = Dt_Target.Select("", "TmpKey Desc");
if (Inner_ArrDr.Length > 0)
{
Ct = (Int64)Inner_ArrDr[0]["TmpKey"];
}
Ct++;
DataRow Nr = Dt_Target.NewRow();
Nr["TmpKey"] = Ct;
Nr["Item_Style"] = "";
Nr[Target_Key] = (Int64)Inner_Dr_Selected[Query_Selected_Key];
Dt_Target.Rows.Add(Nr);
Inner_Dr_Target = Nr;
}
if (Obj_Fields != null)
{
foreach (Str_AddSelectedFields Inner_Obj in Obj_Fields)
{
Inner_Dr_Target[Inner_Obj.Field_Target] = Inner_Dr_Selected[Inner_Obj.Field_Selected];
}
}
if (Obj_FieldsDefault != null)
{
foreach (Str_AddSelectedFieldsDefault Inner_Obj in Obj_FieldsDefault)
{
Inner_Dr_Target[Inner_Obj.Field_Target] = Inner_Obj.Value;
}
}
}
}
}
