/// <summary>
/// The search.
/// </summary>
/// <param name="filter">
/// The filter.
/// </param>
/// <param name="page">
/// The page.
/// </param>
/// <param name="perPage">
/// The per page.
/// </param>
/// <param name="orderType">
/// The ordering type.
/// </param>
/// <param name="sortExpression">
/// The sort expression.
/// </param>
/// <typeparam name="T">
/// The type of source.
/// </typeparam>
/// <typeparam name="TKey">
/// The property of the source.
/// </typeparam>
/// <returns>
/// The search results response.
/// </returns>
public virtual GenericSearchResponse <T> Search <T, TKey>(
Expression <Func <T, bool> > filter,
int page,
int perPage,
SortOrder orderType,
Expression <Func <T, TKey> > sortExpression)
{
using (var context = this.Index.CreateSearchContext())
{
IQueryable <T> allResults = null;
allResults = orderType == SortOrder.Descending
? context.GetQueryable <T>()
.Where(filter)
.OrderByDescending(sortExpression)
.Page(page - 1, perPage)
: context.GetQueryable <T>()
.Where(filter)
.OrderBy(sortExpression)
.Page(page - 1, perPage);
var response = new GenericSearchResponse <T>(allResults.GetResults());
return(response);
}
}
public GenericSearchResponse <ThumbnailTask> Search(ThumbnailTaskSearchCriteria criteria)
{
using (var repository = _thumbnailRepositoryFactory())
{
var query = GetTasksQuery(repository, criteria);
var sortInfos = criteria.SortInfos;
if (sortInfos.IsNullOrEmpty())
{
sortInfos = new[]
{
new SortInfo
{
SortColumn = ReflectionUtility.GetPropertyName <ThumbnailTask>(t => t.CreatedDate), SortDirection = SortDirection.Descending
}
};
}
query = query.OrderBySortInfos(sortInfos);
var totalCount = query.Count();
var ids = query.Skip(criteria.Skip).Take(criteria.Take).Select(x => x.Id).ToArray();
var results = repository.GetThumbnailTasksByIds(ids).Select(t => t.ToModel(AbstractTypeFactory <ThumbnailTask> .TryCreateInstance())).ToArray();
var retVal = new GenericSearchResponse <ThumbnailTask>
{
TotalCount = totalCount,
Results = results.AsQueryable().OrderBySortInfos(sortInfos).ToList()
};
return(retVal);
}
}
/// <summary>
/// Gets all items in the index file.
/// </summary>
/// <returns>
/// The search response.
/// </returns>
public virtual GenericSearchResponse <T> All()
{
using (var context = this.Index.CreateSearchContext())
{
var allResults = context.GetQueryable <T>().GetResults();
var response = new GenericSearchResponse <T>(allResults);
return(response);
}
}
/// <summary>
/// Gets specified items by expression.
/// </summary>
/// <param name="filter">
/// The filter.
/// </param>
/// <returns>
/// The search response.
/// </returns>
public virtual GenericSearchResponse <T> Search(Expression <Func <T, bool> > filter)
{
using (var context = this.Index.CreateSearchContext())
{
var allResults = context.GetQueryable <T>().Where(filter).GetResults();
var response = new GenericSearchResponse <T>(allResults);
return(response);
}
}
/// <summary>
/// Gets specified items on specified page.
/// </summary>
/// <param name="page">
/// The page.
/// </param>
/// <param name="perPage">
/// The per page.
/// </param>
/// <returns>
/// The search response.
/// </returns>
public virtual GenericSearchResponse <T> All(int page, int perPage)
{
using (var context = this.Index.CreateSearchContext())
{
IQueryable <T> allResults = null;
allResults = context.GetQueryable <T>().Page(page - 1, perPage);
var response = new GenericSearchResponse <T>(allResults.GetResults());
return(response);
}
}
/// <summary>
/// Gets specified items by expression on specified page.
/// </summary>
/// <param name="filter">
/// The filter.
/// </param>
/// <param name="page">
/// The page.
/// </param>
/// <param name="perPage">
/// The per page.
/// </param>
/// <returns>
/// The search response.
/// </returns>
public virtual GenericSearchResponse <T> Search(
Expression <Func <T, bool> > filter,
int page,
int perPage)
{
using (var context = this.Index.CreateSearchContext())
{
IQueryable <T> allResults = null;
allResults = context.GetQueryable <T>().Where(filter).Page(page - 1, perPage);
var response = new GenericSearchResponse <T>(allResults.GetResults());
return(response);
}
}
/// <summary>
/// Gets specified items by expression on specified page with a specific order.
/// </summary>
/// <param name="filter">
/// The filter.
/// </param>
/// <param name="page">
/// The page.
/// </param>
/// <param name="perPage">
/// The per page.
/// </param>
/// <param name="orderBy">
/// The order by.
/// </param>
/// <param name="propertyName">
/// The property name.
/// </param>
/// <returns>
/// The search response.
/// </returns>
public virtual GenericSearchResponse <T> Search(
Expression <Func <T, bool> > filter,
int page,
int perPage,
SortOrder orderBy,
string propertyName)
{
using (var context = this.Index.CreateSearchContext())
{
var allResults = context.GetQueryable <T>().Where(filter).GetResults();
var response = new GenericSearchResponse <T>(allResults, page, perPage, orderBy, propertyName);
return(response);
}
}
public GenericSearchResponse <List <SearchTransactionsResponse> > SearchTransactions([FromBody] SearchTransactionsRequest searchTransactionsRequest)
{
string orderByClause = string.Empty;
string limitClause = string.Empty;
string sWhere = "cn.status = 1";
if (!string.IsNullOrWhiteSpace(searchTransactionsRequest.OrganizationId?.ToString()))
{
if (!string.IsNullOrWhiteSpace(sWhere))
{
sWhere += " AND ";
}
sWhere += $"acc.organization_id = {searchTransactionsRequest.OrganizationId}";
}
if (!string.IsNullOrWhiteSpace(searchTransactionsRequest.MemberPayeeId?.ToString()))
{
if (!string.IsNullOrWhiteSpace(sWhere))
{
sWhere += " AND ";
}
sWhere += $"cn.contributor_id = {searchTransactionsRequest.MemberPayeeId}";
}
if (!string.IsNullOrWhiteSpace(searchTransactionsRequest.TransactionName))
{
if (!string.IsNullOrWhiteSpace(sWhere))
{
sWhere += " AND ";
}
sWhere += $"cn.contribution_name = '{searchTransactionsRequest.TransactionName}'";
}
if (!string.IsNullOrWhiteSpace(searchTransactionsRequest.AccountId?.ToString()))
{
if (!string.IsNullOrWhiteSpace(sWhere))
{
sWhere += " AND ";
}
sWhere += $"acc.account_id = {searchTransactionsRequest.AccountId}";
}
if (!string.IsNullOrWhiteSpace(searchTransactionsRequest.TransactionType?.ToString()))
{
if (!string.IsNullOrWhiteSpace(sWhere))
{
sWhere += " AND ";
}
sWhere += $"cn.transaction_type = {searchTransactionsRequest.TransactionType}";
}
if (!string.IsNullOrWhiteSpace(searchTransactionsRequest.TransactionMode?.ToString()))
{
if (!string.IsNullOrWhiteSpace(sWhere))
{
sWhere += " AND ";
}
sWhere += $"cn.transaction_mode = {searchTransactionsRequest.TransactionMode}";
}
if (!string.IsNullOrWhiteSpace(searchTransactionsRequest.Category?.ToString()))
{
if (!string.IsNullOrWhiteSpace(sWhere))
{
sWhere += " AND ";
}
sWhere += $"cn.category = {searchTransactionsRequest.Category}";
}
if (!string.IsNullOrWhiteSpace(searchTransactionsRequest.CheckNumber))
{
if (!string.IsNullOrWhiteSpace(sWhere))
{
sWhere += " AND ";
}
sWhere += $"cn.check_number = '{searchTransactionsRequest.CheckNumber}'";
}
if (!string.IsNullOrWhiteSpace(searchTransactionsRequest.FromDate.ToString()))
{
if (!string.IsNullOrWhiteSpace(sWhere))
{
sWhere += " AND ";
}
sWhere += $"date_format(cn.transaction_date, '%Y-%m-%d') >= '{searchTransactionsRequest.FromDate.Value.ToString("yyyy-MM-dd")}'";
}
if (!string.IsNullOrWhiteSpace(searchTransactionsRequest.ToDate.ToString()))
{
if (!string.IsNullOrWhiteSpace(sWhere))
{
sWhere += " AND ";
}
sWhere += $"date_format(cn.transaction_date, '%Y-%m-%d') <= '{searchTransactionsRequest.ToDate.Value.ToString("yyyy-MM-dd")}'";
}
sWhere = $" WHERE {sWhere} ";
if (!string.IsNullOrWhiteSpace(searchTransactionsRequest.SearchParameters?.OrderBy))
{
orderByClause = $"ORDER BY {searchTransactionsRequest.SearchParameters?.OrderBy} {searchTransactionsRequest.SearchParameters?.SortOrder}";
}
limitClause = $"LIMIT {searchTransactionsRequest.SearchParameters?.StartAt}, {searchTransactionsRequest.SearchParameters?.MaxRecords}";
var transactionList = new List <SearchTransactionsResponse>();
// var ContributionList = new List<Contribution>();
int totalRows = 0;
using (MySqlConnection connection = (MySqlConnection)_context.Database.GetDbConnection())
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = $@"SELECT sql_calc_found_rows cn.contribution_id AS 'Contribution Id',
cn.contributor_id,
acc.account_name,
cr.first_name,
cr.last_name,
cr.family_name,
cn.account_id as 'AccountId',
CASE
WHEN IFNULL(cn.contribution_name, '') = ''
THEN
CONCAT(cr.first_name, ' ', cr.last_name)
ELSE
cn.contribution_name
END
AS 'Name',
cvd.description AS Category,
cvd_transtype.description AS Type,
cvd_transmode.description AS Mode,
cn.amount AS Amount,
cn.check_number AS 'Check #',
cn.transaction_date AS 'Trans DT',
cn.note AS 'Note',
cn.date_added AS 'Date Added'
FROM contribution cn
LEFT JOIN account acc
ON acc.account_id = cn.account_id AND acc.status = 1
LEFT JOIN organization org
ON org.organization_id = acc.organization_id
LEFT JOIN contributor cr ON cr.contributor_id = cn.contributor_id
LEFT JOIN table_column tc
ON tc.table_name = 'contribution'
AND tc.column_name = 'category'
AND tc.status = 1
LEFT JOIN column_value_desc cvd
ON cvd.table_column_id = tc.table_column_id
AND cvd.value = cn.category
AND cvd.status = 1
LEFT JOIN table_column tc_transtype
ON tc_transtype.table_name = 'contribution'
AND tc_transtype.column_name = 'transaction_type'
AND tc_transtype.status = 1
LEFT JOIN column_value_desc cvd_transtype
ON cvd_transtype.table_column_id = tc_transtype.table_column_id
AND cvd_transtype.value = cn.transaction_type
AND cvd_transtype.status = 1
LEFT JOIN table_column tc_transmode
ON tc_transmode.table_name = 'contribution'
AND tc_transmode.column_name = 'transaction_mode'
AND tc_transmode.status = 1
LEFT JOIN column_value_desc cvd_transmode
ON cvd_transmode.table_column_id = tc_transmode.table_column_id
AND cvd_transmode.value = cn.transaction_mode
AND cvd_transmode.status = 1
-- ORDER BY cn.date_added DESC;
{sWhere} {orderByClause} {limitClause};
select found_rows() as total_records";
try
{
MySqlDataAdapter sda = new MySqlDataAdapter();
sda.SelectCommand = command;
DataSet ds = new DataSet();
sda.Fill(ds);
System.Data.DataTable dt = ds.Tables[0];
totalRows = int.Parse(ds.Tables[1].Rows[0]["total_records"].ToString());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dRow in dt.Rows)
{
transactionList.Add(new SearchTransactionsResponse(dRow));
/*ContributionList.Add(new Contribution()
* {
* ContributionName = dRow["Name"].ToString(),
* ContributorId = int.TryParse(dRow["contributor_id"].ToString(), out int conId) ? int.Parse(dRow["contributor_id"].ToString()) : (int?)null,
* Contributor = int.TryParse(dRow["contributor_id"].ToString(), out int contId) ? new Contributor()
* {
* ContributorId = int.Parse(dRow["contributor_id"].ToString()),
* FirstName = dRow["first_name"].ToString(),
* LastName = dRow["last_name"].ToString(),
* FamilyName = dRow["family_name"].ToString()
* } : null,
* Amount = Convert.ToDecimal(dRow["Amount"].ToString()),
* Category = dRow["family_name"].ToString()
* });*/
/*this.ContributionId = dRow["Contribution Id"].ToString();
* this.AccountId = dRow["AccountId"].ToString();
* this.AccountName = dRow["account_name"].ToString();
* this.ContributorName = dRow["Name"].ToString();
* this.Category = dRow["Category"].ToString();
* this.TransactionType = dRow["Type"].ToString();
* this.TransactionMode = dRow["Mode"].ToString();
* this.Amount = dRow["Amount"].ToString();
* this.CheckNumber = dRow["Check #"].ToString();
* this.TransactionDate = Convert.ToDateTime(dRow["Trans DT"].ToString());
* this.Note = dRow["Note"].ToString();
* this.DateAdded = Convert.ToDateTime(dRow["Date Added"].ToString());*/
}
}
}
finally
{
}
}
}
var SearchTransactionResponseList = new GenericSearchResponse <List <SearchTransactionsResponse> >()
{
Response = transactionList,
TotalRecordCount = totalRows
};
return(SearchTransactionResponseList);
}
