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 PagedResult <APIResponseTimesRow> Get([FromQuery] APIResponseTimesArgs args) { 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 groupBy = args.GroupByBankId ? "tx.mongo_bank_id, tx.bank_name, tx.mid" : "tx.mid, tx.mongo_bank_id, tx.bank_name"; var sql = $@"SELECT tx.mid AS MID, 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.corp_id AS CorporateId, tx.corp_name AS Corporate, round(avg((DATE_PART('day', tx.res_snt_back_to_mrchnt + (@TimeZoneOffset ||' minutes')::interval - tx.req_rcv_at_main_flw + (@TimeZoneOffset ||' minutes')::interval) * 24*60*60 + DATE_PART('hour', tx.res_snt_back_to_mrchnt + (@TimeZoneOffset ||' minutes')::interval - tx.req_rcv_at_main_flw + (@TimeZoneOffset ||' minutes')::interval) * 3600 + DATE_PART('minute', tx.res_snt_back_to_mrchnt + (@TimeZoneOffset ||' minutes')::interval - tx.req_rcv_at_main_flw + (@TimeZoneOffset ||' minutes')::interval) * 60 + DATE_PART('second', tx.res_snt_back_to_mrchnt + (@TimeZoneOffset ||' minutes')::interval - tx.req_rcv_at_main_flw + (@TimeZoneOffset ||' minutes')::interval))):: numeric,2) as TotalDurationTransaction, round(avg(ts.ext_Apis_Total)::numeric, 3) as TotalDurationProviders, round(((avg((DATE_PART('day', tx.res_snt_back_to_mrchnt + (@TimeZoneOffset ||' minutes')::interval - tx.req_rcv_at_main_flw + (@TimeZoneOffset ||' minutes')::interval) * 24*60*60 + DATE_PART('hour', tx.res_snt_back_to_mrchnt + (@TimeZoneOffset ||' minutes')::interval - tx.req_rcv_at_main_flw + (@TimeZoneOffset ||' minutes')::interval) * 3600 + DATE_PART('minute', tx.res_snt_back_to_mrchnt + (@TimeZoneOffset ||' minutes')::interval - tx.req_rcv_at_main_flw + (@TimeZoneOffset ||' minutes')::interval) * 60 + DATE_PART('second', tx.res_snt_back_to_mrchnt + (@TimeZoneOffset ||' minutes')::interval - tx.req_rcv_at_main_flw + (@TimeZoneOffset ||' minutes')::interval)))) - avg(ts.ext_Apis_Total)):: numeric, 3) as TotalDurationAPC, count(tx.cs_rpt_txn_id) as NumberOfRequests FROM cs_rpt_txn tx LEFT JOIN (SELECT tsi.cs_rpt_txn_id, SUM((DATE_PART('day', tsi.res_rcv_frm_ext_api + (@TimeZoneOffset ||' minutes')::interval - tsi.req_snt_to_ext_api + (@TimeZoneOffset ||' minutes')::interval) * 24*60*60 + DATE_PART('hour', tsi.res_rcv_frm_ext_api + (@TimeZoneOffset ||' minutes')::interval - tsi.req_snt_to_ext_api + (@TimeZoneOffset ||' minutes')::interval) * 3600 + DATE_PART('minute', tsi.res_rcv_frm_ext_api + (@TimeZoneOffset ||' minutes')::interval - tsi.req_snt_to_ext_api + (@TimeZoneOffset ||' minutes')::interval) * 60 + DATE_PART('second', tsi.res_rcv_frm_ext_api + (@TimeZoneOffset ||' minutes')::interval - tsi.req_snt_to_ext_api + (@TimeZoneOffset ||' minutes')::interval))) AS ext_Apis_Total FROM cs_rpt_timestamp tsi GROUP BY tsi.cs_rpt_txn_id) ts ON tx.cs_rpt_txn_id = ts.cs_rpt_txn_id {where} GROUP BY {groupBy}, tx.mongo_m_id, tx.corp_id, tx.corp_name, tx.global_id, tx.global_name"; var transactions = PageExtensions.FetchPagedResult <APIResponseTimesRow>(_db, args.Page, args.PageSize, sql, args); return(transactions); }