public IEnumerable <FilterOption> Options([FromQuery] RiskSummaryArgs args, [FromQuery] OptionArgs optionsArgs)
{
var criteria = new List <string>();
criteria.Add("txn_type IN ('AddrValidation', 'DocumentCheck', 'EmailCheck', 'FraudScreen', 'IDCheck', 'EnrolCheck', 'Tokenize')");
criteria.AddIfNotNull(args.DateFrom, "req_rcv_at_main_flw >= @DateFrom");
criteria.AddIfNotNull(args.DateTo, "req_rcv_at_main_flw <= @DateTo");
criteria.AddIfNotNull(args.GlobalIds, "GlobalId", optionsArgs.OptionValue, "global_id IN (@GlobalIds)");
criteria.AddIfNotNull(args.BankIds, "BankId", optionsArgs.OptionValue, "mongo_bank_id IN (@BankIds)");
criteria.AddIfNotNull(args.CorporateIds, "CorporateId", optionsArgs.OptionValue, "corp_id IN (@CorporateIds)");
criteria.AddIfNotNull(args.MerchantIds, "MerchantId", optionsArgs.OptionValue, "mongo_m_id IN (@MerchantIds)");
var where = criteria.ToWhereClause();
var with = @"WITH tmp_table AS (select mid, req_rcv_at_main_flw::date as date, ts.api_name, count(*) as total_count from cs_rpt_txn tx
LEFT JOIN cs_rpt_timestamp ts on ts.cs_rpt_txn_id = tx.cs_rpt_txn_id
" + where + @"
group by mid, req_rcv_at_main_flw::date, ts.api_name order by mid, req_rcv_at_main_flw::date, ts.api_name) ";
var sql = with + @"SELECT DISTINCT
" + optionsArgs.OptionValue.MapToColumn() + @" AS Key,
" + optionsArgs.OptionText.MapToColumn() + @" AS Value
from cs_rpt_txn tx
JOIN cs_rpt_timestamp ts on ts.cs_rpt_txn_id = tx.cs_rpt_txn_id
LEFT JOIN tmp_table tmp on tmp.mid = tx.mid and tmp.date = tx.req_rcv_at_main_flw::date and tmp.api_name = ts.api_name
" + where + @"
group by tx.global_name, tx.global_id, tx.bank_name, tx.mongo_bank_id, tx.merchant_name, tx.mongo_m_id, tx.mid, tx.corp_id, tx.corp_name, tx.req_rcv_at_main_flw::date, ts.api_name, tx.resp_code, tx.resp_code_desc, tx.txn_type, tmp.total_count";
var result = _db.Fetch <FilterOption>(sql, args);
return(result);
}
public IEnumerable <FilterOption> Options([FromQuery] RefundRatesArgs args, [FromQuery] OptionArgs optionsArgs)
{
var criteria = new List <string>();
criteria.Add("tx.resp_code = '1000'");
criteria.AddIfNotNull(args.DateFrom, "req_rcv_at_main_flw >= @DateFrom");
criteria.AddIfNotNull(args.DateTo, "req_rcv_at_main_flw <= @DateTo");
criteria.AddIfNotNull(args.GlobalIds, "GlobalId", optionsArgs.OptionValue, "global_id IN (@GlobalIds)");
criteria.AddIfNotNull(args.BankIds, "BankId", optionsArgs.OptionValue, "mongo_bank_id IN (@BankIds)");
criteria.AddIfNotNull(args.CorporateIds, "CorporateId", optionsArgs.OptionValue, "corp_id IN (@CorporateIds)");
criteria.AddIfNotNull(args.MerchantIds, "MerchantId", optionsArgs.OptionValue, "mongo_m_id IN (@MerchantIds)");
criteria.AddIfNotNull(args.Currencies, "Currency", optionsArgs.OptionValue, "currency IN (@Currencies)");
criteria.AddIfNotNull(args.ExchangeCurrencies, "ExchangeCurrency", optionsArgs.OptionValue, "exch_currency IN (@ExchangeCurrencies)");
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 + @"
group by ts.api_name, tx.mid, tx.corp_id, tx.corp_name, tx.req_rcv_at_main_flw::date, tx.card_type, tx.currency, tx.exch_currency";
var result = _db.Fetch <FilterOption>(sql, args);
return(result);
}
public void AddAll(OptionArgs args)
{
foreach (Song song in songs)
{
MediaComponent.addSong(song);
}
}
public IEnumerable <FilterOption> Options([FromQuery] RiskDetailArgs args, [FromQuery] OptionArgs optionsArgs)
{
var criteria = new List <string>();
criteria.Add("mid <> ''");
criteria.Add("txn_type IN ('AddrValidation', 'DocumentCheck', 'EmailCheck', 'FraudScreen', 'IDCheck', 'EnrolCheck', 'Tokenize')");
criteria.AddIfNotNull(args.DateFrom, "req_rcv_at_main_flw >= @DateFrom");
criteria.AddIfNotNull(args.DateTo, "req_rcv_at_main_flw <= @DateTo");
criteria.AddIfNotNull(args.GlobalIds, "GlobalId", optionsArgs.OptionValue, "global_id IN (@GlobalIds)");
criteria.AddIfNotNull(args.BankIds, "BankId", optionsArgs.OptionValue, "mongo_bank_id IN (@BankIds)");
criteria.AddIfNotNull(args.CorporateIds, "CorporateId", optionsArgs.OptionValue, "corp_id IN (@CorporateIds)");
criteria.AddIfNotNull(args.MerchantIds, "MerchantId", optionsArgs.OptionValue, "mongo_m_id = (@MerchantIds)");
criteria.AddIfNotNull(args.Currencies, "Currency", optionsArgs.OptionValue, "currency IN (@Currencies)");
criteria.AddIfNotNull(args.ResponseCodes, "ResponseCode", optionsArgs.OptionValue, "tx.resp_code IN (@ResponseCodes)");
criteria.AddIfNotNull(args.BIN, "BIN", optionsArgs.OptionValue, "bin LIKE @BIN", () => args.BIN = string.Format("%{0}%", args.BIN));
criteria.AddIfNotNull(args.PaymentMethods, "PaymentMethod", optionsArgs.OptionValue, "method_name = @PaymentMethods");
criteria.AddIfNotNull(args.ExchangeCurrencies, "ExchangeCurrency", optionsArgs.OptionValue, "exch_currency IN (@ExchangeCurrencies)");
criteria.AddIfNotNull(args.EmailAddress, "customer_email_address LIKE @EmailAddress", () => args.EmailAddress = string.Format("%{0}%", args.EmailAddress));
criteria.AddIfNotNull(args.MerchantTransactionID, "m_txn_id LIKE @MerchantTransactionID", () => args.MerchantTransactionID = string.Format("%{0}%", args.MerchantTransactionID));
criteria.AddIfNotNull(args.TransactionTypes, "TransactionType", optionsArgs.OptionValue, "txn_type IN (@TransactionTypes)");
criteria.AddIfNotNull(args.Product, "Product", optionsArgs.OptionValue, "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 IEnumerable <FilterOption> Options([FromQuery] TransactionsArgs args, [FromQuery] OptionArgs optionsArgs)
{
var sql = GetSQL(args).ToOptionsSQL(optionsArgs);
var result = _db.Fetch <FilterOption>(sql, args);
return(result);
}
private void DiscardColors(OptionArgs args)
{
OBlock.resetColor();
IBlock.resetColor();
TBlock.resetColor();
JBlock.resetColor();
LBlock.resetColor();
SBlock.resetColor();
ZBlock.resetColor();
ColorData.saveColors();
}
private void Reset(OptionArgs args)
{
BlockType.textureAsset = "Game/Bricks/Cat-Eye Brick";
OBlock.resetColor();
IBlock.resetColor();
TBlock.resetColor();
JBlock.resetColor();
LBlock.resetColor();
SBlock.resetColor();
ZBlock.resetColor();
ColorData.saveColors();
}
public void GivenTheFollowingOption(Table table)
{
this.input = new OptionArgs
{
OptionType = (OptionType)Enum.Parse(typeof(OptionType), table.Rows[0]["type"]),
Spot = Double.Parse(table.Rows[0]["spot"], CultureInfo.InvariantCulture),
Strike = Double.Parse(table.Rows[0]["strike"], CultureInfo.InvariantCulture),
Rate = Double.Parse(table.Rows[0]["rate"], CultureInfo.InvariantCulture),
Volatility = Double.Parse(table.Rows[0]["volatility"], CultureInfo.InvariantCulture)
};
string maturityString = table.Rows[0]["maturity"];
this.input.Maturity = this.ParseMaturity(maturityString);
}
public static string ToOptionsSQL(this string sql, OptionArgs optionArgs, Func <string, string> columnMapper)
{
var valueColumn = columnMapper(optionArgs.OptionValue);
var textColumn = columnMapper(optionArgs.OptionText);
var selStart = sql.LastIndexOf("SELECT ", StringComparison.CurrentCultureIgnoreCase); //This is a problem because it prevents using subqueries in the SQL statement. A better approach could be instead of using a single method to generate the entire SQL statement (Esample: [WebAPI.Endpoints.Transactions.TransactionsController.GetSQL()]), using several methods and that each one be responsible for generating a part of the statement, then you could call the method that generates the necessary part, and avoid having to find the "SELECT" with a "LastIndexOf"
var selEnd = sql.LastIndexOf(" FROM ", StringComparison.CurrentCultureIgnoreCase);
var orderByStart = sql.LastIndexOf(" ORDER BY ", StringComparison.CurrentCultureIgnoreCase);
var len = orderByStart > selEnd ? orderByStart - selEnd : sql.Length - selEnd;
var tmpTable = sql.Substring(0, selStart);
var fromWhere = sql.Substring(selEnd, len);
var optionsSQL = $@"{tmpTable}
SELECT DISTINCT
{valueColumn} AS Key,
{textColumn} AS Value
{fromWhere}
AND {valueColumn} <> ''
AND {textColumn} <> ''
ORDER BY {textColumn}";
return(optionsSQL);
}
public IEnumerable <FilterOption> Options([FromQuery] TransactionByCountrySummaryArgs args, [FromQuery] OptionArgs optionsArgs)
{
var criteria = new List <string>();
criteria.AddIfNotNull(args.DateFrom, "req_rcv_at_main_flw >= @DateFrom");
criteria.AddIfNotNull(args.DateTo, "req_rcv_at_main_flw <= @DateTo");
criteria.AddIfNotNull(args.GlobalIds, "GlobalId", optionsArgs.OptionValue, "global_id IN (@GlobalIds)");
criteria.AddIfNotNull(args.BankIds, "BankId", optionsArgs.OptionValue, "mongo_bank_id IN (@BankIds)");
criteria.AddIfNotNull(args.CorporateIds, "CorporateId", optionsArgs.OptionValue, "corp_id IN (@CorporateIds)");
criteria.AddIfNotNull(args.MID, "MID", optionsArgs.OptionValue, "mid = (@MID)");
criteria.AddIfNotNull(args.Country, "Country", optionsArgs.OptionValue, "upper(tx.billing_address_country) = @Country", () => args.Country = args.Country.ToUpper());
criteria.AddIfNotNull(args.PaymentMethod, "PaymentMethod", optionsArgs.OptionValue, "method_name = @PaymentMethod");
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 + @"
and tx.txn_type IN ('AuthPayment','IncrementalAuthPayment','OneStepPayment','CapturePayment','OnlinePayment','CreditPayment')
group by api_name, mid, tx.corp_id, tx.corp_name, tx.method_name, req_rcv_at_main_flw::date, card_type, billing_address_country" ;
var result = _db.Fetch <FilterOption>(sql, args);
return(result);
}
public IEnumerable <FilterOption> Options([FromQuery] BillingSummaryArgs args, [FromQuery] OptionArgs optionsArgs)
{
var criteria = new List <string>();
criteria.Add("mid <> ''");
criteria.Add("NOT mongo_bank_id = 'UnknownSellingBankId'");
criteria.Add("mongo_bank_id <> ''");
criteria.Add("NOT mongo_m_id = 'UnknownMerchantID'");
criteria.Add("mongo_m_id <> ''");
criteria.AddIfNotNull(args.DateFrom, "req_rcv_at_main_flw >= @DateFrom");
criteria.AddIfNotNull(args.DateTo, "req_rcv_at_main_flw <= @DateTo");
criteria.AddIfNotNull(args.GlobalIds, "GlobalId", optionsArgs.OptionValue, "global_id IN (@GlobalIds)");
criteria.AddIfNotNull(args.BankIds, "BankId", optionsArgs.OptionValue, "mongo_bank_id IN (@BankIds)");
criteria.AddIfNotNull(args.CorporateIds, "CorporateId", optionsArgs.OptionValue, "corp_id IN (@CorporateIds)");
criteria.AddIfNotNull(args.MerchantIds, "MerchantId", optionsArgs.OptionValue, "mongo_m_id IN (@MerchantIds)");
criteria.AddIfNotNull(args.APIIds, "APIId", optionsArgs.OptionValue, "ts.api_id IN (@APIIds)");
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 + @"
GROUP BY tx.global_id, tx.global_name, tx.mongo_bank_id, tx.bank_name, tx.corp_id, tx.corp_name, tx.mongo_m_id, tx.mid, tx.merchant_name, ts.api_id, ts.api_name";
var result = _db.Fetch <FilterOption>(sql, args);
return(result);
}
public IEnumerable <FilterOption> Options([FromQuery] HubSummaryDetailArgs args, [FromQuery] OptionArgs optionsArgs)
{
var criteria = new List <string>();
criteria.Add("mid <> ''");
criteria.AddIfNotNull(args.DateFrom, "req_rcv_at_main_flw >= @DateFrom");
criteria.AddIfNotNull(args.DateTo, "req_rcv_at_main_flw <= @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.TransactionTypes, "txn_type IN (@TransactionTypes)");
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 + @"
group by ts.api_name, tx.corp_id, tx.corp_name" ;
var result = _db.Fetch <FilterOption>(sql, args);
return(result);
}
public IEnumerable <FilterOption> Options([FromQuery] AuthorisationBreakdownSummaryArgs args, [FromQuery] OptionArgs optionsArgs)
{
var criteria = new List <string>();
criteria.AddIfNotNull(args.DateFrom, "req_rcv_at_main_flw >= @DateFrom");
criteria.AddIfNotNull(args.DateTo, "req_rcv_at_main_flw <= @DateTo");
criteria.AddIfNotNull(args.GlobalIds, "GlobalId", optionsArgs.OptionValue, "global_id IN (@GlobalIds)");
criteria.AddIfNotNull(args.BankIds, "BankId", optionsArgs.OptionValue, "mongo_bank_id IN (@BankIds)");
criteria.AddIfNotNull(args.CorporateIds, "CorporateId", optionsArgs.OptionValue, "corp_id IN (@CorporateIds)");
criteria.AddIfNotNull(args.MerchantIds, "MerchantId", optionsArgs.OptionValue, "mongo_m_id = (@MerchantIds)");
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 + @"
AND tx.txn_type IN (AuthPayment','IncrementalAuthPayment','OneStepPayment','VerifyAccount')
GROUP BY api_name, mid, tx.corp_id, tx.corp_name, card_type, req_rcv_at_main_flw::date, ts.resp_code, ts.resp_code_desc" ;
var result = _db.Fetch <FilterOption>(sql, args);
return(result);
}
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);
}
private void RemoveSong(OptionArgs args)
{
MediaComponent.removeSong(args.text);
dynamicInitialize();
}
public void SetSubMenu(OptionArgs args)
{
MediaComponent.collectionSongs.setSongs(genreCollection[args.index].Name, genreCollection[args.index].Songs);
}
public IEnumerable <FilterOption> Options([FromQuery] UptimePerformanceIndicatorsArgs args, [FromQuery] OptionArgs optionsArgs)
{
var sql = GetSQL(args).ToOptionsSQL(optionsArgs);
var result = _db.Fetch <FilterOption>(sql, args);
return(result);
}
public IEnumerable <FilterOption> Options([FromQuery] StatementListArgs args, [FromQuery] OptionArgs optionsArgs)
{
return(null);
}
public static string ToOptionsSQL(this string sql, OptionArgs optionArgs)
{
return(ToOptionsSQL(sql, optionArgs, MapToColumn));
}
public IEnumerable <FilterOption> Options([FromQuery] TransactionsEligibleForCancelArgs args, [FromQuery] OptionArgs optionsArgs)
{
var sql = GetSQL(args).ToOptionsSQL(optionsArgs, x => x); //TRANS-104: Columns names are not mapped anymore, since with de addition of new temp table [tmpFirstTxn1] in GENSPT-409, fields like [tx.mongo_bank_id] aren't valid anymore
var result = _db.Fetch <FilterOption>(sql, args);
return(result);
}
public IEnumerable <FilterOption> Options([FromQuery] CardTypeBreakdownArgs args, [FromQuery] OptionArgs optionsArgs)
{
var criteria = new List <string>();
criteria.Add("tx.resp_code = '1000'");
criteria.Add("(txn_type = 'CapturePayment' OR txn_type = 'OneStepPayment')");
criteria.AddIfNotNull(args.DateFrom, "req_rcv_at_main_flw >= @DateFrom");
criteria.AddIfNotNull(args.DateTo, "req_rcv_at_main_flw <= @DateTo");
criteria.AddIfNotNull(args.GlobalIds, "GlobalId", optionsArgs.OptionValue, "global_id IN (@GlobalIds)");
criteria.AddIfNotNull(args.BankIds, "BankId", optionsArgs.OptionValue, "mongo_bank_id IN (@BankIds)");
criteria.AddIfNotNull(args.CorporateIds, "CorporateId", optionsArgs.OptionValue, "corp_id IN (@CorporateIds)");
criteria.AddIfNotNull(args.MerchantIds, "MerchantId", optionsArgs.OptionValue, "mongo_m_id = (@MerchantIds)");
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 + @"
group by api_name, tx.corp_id, tx.corp_name, card_type, tx.bin_country";
var result = _db.Fetch <FilterOption>(sql, args);
return(result);
}
public IEnumerable <FilterOption> Options([FromQuery] PaymentMethodBreakdownArgs args, [FromQuery] OptionArgs optionsArgs)
{
var criteria = new List <string>();
criteria.Add("tx.resp_code = '1000'");
criteria.Add("(txn_type = 'CapturePayment' OR txn_type = 'OneStepPayment')");
criteria.AddIfNotNull(args.DateFrom, "req_rcv_at_main_flw >= @DateFrom");
criteria.AddIfNotNull(args.DateTo, "req_rcv_at_main_flw <= @DateTo");
criteria.AddIfNotNull(args.GlobalIds, "GlobalId", optionsArgs.OptionValue, "global_id IN (@GlobalIds)");
criteria.AddIfNotNull(args.BankIds, "BankId", optionsArgs.OptionValue, "mongo_bank_id IN (@BankIds)");
criteria.AddIfNotNull(args.CorporateIds, "CorporateId", optionsArgs.OptionValue, "corp_id IN (@CorporateIds)");
criteria.AddIfNotNull(args.MerchantIds, "MerchantId", optionsArgs.OptionValue, "mongo_m_id = (@MerchantIds)");
var where = criteria.ToWhereClause();
var with = @"WITH tmp_table AS (select mid, currency, case when bin_country is null or bin_country = '' then '[unknown]' else bin_country end, count(*) as total_count, sum(amount) as total_value from cs_rpt_txn tx
" + where + @"
group by mid, currency, tx.bin_country order by mid, currency, bin_country) ";
var sql = with + @"SELECT DISTINCT
" + optionsArgs.OptionValue.MapToColumn() + @" AS Key,
" + optionsArgs.OptionText.MapToColumn() + @" AS Value
from cs_rpt_txn tx
LEFT JOIN tmp_table tmp on tmp.currency = tx.currency and tmp.bin_country = case when tx.bin_country is null or tx.bin_country = '' then '[unknown]' else tx.bin_country end
" + where + @"
group by tx.global_name, tx.global_id, tx.bank_name, tx.mongo_bank_id, tx.corp_name, tx.corp_id, tx.merchant_name, tx.mongo_m_id, tx.mid, tx.currency, tx.method_name, tx.bin_country, tmp.total_count, tmp.total_value";
var result = _db.Fetch <FilterOption>(sql, args);
return(result);
}
private void ClearScores(OptionArgs args)
{
HighScoreData.clearScores();
dynamicInitialize();
}
private void SaveControls(OptionArgs args)
{
GameManager.controlType = controlType;
}
public IEnumerable <FilterOption> Options([FromQuery] APIResponseTimesArgs args, [FromQuery] OptionArgs optionsArgs)
{
var criteria = new List <string>();
criteria.Add("tx.amount > 0");
criteria.AddIfNotNull(args.DateFrom, "tx.req_rcv_at_main_flw >= @DateFrom");
criteria.AddIfNotNull(args.DateTo, "tx.req_rcv_at_main_flw <= @DateTo");
criteria.AddIfNotNull(args.GlobalIds, "tx.global_id IN (@GlobalIds)");
criteria.AddIfNotNull(args.BankIds, "tx.mongo_bank_id IN (@BankIds)");
criteria.AddIfNotNull(args.CorporateIds, "tx.corp_id IN (@CorporateIds)");
criteria.AddIfNotNull(args.MerchantIds, "tx.mongo_m_id = (@MerchantIds)");
var where = criteria.ToWhereClause();
var valueColumn = optionsArgs.OptionValue.MapToColumn();
var textColumn = optionsArgs.OptionText.MapToColumn();
var sql = $@"SELECT DISTINCT
{valueColumn} AS Key,
{textColumn} 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}
AND {valueColumn} <> ''
AND {textColumn} <> ''
ORDER BY {textColumn}";
var options = _db.Fetch <FilterOption>(sql, args);
return(options);
}
public IEnumerable <FilterOption> Options([FromQuery] TransactionSuccessFailArgs args, [FromQuery] OptionArgs optionsArgs)
{
var criteria = new List <string>();
criteria.AddIfNotNull(args.DateFrom, "req_rcv_at_main_flw >= @DateFrom");
criteria.AddIfNotNull(args.DateTo, "req_rcv_at_main_flw <= @DateTo");
criteria.AddIfNotNull(args.MerchantIds, "MerchantId", optionsArgs.OptionValue, "mongo_m_id = (@MerchantIds)");
criteria.AddIfNotNull(args.CorporateIds, "CorporateId", optionsArgs.OptionValue, "corp_id IN (@CorporateIds)");
criteria.AddIfNotNull(args.BankIds, "BankId", optionsArgs.OptionValue, "mongo_bank_id IN (@BankIds)");
criteria.AddIfNotNull(args.GlobalIds, "GlobalId", optionsArgs.OptionValue, "global_id IN (@GlobalIds)");
criteria.AddIfNotNull(args.APIIDs, "APIId", optionsArgs.OptionValue, "ts.api_id IN (@APIIDs)");
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 + @"
and tx.txn_type IN ('AuthPayment','IncrementalAuthPayment','OneStepPayment','CapturePayment','OnlinePayment','CreditPayment','VerifyAccount')
group by ts.api_name, ts.api_id, tx.mongo_m_id, tx.mid, tx.corp_id, tx.corp_name, tx.txn_type, tx.method_name, req_rcv_at_main_flw::date, card_type";
var result = _db.Fetch <FilterOption>(sql, args);
return(result);
}
public IEnumerable <FilterOption> Options([FromQuery] AuthResponseReasonArgs args, [FromQuery] OptionArgs optionsArgs)
{
var criteria = new List <string>();
criteria.Add("tx.txn_type IN ('AuthPayment','IncrementalAuthPayment','VerifyAccount')");
criteria.AddIfNotNull(args.DateFrom, "req_rcv_at_main_flw >= @DateFrom");
criteria.AddIfNotNull(args.DateTo, "req_rcv_at_main_flw <= @DateTo");
criteria.AddIfNotNull(args.GlobalIds, "GlobalId", optionsArgs.OptionValue, "global_id IN (@GlobalIds)");
criteria.AddIfNotNull(args.BankIds, "BankId", optionsArgs.OptionValue, "mongo_bank_id IN (@BankIds)");
criteria.AddIfNotNull(args.CorporateIds, "CorporateId", optionsArgs.OptionValue, "corp_id IN (@CorporateIds)");
criteria.AddIfNotNull(args.MerchantIds, "MerchantId", optionsArgs.OptionValue, "mongo_m_id = (@MerchantIds)");
var where = criteria.ToWhereClause();
var with = @"WITH tmp_table AS (select mid, req_rcv_at_main_flw::date as date, card_type, Bin, count(*) as total_count from cs_rpt_txn tx
" + where + @"
GROUP BY mid, req_rcv_at_main_flw::date, card_type, Bin ORDER BY mid, req_rcv_at_main_flw::date, card_type, Bin) ";
var sql = with + @"SELECT DISTINCT
" + optionsArgs.OptionValue.MapToColumn() + @" AS Key,
" + optionsArgs.OptionText.MapToColumn() + @" AS Value
FROM cs_rpt_txn tx
LEFT JOIN tmp_table tmp ON tmp.mid = tx.mid AND tmp.date = tx.req_rcv_at_main_flw::date AND tmp.card_type = tx.card_type AND tmp.Bin = tx.Bin
" + where + @"
GROUP BY tx.global_name, tx.global_id, tx.bank_name, tx.mongo_bank_id, tx.corp_name, tx.corp_id, tx.merchant_name, tx.mongo_m_id, tx.mid, tx.req_rcv_at_main_flw::date, tx.card_type, tx.Bin, tx.resp_code, tx.resp_code_desc, tmp.total_count";
var result = _db.Fetch <FilterOption>(sql, args);
return(result);
}
public void AddSong(OptionArgs args)
{
MediaComponent.addSong(songs[args.index - 1]);
}
public IEnumerable <FilterOption> Options([FromQuery] TransactionsEligibleForRefundApprovalArgs args, [FromQuery] OptionArgs optionsArgs)
{
var sql = GetSQL(args);
var valueColumn = MapToColumn(optionsArgs.OptionValue);
var textColumn = MapToColumn(optionsArgs.OptionText);
var binding = $@"WITH tmp AS({sql})";
var optionsSQL = $@"{binding}
SELECT DISTINCT
{valueColumn} AS Key,
{textColumn} AS Value
from cs_rpt_txn tx LEFT JOIN cs_rpt_timestamp ts ON tx.cs_rpt_txn_id = ts.cs_rpt_txn_id AND ts.api_id IN (@APIIds) LEFT JOIN tmp ON tx.m_txn_id = tmp.MerchantTransactionID AND tmp.MerchantId = tx.mongo_m_id where tx.m_txn_id = tmp.MerchantTransactionID AND (tx.txn_type = 'OneStepPayment' OR tx.txn_type = 'RefundPayment')
AND {valueColumn} <> ''
AND {textColumn} <> ''
ORDER BY {textColumn}";
var result = _db.Fetch <FilterOption>(optionsSQL, args);
return(result);
}
private void SaveColors(OptionArgs args)
{
ColorData.saveColors();
}
private void Race(OptionArgs args)
{
GameManager.gameType = new RaceData();
}
