public PagedResult <UptimePerformanceIndicatorsRow> Get([FromQuery] UptimePerformanceIndicatorsArgs args) { var sql = GetSQL(args); var result = _db.FetchPagedResult <UptimePerformanceIndicatorsRow>(args.Page, args.PageSize, sql, args); return(result); }
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); }
private static string GetSQL(UptimePerformanceIndicatorsArgs args) { var criteria = new List <string>(); 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.MerchantIds, "tx.mongo_m_id IN (@MerchantIds)"); criteria.AddIfNotNull(args.CorporateIds, "tx.corp_id IN (@CorporateIds)"); criteria.AddIfNotNull(args.BankIds, "tx.mongo_bank_id IN (@BankIds)"); criteria.AddIfNotNull(args.GlobalIds, "tx.global_id IN (@GlobalIds)"); criteria.AddIfNotNull(args.MID, "tx.mid = (@MID)"); var where = criteria.ToWhereClause(); var groupBy = args.GroupByBankId.HasValue && args.GroupByBankId.Value ? "tx.mongo_bank_id, tx.bank_name, tx.mid" : "tx.mid, tx.mongo_bank_id, tx.bank_name"; var orderBy = args.OrderBys.ToOrderByClause(""); 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 - tx.req_rcv_at_main_flw) * 24*60*60 + DATE_PART('hour', tx.res_snt_back_to_mrchnt - tx.req_rcv_at_main_flw) * 3600 + DATE_PART('minute', tx.res_snt_back_to_mrchnt - tx.req_rcv_at_main_flw) * 60 + DATE_PART('second', tx.res_snt_back_to_mrchnt - tx.req_rcv_at_main_flw))):: 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 - tx.req_rcv_at_main_flw) * 24*60*60 + DATE_PART('hour', tx.res_snt_back_to_mrchnt - tx.req_rcv_at_main_flw) * 3600 + DATE_PART('minute', tx.res_snt_back_to_mrchnt - tx.req_rcv_at_main_flw) * 60 + DATE_PART('second', tx.res_snt_back_to_mrchnt - tx.req_rcv_at_main_flw)))) - avg(ts.ext_Apis_Total)):: numeric, 3) as TotalDurationAPC FROM cs_rpt_txn tx LEFT JOIN (SELECT cs_rpt_txn_id, SUM((DATE_PART('day', res_rcv_frm_ext_api - req_snt_to_ext_api) * 24*60*60 + DATE_PART('hour', res_rcv_frm_ext_api - req_snt_to_ext_api) *3600 + DATE_PART('minute', res_rcv_frm_ext_api - req_snt_to_ext_api) * 60 + DATE_PART('second', res_rcv_frm_ext_api - req_snt_to_ext_api)) ) AS ext_Apis_Total FROM cs_rpt_timestamp GROUP BY 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 {orderBy}"; return(sql); }