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