public IEnumerable <FilterOption> Options([FromQuery] TransactionBySettlementDateArgs args, [FromQuery] OptionArgs optionsArgs)
{
args.TransactionTypes = args.TransactionTypes ?? new List <string> {
"OneStepPayment", "RefundPayment", "CapturePayment"
};
args.ResponseCodes = (args.ResponseCodes ?? new List <string>()).Union(new[] { "1000" }).ToList();
var criteria = new List <string>();
criteria.AddIfNotNull(args.DateFrom, "settlement_date >= @DateFrom");
criteria.AddIfNotNull(args.DateTo, "settlement_date <= @DateTo");
criteria.AddIfNotNull(args.GlobalIds, "global_id IN (@GlobalIds)");
criteria.AddIfNotNull(args.BankIds, "mongo_bank_id IN (@BankIds)");
criteria.AddIfNotNull(args.CorporateIds, "corp_id IN (@CorporateIds)");
criteria.AddIfNotNull(args.MerchantIds, "mongo_m_id IN (@MerchantIds)");
criteria.AddIfNotNull(args.Currencies, "currency IN (@Currencies)");
criteria.AddIfNotNull(args.TransactionTypes, "txn_type IN (@TransactionTypes)");
criteria.AddIfNotNull(args.ResponseCodes, "tx.resp_code IN (@ResponseCodes)");
criteria.AddIfNotNull(args.MerchantTransactionID, "m_txn_id LIKE @MerchantTransactionID", () => args.MerchantTransactionID = string.Format("%{0}%", args.MerchantTransactionID));
criteria.AddIfNotNull(args.Product, "ts.api_name = @Product");
var where = criteria.ToWhereClause();
var sql = @"select distinct
" + optionsArgs.OptionValue.MapToColumn() + @" AS Key,
" + optionsArgs.OptionText.MapToColumn() + @" AS Value
from cs_rpt_txn tx
LEFT JOIN cs_rpt_timestamp ts ON tx.cs_rpt_txn_id = ts.cs_rpt_txn_id
" + where;
var result = _db.Fetch <FilterOption>(sql, args);
return(result);
}
public PagedResult <TransactionBySettlementDateRow> Get([FromQuery] TransactionBySettlementDateArgs args)
{
args.TransactionTypes = args.TransactionTypes ?? new List <string> {
"OneStepPayment", "RefundPayment", "CapturePayment"
};
args.ResponseCodes = (args.ResponseCodes ?? new List <string>()).Union(new[] { "1000" }).ToList();
var criteria = new List <string>();
criteria.Add("((tx.automatic_transaction = true and tx.txn_type != 'PaymentStatus') or (tx.automatic_transaction is null))");
criteria.AddIfNotNull(args.DateFrom, "settlement_date >= @DateFrom");
criteria.AddIfNotNull(args.DateTo, "settlement_date <= @DateTo");
criteria.AddIfNotNull(args.GlobalIds, "global_id IN (@GlobalIds)");
criteria.AddIfNotNull(args.BankIds, "mongo_bank_id IN (@BankIds)");
criteria.AddIfNotNull(args.CorporateIds, "corp_id IN (@CorporateIds)");
criteria.AddIfNotNull(args.MerchantIds, "mongo_m_id IN (@MerchantIds)");
criteria.AddIfNotNull(args.Currencies, "currency IN (@Currencies)");
criteria.AddIfNotNull(args.TransactionTypes, "txn_type IN (@TransactionTypes)");
criteria.AddIfNotNull(args.ResponseCodes, "tx.resp_code IN (@ResponseCodes)");
criteria.AddIfNotNull(args.MerchantTransactionID, "m_txn_id LIKE @MerchantTransactionID", () => args.MerchantTransactionID = string.Format("%{0}%", args.MerchantTransactionID));
criteria.AddIfNotNull(args.Product, "ts.api_name = @Product");
var where = criteria.ToWhereClause();
var sql = @"select
(tx.settlement_date::timestamp without time zone + (@TimeZoneOffset ||' minutes')::interval)::date as Date,
txn_type as TransactionType,
ts.api_name as Product,
tx.resp_code as ResponseCode,
currency as Currency,
amount*.001 as Amount,
m_txn_id as MerchantTransactionID,
bin as BIN,
acc_holder as AccountHolder,
crypto_key as CryptoId,
tx.global_id AS GlobalId,
tx.global_name AS Global,
tx.mongo_bank_id as BankId,
tx.bank_name as Bank,
tx.mongo_m_id as MerchantId,
tx.mid as MID,
tx.corp_id as CorporateId,
tx.corp_name as Corporate,
requestor_origin as RequestorOrigin,
req_rcv_at_main_flw + (@TimeZoneOffset ||' minutes')::interval as TransactionDate,
tx.provider_reference as ProviderReference,
card_last_four_digits as CardLastFourDigits,
ts.provider_resp_code as ProviderResponseCode
from cs_rpt_txn tx
LEFT JOIN cs_rpt_timestamp ts ON tx.cs_rpt_txn_id = ts.cs_rpt_txn_id
" + where + @"
order by Date";
var result = _db.FetchPagedResult <TransactionBySettlementDateRow>(args.Page, args.PageSize, sql, args);
return(result);
}