public virtual async Task <Response <object> > SearchTransactionsAsync(SearchTransactionsRequest body, CancellationToken cancellationToken = default)
{
using var scope = _clientDiagnostics.CreateScope("RosettaClient.SearchTransactions");
scope.Start();
try
{
return(await RestClient.SearchTransactionsAsync(body, cancellationToken).ConfigureAwait(false));
}
catch (Exception e)
{
scope.Failed(e);
throw;
}
}
public virtual Response <object> SearchTransactions(SearchTransactionsRequest body, CancellationToken cancellationToken = default)
{
using var scope = _clientDiagnostics.CreateScope("RosettaClient.SearchTransactions");
scope.Start();
try
{
return(RestClient.SearchTransactions(body, cancellationToken));
}
catch (Exception e)
{
scope.Failed(e);
throw;
}
}
public IActionResult Index(int page = 1, int pageSize = 10)
{
var request = new SearchTransactionsRequest()
{
SearchParameters = new SearchParameters()
{
MaxRecords = int.MaxValue,
StartAt = page == 1 ? 0 : (page - 1) * pageSize,
SortOrder = "desc",
OrderBy = "cn.date_added"
}
};
var transactions = GetPagedSearchTransactionList(page, pageSize, request);
ViewBag.CategoryList = this.GetCVDList("contribution", "category");
ViewBag.TransactionTypeList = this.GetCVDList("contribution", "transaction_type");
ViewBag.TransactionModeList = this.GetCVDList("contribution", "transaction_mode");
ViewBag.AccountList = this.GetAccountList();
ViewBag.MemberFullNameList = this.GetMemberFullNameList();
ViewBag.PageSize = pageSize;
return(View(transactions));
}
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);
}
protected List <SearchTransactionsResponse> SearchTransactionsFromTheDatabase(SearchTransactionsRequest searchTransactionsRequest)
{
var apiTransaction = new Church.API.Client.ApiCallerTransaction(_apiUrl.SSChurch);
var transactionList = apiTransaction.SearchTransactions(searchTransactionsRequest);
return(new List <SearchTransactionsResponse>());
}
protected IPagedList <SearchTransactionsResponse> GetPagedSearchTransactionList(int?page, int pageSize, SearchTransactionsRequest request)
{
// return a 404 if user browses to before the first page
if (page.HasValue && page < 1)
{
return(null);
}
// retrieve list from database/whereverand
var listUnpaged = SearchTransactionsFromTheDatabase(request);
// page the list
var listPaged = listUnpaged.ToPagedList(page ?? 1, pageSize);
// return a 404 if user browses to pages beyond last page. special case first page if no items exist
if (listPaged.PageNumber != 1 && page.HasValue && page > listPaged.PageCount)
{
return(null);
}
return(listPaged);
}
protected GenericSearchResponse <List <SearchTransactionsResponse> > GetPagedSearchTransactionListV1(int?page, int pageSize, SearchTransactionsRequest request)
{
// return a 404 if user browses to before the first page
if (page.HasValue && page < 1)
{
return(null);
}
// retrieve list from database/whereverand
var transactionList = SearchTransactionsFromTheDatabaseV1(request);
return(transactionList);
}
/*public IActionResult Index(int page = 1, int pageSize = 10)
* {
* var transactions = GetPagedTransactionList(page, pageSize);
*
* ViewBag.CategoryList = this.GetCVDList("contribution", "category");
* ViewBag.TransactionTypeList = this.GetCVDList("contribution", "transaction_type");
* ViewBag.TransactionModeList = this.GetCVDList("contribution", "transaction_mode");
* ViewBag.AccountList = this.GetAccountList();
* ViewBag.MemberFullNameList = this.GetMemberFullNameList();
*
* return View(transactions);
* }
*
* protected IPagedList<Church.API.Models.Contribution> GetPagedTransactionList(int? page, int pageSize)
* {
* // return a 404 if user browses to before the first page
* if (page.HasValue && page < 1)
* return null;
*
* // retrieve list from database/whereverand
* var listUnpaged = GetTransactionsFromTheDatabase();
*
* // page the list
* var listPaged = listUnpaged.ToPagedList(page ?? 1, pageSize);
*
* // return a 404 if user browses to pages beyond last page. special case first page if no items exist
* if (listPaged.PageNumber != 1 && page.HasValue && page > listPaged.PageCount)
* return null;
*
* return listPaged;
* }*/
public IActionResult IndexV1(int page = 1, int pageSize = 10)
{
var request = new SearchTransactionsRequest()
{
SearchParameters = new SearchParameters()
{
MaxRecords = pageSize,
StartAt = page == 1 ? 0 : (page - 1) * pageSize,
SortOrder = "desc",
OrderBy = "cn.date_added"
}
};
var transactions = GetPagedSearchTransactionListV1(page, pageSize, request);
//ViewBag.CategoryList = this.GetCVDList("contribution", "category");
//ViewBag.TransactionTypeList = this.GetCVDList("contribution", "transaction_type");
//ViewBag.TransactionModeList = this.GetCVDList("contribution", "transaction_mode");
//ViewBag.AccountList = this.GetAccountList();
//ViewBag.MemberFullNameList = this.GetMemberFullNameList();
ViewBag.PageSize = pageSize;
ViewBag.PageNumber = 1;
SelectList AccountSelectList = new SelectList(AppUtil.GetItemList <int, string>(this.GetAccountList()), "Id", "Name", request.AccountId);
ViewBag.AccountSelectList = AccountSelectList;
SelectList MemberFullNameSelectList = new SelectList(AppUtil.GetItemList <int, string>(this.GetMemberFullNameList()), "Id", "Name", request.MemberPayeeId);
ViewBag.MemberFullNameSelectList = MemberFullNameSelectList;
SelectList CategorySelectList = new SelectList(AppUtil.GetItemList <string, string>(this.GetCVDList("contribution", "category")), "Id", "Name", request.Category);
ViewBag.CategorySelectList = CategorySelectList;
SelectList TransactionTypeSelectList = new SelectList(AppUtil.GetItemList <string, string>(this.GetCVDList("contribution", "transaction_type")), "Id", "Name", request.TransactionType);
ViewBag.TransactionTypeSelectList = TransactionTypeSelectList;
SelectList TransactionModeSelectList = new SelectList(AppUtil.GetItemList <string, string>(this.GetCVDList("contribution", "transaction_mode")), "Id", "Name", request.TransactionMode);
ViewBag.TransactionModeSelectList = TransactionModeSelectList;
Item[] pglist = new[] {
new Item {
Id = 10, Name = "10"
},
new Item {
Id = 25, Name = "25"
},
new Item {
Id = 50, Name = "50"
},
new Item {
Id = 100, Name = "100"
}
};
SelectList selectList = new SelectList(pglist, "Id", "Name", pageSize);
ViewBag.PageSizeList = selectList;
return(View(transactions));
}
